1 PACKAGE BODY opi_dbi_rep_uom_std_conv_pkg AS
2 /*$Header: OPIDEREPUMB.pls 120.0 2005/05/27 18:38:06 appldev noship $ */
3
4 /**************************************************
5 * File scope variables
6 **************************************************/
7
8 -- Package level variables for session info-
9 -- including schema name for truncating and
10 -- collecting stats. Initialized in check_global_setup.
11 s_opi_schema VARCHAR2(30);
12 s_status VARCHAR2(30);
13 s_industry VARCHAR2(30);
14
15 -- DBI Global start date
16 s_global_start_date DATE;
17
18 -- Package level variables for the standard who columns
19 s_user_id NUMBER;
20 s_login_id NUMBER;
21 s_program_id NUMBER;
22 s_program_login_id NUMBER;
23 s_program_application_id NUMBER;
24 s_request_id NUMBER;
25
26
27 /**************************************************
28 * Common Procedures (to initial and incremental load)
29 *
30 * File scope functions (not in spec)
31 **************************************************/
32
33 -- Global variable setup and verification
34 PROCEDURE global_setup;
35
36 -- Print out error message in a consistent manner
37 FUNCTION err_mesg (p_mesg IN VARCHAR2,
38 p_proc_name IN VARCHAR2,
39 p_stmt_id IN NUMBER)
40 RETURN VARCHAR2;
41
42 -- Print stage done message
43 PROCEDURE print_stage_done_mesg (p_proc_name IN VARCHAR2,
44 p_stmt_id IN NUMBER);
45
46 -- Generate the standard intra-class conversion rates
47 PROCEDURE compute_std_intra_class_conv;
48
49 -- Generate the standard inter-class conversion rates
50 PROCEDURE compute_std_inter_class_conv;
51
52 -- Update the log table.
53 PROCEDURE update_log (p_run_date IN DATE);
54
55 /**************************************************
56 * Initial Load Procedures
57 *
58 * File scope functions (not in spec)
59 **************************************************/
60 -- Clean/Setup up tables for the initial load
61 PROCEDURE setup_tables_init;
62
63 -- Insert the new standard rates (i.e. new combinations not in fact table)
64 -- into the fact table.
65 PROCEDURE insert_std_rates_init (p_run_date IN DATE);
66
67 /**************************************************
68 * Incremental Load Procedures
69 *
70 * File scope functions (not in spec)
71 **************************************************/
72 -- Clean/Verify tables for the initial load
73 PROCEDURE setup_tables_incr;
74
75 -- Merge the rates found during the incremental load
76 -- into the fact table if the rate has changed.
77 PROCEDURE merge_std_rates_incr (p_run_date IN DATE);
78
79
80 /**************************************************
81 * Common Procedures Definitions
82 **************************************************/
83
84 /* global_setup
85
86 Performs global setup of file scope variables and does any checking
87 needed for global DBI setups.
88
89 Parameters: None
90
91 History:
92 Date Author Action
93 12/01/04 Dinkar Gupta Defined function.
94
95 */
96 PROCEDURE global_setup
97 IS
98 l_proc_name CONSTANT VARCHAR2 (40) := 'global_setup';
99 l_stmt_id NUMBER;
100
101
102 BEGIN
103
104 -- Initialization block
105 l_stmt_id := 0;
106
107 l_stmt_id := 10;
108 -- Obtain the OPI schema name to allow truncation of various tables
109 -- get session parameters
110 IF (NOT (fnd_installation.get_app_info('OPI', s_status, s_industry,
111 s_opi_schema))) THEN
112 RAISE SCHEMA_INFO_NOT_FOUND;
113 END IF;
114
115 l_stmt_id := 20;
116 -- Get the global start date
117 s_global_start_date := trunc (bis_common_parameters.get_global_start_date);
118 IF (s_global_start_date IS NULL) THEN
119 RAISE GLOBAL_START_DATE_NULL;
120 END IF;
121
122 l_stmt_id := 30;
123 -- Package level variables for the user logged in
124 s_user_id := nvl(fnd_global.user_id, -1);
125 s_login_id := nvl(fnd_global.login_id, -1);
126 s_program_id := nvl (fnd_global.conc_program_id, -1);
127 s_program_login_id := nvl (fnd_global.conc_login_id, -1);
128 s_program_application_id := nvl (fnd_global.prog_appl_id, -1);
129 s_request_id := nvl (fnd_global.conc_request_id, -1);
130
131 l_stmt_id := 40;
132 return;
133
134 EXCEPTION
135
136 WHEN SCHEMA_INFO_NOT_FOUND THEN
137
138 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
139 (SCHEMA_INFO_NOT_FOUND_MESG,
140 l_proc_name, l_stmt_id));
141 RAISE GLOBAL_SETUP_MISSING;
142
143
144 WHEN GLOBAL_START_DATE_NULL THEN
145
146 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
147 (GLOBAL_START_DATE_NULL_MESG,
148 l_proc_name, l_stmt_id));
149 RAISE GLOBAL_SETUP_MISSING;
150
151
152
153 WHEN OTHERS THEN
154 rollback;
155
156 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
157 l_stmt_id));
158 RAISE GLOBAL_SETUP_MISSING;
159
160 END global_setup;
161
162
163
164 /* err_mesg
165
166 Return a C_ERRBUF_SIZE character long, properly formatted error
167 message with the package name, procedure name and message.
168
169 Parameters:
170 p_mesg - Actual message to be printed
171 p_proc_name - name of procedure that should be printed in the message
172 (optional)
173 p_stmt_id - step in procedure at which error occurred
174 (optional)
175
176 History:
177 Date Author Action
178 12/01/04 Dinkar Gupta Defined function.
179 */
180
181 FUNCTION err_mesg (p_mesg IN VARCHAR2,
182 p_proc_name IN VARCHAR2,
183 p_stmt_id IN NUMBER)
184 RETURN VARCHAR2
185 IS
186
187 l_proc_name CONSTANT VARCHAR2 (60) := 'err_mesg';
188 l_stmt_id NUMBER;
189
190 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
191 -- as the size of the declaration. I have to put 300 here.
192 l_formatted_message VARCHAR2 (300);
193
194 BEGIN
195
196 -- initialization block
197 l_stmt_id := 0;
198
199 -- initialization block
200 l_formatted_message := NULL;
201
202 l_stmt_id := 10;
203 l_formatted_message := substr ((C_PKG_NAME || '.' || p_proc_name || ' #' ||
204 to_char (p_stmt_id) || ': ' || p_mesg),
205 1, C_ERRBUF_SIZE);
206
207 return l_formatted_message;
208
209 EXCEPTION
210
211 WHEN OTHERS THEN
212 -- the exception happened in the exception reporting function !!
213 -- return with ERROR.
214 l_formatted_message := substr ((C_PKG_NAME || '.' || l_proc_name ||
215 ' #' ||
216 to_char (l_stmt_id) || ': ' ||
217 SQLERRM),
218 1, C_ERRBUF_SIZE);
219
220 l_formatted_message := 'Error in error reporting.';
221 return l_formatted_message;
222
223 END err_mesg;
224
225 /* print_stage_done_mesg
226
227 Print a message of 'Done' for whatever procedure/statement called.
228
229 Parameters:
230 p_proc_name - name of procedure that should be printed in the message
231 p_stmt_id - step in procedure at which error occurred
232
233 History:
234 Date Author Action
235 12/13/04 Dinkar Gupta Defined function.
236 */
237
238 PROCEDURE print_stage_done_mesg (p_proc_name IN VARCHAR2,
239 p_stmt_id IN NUMBER)
240 IS
241
242 l_proc_name CONSTANT VARCHAR2 (60) := 'print_stage_done_mesg';
243 l_stmt_id NUMBER;
244
245 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
246 -- as the size of the declaration. I have to put 300 here.
247 l_formatted_message VARCHAR2 (300);
248
249 BEGIN
250
251 -- initialization block
252 l_stmt_id := 0;
253
254 -- initialization block
255 l_formatted_message := NULL;
256
257 l_stmt_id := 10;
258 l_formatted_message := substr ((C_PKG_NAME || '.' || p_proc_name || ' #' ||
259 to_char (p_stmt_id) || ': ' || 'Done.'),
260 1, C_ERRBUF_SIZE);
261
262 BIS_COLLECTION_UTILITIES.PUT_LINE (l_formatted_message);
263
264 return;
265
266 EXCEPTION
267
268 WHEN OTHERS THEN
269 -- the exception happened in the print function
270 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
271 l_stmt_id));
272
273 RAISE; -- on to wrapper
274
275 END print_stage_done_mesg;
276
277
278 /* compute_std_intra_class_conv
279
280 Compute the standard intra-class conversion rates.
281
282 Rates will be computed for all UOMs belonging to the
283 class(es) of the reporting UOM(s) of the the measure(s) specified
284 in the p_measure_tbl input parameter.
285
286 No commits done here. Calling function coordinates commit.
287
288 History:
289 Date Author Action
290 12/06/04 Dinkar Gupta Wrote Function.
291
292 */
293 PROCEDURE compute_std_intra_class_conv
294 IS
295
296 l_proc_name CONSTANT VARCHAR2(40) := 'compute_std_intra_class_conv';
297 l_stmt_id NUMBER;
298
299 BEGIN
300
301 -- initialization block
302 l_stmt_id := 0;
303
304 l_stmt_id := 20;
305 -- Get all intra-class conversion rates.
306 -- It is easier to get all rates since there are not too many rates.
307 -- It is much trickier to figure out which rates have changed since the
308 -- last run (in case of the initial load, all rates are considered
309 -- "changed" since the last run). To figure out precisely which
310 -- rates have changed, we'd have to look at all records and determine:
311 -- 1. If the conversion of the reporting UOM to the base UOM has changed
312 -- since the last run, then all intra class standard rates will
313 -- be different.
314 -- 2. If the conversion of any other rate to the base UOM has changed,
315 -- then only that intra class standard rate has changed.
316 -- However, given that there are only a few reporting UOMs, and that
317 -- every class has about 8 to 10 UOMs, it is simpler to just
318 -- find all intra-class standard rates to the reporting UOMs.
319 --
320 -- In case there are multiple measures tied to the same reporting UOM,
321 -- we need to pick distinct reporting UOMs, irrespective of the measures.
322 INSERT /*+ append */
323 INTO opi_dbi_rep_uom_conv_stg (
324 inventory_item_id,
325 from_uom_code,
326 from_uom_class,
327 rep_uom_code,
328 rep_uom_class,
329 conversion_rate,
330 conversion_type
331 )
332 SELECT
333 C_STD_RATE_ITEM_ID,
334 convs.uom_code,
335 convs.uom_class,
336 msr_mst.rep_uom_code,
337 msr_mst.rep_uom_class,
338 opi_dbi_rep_uom_pkg.uom_convert
339 (C_STD_RATE_ITEM_ID, C_CONV_PRECISION, 1,
340 convs.uom_code, msr_mst.rep_uom_code),
341 C_INTRA_CONV_TYPE
342 FROM
343 (SELECT
344 DISTINCT
345 rep_uom_code,
346 rep_uom_class,
347 base_rep_uom_code
348 FROM opi_dbi_rep_uoms
349 WHERE measure_code in ('WT', 'VOL')
350 AND rep_uom_code IS NOT NULL) msr_mst,
351 mtl_units_of_measure convs
352 WHERE msr_mst.rep_uom_class = convs.uom_class;
353
354 return;
355
356 EXCEPTION
357
358 WHEN OTHERS THEN
359
360 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
361 l_stmt_id));
362 RAISE INTRA_STD_CONV_FAILED;
363
364
365 END compute_std_intra_class_conv;
366
367
368 /* compute_std_inter_class_conv
369
370 Compute the standard inter-class conversion rates.
371
372 Rates will be computed for all UOMs belonging to the
373 class(es) whose base UOM(s) have a defined conversion to the
374 base UOM(s) of the class(es) of the reporting UOM(s).
375
376 No commits done here. Calling function coordinates commit.
377
378 History:
379 Date Author Action
380 12/07/04 Dinkar Gupta Wrote Function.
381
382 */
383 PROCEDURE compute_std_inter_class_conv
384 IS
385
386 l_proc_name CONSTANT VARCHAR2(40) := 'compute_std_inter_class_conv';
387 l_stmt_id NUMBER;
388
389 BEGIN
390
391 -- initialization block
392 l_stmt_id := 0;
393
394 l_stmt_id := 20;
395 -- Get all inter-class conversion rates.
396 -- It is easier to get all rates since there are not too many rates.
397 -- It is much trickier to figure out which rates have changed since the
398 -- last run (in case of the initial load, all rates are considered
399 -- "changed" since the last run). To figure out precisely which
400 -- rates have changed, we'd have to look at all records and determine:
401 -- 1. If the conversion of the base UOM of a "from" class to the base UOM
402 -- of the reporting class has changed, then rates for all UOMs
403 -- from the "from" class will have to be recomputed.
404 -- 2. If the conversion of the reporting class base UOM to the
405 -- reporting UOM has changed, all rates will be different.
406 -- 3. If the conversion from any non-reporting class UOM to the base
407 -- UOM of that class has changed, then only that rate will be
408 -- different.
409 -- This complexity is a basic consequence of the 3 way conversion
410 -- that happens internally for inter-class conversions: from the
411 -- source UOM to the base UOM of that class, followed by the
412 -- conversion between the base UOM of the source and target classes,
413 -- followed by the conversion between the base UOM of the target class
414 -- and the target UOM.
415 --
416 -- However, given that there are only a few reporting UOMs, and that
417 -- every class has about 8 to 10 UOMs, it is simpler to just
418 -- find all inter-class standard rates to the reporting UOMs.
419 --
420 -- In case there are multiple measures tied to the same reporting UOM,
421 -- we need to pick distinct reporting UOMs, irrespective of the measures.
422 INSERT /*+ append */
423 INTO opi_dbi_rep_uom_conv_stg (
424 inventory_item_id,
425 from_uom_code,
426 from_uom_class,
427 rep_uom_code,
428 rep_uom_class,
429 conversion_rate,
430 conversion_type
431 )
432 SELECT
433 C_STD_RATE_ITEM_ID,
434 from_uoms.uom_code,
435 from_class.uom_class,
436 from_rep_bases.rep_uom_code,
437 from_rep_bases.rep_uom_class,
438 opi_dbi_rep_uom_pkg.uom_convert
439 (C_STD_RATE_ITEM_ID, C_CONV_PRECISION, 1,
440 from_uoms.uom_code, from_rep_bases.rep_uom_code),
441 C_INTER_CONV_TYPE
442 FROM
443 (SELECT
444 DISTINCT
445 msr_mst.rep_uom_class,
446 msr_mst.rep_uom_code,
447 msr_mst.base_rep_uom_code,
448 decode (msr_mst.base_rep_uom_code,
449 convs.to_base_uom_code, convs.from_base_uom_code,
450 convs.to_base_uom_code) base_from_uom_code
451 FROM
452 (SELECT
453 DISTINCT
454 rep_uom_code,
455 rep_uom_class,
456 base_rep_uom_code
457 FROM opi_dbi_rep_uoms
458 WHERE measure_code in ('WT', 'VOL')
459 AND rep_uom_code IS NOT NULL) msr_mst,
460 opi_dbi_uom_class_std_conv convs
461 WHERE ( convs.from_base_uom_code = msr_mst.base_rep_uom_code
462 OR convs.to_base_uom_code = msr_mst.base_rep_uom_code))
463 from_rep_bases,
464 mtl_units_of_measure_vl from_class,
465 mtl_units_of_measure_vl from_uoms
466 WHERE from_rep_bases.base_from_uom_code = from_class.uom_code
467 AND from_class.base_uom_flag = C_IS_BASE_UOM
468 AND from_class.uom_class = from_uoms.uom_class;
469
470 return;
471
472 EXCEPTION
473
474 WHEN OTHERS THEN
475
476 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
477 l_stmt_id));
478 RAISE INTER_STD_CONV_FAILED;
479
480
481 END compute_std_inter_class_conv;
482
483
484 /* update_log
485
486 Update the log table for the reporting UOM conversion rates ETL
487 with the run date provided as a parameter.
488
489 Parameters:
490 1. p_run_date - run date of the program, provided by the wrapper. This
491 is the start time of the program, which can be used
492 as a marker between incremental runs.
493
494 No commits done here. Calling function coordinates commit.
495
496 History:
497 Date Author Action
498 12/07/04 Dinkar Gupta Wrote Function.
499
500 */
501 PROCEDURE update_log (p_run_date IN DATE)
502 IS
503
504 l_proc_name CONSTANT VARCHAR2 (40) := 'update_log';
505 l_stmt_id NUMBER;
506
507 BEGIN
508
509 -- Initialization block
510 l_stmt_id := 10;
511
512 -- Update the row for this ETL. It is assumed that there is only
513 -- one such row.
514 UPDATE opi_dbi_conc_prog_run_log
515 SET last_run_date = p_run_date,
516 last_update_date = sysdate,
517 last_updated_by = s_user_id,
518 last_update_login = s_login_id,
519 request_id = s_request_id,
520 program_application_id = s_program_application_id,
521 program_id = s_program_id,
522 program_login_id = s_program_login_id
523 WHERE etl_type = C_ETL_TYPE;
524
525 return;
526
527 EXCEPTION
528
529 WHEN OTHERS THEN
530
531 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
532 l_stmt_id));
533 RAISE LOG_UPDATE_FAILED;
534
535 END update_log;
536
537
538
539 /**************************************************
540 * Initial Load Procedure Definitions
541 **************************************************/
542
543 /* setup_tables_init
544
545 Cleans up tables prior to the initial load run.
546
547 Truncates:
548 1. OPI_DBI_REP_UOM_STD_CONV_F
549 2. OPI_DBI_REP_UOM_CONV_STG
550
551 Deletes:
552 1. Rows from OPI_DBI_CONC_PROG_RUN_LOG where row type = REP_UOM.
553
554 Inserts:
555 1. Row into OPI_DBI_CONC_PROG_RUN_LOG where row type = REP_UOM with a
556 last run date that is very old (e.g. 1st Jan, 1950);
557
558 History:
559 Date Author Action
560 12/06/04 Dinkar Gupta Wrote Function.
561
562 */
563 PROCEDURE setup_tables_init
564 IS
565
566 l_proc_name CONSTANT VARCHAR2 (40) := 'setup_tables_init';
567 l_stmt_id NUMBER;
568
569 BEGIN
570
571 -- initialization block
572 l_stmt_id := 0;
573
574
575 l_stmt_id := 10;
576 -- Truncate the fact table
577 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
578 'OPI_DBI_REP_UOM_STD_CONV_F');
579
580 l_stmt_id := 20;
581 -- Truncate the staging table
582 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
583 'OPI_DBI_REP_UOM_CONV_STG');
584
585 l_stmt_id := 30;
586 -- Delete all rows for this ETL from the log table
587 DELETE
588 FROM opi_dbi_conc_prog_run_log
589 WHERE etl_type = C_ETL_TYPE;
590
591 l_stmt_id := 40;
592 commit;
593
594 l_stmt_id := 50;
595 -- insert a row into the run log with a very old run date.
596 INSERT INTO opi_dbi_conc_prog_run_log (
597 etl_type,
598 last_run_date,
599 created_by,
600 creation_date,
601 last_update_date,
602 last_updated_by,
603 last_update_login,
604 program_id,
605 program_login_id,
606 program_application_id,
607 request_id
608 )
609 VALUES (C_ETL_TYPE,
610 C_START_RUN_DATE,
611 s_user_id,
612 sysdate,
613 sysdate,
614 s_user_id,
615 s_login_id,
616 s_program_id,
617 s_program_login_id,
618 s_program_application_id,
619 s_request_id);
620
621 l_stmt_id := 50;
622 commit;
623
624 return;
625
626 EXCEPTION
627
628 WHEN OTHERS THEN
629 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
630 l_stmt_id));
631 RAISE TABLE_INIT_SETUP_FAILED;
632
633 END setup_tables_init;
634
635 /* insert_std_rates_init
636
637 Insert the new standard conversion rates found from the
638 staging table to the fact table.
639
640 No commits done here. Calling function coordinates commit.
641
642 Using an append hint on the insert since this function is designed
643 to be used after initial loads. Expect to commit after this function
644 before querying from OPI_DBI_REP_UOM_STD_CONV_F.
645
646 No commits done here. Calling function coordinates commit.
647
648 Parameters:
649 1. p_run_date - The run time date passed in from the wrapper routine.
650 Ensures that all records are created with the same
651 creation date as the logged run time of the program.
652
653 History:
654 Date Author Action
655 12/07/04 Dinkar Gupta Wrote Function.
656
657 */
658 PROCEDURE insert_std_rates_init (p_run_date IN DATE)
659 IS
660
661 l_proc_name CONSTANT VARCHAR2(40) := 'insert_std_rates_init';
662 l_stmt_id NUMBER;
663
664 BEGIN
665
666 -- Initialization block
667 l_stmt_id := 0;
668
669 -- Insert all standard rates where from UOM/reporting UOM combination
670 -- is not in in the fact table.
671 -- Standard rates have an item id of 0;
672 INSERT /*+ append */
673 INTO opi_dbi_rep_uom_std_conv_f (
674 from_uom_code,
675 from_uom_class,
676 rep_uom_code,
677 rep_uom_class,
678 conversion_rate,
679 conversion_type,
680 created_by,
681 creation_date,
682 last_update_date,
683 last_updated_by,
684 last_update_login,
685 program_id,
686 program_login_id,
687 program_application_id,
688 request_id
689 )
690 SELECT
691 stg.from_uom_code,
692 stg.from_uom_class,
693 stg.rep_uom_code,
694 stg.rep_uom_class,
695 stg.conversion_rate,
696 stg.conversion_type,
697 s_user_id,
698 p_run_date,
699 p_run_date,
700 s_user_id,
701 s_login_id,
702 s_program_id,
703 s_program_login_id,
704 s_program_application_id,
705 s_request_id
706 FROM
707 opi_dbi_rep_uom_conv_stg stg
708 WHERE stg.inventory_item_id = C_STD_RATE_ITEM_ID;
709
710 return;
711
712 EXCEPTION
713
714 WHEN OTHERS THEN
715
716 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
717 l_stmt_id));
718 RAISE INSERT_NEW_RATES_FAILED;
719
720
721 END insert_std_rates_init;
722
723
724 /* populate_rep_uom_std_conv_init
725
726 Wrapper routine for the initial load of the standard conversion
727 rates program.
728
729 Parameters:
730 errbuf - error message on unsuccessful termination
731 retcode - 0 on success, 1 on warning, -1 on error
732
733 History:
734 Date Author Action
735 12/01/04 Dinkar Gupta Wrote Function.
736 */
737 PROCEDURE populate_rep_uom_std_conv_init (errbuf OUT NOCOPY VARCHAR2,
738 retcode OUT NOCOPY NUMBER)
739
740 IS
741 l_proc_name CONSTANT VARCHAR2 (40) := 'populate_rep_uom_std_conv_init';
742 l_stmt_id NUMBER;
743
744 l_run_date DATE;
745
746 BEGIN
747 -- Initialization block
748 l_stmt_id := 0;
749
750
751 l_stmt_id := 5;
752 -- This is the start of the run time of the program.
753 l_run_date := sysdate;
754 print_stage_done_mesg (l_proc_name, l_stmt_id);
755
756 l_stmt_id := 10;
757 -- Check the global setup
758 global_setup ();
759 print_stage_done_mesg (l_proc_name, l_stmt_id);
760
761 l_stmt_id := 20;
762 -- set/clean up the various program related tables
763 setup_tables_init ();
764 print_stage_done_mesg (l_proc_name, l_stmt_id);
765
766 l_stmt_id := 40;
767 -- Compute all intra-class standard conversion rates and put them
768 -- in the staging table.
769 compute_std_intra_class_conv ();
770 print_stage_done_mesg (l_proc_name, l_stmt_id);
771
772 l_stmt_id := 50;
773 -- Commit intra-class rates to the staging table.
774 commit;
775 print_stage_done_mesg (l_proc_name, l_stmt_id);
776
777 l_stmt_id := 60;
778 -- Compute all inter-class standard conversion rates and put them
779 -- in the staging table.
780 compute_std_inter_class_conv ();
781 print_stage_done_mesg (l_proc_name, l_stmt_id);
782
783 l_stmt_id := 70;
784 -- Commit inter-class rates to the staging table.
785 commit;
786 print_stage_done_mesg (l_proc_name, l_stmt_id);
787
788 l_stmt_id := 80;
789 -- Insert the new rates in the staging table.
790 -- Do not commit until after update of log table.
791 insert_std_rates_init (l_run_date);
792 print_stage_done_mesg (l_proc_name, l_stmt_id);
793
794 l_stmt_id := 90;
795 -- Initial load, so now done. Just update log table.
796 update_log (l_run_date);
797 print_stage_done_mesg (l_proc_name, l_stmt_id);
798
799 l_stmt_id := 100;
800 -- Commit all data finally by truncating the staging table.
801 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
802 'OPI_DBI_REP_UOM_CONV_STG');
803 print_stage_done_mesg (l_proc_name, l_stmt_id);
804
805 errbuf := '';
806 retcode := C_SUCCESS;
807 BIS_COLLECTION_UTILITIES.PUT_LINE (C_SUCCESS_MESG);
808 return;
809
810 EXCEPTION
811
812 WHEN GLOBAL_SETUP_MISSING THEN
813 rollback;
814 retcode := C_ERROR;
815 errbuf := C_INIT_LOAD_ERROR_MESG;
816
817 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
818 (GLOBAL_START_DATE_NULL_MESG,
819 l_proc_name, l_stmt_id));
820 return;
821
822 WHEN TABLE_INIT_SETUP_FAILED THEN
823 rollback;
824 retcode := C_ERROR;
825 errbuf := C_INIT_LOAD_ERROR_MESG;
826
827 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
828 (TABLE_INIT_SETUP_FAILED_MESG,
829 l_proc_name, l_stmt_id));
830 return;
831
832 WHEN MEASURE_LIST_SETUP_FAILED THEN
833 rollback;
834 retcode := C_ERROR;
835 errbuf := C_INIT_LOAD_ERROR_MESG;
836
837 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
838 (MEASURE_LIST_SETUP_FAILED_MESG,
839 l_proc_name, l_stmt_id));
840 return;
841
842 WHEN INTRA_STD_CONV_FAILED THEN
843 rollback;
844 retcode := C_ERROR;
845 errbuf := C_INIT_LOAD_ERROR_MESG;
846
847 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
848 (INTRA_STD_CONV_FAILED_MESG,
849 l_proc_name, l_stmt_id));
850 return;
851
852 WHEN INTER_STD_CONV_FAILED THEN
853 rollback;
854 retcode := C_ERROR;
855 errbuf := C_INIT_LOAD_ERROR_MESG;
856
857 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
858 (INTER_STD_CONV_FAILED_MESG,
859 l_proc_name, l_stmt_id));
860 return;
861
862 WHEN INSERT_NEW_RATES_FAILED THEN
863 rollback;
864 retcode := C_ERROR;
865 errbuf := C_INIT_LOAD_ERROR_MESG;
866
867 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
868 (INSERT_NEW_RATES_FAILED_MESG,
869 l_proc_name, l_stmt_id));
870 return;
871
872 WHEN LOG_UPDATE_FAILED THEN
873 rollback;
874 retcode := C_ERROR;
875 errbuf := C_INIT_LOAD_ERROR_MESG;
876
877 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
878 (LOG_UPDATE_FAILED_MESG,
879 l_proc_name, l_stmt_id));
880 return;
881
882 WHEN OTHERS THEN
883 rollback;
884 retcode := C_ERROR;
885 errbuf := C_INIT_LOAD_ERROR_MESG;
886
887 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
888 l_stmt_id));
889
890 return;
891
892 END populate_rep_uom_std_conv_init;
893
894 /**************************************************
895 * Incremental Load Procedure Definitions
896 **************************************************/
897
898 /* setup_tables_incr
899
900 Cleans up tables prior to the incremental load run.
901
902 Truncates:
903 1. OPI_DBI_REP_UOM_CONV_STG
904
905 Verifies:
906 1. There is exactly one row for this ETL in the run log.
907
908 History:
909 Date Author Action
910 12/06/04 Dinkar Gupta Wrote Function.
911
912 */
913 PROCEDURE setup_tables_incr
914 IS
915
916 l_proc_name CONSTANT VARCHAR2 (40) := 'setup_tables_incr';
917 l_stmt_id NUMBER;
918
919 l_cnt NUMBER;
920
921 BEGIN
922
923 -- initialization block
924 l_stmt_id := 0;
925 l_cnt := 0;
926
927 l_stmt_id := 10;
928 -- Truncate the staging table
929 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
930 'OPI_DBI_REP_UOM_CONV_STG');
931
932 l_stmt_id := 20;
933 -- Delete all rows for this ETL from the log table
934 SELECT count (1)
935 INTO l_cnt
936 FROM opi_dbi_conc_prog_run_log
937 WHERE etl_type = C_ETL_TYPE;
938
939
940 l_stmt_id := 30;
941 IF (l_cnt <> 1) THEN
942 RAISE LAST_RUN_RECORD_MISSING;
943 END IF;
944
945 return;
946
947 EXCEPTION
948
949 WHEN LAST_RUN_RECORD_MISSING THEN
950 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
951 (LAST_RUN_RECORD_MISSING_MESG,
952 l_proc_name, l_stmt_id));
953 RAISE TABLE_INIT_SETUP_FAILED;
954
955
956 WHEN OTHERS THEN
957 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
958 l_stmt_id));
959 RAISE TABLE_INIT_SETUP_FAILED;
960
961 END setup_tables_incr;
962
963
964 /* merge_std_rates_incr
965
966 Merge the rates during the incremental run of the program.
967 Any new UOM combination related rates must be added to the fact.
968 Any existing combinations must be updated
969 only if the new rate is different from the old rate.
970 Do not alter records that have not changed between the previous and
971 current run.
972
973 No commits done here. Calling function coordinates commit.
974
975 Parameters:
976 1. p_run_date - The run time date passed in from the wrapper routine.
977 Ensures that all records are created with the same
978 creation date as the logged run time of the program.
979
980 History:
981 Date Author Action
982 12/07/04 Dinkar Gupta Wrote Function.
983
984
985 */
986 PROCEDURE merge_std_rates_incr (p_run_date IN DATE)
987 IS
988 l_proc_name CONSTANT VARCHAR2 (40) := 'merge_std_rates_incr';
989 l_stmt_id NUMBER;
990
991 BEGIN
992
993 -- Initialization block
994 l_stmt_id := 0;
995
996 l_stmt_id := 10;
997 -- Merge the existing rates into the fact table.
998 -- If a combination does not exist in the fact table, then
999 -- it needs to be added to it.
1000 -- If a combination exists and the rate is different in
1001 -- the fact and the staging table, the fact needs to be updated.
1002 -- If the combination and conversion rate in the fact and
1003 -- and the staging table is the same, the record should not be changed.
1004 MERGE INTO opi_dbi_rep_uom_std_conv_f base
1005 USING
1006 (SELECT
1007 from_uom_code,
1008 from_uom_class,
1009 rep_uom_code,
1010 rep_uom_class,
1011 conversion_rate,
1012 conversion_type,
1013 s_user_id created_by,
1014 p_run_date creation_date,
1015 p_run_date last_update_date,
1016 s_user_id last_updated_by,
1017 s_login_id last_update_login,
1018 s_program_id program_id,
1019 s_program_login_id program_login_id,
1020 s_program_application_id program_application_id,
1021 s_request_id request_id
1022 FROM opi_dbi_rep_uom_conv_stg
1023 WHERE inventory_item_id = C_STD_RATE_ITEM_ID) new
1024 ON ( base.from_uom_code = new.from_uom_code
1025 AND base.rep_uom_code = new.rep_uom_code)
1026 WHEN MATCHED THEN UPDATE
1027 SET
1028 base.conversion_rate = decode
1029 (base.conversion_rate,
1030 new.conversion_rate, base.conversion_rate,
1031 new.conversion_rate),
1032 base.last_update_date = decode
1033 (base.conversion_rate,
1034 new.conversion_rate, base.last_update_date,
1035 new.last_update_date),
1036 base.last_updated_by = decode
1037 (base.conversion_rate,
1038 new.conversion_rate, base.last_updated_by,
1039 new.last_updated_by),
1040 base.last_update_login = decode
1041 (base.conversion_rate,
1042 new.conversion_rate,
1043 base.last_update_login,
1044 new.last_update_login),
1045 base.program_id = decode
1046 (base.conversion_rate,
1047 new.conversion_rate, base.program_id,
1048 new.program_id),
1049 base.program_login_id = decode
1050 (base.conversion_rate,
1051 new.conversion_rate, base.program_login_id,
1052 new.program_login_id),
1053 base.program_application_id = decode
1054 (base.conversion_rate,
1055 new.conversion_rate,
1056 base.program_application_id,
1057 new.program_application_id),
1058 base.request_id = decode
1059 (base.conversion_rate,
1060 new.conversion_rate, base.request_id,
1061 new.request_id)
1062 WHEN NOT MATCHED THEN INSERT (
1063 from_uom_code,
1064 from_uom_class,
1065 rep_uom_code,
1066 rep_uom_class,
1067 conversion_rate,
1068 conversion_type,
1069 created_by,
1070 creation_date,
1071 last_update_date,
1072 last_updated_by,
1073 last_update_login,
1074 program_id,
1075 program_login_id,
1076 program_application_id,
1077 request_id)
1078 VALUES (
1079 new.from_uom_code,
1080 new.from_uom_class,
1081 new.rep_uom_code,
1082 new.rep_uom_class,
1083 new.conversion_rate,
1084 new.conversion_type,
1085 new.created_by,
1086 new.creation_date,
1087 new.last_update_date,
1088 new.last_updated_by,
1089 new.last_update_login,
1090 new.program_id,
1091 new.program_login_id,
1092 new.program_application_id,
1093 new.request_id);
1094
1095 return;
1096
1097 EXCEPTION
1098
1099 WHEN OTHERS THEN
1100
1101 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1102 l_stmt_id));
1103 RAISE MERGE_STD_RATES_FAILED;
1104
1105 END merge_std_rates_incr;
1106
1107
1108 /* populate_rep_uom_std_conv_incr
1109
1110 Wrapper routine for the incremental load of the standard conversion
1111 rates program.
1112
1113 Parameters:
1114 errbuf - error message on unsuccessful termination
1115 retcode - 0 on success, 1 on warning, -1 on error
1116
1117 History:
1118 Date Author Action
1119 12/01/04 Dinkar Gupta Wrote Function.
1120 */
1121 PROCEDURE populate_rep_uom_std_conv_incr (errbuf OUT NOCOPY VARCHAR2,
1122 retcode OUT NOCOPY NUMBER)
1123 IS
1124 l_proc_name CONSTANT VARCHAR2 (40) := 'populate_rep_uom_std_conv_incr';
1125 l_stmt_id NUMBER;
1126
1127 l_run_date DATE;
1128
1129 BEGIN
1130 -- Initialization block
1131 l_stmt_id := 0;
1132
1133 l_stmt_id := 5;
1134 -- This is the start of the run time of the program.
1135 l_run_date := sysdate;
1136 print_stage_done_mesg (l_proc_name, l_stmt_id);
1137
1138 l_stmt_id := 10;
1139 -- Check the global setup
1140 global_setup ();
1141 print_stage_done_mesg (l_proc_name, l_stmt_id);
1142
1143 l_stmt_id := 20;
1144 -- set/clean up the various program related tables
1145 setup_tables_incr ();
1146 print_stage_done_mesg (l_proc_name, l_stmt_id);
1147
1148 l_stmt_id := 40;
1149 -- Compute all intra-class standard conversion rates and put them
1150 -- in the staging table.
1151 compute_std_intra_class_conv ();
1152 print_stage_done_mesg (l_proc_name, l_stmt_id);
1153
1154 l_stmt_id := 50;
1155 -- Commit intra-class rates to the staging table.
1156 commit;
1157 print_stage_done_mesg (l_proc_name, l_stmt_id);
1158
1159 l_stmt_id := 60;
1160 -- Compute all inter-class standard conversion rates and put them
1161 -- in the staging table.
1162 compute_std_inter_class_conv ();
1163 print_stage_done_mesg (l_proc_name, l_stmt_id);
1164
1165 l_stmt_id := 70;
1166 -- Commit inter-class rates to the staging table.
1167 commit;
1168 print_stage_done_mesg (l_proc_name, l_stmt_id);
1169
1170 l_stmt_id := 80;
1171 -- Merge the new rates into the staging table.
1172 -- Note that:
1173 -- 1. Need to insert new rates that are not presently in the fact, but
1174 -- are in the staging table.
1175 -- 2. Need to update rates for combinations in the fact and staging
1176 -- where the conversion rate in the staging table is different
1177 -- from that in the fact.
1178 -- 3. Need to not touch any other rates.
1179 -- Do not commit until after updating the log table.
1180 merge_std_rates_incr (l_run_date);
1181 print_stage_done_mesg (l_proc_name, l_stmt_id);
1182
1183 l_stmt_id := 90;
1184 -- Update the run log with the run time of this run.
1185 update_log (l_run_date);
1186 print_stage_done_mesg (l_proc_name, l_stmt_id);
1187
1188 l_stmt_id := 100;
1189 -- Commit all data finally by truncating the log table.
1190 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
1191 'OPI_DBI_REP_UOM_CONV_STG');
1192 print_stage_done_mesg (l_proc_name, l_stmt_id);
1193
1194 errbuf := '';
1195 retcode := C_SUCCESS;
1196 BIS_COLLECTION_UTILITIES.PUT_LINE (C_SUCCESS_MESG);
1197 return;
1198
1199 EXCEPTION
1200
1201 WHEN GLOBAL_SETUP_MISSING THEN
1202 rollback;
1203 retcode := C_ERROR;
1204 errbuf := C_INCR_LOAD_ERROR_MESG;
1205
1206 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1207 (GLOBAL_START_DATE_NULL_MESG,
1208 l_proc_name, l_stmt_id));
1209 return;
1210
1211 WHEN TABLE_INIT_SETUP_FAILED THEN
1212 rollback;
1213 retcode := C_ERROR;
1214 errbuf := C_INCR_LOAD_ERROR_MESG;
1215
1216 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1217 (TABLE_INIT_SETUP_FAILED_MESG,
1218 l_proc_name, l_stmt_id));
1219 return;
1220
1221 WHEN INTRA_STD_CONV_FAILED THEN
1222 rollback;
1223 retcode := C_ERROR;
1224 errbuf := C_INCR_LOAD_ERROR_MESG;
1225
1226 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1227 (INTRA_STD_CONV_FAILED_MESG,
1228 l_proc_name, l_stmt_id));
1229 return;
1230
1231 WHEN INTER_STD_CONV_FAILED THEN
1232 rollback;
1233 retcode := C_ERROR;
1234 errbuf := C_INCR_LOAD_ERROR_MESG;
1235
1236 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1237 (INTER_STD_CONV_FAILED_MESG,
1238 l_proc_name, l_stmt_id));
1239 return;
1240
1241 WHEN MERGE_STD_RATES_FAILED THEN
1242 rollback;
1243 retcode := C_ERROR;
1244 errbuf := C_INCR_LOAD_ERROR_MESG;
1245
1246 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1247 (MERGE_STD_RATES_FAILED_MESG,
1248 l_proc_name, l_stmt_id));
1249 return;
1250
1251 WHEN LOG_UPDATE_FAILED THEN
1252 rollback;
1253 retcode := C_ERROR;
1254 errbuf := C_INCR_LOAD_ERROR_MESG;
1255
1256 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1257 (LOG_UPDATE_FAILED_MESG,
1258 l_proc_name, l_stmt_id));
1259 return;
1260
1261
1262 WHEN OTHERS THEN
1263 rollback;
1264
1265 retcode := C_ERROR;
1266 errbuf := C_INCR_LOAD_ERROR_MESG;
1267
1268 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1269 l_stmt_id));
1270
1271 return;
1272
1273 END populate_rep_uom_std_conv_incr;
1274
1275 END opi_dbi_rep_uom_std_conv_pkg;
[Home] [Help]