[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_SSXREF_IS_NULL_01' --bug 11894684, replace 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_SSXID_INVALID_01'--bug 11894684, replace 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:
643: for any performance issues we need to gather stats manualy so no need to gather stats in the code.
644: */
645: /*
646:
647: -- checking whether stats needs to be collected on MTL_SYSTEM_ITEMS_INTERFACE
648: SELECT COUNT(1) INTO l_records
649: FROM MTL_SYSTEM_ITEMS_INTERFACE
650: WHERE SET_PROCESS_ID = p_data_set_id;
651:
645: /*
646:
647: -- checking whether stats needs to be collected on MTL_SYSTEM_ITEMS_INTERFACE
648: SELECT COUNT(1) INTO l_records
649: FROM MTL_SYSTEM_ITEMS_INTERFACE
650: WHERE SET_PROCESS_ID = p_data_set_id;
651:
652: l_inv_installed := FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema);
653: IF (l_records > l_stats_profile) AND l_inv_installed AND l_schema IS NOT NULL THEN
650: WHERE SET_PROCESS_ID = p_data_set_id;
651:
652: l_inv_installed := FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema);
653: IF (l_records > l_stats_profile) AND l_inv_installed AND l_schema IS NOT NULL THEN
654: FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_SYSTEM_ITEMS_INTERFACE');
655: Debug_Conc_Log('Collected Statistics on MTL_SYSTEM_ITEMS_INTERFACE');
656: END IF;
657:
658: -- checking whether stats needs to be collected on MTL_ITEM_REVISIONS_INTERFACE
651:
652: l_inv_installed := FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema);
653: IF (l_records > l_stats_profile) AND l_inv_installed AND l_schema IS NOT NULL THEN
654: FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_SYSTEM_ITEMS_INTERFACE');
655: Debug_Conc_Log('Collected Statistics on MTL_SYSTEM_ITEMS_INTERFACE');
656: END IF;
657:
658: -- checking whether stats needs to be collected on MTL_ITEM_REVISIONS_INTERFACE
659: SELECT COUNT(1) INTO l_records
1663: , 0 cnt
1664: , 0 rnk
1665: , 'N' excluded_flag
1666: , msii.*
1667: FROM MTL_SYSTEM_ITEMS_INTERFACE msii;
1668:
1669: /*
1670: * Types for fetching the rows to merged.
1671: */
1676: l_merged_rows MSII_ROWS;
1677: l_merge_base MSII_ROW;
1678: l_old_rowids UROWID_TABLE;
1679:
1680: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE := p_ss_id;
1681: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
1682: l_candidate_trans MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_TYPE%TYPE;
1683:
1684: l_cur_rank PLS_INTEGER := 0; -- because rank() starts at 1
1677: l_merge_base MSII_ROW;
1678: l_old_rowids UROWID_TABLE;
1679:
1680: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE := p_ss_id;
1681: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
1682: l_candidate_trans MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_TYPE%TYPE;
1683:
1684: l_cur_rank PLS_INTEGER := 0; -- because rank() starts at 1
1685: l_mrow_ix PLS_INTEGER := 0;
1678: l_old_rowids UROWID_TABLE;
1679:
1680: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE := p_ss_id;
1681: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
1682: l_candidate_trans MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_TYPE%TYPE;
1683:
1684: l_cur_rank PLS_INTEGER := 0; -- because rank() starts at 1
1685: l_mrow_ix PLS_INTEGER := 0;
1686: l_is_pdh_batch BOOLEAN;
1685: l_mrow_ix PLS_INTEGER := 0;
1686: l_is_pdh_batch BOOLEAN;
1687: l_excluded_flag VARCHAR2(1);
1688:
1689: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE := p_master_org_id;
1690: l_pdh_batch_flag FLAG := p_is_pdh_batch;
1691: l_proc_log_prefix CONSTANT VARCHAR2( 30 ) := 'merge_items - ';
1692:
1693: --bug 14347548 add for compilation error start
1732: )
1733: rnk
1734: , null EXCLUDED_FLAG
1735: , msii.*
1736: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
1737: WHERE PROCESS_FLAG = 1
1738: AND SET_PROCESS_ID = p_batch_id
1739: AND ( SOURCE_SYSTEM_ID IS NULL
1740: OR SOURCE_SYSTEM_ID = G_PDH_SOURCE_SYSTEM_ID
1774: )
1775: )
1776: EXCLUDED_FLAG
1777: , msii.*
1778: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
1779: WHERE PROCESS_FLAG = 0
1780: AND SET_PROCESS_ID = p_batch_id
1781: AND SOURCE_SYSTEM_ID = l_ss_id
1782: AND SOURCE_SYSTEM_REFERENCE IS NOT NULL
1989: /* generate using the following script:
1990: SET LINESIZE 200
1991: 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; '
1992: FROM ALL_TAB_COLUMNS
1993: WHERE TABLE_NAME = 'MTL_SYSTEM_ITEMS_INTERFACE'
1994: AND COLUMN_NAME NOT IN
1995: ( -- special cases (for merge)
1996: 'INVENTORY_ITEM_ID'
1997: , 'ITEM_NUMBER'
2444: IF l_merged_rows IS NOT NULL THEN
2445: -- delete
2446: Debug_Conc_Log( l_proc_log_prefix || 'Deleting ' || l_old_rowids.COUNT || ' old rows ...' );
2447: FORALL rid_ix IN INDICES OF l_old_rowids
2448: DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
2449: WHERE ROWID = l_old_rowids( rid_ix );
2450: -- insert
2451: Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
2452: FORALL row_index IN INDICES OF l_merged_rows
2449: WHERE ROWID = l_old_rowids( rid_ix );
2450: -- insert
2451: Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
2452: FORALL row_index IN INDICES OF l_merged_rows
2453: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
2454: VALUES l_merged_rows( row_index );
2455:
2456: --Bug 13505630
2457: For i in 1..l_merged_rows.COUNT loop
2468: ,l_request_id
2469: ,l_merged_rows(i).TRANSACTION_ID
2470: ,l_msg_text
2471: ,' '
2472: ,'MTL_SYSTEM_ITEMS_INTERFACE'
2473: ,'INV_IOI_ERR_IMPT_DPLC_ITMS'--bug 11894684, replace INV_IOI_ERR
2474: ,l_err_text);
2475: End loop;
2476: END IF;
3155: SELECT ROWID rid
3156: , 0 cnt
3157: , 0 rnk
3158: , msii.*
3159: FROM MTL_SYSTEM_ITEMS_INTERFACE msii;
3160:
3161: TYPE MSII_ROWS IS TABLE OF MSII_ROW INDEX BY BINARY_INTEGER;
3162: TYPE MSII_CURSOR IS REF CURSOR;
3163: c_target_rows MSII_CURSOR;
3167: l_new_rows MSII_ROWS;
3168: l_merged_row MSII_ROW;
3169: l_old_rowids UROWID_TABLE;
3170:
3171: l_candidate_trans MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_TYPE%TYPE;
3172:
3173: l_cur_rank PLS_INTEGER := 0; -- because rank() starts at 1
3174: l_mrow_ix PLS_INTEGER := 0;
3175: l_new_row_idx PLS_INTEGER := 0;
3173: l_cur_rank PLS_INTEGER := 0; -- because rank() starts at 1
3174: l_mrow_ix PLS_INTEGER := 0;
3175: l_new_row_idx PLS_INTEGER := 0;
3176:
3177: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE := p_master_org_id;
3178: l_proc_log_prefix CONSTANT VARCHAR2( 30 ) := 'Merge_Items_For_Import - ';
3179: BEGIN
3180: Debug_Conc_Log( l_proc_log_prefix || 'Entering' );
3181: Debug_Conc_Log( l_proc_log_prefix || 'Batch ID: ' || p_batch_id );
3179: BEGIN
3180: Debug_Conc_Log( l_proc_log_prefix || 'Entering' );
3181: Debug_Conc_Log( l_proc_log_prefix || 'Batch ID: ' || p_batch_id );
3182: -- making sure that item_number always has a value for ready records
3183: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
3184: SET ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
3185: FROM MTL_SYSTEM_ITEMS_KFV
3186: WHERE INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
3187: AND ORGANIZATION_ID = MSII.ORGANIZATION_ID)
3197: ROWID rid,
3198: COUNT( * ) OVER ( PARTITION BY ITEM_NUMBER, ORGANIZATION_ID) cnt,
3199: RANK() OVER ( ORDER BY ITEM_NUMBER, ORGANIZATION_ID) rnk,
3200: msii.*
3201: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
3202: WHERE PROCESS_FLAG = 1
3203: AND SET_PROCESS_ID = p_batch_id
3204: AND ITEM_NUMBER IS NOT NULL
3205: AND ORGANIZATION_ID IS NOT NULL
3786: IF l_merged_rows IS NOT NULL THEN
3787: -- delete
3788: Debug_Conc_Log( l_proc_log_prefix || 'Deleting ' || l_old_rowids.COUNT || ' old rows ...' );
3789: FORALL rid_ix IN INDICES OF l_old_rowids
3790: DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
3791: WHERE ROWID = l_old_rowids( rid_ix );
3792: -- insert
3793: Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
3794: FORALL row_index IN INDICES OF l_merged_rows
3791: WHERE ROWID = l_old_rowids( rid_ix );
3792: -- insert
3793: Debug_Conc_Log( l_proc_log_prefix || 'Inserting ' || l_merged_rows.COUNT || ' merged rows ...' );
3794: FORALL row_index IN INDICES OF l_merged_rows
3795: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
3796: VALUES l_merged_rows( row_index );
3797: END IF;
3798: Debug_Conc_Log( l_proc_log_prefix || 'Exiting' );
3799: EXCEPTION WHEN OTHERS THEN
4991: l_proc_log_prefix VARCHAR2(50) := 'Demerge_Batch_After_Import - ';
4992: BEGIN
4993: Debug_Conc_Log( l_proc_log_prefix || 'Starting ...' );
4994: -- processing items and org assignments
4995: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
4996: SET (PROCESS_FLAG, INVENTORY_ITEM_ID) =
4997: (SELECT PROCESS_FLAG, INVENTORY_ITEM_ID
4998: FROM MTL_SYSTEM_ITEMS_INTERFACE msii_merged
4999: WHERE msii_merged.ITEM_NUMBER = msii.ITEM_NUMBER
4994: -- processing items and org assignments
4995: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
4996: SET (PROCESS_FLAG, INVENTORY_ITEM_ID) =
4997: (SELECT PROCESS_FLAG, INVENTORY_ITEM_ID
4998: FROM MTL_SYSTEM_ITEMS_INTERFACE msii_merged
4999: WHERE msii_merged.ITEM_NUMBER = msii.ITEM_NUMBER
5000: AND msii_merged.ORGANIZATION_ID = msii.ORGANIZATION_ID
5001: AND msii_merged.REQUEST_ID = msii.REQUEST_ID
5002: AND msii_merged.SET_PROCESS_ID = msii.SET_PROCESS_ID
5104: intf.TRANSACTION_ID,
5105: c.CROSS_REFERENCE EXISTING_GTIN,
5106: c.DESCRIPTION EXISTING_GTIN_DESC,
5107: c.CROSS_REFERENCE_ID
5108: FROM MTL_SYSTEM_ITEMS_INTERFACE intf, MTL_PARAMETERS p, MTL_CROSS_REFERENCES c
5109: WHERE intf.SET_PROCESS_ID = p_data_set_id
5110: AND intf.ORGANIZATION_ID = p.ORGANIZATION_ID
5111: AND p.ORGANIZATION_ID = p.MASTER_ORGANIZATION_ID
5112: AND intf.PROCESS_FLAG IN (5,7)
5163: ,l_request_id
5164: ,i.TRANSACTION_ID
5165: ,l_msg_text
5166: ,'GLOBAL_TRADE_ITEM_NUMBER'
5167: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5168: ,'INV_IOI_ERR_NO_ED_XREF_PRIV_01'--bug 11894684, replace INV_IOI_ERR
5169: ,l_err_text);
5170: ELSE
5171: Debug_Conc_Log('Edit Cross Reference Privilege is present on item');
5190: ,l_request_id
5191: ,i.TRANSACTION_ID
5192: ,l_msg_text
5193: ,'GLOBAL_TRADE_ITEM_NUMBER'
5194: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5195: ,'INV_IOI_ERR_GTIN_NOT_NUMBER' --bug 11894684, replace INV_IOI_ERR
5196: ,l_err_text);
5197: l_error := TRUE;
5198: END;
5210: ,l_request_id
5211: ,i.TRANSACTION_ID
5212: ,l_msg_text
5213: ,'GLOBAL_TRADE_ITEM_NUMBER'
5214: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5215: ,'INV_IOI_ERR_GTIN_LTH_N_CRRCT'--bug 11894684, replace INV_IOI_ERR
5216: ,l_err_text);
5217: l_error := TRUE;
5218: END IF; --end IF LENGTH(i.GLOBAL_TRADE_ITEM_NUMBER
5230: ,l_request_id
5231: ,i.TRANSACTION_ID
5232: ,l_msg_text
5233: ,'GLOBAL_TRADE_ITEM_NUMBER'
5234: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5235: ,'INV_IOI_ERR_CKDIGIT_INVALID'--bug 11894684, replace INV_IOI_ERR
5236: ,l_err_text);
5237: l_error := TRUE;
5238: END IF; --end IF (NOT l_error) AND
5250: ,l_request_id
5251: ,i.TRANSACTION_ID
5252: ,l_msg_text
5253: ,'GLOBAL_TRADE_ITEM_NUMBER'
5254: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5255: ,'INV_IOI_ERR_GTIN_LDING_0_IVLD'--bug 11894684, replace INV_IOI_ERR
5256: ,l_err_text);
5257: l_error := TRUE;
5258: END IF;
5271: ,l_request_id
5272: ,i.TRANSACTION_ID
5273: ,l_msg_text
5274: ,'GLOBAL_TRADE_ITEM_NUMBER'
5275: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5276: ,'INV_IOI_ERR_GTIN_3RD_D_IVLD'--bug 11894684, replace INV_IOI_ERR
5277: ,l_err_text);
5278: l_error := TRUE;
5279: END IF;
5292: ,l_request_id
5293: ,i.TRANSACTION_ID
5294: ,l_msg_text
5295: ,'GLOBAL_TRADE_ITEM_NUMBER'
5296: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5297: ,'INV_IOI_ERR_GTIN_7TO9D_IVLD'--bug 11894684, replace INV_IOI_ERR
5298: ,l_err_text);
5299: l_error := TRUE;
5300: END IF;
5312: ,l_request_id
5313: ,i.TRANSACTION_ID
5314: ,l_msg_text
5315: ,'GLOBAL_TRADE_ITEM_NUMBER'
5316: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5317: ,'INV_IOI_ERR_GTIN_0TO4D_IVLD'--bug 11894684, replace INV_IOI_ERR
5318: ,l_err_text);
5319: l_error := TRUE;
5320: END IF;
5333: ,l_request_id
5334: ,i.TRANSACTION_ID
5335: ,l_msg_text
5336: ,'GLOBAL_TRADE_ITEM_NUMBER'
5337: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5338: ,'INV_IOI_ERR_GTIN_2TO3_D_IVLD'--bug 11894684, replace INV_IOI_ERR
5339: ,l_err_text);
5340: l_error := TRUE;
5341: END IF;
5368: ,l_request_id
5369: ,i.TRANSACTION_ID
5370: ,l_msg_text
5371: ,'GLOBAL_TRADE_ITEM_NUMBER'
5372: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5373: ,'INV_IOI_ERR_GTIN_EXS_W_PKITM' --bug 11894684, replace INV_IOI_ERR
5374: ,l_err_text);
5375: l_error := TRUE;
5376: EXCEPTION WHEN NO_DATA_FOUND THEN
5462: ,l_request_id
5463: ,i.TRANSACTION_ID
5464: ,l_msg_text
5465: ,'GLOBAL_TRADE_ITEM_NUMBER'
5466: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5467: ,'INV_IOI_ERR_GTIN_NOT_UPDABLE'--bug 11894684, replace INV_IOI_ERR
5468: ,l_err_text);
5469: END IF; -- IF i.EXISTING_GTIN <> i.GLOBAL_TRADE_ITEM_NUMBER THEN
5470:
5493: ,l_request_id
5494: ,l_transaction_id
5495: ,l_msg_text
5496: ,'GLOBAL_TRADE_ITEM_NUMBER'
5497: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5498: ,'INV_IOI_ERR_RAISE_BE4_GTIN_CR'--bug 11894684, replace INV_IOI_ERR
5499: ,l_err_text);
5500: END IF;
5501: END IF;
5632: INVENTORY_ITEM_ID,
5633: ORGANIZATION_ID,
5634: TRANSACTION_ID,
5635: TRANSACTION_TYPE
5636: FROM MTL_SYSTEM_ITEMS_INTERFACE msi
5637: WHERE msi.PROCESS_FLAG IN (5, 7)
5638: AND msi.SET_PROCESS_ID = p_data_set_id
5639: AND msi.SOURCE_SYSTEM_ID IS NOT NULL
5640: AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
5657: msi.ORGANIZATION_ID,
5658: msi.TRANSACTION_ID,
5659: msi.TRANSACTION_TYPE,
5660: mcr.CROSS_REFERENCE_ID
5661: FROM MTL_SYSTEM_ITEMS_INTERFACE msi, MTL_CROSS_REFERENCES mcr
5662: WHERE msi.PROCESS_FLAG IN (5, 7)
5663: AND msi.SET_PROCESS_ID = p_data_set_id
5664: AND msi.SOURCE_SYSTEM_ID IS NOT NULL
5665: AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
5686: ORGANIZATION_ID,
5687: TRANSACTION_ID,
5688: TRANSACTION_TYPE,
5689: ROWID AS ROW_ID
5690: FROM MTL_SYSTEM_ITEMS_INTERFACE msi
5691: WHERE msi.PROCESS_FLAG IN (0, 11)
5692: AND msi.SET_PROCESS_ID = p_data_set_id
5693: AND msi.SOURCE_SYSTEM_ID IS NOT NULL
5694: AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
5713: msi.TRANSACTION_ID,
5714: msi.TRANSACTION_TYPE,
5715: mcr.CROSS_REFERENCE_ID,
5716: msi.ROWID AS ROW_ID
5717: FROM MTL_SYSTEM_ITEMS_INTERFACE msi, MTL_CROSS_REFERENCES mcr
5718: WHERE msi.PROCESS_FLAG IN (0, 11)
5719: AND msi.SET_PROCESS_ID = p_data_set_id
5720: AND msi.SOURCE_SYSTEM_ID IS NOT NULL
5721: AND msi.SOURCE_SYSTEM_REFERENCE IS NOT NULL
5745: dumm_status NUMBER;
5746: l_org_id NUMBER;
5747:
5748: l_import_xref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
5749: l_inventory_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
5750: l_transaction_id MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_ID%TYPE;
5751: BEGIN
5752: Debug_Conc_Log('Starting Process_SSXref_Intf_Rows for batch_id='||TO_CHAR(p_data_set_id));
5753: BEGIN
5746: l_org_id NUMBER;
5747:
5748: l_import_xref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
5749: l_inventory_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
5750: l_transaction_id MTL_SYSTEM_ITEMS_INTERFACE.TRANSACTION_ID%TYPE;
5751: BEGIN
5752: Debug_Conc_Log('Starting Process_SSXref_Intf_Rows for batch_id='||TO_CHAR(p_data_set_id));
5753: BEGIN
5754: SELECT b.SOURCE_SYSTEM_ID, NVL(opt.IMPORT_XREF_ONLY, 'N')
5792:
5793: -- Bug: 5262421
5794: -- Updating item, user-defined attrs, gdsn attrs, AMLs to successful process status
5795: -- so that they appear correctly in the Imported tab
5796: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
5797: SET PROCESS_FLAG = 7,
5798: TRANSACTION_TYPE = 'UPDATE',
5799: PROGRAM_APPLICATION_ID = l_prog_appid,
5800: PROGRAM_ID = l_prog_id,
5856: l_raise_bus_event := TRUE;
5857: -- Bug: 5262421
5858: -- Updating item, user-defined attrs, gdsn attrs, AMLs to successful process status
5859: -- so that they appear correctly in the Imported tab
5860: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
5861: SET PROCESS_FLAG = 7,
5862: TRANSACTION_TYPE = 'UPDATE',
5863: PROGRAM_APPLICATION_ID = l_prog_appid,
5864: PROGRAM_ID = l_prog_id,
5957: ,l_request_id
5958: ,l_transaction_id
5959: ,l_msg_text
5960: ,'SOURCE_SYSTEM_REFERENCE'
5961: ,'MTL_SYSTEM_ITEMS_INTERFACE'
5962: ,'INV_IOI_ERR_RAISE_BE4_SSICR'--bug 11894684, replace INV_IOI_ERR
5963: ,l_err_text);
5964: END IF; --IF l_return_status <> 'S' THEN
5965: END IF; --IF l_raise_event THEN
6035: retain DESCRIPTION.
6036: */
6037: /* start bug 16309495
6038: In below query Item_number is fetched from MTL_SYSTEM_ITEMS_KFV based on p_inventory_item_id and p_organization_id
6039: and updates MTL_SYSTEM_ITEMS_INTERFACE.
6040: This will fire only for Single Match case.
6041: As we are populating INVENTORY_ITEM_ID,ORGANIZATION_ID
6042: There is no harm in removing ITEM_NUMBER.
6043: We can fetch this value later if need based on invId and OrgId.
6042: There is no harm in removing ITEM_NUMBER.
6043: We can fetch this value later if need based on invId and OrgId.
6044: Hence commenting the inner query and replaced it with Null value.
6045: end bug 16309495*/
6046: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
6047: SET PROCESS_FLAG = 0,
6048: ORGANIZATION_ID = l_org_id,
6049: ORGANIZATION_CODE = l_org_code,
6050: INVENTORY_ITEM_ID = p_inventory_item_id,
6106: SOURCE_SYSTEM_ID = p_source_system_id AND
6107: SOURCE_SYSTEM_REFERENCE = p_source_system_reference AND
6108: (ORGANIZATION_ID = p_organization_id OR ORGANIZATION_CODE = p_organization_code) AND
6109: REVISION IS NOT NULL AND
6110: Upper(REVISION) NOT IN ( SELECT B.REVISION FROM MTL_ITEM_REVISIONS_B B, MTL_SYSTEM_ITEMS_INTERFACE MSII
6111: WHERE MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
6112: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
6113: AND (MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
6114: OR MSII.ORGANIZATION_CODE = MIRI.ORGANIZATION_CODE)
6141: SELECT MSII.SOURCE_SYSTEM_ID,
6142: MSII.SOURCE_SYSTEM_REFERENCE,
6143: MSII.SET_PROCESS_ID,
6144: MSII.BUNDLE_ID
6145: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
6146: WHERE MSII.PROCESS_FLAG = 0
6147: AND MSII.SET_PROCESS_ID = p_data_set_id
6148: AND MSII.BUNDLE_ID = p_bundle_id;
6149:
6189: p_source_system_reference IN VARCHAR2,
6190: p_bundle_id IN NUMBER
6191: )
6192: RETURN VARCHAR2 IS
6193: l_pdh_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
6194: l_pdh_description MTL_SYSTEM_ITEMS_INTERFACE.DESCRIPTION%TYPE;
6195: l_pdh_item_catalog_group_id MTL_SYSTEM_ITEMS_INTERFACE.ITEM_CATALOG_GROUP_ID%TYPE;
6196: l_confirm_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
6197: l_item_num_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_NUM_GEN_METHOD%TYPE;
6190: p_bundle_id IN NUMBER
6191: )
6192: RETURN VARCHAR2 IS
6193: l_pdh_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
6194: l_pdh_description MTL_SYSTEM_ITEMS_INTERFACE.DESCRIPTION%TYPE;
6195: l_pdh_item_catalog_group_id MTL_SYSTEM_ITEMS_INTERFACE.ITEM_CATALOG_GROUP_ID%TYPE;
6196: l_confirm_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
6197: l_item_num_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_NUM_GEN_METHOD%TYPE;
6198: l_item_desc_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_DESC_GEN_METHOD%TYPE;
6191: )
6192: RETURN VARCHAR2 IS
6193: l_pdh_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
6194: l_pdh_description MTL_SYSTEM_ITEMS_INTERFACE.DESCRIPTION%TYPE;
6195: l_pdh_item_catalog_group_id MTL_SYSTEM_ITEMS_INTERFACE.ITEM_CATALOG_GROUP_ID%TYPE;
6196: l_confirm_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
6197: l_item_num_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_NUM_GEN_METHOD%TYPE;
6198: l_item_desc_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_DESC_GEN_METHOD%TYPE;
6199: l_organization_id MTL_PARAMETERS.ORGANIZATION_ID%TYPE;
6192: RETURN VARCHAR2 IS
6193: l_pdh_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
6194: l_pdh_description MTL_SYSTEM_ITEMS_INTERFACE.DESCRIPTION%TYPE;
6195: l_pdh_item_catalog_group_id MTL_SYSTEM_ITEMS_INTERFACE.ITEM_CATALOG_GROUP_ID%TYPE;
6196: l_confirm_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
6197: l_item_num_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_NUM_GEN_METHOD%TYPE;
6198: l_item_desc_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_DESC_GEN_METHOD%TYPE;
6199: l_organization_id MTL_PARAMETERS.ORGANIZATION_ID%TYPE;
6200: l_organization_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
6198: l_item_desc_gen MTL_ITEM_CATALOG_GROUPS_B.ITEM_DESC_GEN_METHOD%TYPE;
6199: l_organization_id MTL_PARAMETERS.ORGANIZATION_ID%TYPE;
6200: l_organization_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
6201: l_batch_rev_policy EGO_IMPORT_OPTION_SETS.REVISION_IMPORT_POLICY%TYPE;
6202: l_instance_rev_policy MTL_SYSTEM_ITEMS_INTERFACE.REVISION_IMPORT_POLICY%TYPE;
6203: l_style_item_flag MTL_SYSTEM_ITEMS_INTERFACE.STYLE_ITEM_FLAG%TYPE;
6204: BEGIN
6205: SELECT
6206: MP.ORGANIZATION_ID,
6199: l_organization_id MTL_PARAMETERS.ORGANIZATION_ID%TYPE;
6200: l_organization_code MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
6201: l_batch_rev_policy EGO_IMPORT_OPTION_SETS.REVISION_IMPORT_POLICY%TYPE;
6202: l_instance_rev_policy MTL_SYSTEM_ITEMS_INTERFACE.REVISION_IMPORT_POLICY%TYPE;
6203: l_style_item_flag MTL_SYSTEM_ITEMS_INTERFACE.STYLE_ITEM_FLAG%TYPE;
6204: BEGIN
6205: SELECT
6206: MP.ORGANIZATION_ID,
6207: MP.ORGANIZATION_CODE,
6232: CONFIRM_STATUS,
6233: NVL(REVISION_IMPORT_POLICY, l_batch_rev_policy),
6234: MSII.STYLE_ITEM_FLAG
6235: 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
6236: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_IMPORT_BATCHES_B BATCH
6237: WHERE MSII.SET_PROCESS_ID = p_data_set_id AND
6238: MSII.SOURCE_SYSTEM_ID = p_source_system_id AND
6239: MSII.SOURCE_SYSTEM_REFERENCE = p_source_system_reference AND
6240: MSII.ORGANIZATION_ID = BATCH.ORGANIZATION_ID AND
6354: )
6355: IS
6356: CURSOR c_ss_items_table IS
6357: SELECT rowid rid
6358: FROM mtl_system_items_interface
6359: WHERE set_process_id = p_set_id
6360: AND process_flag = 0
6361: AND organization_id /*bug 6402904 parse for child items also*/
6362: IN (SELECT organization_id
6369: FOR UPDATE OF item_number;
6370:
6371: CURSOR c_pdh_items_table IS
6372: SELECT rowid rid
6373: FROM mtl_system_items_interface
6374: WHERE set_process_id = p_set_id
6375: AND process_flag = 1
6376: AND item_number IS null
6377: FOR UPDATE OF item_number;
6376: AND item_number IS null
6377: FOR UPDATE OF item_number;
6378:
6379: l_err_text VARCHAR2(240) := NULL;
6380: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE := NULL;
6381: l_item_id MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE := NULL;
6382: l_ret_code INTEGER;
6383: BEGIN
6384: IF p_is_pdh_batch = FND_API.G_TRUE THEN
6414: , inventory_item_id
6415: , item_number
6416: , transaction_id
6417: , transaction_type
6418: FROM mtl_system_items_interface
6419: WHERE set_process_id = p_set_id
6420: AND organization_id = p_org_id
6421: AND process_flag = 1
6422: AND UPPER(transaction_type) = 'SYNC'
6464: l_item_exist := NVL(l_item_exist,0);
6465: END IF;
6466:
6467: IF l_item_exist = 1 THEN
6468: UPDATE mtl_system_items_interface
6469: SET transaction_type = 'UPDATE'
6470: WHERE rowid = item_record.rowid;
6471: ELSE
6472: UPDATE mtl_system_items_interface
6468: UPDATE mtl_system_items_interface
6469: SET transaction_type = 'UPDATE'
6470: WHERE rowid = item_record.rowid;
6471: ELSE
6472: UPDATE mtl_system_items_interface
6473: SET transaction_type = 'CREATE', inventory_item_id = NULL
6474: WHERE rowid = item_record.rowid;
6475: END IF;
6476: END LOOP;
6478: END UPDATE_ITEM_SYNC_RECORDS_PDH;
6479:
6480: /*
6481: * This procedure resolves source system item cross references
6482: * immediately after MTL_SYSTEM_ITEMS_INTERFACE is populated and
6483: * tries to resolve discrepencies between user entered CONFIRM_STATUS
6484: * (if any), TRANSACTION_TYPE (if any) and other data.
6485: * This procedure needs to be safe for re-execution if data is loaded into an
6486: * active batch repeatedly.
6545:
6546: -- populate organization_id if it's null
6547: -- if organization_code is not null, derive from it otherwise use
6548: -- organization id for batch
6549: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
6550: SET ORGANIZATION_ID =
6551: CASE
6552: WHEN ORGANIZATION_CODE IS NOT NULL
6553: THEN (SELECT ORGANIZATION_ID FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE = MSII.ORGANIZATION_CODE)
6733: AND eiai.SUPPLIER_SITE_NAME IS NOT NULL;
6734:
6735: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Done Resolving PK Values in Intersection Interface table' );
6736:
6737: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
6738: SET STYLE_ITEM_ID = (SELECT MSIK.INVENTORY_ITEM_ID
6739: FROM MTL_SYSTEM_ITEMS_KFV MSIK
6740: WHERE MSIK.CONCATENATED_SEGMENTS = MSII.STYLE_ITEM_NUMBER
6741: AND MSIK.ORGANIZATION_ID = MSII.ORGANIZATION_ID
6752: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, update item_number if inventory_item_id is populated' );
6753: -- if user does know the inventory_item_id, use it to overwrite
6754: -- item_number
6755: -- so in the imported tab, we can join to the child entities by item_number alone
6756: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
6757: SET item_number = (SELECT CONCATENATED_SEGMENTS
6758: FROM MTL_SYSTEM_ITEMS_KFV
6759: WHERE INVENTORY_ITEM_ID = MSII.INVENTORY_ITEM_ID
6760: AND ORGANIZATION_ID = MSII.ORGANIZATION_ID)
6833: -- insert fake records ...
6834: l_insert_date := SYSDATE;
6835: -- ... 1. for attributes (no child orgs)
6836: INSERT INTO
6837: MTL_SYSTEM_ITEMS_INTERFACE MSII
6838: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id
6839: , confirm_status, inventory_item_id, item_number
6840: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
6841: , ITEM_CATALOG_GROUP_ID )
6858: -- AND EIUAI.organization_id = MP.organization_id
6859: -- AND MP.master_organization_id = l_org_id
6860: AND NOT EXISTS
6861: (
6862: select null from mtl_system_items_interface
6863: where set_process_id = EIUAI.data_set_id
6864: and item_number = EIUAI.item_number
6865: and process_flag = EIUAI.process_status
6866: and organization_id = l_org_id
6869: -- using GROUP BY rather than distinct for performance reasons (emulating merge code)
6870: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, parent rows inserted for UDAs: ' || SQL%ROWCOUNT );
6871: -- ... 2. for AMLs (no child orgs)
6872: INSERT INTO
6873: MTL_SYSTEM_ITEMS_INTERFACE MSII
6874: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id
6875: , confirm_status, inventory_item_id, item_number
6876: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
6877: SELECT G_TRANS_TYPE_SYNC, 1, p_data_set_id, l_org_id, l_ss_id
6892: -- AND EAI.organization_id = MP.organization_id
6893: -- AND MP.master_organization_id = l_org_id
6894: AND NOT EXISTS
6895: (
6896: select null from mtl_system_items_interface
6897: where set_process_id = EAI.data_set_id
6898: and item_number = EAI.item_number
6899: and process_flag = EAI.process_flag
6900: and organization_id = l_org_id
6904: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, parent rows inserted for AMLs: ' || SQL%ROWCOUNT );
6905:
6906: --R12C: Inseting Fake Row into MSII for orphan row in EGO_ITEM_ASSOCIATIONS_INTF.
6907: INSERT INTO
6908: MTL_SYSTEM_ITEMS_INTERFACE MSII
6909: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id
6910: , confirm_status, inventory_item_id, item_number
6911: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
6912: SELECT G_TRANS_TYPE_SYNC, 1, p_data_set_id, EIAI.organization_id, l_ss_id
6924: AND process_flag = 1
6925: AND EIAI.organization_id = l_org_id
6926: AND NOT EXISTS
6927: (
6928: select null from mtl_system_items_interface
6929: where set_process_id = EIAI.batch_id
6930: and item_number = EIAI.item_number
6931: and process_flag = EIAI.process_flag
6932: and organization_id = l_org_id
6935: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - PDH batch, parent rows inserted for ASSOCIATIONS: ' || SQL%ROWCOUNT );
6936:
6937: --R12C: Insertion of Fake row into MSII for a orphan row in MTL_ITEM_CATEGORIES_INTERFACE
6938: INSERT INTO
6939: MTL_SYSTEM_ITEMS_INTERFACE MSII
6940: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id
6941: , confirm_status, inventory_item_id, item_number
6942: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
6943: SELECT G_TRANS_TYPE_SYNC, 1, p_data_set_id, MICI.organization_id, l_ss_id
6955: AND process_flag = 1
6956: AND MICI.organization_id = l_org_id
6957: AND NOT EXISTS
6958: (
6959: select null from mtl_system_items_interface
6960: where set_process_id = MICI.set_process_id
6961: and item_number = MICI.item_number
6962: and process_flag = MICI.process_flag
6963: and organization_id = l_org_id
6979:
6980: /* Bug 5283663: CREATE rows can have the FAKE confirm status,
6981: since those rows are moved to process flag 3 in resolve_child_entities;
6982: before IOI has a chance to process them
6983: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
6984: SET CONFIRM_STATUS = NULL
6985: WHERE
6986: set_process_id = p_data_set_id
6987: AND process_flag = 1
7007: -- PDH Item Primary Key Possibilities: inventory_item_id, item_number, segment1, segment2, ... segment20
7008: -- User Directive: transaction_type
7009:
7010: -- Normalize the PROCESS_FLAG if it is null
7011: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
7012: SET PROCESS_FLAG = 0
7013: WHERE SET_PROCESS_ID = p_data_set_id AND
7014: PROCESS_FLAG IS NULL;
7015:
7058: -- insert fake records ...
7059: l_insert_date := SYSDATE;
7060: -- ... 1. for attributes (no child orgs)
7061: INSERT INTO
7062: MTL_SYSTEM_ITEMS_INTERFACE MSII
7063: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference, bundle_id
7064: , confirm_status, inventory_item_id, item_number
7065: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN
7066: , ITEM_CATALOG_GROUP_ID )
7086: AND source_system_id = l_ss_id
7087: AND source_system_reference IS NOT NULL
7088: AND NOT EXISTS
7089: (
7090: select null from mtl_system_items_interface
7091: where set_process_id = EIUAI.data_set_id
7092: and source_system_id = EIUAI.source_system_id
7093: and source_system_reference = EIUAI.source_system_reference
7094: and process_flag = EIUAI.process_status
7098: -- using GROUP BY rather than distinct for performance reasons (no load on merge code)
7099: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, parent rows inserted for UDAs: ' || SQL%ROWCOUNT );
7100: -- ... 2. for AMLs (no child orgs)
7101: INSERT INTO
7102: MTL_SYSTEM_ITEMS_INTERFACE MSII
7103: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference
7104: , source_system_reference_desc, confirm_status, inventory_item_id, item_number
7105: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
7106: SELECT G_TRANS_TYPE_SYNC, 0, p_data_set_id, l_org_id
7125: AND source_system_id = l_ss_id
7126: AND source_system_reference IS NOT NULL
7127: AND NOT EXISTS
7128: (
7129: select null from mtl_system_items_interface
7130: where set_process_id = EAI.data_set_id
7131: and source_system_id = EAI.source_system_id
7132: and source_system_reference = EAI.source_system_reference
7133: and process_flag = EAI.process_flag
7138: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, parent rows inserted for AMLs: ' || SQL%ROWCOUNT );
7139:
7140: --R12C: Inserting Fake Row (Parent Row in MSII) for Orphan row in EGO_ITEM_ASSOCIATIONS_INTF
7141: INSERT INTO
7142: MTL_SYSTEM_ITEMS_INTERFACE MSII
7143: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference, bundle_id
7144: , confirm_status, inventory_item_id, item_number
7145: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
7146: SELECT G_TRANS_TYPE_SYNC, 0, p_data_set_id, l_org_id
7161: AND source_system_id = l_ss_id
7162: AND source_system_reference IS NOT NULL
7163: AND NOT EXISTS
7164: (
7165: select null from mtl_system_items_interface
7166: where set_process_id = EIAI.batch_id
7167: and source_system_id = EIAI.source_system_id
7168: and source_system_reference = EIAI.source_system_reference
7169: and process_flag = EIAI.process_flag
7174:
7175:
7176: --R12C: Inserting Fake Row (Parent Row in MSII) for Orphan row in EGO_ITEM_ASSOCIATIONS_INTF
7177: INSERT INTO
7178: MTL_SYSTEM_ITEMS_INTERFACE MSII
7179: ( transaction_type, process_flag, set_process_id, organization_id, source_system_id, source_system_reference, bundle_id
7180: , confirm_status, inventory_item_id, item_number
7181: , CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_LOGIN )
7182: SELECT G_TRANS_TYPE_SYNC, 0, p_data_set_id, l_org_id
7197: AND source_system_id = l_ss_id
7198: AND source_system_reference IS NOT NULL
7199: AND NOT EXISTS
7200: (
7201: select null from mtl_system_items_interface
7202: where set_process_id = MICI.set_process_id
7203: and source_system_id = MICI.source_system_id
7204: and source_system_reference = MICI.source_system_reference
7205: and process_flag = MICI.process_flag
7209: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, parent rows inserted for CAT Assignments: ' || SQL%ROWCOUNT );
7210: -- R12C: End
7211: -- check for records that need to be marked excluded
7212: UPDATE
7213: MTL_SYSTEM_ITEMS_INTERFACE MSII
7214: SET CONFIRM_STATUS = CASE
7215: WHEN CONFIRM_STATUS IS NULL THEN G_EXCLUDED
7216: ELSE G_FAKE_EXCLUDED
7217: END
7264: -- check for records that need to be marked excluded among the merged records
7265: -- (for freshly merged records, confirm_status is NULL)
7266: -- fix for bug 5329665
7267: UPDATE
7268: MTL_SYSTEM_ITEMS_INTERFACE MSII
7269: SET CONFIRM_STATUS = G_EXCLUDED
7270: WHERE
7271: SET_PROCESS_ID = p_data_set_id
7272: AND CONFIRM_STATUS IS NULL
7328: l_security_predicate := NVL(l_security_predicate, '1=1');
7329:
7330: l_sql := q'#
7331: UPDATE
7332: MTL_SYSTEM_ITEMS_INTERFACE MSII
7333: SET TRANSACTION_TYPE = UPPER( TRANSACTION_TYPE )
7334: , INVENTORY_ITEM_ID =
7335: (SELECT INVENTORY_ITEM_ID
7336: FROM MTL_CROSS_REFERENCES_B XREF
7361: -- the revision import policy should not apply if the user has given us a
7362: -- rev code or id in either EGO_ITM_USR_ATTR_INTRFC or MTL_ITEM_REVISIONS_INTERFACE: We set it to 'S' meaning specific.
7363: -- however, if the rev import policy is set to Specific already, we need to verify that
7364: -- the user has given us a rev code or id in either EGO_ITM_USR_ATTR_INTRFC or MTL_ITEM_REVISIONS_INTERFACE.
7365: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
7366: SET REVISION_IMPORT_POLICY =
7367: CASE
7368: WHEN EXISTS -- check the revision interface table
7369: (
7413: -- US (unconfirmed single match), UM (unconfirmed multiple match), UN (unconfirmed no match), EX (excluded), null.
7414: -- See spec (EGOVIMPS.pls) constants for list of confirm status values.
7415: -- Bug: 5458886 - If batch is cross references only batch, then New Item should not be allowed
7416: UPDATE -- for non-fake rows
7417: MTL_SYSTEM_ITEMS_INTERFACE MSII
7418: SET
7419: CONFIRM_STATUS =
7420: CASE
7421: WHEN UPPER( TRANSACTION_TYPE ) = G_TRANS_TYPE_CREATE AND l_import_xref_only = 'N' THEN G_CONF_NEW
7429: ORGANIZATION_ID = l_org_id AND
7430: CONFIRM_STATUS IS NULL;
7431:
7432: UPDATE -- for fake rows
7433: MTL_SYSTEM_ITEMS_INTERFACE MSII
7434: SET
7435: CONFIRM_STATUS =
7436: CASE
7437: WHEN UPPER( TRANSACTION_TYPE ) = G_TRANS_TYPE_CREATE AND l_import_xref_only = 'N' THEN G_CONF_NEW
7446: CONFIRM_STATUS = G_FAKE_CONF_STATUS_FLAG;
7447:
7448: -- update inventory_item_id to make sure their existence makes sense
7449: UPDATE
7450: MTL_SYSTEM_ITEMS_INTERFACE MSII
7451: SET
7452: INVENTORY_ITEM_ID =
7453: CASE
7454: WHEN CONFIRM_STATUS IN ( G_CONF_XREF, G_CONF_MATCH, G_UNCONF_SIGL_MATCH, G_UNCONF_MULT_MATCH
7465: Debug_Conc_Log( 'Resolve_SSXref_on_Data_load - Source System batch, done resolving confirm status' );
7466: -- for all records where item_catalog_group_name is present and item_catalog_group_id is not present
7467: -- update the item_catalog_group_id
7468: UPDATE
7469: MTL_SYSTEM_ITEMS_INTERFACE MSII
7470: SET ITEM_CATALOG_GROUP_ID = (SELECT MICG.ITEM_CATALOG_GROUP_ID
7471: FROM MTL_ITEM_CATALOG_GROUPS_B_KFV MICG
7472: WHERE MICG.CONCATENATED_SEGMENTS = MSII.ITEM_CATALOG_GROUP_NAME)
7473: WHERE MSII.SET_PROCESS_ID = p_data_set_id
7983: INVENTORY_ITEM_ID,
7984: ORGANIZATION_ID,
7985: TRANSACTION_TYPE,
7986: TRANSACTION_ID
7987: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
7988: WHERE SET_PROCESS_ID = p_data_set_id
7989: AND PROCESS_FLAG = 33390;
7990:
7991:
7995: MSII.ORGANIZATION_CODE,
7996: MSII.ORGANIZATION_ID,
7997: MSII.TRANSACTION_ID
7998: FROM MTL_SYSTEM_ITEMS_B_KFV KFV,
7999: MTL_SYSTEM_ITEMS_INTERFACE MSII,
8000: MTL_CROSS_REFERENCES MCR
8001: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8002: AND MCR.CROSS_REFERENCE_TYPE = 'SS_ITEM_XREF'
8003: AND MSII.SOURCE_SYSTEM_ID = MCR.SOURCE_SYSTEM_ID
8064: -- no need to resolve child entities for a Non-PDH batch, if Import Only Cross References is true
8065: IF l_ss_id = get_pdh_source_system_id() THEN
8066: -- move fake rows to have the success process flag value
8067: -- so that they are not processed by IOI
8068: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
8069: SET
8070: REQUEST_ID = l_request_id
8071: , PROGRAM_APPLICATION_ID = l_prog_appid
8072: , PROGRAM_ID = l_prog_id
8090: p_commit => FND_API.G_FALSE );
8091: Debug_Conc_Log('Resolve_Child_Entities - Clean_Up_UDA_Row_Idents Done.');
8092:
8093: Debug_Conc_Log('Resolve_Child_Entities - Resolving Style_Item_Flag');
8094: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
8095: SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID, INVENTORY_ITEM_ID) = (SELECT
8096: DECODE(MAX(NVL(msik.STYLE_ITEM_FLAG, '$NULL$')),
8097: '$NULL$', msii.STYLE_ITEM_FLAG,
8098: NULL, msii.STYLE_ITEM_FLAG,
8147:
8148: -- CC/CM:
8149: -- old link and new link are the same:
8150: l_sql := q'#
8151: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8152: SET PROCESS_FLAG = 33390
8153: WHERE SET_PROCESS_ID = :p_data_set_id
8154: AND PROCESS_FLAG = 0
8155: AND ORGANIZATION_ID = :org_id
8172: source system reference desc is being updated');
8173: -- old link and new link are not the same:
8174: -- check edit privilege on old item link (if any)
8175: l_sql := q'#
8176: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8177: SET PROCESS_FLAG = 33391
8178: WHERE SET_PROCESS_ID = :p_data_set_id
8179: AND PROCESS_FLAG = 0
8180: AND ORGANIZATION_ID = :org_id
8193: 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' );
8194: -- old link and new link are not the same:
8195: -- check edit privilege on new item link
8196: l_sql := q'#
8197: UPDATE MTL_SYSTEM_ITEMS_INTERFACE APPLYSEC
8198: SET PROCESS_FLAG = 33390
8199: WHERE SET_PROCESS_ID = :p_data_set_id
8200: AND PROCESS_FLAG = 0
8201: AND ORGANIZATION_ID = :org_id
8229: ,l_request_id
8230: ,i.TRANSACTION_ID
8231: ,l_msg_text
8232: ,'SOURCE_SYSTEM_REFERENCE'
8233: ,'MTL_SYSTEM_ITEMS_INTERFACE'
8234: ,'INV_IOI_ERR_NO_ED_XREF_PRIV_2'--bug 11894684, replace INV_IOI_ERR
8235: ,l_err_text);
8236:
8237: END LOOP;
8251: ,l_request_id
8252: ,i.TRANSACTION_ID
8253: ,l_msg_text
8254: ,'SOURCE_SYSTEM_REFERENCE'
8255: ,'MTL_SYSTEM_ITEMS_INTERFACE'
8256: ,'INV_IOI_ERR_NO_ED_XREF_PRIV_3'--bug 11894684, replace INV_IOI_ERR
8257: ,l_err_text);
8258: END LOOP;
8259:
8258: END LOOP;
8259:
8260: Debug_Conc_Log( 'Resolve_Child_Entities - After logging errors for records without privilege' );
8261:
8262: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
8263: SET PROCESS_FLAG = 3
8264: WHERE SET_PROCESS_ID = p_data_set_id
8265: AND PROCESS_FLAG IN (33390, 33391)
8266: AND CONFIRM_STATUS IN ( 'CM', 'CC', 'CN', G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE )
8292: AND MIR.REVISION = MIRI.REVISION) )
8293: THEN 'UPDATE'
8294: ELSE 'CREATE'
8295: END)
8296: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8297: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8298: AND MSII.PROCESS_FLAG = 0
8299: AND MSII.CONFIRM_STATUS IN ('CN', 'CM', 'CC', G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE)
8300: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8304: )
8305: WHERE MIRI.PROCESS_FLAG = 0
8306: AND MIRI.SET_PROCESS_ID = p_data_set_id
8307: AND EXISTS (SELECT NULL
8308: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8309: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8310: AND MSII.PROCESS_FLAG = 0
8311: AND MSII.CONFIRM_STATUS IN ( 'CN', 'CM', 'CC', G_CONF_XREF_FAKE, G_CONF_MATCH_FAKE )
8312: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8316: Debug_Conc_Log('Resolve_Child_Entities - After resolving transaction_type for revisions' );
8317:
8318: -- update the confirm status to ready for all READY CN records
8319: UPDATE
8320: MTL_SYSTEM_ITEMS_INTERFACE MSII
8321: SET CONFIRM_STATUS = (
8322: CASE
8323: WHEN (MSII.DESCRIPTION IS NULL
8324: AND NOT EXISTS
8380: -- We are not honoring user entered item number values from R12C
8381: -- so the overwriting of item number must happen only from IOI
8382: /*
8383: UPDATE
8384: MTL_SYSTEM_ITEMS_INTERFACE MSII
8385: SET ITEM_NUMBER = EGO_IMPORT_PVT.GET_NEXT_ITEM_NUMBER(MSII.ITEM_CATALOG_GROUP_ID)
8386: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
8387: AND MSII.ITEM_NUMBER IS NULL
8388: AND MSII.SET_PROCESS_ID = p_data_set_id
8400: Debug_Conc_Log('Resolve_Child_Entities - After updating Sequence Generated Item Numbers' );
8401: */
8402: -- update all records with confirm_status='CC', 'CM' to ready, where conditions are satisfied
8403: UPDATE
8404: MTL_SYSTEM_ITEMS_INTERFACE MSII
8405: SET CONFIRM_STATUS = (
8406: CASE
8407: WHEN
8408: (NVL(MSII.REVISION_IMPORT_POLICY, l_import_policy) = 'N' AND
8425: AND MSII.ORGANIZATION_ID = l_org_id
8426: AND MSII.CONFIRM_STATUS IN ('CC', 'CM');
8427:
8428: UPDATE
8429: MTL_SYSTEM_ITEMS_INTERFACE MSII
8430: SET CONFIRM_STATUS = (
8431: CASE
8432: WHEN ( NVL(MSII.REVISION_IMPORT_POLICY, l_import_policy) = 'N' AND
8433: 1 <> (SELECT COUNT(*)
8456: Debug_Conc_Log('Resolve_Child_Entities - Need to check privilege' );
8457: -- For the confirmed new case, if there exists an active cross reference which you cannot break
8458: -- update process_flag to 33391
8459: l_sql := q'#
8460: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8461: SET PROCESS_FLAG = 33391
8462: WHERE SET_PROCESS_ID = :p_data_set_id
8463: AND PROCESS_FLAG = 0
8464: AND ORGANIZATION_ID = :org_id
8475: Debug_Conc_Log('Resolve_Child_Entities - After checking for CNR' );
8476: -- CC/CM:
8477: -- old link and new link are the same:
8478: l_sql := q'#
8479: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8480: SET PROCESS_FLAG = 33390
8481: WHERE SET_PROCESS_ID = :p_data_set_id
8482: AND PROCESS_FLAG = 0
8483: AND ORGANIZATION_ID = :org_id
8500: source system reference desc is being updated' );
8501: -- old link and new link are not the same:
8502: -- check edit privilege on old item link (if any)
8503: l_sql := q'#
8504: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8505: SET PROCESS_FLAG = 33391
8506: WHERE SET_PROCESS_ID = :p_data_set_id
8507: AND PROCESS_FLAG = 0
8508: AND ORGANIZATION_ID = :org_id
8522:
8523: -- old link and new link are not the same:
8524: -- check edit privilege on new item link
8525: l_sql := q'#
8526: UPDATE MTL_SYSTEM_ITEMS_INTERFACE APPLYSEC
8527: SET PROCESS_FLAG = 33390
8528: WHERE SET_PROCESS_ID = :p_data_set_id
8529: AND PROCESS_FLAG = 0
8530: AND ORGANIZATION_ID = :org_id
8562: ,l_request_id
8563: ,i.TRANSACTION_ID
8564: ,l_msg_text
8565: ,'SOURCE_SYSTEM_REFERENCE'
8566: ,'MTL_SYSTEM_ITEMS_INTERFACE'
8567: ,'INV_IOI_ERR_NO_ED_XREF_PRIV_4'--bug 11894684, replace INV_IOI_ERR
8568: ,l_err_text);
8569: END LOOP;
8570:
8583: ,l_request_id
8584: ,i.TRANSACTION_ID
8585: ,l_msg_text
8586: ,'SOURCE_SYSTEM_REFERENCE'
8587: ,'MTL_SYSTEM_ITEMS_INTERFACE'
8588: ,'INV_IOI_ERR_NO_ED_XREF_PRIV_5'--bug 11894684, replace INV_IOI_ERR
8589: ,l_err_text);
8590: END LOOP;
8591:
8592: Debug_Conc_Log('Resolve_Child_Entities - After logging errors for no privilege' );
8593: END IF; --IF l_security_predicate IS NOT NULL THEN
8594:
8595: -- ELETUCHY XXX: seems wasteful to overwrite transaction type to sync rather than update, no?
8596: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
8597: SET PROCESS_FLAG = 1
8598: , TRANSACTION_TYPE = CASE
8599: WHEN CONFIRM_STATUS = 'CNR' THEN G_TRANS_TYPE_CREATE
8600: ELSE G_TRANS_TYPE_UPDATE
8613: AND CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR' )
8614: AND ORGANIZATION_ID = l_org_id;
8615:
8616: -- ELETUCHY: these fake rows will never be processed by IOI, so putting trans_type to UPDATE is reasonable
8617: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
8618: SET PROCESS_FLAG = 1,
8619: TRANSACTION_TYPE = G_TRANS_TYPE_UPDATE,
8620: CONFIRM_STATUS = G_FAKE_CONF_STATUS_FLAG
8621: WHERE SET_PROCESS_ID = p_data_set_id
8626: Debug_Conc_Log('Resolve_Child_Entities - Done with master item rows' );
8627:
8628: -- updating item_number, item_id from MSII table
8629: -- for child items
8630: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
8631: SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID, ITEM_NUMBER, ITEM_CATALOG_GROUP_ID, ITEM_CATALOG_GROUP_NAME, STYLE_ITEM_FLAG, STYLE_ITEM_ID ) =
8632: (SELECT
8633: 1,
8634: (CASE
8641: MSII2.ITEM_CATALOG_GROUP_ID,
8642: MSII2.ITEM_CATALOG_GROUP_NAME,
8643: NVL(MSII.STYLE_ITEM_FLAG, MSII2.STYLE_ITEM_FLAG),
8644: NVL(MSII.STYLE_ITEM_ID, MSII2.STYLE_ITEM_ID)
8645: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
8646: WHERE MSII2.SET_PROCESS_ID = p_data_set_id
8647: AND MSII2.PROCESS_FLAG = 1
8648: AND MSII2.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
8649: AND MSII2.SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
8659: AND mp.MASTER_ORGANIZATION_ID = l_org_id
8660: AND mp.MASTER_ORGANIZATION_ID <> mp.ORGANIZATION_ID
8661: )
8662: AND EXISTS (SELECT NULL
8663: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
8664: WHERE MSII2.SET_PROCESS_ID = p_data_set_id
8665: AND MSII2.PROCESS_FLAG = 1
8666: AND MSII2.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8667: AND MSII2.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
8689: ELSE 'CREATE'
8690: END),
8691: MSII.INVENTORY_ITEM_ID,
8692: MSII.ITEM_NUMBER
8693: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8694: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8695: AND MSII.PROCESS_FLAG = 1
8696: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8697: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8701: )
8702: WHERE MIRI.PROCESS_FLAG = 0
8703: AND MIRI.SET_PROCESS_ID = p_data_set_id
8704: AND EXISTS (SELECT NULL
8705: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8706: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8707: AND MSII.PROCESS_FLAG = 1
8708: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8709: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8722: ELSE MICI.TRANSACTION_TYPE
8723: END),
8724: MSII.INVENTORY_ITEM_ID,
8725: MSII.ITEM_NUMBER
8726: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8727: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8728: AND MSII.PROCESS_FLAG = 1
8729: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8730: AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
8734: )
8735: WHERE MICI.PROCESS_FLAG = 0
8736: AND MICI.SET_PROCESS_ID = p_data_set_id
8737: AND EXISTS (SELECT NULL
8738: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8739: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8740: AND MSII.PROCESS_FLAG = 1
8741: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8742: AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
8756: END),
8757: MSII.INVENTORY_ITEM_ID,
8758: MSII.ITEM_NUMBER,
8759: NVL(ATTRS.ITEM_CATALOG_GROUP_ID, MSII.ITEM_CATALOG_GROUP_ID)
8760: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8761: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8762: AND MSII.PROCESS_FLAG = 1
8763: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8764: AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
8768: )
8769: WHERE ATTRS.PROCESS_STATUS = 0
8770: AND ATTRS.DATA_SET_ID = p_data_set_id
8771: AND EXISTS (SELECT NULL
8772: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8773: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8774: AND MSII.PROCESS_FLAG = 1
8775: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8776: AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
8791: THEN '2'
8792: WHEN MSII.CONFIRM_STATUS = 'CNR'
8793: THEN '3'
8794: END
8795: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8796: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8797: AND MSII.PROCESS_FLAG = 1
8798: AND MSII.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8799: AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
8826: WHERE ATTRS.PROCESS_STATUS = 1
8827: AND ATTRS.DATA_SET_ID = p_data_set_id
8828: AND ATTRS.REVISION IS NULL
8829: AND EXISTS (SELECT NULL
8830: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8831: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8832: AND MSII.PROCESS_FLAG = 1
8833: AND MSII.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8834: AND MSII.SOURCE_SYSTEM_ID = ATTRS.SOURCE_SYSTEM_ID
8848: SET (REVISION, TRANSACTION_TYPE) =
8849: (SELECT
8850: (CASE
8851: WHEN (EXISTS (SELECT NULL
8852: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8853: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8854: AND MSII.PROCESS_FLAG = 1
8855: AND MSII.CONFIRM_STATUS IN ('CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8856: AND (MSII.REVISION_IMPORT_POLICY = 'L' OR (MSII.REVISION_IMPORT_POLICY IS NULL AND l_import_policy = 'L'))
8862: FROM MTL_ITEM_REVISIONS_B
8863: WHERE INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID
8864: AND ORGANIZATION_ID = MIRI.ORGANIZATION_ID)
8865: WHEN (EXISTS (SELECT NULL
8866: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8867: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8868: AND MSII.PROCESS_FLAG = 1
8869: AND MSII.CONFIRM_STATUS = 'CNR'
8870: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8881: WHERE MIRI.PROCESS_FLAG = 1
8882: AND MIRI.SET_PROCESS_ID = p_data_set_id
8883: AND MIRI.REVISION IS NULL
8884: AND EXISTS (SELECT NULL
8885: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8886: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8887: AND MSII.PROCESS_FLAG = 1
8888: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8889: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
8902: ELSE 'SYNC'
8903: END),
8904: MSII.INVENTORY_ITEM_ID,
8905: MSII.ITEM_NUMBER
8906: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8907: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8908: AND MSII.PROCESS_FLAG = 1
8909: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8910: AND MSII.SOURCE_SYSTEM_ID = EIPI.SOURCE_SYSTEM_ID
8914: )
8915: WHERE EIPI.PROCESS_STATUS = 0
8916: AND EIPI.DATA_SET_ID = p_data_set_id
8917: AND EXISTS (SELECT NULL
8918: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8919: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8920: AND MSII.PROCESS_FLAG = 1
8921: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8922: AND MSII.SOURCE_SYSTEM_ID = EIPI.SOURCE_SYSTEM_ID
8935: ELSE G_TRANS_TYPE_SYNC
8936: END),
8937: MSII.INVENTORY_ITEM_ID,
8938: MSII.ITEM_NUMBER
8939: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8940: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8941: AND MSII.PROCESS_FLAG = 1
8942: AND MSII.CONFIRM_STATUS IN ( 'CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG )
8943: AND MSII.SOURCE_SYSTEM_ID = EAI.SOURCE_SYSTEM_ID
8947: )
8948: WHERE EAI.PROCESS_FLAG = 0
8949: AND EAI.DATA_SET_ID = p_data_set_id
8950: AND EXISTS (SELECT NULL
8951: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8952: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8953: AND MSII.PROCESS_FLAG = 1
8954: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8955: AND MSII.SOURCE_SYSTEM_ID = EAI.SOURCE_SYSTEM_ID
8969: ELSE EIAI.TRANSACTION_TYPE
8970: END),
8971: MSII.INVENTORY_ITEM_ID,
8972: MSII.ITEM_NUMBER
8973: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8974: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8975: AND MSII.PROCESS_FLAG = 1
8976: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8977: AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
8981: )
8982: WHERE EIAI.PROCESS_FLAG = 0
8983: AND EIAI.BATCH_ID = p_data_set_id
8984: AND EXISTS (SELECT NULL
8985: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
8986: WHERE MSII.SET_PROCESS_ID = p_data_set_id
8987: AND MSII.PROCESS_FLAG = 1
8988: AND MSII.CONFIRM_STATUS IN ('CNR', 'CMR', 'CCR', G_FAKE_CONF_STATUS_FLAG)
8989: AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
9011: END IF;
9012:
9013: Debug_Conc_Log('Resolve_Child_Entities - Security Predicate - '||l_security_predicate);
9014:
9015: -- updating all the child entities, which do not have parent in mtl_system_items_interface
9016: -- updating the items and org assignments
9017:
9018: -- Note that using q'# is a new feature of 10g. By using this syntax, we can put as many single quotes within
9019: -- the string. for example q'# This is Devendra's code #'
9017:
9018: -- Note that using q'# is a new feature of 10g. By using this syntax, we can put as many single quotes within
9019: -- the string. for example q'# This is Devendra's code #'
9020: l_sql := q'#
9021: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9022: SET (PROCESS_FLAG, TRANSACTION_TYPE, INVENTORY_ITEM_ID) =
9023: (SELECT
9024: 1,
9025: NVL(MSII.TRANSACTION_TYPE, 'SYNC'),
9040: AND MP.ORGANIZATION_ID <> MP.MASTER_ORGANIZATION_ID
9041: AND MP.ORGANIZATION_ID = MSII.ORGANIZATION_ID)
9042: -- CHILD MUST NOT HAVE A MASTER RECORD IN CURRENT BATCH
9043: AND NOT EXISTS (SELECT NULL
9044: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII2
9045: WHERE MSII2.PROCESS_FLAG in (0, 1, 33390,33391)
9046: AND MSII2.SET_PROCESS_ID = :p_data_set_id1
9047: AND MSII2.SOURCE_SYSTEM_ID = MSII.SOURCE_SYSTEM_ID
9048: AND MSII2.SOURCE_SYSTEM_REFERENCE = MSII.SOURCE_SYSTEM_REFERENCE
9080: WHERE MIRI.SET_PROCESS_ID = :p_data_set_id
9081: AND MIRI.PROCESS_FLAG = 0
9082: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9083: AND NOT EXISTS (SELECT NULL
9084: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9085: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
9086: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9087: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
9088: AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
9116: AND MIRI.PROCESS_FLAG = 1
9117: AND MIRI.REVISION IS NULL
9118: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9119: AND NOT EXISTS (SELECT NULL
9120: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9121: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
9122: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9123: AND MSII.SOURCE_SYSTEM_ID = MIRI.SOURCE_SYSTEM_ID
9124: AND MSII.SOURCE_SYSTEM_REFERENCE = MIRI.SOURCE_SYSTEM_REFERENCE
9158: WHERE MICI.SET_PROCESS_ID = :p_data_set_id
9159: AND MICI.PROCESS_FLAG = 0
9160: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9161: AND NOT EXISTS (SELECT NULL
9162: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9163: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
9164: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9165: AND MSII.SOURCE_SYSTEM_ID = MICI.SOURCE_SYSTEM_ID
9166: AND MSII.SOURCE_SYSTEM_REFERENCE = MICI.SOURCE_SYSTEM_REFERENCE
9199: WHERE EIPI.DATA_SET_ID = :p_data_set_id
9200: AND EIPI.PROCESS_STATUS = 0
9201: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9202: AND NOT EXISTS (SELECT NULL
9203: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9204: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
9205: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9206: AND MSII.SOURCE_SYSTEM_ID = EIPI.SOURCE_SYSTEM_ID
9207: AND MSII.SOURCE_SYSTEM_REFERENCE = EIPI.SOURCE_SYSTEM_REFERENCE
9240: WHERE EIUAI.DATA_SET_ID = :p_data_set_id
9241: AND EIUAI.PROCESS_STATUS = 0
9242: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9243: AND NOT EXISTS (SELECT NULL
9244: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9245: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390, 33391)
9246: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9247: AND MSII.SOURCE_SYSTEM_ID = EIUAI.SOURCE_SYSTEM_ID
9248: AND MSII.SOURCE_SYSTEM_REFERENCE = EIUAI.SOURCE_SYSTEM_REFERENCE
9281: WHERE EAI.DATA_SET_ID = :p_data_set_id
9282: AND EAI.PROCESS_FLAG = 0
9283: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9284: AND NOT EXISTS (SELECT NULL
9285: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9286: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390, 33391)
9287: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9288: AND MSII.SOURCE_SYSTEM_ID = EAI.SOURCE_SYSTEM_ID
9289: AND MSII.SOURCE_SYSTEM_REFERENCE = EAI.SOURCE_SYSTEM_REFERENCE
9322: WHERE EIAI.BATCH_ID = :p_data_set_id
9323: AND EIAI.PROCESS_FLAG = 0
9324: -- MUST NOT HAVE A MASTER ITEM RECORD IN CURRENT BATCH
9325: AND NOT EXISTS (SELECT NULL
9326: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
9327: WHERE MSII.PROCESS_FLAG IN (0, 1, 33390,33391)
9328: AND MSII.SET_PROCESS_ID = :p_data_set_id1
9329: AND MSII.SOURCE_SYSTEM_ID = EIAI.SOURCE_SYSTEM_ID
9330: AND MSII.SOURCE_SYSTEM_REFERENCE = EIAI.SOURCE_SYSTEM_REFERENCE
9343: EXECUTE IMMEDIATE l_sql USING p_data_set_id, p_data_set_id, l_org_id;
9344:
9345: Debug_Conc_Log('Resolve_Child_Entities - After updating intersections intf table for items that has XXref');
9346:
9347: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9348: SET REQUEST_ID = CASE PROCESS_FLAG
9349: WHEN 33390 THEN l_request_id
9350: WHEN 33391 THEN l_request_id
9351: ELSE REQUEST_ID
9367: * import because that bulk-loader looks for process flag 7 with the request id of the calling request.
9368: * Since both that code (Process_SSXref_Intf_Rows) and this chunk are executed within the auspices of
9369: * the same concurrent request, the cross-ref import will work correctly.
9370: */
9371: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9372: SET REQUEST_ID = l_request_id
9373: , PROCESS_FLAG = CASE PROCESS_FLAG
9374: WHEN 33390 THEN 3
9375: WHEN 33391 THEN 3
9397: Debug_Conc_Log('Resolve_Child_Entities - Not calling Merge_Batch_For_Import, because this batch is enabled for data pool');
9398: END IF;
9399:
9400: Debug_Conc_Log('Resolve_Child_Entities - Resolving Style_Item_Flag');
9401: UPDATE MTL_SYSTEM_ITEMS_INTERFACE msii
9402: SET (STYLE_ITEM_FLAG, STYLE_ITEM_ID) = (SELECT
9403: DECODE(MAX(NVL(msik.STYLE_ITEM_FLAG, '$NULL$')),
9404: '$NULL$', msii.STYLE_ITEM_FLAG,
9405: NULL, msii.STYLE_ITEM_FLAG,
9425: END IF;
9426:
9427: END Resolve_Child_Entities;
9428:
9429: PROCEDURE Stamp_Row_RequestId( p_request_id IN MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE
9430: , p_target_rowid IN UROWID
9431: )
9432: IS
9433: BEGIN
9434: IF p_request_id IS NULL THEN RETURN; END IF;
9435:
9436: SAVEPOINT do_stamp_row;
9437:
9438: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9439: SET REQUEST_ID = p_request_id
9440: WHERE MSII.ROWID = p_target_rowid
9441: AND PROCESS_FLAG = 7
9442: AND ( SOURCE_SYSTEM_ID IS NOT NULL
9452: RETURN;
9453: END;
9454:
9455:
9456: PROCEDURE Stamp_RequestId_For_ReImport( p_request_id IN MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE )
9457: IS
9458: l_index PLS_INTEGER;
9459: BEGIN
9460: IF G_MSII_REIMPORT_ROWS IS NOT NULL THEN
9459: BEGIN
9460: IF G_MSII_REIMPORT_ROWS IS NOT NULL THEN
9461: l_index := G_MSII_REIMPORT_ROWS.FIRST;
9462: WHILE l_index IS NOT NULL LOOP
9463: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9464: SET REQUEST_ID = p_request_id
9465: WHERE ROWID = G_MSII_REIMPORT_ROWS( l_index );
9466: l_index := G_MSII_REIMPORT_ROWS.next( l_index );
9467: END LOOP;
9468: G_MSII_REIMPORT_ROWS := NULL;
9469: END IF;
9470: END;
9471:
9472: PROCEDURE Log_Error_For_ReImport(p_request_id IN MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE
9473: , p_target_rowid IN UROWID
9474: , p_err_msg IN VARCHAR2
9475: )
9476: IS
9482: l_prog_appid NUMBER := FND_GLOBAL.PROG_APPL_ID;
9483: l_prog_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
9484: l_err_text VARCHAR2(4000);
9485: BEGIN
9486: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9487: SET REQUEST_ID = p_request_id
9488: WHERE ROWID = p_target_rowid
9489: RETURNING ORGANIZATION_ID, TRANSACTION_ID
9490: INTO l_org_id, l_transaction_id;
9498: ,p_request_id
9499: ,l_transaction_id
9500: ,p_err_msg
9501: ,'GLOBAL_TRADE_ITEM_NUMBER'
9502: ,'MTL_SYSTEM_ITEMS_INTERFACE'
9503: ,'INV_IOI_ERR_FAIL4REIMPORT'--bug 11894684, replace INV_IOI_ERR
9504: ,l_err_text);
9505: EXCEPTION WHEN OTHERS THEN
9506: NULL;
9506: NULL;
9507: END Log_Error_For_ReImport;
9508:
9509: PROCEDURE Prepare_Row_For_ReImport
9510: ( p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
9511: , p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE
9512: , p_target_rowid IN UROWID
9513: , x_return_code OUT NOCOPY NUMBER
9514: , x_err_msg OUT NOCOPY VARCHAR2
9507: END Log_Error_For_ReImport;
9508:
9509: PROCEDURE Prepare_Row_For_ReImport
9510: ( p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
9511: , p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE
9512: , p_target_rowid IN UROWID
9513: , x_return_code OUT NOCOPY NUMBER
9514: , x_err_msg OUT NOCOPY VARCHAR2
9515: )
9513: , x_return_code OUT NOCOPY NUMBER
9514: , x_err_msg OUT NOCOPY VARCHAR2
9515: )
9516: IS
9517: l_process_flag MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9518: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
9519: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9520: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9521: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9514: , x_err_msg OUT NOCOPY VARCHAR2
9515: )
9516: IS
9517: l_process_flag MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9518: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
9519: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9520: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9521: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9522: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9515: )
9516: IS
9517: l_process_flag MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9518: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
9519: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9520: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9521: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9522: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9523: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9516: IS
9517: l_process_flag MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9518: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
9519: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9520: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9521: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9522: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9523: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9524: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9518: l_ssr MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE;
9519: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9520: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9521: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9522: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9523: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9524: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9525: l_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9526: l_ssxref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
9519: l_ss_id MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE;
9520: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9521: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9522: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9523: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9524: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9525: l_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9526: l_ssxref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
9527:
9520: l_ssr_desc MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE_DESC%TYPE;
9521: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9522: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9523: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9524: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9525: l_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9526: l_ssxref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
9527:
9528: l_is_pdh_batch BOOLEAN;
9521: l_batch_ss_id EGO_IMPORT_BATCHES_B.SOURCE_SYSTEM_ID%TYPE;
9522: l_request_id MTL_SYSTEM_ITEMS_INTERFACE.REQUEST_ID%TYPE;
9523: l_org_id MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE;
9524: l_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9525: l_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9526: l_ssxref_only EGO_IMPORT_OPTION_SETS.IMPORT_XREF_ONLY%TYPE;
9527:
9528: l_is_pdh_batch BOOLEAN;
9529: l_is_reimport BOOLEAN;
9527:
9528: l_is_pdh_batch BOOLEAN;
9529: l_is_reimport BOOLEAN;
9530: l_is_ssxref_only BOOLEAN;
9531: l_reimport_process_status MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9532: l_priv_exists VARCHAR2(1);
9533: l_msg_text VARCHAR2(4000);
9534: l_xref_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9535: l_xref_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9530: l_is_ssxref_only BOOLEAN;
9531: l_reimport_process_status MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9532: l_priv_exists VARCHAR2(1);
9533: l_msg_text VARCHAR2(4000);
9534: l_xref_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9535: l_xref_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9536: l_xref_desc MTL_CROSS_REFERENCES.DESCRIPTION%TYPE;
9537: l_org_code MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_CODE%TYPE;
9538: l_party_name VARCHAR2(1000); -- Bug: 5355759
9531: l_reimport_process_status MTL_SYSTEM_ITEMS_INTERFACE.PROCESS_FLAG%TYPE;
9532: l_priv_exists VARCHAR2(1);
9533: l_msg_text VARCHAR2(4000);
9534: l_xref_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9535: l_xref_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9536: l_xref_desc MTL_CROSS_REFERENCES.DESCRIPTION%TYPE;
9537: l_org_code MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_CODE%TYPE;
9538: l_party_name VARCHAR2(1000); -- Bug: 5355759
9539: BEGIN
9533: l_msg_text VARCHAR2(4000);
9534: l_xref_item_number MTL_SYSTEM_ITEMS_INTERFACE.ITEM_NUMBER%TYPE;
9535: l_xref_item_id MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE;
9536: l_xref_desc MTL_CROSS_REFERENCES.DESCRIPTION%TYPE;
9537: l_org_code MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_CODE%TYPE;
9538: l_party_name VARCHAR2(1000); -- Bug: 5355759
9539: BEGIN
9540: x_return_code := 0;
9541: BEGIN -- CHECK THAT BATCH HAS A HEADER
9563: SAVEPOINT do_prepare_master_row; -- ENSURES THAT THE UPDATES ARE AN ATOMIC UNIT WITHIN THE TRANSACTION
9564: -- XXX: Should child updates be attempted first?
9565:
9566: -- update/select master item row
9567: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9568: SET PROCESS_FLAG = CASE PROCESS_FLAG
9569: WHEN 3 THEN 1
9570: -- WHEN 7 THEN G_IOI_STAMP_REQUEST_ID_FLAG
9571: ELSE PROCESS_FLAG END
9610: IF l_item_id IS NOT NULL THEN
9611: -- xxx: should the item number to use for comparison be fetched from the msii kfv?
9612:
9613: -- updating the item's org assignments
9614: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9615: SET PROCESS_FLAG = 1
9616: WHERE ( INVENTORY_ITEM_ID = l_item_id
9617: OR ( ITEM_NUMBER = l_item_number
9618: AND INVENTORY_ITEM_ID IS NULL
9729: );
9730: l_is_reimport := l_is_reimport OR ( 0 <> SQL%ROWCOUNT );
9731: ELSE -- item id is null
9732: -- updating the item's org assignments
9733: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9734: SET PROCESS_FLAG = 1
9735: WHERE ITEM_NUMBER = l_item_number
9736: AND PROCESS_FLAG = 3
9737: AND SET_PROCESS_ID = p_batch_id
9835: l_reimport_process_status := 1;
9836: END IF;
9837:
9838: -- update/select master item row
9839: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9840: SET PROCESS_FLAG = CASE PROCESS_FLAG
9841: WHEN 3 THEN l_reimport_process_status
9842: -- WHEN 7 THEN G_IOI_STAMP_REQUEST_ID_FLAG
9843: ELSE PROCESS_FLAG END
9919: FND_MESSAGE.SET_NAME('EGO', 'EGO_NO_EDIT_XREF_PRIV');
9920: FND_MESSAGE.SET_TOKEN('ITEM', l_xref_item_number);
9921: FND_MESSAGE.SET_TOKEN('ORG', l_org_code);
9922: l_msg_text := FND_MESSAGE.GET;
9923: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9924: SET PROCESS_FLAG = 3
9925: WHERE ROWID = p_target_rowid;
9926: x_return_code := G_NEED_TO_LOG_ERROR;
9927: x_err_msg := l_msg_text;
9935: FND_MESSAGE.SET_TOKEN('ITEM', l_item_number);
9936: FND_MESSAGE.SET_TOKEN('ORG', l_org_code);
9937: l_msg_text := FND_MESSAGE.GET;
9938:
9939: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9940: SET PROCESS_FLAG = 3
9941: WHERE ROWID = p_target_rowid;
9942:
9943: x_return_code := G_NEED_TO_LOG_ERROR;
9954: FND_MESSAGE.SET_TOKEN('ITEM', l_item_number);
9955: FND_MESSAGE.SET_TOKEN('ORG', l_org_code);
9956: l_msg_text := FND_MESSAGE.GET;
9957:
9958: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9959: SET PROCESS_FLAG = 3
9960: WHERE ROWID = p_target_rowid;
9961:
9962: x_return_code := G_NEED_TO_LOG_ERROR;
9974: FND_MESSAGE.SET_TOKEN('ITEM', l_item_number);
9975: FND_MESSAGE.SET_TOKEN('ORG', l_org_code);
9976: l_msg_text := FND_MESSAGE.GET;
9977:
9978: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
9979: SET PROCESS_FLAG = 3
9980: WHERE ROWID = p_target_rowid;
9981:
9982: x_return_code := G_NEED_TO_LOG_ERROR;
9987: END IF;
9988:
9989: IF NOT l_is_ssxref_only THEN
9990: -- updating item's org assignments
9991: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
9992: SET PROCESS_FLAG = 1
9993: WHERE SOURCE_SYSTEM_ID = l_ss_id
9994: AND SOURCE_SYSTEM_REFERENCE = l_ssr
9995: AND PROCESS_FLAG = 3
11992:
11993: IF (p_batch_id IS NOT NULL AND p_src_system_id IS NOT NULL AND p_src_system_ref IS NOT NULL) THEN
11994:
11995: --------------------------------
11996: -- MTL_SYSTEM_ITEMS_INTERFACE --
11997: --------------------------------
11998: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
11999: SET ORGANIZATION_ID =
12000: NVL(ORGANIZATION_ID,
11994:
11995: --------------------------------
11996: -- MTL_SYSTEM_ITEMS_INTERFACE --
11997: --------------------------------
11998: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
11999: SET ORGANIZATION_ID =
12000: NVL(ORGANIZATION_ID,
12001: (SELECT MP.ORGANIZATION_ID
12002: FROM MTL_PARAMETERS MP
12061:
12062: CURSOR c_batch_pks IS
12063: SELECT MSII.SOURCE_SYSTEM_ID SOURCE_SYSTEM_ID
12064: ,MSII.SOURCE_SYSTEM_REFERENCE SOURCE_SYSTEM_REFERENCE
12065: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
12066: WHERE MSII.SET_PROCESS_ID = p_batch_id
12067: AND MSII.PROCESS_FLAG = 0
12068: AND MSII.CONFIRM_STATUS IN ('US', 'UM', 'UN');
12069:
12218: ,inventory_item_id
12219: ,item_number
12220: ,transaction_id
12221: ,transaction_type
12222: FROM mtl_system_items_interface
12223: WHERE set_process_id = p_set_id
12224: AND process_flag = 0
12225: AND organization_id = p_org_id
12226: AND ( confirm_status IS NULL
12270: END IF;
12271:
12272: IF upper(item_record.transaction_type) = 'SYNC' THEN
12273: IF l_item_exist = 1 THEN
12274: UPDATE mtl_system_items_interface
12275: SET transaction_type = 'UPDATE'
12276: WHERE rowid = item_record.rowid;
12277: ELSE
12278: IF item_record.item_number IS NOT NULL THEN
12275: SET transaction_type = 'UPDATE'
12276: WHERE rowid = item_record.rowid;
12277: ELSE
12278: IF item_record.item_number IS NOT NULL THEN
12279: UPDATE mtl_system_items_interface
12280: SET transaction_type = 'CREATE', inventory_item_id = NULL
12281: WHERE rowid = item_record.rowid;
12282: ELSE
12283: UPDATE mtl_system_items_interface
12279: UPDATE mtl_system_items_interface
12280: SET transaction_type = 'CREATE', inventory_item_id = NULL
12281: WHERE rowid = item_record.rowid;
12282: ELSE
12283: UPDATE mtl_system_items_interface
12284: SET transaction_type = 'UPDATE', inventory_item_id = NULL
12285: WHERE rowid = item_record.rowid;
12286: END IF;
12287: END IF;
12290: END UPDATE_ITEM_SYNC_RECORDS;
12291:
12292:
12293: PROCEDURE SET_CONFIRM_STATUS
12294: (p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
12295: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12296: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12297: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12298: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12291:
12292:
12293: PROCEDURE SET_CONFIRM_STATUS
12294: (p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
12295: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12296: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12297: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12298: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12299: ,p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE DEFAULT NULL
12292:
12293: PROCEDURE SET_CONFIRM_STATUS
12294: (p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
12295: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12296: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12297: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12298: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12299: ,p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE DEFAULT NULL
12300: ,p_check_matching_table IN FLAG DEFAULT FND_API.G_FALSE
12293: PROCEDURE SET_CONFIRM_STATUS
12294: (p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
12295: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12296: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12297: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12298: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12299: ,p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE DEFAULT NULL
12300: ,p_check_matching_table IN FLAG DEFAULT FND_API.G_FALSE
12301: ,errmsg OUT NOCOPY VARCHAR2
12294: (p_batch_id IN MTL_SYSTEM_ITEMS_INTERFACE.SET_PROCESS_ID%TYPE
12295: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12296: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12297: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12298: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12299: ,p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE DEFAULT NULL
12300: ,p_check_matching_table IN FLAG DEFAULT FND_API.G_FALSE
12301: ,errmsg OUT NOCOPY VARCHAR2
12302: ,retcode OUT NOCOPY VARCHAR2
12295: ,p_source_system_id IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_ID%TYPE
12296: ,p_source_system_reference IN MTL_SYSTEM_ITEMS_INTERFACE.SOURCE_SYSTEM_REFERENCE%TYPE
12297: ,p_new_status IN MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE
12298: ,p_inventory_item_id IN MTL_SYSTEM_ITEMS_INTERFACE.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
12299: ,p_organization_id IN MTL_SYSTEM_ITEMS_INTERFACE.ORGANIZATION_ID%TYPE DEFAULT NULL
12300: ,p_check_matching_table IN FLAG DEFAULT FND_API.G_FALSE
12301: ,errmsg OUT NOCOPY VARCHAR2
12302: ,retcode OUT NOCOPY VARCHAR2
12303: ) IS
12302: ,retcode OUT NOCOPY VARCHAR2
12303: ) IS
12304: CURSOR cur_old_status IS
12305: SELECT CONFIRM_STATUS
12306: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII, EGO_IMPORT_BATCHES_B BA
12307: WHERE MSII.SET_PROCESS_ID = p_batch_id
12308: AND MSII.SOURCE_SYSTEM_ID = p_source_system_id
12309: AND MSII.SOURCE_SYSTEM_REFERENCE = p_source_system_reference
12310: AND MSII.ORGANIZATION_ID = BA.ORGANIZATION_ID
12311: AND BA.BATCH_ID = p_batch_id
12312: AND NVL(MSII.PROCESS_FLAG, 0) < 1
12313: AND ROWNUM < 2;
12314:
12315: l_old_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
12316: l_need_resolve_unconf_status FLAG := p_check_matching_table;
12317: l_resolved_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := p_new_status;
12318: l_item_in_matching_table NUMBER;
12319: BEGIN
12313: AND ROWNUM < 2;
12314:
12315: l_old_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE;
12316: l_need_resolve_unconf_status FLAG := p_check_matching_table;
12317: l_resolved_status MTL_SYSTEM_ITEMS_INTERFACE.CONFIRM_STATUS%TYPE := p_new_status;
12318: l_item_in_matching_table NUMBER;
12319: BEGIN
12320: OPEN cur_old_status;
12321: FETCH cur_old_status INTO l_old_status;
12411: AND SOURCE_SYSTEM_ID = p_source_system_id
12412: AND SOURCE_SYSTEM_REFERENCE = p_source_system_reference;
12413: END IF;
12414:
12415: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
12416: SET CONFIRM_STATUS = l_resolved_status, INVENTORY_ITEM_ID = p_inventory_item_id
12417: WHERE SET_PROCESS_ID = p_batch_id
12418: AND SOURCE_SYSTEM_ID = p_source_system_id
12419: AND SOURCE_SYSTEM_REFERENCE = p_source_system_reference
12505: THEN 1
12506: ELSE NULL
12507: END
12508: ) UPDATE_CNT
12509: FROM MTL_SYSTEM_ITEMS_INTERFACE
12510: WHERE SET_PROCESS_ID = p_set_process_id
12511: AND ( (ORGANIZATION_ID is not null and ORGANIZATION_ID = p_organization_id )
12512: OR
12513: (ORGANIZATION_ID is null and ORGANIZATION_CODE = p_organization_code )
12532: THEN 1
12533: ELSE NULL
12534: END
12535: ) UPDATE_CNT
12536: FROM MTL_SYSTEM_ITEMS_INTERFACE msii
12537: WHERE SET_PROCESS_ID = p_set_process_id
12538: AND EXISTS
12539: (
12540: SELECT NULL