[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_code OC, organization_id OI,
revision R, inventory_item_id III, item_number IIN,
transaction_id TI, implementation_date ID, effectivity_date ED,
transaction_type A
FROM mtl_item_revisions_interface
WHERE process_flag = 1
and transaction_type in (G_Insert, G_Update)
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 mtl_item_revisions_interface
SET transaction_type = G_Insert
WHERE process_flag = 1
AND upper(transaction_type) = 'INSERT'
AND rownum < G_rows_to_commit;
** ALL INSERTS and UPDATES - Assign Org Id
*/
stmt_num := 1;
UPDATE mtl_item_revisions_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_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;
** FOR INSERTS and UPDATES - Assign transaction ids
*/
stmt_num := 2;
UPDATE mtl_item_revisions_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_Insert, G_Update)
and rownum < G_rows_to_commit;
** FOR INSERTS and UPDATES - Check if ORGANIZATION_ID is null
*/
WHILE continue_loop LOOP
commit_cnt := 0;
UPDATE mtl_item_revisions_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
UPDATE mtl_item_revisions_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
IF (c1rec.A = G_Insert) THEN
/* For Inserts */
stmt_num := 5;
UPDATE mtl_item_revisions_interface
SET organization_id = nvl(organization_id, c1rec.OI),
inventory_item_id = nvl(inventory_item_id, c1rec.III),
revision = UPPER(c1rec.R),
process_flag = 2,
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),
effectivity_date = nvl(effectivity_date, sysdate),
implementation_date = nvl(effectivity_date, sysdate)
WHERE transaction_id = c1rec.TI;
/* For Updates */
stmt_num := 6;
UPDATE mtl_item_revisions_interface
SET organization_id = nvl(organization_id, c1rec.OI),
inventory_item_id = nvl(inventory_item_id, c1rec.III),
revision = UPPER(c1rec.R),
process_flag = 2,
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),
implementation_date = nvl(effectivity_date, NULL)
WHERE transaction_id = c1rec.TI;
SELECT revision R, effectivity_date ED,
transaction_id TI, transaction_type TT
FROM mtl_item_revisions_interface
WHERE organization_id = org_id
and inventory_item_id = assy_id
and transaction_type in (G_Insert, G_Update)
and process_flag = 99;
** FOR INSERTS and UPDATES - Check for ascending order and identical revs
*/
SELECT count(*)
INTO err_cnt
FROM mtl_item_revisions_interface a
WHERE transaction_id <> c1rec.TI
and inventory_item_id = assy_id
and organization_id = org_id
and process_flag = 4
and ( (revision = c1rec.R)
OR
(effectivity_date > c1rec.ED
and revision < c1rec.R)
OR
(effectivity_date < c1rec.ED
and revision > c1rec.R));
** FOR INSERTS - Check production table
*/
stmt_num := 2;
IF (c1rec.TT = G_Insert) THEN
SELECT count(*)
INTO err_cnt
FROM mtl_item_revisions mir
WHERE inventory_item_id = assy_id
and organization_id = org_id
and NOT EXISTS (select 'x'
from mtl_item_revisions_interface miri
where miri.inventory_item_id = mir.inventory_item_id
and miri.organization_id = mir.organization_id
and miri.revision = mir.revision
and miri.process_flag = 4)
and ((revision = c1rec.R)
OR
(effectivity_date > c1rec.ED
AND revision < c1rec.R)
OR
(effectivity_date < c1rec.ED
AND revision > c1rec.R));
** FOR UPDATES - Check production table
*/
stmt_num := 3;
SELECT count(*)
INTO err_cnt
FROM mtl_item_revisions mir
WHERE inventory_item_id = assy_id
and organization_id = org_id
and revision <> c1rec.R
and NOT EXISTS (select 'x'
from mtl_item_revisions_interface miri
where miri.inventory_item_id = mir.inventory_item_id
and miri.organization_id = mir.organization_id
and miri.revision = mir.revision
and miri.process_flag = 4)
and ((effectivity_date > c1rec.ED
AND revision < c1rec.R)
OR
(effectivity_date < c1rec.ED
AND revision > c1rec.R));
UPDATE mtl_item_revisions_interface
SET process_flag = 4
WHERE transaction_id = c1rec.TI;
UPDATE mtl_item_revisions_interface
SET process_flag = 3
WHERE transaction_id = c1rec.TI;
X_program_update_date DATE;
** All "Insert" records
*/
CURSOR c0 IS
select inventory_item_id AII, organization_id OI,
revision R, transaction_id TI,
change_notice CN
from mtl_item_revisions_interface
where process_flag = 2
and transaction_type = G_Insert
and rownum < G_rows_to_commit;
** All "Insert" and "Update" records grouped by Item
*/
CURSOR c1 IS
select inventory_item_id AII, organization_id OI
from mtl_item_revisions_interface
where process_flag = 99
and transaction_type in (G_Insert, G_Update)
group by organization_id, inventory_item_id;
** All "Update" records
*/
CURSOR c3 IS
select inventory_item_id III, organization_id OI,
revision R, transaction_id TI,
creation_date CD, created_by CB, change_notice CN,
ecn_initiation_date EID, implementation_date ID,
effectivity_date ED, revised_item_sequence_id RISI,
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, description D
from mtl_item_revisions_interface
where process_flag = 2
and transaction_type = G_Update
and rownum < G_rows_to_commit;
** FOR UPDATES - Validate
*/
stmt_num := 1;
** Check if implemented "update" record exists in Production
*/
stmt_num := 2;
SELECT creation_date, created_by, change_notice,
ecn_initiation_date, implementation_date,
effectivity_date, 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,
revised_item_sequence_id, description
INTO X_creation_date, X_created_by, X_change_notice,
X_ecn_initiation_date, X_implementation_date,
X_effectivity_date, 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_revised_item_sequence_id,
X_description
FROM mtl_item_revisions
WHERE organization_id = c3rec.OI
and inventory_item_id = c3rec.III
and revision = c3rec.R
and implementation_date is NOT NULL;
UPDATE mtl_item_revisions_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
UPDATE mtl_item_revisions_interface
SET process_flag = 3
WHERE transaction_id = c3rec.TI;
** Update interface record with production record's values
*/
stmt_num := 3;
UPDATE mtl_item_revisions_interface
SET creation_date = X_creation_date,
created_by = X_created_by,
change_notice = X_change_notice,
ecn_initiation_date = X_ecn_initiation_date,
revised_item_sequence_id = X_revised_item_sequence_id,
process_flag = 99,
effectivity_date = nvl(c3rec.ED, X_effectivity_date),
implementation_date = nvl(c3rec.ID, X_implementation_date),
attribute_category = decode(c3rec.AC, G_NullChar, '', NULL,
X_attribute_category, c3rec.AC),
attribute1 = decode(c3rec.A1, G_NullChar, '', NULL,
X_attribute1, c3rec.A1),
attribute2 = decode(c3rec.A2, G_NullChar, '', NULL,
X_attribute2, c3rec.A2),
attribute3 = decode(c3rec.A3, G_NullChar, '', NULL,
X_attribute3, c3rec.A3),
attribute4 = decode(c3rec.A4, G_NullChar, '', NULL,
X_attribute4, c3rec.A4),
attribute5 = decode(c3rec.A5, G_NullChar, '', NULL,
X_attribute5, c3rec.A5),
attribute6 = decode(c3rec.A6, G_NullChar, '', NULL,
X_attribute6, c3rec.A6),
attribute7 = decode(c3rec.A7, G_NullChar, '', NULL,
X_attribute7, c3rec.A7),
attribute8 = decode(c3rec.A8, G_NullChar, '', NULL,
X_attribute8, c3rec.A8),
attribute9 = decode(c3rec.A9, G_NullChar, '', NULL,
X_attribute9, c3rec.A9),
attribute10 = decode(c3rec.A10, G_NullChar, '', NULL,
X_attribute10, c3rec.A10),
attribute11 = decode(c3rec.A11, G_NullChar, '', NULL,
X_attribute11, c3rec.A11),
attribute12 = decode(c3rec.A12, G_NullChar, '', NULL,
X_attribute12, c3rec.A12),
attribute13 = decode(c3rec.A13, G_NullChar, '', NULL,
X_attribute13, c3rec.A13),
attribute14 = decode(c3rec.A14, G_NullChar, '', NULL,
X_attribute14, c3rec.A14),
attribute15 = decode(c3rec.A15, G_NullChar, '', NULL,
X_attribute15, c3rec.A15),
request_id = decode(c3rec.RI, G_NullChar, '', NULL,
X_request_id, c3rec.RI),
program_application_id = decode(c3rec.PAI, G_NullNum, '',
NULL,
X_program_application_id, c3rec.PAI),
program_id = decode(c3rec.PI, G_NullNum, '', NULL,
X_program_id, c3rec.PI),
program_update_date = decode(c3rec.PUD, G_NullDate, '', NULL,
X_program_update_date, c3rec.PUD),
description = decode(c3rec.D, G_NullChar, '', NULL,
X_description, c3rec.D)
WHERE transaction_id = c3rec.TI;
** FOR INSERTS - Validate
*/
stmt_num := 5;
UPDATE mtl_item_revisions_interface
SET process_flag = 3
WHERE transaction_id = c0rec.TI;
SELECT organization_id
INTO dummy_id
FROM mtl_parameters
WHERE organization_id = c0rec.OI;
UPDATE mtl_item_revisions_interface
SET process_flag = 3
WHERE transaction_id = c0rec.TI;
select 1
into dummy
from mtl_system_items
where organization_id = c0rec.OI
and inventory_item_id = c0rec.AII;
UPDATE mtl_item_revisions_interface
SET process_flag = 3
WHERE transaction_id = c0rec.TI;
SELECT 1
INTO dummy
FROM eng_engineering_changes
WHERE organization_id = c0rec.OI
AND change_notice = c0rec.CN;
UPDATE mtl_item_revisions_interface
SET process_flag = 3
WHERE transaction_id = c0rec.TI;
UPDATE mtl_item_revisions_interface
SET process_flag = 99
WHERE transaction_id = c0rec.TI;
Insert and update item revision data from the interface
table, MTL_ITEM_REVISIONS_INTERFACE, into the production table,
MTL_ITEM_REVISIONS.
REQUIRES
prog_appid Program application id
prog_id Program id
req_id Request id
user_id User id
login_id Login id
MODIFIES
MTL_ITEM_REVISIONS_INTERFACE
MTL_ITEM_REVISIONS
RETURNS
0 if successful
SQLCODE if error
NOTES
-----------------------------------------------------------------------------*/
FUNCTION Transact_Item_Revision
( 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" item revision records
*/
CURSOR c1 IS
SELECT inventory_item_id III, organization_id OI,
revision R, last_update_date LUD, last_updated_by LUB,
last_update_login LUL, implementation_date ID,
effectivity_date ED,
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, description D, transaction_id TI
FROM mtl_item_revisions_interface
WHERE process_flag = 4
AND transaction_type = G_Update
AND rownum < G_rows_to_commit;
** Insert Item Revisions
*/
stmt_num := 1;
INSERT INTO mtl_item_revisions
(
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
REVISION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CHANGE_NOTICE,
IMPLEMENTATION_DATE,
EFFECTIVITY_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
DESCRIPTION)
SELECT
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
REVISION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CHANGE_NOTICE,
IMPLEMENTATION_DATE,
EFFECTIVITY_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID,
DESCRIPTION
FROM mtl_item_revisions_interface
WHERE process_flag = 4
and transaction_type = G_Insert
and rownum < 500;
UPDATE mtl_item_revisions_interface mri
SET process_flag = 7
WHERE process_flag = 4
and transaction_type = G_Insert
and EXISTS (SELECT NULL
FROM mtl_item_revisions mir
WHERE mir.inventory_item_id = mri.inventory_item_id
AND mir.organization_id = mri.organization_id
AND mir.revision = mri.revision);
** Update Item Revisions
*/
stmt_num := 4;
UPDATE mtl_item_revisions
SET last_update_date = c1rec.LUD,
last_updated_by = c1rec.LUB,
last_update_login = c1rec.LUL,
implementation_date = c1rec.ID,
effectivity_date = c1rec.ED,
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,
description = c1rec.D
WHERE inventory_item_id = c1rec.III
AND organization_id = c1rec.OI
AND revision = c1rec.R;
UPDATE mtl_item_revisions_interface mri
SET process_flag = 7
WHERE transaction_id = c1rec.TI;
DELETE from mtl_item_revisions_interface
WHERE process_flag = 7
AND rownum < G_rows_to_commit;