[Home] [Help]
573: AND MIRI.SET_PROCESS_ID = #' || p_batch_id || q'#
574: AND MIRI.PROCESS_FLAG = 7
575: AND NOT EXISTS
576: (SELECT NULL
577: FROM MTL_SYSTEM_ITEMS_INTERFACE MSII
578: WHERE MSII.INVENTORY_ITEM_ID = MIRI.INVENTORY_ITEM_ID
579: AND MSII.ORGANIZATION_ID = MIRI.ORGANIZATION_ID
580: AND MSII.SET_PROCESS_ID = MIRI.SET_PROCESS_ID
581: AND MSII.PROCESS_FLAG = MIRI.PROCESS_FLAG)
600: ELSE MIRI.REVISION_ID
601: END) REVISION_ID, #' ||
602: l_item_rev_dl_id || q'# AS DATA_LEVEL_ID
603: FROM
604: MTL_SYSTEM_ITEMS_INTERFACE MSII,
605: MTL_ITEM_REVISIONS_INTERFACE MIRI
606: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
607: AND MSII.TEMPLATE_ID IS NOT NULL
608: AND MIRI.INVENTORY_ITEM_ID(+) = MSII.INVENTORY_ITEM_ID
624: NULL AS PK5_VALUE,
625: NULL AS REVISION_ID, #' ||
626: l_item_dl_id || q'# AS DATA_LEVEL_ID
627: FROM
628: MTL_SYSTEM_ITEMS_INTERFACE MSII
629: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
630: AND MSII.TEMPLATE_ID IS NOT NULL
631: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
632: AND MSII.PROCESS_FLAG = 7
643: NULL AS PK5_VALUE,
644: NULL AS REVISION_ID, #' ||
645: l_item_org_dl_id || q'# AS DATA_LEVEL_ID
646: FROM
647: MTL_SYSTEM_ITEMS_INTERFACE MSII
648: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
649: AND MSII.TEMPLATE_ID IS NOT NULL
650: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
651: AND MSII.PROCESS_FLAG = 7 #';
664: NULL AS PK5_VALUE,
665: NULL AS REVISION_ID, #' ||
666: l_item_gtin_dl_id || q'# AS DATA_LEVEL_ID
667: FROM
668: MTL_SYSTEM_ITEMS_INTERFACE MSII
669: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
670: AND MSII.TEMPLATE_ID IS NOT NULL
671: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
672: AND MSII.PROCESS_FLAG = 7
684: NULL AS PK5_VALUE,
685: NULL AS REVISION_ID, #' ||
686: l_item_gtin_multi_dl_id || q'# AS DATA_LEVEL_ID
687: FROM
688: MTL_SYSTEM_ITEMS_INTERFACE MSII
689: WHERE MSII.ITEM_CATALOG_GROUP_ID IS NOT NULL
690: AND MSII.TEMPLATE_ID IS NOT NULL
691: AND MSII.SET_PROCESS_ID = #' || p_batch_id || q'#
692: AND NVL(MSII.GDSN_OUTBOUND_ENABLED_FLAG, 'N') = 'Y'
1155: l_IOI_run_mode := 1;
1156: ELSIF ( p_transaction_type = 'UPDATE' ) then
1157: l_IOI_run_mode := 2;
1158:
1159: /* Need to get Item ID from MTL_SYSTEM_ITEMS_INTERFACE since ITEM_NUMBER
1160: could be passed with item record to Update_Item
1161: --
1162: -- Use passed record item id to retrieve updated item
1163: --
1198:
1199: IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
1200: x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
1201: x_Error_tbl(1).UNIQUE_ID := 1;
1202: x_Error_tbl(1).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1203: x_Error_tbl(1).MESSAGE_TEXT := SUBSTR(l_return_err,1,239);
1204: RETURN;
1205: END IF;
1206:
1219:
1220: IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
1221: x_return_status := l_return_status;
1222: x_Error_tbl(1).UNIQUE_ID := 1;
1223: x_Error_tbl(1).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1224: x_Error_tbl(1).MESSAGE_TEXT := SUBSTR(l_return_err,1,239);
1225: RETURN;
1226: END IF;
1227: END IF;
1319: , l_transaction_id
1320: , l_inventory_item_id
1321: , l_organization_id
1322: FROM
1323: MTL_SYSTEM_ITEMS_INTERFACE MSII,
1324: MTL_PARAMETERS MP
1325: WHERE set_process_id = l_set_process_id
1326: AND MP.MASTER_ORGANIZATION_ID = MP.ORGANIZATION_ID
1327: AND MP.ORGANIZATION_ID = MSII.ORGANIZATION_ID;
1344: , l_transaction_id
1345: , l_inventory_item_id
1346: , l_organization_id
1347: FROM
1348: MTL_SYSTEM_ITEMS_INTERFACE
1349: WHERE
1350: set_process_id = l_set_process_id;
1351: END IF; -- Bug 12635842
1352:
1478: IF ( l_return_status <> fnd_api.g_RET_STS_SUCCESS ) THEN
1479: x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
1480: l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
1481: x_Error_tbl(l_idx).UNIQUE_ID := 999;
1482: x_Error_tbl(l_idx).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1483: x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR(l_return_err,1,239);
1484: END IF;
1485:
1486: -- Do not insert message, if IOI is success.
1487: --
1488: /*
1489: l_idx := 1;
1490: x_Error_tbl(l_idx).UNIQUE_ID := 0;
1491: x_Error_tbl(l_idx).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1492: x_Error_tbl(l_idx).MESSAGE_TEXT :=
1493: 'Create_Item success. IOI tnx_id=' || to_char(l_transaction_id) ||
1494: ' inventory_item_id=' || to_char(l_inventory_item_id);
1495: */
1533: IF ( l_return_code <> 0
1534: AND l_idx = NVL( x_Error_tbl.COUNT, 0) ) THEN
1535: l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
1536: x_Error_tbl(l_idx).UNIQUE_ID := 1;
1537: x_Error_tbl(l_idx).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1538: x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR('Error during IOI run.'
1539: || ' Process_flag=' || to_char(l_process_flag)
1540: || ' Return_code=' || to_char(l_return_code)
1541: || ' Unexpexted error: ' || l_err_text,1,239);
1575: IF ( l_return_code <> 0
1576: AND l_idx = NVL( x_Error_tbl.COUNT, 0) ) THEN
1577: l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
1578: x_Error_tbl(l_idx).UNIQUE_ID := 1;
1579: x_Error_tbl(l_idx).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1580: x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR('Error during IOI run.'
1581: || ' Process_flag=' || to_char(l_process_flag)
1582: || ' Return_code=' || to_char(l_return_code)
1583: || ' Unexpexted error: ' || l_err_text,1,239);
1599: IF ( l_return_code <> 0 ) THEN
1600: x_return_status := fnd_api.g_RET_STS_UNEXP_ERROR;
1601: l_idx := NVL( x_Error_tbl.COUNT, 0) + 1;
1602: x_Error_tbl(l_idx).UNIQUE_ID := 999;
1603: x_Error_tbl(l_idx).TABLE_NAME := 'MTL_SYSTEM_ITEMS_INTERFACE';
1604: x_Error_tbl(l_idx).MESSAGE_TEXT := SUBSTR(l_err_text,1,239);
1605: END IF;
1606: ----------------------------------------------------------
1607: -- Control the API commit through the IOI
4036: END IF;
4037:
4038: -- Insert item row into MSII for further processing by IOI.
4039:
4040: INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE(
4041: process_flag
4042: ,set_process_id
4043: ,transaction_type
4044: ,ORGANIZATION_ID