[Home] [Help]
1: PACKAGE BODY INVPOPIF AS
2: /* $Header: INVPOPIB.pls 120.67.12020000.3 2013/01/23 08:24:39 lmai ship $ */
3: ---------------------- Package variables and constants -----------------------
4:
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'INVPOPIF';
1: PACKAGE BODY INVPOPIF AS
2: /* $Header: INVPOPIB.pls 120.67.12020000.3 2013/01/23 08:24:39 lmai ship $ */
3: ---------------------- Package variables and constants -----------------------
4:
5: G_PKG_NAME CONSTANT VARCHAR2(30) := 'INVPOPIF';
6:
7: G_SUCCESS CONSTANT NUMBER := 0;
8: G_WARNING CONSTANT NUMBER := 1;
9: G_ERROR CONSTANT NUMBER := 2;
108:
109: END IF;
110:
111: BEGIN
112: ret_status := INVPOPIF.inopinp_open_interface_process(
113: org_id => p_org_id,
114: all_org => p_all_org,
115: val_item_flag => p_val_item_flag,
116: pro_item_flag => p_pro_item_flag,
320: BEGIN
321: /*Added for bug 6372595*/
322: IF source_org_id <> -999
323: THEN
324: INVPOPIF.g_source_org := FALSE ;
325: END IF ;
326:
327: IF l_inv_debug_level IN(101, 102) THEN
328: INVPUTLI.info('INVPOPIF: *** Starting a new IOI process: run_mode='|| TO_CHAR(run_mode) ||' all_org='|| TO_CHAR(all_org));
324: INVPOPIF.g_source_org := FALSE ;
325: END IF ;
326:
327: IF l_inv_debug_level IN(101, 102) THEN
328: INVPUTLI.info('INVPOPIF: *** Starting a new IOI process: run_mode='|| TO_CHAR(run_mode) ||' all_org='|| TO_CHAR(all_org));
329: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: org_id = '|| TO_CHAR(org_id) || 'Default flag=' || To_Char(default_flag) );
330: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: gather_stats = ' || gather_stats); /* Added for Bug 8532728 */
331: END IF;
332: /*
325: END IF ;
326:
327: IF l_inv_debug_level IN(101, 102) THEN
328: INVPUTLI.info('INVPOPIF: *** Starting a new IOI process: run_mode='|| TO_CHAR(run_mode) ||' all_org='|| TO_CHAR(all_org));
329: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: org_id = '|| TO_CHAR(org_id) || 'Default flag=' || To_Char(default_flag) );
330: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: gather_stats = ' || gather_stats); /* Added for Bug 8532728 */
331: END IF;
332: /*
333: ** Make sure transaction type is in upper case
326:
327: IF l_inv_debug_level IN(101, 102) THEN
328: INVPUTLI.info('INVPOPIF: *** Starting a new IOI process: run_mode='|| TO_CHAR(run_mode) ||' all_org='|| TO_CHAR(all_org));
329: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: org_id = '|| TO_CHAR(org_id) || 'Default flag=' || To_Char(default_flag) );
330: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: gather_stats = ' || gather_stats); /* Added for Bug 8532728 */
331: END IF;
332: /*
333: ** Make sure transaction type is in upper case
334: */
337: --IF fnd_global.conc_program_id <> -1 THEN Bug:3547401
338: IF NVL(prog_id,-1) <> -1 AND gather_stats = 1 THEN /* Added for Bug 8532728 */
339:
340: IF l_inv_debug_level IN(101, 102) THEN
341: INVPUTLI.info('INVPOPIF: Gathering interface table stats');
342: END IF;
343:
344: -- Fix for bug#9336604
345: --3515652: Collect stats only if no. records > 50
371: END IF;
372: END IF;
373:
374: IF l_inv_debug_level IN(101, 102) THEN
375: INVPUTLI.info('INVPOPIF: Gathering interface table stats done');
376: END IF;
377:
378: END IF;
379: --End 2698140 : Gather stats before running the IOI
424: prog_appid,
425: prog_id,
426: request_id,
427: cr.transaction_id,
428: 'INVPOPIF: Invalid transaction type',
429: 'TRANSACTION_TYPE',
430: 'MTL_SYSTEM_ITEMS_INTERFACE',
431: 'INV_INT_TRXTYPCODE',
432: err_text);
449: prog_appid,
450: prog_id,
451: request_id,
452: cr.transaction_id,
453: 'INVPOPIF: Invalid transaction type',
454: 'TRANSACTION_TYPE',
455: 'mtl_item_revisions_interface',
456: 'INV_INT_TRXTYPCODE',
457: err_text);
512: prog_appid,
513: prog_id,
514: request_id,
515: cr.transaction_id,
516: 'INVPOPIF: Invalid Organization ID',
517: 'ORGANIZATION_ID',
518: 'MTL_SYSTEM_ITEMS_INTERFACE',
519: 'INV_IOI_ORG_NO_EXIST',
520: err_text);
536: prog_appid,
537: prog_id,
538: request_id,
539: cr.transaction_id,
540: 'INVPOPIF: Invalid Organization ID',
541: 'ORGANIZATION_ID',
542: 'MTL_ITEM_REVISIONS_INTERFACE',
543: 'INV_IOI_ORG_NO_EXIST',
544: err_text);
640: CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
641: START WITH ICC.ITEM_CATALOG_GROUP_ID = msii.ITEM_CATALOG_GROUP_ID ); --R12C
642:
643: --SYNC: IOI to support SYNC operation.
644: /* Bug 9660959 Need to disable this since EGO Import Catalog Item program is calling INVPOPIF more than once
645: the SYNC rows in pervious round will become create/update rows which shouldn't be disabled
646: IF run_mode = 3 THEN
647: --3018673: Start of bug fix.
648: UPDATE mtl_system_items_interface msii
717: where miri.organization_id = mp1.master_organization_id);
718:
719: --Creating Master Items
720: IF l_inv_debug_level IN(101, 102) THEN
721: INVPUTLI.info('INVPOPIF all_org=1: Calling create process for masters');
722: END IF;
723: ret_code_create := INVPOPIF.inopinp_OI_process_create (
724: NULL
725: ,1
719: --Creating Master Items
720: IF l_inv_debug_level IN(101, 102) THEN
721: INVPUTLI.info('INVPOPIF all_org=1: Calling create process for masters');
722: END IF;
723: ret_code_create := INVPOPIF.inopinp_OI_process_create (
724: NULL
725: ,1
726: ,val_item_flag
727: ,pro_item_flag
788: from mtl_parameters mp1
789: where miri.organization_id = mp1.master_organization_id);
790:
791: IF l_inv_debug_level IN(101, 102) THEN
792: INVPUTLI.info('INVPOPIF all_org=1: Calling create process for childs');
793: END IF;
794: --Creating Child Items
795: ret_code_create := INVPOPIF.inopinp_OI_process_create (
796: NULL,
791: IF l_inv_debug_level IN(101, 102) THEN
792: INVPUTLI.info('INVPOPIF all_org=1: Calling create process for childs');
793: END IF;
794: --Creating Child Items
795: ret_code_create := INVPOPIF.inopinp_OI_process_create (
796: NULL,
797: 1,
798: val_item_flag,
799: pro_item_flag,
834: FETCH c_interface_revs INTO mtli_count;
835: CLOSE c_interface_revs;
836:
837: IF l_inv_debug_level IN(101, 102) THEN
838: INVPUTLI.info('INVPOPIF all_org<>1: Calling create process');
839: END IF;
840:
841: IF (mtl_count <> 0 or mtli_count <> 0) THEN
842: ret_code_create := INVPOPIF.inopinp_OI_process_create (
838: INVPUTLI.info('INVPOPIF all_org<>1: Calling create process');
839: END IF;
840:
841: IF (mtl_count <> 0 or mtli_count <> 0) THEN
842: ret_code_create := INVPOPIF.inopinp_OI_process_create (
843: org_id,
844: all_org,
845: val_item_flag,
846: pro_item_flag,
919: from mtl_parameters mp1
920: where miri.organization_id = mp1.master_organization_id);
921:
922: --Update master Items.
923: ret_code_update := INVPOPIF.inopinp_OI_process_update (
924: NULL,
925: 1,
926: val_item_flag,
927: pro_item_flag,
985: from mtl_parameters mp1
986: where miri.organization_id <> mp1.master_organization_id);
987:
988: --Updating the child records.
989: ret_code_update := INVPOPIF.inopinp_OI_process_update (
990: NULL,
991: 1,
992: val_item_flag,
993: pro_item_flag,
1028: FETCH c_interface_revs INTO mtli_count;
1029: CLOSE c_interface_revs;
1030:
1031: IF (mtl_count <> 0 or mtli_count <> 0) THEN
1032: ret_code_update := INVPOPIF.inopinp_OI_process_update (
1033: org_id,
1034: all_org,
1035: val_item_flag,
1036: pro_item_flag,
1137: ret_code := 1;
1138: END IF;
1139:
1140: IF l_inv_debug_level IN(101, 102) THEN
1141: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Before Sync IM Index');
1142: END IF;
1143:
1144: --Start : Sync iM index changes
1145: IF pro_item_flag = 1 --Bug 4667985
1152: --
1153: -- Sync processed rows with item star table
1154: --
1155: IF l_inv_debug_level IN(101, 102) THEN
1156: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI');
1157: END IF;
1158:
1159: --Bug: 2718703 checking for ENI product before calling their package
1160: --This check has been moved to INV_ENI_ITEMS_STAR_PKG
1168: ,x_msg_data => l_msg_data);
1169:
1170:
1171: IF NOT ( l_return_status = FND_API.g_RET_STS_SUCCESS ) THEN
1172: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Synch Up of ENI tables failed');
1173: dumm_status := INVPUOPI.mtl_log_interface_err (
1174: ORG_ID => -1,
1175: USER_ID =>user_id,
1176: LOGIN_ID =>login_id,
1184: MSG_NAME =>'INV_IOI_ERR_SS_ITMS_FRM_IOI', --added by bug 11894684, replace INV_IOI_ERR with specific message name so we can identify error easily
1185: ERR_TEXT =>err_text);
1186:
1187: if ( dumm_status < 0 ) then
1188: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Logging Error');
1189: end if;
1190: END IF; --l_return_status
1191: EXCEPTION
1192: WHEN OTHERS THEN
1208:
1209: END;
1210:
1211: IF l_inv_debug_level IN(101, 102) THEN
1212: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Start Raising Business Events');
1213: END IF;
1214:
1215: --R12: Business Event Enhancement:
1216: --Raise events for EGO Bulk Load and Excel Import
1218:
1219: --Populate Item Bulkload Recs for items and revisions
1220: BEGIN
1221: IF l_inv_debug_level IN(101, 102) THEN
1222: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Insert in to bulkloadrecs for Item');
1223: END IF;
1224:
1225: INSERT INTO MTL_ITEM_BULKLOAD_RECS(
1226: REQUEST_ID
1263: ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_BULKLOAD_EVENT'
1264: ,p_dml_type => 'BULK');
1265:
1266: IF l_inv_debug_level IN(101, 102) THEN
1267: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Raised Item Bulkload Event');
1268: END IF;
1269:
1270: EXCEPTION
1271: WHEN OTHERS THEN
1268: END IF;
1269:
1270: EXCEPTION
1271: WHEN OTHERS THEN
1272: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while raising Item Change Event',1,240);
1273: IF l_inv_debug_level IN(101, 102) THEN
1274: INVPUTLI.info(err_msg);
1275: END IF;
1276: END;
1277: END IF;
1278:
1279:
1280: IF l_inv_debug_level IN(101, 102) THEN
1281: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Insert in to bulkloadrecs for Revision.');
1282: END IF;
1283:
1284: INSERT INTO MTL_ITEM_BULKLOAD_RECS(
1285: REQUEST_ID
1319: ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
1320: ,p_dml_type => 'BULK');
1321:
1322: IF l_inv_debug_level IN(101, 102) THEN
1323: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Raised Revision Bulkload Event');
1324: END IF;
1325:
1326: EXCEPTION
1327: WHEN OTHERS THEN
1324: END IF;
1325:
1326: EXCEPTION
1327: WHEN OTHERS THEN
1328: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while raising REV Change Event',1,240);
1329: IF l_inv_debug_level IN(101, 102) THEN
1330: INVPUTLI.info(err_msg);
1331: END IF;
1332: END;
1335:
1336:
1337: EXCEPTION
1338: WHEN OTHERS THEN
1339: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while inserting records in MTL_ITEM_BULKLOAD_RECS',1,240);
1340: IF l_inv_debug_level IN(101, 102) THEN
1341: INVPUTLI.info(err_msg);
1342: END IF;
1343: END;
1350: ,p_xset_id => xset_id
1351: ,p_entity_type => 'ITEM'
1352: ,p_dml_type => 'BULK');
1353: IF l_inv_debug_level IN(101, 102) THEN
1354: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Calling ICX Bulkload Event');
1355: END IF;
1356: EXCEPTION
1357: WHEN OTHERS THEN
1358: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||'while invoking ICX APIs',1,240);
1354: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Calling ICX Bulkload Event');
1355: END IF;
1356: EXCEPTION
1357: WHEN OTHERS THEN
1358: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||'while invoking ICX APIs',1,240);
1359: IF l_inv_debug_level IN(101, 102) THEN
1360: INVPUTLI.info(err_msg);
1361: END IF;
1362: END;
1377: --Delete processed records from IOI tables.
1378: --Bug: 5473976 Rows will not be deleted if control is coming from Import Workbench
1379: IF (del_rec_flag = 1 AND (NVL(INV_EGO_REVISION_VALIDATE.Get_Process_Control,'!') <> 'EGO_ITEM_BULKLOAD')) THEN
1380: IF l_inv_debug_level IN(101, 102) THEN
1381: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPOPIF.indelitm_delete_item_oi');
1382: END IF;
1383:
1384: ret_code := INVPOPIF.indelitm_delete_item_oi (err_text => err_msg,
1385: com_flag => commit_flag,
1380: IF l_inv_debug_level IN(101, 102) THEN
1381: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPOPIF.indelitm_delete_item_oi');
1382: END IF;
1383:
1384: ret_code := INVPOPIF.indelitm_delete_item_oi (err_text => err_msg,
1385: com_flag => commit_flag,
1386: xset_id => xset_id);
1387:
1388: IF l_inv_debug_level IN(101, 102) THEN
1385: com_flag => commit_flag,
1386: xset_id => xset_id);
1387:
1388: IF l_inv_debug_level IN(101, 102) THEN
1389: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPOPIF.indelitm_delete_item_oi: ret_code=' || ret_code);
1390: END IF;
1391:
1392: ELSE
1393: /****Added for bug 5194369
1424: -- Process Item Category Open Interface records
1425: --
1426:
1427: IF l_inv_debug_level IN(101, 102) THEN
1428: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: calling INV_ITEM_CATEGORY_OI.process_Item_Category_records '||pro_item_flag);
1429: END IF;
1430:
1431: SELECT COUNT(1) INTO mtl_count
1432: FROM mtl_item_categories_interface mici
1450: , p_gather_stats => gather_stats /* Added for Bug 8532728 */ );
1451: END IF;
1452:
1453: IF l_inv_debug_level IN(101, 102) THEN
1454: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: done INV_ITEM_CATEGORY_OI.process_Item_Category_records: ret_code=' || ret_code);
1455: END IF;
1456:
1457: /* SET return code to that of last error, IF any */
1458:
1467: --
1468: -- Process Item Catalog group element values open Interface records
1469: --
1470: IF l_inv_debug_level IN(101, 102) THEN
1471: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: calling INV_ITEM_CATALOG_ELEM_PUB.process_Item_Catalog_grp_recs');
1472: END IF;
1473:
1474: SELECT COUNT(1) INTO mtl_count
1475: FROM mtl_desc_elem_val_interface mdei
1493: ret_code_grp := 0;
1494: END IF;
1495:
1496: IF l_inv_debug_level IN(101, 102) THEN
1497: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: done INV_ITEM_CATALOG_ELEM_PUB.process_Item_Catalog_grp_recs: ret_code=' || ret_code_grp);
1498: END IF;
1499:
1500: IF (ret_code_grp <> 0) THEN
1501: ret_code := ret_code_grp;
1539:
1540: BEGIN
1541:
1542: IF l_inv_debug_level IN(101, 102) THEN
1543: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update : begin org_id: ' || TO_CHAR(org_id));
1544: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
1545: END IF;
1546:
1547: --Validate Catalog_Group_name
1540: BEGIN
1541:
1542: IF l_inv_debug_level IN(101, 102) THEN
1543: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update : begin org_id: ' || TO_CHAR(org_id));
1544: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
1545: END IF;
1546:
1547: --Validate Catalog_Group_name
1548: UPDATE_ITEM_CATALOG_ID(
1567:
1568:
1569:
1570: IF l_inv_debug_level IN(101, 102) THEN
1571: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVNIRIS.change_policy_check');
1572: END IF;
1573:
1574: ret_code := INVNIRIS.change_policy_check (
1575: org_id => org_id,
1582: err_text => err_msg,
1583: xset_id => xset_id);
1584:
1585: IF l_inv_debug_level IN(101, 102) THEN
1586: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVNIRIS.change_policy_check RETURN'||ret_code);
1587: INVPUTLI.info('INVNIRIS.change_policy_check->l'||err_msg);
1588: END IF;
1589:
1590: IF (ret_code <> 0) THEN
1594: END IF;
1595:
1596: IF default_flag = 1 THEN
1597: IF l_inv_debug_level IN(101, 102) THEN
1598: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD1B.mtl_pr_assign_item_data_update');
1599: END IF;
1600:
1601:
1602:
1611: err_text => err_msg,
1612: xset_id => xset_id);
1613: elsif default_flag = 2 then --Rules ER 11830273, populate revision_id for UPDATE case when default_flag is 2
1614: IF l_inv_debug_level IN(101, 102) THEN
1615: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: POPULATE REVISION_ID TO mtl_item_revisions_interface IF REVISION_ID IS NULL');
1616: END IF;
1617: UPDATE mtl_item_revisions_interface MIRI set revision_id = (SELECT R.REVISION_ID FROM MTL_ITEM_REVISIONS_B R WHERE R.REVISION = MIRI.REVISION AND R.ORGANIZATION_ID = MIRI.ORGANIZATION_ID AND R.INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID )
1618: where process_flag = 1
1619: AND set_process_id = xset_id
1624: END IF;
1625:
1626: IF (val_item_flag = 1) THEN
1627: IF (ret_code <> 0) THEN
1628: err_msg := 'INVPOPIF.inopinp_OI_process_update: error in ASSIGN phase of UPDATE;' ||
1629: ' Please check mtl_interface_errors table ' || err_msg;
1630: goto ERROR_LABEL;
1631: END IF;
1632:
1631: END IF;
1632:
1633: --Bug:3777954 added call to new pkg/processing for NIR required items (for EGO)
1634: IF l_inv_debug_level IN(101, 102) THEN
1635: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item');
1636: END IF;
1637:
1638: ret_code := INVNIRIS.mtl_validate_nir_item (
1639: org_id => org_id,
1647: xset_id => xset_id);
1648:
1649:
1650: IF l_inv_debug_level IN(101, 102) THEN
1651: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1652: END IF;
1653:
1654: IF (ret_code <> 0) THEN
1655: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in NIR ASSIGN phase of UPDATE;' ||
1651: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1652: END IF;
1653:
1654: IF (ret_code <> 0) THEN
1655: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in NIR ASSIGN phase of UPDATE;' ||
1656: ' Please check mtl_interface_errors table ' || err_msg;
1657: goto ERROR_LABEL;
1658: END IF;
1659:
1659:
1660: --Bug:3777954 call ends
1661:
1662: IF l_inv_debug_level IN(101, 102) THEN
1663: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD1B.mtl_pr_validate_item_update');
1664: END IF;
1665:
1666: ret_code := INVUPD1B.mtl_pr_validate_item_update (
1667: org_id => org_id,
1674: err_text => err_msg,
1675: xset_id => xset_id);
1676:
1677: IF (ret_code <> 0) THEN
1678: err_msg := 'INVPOPIF.inopinp_OI_process_update: error in VALIDATE phase of UPDATE;' ||
1679: ' Please check mtl_interface_errors table ' || err_msg;
1680: goto ERROR_LABEL;
1681: END IF;
1682:
1684:
1685: IF (pro_item_flag = 1) THEN
1686:
1687: IF l_inv_debug_level IN(101, 102) THEN
1688: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD2B.inproit_process_item_update');
1689: END IF;
1690:
1691: ret_code := INVUPD2B.inproit_process_item_update (
1692: prg_appid => prog_appid,
1699: table_name => table_name,
1700: xset_id => xset_id,
1701: commit_flag => commit_flag); /*Added to fix Bug 8359046*/
1702: IF l_inv_debug_level IN(101, 102) THEN
1703: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: done INVUPD2B.inproit_process_item_update: ret_code=' || ret_code);
1704: END IF;
1705:
1706: IF (ret_code <> 0) THEN
1707: err_msg := 'INVPOPIF.inopinp_OI_process_update: error in PROCESS phase of UPDATE;' ||
1703: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: done INVUPD2B.inproit_process_item_update: ret_code=' || ret_code);
1704: END IF;
1705:
1706: IF (ret_code <> 0) THEN
1707: err_msg := 'INVPOPIF.inopinp_OI_process_update: error in PROCESS phase of UPDATE;' ||
1708: ' Please check mtl_interface_errors table ' || err_msg;
1709: goto ERROR_LABEL;
1710: END IF;
1711:
1713: -- Sync processed rows with item star table
1714: --
1715:
1716: IF l_inv_debug_level IN(101, 102) THEN
1717: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI');
1718: END IF;
1719:
1720: --Bug: 2718703 checking for ENI product before calling their package
1721: --This check has been moved to INV_ENI_ITEMS_STAR_PKG
1733:
1734: EXCEPTION
1735:
1736: WHEN OTHERS THEN
1737: err_text := substr('INVPOPIF.inopinp_OI_process_update ' || SQLERRM , 1,240);
1738: IF l_inv_debug_level IN(101, 102) THEN
1739: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: About to rollback.');
1740: END IF;
1741: ROLLBACK;
1735:
1736: WHEN OTHERS THEN
1737: err_text := substr('INVPOPIF.inopinp_OI_process_update ' || SQLERRM , 1,240);
1738: IF l_inv_debug_level IN(101, 102) THEN
1739: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: About to rollback.');
1740: END IF;
1741: ROLLBACK;
1742: RETURN (ret_code);
1743: END inopinp_OI_process_update;
1790: trans_id NUMBER;
1791:
1792: BEGIN
1793: IF l_inv_debug_level IN(101, 102) THEN
1794: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create : begin org_id: ' || TO_CHAR(org_id));
1795: END IF;
1796:
1797: IF l_inv_debug_level IN(101, 102) THEN
1798: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
1794: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create : begin org_id: ' || TO_CHAR(org_id));
1795: END IF;
1796:
1797: IF l_inv_debug_level IN(101, 102) THEN
1798: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
1799: END IF;
1800:
1801: --Validate Catalog_Group_name
1802: UPDATE_ITEM_CATALOG_ID(
1819: ,x_err_text => err_text);
1820: END IF ;
1821:
1822: IF l_inv_debug_level IN(101, 102) THEN
1823: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPASGI.mtl_pr_assign_item_data');
1824: END IF;
1825:
1826: ret_code := INVPASGI.mtl_pr_assign_item_data (
1827: org_id => org_id,
1835: xset_id => xset_id,
1836: default_flag => default_flag);
1837:
1838: IF l_inv_debug_level IN(101, 102) THEN
1839: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPASGI.mtl_pr_assign_item_data: ret_code=' || ret_code || ' err_msg=' || err_msg);
1840: END IF;
1841:
1842: IF (ret_code <> 0) THEN
1843: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in ASSIGN phase of CREATE;' ||
1839: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPASGI.mtl_pr_assign_item_data: ret_code=' || ret_code || ' err_msg=' || err_msg);
1840: END IF;
1841:
1842: IF (ret_code <> 0) THEN
1843: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in ASSIGN phase of CREATE;' ||
1844: ' Please check mtl_interface_errors table ' || err_msg;
1845: goto ERROR_LABEL;
1846: END IF;
1847:
1848: IF (val_item_flag = 1) THEN
1849: --Bug:3777954 added call to new pkg/processing for NIR required items (for EGO)
1850:
1851: IF l_inv_debug_level IN(101, 102) THEN
1852: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item');
1853: END IF;
1854:
1855: ret_code := INVNIRIS.mtl_validate_nir_item (
1856: org_id => org_id,
1863: err_text => err_msg,
1864: xset_id => xset_id);
1865:
1866: IF l_inv_debug_level IN(101, 102) THEN
1867: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1868: END IF;
1869:
1870: IF (ret_code <> 0) THEN
1871: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in NIR ASSIGN phase of CREATE;' ||
1867: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1868: END IF;
1869:
1870: IF (ret_code <> 0) THEN
1871: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in NIR ASSIGN phase of CREATE;' ||
1872: ' Please check mtl_interface_errors table ' || err_msg;
1873: goto ERROR_LABEL;
1874: END IF;
1875:
1875:
1876: --Bug:3777954 call ends
1877:
1878: IF l_inv_debug_level IN(101, 102) THEN
1879: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPVALI.mtl_pr_validate_item');
1880: END IF;
1881:
1882: ret_code := INVPVALI.mtl_pr_validate_item (
1883: org_id => org_id,
1890: err_text => err_msg,
1891: xset_id => xset_id);
1892:
1893: IF l_inv_debug_level IN(101, 102) THEN
1894: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPVALI.mtl_pr_validate_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1895: END IF;
1896:
1897: IF (ret_code <> 0) THEN
1898: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in VALIDATE phase of CREATE;'||
1894: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPVALI.mtl_pr_validate_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1895: END IF;
1896:
1897: IF (ret_code <> 0) THEN
1898: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in VALIDATE phase of CREATE;'||
1899: ' Please check mtl_interface_errors table ' || err_msg;
1900: goto ERROR_LABEL;
1901: END IF;
1902:
1905:
1906: IF (pro_item_flag = 1) THEN
1907:
1908: IF l_inv_debug_level IN(101, 102) THEN
1909: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPPROC.inproit_process_item');
1910: END IF;
1911:
1912: ret_code := INVPPROC.inproit_process_item (
1913: prg_appid => prog_appid,
1920: table_name => table_name,
1921: xset_id => xset_id);
1922:
1923: IF l_inv_debug_level IN(101, 102) THEN
1924: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPPROC.inproit_process_item: ret_code=' || ret_code);
1925: END IF;
1926:
1927: IF (ret_code <> 0) THEN
1928:
1950: WHERE process_flag = 4
1951: AND set_process_id = xset_id
1952: AND transaction_type = 'CREATE';
1953:
1954: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in PROCESS phase of CREATE;'||
1955: ' Please check mtl_interface_errors table ' || err_msg;
1956:
1957: goto ERROR_LABEL;
1958: END IF;
1960: --
1961: -- Sync processed rows with item star table
1962: --
1963: IF l_inv_debug_level IN(101, 102) THEN
1964: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI');
1965: END IF;
1966:
1967: --Bug: 2718703 checking for ENI product before calling their package
1968: --This check has been moved to INV_ENI_ITEMS_STAR_PKG
1991: EXCEPTION
1992: WHEN NO_DATA_FOUND THEN
1993: NULL; --Child records not created, no need to call JAI api's
1994: WHEN OTHERS THEN
1995: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while IL API call ',1,240);
1996: IF l_inv_debug_level IN(101, 102) THEN
1997: INVPUTLI.info(err_msg);
1998: END IF;
1999: END;
2012: -- Parameter ret_code is defaulted to 1, which is passed
2013: -- back for oracle error in UPDATE st.
2014:
2015: WHEN others THEN
2016: err_text := substr('INVPOPIF.inopinp_OI_process_create ' || SQLERRM , 1,240);
2017: IF l_inv_debug_level IN(101, 102) THEN
2018: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: About to rollback.');
2019: END IF;
2020: ROLLBACK;
2014:
2015: WHEN others THEN
2016: err_text := substr('INVPOPIF.inopinp_OI_process_create ' || SQLERRM , 1,240);
2017: IF l_inv_debug_level IN(101, 102) THEN
2018: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: About to rollback.');
2019: END IF;
2020: ROLLBACK;
2021: RETURN (ret_code);
2022: END inopinp_OI_process_create;
2038: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667453
2039: BEGIN
2040:
2041: IF l_inv_debug_level IN(101, 102) THEN
2042: INVPUTLI.info('INVPOPIF.indelitm_delete_item_oi: begin');
2043: END IF;
2044:
2045: stmt_num := 1;
2046:
2073: END IF;
2074: END LOOP;
2075:
2076: IF l_inv_debug_level IN(101, 102) THEN
2077: INVPUTLI.info('INVPOPIF.indelitm_delete_item_oi: end');
2078: END IF;
2079:
2080:
2081: RETURN (0);
2082:
2083: EXCEPTION
2084:
2085: WHEN OTHERS THEN
2086: err_text := SUBSTR('INVPOPIF.indelitm_delete_item_oi(' || stmt_num || ')' || SQLERRM, 1,240);
2087: RETURN (SQLCODE);
2088:
2089: END indelitm_delete_item_oi;
2090:
2256: -- MSIBKFV item number fetched using MSII inventory_item_id
2257: -- Both l_item_number and item_record.item_number cannot be NULL, hence NVL is not used
2258:
2259: IF l_inv_debug_level IN(101, 102) THEN
2260: INVPUTLI.info('INVPOPIF.update sync records: Item Number update' || item_record.item_number || ' ' || l_item_number || ' ' || l_item_id);
2261: END IF;
2262:
2263: IF ( l_updateable_item_number <> 'Y' ) THEN
2264: dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2262:
2263: IF ( l_updateable_item_number <> 'Y' ) THEN
2264: dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2265: fnd_global.prog_appl_id,fnd_global.conc_program_id,fnd_global.conc_request_id,item_record.transaction_id,
2266: 'INVPOPIF: Update to Item number not allowed',
2267: 'ITEM NUMBER',
2268: 'MTL_SYSTEM_ITEMS_INTERFACE',
2269: 'INV_ITEM_NUMBER_NO_UDPATE',
2270: l_err_text);
2280:
2281: IF ( isMasterOrg(item_record.organization_id) = 0 ) THEN
2282: dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2283: fnd_global.prog_appl_id,fnd_global.conc_program_id,fnd_global.conc_request_id,item_record.transaction_id,
2284: 'INVPOPIF: Update to Item number not allowed in child organization',
2285: 'ITEM NUMBER',
2286: 'MTL_SYSTEM_ITEMS_INTERFACE',
2287: 'INV_ITEM_NUMBER_ORG_NO_UDPATE',
2288: l_err_text);
2303: CLOSE c_fetch_by_item_number;
2304:
2305: IF (l_item_exist <> 0 AND l_item_exist <> l_item_id) THEN
2306: IF l_inv_debug_level IN(101, 102) THEN
2307: INVPUTLI.info('INVPOPIF.update sync records: Item Number update to duplicate case:' || item_record.item_number || ' ' || l_item_exist || ' ' || l_item_id);
2308: END IF;
2309: dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2310: fnd_global.prog_appl_id,fnd_global.conc_program_id,fnd_global.conc_request_id,item_record.transaction_id,
2311: 'INVPOPIF: Update to Duplicate Item number',
2307: INVPUTLI.info('INVPOPIF.update sync records: Item Number update to duplicate case:' || item_record.item_number || ' ' || l_item_exist || ' ' || l_item_id);
2308: END IF;
2309: dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2310: fnd_global.prog_appl_id,fnd_global.conc_program_id,fnd_global.conc_request_id,item_record.transaction_id,
2311: 'INVPOPIF: Update to Duplicate Item number',
2312: 'ITEM NUMBER',
2313: 'MTL_SYSTEM_ITEMS_INTERFACE',
2314: 'INV_UPDATE_TO_EXIST_ITEM_NAME',
2315: l_err_text);
2447: p_prog_appid,
2448: p_prog_id,
2449: p_request_id,
2450: cr.transaction_id,
2451: 'INVPOPIF: Invalid Item Catalog Group Name',
2452: 'ITEM_CATALOG_GROUP_NAME',
2453: 'MTL_SYSTEM_ITEMS_INTERFACE',
2454: 'INV_IOI_CATG_NAME_INVALID',
2455: x_err_text);
2527: END LOOP;
2528: END VALIDATE_RELEASED_ICC;
2529:
2530:
2531: END INVPOPIF;