[Home] [Help]
14:
15: G_NEEDS_REQUEST_ID_STAMP CONSTANT NUMBER := -1;
16: G_NEED_TO_LOG_ERROR CONSTANT NUMBER := -3;
17:
18: SUBTYPE MSII_ROW IS MTL_SYSTEM_ITEMS_INTERFACE%ROWTYPE;
19: TYPE COLUMN_NAMES IS TABLE OF ALL_TAB_COLUMNS.COLUMN_NAME%TYPE;
20: SUBTYPE EIUAI_ROW IS EGO_ITM_USR_ATTR_INTRFC%ROWTYPE;
21: TYPE EIUAI_ROWS IS TABLE OF EIUAI_ROW INDEX BY BINARY_INTEGER;
22: SUBTYPE IAssocs_Row IS EGO_ITEM_ASSOCIATIONS_INTF%ROWTYPE;
100: SELECT
101: ORGANIZATION_ID,
102: TRANSACTION_ID,
103: PROCESS_FLAG
104: FROM MTL_SYSTEM_ITEMS_INTERFACE
105: WHERE SET_PROCESS_ID = p_data_set_id
106: AND PROCESS_FLAG IN (33379, 33389);
107:
108: CURSOR c_ssxref_null_miri IS
161: --R12C:BEGIN
162: BEGIN
163: Debug_Conc_Log( 'Err_null_ssxref_ssid - Begin' );
164: -- MSII
165: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
166: SET PROCESS_FLAG = (CASE WHEN MSII.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
167: TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
168: PROGRAM_APPLICATION_ID = l_prog_appid,
169: PROGRAM_ID = l_prog_id,
163: Debug_Conc_Log( 'Err_null_ssxref_ssid - Begin' );
164: -- MSII
165: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
166: SET PROCESS_FLAG = (CASE WHEN MSII.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
167: TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
168: PROGRAM_APPLICATION_ID = l_prog_appid,
169: PROGRAM_ID = l_prog_id,
170: REQUEST_ID = l_request_id
171: WHERE SET_PROCESS_ID = p_data_set_id
180:
181: -- MIRI
182: UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
183: SET PROCESS_FLAG = (CASE WHEN MIRI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
184: TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
185: PROGRAM_APPLICATION_ID = l_prog_appid,
186: PROGRAM_ID = l_prog_id,
187: REQUEST_ID = l_request_id
188: WHERE SET_PROCESS_ID = p_data_set_id
197:
198: -- MICI
199: UPDATE MTL_ITEM_CATEGORIES_INTERFACE MICI
200: SET PROCESS_FLAG = (CASE WHEN MICI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
201: TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
202: PROGRAM_APPLICATION_ID = l_prog_appid,
203: PROGRAM_ID = l_prog_id,
204: REQUEST_ID = l_request_id
205: WHERE SET_PROCESS_ID = p_data_set_id
214:
215: -- EIUAI
216: UPDATE EGO_ITM_USR_ATTR_INTRFC EIUAI
217: SET PROCESS_STATUS = (CASE WHEN EIUAI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
218: TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
219: PROGRAM_APPLICATION_ID = l_prog_appid,
220: PROGRAM_ID = l_prog_id,
221: REQUEST_ID = l_request_id
222: WHERE DATA_SET_ID = p_data_set_id
231:
232: -- EIPI
233: UPDATE EGO_ITEM_PEOPLE_INTF EIPI
234: SET PROCESS_STATUS = (CASE WHEN EIPI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
235: TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
236: PROGRAM_APPLICATION_ID = l_prog_appid,
237: PROGRAM_ID = l_prog_id,
238: REQUEST_ID = l_request_id
239: WHERE DATA_SET_ID = p_data_set_id
248:
249: -- EAI
250: UPDATE EGO_AML_INTF EAI
251: SET PROCESS_FLAG = (CASE WHEN EAI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
252: TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
253: PROGRAM_APPLICATION_ID = l_prog_appid,
254: PROGRAM_ID = l_prog_id,
255: REQUEST_ID = l_request_id
256: WHERE DATA_SET_ID = p_data_set_id
265:
266: -- R12C:BEGIN ASSOCSINTF
267: UPDATE EGO_ITEM_ASSOCIATIONS_INTF EIAI
268: SET PROCESS_FLAG = (CASE WHEN EIAI.SOURCE_SYSTEM_REFERENCE IS NULL THEN 33379 ELSE 33389 END),
269: TRANSACTION_ID = NVL(TRANSACTION_ID, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL),
270: PROGRAM_APPLICATION_ID = l_prog_appid,
271: PROGRAM_ID = l_prog_id,
272: REQUEST_ID = l_request_id
273: WHERE BATCH_ID = p_data_set_id
296: ,l_request_id
297: ,i.TRANSACTION_ID
298: ,l_msg_text
299: ,'SOURCE_SYSTEM_REFERENCE'
300: ,'MTL_SYSTEM_ITEMS_INTERFACE'
301: ,'INV_IOI_ERR'
302: ,l_err_text);
303:
304: ELSIF i.PROCESS_FLAG = 33389 THEN
313: ,l_request_id
314: ,i.TRANSACTION_ID
315: ,l_msg_text
316: ,'SOURCE_SYSTEM_ID'
317: ,'MTL_SYSTEM_ITEMS_INTERFACE'
318: ,'INV_IOI_ERR'
319: ,l_err_text);
320: END IF;
321: END LOOP;
544: --R12C: END
545:
546: Debug_Conc_Log( 'Err_null_ssxref_ssid - Finished inserting Errors' );
547:
548: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
549: SET PROCESS_FLAG =3
550: WHERE PROCESS_FLAG IN (33379, 33389)
551: AND SET_PROCESS_ID = p_data_set_id;
552:
635: --6602290 : Stats gather through profile
636: l_stats_profile NUMBER := NVL(FND_PROFILE.VALUE('EGO_GATHER_STATS'),100);
637: BEGIN
638: Debug_Conc_Log('Starting Gather_Stats_For_Intf_Tables');
639: -- checking whether stats needs to be collected on MTL_SYSTEM_ITEMS_INTERFACE
640: SELECT COUNT(1) INTO l_records
641: FROM MTL_SYSTEM_ITEMS_INTERFACE
642: WHERE SET_PROCESS_ID = p_data_set_id;
643:
637: BEGIN
638: Debug_Conc_Log('Starting Gather_Stats_For_Intf_Tables');
639: -- checking whether stats needs to be collected on MTL_SYSTEM_ITEMS_INTERFACE
640: SELECT COUNT(1) INTO l_records
641: FROM MTL_SYSTEM_ITEMS_INTERFACE
642: WHERE SET_PROCESS_ID = p_data_set_id;
643:
644: l_inv_installed := FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema);
645: IF (l_records > l_stats_profile) AND l_inv_installed AND l_schema IS NOT NULL THEN
642: WHERE SET_PROCESS_ID = p_data_set_id;
643:
644: l_inv_installed := FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema);
645: IF (l_records > l_stats_profile) AND l_inv_installed AND l_schema IS NOT NULL THEN
646: FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_SYSTEM_ITEMS_INTERFACE');
647: Debug_Conc_Log('Collected Statistics on MTL_SYSTEM_ITEMS_INTERFACE');
648: END IF;
649:
650: -- checking whether stats needs to be collected on MTL_ITEM_REVISIONS_INTERFACE
643:
644: l_inv_installed := FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema);
645: IF (l_records > l_stats_profile) AND l_inv_installed AND l_schema IS NOT NULL THEN
646: FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_SYSTEM_ITEMS_INTERFACE');
647: Debug_Conc_Log('Collected Statistics on MTL_SYSTEM_ITEMS_INTERFACE');
648: END IF;
649:
650: -- checking whether stats needs to be collected on MTL_ITEM_REVISIONS_INTERFACE
651: SELECT COUNT(1) INTO l_records
1642: , 0 cnt
1643: , 0 rnk
1644: , 'N' excluded_flag
1645: , msii.*
1646: FROM MTL_SYSTEM_ITEMS_INTERFACE msii;
1647:
1648: /*
1649: * Types for fetching the rows to merged.
1650: */
1655: l_merged_rows MSII_ROWS;
1656: l_merge_base MSII_ROW;
1657: l_old_rowids UROWID_TABLE;
1658:
1659: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE := p_ss_id;
1660: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
1661: l_candidate_trans MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_TYPE%TYPE;
1662:
1663: l_cur_rank PLS_INTEGER := 0; -- because rank() starts at 1
1656: l_merge_base MSII_ROW;
1657: l_old_rowids UROWID_TABLE;
1658:
1659: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE := p_ss_id;
1660: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
1661: l_candidate_trans MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_TYPE%TYPE;
1662:
1663: l_cur_rank PLS_INTEGER := 0; -- because rank() starts at 1
1664: l_mrow_ix PLS_INTEGER := 0;
1657: l_old_rowids UROWID_TABLE;
1658:
1659: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE := p_ss_id;
1660: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
1661: l_candidate_trans MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_TYPE%TYPE;
1662:
1663: l_cur_rank PLS_INTEGER := 0; -- because rank() starts at 1
1664: l_mrow_ix PLS_INTEGER := 0;
1665: l_is_pdh_batch BOOLEAN;
1664: l_mrow_ix PLS_INTEGER := 0;
1665: l_is_pdh_batch BOOLEAN;
1666: l_excluded_flag VARCHAR2(1);
1667:
1668: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE := p_master_org_id;
1669: l_pdh_batch_flag FLAG := p_is_pdh_batch;
1670: l_proc_log_prefix CONSTANT VARCHAR2( 30 ) := 'merge_items - ';
1671: BEGIN
1672: Debug_Conc_Log( l_proc_log_prefix || 'Entering' );
1698: )
1699: rnk
1700: , null EXCLUDED_FLAG
1701: , msii.*
1702: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
1703: WHERE PROCESS_FLAG = 1
1704: AND SET_PROCESS_ID = p_batch_id
1705: AND ( SOURCE_SYSTEM_ID IS NULL
1706: OR SOURCE_SYSTEM_ID = G_PDH_SOURCE_SYSTEM_ID
1740: )
1741: )
1742: EXCLUDED_FLAG
1743: , msii.*
1744: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
1745: WHERE PROCESS_FLAG = 0
1746: AND SET_PROCESS_ID = p_batch_id
1747: AND SOURCE_SYSTEM_ID = l_ss_id
1748: AND SOURCE_SYSTEM_REFERENCE IS NOT NULL
1952: /* generate using the following script:
1953: SET LINESIZE 200
1954: SELECT 'if l_merged_rows( l_mrow_ix ).' ||column_name || ' is null then l_merged_rows( l_mrow_ix ).' || column_name || ' := old_row.' || column_name || '; end if; '
1955: FROM ALL_TAB_COLUMNS
1956: WHERE TABLE_NAME = 'MTL_SYSTEM_ITEMS_INTERFACE'
1957: AND COLUMN_NAME NOT IN
1958: ( -- special cases (for merge)
1959: 'INVENTORY_ITEM_ID'
1960: , 'ITEM_NUMBER'
2405: IF l_merged_rows IS NOT NULL THEN
2406: -- delete
2407: Debug_Conc_Log( l_proc_log_prefix || 'Deleting ' || l_old_rowids.COUNT || ' old rows ...' );
2408: FORALL rid_ix IN INDICES OF l_old_rowids
2409: DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
2410: WHERE ROWID = l_old_rowids( rid_ix );
2411: -- insert
2412: Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
2413: FORALL row_index IN INDICES OF l_merged_rows
2410: WHERE ROWID = l_old_rowids( rid_ix );
2411: -- insert
2412: Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
2413: FORALL row_index IN INDICES OF l_merged_rows
2414: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
2415: VALUES l_merged_rows( row_index );
2416: END IF;
2417:
2418: IF p_commit = FND_API.G_TRUE THEN
3093: SELECT ROWID rid
3094: , 0 cnt
3095: , 0 rnk
3096: , msii.*
3097: FROM MTL_SYSTEM_ITEMS_INTERFACE msii;
3098:
3099: TYPE MSII_ROWS IS TABLE OF MSII_ROW INDEX BY BINARY_INTEGER;
3100: TYPE MSII_CURSOR IS REF CURSOR;
3101: c_target_rows MSII_CURSOR;
3105: l_new_rows MSII_ROWS;
3106: l_merged_row MSII_ROW;
3107: l_old_rowids UROWID_TABLE;
3108:
3109: l_candidate_trans MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_TYPE%TYPE;
3110:
3111: l_cur_rank PLS_INTEGER := 0; -- because rank() starts at 1
3112: l_mrow_ix PLS_INTEGER := 0;
3113: l_new_row_idx PLS_INTEGER := 0;
3111: l_cur_rank PLS_INTEGER := 0; -- because rank() starts at 1
3112: l_mrow_ix PLS_INTEGER := 0;
3113: l_new_row_idx PLS_INTEGER := 0;
3114:
3115: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE := p_master_org_id;
3116: l_proc_log_prefix CONSTANT VARCHAR2( 30 ) := 'Merge_Items_For_Import - ';
3117: BEGIN
3118: Debug_Conc_Log( l_proc_log_prefix || 'Entering' );
3119: Debug_Conc_Log( l_proc_log_prefix || 'Batch ID: ' || p_batch_id );
3117: BEGIN
3118: Debug_Conc_Log( l_proc_log_prefix || 'Entering' );
3119: Debug_Conc_Log( l_proc_log_prefix || 'Batch ID: ' || p_batch_id );
3120: -- making sure that item_number always has a value for ready records
3121: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
3122: SET ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
3123: FROM MTL_SYSTEM_ITEMS_KFV
3124: WHERE INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
3125: AND ORGANIZATION_ID = MSII.ORGANIZATION_ID)
3135: ROWID rid,
3136: COUNT( * ) OVER ( PARTITION BY ITEM_NUMBER, ORGANIZATION_ID) cnt,
3137: RANK() OVER ( ORDER BY ITEM_NUMBER, ORGANIZATION_ID) rnk,
3138: msii.*
3139: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
3140: WHERE PROCESS_FLAG = 1
3141: AND SET_PROCESS_ID = p_batch_id
3142: AND ITEM_NUMBER IS NOT NULL
3143: AND ORGANIZATION_ID IS NOT NULL
3724: IF l_merged_rows IS NOT NULL THEN
3725: -- delete
3726: Debug_Conc_Log( l_proc_log_prefix || 'Deleting ' || l_old_rowids.COUNT || ' old rows ...' );
3727: FORALL rid_ix IN INDICES OF l_old_rowids
3728: DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
3729: WHERE ROWID = l_old_rowids( rid_ix );
3730: -- insert
3731: Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
3732: FORALL row_index IN INDICES OF l_merged_rows
3729: WHERE ROWID = l_old_rowids( rid_ix );
3730: -- insert
3731: Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
3732: FORALL row_index IN INDICES OF l_merged_rows
3733: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
3734: VALUES l_merged_rows( row_index );
3735: END IF;
3736: Debug_Conc_Log( l_proc_log_prefix || 'Exiting' );
3737: EXCEPTION WHEN OTHERS THEN
4926: l_proc_log_prefix VARCHAR2(50) := 'Demerge_Batch_After_Import - ';
4927: BEGIN
4928: Debug_Conc_Log( l_proc_log_prefix || 'Starting ...' );
4929: -- processing items and org assignments
4930: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
4931: SET (PROCESS_FLAG, INVENTORY_ITEM_ID) =
4932: (SELECT PROCESS_FLAG, INVENTORY_ITEM_ID
4933: FROM MTL_SYSTEM_ITEMS_INTERFACE msii_merged
4934: WHERE msii_merged.ITEM_NUMBER = msii.ITEM_NUMBER
4929: -- processing items and org assignments
4930: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
4931: SET (PROCESS_FLAG, INVENTORY_ITEM_ID) =
4932: (SELECT PROCESS_FLAG, INVENTORY_ITEM_ID
4933: FROM MTL_SYSTEM_ITEMS_INTERFACE msii_merged
4934: WHERE msii_merged.ITEM_NUMBER = msii.ITEM_NUMBER
4935: AND msii_merged.ORGANIZATION_ID = msii.ORGANIZATION_ID
4936: AND msii_merged.REQUEST_ID = msii.REQUEST_ID
4937: AND msii_merged.SET_PROCESS_ID = msii.SET_PROCESS_ID
5038: intf.TRANSACTION_ID,
5039: c.CROSS_REFERENCE EXISTING_GTIN,
5040: c.DESCRIPTION EXISTING_GTIN_DESC,
5041: c.CROSS_REFERENCE_ID
5042: FROM MTL_SYSTEM_ITEMS_INTERFACE intf, MTL_PARAMETERS p, MTL_CROSS_REFERENCES c
5043: WHERE intf.SET_PROCESS_ID = p_data_set_id
5044: AND intf.ORGANIZATION_ID = p.ORGANIZATION_ID
5045: AND p.ORGANIZATION_ID = p.MASTER_ORGANIZATION_ID
5046: AND intf.PROCESS_FLAG IN (5,7)
5097: ,l_request_id
5098: ,i.TRANSACTION_ID
5099: ,l_msg_text
5100: ,'GLOBAL_TRADE_ITEM_NUMBER'
5101: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5102: ,'INV_IOI_ERR'
5103: ,l_err_text);
5104: ELSE
5105: Debug_Conc_Log('Edit Cross Reference Privilege is present on item');
5124: ,l_request_id
5125: ,i.TRANSACTION_ID
5126: ,l_msg_text
5127: ,'GLOBAL_TRADE_ITEM_NUMBER'
5128: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5129: ,'INV_IOI_ERR'
5130: ,l_err_text);
5131: l_error := TRUE;
5132: END;
5144: ,l_request_id
5145: ,i.TRANSACTION_ID
5146: ,l_msg_text
5147: ,'GLOBAL_TRADE_ITEM_NUMBER'
5148: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5149: ,'INV_IOI_ERR'
5150: ,l_err_text);
5151: l_error := TRUE;
5152: END IF; --end IF LENGTH(i.GLOBAL_TRADE_ITEM_NUMBER
5164: ,l_request_id
5165: ,i.TRANSACTION_ID
5166: ,l_msg_text
5167: ,'GLOBAL_TRADE_ITEM_NUMBER'
5168: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5169: ,'INV_IOI_ERR'
5170: ,l_err_text);
5171: l_error := TRUE;
5172: END IF; --end IF (NOT l_error) AND
5184: ,l_request_id
5185: ,i.TRANSACTION_ID
5186: ,l_msg_text
5187: ,'GLOBAL_TRADE_ITEM_NUMBER'
5188: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5189: ,'INV_IOI_ERR'
5190: ,l_err_text);
5191: l_error := TRUE;
5192: END IF;
5205: ,l_request_id
5206: ,i.TRANSACTION_ID
5207: ,l_msg_text
5208: ,'GLOBAL_TRADE_ITEM_NUMBER'
5209: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5210: ,'INV_IOI_ERR'
5211: ,l_err_text);
5212: l_error := TRUE;
5213: END IF;
5226: ,l_request_id
5227: ,i.TRANSACTION_ID
5228: ,l_msg_text
5229: ,'GLOBAL_TRADE_ITEM_NUMBER'
5230: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5231: ,'INV_IOI_ERR'
5232: ,l_err_text);
5233: l_error := TRUE;
5234: END IF;
5246: ,l_request_id
5247: ,i.TRANSACTION_ID
5248: ,l_msg_text
5249: ,'GLOBAL_TRADE_ITEM_NUMBER'
5250: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5251: ,'INV_IOI_ERR'
5252: ,l_err_text);
5253: l_error := TRUE;
5254: END IF;
5267: ,l_request_id
5268: ,i.TRANSACTION_ID
5269: ,l_msg_text
5270: ,'GLOBAL_TRADE_ITEM_NUMBER'
5271: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5272: ,'INV_IOI_ERR'
5273: ,l_err_text);
5274: l_error := TRUE;
5275: END IF;
5302: ,l_request_id
5303: ,i.TRANSACTION_ID
5304: ,l_msg_text
5305: ,'GLOBAL_TRADE_ITEM_NUMBER'
5306: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5307: ,'INV_IOI_ERR'
5308: ,l_err_text);
5309: l_error := TRUE;
5310: EXCEPTION WHEN NO_DATA_FOUND THEN
5396: ,l_request_id
5397: ,i.TRANSACTION_ID
5398: ,l_msg_text
5399: ,'GLOBAL_TRADE_ITEM_NUMBER'
5400: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5401: ,'INV_IOI_ERR'
5402: ,l_err_text);
5403: END IF; -- IF i.EXISTING_GTIN <> i.GLOBAL_TRADE_ITEM_NUMBER THEN
5404:
5427: ,l_request_id
5428: ,l_transaction_id
5429: ,l_msg_text
5430: ,'GLOBAL_TRADE_ITEM_NUMBER'
5431: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5432: ,'INV_IOI_ERR'
5433: ,l_err_text);
5434: END IF;
5435: END IF;
5566: INVENTORY_ITEM_ID,
5567: ORGANIZATION_ID,
5568: TRANSACTION_ID,
5569: TRANSACTION_TYPE
5570: FROM MTL_SYSTEM_ITEMS_INTERFACE msi
5571: WHERE msi.PROCESS_FLAG IN (5, 7)
5572: AND msi.SET_PROCESS_ID = p_data_set_id
5573: AND msi.SOURCE_SYSTEM_ID IS NOT NULL
5574: AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
5591: msi.ORGANIZATION_ID,
5592: msi.TRANSACTION_ID,
5593: msi.TRANSACTION_TYPE,
5594: mcr.CROSS_REFERENCE_ID
5595: FROM MTL_SYSTEM_ITEMS_INTERFACE msi, MTL_CROSS_REFERENCES mcr
5596: WHERE msi.PROCESS_FLAG IN (5, 7)
5597: AND msi.SET_PROCESS_ID = p_data_set_id
5598: AND msi.SOURCE_SYSTEM_ID IS NOT NULL
5599: AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
5620: ORGANIZATION_ID,
5621: TRANSACTION_ID,
5622: TRANSACTION_TYPE,
5623: ROWID AS ROW_ID
5624: FROM MTL_SYSTEM_ITEMS_INTERFACE msi
5625: WHERE msi.PROCESS_FLAG IN (0, 11)
5626: AND msi.SET_PROCESS_ID = p_data_set_id
5627: AND msi.SOURCE_SYSTEM_ID IS NOT NULL
5628: AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
5647: msi.TRANSACTION_ID,
5648: msi.TRANSACTION_TYPE,
5649: mcr.CROSS_REFERENCE_ID,
5650: msi.ROWID AS ROW_ID
5651: FROM MTL_SYSTEM_ITEMS_INTERFACE msi, MTL_CROSS_REFERENCES mcr
5652: WHERE msi.PROCESS_FLAG IN (0, 11)
5653: AND msi.SET_PROCESS_ID = p_data_set_id
5654: AND msi.SOURCE_SYSTEM_ID IS NOT NULL
5655: AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
5679: dumm_status NUMBER;
5680: l_org_id NUMBER;
5681:
5682: l_import_xref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
5683: l_inventory_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
5684: l_transaction_id MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_ID%TYPE;
5685: BEGIN
5686: Debug_Conc_Log('Starting Process_SSXref_Intf_Rows for batch_id='||TO_CHAR(p_data_set_id));
5687: BEGIN
5680: l_org_id NUMBER;
5681:
5682: l_import_xref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
5683: l_inventory_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
5684: l_transaction_id MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_ID%TYPE;
5685: BEGIN
5686: Debug_Conc_Log('Starting Process_SSXref_Intf_Rows for batch_id='||TO_CHAR(p_data_set_id));
5687: BEGIN
5688: SELECT b.SOURCE_SYSTEM_ID, NVL(opt.IMPORT_XREF_ONLY, 'N')
5726:
5727: -- Bug: 5262421
5728: -- Updating item, user-defined attrs, gdsn attrs, AMLs to successful process status
5729: -- so that they appear correctly in the Imported tab
5730: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
5731: SET PROCESS_FLAG = 7,
5732: TRANSACTION_TYPE = 'UPDATE',
5733: PROGRAM_APPLICATION_ID = l_prog_appid,
5734: PROGRAM_ID = l_prog_id,
5790: l_raise_bus_event := TRUE;
5791: -- Bug: 5262421
5792: -- Updating item, user-defined attrs, gdsn attrs, AMLs to successful process status
5793: -- so that they appear correctly in the Imported tab
5794: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
5795: SET PROCESS_FLAG = 7,
5796: TRANSACTION_TYPE = 'UPDATE',
5797: PROGRAM_APPLICATION_ID = l_prog_appid,
5798: PROGRAM_ID = l_prog_id,
5891: ,l_request_id
5892: ,l_transaction_id
5893: ,l_msg_text
5894: ,'SOURCE_SYSTEM_REFERENCE'
5895: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5896: ,'INV_IOI_ERR'
5897: ,l_err_text);
5898: END IF; --IF l_return_status <> 'S' THEN
5899: END IF; --IF l_raise_event THEN
5961: BA.BATCH_ID = p_data_set_id;
5962:
5963: -- Set the confirm status and item number based on the parameters
5964: -- Rows that already have a "fake" confirm_status are kept "fake"
5965: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
5966: SET PROCESS_FLAG = 0,
5967: ORGANIZATION_ID = l_org_id,
5968: ORGANIZATION_CODE = l_org_code,
5969: INVENTORY_ITEM_ID = p_inventory_item_id,
6024: SOURCE_SYSTEM_ID = p_source_system_id AND
6025: SOURCE_SYSTEM_REFERENCE = p_source_system_reference AND
6026: (ORGANIZATION_ID = p_organization_id OR ORGANIZATION_CODE = p_organization_code) AND
6027: REVISION IS NOT NULL AND
6028: Upper(REVISION) NOT IN ( SELECT B.REVISION FROM MTL_ITEM_REVISIONS_B B, MTL_SYSTEM_ITEMS_INTERFACE MSII
6029: WHERE MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
6030: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
6031: AND (MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
6032: OR MSII.ORGANIZATION_CODE = MIRI.ORGANIZATION_CODE)
6059: SELECT MSII.SOURCE_SYSTEM_ID,
6060: MSII.SOURCE_SYSTEM_REFERENCE,
6061: MSII.SET_PROCESS_ID,
6062: MSII.BUNDLE_ID
6063: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
6064: WHERE MSII.PROCESS_FLAG = 0
6065: AND MSII.SET_PROCESS_ID = p_data_set_id
6066: AND MSII.BUNDLE_ID = p_bundle_id;
6067:
6107: p_source_system_reference IN VARCHAR2,
6108: p_bundle_id IN NUMBER
6109: )
6110: RETURN VARCHAR2 IS
6111: l_pdh_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
6112: l_pdh_description MTL_SYSTEM_ITEMS_INTERFACE.DESCRIPTION%TYPE;
6113: l_pdh_item_catalog_group_id MTL_SYSTEM_ITEMS_INTERFACE.ITEM_CATALOG_GROUP_ID%TYPE;
6114: l_confirm_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
6115: l_item_num_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_NUM_GEN_METHOD%TYPE;
6108: p_bundle_id IN NUMBER
6109: )
6110: RETURN VARCHAR2 IS
6111: l_pdh_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
6112: l_pdh_description MTL_SYSTEM_ITEMS_INTERFACE.DESCRIPTION%TYPE;
6113: l_pdh_item_catalog_group_id MTL_SYSTEM_ITEMS_INTERFACE.ITEM_CATALOG_GROUP_ID%TYPE;
6114: l_confirm_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
6115: l_item_num_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_NUM_GEN_METHOD%TYPE;
6116: l_item_desc_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_DESC_GEN_METHOD%TYPE;
6109: )
6110: RETURN VARCHAR2 IS
6111: l_pdh_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
6112: l_pdh_description MTL_SYSTEM_ITEMS_INTERFACE.DESCRIPTION%TYPE;
6113: l_pdh_item_catalog_group_id MTL_SYSTEM_ITEMS_INTERFACE.ITEM_CATALOG_GROUP_ID%TYPE;
6114: l_confirm_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
6115: l_item_num_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_NUM_GEN_METHOD%TYPE;
6116: l_item_desc_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_DESC_GEN_METHOD%TYPE;
6117: l_organization_id MTL_PARAMETERS.ORGANIZATION_ID%TYPE;
6110: RETURN VARCHAR2 IS
6111: l_pdh_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
6112: l_pdh_description MTL_SYSTEM_ITEMS_INTERFACE.DESCRIPTION%TYPE;
6113: l_pdh_item_catalog_group_id MTL_SYSTEM_ITEMS_INTERFACE.ITEM_CATALOG_GROUP_ID%TYPE;
6114: l_confirm_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
6115: l_item_num_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_NUM_GEN_METHOD%TYPE;
6116: l_item_desc_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_DESC_GEN_METHOD%TYPE;
6117: l_organization_id MTL_PARAMETERS.ORGANIZATION_ID%TYPE;
6118: l_organization_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
6116: l_item_desc_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_DESC_GEN_METHOD%TYPE;
6117: l_organization_id MTL_PARAMETERS.ORGANIZATION_ID%TYPE;
6118: l_organization_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
6119: l_batch_rev_policy EGO_IMPORT_OPTION_SETS.REVISION_IMPORT_POLICY%TYPE;
6120: l_instance_rev_policy MTL_SYSTEM_ITEMS_INTERFACE.REVISION_IMPORT_POLICY%TYPE;
6121: l_style_item_flag MTL_SYSTEM_ITEMS_INTERFACE.STYLE_ITEM_FLAG%TYPE;
6122: BEGIN
6123: SELECT
6124: MP.ORGANIZATION_ID,
6117: l_organization_id MTL_PARAMETERS.ORGANIZATION_ID%TYPE;
6118: l_organization_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
6119: l_batch_rev_policy EGO_IMPORT_OPTION_SETS.REVISION_IMPORT_POLICY%TYPE;
6120: l_instance_rev_policy MTL_SYSTEM_ITEMS_INTERFACE.REVISION_IMPORT_POLICY%TYPE;
6121: l_style_item_flag MTL_SYSTEM_ITEMS_INTERFACE.STYLE_ITEM_FLAG%TYPE;
6122: BEGIN
6123: SELECT
6124: MP.ORGANIZATION_ID,
6125: MP.ORGANIZATION_CODE,
6150: CONFIRM_STATUS,
6151: NVL(REVISION_IMPORT_POLICY, l_batch_rev_policy),
6152: MSII.STYLE_ITEM_FLAG
6153: INTO l_pdh_item_number, l_pdh_description, l_pdh_item_catalog_group_id, l_confirm_status, l_instance_rev_policy, l_style_item_flag
6154: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_IMPORT_BATCHES_B BATCH
6155: WHERE MSII.SET_PROCESS_ID = p_data_set_id AND
6156: MSII.SOURCE_SYSTEM_ID = p_source_system_id AND
6157: MSII.SOURCE_SYSTEM_REFERENCE = p_source_system_reference AND
6158: MSII.ORGANIZATION_ID = BATCH.ORGANIZATION_ID AND
6272: )
6273: IS
6274: CURSOR c_ss_items_table IS
6275: SELECT rowid rid
6276: FROM mtl_system_items_interface
6277: WHERE set_process_id = p_set_id
6278: AND process_flag = 0
6279: AND organization_id /*bug 6402904 parse for child items also*/
6280: IN (SELECT organization_id
6287: FOR UPDATE OF item_number;
6288:
6289: CURSOR c_pdh_items_table IS
6290: SELECT rowid rid
6291: FROM mtl_system_items_interface
6292: WHERE set_process_id = p_set_id
6293: AND process_flag = 1
6294: AND item_number IS null
6295: FOR UPDATE OF item_number;
6294: AND item_number IS null
6295: FOR UPDATE OF item_number;
6296:
6297: l_err_text VARCHAR2(240) := NULL;
6298: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE := NULL;
6299: l_item_id MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE := NULL;
6300: l_ret_code INTEGER;
6301: BEGIN
6302: IF p_is_pdh_batch = FND_API.G_TRUE THEN
6332: , inventory_item_id
6333: , item_number
6334: , transaction_id
6335: , transaction_type
6336: FROM mtl_system_items_interface
6337: WHERE set_process_id = p_set_id
6338: AND organization_id = p_org_id
6339: AND process_flag = 1
6340: AND UPPER(transaction_type) = 'SYNC'
6382: l_item_exist := NVL(l_item_exist,0);
6383: END IF;
6384:
6385: IF l_item_exist = 1 THEN
6386: UPDATE mtl_system_items_interface
6387: SET transaction_type = 'UPDATE'
6388: WHERE rowid = item_record.rowid;
6389: ELSE
6390: UPDATE mtl_system_items_interface
6386: UPDATE mtl_system_items_interface
6387: SET transaction_type = 'UPDATE'
6388: WHERE rowid = item_record.rowid;
6389: ELSE
6390: UPDATE mtl_system_items_interface
6391: SET transaction_type = 'CREATE', inventory_item_id = NULL
6392: WHERE rowid = item_record.rowid;
6393: END IF;
6394: END LOOP;
6396: END UPDATE_ITEM_SYNC_RECORDS_PDH;
6397:
6398: /*
6399: * This procedure resolves source system item cross references
6400: * immediately after MTL_SYSTEM_ITEMS_INTERFACE is populated and
6401: * tries to resolve discrepencies between user entered CONFIRM_STATUS
6402: * (if any), TRANSACTION_TYPE (if any) and other data.
6403: * This procedure needs to be safe for re-execution if data is loaded into an
6404: * active batch repeatedly.
6463:
6464: -- populate organization_id if it's null
6465: -- if organization_code is not null, derive from it otherwise use
6466: -- organization id for batch
6467: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
6468: SET ORGANIZATION_ID =
6469: CASE
6470: WHEN ORGANIZATION_CODE IS NOT NULL
6471: THEN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = MSII.ORGANIZATION_CODE)
6648: AND eiai.SUPPLIER_SITE_NAME IS NOT NULL;
6649:
6650: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Done Resolving PK Values in Intersection Interface table' );
6651:
6652: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
6653: SET STYLE_ITEM_ID = (SELECT MSIK.INVENTORY_ITEM_ID
6654: FROM MTL_SYSTEM_ITEMS_KFV MSIK
6655: WHERE MSIK.CONCATENATED_SEGMENTS = MSII.STYLE_ITEM_NUMBER
6656: AND MSIK.ORGANIZATION_ID = MSII.ORGANIZATION_ID
6667: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, update item_number if inventory_item_id is populated' );
6668: -- if user does know the inventory_item_id, use it to overwrite
6669: -- item_number
6670: -- so in the imported tab, we can join to the child entities by item_number alone
6671: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
6672: SET item_number = (SELECT CONCATENATED_SEGMENTS
6673: FROM MTL_SYSTEM_ITEMS_KFV
6674: WHERE INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
6675: AND ORGANIZATION_ID = MSII.ORGANIZATION_ID)
6748: -- insert fake records ...
6749: l_insert_date := SYSDATE;
6750: -- ... 1. for attributes (no child orgs)
6751: INSERT INTO
6752: MTL_SYSTEM_ITEMS_INTERFACE MSII
6753: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id
6754: , confirm_status, inventory_item_id, item_number
6755: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
6756: , ITEM_CATALOG_GROUP_ID )
6773: -- AND EIUAI.organization_id = MP.organization_id
6774: -- AND MP.master_organization_id = l_org_id
6775: AND NOT EXISTS
6776: (
6777: select null from mtl_system_items_interface
6778: where set_process_id = EIUAI.data_set_id
6779: and item_number = EIUAI.item_number
6780: and process_flag = EIUAI.process_status
6781: and organization_id = l_org_id
6784: -- using GROUP BY rather than distinct for performance reasons (emulating merge code)
6785: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, parent rows inserted for UDAs: ' || SQL%ROWCOUNT );
6786: -- ... 2. for AMLs (no child orgs)
6787: INSERT INTO
6788: MTL_SYSTEM_ITEMS_INTERFACE MSII
6789: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id
6790: , confirm_status, inventory_item_id, item_number
6791: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
6792: SELECT G_TRANS_TYPE_SYNC, 1, p_data_set_id, l_org_id, l_ss_id
6807: -- AND EAI.organization_id = MP.organization_id
6808: -- AND MP.master_organization_id = l_org_id
6809: AND NOT EXISTS
6810: (
6811: select null from mtl_system_items_interface
6812: where set_process_id = EAI.data_set_id
6813: and item_number = EAI.item_number
6814: and process_flag = EAI.process_flag
6815: and organization_id = l_org_id
6819: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, parent rows inserted for AMLs: ' || SQL%ROWCOUNT );
6820:
6821: --R12C: Inseting Fake Row into MSII for orphan row in EGO_ITEM_ASSOCIATIONS_INTF.
6822: INSERT INTO
6823: MTL_SYSTEM_ITEMS_INTERFACE MSII
6824: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id
6825: , confirm_status, inventory_item_id, item_number
6826: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
6827: SELECT G_TRANS_TYPE_SYNC, 1, p_data_set_id, EIAI.organization_id, l_ss_id
6839: AND process_flag = 1
6840: AND EIAI.organization_id = l_org_id
6841: AND NOT EXISTS
6842: (
6843: select null from mtl_system_items_interface
6844: where set_process_id = EIAI.batch_id
6845: and item_number = EIAI.item_number
6846: and process_flag = EIAI.process_flag
6847: and organization_id = l_org_id
6850: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, parent rows inserted for ASSOCIATIONS: ' || SQL%ROWCOUNT );
6851:
6852: --R12C: Insertion of Fake row into MSII for a orphan row in MTL_ITEM_CATEGORIES_INTERFACE
6853: INSERT INTO
6854: MTL_SYSTEM_ITEMS_INTERFACE MSII
6855: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id
6856: , confirm_status, inventory_item_id, item_number
6857: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
6858: SELECT G_TRANS_TYPE_SYNC, 1, p_data_set_id, MICI.organization_id, l_ss_id
6870: AND process_flag = 1
6871: AND MICI.organization_id = l_org_id
6872: AND NOT EXISTS
6873: (
6874: select null from mtl_system_items_interface
6875: where set_process_id = MICI.set_process_id
6876: and item_number = MICI.item_number
6877: and process_flag = MICI.process_flag
6878: and organization_id = l_org_id
6894:
6895: /* Bug 5283663: CREATE rows can have the FAKE confirm status,
6896: since those rows are moved to process flag 3 in resolve_child_entities;
6897: before IOI has a chance to process them
6898: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
6899: SET CONFIRM_STATUS = NULL
6900: WHERE
6901: set_process_id = p_data_set_id
6902: AND process_flag = 1
6922: -- PDH Item Primary Key Possibilities: inventory_item_id, item_number, segment1, segment2, ... segment20
6923: -- User Directive: transaction_type
6924:
6925: -- Normalize the PROCESS_FLAG if it is null
6926: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
6927: SET PROCESS_FLAG = 0
6928: WHERE SET_PROCESS_ID = p_data_set_id AND
6929: PROCESS_FLAG IS NULL;
6930:
6964: -- insert fake records ...
6965: l_insert_date := SYSDATE;
6966: -- ... 1. for attributes (no child orgs)
6967: INSERT INTO
6968: MTL_SYSTEM_ITEMS_INTERFACE MSII
6969: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference, bundle_id
6970: , confirm_status, inventory_item_id, item_number
6971: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
6972: , ITEM_CATALOG_GROUP_ID )
6992: AND source_system_id = l_ss_id
6993: AND source_system_reference IS NOT NULL
6994: AND NOT EXISTS
6995: (
6996: select null from mtl_system_items_interface
6997: where set_process_id = EIUAI.data_set_id
6998: and source_system_id = EIUAI.source_system_id
6999: and source_system_reference = EIUAI.source_system_reference
7000: and process_flag = EIUAI.process_status
7004: -- using GROUP BY rather than distinct for performance reasons (no load on merge code)
7005: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, parent rows inserted for UDAs: ' || SQL%ROWCOUNT );
7006: -- ... 2. for AMLs (no child orgs)
7007: INSERT INTO
7008: MTL_SYSTEM_ITEMS_INTERFACE MSII
7009: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference
7010: , source_system_reference_desc, confirm_status, inventory_item_id, item_number
7011: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
7012: SELECT G_TRANS_TYPE_SYNC, 0, p_data_set_id, l_org_id
7031: AND source_system_id = l_ss_id
7032: AND source_system_reference IS NOT NULL
7033: AND NOT EXISTS
7034: (
7035: select null from mtl_system_items_interface
7036: where set_process_id = EAI.data_set_id
7037: and source_system_id = EAI.source_system_id
7038: and source_system_reference = EAI.source_system_reference
7039: and process_flag = EAI.process_flag
7044: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, parent rows inserted for AMLs: ' || SQL%ROWCOUNT );
7045:
7046: --R12C: Inserting Fake Row (Parent Row in MSII) for Orphan row in EGO_ITEM_ASSOCIATIONS_INTF
7047: INSERT INTO
7048: MTL_SYSTEM_ITEMS_INTERFACE MSII
7049: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference, bundle_id
7050: , confirm_status, inventory_item_id, item_number
7051: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
7052: SELECT G_TRANS_TYPE_SYNC, 0, p_data_set_id, l_org_id
7067: AND source_system_id = l_ss_id
7068: AND source_system_reference IS NOT NULL
7069: AND NOT EXISTS
7070: (
7071: select null from mtl_system_items_interface
7072: where set_process_id = EIAI.batch_id
7073: and source_system_id = EIAI.source_system_id
7074: and source_system_reference = EIAI.source_system_reference
7075: and process_flag = EIAI.process_flag
7080:
7081:
7082: --R12C: Inserting Fake Row (Parent Row in MSII) for Orphan row in EGO_ITEM_ASSOCIATIONS_INTF
7083: INSERT INTO
7084: MTL_SYSTEM_ITEMS_INTERFACE MSII
7085: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference, bundle_id
7086: , confirm_status, inventory_item_id, item_number
7087: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
7088: SELECT G_TRANS_TYPE_SYNC, 0, p_data_set_id, l_org_id
7103: AND source_system_id = l_ss_id
7104: AND source_system_reference IS NOT NULL
7105: AND NOT EXISTS
7106: (
7107: select null from mtl_system_items_interface
7108: where set_process_id = MICI.set_process_id
7109: and source_system_id = MICI.source_system_id
7110: and source_system_reference = MICI.source_system_reference
7111: and process_flag = MICI.process_flag
7115: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, parent rows inserted for CAT Assignments: ' || SQL%ROWCOUNT );
7116: -- R12C: End
7117: -- check for records that need to be marked excluded
7118: UPDATE
7119: MTL_SYSTEM_ITEMS_INTERFACE MSII
7120: SET CONFIRM_STATUS = CASE
7121: WHEN CONFIRM_STATUS IS NULL THEN G_EXCLUDED
7122: ELSE G_FAKE_EXCLUDED
7123: END
7170: -- check for records that need to be marked excluded among the merged records
7171: -- (for freshly merged records, confirm_status is NULL)
7172: -- fix for bug 5329665
7173: UPDATE
7174: MTL_SYSTEM_ITEMS_INTERFACE MSII
7175: SET CONFIRM_STATUS = G_EXCLUDED
7176: WHERE
7177: SET_PROCESS_ID = p_data_set_id
7178: AND CONFIRM_STATUS IS NULL
7234: l_security_predicate := NVL(l_security_predicate, '1=1');
7235:
7236: l_sql := q'#
7237: UPDATE
7238: MTL_SYSTEM_ITEMS_INTERFACE MSII
7239: SET TRANSACTION_TYPE = UPPER( TRANSACTION_TYPE )
7240: , INVENTORY_ITEM_ID =
7241: (SELECT INVENTORY_ITEM_ID
7242: FROM MTL_CROSS_REFERENCES_B XREF
7267: -- the revision import policy should not apply if the user has given us a
7268: -- rev code or id in either EGO_ITM_USR_ATTR_INTRFC or MTL_ITEM_REVISIONS_INTERFACE: We set it to 'S' meaning specific.
7269: -- however, if the rev import policy is set to Specific already, we need to verify that
7270: -- the user has given us a rev code or id in either EGO_ITM_USR_ATTR_INTRFC or MTL_ITEM_REVISIONS_INTERFACE.
7271: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
7272: SET REVISION_IMPORT_POLICY =
7273: CASE
7274: WHEN EXISTS -- check the revision interface table
7275: (
7319: -- US (unconfirmed single match), UM (unconfirmed multiple match), UN (unconfirmed no match), EX (excluded), null.
7320: -- See spec (EGOVIMPS.pls) constants for list of confirm status values.
7321: -- Bug: 5458886 - If batch is cross references only batch, then New Item should not be allowed
7322: UPDATE -- for non-fake rows
7323: MTL_SYSTEM_ITEMS_INTERFACE MSII
7324: SET
7325: CONFIRM_STATUS =
7326: CASE
7327: WHEN UPPER( TRANSACTION_TYPE ) = G_TRANS_TYPE_CREATE AND l_import_xref_only = 'N' THEN G_CONF_NEW
7335: ORGANIZATION_ID = l_org_id AND
7336: CONFIRM_STATUS IS NULL;
7337:
7338: UPDATE -- for fake rows
7339: MTL_SYSTEM_ITEMS_INTERFACE MSII
7340: SET
7341: CONFIRM_STATUS =
7342: CASE
7343: WHEN UPPER( TRANSACTION_TYPE ) = G_TRANS_TYPE_CREATE AND l_import_xref_only = 'N' THEN G_CONF_NEW
7352: CONFIRM_STATUS = G_FAKE_CONF_STATUS_FLAG;
7353:
7354: -- update inventory_item_id to make sure their existence makes sense
7355: UPDATE
7356: MTL_SYSTEM_ITEMS_INTERFACE MSII
7357: SET
7358: INVENTORY_ITEM_ID =
7359: CASE
7360: WHEN CONFIRM_STATUS IN ( G_CONF_XREF, G_CONF_MATCH, G_UNCONF_SIGL_MATCH, G_UNCONF_MULT_MATCH
7371: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, done resolving confirm status' );
7372: -- for all records where item_catalog_group_name is present and item_catalog_group_id is not present
7373: -- update the item_catalog_group_id
7374: UPDATE
7375: MTL_SYSTEM_ITEMS_INTERFACE MSII
7376: SET ITEM_CATALOG_GROUP_ID = (SELECT MICG.ITEM_CATALOG_GROUP_ID
7377: FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG
7378: WHERE MICG.CONCATENATED_SEGMENTS = MSII.ITEM_CATALOG_GROUP_NAME)
7379: WHERE MSII.SET_PROCESS_ID = p_data_set_id
7881: INVENTORY_ITEM_ID,
7882: ORGANIZATION_ID,
7883: TRANSACTION_TYPE,
7884: TRANSACTION_ID
7885: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
7886: WHERE SET_PROCESS_ID = p_data_set_id
7887: AND PROCESS_FLAG = 33390;
7888:
7889:
7893: MSII.ORGANIZATION_CODE,
7894: MSII.ORGANIZATION_ID,
7895: MSII.TRANSACTION_ID
7896: FROM MTL_SYSTEM_ITEMS_B_KFV KFV,
7897: MTL_SYSTEM_ITEMS_INTERFACE MSII,
7898: MTL_CROSS_REFERENCES MCR
7899: WHERE MSII.SET_PROCESS_ID = p_data_set_id
7900: AND MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
7901: AND MSII.SOURCE_SYSTEM_ID = MCR.SOURCE_SYSTEM_ID
7962: -- no need to resolve child entities for a Non-PDH batch, if Import Only Cross References is true
7963: IF l_ss_id = get_pdh_source_system_id() THEN
7964: -- move fake rows to have the success process flag value
7965: -- so that they are not processed by IOI
7966: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
7967: SET
7968: REQUEST_ID = l_request_id
7969: , PROGRAM_APPLICATION_ID = l_prog_appid
7970: , PROGRAM_ID = l_prog_id
7987: p_commit => FND_API.G_FALSE );
7988: Debug_Conc_Log('Resolve_Child_Entities - Clean_Up_UDA_Row_Idents Done.');
7989:
7990: Debug_Conc_Log('Resolve_Child_Entities - Resolving Style_Item_Flag');
7991: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
7992: SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID, INVENTORY_ITEM_ID) = (SELECT
7993: DECODE(MAX(NVL(msik.STYLE_ITEM_FLAG, '$NULL$')),
7994: '$NULL$', msii.STYLE_ITEM_FLAG,
7995: NULL, msii.STYLE_ITEM_FLAG,
8044:
8045: -- CC/CM:
8046: -- old link and new link are the same:
8047: l_sql := q'#
8048: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8049: SET PROCESS_FLAG = 33390
8050: WHERE SET_PROCESS_ID = :p_data_set_id
8051: AND PROCESS_FLAG = 0
8052: AND ORGANIZATION_ID = :org_id
8069: source system reference desc is being updated');
8070: -- old link and new link are not the same:
8071: -- check edit privilege on old item link (if any)
8072: l_sql := q'#
8073: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8074: SET PROCESS_FLAG = 33391
8075: WHERE SET_PROCESS_ID = :p_data_set_id
8076: AND PROCESS_FLAG = 0
8077: AND ORGANIZATION_ID = :org_id
8090: Debug_Conc_Log( 'Resolve_Child_Entities - After checking security on old item link (if any) where old link and new link are not the same' );
8091: -- old link and new link are not the same:
8092: -- check edit privilege on new item link
8093: l_sql := q'#
8094: UPDATE MTL_SYSTEM_ITEMS_INTERFACE APPLYSEC
8095: SET PROCESS_FLAG = 33390
8096: WHERE SET_PROCESS_ID = :p_data_set_id
8097: AND PROCESS_FLAG = 0
8098: AND ORGANIZATION_ID = :org_id
8126: ,l_request_id
8127: ,i.TRANSACTION_ID
8128: ,l_msg_text
8129: ,'SOURCE_SYSTEM_REFERENCE'
8130: ,'MTL_SYSTEM_ITEMS_INTERFACE'
8131: ,'INV_IOI_ERR'
8132: ,l_err_text);
8133:
8134: END LOOP;
8148: ,l_request_id
8149: ,i.TRANSACTION_ID
8150: ,l_msg_text
8151: ,'SOURCE_SYSTEM_REFERENCE'
8152: ,'MTL_SYSTEM_ITEMS_INTERFACE'
8153: ,'INV_IOI_ERR'
8154: ,l_err_text);
8155: END LOOP;
8156:
8155: END LOOP;
8156:
8157: Debug_Conc_Log( 'Resolve_Child_Entities - After logging errors for records without privilege' );
8158:
8159: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
8160: SET PROCESS_FLAG = 3
8161: WHERE SET_PROCESS_ID = p_data_set_id
8162: AND PROCESS_FLAG IN (33390, 33391)
8163: AND CONFIRM_STATUS IN ( 'CM', 'CC', 'CN', G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE )
8189: AND MIR.REVISION = MIRI.REVISION) )
8190: THEN 'UPDATE'
8191: ELSE 'CREATE'
8192: END)
8193: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8194: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8195: AND MSII.PROCESS_FLAG = 0
8196: AND MSII.CONFIRM_STATUS IN ('CN', 'CM', 'CC', G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE)
8197: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8201: )
8202: WHERE MIRI.PROCESS_FLAG = 0
8203: AND MIRI.SET_PROCESS_ID = p_data_set_id
8204: AND EXISTS (SELECT NULL
8205: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8206: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8207: AND MSII.PROCESS_FLAG = 0
8208: AND MSII.CONFIRM_STATUS IN ( 'CN', 'CM', 'CC', G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE )
8209: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8213: Debug_Conc_Log('Resolve_Child_Entities - After resolving transaction_type for revisions' );
8214:
8215: -- update the confirm status to ready for all READY CN records
8216: UPDATE
8217: MTL_SYSTEM_ITEMS_INTERFACE MSII
8218: SET CONFIRM_STATUS = (
8219: CASE
8220: WHEN (MSII.DESCRIPTION IS NULL
8221: AND NOT EXISTS
8277: -- We are not honoring user entered item number values from R12C
8278: -- so the overwriting of item number must happen only from IOI
8279: /*
8280: UPDATE
8281: MTL_SYSTEM_ITEMS_INTERFACE MSII
8282: SET ITEM_NUMBER = EGO_IMPORT_PVT.GET_NEXT_ITEM_NUMBER(MSII.ITEM_CATALOG_GROUP_ID)
8283: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
8284: AND MSII.ITEM_NUMBER IS NULL
8285: AND MSII.SET_PROCESS_ID = p_data_set_id
8297: Debug_Conc_Log('Resolve_Child_Entities - After updating Sequence Generated Item Numbers' );
8298: */
8299: -- update all records with confirm_status='CC', 'CM' to ready, where conditions are satisfied
8300: UPDATE
8301: MTL_SYSTEM_ITEMS_INTERFACE MSII
8302: SET CONFIRM_STATUS = (
8303: CASE
8304: WHEN
8305: (NVL(MSII.REVISION_IMPORT_POLICY, l_import_policy) = 'N' AND
8322: AND MSII.ORGANIZATION_ID = l_org_id
8323: AND MSII.CONFIRM_STATUS IN ('CC', 'CM');
8324:
8325: UPDATE
8326: MTL_SYSTEM_ITEMS_INTERFACE MSII
8327: SET CONFIRM_STATUS = (
8328: CASE
8329: WHEN ( NVL(MSII.REVISION_IMPORT_POLICY, l_import_policy) = 'N' AND
8330: 1 <> (SELECT COUNT(*)
8353: Debug_Conc_Log('Resolve_Child_Entities - Need to check privilege' );
8354: -- For the confirmed new case, if there exists an active cross reference which you cannot break
8355: -- update process_flag to 33391
8356: l_sql := q'#
8357: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8358: SET PROCESS_FLAG = 33391
8359: WHERE SET_PROCESS_ID = :p_data_set_id
8360: AND PROCESS_FLAG = 0
8361: AND ORGANIZATION_ID = :org_id
8372: Debug_Conc_Log('Resolve_Child_Entities - After checking for CNR' );
8373: -- CC/CM:
8374: -- old link and new link are the same:
8375: l_sql := q'#
8376: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8377: SET PROCESS_FLAG = 33390
8378: WHERE SET_PROCESS_ID = :p_data_set_id
8379: AND PROCESS_FLAG = 0
8380: AND ORGANIZATION_ID = :org_id
8397: source system reference desc is being updated' );
8398: -- old link and new link are not the same:
8399: -- check edit privilege on old item link (if any)
8400: l_sql := q'#
8401: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8402: SET PROCESS_FLAG = 33391
8403: WHERE SET_PROCESS_ID = :p_data_set_id
8404: AND PROCESS_FLAG = 0
8405: AND ORGANIZATION_ID = :org_id
8419:
8420: -- old link and new link are not the same:
8421: -- check edit privilege on new item link
8422: l_sql := q'#
8423: UPDATE MTL_SYSTEM_ITEMS_INTERFACE APPLYSEC
8424: SET PROCESS_FLAG = 33390
8425: WHERE SET_PROCESS_ID = :p_data_set_id
8426: AND PROCESS_FLAG = 0
8427: AND ORGANIZATION_ID = :org_id
8459: ,l_request_id
8460: ,i.TRANSACTION_ID
8461: ,l_msg_text
8462: ,'SOURCE_SYSTEM_REFERENCE'
8463: ,'MTL_SYSTEM_ITEMS_INTERFACE'
8464: ,'INV_IOI_ERR'
8465: ,l_err_text);
8466: END LOOP;
8467:
8480: ,l_request_id
8481: ,i.TRANSACTION_ID
8482: ,l_msg_text
8483: ,'SOURCE_SYSTEM_REFERENCE'
8484: ,'MTL_SYSTEM_ITEMS_INTERFACE'
8485: ,'INV_IOI_ERR'
8486: ,l_err_text);
8487: END LOOP;
8488:
8489: Debug_Conc_Log('Resolve_Child_Entities - After logging errors for no privilege' );
8490: END IF; --IF l_security_predicate IS NOT NULL THEN
8491:
8492: -- ELETUCHY XXX: seems wasteful to overwrite transaction type to sync rather than update, no?
8493: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
8494: SET PROCESS_FLAG = 1
8495: , TRANSACTION_TYPE = CASE
8496: WHEN CONFIRM_STATUS = 'CNR' THEN G_TRANS_TYPE_CREATE
8497: ELSE G_TRANS_TYPE_UPDATE
8510: AND CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR' )
8511: AND ORGANIZATION_ID = l_org_id;
8512:
8513: -- ELETUCHY: these fake rows will never be processed by IOI, so putting trans_type to UPDATE is reasonable
8514: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
8515: SET PROCESS_FLAG = 1,
8516: TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE,
8517: CONFIRM_STATUS = G_FAKE_CONF_STATUS_FLAG
8518: WHERE SET_PROCESS_ID = p_data_set_id
8523: Debug_Conc_Log('Resolve_Child_Entities - Done with master item rows' );
8524:
8525: -- updating item_number, item_id from MSII table
8526: -- for child items
8527: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8528: SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID, ITEM_NUMBER, ITEM_CATALOG_GROUP_ID, ITEM_CATALOG_GROUP_NAME, STYLE_ITEM_FLAG, STYLE_ITEM_ID ) =
8529: (SELECT
8530: 1,
8531: (CASE
8538: MSII2.ITEM_CATALOG_GROUP_ID,
8539: MSII2.ITEM_CATALOG_GROUP_NAME,
8540: NVL(MSII.STYLE_ITEM_FLAG, MSII2.STYLE_ITEM_FLAG),
8541: NVL(MSII.STYLE_ITEM_ID, MSII2.STYLE_ITEM_ID)
8542: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
8543: WHERE MSII2.SET_PROCESS_ID = p_data_set_id
8544: AND MSII2.PROCESS_FLAG = 1
8545: AND MSII2.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
8546: AND MSII2.SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
8556: AND mp.MASTER_ORGANIZATION_ID = l_org_id
8557: AND mp.MASTER_ORGANIZATION_ID <> mp.ORGANIZATION_ID
8558: )
8559: AND EXISTS (SELECT NULL
8560: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
8561: WHERE MSII2.SET_PROCESS_ID = p_data_set_id
8562: AND MSII2.PROCESS_FLAG = 1
8563: AND MSII2.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8564: AND MSII2.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
8586: ELSE 'CREATE'
8587: END),
8588: MSII.INVENTORY_ITEM_ID,
8589: MSII.ITEM_NUMBER
8590: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8591: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8592: AND MSII.PROCESS_FLAG = 1
8593: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8594: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8598: )
8599: WHERE MIRI.PROCESS_FLAG = 0
8600: AND MIRI.SET_PROCESS_ID = p_data_set_id
8601: AND EXISTS (SELECT NULL
8602: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8603: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8604: AND MSII.PROCESS_FLAG = 1
8605: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8606: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8619: ELSE MICI.TRANSACTION_TYPE
8620: END),
8621: MSII.INVENTORY_ITEM_ID,
8622: MSII.ITEM_NUMBER
8623: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8624: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8625: AND MSII.PROCESS_FLAG = 1
8626: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8627: AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
8631: )
8632: WHERE MICI.PROCESS_FLAG = 0
8633: AND MICI.SET_PROCESS_ID = p_data_set_id
8634: AND EXISTS (SELECT NULL
8635: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8636: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8637: AND MSII.PROCESS_FLAG = 1
8638: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8639: AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
8653: END),
8654: MSII.INVENTORY_ITEM_ID,
8655: MSII.ITEM_NUMBER,
8656: NVL(ATTRS.ITEM_CATALOG_GROUP_ID, MSII.ITEM_CATALOG_GROUP_ID)
8657: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8658: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8659: AND MSII.PROCESS_FLAG = 1
8660: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8661: AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
8665: )
8666: WHERE ATTRS.PROCESS_STATUS = 0
8667: AND ATTRS.DATA_SET_ID = p_data_set_id
8668: AND EXISTS (SELECT NULL
8669: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8670: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8671: AND MSII.PROCESS_FLAG = 1
8672: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8673: AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
8688: THEN '2'
8689: WHEN MSII.CONFIRM_STATUS = 'CNR'
8690: THEN '3'
8691: END
8692: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8693: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8694: AND MSII.PROCESS_FLAG = 1
8695: AND MSII.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8696: AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
8723: WHERE ATTRS.PROCESS_STATUS = 1
8724: AND ATTRS.DATA_SET_ID = p_data_set_id
8725: AND ATTRS.REVISION IS NULL
8726: AND EXISTS (SELECT NULL
8727: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8728: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8729: AND MSII.PROCESS_FLAG = 1
8730: AND MSII.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8731: AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
8745: SET (REVISION, TRANSACTION_TYPE) =
8746: (SELECT
8747: (CASE
8748: WHEN (EXISTS (SELECT NULL
8749: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8750: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8751: AND MSII.PROCESS_FLAG = 1
8752: AND MSII.CONFIRM_STATUS IN ('CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8753: AND (MSII.REVISION_IMPORT_POLICY = 'L' OR (MSII.REVISION_IMPORT_POLICY IS NULL AND l_import_policy = 'L'))
8759: FROM MTL_ITEM_REVISIONS_B
8760: WHERE INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID
8761: AND ORGANIZATION_ID = MIRI.ORGANIZATION_ID)
8762: WHEN (EXISTS (SELECT NULL
8763: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8764: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8765: AND MSII.PROCESS_FLAG = 1
8766: AND MSII.CONFIRM_STATUS = 'CNR'
8767: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8778: WHERE MIRI.PROCESS_FLAG = 1
8779: AND MIRI.SET_PROCESS_ID = p_data_set_id
8780: AND MIRI.REVISION IS NULL
8781: AND EXISTS (SELECT NULL
8782: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8783: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8784: AND MSII.PROCESS_FLAG = 1
8785: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8786: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8799: ELSE 'SYNC'
8800: END),
8801: MSII.INVENTORY_ITEM_ID,
8802: MSII.ITEM_NUMBER
8803: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8804: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8805: AND MSII.PROCESS_FLAG = 1
8806: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8807: AND MSII.SOURCE_SYSTEM_ID = EIPI.SOURCE_SYSTEM_ID
8811: )
8812: WHERE EIPI.PROCESS_STATUS = 0
8813: AND EIPI.DATA_SET_ID = p_data_set_id
8814: AND EXISTS (SELECT NULL
8815: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8816: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8817: AND MSII.PROCESS_FLAG = 1
8818: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8819: AND MSII.SOURCE_SYSTEM_ID = EIPI.SOURCE_SYSTEM_ID
8832: ELSE G_TRANS_TYPE_SYNC
8833: END),
8834: MSII.INVENTORY_ITEM_ID,
8835: MSII.ITEM_NUMBER
8836: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8837: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8838: AND MSII.PROCESS_FLAG = 1
8839: AND MSII.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8840: AND MSII.SOURCE_SYSTEM_ID = EAI.SOURCE_SYSTEM_ID
8844: )
8845: WHERE EAI.PROCESS_FLAG = 0
8846: AND EAI.DATA_SET_ID = p_data_set_id
8847: AND EXISTS (SELECT NULL
8848: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8849: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8850: AND MSII.PROCESS_FLAG = 1
8851: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8852: AND MSII.SOURCE_SYSTEM_ID = EAI.SOURCE_SYSTEM_ID
8866: ELSE EIAI.TRANSACTION_TYPE
8867: END),
8868: MSII.INVENTORY_ITEM_ID,
8869: MSII.ITEM_NUMBER
8870: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8871: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8872: AND MSII.PROCESS_FLAG = 1
8873: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8874: AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
8878: )
8879: WHERE EIAI.PROCESS_FLAG = 0
8880: AND EIAI.BATCH_ID = p_data_set_id
8881: AND EXISTS (SELECT NULL
8882: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8883: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8884: AND MSII.PROCESS_FLAG = 1
8885: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8886: AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
8908: END IF;
8909:
8910: Debug_Conc_Log('Resolve_Child_Entities - Security Predicate - '||l_security_predicate);
8911:
8912: -- updating all the child entities, which do not have parent in mtl_system_items_interface
8913: -- updating the items and org assignments
8914:
8915: -- Note that using q'# is a new feature of 10g. By using this syntax, we can put as many single quotes within
8916: -- the string. for example q'# This is Devendra's code #'
8914:
8915: -- Note that using q'# is a new feature of 10g. By using this syntax, we can put as many single quotes within
8916: -- the string. for example q'# This is Devendra's code #'
8917: l_sql := q'#
8918: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8919: SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID) =
8920: (SELECT
8921: 1,
8922: NVL(MSII.TRANSACTION_TYPE, 'SYNC'),
8937: AND MP.ORGANIZATION_ID <> MP.MASTER_ORGANIZATION_ID
8938: AND MP.ORGANIZATION_ID = MSII.ORGANIZATION_ID)
8939: -- CHILD MUST NOT HAVE A MASTER RECORD IN CURRENT BATCH
8940: AND NOT EXISTS (SELECT NULL
8941: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
8942: WHERE MSII2.PROCESS_FLAG in (0, 1, 33390,33391)
8943: AND MSII2.SET_PROCESS_ID = :p_data_set_id1
8944: AND MSII2.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
8945: AND MSII2.SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
8977: WHERE MIRI.SET_PROCESS_ID = :p_data_set_id
8978: AND MIRI.PROCESS_FLAG = 0
8979: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
8980: AND NOT EXISTS (SELECT NULL
8981: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8982: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
8983: AND MSII.SET_PROCESS_ID = :p_data_set_id1
8984: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8985: AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
9013: AND MIRI.PROCESS_FLAG = 1
9014: AND MIRI.REVISION IS NULL
9015: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9016: AND NOT EXISTS (SELECT NULL
9017: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9018: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
9019: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9020: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
9021: AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
9055: WHERE MICI.SET_PROCESS_ID = :p_data_set_id
9056: AND MICI.PROCESS_FLAG = 0
9057: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9058: AND NOT EXISTS (SELECT NULL
9059: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9060: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
9061: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9062: AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
9063: AND MSII.SOURCE_SYSTEM_REFERENCE = MICI.SOURCE_SYSTEM_REFERENCE
9096: WHERE EIPI.DATA_SET_ID = :p_data_set_id
9097: AND EIPI.PROCESS_STATUS = 0
9098: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9099: AND NOT EXISTS (SELECT NULL
9100: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9101: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
9102: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9103: AND MSII.SOURCE_SYSTEM_ID = EIPI.SOURCE_SYSTEM_ID
9104: AND MSII.SOURCE_SYSTEM_REFERENCE = EIPI.SOURCE_SYSTEM_REFERENCE
9137: WHERE EIUAI.DATA_SET_ID = :p_data_set_id
9138: AND EIUAI.PROCESS_STATUS = 0
9139: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9140: AND NOT EXISTS (SELECT NULL
9141: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9142: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390, 33391)
9143: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9144: AND MSII.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
9145: AND MSII.SOURCE_SYSTEM_REFERENCE = EIUAI.SOURCE_SYSTEM_REFERENCE
9178: WHERE EAI.DATA_SET_ID = :p_data_set_id
9179: AND EAI.PROCESS_FLAG = 0
9180: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9181: AND NOT EXISTS (SELECT NULL
9182: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9183: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390, 33391)
9184: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9185: AND MSII.SOURCE_SYSTEM_ID = EAI.SOURCE_SYSTEM_ID
9186: AND MSII.SOURCE_SYSTEM_REFERENCE = EAI.SOURCE_SYSTEM_REFERENCE
9219: WHERE EIAI.BATCH_ID = :p_data_set_id
9220: AND EIAI.PROCESS_FLAG = 0
9221: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9222: AND NOT EXISTS (SELECT NULL
9223: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9224: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
9225: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9226: AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
9227: AND MSII.SOURCE_SYSTEM_REFERENCE = EIAI.SOURCE_SYSTEM_REFERENCE
9240: EXECUTE IMMEDIATE l_sql USING p_data_set_id, p_data_set_id, l_org_id;
9241:
9242: Debug_Conc_Log('Resolve_Child_Entities - After updating intersections intf table for items that has XXref');
9243:
9244: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9245: SET REQUEST_ID = CASE PROCESS_FLAG
9246: WHEN 33390 THEN l_request_id
9247: WHEN 33391 THEN l_request_id
9248: ELSE REQUEST_ID
9264: * import because that bulk-loader looks for process flag 7 with the request id of the calling request.
9265: * Since both that code (Process_SSXref_Intf_Rows) and this chunk are executed within the auspices of
9266: * the same concurrent request, the cross-ref import will work correctly.
9267: */
9268: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9269: SET REQUEST_ID = l_request_id
9270: , PROCESS_FLAG = CASE PROCESS_FLAG
9271: WHEN 33390 THEN 3
9272: WHEN 33391 THEN 3
9294: Debug_Conc_Log('Resolve_Child_Entities - Not calling Merge_Batch_For_Import, because this batch is enabled for data pool');
9295: END IF;
9296:
9297: Debug_Conc_Log('Resolve_Child_Entities - Resolving Style_Item_Flag');
9298: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
9299: SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID) = (SELECT
9300: DECODE(MAX(NVL(msik.STYLE_ITEM_FLAG, '$NULL$')),
9301: '$NULL$', msii.STYLE_ITEM_FLAG,
9302: NULL, msii.STYLE_ITEM_FLAG,
9322: END IF;
9323:
9324: END Resolve_Child_Entities;
9325:
9326: PROCEDURE Stamp_Row_RequestId( p_request_id IN MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE
9327: , p_target_rowid IN UROWID
9328: )
9329: IS
9330: BEGIN
9331: IF p_request_id IS NULL THEN RETURN; END IF;
9332:
9333: SAVEPOINT do_stamp_row;
9334:
9335: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9336: SET REQUEST_ID = p_request_id
9337: WHERE MSII.ROWID = p_target_rowid
9338: AND PROCESS_FLAG = 7
9339: AND ( SOURCE_SYSTEM_ID IS NOT NULL
9349: RETURN;
9350: END;
9351:
9352:
9353: PROCEDURE Stamp_RequestId_For_ReImport( p_request_id IN MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE )
9354: IS
9355: l_index PLS_INTEGER;
9356: BEGIN
9357: IF G_MSII_REIMPORT_ROWS IS NOT NULL THEN
9356: BEGIN
9357: IF G_MSII_REIMPORT_ROWS IS NOT NULL THEN
9358: l_index := G_MSII_REIMPORT_ROWS.FIRST;
9359: WHILE l_index IS NOT NULL LOOP
9360: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9361: SET REQUEST_ID = p_request_id
9362: WHERE ROWID = G_MSII_REIMPORT_ROWS( l_index );
9363: l_index := G_MSII_REIMPORT_ROWS.next( l_index );
9364: END LOOP;
9365: G_MSII_REIMPORT_ROWS := NULL;
9366: END IF;
9367: END;
9368:
9369: PROCEDURE Log_Error_For_ReImport(p_request_id IN MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE
9370: , p_target_rowid IN UROWID
9371: , p_err_msg IN VARCHAR2
9372: )
9373: IS
9379: l_prog_appid NUMBER := FND_GLOBAL.PROG_APPL_ID;
9380: l_prog_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
9381: l_err_text VARCHAR2(4000);
9382: BEGIN
9383: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9384: SET REQUEST_ID = p_request_id
9385: WHERE ROWID = p_target_rowid
9386: RETURNING ORGANIZATION_ID, TRANSACTION_ID
9387: INTO l_org_id, l_transaction_id;
9395: ,p_request_id
9396: ,l_transaction_id
9397: ,p_err_msg
9398: ,'GLOBAL_TRADE_ITEM_NUMBER'
9399: ,'MTL_SYSTEM_ITEMS_INTERFACE'
9400: ,'INV_IOI_ERR'
9401: ,l_err_text);
9402: EXCEPTION WHEN OTHERS THEN
9403: NULL;
9403: NULL;
9404: END Log_Error_For_ReImport;
9405:
9406: PROCEDURE Prepare_Row_For_ReImport
9407: ( p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
9408: , p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE
9409: , p_target_rowid IN UROWID
9410: , x_return_code OUT NOCOPY NUMBER
9411: , x_err_msg OUT NOCOPY VARCHAR2
9404: END Log_Error_For_ReImport;
9405:
9406: PROCEDURE Prepare_Row_For_ReImport
9407: ( p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
9408: , p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE
9409: , p_target_rowid IN UROWID
9410: , x_return_code OUT NOCOPY NUMBER
9411: , x_err_msg OUT NOCOPY VARCHAR2
9412: )
9410: , x_return_code OUT NOCOPY NUMBER
9411: , x_err_msg OUT NOCOPY VARCHAR2
9412: )
9413: IS
9414: l_process_flag MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9415: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
9416: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9417: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9418: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9411: , x_err_msg OUT NOCOPY VARCHAR2
9412: )
9413: IS
9414: l_process_flag MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9415: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
9416: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9417: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9418: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9419: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9412: )
9413: IS
9414: l_process_flag MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9415: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
9416: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9417: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9418: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9419: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9420: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9413: IS
9414: l_process_flag MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9415: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
9416: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9417: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9418: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9419: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9420: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9421: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9415: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
9416: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9417: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9418: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9419: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9420: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9421: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9422: l_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9423: l_ssxref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
9416: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9417: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9418: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9419: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9420: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9421: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9422: l_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9423: l_ssxref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
9424:
9417: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9418: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9419: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9420: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9421: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9422: l_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9423: l_ssxref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
9424:
9425: l_is_pdh_batch BOOLEAN;
9418: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9419: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9420: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9421: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9422: l_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9423: l_ssxref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
9424:
9425: l_is_pdh_batch BOOLEAN;
9426: l_is_reimport BOOLEAN;
9424:
9425: l_is_pdh_batch BOOLEAN;
9426: l_is_reimport BOOLEAN;
9427: l_is_ssxref_only BOOLEAN;
9428: l_reimport_process_status MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9429: l_priv_exists VARCHAR2(1);
9430: l_msg_text VARCHAR2(4000);
9431: l_xref_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9432: l_xref_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9427: l_is_ssxref_only BOOLEAN;
9428: l_reimport_process_status MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9429: l_priv_exists VARCHAR2(1);
9430: l_msg_text VARCHAR2(4000);
9431: l_xref_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9432: l_xref_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9433: l_xref_desc MTL_CROSS_REFERENCES.DESCRIPTION%TYPE;
9434: l_org_code MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_CODE%TYPE;
9435: l_party_name VARCHAR2(1000); -- Bug: 5355759
9428: l_reimport_process_status MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9429: l_priv_exists VARCHAR2(1);
9430: l_msg_text VARCHAR2(4000);
9431: l_xref_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9432: l_xref_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9433: l_xref_desc MTL_CROSS_REFERENCES.DESCRIPTION%TYPE;
9434: l_org_code MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_CODE%TYPE;
9435: l_party_name VARCHAR2(1000); -- Bug: 5355759
9436: BEGIN
9430: l_msg_text VARCHAR2(4000);
9431: l_xref_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9432: l_xref_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9433: l_xref_desc MTL_CROSS_REFERENCES.DESCRIPTION%TYPE;
9434: l_org_code MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_CODE%TYPE;
9435: l_party_name VARCHAR2(1000); -- Bug: 5355759
9436: BEGIN
9437: x_return_code := 0;
9438: BEGIN -- CHECK THAT BATCH HAS A HEADER
9460: SAVEPOINT do_prepare_master_row; -- ENSURES THAT THE UPDATES ARE AN ATOMIC UNIT WITHIN THE TRANSACTION
9461: -- XXX: Should child updates be attempted first?
9462:
9463: -- update/select master item row
9464: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9465: SET PROCESS_FLAG = CASE PROCESS_FLAG
9466: WHEN 3 THEN 1
9467: -- WHEN 7 THEN G_IOI_STAMP_REQUEST_ID_FLAG
9468: ELSE PROCESS_FLAG END
9507: IF l_item_id IS NOT NULL THEN
9508: -- xxx: should the item number to use for comparison be fetched from the msii kfv?
9509:
9510: -- updating the item's org assignments
9511: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9512: SET PROCESS_FLAG = 1
9513: WHERE ( INVENTORY_ITEM_ID = l_item_id
9514: OR ( ITEM_NUMBER = l_item_number
9515: AND INVENTORY_ITEM_ID IS NULL
9626: );
9627: l_is_reimport := l_is_reimport OR ( 0 <> SQL%ROWCOUNT );
9628: ELSE -- item id is null
9629: -- updating the item's org assignments
9630: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9631: SET PROCESS_FLAG = 1
9632: WHERE ITEM_NUMBER = l_item_number
9633: AND PROCESS_FLAG = 3
9634: AND SET_PROCESS_ID = p_batch_id
9732: l_reimport_process_status := 1;
9733: END IF;
9734:
9735: -- update/select master item row
9736: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9737: SET PROCESS_FLAG = CASE PROCESS_FLAG
9738: WHEN 3 THEN l_reimport_process_status
9739: -- WHEN 7 THEN G_IOI_STAMP_REQUEST_ID_FLAG
9740: ELSE PROCESS_FLAG END
9816: FND_MESSAGE.SET_NAME('EGO', 'EGO_NO_EDIT_XREF_PRIV');
9817: FND_MESSAGE.SET_TOKEN('ITEM', l_xref_item_number);
9818: FND_MESSAGE.SET_TOKEN('ORG', l_org_code);
9819: l_msg_text := FND_MESSAGE.GET;
9820: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9821: SET PROCESS_FLAG = 3
9822: WHERE ROWID = p_target_rowid;
9823: x_return_code := G_NEED_TO_LOG_ERROR;
9824: x_err_msg := l_msg_text;
9832: FND_MESSAGE.SET_TOKEN('ITEM', l_item_number);
9833: FND_MESSAGE.SET_TOKEN('ORG', l_org_code);
9834: l_msg_text := FND_MESSAGE.GET;
9835:
9836: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9837: SET PROCESS_FLAG = 3
9838: WHERE ROWID = p_target_rowid;
9839:
9840: x_return_code := G_NEED_TO_LOG_ERROR;
9851: FND_MESSAGE.SET_TOKEN('ITEM', l_item_number);
9852: FND_MESSAGE.SET_TOKEN('ORG', l_org_code);
9853: l_msg_text := FND_MESSAGE.GET;
9854:
9855: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9856: SET PROCESS_FLAG = 3
9857: WHERE ROWID = p_target_rowid;
9858:
9859: x_return_code := G_NEED_TO_LOG_ERROR;
9871: FND_MESSAGE.SET_TOKEN('ITEM', l_item_number);
9872: FND_MESSAGE.SET_TOKEN('ORG', l_org_code);
9873: l_msg_text := FND_MESSAGE.GET;
9874:
9875: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9876: SET PROCESS_FLAG = 3
9877: WHERE ROWID = p_target_rowid;
9878:
9879: x_return_code := G_NEED_TO_LOG_ERROR;
9884: END IF;
9885:
9886: IF NOT l_is_ssxref_only THEN
9887: -- updating item's org assignments
9888: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9889: SET PROCESS_FLAG = 1
9890: WHERE SOURCE_SYSTEM_ID = l_ss_id
9891: AND SOURCE_SYSTEM_REFERENCE = l_ssr
9892: AND PROCESS_FLAG = 3
11889:
11890: IF (p_batch_id IS NOT NULL AND p_src_system_id IS NOT NULL AND p_src_system_ref IS NOT NULL) THEN
11891:
11892: --------------------------------
11893: -- MTL_SYSTEM_ITEMS_INTERFACE --
11894: --------------------------------
11895: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
11896: SET ORGANIZATION_ID =
11897: NVL(ORGANIZATION_ID,
11891:
11892: --------------------------------
11893: -- MTL_SYSTEM_ITEMS_INTERFACE --
11894: --------------------------------
11895: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
11896: SET ORGANIZATION_ID =
11897: NVL(ORGANIZATION_ID,
11898: (SELECT MP.ORGANIZATION_ID
11899: FROM MTL_PARAMETERS MP
11958:
11959: CURSOR c_batch_pks IS
11960: SELECT MSII.SOURCE_SYSTEM_ID SOURCE_SYSTEM_ID
11961: ,MSII.SOURCE_SYSTEM_REFERENCE SOURCE_SYSTEM_REFERENCE
11962: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
11963: WHERE MSII.SET_PROCESS_ID = p_batch_id
11964: AND MSII.PROCESS_FLAG = 0
11965: AND MSII.CONFIRM_STATUS IN ('US', 'UM', 'UN');
11966:
12098: ,inventory_item_id
12099: ,item_number
12100: ,transaction_id
12101: ,transaction_type
12102: FROM mtl_system_items_interface
12103: WHERE set_process_id = p_set_id
12104: AND process_flag = 0
12105: AND organization_id = p_org_id
12106: AND ( confirm_status IS NULL
12150: END IF;
12151:
12152: IF upper(item_record.transaction_type) = 'SYNC' THEN
12153: IF l_item_exist = 1 THEN
12154: UPDATE mtl_system_items_interface
12155: SET transaction_type = 'UPDATE'
12156: WHERE rowid = item_record.rowid;
12157: ELSE
12158: IF item_record.item_number IS NOT NULL THEN
12155: SET transaction_type = 'UPDATE'
12156: WHERE rowid = item_record.rowid;
12157: ELSE
12158: IF item_record.item_number IS NOT NULL THEN
12159: UPDATE mtl_system_items_interface
12160: SET transaction_type = 'CREATE', inventory_item_id = NULL
12161: WHERE rowid = item_record.rowid;
12162: ELSE
12163: UPDATE mtl_system_items_interface
12159: UPDATE mtl_system_items_interface
12160: SET transaction_type = 'CREATE', inventory_item_id = NULL
12161: WHERE rowid = item_record.rowid;
12162: ELSE
12163: UPDATE mtl_system_items_interface
12164: SET transaction_type = 'UPDATE', inventory_item_id = NULL
12165: WHERE rowid = item_record.rowid;
12166: END IF;
12167: END IF;
12170: END UPDATE_ITEM_SYNC_RECORDS;
12171:
12172:
12173: PROCEDURE SET_CONFIRM_STATUS
12174: (p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
12175: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12176: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12177: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12178: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12171:
12172:
12173: PROCEDURE SET_CONFIRM_STATUS
12174: (p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
12175: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12176: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12177: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12178: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12179: ,p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE DEFAULT NULL
12172:
12173: PROCEDURE SET_CONFIRM_STATUS
12174: (p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
12175: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12176: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12177: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12178: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12179: ,p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE DEFAULT NULL
12180: ,p_check_matching_table IN FLAG DEFAULT FND_API.G_FALSE
12173: PROCEDURE SET_CONFIRM_STATUS
12174: (p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
12175: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12176: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12177: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12178: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12179: ,p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE DEFAULT NULL
12180: ,p_check_matching_table IN FLAG DEFAULT FND_API.G_FALSE
12181: ,errmsg OUT NOCOPY VARCHAR2
12174: (p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
12175: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12176: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12177: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12178: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12179: ,p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE DEFAULT NULL
12180: ,p_check_matching_table IN FLAG DEFAULT FND_API.G_FALSE
12181: ,errmsg OUT NOCOPY VARCHAR2
12182: ,retcode OUT NOCOPY VARCHAR2
12175: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12176: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12177: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12178: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12179: ,p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE DEFAULT NULL
12180: ,p_check_matching_table IN FLAG DEFAULT FND_API.G_FALSE
12181: ,errmsg OUT NOCOPY VARCHAR2
12182: ,retcode OUT NOCOPY VARCHAR2
12183: ) IS
12182: ,retcode OUT NOCOPY VARCHAR2
12183: ) IS
12184: CURSOR cur_old_status IS
12185: SELECT CONFIRM_STATUS
12186: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_IMPORT_BATCHES_B BA
12187: WHERE MSII.SET_PROCESS_ID = p_batch_id
12188: AND MSII.SOURCE_SYSTEM_ID = p_source_system_id
12189: AND MSII.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
12190: AND MSII.ORGANIZATION_ID = BA.ORGANIZATION_ID
12191: AND BA.BATCH_ID = p_batch_id
12192: AND NVL(MSII.PROCESS_FLAG, 0) < 1
12193: AND ROWNUM < 2;
12194:
12195: l_old_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
12196: l_need_resolve_unconf_status FLAG := p_check_matching_table;
12197: l_resolved_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := p_new_status;
12198: l_item_in_matching_table NUMBER;
12199: BEGIN
12193: AND ROWNUM < 2;
12194:
12195: l_old_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
12196: l_need_resolve_unconf_status FLAG := p_check_matching_table;
12197: l_resolved_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := p_new_status;
12198: l_item_in_matching_table NUMBER;
12199: BEGIN
12200: OPEN cur_old_status;
12201: FETCH cur_old_status INTO l_old_status;
12291: AND SOURCE_SYSTEM_ID = p_source_system_id
12292: AND SOURCE_SYSTEM_REFERENCE = p_source_system_reference;
12293: END IF;
12294:
12295: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
12296: SET CONFIRM_STATUS = l_resolved_status, INVENTORY_ITEM_ID = p_inventory_item_id
12297: WHERE SET_PROCESS_ID = p_batch_id
12298: AND SOURCE_SYSTEM_ID = p_source_system_id
12299: AND SOURCE_SYSTEM_REFERENCE = p_source_system_reference
12385: THEN 1
12386: ELSE NULL
12387: END
12388: ) UPDATE_CNT
12389: FROM MTL_SYSTEM_ITEMS_INTERFACE
12390: WHERE SET_PROCESS_ID = p_set_process_id
12391: AND ( (ORGANIZATION_ID is not null and ORGANIZATION_ID = p_organization_id )
12392: OR
12393: (ORGANIZATION_ID is null and ORGANIZATION_CODE = p_organization_code )
12412: THEN 1
12413: ELSE NULL
12414: END
12415: ) UPDATE_CNT
12416: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
12417: WHERE SET_PROCESS_ID = p_set_process_id
12418: AND EXISTS
12419: (
12420: SELECT NULL