[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** Select all INSERTS
*/
CURSOR c1 IS
SELECT organization_id OI, organization_code OC,
assembly_item_id AII, item_number AIN,
common_assembly_item_id CAII, common_item_number CAIN,
common_organization_id COI, common_org_code COC,
alternate_bom_designator ABD, transaction_id TI,
bill_sequence_id BSI, common_bill_sequence_id CBSI,
revision R, last_update_date LUD, last_updated_by LUB,
creation_date CD, created_by CB, last_update_login LUL,
transaction_type A, assembly_type AST
FROM bom_bill_of_mtls_interface
WHERE process_flag = 1
AND transaction_type = G_Insert
AND (all_org = 1
OR
(all_org = 2 AND organization_id = org_id))
AND rownum < G_rows_to_commit;
** Select all UPDATEs and DELETEs
*/
CURSOR c2 IS
SELECT organization_id OI, organization_code OC,
assembly_item_id AII, item_number AIN,
common_assembly_item_id CAII, common_item_number CAIN,
common_organization_id COI, common_org_code COC,
alternate_bom_designator ABD, transaction_id TI,
bill_sequence_id BSI, common_bill_sequence_id CBSI,
revision R, last_update_date LUD, last_updated_by LUB,
creation_date CD, created_by CB, last_update_login LUL,
transaction_type A, assembly_type AST
FROM bom_bill_of_mtls_interface
WHERE process_flag = 1
AND transaction_type in (G_Update, G_Delete)
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_bill_of_mtls_interface
SET transaction_type = G_Insert
WHERE process_flag = 1
AND upper(transaction_type) = 'INSERT'
AND rownum < G_rows_to_commit;
UPDATE bom_bill_of_mtls_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 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_bill_of_mtls_interface ori
SET transaction_id = mtl_system_items_interface_s.nextval,
transaction_type = upper(transaction_type),
bill_sequence_id = decode(upper(transaction_type), G_Insert,
bom_inventory_components_s.nextval,
bill_sequence_id)
WHERE transaction_id is null
AND upper(transaction_type) in (G_Insert, G_Update, G_Delete)
AND process_flag = 1
AND rownum < G_rows_to_commit;
** FOR INSERTs - Assign values
*/
WHILE continue_loop LOOP
commit_cnt := 0;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT bom_item_type
FROM mtl_system_items
WHERE organization_id = c1rec.OI
AND inventory_item_id = c1rec.AII;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
** For Product Families - Insert revision record
*/
IF (c1rec.R is not null) THEN
INSERT into mtl_item_revisions_interface
(INVENTORY_ITEM_ID,
ORGANIZATION_ID,
REVISION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
EFFECTIVITY_DATE,
IMPLEMENTATION_DATE,
TRANSACTION_ID,
PROCESS_FLAG,
TRANSACTION_TYPE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
VALUES
(c1rec.AII, c1rec.OI, UPPER(c1rec.R),
nvl(c1rec.LUD, sysdate),
nvl(c1rec.LUB, user_id),
nvl(c1rec.CD, sysdate),
nvl(c1rec.CB, user_id),
nvl(c1rec.LUL, user_id),
sysdate,
sysdate,
mtl_system_items_interface_s.nextval,
2,
G_Insert,
req_id,
prog_appid,
prog_id,
sysdate);
UPDATE bom_bill_of_mtls_interface
SET organization_id = nvl(organization_id, c1rec.OI),
assembly_item_id = nvl(assembly_item_id, c1rec.AII),
alternate_bom_designator = null,
specific_assembly_comment = null,
pending_from_ecn = null,
common_bill_sequence_id = c1rec.BSI,
common_organization_id = null,
common_assembly_item_id = null,
assembly_type = 1,
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),
process_flag = 2
WHERE transaction_id = c1rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT bill_sequence_id
INTO c1rec.CBSI
FROM bom_bill_of_materials
WHERE organization_id = nvl(c1rec.COI, c1rec.OI)
AND assembly_item_id = c1rec.CAII
AND nvl(alternate_bom_designator, 'NONE') =
nvl(c1rec.ABD, 'NONE');
SELECT bill_sequence_id
INTO c1rec.CBSI
FROM bom_bill_of_mtls_interface
WHERE organization_id = nvl(c1rec.COI, c1rec.OI)
AND transaction_type = G_Insert
AND assembly_item_id = c1rec.CAII
AND nvl(alternate_bom_designator, 'NONE') =
nvl(c1rec.ABD, 'NONE')
AND process_flag not in (3,7)
AND rownum = 1;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT assembly_item_id, organization_id
INTO c1rec.CAII, c1rec.COI
FROM bom_bill_of_materials
WHERE bill_sequence_id = c1rec.CBSI;
SELECT assembly_item_id, organization_id
INTO c1rec.CAII, c1rec.COI
FROM bom_bill_of_mtls_interface
WHERE bill_sequence_id = c1rec.CBSI
AND transaction_type = G_Insert
AND process_flag not in (3,7)
AND rownum = 1;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
** Insert revision record
*/
stmt_num := 12;
INSERT into mtl_item_revisions_interface
(INVENTORY_ITEM_ID,
ORGANIZATION_ID,
REVISION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
EFFECTIVITY_DATE,
IMPLEMENTATION_DATE,
TRANSACTION_ID,
PROCESS_FLAG,
TRANSACTION_TYPE,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
VALUES
(c1rec.AII, c1rec.OI, UPPER(c1rec.R),
nvl(c1rec.LUD, sysdate),
nvl(c1rec.LUB, user_id),
nvl(c1rec.CD, sysdate),
nvl(c1rec.CB, user_id),
nvl(c1rec.LUL, user_id),
sysdate,
sysdate,
mtl_system_items_interface_s.nextval,
2,
G_Insert,
req_id,
prog_appid,
prog_id,
sysdate);
UPDATE bom_bill_of_mtls_interface
SET organization_id = nvl(organization_id, c1rec.OI),
assembly_item_id = nvl(assembly_item_id, c1rec.AII),
common_bill_sequence_id = c1rec.CBSI,
common_organization_id = c1rec.COI,
common_assembly_item_id = c1rec.CAII,
assembly_type = nvl(c1rec.AST, 1),
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),
process_flag = 2
WHERE transaction_id = c1rec.TI;
** FOR UPDATES AND DELETES - Assign Values
*/
continue_loop := TRUE;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
UPDATE bom_bill_of_mtls_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_bill_of_mtls_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_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** Assign Common Info ONLY for UPDATE's
*/
IF (c2rec.A = G_Update) THEN
stmt_num := 18.1;
UPDATE bom_bill_of_mtls_interface
SET organization_id = c2rec.OI,
assembly_item_id = c2rec.AII,
alternate_bom_designator = c2rec.ABD,
bill_sequence_id = c2rec.BSI,
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_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
SELECT bill_sequence_id
INTO c2rec.CBSI
FROM bom_bill_of_materials
WHERE organization_id = nvl(c2rec.COI, c2rec.OI)
AND assembly_item_id = c2rec.CAII
AND nvl(alternate_bom_designator, 'NONE') =
nvl(c2rec.ABD, 'NONE');
SELECT bill_sequence_id
INTO c2rec.CBSI
FROM bom_bill_of_mtls_interface
WHERE organization_id = nvl(c2rec.COI, c2rec.OI)
AND transaction_type = G_Insert
AND assembly_item_id = c2rec.CAII
AND nvl(alternate_bom_designator, 'NONE') =
nvl(c2rec.ABD, 'NONE')
AND process_flag not in (3,7)
AND rownum = 1;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
SELECT assembly_item_id, organization_id
INTO c2rec.CAII, c2rec.COI
FROM bom_bill_of_materials
WHERE bill_sequence_id = c2rec.CBSI;
SELECT assembly_item_id, organization_id
INTO c2rec.CAII, c2rec.COI
FROM bom_bill_of_mtls_interface
WHERE bill_sequence_id = c2rec.CBSI
AND transaction_type = G_Insert
AND process_flag not in (3,7)
AND rownum = 1;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
UPDATE bom_bill_of_mtls_interface
SET organization_id = c2rec.OI,
assembly_item_id = c2rec.AII,
alternate_bom_designator = c2rec.ABD,
bill_sequence_id = c2rec.BSI,
common_bill_sequence_id = c2rec.CBSI,
common_organization_id = c2rec.COI,
common_assembly_item_id = c2rec.CAII,
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 := 26;
UPDATE bom_bill_of_mtls_interface
SET organization_id = c2rec.OI,
assembly_item_id = c2rec.AII,
alternate_bom_designator = c2rec.ABD,
assembly_type = c2rec.AST,
bill_sequence_id = c2rec.BSI,
process_flag = 2
WHERE transaction_id = c2rec.TI;
SELECT bill_sequence_id
INTO cnt
FROM bom_bill_of_materials
WHERE bill_sequence_id = bom_seq_id;
SELECT count(*)
INTO cnt
FROM bom_bill_of_mtls_interface
WHERE bill_sequence_id = bom_seq_id
AND transaction_type = G_Insert
AND process_flag = 4;
SELECT 1
INTO cnt
FROM bom_bill_of_materials
WHERE organization_id = org_id
AND assembly_item_id = assy_id
AND nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE');
SELECT 1
INTO cnt
FROM bom_bill_of_mtls_interface
WHERE organization_id = org_id
AND assembly_item_id = assy_id
AND nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE')
AND transaction_type = G_Insert
AND rownum = 1
AND process_flag = 4;
SELECT 1
INTO cnt
FROM bom_bill_of_materials
WHERE organization_id = org_id
AND assembly_item_id = assy_id
AND alternate_bom_designator is null
AND ((assy_type = 2)
OR
(assy_type =1 and assembly_type = 1)
);
SELECT bill_sequence_id
INTO cnt
FROM bom_bill_of_mtls_interface
WHERE organization_id = org_id
AND assembly_item_id = assy_id
AND alternate_bom_designator is null
AND ((assy_type = 2)
OR
(assy_type =1 and assembly_type = 1)
)
AND process_flag = 4
AND transaction_type = G_Insert
AND rownum = 1;
SELECT 1
INTO cnt
FROM mtl_parameters mp1, mtl_parameters mp2
WHERE mp1.organization_id = org_id
AND mp2.organization_id = cmn_org_id
AND mp1.master_organization_id = mp2.master_organization_id;
SELECT bill_sequence_id
INTO cnt
FROM bom_bill_of_materials
WHERE bill_sequence_id = cmn_bom_id
AND assembly_item_id = cmn_item_id
AND organization_id = cmn_org_id
AND nvl(alternate_bom_designator, 'NONE') = nvl(alt_desg, 'NONE')
AND common_bill_sequence_id = bill_sequence_id
AND (assembly_item_id <> item_id
OR
organization_id <> org_id)
AND ((bom_type <> 1)
OR
(bom_type = 1 AND assembly_type = 1));
SELECT bill_sequence_id
INTO cnt
FROM bom_bill_of_mtls_interface
WHERE bill_sequence_id = cmn_bom_id
AND assembly_item_id = cmn_item_id
AND organization_id = cmn_org_id
AND nvl(alternate_bom_designator, 'NONE') = nvl(alt_desg, 'NONE')
AND common_bill_sequence_id = bill_sequence_id
AND (assembly_item_id <> item_id
OR
organization_id <> org_id)
AND process_flag = 4
AND transaction_type in (G_Insert, G_Update)
AND ((bom_type <> 1)
OR
(bom_type = 1 AND assembly_type = 1));
SELECT bom_item_type, base_item_id, replenish_to_order_flag,
pick_components_flag
INTO bit, base_id, ato, pto
FROM mtl_system_items
WHERE inventory_item_id = item_id
AND organization_id = org_id;
SELECT count(*)
INTO cnt
FROM bom_inventory_components bic
WHERE bic.bill_sequence_id = cmn_bom_id
AND not exists
(SELECT 'x'
FROM mtl_system_items s
WHERE s.organization_id = org_id
AND s.inventory_item_id = bic.component_item_id
AND ((bom_type = 1 AND s.eng_item_flag = 'N')
OR (bom_type = 2))
AND s.bom_enabled_flag = 'Y'
AND s.inventory_item_id <> item_id
AND ((bit = 1 AND s.bom_item_type <> 3)
OR (bit = 2 AND s.bom_item_type <> 3)
OR (bit = 3)
OR (bit = 4
AND (s.bom_item_type = 4
OR (s.bom_item_type IN (2, 1)
AND s.replenish_to_order_flag = 'Y'
AND base_id IS NOT NULL
AND ato = 'Y'))))
AND (bit = 3
OR
pto = 'Y'
OR
s.pick_components_flag = 'N')
AND (bit = 3
OR
NVL(s.bom_item_type, 4) <> 2
OR
(s.bom_item_type = 2
AND ((pto = 'Y'
AND s.pick_components_flag = 'Y')
OR (ato = 'Y'
AND s.replenish_to_order_flag = 'Y'))))
AND not(bit = 4
AND pto = 'Y'
AND s.bom_item_type = 4
AND s.replenish_to_order_flag = 'Y')
);
SELECT count(*)
INTO cnt
FROM bom_inventory_components bic,
bom_substitute_components bsc
WHERE bic.bill_sequence_id = cmn_bom_id
AND bic.component_sequence_id = bsc.component_sequence_id
AND bsc.substitute_component_id not in
(select msi1.inventory_item_id
from mtl_system_items msi1, mtl_system_items msi2
where msi1.organization_id = org_id
and msi1.inventory_item_id = bsc.substitute_component_id
and msi2.organization_id = cmn_org_id
and msi2.inventory_item_id = msi1.inventory_item_id);
SELECT 1
INTO cnt
FROM mtl_system_items msi1, mtl_system_items msi2
WHERE msi1.organization_id = org_id
AND msi1.inventory_item_id = item_id
AND msi2.organization_id = cmn_org_id
AND msi2.inventory_item_id = cmn_item_id
AND msi2.bom_enabled_flag = 'Y'
AND msi1.bom_item_type = msi2.bom_item_type
AND msi1.pick_components_flag = msi2.pick_components_flag
AND msi1.replenish_to_order_flag = msi2.replenish_to_order_flag;
** Select all INSERTS
*/
CURSOR C1 IS
SELECT organization_id OI, bill_sequence_id BSI,
assembly_item_id AII, common_bill_sequence_id CBSI,
common_assembly_item_id CAII, assembly_type AST,
common_organization_id COI, transaction_type A,
alternate_bom_designator ABD, transaction_id TI,
pending_from_ecn PFE
FROM bom_bill_of_mtls_interface
WHERE process_flag = 2
AND transaction_type = G_Insert
AND rownum < G_rows_to_commit;
** Select all UPDATES and DELETES
*/
CURSOR c2 is
SELECT organization_id OI, bill_sequence_id BSI,
assembly_item_id AII, common_bill_sequence_id CBSI,
common_assembly_item_id CAII, assembly_type AST,
common_organization_id COI, transaction_type A,
alternate_bom_designator ABD, transaction_id TI,
next_explode_date NED, creation_date CD,
specific_assembly_comment SAC, created_by CB,
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, pending_from_ecn PFE,
request_id RI, program_application_id PAI, program_id PI,
program_update_date PUD
FROM bom_bill_of_mtls_interface
WHERE process_flag = 2
AND transaction_type in (G_Update, G_Delete)
AND rownum < G_rows_to_commit;
** Select UPDATES for Common Bill Verification
*/
CURSOR c3 is
SELECT organization_id OI, bill_sequence_id BSI,
assembly_item_id AII, common_bill_sequence_id CBSI,
common_assembly_item_id CAII, assembly_type AST,
common_organization_id COI, transaction_type A,
alternate_bom_designator ABD, transaction_id TI
FROM bom_bill_of_mtls_interface
WHERE process_flag = 99
AND transaction_type = G_Update
AND rownum < G_rows_to_commit;
X_program_update_date DATE;
** FOR INSERTS - Validate
*/
WHILE continue_loop LOOP
commit_cnt := 0;
SELECT organization_id
INTO dummy_id
FROM mtl_parameters
WHERE organization_id = c1rec.OI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT bom_item_type
FROM mtl_system_items
WHERE organization_id = c1rec.OI
AND inventory_item_id = c1rec.AII;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT 1
INTO dummy_id
FROM bom_alternate_designators
WHERE organization_id = c1rec.OI
AND alternate_designator_code = c1rec.ABD;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT 1
INTO dummy_id
FROM mtl_system_items
WHERE organization_id = c1rec.OI
AND inventory_item_id = c1rec.AII;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT 1
INTO dummy_id
FROM eng_engineering_changes
WHERE organization_id = c1rec.OI
AND change_notice = c1rec.PFE;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
SELECT 1
INTO dummy_id
FROM mtl_system_items
WHERE organization_id = c1rec.OI
AND inventory_item_id = c1rec.AII
AND bom_enabled_flag = 'Y'
AND ((c1rec.AST = 2)
OR
(c1rec.AST = 1 AND
eng_item_flag = 'N'));
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 4
WHERE transaction_id = c1rec.TI;
** Update "Update" Records and validate "Delete" records
*/
continue_loop := TRUE;
SELECT bom.creation_date, bom.created_by,
bom.common_assembly_item_id,
bom.specific_assembly_comment, bom.pending_from_ecn,
bom.attribute_category, bom.attribute1,
bom.attribute2, bom.attribute3, bom.attribute4,
bom.attribute5,
bom.attribute6, bom.attribute7, bom.attribute8,
bom.attribute9,
bom.attribute10, bom.attribute11, bom.attribute12,
bom.attribute13,
bom.attribute14, bom.attribute15, bom.request_id,
bom.program_application_id, bom.program_id,
bom.program_update_date,
bom.assembly_type, bom.common_bill_sequence_id,
bom.common_organization_id, bom.next_explode_date,
msi.bom_item_type
INTO X_creation_date, X_created_by, X_common_assembly_item_id,
X_specific_assembly_comment, X_pending_from_ecn,
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_assembly_type, X_common_bill_sequence_id,
X_common_organization_id, X_next_explode_date,
x_bom_item_type
FROM bom_bill_of_materials bom,
mtl_system_items msi
WHERE bill_sequence_id = c2rec.BSI
AND msi.organization_id = bom.organization_id
AND msi.inventory_item_id = bom.assembly_item_id;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** ONLY for "Updates"
*/
IF (c2rec.A = G_Update) THEN
IF (x_bom_item_type <> G_ProductFamily) THEN
/*
** Check if column is non-updatable
*/
stmt_num := 13;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** Update interface record with production record's values
*/
stmt_num := 14;
UPDATE bom_bill_of_mtls_interface
SET creation_date = X_creation_date,
created_by = X_created_by,
assembly_type = X_assembly_type,
next_explode_date = X_next_explode_date,
common_assembly_item_id = decode(c2rec.CBSI, null,
X_common_assembly_item_id, G_NullNum, '',
c2rec.CAII),
common_bill_sequence_id = decode(c2rec.CBSI, null,
X_common_bill_sequence_id, G_NullNum, c2rec.BSI,
c2rec.CBSI),
common_organization_id = decode(c2rec.CBSI, null,
X_common_organization_id, G_NullNum, '', c2rec.COI),
specific_assembly_comment = decode(c2rec.SAC, G_NullChar,
null, null, X_specific_assembly_comment, c2rec.SAC),
pending_from_ecn = X_pending_from_ecn,
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),
process_flag = 99
WHERE transaction_id = c2rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c2rec.TI;
** Update interface record with production record's values
*/
stmt_num := 14;
UPDATE bom_bill_of_mtls_interface
SET creation_date = X_creation_date,
created_by = X_created_by,
assembly_type = X_assembly_type,
next_explode_date = X_next_explode_date,
common_assembly_item_id = X_common_assembly_item_id,
common_bill_sequence_id = X_common_bill_sequence_id,
common_organization_id = X_common_organization_id,
specific_assembly_comment = X_specific_assembly_comment,
pending_from_ecn = X_pending_from_ecn,
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),
process_flag = 4 -- Don't pick up records in cursor c3
WHERE transaction_id = c2rec.TI;
ELSIF (c2rec.A = G_Delete) THEN
/*
** Set Process Flag to 4 for "Deletes"
*/
stmt_num := 15;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 4
WHERE transaction_id = c2rec.TI;
** Validate "Update" Records
*/
continue_loop := TRUE;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 4
WHERE transaction_id = c3rec.TI;
Insert, update and delete bill data from the interface
table, BOM_BILL_OF_MTLS_INTERFACE, into the production table,
BOM_BILL_OF_MATERIALS.
REQUIRES
prog_appid Program application id
prog_id Program id
req_id Request id
user_id User id
login_id Login id
MODIFIES
BOM_BILL_OF_MATERIALS
BOM_BILL_OF_MTLS_INTERFACE
RETURNS
0 if successful
SQLCODE if error
NOTES
-----------------------------------------------------------------------------*/
FUNCTION Transact_Bill
( user_id NUMBER,
login_id NUMBER,
prog_appid NUMBER,
prog_id NUMBER,
req_id NUMBER,
err_text OUT VARCHAR2)
return integer
IS
ret_code NUMBER;
X_delete_group_seq_id NUMBER;
X_delete_type NUMBER;
** Select "Update" bill records
*/
CURSOR c1 IS
SELECT bill_sequence_id BSI, common_assembly_item_id CAII,
specific_assembly_comment SAC, common_bill_sequence_id CBSI,
common_organization_id COI,
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_bill_of_mtls_interface
WHERE process_flag = 4
AND transaction_type = G_Update
AND rownum < G_rows_to_commit;
** Select "Delete" bill records
*/
CURSOR c2 IS
SELECT bill_sequence_id BSI, assembly_type AST, organization_id OI,
assembly_item_id AII, alternate_bom_designator ABD,
transaction_id TI
FROM bom_bill_of_mtls_interface
WHERE process_flag = 4
AND transaction_type = G_Delete
AND rownum < G_rows_to_commit;
** Insert bills
*/
stmt_num := 1;
INSERT INTO bom_bill_of_materials(
assembly_item_id,
organization_id,
alternate_bom_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
common_assembly_item_id,
specific_assembly_comment,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
assembly_type,
common_bill_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
common_organization_id,
next_explode_date
)
SELECT
assembly_item_id,
organization_id,
alternate_bom_designator,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
common_assembly_item_id,
specific_assembly_comment,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
assembly_type,
common_bill_sequence_id,
bill_sequence_id,
request_id,
program_application_id,
program_id,
program_update_date,
common_organization_id,
next_explode_date
FROM bom_bill_of_mtls_interface
WHERE process_flag = 4
AND transaction_type = G_Insert
AND rownum < 500;
UPDATE bom_bill_of_mtls_interface bi
SET process_flag = 7
WHERE process_flag = 4
AND transaction_type = G_Insert
AND exists (SELECT null
FROM bom_bill_of_materials bom
WHERE bom.bill_sequence_id = bi.bill_sequence_id);
** Update Bills
*/
stmt_num := 4;
UPDATE bom_bill_of_materials
SET last_update_date = c1rec.LUD,
last_updated_by = c1rec.LUB,
last_update_login = c1rec.LUL,
common_assembly_item_id = c1rec.CAII,
specific_assembly_comment = c1rec.SAC,
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,
common_bill_sequence_id = c1rec.CBSI,
common_organization_id = c1rec.COI
WHERE bill_sequence_id = c1rec.BSI;
UPDATE bom_bill_of_mtls_interface
SET process_flag = 7
WHERE transaction_id = c1rec.TI;
** Delete Bills
*/
stmt_num := 7;
** Get the Bill Delete Group name
*/
IF (X_bill_group_name is null) THEN
stmt_num := 8;
SELECT delete_group_name, description
FROM bom_interface_delete_groups
WHERE UPPER(entity_name) = G_DeleteEntity;
X_bill_group_name := X_billgroup.delete_group_name;
'BOM_BILL_DELETE_GROUP_MISSING');
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_bill_group_name
AND organization_id = c2rec.OI;
/* if delete group if of type routings. make it
* of type bill, routings
*/
if X_delete_type = 3 then
update bom_delete_groups
set delete_type = 6
WHERE delete_group_name = X_bill_group_name
AND organization_id = c2rec.OI;
X_delete_type := 6;
IF (X_delete_type not in (2,6)) THEN
X_error_message := FND_MESSAGE.Get_String('BOM',
'BOM_DELETE_GROUP_INVALID');
ret_code := Modal_Delete.Delete_Manager_Oi(
new_group_seq_id => X_delete_group_seq_id,
name => X_bill_group_name,
group_desc => X_bill_group_description,
org_id => c2rec.OI,
bom_or_eng => c2rec.AST,
del_type => 2,
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 => null,
ent_op_seq_id => null,
user_id => user_id,
err_text => err_text);
UPDATE bom_bill_of_mtls_interface
SET process_flag = 7
WHERE transaction_id = c2rec.TI;
DELETE from bom_bill_of_mtls_interface
WHERE process_flag = 7
AND rownum < G_rows_to_commit;