[Home] [Help]
10: -- NOTE :
11: -- End of Comments
12:
13: -- logging globals.
14: G_LEVEL_PROCEDURE NUMBER := FND_LOG.LEVEL_PROCEDURE;
15: G_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
16:
17: G_RET_STS_SUCCESS VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
18: G_RET_STS_ERROR VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
11: -- End of Comments
12:
13: -- logging globals.
14: G_LEVEL_PROCEDURE NUMBER := FND_LOG.LEVEL_PROCEDURE;
15: G_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
16:
17: G_RET_STS_SUCCESS VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
18: G_RET_STS_ERROR VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
19: G_DELIVERY_DETAIL_ID NUMBER;
51: l_shipped_date JTF_DATE_TABLE := JTF_DATE_TABLE();
52:
53: BEGIN
54: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
55: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting item controls');
56: END IF;
57: -- get the item controls.
58: SELECT msi.serial_number_control_code, msi.lot_control_code, msi.primary_uom_code,
59: msi.inventory_item_id,mis.subinventory_code,cpt.rcv_into_org,cpt.rcv_into_ou,
117: RAISE no_data_found;
118: END IF;
119:
120: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
121: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Inserting into rcv_headers_interface');
122: END IF;
123:
124: -- populate the rcv transactions header.
125: SELECT rcv_interface_groups_s.NEXTVAL INTO x_request_group_id FROM dual;
151: INTO l_hdr_interface_id(k);
152: END LOOP;
153:
154: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
155: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting employee id and requisition information '||x_request_group_id);
156: END IF;
157:
158: -- 2. get the employee id
159: csd_receive_util.get_employee_id (fnd_global.user_id, l_emp_id);
159: csd_receive_util.get_employee_id (fnd_global.user_id, l_emp_id);
160:
161:
162: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
163: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Inserting into rcv_transactions_interface');
164: END IF;
165:
166: -- 3. insert into rcv transactions interface table.
167: l_intf_txn_id.EXTEND(l_shipment_header_id.COUNT);
292: AND wdd.delivery_detail_id = wsn.delivery_detail_id;
293:
294: -- insert into mtl serial number interface.
295: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
296: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Inserting into mtl_serial_numbers_interface total of '||l_fm_serial_num_tbl.COUNT||' records');
297: END IF;
298: FORALL j in 1 ..l_fm_serial_num_tbl.COUNT
299: INSERT INTO mtl_serial_numbers_interface
300: (transaction_interface_id, source_code,
419:
420: BEGIN
421:
422: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
423: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
424: END IF;
425:
426: -- standard check for API compatibility.
427: IF NOT Fnd_Api.Compatible_API_Call
518:
519: BEGIN
520:
521: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
522: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
523: END IF;
524:
525: -- standard check for API compatibility.
526: IF NOT Fnd_Api.Compatible_API_Call
564: WHERE user_id = l_user_id;
565:
566: -- step 2. get the currency code for the destination OU.
567: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
568: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting currency code');
569: END IF;
570:
571: -- bug#12337175 subhat.
572: -- if the destination OU is not an inventory org, then this query may not retrieve the correct/no records.
580: AND org_information_context = 'Operating Unit Information'; --'Accounting Information';
581:
582: -- step 3. get the quantity required.
583: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
584: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Currency code='||l_currency_code||'. Getting required quantity');
585: END IF;
586:
587: FOR i IN 1 ..p_product_txn_ids.COUNT
588: LOOP
605: RAISE fnd_api.g_exc_error;
606: END;
607: -- step 4. get the material account id.
608: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
609: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting material account Id');
610: END IF;
611:
612: SELECT material_account
613: INTO l_material_accounts(i)
616: END LOOP;
617:
618: -- step 4. insert the records into requisitions interface.
619: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
620: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Before inserting into requisitions interface.');
621: END IF;
622:
623: FORALL i IN 1 ..p_product_txn_ids.COUNT
624: INSERT INTO po_requisitions_interface_all (
706: -- step 5. Call the concurrent program.
707: fnd_request.set_org_id (p_destination_ous(1));
708:
709: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
710: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling REQIMPORT CP.');
711: END IF;
712: l_request_id := fnd_request.submit_request(
713: 'PO', 'REQIMPORT', NULL, NULL, FALSE,'CSD', NULL, 'ALL',
714: NULL ,'N', 'Y' , chr(0), NULL, NULL, NULL,
795: -- update csd_product_transactions.
796:
797: -- Step 1. Create internal requisition.
798: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
799: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling CSD_INTERNAL_ORDERS_PVT.create_internal_requisition');
800: END IF;
801:
802: l_product_txn_ids.EXTEND;
803: l_destination_ous.EXTEND;
872: -- update csd_product_transactions.
873:
874: -- Step 1. Create internal requisition.
875: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
876: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling CSD_INTERNAL_ORDERS_PVT.create_internal_requisition');
877: END IF;
878: CSD_INTERNAL_ORDERS_PVT.create_internal_requisition(
879: p_api_version => 1.0,
880: p_product_txn_ids => p_product_txn_ids,
895:
896: IF x_return_status <> G_RET_STS_SUCCESS
897: THEN
898: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
899: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Requisition creation has failed');
900: END IF;
901: RAISE fnd_api.g_exc_error;
902: END IF;
903:
903:
904: -- Step 2. Update the product transaction table with the requisition id.
905:
906: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
907: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Requisition created.Update prod txn tbl with req id='||x_requisition_id);
908: END IF;
909: UPDATE csd_product_transactions SET req_header_id = x_requisition_id
910: WHERE product_transaction_id IN (SELECT * FROM TABLE(CAST(p_product_txn_ids AS JTF_NUMBER_TABLE)));
911:
931: END IF;
932:
933: -- Step 6. Get the ISO order header id and line id and update csd_product_transactions.
934: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
935: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Getting ISO created and updating prod txn tbl.');
936: END IF;
937:
938: FOR i IN (SELECT ooh.header_id,
939: ool.line_id,
964:
965: EXCEPTION
966: WHEN no_data_found THEN
967: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
968: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'ISO creation failed.');
969: END IF;
970: fnd_message.set_name('CSD','CSD_CREATE_ISO_FAIL');
971: fnd_msg_pub.add;
972: fnd_msg_pub.count_and_get(p_count => x_msg_count,
973: p_data => x_msg_data);
974: retcode := 2;
975: WHEN fnd_api.g_exc_error THEN
976: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
977: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'In g_exc_error');
978: END IF;
979: retcode := 2;
980: fnd_msg_pub.count_and_get(p_count => x_msg_count,
981: p_data => x_msg_data);
1023: l_delivery_detail_id_new NUMBER;
1024: BEGIN
1025:
1026: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1027: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
1028: END IF;
1029:
1030: -- standard check for API compatibility.
1031: IF NOT Fnd_Api.Compatible_API_Call
1048:
1049: IF p_shipped_quantity > 0 AND p_shipped_quantity <> p_orig_quantity
1050: THEN
1051: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1052: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Getting the split line id');
1053: END IF;
1054: SELECT line_id
1055: INTO l_order_rec.order_line_id
1056: FROM oe_order_lines_all
1081:
1082: -- call the API to pick release the sales order.
1083: IF p_fm_serial_num_tbl.COUNT = 0 THEN
1084: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1085: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling csd_process_pvt.process_sales_order to pick release');
1086: END IF;
1087: csd_process_pvt.process_sales_order(
1088: p_api_version => 1.0,
1089: p_commit => fnd_api.g_false,
1098: );
1099: IF x_return_status <> G_RET_STS_SUCCESS
1100: THEN
1101: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1102: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Pick release errored.'||x_msg_data);
1103: END IF;
1104: RAISE fnd_api.g_exc_error;
1105: END IF;
1106:
1143:
1144: IF l_dummy = 'B' OR l_dummy = 'R' AND l_picked_quantity IS NULL
1145: THEN
1146: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1147: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'The delivery has been backordered.');
1148: END IF;
1149: fnd_message.set_name('CSD','CSD_NOT_PICK_RELEASED');
1150: fnd_msg_pub.add;
1151: RAISE fnd_api.g_exc_error;
1152:
1153: ELSIF l_dummy = 'S'
1154: THEN
1155: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1156: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'The line needs to be transacted using transact move order.');
1157: END IF;
1158: fnd_message.set_name('CSD','CSD_TRANSACT_MOVE_ORDER');
1159: fnd_msg_pub.add;
1160: RAISE fnd_api.g_exc_error;
1170: WHERE wdd.split_from_delivery_detail_id = l_delivery_detail_id
1171: AND released_status = 'B';
1172:
1173: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1174: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Picking is partial.Splitting the product transaction id by making call to handle_partial_action');
1175: END IF;
1176:
1177: UPDATE csd_product_transactions SET quantity_picked = l_picked_quantity
1178: WHERE order_header_id = p_order_header_id
1192: END;
1193: END IF;
1194:
1195: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1196: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating the picked quantity.');
1197: END IF;
1198:
1199: UPDATE csd_product_transactions SET quantity_available = 0, quantity_picked = l_picked_quantity
1200: WHERE order_header_id = p_order_header_id
1203: END IF;
1204:
1205: ELSE
1206: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1207: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling pick_release to pick release');
1208: END IF;
1209: pick_release(
1210: p_api_version => 1.0,
1211: p_commit => fnd_api.g_false,
1230:
1231: IF x_return_status <> G_RET_STS_SUCCESS
1232: THEN
1233: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1234: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Pick release errored.'||x_msg_data);
1235: END IF;
1236: RAISE fnd_api.g_exc_error;
1237: END IF;
1238:
1239: END IF;
1240:
1241: -- update the product transaction record. Mark it as picked.
1242: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1243: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating ISO as released.');
1244: END IF;
1245: /*UPDATE csd_product_transactions SET release_sales_order_flag = 'Y',
1246: prod_txn_status = 'RELEASED'
1247: WHERE product_transaction_id = p_product_txn_id;*/
1342:
1343: BEGIN
1344:
1345: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1346: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
1347: END IF;
1348:
1349: -- standard check for API compatibility.
1350: IF NOT Fnd_Api.Compatible_API_Call
1417: END IF; -- reservable check over.
1418:
1419: -- get the delivery detail id.
1420: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1421: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Getting the delivery detail id for order header '||p_order_header_id);
1422: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Getting the delivery detail id for order line '||p_order_line_id);
1423: END IF;
1424:
1425: BEGIN
1418:
1419: -- get the delivery detail id.
1420: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1421: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Getting the delivery detail id for order header '||p_order_header_id);
1422: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Getting the delivery detail id for order line '||p_order_line_id);
1423: END IF;
1424:
1425: BEGIN
1426: SELECT wdd.delivery_detail_id,wda.delivery_id
1448: AND wdd.source_code = 'OE';
1449: END;
1450: -- populate the SN's
1451: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1452: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Populating the serial numbers');
1453: END IF;
1454: IF fnd_api.to_boolean(p_is_sn_range) AND p_fm_serial_num_tbl IS NOT NULL
1455: THEN
1456: FOR j IN 1 ..p_fm_serial_num_tbl.COUNT
1495: changed_attributes(1).lot_number := p_lot_num;
1496: END IF;
1497:
1498: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1499: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes');
1500: END IF;
1501:
1502: IF l_attribs_changed_flag
1503: THEN
1514: END IF;
1515:
1516: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1517: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1518: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Update shipping attributes failed '||x_msg_data);
1519: END IF;
1520: --dbms_output.put_line('Update shipping attributes failed '||x_msg_data);
1521: fnd_message.set_name('CSD','CSD_UPDATE_SHIPPING_FAILED');
1522: fnd_message.set_token('err_msg', x_msg_data);
1542: from wsh_delivery_details
1543: where delivery_detail_id = l_delivery_detail_id;
1544:
1545: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1546: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'organization_id = '||l_delivery_org_id);
1547: END IF;
1548:
1549: WSH_SHIPPING_PARAMS_GRP.Get_Shipping_Parameters(
1550: l_delivery_org_id,
1551: l_ship_param_info,
1552: x_return_status);
1553:
1554: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1555: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'x_return_status(Get_Shipping_Parameters)='||x_return_status);
1556: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'DELIVERY_REPORT_SET_ID = '||l_ship_param_info.DELIVERY_REPORT_SET_ID);
1557: END IF;
1558:
1559: p_sc_report_set_id := l_ship_param_info.DELIVERY_REPORT_SET_ID; -- check if it is seeded
1552: x_return_status);
1553:
1554: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1555: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'x_return_status(Get_Shipping_Parameters)='||x_return_status);
1556: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'DELIVERY_REPORT_SET_ID = '||l_ship_param_info.DELIVERY_REPORT_SET_ID);
1557: END IF;
1558:
1559: p_sc_report_set_id := l_ship_param_info.DELIVERY_REPORT_SET_ID; -- check if it is seeded
1560:
1558:
1559: p_sc_report_set_id := l_ship_param_info.DELIVERY_REPORT_SET_ID; -- check if it is seeded
1560:
1561: IF (x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1562: --fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Get_Shipping_Parameters failed');
1563: RAISE fnd_api.g_exc_error;
1564: END IF;
1565:
1566: --bug#14632475 --bug#14644674
1566: --bug#14632475 --bug#14644674
1567:
1568:
1569: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1570: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling wsh_deliveries_pub.delivery_action for ship confirm action');
1571: END IF;
1572:
1573:
1574: wsh_deliveries_pub.delivery_action(
1629: END IF;
1630:
1631: -- update the csd_product_transactions table with the shipped quantity information.
1632: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1633: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating quantity shipped in prod txn tbl');
1634: END IF;
1635:
1636: SELECT shipped_quantity
1637: INTO l_quantity_shipped
1669: END IF;*/
1670: fnd_msg_pub.count_and_get(p_count => x_msg_count,
1671: p_data => x_msg_data);
1672: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1673: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Error in ship confirm action '||x_msg_data);
1674: END IF;
1675:
1676: x_return_status := fnd_api.g_ret_sts_error;
1677: WHEN no_data_found THEN
1777: l_received_quantity NUMBER;
1778: l_delivery_detail_id NUMBER;
1779: BEGIN
1780: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1781: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
1782: END IF;
1783:
1784: -- standard check for API compatibility.
1785: IF NOT Fnd_Api.Compatible_API_Call
1798:
1799: x_return_status := G_RET_STS_SUCCESS;
1800: -- find if there is atleast quantity of 1 shipped for this internal order.
1801: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1802: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Checking for quantity to be received.');
1803: END IF;
1804:
1805: SELECT nvl(quantity_shipped,0),delivery_detail_id
1806: INTO l_dummy,l_delivery_detail_id
1817: END IF;
1818:
1819: -- validate if the internal requisition is already received based on shipping networks setup.
1820: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1821: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Validating if the IR is already received via shipping networks setup.');
1822: END IF;
1823: BEGIN
1824: SELECT SUM(rt.quantity)
1825: INTO l_received_quantity
1883: END IF;
1884:
1885: -- populate the rcv int tables.
1886: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1887: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling populate_rcv_int_tables to populate the receiving interfaces.');
1888: END IF;
1889: populate_rcv_int_tables(p_product_txn_id => p_product_txn_id,
1890: p_receiving_subinv => p_receiving_subinv,
1891: x_request_group_id => x_request_group_id);
1891: x_request_group_id => x_request_group_id);
1892:
1893: -- call the receive API to receive the lines.
1894: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1895: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling csd_receive_pvt.rcv_req_online to auto receive internal requisitions');
1896: END IF;
1897: csd_receive_pvt.rcv_req_online
1898: (p_api_version => 1.0,
1899: p_init_msg_list => fnd_api.g_false,
1906: );
1907: IF x_return_status <> G_RET_STS_SUCCESS
1908: THEN
1909: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1910: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Error in online receipt of IR.');
1911: END IF;
1912: RAISE fnd_api.g_exc_error;
1913: END IF;
1914:
1913: END IF;
1914:
1915: -- update the quantity received field in csd_product_transactions with receipt quantity.
1916: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1917: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating csd_product_transactions.received_quantity');
1918: END IF;
1919:
1920: -- find out the transacted quantity from the rcv_transactions.
1921: SELECT SUM(quantity)
1952: END IF;
1953: EXCEPTION
1954: WHEN fnd_api.g_exc_error THEN
1955: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1956: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Execution error:'||x_msg_data);
1957: END IF;
1958: -- message has been already set and retrieved.
1959: x_return_status := G_RET_STS_ERROR;
1960:
1959: x_return_status := G_RET_STS_ERROR;
1960:
1961: WHEN l_validation THEN
1962: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
1963: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Validation error:'||x_msg_data);
1964: END IF;
1965: fnd_msg_pub.count_and_get(p_count => x_msg_count,
1966: p_data => x_msg_data);
1967: x_return_status := G_RET_STS_ERROR;
2040: ALLOCATION_ERROR EXCEPTION;
2041:
2042: BEGIN
2043: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2044: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Begin API');
2045: END IF;
2046:
2047: -- standard check for API compatibility.
2048: IF NOT Fnd_Api.Compatible_API_Call
2076:
2077: IF NOT (l_delivery_status = 'S' OR l_delivery_status = 'Y') THEN
2078: -- fetch the customer details.
2079: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2080: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Fetching customer, order type and source document');
2081: END IF;
2082: SELECT sold_to_org_id,
2083: order_type_id,
2084: source_document_type_id
2090:
2091: -- get the defaults from picking rule.
2092: -- Auto pick confirm flag will be always set to N in this API
2093: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2094: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Fetching picking defaults');
2095: END IF;
2096:
2097: SELECT NVL(document_set_id, l_document_set_id),
2098: 'I',
2162: WHERE PICKING_RULE_ID = p_picking_rule_id;
2163:
2164: -- create the release batch.
2165: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2166: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Creating the release batch');
2167: END IF;
2168:
2169: WSH_PICKING_BATCHES_GRP.Create_Batch(
2170: p_api_version_number => 1.0,
2180: x_batch_id => x_batch_id
2181: );
2182: IF NOT x_return_status = G_RET_STS_SUCCESS THEN
2183: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2184: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'WSH_PICKING_BATCHES_GRP.Create_Batch has failed:'||x_msg_data);
2185: END IF;
2186: RAISE CREATE_BATCH;
2187: END IF;
2188:
2186: RAISE CREATE_BATCH;
2187: END IF;
2188:
2189: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2190: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Created the release batch, calling release batch now');
2191: END IF;
2192:
2193: wsh_picking_batches_pub.Release_Batch(
2194: p_api_version => 1.0,
2204: x_request_id => x_request_id
2205: );
2206:
2207: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2208: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'after wsh_picking_batches_pub.Release_Batch, return status:'||x_return_status);
2209: END IF;
2210: IF x_return_status = wsh_util_core.g_ret_sts_warning THEN
2211: -- treat warning as success.
2212: x_return_status := G_RET_STS_SUCCESS;
2213: END IF;
2214:
2215: IF x_return_status = G_RET_STS_ERROR THEN
2216: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2217: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Release batch is failed!');
2218: END IF;
2219: RAISE RELEASE_BATCH;
2220: END IF;
2221: END IF;
2224: -- allocation.
2225: IF NOT l_delivery_status = 'Y' THEN
2226: BEGIN
2227: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2228: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Getting allocations if any for order header id:'||p_order_header_id);
2229: END IF;
2230:
2231: SELECT mtrh.header_id,mmtt.transaction_temp_id,
2232: mtrl.line_id,wdd.delivery_detail_id
2243: AND mtrl.header_id = mtrh.header_id
2244: AND mtrl.line_id = mmtt.move_order_line_id(+);
2245:
2246: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2247: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Fetched move order header id '||l_move_order_hdr_id);
2248: END IF;
2249:
2250: EXCEPTION
2251: WHEN no_data_found THEN
2249:
2250: EXCEPTION
2251: WHEN no_data_found THEN
2252: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2253: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'No allocations were found!');
2254: END IF;
2255: l_move_order_hdr_id := 0;
2256: END;
2257:
2265: WHERE wdd.delivery_detail_id = cpt.delivery_detail_id
2266: AND cpt.product_transaction_id = p_product_txn_id;
2267:
2268: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2269: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Creating the allocations for move line id'||l_move_order_line_id);
2270: END IF;
2271: inv_replenish_detail_pub.line_details_pub
2272: (p_line_id => l_move_order_line_id,
2273: x_number_of_rows => x_number_of_rows,
2290: p_auto_pick_confirm => FALSE,
2291: p_commit => FALSE
2292: );
2293: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2294: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Returned from inv_replenish_detail_pub.line_details. Return status'||x_return_status);
2295: END IF;
2296:
2297: IF x_return_status <> G_RET_STS_SUCCESS THEN
2298: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2295: END IF;
2296:
2297: IF x_return_status <> G_RET_STS_SUCCESS THEN
2298: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2299: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Error while trying to create allocations..'||x_msg_data);
2300: END IF;
2301: RAISE ALLOCATION_ERROR;
2302: END IF;
2303:
2301: RAISE ALLOCATION_ERROR;
2302: END IF;
2303:
2304: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2305: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Successfully created the allocation: Transaction temp id is='||l_mmtt_temp_id);
2306: END IF;
2307: END IF;
2308:
2309: IF l_mmtt_temp_id <> 0 THEN
2308:
2309: IF l_mmtt_temp_id <> 0 THEN
2310:
2311: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2312: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Checking if the item is reservable..');
2313: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'mover order line id:'||l_move_order_line_id);
2314: END IF;
2315:
2316: SELECT msi.reservable_type,msi.inventory_item_id,
2309: IF l_mmtt_temp_id <> 0 THEN
2310:
2311: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2312: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Checking if the item is reservable..');
2313: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'mover order line id:'||l_move_order_line_id);
2314: END IF;
2315:
2316: SELECT msi.reservable_type,msi.inventory_item_id,
2317: msi.organization_id
2323: AND mtrl.organization_id = msi.organization_id
2324: AND mtrl.line_id = l_move_order_line_id;
2325:
2326: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2327: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Reservation flag:'||l_reservable_flag);
2328: END IF;
2329:
2330: IF l_reservable_flag = 1 THEN
2331: BEGIN
2340: END;
2341: END IF;
2342:
2343: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2344: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Existing reservation id:'||l_reservation_id);
2345: END IF;
2346:
2347: SELECT fm_serial_number, to_serial_number
2348: BULK COLLECT INTO
2351: WHERE transaction_temp_id = l_mmtt_temp_id;
2352:
2353: -- unmark the serials.
2354: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2355: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Unmarking the existing range');
2356: END IF;
2357:
2358: FOR i in 1 ..l_fm_serial_number.COUNT
2359: LOOP
2370: END LOOP;
2371:
2372: -- delete all the existing serials from MSNT.
2373: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2374: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Deleting from MSNT for transaction_temp_id='||l_mmtt_temp_id);
2375: END IF;
2376:
2377: DELETE FROM mtl_serial_numbers_temp WHERE transaction_temp_id = l_mmtt_temp_id;
2378:
2377: DELETE FROM mtl_serial_numbers_temp WHERE transaction_temp_id = l_mmtt_temp_id;
2378:
2379: -- mark the newly passed serials. And insert them into MSNT.
2380: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2381: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Marking the newly entered serial ranges');
2382: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Range count:'||p_fm_serial_num_tbl.COUNT);
2383:
2384: END IF;
2385:
2378:
2379: -- mark the newly passed serials. And insert them into MSNT.
2380: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2381: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Marking the newly entered serial ranges');
2382: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Range count:'||p_fm_serial_num_tbl.COUNT);
2383:
2384: END IF;
2385:
2386: FOR j in 1 ..p_fm_serial_num_tbl.COUNT
2399: success => x_success
2400: );
2401:
2402: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2403: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Inserting into msnt='||l_mmtt_temp_id);
2404: END IF;
2405:
2406: INSERT INTO mtl_serial_numbers_temp
2407: (transaction_temp_id,
2433: -- the serial number allocation would have been already addressed above.
2434: IF l_picking_qty < l_requested_qty
2435: THEN
2436: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2437: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating the existing allocations for temp id ='||l_mmtt_temp_id);
2438: END IF;
2439: inv_missing_qty_actions_engine.update_allocation_qty
2440: (x_return_status => x_return_status,
2441: x_msg_data => x_msg_data,
2446: );
2447: IF x_return_status <> G_RET_STS_SUCCESS
2448: THEN
2449: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2450: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Error in inv_missing_qty_actions_engine.update_allocation_qty. Msg is '||x_msg_data);
2451: END IF;
2452: RAISE ALLOCATION_ERROR;
2453: END IF;
2454:
2454:
2455: -- we need to process the allocations such that a back order gets created for
2456: -- the remaining quantity.
2457: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2458: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling process action to back order remaining quantity ');
2459: END IF;
2460: inv_missing_qty_actions_engine.process_action
2461: (x_return_status => x_return_status,
2462: x_msg_data => x_msg_data,
2468: );
2469: IF x_return_status <> G_RET_STS_SUCCESS
2470: THEN
2471: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2472: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Error in process_action API. Msg = '||x_msg_data);
2473: END IF;
2474: RAISE ALLOCATION_ERROR;
2475: END IF;
2476: END IF;
2481: -- INV team says that the API queries all the remaining details itself.
2482: l_trolin_tbl(1).line_id := l_move_order_line_id;
2483: -- call the pick confirm api
2484: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2485: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm');
2486: END IF;
2487:
2488: INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm
2489: (p_api_version_number => 1.0,
2500: x_trolin_tbl => l_trolin_tbl
2501: );
2502: IF x_return_status <> G_RET_STS_SUCCESS THEN
2503: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2504: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'ERROR in INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm');
2505: END IF;
2506: RAISE RELEASE_BATCH;
2507: END IF;
2508: END IF;
2510: -- Check if the delivery is successfully pick confirmed.
2511: IF l_move_order_line_id IS NOT NULL
2512: THEN
2513: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2514: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Checking if the delivery is successfully pick released. DDID='||l_delivery_detail_id);
2515: END IF;
2516: SELECT released_status,delivery_detail_id,picked_quantity
2517: INTO l_delivery_status,l_delivery_detail_id_new,l_picked_qty
2518: FROM wsh_delivery_details
2525: END IF;
2526:
2527: IF l_picked_qty IS NOT NULL THEN
2528: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2529: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Updating the picked quantity: Quantity picked = '||l_picked_qty);
2530: END IF;
2531: UPDATE csd_product_transactions SET quantity_picked = l_picked_qty,
2532: delivery_detail_id = l_delivery_detail_id_new
2533: WHERE order_header_id = p_order_header_id
2535: END IF;
2536:
2537: -- check if its a partial release.
2538: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2539: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Checking if the pick release was partial.');
2540: END IF;
2541: BEGIN
2542: SELECT 'Y'
2543: INTO l_dummy
2554:
2555: IF l_dummy = 'Y'
2556: THEN
2557: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2558: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Picking is partial.Splitting the product transaction id by making call to handle_partial_action');
2559: END IF;
2560: handle_partial_action
2561: (action => 'PICK',
2562: p_order_header_id => p_order_header_id,
2565: );
2566: ELSE
2567: -- not a partial pick. The quantity available to pick needs to be updated to 0.
2568: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2569: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'Successfully picked the available quantity. Updating the quantity available to zero for this line');
2570: END IF;
2571:
2572: UPDATE csd_product_transactions SET quantity_available = 0
2573: WHERE delivery_detail_id = l_delivery_detail_id_new;
2580:
2581: EXCEPTION
2582: WHEN CREATE_BATCH THEN
2583: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2584: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'In create_batch exception');
2585: END IF;
2586: x_return_status := G_RET_STS_ERROR;
2587: ROLLBACK TO RELEASE_SALESORDER;
2588: WHEN RELEASE_BATCH THEN
2586: x_return_status := G_RET_STS_ERROR;
2587: ROLLBACK TO RELEASE_SALESORDER;
2588: WHEN RELEASE_BATCH THEN
2589: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2590: fnd_log.string(G_LEVEL_PROCEDURE, lc_api_name,'In release_batch exception');
2591: END IF;
2592: fnd_message.set_name('CSD', 'CSD_PICK_RELEASE_FAIL');
2593: fnd_message.set_token('BATCH_NAME', l_batch_name);
2594: fnd_msg_pub.add;
2671: BEGIN
2672:
2673: fnd_request.set_org_id (p_ir_operating_unit);
2674: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2675: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling POCISO to populate OM interface tables');
2676: END IF;
2677: l_request_id := fnd_request.submit_request(
2678: 'PO', 'POCISO', NULL, NULL, FALSE );
2679: COMMIT;
2695: END IF;
2696:
2697: -- step 4. Create the internal sales order. Call Import Orders CP.
2698: IF (G_LEVEL_PROCEDURE >= G_RUNTIME_LEVEL) THEN
2699: Fnd_Log.STRING(G_LEVEL_PROCEDURE, lc_api_name,'Calling OEOIMP to create ISO');
2700: END IF;
2701: -- bug#12337175, subhat.
2702: -- The OU should be source OU. Since the ISO is always created in the source OU.
2703: -- change p_destination_ous to p_source_ous