[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_CURR_INV_EXP_PKG
Source
1 PACKAGE BODY OPI_DBI_CURR_INV_EXP_PKG AS
2 /*$Header: OPIDECIEXPB.pls 120.3 2005/11/30 01:36:22 srayadur 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 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 -- Conversion rate related variables: global currency code and rate type
25 s_global_curr_code VARCHAR2(10);
26 s_global_rate_type VARCHAR2(15);
27 -- secondary global currency
28 s_secondary_curr_code VARCHAR2(10);
29 s_secondary_rate_type VARCHAR2(15);
30
31
32 /**************************************************
33 * Common Procedures
34 *
35 * File scope functions (not in spec)
36 **************************************************/
37
38 -- Global variable setup and verification
39 PROCEDURE global_setup;
40
41 -- Global wrapup procedure
42 PROCEDURE global_wrapup (p_retcode IN NUMBER,
43 p_exp_rows_collected IN NUMBER);
44
45 -- Print out error message in a consistent manner
46 FUNCTION err_mesg (p_mesg IN VARCHAR2,
47 p_proc_name IN VARCHAR2,
48 p_stmt_id IN NUMBER)
49 RETURN VARCHAR2;
50
51 -- Print stage done message
52 PROCEDURE print_stage_done_mesg (p_proc_name IN VARCHAR2,
53 p_stmt_id IN NUMBER);
54
55 -- Clean up the inventory expiration status related tables
56 PROCEDURE clear_inv_exp_tables;
57
58 -- Extract the expired inventory value from OLTP and insert into fact.
59 PROCEDURE extract_expired_inventory (p_run_date IN DATE,
60 p_rows_collected OUT NOCOPY NUMBER);
61
62 -- Check for missing currency conversion rates
63 PROCEDURE check_missing_rates (p_run_date IN DATE);
64
65 /**************************************************
66 * Common Procedures Definitions
67 **************************************************/
68
69 /* global_setup
70
71 Performs global setup of file scope variables and does any checking
72 needed for global DBI setups.
73
74 Clean up tables if needed.
75
76 Parameters: None
77
78 History:
79 Date Author Action
80 07/07/05 Dinkar Gupta Defined function.
81
82 */
83 PROCEDURE global_setup
84 IS
85 -- {
86 l_proc_name CONSTANT VARCHAR2 (40) := 'global_setup';
87 l_stmt_id NUMBER;
88
89 l_list DBMS_SQL.VARCHAR2_TABLE;
90
91 l_setup_good BOOLEAN;
92
93 -- }
94 BEGIN
95 -- {
96 -- Initialization block
97 l_stmt_id := 0;
98 s_opi_schema := NULL;
99 s_status := NULL;
100 s_industry := NULL;
101 s_user_id := NULL;
102 s_login_id := NULL;
103 s_program_id := NULL;
104 s_program_login_id := NULL;
105 s_program_application_id := NULL;
106 s_request_id := NULL;
107 s_global_curr_code := NULL;
108 s_global_rate_type := NULL;
109 s_secondary_curr_code := NULL;
110 s_secondary_rate_type := NULL;
111
112 -- Common setup API
113 l_stmt_id := 10;
114 IF BIS_COLLECTION_UTILITIES.setup (p_object_name => C_PKG_NAME) = FALSE
115 THEN
116 -- {
117 RAISE BIS_COMMON_API_FAILED;
118 -- }
119 END IF;
120
121 l_stmt_id := 20;
122 -- Obtain the OPI schema name to allow truncation of various tables
123 -- get session parameters
124 IF (NOT (fnd_installation.get_app_info(
125 application_short_name => 'OPI',
126 status => s_status,
127 industry => s_industry,
128 oracle_schema => s_opi_schema))) THEN
129 -- {
130 RAISE SCHEMA_INFO_NOT_FOUND;
131 -- }
132 END IF;
133
134 l_stmt_id := 30;
135 -- Package level variables for the user logged in
136 s_user_id := nvl(fnd_global.user_id, -1);
137 s_login_id := nvl(fnd_global.login_id, -1);
138 s_program_id := nvl (fnd_global.conc_program_id, -1);
139 s_program_login_id := nvl (fnd_global.conc_login_id, -1);
140 s_program_application_id := nvl (fnd_global.prog_appl_id, -1);
141 s_request_id := nvl (fnd_global.conc_request_id, -1);
142
143 l_stmt_id := 40;
144 -- check for the DBI global currency setup
145 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
146 l_list(2) := 'BIS_GLOBAL_START_DATE';
147 l_setup_good := BIS_COMMON_PARAMETERS.check_global_parameters (
148 p_parameter_list => l_list);
149
150 l_stmt_id := 50;
151 -- Ensure that the global currency code has been correctly set up
152 IF (NOT (l_setup_good)) THEN
153 -- {
154 RAISE PRIMARY_CURR_SETUP_BAD;
155 -- }
156 END IF;
157
158 l_stmt_id := 60;
159 -- Get the DBI global currency code
160 s_global_curr_code := BIS_COMMON_PARAMETERS.get_currency_code;
161
162 l_stmt_id := 70;
163 IF (s_global_curr_code IS NULL) THEN
164 -- {
165 RAISE NO_GLOBAL_CURR_CODE;
166 -- }
167 END IF;
168
169 l_stmt_id := 80;
170 -- Get the DBI Global rate type
171 s_global_rate_type := BIS_COMMON_PARAMETERS.get_rate_type;
172
173 l_stmt_id := 90;
174 IF (s_global_rate_type IS NULL) THEN
175 -- {
176 RAISE NO_GLOBAL_RATE_TYPE;
177 -- }
178 END IF;
179
180 l_stmt_id := 100;
181 -- Get the DBI secondary currency code
182 s_secondary_curr_code := BIS_COMMON_PARAMETERS.get_secondary_currency_code;
183
184 l_stmt_id := 110;
185 -- Get the DBI Global rate type
186 s_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
187
188 l_stmt_id := 100;
189 IF ( (s_secondary_curr_code IS NULL AND
190 s_secondary_rate_type IS NOT NULL)
191 OR (s_secondary_curr_code IS NOT NULL AND
192 s_secondary_rate_type IS NULL) ) THEN
193 -- {
194 RAISE SEC_CURR_SETUP_INVALID;
195 -- }
196 END IF;
197
198 l_stmt_id := 110;
199 -- clean the inventory setup tables
200 clear_inv_exp_tables;
201
202 return;
203 -- }
204 EXCEPTION
205 -- {
206 WHEN SCHEMA_INFO_NOT_FOUND THEN
207 -- {
208 BIS_COLLECTION_UTILITIES.PUT_LINE (
209 p_text => err_mesg (p_mesg => SCHEMA_INFO_NOT_FOUND_MESG,
210 p_proc_name => l_proc_name, p_stmt_id => l_stmt_id));
211 RAISE GLOBAL_SETUP_FAILED;
212 -- }
213
214 WHEN BIS_COMMON_API_FAILED THEN
215 -- {
216 BIS_COLLECTION_UTILITIES.PUT_LINE (
217 p_text => err_mesg (p_mesg => BIS_COMMON_API_FAILED_MESG,
218 p_proc_name => l_proc_name, p_stmt_id => l_stmt_id));
219 RAISE GLOBAL_SETUP_FAILED;
220 -- }
221
222 WHEN EXP_TABLE_CLEANUP_FAILED THEN
223 -- {
224 BIS_COLLECTION_UTILITIES.PUT_LINE (
225 p_text => err_mesg (p_mesg => EXP_TABLE_CLEANUP_FAILED_MESG,
226 p_proc_name => l_proc_name, p_stmt_id => l_stmt_id));
227 RAISE GLOBAL_SETUP_FAILED;
228 -- }
229
230 WHEN PRIMARY_CURR_SETUP_BAD THEN
231 -- {
232 BIS_COLLECTION_UTILITIES.PUT_LINE (
233 p_text => err_mesg (p_mesg => PRIMARY_CURR_SETUP_BAD_MESG,
234 p_proc_name => l_proc_name, p_stmt_id => l_stmt_id));
235 RAISE GLOBAL_SETUP_FAILED;
236 -- }
237
238 WHEN NO_GLOBAL_CURR_CODE THEN
239 -- {
240 BIS_COLLECTION_UTILITIES.PUT_LINE (
241 p_text => err_mesg (p_mesg => NO_GLOBAL_CURR_CODE_MESG,
242 p_proc_name => l_proc_name, p_stmt_id => l_stmt_id));
243 RAISE GLOBAL_SETUP_FAILED;
244 -- }
245
246 WHEN NO_GLOBAL_RATE_TYPE THEN
247 -- {
248 BIS_COLLECTION_UTILITIES.PUT_LINE (
249 p_text => err_mesg (p_mesg => NO_GLOBAL_RATE_TYPE_MESG,
250 p_proc_name => l_proc_name,p_stmt_id => l_stmt_id));
251 RAISE GLOBAL_SETUP_FAILED;
252 -- }
253
254 WHEN SEC_CURR_SETUP_INVALID THEN
255 -- {
256 BIS_COLLECTION_UTILITIES.PUT_LINE (
257 p_text => err_mesg (p_mesg => SEC_CURR_SETUP_INVALID_MESG,
258 p_proc_name => l_proc_name,p_stmt_id => l_stmt_id));
259 RAISE GLOBAL_SETUP_FAILED;
260 -- }
261
262 WHEN OTHERS THEN
263 -- {
264 BIS_COLLECTION_UTILITIES.PUT_LINE (
265 p_text => err_mesg (p_mesg => SQLERRM, p_proc_name => l_proc_name,
266 p_stmt_id => l_stmt_id));
267 RAISE GLOBAL_SETUP_FAILED;
268 -- }
269 -- }
270 END global_setup;
271
272
273 /* global_wrapup
274
275 Performs global wrapup.
276
277 If program is successful or ending with a warning, then report success
278 to the BIS logging module.
279
280 Clean up tables if program is erroring out.
281
282 Parameters:
283 1. p_retcode - Current return code of the program.
284 -1 = error
285 1 = warning
286 0 = success
287 2. p_exp_rows_collected - Number of collected rows to report.
288
289 History:
290 Date Author Action
291 07/07/05 Dinkar Gupta Defined function.
292
293 */
294 PROCEDURE global_wrapup (p_retcode IN NUMBER,
295 p_exp_rows_collected IN NUMBER)
296 IS
297 -- {
298 l_proc_name CONSTANT VARCHAR2 (40) := 'global_wrapup';
299 l_stmt_id NUMBER;
300 -- }
301 BEGIN
302 -- {
303 -- Initialization block
304 l_stmt_id := 0;
305
306 l_stmt_id := 10;
307 IF (p_retcode = C_ERROR) THEN
308 -- {
309 l_stmt_id := 20;
310 -- clear up all tables
311 clear_inv_exp_tables;
312
313 l_stmt_id := 30;
314 -- Program has failed. Call the BIS wrapup API appropriately.
315 BIS_COLLECTION_UTILITIES.wrapup (
316 p_status => FALSE,
317 p_count => p_exp_rows_collected,
318 p_message => C_CURR_INV_EXP_LOAD_ERROR_MESG);
319 -- }
320 ELSIF (p_retcode = C_WARNING OR p_retcode = C_SUCCESS) THEN
321 -- {
322 l_stmt_id := 40;
323 -- from a BIS log perspective, program has been successful
324 BIS_COLLECTION_UTILITIES.wrapup (TRUE, p_exp_rows_collected,
325 C_SUCCESS_MESG);
326 -- }
327 END IF;
328
329 return;
330
331 -- }
332 EXCEPTION
333 -- {
334
335 WHEN EXP_TABLE_CLEANUP_FAILED THEN
336 -- {
337 BIS_COLLECTION_UTILITIES.PUT_LINE (
338 p_text => err_mesg (p_mesg => EXP_TABLE_CLEANUP_FAILED_MESG,
339 p_proc_name => l_proc_name, p_stmt_id => l_stmt_id));
340 RAISE GLOBAL_WRAPUP_FAILED;
341 -- }
342
343 WHEN OTHERS THEN
344 -- {
345 BIS_COLLECTION_UTILITIES.PUT_LINE (
346 p_text => err_mesg (p_mesg => SQLERRM, p_proc_name => l_proc_name,
347 p_stmt_id => l_stmt_id));
348 RAISE GLOBAL_WRAPUP_FAILED;
349 -- }
350 -- }
351 END global_wrapup;
352
353
354 /* clear_inv_exp_tables
355
356 Clean up inventory expiration status tables.
357
358 Parameters: None
359
360 History:
361 Date Author Action
362 07/07/05 Dinkar Gupta Defined function.
363
364 */
365 PROCEDURE clear_inv_exp_tables
366 IS
367 -- {
368 l_proc_name CONSTANT VARCHAR2 (40) := 'clear_inv_exp_tables';
369 l_stmt_id NUMBER;
370 -- }
371 BEGIN
372 -- {
373 -- Initialization block
374 l_stmt_id := 0;
375
376 l_stmt_id := 10;
377 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
378 'OPI_DBI_CURR_INV_EXP_F');
379
380 return;
381
382 -- }
383 EXCEPTION
384 -- {
385
386 WHEN OTHERS THEN
387 -- {
388 BIS_COLLECTION_UTILITIES.PUT_LINE (
389 p_text => err_mesg (p_mesg => SQLERRM, p_proc_name =>l_proc_name,
390 p_stmt_id =>l_stmt_id));
391 RAISE EXP_TABLE_CLEANUP_FAILED;
392 -- }
393 -- }
394 END clear_inv_exp_tables;
395
396
397
398 /* err_mesg
399
400 Return a C_ERRBUF_SIZE character long, properly formatted error
401 message with the package name, procedure name and message.
402
403 Parameters:
404 p_mesg - Actual message to be printed
405 p_proc_name - name of procedure that should be printed in the message
406 (optional)
407 p_stmt_id - step in procedure at which error occurred
408 (optional)
409
410 History:
411 Date Author Action
412 07/07/04 Dinkar Gupta Defined function.
413 */
414
415 FUNCTION err_mesg (p_mesg IN VARCHAR2,
416 p_proc_name IN VARCHAR2,
417 p_stmt_id IN NUMBER)
418 RETURN VARCHAR2
419 IS
420 -- {
421 l_proc_name CONSTANT VARCHAR2 (40) := 'err_mesg';
422 l_stmt_id NUMBER;
423
424 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
425 -- as the size of the declaration. I have to put 300 here.
426 l_formatted_message VARCHAR2 (300);
427 -- }
428 BEGIN
429 -- {
430 -- initialization block
431 l_stmt_id := 0;
432
433 -- initialization block
434 l_formatted_message := NULL;
435
436 l_stmt_id := 10;
437 l_formatted_message := substr ((C_PKG_NAME || '.' || p_proc_name || ' #' ||
438 to_char (p_stmt_id) || ': ' || p_mesg),
439 1, C_ERRBUF_SIZE);
440
441 commit;
442
443 return l_formatted_message;
444 -- }
445 EXCEPTION
446 -- {
447 WHEN OTHERS THEN
448 -- {
449 -- the exception happened in the exception reporting function !!
450 -- return with ERROR.
451 l_formatted_message := substr ((C_PKG_NAME || '.' || l_proc_name ||
452 ' #' ||
453 to_char (l_stmt_id) || ': ' ||
454 SQLERRM),
455 1, C_ERRBUF_SIZE);
456
457 l_formatted_message := 'Error in error reporting.';
458 return l_formatted_message;
459 -- }
460 -- }
461 END err_mesg;
462
463 /* print_stage_done_mesg
464
465 Print a message of 'Done' for whatever procedure/statement called.
466
467 Parameters:
468 p_proc_name - name of procedure that should be printed in the message
469 p_stmt_id - step in procedure at which error occurred
470
471 History:
472 Date Author Action
473 07/07/04 Dinkar Gupta Defined function.
474 */
475
476 PROCEDURE print_stage_done_mesg (p_proc_name IN VARCHAR2,
477 p_stmt_id IN NUMBER)
478 IS
479 -- {
480 l_proc_name CONSTANT VARCHAR2 (40) := 'print_stage_done_mesg';
481 l_stmt_id NUMBER;
482
483 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
484 -- as the size of the declaration. I have to put 300 here.
485 l_formatted_message VARCHAR2 (300);
486 -- }
487 BEGIN
488 -- {
489 -- initialization block
490 l_stmt_id := 0;
491
492 -- initialization block
493 l_formatted_message := NULL;
494
495 l_stmt_id := 10;
496 l_formatted_message := substr ((C_PKG_NAME || '.' || p_proc_name || ' #' ||
497 to_char (p_stmt_id) || ': ' || 'Done.'),
498 1, C_ERRBUF_SIZE);
499
500 BIS_COLLECTION_UTILITIES.PUT_LINE (l_formatted_message);
501
502 return;
503 -- }
504 EXCEPTION
505 -- {
506 WHEN OTHERS THEN
507 -- {
508 -- the exception happened in the print function
509 BIS_COLLECTION_UTILITIES.PUT_LINE (
510 p_text => err_mesg (p_mesg => SQLERRM, p_proc_name => l_proc_name,
511 p_stmt_id => l_stmt_id));
512
513 RAISE; -- on to wrapper
514 -- }
515 -- }
516 END print_stage_done_mesg;
517
518 /* extract_expired_inventory
519
520 Extract the onhand and expired lot controlled inventory quantity/value
521 as of the run time from the inventory current snapshot tables,
522 MTL_ONHAND_QUANTITIES and MTL_LOT_NUMBERS. All inventory that is not
523 lot controlled will be ignored in this extraction.
524
525 Inventory value is computed using the current item costs stored in the
526 system.
527
528 Store the inventory value in the functional currency, DBI primary
529 global currency and DBI secondary global currency (if set up).
530
531 Aggregate the information for high level report queries, by rolling
532 up along item and inventory category.
533
534 Query details below.
535
536 DO NOT COMMIT DATA HERE!!
537
538 Parameters:
539 1. p_run_date - Date on which the program was run. Trunc'ed already.
540
541
542 */
543 PROCEDURE extract_expired_inventory (p_run_date IN DATE,
544 p_rows_collected OUT NOCOPY NUMBER)
545 IS
546 -- {
547
548 l_proc_name CONSTANT VARCHAR2 (40) := 'check_missing_rates';
549 l_stmt_id NUMBER;
550
551 -- }
552 BEGIN
553 -- {
554
555 -- initialization block
556 l_stmt_id := 0;
557 p_rows_collected := 0; -- nothing collected yet.
558
559 l_stmt_id := 10;
560 -- Extract data. Query logic is as follows:
561 --
562 -- Extract the onhand and expired lot controlled inventory quantity and
563 -- value as of the run time from the inventory current snapshot tables,
564 -- MTL_ONHAND_QUANTITIES and MTL_LOT_NUMBERS. All inventory that is not
565 -- lot controlled will be ignored in this extraction. Since the MTL
566 -- tables store quantity in the item's primary uom, there is no need
567 -- for unit of measure conversions.
568 --
569 -- Expiration of lots is determined as follows:
570 -- 1. Lot expiration date = NULL
571 -- ==> Lot has not expired
572 -- 2. Lot expiration date >= p_run_date
573 -- ==> Lot has not expired yet
574 -- 3. Lot expiration date < p_run_date
575 -- ==> Lot has expired
576 --
577 -- Inventory value is computed using the current item costs stored in the
578 -- system. The cost type is determined from the primary_cost_method
579 -- (1 = standard, else layered) from MTL_PARAMETERS.
580 --
581 -- For standard costing organizations, the current item cost can
582 -- be obtained from the CST_ITEM_COSTS table. Since there are multiple
583 -- cost_type_id's in the CIC table, we care only about frozen cost
584 -- (cost_type_id = 1). Also, CIC may not have a frozen (cost_type_id = 2)
585 -- record for a layered costing org item, but may have other
586 -- cost_type_id records. Hence the need to do an outer join on the
587 -- cost_type_id(+) = 1 filter condition instead of using just the
588 -- nvl (cost_type_id, 1) = 1 condition.
589 --
590 -- For layered costing organization, the item cost
591 -- must be obtained from CST_QUANTITY_LAYERS at the cost group level.
592 --
593 --
594 -- Due to presence of expense items and the fact that CQL only has
595 -- records for items in layered costing organizations, the joins to
596 -- both costing tables will be outer joins. Join to MTL_SYSTEM_ITEMS_B
597 -- to check whether an item is an expense item or not.
598 --
599 -- Similarly items in expense subinventories will be assigned 0 value.
600 --
601 -- Store the inventory value in the functional currency, DBI primary
602 -- global currency and DBI secondary global currency (if set up). Use
603 -- the standard fii_currency.get_global_rate_primary and
604 -- fii_currency.get_global_rate_secondary to get conversion rates.
605 --
606 -- Rates are only needed for all distinct organizations with defined lots
607 -- as of the day on which the program is being run.
608 -- The FII conversion rate APIs behave as follows:
609 -- 1 for currency code of 'USD' which is the global currency
610 -- ## - conversion factor if conversion rate is defined.
611 -- -1 for dates for which there is no currency conversion rate
612 -- -2 for unrecognized currency conversion rates
613 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
614 -- transaction_date is prior to 01-JAN-1999 (when the EUR
615 -- officially went into circulation).
616 -- (This last case will not happen here since this program always runs
617 -- as of sysdate)
618 --
619 -- Additionally, the secondary conversion rate API returns NULL if
620 -- the DBI secondary global currency has not been set up. Since the
621 -- number of distinct organizations will be small and there is no
622 -- separate conversion rates staging table, the primary and
623 -- secondary currency rate APIs will be called for all orgs, even if
624 -- the primary and secondary currencies are the same.
625 --
626 -- Aggregate the information for high level report queries, by rolling
627 -- up along item and inventory category. Store the item_org_id from
628 -- the item dimension table, ENI_OLTP_ITEM_STAR for report queries.
629 -- As a result, the highest rollup is at the organization level.
630 --
631 -- Maintain the functional currency code and conversion rates
632 -- corresponding to organizations at all records. That way, missing
633 -- rates can be detected very easily by querying the rolled up org
634 -- level records.
635
636 -- For process organizations, we use the get_opm_item_cost API to get
637 -- the cost as on the run date. We collect cost for the lot-controlled items
638 -- in MTL_ONHAND_QUANTITIES that belong to process organizations.These orgs
639 -- are determined from the process_enabled_flag = 'Y' from MTL_PARAMETERS.
640 --
641 INSERT /*+ append parallel (opi_dbi_curr_inv_exp_f) */
642 INTO opi_dbi_curr_inv_exp_f (
643 organization_id,
644 inventory_item_id,
645 item_org_id,
646 uom_code,
647 inv_category_id,
648 func_currency_code,
649 aggregation_level_flag,
650 onhand_qty,
651 expired_qty,
652 onhand_val_b,
653 onhand_val_g,
654 onhand_val_sg,
655 expired_val_b,
656 expired_val_g,
657 expired_val_sg,
658 conversion_rate,
659 sec_conversion_rate,
660 creation_date,
661 last_update_date,
662 created_by,
663 last_updated_by,
664 last_update_login,
665 program_id,
666 program_login_id,
667 program_application_id,
668 request_id
669 )
670 SELECT /*+ parallel (exp_inv) parallel (conv_rates) */
671 exp_inv.organization_id,
672 exp_inv.inventory_item_id,
673 exp_inv.item_org_id,
674 exp_inv.primary_uom_code,
675 exp_inv.inv_category_id,
676 conv_rates.func_currency_code,
677 grouping_id (exp_inv.inv_category_id,
678 exp_inv.item_org_id)
679 aggregation_level_flag,
680 sum (exp_inv.onhand_qty) onhand_qty,
681 sum (exp_inv.expired_qty) expired_qty,
682 sum (exp_inv.onhand_val_b) onhand_val_b,
683 sum (exp_inv.onhand_val_b * conv_rates.conversion_rate)
684 onhand_val_g,
685 sum (exp_inv.onhand_val_b * conv_rates.sec_conversion_rate)
686 onhand_val_sg,
687 sum (exp_inv.expired_val_b) expired_val_b,
688 sum (exp_inv.expired_val_b * conv_rates.conversion_rate)
689 expired_val_g,
690 sum (exp_inv.expired_val_b * conv_rates.sec_conversion_rate)
691 expired_val_sg,
692 conv_rates.conversion_rate,
693 conv_rates.sec_conversion_rate,
694 sysdate,
695 sysdate,
696 s_user_id,
697 s_user_id,
698 s_login_id,
699 s_program_id,
700 s_program_login_id,
701 s_program_application_id,
702 s_request_id
703 FROM
704 (
705 SELECT /*+ parallel (exp_qty) parallel (cic) parallel (cql)
706 parallel (items) parallel (msi) parallel (mp)
707 parallel (subs) */
708 exp_qty.organization_id,
709 exp_qty.inventory_item_id,
710 items.id item_org_id,
711 items.primary_uom_code,
712 nvl (items.inv_category_id, -1) inv_category_id,
713 sum (exp_qty.onhand_qty) onhand_qty,
714 sum (exp_qty.expired_qty) expired_qty,
715 sum (decode (subs.asset_inventory,
716 C_EXPENSE_SUBINVENTORY, 0,
717 (decode (msi.inventory_asset_flag,
718 C_EXPENSE_ITEM_FLAG, 0,
719 exp_qty.onhand_qty *
720 decode(mp.process_enabled_flag,'Y',
721 OPI_DBI_INV_VALUE_INIT_PKG.GET_OPM_ITEM_COST(exp_qty.organization_id,
722 exp_qty.inventory_item_id,
723 p_run_date),
724 decode (mp.primary_cost_method,
725 C_STANDARD_COSTING_ORG,
726 cic.item_cost,
727 cql.item_cost))))))
728 onhand_val_b,
729 sum (decode (subs.asset_inventory,
730 C_EXPENSE_SUBINVENTORY, 0,
731 (decode (msi.inventory_asset_flag,
732 C_EXPENSE_ITEM_FLAG, 0,
733 exp_qty.expired_qty *
734 decode(mp.process_enabled_flag,'Y',
735 OPI_DBI_INV_VALUE_INIT_PKG.GET_OPM_ITEM_COST(exp_qty.organization_id,
736 exp_qty.inventory_item_id,
737 p_run_date),
738 decode (mp.primary_cost_method,
739 C_STANDARD_COSTING_ORG,
740 cic.item_cost,
741 cql.item_cost))))))
742 expired_val_b
743 FROM
744 (
745 SELECT /*+ parallel (moq) parallel (mln)
746 use_hash (moq, mln) */
747 moq.organization_id,
748 moq.inventory_item_id,
749 moq.cost_group_id,
750 moq.subinventory_code,
751 sum (moq.transaction_quantity) onhand_qty,
752 sum (CASE WHEN mln.expiration_date < p_run_date THEN
753 moq.transaction_quantity
754 ELSE
755 0
756 END)
757 expired_qty
758 FROM mtl_onhand_quantities moq,
759 mtl_lot_numbers mln
760 WHERE moq.inventory_item_id = mln.inventory_item_id
761 AND moq.organization_id = mln.organization_id
762 AND moq.lot_number = mln.lot_number
763 GROUP BY
764 moq.organization_id,
765 moq.inventory_item_id,
766 moq.cost_group_id,
767 moq.subinventory_code
768 ) exp_qty,
769 mtl_system_items_b msi,
770 eni_oltp_item_star items,
771 mtl_parameters mp,
772 cst_item_costs cic,
773 cst_quantity_layers cql,
774 mtl_secondary_inventories subs
775 WHERE exp_qty.inventory_item_id = msi.inventory_item_id
776 AND exp_qty.organization_id = msi.organization_id
777 AND exp_qty.inventory_item_id = items.inventory_item_id
778 AND exp_qty.organization_id = items.organization_id
779 AND exp_qty.organization_id = mp.organization_id
780 AND exp_qty.inventory_item_id = cic.inventory_item_id (+)
781 AND exp_qty.organization_id = cic.organization_id (+)
782 AND 1 = cic.cost_type_id (+)
783 AND exp_qty.inventory_item_id = cql.inventory_item_id (+)
784 AND exp_qty.organization_id = cql.organization_id (+)
785 AND exp_qty.cost_group_id = cql.cost_group_id (+)
786 AND exp_qty.organization_id = subs.organization_id
787 AND exp_qty.subinventory_code = subs.secondary_inventory_name
788 GROUP BY
789 exp_qty.organization_id,
790 exp_qty.inventory_item_id,
791 items.id,
792 items.primary_uom_code,
793 nvl (items.inv_category_id, -1)
794 ) exp_inv,
795 (
796 SELECT /*+ parallel (to_conv) parallel (curr_codes) */
797 to_conv.organization_id,
798 curr_codes.currency_code func_currency_code,
799 p_run_date run_date,
800 decode (curr_codes.currency_code,
801 s_global_curr_code, 1,
802 fii_currency.get_global_rate_primary (
803 curr_codes.currency_code,
804 p_run_date) )
805 conversion_rate,
806 decode (s_secondary_curr_code,
807 NULL, NULL,
808 curr_codes.currency_code, 1,
809 fii_currency.get_global_rate_secondary (
810 curr_codes.currency_code,
811 p_run_date))
812 sec_conversion_rate
813 FROM
814 (SELECT /*+ parallel (mtl_lot_numbers) */
815 DISTINCT organization_id
816 FROM mtl_lot_numbers) to_conv,
817 (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
818 parallel (hoi) parallel (gsob)*/
819 DISTINCT hoi.organization_id, gsob.currency_code
820 FROM hr_organization_information hoi,
821 gl_sets_of_books gsob
822 WHERE hoi.org_information_context = 'Accounting Information'
823 AND hoi.org_information1 = to_char(gsob.set_of_books_id))
824 curr_codes
825 WHERE curr_codes.organization_id = to_conv.organization_id
826 ) conv_rates
827 WHERE conv_rates.organization_id = exp_inv.organization_id
828 GROUP BY
829 exp_inv.organization_id,
830 conv_rates.func_currency_code,
831 conv_rates.conversion_rate,
832 conv_rates.sec_conversion_rate,
833 ROLLUP (
834 exp_inv.inv_category_id,
835 (exp_inv.item_org_id,
836 exp_inv.inventory_item_id,
837 exp_inv.primary_uom_code)
838 );
839
840 l_stmt_id := 20;
841 -- Count the number of rows collected
842 p_rows_collected := SQL%ROWCOUNT;
843
844 return;
845
846 -- }
847 EXCEPTION
848 -- {
849
850 WHEN OTHERS THEN
851 -- {
852 BIS_COLLECTION_UTILITIES.PUT_LINE (
853 p_text => err_mesg (p_mesg => SQLERRM, p_proc_name =>l_proc_name,
854 p_stmt_id => l_stmt_id));
855
856 RAISE EXP_INV_EXTRACT_ERROR;
857 -- }
858 -- }
859 END extract_expired_inventory;
860
861
862 /* check_missing_rates
863
864 If any primary conversion rates are missing, throw an exception.
865 If any secondary currency rates are missing (after the secondary
866 currency has been set up) throw an exception.
867
868 Data is checked directly in the fact in the org level records since
869 currency conversion rate errors can be detected at that level.
870
871 Parameters:
872 1. p_run_date - Date on which the program was run.
873
874 History:
875 Date Author Action
876 07/07/05 Dinkar Gupta Defined procedure.
877 */
878
879 PROCEDURE check_missing_rates (p_run_date IN DATE)
880 IS
881 -- {
882 l_proc_name CONSTANT VARCHAR2 (40) := 'check_missing_rates';
883 l_stmt_id NUMBER;
884
885 -- Cursor to see if any rates are missing. See below for details
886 -- about the +999 for sec_conversion_rates.
887 CURSOR invalid_rates_exist_csr IS
888 -- {
889 SELECT 1
890 FROM opi_dbi_curr_inv_exp_f
891 WHERE ( nvl (conversion_rate, -999) < 0
892 OR nvl (sec_conversion_rate, 999) < 0)
893 AND rownum < 2;
894 -- }
895
896 invalid_rates_exist_rec invalid_rates_exist_csr%ROWTYPE;
897
898 -- Set up a cursor to get all the invalid rates.
899 -- By the logic of the fii_currency.get_global_rate_primary
900 -- and fii_currency.get_global_rate_secondary APIs, the returned value
901 -- is -ve if no rate exists:
902 -- -1 for dates with no rate.
903 -- -2 for unrecognized conversion rates.
904 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
905 -- transaction_date is prior to 01-JAN-1999 (when the EUR
906 -- officially went into circulation).
907 -- (This will not happen in our case since this is a complete
908 -- refresh program, written well after 1999).
909 --
910 -- However, with the secondary currency, the null rate means it
911 -- has not been setup and should therefore not be reported as an
912 -- error.
913 --
914 -- Since this is a full refresh program with a nested fact, we report
915 -- missing rates of the org level records (aggregation_level_flag = 3)
916 -- since currency codes are defined at that level.
917 --
918 -- Note: If some orgs never had a functional currency code defined,
919 -- they will show up as errors here.
920 CURSOR invalid_rates_csr (p_pri_sec_curr_same NUMBER) IS
921 -- {
922 SELECT /*+ parallel (compare) */
923 DISTINCT
924 report_order,
925 curr_code,
926 rate_type,
927 p_run_date run_date,
928 func_currency_code
929 FROM (
930 SELECT /*+ parallel (conv) */
931 s_global_curr_code curr_code,
932 s_global_rate_type rate_type,
933 1 report_order, -- ordering global currency first
934 conv.func_currency_code
935 FROM opi_dbi_curr_inv_exp_f conv
936 WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
937 AND conv.aggregation_level_flag = 3 -- org level records
938 UNION ALL
939 SELECT /*+ parallel (conv) */
940 s_secondary_curr_code curr_code,
941 s_secondary_rate_type rate_type,
942 2 report_order, --ordering secondary currency next
943 conv.func_currency_code
944 FROM opi_dbi_curr_inv_exp_f conv
945 WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
946 AND conv.aggregation_level_flag = 3 -- org level records
947 -- check here if primary not same as secondary
948 AND p_pri_sec_curr_same = 0
949 ) compare
950 ORDER BY
951 report_order ASC,
952 func_currency_code;
953 -- }
954
955 -- Flag to ensure all rates have been found.
956 l_all_rates_found BOOLEAN;
957
958 -- Boolean to check if the primary and secondary currencies are the
959 -- same
960 l_pri_sec_curr_same NUMBER;
961 -- }
962 BEGIN
963 -- {
964 -- initialization block
965 l_stmt_id := 0;
966 l_all_rates_found := true;
967 l_pri_sec_curr_same := 0;
968
969 l_stmt_id := 10;
970 -- check if the primary and secondary currencies and rate types are
971 -- identical.
972 IF (s_global_curr_code = nvl (s_secondary_curr_code, '---') AND
973 s_global_rate_type = nvl (s_secondary_rate_type, '---') ) THEN
974 -- {
975 l_pri_sec_curr_same := 1;
976 -- }
977 END IF;
978
979
980 l_stmt_id := 20;
981 -- Check that all rates have been found and are non-negative.
982 -- If there is a problem, notify user.
983 OPEN invalid_rates_exist_csr;
984 FETCH invalid_rates_exist_csr INTO invalid_rates_exist_rec;
985 IF (invalid_rates_exist_csr%FOUND) THEN
986 -- {
987 l_stmt_id := 30;
988 -- print the header out
989 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
990
991 l_stmt_id := 40;
992 -- all rates not found
993 l_all_rates_found := false;
994
995 l_stmt_id := 50;
996 FOR invalid_rate_rec IN invalid_rates_csr (l_pri_sec_curr_same)
997 LOOP
998 -- {
999 l_stmt_id := 60;
1000 BIS_COLLECTION_UTILITIES.writeMissingRate(
1001 p_rate_type => invalid_rate_rec.rate_type,
1002 p_from_currency => invalid_rate_rec.func_currency_code,
1003 p_to_currency => invalid_rate_rec.curr_code,
1004 p_date => invalid_rate_rec.run_date);
1005 -- }
1006 END LOOP;
1007 -- }
1008 END IF;
1009
1010 l_stmt_id := 70;
1011 CLOSE invalid_rates_exist_csr;
1012
1013 -- If all rates not found raise an exception
1014 l_stmt_id := 80;
1015 IF (l_all_rates_found = FALSE) THEN
1016 RAISE MISSING_CONV_RATES;
1017 END IF;
1018
1019 RETURN;
1020
1021 EXCEPTION
1022 -- {
1023
1024 WHEN MISSING_CONV_RATES THEN
1025 -- {
1026 BIS_COLLECTION_UTILITIES.PUT_LINE (
1027 p_text => err_mesg (p_mesg => MISSING_CONV_RATES_MESG,
1028 p_proc_name => l_proc_name, p_stmt_id => l_stmt_id));
1029 RAISE CONV_RATES_ERROR;
1030 -- }
1031
1032 WHEN OTHERS THEN
1033 -- {
1034 BIS_COLLECTION_UTILITIES.PUT_LINE (
1035 p_text => err_mesg (p_mesg => SQLERRM, p_proc_name => l_proc_name,
1036 p_stmt_id => l_stmt_id));
1037
1038 RAISE CONV_RATES_ERROR;
1039 -- }
1040 -- }
1041 END check_missing_rates;
1042
1043
1044 /* ref_curr_inv_exp
1045
1046 Refresh the current inventory expiration fact with the onhand
1047 quantity/value and expired quantity/value of lot controlled inventory
1048 as of the run time of the program.
1049
1050 Data is reported in functional, DBI global and DBI secondary global
1051 currencies. Missing conversion rates cause program to error out. The
1052 fact table is truncated when the program errors out, if possible.
1053
1054 History:
1055 Date Author Action
1056 07/07/05 Dinkar Gupta Wrote Function.
1057
1058 */
1059 PROCEDURE ref_curr_inv_exp (errbuf OUT NOCOPY VARCHAR2,
1060 retcode OUT NOCOPY NUMBER)
1061 IS
1062 -- {
1063 l_proc_name CONSTANT VARCHAR2 (40) := 'ref_curr_inv_exp';
1064 l_stmt_id NUMBER;
1065
1066 l_rows_collected NUMBER;
1067
1068 l_missing_conv_rates BOOLEAN;
1069
1070 l_run_date DATE;
1071 -- }
1072 BEGIN
1073 -- {
1074 -- Initialization block.
1075 l_stmt_id := 0;
1076 l_rows_collected := 0; -- nothing collected yet
1077 retcode := C_SUCCESS; -- by default, success
1078 errbuf := C_SUCCESS_MESG;
1079 print_stage_done_mesg (l_proc_name, l_stmt_id);
1080
1081 l_stmt_id := 10;
1082 -- Call the global setup API
1083 global_setup ();
1084 print_stage_done_mesg (l_proc_name, l_stmt_id);
1085
1086 l_stmt_id := 20;
1087 -- Today's date
1088 l_run_date := trunc (sysdate);
1089 print_stage_done_mesg (l_proc_name, l_stmt_id);
1090
1091 l_stmt_id := 30;
1092 -- Extract all the expired inventory data.
1093 -- Only extract data and insert to fact. Don't commit.
1094 extract_expired_inventory (l_run_date,
1095 l_rows_collected);
1096 print_stage_done_mesg (l_proc_name, l_stmt_id);
1097
1098 l_stmt_id := 40;
1099 -- Commit all data centrally in main procedure.
1100 commit;
1101 print_stage_done_mesg (l_proc_name, l_stmt_id);
1102
1103 l_stmt_id := 50;
1104 -- Check for missing conversion rates.
1105 -- Throws CONV_RATES_ERROR exception if missing rates are found.
1106 check_missing_rates (l_run_date);
1107 print_stage_done_mesg (l_proc_name, l_stmt_id);
1108
1109 l_stmt_id := 60;
1110 -- Successful completion
1111 retcode := C_SUCCESS;
1112 errbuf := C_SUCCESS_MESG;
1113 print_stage_done_mesg (l_proc_name, l_stmt_id);
1114
1115 l_stmt_id := 70;
1116 -- General wrapup procedure
1117 global_wrapup (retcode, l_rows_collected);
1118 print_stage_done_mesg (l_proc_name, l_stmt_id);
1119
1120 return;
1121
1122 -- }
1123 EXCEPTION
1124 -- {
1125 WHEN GLOBAL_SETUP_FAILED THEN
1126 -- {
1127 rollback;
1128 retcode := C_ERROR;
1129 errbuf := C_CURR_INV_EXP_LOAD_ERROR_MESG;
1130 global_wrapup (retcode, l_rows_collected);
1131
1132 BIS_COLLECTION_UTILITIES.PUT_LINE (
1133 p_text => err_mesg (p_mesg => GLOBAL_SETUP_FAILED_MESG,
1134 p_proc_name => l_proc_name, p_stmt_id => l_stmt_id));
1135 return;
1136 -- }
1137
1138 WHEN EXP_INV_EXTRACT_ERROR THEN
1139 -- {
1140 rollback;
1141 retcode := C_ERROR;
1142 errbuf := C_CURR_INV_EXP_LOAD_ERROR_MESG;
1143 global_wrapup (retcode, l_rows_collected);
1144
1145 BIS_COLLECTION_UTILITIES.PUT_LINE (
1146 p_text => err_mesg (p_mesg => EXP_INV_EXTRACT_ERROR_MESG,
1147 p_proc_name => l_proc_name,p_stmt_id => l_stmt_id));
1148 return;
1149 -- }
1150
1151 WHEN CONV_RATES_ERROR THEN
1152 -- {
1153 rollback;
1154 retcode := C_ERROR;
1155 errbuf := C_CURR_INV_EXP_LOAD_ERROR_MESG;
1156 global_wrapup (retcode, l_rows_collected);
1157
1158 BIS_COLLECTION_UTILITIES.PUT_LINE (
1159 p_text => err_mesg (p_mesg => CONV_RATES_ERROR_MESG,
1160 p_proc_name => l_proc_name,p_stmt_id => l_stmt_id));
1161 return;
1162 -- }
1163
1164 WHEN GLOBAL_WRAPUP_FAILED THEN
1165 -- {
1166 rollback;
1167 retcode := C_ERROR;
1168 errbuf := C_CURR_INV_EXP_LOAD_ERROR_MESG;
1169 -- try calling it again?? This time it'll clear the fact.
1170 global_wrapup (retcode, l_rows_collected);
1171
1172 BIS_COLLECTION_UTILITIES.PUT_LINE (
1173 p_text => err_mesg (p_mesg => GLOBAL_WRAPUP_FAILED_MESG,
1174 p_proc_name => l_proc_name, p_stmt_id => l_stmt_id));
1175 return;
1176 -- }
1177
1178 WHEN OTHERS THEN
1179 -- {
1180 rollback;
1181 retcode := C_ERROR;
1182 errbuf := C_CURR_INV_EXP_LOAD_ERROR_MESG;
1183 global_wrapup (retcode, l_rows_collected);
1184
1185 BIS_COLLECTION_UTILITIES.PUT_LINE (
1186 p_text => err_mesg (p_mesg => SQLERRM,p_proc_name => l_proc_name,
1187 p_stmt_id => l_stmt_id));
1188
1189 return;
1190 --}
1191 -- }
1192 END ref_curr_inv_exp;
1193
1194
1195
1196 END OPI_DBI_CURR_INV_EXP_PKG;