[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_SYNC_RECORDS(p_set_id IN NUMBER);
PROCEDURE UPDATE_ITEM_CATALOG_ID(
p_set_id IN NUMBER
,p_prog_appid IN NUMBER
,p_prog_id IN NUMBER
,p_request_id IN NUMBER
,p_user_id IN NUMBER
,p_login_id IN NUMBER
,x_err_text IN OUT NOCOPY VARCHAR2);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET SET_PROCESS_ID = -999
WHERE PROCESS_FLAG = 1
AND TRANSACTION_TYPE in ('CREATE','Create','create');
UPDATE MTL_ITEM_REVISIONS_INTERFACE
SET SET_PROCESS_ID = -999
WHERE PROCESS_FLAG = 1
AND TRANSACTION_TYPE in ('CREATE','Create','create');
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET SET_PROCESS_ID = -999
WHERE PROCESS_FLAG = 1
AND TRANSACTION_TYPE in ('UPDATE','Update','update');
UPDATE MTL_ITEM_REVISIONS_INTERFACE
SET SET_PROCESS_ID = -999
WHERE PROCESS_FLAG = 1
AND TRANSACTION_TYPE in ('UPDATE','Update','update');
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET SET_PROCESS_ID = -999
WHERE PROCESS_FLAG = 1
AND TRANSACTION_TYPE in ('SYNC','Sync','sync');
UPDATE MTL_ITEM_REVISIONS_INTERFACE
SET SET_PROCESS_ID = -999
WHERE PROCESS_FLAG = 1
AND TRANSACTION_TYPE in ('SYNC','Sync','sync');
SELECT count(*) INTO l_pro_flag_3
FROM mtl_system_items_interface
WHERE process_flag = 3
AND request_id = FND_GLOBAL.conc_request_id
AND rownum = 1;
ret_code_update NUMBER := 0;
select rowid
from mtl_system_items_interface
where set_process_id = xset_id
for update;
select rowid
from mtl_item_revisions_interface
where set_process_id = xset_id
for update;
CURSOR update_org_id IS
select rowid, transaction_id
from mtl_system_items_interface
where organization_id is NULL
and set_process_id = xset_id
and process_flag = 1;
CURSOR update_org_id_revs IS
select rowid, transaction_id
from mtl_item_revisions_interface
where organization_id is NULL
and set_process_id = xset_id
and process_flag = 1;
SELECT COUNT(*)
FROM mtl_system_items_interface msii
,mtl_parameters mp1
WHERE set_process_id = xset_id
AND transaction_type = cp_transaction_type
AND process_flag in (1,2,4)
AND mp1.master_organization_id = msii.organization_id
AND ROWNUM = 1;
SELECT count(*)
FROM mtl_item_revisions_interface msii
,mtl_parameters mp1
WHERE set_process_id = xset_id
AND transaction_type = cp_transaction_type
AND process_flag in (1,2,4)
AND mp1.master_organization_id = msii.organization_id
AND ROWNUM = 1;
SELECT count(*)
FROM mtl_system_items_interface
WHERE set_process_id = xset_id
AND transaction_type = cp_transaction_type
AND process_flag in (1,2,4)
AND organization_id
NOT IN (SELECT master_organization_id
FROM mtl_parameters)
AND ROWNUM = 1;
SELECT count(*)
FROM mtl_item_revisions_interface
WHERE set_process_id = xset_id
AND transaction_type = cp_transaction_type
AND process_flag in (1,2,4)
AND organization_id
NOT IN (SELECT master_organization_id
FROM mtl_parameters)
AND ROWNUM = 1;
SELECT count(*)
FROM mtl_system_items_interface
WHERE set_process_id = xset_id
AND transaction_type = cp_transaction_type
AND process_flag in (1,4);
SELECT count(*)
FROM mtl_item_revisions_interface
WHERE set_process_id = xset_id
AND transaction_type = cp_transaction_type
AND process_flag in (1,4);
select rowid, transaction_id
from mtl_system_items_interface
where (transaction_type NOT IN ('CREATE', 'UPDATE','SYNC','ADD','DELETE') --bug13585063,13595665
OR transaction_type IS NULL OR set_process_id >= 900000000000)
AND set_process_id = xset_id
and process_flag = 1;
select rowid, transaction_id
from mtl_item_revisions_interface
where (transaction_type NOT IN ('CREATE', 'UPDATE','SYNC','ADD','DELETE') --bug13585063,13595665
OR transaction_type IS NULL OR set_process_id >= 900000000000)
AND set_process_id = xset_id
and process_flag = 1;
UPDATE mtl_system_items_interface
SET transaction_type = UPPER(transaction_type)
,request_id = req_id
,transaction_id = NVL(transaction_id, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL)
WHERE set_process_id = xset_id
AND process_flag IN (1,4);
UPDATE mtl_item_revisions_interface
SET transaction_type = UPPER(transaction_type)
,request_id = req_id
WHERE set_process_id = xset_id
AND process_flag IN (1,4);
UPDATE + first_rows index(msii, MTL_SYSTEM_ITEMS_INTERFACE_N3) (need add hint if added) -- Bug 10404086
mtl_system_items_interface msii
SET process_flag = -888
WHERE ( transaction_type NOT IN ('CREATE', 'UPDATE','SYNC')
OR transaction_type IS NULL OR set_process_id >= 900000000000)
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = -888
WHERE ( transaction_type NOT IN ('CREATE', 'UPDATE','SYNC')
OR transaction_type IS NULL OR set_process_id >= 900000000000)
AND set_process_id = xset_id;
update mtl_system_items_interface
set process_flag = 3
where rowid = cr.rowid ;
update mtl_item_revisions_interface
set process_flag = 3
where rowid = cr.rowid ;
update MTL_SYSTEM_ITEMS_INTERFACE MSII
set MSII.organization_id =
( select MP.organization_id
from MTL_PARAMETERS MP
where MP.organization_code = MSII.organization_code
)
where MSII.organization_id is NULL
and MSII.set_process_id = xset_id
and MSII.process_flag = 1;
update MTL_ITEM_REVISIONS_INTERFACE MIRI
set miri.template_id =
( select template_id
FROM mtl_item_templates_vl
WHERE template_name = miri.template_name
)
where miri.template_id IS NULL
and miri.template_name IS NOT NULL
and miri.set_process_id = xset_id
and miri.process_flag = 1;
update MTL_ITEM_REVISIONS_INTERFACE MIRI
set MIRI.organization_id =
( select MP.organization_id
from MTL_PARAMETERS MP
where MP.organization_code = MIRI.organization_code
)
where MIRI.organization_id is NULL
and MIRI.set_process_id = xset_id
and MIRI.process_flag = 1;
update MTL_ITEM_REVISIONS_INTERFACE MIRI
set MIRI.REVISION = UPPER(MIRI.REVISION)
WHERE MIRI.set_process_id = xset_id
AND MIRI.process_flag=1;
FOR cr IN update_org_id LOOP
dumm_status := INVPUOPI.mtl_log_interface_err(
-1,
user_id,
login_id,
prog_appid,
prog_id,
request_id,
cr.transaction_id,
'INVPOPIF: Invalid Organization ID',
'ORGANIZATION_ID',
'MTL_SYSTEM_ITEMS_INTERFACE',
'INV_IOI_ORG_NO_EXIST',
err_text);
update mtl_system_items_interface
set process_flag = 3
where rowid = cr.rowid ;
FOR cr IN update_org_id_revs LOOP
dumm_status := INVPUOPI.mtl_log_interface_err (
-1,
user_id,
login_id,
prog_appid,
prog_id,
request_id,
cr.transaction_id,
'INVPOPIF: Invalid Organization ID',
'ORGANIZATION_ID',
'MTL_ITEM_REVISIONS_INTERFACE',
'INV_IOI_ORG_NO_EXIST',
err_text);
UPDATE mtl_item_revisions_interface
SET process_flag = 3
WHERE rowid = cr.rowid;
SELECT style_item_id, style_item_flag , Upper(transaction_type)
INTO l_style_item_id, l_style_item_flag, l_transaction_type
FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE set_process_id = xset_id
AND process_flag=1;
UPDATE ego_itm_usr_attr_intrfc uai
SET (transaction_type, transaction_id,organization_code , organization_id)
= (SELECT Upper(transaction_type), transaction_id, organization_code, organization_id
FROM mtl_system_items_interface msii
WHERE msii.set_process_id = xset_id)
WHERE DATA_SET_ID = xset_id
AND PROCESS_STATUS = 1;
UPDATE mtl_system_items_interface
SET inventory_item_id = MTL_SYSTEM_ITEMS_S.NEXTVAL
WHERE inventory_item_id IS NULL
AND set_process_id = xset_id
AND process_flag = 1;
UPDATE MTL_ITEM_REVISIONS_INTERFACE
SET revision_id = MTL_ITEM_REVISIONS_B_S.NEXTVAL
WHERE revision_id IS NULL
AND set_process_id = xset_id
AND process_flag = 1;
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE set_process_id = xset_id;
** Update Item Status to pending for ITEM CREATE rows in a
** ICC with NIR enabled. This will prevent Active status
** to be defaulted and subsequently applied.
R12C : Changing the New Item Req Reqd = 'Y' sub-query for hierarchy enabled Catalogs */
--6521101 - Pending status updation for master recs only
UPDATE mtl_system_items_interface msii
SET msii.INVENTORY_ITEM_STATUS_CODE = 'Pending'
WHERE (msii.organization_id = org_id OR all_Org = 1)
AND msii.INVENTORY_ITEM_STATUS_CODE IS NULL
AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND msii.process_flag = 1
AND msii.set_process_id = xset_id
AND msii.TRANSACTION_TYPE = 'CREATE'
AND EXISTS (SELECT NULL
FROM MTL_PARAMETERS PARAM
WHERE PARAM.ORGANIZATION_ID = MSII.ORGANIZATION_ID
AND PARAM.MASTER_ORGANIZATION_ID = PARAM.ORGANIZATION_ID)
AND 'Y' =
( SELECT ICC.NEW_ITEM_REQUEST_REQD
FROM MTL_ITEM_CATALOG_GROUPS_B ICC
WHERE ICC.NEW_ITEM_REQUEST_REQD IS NOT NULL
AND ICC.NEW_ITEM_REQUEST_REQD <> 'I'
AND ROWNUM = 1
CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
START WITH ICC.ITEM_CATALOG_GROUP_ID = msii.ITEM_CATALOG_GROUP_ID ); --R12C
the SYNC rows in pervious round will become create/update rows which shouldn't be disabled
IF run_mode = 3 THEN
--3018673: Start of bug fix.
UPDATE mtl_system_items_interface msii
SET process_flag = process_flag + 20000
WHERE transaction_type IN ('CREATE','UPDATE')
AND process_flag < 20000
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = process_flag + 20000
WHERE transaction_type IN ('CREATE','UPDATE')
AND process_flag < 20000
AND set_process_id = xset_id;
UPDATE_SYNC_RECORDS(p_set_id => xset_id);
UPDATE /*+ first_rows index(msii, MTL_SYSTEM_ITEMS_INTERFACE_N3) */ -- Bug 10404086
mtl_system_items_interface msii
SET process_flag = process_flag + 30000
WHERE transaction_type IN ('UPDATE','SYNC') --3018673
AND process_flag < 30000
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = process_flag + 30000
WHERE transaction_type IN ('UPDATE','SYNC') --3018673
AND process_flag < 30000
AND set_process_id = xset_id;
UPDATE /*+ first_rows index(msii, MTL_SYSTEM_ITEMS_INTERFACE_N3) */ -- Bug 10404086
mtl_system_items_interface msii
SET process_flag = process_flag + 60000
WHERE transaction_type = 'CREATE'
AND process_flag < 60000
AND set_process_id = xset_id
AND not exists (select mp1.organization_id
from mtl_parameters mp1
where msii.organization_id = mp1.master_organization_id);
UPDATE mtl_item_revisions_interface miri
SET process_flag = process_flag + 60000
WHERE transaction_type = 'CREATE'
AND process_flag < 60000
AND set_process_id = xset_id
AND not exists (select mp1.organization_id
from mtl_parameters mp1
where miri.organization_id = mp1.master_organization_id);
UPDATE mtl_system_items_interface msii
SET process_flag = process_flag - 60000
WHERE transaction_type = 'CREATE'
AND process_flag >= 60000
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = process_flag - 60000
WHERE transaction_type = 'CREATE'
AND process_flag >= 60000
AND set_process_id = xset_id;
UPDATE /*+ first_rows index(msii, MTL_SYSTEM_ITEMS_INTERFACE_N3) */ -- Bug 10404086
mtl_system_items_interface msii
SET process_flag = process_flag + 60000
WHERE transaction_type = 'CREATE'
AND process_flag < 60000
AND set_process_id = xset_id
AND EXISTS (select mp1.organization_id /*BUG 6158936*/
from mtl_parameters mp1
where msii.organization_id = mp1.master_organization_id);
UPDATE mtl_item_revisions_interface miri
SET process_flag = process_flag + 60000
WHERE transaction_type = 'CREATE'
AND process_flag < 60000
AND set_process_id = xset_id
AND EXISTS (select mp1.organization_id /*BUG 6158936*/
from mtl_parameters mp1
where miri.organization_id = mp1.master_organization_id);
UPDATE mtl_system_items_interface msii
SET process_flag = process_flag - 60000
WHERE transaction_type = 'CREATE'
AND process_flag >= 60000
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = process_flag - 60000
WHERE transaction_type = 'CREATE'
AND process_flag >= 60000
AND set_process_id = xset_id;
UPDATE mtl_system_items_interface msii
SET process_flag = process_flag - 30000
WHERE transaction_type IN ('UPDATE','SYNC') --3018673
AND process_flag >= 30000
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = process_flag - 30000
WHERE transaction_type IN ('UPDATE','SYNC') --3018673
AND process_flag >= 30000
AND set_process_id = xset_id;
IF (run_mode IN (2,3,0)) THEN /* transaction_type IN 'UPDATE' 'SYNC' */
l_processed_flag := TRUE;
UPDATE /*+ first_rows index(msii, MTL_SYSTEM_ITEMS_INTERFACE_N3) */ -- Bug 10404086
mtl_system_items_interface msii
SET process_flag = process_flag + 30000
WHERE transaction_type IN ('CREATE','SYNC') --3018673
AND process_flag < 30000
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = process_flag + 30000
WHERE transaction_type IN ('CREATE','SYNC') --3018673
AND process_flag < 30000
AND set_process_id = xset_id;
OPEN c_master_items(cp_transaction_type=>'UPDATE');
OPEN c_master_revs(cp_transaction_type=>'UPDATE');
UPDATE /*+ first_rows index(msii, MTL_SYSTEM_ITEMS_INTERFACE_N3) */ -- Bug 10404086
mtl_system_items_interface msii
SET process_flag = process_flag + 60000
WHERE transaction_type = 'UPDATE'
AND process_flag < 60000
AND set_process_id = xset_id
AND not exists (select mp1.organization_id
from mtl_parameters mp1
where msii.organization_id = mp1.master_organization_id);
UPDATE mtl_item_revisions_interface miri
SET process_flag = process_flag + 60000
WHERE transaction_type = 'UPDATE'
AND process_flag < 60000
AND set_process_id = xset_id
AND not exists (select mp1.organization_id
from mtl_parameters mp1
where miri.organization_id = mp1.master_organization_id);
ret_code_update := INVPOPIF.inopinp_OI_process_update (
NULL,
1,
val_item_flag,
pro_item_flag,
del_rec_flag,
prog_appid,
prog_id,
request_id,
user_id,
login_id,
err_text,
xset_id,
commit_flag,
default_flag);
UPDATE mtl_system_items_interface msii
SET process_flag = process_flag - 60000
WHERE transaction_type = 'UPDATE'
AND process_flag >= 60000
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = process_flag - 60000
WHERE transaction_type = 'UPDATE'
AND process_flag >= 60000
AND set_process_id = xset_id;
OPEN c_interface_items(cp_transaction_type => 'UPDATE');
OPEN c_interface_revs(cp_transaction_type => 'UPDATE');
UPDATE /*+ first_rows index(msii, MTL_SYSTEM_ITEMS_INTERFACE_N3) */ -- Bug 10404086
mtl_system_items_interface msii
SET process_flag = process_flag + 60000
WHERE transaction_type = 'UPDATE'
AND process_flag < 60000
AND set_process_id = xset_id
AND not exists (select mp1.organization_id
from mtl_parameters mp1
where msii.organization_id <> mp1.master_organization_id);
UPDATE mtl_item_revisions_interface miri
SET process_flag = process_flag + 60000
WHERE transaction_type = 'UPDATE'
AND process_flag < 60000
AND set_process_id = xset_id
AND not exists (select mp1.organization_id
from mtl_parameters mp1
where miri.organization_id <> mp1.master_organization_id);
ret_code_update := INVPOPIF.inopinp_OI_process_update (
NULL,
1,
val_item_flag,
pro_item_flag,
del_rec_flag,
prog_appid,
prog_id,
request_id,
user_id,
login_id,
err_text,
xset_id,
commit_flag,
default_flag);
UPDATE mtl_system_items_interface msii
SET process_flag = process_flag - 60000
WHERE transaction_type = 'UPDATE'
AND process_flag >= 60000
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = process_flag - 60000
WHERE transaction_type = 'UPDATE'
AND process_flag >= 60000
AND set_process_id = xset_id;
OPEN c_interface_items(cp_transaction_type => 'UPDATE');
OPEN c_interface_revs(cp_transaction_type => 'UPDATE');
ret_code_update := INVPOPIF.inopinp_OI_process_update (
org_id,
all_org,
val_item_flag,
pro_item_flag,
del_rec_flag,
prog_appid,
prog_id,
request_id,
user_id,
login_id,
err_text,
xset_id,
commit_flag,
default_flag);
UPDATE mtl_system_items_interface msii
SET process_flag = process_flag - 30000
WHERE transaction_type IN ('CREATE','SYNC') --3018673
AND process_flag >= 30000
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = process_flag - 30000
WHERE transaction_type IN ('CREATE','SYNC') --3018673
AND process_flag >= 30000
AND set_process_id = xset_id;
UPDATE mtl_system_items_interface msii
SET process_flag = process_flag - 20000
WHERE transaction_type IN ('CREATE','UPDATE')
AND process_flag >= 20000
AND set_process_id = xset_id;
UPDATE mtl_item_revisions_interface
SET process_flag = process_flag - 20000
WHERE transaction_type IN ('CREATE','UPDATE')
AND process_flag >= 20000
AND set_process_id = xset_id;
SELECT batch.source_system_id, NVL(opt.import_xref_only,'N')
INTO l_source_system_id, l_import_xref_only
FROM ego_import_batches_b batch
,ego_import_option_sets opt
WHERE batch.batch_id = xset_id
AND batch.batch_id = opt.batch_id;
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI');
INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Insert in to bulkloadrecs for Item');
INSERT INTO MTL_ITEM_BULKLOAD_RECS(
REQUEST_ID
,ENTITY_TYPE
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,TRANSACTION_TYPE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN)
(SELECT /*+ first_rows index(msi, MTL_SYSTEM_ITEMS_INTERFACE_N3) */ -- Bug 10404086
msi.REQUEST_ID
,'ITEM'
,msi.INVENTORY_ITEM_ID
,msi.ORGANIZATION_ID
,msi.TRANSACTION_TYPE
,NVL(msi.CREATION_DATE, SYSDATE)
,NVL(msi.CREATED_BY, -1)
,NVL(msi.LAST_UPDATE_DATE, SYSDATE)
,NVL(msi.LAST_UPDATED_BY, -1)
,msi.LAST_UPDATE_LOGIN
FROM mtl_system_items_interface msi
WHERE msi.request_id = request_id
and msi.set_process_id = xset_id
and msi.process_flag = 7
/* Bug 6139403 Do not raise BE for fake rows*/
and nvl(msi.confirm_status,'isnull')
not in ('CFC', 'CFM', 'FMR', 'UFN', 'UFS', 'UFM', 'FK', 'FEX'));
INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Insert in to bulkloadrecs for Revision.');
INSERT INTO MTL_ITEM_BULKLOAD_RECS(
REQUEST_ID
,ENTITY_TYPE
,INVENTORY_ITEM_ID
,ORGANIZATION_ID
,REVISION_ID
,TRANSACTION_TYPE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN)
(SELECT
mir.REQUEST_ID
,'ITEM_REVISION'
,mir.INVENTORY_ITEM_ID
,mir.ORGANIZATION_ID
,mir.REVISION_ID
,mir.TRANSACTION_TYPE
,NVL(mir.CREATION_DATE, SYSDATE)
,NVL(mir.CREATED_BY, -1)
,NVL(mir.LAST_UPDATE_DATE, SYSDATE)
,NVL(mir.LAST_UPDATED_BY, -1)
,mir.LAST_UPDATE_LOGIN
FROM mtl_item_revisions_interface mir
WHERE mir.request_id = request_id
and mir.set_process_id = xset_id
and mir.process_flag = 7);
err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while inserting records in MTL_ITEM_BULKLOAD_RECS',1,240);
INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPOPIF.indelitm_delete_item_oi');
ret_code := INVPOPIF.indelitm_delete_item_oi (err_text => err_msg,
com_flag => commit_flag,
xset_id => xset_id);
INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPOPIF.indelitm_delete_item_oi: ret_code=' || ret_code);
Delete processed rows (7) from the interface table
if the record was created for CM support, and a similar row with
process flag 5 exists in the interface table.
***/
DELETE
FROM mtl_system_items_interface msii
WHERE process_flag = 7
AND (inventory_item_id, organization_id, set_process_id) IN
(SELECT inventory_item_id, organization_id, set_process_id
FROM mtl_system_items_interface intf
WHERE set_process_id = xset_id
AND process_flag = 5);
SELECT COUNT(1) INTO mtl_count
FROM mtl_item_categories_interface mici
WHERE mici.SET_PROCESS_ID = xset_id
AND mici.process_flag IN (1,2,4);
, p_delete_rec_flag => del_rec_flag
, p_commit_flag => commit_flag
, p_prog_appid => prog_appid
, p_prog_id => prog_id
, p_request_id => request_id
, p_user_id => user_id
, p_login_id => login_id
, p_gather_stats => gather_stats /* Added for Bug 8532728 */ );
IF (ret_code_update <> 0) THEN
ret_code := ret_code_update;
SELECT COUNT(1) INTO mtl_count
FROM mtl_desc_elem_val_interface mdei
WHERE mdei.set_process_id = xset_id
AND mdei.process_flag IN (1, 2, 4);
, p_delete_rec_flag => del_rec_flag
, p_commit_flag => commit_flag
, p_prog_appid => prog_appid
, p_prog_id => prog_id
, p_request_id => request_id
, p_user_id => user_id
, p_login_id => login_id);
FUNCTION inopinp_OI_process_update
(
org_id NUMBER,
all_org NUMBER := 1,
val_item_flag NUMBER := 1,
pro_item_flag NUMBER := 1,
del_rec_flag NUMBER := 1,
prog_appid NUMBER := -1,
prog_id NUMBER := -1,
request_id NUMBER := -1,
user_id NUMBER := -1,
login_id NUMBER := -1,
err_text IN OUT NOCOPY VARCHAR2,
xset_id IN NUMBER DEFAULT -999,
commit_flag IN NUMBER DEFAULT 1,
default_flag IN NUMBER DEFAULT 1)
RETURN INTEGER IS
ret_code NUMBER:= 1;
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update : begin org_id: ' || TO_CHAR(org_id));
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
UPDATE_ITEM_CATALOG_ID(
p_set_id => xset_id
,p_prog_appid => prog_appid
,p_prog_id => prog_id
,p_request_id => request_id
,p_user_id => user_id
,p_login_id => login_id
,x_err_text => err_text);
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVNIRIS.change_policy_check');
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVNIRIS.change_policy_check RETURN'||ret_code);
err_msg := 'INVNIRIS.change_policy_check: error in policy phase of UPDATE;' ||
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD1B.mtl_pr_assign_item_data_update');
ret_code := INVUPD1B.mtl_pr_assign_item_data_update (
org_id => org_id,
all_org => all_org,
prog_appid => prog_appid,
prog_id => prog_id,
request_id => request_id,
user_id => user_id,
login_id => login_id,
err_text => err_msg,
xset_id => xset_id);
elsif default_flag = 2 then --Rules ER 11830273, populate revision_id for UPDATE case when default_flag is 2
IF l_inv_debug_level IN(101, 102) THEN
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: POPULATE REVISION_ID TO mtl_item_revisions_interface IF REVISION_ID IS NULL');
UPDATE mtl_item_revisions_interface MIRI set revision_id = (SELECT R.REVISION_ID FROM MTL_ITEM_REVISIONS_B R WHERE R.REVISION = MIRI.REVISION AND R.ORGANIZATION_ID = MIRI.ORGANIZATION_ID AND R.INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID )
where process_flag = 1
AND set_process_id = xset_id
AND (organization_id = org_id or all_org = 1)
AND REVISION_ID IS NULL
AND REVISION IS NOT NULL;
err_msg := 'INVPOPIF.inopinp_OI_process_update: error in ASSIGN phase of UPDATE;' ||
err_msg := 'INVPOPIF.inopinp_OI_process_create: error in NIR ASSIGN phase of UPDATE;' ||
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD1B.mtl_pr_validate_item_update');
ret_code := INVUPD1B.mtl_pr_validate_item_update (
org_id => org_id,
all_org => all_org,
prog_appid => prog_appid,
prog_id => prog_id,
request_id => request_id,
user_id => user_id,
login_id => login_id,
err_text => err_msg,
xset_id => xset_id);
err_msg := 'INVPOPIF.inopinp_OI_process_update: error in VALIDATE phase of UPDATE;' ||
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD2B.inproit_process_item_update');
ret_code := INVUPD2B.inproit_process_item_update (
prg_appid => prog_appid,
prg_id => prog_id,
req_id => request_id,
user_id => user_id,
login_id => login_id,
error_message => err_msg,
message_name => err_msg_name,
table_name => table_name,
xset_id => xset_id,
commit_flag => commit_flag); /*Added to fix Bug 8359046*/
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: done INVUPD2B.inproit_process_item_update: ret_code=' || ret_code);
err_msg := 'INVPOPIF.inopinp_OI_process_update: error in PROCESS phase of UPDATE;' ||
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI');
err_text := substr('INVPOPIF.inopinp_OI_process_update ' || SQLERRM , 1,240);
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: About to rollback.');
END inopinp_OI_process_update;
SELECT transaction_id, organization_id
FROM mtl_system_items_interface
WHERE process_flag = 4
AND set_process_id = xset_id
AND transaction_type = 'CREATE';
update_recs NUMBER := 0;
INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
UPDATE_ITEM_CATALOG_ID(
p_set_id => xset_id
,p_prog_appid => prog_appid
,p_prog_id => prog_id
,p_request_id => request_id
,p_user_id => user_id
,p_login_id => login_id
,x_err_text => err_text);
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE process_flag = 4
AND set_process_id = xset_id
AND transaction_type = 'CREATE';
SELECT 'Y' INTO l_child_records
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM mtl_system_items_interface msii,
mtl_parameters mp
WHERE transaction_type = 'CREATE'
AND process_flag = 7
AND set_process_id = xset_id
AND msii.organization_id = mp.organization_id
AND mp.organization_id <> mp.master_organization_id);
FUNCTION indelitm_delete_item_oi
(
err_text OUT NOCOPY VARCHAR2,
com_flag IN NUMBER DEFAULT 1,
xset_id IN NUMBER DEFAULT -999
)
RETURN INTEGER
IS
stmt_num NUMBER;
INVPUTLI.info('INVPOPIF.indelitm_delete_item_oi: begin');
DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE process_flag = l_process_flag_7
AND set_process_id in (xset_id, xset_id + 1000000000000)
AND rownum < l_rownum;
DELETE FROM MTL_ITEM_REVISIONS_INTERFACE
WHERE PROCESS_FLAG = l_process_flag_7
AND set_process_id = xset_id
AND rownum < l_rownum;
INVPUTLI.info('INVPOPIF.indelitm_delete_item_oi: end');
err_text := SUBSTR('INVPOPIF.indelitm_delete_item_oi(' || stmt_num || ')' || SQLERRM, 1,240);
END indelitm_delete_item_oi;
PROCEDURE UPDATE_SYNC_RECORDS(p_set_id IN NUMBER) IS
CURSOR c_items_table IS
SELECT rowid
,organization_id
,inventory_item_id
,segment1
,segment2
,segment3
,segment4
,segment5
,segment6
,segment7
,segment8
,segment9
,segment10
,segment11
,segment12
,segment13
,segment14
,segment15
,segment16
,segment17
,segment18
,segment19
,segment20
,item_number
,transaction_id
,transaction_type
FROM mtl_system_items_interface
WHERE set_process_id = p_set_id
AND process_flag = 1
AND (transaction_type = 'SYNC' OR
(transaction_type = 'UPDATE' AND inventory_item_id IS NOT NULL AND
(item_number IS NOT NULL OR
SEGMENT1 IS NOT NULL OR SEGMENT2 IS NOT NULL OR SEGMENT3 IS NOT NULL OR SEGMENT4 IS NOT NULL OR
SEGMENT5 IS NOT NULL OR SEGMENT6 IS NOT NULL OR SEGMENT7 IS NOT NULL OR SEGMENT8 IS NOT NULL OR
SEGMENT9 IS NOT NULL OR SEGMENT10 IS NOT NULL OR SEGMENT11 IS NOT NULL OR SEGMENT12 IS NOT NULL OR
SEGMENT13 IS NOT NULL OR SEGMENT14 IS NOT NULL OR SEGMENT15 IS NOT NULL OR SEGMENT16 IS NOT NULL OR
SEGMENT17 IS NOT NULL OR SEGMENT18 IS NOT NULL OR SEGMENT19 IS NOT NULL OR SEGMENT20 IS NOT NULL
)
)
)
FOR UPDATE OF transaction_type;
SELECT rowid
,organization_id
,inventory_item_id
,item_number
,revision_id
,revision
,transaction_id
,transaction_type
FROM mtl_item_revisions_interface
WHERE set_process_id = p_set_id
AND process_flag = 1
AND transaction_type = 'SYNC'
FOR UPDATE OF transaction_type;
SELECT 1 ,concatenated_segments
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = cp_item_id
--Bug 4964023 - Adding the org id clause for org assign case
AND organization_id = cp_org_id;
SELECT inventory_item_id
FROM mtl_system_items_b_kfv
WHERE concatenated_segments = cp_item_number
AND organization_id = cp_org_id;
SELECT 1
FROM mtl_item_revisions
WHERE inventory_item_id = cp_item_id
AND (revision_id = cp_rev_id
OR revision = cp_revision)
AND organization_id = cp_org_id ;
l_updateable_item_number varchar2(10);
SELECT 1
INTO l_masterOrg
FROM mtl_parameters
WHERE master_organization_id = cp_orgid
AND rownum = 1;
fnd_profile.get('INV_UPDATEABLE_ITEM', l_updateable_item_number);
IF ( item_record.transaction_type = 'UPDATE' AND l_item_exist <> 0 AND l_item_number <> item_record.item_number) THEN
-- UPDATE row MSII item number is different than compared to
-- MSIBKFV item number fetched using MSII inventory_item_id
-- Both l_item_number and item_record.item_number cannot be NULL, hence NVL is not used
IF l_inv_debug_level IN(101, 102) THEN
INVPUTLI.info('INVPOPIF.update sync records: Item Number update' || item_record.item_number || ' ' || l_item_number || ' ' || l_item_id);
IF ( l_updateable_item_number <> 'Y' ) THEN
dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
fnd_global.prog_appl_id,fnd_global.conc_program_id,fnd_global.conc_request_id,item_record.transaction_id,
'INVPOPIF: Update to Item number not allowed',
'ITEM NUMBER',
'MTL_SYSTEM_ITEMS_INTERFACE',
'INV_ITEM_NUMBER_NO_UDPATE',
l_err_text);
update mtl_system_items_interface
set process_flag = 3
where rowid = item_record.rowid ;
'INVPOPIF: Update to Item number not allowed in child organization',
'ITEM NUMBER',
'MTL_SYSTEM_ITEMS_INTERFACE',
'INV_ITEM_NUMBER_ORG_NO_UDPATE',
l_err_text);
update mtl_system_items_interface
set process_flag = 3
where rowid = item_record.rowid ;
INVPUTLI.info('INVPOPIF.update sync records: Item Number update to duplicate case:' || item_record.item_number || ' ' || l_item_exist || ' ' || l_item_id);
'INVPOPIF: Update to Duplicate Item number',
'ITEM NUMBER',
'MTL_SYSTEM_ITEMS_INTERFACE',
'INV_UPDATE_TO_EXIST_ITEM_NAME',
l_err_text);
update mtl_system_items_interface
set process_flag = 3
where rowid = item_record.rowid ;
UPDATE mtl_system_items_interface
SET transaction_type = 'UPDATE'
WHERE rowid = item_record.rowid
AND transaction_type = 'SYNC';
UPDATE mtl_system_items_interface
SET transaction_type = 'CREATE'
WHERE rowid = item_record.rowid
AND transaction_type = 'SYNC';
UPDATE mtl_item_revisions_interface
SET transaction_type = 'UPDATE'
WHERE rowid = revision_record.rowid;
UPDATE mtl_item_revisions_interface
SET transaction_type = 'CREATE'
WHERE rowid = revision_record.rowid;
END UPDATE_SYNC_RECORDS;
PROCEDURE UPDATE_ITEM_CATALOG_ID(
p_set_id IN NUMBER
,p_prog_appid IN NUMBER
,p_prog_id IN NUMBER
,p_request_id IN NUMBER
,p_user_id IN NUMBER
,p_login_id IN NUMBER
,x_err_text IN OUT NOCOPY VARCHAR2) IS
LOGGING_ERR EXCEPTION;
CURSOR update_catg_name(p_catg_name IN VARCHAR2) IS
SELECT ROWID, msii.TRANSACTION_ID
FROM mtl_system_items_interface msii
WHERE SET_PROCESS_ID = p_set_id
AND msii.ITEM_CATALOG_GROUP_NAME = p_catg_name;
SELECT /*+ first_rows index(msii, MTL_SYSTEM_ITEMS_INTERFACE_N3) */
DISTINCT msii.ITEM_CATALOG_GROUP_NAME ||
':' || bkfv.ITEM_CATALOG_GROUP_ID
BULK COLLECT INTO Item_Catalogs_Table
FROM mtl_system_items_interface msii,
mtl_item_catalog_groups_b_kfv bkfv
WHERE msii.ITEM_CATALOG_GROUP_ID IS NULL
AND msii.ITEM_CATALOG_GROUP_NAME IS NOT NULL
AND msii.SET_PROCESS_ID = p_set_id
AND msii.ITEM_CATALOG_GROUP_NAME = bkfv.CONCATENATED_SEGMENTS(+);
FOR cr IN update_catg_name(p_catg_name => l_Item_Catalog_Group_Name)
LOOP
l_dumm_status := INVPUOPI.mtl_log_interface_err(
-1,
p_user_id,
p_login_id,
p_prog_appid,
p_prog_id,
p_request_id,
cr.transaction_id,
'INVPOPIF: Invalid Item Catalog Group Name',
'ITEM_CATALOG_GROUP_NAME',
'MTL_SYSTEM_ITEMS_INTERFACE',
'INV_IOI_CATG_NAME_INVALID',
x_err_text);
UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
SET PROCESS_FLAG = 3
WHERE ROWID = cr.ROWID ;
UPDATE /*+ first_rows index(MSII, MTL_SYSTEM_ITEMS_INTERFACE_N3) */
MTL_SYSTEM_ITEMS_INTERFACE MSII
SET MSII.ITEM_CATALOG_GROUP_ID = l_Item_Catalog_Group_ID
WHERE SET_PROCESS_ID = p_set_id
AND MSII.ITEM_CATALOG_GROUP_NAME = l_Item_Catalog_Group_Name;
END UPDATE_ITEM_CATALOG_ID;
SELECT msii.item_catalog_group_id, msii.ROWID, msii.transaction_id
FROM mtl_system_items_interface msii
WHERE msii.item_catalog_group_id IS NOT NULL
AND msii.set_process_id = p_set_id -- p_set_process_id
AND msii.process_flag = 1
AND NOT EXISTS
( SELECT 1 FROM EGO_MTL_CATALOG_GRP_VERS_B emcgvb
WHERE emcgvb.item_catalog_group_id=msii.item_catalog_group_id
AND emcgvb.VERSION_SEQ_ID <> 0
AND emcgvb.START_ACTIVE_DATE IS NOT NULL AND emcgvb.START_ACTIVE_DATE <= SYSDATE) ;
update mtl_system_items_interface
set process_flag = 3
where rowid = cr.rowid ;