Bom conversion in oracle apps r12
In this post , I will share you the PLSQL script for Bom conversion in oracle apps r12. I am just sharing you sample script you need to do some alterations in your instance as per your requirement.
Bom conversion 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;
0 comments:
Post a Comment