[Home] [Help]
4:
5: -----------------------------------------------------
6: -- Global declarations
7: -----------------------------------------------------
8: SUBTYPE WDR_ROW IS WMS_DEVICE_REQUESTS%ROWTYPE;
9: SUBTYPE WDRH_ROW IS WMS_DEVICE_REQUESTS_HIST%ROWTYPE;
10:
11:
12: -----------------------------------------------------
5: -----------------------------------------------------
6: -- Global declarations
7: -----------------------------------------------------
8: SUBTYPE WDR_ROW IS WMS_DEVICE_REQUESTS%ROWTYPE;
9: SUBTYPE WDRH_ROW IS WMS_DEVICE_REQUESTS_HIST%ROWTYPE;
10:
11:
12: -----------------------------------------------------
13: -- trace
42:
43: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
44: BEGIN
45:
46: SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
47: x_request_id := l_request_id;
48:
49: SELECT wdd1.organization_id,
50: wdd1.subinventory,
68: WHERE wdd1.DELIVERY_DETAIL_ID = p_task_trx_id
69: AND wdd1.delivery_detail_id = wda.parent_delivery_detail_id
70: AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id;
71:
72: insert INTO wms_device_requests (request_id,
73: task_id,
74: task_summary,
75: business_event_id,
76: organization_id,
148:
149: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
150: BEGIN
151:
152: SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
153: x_request_id := l_request_id;
154:
155: SELECT organization_id,
156: subinventory_code,
204: trace('l_xfr_lpn_id:'||l_xfr_lpn_id);
205: trace('l_lpn_id:'||l_lpn_id);
206: END IF;
207:
208: insert INTO wms_device_requests (request_id,
209: task_id,
210: task_summary,
211: business_event_id,
212: organization_id,
299:
300: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
301: BEGIN
302:
303: SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
304: x_request_id := l_request_id;
305:
306: --Added for Bug#8778050 start
307:
333: IF (l_debug = 1) THEN
334: trace(' sub,loc,xfr_sub,xfr_loc:'||l_subinv||','||l_locator_id||','||l_xfr_subinv||','||l_xfr_locator_id);
335: END IF;
336:
337: insert INTO wms_device_requests (request_id,
338: task_id,
339: task_summary,
340: business_event_id,
341: organization_id,
426:
427: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
428: BEGIN
429:
430: SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
431: x_request_id := l_request_id;
432:
433: IF (p_bus_event=WMS_BE_PICK_DROP) OR
434: ((p_bus_event=WMS_BE_PUTAWAY_DROP) AND (p_xfr_subinv IS NOT NULL)) THEN
450: IF (l_debug = 1) THEN
451: trace('Overloaded retrieve_Bus_Event_Details: sub,loc,xfr_sub,xfr_loc,dev_id:'||l_subinv||','||l_locator_id||','||l_xfr_subinv||','||l_xfr_locator_id||','||p_device_id);
452: END IF;
453:
454: insert INTO wms_device_requests (request_id,
455: task_id,
456: task_summary,
457: business_event_id,
458: organization_id,
542: ELSE
543: l_qty := wdrrec.transaction_quantity;
544: END IF;
545: l_count := l_count + 1;
546: INSERT INTO wms_device_requests (request_id,
547: task_id,
548: relation_id,
549: sequence_id,
550: task_summary,
622: ---------------------------------------------------------
623: -- select_Device
624: --
625: ---------------------------------------------------------
626: FUNCTION select_Device(wdrrec WMS_DEVICE_REQUESTS%ROWTYPE,
627: p_autoenable VARCHAR2,
628: p_parent_request_id NUMBER
629: ) return NUMBER is
630:
653: dev_id := 0;
654: BEGIN
655: SELECT device_id, task_id
656: INTO dev_id, par_task_id
657: FROM wms_device_requests_hist
658: WHERE request_id = p_parent_request_id
659: AND ROWNUM < 2;
660: EXCEPTION
661: WHEN OTHERS THEN
827: l_notification_flag := 'Y';
828: END IF;
829:
830: IF l_notification_flag = 'Y' THEN
831: UPDATE wms_device_requests
832: SET device_id = dev_id,
833: task_id = nvl(par_task_id, task_id)
834: WHERE request_id = wdrrec.request_id
835: AND Nvl(task_type_id,0) = Nvl(wdrrec.task_type_id,Nvl(task_type_id,0))
941: wdr.uom UOM, wdr.lot_number LOT, wdr.lot_qty LOTQTY,
942: wdr.serial_number serial,
943: wdr.status_msg STATUSMSG, wdr.last_update_date timestamp,
944: wdr.business_event_id bus_event_id
945: FROM wms_device_requests wdr, mfg_lookups ml1, mfg_lookups ml2,
946: wms_devices_vl wd, mtl_parameters mp1, mtl_item_locations_kfv milk1,
947: mtl_parameters mp2, mtl_item_locations_kfv milk2, wms_license_plate_numbers wlpn,
948: mtl_system_items_kfv msik
949: WHERE ml1.lookup_type(+)= 'WMS_TASK_TYPES' AND ml1.lookup_code(+) = wdr.task_type_id
978: wdr.uom ||l_seperator|| wdr.lot_number ||l_seperator||
979: wdr.lot_qty||l_seperator||
980: wdr.serial_number||l_seperator||wdr.status_msg||l_seperator||wdr.last_update_date
981: CSV_LINE
982: FROM wms_device_requests wdr, mfg_lookups ml1, mfg_lookups ml2,
983: wms_devices_vl wd, mtl_parameters mp1, mtl_item_locations_kfv milk1,
984: mtl_parameters mp2, mtl_item_locations_kfv milk2, wms_license_plate_numbers wlpn,wms_license_plate_numbers wlpn1, --Added for Bug#8512121
985: mtl_system_items_kfv msik
986: WHERE ml1.lookup_type(+) = 'WMS_TASK_TYPES' AND ml1.lookup_code(+) = wdr.task_type_id
1029: FROM wms_devices
1030: WHERE device_id = p_device_id;
1031:
1032: SELECT request_id INTO l_seq_id
1033: FROM wms_device_requests
1034: WHERE device_id = p_device_id
1035: AND ROWNUM<2;
1036:
1037: EXCEPTION
1062: -- Change to just check existence
1063: SELECT 1 INTO l_detail_available FROM dual
1064: WHERE exists(
1065: SELECT 1
1066: FROM wms_device_requests
1067: WHERE device_id = p_device_id
1068: AND nvl(task_summary,'Y') = 'N');
1069:
1070: IF (l_debug = 1) THEN
1212: -- update outfile_name
1213: IF (l_debug = 1) THEN
1214: trace('update outfile_name ' || l_file_name || p_device_id || l_task_sum);
1215: END IF;
1216: UPDATE wms_device_requests
1217: SET outfile_name = l_file_name
1218: WHERE device_id = p_device_id
1219: AND nvl(task_summary, 'Y') = decode(l_lot_serial_enabled,'N','Y','Y',nvl(task_summary,'Y'),'Y');
1220:
1266: p_bus_event IN NUMBER,
1267: x_device_records_exist OUT NOCOPY VARCHAR2) IS -- Modified for bug#8778050
1268:
1269: l_counter NUMBER := 0;
1270: CURSOR cur_dev IS SELECT * FROM wms_device_requests where device_id is
1271: not null;
1272:
1273: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1274: BEGIN
1279: trace('outfile_name='||l_rec.outfile_name||'request_id:'||l_rec.request_id||'task_id:'||l_rec.task_id);
1280: END IF;
1281: */
1282: l_counter := l_counter +1;
1283: INSERT INTO wms_device_requests_hist (request_id,
1284: task_id,
1285: relation_id,
1286: sequence_id,
1287: task_summary,
1381:
1382: END;
1383:
1384: ----------------------------------------------------------
1385: ---- Move rows back to the wms_device_requests temp table
1386: ----------------------------------------------------------
1387:
1388: PROCEDURE move_resubmit_rows(p_request_id number, p_bus_event_id number)IS
1389:
1394: IF (l_debug = 1) THEN
1395: trace('Move from Hist table to temp table');
1396: END IF;
1397:
1398: insert into wms_device_requests (
1399: BUSINESS_EVENT_ID
1400: ,DEVICE_ID
1401: ,DEVICE_STATUS
1402: ,INVENTORY_ITEM_ID
1458: ,TRANSFER_ORG_ID
1459: ,TRANSFER_SUB_CODE
1460: ,UOM
1461: ,XFER_LPN_ID
1462: from wms_device_requests_hist
1463: WHERE request_id = p_request_id
1464: AND status_code ='P'
1465: AND Nvl(business_event_id,-1) = Nvl(p_bus_event_id,-1);
1466:
1526: x_return_status OUT NOCOPY VARCHAR2,
1527: x_msg_count OUT NOCOPY NUMBER,
1528: x_msg_data OUT NOCOPY VARCHAR2,
1529: p_request_id IN OUT NOCOPY NUMBER) IS
1530: cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
1531: cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
1532:
1533: l_seldev number;
1534: l_cur_dev number;
1527: x_msg_count OUT NOCOPY NUMBER,
1528: x_msg_data OUT NOCOPY VARCHAR2,
1529: p_request_id IN OUT NOCOPY NUMBER) IS
1530: cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
1531: cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
1532:
1533: l_seldev number;
1534: l_cur_dev number;
1535: l_lot_ser_ok varchar2(1);
1587: x_return_status := FND_API.G_RET_STS_SUCCESS;
1588: RETURN;
1589: END IF;
1590:
1591: SAVEPOINT WMS_DEVICE_REQUESTS;
1592: -- Initialize message list if p_init_msg_list is set to TRUE.
1593: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1594: FND_MSG_PUB.initialize;
1595: END IF;
1640: RAISE FND_API.G_EXC_ERROR;
1641: ELSE
1642: BEGIN
1643: SELECT request_id INTO l_parent_request_id
1644: FROM wms_device_requests_hist
1645: WHERE request_id = p_request_id
1646: AND task_summary = 'Y';
1647: EXCEPTION
1648: WHEN no_data_found THEN
1708: BEGIN
1709: SELECT TRANSFER_ORG_ID,TRANSFER_SUB_CODE,TRANSFER_LOC_ID
1710: INTO l_xfr_org_id,l_xfr_subinv,l_xfr_locator_id
1711: FROM
1712: wms_device_requests_hist
1713: WHERE REQUEST_ID=p_request_id;
1714: EXCEPTION
1715: WHEN no_data_found THEN
1716: IF (l_debug = 1) THEN
1817: RAISE FND_API.G_EXC_ERROR;
1818: END IF;
1819: end if;
1820:
1821: -- Loop on WMS_DEVICE_REQUESTS per device
1822: IF p_bus_event NOT IN (wms_be_pick_release, wms_be_mo_task_alloc,WMS_BE_WIP_PICK_RELEASE) --Added WMS_BE_WIP_PICK_RELEASE for Bug 13702075
1823: THEN --for pick release and replenish Task Allocation this IS done IN cartonization code
1824:
1825: IF (l_debug = 1) THEN
1851: end if;
1852:
1853: end loop;
1854: END IF;
1855: -- Loop on WMS_DEVICE_REQUESTS per Device
1856: IF (l_debug = 1) THEN
1857: trace('### Submit request per device group ');
1858: END IF;
1859: for l_cur_dev in c_wdr_devgrp loop
1873: IF (l_debug = 1) THEN
1874: trace('update request and request_hist for device '||l_cur_dev.device_id|| ' and parent_request_id='||l_parent_request_id);
1875: END IF;
1876: BEGIN
1877: update wms_device_requests
1878: set relation_id = l_parent_request_id
1879: where device_id = l_cur_dev.device_id;
1880:
1881: update wms_device_requests_hist
1877: update wms_device_requests
1878: set relation_id = l_parent_request_id
1879: where device_id = l_cur_dev.device_id;
1880:
1881: update wms_device_requests_hist
1882: set relation_id = l_parent_request_id
1883: where request_id = l_parent_request_id;
1884: EXCEPTION
1885: WHEN others THEN
1920: l_status_msg := get_msg_stack;--only last message
1921: --IN the stack
1922:
1923: IF l_xml_stat <> 'S' THEN
1924: UPDATE wms_device_requests
1925: SET status_code = l_xml_stat,
1926: status_msg = l_status_msg
1927: WHERE device_id = l_cur_dev.device_id;
1928: ELSE
1925: SET status_code = l_xml_stat,
1926: status_msg = l_status_msg
1927: WHERE device_id = l_cur_dev.device_id;
1928: ELSE
1929: UPDATE wms_device_requests
1930: SET status_code = 'S'
1931: WHERE device_id = l_cur_dev.device_id;
1932: END IF;
1933:
1956: END IF;
1957:
1958:
1959: IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
1960: UPDATE wms_device_requests
1961: SET status_code = l_req_stat,
1962: status_msg = l_req_stat_msg
1963: WHERE device_id = l_cur_dev.device_id;
1964: ELSE
1961: SET status_code = l_req_stat,
1962: status_msg = l_req_stat_msg
1963: WHERE device_id = l_cur_dev.device_id;
1964: ELSE
1965: UPDATE wms_device_requests
1966: SET status_code = 'S'
1967: WHERE device_id = l_cur_dev.device_id;
1968: END IF;
1969: end if;
1990:
1991: IF (l_debug = 1) THEN
1992: trace(' Delete request rows');
1993: END IF;
1994: delete from wms_device_requests;
1995:
1996: EXCEPTION
1997: WHEN FND_API.G_EXC_ERROR THEN
1998: IF (l_debug = 1) THEN
1997: WHEN FND_API.G_EXC_ERROR THEN
1998: IF (l_debug = 1) THEN
1999: trace('Error: G_EXC_ERR : Delete request rows');
2000: END IF;
2001: delete from wms_device_requests;
2002: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2003: FND_MSG_PUB.ADD;
2004: ROLLBACK TO WMS_DEVICE_REQUESTS;
2005: x_return_status := FND_API.G_RET_STS_ERROR;
2000: END IF;
2001: delete from wms_device_requests;
2002: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2003: FND_MSG_PUB.ADD;
2004: ROLLBACK TO WMS_DEVICE_REQUESTS;
2005: x_return_status := FND_API.G_RET_STS_ERROR;
2006: FND_MSG_PUB.Count_And_Get
2007: ( p_count => x_msg_count,
2008: p_data => x_msg_data
2011: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2012: IF (l_debug = 1) THEN
2013: trace('Error: G_EXC_UNEXP : Delete request rows');
2014: END IF;
2015: delete from wms_device_requests;
2016: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2017: FND_MSG_PUB.ADD;
2018: ROLLBACK TO WMS_DEVICE_REQUESTS;
2019: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2014: END IF;
2015: delete from wms_device_requests;
2016: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2017: FND_MSG_PUB.ADD;
2018: ROLLBACK TO WMS_DEVICE_REQUESTS;
2019: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2020: FND_MSG_PUB.Count_And_Get
2021: ( p_count => x_msg_count,
2022: p_data => x_msg_data
2025: WHEN OTHERS THEN
2026: IF (l_debug = 1) THEN
2027: trace('Error: '||substr(sqlerrm, 1, 100));
2028: END IF;
2029: delete from wms_device_requests;
2030: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2031: FND_MSG_PUB.ADD;
2032: ROLLBACK TO WMS_DEVICE_REQUESTS;
2033: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2028: END IF;
2029: delete from wms_device_requests;
2030: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2031: FND_MSG_PUB.ADD;
2032: ROLLBACK TO WMS_DEVICE_REQUESTS;
2033: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2034: FND_MSG_PUB.Count_And_Get
2035: ( p_count => x_msg_count,
2036: p_data => x_msg_data
2097:
2098: Inserting into WDR is kept transparent to the OPM team who will call this
2099: through a wrapper API in a group package
2100:
2101: The request traffic will get logged in the wms_device_requests_hist table
2102: in addition to being captured in wms_carousel_log
2103: */
2104: PROCEDURE DEVICE_REQUEST(
2105: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2122: x_msg_count OUT NOCOPY NUMBER,
2123: x_msg_data OUT NOCOPY VARCHAR2,
2124: p_request_id IN OUT NOCOPY NUMBER,
2125: p_device_id IN NUMBER) IS
2126: cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
2127: cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
2128:
2129: l_seldev number;
2130: l_cur_dev number;
2123: x_msg_data OUT NOCOPY VARCHAR2,
2124: p_request_id IN OUT NOCOPY NUMBER,
2125: p_device_id IN NUMBER) IS
2126: cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
2127: cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
2128:
2129: l_seldev number;
2130: l_cur_dev number;
2131: l_lot_ser_ok varchar2(1);
2177: x_return_status := FND_API.G_RET_STS_SUCCESS;
2178: RETURN;
2179: END IF;
2180:
2181: SAVEPOINT WMS_DEVICE_REQUESTS;
2182: -- Initialize message list if p_init_msg_list is set to TRUE.
2183: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2184: FND_MSG_PUB.initialize;
2185: END IF;
2230: RAISE FND_API.G_EXC_ERROR;
2231: ELSE
2232: BEGIN
2233: SELECT request_id INTO l_parent_request_id
2234: FROM wms_device_requests_hist
2235: WHERE request_id = p_request_id
2236: AND task_summary = 'Y';
2237: EXCEPTION
2238: WHEN no_data_found THEN
2348: RAISE FND_API.G_EXC_ERROR;
2349: END IF;
2350: end if;
2351:
2352: -- Loop on WMS_DEVICE_REQUESTS per device
2353: IF p_bus_event NOT IN (wms_be_pick_release, wms_be_mo_task_alloc)
2354: THEN --for pick release and replenish Task Allocation this IS done IN cartonization code
2355:
2356: IF (l_debug = 1) THEN
2388: end if;
2389:
2390: end loop;
2391: END IF;
2392: -- Loop on WMS_DEVICE_REQUESTS per Device
2393: IF (l_debug = 1) THEN
2394: trace('### Submit request per device group ');
2395: END IF;
2396: for l_cur_dev in c_wdr_devgrp loop
2410: IF (l_debug = 1) THEN
2411: trace('update request and request_hist for device '||l_cur_dev.device_id|| ' and parent_request_id='||l_parent_request_id);
2412: END IF;
2413: BEGIN
2414: update wms_device_requests
2415: set relation_id = l_parent_request_id
2416: where device_id = l_cur_dev.device_id;
2417:
2418: update wms_device_requests_hist
2414: update wms_device_requests
2415: set relation_id = l_parent_request_id
2416: where device_id = l_cur_dev.device_id;
2417:
2418: update wms_device_requests_hist
2419: set relation_id = l_parent_request_id
2420: where request_id = l_parent_request_id;
2421: EXCEPTION
2422: WHEN others THEN
2457: l_status_msg := get_msg_stack;--only last message
2458: --IN the stack
2459:
2460: IF l_xml_stat <> 'S' THEN
2461: UPDATE wms_device_requests
2462: SET status_code = l_xml_stat,
2463: status_msg = l_status_msg
2464: WHERE device_id = l_cur_dev.device_id;
2465: ELSE
2462: SET status_code = l_xml_stat,
2463: status_msg = l_status_msg
2464: WHERE device_id = l_cur_dev.device_id;
2465: ELSE
2466: UPDATE wms_device_requests
2467: SET status_code = 'S'
2468: WHERE device_id = l_cur_dev.device_id;
2469: END IF;
2470:
2493: END IF;
2494:
2495:
2496: IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
2497: UPDATE wms_device_requests
2498: SET status_code = l_req_stat,
2499: status_msg = l_req_stat_msg
2500: WHERE device_id = l_cur_dev.device_id;
2501: ELSE
2498: SET status_code = l_req_stat,
2499: status_msg = l_req_stat_msg
2500: WHERE device_id = l_cur_dev.device_id;
2501: ELSE
2502: UPDATE wms_device_requests
2503: SET status_code = 'S'
2504: WHERE device_id = l_cur_dev.device_id;
2505: END IF;
2506: end if;
2520:
2521: IF (l_debug = 1) THEN
2522: trace(' Delete request rows');
2523: END IF;
2524: delete from wms_device_requests;
2525:
2526: EXCEPTION
2527: WHEN FND_API.G_EXC_ERROR THEN
2528: IF (l_debug = 1) THEN
2527: WHEN FND_API.G_EXC_ERROR THEN
2528: IF (l_debug = 1) THEN
2529: trace('Error: G_EXC_ERR : Delete request rows');
2530: END IF;
2531: delete from wms_device_requests;
2532: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2533: FND_MSG_PUB.ADD;
2534: ROLLBACK TO WMS_DEVICE_REQUESTS;
2535: x_return_status := FND_API.G_RET_STS_ERROR;
2530: END IF;
2531: delete from wms_device_requests;
2532: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2533: FND_MSG_PUB.ADD;
2534: ROLLBACK TO WMS_DEVICE_REQUESTS;
2535: x_return_status := FND_API.G_RET_STS_ERROR;
2536: FND_MSG_PUB.Count_And_Get
2537: ( p_count => x_msg_count,
2538: p_data => x_msg_data
2541: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2542: IF (l_debug = 1) THEN
2543: trace('Error: G_EXC_UNEXP : Delete request rows');
2544: END IF;
2545: delete from wms_device_requests;
2546: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2547: FND_MSG_PUB.ADD;
2548: ROLLBACK TO WMS_DEVICE_REQUESTS;
2549: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2544: END IF;
2545: delete from wms_device_requests;
2546: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2547: FND_MSG_PUB.ADD;
2548: ROLLBACK TO WMS_DEVICE_REQUESTS;
2549: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2550: FND_MSG_PUB.Count_And_Get
2551: ( p_count => x_msg_count,
2552: p_data => x_msg_data
2555: WHEN OTHERS THEN
2556: IF (l_debug = 1) THEN
2557: trace('Error: '||substr(sqlerrm, 1, 100));
2558: END IF;
2559: delete from wms_device_requests;
2560: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2561: FND_MSG_PUB.ADD;
2562: ROLLBACK TO WMS_DEVICE_REQUESTS;
2563: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2558: END IF;
2559: delete from wms_device_requests;
2560: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2561: FND_MSG_PUB.ADD;
2562: ROLLBACK TO WMS_DEVICE_REQUESTS;
2563: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2564: FND_MSG_PUB.Count_And_Get
2565: ( p_count => x_msg_count,
2566: p_data => x_msg_data
2584: P_business_event_id IN NUMBER
2585: )IS
2586:
2587: CURSOR x_cur IS SELECT distinct device_id dev_id
2588: FROM wms_device_requests_hist
2589: WHERE Nvl(device_id, -1) = Nvl( p_device_id, -1)
2590: AND request_id = p_request_id
2591: AND status_code ='P'
2592: AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1))
2620: IF (l_debug = 1) THEN
2621: trace('deleting all current error records from WDRH');
2622: END IF;
2623: --delete all current error records from WDRH
2624: DELETE FROM wms_device_requests_hist
2625: WHERE request_id = p_request_id
2626: AND status_code ='P'
2627: AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1))
2628: AND Nvl(task_id,-1) = Nvl(p_task_trx_id,nvl(task_id, -1))
2655: ELSE--other business event
2656: BEGIN
2657: SELECT nvl(WCS_ENABLED,'N') into l_wcs_enabled FROM MTL_PARAMETERS
2658: WHERE ORGANIZATION_ID = (SELECT ORGANIZATION_ID
2659: FROM wms_device_requests_hist
2660: WHERE request_id = p_request_id
2661: AND status_code ='P'
2662: AND Nvl(business_event_id,-1) = Nvl(p_business_event_id,-1)
2663: AND ROWNUM < 2);
2689: x_status_msg => l_stat_msg );
2690: END IF;
2691:
2692: IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
2693: UPDATE wms_device_requests_hist
2694: SET status_code = l_req_stat,
2695: status_msg = l_stat_msg
2696: WHERE device_id = l_rec.dev_id
2697: AND request_id = p_request_id;
2695: status_msg = l_stat_msg
2696: WHERE device_id = l_rec.dev_id
2697: AND request_id = p_request_id;
2698: ELSE
2699: UPDATE wms_device_requests_hist
2700: SET status_code = 'S'
2701: WHERE device_id = l_rec.dev_id
2702: AND request_id = p_request_id;
2703: END IF;
2707: END IF;
2708:
2709: --finally remove the rows from the request table.
2710:
2711: DELETE FROM wms_device_requests
2712: WHERE request_id = p_request_id
2713: AND Nvl(business_event_id,-1) = Nvl(p_business_event_id,-1)
2714: AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1));
2715:
2716: EXCEPTION
2717: WHEN FND_API.G_EXC_ERROR THEN
2718: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_RESUBMIT_FAIL');
2719: FND_MSG_PUB.ADD;
2720: ROLLBACK TO WMS_DEVICE_REQUESTS;
2721: FND_MSG_PUB.Count_And_Get
2722: ( p_count => l_dev_req_type,
2723: p_data => l_req_stat
2724: );
2722: ( p_count => l_dev_req_type,
2723: p_data => l_req_stat
2724: );
2725: --this is set to P in the form while making call to concurrent req
2726: UPDATE wms_device_requests_hist
2727: SET status_code = 'E',resubmit_date = null
2728: WHERE request_id = p_request_id
2729: AND BUSINESS_EVENT_ID =p_business_event_id
2730: AND status_code = 'P';
2733:
2734: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2735: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_RESUBMIT_FAIL');
2736: FND_MSG_PUB.ADD;
2737: ROLLBACK TO WMS_DEVICE_REQUESTS;
2738: FND_MSG_PUB.Count_And_Get
2739: ( p_count => l_dev_req_type,
2740: p_data => l_req_stat
2741: );
2740: p_data => l_req_stat
2741: );
2742:
2743: --this is set to P in the form while making call to concurrent req
2744: UPDATE wms_device_requests_hist
2745: SET status_code = 'E',resubmit_date = null
2746: WHERE request_id = p_request_id
2747: AND BUSINESS_EVENT_ID =p_business_event_id
2748: AND status_code = 'P';
2752: WHEN OTHERS THEN
2753: trace('Resubmit_req SQL error :'||substr(sqlerrm, 1, 240));
2754: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_RESUBMIT_FAIL');
2755: FND_MSG_PUB.ADD;
2756: ROLLBACK TO WMS_DEVICE_REQUESTS;
2757: FND_MSG_PUB.Count_And_Get
2758: ( p_count => l_dev_req_type,
2759: p_data => l_req_stat
2760: );
2758: ( p_count => l_dev_req_type,
2759: p_data => l_req_stat
2760: );
2761: --this is set to P in the form while making call to concurrent req
2762: UPDATE wms_device_requests_hist
2763: SET status_code = 'E',resubmit_date = null
2764: WHERE request_id = p_request_id
2765: AND BUSINESS_EVENT_ID =p_business_event_id
2766: AND status_code = 'P';
2826:
2827: END IF ;
2828:
2829: END IF ;
2830: SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
2831: wms_pkRel_dev_req_id := l_request_id;
2832:
2833: --global parameter wms_call_device_request is used in WMSCRTNB.pls
2834: IF p_bus_event_id = wms_be_pick_release then
2871: l_ret := fnd_concurrent.set_completion_status('ERROR', 'WMS_MISS_REQ_PARAMETER');
2872: x_retcode := 2;
2873: x_errbuf := 'ERROR';
2874: ELSE
2875: delete from wms_device_requests_hist
2876: where creation_date < p_purge_date and organization_id = p_orgid ;
2877: delete from wms_lpn_histories
2878: where creation_date < p_purge_date and organization_id = p_orgid ;
2879: delete from wms_dispatched_tasks_history