[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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_ref_desgs_interface
WHERE process_flag = 1
AND transaction_type in (G_Insert, G_Update, G_Delete)
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND component_sequence_id 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, transaction_type A
FROM bom_ref_desgs_interface
WHERE process_flag = 1
AND transaction_type in (G_Insert, G_Update, G_Delete)
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND component_sequence_id is not 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_ref_desgs_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_ref_desgs_interface
SET transaction_type = G_Insert
WHERE process_flag = 1
AND upper(transaction_type) = 'INSERT'
AND rownum < G_rows_to_commit;
UPDATE bom_ref_desgs_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_ref_desgs_interface
SET transaction_id = mtl_system_items_interface_s.nextval,
transaction_type = upper(transaction_type)
WHERE transaction_id is null
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND process_flag = 1
AND rownum < G_rows_to_commit;
UPDATE bom_ref_desgs_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_ref_desgs_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_ref_desgs_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_ref_desgs_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_ref_desgs_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_ref_desgs_interface
SET component_sequence_id = c1rec.CSI,
assembly_item_id = c1rec.AII,
component_item_id = c1rec.CII,
bill_sequence_id = c1rec.BSI,
organization_id = c1rec.OI
WHERE transaction_id = c1rec.TI;
IF (c2rec.A = G_Insert) THEN
stmt_num := 11;
UPDATE bom_ref_desgs_interface
SET process_flag = 2, -- Changed from 99 to 2, bug 1342291
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 := 12;
UPDATE bom_ref_desgs_interface
SET process_flag = 2, -- Changed from 99 to 2, bug 1342291
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 := 13;
UPDATE bom_ref_desgs_interface
SET process_flag = 2
WHERE transaction_id = c2rec.TI;
SELECT mtl_system_items_interface_s.nextval
INTO dummy_txn
FROM sys.dual;
-- Only INSERTS and UPDATES have process_flag = 99
UPDATE bom_ref_desgs_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_designator is
** not filled in.
*/
stmt_num := 1;
SELECT 1
INTO dummy
FROM bom_reference_designators a, bom_ref_desgs_interface b
WHERE b.transaction_id = trans_id
AND (b.transaction_type = G_Insert
OR (b.transaction_type= G_Update
AND b.new_designator is not null))
AND (UPPER(b.interface_entity_type) = 'BILL'
OR b.interface_entity_type is null)
AND a.component_sequence_id = b.component_sequence_id
AND a.component_reference_designator = decode(b.transaction_type,
G_Insert, b.component_reference_designator, G_Update,
b.new_designator)
AND rownum = 1;
SELECT count(*)
INTO dummy
FROM bom_ref_desgs_interface a
WHERE transaction_id = trans_id
AND (transaction_type = G_Insert
OR (transaction_type= G_Update AND new_designator is not null))
AND (UPPER(a.interface_entity_type) = 'BILL'
OR a.interface_entity_type is null)
AND exists
(SELECT 'same designator'
FROM bom_ref_desgs_interface b
WHERE b.rowid <> a.rowid
AND (b.transaction_type = G_Insert
OR (b.transaction_type = G_Update
AND b.new_designator 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.component_reference_designator, G_Update,
b.new_designator) = decode(a.transaction_type, G_Insert,
a.component_reference_designator, G_Update,
a.new_designator)
AND b.component_sequence_id = a.component_sequence_id
AND b.process_flag not in (3,7))
AND process_flag not in (3,7);
SELECT count(*)
INTO ref_qty
FROM bom_reference_designators
WHERE component_sequence_id = cmp_seq_id;
SELECT count(*)
INTO int_ref_qty
FROM bom_ref_desgs_interface
WHERE component_sequence_id = cmp_seq_id
AND transaction_type = G_Insert
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND process_flag not in (3,7);
SELECT count(*)
INTO int_del_ref_qty
FROM bom_ref_desgs_interface
WHERE component_sequence_id = cmp_seq_id
AND transaction_type = G_Delete
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
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, component_reference_designator CRD,
ref_designator_comment RDC,
creation_date CD, created_by CB, change_notice CN,
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_ref_desgs_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_ref_desgs_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 INSERTS for set processing
*/
CURSOR c3 IS
SELECT change_notice CN, transaction_id TI,
organization_id OI
FROM bom_ref_desgs_interface
WHERE process_flag = 2
AND transaction_type in (G_Insert)
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null);
** FOR UPDATES and DELETES
*/
continue_loop := TRUE;
SELECT brd.creation_date, brd.created_by,
brd.ref_designator_comment, brd.acd_type,
brd.change_notice,
brd.attribute_category, brd.attribute1,
brd.attribute2, brd.attribute3, brd.attribute4,
brd.attribute5, brd.attribute6, brd.attribute7,
brd.attribute8, brd.attribute9,
brd.attribute10, brd.attribute11, brd.attribute12,
brd.attribute13,
brd.attribute14, brd.attribute15, brd.request_id,
brd.program_application_id, brd.program_id,
brd.program_update_date, bic.quantity_related,
bic.component_quantity
INTO X_creation_date, X_created_by,
X_ref_designator_comment, 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, X_quantity_related,
X_component_quantity
FROM bom_reference_designators brd,
bom_inventory_components bic
WHERE brd.component_sequence_id = c2rec.CSI
AND brd.component_reference_designator = c2rec.CRD
AND brd.component_sequence_id = bic.component_sequence_id
AND bic.implementation_date is not null;
UPDATE bom_ref_desgs_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 an error if user filled it in
*/
stmt_num := 2;
UPDATE bom_ref_desgs_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** Update interface record with production record's values
*/
stmt_num := 3;
UPDATE bom_ref_desgs_interface
SET creation_date = X_creation_date,
created_by = X_created_by,
ref_designator_comment = nvl(c2rec.RDC,
X_ref_designator_comment),
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;
ELSIF (c2rec.A = G_Delete) THEN
/*
** Count reference designators if quantity related is Yes
*/
stmt_num := 4;
UPDATE bom_ref_desgs_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** Set Process Flag to 4 for "Deletes"
*/
stmt_num := 5;
UPDATE bom_ref_desgs_interface
SET process_flag = 4
WHERE transaction_id = c2rec.TI;
SELECT 1
INTO dummy
FROM eng_engineering_changes
WHERE organization_id = c3rec.OI
AND change_notice = c3rec.CN;
UPDATE bom_ref_desgs_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
SELECT count(*)
INTO dummy
FROM bom_ref_desgs_interface
WHERE transaction_id = c1rec.TI
AND component_reference_designator is null;
UPDATE bom_ref_desgs_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT bbom.assembly_item_id, bbom.organization_id,
bbom.assembly_type, bic.component_item_id,
bic.bom_item_type, mtl.bom_item_type,
bic.component_quantity, bic.quantity_related
INTO assy_id_dummy, org_id_dummy, assy_type_dummy,
comp_id_dummy, comp_type, dummy,
X_component_quantity, X_quantity_related
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_ref_desgs_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_ref_desgs_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_ref_desgs_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_ref_desgs_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_ref_desgs_interface
SET process_flag = 4
WHERE transaction_id = c1rec.TI;
Insert, update and delete reference designator data from the interface
table, BOM_REF_DESGS_INTERFACE, into the production table,
BOM_REFERENCE_DESIGNATORS.
REQUIRES
prog_appid Program application id
prog_id Program id
req_id Request id
user_id User id
login_id Login id
MODIFIES
BOM_REFERENCE_DESIGNATORS
BOM_REF_DESGS_INTERFACE
RETURNS
0 if successful
SQLCODE if error
NOTES
-----------------------------------------------------------------------------*/
FUNCTION Transact_Reference_Designator
( 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" reference designator records
*/
CURSOR c1 IS
SELECT component_sequence_id CSI, component_reference_designator CRD,
new_designator ND, ref_designator_comment RDC,
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_ref_desgs_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" reference designators records
*/
CURSOR c2 IS
SELECT component_sequence_id CSI, component_reference_designator CRD,
transaction_id TI
FROM bom_ref_desgs_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 Reference Designators
*/
stmt_num := 1;
INSERT into BOM_REFERENCE_DESIGNATORS
(
COMPONENT_REFERENCE_DESIGNATOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REF_DESIGNATOR_COMMENT,
CHANGE_NOTICE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
COMPONENT_SEQUENCE_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE
)
SELECT
COMPONENT_REFERENCE_DESIGNATOR,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REF_DESIGNATOR_COMMENT,
CHANGE_NOTICE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
COMPONENT_SEQUENCE_ID,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE
FROM bom_ref_desgs_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_ref_desgs_interface brdi
SET process_flag = 7
WHERE process_flag = 4
AND transaction_type = G_Insert
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND exists
(SELECT null
FROM bom_reference_designators brd
WHERE brd.component_sequence_id = brdi.component_sequence_id
AND brd.component_reference_designator =
brdi.component_reference_designator
AND nvl(brd.acd_type,999) = nvl(brdi.acd_type,999));
Moved the UPDATE bom_ref_desgs_interface brdi outside
the LOOP as this was causing the Performance Issues when there are
large number record in the interface table.
This is due to the reason that for every 500 records inserted in
the production table, the interface table is being updated once,
which is not required.
So moving this outside the loop, this ensures that the process flag is
updated to 7 for all processed rows only once after all the rows
are inserted.
The 11.0 bug fix uses a HINT on BOM_REFERENCE_DESIGNATORS Table in the
update stmt to force the Unique Index. This might not be acceptable in 11.5
given the difference in the optimizer. This is therefore removed in 11.5.
If the 11.5 instance faces performance issues, this hint can be introduced
to see if there is any performance benefit.
*/
/*
** Update Reference Designators
*/
stmt_num := 3;
UPDATE bom_reference_designators
SET component_reference_designator = nvl(c1rec.ND, c1rec.CRD),
ref_designator_comment = c1rec.RDC,
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 component_reference_designator = c1rec.CRD;
UPDATE bom_ref_desgs_interface
SET process_flag = 7
WHERE transaction_id = c1rec.TI;
** Delete Reference Designators
*/
stmt_num := 6;
DELETE FROM bom_reference_designators
WHERE component_sequence_id = c2rec.CSI
AND component_reference_designator = c2rec.CRD;
UPDATE bom_ref_desgs_interface
SET process_flag = 7
WHERE transaction_id = c2rec.TI;
DELETE from bom_ref_desgs_interface
WHERE process_flag = 7
AND (UPPER(interface_entity_type) = 'BILL'
OR interface_entity_type is null)
AND rownum < G_rows_to_commit;