Costing Reports and Reconciliation in oracle Applications Costing or Inventory closing

Costing Reports and Reconciliation

When closing an accounting period the ‘Close accounting period’ concurrent process (INCTPC) is kicked off as well as the ‘Transfer transactions to GL’ concurrent process (INCTGL). The ‘Close accounting period’ process summarizes the costs related inventory and manufacturing activities for a given account period. The ‘Transfer transactions to GL’ process distributes those cost to the general ledger.



Oracle provides a variety of reports for evaluating current inventory quantity and value as well as reconciling inventory and manufacturing transaction to what has been transferred to GL for a given period. The following is a list of the costing reports that should reconcile if run with the correct parameters dependent on the organization set up:



Period Close Reconciliation Report:

(Available 11.5.10, Executable CSTRPCRE) This concurrent program and report is used to create summarized transaction records. It displays the differences between accounted value and inventory in the Discrepancy column. The inventory value is used as the baseline for calculation for the next period summarization values. The Period Close Reconciliation report can be run at any time during the period or automatically during the Period Close Process by setting the profile option CST:Period Summary to either automatic or manual. If it is generated for an open period, you are creating a simulation, or snapshot of the period. If the program is run for an accounting period that is not in a Closed status, the report reads directly from a temporary table, The simulation status is indicated in the report title. (See Note 295182.1)



Standard Costing Organization, PJM/WMS Not enabled:

-Period Close Value Summary

-Inventory Value Report

-Subinventory Account Value Report

-Elemental Inventory Value Report

-Transaction Historical Summary report



Average Costing Organization, PJM/WMS Not enabled:

-Period Close Value Summary (for organization level balance only)

-Elemental Inventory Value Report – Average Costing

-All Inventory Value Report – Average Costing

-Transaction Value Historical Summary-Average Costing



FIFO/LIFO Costing Organization, PJM/WMS Not enabled:

-Period Close Value Summary (if there are no unsummarized intransit balance or layer cost update, for organization level balance only)

-Elemental Inventory Value Report-Average Costing

-All Inventory Value Report-Average Costing

-Transaction Value Historical Summary-Average Costing



Standard Costing Organization, PJM/WMS enabled:

-Period Close Value Summary – Warehouse Management

-Inventory Value Report – Warehouse Management

-Cost Group Account Value Report

-Elemental Inventory Value Report – Warehouse Management

-Transaction Historical Summary Report (for organization level balance only) with option value ‘Roll back to first Date’ set to the last day of the current period.



Average Costing Organization, PJM/WMS enabled:

-Period Close Value Summary – Warehouse Management

-Elemental Inventory Value Report – Average Costing

-All Inventory Value Report-Average Costing

-Transaction Value Historical Summary-Average Costing (if there are no intransit transactions in another organization that affects the intransit quantity in this organization)



FIFO/LIFO Costing Organization, PJM/WMS enabled:

-Period Close Value Summary-Warehouse Management (if there are no un summarized intransit balance)

-Elemental Inventory Value Report-Average Costing

-All Inventory Value Report-Average Costing

-Transaction Value Historical Summary-Average Costing (if there is no intransit transactions in another organization that affects the intransit quantity in this organization)



Reconciling Reports



-When reconciling reports insure that the correct report is being used depending on the organizational set up. Some customers do use other report combinations to reconcile and in some cases this is acceptable.



-Make sure that the reports (other than the Period Close Value Summary (-Warehouse Management), the Transaction Historical Summary Report and the Transaction Value Historical Summary-Average Costing) are run after all the transactions in the current period are costed and before transactions in the subsequent period are created.



-Back Dating Transactions: Oracle does allow customer to back date transactions for their business needs. Customers need to understand that back dating transactions can affect the balance when doing reconciliation but reconciliation issues can be avoided when back dating transactions by utilizing the Cost Cut off field in the Organization Parameter screen insuring that back dated transactions are costed in the correct sequence. The issue with back dating transactions is explained in the “PERIOD CLOSE SUMMARY ALGORITHM” document available at the url:

Please Contact Oracle Support

To verify if there have been back dated transactions, the following script can be run to check the type of transaction the dates and the periods affected:



SELECT DISTINCT
MMT1.TRANSACTION_ID,
MMT1.TRANSACTION_DATE,
MCACD1.TRANSACTION_COSTED_DATE,
MMT2.TRANSACTION_ID,
MMT2.TRANSACTION_DATE,
MCACD2.TRANSACTION_COSTED_DATE,
MMT1.INVENTORY_ITEM_ID,
MMT1.COST_GROUP_ID
FROM ORG_ACCT_PERIODS OAP,
MTL_MATERIAL_TRANSACTIONS MMT1,
MTL_MATERIAL_TRANSACTIONS MMT2,
MTL_CST_ACTUAL_COST_DETAILS MCACD1,
MTL_CST_ACTUAL_COST_DETAILS MCACD2
WHERE OAP.ACCT_PERIOD_ID = &ACCT_PERIOD_ID
AND MMT1.TRANSACTION_DATE
BETWEEN OAP.PERIOD_START_DATE
AND OAP.SCHEDULE_CLOSE_DATE
AND MMT1.INVENTORY_ITEM_ID = MMT2.INVENTORY_ITEM_ID
AND MMT1.COST_GROUP_ID = MMT2.COST_GROUP_ID
AND MMT1.TRANSACTION_DATE < MMT2.TRANSACTION_DATE
AND MCACD1.TRANSACTION_ID = MMT1.TRANSACTION_ID
AND MCACD2.TRANSACTION_ID = MMT2.TRANSACTION_ID
AND MCACD1.TRANSACTION_COSTED_DATE >
MCACD2.TRANSACTION_COSTED_DATE
(RECEIPT TRANSACTION MAY APPEAR TO BE BACK DATED TRANSACTIONS BECAUSE THEY WILL NOT BE TIME STAMPED AND TRANSACTION_DATE WILL BE NULL)



-DATA CORRUPTION WHERE ONHAND QUANTITY DOES NOT MATCH WITH THE VALUES IN THE MTL_MATERIAL_TRANSACTIONS CAN ALSO CAUSE REPORTS NOT TO RECONCILE. THE FOLLOWING SCRIPTS CAN BE RUN IN AN 11.5.10 OR HIGHER ENVIRONMENT TO SEE IF THIS TYPE OF DATA CORRUPTION EXISTS:
SELECT TXN.INVENTORY_ITEM_ID,
TXN.MMT_QTY ,
ONHAND.QTY
FROM (SELECT MMT.INVENTORY_ITEM_ID ,
SUM(MMT.PRIMARY_QUANTITY) MMT_QTY,
MMT.ORGANIZATION_ID
FROM MTL_MATERIAL_TRANSACTIONS MMT,
MTL_SYSTEM_ITEMS MSI ,
MTL_SECONDARY_INVENTORIES MSE
WHERE MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MSI.INVENTORY_ASSET_FLAG = 'Y'
AND MSE.SECONDARY_INVENTORY_NAME= MMT.SUBINVENTORY_CODE
AND MSE.ASSET_INVENTORY = 1
AND MSE.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MMT.TRANSACTION_ACTION_ID NOT IN (5,6,24,30,50,51,52,55,26,7,11,17,10,9,13,14)
AND NVL(MMT.LOGICAL_TRANSACTION,0) <> 1
GROUP BY MMT.INVENTORY_ITEM_ID,
MMT.ORGANIZATION_ID
)
TXN ,
(SELECT MOQ.INVENTORY_ITEM_ID ,
SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY) QTY,
MOQ.ORGANIZATION_ID
FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ,
MTL_SYSTEM_ITEMS MSI ,
MTL_SECONDARY_INVENTORIES MSE
WHERE MSI.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MOQ.INVENTORY_ITEM_ID
AND MSI.INVENTORY_ASSET_FLAG = 'Y'
AND MSE.SECONDARY_INVENTORY_NAME= MOQ.SUBINVENTORY_CODE
AND MSE.ASSET_INVENTORY = 1
AND MSE.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
GROUP BY MOQ.INVENTORY_ITEM_ID,
MOQ.ORGANIZATION_ID
)
ONHAND,
MTL_PARAMETERS MP
WHERE TXN.INVENTORY_ITEM_ID = ONHAND.INVENTORY_ITEM_ID (+)
AND TXN.MMT_QTY <> NVL(ONHAND.QTY,0)
AND MP.ORGANIZATION_ID = &ORG_ID
AND TXN.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND ONHAND.ORGANIZATION_ID = TXN.ORGANIZATION_ID
AND NVL(MP.WMS_ENABLED_FLAG,'N') = 'N';

Bill of Materials - BOM Total Explosion Query in Oracle Applications

 SELECT TRIM (RPAD (' ', LEVEL + 1, '.')) || (LEVEL) assm_level,
                 LEVEL COMPONENT_LEVEL,
                 c.component_item_id,
                 (Select segment1 from apps.mtl_system_items_b msi where msi.inventory_item_id = c.component_item_id and organization_id = 87) component_name,
                 (Select description from apps.mtl_system_items_b msi where msi.inventory_item_id = c.component_item_id and organization_id = 87) component_description,
                 a.organization_id,
                 :P_ORG org_code,
                 c.ITEM_NUM assm_ITEM_NUM,
                 c.OPERATION_SEQ_NUM assm_OPERATION_SEQ_NUM,
                 c.EFFECTIVITY_DATE assm_EFFECTIVITY_DATE,
                 c.DISABLE_DATE assm_DISABLE_DATE,
                 c.COMPONENT_QUANTITY assm_COMPONENT_QUANTITY,
                 c.attribute6 assm_eco
            FROM (SELECT *
                    FROM apps.bom_inventory_components
                   WHERE TRUNC (SYSDATE) BETWEEN TRUNC (effectivity_date)
                                             AND TRUNC (
                                                    NVL (disable_date,
                                                         SYSDATE + 1))) c,
                 (SELECT *
                    FROM apps.bom_bill_of_materials
                   WHERE     alternate_bom_designator IS NULL
                   and organization_id = (Select organization_id from apps.mtl_parameters mp where  mp.organization_code = :P_ORG)
                    ) a
           WHERE 1 = 1 AND a.common_bill_sequence_id = c.bill_sequence_id
      START WITH a.assembly_item_id = (Select msi.inventory_item_id from apps.mtl_system_items_b msi where segment1 = :P_ITEM and organization_id = 87)
      CONNECT BY NOCYCLE PRIOR c.component_item_id = a.assembly_item_id;


_________________________________________________________________________________

    SELECT TRIM (RPAD (' ', LEVEL + 1, '.')) || (LEVEL) assm_level,
           LEVEL COMPONENT_LEVEL,
           c.component_item_id,
           a.organization_id,
           c.ITEM_NUM assm_ITEM_NUM,
           c.OPERATION_SEQ_NUM assm_OPERATION_SEQ_NUM,
           c.EFFECTIVITY_DATE assm_EFFECTIVITY_DATE,
           c.DISABLE_DATE assm_DISABLE_DATE,
           c.COMPONENT_QUANTITY assm_COMPONENT_QUANTITY,
           c.attribute6 assm_eco
      FROM (SELECT *
              FROM apps.bom_inventory_components
             WHERE TRUNC (SYSDATE) BETWEEN TRUNC (effectivity_date)
                                       AND TRUNC (
                                              NVL (disable_date, SYSDATE + 1))) c,
           (SELECT *
              FROM apps.bom_bill_of_materials
             WHERE alternate_bom_designator IS NULL --AND organization_id = 6047
             ) a,
           (SELECT msi.inventory_item_id, mp.organization_id
              FROM apps.mtl_system_items_b msi, apps.mtl_parameters mp
             WHERE     mp.organization_id = msi.organization_id
                   AND mp.organization_code = :P_ORG
                   AND msi.segment1 = :P_ITEM) item
     WHERE 1 = 1 AND a.organization_id = item.organization_id and a.common_bill_sequence_id = c.bill_sequence_id
START WITH a.assembly_item_id = item.inventory_item_id
CONNECT BY NOCYCLE PRIOR c.component_item_id = a.assembly_item_id;

BOM Bill of materials Implosion Query in Oracle Apps

SELECT TRIM (RPAD (' ', LEVEL + P_LEVEL + 1, '.')) || (LEVEL)
                    assm_level,
                 LEVEL + P_LEVEL COMPONENT_LEVEL,
                 c.component_item_id,
                 a.organization_id,
                 c.ITEM_NUM assm_ITEM_NUM,
                 c.OPERATION_SEQ_NUM assm_OPERATION_SEQ_NUM,
                 c.EFFECTIVITY_DATE assm_EFFECTIVITY_DATE,
                 c.DISABLE_DATE assm_DISABLE_DATE,
                 c.COMPONENT_QUANTITY assm_COMPONENT_QUANTITY,
                 (SELECT MAX (revision)
                    FROM mtl_item_revisions
                   WHERE     organization_id = P_ORG_id
                         AND EFFECTIVITY_DATE <= SYSDATE
                         AND inventory_item_id = a.assembly_item_id)
                    assm_item_rev,
                 a.assembly_item_id,
                 a.bill_sequence_id
            FROM (SELECT *
                    FROM apps.bom_inventory_components
                   WHERE TRUNC (SYSDATE) BETWEEN TRUNC (effectivity_date)
                                             AND TRUNC (
                                                    NVL (disable_date,
                                                         SYSDATE + 1))) c,
                 (SELECT *
                    FROM apps.bom_bill_of_materials
                   WHERE     alternate_bom_designator IS NULL
                         AND organization_id = P_ORG_id) a
           WHERE 1 = 1 AND a.bill_sequence_id = c.bill_sequence_id
      START WITH c.component_item_id = P_COMPONENT_ITEM_ID
      CONNECT BY NOCYCLE PRIOR a.assembly_item_id = c.component_item_id;

BOM Bill of Materials explosion query in BOM Oracle Apps

SELECT TRIM (RPAD (' ', LEVEL + 1, '.')) || (LEVEL) assm_level,
                 LEVEL COMPONENT_LEVEL,
                 c.component_item_id,
                 a.organization_id,
                 c.ITEM_NUM assm_ITEM_NUM,
                 c.OPERATION_SEQ_NUM assm_OPERATION_SEQ_NUM,
                 c.EFFECTIVITY_DATE assm_EFFECTIVITY_DATE,
                 c.DISABLE_DATE assm_DISABLE_DATE,
                 c.COMPONENT_QUANTITY assm_COMPONENT_QUANTITY,
                 (SELECT MAX (revision)
                    FROM mtl_item_revisions
                   WHERE     organization_id = P_ORGANIZATION_ID
                         AND EFFECTIVITY_DATE <= SYSDATE
                         AND inventory_item_id = c.COMPONENT_ITEM_ID)
                    assm_item_rev
            FROM (SELECT *
                    FROM apps.bom_inventory_components
                   WHERE TRUNC (SYSDATE) BETWEEN TRUNC (effectivity_date)
                                             AND TRUNC (
                                                    NVL (disable_date,
                                                         SYSDATE + 1))) c,
                 (SELECT *
                    FROM apps.bom_bill_of_materials
                   WHERE     alternate_bom_designator IS NULL
                         AND organization_id = P_organization_id) a
           WHERE 1 = 1 AND a.bill_sequence_id = c.bill_sequence_id
      START WITH a.assembly_item_id = P_ITEM_ID
      CONNECT BY NOCYCLE PRIOR c.component_item_id = a.assembly_item_id;

Bill of Materials - Exploding BOM using - bompxinq.exploder_userexit

THE MAIN purpose OF exploding BOM IS TO get THE details FROM THE TEMPORARY TABLE that oracle uses TO hold data.
Its SESSION based. So once you ARE OUT OF that SESSION THE DATA IS gone.
BOM_SMALL_EXPL_TEMP IS THE TABLE used TO hold THE DATA IN A session.
DATA cannot be retrived be querying THE VIEW FROM FORM --> HELP-->Record History.
Hence explode THE BOM FOR THE Item you needed data.
There ARE many ways  OF holding THE data.You can WRITE TO A FILE, PRINT report so on..Its upto you.
IN this post i used a table BOM_SMALL_EXPL_TEMP1 to hold the data.
Remember its not a temporary table, its a copy of BOM_SMALL_EXPL_TEMP with all the columns.
I too found it difficult to find the values to explode BOM.
So i have explored the standard oracle form to find out what columns its uses to explode.
 All the comments after assignments are the standard oracle Block.FieldName (Block Name -> B_BILL_OF_MATLS).
 So you can pass the values of your Appliction if needed.

/* Formatted on 6/10/2015 2:34:04 PM (QP5 v5.265.14096.37972) */

CREATE OR REPLACE PROCEDURE EAM_BOM_EXPLODE (p_item_id IN NUMBER) /* The BOM assembly which you want to explode */

IS

   v_group_id        NUMBER;

   x_error_message   VARCHAR2 (2000);

   x_error_code      NUMBER;

   sess_id           NUMBER;

   l_rec_count       NUMBER;

BEGIN

   -- fnd_global.apps_initialize (<>, <>, <>);

   DELETE FROM BOM_SMALL_EXPL_TEMP1;

   COMMIT;

   SELECT bom_explosion_temp_s.NEXTVAL INTO v_group_id FROM DUAL;

   SELECT bom_explosion_temp_session_s.NEXTVAL INTO sess_id FROM DUAL;

   bompxinq.exploder_userexit (Verify_Flag         => 0,

                               Org_Id              => 1213,

                               Order_By            => 1, --:B_Bill_Of_Matls.Bom_Bill_Sort_Order_Type,

                               Grp_Id              => v_group_id,

                               Session_Id          => 0,

                               Levels_To_Explode   => 20, --:B_Bill_Of_Matls.Levels_To_Explode,

                               Bom_Or_Eng          => 1, -- :Parameter.Bom_Or_Eng,

                               Impl_Flag           => 1, --:B_Bill_Of_Matls.Impl_Only,

                               Plan_Factor_Flag    => 2, --:B_Bill_Of_Matls.Planning_Percent,

                               Explode_Option      => 3, --:B_Bill_Of_Matls.Bom_Inquiry_Display_Type,

                               Module              => 2, --:B_Bill_Of_Matls.Costs,

                               Cst_Type_Id         => 0, --:B_Bill_Of_Matls.Cost_Type_Id,

                               Std_Comp_Flag       => 2,

                               Expl_Qty            => 1, --:B_Bill_Of_Matls.Explosion_Quantity,

                               Item_Id             => p_item_id, --:B_Bill_Of_Matls.Assembly_Item_Id,

                               Alt_Desg            => NULL, --:B_Bill_Of_Matls.Alternate_Bom_Designator,

                               Comp_Code           => NULL,

                               Unit_Number_From    => 0, --NVL(:B_Bill_Of_Matls.Unit_Number_From, :CONTEXT.UNIT_NUMBER_FROM),

                               Unit_Number_To      => 'ZZZZZZZZZZZZZZZZZ', --NVL(:B_Bill_Of_Matls.Unit_Number_To, :CONTEXT.UNIT_NUMBER_TO),

                               Rev_Date            => SYSDATE, --:B_Bill_Of_Matls.Disp_Date,

                               Show_Rev            => 1,                -- yes

                               Material_Ctrl       => 2, --:B_Bill_Of_Matls.Material_Control,

                               Lead_Time           => 2, --:B_Bill_Of_Matls.Lead_Time,

                               err_msg             => x_error_message, --err_msg

                               ERROR_CODE          => x_error_code); --error_code

   SELECT COUNT (*)

     INTO l_rec_count

     FROM                                            --BOM_EXPLOSION_TEMP temp

         BOM_SMALL_EXPL_TEMP temp

    WHERE temp.GROUP_ID = v_group_id;

   DBMS_OUTPUT.PUT_LINE ('l_rec_count = ' || l_rec_count);

   INSERT INTO BOM_SMALL_EXPL_TEMP1

      SELECT * FROM BOM_SMALL_EXPL_TEMP;

   COMMIT;

   DBMS_OUTPUT.PUT_LINE (x_error_message);

   DBMS_OUTPUT.PUT_LINE (x_error_code);

   DBMS_OUTPUT.PUT_LINE ('grp_id = ' || v_group_id);

   DBMS_OUTPUT.PUT_LINE ('sess_id = ' || sess_id);

END;

Once you ARE done compiling EXECUTE THE FOLLOWING command TO INSERT THE DATA INTO THE TABLE you have created IN THE above CASE its BOM_SMALL_EXPL_TEMP1.

BEGIN

   EAM_BOM_EXPLODE (p_item_id);

END;

/

SELECT * FROM BOM_SMALL_EXPL_TEMP1;

Bom Interfaces

Interface tables are :-
----------------------------
BOM_BILL_OF_MTLS_INTERFACE
BOM_INVENTORY_COMPS_INTERFACE

---MTL_INTERFACE_ERRORS(Error Message table)

Import Porgram :-
Bill and Routing Interface (Here parameter import routings will be no)
---------------------------------------------------------------------------------
BOM Sharing
--------------
Bill of material created for one organization can be share with other organizations.
Steps 1. Create the master bom and its inventory components
2. Create the same bom for other organizations with additional two fields i.e Common_Organization_Id and Common_Assembly_Item_Id. Here common_organization_id will be the master organization id and common assembly item id will be the master assembly item id(same assembly item id of sub org also).
If you are running Bill and Routing Interface for master and sub organization bom at the same time, there is possibility of throwing error if the master organization id is greater than sub organization id.
So better to run the Master bom first then run the child organizations.
--------------------------------------------------------------------------------

create the following staging table
CREATE TABLE XXX_BOM_BILL_MTLS_STG
(
ITEM_NAME VARCHAR2(50),
REVISION_NO CHAR(5),
ITEM_SEQ_BOM_COMP NUMBER(5),
OPERATION_SEQUENCE NUMBER(5),
BOM_COMPONENT VARCHAR2(50),
QUANTIY NUMBER(10,2),
YIELD_FACTOR NUMBER(10,2),
SUPPLY_TYPE VARCHAR2(10),
H_VERIFY_FLAG CHAR(1 BYTE),
L_VERIFY_FLAG CHAR(1),
ERROR_MESSAGE VARCHAR2(3000)
)
-------------------------------------------------------------------------------------
The script to upload data into interface tables
CREATE OR REPLACE procedure xxx_bom_bill_mtls_api
(errbuf varchar2,retcode varchar2) as
l_verify_flag char(1);
l_error_message varchar2(2500);
l_organization_id number(15);
l_inventory_item_id number(15);
l_component_item_id number(15);
l_bom_exists number(15);
l_wip_supply_type number(3);

CURSOR C_HEADER IS
select distinct item_name,revision_no
from xxx_bom_bill_mtls_stg
where nvl(h_verify_flag,'N') = 'N';

CURSOR C_LINES (p_item_name varchar2)IS
select *
from xxx_bom_bill_mtls_stg
where item_name = p_item_name
order by item_name,item_seq_bom_comp;

BEGIN

FOR C_BOM IN C_HEADER
LOOP

l_verify_flag := 'Y';
l_error_message := null;
l_bom_exists := null;

BEGIN
select organization_id
into l_organization_id
from org_organization_definitions
where organization_name = 'xxx Main Store';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Organization is not valid';
END;

BEGIN
select inventory_item_id
into l_inventory_item_id
from mtl_system_items_b
where organization_id = l_organization_id
and segment1'.'segment2'.'segment3'.'segment4
=trim(upper(c_bom.item_name));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message'Item is not valid';
END;

BEGIN
select assembly_item_id
into l_bom_exists
from BOM_BILL_OF_MATERIALS_V
where organization_id = l_organization_id
and assembly_item_id = l_inventory_item_id ;
if l_bom_exists >0 then
l_verify_flag := 'N';
l_error_message := l_error_message'Item already existing';
end if;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

IF l_verify_flag <> 'N' then
savepoint A;

BEGIN
insert into
BOM_BILL_OF_MTLS_INTERFACE
(
ASSEMBLY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE
,REVISION
,ASSEMBLY_TYPE
,PROCESS_FLAG
,ATTRIBUTE1
)
VALUES
(
l_inventory_item_id
,l_organization_id
,'CREATE'
,trim(nvl(c_bom.revision_no,0))
,1 ---1) Manufacturing, 2) Engineering
,1
,1
) ;

update xxx_bom_bill_mtls_stg
set h_verify_flag = 'Y'
where item_name = c_bom.item_name;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := SQLERRM;
update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N',
error_message = l_error_message
where item_name = c_bom.item_name;
goto next_bom;
END;

FOR C_COMP IN C_LINES(c_bom.item_name)
LOOP

BEGIN
select inventory_item_id
into l_component_item_id
from mtl_system_items_b
where organization_id = l_organization_id
and upper(segment1'.'segment2'.'segment3'.'segment4)
=upper(trim(C_COMP.bom_component));
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message 'Bom Component is not valid';
END;

BEGIN
If c_comp.supply_type = 'Phantom' then
l_wip_supply_type := 6;
else
l_wip_supply_type := 1;
end if;
END;

IF l_verify_flag <> 'N' then

BEGIN
insert into
bom_inventory_comps_interface
(
assembly_item_id
,process_flag
,transaction_type
,component_item_id
,component_sequence_id
,item_num
,operation_seq_num
,organization_id
,effectivity_date
,component_quantity
,component_yield_factor
,attribute6
,attribute9
,wip_supply_type
)
VALUES
(
l_inventory_item_id
,1
,'CREATE'
,l_component_item_id
,bom_inventory_components_s.nextval
,trim(c_comp.item_seq_bom_comp)
,trim(C_COMP.operation_sequence)
,l_organization_id
,sysdate
,trim(c_comp.quantiy)
,trim(c_comp.yield_factor)
,1
,1
,l_wip_supply_type
);

update xxx_bom_bill_mtls_stg
set l_verify_flag = 'Y'
where item_name = c_comp.item_name
and bom_component = c_comp.bom_component;
EXCEPTION
WHEN OTHERS THEN
l_error_message := SQLERRM;
rollback to savepoint A;

update xxx_bom_bill_mtls_stg
set l_verify_flag = 'N',
error_message = l_error_message
where item_name = c_comp.item_name
and bom_component = c_comp.bom_component;

update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N'
where item_name = c_comp.item_name ;
goto next_bom ;
END;

ELSE
rollback to savepoint A;
update xxx_bom_bill_mtls_stg
set l_verify_flag = 'N',
error_message = l_error_message
where item_name = c_comp.item_name
and bom_component = c_comp.bom_component;
update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N'
where item_name = c_comp.item_name ;
goto next_bom;
END IF;

END LOOP;

ELSE
update xxx_bom_bill_mtls_stg
set h_verify_flag = 'N',
error_message = l_error_message
where item_name = c_bom.item_name ;

END IF;
<
COMMIT;
END LOOP;
end xxx_bom_bill_mtls_api;
/