1 PACKAGE BODY WSH_PURGE AS
2 /* $Header: WSHPURGB.pls 120.4 2006/04/10 23:20:16 alksharm noship $ */
3
4 -- Description: Constant to distinguish CONCURRENT request from
5 -- ONLINE request
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
369 l_source_system VARCHAR2(20);
370 l_loop_index NUMBER;
371 i number;
372
373 l_trip_id NUMBER;
374 l_delivery_id NUMBER;
375 l_stop_id NUMBER;
376 l_leg_id NUMBER;
377 l_detail_id NUMBER;
378 l_container_id NUMBER;
379 l_container_flag VARCHAR2(1);
380
381 l_trip_name VARCHAR2(30);
382 l_delivery_name VARCHAR2(30);
383
384 l_trip_purge_row Trip_ID_Rec_Type;
385 l_del_purge_row Delivery_ID_Rec_Type;
386
387 l_tbl_trip_purge_set Trip_ID_Tbl_Type;
388
389 TYPE PurgeCurType IS REF CURSOR;
390 c_trip_purge_cur PurgeCurType;
391 c_del_purge_cur PurgeCurType;
392 c_empty_trip_cur PurgeCurType;
393 c_empty_containers PurgeCurType;
394
395 CURSOR c_dels_for_trip(p_tripid NUMBER) IS
396 SELECT distinct wnd.delivery_id, wnd.name
397 FROM wsh_trips wt,
398 wsh_trip_stops wts,
399 wsh_delivery_legs wdl,
400 wsh_new_deliveries wnd
401 WHERE wt.trip_id = wts.trip_id
402 AND wts.stop_id = wdl.pick_up_stop_id
403 AND wdl.delivery_id = wnd.delivery_id
404 AND wts.trip_id = p_tripid;
405
406 CURSOR c_stops_for_trip(p_tripid NUMBER) IS
407 SELECT stop_id
408 FROM wsh_trip_stops
409 WHERE trip_id= p_tripid;
410
411 CURSOR c_legs_for_del(p_deliveryid NUMBER) IS
412 SELECT delivery_leg_id
413 FROM wsh_delivery_legs
414 WHERE delivery_id = p_deliveryid;
415
416 CURSOR c_details_for_del(p_deliveryid NUMBER) IS
417 SELECT wda.delivery_detail_id,
418 wdd.container_flag
419 FROM wsh_delivery_assignments_v wda,
420 wsh_delivery_details wdd
421 WHERE wda.delivery_id = p_deliveryid
422 AND wda.delivery_detail_id = wdd.delivery_detail_id;
423
424
425 --cursor to select empty trips would come from dynamic sql
426 --cursor to select empty dels would come from dynamic sql
427 --(based on create date from and to)
428
429 /*
430 --cursor to select orphaned deliveries with only empty containers
431 CURSOR c_delivery_empty_containers IS
432 SELECT wnd.delivery_id
433 --wda.delivery_detail_id
434 FROM wsh_new_deliveries wnd,
435 wsh_delivery_assignments_v wda,
436 wsh_delivery_legs wdl
437 WHERE wnd.delivery_id = wda.delivery_id
438 AND wnd.delivery_id = wdl.delivery_id(+)
439 AND wdl.delivery_leg_id IS NULL
440 AND NOT EXISTS (
441 SELECT 1
442 FROM wsh_delivery_details wdd,
443 wsh_delivery_assignments_v wda1
444 WHERE wdd.delivery_Detail_id = wda1.delivery_detail_id
445 AND wda1.delivery_id = wnd.delivery_id
446 AND wdd.container_flag = 'N'
447 ) ;
448 */
449
450 --cursor to select orphaned empty containers
451 /* CURSOR c_empty_containers IS
452 SELECT
453 wdd.delivery_detail_id,
454 'EMPTYLPNS'
455 --wdd.container_name dd_lpn_number,
456 FROM
457 wsh_delivery_assignments_v wda,
458 wsh_delivery_details wdd
459 WHERE
460 wda.delivery_detail_id = wdd.delivery_detail_id AND
461 wdd.container_flag = 'Y'AND
462 wda.delivery_id IS NULL AND
463 NOT EXISTS (
464 SELECT 1
465 FROM
466 wsh_delivery_assignments_v wda2
467 WHERE
468 wda2.parent_delivery_detail_id = wda.delivery_detail_id
469 ) ;
470 */
471
472 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GET_PURGE_SET';
473
474 BEGIN
475
476 -- Debug Statements
477 --
478 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
479 --
480 IF l_debug_on IS NULL THEN
481 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
482 END IF;
483
484 IF l_debug_on THEN
485 WSH_DEBUG_SV.push(l_module_name);
486 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_SYSTEM',p_source_system);
487 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_ORG',p_ship_from_org);
488 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER_FROM',p_order_number_from);
489 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER_To',p_order_number_to);
490 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_TYPE',p_order_type);
491 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE_FROM',p_ship_date_from);
492 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE_TO',p_ship_date_to);
493 WSH_DEBUG_SV.log(l_module_name,'P_DELETE_BEYOND_X_SHIP_DAYS',p_delete_beyond_x_ship_days);
494 WSH_DEBUG_SV.log(l_module_name,'P_PURGE_INTRANSIT_TRIPS',p_purge_intransit_trips);
495 WSH_DEBUG_SV.log(l_module_name,'P_DELETE_EMPTY_Records',p_delete_empty_records);
496 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_DATE_FROM',p_create_date_from);
497 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_DATE_TO',p_create_date_to);
498 WSH_DEBUG_SV.log(l_module_name,'P_DEL_BEYOND_CREATION_DAYS',p_del_beyond_creation_days);
499 END IF;
500
501 x_return_status:=WSH_UTIL_CORE.G_RET_STS_SUCCESS;
502
503 --construct trip_sql
504 trip_sql := 'SELECT
505 trip_id,trip_name,''NON_EMPTY'' purge_set_type
506 FROM
507 wsh_purge_set_v outer
508 GROUP BY trip_id,trip_name
509 HAVING count(outer.dd_id) = (SELECT count(inner.dd_id)
510 FROM
511 wsh_purge_set_v inner ';
512 --check whether FTE is installed
513 IF (wsh_util_core.fte_is_installed='Y') THEN
514 trip_sql := trip_sql || ',fte_invoice_headers fih ' ;
515 END IF;
516
517 trip_sql := trip_sql || '
518 WHERE inner.trip_id = outer.trip_id
519 ';
520 --dbms_output.put_line(trip_sql);
521
522 IF (p_ship_from_org IS NOT NULL) THEN
523 trip_sql := trip_sql || '
524 AND inner.del_ship_from_org = '
525 || p_ship_from_org ;
526 END IF;
527
528 IF (p_ship_date_from IS NOT NULL) THEN
529 trip_sql := trip_sql ||
530 ' AND inner.del_pickup_date BETWEEN ''' || FND_DATE.CANONICAL_TO_DATE(p_ship_date_from) ||
531 ''' AND ''' || FND_DATE.CANONICAL_TO_DATE(p_ship_date_to) || '''';
532 ELSE
533 trip_sql := trip_sql ||
534 ' AND inner.del_pickup_date < '''
535 || to_date(SYSDATE - p_delete_beyond_x_ship_days,'DD-MM-YYYY') ||'''' ;
536 END IF;
537
538
539 IF (p_source_system = 'ALL') THEN
540 trip_sql := trip_sql || '
541 AND inner.dd_source_code IN (''OE'',''PO'',''WSH'') ' ;
542 ELSIF (p_source_system = 'OE') THEN
543 trip_sql := trip_sql || '
544 AND inner.dd_source_code IN (''OE'',''WSH'') ' ;
545 ELSIF (p_source_system = 'PO') THEN
546 trip_sql := trip_sql || '
547 AND inner.dd_source_code IN (''PO'',''WSH'') ' ;
548 ELSIF (p_source_system = 'WSH') THEN
549 trip_sql := trip_sql || '
550 AND inner.dd_source_code = ''WSH'' ' ;
551 END IF;
552
553 --check to take order type
554 IF (p_order_type IS NOT NULL) THEN
555 trip_sql := trip_sql ||
556 ' AND inner.dd_source_header_type_id = '
557 || p_order_type ;
558 END IF; --end check to take order type
559
560 --check to take order number range
561 IF (p_order_number_from IS NOT NULL) THEN
562 trip_sql := trip_sql ||
563 ' AND inner.dd_source_header_number BETWEEN '
564 || p_order_number_from ||
565 ' AND ' || p_order_number_to || ' ' ;
566 END IF;--end check to take order number range
567
568 --check for deleting Intransit Trips
569 IF (p_purge_intransit_trips = 'Y') THEN
570 trip_sql := trip_sql ||
571 ' AND inner.trip_status IN (''CL'',''IT'') ' ;
572 ELSE
573 trip_sql := trip_sql ||
574 ' AND inner.trip_status IN (''CL'') ' ;
575 END IF;--end check for deleting Intransit Trips
576
577 trip_sql := trip_sql ||
578 ' AND DECODE(inner.dd_source_code,
579 ''OE'',(DECODE((SELECT count(oe.order_number)
580 FROM oe_order_headers_all oe
581 WHERE oe.header_id= inner.dd_source_header_id),0,''FALSE'',''TRUE'')),
582 ''PO'',( DECODE((SELECT count(po.po_header_id)
583 FROM po_headers_all po
584 WHERE po.po_header_id= inner.dd_source_header_id),0,''FALSE'',''TRUE'')),
585 ''WSH'', ''FALSE'',
586 ''TRUE'') = ''FALSE''
587 ';
588 --check whether FTE is installed
589 IF (wsh_util_core.fte_is_installed='Y') THEN
590 trip_sql := trip_sql ||
591 'AND fih.bol(+) = inner.bol
592 AND DECODE(fih.bill_status,
593 ''PAID'', ''Y'',
594 ''OBSOLETE'' ,''Y'',
595 NULL, ''Y'',
596 ''N'') = ''Y''
597 ';
598 END IF;--end check whether FTE is installed
599 --end construct trip_sql
600
601 trip_sql := trip_sql ||
602 ')' ;
603
604 --check whether to delete empty records
605 IF (p_delete_empty_records ='Y') THEN
606 --construct delivery_sql for empty deliveries
607 delivery_sql := 'SELECT
608 wnd.delivery_id,wnd.name
609 FROM
610 wsh_new_deliveries wnd,
611 wsh_delivery_assignments_v wda,
612 wsh_delivery_legs wdl
613 WHERE
614 wda.delivery_id(+) = wnd.delivery_id AND
615 wnd.delivery_id = wdl.delivery_id(+) AND
616 wdl.delivery_leg_id IS NULL AND
617 wda.delivery_detail_id IS NULL ' ;
618
619 --check for taking creation dates
620 IF (p_create_date_from IS NOT NULL) THEN
621 delivery_sql := delivery_sql || '
622 AND wnd.creation_date
623 BETWEEN '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_from) || '''
624 AND '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_to) || '''' ;
625 ELSE
626 delivery_sql := delivery_sql || '
627 AND wnd.creation_date < '''
628 || to_date(SYSDATE - p_del_beyond_creation_days,'DD-MM-YYYY') ||'''' ;
629 END IF; --end check for taking creation dates
630
631 delivery_sql := delivery_sql || '
632 ORDER BY wnd.name ' ;
633 --end construct delivery_sql
634
635 --construct SQL for empty trips
636 empty_trip_sql := ' SELECT distinct wt.trip_id, wt.name
637 FROM wsh_trips wt,
638 wsh_trip_stops wts,
639 wsh_delivery_legs wdl1,
640 wsh_delivery_legs wdl2
641 WHERE ';
642
643 --check for taking creation dates
644 IF (p_create_date_from IS NOT NULL) THEN
645 empty_trip_sql := empty_trip_sql || '
646 wt.creation_date
647 BETWEEN '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_from) || '''
648 AND '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_to) || '''' ;
649 ELSE
650 empty_trip_sql := empty_trip_sql || '
651 wt.creation_date < '''
652 || to_date(SYSDATE - p_del_beyond_creation_days,'DD-MM-YYYY') ||'''' ;
653 END IF; --end check for taking creation dates
654
655 empty_trip_sql := empty_trip_sql || '
656 AND wt.trip_id = wts.trip_id(+)
657 AND wdl1.pick_up_stop_id(+) = wts.stop_id
658 AND wdl2.drop_off_stop_id(+) = wts.stop_id
659 AND wdl1.delivery_leg_id IS NULL
660 AND wdl2.delivery_leg_id IS NULL
661 AND NOT EXISTS
662 ( SELECT 1
663 FROM wsh_trip_stops wtss,
664 wsh_delivery_legs wdl1s
665 WHERE wtss.trip_id = wt.trip_id
666 AND wdl1s.pick_up_stop_id = wtss.stop_id
667 )
668 ' ;
669 --end contructing SQL for empty trips
670
671 --construct SQL for empty containers
672 empty_container_sql := 'SELECT wdd.delivery_detail_id,
673 ''EMPTYLPNS''
674 --wdd.container_name dd_lpn_number,
675 FROM
676 wsh_delivery_assignments_v wda,
677 wsh_delivery_details wdd
678 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
679 AND wdd.container_flag = ''Y''
680 AND wda.delivery_id IS NULL ';
681
682 --check for taking creation dates
683 IF (p_create_date_from IS NOT NULL) THEN
684 empty_container_sql := empty_container_sql || '
685 AND wdd.creation_date
686 BETWEEN '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_from) || '''
687 AND '''|| FND_DATE.CANONICAL_TO_DATE(p_create_date_to) || '''' ;
688 ELSE
689 empty_container_sql := empty_container_sql || '
690 AND wdd.creation_date < '''
691 || to_date(SYSDATE - p_del_beyond_creation_days,'DD-MM-YYYY') ||'''' ;
692 END IF; --end check for taking creation dates
693
694 empty_container_sql := empty_container_sql || '
695 AND NOT EXISTS (
696 SELECT 1
697 FROM
698 wsh_delivery_assignments_v wda2
699 WHERE
700 wda2.parent_delivery_detail_id = wda.delivery_detail_id
701 ) ' ;
702
703 END IF; --end check whether to delete empty records
704
705 --fetch trip ids for non empty trips and insert into PL/SQL table
706 OPEN c_trip_purge_cur FOR trip_sql;
707 FETCH c_trip_purge_cur BULK COLLECT into x_tbl_trip_purge_set;
708 CLOSE c_trip_purge_cur;
709
710 IF x_tbl_trip_purge_set.COUNT > 0 THEN --check for number of records in plsql table
711 IF l_debug_on THEN
712 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.VALIDATE_TRIPS', WSH_DEBUG_SV.C_PROC_LEVEL);
713 END IF;
714 --Check whether LPNs belonging to the trips are eligible to purge from WMS
715 Validate_Trips( p_tbl_trip_purge_set => x_tbl_trip_purge_set,
716 x_tbl_trip_purge_set => l_tbl_trip_purge_set,
717 x_return_status => l_return_status );
718
719 x_tbl_trip_purge_set := l_tbl_trip_purge_set;
720
721 IF l_debug_on THEN
722 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
723 END IF;
724
725 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
726 x_return_status := l_return_status;
727 IF l_debug_on THEN
728 WSH_DEBUG_SV.pop(l_module_name);
729 END IF;
730 RETURN;
731 END IF;
732 END IF;
733
734 --check whether to fetch empty records
735 IF (p_delete_empty_records ='Y') THEN
736 --fetch trip ids for empty trips and insert into PL/SQL table
737 OPEN c_empty_trip_cur FOR empty_trip_sql;
738 LOOP
739 FETCH c_empty_trip_cur into l_trip_id,l_trip_name;
740 EXIT WHEN c_empty_trip_cur%NOTFOUND;
741 x_tbl_trip_purge_set(x_tbl_trip_purge_set.COUNT+1).trip_id := l_trip_id;
742 x_tbl_trip_purge_set(x_tbl_trip_purge_set.COUNT).trip_name := l_trip_name;
743 x_tbl_trip_purge_set(x_tbl_trip_purge_set.COUNT).purge_set_type := 'EMPTYTRIPS';
744 END LOOP;
745 CLOSE c_empty_trip_cur;
746
747 --fetch delivery ids for empty deliveries
748 OPEN c_del_purge_cur FOR delivery_sql;
749 LOOP
750 FETCH c_del_purge_cur into l_delivery_id,l_delivery_name;
751 EXIT WHEN c_del_purge_cur%NOTFOUND;
752 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT+1).delivery_id := l_delivery_id;
753 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT).delivery_name := l_delivery_name;
754 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT).purge_set_type := 'EMPTYDELS';
755 END LOOP;
756 CLOSE c_del_purge_cur;
757
758 --fetch delivery ids for Deliveries having empty containers only
759 /* OPEN c_delivery_empty_containers;
760 LOOP
761 FETCH c_delivery_empty_containers into l_delivery_id;
762 EXIT WHEN c_delivery_empty_containers%NOTFOUND;
763 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT+1).delivery_id := l_delivery_id;
764 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT).purge_set_type := 'DEL_EMPTYLPNS';
765 END LOOP;
766 CLOSE c_delivery_empty_containers;
767 */
768 --fetch container id for orphaned empty containers
769 OPEN c_empty_containers FOR empty_container_sql;
770 FETCH c_empty_containers BULK COLLECT INTO x_tbl_containers_purge_set;
771 CLOSE c_empty_containers;
772 END IF; --end check whether to fetch empty records
773
774 --add the deliveries belonging to trips from the table x_tbl_trip_purge_set
775 --to the table x_delivery_tbl_trip_set
776 IF x_tbl_trip_purge_set.COUNT > 0 THEN
777 FOR l_loop_index in x_tbl_trip_purge_set.FIRST .. x_tbl_trip_purge_set.LAST
778 LOOP
779 l_trip_id := x_tbl_trip_purge_set(l_loop_index).trip_id;
780
781 IF (x_tbl_trip_purge_set(l_loop_index).purge_set_type = 'NON_EMPTY') THEN
782 OPEN c_dels_for_trip(l_trip_id);
783 LOOP
784 FETCH c_dels_for_trip into l_delivery_id,l_delivery_name;
785 EXIT WHEN c_dels_for_trip%NOTFOUND;
786 --x_tbl_delivery_purge_set.EXTEND;
787 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT+1).delivery_id := l_delivery_id;
788 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT).delivery_name := l_delivery_name;
789 x_tbl_delivery_purge_set(x_tbl_delivery_purge_set.COUNT).purge_set_type := 'NON_EMPTY';
790 END LOOP;
791 CLOSE c_dels_for_trip;
792 END IF;
793 --fetch trip stops for the trips
794 OPEN c_stops_for_trip(l_trip_id);
795 LOOP
796 FETCH c_stops_for_trip into l_stop_id;
797 EXIT WHEN c_stops_for_trip%NOTFOUND;
798 x_tbl_trip_stops_purge_set(x_tbl_trip_stops_purge_set.COUNT+1).stop_id := l_stop_id;
799 END LOOP;
800 CLOSE c_stops_for_trip;
801 END LOOP; -- end adding delivery ids to plsql table
802 END IF;
803
804 --fetch delivery legs and delivery details for Delivery Ids into PL/SQL table
805 IF x_tbl_delivery_purge_set.COUNT > 0 THEN
806 FOR l_loop_index in x_tbl_delivery_purge_set.FIRST .. x_tbl_delivery_purge_set.LAST
807 LOOP
808 l_delivery_id := x_tbl_delivery_purge_set(l_loop_index).delivery_id;
809 --fetch delivery legs
810 IF (x_tbl_delivery_purge_set(l_loop_index).purge_set_type = 'NON_EMPTY') THEN
811 OPEN c_legs_for_del(l_delivery_id);
812 LOOP
813 FETCH c_legs_for_del INTO l_leg_id;
814 EXIT WHEN c_legs_for_del%NOTFOUND;
815 x_tbl_del_legs_purge_set(x_tbl_del_legs_purge_set.COUNT+1).delivery_leg_id := l_leg_id;
816 END LOOP;
817 CLOSE c_legs_for_del;
818 END IF;
819 --fetch delivery details and containers for non empty deliveries
820 IF x_tbl_delivery_purge_set(l_loop_index).purge_set_type = 'NON_EMPTY' THEN
821 OPEN c_details_for_del(l_delivery_id);
822 LOOP
823 FETCH c_details_for_del INTO l_detail_id,l_container_flag;
824 EXIT WHEN c_details_for_del%NOTFOUND;
825 --l_container_flag = 'Y' would be Container
826 IF l_container_flag = 'N' THEN -- Delivery Detail
827 x_tbl_del_details_purge_set(x_tbl_del_details_purge_set.COUNT+1).delivery_detail_id := l_detail_id;
828 ELSE -- Container
829 x_tbl_containers_purge_set(x_tbl_containers_purge_set.COUNT+1).container_id := l_detail_id;
830 x_tbl_containers_purge_set(x_tbl_containers_purge_set.COUNT).purge_set_type := 'NON_EMPTY';
831 END IF;
832 END LOOP;
833 CLOSE c_details_for_del;
834 END IF;
835
836 --fetch empty containers for deliveries
837 /* IF x_tbl_delivery_purge_set(l_loop_index).purge_set_type = 'DEL_EMPTYLPNS' THEN
838 OPEN c_details_for_del(l_delivery_id);
839 LOOP
840 FETCH c_details_for_del INTO l_detail_id,l_container_flag;
841 EXIT WHEN c_details_for_del%NOTFOUND;
842 x_tbl_containers_purge_set(x_tbl_containers_purge_set.COUNT+1).container_id := l_detail_id;
843 x_tbl_containers_purge_set(x_tbl_containers_purge_set.COUNT).purge_set_type := 'EMPTYLPNS';
844 END LOOP;
845 CLOSE c_details_for_del;
846 END IF; */
847 END LOOP;
848 END IF;
849
850 IF l_debug_on THEN
851 WSH_DEBUG_SV.pop(l_module_name);
852 END IF;
853
854 EXCEPTION
855 WHEN OTHERS THEN
856 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
857 IF l_debug_on THEN
858 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
859 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
860 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
861 END IF;
862
863 END Get_Purge_Set;
864
865
866 /*-----------------------------------------------------------------------------
867
868 Procedure: Purge_Entities
869 Parameters: p_tbl_trip_purge_set - pl/sql table of trip id's eligible for purge
870 p_tbl_delivery_purge_set - pl/sql table of delivery id's eligible for purge
871 p_tbl_del_details_purge_set - pl/sql table of delivery detail id's eligible for purge
872 p_tbl_del_legs_purge_set - pl/sql table of delivery leg id's eligible for purge
873 p_tbl_trip_stops_purge_set - pl/sql table of trip stop id's eligible for purge
874 p_tbl_containers_purge_set pl/sql - table of container id's eligible for purge
875 x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
876
877 Description: This API calls the individual APIs to delete the data in
878 Shipping and Transportation tables
879 =============================================================================
880 Input: Table of Record Types for Trips, Stops, Legs, Deliveries, Containers, Details
881 Output: Return Status - success or failure
882 ==============================================================================
883 Logic: i) Call Purge_Delivery_Details
884 ii) Call Purge_Containers
885 iii) Call Purge_Delivery_Legs
886 iv) Call Purge_Trip_Stops
887 v) Call Purge_Deliveries
888 vi) Call Purge_Trips
889 -----------------------------------------------------------------------------*/
890
891 PROCEDURE Purge_Entities( p_tbl_trip_purge_set Trip_ID_Tbl_Type ,
892 p_tbl_delivery_purge_set Delivery_ID_Tbl_Type,
893 p_tbl_del_details_purge_set Del_Detail_ID_Tbl_Type,
894 p_tbl_del_legs_purge_set Del_Leg_ID_Tbl_Type,
895 p_tbl_trip_stops_purge_set Trip_Stop_ID_Tbl_Type,
896 p_tbl_containers_purge_set Container_ID_Tbl_Type,
897 x_return_status OUT NOCOPY VARCHAR2
898 )IS
899 l_debug_on BOOLEAN;
900 l_return_status VARCHAR2(1);
901
902 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_ENTITIES';
903 BEGIN
904
905 -- Debug Statements
906 --
907 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
908 --
909 IF l_debug_on IS NULL THEN
910 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
911 END IF;
912
913 IF l_debug_on THEN
914 WSH_DEBUG_SV.push(l_module_name);
915 END IF;
916
917 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
918
919 IF p_tbl_del_details_purge_set.COUNT > 0 THEN
920 IF l_debug_on THEN
921 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_DELIVERY_DETAILS', WSH_DEBUG_SV.C_PROC_LEVEL);
922 END IF;
923 --Purge Delivery Details
924 Purge_Delivery_Details( p_tbl_del_detail_purge_set => p_tbl_del_details_purge_set,
925 x_return_status => l_return_status);
926
927 IF l_debug_on THEN
928 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
929 END IF;
930
931 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
932 x_return_status := l_return_status;
933 IF l_debug_on THEN
934 WSH_DEBUG_SV.pop(l_module_name);
935 END IF;
936 RETURN;
937 END IF;
938 END IF;
939
940 IF p_tbl_containers_purge_set.COUNT > 0 THEN
941 IF l_debug_on THEN
942 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_CONTAINERS', WSH_DEBUG_SV.C_PROC_LEVEL);
943 END IF;
944 --Purge Containers
945 Purge_Containers(p_tbl_containers_purge_set => p_tbl_containers_purge_set,
946 x_return_status => l_return_status);
947
948 IF l_debug_on THEN
949 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
950 END IF;
951
952 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
953 x_return_status := l_return_status;
954 IF l_debug_on THEN
955 WSH_DEBUG_SV.pop(l_module_name);
956 END IF;
957 RETURN;
958 END IF;
959 END IF;
960
961 IF p_tbl_del_legs_purge_set.COUNT > 0 THEN
962 IF l_debug_on THEN
963 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_DELIVERY_LEGS', WSH_DEBUG_SV.C_PROC_LEVEL);
964 END IF;
965 --Purge Delivery Legs
966 Purge_Delivery_Legs(p_tbl_del_leg_purge_set => p_tbl_del_legs_purge_set,
967 x_return_status => l_return_status);
968
969 IF l_debug_on THEN
970 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
971 END IF;
972
973 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
974 x_return_status := l_return_status;
975 IF l_debug_on THEN
976 WSH_DEBUG_SV.pop(l_module_name);
977 END IF;
978 RETURN;
979 END IF;
980 END IF;
981
982 IF p_tbl_trip_stops_purge_set.COUNT > 0 THEN
983 IF l_debug_on THEN
984 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_TRIP_STOPS', WSH_DEBUG_SV.C_PROC_LEVEL);
985 END IF;
986 --Purge Trip Stops
987 Purge_Trip_Stops(p_tbl_trip_stop_purge_set => p_tbl_trip_stops_purge_set,
988 x_return_status => l_return_status);
989
990 IF l_debug_on THEN
991 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
992 END IF;
993
994 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
995 x_return_status := l_return_status;
996 IF l_debug_on THEN
997 WSH_DEBUG_SV.pop(l_module_name);
998 END IF;
999 RETURN;
1000 END IF;
1001 END IF;
1002
1003 IF (p_tbl_delivery_purge_set.COUNT > 0 OR p_tbl_trip_purge_set.COUNT > 0) THEN
1004
1005 IF l_debug_on THEN
1006 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_WORKFLOW', WSH_DEBUG_SV.C_PROC_LEVEL);
1007 END IF;
1008 --Purge workflows related to Trips and Deliveries
1009 Purge_Workflow( p_tbl_trip_purge_set => p_tbl_trip_purge_set,
1010 p_tbl_delivery_purge_set=> p_tbl_delivery_purge_set,
1011 x_return_status => l_return_status);
1012
1013 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1014 x_return_status := l_return_status;
1015 IF l_debug_on THEN
1016 WSH_DEBUG_SV.pop(l_module_name);
1017 END IF;
1018 RETURN;
1019 END IF;
1020 END IF;
1021
1022 IF p_tbl_delivery_purge_set.COUNT > 0 THEN
1023 IF l_debug_on THEN
1024 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_DELIVERIES', WSH_DEBUG_SV.C_PROC_LEVEL);
1025 END IF;
1026 --Purge Deliveries
1027 Purge_Deliveries(p_tbl_delivery_purge_set => p_tbl_delivery_purge_set,
1028 x_return_status => l_return_status);
1029
1030 IF l_debug_on THEN
1031 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
1032 END IF;
1033
1034 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1035 x_return_status := l_return_status;
1036 IF l_debug_on THEN
1037 WSH_DEBUG_SV.pop(l_module_name);
1038 END IF;
1039 RETURN;
1040 END IF;
1041 END IF;
1042
1043 IF p_tbl_trip_purge_set.COUNT > 0 THEN
1044 IF l_debug_on THEN
1045 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_PURGE.PURGE_TRIPS', WSH_DEBUG_SV.C_PROC_LEVEL);
1046 END IF;
1047 --Purge Trips
1048 Purge_Trips(p_tbl_trip_purge_set => p_tbl_trip_purge_set,
1049 x_return_status => l_return_status);
1050
1051 IF l_debug_on THEN
1052 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
1053 END IF;
1054
1055 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1056 x_return_status := l_return_status;
1057 IF l_debug_on THEN
1058 WSH_DEBUG_SV.pop(l_module_name);
1059 END IF;
1060 RETURN;
1061 END IF;
1062 END IF;
1063
1064 IF l_debug_on THEN
1065 WSH_DEBUG_SV.pop(l_module_name);
1066 END IF;
1067
1068
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1072 IF l_debug_on THEN
1073 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1074 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1075 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1076 END IF;
1077
1078
1079 END Purge_Entities;
1080
1081
1082 /*-----------------------------------------------------------------------------
1083 Procedure: Purge_Trips
1084 Parameters: p_tbl_trip_purge_set pl/sql table of trip id's eligible for purge
1085 x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1086
1087 Description: This API delete the data in Shipping and Transportation
1088 related to trip
1089 ==============================================================================
1090 Input: Table of Record Types for Trips
1091 Output: Return Status - success or failure
1092 ==============================================================================
1093 Logic: i) Delete records from the following tables:
1094 WSH_EXCEPTIONS, WSH_FREIGHT_COSTS, WSH_DOCUMENT_INSTANCES,WSH_TRIPS
1095 -----------------------------------------------------------------------------*/
1096
1097 PROCEDURE Purge_Trips( p_tbl_trip_purge_set Trip_ID_Tbl_Type,
1098 x_return_status OUT NOCOPY VARCHAR2
1099 )IS
1100
1101 l_debug_on BOOLEAN;
1102 l_loop_index NUMBER;
1103 l_trip_id NUMBER;
1104
1105 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_TRIPS';
1106 BEGIN
1107
1108 -- Debug Statements
1109 --
1110 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1111 --
1112 IF l_debug_on IS NULL THEN
1113 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1114 END IF;
1115
1116 IF l_debug_on THEN
1117 WSH_DEBUG_SV.push(l_module_name);
1118 END IF;
1119
1120 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1121
1122 FOR l_loop_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
1123 LOOP
1124 l_trip_id := p_tbl_trip_purge_set(l_loop_index).trip_id;
1125
1126 DELETE
1127 FROM wsh_exceptions
1128 WHERE trip_id = l_trip_id;
1129
1130 IF SQL%FOUND THEN
1131 IF l_debug_on THEN
1132 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1133 END IF;
1134 END IF;
1135
1136 DELETE
1137 FROM wsh_freight_costs
1138 WHERE trip_id = l_trip_id;
1139
1140 IF SQL%FOUND THEN
1141 IF l_debug_on THEN
1142 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1143 END IF;
1144 END IF;
1145
1146 DELETE
1147 FROM wsh_document_instances
1148 WHERE entity_id = l_trip_id
1149 AND entity_name = 'WSH_TRIPS';
1150
1151 IF SQL%FOUND THEN
1152 IF l_debug_on THEN
1153 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_document_instances: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1154 END IF;
1155 END IF;
1156
1157 DELETE
1158 FROM wsh_trips
1159 WHERE trip_id = l_trip_id;
1160
1161 IF SQL%FOUND THEN
1162 IF l_debug_on THEN
1163 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_trips: TRIP_ID=' || l_trip_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1164 END IF;
1165 END IF;
1166
1167 END LOOP;
1168
1169 IF l_debug_on THEN
1170 WSH_DEBUG_SV.pop(l_module_name);
1171 END IF;
1172
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1176 IF l_debug_on THEN
1177 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1178 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1179 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1180 END IF;
1181
1182 END Purge_Trips;
1183
1184
1185 /*-----------------------------------------------------------------------------
1186 Procedure: Purge_Deliveries
1187 Parameters: p_tbl_delivery_purge_set pl/sql table of delivery id's eligible for purge
1188 x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1189
1190 Description: This API delete the data in Shipping and Transportation
1191 related to delivery
1192 =============================================================================+
1193 Input: Table of Record Types for Deliveries
1194 Output: Return Status - success or failure
1195 ==============================================================================
1196 Logic: i) Delete records from the following tables:
1197 WSH_EXCEPTIONS, WSH_TRANSACTIONS_HISTORY, WSH_DOCUMENT_INSTANCES,
1198 WSH_FREIGHT_COSTS
1199
1200 If FTE is installed,
1201 FTE_SHIPMENT_STATUS_DETAILS, FTE_SHIPMENT_STATUS_EXCEPTIONS,
1202 FTE_MESSAGE_PARTNER, FTE_MESSAGE_CONTACT, FTE_MESSAGE_LOCATION,
1203 FTE_DELIVERY_PROOF, FTE_SHIPMENT_STATUS_HEADERS
1204
1205 If ITM Screening is done,
1206 WSH_ITM_RESPONSE_LINES, WSH_ITM_RESPONSE_HEADERS, WSH_ITM_REQUEST_CONTROL,
1207 WSH_INBOUND_TXN_HISTORY
1208 and finally WSH_NEW_DELIVERIES.
1209 -----------------------------------------------------------------------------*/
1210
1211 PROCEDURE Purge_Deliveries( p_tbl_delivery_purge_set Delivery_ID_Tbl_Type,
1212 x_return_status OUT NOCOPY VARCHAR2
1213 )IS
1214 l_debug_on BOOLEAN;
1215 l_loop_index NUMBER;
1216 l_delivery_id NUMBER;
1217
1218 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_DELIVERIES';
1219 BEGIN
1220
1221 -- Debug Statements
1222 --
1223 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1224 --
1225 IF l_debug_on IS NULL THEN
1226 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1227 END IF;
1228
1229 IF l_debug_on THEN
1230 WSH_DEBUG_SV.push(l_module_name);
1231 END IF;
1232
1233 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1234
1235 FOR l_loop_index in p_tbl_delivery_purge_set.FIRST .. p_tbl_delivery_purge_set.LAST
1236 LOOP
1237
1238 l_delivery_id := p_tbl_delivery_purge_set(l_loop_index).delivery_id;
1239
1240 DELETE
1241 FROM wsh_exceptions
1242 WHERE delivery_id = l_delivery_id;
1243
1244 IF SQL%FOUND THEN
1245 IF l_debug_on THEN
1246 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1247 END IF;
1248 END IF;
1249
1250 DELETE
1251 FROM wsh_transactions_history
1252 WHERE entity_number = to_char(l_delivery_id)
1253 AND entity_type = 'DLVY';
1254
1255 IF SQL%FOUND THEN
1256 IF l_debug_on THEN
1257 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_transactions_history: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1258 END IF;
1259 END IF;
1260
1261 DELETE
1262 FROM wsh_freight_costs
1263 WHERE delivery_id = l_delivery_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: DELIVERY_ID=' || l_delivery_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_delivery_id
1274 AND entity_name = 'WSH_NEW_DELIVERIES';
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: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1279 END IF;
1280 END IF;
1281
1282 IF (wsh_util_core.fte_is_installed='Y') THEN
1283
1284 DELETE
1285 FROM fte_shipment_status_details
1286 WHERE transaction_id IN (SELECT transaction_id
1287 FROM fte_shipment_status_headers
1288 WHERE delivery_id = l_delivery_id);
1289
1290 IF SQL%FOUND THEN
1291 IF l_debug_on THEN
1292 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_shipment_status_details: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1293 END IF;
1294 END IF;
1295
1296 DELETE
1297 FROM fte_shipment_status_exceptions
1298 WHERE transaction_id IN (SELECT transaction_id
1299 FROM fte_shipment_status_headers
1300 WHERE delivery_id = l_delivery_id);
1301
1302 IF SQL%FOUND THEN
1303 IF l_debug_on THEN
1304 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_shipment_status_exceptions: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1305 END IF;
1306 END IF;
1307
1308 DELETE
1309 FROM fte_message_partner
1310 WHERE transaction_id IN (SELECT transaction_id
1311 FROM fte_shipment_status_headers
1312 WHERE delivery_id = l_delivery_id);
1313
1314 IF SQL%FOUND THEN
1315 IF l_debug_on THEN
1316 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_partner: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1317 END IF;
1318 END IF;
1319
1320 DELETE
1321 FROM fte_message_address
1322 WHERE transaction_id IN (SELECT transaction_id
1323 FROM fte_shipment_status_headers
1324 WHERE delivery_id = l_delivery_id);
1325
1326 IF SQL%FOUND THEN
1327 IF l_debug_on THEN
1328 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_address: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1329 END IF;
1330 END IF;
1331
1332 DELETE
1333 FROM fte_message_contact
1334 WHERE transaction_id IN (SELECT transaction_id
1335 FROM fte_shipment_status_headers
1336 WHERE delivery_id = l_delivery_id);
1337
1338 IF SQL%FOUND THEN
1339 IF l_debug_on THEN
1340 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_contact: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1341 END IF;
1342 END IF;
1343
1344 DELETE
1345 FROM fte_message_location
1346 WHERE transaction_id IN (SELECT transaction_id
1347 FROM fte_shipment_status_headers
1348 WHERE delivery_id = l_delivery_id);
1349
1350 IF SQL%FOUND THEN
1351 IF l_debug_on THEN
1352 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_message_location: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1353 END IF;
1354 END IF;
1355
1356
1357 DELETE
1358 FROM fte_delivery_proof
1359 WHERE transaction_id IN (SELECT transaction_id
1360 FROM fte_shipment_status_headers
1361 WHERE delivery_id = l_delivery_id);
1362
1363 IF SQL%FOUND THEN
1364 IF l_debug_on THEN
1365 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_delivery_proof: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1366 END IF;
1367 END IF;
1368
1369
1370 DELETE
1371 FROM fte_shipment_status_headers
1372 WHERE delivery_id = l_delivery_id;
1373
1374
1375 IF SQL%FOUND THEN
1376 IF l_debug_on THEN
1377 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_shipment_status_headers: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1378 END IF;
1379 END IF;
1380 END IF;
1381
1382 DELETE
1383 FROM wsh_itm_response_lines
1384 WHERE response_header_id IN ( SELECT wirh.response_header_id
1385 FROM wsh_itm_response_headers wirh,
1386 wsh_itm_request_control wirc
1387 WHERE wirc.original_system_reference = l_delivery_id
1388 AND wirc.request_control_id = wirh.request_control_id
1389 AND wirc.service_type_code = 'WSH_EXPORT_COMPLIANCE' );
1390
1391 IF SQL%FOUND THEN
1392 IF l_debug_on THEN
1393 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_itm_response_lines: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1394 END IF;
1395 END IF;
1396
1397 DELETE
1398 FROM wsh_itm_response_headers
1399 WHERE request_control_id IN ( SELECT request_control_id
1400 FROM wsh_itm_request_control
1401 WHERE original_system_reference = l_delivery_id
1402 AND service_type_code = 'WSH_EXPORT_COMPLIANCE' ) ;
1403
1404 IF SQL%FOUND THEN
1405 IF l_debug_on THEN
1406 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_itm_response_headers: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1407 END IF;
1408 END IF;
1409
1410 DELETE
1411 FROM wsh_itm_request_control
1412 WHERE original_system_reference = l_delivery_id
1413 AND service_type_code = 'WSH_EXPORT_COMPLIANCE';
1414
1415 IF SQL%FOUND THEN
1416 IF l_debug_on THEN
1417 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_itm_request_control: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1418 END IF;
1419 END IF;
1420
1421 DELETE
1422 FROM wsh_inbound_txn_history
1423 WHERE shipment_header_id IN ( SELECT rcv_shipment_header_id
1424 FROM wsh_new_deliveries
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 wsh_inbound_txn_history: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1430 END IF;
1431 END IF;
1432
1433 DELETE
1434 FROM wsh_new_deliveries
1435 WHERE delivery_id = l_delivery_id;
1436
1437 IF SQL%FOUND THEN
1438 IF l_debug_on THEN
1439 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_new_deliveries: DELIVERY_ID=' || l_delivery_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1440 END IF;
1441 END IF;
1442
1443 END LOOP;
1444
1445 IF l_debug_on THEN
1446 WSH_DEBUG_SV.pop(l_module_name);
1447 END IF;
1448
1449 EXCEPTION
1450 WHEN OTHERS THEN
1451 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1452 IF l_debug_on THEN
1453 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1454 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1455 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1456 END IF;
1457
1458 END Purge_Deliveries;
1459
1460
1461 /*-----------------------------------------------------------------------------
1462 Procedure: Purge_Trip_Stops
1463 Parameters: p_tbl_trip_stop_purge_set pl/sql table of trip stop id's
1464 eligible for purge
1465 x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1466
1467 Description: This API delete the data in Shipping and Transportation
1468 related to trip stop
1469 ==============================================================================
1470 Input: Table of Record Types for Trip Stops
1471 Output: Return Status - success or failure
1472 ==============================================================================
1473 Logic: i) Delete records from the following tables:
1474 WSH_EXCEPTIONS, WSH_FREIGHT_COSTS, WSH_TRIP_STOPS
1475 -----------------------------------------------------------------------------*/
1476
1477 PROCEDURE Purge_Trip_Stops( p_tbl_trip_stop_purge_set Trip_Stop_ID_Tbl_Type,
1478 x_return_status OUT NOCOPY VARCHAR2
1479 )IS
1480 l_debug_on BOOLEAN;
1481 l_loop_index NUMBER;
1482 l_stop_id NUMBER;
1483
1484 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_TRIP_STOPS';
1485 BEGIN
1486
1487 -- Debug Statements
1488 --
1489 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1490 --
1491 IF l_debug_on IS NULL THEN
1492 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1493 END IF;
1494
1495 IF l_debug_on THEN
1496 WSH_DEBUG_SV.push(l_module_name);
1497 END IF;
1498
1499 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1500
1501 FOR l_loop_index in p_tbl_trip_stop_purge_set.FIRST .. p_tbl_trip_stop_purge_set.LAST
1502 LOOP
1503 l_stop_id := p_tbl_trip_stop_purge_set(l_loop_index).stop_id;
1504
1505 DELETE
1506 FROM wsh_exceptions
1507 WHERE trip_stop_id = l_stop_id;
1508
1509 IF SQL%FOUND THEN
1510 IF l_debug_on THEN
1511 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: STOP_ID=' || l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1512 END IF;
1513 END IF;
1514
1515 DELETE
1516 FROM wsh_freight_costs
1517 WHERE stop_id = l_stop_id;
1518
1519 IF SQL%FOUND THEN
1520 IF l_debug_on THEN
1521 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: STOP_ID=' || l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1522 END IF;
1523 END IF;
1524
1525 DELETE
1526 FROM wsh_trip_stops
1527 WHERE stop_id = l_stop_id;
1528
1529 IF SQL%FOUND THEN
1530 IF l_debug_on THEN
1531 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_trip_stops: STOP_ID=' || l_stop_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1532 END IF;
1533 END IF;
1534
1535 END LOOP;
1536
1537 IF l_debug_on THEN
1538 WSH_DEBUG_SV.pop(l_module_name);
1539 END IF;
1540
1541 EXCEPTION
1542 WHEN OTHERS THEN
1543 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1544 IF l_debug_on THEN
1545 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1546 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1547 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1548 END IF;
1549
1550 END Purge_Trip_Stops;
1551
1552
1553 /*-----------------------------------------------------------------------------
1554 Procedure: Purge_Delivery_Legs
1555 Parameters: p_tbl_del_leg_purge_set pl/sql table of delivery leg id's eligible for purge
1556 x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1557
1558 Description: This API delete the data in Shipping and Transportation
1559 related to delivery leg
1560 ==============================================================================
1561 Input: Table of Record Types for Delivery Legs
1562 Output: Return Status - success or failure
1563 ==============================================================================
1564 Logic: i) Delete records from the following tables:
1565 WSH_FREIGHT_COSTS, WSH_DOCUMENT_INSTANCES, WSH_DELIVERY_LEG_ACTIVITIES,
1566 WSH_DELIVERY_LEG_DETAILS, WSH_DELIVERY_LEGS
1567
1568 If FTE is installed,
1569 FTE_INVOICE_LINES, FTE_INVOICE_HISTORY, FTE_INVOICE_HEADERS,
1570 FTE_FAILURE_REASONS
1571 -----------------------------------------------------------------------------*/
1572
1573 PROCEDURE Purge_Delivery_Legs( p_tbl_del_leg_purge_set Del_Leg_ID_Tbl_Type,
1574 x_return_status OUT NOCOPY VARCHAR2
1575 )IS
1576 l_debug_on BOOLEAN;
1577 l_loop_index NUMBER;
1578 l_leg_id NUMBER;
1579
1580 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_DELIVERY_LEGS';
1581 BEGIN
1582 -- Debug Statements
1583 --
1584 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1585 --
1586 IF l_debug_on IS NULL THEN
1587 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1588 END IF;
1589
1590 IF l_debug_on THEN
1591 WSH_DEBUG_SV.push(l_module_name);
1592 END IF;
1593
1594 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1595
1596 FOR l_loop_index in p_tbl_del_leg_purge_set.FIRST .. p_tbl_del_leg_purge_set.LAST
1597 LOOP
1598 l_leg_id := p_tbl_del_leg_purge_set(l_loop_index).delivery_leg_id;
1599
1600 DELETE
1601 FROM wsh_freight_costs
1602 WHERE delivery_leg_id = l_leg_id;
1603
1604 IF SQL%FOUND THEN
1605 IF l_debug_on THEN
1606 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1607 END IF;
1608 END IF;
1609
1610 IF (wsh_util_core.fte_is_installed='Y') THEN
1611
1612 DELETE
1613 FROM fte_invoice_lines
1614 WHERE invoice_header_id IN ( SELECT fih.invoice_header_id
1615 FROM fte_invoice_headers fih,
1616 wsh_document_instances wdi
1617 WHERE wdi.entity_id = l_leg_id
1618 AND wdi.entity_name = 'WSH_DELIVERY_LEGS'
1619 AND wdi.sequence_number = fih.bol
1620 ) ;
1621 IF SQL%FOUND THEN
1622 IF l_debug_on THEN
1623 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_invoice_lines: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1624 END IF;
1625 END IF;
1626
1627 DELETE
1628 FROM fte_invoice_headers
1629 WHERE bol IN (SELECT sequence_number
1630 FROM wsh_document_instances
1631 WHERE entity_id = l_leg_id
1632 AND entity_name = 'WSH_DELIVERY_LEGS') ;
1633
1634 IF SQL%FOUND THEN
1635 IF l_debug_on THEN
1636 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_invoice_headers: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1637 END IF;
1638 END IF;
1639
1640 DELETE
1641 FROM fte_invoice_history
1642 WHERE bol IN (SELECT sequence_number
1643 FROM wsh_document_instances
1644 WHERE entity_id = l_leg_id
1645 AND entity_name = 'WSH_DELIVERY_LEGS') ;
1646
1647 IF SQL%FOUND THEN
1648 IF l_debug_on THEN
1649 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_invoice_history: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1650 END IF;
1651 END IF;
1652
1653 DELETE
1654 FROM fte_failure_reasons
1655 WHERE bol IN ( SELECT sequence_number
1656 FROM wsh_document_instances
1657 WHERE entity_id = l_leg_id
1658 AND entity_name = 'WSH_DELIVERY_LEGS') ;
1659
1660 IF SQL%FOUND THEN
1661 IF l_debug_on THEN
1662 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from fte_failure_reasons: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1663 END IF;
1664 END IF;
1665
1666 END IF;
1667
1668 DELETE
1669 FROM wsh_document_instances
1670 WHERE entity_id = l_leg_id
1671 AND entity_name = 'WSH_DELIVERY_LEGS';
1672
1673 IF SQL%FOUND THEN
1674 IF l_debug_on THEN
1675 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_document_instances: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1676 END IF;
1677 END IF;
1678
1679 DELETE
1680 FROM wsh_delivery_leg_activities
1681 WHERE delivery_leg_id = l_leg_id ;
1682
1683 IF SQL%FOUND THEN
1684 IF l_debug_on THEN
1685 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_leg_activities: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1686 END IF;
1687 END IF;
1688
1689 DELETE
1690 FROM wsh_delivery_leg_details
1691 WHERE delivery_leg_id = l_leg_id;
1692
1693 IF SQL%FOUND THEN
1694 IF l_debug_on THEN
1695 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_leg_details: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1696 END IF;
1697 END IF;
1698
1699
1700 DELETE
1701 FROM wsh_delivery_legs
1702 WHERE delivery_leg_id = l_leg_id ;
1703
1704 IF SQL%FOUND THEN
1705 IF l_debug_on THEN
1706 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_legs: LEG_ID=' || l_leg_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1707 END IF;
1708 END IF;
1709 END LOOP;
1710
1711 IF l_debug_on THEN
1712 WSH_DEBUG_SV.pop(l_module_name);
1713 END IF;
1714
1715 EXCEPTION
1716 WHEN OTHERS THEN
1717 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1718 IF l_debug_on THEN
1719 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1720 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1721 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1722 END IF;
1723
1724 END Purge_Delivery_Legs;
1725
1726
1727 /*-----------------------------------------------------------------------------
1728 Procedure: Purge_Delivery_Details
1729 Parameters: p_tbl_del_detail_purge_set pl/sql table of delivery detail id's
1730 eligible for purge
1731 x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1732
1733 Description: This API delete the data in Shipping and Transportation
1734 related to delivery detail
1735 =============================================================================
1736 Input: Table of Record Types for Delivery Details
1737 Output: Return Status - success or failure
1738 ==============================================================================
1739 Logic: i) Delete records from the following tables:
1740 WSH_FREIGHT_COSTS, WSH_SERIAL_NUMBERS, WSH_EXCEPTIONS,
1741 wsh_delivery_assignments_v, WSH_DELIVERY_DETAILS
1742 -----------------------------------------------------------------------------*/
1743 PROCEDURE Purge_Delivery_Details(p_tbl_del_detail_purge_set Del_Detail_ID_Tbl_Type,
1744 x_return_status OUT NOCOPY VARCHAR2
1745 )IS
1746 l_debug_on BOOLEAN;
1747 l_loop_index NUMBER;
1748 l_detail_id NUMBER;
1749
1750 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_DELIVERY_DETAILS';
1751 BEGIN
1752 -- Debug Statements
1753 --
1754 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1755 --
1756 IF l_debug_on IS NULL THEN
1757 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1758 END IF;
1759
1760 IF l_debug_on THEN
1761 WSH_DEBUG_SV.push(l_module_name);
1762 END IF;
1763
1764 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1765
1766 FOR l_loop_index in p_tbl_del_detail_purge_set.FIRST .. p_tbl_del_detail_purge_set.LAST
1767 LOOP
1768 l_detail_id := p_tbl_del_detail_purge_set(l_loop_index).delivery_detail_id;
1769
1770 DELETE
1771 FROM wsh_serial_numbers
1772 WHERE delivery_detail_id = l_detail_id;
1773
1774 IF SQL%FOUND THEN
1775 IF l_debug_on THEN
1776 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_serial_numbers: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1777 END IF;
1778 END IF;
1779
1780 DELETE
1781 FROM wsh_exceptions
1782 WHERE delivery_detail_id = l_detail_id;
1783
1784 IF SQL%FOUND THEN
1785 IF l_debug_on THEN
1786 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1787 END IF;
1788 END IF;
1789
1790 DELETE
1791 FROM wsh_freight_costs
1792 WHERE delivery_detail_id = l_detail_id;
1793
1794 IF SQL%FOUND THEN
1795 IF l_debug_on THEN
1796 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1797 END IF;
1798 END IF;
1799
1800 DELETE
1801 FROM wsh_delivery_assignments_v
1802 WHERE delivery_detail_id = l_detail_id;
1803
1804 IF SQL%FOUND THEN
1805 IF l_debug_on THEN
1806 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);
1807 END IF;
1808 END IF;
1809
1810 DELETE
1811 FROM wsh_delivery_details
1812 WHERE delivery_detail_id = l_detail_id;
1813
1814 IF SQL%FOUND THEN
1815 IF l_debug_on THEN
1816 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_details: DELIVERY_DETAIL_ID=' || l_detail_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1817 END IF;
1818 END IF;
1819 END LOOP;
1820
1821 IF l_debug_on THEN
1822 WSH_DEBUG_SV.pop(l_module_name);
1823 END IF;
1824
1825 EXCEPTION
1826 WHEN OTHERS THEN
1827 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1828 IF l_debug_on THEN
1829 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1830 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1831 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1832 END IF;
1833
1834 END Purge_Delivery_Details;
1835
1836
1837 /*-----------------------------------------------------------------------------
1838 Procedure: Purge_Containers
1839 Parameters: p_tbl_containers_purge_set pl/sql table of container id's eligible for purge
1840 x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
1841
1842 Description: This API calls the WMS API to delete the data in WMS
1843 related to Containers
1844 ==============================================================================
1845 Input: Table of Record Types for Container Ids
1846 Output: Return Status - success or failure
1847 ==============================================================================
1848 Logic: i) Delete records from the following tables:
1849 wsh_delivery_assignments_v, WSH_DELIVERY_DETAILS
1850 -----------------------------------------------------------------------------*/
1851 PROCEDURE Purge_Containers(p_tbl_containers_purge_set Container_ID_Tbl_Type,
1852 x_return_status OUT NOCOPY VARCHAR2
1853 )IS
1854 l_debug_on BOOLEAN;
1855 l_loop_index NUMBER;
1856 l_container_id NUMBER;
1857 l_lpn_id NUMBER;
1858 l_return_status VARCHAR2(1);
1859
1860 l_msg_count NUMBER;
1861 l_msg_data VARCHAR2(32767);
1862
1863 l_wms_lpn_record WMS_Data_Type_Definitions_PUB.LPNPurgeRecordType;
1864
1865 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_CONTAINERS';
1866 BEGIN
1867 -- Debug Statements
1868 --
1869 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1870 --
1871 IF l_debug_on IS NULL THEN
1872 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1873 END IF;
1874
1875 IF l_debug_on THEN
1876 WSH_DEBUG_SV.push(l_module_name);
1877 END IF;
1878
1879 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1880
1881 FOR l_loop_index in p_tbl_containers_purge_set.FIRST .. p_tbl_containers_purge_set.LAST
1882 LOOP
1883 l_container_id := p_tbl_containers_purge_set(l_loop_index).container_id;
1884
1885
1886 DELETE
1887 FROM wsh_exceptions
1888 WHERE delivery_detail_id = l_container_id;
1889
1890 IF SQL%FOUND THEN
1891 IF l_debug_on THEN
1892 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_exceptions: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1893 END IF;
1894 END IF;
1895
1896 DELETE
1897 FROM wsh_freight_costs
1898 WHERE delivery_detail_id = l_container_id;
1899
1900 IF SQL%FOUND THEN
1901 IF l_debug_on THEN
1902 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_freight_costs: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1903 END IF;
1904 END IF;
1905
1906 DELETE
1907 FROM wsh_delivery_assignments_v
1908 WHERE delivery_detail_id = l_container_id;
1909
1910 IF SQL%FOUND THEN
1911 IF l_debug_on THEN
1912 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_assignments_v: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1913 END IF;
1914 END IF;
1915
1916 DELETE
1917 FROM wsh_delivery_details
1918 WHERE delivery_detail_id = l_container_id
1919 RETURNING lpn_id INTO l_lpn_id;
1920
1921 IF SQL%FOUND THEN
1922 IF l_debug_on THEN
1923 WSH_DEBUG_SV.logmsg(l_module_name,'Deleted from wsh_delivery_details: CONTAINER_ID=' || l_container_id, WSH_DEBUG_SV.C_PROC_LEVEL);
1924 END IF;
1925 END IF;
1926
1927 IF l_lpn_id IS NOT NULL THEN -- Populate the LPN IDs in the table
1928 l_wms_lpn_record.LPN_IDs(l_wms_lpn_record.LPN_IDs.COUNT+1) := l_lpn_id;
1929 END IF;
1930 END LOOP;
1931
1932 --Call the WMS API to DELETE the LPNs
1933 IF l_debug_on THEN
1934 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_CONTAINER_GRP.LPN_PURGE_ACTIONS', WSH_DEBUG_SV.C_PROC_LEVEL);
1935 END IF;
1936
1937 WMS_Container_GRP.LPN_Purge_Actions( p_api_version =>1.0,
1938 p_init_msg_list =>FND_API.G_TRUE,
1939 p_commit =>'FALSE',
1940 x_return_status =>l_return_status,
1941 x_msg_count =>l_msg_count,
1942 x_msg_data =>l_msg_data,
1943 p_caller =>'WSH',
1944 p_action =>WMS_Container_GRP.G_LPN_PURGE_ACTION_DELETE,
1945 p_lpn_purge_rec => l_wms_lpn_record
1946 );
1947
1948 IF l_debug_on THEN
1949 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
1950 WSH_DEBUG_SV.log(l_module_name,'L_MSG_DATA',l_msg_data);
1951 END IF;
1952
1953 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1954 x_return_status := l_return_status;
1955 IF l_debug_on THEN
1956 WSH_DEBUG_SV.pop(l_module_name);
1957 END IF;
1958 RETURN;
1959 END IF;
1960
1961 IF l_debug_on THEN
1962 WSH_DEBUG_SV.pop(l_module_name);
1963 END IF;
1964
1965 EXCEPTION
1966 WHEN OTHERS THEN
1967 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
1968 IF l_debug_on THEN
1969 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
1970 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
1971 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
1972 END IF;
1973
1974 END Purge_Containers;
1975
1976 -----------------------------------------------------------------------------
1977 --
1978 -- Procedure: Generate_Purge_Report
1979 -- Parameters: p_execution_mode Specifies whether to Purge Data or View Purge Set
1980 -- p_source_system Only the delivery details belonging to this Source System
1981 -- would be considered eligible for Purge
1982 -- p_ship_from_org Only the deliveries belonging to this Ship From Org
1983 -- would be considered eligible for Purge
1984 -- p_order_number_from Only the delivery details having source_header_number
1985 -- greater than Order Number From would be considered eligible for Purge
1986 -- p_order_number_to Only the delivery details having source_header_number
1987 -- less than Order Number To would be considered eligible for Purge
1988 -- p_order_type Only the delivery details belonging to this Order Type
1989 -- would be considered eligible for Purge
1990 -- p_ship_date_from Only the deliveries having initial_pickup_date greater
1991 -- than Ship Date From would be considered eligible for Purge
1992 -- p_ship_date_to Only the deliveries having initial_pickup_date less than
1993 -- Ship Date To would be considered eligible for Purge
1994 -- p_delete_beyond_x_ship_days Only the deliveries having initial_pickup_date greater
1995 -- than the specified date would be considered eligible for Purge
1996 -- p_purge_intransit_trips Decides whether to purge In Transit Trips or not
1997 -- p_delete_empty_records Decides whether to delete empty record or not.
1998 -- The empty records can be Empty Trips, Orphaned Empty Deliveries,
1999 -- Delivery with Empty containers, Empty Containers
2000 -- p_create_date_from Only Empty records having creation_date greater than this
2001 -- date would be purged
2002 -- p_create_date_to Only Empty records having creation_date less than this
2003 -- date would be purged
2004 -- p_del_beyond_creation_days Only Empty records having creation_date greater than
2005 -- this date would be purged
2006 -- p_sort_per_criteria Sorts the report output according to Trip,
2007 -- Delivery or Order Number
2008 -- p_print_detail If Low, the report would contain the parameters / summary
2009 -- page and all detail pages with Trips, Deliveries and
2010 -- Sales Orders data eligible to purge or purged.
2011 -- If No, the report would contain only the parameters / summary page.
2012 -- p_tbl_trip_purge_set pl/sql table of trip id's eligible for purge
2013 -- p_tbl_delivery_purge_set pl/sql table of delivery id's eligible for purge
2014 -- p_tbl_delivery_purge_set pl/sql table of container ids's eligible for purge
2015 -- p_count_legs count of delivery legs to be purged/eligible to be purged
2016 -- p_count_stops count of trip stops to be purged/eligible to be purged
2017 -- p_count_details count of delivery details to be purged/eligible to be purged
2018 -- p_count_containers count of containers to be purged/eligible to be purged
2019 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
2020
2021 -- Description: This API generates the XML and writes it in output file
2022 -- of the concurrent program to be used by the XML Publisher
2023 -- to generate the XML report
2024 -----------------------------------------------------------------------------
2025
2026 PROCEDURE Generate_Purge_Report(p_execution_mode varchar2,
2027 p_source_system varchar2,
2028 p_ship_from_org number,
2029 p_order_number_from varchar2,
2030 p_order_number_to varchar2,
2031 p_order_type number,
2032 p_ship_date_from varchar2,
2033 p_ship_date_to varchar2,
2034 p_delete_beyond_x_ship_days number,
2035 p_purge_intransit_trips varchar2,
2036 p_delete_empty_records varchar2,
2037 p_create_date_from varchar2,
2038 p_create_date_to varchar2,
2039 p_del_beyond_creation_days number,
2040 p_sort_per_criteria varchar2,
2041 p_print_detail varchar2,
2042 p_tbl_trip_purge_set Trip_ID_Tbl_Type ,
2043 p_tbl_delivery_purge_set Delivery_ID_Tbl_Type,
2044 p_tbl_container_purge_set Container_ID_Tbl_Type,
2045 p_count_legs NUMBER,
2046 p_count_stops NUMBER,
2047 p_count_details NUMBER,
2048 p_count_containers NUMBER,
2049 x_return_status OUT NOCOPY VARCHAR2
2050 )IS
2051 l_debug_on BOOLEAN;
2052
2053 l_trip_id NUMBER;
2054 l_delivery_id NUMBER;
2055 l_delivery_name VARCHAR2(30);
2056 l_trip_name VARCHAR2(30);
2057 l_sales_order VARCHAR2(150);
2058 l_bol_number NUMBER;
2059 l_container_id NUMBER;
2060 l_waybill VARCHAR2(30);
2061 l_gross_weight NUMBER;
2062 l_ship_to VARCHAR2(500);
2063 l_customer_name VARCHAR2(50);
2064 l_pickup_date DATE;
2065 l_dropoff_date DATE;
2066 l_ship_date DATE;
2067 l_order_type VARCHAR2(240);
2068 l_create_date DATE;
2069
2070 l_trip_index NUMBER;
2071 l_delivery_index NUMBER;
2072 l_sales_order_index NUMBER;
2073 l_container_index NUMBER;
2074
2075 l_nonempty_count NUMBER;
2076 l_lpn_count NUMBER;
2077 l_empty_trip_count NUMBER;
2078 l_empty_del_count NUMBER;
2079 l_empty_lpn_count NUMBER;
2080
2081 l_err varchar2(500);
2082
2083 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'GENERATE_PURGE_REPORT';
2084
2085 --Get Deliveries for Trips
2086 CURSOR c_dels_for_trip(p_tripid NUMBER) IS
2087 SELECT distinct del_id,
2088 del_name,
2089 del_waybill,
2090 del_gross_weight,
2091 del_ui_location_code,
2092 del_customer_name,
2093 del_pickup_date,
2094 del_dropoff_date,
2095 del_ship_date,
2096 bol
2097 FROM wsh_purge_set_v
2098 WHERE trip_id = p_tripid;
2099
2100 --Added hints to the query for bug 4891951
2101 CURSOR c_bols_for_del(p_delivery_id NUMBER) IS
2102 SELECT /*+use_nl(v.wda, v.wnd, v.wdl)*/ distinct v.bol
2103 FROM wsh_purge_set_v v
2104 WHERE v.del_id = p_delivery_id
2105 AND v.bol is not null;
2106
2107 --Get Sales Order Details for Deliveries
2108 --Added hints to the query for bug 4891951
2109 CURSOR c_so_for_delivery(p_delivery_id NUMBER) IS
2110 SELECT /*+use_nl(v.wda, v.wnd, v.wdl)*/ DISTINCT v.dd_source_header_number,
2111 v.dd_source_header_type_name--,
2112 -- dd_creation_date
2113 FROM wsh_purge_set_v v
2114 WHERE v.del_id = p_delivery_id
2115 AND v.dd_source_header_number is not null;
2116
2117 CURSOR c_dels_trips_for_order(p_ordernumber VARCHAR2) IS
2118 SELECT distinct trip_id,
2119 trip_name,
2120 del_id,
2121 del_name,
2122 del_waybill,
2123 del_gross_weight,
2124 del_ui_location_code,
2125 del_customer_name,
2126 del_pickup_date,
2127 del_dropoff_date,
2128 del_ship_date,
2129 bol
2130 FROM wsh_purge_set_v
2131 WHERE dd_source_header_number= p_ordernumber;
2132
2133 --Get Containers for Sales Orders
2134 /*CURSOR c_containers_for_so(order_number varchar2) IS
2135 SELECT distinct wda.parent_delivery_detail_id
2136 FROM wsh_delivery_assignments_v wda,
2137 wsh_delivery_details wdd
2138 WHERE wda.delivery_detail_id = wdd.delivery_detail_id
2139 AND wdd.source_header_number = order_number
2140 AND wda.parent_delivery_detail_id IS NOT NULL;
2141 */
2142
2143 /*CURSOR c_containers_for_so(order_number varchar2) IS
2144 SELECT DISTINCT wda.parent_delivery_detail_id
2145 FROM wsh_delivery_assignments_v wda ,wsh_Delivery_Details wdd
2146 WHERE wda.parent_delivery_detail_id is not null
2147 AND wdd.delivery_Detail_id = wda.delivery_detail_id
2148 CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id
2149 START WITH wdd.source_header_number =order_number;
2150 */
2151
2152 CURSOR c_containers_for_so(p_order_number varchar2) IS
2153 SELECT DISTINCT wda.parent_delivery_detail_id
2154 FROM wsh_delivery_assignments_v wda
2155 WHERE wda.parent_delivery_detail_id is not null
2156 CONNECT BY PRIOR wda.delivery_detail_id = wda.parent_delivery_detail_id
2157 START WITH wda.delivery_id IN (select wda1.delivery_id from
2158 wsh_delivery_assignments_v wda1, wsh_delivery_Details wdd
2159 WHERE wda1.delivery_Detail_id = wdd.delivery_Detail_id
2160 and wdd.source_header_number = p_order_number);
2161
2162 BEGIN
2163 -- Debug Statements
2164 --
2165 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2166 --
2167 IF l_debug_on IS NULL THEN
2168 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2169 END IF;
2170
2171 IF l_debug_on THEN
2172 WSH_DEBUG_SV.push(l_module_name);
2173 END IF;
2174
2175 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2176
2177 l_nonempty_count := 0;
2178 l_lpn_count := 0;
2179 l_empty_trip_count := 0;
2180 l_empty_del_count := 0;
2181 l_empty_lpn_count := 0;
2182
2183 IF l_debug_on THEN
2184 WSH_DEBUG_SV.log(l_module_name,'P_EXECUTION_MODE',p_execution_mode);
2185 WSH_DEBUG_SV.log(l_module_name,'P_SOURCE_SYSTEM',p_source_system);
2186 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_FROM_ORG',p_ship_from_org);
2187 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER_FROM',p_order_number_from);
2188 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_NUMBER_To',p_order_number_to);
2189 WSH_DEBUG_SV.log(l_module_name,'P_ORDER_TYPE',p_order_type);
2190 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE_FROM',p_ship_date_from);
2191 WSH_DEBUG_SV.log(l_module_name,'P_SHIP_DATE_TO',p_ship_date_to);
2192 WSH_DEBUG_SV.log(l_module_name,'P_DELETE_BEYOND_X_SHIP_DAYS',p_delete_beyond_x_ship_days);
2193 WSH_DEBUG_SV.log(l_module_name,'P_PURGE_INTRANSIT_TRIPS',p_purge_intransit_trips);
2194 WSH_DEBUG_SV.log(l_module_name,'P_DELETE_EMPTY_Records',p_delete_empty_records);
2195 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_DATE_FROM',p_create_date_from);
2196 WSH_DEBUG_SV.log(l_module_name,'P_CREATE_DATE_TO',p_create_date_to);
2197 WSH_DEBUG_SV.log(l_module_name,'P_DEL_BEYOND_CREATION_DAYS',p_del_beyond_creation_days);
2198 WSH_DEBUG_SV.log(l_module_name,'P_SORT_PER_CRITERIA',p_sort_per_criteria);
2199 WSH_DEBUG_SV.log(l_module_name,'P_PRINT_DETAIL',p_print_detail);
2200 WSH_DEBUG_SV.log(l_module_name,'P_COUNT_LEGS',p_count_legs);
2201 WSH_DEBUG_SV.log(l_module_name,'P_COUNT_STOPS',p_count_stops);
2202 WSH_DEBUG_SV.log(l_module_name,'P_COUNT_DETAILS',p_count_details);
2203 WSH_DEBUG_SV.log(l_module_name,'P_COUNT_ContainerS',p_count_containers);
2204 END IF;
2205
2206 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<?xml version="1.0" ?>');
2207 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ROWSET>');
2208 IF p_execution_mode = 'V' THEN
2209 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<EXEC_MODE>View Purge Selection</EXEC_MODE>');
2210 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<COUNT_HEADING>Eligible to Purge</COUNT_HEADING>');
2211 ELSE
2212 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<EXEC_MODE>Purge</EXEC_MODE>');
2213 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<COUNT_HEADING>Purged</COUNT_HEADING>)');
2214 END IF;
2215 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CURR_DATE>'|| SYSDATE ||'</CURR_DATE>');
2216
2217 IF (p_source_system = 'ALL') THEN
2218 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SOURCE_SYSTEM>All</SOURCE_SYSTEM>');
2219 ELSIF (p_source_system = 'OE') THEN
2220 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SOURCE_SYSTEM>Order Management</SOURCE_SYSTEM>');
2221 ELSIF (p_source_system = 'PO') THEN
2222 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SOURCE_SYSTEM>Purchasing</SOURCE_SYSTEM>');
2223 ELSIF (p_source_system = 'WSH') THEN
2224 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SOURCE_SYSTEM>Shipping</SOURCE_SYSTEM>');
2225 END IF;
2226
2227 IF p_ship_from_org IS NOT NULL THEN
2228 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_ORG>' ||
2229 WSH_UTIL_CORE.GET_ORG_NAME(p_organization_id => to_number(p_ship_from_org)) ||
2230 '</SHIP_ORG>');
2231 ELSE
2232 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SHIP_ORG></SHIP_ORG>');
2233 END IF;
2234
2235 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_NUM_FROM>' || p_order_number_from || '</ORDER_NUM_FROM>');
2236 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_NUM_TO>' || p_order_number_to || '</ORDER_NUM_TO>');
2237 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<ORDER_TYPE>' || p_order_type || '</ORDER_TYPE>');
2238 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SHIP_DATE_FROM>' || FND_DATE.CANONICAL_TO_DATE(p_ship_date_from) || '</SHIP_DATE_FROM>');
2239 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SHIP_DATE_TO>' || FND_DATE.CANONICAL_TO_DATE(p_ship_date_to) || '</SHIP_DATE_TO>');
2240 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<DEL_SHIP_DAYS>' || p_delete_beyond_x_ship_days || '</DEL_SHIP_DAYS>');
2241
2242 IF p_purge_intransit_trips = 'Y' THEN
2243 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CLOSE_IT_TRIPS>Yes</CLOSE_IT_TRIPS>');
2244 ELSE
2245 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CLOSE_IT_TRIPS>No</CLOSE_IT_TRIPS>');
2246 END IF;
2247
2248 IF p_delete_empty_records = 'Y' THEN
2249 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<DEL_EMPTY>Yes</DEL_EMPTY>');
2250 ELSE
2251 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<DEL_EMPTY>No</DEL_EMPTY>');
2252 END IF;
2253
2254 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CREATE_DATE_FROM>'|| FND_DATE.CANONICAL_TO_DATE(p_create_date_from) ||'</CREATE_DATE_FROM>');
2255 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<CREATE_DATE_TO>'|| FND_DATE.CANONICAL_TO_DATE(p_create_date_to) ||'</CREATE_DATE_TO>');
2256 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<DEL_CREATE_DAYS>' || p_del_beyond_creation_days || '</DEL_CREATE_DAYS>');
2257
2258 IF p_sort_per_criteria = 'T' THEN
2259 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_CRITERIA>Trip</SORT_CRITERIA>');
2260 ELSIF p_sort_per_criteria = 'D' THEN
2261 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_CRITERIA>Delivery</SORT_CRITERIA>');
2262 ELSIF p_sort_per_criteria = 'O' THEN
2263 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_CRITERIA>Order</SORT_CRITERIA>');
2264 END IF;
2265
2266 IF p_print_detail='L' THEN
2267 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<PRINT_DETAIL>Detail with LPN</PRINT_DETAIL>');
2268 ELSIF p_print_detail='D' THEN
2269 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<PRINT_DETAIL>Detail</PRINT_DETAIL>');
2270 ELSIF p_print_detail='S' THEN
2271 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<PRINT_DETAIL>Summary</PRINT_DETAIL>');
2272 END IF;
2273
2274 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<PURGED_BY>'|| FND_GLOBAL.USER_NAME ||'</PURGED_BY>');
2275 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<REQUEST_ID>' ||FND_GLOBAL.CONC_REQUEST_ID ||'</REQUEST_ID>');
2276 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<NO_OF_TRIPS>' || p_tbl_trip_purge_set.COUNT || '</NO_OF_TRIPS>');
2277 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<NO_OF_STOPS>' || p_count_stops || '</NO_OF_STOPS>');
2278 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<NO_OF_DELS>' || p_tbl_delivery_purge_set.COUNT || '</NO_OF_DELS>');
2279 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<NO_OF_LINES>' || p_count_details || '</NO_OF_LINES>');
2280
2281 IF p_print_detail <> 'S' THEN --check for print detail
2282 IF p_tbl_trip_purge_set.COUNT > 0 THEN
2283 FOR l_trip_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
2284 LOOP
2285 l_trip_id := p_tbl_trip_purge_set(l_trip_index).trip_id;
2286 l_trip_name := p_tbl_trip_purge_set(l_trip_index).trip_name;
2287 IF (p_tbl_trip_purge_set(l_trip_index).purge_set_type = 'NON_EMPTY') THEN
2288 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP>');
2289 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID>' || l_trip_id || '</TRIP_ID>');
2290 l_nonempty_count := l_nonempty_count +1;
2291 OPEN c_dels_for_trip(l_trip_id);
2292 LOOP
2293 FETCH c_dels_for_trip into l_delivery_id,l_delivery_name,l_waybill,l_gross_weight,
2294 l_ship_to,l_customer_name,l_pickup_date,l_dropoff_date,l_ship_date,l_bol_number ;
2295 EXIT WHEN c_dels_for_trip%NOTFOUND;
2296
2297 IF p_sort_per_criteria <> 'O' THEN --check for sort by order
2298 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRIP>');
2299 IF P_SORT_PER_CRITERIA = 'T' THEN
2300 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>' || l_trip_name || '</SORT_ID>');
2301 ELSIF P_SORT_PER_CRITERIA = 'D' THEN
2302 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>' || l_delivery_name || '</SORT_ID>');
2303 END IF ;
2304 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID>' || l_trip_name || '</TRIP_ID>');
2305 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DELIVERY_ID>' || l_delivery_name || '</DELIVERY_ID>');
2306 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<WAYBILL>' || l_waybill || '</WAYBILL>');
2307 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BOL>' || l_bol_number || '</BOL>');
2308 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_WEIGHT>' || l_gross_weight || '</GROSS_WEIGHT>');
2309 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_TO>' || l_ship_to || '</SHIP_TO>');
2310 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CUSTOMER><![CDATA[ ' || l_customer_name || ']]></CUSTOMER>');
2311 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PICKUP_DATE>' || l_pickup_date || '</PICKUP_DATE>');
2312 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DROPOFF_DATE>' || l_dropoff_date || '</DROPOFF_DATE>');
2313 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_DATE>' || l_ship_date || '</SHIP_DATE>');
2314 END IF;--check for sort by order
2315
2316 OPEN c_so_for_delivery(l_delivery_id);
2317 LOOP
2318 FETCH c_so_for_delivery into l_sales_order,l_order_type;
2319 EXIT WHEN c_so_for_delivery%NOTFOUND;
2320 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SALES_ORDER>');
2321 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ORDER_NUMBER>' || l_sales_order || '</ORDER_NUMBER>');
2322 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ORDER_TYPE>' || l_order_type || '</ORDER_TYPE>');
2323 IF p_print_detail = 'L' THEN --check whether to print container details
2324 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CONTAINER>');
2325 OPEN c_containers_for_so(l_sales_order);
2326 LOOP
2327 l_lpn_count := l_lpn_count+1;
2328 FETCH c_containers_for_so into l_container_id;
2329 EXIT WHEN c_containers_for_so%NOTFOUND;
2330 IF l_lpn_count = 1 THEN
2331 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_container_id);
2332 ELSE
2333 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ,' || l_container_id);
2334 END IF;
2335 END LOOP;
2336 CLOSE c_containers_for_so;
2337 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</CONTAINER>');
2338 END IF; -- end check to print container details
2339
2340 IF p_sort_per_criteria = 'O' THEN --check for sort by order
2341 OPEN c_dels_trips_for_order(l_sales_order);
2342 LOOP
2343 FETCH c_dels_trips_for_order into l_trip_id,l_trip_name,l_delivery_id,
2344 l_delivery_name,l_waybill,l_gross_weight,l_ship_to,l_customer_name,
2345 l_pickup_date,l_dropoff_date,l_ship_date,l_bol_number ;
2346 EXIT WHEN c_dels_trips_for_order%NOTFOUND;
2347 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRIP>');
2348 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID>' || l_trip_name || '</TRIP_ID>');
2349 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DELIVERY_ID>' || l_delivery_name || '</DELIVERY_ID>');
2350 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<WAYBILL>' || l_waybill || '</WAYBILL>');
2351 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BOL>' || l_bol_number || '</BOL>');
2352 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_WEIGHT>' || l_gross_weight || '</GROSS_WEIGHT>');
2353 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_TO>' || l_ship_to || '</SHIP_TO>');
2354 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CUSTOMER><![CDATA[ ' || l_customer_name || ']]></CUSTOMER>');
2355 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PICKUP_DATE>' || l_pickup_date || '</PICKUP_DATE>');
2356 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DROPOFF_DATE>' || l_dropoff_date || '</DROPOFF_DATE>');
2357 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SHIP_DATE>' || l_ship_date || '</SHIP_DATE>');
2358 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');
2359 END LOOP;
2360 CLOSE c_dels_trips_for_order;
2361 END IF; --check for sort by order
2362
2363 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</SALES_ORDER>');
2364 END LOOP;
2365 CLOSE c_so_for_delivery;
2366 IF p_sort_per_criteria <> 'O' THEN --check for sort by order
2367 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');
2368 END IF; --check for sort by order
2369 END LOOP;
2370 CLOSE c_dels_for_trip;
2371 --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');
2372 END IF;
2373 END LOOP;
2374 END IF; --check for number of records in plsql table
2375
2376 -- print empty trips
2377 IF p_tbl_trip_purge_set.COUNT > 0 THEN --check for number of records in plsql table
2378 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_TRIPS>');
2379 FOR l_trip_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
2380 LOOP
2381 l_trip_id := p_tbl_trip_purge_set(l_trip_index).trip_id;
2382 l_trip_name := p_tbl_trip_purge_set(l_trip_index).trip_name;
2383 IF (p_tbl_trip_purge_set(l_trip_index).purge_set_type = 'EMPTYTRIPS') THEN
2384 l_empty_trip_count := l_empty_trip_count+1;
2385 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRIP>');
2386 IF P_SORT_PER_CRITERIA = 'T' THEN
2387 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>' || lpad(l_trip_id,10,'0') || '</SORT_ID>');
2388 ELSIF P_SORT_PER_CRITERIA = 'D' THEN
2389 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>999999999</SORT_ID>');
2390 END IF ;
2391 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID>' || l_trip_id || '</TRIP_ID>');
2392 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DELIVERY_ID></DELIVERY_ID>');
2393 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');*/
2394 IF l_empty_trip_count = 1 THEN
2395 FND_FILE.PUT(FND_FILE.OUTPUT,l_trip_name);
2396 ELSE
2397 FND_FILE.PUT(FND_FILE.OUTPUT, ', ' || l_trip_name);
2398 END IF;
2399 END IF;
2400 END LOOP;
2401 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</EMPTY_TRIPS>');
2402 END IF; --check for number of records in plsql table
2403
2404 -- print empty deliveries
2405 IF p_tbl_delivery_purge_set.COUNT > 0 THEN --check for number of records in plsql table
2406 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_DELS>');
2407 FOR l_delivery_index in p_tbl_delivery_purge_set.FIRST .. p_tbl_delivery_purge_set.LAST
2408 LOOP
2409 l_delivery_id := p_tbl_delivery_purge_set(l_delivery_index).delivery_id;
2410 l_delivery_name := p_tbl_delivery_purge_set(l_delivery_index).delivery_name;
2411 IF (p_tbl_delivery_purge_set(l_delivery_index).purge_set_type = 'EMPTYDELS') THEN
2412 l_empty_del_count := l_empty_del_count+1 ;
2413 IF l_empty_del_count = 1 THEN
2414 FND_FILE.PUT(FND_FILE.OUTPUT,l_delivery_name);
2415 ELSE
2416 FND_FILE.PUT(FND_FILE.OUTPUT, ', ' || l_delivery_name);
2417 END IF;
2418 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRIP>');
2419 IF P_SORT_PER_CRITERIA = 'T' THEN
2420 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>999999999</SORT_ID>');
2421 ELSIF P_SORT_PER_CRITERIA = 'D' THEN
2422 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>' || lpad(l_delivery_id,10,'0') || '</SORT_ID>');
2423 END IF ;
2424 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID></TRIP_ID>');
2425 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DELIVERY_ID>' || l_delivery_id || '</DELIVERY_ID>');
2426 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');*/
2427
2428 END IF;
2429 END LOOP;
2430 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</EMPTY_DELS>');
2431 END IF; --check for number of records in plsql table
2432
2433 -- print empty containers
2434 IF p_tbl_container_purge_set.COUNT > 0 THEN --check for number of records in plsql table
2435 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_LPNS>');
2436 FOR l_container_index in p_tbl_container_purge_set.FIRST .. p_tbl_container_purge_set.LAST
2437 LOOP
2438 l_container_id := p_tbl_container_purge_set(l_container_index).container_id;
2439 IF (p_tbl_container_purge_set(l_container_index).purge_set_type = 'EMPTYLPNS') THEN
2440 l_empty_lpn_count := l_empty_lpn_count+1 ;
2441 IF l_empty_lpn_count = 1 THEN
2442 FND_FILE.PUT(FND_FILE.OUTPUT,l_container_id);
2443 ELSE
2444 FND_FILE.PUT(FND_FILE.OUTPUT, ', ' || l_container_id);
2445 END IF;
2446 /*FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TRIP>');
2447 IF P_SORT_PER_CRITERIA = 'T' THEN
2448 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>999999999</SORT_ID>');
2449 ELSIF P_SORT_PER_CRITERIA = 'D' THEN
2450 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<SORT_ID>' || lpad(l_delivery_id,10,'0') || '</SORT_ID>');
2451 END IF ;
2452 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<TRIP_ID></TRIP_ID>');
2453 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DELIVERY_ID>' || l_delivery_id || '</DELIVERY_ID>');
2454 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</TRIP>');*/
2455
2456 END IF;
2457 END LOOP;
2458 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</EMPTY_LPNS>');
2459 END IF; --check for number of records in plsql table
2460
2461 END IF; --check for print detail
2462 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NON_EMPTY_COUNT>' || l_nonempty_count || '</NON_EMPTY_COUNT>');
2463 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_TRIP_COUNT>' || l_empty_trip_count || '</EMPTY_TRIP_COUNT>');
2464 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_DEL_COUNT>' || l_empty_del_count || '</EMPTY_DEL_COUNT>');
2465 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPTY_LPN_COUNT>' || l_empty_lpn_count || '</EMPTY_LPN_COUNT>');
2466 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</ROWSET>');
2467
2468 IF l_debug_on THEN
2469 WSH_DEBUG_SV.pop(l_module_name);
2470 END IF;
2471
2472 EXCEPTION
2473 WHEN OTHERS THEN
2474 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2475 l_err := SQLERRM;
2476
2477 IF l_debug_on THEN
2478 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2479 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2480 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2481 END IF;
2482
2483 END Generate_Purge_Report;
2484
2485
2486 -----------------------------------------------------------------------------
2487 --
2488 -- Procedure: Purge_Workflow
2489 -- Parameters: p_tbl_trip_purge_set pl/sql table of trip id's eligible for purge
2490 -- p_tbl_delivery_purge_set pl/sql table of delivery id's eligible for purge
2491 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
2492
2493 -- Description: This API deletes the workflows for Trip and Delivery.
2494 -----------------------------------------------------------------------------
2495
2496 PROCEDURE Purge_Workflow(p_tbl_trip_purge_set Trip_ID_Tbl_Type ,
2497 p_tbl_delivery_purge_set Delivery_ID_Tbl_Type,
2498 x_return_status OUT NOCOPY VARCHAR2) IS
2499
2500 l_debug_on BOOLEAN;
2501 l_return_status VARCHAR2(1);
2502
2503 l_delivery_ids_tab WSH_UTIL_CORE.column_tab_type;
2504 l_trip_ids_tab WSH_UTIL_CORE.column_tab_type;
2505
2506 l_success_count NUMBER;
2507
2508 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PURGE_WORKFLOW';
2509
2510 BEGIN
2511
2512 -- Debug Statements
2513 --
2514 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2515 --
2516 IF l_debug_on IS NULL THEN
2517 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2518 END IF;
2519
2520 IF l_debug_on THEN
2521 WSH_DEBUG_SV.push(l_module_name);
2522 END IF;
2523
2524 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2525
2526 --Check for number of ids in delivery table
2527 IF p_tbl_delivery_purge_set.COUNT > 0 THEN
2528 FOR l_delivery_index in p_tbl_delivery_purge_set.FIRST .. p_tbl_delivery_purge_set.LAST
2529 LOOP
2530 l_delivery_ids_tab(l_delivery_index) := p_tbl_delivery_purge_set(l_delivery_index).delivery_id;
2531 END LOOP;
2532
2533 IF l_debug_on THEN
2534 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.PURGE_ENTITY', WSH_DEBUG_SV.C_PROC_LEVEL);
2535 WSH_DEBUG_SV.logmsg(l_module_name,'no of delivery ids =' || l_delivery_ids_tab.count, WSH_DEBUG_SV.C_PROC_LEVEL);
2536 END IF;
2537
2538 WSH_WF_STD.Purge_Entity(
2539 p_entity_type => 'DELIVERY',
2540 p_entity_ids =>l_delivery_ids_tab,
2541 --p_action IN VARCHAR2 DEFAULT 'PURGE',
2542 --p_docommit IN BOOLEAN DEFAULT FALSE,
2543 x_success_count => l_success_count,
2544 x_return_status => l_return_status) ;
2545
2546 IF l_debug_on THEN
2547 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
2548 WSH_DEBUG_SV.log(l_module_name,'L_SUCCESS_COUNT',l_success_count);
2549 END IF;
2550
2551 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2552 x_return_status := l_return_status;
2553 IF l_debug_on THEN
2554 WSH_DEBUG_SV.pop(l_module_name);
2555 END IF;
2556 RETURN;
2557 END IF;
2558 END IF; --Check for number of ids in delivery table
2559
2560 --Check for number of ids in trip table
2561 IF p_tbl_trip_purge_set.COUNT > 0 THEN
2562 FOR l_trip_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
2563 LOOP
2564 l_trip_ids_tab(l_trip_index) := p_tbl_trip_purge_set(l_trip_index).trip_id;
2565 END LOOP;
2566
2567 IF l_debug_on THEN
2568 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WSH_WF_STD.PURGE_ENTITY', WSH_DEBUG_SV.C_PROC_LEVEL);
2569 END IF;
2570
2571 WSH_WF_STD.Purge_Entity(
2572 p_entity_type => 'TRIP',
2573 p_entity_ids =>l_trip_ids_tab,
2574 --p_action IN VARCHAR2 DEFAULT 'PURGE',
2575 --p_docommit IN BOOLEAN DEFAULT FALSE,
2576 x_success_count => l_success_count,
2577 x_return_status => l_return_status) ;
2578
2579 IF l_debug_on THEN
2580 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
2581 WSH_DEBUG_SV.log(l_module_name,'L_SUCCESS_COUNT',l_success_count);
2582 END IF;
2583
2584 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2585 x_return_status := l_return_status;
2586 IF l_debug_on THEN
2587 WSH_DEBUG_SV.pop(l_module_name);
2588 END IF;
2589 RETURN;
2590 END IF;
2591 END IF; --Check for number of ids in trip table
2592
2593 IF l_debug_on THEN
2594 WSH_DEBUG_SV.pop(l_module_name);
2595 END IF;
2596
2597 END Purge_Workflow;
2598
2599
2600 -----------------------------------------------------------------------------
2601 --
2602 -- Procedure: Validate_Trips
2603 -- Parameters: p_tbl_trip_purge_set pl/sql table of trip id's eligible for purge
2604 -- x_tbl_trip_purge_set pl/sql table of trip id's eligible for purge
2605 -- after validating all the LPNs belonging to the trip with WMS API
2606 -- x_return_status - Returns WSH_UTIL_CORE.G_RET_STS_SUCCESS if Success
2607
2608 -- Description: This API call the WMS_Container_Grp API for checking the validity of
2609 -- each LPN belonging to a particular trip.
2610 -- The WMS LPN Purge API returns the list of LPN Ids that are eligible to
2611 -- be purged from WMS side. If the number of LPNs returned by WMS
2612 -- is same as the number of LPNs passed by this API that means that
2613 -- all the LPNs within the trip are eligible to be purged and the
2614 -- further validations for MDC/Moves can be performed on the trip.
2615 -- If the count is not same then the trip is marked as in eligible for
2616 -- purge and is excluded from the list of trips to be purged.
2617 -- This API also checks whether the trip is a part of valid Continuous
2618 -- Move(CM). A valid CM is one in which all the the trips are eligible
2619 -- for purge. If not then the Trip is not eligible to be purged.
2620 -- This API also checks whether the trip is a part of valid MDC
2621 -- configuration. A valid MDC configurationis one in which all
2622 -- the the trips are eligible for purge. If not then the Trip is
2623 -- not eligible to be purged.
2624 -----------------------------------------------------------------------------
2625 PROCEDURE Validate_Trips( p_tbl_trip_purge_set Trip_ID_Tbl_Type ,
2626 x_tbl_trip_purge_set OUT NOCOPY Trip_ID_Tbl_Type ,
2627 x_return_status OUT NOCOPY VARCHAR2) IS
2628
2629 l_debug_on BOOLEAN;
2630 l_return_status VARCHAR2(1);
2631
2632 l_trip_id NUMBER;
2633 l_move_id NUMBER;
2634 l_lpn_id NUMBER;
2635 l_trip_index NUMBER;
2636 l_loop_index NUMBER;
2637 l_old_move NUMBER;
2638 l_new_move NUMBER;
2639 l_mdc_trip NUMBER;
2640 l_lpn_count NUMBER;
2641
2642 l_lpn_valid BOOLEAN;
2643 l_move_valid BOOLEAN;
2644 l_trip_valid BOOLEAN;
2645 l_move_found BOOLEAN;
2646 l_trip_found BOOLEAN;
2647
2648 l_err VARCHAR2(500);
2649 sql_tripmove VARCHAR2(4000);
2650
2651 l_msg_count NUMBER;
2652 l_msg_data VARCHAR2(32767);
2653
2654 l_wms_lpn_record WMS_Data_Type_Definitions_PUB.LPNPurgeRecordType;
2655
2656 --TYPE IDTableType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2657 --l_lpn_ids IDTableType;
2658
2659 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'VALIDATE_TRIPS';
2660
2661 TYPE PurgeCurType IS REF CURSOR;
2662 c_trip_moves PurgeCurType; --The cursor gets all the moves their trips
2663 --wherever the move has more than 1 trip
2664 l_tbl_trip_moves Trip_moves_Tbl_Type;
2665 l_tbl_trip_mdc WSH_UTIL_CORE.ID_TAB_TYPE;
2666
2667 CURSOR c_lpns_for_trip(p_tripid NUMBER) IS
2668 SELECT wdd.lpn_id
2669 FROM wsh_trips wt,
2670 wsh_trip_stops wts,
2671 wsh_delivery_legs wdl,
2672 wsh_new_deliveries wnd,
2673 wsh_delivery_assignments_v wda,
2674 wsh_delivery_details wdd
2675 WHERE wt.trip_id = wts.trip_id
2676 AND wts.stop_id = wdl.pick_up_stop_id
2677 AND wdl.delivery_id = wnd.delivery_id
2678 AND wda.delivery_id = wnd.delivery_id
2679 AND wda.delivery_detail_id = wdd.delivery_detail_id
2680 AND wdd.container_flag = 'Y'
2681 AND wdd.lpn_id IS NOT NULL
2682 AND wts.trip_id = p_tripid;
2683
2684 -- Bug 5084113
2685 /* Replaced the query below with a non recursive query
2686 The query below was fetching,
2687 1. All the deliveries of consolidation type( wnd.delivery_type = 'CONSOLIDATION' or wdl.parent_delivery_leg_id IS NULL )
2688 2. Search for possible consolidations within the above list.
2689 3. Build the list of deliveries under consolidation deliveries in step2 and identify the list of trips.
2690
2691 CURSOR c_get_mdc_trips(p_tripid NUMBER) IS
2692 SELECT
2693 DISTINCT wt1.trip_id
2694 FROM
2695 wsh_trips wt1,
2696 wsh_trip_stops pickup_stop1,
2697 wsh_trip_stops dropoff_stop1,
2698 wsh_delivery_legs wdl1
2699 WHERE
2700 wdl1.pick_up_stop_id = pickup_stop1.stop_id AND
2701 wdl1.drop_off_stop_id = dropoff_stop1.stop_id AND
2702 wt1.trip_id = pickup_stop1.trip_id AND
2703 wt1.trip_id = dropoff_stop1.trip_id AND
2704 wdl1.delivery_id IN (SELECT delivery_id
2705 FROM wsh_delivery_legs
2706 START WITH delivery_id IN (SELECT delivery_id
2707 FROM wsh_delivery_legs
2708 WHERE parent_delivery_leg_id IS NULL
2709 START WITH delivery_id IN (SELECT wdl.delivery_id
2710 FROM
2711 wsh_new_deliveries wnd,
2712 wsh_delivery_legs wdl,
2713 wsh_trip_stops pickup_stop,
2714 wsh_trip_stops dropoff_stop,
2715 wsh_trips wt
2716 WHERE
2717 wnd.delivery_id = wdl.delivery_id AND
2718 wdl.pick_up_stop_id = pickup_stop.stop_id AND
2719 wdl.drop_off_stop_id = dropoff_stop.stop_id AND
2720 wt.trip_id = pickup_stop.trip_id AND
2721 wt.trip_id = dropoff_stop.trip_id AND
2722 ((wnd.delivery_type = 'CONSOLIDATION')
2723 OR
2724 (wdl.parent_delivery_leg_id IS NULL)
2725 ) AND
2726 wt.trip_id = p_tripid)
2727 CONNECT BY delivery_leg_id = PRIOR parent_delivery_leg_id )
2728 CONNECT BY parent_delivery_leg_id = PRIOR delivery_leg_id)
2729 ORDER BY wt1.trip_id;
2730 */
2731
2732 CURSOR c_get_mdc_trips(p_tripid NUMBER) IS
2733 SELECT
2734 DISTINCT wt1.trip_id
2735 FROM
2736 wsh_trips wt1,
2737 wsh_trip_stops wts,
2738 wsh_delivery_legs wdl1
2739 WHERE
2740 (wdl1.pick_up_stop_id = wts.stop_id OR
2741 wdl1.drop_off_stop_id = wts.stop_id) AND
2742 wt1.trip_id = wts.trip_id AND
2743 wdl1.delivery_id IN
2744 (
2745 SELECT delivery_id
2746 FROM wsh_delivery_legs
2747 WHERE parent_delivery_leg_id
2748 IN
2749 (
2750 SELECT wdl.delivery_leg_id
2751 FROM
2752 wsh_delivery_legs wdl,
2753 wsh_trip_stops wts,
2754 wsh_trips wt
2755 WHERE
2756 (wdl.pick_up_stop_id = wts.stop_id OR
2757 wdl.drop_off_stop_id = wts.stop_id) AND
2758 wt.trip_id = wts.trip_id AND
2759 wdl.parent_delivery_leg_id IS NULL AND
2760 wt.trip_id = p_tripid
2761 )
2762 )
2763 ORDER BY wt1.trip_id;
2764
2765 BEGIN
2766 -- Debug Statements
2767 --
2768 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2769 --
2770 IF l_debug_on IS NULL THEN
2771 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2772 END IF;
2773
2774 IF l_debug_on THEN
2775 WSH_DEBUG_SV.push(l_module_name);
2776 END IF;
2777
2778 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2779 l_lpn_valid := TRUE;
2780
2781 IF (wsh_util_core.fte_is_installed='Y') THEN
2782 sql_tripmove := 'SELECT move_id, trip_id
2783 FROM fte_trip_moves
2784 WHERE move_id IN
2785 (SELECT move_id
2786 FROM fte_trip_moves
2787 GROUP BY move_id
2788 HAVING count(trip_id) >1 )
2789 ORDER BY move_id';
2790
2791 OPEN c_trip_moves FOR sql_tripmove;
2792 FETCH c_trip_moves BULK COLLECT into l_tbl_trip_moves;
2793 CLOSE c_trip_moves;
2794 END IF;
2795
2796 FOR l_trip_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
2797 LOOP
2798 l_lpn_valid := TRUE;
2799 l_trip_id := p_tbl_trip_purge_set(l_trip_index).trip_id;
2800 --Get all the LPNs for the Trip
2801 OPEN c_lpns_for_trip(l_trip_id);
2802 FETCH c_lpns_for_trip BULK COLLECT into l_wms_lpn_record.LPN_IDs;
2803 CLOSE c_lpns_for_trip;
2804
2805 l_lpn_count := l_wms_lpn_record.LPN_IDs.COUNT;
2806
2807 --call WMS API to check whether the LPN is eligible for purge
2808 IF l_debug_on THEN
2809 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit WMS_CONTAINER_GRP.LPN_PURGE_ACTIONS', WSH_DEBUG_SV.C_PROC_LEVEL);
2810 END IF;
2811
2812 WMS_Container_GRP.LPN_Purge_Actions( p_api_version =>1.0,
2813 p_init_msg_list =>FND_API.G_TRUE,
2814 p_commit =>'FALSE',
2815 x_return_status =>l_return_status,
2816 x_msg_count =>l_msg_count,
2817 x_msg_data =>l_msg_data,
2818 p_caller =>'WSH',
2819 p_action =>WMS_Container_GRP.G_LPN_PURGE_ACTION_VALIDATE,
2820 p_lpn_purge_rec => l_wms_lpn_record
2821 );
2822
2823 IF l_debug_on THEN
2824 WSH_DEBUG_SV.log(l_module_name,'L_RETURN_STATUS',l_return_status);
2825 WSH_DEBUG_SV.log(l_module_name,'L_MSG_DATA',l_msg_data);
2826 END IF;
2827
2828 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
2829 x_return_status := l_return_status;
2830 IF l_debug_on THEN
2831 WSH_DEBUG_SV.pop(l_module_name);
2832 END IF;
2833 RETURN;
2834 END IF;
2835
2836 IF l_lpn_count <> l_wms_lpn_record.LPN_IDs.COUNT THEN
2837 l_lpn_valid := FALSE;
2838 END IF;
2839
2840 --Check for Trip Moves/MDC related validations only when LPN check returns TRUE
2841 IF l_lpn_valid THEN -- is there some other way to stop the loop here and
2842 -- and continue the loop with the next value ?
2843 l_trip_valid := TRUE;
2844 l_move_found := FALSE;
2845 --Check for Trip Moves related validations
2846 IF l_tbl_trip_moves.COUNT > 0 THEN
2847 FOR l_loop_index in l_tbl_trip_moves.FIRST .. l_tbl_trip_moves.LAST
2848 LOOP
2849 IF l_tbl_trip_moves(l_loop_index).trip_id = l_trip_id THEN
2850 l_move_id := l_tbl_trip_moves(l_loop_index).move_id;
2851 l_move_found := TRUE;
2852 FOR l_move_index in l_tbl_trip_moves.FIRST .. l_tbl_trip_moves.LAST
2853 LOOP
2854 l_old_move := l_new_move;
2855 l_new_move :=l_tbl_trip_moves(l_move_index).move_id;
2856 IF (l_old_move IS NOT NULL AND l_old_move <> l_new_move) THEN
2857 EXIT;
2858 END IF;
2859
2860 IF l_tbl_trip_moves(l_move_index).move_id = l_move_id THEN
2861 l_trip_id := l_tbl_trip_moves(l_move_index).trip_id;
2862 l_trip_valid := FALSE;
2863 FOR l_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
2864 LOOP
2865 IF p_tbl_trip_purge_set(l_index).trip_id = l_trip_id THEN
2866 l_trip_valid := TRUE;
2867 END IF;
2868 EXIT WHEN l_trip_valid;
2869 END LOOP;
2870
2871 IF NOT l_trip_valid THEN
2872 EXIT;
2873 END IF;
2874 END IF;
2875 END LOOP;
2876 END IF;
2877 EXIT WHEN l_move_found;
2878 END LOOP;
2879 END IF;
2880 --End Check for Trip Moves related validations
2881
2882 --Check for MDC related validations only if Trip Move related validation returns TRUE
2883 IF l_trip_valid THEN
2884 OPEN c_get_mdc_trips(l_trip_id);
2885 FETCH c_get_mdc_trips BULK COLLECT into l_tbl_trip_mdc;
2886 CLOSE c_get_mdc_trips;
2887 --Check for MDC related validations
2888 IF l_tbl_trip_mdc.COUNT > 1 THEN
2889 FOR l_loop_index in l_tbl_trip_mdc.FIRST .. l_tbl_trip_mdc.LAST
2890 LOOP
2891 l_mdc_trip := l_tbl_trip_mdc(l_loop_index);
2892 l_trip_valid := FALSE;
2893 FOR l_index in p_tbl_trip_purge_set.FIRST .. p_tbl_trip_purge_set.LAST
2894 LOOP
2895 IF p_tbl_trip_purge_set(l_index).trip_id = l_mdc_trip THEN
2896 l_trip_valid := TRUE;
2897 END IF;
2898 EXIT WHEN l_trip_valid;
2899 END LOOP;
2900 EXIT WHEN NOT l_trip_valid;
2901 END LOOP;
2902 END IF; --End check for MDC related validations
2903 END IF;--Check for MDC related validations only if Trip Move related validation returns TRUE
2904 END IF; --End Check for Trip Moves/MDC related validations only when LPN check returns TRUE
2905
2906 IF l_lpn_valid AND l_trip_valid THEN
2907 x_tbl_trip_purge_set(x_tbl_trip_purge_set.COUNT+1).trip_id := p_tbl_trip_purge_set(l_trip_index).trip_id;
2908 x_tbl_trip_purge_set(x_tbl_trip_purge_set.COUNT).trip_name := p_tbl_trip_purge_set(l_trip_index).trip_name;
2909 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;
2910 END IF ;
2911 END LOOP;
2912
2913 IF l_debug_on THEN
2914 WSH_DEBUG_SV.pop(l_module_name);
2915 END IF;
2916
2917 EXCEPTION
2918 WHEN OTHERS THEN
2919 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR ;
2920 l_err := SQLERRM;
2921
2922 IF l_debug_on THEN
2923 WSH_DEBUG_SV.logmsg(l_module_name,'Unexpected error has occured. Oracle error message is '||
2924 SQLERRM,WSH_DEBUG_SV.C_UNEXPEC_ERR_LEVEL);
2925 WSH_DEBUG_SV.pop(l_module_name,'EXCEPTION:OTHERS');
2926 END IF;
2927 END Validate_Trips ;
2928
2929 END WSH_PURGE;
[Home] [Help]