1 package body wms_archive_pvt as
2 /* $Header: WMSTARCB.pls 115.5 2004/04/15 01:11:45 joabraha noship $ */
3 --
4 --
5 -- Internal Constant variables
6 --
7 c_records_per_worker constant number default 50000;
8 c_record_hi_number_per_worker constant number default 300000;
9
10 --c_records_per_worker constant number default 50;
11 --c_record_hi_number_per_worker constant number default 300;
12
13 --
14 --
15 l_pkg varchar2(72) := 'WMS_TASK_ARCHIVE :';
16 l_total_records number := 0;
17 --
18 --
19 -- ---------------------------------------------------------------------------------------
20 -- |---------------------< trace >--------------------------------------------------------|
21 -- ---------------------------------------------------------------------------------------
22 -- {Start Of Comments}
23 --
24 -- Description:
25 -- Wrapper around the tracing utility.
26 --
27 -- Prerequisites:
28 -- None
29 --
30 -- In Parameters:
31 -- Name Reqd Type Description
32 -- p_message Yes varchar2 Message to be displayed in the log file.
33 -- p_prompt Yes varchar2 Prompt.
34 -- p_level No number Level.
35 --
36 -- Post Success:
37 -- None.
38 --
39 -- Post Failure:
40 -- None
41 --
42 -- Access Status:
43 -- Internal Development Use Only.
44 --
45 -- {End Of Comments}
46 --
47
48 Procedure trace(
49 p_message in varchar2
50 , p_level in number
51 ) is
52 begin
53 INV_LOG_UTIL.trace(p_message, 'WMS_TASK_ARCHIVE', p_level);
54 end trace;
55 --
56 --
57 -- ---------------------------------------------------------------------------------------
58 -- |-------------------------------< archive_tasks >--------------------------------------|
59 -- ---------------------------------------------------------------------------------------
60 -- {Start Of Comments}
61 --
62 -- Description:
63 -- Archives tasks records based on organization.
64 --
65 -- Package-Procedure combination
66 --
67 -- Prerequisites:
68 --
69 --
70 --
71 -- In Parameters:
72 -- Name Reqd Type Description
73 -- x_errbuf Yes varchar2 Concurrent Manager Parameter.
74 -- x_retcode Yes varchar2 Concurrent Manager Parameter.
75 -- p_org_code Yes number Organization for which data needs to be purged.
76 -- p_purge_days Yes number Number of days of data left starting with current
77 -- date and going back.
78 -- p_archive_batches Yes number Number of batches into which the records needs
79 -- to be broken up.
80 --
81 --
82 -- Post Success:
83 -- Data in the history table are deleted once the Archive tables are populated apropriately.
84 --
85 -- Post Failure:
86 -- No data archiving takes place,
87 --
88 -- Access Status:
89 -- Internal Development Use Only.
90 --
91 -- {End Of Comments}
92 --
93 Procedure archive_tasks(
94 x_errbuf out nocopy varchar2
95 , x_retcode out nocopy number
96 , p_org_id in number
97 , p_purge_days in number
98 , p_archive_batches in number
99 ) is
100 l_proc varchar2(72) := 'ARCHIVE_WMS_TASKS :';
101 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
102
103 l_max_date_time varchar2(50):= null;
104 l_sys_date varchar2(50):= null;
105
106 l_total_record_count number:= 0;
107
108 l_min_range_date_time date;
109 l_max_range_date_time date;
110
111 l_days_between number := 0;
112 l_each_worker_chunk number:= 0;
113
114
115 l_record_per_worker number:= 0;
116 l_number_workers number:= 0;
117 l_organization_id number:= 0;
118
119 l_loop_counter number:= 1;
120 l_from_date date;
121 l_to_date date;
122 l_num_batches number;
123 l_purge_req_id number;
124
125 l_purge_days number:= 0;
126
127 i number;
128 type l_reqstatus_table is table of number
129 index by binary_integer;
130
131 l_reqstatus_tbl_type l_reqstatus_table;
132 --l_num_of_workers_launched number:= 1;
133
134 submission_error_except exception;
135
136 -- ### This cursor gets the date upto which data will be purged.
137 -- ### Data in tables whose creation date is less than this date will be
138 -- ### purged(not including this date, is emphasized).
139 cursor c_get_total_eligible_recs is
140 select count(*), min(last_update_date), max(last_update_date),
141 (max(last_update_date) - min(last_update_date))
142 from wms_dispatched_tasks_history
143 where last_update_date < (sysdate - l_purge_days)
144 and organization_id = nvl(p_org_id, organization_id);
145 --
146 --
147 begin
148 if (l_debug = 1) then
149 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'RRRR/MM/DD HH24:MI:SS'), 1);
150 trace(l_proc || ' p_org_id => ' || nvl(p_org_id, -99));
151 trace(l_proc || ' p_purge_days => ' || nvl(p_purge_days, -99));
152 trace(l_proc || ' p_archive_batches => ' || nvl(p_archive_batches, -99));
153 end if;
154
155 -- @@@ Validating input parameters.
156 if (p_purge_days is null) then
157 l_purge_days := 0;
158 else
159 l_purge_days := p_purge_days;
160 end if;
161
162 -- @@@ Get total number of eligible records to be archived.
163 open c_get_total_eligible_recs;
164 fetch c_get_total_eligible_recs
165 into l_total_record_count, l_min_range_date_time, l_max_range_date_time, l_days_between;
166
167 if l_total_record_count = 0 then
168 if (l_debug = 1) then
169 trace(l_proc || ' Eligible records not found for Archiving for date range provided... ');
170 end if;
171
172 close c_get_total_eligible_recs;
173 raise fnd_api.g_exc_error;
174 --return;
175 elsif l_total_record_count > 0 then
176 if (l_debug = 1) then
177 trace(l_proc || ' l_total_record_count => '|| nvl(l_total_record_count, -99));
178 trace(l_proc || ' l_min_range_date_time => '|| nvl(to_char(l_min_range_date_time, 'RRRR/MM/DD HH24:MI:SS'), '@@@'));
179 trace(l_proc || ' l_max_range_date_time => '|| nvl(to_char(l_max_range_date_time, 'RRRR/MM/DD HH24:MI:SS'), '@@@'));
180 trace(l_proc || ' l_days_between => '|| nvl(l_days_between, -99));
181 end if;
182
183 if (l_debug = 1) then
184 trace(l_proc || ' Before Checking l_total_record_count and setting value for the l_record_per_worker variable...');
185 end if;
186
187 -- @@@ Determine number of records to be processe
188 -- @@@ If the total number of records is less than a million then its set to 50000 else 300000;
189 if l_total_record_count < 100000 then
190 l_record_per_worker := c_records_per_worker;
191 else
192 l_record_per_worker := c_record_hi_number_per_worker;
193 end if;
194
195 if (l_debug = 1) then
196 trace(l_proc || ' l_record_per_worker => '|| l_record_per_worker);
197 end if;
198
199 if (l_debug = 1) then
200 trace(l_proc || ' After Checking l_total_record_count and setting value for the l_record_per_worker variable...');
201 end if;
202
203 if (l_debug = 1) then
204 trace(l_proc || ' Before calculating value for l_num_batches variable...');
205 trace(l_proc || ' l_total_record_count => ' || l_total_record_count);
206 trace(l_proc || ' l_record_per_worker => ' || l_record_per_worker);
207 trace(l_proc || ' p_archive_batches => ' || p_archive_batches);
208 end if;
209
210 -- @@@ Calculate the number of workers required for this run.
211 -- @@@ The idea is to use the smaller value.
212 l_num_batches := ceil(l_total_record_count/l_record_per_worker);
213
214 if (l_debug = 1) then
215 trace(l_proc || ' l_num_batches => ' || l_num_batches);
216 end if;
217
218 if p_archive_batches > l_num_batches then
219 l_number_workers:= l_num_batches;
220 else
221 l_number_workers:= p_archive_batches;
222 end if;
223
224 if (l_debug = 1) then
225 trace(l_proc || ' l_number_workers => '|| l_number_workers);
226 end if;
227
228 if (l_debug = 1) then
229 trace(l_proc || ' After calculating value for l_num_batches variable...');
230 end if;
231
232 if (l_debug = 1) then
233 trace(l_proc || ' Before calculating value for l_each_worker_chunk variable...');
234 trace(l_proc || ' l_days_between => ' || l_days_between);
235 trace(l_proc || ' l_num_batches =>' || l_num_batches);
236 end if;
237
238 -- @@@ Get the chunk of data in terms of days to be assigned to each worker.
239 l_each_worker_chunk := l_days_between/l_number_workers;
240 if (l_debug = 1) then
241 trace(l_proc || ' l_each_worker_chunk => '|| l_each_worker_chunk);
242 end if;
243
244 if (l_debug = 1) then
245 trace(l_proc || ' After calculating value for l_each_worker_chunk variable...');
246 end if;
247
248 if l_number_workers > 0 then
249 if (l_debug = 1) then
250 trace(l_proc || ' Before entering the for loop... ');
251 end if;
252
253 -- @@@ The from date and the to date passed to the worker program is derived in the
254 -- @@@ loop itself.
255 for i in 1..l_number_workers
256 loop
257 -- @@@ The l_loop_counter is used to determine mainly the first run in the loop.
258 -- @@@ Note that the 'l_from_date' and 'l_to_date' are both defined as date variables.
259 -- @@@ The 'l_min_range_date_time' is defined as a varchar2 so as to derive the date/time
260 -- @@@ information to the precision of the last second.
261 -- @@@ The 'l_min_range_date_time' derived as follows from the
262 -- @@@ 'c_get_total_eligible_recs' cursor:
263 -- @@@ "to_char(min(last_update_date), 'MM/DD/YY HH:MI:SS')"
264 -- @@@ Since the 'l_min_range_date_time' is a varchar, the fnd_date.displaydate_to_date()
265 -- @@@ is used to convert it to a date and assign to the l_from_date(date variable).
266 -- @@@ This only needs to be done the very first time since in the subsequence runs,
267 -- @@@ date arithmetic is being performed with the SELECT from dual.
268 -- @@@
269 -- @@@ The logic of deriving the 'l_from_date' and 'l_to_date' for each worker call is as follows:
270 -- @@@ 1. For the very first run, the 'l_from_date' is equal to the 'l_min_range_date_time'.
271 -- @@@ This is passed as a date variable to the first worker call.
272 -- @@@ 2. The 'l_to_date' is computed as the 'l_from_date + l_each_worker_chunk' every time as follows:
273 -- @@@ The 'l_each_worker_chunk' stores the number of days for each worker.
274 -- @@@ Hence the
275 -- @@@ 'select (l_from_date + l_each_worker_chunk) into l_to_date from dual;'
276 -- @@@ effectively derives the l_to_date with a precision to the last second.
277 -- @@@ 3. In every subsequent run, date arithmetic is performed on the 'l_from_date' and 'l_to_date'
278 -- @@@ derived in the previous run prior to the worker call.
279 -- @@@
280 -- @@@ One another thing to note is that in the subsequent runs, the 'l_from_date' is set to the
281 -- @@@ 'l_to_date' from the previous run in the loop. The SQL in the worker is selecting a range
282 -- @@@ greater that the 'l_from_date'.
283 if (i = 1) then
284 -- @@@ Get the min date range for the first worker call.
285 l_from_date := l_min_range_date_time - 1/(3600*24);
286 if (l_debug = 1) then
287 trace(l_proc || ' Inside if for (i > 1)...');
288 trace(l_proc || ' i => ' || i);
289 trace(l_proc || ' l_from_date => ' || to_char(l_from_date, 'RRRR/MM/DD HH24:MI:SS'));
290 end if;
291 elsif (i > 1) then
292 -- @@@ Get the min date range for subsequent worker calls.
293 l_from_date := l_to_date;
294 if (l_debug = 1) then
295 trace(l_proc || ' Inside if for (i > 1)...');
296 trace(l_proc || ' i => ' || i);
297 trace(l_proc || ' l_from_date => ' || to_char(l_from_date, 'RRRR/MM/DD HH24:MI:SS'));
298 end if;
299 end if;
300
301 if (l_debug = 1) then
302 trace(l_proc || ' l_from_date Outside the if check for counter(i) => '|| to_char(l_from_date, 'RRRR/MM/DD HH24:MI:SS'));
303 end if;
304
305 -- @@@ Get the max date range for the worker call.
306 -- @@@
307 --l_to_date := l_from_date + l_each_worker_chunk;
308 select (l_from_date + l_each_worker_chunk) into l_to_date from dual;
309
310 if (l_debug = 1) then
311 trace(l_proc || ' i => ' || i);
312 trace(l_proc || ' l_to_date => ' || to_char(l_to_date, 'RRRR/MM/DD HH24:MI:SS'));
313 end if;
314
315 if (l_debug = 1) then
316 trace(l_proc || ' Loop Counter => ' || i);
317 trace(l_proc || ' Before Launching WMS Task Purge Worker ...');
318 trace(l_proc || ' For Range, with From Date => ' || to_char(l_from_date, 'RRRR/MM/DD HH24:MI:SS'));
319 trace(l_proc || ' and To Date => ' || to_char(l_to_date, 'RRRR/MM/DD HH24:MI:SS'));
320 end if;
321
322
323 -- @@@ Calling the purge worker for a specific date range..
324 -- @@@ 'l_purge_req_id' returns the concurrent request id for the worker launched.
325 l_purge_req_id := fnd_request.submit_request(application => 'WMS'
326 , program => 'WMSTARCW'
327 , argument1 => to_char(l_from_date, 'RRRR/MM/DD HH24:MI:SS')
328 , argument2 => to_char(l_to_date, 'RRRR/MM/DD HH24:MI:SS')
329 , argument3 => p_org_id);
330
331 if (l_debug = 1) then
332 trace(l_proc || ' l_purge_req_id => ' || l_purge_req_id);
333 end if;
334 -- @@@ Handle worker submission error
335 -- @@@ Raise exception if failed else commit and proceed.
336 if (l_purge_req_id = 0) then
337 if (l_debug = 1) then
338 trace(l_proc || ' Error launching Purge Worker Number... ');
339 end if;
340 raise submission_error_except;
341 else
342 if (l_debug = 1) then
343 trace(l_proc || ' Purge Worker launching Successfully... ' || i);
344 end if;
345 commit;
346 end if;
347
348 if (l_debug = 1) then
349 trace(l_proc || ' Concurrent Request Id ' || l_purge_req_id|| ' Submitted' );
350 trace(l_proc || ' WMS Task Purge Worker Number = ' || i|| ' Launched');
351 end if;
352
353 --l_num_of_workers_launched := l_num_of_workers_launched + 1;
354 if (l_debug = 1) then
355 trace(l_proc || ' l_num_of_workers_launched ' || i);
356 end if;
357
358 l_reqstatus_tbl_type(i) := l_purge_req_id;
359 if (l_debug = 1) then
360 trace(l_proc || ' l_reqstatus_tbl_type(' || i ||') => '|| l_reqstatus_tbl_type(i));
361 end if;
362 end loop;-- Marker End Loop for call to the Archiving Task Worker
363 if (l_debug = 1) then
364 trace(l_proc || ' Outside the For Loop...');
365 end if;
366 end if;
367 close c_get_total_eligible_recs;
368 end if ;
369
370 if (l_debug = 1) then
371 trace(l_proc || ' The following Worker Requests have been launched :');
372 end if;
373
374 for i in 1..l_reqstatus_tbl_type.count
375 loop
376 if (l_debug = 1) then
377 trace(l_proc || ' Worker Number ' || i || '...Concurrent Request ID ' || l_reqstatus_tbl_type(i));
378 trace(l_proc || ' Please monitor for concurrent request failures....');
379 end if;
380 end loop;
381
382 x_retcode := 0;
383 x_errbuf := 'Success';
384 exception
385 when fnd_api.g_exc_error then
386 if (l_debug = 1) then
387 trace(l_proc || ' fnd_api.g_exc_error :' || sqlcode);
388 trace(l_proc || ' fnd_api.g_exc_error :' || substr(sqlerrm, 1, 100));
389 end if;
390
391 if c_get_total_eligible_recs%ISOPEN then
392 close c_get_total_eligible_recs;
393 end if;
394
395 x_retcode := 2;
396 x_errbuf := 'Error';
397 return;
398 when submission_error_except then
399 if (l_debug = 1) then
400 trace(l_proc || ' submission_error_except :' || sqlcode);
401 trace(l_proc || ' submission_error_except :' || substr(sqlerrm, 1, 100));
402
403 trace(l_proc || ' Number of workers launched before submission failure :' || i);
404 trace(l_proc || ' Date Range for the last successful worker submission :');
405 trace(l_proc || ' From Date = ' || l_from_date || ' .....To Date= ' || l_to_date);
406
407 trace(l_proc || ' The following Worker Requests have been launched before the last worker failed to Launch :');
408 for i in 1..l_reqstatus_tbl_type.count
409 loop
410 trace(l_proc || ' Worker Number ' || i || '...Concurrent Request ID ' || l_reqstatus_tbl_type(i));
411 end loop;
412 end if;
413
414 if c_get_total_eligible_recs%ISOPEN then
415 close c_get_total_eligible_recs;
416 end if;
417
418 x_retcode := 2;
419 x_errbuf := 'Error';
420 return;
421 when others then
422 if (l_debug = 1) then
423 trace(l_proc || ' Other error :' || sqlcode);
424 trace(l_proc || ' Other error :' || substr(sqlerrm, 1, 100));
425
426 trace(l_proc || ' Number of workers launched before submission failure :' || i);
427 trace(l_proc || ' Date Range for the last successful worker submission :');
428 trace(l_proc || ' From Date = ' || l_from_date || ' .....To Date= ' || l_to_date);
429
430 trace(l_proc || ' The following Worker Requests have been launched before the last worker failed to Launch :');
431 for i in 1..l_reqstatus_tbl_type.count
432 loop
433 trace(l_proc || ' Worker Number ' || i || '...Concurrent Request ID ' || l_reqstatus_tbl_type(i));
434 end loop;
435 end if;
436
437 if c_get_total_eligible_recs%ISOPEN then
438 close c_get_total_eligible_recs;
439 end if;
440
441 x_retcode := 2;
442 x_errbuf := 'Error';
443 return;
444 end archive_tasks;
445 --
446 --
447 -- ---------------------------------------------------------------------------------------
448 -- |-------------------------------< archive_tasks_worker >-----------------------------|
449 -- ---------------------------------------------------------------------------------------
450 -- {Start Of Comments}
451 --
452 -- Description:
453 -- Archives tasks records based on organization.
454 --
455 -- Package-Procedure combination
456 --
457 -- Prerequisites:
458 --
459 --
460 --
461 -- In Parameters:
462 -- Name Reqd Type Description
463 -- x_errbuf Yes varchar2 Concurrent Manager Parameter.
464 -- x_retcode Yes varchar2 Concurrent Manager Parameter. -- x_subinventory_code Yes varchar2 Call procedure to be registered
465 -- p_from_date Yes number From Date for archive process.
466 -- p_to_date Yes number To date for the archive process
467 -- p_org_code Yes varchar2 Organization Code for which data needs to be purged.
468 --
469 --
470 -- Post Success:
471 -- Data in the history table are deleted once the Archive tables are populated apropriately.
472 --
473 -- Post Failure:
474 -- No data archiving takes place,
475 --
476 -- Access Status:
477 -- Internal Development Use Only.
478 --
479 -- {End Of Comments}
480 --
481 Procedure archive_tasks_worker(
482 x_errbuf out nocopy varchar2
483 , x_retcode out nocopy number
484 , p_from_date in varchar2
485 , p_to_date in varchar2
486 , p_org_id in number
487 ) is
488
489 l_proc varchar2(72) := 'ARCHIVE_WMS_TASKS_WORKER :';
490 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
491
492 l_organization_id number;
493 l_number_of_records number;
494
495 l_min_date date;
496 l_max_date date;
497
498 begin
499 savepoint archiving_task_savepoint;
500 if (l_debug = 1) then
501 trace(l_proc || ' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'RRRR/MM/DD HH24:MI:SS'), 1);
502 trace(l_proc || ' p_from_date => ' || p_from_date);
503 trace(l_proc || ' p_to_date => ' || p_to_date);
504 trace(l_proc || ' p_org_id => ' || p_org_id);
505 end if;
506
507 l_min_date := to_date(p_from_date, 'RRRR/MM/DD HH24:MI:SS');
508 l_max_date := to_date(p_to_date, 'RRRR/MM/DD HH24:MI:SS');
509
510 if l_max_date < l_min_date then
511 if (l_debug = 1) then
512 trace(l_proc || 'To date cannot be less than From date');
513 end if;
514 raise fnd_api.g_exc_error;
515 end if;
516
517 -- @@@ Insert section.
518 -- @@@ Insert records from the wms_dispatched_tasks_history into wms_dispatched_tasks_arch
519 -- @@@ where parent_transaction_id is not null and task_type in (2,8)
520 if (l_debug = 1) then
521 trace(l_proc || ' Start of insert into wms_dispatched_tasks_arch ...');
522 trace(l_proc || ' for parent_transaction_id is not null and task_type in (2,8)');
523 end if;
524
525 insert into wms_dispatched_tasks_arch(
526 TASK_ID
527 ,TRANSACTION_ID
528 ,ORGANIZATION_ID
529 ,USER_TASK_TYPE
530 ,PERSON_ID
531 ,EFFECTIVE_START_DATE
532 ,EFFECTIVE_END_DATE
533 ,EQUIPMENT_ID
534 ,EQUIPMENT_INSTANCE
535 ,PERSON_RESOURCE_ID
536 ,MACHINE_RESOURCE_ID
537 ,STATUS
538 ,DISPATCHED_TIME
539 ,LOADED_TIME
540 ,DROP_OFF_TIME
541 ,LAST_UPDATE_DATE
542 ,LAST_UPDATED_BY
543 ,CREATION_DATE
544 ,CREATED_BY
545 ,LAST_UPDATE_LOGIN
546 ,ATTRIBUTE_CATEGORY
547 ,ATTRIBUTE1
548 ,ATTRIBUTE2
549 ,ATTRIBUTE3
550 ,ATTRIBUTE4
551 ,ATTRIBUTE5
552 ,ATTRIBUTE6
553 ,ATTRIBUTE7
554 ,ATTRIBUTE8
555 ,ATTRIBUTE9
556 ,ATTRIBUTE10
557 ,ATTRIBUTE11
558 ,ATTRIBUTE12
559 ,ATTRIBUTE13
560 ,ATTRIBUTE14
561 ,ATTRIBUTE15
562 ,TASK_TYPE
563 ,PRIORITY
564 ,TASK_GROUP_ID
565 ,SUGGESTED_DEST_SUBINVENTORY
566 ,SUGGESTED_DEST_LOCATOR_ID
567 ,OPERATION_PLAN_ID
568 ,MOVE_ORDER_LINE_ID
569 ,TRANSFER_LPN_ID
570 ,TRANSACTION_BATCH_ID
571 ,TRANSACTION_BATCH_SEQ
572 ,INVENTORY_ITEM_ID
573 ,REVISION
574 ,TRANSACTION_QUANTITY
575 ,TRANSACTION_UOM_CODE
576 ,SOURCE_SUBINVENTORY_CODE
577 ,SOURCE_LOCATOR_ID
578 ,DEST_SUBINVENTORY_CODE
579 ,DEST_LOCATOR_ID
580 ,LPN_ID
581 ,CONTENT_LPN_ID
582 ,IS_PARENT
583 ,PARENT_TRANSACTION_ID
584 ,TRANSFER_ORGANIZATION_ID
585 ,SOURCE_DOCUMENT_ID
586 ,OP_PLAN_INSTANCE_ID
587 ,TASK_METHOD
588 ,TRANSACTION_TYPE_ID
589 ,TRANSACTION_SOURCE_TYPE_ID
590 ,TRANSACTION_ACTION_ID)
591 select
592 wdth.TASK_ID
593 ,wdth.TRANSACTION_ID
594 ,wdth.ORGANIZATION_ID
595 ,wdth.USER_TASK_TYPE
596 ,wdth.PERSON_ID
597 ,wdth.EFFECTIVE_START_DATE
598 ,wdth.EFFECTIVE_END_DATE
599 ,wdth.EQUIPMENT_ID
600 ,wdth.EQUIPMENT_INSTANCE
601 ,wdth.PERSON_RESOURCE_ID
602 ,wdth.MACHINE_RESOURCE_ID
603 ,wdth.STATUS
604 ,wdth.DISPATCHED_TIME
605 ,wdth.LOADED_TIME
606 ,wdth.DROP_OFF_TIME
607 ,wdth.LAST_UPDATE_DATE
608 ,wdth.LAST_UPDATED_BY
609 ,wdth.CREATION_DATE
610 ,wdth.CREATED_BY
611 ,wdth.LAST_UPDATE_LOGIN
612 ,wdth.ATTRIBUTE_CATEGORY
613 ,wdth.ATTRIBUTE1
614 ,wdth.ATTRIBUTE2
615 ,wdth.ATTRIBUTE3
616 ,wdth.ATTRIBUTE4
617 ,wdth.ATTRIBUTE5
618 ,wdth.ATTRIBUTE6
619 ,wdth.ATTRIBUTE7
620 ,wdth.ATTRIBUTE8
621 ,wdth.ATTRIBUTE9
622 ,wdth.ATTRIBUTE10
623 ,wdth.ATTRIBUTE11
624 ,wdth.ATTRIBUTE12
625 ,wdth.ATTRIBUTE13
626 ,wdth.ATTRIBUTE14
627 ,wdth.ATTRIBUTE15
628 ,wdth.TASK_TYPE
629 ,wdth.PRIORITY
630 ,wdth.TASK_GROUP_ID
631 ,wdth.SUGGESTED_DEST_SUBINVENTORY
632 ,wdth.SUGGESTED_DEST_LOCATOR_ID
633 ,wdth.OPERATION_PLAN_ID
634 ,wdth.MOVE_ORDER_LINE_ID
635 ,wdth.TRANSFER_LPN_ID
636 ,wdth.TRANSACTION_BATCH_ID
637 ,wdth.TRANSACTION_BATCH_SEQ
638 ,wdth.INVENTORY_ITEM_ID
639 ,wdth.REVISION
640 ,wdth.TRANSACTION_QUANTITY
641 ,wdth.TRANSACTION_UOM_CODE
642 ,wdth.SOURCE_SUBINVENTORY_CODE
643 ,wdth.SOURCE_LOCATOR_ID
644 ,wdth.DEST_SUBINVENTORY_CODE
645 ,wdth.DEST_LOCATOR_ID
646 ,wdth.LPN_ID
647 ,wdth.CONTENT_LPN_ID
648 ,wdth.IS_PARENT
649 ,wdth.PARENT_TRANSACTION_ID
650 ,wdth.TRANSFER_ORGANIZATION_ID
651 ,wdth.SOURCE_DOCUMENT_ID
652 ,wdth.OP_PLAN_INSTANCE_ID
653 ,wdth.TASK_METHOD
654 ,wdth.TRANSACTION_TYPE_ID
655 ,wdth.TRANSACTION_SOURCE_TYPE_ID
656 ,wdth.TRANSACTION_ACTION_ID
657 from wms_dispatched_tasks_history wdth, wms_op_plan_instances_hist wopih
658 where wdth.last_update_date > l_min_date
659 and wdth.last_update_date <= l_max_date
660 and wdth.parent_transaction_id is not null
661 and wdth.task_type in (2,8)
662 and wdth.op_plan_instance_id = wopih.op_plan_instance_id
663 and wdth.organization_id = wopih.organization_id
664 and wdth.organization_id = nvl(p_org_id, wdth.organization_id);
665
666 l_number_of_records := SQL%ROWCOUNT;
667 if (l_debug = 1) then
668 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
669 end if;
670
671 if (l_debug = 1) then
672 trace(l_proc || ' End of insert into wms_dispatched_tasks_arch ...');
673 trace(l_proc || ' for parent_transaction_id is not null and task_type in (2,8)');
674 end if;
675
676 -- @@@ Insert records from the wms_dispatched_tasks_history into wms_dispatched_tasks_arch
677 -- @@@ where parent_transaction_id is null and task_type not in (2,8)
678 if (l_debug = 1) then
679 trace(l_proc || ' Start of insert into wms_dispatched_tasks_arch ...');
680 trace(l_proc || ' for parent_transaction_id is null and task_type not in (2,8)');
681 end if;
682
683 insert into wms_dispatched_tasks_arch(
684 TASK_ID
685 ,TRANSACTION_ID
686 ,ORGANIZATION_ID
687 ,USER_TASK_TYPE
688 ,PERSON_ID
689 ,EFFECTIVE_START_DATE
690 ,EFFECTIVE_END_DATE
691 ,EQUIPMENT_ID
692 ,EQUIPMENT_INSTANCE
693 ,PERSON_RESOURCE_ID
694 ,MACHINE_RESOURCE_ID
695 ,STATUS
696 ,DISPATCHED_TIME
697 ,LOADED_TIME
698 ,DROP_OFF_TIME
699 ,LAST_UPDATE_DATE
700 ,LAST_UPDATED_BY
701 ,CREATION_DATE
702 ,CREATED_BY
703 ,LAST_UPDATE_LOGIN
704 ,ATTRIBUTE_CATEGORY
705 ,ATTRIBUTE1
706 ,ATTRIBUTE2
707 ,ATTRIBUTE3
708 ,ATTRIBUTE4
709 ,ATTRIBUTE5
710 ,ATTRIBUTE6
711 ,ATTRIBUTE7
712 ,ATTRIBUTE8
713 ,ATTRIBUTE9
714 ,ATTRIBUTE10
715 ,ATTRIBUTE11
716 ,ATTRIBUTE12
717 ,ATTRIBUTE13
718 ,ATTRIBUTE14
719 ,ATTRIBUTE15
720 ,TASK_TYPE
721 ,PRIORITY
722 ,TASK_GROUP_ID
723 ,SUGGESTED_DEST_SUBINVENTORY
724 ,SUGGESTED_DEST_LOCATOR_ID
725 ,OPERATION_PLAN_ID
726 ,MOVE_ORDER_LINE_ID
727 ,TRANSFER_LPN_ID
728 ,TRANSACTION_BATCH_ID
729 ,TRANSACTION_BATCH_SEQ
730 ,INVENTORY_ITEM_ID
731 ,REVISION
732 ,TRANSACTION_QUANTITY
733 ,TRANSACTION_UOM_CODE
734 ,SOURCE_SUBINVENTORY_CODE
735 ,SOURCE_LOCATOR_ID
736 ,DEST_SUBINVENTORY_CODE
737 ,DEST_LOCATOR_ID
738 ,LPN_ID
739 ,CONTENT_LPN_ID
740 ,IS_PARENT
741 ,PARENT_TRANSACTION_ID
742 ,TRANSFER_ORGANIZATION_ID
743 ,SOURCE_DOCUMENT_ID
744 ,OP_PLAN_INSTANCE_ID
745 ,TASK_METHOD
746 ,TRANSACTION_TYPE_ID
747 ,TRANSACTION_SOURCE_TYPE_ID
748 ,TRANSACTION_ACTION_ID)
749 select
750 TASK_ID
751 ,TRANSACTION_ID
752 ,ORGANIZATION_ID
753 ,USER_TASK_TYPE
754 ,PERSON_ID
755 ,EFFECTIVE_START_DATE
756 ,EFFECTIVE_END_DATE
757 ,EQUIPMENT_ID
758 ,EQUIPMENT_INSTANCE
759 ,PERSON_RESOURCE_ID
760 ,MACHINE_RESOURCE_ID
761 ,STATUS
762 ,DISPATCHED_TIME
763 ,LOADED_TIME
764 ,DROP_OFF_TIME
765 ,LAST_UPDATE_DATE
766 ,LAST_UPDATED_BY
767 ,CREATION_DATE
768 ,CREATED_BY
769 ,LAST_UPDATE_LOGIN
770 ,ATTRIBUTE_CATEGORY
771 ,ATTRIBUTE1
772 ,ATTRIBUTE2
773 ,ATTRIBUTE3
774 ,ATTRIBUTE4
775 ,ATTRIBUTE5
776 ,ATTRIBUTE6
777 ,ATTRIBUTE7
778 ,ATTRIBUTE8
779 ,ATTRIBUTE9
780 ,ATTRIBUTE10
781 ,ATTRIBUTE11
782 ,ATTRIBUTE12
783 ,ATTRIBUTE13
784 ,ATTRIBUTE14
785 ,ATTRIBUTE15
786 ,TASK_TYPE
787 ,PRIORITY
788 ,TASK_GROUP_ID
789 ,SUGGESTED_DEST_SUBINVENTORY
790 ,SUGGESTED_DEST_LOCATOR_ID
791 ,OPERATION_PLAN_ID
792 ,MOVE_ORDER_LINE_ID
793 ,TRANSFER_LPN_ID
794 ,TRANSACTION_BATCH_ID
795 ,TRANSACTION_BATCH_SEQ
796 ,INVENTORY_ITEM_ID
797 ,REVISION
798 ,TRANSACTION_QUANTITY
799 ,TRANSACTION_UOM_CODE
800 ,SOURCE_SUBINVENTORY_CODE
801 ,SOURCE_LOCATOR_ID
802 ,DEST_SUBINVENTORY_CODE
803 ,DEST_LOCATOR_ID
804 ,LPN_ID
805 ,CONTENT_LPN_ID
806 ,IS_PARENT
807 ,PARENT_TRANSACTION_ID
808 ,TRANSFER_ORGANIZATION_ID
809 ,SOURCE_DOCUMENT_ID
810 ,OP_PLAN_INSTANCE_ID
811 ,TASK_METHOD
812 ,TRANSACTION_TYPE_ID
813 ,TRANSACTION_SOURCE_TYPE_ID
814 ,TRANSACTION_ACTION_ID
815 from wms_dispatched_tasks_history wdth
816 where wdth.last_update_date > l_min_date
817 and wdth.last_update_date <= l_max_date
818 and (wdth.parent_transaction_id is null or wdth.task_type not in (2,8))
819 and wdth.organization_id = nvl(p_org_id, wdth.organization_id);
820
821 l_number_of_records := SQL%ROWCOUNT;
822 if (l_debug = 1) then
823 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
824 end if;
825
826 if (l_debug = 1) then
827 trace(l_proc || ' End of insert into wms_dispatched_tasks_arch ...');
828 trace(l_proc || ' for parent_transaction_id is null and task_type not in (2,8)');
829 end if;
830
831 -- @@@ Insert records into the wms_op_plan_instances_arch from the wms_op_plan_instances_hist
832 if (l_debug = 1) then
833 trace(l_proc || ' Start of insert into wms_op_plan_instances_arch ...');
834 end if;
835
836 insert into wms_op_plan_instances_arch(
837 OP_PLAN_INSTANCE_ID
838 ,OPERATION_PLAN_ID
839 ,STATUS
840 ,ORGANIZATION_ID
841 ,PLAN_EXECUTION_START_DATE
842 ,PLAN_EXECUTION_END_DATE
843 ,CREATED_BY
844 ,CREATION_DATE
845 ,LAST_UPDATED_BY
846 ,LAST_UPDATE_DATE
847 ,LAST_UPDATE_LOGIN
848 ,ATTRIBUTE_CATEGORY
849 ,ATTRIBUTE1
850 ,ATTRIBUTE2
851 ,ATTRIBUTE3
852 ,ATTRIBUTE4
853 ,ATTRIBUTE5
854 ,ATTRIBUTE6
855 ,ATTRIBUTE7
856 ,ATTRIBUTE8
857 ,ATTRIBUTE9
858 ,ATTRIBUTE10
859 ,ATTRIBUTE11
860 ,ATTRIBUTE12
861 ,ATTRIBUTE13
862 ,ATTRIBUTE14
863 ,ATTRIBUTE15
864 ,ACTIVITY_TYPE_ID
865 ,PLAN_TYPE_ID
866 ,ORIG_SOURCE_SUB_CODE
867 ,ORIG_SOURCE_LOC_ID
868 ,ORIG_DEST_SUB_CODE
869 ,ORIG_DEST_LOC_ID)
870 select
871 OP_PLAN_INSTANCE_ID
872 ,OPERATION_PLAN_ID
873 ,STATUS
874 ,ORGANIZATION_ID
875 ,PLAN_EXECUTION_START_DATE
876 ,PLAN_EXECUTION_END_DATE
877 ,CREATED_BY
878 ,CREATION_DATE
879 ,LAST_UPDATED_BY
880 ,LAST_UPDATE_DATE
881 ,LAST_UPDATE_LOGIN
882 ,ATTRIBUTE_CATEGORY
883 ,ATTRIBUTE1
884 ,ATTRIBUTE2
885 ,ATTRIBUTE3
886 ,ATTRIBUTE4
887 ,ATTRIBUTE5
888 ,ATTRIBUTE6
889 ,ATTRIBUTE7
890 ,ATTRIBUTE8
891 ,ATTRIBUTE9
892 ,ATTRIBUTE10
893 ,ATTRIBUTE11
894 ,ATTRIBUTE12
895 ,ATTRIBUTE13
896 ,ATTRIBUTE14
897 ,ATTRIBUTE15
898 ,ACTIVITY_TYPE_ID
899 ,PLAN_TYPE_ID
900 ,ORIG_SOURCE_SUB_CODE
901 ,ORIG_SOURCE_LOC_ID
902 ,ORIG_DEST_SUB_CODE
903 ,ORIG_DEST_LOC_ID
904 from wms_op_plan_instances_hist wopih
905 where wopih.last_update_date > l_min_date
906 and wopih.last_update_date <= l_max_date
907 and wopih.organization_id = nvl(p_org_id, wopih.organization_id);
908
909 l_number_of_records := SQL%ROWCOUNT;
910 if (l_debug = 1) then
911 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
912 end if;
913
914 if (l_debug = 1) then
915 trace(l_proc || ' End of insert into wms_op_plan_instances_arch ...');
916 end if;
917
918 -- @@@ Insert records from the wms_op_opertn_instances_hist into wms_op_opertn_instances_arch
919 if (l_debug = 1) then
920 trace(l_proc || ' Start of insert into wms_op_opertn_instances_arch ...');
921 end if;
922
923 insert into wms_op_opertn_instances_arch(
924 OPERATION_INSTANCE_ID
925 ,OP_PLAN_INSTANCE_ID
926 ,ORGANIZATION_ID
927 ,OPERATION_STATUS
928 ,OPERATION_PLAN_DETAIL_ID
929 ,OPERATION_SEQUENCE
930 ,FROM_SUBINVENTORY_CODE
931 ,FROM_LOCATOR_ID
932 ,TO_SUBINVENTORY_CODE
933 ,TO_LOCATOR_ID
934 ,CREATED_BY
935 ,CREATION_DATE
936 ,LAST_UPDATED_BY
937 ,LAST_UPDATE_DATE
938 ,LAST_UPDATE_LOGIN
939 ,ATTRIBUTE_CATEGORY
940 ,ATTRIBUTE1
941 ,ATTRIBUTE2
942 ,ATTRIBUTE3
943 ,ATTRIBUTE4
944 ,ATTRIBUTE5
945 ,ATTRIBUTE6
946 ,ATTRIBUTE7
947 ,ATTRIBUTE8
948 ,ATTRIBUTE9
949 ,ATTRIBUTE10
950 ,ATTRIBUTE11
951 ,ATTRIBUTE12
952 ,ATTRIBUTE13
953 ,ATTRIBUTE14
954 ,ATTRIBUTE15
955 ,OPERATION_TYPE_ID
956 ,ACTIVITY_TYPE_ID
957 ,SUG_TO_SUB_CODE
958 ,SUG_TO_LOCATOR_ID
959 ,SOURCE_TASK_ID
960 ,EMPLOYEE_ID
961 ,EQUIPMENT_ID
962 ,ACTIVATE_TIME
963 ,COMPLETE_TIME
964 ,IS_IN_INVENTORY)
965 select
966 OPERATION_INSTANCE_ID
967 ,OP_PLAN_INSTANCE_ID
968 ,ORGANIZATION_ID
969 ,OPERATION_STATUS
970 ,OPERATION_PLAN_DETAIL_ID
971 ,OPERATION_SEQUENCE
972 ,FROM_SUBINVENTORY_CODE
973 ,FROM_LOCATOR_ID
974 ,TO_SUBINVENTORY_CODE
975 ,TO_LOCATOR_ID
976 ,CREATED_BY
977 ,CREATION_DATE
978 ,LAST_UPDATED_BY
979 ,LAST_UPDATE_DATE
980 ,LAST_UPDATE_LOGIN
981 ,ATTRIBUTE_CATEGORY
982 ,ATTRIBUTE1
983 ,ATTRIBUTE2
984 ,ATTRIBUTE3
985 ,ATTRIBUTE4
986 ,ATTRIBUTE5
987 ,ATTRIBUTE6
988 ,ATTRIBUTE7
989 ,ATTRIBUTE8
990 ,ATTRIBUTE9
991 ,ATTRIBUTE10
992 ,ATTRIBUTE11
993 ,ATTRIBUTE12
994 ,ATTRIBUTE13
995 ,ATTRIBUTE14
996 ,ATTRIBUTE15
997 ,OPERATION_TYPE_ID
998 ,ACTIVITY_TYPE_ID
999 ,SUG_TO_SUB_CODE
1000 ,SUG_TO_LOCATOR_ID
1001 ,SOURCE_TASK_ID
1002 ,EMPLOYEE_ID
1003 ,EQUIPMENT_ID
1004 ,ACTIVATE_TIME
1005 ,COMPLETE_TIME
1006 ,IS_IN_INVENTORY
1007 from wms_op_opertn_instances_hist wooih
1008 where wooih.last_update_date > l_min_date
1009 and wooih.last_update_date <= l_max_date
1010 and wooih.organization_id = nvl(p_org_id, wooih.organization_id);
1011
1012 l_number_of_records := SQL%ROWCOUNT;
1013 if (l_debug = 1) then
1014 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
1015 end if;
1016
1017 if (l_debug = 1) then
1018 trace(l_proc || ' End of insert into wms_op_opertn_instances_arch ...');
1019 end if;
1020
1021 -- @@@ Delete Section
1022 -- @@@ Delete records from wms_dispatched_tasks_arch table.
1023 if (l_debug = 1) then
1024 trace(l_proc || ' Start of delete from wms_dispatched_tasks_history ...');
1025 trace(l_proc || ' for parent_transaction_id is not null and task_type in (2,8)');
1026 end if;
1027
1028 delete from wms_dispatched_tasks_history
1029 where task_id in (
1030 select wdth.task_id
1031 from wms_dispatched_tasks_history wdth, wms_op_plan_instances_hist wopih
1032 where wdth.last_update_date > l_min_date
1033 and wdth.last_update_date <= l_max_date
1034 and wdth.parent_transaction_id is not null
1035 and wdth.task_type in (2,8)
1036 and wdth.op_plan_instance_id = wopih.op_plan_instance_id
1037 and wdth.organization_id = wopih.organization_id
1038 and wdth.organization_id = nvl(p_org_id, wdth.organization_id));
1039
1040 l_number_of_records := SQL%ROWCOUNT;
1041 if (l_debug = 1) then
1042 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
1043 end if;
1044
1045 if (l_debug = 1) then
1046 trace(l_proc || ' End of delete from wms_dispatched_tasks_history ...');
1047 trace(l_proc || ' for parent_transaction_id is not null and task_type in (2,8)');
1048 end if;
1049
1050 -- @@@ Delete records from wms_dispatched_tasks_arch table.
1051 if (l_debug = 1) then
1052 trace(l_proc || ' Start of delete from wms_dispatched_tasks_history ...');
1053 trace(l_proc || ' for parent_transaction_id is null and task_type not in (2,8)');
1054 end if;
1055
1056 delete from wms_dispatched_tasks_history wdth
1057 where wdth.last_update_date > l_min_date
1058 and wdth.last_update_date <= l_max_date
1059 and (wdth.parent_transaction_id is null or wdth.task_type not in (2,8))
1060 and wdth.organization_id = nvl(p_org_id, wdth.organization_id);
1061
1062 l_number_of_records := SQL%ROWCOUNT;
1063 if (l_debug = 1) then
1064 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
1065 end if;
1066
1067 if (l_debug = 1) then
1068 trace(l_proc || ' End of delete from wms_dispatched_tasks_history ...');
1069 trace(l_proc || ' for parent_transaction_id is null and task_type not in (2,8)');
1070 end if;
1071
1072 -- @@@ Delete records from the wms_op_plan_instances_hist table.
1073 if (l_debug = 1) then
1074 trace(l_proc || ' Start of delete from wms_op_plan_instances_hist ...');
1075 end if;
1076
1077 delete from wms_op_plan_instances_hist wopih
1078 where wopih.last_update_date > l_min_date
1079 and wopih.last_update_date <= l_max_date
1080 and wopih.organization_id = nvl(p_org_id, wopih.organization_id);
1081
1082 l_number_of_records := SQL%ROWCOUNT;
1083 if (l_debug = 1) then
1084 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
1085 end if;
1086
1087 if (l_debug = 1) then
1088 trace(l_proc || ' End of delete from wms_op_plan_instances_hist ...');
1089 end if;
1090
1091 -- @@@ Delete records from the wms_op_opertn_instances_hist table
1092 if (l_debug = 1) then
1093 trace(l_proc || ' Start of delete from wms_op_opertn_instances_hist ...');
1094 end if;
1095
1096 delete from wms_op_opertn_instances_hist wooih
1097 where wooih.last_update_date > l_min_date
1098 and wooih.last_update_date <= l_max_date
1099 and wooih.organization_id = nvl(p_org_id, wooih.organization_id);
1100
1101 l_number_of_records := SQL%ROWCOUNT;
1102 if (l_debug = 1) then
1103 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
1104 end if;
1105
1106 if (l_debug = 1) then
1107 trace(l_proc || ' End of delete from wms_op_opertn_instances_hist ...');
1108 end if;
1109
1110 -- @@@ Delete records from the wms_op_opertn_instances_hist table
1111 if (l_debug = 1) then
1112 trace(l_proc || ' Start of delete from wms_exceptions ...');
1113 end if;
1114
1115 delete from wms_exceptions wex
1116 where wex.creation_date > l_min_date
1117 and wex.creation_date <= l_max_date
1118 and wex.organization_id = nvl(p_org_id, wex.organization_id);
1119
1120 if (l_debug = 1) then
1121 trace(l_proc || ' End of delete from wms_exceptions ...');
1122 end if;
1123
1124 if (l_debug = 1) then
1125 trace(l_proc || ' Before commit in Worker...');
1126 end if;
1127
1128 commit;
1129 if (l_debug = 1) then
1130 trace(l_proc || ' After commit in Worker...');
1131 end if;
1132
1133
1134 x_retcode := 0;
1135 x_errbuf := 'Success';
1136 exception
1137 when fnd_api.g_exc_error then
1138 if (l_debug = 1) then
1139 trace(l_proc || ' fnd_api.g_exc_error :' || sqlcode);
1140 trace(l_proc || ' fnd_api.g_exc_error :' || substr(sqlerrm, 1, 100));
1141 end if;
1142
1143 rollback to archiving_task_savepoint;
1144 x_retcode := 2;
1145 x_errbuf := 'Error';
1146 return;
1147 when others then
1148 if (l_debug = 1) then
1149 trace(l_proc || ' SQL Error Code :' || sqlcode);
1150 trace(l_proc || ' SQL Error Message :' || substr(sqlerrm, 1, 100));
1151 end if;
1152
1153 rollback to archiving_task_savepoint;
1154
1155 x_retcode := 2;
1156 x_errbuf := 'Error';
1157 return;
1158 end archive_tasks_worker;
1159 --
1160 --
1161 -- ---------------------------------------------------------------------------------------
1162 -- |-------------------------------< unarchive_tasks >------------------------------------|
1163 -- ---------------------------------------------------------------------------------------
1164 -- {Start Of Comments}
1165 --
1166 -- Description:
1167 -- Archives tasks records based on organization.
1168 --
1169 -- Package-Procedure combination
1170 --
1171 -- Prerequisites:
1172 --
1173 --
1174 --
1175 -- In Parameters:
1176 -- Name Reqd Type Description
1177 -- x_errbuf Yes varchar2 Concurrent Manager Parameter.
1178 -- x_retcode Yes varchar2 Concurrent Manager Parameter.
1179 -- p_from_date Yes varchar2 date from which records need to be restored.
1180 -- p_to_date Yes varchar2 date to which records need to be restored.
1181 -- p_org_code Yes number Organization Code for the process.
1182 -- p_unarch_batches Yes number Number of batches into which the records
1183 -- needs to be broken up.
1184 --
1185 --
1186 -- Post Success:
1187 -- Data in the history table are deleted once the Archive tables are populated apropriately.
1188 --
1189 -- Post Failure:
1190 -- No data archiving takes place,
1191 --
1192 -- Access Status:
1193 -- Internal Development Use Only.
1194 --
1195 -- {End Of Comments}
1196 --
1197 Procedure unarchive_tasks(
1198 x_errbuf out nocopy varchar2
1199 , x_retcode out nocopy number
1200 , p_from_date in varchar2
1201 , p_to_date in varchar2
1202 , p_org_id in number
1203 , p_unarch_batches in number
1204 ) is
1205
1206 l_proc varchar2(72) := 'UNARCHIVE_WMS_TASKS :';
1207 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1208
1209 l_max_date_time varchar2(50):= null;
1210 l_sys_date varchar2(50):= null;
1211
1212 l_total_record_count number:= 0;
1213 l_min_range_date_time varchar2(50):= null;
1214 l_max_range_date_time varchar2(50):= null;
1215 l_days_between number := 0;
1216 l_each_worker_chunk number:= 0;
1217
1218
1219 l_record_per_worker number:= 0;
1220 l_number_workers number:= 0;
1221 l_organization_id number:= 0;
1222
1223 l_loop_counter number:= 1;
1224 l_from_date date;
1225 l_to_date date;
1226 l_num_batches number:= 0;
1227 l_purge_req_id number;
1228
1229 l_min_date date;
1230 l_max_date date;
1231
1232 type l_reqstatus_table is table of number
1233 index by binary_integer;
1234
1235 l_reqstatus_tbl_type l_reqstatus_table;
1236 --l_num_of_workers_launched number;
1237
1238 submission_error_except exception;
1239 i number;
1240 l_number_of_records number;
1241
1242
1243 -- ### This cursor gets the record count and days between the p_from_date and p_to_date.
1244 -- ### Data in this tables which lies betweent he range provided is elligible to be moved
1245 -- ### back to the history tables.
1246 cursor c_get_total_eligible_recs is
1247 select count(*), (l_max_date - l_min_date)
1248 from wms_dispatched_tasks_arch
1249 where last_update_date > l_min_date
1250 and last_update_date <= l_max_date
1251 and organization_id = nvl(p_org_id, organization_id);
1252 --
1253 --
1254 begin
1255 savepoint unarch_task_master_savepoint;
1256 if (l_debug = 1) then
1257 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'RRRR/MM/DD HH24:MI:SS'), 1);
1258 trace(l_proc || ' p_from_date => ' || p_from_date);
1259 trace(l_proc || ' p_to_date => ' || p_to_date);
1260 trace(l_proc || ' p_org_id => ' || p_org_id);
1261 trace(l_proc || ' p_unarch_batches => ' || p_unarch_batches);
1262 end if;
1263
1264 if (l_debug = 1) then
1265 trace(l_proc || ' Before validating date...');
1266 end if;
1267
1268 l_min_date := to_date(p_from_date, 'RRRR/MM/DD HH24:MI:SS');
1269 l_max_date := to_date(p_to_date, 'RRRR/MM/DD HH24:MI:SS');
1270
1271 if (l_debug = 1) then
1272 trace(l_proc || ' l_min_date => ' || to_char(l_min_date, 'RRRR/MM/DD HH24:MI:SS'));
1273 trace(l_proc || ' l_max_date => ' || to_char(l_max_date, 'RRRR/MM/DD HH24:MI:SS'));
1274 end if;
1275
1276 -- @@@ Validating input parameters.
1277 if (l_max_date < l_min_date) then
1278 if (l_debug = 1) then
1279 trace(l_proc || 'To date cannot be less than From date', 9);
1280 end if;
1281 raise fnd_api.g_exc_error;
1282 end if;
1283
1284 if (l_debug = 1) then
1285 trace(l_proc || ' After validating date...');
1286 end if;
1287
1288 if (l_debug = 1) then
1289 trace(l_proc || ' Before validating p_unarch_batches...');
1290 end if;
1291
1292 if (l_debug = 1) then
1293 trace(l_proc || ' After validating p_unarch_batches...');
1294 end if;
1295
1296 -- @@@ Get total number of eligible records to be archived.
1297 open c_get_total_eligible_recs;
1298 fetch c_get_total_eligible_recs
1299 into l_total_record_count, l_days_between;
1300
1301 if l_total_record_count = 0 then
1302 if (l_debug = 1) then
1303 trace(l_proc || ' Eligible records not found for Unarchiving for date range provided... ');
1304 end if;
1305
1306 close c_get_total_eligible_recs;
1307 raise fnd_api.g_exc_error;
1308 --return;
1309 elsif l_total_record_count > 0 then
1310 if (l_debug = 1) then
1311 trace(l_proc || ' l_total_record_count => '|| nvl(l_total_record_count, -99));
1312 trace(l_proc || ' l_days_between => '|| nvl(l_days_between, -99));
1313 trace(l_proc || ' Before Checking l_total_record_count and setting value for the l_record_per_worker variable...');
1314 end if;
1315
1316 -- @@@ Determine number of records to be processed by each worker.
1317 -- @@@ If the total number of records is less than a million then its set to 50000 else 300000;
1318 if l_total_record_count < 100000 then
1319 l_record_per_worker := c_records_per_worker;
1320 else
1321 l_record_per_worker := c_record_hi_number_per_worker;
1322 end if;
1323
1324 if (l_debug = 1) then
1325 trace(l_proc || ' l_record_per_worker => '|| l_record_per_worker);
1326 trace(l_proc || ' After Checking l_total_record_count and setting value for the l_record_per_worker variable...');
1327 trace(l_proc || ' Before calculating value for l_num_batches variable...');
1328 trace(l_proc || ' l_total_record_count => ' || l_total_record_count);
1329 trace(l_proc || ' l_record_per_worker => ' || l_record_per_worker);
1330 trace(l_proc || ' p_unarch_batches => ' || p_unarch_batches);
1331 end if;
1332
1333 -- @@@ Calculate the number of workers required for this run.
1334 -- @@@ The entire batch will be divided between multiple workers.
1335 l_num_batches := ceil(l_total_record_count/l_record_per_worker);
1336
1337 if (l_debug = 1) then
1338 trace(l_proc || ' l_num_batches => ' || l_num_batches);
1339 end if;
1340
1341 if p_unarch_batches > l_num_batches then
1342 l_number_workers:= l_num_batches;
1343 else
1344 l_number_workers:= p_unarch_batches;
1345 end if;
1346
1347 if (l_debug = 1) then
1348 trace(l_proc || ' l_number_workers => '|| l_number_workers);
1349 trace(l_proc || ' After calculating value for l_num_batches variable...');
1350 end if;
1351
1352
1353 -- @@@ Get the chunk of data in terms of days to be assigned to each worker.
1354 l_each_worker_chunk := l_days_between/l_number_workers;
1355 if (l_debug = 1) then
1356 trace(l_proc || ' l_each_worker_chunk '|| l_each_worker_chunk);
1357 end if;
1358
1359 if (l_debug = 1) then
1360 trace(l_proc || ' Start of Insert based on the exception condition...');
1361 end if;
1362
1363 --savepoint unarch_task_master_savepoint;
1364 -- @@@ Insert section.
1365 -- @@@ Insert records from the wms_dispatched_tasks_arch into wms_dispatched_tasks_history
1366 -- @@@ These records are those which may have be missed in the worker cursor.
1367 -- @@@ For every wopia, there can be multiple records in the wdta. Since the main cursor in
1368 -- @@@ the master, queries on the wdta, there is a chance that the from and to date
1369 -- @@@ specified by the user may not get all the related records satisfied by the condition
1370 -- @@@ mentioned in this SQL. This makes sure that there is no data inconsistency.
1371 insert into wms_dispatched_tasks_history(
1372 TASK_ID
1373 ,TRANSACTION_ID
1374 ,ORGANIZATION_ID
1375 ,USER_TASK_TYPE
1376 ,PERSON_ID
1377 ,EFFECTIVE_START_DATE
1378 ,EFFECTIVE_END_DATE
1379 ,EQUIPMENT_ID
1380 ,EQUIPMENT_INSTANCE
1381 ,PERSON_RESOURCE_ID
1382 ,MACHINE_RESOURCE_ID
1383 ,STATUS
1384 ,DISPATCHED_TIME
1385 ,LOADED_TIME
1386 ,DROP_OFF_TIME
1387 ,LAST_UPDATE_DATE
1388 ,LAST_UPDATED_BY
1389 ,CREATION_DATE
1390 ,CREATED_BY
1391 ,LAST_UPDATE_LOGIN
1392 ,ATTRIBUTE_CATEGORY
1393 ,ATTRIBUTE1
1394 ,ATTRIBUTE2
1395 ,ATTRIBUTE3
1396 ,ATTRIBUTE4
1397 ,ATTRIBUTE5
1398 ,ATTRIBUTE6
1399 ,ATTRIBUTE7
1400 ,ATTRIBUTE8
1401 ,ATTRIBUTE9
1402 ,ATTRIBUTE10
1403 ,ATTRIBUTE11
1404 ,ATTRIBUTE12
1405 ,ATTRIBUTE13
1406 ,ATTRIBUTE14
1407 ,ATTRIBUTE15
1408 ,TASK_TYPE
1409 ,PRIORITY
1410 ,TASK_GROUP_ID
1411 ,SUGGESTED_DEST_SUBINVENTORY
1412 ,SUGGESTED_DEST_LOCATOR_ID
1413 ,OPERATION_PLAN_ID
1414 ,MOVE_ORDER_LINE_ID
1415 ,TRANSFER_LPN_ID
1416 ,TRANSACTION_BATCH_ID
1417 ,TRANSACTION_BATCH_SEQ
1418 ,INVENTORY_ITEM_ID
1419 ,REVISION
1420 ,TRANSACTION_QUANTITY
1421 ,TRANSACTION_UOM_CODE
1422 ,SOURCE_SUBINVENTORY_CODE
1423 ,SOURCE_LOCATOR_ID
1424 ,DEST_SUBINVENTORY_CODE
1425 ,DEST_LOCATOR_ID
1426 ,LPN_ID
1427 ,CONTENT_LPN_ID
1428 ,IS_PARENT
1429 ,PARENT_TRANSACTION_ID
1430 ,TRANSFER_ORGANIZATION_ID
1431 ,SOURCE_DOCUMENT_ID
1432 ,OP_PLAN_INSTANCE_ID
1433 ,TASK_METHOD
1434 ,TRANSACTION_TYPE_ID
1435 ,TRANSACTION_SOURCE_TYPE_ID
1436 ,TRANSACTION_ACTION_ID)
1437 select
1438 wdta.TASK_ID
1439 ,wdta.TRANSACTION_ID
1440 ,wdta.ORGANIZATION_ID
1441 ,wdta.USER_TASK_TYPE
1442 ,wdta.PERSON_ID
1443 ,wdta.EFFECTIVE_START_DATE
1444 ,wdta.EFFECTIVE_END_DATE
1445 ,wdta.EQUIPMENT_ID
1446 ,wdta.EQUIPMENT_INSTANCE
1447 ,wdta.PERSON_RESOURCE_ID
1448 ,wdta.MACHINE_RESOURCE_ID
1449 ,wdta.STATUS
1450 ,wdta.DISPATCHED_TIME
1451 ,wdta.LOADED_TIME
1452 ,wdta.DROP_OFF_TIME
1453 ,wdta.LAST_UPDATE_DATE
1454 ,wdta.LAST_UPDATED_BY
1455 ,wdta.CREATION_DATE
1456 ,wdta.CREATED_BY
1457 ,wdta.LAST_UPDATE_LOGIN
1458 ,wdta.ATTRIBUTE_CATEGORY
1459 ,wdta.ATTRIBUTE1
1460 ,wdta.ATTRIBUTE2
1461 ,wdta.ATTRIBUTE3
1462 ,wdta.ATTRIBUTE4
1463 ,wdta.ATTRIBUTE5
1464 ,wdta.ATTRIBUTE6
1465 ,wdta.ATTRIBUTE7
1466 ,wdta.ATTRIBUTE8
1467 ,wdta.ATTRIBUTE9
1468 ,wdta.ATTRIBUTE10
1469 ,wdta.ATTRIBUTE11
1470 ,wdta.ATTRIBUTE12
1471 ,wdta.ATTRIBUTE13
1472 ,wdta.ATTRIBUTE14
1473 ,wdta.ATTRIBUTE15
1474 ,wdta.TASK_TYPE
1475 ,wdta.PRIORITY
1476 ,wdta.TASK_GROUP_ID
1477 ,wdta.SUGGESTED_DEST_SUBINVENTORY
1478 ,wdta.SUGGESTED_DEST_LOCATOR_ID
1479 ,wdta.OPERATION_PLAN_ID
1480 ,wdta.MOVE_ORDER_LINE_ID
1481 ,wdta.TRANSFER_LPN_ID
1482 ,wdta.TRANSACTION_BATCH_ID
1483 ,wdta.TRANSACTION_BATCH_SEQ
1484 ,wdta.INVENTORY_ITEM_ID
1485 ,wdta.REVISION
1486 ,wdta.TRANSACTION_QUANTITY
1487 ,wdta.TRANSACTION_UOM_CODE
1488 ,wdta.SOURCE_SUBINVENTORY_CODE
1489 ,wdta.SOURCE_LOCATOR_ID
1490 ,wdta.DEST_SUBINVENTORY_CODE
1491 ,wdta.DEST_LOCATOR_ID
1492 ,wdta.LPN_ID
1493 ,wdta.CONTENT_LPN_ID
1494 ,wdta.IS_PARENT
1495 ,wdta.PARENT_TRANSACTION_ID
1496 ,wdta.TRANSFER_ORGANIZATION_ID
1497 ,wdta.SOURCE_DOCUMENT_ID
1498 ,wdta.OP_PLAN_INSTANCE_ID
1499 ,wdta.TASK_METHOD
1500 ,wdta.TRANSACTION_TYPE_ID
1501 ,wdta.TRANSACTION_SOURCE_TYPE_ID
1502 ,wdta.TRANSACTION_ACTION_ID
1503 from wms_dispatched_tasks_arch wdta, wms_op_plan_instances_arch wopia
1504 where wdta.last_update_date < l_min_date
1505 and wopia.op_plan_instance_id = wdta.op_plan_instance_id
1506 and wdta.organization_id = wopia.organization_id
1507 and wdta.organization_id = nvl(p_org_id, wdta.organization_id)
1508 and wopia.last_update_date > l_min_date
1509 and wopia.last_update_date <= l_max_date;
1510
1511 l_number_of_records := SQL%ROWCOUNT;
1512 if (l_debug = 1) then
1513 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
1514 end if;
1515
1516 if (l_debug = 1) then
1517 trace(l_proc || ' End of Insert based on the exception condition...');
1518 end if;
1519
1520 if (l_debug = 1) then
1521 trace(l_proc || ' Start of Delete based on the exception condition...');
1522 end if;
1523
1524 -- @@@ Delete this information from the wms_dispatched_tasks_arch table after inserting.
1525 delete from wms_dispatched_tasks_arch
1526 where task_id in (
1527 select wdta.task_id
1528 from wms_dispatched_tasks_arch wdta, wms_op_plan_instances_arch wopia
1529 where wdta.last_update_date <= l_min_date
1530 and wopia.op_plan_instance_id = wdta.op_plan_instance_id
1531 and wdta.organization_id = wopia.organization_id
1532 and wdta.organization_id = nvl(p_org_id, wdta.organization_id)
1533 and wopia.last_update_date > l_min_date
1534 and wopia.last_update_date <= l_max_date);
1535
1536 l_number_of_records := SQL%ROWCOUNT;
1537 if (l_debug = 1) then
1538 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
1539 end if;
1540
1541 if (l_debug = 1) then
1542 trace(l_proc || ' End of Delete based on the exception condition...');
1543 end if;
1544
1545
1546 if l_number_workers > 0 then
1547 if (l_debug = 1) then
1548 trace(l_proc || ' Before Entering the for loop... ');
1549 end if;
1550
1551 -- @@@ Loop to call multiple workers to assign separate batches of records to be processsed.
1552 for i in 1..l_number_workers
1553 loop
1554 -- @@@ The l_loop_counter(i) is used to determine mainly the first run in the loop.
1555 -- @@@ Note that the 'l_from_date' and 'l_to_date' are both defined as date variables.
1556 -- @@@ The 'l_min_range_date_time' is defined as a varchar2 so as to derive the date/time
1557 -- @@@ information to the precision of the last second.
1558 -- @@@ The 'l_min_range_date_time' derived as follows from the
1559 -- @@@ 'c_get_total_eligible_recs' cursor:
1560 -- @@@ "to_char(min(last_update_date), 'MM/DD/YY HH:MI:SS')"
1561 -- @@@ Since the 'l_min_range_date_time' is a varchar, the fnd_date.displaydate_to_date()
1562 -- @@@ is used to convert it to a date and assign to the l_from_date(date variable).
1563 -- @@@ This only needs to be done the very first time since in the subsequence runs,
1564 -- @@@ date arithmetic is being performed with the SELECT from dual.
1565 -- @@@
1566 -- @@@ The logic of deriving the 'l_from_date' and 'l_to_date' for each worker call is as follows:
1567 -- @@@ 1. For the very first run, the 'l_from_date' is equal to the 'l_min_range_date_time'.
1568 -- @@@ This is passed as a date variable to the first worker call.
1569 -- @@@ 2. The 'l_to_date' is computed as the 'l_from_date + l_each_worker_chunk' every time as follows:
1570 -- @@@ The 'l_each_worker_chunk' stores the number of days for each worker.
1571 -- @@@ Hence the
1572 -- @@@ 'select (l_from_date + l_each_worker_chunk) into l_to_date from dual;'
1573 -- @@@ effectively derives the l_to_date with a precision to the last second.
1574 -- @@@ 3. In every subsequent run, date arithmetic is performed on the 'l_from_date' and 'l_to_date'
1575 -- @@@ derived in the previous run prior to the worker call.
1576 -- @@@
1577 -- @@@ One another thing to note is that the subsequent runs, the 'l_from_date' is set to the
1578 -- @@@ 'l_to_date' from the last run in the loop. The SQL in the worker is selecting a range
1579 -- @@@ greater that the 'l_from_date'.
1580 if (i = 1) then
1581 -- @@@ Get the min date range for the first worker call.
1582 --l_from_date := fnd_date.displaydate_to_date(l_min_date);
1583 l_from_date := l_min_date;
1584 if (l_debug = 1) then
1585 trace(l_proc || ' Inside if for (i = 1)...');
1586 trace(l_proc || ' i => ' || i);
1587 trace(l_proc || ' l_from_date => ' || to_char(l_from_date, 'RRRR/MM/DD HH24:MI:SS'));
1588 end if;
1589 elsif (i > 1) then
1590 -- @@@ Get the min date range for subsequent worker call.
1591 l_from_date := l_to_date;
1592 if (l_debug = 1) then
1593 trace(l_proc || ' Inside if for (i > 1)...');
1594 trace(l_proc || ' i => ' || i);
1595 trace(l_proc || ' l_from_date => ' || to_char(l_from_date, 'RRRR/MM/DD HH24:MI:SS'));
1596 end if;
1597 end if;
1598
1599 if (l_debug = 1) then
1600 trace(l_proc || ' l_from_date Outside the if check for counter(i) => ' || to_char(l_from_date, 'RRRR/MM/DD HH24:MI:SS'));
1601 end if;
1602
1603 -- @@@ Get the max date range for the worker call.
1604 -- @@@ If the loop counter value equals the l_number_workers, then set the l_to_date to
1605 if (i = l_number_workers) then
1606 l_to_date := l_max_date;
1607 else
1608 --l_to_date := l_from_date + l_each_worker_chunk;
1609 select (l_from_date + l_each_worker_chunk) into l_to_date from dual;
1610 end if;
1611
1612 if (l_debug = 1) then
1613 trace(l_proc || ' i => ' || i);
1614 trace(l_proc || ' l_to_date => ' || to_char(l_to_date, 'RRRR/MM/DD HH24:MI:SS'));
1615 end if;
1616
1617 if (l_debug = 1) then
1618 trace(l_proc || ' Loop Counter = ' || i);
1619 trace(l_proc || ' Before Launching WMS Task Purge Worker ...');
1620 trace(l_proc || ' For Range, with From Date => ' || to_char(l_from_date, 'RRRR/MM/DD HH24:MI:SS'));
1621 trace(l_proc || ' and To Date => ' || to_char(l_to_date, 'RRRR/MM/DD HH24:MI:SS'));
1622 end if;
1623
1624
1625 -- @@@ Calling the purge worker for a specific date range..
1626 -- @@@
1627 l_purge_req_id := fnd_request.submit_request(application => 'WMS'
1628 , program => 'WMSTUARW'
1629 , argument1 => to_char(l_from_date, 'RRRR/MM/DD HH24:MI:SS')
1630 , argument2 => to_char(l_to_date, 'RRRR/MM/DD HH24:MI:SS')
1631 , argument3 => p_org_id);
1632
1633 if (l_debug = 1) then
1634 trace(l_proc || ' l_purge_req_id => ' || l_purge_req_id);
1635 end if;
1636
1637 -- @@@ Handle worker submission error
1638 -- @@@ Raise exception if failed else commit and proceed.
1639 if (l_purge_req_id = 0) then
1640 if (l_debug = 1) then
1641 trace(l_proc || ' Error launching last Purge Worker........');
1642 end if;
1643 raise submission_error_except;
1644 else
1645 commit;
1646 end if;
1647
1648 if (l_debug = 1) then
1649 trace(l_proc || ' Concurrent Request Id ' || l_purge_req_id|| ' Submitted' );
1650 trace(l_proc || ' WMS Task Purge Worker Number = ' || i || ' Launched');
1651 end if;
1652
1653 --l_num_of_workers_launched := l_num_of_workers_launched + 1;
1654 if (l_debug = 1) then
1655 trace(l_proc || ' l_num_of_workers_launched ' || i);
1656 end if;
1657
1658 l_reqstatus_tbl_type(i) := l_purge_req_id;
1659 if (l_debug = 1) then
1660 trace(l_proc || ' l_reqstatus_tbl_type(' || i ||') => '|| l_reqstatus_tbl_type(i));
1661 end if;
1662 end loop;-- Marker End Loop for call to the Archiving Task Worker
1663 if (l_debug = 1) then
1664 trace(l_proc || ' Outside the For Loop...');
1665 trace(l_proc || ' l_loop_counter => ' || l_loop_counter);
1666 --trace(l_proc || ' l_number_workers => ' || l_number_workers);
1667 end if;
1668 end if;
1669 close c_get_total_eligible_recs;
1670 end if ;
1671
1672 if (l_debug = 1) then
1673 trace(l_proc || ' The following Worker Requests have been launched :');
1674 end if;
1675
1676 for i in 1..l_reqstatus_tbl_type.count
1677 loop
1678 if (l_debug = 1) then
1679 trace(l_proc || ' Worker Number => ' || i || '...Concurrent Request ID =>' || l_reqstatus_tbl_type(i));
1680 trace(l_proc || ' Please monitor for concurrent request failures....');
1681 end if;
1682 end loop;
1683
1684 x_retcode := 0;
1685 x_errbuf := 'Success';
1686 exception
1687 when fnd_api.g_exc_error then
1688 if (l_debug = 1) then
1689 trace(l_proc || ' fnd_api.g_exc_error :' || sqlcode);
1690 trace(l_proc || ' fnd_api.g_exc_error :' || substr(sqlerrm, 1, 100));
1691 end if;
1692
1693 if c_get_total_eligible_recs%ISOPEN then
1694 close c_get_total_eligible_recs;
1695 end if;
1696
1697 rollback to unarch_task_master_savepoint;
1698 x_retcode := 2;
1699 x_errbuf := 'Error';
1700 return;
1701 when submission_error_except then
1702 if (l_debug = 1) then
1703 trace(l_proc || ' submission_error_except :' || sqlcode);
1704 trace(l_proc || ' submission_error_except :' || substr(sqlerrm, 1, 100));
1705
1706 trace(l_proc || ' Number of workers launched before submission failure :' || i);
1707 trace(l_proc || ' Date Range for the last successful worker submission :');
1708 trace(l_proc || ' From Date = ' || l_from_date || ' .....To Date= ' || l_to_date);
1709
1710 trace(l_proc || ' The following Worker Requests have been launched before the last worker failed to Launch :');
1711 for i in 1..l_reqstatus_tbl_type.count
1712 loop
1713 trace(l_proc || ' Worker Number ' || i || '...Concurrent Request ID ' || l_reqstatus_tbl_type(i));
1714 end loop;
1715 end if;
1716
1717 if c_get_total_eligible_recs%ISOPEN then
1718 close c_get_total_eligible_recs;
1719 end if;
1720
1721 rollback to unarch_task_master_savepoint;
1722 x_retcode := 2;
1723 x_errbuf := 'Error';
1724 return;
1725 when others then
1726 if (l_debug = 1) then
1727 trace(l_proc || ' Others Error :' || sqlcode);
1728 trace(l_proc || ' Others Error :' || substr(sqlerrm, 1, 100));
1729
1730 trace(l_proc || ' Number of workers launched before submission failure :' || i);
1731 trace(l_proc || ' Date Range for the last successful worker submission :');
1732 trace(l_proc || ' From Date = ' || l_from_date || ' .....To Date= ' || l_to_date);
1733
1734 trace(l_proc || ' The following Worker Requests have been launched before the last worker failed to Launch :');
1735 for i in 1..l_reqstatus_tbl_type.count
1736 loop
1737 trace(l_proc || ' Worker Number ' || i || '...Concurrent Request ID ' || l_reqstatus_tbl_type(i));
1738 end loop;
1739 end if;
1740
1741 if c_get_total_eligible_recs%ISOPEN then
1742 close c_get_total_eligible_recs;
1743 end if;
1744
1745 rollback to unarch_task_master_savepoint;
1746
1747 x_retcode := 2;
1748 x_errbuf := 'Error';
1749 return;
1750 end unarchive_tasks;
1751 --
1752 --
1753 -- ---------------------------------------------------------------------------------------
1754 -- |-------------------------------< unarchive_tasks_worker >-----------------------------|
1755 -- ---------------------------------------------------------------------------------------
1756 -- {Start Of Comments}
1757 --
1758 -- Description:
1759 -- Archives tasks records based on organization.
1760 --
1761 -- Package-Procedure combination
1762 --
1763 -- Prerequisites:
1764 --
1765 --
1766 --
1767 -- In Parameters:
1768 -- Name Reqd Type Description
1769 -- x_errbuf Yes varchar2 Concurrent Manager Parameter.
1770 -- x_retcode Yes varchar2 Concurrent Manager Parameter. -- x_subinventory_code Yes varchar2 Call procedure to be registered
1771 -- p_from_date Yes number From Date for archive process.
1772 -- p_to_date Yes number To date for the archive process
1773 -- p_org_code Yes varchar2 Organization Code for which data needs
1774 -- to be purged.
1775 --
1776 --
1777 -- Post Success:
1778 -- Data in the Archive tables are deleted once the history tables are populated apropriately.
1779 --
1780 -- Post Failure:
1781 -- No data archiving takes place,
1782 --
1783 -- Access Status:
1784 -- Internal Development Use Only.
1785 --
1786 -- {End Of Comments}
1787 --
1788 Procedure unarchive_tasks_worker(
1789 x_errbuf out nocopy varchar2
1790 , x_retcode out nocopy number
1791 , p_from_date in varchar2
1792 , p_to_date in varchar2
1793 , p_org_id in number
1794 ) is
1795
1796 l_proc varchar2(72) := 'UNARCHIVE_WMS_TASKS_WORKER :';
1797 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1798
1799 l_organization_id number;
1800 l_number_of_records number;
1801
1802 l_min_date date;
1803 l_max_date date;
1804
1805 begin
1806 savepoint unarch_task_worker_savepoint;
1807 if (l_debug = 1) then
1808 trace(' Entering procedure '|| l_proc || ':'|| to_char(sysdate, 'RRRR/MM/DD HH24:MI:SS'), 1);
1809 trace(l_proc || ' p_from_date => ' || p_from_date);
1810 trace(l_proc || ' p_to_date => ' || p_to_date);
1811 trace(l_proc || ' p_org_id => ' || nvl(p_org_id, -99));
1812 end if;
1813
1814 l_min_date := to_date(p_from_date, 'RRRR/MM/DD HH24:MI:SS');
1815 l_max_date := to_date(p_to_date, 'RRRR/MM/DD HH24:MI:SS');
1816
1817 if (l_debug = 1) then
1818 trace(l_proc || ' l_min_date => ' || to_char(l_min_date, 'RRRR/MM/DD HH24:MI:SS'));
1819 trace(l_proc || ' l_max_date => ' || to_char(l_max_date, 'RRRR/MM/DD HH24:MI:SS'));
1820 end if;
1821
1822 if (l_max_date < l_min_date) then
1823 if (l_debug = 1) then
1824 trace(l_proc || 'To date cannot be less than From date', 9);
1825 end if;
1826 raise fnd_api.g_exc_error;
1827 end if;
1828
1829 -- @@@ Insert section.
1830 -- @@@ Insert records from the wms_dispatched_tasks_arch into wms_dispatched_tasks_history
1831 -- @@@ where parent_transaction_id is not null and task_type in (2,8)
1832 if (l_debug = 1) then
1833 trace(l_proc || ' Start of insert into wms_dispatched_tasks_history ...');
1834 end if;
1835
1836 insert into wms_dispatched_tasks_history(
1837 TASK_ID
1838 ,TRANSACTION_ID
1839 ,ORGANIZATION_ID
1840 ,USER_TASK_TYPE
1841 ,PERSON_ID
1842 ,EFFECTIVE_START_DATE
1843 ,EFFECTIVE_END_DATE
1844 ,EQUIPMENT_ID
1845 ,EQUIPMENT_INSTANCE
1846 ,PERSON_RESOURCE_ID
1847 ,MACHINE_RESOURCE_ID
1848 ,STATUS
1849 ,DISPATCHED_TIME
1850 ,LOADED_TIME
1851 ,DROP_OFF_TIME
1852 ,LAST_UPDATE_DATE
1853 ,LAST_UPDATED_BY
1854 ,CREATION_DATE
1855 ,CREATED_BY
1856 ,LAST_UPDATE_LOGIN
1857 ,ATTRIBUTE_CATEGORY
1858 ,ATTRIBUTE1
1859 ,ATTRIBUTE2
1860 ,ATTRIBUTE3
1861 ,ATTRIBUTE4
1862 ,ATTRIBUTE5
1863 ,ATTRIBUTE6
1864 ,ATTRIBUTE7
1865 ,ATTRIBUTE8
1866 ,ATTRIBUTE9
1867 ,ATTRIBUTE10
1868 ,ATTRIBUTE11
1869 ,ATTRIBUTE12
1870 ,ATTRIBUTE13
1871 ,ATTRIBUTE14
1872 ,ATTRIBUTE15
1873 ,TASK_TYPE
1874 ,PRIORITY
1875 ,TASK_GROUP_ID
1876 ,SUGGESTED_DEST_SUBINVENTORY
1877 ,SUGGESTED_DEST_LOCATOR_ID
1878 ,OPERATION_PLAN_ID
1879 ,MOVE_ORDER_LINE_ID
1880 ,TRANSFER_LPN_ID
1881 ,TRANSACTION_BATCH_ID
1882 ,TRANSACTION_BATCH_SEQ
1883 ,INVENTORY_ITEM_ID
1884 ,REVISION
1885 ,TRANSACTION_QUANTITY
1886 ,TRANSACTION_UOM_CODE
1887 ,SOURCE_SUBINVENTORY_CODE
1888 ,SOURCE_LOCATOR_ID
1889 ,DEST_SUBINVENTORY_CODE
1890 ,DEST_LOCATOR_ID
1891 ,LPN_ID
1892 ,CONTENT_LPN_ID
1893 ,IS_PARENT
1894 ,PARENT_TRANSACTION_ID
1895 ,TRANSFER_ORGANIZATION_ID
1896 ,SOURCE_DOCUMENT_ID
1897 ,OP_PLAN_INSTANCE_ID
1898 ,TASK_METHOD
1899 ,TRANSACTION_TYPE_ID
1900 ,TRANSACTION_SOURCE_TYPE_ID
1901 ,TRANSACTION_ACTION_ID)
1902 select
1903 wdta.TASK_ID
1904 ,wdta.TRANSACTION_ID
1905 ,wdta.ORGANIZATION_ID
1906 ,wdta.USER_TASK_TYPE
1907 ,wdta.PERSON_ID
1908 ,wdta.EFFECTIVE_START_DATE
1909 ,wdta.EFFECTIVE_END_DATE
1910 ,wdta.EQUIPMENT_ID
1911 ,wdta.EQUIPMENT_INSTANCE
1912 ,wdta.PERSON_RESOURCE_ID
1913 ,wdta.MACHINE_RESOURCE_ID
1914 ,wdta.STATUS
1915 ,wdta.DISPATCHED_TIME
1916 ,wdta.LOADED_TIME
1917 ,wdta.DROP_OFF_TIME
1918 ,wdta.LAST_UPDATE_DATE
1919 ,wdta.LAST_UPDATED_BY
1920 ,wdta.CREATION_DATE
1921 ,wdta.CREATED_BY
1922 ,wdta.LAST_UPDATE_LOGIN
1923 ,wdta.ATTRIBUTE_CATEGORY
1924 ,wdta.ATTRIBUTE1
1925 ,wdta.ATTRIBUTE2
1926 ,wdta.ATTRIBUTE3
1927 ,wdta.ATTRIBUTE4
1928 ,wdta.ATTRIBUTE5
1929 ,wdta.ATTRIBUTE6
1930 ,wdta.ATTRIBUTE7
1931 ,wdta.ATTRIBUTE8
1932 ,wdta.ATTRIBUTE9
1933 ,wdta.ATTRIBUTE10
1934 ,wdta.ATTRIBUTE11
1935 ,wdta.ATTRIBUTE12
1936 ,wdta.ATTRIBUTE13
1937 ,wdta.ATTRIBUTE14
1938 ,wdta.ATTRIBUTE15
1939 ,wdta.TASK_TYPE
1940 ,wdta.PRIORITY
1941 ,wdta.TASK_GROUP_ID
1942 ,wdta.SUGGESTED_DEST_SUBINVENTORY
1943 ,wdta.SUGGESTED_DEST_LOCATOR_ID
1944 ,wdta.OPERATION_PLAN_ID
1945 ,wdta.MOVE_ORDER_LINE_ID
1946 ,wdta.TRANSFER_LPN_ID
1947 ,wdta.TRANSACTION_BATCH_ID
1948 ,wdta.TRANSACTION_BATCH_SEQ
1949 ,wdta.INVENTORY_ITEM_ID
1950 ,wdta.REVISION
1951 ,wdta.TRANSACTION_QUANTITY
1952 ,wdta.TRANSACTION_UOM_CODE
1953 ,wdta.SOURCE_SUBINVENTORY_CODE
1954 ,wdta.SOURCE_LOCATOR_ID
1955 ,wdta.DEST_SUBINVENTORY_CODE
1956 ,wdta.DEST_LOCATOR_ID
1957 ,wdta.LPN_ID
1958 ,wdta.CONTENT_LPN_ID
1959 ,wdta.IS_PARENT
1960 ,wdta.PARENT_TRANSACTION_ID
1961 ,wdta.TRANSFER_ORGANIZATION_ID
1962 ,wdta.SOURCE_DOCUMENT_ID
1963 ,wdta.OP_PLAN_INSTANCE_ID
1964 ,wdta.TASK_METHOD
1965 ,wdta.TRANSACTION_TYPE_ID
1966 ,wdta.TRANSACTION_SOURCE_TYPE_ID
1967 ,wdta.TRANSACTION_ACTION_ID
1968 from wms_dispatched_tasks_arch wdta
1969 where wdta.last_update_date > l_min_date
1970 and wdta.last_update_date <= l_max_date
1971 and wdta.organization_id = nvl(p_org_id, wdta.organization_id);
1972
1973 l_number_of_records := SQL%ROWCOUNT;
1974 if (l_debug = 1) then
1975 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
1976 end if;
1977
1978 if (l_debug = 1) then
1979 trace(l_proc || ' End of insert into wms_dispatched_tasks_history ...');
1980 end if;
1981
1982 -- @@@ Insert records from the wms_op_plan_instance_hist into wms_op_plan_instances_archive
1983 if (l_debug = 1) then
1984 trace(l_proc || ' Start of insert into wms_op_plan_instance_hist ...');
1985 end if;
1986
1987 insert into wms_op_plan_instances_hist(
1988 OP_PLAN_INSTANCE_ID
1989 ,OPERATION_PLAN_ID
1990 ,STATUS
1991 ,ORGANIZATION_ID
1992 ,PLAN_EXECUTION_START_DATE
1993 ,PLAN_EXECUTION_END_DATE
1994 ,CREATED_BY
1995 ,CREATION_DATE
1996 ,LAST_UPDATED_BY
1997 ,LAST_UPDATE_DATE
1998 ,LAST_UPDATE_LOGIN
1999 ,ATTRIBUTE_CATEGORY
2000 ,ATTRIBUTE1
2001 ,ATTRIBUTE2
2002 ,ATTRIBUTE3
2003 ,ATTRIBUTE4
2004 ,ATTRIBUTE5
2005 ,ATTRIBUTE6
2006 ,ATTRIBUTE7
2007 ,ATTRIBUTE8
2008 ,ATTRIBUTE9
2009 ,ATTRIBUTE10
2010 ,ATTRIBUTE11
2011 ,ATTRIBUTE12
2012 ,ATTRIBUTE13
2013 ,ATTRIBUTE14
2014 ,ATTRIBUTE15
2015 ,ACTIVITY_TYPE_ID
2016 ,PLAN_TYPE_ID
2017 ,ORIG_SOURCE_SUB_CODE
2018 ,ORIG_SOURCE_LOC_ID
2019 ,ORIG_DEST_SUB_CODE
2020 ,ORIG_DEST_LOC_ID)
2021 select
2022 OP_PLAN_INSTANCE_ID
2023 ,OPERATION_PLAN_ID
2024 ,STATUS
2025 ,ORGANIZATION_ID
2026 ,PLAN_EXECUTION_START_DATE
2027 ,PLAN_EXECUTION_END_DATE
2028 ,CREATED_BY
2029 ,CREATION_DATE
2030 ,LAST_UPDATED_BY
2031 ,LAST_UPDATE_DATE
2032 ,LAST_UPDATE_LOGIN
2033 ,ATTRIBUTE_CATEGORY
2034 ,ATTRIBUTE1
2035 ,ATTRIBUTE2
2036 ,ATTRIBUTE3
2037 ,ATTRIBUTE4
2038 ,ATTRIBUTE5
2039 ,ATTRIBUTE6
2040 ,ATTRIBUTE7
2041 ,ATTRIBUTE8
2042 ,ATTRIBUTE9
2043 ,ATTRIBUTE10
2044 ,ATTRIBUTE11
2045 ,ATTRIBUTE12
2046 ,ATTRIBUTE13
2047 ,ATTRIBUTE14
2048 ,ATTRIBUTE15
2049 ,ACTIVITY_TYPE_ID
2050 ,PLAN_TYPE_ID
2051 ,ORIG_SOURCE_SUB_CODE
2052 ,ORIG_SOURCE_LOC_ID
2053 ,ORIG_DEST_SUB_CODE
2054 ,ORIG_DEST_LOC_ID
2055 from wms_op_plan_instances_arch wopia
2056 where wopia.last_update_date > l_min_date
2057 and wopia.last_update_date <= l_max_date
2058 and wopia.organization_id = nvl(p_org_id, wopia.organization_id);
2059
2060 l_number_of_records := SQL%ROWCOUNT;
2061 if (l_debug = 1) then
2062 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
2063 end if;
2064
2065 if (l_debug = 1) then
2066 trace(l_proc || ' End of insert into wms_op_plan_instance_hist ...');
2067 end if;
2068
2069 -- @@@ Insert records from the wms_op_opertn_instances_archive into wms_op_opertn_instance_hist
2070 if (l_debug = 1) then
2071 trace(l_proc || ' Start of insert into wms_op_opertn_instances_hist ...');
2072 end if;
2073
2074 insert into wms_op_opertn_instances_hist(
2075 OPERATION_INSTANCE_ID
2076 ,OP_PLAN_INSTANCE_ID
2077 ,ORGANIZATION_ID
2078 ,OPERATION_STATUS
2079 ,OPERATION_PLAN_DETAIL_ID
2080 ,OPERATION_SEQUENCE
2081 ,FROM_SUBINVENTORY_CODE
2082 ,FROM_LOCATOR_ID
2083 ,TO_SUBINVENTORY_CODE
2084 ,TO_LOCATOR_ID
2085 ,CREATED_BY
2086 ,CREATION_DATE
2087 ,LAST_UPDATED_BY
2088 ,LAST_UPDATE_DATE
2089 ,LAST_UPDATE_LOGIN
2090 ,ATTRIBUTE_CATEGORY
2091 ,ATTRIBUTE1
2092 ,ATTRIBUTE2
2093 ,ATTRIBUTE3
2094 ,ATTRIBUTE4
2095 ,ATTRIBUTE5
2096 ,ATTRIBUTE6
2097 ,ATTRIBUTE7
2098 ,ATTRIBUTE8
2099 ,ATTRIBUTE9
2100 ,ATTRIBUTE10
2101 ,ATTRIBUTE11
2102 ,ATTRIBUTE12
2103 ,ATTRIBUTE13
2104 ,ATTRIBUTE14
2105 ,ATTRIBUTE15
2106 ,OPERATION_TYPE_ID
2107 ,ACTIVITY_TYPE_ID
2108 ,SUG_TO_SUB_CODE
2109 ,SUG_TO_LOCATOR_ID
2110 ,SOURCE_TASK_ID
2111 ,EMPLOYEE_ID
2112 ,EQUIPMENT_ID
2113 ,ACTIVATE_TIME
2114 ,COMPLETE_TIME
2115 ,IS_IN_INVENTORY)
2116 select
2117 OPERATION_INSTANCE_ID
2118 --,OPERATION_TYPE
2119 ,OP_PLAN_INSTANCE_ID
2120 ,ORGANIZATION_ID
2121 ,OPERATION_STATUS
2122 ,OPERATION_PLAN_DETAIL_ID
2123 ,OPERATION_SEQUENCE
2124 --,LPN_ID
2125 --,FROM_ZONE_ID
2126 ,FROM_SUBINVENTORY_CODE
2127 ,FROM_LOCATOR_ID
2128 --,TO_ZONE_ID
2129 ,TO_SUBINVENTORY_CODE
2130 ,TO_LOCATOR_ID
2131 ,CREATED_BY
2132 ,CREATION_DATE
2133 ,LAST_UPDATED_BY
2134 ,LAST_UPDATE_DATE
2135 ,LAST_UPDATE_LOGIN
2136 ,ATTRIBUTE_CATEGORY
2137 ,ATTRIBUTE1
2138 ,ATTRIBUTE2
2139 ,ATTRIBUTE3
2140 ,ATTRIBUTE4
2141 ,ATTRIBUTE5
2142 ,ATTRIBUTE6
2143 ,ATTRIBUTE7
2144 ,ATTRIBUTE8
2145 ,ATTRIBUTE9
2146 ,ATTRIBUTE10
2147 ,ATTRIBUTE11
2148 ,ATTRIBUTE12
2149 ,ATTRIBUTE13
2150 ,ATTRIBUTE14
2151 ,ATTRIBUTE15
2152 ,OPERATION_TYPE_ID
2153 ,ACTIVITY_TYPE_ID
2154 ,SUG_TO_SUB_CODE
2155 ,SUG_TO_LOCATOR_ID
2156 ,SOURCE_TASK_ID
2157 ,EMPLOYEE_ID
2158 ,EQUIPMENT_ID
2159 ,ACTIVATE_TIME
2160 ,COMPLETE_TIME
2161 ,IS_IN_INVENTORY
2162 from wms_op_opertn_instances_arch wooia
2163 where wooia.last_update_date > l_min_date
2164 and wooia.last_update_date <= l_max_date
2165 and wooia.organization_id = nvl(p_org_id, wooia.organization_id);
2166
2167 l_number_of_records := SQL%ROWCOUNT;
2168 if (l_debug = 1) then
2169 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
2170 end if;
2171
2172 if (l_debug = 1) then
2173 trace(l_proc || ' End of insert into wms_op_opertn_instances_hist ...');
2174 end if;
2175
2176 -- @@@ Delete Section
2177 -- @@@ Delete records from wms_dispatched_tasks_arch table.
2178 if (l_debug = 1) then
2179 trace(l_proc || ' Start of delete from wms_dispatched_tasks_arch ...');
2180 end if;
2181
2182 delete from wms_dispatched_tasks_arch wdta
2183 where wdta.last_update_date > l_min_date
2184 and wdta.last_update_date <= l_max_date
2185 and wdta.organization_id = nvl(p_org_id, wdta.organization_id);
2186
2187 l_number_of_records := SQL%ROWCOUNT;
2188 if (l_debug = 1) then
2189 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
2190 end if;
2191
2192 if (l_debug = 1) then
2193 trace(l_proc || ' End of delete from wms_dispatched_tasks_arch ...');
2194 end if;
2195
2196 -- @@@ Delete records from the wms_op_plan_instances_arch table.
2197 if (l_debug = 1) then
2198 trace(l_proc || ' Start of delete from wms_op_plan_instances_arch ...');
2199 end if;
2200
2201 delete from wms_op_plan_instances_arch wopia
2202 where wopia.last_update_date > l_min_date
2203 and wopia.last_update_date <= l_max_date
2204 and wopia.organization_id = nvl(p_org_id, wopia.organization_id);
2205
2206 l_number_of_records := SQL%ROWCOUNT;
2207 if (l_debug = 1) then
2208 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
2209 end if;
2210
2211 if (l_debug = 1) then
2212 trace(l_proc || ' End of delete from wms_op_plan_instances_arch ...');
2213 end if;
2214
2215 -- @@@ Delete records from the wms_op_opertn_instances_hist table
2216 if (l_debug = 1) then
2217 trace(l_proc || ' Start of delete from wms_op_opertn_instances_arch ...');
2218 end if;
2219
2220 delete from wms_op_opertn_instances_arch wooia
2221 where wooia.last_update_date > l_min_date
2222 and wooia.last_update_date <= l_max_date
2223 and wooia.organization_id = nvl(p_org_id, wooia.organization_id);
2224
2225 l_number_of_records := SQL%ROWCOUNT;
2226 if (l_debug = 1) then
2227 trace(l_proc || ' l_number_of_records => '|| l_number_of_records);
2228 end if;
2229
2230 if (l_debug = 1) then
2231 trace(l_proc || ' End of delete from wms_op_opertn_instances_arch ...');
2232 end if;
2233
2234 x_retcode := 0;
2235 x_errbuf := 'Success';
2236 exception
2237 when fnd_api.g_exc_error then
2238 if (l_debug = 1) then
2239 trace(l_proc || ' fnd_api.g_exc_error :' || sqlcode);
2240 trace(l_proc || ' fnd_api.g_exc_error :' || substr(sqlerrm, 1, 100));
2241 end if;
2242
2243 rollback to unarch_task_worker_savepoint;
2244
2245 x_retcode := 2;
2246 x_errbuf := 'Error';
2247 when others then
2248 if (l_debug = 1) then
2249 trace(l_proc || ' Others error :' || sqlcode);
2250 trace(l_proc || ' Others error :' || substr(sqlerrm, 1, 100));
2251 end if;
2252
2253 rollback to unarch_task_worker_savepoint;
2254
2255 x_retcode := 2;
2256 x_errbuf := 'Error';
2257
2258 end unarchive_tasks_worker;
2259 --
2260 --
2261 end wms_archive_pvt;
[Home] [Help]