[Home] [Help]
1: PACKAGE BODY WSH_PURGE AS
2: /* $Header: WSHPURGB.pls 120.4 2006/04/10 23:20:16 alksharm noship $ */
3:
4: -- Description: Constant to distinguish CONCURRENT request from
5: -- ONLINE request
5: -- ONLINE request
6: G_CONC_REQ VARCHAR2(1) := FND_API.G_TRUE;
7:
8: --Package Name
9: G_PKG_NAME CONSTANT VARCHAR2(50):='WSH_PURGE';
10:
11: -----------------------------------------------------------------------------
12: --
13: -- Procedure: Process_Purge
130: WSH_DEBUG_SV.log(l_module_name,'P_PRINT_DETAIL',p_print_detail);
131: END IF;
132:
133: IF l_debug_on THEN
134: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.GET_PURGE_SET', WSH_DEBUG_SV.C_PROC_LEVEL);
135: END IF;
136:
137: --call Get_Purge_Set
138: Get_Purge_Set( p_source_system => p_source_system ,
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;
169:
170: IF l_debug_on THEN
173: RETURN;
174: END IF;
175:
176: IF l_debug_on THEN
177: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.GENERATE_PURGE_REPORT', WSH_DEBUG_SV.C_PROC_LEVEL);
178: END IF;
179: --call Generate_Purge_Report
180: Generate_Purge_Report( p_execution_mode => p_execution_mode ,
181: p_source_system => p_source_system ,
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;
215:
216: IF l_debug_on THEN
220: END IF;
221:
222: IF p_execution_mode = 'P' THEN
223: IF l_debug_on THEN
224: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_ENTITIES', WSH_DEBUG_SV.C_PROC_LEVEL);
225: END IF;
226:
227: --call Purge_Entities
228: Purge_Entities(p_tbl_trip_purge_set => l_tbl_trip_purge_set ,
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;
246:
247: IF l_debug_on THEN
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
307: ==============================================================================
308: Input: Parameters as given in the FDD.
503: --construct trip_sql
504: trip_sql := 'SELECT
505: trip_id,trip_name,''NON_EMPTY'' purge_set_type
506: FROM
507: wsh_purge_set_v outer
508: GROUP BY trip_id,trip_name
509: HAVING count(outer.dd_id) = (SELECT count(inner.dd_id)
510: FROM
511: wsh_purge_set_v inner ';
507: wsh_purge_set_v outer
508: GROUP BY trip_id,trip_name
509: HAVING count(outer.dd_id) = (SELECT count(inner.dd_id)
510: FROM
511: wsh_purge_set_v inner ';
512: --check whether FTE is installed
513: IF (wsh_util_core.fte_is_installed='Y') THEN
514: trip_sql := trip_sql || ',fte_invoice_headers fih ' ;
515: END IF;
708: CLOSE c_trip_purge_cur;
709:
710: IF x_tbl_trip_purge_set.COUNT > 0 THEN --check for number of records in plsql table
711: IF l_debug_on THEN
712: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.VALIDATE_TRIPS', WSH_DEBUG_SV.C_PROC_LEVEL);
713: END IF;
714: --Check whether LPNs belonging to the trips are eligible to purge from WMS
715: Validate_Trips( p_tbl_trip_purge_set => x_tbl_trip_purge_set,
716: x_tbl_trip_purge_set => l_tbl_trip_purge_set,
917: x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
918:
919: IF p_tbl_del_details_purge_set.COUNT > 0 THEN
920: IF l_debug_on THEN
921: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_DELIVERY_DETAILS', WSH_DEBUG_SV.C_PROC_LEVEL);
922: END IF;
923: --Purge Delivery Details
924: Purge_Delivery_Details( p_tbl_del_detail_purge_set => p_tbl_del_details_purge_set,
925: x_return_status => l_return_status);
938: END IF;
939:
940: IF p_tbl_containers_purge_set.COUNT > 0 THEN
941: IF l_debug_on THEN
942: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_CONTAINERS', WSH_DEBUG_SV.C_PROC_LEVEL);
943: END IF;
944: --Purge Containers
945: Purge_Containers(p_tbl_containers_purge_set => p_tbl_containers_purge_set,
946: x_return_status => l_return_status);
959: END IF;
960:
961: IF p_tbl_del_legs_purge_set.COUNT > 0 THEN
962: IF l_debug_on THEN
963: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_DELIVERY_LEGS', WSH_DEBUG_SV.C_PROC_LEVEL);
964: END IF;
965: --Purge Delivery Legs
966: Purge_Delivery_Legs(p_tbl_del_leg_purge_set => p_tbl_del_legs_purge_set,
967: x_return_status => l_return_status);
980: END IF;
981:
982: IF p_tbl_trip_stops_purge_set.COUNT > 0 THEN
983: IF l_debug_on THEN
984: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_TRIP_STOPS', WSH_DEBUG_SV.C_PROC_LEVEL);
985: END IF;
986: --Purge Trip Stops
987: Purge_Trip_Stops(p_tbl_trip_stop_purge_set => p_tbl_trip_stops_purge_set,
988: x_return_status => l_return_status);
1002:
1003: IF (p_tbl_delivery_purge_set.COUNT > 0 OR p_tbl_trip_purge_set.COUNT > 0) THEN
1004:
1005: IF l_debug_on THEN
1006: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_WORKFLOW', WSH_DEBUG_SV.C_PROC_LEVEL);
1007: END IF;
1008: --Purge workflows related to Trips and Deliveries
1009: Purge_Workflow( p_tbl_trip_purge_set => p_tbl_trip_purge_set,
1010: p_tbl_delivery_purge_set=> p_tbl_delivery_purge_set,
1020: END IF;
1021:
1022: IF p_tbl_delivery_purge_set.COUNT > 0 THEN
1023: IF l_debug_on THEN
1024: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_DELIVERIES', WSH_DEBUG_SV.C_PROC_LEVEL);
1025: END IF;
1026: --Purge Deliveries
1027: Purge_Deliveries(p_tbl_delivery_purge_set => p_tbl_delivery_purge_set,
1028: x_return_status => l_return_status);
1041: END IF;
1042:
1043: IF p_tbl_trip_purge_set.COUNT > 0 THEN
1044: IF l_debug_on THEN
1045: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_TRIPS', WSH_DEBUG_SV.C_PROC_LEVEL);
1046: END IF;
1047: --Purge Trips
1048: Purge_Trips(p_tbl_trip_purge_set => p_tbl_trip_purge_set,
1049: x_return_status => l_return_status);
2093: del_pickup_date,
2094: del_dropoff_date,
2095: del_ship_date,
2096: bol
2097: FROM wsh_purge_set_v
2098: WHERE trip_id = p_tripid;
2099:
2100: --Added hints to the query for bug 4891951
2101: CURSOR c_bols_for_del(p_delivery_id NUMBER) IS
2099:
2100: --Added hints to the query for bug 4891951
2101: CURSOR c_bols_for_del(p_delivery_id NUMBER) IS
2102: SELECT /*+use_nl(v.wda, v.wnd, v.wdl)*/ distinct v.bol
2103: FROM wsh_purge_set_v v
2104: WHERE v.del_id = p_delivery_id
2105: AND v.bol is not null;
2106:
2107: --Get Sales Order Details for Deliveries
2109: CURSOR c_so_for_delivery(p_delivery_id NUMBER) IS
2110: SELECT /*+use_nl(v.wda, v.wnd, v.wdl)*/ DISTINCT v.dd_source_header_number,
2111: v.dd_source_header_type_name--,
2112: -- dd_creation_date
2113: FROM wsh_purge_set_v v
2114: WHERE v.del_id = p_delivery_id
2115: AND v.dd_source_header_number is not null;
2116:
2117: CURSOR c_dels_trips_for_order(p_ordernumber VARCHAR2) IS
2126: del_pickup_date,
2127: del_dropoff_date,
2128: del_ship_date,
2129: bol
2130: FROM wsh_purge_set_v
2131: WHERE dd_source_header_number= p_ordernumber;
2132:
2133: --Get Containers for Sales Orders
2134: /*CURSOR c_containers_for_so(order_number varchar2) IS
2925: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2926: END IF;
2927: END Validate_Trips ;
2928:
2929: END WSH_PURGE;