[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id,rowid
FROM mtl_item_revisions_interface
WHERE inventory_item_id IS NULL
AND set_process_id = xset_id
AND process_flag = 1
AND (organization_id = org_id OR
all_org = l_all_org );
CURSOR cc is select distinct item_number,
organization_id
from mtl_item_revisions_interface
where inventory_item_id is NULL
and item_number is not NULL
and organization_id is not NULL
and set_process_id = xset_id
and process_flag = 1;
CURSOR ff is select distinct inventory_item_id,
organization_id
from mtl_item_revisions_interface
where process_flag = 1
and set_process_id = xset_id
and transaction_id IS NULL --Bug: 3019435 Added condition
and (organization_id = org_id or
all_org = 1);
SELECT rowid
FROM mtl_item_revisions_interface
WHERE process_flag = 1
AND set_process_id = xset_id
AND transaction_type = 'CREATE'
AND revision_id IS NULL
AND (organization_id = org_id OR all_org = 1)
ORDER BY revision;
CURSOR ct is select distinct transaction_id
from mtl_item_revisions_interface
where inventory_item_id is NULL
and transaction_id is not NULL
and organization_id is not NULL
and set_process_id = xset_id
and process_flag = 1;
update MTL_ITEM_REVISIONS_INTERFACE i
set i.organization_id = (select o.organization_id
from MTL_PARAMETERS o
where o.organization_code = i.organization_code)
where i.organization_id is NULL
and set_process_id = xset_id
and i.process_flag = l_process_flag_1;
SELECT inventory_item_id INTO flex_id
FROM mtl_system_items_interface
WHERE transaction_id = ctr.transaction_id
AND ROWNUM = 1;
UPDATE mtl_item_revisions_interface
SET inventory_item_id = flex_id
WHERE transaction_id = ctr.transaction_id
AND set_process_id = xset_id;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3,
transaction_id = NVL(transaction_id,MTL_SYSTEM_ITEMS_INTERFACE_S.nextval)
where item_number = cr.item_number
and inventory_item_id is NULL
and process_flag = l_process_flag_1
and set_process_id = xset_id
and organization_id = cr.organization_id
RETURNING transaction_id INTO tran_id;
select MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
into tran_id
from dual;
update mtl_item_revisions_interface
set process_flag = l_process_flag_3,
transaction_id = tran_id
where item_number = cr.item_number
and inventory_item_id is NULL
and process_flag = l_process_flag_1
and set_process_id = xset_id
and organization_id = cr.organization_id;
update mtl_item_revisions_interface
set inventory_item_id = flex_id
where item_number = cr.item_number
and set_process_id = xset_id
and organization_id = cr.organization_id;
select MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
into tran_id
from dual;
update mtl_item_revisions_interface
set transaction_id = tran_id
where inventory_item_id = cr.inventory_item_id
and organization_id = cr.organization_id
-- and set_process_id + 0 = xset_id -- fix for bug#8757041,removed + 0
and set_process_id = xset_id
and process_flag = l_process_flag_1;
select MTL_ITEM_REVISIONS_B_S.nextval
into rev_id
from dual;
update mtl_item_revisions_interface
set revision_id = rev_id
where rowid = cr.rowid;
** update process flag , at last
** For bug 3226359 added code to update date fields with sysdate + 1/86400 (1 sec) if they are NULL
*/
update mtl_item_revisions_interface
set process_flag = DECODE(default_flag, 1, l_process_flag_2 , l_process_flag_1),
LAST_UPDATE_DATE = nvl(LAST_UPDATE_DATE,(sysdate + 1/86400)),
/* LAST_UPDATED_BY = -1,
** NP 13OCT94 If you encounter ORA-6502 then see TAR 106456.555
** The decode stmts are the culprits!
*/
LAST_UPDATED_BY = decode(LAST_UPDATED_BY, NULL, user_id,LAST_UPDATED_BY),
CREATION_DATE = nvl(CREATION_DATE,(sysdate + 1/86400)),
/*CREATED_BY = -1,*/
CREATED_BY = decode(LAST_UPDATED_BY, NULL, user_id,LAST_UPDATED_BY),
CHANGE_NOTICE = NULL,
ECN_INITIATION_DATE = NULL,
IMPLEMENTATION_DATE = nvl(effectivity_date, (l_sysdate + 1/86400)),
implemented_serial_number = NULL,
revised_item_sequence_id = NULL ,
effectivity_date = nvl(effectivity_date, (l_sysdate + 1/86400)),
revision = trim(revision) --Bugfix 6457167
where inventory_item_id is not null
and process_flag = l_process_flag_1
and set_process_id = xset_id
and (organization_id = org_id or all_org = l_all_org);
/* update mtl_item_revisions_interface i
set i.process_flag = l_process_flag_3,
i.LAST_UPDATE_DATE = sysdate,
i.LAST_UPDATED_BY = decode(i.LAST_UPDATED_BY, NULL, user_id,i.LAST_UPDATED_BY),
i.CREATION_DATE = l_sysdate,
i.CREATED_BY = decode(i.LAST_UPDATED_BY, NULL, user_id,i.LAST_UPDATED_BY)
where ( i.inventory_item_id is NULL or
i.organization_id is NULL)
and set_process_id = xset_id
and i.process_flag = l_process_flag_1
and ( i.organization_id = org_id or
all_org = l_all_org );*/
UPDATE mtl_item_revisions_interface i
SET i.process_flag = l_process_flag_3,
i.LAST_UPDATE_DATE = sysdate,
i.LAST_UPDATED_BY = decode(i.LAST_UPDATED_BY, NULL, user_id,i.LAST_UPDATED_BY),
i.CREATION_DATE = l_sysdate,
i.CREATED_BY = decode(i.LAST_UPDATED_BY, NULL, user_id,i.LAST_UPDATED_BY),
i.transaction_id = NVL(transaction_id,MTL_SYSTEM_ITEMS_INTERFACE_S.nextval)
WHERE i.rowid = rec.rowid
RETURNING i.transaction_id INTO tran_id ;
update mtl_item_revisions_interface i
set i.process_flag = l_process_flag_3
where i.transaction_id in (select m.transaction_id
from mtl_item_revisions_interface m
where m.process_flag = l_process_flag_3
and (m.organization_id = org_id or
all_org = l_all_org )
and set_process_id = xset_id )
and i.process_flag = l_process_flag_2
and set_process_id = xset_id
and (i.organization_id = org_id or
all_org = l_all_org);