BOM API : Bom_Bo_Pub.process_bom

BOM API : Bom_Bo_Pub.process_bom

Declare    
       
      -- API input variables
      l_bom_header_rec              Bom_Bo_Pub.bom_head_rec_type                := Bom_Bo_Pub.g_miss_bom_header_rec;
      l_bom_revision_tbl            Bom_Bo_Pub.bom_revision_tbl_type            := Bom_Bo_Pub.g_miss_bom_revision_tbl;
      l_bom_component_tbl           Bom_Bo_Pub.bom_comps_tbl_type               := Bom_Bo_Pub.g_miss_bom_component_tbl;
      l_bom_ref_designator_tbl      Bom_Bo_Pub.bom_ref_designator_tbl_type      := Bom_Bo_Pub.g_miss_bom_ref_designator_tbl;
      l_bom_sub_component_tbl       Bom_Bo_Pub.bom_sub_component_tbl_type       := Bom_Bo_Pub.g_miss_bom_sub_component_tbl;
 
      -- API output variables
      x_bom_header_rec              Bom_Bo_Pub.bom_head_rec_type                := Bom_Bo_Pub.g_miss_bom_header_rec;
      x_bom_revision_tbl            Bom_Bo_Pub.bom_revision_tbl_type            := Bom_Bo_Pub.g_miss_bom_revision_tbl;
      x_bom_component_tbl           Bom_Bo_Pub.bom_comps_tbl_type               := Bom_Bo_Pub.g_miss_bom_component_tbl;
      x_bom_ref_designator_tbl      Bom_Bo_Pub.bom_ref_designator_tbl_type      := Bom_Bo_Pub.g_miss_bom_ref_designator_tbl;
      x_bom_sub_component_tbl       Bom_Bo_Pub.bom_sub_component_tbl_type       := Bom_Bo_Pub.g_miss_bom_sub_component_tbl;
      x_message_list                Error_Handler.Error_Tbl_Type;
 
      l_error_table           Error_Handler.Error_Tbl_Type;        
      l_output_dir            VARCHAR2(500) :=  '/usr/tmp/test';
      l_debug_filename        VARCHAR2(60) :=  'test_debug.dbg';
 
      l_return_status         VARCHAR2(1) := NULL;
      l_msg_count             NUMBER      := 0;
      l_cnt                   NUMBER      := 1;
     
       -- WHO columns
      l_user_id     NUMBER := -1;
      l_resp_id     NUMBER := -1;
      l_application_id  NUMBER := -1;
      l_row_cnt     NUMBER := 1;
      l_user_name       VARCHAR2(30) := 'MFG';
      l_resp_name       VARCHAR2(30) := 'MFG_AND_DIST_SUPER_USER_APS';    
       
BEGIN
 
          -- Get the user_id
          SELECT user_id
          INTO l_user_id
          FROM fnd_user
          WHERE user_name = l_user_name;
           
          -- Get the application_id and responsibility_id
          SELECT application_id, responsibility_id
          INTO l_application_id, l_resp_id
          FROM fnd_responsibility
          WHERE responsibility_key = l_resp_name;
           
          -- intiialize applications information
          FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);  -- Mfg / Mfg & Dist Mgr / INV
          dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );
           
          -- initialize BOM header
         l_bom_header_rec.assembly_item_name := 'RS_BOM_ASSY1';
         l_bom_header_rec.organization_code  := 'M1';  
         l_bom_header_rec.assembly_type      := 1;
         l_bom_header_rec.transaction_type   := 'CREATE';
         l_bom_header_rec.return_status      := NULL;
         
          l_cnt := 1;
          -- initialize BOM components
          -- component 1
          l_bom_component_tbl (l_cnt).organization_code := 'M1';
          l_bom_component_tbl (l_cnt).assembly_item_name := 'RS_BOM_ASSY1';
          l_bom_component_tbl (l_cnt).start_effective_date :=  sysdate; -- to_date('16-JUL-2010 19:30:39','DD-MON-YY HH24:MI:SS'); -- should match timestamp for UPDATE
          l_bom_component_tbl (l_cnt).component_item_name :=  'RS_BOM_C1';
          l_bom_component_tbl (l_cnt).alternate_bom_code := NULL;
          l_bom_component_tbl (l_cnt).supply_subinventory := 'RIP';
          l_bom_component_tbl (l_cnt).location_name := NULL;      -- '6.6.6..';  -- provide concatenated segments for locator
          l_bom_component_tbl (l_cnt).comments := 'Created from BOM API';
          l_bom_component_tbl (l_cnt).item_sequence_number :=    '10';
          l_bom_component_tbl (l_cnt).operation_sequence_number := '10';
          l_bom_component_tbl (l_cnt).transaction_type := 'CREATE';
          l_bom_component_tbl (l_cnt).quantity_per_assembly :=  2;
          l_bom_component_tbl (l_cnt).return_status := NULL;
         
          -- component 2
          l_cnt := l_cnt + 1;          
          l_bom_component_tbl (l_cnt).organization_code := 'M1';
          l_bom_component_tbl (l_cnt).assembly_item_name := 'RS_BOM_ASSY1';
          l_bom_component_tbl (l_cnt).start_effective_date :=  SYSDATE;  
          l_bom_component_tbl (l_cnt).component_item_name :=  'RS_BOM_C2';
          l_bom_component_tbl (l_cnt).alternate_bom_code := NULL;
          l_bom_component_tbl (l_cnt).supply_subinventory := 'RIP';
          l_bom_component_tbl (l_cnt).location_name := NULL;      
          l_bom_component_tbl (l_cnt).comments := 'Created from BOM API';
          l_bom_component_tbl (l_cnt).item_sequence_number :=    '20';
          l_bom_component_tbl (l_cnt).operation_sequence_number := '10';
          l_bom_component_tbl (l_cnt).transaction_type := 'CREATE';
          l_bom_component_tbl (l_cnt).quantity_per_assembly :=  5;
          l_bom_component_tbl (l_cnt).return_status := NULL;
         
         -- initialize error stack for logging errors
         Error_Handler.initialize;
           
         -- call API to create / update bill  
         DBMS_OUTPUT.PUT_LINE('=======================================================');
         DBMS_OUTPUT.PUT_LINE('Calling Bom_Bo_Pub.process_bom API');    
         
         Bom_Bo_Pub.process_bom (p_bo_identifier               => 'BOM',
                                  p_api_version_number          => 1.0,
                                  p_init_msg_list               => TRUE,
                                  p_bom_header_rec              => l_bom_header_rec,
                                  p_bom_revision_tbl            => l_bom_revision_tbl,
                                  p_bom_component_tbl           => l_bom_component_tbl,
                                  p_bom_ref_designator_tbl      => l_bom_ref_designator_tbl,
                                  p_bom_sub_component_tbl       => l_bom_sub_component_tbl,
                                  x_bom_header_rec              => x_bom_header_rec,
                                  x_bom_revision_tbl            => x_bom_revision_tbl,
                                  x_bom_component_tbl           => x_bom_component_tbl,
                                  x_bom_ref_designator_tbl      => x_bom_ref_designator_tbl,
                                  x_bom_sub_component_tbl       => x_bom_sub_component_tbl,
                                  x_return_status               => l_return_status,
                                  x_msg_count                   => l_msg_count    ,
                                  p_debug                       => 'Y',
                                  p_output_dir                  => l_output_dir,
                                  p_debug_filename              => l_debug_filename
                                 );
           
       DBMS_OUTPUT.PUT_LINE('=======================================================');
       DBMS_OUTPUT.PUT_LINE('Return Status: '||l_return_status);
 
       IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
 
           dbms_output.put_line('x_msg_count:' || l_msg_count);
             
            Error_Handler.GET_MESSAGE_LIST(x_message_list => l_error_table);
            DBMS_OUTPUT.PUT_LINE('Error Message Count :'||l_error_table.COUNT);
            FOR i IN 1..l_error_table.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE(to_char(i)||':'||l_error_table(i).entity_index||':'||l_error_table(i).table_name);
              DBMS_OUTPUT.PUT_LINE(to_char(i)||':'||l_error_table(i).message_text);
            END LOOP;          
           
       END IF;
       DBMS_OUTPUT.PUT_LINE('=======================================================');
     
EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
          DBMS_OUTPUT.PUT_LINE('=======================================================');
       RAISE;
END ;

Oracle Apps BOM Tables and Queries

BOM_DEPARTMENTS Departments
BOM_DEPARTMENT_CLASSES Department classes
BOM_DEPARTMENT_RESOURCES Resources associated with departments
BOM_OPERATIONAL_ROUTINGS Routings
BOM_OPERATION_NETWORKS Routing operation networks
BOM_OPERATION_RESOURCES Resources on operations
BOM_OPERATION_SEQUENCES Routing operations
BOM_OPERATION_SKILLS
BOM_RESOURCES Resources, overheads, material cost codes, and material overheads
BOM_STANDARD_OPERATIONS Standard operations
BOM_ALTERNATE_DESIGNATORS Alternate designators
BOM_COMPONENTS_B Bill of material components
BOM_STRUCTURES_B Bills of material
BOM_STRUCTURE_TYPES_B Structure Type master table
BOM_BILL_OF_MATERIALS
BOM_PARAMETERS
BOM_DEPENDENT_DESC_ELEMENTS
BOM_REFERENCE_DESIGNATORS
BOM_SUBSTITUTE_COMPONENTS
FND_DOCUMENTS
FND_DOCUMENTS_TL
FND_DOCUMENTS_SHORT_TEXT
FND_ATTACHED_DOCUMENTS

Select * from MFG_LOOKUPS
WHERE
--LOOKUP_CODE =  'BOM_ITEM_TYPE'
--LOOKUP_CODE =  'WIP_SUPPLY_TYPE'
--LOOKUP_CODE =  'BOM_ASSEMBLY_TYPE'

-----------------------------------------------------------------
1) Query for BOM Details


SELECT d.inventory_item_id,
       d.concatenated_segments "Assembly_item",
       e.organization_code "Organization",
       nvl(a.ALTERNATE_BOM_DESIGNATOR,'10') "ALTERNATIVE BOM",
       b.ITEM_NUM "ITEM",
       c.concatenated_segments "COMPONENT",
       b.component_quantity "UNIT",
       c.primary_uom_code "QUANTITY",
       d.primary_uom_code "UNIT"
FROM   bom_bill_of_materials       a
      ,bom_inventory_components  b
      ,mtl_system_items_kfv  c
      ,mtl_system_items_kfv d
      ,org_organization_definitions e
WHERE  1=1
AND   a.organization_id in (:P_organization_id)
AND (a.BILL_SEQUENCE_ID  =  b.BILL_SEQUENCE_ID
          OR a.COMMON_BILL_SEQUENCE_ID  =  b.BILL_SEQUENCE_ID)
AND    b.implementation_date IS NOT NULL
AND    b.DISABLE_DATE    IS   NULL
AND  a.organization_id  =  c.organization_id
AND  b.COMPONENT_ITEM_ID  = c.inventory_ITEM_ID
AND  a.assembly_item_id = d.inventory_item_id
AND  a.organization_id = d.organization_id
AND   a.organization_id = e.organization_id
--AND    c.organization_id   = :P_organization_id
--AND  a.ASSEMBLY_ITEM_ID  = :P_assembly_item_id
-- AND  ALTERNATE_BOM_DESIGNATOR IS NULL
ORDER BY e.organization_code,d.inventory_item_id,ALTERNATE_BOM_DESIGNATOR,b.ITEM_NUM;



----------------------------------------------------------------
2) Query for Resources


SELECT  c.concatenated_segments "Assemly_Item",
        d.organization_code "Organization",
        a.alternate_routing_designator "Alternate_routing",
        b.operation_seq_num "OPERATION NUMBER",
        e.department_code "WORK CENTER",
        b.operation_description "DESCRIPTION(Operation)",
        c.primary_uom_code "OPERATION UNIT",
        G.uom "UNIT",
       f.operation_code,
        g.resource_code,
        g.USAGE_RATE_OR_AMOUNT
        h.DESCRIPTION
FROM    bom_operational_routings a
       ,bom_operation_sequences b
       ,mtl_system_items_kfv c
       ,org_organization_definitions d
       ,bom_departments e
       ,bom_standard_operations f
       ,bom_operation_resources_v g
       ,bom_resources h
WHERE a.organization_id in (:P_organization_id)
AND   a.organization_id = d.organization_id
AND   a.organization_id = c.organization_id
AND   a.assembly_item_id = c.inventory_item_id
AND   a.routing_sequence_id = b.routing_sequence_id
AND   b.department_id = e.department_id
AND   b.disable_date is null
--AND   a.assembly_item_id = :P_assembly_item_id
AND f.standard_operation_id(+) = b.standard_operation_id
AND g.operation_sequence_id = b.operation_sequence_id
AND h.resource_id = g.resource_id
order by d.organization_code,c.concatenated_segments,a.alternate_routing_designator, b.operation_seq_num


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

3) Departments and Resources and Departments associated with Resources



select b.organization_code "Plant",
       a.DEPARTMENT_CODE "Department",
       a.DESCRIPTION "Department Description",
       a.DEPARTMENT_CLASS_CODE "Department Class",
       c.DESCRIPTION "Department Class Description",
       d.location_code "Department_location"
from   BOM_DEPARTMENTS a
      ,org_organization_definitions b
      ,BOM_DEPARTMENT_CLASSES c
      ,hr_locations d
where  a.organization_id in (:P_organization_id)
AND    a.organization_id  = b.organization_id
AND    a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE
AND    a.organization_id  = c.organization_id
AND    a.location_id = d.location_id(+)

select b.organization_code "Plant",
       a.RESOURCE_CODE "Resource Name",
       a.DESCRIPTION "Resource Description",
       a.UNIT_OF_MEASURE
from   BOM_RESOURCES a
      ,org_organization_definitions b
      --,mfg_lookups c
where  a.organization_id in (:P_organization_id)
AND    a.organization_id = b.organization_id
AND    a.Disable_date is Null
--AND    c.lookup_type = 'BOM_BASIS_TYPE'
--AND    c.LOOKUP_CODE = a.DEFAULT_BASIS_TYPE

exec mo_global.set_policy_context('S',:P_org_id);

--BOM_RESOURCE_TYPE
--BOM_BASIS_TYPE

select * from mfg_lookups where lookup_type like 'BOM_BASIS_TYPE'


select * from BOM_DEPARTMENT_RESOURCES

select * from BOM_RESOURCE_SHIFTS


select b.organization_code "Plant",
       a.DEPARTMENT_CODE "Department",
       a.DESCRIPTION "Department Description",
       a.DEPARTMENT_CLASS_CODE "Department Class",
       c.DESCRIPTION "Department Class Description",
       d.location_code "Department_location",
       f.RESOURCE_CODE "Resource Name",
       f.DESCRIPTION "Resource Description",
       f.UNIT_OF_MEASURE
from   BOM_DEPARTMENTS a
      ,org_organization_definitions b
      ,BOM_DEPARTMENT_CLASSES c
      ,hr_locations d
      ,BOM_DEPARTMENT_RESOURCES e
      ,BOM_RESOURCES f
     -- ,BOM_RESOURCE_SHIFTS g
where  a.organization_id in (:P_oranization_id)
AND    a.organization_id  = b.organization_id
AND    a.DEPARTMENT_CLASS_CODE = c.DEPARTMENT_CLASS_CODE
AND    a.organization_id  = c.organization_id
AND    a.location_id = d.location_id(+)
AND    a.DEPARTMENT_ID = e.DEPARTMENT_ID
AND    e.RESOURCE_ID = f.RESOURCE_ID
--AND    a.DEPARTMENT_ID = g.DEPARTMENT_ID
--AND    f.RESOURCE_ID = g.RESOURCE_ID

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

Routing Open Interface

Concurrent: Bill and Routing Interface

-- Create Routing
    insert into bom_op_routings_interface
    (
    assembly_item_id,
    process_revision,
    process_flag,
    transaction_type,
    routing_type,
    organization_id
    )
    values
    (
     218967,
     Null,
     1, --1 (Pending), 3 (Assign/Validation Failed), 4 (Import Failed) , 7 (Import Succeeded).
     'CREATE',
     1,----1) manufacturing, 2) engineering
     204
    );


-- Create Routing Sequences
    insert into bom_op_sequences_interface
    (
    assembly_item_id,
    operation_seq_num,
    department_id,
    department_code,
    process_flag,
    transaction_type,
    organization_id,
    effectivity_date
    )
    values
    (
    218967,
    10,
    42877,
    'PD1',
    1,
    'CREATE',
    204,
    sysdate
    );


-- Create Operation Resources
    insert into bom_op_resources_interface
    (
    resource_seq_num,
    resource_id,
    resource_code,
    usage_rate_or_amount, --basis_type ,
    assigned_units,--schedule_flag,autocharge_type,
    assembly_item_id,
    operation_seq_num, --operation_sequence_id,
    process_flag,
    transaction_type,
    effectivity_date,
    organization_id ,
    schedule_flag,
    schedule_seq_num
    )
    values
    (
    10,
    90529,
    'PR1',
    1,
    1,
    218967,
    10, --2346216 ,
    1,
    'CREATE',
    sysdate,
    204,
    1,
    10
    );



         select * from mtl_system_items_kfv where inventory_item_id = 133 and organization_id = 204
 
 select * from bom_departments where department_id = 42877 and organization_id = 204

 select * from BOM_DEPARTMENT_RESOURCES where department_id = 42877

 select * from bom_resources  where resource_id = 90529

 select * from bom_op_resources_interface

BOM Routing Creation API

Creating Maintenance Routes with BOM_RTG_PUB API (You can use this API for discrete manufacturing)
Firstly I create a custom table then I insert the datas from excel sheet.


CREATE TABLE XXINN_MAINTENANCE_ROUTES(
ASSEMBLY_ITEM_NAME         VARCHAR2(240 BYTE),
ORGANIZATION_CODE          VARCHAR2(3 BYTE),
LONG_DESCRIPTION           VARCHAR2(4000 BYTE),
OPERATION_DESCRIPTION      VARCHAR2(400 BYTE),
OPERATION_SEQUENCE_NUMBER  NUMBER,
DEPARTMENT_CODE            VARCHAR2(10 BYTE),
RESOURCE_SEQUENCE_NUMBER   NUMBER,
RESOURCE_CODE              VARCHAR2(10 BYTE),
USAGE_RATE_OR_AMOUNT       NUMBER,
ASSIGNED_UNITS             NUMBER,
ERROR                      VARCHAR2(2000 BYTE)
)
Then I have written this procedure for creating the maintenance routes.

CREATE OR REPLACE PROCEDURE APPS.XXINN_CREATE_MAINTNCE_ROUTE(ERRBUF  OUT VARCHAR2,
RETCODE OUT NUMBER) IS
P_HEADER_REC       Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
P_OPERATION_REC    Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
P_RESOURCE_REC     Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
p_RETURN_STATUS    VARCHAR2(240);
p_MSG_COUNT        NUMBER;
l_ret_text         VARCHAR2(32000);
p_Message_List     Error_Handler.Error_Tbl_Type;
i                  NUMBER;
j                  NUMBER;
x_rtg_header_rec   Bom_Rtg_Pub.Rtg_Header_Rec_Type;
x_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type;
x_operation_tbl    Bom_Rtg_Pub.Operation_Tbl_Type;
x_op_resource_tbl  Bom_Rtg_Pub.Op_Resource_Tbl_Type;
x_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type;
x_op_network_tbl   Bom_Rtg_Pub.Op_Network_Tbl_Type;

CURSOR C1_ENG IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code
FROM  APPS.XXINN_MAINTENANCE_ROUTES t;

CURSOR C1_OPR(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.department_code,
LTRIM (RTRIM(T.LONG_DESCRIPTION)) LONG_DESCRIPTION,
translate (LTRIM(RTRIM( t.OPERATION_DESCRIPTION)),'ÇĞŞİÜÖ','CGSIUO') OPERATION_DESCRIPTION
FROM APPS.XXINN_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
ORDER BY t.operation_sequence_number;

CURSOR C1_RES(p_ASSEMBLY_ITEM VARCHAR2, p_org_code VARCHAR2, p_seq_number NUMBER) IS
SELECT DISTINCT t.assembly_item_name,
t.organization_code,
t.operation_sequence_number,
t.department_code,
t.resource_sequence_number,
t.resource_code,
t.usage_rate_or_amount,
t.ASSIGNED_UNITS
FROM APPS.XXINN_MAINTENANCE_ROUTES t
WHERE t.assembly_item_name = p_ASSEMBLY_ITEM
AND t.organization_code = p_org_code
AND t.operation_sequence_number = p_seq_number
ORDER BY t.resource_sequence_number;

BEGIN
FND_GLOBAL.APPS_INITIALIZE(1117, 23118, 426); --user_id, resp_id, resp_appl_id
FOR C_REC IN C1_ENG LOOP
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, C_REC.ASSEMBLY_ITEM_NAME);
DBMS_OUTPUT.PUT_LINE(C_REC.ASSEMBLY_ITEM_NAME);

P_HEADER_REC                    := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
P_HEADER_REC.ASSEMBLY_ITEM_NAME := C_REC.ASSEMBLY_ITEM_NAME;
P_HEADER_REC.ORGANIZATION_CODE  := C_REC.ORGANIZATION_CODE;
P_HEADER_REC.Eng_Routing_Flag   := 2;   -- 1 for manufacturing  2- for Engineering
P_HEADER_REC.Transaction_Type   := 'CREATE'; --
P_HEADER_REC.Return_Status      := NULL; --
i                               := 0;
j                               := 0;
P_OPERATION_REC.DELETE;
P_RESOURCE_REC.DELETE;

FOR c_rec2 IN C1_OPR(C_REC.ASSEMBLY_ITEM_NAME, C_REC.ORGANIZATION_CODE) LOOP
i := i + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
'            ' || C_REC2.OPERATION_SEQUENCE_NUMBER || '-' ||
C_REC2.DEPARTMENT_CODE);
DBMS_OUTPUT.PUT_LINE('            ' ||
C_REC2.OPERATION_SEQUENCE_NUMBER || '-' ||
C_REC2.DEPARTMENT_CODE||'-'||C_REC2.OPERATION_DESCRIPTION);
P_OPERATION_REC(i).ASSEMBLY_ITEM_NAME := C_REC2.ASSEMBLY_ITEM_NAME;
P_OPERATION_REC(i).ORGANIZATION_CODE := C_REC2.ORGANIZATION_CODE;
P_OPERATION_REC(i).OPERATION_SEQUENCE_NUMBER := C_REC2.OPERATION_SEQUENCE_NUMBER;
P_OPERATION_REC(i).DEPARTMENT_CODE := C_REC2.DEPARTMENT_CODE;
P_OPERATION_REC(i).LONG_DESCRIPTION := C_REC2.LONG_DESCRIPTION; --Ana tabloda long description diye bir alan aç
P_OPERATION_REC(i).OPERATION_DESCRIPTION := C_REC2.OPERATION_DESCRIPTION;
P_OPERATION_REC(i).Operation_Type := 1; --
P_OPERATION_REC(i).Start_Effective_Date := SYSDATE; --
P_OPERATION_REC(i).Transaction_Type := 'CREATE'; --

FOR c_rec3 IN C1_RES(C_REC.ASSEMBLY_ITEM_NAME,
C_REC.ORGANIZATION_CODE,
C_REC2.OPERATION_SEQUENCE_NUMBER) LOOP
j := j + 1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
'                ' ||
C_REC3.RESOURCE_SEQUENCE_NUMBER || '-' ||
C_REC3.RESOURCE_CODE);
DBMS_OUTPUT.PUT_LINE('                ' ||
C_REC3.RESOURCE_SEQUENCE_NUMBER || '-' ||
C_REC3.RESOURCE_CODE);
P_RESOURCE_REC(j).ASSEMBLY_ITEM_NAME := C_REC3.ASSEMBLY_ITEM_NAME;
P_RESOURCE_REC(j).ORGANIZATION_CODE := C_REC3.ORGANIZATION_CODE;
P_RESOURCE_REC(j).OPERATION_SEQUENCE_NUMBER := C_REC3.OPERATION_SEQUENCE_NUMBER;
P_RESOURCE_REC(j).RESOURCE_SEQUENCE_NUMBER := C_REC3.RESOURCE_SEQUENCE_NUMBER;
P_RESOURCE_REC(j).RESOURCE_CODE := C_REC3.RESOURCE_CODE;
P_RESOURCE_REC(j).USAGE_RATE_OR_AMOUNT := C_REC3.USAGE_RATE_OR_AMOUNT;
P_RESOURCE_REC(j).Operation_Type := 1; --
P_RESOURCE_REC(j).Op_Start_Effective_Date := SYSDATE; --
P_RESOURCE_REC(j).Transaction_Type := 'CREATE'; --
P_RESOURCE_REC(j).Schedule_Flag := 1;
P_RESOURCE_REC(J).ASSIGNED_UNITS := C_REC3.ASSIGNED_UNITS;

END LOOP; --res

END LOOP; -- opr

x_rtg_header_rec := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
x_rtg_revision_tbl.delete;
x_operation_tbl.delete;
x_op_resource_tbl.delete;
x_sub_resource_tbl.delete;
x_op_network_tbl.delete;

APPS.BOM_RTG_PUB.PROCESS_RTG(p_init_msg_list    => TRUE,
p_rtg_header_rec   => P_HEADER_REC,
p_operation_tbl    => P_OPERATION_REC,
p_op_resource_tbl  => P_RESOURCE_REC,
x_rtg_header_rec   => x_rtg_header_rec,
x_rtg_revision_tbl => x_rtg_revision_tbl,
x_operation_tbl    => x_operation_tbl,
x_op_resource_tbl  => x_op_resource_tbl,
x_sub_resource_tbl => x_sub_resource_tbl,
x_op_network_tbl   => x_op_network_tbl,
x_return_status    => p_RETURN_STATUS,
X_MSG_COUNT        => p_MSG_COUNT);

IF p_RETURN_STATUS <> 'S' THEN
error_handler.Get_Message_List(p_message_list);
FOR i IN 1 .. p_message_list.COUNT LOOP

IF i = 1 THEN
l_ret_text := p_Message_List(i).message_text;

ELSE
l_ret_text := l_ret_text || chr(10) || p_Message_List(i).message_text;
END IF;

END LOOP;

-- ROLLBACK;

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' ERROR!!!!!!!' || chr(10) || 'Error:' ||p_Message_List(i).message_type||'-'||p_Message_List(i).message_name||
l_ret_text);

DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' ERROR!!!!!!!' || chr(10) || 'Error:' ||p_Message_List(i).message_type||'-'||p_Message_List(i).message_name||
l_ret_text);

UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR = l_ret_text
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code;

ELSE

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,

P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' the process is successful..');

DBMS_OUTPUT.PUT_LINE(P_HEADER_REC.ASSEMBLY_ITEM_NAME ||
' the process is successful..');

UPDATE APPS.XXINN_MAINTENANCE_ROUTES xxinn
SET XXINN.ERROR = null
WHERE XXINN.ASSEMBLY_ITEM_NAME = c_rec.assembly_item_name
AND XXINN.ORGANIZATION_CODE = c_rec.organization_code;

END IF;

END LOOP;

END

;
/

BOM Creation API

===============================================================
bom_bo_pub.process_bom
(p_bo_identifier => 'BOM',
-- This parameter is required. It is used by the API to compare the version number of
-- incoming calls to its current version number.
p_api_version_number => 1.0 ,
-- This parameter is set to TRUE, allows callers to request that the API do the
--initialization of message list on their behalf.
p_init_msg_list => TRUE,
-- This is a set of data structures that represent the incoming business objects. This is a
--record that holds the Bill of Materials header for the BOM
p_bom_header_rec => v_bom_header_rec,
-- All the p*_tbl parameters are data structure that represent incoming business objects
-- They are PL/SQL tables of records that hold for each of the other entities.
p_bom_revision_tbl => v_bom_revision_tbl , p_bom_component_tbl => v_bom_component_tbl, p_bom_ref_designator_tbl => v_bom_ref_designator_tbl, p_bom_sub_component_tbl => v_bom_sub_component_tbl,
-- All the x*_tbl parameters are data structure that represent outgoing business objects
-- They are PL/SQL tables of records that hold records for each of the other entities except
-- now they have all the changes that the import program made to it through all the
-- steps.
x_bom_header_rec => v_bom_hdr_rec , x_bom_revision_tbl => v_bom_rev_tbl,
x_bom_component_tbl => v_bom_comp_tbl,
x_bom_ref_designator_tbl => v_bom_ref_desig_tbl, x_bom_sub_component_tbl => v_bom_sub_comp_tbl,
-- This is a flag that indicates the state of the whole business object after the
-- import. 'S' - Success, 'E' - Error, 'F' - Fatal Error, 'U' - Unexpected Error ,
x_return_status => v_return_status ,
-- This holds the number of messages in the API message stack after
--the import.
x_msg_count => v_msg_count
p_debug => 'N',
p_output_dir => '',
p_debug_filename => '' );
=========================================================