[Home] [Help]
2: /* $Header: AHLVMMGB.pls 120.0.12020000.3 2013/01/03 09:30:28 sansatpa noship $*/
3: ------------------------------------
4: -- Common constants and variables --
5: ------------------------------------
6: l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
7: l_log_statement NUMBER := fnd_log.level_statement;
8: l_log_procedure NUMBER := fnd_log.level_procedure;
9: l_log_error NUMBER := fnd_log.level_error;
10: l_log_unexpected NUMBER := fnd_log.level_unexpected;
3: ------------------------------------
4: -- Common constants and variables --
5: ------------------------------------
6: l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
7: l_log_statement NUMBER := fnd_log.level_statement;
8: l_log_procedure NUMBER := fnd_log.level_procedure;
9: l_log_error NUMBER := fnd_log.level_error;
10: l_log_unexpected NUMBER := fnd_log.level_unexpected;
11: l_log_exception NUMBER := fnd_log.level_exception;
4: -- Common constants and variables --
5: ------------------------------------
6: l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
7: l_log_statement NUMBER := fnd_log.level_statement;
8: l_log_procedure NUMBER := fnd_log.level_procedure;
9: l_log_error NUMBER := fnd_log.level_error;
10: l_log_unexpected NUMBER := fnd_log.level_unexpected;
11: l_log_exception NUMBER := fnd_log.level_exception;
12: G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
5: ------------------------------------
6: l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
7: l_log_statement NUMBER := fnd_log.level_statement;
8: l_log_procedure NUMBER := fnd_log.level_procedure;
9: l_log_error NUMBER := fnd_log.level_error;
10: l_log_unexpected NUMBER := fnd_log.level_unexpected;
11: l_log_exception NUMBER := fnd_log.level_exception;
12: G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
13:
6: l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
7: l_log_statement NUMBER := fnd_log.level_statement;
8: l_log_procedure NUMBER := fnd_log.level_procedure;
9: l_log_error NUMBER := fnd_log.level_error;
10: l_log_unexpected NUMBER := fnd_log.level_unexpected;
11: l_log_exception NUMBER := fnd_log.level_exception;
12: G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
13:
14: G_DELIVERY_DETAIL_ID NUMBER;
7: l_log_statement NUMBER := fnd_log.level_statement;
8: l_log_procedure NUMBER := fnd_log.level_procedure;
9: l_log_error NUMBER := fnd_log.level_error;
10: l_log_unexpected NUMBER := fnd_log.level_unexpected;
11: l_log_exception NUMBER := fnd_log.level_exception;
12: G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
13:
14: G_DELIVERY_DETAIL_ID NUMBER;
15:
170: BEGIN
171:
172: SAVEPOINT MM_CHECK_AVAILABILITY_SP;
173: IF (L_LOG_PROCEDURE >= L_LOG_CURRENT_LEVEL) THEN
174: FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG || '.begin', 'At the start of the PLSQL procedure CHECK_AVAILABILITY');
175: END IF;
176:
177: -- Initialize API return status to success
178: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
318: BEGIN
319:
320: SAVEPOINT MM_SCHEDULE_MATERIALS_SP;
321: IF (L_LOG_PROCEDURE >= L_LOG_CURRENT_LEVEL) THEN
322: FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG || '.begin', 'At the start of the PLSQL procedure SCHEDULE_MATERIALS');
323: END IF;
324:
325: -- Initialize API return status to success
326: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
455:
456: BEGIN
457: SAVEPOINT MM_SCHEDULE_VISIT_MATERIALS;
458: IF (L_LOG_PROCEDURE >= L_LOG_CURRENT_LEVEL) THEN
459: FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG || '.begin', 'At the start of the PLSQL procedure SCHEDULE_MATERIALS');
460: END IF;
461:
462: -- Initialize API return status to success
463: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
567:
568: BEGIN
569:
570: IF (l_log_procedure >= l_log_current_level) THEN
571: FND_LOG.string(FND_LOG.level_procedure, L_DEBUG || '.begin','At the start of PLSQL procedure');
572: END IF;
573:
574: -- Query to fetch the requested quantity for the demand
575: SELECT requested_quantity
625: l_available_date := NULL;
626: END IF;
627:
628: IF (l_log_procedure >= l_log_current_level) THEN
629: fnd_log.string(l_log_procedure, L_DEBUG || '.end', 'At the end of PL SQL procedure');
630: END IF;
631:
632: RETURN l_available_date;
633:
708:
709: -- Standard start of API savepoint
710: SAVEPOINT CREATE_MOVE_ORDER_SP;
711: IF (l_log_procedure >= l_log_current_level) THEN
712: FND_LOG.string(l_log_procedure, L_DEBUG || '.begin',
713: 'At the start of the PLSQL procedure CREATE_MOVE_ORDER');
714: END IF;
715:
716: -- Initialize API return status to success
748:
749: --Throw error when the demand is not AOG
750: IF(l_aog <> 'Y') THEN
751: IF (l_log_statement >= l_log_current_level) THEN
752: FND_LOG.string(l_log_statement, L_DEBUG,
753: 'The demand is not AOG');
754: END IF;
755:
756: FND_MESSAGE.set_name('AHL','AHL_MM_NO_MO_AOG');
760:
761: --Throw error when demand location is not defined
762: IF(l_dmd_loc_id IS NULL) THEN
763: IF (l_log_statement >= l_log_current_level) THEN
764: FND_LOG.string(l_log_statement, L_DEBUG,
765: 'Visit Locator is not defined to create move order');
766: END IF;
767:
768: FND_MESSAGE.set_name('AHL','AHL_MM_NO_LOCATOR');
837: --Write in Log and exit loop when deleting reservation fails
838: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)
839: THEN
840: IF (l_log_statement >= l_log_current_level) THEN
841: FND_LOG.string(l_log_statement, L_DEBUG,
842: 'After CANCEL_RESERVATION, Return Status is :' || x_return_status);
843: END IF;
844: RAISE FND_API.G_EXC_ERROR;
845: END IF;
870: l_from_line_tbl(i).serial_number_end := p_supply_tbl_type(i).Serial_Number;
871: END LOOP;
872:
873: IF (l_log_statement >= l_log_current_level) THEN
874: FND_LOG.string(l_log_statement,
875: L_DEBUG,
876: 'Calling INV_MOVE_ORDER_PUB.PROCESS_MOVE_ORDER');
877: END IF;
878:
896: OR l_to_hdr_rec.header_id IS NULL
897: OR l_to_line_tbl.COUNT <= 0)
898: THEN
899: IF (l_log_statement >= l_log_current_level) THEN
900: FND_LOG.string(l_log_statement, L_DEBUG,
901: 'After PROCESS_MOVE_ORDER, Return Status is :' || x_return_status);
902: END IF;
903: RAISE FND_API.G_EXC_ERROR;
904: END IF;
906: FOR i in p_supply_tbl_type.FIRST .. p_supply_tbl_type.LAST
907: LOOP
908:
909: IF (l_log_statement >= l_log_current_level) THEN
910: FND_LOG.string(l_log_statement, L_DEBUG,
911: 'Calling INV_PPENGINE_PVT.CREATE_SUGGESTIONS');
912: END IF;
913:
914: -- Creates pick suggestions and also suggests serial numbers
932: --Write in Log and exit loop when create suggestion fails
933: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)
934: THEN
935: IF (l_log_statement >= l_log_current_level) THEN
936: FND_LOG.string(l_log_statement, L_DEBUG,
937: 'After CREATE_SUGGESTIONS, Return Status is :' || x_return_status);
938: END IF;
939: RAISE FND_API.G_EXC_ERROR;
940: END IF;
1004: WHERE MMTT.transaction_temp_id = MSNT.transaction_temp_id
1005: AND MMTT.move_order_line_id = l_to_line_tbl(i).line_id;
1006:
1007: IF (l_log_statement >= l_log_current_level) THEN
1008: FND_LOG.string(l_log_statement, L_DEBUG,
1009: 'l_temp_txn_id: ' || l_temp_txn_id
1010: || ', l_fm_serial: ' || l_fm_serial
1011: || ', l_to_serial: ' || l_to_serial);
1012:
1009: 'l_temp_txn_id: ' || l_temp_txn_id
1010: || ', l_fm_serial: ' || l_fm_serial
1011: || ', l_to_serial: ' || l_to_serial);
1012:
1013: FND_LOG.string(l_log_statement, L_DEBUG,
1014: 'Calling inv_unmark_rsv_serial for serial: ' || l_fm_serial);
1015: END IF;
1016:
1017: SERIAL_CHECK.inv_unmark_rsv_serial (from_serial_number => l_fm_serial
1024: , p_update_reservation => 'F'
1025: );
1026:
1027: IF (l_log_statement >= l_log_current_level) THEN
1028: FND_LOG.string(l_log_statement, L_DEBUG,
1029: 'Deleting mtl_serial_numbers_temp with temp_txn_id: ' || l_temp_txn_id);
1030: END IF;
1031:
1032: DELETE FROM mtl_serial_numbers_temp WHERE transaction_temp_id = l_temp_txn_id;
1031:
1032: DELETE FROM mtl_serial_numbers_temp WHERE transaction_temp_id = l_temp_txn_id;
1033:
1034: IF (l_log_statement >= l_log_current_level) THEN
1035: FND_LOG.string(l_log_statement, L_DEBUG,
1036: 'Rows deleted from mtl_serial_numbers_temp: ' || SQL%ROWCOUNT);
1037: END IF;
1038:
1039: IF (l_log_statement >= l_log_current_level) THEN
1036: 'Rows deleted from mtl_serial_numbers_temp: ' || SQL%ROWCOUNT);
1037: END IF;
1038:
1039: IF (l_log_statement >= l_log_current_level) THEN
1040: FND_LOG.string(l_log_statement, L_DEBUG,
1041: 'Calling inv_mark_rsv_serial for Serial: ' || p_supply_tbl_type(i).Serial_Number);
1042: END IF;
1043:
1044: SERIAL_CHECK.inv_mark_rsv_serial (from_serial_number => p_supply_tbl_type(i).Serial_Number
1060: --Need to check the meaning of the different values
1061: IF(l_mark_serial_success = 3) THEN
1062:
1063: IF (l_log_statement >= l_log_current_level) THEN
1064: FND_LOG.string(l_log_statement, L_DEBUG,
1065: 'Inserting into mtl_serial_numbers_temp for Serial: '
1066: || p_supply_tbl_type(i).Serial_Number);
1067: END IF;
1068:
1088: , '1' );
1089:
1090: ELSE
1091: IF (l_log_statement >= l_log_current_level) THEN
1092: FND_LOG.string(l_log_statement, L_DEBUG,
1093: 'Marking required serial number failed for Serial: '
1094: || p_supply_tbl_type(i).Serial_Number
1095: || ' with return status: ' || l_mark_serial_success);
1096: END IF;
1121: p_count => x_msg_count,
1122: p_data => x_msg_data);
1123:
1124: IF (l_log_procedure >= l_log_current_level) THEN
1125: FND_LOG.string(l_log_procedure, L_DEBUG || '.End',
1126: 'At the End of the PLSQL procedure CREATE_MOVE_ORDER');
1127: END IF;
1128:
1129: EXCEPTION
1506: RETURN;
1507: END IF;
1508:
1509: --Get Workflow Attributes.
1510: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1511: fnd_log.string
1512: (
1513: fnd_log.level_procedure,
1514: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1507: END IF;
1508:
1509: --Get Workflow Attributes.
1510: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1511: fnd_log.string
1512: (
1513: fnd_log.level_procedure,
1514: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1515: 'Before calling Ahl_Utility_Pvt.Get_Wf_Process_Name'
1509: --Get Workflow Attributes.
1510: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1511: fnd_log.string
1512: (
1513: fnd_log.level_procedure,
1514: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1515: 'Before calling Ahl_Utility_Pvt.Get_Wf_Process_Name'
1516: );
1517: END IF;
1526: x_item_type => l_item_type,
1527: x_msg_count => l_msg_count,
1528: x_msg_data => l_msg_data,
1529: x_return_status => l_return_status);
1530: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1531: fnd_log.string
1532: (
1533: fnd_log.level_procedure,
1534: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1527: x_msg_count => l_msg_count,
1528: x_msg_data => l_msg_data,
1529: x_return_status => l_return_status);
1530: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1531: fnd_log.string
1532: (
1533: fnd_log.level_procedure,
1534: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1535: 'After calling Ahl_Utility_Pvt.Get_Wf_Process_Name, Return Status : '|| l_return_status
1529: x_return_status => l_return_status);
1530: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1531: fnd_log.string
1532: (
1533: fnd_log.level_procedure,
1534: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1535: 'After calling Ahl_Utility_Pvt.Get_Wf_Process_Name, Return Status : '|| l_return_status
1536: );
1537: END IF;
1541: RETURN;
1542: END IF;
1543: -- if the mapping is not active then return normal.
1544: IF (l_active_flag <> 'Y') THEN
1545: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1546: fnd_log.string
1547: (
1548: fnd_log.level_procedure,
1549: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1542: END IF;
1543: -- if the mapping is not active then return normal.
1544: IF (l_active_flag <> 'Y') THEN
1545: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1546: fnd_log.string
1547: (
1548: fnd_log.level_procedure,
1549: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1550: 'Returning to caller as Workflow is not active, Return Status : S'
1544: IF (l_active_flag <> 'Y') THEN
1545: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1546: fnd_log.string
1547: (
1548: fnd_log.level_procedure,
1549: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1550: 'Returning to caller as Workflow is not active, Return Status : S'
1551: );
1552: END IF;
1593:
1594: fnd_message.set_name('AHL','AHL_MM_AOG_FLAG_WF_SUB'); -- sukhwsin-pending to correct message.
1595: fnd_message.set_token('WORKORDER',l_wo_dtl_tab(k).workorder_name);
1596: l_subject := fnd_message.get;
1597: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1598: fnd_log.string
1599: (
1600: fnd_log.level_procedure,
1601: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1594: fnd_message.set_name('AHL','AHL_MM_AOG_FLAG_WF_SUB'); -- sukhwsin-pending to correct message.
1595: fnd_message.set_token('WORKORDER',l_wo_dtl_tab(k).workorder_name);
1596: l_subject := fnd_message.get;
1597: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1598: fnd_log.string
1599: (
1600: fnd_log.level_procedure,
1601: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1602: 'Before calling AHL_WF_NOTIFICATION_PVT.Launch_OA_Notification' ||
1596: l_subject := fnd_message.get;
1597: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1598: fnd_log.string
1599: (
1600: fnd_log.level_procedure,
1601: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1602: 'Before calling AHL_WF_NOTIFICATION_PVT.Launch_OA_Notification' ||
1603: 'l_process_name ' || l_process_name ||
1604: 'l_subject ' || l_subject ||
1618: p_param2_name => 'P_ASM_IDS',
1619: p_param2_value => TO_CHAR(l_wo_dtl_tab(k).asm_ids),
1620: x_item_key => l_item_key,
1621: x_return_status => l_return_status);
1622: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1623: fnd_log.string
1624: (
1625: fnd_log.level_procedure,
1626: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1619: p_param2_value => TO_CHAR(l_wo_dtl_tab(k).asm_ids),
1620: x_item_key => l_item_key,
1621: x_return_status => l_return_status);
1622: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1623: fnd_log.string
1624: (
1625: fnd_log.level_procedure,
1626: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1627: 'After calling AHL_WF_NOTIFICATION_PVT.Launch_OA_Notification, Return Status : '|| l_return_status ||
1621: x_return_status => l_return_status);
1622: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1623: fnd_log.string
1624: (
1625: fnd_log.level_procedure,
1626: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
1627: 'After calling AHL_WF_NOTIFICATION_PVT.Launch_OA_Notification, Return Status : '|| l_return_status ||
1628: 'l_item_key ' || l_item_key
1629: );
1761:
1762: BEGIN
1763:
1764: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1765: FND_LOG.STRING(G_LEVEL_PROCEDURE, L_API_NAME,'Begin API');
1766: END IF;
1767:
1768: SELECT MSI.SEGMENT1,
1769: MSI.INTERNAL_ORDER_ENABLED_FLAG
1775: AND ASM.ORGANIZATION_ID = MSI.ORGANIZATION_ID
1776: AND SCHEDULED_MATERIAL_ID = P_SCH_MTL_ID;
1777:
1778: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
1779: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'L_INVENTORY_ITEM = ' || L_INVENTORY_ITEM || ' has INTERNAL_ORDER_ENABLED_FLAG value - ' || L_ISO_ENABLED_FLAG);
1780: END IF;
1781:
1782: IF('Y' <> L_ISO_ENABLED_FLAG) THEN
1783: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
1780: END IF;
1781:
1782: IF('Y' <> L_ISO_ENABLED_FLAG) THEN
1783: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
1784: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'L_INVENTORY_ITEM = ' || L_INVENTORY_ITEM || ' has INTERNAL_ORDER_ENABLED_FLAG value - N. Process can not continue.');
1785: END IF;
1786:
1787: RAISE INVALID_ITEM_TYPE;
1788: END IF;
1793:
1794: LOOP
1795:
1796: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
1797: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME , '---------------------------------');
1798: END IF;
1799:
1800: J := 1;
1801: L_CURRENT_SUPPLY_ORG := L_SOURCE_ORG_TBL(L_SOURCE_ORG_TBL.first);
1803: L_CURRENT_SUPPLY_LOCATOR := P_SOURCE_LOCATORS_TBL(P_SOURCE_LOCATORS_TBL.FIRST);
1804:
1805:
1806: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
1807: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME ,
1808: ' L_CURRENT_SUPPLY_ORG - ' || L_CURRENT_SUPPLY_ORG ||
1809: ' L_CURRENT_SUPPLY_SUB_INV - ' || L_CURRENT_SUPPLY_SUB_INV ||
1810: ' L_CURRENT_SUPPLY_LOCATOR - ' || L_CURRENT_SUPPLY_LOCATOR ||
1811: ', L_SOURCE_ORG_TBL size - ' || L_SOURCE_ORG_TBL.COUNT);
1816:
1817: BEGIN
1818:
1819: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
1820: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME ,
1821: ' L_SOURCE_ORG_TBL(' || I || ') - ' || L_SOURCE_ORG_TBL(I));
1822: END IF;
1823:
1824: IF(L_CURRENT_SUPPLY_ORG = L_SOURCE_ORG_TBL(I)) THEN
1841:
1842: EXCEPTION
1843: when no_data_found then
1844: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
1845: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME ,
1846: ' Supply Org data missing from table as already removed');
1847: END IF;
1848:
1849: END;
1852:
1853: FOR K IN 1 .. L_FM_SERIAL_NUM_TBL.COUNT
1854: LOOP
1855: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
1856: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME ,
1857: ' L_FM_SERIAL_NUM_TBL(' || K || ') - ' || L_FM_SERIAL_NUM_TBL(K) ||
1858: ' L_TO_SERIAL_NUM_TBL(' || K || ') - ' || L_TO_SERIAL_NUM_TBL(K) ||
1859: ' L_SOURCE_SUB_INV_TBL(' || K || ') - ' || L_SOURCE_SUB_INV_TBL(K) ||
1860: ' L_SOURCE_LOCATORS_TBL(' || K || ') - ' || L_SOURCE_LOCATORS_TBL(K) ||
1903:
1904: EXCEPTION
1905: WHEN INVALID_ITEM_TYPE THEN
1906: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
1907: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME , ' INVALID_ITEM_TYPE EXCEPTION - Invalid Item Type');
1908: END IF;
1909:
1910: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_INVLD_ITEM_FOR_ISO');
1911: fnd_message.set_token('ITEM_NAME',L_INVENTORY_ITEM );
1915: p_encoded => fnd_api.g_false);
1916: X_RETURN_STATUS := G_RET_STS_ERROR;
1917: WHEN OTHERS THEN
1918: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
1919: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME , ' OTHERS exception.');
1920: END IF;
1921:
1922:
1923: END CREATE_INTERNAL_SALES_ORDER;
2041: FND_GLOBAL.APPS_INITIALIZE(1003259/*TSMITH*/, 62211/*AHL_SUPER_USER_CA_US*/, 867/*APPLICATION ID */);
2042: mo_global.init('AHL');
2043:
2044: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2045: Fnd_Log.STRING(G_LEVEL_PROCEDURE, l_api_name,'Begin API');
2046: END IF;
2047:
2048: -- standard check for API compatibility.
2049: IF NOT Fnd_Api.Compatible_API_Call (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
2054: END IF;
2055: x_return_status := G_RET_STS_SUCCESS;
2056:
2057: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2058: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'P_SOURCE_ORG = ' || P_SOURCE_ORG);
2059: END IF;
2060:
2061: -- step 1 - get inv item id
2062: SELECT inventory_item_id, ORGANIZATION_ID, REQUESTED_DATE
2063: INTO l_inventory_item_id, l_demand_org_id, l_need_by_date
2064: FROM AHL_SCHEDULE_MATERIALS
2065: where SCHEDULED_MATERIAL_ID = P_SCH_MTL_ID;
2066: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2067: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_inventory_item_id = ' || l_inventory_item_id);
2068: end if;
2069:
2070:
2071: -- step 2 - Get UOM code for the item
2076: FROM mtl_system_items_b
2077: where INVENTORY_ITEM_ID = L_INVENTORY_ITEM_ID
2078: and rownum = 1;
2079: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2080: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_uom_code = ' || l_uom_code);
2081: end if;
2082:
2083:
2084: -- step 3 - Get Source Operationg Unit
2086: INTO L_SOURCE_OU
2087: FROM ORG_ORGANIZATION_DEFINITIONS
2088: WHERE ORGANIZATION_ID = P_SOURCE_ORG;
2089: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2090: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'L_SOURCE_OU = ' || L_SOURCE_OU);
2091: end if;
2092:
2093:
2094: -- step 4 - Get destination Operationg Unit
2096: INTO L_DESTINATION_OU
2097: FROM ORG_ORGANIZATION_DEFINITIONS
2098: where ORGANIZATION_ID = l_demand_org_id;
2099: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2100: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'L_DESTINATION_OU = ' || L_DESTINATION_OU);
2101: end if;
2102:
2103:
2104: -- step 5 - get the Internal location id.
2102:
2103:
2104: -- step 5 - get the Internal location id.
2105: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2106: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Getting Internal Location Id');
2107: END IF;
2108:
2109: BEGIN
2110: SELECT LOC.LOCATION_ID
2119: AND CUST.CUSTOMER_TYPE = 'I'
2120: AND ROWNUM = 1;
2121:
2122: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2123: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'L_DESTINATION_LOC_ID = ' || L_DESTINATION_LOC_ID);
2124: end if;
2125: EXCEPTION
2126: WHEN NO_DATA_FOUND THEN
2127: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2124: end if;
2125: EXCEPTION
2126: WHEN NO_DATA_FOUND THEN
2127: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2128: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'No Data Found Exception for getting Internal Location Id');
2129: END IF;
2130: --L_DESTINATION_LOC_ID := null;
2131: --IF(L_DESTINATION_LOC_ID IS NULL) THEN
2132: RAISE NO_CUST_ASSOC;
2139: INTO l_person_id
2140: FROM fnd_user
2141: where USER_ID = L_USER_ID;
2142: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2143: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_person_id = ' || l_person_id);
2144: end if;
2145:
2146:
2147: -- step 6 - get the currency code for the destination OU.
2145:
2146:
2147: -- step 6 - get the currency code for the destination OU.
2148: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2149: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Getting currency code');
2150: END IF;
2151: SELECT currency_code
2152: INTO l_currency_code
2153: FROM gl_sets_of_books,
2155: WHERE SET_OF_BOOKS_ID = ORG_INFORMATION1
2156: AND organization_id = L_DESTINATION_OU
2157: and ORG_INFORMATION_CONTEXT = 'Accounting Information';
2158: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2159: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_currency_code = ' || l_currency_code);
2160: end if;
2161:
2162:
2163: -- step 7 - get the material account id.
2161:
2162:
2163: -- step 7 - get the material account id.
2164: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2165: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Getting material account Id');
2166: END IF;
2167: SELECT material_account
2168: INTO l_material_account
2169: FROM mtl_parameters
2168: INTO l_material_account
2169: FROM mtl_parameters
2170: where ORGANIZATION_ID = l_demand_org_id;
2171: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2172: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_material_account = ' || l_material_account);
2173: end if;
2174:
2175: -- Get the shipping network information and internal order enabled flag value
2176: BEGIN
2181: AND TO_ORGANIZATION_ID = L_DEMAND_ORG_ID;
2182: EXCEPTION
2183: WHEN NO_DATA_FOUND THEN
2184: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2185: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,' No Shipping Network found between Demand org: ' || L_DEMAND_ORG_ID || ' and Supply Org: ' || P_SOURCE_ORG);
2186: END IF;
2187: RAISE NO_SHIPPING_NETWORK;
2188: END;
2189:
2187: RAISE NO_SHIPPING_NETWORK;
2188: END;
2189:
2190: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2191: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'L_SHIPPING_NETWORK_CODE = ' || L_SHIPPING_NETWORK_CODE || ', L_ISO_REQ_FLAG = ' || L_ISO_REQ_FLAG);
2192: END IF;
2193:
2194: IF(L_SHIPPING_NETWORK_CODE IS NOT NULL AND L_ISO_REQ_FLAG <> 1) THEN
2195: RAISE NO_ISO_ENABLED_NETWORK;
2206: and ASM.SCHEDULED_MATERIAL_ID = P_SCH_MTL_ID;
2207: EXCEPTION
2208: WHEN NO_DATA_FOUND THEN
2209: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2210: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,' No Sub Inventory and Locator details found for the visit. Set demand sub inv and locator value to null.');
2211: END IF;
2212: L_DEMAND_SUBINV_CODE := NULL;
2213: L_DEMAND_LOCATOR_ID := NULL;
2214: END;
2213: L_DEMAND_LOCATOR_ID := NULL;
2214: END;
2215:
2216: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2217: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'L_DEMAND_SUBINV_CODE = ' || L_DEMAND_SUBINV_CODE);
2218: END IF;
2219:
2220:
2221: -- step 8 - insert the records into requisitions interface.
2219:
2220:
2221: -- step 8 - insert the records into requisitions interface.
2222: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2223: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Before inserting into requisitions interface.');
2224: END IF;
2225:
2226: INSERT
2227: INTO po_requisitions_interface_all
2317: null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
2318: null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
2319: null, null, null, null, null, null, null, null, null ) ;
2320: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2321: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_request_id for requistion import = ' || l_request_id);
2322: end if;
2323: COMMIT;
2324: l_success := fnd_concurrent.wait_for_request ( request_id => l_request_id, interval => 5, phase => x_phase, status => x_status, dev_phase => x_dev_phase, dev_status => x_dev_status, MESSAGE => x_message ) ;
2325:
2324: l_success := fnd_concurrent.wait_for_request ( request_id => l_request_id, interval => 5, phase => x_phase, status => x_status, dev_phase => x_dev_phase, dev_status => x_dev_status, MESSAGE => x_message ) ;
2325:
2326: IF l_success <> TRUE THEN
2327: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2328: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,' Requisition Import failed ' || x_message);
2329: END IF;
2330: X_MSG_DATA := X_MESSAGE;
2331: RAISE CREATE_IR_EXC;
2332: END IF;
2352: update PO_REQUISITION_HEADERS_ALL set INTERFACE_SOURCE_LINE_ID = P_SCH_MTL_ID where REQUISITION_HEADER_ID = X_REQUISITION_HDR_ID;
2353:
2354:
2355: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2356: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'For Internal Requisition, x_requisition_num = ' || X_REQUISITION_NUM ||
2357: ', x_requisition_hdr_id = ' || X_REQUISITION_HDR_ID ||
2358: ', x_requisition_line_id = ' || X_REQUISITION_LINE_ID);
2359: END IF;
2360:
2365: L_SUCCESS := FND_CONCURRENT.WAIT_FOR_REQUEST( REQUEST_ID => L_REQUEST_ID, INTERVAL => 3, PHASE => X_PHASE, STATUS => X_STATUS, DEV_PHASE => X_DEV_PHASE, DEV_STATUS => X_DEV_STATUS, MESSAGE => X_MESSAGE );
2366:
2367: IF l_success <> TRUE THEN
2368: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2369: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,' ISO creation failed ' || x_message);
2370: END IF;
2371: X_MSG_DATA := X_MESSAGE;
2372: RAISE CREATE_ISO_EXC;
2373: end if;
2381: L_SUCCESS := FND_CONCURRENT.WAIT_FOR_REQUEST( REQUEST_ID => L_REQUEST_ID, INTERVAL => 3, PHASE => X_PHASE, STATUS => X_STATUS, DEV_PHASE => X_DEV_PHASE, DEV_STATUS => X_DEV_STATUS, MESSAGE => X_MESSAGE );
2382:
2383: IF l_success <> TRUE THEN
2384: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2385: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,' Order Import failed ' || x_message);
2386: END IF;
2387: X_MSG_DATA := X_MESSAGE;
2388: RAISE CREATE_ISO_EXC;
2389: end if;
2409: RAISE CREATE_ISO_EXC;
2410: END;
2411:
2412: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2413: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'For Internal Order Created, X_INT_ORDER_HDR_ID = ' || X_INT_ORDER_HDR_ID ||
2414: ', X_INT_ORDER_LINE_ID = ' || X_INT_ORDER_LINE_ID ||
2415: ', X_INT_ORDER_NUM = ' || X_INT_ORDER_NUM);
2416: end if;
2417:
2430: X_MSG_DATA => X_MSG_DATA,
2431: x_return_status => X_RETURN_STATUS);
2432:
2433: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2434: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'For IR Reservation Created, X_RESERVATION_ID = ' || X_RESERVATION_ID ||
2435: ', X_RETURN_STATUS = ' || X_RETURN_STATUS ||
2436: ', X_INT_ORDER_NUM for int req = ' || X_INT_ORDER_NUM);
2437: end if;
2438:
2438:
2439: IF X_RETURN_STATUS <> G_RET_STS_SUCCESS
2440: THEN
2441: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
2442: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' create reservation failed as: '|| X_MSG_DATA);
2443: end if;
2444: RAISE CREATE_IR_RSRV_EXC;
2445: end if;
2446:
2503:
2504: IF X_RETURN_STATUS <> G_RET_STS_SUCCESS
2505: THEN
2506: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2507: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, 'Pick Release/Confirm failed as: '|| X_MSG_DATA);
2508: END IF;
2509:
2510: L_ERROR_MESSAGE := X_MSG_DATA;
2511: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2508: END IF;
2509:
2510: L_ERROR_MESSAGE := X_MSG_DATA;
2511: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2512: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' L_ERROR_MESSAGE: '|| L_ERROR_MESSAGE);
2513: END IF;
2514:
2515: L_MM_TXN_INTERFACE_S_CURVAL := AHL_MM_TXN_INTERFACE_S.CURRVAL;
2516: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_PICK_REL_FAIL');
2519: FND_MESSAGE.SET_TOKEN('RSRV_ID',X_RESERVATION_ID );
2520: L_ERROR_MESSAGE := L_ERROR_MESSAGE || ' ' || FND_MESSAGE.GET();
2521:
2522: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2523: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' L_ERROR_MESSAGE: '|| L_ERROR_MESSAGE);
2524: end if;
2525:
2526: --UPDATE AHL_MM_TXN_INTERFACE SET TXN_STATUS = 'E' WHERE TRANSACTION_ID = L_TRANSACTION_ID;
2527: --FORALL X IN P_FM_SERIAL_NUM_TBL.FIRST .. P_FM_SERIAL_NUM_TBL.LAST
2544: EXCEPTION
2545:
2546: WHEN NO_CUST_ASSOC THEN
2547: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2548: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'NO_CUST_ASSOC block, Internal customer not defined or not associated to internal location');
2549: END IF;
2550: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_IR_NO_CUST_ASSOC');
2551: FND_MSG_PUB.ADD;
2552: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2555: X_RETURN_STATUS := G_RET_STS_ERROR;
2556:
2557: WHEN NO_SHIPPING_NETWORK THEN
2558: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2559: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'NO_SHIPPING_NETWORK block, Shipping network not defined for the org.');
2560: END IF;
2561: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_NO_SHIPPING_NETWORK');
2562: FND_MSG_PUB.ADD;
2563: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2566: X_RETURN_STATUS := G_RET_STS_ERROR;
2567:
2568: WHEN NO_ISO_ENABLED_NETWORK THEN
2569: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2570: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'NO_ISO_ENABLED_NETWORK block, Internal Order Enabled flag not selected');
2571: END IF;
2572: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_NO_ISO_ENABLED_NETWORK');
2573: FND_MSG_PUB.ADD;
2574: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2577: X_RETURN_STATUS := G_RET_STS_ERROR;
2578:
2579: WHEN CREATE_IR_EXC THEN
2580: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2581: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'CREATE_IR_EXC block, add error message AHL_MM_IR_CREATION_ERR');
2582: END IF;
2583: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_IR_CREATION_ERR');
2584: FND_MSG_PUB.ADD;
2585: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2589: --rollback to RELEASE_SALESORDER;
2590:
2591: WHEN CREATE_ISO_EXC THEN
2592: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2593: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'CREATE_ISO_EXC block, add error message AHL_MM_ISO_CREATION_ERR');
2594: END IF;
2595: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_ISO_CREATION_ERR');
2596: FND_MSG_PUB.ADD;
2597: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2601: --rollback to RELEASE_SALESORDER;
2602:
2603: when CREATE_IR_RSRV_EXC then
2604: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2605: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'CREATE_IR_RSRV_EXC, For IR Reservation Created, X_REQUISITION_NUM = ' || X_REQUISITION_NUM
2606: || ', X_INT_ORDER_NUM for int req = ' || X_INT_ORDER_NUM || ', X_RESERVATION_ID for int req = ' || X_RESERVATION_ID);
2607: END IF;
2608: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_IR_RSRV_ERR');
2609: FND_MESSAGE.SET_TOKEN('REQ_NUM',X_REQUISITION_NUM );
2616: --rollback to CREATE_IR_RESERVATION;
2617:
2618: WHEN PICK_REL_EXC THEN
2619: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2620: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'PICK_REL_EXC, For IR Reservation Created, X_REQUISITION_NUM = ' || X_REQUISITION_NUM
2621: || ', X_INT_ORDER_NUM for int req = ' || X_INT_ORDER_NUM || ', X_RESERVATION_ID for int req = ' || X_RESERVATION_ID);
2622: END IF;
2623: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_PICK_REL_FAIL');
2624: FND_MESSAGE.SET_TOKEN('REQ_NUM',X_REQUISITION_NUM );
2717:
2718: IF X_RETURN_STATUS <> G_RET_STS_SUCCESS
2719: THEN
2720: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2721: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'create reservation failed as: '|| X_MSG_DATA);
2722: END IF;
2723: RAISE fnd_api.g_exc_error;
2724: end if;
2725:
2819: J := 1;
2820: L_CURRENT_SUPPLY_SUB_INV := L_SOURCE_SUB_INV_TBL(L_SOURCE_SUB_INV_TBL.FIRST);
2821: L_CURRENT_SUPPLY_LOCATOR := L_SOURCE_LOCATOR_TBL(L_SOURCE_LOCATOR_TBL.FIRST);
2822: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2823: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME ,
2824: ' L_CURRENT_SUPPLY_SUB_INV - ' || L_CURRENT_SUPPLY_SUB_INV ||
2825: ', L_CURRENT_SUPPLY_LOCATOR - ' || L_CURRENT_SUPPLY_LOCATOR ||
2826: ', L_SOURCE_SUB_INV_TBL size - ' || L_SOURCE_SUB_INV_TBL.COUNT);
2827: END IF;
2830: LOOP
2831: BEGIN
2832:
2833: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2834: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME , '------------------------------------------');
2835: END IF;
2836:
2837: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2838: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME ,
2834: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME , '------------------------------------------');
2835: END IF;
2836:
2837: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2838: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME ,
2839: ' CURRENT_SUPPLY_SUB_INV - ' || L_SOURCE_SUB_INV_TBL(I) ||
2840: ' CURRENT_SUPPLY_LOCATOR - ' || L_SOURCE_LOCATOR_TBL(I));
2841: END IF;
2842:
2861:
2862: EXCEPTION
2863: when no_data_found then
2864: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2865: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME ,
2866: ' Supply Sub Inv data missing from table as already removed');
2867: END IF;
2868:
2869: END; -- End to begin
2872:
2873: FOR K IN 1 .. L_FM_SERIAL_NUM_TBL.COUNT
2874: LOOP
2875: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2876: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME ,
2877: ' L_FM_SERIAL_NUM_TBL(' || K || ') - ' || L_FM_SERIAL_NUM_TBL(K) ||
2878: ' L_TO_SERIAL_NUM_TBL(' || K || ') - ' || L_TO_SERIAL_NUM_TBL(K) ||
2879: ' L_QUANTITY_TBL(' || K || ') - ' || L_QUANTITY_TBL(K));
2880: END IF;
2906:
2907: /*IF X_RETURN_STATUS <> G_RET_STS_SUCCESS
2908: THEN
2909: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
2910: FND_LOG.STRING( l_log_statement, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, 'Pick Release/Confirmation failed as: '|| L_MSG_DATA);
2911: end if;
2912: RAISE PICK_REL_EXC;
2913: end if;*/
2914:
3039: where transaction_temp_id = p_mmtt_temp_id;
3040:
3041: BEGIN
3042: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
3043: fnd_log.string(G_LEVEL_PROCEDURE, l_api_name,'Begin API');
3044: END IF;
3045:
3046: -- standard check for API compatibility.
3047: IF NOT Fnd_Api.Compatible_API_Call
3069: FND_PROFILE.GET('AHL_DEF_PICK_RELEASE_RULE', L_PICKING_RULE_ID);
3070: END IF;
3071:
3072: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3073: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'L_PICKING_RULE_ID = ' || L_PICKING_RULE_ID);
3074: END IF;
3075:
3076: IF L_PICKING_RULE_ID IS NULL OR L_PICKING_RULE_ID = 0 THEN
3077: RAISE INVALID_PICK_RULE;
3087: and released_status not in ('S', 'Y', 'C');
3088: EXCEPTION
3089: WHEN NO_DATA_FOUND THEN
3090: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3091: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,' No delivery details found');
3092: end if;
3093: RAISE INVALID_DELIVERY_DETAIL;
3094: END;
3095:
3096: /*-- requested quantity is the quantity available for transaction.*/
3097:
3098: IF NOT (l_delivery_status = 'S' OR l_delivery_status = 'Y') THEN
3099: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3100: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' L_DELIVERY_STATUS is = ' || L_DELIVERY_STATUS||
3101: ', l_delivery_detail_id = ' || L_DELIVERY_DETAIL_ID);
3102: end if;
3103: -- fetch the customer details.
3104: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3101: ', l_delivery_detail_id = ' || L_DELIVERY_DETAIL_ID);
3102: end if;
3103: -- fetch the customer details.
3104: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3105: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Fetching customer, order type and source document');
3106: END IF;
3107: SELECT sold_to_org_id,
3108: order_type_id,
3109: source_document_type_id
3115:
3116: -- get the defaults from picking rule.
3117: -- Auto pick confirm flag will be always set to N in this API
3118: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3119: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Fetching picking defaults');
3120: END IF;
3121:
3122: SELECT NVL(document_set_id, l_document_set_id),
3123: 'I',
3186: from WSH_PICKING_RULES
3187: WHERE PICKING_RULE_ID = l_picking_rule_id; -- value 628
3188:
3189: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3190: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.backorders_only_flag ' || l_batch_rec.backorders_only_flag);
3191: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.existing_rsvs_only_flag ' || l_batch_rec.existing_rsvs_only_flag);
3192: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.customer_id ' || l_batch_rec.customer_id);
3193: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.order_header_id ' || l_batch_rec.order_header_id);
3194: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.from_scheduled_ship_date ' || l_batch_rec.from_scheduled_ship_date);
3187: WHERE PICKING_RULE_ID = l_picking_rule_id; -- value 628
3188:
3189: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3190: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.backorders_only_flag ' || l_batch_rec.backorders_only_flag);
3191: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.existing_rsvs_only_flag ' || l_batch_rec.existing_rsvs_only_flag);
3192: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.customer_id ' || l_batch_rec.customer_id);
3193: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.order_header_id ' || l_batch_rec.order_header_id);
3194: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.from_scheduled_ship_date ' || l_batch_rec.from_scheduled_ship_date);
3195: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.organization_id ' || l_batch_rec.organization_id);
3188:
3189: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3190: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.backorders_only_flag ' || l_batch_rec.backorders_only_flag);
3191: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.existing_rsvs_only_flag ' || l_batch_rec.existing_rsvs_only_flag);
3192: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.customer_id ' || l_batch_rec.customer_id);
3193: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.order_header_id ' || l_batch_rec.order_header_id);
3194: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.from_scheduled_ship_date ' || l_batch_rec.from_scheduled_ship_date);
3195: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.organization_id ' || l_batch_rec.organization_id);
3196: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.include_planned_lines ' || l_batch_rec.include_planned_lines);
3189: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3190: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.backorders_only_flag ' || l_batch_rec.backorders_only_flag);
3191: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.existing_rsvs_only_flag ' || l_batch_rec.existing_rsvs_only_flag);
3192: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.customer_id ' || l_batch_rec.customer_id);
3193: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.order_header_id ' || l_batch_rec.order_header_id);
3194: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.from_scheduled_ship_date ' || l_batch_rec.from_scheduled_ship_date);
3195: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.organization_id ' || l_batch_rec.organization_id);
3196: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.include_planned_lines ' || l_batch_rec.include_planned_lines);
3197: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autocreate_delivery_flag ' || l_batch_rec.autocreate_delivery_flag);
3190: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.backorders_only_flag ' || l_batch_rec.backorders_only_flag);
3191: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.existing_rsvs_only_flag ' || l_batch_rec.existing_rsvs_only_flag);
3192: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.customer_id ' || l_batch_rec.customer_id);
3193: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.order_header_id ' || l_batch_rec.order_header_id);
3194: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.from_scheduled_ship_date ' || l_batch_rec.from_scheduled_ship_date);
3195: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.organization_id ' || l_batch_rec.organization_id);
3196: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.include_planned_lines ' || l_batch_rec.include_planned_lines);
3197: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autocreate_delivery_flag ' || l_batch_rec.autocreate_delivery_flag);
3198: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autodetail_pr_flag ' || l_batch_rec.autodetail_pr_flag);
3191: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.existing_rsvs_only_flag ' || l_batch_rec.existing_rsvs_only_flag);
3192: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.customer_id ' || l_batch_rec.customer_id);
3193: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.order_header_id ' || l_batch_rec.order_header_id);
3194: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.from_scheduled_ship_date ' || l_batch_rec.from_scheduled_ship_date);
3195: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.organization_id ' || l_batch_rec.organization_id);
3196: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.include_planned_lines ' || l_batch_rec.include_planned_lines);
3197: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autocreate_delivery_flag ' || l_batch_rec.autocreate_delivery_flag);
3198: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autodetail_pr_flag ' || l_batch_rec.autodetail_pr_flag);
3199: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.allocation_method ' || l_batch_rec.allocation_method);
3192: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.customer_id ' || l_batch_rec.customer_id);
3193: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.order_header_id ' || l_batch_rec.order_header_id);
3194: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.from_scheduled_ship_date ' || l_batch_rec.from_scheduled_ship_date);
3195: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.organization_id ' || l_batch_rec.organization_id);
3196: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.include_planned_lines ' || l_batch_rec.include_planned_lines);
3197: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autocreate_delivery_flag ' || l_batch_rec.autocreate_delivery_flag);
3198: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autodetail_pr_flag ' || l_batch_rec.autodetail_pr_flag);
3199: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.allocation_method ' || l_batch_rec.allocation_method);
3200: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || l_batch_rec.pick_from_locator_id);
3193: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.order_header_id ' || l_batch_rec.order_header_id);
3194: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.from_scheduled_ship_date ' || l_batch_rec.from_scheduled_ship_date);
3195: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.organization_id ' || l_batch_rec.organization_id);
3196: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.include_planned_lines ' || l_batch_rec.include_planned_lines);
3197: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autocreate_delivery_flag ' || l_batch_rec.autocreate_delivery_flag);
3198: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autodetail_pr_flag ' || l_batch_rec.autodetail_pr_flag);
3199: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.allocation_method ' || l_batch_rec.allocation_method);
3200: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || l_batch_rec.pick_from_locator_id);
3201: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.auto_pick_confirm_flag ' || l_batch_rec.auto_pick_confirm_flag);
3194: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.from_scheduled_ship_date ' || l_batch_rec.from_scheduled_ship_date);
3195: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.organization_id ' || l_batch_rec.organization_id);
3196: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.include_planned_lines ' || l_batch_rec.include_planned_lines);
3197: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autocreate_delivery_flag ' || l_batch_rec.autocreate_delivery_flag);
3198: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autodetail_pr_flag ' || l_batch_rec.autodetail_pr_flag);
3199: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.allocation_method ' || l_batch_rec.allocation_method);
3200: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || l_batch_rec.pick_from_locator_id);
3201: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.auto_pick_confirm_flag ' || l_batch_rec.auto_pick_confirm_flag);
3202: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autopack_flag ' || l_batch_rec.autopack_flag );
3195: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.organization_id ' || l_batch_rec.organization_id);
3196: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.include_planned_lines ' || l_batch_rec.include_planned_lines);
3197: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autocreate_delivery_flag ' || l_batch_rec.autocreate_delivery_flag);
3198: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autodetail_pr_flag ' || l_batch_rec.autodetail_pr_flag);
3199: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.allocation_method ' || l_batch_rec.allocation_method);
3200: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || l_batch_rec.pick_from_locator_id);
3201: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.auto_pick_confirm_flag ' || l_batch_rec.auto_pick_confirm_flag);
3202: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autopack_flag ' || l_batch_rec.autopack_flag );
3203: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || L_BATCH_REC.PICK_FROM_LOCATOR_ID);
3196: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.include_planned_lines ' || l_batch_rec.include_planned_lines);
3197: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autocreate_delivery_flag ' || l_batch_rec.autocreate_delivery_flag);
3198: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autodetail_pr_flag ' || l_batch_rec.autodetail_pr_flag);
3199: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.allocation_method ' || l_batch_rec.allocation_method);
3200: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || l_batch_rec.pick_from_locator_id);
3201: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.auto_pick_confirm_flag ' || l_batch_rec.auto_pick_confirm_flag);
3202: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autopack_flag ' || l_batch_rec.autopack_flag );
3203: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || L_BATCH_REC.PICK_FROM_LOCATOR_ID);
3204: END IF;
3197: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autocreate_delivery_flag ' || l_batch_rec.autocreate_delivery_flag);
3198: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autodetail_pr_flag ' || l_batch_rec.autodetail_pr_flag);
3199: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.allocation_method ' || l_batch_rec.allocation_method);
3200: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || l_batch_rec.pick_from_locator_id);
3201: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.auto_pick_confirm_flag ' || l_batch_rec.auto_pick_confirm_flag);
3202: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autopack_flag ' || l_batch_rec.autopack_flag );
3203: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || L_BATCH_REC.PICK_FROM_LOCATOR_ID);
3204: END IF;
3205: -- create the release batch.
3198: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autodetail_pr_flag ' || l_batch_rec.autodetail_pr_flag);
3199: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.allocation_method ' || l_batch_rec.allocation_method);
3200: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || l_batch_rec.pick_from_locator_id);
3201: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.auto_pick_confirm_flag ' || l_batch_rec.auto_pick_confirm_flag);
3202: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autopack_flag ' || l_batch_rec.autopack_flag );
3203: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || L_BATCH_REC.PICK_FROM_LOCATOR_ID);
3204: END IF;
3205: -- create the release batch.
3206: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3199: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.allocation_method ' || l_batch_rec.allocation_method);
3200: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || l_batch_rec.pick_from_locator_id);
3201: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.auto_pick_confirm_flag ' || l_batch_rec.auto_pick_confirm_flag);
3202: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.autopack_flag ' || l_batch_rec.autopack_flag );
3203: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || L_BATCH_REC.PICK_FROM_LOCATOR_ID);
3204: END IF;
3205: -- create the release batch.
3206: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3207: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Creating the release batch');
3203: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'l_batch_rec.pick_from_locator_id ' || L_BATCH_REC.PICK_FROM_LOCATOR_ID);
3204: END IF;
3205: -- create the release batch.
3206: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3207: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Creating the release batch');
3208: END IF;
3209:
3210: WSH_PICKING_BATCHES_GRP.Create_Batch(
3211: p_api_version_number => 1.0,
3221: x_batch_id => x_batch_id
3222: );
3223:
3224: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3225: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' create batch x_return_status = ' || X_RETURN_STATUS ||
3226: ', create batch x_batch_id = ' || X_BATCH_ID);
3227: end if;
3228:
3229: IF NOT x_return_status = G_RET_STS_SUCCESS THEN
3227: end if;
3228:
3229: IF NOT x_return_status = G_RET_STS_SUCCESS THEN
3230: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
3231: fnd_log.string(G_LEVEL_PROCEDURE, l_api_name,'WSH_PICKING_BATCHES_GRP.Create_Batch has failed:'||x_msg_data);
3232: END IF;
3233: RAISE CREATE_BATCH;
3234: END IF;
3235:
3233: RAISE CREATE_BATCH;
3234: END IF;
3235:
3236: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3237: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Created the release batch, calling release batch now');
3238: END IF;
3239:
3240: wsh_picking_batches_pub.Release_Batch(
3241: p_api_version => 1.0,
3252: );
3253:
3254:
3255: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3256: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'after wsh_picking_batches_pub.Release_Batch, return status:'||x_return_status);
3257: end if;
3258:
3259: IF x_return_status = wsh_util_core.g_ret_sts_warning THEN
3260: -- treat warning as success.
3262: END IF;
3263:
3264: IF x_return_status = G_RET_STS_ERROR THEN
3265: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3266: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Release batch is failed!');
3267: END IF;
3268: RAISE RELEASE_BATCH;
3269: END IF;
3270: END IF;
3273: -- allocation.
3274: IF NOT l_delivery_status = 'Y' THEN
3275: BEGIN
3276: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3277: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Getting allocations if any for order header id:'||p_order_header_id || ' and delivery_detail_id:' || l_delivery_detail_id);
3278: END IF;
3279:
3280: SELECT mtrh.header_id,mmtt.transaction_temp_id,
3281: mtrl.line_id,wdd.delivery_detail_id
3291: AND MTRL.LINE_ID = MMTT.MOVE_ORDER_LINE_ID(+)
3292: AND wdd.delivery_detail_id = l_delivery_detail_id;
3293:
3294: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3295: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,' Fetched move order header id '||L_MOVE_ORDER_HDR_ID ||
3296: ' Fetched l_mmtt_temp_id '||l_mmtt_temp_id);
3297: end if;
3298:
3299: EXCEPTION
3298:
3299: EXCEPTION
3300: WHEN no_data_found THEN
3301: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3302: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, 'No allocations were found!');
3303: END IF;
3304: l_move_order_hdr_id := 0;
3305: END;
3306:
3312: FROM wsh_delivery_details wdd
3313: WHERE wdd.source_header_id = p_order_header_id;
3314:
3315: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3316: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Creating the allocations for move line id'||l_move_order_line_id);
3317: END IF;
3318:
3319: IF not NVL(l_move_order_line_id,0) = 0 THEN
3320: inv_replenish_detail_pub.line_details_pub
3339: p_auto_pick_confirm => FALSE,
3340: p_commit => FALSE
3341: );
3342: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3343: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Returned from inv_replenish_detail_pub.line_details. Return status'||x_return_status);
3344: END IF;
3345:
3346: IF x_return_status <> G_RET_STS_SUCCESS THEN
3347: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3344: END IF;
3345:
3346: IF x_return_status <> G_RET_STS_SUCCESS THEN
3347: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3348: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Error while trying to create allocations..'||x_msg_data);
3349: END IF;
3350: RAISE ALLOCATION_ERROR;
3351: END IF;
3352:
3350: RAISE ALLOCATION_ERROR;
3351: END IF;
3352:
3353: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3354: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Successfully created the allocation: Transaction temp id is='||l_mmtt_temp_id);
3355: END IF;
3356: END IF;
3357: END IF;
3358:
3358:
3359: IF l_mmtt_temp_id <> 0 THEN
3360:
3361: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3362: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Checking if the item is reservable..');
3363: fnd_log.string(L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'mover order line id:'||l_move_order_line_id);
3364: END IF;
3365:
3366: SELECT msi.reservable_type,msi.inventory_item_id,
3359: IF l_mmtt_temp_id <> 0 THEN
3360:
3361: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3362: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Checking if the item is reservable..');
3363: fnd_log.string(L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'mover order line id:'||l_move_order_line_id);
3364: END IF;
3365:
3366: SELECT msi.reservable_type,msi.inventory_item_id,
3367: msi.organization_id
3373: AND mtrl.organization_id = msi.organization_id
3374: AND mtrl.line_id = l_move_order_line_id;
3375:
3376: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3377: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Reservation flag:'||l_reservable_flag);
3378: END IF;
3379:
3380: IF l_reservable_flag = 1 THEN
3381: BEGIN
3390: END;
3391: END IF;
3392:
3393: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3394: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Existing reservation id: '||l_reservation_id);
3395: END IF;
3396:
3397:
3398: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3395: END IF;
3396:
3397:
3398: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3399: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' defualt allocation details');
3400: end if;
3401:
3402: for serial_number_rec in get_init_serial_alloc(l_mmtt_temp_id)
3403: LOOP
3401:
3402: for serial_number_rec in get_init_serial_alloc(l_mmtt_temp_id)
3403: LOOP
3404: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3405: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' ll_fm_serial_number: ' || serial_number_rec.fm_serial_number);
3406: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' ll_to_serial_number : ' || SERIAL_NUMBER_REC.TO_SERIAL_NUMBER);
3407: end if;
3408: end loop;
3409:
3402: for serial_number_rec in get_init_serial_alloc(l_mmtt_temp_id)
3403: LOOP
3404: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3405: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' ll_fm_serial_number: ' || serial_number_rec.fm_serial_number);
3406: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' ll_to_serial_number : ' || SERIAL_NUMBER_REC.TO_SERIAL_NUMBER);
3407: end if;
3408: end loop;
3409:
3410:
3414: FROM mtl_serial_numbers_temp
3415: WHERE transaction_temp_id = l_mmtt_temp_id;
3416:
3417: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3418: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' after allocation details');
3419: end if;
3420:
3421: FOR k IN 1 ..l_fm_serial_number.COUNT
3422: LOOP
3420:
3421: FOR k IN 1 ..l_fm_serial_number.COUNT
3422: LOOP
3423: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3424: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' l_fm_serial_number = ' || L_FM_SERIAL_NUMBER(K)||
3425: ', l_to_serial_number = ' || L_TO_SERIAL_NUMBER(K));
3426: end if;
3427: end loop;
3428:
3427: end loop;
3428:
3429: -- unmark the serials.
3430: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3431: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Unmarking the existing range');
3432: END IF;
3433:
3434: FOR i in 1 ..l_fm_serial_number.COUNT
3435: LOOP
3447: END LOOP;
3448:
3449: -- delete all the existing serials from MSNT.
3450: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3451: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Deleting from MSNT for transaction_temp_id='||l_mmtt_temp_id);
3452: END IF;
3453:
3454: DELETE FROM mtl_serial_numbers_temp WHERE transaction_temp_id = l_mmtt_temp_id;
3455:
3454: DELETE FROM mtl_serial_numbers_temp WHERE transaction_temp_id = l_mmtt_temp_id;
3455:
3456: -- mark the newly passed serials. And insert them into MSNT.
3457: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3458: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Marking the newly entered serial ranges');
3459: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Range count:'||p_fm_serial_num_tbl.COUNT);
3460:
3461: END IF;
3462:
3455:
3456: -- mark the newly passed serials. And insert them into MSNT.
3457: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3458: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Marking the newly entered serial ranges');
3459: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Range count:'||p_fm_serial_num_tbl.COUNT);
3460:
3461: END IF;
3462:
3463: l_picking_qty := 0;
3477: success => x_success
3478: );
3479:
3480: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3481: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Inserting into msnt='||l_mmtt_temp_id);
3482: END IF;
3483:
3484: INSERT INTO mtl_serial_numbers_temp
3485: (transaction_temp_id,
3512: -- if we are doing partial picking, then need to update the existing allocation details.
3513: -- the serial number allocation would have been already addressed above.
3514:
3515: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3516: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' allocation details after unmarking and marking');
3517: end if;
3518:
3519: for serial_number_rec in get_init_serial_alloc(l_mmtt_temp_id)
3520: LOOP
3518:
3519: for serial_number_rec in get_init_serial_alloc(l_mmtt_temp_id)
3520: LOOP
3521: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3522: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' ll_fm_serial_number: ' || SERIAL_NUMBER_REC.FM_SERIAL_NUMBER ||
3523: ', ll_to_serial_number : ' || SERIAL_NUMBER_REC.TO_SERIAL_NUMBER);
3524: end if;
3525: end loop;
3526:
3526:
3527: IF l_picking_qty < l_requested_qty
3528: then
3529: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3530: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' l_picking_qty is less than l_requested_qty');
3531: end if;
3532:
3533: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
3534: fnd_log.string(G_LEVEL_PROCEDURE, l_api_name,'Updating the existing allocations for temp id ='||l_mmtt_temp_id);
3530: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME, ' l_picking_qty is less than l_requested_qty');
3531: end if;
3532:
3533: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
3534: fnd_log.string(G_LEVEL_PROCEDURE, l_api_name,'Updating the existing allocations for temp id ='||l_mmtt_temp_id);
3535: END IF;
3536: inv_missing_qty_actions_engine.update_allocation_qty
3537: (x_return_status => x_return_status,
3538: x_msg_data => x_msg_data,
3543: );
3544: IF x_return_status <> G_RET_STS_SUCCESS
3545: THEN
3546: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
3547: fnd_log.string(G_LEVEL_PROCEDURE, l_api_name,'Error in inv_missing_qty_actions_engine.update_allocation_qty. Msg is '||x_msg_data);
3548: END IF;
3549: RAISE ALLOCATION_ERROR;
3550: END IF;
3551:
3551:
3552: -- we need to process the allocations such that a back order gets created for
3553: -- the remaining quantity.
3554: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
3555: fnd_log.string(G_LEVEL_PROCEDURE, l_api_name,'Calling process action to back order remaining quantity ');
3556: END IF;
3557: inv_missing_qty_actions_engine.process_action
3558: (x_return_status => x_return_status,
3559: x_msg_data => x_msg_data,
3565: );
3566: IF x_return_status <> G_RET_STS_SUCCESS
3567: THEN
3568: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
3569: fnd_log.string(G_LEVEL_PROCEDURE, l_api_name,'Error in process_action API. Msg = '||x_msg_data);
3570: END IF;
3571: RAISE ALLOCATION_ERROR;
3572: END IF;
3573: END IF;
3578: -- INV team says that the API queries all the remaining details itself.
3579: l_trolin_tbl(1).line_id := l_move_order_line_id;
3580: -- call the pick confirm api
3581: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3582: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm');
3583: END IF;
3584:
3585: INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm
3586: (p_api_version_number => 1.0,
3597: x_trolin_tbl => l_trolin_tbl
3598: );
3599: IF x_return_status <> G_RET_STS_SUCCESS THEN
3600: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3601: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'ERROR in INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm');
3602: END IF;
3603: RAISE RELEASE_BATCH;
3604: END IF;
3605: END IF;
3607: -- Check if the delivery is successfully pick confirmed.
3608: IF l_move_order_line_id IS NOT NULL
3609: THEN
3610: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3611: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'Checking if the delivery is successfully pick released. DDID='||l_delivery_detail_id);
3612: END IF;
3613: SELECT released_status,delivery_detail_id,picked_quantity
3614: INTO l_delivery_status,l_delivery_detail_id_new,l_picked_qty
3615: FROM wsh_delivery_details
3621: END IF;
3622: END IF;
3623:
3624: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3625: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,' p_commit value - ' || p_commit);
3626: END IF;
3627:
3628: IF fnd_api.to_boolean(p_commit)
3629: THEN
3632:
3633: EXCEPTION
3634: WHEN INVALID_PICK_RULE THEN
3635: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3636: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'In INVALID_PICK_RULE exception');
3637: end if;
3638: FND_MESSAGE.SET_NAME('AHL', 'AHL_PICKING_RULE_ID_INV');
3639: fnd_message.set_token('PICKING_RULE_ID',l_picking_rule_id );
3640: FND_MSG_PUB.ADD;
3644: X_RETURN_STATUS := G_RET_STS_ERROR;
3645: rollback to RELEASE_SALESORDER;
3646: WHEN INVALID_DELIVERY_DETAIL THEN
3647: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3648: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'In INVALID_DELIVERY_DETAIL exception');
3649: end if;
3650: FND_MESSAGE.SET_NAME('AHL', 'AHL_INVLD_DELIVERY_DETAILS');
3651: FND_MSG_PUB.ADD;
3652: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3655: X_RETURN_STATUS := G_RET_STS_ERROR;
3656: ROLLBACK TO RELEASE_SALESORDER;
3657: WHEN CREATE_BATCH THEN
3658: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3659: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'In create_batch exception');
3660: end if;
3661: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_PICK_REL_CRT_BTCH_FAIL');
3662: FND_MSG_PUB.ADD;
3663: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3666: x_return_status := G_RET_STS_ERROR;
3667: ROLLBACK TO RELEASE_SALESORDER;
3668: WHEN RELEASE_BATCH THEN
3669: if (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) then
3670: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'In release_batch exception');
3671: end if;
3672: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_PICK_REL_REL_BTCH_FAIL');
3673: fnd_msg_pub.add;
3674: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3677: x_return_status := G_RET_STS_ERROR;
3678: ROLLBACK TO RELEASE_SALESORDER;
3679: WHEN ALLOCATION_ERROR THEN
3680: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3681: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'In allocation_error exception');
3682: END IF;
3683: FND_MESSAGE.SET_NAME('AHL', 'AHL_MM_PICK_REL_ALOC_FAIL');
3684: fnd_msg_pub.add;
3685: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3688: x_return_status := G_RET_STS_ERROR;
3689: ROLLBACK TO RELEASE_SALESORDER;
3690: WHEN NO_DATA_FOUND THEN
3691: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3692: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'In NO_DATA_FOUND exception');
3693: END IF;
3694: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3695: P_DATA => X_MSG_DATA,
3696: p_encoded => fnd_api.g_false);
3697: X_RETURN_STATUS := G_RET_STS_ERROR;
3698: ROLLBACK TO RELEASE_SALESORDER;
3699: WHEN OTHERS THEN
3700: IF (L_LOG_STATEMENT >= L_LOG_CURRENT_LEVEL) THEN
3701: FND_LOG.STRING( L_LOG_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'|| L_API_NAME,'In OTHERS exception');
3702: END IF;
3703: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3704: P_DATA => X_MSG_DATA,
3705: p_encoded => fnd_api.g_false);
3759: L_DEBUG CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3760: BEGIN
3761: SAVEPOINT MM_AOG_UPD_NOTIFICATION_SP;
3762: IF (L_LOG_PROCEDURE >= L_LOG_CURRENT_LEVEL) THEN
3763: FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG || '.begin', 'At the start of the PLSQL procedure AOG_UPD_NOTIFICATION');
3764: END IF;
3765:
3766: -- Initialize API return status to success
3767: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3771: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3772: END IF;
3773:
3774: IF (L_LOG_PROCEDURE >= L_LOG_CURRENT_LEVEL) THEN
3775: FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG, 'ASM ID Count : ' || TO_CHAR(P_SCHEDULE_MATERIAL_ID_TBL.COUNT) );
3776: END IF;
3777:
3778: FOR I IN 1 .. P_SCHEDULE_MATERIAL_ID_TBL.COUNT
3779: LOOP
3786:
3787: L_SQL_STRING := L_SQL_STRING || ')';
3788:
3789: IF (L_LOG_PROCEDURE >= L_LOG_CURRENT_LEVEL) THEN
3790: FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG || '.begin', 'Query Formed ' || L_SQL_STRING);
3791: END IF;
3792:
3793: AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
3794: (
3891: L_DEBUG CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
3892: BEGIN
3893: SAVEPOINT MM_UPDATE_AOG_SP;
3894: IF (L_LOG_PROCEDURE >= L_LOG_CURRENT_LEVEL) THEN
3895: FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG || '.begin', 'At the start of the PLSQL procedure UPDATE_AOG');
3896: END IF;
3897:
3898: -- Initialize API return status to success
3899: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
3910: IF(P_AOG_TBL(I).AOG_FLAG <> CURRENT_AOG.AOG_FLAG OR CURRENT_AOG.AOG_FLAG IS NULL )
3911: THEN
3912: IF(P_AOG_TBL(I).AOG_FLAG = 'Y' OR P_AOG_TBL(I).AOG_FLAG = 'y') THEN
3913: IF (L_LOG_PROCEDURE >= L_LOG_CURRENT_LEVEL) THEN
3914: FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG, 'Invoking Update Notification for ' || P_AOG_TBL(I).SCHEDULE_MATERIAL_ID);
3915: END IF;
3916: P_SCHEDULE_MATERIAL_ID_TBL(P_COUNT) := P_AOG_TBL(I).SCHEDULE_MATERIAL_ID;
3917: P_COUNT := P_COUNT + 1;
3918: END IF;
4113:
4114: BEGIN
4115: G_DEBUG_LINE_NUM := 10;
4116: IF (l_log_statement >= l_log_current_level) THEN
4117: FND_LOG.STRING(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Entered Perform_Inter_Org_Transfer Procedure');
4118: IF (p_x_mtltfr_tbl IS NOT NULL) THEN
4119: FND_LOG.STRING(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Input record count ='||p_x_mtltfr_tbl.COUNT);
4120: END IF;
4121: END IF;
4115: G_DEBUG_LINE_NUM := 10;
4116: IF (l_log_statement >= l_log_current_level) THEN
4117: FND_LOG.STRING(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Entered Perform_Inter_Org_Transfer Procedure');
4118: IF (p_x_mtltfr_tbl IS NOT NULL) THEN
4119: FND_LOG.STRING(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Input record count ='||p_x_mtltfr_tbl.COUNT);
4120: END IF;
4121: END IF;
4122:
4123: -- Initialize return status to success
4152: G_DEBUG_LINE_NUM := 20;
4153: -- Call API to process transfer
4154: BEGIN
4155: IF (l_log_statement >= l_log_current_level) THEN
4156: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Calling API Perform_Inter_Org_Transfer_Inv');
4157: END IF;
4158: Perform_Inter_Org_Transfer_Inv (p_api_version => p_api_version,
4159: p_init_msg_list => p_init_msg_list,
4160: p_commit => l_commit,
4165: x_msg_count => x_msg_count,
4166: x_msg_data => x_msg_data,
4167: p_x_mtltfr_tbl => p_x_mtltfr_tbl );
4168: IF (l_log_statement >= l_log_current_level) THEN
4169: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Back from API Perform_Inter_Org_Transfer_Inv with'||
4170: ' retrun status '||x_return_status);
4171: END IF;
4172: EXCEPTION
4173: WHEN OTHERS THEN
4171: END IF;
4172: EXCEPTION
4173: WHEN OTHERS THEN
4174: IF (l_log_exception >= l_log_current_level) THEN
4175: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':API Perform_Inter_Org_Transfer_Inv returned exception');
4176: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM -'|| SQLERRM);
4177: END IF;
4178: -- Raise Exception
4179: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4172: EXCEPTION
4173: WHEN OTHERS THEN
4174: IF (l_log_exception >= l_log_current_level) THEN
4175: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':API Perform_Inter_Org_Transfer_Inv returned exception');
4176: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM -'|| SQLERRM);
4177: END IF;
4178: -- Raise Exception
4179: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4180: END;
4181: G_DEBUG_LINE_NUM := 30;
4182: IF (x_return_status = FND_API.G_RET_STS_ERROR)
4183: THEN
4184: IF (l_log_error >= l_log_current_level) THEN
4185: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':API Perform_Inter_Org_Transfer_Inv returned error.');
4186: END IF;
4187: -- Raise exception
4188: RAISE FND_API.G_EXC_ERROR;
4189: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
4188: RAISE FND_API.G_EXC_ERROR;
4189: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
4190: THEN
4191: IF (l_log_error >= l_log_current_level) THEN
4192: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':API Perform_Inter_Org_Transfer_Inv returned unexpected error.');
4193: END IF;
4194: -- Raise exception
4195: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4196: ELSIF (x_return_status = FND_API.G_RET_STS_SUCCESS)
4211: THEN
4212: -- Direct transfer. Create Reservation
4213: G_DEBUG_LINE_NUM := 50;
4214: IF (l_log_statement >= l_log_current_level) THEN
4215: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Shipping network type is direct for serial '||p_x_mtltfr_tbl(i).serial_number);
4216: END IF;
4217: IF (l_serial_number_tbl IS NOT NULL)
4218: THEN
4219: l_count := l_serial_number_tbl.COUNT;
4226: ELSE
4227: -- Do not create Reservation
4228: G_DEBUG_LINE_NUM := 55;
4229: IF (l_log_statement >= l_log_current_level) THEN
4230: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Shipping network type is InTransit between source org '||p_x_mtltfr_tbl(i).source_org_id||
4231: ' And dest org '||p_x_mtltfr_tbl(i).dest_org_id);
4232: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':No reservation created. For the serial '||p_x_mtltfr_tbl(i).serial_number||
4233: 'Shipment number is '||p_x_mtltfr_tbl(i).shipment_number);
4234:
4228: G_DEBUG_LINE_NUM := 55;
4229: IF (l_log_statement >= l_log_current_level) THEN
4230: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Shipping network type is InTransit between source org '||p_x_mtltfr_tbl(i).source_org_id||
4231: ' And dest org '||p_x_mtltfr_tbl(i).dest_org_id);
4232: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':No reservation created. For the serial '||p_x_mtltfr_tbl(i).serial_number||
4233: 'Shipment number is '||p_x_mtltfr_tbl(i).shipment_number);
4234:
4235: END IF;
4236: END IF;
4238: IF (l_serial_number_tbl IS NOT NULL AND l_serial_number_tbl.COUNT >0)
4239: THEN
4240: G_DEBUG_LINE_NUM := 65;
4241: IF (l_log_statement >= l_log_current_level) THEN
4242: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Reservation to be made for '||l_serial_number_tbl.COUNT||' serials');
4243: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Populate variable for resevation');
4244: END IF;
4245: l_rsv_rec.demand_source_line_detail := p_sched_material_id;
4246: l_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
4239: THEN
4240: G_DEBUG_LINE_NUM := 65;
4241: IF (l_log_statement >= l_log_current_level) THEN
4242: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Reservation to be made for '||l_serial_number_tbl.COUNT||' serials');
4243: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Populate variable for resevation');
4244: END IF;
4245: l_rsv_rec.demand_source_line_detail := p_sched_material_id;
4246: l_rsv_rec.supply_source_type_id := inv_reservation_global.g_source_type_inv;
4247: l_rsv_rec.supply_source_header_id := null;
4253: -- Call Process reservation API
4254: IF (l_log_statement >= l_log_current_level)
4255: THEN
4256: G_DEBUG_LINE_NUM := 67;
4257: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Calling PROCESS_RESERVATION API with following paramters');
4258: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':l_rsv_rec.demand_source_line_detail ='||l_rsv_rec.demand_source_line_detail||
4259: ' ,l_rsv_rec.supply_source_type_id ='||l_rsv_rec.supply_source_type_id||
4260: ' ,l_rsv_rec.subinventory_code ='||l_rsv_rec.subinventory_code||
4261: ' ,l_rsv_rec.supply_source_header_id ='||l_rsv_rec.supply_source_header_id ||
4254: IF (l_log_statement >= l_log_current_level)
4255: THEN
4256: G_DEBUG_LINE_NUM := 67;
4257: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Calling PROCESS_RESERVATION API with following paramters');
4258: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':l_rsv_rec.demand_source_line_detail ='||l_rsv_rec.demand_source_line_detail||
4259: ' ,l_rsv_rec.supply_source_type_id ='||l_rsv_rec.supply_source_type_id||
4260: ' ,l_rsv_rec.subinventory_code ='||l_rsv_rec.subinventory_code||
4261: ' ,l_rsv_rec.supply_source_header_id ='||l_rsv_rec.supply_source_header_id ||
4262: ' ,l_rsv_rec.supply_source_line_id ='||l_rsv_rec.supply_source_line_id||
4293: x_reservation_id_tbl => l_reservation_id_tbl);
4294: EXCEPTION
4295: WHEN OTHERS THEN
4296: IF (l_log_exception >= l_log_current_level) THEN
4297: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':API AHL_MM_RESERVATIONS_PVT.PROCESS_RESERVATION returned exception');
4298: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM ='||SQLERRM);
4299: END IF;
4300: -- Raise Exception
4301: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4294: EXCEPTION
4295: WHEN OTHERS THEN
4296: IF (l_log_exception >= l_log_current_level) THEN
4297: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':API AHL_MM_RESERVATIONS_PVT.PROCESS_RESERVATION returned exception');
4298: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM ='||SQLERRM);
4299: END IF;
4300: -- Raise Exception
4301: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4302: END;
4301: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4302: END;
4303: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
4304: IF (l_log_error >= l_log_current_level) THEN
4305: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':API AHL_MM_RESERVATIONS_PVT.PROCESS_RESERVATION returned error. x_msg_count ='||x_msg_count);
4306: END IF;
4307: -- Raise exception
4308: RAISE FND_API.G_EXC_ERROR;
4309: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4307: -- Raise exception
4308: RAISE FND_API.G_EXC_ERROR;
4309: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4310: IF (l_log_error >= l_log_current_level) THEN
4311: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':API AHL_MM_RESERVATIONS_PVT.PROCESS_RESERVATION returned unexpected error.');
4312: END IF;
4313: -- Raise exception
4314: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4315: ELSIF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4313: -- Raise exception
4314: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4315: ELSIF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4316: IF (l_log_statement >= l_log_current_level) THEN
4317: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':API AHL_MM_RESERVATIONS_PVT.PROCESS_RESERVATION completed successfully.');
4318: END IF;
4319: --FND_MESSAGE.Set_Name('AHL','AHL_MM_RES_MSG');
4320: --FND_MESSAGE.Set_Token('MSG','Material transfer is completed and reservation made');
4321: --FND_MSG_PUB.ADD;
4318: END IF;
4319: --FND_MESSAGE.Set_Name('AHL','AHL_MM_RES_MSG');
4320: --FND_MESSAGE.Set_Token('MSG','Material transfer is completed and reservation made');
4321: --FND_MSG_PUB.ADD;
4322: --FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Number of reservations = ' || l_reservation_id_tbl.COUNT);
4323: IF(l_reservation_id_tbl.COUNT > 0) THEN
4324: FOR i IN l_reservation_id_tbl.FIRST..l_reservation_id_tbl.LAST LOOP
4325: IF (l_log_statement >= l_log_current_level) THEN
4326: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Reservation_ID ' || i || ':= ' || l_reservation_id_tbl(i));
4322: --FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Number of reservations = ' || l_reservation_id_tbl.COUNT);
4323: IF(l_reservation_id_tbl.COUNT > 0) THEN
4324: FOR i IN l_reservation_id_tbl.FIRST..l_reservation_id_tbl.LAST LOOP
4325: IF (l_log_statement >= l_log_current_level) THEN
4326: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Reservation_ID ' || i || ':= ' || l_reservation_id_tbl(i));
4327: END IF;
4328: x_reservation_id := l_reservation_id_tbl(i);
4329: END LOOP;
4330: END IF;
4338: IF FND_API.To_Boolean(p_commit) THEN
4339: COMMIT ;
4340: END IF;
4341: IF (l_log_statement >= l_log_current_level) THEN
4342: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':API Perform_Inter_Org_Transfer completed sucessfully');
4343: END IF;
4344: EXCEPTION
4345: WHEN FND_API.G_EXC_ERROR THEN
4346: x_return_status := FND_API.G_RET_STS_ERROR;
4344: EXCEPTION
4345: WHEN FND_API.G_EXC_ERROR THEN
4346: x_return_status := FND_API.G_RET_STS_ERROR;
4347: IF (l_log_error >= l_log_current_level) THEN
4348: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Error in processing. x_return_status ='||x_return_status);
4349:
4350: END IF;
4351: ROLLBACK TO Perform_Inter_Org_Transfer;
4352: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4352: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4353: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4354: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4355: IF (l_log_error >= l_log_current_level) THEN
4356: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Error in processing. x_return_status ='||x_return_status);
4357: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM ='||SQLERRM);
4358: END IF;
4359: ROLLBACK TO Perform_Inter_Org_Transfer;
4360: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4353: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4354: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4355: IF (l_log_error >= l_log_current_level) THEN
4356: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Error in processing. x_return_status ='||x_return_status);
4357: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM ='||SQLERRM);
4358: END IF;
4359: ROLLBACK TO Perform_Inter_Org_Transfer;
4360: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4361: WHEN OTHERS THEN
4360: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4361: WHEN OTHERS THEN
4362: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4363: IF (l_log_exception >= l_log_current_level) THEN
4364: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':Error in processing. x_return_status ='||x_return_status);
4365: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM ='||SQLERRM);
4366: END IF;
4367: ROLLBACK TO Perform_Inter_Org_Transfer;
4368: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME, p_procedure_name => 'Perform_Inter_Org_Transfer', p_error_text => SQLERRM);
4361: WHEN OTHERS THEN
4362: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4363: IF (l_log_exception >= l_log_current_level) THEN
4364: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':Error in processing. x_return_status ='||x_return_status);
4365: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM ='||SQLERRM);
4366: END IF;
4367: ROLLBACK TO Perform_Inter_Org_Transfer;
4368: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME, p_procedure_name => 'Perform_Inter_Org_Transfer', p_error_text => SQLERRM);
4369: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4465: BEGIN
4466: G_DEBUG_LINE_NUM := 100;
4467: -- Log
4468: IF (l_log_statement >= l_log_current_level) THEN
4469: FND_LOG.STRING(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Entered Inter Org Transfer Inventory Procedure');
4470: IF (p_x_mtltfr_tbl IS NOT NULL) THEN
4471: FND_LOG.STRING(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Input record count ='||p_x_mtltfr_tbl.COUNT);
4472: END IF;
4473: END IF;
4467: -- Log
4468: IF (l_log_statement >= l_log_current_level) THEN
4469: FND_LOG.STRING(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Entered Inter Org Transfer Inventory Procedure');
4470: IF (p_x_mtltfr_tbl IS NOT NULL) THEN
4471: FND_LOG.STRING(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Input record count ='||p_x_mtltfr_tbl.COUNT);
4472: END IF;
4473: END IF;
4474: -- Standard start of API savepoint
4475: SAVEPOINT Perform_Inter_Org_Transfer_Inv;
4507: G_DEBUG_LINE_NUM := 110;
4508: FOR i IN p_x_mtltfr_tbl.FIRST..p_x_mtltfr_tbl.LAST
4509: LOOP
4510: IF (l_log_statement >= l_log_current_level) THEN
4511: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Calling Validate_Record for record i=['||i||']');
4512: END IF;
4513: G_DEBUG_LINE_NUM := 120;
4514: --Call Validate_record to validate the material transfer record.
4515: l_x_mtltfr_rec := p_x_mtltfr_tbl(i);
4518: x_msg_count => x_msg_count,
4519: x_msg_data => x_msg_data );
4520:
4521: IF (l_log_statement >= l_log_current_level) THEN
4522: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':After calling Validate_Record for i=['||i||']');
4523: END IF;
4524:
4525: IF (x_return_status = FND_API.G_RET_STS_ERROR)
4526: THEN
4526: THEN
4527: l_error := true;
4528: IF (l_log_statement >= l_log_current_level)
4529: THEN
4530: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Error in Validation for record no'||i);
4531: END IF;
4532: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
4533: THEN
4534: IF (l_log_statement >= l_log_current_level) THEN
4531: END IF;
4532: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)
4533: THEN
4534: IF (l_log_statement >= l_log_current_level) THEN
4535: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Unexpected error in Validation. No further processing');
4536: END IF;
4537: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4538: END IF;
4539:
4541: G_DEBUG_LINE_NUM := 130;
4542: -- End of validation
4543: --log
4544: IF (l_log_statement >= l_log_current_level) THEN
4545: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validation completed for all records');
4546: END IF;
4547: IF (l_error) THEN
4548: -- No further processing till validation errors are there
4549: G_DEBUG_LINE_NUM := 140;
4548: -- No further processing till validation errors are there
4549: G_DEBUG_LINE_NUM := 140;
4550: --log
4551: IF (l_log_statement >= l_log_current_level) THEN
4552: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validation Errors are there. Raise Error');
4553: END IF;
4554: RAISE FND_API.G_EXC_ERROR;
4555: END IF;
4556: -- No errors. Insert in interface table.
4556: -- No errors. Insert in interface table.
4557: l_error := false;
4558: --log
4559: IF (l_log_statement >= l_log_current_level) THEN
4560: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':No Validation Errors are there. Continue...');
4561: END IF;
4562:
4563: G_DEBUG_LINE_NUM := 150;
4564: l_transaction_header_id := NULL; -- this will identify the records to be picked up by transaction Manager
4569:
4570: --log
4571: G_DEBUG_LINE_NUM := 155;
4572: IF (l_log_statement >= l_log_current_level) THEN
4573: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Transaction Header id is '||l_transaction_header_id);
4574: END IF;
4575:
4576: l_transaction_id := NULL; -- one transaction id per record
4577: -- loop through the material record
4587:
4588: --log
4589: G_DEBUG_LINE_NUM := 157;
4590: IF (l_log_statement >= l_log_current_level) THEN
4591: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Shipping network type is '||l_shipping_network_code);
4592: END IF;
4593:
4594: G_DEBUG_LINE_NUM := 160;
4595: --log
4593:
4594: G_DEBUG_LINE_NUM := 160;
4595: --log
4596: IF (l_log_statement >= l_log_current_level) THEN
4597: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Calling Insert_Material for record '||i);
4598: END IF;
4599: --reset record type variable
4600: l_x_mtltfr_rec := NULL;
4601: l_x_mtltfr_rec := p_x_mtltfr_tbl(i);
4608:
4609: --log
4610: G_DEBUG_LINE_NUM := 165;
4611: IF (l_log_statement >= l_log_current_level) THEN
4612: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Return status from Insert_Material '||x_return_status);
4613: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Shipment Number for the record '||l_x_mtltfr_rec.shipment_number);
4614: END IF;
4615: -- Store the shipment id
4616: p_x_mtltfr_tbl(i).shipment_number := l_x_mtltfr_rec.shipment_number;
4609: --log
4610: G_DEBUG_LINE_NUM := 165;
4611: IF (l_log_statement >= l_log_current_level) THEN
4612: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Return status from Insert_Material '||x_return_status);
4613: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Shipment Number for the record '||l_x_mtltfr_rec.shipment_number);
4614: END IF;
4615: -- Store the shipment id
4616: p_x_mtltfr_tbl(i).shipment_number := l_x_mtltfr_rec.shipment_number;
4617:
4621:
4622: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
4623: l_error := true;
4624: IF (l_log_error >= l_log_current_level) THEN
4625: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Error in Insert_Material Procedure for record '||i);
4626: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Continuing...');
4627: END IF;
4628: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4629: l_error := true;
4622: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
4623: l_error := true;
4624: IF (l_log_error >= l_log_current_level) THEN
4625: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Error in Insert_Material Procedure for record '||i);
4626: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Continuing...');
4627: END IF;
4628: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4629: l_error := true;
4630: IF (l_log_error >= l_log_current_level) THEN
4627: END IF;
4628: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4629: l_error := true;
4630: IF (l_log_error >= l_log_current_level) THEN
4631: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Unexpected Error in Insert_Material Procedure for record '||i);
4632: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Raising Error');
4633: END IF;
4634: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4635: END IF;
4628: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4629: l_error := true;
4630: IF (l_log_error >= l_log_current_level) THEN
4631: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Unexpected Error in Insert_Material Procedure for record '||i);
4632: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Raising Error');
4633: END IF;
4634: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4635: END IF;
4636: END LOOP;
4636: END LOOP;
4637: IF(l_error ) THEN
4638: G_DEBUG_LINE_NUM := 180;
4639: IF (l_log_error >= l_log_current_level) THEN
4640: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Error in Insert_Material Procedure');
4641: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Raising Error');
4642: END IF;
4643: -- Processing stops
4644: RAISE FND_API.G_EXC_ERROR;
4637: IF(l_error ) THEN
4638: G_DEBUG_LINE_NUM := 180;
4639: IF (l_log_error >= l_log_current_level) THEN
4640: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Error in Insert_Material Procedure');
4641: FND_LOG.string(l_log_error, l_debug_module, G_DEBUG_LINE_NUM||':Raising Error');
4642: END IF;
4643: -- Processing stops
4644: RAISE FND_API.G_EXC_ERROR;
4645: END IF;
4645: END IF;
4646: -- Now process the interface records
4647: --log
4648: IF (l_log_statement >= l_log_current_level) THEN
4649: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Calling INV_TXN_MANAGER_PUB.process_Transactions');
4650: END IF;
4651: BEGIN
4652: G_DEBUG_LINE_NUM := 185;
4653: l_result := INV_TXN_MANAGER_PUB.process_Transactions( p_api_version => NULL ,
4663: EXCEPTION
4664: WHEN OTHERS
4665: THEN
4666: IF (l_log_exception >= l_log_current_level) THEN
4667: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':API INV_TXN_MANAGER_PUB.process_Transactions returned exception');
4668: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM -'|| SQLERRM);
4669: END IF;
4670: -- Raise Exception
4671: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4664: WHEN OTHERS
4665: THEN
4666: IF (l_log_exception >= l_log_current_level) THEN
4667: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':API INV_TXN_MANAGER_PUB.process_Transactions returned exception');
4668: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM -'|| SQLERRM);
4669: END IF;
4670: -- Raise Exception
4671: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4672: END;
4673: mo_global.init('AHL');
4674: IF( l_result = -1 OR x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4675: G_DEBUG_LINE_NUM := 188;
4676: IF (l_log_statement >= l_log_current_level) THEN
4677: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':INV_TXN_MANAGER_PUB.process_Transactions returned error.x_msg_data = '||x_msg_data);
4678: END IF;
4679: END IF;
4680: END IF;
4681:
4679: END IF;
4680: END IF;
4681:
4682: IF (l_log_statement >= l_log_current_level) THEN
4683: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':INV_TXN_MANAGER_PUB.process_Transactions return status is '||x_return_status);
4684: END IF;
4685: -- Now Loop thru the transaction Id table, Check Errors
4686: IF (l_transaction_id_tbl.COUNT > 0)
4687: THEN
4694: OPEN Transaction_Error_Cur(l_transaction_id_tbl(i));
4695: FETCH Transaction_Error_Cur INTO l_error_msg,l_error_code, l_item, l_serial;
4696: IF(Transaction_Error_Cur%FOUND AND (l_error_code IS NOT NULL OR trim(l_error_code) = '')) THEN
4697: IF (l_log_statement >= l_log_current_level) THEN
4698: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Error in transaction['||l_error_code||':'||l_error_msg||']');
4699: END IF;
4700: FND_MESSAGE.Set_Name('AHL','AHL_MM_MTLTFR_ERROR');
4701: FND_MESSAGE.Set_Token('MSG',l_error_msg);
4702: FND_MESSAGE.Set_Token('CODE',l_error_code);
4828: x_return_status := FND_API.G_RET_STS_SUCCESS;
4829: --log
4830: G_DEBUG_LINE_NUM := 200;
4831: IF (l_log_statement >= l_log_current_level) THEN
4832: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':In Insert_Material Procedure with shipping network type '||p_shipping_type||
4833: ',transaction header id '||p_x_txn_hdr_id);
4834: END IF;
4835:
4836: IF(p_x_txn_hdr_id IS NULL) THEN
4838: END IF;
4839: G_DEBUG_LINE_NUM := 210;
4840: SELECT Mtl_Material_Transactions_S.nextval INTO p_x_txn_intf_id FROM DUAL;
4841: IF (l_log_statement >= l_log_current_level) THEN
4842: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Interface Id created - '||p_x_txn_intf_id);
4843:
4844: END IF;
4845:
4846: IF (p_shipping_type = C_INTRANSIT_NETWORK)
4882: END IF;
4883: l_shipment_number := 'CMRO'||l_shipment_num;
4884:
4885: IF (l_log_statement >= l_log_current_level) THEN
4886: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Shipment Number created - '||l_shipment_number);
4887: END IF;
4888: ELSIF (p_shipping_type = C_DIRECT_NETWORK)
4889: THEN
4890: G_DEBUG_LINE_NUM := 230;
5046: );
5047: END IF;
5048: G_DEBUG_LINE_NUM := 260;
5049: IF (l_log_statement >= l_log_current_level) THEN
5050: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Inserted in serial interface table');
5051: END IF;
5052: INSERT
5053: INTO MTL_TRANSACTIONS_INTERFACE
5054: (
5113: l_shipment_number
5114: );
5115: G_DEBUG_LINE_NUM := 270;
5116: IF (l_log_statement >= l_log_current_level) THEN
5117: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Inserted in transaction interface table');
5118: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Returning from Insert_Material Procedure');
5119: END IF;
5120: p_x_mtltfr_rec.shipment_number := l_shipment_number;
5121: EXCEPTION
5114: );
5115: G_DEBUG_LINE_NUM := 270;
5116: IF (l_log_statement >= l_log_current_level) THEN
5117: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Inserted in transaction interface table');
5118: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Returning from Insert_Material Procedure');
5119: END IF;
5120: p_x_mtltfr_rec.shipment_number := l_shipment_number;
5121: EXCEPTION
5122: WHEN OTHERS
5122: WHEN OTHERS
5123: THEN
5124: x_return_status := FND_API.G_RET_STS_ERROR;
5125: IF (l_log_exception >= l_log_current_level) THEN
5126: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':Exception in Insert_Material');
5127: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM -'||SQLERRM);
5128: END IF;
5129: FND_MESSAGE.Set_Name('AHL','AHL_MM_MTLTFR_ERROR');
5130: FND_MESSAGE.Set_Token('MSG',SQLERRM);
5123: THEN
5124: x_return_status := FND_API.G_RET_STS_ERROR;
5125: IF (l_log_exception >= l_log_current_level) THEN
5126: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':Exception in Insert_Material');
5127: FND_LOG.string(l_log_exception, l_debug_module, G_DEBUG_LINE_NUM||':SQLERRM -'||SQLERRM);
5128: END IF;
5129: FND_MESSAGE.Set_Name('AHL','AHL_MM_MTLTFR_ERROR');
5130: FND_MESSAGE.Set_Token('MSG',SQLERRM);
5131: FND_MESSAGE.Set_Token('ITEM',p_x_mtltfr_rec.inventory_item_id);
5228: x_return_status := FND_API.G_RET_STS_SUCCESS;
5229: --log
5230: G_DEBUG_LINE_NUM := 300;
5231: IF (l_log_statement >= l_log_current_level) THEN
5232: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':In Validate_Record procedure');
5233: END IF;
5234: -- get the item name instead of id for error message purpose
5235: BEGIN
5236: SELECT concatenated_segments
5269: -- Validate transaction quantity
5270: -- transaction quantity should be +ve always.
5271: G_DEBUG_LINE_NUM := 310;
5272: IF (l_log_statement >= l_log_current_level) THEN
5273: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validating quantity '||p_x_mtltfr_rec.quantity);
5274: END IF;
5275: -- Quantity validation
5276: IF(NVL(p_x_mtltfr_rec.quantity,0) <= 0) THEN
5277: FND_MESSAGE.Set_Name('AHL','AHL_PRD_INVALID_QTY');
5280: x_return_status := FND_API.G_RET_STS_ERROR;
5281: END IF;
5282: G_DEBUG_LINE_NUM := 320;
5283: IF (l_log_statement >= l_log_current_level) THEN
5284: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validating item '||p_x_mtltfr_rec.inventory_item_id||' and org id '||p_x_mtltfr_rec.source_org_id);
5285: END IF;
5286: -- Item validation
5287: IF (p_x_mtltfr_rec.source_org_id IS NOT NULL AND p_x_mtltfr_rec.inventory_item_id IS NOT NULL) THEN
5288: OPEN ITEM_VALID_CUR_DEST(p_x_mtltfr_rec.dest_org_id, p_x_mtltfr_rec.inventory_item_id);
5296:
5297: G_DEBUG_LINE_NUM := 330;
5298: IF(ITEM_VALID_CUR_DEST%NOTFOUND) THEN
5299: IF (l_log_statement >= l_log_current_level) THEN
5300: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Item/Destination Org combination is invalid');
5301: END IF;
5302: FND_MESSAGE.Set_Name('AHL','AHL_COM_INVALID_ITEM');
5303: FND_MESSAGE.Set_Token('FIELD',NVL(l_concatenated_segments, p_x_mtltfr_rec.Inventory_Item_Id));
5304: FND_MSG_PUB.ADD;
5307: CLOSE ITEM_VALID_CUR_DEST;
5308: ELSE
5309: G_DEBUG_LINE_NUM := 340;
5310: IF (l_log_statement >= l_log_current_level) THEN
5311: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validating Source and Destination Orgs for null');
5312: END IF;
5313: IF p_x_mtltfr_rec.source_org_id IS NULL THEN
5314: IF (l_log_statement >= l_log_current_level) THEN
5315: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Source Org is null');
5311: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validating Source and Destination Orgs for null');
5312: END IF;
5313: IF p_x_mtltfr_rec.source_org_id IS NULL THEN
5314: IF (l_log_statement >= l_log_current_level) THEN
5315: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Source Org is null');
5316: END IF;
5317: FND_MESSAGE.Set_Name('AHL','AHL_MM_SOURCE_ORG_NULL');
5318: FND_MESSAGE.Set_Token('SER', p_x_mtltfr_rec.serial_number);
5319: FND_MSG_PUB.ADD;
5321: END IF;
5322: IF p_x_mtltfr_rec.dest_org_id IS NULL THEN
5323: G_DEBUG_LINE_NUM := 350;
5324: IF (l_log_statement >= l_log_current_level) THEN
5325: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Destination Org is null');
5326: END IF;
5327: FND_MESSAGE.Set_Name('AHL','AHL_MM_DEST_ORG_NULL');
5328: FND_MESSAGE.Set_Token('SER', p_x_mtltfr_rec.serial_number);
5329: FND_MSG_PUB.ADD;
5331: END IF;
5332: END IF;
5333: G_DEBUG_LINE_NUM := 352;
5334: IF (l_log_statement >= l_log_current_level) THEN
5335: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validating Item for null');
5336: END IF;
5337: IF p_x_mtltfr_rec.Inventory_Item_Id IS NULL THEN
5338: G_DEBUG_LINE_NUM := 360;
5339: IF (l_log_statement >= l_log_current_level) THEN
5336: END IF;
5337: IF p_x_mtltfr_rec.Inventory_Item_Id IS NULL THEN
5338: G_DEBUG_LINE_NUM := 360;
5339: IF (l_log_statement >= l_log_current_level) THEN
5340: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Item is null');
5341: END IF;
5342: FND_MESSAGE.Set_Name('AHL','AHL_CMP_ITEM_NULL');
5343: FND_MSG_PUB.ADD;
5344: x_return_status := FND_API.G_RET_STS_ERROR;
5345: END IF;
5346:
5347: IF (l_log_statement >= l_log_current_level) THEN
5348: G_DEBUG_LINE_NUM := 370;
5349: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validating Serial '||p_x_mtltfr_rec.Serial_Number);
5350: END IF;
5351: IF(p_x_mtltfr_rec.Serial_Number IS NULL OR p_x_mtltfr_rec.Serial_Number = FND_API.G_MISS_CHAR)
5352: THEN
5353: G_DEBUG_LINE_NUM := 380;
5351: IF(p_x_mtltfr_rec.Serial_Number IS NULL OR p_x_mtltfr_rec.Serial_Number = FND_API.G_MISS_CHAR)
5352: THEN
5353: G_DEBUG_LINE_NUM := 380;
5354: IF (l_log_statement >= l_log_current_level) THEN
5355: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Item serial is NULL. Can not be transfered');
5356: END IF;
5357: FND_MESSAGE.Set_Name('AHL','AHL_MM_SERIAL_NULL');
5358: FND_MSG_PUB.ADD;
5359: x_return_status := FND_API.G_RET_STS_ERROR;
5360: ELSE
5361: IF (p_x_mtltfr_rec.Serial_Number IS NOT NULL AND l_serial_Control_dest = 1) THEN
5362: G_DEBUG_LINE_NUM := 390;
5363: IF (l_log_statement >= l_log_current_level) THEN
5364: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Item is not serialized in destination org. Can not be transfered');
5365: END IF;
5366: FND_MESSAGE.Set_Name('AHL','AHL_PRD_SRLNUM_NOTMAND');
5367: FND_MESSAGE.Set_Token('SER',p_x_mtltfr_rec.Serial_Number);
5368: FND_MESSAGE.Set_Token('ITEM',l_concatenated_segments);
5371: END IF;
5372: END IF;
5373: IF (l_log_statement >= l_log_current_level) THEN
5374: G_DEBUG_LINE_NUM := 392;
5375: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validating Serial Control/Lot Control');
5376: END IF;
5377: IF (l_lot_control_src <> 1 AND l_lot_control_src IS NOT NULL) THEN
5378: G_DEBUG_LINE_NUM := 400;
5379: IF (l_log_statement >= l_log_current_level) THEN
5376: END IF;
5377: IF (l_lot_control_src <> 1 AND l_lot_control_src IS NOT NULL) THEN
5378: G_DEBUG_LINE_NUM := 400;
5379: IF (l_log_statement >= l_log_current_level) THEN
5380: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Item is lot controlled in destination org. Can not be transfered');
5381: END IF;
5382: FND_MESSAGE.Set_Name('AHL','AHL_MM_LOT_BASED_ITEM');
5383: FND_MSG_PUB.ADD;
5384: x_return_status := FND_API.G_RET_STS_ERROR;
5385: END IF;
5386: -- Visit Locator validation
5387: IF (l_log_statement >= l_log_current_level) THEN
5388: G_DEBUG_LINE_NUM := 402;
5389: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validating Locator');
5390: END IF;
5391: IF (p_x_mtltfr_rec.dest_locator_id IS NULL) THEN
5392: IF (l_log_statement >= l_log_current_level) THEN
5393: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Visit locator is null');
5389: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validating Locator');
5390: END IF;
5391: IF (p_x_mtltfr_rec.dest_locator_id IS NULL) THEN
5392: IF (l_log_statement >= l_log_current_level) THEN
5393: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Visit locator is null');
5394: END IF;
5395: FND_MESSAGE.Set_Name('AHL','AHL_MM_NULL_VISIT_LOCATOR');
5396: FND_MSG_PUB.ADD;
5397: x_return_status := FND_API.G_RET_STS_ERROR;
5400: OPEN LOCATOR_VALID_CUR(p_x_mtltfr_rec.dest_org_id, p_x_mtltfr_rec.dest_locator_id);
5401: FETCH LOCATOR_VALID_CUR INTO l_temp_number;
5402: IF (LOCATOR_VALID_CUR%NOTFOUND) THEN
5403: IF (l_log_statement >= l_log_current_level) THEN
5404: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Invalid destination locator');
5405: END IF;
5406: FND_MESSAGE.Set_Name('AHL','AHL_MM_INVALID_LOCATOR');
5407: FND_MESSAGE.Set_Token('ORG',NVL(l_dest_organization_name, p_x_mtltfr_rec.dest_org_id));
5408: FND_MESSAGE.Set_Token('LOC',p_x_mtltfr_rec.dest_locator_id);
5415: OPEN LOCATOR_VALID_CUR(p_x_mtltfr_rec.source_org_id, p_x_mtltfr_rec.source_locator_id);
5416: FETCH LOCATOR_VALID_CUR INTO l_temp_number;
5417: IF (LOCATOR_VALID_CUR%NOTFOUND) THEN
5418: IF (l_log_statement >= l_log_current_level) THEN
5419: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Invalid source locator');
5420: END IF;
5421: FND_MESSAGE.Set_Name('AHL','AHL_MM_INVALID_LOCATOR');
5422: FND_MESSAGE.Set_Token('ORG',NVL(l_src_organization_name,p_x_mtltfr_rec.source_org_id));
5423: FND_MESSAGE.Set_Token('LOC',p_x_mtltfr_rec.dest_locator_id);
5427: CLOSE LOCATOR_VALID_CUR;
5428: -- Check if already reserved
5429: IF (l_log_statement >= l_log_current_level) THEN
5430: G_DEBUG_LINE_NUM := 405;
5431: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validating for Existing reservation');
5432: END IF;
5433: OPEN RESERVATION_VALID_CUR(p_x_mtltfr_rec.source_org_id, p_x_mtltfr_rec.inventory_item_id, p_x_mtltfr_rec.serial_number);
5434: FETCH RESERVATION_VALID_CUR INTO l_temp_number;
5435: IF (RESERVATION_VALID_CUR%FOUND) THEN
5433: OPEN RESERVATION_VALID_CUR(p_x_mtltfr_rec.source_org_id, p_x_mtltfr_rec.inventory_item_id, p_x_mtltfr_rec.serial_number);
5434: FETCH RESERVATION_VALID_CUR INTO l_temp_number;
5435: IF (RESERVATION_VALID_CUR%FOUND) THEN
5436: IF (l_log_statement >= l_log_current_level) THEN
5437: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Reservation already exists on the serial');
5438: END IF;
5439: FND_MESSAGE.Set_Name('AHL','AHL_MM_EXIST_RES');
5440: FND_MESSAGE.Set_Token('SER',p_x_mtltfr_rec.serial_number);
5441: FND_MSG_PUB.ADD;
5444: CLOSE RESERVATION_VALID_CUR;
5445: -- Shipping network validation
5446: IF (l_log_statement >= l_log_current_level) THEN
5447: G_DEBUG_LINE_NUM := 408;
5448: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Validating for Shipping Network');
5449: END IF;
5450: OPEN SHIPPING_NETWORK_CUR(p_x_mtltfr_rec.source_org_id, p_x_mtltfr_rec.dest_org_id);
5451: FETCH SHIPPING_NETWORK_CUR INTO l_temp_number;
5452: IF (SHIPPING_NETWORK_CUR%NOTFOUND) THEN
5450: OPEN SHIPPING_NETWORK_CUR(p_x_mtltfr_rec.source_org_id, p_x_mtltfr_rec.dest_org_id);
5451: FETCH SHIPPING_NETWORK_CUR INTO l_temp_number;
5452: IF (SHIPPING_NETWORK_CUR%NOTFOUND) THEN
5453: IF (l_log_statement >= l_log_current_level) THEN
5454: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'No shipping network between the organizations');
5455: END IF;
5456: FND_MESSAGE.Set_Name('AHL','AHL_MM_NO_SHIP_NETWORK');
5457: FND_MSG_PUB.ADD;
5458: x_return_status := FND_API.G_RET_STS_ERROR;
5463: OPEN SHIPPING_ATTRIB_CUR(p_x_mtltfr_rec.source_org_id, p_x_mtltfr_rec.dest_org_id);
5464: FETCH SHIPPING_ATTRIB_CUR INTO l_temp_number;
5465: IF (SHIPPING_ATTRIB_CUR%FOUND) THEN
5466: IF (l_log_statement >= l_log_current_level) THEN
5467: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||'Internal Order required to transfer between the organizations');
5468: END IF;
5469: FND_MESSAGE.Set_Name('AHL','AHL_MM_INTERNAL_ORD_REQD');
5470: FND_MSG_PUB.ADD;
5471: x_return_status := FND_API.G_RET_STS_ERROR;
5473: CLOSE SHIPPING_ATTRIB_CUR;
5474: --This should be the last print
5475: G_DEBUG_LINE_NUM := 410;
5476: IF (l_log_statement >= l_log_current_level) THEN
5477: FND_LOG.string(l_log_statement, l_debug_module, G_DEBUG_LINE_NUM||':Leaving Validate_Record with x_return_status ='||x_return_status);
5478: END IF;
5479: END Validate_Record;
5480: -- End of changes by debadey
5481:
5667: -- Standard start of API savepoint
5668: SAVEPOINT FILTER_INSTANCES_SP;
5669:
5670: IF (l_log_procedure >= l_log_current_level) THEN
5671: FND_LOG.string(l_log_procedure, l_debug || '.begin','At the start of PLSQL procedure');
5672: END IF;
5673:
5674: -- Initialize message list if p_init_msg_list is set to TRUE.
5675: IF FND_API.to_boolean( p_init_msg_list ) THEN
5679: -- Initialize API return status to success
5680: x_return_status := FND_API.G_RET_STS_SUCCESS;
5681:
5682: IF (l_log_statement >= l_log_current_level) THEN
5683: FND_LOG.string(l_log_statement, l_debug, 'Scheduled_Material_ID :' || p_demand_Record.Scheduled_Material_ID);
5684: FND_LOG.string(l_log_statement, l_debug, 'Include_HardLimit_MRs :' || p_demand_Record.Include_HardLimit_MRs);
5685: FND_LOG.string(l_log_statement, l_debug, 'Include_SoftLimit_MRs :' || p_demand_Record.Include_SoftLimit_MRs);
5686: FND_LOG.string(l_log_statement, l_debug, 'Include_Alt_Items :' || p_demand_Record.Include_Alt_Items);
5687: FND_LOG.string(l_log_statement, l_debug, 'Organization_Name :' || p_demand_Record.Organization_Name);
5680: x_return_status := FND_API.G_RET_STS_SUCCESS;
5681:
5682: IF (l_log_statement >= l_log_current_level) THEN
5683: FND_LOG.string(l_log_statement, l_debug, 'Scheduled_Material_ID :' || p_demand_Record.Scheduled_Material_ID);
5684: FND_LOG.string(l_log_statement, l_debug, 'Include_HardLimit_MRs :' || p_demand_Record.Include_HardLimit_MRs);
5685: FND_LOG.string(l_log_statement, l_debug, 'Include_SoftLimit_MRs :' || p_demand_Record.Include_SoftLimit_MRs);
5686: FND_LOG.string(l_log_statement, l_debug, 'Include_Alt_Items :' || p_demand_Record.Include_Alt_Items);
5687: FND_LOG.string(l_log_statement, l_debug, 'Organization_Name :' || p_demand_Record.Organization_Name);
5688: FND_LOG.string(l_log_statement, l_debug, 'Serial_Number :' || p_demand_Record.Serial_Number);
5681:
5682: IF (l_log_statement >= l_log_current_level) THEN
5683: FND_LOG.string(l_log_statement, l_debug, 'Scheduled_Material_ID :' || p_demand_Record.Scheduled_Material_ID);
5684: FND_LOG.string(l_log_statement, l_debug, 'Include_HardLimit_MRs :' || p_demand_Record.Include_HardLimit_MRs);
5685: FND_LOG.string(l_log_statement, l_debug, 'Include_SoftLimit_MRs :' || p_demand_Record.Include_SoftLimit_MRs);
5686: FND_LOG.string(l_log_statement, l_debug, 'Include_Alt_Items :' || p_demand_Record.Include_Alt_Items);
5687: FND_LOG.string(l_log_statement, l_debug, 'Organization_Name :' || p_demand_Record.Organization_Name);
5688: FND_LOG.string(l_log_statement, l_debug, 'Serial_Number :' || p_demand_Record.Serial_Number);
5689: FND_LOG.string(l_log_statement, l_debug, 'Item_Owner :' || p_demand_Record.Item_Owner);
5682: IF (l_log_statement >= l_log_current_level) THEN
5683: FND_LOG.string(l_log_statement, l_debug, 'Scheduled_Material_ID :' || p_demand_Record.Scheduled_Material_ID);
5684: FND_LOG.string(l_log_statement, l_debug, 'Include_HardLimit_MRs :' || p_demand_Record.Include_HardLimit_MRs);
5685: FND_LOG.string(l_log_statement, l_debug, 'Include_SoftLimit_MRs :' || p_demand_Record.Include_SoftLimit_MRs);
5686: FND_LOG.string(l_log_statement, l_debug, 'Include_Alt_Items :' || p_demand_Record.Include_Alt_Items);
5687: FND_LOG.string(l_log_statement, l_debug, 'Organization_Name :' || p_demand_Record.Organization_Name);
5688: FND_LOG.string(l_log_statement, l_debug, 'Serial_Number :' || p_demand_Record.Serial_Number);
5689: FND_LOG.string(l_log_statement, l_debug, 'Item_Owner :' || p_demand_Record.Item_Owner);
5690: FND_LOG.string(l_log_statement, l_debug, 'Reservation_Type :' || p_demand_Record.Reservation_Type);
5683: FND_LOG.string(l_log_statement, l_debug, 'Scheduled_Material_ID :' || p_demand_Record.Scheduled_Material_ID);
5684: FND_LOG.string(l_log_statement, l_debug, 'Include_HardLimit_MRs :' || p_demand_Record.Include_HardLimit_MRs);
5685: FND_LOG.string(l_log_statement, l_debug, 'Include_SoftLimit_MRs :' || p_demand_Record.Include_SoftLimit_MRs);
5686: FND_LOG.string(l_log_statement, l_debug, 'Include_Alt_Items :' || p_demand_Record.Include_Alt_Items);
5687: FND_LOG.string(l_log_statement, l_debug, 'Organization_Name :' || p_demand_Record.Organization_Name);
5688: FND_LOG.string(l_log_statement, l_debug, 'Serial_Number :' || p_demand_Record.Serial_Number);
5689: FND_LOG.string(l_log_statement, l_debug, 'Item_Owner :' || p_demand_Record.Item_Owner);
5690: FND_LOG.string(l_log_statement, l_debug, 'Reservation_Type :' || p_demand_Record.Reservation_Type);
5691: FND_LOG.string(l_log_statement, l_debug, 'WO_Number :' || p_demand_Record.WO_Number);
5684: FND_LOG.string(l_log_statement, l_debug, 'Include_HardLimit_MRs :' || p_demand_Record.Include_HardLimit_MRs);
5685: FND_LOG.string(l_log_statement, l_debug, 'Include_SoftLimit_MRs :' || p_demand_Record.Include_SoftLimit_MRs);
5686: FND_LOG.string(l_log_statement, l_debug, 'Include_Alt_Items :' || p_demand_Record.Include_Alt_Items);
5687: FND_LOG.string(l_log_statement, l_debug, 'Organization_Name :' || p_demand_Record.Organization_Name);
5688: FND_LOG.string(l_log_statement, l_debug, 'Serial_Number :' || p_demand_Record.Serial_Number);
5689: FND_LOG.string(l_log_statement, l_debug, 'Item_Owner :' || p_demand_Record.Item_Owner);
5690: FND_LOG.string(l_log_statement, l_debug, 'Reservation_Type :' || p_demand_Record.Reservation_Type);
5691: FND_LOG.string(l_log_statement, l_debug, 'WO_Number :' || p_demand_Record.WO_Number);
5692: FND_LOG.string(l_log_statement, l_debug, 'WO_Org :' || p_demand_Record.WO_Org);
5685: FND_LOG.string(l_log_statement, l_debug, 'Include_SoftLimit_MRs :' || p_demand_Record.Include_SoftLimit_MRs);
5686: FND_LOG.string(l_log_statement, l_debug, 'Include_Alt_Items :' || p_demand_Record.Include_Alt_Items);
5687: FND_LOG.string(l_log_statement, l_debug, 'Organization_Name :' || p_demand_Record.Organization_Name);
5688: FND_LOG.string(l_log_statement, l_debug, 'Serial_Number :' || p_demand_Record.Serial_Number);
5689: FND_LOG.string(l_log_statement, l_debug, 'Item_Owner :' || p_demand_Record.Item_Owner);
5690: FND_LOG.string(l_log_statement, l_debug, 'Reservation_Type :' || p_demand_Record.Reservation_Type);
5691: FND_LOG.string(l_log_statement, l_debug, 'WO_Number :' || p_demand_Record.WO_Number);
5692: FND_LOG.string(l_log_statement, l_debug, 'WO_Org :' || p_demand_Record.WO_Org);
5693: FND_LOG.string(l_log_statement, l_debug, 'Filter_Reservations :' || p_demand_Record.Filter_Reservations);
5686: FND_LOG.string(l_log_statement, l_debug, 'Include_Alt_Items :' || p_demand_Record.Include_Alt_Items);
5687: FND_LOG.string(l_log_statement, l_debug, 'Organization_Name :' || p_demand_Record.Organization_Name);
5688: FND_LOG.string(l_log_statement, l_debug, 'Serial_Number :' || p_demand_Record.Serial_Number);
5689: FND_LOG.string(l_log_statement, l_debug, 'Item_Owner :' || p_demand_Record.Item_Owner);
5690: FND_LOG.string(l_log_statement, l_debug, 'Reservation_Type :' || p_demand_Record.Reservation_Type);
5691: FND_LOG.string(l_log_statement, l_debug, 'WO_Number :' || p_demand_Record.WO_Number);
5692: FND_LOG.string(l_log_statement, l_debug, 'WO_Org :' || p_demand_Record.WO_Org);
5693: FND_LOG.string(l_log_statement, l_debug, 'Filter_Reservations :' || p_demand_Record.Filter_Reservations);
5694: FND_LOG.string(l_log_statement, l_debug, 'Visit_Org :' || p_demand_Record.Visit_Org);
5687: FND_LOG.string(l_log_statement, l_debug, 'Organization_Name :' || p_demand_Record.Organization_Name);
5688: FND_LOG.string(l_log_statement, l_debug, 'Serial_Number :' || p_demand_Record.Serial_Number);
5689: FND_LOG.string(l_log_statement, l_debug, 'Item_Owner :' || p_demand_Record.Item_Owner);
5690: FND_LOG.string(l_log_statement, l_debug, 'Reservation_Type :' || p_demand_Record.Reservation_Type);
5691: FND_LOG.string(l_log_statement, l_debug, 'WO_Number :' || p_demand_Record.WO_Number);
5692: FND_LOG.string(l_log_statement, l_debug, 'WO_Org :' || p_demand_Record.WO_Org);
5693: FND_LOG.string(l_log_statement, l_debug, 'Filter_Reservations :' || p_demand_Record.Filter_Reservations);
5694: FND_LOG.string(l_log_statement, l_debug, 'Visit_Org :' || p_demand_Record.Visit_Org);
5695: FND_LOG.string(l_log_statement, l_debug, 'Visit_Number :' || p_demand_Record.Visit_Number);
5688: FND_LOG.string(l_log_statement, l_debug, 'Serial_Number :' || p_demand_Record.Serial_Number);
5689: FND_LOG.string(l_log_statement, l_debug, 'Item_Owner :' || p_demand_Record.Item_Owner);
5690: FND_LOG.string(l_log_statement, l_debug, 'Reservation_Type :' || p_demand_Record.Reservation_Type);
5691: FND_LOG.string(l_log_statement, l_debug, 'WO_Number :' || p_demand_Record.WO_Number);
5692: FND_LOG.string(l_log_statement, l_debug, 'WO_Org :' || p_demand_Record.WO_Org);
5693: FND_LOG.string(l_log_statement, l_debug, 'Filter_Reservations :' || p_demand_Record.Filter_Reservations);
5694: FND_LOG.string(l_log_statement, l_debug, 'Visit_Org :' || p_demand_Record.Visit_Org);
5695: FND_LOG.string(l_log_statement, l_debug, 'Visit_Number :' || p_demand_Record.Visit_Number);
5696: FND_LOG.string(l_log_statement, l_debug, 'Visit_Start_Date :' || p_demand_Record.Visit_Start_Date);
5689: FND_LOG.string(l_log_statement, l_debug, 'Item_Owner :' || p_demand_Record.Item_Owner);
5690: FND_LOG.string(l_log_statement, l_debug, 'Reservation_Type :' || p_demand_Record.Reservation_Type);
5691: FND_LOG.string(l_log_statement, l_debug, 'WO_Number :' || p_demand_Record.WO_Number);
5692: FND_LOG.string(l_log_statement, l_debug, 'WO_Org :' || p_demand_Record.WO_Org);
5693: FND_LOG.string(l_log_statement, l_debug, 'Filter_Reservations :' || p_demand_Record.Filter_Reservations);
5694: FND_LOG.string(l_log_statement, l_debug, 'Visit_Org :' || p_demand_Record.Visit_Org);
5695: FND_LOG.string(l_log_statement, l_debug, 'Visit_Number :' || p_demand_Record.Visit_Number);
5696: FND_LOG.string(l_log_statement, l_debug, 'Visit_Start_Date :' || p_demand_Record.Visit_Start_Date);
5697: FND_LOG.string(l_log_statement, l_debug, 'Visit_Unit :' || p_demand_Record.Visit_Unit);
5690: FND_LOG.string(l_log_statement, l_debug, 'Reservation_Type :' || p_demand_Record.Reservation_Type);
5691: FND_LOG.string(l_log_statement, l_debug, 'WO_Number :' || p_demand_Record.WO_Number);
5692: FND_LOG.string(l_log_statement, l_debug, 'WO_Org :' || p_demand_Record.WO_Org);
5693: FND_LOG.string(l_log_statement, l_debug, 'Filter_Reservations :' || p_demand_Record.Filter_Reservations);
5694: FND_LOG.string(l_log_statement, l_debug, 'Visit_Org :' || p_demand_Record.Visit_Org);
5695: FND_LOG.string(l_log_statement, l_debug, 'Visit_Number :' || p_demand_Record.Visit_Number);
5696: FND_LOG.string(l_log_statement, l_debug, 'Visit_Start_Date :' || p_demand_Record.Visit_Start_Date);
5697: FND_LOG.string(l_log_statement, l_debug, 'Visit_Unit :' || p_demand_Record.Visit_Unit);
5698: END IF;
5691: FND_LOG.string(l_log_statement, l_debug, 'WO_Number :' || p_demand_Record.WO_Number);
5692: FND_LOG.string(l_log_statement, l_debug, 'WO_Org :' || p_demand_Record.WO_Org);
5693: FND_LOG.string(l_log_statement, l_debug, 'Filter_Reservations :' || p_demand_Record.Filter_Reservations);
5694: FND_LOG.string(l_log_statement, l_debug, 'Visit_Org :' || p_demand_Record.Visit_Org);
5695: FND_LOG.string(l_log_statement, l_debug, 'Visit_Number :' || p_demand_Record.Visit_Number);
5696: FND_LOG.string(l_log_statement, l_debug, 'Visit_Start_Date :' || p_demand_Record.Visit_Start_Date);
5697: FND_LOG.string(l_log_statement, l_debug, 'Visit_Unit :' || p_demand_Record.Visit_Unit);
5698: END IF;
5699:
5692: FND_LOG.string(l_log_statement, l_debug, 'WO_Org :' || p_demand_Record.WO_Org);
5693: FND_LOG.string(l_log_statement, l_debug, 'Filter_Reservations :' || p_demand_Record.Filter_Reservations);
5694: FND_LOG.string(l_log_statement, l_debug, 'Visit_Org :' || p_demand_Record.Visit_Org);
5695: FND_LOG.string(l_log_statement, l_debug, 'Visit_Number :' || p_demand_Record.Visit_Number);
5696: FND_LOG.string(l_log_statement, l_debug, 'Visit_Start_Date :' || p_demand_Record.Visit_Start_Date);
5697: FND_LOG.string(l_log_statement, l_debug, 'Visit_Unit :' || p_demand_Record.Visit_Unit);
5698: END IF;
5699:
5700: --Clear the temp table before inserting new rows
5693: FND_LOG.string(l_log_statement, l_debug, 'Filter_Reservations :' || p_demand_Record.Filter_Reservations);
5694: FND_LOG.string(l_log_statement, l_debug, 'Visit_Org :' || p_demand_Record.Visit_Org);
5695: FND_LOG.string(l_log_statement, l_debug, 'Visit_Number :' || p_demand_Record.Visit_Number);
5696: FND_LOG.string(l_log_statement, l_debug, 'Visit_Start_Date :' || p_demand_Record.Visit_Start_Date);
5697: FND_LOG.string(l_log_statement, l_debug, 'Visit_Unit :' || p_demand_Record.Visit_Unit);
5698: END IF;
5699:
5700: --Clear the temp table before inserting new rows
5701: DELETE FROM ahl_applicable_instances;
5719:
5720: IF (l_mc_header_id is NULL OR l_mc_header_id = '') THEN
5721:
5722: IF (l_log_statement >= l_log_current_level) THEN
5723: FND_LOG.string(l_log_statement, l_debug, 'Requirement is not position based, returning back');
5724: END IF;
5725:
5726: --Standard check for commit
5727: IF (FND_API.TO_BOOLEAN(p_commit) AND x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
5735: --Inventory
5736: IF (p_module_type = 'INVENTORY') THEN
5737:
5738: IF (l_log_statement >= l_log_current_level) THEN
5739: FND_LOG.string(l_log_statement, l_debug, 'Called by Inventory');
5740: END IF;
5741:
5742: IF(NVL(p_demand_Record.Include_Alt_Items, 'N') <> 'Y') THEN
5743: l_inv_item_id_for_api := l_inventory_item_id;
5760: --Write in Log and exit when return status is not success
5761: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)
5762: THEN
5763: IF (l_log_statement >= l_log_current_level) THEN
5764: FND_LOG.string(l_log_statement, l_debug,
5765: 'After calling get_instances_for_req, Return Status is :' || x_return_status);
5766: END IF;
5767: RAISE FND_API.G_EXC_ERROR;
5768: END IF;
5786:
5787: IF(x_return_status <> FND_API.G_RET_STS_SUCCESS)
5788: THEN
5789: IF (l_log_statement >= l_log_current_level) THEN
5790: FND_LOG.string(l_log_statement, l_debug,
5791: 'After calling Get_Alternate_Items, Return Status is :' || x_return_status
5792: || ', l_alt_items_tbl.COUNT :' || l_alt_items_tbl.COUNT);
5793: END IF;
5794: RAISE FND_API.G_EXC_ERROR;
5806: ELSE
5807: --No items are matching SB rules, and temp table is already empty
5808: --Simply return without further processing
5809: IF (l_log_statement >= l_log_current_level) THEN
5810: FND_LOG.string(l_log_statement, l_debug,
5811: 'No items match SB rules, returning without populating temp table');
5812: END IF;
5813:
5814: --Standard check for commit
5838: p_data => l_err_msg,
5839: p_msg_index_out => l_msg_index_out);
5840:
5841: IF (l_log_statement >= l_log_current_level) THEN
5842: FND_LOG.string(l_log_statement, l_debug,
5843: 'Error message-'||l_msg_index_out||':' || l_err_msg);
5844: END IF;
5845: END LOOP;
5846:
5844: END IF;
5845: END LOOP;
5846:
5847: IF (l_log_statement >= l_log_current_level) THEN
5848: FND_LOG.string(l_log_statement, l_debug,
5849: 'Alt items is not required and primary item is invalid.'
5850: || ' Returning back without temp table population.');
5851: END IF;
5852:
5907: --Existing reservations supply specific filters
5908: IF (p_module_type = 'ER') THEN
5909:
5910: IF (l_log_statement >= l_log_current_level) THEN
5911: FND_LOG.string(l_log_statement, l_debug, 'Called by Existing reservations');
5912: END IF;
5913:
5914: --Base criteria for existing reservations
5915: l_base_query := l_base_query || ' AND SERIALS.serial_number IN'
5975: --Work Order supply specific filters
5976: ELSIF (p_module_type = 'WO') THEN
5977:
5978: IF (l_log_statement >= l_log_current_level) THEN
5979: FND_LOG.string(l_log_statement, l_debug, 'Called by Work Order Supply');
5980: END IF;
5981:
5982: --Base criteria for Work Order Supply
5983: l_base_query := l_base_query || ' AND EXISTS (SELECT 1'
6023: --Cannibalization supply specific filters
6024: ELSIF (p_module_type = 'CNB') THEN
6025:
6026: IF (l_log_statement >= l_log_current_level) THEN
6027: FND_LOG.string(l_log_statement, l_debug, 'Called by Cannibalization Supply');
6028: END IF;
6029:
6030: --Beginning of base criteria for Cannibalization Supply
6031: l_base_query := l_base_query || ' AND INST.instance_id IN'
6089:
6090: END IF;
6091:
6092: IF (l_log_statement >= l_log_current_level) THEN
6093: FND_LOG.string(l_log_statement, l_debug, 'Final Query: ' || l_base_query);
6094: END IF;
6095:
6096: --Finally run the DDL statement to populate AHL_APPLICABLE_INSTANCES
6097: EXECUTE IMMEDIATE l_base_query;
6119: END LOOP;
6120: CLOSE Get_Inst_Net_Reading;
6121:
6122: IF (l_log_statement >= l_log_current_level) THEN
6123: FND_LOG.string(l_log_statement, l_debug,'After creating a record of applicable instances'
6124: || ', no of instance+MR combination records: ' || l_appl_inst_tbl.COUNT );
6125: END IF;
6126:
6127: --Populate Effective MRs in MR Table Type
6133: END LOOP;
6134: CLOSE Get_Effective_MRs;
6135:
6136: IF (l_log_statement >= l_log_current_level) THEN
6137: FND_LOG.string(l_log_statement, l_debug,'After creating a record of effective MRs'
6138: || ', no of MRs record: ' || l_eff_mr_tbl.COUNT );
6139: END IF;
6140:
6141: IF(l_appl_inst_tbl.COUNT > 0 AND l_eff_mr_tbl.COUNT > 0)
6186: --Discard the instance if it is not satisfying build goal
6187: IF((last_acc_value + max_ctr_value - net_reading) < build_goal_value)
6188: THEN
6189: IF (l_log_statement >= l_log_current_level) THEN
6190: FND_LOG.string(l_log_statement, l_debug,
6191: ' To remove Instance ID: ' || l_appl_inst_tbl(j).ITEM_INSTANCE_ID
6192: || 'last_acc_value: ' || last_acc_value
6193: || ', max_ctr_value: ' || max_ctr_value
6194: || ', net_reading:' || net_reading
6198: DELETE AHL_APPLICABLE_INSTANCES
6199: WHERE CSI_ITEM_INSTANCE_ID = l_appl_inst_tbl(j).ITEM_INSTANCE_ID;
6200:
6201: IF (l_log_statement >= l_log_current_level) THEN
6202: FND_LOG.string(l_log_statement, l_debug,'After removing instance '
6203: || l_appl_inst_tbl(j).ITEM_INSTANCE_ID || ' from the list of applicable instances');
6204: END IF;
6205:
6206: END IF;
6220: p_count => x_msg_count,
6221: p_data => x_msg_data);
6222:
6223: IF (l_log_procedure >= l_log_current_level) THEN
6224: FND_LOG.string(l_log_procedure, l_debug || '.end','At the end of PLSQL procedure');
6225: END IF;
6226:
6227: EXCEPTION
6228: WHEN FND_API.G_EXC_ERROR THEN
6308: BEGIN
6309: -- Standard start of API savepoint
6310: SAVEPOINT GET_AVAILABLE_QUANTITY;
6311: IF (L_LOG_PROCEDURE >= L_LOG_CURRENT_LEVEL) THEN
6312: FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG || '.begin', 'At the start of the PLSQL procedure GET_AVAILABLE_QUANTITY');
6313: END IF;
6314: -- Initialize API return status to success
6315: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
6316: -- Standard call to check for call compatibility.
6367: -- Standard call to get message count and if count is 1, get message info
6368: FND_MSG_PUB.count_and_get ( p_count => x_msg_count, p_data => x_msg_data, p_encoded => FND_API.G_FALSE );
6369: -- Log API exit point
6370: IF (L_LOG_PROCEDURE >= L_LOG_CURRENT_LEVEL) THEN
6371: FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG || '.end', 'At the end of PLSQL procedure');
6372: END IF;
6373: EXCEPTION
6374: WHEN FND_API.G_EXC_ERROR THEN
6375: ROLLBACK TO GET_AVAILABLE_QUANTITY;
6445:
6446: BEGIN
6447: SAVEPOINT MM_SCH_ALL_VISIT_MTLS;
6448: IF (L_LOG_PROCEDURE >= L_LOG_CURRENT_LEVEL) THEN
6449: FND_LOG.STRING(L_LOG_PROCEDURE, L_DEBUG || '.begin', 'At the start of the PLSQL procedure SCHEDULE_MATERIALS');
6450: END IF;
6451:
6452: -- Initialize API return status to success
6453: X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;