[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_WMS_UTILITY_PKG
Source
1 PACKAGE BODY opi_dbi_wms_utility_pkg AS
2 /*$Header: OPIDEWUTILB.pls 120.0 2005/05/24 17:13:00 appldev noship $ */
3
4 /**************************************************
5 * Common Procedures
6 *
7 * File scope functions (not in spec)
8 **************************************************/
9
10 -- Print out error message in a consistent manner
11 FUNCTION err_mesg (p_mesg IN VARCHAR2,
12 p_proc_name IN VARCHAR2,
13 p_stmt_id IN NUMBER)
14 RETURN VARCHAR2;
15
16 -- Report item level conversion rate error details
17 PROCEDURE report_item_conv_rate_err (errbuf OUT NOCOPY VARCHAR2,
18 retcode OUT NOCOPY NUMBER);
19
20 -- Report Locator level conversion rate error details
21 PROCEDURE report_locator_conv_rate_err (errbuf OUT NOCOPY VARCHAR2,
22 retcode OUT NOCOPY NUMBER);
23
24
25 /**************************************************
26 * Common Procedures
27 *
28 * File scope functions (not in spec)
29 **************************************************/
30 /* err_mesg
31
32 Return a C_ERRBUF_SIZE character long, properly formatted error
33 message with the package name, procedure name and message.
34
35 Parameters:
36 p_mesg - Actual message to be printed
37 p_proc_name - name of procedure that should be printed in the message
38 (optional)
39 p_stmt_id - step in procedure at which error occurred
40 (optional)
41
42 History:
43 Date Author Action
44 12/08/04 Dinkar Gupta Defined function.
45 */
46
47 FUNCTION err_mesg (p_mesg IN VARCHAR2,
48 p_proc_name IN VARCHAR2,
49 p_stmt_id IN NUMBER)
50 RETURN VARCHAR2
51 IS
52
53 l_proc_name CONSTANT VARCHAR2 (60) := 'err_mesg';
54 l_stmt_id NUMBER;
55
56 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
57 -- as the size of the declaration. I have to put 300 here.
58 l_formatted_message VARCHAR2 (300);
59
60 BEGIN
61
62 -- initialization block
63 l_stmt_id := 0;
64
65 -- initialization block
66 l_formatted_message := NULL;
67
68 l_stmt_id := 10;
69 l_formatted_message := substr ((C_PKG_NAME || '.' || p_proc_name || ' #' ||
70 to_char (p_stmt_id) || ': ' || p_mesg),
71 1, C_ERRBUF_SIZE);
72
73 commit;
74
75 return l_formatted_message;
76
77 EXCEPTION
78
79 WHEN OTHERS THEN
80 -- the exception happened in the exception reporting function !!
81 -- return with ERROR.
82 l_formatted_message := substr ((C_PKG_NAME || '.' || l_proc_name ||
83 ' #' ||
84 to_char (l_stmt_id) || ': ' ||
85 SQLERRM),
86 1, C_ERRBUF_SIZE);
87
88 l_formatted_message := 'Error in error reporting.';
89 return l_formatted_message;
90
91 END err_mesg;
92
93
94 /**************************************************
95 * Public Procedures
96 **************************************************/
97
98 /* report_item_setup_missing
99
100 Reports the items in WMS organizations which are missing
101 weight and/or volume setup.
102
103 Parameters:
104 errbuf - error message on unsuccessful termination
105 retcode - 0 on success, 1 on warning, -1 on error
106
107 History:
108 Date Author Action
109 12/20/04 Dinkar Gupta Wrote Function.
110
111 */
112 PROCEDURE report_item_setup_missing (errbuf OUT NOCOPY VARCHAR2,
113 retcode OUT NOCOPY NUMBER)
114 IS
115
116 l_proc_name CONSTANT VARCHAR2 (40) := 'report_item_setup_missing';
117 l_stmt_id NUMBER;
118 l_local_retcode NUMBER;
119
120 -- Header message
121 l_item_missing_header VARCHAR2 (10000);
122
123 -- Fix Message
124 l_item_missing_fix VARCHAR2 (10000);
125
126 -- Number of rows missing
127 l_num_rows_missing NUMBER := 0;
128
129 -- 4 columns: Org, Item, Volume Missing, Weight Missing.
130 -- Each has a header and some width.
131 l_org_col_width NUMBER;
132 l_item_col_width NUMBER;
133 l_vol_col_width NUMBER;
134 l_wt_col_width NUMBER;
135 l_org_col_header VARCHAR2 (80);
136 l_item_col_header VARCHAR2 (80);
137 l_vol_col_header VARCHAR2 (80);
138 l_wt_col_header VARCHAR2 (80);
139
140 -- Markers to mark missing setups.
141 l_wt_missing_marker VARCHAR2 (80);
142 l_wt_ok_marker VARCHAR2 (80);
143 l_vol_missing_marker VARCHAR2 (80);
144 l_vol_ok_marker VARCHAR2 (80);
145
146 -- Line by line print
147 l_line_print VARCHAR2 (1000);
148
149 -- Message for too many missing rows
150 l_too_many_rows_msg VARCHAR2 (1000);
151
152 -- General record for missing setups
153 TYPE opi_dbi_item_setup_missing_rec is RECORD (
154 org VARCHAR2(300),
155 item VARCHAR2(240),
156 wt_missing_flag NUMBER,
157 vol_missing_flag NUMBER);
158 l_items_setup_missing_rec opi_dbi_item_setup_missing_rec;
159
160
161 -- Cursor to count how many rows are missing
162 CURSOR item_setup_missing_count_csr IS
163 SELECT
164 count (*)
165 FROM mtl_parameters mp,
166 eni_oltp_item_star items
167 WHERE mp.organization_id = items.organization_id
168 AND mp.wms_enabled_flag = 'Y'
169 AND mp.process_enabled_flag <> 'Y'
170 AND ( items.weight_uom_code IS NULL
171 OR items.volume_uom_code IS NULL
172 OR items.unit_weight IS NULL
173 OR items.unit_volume IS NULL);
174
175
176 -- Cursor to list all the items with missing unit weight and/or
177 -- volume.
178 TYPE item_setup_missing_cursor IS REF CURSOR;
179 item_setup_missing_csr item_setup_missing_cursor;
180
181 l_item_setup_missing_sql CONSTANT VARCHAR2 (4000) :=
182 'SELECT
183 (orgs.name || '' ('' || mp.organization_code || '')'') org,
184 items.value item,
185 CASE
186 WHEN items.weight_uom_code IS NULL OR
187 items.unit_weight IS NULL THEN
188 1 /*C_WT_MISSING*/
189 ELSE
190 0 /*C_NOTHING_MISSING*/
191 END wt_missing_flag,
192 CASE
193 WHEN items.volume_uom_code IS NULL OR
194 items.unit_volume IS NULL THEN
195 2 /*C_VOL_MISSING*/
196 ELSE
197 3 /*C_NOTHING_MISSING*/
198 END vol_missing_flag
199 FROM mtl_parameters mp,
200 eni_oltp_item_star items,
201 hr_all_organization_units_vl orgs
202 WHERE mp.organization_id = orgs.organization_id
203 AND mp.organization_id = items.organization_id
204 AND mp.wms_enabled_flag = ''Y''
205 AND mp.process_enabled_flag <> ''Y''
206 AND ( items.weight_uom_code IS NULL
207 OR items.volume_uom_code IS NULL
208 OR items.unit_weight IS NULL
209 OR items.unit_volume IS NULL)
210 ORDER BY
211 (orgs.name || '' ('' || mp.organization_code || '')''),
212 items.value';
213
214
215 BEGIN
216
217 -- Initialization Block
218 l_stmt_id := 0;
219 l_local_retcode := C_SUCCESS;
220
221 -- Column widths for each column.
222 l_stmt_id := 10;
223 l_org_col_width := C_ORG_COL_WIDTH;
224 l_item_col_width := C_ITEM_COL_WIDTH;
225 l_vol_col_width := C_VOL_MISSING_COL_WIDTH;
226 l_wt_col_width := C_WT_MISSING_COL_WIDTH;
227 l_wt_missing_marker := NULL;
228 l_wt_ok_marker := NULL;
229 l_vol_missing_marker := NULL;
230 l_vol_ok_marker := NULL;
231 l_line_print := NULL;
232
233 -- Column Headers
234 l_stmt_id := 20;
235 l_org_col_header := FND_MESSAGE.get_string ('OPI', 'OPI_DBI_ORG_COL_HDR');
236 l_org_col_header := substr (l_org_col_header, 1, l_org_col_width);
237
238 l_item_col_header := FND_MESSAGE.get_string (
239 'OPI', 'OPI_DBI_ITEM_COL_HDR');
240 l_item_col_header := substr (l_item_col_header, 1, l_item_col_width);
241
242 l_vol_col_header := FND_MESSAGE.get_string (
243 'OPI', 'OPI_DBI_VOL_SETUP_COL_HDR');
244 l_vol_col_header := substr (l_vol_col_header, 1, l_vol_col_width);
245
246 l_wt_col_header := FND_MESSAGE.get_string (
247 'OPI', 'OPI_DBI_WT_SETUP_COL_HDR');
248 l_wt_col_header := substr (l_wt_col_header, 1, l_wt_col_width);
249
250 --First count how many items are missing setups
251 l_stmt_id := 25;
252 OPEN item_setup_missing_count_csr;
253 FETCH item_setup_missing_count_csr INTO l_num_rows_missing;
254 CLOSE item_setup_missing_count_csr;
255
256 IF (l_num_rows_missing > 0) THEN
257
258 -- Item setup missing header message
259 l_stmt_id := 30;
260 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
261 l_item_missing_header := FND_MESSAGE.get_string (
262 'OPI','OPI_DBI_ITEM_WT_VOL_MISSING');
263 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_item_missing_header);
264
265 -- Item setup missing fix message. Print it.
266 l_stmt_id := 40;
267 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
268 l_item_missing_fix := FND_MESSAGE.get_string (
269 'OPI','OPI_DBI_ITEM_WT_VOL_FIX');
270 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_item_missing_fix);
271
272 -- If not too many rows, print out the details
273 IF (l_num_rows_missing <= C_NUM_ROWS_TO_REPORT) THEN
274
275 l_stmt_id := 50;
276 -- Print out the headers padded out with the right number
277 -- of spaces.
278 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
279 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
280 rpad (l_org_col_header,
281 l_org_col_width + C_COL_SPACING) ||
282 rpad (l_item_col_header,
283 l_item_col_width + C_COL_SPACING) ||
284 rpad (l_vol_col_header,
285 l_vol_col_width + C_COL_SPACING) ||
286 rpad (l_wt_col_header,
287 l_wt_col_width + C_COL_SPACING) );
288
289 -- Underline the header with dashes
290 l_stmt_id := 60;
291 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
292 rpad ('-', l_org_col_width, '-') ||
293 rpad (' ', C_COL_SPACING) ||
294 rpad ('-', l_item_col_width, '-') ||
295 rpad (' ', C_COL_SPACING) ||
296 rpad ('-', l_vol_col_width, '-') ||
297 rpad (' ', C_COL_SPACING) ||
298 rpad ('-', l_wt_col_width, '-') ||
299 rpad (' ', C_COL_SPACING) );
300
301 -- Markers for missing weight and volume
302 l_stmt_id := 70;
303 l_wt_missing_marker :=
304 substr (FND_MESSAGE.get_string ('OPI', 'OPI_DBI_UNDEF_STR'),
305 1, l_wt_col_width);
306 l_wt_ok_marker :=
307 substr (FND_MESSAGE.get_string ('OPI', 'OPI_DBI_OK_STR'),
308 1, l_vol_col_width);
309 l_vol_missing_marker :=
310 substr (FND_MESSAGE.get_string ('OPI', 'OPI_DBI_UNDEF_STR'),
311 1, l_wt_col_width);
312 l_vol_ok_marker :=
313 substr (FND_MESSAGE.get_string ('OPI', 'OPI_DBI_OK_STR'),
314 1, l_vol_col_width);
315
316 -- Report every missing setup.
317 l_stmt_id := 80;
318 OPEN item_setup_missing_csr FOR l_item_setup_missing_sql;
319 FETCH item_setup_missing_csr INTO l_items_setup_missing_rec;
320 WHILE item_setup_missing_csr%FOUND
321 LOOP
322
323 -- Every line will have an org/item.
324 l_stmt_id := 90;
325 l_line_print :=
326 rpad (substr (l_items_setup_missing_rec.org, 1,
327 l_org_col_width),
328 l_org_col_width + C_COL_SPACING) ||
329 rpad (substr (l_items_setup_missing_rec.item, 1,
330 l_item_col_width),
331 l_item_col_width + C_COL_SPACING);
332
333 -- Figure out if the volume is missing
334 l_stmt_id := 100;
335 IF (l_items_setup_missing_rec.vol_missing_flag = C_VOL_MISSING)
336 THEN
337 l_line_print :=
338 l_line_print || rpad (l_vol_missing_marker,
339 l_vol_col_width + C_COL_SPACING);
340 ELSE
341 l_line_print :=
342 l_line_print || rpad (l_vol_ok_marker,
343 l_vol_col_width + C_COL_SPACING);
344 END IF;
345
346 -- Figure out if the weight is missing
347 l_stmt_id := 110;
348 IF (l_items_setup_missing_rec.wt_missing_flag = C_WT_MISSING)
349 THEN
350 l_line_print :=
351 l_line_print || rpad (l_wt_missing_marker,
352 l_wt_col_width + C_COL_SPACING);
353 ELSE
354 l_line_print :=
355 l_line_print || rpad (l_wt_ok_marker,
356 l_wt_col_width + C_COL_SPACING);
357 END IF;
358
359 -- Print this missing rate line.
360 l_stmt_id := 120;
361 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_line_print);
362
363 -- Fetch the next line
364 FETCH item_setup_missing_csr INTO l_items_setup_missing_rec;
365
366 END LOOP;
367
368 CLOSE item_setup_missing_csr;
369
370 ELSIF (l_num_rows_missing > C_NUM_ROWS_TO_REPORT) THEN
371
372 l_too_many_rows_msg := FND_MESSAGE.get_string (
373 'OPI', 'OPI_DBI_WT_VOL_SETUP_TOOMANY');
374 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_too_many_rows_msg);
375 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
376 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_item_setup_missing_sql);
377 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
378
379
380 END IF;
381
382 END IF; -- l_num_rows_missing > 0
383
384
385 IF (l_num_rows_missing > 0) THEN
386 l_local_retcode := C_WARNING;
387 END IF;
388
389 errbuf := '';
390 retcode := l_local_retcode;
391
392 return;
393
394 EXCEPTION
395
396 WHEN OTHERS THEN
397 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
398 l_stmt_id));
399 errbuf := SQLERRM;
400 retcode := C_ERROR;
401 return;
402
403 END report_item_setup_missing;
404
405 /* report_locator_setup_missing
406
407 Reports the locators in WMS organizations which are missing
408 weight and/or volume setup.
409
410 Parameters:
411 errbuf - error message on unsuccessful termination
412 retcode - 0 on success, 1 on warning, -1 on error
413
414 History:
415 Date Author Action
416 12/20/04 Dinkar Gupta Wrote Function.
417
418 */
419 PROCEDURE report_locator_setup_missing (errbuf OUT NOCOPY VARCHAR2,
420 retcode OUT NOCOPY NUMBER)
421 IS
422
423 l_proc_name CONSTANT VARCHAR2 (40) := 'report_locator_setup_missing';
424 l_stmt_id NUMBER;
425 l_local_retcode NUMBER;
426
427 -- Header message
428 l_loc_missing_header VARCHAR2 (10000);
429
430 -- Fix Message
431 l_loc_missing_fix VARCHAR2 (10000);
432
433 -- 5 columns: Org, Subinventory, Locator, Volume Missing, Weight Missing.
434 -- Each has a header and some width.
435 l_org_col_width NUMBER;
436 l_sub_col_width NUMBER;
437 l_loc_col_width NUMBER;
438 l_vol_col_width NUMBER;
439 l_wt_col_width NUMBER;
440 l_org_col_header VARCHAR2 (80);
441 l_sub_col_header VARCHAR2 (80);
442 l_loc_col_header VARCHAR2 (80);
443 l_vol_col_header VARCHAR2 (80);
444 l_wt_col_header VARCHAR2 (80);
445
446 -- Markers to mark missing setups.
447 l_wt_missing_marker VARCHAR2 (80);
448 l_wt_ok_marker VARCHAR2 (80);
449 l_vol_missing_marker VARCHAR2 (80);
450 l_vol_ok_marker VARCHAR2 (80);
451
452 -- Line by line print
453 l_line_print VARCHAR2 (1000);
454
455 -- Number of rows missing
456 l_num_rows_missing NUMBER := 0;
457
458 -- Message for too many missing rows
459 l_too_many_rows_msg VARCHAR2 (1000);
460
461 -- General record for missing setups
462 TYPE opi_dbi_loc_setup_missing_rec is RECORD (
463 org VARCHAR2(300),
464 sub VARCHAR2(240),
465 loc VARCHAR2(400),
466 wt_missing_flag NUMBER,
467 vol_missing_flag NUMBER);
468 locator_setup_missing_rec opi_dbi_loc_setup_missing_rec;
469
470 -- Count of how many locators are actually missing setups.
471 -- Cursor to list all the locators with missing weight and/or
472 -- volume capacity.
473
474 CURSOR loc_setup_missing_count_csr IS
475 SELECT
476 count (*)
477 FROM mtl_parameters mp,
478 mtl_item_locations mil
479 WHERE mp.organization_id = mil.organization_id
480 AND mp.wms_enabled_flag = 'Y'
481 AND mp.process_enabled_flag <> 'Y'
482 AND ( mil.max_weight IS NULL
483 OR mil.max_cubic_area IS NULL
484 OR mil.location_weight_uom_code IS NULL
485 OR mil.volume_uom_code IS NULL);
486
487 -- Cursor to list all the locators with missing weight and/or
488 -- volume capacity.
489 TYPE locator_setup_missing_cursor IS REF CURSOR;
490 locator_setup_missing_csr locator_setup_missing_cursor;
491
492 -- Missing setup SQL
493 l_locator_setup_missing_sql CONSTANT VARCHAR2 (4000) :=
494 'SELECT
495 (orgs.name || '' ('' || mp.organization_code || '')'') org,
496 mil.subinventory_code sub,
497 INV_PROJECT.get_locator (mil.inventory_location_id,
498 mil.organization_id) loc,
499 CASE
500 WHEN mil.max_weight IS NULL OR
501 mil.location_weight_uom_code IS NULL THEN
502 1 /*C_WT_MISSING*/
503 ELSE
504 0 /*C_NOTHING_MISSING*/
505 END wt_missing_flag,
506 CASE
507 WHEN mil.max_cubic_area IS NULL OR
508 mil.volume_uom_code IS NULL THEN
509 2 /*C_VOL_MISSING*/
510 ELSE
511 0 /*C_NOTHING_MISSING*/
512 END vol_missing_flag
513 FROM mtl_parameters mp,
514 mtl_item_locations mil,
515 hr_all_organization_units_vl orgs
516 WHERE mp.organization_id = orgs.organization_id
517 AND mp.organization_id = mil.organization_id
518 AND mp.wms_enabled_flag = ''Y''
519 AND mp.process_enabled_flag <> ''Y''
520 AND ( mil.max_weight IS NULL
521 OR mil.max_cubic_area IS NULL
522 OR mil.location_weight_uom_code IS NULL
523 OR mil.volume_uom_code IS NULL)
524 ORDER BY
525 (orgs.name || '' ('' || mp.organization_code || '')''),
526 mil.subinventory_code,
527 INV_PROJECT.get_locator (mil.inventory_location_id,
528 mil.organization_id)';
529
530 BEGIN
531
532 -- Initialization Block
533 l_stmt_id := 0;
534 l_local_retcode := C_SUCCESS;
535 l_wt_missing_marker := NULL;
536 l_wt_ok_marker := NULL;
537 l_vol_missing_marker := NULL;
538 l_vol_ok_marker := NULL;
539 l_line_print := NULL;
540
541 -- Column widths for each column.
542 l_stmt_id := 10;
543 l_org_col_width := C_ORG_COL_WIDTH;
544 l_sub_col_width := C_SUB_COL_WIDTH;
545 l_loc_col_width := C_LOCATOR_COL_WIDTH;
546 l_vol_col_width := C_VOL_MISSING_COL_WIDTH;
547 l_wt_col_width := C_WT_MISSING_COL_WIDTH;
548
549 -- Column Headers
550 l_stmt_id := 20;
551 l_org_col_header := substr ('Organization', 1, l_org_col_width);
552
553 l_sub_col_header := FND_MESSAGE.get_string ('OPI', 'OPI_DBI_SUB_COL_HDR');
554 l_sub_col_header := substr (l_sub_col_header, 1, l_sub_col_width);
555
556 l_loc_col_header := FND_MESSAGE.get_string (
557 'OPI', 'OPI_DBI_LOCATOR_COL_HDR');
558 l_loc_col_header := substr (l_loc_col_header, 1, l_loc_col_width);
559
560 l_vol_col_header := FND_MESSAGE.get_string (
561 'OPI', 'OPI_DBI_VOL_SETUP_COL_HDR');
562 l_vol_col_header := substr (l_vol_col_header, 1, l_vol_col_width);
563
564 l_wt_col_header := FND_MESSAGE.get_string (
565 'OPI', 'OPI_DBI_WT_SETUP_COL_HDR');
566 l_wt_col_header := substr (l_wt_col_header, 1, l_wt_col_width);
567
568 -- Check how many rows need to be fixed.
569 OPEN loc_setup_missing_count_csr;
570 FETCH loc_setup_missing_count_csr INTO l_num_rows_missing;
571 CLOSE loc_setup_missing_count_csr;
572
573 -- Only print out stuff if there is something missing
574 IF (l_num_rows_missing > 0) THEN
575
576 -- Locator setup missing header message
577 l_stmt_id := 30;
578 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
579
580 l_loc_missing_header := FND_MESSAGE.get_string (
581 'OPI', 'OPI_DBI_LOC_WT_VOL_MISSING');
582 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_loc_missing_header);
583 -- Locator setup missing fix message. Print it.
584 l_stmt_id := 40;
585 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
586 l_loc_missing_fix := FND_MESSAGE.get_string (
587 'OPI', 'OPI_DBI_LOC_WT_VOL_FIX');
588 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_loc_missing_fix);
589
590 -- Only print out details if not too long. Else print out SQL
591 IF (l_num_rows_missing <= C_NUM_ROWS_TO_REPORT) THEN
592
593
594 -- Print out the headers padded out with the right number of spaces
595 l_stmt_id := 50;
596 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
597 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
598 rpad (l_org_col_header,
599 l_org_col_width + C_COL_SPACING) ||
600 rpad (l_sub_col_header,
601 l_sub_col_width + C_COL_SPACING) ||
602 rpad (l_loc_col_header,
603 l_loc_col_width + C_COL_SPACING) ||
604 rpad (l_vol_col_header,
605 l_vol_col_width + C_COL_SPACING) ||
606 rpad (l_wt_col_header,
607 l_wt_col_width + C_COL_SPACING) );
608
609 -- Underline the header with dashes
610 l_stmt_id := 60;
611 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
612 rpad ('-', l_org_col_width, '-') ||
613 rpad (' ', C_COL_SPACING) ||
614 rpad ('-', l_sub_col_width, '-') ||
615 rpad (' ', C_COL_SPACING) ||
616 rpad ('-', l_loc_col_width, '-') ||
617 rpad (' ', C_COL_SPACING) ||
618 rpad ('-', l_vol_col_width, '-') ||
619 rpad (' ', C_COL_SPACING) ||
620 rpad ('-', l_wt_col_width, '-') ||
621 rpad (' ', C_COL_SPACING) );
622
623 -- Markers for missing weight and volume
624 l_stmt_id := 70;
625 l_wt_missing_marker :=
626 substr (FND_MESSAGE.get_string ('OPI', 'OPI_DBI_UNDEF_STR'),
627 1, l_wt_col_width);
628 l_wt_ok_marker :=
629 substr (FND_MESSAGE.get_string ('OPI', 'OPI_DBI_OK_STR'),
630 1, l_vol_col_width);
631 l_vol_missing_marker :=
632 substr (FND_MESSAGE.get_string ('OPI', 'OPI_DBI_UNDEF_STR'),
633 1, l_wt_col_width);
634 l_vol_ok_marker :=
635 substr (FND_MESSAGE.get_string ('OPI', 'OPI_DBI_OK_STR'),
636 1, l_vol_col_width);
637
638 -- Report for every missing setup.
639 l_stmt_id := 80;
640 OPEN locator_setup_missing_csr FOR l_locator_setup_missing_sql;
641 FETCH locator_setup_missing_csr INTO locator_setup_missing_rec;
642 WHILE locator_setup_missing_csr%FOUND
643 LOOP
644
645 -- Every line will have an org/sub/loc.
646 l_stmt_id := 90;
647 l_line_print :=
648 rpad (substr (locator_setup_missing_rec.org, 1,
649 l_org_col_width),
650 l_org_col_width + C_COL_SPACING) ||
651 rpad (substr (locator_setup_missing_rec.sub, 1,
652 l_sub_col_width),
653 l_sub_col_width + C_COL_SPACING) ||
654 rpad (substr (locator_setup_missing_rec.loc, 1,
655 l_loc_col_width),
656 l_loc_col_width + C_COL_SPACING);
657
658 -- Figure out if the volume is missing
659 l_stmt_id := 100;
660 IF (locator_setup_missing_rec.vol_missing_flag = C_VOL_MISSING)
661 THEN
662 l_line_print :=
663 l_line_print || rpad (l_vol_missing_marker,
664 l_vol_col_width + C_COL_SPACING);
665 ELSE
666 l_line_print :=
667 l_line_print || rpad (l_vol_ok_marker,
668 l_vol_col_width + C_COL_SPACING);
669 END IF;
670
671 -- Figure out if the weight is missing
672 l_stmt_id := 110;
673 IF (locator_setup_missing_rec.wt_missing_flag = C_WT_MISSING)
674 THEN
675 l_line_print :=
676 l_line_print || rpad (l_wt_missing_marker,
677 l_wt_col_width + C_COL_SPACING);
678 ELSE
679 l_line_print :=
680 l_line_print || rpad (l_wt_ok_marker,
681 l_wt_col_width + C_COL_SPACING);
682 END IF;
683
684 -- Print this missing rate line.
685 l_stmt_id := 120;
686 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_line_print);
687
688 -- Get the next row.
689 FETCH locator_setup_missing_csr INTO locator_setup_missing_rec;
690
691 END LOOP;
692
693 CLOSE locator_setup_missing_csr;
694
695 ELSIF (l_num_rows_missing > C_NUM_ROWS_TO_REPORT) THEN
696 l_too_many_rows_msg := FND_MESSAGE.get_string (
697 'OPI', 'OPI_DBI_WT_VOL_SETUP_TOOMANY');
698 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_too_many_rows_msg);
699 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
700 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_locator_setup_missing_sql);
701 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
702 END IF;
703
704 END IF; -- l_num_rows_to_report > 0
705
706 IF (l_num_rows_missing > 0) THEN
707 -- Must issue warning since we found something.
708 l_stmt_id := 130;
709 l_local_retcode := C_WARNING;
710 END IF;
711
712
713 errbuf := '';
714 retcode := l_local_retcode;
715
716 return;
717
718 EXCEPTION
719
720 WHEN OTHERS THEN
721 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
722 l_stmt_id));
723 errbuf := SQLERRM;
724 retcode := C_ERROR;
725 return;
726
727 END report_locator_setup_missing;
728
729 /* report_item_conv_rate_err
730
731 Report item level conversion rate errors.
732
733 Providing this function the signature of retcode, errbuf since
734 it may need to be a standalone conc prog.
735 */
736 PROCEDURE report_item_conv_rate_err (errbuf OUT NOCOPY VARCHAR2,
737 retcode OUT NOCOPY NUMBER)
738 IS
739
740 l_proc_name CONSTANT VARCHAR2 (40) := 'report_item_conv_rate_err';
741 l_stmt_id NUMBER;
742
743 -- Cursor to get missing volume conversion rates
744 CURSOR missing_vol_conv_csr IS
745 SELECT
746 (orgs.name || ' (' || mp.organization_code || ')') org,
747 items.value item,
748 errors.volume_uom_code
749 FROM
750 (SELECT
751 organization_id,
752 inventory_item_id || '-' || organization_id item_org_id,
753 unit_volume_uom_code volume_uom_code
754 FROM opi_dbi_wms_stor_item_conv_stg
755 WHERE volume_conv_rate < 0
756 UNION
757 SELECT
758 organization_id,
759 item_org_id,
760 volume_uom_code
761 FROM opi_dbi_wms_curr_utz_item_f
762 WHERE utilized_volume/volume_qty < 0
763 AND aggregation_level_flag = C_ITEM_AGGR_LEVEL
764 ) errors,
765 eni_oltp_item_star items,
766 hr_all_organization_units_vl orgs,
767 mtl_parameters mp
768 WHERE errors.organization_id = orgs.organization_id
769 AND errors.organization_id = items.organization_id
770 AND errors.item_org_id = items.id
771 AND errors.organization_id = mp.organization_id
772 ORDER BY
773 (orgs.name || ' (' || mp.organization_code || ')'),
774 items.value;
775
776 -- Cursor to get missing weight conversion rates
777 CURSOR missing_wt_conv_csr IS
778 SELECT
779 (orgs.name || ' (' || mp.organization_code || ')') org,
780 items.value item,
781 errors.weight_uom_code
782 FROM
783 (SELECT
784 organization_id,
785 inventory_item_id || '-' || organization_id item_org_id,
786 unit_weight_uom_code weight_uom_code
787 FROM opi_dbi_wms_stor_item_conv_stg
788 WHERE weight_conv_rate < 0
789 UNION
790 SELECT
791 organization_id,
792 item_org_id,
793 weight_uom_code
794 FROM opi_dbi_wms_curr_utz_item_f
795 WHERE stored_weight/weight_qty < 0
796 AND aggregation_level_flag = C_ITEM_AGGR_LEVEL
797 ) errors,
798 eni_oltp_item_star items,
799 hr_all_organization_units_vl orgs,
800 mtl_parameters mp
801 WHERE errors.organization_id = orgs.organization_id
802 AND errors.organization_id = items.organization_id
803 AND errors.item_org_id = items.id
804 AND errors.organization_id = mp.organization_id
805 ORDER BY
806 (orgs.name || ' (' || mp.organization_code || ')'),
807 items.value;
808
809 -- Cursor to get the reporting UOM.
810 CURSOR get_rep_uom_csr (p_measure_code IN VARCHAR2)
811 IS
812 SELECT rep_uom_code
813 FROM opi_dbi_rep_uoms
814 WHERE measure_code = p_measure_code;
815
816 -- place holder for UOM.
817 l_uom VARCHAR2 (3);
818
819 -- Boolean to track the header has been printed.
820 l_header_printed BOOLEAN;
821
822 -- Header/table display parameters.
823 l_org_col_width NUMBER;
824 l_item_col_width NUMBER;
825 l_vol_col_width NUMBER;
826 l_wt_col_width NUMBER;
827 l_org_col_header VARCHAR2 (80);
828 l_item_col_header VARCHAR2 (80);
829 l_vol_col_header VARCHAR2 (80);
830 l_wt_col_header VARCHAR2 (80);
831
832 BEGIN
833
834 -- Initialization block
835 l_stmt_id := 0;
836 l_uom := NULL;
837 l_header_printed := FALSE;
838
839 -- Column widths
840 l_stmt_id := 10;
841 l_org_col_width := C_ORG_COL_WIDTH;
842 l_item_col_width := C_ITEM_COL_WIDTH;
843 l_vol_col_width := C_VOL_UOM_COL_WIDTH;
844 l_wt_col_width := C_WT_UOM_COL_WIDTH;
845
846 -- Column Headers
847 l_stmt_id := 20;
848 l_org_col_header := substr ('Organization', 1, l_org_col_width);
849
850 l_item_col_header := FND_MESSAGE.get_string (
851 'OPI', 'OPI_DBI_ITEM_COL_HDR');
852 l_item_col_header := substr (l_item_col_header, 1, l_item_col_width);
853
854 l_vol_col_header := FND_MESSAGE.get_string (
855 'OPI', 'OPI_DBI_ITEM_VOL_UOM_COL_HDR');
856 l_vol_col_header := substr (l_vol_col_header, 1, l_vol_col_width);
857
858 l_wt_col_header := FND_MESSAGE.get_string (
859 'OPI', 'OPI_DBI_ITEM_WT_UOM_COL_HDR');
860 l_wt_col_header := substr (l_wt_col_header, 1, l_wt_col_width);
861
862 -- Get the volume UOM code
863 l_stmt_id := 10;
864 OPEN get_rep_uom_csr ('VOL');
865 FETCH get_rep_uom_csr INTO l_uom;
866 IF (get_rep_uom_csr%NOTFOUND) THEN
867 l_uom := NULL;
868 END IF;
869 CLOSE get_rep_uom_csr;
870
871 -- Report on the volume errors only if the volume reporting UOM
872 -- has been defined.
873 l_stmt_id := 20;
874 IF (l_uom IS NOT NULL) THEN
875
876 -- No header printed yet
877 l_header_printed := FALSE;
878
879 -- Print all missing rates;
880 l_stmt_id := 30;
881 FOR missing_vol_conv_rec IN missing_vol_conv_csr
882 LOOP
883
884 -- If something was found, then print the header messsage.
885 l_stmt_id := 40;
886 IF (l_header_printed = FALSE) THEN
887
888 -- Blank line.
889 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
890 -- Locator volume missing header
891 FND_FILE.PUT_LINE (
892 FND_FILE.OUTPUT,
893 FND_MESSAGE.get_string ('OPI',
894 'OPI_DBI_ITEM_VOL_CONV_ERR_HDR'));
895
896 -- Blank line.
897 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
898 -- Volume reporting UOM.
899 FND_FILE.PUT_LINE (
900 FND_FILE.OUTPUT,
901 FND_MESSAGE.get_string ('OPI', 'OPI_DBI_REP_UOM_VOL') ||
902 ' ' || l_uom);
903
904
905 -- Blank line.
906 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
907 -- Table header row
908 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
909 rpad (l_org_col_header,
910 l_org_col_width + C_COL_SPACING) ||
911 rpad (l_item_col_header,
912 l_item_col_width + C_COL_SPACING) ||
913 rpad (l_vol_col_header,
914 l_vol_col_width + C_COL_SPACING));
915
916 -- Table header underline
917 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
918 rpad ('-', l_org_col_width, '-') ||
919 rpad (' ', C_COL_SPACING) ||
920 rpad ('-', l_item_col_width, '-') ||
921 rpad (' ', C_COL_SPACING) ||
922 rpad ('-', l_vol_col_width, '-') ||
923 rpad (' ', C_COL_SPACING) );
924
925
926 l_header_printed := TRUE;
927
928 END IF;
929
930
931 -- Output every row.
932 l_stmt_id := 50;
933 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
934 rpad (substr (missing_vol_conv_rec.org, 1,
935 l_org_col_width),
936 l_org_col_width + C_COL_SPACING) ||
937 rpad (substr (missing_vol_conv_rec.item, 1,
938 l_item_col_width),
939 l_item_col_width + C_COL_SPACING) ||
940 rpad (substr (missing_vol_conv_rec.volume_uom_code, 1,
941 l_vol_col_width),
942 l_vol_col_width + C_COL_SPACING));
943
944 END LOOP;
945
946 -- Footer for this section
947 l_stmt_id := 60;
948 IF (l_header_printed = TRUE) THEN
949 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
950 OPI_DBI_REP_UOM_PKG.err_msg_footer;
951 END IF;
952
953 END IF;
954
955 -- Get the volume UOM code
956 l_stmt_id := 100;
957 OPEN get_rep_uom_csr ('WT');
958 FETCH get_rep_uom_csr INTO l_uom;
959 IF (get_rep_uom_csr%NOTFOUND) THEN
960 l_uom := NULL;
961 END IF;
962 CLOSE get_rep_uom_csr;
963
964 -- Report on the volume errors only if the volume reporting UOM
965 -- has been defined.
966 l_stmt_id := 120;
967 IF (l_uom IS NOT NULL) THEN
968
969 -- No header printed yet
970 l_header_printed := FALSE;
971
972 -- Print all missing rates;
973 l_stmt_id := 130;
974 FOR missing_wt_conv_rec IN missing_wt_conv_csr
975 LOOP
976
977 -- If something was found, then print the header messsage.
978 l_stmt_id := 140;
979 IF (l_header_printed = FALSE) THEN
980
981
982 -- Blank line.
983 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
984 -- Locator volume missing header
985 FND_FILE.PUT_LINE (
986 FND_FILE.OUTPUT,
987 FND_MESSAGE.get_string ('OPI',
988 'OPI_DBI_ITEM_WT_CONV_ERR_HDR'));
989
990 -- Blank line.
991 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
992 -- Volume reporting UOM.
993 FND_FILE.PUT_LINE (
994 FND_FILE.OUTPUT,
995 FND_MESSAGE.get_string ('OPI', 'OPI_DBI_REP_UOM_WT') ||
996 ' ' || l_uom);
997
998 -- Blank line.
999 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
1000 -- Table header row
1001 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1002 rpad (l_org_col_header,
1003 l_org_col_width + C_COL_SPACING) ||
1004 rpad (l_item_col_header,
1005 l_item_col_width + C_COL_SPACING) ||
1006 rpad (l_vol_col_header,
1007 l_vol_col_width + C_COL_SPACING));
1008
1009 -- Table header underline
1010 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1011 rpad ('-', l_org_col_width, '-') ||
1012 rpad (' ', C_COL_SPACING) ||
1013 rpad ('-', l_item_col_width, '-') ||
1014 rpad (' ', C_COL_SPACING) ||
1015 rpad ('-', l_vol_col_width, '-') ||
1016 rpad (' ', C_COL_SPACING) );
1017
1018
1019 l_header_printed := TRUE;
1020
1021 END IF;
1022
1023
1024 -- Output every row.
1025 l_stmt_id := 150;
1026 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1027 rpad (substr (missing_wt_conv_rec.org, 1,
1028 l_org_col_width),
1029 l_org_col_width + C_COL_SPACING) ||
1030 rpad (substr (missing_wt_conv_rec.item, 1,
1031 l_item_col_width),
1032 l_item_col_width + C_COL_SPACING) ||
1033 rpad (substr (missing_wt_conv_rec.weight_uom_code, 1,
1034 l_vol_col_width),
1035 l_vol_col_width + C_COL_SPACING));
1036
1037 END LOOP;
1038
1039 -- Footer for this section
1040 l_stmt_id := 160;
1041 IF (l_header_printed = TRUE) THEN
1042 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
1043 OPI_DBI_REP_UOM_PKG.err_msg_footer;
1044 END IF;
1045
1046 END IF;
1047
1048
1049
1050 errbuf := '';
1051 retcode := C_SUCCESS;
1052 return;
1053
1054 EXCEPTION
1055
1056 WHEN OTHERS THEN
1057 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1058 l_stmt_id));
1059 errbuf := SQLERRM;
1060 retcode := C_ERROR;
1061 RAISE ITEM_CONV_RATES_DET_ERR;
1062 return;
1063
1064 END report_item_conv_rate_err;
1065
1066
1067 /* report_locator_conv_rate_err
1068
1069 Report locator level conversion rate errors.
1070
1071 Providing this function the signature of retcode, errbuf since
1072 it may need to be a standalone conc prog.
1073 */
1074 PROCEDURE report_locator_conv_rate_err (errbuf OUT NOCOPY VARCHAR2,
1075 retcode OUT NOCOPY NUMBER)
1076 IS
1077
1078 l_proc_name CONSTANT VARCHAR2 (40) := 'report_locator_conv_rate_err';
1079 l_stmt_id NUMBER;
1080
1081 -- Report all missing volumes at the locator level.
1082 CURSOR missing_vol_conv_csr IS
1083 SELECT
1084 (orgs.name || ' (' || mp.organization_code || ')') org,
1085 stg.subinventory_code sub,
1086 INV_PROJECT.get_locator (stg.locator_id, stg.organization_id)
1087 loc,
1088 stg.volume_uom_code
1089 FROM opi_dbi_wms_curr_utz_sub_stg stg,
1090 mtl_parameters mp,
1091 hr_all_organization_units_vl orgs
1092 WHERE mp.organization_id = orgs.organization_id
1093 AND mp.wms_enabled_flag = 'Y'
1094 AND mp.process_enabled_flag <> 'Y'
1095 AND mp.organization_id = stg.organization_id
1096 AND stg.volume_capacity_rep < 0
1097 ORDER BY
1098 (orgs.name || ' (' || mp.organization_code || ')'),
1099 stg.subinventory_code,
1100 INV_PROJECT.get_locator (stg.locator_id, stg.organization_id);
1101
1102 -- Report all missing weights at the locator level.
1103 CURSOR missing_wt_conv_csr IS
1104 SELECT
1105 (orgs.name || ' (' || mp.organization_code || ')') org,
1106 stg.subinventory_code sub,
1107 INV_PROJECT.get_locator (stg.locator_id, stg.organization_id)
1108 loc,
1109 stg.weight_uom_code
1110 FROM opi_dbi_wms_curr_utz_sub_stg stg,
1111 mtl_parameters mp,
1112 hr_all_organization_units_vl orgs
1113 WHERE mp.organization_id = orgs.organization_id
1114 AND mp.wms_enabled_flag = 'Y'
1115 AND mp.process_enabled_flag <> 'Y'
1116 AND mp.organization_id = stg.organization_id
1117 AND stg.weight_capacity_rep < 0
1118 ORDER BY
1119 (orgs.name || ' (' || mp.organization_code || ')'),
1120 stg.subinventory_code,
1121 INV_PROJECT.get_locator (stg.locator_id, stg.organization_id);
1122
1123 -- Cursor to get the reporting UOM.
1124 CURSOR get_rep_uom_csr (p_measure_code IN VARCHAR2)
1125 IS
1126 SELECT rep_uom_code
1127 FROM opi_dbi_rep_uoms
1128 WHERE measure_code = p_measure_code;
1129
1130 -- place holder for UOM.
1131 l_uom VARCHAR2 (3);
1132
1133 -- Boolean to track the header has been printed.
1134 l_header_printed BOOLEAN;
1135
1136 -- Header/table display parameters.
1137 l_org_col_width NUMBER;
1138 l_sub_col_width NUMBER;
1139 l_loc_col_width NUMBER;
1140 l_vol_col_width NUMBER;
1141 l_wt_col_width NUMBER;
1142 l_org_col_header VARCHAR2 (80);
1143 l_sub_col_header VARCHAR2 (80);
1144 l_loc_col_header VARCHAR2 (80);
1145 l_vol_col_header VARCHAR2 (80);
1146 l_wt_col_header VARCHAR2 (80);
1147
1148 BEGIN
1149
1150 -- Initialization block
1151 l_stmt_id := 0;
1152 l_uom := NULL;
1153 l_header_printed := FALSE;
1154
1155 -- Column widths
1156 l_stmt_id := 10;
1157 l_org_col_width := C_ORG_COL_WIDTH;
1158 l_sub_col_width := C_SUB_COL_WIDTH;
1159 l_loc_col_width := C_LOCATOR_COL_WIDTH;
1160 l_vol_col_width := C_VOL_UOM_COL_WIDTH;
1161 l_wt_col_width := C_WT_UOM_COL_WIDTH;
1162
1163
1164 -- Column Headers
1165 l_stmt_id := 20;
1166 l_org_col_header := substr ('Organization', 1, l_org_col_width);
1167
1168 l_sub_col_header := FND_MESSAGE.get_string ('OPI', 'OPI_DBI_SUB_COL_HDR');
1169 l_sub_col_header := substr (l_sub_col_header, 1, l_sub_col_width);
1170
1171 l_loc_col_header := FND_MESSAGE.get_string (
1172 'OPI', 'OPI_DBI_LOCATOR_COL_HDR');
1173 l_loc_col_header := substr (l_loc_col_header, 1, l_loc_col_width);
1174
1175 l_vol_col_header := FND_MESSAGE.get_string (
1176 'OPI', 'OPI_DBI_LOC_VOL_UOM_COL_HDR');
1177 l_vol_col_header := substr (l_vol_col_header, 1, l_vol_col_width);
1178
1179 l_wt_col_header := FND_MESSAGE.get_string (
1180 'OPI', 'OPI_DBI_LOC_WT_UOM_COL_HDR');
1181 l_wt_col_header := substr (l_wt_col_header, 1, l_wt_col_width);
1182
1183 -- Get the volume UOM code
1184 l_stmt_id := 10;
1185 OPEN get_rep_uom_csr ('VOL');
1186 FETCH get_rep_uom_csr INTO l_uom;
1187 IF (get_rep_uom_csr%NOTFOUND) THEN
1188 l_uom := NULL;
1189 END IF;
1190 CLOSE get_rep_uom_csr;
1191
1192 -- Report on the volume errors only if the volume reporting UOM
1193 -- has been defined.
1194 l_stmt_id := 20;
1195 IF (l_uom IS NOT NULL) THEN
1196
1197 -- No header printed yet
1198 l_header_printed := FALSE;
1199
1200 -- Print all missing rates;
1201 l_stmt_id := 30;
1202 FOR missing_vol_conv_rec IN missing_vol_conv_csr
1203 LOOP
1204
1205 -- If something was found, then print the header messsage.
1206 l_stmt_id := 40;
1207 IF (l_header_printed = FALSE) THEN
1208
1209 -- Blank line.
1210 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
1211 -- Volume reporting UOM.
1212 FND_FILE.PUT_LINE (
1213 FND_FILE.OUTPUT,
1214 FND_MESSAGE.get_string ('OPI', 'OPI_DBI_REP_UOM_VOL') ||
1215 ' ' || l_uom);
1216
1217 -- Blank line.
1218 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
1219 -- Locator volume missing header
1220 FND_FILE.PUT_LINE (
1221 FND_FILE.OUTPUT,
1222 FND_MESSAGE.get_string ('OPI',
1223 'OPI_DBI_LOC_VOL_CONV_ERR_HDR'));
1224
1225 -- Blank line.
1226 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
1227 -- Volume reporting UOM.
1228 FND_FILE.PUT_LINE (
1229 FND_FILE.OUTPUT,
1230 FND_MESSAGE.get_string ('OPI', 'OPI_DBI_REP_UOM_VOL') ||
1231 ' ' || l_uom);
1232
1233 -- Blank line.
1234 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
1235 -- Table header row
1236 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1237 rpad (l_org_col_header,
1238 l_org_col_width + C_COL_SPACING) ||
1239 rpad (l_sub_col_header,
1240 l_sub_col_width + C_COL_SPACING) ||
1241 rpad (l_loc_col_header,
1242 l_loc_col_width + C_COL_SPACING) ||
1243 rpad (l_vol_col_header,
1244 l_vol_col_width + C_COL_SPACING));
1245
1246 -- Table header underline
1247 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1248 rpad ('-', l_org_col_width, '-') ||
1249 rpad (' ', C_COL_SPACING) ||
1250 rpad ('-', l_sub_col_width, '-') ||
1251 rpad (' ', C_COL_SPACING) ||
1252 rpad ('-', l_loc_col_width, '-') ||
1253 rpad (' ', C_COL_SPACING) ||
1254 rpad ('-', l_vol_col_width, '-') ||
1255 rpad (' ', C_COL_SPACING) );
1256
1257
1258 l_header_printed := TRUE;
1259
1260 END IF;
1261
1262
1263 -- Output every row.
1264 l_stmt_id := 50;
1265 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1266 rpad (substr (missing_vol_conv_rec.org, 1,
1267 l_org_col_width),
1268 l_org_col_width + C_COL_SPACING) ||
1269 rpad (substr (missing_vol_conv_rec.sub, 1,
1270 l_sub_col_width),
1271 l_sub_col_width + C_COL_SPACING) ||
1272 rpad (substr (missing_vol_conv_rec.loc, 1,
1273 l_loc_col_width),
1274 l_loc_col_width + C_COL_SPACING) ||
1275 rpad (substr (missing_vol_conv_rec.volume_uom_code, 1,
1276 l_vol_col_width),
1277 l_vol_col_width + C_COL_SPACING));
1278
1279 END LOOP;
1280
1281 -- Footer for this section
1282 l_stmt_id := 60;
1283 IF (l_header_printed = TRUE) THEN
1284 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
1285 OPI_DBI_REP_UOM_PKG.err_msg_footer;
1286 END IF;
1287
1288 END IF;
1289
1290 -- Get the volume UOM code
1291 l_stmt_id := 90;
1292 OPEN get_rep_uom_csr ('WT');
1293 FETCH get_rep_uom_csr INTO l_uom;
1294 IF (get_rep_uom_csr%NOTFOUND) THEN
1295 l_uom := NULL;
1296 END IF;
1297 CLOSE get_rep_uom_csr;
1298
1299 -- Report on the weight errors only if the weight reporting UOM
1300 -- has been defined.
1301 l_stmt_id := 100;
1302 IF (l_uom IS NOT NULL) THEN
1303
1304 -- No header printed yet
1305 l_header_printed := FALSE;
1306
1307 -- Print all missing rates;
1308 l_stmt_id := 110;
1309 FOR missing_wt_conv_rec IN missing_wt_conv_csr
1310 LOOP
1311
1312 -- If something was found, then print the header messsage.
1313 l_stmt_id := 120;
1314 IF (l_header_printed = FALSE) THEN
1315
1316 -- Blank line.
1317 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
1318 -- Locator weight missing header
1319 FND_FILE.PUT_LINE (
1320 FND_FILE.OUTPUT,
1321 FND_MESSAGE.get_string ('OPI',
1322 'OPI_DBI_LOC_WT_CONV_ERR_HDR'));
1323
1324 -- Blank line.
1325 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
1326 -- Weight reporting UOM.
1327 FND_FILE.PUT_LINE (
1328 FND_FILE.OUTPUT,
1329 FND_MESSAGE.get_string ('OPI', 'OPI_DBI_REP_UOM_WT') ||
1330 ' ' || l_uom);
1331
1332 -- Blank line.
1333 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
1334 -- Table header row
1335 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1336 rpad (l_org_col_header,
1337 l_org_col_width + C_COL_SPACING) ||
1338 rpad (l_sub_col_header,
1339 l_sub_col_width + C_COL_SPACING) ||
1340 rpad (l_loc_col_header,
1341 l_loc_col_width + C_COL_SPACING) ||
1342 rpad (l_wt_col_header,
1343 l_wt_col_width + C_COL_SPACING));
1344
1345 -- Table header underline
1346 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1347 rpad ('-', l_org_col_width, '-') ||
1348 rpad (' ', C_COL_SPACING) ||
1349 rpad ('-', l_sub_col_width, '-') ||
1350 rpad (' ', C_COL_SPACING) ||
1351 rpad ('-', l_loc_col_width, '-') ||
1352 rpad (' ', C_COL_SPACING) ||
1353 rpad ('-', l_wt_col_width, '-') ||
1354 rpad (' ', C_COL_SPACING) );
1355
1356 l_header_printed := TRUE;
1357
1358 END IF;
1359
1360 -- Output every row.
1361 l_stmt_id := 130;
1362 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1363 rpad (substr (missing_wt_conv_rec.org, 1,
1364 l_org_col_width),
1365 l_org_col_width + C_COL_SPACING) ||
1366 rpad (substr (missing_wt_conv_rec.sub, 1,
1367 l_sub_col_width),
1368 l_sub_col_width + C_COL_SPACING) ||
1369 rpad (substr (missing_wt_conv_rec.loc, 1,
1370 l_loc_col_width),
1371 l_loc_col_width + C_COL_SPACING) ||
1372 rpad (substr (missing_wt_conv_rec.weight_uom_code, 1,
1373 l_wt_col_width),
1374 l_wt_col_width + C_COL_SPACING));
1375
1376 END LOOP;
1377
1378 -- Footer for this section
1379 l_stmt_id := 140;
1380 IF (l_header_printed = TRUE) THEN
1381 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, C_BLANK_LINE);
1382 OPI_DBI_REP_UOM_PKG.err_msg_footer;
1383 END IF;
1384
1385 END IF;
1386
1387 errbuf := '';
1388 retcode := C_SUCCESS;
1389 return;
1390
1391 EXCEPTION
1392
1393 WHEN OTHERS THEN
1394 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1395 l_stmt_id));
1396 errbuf := SQLERRM;
1397 retcode := C_ERROR;
1398 RAISE LOC_CONV_RATES_DET_ERR;
1399 return;
1400
1401 END report_locator_conv_rate_err;
1402
1403
1404 /* report_item_loc_conv_rate_err
1405
1406 Report the item level and locator level information for missing
1407 conversion rates found by the ETLs for the Warehouse Storage Utilized
1408 and Current Capacity Utilization reports.
1409
1410 This function is meant to be publicly accessed by a standalone
1411 concurrent program that the user can optionally run to debug
1412 their item/locator setups.
1413
1414 History:
1415 Date Author Action
1416 01/10/05 Dinkar Gupta Wrote Function.
1417
1418 */
1419 PROCEDURE report_item_loc_conv_rate_err (errbuf OUT NOCOPY VARCHAR2,
1420 retcode OUT NOCOPY NUMBER)
1421 IS
1422 l_proc_name CONSTANT VARCHAR2 (40) := 'report_item_loc_conv_rate_err';
1423
1424 l_stmt_id NUMBER;
1425
1426 BEGIN
1427
1428 -- Initialization block
1429 l_stmt_id := 0;
1430
1431
1432 -- print out the title
1433 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1434 FND_MESSAGE.get_string (
1435 'OPI', 'OPI_DBI_ITEM_LOC_CONV_ERR_HDR'));
1436
1437 -- Report the item level details.
1438 -- Volume and weight should be reported in two different sections.
1439 l_stmt_id := 10;
1440 report_item_conv_rate_err (errbuf, retcode);
1441 l_stmt_id := 20;
1442
1443 -- Report the locator level details.
1444 -- Volume and weight should be reported in two different sections.
1445 l_stmt_id := 30;
1446 report_locator_conv_rate_err (errbuf, retcode);
1447 l_stmt_id := 40;
1448
1449 errbuf := '';
1450 retcode := C_SUCCESS;
1451
1452 return;
1453
1454 EXCEPTION
1455
1456 WHEN ITEM_CONV_RATES_DET_ERR THEN
1457
1458 BIS_COLLECTION_UTILITIES.PUT_LINE (
1459 err_mesg (ITEM_CONV_RATES_DET_ERR_MESG,
1460 l_proc_name, l_stmt_id));
1461
1462 errbuf := C_ITEM_LOC_CONV_RATE_ERR;
1463 retcode := C_ERROR;
1464 return;
1465
1466 WHEN LOC_CONV_RATES_DET_ERR THEN
1467
1468 BIS_COLLECTION_UTILITIES.PUT_LINE (
1469 err_mesg (LOC_CONV_RATES_DET_ERR_MESG,
1470 l_proc_name, l_stmt_id));
1471
1472 errbuf := C_ITEM_LOC_CONV_RATE_ERR;
1473 retcode := C_ERROR;
1474 return;
1475
1476 WHEN OTHERS THEN
1477 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1478 l_stmt_id));
1479 errbuf := SQLERRM;
1480 retcode := C_ERROR;
1481 return;
1482
1483 END report_item_loc_conv_rate_err;
1484
1485 /* set_wdth_cu1_date
1486
1487 Get the CU1 date based on the data in the WMS_DISPATCHED_TASKS_HISTORY
1488 table. The CU1 date is the first one with transaction_temp_id not set
1489 as null.
1490
1491 If no such date is found, then set the sysdate to be the CU1 date.
1492
1493 In general since this API can be called simultaneously by multiple
1494 ETLs, it will merge the CU1 date into the OPI_DBI_CONC_PROG_RUN_LOG
1495 with type = 'WDTH_CU1_DATE'.
1496
1497 Parameters:
1498 p_overwrite - if true, then function always picks the date from WDTH.
1499 if false, then does nothing if a record already exists
1500 in the OPI_DBI_CONC_PROG_RUN_LOG.
1501
1502 History:
1503 Date Author Action
1504 01/10/05 Dinkar Gupta Wrote Function.
1505
1506 */
1507 PROCEDURE set_wdth_cu1_date (p_overwrite BOOLEAN)
1508 IS
1509
1510 -- Date from WDTH
1511 l_wdth_cu1_date DATE;
1512
1513 -- Check if CU1 date already exists
1514 l_cu1_date_exists BOOLEAN;
1515
1516 -- Existing CU1 date
1517 l_existing_cu1_date DATE;
1518
1519 -- Cursor to get existing CU1 date.
1520 CURSOR existing_cu1_date_csr IS
1521 SELECT last_run_date
1522 FROM opi_dbi_conc_prog_run_log
1523 WHERE ETL_TYPE = C_WDTH_CU1_DATE_TYPE;
1524
1525 -- Cursor to get the CU1 date from WDTH
1526 CURSOR wdth_cu1_date_csr IS
1527 SELECT /*+ parallel (wdth) */
1528 nvl (min (wdth.creation_date), sysdate)
1529 FROM wms_dispatched_tasks_history wdth
1530 WHERE transaction_temp_id IS NOT NULL;
1531
1532 -- Audit columns
1533 l_user_id NUMBER;
1534 l_login_id NUMBER;
1535 l_program_id NUMBER;
1536 l_program_login_id NUMBER;
1537 l_program_application_id NUMBER;
1538 l_request_id NUMBER;
1539
1540
1541 BEGIN
1542
1543 -- No CU1 existing date so far.
1544 l_cu1_date_exists := FALSE;
1545
1546 -- If not forced to overwrite, check if a record already exists.
1547 IF (p_overwrite = FALSE) THEN
1548
1549 OPEN existing_cu1_date_csr;
1550 FETCH existing_cu1_date_csr INTO l_existing_cu1_date;
1551 IF (existing_cu1_date_csr%FOUND) THEN
1552 l_cu1_date_exists := TRUE;
1553 END IF;
1554
1555 CLOSE existing_cu1_date_csr;
1556
1557 END IF;
1558
1559 -- Set the WDTH date if forced to overwrite or if no
1560 -- record exists.
1561 IF (p_overwrite = TRUE OR l_cu1_date_exists = FALSE) THEN
1562
1563 -- Get the CU1 date. Will default to sysdate.
1564 OPEN wdth_cu1_date_csr;
1565 FETCH wdth_cu1_date_csr INTO l_wdth_cu1_date;
1566 CLOSE wdth_cu1_date_csr;
1567
1568 -- Audit column information
1569 l_user_id := nvl(fnd_global.user_id, -1);
1570 l_login_id := nvl(fnd_global.login_id, -1);
1571 l_program_id := nvl (fnd_global.conc_program_id, -1);
1572 l_program_login_id := nvl (fnd_global.conc_login_id, -1);
1573 l_program_application_id := nvl (fnd_global.prog_appl_id, -1);
1574 l_request_id := nvl (fnd_global.conc_request_id, -1);
1575
1576
1577 -- Merge the date into the log
1578 MERGE INTO opi_dbi_conc_prog_run_log base
1579 USING
1580 (SELECT
1581 C_WDTH_CU1_DATE_TYPE etl_type,
1582 l_wdth_cu1_date last_run_date,
1583 sysdate creation_date,
1584 sysdate last_update_date,
1585 l_user_id created_by,
1586 l_user_id last_updated_by,
1587 l_login_id last_update_login,
1588 l_program_application_id program_application_id,
1589 l_program_id program_id,
1590 l_program_login_id program_login_id,
1591 l_request_id request_id
1592 FROM dual) new
1593 ON (base.etl_type = new.etl_type)
1594 WHEN MATCHED THEN UPDATE
1595 SET
1596 base.last_run_date = new.last_run_date,
1597 base.last_update_date = new.last_update_date,
1598 base.last_updated_by = new.last_updated_by,
1599 base.last_update_login = new.last_update_login,
1600 base.program_id = new.program_id,
1601 base.program_login_id = new.program_login_id,
1602 base.program_application_id = new.program_application_id,
1603 base.request_id = new.request_id
1604 WHEN NOT MATCHED THEN
1605 INSERT (
1606 base.etl_type,
1607 base.last_run_date,
1608 base.creation_date,
1609 base.last_update_date,
1610 base.created_by,
1611 base.last_updated_by,
1612 base.last_update_login,
1613 base.program_id,
1614 base.program_login_id,
1615 base.program_application_id,
1616 base.request_id
1617 )
1618 VALUES (
1619 new.etl_type,
1620 new.last_run_date,
1621 new.creation_date,
1622 new.last_update_date,
1623 new.created_by,
1624 new.last_updated_by,
1625 new.last_update_login,
1626 new.program_id,
1627 new.program_login_id,
1628 new.program_application_id,
1629 new.request_id);
1630
1631
1632 -- Commit date
1633 commit;
1634
1635 END IF;
1636
1637 END set_wdth_cu1_date;
1638
1639
1640 /* set_wms_pts_gsd
1641
1642 Set the WMS pick to ship rack start date as the max of the
1643 WDTH CU1 date and GSD with a type of 'WMS_PTS_GSD'. If the GSD is
1644 NULL, then set the pick to ship start date as NULL.
1645
1646 As a side effect, populates/updates the WDTH CU1 date as needed.
1647
1648 Also, we don't want to modify this date unless it is different,
1649 because otherwise MVs that depend on this might not fast refresh.
1650
1651 Parameters:
1652 p_overwrite - if true, then function force updates the WDTH CU1 date.
1653 if false, then WDTH CU1 date is not modified (if
1654 it already exists).
1655
1656 History:
1657 Date Author Action
1658 02/18/05 Dinkar Gupta Wrote Function.
1659
1660 */
1661 PROCEDURE set_wms_pts_gsd (p_overwrite BOOLEAN)
1662 IS
1663
1664 -- WDTH CU1 date
1665 CURSOR wdth_cu1_date_csr IS
1666 SELECT
1667 last_run_date
1668 FROM opi_dbi_conc_prog_run_log
1669 WHERE etl_type = C_WDTH_CU1_DATE_TYPE;
1670
1671 -- PTS ship date
1672 CURSOR wms_pts_gsd_csr IS
1673 SELECT
1674 last_run_date
1675 FROM opi_dbi_conc_prog_run_log
1676 WHERE etl_type = C_WMS_PTS_DATE_TYPE;
1677
1678
1679 -- Pick to Ship start date
1680 l_pts_start_date DATE;
1681 l_old_pts_start_date DATE;
1682
1683 -- GSD
1684 l_gsd DATE;
1685
1686 -- Audit columns
1687 l_user_id NUMBER;
1688 l_login_id NUMBER;
1689 l_program_id NUMBER;
1690 l_program_login_id NUMBER;
1691 l_program_application_id NUMBER;
1692 l_request_id NUMBER;
1693
1694 BEGIN
1695
1696 -- First set the WDTH CU1 date as requested
1697 set_wdth_cu1_date (p_overwrite);
1698
1699 -- get the GSD
1700 l_gsd := bis_common_parameters.get_global_start_date;
1701
1702 -- Pick to ship start date is max of GSD and WDTH CU1 date.
1703 OPEN wdth_cu1_date_csr;
1704 FETCH wdth_cu1_date_csr INTO l_pts_start_date;
1705
1706 IF (l_gsd IS NULL) THEN
1707 l_pts_start_date := NULL;
1708 ELSIF (wdth_cu1_date_csr%NOTFOUND OR
1709 l_pts_start_date IS NULL OR
1710 l_pts_start_date < l_gsd) THEN
1711 l_pts_start_date := l_gsd;
1712 END IF;
1713
1714 CLOSE wdth_cu1_date_csr;
1715
1716 -- Get the existing PTS start date.
1717 OPEN wms_pts_gsd_csr;
1718 FETCH wms_pts_gsd_csr INTO l_old_pts_start_date;
1719 CLOSE wms_pts_gsd_csr;
1720
1721 -- Merge the start date into the log table if it has not changed.
1722 IF (nvl (l_old_pts_start_date, to_date ('01-01-1951', 'DD-MM-YYYY')) <>
1723 nvl (l_pts_start_date, to_date ('01-01-1951', 'DD-MM-YYYY')) ) THEN
1724
1725 -- Audit column information
1726 l_user_id := nvl(fnd_global.user_id, -1);
1727 l_login_id := nvl(fnd_global.login_id, -1);
1728 l_program_id := nvl (fnd_global.conc_program_id, -1);
1729 l_program_login_id := nvl (fnd_global.conc_login_id, -1);
1730 l_program_application_id := nvl (fnd_global.prog_appl_id, -1);
1731 l_request_id := nvl (fnd_global.conc_request_id, -1);
1732
1733 -- Merge the date into the log
1734 MERGE INTO opi_dbi_conc_prog_run_log base
1735 USING
1736 (SELECT
1737 C_WMS_PTS_DATE_TYPE etl_type,
1738 l_pts_start_date last_run_date,
1739 sysdate creation_date,
1740 sysdate last_update_date,
1741 l_user_id created_by,
1742 l_user_id last_updated_by,
1743 l_login_id last_update_login,
1744 l_program_application_id program_application_id,
1745 l_program_id program_id,
1746 l_program_login_id program_login_id,
1747 l_request_id request_id
1748 FROM dual) new
1749 ON (base.etl_type = new.etl_type)
1750 WHEN MATCHED THEN UPDATE
1751 SET
1752 base.last_run_date = new.last_run_date,
1753 base.last_update_date = new.last_update_date,
1754 base.last_updated_by = new.last_updated_by,
1755 base.last_update_login = new.last_update_login,
1756 base.program_id = new.program_id,
1757 base.program_login_id = new.program_login_id,
1758 base.program_application_id = new.program_application_id,
1759 base.request_id = new.request_id
1760 WHEN NOT MATCHED THEN
1761 INSERT (
1762 base.etl_type,
1763 base.last_run_date,
1764 base.creation_date,
1765 base.last_update_date,
1766 base.created_by,
1767 base.last_updated_by,
1768 base.last_update_login,
1769 base.program_id,
1770 base.program_login_id,
1771 base.program_application_id,
1772 base.request_id
1773 )
1774 VALUES (
1775 new.etl_type,
1776 new.last_run_date,
1777 new.creation_date,
1778 new.last_update_date,
1779 new.created_by,
1780 new.last_updated_by,
1781 new.last_update_login,
1782 new.program_id,
1783 new.program_login_id,
1784 new.program_application_id,
1785 new.request_id);
1786
1787 -- Commit date
1788 commit;
1789
1790 END IF;
1791
1792 return;
1793
1794 END set_wms_pts_gsd;
1795
1796 function get_uom_rate (p_inventory_item_id varchar2,
1797 p_primary_uom_code varchar2,
1798 p_txn_uom_code varchar2) return number
1799 is
1800 l_ret number;
1801 begin
1802 l_ret := inv_convert.inv_um_convert(
1803 p_inventory_item_id, 5, 1,
1804 p_txn_uom_code,
1805 p_primary_uom_code,
1806 null, null
1807 );
1808 if (l_ret < 0) then
1809 if (not g_missing_uom) then
1810 bis_collection_utilities.writemissinguomheader;
1811 g_missing_uom := true;
1812 end if;
1813 bis_collection_utilities.writemissinguom(
1814 nvl(p_txn_uom_code,' '),
1815 nvl(p_primary_uom_code,' '),
1816 p_inventory_item_id
1817 );
1818 end if;
1819 return l_ret;
1820 end get_uom_rate;
1821
1822 END opi_dbi_wms_utility_pkg;