1 PACKAGE BODY opi_dbi_wms_capacity_utz_pkg AS
2 /*$Header: OPIDEWCUTZB.pls 120.0 2005/05/24 18:25:37 appldev 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 -- Truncate/sets up relevant tables
48 PROCEDURE setup_tables;
49
50
51 /**************************************************
52 * Warehouse Capacity Related procedures
53 **************************************************/
54
55 -- Extract locator capacities into the subinventory staging table
56 PROCEDURE extract_locator_capacities (p_wt_rep_uom_code IN VARCHAR2,
57 p_vol_rep_uom_code IN VARCHAR2);
58
59 -- Summarize the locator capacities to the subinventory and
60 -- organization levels.
61 PROCEDURE summarize_capacities;
62
63 -- Report missing rates/errors
64 FUNCTION check_locator_setup_errors
65 RETURN BOOLEAN;
66
67 /**************************************************
68 * Item Storage Related procedures
69 **************************************************/
70 -- Extract item storage details into the item fact table
71 PROCEDURE extract_item_storage (p_wt_rep_uom_code IN VARCHAR2,
72 p_vol_rep_uom_code IN VARCHAR2);
73
74
75 -- Report missing rates/errors
76 FUNCTION check_item_setup_errors
77 RETURN BOOLEAN;
78
79 /**************************************************
80 * Common Procedures Definitions
81 **************************************************/
82
83 /* global_setup
84
85 Performs global setup of file scope variables and does any checking
86 needed for global DBI setups.
87
88 Parameters: None
89
90 History:
91 Date Author Action
92 12/01/04 Dinkar Gupta Defined function.
93
94 */
95 PROCEDURE global_setup
96 IS
97 l_proc_name CONSTANT VARCHAR2 (40) := 'global_setup';
98 l_stmt_id NUMBER;
99
100
101 -- Cursor to get the reporting UOM for a certain measure code.
102 -- Can be null if not set up.
103 CURSOR get_rep_uom_csr (p_measure_code VARCHAR2)
104 IS
105 SELECT rep_uom_code
106 FROM opi_dbi_rep_uoms
107 WHERE measure_code = p_measure_code;
108
109 BEGIN
110
111 -- Initialization block
112 l_stmt_id := 0;
113
114 l_stmt_id := 10;
115 -- Obtain the OPI schema name to allow truncation of various tables
116 -- get session parameters
117 IF (NOT (fnd_installation.get_app_info('OPI', s_status, s_industry,
118 s_opi_schema))) THEN
119 RAISE SCHEMA_INFO_NOT_FOUND;
120 END IF;
121
122 l_stmt_id := 20;
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 := 30;
132 -- Get the weight reporting UOM.
133 OPEN get_rep_uom_csr (C_WT_MEASURE_CODE);
134 FETCH get_rep_uom_csr INTO s_wt_rep_uom_code;
135 IF (get_rep_uom_csr%NOTFOUND) THEN
136 s_wt_rep_uom_code := NULL;
137 END IF;
138
139 CLOSE get_rep_uom_csr;
140
141 l_stmt_id := 40;
142 -- Get the volume reporting UOM.
143 OPEN get_rep_uom_csr (C_VOL_MEASURE_CODE);
144 FETCH get_rep_uom_csr INTO s_vol_rep_uom_code;
145 IF (get_rep_uom_csr%NOTFOUND) THEN
146 s_vol_rep_uom_code := NULL;
147 END IF;
148
149 CLOSE get_rep_uom_csr;
150
151 return;
152
153 EXCEPTION
154
155 WHEN SCHEMA_INFO_NOT_FOUND THEN
156
157 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
158 (SCHEMA_INFO_NOT_FOUND_MESG,
159 l_proc_name, l_stmt_id));
160 RAISE GLOBAL_SETUP_MISSING;
161
162
163 WHEN OTHERS THEN
164 rollback;
165
166 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
167 l_stmt_id));
168 RAISE GLOBAL_SETUP_MISSING;
169
170 END global_setup;
171
172
173
174 /* err_mesg
175
176 Return a C_ERRBUF_SIZE character long, properly formatted error
177 message with the package name, procedure name and message.
178
179 Parameters:
180 p_mesg - Actual message to be printed
181 p_proc_name - name of procedure that should be printed in the message
182 (optional)
183 p_stmt_id - step in procedure at which error occurred
184 (optional)
185
186 History:
187 Date Author Action
188 12/08/04 Dinkar Gupta Defined function.
189 */
190
191 FUNCTION err_mesg (p_mesg IN VARCHAR2,
192 p_proc_name IN VARCHAR2,
193 p_stmt_id IN NUMBER)
194 RETURN VARCHAR2
195 IS
196
197 l_proc_name CONSTANT VARCHAR2 (60) := 'err_mesg';
198 l_stmt_id NUMBER;
199
200 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
201 -- as the size of the declaration. I have to put 300 here.
202 l_formatted_message VARCHAR2 (300);
203
204 BEGIN
205
206 -- initialization block
207 l_stmt_id := 0;
208
209 -- initialization block
210 l_formatted_message := NULL;
211
212 l_stmt_id := 10;
213 l_formatted_message := substr ((C_PKG_NAME || '.' || p_proc_name || ' #' ||
214 to_char (p_stmt_id) || ': ' || p_mesg),
215 1, C_ERRBUF_SIZE);
216
217 commit;
218
219 return l_formatted_message;
220
221 EXCEPTION
222
223 WHEN OTHERS THEN
224 -- the exception happened in the exception reporting function !!
225 -- return with ERROR.
226 l_formatted_message := substr ((C_PKG_NAME || '.' || l_proc_name ||
227 ' #' ||
228 to_char (l_stmt_id) || ': ' ||
229 SQLERRM),
230 1, C_ERRBUF_SIZE);
231
232 l_formatted_message := 'Error in error reporting.';
233 return l_formatted_message;
234
235 END err_mesg;
236
237 /* print_stage_done_mesg
238
239 Print a message of 'Done' for whatever procedure/statement called.
240
241 Parameters:
242 p_proc_name - name of procedure that should be printed in the message
243 p_stmt_id - step in procedure at which error occurred
244
245 History:
246 Date Author Action
247 12/13/04 Dinkar Gupta Defined function.
248 */
249
250 PROCEDURE print_stage_done_mesg (p_proc_name IN VARCHAR2,
251 p_stmt_id IN NUMBER)
252 IS
253
254 l_proc_name CONSTANT VARCHAR2 (60) := 'print_stage_done_mesg';
255 l_stmt_id NUMBER;
256
257 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
258 -- as the size of the declaration. I have to put 300 here.
259 l_formatted_message VARCHAR2 (300);
260
261 BEGIN
262
263 -- initialization block
264 l_stmt_id := 0;
265
266 -- initialization block
267 l_formatted_message := NULL;
268
269 l_stmt_id := 10;
270 l_formatted_message := substr ((C_PKG_NAME || '.' || p_proc_name || ' #' ||
271 to_char (p_stmt_id) || ': ' || 'Done.'),
272 1, C_ERRBUF_SIZE);
273
274 BIS_COLLECTION_UTILITIES.PUT_LINE (l_formatted_message);
275
276 return;
277
278 EXCEPTION
279
280 WHEN OTHERS THEN
281 -- the exception happened in the print function
282 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
283 l_stmt_id));
284
285 RAISE; -- on to wrapper
286
287 END print_stage_done_mesg;
288
289 /* setup_tables
290
291 Clean up tables related to the Current Capacity Utilization report.
292 Currently, only need to truncate:
293 1. opi_dbi_wms_curr_utz_sub_stg
294 2. opi_dbi_wms_curr_utz_sub_f
295 1. opi_dbi_wms_curr_utz_item_f
296
297 History:
298 Date Author Action
299 12/08/04 Dinkar Gupta Wrote Function.
300 */
301 PROCEDURE setup_tables
302 IS
303
304 l_proc_name CONSTANT VARCHAR2 (40) := 'setup_tables';
305 l_stmt_id NUMBER;
306
307 BEGIN
308
309 -- Initialization block
310 l_stmt_id := 0;
311
312 l_stmt_id := 10;
313 -- Truncate OPI_DBI_WMS_CURR_UTZ_SUB_STG
314 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
315 'OPI_DBI_WMS_CURR_UTZ_SUB_STG');
316
317 l_stmt_id := 20;
318 -- Truncate OPI_DBI_WMS_CURR_UTZ_SUB_F
319 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
320 'OPI_DBI_WMS_CURR_UTZ_SUB_F');
321
322 l_stmt_id := 30;
323 -- Truncate OPI_DBI_WMS_CURR_UTZ_ITEM_F
324 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
325 'OPI_DBI_WMS_CURR_UTZ_ITEM_F');
326
327 return;
328
329 EXCEPTION
330
331 WHEN OTHERS THEN
332 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
333 l_stmt_id));
334 RAISE TABLE_SETUP_FAILED;
335
336 END setup_tables;
337
338
339 /* refresh_current_utilization
340
341 Wrapper routine to refresh
342 1. The weight/volume capacity of warehouses and their subinventories.
343 2. The weight and volume of items stored currently in the
344 warehouse's locators.
345
346 Locator capacities:
347 A locator may have a defined max weight and/or max volume capacity.
348 Locators with neither values defined are ignored. Locators with
349 only one of the quantities defined contribute to the corresponding
350 measure for the subinventory to which they belong.
351
352 Item Weights/volume:
353 The item weight will be taken into account only if the locator
354 it is present in has a defined weight capacity. Similar condition
355 for the item volume. Item quantities present in locators with
356 neither weight nor volume capacity defined are ignored.
357
358 Errors are reported for all:
359 1. Defined locator weight/volume capacities that cannot be
360 converted to the weight/volume reporting UOM values.
361 2. Defined item weights/volumes that cannot be converted
362 into the weight/volume reporting UOM values.
363
364 No data is collected for the report in case of errors, and the fact
365 tables are left truncated.
366
367 Warnings need to be generated for:
368 1. All locators whose unit weight/volume capacities are undefined.
369 2. All items whose unit weight/volume are undefined.
370 Warnings are not generated here for performance reasons mainly.
371 However there is a performance reason for it also, namely that if
372 a user does not set up some item weight/volumes or locator capacities,
373 reporting warnings in this program will always cause termination with
374 warnings. A separate concurrent program will be provided with the
375 explicit task of reporting setup warnings.
376
377 Parameters:
378 errbuf - error message on unsuccessful termination
379 retcode - 0 on success, 1 on warning, -1 on error
380
381 History:
382 Date Author Action
383 12/08/04 Dinkar Gupta Wrote Function.
384 */
385 PROCEDURE refresh_current_utilization (errbuf OUT NOCOPY VARCHAR2,
386 retcode OUT NOCOPY NUMBER)
387 IS
388
389 l_proc_name CONSTANT VARCHAR2 (40) := 'refresh_current_utilization';
390 l_stmt_id NUMBER;
391
392 -- Booleans to track errors
393 l_locator_error BOOLEAN;
394 l_item_error BOOLEAN;
395
396 BEGIN
397
398 -- Initialization Block
399 l_stmt_id := 10;
400 l_locator_error := FALSE;
401 l_item_error := FALSE;
402
403 l_stmt_id := 10;
404 -- Set up the global parameters
405 global_setup ();
406 print_stage_done_mesg (l_proc_name, l_stmt_id);
407
408 l_stmt_id := 20;
409 -- Set up the relevant tables
410 setup_tables ();
411 print_stage_done_mesg (l_proc_name, l_stmt_id);
412
413 l_stmt_id := 30;
414 -- Ensure that we only do something if at least one of the
415 -- reporting UOMs is set up. Otherwise just return.
416 IF (s_wt_rep_uom_code IS NULL AND
417 s_vol_rep_uom_code IS NULL) THEN
418 RAISE NO_REP_UOMS_DEFINED;
419 END IF;
420 print_stage_done_mesg (l_proc_name, l_stmt_id);
421
422 l_stmt_id := 40;
423 -- Capture Locator capacity info into the staging table.
424 -- Errors will be reported off this table for starters.
425 extract_locator_capacities (s_wt_rep_uom_code, s_vol_rep_uom_code);
426 print_stage_done_mesg (l_proc_name, l_stmt_id);
427
428 l_stmt_id := 50;
429 -- Commit the information inserted into the staging table.
430 commit;
431 print_stage_done_mesg (l_proc_name, l_stmt_id);
432
433 l_stmt_id := 60;
434 -- Extract all the item level information into the item fact using
435 -- the locator capacities in the staging table to determine if items
436 -- need to be ignored.
437 extract_item_storage (s_wt_rep_uom_code, s_vol_rep_uom_code);
438 print_stage_done_mesg (l_proc_name, l_stmt_id);
439
440 l_stmt_id := 70;
441 -- Commit all item level information extracted so far.
442 commit;
443 print_stage_done_mesg (l_proc_name, l_stmt_id);
444
445 l_stmt_id := 80;
446 -- Report errors in reporting UOM conversions for the locator staging
447 -- table. Track if any errors occurred.
448 l_locator_error := check_locator_setup_errors ();
449 print_stage_done_mesg (l_proc_name, l_stmt_id);
450
451 l_stmt_id := 90;
452 -- Report errors in reporting UOM conversions for the item weight/
453 -- volume in the item fact. Track if any errors occurred.
454 l_item_error := check_item_setup_errors ();
455 print_stage_done_mesg (l_proc_name, l_stmt_id);
456
457 l_stmt_id := 100;
458 -- If any errors occurred, abort after cleaning out all the tables.
459 IF (l_locator_error OR l_item_error) THEN
460
461 -- Don't truncate the locator capacity staging table.
462 -- The details in the table can be used for detail locator
463 -- level error reporting in the standalone concurrent program,
464 -- Report Warehouse Storage Utilized/Current Capacity Utilization
465 -- UOM Conversion Rates Error Details.
466 commit;
467
468 -- exit
469 RAISE PREMATURE_ABORT;
470
471 END IF;
472 print_stage_done_mesg (l_proc_name, l_stmt_id);
473
474 l_stmt_id := 110;
475 -- If got here, then no errors have occurred. So move all the
476 -- locator data into the subinventory fact table.
477 summarize_capacities ();
478 print_stage_done_mesg (l_proc_name, l_stmt_id);
479
480 l_stmt_id := 120;
481 -- Commit all data
482 Commit;
483 print_stage_done_mesg (l_proc_name, l_stmt_id);
484
485 l_stmt_id := 130;
486 -- Everything successful so commit and truncate the staging table.
487 commit;
488 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
489 'OPI_DBI_WMS_CURR_UTZ_SUB_STG');
490 print_stage_done_mesg (l_proc_name, l_stmt_id);
491
492 errbuf := '';
493 retcode := C_SUCCESS;
494 BIS_COLLECTION_UTILITIES.PUT_LINE (C_SUCCESS_MESG);
495 return;
496
497 EXCEPTION
498
499 WHEN GLOBAL_SETUP_MISSING THEN
500 rollback;
501 retcode := C_ERROR;
502 errbuf := C_CURR_UTZ_LOAD_ERROR_MESG;
503
504 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
505 (GLOBAL_SETUP_MISSING_MESG,
506 l_proc_name, l_stmt_id));
507 return;
508
509 WHEN TABLE_SETUP_FAILED THEN
510 rollback;
511 retcode := C_ERROR;
512 errbuf := C_CURR_UTZ_LOAD_ERROR_MESG;
513
514 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
515 (TABLE_SETUP_FAILED_MESG,
516 l_proc_name, l_stmt_id));
517 return;
518
519 WHEN LOCATOR_CAP_CALC_FAILED THEN
520 rollback;
521 retcode := C_ERROR;
522 errbuf := C_CURR_UTZ_LOAD_ERROR_MESG;
523
524 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
525 (LOCATOR_CAP_CALC_MESG,
526 l_proc_name, l_stmt_id));
527 return;
528
529 WHEN NO_REP_UOMS_DEFINED THEN
530 rollback;
531 retcode := C_WARNING; -- ??? should this be success?
532 errbuf := C_CURR_UTZ_LOAD_WARN_MESG;
533
534 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
535 (NO_REP_UOMS_DEFINED_MESG,
536 l_proc_name, l_stmt_id));
537 return;
538
539 WHEN PREMATURE_ABORT THEN
540 rollback;
541 retcode := C_ERROR;
542 errbuf := C_CURR_UTZ_LOAD_WARN_MESG;
543
544 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
545 (PREMATURE_ABORT_MESG,
546 l_proc_name, l_stmt_id));
547 return;
548
549 WHEN ITEM_STOR_CALC_FAILED THEN
550 rollback;
551 retcode := C_ERROR;
552 errbuf := C_CURR_UTZ_LOAD_ERROR_MESG;
553
554 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
555 (ITEM_STOR_CALC_FAILED_MESG,
556 l_proc_name, l_stmt_id));
557 return;
558
559 WHEN LOCATOR_ERR_CHECK_FAILED THEN
560 rollback;
561 retcode := C_ERROR;
562 errbuf := C_CURR_UTZ_LOAD_ERROR_MESG;
563
564 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
565 (LOCATOR_ERR_CHECK_FAILED_MESG,
566 l_proc_name, l_stmt_id));
567 return;
568
569 WHEN ITEM_ERR_CHECK_FAILED THEN
570 rollback;
571 retcode := C_ERROR;
572 errbuf := C_CURR_UTZ_LOAD_ERROR_MESG;
573
574 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
575 (ITEM_ERR_CHECK_FAILED_MESG,
576 l_proc_name, l_stmt_id));
577 return;
578
579 WHEN OTHERS THEN
580 rollback;
581 retcode := C_ERROR;
582 errbuf := C_CURR_UTZ_LOAD_ERROR_MESG;
583
584 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
585 l_stmt_id));
586
587 return;
588
589 END refresh_current_utilization;
590
591
592 /**************************************************
593 * Warehouse Capacity Related procedures
594 **************************************************/
595
596 /* extract_locator_capacities
597
598 Extract the locator weight and volume capacities in the
599 staging table, OPI_DBI_WMS_CURR_UTZ_SUB_STG. The capacities
600 should be stored in the reporting units of the measures.
601 If the weight or volume capacity is null, store NULL.
602 If the conversion is not defined in the reporting UOM table,
603 OPI_DBI_REP_UOM_STD_CONV_F, store -1.
604
605 Note also that one or both of the reporting units for weight
606 and volume can be NULL. That is not an error condition.
607
608 No committing of data here. The calling function handles that.
609
610 Parameters:
611 1. p_wt_rep_uom_code - Weight reporting UOM code
612 2. p_vol_rep_uom_code - Volume reporting UOM code
613
614 History:
615 Date Author Action
616 12/08/04 Dinkar Gupta Wrote Function.
617
618 */
619 PROCEDURE extract_locator_capacities (p_wt_rep_uom_code IN VARCHAR2,
620 p_vol_rep_uom_code IN VARCHAR2)
621 IS
622 l_proc_name CONSTANT VARCHAR2 (40) := 'extract_locator_capacities';
623 l_stmt_id NUMBER;
624
625 -- local copy of reporting UOMs
626 l_wt_rep_uom_code VARCHAR2 (3);
627 l_vol_rep_uom_code VARCHAR2 (3);
628
629 BEGIN
630
631 -- Initialization block
632 l_stmt_id := 0;
633 l_wt_rep_uom_code := NULL;
634 l_vol_rep_uom_code := NULL;
635
636 l_stmt_id := 10;
637 -- If both reporting UOMs are undefined, return. Wrapper routine
638 -- should ensure this doesn't happen, but check just in case.
639 IF (p_wt_rep_uom_code IS NULL AND
640 p_vol_rep_uom_code IS NULL) THEN
641 RAISE NO_REP_UOMS_DEFINED;
642 END IF;
643
644 l_stmt_id := 20;
645 -- Set the reporting UOM's to the dummy code, to avoid handling
646 -- the condition that one of them can be NULL in the SQL below.
647 l_wt_rep_uom_code := nvl (p_wt_rep_uom_code, C_DUMMY_UOM_CODE);
648 l_vol_rep_uom_code := nvl (p_vol_rep_uom_code, C_DUMMY_UOM_CODE);
649
650 l_stmt_id := 30;
651 -- Compute the weight/volume capacities for locators in warehouse
652 -- enabled organizations. No need to specifically filter out
653 -- process organizations since their locator setup is not
654 -- stored in MTL_ITEM_LOCATIONS.
655 --
656 -- The locator weight capacity, volume_capacity, weight_uom_code
657 -- and volume_uom_code are stored in the MTL_ITEM_LOCATIONS table.
658 --
659 -- All possible conversions to the reporting UOMs are stored in
660 -- the standard conversion rates table, OPI_DBI_REP_UOM_STD_CONV_F.
661 -- The extraction program will join twice to this table, once
662 -- for weight capacity conversions and once for volume capacity
663 -- conversions. Since certain rows may not join to the conversion
664 -- fact for one of the measures, use outer joins.
665 --
666 -- The following are possible pathological scenarios:
667 --
668 -- Capacity Setups:
669 -- 1. Locator Weight capacity (value or UOM) might be missing.
670 -- 2. Locator Volume capacity (value or UOM) might be missing.
671 -- Either of the two above are not cause for error, and the corresponding
672 -- capacity should be stored as NULL.
673 -- The above conditions are detectable as the value of max_weight
674 -- and max_cubic_area in MTL_ITEM_LOCATIONS will be NULL if the setup
675 -- is missing.
676 --
677 -- If both capacities are NULL, then for conciseness don't store a
678 -- row for that locator.
679 --
680 -- Missing Conversions:
681 -- 1. Locator Weight capacity conversion to reporting weight UOM is
682 -- invalid.
683 -- 2. Locator Volume capacity conversion to reporting weight UOM is
684 -- invalid.
685 -- Either of the two above are error conditions and the corresponding
686 -- capacities must therefore be flagged with a -1 so that it can be
687 -- reported.
688 -- The above conditions are detectable as the corresponding conversion
689 -- rates will be negative in the standard conversion rates table,
690 -- OPI_DBI_REP_UOM_STD_CONV_F.
691 --
692 -- Missing Reporting UOMs:
693 -- 1. The Weight Reporting UOM can be missing.
694 -- 2. The Volume Reporting UOM can be missing.
695 -- Since both won't be missing, these are acceptable scenarios i.e.
696 -- ones where customers do not care about one of the measures. Simply
697 -- set that measure to NULL everywhere.
698 --
699 -- Weight/Volume UOM can be the same:
700 -- 1. The locator's weight capacity and volume capacity UOM can be the
701 -- same. This is acceptable. However, suppose this UOM is Kg, that
702 -- needs to be converted to weight reporting UOM (lbs) and volume
703 -- reporting UOM M3.
704 -- Say the conversion rates table has a rate from Kg --> lbs, but
705 -- nothing from Kg --> M3. To avoid filtering out rows for such
706 -- locators and instead reporting errors for them, this SQL has
707 -- to be written with an inline view which contains all the
708 -- from and reporting UOMs, and an outer SQL that purely outer
709 -- joins to the standard conversion rates tables based on these
710 -- from and reporting UOMs.
711 --
712 INSERT /*+ append */
713 INTO opi_dbi_wms_curr_utz_sub_stg (
714 organization_id,
715 subinventory_code,
716 locator_id,
717 weight_capacity_b,
718 weight_uom_code,
719 weight_capacity_rep,
720 volume_capacity_b,
721 volume_uom_code,
722 volume_capacity_rep)
723 SELECT /*+ parallel (loc) parallel (wt_conv) parallel (vol_conv)
724 use_hash (loc, wt_conv, vol_conv) */
725 loc.organization_id,
726 loc.subinventory_code,
727 loc.locator_id,
728 loc.max_weight weight_capacity_b,
729 decode (loc.weight_uom_code,
730 C_DUMMY_UOM_CODE, NULL,
731 loc.weight_uom_code) weight_uom_code,
732 CASE -- the order of conditions matters
733 WHEN loc.max_weight IS NULL THEN
734 -- Weight capacity is not setup up. Legitimate NULL case.
735 NULL
736 WHEN loc.wt_rep_uom_code = C_DUMMY_UOM_CODE THEN
737 -- No weight reporting UOM defined. Acceptable.
738 NULL
739 WHEN loc.weight_uom_code = C_DUMMY_UOM_CODE AND
740 loc.max_weight IS NOT NULL THEN
741 -- Setup error. Value defined, but no UOM.
742 -- Ignore this locator.
743 NULL
744 WHEN wt_conv.conversion_rate IS NULL THEN
745 -- Row created from pure outer join i.e. it is a valid
746 -- combination with non-null UOMs and there is
747 -- no conv. rate for this combination.
748 -- From_uom_code and rep_uom_code
749 -- will be null in the wt_conv table for this row also.
750 C_CONV_NOT_SETUP
751 WHEN wt_conv.conversion_rate < 0 THEN
752 -- Error found in conversion rates table.
753 C_CONV_NOT_SETUP
754 WHEN wt_conv.conversion_rate >= 0 THEN
755 -- Valid conv. rate found.
756 -- Note: allowing conv. rate = 0.
757 loc.max_weight * wt_conv.conversion_rate
758 ELSE
759 -- Why will we get here? Should really never.
760 C_CONV_NOT_SETUP
761 END weight_capacity_rep,
762 loc.max_cubic_area volume_capacity_b,
763 decode (loc.volume_uom_code,
764 C_DUMMY_UOM_CODE, NULL,
765 loc.volume_uom_code) volume_uom_code,
766 CASE -- the order of conditions matters
767 WHEN loc.max_cubic_area IS NULL THEN
768 -- Volume capacity is not setup up. Legitimate NULL case.
769 NULL
770 WHEN loc.vol_rep_uom_code = C_DUMMY_UOM_CODE THEN
771 -- No volume reporting UOM defined. Acceptable.
772 NULL
773 WHEN loc.volume_uom_code = C_DUMMY_UOM_CODE AND
774 loc.max_cubic_area IS NOT NULL THEN
775 -- Setup error. Value defined, but no UOM.
776 -- Ignore this locator.
777 NULL
778 WHEN vol_conv.conversion_rate IS NULL THEN
779 -- Row created from pure outer join i.e. it is a valid
780 -- combination with non-null UOMs and there is
781 -- no conv. rate for this combination.
782 -- From_uom_code and rep_uom_code
783 -- will be null in the vol_conv table for this row also.
784 C_CONV_NOT_SETUP
785 WHEN vol_conv.conversion_rate < 0 THEN
786 -- Error found in conversion rates table.
787 C_CONV_NOT_SETUP
788 WHEN vol_conv.conversion_rate >= 0 THEN
789 -- Valid conv. rate found.
790 -- Note: allowing conv. rate = 0.
791 loc.max_cubic_area * vol_conv.conversion_rate
792 ELSE
793 -- Why will we get here? Should really never.
794 C_CONV_NOT_SETUP
795 END volume_capacity_rep
796 FROM
797 (SELECT /*+ parallel (mil) parallel (mp) */
798 mil.organization_id,
799 mil.subinventory_code,
800 mil.inventory_location_id locator_id,
801 mil.max_weight max_weight,
802 mil.max_cubic_area max_cubic_area,
803 nvl (mil.location_weight_uom_code,
804 C_DUMMY_UOM_CODE) weight_uom_code,
805 l_wt_rep_uom_code wt_rep_uom_code,
806 nvl (mil.volume_uom_code, C_DUMMY_UOM_CODE) volume_uom_code,
807 l_vol_rep_uom_code vol_rep_uom_code
808 FROM mtl_item_locations mil,
809 mtl_parameters mp
810 WHERE mp.wms_enabled_flag = 'Y'
811 AND mil.organization_id = mp.organization_id
812 -- filter out locators with neither capacity defined
813 AND ( ( mil.max_weight IS NOT NULL
814 AND mil.location_weight_uom_code IS NOT NULL)
815 OR ( mil.max_cubic_area IS NOT NULL
816 AND mil.volume_uom_code IS NOT NULL)
817 )
818 ) loc,
819 opi_dbi_rep_uom_std_conv_f wt_conv,
820 opi_dbi_rep_uom_std_conv_f vol_conv
821 WHERE loc.weight_uom_code = wt_conv.from_uom_code (+)
822 AND loc.wt_rep_uom_code = wt_conv.rep_uom_code (+)
823 AND loc.volume_uom_code = vol_conv.from_uom_code (+)
824 AND loc.vol_rep_uom_code = vol_conv.rep_uom_code (+);
825
826 return;
827
828 EXCEPTION
829
830 -- Just report this in the wrapper. Shouldn't happen ever anyway.
831 WHEN NO_REP_UOMS_DEFINED THEN
832 RAISE;
833
834 WHEN OTHERS THEN
835 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
836 l_stmt_id));
837 RAISE LOCATOR_CAP_CALC_FAILED;
838
839 END extract_locator_capacities;
840
841
842 /* Summarize_capacities
843
844 Summarize the locator capacities to the subinventory and organization
845 levels (much like a nested MV without the time dimension).
846 The capacities summarized are in the reporting UOMs of the
847 respective measures
848
849 The aggregation levels (in keeping with the values used in other
850 MVs etc):
851 7 - Organization level records
852 1 - Subinventory level records
853
854 No committing of data here. The calling function handles that.
855
856 History:
857 Date Author Action
858 12/08/04 Dinkar Gupta Wrote Function.
859
860 */
861 PROCEDURE summarize_capacities
862 IS
863
864 l_proc_name CONSTANT VARCHAR2 (40) := 'summarize_capacities';
865 l_stmt_id NUMBER;
866
867 BEGIN
868
869 -- Initialization block
870 l_stmt_id := 0;
871
872 l_stmt_id := 10;
873 -- Summarize the reporting UOM weight and volume capacity values
874 -- stored in the staging table. Since the report needs to run
875 -- only at the subinventory and organization levels, aggregate the
876 -- data. The extra cost of aggregating to the organization level is
877 -- minimal.
878 --
879 -- It is assumed that the data can simply be aggregated. Any errors
880 -- that were recorded as negative capacities have been handled/
881 -- resolved already (or this function is not called when such errors
882 -- exist).
883 INSERT /*+ append */
884 INTO opi_dbi_wms_curr_utz_sub_f (
885 organization_id,
886 subinventory_code,
887 aggregation_level_flag,
888 weight_capacity,
889 volume_capacity,
890 creation_date,
891 last_update_date,
892 created_by,
893 last_updated_by,
894 last_update_login,
895 program_id,
896 program_login_id,
897 program_application_id,
898 request_id
899 )
900 SELECT /*+ parallel (stg) */
901 stg.organization_id,
902 decode (stg.subinventory_code,
903 NULL, NULL,
904 stg.subinventory_code || '-' || stg.organization_id)
905 subinventory_code,
906 decode (grouping_id (stg.organization_id,
907 decode (stg.subinventory_code,
908 NULL, NULL,
909 stg.subinventory_code || '-' ||
910 stg.organization_id)),
911 0, 1,
912 1, 7,
913 -1) aggregation_level_flag,
914 sum (stg.weight_capacity_rep) weight_capacity_rep,
915 sum (stg.volume_capacity_rep) volume_capacity_rep,
916 sysdate,
917 sysdate,
918 s_user_id,
919 s_user_id,
920 s_login_id,
921 s_program_id,
922 s_program_login_id,
923 s_program_application_id,
924 s_request_id
925 FROM opi_dbi_wms_curr_utz_sub_stg stg
926 GROUP BY
927 stg.organization_id,
928 rollup (decode (stg.subinventory_code,
929 NULL, NULL,
930 stg.subinventory_code || '-' || stg.organization_id));
931
932
933 return;
934
935 EXCEPTION
936
937 WHEN OTHERS THEN
938 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
939 l_stmt_id));
940 RAISE SUMMARIZE_CAP_FAILED;
941
942 END summarize_capacities;
943
944 /* check_locator_setup_errors
945
946 Report any invalid conversion rates found for locator weight
947 and or volume capacities.
948
949 History:
950 Date Author Action
951 12/30/04 Dinkar Gupta Wrote Function.
952
953 */
954 FUNCTION check_locator_setup_errors
955 RETURN BOOLEAN
956
957 IS
958
959 l_proc_name CONSTANT VARCHAR2 (40) := 'check_locator_setup_errors';
960 l_stmt_id NUMBER;
961
962 -- check if the header has been printed
963 l_header_printed BOOLEAN;
964
965 -- check whether to return errors
966 l_errors BOOLEAN;
967
968 -- Cursor for missing weight conversions.
969 CURSOR wt_missing_rates_csr IS
970 SELECT DISTINCT
971 weight_uom_code uom_code
972 FROM opi_dbi_wms_curr_utz_sub_stg
973 WHERE weight_capacity_rep < 0;
974
975 -- Cursor for missing volume conversions.
976 CURSOR vol_missing_rates_csr IS
977 SELECT DISTINCT
978 volume_uom_code uom_code
979 FROM opi_dbi_wms_curr_utz_sub_stg
980 WHERE volume_capacity_rep < 0;
981
982 BEGIN
983
984 -- Initialization block
985 l_errors := FALSE; -- no errors yet
986 l_header_printed := FALSE;
987 l_stmt_id := 0;
988
989 -- Report missing weights.
990 l_header_printed := FALSE;
991 l_stmt_id := 10;
992 FOR wt_missing_rates_rec IN wt_missing_rates_csr
993 LOOP
994
995 -- Print the header once if not done so yet.
996 l_stmt_id := 20;
997 IF (l_header_printed = FALSE) THEN
998
999 OPI_DBI_REP_UOM_PKG.err_msg_header_spec ('WT', 'LOC');
1000 l_header_printed := TRUE;
1001
1002 END IF;
1003
1004 -- Print the missing rates.
1005 l_stmt_id := 30;
1006 OPI_DBI_REP_UOM_PKG.err_msg_missing_uoms (
1007 wt_missing_rates_rec.uom_code, s_wt_rep_uom_code);
1008
1009 -- There is an error to report
1010 l_stmt_id := 40;
1011 l_errors := TRUE;
1012
1013 END LOOP;
1014
1015 -- Footer message.
1016 IF (l_header_printed = TRUE) THEN
1017 OPI_DBI_REP_UOM_PKG.err_msg_footer;
1018 END IF;
1019
1020 -- Report missing weights.
1021 l_header_printed := FALSE;
1022 l_stmt_id := 50;
1023 FOR vol_missing_rates_rec IN vol_missing_rates_csr
1024 LOOP
1025
1026 -- Print the header once if not done so yet.
1027 l_stmt_id := 60;
1028 IF (l_header_printed = FALSE) THEN
1029
1030 OPI_DBI_REP_UOM_PKG.err_msg_header_spec ('VOL', 'LOC');
1031 l_header_printed := TRUE;
1032
1033 END IF;
1034
1035 -- Print the missing rates.
1036 l_stmt_id := 70;
1037 OPI_DBI_REP_UOM_PKG.err_msg_missing_uoms (
1038 vol_missing_rates_rec.uom_code, s_vol_rep_uom_code);
1039
1040 -- There is an error to report
1041 l_stmt_id := 80;
1042 l_errors := TRUE;
1043
1044 END LOOP;
1045
1046 -- Footer message.
1047 IF (l_header_printed = TRUE) THEN
1048 OPI_DBI_REP_UOM_PKG.err_msg_footer;
1049 END IF;
1050
1051 return l_errors;
1052
1053 EXCEPTION
1054
1055 WHEN OTHERS THEN
1056 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1057 l_stmt_id));
1058 RAISE LOCATOR_ERR_CHECK_FAILED;
1059
1060 END check_locator_setup_errors;
1061
1062
1063 /**************************************************
1064 * Item Storage Related procedures
1065 **************************************************/
1066
1067 /* extract_locator_capacities
1068
1069 Extract the item storage in all locators of warehouses that
1070 have weight and/or volume capacities defined. The locator
1071 capacities are stored in the locator staging table,
1072 OPI_DBI_WMS_CURR_UTZ_SUB_STG. The item weights/volumes must
1073 be stored in the reporting UOM values. If a locator does
1074 not have weight capacity defined the item's weight in that
1075 locator is ignored. Same goes for the item volume. If the
1076 conversion rate for the weight/volume to the reporting UOMs
1077 is not defined, store negative error codes.
1078
1079
1080
1081 Note also that one or both of the reporting units for weight
1082 and volume can be NULL. That is not an error condition.
1083
1084 No committing of data here. The calling function handles that.
1085
1086 Parameters:
1087 1. p_wt_rep_uom_code - Weight reporting UOM code
1088 2. p_vol_rep_uom_code - Volume reporting UOM code
1089
1090 History:
1091 Date Author Action
1092 12/08/04 Dinkar Gupta Wrote Function.
1093
1094 */
1095 PROCEDURE extract_item_storage (p_wt_rep_uom_code IN VARCHAR2,
1096 p_vol_rep_uom_code IN VARCHAR2)
1097
1098 IS
1099 l_proc_name CONSTANT VARCHAR2 (40) := 'extract_item_storage';
1100 l_stmt_id NUMBER;
1101
1102 -- local copy of reporting UOMs
1103 l_wt_rep_uom_code VARCHAR2 (3);
1104 l_vol_rep_uom_code VARCHAR2 (3);
1105
1106 BEGIN
1107
1108 -- initialization block
1109 l_stmt_id := 0;
1110 l_wt_rep_uom_code := NULL;
1111 l_vol_rep_uom_code := NULL;
1112
1113 l_stmt_id := 10;
1114 -- If both reporting UOMs are undefined, return. Wrapper routine
1115 -- should ensure this doesn't happen, but check just in case.
1116 IF (p_wt_rep_uom_code IS NULL AND
1117 p_vol_rep_uom_code IS NULL) THEN
1118 RAISE NO_REP_UOMS_DEFINED;
1119 END IF;
1120
1121 l_stmt_id := 20;
1122 -- Set the reporting UOM's to the dummy code, to avoid handling
1123 -- the condition that one of them can be NULL in the SQL below.
1124 l_wt_rep_uom_code := nvl (p_wt_rep_uom_code, C_DUMMY_UOM_CODE);
1125 l_vol_rep_uom_code := nvl (p_vol_rep_uom_code, C_DUMMY_UOM_CODE);
1126
1127 l_stmt_id := 30;
1128 -- Extract the item storage details for all organizations that are
1129 -- WMS enabled. No need to specifically filter out
1130 -- process organizations since their locator setup is not
1131 -- stored in MTL_ONHAND_QUANTITIES.
1132 --
1133 -- Collecting 3 measures:
1134 -- 1. Quantity stored in any locator with weight or volume capacity
1135 -- defined.
1136 -- 2. Weight stored in all locators with weight capacity defined.
1137 -- 3. Volume stored in all locators with volume capacity defined.
1138 --
1139 -- The item setup form guarantees that a UOM be specified for the
1140 -- item weight/volume before a value is specified. So no need to
1141 -- worry about missing UOMs and specified values.
1142 --
1143 -- The subinventory capacity staging table, OPI_DBI_WMS_CURR_UTZ_SUB_STG,
1144 -- has all the locator level records. Use the values in the reporting
1145 -- UOM value columns of the staging table. NULL values in those
1146 -- columns indicate that the corresponding capacity was not specified
1147 -- for the measure on that locator.
1148 --
1149 -- For efficient report query retrieval, store the
1150 -- the data aggregated at the following levels, marked by the
1151 -- following aggregation level flag values:
1152 -- 7 - Organization
1153 -- 1 - Organization, Inventory Category, Subinventory
1154 -- 0 - Organization, Inventory Category, Subinventory, Item
1155 --
1156 -- All possible conversions to the reporting UOMs are stored in
1157 -- the standard conversion rates table, OPI_DBI_REP_UOM_STD_CONV_F.
1158 -- The extraction program will join twice to this table, once
1159 -- for weight capacity conversions and once for volume capacity
1160 -- conversions. Since certain rows may not join to the conversion
1161 -- fact for one of the measures, use outer joins.
1162 --
1163 -- The following pathalogical scenarios are possible:
1164 --
1165 -- Missing Setups:
1166 -- 1. Item Weight setup is missing.
1167 -- 2. Item Volume setup is missing.
1168 -- Either or both of the above are acceptable. In case of no setup,
1169 -- report NULL for the corresponding measure, and report a quantity
1170 -- for the item.
1171 --
1172 -- Missing locator setup:
1173 -- 1. Locator is missing weight capacity.
1174 -- 2. Locator is missing volume capacity.
1175 -- By design, both of the above conditions cannot be simultaneously true.
1176 -- Ignore the item's contribution to the measure whose capacity setup
1177 -- is missing on the locator by recording a NULL value.
1178 --
1179 -- Missing conversion rates:
1180 -- 1. Item weight cannot be converted into the reporting weight UOM.
1181 -- 2. Item volume cannot be converted into the reporting volume UOM.
1182 -- This will be recorded as a negative number, given the negative
1183 -- error codes for missing rates. Store that for the moment, and
1184 -- report errors later. If no conversion rate is found, record a
1185 -- negative number.
1186 --
1187 -- Missing Reporting UOMs:
1188 -- 1. The Weight Reporting UOM can be missing.
1189 -- 2. The Volume Reporting UOM can be missing.
1190 -- Since both won't be missing, these are acceptable scenarios i.e.
1191 -- ones where customers do not care about one of the measures. Simply
1192 -- set that measure to NULL everywhere.
1193 --
1194 -- Weight/Volume UOM can be the same:
1195 -- 1. The item's weight capacity and volume UOM can be the
1196 -- same. This is acceptable. However, suppose this UOM is Kg, that
1197 -- needs to be converted to weight reporting UOM (lbs) and volume
1198 -- reporting UOM M3.
1199 -- Say the conversion rates table has a rate from Kg --> lbs, but
1200 -- nothing from Kg --> M3. To avoid filtering out rows for such
1201 -- items and instead reporting errors for them, this SQL has
1202 -- to be written with an inline view which contains all the
1203 -- from and reporting UOMs, and an outer SQL that purely outer
1204 -- joins to the standard conversion rates tables based on these
1205 -- from and reporting UOMs.
1206 --
1207 INSERT /*+ append */
1208 INTO opi_dbi_wms_curr_utz_item_f (
1209 organization_id,
1210 item_org_id,
1211 uom_code,
1212 subinventory_code,
1213 inv_category_id,
1214 aggregation_level_flag,
1215 stored_qty,
1216 weight_qty,
1217 stored_weight,
1218 weight_uom_code,
1219 volume_qty,
1220 utilized_volume,
1221 volume_uom_code,
1222 creation_date,
1223 last_update_date,
1224 created_by,
1225 last_updated_by,
1226 last_update_login,
1227 program_id,
1228 program_login_id,
1229 program_application_id,
1230 request_id)
1231 SELECT /*+ parallel (onh) parallel (wt_conv) parallel (vol_conv)
1232 use_hash (onh, wt_conv, vol_conv) */
1233 onh.organization_id,
1234 (onh.inventory_item_id || '-' || onh.organization_id)
1235 item_org_id,
1236 onh.primary_uom_code uom_code,
1237 decode (onh.subinventory_code,
1238 NULL, NULL,
1239 (onh.subinventory_code || '-' || onh.organization_id))
1240 subinventory_code,
1241 nvl (onh.inv_category_id, -1) inv_category_id,
1242 grouping_id (onh.organization_id,
1243 nvl (onh.inv_category_id, -1),
1244 decode (onh.subinventory_code,
1245 NULL, NULL,
1246 (onh.subinventory_code || '-' ||
1247 onh.organization_id)),
1248 (onh.inventory_item_id || '-' ||
1249 onh.organization_id))
1250 aggregation_level_flag,
1251 sum (onh.stored_qty) stored_qty,
1252 sum (onh.weight_qty) weight_qty,
1253 sum (
1254 CASE -- order of conditions matters
1255 WHEN onh.unit_weight IS NULL THEN
1256 -- Weight not setup. Acceptable.
1257 NULL
1258 WHEN onh.wt_rep_uom_code = C_DUMMY_UOM_CODE THEN
1259 -- Weight reporting UOM not set up. Acceptable
1260 NULL
1261 WHEN wt_conv.conversion_rate IS NULL THEN
1262 -- Row created from pure outer join i.e. it is a valid
1263 -- combination with non-null UOMs and there is
1264 -- no conv. rate for this combination.
1265 -- From_uom_code and rep_uom_code
1266 -- will be null in the wt_conv table for this row also.
1267 C_CONV_NOT_SETUP
1268 WHEN wt_conv.conversion_rate < 0 THEN
1269 -- Error found in conversion rates table.
1270 C_CONV_NOT_SETUP
1271 WHEN wt_conv.conversion_rate >= 0 THEN
1272 -- Valid conv. rate found.
1273 -- Note: allowing conv. rate = 0.
1274 onh.weight_qty * wt_conv.conversion_rate *
1275 onh.unit_weight
1276 ELSE
1277 -- Why will we get here? Should really never.
1278 C_CONV_NOT_SETUP
1279 END
1280 ) stored_weight,
1281 onh.weight_uom_code,
1282 sum (onh.volume_qty) volume_qty,
1283 sum (
1284 CASE -- order of conditions matters
1285 WHEN onh.unit_volume IS NULL THEN
1286 -- Volume not setup. Acceptable.
1287 NULL
1288 WHEN onh.vol_rep_uom_code = C_DUMMY_UOM_CODE THEN
1289 -- Volume reporting UOM not set up. Acceptable
1290 NULL
1291 WHEN vol_conv.conversion_rate IS NULL THEN
1292 -- Row created from pure outer join i.e. it is a valid
1293 -- combination with non-null UOMs and there is
1294 -- no conv. rate for this combination.
1295 -- From_uom_code and rep_uom_code
1296 -- will be null in the vol_conv table for this row also.
1297 C_CONV_NOT_SETUP
1298 WHEN vol_conv.conversion_rate < 0 THEN
1299 -- Error found in conversion rates table.
1300 C_CONV_NOT_SETUP
1301 WHEN vol_conv.conversion_rate >= 0 THEN
1302 -- Valid conv. rate found.
1303 -- Note: allowing conv. rate = 0.
1304 onh.volume_qty * vol_conv.conversion_rate *
1305 onh.unit_volume
1306 ELSE
1307 -- Why will we get here? Should really never.
1308 C_CONV_NOT_SETUP
1309 END
1310 ) utilized_volume,
1311 onh.volume_uom_code,
1312 sysdate,
1313 sysdate,
1314 s_user_id,
1315 s_user_id,
1316 s_login_id,
1317 s_program_id,
1318 s_program_login_id,
1319 s_program_application_id,
1320 s_request_id
1321 FROM
1322 (SELECT /*+ parallel (moq) parallel (mp) parallel (loc)
1323 parallel (items) */
1324 moq.organization_id,
1325 moq.inventory_item_id,
1326 items.primary_uom_code,
1327 moq.subinventory_code,
1328 nvl (items.weight_uom_code, C_DUMMY_UOM_CODE) weight_uom_code,
1329 l_wt_rep_uom_code wt_rep_uom_code,
1330 nvl (items.volume_uom_code, C_DUMMY_UOM_CODE) volume_uom_code,
1331 l_vol_rep_uom_code vol_rep_uom_code,
1332 items.inv_category_id,
1333 items.unit_weight,
1334 items.unit_volume,
1335 sum (moq.transaction_quantity) stored_qty,
1336 sum (decode (weight_capacity_rep,
1337 NULL, NULL,
1338 moq.transaction_quantity)) weight_qty,
1339 sum (decode (volume_capacity_rep,
1340 NULL, NULL,
1341 moq.transaction_quantity)) volume_qty
1342 FROM
1343 mtl_onhand_quantities moq,
1344 mtl_parameters mp,
1345 opi_dbi_wms_curr_utz_sub_stg loc,
1346 eni_oltp_item_star items
1347 WHERE mp.wms_enabled_flag = 'Y'
1348 AND moq.organization_id = mp.organization_id
1349 AND moq.organization_id = items.organization_id
1350 AND moq.inventory_item_id = items.inventory_item_id
1351 AND moq.organization_id = loc.organization_id
1352 AND moq.subinventory_code = loc.subinventory_code
1353 AND moq.locator_id = loc.locator_id
1354 GROUP BY
1355 moq.organization_id,
1356 moq.inventory_item_id,
1357 items.primary_uom_code,
1358 moq.subinventory_code,
1359 nvl (items.weight_uom_code, C_DUMMY_UOM_CODE),
1360 nvl (items.volume_uom_code, C_DUMMY_UOM_CODE),
1361 items.inv_category_id,
1362 items.unit_weight,
1363 items.unit_volume
1364 ) onh,
1365 opi_dbi_rep_uom_std_conv_f wt_conv,
1366 opi_dbi_rep_uom_std_conv_f vol_conv
1367 WHERE onh.weight_uom_code = wt_conv.from_uom_code (+)
1368 AND onh.wt_rep_uom_code = wt_conv.rep_uom_code (+)
1369 AND onh.volume_uom_code = vol_conv.from_uom_code (+)
1370 AND onh.vol_rep_uom_code = vol_conv.rep_uom_code (+)
1371 GROUP BY
1372 onh.organization_id,
1373 rollup ( (nvl (onh.inv_category_id, -1),
1374 decode (onh.subinventory_code,
1375 NULL, NULL,
1376 (onh.subinventory_code || '-' ||
1377 onh.organization_id))),
1378 ((onh.inventory_item_id || '-' ||
1379 onh.organization_id),
1380 onh.primary_uom_code,
1381 onh.weight_uom_code,
1382 onh.volume_uom_code) );
1383
1384 return;
1385
1386 EXCEPTION
1387
1388 -- Just report this in the wrapper. Shouldn't happen ever anyway.
1389 WHEN NO_REP_UOMS_DEFINED THEN
1390 RAISE;
1391
1392 WHEN OTHERS THEN
1393 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1394 l_stmt_id));
1395 RAISE ITEM_STOR_CALC_FAILED;
1396
1397 END extract_item_storage;
1398
1399 /* check_item_setup_errors
1400
1401 Report any invalid conversion rates found for item weight
1402 and or volume storage.
1403
1404 History:
1405 Date Author Action
1406 12/30/04 Dinkar Gupta Wrote Function.
1407
1408 */
1409 FUNCTION check_item_setup_errors
1410 RETURN BOOLEAN
1411
1412 IS
1413
1414 l_proc_name CONSTANT VARCHAR2 (40) := 'check_item_setup_errors';
1415 l_stmt_id NUMBER;
1416
1417 -- check if the header has been printed
1418 l_header_printed BOOLEAN;
1419
1420 -- check whether to return errors
1421 l_errors BOOLEAN;
1422
1423 -- Cursor for missing weight conversions at the non-aggregated levels.
1424 -- Use the fact the the quantity and weight should have the same
1425 -- sign.
1426 CURSOR wt_missing_rates_csr IS
1427 SELECT DISTINCT
1428 weight_uom_code uom_code
1429 FROM opi_dbi_wms_curr_utz_item_f
1430 WHERE stored_weight/weight_qty < 0
1431 AND aggregation_level_flag = 0;
1432
1433 -- Cursor for missing volume conversions at the non-aggregated levels.
1434 -- Use the fact the the quantity and volume should have the same
1435 -- sign.
1436 CURSOR vol_missing_rates_csr IS
1437 SELECT DISTINCT
1438 volume_uom_code uom_code
1439 FROM opi_dbi_wms_curr_utz_item_f
1440 WHERE utilized_volume/volume_qty < 0
1441 AND aggregation_level_flag = 0;
1442
1443 BEGIN
1444
1445 -- Initialization block
1446 l_errors := FALSE; -- no errors yet
1447 l_header_printed := FALSE;
1448 l_stmt_id := 0;
1449
1450 -- Report missing weights.
1451 l_header_printed := FALSE;
1452 l_stmt_id := 10;
1453 FOR wt_missing_rates_rec IN wt_missing_rates_csr
1454 LOOP
1455
1456 -- Print the header once if not done so yet.
1457 l_stmt_id := 20;
1458 IF (l_header_printed = FALSE) THEN
1459
1460 OPI_DBI_REP_UOM_PKG.err_msg_header_spec ('WT', 'ITEM');
1461 l_header_printed := TRUE;
1462
1463 END IF;
1464
1465 -- Print the missing rates.
1466 l_stmt_id := 30;
1467 OPI_DBI_REP_UOM_PKG.err_msg_missing_uoms (
1468 wt_missing_rates_rec.uom_code, s_wt_rep_uom_code);
1469
1470 -- There is an error to report
1471 l_stmt_id := 40;
1472 l_errors := TRUE;
1473
1474 END LOOP;
1475
1476 -- Footer message.
1477 IF (l_header_printed = TRUE) THEN
1478 OPI_DBI_REP_UOM_PKG.err_msg_footer;
1479 END IF;
1480
1481 -- Report missing weights.
1482 l_header_printed := FALSE;
1483 l_stmt_id := 50;
1484 FOR vol_missing_rates_rec IN vol_missing_rates_csr
1485 LOOP
1486
1487 -- Print the header once if not done so yet.
1488 l_stmt_id := 60;
1489 IF (l_header_printed = FALSE) THEN
1490
1491 OPI_DBI_REP_UOM_PKG.err_msg_header_spec ('VOL', 'ITEM');
1492 l_header_printed := TRUE;
1493
1494 END IF;
1495
1496 -- Print the missing rates.
1497 l_stmt_id := 70;
1498 OPI_DBI_REP_UOM_PKG.err_msg_missing_uoms (
1499 vol_missing_rates_rec.uom_code, s_vol_rep_uom_code);
1500
1501 -- There is an error to report
1502 l_stmt_id := 80;
1503 l_errors := TRUE;
1504
1505 END LOOP;
1506
1507 -- Footer message.
1508 IF (l_header_printed = TRUE) THEN
1509 OPI_DBI_REP_UOM_PKG.err_msg_footer;
1510 END IF;
1511
1512 return l_errors;
1513
1514 EXCEPTION
1515
1516 WHEN OTHERS THEN
1517 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1518 l_stmt_id));
1519 RAISE ITEM_ERR_CHECK_FAILED;
1520
1521 END check_item_setup_errors;
1522
1523
1524
1525 END opi_dbi_wms_capacity_utz_pkg;
[Home] [Help]