[Home] [Help]
58: IF p_xset_id IS NULL THEN
59:
60: IF p_run_mode = 1 THEN
61:
62: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
63: SET SET_PROCESS_ID = -999
64: WHERE PROCESS_FLAG = 1
65: AND TRANSACTION_TYPE in ('CREATE','Create','create');
66:
70: AND TRANSACTION_TYPE in ('CREATE','Create','create');
71:
72: ELSIF p_run_mode = 2 THEN
73:
74: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
75: SET SET_PROCESS_ID = -999
76: WHERE PROCESS_FLAG = 1
77: AND TRANSACTION_TYPE in ('UPDATE','Update','update');
78:
82: AND TRANSACTION_TYPE in ('UPDATE','Update','update');
83:
84: ELSIF p_run_mode = 3 THEN
85:
86: UPDATE MTL_SYSTEM_ITEMS_INTERFACE
87: SET SET_PROCESS_ID = -999
88: WHERE PROCESS_FLAG = 1
89: AND TRANSACTION_TYPE in ('SYNC','Sync','sync');
90:
114: run_mode => p_run_mode,
115: source_org_id => source_org_id);
116:
117: SELECT count(*) INTO l_pro_flag_3
118: FROM mtl_system_items_interface
119: WHERE process_flag = 3
120: AND request_id = FND_GLOBAL.conc_request_id
121: AND rownum = 1;
122:
181: l_msg_count NUMBER;
182:
183: CURSOR lock_rows IS
184: select rowid
185: from mtl_system_items_interface
186: where set_process_id = xset_id
187: for update;
188:
189: CURSOR lock_revs IS
193: for update;
194:
195: CURSOR update_org_id IS
196: select rowid, transaction_id
197: from mtl_system_items_interface
198: where organization_id is NULL
199: and set_process_id = xset_id
200: and process_flag = 1;
201:
207: and process_flag = 1;
208:
209: CURSOR c_master_items(cp_transaction_type VARCHAR2) IS
210: SELECT COUNT(*)
211: FROM mtl_system_items_interface msii
212: ,mtl_parameters mp1
213: WHERE set_process_id = xset_id
214: AND transaction_type = cp_transaction_type
215: AND process_flag in (1,2,4)
229: --: Bug 6158936
230: --: Child counts
231: CURSOR c_child_items(cp_transaction_type VARCHAR2) IS
232: SELECT count(*)
233: FROM mtl_system_items_interface
234: WHERE set_process_id = xset_id
235: AND transaction_type = cp_transaction_type
236: AND process_flag in (1,2,4)
237: AND organization_id
251: AND ROWNUM = 1;
252:
253: CURSOR c_interface_items(cp_transaction_type VARCHAR2) IS
254: SELECT count(*)
255: FROM mtl_system_items_interface
256: WHERE set_process_id = xset_id
257: AND transaction_type = cp_transaction_type
258: AND process_flag in (1,4);
259:
300: END IF;
301:
302: --3515652: Collect stats only if no. records > 50
303: SELECT count(*) INTO l_records
304: FROM mtl_system_items_interface
305: WHERE set_process_id = xset_id
306: AND process_flag = 1;
307:
308: IF (l_records > 50)
308: IF (l_records > 50)
309: AND FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_schema)
310: THEN
311: IF l_schema IS NOT NULL THEN
312: FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_SYSTEM_ITEMS_INTERFACE');
313: FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_ITEM_REVISIONS_INTERFACE');
314: FND_STATS.GATHER_TABLE_STATS(l_schema, 'MTL_DESC_ELEM_VAL_INTERFACE');
315: END IF;
316: END IF;
326: -- validation fails while Creating or Updating an Item.
327:
328: -- Bug 3975408 :Changed the where clause to (1,4) of the following update.
329:
330: UPDATE mtl_system_items_interface
331: SET transaction_type = UPPER(transaction_type)
332: ,request_id = req_id
333: ,transaction_id = NVL(transaction_id, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL)
334: WHERE set_process_id = xset_id
329:
330: UPDATE mtl_system_items_interface
331: SET transaction_type = UPPER(transaction_type)
332: ,request_id = req_id
333: ,transaction_id = NVL(transaction_id, MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL)
334: WHERE set_process_id = xset_id
335: AND process_flag IN (1,4);
336:
337: UPDATE mtl_item_revisions_interface
340: WHERE set_process_id = xset_id
341: AND process_flag IN (1,4);
342:
343: --SYNC: IOI to support SYNC operation.
344: UPDATE mtl_system_items_interface msii
345: SET process_flag = -888
346: WHERE ( transaction_type NOT IN ('CREATE', 'UPDATE','SYNC')
347: OR transaction_type IS NULL OR set_process_id >= 900000000000)
348: AND set_process_id = xset_id;
359: -- End: 2808277 Supporting Item Revision Update
360:
361: -- Assign missing organization_id from organization_code
362:
363: update MTL_SYSTEM_ITEMS_INTERFACE MSII
364: set MSII.organization_id =
365: ( select MP.organization_id
366: from MTL_PARAMETERS MP
367: where MP.organization_code = MSII.organization_code
409: request_id,
410: cr.transaction_id,
411: 'INVPOPIF: Invalid Organization ID',
412: 'ORGANIZATION_ID',
413: 'MTL_SYSTEM_ITEMS_INTERFACE',
414: 'INV_IOI_ORG_NO_EXIST',
415: err_text);
416: if dumm_status < 0 then
417: raise LOGGING_ERR;
416: if dumm_status < 0 then
417: raise LOGGING_ERR;
418: end if;
419:
420: update mtl_system_items_interface
421: set process_flag = 3
422: where rowid = cr.rowid ;
423:
424: END LOOP;
453: ** to be defaulted and subsequently applied.
454:
455: R12C : Changing the New Item Req Reqd = 'Y' sub-query for hierarchy enabled Catalogs */
456: --6521101 - Pending status updation for master recs only
457: UPDATE mtl_system_items_interface msii
458: SET msii.INVENTORY_ITEM_STATUS_CODE = 'Pending'
459: WHERE (msii.organization_id = org_id OR all_Org = 1)
460: AND msii.INVENTORY_ITEM_STATUS_CODE IS NULL
461: AND msii.ITEM_CATALOG_GROUP_ID IS NOT NULL
477:
478: --SYNC: IOI to support SYNC operation.
479: IF run_mode = 3 THEN
480: --3018673: Start of bug fix.
481: UPDATE mtl_system_items_interface msii
482: SET process_flag = process_flag + 20000
483: WHERE transaction_type IN ('CREATE','UPDATE')
484: AND process_flag < 20000
485: AND set_process_id = xset_id;
500: IF (run_mode IN (1,3,0)) THEN --{ /* transaction_type IN 'CREATE' 'SYNC' */
501:
502: l_processed_flag := TRUE;
503:
504: UPDATE mtl_system_items_interface msii
505: SET process_flag = process_flag + 30000
506: WHERE transaction_type IN ('UPDATE','SYNC') --3018673
507: AND process_flag < 30000
508: AND set_process_id = xset_id;
528: inopinp_OI_process_create for the master org */
529:
530: IF (mtl_count <> 0 or mtli_count <> 0) THEN
531:
532: UPDATE mtl_system_items_interface msii
533: SET process_flag = process_flag + 60000
534: WHERE transaction_type = 'CREATE'
535: AND process_flag < 60000
536: AND set_process_id = xset_id
566: ,xset_id
567: ,commit_flag
568: ,default_flag);
569:
570: UPDATE mtl_system_items_interface msii
571: SET process_flag = process_flag - 60000
572: WHERE transaction_type = 'CREATE'
573: AND process_flag >= 60000
574: AND set_process_id = xset_id;
599: IF (mtl_count <> 0 or mtli_count <> 0) THEN
600:
601: /* R12C Bug 6158936 - All Master Items and revs will be isolated during child procesing */
602:
603: UPDATE mtl_system_items_interface msii
604: SET process_flag = process_flag + 60000
605: WHERE transaction_type = 'CREATE'
606: AND process_flag < 60000
607: AND set_process_id = xset_id
639: default_flag);
640:
641: /* R12C Bug 6158936 : Moving Master Items and revs back to batch */
642:
643: UPDATE mtl_system_items_interface msii
644: SET process_flag = process_flag - 60000
645: WHERE transaction_type = 'CREATE'
646: AND process_flag >= 60000
647: AND set_process_id = xset_id;
686: default_flag);
687: END IF;
688: END IF; --}
689:
690: UPDATE mtl_system_items_interface msii
691: SET process_flag = process_flag - 30000
692: WHERE transaction_type IN ('UPDATE','SYNC') --3018673
693: AND process_flag >= 30000
694: AND set_process_id = xset_id;
704: IF (run_mode IN (2,3,0)) THEN /* transaction_type IN 'UPDATE' 'SYNC' */
705:
706: l_processed_flag := TRUE;
707:
708: UPDATE mtl_system_items_interface msii
709: SET process_flag = process_flag + 30000
710: WHERE transaction_type IN ('CREATE','SYNC') --3018673
711: AND process_flag < 30000
712: AND set_process_id = xset_id;
728: CLOSE c_master_revs;
729:
730: IF (mtl_count <> 0 or mtli_count <> 0) THEN
731:
732: UPDATE mtl_system_items_interface msii
733: SET process_flag = process_flag + 60000
734: WHERE transaction_type = 'UPDATE'
735: AND process_flag < 60000
736: AND set_process_id = xset_id
763: xset_id,
764: commit_flag,
765: default_flag);
766:
767: UPDATE mtl_system_items_interface msii
768: SET process_flag = process_flag - 60000
769: WHERE transaction_type = 'UPDATE'
770: AND process_flag >= 60000
771: AND set_process_id = xset_id;
793: IF (mtl_count <> 0 or mtli_count <> 0) THEN
794:
795: /* R12C Bug 6158936 - All Master Items and revs will be isolated during child procesing */
796:
797: UPDATE mtl_system_items_interface msii
798: SET process_flag = process_flag + 60000
799: WHERE transaction_type = 'UPDATE'
800: AND process_flag < 60000
801: AND set_process_id = xset_id
830: default_flag);
831:
832: /* R12C Bug 6158936 : Moving Master Items and revs back to batch */
833:
834: UPDATE mtl_system_items_interface msii
835: SET process_flag = process_flag - 60000
836: WHERE transaction_type = 'UPDATE'
837: AND process_flag >= 60000
838: AND set_process_id = xset_id;
874: END IF;
875:
876: END IF; --}
877:
878: UPDATE mtl_system_items_interface msii
879: SET process_flag = process_flag - 30000
880: WHERE transaction_type IN ('CREATE','SYNC') --3018673
881: AND process_flag >= 30000
882: AND set_process_id = xset_id;
891:
892: --3018673: Start of bug fix.
893: IF run_mode = 3 THEN
894:
895: UPDATE mtl_system_items_interface msii
896: SET process_flag = process_flag - 20000
897: WHERE transaction_type IN ('CREATE','UPDATE')
898: AND process_flag >= 20000
899: AND set_process_id = xset_id;
1070: ,NVL(msi.CREATED_BY, -1)
1071: ,NVL(msi.LAST_UPDATE_DATE, SYSDATE)
1072: ,NVL(msi.LAST_UPDATED_BY, -1)
1073: ,msi.LAST_UPDATE_LOGIN
1074: FROM mtl_system_items_interface msi
1075: WHERE msi.request_id = request_id
1076: and msi.set_process_id = xset_id
1077: and msi.process_flag = 7
1078: /* Bug 6139403 Do not raise BE for fake rows*/
1216: if the record was created for CM support, and a similar row with
1217: process flag 5 exists in the interface table.
1218: ***/
1219: DELETE
1220: FROM mtl_system_items_interface msii
1221: WHERE process_flag = 7
1222: AND (inventory_item_id, organization_id, set_process_id) IN
1223: (SELECT inventory_item_id, organization_id, set_process_id
1224: FROM mtl_system_items_interface intf
1220: FROM mtl_system_items_interface msii
1221: WHERE process_flag = 7
1222: AND (inventory_item_id, organization_id, set_process_id) IN
1223: (SELECT inventory_item_id, organization_id, set_process_id
1224: FROM mtl_system_items_interface intf
1225: WHERE set_process_id = xset_id
1226: AND process_flag = 5);
1227: END IF; -- del_rec_flag = 1
1228:
1552: RETURN INTEGER IS
1553:
1554: CURSOR Error_Items IS
1555: SELECT transaction_id, organization_id
1556: FROM mtl_system_items_interface
1557: WHERE process_flag = 4
1558: AND set_process_id = xset_id
1559: AND transaction_type = 'CREATE';
1560:
1715: request_id,
1716: ee.transaction_id,
1717: err_msg,
1718: 'INVENTORY_ITEM_ID',
1719: 'MTL_SYSTEM_ITEMS_INTERFACE',
1720: 'INV_IOI_ERR',
1721: err_msg);
1722: END LOOP;
1723:
1720: 'INV_IOI_ERR',
1721: err_msg);
1722: END LOOP;
1723:
1724: UPDATE mtl_system_items_interface
1725: SET process_flag = 3
1726: WHERE process_flag = 4
1727: AND set_process_id = xset_id
1728: AND transaction_type = 'CREATE';
1746: BEGIN
1747: SELECT 'Y' INTO l_child_records
1748: FROM DUAL
1749: WHERE EXISTS (SELECT NULL
1750: FROM mtl_system_items_interface msii,
1751: mtl_parameters mp
1752: WHERE transaction_type = 'CREATE'
1753: AND process_flag = 7
1754: AND set_process_id = xset_id
1820:
1821: stmt_num := 1;
1822:
1823: LOOP
1824: DELETE FROM MTL_SYSTEM_ITEMS_INTERFACE
1825: WHERE process_flag = l_process_flag_7
1826: AND set_process_id in (xset_id, xset_id + 1000000000000)
1827: AND rownum < l_rownum;
1828:
1893: ,segment20
1894: ,item_number
1895: ,transaction_id
1896: ,transaction_type
1897: FROM mtl_system_items_interface
1898: WHERE set_process_id = p_set_id
1899: AND process_flag = 1
1900: AND (transaction_type = 'SYNC' OR
1901: (transaction_type = 'UPDATE' AND inventory_item_id IS NOT NULL AND
2040: dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2041: fnd_global.prog_appl_id,fnd_global.conc_program_id,fnd_global.conc_request_id,item_record.transaction_id,
2042: 'INVPOPIF: Update to Item number not allowed',
2043: 'ITEM NUMBER',
2044: 'MTL_SYSTEM_ITEMS_INTERFACE',
2045: 'INV_ITEM_NUMBER_NO_UDPATE',
2046: l_err_text);
2047: IF dumm_status < 0 THEN
2048: raise LOGGING_ERR;
2047: IF dumm_status < 0 THEN
2048: raise LOGGING_ERR;
2049: END IF;
2050:
2051: update mtl_system_items_interface
2052: set process_flag = 3
2053: where rowid = item_record.rowid ;
2054:
2055: END IF;
2058: dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2059: fnd_global.prog_appl_id,fnd_global.conc_program_id,fnd_global.conc_request_id,item_record.transaction_id,
2060: 'INVPOPIF: Update to Item number not allowed in child organization',
2061: 'ITEM NUMBER',
2062: 'MTL_SYSTEM_ITEMS_INTERFACE',
2063: 'INV_ITEM_NUMBER_ORG_NO_UDPATE',
2064: l_err_text);
2065: IF dumm_status < 0 THEN
2066: raise LOGGING_ERR;
2065: IF dumm_status < 0 THEN
2066: raise LOGGING_ERR;
2067: END IF;
2068:
2069: update mtl_system_items_interface
2070: set process_flag = 3
2071: where rowid = item_record.rowid ;
2072:
2073: END IF;
2085: dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2086: fnd_global.prog_appl_id,fnd_global.conc_program_id,fnd_global.conc_request_id,item_record.transaction_id,
2087: 'INVPOPIF: Update to Duplicate Item number',
2088: 'ITEM NUMBER',
2089: 'MTL_SYSTEM_ITEMS_INTERFACE',
2090: 'INV_UPDATE_TO_EXIST_ITEM_NAME',
2091: l_err_text);
2092: IF dumm_status < 0 THEN
2093: raise LOGGING_ERR;
2092: IF dumm_status < 0 THEN
2093: raise LOGGING_ERR;
2094: END IF;
2095:
2096: update mtl_system_items_interface
2097: set process_flag = 3
2098: where rowid = item_record.rowid ;
2099:
2100: END IF;
2101: END IF;
2102: END IF; --ITEM ID
2103:
2104: IF l_item_exist <> 0 THEN
2105: UPDATE mtl_system_items_interface
2106: SET transaction_type = 'UPDATE'
2107: WHERE rowid = item_record.rowid
2108: AND transaction_type = 'SYNC';
2109: ELSE
2106: SET transaction_type = 'UPDATE'
2107: WHERE rowid = item_record.rowid
2108: AND transaction_type = 'SYNC';
2109: ELSE
2110: UPDATE mtl_system_items_interface
2111: SET transaction_type = 'CREATE'
2112: WHERE rowid = item_record.rowid
2113: AND transaction_type = 'SYNC';
2114: END IF;
2172:
2173: LOGGING_ERR EXCEPTION;
2174: CURSOR update_catg_name(p_catg_name IN VARCHAR2) IS
2175: SELECT ROWID, msii.TRANSACTION_ID
2176: FROM mtl_system_items_interface msii
2177: WHERE SET_PROCESS_ID = p_set_id
2178: AND msii.ITEM_CATALOG_GROUP_NAME = p_catg_name;
2179:
2180: --Holds {Item Catalog Group Name: Item Catalog ID}
2185:
2186: l_Item_Catalog_Group_ID
2187: mtl_item_catalog_groups_b.ITEM_CATALOG_GROUP_ID%TYPE;
2188: l_Item_Catalog_Group_Name
2189: mtl_system_items_interface.ITEM_CATALOG_GROUP_NAME%TYPE;
2190:
2191: l_Item_Catalog VARCHAR2(2000);
2192: l_index INTEGER;
2193: l_dumm_status NUMBER;
2195: BEGIN
2196: SELECT DISTINCT msii.ITEM_CATALOG_GROUP_NAME ||
2197: ':' || bkfv.ITEM_CATALOG_GROUP_ID
2198: BULK COLLECT INTO Item_Catalogs_Table
2199: FROM mtl_system_items_interface msii,
2200: mtl_item_catalog_groups_b_kfv bkfv
2201: WHERE msii.ITEM_CATALOG_GROUP_ID IS NULL
2202: AND msii.ITEM_CATALOG_GROUP_NAME IS NOT NULL
2203: AND msii.SET_PROCESS_ID = p_set_id
2223: p_request_id,
2224: cr.transaction_id,
2225: 'INVPOPIF: Invalid Item Catalog Group Name',
2226: 'ITEM_CATALOG_GROUP_NAME',
2227: 'MTL_SYSTEM_ITEMS_INTERFACE',
2228: 'INV_IOI_CATG_NAME_INVALID',
2229: x_err_text);
2230: IF l_dumm_status < 0 then
2231: raise LOGGING_ERR;
2230: IF l_dumm_status < 0 then
2231: raise LOGGING_ERR;
2232: END IF;
2233:
2234: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
2235: SET PROCESS_FLAG = 3
2236: WHERE ROWID = cr.ROWID ;
2237: END LOOP; --cr
2238:
2239: ELSE --Get the ID and populate in the ITEM_CATALOG_GROUP_ID column
2240: l_Item_Catalog_Group_ID
2241: := SUBSTR(l_Item_Catalog, INSTR(l_Item_Catalog,':') +1);
2242:
2243: UPDATE MTL_SYSTEM_ITEMS_INTERFACE MSII
2244: SET MSII.ITEM_CATALOG_GROUP_ID = l_Item_Catalog_Group_ID
2245: WHERE SET_PROCESS_ID = p_set_id
2246: AND MSII.ITEM_CATALOG_GROUP_NAME = l_Item_Catalog_Group_Name;
2247: END IF;