OPM Formula Creation

 
CREATE TABLE APPS.JLN_FORMULA_HEADERS
(
  FORMULA_NO               VARCHAR2(32 BYTE)    NOT NULL,
  FORMULA_VERS             NUMBER               NOT NULL,
  FORMULA_TYPE             NUMBER(5)            NOT NULL,
  FORMULA_DESC1            VARCHAR2(70 BYTE),
  FORMULA_CLASS            VARCHAR2(8 BYTE),
  INACTIVE_IND             NUMBER(5)            NOT NULL,
  FORMULA_UOM              VARCHAR2(4 BYTE),
  FORMULA_STATUS           VARCHAR2(30 BYTE),
  FLAG                     VARCHAR2(2 BYTE),
  RECORD_TYPE              VARCHAR2(12 BYTE),
  OWNER_ORGANIZATION_CODE  VARCHAR2(10 BYTE),
  OWNER_NAME               VARCHAR2(20 BYTE),
  LINE_TYPE                NUMBER(10),
  LINE_NO                  INTEGER,
  QTY                      NUMBER(20,10),
  RELEASE_TYPE             VARCHAR2(10 BYTE),
  COST_ALLOC               VARCHAR2(10 BYTE),
  SCALE_TYPE_HDR           NUMBER(10),
  DETAIL_UOM               VARCHAR2(10 BYTE),
  SCALE_TYPE_DTL           INTEGER,
  PHANTOM_TYPE             INTEGER,
  DELETE_MARK              VARCHAR2(10 BYTE),
  CONTRIBUTE_YIELD_IND     VARCHAR2(10 BYTE),
  INVENTORY_ITEM_CODE      VARCHAR2(15 BYTE)
);

Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values  ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',1,1,1000,1,1,1,'PCS',1,0,0,'N','35521');

Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values  ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,244,1,1,1,'KG',1,0,0,'N','0130019210');

Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values  ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,8.196721,1,1,1,'KG',1,0,0,'N','0729424400');
Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values  ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,20.833333,1,1,1,'PCS',1,0,0,'N','0729880800');
Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values  ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,0.434028,1,1,1,'PCS',1,0,0,'N','0729203010');
Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values  ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,0.006,1,1,1,'PCS',1,0,0,'N','0729285200');
Insert into JLN_FORMULA_HEADERS(FORMULA_NO, FORMULA_VERS, FORMULA_TYPE, FORMULA_DESC1, FORMULA_CLASS, INACTIVE_IND, FORMULA_UOM, FORMULA_STATUS, FLAG, RECORD_TYPE,OWNER_ORGANIZATION_CODE, OWNER_NAME, LINE_TYPE, LINE_NO, QTY, RELEASE_TYPE, COST_ALLOC, SCALE_TYPE_HDR, DETAIL_UOM, SCALE_TYPE_DTL, PHANTOM_TYPE, DELETE_MARK, CONTRIBUTE_YIELD_IND, INVENTORY_ITEM_CODE) Values  ('35521',1,1,'Packaging formula for Vermicelli 200 gm x48 pcs ctn','',0,'PCS',100,'','','FMO','SETUP USER',-1,1,0.011,1,1,1,'PCS',1,0,0,'N','0529574100');

COMMIT;
----------------------------------------

DECLARE
   CURSOR c_header IS
      SELECT DISTINCT formula_no
      FROM     JLN_formula_headers --mii_gmd_formula
       WHERE flag IS NULL;
      
   CURSOR c_master  (
      p_formula     varchar2   --TEST_M020
   ) IS
      SELECT *
        FROM     JLN_formula_headers --mii_gmd_formula
       WHERE flag IS NULL
         AND formula_no = p_formula;
      
   l_formula_header_tbl   gmd_formula_pub.formula_insert_hdr_tbl_type;
   l_formula             JLN_formula_headers%ROWTYPE; -- mii_gmd_formula%ROWTYPE;
   l_count                NUMBER                                      := 0;
   l_loop_cnt             NUMBER                                      := 0;
   l_record_count         NUMBER                                      := 0;
   l_data                 VARCHAR2 (2000);
   l_return_status        VARCHAR2 (1);
   l_status               VARCHAR2 (1);
   i                      NUMBER                                      := 1;
   l_dummy_cnt            NUMBER;
   l_api_version          NUMBER                                      := 1;
   l_init_msg_list        BOOLEAN;
   l_commit               BOOLEAN;
   return_sts             BOOLEAN;
   v_item_id              number;
   v_organization_id      number;
   v_user_id                            number;
BEGIN
--FND_GLOBAL.APPS_INITIALIZE(1112,22882,552,0);
   fnd_global.apps_initialize (user_id           => 1110,
                               resp_id           => 50774,
                               resp_appl_id      => 555
                              );
                              
                            --  FND_RESPONSIBILITY_VL ,  FND_user
                            
                              
/*FND_GLOBAL.APPS_INITIALIZE(user_id, resp_id, resp_appl_id);*/
   l_init_msg_list := TRUE;
   l_commit := TRUE;

FOR l_header IN c_header  
LOOP
   OPEN c_master(l_header.formula_no);
   LOOP
      FETCH c_master INTO l_formula;
      exit WHEN c_master%NOTFOUND;
     
      dbms_output.put_line (c_master%ROWCOUNT);
      BEGIN
         SELECT DISTINCT inventory_item_id
           INTO v_item_id
           FROM mtl_system_items_b
          WHERE segment1 = UPPER(l_formula.inventory_item_code);
      
         SELECT organization_id
          INTO v_organization_id
           FROM mtl_parameters
          WHERE organization_code = l_formula.owner_organization_code;
      
         SELECT user_id
           INTO v_user_id
           FROM fnd_user
          WHERE user_name = l_formula.owner_name;
          
      EXCEPTION
         WHEN others THEN
            UPDATE JLN_formula_headers --mii_gmd_formula
             set flag = 'E'
             --, note = 'Ada exception'
            WHERE formula_no = l_formula.formula_no;
      END;


      l_formula_header_tbl (c_master%ROWCOUNT).record_type := 'I';
      l_formula_header_tbl (c_master%ROWCOUNT).formula_no := UPPER(l_formula.formula_no);
      l_formula_header_tbl (c_master%ROWCOUNT).formula_vers := l_formula.formula_vers;
      l_formula_header_tbl (c_master%ROWCOUNT).formula_type := l_formula.formula_type;
      l_formula_header_tbl (c_master%ROWCOUNT).formula_desc1 := l_formula.formula_desc1;
      l_formula_header_tbl (c_master%ROWCOUNT).formula_class := l_formula.formula_class;
      l_formula_header_tbl (c_master%ROWCOUNT).inactive_ind := l_formula.inactive_ind;
      l_formula_header_tbl (c_master%ROWCOUNT).owner_organization_id := v_organization_id;
      l_formula_header_tbl (c_master%ROWCOUNT).formula_status := l_formula.formula_status;
      l_formula_header_tbl (c_master%ROWCOUNT).owner_id := v_user_id;
      l_formula_header_tbl (c_master%ROWCOUNT).line_type := l_formula.line_type;
      l_formula_header_tbl (c_master%ROWCOUNT).line_no := l_formula.line_no;
      l_formula_header_tbl (c_master%ROWCOUNT).inventory_item_id := v_item_id;
      l_formula_header_tbl (c_master%ROWCOUNT).qty := l_formula.qty;
      l_formula_header_tbl (c_master%ROWCOUNT).detail_uom := l_formula.detail_uom;
      l_formula_header_tbl (c_master%ROWCOUNT).release_type := l_formula.release_type;
      l_formula_header_tbl (c_master%ROWCOUNT).scale_type_hdr := l_formula.scale_type_hdr;
      l_formula_header_tbl (c_master%ROWCOUNT).scale_type_dtl := l_formula.scale_type_dtl;
      l_formula_header_tbl (c_master%ROWCOUNT).cost_alloc := l_formula.cost_alloc;
      l_formula_header_tbl (c_master%ROWCOUNT).CONTRIBUTE_YIELD_IND := 'N'; -- l_formula.CONTRIBUTE_YIELD_IND;
      l_formula_header_tbl (c_master%ROWCOUNT).PHANTOM_TYPE := l_formula.PHANTOM_TYPE;
      l_formula_header_tbl (c_master%ROWCOUNT).delete_mark := l_formula.delete_mark;
      l_formula_header_tbl (c_master%ROWCOUNT).CONTRIBUTE_STEP_QTY_IND := 'Y';
     
     
      DBMS_OUTPUT.put_line ('Value Test ' || l_formula_header_tbl (1).formula_no);
      
    
 
     
--      UPDATE mii_gmd_formula
--         set flag = 'Y'
--       WHERE formula_no = l_formula.formula_no
--         AND line_no = l_formula.line_no
--         AND inventory_item_code = l_formula.inventory_item_code;
        
   END LOOP;
 
   CLOSE c_master;
 
   gmd_formula_pub.insert_formula
                                (p_api_version             => 1.0,
                                 p_formula_header_tbl      => l_formula_header_tbl,
                                 x_return_status           => l_return_status,
                                 x_msg_count               => l_count,
                                 x_msg_data                => l_data
                                );
                                DBMS_OUTPUT.put_line ('l_return_status:'||l_return_status);
                                DBMS_OUTPUT.put_line ('l_data:'||l_data);
                               
   IF l_return_status = 'E' OR l_return_status = 'U'
         THEN
            UPDATE JLN_formula_headers--mii_gmd_formula
             set flag = l_return_status
             --, note = l_data
       WHERE formula_no = l_formula.formula_no;
  ELSE
     UPDATE JLN_formula_headers--mii_gmd_formula
             set flag = 'Y'
--             , note = l_data
       WHERE formula_no = l_formula.formula_no;
   END IF;                            
                                   
END LOOP;
/*
   IF l_count >= 0
   THEN
      l_loop_cnt := 1;

      LOOP
         fnd_msg_pub.get (p_msg_index          => l_loop_cnt,
                          p_data               => l_data,
                          p_encoded            => fnd_api.g_false,
                          p_msg_index_out      => l_dummy_cnt
                         );
         DBMS_OUTPUT.put_line ('Record = ' || l_loop_cnt);
         DBMS_OUTPUT.put_line (l_data);

         IF l_status = 'E' OR l_status = 'U'
         THEN
            l_data := CONCAT ('ERROR ', l_data);
         END IF;

         DBMS_OUTPUT.put_line (l_data);

         IF (l_status = 'U')
         THEN
            l_return_status := l_status;
         ELSIF (l_status = 'E' AND l_return_status <> 'U')
         THEN
            l_return_status := l_status;
         ELSE
            l_return_status := l_status;
         END IF;

         l_loop_cnt := l_loop_cnt + 1;

         IF l_loop_cnt > l_count
         THEN
            EXIT;
         END IF;
      END LOOP;
   END IF;*/
   COMMIT;

END;

OBIEE 12c -5

OBIEE 12c -5

OBIEE 12c -3

OBIEE 12c -3

Error : To Create OPM Batch, ORA-01400: Cannot insert null into .... in Package GME_BATCH_HEADER_DBL Procedure INSERT_ROW

First check calculate_step_quantity is null or not.

select * from gmd_recipes_b
where calculate_step_quantity is null

Sometimes, When uploaded Recipe using API, it will happen,

Solution is to update by bellow Script : 

update gmd_recipes_b
set calculate_step_quantity = 0  -- or = 1
where calculate_step_quantity is null

Here  0  is if no automatic_step_calculation needed &                                                                                            1 is if automatic_step_calculation 

 

SLA Setup Details R12

 
  select *
   FROM xla_prod_acct_headers  pah,
           xla_event_classes_tl   ecl,
           xla_event_types_tl     etl,
           xla_lookups            xlk
     WHERE     pah.application_id = ecl.application_id
           AND pah.entity_code = ecl.entity_code
           AND pah.event_class_code = ecl.event_class_code
           AND ecl.language = USERENV ('LANG')
           AND pah.application_id = etl.application_id
           AND pah.entity_code = etl.entity_code
           AND pah.event_class_code = etl.event_class_code
           AND pah.event_type_code = etl.event_type_code
           AND etl.language = USERENV ('LANG')
           AND xlk.lookup_code(+) = pah.validation_status_code
           AND xlk.lookup_type(+) = 'XLA_COMPILE_STATUS_TYPE';

Inactive Organizations

To Inactive Organizations existing organizations from back end:

update hr_organization_information o3 set ORG_INFORMATION2='N' where ORG_INFORMATION1='INV'
and ORGANIZATION_ID in (
select ORGANIZATION_ID FROM hr_all_organization_units hao-- where NAME='CTG-MAT-AMCL'
where DATE_FROM<=to_date('9/24/2019','mm-dd-yyyy') and DATE_to is null
and NAME like 'HIP-%'
)


update hr_all_organization_units hao set DATE_TO =to_date(sysdate-1,'dd-mm-yyyy')
where DATE_FROM<=to_date('9/24/2019','mm-dd-yyyy') and DATE_TO is null and NAME like 'HIP-%'

OBIEE 12c -4

OBIEE 12c -4

Transaction Summary:

 Transaction Summary:

select transaction_type_name, count(1) tm from mtl_material_transactions a, mtl_transaction_types tp
  where a.transaction_type_id=tp.transaction_type_id
  group by transaction_type_name
  ORDER  by transaction_type_name