[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,
147:
148: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
149: BEGIN
150:
151: SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
152: x_request_id := l_request_id;
153:
154: SELECT organization_id,
155: subinventory_code,
199: IF (l_debug = 1) THEN
200: trace(' sub,loc,xfr_sub,xfr_loc:'||l_subinv||','||l_locator_id||','||l_xfr_subinv||','||l_xfr_locator_id);
201: END IF;
202:
203: insert INTO wms_device_requests (request_id,
204: task_id,
205: task_summary,
206: business_event_id,
207: organization_id,
291:
292: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
293: BEGIN
294:
295: SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
296: x_request_id := l_request_id;
297:
298: IF (p_bus_event=WMS_BE_PICK_DROP) OR
299: ((p_bus_event=WMS_BE_PUTAWAY_DROP) AND (p_xfr_subinv IS NOT NULL)) THEN
315: IF (l_debug = 1) THEN
316: trace(' sub,loc,xfr_sub,xfr_loc:'||l_subinv||','||l_locator_id||','||l_xfr_subinv||','||l_xfr_locator_id);
317: END IF;
318:
319: insert INTO wms_device_requests (request_id,
320: task_id,
321: task_summary,
322: business_event_id,
323: organization_id,
406:
407: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
408: BEGIN
409:
410: SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
411: x_request_id := l_request_id;
412:
413: IF (p_bus_event=WMS_BE_PICK_DROP) OR
414: ((p_bus_event=WMS_BE_PUTAWAY_DROP) AND (p_xfr_subinv IS NOT NULL)) THEN
430: IF (l_debug = 1) THEN
431: 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);
432: END IF;
433:
434: insert INTO wms_device_requests (request_id,
435: task_id,
436: task_summary,
437: business_event_id,
438: organization_id,
522: ELSE
523: l_qty := wdrrec.transaction_quantity;
524: END IF;
525: l_count := l_count + 1;
526: INSERT INTO wms_device_requests (request_id,
527: task_id,
528: relation_id,
529: sequence_id,
530: task_summary,
602: ---------------------------------------------------------
603: -- select_Device
604: --
605: ---------------------------------------------------------
606: FUNCTION select_Device(wdrrec WMS_DEVICE_REQUESTS%ROWTYPE,
607: p_autoenable VARCHAR2,
608: p_parent_request_id NUMBER
609: ) return NUMBER is
610:
633: dev_id := 0;
634: BEGIN
635: SELECT device_id, task_id
636: INTO dev_id, par_task_id
637: FROM wms_device_requests_hist
638: WHERE request_id = p_parent_request_id
639: AND ROWNUM < 2;
640: EXCEPTION
641: WHEN OTHERS THEN
807: l_notification_flag := 'Y';
808: END IF;
809:
810: IF l_notification_flag = 'Y' THEN
811: UPDATE wms_device_requests
812: SET device_id = dev_id,
813: task_id = nvl(par_task_id, task_id)
814: WHERE request_id = wdrrec.request_id
815: AND Nvl(task_type_id,0) = Nvl(wdrrec.task_type_id,Nvl(task_type_id,0))
921: wdr.uom UOM, wdr.lot_number LOT, wdr.lot_qty LOTQTY,
922: wdr.serial_number serial,
923: wdr.status_msg STATUSMSG, wdr.last_update_date timestamp,
924: wdr.business_event_id bus_event_id
925: FROM wms_device_requests wdr, mfg_lookups ml1, mfg_lookups ml2,
926: wms_devices_vl wd, mtl_parameters mp1, mtl_item_locations_kfv milk1,
927: mtl_parameters mp2, mtl_item_locations_kfv milk2, wms_license_plate_numbers wlpn,
928: mtl_system_items_kfv msik
929: WHERE ml1.lookup_type(+)= 'WMS_TASK_TYPES' AND ml1.lookup_code(+) = wdr.task_type_id
958: wdr.uom ||l_seperator|| wdr.lot_number ||l_seperator||
959: wdr.lot_qty||l_seperator||
960: wdr.serial_number||l_seperator||wdr.status_msg||l_seperator||wdr.last_update_date
961: CSV_LINE
962: FROM wms_device_requests wdr, mfg_lookups ml1, mfg_lookups ml2,
963: wms_devices_vl wd, mtl_parameters mp1, mtl_item_locations_kfv milk1,
964: mtl_parameters mp2, mtl_item_locations_kfv milk2, wms_license_plate_numbers wlpn,
965: mtl_system_items_kfv msik
966: WHERE ml1.lookup_type(+) = 'WMS_TASK_TYPES' AND ml1.lookup_code(+) = wdr.task_type_id
1008: FROM wms_devices
1009: WHERE device_id = p_device_id;
1010:
1011: SELECT request_id INTO l_seq_id
1012: FROM wms_device_requests
1013: WHERE device_id = p_device_id
1014: AND ROWNUM<2;
1015:
1016: EXCEPTION
1041: -- Change to just check existence
1042: SELECT 1 INTO l_detail_available FROM dual
1043: WHERE exists(
1044: SELECT 1
1045: FROM wms_device_requests
1046: WHERE device_id = p_device_id
1047: AND nvl(task_summary,'Y') = 'N');
1048:
1049: IF (l_debug = 1) THEN
1190: -- update outfile_name
1191: IF (l_debug = 1) THEN
1192: trace('update outfile_name ' || l_file_name || p_device_id || l_task_sum);
1193: END IF;
1194: UPDATE wms_device_requests
1195: SET outfile_name = l_file_name
1196: WHERE device_id = p_device_id
1197: AND nvl(task_summary, 'Y') = decode(l_lot_serial_enabled,'N','Y','Y',nvl(task_summary,'Y'),'Y');
1198:
1242: ---------------------------------------------------------
1243: PROCEDURE populate_History (x_device_records_exist OUT NOCOPY VARCHAR2) IS
1244:
1245: l_counter NUMBER := 0;
1246: CURSOR cur_dev IS SELECT * FROM wms_device_requests where device_id is
1247: not null;
1248:
1249: l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1250: BEGIN
1255: trace('outfile_name='||l_rec.outfile_name||'request_id:'||l_rec.request_id||'task_id:'||l_rec.task_id);
1256: END IF;
1257: */
1258: l_counter := l_counter +1;
1259: INSERT INTO wms_device_requests_hist (request_id,
1260: task_id,
1261: relation_id,
1262: sequence_id,
1263: task_summary,
1349:
1350: END;
1351:
1352: ----------------------------------------------------------
1353: ---- Move rows back to the wms_device_requests temp table
1354: ----------------------------------------------------------
1355:
1356: PROCEDURE move_resubmit_rows(p_request_id number, p_bus_event_id number)IS
1357:
1362: IF (l_debug = 1) THEN
1363: trace('Move from Hist table to temp table');
1364: END IF;
1365:
1366: insert into wms_device_requests (
1367: BUSINESS_EVENT_ID
1368: ,DEVICE_ID
1369: ,DEVICE_STATUS
1370: ,INVENTORY_ITEM_ID
1426: ,TRANSFER_ORG_ID
1427: ,TRANSFER_SUB_CODE
1428: ,UOM
1429: ,XFER_LPN_ID
1430: from wms_device_requests_hist
1431: WHERE request_id = p_request_id
1432: AND status_code ='P'
1433: AND Nvl(business_event_id,-1) = Nvl(p_bus_event_id,-1);
1434:
1493: x_return_status OUT NOCOPY VARCHAR2,
1494: x_msg_count OUT NOCOPY NUMBER,
1495: x_msg_data OUT NOCOPY VARCHAR2,
1496: p_request_id IN OUT NOCOPY NUMBER) IS
1497: cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
1498: cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
1499:
1500: l_seldev number;
1501: l_cur_dev number;
1494: x_msg_count OUT NOCOPY NUMBER,
1495: x_msg_data OUT NOCOPY VARCHAR2,
1496: p_request_id IN OUT NOCOPY NUMBER) IS
1497: cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
1498: cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
1499:
1500: l_seldev number;
1501: l_cur_dev number;
1502: l_lot_ser_ok varchar2(1);
1548: x_return_status := FND_API.G_RET_STS_SUCCESS;
1549: RETURN;
1550: END IF;
1551:
1552: SAVEPOINT WMS_DEVICE_REQUESTS;
1553: -- Initialize message list if p_init_msg_list is set to TRUE.
1554: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1555: FND_MSG_PUB.initialize;
1556: END IF;
1601: RAISE FND_API.G_EXC_ERROR;
1602: ELSE
1603: BEGIN
1604: SELECT request_id INTO l_parent_request_id
1605: FROM wms_device_requests_hist
1606: WHERE request_id = p_request_id
1607: AND task_summary = 'Y';
1608: EXCEPTION
1609: WHEN no_data_found THEN
1735: RAISE FND_API.G_EXC_ERROR;
1736: END IF;
1737: end if;
1738:
1739: -- Loop on WMS_DEVICE_REQUESTS per device
1740: IF p_bus_event NOT IN (wms_be_pick_release, wms_be_mo_task_alloc)
1741: THEN --for pick release and replenish Task Allocation this IS done IN cartonization code
1742:
1743: IF (l_debug = 1) THEN
1769: end if;
1770:
1771: end loop;
1772: END IF;
1773: -- Loop on WMS_DEVICE_REQUESTS per Device
1774: IF (l_debug = 1) THEN
1775: trace('### Submit request per device group ');
1776: END IF;
1777: for l_cur_dev in c_wdr_devgrp loop
1791: IF (l_debug = 1) THEN
1792: trace('update request and request_hist for device '||l_cur_dev.device_id|| ' and parent_request_id='||l_parent_request_id);
1793: END IF;
1794: BEGIN
1795: update wms_device_requests
1796: set relation_id = l_parent_request_id
1797: where device_id = l_cur_dev.device_id;
1798:
1799: update wms_device_requests_hist
1795: update wms_device_requests
1796: set relation_id = l_parent_request_id
1797: where device_id = l_cur_dev.device_id;
1798:
1799: update wms_device_requests_hist
1800: set relation_id = l_parent_request_id
1801: where request_id = l_parent_request_id;
1802: EXCEPTION
1803: WHEN others THEN
1838: l_status_msg := get_msg_stack;--only last message
1839: --IN the stack
1840:
1841: IF l_xml_stat <> 'S' THEN
1842: UPDATE wms_device_requests
1843: SET status_code = l_xml_stat,
1844: status_msg = l_status_msg
1845: WHERE device_id = l_cur_dev.device_id;
1846: ELSE
1843: SET status_code = l_xml_stat,
1844: status_msg = l_status_msg
1845: WHERE device_id = l_cur_dev.device_id;
1846: ELSE
1847: UPDATE wms_device_requests
1848: SET status_code = 'S'
1849: WHERE device_id = l_cur_dev.device_id;
1850: END IF;
1851:
1874: END IF;
1875:
1876:
1877: IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
1878: UPDATE wms_device_requests
1879: SET status_code = l_req_stat,
1880: status_msg = l_req_stat_msg
1881: WHERE device_id = l_cur_dev.device_id;
1882: ELSE
1879: SET status_code = l_req_stat,
1880: status_msg = l_req_stat_msg
1881: WHERE device_id = l_cur_dev.device_id;
1882: ELSE
1883: UPDATE wms_device_requests
1884: SET status_code = 'S'
1885: WHERE device_id = l_cur_dev.device_id;
1886: END IF;
1887: end if;
1908:
1909: IF (l_debug = 1) THEN
1910: trace(' Delete request rows');
1911: END IF;
1912: delete from wms_device_requests;
1913:
1914: EXCEPTION
1915: WHEN FND_API.G_EXC_ERROR THEN
1916: IF (l_debug = 1) THEN
1915: WHEN FND_API.G_EXC_ERROR THEN
1916: IF (l_debug = 1) THEN
1917: trace('Error: G_EXC_ERR : Delete request rows');
1918: END IF;
1919: delete from wms_device_requests;
1920: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
1921: FND_MSG_PUB.ADD;
1922: ROLLBACK TO WMS_DEVICE_REQUESTS;
1923: x_return_status := FND_API.G_RET_STS_ERROR;
1918: END IF;
1919: delete from wms_device_requests;
1920: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
1921: FND_MSG_PUB.ADD;
1922: ROLLBACK TO WMS_DEVICE_REQUESTS;
1923: x_return_status := FND_API.G_RET_STS_ERROR;
1924: FND_MSG_PUB.Count_And_Get
1925: ( p_count => x_msg_count,
1926: p_data => x_msg_data
1929: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1930: IF (l_debug = 1) THEN
1931: trace('Error: G_EXC_UNEXP : Delete request rows');
1932: END IF;
1933: delete from wms_device_requests;
1934: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
1935: FND_MSG_PUB.ADD;
1936: ROLLBACK TO WMS_DEVICE_REQUESTS;
1937: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1932: END IF;
1933: delete from wms_device_requests;
1934: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
1935: FND_MSG_PUB.ADD;
1936: ROLLBACK TO WMS_DEVICE_REQUESTS;
1937: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1938: FND_MSG_PUB.Count_And_Get
1939: ( p_count => x_msg_count,
1940: p_data => x_msg_data
1943: WHEN OTHERS THEN
1944: IF (l_debug = 1) THEN
1945: trace('Error: '||substr(sqlerrm, 1, 100));
1946: END IF;
1947: delete from wms_device_requests;
1948: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
1949: FND_MSG_PUB.ADD;
1950: ROLLBACK TO WMS_DEVICE_REQUESTS;
1951: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1946: END IF;
1947: delete from wms_device_requests;
1948: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
1949: FND_MSG_PUB.ADD;
1950: ROLLBACK TO WMS_DEVICE_REQUESTS;
1951: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1952: FND_MSG_PUB.Count_And_Get
1953: ( p_count => x_msg_count,
1954: p_data => x_msg_data
2013:
2014: Inserting into WDR is kept transparent to the OPM team who will call this
2015: through a wrapper API in a group package
2016:
2017: The request traffic will get logged in the wms_device_requests_hist table
2018: in addition to being captured in wms_carousel_log
2019: */
2020: PROCEDURE DEVICE_REQUEST(
2021: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2038: x_msg_count OUT NOCOPY NUMBER,
2039: x_msg_data OUT NOCOPY VARCHAR2,
2040: p_request_id IN OUT NOCOPY NUMBER,
2041: p_device_id IN NUMBER) IS
2042: cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
2043: cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
2044:
2045: l_seldev number;
2046: l_cur_dev number;
2039: x_msg_data OUT NOCOPY VARCHAR2,
2040: p_request_id IN OUT NOCOPY NUMBER,
2041: p_device_id IN NUMBER) IS
2042: cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
2043: cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
2044:
2045: l_seldev number;
2046: l_cur_dev number;
2047: l_lot_ser_ok varchar2(1);
2093: x_return_status := FND_API.G_RET_STS_SUCCESS;
2094: RETURN;
2095: END IF;
2096:
2097: SAVEPOINT WMS_DEVICE_REQUESTS;
2098: -- Initialize message list if p_init_msg_list is set to TRUE.
2099: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2100: FND_MSG_PUB.initialize;
2101: END IF;
2146: RAISE FND_API.G_EXC_ERROR;
2147: ELSE
2148: BEGIN
2149: SELECT request_id INTO l_parent_request_id
2150: FROM wms_device_requests_hist
2151: WHERE request_id = p_request_id
2152: AND task_summary = 'Y';
2153: EXCEPTION
2154: WHEN no_data_found THEN
2264: RAISE FND_API.G_EXC_ERROR;
2265: END IF;
2266: end if;
2267:
2268: -- Loop on WMS_DEVICE_REQUESTS per device
2269: IF p_bus_event NOT IN (wms_be_pick_release, wms_be_mo_task_alloc)
2270: THEN --for pick release and replenish Task Allocation this IS done IN cartonization code
2271:
2272: IF (l_debug = 1) THEN
2304: end if;
2305:
2306: end loop;
2307: END IF;
2308: -- Loop on WMS_DEVICE_REQUESTS per Device
2309: IF (l_debug = 1) THEN
2310: trace('### Submit request per device group ');
2311: END IF;
2312: for l_cur_dev in c_wdr_devgrp loop
2326: IF (l_debug = 1) THEN
2327: trace('update request and request_hist for device '||l_cur_dev.device_id|| ' and parent_request_id='||l_parent_request_id);
2328: END IF;
2329: BEGIN
2330: update wms_device_requests
2331: set relation_id = l_parent_request_id
2332: where device_id = l_cur_dev.device_id;
2333:
2334: update wms_device_requests_hist
2330: update wms_device_requests
2331: set relation_id = l_parent_request_id
2332: where device_id = l_cur_dev.device_id;
2333:
2334: update wms_device_requests_hist
2335: set relation_id = l_parent_request_id
2336: where request_id = l_parent_request_id;
2337: EXCEPTION
2338: WHEN others THEN
2373: l_status_msg := get_msg_stack;--only last message
2374: --IN the stack
2375:
2376: IF l_xml_stat <> 'S' THEN
2377: UPDATE wms_device_requests
2378: SET status_code = l_xml_stat,
2379: status_msg = l_status_msg
2380: WHERE device_id = l_cur_dev.device_id;
2381: ELSE
2378: SET status_code = l_xml_stat,
2379: status_msg = l_status_msg
2380: WHERE device_id = l_cur_dev.device_id;
2381: ELSE
2382: UPDATE wms_device_requests
2383: SET status_code = 'S'
2384: WHERE device_id = l_cur_dev.device_id;
2385: END IF;
2386:
2409: END IF;
2410:
2411:
2412: IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
2413: UPDATE wms_device_requests
2414: SET status_code = l_req_stat,
2415: status_msg = l_req_stat_msg
2416: WHERE device_id = l_cur_dev.device_id;
2417: ELSE
2414: SET status_code = l_req_stat,
2415: status_msg = l_req_stat_msg
2416: WHERE device_id = l_cur_dev.device_id;
2417: ELSE
2418: UPDATE wms_device_requests
2419: SET status_code = 'S'
2420: WHERE device_id = l_cur_dev.device_id;
2421: END IF;
2422: end if;
2436:
2437: IF (l_debug = 1) THEN
2438: trace(' Delete request rows');
2439: END IF;
2440: delete from wms_device_requests;
2441:
2442: EXCEPTION
2443: WHEN FND_API.G_EXC_ERROR THEN
2444: IF (l_debug = 1) THEN
2443: WHEN FND_API.G_EXC_ERROR THEN
2444: IF (l_debug = 1) THEN
2445: trace('Error: G_EXC_ERR : Delete request rows');
2446: END IF;
2447: delete from wms_device_requests;
2448: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2449: FND_MSG_PUB.ADD;
2450: ROLLBACK TO WMS_DEVICE_REQUESTS;
2451: x_return_status := FND_API.G_RET_STS_ERROR;
2446: END IF;
2447: delete from wms_device_requests;
2448: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2449: FND_MSG_PUB.ADD;
2450: ROLLBACK TO WMS_DEVICE_REQUESTS;
2451: x_return_status := FND_API.G_RET_STS_ERROR;
2452: FND_MSG_PUB.Count_And_Get
2453: ( p_count => x_msg_count,
2454: p_data => x_msg_data
2457: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2458: IF (l_debug = 1) THEN
2459: trace('Error: G_EXC_UNEXP : Delete request rows');
2460: END IF;
2461: delete from wms_device_requests;
2462: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2463: FND_MSG_PUB.ADD;
2464: ROLLBACK TO WMS_DEVICE_REQUESTS;
2465: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2460: END IF;
2461: delete from wms_device_requests;
2462: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2463: FND_MSG_PUB.ADD;
2464: ROLLBACK TO WMS_DEVICE_REQUESTS;
2465: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2466: FND_MSG_PUB.Count_And_Get
2467: ( p_count => x_msg_count,
2468: p_data => x_msg_data
2471: WHEN OTHERS THEN
2472: IF (l_debug = 1) THEN
2473: trace('Error: '||substr(sqlerrm, 1, 100));
2474: END IF;
2475: delete from wms_device_requests;
2476: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2477: FND_MSG_PUB.ADD;
2478: ROLLBACK TO WMS_DEVICE_REQUESTS;
2479: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2474: END IF;
2475: delete from wms_device_requests;
2476: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2477: FND_MSG_PUB.ADD;
2478: ROLLBACK TO WMS_DEVICE_REQUESTS;
2479: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2480: FND_MSG_PUB.Count_And_Get
2481: ( p_count => x_msg_count,
2482: p_data => x_msg_data
2500: P_business_event_id IN NUMBER
2501: )IS
2502:
2503: CURSOR x_cur IS SELECT distinct device_id dev_id
2504: FROM wms_device_requests_hist
2505: WHERE Nvl(device_id, -1) = Nvl( p_device_id, -1)
2506: AND request_id = p_request_id
2507: AND status_code ='P'
2508: AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1))
2536: IF (l_debug = 1) THEN
2537: trace('deleting all current error records from WDRH');
2538: END IF;
2539: --delete all current error records from WDRH
2540: DELETE FROM wms_device_requests_hist
2541: WHERE request_id = p_request_id
2542: AND status_code ='P'
2543: AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1))
2544: AND Nvl(task_id,-1) = Nvl(p_task_trx_id,nvl(task_id, -1))
2571: ELSE--other business event
2572: BEGIN
2573: SELECT nvl(WCS_ENABLED,'N') into l_wcs_enabled FROM MTL_PARAMETERS
2574: WHERE ORGANIZATION_ID = (SELECT ORGANIZATION_ID
2575: FROM wms_device_requests_hist
2576: WHERE request_id = p_request_id
2577: AND status_code ='P'
2578: AND Nvl(business_event_id,-1) = Nvl(p_business_event_id,-1)
2579: AND ROWNUM < 2);
2605: x_status_msg => l_stat_msg );
2606: END IF;
2607:
2608: IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
2609: UPDATE wms_device_requests_hist
2610: SET status_code = l_req_stat,
2611: status_msg = l_stat_msg
2612: WHERE device_id = l_rec.dev_id
2613: AND request_id = p_request_id;
2611: status_msg = l_stat_msg
2612: WHERE device_id = l_rec.dev_id
2613: AND request_id = p_request_id;
2614: ELSE
2615: UPDATE wms_device_requests_hist
2616: SET status_code = 'S'
2617: WHERE device_id = l_rec.dev_id
2618: AND request_id = p_request_id;
2619: END IF;
2623: END IF;
2624:
2625: --finally remove the rows from the request table.
2626:
2627: DELETE FROM wms_device_requests
2628: WHERE request_id = p_request_id
2629: AND Nvl(business_event_id,-1) = Nvl(p_business_event_id,-1)
2630: AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1));
2631:
2632: EXCEPTION
2633: WHEN FND_API.G_EXC_ERROR THEN
2634: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_RESUBMIT_FAIL');
2635: FND_MSG_PUB.ADD;
2636: ROLLBACK TO WMS_DEVICE_REQUESTS;
2637: FND_MSG_PUB.Count_And_Get
2638: ( p_count => l_dev_req_type,
2639: p_data => l_req_stat
2640: );
2638: ( p_count => l_dev_req_type,
2639: p_data => l_req_stat
2640: );
2641: --this is set to P in the form while making call to concurrent req
2642: UPDATE wms_device_requests_hist
2643: SET status_code = 'E',resubmit_date = null
2644: WHERE request_id = p_request_id
2645: AND BUSINESS_EVENT_ID =p_business_event_id
2646: AND status_code = 'P';
2649:
2650: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2651: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_RESUBMIT_FAIL');
2652: FND_MSG_PUB.ADD;
2653: ROLLBACK TO WMS_DEVICE_REQUESTS;
2654: FND_MSG_PUB.Count_And_Get
2655: ( p_count => l_dev_req_type,
2656: p_data => l_req_stat
2657: );
2656: p_data => l_req_stat
2657: );
2658:
2659: --this is set to P in the form while making call to concurrent req
2660: UPDATE wms_device_requests_hist
2661: SET status_code = 'E',resubmit_date = null
2662: WHERE request_id = p_request_id
2663: AND BUSINESS_EVENT_ID =p_business_event_id
2664: AND status_code = 'P';
2668: WHEN OTHERS THEN
2669: trace('Resubmit_req SQL error :'||substr(sqlerrm, 1, 240));
2670: FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_RESUBMIT_FAIL');
2671: FND_MSG_PUB.ADD;
2672: ROLLBACK TO WMS_DEVICE_REQUESTS;
2673: FND_MSG_PUB.Count_And_Get
2674: ( p_count => l_dev_req_type,
2675: p_data => l_req_stat
2676: );
2674: ( p_count => l_dev_req_type,
2675: p_data => l_req_stat
2676: );
2677: --this is set to P in the form while making call to concurrent req
2678: UPDATE wms_device_requests_hist
2679: SET status_code = 'E',resubmit_date = null
2680: WHERE request_id = p_request_id
2681: AND BUSINESS_EVENT_ID =p_business_event_id
2682: AND status_code = 'P';
2742:
2743: END IF ;
2744:
2745: END IF ;
2746: SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
2747: wms_pkRel_dev_req_id := l_request_id;
2748:
2749: --global parameter wms_call_device_request is used in WMSCRTNB.pls
2750: IF p_bus_event_id = wms_be_pick_release then
2784: l_ret := fnd_concurrent.set_completion_status('ERROR', 'WMS_MISS_REQ_PARAMETER');
2785: x_retcode := 2;
2786: x_errbuf := 'ERROR';
2787: ELSE
2788: delete from wms_device_requests_hist
2789: where creation_date < p_purge_date and organization_id = p_orgid ;
2790: delete from wms_lpn_histories
2791: where creation_date < p_purge_date and organization_id = p_orgid ;
2792: delete from wms_dispatched_tasks_history