Update Option Class BOM WIP_SUPPLY_TYPE

Using BOM_INVENTORY_COMPS_INTERFACE

SEGMENT1	OPTIONAL	WIP_SUPPLY_TYPE	MUTUALLY_EXCLUSIVE_OPTIONS
420K60D8	1	2	2
917Y66D8	1	2	2
156A66S4	1	2	2
156A66S8	1	2	2
157A66D4	1	2	2
157A66D8	1	2	2
157A66S4	1	2	2
157A66S8	1	2	2
156A66D4	1	2	2
156A66D8	1	2	2
437A60D4	1	2	2
437A60D8	1	2	2
437A60S4	1	2	2

 I have some data needs to change from WIP_SUPPLY_TYPE = 2 to WIP_SUPPLY_TYPE = 6.

WIP_SUPPLY_TYPE = 2 means Assembly Pull
WIP_SUPPLY_TYPE = 6 means Phantom.

Insert into apps.bom_inventory_comps_interface
(component_sequence_id, wip_supply_type, process_flag, transaction_type)
SELECT component_sequence_id, 6 wip_supply_type
, 1 process_flag, 'UPDATE' transaction_type
from (SELECT B.SEGMENT1
, D.SEGMENT1
, BIC.OPTIONAL
, BIC.COMPONENT_SEQUENCE_ID
, BIC.WIP_SUPPLY_TYPE
, BIC.MUTUALLY_EXCLUSIVE_OPTIONS
FROM apps.BOM_BILL_OF_MATERIALS A, apps.MTL_SYSTEM_ITEMS_B B, apps.BOM_INVENTORY_COMPONENTS BIC, apps.MTL_SYSTEM_ITEMS_B D
, apps.HR_ORGANIZATION_UNITS HR
WHERE A.ASSEMBLY_ITEM_ID = B.INVENTORY_ITEM_ID
AND BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.ORGANIZATION_ID = HR.ORGANIZATION_ID
AND HR.NAME LIKE '%Master%'
AND B.SEGMENT1 = 'SLAB'
AND BIC.DISABLE_DATE IS NULL
AND BIC.COMPONENT_ITEM_ID = D.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = B.ORGANIZATION_ID
AND BIC.WIP_SUPPLY_TYPE = 2
AND (D.SEGMENT1 LIKE '%8' OR D.SEGMENT1 LIKE '%4'))

HR.NAME is your Master Inventory Organization name
B.SEGMENT1 is one of your option classes name
D.SEGMENT1 is the element that you want to make changes to.

Basically above query will insert records into BOM_INVENTORY_COMPS_INTERFACE table with ITEMs ends with 8 or 4 and WIP_SUPPLY_TYPE as Assembly Pull under SLAB OPTION CLASS.

I want to update WIP_SUPPLY_TYPE CODE for above option class components to be PHANTOM which is WIP_SUPPLY_TYPE value as 6.

SQL> insert into apps.bom_inventory_comps_interface
(component_sequence_id, wip_supply_type, process_flag, transaction_type)
SELECT component_sequence_id, 6 wip_supply_type, 1 process_flag, 'UPDATE' transaction_type
from (SELECT B.SEGMENT1
, D.SEGMENT1
, BIC.OPTIONAL
, BIC.COMPONENT_SEQUENCE_ID
, BIC.WIP_SUPPLY_TYPE
, BIC.MUTUALLY_EXCLUSIVE_OPTIONS
FROM apps.BOM_BILL_OF_MATERIALS A, apps.MTL_SYSTEM_ITEMS_B B, apps.BOM_INVENTORY_COMPONENTS BIC, apps.MTL_SYSTEM_ITEMS_B D
, apps.HR_ORGANIZATION_UNITS HR
WHERE A.ASSEMBLY_ITEM_ID = B.INVENTORY_ITEM_ID
AND BIC.BILL_SEQUENCE_ID = A.BILL_SEQUENCE_ID
AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
AND B.ORGANIZATION_ID = HR.ORGANIZATION_ID
AND HR.NAME LIKE '%Master%'
AND B.SEGMENT1 = 'SLAB'
AND BIC.DISABLE_DATE IS NULL
AND BIC.COMPONENT_ITEM_ID = D.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = B.ORGANIZATION_ID
AND BIC.WIP_SUPPLY_TYPE = 2
AND (D.SEGMENT1 LIKE '%8' OR D.SEGMENT1 LIKE '%4'));

227 rows created.

SQL> commit;

Login to your Application and Go to Bills Of Material Super User





Choose Master Inventory Organization



All Oganizations to No
Import Routings No -- Since we don't use any routings.

Run the request.

BOM_INVENTORY_COMPS_INTERFACE should be cleaned after successfully update components.