[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** Select all INSERTS
*/
CURSOR c1 IS
SELECT organization_code OC, organization_id OI,
assembly_item_id AII, assembly_item_number AIN,
alternate_bom_designator ABD, bill_sequence_id BSI,
component_sequence_id CSI, transaction_id TI,
component_item_id CII, component_item_number CIN,
location_name LN, supply_locator_id SLI,
operation_seq_num OSN,
to_char(effectivity_date, 'YYYY/MM/DD HH24:MI:SS') ED,
bom_item_type BIT, transaction_type A, WIP_SUPPLY_TYPE WST,
supply_subinventory SS
FROM bom_inventory_comps_interface
WHERE process_flag = 1
AND transaction_type = G_Insert
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND (all_org = 1
OR
(all_org = 2 and organization_id = org_id))
AND rownum < G_rows_to_commit;
** Select UPDATES and DELETES
*/
CURSOR c2 IS
SELECT organization_code OC, organization_id OI,
assembly_item_id AII, assembly_item_number AIN,
alternate_bom_designator ABD, bill_sequence_id BSI,
component_sequence_id CSI, transaction_id TI,
component_item_id CII, component_item_number CIN,
location_name LN, supply_locator_id SLI,
operation_seq_num OSN, assembly_type AST,
to_char(effectivity_date, 'YYYY/MM/DD HH24:MI:SS') ED,
bom_item_type BIT, transaction_type A
FROM bom_inventory_comps_interface
WHERE process_flag = 1
AND transaction_type in (G_UPDATE, G_DELETE)
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND (all_org = 1
OR
(all_org = 2 and organization_id = org_id))
AND rownum < G_rows_to_commit;
/** G_INSERT is 'CREATE'. Update 'INSERT' to 'CREATE' **/
stmt_num := 0.5 ;
UPDATE bom_inventory_comps_interface
SET transaction_type = G_Insert
WHERE process_flag = 1
AND upper(transaction_type) = 'INSERT'
AND rownum < G_rows_to_commit;
UPDATE bom_inventory_comps_interface ori
SET organization_id = (SELECT organization_id
FROM mtl_parameters a
WHERE a.organization_code = ori.organization_code)
WHERE process_flag = 1
AND upper(transaction_type) in (G_Insert, G_Delete, G_Update)
AND (UPPER(ori.interface_entity_type) = 'BILL'
OR ori.interface_entity_type is null)
AND organization_id is null
AND organization_code is not null
AND exists (SELECT organization_code
FROM mtl_parameters b
WHERE b.organization_code = ori.organization_code)
AND rownum < G_rows_to_commit;
** FOR INSERTS - Assign transaction ids
*/
stmt_num := 1;
UPDATE bom_inventory_comps_interface
SET transaction_id = mtl_system_items_interface_s.nextval,
component_sequence_id = bom_inventory_components_s.nextval,
transaction_type = upper(transaction_type)
WHERE transaction_id is null
AND process_flag = 1
AND upper(transaction_type) = G_Insert
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND rownum < G_rows_to_commit;
** FOR UPDATES and DELETES - Assign transaction ids
*/
stmt_num := 1;
UPDATE bom_inventory_comps_interface
SET transaction_id = mtl_system_items_interface_s.nextval,
transaction_type = upper(transaction_type)
WHERE transaction_id is null
AND process_flag = 1
AND upper(transaction_type) in (G_UPDATE, G_DELETE)
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND rownum < G_rows_to_commit;
** FOR INSERTS - Assign values
*/
WHILE continue_loop LOOP
commit_cnt := 0;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT bom.bill_sequence_id, msi.bom_item_type,
msi.atp_components_flag
INTO c1rec.BSI, x_bom_item_type, x_atp_comp_flag
FROM bom_bill_of_materials bom,
mtl_system_items msi
WHERE bom.organization_id = c1rec.OI
AND bom.assembly_item_id = c1rec.AII
AND nvl(bom.alternate_bom_designator, 'NONE') =
nvl(c1rec.ABD, 'NONE')
AND msi.organization_id = bom.organization_id
AND msi.inventory_item_id = bom.assembly_item_id;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT bom.assembly_item_id, bom.organization_id,
bom.alternate_bom_designator, msi.bom_item_type,
msi.atp_components_flag
INTO c1rec.AII, c1rec.OI, c1rec.ABD, x_bom_item_type,
x_atp_comp_flag
FROM bom_bill_of_materials bom,
mtl_system_items msi
WHERE bom.bill_sequence_id = c1rec.BSI
AND msi.organization_id = bom.organization_id
AND msi.inventory_item_id = bom.assembly_item_id;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT wip_supply_type, wip_supply_subinventory,
wip_supply_locator_id
INTO c1rec.wst, c1rec.SS, c1rec.SLI
FROM mtl_system_items
WHERE organization_id = c1rec.OI
AND inventory_item_id = c1rec.CII;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT bom_item_type, default_include_in_rollup_flag,
atp_flag, pick_components_flag
INTO c1rec.BIT, x_rollup_flag, x_atp_flag, x_pick_components
FROM mtl_system_items
WHERE organization_id = c1rec.OI
AND inventory_item_id = c1rec.CII;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
GOTO update_member;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
** Update Component with defaults and derived values
*/
stmt_num := 11;
UPDATE bom_inventory_comps_interface
SET component_item_id = nvl(component_item_id, c1rec.CII),
item_num = nvl(item_num, 1),
component_quantity = nvl(component_quantity, 1),
component_yield_factor = nvl(component_yield_factor, 1),
implementation_date = effectivity_date,
planning_factor = nvl(planning_factor, 100),
quantity_related = nvl(quantity_related, 2),
so_basis = nvl(so_basis, 2),
optional = nvl(optional, 2),
mutually_exclusive_options = nvl(mutually_exclusive_options,2),
include_in_cost_rollup = nvl(include_in_cost_rollup, decode(nvl(x_rollup_flag, 'Y'),'Y', 1, 2)),
check_atp = nvl(check_atp, x_check_atp_default),
required_to_ship = nvl(required_to_ship, 2),
required_for_revenue = nvl(required_for_Revenue, 2),
include_on_ship_docs = nvl(include_on_ship_docs, 2),
include_on_bill_docs = nvl(include_on_bill_docs, 2),
low_quantity = nvl(low_quantity, nvl(high_quantity,null)),
high_quantity = nvl(high_quantity,nvl(low_quantity,null)),
bill_sequence_id = nvl(bill_Sequence_id, c1rec.BSI),
pick_components = decode(x_pick_components, 'Y', 1, 2),
wip_supply_type = NVL(wip_supply_type, c1rec.wst),
supply_subinventory = NVL(supply_subinventory, c1rec.ss),
supply_locator_id = nvl(supply_locator_id, c1rec.SLI),
assembly_item_id = nvl(assembly_item_id, c1rec.AII),
alternate_bom_designator = nvl(alternate_bom_designator,
c1rec.ABD),
organization_id = nvl(organization_id, c1rec.OI),
creation_date = nvl(creation_date, sysdate),
created_by = nvl(created_by, user_id),
last_update_date = nvl(last_update_date, sysdate),
last_updated_by = nvl(last_updated_by, user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id =nvl(program_application_id,prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate),
process_flag = 2,
bom_item_type = c1rec.BIT
WHERE transaction_id = c1rec.TI;
<>
/*
** Update Product Family Member with defaults and derived values
*/
x_current_date := trunc(sysdate);
UPDATE bom_inventory_comps_interface
SET component_item_id = nvl(component_item_id, c1rec.CII),
bill_sequence_id = nvl(bill_Sequence_id, c1rec.BSI),
assembly_item_id = nvl(assembly_item_id, c1rec.AII),
alternate_bom_designator = nvl(alternate_bom_designator,
c1rec.ABD),
organization_id = nvl(organization_id, c1rec.OI),
operation_seq_num = 1,
item_num = 1,
component_quantity = 1,
component_yield_factor = 1,
planning_factor = nvl(planning_factor,100),
quantity_related = 2,
so_basis = 2,
optional = 2,
mutually_exclusive_options = 2,
required_to_ship = 2,
required_for_revenue = 2,
include_on_ship_docs = 2,
effectivity_date = nvl(trunc(effectivity_date),
x_current_date),
implementation_date = nvl(trunc(effectivity_date),
x_current_date),
include_in_cost_rollup = decode(nvl(x_rollup_flag,
'Y'),'Y', 1, 2),
check_atp = x_check_atp_default,
pick_components = decode(x_pick_components,
'Y', 1, 2),
bom_item_type = c1rec.BIT,
supply_locator_id = null,
low_quantity = null,
high_quantity = null,
change_notice = null,
shipping_allowed = null,
acd_type = null,
old_component_sequence_id = null,
wip_supply_type = null,
supply_subinventory = null,
operation_lead_time_percent = null,
revised_item_sequence_id = null,
cost_factor = null,
substitute_comp_id = null,
substitute_comp_number = null,
reference_designator = null,
creation_date = nvl(creation_date, sysdate),
created_by = nvl(created_by, user_id),
last_update_date = nvl(last_update_date, sysdate),
last_updated_by = nvl(last_updated_by, user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id =nvl(program_application_id,prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate),
process_flag = 2
WHERE transaction_id = c1rec.TI;
** FOR UPDATES and DELETES - Assign values
*/
continue_loop := TRUE;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
SELECT bom.bill_sequence_id, bom.assembly_type,
msi.bom_item_type
INTO c2rec.BSI, c2rec.AST, x_bom_item_type
FROM bom_bill_of_materials bom,
mtl_system_items msi
WHERE bom.organization_id = c2rec.OI
AND bom.assembly_item_id = c2rec.AII
AND nvl(bom.alternate_bom_designator, 'NONE') =
nvl(c2rec.ABD, 'NONE')
AND msi.organization_id = bom.organization_id
AND msi.inventory_item_id = bom.assembly_item_id;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
SELECT bom.assembly_item_id, bom.organization_id,
bom.alternate_bom_designator, bom.assembly_type,
msi.bom_item_type
INTO c2rec.AII, c2rec.OI, c2rec.ABD, c2rec.AST,
x_bom_item_type
FROM bom_bill_of_materials bom,
mtl_system_items msi
WHERE bom.bill_sequence_id = c2rec.BSI
AND msi.organization_id = bom.organization_id
AND msi.inventory_item_id = bom.assembly_item_id;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
SELECT component_sequence_id
INTO c2rec.CSI
FROM bom_inventory_components
WHERE bill_sequence_id = c2rec.BSI
AND component_item_id = c2rec.CII
AND operation_seq_num = decode(x_bom_item_type,
G_ProductFamily, 1, c2rec.OSN)
AND effectivity_date = to_date(c2rec.ED,
'YYYY/MM/DD HH24:MI:SS');
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
SELECT bbom.assembly_item_id, bbom.organization_id,
bbom.bill_sequence_id,
bbom.alternate_bom_designator, bbom.assembly_type,
bic.component_item_id, msi.bom_item_type
INTO c2rec.AII, c2rec.OI, c2rec.BSI, c2rec.ABD, c2rec.AST,
c2rec.CII, x_bom_item_type
FROM mtl_system_items msi,
bom_bill_of_materials bbom,
bom_inventory_components bic
WHERE bbom.bill_sequence_id = bic.bill_sequence_id
AND bic.component_sequence_id = c2rec.CSI
AND msi.organization_id = bbom.organization_id
AND msi.inventory_item_id = bbom.assembly_item_id;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** FOR UPDATES - Assign Supply Locator
*/
IF (c2rec.A = G_UPDATE) THEN
/*
** For Product Family Members
*/
IF (x_bom_item_type = G_ProductFamily) THEN
stmt_num := 10.1;
UPDATE bom_inventory_comps_interface
SET component_sequence_id = c2rec.CSI,
component_item_id = c2rec.CII,
bill_sequence_id = c2rec.BSI,
organization_id = c2rec.OI,
assembly_item_id = c2rec.AII,
alternate_bom_designator = c2rec.ABD,
assembly_type = c2rec.AST,
last_update_date = nvl(last_update_date, sysdate),
last_updated_by = nvl(last_updated_by, user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id =nvl(program_application_id,
prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate),
process_flag = 2
WHERE transaction_id = c2rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** Update "Update" record
*/
stmt_num := 11;
UPDATE bom_inventory_comps_interface
SET component_sequence_id = c2rec.CSI,
component_item_id = c2rec.CII,
bill_sequence_id = c2rec.BSI,
organization_id = c2rec.OI,
assembly_item_id = c2rec.AII,
alternate_bom_designator = c2rec.ABD,
assembly_type = c2rec.AST,
supply_locator_id = c2rec.SLI,
implementation_date = nvl(new_effectivity_date, NULL),
last_update_date = nvl(last_update_date, sysdate),
last_updated_by = nvl(last_updated_by, user_id),
last_update_login = nvl(last_update_login, user_id),
request_id = nvl(request_id, req_id),
program_application_id =nvl(program_application_id,
prog_appid),
program_id = nvl(program_id, prog_id),
program_update_date = nvl(program_update_date, sysdate),
process_flag = 2
WHERE transaction_id = c2rec.TI;
ELSIF (c2rec.A = G_DELETE) THEN
stmt_num := 8;
UPDATE bom_inventory_comps_interface
SET component_sequence_id = c2rec.CSI,
component_item_id = c2rec.CII,
bill_sequence_id = c2rec.BSI,
organization_id = c2rec.OI,
assembly_item_id = c2rec.AII,
alternate_bom_designator = c2rec.ABD,
assembly_type = c2rec.AST,
process_flag = 2
WHERE transaction_id = c2rec.TI;
** INSERTS ONLY - Load rows from component interface into sub comp interface
*/
stmt_num := 1;
INSERT into bom_sub_comps_interface (
SUBSTITUTE_COMPONENT_ID,
SUBSTITUTE_COMP_NUMBER,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
COMPONENT_SEQUENCE_ID,
PROCESS_FLAG,
TRANSACTION_TYPE,
SUBSTITUTE_ITEM_QUANTITY)
SELECT
SUBSTITUTE_COMP_ID,
SUBSTITUTE_COMP_NUMBER,
ORGANIZATION_ID,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, user_id),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, user_id),
NVL(LAST_UPDATE_LOGIN, user_id),
NVL(REQUEST_ID, req_id),
NVL(PROGRAM_APPLICATION_ID, prog_appid),
NVL(PROGRAM_ID, prog_id),
NVL(PROGRAM_UPDATE_DATE, sysdate),
COMPONENT_SEQUENCE_ID,
1,
G_Insert,
COMPONENT_QUANTITY
FROM bom_inventory_comps_interface
WHERE process_flag = 2
AND transaction_type = G_Insert
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND (substitute_comp_id is not null
OR
substitute_comp_number is not null);
** INSERTS ONLY - Load rows from component interface into ref desgs interface
*/
stmt_num := 1;
INSERT INTO bom_ref_desgs_interface (
COMPONENT_REFERENCE_DESIGNATOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
COMPONENT_SEQUENCE_ID,
TRANSACTION_TYPE,
PROCESS_FLAG)
SELECT
REFERENCE_DESIGNATOR,
NVL(LAST_UPDATE_DATE, SYSDATE),
NVL(LAST_UPDATED_BY, user_id),
NVL(CREATION_DATE,SYSDATE),
NVL(CREATED_BY, user_id),
NVL(LAST_UPDATE_LOGIN, user_id),
NVL(REQUEST_ID, req_id),
NVL(PROGRAM_APPLICATION_ID, prog_appid),
NVL(PROGRAM_ID, prog_id),
NVL(PROGRAM_UPDATE_DATE, sysdate),
COMPONENT_SEQUENCE_ID,
G_Insert,
1
FROM bom_inventory_comps_interface
WHERE process_flag = 2
AND transaction_type = G_Insert
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND reference_designator is not null;
SELECT count(*) INTO l_total FROM bom_inventory_components WHERE
bill_sequence_id = bill_seq_id;
SELECT 1
INTO dummy
FROM bom_inventory_components
WHERE component_sequence_id = cmp_seq_id;
SELECT count(*)
INTO dummy
FROM bom_inventory_comps_interface
WHERE component_sequence_id = cmp_seq_id
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND process_flag = 4;
SELECT component_sequence_id
INTO cnt
FROM bom_inventory_components
WHERE bill_sequence_id = bill_seq_id
AND effectivity_date = to_date(eff_date,'YYYY/MM/DD HH24:MI:SS')
AND component_item_id = cmp_item_id
AND operation_seq_num = op_seq
AND ((act = G_UPDATE AND component_sequence_id <> comp_seq_id)
OR
(act = G_Insert));
SELECT component_sequence_id
INTO cnt
FROM bom_inventory_comps_interface
WHERE bill_sequence_id = bill_seq_id
AND effectivity_date = to_date(eff_date,'YYYY/MM/DD HH24:MI:SS')
AND component_item_id = cmp_item_id
AND operation_seq_num = op_seq
AND rownum = 1
AND transaction_type in (G_Insert, G_UPDATE)
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND process_flag = 4;
SELECT count(*)
INTO dummy
FROM bom_inventory_components
WHERE bill_sequence_id = bom_id
AND component_item_id = cmp_id
AND operation_seq_num = op_num
AND implementation_date is not null
AND ((act = G_UPDATE AND component_sequence_id <> comp_seq_id)
OR
(act = G_Insert))
AND ((dis_date is null
AND to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') <
nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') +1))
OR
(dis_date is not null
AND to_date(dis_date,'YYYY/MM/DD HH24:MI:SS') > effectivity_date
AND to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') <
nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI:SS')+1)))
AND not exists -- Added for Bug 1929222
( SELECT null
FROM bom_inventory_comps_interface
WHERE bill_sequence_id = bom_id
AND process_flag = 4
AND component_item_id = cmp_id
AND operation_seq_num = op_num
AND implementation_date is not null
AND transaction_type = G_UPDATE
AND to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') >=
nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI:SS')+1)
);
SELECT count(*)
INTO dummy
FROM bom_inventory_comps_interface
WHERE bill_sequence_id = bom_id
AND process_flag = 4
AND transaction_type in (G_Insert, G_UPDATE)
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND component_item_id = cmp_id
AND operation_seq_num = op_num
AND implementation_date is not null
AND ((dis_date is null
AND to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') <
nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') +1))
OR
(dis_date is not null
AND to_date(dis_date,'YYYY/MM/DD HH24:MI:SS') > effectivity_date
AND to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') <
nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') +1)));
SELECT 1
INTO dummy
FROM mtl_system_items assy, mtl_system_items comp
WHERE comp.organization_id = org_id
AND assy.organization_id = org_id
AND comp.inventory_item_id = cmp_id
AND assy.inventory_item_id = assy_id
AND comp.bom_enabled_flag = 'Y'
AND comp.inventory_item_id <> assy.inventory_item_id
AND ((eng_bill = 1 and comp.eng_item_flag = 'N')
OR (eng_bill = 2))
AND ((assy.bom_item_type = 1 and comp.bom_item_type <> 3)
OR (assy.bom_item_type = 2 and comp.bom_item_type <> 3)
OR (assy.bom_item_type = 3)
OR (assy.bom_item_type = 4
AND (comp.bom_item_type = 4
OR (comp.bom_item_type in (2,1)
AND comp.replenish_to_order_flag = 'Y'
AND assy.base_item_id is not null
AND assy.replenish_to_order_flag = 'Y'))))
AND (assy.bom_item_type = 3
OR assy.pick_components_flag = 'Y'
OR comp.pick_components_flag = 'N')
AND (assy.bom_item_type = 3
OR comp.bom_item_type <> 2
OR (comp.bom_item_type = 2
AND ((assy.pick_components_flag = 'Y'
AND comp.pick_components_flag = 'Y')
OR (assy.replenish_to_order_flag = 'Y'
AND comp.replenish_to_order_flag = 'Y'))))
AND not(assy.bom_item_type = 4
AND assy.pick_components_flag = 'Y'
AND comp.bom_item_type = 4
AND comp.replenish_to_order_flag = 'Y');
SELECT atp_components_flag,
wip_supply_type,
replenish_to_order_flag,
pick_components_flag
INTO assy_atp_components_flag,
assy_wip_supply_type,
assy_replenish_to_order_flag,
assy_pick_components_flag
FROM mtl_system_items
WHERE inventory_item_id = assy_id
AND organization_id = org_id;
SELECT atp_components_flag,
atp_flag
INTO l_atp_comps_flag,
l_atp_flag
FROM mtl_system_items msi
WHERE inventory_item_id = cmp_id
AND organization_id = org_id;
SELECT 1
INTO dummy
FROM mtl_system_items assy, mtl_system_items comp
WHERE comp.organization_id = org_id
AND assy.organization_id = org_id
AND comp.inventory_item_id = cmp_id
AND assy.inventory_item_id = assy_id
AND (comp.atp_components_flag = 'Y' OR
comp.atp_flag = 'Y')
AND assy.atp_components_flag = 'N'
AND (nvl(assy.wip_supply_type,1) = 6
OR assy.replenish_to_order_flag = 'Y'
OR assy.pick_components_flag = 'Y');
SELECT bom_item_type
INTO dummy
FROM mtl_system_items
WHERE organization_id = org_id
AND inventory_item_id = assy_id;
SELECT distinct operation_seq_num
INTO dummy
FROM bom_operation_sequences a, bom_operational_routings b
WHERE b.organization_id = org_id
AND b.assembly_item_id = assy_id
AND operation_seq_num = op_seq
-- AND a.effectivity_date < sysdate
AND NVL(a.disable_date,sysdate+1) > sysdate
AND b.common_routing_sequence_id = a.routing_sequence_id
AND ((alt_desg is null and b.alternate_routing_designator is null)
OR
(alt_desg is not null
AND
((b.alternate_routing_designator = alt_desg)
or
(b.alternate_routing_designator is null
AND not exists
(SELECT 'No alt routing'
FROM bom_operational_routings c
WHERE c.organization_id = org_id
AND c.assembly_item_id = assy_id
AND c.alternate_routing_designator = alt_desg)))));
update_comp EXCEPTION;
X_program_update_date DATE;
l_pud DATE; -- Program Update Date
** Select all INSERTS
*/
CURSOR c1 IS
SELECT component_sequence_id CSI, bill_sequence_id BSI,
transaction_id TI, transaction_type A,
to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED,
effectivity_date EDD,
to_char(disable_date,'YYYY/MM/DD HH24:MI:SS') DD,
to_char(implementation_date,'YYYY/MM/DD HH24:MI:SS') ID,
operation_seq_num OSN, supply_locator_id SLI,
supply_subinventory SS,
msic.organization_id OI, component_item_id CII,
assembly_item_id AII, alternate_bom_designator ABD,
planning_factor PF, optional O, check_atp CATP,
msic.atp_flag AF, so_basis SB, required_for_revenue RFR,
required_to_ship RTS, mutually_exclusive_options MEO,
low_quantity LQ, high_quantity HQ,change_notice CN,
quantity_related QR, include_in_cost_rollup ICR,
shipping_allowed SA, include_on_ship_docs ISD,
component_yield_factor CYF, ici.wip_supply_type WST,
component_quantity CQ, msic.bom_item_type BITC,
msic.pick_components_flag PCF, msia.bom_item_type BITA,
msia.pick_components_flag PCFA,
msia.replenish_to_order_flag RTOF,
msic.replenish_to_order_flag RTOFC,
msia.atp_components_flag ACF,
msic.ato_forecast_control AFC
FROM mtl_system_items msic,
mtl_system_items msia,
bom_inventory_comps_interface ici
WHERE process_flag = 2
AND transaction_type = G_Insert
AND (UPPER(ici.interface_entity_type) = 'BILL'
OR ici.interface_entity_type is null)
AND msic.organization_id = ici.organization_id
AND msia.organization_id = ici.organization_id
AND msic.inventory_item_id = ici.component_item_id
AND msia.inventory_item_id = ici.assembly_item_id;
** Select all UPDATES and DELETES
*/
CURSOR c2 IS
SELECT ici.component_sequence_id CSI, ici.bill_sequence_id BSI,
ici.transaction_id TI, ici.acd_type ACD,
ici.transaction_type A,
to_char(ici.effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED,
ici.effectivity_date EDD, ici.item_num INUM,
to_char(ici.disable_date,'YYYY/MM/DD HH24:MI:SS') DD,
ici.disable_date DDD,
ici.implementation_date ID,
ici.operation_seq_num OSN, ici.supply_locator_id SLI,
ici.supply_subinventory SS, ici.creation_date CD,
ici.created_by CB, ici.change_notice CN,
ici.old_component_sequence_id OCSI,
ici.new_effectivity_date NED,
ici.include_in_cost_rollup IICR, ici.check_atp CA,
ici.pick_components PC, ici.operation_lead_time_percent OLTP,
ici.revised_item_sequence_id RISI, ici.bom_item_type BIT,
ici.new_operation_seq_num NOSN, ici.component_remarks CR,
msic.organization_id OI, ici.component_item_id CII,
ici.assembly_item_id AII, ici.alternate_bom_designator ABD,
ici.planning_factor PF, ici.optional O, ici.check_atp CATP,
msic.atp_flag AF, ici.so_basis SB,
ici.required_for_revenue RFR, ici.include_on_ship_docs IOSD,
ici.required_to_ship RTS, ici.mutually_exclusive_options MEO,
ici.low_quantity LQ, ici.high_quantity HQ,
ici.quantity_related QR, ici.include_in_cost_rollup ICR,
ici.shipping_allowed SA, ici.include_on_ship_docs ISD,
ici.component_yield_factor CYF, ici.wip_supply_type WST,
ici.component_quantity CQ, ici.attribute_category AC,
ici.attribute1 A1, ici.attribute2 A2, ici.attribute3 A3,
ici.attribute4 A4, ici.attribute5 A5, ici.attribute6 A6,
ici.attribute7 A7, ici.attribute8 A8, ici.attribute9 A9,
ici.attribute10 A10, ici.attribute11 A11, ici.attribute12 A12,
ici.attribute13 A13, ici.attribute14 A14, ici.attribute15 A15,
ici.request_id RI, ici.program_application_id PAI,
ici.program_update_date PUD, ici.program_id PI,
msic.bom_item_type BITC,
msic.pick_components_flag PCF, msia.bom_item_type BITA,
msia.pick_components_flag PCFA,
msia.replenish_to_order_flag RTOF,
msic.replenish_to_order_flag RTOFC,
msia.atp_components_flag ACF,
msic.ato_forecast_control AFC
FROM mtl_system_items msic,
mtl_system_items msia,
bom_inventory_comps_interface ici
WHERE process_flag = 2
AND transaction_type in (G_UPDATE, G_DELETE)
AND (UPPER(ici.interface_entity_type) = 'BILL'
OR ici.interface_entity_type is null)
AND msic.organization_id = ici.organization_id
AND msia.organization_id = ici.organization_id
AND msic.inventory_item_id = ici.component_item_id
AND msia.inventory_item_id = ici.assembly_item_id;
** Select all UPDATES with process_flag = 99
*/
CURSOR c3 IS
SELECT ici.component_sequence_id CSI, ici.bill_sequence_id BSI,
ici.transaction_id TI, ici.transaction_type A,
ici.implementation_date ID,
to_char(ici.effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED,
ici.effectivity_date EDD, ici.item_num INUM,
to_char(ici.disable_date,'YYYY/MM/DD HH24:MI:SS') DD,
ici.operation_seq_num OSN, ici.supply_locator_id SLI,
ici.supply_subinventory SS,
msic.organization_id OI, ici.component_item_id CII,
ici.assembly_item_id AII, ici.alternate_bom_designator ABD,
ici.planning_factor PF, ici.optional O, ici.check_atp CATP,
msic.atp_flag AF, ici.so_basis SB,
ici.required_for_revenue RFR,
ici.required_to_ship RTS, ici.mutually_exclusive_options MEO,
ici.low_quantity LQ, ici.high_quantity HQ,
ici.quantity_related QR, ici.include_in_cost_rollup ICR,
ici.shipping_allowed SA, ici.include_on_ship_docs ISD,
ici.component_yield_factor CYF, ici.wip_supply_type WST,
ici.component_quantity CQ, msic.bom_item_type BITC,
msic.pick_components_flag PCF, msia.bom_item_type BITA,
msia.pick_components_flag PCFA,
msia.replenish_to_order_flag RTOF,
msic.replenish_to_order_flag RTOFC,
msia.atp_components_flag ACF,
msic.ato_forecast_control AFC
FROM mtl_system_items msic,
mtl_system_items msia,
bom_inventory_comps_interface ici
WHERE ici.process_flag = 99
AND ici.transaction_type = G_Update
AND (UPPER(ici.interface_entity_type) = 'BILL'
OR ici.interface_entity_type is null)
AND msic.organization_id = ici.organization_id
AND msia.organization_id = ici.organization_id
AND msic.inventory_item_id = ici.component_item_id
AND msia.inventory_item_id = ici.assembly_item_id;
** FOR UPDATES and DELETES
*/
go_on := TRUE;
SELECT creation_date, created_by, operation_seq_num,item_num,
component_quantity, component_yield_factor,
component_remarks, effectivity_date, change_notice,
implementation_date, disable_date, component_item_id,
attribute_category, attribute1,
attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9,
attribute10, attribute11, attribute12, attribute13,
attribute14, attribute15, request_id,
program_application_id, program_id, program_update_date,
planning_factor, quantity_related, so_basis, optional,
mutually_exclusive_options, include_in_cost_rollup,
check_atp, shipping_allowed, required_to_ship,
required_for_revenue, include_on_ship_docs,
include_on_bill_docs, low_quantity, high_quantity,
acd_type, old_component_sequence_id, wip_supply_type,
pick_components, supply_subinventory, supply_locator_id,
operation_lead_time_percent, cost_factor, bom_item_type,
revised_item_sequence_id, bill_sequence_id
INTO X_creation_date, X_created_by, X_operation_seq_num,
X_item_num, X_component_quantity, X_component_yield_factor,
X_component_remarks, X_effectivity_date, X_change_notice,
X_implementation_date, X_disable_date, X_component_item_id,
X_attribute_category, X_attribute1,
X_attribute2, X_attribute3, X_attribute4, X_attribute5,
X_attribute6, X_attribute7, X_attribute8, X_attribute9,
X_attribute10, X_attribute11, X_attribute12, X_attribute13,
X_attribute14, X_attribute15, X_request_id,
X_program_application_id, X_program_id,
X_program_update_date,
X_planning_factor, X_quantity_related, X_so_basis,
X_optional, X_mutually_exclusive_options,
X_include_in_cost_rollup, X_check_atp, X_shipping_allowed,
X_required_to_ship, X_required_for_revenue,
X_include_on_ship_docs, X_include_on_bill_docs,
X_low_quantity, X_high_quantity, X_acd_type,
X_old_component_sequence_id, X_wip_supply_type,
X_pick_components, X_supply_subinventory,
X_supply_locator_id, X_operation_lead_time_percent,
X_cost_factor, X_bom_item_type,
X_revised_item_sequence_id, X_bill_sequence_id
FROM bom_inventory_components
WHERE component_sequence_id = c2rec.CSI
AND implementation_date is NOT NULL;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
The Decode statement in UPDATE is making the time part of the
Program Update to 12 Mid Night. To correct this the decode has been
exploded into If then Else here
*/
-- decode(c2rec.PUD, G_NullDate, '',NULL,X_program_update_date,c2rec.PUD),
--
IF (c2rec.PUD = G_NullDate) THEN
l_pud := '';
l_pud := X_program_update_date;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** ONLY for "Updates"
*/
IF (c2rec.A = G_UPDATE) THEN
/*
** For Product Family Members
*/
IF (c2rec.BITA = G_ProductFamily) THEN
/*
** Check if column is non-updatable and give error if user filled it in
*/
IF (c2rec.CD is not null -- creation date
OR c2rec.CB is not null -- created by
OR c2rec.CN is not null -- change notice
OR c2rec.SA is not null -- shipping allowed
OR c2rec.OCSI is not null -- old comp seq id
OR c2rec.PC is not null -- pick components
OR c2rec.OLTP is not null -- op lead time percent
OR c2rec.RISI is not null -- rev item seq id
OR c2rec.BIT is not null -- bom item type
OR c2rec.ACD is not null -- acd type
OR (c2rec.OSN <> 1 AND
c2rec.OSN is NOT NULL) -- operation_seq_num
OR c2rec.INUM is not null -- item_num
OR c2rec.CQ is not null -- component quantity
OR c2rec.CYF is not null -- component yield factor
OR c2rec.ID is not null -- implementation date
OR c2rec.QR is not null -- quantity related
OR c2rec.SB is not null -- so basis
OR c2rec.O is not null -- optional
OR c2rec.MEO is not null -- mutually exclusive options
OR c2rec.ICR is not null -- include in cost rollup
OR c2rec.CA is not null -- check atp
OR c2rec.RTS is not null -- required to ship
OR c2rec.RFR is not null -- required for revenue
OR c2rec.ISD is not null -- include on ship docs
OR c2rec.LQ is not null -- low quantity
OR c2rec.HQ is not null -- high quantity
OR c2rec.WST is not null -- wip supply type
OR c2rec.SS is not null -- supply subinventory
OR c2rec.SLI is not null -- supply locator id
) THEN
ret_code := INVPUOPI.mtl_log_interface_err(
org_id => c2rec.OI,
user_id => user_id,
login_id => login_id,
prog_appid => prog_appid,
prog_id => prog_id,
req_id => req_id,
trans_id => c2rec.TI,
error_text => err_text,
tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
msg_name => 'BOM_COLUMN_NOT_UPDATABLE',
err_text => err_text);
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** Update interface record with production record's values
*/
stmt_num := 6;
UPDATE bom_inventory_comps_interface
SET operation_seq_num = X_operation_seq_num,
component_item_id = X_component_item_id,
creation_date = X_creation_date,
created_by = X_created_by,
item_num = X_item_num,
component_quantity = X_component_quantity,
component_yield_factor = X_component_yield_factor,
component_remarks = decode(c2rec.CR, null,
X_component_remarks, G_NullChar, '', c2rec.CR),
effectivity_date = nvl(trunc(c2rec.NED),
X_effectivity_date),
change_notice = X_change_notice,
implementation_date = nvl(trunc(c2rec.NED),
X_effectivity_date),
disable_date = decode(c2rec.DDD, null,
X_disable_date, G_NullDate, '', c2rec.DDD),
planning_factor = nvl(c2rec.PF, X_planning_factor),
quantity_related = X_quantity_related,
so_basis = X_so_basis,
optional = X_optional,
mutually_exclusive_options = X_mutually_exclusive_options,
include_in_cost_rollup = X_include_in_cost_rollup,
check_atp = X_check_atp,
shipping_allowed = X_shipping_allowed,
required_to_ship = X_required_to_ship,
required_for_revenue = X_required_for_revenue,
include_on_ship_docs = X_include_on_ship_docs,
include_on_bill_docs = X_include_on_bill_docs,
low_quantity = X_low_quantity,
high_quantity = X_high_quantity,
acd_type = X_acd_type,
old_component_sequence_id = X_old_component_sequence_id,
bill_sequence_id = X_bill_sequence_id,
wip_supply_type = X_wip_supply_type,
pick_components = X_pick_components,
supply_subinventory = X_supply_subinventory,
supply_locator_id = X_supply_locator_id,
operation_lead_time_percent = X_operation_lead_time_percent,
revised_item_sequence_id = X_revised_item_sequence_id,
cost_factor = X_cost_factor,
bom_item_type = X_bom_item_type,
attribute_category = decode(c2rec.AC, G_NullChar, '', NULL,
X_attribute_category, c2rec.AC),
attribute1 = decode(c2rec.A1, G_NullChar, '', NULL,
X_attribute1, c2rec.A1),
attribute2 = decode(c2rec.A2, G_NullChar, '', NULL,
X_attribute2, c2rec.A2),
attribute3 = decode(c2rec.A3, G_NullChar, '', NULL,
X_attribute3, c2rec.A3),
attribute4 = decode(c2rec.A4, G_NullChar, '', NULL,
X_attribute4, c2rec.A4),
attribute5 = decode(c2rec.A5, G_NullChar, '', NULL,
X_attribute5, c2rec.A5),
attribute6 = decode(c2rec.A6, G_NullChar, '', NULL,
X_attribute6, c2rec.A6),
attribute7 = decode(c2rec.A7, G_NullChar, '', NULL,
X_attribute7, c2rec.A7),
attribute8 = decode(c2rec.A8, G_NullChar, '', NULL,
X_attribute8, c2rec.A8),
attribute9 = decode(c2rec.A9, G_NullChar, '', NULL,
X_attribute9, c2rec.A9),
attribute10 = decode(c2rec.A10, G_NullChar, '', NULL,
X_attribute10, c2rec.A10),
attribute11 = decode(c2rec.A11, G_NullChar, '', NULL,
X_attribute11, c2rec.A11),
attribute12 = decode(c2rec.A12, G_NullChar, '', NULL,
X_attribute12, c2rec.A12),
attribute13 = decode(c2rec.A13, G_NullChar, '', NULL,
X_attribute13, c2rec.A13),
attribute14 = decode(c2rec.A14, G_NullChar, '', NULL,
X_attribute14, c2rec.A14),
attribute15 = decode(c2rec.A15, G_NullChar, '', NULL,
X_attribute15, c2rec.A15),
request_id = decode(c2rec.RI, G_NullChar, '', NULL,
X_request_id, c2rec.RI),
program_application_id = decode(c2rec.PAI, G_NullNum,
'', NULL, X_program_application_id, c2rec.PAI),
program_id = decode(c2rec.PI, G_NullNum, '', NULL,
X_program_id, c2rec.PI),
program_update_date = l_pud,
process_flag = 99
WHERE transaction_id = c2rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** Update interface record with production record's values
*/
stmt_num := 6;
UPDATE bom_inventory_comps_interface
SET operation_seq_num = nvl(c2rec.NOSN, X_operation_seq_num),
component_item_id = X_component_item_id,
creation_date = X_creation_date,
created_by = X_created_by,
item_num = nvl(c2rec.INUM, X_item_num),
component_quantity = nvl(c2rec.CQ, X_component_quantity),
component_yield_factor = nvl(c2rec.CYF,
X_component_yield_factor),
component_remarks = decode(c2rec.CR, null,
X_component_remarks, G_NullChar, '', c2rec.CR),
effectivity_date = nvl(c2rec.NED, X_effectivity_date),
change_notice = X_change_notice,
implementation_date = nvl(c2rec.ID, X_implementation_date),
disable_date = decode(c2rec.DDD, null,
X_disable_date, G_NullDate, '', c2rec.DDD),
planning_factor = nvl(c2rec.PF, X_planning_factor),
quantity_related = nvl(c2rec.QR, X_quantity_related),
so_basis = nvl(c2rec.SB, X_so_basis),
optional = nvl(c2rec.O, X_optional),
mutually_exclusive_options = nvl(c2rec.MEO,
X_mutually_exclusive_options),
include_in_cost_rollup = nvl(c2rec.IICR,
X_include_in_cost_rollup),
check_atp = nvl(c2rec.CA, X_check_atp),
shipping_allowed = X_shipping_allowed,
required_to_ship = nvl(c2rec.RTS, X_required_to_ship),
required_for_revenue = nvl(c2rec.RFR,
X_required_for_revenue),
include_on_ship_docs = nvl(c2rec.IOSD,
X_include_on_ship_docs),
include_on_bill_docs = X_include_on_bill_docs,
low_quantity = decode(c2rec.LQ, G_NullNum, '', null,
X_low_quantity, c2rec.LQ),
high_quantity = decode(c2rec.HQ, G_NullNum, '', null,
X_high_quantity, c2rec.HQ),
acd_type = X_acd_type,
old_component_sequence_id = X_old_component_sequence_id,
bill_sequence_id = X_bill_sequence_id,
wip_supply_type = decode(c2rec.WST, null,
X_wip_supply_type, G_NullNum, '', c2rec.WST),
pick_components = X_pick_components,
supply_subinventory = decode(c2rec.SS, null,
X_supply_subinventory, G_NullChar, '', c2rec.SS),
supply_locator_id = decode(c2rec.SLI, null,
X_supply_locator_id, G_NullNum, '', c2rec.SLI),
-- operation_lead_time_percent = X_operation_lead_time_percent,
operation_lead_time_percent = NULL, -- for bug 1804509
revised_item_sequence_id = X_revised_item_sequence_id,
cost_factor = X_cost_factor,
bom_item_type = X_bom_item_type,
attribute_category = decode(c2rec.AC, G_NullChar, '', NULL,
X_attribute_category, c2rec.AC),
attribute1 = decode(c2rec.A1, G_NullChar, '', NULL,
X_attribute1, c2rec.A1),
attribute2 = decode(c2rec.A2, G_NullChar, '', NULL,
X_attribute2, c2rec.A2),
attribute3 = decode(c2rec.A3, G_NullChar, '', NULL,
X_attribute3, c2rec.A3),
attribute4 = decode(c2rec.A4, G_NullChar, '', NULL,
X_attribute4, c2rec.A4),
attribute5 = decode(c2rec.A5, G_NullChar, '', NULL,
X_attribute5, c2rec.A5),
attribute6 = decode(c2rec.A6, G_NullChar, '', NULL,
X_attribute6, c2rec.A6),
attribute7 = decode(c2rec.A7, G_NullChar, '', NULL,
X_attribute7, c2rec.A7),
attribute8 = decode(c2rec.A8, G_NullChar, '', NULL,
X_attribute8, c2rec.A8),
attribute9 = decode(c2rec.A9, G_NullChar, '', NULL,
X_attribute9, c2rec.A9),
attribute10 = decode(c2rec.A10, G_NullChar, '', NULL,
X_attribute10, c2rec.A10),
attribute11 = decode(c2rec.A11, G_NullChar, '', NULL,
X_attribute11, c2rec.A11),
attribute12 = decode(c2rec.A12, G_NullChar, '', NULL,
X_attribute12, c2rec.A12),
attribute13 = decode(c2rec.A13, G_NullChar, '', NULL,
X_attribute13, c2rec.A13),
attribute14 = decode(c2rec.A14, G_NullChar, '', NULL,
X_attribute14, c2rec.A14),
attribute15 = decode(c2rec.A15, G_NullChar, '', NULL,
X_attribute15, c2rec.A15),
request_id = decode(c2rec.RI, G_NullChar, '', NULL,
X_request_id, c2rec.RI),
program_application_id = decode(c2rec.PAI, G_NullNum,
'', NULL, X_program_application_id, c2rec.PAI),
program_id = decode(c2rec.PI, G_NullNum, '', NULL,
X_program_id, c2rec.PI),
program_update_date =l_pud,
process_flag = 99
WHERE transaction_id = c2rec.TI;
ELSIF (c2rec.A = G_DELETE) THEN
/*
** Set Process Flag to 4 for "Deletes"
*/
stmt_num := 10;
UPDATE bom_inventory_comps_interface
SET process_flag = 4
WHERE transaction_id = c2rec.TI;
** FOR UPDATES - Validate
*/
FOR c3rec IN c3 LOOP
BEGIN
stmt_num := 1;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
RAISE Update_Comp;
update bom_inventory_comps_interface
set operation_lead_time_percent =
(select operation_lead_time_percent
FROM bom_operation_sequences bos
WHERE c3rec.OSN = bos.operation_seq_num
AND bos.ROUTING_SEQUENCE_ID =
(select COMMON_ROUTING_SEQUENCE_ID
from BOM_OPERATIONAL_ROUTINGS bor
where bor.ASSEMBLY_ITEM_ID = c3rec.AII
and bor.ORGANIZATION_ID = c3rec.OI
and NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, NVL(c3rec.ABD, 'NONE')) = NVL(c3rec.ABD, 'NONE')
AND (c3rec.ABD IS NULL
OR (c3rec.ABD IS NOT NULL
AND ( bor.ALTERNATE_ROUTING_DESIGNATOR = c3rec.ABD
OR NOT EXISTS
(SELECT NULL
FROM BOM_OPERATIONAL_ROUTINGS bor2
WHERE bor2.ASSEMBLY_ITEM_ID = c3rec.AII
AND bor2.ORGANIZATION_ID = c3rec.OI
AND bor2.ALTERNATE_ROUTING_DESIGNATOR = c3rec.ABD)))))
AND bos.EFFECTIVITY_DATE < sysdate
AND NVL(TRUNC(bos.DISABLE_DATE), TRUNC(SYSDATE)+1) > TRUNC(SYSDATE))
WHERE transaction_id = c3rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_inventory_comps_interface
SET component_yield_factor = 1,
check_atp = 2,
include_on_ship_docs = 2,
so_basis = 2,
mutually_exclusive_options = 2,
required_to_ship = 2,
required_for_revenue = 2,
low_quantity = NULL,
high_quantity = NULL
WHERE transaction_id = c3rec.TI;
There is an update in the pld: BOMFMBM1.pld
-- R11 onwards a Model/Option Class will not be forced to have
-- a Wip_supply_type of Phantom.
-- But the user would still see a warning.
IF (c3rec.BITC = 1 or c3rec.BITC = 2) and (c3rec.WST <> 6) THEN
err_text := 'WIP supply type must be Phantom';
SELECT distinct 'I'
INTO oe_install
FROM fnd_product_installations
WHERE application_id = 300
AND status = 'I';
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
SELECT inventory_asset_flag,restrict_subinventories_code,
restrict_locators_code, location_control_code
INTO inv_asst, r_subinv, r_loc, loc_ctl
FROM mtl_system_items
WHERE inventory_item_id = c3rec.CII
AND organization_id = c3rec.OI;
SELECT locator_type
INTO sub_loc_code
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = c3rec.SS
AND organization_id = c3rec.OI
AND nvl(disable_date,TRUNC(c3rec.EDD)+1) >
TRUNC(c3rec.EDD)
AND quantity_tracked = 1;
SELECT locator_type
INTO sub_loc_code
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = c3rec.SS
AND organization_id = c3rec.OI
AND nvl(disable_date,TRUNC(c3rec.EDD)+1) >
TRUNC(c3rec.EDD)
AND quantity_tracked = 1
AND ((inv_asst = 'Y' and asset_inventory = 1)
or
(inv_asst = 'N'));
SELECT locator_type
INTO sub_loc_code
FROM mtl_secondary_inventories sub,
mtl_item_sub_inventories item
WHERE item.organization_id = sub.organization_id
AND item.secondary_inventory =
sub.secondary_inventory_name
AND item.inventory_item_id = c3rec.CII
AND sub.secondary_inventory_name = c3rec.SS
AND sub.organization_id = c3rec.OI
AND nvl(sub.disable_date,TRUNC(c3rec.EDD)+1) >
TRUNC(c3rec.EDD)
AND sub.quantity_tracked = 1;
SELECT locator_type
INTO sub_loc_code
FROM mtl_secondary_inventories sub,
mtl_item_sub_inventories item
WHERE item.organization_id = sub.organization_id
AND item.secondary_inventory =
sub.secondary_inventory_name
AND item.inventory_item_id = c3rec.CII
AND sub.secondary_inventory_name = c3rec.SS
AND sub.organization_id = c3rec.OI
AND nvl(sub.disable_date,TRUNC(c3rec.EDD)+1) >
TRUNC(c3rec.EDD)
AND sub.quantity_tracked = 1
AND ((inv_asst = 'Y' and sub.asset_inventory = 1)
or
(inv_asst = 'N'));
SELECT stock_locator_control_code
INTO org_loc
FROM mtl_parameters
WHERE organization_id = c3rec.OI;
SELECT 'loc exists'
INTO dummy
FROM mtl_item_locations
WHERE inventory_location_id = c3rec.SLI
AND organization_id = c3rec.OI
AND subinventory_code = c3rec.SS
AND nvl(disable_date,trunc(c3rec.EDD)+1) >
trunc(c3rec.EDD);
SELECT 'restricted loc exists'
INTO dummy
FROM mtl_item_locations loc,
mtl_secondary_locators item
WHERE loc.inventory_location_id = c3rec.SLI
AND loc.organization_id = c3rec.OI
AND loc.subinventory_code = c3rec.SS
AND nvl(loc.disable_date,trunc(c3rec.EDD)+1) >
trunc(c3rec.EDD)
AND loc.inventory_location_id = item.secondary_locator
AND loc.organization_id = item.organization_id
AND item.inventory_item_id = c3rec.CII;
SELECT 'loc exists'
INTO dummy
FROM mtl_item_locations
WHERE inventory_location_id = c3rec.SLI
AND organization_id = c3rec.OI
AND subinventory_code = c3rec.SS
AND nvl(disable_date,trunc(c3rec.EDD)+1) >
trunc(c3rec.EDD);
SELECT 'restricted loc exists'
INTO dummy
FROM mtl_item_locations loc,
mtl_secondary_locators item
WHERE loc.inventory_location_id = c3rec.SLI
AND loc.organization_id = c3rec.OI
AND loc.subinventory_code = c3rec.SS
AND nvl(loc.disable_date,trunc(c3rec.EDD)+1) >
trunc(c3rec.EDD)
AND loc.inventory_location_id = item.secondary_locator
AND loc.organization_id = item.organization_id
AND item.inventory_item_id = c3rec.CII;
SELECT 'loc exists'
INTO dummy
FROM mtl_item_locations
WHERE inventory_location_id = c3rec.SLI
AND organization_id = c3rec.OI
AND subinventory_code = c3rec.SS
AND nvl(disable_date,trunc(c3rec.EDD)+1) >
trunc(c3rec.EDD);
SELECT 'restricted loc exists'
INTO dummy
FROM mtl_item_locations loc,
mtl_secondary_locators item
WHERE loc.inventory_location_id = c3rec.SLI
AND loc.organization_id = c3rec.OI
AND loc.subinventory_code = c3rec.SS
AND nvl(loc.disable_date,trunc(c3rec.EDD)+1) >
trunc(c3rec.EDD)
AND loc.inventory_location_id = item.secondary_locator
AND loc.organization_id = item.organization_id
AND item.inventory_item_id = c3rec.CII;
SELECT count(*)
INTO ref_qty
FROM bom_reference_designators
WHERE component_sequence_id = c3rec.CSI;
SELECT count(*)
INTO int_ref_qty
FROM bom_ref_desgs_interface
WHERE component_sequence_id = c3rec.CSI
AND transaction_type = G_Insert
AND process_flag = 4;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
RAISE update_comp;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
WHEN Update_Comp THEN
stmt_num := 29;
UPDATE bom_inventory_comps_interface
SET process_flag = 4
WHERE transaction_id = c3rec.TI;
** FOR INSERTS - Validate
*/
/*
** Verify for uniqueness of component seq ID
*/
FOR c1rec IN c1 LOOP
BEGIN
x_bill_type := null;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT bom_item_type, assembly_item_id
FROM mtl_system_items msi,
bom_bill_of_materials bom
WHERE msi.organization_id = bom.organization_id
AND msi.inventory_item_id = bom.assembly_item_id
AND bom.bill_sequence_id = c1rec.BSI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT inventory_item_id
FROM mtl_system_items
WHERE organization_id = c1rec.OI
AND inventory_item_id = c1rec.CII
AND bom_enabled_flag = 'Y'
AND eng_item_flag = 'N'
AND bom_item_type <> G_ProductFamily
AND product_family_item_id is null
AND c1rec.CII <> x_assembly_item_id;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
RAISE Update_Comp;
SELECT 'Is pointing to a common'
INTO dummy
FROM bom_bill_of_materials
WHERE bill_sequence_id = c1rec.BSI
AND common_bill_sequence_id <> c1rec.BSI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT 1
INTO dummy
FROM bom_bill_of_materials bbom
WHERE bbom.common_bill_sequence_id = c1rec.BSI
AND bbom.organization_id <> bbom.common_organization_id
AND not exists
(SELECT null
FROM mtl_system_items msi
WHERE msi.organization_id = bbom.organization_id
AND msi.inventory_item_id = c1rec.CII
AND msi.bom_enabled_flag = 'Y'
AND ((bbom.assembly_type = 2)
OR
(bbom.assembly_type = 1
AND msi.eng_item_flag = 'N')))
AND rownum < 2;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT assembly_type
FROM bom_bill_of_materials
WHERE bill_sequence_id = c1rec.BSI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
update bom_inventory_comps_interface
set operation_lead_time_percent =
(select operation_lead_time_percent
FROM bom_operation_sequences bos
WHERE c1rec.OSN = bos.operation_seq_num
AND bos.ROUTING_SEQUENCE_ID =
(select COMMON_ROUTING_SEQUENCE_ID
from BOM_OPERATIONAL_ROUTINGS bor
where bor.ASSEMBLY_ITEM_ID = c1rec.AII
and bor.ORGANIZATION_ID = c1rec.OI
and NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, NVL(c1rec.ABD, 'NONE')) = NVL(c1rec.ABD,'NONE')
AND (c1rec.ABD IS NULL
OR (c1rec.ABD IS NOT NULL
AND ( bor.ALTERNATE_ROUTING_DESIGNATOR = c1rec.ABD
OR NOT EXISTS
(SELECT NULL
FROM BOM_OPERATIONAL_ROUTINGS bor2
WHERE bor2.ASSEMBLY_ITEM_ID = c1rec.AII
AND bor2.ORGANIZATION_ID = c1rec.OI
AND bor2.ALTERNATE_ROUTING_DESIGNATOR = c1rec.
ABD)))))
AND bos.EFFECTIVITY_DATE < sysdate
AND NVL(TRUNC(bos.DISABLE_DATE), TRUNC(SYSDATE)+1) > TRUNC(SYSDATE))
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT 1
INTO dummy
FROM eng_engineering_changes
WHERE organization_id = c1rec.OI
AND change_notice = c1rec.CN;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET component_yield_factor = 1,
check_atp = 2,
include_on_ship_docs = 2,
so_basis = 2,
mutually_exclusive_options = 2,
required_to_ship = 2,
required_for_revenue = 2,
low_quantity = NULL,
high_quantity = NULL
WHERE transaction_id = c1rec.TI;
There is an update in the pld: BOMFMBM1.pld
-- R11 onwards a Model/Option Class will not be forced to have
-- a Wip_supply_type of Phantom.
-- But the user would still see a warning.
IF (c1rec.BITC = 1 or c1rec.BITC = 2) and (c1rec.WST <> 6) THEN
err_text := 'WIP supply type must be Phantom';
SELECT distinct 'I'
INTO oe_install
FROM fnd_product_installations
WHERE application_id = 300
AND status = 'I';
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
RAISE update_comp;
SELECT inventory_asset_flag,restrict_subinventories_code,
restrict_locators_code, location_control_code
INTO inv_asst, r_subinv, r_loc, loc_ctl
FROM mtl_system_items
WHERE inventory_item_id = c1rec.CII
AND organization_id = c1rec.OI;
SELECT locator_type
INTO sub_loc_code
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = c1rec.SS
AND organization_id = c1rec.OI
AND nvl(disable_date,TRUNC(c1rec.EDD)+1) >
TRUNC(c1rec.EDD)
AND quantity_tracked = 1;
SELECT locator_type
INTO sub_loc_code
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = c1rec.SS
AND organization_id = c1rec.OI
AND nvl(disable_date,TRUNC(c1rec.EDD)+1) >
TRUNC(c1rec.EDD)
AND quantity_tracked = 1
AND ((inv_asst = 'Y' and asset_inventory = 1)
or
(inv_asst = 'N'));
SELECT locator_type
INTO sub_loc_code
FROM mtl_secondary_inventories sub,
mtl_item_sub_inventories item
WHERE item.organization_id = sub.organization_id
AND item.secondary_inventory =
sub.secondary_inventory_name
AND item.inventory_item_id = c1rec.CII
AND sub.secondary_inventory_name = c1rec.SS
AND sub.organization_id = c1rec.OI
AND nvl(sub.disable_date,TRUNC(c1rec.EDD)+1) >
TRUNC(c1rec.EDD)
AND sub.quantity_tracked = 1;
SELECT locator_type
INTO sub_loc_code
FROM mtl_secondary_inventories sub,
mtl_item_sub_inventories item
WHERE item.organization_id = sub.organization_id
AND item.secondary_inventory =
sub.secondary_inventory_name
AND item.inventory_item_id = c1rec.CII
AND sub.secondary_inventory_name = c1rec.SS
AND sub.organization_id = c1rec.OI
AND nvl(sub.disable_date,TRUNC(c1rec.EDD)+1) >
TRUNC(c1rec.EDD)
AND sub.quantity_tracked = 1
AND ((inv_asst = 'Y' and sub.asset_inventory = 1)
or
(inv_asst = 'N'));
SELECT stock_locator_control_code
INTO org_loc
FROM mtl_parameters
WHERE organization_id = c1rec.OI;
SELECT 'loc exists'
INTO dummy
FROM mtl_item_locations
WHERE inventory_location_id = c1rec.SLI
AND organization_id = c1rec.OI
AND subinventory_code = c1rec.SS
AND nvl(disable_date,trunc(c1rec.EDD)+1) >
trunc(c1rec.EDD);
SELECT 'restricted loc exists'
INTO dummy
FROM mtl_item_locations loc,
mtl_secondary_locators item
WHERE loc.inventory_location_id = c1rec.SLI
AND loc.organization_id = c1rec.OI
AND loc.subinventory_code = c1rec.SS
AND nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
trunc(c1rec.EDD)
AND loc.inventory_location_id = item.secondary_locator
AND loc.organization_id = item.organization_id
AND item.inventory_item_id = c1rec.CII;
SELECT 'loc exists'
INTO dummy
FROM mtl_item_locations
WHERE inventory_location_id = c1rec.SLI
AND organization_id = c1rec.OI
AND subinventory_code = c1rec.SS
AND nvl(disable_date,trunc(c1rec.EDD)+1) >
trunc(c1rec.EDD);
SELECT 'restricted loc exists'
INTO dummy
FROM mtl_item_locations loc,
mtl_secondary_locators item
WHERE loc.inventory_location_id = c1rec.SLI
AND loc.organization_id = c1rec.OI
AND loc.subinventory_code = c1rec.SS
AND nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
trunc(c1rec.EDD)
AND loc.inventory_location_id = item.secondary_locator
AND loc.organization_id = item.organization_id
AND item.inventory_item_id = c1rec.CII;
SELECT 'loc exists'
INTO dummy
FROM mtl_item_locations
WHERE inventory_location_id = c1rec.SLI
AND organization_id = c1rec.OI
AND subinventory_code = c1rec.SS
AND nvl(disable_date,trunc(c1rec.EDD)+1) >
trunc(c1rec.EDD);
SELECT 'restricted loc exists'
INTO dummy
FROM mtl_item_locations loc,
mtl_secondary_locators item
WHERE loc.inventory_location_id = c1rec.SLI
AND loc.organization_id = c1rec.OI
AND loc.subinventory_code = c1rec.SS
AND nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
trunc(c1rec.EDD)
AND loc.inventory_location_id = item.secondary_locator
AND loc.organization_id = item.organization_id
AND item.inventory_item_id = c1rec.CII;
RAISE update_comp;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_inventory_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
WHEN Update_Comp THEN
stmt_num := 29;
UPDATE bom_inventory_comps_interface
SET process_flag = 4
WHERE transaction_id = c1rec.TI;
Insert, update and delete component data from the interface
table, BOM_INVENTORY_COMPS_INTERFACE, into the production table,
BOM_INVENTORY_COMPONENTS.
REQUIRES
prog_appid Program application id
prog_id Program id
req_id Request id
user_id User id
login_id Login id
MODIFIES
BOM_INVENTORY_COMPONENTS
BOM_INVENTORY_COMPS_INTERFACE
RETURNS
0 if successful
SQLCODE if error
NOTES
-----------------------------------------------------------------------------*/
FUNCTION Transact_Component
( user_id NUMBER,
login_id NUMBER,
prog_appid NUMBER,
prog_id NUMBER,
req_id NUMBER,
err_text OUT NOCOPY VARCHAR2)
return integer
IS
stmt_num NUMBER := 0;
X_delete_group_seq_id NUMBER;
X_delete_type NUMBER;
** Select "CREATE" product family member and component records
*/
CURSOR c0 IS
SELECT bic.operation_seq_num OSN, bic.component_item_id CII,
bic.last_update_date LUD, bic.organization_id OI,
bic.last_updated_by LUB, bic.creation_date CD, bic.created_by CB,
bic.last_update_login LUL,
bic.item_num INUM, bic.component_quantity CQ,
bic.component_yield_factor CYF,
bic.component_remarks CR, bic.effectivity_date ED,
bic.change_notice CN,
bic.implementation_date ID, bic.disable_date DD,
bic.attribute_category AC,
bic.attribute1 A1, bic.attribute2 A2, bic.attribute3 A3,
bic.attribute4 A4,
bic.attribute5 A5,
bic.attribute6 A6, bic.attribute7 A7, bic.attribute8 A8,
bic.attribute9 A9,
bic.attribute10 A10,
bic.attribute11 A11, bic.attribute12 A12, bic.attribute13 A13,
bic.attribute14 A14, bic.attribute15 A15,
bic.planning_factor PF, bic.quantity_related QR, bic.so_basis SB,
bic.optional O,
bic.mutually_exclusive_options MEO,
bic.include_in_cost_rollup ICR,
bic.check_atp CA,
bic.shipping_allowed SA, bic.required_to_ship RTS,
bic.required_for_revenue RFR,
bic.include_on_ship_docs ISD, bic.low_quantity LQ,
bic.high_quantity HQ,
bic.component_sequence_id CSI, bic.bill_sequence_id BSI,
bic.request_id RI,
bic.program_application_id PAI, bic.program_id PI,
bic.program_update_date PUD,
bic.wip_supply_type WST, bic.supply_locator_id SLI,
bic.supply_subinventory SS, bic.transaction_id TI,
msi2.bom_item_type BIT, msi1.bom_item_type CBIT,
bom.assembly_item_id AII,
bic.operation_lead_time_percent OLTP --1851537
FROM
bom_bill_of_materials bom,
mtl_system_items msi1,
mtl_system_items msi2,
bom_inventory_comps_interface bic
WHERE bic.process_flag = 4
AND bic.transaction_type = G_Insert
AND rownum < G_rows_to_commit
AND (UPPER(bic.interface_entity_type) = 'BILL'
OR bic.interface_entity_type is null)
AND bic.bill_sequence_id = bom.bill_sequence_id
AND bom.assembly_item_id = msi2.inventory_item_id
AND bom.organization_id = msi2.organization_id
AND bic.component_item_id = msi1.inventory_item_id
AND bom.organization_id = msi1.organization_id;
** Select "Update" component records
*/
CURSOR c1 IS
SELECT component_sequence_id CSI, last_update_date LUD,
last_updated_by LUB, last_update_login LUL,
operation_seq_num OSN,
operation_lead_time_percent OLTP, -- For bug 1804509
item_num INUM, component_quantity CQ,
component_yield_factor CYF, component_remarks CR,
effectivity_date ED, implementation_date ID, disable_date DD,
planning_factor PF, quantity_related QR, so_basis SB,
optional O, mutually_exclusive_options MEO,
include_in_cost_rollup IICR, check_atp CA, required_to_ship RTS,
required_for_revenue RFR, include_on_ship_docs IOSD,
low_quantity LQ, high_quantity HQ, wip_supply_type WST,
supply_subinventory SS, supply_locator_id SLI,
attribute_category AC, attribute1 A1, attribute2 A2,
attribute3 A3, attribute4 A4, attribute5 A5, attribute6 A6,
attribute7 A7, attribute8 A8, attribute9 A9, attribute10 A10,
attribute11 A11, attribute12 A12, attribute13 A13,
attribute14 A14, attribute15 A15, request_id RI,
program_application_id PAI, program_id PI,
program_update_date PUD, transaction_id TI
FROM bom_inventory_comps_interface
WHERE process_flag = 4
AND transaction_type = G_UPDATE
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND rownum < G_rows_to_commit;
** Select "Delete" component records
*/
CURSOR c2 IS
SELECT bic.bill_sequence_id BSI, bic.assembly_type AST,
bic.organization_id OI,
bic.assembly_item_id AII, bic.alternate_bom_designator ABD,
bic.component_sequence_id CSI, bic.transaction_id TI,
msi.bom_item_type BIT, msic.bom_item_type BITC,
bic.component_item_id CII
FROM mtl_system_items msi,
mtl_system_items msic,
bom_inventory_comps_interface bic
WHERE bic.process_flag = 4
AND bic.transaction_type = G_DELETE
AND (UPPER(bic.interface_entity_type) = 'BILL'
OR bic.interface_entity_type is null)
AND msi.organization_id = bic.organization_id
AND msi.inventory_item_id = bic.assembly_item_id
AND msic.organization_id = bic.organization_id
AND msic.inventory_item_id = bic.component_item_id
AND rownum < G_rows_to_commit;
** Insert Components
*/
stmt_num := 20;
INSERT INTO bom_inventory_components
(
OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ITEM_NUM,
COMPONENT_QUANTITY,
COMPONENT_YIELD_FACTOR,
COMPONENT_REMARKS,
EFFECTIVITY_DATE,
CHANGE_NOTICE,
IMPLEMENTATION_DATE,
DISABLE_DATE,
ATTRIBUTE_CATEGORY,
OPERATION_LEAD_TIME_PERCENT, --1851537
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
SHIPPING_ALLOWED,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
LOW_QUANTITY,
HIGH_QUANTITY,
COMPONENT_SEQUENCE_ID,
BILL_SEQUENCE_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
WIP_SUPPLY_TYPE,
SUPPLY_LOCATOR_ID,
SUPPLY_SUBINVENTORY,
BOM_ITEM_TYPE
)
VALUES(
c0rec.OSN,
c0rec.CII,
c0rec.LUD,
c0rec.LUB,
c0rec.CD,
c0rec.CB,
c0rec.LUL,
c0rec.INUM,
c0rec.CQ,
c0rec.CYF,
c0rec.CR,
c0rec.ED,
c0rec.CN,
c0rec.ID,
c0rec.DD,
c0rec.AC,
c0rec.OLTP, --1851537
c0rec.A1,
c0rec.A2,
c0rec.A3,
c0rec.A4,
c0rec.A5,
c0rec.A6,
c0rec.A7,
c0rec.A8,
c0rec.A9,
c0rec.A10,
c0rec.A11,
c0rec.A12,
c0rec.A13,
c0rec.A14,
c0rec.A15,
c0rec.PF,
c0rec.QR,
c0rec.SB,
c0rec.O,
c0rec.MEO,
c0rec.ICR,
c0rec.CA,
c0rec.SA,
c0rec.RTS,
c0rec.RFR,
c0rec.ISD,
c0rec.LQ,
c0rec.HQ,
c0rec.CSI,
c0rec.BSI,
c0rec.RI,
c0rec.PAI,
c0rec.PI,
c0rec.PUD,
c0rec.WST,
c0rec.SLI,
c0rec.SS,
c0rec.CBIT);
** If product family member is added, need to update PRODUCT_FAMILY_ID
** in mtl_system_items.
*/
IF (c0rec.BIT = G_ProductFamily) THEN
BEGIN
UPDATE mtl_system_items
SET product_family_item_id = c0rec.AII
WHERE inventory_item_id = c0rec.CII
AND organization_id = c0rec.OI;
UPDATE bom_inventory_comps_interface
SET process_flag = 7
WHERE transaction_id = c0rec.TI;
** Update Components
*/
stmt_num := 63;
UPDATE bom_inventory_components
SET last_update_date = c1rec.LUD,
last_updated_by = c1rec.LUB,
last_update_login = c1rec.LUL,
operation_seq_num = c1rec.OSN,
operation_lead_time_percent = c1rec.OLTP, -- For bug 1804509
item_num = c1rec.INUM,
component_quantity = c1rec.CQ,
component_yield_factor = c1rec.CYF,
component_remarks = c1rec.CR,
effectivity_date = c1rec.ED,
implementation_date = c1rec.ID,
disable_date = c1rec.DD,
planning_factor = c1rec.PF,
quantity_related = c1rec.QR,
so_basis = c1rec.SB,
optional = c1rec.O,
mutually_exclusive_options = c1rec.MEO,
include_in_cost_rollup = c1rec.IICR,
check_atp = c1rec.CA,
required_to_ship = c1rec.RTS,
required_for_revenue = c1rec.RFR,
include_on_ship_docs = c1rec.IOSD,
low_quantity = c1rec.LQ,
high_quantity = c1rec.HQ,
wip_supply_type = c1rec.WST,
supply_subinventory = c1rec.SS,
supply_locator_id = c1rec.SLI,
attribute_category = c1rec.AC,
attribute1 = c1rec.A1,
attribute2 = c1rec.A2,
attribute3 = c1rec.A3,
attribute4 = c1rec.A4,
attribute5 = c1rec.A5,
attribute6 = c1rec.A6,
attribute7 = c1rec.A7,
attribute8 = c1rec.A8,
attribute9 = c1rec.A9,
attribute10 = c1rec.A10,
attribute11 = c1rec.A11,
attribute12 = c1rec.A12,
attribute13 = c1rec.A13,
attribute14 = c1rec.A14,
attribute15 = c1rec.A15,
request_id = c1rec.RI,
program_application_id = c1rec.PAI,
program_id = c1rec.PI,
program_update_date = c1rec.PUD
WHERE component_sequence_id = c1rec.CSI;
UPDATE bom_inventory_comps_interface
SET process_flag = 7
WHERE transaction_id = c1rec.TI;
** Delete Components
*/
stmt_num := 33;
** For Members, delete specific Allocation record.
** Also null out product family id in item master if
** this member has no more Allocations for this Product Family.
*/
DELETE FROM bom_inventory_components
WHERE component_sequence_id = c2rec.CSI;
SELECT 'yes'
INTO l_members_still_exist
FROM bom_inventory_components
WHERE bill_sequence_id = c2rec.BSI
AND component_item_id = c2rec.CII
AND rownum = 1;
UPDATE mtl_system_items
SET product_family_item_id = null
WHERE inventory_item_id = c2rec.CII
AND organization_id = c2rec.OI;
** Get the Component Delete Group name
*/
IF (X_comp_group_name is null) THEN
DECLARE
CURSOR GetCompGroup IS
SELECT delete_group_name, description
FROM bom_interface_delete_groups
WHERE UPPER(entity_name) = G_DeleteEntity;
X_comp_group_name := X_compgroup.delete_group_name;
'BOM_COMP_DELETE_GROUP_MISSING');
X_delete_group_seq_id := null;
SELECT delete_group_sequence_id, delete_type
INTO X_delete_group_seq_id, X_delete_type
FROM bom_delete_groups
WHERE delete_group_name = X_comp_group_name
AND organization_id = c2rec.OI;
IF (X_delete_type <> 4) THEN
X_error_message := FND_MESSAGE.Get_String('BOM',
'BOM_DELETE_GROUP_INVALID');
X_new_group_seq_id := Modal_Delete.Delete_Manager_Oi(
new_group_seq_id => X_delete_group_seq_id,
name => X_comp_group_name,
group_desc => X_comp_group_description,
org_id => c2rec.OI,
bom_or_eng => c2rec.AST,
del_type => 4,
ent_bill_seq_id => c2rec.BSI,
ent_rtg_seq_id => null,
ent_inv_item_id => c2rec.AII,
ent_alt_designator => c2rec.ABD,
ent_comp_seq_id => c2rec.CSI,
ent_op_seq_id => null,
user_id => user_id,
err_text => err_text);
UPDATE bom_inventory_comps_interface
SET process_flag = 7
WHERE transaction_id = c2rec.TI;
DELETE from bom_inventory_comps_interface
WHERE process_flag = 7
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND rownum < G_rows_to_commit;