[Home] [Help]
1: PACKAGE BODY INVPOPIF AS
2: /* $Header: INVPOPIB.pls 120.45 2008/01/25 14:52:24 ajmittal 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.45 2008/01/25 14:52:24 ajmittal 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;
97:
98: END IF;
99:
100: BEGIN
101: ret_status := INVPOPIF.inopinp_open_interface_process(
102: org_id => p_org_id,
103: all_org => p_all_org,
104: val_item_flag => p_val_item_flag,
105: pro_item_flag => p_pro_item_flag,
279: BEGIN
280: /*Added for bug 6372595*/
281: IF source_org_id <> -999
282: THEN
283: INVPOPIF.g_source_org := FALSE ;
284: END IF ;
285:
286: IF l_inv_debug_level IN(101, 102) THEN
287: INVPUTLI.info('INVPOPIF: *** Starting a new IOI process: run_mode='|| TO_CHAR(run_mode) ||' all_org='|| TO_CHAR(all_org));
283: INVPOPIF.g_source_org := FALSE ;
284: END IF ;
285:
286: IF l_inv_debug_level IN(101, 102) THEN
287: INVPUTLI.info('INVPOPIF: *** Starting a new IOI process: run_mode='|| TO_CHAR(run_mode) ||' all_org='|| TO_CHAR(all_org));
288: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: org_id = '|| TO_CHAR(org_id) || 'Default flag=' || To_Char(default_flag) );
289: END IF;
290: /*
291: ** Make sure transaction type is in upper case
284: END IF ;
285:
286: IF l_inv_debug_level IN(101, 102) THEN
287: INVPUTLI.info('INVPOPIF: *** Starting a new IOI process: run_mode='|| TO_CHAR(run_mode) ||' all_org='|| TO_CHAR(all_org));
288: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: org_id = '|| TO_CHAR(org_id) || 'Default flag=' || To_Char(default_flag) );
289: END IF;
290: /*
291: ** Make sure transaction type is in upper case
292: */
295: --IF fnd_global.conc_program_id <> -1 THEN Bug:3547401
296: IF NVL(prog_id,-1) <> -1 THEN
297:
298: IF l_inv_debug_level IN(101, 102) THEN
299: INVPUTLI.info('INVPOPIF: Gathering interface table stats');
300: END IF;
301:
302: --3515652: Collect stats only if no. records > 50
303: SELECT count(*) INTO l_records
315: END IF;
316: END IF;
317:
318: IF l_inv_debug_level IN(101, 102) THEN
319: INVPUTLI.info('INVPOPIF: Gathering interface table stats done');
320: END IF;
321:
322: END IF;
323: --End 2698140 : Gather stats before running the IOI
407: prog_appid,
408: prog_id,
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);
431: prog_appid,
432: prog_id,
433: request_id,
434: cr.transaction_id,
435: 'INVPOPIF: Invalid Organization ID',
436: 'ORGANIZATION_ID',
437: 'MTL_ITEM_REVISIONS_INTERFACE',
438: 'INV_IOI_ORG_NO_EXIST',
439: err_text);
548: where miri.organization_id = mp1.master_organization_id);
549:
550: --Creating Master Items
551: IF l_inv_debug_level IN(101, 102) THEN
552: INVPUTLI.info('INVPOPIF all_org=1: Calling create process for masters');
553: END IF;
554: ret_code_create := INVPOPIF.inopinp_OI_process_create (
555: NULL
556: ,1
550: --Creating Master Items
551: IF l_inv_debug_level IN(101, 102) THEN
552: INVPUTLI.info('INVPOPIF all_org=1: Calling create process for masters');
553: END IF;
554: ret_code_create := INVPOPIF.inopinp_OI_process_create (
555: NULL
556: ,1
557: ,val_item_flag
558: ,pro_item_flag
618: from mtl_parameters mp1
619: where miri.organization_id = mp1.master_organization_id);
620:
621: IF l_inv_debug_level IN(101, 102) THEN
622: INVPUTLI.info('INVPOPIF all_org=1: Calling create process for childs');
623: END IF;
624: --Creating Child Items
625: ret_code_create := INVPOPIF.inopinp_OI_process_create (
626: NULL,
621: IF l_inv_debug_level IN(101, 102) THEN
622: INVPUTLI.info('INVPOPIF all_org=1: Calling create process for childs');
623: END IF;
624: --Creating Child Items
625: ret_code_create := INVPOPIF.inopinp_OI_process_create (
626: NULL,
627: 1,
628: val_item_flag,
629: pro_item_flag,
664: FETCH c_interface_revs INTO mtli_count;
665: CLOSE c_interface_revs;
666:
667: IF l_inv_debug_level IN(101, 102) THEN
668: INVPUTLI.info('INVPOPIF all_org<>1: Calling create process');
669: END IF;
670:
671: IF (mtl_count <> 0 or mtli_count <> 0) THEN
672: ret_code_create := INVPOPIF.inopinp_OI_process_create (
668: INVPUTLI.info('INVPOPIF all_org<>1: Calling create process');
669: END IF;
670:
671: IF (mtl_count <> 0 or mtli_count <> 0) THEN
672: ret_code_create := INVPOPIF.inopinp_OI_process_create (
673: org_id,
674: all_org,
675: val_item_flag,
676: pro_item_flag,
747: from mtl_parameters mp1
748: where miri.organization_id = mp1.master_organization_id);
749:
750: --Update master Items.
751: ret_code_update := INVPOPIF.inopinp_OI_process_update (
752: NULL,
753: 1,
754: val_item_flag,
755: pro_item_flag,
812: from mtl_parameters mp1
813: where miri.organization_id <> mp1.master_organization_id);
814:
815: --Updating the child records.
816: ret_code_update := INVPOPIF.inopinp_OI_process_update (
817: NULL,
818: 1,
819: val_item_flag,
820: pro_item_flag,
855: FETCH c_interface_revs INTO mtli_count;
856: CLOSE c_interface_revs;
857:
858: IF (mtl_count <> 0 or mtli_count <> 0) THEN
859: ret_code_update := INVPOPIF.inopinp_OI_process_update (
860: org_id,
861: all_org,
862: val_item_flag,
863: pro_item_flag,
964: ret_code := 1;
965: END IF;
966:
967: IF l_inv_debug_level IN(101, 102) THEN
968: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Before Sync IM Index');
969: END IF;
970:
971: --Start : Sync iM index changes
972: IF pro_item_flag = 1 --Bug 4667985
979: --
980: -- Sync processed rows with item star table
981: --
982: IF l_inv_debug_level IN(101, 102) THEN
983: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI');
984: END IF;
985:
986: --Bug: 2718703 checking for ENI product before calling their package
987: --This check has been moved to INV_ENI_ITEMS_STAR_PKG
995: ,x_msg_data => l_msg_data);
996:
997:
998: IF NOT ( l_return_status = FND_API.g_RET_STS_SUCCESS ) THEN
999: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Synch Up of ENI tables failed');
1000: dumm_status := INVPUOPI.mtl_log_interface_err (
1001: ORG_ID => -1,
1002: USER_ID =>user_id,
1003: LOGIN_ID =>login_id,
1011: MSG_NAME =>'INV_IOI_ERR',
1012: ERR_TEXT =>err_text);
1013:
1014: if ( dumm_status < 0 ) then
1015: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Logging Error');
1016: end if;
1017: END IF; --l_return_status
1018: EXCEPTION
1019: WHEN OTHERS THEN
1035:
1036: END;
1037:
1038: IF l_inv_debug_level IN(101, 102) THEN
1039: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Start Raising Business Events');
1040: END IF;
1041:
1042: --R12: Business Event Enhancement:
1043: --Raise events for EGO Bulk Load and Excel Import
1045:
1046: --Populate Item Bulkload Recs for items and revisions
1047: BEGIN
1048: IF l_inv_debug_level IN(101, 102) THEN
1049: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Insert in to bulkloadrecs for Item');
1050: END IF;
1051:
1052: INSERT INTO MTL_ITEM_BULKLOAD_RECS(
1053: REQUEST_ID
1090: ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_BULKLOAD_EVENT'
1091: ,p_dml_type => 'BULK');
1092:
1093: IF l_inv_debug_level IN(101, 102) THEN
1094: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Raised Item Bulkload Event');
1095: END IF;
1096:
1097: EXCEPTION
1098: WHEN OTHERS THEN
1095: END IF;
1096:
1097: EXCEPTION
1098: WHEN OTHERS THEN
1099: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while raising Item Change Event',1,240);
1100: IF l_inv_debug_level IN(101, 102) THEN
1101: INVPUTLI.info(err_msg);
1102: END IF;
1103: END;
1104: END IF;
1105:
1106:
1107: IF l_inv_debug_level IN(101, 102) THEN
1108: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Insert in to bulkloadrecs for Revision.');
1109: END IF;
1110:
1111: INSERT INTO MTL_ITEM_BULKLOAD_RECS(
1112: REQUEST_ID
1146: ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT'
1147: ,p_dml_type => 'BULK');
1148:
1149: IF l_inv_debug_level IN(101, 102) THEN
1150: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Raised Revision Bulkload Event');
1151: END IF;
1152:
1153: EXCEPTION
1154: WHEN OTHERS THEN
1151: END IF;
1152:
1153: EXCEPTION
1154: WHEN OTHERS THEN
1155: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while raising REV Change Event',1,240);
1156: IF l_inv_debug_level IN(101, 102) THEN
1157: INVPUTLI.info(err_msg);
1158: END IF;
1159: END;
1162:
1163:
1164: EXCEPTION
1165: WHEN OTHERS THEN
1166: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while inserting records in MTL_ITEM_BULKLOAD_RECS',1,240);
1167: IF l_inv_debug_level IN(101, 102) THEN
1168: INVPUTLI.info(err_msg);
1169: END IF;
1170: END;
1177: ,p_xset_id => xset_id
1178: ,p_entity_type => 'ITEM'
1179: ,p_dml_type => 'BULK');
1180: IF l_inv_debug_level IN(101, 102) THEN
1181: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Calling ICX Bulkload Event');
1182: END IF;
1183: EXCEPTION
1184: WHEN OTHERS THEN
1185: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||'while invoking ICX APIs',1,240);
1181: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: ' || 'Calling ICX Bulkload Event');
1182: END IF;
1183: EXCEPTION
1184: WHEN OTHERS THEN
1185: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||'while invoking ICX APIs',1,240);
1186: IF l_inv_debug_level IN(101, 102) THEN
1187: INVPUTLI.info(err_msg);
1188: END IF;
1189: END;
1198: --Delete processed records from IOI tables.
1199: --Bug: 5473976 Rows will not be deleted if control is coming from Import Workbench
1200: IF (del_rec_flag = 1 AND (NVL(INV_EGO_REVISION_VALIDATE.Get_Process_Control,'!') <> 'EGO_ITEM_BULKLOAD')) THEN
1201: IF l_inv_debug_level IN(101, 102) THEN
1202: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPOPIF.indelitm_delete_item_oi');
1203: END IF;
1204:
1205: ret_code := INVPOPIF.indelitm_delete_item_oi (err_text => err_msg,
1206: com_flag => commit_flag,
1201: IF l_inv_debug_level IN(101, 102) THEN
1202: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPOPIF.indelitm_delete_item_oi');
1203: END IF;
1204:
1205: ret_code := INVPOPIF.indelitm_delete_item_oi (err_text => err_msg,
1206: com_flag => commit_flag,
1207: xset_id => xset_id);
1208:
1209: IF l_inv_debug_level IN(101, 102) THEN
1206: com_flag => commit_flag,
1207: xset_id => xset_id);
1208:
1209: IF l_inv_debug_level IN(101, 102) THEN
1210: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPOPIF.indelitm_delete_item_oi: ret_code=' || ret_code);
1211: END IF;
1212:
1213: ELSE
1214: /****Added for bug 5194369
1234: -- Process Item Category Open Interface records
1235: --
1236:
1237: IF l_inv_debug_level IN(101, 102) THEN
1238: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: calling INV_ITEM_CATEGORY_OI.process_Item_Category_records '||pro_item_flag);
1239: END IF;
1240:
1241: SELECT COUNT(1) INTO mtl_count
1242: FROM mtl_item_categories_interface mici
1259: , p_login_id => login_id);
1260: END IF;
1261:
1262: IF l_inv_debug_level IN(101, 102) THEN
1263: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: done INV_ITEM_CATEGORY_OI.process_Item_Category_records: ret_code=' || ret_code);
1264: END IF;
1265:
1266: /* SET return code to that of last error, IF any */
1267:
1276: --
1277: -- Process Item Catalog group element values open Interface records
1278: --
1279: IF l_inv_debug_level IN(101, 102) THEN
1280: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: calling INV_ITEM_CATALOG_ELEM_PUB.process_Item_Catalog_grp_recs');
1281: END IF;
1282:
1283: SELECT COUNT(1) INTO mtl_count
1284: FROM mtl_desc_elem_val_interface mdei
1302: ret_code_grp := 0;
1303: END IF;
1304:
1305: IF l_inv_debug_level IN(101, 102) THEN
1306: INVPUTLI.info('INVPOPIF.inopinp_open_interface_process: done INV_ITEM_CATALOG_ELEM_PUB.process_Item_Catalog_grp_recs: ret_code=' || ret_code_grp);
1307: END IF;
1308:
1309: IF (ret_code_grp <> 0) THEN
1310: ret_code := ret_code_grp;
1348:
1349: BEGIN
1350:
1351: IF l_inv_debug_level IN(101, 102) THEN
1352: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update : begin org_id: ' || TO_CHAR(org_id));
1353: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
1354: END IF;
1355:
1356: --Validate Catalog_Group_name
1349: BEGIN
1350:
1351: IF l_inv_debug_level IN(101, 102) THEN
1352: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update : begin org_id: ' || TO_CHAR(org_id));
1353: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
1354: END IF;
1355:
1356: --Validate Catalog_Group_name
1357: UPDATE_ITEM_CATALOG_ID(
1364: ,x_err_text => err_text);
1365:
1366:
1367: IF l_inv_debug_level IN(101, 102) THEN
1368: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVNIRIS.change_policy_check');
1369: END IF;
1370:
1371: ret_code := INVNIRIS.change_policy_check (
1372: org_id => org_id,
1379: err_text => err_msg,
1380: xset_id => xset_id);
1381:
1382: IF l_inv_debug_level IN(101, 102) THEN
1383: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVNIRIS.change_policy_check RETURN'||ret_code);
1384: INVPUTLI.info('INVNIRIS.change_policy_check->l'||err_msg);
1385: END IF;
1386:
1387: IF (ret_code <> 0) THEN
1391: END IF;
1392:
1393: IF default_flag = 1 THEN
1394: IF l_inv_debug_level IN(101, 102) THEN
1395: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD1B.mtl_pr_assign_item_data_update');
1396: END IF;
1397:
1398:
1399:
1411: END IF;
1412:
1413: IF (val_item_flag = 1) THEN
1414: IF (ret_code <> 0) THEN
1415: err_msg := 'INVPOPIF.inopinp_OI_process_update: error in ASSIGN phase of UPDATE;' ||
1416: ' Please check mtl_interface_errors table ' || err_msg;
1417: goto ERROR_LABEL;
1418: END IF;
1419:
1418: END IF;
1419:
1420: --Bug:3777954 added call to new pkg/processing for NIR required items (for EGO)
1421: IF l_inv_debug_level IN(101, 102) THEN
1422: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item');
1423: END IF;
1424:
1425: ret_code := INVNIRIS.mtl_validate_nir_item (
1426: org_id => org_id,
1434: xset_id => xset_id);
1435:
1436:
1437: IF l_inv_debug_level IN(101, 102) THEN
1438: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1439: END IF;
1440:
1441: IF (ret_code <> 0) THEN
1442: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in NIR ASSIGN phase of UPDATE;' ||
1438: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1439: END IF;
1440:
1441: IF (ret_code <> 0) THEN
1442: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in NIR ASSIGN phase of UPDATE;' ||
1443: ' Please check mtl_interface_errors table ' || err_msg;
1444: goto ERROR_LABEL;
1445: END IF;
1446:
1446:
1447: --Bug:3777954 call ends
1448:
1449: IF l_inv_debug_level IN(101, 102) THEN
1450: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD1B.mtl_pr_validate_item_update');
1451: END IF;
1452:
1453: ret_code := INVUPD1B.mtl_pr_validate_item_update (
1454: org_id => org_id,
1461: err_text => err_msg,
1462: xset_id => xset_id);
1463:
1464: IF (ret_code <> 0) THEN
1465: err_msg := 'INVPOPIF.inopinp_OI_process_update: error in VALIDATE phase of UPDATE;' ||
1466: ' Please check mtl_interface_errors table ' || err_msg;
1467: goto ERROR_LABEL;
1468: END IF;
1469:
1471:
1472: IF (pro_item_flag = 1) THEN
1473:
1474: IF l_inv_debug_level IN(101, 102) THEN
1475: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INVUPD2B.inproit_process_item_update');
1476: END IF;
1477:
1478: ret_code := INVUPD2B.inproit_process_item_update (
1479: prg_appid => prog_appid,
1486: table_name => table_name,
1487: xset_id => xset_id);
1488:
1489: IF l_inv_debug_level IN(101, 102) THEN
1490: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: done INVUPD2B.inproit_process_item_update: ret_code=' || ret_code);
1491: END IF;
1492:
1493: IF (ret_code <> 0) THEN
1494: err_msg := 'INVPOPIF.inopinp_OI_process_update: error in PROCESS phase of UPDATE;' ||
1490: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: done INVUPD2B.inproit_process_item_update: ret_code=' || ret_code);
1491: END IF;
1492:
1493: IF (ret_code <> 0) THEN
1494: err_msg := 'INVPOPIF.inopinp_OI_process_update: error in PROCESS phase of UPDATE;' ||
1495: ' Please check mtl_interface_errors table ' || err_msg;
1496: goto ERROR_LABEL;
1497: END IF;
1498:
1500: -- Sync processed rows with item star table
1501: --
1502:
1503: IF l_inv_debug_level IN(101, 102) THEN
1504: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI');
1505: END IF;
1506:
1507: --Bug: 2718703 checking for ENI product before calling their package
1508: --This check has been moved to INV_ENI_ITEMS_STAR_PKG
1520:
1521: EXCEPTION
1522:
1523: WHEN OTHERS THEN
1524: err_text := substr('INVPOPIF.inopinp_OI_process_update ' || SQLERRM , 1,240);
1525: IF l_inv_debug_level IN(101, 102) THEN
1526: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: About to rollback.');
1527: END IF;
1528: ROLLBACK;
1522:
1523: WHEN OTHERS THEN
1524: err_text := substr('INVPOPIF.inopinp_OI_process_update ' || SQLERRM , 1,240);
1525: IF l_inv_debug_level IN(101, 102) THEN
1526: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: About to rollback.');
1527: END IF;
1528: ROLLBACK;
1529: RETURN (ret_code);
1530: END inopinp_OI_process_update;
1577: trans_id NUMBER;
1578:
1579: BEGIN
1580: IF l_inv_debug_level IN(101, 102) THEN
1581: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create : begin org_id: ' || TO_CHAR(org_id));
1582: END IF;
1583:
1584: IF l_inv_debug_level IN(101, 102) THEN
1585: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
1581: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create : begin org_id: ' || TO_CHAR(org_id));
1582: END IF;
1583:
1584: IF l_inv_debug_level IN(101, 102) THEN
1585: INVPUTLI.info('INVPOPIF.inopinp_OI_process_update: calling UPDATE_ITEM_CATALOG_ID');
1586: END IF;
1587:
1588: --Validate Catalog_Group_name
1589: UPDATE_ITEM_CATALOG_ID(
1595: ,p_login_id => login_id
1596: ,x_err_text => err_text);
1597:
1598: IF l_inv_debug_level IN(101, 102) THEN
1599: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPASGI.mtl_pr_assign_item_data');
1600: END IF;
1601:
1602: ret_code := INVPASGI.mtl_pr_assign_item_data (
1603: org_id => org_id,
1611: xset_id => xset_id,
1612: default_flag => default_flag);
1613:
1614: IF l_inv_debug_level IN(101, 102) THEN
1615: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPASGI.mtl_pr_assign_item_data: ret_code=' || ret_code || ' err_msg=' || err_msg);
1616: END IF;
1617:
1618: IF (ret_code <> 0) THEN
1619: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in ASSIGN phase of CREATE;' ||
1615: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPASGI.mtl_pr_assign_item_data: ret_code=' || ret_code || ' err_msg=' || err_msg);
1616: END IF;
1617:
1618: IF (ret_code <> 0) THEN
1619: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in ASSIGN phase of CREATE;' ||
1620: ' Please check mtl_interface_errors table ' || err_msg;
1621: goto ERROR_LABEL;
1622: END IF;
1623:
1624: IF (val_item_flag = 1) THEN
1625: --Bug:3777954 added call to new pkg/processing for NIR required items (for EGO)
1626:
1627: IF l_inv_debug_level IN(101, 102) THEN
1628: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item');
1629: END IF;
1630:
1631: ret_code := INVNIRIS.mtl_validate_nir_item (
1632: org_id => org_id,
1639: err_text => err_msg,
1640: xset_id => xset_id);
1641:
1642: IF l_inv_debug_level IN(101, 102) THEN
1643: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1644: END IF;
1645:
1646: IF (ret_code <> 0) THEN
1647: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in NIR ASSIGN phase of CREATE;' ||
1643: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVNIRIS.mtl_validate_nir_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1644: END IF;
1645:
1646: IF (ret_code <> 0) THEN
1647: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in NIR ASSIGN phase of CREATE;' ||
1648: ' Please check mtl_interface_errors table ' || err_msg;
1649: goto ERROR_LABEL;
1650: END IF;
1651:
1651:
1652: --Bug:3777954 call ends
1653:
1654: IF l_inv_debug_level IN(101, 102) THEN
1655: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPVALI.mtl_pr_validate_item');
1656: END IF;
1657:
1658: ret_code := INVPVALI.mtl_pr_validate_item (
1659: org_id => org_id,
1666: err_text => err_msg,
1667: xset_id => xset_id);
1668:
1669: IF l_inv_debug_level IN(101, 102) THEN
1670: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPVALI.mtl_pr_validate_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1671: END IF;
1672:
1673: IF (ret_code <> 0) THEN
1674: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in VALIDATE phase of CREATE;'||
1670: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPVALI.mtl_pr_validate_item: ret_code=' || ret_code || ' err_msg=' || err_msg);
1671: END IF;
1672:
1673: IF (ret_code <> 0) THEN
1674: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in VALIDATE phase of CREATE;'||
1675: ' Please check mtl_interface_errors table ' || err_msg;
1676: goto ERROR_LABEL;
1677: END IF;
1678:
1681:
1682: IF (pro_item_flag = 1) THEN
1683:
1684: IF l_inv_debug_level IN(101, 102) THEN
1685: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INVPPROC.inproit_process_item');
1686: END IF;
1687:
1688: ret_code := INVPPROC.inproit_process_item (
1689: prg_appid => prog_appid,
1696: table_name => table_name,
1697: xset_id => xset_id);
1698:
1699: IF l_inv_debug_level IN(101, 102) THEN
1700: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: done INVPPROC.inproit_process_item: ret_code=' || ret_code);
1701: END IF;
1702:
1703: IF (ret_code <> 0) THEN
1704:
1726: WHERE process_flag = 4
1727: AND set_process_id = xset_id
1728: AND transaction_type = 'CREATE';
1729:
1730: err_msg := 'INVPOPIF.inopinp_OI_process_create: error in PROCESS phase of CREATE;'||
1731: ' Please check mtl_interface_errors table ' || err_msg;
1732:
1733: goto ERROR_LABEL;
1734: END IF;
1736: --
1737: -- Sync processed rows with item star table
1738: --
1739: IF l_inv_debug_level IN(101, 102) THEN
1740: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: calling INV_ENI_ITEMS_STAR_PKG.Sync_Star_Items_From_IOI');
1741: END IF;
1742:
1743: --Bug: 2718703 checking for ENI product before calling their package
1744: --This check has been moved to INV_ENI_ITEMS_STAR_PKG
1767: EXCEPTION
1768: WHEN NO_DATA_FOUND THEN
1769: NULL; --Child records not created, no need to call JAI api's
1770: WHEN OTHERS THEN
1771: err_msg := SUBSTR('INVPOPIF: Error:' ||SQLERRM ||' while IL API call ',1,240);
1772: IF l_inv_debug_level IN(101, 102) THEN
1773: INVPUTLI.info(err_msg);
1774: END IF;
1775: END;
1788: -- Parameter ret_code is defaulted to 1, which is passed
1789: -- back for oracle error in UPDATE st.
1790:
1791: WHEN others THEN
1792: err_text := substr('INVPOPIF.inopinp_OI_process_create ' || SQLERRM , 1,240);
1793: IF l_inv_debug_level IN(101, 102) THEN
1794: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: About to rollback.');
1795: END IF;
1796: ROLLBACK;
1790:
1791: WHEN others THEN
1792: err_text := substr('INVPOPIF.inopinp_OI_process_create ' || SQLERRM , 1,240);
1793: IF l_inv_debug_level IN(101, 102) THEN
1794: INVPUTLI.info('INVPOPIF.inopinp_OI_process_create: About to rollback.');
1795: END IF;
1796: ROLLBACK;
1797: RETURN (ret_code);
1798: END inopinp_OI_process_create;
1814: l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667453
1815: BEGIN
1816:
1817: IF l_inv_debug_level IN(101, 102) THEN
1818: INVPUTLI.info('INVPOPIF.indelitm_delete_item_oi: begin');
1819: END IF;
1820:
1821: stmt_num := 1;
1822:
1849: END IF;
1850: END LOOP;
1851:
1852: IF l_inv_debug_level IN(101, 102) THEN
1853: INVPUTLI.info('INVPOPIF.indelitm_delete_item_oi: end');
1854: END IF;
1855:
1856:
1857: RETURN (0);
1858:
1859: EXCEPTION
1860:
1861: WHEN OTHERS THEN
1862: err_text := SUBSTR('INVPOPIF.indelitm_delete_item_oi(' || stmt_num || ')' || SQLERRM, 1,240);
1863: RETURN (SQLCODE);
1864:
1865: END indelitm_delete_item_oi;
1866:
2032: -- MSIBKFV item number fetched using MSII inventory_item_id
2033: -- Both l_item_number and item_record.item_number cannot be NULL, hence NVL is not used
2034:
2035: IF l_inv_debug_level IN(101, 102) THEN
2036: INVPUTLI.info('INVPOPIF.update sync records: Item Number update' || item_record.item_number || ' ' || l_item_number || ' ' || l_item_id);
2037: END IF;
2038:
2039: IF ( l_updateable_item_number <> 'Y' ) THEN
2040: dumm_status := INVPUOPI.mtl_log_interface_err(-1,fnd_global.user_id,fnd_global.login_id,
2038:
2039: IF ( l_updateable_item_number <> 'Y' ) THEN
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);
2056:
2057: IF ( isMasterOrg(item_record.organization_id) = 0 ) THEN
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);
2079: CLOSE c_fetch_by_item_number;
2080:
2081: IF (l_item_exist <> 0 AND l_item_exist <> l_item_id) THEN
2082: IF l_inv_debug_level IN(101, 102) THEN
2083: INVPUTLI.info('INVPOPIF.update sync records: Item Number update to duplicate case:' || item_record.item_number || ' ' || l_item_exist || ' ' || l_item_id);
2084: 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',
2083: INVPUTLI.info('INVPOPIF.update sync records: Item Number update to duplicate case:' || item_record.item_number || ' ' || l_item_exist || ' ' || l_item_id);
2084: 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);
2221: p_prog_appid,
2222: p_prog_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);
2249: END LOOP;
2250:
2251: END UPDATE_ITEM_CATALOG_ID;
2252:
2253: END INVPOPIF;