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
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
14 -- Parameters: errbuf Parameter for the Concurrent Program to get the error.
15 -- retcode Parameter for the Concurrent Program to get the return code
16 -- p_execution_mode Specifies whether to Purge Data or View Purge Set
17 -- p_source_system Only the delivery details belonging to this Source System
18 -- would be considered eligible for Purge
19 -- p_ship_from_org Only the deliveries belonging to this Ship From Org
20 -- would be considered eligible for Purge
21 -- p_order_number_from Only the delivery details having source_header_number
22 -- greater than Order Number From would be considered eligible for Purge
23 -- p_order_number_to Only the delivery details having source_header_number
24 -- less than Order Number To would be considered eligible for Purge
25 -- p_order_type Only the delivery details belonging to this Order Type
26 -- would be considered eligible for Purge
27 -- p_ship_date_from Only the deliveries having initial_pickup_date greater
28 -- than Ship Date From would be considered eligible for Purge
29 -- p_ship_date_to Only the deliveries having initial_pickup_date less than
30 -- Ship Date To would be considered eligible for Purge
31 -- p_delete_beyond_x_ship_days Only the deliveries having initial_pickup_date less
32 -- than the specified date would be considered eligible for Purge
33 -- p_purge_intransit_trips Decides whether to purge In Transit Trips or not
34 -- p_delete_empty_records Decides whether to delete empty record or not.
35 -- The empty records can be Empty Trips, Orphaned Empty Deliveries,
36 -- Delivery with Empty containers, Empty Containers
37 -- p_create_date_from Only Empty records having creation_date greater than this
38 -- date would be purged
39 -- p_create_date_to Only Empty records having creation_date less than this
40 -- date would be purged
41 -- p_del_beyond_creation_days Only Empty records having creation_date less than
42 -- this date would be purged
43 -- p_sort_per_criteria Sorts the report output according to Trip,
44 -- Delivery or Order Number
45 -- p_print_detail If "Detail with LPN", the report would contain the parameters / summary
46 -- page and all detail pages with Trips, Deliveries and
47 -- Sales Orders with Container data eligible to purge or purged.
48 -- If "Detail", the report would contain the parameters / summary
49 -- page and all detail pages with Trips, Deliveries and
50 -- Sales Orders data eligible to purge or purged.
51 -- If "Summary", the report would contain only the parameters / summary page.
52
53 -- Description: This procedure is called by the concurrent program. The procedure has the following structure
54 -- calls Get_Purge_Set - To get the valid entities to be purged
55 -- calls Purge_Entities - To purge data in Shipping/FTE tables
56 -- calls Generate_Report - To generate the report through XML publisher
57 -----------------------------------------------------------------------------
58
59 PROCEDURE Process_Purge( errbuf OUT NOCOPY VARCHAR2,
60 retcode OUT NOCOPY VARCHAR2,
61 p_execution_mode varchar2,
62 p_source_system varchar2,
63 p_ship_from_org number,
64 p_order_number_from varchar2,
65 p_source_system_dummy varchar2,
66 p_order_number_to varchar2,
67 p_dummy_order varchar2,
68 p_order_type number,
69 p_ship_date_from varchar2,
70 p_ship_date_to varchar2,
71 p_dummy_ship_date varchar2,
72 p_delete_beyond_x_ship_days number,
73 p_dummy_x_ship_days varchar2,
74 p_purge_intransit_trips varchar2,
75 p_delete_empty_records varchar2,
76 p_create_date_from varchar2,
77 p_create_date_to varchar2,
78 p_dummy_create_date varchar2,
79 p_del_beyond_creation_days number,
80 p_dummy_x_create_days varchar2,
81 p_sort_per_criteria varchar2,
82 p_print_detail varchar2
83 )IS
84
85 l_return_status VARCHAR2(1);
86 l_debug_on BOOLEAN;
87
88 --PLSQL tables for the entities
89 l_tbl_trip_purge_set Trip_ID_Tbl_Type; --Trip
90 l_tbl_delivery_purge_set Delivery_ID_Tbl_Type; --Delivery
91 l_tbl_del_detail_purge_set Del_Detail_ID_Tbl_Type; -- Delivery Detail
92 l_tbl_del_leg_purge_set Del_Leg_ID_Tbl_Type; --Delivery Leg
93 l_tbl_trip_stop_purge_set Trip_Stop_ID_Tbl_Type; --Trip Stop
94 l_tbl_container_purge_set Container_ID_Tbl_Type; --Container
95
96 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_PURGE';
97
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 --
107 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
108 --
109 IF l_debug_on IS NULL THEN
110 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
111 END IF;
112 --
113 IF l_debug_on THEN
114 WSH_DEBUG_SV.push(l_module_name);
115 WSH_DEBUG_SV.log(l_module_name,'P_EXECUTION_MODE',p_execution_mode);
116 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_SYSTEM',p_source_system);
117 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_ORG',p_ship_from_org);
118 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER_FROM',p_order_number_from);
119 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER_To',p_order_number_to);
120 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_TYPE',p_order_type);
121 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE_FROM',p_ship_date_from);
122 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE_TO',p_ship_date_to);
123 WSH_DEBUG_SV.log(l_module_name,'P_DELETE_BEYOND_X_SHIP_DAYS',p_delete_beyond_x_ship_days);
124 WSH_DEBUG_SV.log(l_module_name,'P_PURGE_INTRANSIT_TRIPS',p_purge_intransit_trips);
125 WSH_DEBUG_SV.log(l_module_name,'P_DELETE_EMPTY_Records',p_delete_empty_records);
126 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_DATE_FROM',p_create_date_from);
127 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_DATE_TO',p_create_date_to);
128 WSH_DEBUG_SV.log(l_module_name,'P_DEL_BEYOND_CREATION_DAYS',p_del_beyond_creation_days);
129 WSH_DEBUG_SV.log(l_module_name,'P_SORT_PER_CRITERIA',p_sort_per_criteria);
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 ,
139 p_ship_from_org => p_ship_from_org ,
140 p_order_number_from => p_order_number_from ,
141 p_order_number_to => p_order_number_to ,
142 p_order_type => p_order_type ,
143 p_ship_date_from => p_ship_date_from ,
144 p_ship_date_to => p_ship_date_to ,
145 p_delete_beyond_x_ship_days => p_delete_beyond_x_ship_days ,
146 p_purge_intransit_trips => p_purge_intransit_trips ,
147 p_delete_empty_records => p_delete_empty_records ,
148 p_create_date_from => p_create_date_from ,
149 p_create_date_to => p_create_date_to ,
150 p_del_beyond_creation_days => p_del_beyond_creation_days ,
151 x_tbl_trip_purge_set => l_tbl_trip_purge_set ,
152 x_tbl_delivery_purge_set => l_tbl_delivery_purge_set,
153 x_tbl_del_details_purge_set => l_tbl_del_detail_purge_set,
154 x_tbl_del_legs_purge_set => l_tbl_del_leg_purge_set ,
155 x_tbl_trip_stops_purge_set => l_tbl_trip_stop_purge_set,
156 x_tbl_containers_purge_set => l_tbl_container_purge_set,
157 x_return_status => l_return_status
158 );
159
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;
169
170 IF l_debug_on THEN
171 WSH_DEBUG_SV.pop(l_module_name);
172 END IF;
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 ,
182 p_ship_from_org => p_ship_from_org ,
183 p_order_number_from => p_order_number_from ,
184 p_order_number_to => p_order_number_to ,
185 p_order_type => p_order_type ,
186 p_ship_date_from => p_ship_date_from ,
187 p_ship_date_to => p_ship_date_to ,
188 p_delete_beyond_x_ship_days => p_delete_beyond_x_ship_days ,
189 p_purge_intransit_trips => p_purge_intransit_trips ,
190 p_delete_empty_records => p_delete_empty_records ,
191 p_create_date_from => p_create_date_from ,
192 p_create_date_to => p_create_date_to ,
193 p_del_beyond_creation_days => p_del_beyond_creation_days ,
194 p_sort_per_criteria => p_sort_per_criteria ,
195 p_print_detail => p_print_detail ,
196 p_tbl_trip_purge_set => l_tbl_trip_purge_set ,
197 p_tbl_delivery_purge_set => l_tbl_delivery_purge_set,
198 p_tbl_container_purge_set => l_tbl_container_purge_set,
199 p_count_legs => l_tbl_del_leg_purge_set.COUNT ,
200 p_count_stops => l_tbl_trip_stop_purge_set.COUNT,
201 p_count_details => l_tbl_del_detail_purge_set.COUNT,
202 p_count_containers => l_tbl_container_purge_set.COUNT,
203 x_return_status => l_return_status
204 );
205
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;
215
216 IF l_debug_on THEN
217 WSH_DEBUG_SV.pop(l_module_name);
218 END IF;
219 RETURN;
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 ,
229 p_tbl_delivery_purge_set => l_tbl_delivery_purge_set ,
230 p_tbl_del_details_purge_set => l_tbl_del_detail_purge_set ,
231 p_tbl_del_legs_purge_set => l_tbl_del_leg_purge_set ,
232 p_tbl_trip_stops_purge_set => l_tbl_trip_stop_purge_set ,
233 p_tbl_containers_purge_set => l_tbl_container_purge_set ,
234 x_return_status => l_return_status
235 );
236
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;
246
247 IF l_debug_on THEN
248 WSH_DEBUG_SV.pop(l_module_name);
249 END IF;
250 RETURN;
251 END IF;
252 END IF;
253
254 l_tbl_trip_purge_set.DELETE;
255 l_tbl_delivery_purge_set.DELETE;
256 l_tbl_del_detail_purge_set.DELETE;
257 l_tbl_del_leg_purge_set.DELETE;
258 l_tbl_trip_stop_purge_set.DELETE;
259 l_tbl_container_purge_set.DELETE;
260
261 IF l_debug_on THEN
262 WSH_DEBUG_SV.pop(l_module_name);
263 END IF;
264
265 END Process_Purge;
266
267
268 /*-----------------------------------------------------------------------------
269 Procedure: Get_Purge_Set
270 Parameters: p_source_system Only the delivery details belonging to this Source System
271 would be considered eligible for Purge
272 p_ship_from_org Only the deliveries belonging to this Ship From Org
273 would be considered eligible for Purge
274 p_order_number_from Only the delivery details having source_header_number
275 greater than Order Number From would be considered eligible for Purge
276 p_order_number_to Only the delivery details having source_header_number
277 less than Order Number To would be considered eligible for Purge
278 p_order_type Only the delivery details belonging to this Order Type
279 would be considered eligible for Purge
280 p_ship_date_from Only the deliveries having initial_pickup_date greater
281 than Ship Date From would be considered eligible for Purge
282 p_ship_date_to Only the deliveries having initial_pickup_date less than
283 Ship Date To would be considered eligible for Purge
284 p_delete_beyond_x_ship_days Only the deliveries having initial_pickup_date greater
285 than the specified date would be considered eligible for Purge
286 p_purge_intransit_trips Decides whether to purge In Transit Trips or not
287 p_delete_empty_records Decides whether to delete empty record or not.
288 The empty records can be Empty Trips, Orphaned Empty Deliveries,
289 Delivery with Empty containers, Empty Containers
290 p_create_date_from Only Empty records having creation_date greater than this
291 date would be purged
292 p_create_date_to Only Empty records having creation_date less than this
293 date would be purged
294 p_del_beyond_creation_days Only Empty records having creation_date greater than
295 this date would be purged
296 x_tbl_trip_purge_set - pl/sql table of trip id's eligible for purge
297 x_tbl_delivery_purge_set - pl/sql table of delivery id's eligible for purge
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
307 ==============================================================================
308 Input: Parameters as given in the FDD.
309 Output: Table of Record Types for Trips, Stops, Legs,
310 Deliveries, Containers, Details
311 ================================================================================
312 Logic: i) Build Trip SQL for purge set types:
313 NON_EMPTY - Complete Shipping Entities
314
315 ii) Build Delivery SQL for purge set type:
316 EMPTYDELS - Orphaned empty deliveries without any details
317
318 only Delete Empty Records, creation date from, to and delete beyond
319 x creation days will be honoured.
320
321 iii) Execute the Trip ,Delivery and Container dynamically built SQLs and populate the
322 Table of record types for trip,delivery and containers.
323
324 Add Trip IDs for purge set types :
325 EMPTYTRIPS - Orphaned Trips without any deliveries assigned to them
326
327 iv) For NON_EMPTY purge sets get the deliveries for all
328 trips and add delivery ids to the purge set for deliveries.
329
330 v) For a given trip get all the stops and create a stops purge set.
331
332 vi) For a given delivery get all the delivery legs and details and create
333 a purge set.
334
335 vii) From NON_EMPTY get all the container ids(wdd.container_flag='Y') and
336 populate them in container purge set
337 -----------------------------------------------------------------------------*/
338
339 PROCEDURE Get_Purge_Set(p_source_system varchar2,
340 p_ship_from_org number,
341 p_order_number_from varchar2,
342 p_order_number_to varchar2,
343 p_order_type number,
344 p_ship_date_from varchar2,
345 p_ship_date_to varchar2,
346 p_delete_beyond_x_ship_days number,
347 p_purge_intransit_trips varchar2,
348 p_delete_empty_records varchar2,
349 p_create_date_from varchar2,
350 p_create_date_to varchar2,
351 p_del_beyond_creation_days number,
352 x_tbl_trip_purge_set OUT NOCOPY Trip_ID_Tbl_Type ,
353 x_tbl_delivery_purge_set OUT NOCOPY Delivery_ID_Tbl_Type,
354 x_tbl_del_details_purge_set OUT NOCOPY Del_Detail_ID_Tbl_Type,
355 x_tbl_del_legs_purge_set OUT NOCOPY Del_Leg_ID_Tbl_Type,
356 x_tbl_trip_stops_purge_set OUT NOCOPY Trip_Stop_ID_Tbl_Type,
357 x_tbl_containers_purge_set OUT NOCOPY Container_ID_Tbl_Type,
358 x_return_status OUT NOCOPY VARCHAR2
359 )IS
360
361 l_debug_on BOOLEAN;
362 l_return_status VARCHAR2(1);
363
364 trip_sql VARCHAR2(4000);
365 delivery_sql VARCHAR2(4000);
366 empty_trip_sql VARCHAR2(4000);
367 empty_container_sql VARCHAR2(4000);
368 --Variable added for bug 12397111
369 cancel_wdd_sql VARCHAR2(4000);
370 l_delivery_detail_id Del_Detail_ID_Tbl_Type;
371
372
373
374 l_source_system VARCHAR2(20);
375 l_loop_index NUMBER;
376 i number;
377
378 l_trip_id NUMBER;
379 l_delivery_id NUMBER;
380 l_stop_id NUMBER;
381 l_leg_id NUMBER;
382 l_detail_id NUMBER;
383 l_container_id NUMBER;
384 l_container_flag VARCHAR2(1);
385
386 l_trip_name VARCHAR2(30);
387 l_delivery_name VARCHAR2(30);
388
389 l_trip_purge_row Trip_ID_Rec_Type;
390 l_del_purge_row Delivery_ID_Rec_Type;
391
392 l_tbl_trip_purge_set Trip_ID_Tbl_Type;
393
394 TYPE PurgeCurType IS REF CURSOR;
395 c_trip_purge_cur PurgeCurType;
396 c_del_purge_cur PurgeCurType;
397 c_empty_trip_cur PurgeCurType;
398 c_empty_containers PurgeCurType;
399 --Cursor variable added for bug 12397111
400 c_wdd_purge_cur PurgeCurType;
401
402 CURSOR c_dels_for_trip(p_tripid NUMBER) IS
403 SELECT distinct wnd.delivery_id, wnd.name
404 FROM wsh_trips wt,
405 wsh_trip_stops wts,
406 wsh_delivery_legs wdl,
407 wsh_new_deliveries wnd
408 WHERE wt.trip_id = wts.trip_id
409 AND wts.stop_id = wdl.pick_up_stop_id
410 AND wdl.delivery_id = wnd.delivery_id
411 AND wts.trip_id = p_tripid;
412
413 CURSOR c_stops_for_trip(p_tripid NUMBER) IS
414 SELECT stop_id
415 FROM wsh_trip_stops
416 WHERE trip_id= p_tripid;
417
418 CURSOR c_legs_for_del(p_deliveryid NUMBER) IS
419 SELECT delivery_leg_id
420 FROM wsh_delivery_legs
421 WHERE delivery_id = p_deliveryid;
422
423 --Bug 12397111 : Added union to fetch cancelled delivery lines while purging
424 CURSOR c_details_for_del(p_deliveryid NUMBER) IS
425 SELECT wda.delivery_detail_id,
426 wdd.container_flag
427 FROM wsh_delivery_assignments_v wda,
428 wsh_delivery_details wdd
429 WHERE wda.delivery_id = p_deliveryid
430 AND wda.delivery_detail_id = wdd.delivery_detail_id
431 UNION
432 SELECT wdd_can.delivery_detail_id,
433 'N'
434 FROM wsh_delivery_assignments_v wda,
435 wsh_delivery_details wdd,
436 wsh_delivery_details wdd_can
437 WHERE wda.delivery_id = p_deliveryid
438 AND wda.delivery_detail_id = wdd.delivery_detail_id
439 AND wdd_can.source_line_id = wdd.source_line_id
440 AND wdd.source_code = 'OE'
441 AND wdd_can.source_code = 'OE'
442 AND wdd_can.released_status = 'D';
443
444
445 --cursor to select empty trips would come from dynamic sql
446 --cursor to select empty dels would come from dynamic sql
447 --(based on create date from and to)
448
449 /*
450 --cursor to select orphaned deliveries with only empty containers
451 CURSOR c_delivery_empty_containers IS
452 SELECT wnd.delivery_id
453 --wda.delivery_detail_id
454 FROM wsh_new_deliveries wnd,
455 wsh_delivery_assignments_v wda,
456 wsh_delivery_legs wdl
457 WHERE wnd.delivery_id = wda.delivery_id
458 AND wnd.delivery_id = wdl.delivery_id(+)
459 AND wdl.delivery_leg_id IS NULL
460 AND NOT EXISTS (
461 SELECT 1
462 FROM wsh_delivery_details wdd,
463 wsh_delivery_assignments_v wda1
464 WHERE wdd.delivery_Detail_id = wda1.delivery_detail_id
465 AND wda1.delivery_id = wnd.delivery_id
466 AND wdd.container_flag = 'N'
467 ) ;
468 */
469
470 --cursor to select orphaned empty containers
471 /* CURSOR c_empty_containers IS
472 SELECT
473 wdd.delivery_detail_id,
474 'EMPTYLPNS'
475 --wdd.container_name dd_lpn_number,
476 FROM
477 wsh_delivery_assignments_v wda,
478 wsh_delivery_details wdd
479 WHERE
480 wda.delivery_detail_id = wdd.delivery_detail_id AND
481 wdd.container_flag = 'Y'AND
482 wda.delivery_id IS NULL AND
483 NOT EXISTS (
484 SELECT 1
485 FROM
486 wsh_delivery_assignments_v wda2
487 WHERE
488 wda2.parent_delivery_detail_id = wda.delivery_detail_id
489 ) ;
490 */
491
492 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_PURGE_SET';
493
494 BEGIN
495
496 -- Debug Statements
497 --
498 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
499 --
500 IF l_debug_on IS NULL THEN
501 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
502 END IF;
503
504 IF l_debug_on THEN
505 WSH_DEBUG_SV.push(l_module_name);
506 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_SYSTEM',p_source_system);
507 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_ORG',p_ship_from_org);
508 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER_FROM',p_order_number_from);
509 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER_To',p_order_number_to);
510 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_TYPE',p_order_type);
511 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE_FROM',p_ship_date_from);
512 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE_TO',p_ship_date_to);
513 WSH_DEBUG_SV.log(l_module_name,'P_DELETE_BEYOND_X_SHIP_DAYS',p_delete_beyond_x_ship_days);
514 WSH_DEBUG_SV.log(l_module_name,'P_PURGE_INTRANSIT_TRIPS',p_purge_intransit_trips);
515 WSH_DEBUG_SV.log(l_module_name,'P_DELETE_EMPTY_Records',p_delete_empty_records);
516 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_DATE_FROM',p_create_date_from);
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
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;
534
535 trip_sql :=trip_sql ||'WHERE ';
536
537 IF (p_ship_from_org IS NOT NULL) THEN
538 trip_sql := trip_sql || '
539 inner.del_ship_from_org = '
540 || p_ship_from_org || 'AND ' ;
541 END IF;
542
543 --added TRUNC function for bug 12605679
544 IF (p_ship_date_from IS NOT NULL) THEN
545 trip_sql := trip_sql ||
546 ' TRUNC(inner.del_pickup_date) BETWEEN ''' || FND_DATE.CANONICAL_TO_DATE(p_ship_date_from) ||
547 ''' AND ''' || FND_DATE.CANONICAL_TO_DATE(p_ship_date_to) || '''';
548 ELSE
549 trip_sql := trip_sql ||
550 ' inner.del_pickup_date < '''
551 || to_date(SYSDATE - p_delete_beyond_x_ship_days,'DD-MM-YYYY') ||'''' ;
552 END IF;
553 --RTV changes
554 IF p_source_system IS NULL THEN
555 RETURN;
556 END IF;
557 IF p_source_system = 'WSH' THEN
558 trip_sql := trip_sql || ' AND inner.dd_source_code = ''WSH'' ' ;
559 ELSIF p_source_system <> 'ALL' THEN
560 trip_sql := trip_sql || ' AND inner.dd_source_code IN ('''||p_source_system||''',''WSH'') ' ;
561 END IF;
562
563 /*
564 IF (p_source_system = 'ALL') THEN
565 trip_sql := trip_sql || '
566 AND inner.dd_source_code IN (''OE'',''PO'',''WSH'') ' ;
567 ELSIF (p_source_system = 'OE') THEN
568 trip_sql := trip_sql || '
569 AND inner.dd_source_code IN (''OE'',''WSH'') ' ;
570 ELSIF (p_source_system = 'PO') THEN
571 trip_sql := trip_sql || '
572 AND inner.dd_source_code IN (''PO'',''WSH'') ' ;
573 ELSIF (p_source_system = 'WSH') THEN
574 trip_sql := trip_sql || '
575 AND inner.dd_source_code = ''WSH'' ' ;
576 END IF;
577 */
578 --RTV changes
579 --check to take order type
580 IF (p_order_type IS NOT NULL) THEN
581 trip_sql := trip_sql ||
582 ' AND inner.dd_source_header_type_id = '
583 || p_order_type ;
584 END IF; --end check to take order type
585
586 --check to take order number range
587 IF (p_order_number_from IS NOT NULL) THEN
588 trip_sql := trip_sql ||
589 ' AND inner.dd_source_header_number BETWEEN '
590 || p_order_number_from ||
591 ' AND ' || p_order_number_to || ' ' ;
592 END IF;--end check to take order number range
593 --RTV changes Exclude the OKE lines since they are not eligible to be purged and we cannot purge it
594 trip_sql := trip_sql || ' AND inner.dd_source_code <> ''OKE'' ' ;
595
596 --check for deleting Intransit Trips
597 IF (p_purge_intransit_trips = 'Y') THEN
598 trip_sql := trip_sql ||
599 ' AND inner.trip_status IN (''CL'',''IT'') ' ;
600 ELSE
601 trip_sql := trip_sql ||
602 ' AND inner.trip_status IN (''CL'') ' ;
603 END IF;--end check for deleting Intransit Trips
604 --RTV changes
605 trip_sql := trip_sql ||
606 ' AND DECODE(inner.dd_source_code,
607 ''OE'',(DECODE((SELECT count(oe.order_number)
608 FROM oe_order_headers_all oe
609 WHERE oe.header_id= inner.dd_source_header_id),0,''FALSE'',''TRUE'')),
610 ''PO'',( DECODE((SELECT count(po.po_header_id)
611 FROM po_headers_all po
612 WHERE po.po_header_id= inner.dd_source_header_id),0,''FALSE'',''TRUE'')),
613 ''RTV'',(Decode(inner.dd_po_shipment_line_id,NULL,
614 decode((select count(mmt.transaction_id)
615 from mtl_material_transactions mmt
616 WHERE mmt.picking_line_id = inner.dd_id
617 ),0,''FALSE'',''TRUE''
618 ),
619 decode((select count(rt.interface_source_line_id)
620 from rcv_transactions rt
621 WHERE rt.interface_source_line_id= inner.dd_id
622 ),0,''FALSE'',''TRUE''
623 )
624 )
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'',
634 ''OBSOLETE'' ,''Y'',
635 NULL, ''Y'',
636 ''N'') = ''Y''
637 ';
638 END IF;--end check whether FTE is installed
639 --end construct trip_sql
640
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
650 WHERE
651 dd.delivery_detail_id = inner.dd_id AND
652 (
653 Nvl(dd.released_status,''N'') NOT IN (''C'',''L'') OR
654 dd.oe_interfaced_flag IN (''N'',''P'') OR
655 dd.inv_interfaced_flag IN (''N'',''P'')
656 )
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
666 --end of trip_sql construct
667 IF l_debug_on THEN
668 WSH_DEBUG_SV.log(l_module_name,'trip_sql ==>',trip_sql);
669 END IF;
670 --check whether to delete empty records
671 IF (p_delete_empty_records ='Y') THEN
672 --construct delivery_sql for empty deliveries
673 delivery_sql := 'SELECT
674 wnd.delivery_id,wnd.name
675 FROM
676 wsh_new_deliveries wnd,
677 wsh_delivery_assignments_v wda,
678 wsh_delivery_legs wdl
679 WHERE
680 wda.delivery_id(+) = wnd.delivery_id AND
681 wnd.delivery_id = wdl.delivery_id(+) AND
682 wdl.delivery_leg_id IS NULL AND
683 wda.delivery_detail_id IS NULL ' ;
684
685 --check for taking creation dates
686 --added TRUNC function for bug 12605679
687 IF (p_create_date_from IS NOT NULL) THEN
688 delivery_sql := delivery_sql || '
689 AND TRUNC(wnd.creation_date)
690 BETWEEN '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_from) || '''
691 AND '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_to) || '''' ;
692 ELSE
693 delivery_sql := delivery_sql || '
694 AND wnd.creation_date < '''
695 || to_date(SYSDATE - p_del_beyond_creation_days,'DD-MM-YYYY') ||'''' ;
696 END IF; --end check for taking creation dates
697
698 --Bugfix 14578425 Start
699 --check for organization
700 IF (p_ship_from_org IS NOT NULL) THEN
701 delivery_sql := delivery_sql || '
702 AND wnd.organization_id ='|| p_ship_from_org ;
703 END IF; --end check for organization
704 --Bugfix 14578425 End
705
706 delivery_sql := delivery_sql || '
707 ORDER BY wnd.name ' ;
708 --end construct delivery_sql
709
710 --construct SQL for empty trips
711 empty_trip_sql := ' SELECT distinct wt.trip_id, wt.name
712 FROM wsh_trips wt,
713 wsh_trip_stops wts,
714 wsh_delivery_legs wdl1,
715 wsh_delivery_legs wdl2
716 WHERE ';
717
718 --check for taking creation dates
719 --added TRUNC function for bug 12605679
720 IF (p_create_date_from IS NOT NULL) THEN
721 empty_trip_sql := empty_trip_sql || '
722 TRUNC(wt.creation_date)
723 BETWEEN '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_from) || '''
724 AND '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_to) || '''' ;
725 ELSE
726 empty_trip_sql := empty_trip_sql || '
727 wt.creation_date < '''
728 || to_date(SYSDATE - p_del_beyond_creation_days,'DD-MM-YYYY') ||'''' ;
729 END IF; --end check for taking creation dates
730
731 empty_trip_sql := empty_trip_sql || '
732 AND wt.trip_id = wts.trip_id(+)
733 AND wdl1.pick_up_stop_id(+) = wts.stop_id
734 AND wdl2.drop_off_stop_id(+) = wts.stop_id
735 AND wdl1.delivery_leg_id IS NULL
736 AND wdl2.delivery_leg_id IS NULL
737 AND NOT EXISTS
738 ( SELECT 1
739 FROM wsh_trip_stops wtss,
740 wsh_delivery_legs wdl1s
741 WHERE wtss.trip_id = wt.trip_id
742 AND wdl1s.pick_up_stop_id = wtss.stop_id
743 )' ;
744 --end contructing SQL for empty trips
745
746 --construct SQL for empty containers
747 empty_container_sql := 'SELECT wdd.delivery_detail_id,
748 ''EMPTYLPNS''
749 --wdd.container_name dd_lpn_number,
750 FROM
751 wsh_delivery_assignments_v wda,
752 wsh_delivery_details wdd
753 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
754 AND wdd.container_flag = ''Y''
755 AND wda.delivery_id IS NULL ';
756
757 --check for taking creation dates
758 --added TRUNC function for bug 12605679
759 IF (p_create_date_from IS NOT NULL) THEN
760 empty_container_sql := empty_container_sql || '
761 AND TRUNC(wdd.creation_date)
762 BETWEEN '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_from) || '''
763 AND '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_to) || '''' ;
764 ELSE
765 empty_container_sql := empty_container_sql || '
766 AND wdd.creation_date < '''
767 || to_date(SYSDATE - p_del_beyond_creation_days,'DD-MM-YYYY') ||'''' ;
768 END IF; --end check for taking creation dates
769
770 empty_container_sql := empty_container_sql || '
771 AND NOT EXISTS (
772 SELECT 1
773 FROM
774 wsh_delivery_assignments_v wda2
775 WHERE
776 wda2.parent_delivery_detail_id = wda.delivery_detail_id
777 ) ' ;
778
779 END IF; --end check whether to delete empty records
780
781 --fetch trip ids for non empty trips and insert into PL/SQL table
782 OPEN c_trip_purge_cur FOR trip_sql;
783 FETCH c_trip_purge_cur BULK COLLECT into x_tbl_trip_purge_set;
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,
793 x_return_status => l_return_status );
794
795 x_tbl_trip_purge_set := l_tbl_trip_purge_set;
796
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;
806 RETURN;
807 END IF;
808 END IF;
809
810 --check whether to fetch empty records
811 IF (p_delete_empty_records ='Y') THEN
812 --fetch trip ids for empty trips and insert into PL/SQL table
813 OPEN c_empty_trip_cur FOR empty_trip_sql;
814 LOOP
815 FETCH c_empty_trip_cur into l_trip_id,l_trip_name;
816 EXIT WHEN c_empty_trip_cur%NOTFOUND;
817 x_tbl_trip_purge_set(x_tbl_trip_purge_set.COUNT+1).trip_id := l_trip_id;
818 x_tbl_trip_purge_set(x_tbl_trip_purge_set.COUNT).trip_name := l_trip_name;
819 x_tbl_trip_purge_set(x_tbl_trip_purge_set.COUNT).purge_set_type := 'EMPTYTRIPS';
820 END LOOP;
821 CLOSE c_empty_trip_cur;
822
823 --fetch delivery ids for empty deliveries
824 OPEN c_del_purge_cur FOR delivery_sql;
825 LOOP
826 FETCH c_del_purge_cur into l_delivery_id,l_delivery_name;
827 EXIT WHEN c_del_purge_cur%NOTFOUND;
828 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT+1).delivery_id := l_delivery_id;
829 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT).delivery_name := l_delivery_name;
830 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT).purge_set_type := 'EMPTYDELS';
831 END LOOP;
832 CLOSE c_del_purge_cur;
833
834 --fetch delivery ids for Deliveries having empty containers only
835 /* OPEN c_delivery_empty_containers;
836 LOOP
837 FETCH c_delivery_empty_containers into l_delivery_id;
838 EXIT WHEN c_delivery_empty_containers%NOTFOUND;
839 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT+1).delivery_id := l_delivery_id;
840 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT).purge_set_type := 'DEL_EMPTYLPNS';
841 END LOOP;
842 CLOSE c_delivery_empty_containers;
843 */
844 --fetch container id for orphaned empty containers
845 OPEN c_empty_containers FOR empty_container_sql;
846 FETCH c_empty_containers BULK COLLECT INTO x_tbl_containers_purge_set;
847 CLOSE c_empty_containers;
848 END IF; --end check whether to fetch empty records
849
850 --add the deliveries belonging to trips from the table x_tbl_trip_purge_set
851 --to the table x_delivery_tbl_trip_set
852 IF x_tbl_trip_purge_set.COUNT > 0 THEN
853 FOR l_loop_index in x_tbl_trip_purge_set.FIRST .. x_tbl_trip_purge_set.LAST
854 LOOP
855 l_trip_id := x_tbl_trip_purge_set(l_loop_index).trip_id;
856
857 IF (x_tbl_trip_purge_set(l_loop_index).purge_set_type = 'NON_EMPTY') THEN
858 OPEN c_dels_for_trip(l_trip_id);
859 LOOP
860 FETCH c_dels_for_trip into l_delivery_id,l_delivery_name;
861 EXIT WHEN c_dels_for_trip%NOTFOUND;
862 --x_tbl_delivery_purge_set.EXTEND;
863 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT+1).delivery_id := l_delivery_id;
864 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT).delivery_name := l_delivery_name;
865 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT).purge_set_type := 'NON_EMPTY';
866 END LOOP;
867 CLOSE c_dels_for_trip;
868 END IF;
869 --fetch trip stops for the trips
870 OPEN c_stops_for_trip(l_trip_id);
871 LOOP
872 FETCH c_stops_for_trip into l_stop_id;
873 EXIT WHEN c_stops_for_trip%NOTFOUND;
874 x_tbl_trip_stops_purge_set(x_tbl_trip_stops_purge_set.COUNT+1).stop_id := l_stop_id;
875 END LOOP;
876 CLOSE c_stops_for_trip;
877 END LOOP; -- end adding delivery ids to plsql table
878 END IF;
879
880 --fetch delivery legs and delivery details for Delivery Ids into PL/SQL table
881 IF x_tbl_delivery_purge_set.COUNT > 0 THEN
882 FOR l_loop_index in x_tbl_delivery_purge_set.FIRST .. x_tbl_delivery_purge_set.LAST
883 LOOP
884 l_delivery_id := x_tbl_delivery_purge_set(l_loop_index).delivery_id;
885 --fetch delivery legs
886 IF (x_tbl_delivery_purge_set(l_loop_index).purge_set_type = 'NON_EMPTY') THEN
887 OPEN c_legs_for_del(l_delivery_id);
888 LOOP
889 FETCH c_legs_for_del INTO l_leg_id;
890 EXIT WHEN c_legs_for_del%NOTFOUND;
891 x_tbl_del_legs_purge_set(x_tbl_del_legs_purge_set.COUNT+1).delivery_leg_id := l_leg_id;
892 END LOOP;
893 CLOSE c_legs_for_del;
894 END IF;
895 --fetch delivery details and containers for non empty deliveries
896 IF x_tbl_delivery_purge_set(l_loop_index).purge_set_type = 'NON_EMPTY' THEN
897 OPEN c_details_for_del(l_delivery_id);
898 LOOP
899 FETCH c_details_for_del INTO l_detail_id,l_container_flag;
900 EXIT WHEN c_details_for_del%NOTFOUND;
901 --l_container_flag = 'Y' would be Container
902 IF l_container_flag = 'N' THEN -- Delivery Detail
903 x_tbl_del_details_purge_set(x_tbl_del_details_purge_set.COUNT+1).delivery_detail_id := l_detail_id;
904 ELSE -- Container
905 x_tbl_containers_purge_set(x_tbl_containers_purge_set.COUNT+1).container_id := l_detail_id;
906 x_tbl_containers_purge_set(x_tbl_containers_purge_set.COUNT).purge_set_type := 'NON_EMPTY';
907 END IF;
908 END LOOP;
909 CLOSE c_details_for_del;
910 END IF;
911
912 --fetch empty containers for deliveries
913 /* IF x_tbl_delivery_purge_set(l_loop_index).purge_set_type = 'DEL_EMPTYLPNS' THEN
914 OPEN c_details_for_del(l_delivery_id);
915 LOOP
916 FETCH c_details_for_del INTO l_detail_id,l_container_flag;
917 EXIT WHEN c_details_for_del%NOTFOUND;
918 x_tbl_containers_purge_set(x_tbl_containers_purge_set.COUNT+1).container_id := l_detail_id;
919 x_tbl_containers_purge_set(x_tbl_containers_purge_set.COUNT).purge_set_type := 'EMPTYLPNS';
920 END LOOP;
921 CLOSE c_details_for_del;
922 END IF; */
923 END LOOP;
924 END IF;
925
926 --Bug 12397111 - Start
927 cancel_wdd_sql := 'SELECT wdd.delivery_detail_id
928 FROM wsh_delivery_details wdd
929 WHERE wdd.source_code = ''OE''
930 AND wdd.released_status = ''D''
931 AND NOT EXISTS
932 ( SELECT 1
933 FROM wsh_delivery_details det
934 WHERE det.source_code = ''OE''
935 AND det.source_line_id = wdd.source_line_id
936 AND det.released_status <> ''D'' )
937 AND NOT EXISTS
938 ( SELECT 1
939 FROM oe_order_headers_all oeh
940 WHERE oeh.header_id = wdd.source_header_id ) ';
941 --check for taking creation dates
942
943 IF (p_create_date_from IS NOT NULL) THEN
944 cancel_wdd_sql := cancel_wdd_sql || '
945 AND TRUNC(wdd.creation_date)
946 BETWEEN '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_from) || '''
947 AND '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_to) || '''' ;
948 ELSE
949 cancel_wdd_sql := cancel_wdd_sql || '
950 AND wdd.creation_date < '''
951 || to_date(SYSDATE - p_del_beyond_creation_days,'DD-MM-YYYY') ||'''' ;
952 END IF; --end check for taking creation dates
953
954 IF l_debug_on THEN
955 WSH_DEBUG_SV.log(l_module_name,'cancel_wdd_sql', cancel_wdd_sql);
956 END IF;
957
958 --fetch orphan canceled delivery detail ids
959 OPEN c_wdd_purge_cur FOR cancel_wdd_sql;
960 LOOP
961
962 FETCH c_wdd_purge_cur BULK COLLECT INTO l_delivery_detail_id LIMIT 5000;
963
964 EXIT WHEN l_delivery_detail_id.COUNT<=0;
965
966 FOR l_count in l_delivery_detail_id.FIRST..l_delivery_detail_id.LAST
967 LOOP
968 x_tbl_del_details_purge_set(x_tbl_del_details_purge_set.COUNT+1).delivery_detail_id := l_delivery_detail_id(l_count).delivery_detail_id;
969 END LOOP;
970
971 END LOOP;
972 CLOSE c_wdd_purge_cur;
973 --Bug 12397111 - Ends
974
975 IF l_debug_on THEN
976 WSH_DEBUG_SV.pop(l_module_name);
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');
986 END IF;
987
988 END Get_Purge_Set;
989
990
991 /*-----------------------------------------------------------------------------
992
993 Procedure: Purge_Entities
994 Parameters: p_tbl_trip_purge_set - pl/sql table of trip id's eligible for purge
995 p_tbl_delivery_purge_set - pl/sql table of delivery id's eligible for purge
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 =============================================================================
1005 Input: Table of Record Types for Trips, Stops, Legs, Deliveries, Containers, Details
1006 Output: Return Status - success or failure
1007 ==============================================================================
1008 Logic: i) Call Purge_Delivery_Details
1009 ii) Call Purge_Containers
1010 iii) Call Purge_Delivery_Legs
1011 iv) Call Purge_Trip_Stops
1012 v) Call Purge_Deliveries
1013 vi) Call Purge_Trips
1014 -----------------------------------------------------------------------------*/
1015
1016 PROCEDURE Purge_Entities( p_tbl_trip_purge_set Trip_ID_Tbl_Type ,
1017 p_tbl_delivery_purge_set Delivery_ID_Tbl_Type,
1018 p_tbl_del_details_purge_set Del_Detail_ID_Tbl_Type,
1019 p_tbl_del_legs_purge_set Del_Leg_ID_Tbl_Type,
1020 p_tbl_trip_stops_purge_set Trip_Stop_ID_Tbl_Type,
1021 p_tbl_containers_purge_set Container_ID_Tbl_Type,
1022 x_return_status OUT NOCOPY VARCHAR2
1023 )IS
1024 l_debug_on BOOLEAN;
1025 l_return_status VARCHAR2(1);
1026
1027 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_ENTITIES';
1028 BEGIN
1029
1030 -- Debug Statements
1031 --
1032 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1033 --
1034 IF l_debug_on IS NULL THEN
1035 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1036 END IF;
1037
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);
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);
1051
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;
1061 RETURN;
1062 END IF;
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);
1072
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;
1082 RETURN;
1083 END IF;
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);
1093
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;
1103 RETURN;
1104 END IF;
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);
1114
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;
1124 RETURN;
1125 END IF;
1126 END IF;
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,
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;
1143 RETURN;
1144 END IF;
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);
1154
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;
1164 RETURN;
1165 END IF;
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);
1175
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;
1185 RETURN;
1186 END IF;
1187 END IF;
1188
1189 IF l_debug_on THEN
1190 WSH_DEBUG_SV.pop(l_module_name);
1191 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');
1201 END IF;
1202
1203
1204 END Purge_Entities;
1205
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 ==============================================================================
1215 Input: Table of Record Types for Trips
1216 Output: Return Status - success or failure
1217 ==============================================================================
1218 Logic: i) Delete records from the following tables:
1219 WSH_EXCEPTIONS, WSH_FREIGHT_COSTS, WSH_DOCUMENT_INSTANCES,WSH_TRIPS
1220 -----------------------------------------------------------------------------*/
1221
1222 PROCEDURE Purge_Trips( p_tbl_trip_purge_set Trip_ID_Tbl_Type,
1223 x_return_status OUT NOCOPY VARCHAR2
1224 )IS
1225
1226 l_debug_on BOOLEAN;
1227 l_loop_index NUMBER;
1228 l_trip_id NUMBER;
1229
1230 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_TRIPS';
1231 BEGIN
1232
1233 -- Debug Statements
1234 --
1235 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1236 --
1237 IF l_debug_on IS NULL THEN
1238 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1239 END IF;
1240
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;
1250
1251 DELETE
1252 FROM wsh_exceptions
1253 WHERE trip_id = l_trip_id;
1254
1255 IF SQL%FOUND THEN
1256 IF l_debug_on THEN
1257 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1258 END IF;
1259 END IF;
1260
1261 DELETE
1262 FROM wsh_freight_costs
1263 WHERE trip_id = l_trip_id;
1264
1265 IF SQL%FOUND THEN
1266 IF l_debug_on THEN
1267 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1268 END IF;
1269 END IF;
1270
1271 DELETE
1272 FROM wsh_document_instances
1273 WHERE entity_id = l_trip_id
1274 AND entity_name = 'WSH_TRIPS';
1275
1276 IF SQL%FOUND THEN
1277 IF l_debug_on THEN
1278 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_document_instances: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1279 END IF;
1280 END IF;
1281
1282 DELETE
1283 FROM wsh_trips
1284 WHERE trip_id = l_trip_id;
1285
1286 IF SQL%FOUND THEN
1287 IF l_debug_on THEN
1288 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_trips: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1289 END IF;
1290 END IF;
1291
1292 END LOOP;
1293
1294 IF l_debug_on THEN
1295 WSH_DEBUG_SV.pop(l_module_name);
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');
1305 END IF;
1306
1307 END Purge_Trips;
1308
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 =============================================================================+
1318 Input: Table of Record Types for Deliveries
1319 Output: Return Status - success or failure
1320 ==============================================================================
1321 Logic: i) Delete records from the following tables:
1322 WSH_EXCEPTIONS, WSH_TRANSACTIONS_HISTORY, WSH_DOCUMENT_INSTANCES,
1323 WSH_FREIGHT_COSTS
1324
1325 If FTE is installed,
1326 FTE_SHIPMENT_STATUS_DETAILS, FTE_SHIPMENT_STATUS_EXCEPTIONS,
1327 FTE_MESSAGE_PARTNER, FTE_MESSAGE_CONTACT, FTE_MESSAGE_LOCATION,
1328 FTE_DELIVERY_PROOF, FTE_SHIPMENT_STATUS_HEADERS
1329
1330 If ITM Screening is done,
1331 WSH_ITM_RESPONSE_LINES, WSH_ITM_RESPONSE_HEADERS, WSH_ITM_REQUEST_CONTROL,
1332 WSH_INBOUND_TXN_HISTORY
1333 and finally WSH_NEW_DELIVERIES.
1334 -----------------------------------------------------------------------------*/
1335
1336 PROCEDURE Purge_Deliveries( p_tbl_delivery_purge_set Delivery_ID_Tbl_Type,
1337 x_return_status OUT NOCOPY VARCHAR2
1338 )IS
1339 l_debug_on BOOLEAN;
1340 l_loop_index NUMBER;
1341 l_delivery_id NUMBER;
1342
1343 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_DELIVERIES';
1344 BEGIN
1345
1346 -- Debug Statements
1347 --
1348 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1349 --
1350 IF l_debug_on IS NULL THEN
1351 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1352 END IF;
1353
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
1363 l_delivery_id := p_tbl_delivery_purge_set(l_loop_index).delivery_id;
1364
1365 DELETE
1366 FROM wsh_exceptions
1367 WHERE delivery_id = l_delivery_id;
1368
1369 IF SQL%FOUND THEN
1370 IF l_debug_on THEN
1371 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1372 END IF;
1373 END IF;
1374
1375 DELETE
1376 FROM wsh_transactions_history
1377 WHERE entity_number = to_char(l_delivery_id)
1378 AND entity_type = 'DLVY';
1379
1380 IF SQL%FOUND THEN
1381 IF l_debug_on THEN
1382 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_transactions_history: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1383 END IF;
1384 END IF;
1385
1386 DELETE
1387 FROM wsh_freight_costs
1388 WHERE delivery_id = l_delivery_id;
1389
1390 IF SQL%FOUND THEN
1391 IF l_debug_on THEN
1392 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1393 END IF;
1394 END IF;
1395
1396 DELETE
1397 FROM wsh_document_instances
1398 WHERE entity_id = l_delivery_id
1399 AND entity_name = 'WSH_NEW_DELIVERIES';
1400
1401 IF SQL%FOUND THEN
1402 IF l_debug_on THEN
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
1412 FROM fte_shipment_status_headers
1413 WHERE delivery_id = l_delivery_id);
1414
1415 IF SQL%FOUND THEN
1416 IF l_debug_on THEN
1417 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_shipment_status_details: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1418 END IF;
1419 END IF;
1420
1421 DELETE
1422 FROM fte_shipment_status_exceptions
1423 WHERE transaction_id IN (SELECT transaction_id
1424 FROM fte_shipment_status_headers
1425 WHERE delivery_id = l_delivery_id);
1426
1427 IF SQL%FOUND THEN
1428 IF l_debug_on THEN
1429 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_shipment_status_exceptions: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1430 END IF;
1431 END IF;
1432
1433 DELETE
1434 FROM fte_message_partner
1435 WHERE transaction_id IN (SELECT transaction_id
1436 FROM fte_shipment_status_headers
1437 WHERE delivery_id = l_delivery_id);
1438
1439 IF SQL%FOUND THEN
1440 IF l_debug_on THEN
1441 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_partner: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1442 END IF;
1443 END IF;
1444
1445 DELETE
1446 FROM fte_message_address
1447 WHERE transaction_id IN (SELECT transaction_id
1448 FROM fte_shipment_status_headers
1449 WHERE delivery_id = l_delivery_id);
1450
1451 IF SQL%FOUND THEN
1452 IF l_debug_on THEN
1453 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_address: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1454 END IF;
1455 END IF;
1456
1457 DELETE
1458 FROM fte_message_contact
1459 WHERE transaction_id IN (SELECT transaction_id
1460 FROM fte_shipment_status_headers
1461 WHERE delivery_id = l_delivery_id);
1462
1463 IF SQL%FOUND THEN
1464 IF l_debug_on THEN
1465 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_contact: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1466 END IF;
1467 END IF;
1468
1469 DELETE
1470 FROM fte_message_location
1471 WHERE transaction_id IN (SELECT transaction_id
1472 FROM fte_shipment_status_headers
1473 WHERE delivery_id = l_delivery_id);
1474
1475 IF SQL%FOUND THEN
1476 IF l_debug_on THEN
1477 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_location: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1478 END IF;
1479 END IF;
1480
1481
1482 DELETE
1483 FROM fte_delivery_proof
1484 WHERE transaction_id IN (SELECT transaction_id
1485 FROM fte_shipment_status_headers
1486 WHERE delivery_id = l_delivery_id);
1487
1488 IF SQL%FOUND THEN
1489 IF l_debug_on THEN
1490 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_delivery_proof: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1491 END IF;
1492 END IF;
1493
1494
1495 DELETE
1496 FROM fte_shipment_status_headers
1497 WHERE delivery_id = l_delivery_id;
1498
1499
1500 IF SQL%FOUND THEN
1501 IF l_debug_on THEN
1502 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_shipment_status_headers: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1503 END IF;
1504 END IF;
1505 END IF;
1506
1507 DELETE
1508 FROM wsh_itm_response_lines
1509 WHERE response_header_id IN ( SELECT wirh.response_header_id
1510 FROM wsh_itm_response_headers wirh,
1511 wsh_itm_request_control wirc
1512 WHERE wirc.original_system_reference = l_delivery_id
1513 AND wirc.request_control_id = wirh.request_control_id
1514 AND wirc.service_type_code = 'WSH_EXPORT_COMPLIANCE' );
1515
1516 IF SQL%FOUND THEN
1517 IF l_debug_on THEN
1518 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_itm_response_lines: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1519 END IF;
1520 END IF;
1521
1522 DELETE
1523 FROM wsh_itm_response_headers
1524 WHERE request_control_id IN ( SELECT request_control_id
1525 FROM wsh_itm_request_control
1526 WHERE original_system_reference = l_delivery_id
1527 AND service_type_code = 'WSH_EXPORT_COMPLIANCE' ) ;
1528
1529 IF SQL%FOUND THEN
1530 IF l_debug_on THEN
1531 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_itm_response_headers: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1532 END IF;
1533 END IF;
1534
1535 DELETE
1536 FROM wsh_itm_request_control
1537 WHERE original_system_reference = l_delivery_id
1538 AND service_type_code = 'WSH_EXPORT_COMPLIANCE';
1539
1540 IF SQL%FOUND THEN
1541 IF l_debug_on THEN
1542 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_itm_request_control: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1543 END IF;
1544 END IF;
1545
1546 DELETE
1547 FROM wsh_inbound_txn_history
1548 WHERE shipment_header_id IN ( SELECT rcv_shipment_header_id
1549 FROM wsh_new_deliveries
1550 WHERE delivery_id = l_delivery_id) ;
1551
1552 IF SQL%FOUND THEN
1553 IF l_debug_on THEN
1554 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_inbound_txn_history: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1555 END IF;
1556 END IF;
1557
1558 DELETE
1559 FROM wsh_new_deliveries
1560 WHERE delivery_id = l_delivery_id;
1561
1562 IF SQL%FOUND THEN
1563 IF l_debug_on THEN
1564 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_new_deliveries: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1565 END IF;
1566 END IF;
1567
1568 END LOOP;
1569
1570 IF l_debug_on THEN
1571 WSH_DEBUG_SV.pop(l_module_name);
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');
1581 END IF;
1582
1583 END Purge_Deliveries;
1584
1585
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 ==============================================================================
1595 Input: Table of Record Types for Trip Stops
1596 Output: Return Status - success or failure
1597 ==============================================================================
1598 Logic: i) Delete records from the following tables:
1599 WSH_EXCEPTIONS, WSH_FREIGHT_COSTS, WSH_TRIP_STOPS
1600 -----------------------------------------------------------------------------*/
1601
1602 PROCEDURE Purge_Trip_Stops( p_tbl_trip_stop_purge_set Trip_Stop_ID_Tbl_Type,
1603 x_return_status OUT NOCOPY VARCHAR2
1604 )IS
1605 l_debug_on BOOLEAN;
1606 l_loop_index NUMBER;
1607 l_stop_id NUMBER;
1608
1609 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_TRIP_STOPS';
1610 BEGIN
1611
1612 -- Debug Statements
1613 --
1614 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1615 --
1616 IF l_debug_on IS NULL THEN
1617 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1618 END IF;
1619
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;
1629
1630 DELETE
1631 FROM wsh_exceptions
1632 WHERE trip_stop_id = l_stop_id;
1633
1634 IF SQL%FOUND THEN
1635 IF l_debug_on THEN
1636 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: STOP_ID=' || l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1637 END IF;
1638 END IF;
1639
1640 DELETE
1641 FROM wsh_freight_costs
1642 WHERE stop_id = l_stop_id;
1643
1644 IF SQL%FOUND THEN
1645 IF l_debug_on THEN
1646 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: STOP_ID=' || l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1647 END IF;
1648 END IF;
1649
1650 DELETE
1651 FROM wsh_trip_stops
1652 WHERE stop_id = l_stop_id;
1653
1654 IF SQL%FOUND THEN
1655 IF l_debug_on THEN
1656 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_trip_stops: STOP_ID=' || l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1657 END IF;
1658 END IF;
1659
1660 END LOOP;
1661
1662 IF l_debug_on THEN
1663 WSH_DEBUG_SV.pop(l_module_name);
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');
1673 END IF;
1674
1675 END Purge_Trip_Stops;
1676
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 ==============================================================================
1686 Input: Table of Record Types for Delivery Legs
1687 Output: Return Status - success or failure
1688 ==============================================================================
1689 Logic: i) Delete records from the following tables:
1690 WSH_FREIGHT_COSTS, WSH_DOCUMENT_INSTANCES, WSH_DELIVERY_LEG_ACTIVITIES,
1691 WSH_DELIVERY_LEG_DETAILS, WSH_DELIVERY_LEGS
1692
1693 If FTE is installed,
1694 FTE_INVOICE_LINES, FTE_INVOICE_HISTORY, FTE_INVOICE_HEADERS,
1695 FTE_FAILURE_REASONS
1696 -----------------------------------------------------------------------------*/
1697
1698 PROCEDURE Purge_Delivery_Legs( p_tbl_del_leg_purge_set Del_Leg_ID_Tbl_Type,
1699 x_return_status OUT NOCOPY VARCHAR2
1700 )IS
1701 l_debug_on BOOLEAN;
1702 l_loop_index NUMBER;
1703 l_leg_id NUMBER;
1704
1705 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_DELIVERY_LEGS';
1706 BEGIN
1707 -- Debug Statements
1708 --
1709 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1710 --
1711 IF l_debug_on IS NULL THEN
1712 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1713 END IF;
1714
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;
1724
1725 DELETE
1726 FROM wsh_freight_costs
1727 WHERE delivery_leg_id = l_leg_id;
1728
1729 IF SQL%FOUND THEN
1730 IF l_debug_on THEN
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
1740 FROM fte_invoice_headers fih,
1741 wsh_document_instances wdi
1742 WHERE wdi.entity_id = l_leg_id
1743 AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
1744 AND wdi.sequence_number = fih.bol
1745 ) ;
1746 IF SQL%FOUND THEN
1747 IF l_debug_on THEN
1748 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_invoice_lines: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1749 END IF;
1750 END IF;
1751
1752 DELETE
1753 FROM fte_invoice_headers
1754 WHERE bol IN (SELECT sequence_number
1755 FROM wsh_document_instances
1756 WHERE entity_id = l_leg_id
1757 AND entity_name = 'WSH_DELIVERY_LEGS') ;
1758
1759 IF SQL%FOUND THEN
1760 IF l_debug_on THEN
1761 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_invoice_headers: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1762 END IF;
1763 END IF;
1764
1765 DELETE
1766 FROM fte_invoice_history
1767 WHERE bol IN (SELECT sequence_number
1768 FROM wsh_document_instances
1769 WHERE entity_id = l_leg_id
1770 AND entity_name = 'WSH_DELIVERY_LEGS') ;
1771
1772 IF SQL%FOUND THEN
1773 IF l_debug_on THEN
1774 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_invoice_history: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1775 END IF;
1776 END IF;
1777
1778 DELETE
1779 FROM fte_failure_reasons
1780 WHERE bol IN ( SELECT sequence_number
1781 FROM wsh_document_instances
1782 WHERE entity_id = l_leg_id
1783 AND entity_name = 'WSH_DELIVERY_LEGS') ;
1784
1785 IF SQL%FOUND THEN
1786 IF l_debug_on THEN
1787 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_failure_reasons: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1788 END IF;
1789 END IF;
1790
1791 END IF;
1792
1793 DELETE
1794 FROM wsh_document_instances
1795 WHERE entity_id = l_leg_id
1796 AND entity_name = 'WSH_DELIVERY_LEGS';
1797
1798 IF SQL%FOUND THEN
1799 IF l_debug_on THEN
1800 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_document_instances: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1801 END IF;
1802 END IF;
1803
1804 DELETE
1805 FROM wsh_delivery_leg_activities
1806 WHERE delivery_leg_id = l_leg_id ;
1807
1808 IF SQL%FOUND THEN
1809 IF l_debug_on THEN
1810 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_leg_activities: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1811 END IF;
1812 END IF;
1813
1814 DELETE
1815 FROM wsh_delivery_leg_details
1816 WHERE delivery_leg_id = l_leg_id;
1817
1818 IF SQL%FOUND THEN
1819 IF l_debug_on THEN
1820 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_leg_details: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1821 END IF;
1822 END IF;
1823
1824
1825 DELETE
1826 FROM wsh_delivery_legs
1827 WHERE delivery_leg_id = l_leg_id ;
1828
1829 IF SQL%FOUND THEN
1830 IF l_debug_on THEN
1831 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_legs: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1832 END IF;
1833 END IF;
1834 END LOOP;
1835
1836 IF l_debug_on THEN
1837 WSH_DEBUG_SV.pop(l_module_name);
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');
1847 END IF;
1848
1849 END Purge_Delivery_Legs;
1850
1851
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 =============================================================================
1861 Input: Table of Record Types for Delivery Details
1862 Output: Return Status - success or failure
1863 ==============================================================================
1864 Logic: i) Delete records from the following tables:
1865 WSH_FREIGHT_COSTS, WSH_SERIAL_NUMBERS, WSH_EXCEPTIONS,
1866 wsh_delivery_assignments_v, WSH_DELIVERY_DETAILS
1867 -----------------------------------------------------------------------------*/
1868 PROCEDURE Purge_Delivery_Details(p_tbl_del_detail_purge_set Del_Detail_ID_Tbl_Type,
1869 x_return_status OUT NOCOPY VARCHAR2
1870 )IS
1871 l_debug_on BOOLEAN;
1872 l_loop_index NUMBER;
1873 l_detail_id NUMBER;
1874
1875 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_DELIVERY_DETAILS';
1876 BEGIN
1877 -- Debug Statements
1878 --
1879 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1880 --
1881 IF l_debug_on IS NULL THEN
1882 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1883 END IF;
1884
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;
1894
1895 DELETE
1896 FROM wsh_serial_numbers
1897 WHERE delivery_detail_id = l_detail_id;
1898
1899 IF SQL%FOUND THEN
1900 IF l_debug_on THEN
1901 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_serial_numbers: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1902 END IF;
1903 END IF;
1904
1905 DELETE
1906 FROM wsh_exceptions
1907 WHERE delivery_detail_id = l_detail_id;
1908
1909 IF SQL%FOUND THEN
1910 IF l_debug_on THEN
1911 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1912 END IF;
1913 END IF;
1914
1915 DELETE
1916 FROM wsh_freight_costs
1917 WHERE delivery_detail_id = l_detail_id;
1918
1919 IF SQL%FOUND THEN
1920 IF l_debug_on THEN
1921 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1922 END IF;
1923 END IF;
1924
1925 DELETE
1926 FROM wsh_delivery_assignments_v
1927 WHERE delivery_detail_id = l_detail_id;
1928
1929 IF SQL%FOUND THEN
1930 IF l_debug_on THEN
1931 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_assignments_v: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1932 END IF;
1933 END IF;
1934
1935 DELETE
1936 FROM wsh_delivery_details
1937 WHERE delivery_detail_id = l_detail_id;
1938
1939 IF SQL%FOUND THEN
1940 IF l_debug_on THEN
1941 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_details: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1942 END IF;
1943 END IF;
1944 END LOOP;
1945
1946 IF l_debug_on THEN
1947 WSH_DEBUG_SV.pop(l_module_name);
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');
1957 END IF;
1958
1959 END Purge_Delivery_Details;
1960
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 ==============================================================================
1970 Input: Table of Record Types for Container Ids
1971 Output: Return Status - success or failure
1972 ==============================================================================
1973 Logic: i) Delete records from the following tables:
1974 wsh_delivery_assignments_v, WSH_DELIVERY_DETAILS
1975 -----------------------------------------------------------------------------*/
1976 PROCEDURE Purge_Containers(p_tbl_containers_purge_set Container_ID_Tbl_Type,
1977 x_return_status OUT NOCOPY VARCHAR2
1978 )IS
1979 l_debug_on BOOLEAN;
1980 l_loop_index NUMBER;
1981 l_container_id NUMBER;
1982 l_lpn_id NUMBER;
1983 l_return_status VARCHAR2(1);
1984
1985 l_msg_count NUMBER;
1986 l_msg_data VARCHAR2(32767);
1987
1988 l_wms_lpn_record WMS_Data_Type_Definitions_PUB.LPNPurgeRecordType;
1989
1990 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_CONTAINERS';
1991 BEGIN
1992 -- Debug Statements
1993 --
1994 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1995 --
1996 IF l_debug_on IS NULL THEN
1997 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1998 END IF;
1999
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;
2009
2010
2011 DELETE
2012 FROM wsh_exceptions
2013 WHERE delivery_detail_id = l_container_id;
2014
2015 IF SQL%FOUND THEN
2016 IF l_debug_on THEN
2017 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);
2018 END IF;
2019 END IF;
2020
2021 DELETE
2022 FROM wsh_freight_costs
2023 WHERE delivery_detail_id = l_container_id;
2024
2025 IF SQL%FOUND THEN
2026 IF l_debug_on THEN
2027 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);
2028 END IF;
2029 END IF;
2030
2031 DELETE
2032 FROM wsh_delivery_assignments_v
2033 WHERE delivery_detail_id = l_container_id;
2034
2035 IF SQL%FOUND THEN
2036 IF l_debug_on THEN
2037 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_assignments_v: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);
2038 END IF;
2039 END IF;
2040
2041 DELETE
2042 FROM wsh_delivery_details
2043 WHERE delivery_detail_id = l_container_id
2044 RETURNING lpn_id INTO l_lpn_id;
2045
2046 IF SQL%FOUND THEN
2047 IF l_debug_on THEN
2048 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_details: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);
2049 END IF;
2050 END IF;
2051
2052 IF l_lpn_id IS NOT NULL THEN -- Populate the LPN IDs in the table
2053 l_wms_lpn_record.LPN_IDs(l_wms_lpn_record.LPN_IDs.COUNT+1) := l_lpn_id;
2054 END IF;
2055 END LOOP;
2056
2057 --Call the WMS API to DELETE the LPNs
2058 IF l_debug_on THEN
2059 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_CONTAINER_GRP.LPN_PURGE_ACTIONS', WSH_DEBUG_SV.C_PROC_LEVEL);
2060 END IF;
2061
2062 WMS_Container_GRP.LPN_Purge_Actions( p_api_version =>1.0,
2063 p_init_msg_list =>FND_API.G_TRUE,
2064 p_commit =>'FALSE',
2065 x_return_status =>l_return_status,
2066 x_msg_count =>l_msg_count,
2067 x_msg_data =>l_msg_data,
2068 p_caller =>'WSH',
2069 p_action =>WMS_Container_GRP.G_LPN_PURGE_ACTION_DELETE,
2070 p_lpn_purge_rec => l_wms_lpn_record
2071 );
2072
2073 IF l_debug_on THEN
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;
2083 RETURN;
2084 END IF;
2085
2086 IF l_debug_on THEN
2087 WSH_DEBUG_SV.pop(l_module_name);
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');
2097 END IF;
2098
2099 END Purge_Containers;
2100
2101 -----------------------------------------------------------------------------
2102 --
2103 -- Procedure: Generate_Purge_Report
2104 -- Parameters: p_execution_mode Specifies whether to Purge Data or View Purge Set
2105 -- p_source_system Only the delivery details belonging to this Source System
2106 -- would be considered eligible for Purge
2107 -- p_ship_from_org Only the deliveries belonging to this Ship From Org
2108 -- would be considered eligible for Purge
2109 -- p_order_number_from Only the delivery details having source_header_number
2110 -- greater than Order Number From would be considered eligible for Purge
2111 -- p_order_number_to Only the delivery details having source_header_number
2112 -- less than Order Number To would be considered eligible for Purge
2113 -- p_order_type Only the delivery details belonging to this Order Type
2114 -- would be considered eligible for Purge
2115 -- p_ship_date_from Only the deliveries having initial_pickup_date greater
2116 -- than Ship Date From would be considered eligible for Purge
2117 -- p_ship_date_to Only the deliveries having initial_pickup_date less than
2118 -- Ship Date To would be considered eligible for Purge
2119 -- p_delete_beyond_x_ship_days Only the deliveries having initial_pickup_date greater
2120 -- than the specified date would be considered eligible for Purge
2121 -- p_purge_intransit_trips Decides whether to purge In Transit Trips or not
2122 -- p_delete_empty_records Decides whether to delete empty record or not.
2123 -- The empty records can be Empty Trips, Orphaned Empty Deliveries,
2124 -- Delivery with Empty containers, Empty Containers
2125 -- p_create_date_from Only Empty records having creation_date greater than this
2126 -- date would be purged
2127 -- p_create_date_to Only Empty records having creation_date less than this
2128 -- date would be purged
2129 -- p_del_beyond_creation_days Only Empty records having creation_date greater than
2130 -- this date would be purged
2131 -- p_sort_per_criteria Sorts the report output according to Trip,
2132 -- Delivery or Order Number
2133 -- p_print_detail If Low, the report would contain the parameters / summary
2134 -- page and all detail pages with Trips, Deliveries and
2135 -- Sales Orders data eligible to purge or purged.
2136 -- If No, the report would contain only the parameters / summary page.
2137 -- p_tbl_trip_purge_set pl/sql table of trip id's eligible for purge
2138 -- p_tbl_delivery_purge_set pl/sql table of delivery id's eligible for purge
2139 -- p_tbl_delivery_purge_set pl/sql table of container ids's eligible for purge
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
2149 -----------------------------------------------------------------------------
2150
2151 PROCEDURE Generate_Purge_Report(p_execution_mode varchar2,
2152 p_source_system varchar2,
2153 p_ship_from_org number,
2154 p_order_number_from varchar2,
2155 p_order_number_to varchar2,
2156 p_order_type number,
2157 p_ship_date_from varchar2,
2158 p_ship_date_to varchar2,
2159 p_delete_beyond_x_ship_days number,
2160 p_purge_intransit_trips varchar2,
2161 p_delete_empty_records varchar2,
2162 p_create_date_from varchar2,
2163 p_create_date_to varchar2,
2164 p_del_beyond_creation_days number,
2165 p_sort_per_criteria varchar2,
2166 p_print_detail varchar2,
2167 p_tbl_trip_purge_set Trip_ID_Tbl_Type ,
2168 p_tbl_delivery_purge_set Delivery_ID_Tbl_Type,
2169 p_tbl_container_purge_set Container_ID_Tbl_Type,
2170 p_count_legs NUMBER,
2171 p_count_stops NUMBER,
2172 p_count_details NUMBER,
2173 p_count_containers NUMBER,
2174 x_return_status OUT NOCOPY VARCHAR2
2175 )IS
2176 l_debug_on BOOLEAN;
2177
2178 l_trip_id NUMBER;
2179 l_delivery_id NUMBER;
2180 l_delivery_name VARCHAR2(30);
2181 l_trip_name VARCHAR2(30);
2182 l_sales_order VARCHAR2(150);
2183 l_bol_number NUMBER;
2184 l_container_id NUMBER;
2185 l_waybill VARCHAR2(30);
2186 l_gross_weight NUMBER;
2187 l_ship_to VARCHAR2(500);
2188 l_customer_name VARCHAR2(50);
2189 l_pickup_date DATE;
2190 l_dropoff_date DATE;
2191 l_ship_date DATE;
2192 l_order_type VARCHAR2(240);
2193 l_create_date DATE;
2194
2195 l_trip_index NUMBER;
2196 l_delivery_index NUMBER;
2197 l_sales_order_index NUMBER;
2198 l_container_index NUMBER;
2199
2200 l_nonempty_count NUMBER;
2201 l_lpn_count NUMBER;
2202 l_empty_trip_count NUMBER;
2203 l_empty_del_count NUMBER;
2204 l_empty_lpn_count NUMBER;
2205 l_buff_size NUMBER;
2206 l_src_meaning wsh_lookups.meaning%TYPE; --RTV changes
2207 l_err varchar2(500);
2208
2209 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GENERATE_PURGE_REPORT';
2210
2211 --Get Deliveries for Trips
2212 CURSOR c_dels_for_trip(p_tripid NUMBER) IS
2213 SELECT distinct del_id,
2214 del_name,
2215 del_waybill,
2216 del_gross_weight,
2217 del_ui_location_code,
2218 del_customer_name,
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
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
2234 --Added hints to the query for bug 4891951
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
2244 SELECT distinct trip_id,
2245 trip_name,
2246 del_id,
2247 del_name,
2248 del_waybill,
2249 del_gross_weight,
2250 del_ui_location_code,
2251 del_customer_name,
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
2261 SELECT distinct wda.parent_delivery_detail_id
2262 FROM wsh_delivery_assignments_v wda,
2263 wsh_delivery_details wdd
2264 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
2265 AND wdd.source_header_number = order_number
2266 AND wda.parent_delivery_detail_id IS NOT NULL;
2267 */
2268
2269 /*CURSOR c_containers_for_so(order_number varchar2) IS
2270 SELECT DISTINCT wda.parent_delivery_detail_id
2271 FROM wsh_delivery_assignments_v wda ,wsh_Delivery_Details wdd
2272 WHERE wda.parent_delivery_detail_id is not null
2273 AND wdd.delivery_Detail_id = wda.delivery_detail_id
2274 CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id
2275 START WITH wdd.source_header_number =order_number;
2276 */
2277
2278 CURSOR c_containers_for_so(p_order_number varchar2) IS
2279 SELECT DISTINCT wda.parent_delivery_detail_id
2280 FROM wsh_delivery_assignments_v wda
2281 WHERE wda.parent_delivery_detail_id is not null
2282 CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id
2283 START WITH wda.delivery_id IN (select wda1.delivery_id from
2284 wsh_delivery_assignments_v wda1, wsh_delivery_Details wdd
2285 WHERE wda1.delivery_Detail_id = wdd.delivery_Detail_id
2286 and wdd.source_header_number = p_order_number);
2287
2288 BEGIN
2289 -- Debug Statements
2290 --
2291 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2292 --
2293 IF l_debug_on IS NULL THEN
2294 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2295 END IF;
2296
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;
2306 l_empty_del_count := 0;
2307 l_empty_lpn_count := 0;
2308
2309 IF l_debug_on THEN
2310 WSH_DEBUG_SV.log(l_module_name,'P_EXECUTION_MODE',p_execution_mode);
2311 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_SYSTEM',p_source_system);
2312 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_ORG',p_ship_from_org);
2313 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER_FROM',p_order_number_from);
2314 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER_To',p_order_number_to);
2315 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_TYPE',p_order_type);
2316 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE_FROM',p_ship_date_from);
2317 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE_TO',p_ship_date_to);
2318 WSH_DEBUG_SV.log(l_module_name,'P_DELETE_BEYOND_X_SHIP_DAYS',p_delete_beyond_x_ship_days);
2319 WSH_DEBUG_SV.log(l_module_name,'P_PURGE_INTRANSIT_TRIPS',p_purge_intransit_trips);
2320 WSH_DEBUG_SV.log(l_module_name,'P_DELETE_EMPTY_Records',p_delete_empty_records);
2321 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_DATE_FROM',p_create_date_from);
2322 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_DATE_TO',p_create_date_to);
2323 WSH_DEBUG_SV.log(l_module_name,'P_DEL_BEYOND_CREATION_DAYS',p_del_beyond_creation_days);
2324 WSH_DEBUG_SV.log(l_module_name,'P_SORT_PER_CRITERIA',p_sort_per_criteria);
2325 WSH_DEBUG_SV.log(l_module_name,'P_PRINT_DETAIL',p_print_detail);
2326 WSH_DEBUG_SV.log(l_module_name,'P_COUNT_LEGS',p_count_legs);
2327 WSH_DEBUG_SV.log(l_module_name,'P_COUNT_STOPS',p_count_stops);
2328 WSH_DEBUG_SV.log(l_module_name,'P_COUNT_DETAILS',p_count_details);
2329 WSH_DEBUG_SV.log(l_module_name,'P_COUNT_ContainerS',p_count_containers);
2330 END IF;
2331
2332 -- FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<?xml version="1.0" ?>');
2333 --bug 12605679: Added encoding to UTF-8
2334 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<?xml version="1.0" encoding="UTF-8" ?>');
2335 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ROWSET>');
2336 IF p_execution_mode = 'V' THEN
2337 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<EXEC_MODE>View Purge Selection</EXEC_MODE>');
2338 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<COUNT_HEADING>Eligible to Purge</COUNT_HEADING>');
2339 ELSE
2340 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<EXEC_MODE>Purge</EXEC_MODE>');
2341 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<COUNT_HEADING>Purged</COUNT_HEADING>)');
2342 END IF;
2343 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CURR_DATE>'|| SYSDATE ||'</CURR_DATE>');
2344
2345 --RTV changes
2346 /*
2347 IF (p_source_system = 'ALL') THEN
2348 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SOURCE_SYSTEM>All</SOURCE_SYSTEM>');
2349 ELSIF (p_source_system = 'OE') THEN
2350 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SOURCE_SYSTEM>Order Management</SOURCE_SYSTEM>');
2351 ELSIF (p_source_system = 'PO') THEN
2352 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SOURCE_SYSTEM>Purchasing</SOURCE_SYSTEM>');
2353 ELSIF (p_source_system = 'WSH') THEN
2354 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SOURCE_SYSTEM>Shipping</SOURCE_SYSTEM>');
2355 END IF;
2356 */
2357
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 (
2367 end_date_active, Trunc(SYSDATE))
2368 AND ROWNUM < 2;
2369 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SOURCE_SYSTEM>'||l_src_meaning||'</SOURCE_SYSTEM>');
2370 --RTV changes
2371
2372
2373 IF p_ship_from_org IS NOT NULL THEN
2374 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_ORG>' ||
2375 WSH_UTIL_CORE.GET_ORG_NAME(p_organization_id => to_number(p_ship_from_org)) ||
2376 '</SHIP_ORG>');
2377 ELSE
2378 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SHIP_ORG></SHIP_ORG>');
2379 END IF;
2380
2381 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_NUM_FROM>' || p_order_number_from || '</ORDER_NUM_FROM>');
2382 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_NUM_TO>' || p_order_number_to || '</ORDER_NUM_TO>');
2383 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_TYPE>' || p_order_type || '</ORDER_TYPE>');
2384 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SHIP_DATE_FROM>' || FND_DATE.CANONICAL_TO_DATE(p_ship_date_from) || '</SHIP_DATE_FROM>');
2385 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SHIP_DATE_TO>' || FND_DATE.CANONICAL_TO_DATE(p_ship_date_to) || '</SHIP_DATE_TO>');
2386 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<DEL_SHIP_DAYS>' || p_delete_beyond_x_ship_days || '</DEL_SHIP_DAYS>');
2387
2388 IF p_purge_intransit_trips = 'Y' THEN
2389 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CLOSE_IT_TRIPS>Yes</CLOSE_IT_TRIPS>');
2390 ELSE
2391 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CLOSE_IT_TRIPS>No</CLOSE_IT_TRIPS>');
2392 END IF;
2393
2394 IF p_delete_empty_records = 'Y' THEN
2395 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<DEL_EMPTY>Yes</DEL_EMPTY>');
2396 ELSE
2397 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<DEL_EMPTY>No</DEL_EMPTY>');
2398 END IF;
2399
2400 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CREATE_DATE_FROM>'|| FND_DATE.CANONICAL_TO_DATE(p_create_date_from) ||'</CREATE_DATE_FROM>');
2401 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CREATE_DATE_TO>'|| FND_DATE.CANONICAL_TO_DATE(p_create_date_to) ||'</CREATE_DATE_TO>');
2402 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<DEL_CREATE_DAYS>' || p_del_beyond_creation_days || '</DEL_CREATE_DAYS>');
2403
2404 IF p_sort_per_criteria = 'T' THEN
2405 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_CRITERIA>Trip</SORT_CRITERIA>');
2406 ELSIF p_sort_per_criteria = 'D' THEN
2407 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_CRITERIA>Delivery</SORT_CRITERIA>');
2408 ELSIF p_sort_per_criteria = 'O' THEN
2409 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_CRITERIA>Order</SORT_CRITERIA>');
2410 END IF;
2411
2412 IF p_print_detail='L' THEN
2413 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<PRINT_DETAIL>Detail with LPN</PRINT_DETAIL>');
2414 ELSIF p_print_detail='D' THEN
2415 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<PRINT_DETAIL>Detail</PRINT_DETAIL>');
2416 ELSIF p_print_detail='S' THEN
2417 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<PRINT_DETAIL>Summary</PRINT_DETAIL>');
2418 END IF;
2419
2420 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<PURGED_BY>'|| FND_GLOBAL.USER_NAME ||'</PURGED_BY>');
2421 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<REQUEST_ID>' ||FND_GLOBAL.CONC_REQUEST_ID ||'</REQUEST_ID>');
2422 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<NO_OF_TRIPS>' || p_tbl_trip_purge_set.COUNT || '</NO_OF_TRIPS>');
2423 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<NO_OF_STOPS>' || p_count_stops || '</NO_OF_STOPS>');
2424 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<NO_OF_DELS>' || p_tbl_delivery_purge_set.COUNT || '</NO_OF_DELS>');
2425 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<NO_OF_LINES>' || p_count_details || '</NO_OF_LINES>');
2426
2427 IF p_print_detail <> 'S' THEN --check for print detail
2428 IF p_tbl_trip_purge_set.COUNT > 0 THEN
2429 FOR l_trip_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
2430 LOOP
2431 l_trip_id := p_tbl_trip_purge_set(l_trip_index).trip_id;
2432 l_trip_name := p_tbl_trip_purge_set(l_trip_index).trip_name;
2433 IF (p_tbl_trip_purge_set(l_trip_index).purge_set_type = 'NON_EMPTY') THEN
2434 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP>');
2435 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID>' || l_trip_id || '</TRIP_ID>');
2436 l_nonempty_count := l_nonempty_count +1;
2437 OPEN c_dels_for_trip(l_trip_id);
2438 LOOP
2439 FETCH c_dels_for_trip into l_delivery_id,l_delivery_name,l_waybill,l_gross_weight,
2440 l_ship_to,l_customer_name,l_pickup_date,l_dropoff_date,l_ship_date,l_bol_number ;
2441 EXIT WHEN c_dels_for_trip%NOTFOUND;
2442
2443 IF p_sort_per_criteria <> 'O' THEN --check for sort by order
2444 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRIP>');
2445 IF P_SORT_PER_CRITERIA = 'T' THEN
2446 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>' || l_trip_name || '</SORT_ID>');
2447 ELSIF P_SORT_PER_CRITERIA = 'D' THEN
2448 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>' || l_delivery_name || '</SORT_ID>');
2449 END IF ;
2450 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID>' || l_trip_name || '</TRIP_ID>');
2451 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DELIVERY_ID>' || l_delivery_name || '</DELIVERY_ID>');
2452 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<WAYBILL>' || l_waybill || '</WAYBILL>');
2453 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BOL>' || l_bol_number || '</BOL>');
2454 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_WEIGHT>' || l_gross_weight || '</GROSS_WEIGHT>');
2455 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_TO>' || l_ship_to || '</SHIP_TO>');
2456 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CUSTOMER><![CDATA[ ' || l_customer_name || ']]></CUSTOMER>');
2457 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PICKUP_DATE>' || l_pickup_date || '</PICKUP_DATE>');
2458 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DROPOFF_DATE>' || l_dropoff_date || '</DROPOFF_DATE>');
2459 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_DATE>' || l_ship_date || '</SHIP_DATE>');
2460 END IF;--check for sort by order
2461
2462 OPEN c_so_for_delivery(l_delivery_id);
2463 LOOP
2464 FETCH c_so_for_delivery into l_sales_order,l_order_type;
2465 EXIT WHEN c_so_for_delivery%NOTFOUND;
2466 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SALES_ORDER>');
2467 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ORDER_NUMBER>' || l_sales_order || '</ORDER_NUMBER>');
2468 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ORDER_TYPE>' || l_order_type || '</ORDER_TYPE>');
2469 IF p_print_detail = 'L' THEN --check whether to print container details
2470 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CONTAINER>');
2471 OPEN c_containers_for_so(l_sales_order);
2472 LOOP
2473 l_lpn_count := l_lpn_count+1;
2474 FETCH c_containers_for_so into l_container_id;
2475 EXIT WHEN c_containers_for_so%NOTFOUND;
2476 IF l_lpn_count = 1 THEN
2477 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_container_id);
2478 ELSE
2479 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ,' || l_container_id);
2480 END IF;
2481 END LOOP;
2482 CLOSE c_containers_for_so;
2483 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</CONTAINER>');
2484 END IF; -- end check to print container details
2485
2486 IF p_sort_per_criteria = 'O' THEN --check for sort by order
2487 OPEN c_dels_trips_for_order(l_sales_order);
2488 LOOP
2489 FETCH c_dels_trips_for_order into l_trip_id,l_trip_name,l_delivery_id,
2490 l_delivery_name,l_waybill,l_gross_weight,l_ship_to,l_customer_name,
2491 l_pickup_date,l_dropoff_date,l_ship_date,l_bol_number ;
2492 EXIT WHEN c_dels_trips_for_order%NOTFOUND;
2493 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRIP>');
2494 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID>' || l_trip_name || '</TRIP_ID>');
2495 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DELIVERY_ID>' || l_delivery_name || '</DELIVERY_ID>');
2496 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<WAYBILL>' || l_waybill || '</WAYBILL>');
2497 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BOL>' || l_bol_number || '</BOL>');
2498 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_WEIGHT>' || l_gross_weight || '</GROSS_WEIGHT>');
2499 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_TO>' || l_ship_to || '</SHIP_TO>');
2500 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CUSTOMER><![CDATA[ ' || l_customer_name || ']]></CUSTOMER>');
2501 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PICKUP_DATE>' || l_pickup_date || '</PICKUP_DATE>');
2502 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DROPOFF_DATE>' || l_dropoff_date || '</DROPOFF_DATE>');
2503 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_DATE>' || l_ship_date || '</SHIP_DATE>');
2504 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');
2505 END LOOP;
2506 CLOSE c_dels_trips_for_order;
2507 END IF; --check for sort by order
2508
2509 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</SALES_ORDER>');
2510 END LOOP;
2511 CLOSE c_so_for_delivery;
2512 IF p_sort_per_criteria <> 'O' THEN --check for sort by order
2513 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');
2514 END IF; --check for sort by order
2515 END LOOP;
2516 CLOSE c_dels_for_trip;
2517 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');
2518 END IF;
2519 END LOOP;
2520 END IF; --check for number of records in plsql table
2521
2522 -- print empty trips
2523 IF p_tbl_trip_purge_set.COUNT > 0 THEN --check for number of records in plsql table
2524 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_TRIPS>');
2525 l_buff_size := 0 ; -- Reset the buffer size to zero
2526 FOR l_trip_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
2527 LOOP
2528 l_trip_id := p_tbl_trip_purge_set(l_trip_index).trip_id;
2529 l_trip_name := p_tbl_trip_purge_set(l_trip_index).trip_name;
2530 IF (p_tbl_trip_purge_set(l_trip_index).purge_set_type = 'EMPTYTRIPS') THEN
2531 l_empty_trip_count := l_empty_trip_count+1;
2532 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRIP>');
2533 IF P_SORT_PER_CRITERIA = 'T' THEN
2534 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>' || lpad(l_trip_id,10,'0') || '</SORT_ID>');
2535 ELSIF P_SORT_PER_CRITERIA = 'D' THEN
2536 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>999999999</SORT_ID>');
2537 END IF ;
2538 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID>' || l_trip_id || '</TRIP_ID>');
2539 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DELIVERY_ID></DELIVERY_ID>');
2540 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');*/
2541
2542 --Bug 8204644
2543 IF ( l_buff_size = 0 AND l_empty_trip_count=1 ) THEN
2544 l_buff_size := lengthb(l_trip_name);
2545 FND_FILE.PUT(FND_FILE.OUTPUT,l_trip_name);
2546 ELSIF ( l_buff_size = 0 AND l_empty_trip_count<>1 ) THEN
2547 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,',');
2548 l_buff_size := lengthb(l_trip_name);
2549 FND_FILE.PUT(FND_FILE.OUTPUT,l_trip_name);
2550 ELSIF l_buff_size < 30000 THEN
2551 l_buff_size := l_buff_size + lengthb(l_trip_name) + 2;
2552 FND_FILE.PUT(FND_FILE.OUTPUT, ', ' || l_trip_name);
2553 ELSIF l_buff_size >= 30000 THEN
2554 l_buff_size := 0;
2555 FND_FILE.PUT(FND_FILE.OUTPUT, ', ' || l_trip_name );
2556 END IF;
2557 --Bug 8204644
2558 END IF;
2559 END LOOP;
2560 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</EMPTY_TRIPS>');
2561 END IF; --check for number of records in plsql table
2562
2563 -- print empty deliveries
2564 IF p_tbl_delivery_purge_set.COUNT > 0 THEN --check for number of records in plsql table
2565 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_DELS>');
2566 l_buff_size := 0 ; -- Reset the buffer size to zero
2567 FOR l_delivery_index in p_tbl_delivery_purge_set.FIRST .. p_tbl_delivery_purge_set.LAST
2568 LOOP
2569 l_delivery_id := p_tbl_delivery_purge_set(l_delivery_index).delivery_id;
2570 l_delivery_name := p_tbl_delivery_purge_set(l_delivery_index).delivery_name;
2571 IF (p_tbl_delivery_purge_set(l_delivery_index).purge_set_type = 'EMPTYDELS') THEN
2572 l_empty_del_count := l_empty_del_count+1 ;
2573
2574 --Bug 8204644
2575 IF ( l_buff_size = 0 AND l_empty_del_count=1 ) THEN
2576 l_buff_size := lengthb(l_delivery_name);
2577 FND_FILE.PUT(FND_FILE.OUTPUT,l_delivery_name);
2578 ELSIF ( l_buff_size = 0 AND l_empty_del_count<>1 ) THEN
2579 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,',');
2580 l_buff_size := lengthb(l_delivery_name);
2581 FND_FILE.PUT(FND_FILE.OUTPUT,l_delivery_name);
2582 ELSIF l_buff_size < 30000 THEN
2583 l_buff_size := l_buff_size + lengthb(l_delivery_name) + 2;
2584 FND_FILE.PUT(FND_FILE.OUTPUT, ', ' || l_delivery_name);
2585 ELSIF l_buff_size >= 30000 THEN
2586 l_buff_size := 0;
2587 FND_FILE.PUT(FND_FILE.OUTPUT, ', ' || l_delivery_name );
2588 END IF;
2589 --Bug 8204644
2590
2591 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRIP>');
2592 IF P_SORT_PER_CRITERIA = 'T' THEN
2593 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>999999999</SORT_ID>');
2594 ELSIF P_SORT_PER_CRITERIA = 'D' THEN
2595 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>' || lpad(l_delivery_id,10,'0') || '</SORT_ID>');
2596 END IF ;
2597 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID></TRIP_ID>');
2598 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DELIVERY_ID>' || l_delivery_id || '</DELIVERY_ID>');
2599 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');*/
2600
2601 END IF;
2602 END LOOP;
2603 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</EMPTY_DELS>');
2604 END IF; --check for number of records in plsql table
2605
2606 -- print empty containers
2607 IF p_tbl_container_purge_set.COUNT > 0 THEN --check for number of records in plsql table
2608 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_LPNS>');
2609 l_buff_size := 0 ; -- Reset the buffer size to zero
2610 FOR l_container_index in p_tbl_container_purge_set.FIRST .. p_tbl_container_purge_set.LAST
2611 LOOP
2612 l_container_id := p_tbl_container_purge_set(l_container_index).container_id;
2613 IF (p_tbl_container_purge_set(l_container_index).purge_set_type = 'EMPTYLPNS') THEN
2614 l_empty_lpn_count := l_empty_lpn_count+1 ;
2615
2616 --Bug 8204644
2617 IF ( l_buff_size = 0 AND l_empty_lpn_count=1 ) THEN
2618 l_buff_size := lengthb(l_container_id);
2619 FND_FILE.PUT(FND_FILE.OUTPUT,l_container_id);
2620 ELSIF ( l_buff_size = 0 AND l_empty_lpn_count<>1 ) THEN
2621 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,',');
2622 l_buff_size := lengthb(l_container_id);
2623 FND_FILE.PUT(FND_FILE.OUTPUT,l_container_id);
2624 ELSIF l_buff_size < 30000 THEN
2625 l_buff_size := l_buff_size + lengthb(l_container_id) + 2;
2626 FND_FILE.PUT(FND_FILE.OUTPUT, ', ' || l_container_id);
2627 ELSIF l_buff_size >= 30000 THEN
2628 l_buff_size := 0;
2629 FND_FILE.PUT(FND_FILE.OUTPUT, ', ' || l_container_id );
2630 END IF;
2631 --Bug 8204644
2632
2633 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRIP>');
2634 IF P_SORT_PER_CRITERIA = 'T' THEN
2635 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>999999999</SORT_ID>');
2636 ELSIF P_SORT_PER_CRITERIA = 'D' THEN
2637 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>' || lpad(l_delivery_id,10,'0') || '</SORT_ID>');
2638 END IF ;
2639 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID></TRIP_ID>');
2640 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DELIVERY_ID>' || l_delivery_id || '</DELIVERY_ID>');
2641 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');*/
2642
2643 END IF;
2644 END LOOP;
2645 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</EMPTY_LPNS>');
2646 END IF; --check for number of records in plsql table
2647
2648 END IF; --check for print detail
2649 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NON_EMPTY_COUNT>' || l_nonempty_count || '</NON_EMPTY_COUNT>');
2650 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_TRIP_COUNT>' || l_empty_trip_count || '</EMPTY_TRIP_COUNT>');
2651 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_DEL_COUNT>' || l_empty_del_count || '</EMPTY_DEL_COUNT>');
2652 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_LPN_COUNT>' || l_empty_lpn_count || '</EMPTY_LPN_COUNT>');
2653 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</ROWSET>');
2654
2655 IF l_debug_on THEN
2656 WSH_DEBUG_SV.pop(l_module_name);
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 '||
2666 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2667 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2668 END IF;
2669
2670 END Generate_Purge_Report;
2671
2672
2673 -----------------------------------------------------------------------------
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
2683 PROCEDURE Purge_Workflow(p_tbl_trip_purge_set Trip_ID_Tbl_Type ,
2684 p_tbl_delivery_purge_set Delivery_ID_Tbl_Type,
2685 x_return_status OUT NOCOPY VARCHAR2) IS
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
2695 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_WORKFLOW';
2696
2697 BEGIN
2698
2699 -- Debug Statements
2700 --
2701 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2702 --
2703 IF l_debug_on IS NULL THEN
2704 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2705 END IF;
2706
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
2716 LOOP
2717 l_delivery_ids_tab(l_delivery_index) := p_tbl_delivery_purge_set(l_delivery_index).delivery_id;
2718 END LOOP;
2719
2720 IF l_debug_on THEN
2721 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.PURGE_ENTITY', WSH_DEBUG_SV.C_PROC_LEVEL);
2722 WSH_DEBUG_SV.logmsg(l_module_name,'no of delivery ids =' || l_delivery_ids_tab.count, WSH_DEBUG_SV.C_PROC_LEVEL);
2723 END IF;
2724
2725 WSH_WF_STD.Purge_Entity(
2726 p_entity_type => 'DELIVERY',
2727 p_entity_ids =>l_delivery_ids_tab,
2728 --p_action IN VARCHAR2 DEFAULT 'PURGE',
2729 --p_docommit IN BOOLEAN DEFAULT FALSE,
2730 x_success_count => l_success_count,
2731 x_return_status => l_return_status) ;
2732
2733 IF l_debug_on THEN
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;
2743 RETURN;
2744 END IF;
2745 END IF; --Check for number of ids in delivery table
2746
2747 --Check for number of ids in trip table
2748 IF p_tbl_trip_purge_set.COUNT > 0 THEN
2749 FOR l_trip_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
2750 LOOP
2751 l_trip_ids_tab(l_trip_index) := p_tbl_trip_purge_set(l_trip_index).trip_id;
2752 END LOOP;
2753
2754 IF l_debug_on THEN
2755 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.PURGE_ENTITY', WSH_DEBUG_SV.C_PROC_LEVEL);
2756 END IF;
2757
2758 WSH_WF_STD.Purge_Entity(
2759 p_entity_type => 'TRIP',
2760 p_entity_ids =>l_trip_ids_tab,
2761 --p_action IN VARCHAR2 DEFAULT 'PURGE',
2762 --p_docommit IN BOOLEAN DEFAULT FALSE,
2763 x_success_count => l_success_count,
2764 x_return_status => l_return_status) ;
2765
2766 IF l_debug_on THEN
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;
2776 RETURN;
2777 END IF;
2778 END IF; --Check for number of ids in trip table
2779
2780 IF l_debug_on THEN
2781 WSH_DEBUG_SV.pop(l_module_name);
2782 END IF;
2783
2784 END Purge_Workflow;
2785
2786
2787 -----------------------------------------------------------------------------
2788 --
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
2798 -- be purged from WMS side. If the number of LPNs returned by WMS
2799 -- is same as the number of LPNs passed by this API that means that
2800 -- all the LPNs within the trip are eligible to be purged and the
2801 -- further validations for MDC/Moves can be performed on the trip.
2802 -- If the count is not same then the trip is marked as in eligible for
2803 -- purge and is excluded from the list of trips to be purged.
2804 -- This API also checks whether the trip is a part of valid Continuous
2805 -- Move(CM). A valid CM is one in which all the the trips are eligible
2806 -- for purge. If not then the Trip is not eligible to be purged.
2807 -- This API also checks whether the trip is a part of valid MDC
2808 -- configuration. A valid MDC configurationis one in which all
2809 -- the the trips are eligible for purge. If not then the Trip is
2810 -- not eligible to be purged.
2811 -----------------------------------------------------------------------------
2812 PROCEDURE Validate_Trips( p_tbl_trip_purge_set Trip_ID_Tbl_Type ,
2813 x_tbl_trip_purge_set OUT NOCOPY Trip_ID_Tbl_Type ,
2814 x_return_status OUT NOCOPY VARCHAR2) IS
2815
2816 l_debug_on BOOLEAN;
2817 l_return_status VARCHAR2(1);
2818
2819 l_trip_id NUMBER;
2820 l_move_id NUMBER;
2821 l_lpn_id NUMBER;
2822 l_trip_index NUMBER;
2823 l_loop_index NUMBER;
2824 l_old_move NUMBER;
2825 l_new_move NUMBER;
2826 l_mdc_trip NUMBER;
2827 l_lpn_count NUMBER;
2828
2829 l_lpn_valid BOOLEAN;
2830 l_move_valid BOOLEAN;
2831 l_trip_valid BOOLEAN;
2832 l_move_found BOOLEAN;
2833 l_trip_found BOOLEAN;
2834
2835 l_err VARCHAR2(500);
2836 sql_tripmove VARCHAR2(4000);
2837
2838 l_msg_count NUMBER;
2839 l_msg_data VARCHAR2(32767);
2840
2841 l_wms_lpn_record WMS_Data_Type_Definitions_PUB.LPNPurgeRecordType;
2842
2843 --TYPE IDTableType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2844 --l_lpn_ids IDTableType;
2845
2846 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_TRIPS';
2847
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,
2857 wsh_trip_stops wts,
2858 wsh_delivery_legs wdl,
2859 wsh_new_deliveries wnd,
2860 wsh_delivery_assignments_v wda,
2861 wsh_delivery_details wdd
2862 WHERE wt.trip_id = wts.trip_id
2863 AND wts.stop_id = wdl.pick_up_stop_id
2864 AND wdl.delivery_id = wnd.delivery_id
2865 AND wda.delivery_id = wnd.delivery_id
2866 AND wda.delivery_detail_id = wdd.delivery_detail_id
2867 AND wdd.container_flag = 'Y'
2868 AND wdd.lpn_id IS NOT NULL
2869 AND wts.trip_id = p_tripid;
2870
2871 -- Bug 5084113
2872 /* Replaced the query below with a non recursive query
2873 The query below was fetching,
2874 1. All the deliveries of consolidation type( wnd.delivery_type = 'CONSOLIDATION' or wdl.parent_delivery_leg_id IS NULL )
2875 2. Search for possible consolidations within the above list.
2876 3. Build the list of deliveries under consolidation deliveries in step2 and identify the list of trips.
2877
2878 CURSOR c_get_mdc_trips(p_tripid NUMBER) IS
2879 SELECT
2880 DISTINCT wt1.trip_id
2881 FROM
2882 wsh_trips wt1,
2883 wsh_trip_stops pickup_stop1,
2884 wsh_trip_stops dropoff_stop1,
2885 wsh_delivery_legs wdl1
2886 WHERE
2887 wdl1.pick_up_stop_id = pickup_stop1.stop_id AND
2888 wdl1.drop_off_stop_id = dropoff_stop1.stop_id AND
2889 wt1.trip_id = pickup_stop1.trip_id AND
2890 wt1.trip_id = dropoff_stop1.trip_id AND
2891 wdl1.delivery_id IN (SELECT delivery_id
2892 FROM wsh_delivery_legs
2893 START WITH delivery_id IN (SELECT delivery_id
2894 FROM wsh_delivery_legs
2895 WHERE parent_delivery_leg_id IS NULL
2896 START WITH delivery_id IN (SELECT wdl.delivery_id
2897 FROM
2898 wsh_new_deliveries wnd,
2899 wsh_delivery_legs wdl,
2900 wsh_trip_stops pickup_stop,
2901 wsh_trip_stops dropoff_stop,
2902 wsh_trips wt
2903 WHERE
2904 wnd.delivery_id = wdl.delivery_id AND
2905 wdl.pick_up_stop_id = pickup_stop.stop_id AND
2906 wdl.drop_off_stop_id = dropoff_stop.stop_id AND
2907 wt.trip_id = pickup_stop.trip_id AND
2908 wt.trip_id = dropoff_stop.trip_id AND
2909 ((wnd.delivery_type = 'CONSOLIDATION')
2910 OR
2911 (wdl.parent_delivery_leg_id IS NULL)
2912 ) AND
2913 wt.trip_id = p_tripid)
2914 CONNECT BY delivery_leg_id = PRIOR parent_delivery_leg_id )
2915 CONNECT BY parent_delivery_leg_id = PRIOR delivery_leg_id)
2916 ORDER BY wt1.trip_id;
2917 */
2918
2919 CURSOR c_get_mdc_trips(p_tripid NUMBER) IS
2920 SELECT
2921 DISTINCT wt1.trip_id
2922 FROM
2923 wsh_trips wt1,
2924 wsh_trip_stops wts,
2925 wsh_delivery_legs wdl1
2926 WHERE
2927 (wdl1.pick_up_stop_id = wts.stop_id OR
2928 wdl1.drop_off_stop_id = wts.stop_id) AND
2929 wt1.trip_id = wts.trip_id AND
2930 wdl1.delivery_id IN
2931 (
2932 SELECT delivery_id
2933 FROM wsh_delivery_legs
2934 WHERE parent_delivery_leg_id
2935 IN
2936 (
2937 SELECT wdl.delivery_leg_id
2938 FROM
2939 wsh_delivery_legs wdl,
2940 wsh_trip_stops wts,
2941 wsh_trips wt
2942 WHERE
2943 (wdl.pick_up_stop_id = wts.stop_id OR
2944 wdl.drop_off_stop_id = wts.stop_id) AND
2945 wt.trip_id = wts.trip_id AND
2946 wdl.parent_delivery_leg_id IS NULL AND
2947 wt.trip_id = p_tripid
2948 )
2949 )
2950 ORDER BY wt1.trip_id;
2951
2952 BEGIN
2953 -- Debug Statements
2954 --
2955 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2956 --
2957 IF l_debug_on IS NULL THEN
2958 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2959 END IF;
2960
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
2970 FROM fte_trip_moves
2971 WHERE move_id IN
2972 (SELECT move_id
2973 FROM fte_trip_moves
2974 GROUP BY move_id
2975 HAVING count(trip_id) >1 )
2976 ORDER BY move_id';
2977
2978 OPEN c_trip_moves FOR sql_tripmove;
2979 FETCH c_trip_moves BULK COLLECT into l_tbl_trip_moves;
2980 CLOSE c_trip_moves;
2981 END IF;
2982
2983 FOR l_trip_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
2984 LOOP
2985 l_lpn_valid := TRUE;
2986 l_trip_id := p_tbl_trip_purge_set(l_trip_index).trip_id;
2987 --Get all the LPNs for the Trip
2988 OPEN c_lpns_for_trip(l_trip_id);
2989 FETCH c_lpns_for_trip BULK COLLECT into l_wms_lpn_record.LPN_IDs;
2990 CLOSE c_lpns_for_trip;
2991
2992 l_lpn_count := l_wms_lpn_record.LPN_IDs.COUNT;
2993
2994 --call WMS API to check whether the LPN is eligible for purge
2995 IF l_debug_on THEN
2996 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_CONTAINER_GRP.LPN_PURGE_ACTIONS', WSH_DEBUG_SV.C_PROC_LEVEL);
2997 END IF;
2998
2999 WMS_Container_GRP.LPN_Purge_Actions( p_api_version =>1.0,
3000 p_init_msg_list =>FND_API.G_TRUE,
3001 p_commit =>'FALSE',
3002 x_return_status =>l_return_status,
3003 x_msg_count =>l_msg_count,
3004 x_msg_data =>l_msg_data,
3005 p_caller =>'WSH',
3006 p_action =>WMS_Container_GRP.G_LPN_PURGE_ACTION_VALIDATE,
3007 p_lpn_purge_rec => l_wms_lpn_record
3008 );
3009
3010 IF l_debug_on THEN
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;
3020 RETURN;
3021 END IF;
3022
3023 IF l_lpn_count <> l_wms_lpn_record.LPN_IDs.COUNT THEN
3024 l_lpn_valid := FALSE;
3025 END IF;
3026
3027 --Check for Trip Moves/MDC related validations only when LPN check returns TRUE
3028 IF l_lpn_valid THEN -- is there some other way to stop the loop here and
3029 -- and continue the loop with the next value ?
3030 l_trip_valid := TRUE;
3031 l_move_found := FALSE;
3032 --Check for Trip Moves related validations
3033 IF l_tbl_trip_moves.COUNT > 0 THEN
3034 FOR l_loop_index in l_tbl_trip_moves.FIRST .. l_tbl_trip_moves.LAST
3035 LOOP
3036 IF l_tbl_trip_moves(l_loop_index).trip_id = l_trip_id THEN
3037 l_move_id := l_tbl_trip_moves(l_loop_index).move_id;
3038 l_move_found := TRUE;
3039 FOR l_move_index in l_tbl_trip_moves.FIRST .. l_tbl_trip_moves.LAST
3040 LOOP
3041 l_old_move := l_new_move;
3042 l_new_move :=l_tbl_trip_moves(l_move_index).move_id;
3043 IF (l_old_move IS NOT NULL AND l_old_move <> l_new_move) THEN
3044 EXIT;
3045 END IF;
3046
3047 IF l_tbl_trip_moves(l_move_index).move_id = l_move_id THEN
3048 l_trip_id := l_tbl_trip_moves(l_move_index).trip_id;
3049 l_trip_valid := FALSE;
3050 FOR l_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
3051 LOOP
3052 IF p_tbl_trip_purge_set(l_index).trip_id = l_trip_id THEN
3053 l_trip_valid := TRUE;
3054 END IF;
3055 EXIT WHEN l_trip_valid;
3056 END LOOP;
3057
3058 IF NOT l_trip_valid THEN
3059 EXIT;
3060 END IF;
3061 END IF;
3062 END LOOP;
3063 END IF;
3064 EXIT WHEN l_move_found;
3065 END LOOP;
3066 END IF;
3067 --End Check for Trip Moves related validations
3068
3069 --Check for MDC related validations only if Trip Move related validation returns TRUE
3070 IF l_trip_valid THEN
3071 OPEN c_get_mdc_trips(l_trip_id);
3072 FETCH c_get_mdc_trips BULK COLLECT into l_tbl_trip_mdc;
3073 CLOSE c_get_mdc_trips;
3074 --Check for MDC related validations
3075 IF l_tbl_trip_mdc.COUNT > 1 THEN
3076 FOR l_loop_index in l_tbl_trip_mdc.FIRST .. l_tbl_trip_mdc.LAST
3077 LOOP
3078 l_mdc_trip := l_tbl_trip_mdc(l_loop_index);
3079 l_trip_valid := FALSE;
3080 FOR l_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
3081 LOOP
3082 IF p_tbl_trip_purge_set(l_index).trip_id = l_mdc_trip THEN
3083 l_trip_valid := TRUE;
3084 END IF;
3085 EXIT WHEN l_trip_valid;
3086 END LOOP;
3087 EXIT WHEN NOT l_trip_valid;
3088 END LOOP;
3089 END IF; --End check for MDC related validations
3090 END IF;--Check for MDC related validations only if Trip Move related validation returns TRUE
3091 END IF; --End Check for Trip Moves/MDC related validations only when LPN check returns TRUE
3092
3093 IF l_lpn_valid AND l_trip_valid THEN
3094 x_tbl_trip_purge_set(x_tbl_trip_purge_set.COUNT+1).trip_id := p_tbl_trip_purge_set(l_trip_index).trip_id;
3095 x_tbl_trip_purge_set(x_tbl_trip_purge_set.COUNT).trip_name := p_tbl_trip_purge_set(l_trip_index).trip_name;
3096 x_tbl_trip_purge_set(x_tbl_trip_purge_set.COUNT).purge_set_type := p_tbl_trip_purge_set(l_trip_index).purge_set_type;
3097 END IF ;
3098 END LOOP;
3099
3100 IF l_debug_on THEN
3101 WSH_DEBUG_SV.pop(l_module_name);
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 '||
3111 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
3112 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
3113 END IF;
3114 END Validate_Trips ;
3115
3116 END WSH_PURGE;
[Home] [Help]