[Home] [Help]
1: PACKAGE BODY WSH_PURGE AS
2: /* $Header: WSHPURGB.pls 120.11.12020000.2 2012/09/25 11:31:27 sunilku ship $ */
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.
525: trip_sql := 'WITH trip_inner_query AS
526: (SELECT inner.trip_id trip_id,
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;
641: -- Bug 10184411
642: -- The following check is being added to ensure no un-shipped or un-interfaced
643: -- delivery lines are being purged.This check is mandatory for TPW shipments.
644: -- Don't prefer to use a sub-query here.But we dont have these columns in the purge view.
645: -- We can add these filtering columns to the view wsh_purge_set_v and scan directly and this sub-query can be flushed out.
646: trip_sql := trip_sql ||
647: ' AND NOT EXISTS
648: ( SELECT 1 FROM
649: wsh_delivery_details dd
657: ) ';
658: trip_sql := trip_sql ||
659: 'GROUP BY inner.trip_id)' ;
660: trip_sql := trip_sql ||' SELECT outer.trip_id , outer.trip_name , ''NON_EMPTY'' purge_set_type
661: FROM wsh_purge_set_v outer ,trip_inner_query
662: where outer.trip_id = trip_inner_query.trip_id
663: GROUP BY outer.trip_id , outer.trip_name
664: HAVING count ( outer.dd_id ) = (select dd_cnt from trip_inner_query where trip_id = outer.trip_id)';
665:
784: CLOSE c_trip_purge_cur;
785:
786: IF x_tbl_trip_purge_set.COUNT > 0 THEN --check for number of records in plsql table
787: IF l_debug_on THEN
788: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.VALIDATE_TRIPS', WSH_DEBUG_SV.C_PROC_LEVEL);
789: END IF;
790: --Check whether LPNs belonging to the trips are eligible to purge from WMS
791: Validate_Trips( p_tbl_trip_purge_set => x_tbl_trip_purge_set,
792: x_tbl_trip_purge_set => l_tbl_trip_purge_set,
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);
1047: END IF;
1048: --Purge Delivery Details
1049: Purge_Delivery_Details( p_tbl_del_detail_purge_set => p_tbl_del_details_purge_set,
1050: x_return_status => l_return_status);
1063: END IF;
1064:
1065: IF p_tbl_containers_purge_set.COUNT > 0 THEN
1066: IF l_debug_on THEN
1067: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_CONTAINERS', WSH_DEBUG_SV.C_PROC_LEVEL);
1068: END IF;
1069: --Purge Containers
1070: Purge_Containers(p_tbl_containers_purge_set => p_tbl_containers_purge_set,
1071: x_return_status => l_return_status);
1084: END IF;
1085:
1086: IF p_tbl_del_legs_purge_set.COUNT > 0 THEN
1087: IF l_debug_on THEN
1088: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_DELIVERY_LEGS', WSH_DEBUG_SV.C_PROC_LEVEL);
1089: END IF;
1090: --Purge Delivery Legs
1091: Purge_Delivery_Legs(p_tbl_del_leg_purge_set => p_tbl_del_legs_purge_set,
1092: x_return_status => l_return_status);
1105: END IF;
1106:
1107: IF p_tbl_trip_stops_purge_set.COUNT > 0 THEN
1108: IF l_debug_on THEN
1109: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_TRIP_STOPS', WSH_DEBUG_SV.C_PROC_LEVEL);
1110: END IF;
1111: --Purge Trip Stops
1112: Purge_Trip_Stops(p_tbl_trip_stop_purge_set => p_tbl_trip_stops_purge_set,
1113: x_return_status => l_return_status);
1127:
1128: IF (p_tbl_delivery_purge_set.COUNT > 0 OR p_tbl_trip_purge_set.COUNT > 0) THEN
1129:
1130: IF l_debug_on THEN
1131: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_WORKFLOW', WSH_DEBUG_SV.C_PROC_LEVEL);
1132: END IF;
1133: --Purge workflows related to Trips and Deliveries
1134: Purge_Workflow( p_tbl_trip_purge_set => p_tbl_trip_purge_set,
1135: p_tbl_delivery_purge_set=> p_tbl_delivery_purge_set,
1145: END IF;
1146:
1147: IF p_tbl_delivery_purge_set.COUNT > 0 THEN
1148: IF l_debug_on THEN
1149: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_DELIVERIES', WSH_DEBUG_SV.C_PROC_LEVEL);
1150: END IF;
1151: --Purge Deliveries
1152: Purge_Deliveries(p_tbl_delivery_purge_set => p_tbl_delivery_purge_set,
1153: x_return_status => l_return_status);
1166: END IF;
1167:
1168: IF p_tbl_trip_purge_set.COUNT > 0 THEN
1169: IF l_debug_on THEN
1170: WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_TRIPS', WSH_DEBUG_SV.C_PROC_LEVEL);
1171: END IF;
1172: --Purge Trips
1173: Purge_Trips(p_tbl_trip_purge_set => p_tbl_trip_purge_set,
1174: x_return_status => l_return_status);
2219: del_pickup_date,
2220: del_dropoff_date,
2221: del_ship_date,
2222: bol
2223: FROM wsh_purge_set_v
2224: WHERE trip_id = p_tripid;
2225:
2226: --Added hints to the query for bug 4891951
2227: CURSOR c_bols_for_del(p_delivery_id NUMBER) IS
2225:
2226: --Added hints to the query for bug 4891951
2227: CURSOR c_bols_for_del(p_delivery_id NUMBER) IS
2228: SELECT /*+use_nl(v.wda, v.wnd, v.wdl)*/ distinct v.bol
2229: FROM wsh_purge_set_v v
2230: WHERE v.del_id = p_delivery_id
2231: AND v.bol is not null;
2232:
2233: --Get Sales Order Details for Deliveries
2235: CURSOR c_so_for_delivery(p_delivery_id NUMBER) IS
2236: SELECT /*+use_nl(v.wda, v.wnd, v.wdl)*/ DISTINCT v.dd_source_header_number,
2237: v.dd_source_header_type_name--,
2238: -- dd_creation_date
2239: FROM wsh_purge_set_v v
2240: WHERE v.del_id = p_delivery_id
2241: AND v.dd_source_header_number is not null;
2242:
2243: CURSOR c_dels_trips_for_order(p_ordernumber VARCHAR2) IS
2252: del_pickup_date,
2253: del_dropoff_date,
2254: del_ship_date,
2255: bol
2256: FROM wsh_purge_set_v
2257: WHERE dd_source_header_number= p_ordernumber;
2258:
2259: --Get Containers for Sales Orders
2260: /*CURSOR c_containers_for_so(order_number varchar2) IS
2358: SELECT meaning
2359: INTO l_src_meaning
2360: FROM wsh_lookups
2361: WHERE lookup_code = p_source_system
2362: AND lookup_type in ('WSH_PURGE_SOURCE_SYSTEMS','SOURCE_SYSTEM')
2363: AND LOOKUP_CODE <> 'OKE'
2364: AND enabled_flag = 'Y'
2365: AND Trunc(SYSDATE) BETWEEN Nvl(start_date_active, Trunc(SYSDATE)) AND Nvl
2366: (
3112: WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3113: END IF;
3114: END Validate_Trips ;
3115:
3116: END WSH_PURGE;