[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id OI, substitute_comp_number SCN,
substitute_component_id SCI, transaction_id TI,
transaction_type A, new_sub_comp_id NSCI,
new_sub_comp_number NSCN
FROM bom_sub_comps_interface
WHERE process_flag = 1
AND ((transaction_type in (G_Insert, G_Update, G_Delete)
AND substitute_component_id is null)
OR
(transaction_type = G_Update
AND new_sub_comp_id is null
AND new_sub_comp_number is not null))
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 component_sequence_id CSI,
transaction_id TI, organization_id OI,
bill_sequence_id BSI, assembly_item_id AII,
assembly_item_number AIN, alternate_bom_designator ABD,
component_item_id CII, component_item_number CIN,
operation_seq_num OSN, transaction_type A,
to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED
FROM bom_sub_comps_interface
WHERE process_flag = 1
AND transaction_type in (G_Insert, G_Update, G_Delete)
AND component_sequence_id is null
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 transaction_id TI, organization_id OI,
component_sequence_id CSI, substitute_item_quantity SIQ,
transaction_type A
FROM bom_sub_comps_interface
WHERE process_flag = 1
AND transaction_type in (G_Insert, G_Update, G_Delete)
AND component_sequence_id is not null
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 component_sequence_id CSI
FROM bom_sub_comps_interface
WHERE process_flag = 99
AND transaction_type in (G_Insert, G_Update)
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))
GROUP BY component_sequence_id;
/** G_INSERT is 'CREATE'. Update 'INSERT' to 'CREATE' **/
stmt_num := 0.5 ;
UPDATE bom_sub_comps_interface
SET transaction_type = G_Insert
WHERE process_flag = 1
AND upper(transaction_type) = 'INSERT'
AND rownum < G_rows_to_commit;
UPDATE bom_sub_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(interface_entity_type) = 'BILL'
OR 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;
UPDATE bom_sub_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(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND rownum < G_rows_to_commit;
** FOR ALL RECORDS - Update substitute component id if null
** FOR UPDATES - Update new substitute component id if null
*/
stmt_num := 3;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c0rec.TI;
IF (c0rec.A = G_Update AND c0rec.NSCI is null
AND c0rec.NSCN is not null) THEN
ret_code := INVPUOPI.mtl_pr_parse_flex_name(
org_id => c0rec.OI,
flex_code => 'MSTK',
flex_name => c0rec.NSCN,
flex_id => c0rec.NSCI,
set_id => -1,
err_text => err_text);
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c0rec.TI;
UPDATE bom_sub_comps_interface
SET substitute_component_id = c0rec.SCI,
new_sub_comp_id = c0rec.NSCI
WHERE transaction_id = c0rec.TI;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT bill_sequence_id, assembly_type
INTO c1rec.BSI, X_dummy
FROM bom_bill_of_materials
WHERE organization_id = c1rec.OI
AND assembly_item_id = c1rec.AII
AND nvl(alternate_bom_designator, 'NONE') =
nvl(c1rec.ABD, 'NONE');
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT component_sequence_id
INTO c1rec.CSI
FROM bom_inventory_components
WHERE bill_sequence_id = c1rec.BSI
AND component_item_id = c1rec.CII
AND operation_seq_num = c1rec.OSN
AND effectivity_date = to_date(c1rec.ED,'YYYY/MM/DD HH24:MI:SS');
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_sub_comps_interface
SET component_sequence_id = c1rec.CSI,
assembly_item_id = c1rec.AII,
component_item_id = c1rec.CII,
bill_sequence_id = c1rec.BSI
WHERE transaction_id = c1rec.TI;
** FOR INSERTS - Set substitute component quantity if null
** FOR ALL RECORDS - Set defaults and process_flag for valid records
*/
stmt_num := 14;
IF (c2rec.A = G_Insert) THEN
IF (c2rec.SIQ is null) THEN
BEGIN
select component_quantity
into c2rec.SIQ
from bom_inventory_components
where component_sequence_id = c2rec.CSI;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
UPDATE bom_sub_comps_interface
SET process_flag = 99,
substitute_item_quantity = c2rec.SIQ,
acd_type = null,
last_update_date = nvl(last_update_date,sysdate),
last_updated_by = nvl(last_updated_by,user_id),
creation_date = nvl(creation_date,sysdate),
created_by = nvl(created_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)
WHERE transaction_id = c2rec.TI;
ELSIF (c2rec.A = G_Update) THEN
stmt_num := 16;
UPDATE bom_sub_comps_interface
SET process_flag = 99,
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)
WHERE transaction_id = c2rec.TI;
ELSIF (c2rec.A = G_Delete) THEN
stmt_num := 17;
UPDATE bom_sub_comps_interface
SET process_flag = 2
WHERE transaction_id = c2rec.TI;
** FOR INSERTS AND UPDATES - Set records with same component_sequence_id
** with the same txn id for set processing
*/
commit_cnt := 0;
SELECT mtl_system_items_interface_s.nextval
INTO dummy_txn
FROM sys.dual;
UPDATE bom_sub_comps_interface
SET transaction_id = dummy_txn,
process_flag = 2
WHERE component_sequence_id = c3rec.CSI
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND process_flag = 99;
** If it's an UPDATE, then this check is unnecessary if new_sub_comp_id is
** not filled in.
*/
stmt_num := 1;
SELECT 1
INTO dummy
FROM bom_substitute_components a, bom_sub_comps_interface b
WHERE b.transaction_id = trans_id
AND (UPPER(b.interface_entity_type) = 'BILL'
OR b.interface_entity_type is null)
AND (b.transaction_type = G_Insert
OR (b.transaction_type= G_Update
AND b.new_sub_comp_id is not null))
AND a.component_sequence_id = b.component_sequence_id
AND a.substitute_component_id = decode(b.transaction_type, G_Insert,
b.substitute_component_id, G_Update, b.new_sub_comp_id)
AND rownum = 1;
SELECT count(*)
INTO dummy
FROM bom_sub_comps_interface a
WHERE transaction_id = trans_id
AND (transaction_type = G_Insert
OR (transaction_type= G_Update AND new_sub_comp_id is not null))
AND (UPPER(a.interface_entity_type) = 'BILL'
OR a.interface_entity_type is null)
AND exists
(SELECT 'same substitute'
FROM bom_sub_comps_interface b
WHERE b.transaction_id = trans_id
AND b.rowid <> a.rowid
AND (b.transaction_type = G_Insert
OR (b.transaction_type = G_Update
AND b.new_sub_comp_id is not null))
AND (UPPER(b.interface_entity_type) = 'BILL'
OR b.interface_entity_type is null)
AND decode(b.transaction_type, G_Insert,
b.substitute_component_id, G_Update, b.new_sub_comp_id) =
decode(a.transaction_type, G_Insert,
a.substitute_component_id, G_Update, a.new_sub_comp_id)
AND b.process_flag not in (3,7))
AND process_flag not in (3,7);
X_program_update_date DATE;
** Get UPDATE and DELETEs for row by row processing
*/
CURSOR c2 IS
SELECT component_sequence_id CSI, substitute_component_id SCI,
creation_date CD, created_by CB, change_notice CN,
substitute_item_quantity SIQ, new_sub_comp_id NSCI,
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, acd_type ACD,
transaction_id TI, transaction_type A
FROM bom_sub_comps_interface
WHERE process_flag = 2
AND 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;
** Get UPDATE and INSERTs for set processing
*/
CURSOR c1 IS
SELECT component_sequence_id CSI, count(*) CNT,
transaction_id TI, assembly_item_id AII,
organization_id OI
FROM bom_sub_comps_interface
WHERE process_flag = 2
AND transaction_type in (G_Insert, G_Update)
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
GROUP BY transaction_id, component_sequence_id,
organization_id, assembly_item_id;
** Get UPDATE and INSERTs for row by row processing
*/
CURSOR c3 IS
SELECT bsci.component_sequence_id CSI, bsci.substitute_component_id SCI,
bsci.new_sub_comp_id NSCI, bsci.transaction_id TI,
bsci.transaction_type TT, bsci.substitute_item_quantity SIQ,
bbom.bill_sequence_id BSI, bbom.organization_id OI,
bbom.assembly_item_id AII, bbom.assembly_type AST,
bic.component_item_id CII,
bsci.change_notice CN
FROM bom_inventory_components bic,
bom_bill_of_materials bbom,
bom_sub_comps_interface bsci
WHERE bsci.process_flag = 2
AND bsci.transaction_type in (G_Insert, G_Update)
AND (UPPER(bsci.interface_entity_type) = 'BILL'
OR bsci.interface_entity_type is null)
AND bsci.component_sequence_id = bic.component_sequence_id
AND bic.bill_sequence_id = bbom.bill_sequence_id
AND rownum < G_rows_to_commit;
** FOR UPDATES and DELETES
*/
continue_loop := TRUE;
SELECT bsc.creation_date, bsc.created_by,
bsc.substitute_item_quantity, bsc.acd_type,
bsc.change_notice,
bsc.attribute_category, bsc.attribute1,
bsc.attribute2, bsc.attribute3, bsc.attribute4,
bsc.attribute5, bsc.attribute6, bsc.attribute7,
bsc.attribute8, bsc.attribute9,
bsc.attribute10, bsc.attribute11, bsc.attribute12,
bsc.attribute13,
bsc.attribute14, bsc.attribute15, bsc.request_id,
bsc.program_application_id, bsc.program_id,
bsc.program_update_date
INTO X_creation_date, X_created_by,
X_substitute_item_quantity, X_acd_type, X_change_notice,
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
FROM bom_substitute_components bsc,
bom_inventory_components bic
WHERE bsc.component_sequence_id = c2rec.CSI
AND bsc.substitute_component_id = c2rec.SCI
AND bsc.component_sequence_id = bic.component_sequence_id
AND bic.implementation_date is not null;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** FOR UPDATES
*/
IF (c2rec.A = G_Update) THEN
/*
** Check if column is non-updatable and give error if user filled it in
*/
stmt_num := 2;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** Update interface record with production record's values
*/
stmt_num := 3;
UPDATE bom_sub_comps_interface
SET creation_date = X_creation_date,
created_by = X_created_by,
substitute_item_quantity = nvl(c2rec.SIQ,
X_substitute_item_quantity),
change_notice = X_change_notice,
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 = decode(c2rec.PUD, G_NullDate, '',
NULL,X_program_update_date, c2rec.PUD)
WHERE transaction_id = c2rec.TI
AND transaction_type = G_Update
AND component_sequence_id = c2rec.CSI
AND substitute_component_id = c2rec.SCI;
ELSIF (c2rec.A = G_Delete) THEN
/*
** Set Process Flag to 4 for "Deletes"
*/
stmt_num := 4;
UPDATE bom_sub_comps_interface
SET process_flag = 4
WHERE transaction_id = c2rec.TI;
SELECT bbom.assembly_item_id, bbom.organization_id,
bbom.assembly_type, bic.component_item_id,
bic.bom_item_type, mtl.bom_item_type
INTO assy_id_dummy, org_id_dummy, assy_type_dummy,
comp_id_dummy, comp_type, dummy
FROM bom_inventory_components bic,
bom_bill_of_materials bbom,
mtl_system_items mtl
WHERE bic.component_sequence_id = c1rec.CSI
AND bic.implementation_date is not null
AND bbom.bill_sequence_id = bic.bill_sequence_id
AND mtl.inventory_item_id = bbom.assembly_item_id
AND mtl.organization_id = bbom.organization_id;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
** FOR INSERTS and UPDATES - row by row processing
*/
continue_loop := TRUE;
IF (c3rec.TT = G_Insert
OR (c3rec.TT = G_Update AND c3rec.NSCI is not null)) THEN
SELECT 1
INTO dummy
FROM mtl_system_items
WHERE organization_id = c3rec.OI
AND inventory_item_id = decode(c3rec.TT, G_Insert, c3rec.SCI,
G_Update, c3rec.NSCI)
AND bom_enabled_flag = 'Y'
AND bom_item_type = 4
AND ((c3rec.AST = 2)
OR
(c3rec.AST = 1 AND eng_item_flag = 'N'));
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
IF (c3rec.TT = G_Insert
OR (c3rec.TT = G_Update AND c3rec.NSCI is not null)) THEN
SELECT 1
INTO dummy
FROM bom_bill_of_materials bbom
WHERE bbom.common_bill_sequence_id = c3rec.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 = decode(c3rec.TT, G_Insert,
c3rec.SCI, G_Update, c3rec.NSCI)
AND msi.bom_enabled_flag = 'Y'
AND ((bbom.assembly_type = 2)
OR
(bbom.assembly_type = 1
AND msi.eng_item_flag = 'N')));
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
IF ((c3rec.TT = G_Update AND c3rec.NSCI in (c3rec.AII, c3rec.CII))
OR
(c3rec.TT = G_Insert AND c3rec.SCI in (c3rec.AII,c3rec.CII))) THEN
err_text := 'Substitute item is the same as assembly item or component item';
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
If (c3rec.CN is not NULL) and (c3rec.TT = G_INSERT) THEN
BEGIN
SELECT 1
INTO dummy
FROM eng_engineering_changes
WHERE organization_id = c3rec.OI
AND change_notice = c3rec.CN;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_sub_comps_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_sub_comps_interface
SET process_flag = 4
WHERE transaction_id = c3rec.TI;
Insert, update and delete substitute component data from the interface
table, BOM_SUB_COMPS_INTERFACE, into the production table,
BOM_SUBSTITUTE_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_SUBSTITITE_COMPONENTS
BOM_SUB_COMPS_INTERFACE
RETURNS
0 if successful
SQLCODE if error
NOTES
-----------------------------------------------------------------------------*/
FUNCTION Transact_Substitute_Component
( user_id NUMBER,
login_id NUMBER,
prog_appid NUMBER,
prog_id NUMBER,
req_id NUMBER,
err_text OUT VARCHAR2)
return integer
IS
stmt_num NUMBER := 0;
** Select "Update" substitute component records
*/
CURSOR c1 IS
SELECT component_sequence_id CSI, substitute_component_id SCI,
new_sub_comp_id NSCI,
substitute_item_quantity SIQ,
last_update_date LUD, last_updated_by LUB,
last_update_login LUL,
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_sub_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" substitute component records
*/
CURSOR c2 IS
SELECT component_sequence_id CSI, substitute_component_id SCI,
transaction_id TI
FROM bom_sub_comps_interface
WHERE process_flag = 4
AND transaction_type = G_Delete
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND rownum < G_rows_to_commit;
** Insert Substitute Components
*/
stmt_num := 1;
INSERT into BOM_SUBSTITUTE_COMPONENTS
(
SUBSTITUTE_COMPONENT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
SUBSTITUTE_ITEM_QUANTITY,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
COMPONENT_SEQUENCE_ID,
CHANGE_NOTICE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE
)
SELECT
SUBSTITUTE_COMPONENT_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
created_by,
last_update_login,
SUBSTITUTE_ITEM_QUANTITY,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
COMPONENT_SEQUENCE_ID,
CHANGE_NOTICE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE
FROM bom_sub_comps_interface
WHERE process_flag = 4
AND transaction_type = G_Insert
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND rownum < 500;
UPDATE bom_sub_comps_interface bsi
SET process_flag = 7
WHERE process_flag = 4
AND transaction_type = G_Insert
AND (UPPER(bsi.interface_entity_type) = 'BILL'
OR bsi.interface_entity_type is null)
AND exists
(SELECT null
FROM bom_substitute_components bsc
WHERE bsc.component_sequence_id = bsi.component_sequence_id
AND bsc.substitute_component_id = bsi.substitute_component_id
AND nvl(bsc.acd_type,999) = nvl(bsi.acd_type,999));
** Update Substitute Components
*/
stmt_num := 3;
UPDATE bom_substitute_components
SET substitute_component_id = nvl(c1rec.NSCI, c1rec.SCI),
substitute_item_quantity = c1rec.SIQ,
last_update_date = c1rec.LUD,
last_updated_by = c1rec.LUB,
last_update_login = c1rec.LUL,
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
AND substitute_component_id = c1rec.SCI;
UPDATE bom_sub_comps_interface
SET process_flag = 7
WHERE transaction_id = c1rec.TI;
** Delete Substitute Components
*/
stmt_num := 6;
DELETE FROM bom_substitute_components
WHERE component_sequence_id = c2rec.CSI
AND substitute_component_id = c2rec.SCI;
UPDATE bom_sub_comps_interface
SET process_flag = 7
WHERE transaction_id = c2rec.TI;
DELETE from bom_sub_comps_interface
WHERE process_flag = 7
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND rownum < G_rows_to_commit;