[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_WMS_WAA_PKG
Source
1 PACKAGE BODY opi_dbi_wms_waa_pkg AS
2 /* $Header: OPIDEWMSWAAB.pls 120.0 2005/05/24 18:17:07 appldev noship $ */
3 --
4 --Global Variables
5 --
6 g_gsd DATE;
7 g_last_run_date DATE;
8 g_sysdate DATE;
9 g_user_id NUMBER;
10 g_login_id NUMBER;
11 g_program_id NUMBER;
12 g_program_login_id NUMBER;
13 g_program_application_id NUMBER;
14 g_request_id NUMBER;
15 --
16 g_error NUMBER;
17 g_package VARCHAR2(100);
18 g_row_count NUMBER;
19 no_initial_data EXCEPTION;
20 --
21 --Local Procedures
22 --
23 /****************************** GET_WMS_GSD **********************************/
24 FUNCTION get_wms_gsd(errbuf IN OUT NOCOPY VARCHAR2
25 ,retcode IN OUT NOCOPY VARCHAR2)
26 RETURN DATE IS
27 --
28 CURSOR c_opi_gsd IS
29 SELECT MIN(creation_date)
30 FROM wms_dispatched_tasks_history
31 WHERE transaction_temp_id IS NOT NULL;
32 --
33 CURSOR c_wms_gsd IS
34 SELECT last_run_date
35 FROM opi_dbi_conc_prog_run_log
36 WHERE etl_type = 'WMS_WAA_GSD';
37 --
38 l_stmt_num NUMBER;
39 x_gsd DATE;
40 l_wms_gsd_available BOOLEAN;
41 --
42 l_procedure VARCHAR2(100);
43 --
44 no_data_available EXCEPTION;
45 BEGIN
46 --
47 --Initialize Local Variables
48 l_procedure := 'GET_WMS_GSD';
49 --
50 l_stmt_num := 10;
51 --
52 OPEN c_wms_gsd;
53 FETCH c_wms_gsd INTO x_gsd;
54 l_wms_gsd_available := c_wms_gsd%found;
55 CLOSE c_wms_gsd;
56 --
57 l_stmt_num := 20;
58 --
59 IF x_gsd IS NULL THEN
60 OPEN c_opi_gsd;
61 FETCH c_opi_gsd INTO x_gsd;
62 CLOSE c_opi_gsd;
63 l_stmt_num := 30;
64 --
65 IF x_gsd IS NULL THEN
66 RAISE no_data_available;
67 ELSE
68 IF l_wms_gsd_available THEN
69 l_stmt_num := 40;
70 UPDATE opi_dbi_conc_prog_run_log log
71 SET log.last_run_date = x_gsd
72 ,log.last_update_date = sysdate
73 ,log.last_updated_by = g_user_id
74 ,log.last_update_login = g_login_id
75 WHERE log.etl_type = 'WMS_WAA_GSD';
76 l_stmt_num := 50;
77 ELSE
78 INSERT INTO opi_dbi_conc_prog_run_log log
79 (log.etl_type
80 ,log.last_run_date
81 ,log.created_by
82 ,log.creation_date
83 ,log.last_update_date
84 ,log.last_updated_by
85 ,log.last_update_login
86 ,log.program_id
87 ,log.program_login_id
88 ,log.program_application_id
89 ,log.request_id)
90 VALUES ('WMS_WAA_GSD'
91 ,x_gsd
92 ,g_user_id
93 ,sysdate
94 ,sysdate
95 ,g_user_id
96 ,g_login_id
97 ,g_program_id
98 ,g_login_id
99 ,g_program_application_id
100 ,g_request_id);
101 l_stmt_num := 60;
102 END IF;
103 END IF;
104 END IF;
105 --
106 l_stmt_num := 70;
107 --
108 COMMIT;
109 --
110 l_stmt_num := 80;
111 RETURN x_gsd;
112 EXCEPTION
113 WHEN no_data_available THEN
114 retcode := 1;
115 errbuf := 'Warning in '||g_package||l_procedure||' at line#: '
116 ||l_stmt_num||' - '
117 ||'No data avialble for extraction';
118 --
119 bis_collection_utilities.put_line('Error Number: ' || retcode);
120 bis_collection_utilities.put_line('Error Message: '|| errbuf);
121 --
122 WHEN OTHERS THEN
123 retcode := SQLCODE;
124 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
125 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
126 --
127 bis_collection_utilities.put_line('Error Number: ' || retcode);
128 bis_collection_utilities.put_line('Error Message: '|| errbuf);
129 --
130 END get_wms_gsd;
131 --
132 /****************************** SET_LAST_RUN_DATE ****************************/
133 PROCEDURE set_last_run_date(errbuf IN OUT NOCOPY VARCHAR2
134 ,retcode IN OUT NOCOPY VARCHAR2) IS
135 --
136 l_procedure VARCHAR2(100);
137 l_stmt_num NUMBER;
138 --
139 BEGIN
140 --
141 --Initialize Local Variables
142 l_procedure := 'SET_LAST_RUN_DATE';
143 --
144 l_stmt_num := 10;
145 INSERT INTO opi_dbi_conc_prog_run_log
146 (etl_type
147 ,last_run_date
148 ,created_by
149 ,creation_date
150 ,last_update_date
151 ,last_updated_by
152 ,last_update_login
153 ,program_id
154 ,program_login_id
155 ,program_application_id
156 ,request_id)
157 VALUES ('WMS_WAA'
158 ,g_last_run_date
159 ,g_user_id
160 ,sysdate
161 ,sysdate
162 ,g_user_id
163 ,g_login_id
164 ,g_program_id
165 ,g_login_id
166 ,g_program_application_id
167 ,g_request_id);
168 --
169 bis_collection_utilities.put_line('Updated the information of '
170 ||'Last Collection Date');
171 --
172 l_stmt_num := 20;
173 EXCEPTION
174 WHEN OTHERS THEN
175 l_stmt_num := 30;
176 retcode := SQLCODE;
177 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
178 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
179 --
180 bis_collection_utilities.put_line('Failed to update collection date'
181 ||' in log table. Aborting');
182 bis_collection_utilities.put_line('Error Number: ' || retcode);
183 bis_collection_utilities.put_line('Error Message: '|| errbuf);
184 --
185 END set_last_run_date;
186 --
187 /****************************** RESET_LAST_RUN_DATE **************************/
188 PROCEDURE reset_last_run_date(errbuf IN OUT NOCOPY VARCHAR2
189 ,retcode IN OUT NOCOPY VARCHAR2) IS
190 --
191 l_procedure VARCHAR2(100);
192 l_stmt_num NUMBER;
193 --
194 BEGIN
195 --
196 --Initialize Local Variables
197 l_procedure := 'RESET_LAST_RUN_DATE';
198 --
199 l_stmt_num := 10;
200 --
201 UPDATE opi_dbi_conc_prog_run_log
202 SET last_run_date = g_last_run_date
203 ,last_update_date = SYSDATE
204 ,last_updated_by = g_user_id
205 ,last_update_login = g_login_id
206 ,program_id = g_program_id
207 ,program_login_id = g_login_id
208 ,program_application_id = g_program_application_id
209 ,request_id = g_request_id
210 WHERE etl_type = 'WMS_WAA';
211 --
212 l_stmt_num := 20;
213 EXCEPTION
214 WHEN OTHERS THEN
215 retcode := SQLCODE;
216 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
217 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
218 --
219 bis_collection_utilities.put_line('Failed to update collection date'
220 ||' in log table. Aborting');
221 bis_collection_utilities.put_line('Error Number: ' || retcode);
222 bis_collection_utilities.put_line('Error Message: '|| errbuf);
223 --
224 END reset_last_run_date;
225 --
226 /*************************** CLEANUP_STAGING_INDEX ***************************/
227 PROCEDURE cleanup_staging_index(errbuf IN OUT NOCOPY VARCHAR2
228 ,retcode IN OUT NOCOPY VARCHAR2) IS
229 --
230 l_procedure VARCHAR2(100);
231 --
232 l_schema VARCHAR2(30);
233 l_status VARCHAR2(30);
234 l_industry VARCHAR2(30);
235 l_stmt_num NUMBER;
236 --
237 BEGIN
238 --
239 --Initialize Local Variables
240 l_procedure := 'CLEANUP_STAGING_INDEX';
241 --
242 l_stmt_num := 10;
243 IF fnd_installation.get_app_info(application_short_name => 'OPI'
244 ,status => l_status
245 ,industry => l_industry
246 ,oracle_schema => l_schema) THEN
247 l_stmt_num := 20;
248 oki_dbi_scm_rsg_api_pvt.drop_index(p_table_name => 'opi_dbi_wms_tasks_stg'
249 ,p_owner => l_schema
250 ,p_retcode => retcode);
251 --
252 l_stmt_num := 30;
253 oki_dbi_scm_rsg_api_pvt.drop_index(p_table_name => 'opi_dbi_wms_op_stg'
254 ,p_owner => l_schema
255 ,p_retcode => retcode);
256 --
257 l_stmt_num := 40;
258 oki_dbi_scm_rsg_api_pvt.drop_index(p_table_name => 'opi_dbi_wms_ex_stg'
259 ,p_owner => l_schema
260 ,p_retcode => retcode);
261 --
262 bis_collection_utilities.put_line('Dropped the Indexes on Staging Tables');
263 l_stmt_num := 50;
264 END IF;
265 l_stmt_num := 60;
266 EXCEPTION
267 WHEN OTHERS THEN
268 retcode := SQLCODE;
269 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
270 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
271 --
272 bis_collection_utilities.put_line('Error Number: ' || retcode);
273 bis_collection_utilities.put_line('Error Message: '|| errbuf);
274 --
275 END cleanup_staging_index;
276 --
277 /*************************** RESET_STAGING_INDEX *****************************/
278 PROCEDURE reset_staging_index(errbuf IN OUT NOCOPY VARCHAR2
279 ,retcode IN OUT NOCOPY VARCHAR2) IS
280 --
281 l_procedure VARCHAR2(100);
282 --
283 l_schema VARCHAR2(30);
284 l_status VARCHAR2(30);
285 l_industry VARCHAR2(30);
286 l_stmt_num NUMBER;
287 --
288 BEGIN
289 --
290 --Initialize Local Variables
291 l_procedure := 'RESET_STAGING_INDEX';
292 --
293 l_stmt_num := 10;
294 --
295 IF fnd_installation.get_app_info(application_short_name => 'OPI'
296 ,status => l_status
297 ,industry => l_industry
298 ,oracle_schema => l_schema) THEN
299 l_stmt_num := 20;
300 oki_dbi_scm_rsg_api_pvt.create_index(p_table_name=>'opi_dbi_wms_tasks_stg'
301 ,p_owner =>l_schema
302 ,p_retcode =>retcode);
303 --
304 l_stmt_num := 30;
305 oki_dbi_scm_rsg_api_pvt.create_index(p_table_name=>'opi_dbi_wms_op_stg'
306 ,p_owner =>l_schema
307 ,p_retcode =>retcode);
308 --
309 l_stmt_num := 40;
310 oki_dbi_scm_rsg_api_pvt.create_index(p_table_name=>'opi_dbi_wms_ex_stg'
311 ,p_owner =>l_schema
312 ,p_retcode =>retcode);
313 --
314 l_stmt_num := 50;
315 --
316 bis_collection_utilities.put_line('Recreated the Indexes on the '
317 ||'Staging Tables');
318 END IF;
319 l_stmt_num := 60;
320 --
321 EXCEPTION
322 WHEN OTHERS THEN
323 retcode := SQLCODE;
324 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
325 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
326 --
327 bis_collection_utilities.put_line('Error Number: ' || retcode);
328 bis_collection_utilities.put_line('Error Message: '|| errbuf);
329 --
330 END reset_staging_index;
331 /*************************** CLEANUP_INITIAL_DATA ****************************/
332 PROCEDURE cleanup_initial_data(errbuf IN OUT NOCOPY VARCHAR2
333 ,retcode IN OUT NOCOPY VARCHAR2) IS
334 --
335 l_procedure VARCHAR2(100);
336 --
337 l_schema VARCHAR2(30);
338 l_status VARCHAR2(30);
339 l_industry VARCHAR2(30);
340 l_stmt_num NUMBER;
341 --
342 BEGIN
343 --
344 --Initialize Local Variables
345 l_procedure := 'CLEANUP_INITIAL_DATA';
346 --
347 l_stmt_num := 10;
348 --
349 IF fnd_installation.get_app_info(application_short_name => 'OPI'
350 ,status => l_status
351 ,industry => l_industry
352 ,oracle_schema => l_schema) THEN
353 l_stmt_num := 20;
354 --Delete the Last Run Date from Log Table
355 BEGIN
356 DELETE FROM opi_dbi_conc_prog_run_log
357 WHERE ETL_TYPE = 'WMS_WAA';
358 END;
359 --Truncate Staging Tables
360 l_stmt_num := 30;
361 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_op_stg';
362 l_stmt_num := 40;
363 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_tasks_stg';
364 l_stmt_num := 50;
365 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_ex_stg';
366 l_stmt_num := 60;
367 bis_collection_utilities.put_line('Truncated the Staging Tables');
368 --Truncate Fact Tables along with MV Logs
369 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema
370 ||'.opi_dbi_wms_op_f PURGE MATERIALIZED VIEW LOG';
371 l_stmt_num := 70;
372 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema
373 ||'.opi_dbi_wms_tasks_f PURGE MATERIALIZED VIEW LOG';
374 l_stmt_num := 80;
375 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema
376 ||'.opi_dbi_wms_ex_f PURGE MATERIALIZED VIEW LOG';
377 --
378 bis_collection_utilities.put_line('Truncated the Fact Tables');
379 l_stmt_num := 90;
380 END IF;
381 EXCEPTION
382 WHEN OTHERS THEN
383 retcode := SQLCODE;
384 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
385 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
386 --
387 bis_collection_utilities.put_line('Error Number: ' || retcode);
388 bis_collection_utilities.put_line('Error Message: '|| errbuf);
389 --
390 END cleanup_initial_data;
391 --
392 /*************************** CLEANUP_STAGING_DATA ****************************/
393 PROCEDURE cleanup_staging_data(errbuf IN OUT NOCOPY VARCHAR2
394 ,retcode IN OUT NOCOPY VARCHAR2) IS
395 --
396 l_procedure VARCHAR2(100);
397 --
398 l_schema VARCHAR2(30);
399 l_status VARCHAR2(30);
400 l_industry VARCHAR2(30);
401 l_stmt_num NUMBER;
402 --
403 BEGIN
404 --
405 --Initialize Local Variables
406 l_procedure := 'CLEANUP_STAGING_DATA';
407 --
408 l_stmt_num := 10;
409 IF fnd_installation.get_app_info(application_short_name => 'OPI'
410 ,status => l_status
411 ,industry => l_industry
412 ,oracle_schema => l_schema) THEN
413 l_stmt_num := 20;
414 --Truncate Staging Tables
415 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_tasks_stg';
416 l_stmt_num := 30;
417 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_op_stg';
418 l_stmt_num := 40;
419 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_schema||'.opi_dbi_wms_ex_stg';
420 l_stmt_num := 50;
421 END IF;
422 l_stmt_num := 60;
423 EXCEPTION
424 WHEN OTHERS THEN
425 retcode := SQLCODE;
426 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
427 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
428 --
429 bis_collection_utilities.put_line('Error Number: ' || retcode);
430 bis_collection_utilities.put_line('Error Message: '|| errbuf);
431 --
432 END cleanup_staging_data;
433 --
434 /*************************** WRAPUP_SUCCESS **********************************/
435 PROCEDURE wrapup_success(program_type IN VARCHAR2
436 ,errbuf IN OUT NOCOPY VARCHAR2
437 ,retcode IN OUT NOCOPY VARCHAR2) IS
438
439 --
440 l_procedure VARCHAR2(100);
441 l_message VARCHAR2(500);
442 l_stmt_num NUMBER;
443 BEGIN
444 --
445 --Initialize Local Variables
446 l_procedure := 'WRAPUP_SUCCESS';
447 --
448 l_stmt_num := 10;
449 --
450 COMMIT;
451 --
452 IF program_type = 'INIT' THEN
453 l_message := 'Successful in Initial Load';
454 ELSIF program_type = 'INCR' THEN
455 l_message := 'Successful in Incremental Load';
456 END IF;
457 --
458 bis_collection_utilities.wrapup(p_status => TRUE
459 ,p_count => g_row_count
460 ,p_message => l_message
461 );
462 l_stmt_num := 20;
463 --
464 COMMIT;
465 --
466 EXCEPTION
467 WHEN OTHERS THEN
468 retcode := SQLCODE;
469 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
470 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
471 --
472 bis_collection_utilities.put_line('Error Number: ' || retcode);
473 bis_collection_utilities.put_line('Error Message: '|| errbuf);
474 --
475 END wrapup_success;
476 --
477 /*************************** WRAPUP_FAILURE **********************************/
478 PROCEDURE wrapup_failure(program_type IN VARCHAR2) IS
479 --
480 l_procedure VARCHAR2(100);
481 l_message VARCHAR2(500);
482 l_stmt_num NUMBER;
483 BEGIN
484 --
485 --Initialize Local Variables
486 l_procedure := 'WRAPUP_FAILURE';
487 --
488 l_stmt_num := 10;
489 --
490 ROLLBACK;
491 --
492 IF program_type = 'INIT' THEN
493 l_message := 'Failed in Initial Load';
494 ELSIF program_type = 'INCR' THEN
495 l_message := 'Failed in Incremental Load';
496 END IF;
497 --
498 bis_collection_utilities.wrapup(p_status => FALSE
499 ,p_count => 0
500 ,p_message => l_message
501 );
502 l_stmt_num := 20;
503 END wrapup_failure;
504 --
505 /*************************** GATHER_STATS ************************************/
506 PROCEDURE gather_stats(p_table_name VARCHAR2) IS
507 --
508 l_procedure VARCHAR2(100);
509 l_table_owner VARCHAR2(32);
510 l_stmt_num NUMBER;
511 --
512 CURSOR c_table_owner IS
513 SELECT table_owner
514 FROM USER_SYNONYMS
515 WHERE synonym_name = p_table_name;
516 BEGIN
517 --
518 --Initialize Local Variables
519 l_procedure := 'GATHER_STATS';
520 --
521 l_stmt_num := 10;
522 --
523 -- Find owner of the table passed to procedure
524 --
525 OPEN c_table_owner;
526 FETCH c_table_owner INTO l_table_owner;
527 CLOSE c_table_owner;
528 l_stmt_num := 20;
529 --
530 -- Gather table statistics these stats will be used by CBO
531 -- for query optimization.
532 --
533 FND_STATS.GATHER_TABLE_STATS(ownname => l_table_owner
534 ,tabname => p_table_name
535 ,percent => 10
536 ,degree => 4
537 ,cascade => true);
538 --
539 l_stmt_num := 30;
540 END GATHER_STATS;
541 --
542 /*********************** STAGING_GATHER_STATS ********************************/
543 PROCEDURE staging_gather_stats(errbuf IN OUT NOCOPY VARCHAR2
544 ,retcode IN OUT NOCOPY VARCHAR2) IS
545 --
546 l_procedure VARCHAR2(100);
547 l_stmt_num NUMBER;
548 --
549 BEGIN
550 --
551 --Initialize Local Variables
552 l_procedure := 'STAGING_GATHER_STATS';
553 --
554 l_stmt_num := 10;
555 --
556 -- Gather Status for all the Staging Tables
557 --
558 gather_stats('OPI_DBI_WMS_TASKS_STG');
559 l_stmt_num := 20;
560 gather_stats('OPI_DBI_WMS_OP_STG');
561 l_stmt_num := 30;
562 gather_stats('OPI_DBI_WMS_EX_STG');
563 l_stmt_num := 40;
564 --
565 EXCEPTION
566 WHEN OTHERS THEN
567 retcode := SQLCODE;
568 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
569 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
570 --
571 bis_collection_utilities.put_line('Error Number: ' || retcode);
572 bis_collection_utilities.put_line('Error Message: '|| errbuf);
573 --
574 END staging_gather_stats;
575 --
576 /*********************** PRINT_GSD_MESSAGE ***********************************/
577 PROCEDURE print_gsd_message(p_gsd IN DATE) IS
578 BEGIN
579 BIS_COLLECTION_UTILITIES.put_line(
580 '*****************************************'
581 ||'*************************************');
582 BIS_COLLECTION_UTILITIES.put_line(
583 'The Picks '||fnd_global.local_chr(38)
584 ||' Exception Analysis reports as well as the '
585 ||'Operation Plan');
586 BIS_COLLECTION_UTILITIES.put_line(
587 'Performance reports are suppported only from the date the '
588 ||'WMS CU1 patch was');
589 BIS_COLLECTION_UTILITIES.put_line(
590 'applied. Hence, this extraction includes data from '
591 ||to_char(p_gsd,'MM/DD/YYYY HH24:MI:SS')||' to '
592 ||to_char(g_sysdate,'MM/DD/YYYY HH24:MI:SS')||'. ');
593 BIS_COLLECTION_UTILITIES.put_line(
594 'The following are the reports affected by '
595 ||'this restriction:');
596 BIS_COLLECTION_UTILITIES.put_line(' ');
597 BIS_COLLECTION_UTILITIES.put_line('Picks '||fnd_global.local_chr(38)
598 ||' Exceptions Analysis ');
599 BIS_COLLECTION_UTILITIES.put_line('Picks '||fnd_global.local_chr(38)
600 ||' Exceptions Trend ');
601 BIS_COLLECTION_UTILITIES.put_line('Pick Exceptions By Reason ');
602 BIS_COLLECTION_UTILITIES.put_line('Operation Plan Performance ');
603 BIS_COLLECTION_UTILITIES.put_line('Operation Plan Exceptions By Reason');
604 BIS_COLLECTION_UTILITIES.put_line(
605 '*****************************************'
606 ||'*************************************');
607 END print_gsd_message;
608 --
609 /*********************** CHECK_LAST_RUN_DATE *********************************/
610 PROCEDURE check_last_run_date(errbuf IN OUT NOCOPY VARCHAR2
611 ,retcode IN OUT NOCOPY VARCHAR2) IS
612 CURSOR c_last_run_date IS
613 SELECT last_run_date
614 FROM opi_dbi_conc_prog_run_log
615 WHERE etl_type = 'WMS_WAA';
616 --
617 l_stmt_num NUMBER;
618 l_last_run_date DATE;
619 l_procedure VARCHAR2(100);
620 last_run_date_not_available EXCEPTION;
621 BEGIN
622 --
623 --Initialize Local Variables
624 l_procedure := 'CHECK_LAST_RUN_DATE';
625 --
626 l_stmt_num := 10;
627 --
628 OPEN c_last_run_date;
629 FETCH c_last_run_date INTO l_last_run_date;
630 CLOSE c_last_run_date;
631 --
632 l_stmt_num := 20;
633 IF l_last_run_date IS NULL THEN
634 l_stmt_num := 30;
635 retcode := -1;
636 errbuf := ' Last Run Date is not available, Aborting. '
637 ||'Pls. run the Initial Load';
638 retcode := -1;
639 RAISE last_run_date_not_available;
640 END IF;
641 --
642 l_stmt_num := 40;
643 EXCEPTION
644 WHEN last_run_date_not_available THEN
645 retcode := -1;
646 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
647 ||l_stmt_num
648 ||errbuf;
649 bis_collection_utilities.put_line(errbuf);
650 RAISE no_initial_data;
651 WHEN OTHERS THEN
652 retcode := SQLCODE;
653 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
654 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
655 --
656 bis_collection_utilities.put_line('Error Number: ' || retcode);
657 bis_collection_utilities.put_line('Error Message: '|| errbuf);
658 --
659 END check_last_run_date;
660 --
661 /*************************** INIT_TASKS **************************************
662 | 07-APR-2005 MOHIT Updated the performance hints on OLTP tables |
663 ******************************************************************************/
664 PROCEDURE init_tasks(errbuf IN OUT NOCOPY VARCHAR2
665 ,retcode IN OUT NOCOPY VARCHAR2) IS
666 --
667 l_procedure VARCHAR2(100);
668 l_stmt_num NUMBER;
669 --
670 l_row_count NUMBER;
671 BEGIN
672 --
673 --Initialize Local Variables
674 l_procedure := 'INIT_TASKS';
675 --
676 l_stmt_num := 10;
677 --
678 --Collect all Pick Tasks into Tasks Staging Table
679 --
680 INSERT /*+ append parallel(tasks) */
681 INTO opi_dbi_wms_tasks_stg tasks
682 (tasks.task_id
683 ,tasks.organization_id
684 ,tasks.inventory_item_id
685 ,tasks.task_type
686 ,tasks.completion_date
687 ,tasks.op_plan_instance_id
688 ,tasks.is_parent
689 ,tasks.subinventory_code
690 ,tasks.transaction_temp_id
691 )
692 select /*+ ordered parallel (wdth) parallel (msi)
693 use_hash (sinv,wdth,msi) */
694 wdth.task_id task_id
695 ,wdth.organization_id organization_id
696 ,wdth.inventory_item_id inventory_item_id
697 ,wdth.task_type task_type
698 ,wdth.drop_off_time completion_date
699 ,wdth.op_plan_instance_id op_plan_instance_id
700 ,nvl(wdth.is_parent,'Y')
701 ,CASE WHEN wdth.task_type = 1 THEN wdth.source_subinventory_code
702 WHEN wdth.task_type in (2,8) THEN wdth.dest_subinventory_code
703 END subinventory_code
704 ,wdth.transaction_temp_id transaction_temp_id
705 from wms_dispatched_tasks_history wdth
706 ,mtl_system_items_b msi
707 ,mtl_secondary_inventories sinv
708 where wdth.inventory_item_id = msi.inventory_item_id
709 AND wdth.organization_id = msi.organization_id
710 AND decode(wdth.task_type
711 ,1,wdth.source_subinventory_code
712 ,wdth.dest_subinventory_code) = sinv.secondary_inventory_name
713 AND wdth.organization_id = sinv.organization_id
714 AND wdth.drop_off_time >= g_gsd
715 AND wdth.drop_off_time <= g_last_run_date
716 AND wdth.transaction_temp_id IS NOT NULL
717 AND wdth.task_type in (1,2,8)
718 AND wdth.status in (6,11);
719 --
720 l_stmt_num := 20;
721 l_row_count := sql%rowcount;
722 bis_collection_utilities.put_line('Finished collection of Tasks '
723 ||'into Tasks Staging Table : '
724 ||l_row_count||' row(s) processed');
725 --
726 l_stmt_num := 30;
727 COMMIT;
728 --
729 l_stmt_num := 40;
730 EXCEPTION
731 WHEN OTHERS THEN
732 retcode := SQLCODE;
733 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
734 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
735 --
736 bis_collection_utilities.put_line('Error Number: ' || retcode);
737 bis_collection_utilities.put_line('Error Message: '|| errbuf);
738 --
739 END init_tasks;
740 --
741 /*************************** INIT_OPS ****************************************/
742 PROCEDURE init_ops(errbuf IN OUT NOCOPY VARCHAR2
743 ,retcode IN OUT NOCOPY VARCHAR2) IS
744 --
745 l_procedure VARCHAR2(100);
746 l_stmt_num NUMBER;
747 --
748 l_row_count NUMBER;
749 BEGIN
750 --
751 --Initialize Local Variables
752 l_procedure := 'INIT_OPS';
753 --
754 l_stmt_num := 10;
755 --
756 --Collect all the Operation Plans into OP Staging Table
757 --
758 INSERT /*+ append parallel(ops) */
759 INTO opi_dbi_wms_op_stg ops
760 (ops.organization_id
761 ,ops.subinventory_code
762 ,ops.inventory_item_id
763 ,ops.operation_plan_id
764 ,ops.op_plan_instance_id
765 ,ops.status
766 ,ops.plan_execution_start_date
767 ,ops.plan_execution_end_date
768 ,ops.plan_elapsed_time
769 )
770 SELECT /*+ parallel (tasks) parallel (woiph)
771 parallel (wop) parallel (msi) parallel (sinv)
772 use_hash (tasks) use_hash (woiph)
773 use_hash (wop) use_hash (msi) use_hash (sinv) */
774 woiph.organization_id organization_id
775 ,tasks.subinventory_code subinventory_code
776 ,tasks.inventory_item_id inventory_item_id
777 ,wop.operation_plan_id operation_plan_id
778 ,woiph.op_plan_instance_id op_plan_instance_id
779 ,woiph.status status
780 ,woiph.plan_execution_start_date plan_execution_start_date
781 ,woiph.plan_execution_end_date plan_execution_end_date
782 ,( woiph.plan_execution_end_date
783 - woiph.plan_execution_start_date)*24 plan_elapsed_time
784 FROM opi_dbi_wms_tasks_stg tasks
785 ,wms_op_plan_instances_hist woiph
786 ,wms_op_plans_b wop
787 ,mtl_system_items_b msi
788 ,mtl_secondary_inventories sinv
789 WHERE tasks.op_plan_instance_id = woiph.op_plan_instance_id
790 AND woiph.operation_plan_id = wop.operation_plan_id
791 AND tasks.subinventory_code = sinv.secondary_inventory_name
792 AND tasks.organization_id = sinv.organization_id
793 AND tasks.inventory_item_id = msi.inventory_item_id
794 AND tasks.organization_id = msi.organization_id
795 AND woiph.status in (3,4,5)
796 AND wop.activity_type_id = 1
797 AND woiph.plan_execution_start_date >= g_gsd
798 AND woiph.plan_execution_end_date <= g_last_run_date
799 AND tasks.is_parent = 'Y'
800 AND tasks.task_type in (2,8);
801 --
802 l_stmt_num := 20;
803 l_row_count := sql%rowcount;
804 bis_collection_utilities.put_line('Finished collection of Operation Plans '
805 ||'into OP Staging Table : '
806 ||l_row_count||' row(s) processed');
807 --
808 l_stmt_num := 30;
809 COMMIT;
810 --
811 l_stmt_num := 40;
812 EXCEPTION
813 WHEN OTHERS THEN
814 retcode := SQLCODE;
815 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
816 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
817 --
818 bis_collection_utilities.put_line('Error Number: ' || retcode);
819 bis_collection_utilities.put_line('Error Message: '|| errbuf);
820 --
821 END init_ops;
822 --
823 /*************************** INIT_EXS ****************************************/
824 PROCEDURE init_exs(errbuf IN OUT NOCOPY VARCHAR2
825 ,retcode IN OUT NOCOPY VARCHAR2) IS
826 --
827 l_procedure VARCHAR2(100);
828 l_stmt_num NUMBER;
829 --
830 l_row_count NUMBER;
831 BEGIN
832 --
833 --Initialize Local Variables
834 l_procedure := 'INIT_EXS';
835 --
836 l_stmt_num := 10;
837 --
838 --Collect all Exceptions into Exceptions Staging Table
839 --
840 INSERT /*+ append (exs) */
841 INTO opi_dbi_wms_ex_stg exs
842 (exs.exception_id
843 ,exs.task_id
844 ,exs.organization_id
845 ,exs.inventory_item_id
846 ,exs.subinventory_code
847 ,exs.operation_plan_id
848 ,exs.operation_plan_indicator
849 ,exs.operation_plan_status
850 ,exs.op_plan_instance_id
851 ,exs.completion_date
852 ,exs.reason_id
853 )
854 SELECT /*+ parallel (tasks) parallel (wmx) parallel (mtr)
855 use_hash (tasks) use_hash (wmx) use_hash (mtr) */
856 wmx.sequence_number exception_id
857 ,wmx.task_id task_id
858 ,tasks.organization_id organization_id
859 ,tasks.inventory_item_id inventory_item_id
860 ,tasks.subinventory_code subinventory_code
861 ,NULL operation_plan_id
862 ,1 operation_plan_indicator
863 ,NULL operation_plan_status
864 ,NULL op_plan_instance_id
865 ,tasks.completion_date completion_date
866 ,wmx.reason_id reason_id
867 FROM opi_dbi_wms_tasks_stg tasks
868 ,wms_exceptions wmx
869 ,mtl_transaction_reasons mtr
870 WHERE wmx.task_id = tasks.transaction_temp_id
871 AND tasks.task_type = 1
872 AND tasks.is_parent = 'Y'
873 AND mtr.reason_id = wmx.reason_id
874 AND mtr.reason_type = 1
875 UNION ALL
876 SELECT /*+ parallel (ops) parallel (wmx) parallel (tasks) parallel (mtr)
877 use_hash (ops) use_hash (wmx) use_hash (tasks) use_hash (mtr) */
878 wmx.sequence_number exception_id
879 ,wmx.task_id task_id
880 ,ops.organization_id organization_id
881 ,ops.inventory_item_id inventory_item_id
882 ,ops.subinventory_code subinventory_code
883 ,ops.operation_plan_id operation_plan_id
884 ,2 operation_plan_indicator
885 ,ops.status operation_plan_status
886 ,ops.op_plan_instance_id op_plan_instance_id
887 ,ops.plan_execution_end_date completion_date
888 ,wmx.reason_id reason_id
889 FROM opi_dbi_wms_op_stg ops
890 ,wms_exceptions wmx
891 ,opi_dbi_wms_tasks_stg tasks
892 ,mtl_transaction_reasons mtr
893 WHERE tasks.op_plan_instance_id = ops.op_plan_instance_id
894 AND tasks.task_type in (2,8)
895 AND wmx.task_id = tasks.transaction_temp_id
896 AND tasks.organization_id = ops.organization_id
897 AND mtr.reason_id = wmx.reason_id;
898 --
899 l_stmt_num := 20;
900 l_row_count := sql%rowcount;
901 bis_collection_utilities.put_line('Finished collection of Exceptions '
902 ||'into Exceptions Staging Table : '
903 ||l_row_count||' row(s) processed');
904 --
905 l_stmt_num := 30;
906 COMMIT;
907 --
908 l_stmt_num := 40;
909 EXCEPTION
910 WHEN OTHERS THEN
911 retcode := SQLCODE;
912 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
913 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
914 --
915 bis_collection_utilities.put_line('Error Number: ' || retcode);
916 bis_collection_utilities.put_line('Error Message: '|| errbuf);
917 --
918 END init_exs;
919 --
920 /*************************** INIT_TASKF **************************************/
921 PROCEDURE init_taskf (errbuf IN OUT NOCOPY VARCHAR2
922 ,retcode IN OUT NOCOPY VARCHAR2) IS
923 --
924 l_procedure VARCHAR2(100);
925 l_stmt_num NUMBER;
926 l_row_count NUMBER;
927 BEGIN
928 --
929 --Initialize Local Variables
930 l_procedure := 'INIT_TASKF';
931 --
932 l_stmt_num := 10;
933 --
934 --Load all Pick Tasks collected in Staging Table into Tasks Fact
935 --
936 INSERT /*+ append parallel(taskf) */
937 INTO opi_dbi_wms_tasks_f taskf
938 (taskf.organization_id
939 ,taskf.subinventory_code
940 ,taskf.inventory_item_id
941 ,taskf.completion_date
942 ,taskf.picks
943 ,taskf.picks_with_exceptions
944 ,taskf.pick_exceptions
945 ,taskf.creation_date
946 ,taskf.last_update_date
947 ,taskf.created_by
948 ,taskf.last_updated_by
949 ,taskf.last_update_login
950 ,taskf.request_id
951 ,taskf.program_application_id
952 ,taskf.program_id
953 ,taskf.program_update_date
954 )
955 SELECT /*+ parallel (tasks) parallel (exs)
956 use_hash (tasks) use_hash (exs) */
957 tasks.organization_id organization_id
958 ,tasks.subinventory_code subinventory_code
959 ,tasks.inventory_item_id inventory_item_id
960 ,TRUNC(tasks.completion_date) completion_date
961 ,COUNT(tasks.task_id) picks
962 ,COUNT(exs.task_id) picks_with_exceptions
963 ,SUM(exs.ex_cnt) pick_exceptions
964 ,SYSDATE creation_date
965 ,SYSDATE last_update_date
966 ,g_user_id created_by
967 ,g_user_id last_updated_by
968 ,g_login_id last_update_login
969 ,g_request_id request_id
970 ,g_program_application_id program_application_id
971 ,g_program_id program_id
972 ,g_sysdate program_update_date
973 FROM opi_dbi_wms_tasks_stg tasks
974 ,(SELECT /*+ parallel (ex) use_hash (ex) */
975 ex.task_id
976 ,COUNT(ex.exception_id) ex_cnt
977 FROM opi_dbi_wms_ex_stg ex
978 WHERE ex.operation_plan_indicator = 1
979 GROUP BY task_id ) exs
980 WHERE tasks.transaction_temp_id = exs.task_id(+)
981 AND tasks.task_type = 1
982 GROUP BY tasks.organization_id
983 ,tasks.subinventory_code
984 ,tasks.inventory_item_id
985 ,TRUNC(tasks.completion_date);
986 --
987 l_stmt_num := 20;
988 l_row_count := sql%rowcount;
989 g_row_count := nvl(g_row_count,0) + l_row_count;
990 bis_collection_utilities.put_line('Finished Loading Pick Tasks '
991 ||'into Tasks Fact Table : '
992 ||l_row_count||' row(s) processed');
993 --
994 l_stmt_num := 40;
995 COMMIT;
996 --
997 l_stmt_num := 50;
998 EXCEPTION
999 WHEN OTHERS THEN
1000 retcode := SQLCODE;
1001 bis_collection_utilities.put_line('Tasks Fact Initial '
1002 ||'Load Failed.');
1003 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
1004 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
1005 --
1006 bis_collection_utilities.put_line('Error Number: ' || retcode);
1007 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1008 --
1009 END init_taskf;
1010 --
1011 /*************************** INIT_OPF ****************************************/
1012 PROCEDURE init_opf(errbuf IN OUT NOCOPY VARCHAR2
1013 ,retcode IN OUT NOCOPY VARCHAR2) IS
1014 --
1015 l_procedure VARCHAR2(100);
1016 l_stmt_num NUMBER;
1017 --
1018 l_row_count NUMBER;
1019 BEGIN
1020 --
1021 --Initiailize Local Variables
1022 l_procedure := 'INIT_OPF';
1023 l_stmt_num := 10;
1024 --
1025 --Load all Operation Plans collected in Staging Table into OP Fact
1026 --
1027 INSERT /*+ append parallel(opf) */
1028 INTO opi_dbi_wms_op_f opf
1029 (opf.organization_id
1030 ,opf.subinventory_code
1031 ,opf.inventory_item_id
1032 ,opf.operation_plan_id
1033 ,opf.status
1034 ,opf.plan_execution_end_date
1035 ,opf.plan_elapsed_time
1036 ,opf.executions
1037 ,opf.executions_with_exceptions
1038 ,opf.exceptions
1039 ,opf.creation_date
1040 ,opf.last_update_date
1041 ,opf.created_by
1042 ,opf.last_updated_by
1043 ,opf.last_update_login
1044 ,opf.request_id
1045 ,opf.program_application_id
1046 ,opf.program_id
1047 ,opf.program_update_date
1048 )
1049 SELECT /*+ parallel (ops) parallel (exs)
1050 use_hash (ops) use_hash (exs) */
1051 ops.organization_id organization_id
1052 ,ops.subinventory_code subinventory_code
1053 ,ops.inventory_item_id inventory_item_id
1054 ,ops.operation_plan_id operation_plan_id
1055 ,ops.status status
1056 ,trunc(ops.plan_execution_end_date) plan_execution_end_date
1057 ,sum(ops.plan_elapsed_time) plan_elapsed_time
1058 ,count(ops.op_plan_instance_id) executions
1059 ,count(exs.op_plan_instance_id) executions_with_exceptions
1060 ,sum(nvl(exs.ex_cnt,0)) exceptions
1061 ,SYSDATE creation_date
1062 ,SYSDATE last_update_date
1063 ,g_user_id created_by
1064 ,g_user_id last_updated_by
1065 ,g_login_id last_update_login
1066 ,g_request_id request_id
1067 ,g_program_application_id program_application_id
1068 ,g_program_id program_id
1069 ,g_sysdate program_update_date
1070 FROM opi_dbi_wms_op_stg ops
1071 ,(SELECT /*+ parallel (ex) use_hash (ex) */
1072 NVL(ex.op_plan_instance_id,0) op_plan_instance_id
1073 ,count(ex.exception_id) ex_cnt
1074 FROM opi_dbi_wms_ex_stg ex
1075 WHERE ex.operation_plan_indicator = 2
1076 GROUP BY nvl(ex.op_plan_instance_id,0)) exs
1077 WHERE ops.op_plan_instance_id = exs.op_plan_instance_id(+)
1078 GROUP BY ops.organization_id
1079 ,ops.subinventory_code
1080 ,ops.inventory_item_id
1081 ,ops.operation_plan_id
1082 ,ops.status
1083 ,TRUNC(ops.plan_execution_end_date);
1084 --
1085 l_stmt_num := 20;
1086 l_row_count := sql%rowcount;
1087 g_row_count := nvl(g_row_count,0) + l_row_count;
1088 bis_collection_utilities.put_line('Finished Loading Operation Plans '
1089 ||'into OP Fact Table : '
1090 ||l_row_count||' row(s) processed');
1091 --
1092 l_stmt_num := 30;
1093 COMMIT;
1094 --
1095 l_stmt_num := 40;
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 retcode := SQLCODE;
1099 bis_collection_utilities.put_line('Operation Plans Fact Initial '
1100 ||'Load Failed.');
1101 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
1102 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
1103 --
1104 bis_collection_utilities.put_line('Error Number: ' || retcode);
1105 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1106 --
1107 END init_opf;
1108 --
1109 /*************************** INIT_EXF ****************************************/
1110 PROCEDURE init_exf(errbuf IN OUT NOCOPY VARCHAR2
1111 ,retcode IN OUT NOCOPY VARCHAR2) IS
1112 --
1113 l_procedure VARCHAR2(100);
1114 l_stmt_num NUMBER;
1115 l_row_count NUMBER;
1116 BEGIN
1117 --
1118 --Initialize Local Variables
1119 l_procedure := 'INIT_EXF';
1120 l_stmt_num := 10;
1121 --
1122 --Load all Exceptions collected in Staging Table into Exceptions Fact
1123 --
1124 INSERT /*+ append (exf) */
1125 INTO opi_dbi_wms_ex_f exf
1126 (exf.organization_id
1127 ,exf.subinventory_code
1128 ,exf.inventory_item_id
1129 ,exf.operation_plan_id
1130 ,exf.operation_plan_indicator
1131 ,exf.operation_plan_status
1132 ,exf.reason_id
1133 ,exf.completion_date
1134 ,exf.exceptions
1135 ,exf.creation_date
1136 ,exf.last_update_date
1137 ,exf.created_by
1138 ,exf.last_updated_by
1139 ,exf.last_update_login
1140 ,exf.request_id
1141 ,exf.program_application_id
1142 ,exf.program_id
1143 ,exf.program_update_date
1144 )
1145 SELECT /*+ parallel (exs) use_hash (exs) */
1146 exs.organization_id organization_id
1147 ,exs.subinventory_code subinventory_code
1148 ,exs.inventory_item_id inventory_item_id
1149 ,exs.operation_plan_id operation_plan_id
1150 ,exs.operation_plan_indicator operation_plan_indicator
1151 ,exs.operation_plan_status operation_plan_status
1152 ,exs.reason_id reason_id
1153 ,trunc(exs.completion_date) completion_date
1154 ,COUNT(exs.exception_id) exceptions
1155 ,SYSDATE creation_date
1156 ,SYSDATE last_update_date
1157 ,g_user_id created_by
1158 ,g_user_id last_updated_by
1159 ,g_login_id last_update_login
1160 ,g_request_id request_id
1161 ,g_program_application_id program_application_id
1162 ,g_program_id program_id
1163 ,g_sysdate program_update_date
1164 FROM opi_dbi_wms_ex_stg exs
1165 GROUP BY exs.organization_id
1166 ,exs.subinventory_code
1167 ,exs.inventory_item_id
1168 ,exs.operation_plan_id
1169 ,exs.operation_plan_indicator
1170 ,exs.operation_plan_status
1171 ,exs.reason_id
1172 ,TRUNC(exs.completion_date) ;
1173 --
1174 l_stmt_num := 20;
1175 l_row_count := sql%rowcount;
1176 g_row_count := nvl(g_row_count,0) + l_row_count;
1177 bis_collection_utilities.put_line('Finished Loading Exceptions '
1178 ||'into Exceptions Fact Table : '
1179 ||l_row_count||' row(s) processed');
1180 --
1181 l_stmt_num := 30;
1182 COMMIT;
1183 --
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 retcode := SQLCODE;
1187 bis_collection_utilities.put_line('Exceptions Fact Initial '
1188 ||'Load Failed.');
1189 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
1190 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
1191 --
1192 bis_collection_utilities.put_line('Error Number: ' || retcode);
1193 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1194 --
1195 END init_exf;
1196 --
1197 /*************************** INCR_TASKS **************************************/
1198 PROCEDURE incr_tasks(errbuf IN OUT NOCOPY VARCHAR2
1199 ,retcode IN OUT NOCOPY VARCHAR2) IS
1200 --
1201 l_procedure VARCHAR2(100);
1202 l_stmt_num NUMBER;
1203 --
1204 l_row_count NUMBER;
1205 BEGIN
1206 --
1207 --Initialize Local Variables
1208 l_procedure := 'INCR_TASKS';
1209 --
1210 l_stmt_num := 10;
1211 --
1212 --Collect all Pick Tasks into Tasks Staging Table
1213 --
1214 INSERT INTO opi_dbi_wms_tasks_stg tasks
1215 (tasks.task_id
1216 ,tasks.organization_id
1217 ,tasks.inventory_item_id
1218 ,tasks.task_type
1219 ,tasks.completion_date
1220 ,tasks.op_plan_instance_id
1221 ,tasks.is_parent
1222 ,tasks.subinventory_code
1223 ,tasks.transaction_temp_id
1224 )
1225 select wdth.task_id task_id
1226 ,wdth.organization_id organization_id
1227 ,wdth.inventory_item_id inventory_item_id
1228 ,wdth.task_type task_type
1229 ,wdth.drop_off_time completion_date
1230 ,wdth.op_plan_instance_id op_plan_instance_id
1231 ,nvl(wdth.is_parent,'Y') is_parent
1232 ,wdth.source_subinventory_code subinventory_code
1233 ,wdth.transaction_temp_id transaction_temp_id
1234 from wms_dispatched_tasks_history wdth
1235 ,mtl_system_items_b msi
1236 ,mtl_secondary_inventories sinv
1237 ,opi_dbi_conc_prog_run_log log
1238 where wdth.inventory_item_id = msi.inventory_item_id
1239 AND wdth.organization_id = msi.organization_id
1240 AND decode(wdth.task_type
1241 ,1,source_subinventory_code
1242 ,dest_subinventory_code) = sinv.secondary_inventory_name
1243 AND wdth.organization_id = sinv.organization_id
1244 AND wdth.drop_off_time >= g_gsd
1245 AND wdth.drop_off_time <= g_last_run_date
1246 AND wdth.drop_off_time >= log.last_run_date
1247 AND log.etl_type = 'WMS_WAA'
1248 AND wdth.transaction_temp_id IS NOT NULL
1249 and nvl(wdth.is_parent,'Y') = 'Y'
1250 AND wdth.task_type = 1
1251 AND wdth.status in (6,11);
1252 --
1253 l_stmt_num := 20;
1254 l_row_count := sql%rowcount;
1255 bis_collection_utilities.put_line('Finished collection of Tasks '
1256 ||'into Tasks Staging Table : '
1257 ||l_row_count||' row(s) processed');
1258 --
1259 l_stmt_num := 30;
1260 --
1261 l_stmt_num := 40;
1262 EXCEPTION
1263 WHEN OTHERS THEN
1264 retcode := SQLCODE;
1265 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
1266 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
1267 --
1268 bis_collection_utilities.put_line('Error Number: ' || retcode);
1269 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1270 --
1271 END incr_tasks;
1272 --
1273 /*************************** INCR_OPS ****************************************/
1274 PROCEDURE incr_ops(errbuf IN OUT NOCOPY VARCHAR2
1275 ,retcode IN OUT NOCOPY VARCHAR2) IS
1276 --
1277 l_procedure VARCHAR2(100);
1278 l_stmt_num NUMBER;
1279 --
1280 l_row_count NUMBER;
1281 BEGIN
1282 --
1283 --Initialize Local Variables
1284 l_procedure := 'INCR_OPS';
1285 --
1286 l_stmt_num := 10;
1287 --
1288 --Collect all the Operation Plans into OP Staging Table
1289 --
1290 INSERT INTO opi_dbi_wms_op_stg ops
1291 (ops.organization_id
1292 ,ops.subinventory_code
1293 ,ops.inventory_item_id
1294 ,ops.operation_plan_id
1295 ,ops.op_plan_instance_id
1296 ,ops.status
1297 ,ops.plan_execution_start_date
1298 ,ops.plan_execution_end_date
1299 ,ops.plan_elapsed_time
1300 )
1301 SELECT wopih.organization_id organization_id
1302 ,wdth.dest_subinventory_code subinventory_code
1303 ,wdth.inventory_item_id inventory_item_id
1304 ,wop.operation_plan_id operation_plan_id
1305 ,wopih.op_plan_instance_id op_plan_instance_id
1306 ,wopih.status status
1307 ,wopih.plan_execution_start_date plan_execution_start_date
1308 ,wopih.plan_execution_end_date plan_execution_end_date
1309 ,( wopih.plan_execution_end_date
1310 - wopih.plan_execution_start_date)*24 plan_elapsed_time
1311 FROM wms_dispatched_tasks_history wdth
1312 ,opi_dbi_conc_prog_run_log log
1313 ,wms_op_plan_instances_hist wopih
1314 ,wms_op_plans_b wop
1315 ,mtl_system_items_b msi
1316 ,mtl_secondary_inventories sinv
1317 WHERE wdth.op_plan_instance_id = wopih.op_plan_instance_id
1318 AND wopih.operation_plan_id = wop.operation_plan_id
1319 AND wdth.dest_subinventory_code = sinv.secondary_inventory_name
1320 AND wdth.organization_id = sinv.organization_id
1321 AND wdth.inventory_item_id = msi.inventory_item_id
1322 AND wdth.organization_id = msi.organization_id
1323 AND wopih.status in (3,4,5)
1324 AND wop.activity_type_id = 1
1325 AND wopih.plan_execution_start_date >= g_gsd
1326 AND wopih.plan_execution_end_date <= g_last_run_date
1327 AND wopih.plan_execution_end_date >= log.last_run_date
1328 AND log.etl_type = 'WMS_WAA'
1329 AND nvl(wdth.is_parent,'Y') = 'Y'
1330 AND wdth.transaction_temp_id IS NOT NULL
1331 AND wdth.drop_off_time >= g_gsd
1332 AND wdth.task_type in (2,8);
1333 --
1334 l_stmt_num := 20;
1335 l_row_count := sql%rowcount;
1336 bis_collection_utilities.put_line('Finished collection of Operation Plans '
1337 ||'into OP Staging Table : '
1338 ||l_row_count||' row(s) processed');
1339 --
1340 l_stmt_num := 30;
1341 --
1342 l_stmt_num := 40;
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 retcode := SQLCODE;
1346 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
1347 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
1348 --
1349 bis_collection_utilities.put_line('Error Number: ' || retcode);
1350 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1351 --
1352 END incr_ops;
1353 --
1354 /*************************** INCR_EXS ****************************************/
1355 PROCEDURE incr_exs(errbuf IN OUT NOCOPY VARCHAR2
1356 ,retcode IN OUT NOCOPY VARCHAR2) IS
1357 --
1358 l_procedure VARCHAR2(100);
1359 l_stmt_num NUMBER;
1360 --
1361 l_row_count NUMBER;
1362 BEGIN
1363 --
1364 --Initialize Local Variables
1365 l_procedure := 'INCR_EXS';
1366 --
1367 l_stmt_num := 10;
1368 --
1369 --Collect all Exceptions into Exceptions Staging Table
1370 --
1371 INSERT INTO opi_dbi_wms_ex_stg exs
1372 (exs.exception_id
1373 ,exs.task_id
1374 ,exs.organization_id
1375 ,exs.inventory_item_id
1376 ,exs.subinventory_code
1377 ,exs.operation_plan_id
1378 ,exs.operation_plan_indicator
1379 ,exs.operation_plan_status
1380 ,exs.op_plan_instance_id
1381 ,exs.completion_date
1382 ,exs.reason_id
1383 )
1384 SELECT wmx.sequence_number exception_id
1385 ,wmx.task_id task_id
1386 ,tasks.organization_id organization_id
1387 ,tasks.inventory_item_id inventory_item_id
1388 ,tasks.subinventory_code subinventory_code
1389 ,NULL operation_plan_id
1390 ,1 operation_plan_indicator
1391 ,NULL operation_plan_status
1392 ,NULL op_plan_instance_id
1393 ,tasks.completion_date completion_date
1394 ,wmx.reason_id reason_id
1395 FROM opi_dbi_wms_tasks_stg tasks
1396 ,wms_exceptions wmx
1397 ,mtl_transaction_reasons mtr
1398 WHERE wmx.task_id = tasks.transaction_temp_id
1399 AND tasks.task_type = 1
1400 AND tasks.is_parent = 'Y'
1401 AND mtr.reason_id = wmx.reason_id
1402 AND mtr.reason_type = 1
1403 UNION ALL
1404 SELECT wmx.sequence_number exception_id
1405 ,wmx.task_id task_id
1406 ,ops.organization_id organization_id
1407 ,ops.inventory_item_id inventory_item_id
1408 ,ops.subinventory_code subinventory_code
1409 ,ops.operation_plan_id operation_plan_id
1410 ,2 operation_plan_indicator
1411 ,ops.status operation_plan_status
1412 ,ops.op_plan_instance_id op_plan_instance_id
1413 ,ops.plan_execution_end_date completion_date
1414 ,wmx.reason_id reason_id
1415 FROM opi_dbi_wms_op_stg ops
1416 ,wms_exceptions wmx
1417 ,wms_dispatched_tasks_history wdth
1418 ,mtl_transaction_reasons mtr
1419 WHERE wdth.op_plan_instance_id = ops.op_plan_instance_id
1420 AND wdth.task_type in (2,8)
1421 AND wdth.transaction_temp_id IS NOT NULL
1422 AND wmx.task_id = wdth.transaction_temp_id
1423 AND wdth.organization_id = ops.organization_id
1424 AND mtr.reason_id = wmx.reason_id;
1425 --
1426 l_stmt_num := 20;
1427 l_row_count := sql%rowcount;
1428 bis_collection_utilities.put_line('Finished collection of Exceptions '
1429 ||'into Exceptions Staging Table : '
1430 ||l_row_count||' row(s) processed');
1431 --
1432 l_stmt_num := 30;
1433 --
1434 l_stmt_num := 40;
1435 EXCEPTION
1436 WHEN OTHERS THEN
1437 retcode := SQLCODE;
1438 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
1439 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
1440 --
1441 bis_collection_utilities.put_line('Error Number: ' || retcode);
1442 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1443 --
1444 END incr_exs;
1445 --
1446 /*************************** INCR_TASKF **************************************/
1447 PROCEDURE incr_taskf (errbuf IN OUT NOCOPY VARCHAR2
1448 ,retcode IN OUT NOCOPY VARCHAR2) IS
1449 --
1450 l_procedure VARCHAR2(100);
1451 l_stmt_num NUMBER;
1452 l_row_count NUMBER;
1453 BEGIN
1454 --
1455 --Initialize Local Variables
1456 l_procedure := 'INCR_TASKF';
1457 l_stmt_num := 10;
1458 --
1459 --Load all Pick Tasks collected in Staging Table into Tasks Fact
1460 --
1461 MERGE INTO opi_dbi_wms_tasks_f taskf
1462 USING (
1463 SELECT tasks.organization_id organization_id
1464 ,tasks.subinventory_code subinventory_code
1465 ,tasks.inventory_item_id inventory_item_id
1466 ,TRUNC(tasks.completion_date) completion_date
1467 ,COUNT(tasks.task_id) picks
1468 ,COUNT(exs.task_id) picks_with_exceptions
1469 ,SUM(exs.ex_cnt) pick_exceptions
1470 FROM opi_dbi_wms_tasks_stg tasks
1471 ,(SELECT ex.task_id
1472 ,COUNT(ex.exception_id) ex_cnt
1473 FROM opi_dbi_wms_ex_stg ex
1474 WHERE ex.operation_plan_indicator = 1
1475 GROUP BY task_id ) exs
1476 WHERE tasks.transaction_temp_id = exs.task_id(+)
1477 AND tasks.task_type = 1
1478 GROUP BY tasks.organization_id
1479 ,tasks.subinventory_code
1480 ,tasks.inventory_item_id
1481 ,TRUNC(tasks.completion_date)
1482 ) s
1483 ON ( taskf.organization_id = s.organization_id
1484 AND taskf.subinventory_code = s.subinventory_code
1485 AND taskf.inventory_item_id = s.inventory_item_id
1486 AND taskf.completion_date = s.completion_date
1487 )
1488 WHEN MATCHED THEN
1489 UPDATE SET taskf.picks = taskf.picks + s.picks
1490 ,taskf.picks_with_exceptions
1491 = taskf.picks_with_exceptions
1492 + s.picks_with_exceptions
1493 ,taskf.pick_exceptions = taskf.pick_exceptions
1494 + s.pick_exceptions
1495 ,taskf.last_update_date = SYSDATE
1496 ,taskf.last_updated_by = g_user_id
1497 ,taskf.last_update_login = g_login_id
1498 WHEN NOT MATCHED THEN
1499 INSERT (taskf.organization_id
1500 ,taskf.subinventory_code
1501 ,taskf.inventory_item_id
1502 ,taskf.completion_date
1503 ,taskf.picks
1504 ,taskf.picks_with_exceptions
1505 ,taskf.pick_exceptions
1506 ,taskf.creation_date
1507 ,taskf.last_update_date
1508 ,taskf.created_by
1509 ,taskf.last_updated_by
1510 ,taskf.last_update_login
1511 ,taskf.request_id
1512 ,taskf.program_application_id
1513 ,taskf.program_id
1514 ,taskf.program_update_date
1515 )VALUES
1516 (s.organization_id
1517 ,s.subinventory_code
1518 ,s.inventory_item_id
1519 ,s.completion_date
1520 ,s.picks
1521 ,s.picks_with_exceptions
1522 ,s.pick_exceptions
1523 ,SYSDATE
1524 ,SYSDATE
1525 ,g_user_id
1526 ,g_user_id
1527 ,g_login_id
1528 ,g_request_id
1529 ,g_program_application_id
1530 ,g_program_id
1531 ,g_sysdate
1532 );
1533 --
1534 l_stmt_num := 20;
1535 l_row_count := sql%rowcount;
1536 g_row_count := nvl(g_row_count,0) + l_row_count;
1537 bis_collection_utilities.put_line('Finished Loading Pick Tasks '
1538 ||'into Tasks Fact Table : '
1539 ||l_row_count||' row(s) processed');
1540 --
1541 l_stmt_num := 40;
1542 --
1543 l_stmt_num := 50;
1544 EXCEPTION
1545 WHEN OTHERS THEN
1546 retcode := SQLCODE;
1547 bis_collection_utilities.put_line('Tasks Fact Incremental '
1548 ||'Load Failed.');
1549 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
1550 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
1551 --
1552 bis_collection_utilities.put_line('Error Number: ' || retcode);
1553 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1554 --
1555 END incr_taskf;
1556 --
1557 /*************************** INCR_OPF ****************************************/
1558 PROCEDURE incr_opf(errbuf IN OUT NOCOPY VARCHAR2
1559 ,retcode IN OUT NOCOPY VARCHAR2) IS
1560 --
1561 l_procedure VARCHAR2(100);
1562 l_stmt_num NUMBER;
1563 --
1564 l_row_count NUMBER;
1565 BEGIN
1566 --
1567 --Initialize Local Variables
1568 --
1569 l_procedure := 'INCR_OPF';
1570 l_stmt_num := 10;
1571 --
1572 --Load all Operation Plans collected in Staging Table into OP Fact
1573 --
1574 MERGE INTO opi_dbi_wms_op_f opf
1575 USING (
1576 SELECT ops.organization_id organization_id
1577 ,ops.subinventory_code subinventory_code
1578 ,ops.inventory_item_id inventory_item_id
1579 ,ops.operation_plan_id operation_plan_id
1580 ,ops.status status
1581 ,trunc(ops.plan_execution_end_date) plan_execution_end_date
1582 ,sum(ops.plan_elapsed_time) plan_elapsed_time
1583 ,nvl(count(ops.op_plan_instance_id),0) executions
1584 ,nvl(count(exs.op_plan_instance_id),0) executions_with_exceptions
1585 ,sum(nvl(exs.ex_cnt,0)) exceptions
1586 FROM opi_dbi_wms_op_stg ops
1587 ,(SELECT NVL(ex.op_plan_instance_id,0) op_plan_instance_id
1588 ,count(ex.exception_id) ex_cnt
1589 FROM opi_dbi_wms_ex_stg ex
1590 WHERE ex.operation_plan_indicator = 2
1591 GROUP BY nvl(ex.op_plan_instance_id,0)) exs
1592 WHERE ops.op_plan_instance_id = exs.op_plan_instance_id(+)
1593 GROUP BY ops.organization_id
1594 ,ops.subinventory_code
1595 ,ops.inventory_item_id
1596 ,ops.operation_plan_id
1597 ,ops.status
1598 ,TRUNC(ops.plan_execution_end_date)
1599 ) s
1600 ON ( opf.organization_id = s.organization_id
1601 AND opf.subinventory_code = s.subinventory_code
1602 AND opf.inventory_item_id = s.inventory_item_id
1603 AND opf.operation_plan_id = s.operation_plan_id
1604 AND opf.status = s.status
1605 AND opf.plan_execution_end_date = s.plan_execution_end_date
1606 )
1607 WHEN MATCHED THEN
1608 UPDATE SET opf.plan_elapsed_time = opf.plan_elapsed_time
1609 + s.plan_elapsed_time
1610 ,opf.executions = opf.executions
1611 + s.executions
1612 ,opf.executions_with_exceptions = opf.executions_with_exceptions
1613 + s.executions_with_exceptions
1614 ,opf.exceptions = opf.exceptions
1615 + s.exceptions
1616 ,opf.last_update_date = SYSDATE
1617 ,opf.last_updated_by = g_user_id
1618 ,opf.last_update_login = g_login_id
1619 WHEN NOT MATCHED THEN
1620 INSERT (opf.organization_id
1621 ,opf.subinventory_code
1622 ,opf.inventory_item_id
1623 ,opf.operation_plan_id
1624 ,opf.status
1625 ,opf.plan_execution_end_date
1626 ,opf.plan_elapsed_time
1627 ,opf.executions
1628 ,opf.executions_with_exceptions
1629 ,opf.exceptions
1630 ,opf.creation_date
1631 ,opf.last_update_date
1632 ,opf.created_by
1633 ,opf.last_updated_by
1634 ,opf.last_update_login
1635 ,opf.request_id
1636 ,opf.program_application_id
1637 ,opf.program_id
1638 ,opf.program_update_date
1639 )VALUES
1640 (s.organization_id
1641 ,s.subinventory_code
1642 ,s.inventory_item_id
1643 ,s.operation_plan_id
1644 ,s.status
1645 ,s.plan_execution_end_date
1646 ,s.plan_elapsed_time
1647 ,s.executions
1648 ,s.executions_with_exceptions
1649 ,s.exceptions
1650 ,SYSDATE
1651 ,SYSDATE
1652 ,g_user_id
1653 ,g_user_id
1654 ,g_login_id
1655 ,g_request_id
1656 ,g_program_application_id
1657 ,g_program_id
1658 ,g_sysdate
1659 );
1660 --
1661 l_stmt_num := 20;
1662 l_row_count := sql%rowcount;
1663 g_row_count := nvl(g_row_count,0) + l_row_count;
1664 bis_collection_utilities.put_line('Finished Loading Operation Plans '
1665 ||'into OP Fact Table : '
1666 ||l_row_count||' row(s) processed');
1667 --
1668 l_stmt_num := 30;
1669 COMMIT;
1670 --
1671 l_stmt_num := 40;
1672 EXCEPTION
1673 WHEN OTHERS THEN
1674 retcode := SQLCODE;
1675 bis_collection_utilities.put_line('Operation Plans Fact Incremental '
1676 ||'Load Failed.');
1677 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
1678 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
1679 --
1680 bis_collection_utilities.put_line('Error Number: ' || retcode);
1681 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1682 --
1683 END incr_opf;
1684 --
1685 /*************************** INCR_EXF ****************************************/
1686 PROCEDURE incr_exf(errbuf IN OUT NOCOPY VARCHAR2
1687 ,retcode IN OUT NOCOPY VARCHAR2) IS
1688 --
1689 l_procedure VARCHAR2(100);
1690 l_stmt_num NUMBER;
1691 l_row_count NUMBER;
1692 BEGIN
1693 --
1694 --Initialize Local Variables
1695 l_procedure := 'INCR_EXF';
1696 l_stmt_num := 10;
1697 --
1698 --Load all Exceptions collected in Staging Table into Exceptions Fact
1699 --
1700 MERGE INTO opi_dbi_wms_ex_f exf
1701 USING (
1702 SELECT exs.organization_id organization_id
1703 ,exs.subinventory_code subinventory_code
1704 ,exs.inventory_item_id inventory_item_id
1705 ,exs.operation_plan_id operation_plan_id
1706 ,exs.operation_plan_indicator operation_plan_indicator
1707 ,exs.operation_plan_status operation_plan_status
1708 ,exs.reason_id reason_id
1709 ,trunc(exs.completion_date) completion_date
1710 ,COUNT(exs.exception_id) exceptions
1711 FROM opi_dbi_wms_ex_stg exs
1712 GROUP BY exs.organization_id
1713 ,exs.subinventory_code
1714 ,exs.inventory_item_id
1715 ,exs.operation_plan_id
1716 ,exs.operation_plan_indicator
1717 ,exs.operation_plan_status
1718 ,exs.reason_id
1719 ,TRUNC(exs.completion_date)
1720 ) s
1721 ON ( exf.organization_id = s.organization_id
1722 AND exf.subinventory_code = s.subinventory_code
1723 AND exf.inventory_item_id = s.inventory_item_id
1724 AND exf.operation_plan_id = s.operation_plan_id
1725 AND exf.operation_plan_indicator = s.operation_plan_indicator
1726 AND exf.operation_plan_status = s.operation_plan_status
1727 AND exf.reason_id = s.reason_id
1728 AND exf.completion_date = s.completion_date
1729 )
1730 WHEN MATCHED THEN UPDATE SET exf.exceptions = exf.exceptions + s.exceptions
1731 ,exf.last_update_date = SYSDATE
1732 ,exf.last_updated_by = g_user_id
1733 ,exf.last_update_login = g_login_id
1734 WHEN NOT MATCHED THEN
1735 INSERT (exf.organization_id
1736 ,exf.subinventory_code
1737 ,exf.inventory_item_id
1738 ,exf.operation_plan_id
1739 ,exf.operation_plan_indicator
1740 ,exf.operation_plan_status
1741 ,exf.reason_id
1742 ,exf.completion_date
1743 ,exf.exceptions
1744 ,exf.creation_date
1745 ,exf.last_update_date
1746 ,exf.created_by
1747 ,exf.last_updated_by
1748 ,exf.last_update_login
1749 ,exf.request_id
1750 ,exf.program_application_id
1751 ,exf.program_id
1752 ,exf.program_update_date
1753 )VALUES
1754 (s.organization_id
1755 ,s.subinventory_code
1756 ,s.inventory_item_id
1757 ,s.operation_plan_id
1758 ,s.operation_plan_indicator
1759 ,s.operation_plan_status
1760 ,s.reason_id
1761 ,s.completion_date
1762 ,s.exceptions
1763 ,SYSDATE
1764 ,SYSDATE
1765 ,g_user_id
1766 ,g_user_id
1767 ,g_login_id
1768 ,g_request_id
1769 ,g_program_application_id
1770 ,g_program_id
1771 ,g_sysdate
1772 );
1773 --
1774 l_stmt_num := 20;
1775 l_row_count := sql%rowcount;
1776 g_row_count := nvl(g_row_count,0) + l_row_count;
1777 bis_collection_utilities.put_line('Finished Loading Exceptions '
1778 ||'into Exceptions Fact Table : '
1779 ||l_row_count||' row(s) processed');
1780 --
1781 l_stmt_num := 30;
1782 COMMIT;
1783 --
1784 EXCEPTION
1785 WHEN OTHERS THEN
1786 retcode := SQLCODE;
1787 bis_collection_utilities.put_line('Exceptions Fact Incremental '
1788 ||'Load Failed.');
1789 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
1790 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
1791 --
1792 bis_collection_utilities.put_line('Error Number: ' || retcode);
1793 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1794 --
1795 END incr_exf;
1796 --
1797 --Public Procedures
1798 --
1799 /*****************************************************************************/
1800 /********************************* INITIAL_LOAD ******************************/
1801 /*****************************************************************************/
1802 PROCEDURE initial_load(errbuf IN OUT NOCOPY VARCHAR2
1803 ,retcode IN OUT NOCOPY VARCHAR2)
1804 AS
1805 --
1806 --Local Variables
1807 --
1808 l_wms_gsd DATE;
1809 l_bis_gsd DATE;
1810 l_list DBMS_SQL.VARCHAR2_TABLE;
1811 --
1812 l_procedure VARCHAR2(100);
1813 l_stmt_num NUMBER;
1814 program_in_progress EXCEPTION;
1815 gsd_not_available EXCEPTION;
1816 no_data_available EXCEPTION;
1817 PRAGMA EXCEPTION_INIT(no_data_available,-06503);
1818 BEGIN
1819 --
1820 --Initialize Global Variables
1821 g_package := 'OPI_DBI_WAA_PKG.';
1822 g_sysdate := SYSDATE;
1823 g_user_id := nvl(fnd_global.user_id, -1);
1824 g_login_id := nvl(fnd_global.login_id, -1);
1825 g_program_id := fnd_global.CONC_PROGRAM_ID;
1826 g_program_login_id := fnd_global.CONC_LOGIN_ID;
1827 g_program_application_id := fnd_global.PROG_APPL_ID;
1828 g_request_id := fnd_global.CONC_REQUEST_ID;
1829 g_error := -1;
1830 --
1831 --Initialize Local Variables
1832 l_procedure := 'INITIAL_LOAD';
1833 l_stmt_num := 10;
1834 --
1835 g_last_run_date := SYSDATE;
1836 --
1837 l_stmt_num := 20;
1838 --
1839 l_list(1) := 'BIS_GLOBAL_START_DATE';
1840 IF bis_common_parameters.check_global_parameters(l_list) THEN
1841 --
1842 l_stmt_num := 30;
1843 l_bis_gsd := BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE;
1844 --
1845 IF l_bis_gsd IS NULL THEN
1846 RAISE gsd_not_available;
1847 END IF;
1848 --
1849 l_stmt_num := 60;
1850 --
1851 BEGIN
1852 l_wms_gsd:= get_wms_gsd(errbuf,retcode);
1853 EXCEPTION
1854 WHEN no_data_available THEN
1855 retcode := 1;
1856 errbuf := 'Warning in '||g_package||l_procedure||' at line#: '
1857 ||l_stmt_num||' - '
1858 ||'No data avialble for extraction';
1859 --
1860 bis_collection_utilities.put_line('Error Number: ' || retcode);
1861 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1862 --
1863 END;
1864 --
1865 print_gsd_message(l_wms_gsd);
1866 --
1867 l_stmt_num := 70;
1868 --
1869 IF g_gsd IS NULL THEN
1870 g_gsd := greatest(l_wms_gsd,l_bis_gsd);
1871 l_stmt_num := 80;
1872 END IF;
1873 --
1874 l_stmt_num := 100;
1875 --
1876 bis_collection_utilities.put_line
1877 ('Initial Load starts at '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
1878 --
1879 l_stmt_num := 110;
1880 --
1881 IF BIS_COLLECTION_UTILITIES.SETUP('OPI_DBI_WMS_TASKS_F') = FALSE THEN
1882 --
1883 l_stmt_num := 120;
1884 RAISE_APPLICATION_ERROR(-20000, errbuf);
1885 END IF;
1886 --
1887 l_stmt_num := 130;
1888 --
1889 -- Alter the Session variables for good Performance
1890 execute immediate 'alter session set hash_area_size=100000000';
1891 execute immediate 'alter session set sort_area_size=100000000';
1892 --
1893 l_stmt_num := 140;
1894 --
1895 cleanup_initial_data(errbuf,retcode);
1896 l_stmt_num := 150;
1897 --
1898 cleanup_staging_index(errbuf,retcode);
1899 l_stmt_num := 160;
1900 -- Collect data into all Staging Tables
1901 init_tasks(errbuf,retcode);
1902 l_stmt_num := 170;
1903 --
1904 init_ops(errbuf,retcode);
1905 l_stmt_num := 180;
1906 --
1907 init_exs(errbuf,retcode);
1908 l_stmt_num := 190;
1909 --
1910 reset_staging_index(errbuf,retcode);
1911 l_stmt_num := 200;
1912 --
1913 staging_gather_stats(errbuf,retcode);
1914 l_stmt_num := 210;
1915 --
1916 init_taskf(errbuf,retcode);
1917 l_stmt_num := 220;
1918 --
1919 init_opf(errbuf,retcode);
1920 l_stmt_num := 230;
1921 --
1922 init_exf(errbuf,retcode);
1923 l_stmt_num := 240;
1924 --
1925 IF l_wms_gsd IS NOT NULL THEN
1926 set_last_run_date(errbuf,retcode);
1927 END IF;
1928 l_stmt_num := 250;
1929 --
1930 cleanup_staging_data(errbuf,retcode);
1931 l_stmt_num := 260;
1932 --
1933 wrapup_success('INIT',errbuf,retcode);
1934 l_stmt_num := 270;
1935 --
1936 ELSE
1937 l_stmt_num := 280;
1938 retcode := g_error;
1939 bis_collection_utilities.put_line('Global Parameters are not setup.');
1940 bis_collection_utilities.put_line('Please check that the profile option '
1941 ||'BIS_GLOBAL_START_DATE is setup.');
1942 wrapup_failure('INIT');
1943 l_stmt_num := 290;
1944 END IF;
1945 EXCEPTION
1946 WHEN GSD_NOT_AVAILABLE THEN
1947 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
1948 ||l_stmt_num;
1949 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1950 bis_collection_utilities.put_line('Global start date'
1951 ||' is not available.'
1952 ||' Aborting.');
1953 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
1954 retcode := SQLCODE;
1955 errbuf := SQLERRM;
1956 WHEN OTHERS THEN
1957 retcode := SQLCODE;
1958 bis_collection_utilities.put_line('Initial Load Failed.');
1959 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
1960 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
1961 --
1962 bis_collection_utilities.put_line('Error Number: ' || retcode);
1963 bis_collection_utilities.put_line('Error Message: '|| errbuf);
1964 --
1965 wrapup_failure('INIT');
1966 --
1967 RAISE_APPLICATION_ERROR(-20000,errbuf);
1968 --
1969 END initial_load;
1970 --
1971 /*****************************************************************************/
1972 /******************************** INCREMENTAL_LOAD ***************************/
1973 /*****************************************************************************/
1974 PROCEDURE incremental_load(errbuf IN OUT NOCOPY VARCHAR2
1975 ,retcode IN OUT NOCOPY VARCHAR2) IS
1976 l_wms_gsd DATE;
1977 l_bis_gsd DATE;
1978 l_list DBMS_SQL.VARCHAR2_TABLE;
1979 --
1980 l_procedure VARCHAR2(100);
1981 l_stmt_num NUMBER;
1982 program_in_progress EXCEPTION;
1983 gsd_not_available EXCEPTION;
1984 no_data_available EXCEPTION;
1985 PRAGMA EXCEPTION_INIT(no_data_available,-06503);
1986 BEGIN
1987 --
1988 --Initialize Global Variables
1989 g_package := 'OPI_DBI_WAA_PKG.';
1990 g_sysdate := SYSDATE;
1991 g_user_id := nvl(fnd_global.user_id, -1);
1992 g_login_id := nvl(fnd_global.login_id, -1);
1993 g_program_id := fnd_global.CONC_PROGRAM_ID;
1994 g_program_login_id := fnd_global.CONC_LOGIN_ID;
1995 g_program_application_id := fnd_global.PROG_APPL_ID;
1996 g_request_id := fnd_global.CONC_REQUEST_ID;
1997 g_error := -1;
1998 g_last_run_date := SYSDATE;
1999 --
2000 --Initialize Local Variables
2001 l_procedure := 'INCREMENTAL_LOAD';
2002 --
2003 l_list(1) := 'BIS_GLOBAL_START_DATE';
2004 IF bis_common_parameters.check_global_parameters(l_list) THEN
2005 --
2006 l_stmt_num := 30;
2007 l_bis_gsd := BIS_COMMON_PARAMETERS.GET_GLOBAL_START_DATE;
2008 --
2009 IF l_bis_gsd IS NULL THEN
2010 RAISE gsd_not_available;
2011 END IF;
2012 --
2013 l_stmt_num := 60;
2014 --
2015 check_last_run_date(errbuf,retcode);
2016 --
2017 BEGIN
2018 l_wms_gsd:= get_wms_gsd(errbuf,retcode);
2019 EXCEPTION
2020 WHEN no_data_available THEN
2021 retcode := 1;
2022 errbuf := 'Warning in '||g_package||l_procedure||' at line#: '
2023 ||l_stmt_num||' - '
2024 ||'No data avialble for extraction';
2025 --
2026 bis_collection_utilities.put_line('Error Number: ' || retcode);
2027 bis_collection_utilities.put_line('Error Message: '|| errbuf);
2028 --
2029 END;
2030 --
2031 print_gsd_message(l_wms_gsd);
2032 --
2033 l_stmt_num := 70;
2034 --
2035 IF g_gsd IS NULL THEN
2036 g_gsd := greatest(l_wms_gsd,l_bis_gsd);
2037 l_stmt_num := 80;
2038 --
2039 END IF;
2040 --
2041 l_stmt_num := 100;
2042 --
2043 bis_collection_utilities.put_line
2044 ('Incremental Load starts at '||to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'));
2045 --
2046 l_stmt_num := 110;
2047 --
2048 IF BIS_COLLECTION_UTILITIES.SETUP('OPI_DBI_WMS_TASKS_F') = FALSE THEN
2049 --
2050 l_stmt_num := 120;
2051 RAISE_APPLICATION_ERROR(-20000, errbuf);
2052 END IF;
2053 --
2054 l_stmt_num := 130;
2055 --
2056 -- Alter the Session variables for good Performance
2057 execute immediate 'alter session set hash_area_size=100000000';
2058 execute immediate 'alter session set sort_area_size=100000000';
2059 --
2060 l_stmt_num := 140;
2061 --
2062 cleanup_staging_data(errbuf,retcode);
2063 --
2064 incr_tasks(errbuf,retcode);
2065 l_stmt_num := 170;
2066 --
2067 incr_ops(errbuf,retcode);
2068 l_stmt_num := 180;
2069 --
2070 incr_exs(errbuf,retcode);
2071 l_stmt_num := 190;
2072 --
2073 staging_gather_stats(errbuf,retcode);
2074 l_stmt_num := 210;
2075 --
2076 incr_taskf(errbuf,retcode);
2077 l_stmt_num := 220;
2078 --
2079 incr_opf(errbuf,retcode);
2080 l_stmt_num := 230;
2081 --
2082 incr_exf(errbuf,retcode);
2083 l_stmt_num := 240;
2084 --
2085 reset_last_run_date(errbuf,retcode);
2086 l_stmt_num := 250;
2087 --
2088 wrapup_success('INCR',errbuf,retcode);
2089 l_stmt_num := 270;
2090 ELSE
2091 l_stmt_num := 280;
2092 retcode := g_error;
2093 bis_collection_utilities.put_line('Global Parameters are not setup.');
2094 bis_collection_utilities.put_line('Please check that the profile option '
2095 ||'BIS_GLOBAL_START_DATE is setup.');
2096 wrapup_failure('INCR');
2097 l_stmt_num := 290;
2098 END IF;
2099 --
2100 EXCEPTION
2101 WHEN GSD_NOT_AVAILABLE THEN
2102 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
2103 ||l_stmt_num;
2104 bis_collection_utilities.put_line('Error Message: '|| errbuf);
2105 bis_collection_utilities.put_line('Global start date'
2106 ||' is not available.'
2107 ||' Aborting.');
2108 BIS_COLLECTION_UTILITIES.put_line(SQLERRM);
2109 retcode := SQLCODE;
2110 errbuf := SQLERRM;
2111 WHEN NO_INITIAL_DATA THEN
2112 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
2113 ||l_stmt_num;
2114 bis_collection_utilities.put_line('Error Message: '|| errbuf);
2115 bis_collection_utilities.put_line(' Initial Load data is not available,'
2116 ||' Please run the Initial Load');
2117 retcode := -1;
2118 WHEN OTHERS THEN
2119 retcode := SQLCODE;
2120 bis_collection_utilities.put_line('Incremental Load Failed.');
2121 errbuf := 'ERROR in '||g_package||l_procedure||' at line#: '
2122 ||l_stmt_num||' - '||substr(SQLERRM, 1,200);
2123 --
2124 bis_collection_utilities.put_line('Error Number: ' || retcode);
2125 bis_collection_utilities.put_line('Error Message: '|| errbuf);
2126 --
2127 wrapup_failure('INCR');
2128 --
2129 RAISE_APPLICATION_ERROR(-20000,errbuf);
2130 --
2131 END incremental_load;
2132 --
2133 END opi_dbi_wms_waa_pkg;