BOM interface Tables in oracle apps R12
In this post , We will be discuss about BOM interface Tables in oracle apps R12. BOM interface tables helps to import/Create the Item bom into the oracle apps r12. We can bulk upload the BOM Items into the oracle application using the BOM interface table in oracle apps r12. If we have to create Item BOM's in bulk , then we have to use the BOM interface. BOM interface helps to create the bulk item BOM'S in a very short time as compared to front end application. Oracle has provided the BOM interface table to support the bulk BOM item creation in oracle apps r12. Here below is the detail about BOM interface Tables in oracle apps R12.
2 Important BOM interface Tables in oracle apps R12
1.bom_bill_of_mtls_interface
2.bom_inventory_comps_interface
PLSQL Script to Using BOM interface Tables in oracle apps R12
 |
| BOM interface Tables in oracle apps R12 |
Declare
CURSOR cur_parent_bom_detail
IS
SELECT organization_code, item_number, revision, alternate_name
FROM xx_bom_stag
group by organization_code, item_number, revision, alternate_name;
CURSOR cur_child_bom_detail (
v_organization_code VARCHAR2,
v_item_number VARCHAR2,
v_revision VARCHAR2,
v_alternate_name VARCHAR2
)
IS
SELECT /*+ USE_HASH_AGGREGATION INDEX_RS_ASC (XXdata_loading_BOM_STAG
XXdata_loading_BOM_STAG_2)*/ *
FROM xx_bom_stag
WHERE organization_code = v_organization_code
AND item_number = v_item_number
AND revision =v_revision
AND alternate_name =v_alternate_name;
org_id NUMBER;
assem_item_id NUMBER;
v_supply_locator_id NUMBER;
supply_look_code NUMBER;
comp_item_id NUMBER;
v_erro_msg VARCHAR2 (32767);
CURSOR cur_get_org_id (v_org_code VARCHAR2)
IS
SELECT a.organization_id
FROM mtl_parameters a, org_organization_definitions b
WHERE a.master_organization_id = b.organization_id
AND a.organization_code = v_org_code;
CURSOR cur_get_item_id (v_org_id NUMBER, v_item_number VARCHAR2)
IS
SELECT inventory_item_id
FROM mtl_system_items_b
WHERE organization_id = v_org_id AND segment1 = v_item_number;
CURSOR cur_supply_type (v_supply_type VARCHAR2)
IS
SELECT lookup_code
FROM mfg_lookups
WHERE UPPER (meaning) = UPPER (v_supply_type)
AND lookup_type = 'WIP_SUPPLY';
BEGIN
BEGIN
FOR parent_bom IN cur_parent_bom_detail
LOOP
BEGIN
OPEN cur_get_org_id (parent_bom.organization_code);
FETCH cur_get_org_id
INTO org_id;
IF cur_get_org_id%NOTFOUND
THEN
org_id := NULL;
CLOSE cur_get_org_id;
END IF;
CLOSE cur_get_org_id;
OPEN cur_get_item_id (org_id, parent_bom.item_number);
FETCH cur_get_item_id
INTO assem_item_id;
IF cur_get_item_id%NOTFOUND
THEN
assem_item_id := NULL;
CLOSE cur_get_item_id;
END IF;
CLOSE cur_get_item_id;
----------Insert bom_bill_of_mtls_interface -----------------
INSERT INTO bom_bill_of_mtls_interface
(assembly_item_id, organization_id,
alternate_bom_designator,
organization_code,
item_number, last_update_date,
last_updated_by, creation_date, created_by,
last_update_login, transaction_type, process_flag
)
VALUES (assem_item_id, org_id,
parent_bom.alternate_name,
parent_bom.organization_code,
parent_bom.item_number, SYSDATE,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
fnd_global.user_id, 'CREATE', 1
);
-----------------Child Record Insertion -----------
FOR child_bom IN
cur_child_bom_detail
(v_organization_code => parent_bom.organization_code,
v_item_number => parent_bom.item_number,
v_revision => parent_bom.revision,
v_alternate_name => parent_bom.alternate_name
)
LOOP
BEGIN
OPEN cur_get_item_id (org_id,
child_bom.comp_item_number);
FETCH cur_get_item_id
INTO comp_item_id;
IF cur_get_item_id%NOTFOUND
THEN
comp_item_id := NULL;
CLOSE cur_get_item_id;
END IF;
CLOSE cur_get_item_id;
OPEN cur_supply_type (child_bom.wip_supply_type);
FETCH cur_supply_type
INTO supply_look_code;
IF cur_supply_type%NOTFOUND
THEN
supply_look_code := NULL;
CLOSE cur_supply_type;
END IF;
CLOSE cur_supply_type;
IF child_bom.location_name IS NOT NULL
THEN
BEGIN
SELECT inventory_location_id
INTO v_supply_locator_id
FROM mtl_item_locations_kfv
WHERE organization_id = org_id
AND subinventory_code =
child_bom.supply_subinventory
AND concatenated_segments =
child_bom.location_name;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_supply_locator_id := NULL;
WHEN OTHERS
THEN
v_supply_locator_id := NULL;
END;
END IF;
INSERT INTO bom_inventory_comps_interface
(component_item_id, assembly_item_id,
organization_id, component_item_number,
organization_code,
assembly_item_number,
alternate_bom_designator,
item_num,
operation_seq_num, effectivity_date,
component_quantity,
component_yield_factor,
wip_supply_type,
supply_subinventory,
supply_locator_id, last_update_date,
last_updated_by, creation_date,
created_by, last_update_login,
transaction_type, process_flag,
component_remarks
)
VALUES (comp_item_id, assem_item_id,
org_id, child_bom.comp_item_number,
child_bom.organization_code,
child_bom.item_number,
child_bom.alternate_name,
child_bom.item_seq_num,
child_bom.operation_seq_num, SYSDATE,
child_bom.component_quantity,
child_bom.component_yield_factor,
supply_look_code,
child_bom.supply_subinventory,
v_supply_locator_id, SYSDATE,
fnd_global.user_id, SYSDATE,
fnd_global.user_id, fnd_global.user_id,
'CREATE', 1,
'BOM Migration'
);
UPDATE xx_bom_stag
SET flag_process = 'P'
WHERE entry_id = child_bom.entry_id;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
v_erro_msg := SQLERRM;
UPDATE xx_bom_stag
SET flag_process = 'E',
error_message = v_erro_msg
WHERE entry_id = child_bom.entry_id;
COMMIT;
END;
COMMIT;
END LOOP;
COMMIT;
END;
END LOOP;
END;
END;
|
| BOM interface Tables in oracle apps R12 |
0 comments:
Post a Comment