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
 

Checking LCM Component Cost

To check any Component Cost generated multiple times, use the bellow SQL :

Also can check In Landed Cost Management (LCM) On Item Costs > Actual Cost Transactions View, however, the entry for Analysis Code "DUTY" and Component Class "OVERHEAD" is exactly double.
 

 

 a) Select
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE, count(*)
From gmf_lc_adj_transactions
Group by
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE
having count(*) > 1;

2) select * From gmf_lc_adj_transactions where
(RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE) IN
(Select
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE
From gmf_lc_adj_transactions
Group by
RCV_TRANSACTION_ID, EVENT_TYPE, EVENT_SOURCE_ID,
LEDGER_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID,
PARENT_SHIP_LINE_ID, SHIP_HEADER_ID, SHIP_LINE_GROUP_ID,
SHIP_LINE_ID, ADJUSTMENT_NUM, COMPONENT_NAME,
COST_CMPNTCLS_ID, COST_ANALYSIS_CODE,
LC_ADJUSTMENT_FLAG, TRANSACTION_DATE
having count(*) > 1); 

 

 

API to create New Item Oracle EBS R12

 Create Custom Table : XX_ITEM_INTERFACE_STG_DEBUG

 Insert Item information:

SET DEFINE OFF;
Insert into XX_ITEM_INTERFACE_STG_DEBUG
   (SEGMENT1, DESCRIPTION, PRIMARY_UOM_CODE, PROCESS_QUALITY_ENABLED_FLAG, PROCESS_COSTING_ENABLED_FLAG,
    RECIPE_ENABLED_FLAG, PROCESS_EXECUTION_ENABLED_FLAG, I_STSTAUS)
 Values
   ('0628411080', 'TRANSFORMER CAPACITY-220 VA', 'PCS', 'Y', 'Y',
    'Y', 'Y', NULL);
COMMIT;
 

Run Procedure as bellow :

 /* Cursor Declaration ***/
declare
cursor c1 is select * from XX_ITEM_INTERFACE_STG_DEBUG ;

/* Variable Declaration **/

l_org_id                 number(3);
l_uom                    varchar2(15);
l_suom                   varchar2(15);
l_item_type             varchar2(30);
l_desc                     varchar2(30);
l_temp_id                varchar2(3);
l_sale_ac                 number(16);
l_exp_ac                  number(16);
l_cost_sale_ac          number(16);
l_item_number         varchar2(100);
l_flag                       varchar2(4) default'A';
l_msg                      varchar2(200);
begin
    --delete from MTL_SYSTEM_ITEMS_INTERFACE;
    --commit;
    
    for x1 in c1 loop
        /* Validation For Organization  */
--        begin
--            select  organization_id into l_org_id
--            from    ORG_ORGANIZATION_DEFINITIONS
--            where   organization_code = x1.organization_code;
--        exception
--            when others then
--            l_flag :='E';
--            l_msg :='Item Interface'||'Organization Id'||l_org_id||' not in system';
--            fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
--        end;
        
        /* Validation for Item Number */
        
         if  x1.SEGMENT1 is null then
                l_flag :='E';
                l_msg :='Item number should not be NULL';
                fnd_file.PUT_LINE(fnd_file.log,'Error Occured--'||l_msg);
            end if;
        /* Validation for Description */
        
        if x1.DESCRIPTION is null then
            l_flag :='E';
            l_msg :='Item description should not be NULL';
            fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg|| x1.SEGMENT1);
       end if;
       
       /* Validation for Unit Of Measure */
       
       if x1.PRIMARY_UOM_CODE is not null then
            begin
                select UOM_CODE into l_uom
                from mtl_units_of_measure
                where UOM_CODE =x1.PRIMARY_UOM_CODE;
            exception
            when others then
            l_flag :='E';
            l_msg := x1.SEGMENT1||'---'||l_uom ||'UOM is not Valid';
            fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
            end;    
       else
            l_msg :='Primary UOM should not be NULL ';
            fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
       end if;
       
            
      begin
            select COST_OF_SALES_ACCOUNT into l_cost_sale_ac
            from  MTL_PARAMETERS
            where ORGANIZATION_ID = 102;
      exception
            when no_data_found then
                l_flag :='E';
                l_msg :='Cost of Sales Account should not be Null';
                fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
            when others then
            l_flag :='E';
            l_msg :='Cost of Sales Account is not Valid';
            fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
            end;    
            
      begin
            select SALES_ACCOUNT into l_sale_ac
            from  MTL_PARAMETERS
             where ORGANIZATION_ID = 102;
             
      exception
            when no_data_found then
                l_flag :='E';
                l_msg :='Sales Account should not be Null';
                fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
            when others then
            l_flag :='E';
            l_msg :='Sales Account is not Valid';
            fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
            end;
            
      begin
           
       select EXPENSE_ACCOUNT into l_exp_ac
            from  MTL_PARAMETERS
              where ORGANIZATION_ID = 102;
              
      exception
            when no_data_found then
                l_flag :='E';
                l_msg :='Expence Account  should not be Null';
                fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
            when others then
            l_flag :='E';
            l_msg :='Expence  Account is not Valid';
            fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg);
            end;      
                       
      if l_flag!='E' then
        insert into MTL_SYSTEM_ITEMS_INTERFACE
        (   ORGANIZATION_ID,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            DESCRIPTION,
            SEGMENT1,
            PRIMARY_UOM_CODE,
            TEMPLATE_ID,
            SUMMARY_FLAG,
            ENABLED_FLAG,
            SALES_ACCOUNT,
            COST_OF_SALES_ACCOUNT,
            EXPENSE_ACCOUNT,
            PROCESS_QUALITY_ENABLED_FLAG,
            PROCESS_COSTING_ENABLED_FLAG,
            RECIPE_ENABLED_FLAG,
            PROCESS_EXECUTION_ENABLED_FLAG,
            RESTRICT_SUBINVENTORIES_CODE,
            RESTRICT_LOCATORS_CODE ,
            LOCATION_CONTROL_CODE ,
            SET_PROCESS_ID,
            PROCESS_FLAG,
            TRANSACTION_TYPE)
        values( 102,
                sysdate,
                fnd_global.USER_ID,
                sysdate,
                fnd_global.USER_ID,
                fnd_global.LOGIN_ID,
                x1.DESCRIPTION,
                x1.SEGMENT1,
                l_uom,
                  --  l_item_type,
               2,
                'Y',
               'Y',
                l_sale_ac,
                l_cost_sale_ac,
                l_exp_ac,
               x1.PROCESS_QUALITY_ENABLED_FLAG,
               x1.PROCESS_COSTING_ENABLED_FLAG,
               x1.RECIPE_ENABLED_FLAG,
               x1.PROCESS_EXECUTION_ENABLED_FLAG,
               1,
               1,
               2,
                2,
                1,
                'CREATE'
              );
      else
             update XX_ITEM_INTERFACE_STG_DEBUG set I_STSTAUS=l_msg where SEGMENT1= x1.SEGMENT1;
      end if;
      end loop;
      
         
      commit;
end;
/

 

Then run Import Item program.

Transfer Price Update R12

 CREATE OR REPLACE PROCEDURE APPS.PRG_COSTING_PERIOD_ALL_NEW_MK
(
  errbuff   OUT   VARCHAR2,
  retcode   OUT   NUMBER,
  PERIODID IN NUMBER
  --mmm VARCHAR2
)
is

--// ******************* created for Inv Periods Open as per OU ***********************************************
--//                            Created By : Mahfuz
--//                            Dated : 14 Sep 2014
--//                            Updated : 01 Sep 2020 , include STL
--//           ICS/PML, DAIRY, SPL, STL, SAL, PABL and SBCL
--//                            Updated on : 22 Nov 2018 by Mahfuz for new org cancellation and Except eAM org
--// ******************* created by Mahfuz for Inv Periods Open ***********************************************

        ddt        number;
        Tddt       date;
          

        cursor c1 is
        
         select o.ORGANIZATION_ID P_OrgID from org_organization_definitions o, mtl_parameters p where o.ORGANIZATION_ID  not in (370,1550,1650,1651)
  and o.ORGANIZATION_ID=p.ORGANIZATION_ID and p.PROCESS_ENABLED_FLAG='Y' and DISABLE_DATE is null;


        c1_hd c1%rowtype;

        begin

        DELETE  xx_update_transfer_price_m;
        commit;

 INSERT INTO xx_update_transfer_price_m
               (transaction_id, inventory_item_id, organization_id,
                transfer_price, new_transfer_price,
                transfer_organization_id, xx_transfer_price,
                xx_process_flag, xx_period_id, process_status, type_ind
               )
        VALUES (periodid, periodid, periodid,
                0, 0,
                periodid, 0,
                100, periodid, 'RUNNING', 'START'
               );

   COMMIT;




   -- Start ME -------------------
   INSERT INTO xx_update_transfer_price_m
      SELECT DISTINCT mtl.transaction_id, mtl.inventory_item_id,
                      mtl.organization_id, orgto.organization_code toorgcode,
                      orgto.organization_name toorgname, mtl.transfer_price,
                      0 new_transfer_price, mtl.transfer_organization_id,
                      orgfrm1.organization_code fromorgcode,
                      orgfrm1.organization_name fromorgname,
                      mtl.transfer_price tp, 0 xx_process_flag,
                      periodid xx_period_id, NULL, cmpnt_cost, 'NA' item_code,
                      'ME2OPM' type_ind
                 FROM mtl_material_transactions mtl,
                      org_organization_definitions orgfrm1         -- From org
                                                          ,
                      org_organization_definitions orgto            --  To org
                                                        ,
                      (SELECT inventory_item_id, item_cost cmpnt_cost,
                              organization_id, periodid period_id
                         FROM cst_item_costs
                        WHERE item_cost <> 0) cst,
                      mtl_parameters op
                WHERE mtl.source_code = 'RCV'
                  AND TRUNC (mtl.transaction_date)
                         BETWEEN (SELECT TRUNC (start_date)
                                    FROM gmf_period_statuses
                                   WHERE period_id = periodid
                                                             --    v_period_id
                      )
                             AND (SELECT TRUNC (end_date)
                                    FROM gmf_period_statuses
                                   WHERE period_id = periodid)
                  AND mtl.transaction_action_id = 12
                  AND mtl.transfer_organization_id = orgfrm1.organization_id
                  AND mtl.organization_id = orgto.organization_id
                  AND orgto.organization_id = op.organization_id
                  AND op.process_enabled_flag = 'Y'
                  --and orgTo.ORGANIZATION_NAME not like '%-ME-%'
                  --and orgFrm1.OPERATING_UNIT<>orgTo.OPERATING_UNIT
                  AND mtl.transfer_organization_id = cst.organization_id
                  AND mtl.inventory_item_id = cst.inventory_item_id
                  AND (   mtl.transfer_price <> cst.cmpnt_cost
                     OR cst.cmpnt_cost IS NULL
                   );

   COMMIT;

   DECLARE
      CURSOR c_mmt
      IS
         SELECT *
           FROM xx_update_transfer_price_m
          WHERE transaction_id = periodid
            AND xx_process_flag = 100
            AND process_status = 'RUNNING';

      h_mmt   c_mmt%ROWTYPE;
   BEGIN
      OPEN c_mmt;

      LOOP
         FETCH c_mmt
          INTO h_mmt;

         EXIT WHEN c_mmt%NOTFOUND;

         -------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE---------------------------
         DECLARE
            CURSOR c_mmtz
            IS
               SELECT *
                 FROM xx_update_transfer_price_m
                WHERE process_status IS NULL
                  AND cmpnt_cost <> 0
                  AND type_ind = 'ME2OPM';

             --where TRANSACTION_ID = 1227428
            -- order by TRANSACTION_ID;
            h_mmtz   c_mmtz%ROWTYPE;
         BEGIN
            OPEN c_mmtz;

            LOOP
               FETCH c_mmtz
                INTO h_mmtz;

               EXIT WHEN c_mmtz%NOTFOUND;

               UPDATE mtl_material_transactions
                  SET transfer_price = h_mmtz.cmpnt_cost,
                      actual_cost = NULL,
                      transaction_cost = 0
                WHERE mtl_material_transactions.transaction_id =
                                                         h_mmtz.transaction_id;

               UPDATE xx_update_transfer_price_m
                  SET xx_process_flag = 990,  xx_transfer_price = h_mmtz.cmpnt_cost,
                      process_status = 'COMPLETED'
                WHERE transaction_id = h_mmtz.transaction_id;

               COMMIT;
            END LOOP;
         EXCEPTION
            WHEN OTHERS
            THEN
               NULL;

               CLOSE c_mmtz;
         END;

----------------------------------
         COMMIT;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;

         CLOSE c_mmt;
   END;                  -------------------------- END ME -------------------




   --------Start SAL ----------------------------
   INSERT INTO xx_update_transfer_price_m
      SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
             o.organization_code, o.organization_name, mtl.transfer_price,
             0 new_transfer_price, mtl.transfer_organization_id,
             orgto.organization_code toorgcode,
             orgto.organization_name toorgname, mtl.transfer_price tp,
             0 xx_process_flag, periodid xx_period_id, NULL,     -- CMPNT_COST
             c.sales_price cmpnt_cost, i.segment1, 'SAL' type_ind
        --,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
      FROM   mpfl_sales_price_ics c,
             org_organization_definitions o,
             mtl_system_items_b i,
             mtl_material_transactions mtl,
             org_organization_definitions orgto
       WHERE c.org_code = o.operating_unit
         AND c.item_code = i.segment1
         AND i.organization_id = o.organization_id
         AND c.proc_type = 'SAL'
         AND mtl.source_code = 'RCV'
         --and orgTo.ORGANIZATION_CODE in ('415','416','417')
         AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid
                                                                              --    v_period_id
                                          )
                                              AND (SELECT TRUNC (end_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid)
         AND mtl.transaction_action_id = 12
         AND mtl.transfer_organization_id = o.organization_id
         AND mtl.inventory_item_id = i.inventory_item_id
         AND mtl.organization_id = orgto.organization_id
         AND o.operating_unit <> orgto.operating_unit
           AND orgto.operating_unit not in (148);

   COMMIT;

   --FOR i IN 1 .. 1 LOOP

   -------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
   DECLARE
      CURSOR c_mmtz_sal
      IS
         SELECT *
           FROM xx_update_transfer_price_m
          WHERE process_status IS NULL AND cmpnt_cost <> 0
                AND type_ind = 'SAL';

      --  AND item_code = '0273015700';

      --where TRANSACTION_ID = 1227428
      -- order by TRANSACTION_ID;
      h_mmtz_sal   c_mmtz_sal%ROWTYPE;
   BEGIN
      OPEN c_mmtz_sal;

      LOOP
         FETCH c_mmtz_sal
          INTO h_mmtz_sal;

         EXIT WHEN c_mmtz_sal%NOTFOUND;

         UPDATE mtl_material_transactions
            SET transfer_price = h_mmtz_sal.cmpnt_cost,
                actual_cost = NULL,
                transaction_cost = 0
          WHERE mtl_material_transactions.transaction_id =
                                                     h_mmtz_sal.transaction_id;

         UPDATE xx_update_transfer_price_m
            SET xx_process_flag = 990,
                process_status = 'COMPLETED',
                xx_transfer_price = h_mmtz_sal.cmpnt_cost
          WHERE transaction_id = h_mmtz_sal.transaction_id;

         COMMIT;
      END LOOP;
    EXCEPTION
      WHEN OTHERS
      THEN
         NULL;

         CLOSE c_mmtz_sal;
   END;                           ----------END SAL --------------------------



   --------Start SPL ----------------------------
   INSERT INTO xx_update_transfer_price_m
      SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
             o.organization_code, o.organization_name, mtl.transfer_price,
             0 new_transfer_price, mtl.transfer_organization_id,
             orgto.organization_code toorgcode,
             orgto.organization_name toorgname, mtl.transfer_price tp,
             0 xx_process_flag, periodid xx_period_id, NULL,     -- CMPNT_COST
             c.sales_price cmpnt_cost, i.segment1, 'SPL' type_ind
        --,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
      FROM   mpfl_sales_price_ics c,
             org_organization_definitions o,
             mtl_system_items_b i,
             mtl_material_transactions mtl,
             org_organization_definitions orgto
       WHERE c.org_code = o.operating_unit
         AND c.item_code = i.segment1
         AND i.organization_id = o.organization_id
         AND c.proc_type = 'SPL'
         AND mtl.source_code = 'RCV'
         --and orgTo.ORGANIZATION_CODE in ('415','416','417')
         AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid
                                                                              --    v_period_id
                                          )
                                              AND (SELECT TRUNC (end_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid)
         AND mtl.transaction_action_id = 12
         AND mtl.transfer_organization_id = o.organization_id
         AND mtl.inventory_item_id = i.inventory_item_id
         AND mtl.organization_id = orgto.organization_id
         AND o.operating_unit <> orgto.operating_unit
           AND orgto.operating_unit not in (148);

   COMMIT;

   --FOR i IN 1 .. 1 LOOP

   -------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
   DECLARE
      CURSOR c_mmtz_SPL
      IS
         SELECT *
           FROM xx_update_transfer_price_m
          WHERE process_status IS NULL AND cmpnt_cost <> 0
                AND type_ind = 'SPL';

      --  AND item_code = '0273015700';

      --where TRANSACTION_ID = 1227428
      -- order by TRANSACTION_ID;
      h_mmtz_SPL   c_mmtz_SPL%ROWTYPE;
   BEGIN
      OPEN c_mmtz_SPL;

      LOOP
         FETCH c_mmtz_SPL
          INTO h_mmtz_SPL;

         EXIT WHEN c_mmtz_SPL%NOTFOUND;

         UPDATE mtl_material_transactions
            SET transfer_price = h_mmtz_SPL.cmpnt_cost,
                actual_cost = NULL,
                transaction_cost = 0
          WHERE mtl_material_transactions.transaction_id =
                                                     h_mmtz_SPL.transaction_id;

         UPDATE xx_update_transfer_price_m
            SET xx_process_flag = 990,
                process_status = 'COMPLETED',
                xx_transfer_price = h_mmtz_SPL.cmpnt_cost
          WHERE transaction_id = h_mmtz_SPL.transaction_id;

         COMMIT;
      END LOOP;
    EXCEPTION
      WHEN OTHERS
      THEN
         NULL;

         CLOSE c_mmtz_SPL;
   END;                           ----------END SPL --------------------------







   --------Start STL ----------------------------
   INSERT INTO xx_update_transfer_price_m
      SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
             o.organization_code, o.organization_name, mtl.transfer_price,
             0 new_transfer_price, mtl.transfer_organization_id,
             orgto.organization_code toorgcode,
             orgto.organization_name toorgname, mtl.transfer_price tp,
             0 xx_process_flag, periodid xx_period_id, NULL,     -- CMPNT_COST
             c.sales_price cmpnt_cost, i.segment1, 'STL' type_ind
        --,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
      FROM   mpfl_sales_price_ics c,
             org_organization_definitions o,
             mtl_system_items_b i,
             mtl_material_transactions mtl,
             org_organization_definitions orgto
       WHERE c.org_code = o.operating_unit
         AND c.item_code = i.segment1
         AND i.organization_id = o.organization_id
         AND c.proc_type = 'STL'
         AND mtl.source_code = 'RCV'
         --and orgTo.ORGANIZATION_CODE in ('415','416','417')
         AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid
                                                                              --    v_period_id
                                          )
                                              AND (SELECT TRUNC (end_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid)
         AND mtl.transaction_action_id = 12
         AND mtl.transfer_organization_id = o.organization_id
         AND mtl.inventory_item_id = i.inventory_item_id
         AND mtl.organization_id = orgto.organization_id
         AND o.operating_unit <> orgto.operating_unit
           AND orgto.operating_unit not in (148);

   COMMIT;

   --FOR i IN 1 .. 1 LOOP

   -------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
   DECLARE
      CURSOR c_mmtz_STL
      IS
         SELECT *
           FROM xx_update_transfer_price_m
          WHERE process_status IS NULL AND cmpnt_cost <> 0
                AND type_ind = 'STL';

      --  AND item_code = '0273015700';

      --where TRANSACTION_ID = 1227428
      -- order by TRANSACTION_ID;
      h_mmtz_STL   c_mmtz_STL%ROWTYPE;
   BEGIN
      OPEN c_mmtz_STL;

      LOOP
         FETCH c_mmtz_STL
          INTO h_mmtz_STL;

         EXIT WHEN c_mmtz_STL%NOTFOUND;

         UPDATE mtl_material_transactions
            SET transfer_price = h_mmtz_STL.cmpnt_cost,
                actual_cost = NULL,
                transaction_cost = 0
          WHERE mtl_material_transactions.transaction_id =
                                                     h_mmtz_STL.transaction_id;

         UPDATE xx_update_transfer_price_m
            SET xx_process_flag = 990,
                process_status = 'COMPLETED',
                xx_transfer_price = h_mmtz_STL.cmpnt_cost
          WHERE transaction_id = h_mmtz_STL.transaction_id;

         COMMIT;
      END LOOP;
    EXCEPTION
      WHEN OTHERS
      THEN
         NULL;

         CLOSE c_mmtz_STL;
   END;                           ----------END STL --------------------------




   --------Start SBCL ----------------------------
   INSERT INTO xx_update_transfer_price_m
      SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
             o.organization_code, o.organization_name, mtl.transfer_price,
             0 new_transfer_price, mtl.transfer_organization_id,
             orgto.organization_code toorgcode,
             orgto.organization_name toorgname, mtl.transfer_price tp,
             0 xx_process_flag, periodid xx_period_id, NULL,     -- CMPNT_COST
             c.sales_price cmpnt_cost, i.segment1, 'SBCL' type_ind
        --,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
      FROM   mpfl_sales_price_ics c,
             org_organization_definitions o,
             mtl_system_items_b i,
             mtl_material_transactions mtl,
             org_organization_definitions orgto
       WHERE c.org_code = o.operating_unit
         AND c.item_code = i.segment1
         AND i.organization_id = o.organization_id
         AND c.proc_type = 'SBCL'
         AND mtl.source_code = 'RCV'
         --and orgTo.ORGANIZATION_CODE in ('415','416','417')
         AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid
                                                                              --    v_period_id
                                          )
                                              AND (SELECT TRUNC (end_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid)
         AND mtl.transaction_action_id = 12
         AND mtl.transfer_organization_id = o.organization_id
         AND mtl.inventory_item_id = i.inventory_item_id
         AND mtl.organization_id = orgto.organization_id
         AND o.operating_unit <> orgto.operating_unit
           AND orgto.operating_unit not in (148);

   COMMIT;

   --FOR i IN 1 .. 1 LOOP

   -------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
   DECLARE
      CURSOR c_mmtz_SBCL
      IS
         SELECT *
           FROM xx_update_transfer_price_m
          WHERE process_status IS NULL AND cmpnt_cost <> 0
                AND type_ind = 'SBCL';

      --  AND item_code = '0273015700';

      --where TRANSACTION_ID = 1227428
      -- order by TRANSACTION_ID;
      h_mmtz_SBCL   c_mmtz_SBCL%ROWTYPE;
   BEGIN
      OPEN c_mmtz_SBCL;

      LOOP
         FETCH c_mmtz_SBCL
          INTO h_mmtz_SBCL;

         EXIT WHEN c_mmtz_SBCL%NOTFOUND;

         UPDATE mtl_material_transactions
            SET transfer_price = h_mmtz_SBCL.cmpnt_cost,
                actual_cost = NULL,
                transaction_cost = 0
          WHERE mtl_material_transactions.transaction_id =
                                                     h_mmtz_SBCL.transaction_id;

         UPDATE xx_update_transfer_price_m
            SET xx_process_flag = 990,
                process_status = 'COMPLETED',
                xx_transfer_price = h_mmtz_SBCL.cmpnt_cost
          WHERE transaction_id = h_mmtz_SBCL.transaction_id;

         COMMIT;
      END LOOP;
    EXCEPTION
      WHEN OTHERS
      THEN
         NULL;

         CLOSE c_mmtz_SBCL;
   END;                           ----------END SBCL --------------------------








   --------Start PABL ----------------------------
   INSERT INTO xx_update_transfer_price_m
      SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
             o.organization_code, o.organization_name, mtl.transfer_price,
             0 new_transfer_price, mtl.transfer_organization_id,
             orgto.organization_code toorgcode,
             orgto.organization_name toorgname, mtl.transfer_price tp,
             0 xx_process_flag, periodid xx_period_id, NULL,     -- CMPNT_COST
             c.sales_price cmpnt_cost, i.segment1, 'PABL' type_ind
        --,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
      FROM   mpfl_sales_price_ics c,
             org_organization_definitions o,
             mtl_system_items_b i,
             mtl_material_transactions mtl,
             org_organization_definitions orgto
       WHERE c.org_code = o.operating_unit
         AND c.item_code = i.segment1
         AND i.organization_id = o.organization_id
         AND c.proc_type = 'PABL'
         AND mtl.source_code = 'RCV'
         --and orgTo.ORGANIZATION_CODE in ('415','416','417')
         AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid
                                                                              --    v_period_id
                                          )
                                              AND (SELECT TRUNC (end_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid)
         AND mtl.transaction_action_id = 12
         AND mtl.transfer_organization_id = o.organization_id
         AND mtl.inventory_item_id = i.inventory_item_id
         AND mtl.organization_id = orgto.organization_id
         AND o.operating_unit <> orgto.operating_unit
           AND orgto.operating_unit not in (148);

   COMMIT;

   --FOR i IN 1 .. 1 LOOP

   -------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
   DECLARE
      CURSOR c_mmtz_pabl
      IS
         SELECT *
           FROM xx_update_transfer_price_m
          WHERE process_status IS NULL AND cmpnt_cost <> 0
                AND type_ind = 'PABL';

      --  AND item_code = '0273015700';

      --where TRANSACTION_ID = 1227428
      -- order by TRANSACTION_ID;
      h_mmtz_pabl   c_mmtz_pabl%ROWTYPE;
   BEGIN
      OPEN c_mmtz_pabl;

      LOOP
         FETCH c_mmtz_pabl
          INTO h_mmtz_pabl;

         EXIT WHEN c_mmtz_pabl%NOTFOUND;

         UPDATE mtl_material_transactions
            SET transfer_price = h_mmtz_pabl.cmpnt_cost,
                actual_cost = NULL,
                transaction_cost = 0
          WHERE mtl_material_transactions.transaction_id =
                                                     h_mmtz_pabl.transaction_id;

         UPDATE xx_update_transfer_price_m
            SET xx_process_flag = 990,
                process_status = 'COMPLETED',
                xx_transfer_price = h_mmtz_pabl.cmpnt_cost
          WHERE transaction_id = h_mmtz_pabl.transaction_id;

         COMMIT;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;

         CLOSE c_mmtz_pabl;
   END;                           ----------END PABL --------------------------






--------Start ICS -----------------------------------
   INSERT INTO xx_update_transfer_price_m
      SELECT DISTINCT mtl.transaction_id, mtl.inventory_item_id,
                      mtl.organization_id, orgto.organization_code toorgcode,
                      orgto.organization_name toorgname, mtl.transfer_price,
                      0 new_transfer_price, mtl.transfer_organization_id,
                      orgfrm1.organization_code fromorgcode,
                      orgfrm1.organization_name fromorgname,
                      mtl.transfer_price tp, 0 xx_process_flag,
                      periodid xx_period_id, NULL, cmpnt_cost, item_code,
                      'ICS' type_ind
                 FROM mtl_material_transactions mtl,
                      org_organization_definitions orgfrm1         -- From org
                                                          ,
                      org_organization_definitions orgto            --  To org
                                                        ,
                      (SELECT i.segment1 item_code, i.inventory_item_id,
                              sales_price cmpnt_cost, i.segment1,
                              i.organization_id
                         FROM mpfl_sales_price_ics c,
                              org_organization_definitions o,
                              mtl_system_items_b i
                        WHERE c.org_code = o.organization_code
                          AND c.item_code = i.segment1
                          AND i.organization_id = o.organization_id
                          AND proc_type = 'ICS') cst
                WHERE mtl.source_code = 'RCV'
                  AND orgto.organization_code IN ('415', '416', '417')
                  AND TRUNC (mtl.transaction_date)
                         BETWEEN (SELECT TRUNC (start_date)
                                    FROM gmf_period_statuses
                                   WHERE period_id = periodid
                                                             --    v_period_id
                      )
                             AND (SELECT TRUNC (end_date)
                                    FROM gmf_period_statuses
                                   WHERE period_id = periodid)
                  AND mtl.transaction_action_id = 12
                  AND mtl.transfer_organization_id = orgfrm1.organization_id
                  AND mtl.organization_id = orgto.organization_id
                  AND orgfrm1.operating_unit <> orgto.operating_unit
                  AND orgto.operating_unit not in (148)
                  AND mtl.organization_id = cst.organization_id
                  AND mtl.inventory_item_id = cst.inventory_item_id;
                  
                    COMMIT;  
                  -----------------------------------------------
                 
                  -------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
   DECLARE
      CURSOR c_mmtz_ics
      IS
         SELECT *
           FROM xx_update_transfer_price_m
          WHERE process_status IS NULL AND type_ind = 'ICS';

      h_mmtz_ics   c_mmtz_ics%ROWTYPE;
   BEGIN
      OPEN c_mmtz_ics;

      LOOP
         FETCH c_mmtz_ics
          INTO h_mmtz_ics;

         EXIT WHEN c_mmtz_ics%NOTFOUND;

         UPDATE mtl_material_transactions
            SET transfer_price = h_mmtz_ics.cmpnt_cost,
                actual_cost = NULL,
                transaction_cost = 0
          WHERE mtl_material_transactions.transaction_id =
                                                     h_mmtz_ics.transaction_id;

         UPDATE xx_update_transfer_price_m
            SET xx_process_flag = 990,
                process_status = 'COMPLETED',
                xx_transfer_price = h_mmtz_ics.cmpnt_cost
          WHERE transaction_id = h_mmtz_ics.transaction_id;

         COMMIT;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;

         CLOSE c_mmtz_ics;
   END;

------------------------------------

   --END LOOP;
   COMMIT;
   
          INSERT INTO xx_update_transfer_price_m
          SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
             o.organization_code, o.organization_name, mtl.transfer_price,
             0 new_transfer_price, mtl.transfer_organization_id,
             orgto.organization_code toorgcode,
             orgto.organization_name toorgname, mtl.transfer_price tp,
             0 xx_process_flag, periodid xx_period_id, NULL,     -- CMPNT_COST
             c.sales_price cmpnt_cost, i.segment1, 'ICSNEW' type_ind
        --,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
      FROM   mpfl_sales_price_ics c,
             org_organization_definitions o,
             mtl_system_items_b i,
             mtl_material_transactions mtl,
             org_organization_definitions orgto
       WHERE --c.org_code
       o.operating_unit IN (133,135,141,432,433,1087)       
       --= o.operating_unit
         AND c.item_code = i.segment1
         AND i.organization_id = o.organization_id
         AND c.proc_type = 'ICS'
         AND mtl.source_code = 'RCV'
         --and orgTo.ORGANIZATION_CODE in ('415','416','417')
         AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid
                                                                              --    v_period_id
                                          )
                                              AND (SELECT TRUNC (end_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid)
         AND mtl.transaction_action_id = 12
         AND mtl.transfer_organization_id = o.organization_id
         AND mtl.inventory_item_id = i.inventory_item_id
         AND mtl.organization_id = orgto.organization_id
         AND o.operating_unit <> orgto.operating_unit
         AND orgto.operating_unit not in (148);
         
    COMMIT;
         
    
     -------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
   DECLARE
      CURSOR c_mmtz_icsnew
      IS
         SELECT *
           FROM xx_update_transfer_price_m
          WHERE process_status IS NULL AND type_ind = 'ICSNEW';


      h_mmtz_icsnew   c_mmtz_icsnew%ROWTYPE;
   BEGIN
      OPEN c_mmtz_icsnew;

      LOOP
         FETCH c_mmtz_icsnew
          INTO h_mmtz_icsnew;

         EXIT WHEN c_mmtz_icsnew%NOTFOUND;

         UPDATE mtl_material_transactions
            SET transfer_price = h_mmtz_icsnew.cmpnt_cost,
                actual_cost = NULL,
                transaction_cost = 0
          WHERE mtl_material_transactions.transaction_id =
                                                     h_mmtz_icsnew.transaction_id;

         UPDATE xx_update_transfer_price_m
            SET xx_process_flag = 990,
                process_status = 'COMPLETED',
                xx_transfer_price = h_mmtz_icsnew.cmpnt_cost
          WHERE transaction_id = h_mmtz_icsnew.transaction_id;

         COMMIT;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;

         CLOSE c_mmtz_icsnew;
   END;



--------Start DAIRY ----------------------------
   INSERT INTO xx_update_transfer_price_m
   
   
      SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
             o.organization_code, o.organization_name, mtl.transfer_price,
             0 new_transfer_price, mtl.transfer_organization_id,
             orgto.organization_code toorgcode,
             orgto.organization_name toorgname, mtl.transfer_price tp,
             0 xx_process_flag, periodid xx_period_id, NULL,     -- CMPNT_COST
             c.sales_price cmpnt_cost, i.segment1, 'DAIRY' type_ind
        --,o.OPERATING_UNIT fromou, orgto.OPERATING_UNIT toou, orgto.ORGANIZATION_CODE, orgto.ORGANIZATION_NAME
      FROM   mpfl_sales_price_ics c,
             org_organization_definitions o,
             mtl_system_items_b i,
             mtl_material_transactions mtl,
             org_organization_definitions orgto
       WHERE c.org_code = o.operating_unit
         AND c.item_code = i.segment1
         AND i.organization_id = o.organization_id
         AND c.proc_type = 'DAIRY'
         AND mtl.source_code = 'RCV'
         --and orgTo.ORGANIZATION_CODE in ('415','416','417')
         AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid
                                                                              --    v_period_id
                                          )
                                              AND (SELECT TRUNC (end_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid)
         AND mtl.transaction_action_id = 12
         AND mtl.transfer_organization_id = o.organization_id
         AND mtl.inventory_item_id = i.inventory_item_id
         AND mtl.organization_id = orgto.organization_id
         AND o.operating_unit <> orgto.operating_unit
         AND orgto.operating_unit not in (148)
         
         ;

   COMMIT;

   --FOR i IN 1 .. 1 LOOP

   -------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
   DECLARE
      CURSOR c_mmtz_DAIRY
      IS
         SELECT *
           FROM xx_update_transfer_price_m
          WHERE process_status IS NULL AND cmpnt_cost <> 0
                AND type_ind = 'DAIRY';

      --  AND item_code = '0273015700';

      --where TRANSACTION_ID = 1227428
      -- order by TRANSACTION_ID;
      h_mmtz_DAIRY   c_mmtz_DAIRY%ROWTYPE;
   BEGIN
      OPEN c_mmtz_DAIRY;

      LOOP
         FETCH c_mmtz_DAIRY
          INTO h_mmtz_DAIRY;

         EXIT WHEN c_mmtz_DAIRY%NOTFOUND;

         UPDATE mtl_material_transactions
            SET transfer_price = h_mmtz_DAIRY.cmpnt_cost,
                actual_cost = NULL,
                transaction_cost = 0
          WHERE mtl_material_transactions.transaction_id =
                                                     h_mmtz_DAIRY.transaction_id;

         UPDATE xx_update_transfer_price_m
            SET xx_process_flag = 990,
                process_status = 'COMPLETED',
                xx_transfer_price = h_mmtz_DAIRY.cmpnt_cost
          WHERE transaction_id = h_mmtz_DAIRY.transaction_id;

         COMMIT;
      END LOOP;
    EXCEPTION
      WHEN OTHERS
      THEN
         NULL;

         CLOSE c_mmtz_DAIRY;
   END;                           ----------END DAIRY --------------------------

 --------------------ICS Inhouse -------------------------
          INSERT INTO xx_update_transfer_price_m
         SELECT mtl.transaction_id, i.inventory_item_id, i.organization_id,
             o.organization_code, o.organization_name, mtl.transfer_price,
             0 new_transfer_price, mtl.transfer_organization_id,
             orgto.organization_code toorgcode,
             orgto.organization_name toorgname, mtl.transfer_price tp,
             0 xx_process_flag, periodid xx_period_id, NULL,     -- CMPNT_COST
             c.sales_price cmpnt_cost, i.segment1, 'ICSINH' type_ind
      FROM   mpfl_sales_price_ics c,
             org_organization_definitions o,
             mtl_system_items_b i,
             mtl_material_transactions mtl,
             org_organization_definitions orgto
       WHERE  o.operating_unit IN (141)       
         AND c.item_code = i.segment1
         AND i.organization_id = o.organization_id
         AND c.proc_type = 'ICS'
         AND mtl.source_code = 'RCV'
         AND TRUNC (mtl.transaction_date) BETWEEN (SELECT TRUNC (start_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid
                                                                              --    v_period_id
                                          )
                                              AND (SELECT TRUNC (end_date)
                                                     FROM gmf_period_statuses
                                                    WHERE period_id = periodid)
         AND mtl.transaction_action_id = 12
         AND mtl.transfer_organization_id = o.organization_id
         AND mtl.inventory_item_id = i.inventory_item_id
         AND mtl.organization_id = orgto.organization_id
         AND  orgto.operating_unit in (135,433);
      COMMIT;

   -------------FOR FULL COST FROM GL AND UPDATE XX_UPDATE_TRANSFER_PRICE--------- --- mmt transfer price update-----
   DECLARE
      CURSOR c_mmtz_icsinh
      IS
         SELECT *
           FROM xx_update_transfer_price_m
          WHERE process_status IS NULL AND type_ind = 'ICSINH';


      h_mmtz_icsinh   c_mmtz_icsinh%ROWTYPE;
   BEGIN
      OPEN c_mmtz_icsinh;

      LOOP
         FETCH c_mmtz_icsinh
          INTO h_mmtz_icsinh;

         EXIT WHEN c_mmtz_icsinh%NOTFOUND;

         UPDATE mtl_material_transactions
            SET transfer_price = h_mmtz_icsinh.cmpnt_cost,
                actual_cost = NULL,
                transaction_cost = 0
          WHERE mtl_material_transactions.transaction_id =
                                                     h_mmtz_icsinh.transaction_id;

         UPDATE xx_update_transfer_price_m
            SET xx_process_flag = 990,
                process_status = 'COMPLETED',
                xx_transfer_price = h_mmtz_icsinh.cmpnt_cost
          WHERE transaction_id = h_mmtz_icsinh.transaction_id;

         COMMIT;
      END LOOP;
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;

         CLOSE c_mmtz_icsinh;
   END;


------------------------------------

   --END LOOP;
   COMMIT;
--------END ICS ---------------------------------------------

   --END LOOP;
   COMMIT;
   
  APPS.PRG_COSTING_PERIOD_ACROSS_MK(periodid);
 
   COMMIT;

        select to_number(to_char(to_date(sysdate,'dd-mm-yy'),'dd')) into ddt from dual;
        
        select to_date('01/'||TO_CHAR(ADD_MONTHS(to_date(sysdate,'dd-mm-yy'),2),'MM') || '/' || TO_CHAR(ADD_MONTHS(to_date(sysdate,'dd-mm-yy'),2),'YY'),'dd-mm-yy') into Tddt from dual
        ;
       
        if ddt>=26 then
            open c1;
            loop
                    Fetch c1 into c1_hd;
                    EXIT WHEN c1%NOTFOUND;
                    
                   
                 --   APPS.XX_ORG_ACCT_PERIODS(c1_hd.P_OrgID,Tddt);

                    commit;
            end loop;
            commit;
         --   APPS.mpfl_OldPeriod_cancelation();
            close c1;
            
         end if;
         
      
  EXCEPTION
   WHEN OTHERS
   THEN
      -- DBMS_OUTPUT.PUT_LINE('Error -'||SQLERRM);
      fnd_file.put_line (fnd_file.LOG, 'Error - ' || SQLERRM);
 END;
/