[Home] [Help]
98: BEGIN
99:
100: -- Set for logging
101: IF G_CONC_REQ = FND_API.G_TRUE THEN
102: WSH_UTIL_CORE.Enable_Concurrent_Log_Print;
103: END IF;
104:
105: -- Debug Statements
106: --
160: IF l_debug_on THEN
161: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
162: END IF;
163:
164: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
165: IF G_CONC_REQ = FND_API.G_TRUE THEN
166: errbuf := 'Error occurred in WSH_PURGE.GET_PURGE_SET';
167: retcode := '2';
168: END IF;
206: IF l_debug_on THEN
207: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
208: END IF;
209:
210: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
211: IF G_CONC_REQ = FND_API.G_TRUE THEN
212: errbuf := 'Error occurred in WSH_PURGE.GENERATE_PURGE_REPORT';
213: retcode := '2';
214: END IF;
237: IF l_debug_on THEN
238: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
239: END IF;
240:
241: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
242: IF G_CONC_REQ = FND_API.G_TRUE THEN
243: errbuf := 'Error occurred in WSH_PURGE.PURGE_ENTITIES';
244: retcode := '2';
245: END IF;
298: x_tbl_del_details_purge_set - pl/sql table of delivery detail id's eligible for purge
299: x_tbl_del_legs_purge_set - pl/sql table of delivery leg id's eligible for purge
300: x_tbl_trip_stops_purge_set - pl/sql table of trip stop id's eligible for purge
301: x_tbl_containers_purge_set - pl/sql table of container id's eligible for purge
302: x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
303:
304: Description: This API gets all the Shipping Data from the view WSH_PURGE_SET_V
305: and puts it into the plsql tables for respective entities after validating it
306: with the user given parameters
517: WSH_DEBUG_SV.log(l_module_name,'P_CREATE_DATE_TO',p_create_date_to);
518: WSH_DEBUG_SV.log(l_module_name,'P_DEL_BEYOND_CREATION_DAYS',p_del_beyond_creation_days);
519: END IF;
520:
521: x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
522:
523: --construct trip_sql
524: --modifed sql for bug 12615666
525: trip_sql := 'WITH trip_inner_query AS
527: COUNT ( inner.dd_id ) dd_cnt
528: FROM
529: wsh_purge_set_v inner ';
530: --check whether FTE is installed
531: IF (wsh_util_core.fte_is_installed='Y') THEN
532: trip_sql := trip_sql || ',fte_invoice_headers fih ' ;
533: END IF;
534:
535: trip_sql :=trip_sql ||'WHERE ';
625: ),
626: ''FALSE'') = ''FALSE''
627: ';
628: --check whether FTE is installed
629: IF (wsh_util_core.fte_is_installed='Y') THEN
630: trip_sql := trip_sql ||
631: ' AND fih.bol(+) = inner.bol
632: AND DECODE(fih.bill_status,
633: ''PAID'', ''Y'',
797: IF l_debug_on THEN
798: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
799: END IF;
800:
801: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
802: x_return_status := l_return_status;
803: IF l_debug_on THEN
804: WSH_DEBUG_SV.pop(l_module_name);
805: END IF;
977: END IF;
978:
979: EXCEPTION
980: WHEN OTHERS THEN
981: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
982: IF l_debug_on THEN
983: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
984: SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
985: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
996: p_tbl_del_details_purge_set - pl/sql table of delivery detail id's eligible for purge
997: p_tbl_del_legs_purge_set - pl/sql table of delivery leg id's eligible for purge
998: p_tbl_trip_stops_purge_set - pl/sql table of trip stop id's eligible for purge
999: p_tbl_containers_purge_set pl/sql - table of container id's eligible for purge
1000: x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1001:
1002: Description: This API calls the individual APIs to delete the data in
1003: Shipping and Transportation tables
1004: =============================================================================
1038: IF l_debug_on THEN
1039: WSH_DEBUG_SV.push(l_module_name);
1040: END IF;
1041:
1042: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1043:
1044: IF p_tbl_del_details_purge_set.COUNT > 0 THEN
1045: IF l_debug_on THEN
1046: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_DELIVERY_DETAILS', WSH_DEBUG_SV.C_PROC_LEVEL);
1052: IF l_debug_on THEN
1053: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
1054: END IF;
1055:
1056: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1057: x_return_status := l_return_status;
1058: IF l_debug_on THEN
1059: WSH_DEBUG_SV.pop(l_module_name);
1060: END IF;
1073: IF l_debug_on THEN
1074: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
1075: END IF;
1076:
1077: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1078: x_return_status := l_return_status;
1079: IF l_debug_on THEN
1080: WSH_DEBUG_SV.pop(l_module_name);
1081: END IF;
1094: IF l_debug_on THEN
1095: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
1096: END IF;
1097:
1098: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1099: x_return_status := l_return_status;
1100: IF l_debug_on THEN
1101: WSH_DEBUG_SV.pop(l_module_name);
1102: END IF;
1115: IF l_debug_on THEN
1116: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
1117: END IF;
1118:
1119: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1120: x_return_status := l_return_status;
1121: IF l_debug_on THEN
1122: WSH_DEBUG_SV.pop(l_module_name);
1123: END IF;
1134: Purge_Workflow( p_tbl_trip_purge_set => p_tbl_trip_purge_set,
1135: p_tbl_delivery_purge_set=> p_tbl_delivery_purge_set,
1136: x_return_status => l_return_status);
1137:
1138: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1139: x_return_status := l_return_status;
1140: IF l_debug_on THEN
1141: WSH_DEBUG_SV.pop(l_module_name);
1142: END IF;
1155: IF l_debug_on THEN
1156: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
1157: END IF;
1158:
1159: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1160: x_return_status := l_return_status;
1161: IF l_debug_on THEN
1162: WSH_DEBUG_SV.pop(l_module_name);
1163: END IF;
1176: IF l_debug_on THEN
1177: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
1178: END IF;
1179:
1180: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1181: x_return_status := l_return_status;
1182: IF l_debug_on THEN
1183: WSH_DEBUG_SV.pop(l_module_name);
1184: END IF;
1192:
1193:
1194: EXCEPTION
1195: WHEN OTHERS THEN
1196: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1197: IF l_debug_on THEN
1198: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1199: SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1200: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1206:
1207: /*-----------------------------------------------------------------------------
1208: Procedure: Purge_Trips
1209: Parameters: p_tbl_trip_purge_set pl/sql table of trip id's eligible for purge
1210: x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1211:
1212: Description: This API delete the data in Shipping and Transportation
1213: related to trip
1214: ==============================================================================
1241: IF l_debug_on THEN
1242: WSH_DEBUG_SV.push(l_module_name);
1243: END IF;
1244:
1245: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1246:
1247: FOR l_loop_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
1248: LOOP
1249: l_trip_id := p_tbl_trip_purge_set(l_loop_index).trip_id;
1296: END IF;
1297:
1298: EXCEPTION
1299: WHEN OTHERS THEN
1300: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1301: IF l_debug_on THEN
1302: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1303: SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1304: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1309:
1310: /*-----------------------------------------------------------------------------
1311: Procedure: Purge_Deliveries
1312: Parameters: p_tbl_delivery_purge_set pl/sql table of delivery id's eligible for purge
1313: x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1314:
1315: Description: This API delete the data in Shipping and Transportation
1316: related to delivery
1317: =============================================================================+
1354: IF l_debug_on THEN
1355: WSH_DEBUG_SV.push(l_module_name);
1356: END IF;
1357:
1358: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1359:
1360: FOR l_loop_index in p_tbl_delivery_purge_set.FIRST .. p_tbl_delivery_purge_set.LAST
1361: LOOP
1362:
1403: WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_document_instances: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1404: END IF;
1405: END IF;
1406:
1407: IF (wsh_util_core.fte_is_installed='Y') THEN
1408:
1409: DELETE
1410: FROM fte_shipment_status_details
1411: WHERE transaction_id IN (SELECT transaction_id
1572: END IF;
1573:
1574: EXCEPTION
1575: WHEN OTHERS THEN
1576: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1577: IF l_debug_on THEN
1578: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1579: SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1580: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1586: /*-----------------------------------------------------------------------------
1587: Procedure: Purge_Trip_Stops
1588: Parameters: p_tbl_trip_stop_purge_set pl/sql table of trip stop id's
1589: eligible for purge
1590: x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1591:
1592: Description: This API delete the data in Shipping and Transportation
1593: related to trip stop
1594: ==============================================================================
1620: IF l_debug_on THEN
1621: WSH_DEBUG_SV.push(l_module_name);
1622: END IF;
1623:
1624: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1625:
1626: FOR l_loop_index in p_tbl_trip_stop_purge_set.FIRST .. p_tbl_trip_stop_purge_set.LAST
1627: LOOP
1628: l_stop_id := p_tbl_trip_stop_purge_set(l_loop_index).stop_id;
1664: END IF;
1665:
1666: EXCEPTION
1667: WHEN OTHERS THEN
1668: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1669: IF l_debug_on THEN
1670: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1671: SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1672: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1677:
1678: /*-----------------------------------------------------------------------------
1679: Procedure: Purge_Delivery_Legs
1680: Parameters: p_tbl_del_leg_purge_set pl/sql table of delivery leg id's eligible for purge
1681: x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1682:
1683: Description: This API delete the data in Shipping and Transportation
1684: related to delivery leg
1685: ==============================================================================
1715: IF l_debug_on THEN
1716: WSH_DEBUG_SV.push(l_module_name);
1717: END IF;
1718:
1719: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1720:
1721: FOR l_loop_index in p_tbl_del_leg_purge_set.FIRST .. p_tbl_del_leg_purge_set.LAST
1722: LOOP
1723: l_leg_id := p_tbl_del_leg_purge_set(l_loop_index).delivery_leg_id;
1731: WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1732: END IF;
1733: END IF;
1734:
1735: IF (wsh_util_core.fte_is_installed='Y') THEN
1736:
1737: DELETE
1738: FROM fte_invoice_lines
1739: WHERE invoice_header_id IN ( SELECT fih.invoice_header_id
1838: END IF;
1839:
1840: EXCEPTION
1841: WHEN OTHERS THEN
1842: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1843: IF l_debug_on THEN
1844: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1845: SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1846: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1852: /*-----------------------------------------------------------------------------
1853: Procedure: Purge_Delivery_Details
1854: Parameters: p_tbl_del_detail_purge_set pl/sql table of delivery detail id's
1855: eligible for purge
1856: x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1857:
1858: Description: This API delete the data in Shipping and Transportation
1859: related to delivery detail
1860: =============================================================================
1885: IF l_debug_on THEN
1886: WSH_DEBUG_SV.push(l_module_name);
1887: END IF;
1888:
1889: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1890:
1891: FOR l_loop_index in p_tbl_del_detail_purge_set.FIRST .. p_tbl_del_detail_purge_set.LAST
1892: LOOP
1893: l_detail_id := p_tbl_del_detail_purge_set(l_loop_index).delivery_detail_id;
1948: END IF;
1949:
1950: EXCEPTION
1951: WHEN OTHERS THEN
1952: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1953: IF l_debug_on THEN
1954: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1955: SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1956: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1961:
1962: /*-----------------------------------------------------------------------------
1963: Procedure: Purge_Containers
1964: Parameters: p_tbl_containers_purge_set pl/sql table of container id's eligible for purge
1965: x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1966:
1967: Description: This API calls the WMS API to delete the data in WMS
1968: related to Containers
1969: ==============================================================================
2000: IF l_debug_on THEN
2001: WSH_DEBUG_SV.push(l_module_name);
2002: END IF;
2003:
2004: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2005:
2006: FOR l_loop_index in p_tbl_containers_purge_set.FIRST .. p_tbl_containers_purge_set.LAST
2007: LOOP
2008: l_container_id := p_tbl_containers_purge_set(l_loop_index).container_id;
2074: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
2075: WSH_DEBUG_SV.log(l_module_name,'L_MSG_DATA',l_msg_data);
2076: END IF;
2077:
2078: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2079: x_return_status := l_return_status;
2080: IF l_debug_on THEN
2081: WSH_DEBUG_SV.pop(l_module_name);
2082: END IF;
2088: END IF;
2089:
2090: EXCEPTION
2091: WHEN OTHERS THEN
2092: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2093: IF l_debug_on THEN
2094: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2095: SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2096: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2140: -- p_count_legs count of delivery legs to be purged/eligible to be purged
2141: -- p_count_stops count of trip stops to be purged/eligible to be purged
2142: -- p_count_details count of delivery details to be purged/eligible to be purged
2143: -- p_count_containers count of containers to be purged/eligible to be purged
2144: -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
2145:
2146: -- Description: This API generates the XML and writes it in output file
2147: -- of the concurrent program to be used by the XML Publisher
2148: -- to generate the XML report
2297: IF l_debug_on THEN
2298: WSH_DEBUG_SV.push(l_module_name);
2299: END IF;
2300:
2301: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2302:
2303: l_nonempty_count := 0;
2304: l_lpn_count := 0;
2305: l_empty_trip_count := 0;
2371:
2372:
2373: IF p_ship_from_org IS NOT NULL THEN
2374: FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'
2375: WSH_UTIL_CORE.GET_ORG_NAME(p_organization_id => to_number(p_ship_from_org)) ||
2376: '
2377: ELSE
2378: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '
2379: END IF;
2657: END IF;
2658:
2659: EXCEPTION
2660: WHEN OTHERS THEN
2661: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2662: l_err := SQLERRM;
2663:
2664: IF l_debug_on THEN
2665: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2674: --
2675: -- Procedure: Purge_Workflow
2676: -- Parameters: p_tbl_trip_purge_set pl/sql table of trip id's eligible for purge
2677: -- p_tbl_delivery_purge_set pl/sql table of delivery id's eligible for purge
2678: -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
2679:
2680: -- Description: This API deletes the workflows for Trip and Delivery.
2681: -----------------------------------------------------------------------------
2682:
2686:
2687: l_debug_on BOOLEAN;
2688: l_return_status VARCHAR2(1);
2689:
2690: l_delivery_ids_tab WSH_UTIL_CORE.column_tab_type;
2691: l_trip_ids_tab WSH_UTIL_CORE.column_tab_type;
2692:
2693: l_success_count NUMBER;
2694:
2687: l_debug_on BOOLEAN;
2688: l_return_status VARCHAR2(1);
2689:
2690: l_delivery_ids_tab WSH_UTIL_CORE.column_tab_type;
2691: l_trip_ids_tab WSH_UTIL_CORE.column_tab_type;
2692:
2693: l_success_count NUMBER;
2694:
2695: l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_WORKFLOW';
2707: IF l_debug_on THEN
2708: WSH_DEBUG_SV.push(l_module_name);
2709: END IF;
2710:
2711: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2712:
2713: --Check for number of ids in delivery table
2714: IF p_tbl_delivery_purge_set.COUNT > 0 THEN
2715: FOR l_delivery_index in p_tbl_delivery_purge_set.FIRST .. p_tbl_delivery_purge_set.LAST
2734: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
2735: WSH_DEBUG_SV.log(l_module_name,'L_SUCCESS_COUNT',l_success_count);
2736: END IF;
2737:
2738: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2739: x_return_status := l_return_status;
2740: IF l_debug_on THEN
2741: WSH_DEBUG_SV.pop(l_module_name);
2742: END IF;
2767: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
2768: WSH_DEBUG_SV.log(l_module_name,'L_SUCCESS_COUNT',l_success_count);
2769: END IF;
2770:
2771: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2772: x_return_status := l_return_status;
2773: IF l_debug_on THEN
2774: WSH_DEBUG_SV.pop(l_module_name);
2775: END IF;
2789: -- Procedure: Validate_Trips
2790: -- Parameters: p_tbl_trip_purge_set pl/sql table of trip id's eligible for purge
2791: -- x_tbl_trip_purge_set pl/sql table of trip id's eligible for purge
2792: -- after validating all the LPNs belonging to the trip with WMS API
2793: -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
2794:
2795: -- Description: This API call the WMS_Container_Grp API for checking the validity of
2796: -- each LPN belonging to a particular trip.
2797: -- The WMS LPN Purge API returns the list of LPN Ids that are eligible to
2848: TYPE PurgeCurType IS REF CURSOR;
2849: c_trip_moves PurgeCurType; --The cursor gets all the moves their trips
2850: --wherever the move has more than 1 trip
2851: l_tbl_trip_moves Trip_moves_Tbl_Type;
2852: l_tbl_trip_mdc WSH_UTIL_CORE.ID_TAB_TYPE;
2853:
2854: CURSOR c_lpns_for_trip(p_tripid NUMBER) IS
2855: SELECT wdd.lpn_id
2856: FROM wsh_trips wt,
2961: IF l_debug_on THEN
2962: WSH_DEBUG_SV.push(l_module_name);
2963: END IF;
2964:
2965: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2966: l_lpn_valid := TRUE;
2967:
2968: IF (wsh_util_core.fte_is_installed='Y') THEN
2969: sql_tripmove := 'SELECT move_id, trip_id
2964:
2965: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2966: l_lpn_valid := TRUE;
2967:
2968: IF (wsh_util_core.fte_is_installed='Y') THEN
2969: sql_tripmove := 'SELECT move_id, trip_id
2970: FROM fte_trip_moves
2971: WHERE move_id IN
2972: (SELECT move_id
3011: WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
3012: WSH_DEBUG_SV.log(l_module_name,'L_MSG_DATA',l_msg_data);
3013: END IF;
3014:
3015: IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
3016: x_return_status := l_return_status;
3017: IF l_debug_on THEN
3018: WSH_DEBUG_SV.pop(l_module_name);
3019: END IF;
3102: END IF;
3103:
3104: EXCEPTION
3105: WHEN OTHERS THEN
3106: x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
3107: l_err := SQLERRM;
3108:
3109: IF l_debug_on THEN
3110: WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||