[Home] [Help]
105: x_return_status := 'E';
106: IF (l_debug = 1) THEN
107: trace('Error retrieve in ship confirm details, no data found');
108: END IF;
109: RAISE fnd_api.g_exc_unexpected_error;
110: WHEN others THEN
111: x_return_status := 'E';
112: IF (l_debug = 1) THEN
113: trace('Other error in retrieve ship confirm details');
243: x_return_status := 'E';
244: IF (l_debug = 1) THEN
245: trace('Error retrieve in business event details, no data found');
246: END IF;
247: RAISE fnd_api.g_exc_unexpected_error;
248: WHEN others THEN
249: x_return_status := 'E';
250: IF (l_debug = 1) THEN
251: trace('Other error in retrieve business event details');
358: x_return_status := 'E';
359: IF (l_debug = 1) THEN
360: trace('Error in retrieve business event details, no data found');
361: END IF;
362: RAISE fnd_api.g_exc_unexpected_error;
363: WHEN others THEN
364: x_return_status := 'E';
365: IF (l_debug = 1) THEN
366: trace('Other error in retrieve business event details');
475: x_return_status := 'E';
476: IF (l_debug = 1) THEN
477: trace('Error in retrieve business event details, no data found');
478: END IF;
479: RAISE fnd_api.g_exc_unexpected_error;
480: WHEN others THEN
481: x_return_status := 'E';
482: IF (l_debug = 1) THEN
483: trace('Other error in retrieve business event details');
884: WHEN utl_file.invalid_mode THEN
885: IF (l_debug = 1) THEN
886: trace('Invalid mode in WriteToFile: w');
887: END IF;
888: WHEN fnd_api.g_exc_error THEN
889: IF (l_debug = 1) THEN
890: trace(' Expected Error in WriteXmlFile');
891: END IF;
892: WHEN fnd_api.g_exc_unexpected_error THEN
888: WHEN fnd_api.g_exc_error THEN
889: IF (l_debug = 1) THEN
890: trace(' Expected Error in WriteXmlFile');
891: END IF;
892: WHEN fnd_api.g_exc_unexpected_error THEN
893: IF (l_debug = 1) THEN
894: trace(' Unexpected Error in WriteXmlFile');
895: END IF;
896: WHEN OTHERS THEN
1017: WHEN no_data_found THEN
1018: IF (l_debug = 1) THEN
1019: trace('Error in getting device property, no data found');
1020: END IF;
1021: RAISE fnd_api.g_exc_unexpected_error;
1022: WHEN others THEN
1023: IF (l_debug = 1) THEN
1024: trace('Other error in getting device property');
1025: trace('SQL error :'||substr(sqlerrm, 1, 240));
1183: ELSE
1184: IF (l_debug = 1) THEN
1185: trace('Invalid iotype value passed to generate_xml_csv:'||p_iotype);
1186: END IF;
1187: RAISE fnd_api.g_exc_unexpected_error;
1188: END IF;
1189:
1190: -- update outfile_name
1191: IF (l_debug = 1) THEN
1215: FND_MESSAGE.SET_TOKEN('ERROR_REASON', 'Invalid Mode');
1216: FND_MSG_PUB.ADD;
1217: retval := -1;
1218: return retval;
1219: WHEN FND_API.G_EXC_ERROR THEN
1220: IF (l_debug = 1) THEN
1221: trace(' Expected error in generate XML ');
1222: END IF;
1223: RAISE FND_API.G_EXC_ERROR;
1219: WHEN FND_API.G_EXC_ERROR THEN
1220: IF (l_debug = 1) THEN
1221: trace(' Expected error in generate XML ');
1222: END IF;
1223: RAISE FND_API.G_EXC_ERROR;
1224: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1225: IF (l_debug = 1) THEN
1226: trace(' UnExpected error in generate XML ');
1227: END IF;
1220: IF (l_debug = 1) THEN
1221: trace(' Expected error in generate XML ');
1222: END IF;
1223: RAISE FND_API.G_EXC_ERROR;
1224: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1225: IF (l_debug = 1) THEN
1226: trace(' UnExpected error in generate XML ');
1227: END IF;
1228: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1224: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1225: IF (l_debug = 1) THEN
1226: trace(' UnExpected error in generate XML ');
1227: END IF;
1228: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1229: WHEN OTHERS THEN
1230: IF (l_debug = 1) THEN
1231: trace(' Other error in generate XML ');
1232: trace('SQL error :'||substr(sqlerrm, 1, 240));
1230: IF (l_debug = 1) THEN
1231: trace(' Other error in generate XML ');
1232: trace('SQL error :'||substr(sqlerrm, 1, 240));
1233: END IF;
1234: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1235: END generate_xml_csv;
1236:
1237:
1238:
1473: -- DEVICE_REQUEST
1474: --
1475: ---------------------------------------------------------
1476: PROCEDURE DEVICE_REQUEST(
1477: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1478: p_bus_event IN NUMBER,
1479: p_call_ctx IN VARCHAR2 ,
1480: p_task_trx_id IN NUMBER := NULL,
1481: p_org_id IN NUMBER := NULL,
1544: l_setup_row_cnt:=0;
1545: END;
1546:
1547: IF (l_setup_row_cnt = 0 AND p_bus_event < 50) THEN
1548: x_return_status := FND_API.G_RET_STS_SUCCESS;
1549: RETURN;
1550: END IF;
1551:
1552: SAVEPOINT WMS_DEVICE_REQUESTS;
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;
1557: -- Initialize API return status to success
1558: x_return_status := FND_API.G_RET_STS_SUCCESS;
1554: IF FND_API.to_Boolean( p_init_msg_list ) THEN
1555: FND_MSG_PUB.initialize;
1556: END IF;
1557: -- Initialize API return status to success
1558: x_return_status := FND_API.G_RET_STS_SUCCESS;
1559:
1560: IF (l_debug = 1) THEN
1561: trace('******* Device Request *********');
1562: END IF;
1567: trace('Invlid Business Event');
1568: END IF;
1569: FND_MESSAGE.SET_NAME('WMS', 'WMS_BUSEVENT_INVALID');
1570: FND_MSG_PUB.ADD;
1571: RAISE FND_API.G_EXC_ERROR;
1572: END IF;
1573:
1574: -- Verify parameters passed . Either TaskId should be not NULL or
1575: -- Org and Sub should be Not NULL
1586: trace('Either Task_id is null or orgid is NULL OR sub IS NULL');
1587: END IF;
1588: FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
1589: FND_MSG_PUB.ADD;
1590: RAISE FND_API.G_EXC_ERROR;
1591: END IF;
1592:
1593: -- Validate p_request_id when the busniess event is Task Complete
1594: IF(p_bus_event in (wms_be_task_complete, wms_be_task_skip, wms_be_task_cancel)) THEN
1597: trace('Error: p_request_id is null for Task Complete/skip/cancel bus event');
1598: END IF;
1599: FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
1600: FND_MSG_PUB.ADD;
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
1611: trace('Error: p_request_id is not valid for Task Complete/skip/cancel');
1612: END IF;
1613: FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
1614: FND_MSG_PUB.ADD;
1615: RAISE FND_API.G_EXC_ERROR;
1616: END;
1617: END IF;
1618: IF (l_debug = 1) THEN
1619: trace('Passed validation for Task Complete/skip/cancel, l_parent_request_id='||l_parent_request_id);
1690: trace ('Could not retrieve Event Details');
1691: END IF;
1692: FND_MESSAGE.SET_NAME('WMS', 'WMS_PICKREL_ERR');
1693: FND_MSG_PUB.ADD;
1694: RAISE FND_API.G_EXC_ERROR;
1695: END IF;
1696:
1697: ELSIF (p_task_trx_id IS NOT NULL) THEN
1698: IF (l_debug = 1) THEN
1715: trace('Could not retrieve Event Details');
1716: END IF;
1717: FND_MESSAGE.SET_NAME('WMS', 'WMS_BUSEVENT_ERR');
1718: FND_MSG_PUB.ADD;
1719: RAISE FND_API.G_EXC_ERROR;
1720: END IF;
1721: END IF;
1722: ELSIF (p_task_trx_id IS NOT NULL AND p_bus_event = wms_be_ship_confirm) THEN
1723: IF (l_debug = 1) THEN
1731: trace('Could not retrieve ship confirm details');
1732: END IF;
1733: FND_MESSAGE.SET_NAME('WMS', 'WMS_SHIPCONFIRM_ERR');
1734: FND_MSG_PUB.ADD;
1735: RAISE FND_API.G_EXC_ERROR;
1736: END IF;
1737: end if;
1738:
1739: -- Loop on WMS_DEVICE_REQUESTS per device
1761: trace('Could not retrieve lot and or serial details');
1762: END IF;
1763: FND_MESSAGE.SET_NAME('WMS', 'WMS_LOT_SER_DETAIL_ERR');
1764: FND_MSG_PUB.ADD;
1765: RAISE FND_API.G_EXC_ERROR;
1766: END IF;
1767: l_lot_ser_ok:= NULL;
1768: END IF;
1769: end if;
1804: IF (l_debug = 1) THEN
1805: trace('Error in updating relation_id on the request table for task complete/skip/cancel, dev_id='||l_cur_dev.device_id);
1806: trace('SQL error :'||substr(sqlerrm, 1, 240));
1807: END IF;
1808: RAISE FND_API.G_EXC_ERROR;
1809: END;
1810: END IF;
1811: END IF;
1812:
1873: x_status_msg => l_req_stat_msg );
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;
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
1917: trace('Error: G_EXC_ERR : Delete request rows');
1918: END IF;
1919: delete from wms_device_requests;
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
1927: );
1925: ( p_count => x_msg_count,
1926: p_data => x_msg_data
1927: );
1928:
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;
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
1941: );
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
1955: );
1956:
1957: END;
1958:
1959: PROCEDURE DEVICE_REQUEST(
1960: p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1961: p_bus_event IN NUMBER,
1962: p_call_ctx IN VARCHAR2 ,
1963: p_task_trx_id IN NUMBER := NULL,
1964: p_org_id IN NUMBER := NULL,
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,
2022: p_bus_event IN NUMBER,
2023: p_call_ctx IN VARCHAR2 ,
2024: p_task_trx_id IN NUMBER := NULL,
2025: p_org_id IN NUMBER := NULL,
2089: l_setup_row_cnt:=0;
2090: END;
2091:
2092: IF (l_setup_row_cnt = 0 AND p_bus_event < 50) THEN
2093: x_return_status := FND_API.G_RET_STS_SUCCESS;
2094: RETURN;
2095: END IF;
2096:
2097: SAVEPOINT WMS_DEVICE_REQUESTS;
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;
2102: -- Initialize API return status to success
2103: x_return_status := FND_API.G_RET_STS_SUCCESS;
2099: IF FND_API.to_Boolean( p_init_msg_list ) THEN
2100: FND_MSG_PUB.initialize;
2101: END IF;
2102: -- Initialize API return status to success
2103: x_return_status := FND_API.G_RET_STS_SUCCESS;
2104:
2105: IF (l_debug = 1) THEN
2106: trace('******* Device Request *********');
2107: END IF;
2112: trace('Invlid Business Event');
2113: END IF;
2114: FND_MESSAGE.SET_NAME('WMS', 'WMS_BUSEVENT_INVALID');
2115: FND_MSG_PUB.ADD;
2116: RAISE FND_API.G_EXC_ERROR;
2117: END IF;
2118:
2119: -- Verify parameters passed . Either TaskId should be not NULL or
2120: -- Org and Sub should be Not NULL
2131: trace('Either Task_id is null or orgid is NULL OR sub IS NULL');
2132: END IF;
2133: FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
2134: FND_MSG_PUB.ADD;
2135: RAISE FND_API.G_EXC_ERROR;
2136: END IF;
2137:
2138: -- Validate p_request_id when the busniess event is Task Complete
2139: IF(p_bus_event in (wms_be_task_complete, wms_be_task_skip, wms_be_task_cancel)) THEN
2142: trace('Error: p_request_id is null for Task Complete/skip/cancel bus event');
2143: END IF;
2144: FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
2145: FND_MSG_PUB.ADD;
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
2156: trace('Error: p_request_id is not valid for Task Complete/skip/cancel');
2157: END IF;
2158: FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
2159: FND_MSG_PUB.ADD;
2160: RAISE FND_API.G_EXC_ERROR;
2161: END;
2162: END IF;
2163: IF (l_debug = 1) THEN
2164: trace('Passed validation for Task Complete/skip/cancel, l_parent_request_id='||l_parent_request_id);
2219: trace ('Could not retrieve Event Details');
2220: END IF;
2221: FND_MESSAGE.SET_NAME('WMS', 'WMS_PICKREL_ERR');
2222: FND_MSG_PUB.ADD;
2223: RAISE FND_API.G_EXC_ERROR;
2224: END IF;
2225:
2226: ELSIF (p_task_trx_id IS NOT NULL) THEN
2227: IF (l_debug = 1) THEN
2244: trace('Could not retrieve Event Details');
2245: END IF;
2246: FND_MESSAGE.SET_NAME('WMS', 'WMS_BUSEVENT_ERR');
2247: FND_MSG_PUB.ADD;
2248: RAISE FND_API.G_EXC_ERROR;
2249: END IF;
2250: END IF;
2251: ELSIF (p_task_trx_id IS NOT NULL AND p_bus_event = wms_be_ship_confirm) THEN
2252: IF (l_debug = 1) THEN
2260: trace('Could not retrieve ship confirm details');
2261: END IF;
2262: FND_MESSAGE.SET_NAME('WMS', 'WMS_SHIPCONFIRM_ERR');
2263: FND_MSG_PUB.ADD;
2264: RAISE FND_API.G_EXC_ERROR;
2265: END IF;
2266: end if;
2267:
2268: -- Loop on WMS_DEVICE_REQUESTS per device
2296: trace('Could not retrieve lot and or serial details');
2297: END IF;
2298: FND_MESSAGE.SET_NAME('WMS', 'WMS_LOT_SER_DETAIL_ERR');
2299: FND_MSG_PUB.ADD;
2300: RAISE FND_API.G_EXC_ERROR;
2301: END IF;
2302: l_lot_ser_ok:= NULL;
2303: END IF;
2304: end if;
2339: IF (l_debug = 1) THEN
2340: trace('Error in updating relation_id on the request table for task complete/skip/cancel, dev_id='||l_cur_dev.device_id);
2341: trace('SQL error :'||substr(sqlerrm, 1, 240));
2342: END IF;
2343: RAISE FND_API.G_EXC_ERROR;
2344: END;
2345: END IF;
2346: END IF;
2347:
2408: x_status_msg => l_req_stat_msg );
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;
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
2445: trace('Error: G_EXC_ERR : Delete request rows');
2446: END IF;
2447: delete from wms_device_requests;
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
2455: );
2453: ( p_count => x_msg_count,
2454: p_data => x_msg_data
2455: );
2456:
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;
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
2469: );
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
2483: );
2555: ,p_request_id
2556: ,l_successful_row_cnt
2557: );
2558:
2559: IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
2560: IF (l_debug = 1) THEN
2561: trace('device Confirmation returned:Unexpected error:l_msg_count'||l_msg_count||'::l_msg_data:'||l_msg_data);
2562: END IF;
2563: RAISE FND_API.g_exc_unexpected_error;
2559: IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
2560: IF (l_debug = 1) THEN
2561: trace('device Confirmation returned:Unexpected error:l_msg_count'||l_msg_count||'::l_msg_data:'||l_msg_data);
2562: END IF;
2563: RAISE FND_API.g_exc_unexpected_error;
2564: ELSE
2565: IF (l_debug = 1) THEN
2566: trace('device Confirmation returned:Success:Number of successful rows ::'||l_successful_row_cnt);
2567: END IF;
2604: x_device_status => l_dev_stat,
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
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
2646: AND status_code = 'P';
2647:
2648: COMMIT;
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
2713: IF l_device_cnt = 0 THEN
2714: IF (l_debug = 1) THEN
2715: trace(' No device exist in this Org'||p_org_id);
2716: END IF;
2717: x_return_status := FND_API.G_RET_STS_SUCCESS;
2718: RETURN;
2719: ELSIF p_bus_event_id IS NOT NULL THEN --check in the association table between device and bus_event
2720:
2721: BEGIN
2735: IF (l_setup_row_cnt = 0 AND (p_bus_event_id < 50 )) THEN
2736: IF (l_debug = 1) THEN
2737: trace(' Device Request is not being used for business event='||p_bus_event_id);
2738: END IF;
2739: x_return_status := FND_API.G_RET_STS_SUCCESS;
2740: RETURN;
2741: END IF;
2742:
2743: END IF ;
2755:
2756: IF (l_debug = 1) THEN
2757: trace('wms_call_device_request:::'||wms_call_device_request);
2758: END IF;
2759: x_return_status := FND_API.G_RET_STS_SUCCESS;
2760:
2761: END is_device_set_up;
2762:
2763: