1 PACKAGE BODY opi_dbi_wms_storage_utz_pkg AS
2 /*$Header: OPIDEWSTORB.pls 120.1 2006/02/14 01:45:16 achandak noship $ */
3
4
5 /**************************************************
6 * File scope variables
7 **************************************************/
8
9 -- Package level variables for session info-
10 -- including schema name for truncating and
11 -- collecting stats. Initialized in check_global_setup.
12 s_opi_schema VARCHAR2(30);
13 s_status VARCHAR2(30);
14 s_industry VARCHAR2(30);
15
16 -- Package level variables for the standard who columns
17 s_user_id NUMBER;
18 s_login_id NUMBER;
19 s_program_id NUMBER;
20 s_program_login_id NUMBER;
21 s_program_application_id NUMBER;
22 s_request_id NUMBER;
23
24 -- Weight and Volume reporting UOMs
25 s_wt_rep_uom_code VARCHAR2 (3);
26 s_vol_rep_uom_code VARCHAR2 (3);
27
28 /**************************************************
29 * Common Procedures (to locator capacity and item storage computations)
30 *
31 * File scope functions (not in spec)
32 **************************************************/
33
34 -- Global variable setup and verification
35 PROCEDURE global_setup;
36
37 -- Print out error message in a consistent manner
38 FUNCTION err_mesg (p_mesg IN VARCHAR2,
39 p_proc_name IN VARCHAR2,
40 p_stmt_id IN NUMBER)
41 RETURN VARCHAR2;
42
43 -- Print stage done message
44 PROCEDURE print_stage_done_mesg (p_proc_name IN VARCHAR2,
45 p_stmt_id IN NUMBER);
46
47 -- Update the log table.
48 PROCEDURE update_log (p_run_date IN DATE);
49
50 -- Check for missing rates
51 FUNCTION check_missing_rates (p_table_name IN VARCHAR2)
52 RETURN BOOLEAN;
53
54 -- Transfer fact table records into the staging table.
55 PROCEDURE transfer_fact_to_staging;
56
57
58 /**************************************************
59 * Initial load procedures
60 **************************************************/
61 -- Setup/clean up relevant tables
62 PROCEDURE setup_tables_init;
63
64 -- Compute the conversion rates for weight/volume for
65 -- all items for the initial load
66 PROCEDURE compute_conv_rates_init (p_wt_rep_uom_code IN VARCHAR2,
67 p_vol_rep_uom_code IN VARCHAR2);
68
69 /**************************************************
70 * Incremental load procedures
71 **************************************************/
72 -- Setup/clean up relevant tables
73 PROCEDURE setup_tables_incr;
74
75 -- Compute the conversion rates for weight/volume for
76 -- all items for the incremental runs
77 PROCEDURE compute_conv_rates_incr (p_wt_rep_uom_code IN VARCHAR2,
78 p_vol_rep_uom_code IN VARCHAR2);
79
80
81 /**************************************************
82 * Common Procedures Definitions
83 **************************************************/
84
85 /* global_setup
86
87 Performs global setup of file scope variables and does any checking
88 needed for global DBI setups.
89
90 Parameters: None
91
92 History:
93 Date Author Action
94 12/13/04 Dinkar Gupta Defined function.
95
96 */
97 PROCEDURE global_setup
98 IS
99 l_proc_name CONSTANT VARCHAR2 (40) := 'global_setup';
100 l_stmt_id NUMBER;
101
102
103 -- Cursor to get the reporting UOM for a certain measure code.
104 -- Can be null if not set up.
105 CURSOR get_rep_uom_csr (p_measure_code VARCHAR2)
106 IS
107 SELECT rep_uom_code
108 FROM opi_dbi_rep_uoms
109 WHERE measure_code = p_measure_code
110 AND rep_uom_code IS NOT NULL;
111
112 BEGIN
113
114 -- Initialization block
115 l_stmt_id := 0;
116
117 l_stmt_id := 10;
118 -- Obtain the OPI schema name to allow truncation of various tables
119 -- get session parameters
120 IF (NOT (fnd_installation.get_app_info('OPI', s_status, s_industry,
121 s_opi_schema))) THEN
122 RAISE SCHEMA_INFO_NOT_FOUND;
123 END IF;
124
125 l_stmt_id := 20;
126 -- Package level variables for the user logged in
127 s_user_id := nvl(fnd_global.user_id, -1);
128 s_login_id := nvl(fnd_global.login_id, -1);
129 s_program_id := nvl (fnd_global.conc_program_id, -1);
130 s_program_login_id := nvl (fnd_global.conc_login_id, -1);
131 s_program_application_id := nvl (fnd_global.prog_appl_id, -1);
132 s_request_id := nvl (fnd_global.conc_request_id, -1);
133
134 l_stmt_id := 30;
135 -- Get the weight reporting UOM.
136 OPEN get_rep_uom_csr (C_WT_MEASURE_CODE);
137 FETCH get_rep_uom_csr INTO s_wt_rep_uom_code;
138 IF (get_rep_uom_csr%NOTFOUND) THEN
139 s_wt_rep_uom_code := NULL;
140 END IF;
141
142 CLOSE get_rep_uom_csr;
143
144 l_stmt_id := 40;
145 -- Get the volume reporting UOM.
146 OPEN get_rep_uom_csr (C_VOL_MEASURE_CODE);
147 FETCH get_rep_uom_csr INTO s_vol_rep_uom_code;
148 IF (get_rep_uom_csr%NOTFOUND) THEN
149 s_vol_rep_uom_code := NULL;
150 END IF;
151
152 CLOSE get_rep_uom_csr;
153
154 return;
155
156 EXCEPTION
157
158 WHEN SCHEMA_INFO_NOT_FOUND THEN
159
160 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
161 (SCHEMA_INFO_NOT_FOUND_MESG,
162 l_proc_name, l_stmt_id));
163 RAISE GLOBAL_SETUP_MISSING;
164
165
166 WHEN OTHERS THEN
167 rollback;
168
169 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
170 l_stmt_id));
171 RAISE GLOBAL_SETUP_MISSING;
172
173 END global_setup;
174
175
176
177 /* err_mesg
178
179 Return a C_ERRBUF_SIZE character long, properly formatted error
180 message with the package name, procedure name and message.
181
182 Parameters:
183 p_mesg - Actual message to be printed
184 p_proc_name - name of procedure that should be printed in the message
185 (optional)
186 p_stmt_id - step in procedure at which error occurred
187 (optional)
188
189 History:
190 Date Author Action
191 12/13/04 Dinkar Gupta Defined function.
192 */
193
194 FUNCTION err_mesg (p_mesg IN VARCHAR2,
195 p_proc_name IN VARCHAR2,
196 p_stmt_id IN NUMBER)
197 RETURN VARCHAR2
198 IS
199
200 l_proc_name CONSTANT VARCHAR2 (60) := 'err_mesg';
201 l_stmt_id NUMBER;
202
203 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
204 -- as the size of the declaration. I have to put 300 here.
205 l_formatted_message VARCHAR2 (300);
206
207 BEGIN
208
209 -- initialization block
210 l_stmt_id := 0;
211
212 -- initialization block
213 l_formatted_message := NULL;
214
215 l_stmt_id := 10;
216 l_formatted_message := substr ((C_PKG_NAME || '.' || p_proc_name || ' #' ||
217 to_char (p_stmt_id) || ': ' || p_mesg),
218 1, C_ERRBUF_SIZE);
219
220 commit;
221
222 return l_formatted_message;
223
224 EXCEPTION
225
226 WHEN OTHERS THEN
227 -- the exception happened in the exception reporting function !!
228 -- return with ERROR.
229 l_formatted_message := substr ((C_PKG_NAME || '.' || l_proc_name ||
230 ' #' ||
231 to_char (l_stmt_id) || ': ' ||
232 SQLERRM),
233 1, C_ERRBUF_SIZE);
234
235 l_formatted_message := 'Error in error reporting.';
236 return l_formatted_message;
237
238 END err_mesg;
239
240 /* print_stage_done_mesg
241
242 Print a message of 'Done' for whatever procedure/statement called.
243
244 Parameters:
245 p_proc_name - name of procedure that should be printed in the message
246 p_stmt_id - step in procedure at which error occurred
247
248 History:
249 Date Author Action
250 12/13/04 Dinkar Gupta Defined function.
251 */
252
253 PROCEDURE print_stage_done_mesg (p_proc_name IN VARCHAR2,
254 p_stmt_id IN NUMBER)
255 IS
256
257 l_proc_name CONSTANT VARCHAR2 (60) := 'print_stage_done_mesg';
258 l_stmt_id NUMBER;
259
260 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
261 -- as the size of the declaration. I have to put 300 here.
262 l_formatted_message VARCHAR2 (300);
263
264 BEGIN
265
266 -- initialization block
267 l_stmt_id := 0;
268
269 -- initialization block
270 l_formatted_message := NULL;
271
272 l_stmt_id := 10;
273 l_formatted_message := substr ((C_PKG_NAME || '.' || p_proc_name || ' #' ||
274 to_char (p_stmt_id) || ': ' || 'Done.'),
275 1, C_ERRBUF_SIZE);
276
277 BIS_COLLECTION_UTILITIES.PUT_LINE (l_formatted_message);
278
279 return;
280
281 EXCEPTION
282
283 WHEN OTHERS THEN
284 -- the exception happened in the print function
285 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
286 l_stmt_id));
287
288 RAISE; -- on to wrapper
289
290 END print_stage_done_mesg;
291
292 /* update_log
293
294 Update the log table for the reporting UOM conversion rates ETL
295 with the run date provided as a parameter.
296
297 Parameters:
298 1. p_run_date - run date of the program, provided by the wrapper. This
299 is the start time of the program, which can be used
300 as a marker between incremental runs.
301
302 No commits done here. Calling function coordinates commit.
303
304 History:
305 Date Author Action
306 12/13/04 Dinkar Gupta Wrote Function.
307
308 */
309 PROCEDURE update_log (p_run_date IN DATE)
310 IS
311
312 l_proc_name CONSTANT VARCHAR2 (40) := 'update_log';
313 l_stmt_id NUMBER;
314
315 BEGIN
316
317 -- Initialization block
318 l_stmt_id := 10;
319
320 -- Update the row for this ETL. It is assumed that there is only
321 -- one such row.
322 UPDATE opi_dbi_conc_prog_run_log
323 SET last_run_date = p_run_date,
324 last_update_date = sysdate,
325 last_updated_by = s_user_id,
326 last_update_login = s_login_id,
327 request_id = s_request_id,
328 program_application_id = s_program_application_id,
329 program_id = s_program_id,
330 program_login_id = s_program_login_id
331 WHERE etl_type = C_ETL_TYPE;
332
333 return;
334
335 EXCEPTION
336
337 WHEN OTHERS THEN
338
339 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
340 l_stmt_id));
341 RAISE LOG_UPDATE_FAILED;
342
343 END update_log;
344
345 /* check_missing_rates
346
347 Check if there are any rates that are missing.
348
349 Report a summary of the rates missing, and whether they are
350 inter or intra class.
351
352 Parameters:
353 p_table_name - opi_dbi_wms_stor_item_conv_f for fact table,
354 opi_dbi_wms_stor_item_conv_stg for staging table.
355
356 Return:
357 TRUE - if rates are missing.
358 FALSE - if no rates are missing.
359
360 No commits done here. Calling function coordinates commit.
361
362 History:
363 Date Author Action
364 12/20/04 Dinkar Gupta Wrote Function.
365
366 */
367 FUNCTION check_missing_rates (p_table_name IN VARCHAR2)
368 RETURN BOOLEAN
369 IS
370
371 l_proc_name CONSTANT VARCHAR2 (40) := 'check_missing_rates';
372 l_stmt_id NUMBER;
373
374 -- Check if missing rates were found
375 l_missing_rates BOOLEAN;
376
377 -- Print the header only once.
378 l_header_printed BOOLEAN;
379
380 -- Record type to fetch missing rates records into
381 TYPE err_rec_type IS RECORD (uom_code VARCHAR2 (3));
382 l_err_rec err_rec_type;
383
384 -- Missing rates cursors
385 TYPE err_curr_type IS REF CURSOR;
386 wt_missing_rates_csr err_curr_type;
387 vol_missing_rates_csr err_curr_type;
388
389 BEGIN
390
391 -- Initialization block
392 l_stmt_id := 0;
393 l_missing_rates := FALSE; -- nothing missing so far
394 l_header_printed := FALSE;
395
396 -- Open the two cursors for the different conversion errors.
397 -- Ignore NULL rates - only care about errors.
398 l_stmt_id := 10;
399 OPEN wt_missing_rates_csr FOR
400 'SELECT DISTINCT unit_weight_uom_code
401 FROM ' || p_table_name || '
402 WHERE weight_conv_rate < 0';
403
404 l_stmt_id := 20;
405 OPEN vol_missing_rates_csr FOR
406 'SELECT DISTINCT unit_volume_uom_code
407 FROM ' || p_table_name || '
408 WHERE volume_conv_rate < 0';
409
410 -- Leverage a lot of the basic functionality in the reporting UOM package.
411
412 -- Print the header for weight missing rates.
413 l_header_printed := FALSE;
414
415 -- Report the weight missing rates first.
416 l_stmt_id := 30;
417 FETCH wt_missing_rates_csr INTO l_err_rec;
418 WHILE wt_missing_rates_csr%FOUND
419 LOOP
420
421 -- Print the header once
422 l_stmt_id := 20;
423 IF (l_header_printed = FALSE) THEN
424
425 OPI_DBI_REP_UOM_PKG.err_msg_header_spec ('WT', 'ITEM');
426 l_header_printed := TRUE;
427
428 END IF;
429
430 -- Print the missing rates.
431 l_stmt_id := 30;
432 OPI_DBI_REP_UOM_PKG.err_msg_missing_uoms (
433 l_err_rec.uom_code, s_wt_rep_uom_code);
434
435 -- Found at least one bad rate
436 l_stmt_id := 40;
437 l_missing_rates := TRUE;
438
439 FETCH wt_missing_rates_csr INTO l_err_rec;
440
441 END LOOP;
442
443 -- Footer message.
444 IF (l_header_printed = TRUE) THEN
445 OPI_DBI_REP_UOM_PKG.err_msg_footer;
446 END IF;
447
448 -- Print the header for volume missing rates again.
449 l_header_printed := FALSE;
450
451 -- Report volume missing rates.
452 l_stmt_id := 40;
453 FETCH vol_missing_rates_csr INTO l_err_rec;
454 WHILE vol_missing_rates_csr%FOUND
455 LOOP
456
457 -- Print the header once
458 l_stmt_id := 20;
459 IF (l_header_printed = FALSE) THEN
460
461 OPI_DBI_REP_UOM_PKG.err_msg_header_spec ('VOL', 'ITEM');
462 l_header_printed := TRUE;
463
464 END IF;
465
466 -- Print the missing rates.
467 l_stmt_id := 30;
468 OPI_DBI_REP_UOM_PKG.err_msg_missing_uoms (
469 l_err_rec.uom_code, s_vol_rep_uom_code);
470
471 -- Found at least one bad rate
472 l_stmt_id := 40;
473 l_missing_rates := TRUE;
474
475 FETCH vol_missing_rates_csr INTO l_err_rec;
476
477 END LOOP;
478
479 -- Footer message.
480 IF (l_header_printed = TRUE) THEN
481 OPI_DBI_REP_UOM_PKG.err_msg_footer;
482 END IF;
483
484 -- Finally!
485 return l_missing_rates;
486
487 EXCEPTION
488
489 WHEN OTHERS THEN
490 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
491 l_stmt_id));
492 RAISE CONV_RATES_CHECK_FAILED;
493
494 END check_missing_rates;
495
496 /* transfer_fact_to_staging
497
498 Transfer all fact table records to the staging table. Used in the
499 initial load in case of errors, because the initial load directly
500 loads the fact table. However in case of errors, the fact
501 will be truncated and the data will be left in the staging table
502 for detailed reporting.
503
504 No commits done here. Calling function coordinates commit.
505
506 History:
507 Date Author Action
508 11/01/05 Dinkar Gupta Wrote Function.
509
510 */
511 PROCEDURE transfer_fact_to_staging
512
513 IS
514
515 l_proc_name CONSTANT VARCHAR2 (40) := 'check_missing_rates';
516 l_stmt_id NUMBER;
517
518 BEGIN
519
520 -- Initialization block
521 l_stmt_id := 0;
522
523 -- Move all data from the fact to the staging table.
524 -- Not responsible for truncating staging table.
525 l_stmt_id := 10;
526 INSERT INTO opi_dbi_wms_stor_item_conv_stg (
527 organization_id,
528 inventory_item_id,
529 unit_weight_uom_code,
530 weight_conv_rate,
531 weight_conv_rate_type,
532 unit_volume_uom_code,
533 volume_conv_rate,
534 volume_conv_rate_type
535 )
536 SELECT
537 organization_id,
538 inventory_item_id,
539 unit_weight_uom_code,
540 weight_conv_rate,
541 weight_conv_rate_type,
542 unit_volume_uom_code,
543 volume_conv_rate,
544 volume_conv_rate_type
545 FROM opi_dbi_wms_stor_item_conv_f;
546
547 return;
548
549 EXCEPTION
550
551 WHEN OTHERS THEN
552 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
553 l_stmt_id));
554 RAISE TRX_STG_TO_FACT_FAILED;
555
556
557 END transfer_fact_to_staging;
558
559
560
561 /**************************************************
562 * Initial Load functions
563 **************************************************/
564
565 /* setup_tables_init
566
567 Truncate the following tables:
568 1. opi_dbi_wms_stor_item_conv_f
569 2. opi_dbi_wms_stor_item_conv_stg
570
571 Clean up the run log table, OPI_DBI_CONC_PROG_RUN_LOG
572 and seed in a new row.
573
574
575 History:
576 Date Author Action
577 12/13/04 Dinkar Gupta Wrote Function.
578
579
580 */
581 PROCEDURE setup_tables_init
582 IS
583
584 l_proc_name CONSTANT VARCHAR2 (40) := 'setup_tables_init';
585 l_stmt_id NUMBER;
586
587 BEGIN
588
589 -- Initialization block
590 l_stmt_id := 0;
591
592 l_stmt_id := 10;
593 -- Truncate the fact table
594 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
595 'MLOG$_OPI_DBI_WMS_STOR_ITE');
596
597 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
598 'OPI_DBI_WMS_STOR_ITEM_CONV_F');
599
600 l_stmt_id := 20;
601 -- Truncate the staging table
602 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
603 'OPI_DBI_WMS_STOR_ITEM_CONV_STG');
604
605
606 l_stmt_id := 30;
607 -- Delete all rows from the log table for this ETL.
608 DELETE
609 FROM opi_dbi_conc_prog_run_log
610 WHERE etl_type = C_ETL_TYPE;
611
612 l_stmt_id := 40;
613 -- Record the delete
614 commit;
615
616 l_stmt_id := 50;
617 -- Create a new row for the ETL in the run log, with a last
618 -- run date set to a very log time ago.
619 INSERT INTO opi_dbi_conc_prog_run_log (
620 etl_type,
621 last_run_date,
622 created_by,
623 creation_date,
624 last_update_date,
625 last_updated_by,
626 last_update_login,
627 program_id,
628 program_login_id,
629 program_application_id,
630 request_id
631 )
632 VALUES (C_ETL_TYPE,
633 C_START_RUN_DATE,
634 s_user_id,
635 sysdate,
636 sysdate,
637 s_user_id,
638 s_login_id,
639 s_program_id,
640 s_program_login_id,
641 s_program_application_id,
642 s_request_id);
643
644 l_stmt_id := 60;
645 -- Record this new row.
646 commit;
647
648 l_stmt_id := 70;
649 -- Alter the Session variables for good Performance
650 execute immediate 'alter session set hash_area_size=100000000';
651 execute immediate 'alter session set sort_area_size=100000000';
652
653 return;
654
655 EXCEPTION
656
657 WHEN OTHERS THEN
658 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
659 l_stmt_id));
660 RAISE TABLE_SETUP_FAILED;
661
662 END setup_tables_init;
663
664
665 /* compute_conv_rates_init
666
667 Extract the item weight/volume conversions to the corresponding
668 reporting UOMS. The item information is stored in the inventory value
669 fact. Only interested in the items of WMS enabled discrete organizations.
670 (This set of items is generally smaller than all the items for all
671 organizations).
672
673 If the conversion rate for the weight/volume to the reporting UOMs
674 is not defined, store negative error codes.
675
676
677
678 Note also that one or both of the reporting units for weight
679 and volume can be NULL. That is not an error condition.
680
681 No committing of data here. The calling function handles that.
682
683 Parameters:
684 1. p_wt_rep_uom_code - Weight reporting UOM code
685 2. p_vol_rep_uom_code - Volume reporting UOM code
686
687 History:
688 Date Author Action
689 12/13/04 Dinkar Gupta Wrote Function.
690
691 */
692 PROCEDURE compute_conv_rates_init (p_wt_rep_uom_code IN VARCHAR2,
693 p_vol_rep_uom_code IN VARCHAR2)
694
695 IS
696 l_proc_name CONSTANT VARCHAR2 (40) := 'compute_conv_rates_init';
697 l_stmt_id NUMBER;
698
699 -- local copy of reporting UOMs
700 l_wt_rep_uom_code VARCHAR2 (3);
701 l_vol_rep_uom_code VARCHAR2 (3);
702
703 BEGIN
704
705 -- initialization block
706 l_stmt_id := 0;
707 l_wt_rep_uom_code := NULL;
708 l_vol_rep_uom_code := NULL;
709
710 l_stmt_id := 10;
711 -- If both reporting UOMs are undefined, return. Wrapper routine
712 -- should ensure this doesn't happen, but check just in case.
713 IF (p_wt_rep_uom_code IS NULL AND
714 p_vol_rep_uom_code IS NULL) THEN
715 RAISE NO_REP_UOMS_DEFINED;
716 END IF;
717
718 l_stmt_id := 20;
719 -- Set the reporting UOM's to the dummy code, to avoid handling
720 -- the condition that one of them can be NULL in the SQL below.
721 l_wt_rep_uom_code := nvl (p_wt_rep_uom_code, C_DUMMY_UOM_CODE);
722 l_vol_rep_uom_code := nvl (p_vol_rep_uom_code, C_DUMMY_UOM_CODE);
723
724 l_stmt_id := 30;
725 -- Extract the weight/volume conversion rates for all
726 -- distinct item/org pairs that belong to WMS enabled discrete
727 -- manufacturing organizations and that are already present in the
728 -- inventory value fact, OPI_DBI_INV_VALUE_F. Need to specifically
729 -- filter out process organizations here, because we only
730 -- join to the inventory value fact which has data from both, discrete
731 -- and process organizations.
732 --
733 -- Collecting 2 measures:
734 -- 1. Conversion rate from the item weight UOM code to the weight
735 -- reporting UOM code.
736 -- 2. Conversion rate from the item volume UOM code to the volume
737 -- reporting UOM code.
738 --
739 -- The item setup form guarantees that a UOM be specified for the
740 -- item weight/volume before a value is specified. So no need to
741 -- worry about missing UOMs and specified values.
742 --
743 -- All possible conversions to the reporting UOMs are stored in
744 -- the standard conversion rates table, OPI_DBI_REP_UOM_STD_CONV_F.
745 -- The extraction program will join twice to this table, once
746 -- for weight conversions and once for volume conversions.
747 -- Since certain rows may not join to the conversion fact for
748 -- one of the measures, use outer joins.
749 --
750 -- The following pathalogical scenarios are possible:
751 --
752 -- Missing Setups:
753 -- 1. Item Weight setup is missing.
754 -- 2. Item Volume setup is missing.
755 -- Either or both of the above are acceptable. In case of no setup,
756 -- report NULL for the corresponding measure.
757 --
758 -- Missing conversion rates:
759 -- 1. Item weight cannot be converted into the reporting weight UOM.
760 -- 2. Item volume cannot be converted into the reporting volume UOM.
761 -- This will be recorded as a negative number, given the negative
762 -- error codes for missing rates. Store that for the moment, and
763 -- report errors later. If no conversion rate is found, record a
764 -- negative number.
765 --
766 -- Missing Reporting UOMs:
767 -- 1. The Weight Reporting UOM can be missing.
768 -- 2. The Volume Reporting UOM can be missing.
769 -- Since both won't be missing, these are acceptable scenarios i.e.
770 -- ones where customers do not care about one of the measures. Simply
771 -- set the conversion rate for that measure to NULL everywhere.
772 --
773 -- Weight/Volume UOM can be the same:
774 -- 1. The item's weight capacity and volume UOM can be the
775 -- same. This is acceptable. However, suppose this UOM is Kg, that
776 -- needs to be converted to weight reporting UOM (lbs) and volume
777 -- reporting UOM M3.
778 -- Say the conversion rates table has a rate from Kg --> lbs, but
779 -- nothing from Kg --> M3. To avoid filtering out rows for such
780 -- items and instead reporting errors for them, this SQL has
781 -- to be written with an inline view which contains all the
782 -- from and reporting UOMs, and an outer SQL that purely outer
783 -- joins to the standard conversion rates tables based on these
784 -- from and reporting UOMs.
785 --
786 INSERT /*+ append parallel (opi_dbi_wms_stor_item_conv_f) */
787 INTO opi_dbi_wms_stor_item_conv_f (
788 organization_id,
789 inventory_item_id,
790 unit_weight_uom_code,
791 weight_conv_rate,
792 weight_conv_rate_type,
793 unit_volume_uom_code,
794 volume_conv_rate,
795 volume_conv_rate_type,
796 creation_date,
797 last_update_date,
798 created_by,
799 last_updated_by,
800 last_update_login,
801 program_id,
802 program_login_id,
803 program_application_id,
804 request_id)
805 SELECT /*+ use_hash (items, wt_conv, vol_conv)
806 parallel (items) parallel (wt_conv) parallel (vol_conv) */
807 items.organization_id,
808 items.inventory_item_id,
809 decode (items.weight_uom_code,
810 C_DUMMY_UOM_CODE, NULL,
811 items.weight_uom_code) unit_weight_uom_code,
812 CASE -- order of conditions matters
813 WHEN items.unit_weight IS NULL THEN
814 -- Weight not setup. Acceptable.
815 NULL
816 WHEN items.wt_rep_uom_code = C_DUMMY_UOM_CODE THEN
817 -- Weight reporting UOM not set up. Acceptable
818 NULL
819 WHEN wt_conv.conversion_rate IS NULL THEN
820 -- Row created from pure outer join i.e. it is a valid
821 -- combination with non-null UOMs and there is
822 -- no conv. rate for this combination.
823 -- From_uom_code and rep_uom_code
824 -- will be null in the wt_conv table for this row also.
825 C_CONV_NOT_SETUP
826 WHEN wt_conv.conversion_rate < 0 THEN
827 -- Error found in conversion rates table.
828 C_CONV_NOT_SETUP
829 WHEN wt_conv.conversion_rate >= 0 THEN
830 -- Valid conv. rate found.
831 -- Note: allowing conv. rate = 0.
832 wt_conv.conversion_rate
833 ELSE
834 -- Why will we get here? Should really never.
835 C_CONV_NOT_SETUP
836 END weight_conv_rate,
837 wt_conv.conversion_type weight_conv_rate_type,
838 decode (items.volume_uom_code,
839 C_DUMMY_UOM_CODE, NULL,
840 items.volume_uom_code) unit_volume_uom_code,
841 CASE -- order of conditions matters
842 WHEN items.unit_volume IS NULL THEN
843 -- Volume not setup. Acceptable.
844 NULL
845 WHEN items.vol_rep_uom_code = C_DUMMY_UOM_CODE THEN
846 -- Volume reporting UOM not set up. Acceptable
847 NULL
848 WHEN vol_conv.conversion_rate IS NULL THEN
849 -- Row created from pure outer join i.e. it is a valid
850 -- combination with non-null UOMs and there is
851 -- no conv. rate for this combination.
852 -- From_uom_code and rep_uom_code
853 -- will be null in the vol_conv table for this row also.
854 C_CONV_NOT_SETUP
855 WHEN vol_conv.conversion_rate < 0 THEN
856 -- Error found in conversion rates table.
857 C_CONV_NOT_SETUP
858 WHEN vol_conv.conversion_rate >= 0 THEN
859 -- Valid conv. rate found.
860 -- Note: allowing conv. rate = 0.
861 vol_conv.conversion_rate
862 ELSE
863 -- Why will we get here? Should really never.
864 C_CONV_NOT_SETUP
865 END volume_conv_rate,
866 vol_conv.conversion_type volume_conv_rate_type,
867 sysdate,
868 sysdate,
869 s_user_id,
870 s_user_id,
871 s_login_id,
872 s_program_id,
873 s_program_login_id,
874 s_program_application_id,
875 s_request_id
876 FROM
877 (SELECT /*+ parallel (conv_items) parallel (item_attr)
878 use_hash (conv_items, item_attr) */
879 DISTINCT
880 conv_items.organization_id,
881 conv_items.inventory_item_id,
882 item_attr.primary_uom_code,
883 nvl (item_attr.weight_uom_code, C_DUMMY_UOM_CODE) weight_uom_code,
884 l_wt_rep_uom_code wt_rep_uom_code,
885 nvl (item_attr.volume_uom_code, C_DUMMY_UOM_CODE) volume_uom_code,
886 l_vol_rep_uom_code vol_rep_uom_code,
887 item_attr.unit_weight,
888 item_attr.unit_volume
889 FROM
890 (SELECT /*+ parallel (fact) parallel (mp)
891 use_hash (fact, mp) */
892 DISTINCT
893 fact.organization_id,
894 fact.inventory_item_id
895 FROM opi_dbi_inv_value_f fact,
896 mtl_parameters mp
897 WHERE mp.wms_enabled_flag = 'Y'
898 AND fact.organization_id = mp.organization_id
899 AND fact.source = C_DISCRETE_ORGS
900 ) conv_items,
901 eni_oltp_item_star item_attr
902 WHERE conv_items.organization_id = item_attr.organization_id
903 AND conv_items.inventory_item_id = item_attr.inventory_item_id
904 ) items,
905 opi_dbi_rep_uom_std_conv_f wt_conv,
906 opi_dbi_rep_uom_std_conv_f vol_conv
907 WHERE items.weight_uom_code = wt_conv.from_uom_code (+)
908 AND items.wt_rep_uom_code = wt_conv.rep_uom_code (+)
909 AND items.volume_uom_code = vol_conv.from_uom_code (+)
910 AND items.vol_rep_uom_code = vol_conv.rep_uom_code (+);
911
912 return;
913
914 EXCEPTION
915
916 -- Just report this in the wrapper. Shouldn't happen ever anyway.
917 WHEN NO_REP_UOMS_DEFINED THEN
918 RAISE;
919
920 WHEN OTHERS THEN
921 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
922 l_stmt_id));
923 RAISE CONV_RATE_CALC_FAILED;
924
925 END compute_conv_rates_init;
926
927
928 /* wt_vol_init_load
929
930 Set up conversion rates in the reporting UOM conversion rates facts
931 for the weight and volume measures.
932
933 The conversion rates are set up for all items in the inventory
934 value fact, OPI_DBI_INV_VALUE_F, for WMS enabled, discrete
935 manufacturing organizations.
936
937 Currently only interested in conversion rates for weights/volumes.
938
939 History:
940 Date Author Action
941 12/13/04 Dinkar Gupta Wrote Function.
942
943 */
944 PROCEDURE wt_vol_init_load (errbuf OUT NOCOPY VARCHAR2,
945 retcode OUT NOCOPY NUMBER)
946 IS
947
948 l_proc_name CONSTANT VARCHAR2 (40) := 'wt_vol_init_load';
949 l_stmt_id NUMBER;
950
951 -- Check if missing conversion rates
952 l_missing_rates BOOLEAN;
953
954 -- Run date of the program
955 l_run_date DATE;
956
957 BEGIN
958
959 -- Initialization block.
960 l_stmt_id := 0;
961 l_missing_rates := FALSE;
962
963 l_stmt_id := 5;
964 -- Capture the running start time of the program
965 l_run_date := sysdate;
966 print_stage_done_mesg (l_proc_name, l_stmt_id);
967
968 l_stmt_id := 10;
969 -- Set up the global parameters
970 global_setup ();
971 print_stage_done_mesg (l_proc_name, l_stmt_id);
972
973 l_stmt_id := 20;
974 -- Set up the relevant tables
975 setup_tables_init ();
976 print_stage_done_mesg (l_proc_name, l_stmt_id);
977
978 l_stmt_id := 30;
979 -- Ensure that we only do something if at least one of the
980 -- reporting UOMs is set up. Otherwise just return.
981 IF (s_wt_rep_uom_code IS NULL AND
982 s_vol_rep_uom_code IS NULL) THEN
983 RAISE NO_REP_UOMS_DEFINED;
984 END IF;
985 print_stage_done_mesg (l_proc_name, l_stmt_id);
986
987 l_stmt_id := 40;
988 -- Compute the item specific conversion rates.
989 -- Insert them into the fact table directly.
990 compute_conv_rates_init (s_wt_rep_uom_code, s_vol_rep_uom_code);
991 print_stage_done_mesg (l_proc_name, l_stmt_id);
992
993 l_stmt_id := 50;
994 -- Commit the rates found so far.
995 commit;
996 print_stage_done_mesg (l_proc_name, l_stmt_id);
997
998 l_stmt_id := 60;
999 -- Determine if there are any missing rates, and report them.
1000 l_missing_rates := check_missing_rates ('opi_dbi_wms_stor_item_conv_f');
1001 print_stage_done_mesg (l_proc_name, l_stmt_id);
1002
1003 l_stmt_id := 70;
1004 -- If there are missing rates, transfer the data
1005 -- from the fact to the staging table, and error out.
1006 -- Don't truncate the staging table.
1007 -- The details in the table can be used for detail item
1008 -- level error reporting in the standalone concurrent program,
1009 -- Report Warehouse Storage Utilized/Current Capacity Utilization
1010 -- UOM Conversion Rates Error Details.
1011 IF (l_missing_rates = TRUE) THEN
1012
1013 l_stmt_id := 72;
1014 -- Transfer the fact table to the staging table that is
1015 -- currently empty.
1016 transfer_fact_to_staging;
1017
1018 l_stmt_id := 76;
1019 -- Commit all data by truncating the fact table.
1020 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
1021 'OPI_DBI_WMS_STOR_ITEM_CONV_F');
1022
1023 RAISE MISSING_RATES_FOUND;
1024
1025 END IF;
1026 print_stage_done_mesg (l_proc_name, l_stmt_id);
1027
1028 l_stmt_id := 70;
1029 -- Update the log table
1030 update_log (l_run_date);
1031 print_stage_done_mesg (l_proc_name, l_stmt_id);
1032
1033 l_stmt_id := 80;
1034 -- Commit all data by truncating the staging table.
1035 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
1036 'OPI_DBI_WMS_STOR_ITEM_CONV_STG');
1037 print_stage_done_mesg (l_proc_name, l_stmt_id);
1038
1039 errbuf := '';
1040 retcode := C_SUCCESS;
1041 BIS_COLLECTION_UTILITIES.PUT_LINE (C_SUCCESS_MESG);
1042 return;
1043
1044 EXCEPTION
1045
1046 WHEN GLOBAL_SETUP_MISSING THEN
1047 rollback;
1048 retcode := C_ERROR;
1049 errbuf := C_STOR_INIT_LOAD_ERROR_MESG;
1050
1051 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1052 (GLOBAL_SETUP_MISSING_MESG,
1053 l_proc_name, l_stmt_id));
1054 return;
1055
1056 WHEN TABLE_SETUP_FAILED THEN
1057 rollback;
1058 retcode := C_ERROR;
1059 errbuf := C_STOR_INIT_LOAD_ERROR_MESG;
1060
1061 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1062 (TABLE_SETUP_FAILED_MESG,
1063 l_proc_name, l_stmt_id));
1064 return;
1065
1066 WHEN NO_REP_UOMS_DEFINED THEN
1067 rollback;
1068 retcode := C_WARNING;
1069 errbuf := C_STOR_INIT_LOAD_ERROR_MESG;
1070
1071 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1072 (NO_REP_UOMS_DEFINED_MESG,
1073 l_proc_name, l_stmt_id));
1074 return;
1075
1076 WHEN CONV_RATE_CALC_FAILED THEN
1077 rollback;
1078 retcode := C_ERROR;
1079 errbuf := C_STOR_INIT_LOAD_ERROR_MESG;
1080
1081 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1082 (CONV_RATE_CALC_FAILED_MESG,
1083 l_proc_name, l_stmt_id));
1084 return;
1085
1086 WHEN CONV_RATES_CHECK_FAILED THEN
1087 rollback;
1088 retcode := C_ERROR;
1089 errbuf := C_STOR_INIT_LOAD_ERROR_MESG;
1090
1091 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1092 (CONV_RATES_CHECK_FAILED_MESG,
1093 l_proc_name, l_stmt_id));
1094 return;
1095
1096 WHEN MISSING_RATES_FOUND THEN
1097 rollback;
1098 retcode := C_ERROR;
1099 errbuf := C_STOR_INIT_LOAD_ERROR_MESG;
1100
1101 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1102 (MISSING_RATES_FOUND_MESG,
1103 l_proc_name, l_stmt_id));
1104 return;
1105
1106 WHEN LOG_UPDATE_FAILED THEN
1107 rollback;
1108 retcode := C_ERROR;
1109 errbuf := C_STOR_INIT_LOAD_ERROR_MESG;
1110
1111 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1112 (LOG_UPDATE_FAILED_MESG,
1113 l_proc_name, l_stmt_id));
1114 return;
1115
1116 WHEN TRX_STG_TO_FACT_FAILED THEN
1117 rollback;
1118 retcode := C_ERROR;
1119 errbuf := C_STOR_INIT_LOAD_ERROR_MESG;
1120
1121 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1122 (TRX_STG_TO_FACT_FAILED_MESG,
1123 l_proc_name, l_stmt_id));
1124 return;
1125
1126
1127 WHEN OTHERS THEN
1128 rollback;
1129 retcode := C_ERROR;
1130 errbuf := C_STOR_INIT_LOAD_ERROR_MESG;
1131
1132 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1133 l_stmt_id));
1134
1135 return;
1136
1137 END wt_vol_init_load;
1138
1139
1140 /**************************************************
1141 * Incremental Load functions
1142 **************************************************/
1143
1144 /* setup_tables_incr
1145
1146 Truncate the following tables:
1147 2. opi_dbi_wms_stor_item_conv_stg
1148
1149
1150
1151 History:
1152 Date Author Action
1153 12/13/04 Dinkar Gupta Wrote Function.
1154
1155
1156 */
1157 PROCEDURE setup_tables_incr
1158 IS
1159
1160 l_proc_name CONSTANT VARCHAR2 (40) := 'setup_tables_incr';
1161 l_stmt_id NUMBER;
1162
1163 l_cnt NUMBER;
1164
1165 BEGIN
1166
1167 -- Initialization block
1168 l_stmt_id := 0;
1169 l_cnt := NULL;
1170
1171 l_stmt_id := 10;
1172 -- Truncate the staging table
1173 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
1174 'OPI_DBI_WMS_STOR_ITEM_CONV_STG');
1175
1176 l_stmt_id := 20;
1177 -- Check all rows for this ETL from the log table
1178 SELECT count (1)
1179 INTO l_cnt
1180 FROM opi_dbi_conc_prog_run_log
1181 WHERE etl_type = C_ETL_TYPE;
1182
1183
1184 l_stmt_id := 30;
1185 IF (l_cnt <> 1) THEN
1186 RAISE LAST_RUN_RECORD_MISSING;
1187 END IF;
1188
1189 return;
1190
1191 EXCEPTION
1192
1193 WHEN OTHERS THEN
1194 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1195 l_stmt_id));
1196 RAISE TABLE_SETUP_FAILED;
1197
1198 END setup_tables_incr;
1199
1200 /* compute_conv_rates_incr
1201
1202 Extract the item weight/volume conversions to the corresponding
1203 reporting UOMS. The item information is stored in the inventory value
1204 fact. Only interested in the items of WMS enabled discrete organizations.
1205 (This set of items is generally smaller than all the items for all
1206 organizations).
1207
1208 Collect only for items that have changed since the fact should be
1209 updated as little as possible to maintain effective fast
1210 refreshability of MVs built on top of it.
1211
1212 If the conversion rate for the weight/volume to the reporting UOMs
1213 is not defined, store negative error codes.
1214
1215
1216
1217 Note also that one or both of the reporting units for weight
1218 and volume can be NULL. That is not an error condition.
1219
1220 No committing of data here. The calling function handles that.
1221
1222 Parameters:
1223 1. p_wt_rep_uom_code - Weight reporting UOM code
1224 2. p_vol_rep_uom_code - Volume reporting UOM code
1225
1226 History:
1227 Date Author Action
1228 12/13/04 Dinkar Gupta Wrote Function.
1229
1230 */
1231 PROCEDURE compute_conv_rates_incr (p_wt_rep_uom_code IN VARCHAR2,
1232 p_vol_rep_uom_code IN VARCHAR2)
1233
1234 IS
1235 l_proc_name CONSTANT VARCHAR2 (40) := 'compute_conv_rates_incr';
1236 l_stmt_id NUMBER;
1237
1238 -- local copy of reporting UOMs
1239 l_wt_rep_uom_code VARCHAR2 (3);
1240 l_vol_rep_uom_code VARCHAR2 (3);
1241
1242 BEGIN
1243
1244 -- initialization block
1245 l_stmt_id := 0;
1246 l_wt_rep_uom_code := NULL;
1247 l_vol_rep_uom_code := NULL;
1248
1249 l_stmt_id := 10;
1250 -- If both reporting UOMs are undefined, return. Wrapper routine
1251 -- should ensure this doesn't happen, but check just in case.
1252 IF (p_wt_rep_uom_code IS NULL AND
1253 p_vol_rep_uom_code IS NULL) THEN
1254 RAISE NO_REP_UOMS_DEFINED;
1255 END IF;
1256
1257 l_stmt_id := 20;
1258 -- Set the reporting UOM's to the dummy code, to avoid handling
1259 -- the condition that one of them can be NULL in the SQL below.
1260 l_wt_rep_uom_code := nvl (p_wt_rep_uom_code, C_DUMMY_UOM_CODE);
1261 l_vol_rep_uom_code := nvl (p_vol_rep_uom_code, C_DUMMY_UOM_CODE);
1262
1263 l_stmt_id := 30;
1264 -- Extract the weight/volume conversion rates for all
1265 -- distinct item/org pairs that belong to WMS enabled discrete
1266 -- manufacturing organizations and that are already present in the
1267 -- inventory value fact, OPI_DBI_INV_VALUE_F. Need to specifically
1268 -- filter out process organizations here, because we only
1269 -- join to the inventory value fact which has data from both, discrete
1270 -- and process organizations.
1271 --
1272 -- Collecting 2 measures:
1273 -- 1. Conversion rate from the item weight UOM code to the weight
1274 -- reporting UOM code.
1275 -- 2. Conversion rate from the item volume UOM code to the volume
1276 -- reporting UOM code.
1277 --
1278 -- The item setup form guarantees that a UOM be specified for the
1279 -- item weight/volume before a value is specified. So no need to
1280 -- worry about missing UOMs and specified values.
1281 --
1282 -- All possible conversions to the reporting UOMs are stored in
1283 -- the standard conversion rates table, OPI_DBI_REP_UOM_STD_CONV_F.
1284 -- The extraction program will join twice to this table, once
1285 -- for weight conversions and once for volume conversions.
1286 -- Since certain rows may not join to the conversion fact for
1287 -- one of the measures, use outer joins.
1288 --
1289 -- The following pathalogical scenarios are possible:
1290 --
1291 -- Missing Setups:
1292 -- 1. Item Weight setup is missing.
1293 -- 2. Item Volume setup is missing.
1294 -- Either or both of the above are acceptable. In case of no setup,
1295 -- report NULL for the corresponding measure.
1296 --
1297 -- Missing conversion rates:
1298 -- 1. Item weight cannot be converted into the reporting weight UOM.
1299 -- 2. Item volume cannot be converted into the reporting volume UOM.
1300 -- This will be recorded as a negative number, given the negative
1301 -- error codes for missing rates. Store that for the moment, and
1302 -- report errors later. If no conversion rate is found, record a
1303 -- negative number.
1304 --
1305 -- Missing Reporting UOMs:
1306 -- 1. The Weight Reporting UOM can be missing.
1307 -- 2. The Volume Reporting UOM can be missing.
1308 -- Since both won't be missing, these are acceptable scenarios i.e.
1309 -- ones where customers do not care about one of the measures. Simply
1310 -- set the conversion rate for that measure to NULL everywhere.
1311 --
1312 -- Weight/Volume UOM can be the same:
1313 -- 1. The item's weight capacity and volume UOM can be the
1314 -- same. This is acceptable. However, suppose this UOM is Kg, that
1315 -- needs to be converted to weight reporting UOM (lbs) and volume
1316 -- reporting UOM M3.
1317 -- Say the conversion rates table has a rate from Kg --> lbs, but
1318 -- nothing from Kg --> M3. To avoid filtering out rows for such
1319 -- items and instead reporting errors for them, this SQL has
1320 -- to be written with an inline view which contains all the
1321 -- from and reporting UOMs, and an outer SQL that purely outer
1322 -- joins to the standard conversion rates tables based on these
1323 -- from and reporting UOMs.
1324 --
1325 --
1326 -- It seems like overkill to compute conversion rates for all
1327 -- item/orgs in the inventory value fact. The reasons for this
1328 -- decision are the following:
1329 -- 1. Having a separate staging table to hold just incremental rows
1330 -- collected by the inventory value fact requires addressing
1331 -- all sorts of synchronization issues between the inventory
1332 -- value and WMS ETLs. Who populates the data? Who truncates the
1333 -- table? What if the two programs are running simultaneously?
1334 -- Should the inventory ETL only populate this table once WMS has
1335 -- been implemented?
1336 --
1337 -- 2. Users could change the weight and/or volume UOM of an item.
1338 -- In that case, it will not be sufficient to look only at new
1339 -- conversion rates that have changed and new items. We would
1340 -- have to look at setups of existing items also. Given the
1341 -- lack of indexes on the last_update_date column on
1342 -- all the tables involved, all such queries would result in
1343 -- full table scans.
1344 -- 3. The inventory fact does not grow terribly quickly. Hence a
1345 -- full scan of distinct item/orgs in it will not be too expensive.
1346 INSERT /*+ append */
1347 INTO opi_dbi_wms_stor_item_conv_stg (
1348 organization_id,
1349 inventory_item_id,
1350 unit_weight_uom_code,
1351 weight_conv_rate,
1352 weight_conv_rate_type,
1353 unit_volume_uom_code,
1354 volume_conv_rate,
1355 volume_conv_rate_type)
1356 SELECT /*+ use_hash (all_rates, existing) */
1357 all_rates.organization_id,
1358 all_rates.inventory_item_id,
1359 all_rates.unit_weight_uom_code,
1360 all_rates.weight_conv_rate,
1361 all_rates.weight_conv_rate_type,
1362 all_rates.unit_volume_uom_code,
1363 all_rates.volume_conv_rate,
1364 all_rates.volume_conv_rate_type
1365 FROM
1366 (SELECT /*+ use_hash (items, wt_conv, vol_conv) */
1367 items.organization_id,
1368 items.inventory_item_id,
1369 decode (items.weight_uom_code,
1370 C_DUMMY_UOM_CODE, NULL,
1371 items.weight_uom_code) unit_weight_uom_code,
1372 CASE -- order of conditions matters
1373 WHEN items.unit_weight IS NULL THEN
1374 -- Weight not setup. Acceptable.
1375 NULL
1376 WHEN items.wt_rep_uom_code = C_DUMMY_UOM_CODE THEN
1377 -- Weight reporting UOM not set up. Acceptable
1378 NULL
1379 WHEN wt_conv.conversion_rate IS NULL THEN
1380 -- Row created from pure outer join i.e. it is a valid
1381 -- combination with non-null UOMs and there is
1382 -- no conv. rate for this combination.
1383 -- From_uom_code and rep_uom_code
1384 -- will be null in the wt_conv table for this row also.
1385 C_CONV_NOT_SETUP
1386 WHEN wt_conv.conversion_rate < 0 THEN
1387 -- Error found in conversion rates table.
1388 C_CONV_NOT_SETUP
1389 WHEN wt_conv.conversion_rate >= 0 THEN
1390 -- Valid conv. rate found.
1391 -- Note: allowing conv. rate = 0.
1392 wt_conv.conversion_rate
1393 ELSE
1394 -- Why will we get here? Should really never.
1395 C_CONV_NOT_SETUP
1396 END weight_conv_rate,
1397 wt_conv.conversion_type weight_conv_rate_type,
1398 decode (items.volume_uom_code,
1399 C_DUMMY_UOM_CODE, NULL,
1400 items.volume_uom_code) unit_volume_uom_code,
1401 CASE -- order of conditions matters
1402 WHEN items.unit_volume IS NULL THEN
1403 -- Volume not setup. Acceptable.
1404 NULL
1405 WHEN items.vol_rep_uom_code = C_DUMMY_UOM_CODE THEN
1406 -- Volume reporting UOM not set up. Acceptable
1407 NULL
1408 WHEN vol_conv.conversion_rate IS NULL THEN
1409 -- Row created from pure outer join i.e. it is a valid
1410 -- combination with non-null UOMs and there is
1411 -- no conv. rate for this combination.
1412 -- From_uom_code and rep_uom_code
1413 -- will be null in the vol_conv table for this row also.
1414 C_CONV_NOT_SETUP
1415 WHEN vol_conv.conversion_rate < 0 THEN
1416 -- Error found in conversion rates table.
1417 C_CONV_NOT_SETUP
1418 WHEN vol_conv.conversion_rate >= 0 THEN
1419 -- Valid conv. rate found.
1420 -- Note: allowing conv. rate = 0.
1421 vol_conv.conversion_rate
1422 ELSE
1423 -- Why will we get here? Should really never.
1424 C_CONV_NOT_SETUP
1425 END volume_conv_rate,
1426 vol_conv.conversion_type volume_conv_rate_type
1427 FROM
1428 (SELECT /*+ use_hash (conv_items, item_attr) */
1429 conv_items.organization_id,
1430 conv_items.inventory_item_id,
1431 item_attr.primary_uom_code,
1432 nvl (item_attr.weight_uom_code, C_DUMMY_UOM_CODE) weight_uom_code,
1433 l_wt_rep_uom_code wt_rep_uom_code,
1434 nvl (item_attr.volume_uom_code, C_DUMMY_UOM_CODE) volume_uom_code,
1435 l_vol_rep_uom_code vol_rep_uom_code,
1436 item_attr.unit_weight,
1437 item_attr.unit_volume
1438 FROM
1439 (SELECT /*+ use_hash (fact, mp) */
1440 DISTINCT
1441 fact.organization_id,
1442 fact.inventory_item_id
1443 FROM opi_dbi_inv_value_f fact,
1444 mtl_parameters mp
1445 WHERE mp.wms_enabled_flag = 'Y'
1446 AND fact.organization_id = mp.organization_id
1447 AND fact.source = C_DISCRETE_ORGS
1448 ) conv_items,
1449 eni_oltp_item_star item_attr
1450 WHERE conv_items.organization_id = item_attr.organization_id
1451 AND conv_items.inventory_item_id = item_attr.inventory_item_id
1452 ) items,
1453 opi_dbi_rep_uom_std_conv_f wt_conv,
1454 opi_dbi_rep_uom_std_conv_f vol_conv
1455 WHERE items.weight_uom_code = wt_conv.from_uom_code (+)
1456 AND items.wt_rep_uom_code = wt_conv.rep_uom_code (+)
1457 AND items.volume_uom_code = vol_conv.from_uom_code (+)
1458 AND items.vol_rep_uom_code = vol_conv.rep_uom_code (+)
1459 ) all_rates,
1460 opi_dbi_wms_stor_item_conv_f existing
1461 WHERE all_rates.organization_id = existing.organization_id (+)
1462 AND all_rates.inventory_item_id = existing.inventory_item_id (+)
1463 AND ( ( existing.organization_id IS NULL
1464 AND existing.inventory_item_id IS NULL )
1465 OR nvl (all_rates.unit_weight_uom_code, C_DUMMY_UOM_CODE) <>
1466 nvl (existing.unit_weight_uom_code, C_DUMMY_UOM_CODE)
1467 OR nvl (all_rates.weight_conv_rate, -1) <>
1468 nvl (existing.weight_conv_rate, -1)
1469 OR nvl (all_rates.weight_conv_rate_type, -1) <>
1470 nvl (existing.weight_conv_rate_type, -1)
1471 OR nvl (all_rates.unit_volume_uom_code, C_DUMMY_UOM_CODE) <>
1472 nvl (existing.unit_volume_uom_code, C_DUMMY_UOM_CODE)
1473 OR nvl (all_rates.volume_conv_rate, -1)<>
1474 nvl (existing.volume_conv_rate, -1)
1475 OR nvl (all_rates.volume_conv_rate_type, -1) <>
1476 nvl (existing.volume_conv_rate_type, -1)
1477 );
1478
1479 return;
1480
1481 EXCEPTION
1482
1483 -- Just report this in the wrapper. Shouldn't happen ever anyway.
1484 WHEN NO_REP_UOMS_DEFINED THEN
1485 RAISE;
1486
1487 WHEN OTHERS THEN
1488 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1489 l_stmt_id));
1490 RAISE CONV_RATE_CALC_FAILED;
1491
1492 END compute_conv_rates_incr;
1493
1494
1495 /* merge_rates_into_fact
1496
1497 Merge all the rates computed in the staging table for the incremental
1498 load into the fact table.
1499
1500 The staging table only has those records that have been modified
1501 between incremental loads, or are brand new.
1502
1503 History:
1504 Date Author Action
1505 12/13/04 Dinkar Gupta Wrote Function.
1506 */
1507 PROCEDURE merge_rates_into_fact
1508 IS
1509
1510 l_proc_name CONSTANT VARCHAR2 (40) := 'merge_rates_into_fact';
1511 l_stmt_id NUMBER;
1512
1513 BEGIN
1514
1515 -- Initialization block
1516 l_stmt_id := 0;
1517
1518 l_stmt_id := 10;
1519 -- Merge all data into the fact table.
1520 MERGE INTO opi_dbi_wms_stor_item_conv_f base
1521 USING
1522 (SELECT
1523 organization_id,
1524 inventory_item_id,
1525 unit_weight_uom_code,
1526 weight_conv_rate,
1527 weight_conv_rate_type,
1528 unit_volume_uom_code,
1529 volume_conv_rate,
1530 volume_conv_rate_type,
1531 sysdate creation_date,
1532 sysdate last_update_date,
1533 s_user_id created_by,
1534 s_user_id last_updated_by,
1535 s_login_id last_update_login,
1536 s_program_id program_id,
1537 s_program_login_id program_login_id,
1538 s_program_application_id program_application_id,
1539 s_request_id request_id
1540 FROM opi_dbi_wms_stor_item_conv_stg) new
1541 ON ( base.organization_id = new.organization_id
1542 AND base.inventory_item_id = new.inventory_item_id)
1543 WHEN MATCHED THEN UPDATE
1544 SET
1545 base.unit_weight_uom_code = new.unit_weight_uom_code,
1546 base.weight_conv_rate = new.weight_conv_rate,
1547 base.weight_conv_rate_type = new.weight_conv_rate_type,
1548 base.unit_volume_uom_code = new.unit_volume_uom_code,
1549 base.volume_conv_rate = new.volume_conv_rate,
1550 base.volume_conv_rate_type = new.volume_conv_rate_type,
1551 base.last_update_date = new.last_update_date,
1552 base.last_updated_by = new.last_updated_by,
1553 base.last_update_login = new.last_update_login,
1554 base.program_id = new.program_id,
1555 base.program_login_id = new.program_login_id,
1556 base.program_application_id = new.program_application_id,
1557 base.request_id = new.request_id
1558 WHEN NOT MATCHED THEN
1559 INSERT (
1560 organization_id,
1561 inventory_item_id,
1562 unit_weight_uom_code,
1563 weight_conv_rate,
1564 weight_conv_rate_type,
1565 unit_volume_uom_code,
1566 volume_conv_rate,
1567 volume_conv_rate_type,
1568 creation_date,
1569 last_update_date,
1570 created_by,
1571 last_updated_by,
1572 last_update_login,
1573 program_id,
1574 program_login_id,
1575 program_application_id,
1576 request_id)
1577 VALUES (
1578 new.organization_id,
1579 new.inventory_item_id,
1580 new.unit_weight_uom_code,
1581 new.weight_conv_rate,
1582 new.weight_conv_rate_type,
1583 new.unit_volume_uom_code,
1584 new.volume_conv_rate,
1585 new.volume_conv_rate_type,
1586 new.creation_date,
1587 new.last_update_date,
1588 new.created_by,
1589 new.last_updated_by,
1590 new.last_update_login,
1591 new.program_id,
1592 new.program_login_id,
1593 new.program_application_id,
1594 new.request_id);
1595
1596 return;
1597
1598 EXCEPTION
1599
1600 WHEN OTHERS THEN
1601
1602 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1603 l_stmt_id));
1604 RAISE MERGE_RATES_FAILED;
1605
1606
1607 END merge_rates_into_fact;
1608
1609 /* wt_vol_incr_load
1610
1611 Set up conversion rates in the reporting UOM conversion rates facts
1612 for the weight and volume measures.
1613
1614 The conversion rates are set up for all items in the inventory
1615 value fact, OPI_DBI_INV_VALUE_F, for WMS enabled, discrete
1616 manufacturing organizations.
1617
1618 Currently only interested in conversion rates for weights/volumes.
1619
1620 During the incremental load, consider only those items/orgs that
1621 have been added during incremental runs of the inventory program
1622 after the previous run of the WMS conversion rates program.
1623
1624 History:
1625 Date Author Action
1626 12/13/04 Dinkar Gupta Wrote Function.
1627
1628 */
1629 PROCEDURE wt_vol_incr_load (errbuf OUT NOCOPY VARCHAR2,
1630 retcode OUT NOCOPY NUMBER)
1631 IS
1632
1633 l_proc_name CONSTANT VARCHAR2 (40) := 'wt_vol_incr_load';
1634 l_stmt_id NUMBER;
1635
1636 -- Check if missing conversion rates
1637 l_missing_rates BOOLEAN;
1638
1639 -- Run date of the program
1640 l_run_date DATE;
1641
1642 BEGIN
1643
1644 -- Initialization block.
1645 l_stmt_id := 0;
1646 l_missing_rates := FALSE;
1647
1648 l_stmt_id := 5;
1649 -- Capture the running start time of the program
1650 l_run_date := sysdate;
1651 print_stage_done_mesg (l_proc_name, l_stmt_id);
1652
1653 l_stmt_id := 10;
1654 -- Set up the global parameters
1655 global_setup ();
1656 print_stage_done_mesg (l_proc_name, l_stmt_id);
1657
1658 l_stmt_id := 20;
1659 -- Set up the relevant tables
1660 setup_tables_incr ();
1661 print_stage_done_mesg (l_proc_name, l_stmt_id);
1662
1663 l_stmt_id := 30;
1664 -- Ensure that we only do something if at least one of the
1665 -- reporting UOMs is set up. Otherwise just return.
1666 IF (s_wt_rep_uom_code IS NULL AND
1667 s_vol_rep_uom_code IS NULL) THEN
1668 RAISE NO_REP_UOMS_DEFINED;
1669 END IF;
1670 print_stage_done_mesg (l_proc_name, l_stmt_id);
1671
1672 l_stmt_id := 40;
1673 -- Compute the item specific conversion rates.
1674 -- Insert them into the staging table.
1675 compute_conv_rates_incr (s_wt_rep_uom_code, s_vol_rep_uom_code);
1676 print_stage_done_mesg (l_proc_name, l_stmt_id);
1677
1678 l_stmt_id := 50;
1679 -- Commit data to the staging table.
1680 commit;
1681 print_stage_done_mesg (l_proc_name, l_stmt_id);
1682
1683 -- Check for missing rates.
1684 l_stmt_id := 60;
1685 l_missing_rates := check_missing_rates ('opi_dbi_wms_stor_item_conv_stg');
1686 print_stage_done_mesg (l_proc_name, l_stmt_id);
1687
1688 l_stmt_id := 70;
1689 -- If there are missing rates commit the staging table
1690 -- and error out.
1691 IF (l_missing_rates = TRUE) THEN
1692
1693 l_stmt_id := 74;
1694 -- Don't truncate the staging table.
1695 -- The details in the table can be used for detail item
1696 -- level error reporting in the standalone concurrent program,
1697 -- Report Warehouse Storage Utilized/Current Capacity Utilization
1698 -- UOM Conversion Rates Error Details.
1699 commit;
1700
1701 RAISE MISSING_RATES_FOUND;
1702
1703 END IF;
1704 print_stage_done_mesg (l_proc_name, l_stmt_id);
1705
1706 l_stmt_id := 80;
1707 -- Merge all rates into the conversion rates fact table.
1708 merge_rates_into_fact ();
1709 print_stage_done_mesg (l_proc_name, l_stmt_id);
1710
1711 l_stmt_id := 90;
1712 -- Update log table with run date of this program.
1713 update_log (l_run_date);
1714 print_stage_done_mesg (l_proc_name, l_stmt_id);
1715
1716 l_stmt_id := 100;
1717 -- Commit all data by truncating the staging table.
1718 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
1719 'OPI_DBI_WMS_STOR_ITEM_CONV_STG');
1720 print_stage_done_mesg (l_proc_name, l_stmt_id);
1721
1722 errbuf := '';
1723 retcode := C_SUCCESS;
1724 BIS_COLLECTION_UTILITIES.PUT_LINE (C_SUCCESS_MESG);
1725 return;
1726
1727 EXCEPTION
1728
1729 WHEN GLOBAL_SETUP_MISSING THEN
1730 rollback;
1731 retcode := C_ERROR;
1732 errbuf := C_STOR_INCR_LOAD_ERROR_MESG;
1733
1734 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1735 (GLOBAL_SETUP_MISSING_MESG,
1736 l_proc_name, l_stmt_id));
1737 return;
1738
1739 WHEN TABLE_SETUP_FAILED THEN
1740 rollback;
1741 retcode := C_ERROR;
1742 errbuf := C_STOR_INCR_LOAD_ERROR_MESG;
1743
1744 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1745 (TABLE_SETUP_FAILED_MESG,
1746 l_proc_name, l_stmt_id));
1747 return;
1748
1749 WHEN LAST_RUN_RECORD_MISSING THEN
1750 rollback;
1751 retcode := C_ERROR;
1752 errbuf := C_STOR_INCR_LOAD_ERROR_MESG;
1753
1754 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1755 (LAST_RUN_RECORD_MISSING_MESG,
1756 l_proc_name, l_stmt_id));
1757 return;
1758
1759 WHEN LOG_UPDATE_FAILED THEN
1760 rollback;
1761 retcode := C_ERROR;
1762 errbuf := C_STOR_INCR_LOAD_ERROR_MESG;
1763
1764 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1765 (LOG_UPDATE_FAILED_MESG,
1766 l_proc_name, l_stmt_id));
1767 return;
1768
1769 WHEN NO_REP_UOMS_DEFINED THEN
1770 rollback;
1771 retcode := C_WARNING;
1772 errbuf := C_STOR_INCR_LOAD_ERROR_MESG;
1773
1774 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1775 (NO_REP_UOMS_DEFINED_MESG,
1776 l_proc_name, l_stmt_id));
1777 return;
1778
1779 WHEN CONV_RATE_CALC_FAILED THEN
1780 rollback;
1781 retcode := C_ERROR;
1782 errbuf := C_STOR_INCR_LOAD_ERROR_MESG;
1783
1784 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1785 (CONV_RATE_CALC_FAILED_MESG,
1786 l_proc_name, l_stmt_id));
1787 return;
1788
1789 WHEN CONV_RATES_CHECK_FAILED THEN
1790 rollback;
1791 retcode := C_ERROR;
1792 errbuf := C_STOR_INCR_LOAD_ERROR_MESG;
1793
1794 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1795 (CONV_RATES_CHECK_FAILED_MESG,
1796 l_proc_name, l_stmt_id));
1797 return;
1798
1799 WHEN MISSING_RATES_FOUND THEN
1800 rollback;
1801 retcode := C_ERROR;
1802 errbuf := C_STOR_INCR_LOAD_ERROR_MESG;
1803
1804 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1805 (MISSING_RATES_FOUND_MESG,
1806 l_proc_name, l_stmt_id));
1807 return;
1808
1809 WHEN MERGE_RATES_FAILED THEN
1810 rollback;
1811 retcode := C_ERROR;
1812 errbuf := C_STOR_INCR_LOAD_ERROR_MESG;
1813
1814 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1815 (MERGE_RATES_FAILED_MESG,
1816 l_proc_name, l_stmt_id));
1817 return;
1818
1819 WHEN OTHERS THEN
1820 rollback;
1821 retcode := C_ERROR;
1822 errbuf := C_STOR_INCR_LOAD_ERROR_MESG;
1823
1824 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1825 l_stmt_id));
1826
1827 return;
1828
1829 END wt_vol_incr_load;
1830
1831
1832
1833 END opi_dbi_wms_storage_utz_pkg;
[Home] [Help]