1 PACKAGE BODY opi_dbi_inv_cca_pkg AS
2 /*$Header: OPIDEICCAB.pls 120.9 2006/03/28 21:33:43 visgupta 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 -- DBI Global start date
17 s_global_start_date DATE;
18 s_r12_migration_date DATE;
19
20 -- Conversion rate related variables: global currency code and rate type
21 s_global_curr_code VARCHAR2(10);
22 s_global_rate_type VARCHAR2(15);
23
24 -- secondary global currency
25 s_secondary_curr_code VARCHAR2(10);
26 s_secondary_rate_type VARCHAR2(15);
27
28 -- Package level variables for the user logged in
29 s_user_id NUMBER;
30 s_login_id NUMBER;
31 s_program_id NUMBER;
32 s_program_login_id NUMBER;
33 s_program_application_id NUMBER;
34 s_request_id NUMBER;
35
36 /**************************************************
37 * Common Procedures (to initial and incremental load)
38 *
39 * File scope functions (not in spec)
40 **************************************************/
41
42 -- Check for primary currency code and global start date
43 FUNCTION check_global_setup
44 RETURN BOOLEAN;
45
46 -- Print out error message in a consistent manner
47 FUNCTION err_mesg (p_mesg IN VARCHAR2,
48 p_proc_name IN VARCHAR2,
49 p_stmt_id IN NUMBER)
50 RETURN VARCHAR2;
51
52 /**************************************************
53 * Initial Load Procedures
54 *
55 * File scope functions (not in spec)
56 **************************************************/
57
58 -- Initialization
59 PROCEDURE cca_initialize_init;
60
61 -- Extract all MMT Cyclecount transactions into a staging table
62 PROCEDURE extract_cca_mmt_init;
63
64 -- Extract Discrete adjustment entry data
65 PROCEDURE extract_discrete_adj_init (p_global_start_date IN DATE);
66
67 -- Extract Process adjustment entry data
68 PROCEDURE extract_process_adj_init (p_global_start_date IN DATE);
69
70 -- Extract the exact match entry data
71 PROCEDURE extract_exact_matches_init;
72
73 -- Compute the conversion rates
74 PROCEDURE compute_cca_conv_rates_init (p_global_curr_code IN VARCHAR2,
75 p_global_rate_type IN VARCHAR2);
76
77 -- Populate the data into the fact table
78 PROCEDURE populate_fact_init;
79
80
81 /**************************************************
82 * Incremental Load Procedures
83 *
84 * File scope functions (not in spec)
85 **************************************************/
86
87 -- Initialization
88 PROCEDURE cca_initialize_incr;
89
90 -- Extract Discrete adjustment entry data
91 PROCEDURE extract_discrete_adj_incr (p_global_start_date IN DATE);
92
93 -- Extract Process adjustment entry data
94 PROCEDURE extract_process_adj_incr (p_global_start_date IN DATE);
95
96 -- Extract the exact match entry data
97 PROCEDURE extract_exact_matches_incr;
98
99 -- Compute the conversion rates
100 PROCEDURE compute_cca_conv_rates_incr (p_global_curr_code IN VARCHAR2,
101 p_global_rate_type IN VARCHAR2);
102
103 -- Populate the data into the fact table
104 PROCEDURE populate_fact_incr;
105
106 /**************************************************
107 * Common Procedures Definitions
108 **************************************************/
109
110 /* check_global_setup
111
112 Checks to see if basic global parameters are set up.
113 Currently these include the:
114 1. Global start date
115 2. Global currency code
116
117 Parameters: None
118
119 History:
120 Date Author Action
121 01/12/04 Dinkar Gupta Defined function.
122 06/07/04 Dinkar Gupta Added initialization of all file scope
123 variables to this function for new
124 GSCC standard that does not like
125 initialization outside BEGIN/END block.
126 */
127 FUNCTION check_global_setup
128 RETURN BOOLEAN
129 IS
130 l_proc_name CONSTANT VARCHAR2 (40) := 'check_global_setup';
131 l_stmt_id NUMBER;
132
133 l_setup_good BOOLEAN;
134
135 l_list dbms_sql.varchar2_table;
136
137 BEGIN
138
139 -- Initialization block
140 l_setup_good := false;
141 l_stmt_id := 0;
142
143 -- Initialize file scope static variables
144 -- Package level variables for session info-
145 -- including schema name for truncating and
146 -- collecting stats
147 s_opi_schema := NULL;
148 s_status := NULL;
149 s_industry := NULL;
150
151 -- DBI Global start date
152 s_global_start_date := NULL;
153
154 -- Conversion rate related variables: global currency code and rate type
155 s_global_curr_code := NULL;
156 s_global_rate_type := NULL;
157 s_secondary_curr_code := NULL;
158 s_secondary_rate_type := NULL;
159
160 -- Package level variables for the user logged in
161 s_user_id := nvl(fnd_global.user_id, -1);
162 s_login_id := nvl(fnd_global.login_id, -1);
163 s_program_id := nvl(fnd_global.conc_program_id, -1);
164 s_program_login_id := nvl(fnd_global.conc_login_id , -1);
165 s_program_application_id := nvl(fnd_global.prog_appl_id , -1);
166 s_request_id := nvl(fnd_global.conc_request_id, -1);
167
168 -- Parameters we want to check for
169 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
170 l_list(2) := 'BIS_GLOBAL_START_DATE';
171
172 l_stmt_id := 10;
173 l_setup_good := bis_common_parameters.check_global_parameters(l_list);
174 return l_setup_good;
175
176 EXCEPTION
177
178 WHEN OTHERS THEN
179 rollback;
180
181 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
182 l_stmt_id));
183
184 l_setup_good := false;
185 return l_setup_good;
186
187
188 END check_global_setup;
189
190
191 /* err_mesg
192 Return a C_ERRBUF_SIZE character long, properly formatted error
193 message with the package name, procedure name and message.
194
195 Parameters:
196 p_mesg - Actual message to be printed
197 p_proc_name - name of procedure that should be printed in the message
198 (optional)
199 p_stmt_id - step in procedure at which error occurred
200 (optional)
201
202 History:
203 Date Author Action
204 01/12/04 Dinkar Gupta Defined function.
205 */
206
207 FUNCTION err_mesg (p_mesg IN VARCHAR2,
208 p_proc_name IN VARCHAR2,
209 p_stmt_id IN NUMBER)
210 RETURN VARCHAR2
211 IS
212
213 l_proc_name CONSTANT VARCHAR2 (60) := 'err_mesg';
214 l_stmt_id NUMBER;
215
216 -- The variable declaration cannot take C_ERRBUF_SIZE (a defined constant)
217 -- as the size of the declaration. I have to put 300 here.
218 l_formatted_message VARCHAR2 (300);
219
220 BEGIN
221
222 -- initialization block
223 l_stmt_id := 0;
224
225 -- initialization block
226 l_formatted_message := NULL;
227
228 l_stmt_id := 10;
229 l_formatted_message := substr ((C_PKG_NAME || '.' || p_proc_name || ' #' ||
230 to_char (p_stmt_id) || ': ' || p_mesg),
231 1, C_ERRBUF_SIZE);
232
233 commit;
234
235 return l_formatted_message;
236
237 EXCEPTION
238
239 WHEN OTHERS THEN
240 -- the exception happened in the exception reporting function !!
241 -- return with ERROR.
242 l_formatted_message := substr ((C_PKG_NAME || '.' || l_proc_name ||
243 ' #' ||
244 to_char (l_stmt_id) || ': ' ||
245 SQLERRM),
246 1, C_ERRBUF_SIZE);
247
248 l_formatted_message := 'Error in error reporting.';
249 return l_formatted_message;
250
251 END err_mesg;
252
253
254 /**************************************************
255 * Initial Load Procedure Definitions
256 **************************************************/
257
258 /* run_initial_load
259
260 Wrapper routine for the initial load of the cycle count accuracy ETL.
261
262 Parameters:
263 retcode - 0 on successful completion, -1 on error and 1 for warning.
264 errbuf - empty on successful completion, message on error or warning
265
266
267 R12 Changes
268 -----------
269 Made changes to calling of procedures and commit.
270 -------------------------------
271
272
273 History:
274 Date Author Action
275 01/12/04 Dinkar Gupta Defined procedure.
276 03/04/04 Dinkar Gupta Added call to OPM package.
277 07/05/05 Vishal Gupta Made Changes for R12.
278
279 */
280
281 PROCEDURE run_initial_load (errbuf OUT NOCOPY VARCHAR2,
282 retcode OUT NOCOPY NUMBER)
283 IS
284
285 l_proc_name CONSTANT VARCHAR2 (40) := 'run_initial_load';
286 l_stmt_id NUMBER;
287
288
289 BEGIN
290
291 -- initialization block
292 l_stmt_id := 0;
293
294 l_stmt_id := 10;
295 -- Check for DBI global parameter setup, initialize file scope variables,
296 -- check for bounds and truncate tables.
297 BIS_COLLECTION_UTILITIES.PUT_LINE ('Initializing cycle count initial load ...');
298 cca_initialize_init ();
299
300 l_stmt_id := 20;
301 -- Set up the bounds for Cycle Count accuracy in the log table.
302 -- Commit will be done
303 BIS_COLLECTION_UTILITIES.PUT_LINE (
304 'Setting up bounds for Process and Discrete ...');
305 OPI_DBI_BOUNDS_PKG.MAINTAIN_OPI_DBI_LOGS ( p_etl_type=> C_ETL_TYPE,
306 p_load_type=> C_LOAD_INIT);
307
308
309 l_stmt_id := 30;
310 -- Make a call to the OPM extraction for PreR12. Do this within
311 -- a begin/end block and throw a custom exception if the
312 -- OPM code ends with an error.
313 -- first check R12 migration date > GSD
314 BIS_COLLECTION_UTILITIES.PUT_LINE ('R12 Migration Date ...' || s_r12_migration_date);
315
316 -- R12 date will not be null. There will be some suitable date returned by
317 -- opi_dbi_rpt_util_pkg.get_inv_convergence_date
318 IF (s_r12_migration_date > s_global_start_date) THEN
319 --{
320 BIS_COLLECTION_UTILITIES.PUT_LINE (
321 'Extracting data for Pre R12 rocess manufacturing organizations ....');
322 BEGIN
323
324 opi_dbi_inv_cca_opm_pkg.run_initial_load_opm (errbuf, retcode);
325 commit; -- commit if successful
326
327
328 EXCEPTION
329
330 WHEN OTHERS THEN
331 RAISE OPM_EXTRACTION_ERROR;
332
333 END;
334 --}
335 ELSE
336 --{
337 BIS_COLLECTION_UTILITIES.PUT_LINE (
338 'Migration date is less than GSD. Skipping PreR12 Process Cycle Count Extraction ....');
339 --}
340 END IF;
341
342
343 l_stmt_id := 40;
344 -- Extract all Cycle count transactions from MMT inot a staging table
345 BIS_COLLECTION_UTILITIES.PUT_LINE (
346 'Extracting cycle count trasnactions into a staging table ...');
347 extract_cca_mmt_init ();
348
349 l_stmt_id := 50;
350 --
351 commit;
352
353 l_stmt_id := 55;
354 -- Call API to load ledger data into Global temp table
355 -- This temp table will be joined to extract process adjustments
356 BIS_COLLECTION_UTILITIES.PUT_LINE (
357 'Loading Ledger data into temp table ...');
358 OPI_DBI_BOUNDS_PKG.load_opm_org_ledger_data;
359
360 l_stmt_id := 57;
361 -- Committing the data. Since the temp table is made with On Commit preserve rows
362 -- there will be no problem.
363 commit;
364
365 l_stmt_id := 60;
366 -- Extract the adjustment entry data from MMT/MTA using the
367 -- bounds just set up.
368 BIS_COLLECTION_UTILITIES.PUT_LINE (
369 'Extracting cycle count adjustment information for discrete manufacturing orgs ...');
370 extract_discrete_adj_init (s_global_start_date);
371
372 l_stmt_id := 70;
373 -- Due to the bulk insert in parallel mode, commit before
374 -- inputting more data to the staging table.
375 commit;
376
377 l_stmt_id := 80;
378 -- Extract the adjustment entry data from MMT/MTA using the
379 -- bounds just set up.
380 BIS_COLLECTION_UTILITIES.PUT_LINE (
381 'Extracting cycle count adjustment information for Process manufacturing orgs ...');
382 extract_process_adj_init (s_global_start_date);
383
384 l_stmt_id := 90;
385 -- Due to the bulk insert in parallel mode, commit before
386 -- inputting more data to the staging table.
387 commit;
388
389 l_stmt_id := 100;
390 -- Extract the exact matches entry data from MCCE. Collect all rows
391 -- with last_update_date greater than the global start date.
392 BIS_COLLECTION_UTILITIES.PUT_LINE (
393 'Extracting cycle count exact match information for discrete manufacturing organizations ...');
394 extract_exact_matches_init ();
395
396 l_stmt_id := 110;
397 -- Due to the bulk insert in parallel mode, commit before
398 -- moving ahead.
399 commit;
400
401 l_stmt_id := 120;
402 -- Compute the conversion rates for all org/date pairs in the
403 -- staging table. This API will now commit, due to an
404 -- insert+append hint.
405 BIS_COLLECTION_UTILITIES.PUT_LINE (
406 'Computing conversion rates for all extracted cycle counting data ...');
407 compute_cca_conv_rates_init (s_global_curr_code, s_global_rate_type);
408
409 l_stmt_id := 130;
410 -- Commit the conversion rates found before finally merging all
411 -- data to the fact table.
412 commit;
413
414 l_stmt_id := 140;
415 -- Insert all the data into the fact table in one shot.
416 BIS_COLLECTION_UTILITIES.PUT_LINE ('Inserting data into the fact table ...');
417 populate_fact_init ();
418
419 l_stmt_id := 150;
420 -- Update the bounds table
421 BIS_COLLECTION_UTILITIES.PUT_LINE ('Updating run time bounds for next run ...');
422 OPI_DBI_BOUNDS_PKG.SET_LOAD_SUCCESSFUL(C_ETL_TYPE, C_LOAD_INIT);
423
424 l_stmt_id := 160;
425 -- Truncate the staging table, commit the data to the fact table
426 -- and update the CCA related bounds in one database transaction
427 -- for consistency.
428 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
429 'opi_dbi_inv_cca_stg');
430
431 l_stmt_id := 170;
432 -- Truncate the MMT staging table, commit the data to the fact table
433 -- and update the CCA related bounds in one database transaction
434 -- for consistency.
435 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
436 'opi_dbi_mmt_cca_stg');
437
438
439 -- return successfully
440 retcode := C_SUCCESS;
441 errbuf := '';
442
443 BIS_COLLECTION_UTILITIES.PUT_LINE ('Cycle count data extracted into the fact table successfully.');
444
445 RETURN;
446
447 EXCEPTION
448
449
450 WHEN INITIALIZATION_ERROR THEN
451
452 rollback;
453
454 -- report the error
455 retcode := C_ERROR;
456 errbuf := 'Inventory Cycle Count Accuracy ETL initial load terminated with errors. Please check the concurrent program log file for errors.';
457 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
458 (INITIALIZATION_ERROR_MESG,
459 l_proc_name, l_stmt_id));
460 RETURN;
461
462 WHEN BOUNDS_SETUP_ERROR THEN
463
464 rollback;
465
466 -- report the error
467 retcode := C_ERROR;
468 errbuf := 'Inventory Cycle Count Accuracy ETL initial load terminated with errors. Please check the concurrent program log file for errors.';
469 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
470 (BOUNDS_SETUP_ERROR_MESG,
471 l_proc_name, l_stmt_id));
472 RETURN;
473
474
475 WHEN ADJUSTMENT_EXTR_ERROR THEN
476
477 rollback;
478
479 -- report the error
480 retcode := C_ERROR;
481 errbuf := 'Inventory Cycle Count Accuracy ETL initial load terminated with errors. Please check the concurrent program log file for errors.';
482 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
483 (ADJUSTMENT_EXTR_ERROR_MESG,
484 l_proc_name, l_stmt_id));
485 RETURN;
486
487
488 WHEN EXACT_MATCH_EXTR_ERROR THEN
489
490 rollback;
491
492 -- report the error
493 retcode := C_ERROR;
494 errbuf := 'Inventory Cycle Count Accuracy ETL initial load terminated with errors. Please check the concurrent program log file for errors.';
495 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
496 (EXACT_MATCH_EXTR_ERROR_MESG,
497 l_proc_name, l_stmt_id));
498 RETURN;
499
500 WHEN BOUNDS_UPDATE_ERROR THEN
501
502 rollback;
503
504 -- report the error
505 retcode := C_ERROR;
506 errbuf := 'Inventory Cycle Count Accuracy ETL initial load terminated with errors. Please check the concurrent program log file for errors.';
507 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
508 (BOUNDS_UPDATE_ERROR_MESG,
509 l_proc_name, l_stmt_id));
510 RETURN;
511
512
513 WHEN CONV_RATES_ERROR THEN
514
515 rollback;
516
517 -- report the error
518 retcode := C_ERROR;
519 errbuf := 'Inventory Cycle Count Accuracy ETL initial load terminated with errors. Please check the concurrent program log file for errors.';
520 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
521 (CONV_RATES_ERROR_MESG,
522 l_proc_name, l_stmt_id));
523 RETURN;
524
525
526 WHEN FACT_MERGE_ERROR THEN
527
528 rollback;
529
530 -- report the error
531 retcode := C_ERROR;
532 errbuf := 'Inventory Cycle Count Accuracy ETL initial load terminated with errors. Please check the concurrent program log file for errors.';
533 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
534 (FACT_MERGE_ERROR_MESG,
535 l_proc_name, l_stmt_id));
536 RETURN;
537
538
539 WHEN OPM_EXTRACTION_ERROR THEN
540
541 rollback;
542
543 -- report the error
544 retcode := C_ERROR;
545 errbuf := 'Inventory Cycle Count Accuracy ETL initial load terminated with errors. Please check the concurrent program log file for errors.';
546 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
547 (OPM_EXTRACTION_ERROR_MESG,
548 l_proc_name, l_stmt_id));
549 RETURN;
550
551 WHEN OTHERS THEN
552
553 rollback;
554
555 -- report the error
556 retcode := C_ERROR;
557 errbuf := 'Inventory Cycle Count Accuracy ETL initial load terminated with errors. Please check the concurrent program log file for errors.';
558
559 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
560 l_stmt_id));
561
562 RETURN;
563
564
565 END run_initial_load;
566
567
568 /* cca_initialize_init
569
570 Check that the primary currency code and global start date have been
571 set up.
572
573 Get the OPI schema info.
574
575 Check if the log table has rows created by the Inventory Value ETL.
576
577 Truncate all the needed tables for the initial load.
578
579 Note: Do not check to see if this initial load has been run previously.
580
581 History:
582 Date Author Action
583 01/12/04 Dinkar Gupta Defined procedure.
584 04/13/04 Dinkar Gupta Changed procedure so that it does not
585 throw an exception in case no MMT/MIF
586 rows are found in the log. The behaviour
587 can happen in case of purely process
588 manufacturing instances.
589 05/14/04 Dinkar Gupta Added hints to alter SQL hash and sort
590 area sizes based on perf team
591 recommendation.
592 08/17/04 Dinkar Gupta Added Secondary Currency support.
593
594 07/05/05 Vishal Gupta Made changes for R12. No check
595 pertaining to logs
596 */
597
598 PROCEDURE cca_initialize_init
599 IS
600
601 l_proc_name CONSTANT VARCHAR2 (40) := 'cca_initialize_init';
602 l_stmt_id NUMBER;
603
604
605 BEGIN
606
607 -- initialization block
608 l_stmt_id := 0;
609
610 l_stmt_id := 10;
611 -- Check for the primary currency code and global start date setup.
612 -- These two parameters must be set up prior to any DBI load.
613 IF (NOT (check_global_setup ())) THEN
614 RAISE GLOBAL_SETUP_MISSING;
615 END IF;
616
617 l_stmt_id := 20;
618 -- Obtain the OPI schema name to allow truncation of various tables
619 -- get session parameters
620 IF (NOT (fnd_installation.get_app_info('OPI', s_status, s_industry,
621 s_opi_schema))) THEN
622 RAISE SCHEMA_INFO_NOT_FOUND;
623 END IF;
624
625
626 -- Get the global start date
627 l_stmt_id := 30;
628 s_global_start_date := trunc (bis_common_parameters.get_global_start_date);
629 IF (s_global_start_date IS NULL) THEN
630 RAISE GLOBAL_START_DATE_NULL;
631 END IF;
632
633
634 l_stmt_id := 40;
635 -- Get the DBI global currency code
636 s_global_curr_code := bis_common_parameters.get_currency_code;
637
638 l_stmt_id := 50;
639 IF (s_global_curr_code IS NULL) THEN
640 RAISE NO_GLOBAL_CURR_CODE;
641 END IF;
642
643
644 l_stmt_id := 60;
645 -- Get the DBI Global rate type
646 s_global_rate_type := bis_common_parameters.get_rate_type;
647
648 l_stmt_id := 70;
649 IF (s_global_rate_type IS NULL) THEN
650 RAISE NO_GLOBAL_RATE_TYPE;
651 END IF;
652
653 l_stmt_id := 80;
654 -- Get the DBI secondary currency code
655 s_secondary_curr_code := bis_common_parameters.get_secondary_currency_code;
656
657 l_stmt_id := 90;
658 -- Get the DBI Global rate type
659 s_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
660
661 l_stmt_id := 100;
662 IF ( (s_secondary_curr_code IS NULL AND
663 s_secondary_rate_type IS NOT NULL)
664 OR (s_secondary_curr_code IS NOT NULL AND
665 s_secondary_rate_type IS NULL) ) THEN
666 RAISE SEC_CURR_SETUP_INVALID;
667 END IF;
668
669
670 -- Truncate the following tables (in case of exceptions, nothing
671 -- special to do here because it is a database error):
672
673 l_stmt_id := 120;
674 -- Staging table
675 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
676 'opi_dbi_inv_cca_stg');
677
678 l_stmt_id := 130;
679 -- Staging table
680 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
681 'opi_dbi_mmt_cca_stg');
682
683 l_stmt_id := 140;
684 -- Conversion rates table
685 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
686 'opi_dbi_inv_cca_conv');
687
688
689 l_stmt_id := 150;
690 -- MV Log on the fact table, because the purge directive on the
691 -- fact table does not work.
692 --EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
693 -- 'mlog$_opi_dbi_inv_cca_f' );
694
695
696 l_stmt_id := 160;
697 -- Fact table including the MV log on the table, for efficiency
698 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
699 'opi_dbi_inv_cca_f PURGE MATERIALIZED VIEW LOG');
700
701
702 -- Not using
703 l_stmt_id := 170;
704 -- Temp table for bounds computation
705 -- EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
706 -- 'opi_dbi_inv_value_log_tmp');
707
708
709 l_stmt_id := 180;
710 -- User and login id's
711 s_user_id := nvl(fnd_global.user_id, -1);
712 s_login_id := nvl(fnd_global.login_id, -1);
713 s_program_id := nvl(fnd_global.conc_program_id, -1);
714 s_program_login_id := nvl(fnd_global.conc_login_id , -1);
715 s_program_application_id := nvl(fnd_global.prog_appl_id , -1);
716 s_request_id := nvl(fnd_global.conc_request_id, -1);
717
718 l_stmt_id := 190;
719 -- Initial load requires lots of memory for OLTP table
720 -- hash joins. Performance team recommends the following
721 -- 2 alter session commands.
722 EXECUTE IMMEDIATE ('ALTER SESSION SET SORT_AREA_SIZE=100000000');
723 EXECUTE IMMEDIATE ('ALTER SESSION SET HASH_AREA_SIZE=100000000');
724
725 l_stmt_id := 200;
726 -- R12 Migration date
727 -- will be uncommented when this package is available
728 opi_dbi_rpt_util_pkg.get_inv_convergence_date (s_r12_migration_date);
729
730 RETURN;
731
732 EXCEPTION
733
734 WHEN GLOBAL_SETUP_MISSING THEN
735
736 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (GLOBAL_SETUP_MISSING_MESG,
737 l_proc_name, l_stmt_id));
738 RAISE INITIALIZATION_ERROR;
739
740 WHEN SCHEMA_INFO_NOT_FOUND THEN
741
742 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
743 (SCHEMA_INFO_NOT_FOUND_MESG,
744 l_proc_name, l_stmt_id));
745 RAISE INITIALIZATION_ERROR;
746
747 WHEN INIT_BOUNDS_MISSING THEN
748
749 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
750 (INIT_BOUNDS_MISSING_MESG,
751 l_proc_name, l_stmt_id));
752 RAISE INITIALIZATION_ERROR;
753
754 WHEN GLOBAL_START_DATE_NULL THEN
755
756 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
757 (GLOBAL_START_DATE_NULL_MESG,
758 l_proc_name, l_stmt_id));
759 RAISE INITIALIZATION_ERROR;
760
761
762 WHEN NO_GLOBAL_CURR_CODE THEN
763
764 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
765 (NO_GLOBAL_CURR_CODE_MESG,
766 l_proc_name, l_stmt_id));
767 RAISE INITIALIZATION_ERROR;
768
769
770 WHEN NO_GLOBAL_RATE_TYPE THEN
771
772 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
773 (NO_GLOBAL_RATE_TYPE_MESG,
774 l_proc_name, l_stmt_id));
775 RAISE INITIALIZATION_ERROR;
776
777
778 WHEN SEC_CURR_SETUP_INVALID THEN
779 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
780 (SEC_CURR_SETUP_INVALID_MESG,
781 l_proc_name, l_stmt_id));
782
783 RAISE INITIALIZATION_ERROR;
784
785 WHEN OTHERS THEN
786
787 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
788 l_stmt_id));
789
790 RAISE INITIALIZATION_ERROR;
791
792
793 END cca_initialize_init;
794
795 /* extract_cca_mmt_init
796
797 Extract Cycle count type of transactions from MMT which are after Global
798 start date. For this transaction_source_type_id = 9 . This contains 2
799 type of Cycle count transactions :
800 Transaction_type_id = 4 Cycle Count Adjustments
801 Trasnaction_type_id = 5 Cycle Count Subinventory Transfer
802
803 */
804
805 PROCEDURE extract_cca_mmt_init
806 IS
807 l_stmt_id NUMBER;
808 l_proc_name VARCHAR2(40) := 'Extract_cca_mmt_init';
809
810 BEGIN
811
812 l_stmt_id :=0 ;
813
814
815 Insert /*+ append parallel (opi_dbi_mmt_cca_stg) */
816 into OPI_DBI_MMT_CCA_STG
817 (TRANSACTION_ID,
818 ORGANIZATION_ID,
819 INVENTORY_ITEM_ID,
820 CYCLE_COUNT_ID,
821 SUBINVENTORY_CODE,
822 TRANSFER_SUBINVENTORY,
823 TRANSACTION_DATE,
824 TRANSACTION_TYPE_ID,
825 PRIMARY_QUANTITY)
826 Select TRANSACTION_ID,
827 ORGANIZATION_ID,
828 INVENTORY_ITEM_ID,
829 CYCLE_COUNT_ID,
830 SUBINVENTORY_CODE,
831 TRANSFER_SUBINVENTORY,
832 TRANSACTION_DATE,
833 TRANSACTION_TYPE_ID,
834 PRIMARY_QUANTITY
835 From MTL_MATERIAL_TRANSACTIONS
836 Where Transaction_date >= s_global_start_date
837 and Transaction_source_type_id = 9 ;
838
839
840 -- commit will handled in wrapper
841
842
843 RETURN;
844
845 EXCEPTION
846
847 WHEN OTHERS THEN
848
849 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
850 l_stmt_id));
851
852 RAISE CCA_MMT_STG_ERROR;
853
854 END extract_cca_mmt_init;
855
856
857 /* extract_discrete_adj_init
858
859 Extract discrete adjustment entry data from MTL_CYCLE_COUNT_ENTRIES (MCCE),
860 MMT and MTA. Adjustments can get costed well after approval and
861 interim cost updates may cause the values recorded in MCCE to
862 be different. Thus we need to get the cost associated to the item
863 using the value in MTA so that the reported adjustments match
864 the MTA reported value.
865
866 However, due to issues with layered costing in LIFO/FIFO orgs, the
867 system inventory value is computed using the cost at the time when
868 the entry was made. Thus the reported system inventory value
869 matches the one reported in Oracle Forms.
870
871 We are only interested in entries completed after the global start
872 date (i.e. entry_status_code = 5 and approval_date > global start date).
873 But it is not possible mmt_mta join to return records where cycle count
874 entries are not completed.
875
876 The only MTA rows we are interested in are those that affect the
877 inventory account (accounting line type = 1).
878
879 MTA does not store rows for expense items, or items in expense
880 subinventories. Thus the join between MMT and MTA will have to
881 be an outer join. Rows with no matches will be assigned an
882 item cost of 0.
883
884 MCCE does not store quantities in the primary uom_code. If the
885 transaction uom is not the same as the primary uom, all
886 quantities will have to be converted to the primary uom.
887
888 There is a need to check for adjustment quantity = 0 serial item counts
889 can have count level matches, but serial number level adjustments,
890 To handle this, make four enhancements:
891 1. Declare exact matches if the sum of the MMT primary quantity is 0.
892 2. Pick the MCCE item_unit_cost if the quantity is 0 (check for expense
893 subs before that).
894 3. Set the tolerance to 0 if there is an adjustment in another
895 subinventory that the one counted. This will automatically
896 make any adjustments against it misses.
897 4. The system_inventory_qty for the other subinventory is the negative
898 of the adjustment quantity, so that the sum of the adjustment
899 and system quantity is always 0.
900
901 We will only be scanning MMT rows between the transaction ranges
902 in the log table.
903
904 Also, since the fact table is at a cycle count entry level,
905 it is impossible for the sum (mmt.primary_quantity) to be 0. Thus
906 the item_cost formula of sum (value)/sum (quantity) is always
907 valid and we need not worry about sum (quantity) being 0 or NULL.
908
909 Since the join conditions between MCCE - MMT - MTA is one to many
910 to many, join MMT/MTA on transaction_id first.
911
912 Also, to handle serial item adjustments where the adjustment
913 automatically issues an item from a sub to move it to the next,
914 use the subinventory_code from MMT, not MCCE.
915
916 There cannot be an entry in MMT and MTA for which cycle count has not been approved.
917 ie entry_status_code = 5.
918
919 -------- R12 Changes
920 1> Subinventory Transfer type of transaction (MMT.transaction_type_id = 5)
921 is seen in Cycle Count. This case occurs when a LPN is received in
922 say subinv. BULK, and cycle count is done in say subinv. CASE for a specific LPN.
923 This type of transaction has 2 rows in MMT, one for issue transaction
924 and other for receipt transaction. But the MTA has no rows for receipt
925 transaction while it has 2 entries differing only in MTA.primary_quantity.
926
927 The subinventory for issue transaction where mta.primary_quantity is -ve and
928 transfer_subinventory for issue transaction where mta.primary_quantity is
929 positive.
930 This case will not be encountered for Expense item as LPN cannot be made
931 for expense item.
932 Such transactions can only be done through Warehouse mobile.
933
934
935 Currently for such transactions MMT.cycle_count_id is not populated.
936 Bug # 4392340 has been logged for the same.
937
938 2> Changes to log table and columns.
939
940 3> mmt staging table is introduced to collect all mmt transactions
941 for discrete and process orgs at one go. Here as we are joining
942 with bounds table which has bounds for discrete orgs by
943 transaction_id we do not need to put any filters to get only
944 discrete organizations. For process there is one single record in
945 bounds table which has bound_level_entity_id as NULL and hence mmt
946 records for process orgs will not be selected in the extract
947 below.
948
949
950 ----------------------
951
952
953 Do not commit data. Let the wrapper coordinate committing of data.
954
955 Paramters:
956 IN:
957 p_global_start_date - DBI global start date
958
959 History:
960 Date Author Action
961 01/14/04 Dinkar Gupta Defined procedure.
962 02/10/04 Dinkar Gupta Added group by transaction_id
963 in the mmt/mta join.
964 Used MMT subinventory_code
965 to handle serial item issues.
966 02/18/04 Dinkar Gupta Added condition to handle
967 cases where MMT quantity is 0 for
968 serial items.
969 Also added condition to report miss
970 for adjustments against any other
971 sub than the one being counted.
972 03/10/04 Dinkar Gupta Used item_cost from MCCE for system
973 inventory value for LIFO/FIFO org
974 issue.
975 06/22/05 Vishal Gupta Refer to R12 Changes in the above
976 header.
977
978
979 */
980 PROCEDURE extract_discrete_adj_init (p_global_start_date IN DATE)
981 IS
982 l_proc_name CONSTANT VARCHAR2 (40) := 'extract_adjustments_init';
983 l_stmt_id NUMBER;
984
985 BEGIN
986
987 -- initialization block
988 l_stmt_id := 0;
989
990 l_stmt_id := 10;
991 -- This procedure only inserts all adjustment entry data into the
992 -- staging table. For an explanation of the logic, please refer to the
993 -- procedure header.
994
995 -- Join condition justifications
996 --
997 -- to MCCC - on org, header and abc_class. The org is redundant,
998 -- but this should allow use of U1 index on MCCC.
999 --
1000 -- to MCCI - on header and item. This should allow use of U1 index
1001 -- on MCCI.
1002 --
1003 -- to MCCH - on header. This should allow use of U1 index.
1004 --
1005 -- to MCCE - on entry. This should allow use of U1 index.
1006 --
1007 -- to MSI - on org and item. This should allow use of the U1 index.
1008 --
1009 -- to SUBS - on org and subinventory code
1010 INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
1011 INTO opi_dbi_inv_cca_stg (
1012 organization_id,
1013 inventory_item_id,
1014 cycle_count_header_id,
1015 abc_class_id,
1016 subinventory_code,
1017 cycle_count_entry_id,
1018 source,
1019 approval_date,
1020 uom_code,
1021 system_inventory_qty,
1022 positive_adjustment_qty,
1023 negative_adjustment_qty,
1024 item_unit_cost,
1025 item_adj_unit_cost,
1026 hit_miss_pos,
1027 hit_miss_neg,
1028 exact_match)
1029 SELECT /*+ parallel(mcce) parallel(mcch) parallel(mcci)
1030 parallel(mccc) parallel(msi) parallel(subs)
1031 parallel(mmt_mta)
1032 use_hash(mcce mcch mcci mccc msi subs) */
1033 mcce.organization_id,
1034 mcce.inventory_item_id,
1035 mcce.cycle_count_header_id,
1036 to_char (mccc.abc_class_id),
1037 mmt_mta.subinventory_code,
1038 to_char (mcce.cycle_count_entry_id),
1039 C_OPI_SOURCE,
1040 trunc (mcce.approval_date),
1041 msi.primary_uom_code,
1042 decode (mcce.count_uom_current,
1043 msi.primary_uom_code,
1044 decode (mmt_mta.subinventory_code,
1045 mcce.subinventory, mcce.system_quantity_current,
1046 -1 * sum (mmt_mta.primary_quantity)),
1047 decode (mmt_mta.subinventory_code,
1048 mcce.subinventory,
1049 inv_convert.inv_um_convert
1050 (mcce.inventory_item_id, C_MMT_PRECISION,
1051 mcce.system_quantity_current,
1052 mcce.count_uom_current,
1053 msi.primary_uom_code, NULL, NULL),
1054 inv_convert.inv_um_convert
1055 (mcce.inventory_item_id, C_MMT_PRECISION,
1056 -1 * sum (mmt_mta.primary_quantity),
1057 mcce.count_uom_current,
1058 msi.primary_uom_code, NULL, NULL))),
1059 CASE WHEN sum (mmt_mta.primary_quantity) > 0 THEN
1060 sum (mmt_mta.primary_quantity)
1061 ELSE 0
1062 END,
1063 CASE WHEN sum (mmt_mta.primary_quantity) < 0 THEN
1064 -1 * sum (mmt_mta.primary_quantity)
1065 ELSE 0
1066 END,
1067 decode (subs.asset_inventory,
1068 C_EXPENSE_SUBINVENTORY, 0,
1069 mcce.item_unit_cost),
1070 decode (sum (mmt_mta.primary_quantity),
1071 0, decode (subs.asset_inventory,
1072 C_EXPENSE_SUBINVENTORY, 0,
1073 mcce.item_unit_cost),
1074 nvl (sum (mmt_mta.base_transaction_value)/
1075 sum (mmt_mta.primary_quantity), 0)),
1076 decode (mmt_mta.subinventory_code,
1077 mcce.subinventory, nvl (mccc.hit_miss_tolerance_positive,
1078 mcch.hit_miss_tolerance_positive),
1079 0),
1080 decode (mmt_mta.subinventory_code,
1081 mcce.subinventory, nvl (mccc.hit_miss_tolerance_negative,
1082 mcch.hit_miss_tolerance_negative),
1083 0),
1084 decode (sum (mmt_mta.primary_quantity),
1085 0, C_EXACT_MATCH,
1086 C_NO_MATCH)
1087 FROM mtl_cycle_count_entries mcce,
1088 mtl_cycle_count_headers mcch,
1089 mtl_cycle_count_items mcci,
1090 mtl_cycle_count_classes mccc,
1091 mtl_system_items_b msi,
1092 mtl_secondary_inventories subs,
1093 (SELECT /*+ no_merge
1094 parallel(mmt) parallel(mta) parallel(log)
1095 use_hash(mmt mta log)*/
1096 mmt.organization_id,
1097 mmt.inventory_item_id,
1098 mmt.transaction_date,
1099 mmt.cycle_count_id,
1100 decode (mmt.transaction_type_id,
1101 5, decode(sign(nvl(mta.primary_quantity,0)),
1102 1, 0,
1103 mmt.primary_quantity),
1104 mmt.primary_quantity) primary_quantity,
1105 decode (mmt.transaction_type_id,
1106 5, decode(sign(nvl(mta.primary_quantity,0)),
1107 1, mmt.transfer_subinventory,
1108 mmt.subinventory_code),
1109 mmt.subinventory_code) subinventory_code,
1110 sum (mta.base_transaction_value) base_transaction_value
1111 FROM opi_dbi_mmt_cca_stg mmt,
1112 mtl_transaction_accounts mta,
1113 opi_dbi_conc_prog_run_log log
1114 WHERE log.etl_type = C_ETL_TYPE
1115 AND log.driving_table_code = C_LOG_MMT_DRV_TBL
1116 AND log.load_type = C_LOAD_INIT
1117 AND mmt.organization_id = log.bound_level_entity_id
1118 AND mmt.transaction_id >= log.from_bound_id
1119 AND mmt.transaction_id < log.to_bound_id
1120 AND mmt.transaction_date >= p_global_start_date
1121 -- AND mmt.transaction_type_id = C_MMT_CYCLE_COUNT_ADJ
1122 AND mmt.transaction_id = mta.transaction_id (+)
1123 AND nvl (mta.accounting_line_type, C_INVENTORY_ACCOUNT) =
1124 C_INVENTORY_ACCOUNT -- 1
1125 GROUP BY
1126 mmt.organization_id,
1127 mmt.inventory_item_id,
1128 mmt.transaction_date,
1129 mmt.cycle_count_id,
1130 decode (mmt.transaction_type_id,
1131 5, decode(sign(nvl(mta.primary_quantity,0)),
1132 1, 0,
1133 mmt.primary_quantity),
1134 mmt.primary_quantity),
1135 decode (mmt.transaction_type_id,
1136 5, decode(sign(nvl(mta.primary_quantity,0)),
1137 1, mmt.transfer_subinventory,
1138 mmt.subinventory_code),
1139 mmt.subinventory_code),
1140 mmt.transaction_id) mmt_mta
1141 WHERE mmt_mta.organization_id = msi.organization_id
1142 AND mmt_mta.inventory_item_id = msi.inventory_item_id
1143 AND mmt_mta.cycle_count_id = mcce.cycle_count_entry_id
1144 AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY
1145 AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
1146 AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
1147 AND mcce.inventory_item_id = mcci.inventory_item_id
1148 AND mcce.organization_id = mccc.organization_id
1149 AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
1150 AND mcci.abc_class_id = mccc.abc_class_id
1151 AND mmt_mta.organization_id = subs.organization_id
1152 AND mmt_mta.subinventory_code = subs.secondary_inventory_name
1153 GROUP BY
1154 mcce.organization_id,
1155 mcce.inventory_item_id,
1156 mcce.cycle_count_header_id,
1157 mccc.abc_class_id,
1158 mmt_mta.subinventory_code,
1159 mcce.cycle_count_entry_id,
1160 trunc (mcce.approval_date),
1161 msi.primary_uom_code,
1162 mcce.count_uom_current,
1163 mcce.system_quantity_current,
1164 subs.asset_inventory,
1165 mcce.item_unit_cost,
1166 mcce.subinventory,
1167 mccc.hit_miss_tolerance_positive,
1168 mcch.hit_miss_tolerance_positive,
1169 mcce.subinventory,
1170 mccc.hit_miss_tolerance_negative,
1171 mcch.hit_miss_tolerance_negative;
1172
1173 RETURN;
1174
1175 EXCEPTION
1176
1177 WHEN OTHERS THEN
1178
1179 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1180 l_stmt_id));
1181
1182 RAISE ADJUSTMENT_EXTR_ERROR;
1183
1184 END extract_discrete_adj_init;
1185
1186
1187
1188 /* extract_process_adj_init
1189 Extract process adjustment entry data from MTL_CYCLE_COUNT_ENTRIES (MCCE),
1190 MMT and GTV. The transactions in GTV can be either in Draft, New or Final Mode.
1191 We will only consider Draft and Final Mode of transactions only.
1192
1193 For initial load there will not be any separate bounds for Final and Draft
1194 transaction.
1195
1196 Subinventory Transafer type of transaction for Cycle Count is considered. There
1197 is no change from discrete.
1198
1199 1> Subinventory Transfer type of transaction (MMT.transaction_type_id = 5)
1200 is seen in Cycle Count. This case occurs when a LPN is received in
1201 say BULK, and cycle count is done in say CASE for a specific LPN.
1202 This type of transaction has 2 rows in MMT, one for issue transaction
1203 and other for receipt transaction. But the GTV has no rows for receipt
1204 transaction while it has 2 entries differing only in GTV.subinventory_code.
1205 GTV.subinventory_code will have issue subinventory_code for one of two rows in GTV
1206 pertianing to issue transaction in MMT. The other row will have
1207 MMT.transfer_subinventory in GTV.subinventory_code.
1208
1209
1210 Currently for such transactions MMT.cycle_count_id is not populated.
1211 Bug # 4392340 has been logged for the same.
1212
1213 The bounds for Process is applied same for all organization. The driving table
1214 will be GTV.
1215 To isolate cycle count transaction in GTV following is required:
1216 EVENT_CLASS_CODE = 'MISC_TXN' and 'SUBINV_XFER'
1217 EVENT_TYPE_CODE = 'CYCLE_COUNT_ADJ' and 'CYCLE_COUNT_XFER'
1218 JOURNAL_LINE_TYPE = 'INV'
1219 TRANSACTION_SOURCE = 'INVENTORY'
1220
1221 We are only interested in entries completed after the global start
1222 date (i.e. entry_status_code = 5 and approval_date > global start date).
1223
1224 Unlike the case with MTA, GTV has rows for expense items, or items in
1225 expense subinventories. Hence also no outer join is required.
1226
1227 For item_unit_cost, adjustments can get costed well after approval and interim
1228 cost updates may cause the values recorded in MCCE to be different. Thus
1229 we need to get the cost associated to the item using the value in GTV so
1230 that the reported adjustments match the GTV reported value.
1231
1232 However, due to issues with layered costing in LIFO/FIFO orgs, the
1233 system inventory value is computed using the cost at the time when
1234 the entry was made. Thus the reported system inventory value
1235 matches the one reported in Oracle Forms.
1236
1237 MCCE does not store quantities in the primary uom_code. If the
1238 transaction uom is not the same as the primary uom, all
1239 quantities will have to be converted to the primary uom.
1240
1241 Also, since the fact table is at a cycle count entry level,
1242 it is impossible for the sum (mmt.primary_quantity) to be 0. Thus
1243 the item_cost formula of sum (value)/sum (quantity) is always
1244 valid and we need not worry about sum (quantity) being 0 or NULL.
1245
1246 Since the join conditions between MCCE - MMT - GTV is one to many
1247 to many, join GTV/MMT on transaction_id first.
1248
1249 IMPORTANT NOTE R12:
1250 -------------------
1251 draft and permanent quantity/value is not handled separately as in
1252 other ETLs as its assumed that either all the MMT-GTV records are
1253 in draft or permanent status for one cycle count entries.
1254
1255 Now, as we relook at all the draft records in every ETL its
1256 possible that same draft records are collected again. But as our
1257 fact is at cycle_count_entry level its assumed that either all or
1258 none of MMT-GTV for a cycle count entry are collected. Hence in
1259 Merge of cycle count values are only replaced and not added.
1260
1261 Do not commit data. Let the wrapper coordinate committing of data.
1262
1263 Paramters:
1264 IN:
1265 p_global_start_date - DBI global start date
1266
1267 History:
1268 Date Author Action
1269 06/22/05 Vishal Gupta New Procedure. To collect data for
1270 Post R12 Process cycle counting,
1271
1272
1273 */
1274 PROCEDURE extract_process_adj_init (p_global_start_date IN DATE)
1275 IS
1276 l_proc_name CONSTANT VARCHAR2 (40) := 'extract_adjustments_init';
1277 l_stmt_id NUMBER;
1278
1279 l_from_bound_date OPI_DBI_CONC_PROG_RUN_LOG.from_bound_date%type;
1280 l_to_bound_date OPI_DBI_CONC_PROG_RUN_LOG.to_bound_date%type;
1281
1282 BEGIN
1283
1284 -- initialization block
1285 l_stmt_id := 0;
1286
1287 SELECT from_bound_date, to_bound_date
1288 INTO l_from_bound_date, l_to_bound_date
1289 FROM OPI_DBI_CONC_PROG_RUN_LOG
1290 WHERE driving_table_code = C_LOG_GTV_DRV_TBL
1291 AND etl_type = C_ETL_TYPE
1292 AND load_type = C_LOAD_INIT;
1293
1294
1295 l_stmt_id := 10;
1296 -- This procedure only inserts all adjustment entry data into the
1297 -- staging table. For an explanation of the logic, please refer to the
1298 -- procedure header.
1299
1300 -- Join condition justifications
1301 --
1302 -- to MCCC - on org, header and abc_class. The org is redundant,
1303 -- but this should allow use of U1 index on MCCC.
1304 --
1305 -- to MCCI - on header and item. This should allow use of U1 index
1306 -- on MCCI.
1307 --
1308 -- to MCCH - on header. This should allow use of U1 index.
1309 --
1310 -- to MCCE - on entry. This should allow use of U1 index.
1311 --
1312 -- to MSI - on org and item. This should allow use of the U1 index.
1313 --
1314 -- to SUBS - on org and subinventory code
1315
1316
1317 INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
1318 INTO opi_dbi_inv_cca_stg (
1319 organization_id,
1320 inventory_item_id,
1321 cycle_count_header_id,
1322 abc_class_id,
1323 subinventory_code,
1324 cycle_count_entry_id,
1325 source,
1326 approval_date,
1327 uom_code,
1328 system_inventory_qty,
1329 positive_adjustment_qty,
1330 negative_adjustment_qty,
1331 item_unit_cost,
1332 item_adj_unit_cost,
1333 hit_miss_pos,
1334 hit_miss_neg,
1335 exact_match )
1336 SELECT mcce.organization_id,
1337 mcce.inventory_item_id,
1338 mcce.cycle_count_header_id,
1339 to_char (mccc.abc_class_id),
1340 mmt_gtv.subinventory_code,
1341 to_char (mcce.cycle_count_entry_id),
1342 C_OPM_SOURCE,
1343 trunc (mcce.approval_date),
1344 msi.primary_uom_code,
1345 decode (mcce.count_uom_current,
1346 msi.primary_uom_code,
1347 mcce.system_quantity_current,
1348 inv_convert.inv_um_convert ( mcce.inventory_item_id,
1349 C_MMT_PRECISION,
1350 mcce.system_quantity_current,
1351 mcce.count_uom_current,
1352 msi.primary_uom_code,
1353 NULL,
1354 NULL)) system_inventory_qty,
1355 CASE WHEN sum (mmt_gtv.primary_quantity) > 0 THEN
1356 sum (mmt_gtv.primary_quantity)
1357 ELSE 0
1358 END positive_adjustment_qty,
1359 CASE WHEN sum (mmt_gtv.primary_quantity) < 0 THEN
1360 -1 * sum (mmt_gtv.primary_quantity)
1361 ELSE 0
1362 END negative_adjustment_qty ,
1363 decode (subs.asset_inventory,
1364 C_EXPENSE_SUBINVENTORY, 0,
1365 mcce.item_unit_cost) item_unit_cost,
1366 decode (sum (mmt_gtv.primary_quantity),
1367 0, decode (subs.asset_inventory,
1368 C_EXPENSE_SUBINVENTORY, 0,
1369 mcce.item_unit_cost),
1370 nvl (sum (mmt_gtv.transaction_base_value)/
1371 sum (mmt_gtv.primary_quantity), 0)) item_adj_unit_cost,
1372 decode (mmt_gtv.subinventory_code,
1373 mcce.subinventory, nvl (mccc.hit_miss_tolerance_positive,
1374 mcch.hit_miss_tolerance_positive),
1375 0),
1376 decode (mmt_gtv.subinventory_code,
1377 mcce.subinventory, nvl (mccc.hit_miss_tolerance_negative,
1378 mcch.hit_miss_tolerance_negative),
1379 0),
1380 decode (sum (mmt_gtv.primary_quantity),
1381 0, C_EXACT_MATCH,
1382 C_NO_MATCH) exact_match
1383 FROM mtl_cycle_count_entries mcce,
1384 mtl_cycle_count_headers mcch,
1385 mtl_cycle_count_items mcci,
1386 mtl_cycle_count_classes mccc,
1387 mtl_system_items_b msi,
1388 mtl_secondary_inventories subs,
1389 -- below select is grouped at transaction_id, quantity level so that
1390 -- quantity is summed correctly and then it can be joined with
1391 -- mcce and other tables outside.
1392 (SELECT mmt.organization_id,
1393 mmt.transaction_id,
1394 mmt.inventory_item_id,
1395 mmt.cycle_count_id,
1396 DECODE (mmt.transaction_type_id ,
1397 5 , DECODE(GTV.subinventory_code,
1398 MMT.subinventory_code, MMT.primary_quantity,
1399 mmt.transfer_subinventory, -1* MMT.primary_quantity),mmt.primary_quantity) primary_quantity,
1400 DECODE (mmt.transaction_type_id ,
1401 5 ,GTV.subinventory_code,
1402 mmt.subinventory_code) subinventory_code,
1403 SUM (gtv.txn_base_value) transaction_base_value
1404 FROM mtl_material_transactions mmt ,
1405 (SELECT gt.*
1406 FROM gmf_transaction_valuation gt,
1407 opi_dbi_org_le_temp olt
1408 WHERE olt.organization_id = gt.organization_id
1409 AND olt.ledger_id = gt.ledger_id
1410 AND olt.legal_entity_id = gt.legal_entity_id
1411 AND olt.valuation_cost_type_id = gt.valuation_cost_type_id) gtv
1412 WHERE gtv.event_class_code in ('MISC_TXN' , 'SUBINV_XFER')
1413 AND gtv.event_type_code in ('CYCLE_COUNT_ADJ' , 'CYCLE_COUNT_XFER')
1414 AND gtv.journal_line_type = 'INV'
1415 AND gtv.transaction_source = 'INVENTORY'
1416 AND gtv.transaction_date >= l_from_bound_date
1417 -- for final posted records consider within the bounds
1418 -- for draft posted records consider all the txns
1419 AND DECODE( accounted_flag,
1420 NULL, gtv.final_posting_date,
1421 'D',s_global_start_date) < l_to_bound_date
1422 and gtv.transaction_id = mmt.transaction_id
1423 GROUP BY
1424 mmt.organization_id,
1425 mmt.inventory_item_id,
1426 mmt.cycle_count_id,
1427 DECODE (mmt.transaction_type_id ,
1428 5 , DECODE(GTV.subinventory_code,
1429 MMT.subinventory_code, MMT.primary_quantity,
1430 mmt.transfer_subinventory, -1* MMT.primary_quantity),mmt.primary_quantity),
1431 DECODE (mmt.transaction_type_id ,
1432 5 ,GTV.subinventory_code,
1433 mmt.subinventory_code) ,
1434 mmt.transaction_id) mmt_gtv
1435 WHERE mmt_gtv.organization_id = msi.organization_id
1436 AND mmt_gtv.inventory_item_id = msi.inventory_item_id
1437 AND mmt_gtv.cycle_count_id = mcce.cycle_count_entry_id
1438 -- mmt records are created only after cycle count is approved
1439 -- hence its not possible that entry_status_code is not approved.
1440 -- it is ensured that all costed txns are collected
1441 AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY -- 5
1442 AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
1443 AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
1444 AND mcce.inventory_item_id = mcci.inventory_item_id
1445 AND mcce.organization_id = mccc.organization_id
1446 AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
1447 AND mcci.abc_class_id = mccc.abc_class_id
1448 AND mmt_gtv.organization_id = subs.organization_id
1449 AND mmt_gtv.subinventory_code = subs.secondary_inventory_name
1450 GROUP BY
1451 mcce.organization_id,
1452 mcce.inventory_item_id,
1453 mcce.cycle_count_header_id,
1454 mccc.abc_class_id,
1455 mmt_gtv.subinventory_code,
1456 mcce.cycle_count_entry_id,
1457 trunc (mcce.approval_date),
1458 msi.primary_uom_code,
1459 mcce.count_uom_current,
1460 mcce.system_quantity_current,
1461 subs.asset_inventory,
1462 mcce.item_unit_cost,
1463 mcce.subinventory,
1464 mccc.hit_miss_tolerance_positive,
1465 mcch.hit_miss_tolerance_positive,
1466 mcce.subinventory,
1467 mccc.hit_miss_tolerance_negative,
1468 mcch.hit_miss_tolerance_negative;
1469
1470
1471
1472 RETURN;
1473
1474 EXCEPTION
1475
1476 WHEN NO_DATA_FOUND THEN
1477
1478 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1479 l_stmt_id));
1480
1481 RAISE ADJUSTMENT_EXTR_ERROR;
1482
1483
1484 WHEN OTHERS THEN
1485
1486 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1487 l_stmt_id));
1488
1489 RAISE ADJUSTMENT_EXTR_ERROR;
1490
1491 END extract_process_adj_init;
1492
1493
1494
1495 /* extract_exact_matches_init
1496
1497 Extract the exact match entry data from the MTL_CYCLE_COUNT_ENTRIES
1498 table. Exact match data is easier to handle than adjustment entries
1499 because exact matches are approved on creation and there are no
1500 other transaction tables that need to be visited.
1501
1502 We are only interested in entries completed after the global start
1503 date (i.e. entry_status_code = 5 and approval_date > global start date).
1504
1505 The adjustment quantity is always 0 and hit/miss tolerances are not
1506 needed since all entries are, by definition, hits. We still need to join
1507 to MTL_CYCLE_COUNT_CLASSES to get the count and count class information.
1508
1509 No need to join to MTL_CYCLE_COUNT_HEADERS as we don't need to
1510 get any tolerances.
1511
1512 MCCE does not store quantities in the primary uom_code. If the
1513 transaction uom is not the same as the primary uom, all
1514 quantities will have to be converted to the primary uom.
1515
1516 The date range being extracted is specified in the log table.
1517
1518 However, since we are dealing with last_update_dates, we need to
1519 be sure that the dates in the log tables have timestamps so that
1520 we do not collect partially collected days again.
1521
1522 The one special case is that we need to identify expense
1523 subinventories and set the item cost to 0 for all entries
1524 bearing those subinventory codes.
1525
1526 ------------ R12 Changes
1527 Since Process exact matches will be extrracted in the same insert.
1528 Date bounds speific to organization based on first uncosted transaction
1529 cannot be applied. Hence we will use date bounds that are being used for
1530 the process adjustments. ie Collect all the exact matches for which
1531 MCCE.approval_date (MCCE.last_update_date) is between global
1532 start date and the initial program start date.
1533
1534 Discrete Adjustments are collected only upto first uncosted transaction
1535 date. Hence exact matches and discrete cycle cout adjustments will be
1536 out of sync if there are uncosted transactions. Please refer bug 4395280.
1537
1538
1539 ------------
1540
1541
1542 Do not commit data. Let the wrapper coordinate committing of data.
1543
1544 Parameters:
1545 None.
1546
1547 History:
1548 Date Author Action
1549 01/15/04 Dinkar Gupta Defined procedure.
1550 02/05/04 Dinkar Gupta Added logic to join to the from and to
1551 transaction dates in the log table.
1552 06/22/05 Vishal Gupta Made changes on bounds.
1553
1554 */
1555 PROCEDURE extract_exact_matches_init
1556 IS
1557 l_proc_name CONSTANT VARCHAR2 (40) := 'extract_exact_matches_init';
1558 l_stmt_id NUMBER;
1559 l_from_bound_date OPI_DBI_CONC_PROG_RUN_LOG.from_bound_date%type;
1560 l_to_bound_date OPI_DBI_CONC_PROG_RUN_LOG.to_bound_date%type;
1561
1562 BEGIN
1563
1564 -- initialization block
1565 l_stmt_id := 0;
1566
1567 SELECT from_bound_date, to_bound_date
1568 INTO l_from_bound_date, l_to_bound_date
1569 FROM OPI_DBI_CONC_PROG_RUN_LOG
1570 WHERE driving_table_code = C_LOG_GTV_DRV_TBL
1571 AND etl_type = C_ETL_TYPE
1572 AND load_type = C_LOAD_INIT;
1573
1574
1575 l_stmt_id := 10;
1576 -- This procedure will extract all all exact match data from MCCE
1577 -- and insert it into the staging table. For SQL logic, please refer
1578 -- to the procedure header
1579
1580 -- Join condition justifications
1581 --
1582 -- to MCCC - on org, header and abc_class. The org is redundant,
1583 -- but this should allow use of U1 index on MCCC.
1584 -- Note the explicit check for the 0 adjustment quantity. This should
1585 -- ideally not be required but we found an example (which we have not
1586 -- been able to recreate) where there was an adjustment on a serialized
1587 -- item but the adjustment date was null in MCCE and
1588 -- MTL_CC_SERIAL_NUMBERS. To filter that case, we filter explicitly on
1589 -- on the quantity. Most likely, this is an OLTP bug that will be
1590 -- fixed.
1591 --
1592 -- to MCCI - on header and item. This should allow use of U1 index
1593 -- on MCCI.
1594 --
1595 -- to MCCE - on entry. This should allow use of U1 index.
1596 -- On entry_status_code. This is the leading column of the N6 index.
1597 -- Filter on last_update_date. This should use the newly created
1598 -- N8 (?) index.
1599 --
1600 -- no join needed to log - as we can extract GTV date bounds into variables
1601 -- .
1602
1603
1604
1605 INSERT /*+ append parallel (opi_dbi_inv_cca_stg) */
1606 INTO opi_dbi_inv_cca_stg (
1607 organization_id,
1608 inventory_item_id,
1609 cycle_count_header_id,
1610 abc_class_id,
1611 subinventory_code,
1612 cycle_count_entry_id,
1613 source,
1614 approval_date,
1615 uom_code,
1616 system_inventory_qty,
1617 positive_adjustment_qty,
1618 negative_adjustment_qty,
1619 item_unit_cost,
1620 item_adj_unit_cost,
1621 hit_miss_pos,
1622 hit_miss_neg,
1623 exact_match)
1624 SELECT /*+ parallel(mcce) parallel(mcci) parallel(mccc)
1625 parallel(msi) parallel(subs) parallel(log)
1626 use_hash(mcci mccc msi subs) */
1627 mcce.organization_id,
1628 mcce.inventory_item_id,
1629 mcce.cycle_count_header_id,
1630 to_char (mccc.abc_class_id),
1631 mcce.subinventory,
1632 to_char (mcce.cycle_count_entry_id),
1633 decode(mp.process_enabled_flag,
1634 'Y',C_OPM_SOURCE,
1635 C_OPI_SOURCE ) source,
1636 trunc (mcce.approval_date),
1637 msi.primary_uom_code,
1638 decode (mcce.count_uom_current,
1639 msi.primary_uom_code, mcce.system_quantity_current,
1640 inv_convert.inv_um_convert
1641 (mcce.inventory_item_id, C_MMT_PRECISION,
1642 mcce.system_quantity_current, mcce.count_uom_current,
1643 msi.primary_uom_code, NULL, NULL)),
1644 0,
1645 0,
1646 decode (subs.asset_inventory,
1647 C_EXPENSE_SUBINVENTORY, 0,
1648 mcce.item_unit_cost),
1649 0,
1650 NULL,
1651 NULL,
1652 C_EXACT_MATCH
1653 FROM mtl_cycle_count_entries mcce,
1654 mtl_cycle_count_items mcci,
1655 mtl_cycle_count_classes mccc,
1656 mtl_system_items_b msi,
1657 mtl_secondary_inventories subs,
1658 mtl_parameters mp
1659 -- exact matches are approved at the time entry is completed
1660 -- hence it is not possible that status of a cycle count entry
1661 -- changes once the load is collected for that date range.
1662 WHERE mcce.last_update_date >= l_from_bound_date
1663 AND mcce.last_update_date < l_to_bound_date
1664 AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY
1665 AND mcce.organization_id = mp.organization_id
1666 AND mcce.adjustment_date IS NULL
1667 AND mcce.adjustment_quantity = 0
1668 AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
1669 AND mcce.inventory_item_id = mcci.inventory_item_id
1670 AND mcce.organization_id = mccc.organization_id
1671 AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
1672 AND mcci.abc_class_id = mccc.abc_class_id
1673 AND mcce.organization_id = msi.organization_id
1674 AND mcce.inventory_item_id = msi.inventory_item_id
1675 AND mcce.organization_id = subs.organization_id
1676 AND mcce.subinventory = subs.secondary_inventory_name;
1677
1678
1679 -- Do not commit here. Let the wrapper handle commit operations.
1680
1681 RETURN;
1682
1683 EXCEPTION
1684
1685 WHEN NO_DATA_FOUND THEN
1686
1687 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1688 l_stmt_id));
1689
1690 RAISE EXACT_MATCH_EXTR_ERROR;
1691
1692 WHEN OTHERS THEN
1693
1694 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1695 l_stmt_id));
1696
1697 RAISE EXACT_MATCH_EXTR_ERROR;
1698
1699 END extract_exact_matches_init;
1700
1701 /* compute_cca_conv_rates_init
1702
1703 Compute all the conversion rates for all distinct organization,
1704 transaction date pairs in the staging table. The date in the fact
1705 table is already without a timestamp i.e. trunc'ed.
1706
1707 There are two conversion rates to be computed:
1708 1. Primary global
1709 2. Secondary global (if set up)
1710
1711 The conversion rate work table was truncated during
1712 the initialization phase.
1713
1714 Get the currency conversion rates based on the data in
1715 OPI_DBI_INV_CCA_STG using the fii_currency.get_global_rate_primary
1716 API for the primary global currency and
1717 fii_currency.get_global_rate_secondary for the secondary global currency.
1718 The primary currency API:
1719 1. finds the conversion rate if one exists.
1720 2. 'USD' always has a conversion rate of 1 (since it is global currency).
1721 3. returns -1 if there is no conversion rate on that date.
1722 4. returns -2 if the currency code is not found.
1723 5. returns -3 if the transaction_date is prior to 01-JAN-1999,
1724 the functional currency code is EUR and there is no EUR to USD
1725 conversion rate defined on 01-JAN-1999.
1726
1727 The secondary currency API:
1728 1. Finds the global secondary currency rate if one exists.
1729 2. Returns a rate of 1 if the secondary currency has not been set up.
1730 3. Returns -1, -2, -3 in the same way as the primary currency code API.
1731
1732 If the global and secondary currency codes and rate types are identical,
1733 do not call the secondary currency API. Instead update the secondary
1734 rates from the primary.
1735
1736 If the secondary currency has not been set up, set the conversion rate
1737 to null.
1738
1739 If any primary conversion rates are missing, throw an exception.
1740 If any secondary currency rates are missing (after the secondary
1741 currency has been set up) throw an exception.
1742
1743 Need to commit data here due to insert+append.
1744
1745 History:
1746 Date Author Action
1747 01/15/04 Dinkar Gupta Defined procedure.
1748 05/11/04 Dinkar Gupta Specializing this procedure for initial
1749 load.
1750 08/17/04 Dinkar Gupta Added secondary currency support
1751
1752 */
1753
1754 PROCEDURE compute_cca_conv_rates_init (p_global_curr_code IN VARCHAR2,
1755 p_global_rate_type IN VARCHAR2)
1756
1757 IS
1758
1759 l_proc_name CONSTANT VARCHAR2 (60) := 'compute_cca_conv_rates_init';
1760 l_stmt_id NUMBER;
1761
1762 -- Cursor to see if any rates are missing. See below for details
1763 CURSOR invalid_rates_exist_csr IS
1764 SELECT 1
1765 FROM opi_dbi_inv_cca_conv
1766 WHERE ( nvl (conversion_rate, -999) < 0
1767 OR nvl (sec_conversion_rate, 999) < 0)
1768 AND rownum < 2;
1769
1770 invalid_rates_exist_rec invalid_rates_exist_csr%ROWTYPE;
1771
1772 -- Set up a cursor to get all the invalid rates.
1773 -- By the logic of the fii_currency.get_global_rate_primary
1774 -- and fii_currency.get_global_rate_secondary APIs, the returned value
1775 -- is -ve if no rate exists:
1776 -- -1 for dates with no rate.
1777 -- -2 for unrecognized conversion rates.
1778 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
1779 -- transaction_date is prior to 01-JAN-1999 (when the EUR
1780 -- officially went into circulation).
1781 --
1782 -- However, with the secondary currency, the null rate means it
1783 -- has not been setup and should therefore not be reported as an
1784 -- error.
1785 --
1786 -- Also, cross check with the org-date pairs in the staging table,
1787 -- in case some orgs never had a functional currency code defined.
1788 CURSOR invalid_rates_csr (p_pri_sec_curr_same NUMBER) IS
1789 SELECT /*+ parallel (compare) */
1790 DISTINCT
1791 report_order,
1792 curr_code,
1793 rate_type,
1794 approval_date,
1795 func_currency_code
1796 FROM (
1797 SELECT /*+ parallel (conv) parallel (mp) parallel (to_conv) */
1798 DISTINCT
1799 s_global_curr_code curr_code,
1800 s_global_rate_type rate_type,
1801 1 report_order, -- ordering global currency first
1802 mp.organization_code,
1803 decode (conv.conversion_rate,
1804 C_EURO_MISSING_AT_START, C_EURO_START_DATE,
1805 conv.approval_date) approval_date,
1806 conv.func_currency_code
1807 FROM opi_dbi_inv_cca_conv conv,
1808 mtl_parameters mp,
1809 (SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
1810 DISTINCT organization_id, approval_date
1811 FROM opi_dbi_inv_cca_stg) to_conv
1812 WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
1813 AND mp.organization_id = to_conv.organization_id
1814 AND conv.approval_date (+) = to_conv.approval_date
1815 AND conv.organization_id (+) = to_conv.organization_id
1816 UNION ALL
1817 SELECT /*+ parallel (conv) parallel (mp) parallel (to_conv) */
1818 DISTINCT
1819 s_secondary_curr_code curr_code,
1820 s_secondary_rate_type rate_type,
1821 decode (p_pri_sec_curr_same,
1822 1, 1,
1823 2) report_order, --ordering secondary currency next
1824 mp.organization_code,
1825 decode (conv.sec_conversion_rate,
1826 C_EURO_MISSING_AT_START, C_EURO_START_DATE,
1827 conv.approval_date) approval_date,
1828 conv.func_currency_code
1829 FROM opi_dbi_inv_cca_conv conv,
1830 mtl_parameters mp,
1831 (SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
1832 DISTINCT organization_id, approval_date
1833 FROM opi_dbi_inv_cca_stg) to_conv
1834 WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
1835 AND mp.organization_id = to_conv.organization_id
1836 AND conv.approval_date (+) = to_conv.approval_date
1837 AND conv.organization_id (+) = to_conv.organization_id)
1838 compare
1839 ORDER BY
1840 report_order ASC,
1841 approval_date,
1842 func_currency_code;
1843
1844
1845 -- Flag to ensure all rates have been found.
1846 l_all_rates_found BOOLEAN;
1847
1848 -- Boolean to check if the primary and secondary currencies are the
1849 -- same
1850 l_pri_sec_curr_same NUMBER;
1851
1852 BEGIN
1853
1854 -- initialization block
1855 l_stmt_id := 0;
1856 l_all_rates_found := true;
1857 l_pri_sec_curr_same := 0;
1858
1859 l_stmt_id := 5;
1860 -- check if the primary and secondary currencies and rate types are
1861 -- identical.
1862 IF (s_global_curr_code = nvl (s_secondary_curr_code, '---') AND
1863 s_global_rate_type = nvl (s_secondary_rate_type, '---') ) THEN
1864 l_pri_sec_curr_same := 1;
1865 END IF;
1866
1867
1868 l_stmt_id := 10;
1869 -- Get all the distinct organization and date pairs and the
1870 -- base currency codes for the orgs into the conversion rates
1871 -- work table.
1872
1873 -- Use the fii_currency.get_global_rate_primary function to get the
1874 -- conversion rate given a currency code and a date.
1875 -- The function returns:
1876 -- 1 for currency code of 'USD' which is the global currency
1877 -- -1 for dates for which there is no currency conversion rate
1878 -- -2 for unrecognized currency conversion rates
1879 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
1880 -- transaction_date is prior to 01-JAN-1999 (when the EUR
1881 -- officially went into circulation).
1882
1883 -- Use the fii_currency.get_global_rate_secondary to get the secondary
1884 -- global rate. If the secondary currency has not been set up,
1885 -- make the rate null. If the secondary currency/rate types are the
1886 -- same as the primary, don't call the API but rather use an update
1887 -- statement followed by the insert.
1888
1889 -- By selecting distinct org and currency code from the gl_set_of_books
1890 -- and hr_organization_information, take care of duplicate codes.
1891
1892 INSERT /*+ append parallel (opi_dbi_inv_cca_conv) */
1893 INTO opi_dbi_inv_cca_conv (
1894 organization_id,
1895 func_currency_code,
1896 approval_date,
1897 conversion_rate,
1898 sec_conversion_rate)
1899 SELECT /*+ parallel (to_conv) parallel (curr_codes) */
1900 to_conv.organization_id,
1901 curr_codes.currency_code,
1902 to_conv.approval_date,
1903 decode (curr_codes.currency_code,
1904 s_global_curr_code, 1,
1905 fii_currency.get_global_rate_primary (
1906 curr_codes.currency_code,
1907 to_conv.approval_date) ),
1908 decode (s_secondary_curr_code,
1909 NULL, NULL,
1910 curr_codes.currency_code, 1,
1911 decode (l_pri_sec_curr_same,
1912 1, C_PRI_SEC_CURR_SAME_MARKER,
1913 fii_currency.get_global_rate_secondary (
1914 curr_codes.currency_code,
1915 to_conv.approval_date)))
1916 FROM
1917 (SELECT /*+ parallel (opi_dbi_inv_cca_stg) */
1918 DISTINCT organization_id, approval_date
1919 FROM opi_dbi_inv_cca_stg) to_conv,
1920 (SELECT /*+ leading (hoi) full (hoi) use_hash (gsob)
1921 parallel (hoi) parallel (gsob)*/
1922 DISTINCT hoi.organization_id, gsob.currency_code
1923 FROM hr_organization_information hoi,
1924 gl_sets_of_books gsob
1925 WHERE hoi.org_information_context = 'Accounting Information'
1926 AND hoi.org_information1 = to_char(gsob.set_of_books_id))
1927 curr_codes
1928 WHERE curr_codes.organization_id = to_conv.organization_id;
1929
1930 l_stmt_id := 15;
1931 commit; -- due to insert+append
1932
1933 l_stmt_id := 20;
1934 -- if the primary and secondary currency codes are the same, then
1935 -- update the secondary with the primary
1936 IF (l_pri_sec_curr_same = 1) THEN
1937
1938 l_stmt_id := 30;
1939 UPDATE /*+ parallel (opi_dbi_inv_cca_conv) */ opi_dbi_inv_cca_conv
1940 SET sec_conversion_rate = conversion_rate;
1941
1942 -- safe to commit, as before
1943 l_stmt_id := 40;
1944 commit;
1945
1946 END IF;
1947
1948 -- Check that all rates have been found and are non-negative.
1949 -- If there is a problem, notify user.
1950 l_stmt_id := 50;
1951 OPEN invalid_rates_exist_csr;
1952 FETCH invalid_rates_exist_csr INTO invalid_rates_exist_rec;
1953 IF (invalid_rates_exist_csr%FOUND) THEN
1954
1955 -- print the header out
1956 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1957
1958 -- all rates not found
1959 l_all_rates_found := false;
1960
1961
1962 FOR invalid_rate_rec IN invalid_rates_csr (l_pri_sec_curr_same)
1963 LOOP
1964
1965 BIS_COLLECTION_UTILITIES.writeMissingRate(
1966 invalid_rate_rec.rate_type,
1967 invalid_rate_rec.func_currency_code,
1968 invalid_rate_rec.curr_code,
1969 invalid_rate_rec.approval_date);
1970
1971 END LOOP;
1972 END IF;
1973
1974 l_stmt_id := 55;
1975 CLOSE invalid_rates_exist_csr;
1976
1977 -- If all rates not found raise an exception
1978 l_stmt_id := 60;
1979 IF (l_all_rates_found = FALSE) THEN
1980 RAISE MISSING_CONV_RATES;
1981 END IF;
1982
1983 RETURN;
1984
1985 EXCEPTION
1986
1987 WHEN MISSING_CONV_RATES THEN
1988
1989 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
1990 (MISSING_CONV_RATES_MESG,
1991 l_proc_name, l_stmt_id));
1992 RAISE CONV_RATES_ERROR;
1993
1994
1995 WHEN OTHERS THEN
1996
1997 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
1998 l_stmt_id));
1999
2000 RAISE CONV_RATES_ERROR;
2001
2002
2003 END compute_cca_conv_rates_init;
2004
2005
2006 /* populate_fact_init
2007
2008 Move all the staging table data into the fact table. The granularity
2009 of the data remains the same as the staging table i.e. the
2010 cycle count entry level.
2011
2012 The fact joins to the conversion rates table to compute the values
2013 in both the functional currency and global currency.
2014
2015 Quantities in the staging table are already the primary UOM.
2016
2017 Adjustment values are computed using the item_adj_unit_cost which
2018 is derived from the MTA base_transaction_value. Thus reported
2019 adjustment values always match MTA.
2020
2021 System inventory values are computed using the item_unit_cost which
2022 is the cost of the item at the time when the entry was first entered.
2023 Thus system inventory value matches that reported in Oracle Forms.
2024 This is to work around the issues of layered costing in LIFO/FIFO
2025 orgs (bug 3471888).
2026
2027 Use the absolute value of system quantity to compute hit/miss because
2028 the system quantity can be negative. The tolerances however are
2029 defined as positive numbers. The sign and value of the adjustment
2030 are otherwise correct to allow for using absolute value of
2031 system quantity.
2032
2033 The computation of whether an entry is a hit or a miss is done in
2034 this step.
2035 1. If the entry is an exact match, it is a hit.
2036 2. If the +ve and -ve hit/miss tolerances are null, it is a hit.
2037 3. If the system_inventory_qty is 0:
2038 - if the adjustment_qty is non zero, it is a miss.
2039 - else it is a hit.
2040 4. If the adjustment is +ve:
2041 - if the +ve tolerance is null, it is a hit.
2042 - else (adjustment_qty/system_inventory_qty) <=
2043 (+ve tolerance)/100, it is a hit.
2044 This check handles the divide by 0 case.
2045 5. If the adjustment is -ve:
2046 - if the -ve tolerance is null, it is a hit.
2047 - else (adjustment_qty/system_inventory_qty) <=
2048 (-ve tolerance)/100, it is a hit. [since both the negative
2049 adjustment quantities and tolerances are reported as
2050 positive numbers.]
2051 6. Any other case is a miss.
2052
2053 Do not commit anything in this step. Let the wrapper handle that.
2054
2055 History:
2056 Date Author Action
2057 01/15/04 Dinkar Gupta Defined procedure.
2058 02/09/04 Dinkar Gupta Used absolute value of
2059 system inventory quantity
2060 to compute hit/miss.
2061 03/10/04 Dinkar Gupta Used item_cost from MCCE for system
2062 inventory value and cost from MTA
2063 for adjustment value to account for
2064 for LIFO/FIFO org layered costing.
2065 08/17/04 Dinkar Gupta Added secondary currency support
2066 */
2067
2068 PROCEDURE populate_fact_init
2069 IS
2070
2071 l_proc_name CONSTANT VARCHAR2(40) := 'populate_fact_init';
2072 l_stmt_id NUMBER;
2073
2074 BEGIN
2075
2076 -- initialization block
2077 l_stmt_id := 0;
2078
2079 l_stmt_id := 10;
2080 -- Insert data into the fact table. For explanation of logic,
2081 -- see procedure header.
2082 INSERT /*+ append parallel(opi_dbi_inv_cca_f) */
2083 INTO opi_dbi_inv_cca_f (
2084 organization_id,
2085 inventory_item_id,
2086 cycle_count_header_id,
2087 abc_class_id,
2088 subinventory_code,
2089 cycle_count_entry_id,
2090 source,
2091 approval_date,
2092 uom_code,
2093 system_inventory_qty,
2094 system_inventory_val_b,
2095 system_inventory_val_g,
2096 system_inventory_val_sg,
2097 positive_adjustment_qty,
2098 positive_adjustment_val_b,
2099 positive_adjustment_val_g,
2100 positive_adjustment_val_sg,
2101 negative_adjustment_qty,
2102 negative_adjustment_val_b,
2103 negative_adjustment_val_g,
2104 negative_adjustment_val_sg,
2105 conversion_rate,
2106 sec_conversion_rate,
2107 item_unit_cost,
2108 hit_or_miss,
2109 exact_match,
2110 last_update_date,
2111 last_updated_by,
2112 last_update_login,
2113 creation_date,
2114 created_by,
2115 request_id,
2116 program_application_id,
2117 program_id,
2118 program_update_date)
2119 SELECT /*+ parallel(stg) parallel (conv) use_hash (stg conv)*/
2120 stg.organization_id,
2121 stg.inventory_item_id,
2122 stg.cycle_count_header_id,
2123 stg.abc_class_id,
2124 stg.subinventory_code,
2125 stg.cycle_count_entry_id,
2126 stg.source,
2127 stg.approval_date,
2128 stg.uom_code,
2129 stg.system_inventory_qty,
2130 stg.system_inventory_qty * stg.item_unit_cost,
2131 stg.system_inventory_qty * stg.item_unit_cost *
2132 conv.conversion_rate,
2133 stg.system_inventory_qty * stg.item_unit_cost *
2134 conv.sec_conversion_rate,
2135 stg.positive_adjustment_qty,
2136 stg.positive_adjustment_qty * stg.item_adj_unit_cost,
2137 stg.positive_adjustment_qty * stg.item_adj_unit_cost *
2138 conv.conversion_rate,
2139 stg.positive_adjustment_qty * stg.item_adj_unit_cost *
2140 conv.sec_conversion_rate,
2141 stg.negative_adjustment_qty,
2142 stg.negative_adjustment_qty * stg.item_adj_unit_cost,
2143 stg.negative_adjustment_qty * stg.item_adj_unit_cost *
2144 conv.conversion_rate,
2145 stg.negative_adjustment_qty * stg.item_adj_unit_cost *
2146 conv.sec_conversion_rate,
2147 conv.conversion_rate,
2148 conv.sec_conversion_rate,
2149 stg.item_unit_cost,
2150 CASE
2151 WHEN stg.exact_match = C_EXACT_MATCH THEN
2152 C_HIT
2153 WHEN stg.hit_miss_pos IS NULL AND stg.hit_miss_neg IS NULL THEN
2154 C_HIT
2155 WHEN stg.hit_miss_pos IS NULL AND
2156 stg.positive_adjustment_qty > 0 THEN
2157 C_HIT
2158 WHEN stg.hit_miss_neg IS NULL AND
2159 stg.negative_adjustment_qty > 0 THEN
2160 C_HIT
2161 WHEN stg.system_inventory_qty = 0 THEN
2162 CASE
2163 WHEN stg.positive_adjustment_qty = 0 AND
2164 stg.negative_adjustment_qty = 0 THEN
2165 C_HIT
2166 ELSE
2167 C_MISS
2168 END
2169 WHEN stg.positive_adjustment_qty > 0 AND
2170 (stg.positive_adjustment_qty/
2171 abs (stg.system_inventory_qty)) <=
2172 (stg.hit_miss_pos/100) THEN
2173 C_HIT
2174 WHEN stg.negative_adjustment_qty > 0 AND
2175 (stg.negative_adjustment_qty/
2176 abs (stg.system_inventory_qty)) <=
2177 (stg.hit_miss_neg/100) THEN
2178 C_HIT
2179 ELSE
2180 C_MISS
2181 END,
2182 stg.exact_match,
2183 sysdate,
2184 s_user_id,
2185 s_login_id,
2186 sysdate,
2187 s_user_id,
2188 s_request_id,
2189 s_program_application_id,
2190 s_program_id,
2191 sysdate
2192 FROM opi_dbi_inv_cca_stg stg,
2193 opi_dbi_inv_cca_conv conv
2194 WHERE stg.organization_id = conv.organization_id
2195 AND stg.approval_date = conv.approval_date;
2196
2197 -- Do not commit here. Let the wrapper handle that.
2198
2199 RETURN;
2200
2201 EXCEPTION
2202
2203 WHEN OTHERS THEN
2204
2205 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
2206 l_stmt_id));
2207
2208 RAISE FACT_MERGE_ERROR;
2209
2210 END populate_fact_init;
2211
2212
2213
2214
2215 /**************************************************
2216 * Incremental Load Procedure Definitions
2217 **************************************************/
2218
2219 /* run_incr_load
2220
2221 Wrapper routine for the incremental load of the cycle count accuracy ETL.
2222
2223 Parameters:
2224 retcode - 0 on successful completion, -1 on error and 1 for warning.
2225 errbuf - empty on successful completion, message on error or warning
2226
2227 History:
2228 Date Author Action
2229 01/12/04 Dinkar Gupta Defined procedure.
2230 03/04/04 Dinkar Gupta Added call to OPM package.
2231 */
2232 PROCEDURE run_incr_load (errbuf OUT NOCOPY VARCHAR2,
2233 retcode OUT NOCOPY NUMBER)
2234
2235 IS
2236 l_proc_name CONSTANT VARCHAR2 (40) := 'run_incr_load';
2237 l_stmt_id NUMBER;
2238
2239 BEGIN
2240
2241 -- initialization block
2242 l_stmt_id := 0;
2243
2244 l_stmt_id := 10;
2245 -- Check for DBI global parameter setup, initialize file scope variables,
2246 -- check for bounds and truncate the needed tables.
2247 BIS_COLLECTION_UTILITIES.PUT_LINE ('Initializing cycle count incremental load ...');
2248 cca_initialize_incr ();
2249
2250 l_stmt_id := 20;
2251 -- Set up the bounds for Cycle Count accuracy in the log table.
2252 BIS_COLLECTION_UTILITIES.PUT_LINE (
2253 'Setting up bounds for discrete manufacturing organzations ...');
2254 -- return status not speified in the procedure
2255 OPI_DBI_BOUNDS_PKG.MAINTAIN_OPI_DBI_LOGS ( p_etl_type=> C_ETL_TYPE,
2256 p_load_type=> C_LOAD_INCR);
2257 l_stmt_id := 30;
2258 -- Extract the adjustment entry data from MMT/MTA using the
2259 -- bounds just set up.
2260 BIS_COLLECTION_UTILITIES.PUT_LINE (
2261 'Extracting cycle count adjustment information for discrete manufacturing orgs ...');
2262 extract_discrete_adj_incr (s_global_start_date);
2263
2264 l_stmt_id := 40;
2265 -- Due to the bulk insert, commit before
2266 -- inputting more data to the staging table.
2267 commit;
2268
2269 l_stmt_id := 45;
2270 -- Call API to load ledger data into Global temp table
2271 -- This temp table will be joined to extract process adjustments
2272 BIS_COLLECTION_UTILITIES.PUT_LINE (
2273 'Loading Ledger data into temp table ...');
2274 OPI_DBI_BOUNDS_PKG.load_opm_org_ledger_data;
2275
2276 l_stmt_id := 47;
2277 -- Committing the data. Since the temp table is made with On Commit preserve rows
2278 -- there will be no problem.
2279 commit;
2280
2281 l_stmt_id := 50;
2282 -- Extract the adjustment entry data from MMT/MTA using the
2283 -- bounds just set up.
2284 BIS_COLLECTION_UTILITIES.PUT_LINE (
2285 'Extracting cycle count adjustment information for process manufacturing orgs ...');
2286 extract_process_adj_incr (s_global_start_date);
2287
2288 l_stmt_id := 60;
2289 -- Due to the bulk insert, commit before
2290 -- inputting more data to the staging table.
2291 commit;
2292
2293 l_stmt_id := 70;
2294 -- Extract the exact matches entry data from MCCE. Collect all rows
2295 -- with last_update_date greater than the global start date.
2296 BIS_COLLECTION_UTILITIES.PUT_LINE (
2297 'Extracting cycle count exact match information for discrete and process manufacturing organizations ...');
2298 extract_exact_matches_incr ();
2299
2300 l_stmt_id := 80;
2301 -- Due to the bulk insert, commit before moving ahead.
2302 commit;
2303
2304 l_stmt_id := 90;
2305 -- Compute the conversion rates for all org/date pairs in the
2306 -- staging table. This API will now commit, due to an
2307 -- insert+append hint.
2308 BIS_COLLECTION_UTILITIES.PUT_LINE (
2309 'Computing conversion rates for all extracted cycle counting data ...');
2310 compute_cca_conv_rates_incr (s_global_curr_code, s_global_rate_type);
2311
2312 l_stmt_id := 100;
2313 -- Commit the conversion rates found before finally merging all
2314 -- data to the fact table.
2315 commit;
2316
2317 l_stmt_id := 110;
2318 -- Insert all the data into the fact table in one shot.
2319 BIS_COLLECTION_UTILITIES.PUT_LINE ('Inserting data into the fact table ...');
2320 populate_fact_incr ();
2321
2322
2323 l_stmt_id := 120;
2324 -- Update the bounds table
2325 BIS_COLLECTION_UTILITIES.PUT_LINE ('Updating run time bounds for next run ...');
2326 -- return status not speified in the procedure
2327 OPI_DBI_BOUNDS_PKG.SET_LOAD_SUCCESSFUL(C_ETL_TYPE, C_LOAD_INCR);
2328
2329
2330 l_stmt_id := 130;
2331 -- Truncate the staging table, commit the data to the fact table
2332 -- and update the bounds related to cycle counts in one database
2333 -- transaction for consistency.
2334 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
2335 'opi_dbi_inv_cca_stg');
2336
2337
2338 -- return successfully
2339 retcode := C_SUCCESS;
2340 errbuf := '';
2341
2342 BIS_COLLECTION_UTILITIES.PUT_LINE ('Cycle count data extracted into the fact table successfully.');
2343
2344 RETURN;
2345
2346 EXCEPTION
2347
2348
2349
2350 WHEN INITIALIZATION_ERROR THEN
2351
2352 rollback;
2353
2354 -- report the error
2355 retcode := C_ERROR;
2356 errbuf := 'Inventory Cycle Count Accuracy ETL incremental load terminated with errors. Please check the concurrent program log file for errors.';
2357 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2358 (INITIALIZATION_ERROR_MESG,
2359 l_proc_name, l_stmt_id));
2360 RETURN;
2361
2362
2363 WHEN BOUNDS_SETUP_ERROR THEN
2364
2365 rollback;
2366
2367 -- report the error
2368 retcode := C_ERROR;
2369 errbuf := 'Inventory Cycle Count Accuracy ETL incremental load terminated with errors. Please check the concurrent program log file for errors.';
2370 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2371 (BOUNDS_SETUP_ERROR_MESG,
2372 l_proc_name, l_stmt_id));
2373 RETURN;
2374
2375
2376 WHEN ADJUSTMENT_EXTR_ERROR THEN
2377
2378 rollback;
2379
2380 -- report the error
2381 retcode := C_ERROR;
2382 errbuf := 'Inventory Cycle Count Accuracy ETL incremental load terminated with errors. Please check the concurrent program log file for errors.';
2383 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2384 (ADJUSTMENT_EXTR_ERROR_MESG,
2385 l_proc_name, l_stmt_id));
2386 RETURN;
2387
2388
2389 WHEN EXACT_MATCH_EXTR_ERROR THEN
2390
2391 rollback;
2392
2393 -- Truncate the staging table because extraction failed midway.
2394 -- In the next run, all previously extracted and
2395 -- adjustment entry data will be re-extracted.
2396 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
2397 'opi_dbi_inv_cca_stg');
2398
2399 -- report the error
2400 retcode := C_ERROR;
2401 errbuf := 'Inventory Cycle Count Accuracy ETL incremental load terminated with errors. Please check the concurrent program log file for errors.';
2402 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2403 (EXACT_MATCH_EXTR_ERROR_MESG,
2404 l_proc_name, l_stmt_id));
2405 RETURN;
2406
2407
2408 WHEN BOUNDS_UPDATE_ERROR THEN
2409
2410 rollback;
2411
2412 -- Truncate the staging table because extraction failed midway.
2413 -- In the next run, all previously extracted exact match and
2414 -- adjustment entry data will be re-extracted.
2415 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
2416 'opi_dbi_inv_cca_stg');
2417
2418 -- report the error
2419 retcode := C_ERROR;
2420 errbuf := 'Inventory Cycle Count Accuracy ETL incremental load terminated with errors. Please check the concurrent program log file for errors.';
2421 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2422 (BOUNDS_UPDATE_ERROR_MESG,
2423 l_proc_name, l_stmt_id));
2424 RETURN;
2425
2426
2427
2428 WHEN CONV_RATES_ERROR THEN
2429
2430 rollback;
2431
2432 -- report the error
2433 retcode := C_ERROR;
2434 errbuf := 'Inventory Cycle Count Accuracy ETL incremental load terminated with errors. Please check the concurrent program log file for errors.';
2435 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2436 (CONV_RATES_ERROR_MESG,
2437 l_proc_name, l_stmt_id));
2438 RETURN;
2439
2440
2441 WHEN FACT_MERGE_ERROR THEN
2442
2443 rollback;
2444
2445 -- report the error
2446 retcode := C_ERROR;
2447 errbuf := 'Inventory Cycle Count Accuracy ETL incremental load terminated with errors. Please check the concurrent program log file for errors.';
2448 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2449 (FACT_MERGE_ERROR_MESG,
2450 l_proc_name, l_stmt_id));
2451 RETURN;
2452
2453 WHEN OPM_EXTRACTION_ERROR THEN
2454
2455 rollback;
2456
2457 -- report the error
2458 retcode := C_ERROR;
2459 errbuf := 'Inventory Cycle Count Accuracy ETL incremental load terminated with errors. Please check the concurrent program log file for errors.';
2460 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2461 (OPM_EXTRACTION_ERROR_MESG,
2462 l_proc_name, l_stmt_id));
2463 RETURN;
2464
2465 WHEN OTHERS THEN
2466
2467 rollback;
2468
2469 -- report the error
2470 retcode := C_ERROR;
2471 errbuf := 'Inventory Cycle Count Accuracy ETL incremental load terminated with errors. Please check the concurrent program log file for errors.';
2472
2473 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
2474 l_stmt_id));
2475
2476 RETURN;
2477
2478 END run_incr_load;
2479
2480
2481 /* cca_initialize_incr
2482
2483 Check that the primary currency code and global start date have been
2484 set up.
2485
2486 Get the OPI schema info.
2487
2488 Check if the log table has rows created for type = 'CCA' to determine
2489 if at least the initial load has already been run.
2490
2491 Truncate all the conversion rates table.
2492
2493 If the transaction_id bounds are not null for all orgs, then
2494 an error occurred during the extraction in the previous run. If so,
2495 truncate the staging table as the partial extraction from last time
2496 will be redone all over again in this run.
2497
2498 Note: Do not check to see if this initial load has been run previously.
2499
2500 History:
2501 Date Author Action
2502 01/19/04 Dinkar Gupta Defined procedure.
2503 04/08/04 Dinkar Gupta Changed the bounds checking logic to
2504 only look for rows with missing
2505 from_transaction_ids. In case MMT was
2506 empty in the initial load, this will
2507 still behave properly.
2508 08/17/04 Dinkar Gupta Added Secondary Currency support.
2509 */
2510
2511 PROCEDURE cca_initialize_incr
2512 IS
2513
2514 l_proc_name CONSTANT VARCHAR2 (40) := 'cca_initialize_incr';
2515 l_stmt_id NUMBER;
2516
2517
2518 BEGIN
2519
2520 -- initialization block
2521 l_stmt_id := 0;
2522
2523
2524 l_stmt_id := 10;
2525 -- Check for the primary currency code and global start date setup.
2526 -- These two parameters must be set up prior to any DBI load.
2527 IF (NOT (check_global_setup ())) THEN
2528 RAISE GLOBAL_SETUP_MISSING;
2529 END IF;
2530
2531 l_stmt_id := 20;
2532 -- Obtain the OPI schema name to allow truncation of various tables
2533 -- get session parameters
2534 IF (NOT (fnd_installation.get_app_info('OPI', s_status, s_industry,
2535 s_opi_schema))) THEN
2536 RAISE SCHEMA_INFO_NOT_FOUND;
2537 END IF;
2538
2539
2540 -- Get the global start date
2541 l_stmt_id := 30;
2542 s_global_start_date := trunc (bis_common_parameters.get_global_start_date);
2543 IF (s_global_start_date IS NULL) THEN
2544 RAISE GLOBAL_START_DATE_NULL;
2545 END IF;
2546
2547
2548 l_stmt_id := 40;
2549 -- Get the DBI global currency code
2550 s_global_curr_code := bis_common_parameters.get_currency_code;
2551
2552 l_stmt_id := 50;
2553 IF (s_global_curr_code IS NULL) THEN
2554 RAISE NO_GLOBAL_CURR_CODE;
2555 END IF;
2556
2557
2558 l_stmt_id := 60;
2559 -- Get the DBI Global rate type
2560 s_global_rate_type := bis_common_parameters.get_rate_type;
2561
2562 l_stmt_id := 70;
2563 IF (s_global_rate_type IS NULL) THEN
2564 RAISE NO_GLOBAL_RATE_TYPE;
2565 END IF;
2566
2567 l_stmt_id := 80;
2568 -- Get the DBI secondary currency code
2569 s_secondary_curr_code := bis_common_parameters.get_secondary_currency_code;
2570
2571 l_stmt_id := 90;
2572 -- Get the DBI Global rate type
2573 s_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
2574
2575 l_stmt_id := 100;
2576 IF ( (s_secondary_curr_code IS NULL AND
2577 s_secondary_rate_type IS NOT NULL)
2578 OR (s_secondary_curr_code IS NOT NULL AND
2579 s_secondary_rate_type IS NULL) ) THEN
2580 RAISE SEC_CURR_SETUP_INVALID;
2581 END IF;
2582
2583 l_stmt_id := 110;
2584 -- Since bounds are now being updated only when the merge to
2585 -- the fact is complete and are being committed using the truncate
2586 -- on the staging table, just ensure that the staging table is
2587 -- truncated.
2588 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
2589 'opi_dbi_inv_cca_stg');
2590
2591
2592 -- Truncate the following tables (in case of exceptions, nothing
2593 -- special to do here because it is a database error):
2594
2595 l_stmt_id := 120;
2596 -- Conversion rates table
2597 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
2598 'opi_dbi_inv_cca_conv');
2599
2600 -- Not using
2601 l_stmt_id := 130;
2602 -- Temp table for bounds computation
2603 --EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
2604 -- 'opi_dbi_inv_value_log_tmp');
2605
2606
2607 l_stmt_id := 140;
2608 -- User and login id's
2609 s_user_id := nvl(fnd_global.user_id, -1);
2610 s_login_id := nvl(fnd_global.login_id, -1);
2611 s_program_id := nvl(fnd_global.conc_program_id, -1);
2612 s_program_login_id := nvl(fnd_global.conc_login_id , -1);
2613 s_program_application_id := nvl(fnd_global.prog_appl_id , -1);
2614 s_request_id := nvl(fnd_global.conc_request_id, -1);
2615
2616 RETURN;
2617
2618 EXCEPTION
2619
2620 WHEN GLOBAL_SETUP_MISSING THEN
2621
2622 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (GLOBAL_SETUP_MISSING_MESG,
2623 l_proc_name, l_stmt_id));
2624 RAISE INITIALIZATION_ERROR;
2625
2626 WHEN SCHEMA_INFO_NOT_FOUND THEN
2627
2628 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2629 (SCHEMA_INFO_NOT_FOUND_MESG,
2630 l_proc_name, l_stmt_id));
2631 RAISE INITIALIZATION_ERROR;
2632
2633
2634 WHEN GLOBAL_START_DATE_NULL THEN
2635
2636 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2637 (GLOBAL_START_DATE_NULL_MESG,
2638 l_proc_name, l_stmt_id));
2639 RAISE INITIALIZATION_ERROR;
2640
2641
2642 WHEN NO_GLOBAL_CURR_CODE THEN
2643
2644 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2645 (NO_GLOBAL_CURR_CODE_MESG,
2646 l_proc_name, l_stmt_id));
2647 RAISE INITIALIZATION_ERROR;
2648
2649
2650 WHEN NO_GLOBAL_RATE_TYPE THEN
2651
2652 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2653 (NO_GLOBAL_RATE_TYPE_MESG,
2654 l_proc_name, l_stmt_id));
2655 RAISE INITIALIZATION_ERROR;
2656
2657
2658 WHEN SEC_CURR_SETUP_INVALID THEN
2659 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
2660 (SEC_CURR_SETUP_INVALID_MESG,
2661 l_proc_name, l_stmt_id));
2662
2663 RAISE INITIALIZATION_ERROR;
2664
2665 WHEN OTHERS THEN
2666
2667 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
2668 l_stmt_id));
2669
2670 RAISE INITIALIZATION_ERROR;
2671
2672
2673 END cca_initialize_incr;
2674
2675
2676 /* extract_discrete_adj_incr
2677
2678 Extract discrete adjustment entry data from MTL_CYCLE_COUNT_ENTRIES (MCCE),
2679 MMT and MTA. Adjustments can get costed well after approval and
2680 interim cost updates may cause the values recorded in MCCE to
2681 be different. Thus we need to get the cost associated to the item
2682 using the value in MTA so that the reported adjustments match
2683 the MTA reported value.
2684
2685 However, due to issues with layered costing in LIFO/FIFO orgs, the
2686 system inventory value is computed using the cost at the time when
2687 the entry was made. Thus the reported system inventory value
2688 matches the one reported in Oracle Forms.
2689
2690 We are only interested in entries completed after the global start
2691 date (i.e. entry_status_code = 5 and approval_date > global start date).
2692
2693 The only MTA rows we are interested in are those that affect the
2694 inventory account (accounting line type = 1).
2695
2696 MTA does not store rows for expense items, or items in expense
2697 subinventories. Thus the join between MMT and MTA will have to
2698 be an outer join. Rows with no matches will be assigned an
2699 item cost of 0.
2700
2701 MCCE does not store quantities in the primary uom_code. If the
2702 transaction uom is not the same as the primary uom, all
2703 quantities will have to be converted to the primary uom.
2704
2705 There is a need to check for adjustment quantity = 0 serial item counts
2706 can have count level matches, but serial number level adjustments,
2707 To handle this, make four enhancements:
2708 1. Declare exact matches if the sum of the MMT primary quantity is 0.
2709 2. Pick the MCCE item_unit_cost if the quantity is 0 (check for expense
2710 subs before that).
2711 3. Set the tolerance to 0 if there is an adjustment in another
2712 subinventory that the one counted. This will automatically
2713 make any adjustments against it misses.
2714 4. The system_inventory_qty for the other subinventory is the negative
2715 of the adjustment quantity, so that the sum of the adjustment
2716 and system quantity is always 0.
2717
2718 We will only be scanning MMT rows between the transaction ranges
2719 in the log table.
2720
2721 Also, since the fact table is at a cycle count entry level,
2722 it is impossible for the sum (mmt.primary_quantity) to be 0. Thus
2723 the item_cost formula of sum (value)/sum (quantity) is always
2724 valid and we need not worry about sum (quantity) being 0 or NULL.
2725
2726 Since the join conditions between MCCE - MMT - MTA is one to many
2727 to many, join MMT/MTA on transaction_id first.
2728
2729 Also, to handle serial item adjustments where the adjustment
2730 automatically issues an item from a sub to move it to the next,
2731 use the subinventory_code from MMT, not MCCE.
2732
2733 -------- R12 Changes
2734 1> Subinventory Transfer type of transaction (MMT.transaction_type_id = 5)
2735 is seen in Cycle Count. This case occurs when a LPN is received in
2736 say BULK, and cycle count is done in say CASE for a specific LPN.
2737 This type of transaction has 2 rows in MMT, one for issue transaction
2738 and other for receipt transaction. But the MTA has no rows for receipt
2739 transaction while it has 2 entries differing only in MTA.primary_quantity.
2740 Can be done through Warehouse mobile.
2741
2742 Currently for such transactions MMT.cycle_count_id is not populated.
2743 Bug # 4392340 has been logged for the same.
2744
2745 2> Changes to log table and columns.
2746
2747 ----------------------
2748
2749
2750 Do not commit data. Let the wrapper coordinate committing of data.
2751
2752 Paramters:
2753 IN:
2754 p_global_start_date - DBI global start date
2755
2756
2757 History:
2758 Date Author Action
2759 01/19/04 Dinkar Gupta Defined procedure.
2760 02/10/04 Dinkar Gupta Added group by transaction_id
2761 in the mmt/mta join.
2762 Used MMT subinventory_code
2763 to handle serial item issues.
2764 02/18/04 Dinkar Gupta Added condition to handle
2765 cases where MMT quantity is 0 for
2766 serial items.
2767 Also added condition to report miss
2768 for adjustments against any other
2769 sub than the one being counted.
2770 03/10/04 Dinkar Gupta Used item_cost from MCCE for system
2771 inventory value for LIFO/FIFO org
2772 issue.
2773
2774
2775 06/22/05 Vishal Gupta Refer to R12 Changes in the above
2776 header.
2777
2778
2779 */
2780 PROCEDURE extract_discrete_adj_incr (p_global_start_date IN DATE)
2781 IS
2782 l_proc_name CONSTANT VARCHAR2 (40) := 'extract_adjustments_init';
2783 l_stmt_id NUMBER;
2784
2785 BEGIN
2786
2787 -- initialization block
2788 l_stmt_id := 0;
2789
2790 l_stmt_id := 10;
2791 -- This procedure only inserts all adjustment entry data into the
2792 -- staging table. For an explanation of the logic, please refer to the
2793 -- procedure header.
2794
2795 -- Join condition justifications
2796 --
2797 -- to MCCC - on org, header and abc_class. The org is redundant,
2798 -- but this should allow use of U1 index on MCCC.
2799 --
2800 -- to MCCI - on header and item. This should allow use of U1 index
2801 -- on MCCI.
2802 --
2803 -- to MCCH - on header. This should allow use of U1 index.
2804 --
2805 -- to MCCE - on entry. This should allow use of U1 index.
2806 --
2807 -- to MSI - on org and item. This should allow use of the U1 index.
2808 --
2809 -- to SUBS - on org and subinventory code
2810 INSERT
2811 INTO opi_dbi_inv_cca_stg (
2812 organization_id,
2813 inventory_item_id,
2814 cycle_count_header_id,
2815 abc_class_id,
2816 subinventory_code,
2817 cycle_count_entry_id,
2818 source,
2819 approval_date,
2820 uom_code,
2821 system_inventory_qty,
2822 positive_adjustment_qty,
2823 negative_adjustment_qty,
2824 item_unit_cost,
2825 item_adj_unit_cost,
2826 hit_miss_pos,
2827 hit_miss_neg,
2828 exact_match)
2829 SELECT
2830 mcce.organization_id,
2831 mcce.inventory_item_id,
2832 mcce.cycle_count_header_id,
2833 to_char (mccc.abc_class_id),
2834 mmt_mta.subinventory_code,
2835 to_char (mcce.cycle_count_entry_id),
2836 C_OPI_SOURCE,
2837 trunc (mcce.approval_date),
2838 msi.primary_uom_code,
2839 decode (mcce.count_uom_current,
2840 msi.primary_uom_code,
2841 decode (mmt_mta.subinventory_code,
2842 mcce.subinventory, mcce.system_quantity_current,
2843 -1 * sum (mmt_mta.primary_quantity)),
2844 decode (mmt_mta.subinventory_code,
2845 mcce.subinventory,
2846 inv_convert.inv_um_convert
2847 (mcce.inventory_item_id, C_MMT_PRECISION,
2848 mcce.system_quantity_current,
2849 mcce.count_uom_current,
2850 msi.primary_uom_code, NULL, NULL),
2851 inv_convert.inv_um_convert
2852 (mcce.inventory_item_id, C_MMT_PRECISION,
2853 -1 * sum (mmt_mta.primary_quantity),
2854 mcce.count_uom_current,
2855 msi.primary_uom_code, NULL, NULL))),
2856 CASE WHEN sum (mmt_mta.primary_quantity) > 0 THEN
2857 sum (mmt_mta.primary_quantity)
2858 ELSE 0
2859 END,
2860 CASE WHEN sum (mmt_mta.primary_quantity) < 0 THEN
2861 -1 * sum (mmt_mta.primary_quantity)
2862 ELSE 0
2863 END,
2864 decode (subs.asset_inventory,
2865 C_EXPENSE_SUBINVENTORY, 0,
2866 mcce.item_unit_cost),
2867 decode (sum (mmt_mta.primary_quantity),
2868 0, decode (subs.asset_inventory,
2869 C_EXPENSE_SUBINVENTORY, 0,
2870 mcce.item_unit_cost),
2871 nvl (sum (mmt_mta.base_transaction_value)/
2872 sum (mmt_mta.primary_quantity), 0)),
2873 decode (mmt_mta.subinventory_code,
2874 mcce.subinventory, nvl (mccc.hit_miss_tolerance_positive,
2875 mcch.hit_miss_tolerance_positive),
2876 0),
2877 decode (mmt_mta.subinventory_code,
2878 mcce.subinventory, nvl (mccc.hit_miss_tolerance_negative,
2879 mcch.hit_miss_tolerance_negative),
2880 0),
2881 decode (sum (mmt_mta.primary_quantity),
2882 0, C_EXACT_MATCH,
2883 C_NO_MATCH)
2884 FROM mtl_cycle_count_entries mcce,
2885 mtl_cycle_count_headers mcch,
2886 mtl_cycle_count_items mcci,
2887 mtl_cycle_count_classes mccc,
2888 mtl_system_items_b msi,
2889 mtl_secondary_inventories subs,
2890 (SELECT
2891 mmt.organization_id,
2892 mmt.inventory_item_id,
2893 mmt.transaction_date,
2894 mmt.cycle_count_id,
2895 decode (mmt.transaction_type_id,
2896 5, decode(sign(nvl(mta.primary_quantity,0)),
2897 1, 0,
2898 mmt.primary_quantity),
2899 mmt.primary_quantity) primary_quantity,
2900 decode (mmt.transaction_type_id,
2901 5, decode(sign(nvl(mta.primary_quantity,0)),
2902 1, mmt.transfer_subinventory,
2903 mmt.subinventory_code),
2904 mmt.subinventory_code) subinventory_code,
2905 sum (mta.base_transaction_value) base_transaction_value
2906 FROM mtl_material_transactions mmt,
2907 mtl_transaction_accounts mta,
2908 opi_dbi_conc_prog_run_log log
2909 WHERE log.etl_type = C_ETL_TYPE
2910 AND log.driving_table_code = C_LOG_MMT_DRV_TBL
2911 AND log.load_type = C_LOAD_INCR
2912 AND mmt.organization_id = log.bound_level_entity_id
2913 AND mmt.transaction_id >= log.from_bound_id
2914 AND mmt.transaction_id < log.to_bound_id
2915 AND mmt.transaction_date > p_global_start_date
2916 AND mmt.transaction_source_type_id = 9
2917 AND mmt.transaction_type_id in ( C_MMT_CYCLE_COUNT_ADJ, 5)
2918 --AND mmt.transaction_type_id = C_MMT_CYCLE_COUNT_ADJ
2919 AND mmt.transaction_id = mta.transaction_id (+)
2920 AND nvl (mta.accounting_line_type, C_INVENTORY_ACCOUNT) =
2921 C_INVENTORY_ACCOUNT
2922 GROUP BY
2923 mmt.organization_id,
2924 mmt.inventory_item_id,
2925 mmt.transaction_date,
2926 mmt.cycle_count_id,
2927 decode (mmt.transaction_type_id,
2928 5, decode(sign(nvl(mta.primary_quantity,0)),
2929 1, 0,
2930 mmt.primary_quantity),
2931 mmt.primary_quantity),
2932 decode (mmt.transaction_type_id,
2933 5, decode(sign(nvl(mta.primary_quantity,0)),
2934 1, mmt.transfer_subinventory,
2935 mmt.subinventory_code),
2936 mmt.subinventory_code),
2937 mmt.transaction_id) mmt_mta
2938 WHERE mmt_mta.organization_id = msi.organization_id
2939 AND mmt_mta.inventory_item_id = msi.inventory_item_id
2940 AND mmt_mta.cycle_count_id = mcce.cycle_count_entry_id
2941 AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY
2942 AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
2943 AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
2944 AND mcce.inventory_item_id = mcci.inventory_item_id
2945 AND mcce.organization_id = mccc.organization_id
2946 AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
2947 AND mcci.abc_class_id = mccc.abc_class_id
2948 AND mmt_mta.organization_id = subs.organization_id
2949 AND mmt_mta.subinventory_code = subs.secondary_inventory_name
2950 GROUP BY
2951 mcce.organization_id,
2952 mcce.inventory_item_id,
2953 mcce.cycle_count_header_id,
2954 mccc.abc_class_id,
2955 mmt_mta.subinventory_code,
2956 mcce.cycle_count_entry_id,
2957 trunc (mcce.approval_date),
2958 msi.primary_uom_code,
2959 mcce.count_uom_current,
2960 mcce.system_quantity_current,
2961 subs.asset_inventory,
2962 mcce.item_unit_cost,
2963 mcce.subinventory,
2964 mccc.hit_miss_tolerance_positive,
2965 mcch.hit_miss_tolerance_positive,
2966 mcce.subinventory,
2967 mccc.hit_miss_tolerance_negative,
2968 mcch.hit_miss_tolerance_negative;
2969
2970 RETURN;
2971
2972 EXCEPTION
2973
2974 WHEN OTHERS THEN
2975
2976 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
2977 l_stmt_id));
2978
2979 RAISE ADJUSTMENT_EXTR_ERROR;
2980
2981 END extract_discrete_adj_incr;
2982
2983
2984
2985 /* extract_process_adj_incr
2986
2987 Extract process adjustment entry data from MTL_CYCLE_COUNT_ENTRIES (MCCE),
2988 MMT and GTV. The transactions in GTV can be either in Draft, New or Final Mode.
2989 We will only consider Draft and Final Mode of transactions only.
2990
2991 For initial load there will not be any separate bounds for Final and Draft
2992 transaction.
2993
2994 Subinventory Transafer type of transaction for Cycle Count is considered. There
2995 is no change from discrete.
2996
2997 1> Subinventory Transfer type of transaction (MMT.transaction_type_id = 5)
2998 is seen in Cycle Count. This case occurs when a LPN is received in
2999 say BULK, and cycle count is done in say CASE for a specific LPN.
3000 This type of transaction has 2 rows in MMT, one for issue transaction
3001 and other for receipt transaction. But the GTV has no rows for receipt
3002 transaction while it has 2 entries differing only in GTV.subinventory_code.
3003 GTV.subinventory_code will have issue subinventory_code for one of two rows in GTV
3004 pertianing to issue transaction in MMT. The other row will have
3005 MMT.transfer_subinventory in GTV.subinventory_code.
3006
3007
3008 Currently for such transactions MMT.cycle_count_id is not populated.
3009 Bug # 4392340 has been logged for the same.
3010
3011 The bounds for Process is applied same for all organization. The driving table
3012 will be GTV.
3013 To isolate cycle count transaction in GTV following is required:
3014 EVENT_CLASS_CODE = 'MISC_TXN' and 'SUBINV_XFER'
3015 EVENT_TYPE_CODE = 'CYCLE_COUNT_ADJ' and 'CYCLE_COUNT_XFER'
3016 JOURNAL_LINE_TYPE = 'INV'
3017 TRANSACTION_SOURCE = 'INVENTORY'
3018
3019 We are only interested in entries completed after the global start
3020 date (i.e. entry_status_code = 5 and approval_date > global start date).
3021
3022 Unlike the case with MTA, GTV has rows for expense items, or items in
3023 expense subinventories. Hence also no outer join is required.
3024
3025 Adjustments can get costed well after approval and interim cost updates may
3026 cause the values recorded in MCCE to be different. Thus we need to get the
3027 cost associated to the item using the value in GTV so that the reported
3028 adjustments match the GTV reported value.
3029
3030 However, due to issues with layered costing in LIFO/FIFO orgs, the
3031 system inventory value is computed using the cost at the time when
3032 the entry was made. Thus the reported system inventory value
3033 matches the one reported in Oracle Forms.
3034
3035 MCCE does not store quantities in the primary uom_code. If the
3036 transaction uom is not the same as the primary uom, all
3037 quantities will have to be converted to the primary uom.
3038
3039 Also, since the fact table is at a cycle count entry level,
3040 it is impossible for the sum (mmt.primary_quantity) to be 0. Thus
3041 the item_cost formula of sum (value)/sum (quantity) is always
3042 valid and we need not worry about sum (quantity) being 0 or NULL.
3043
3044 Since the join conditions between MCCE - MMT - GTV is one to many
3045 to many, join GTV/MMT on transaction_id first.
3046
3047 Do not commit data. Let the wrapper coordinate committing of data.
3048
3049 Paramters:
3050 IN:
3051 p_global_start_date - DBI global start date
3052
3053 History:
3054 Date Author Action
3055 06/22/05 Vishal Gupta New Procedure. To collect data for
3056 Post R12 Process cycle counting,
3057
3058
3059 */
3060 PROCEDURE extract_process_adj_incr (p_global_start_date IN DATE)
3061 IS
3062 l_proc_name CONSTANT VARCHAR2 (40) := 'extract_adjustments_init';
3063 l_stmt_id NUMBER;
3064 l_from_bound_date OPI_DBI_CONC_PROG_RUN_LOG.from_bound_date%type;
3065 l_to_bound_date OPI_DBI_CONC_PROG_RUN_LOG.to_bound_date%type;
3066
3067 BEGIN
3068
3069 -- initialization block
3070 l_stmt_id := 0;
3071
3072 SELECT from_bound_date, to_bound_date
3073 INTO l_from_bound_date, l_to_bound_date
3074 FROM OPI_DBI_CONC_PROG_RUN_LOG
3075 WHERE driving_table_code = C_LOG_GTV_DRV_TBL
3076 AND etl_type = C_ETL_TYPE
3077 AND load_type = C_LOAD_INCR;
3078
3079
3080 l_stmt_id := 10;
3081 -- This procedure only inserts all adjustment entry data into the
3082 -- staging table. For an explanation of the logic, please refer to the
3083 -- procedure header.
3084
3085 -- Join condition justifications
3086 --
3087 -- to MCCC - on org, header and abc_class. The org is redundant,
3088 -- but this should allow use of U1 index on MCCC.
3089 --
3090 -- to MCCI - on header and item. This should allow use of U1 index
3091 -- on MCCI.
3092 --
3093 -- to MCCH - on header. This should allow use of U1 index.
3094 --
3095 -- to MCCE - on entry. This should allow use of U1 index.
3096 --
3097 -- to MSI - on org and item. This should allow use of the U1 index.
3098 --
3099 -- to SUBS - on org and subinventory code
3100
3101
3102 INSERT
3103 INTO opi_dbi_inv_cca_stg (
3104 organization_id,
3105 inventory_item_id,
3106 cycle_count_header_id,
3107 abc_class_id,
3108 subinventory_code,
3109 cycle_count_entry_id,
3110 source,
3111 approval_date,
3112 uom_code,
3113 system_inventory_qty,
3114 positive_adjustment_qty,
3115 negative_adjustment_qty,
3116 item_unit_cost,
3117 item_adj_unit_cost,
3118 hit_miss_pos,
3119 hit_miss_neg,
3120 exact_match )
3121 SELECT mcce.organization_id,
3122 mcce.inventory_item_id,
3123 mcce.cycle_count_header_id,
3124 to_char (mccc.abc_class_id),
3125 mmt_gtv.subinventory_code,
3126 to_char (mcce.cycle_count_entry_id),
3127 C_OPM_SOURCE,
3128 trunc (mcce.approval_date),
3129 msi.primary_uom_code,
3130 decode (mcce.count_uom_current,
3131 msi.primary_uom_code,
3132 mcce.system_quantity_current,
3133 inv_convert.inv_um_convert ( mcce.inventory_item_id,
3134 C_MMT_PRECISION,
3135 mcce.system_quantity_current,
3136 mcce.count_uom_current,
3137 msi.primary_uom_code,
3138 NULL,
3139 NULL)) system_inventory_qty,
3140 CASE WHEN sum (mmt_gtv.primary_quantity) > 0 THEN
3141 sum (mmt_gtv.primary_quantity)
3142 ELSE 0
3143 END positive_adjustment_qty,
3144 CASE WHEN sum (mmt_gtv.primary_quantity) < 0 THEN
3145 -1 * sum (mmt_gtv.primary_quantity)
3146 ELSE 0
3147 END negative_adjustment_qty ,
3148 decode (subs.asset_inventory,
3149 C_EXPENSE_SUBINVENTORY, 0,
3150 mcce.item_unit_cost) item_unit_cost,
3151 decode (sum (mmt_gtv.primary_quantity),
3152 0, decode (subs.asset_inventory,
3153 C_EXPENSE_SUBINVENTORY, 0,
3154 mcce.item_unit_cost),
3155 nvl (sum (mmt_gtv.transaction_base_value)/
3156 sum (mmt_gtv.primary_quantity), 0)) item_adj_unit_cost,
3157 decode (mmt_gtv.subinventory_code,
3158 mcce.subinventory, nvl (mccc.hit_miss_tolerance_positive,
3159 mcch.hit_miss_tolerance_positive),
3160 0),
3161 decode (mmt_gtv.subinventory_code,
3162 mcce.subinventory, nvl (mccc.hit_miss_tolerance_negative,
3163 mcch.hit_miss_tolerance_negative),
3164 0),
3165 decode (sum (mmt_gtv.primary_quantity),
3166 0, C_EXACT_MATCH,
3167 C_NO_MATCH) exact_match
3168 FROM mtl_cycle_count_entries mcce,
3169 mtl_cycle_count_headers mcch,
3170 mtl_cycle_count_items mcci,
3171 mtl_cycle_count_classes mccc,
3172 mtl_system_items_b msi,
3173 mtl_secondary_inventories subs,
3174 -- as MMT to GTV is one to many relation grouping is done
3175 -- by transaction_id and primary_quantity level.
3176 (SELECT mmt.organization_id,
3177 mmt.transaction_id,
3178 mmt.inventory_item_id,
3179 mmt.cycle_count_id,
3180 DECODE (mmt.transaction_type_id ,
3181 5 , DECODE(GTV.subinventory_code,
3182 MMT.subinventory_code, MMT.primary_quantity,
3183 mmt.transfer_subinventory, -1* MMT.primary_quantity),mmt.primary_quantity) primary_quantity,
3184 DECODE (mmt.transaction_type_id ,
3185 5 ,GTV.subinventory_code,
3186 mmt.subinventory_code) subinventory_code,
3187 SUM (gtv.txn_base_value) transaction_base_value
3188 FROM mtl_material_transactions mmt ,
3189 (SELECT gt.*
3190 FROM gmf_transaction_valuation gt,
3191 opi_dbi_org_le_temp olt
3192 WHERE olt.organization_id = gt.organization_id
3193 AND olt.ledger_id = gt.ledger_id
3194 AND olt.legal_entity_id = gt.legal_entity_id
3195 AND olt.valuation_cost_type_id = gt.valuation_cost_type_id) gtv
3196 WHERE gtv.event_class_code in ( 'MISC_TXN','SUBINV_XFER')
3197 AND gtv.event_type_code in ('CYCLE_COUNT_ADJ','CYCLE_COUNT_XFER')
3198 AND gtv.journal_line_type = 'INV'
3199 AND gtv.transaction_source = 'INVENTORY'
3200 AND gtv.transaction_date >= l_from_bound_date
3201 -- all draft rows are considered in every incremental run
3202 AND DECODE( accounted_flag,
3203 NULL, gtv.final_posting_date,
3204 'D',s_global_start_date) < l_to_bound_date
3205 and gtv.transaction_id = mmt.transaction_id
3206 GROUP BY
3207 mmt.organization_id,
3208 mmt.inventory_item_id,
3209 mmt.cycle_count_id,
3210 DECODE (mmt.transaction_type_id ,
3211 5 , DECODE(GTV.subinventory_code,
3212 MMT.subinventory_code, MMT.primary_quantity,
3213 mmt.transfer_subinventory, -1* MMT.primary_quantity),mmt.primary_quantity),
3214 DECODE (mmt.transaction_type_id ,
3215 5 ,GTV.subinventory_code,
3216 mmt.subinventory_code) ,
3217 mmt.transaction_id) mmt_gtv
3218 WHERE mmt_gtv.organization_id = msi.organization_id
3219 AND mmt_gtv.inventory_item_id = msi.inventory_item_id
3220 AND mmt_gtv.cycle_count_id = mcce.cycle_count_entry_id
3221 AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY -- 5
3222 AND mcce.cycle_count_header_id = mcch.cycle_count_header_id
3223 AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
3224 AND mcce.inventory_item_id = mcci.inventory_item_id
3225 AND mcce.organization_id = mccc.organization_id
3226 AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
3227 AND mcci.abc_class_id = mccc.abc_class_id
3228 AND mmt_gtv.organization_id = subs.organization_id
3229 AND mmt_gtv.subinventory_code = subs.secondary_inventory_name
3230 GROUP BY
3231 mcce.organization_id,
3232 mcce.inventory_item_id,
3233 mcce.cycle_count_header_id,
3234 mccc.abc_class_id,
3235 mmt_gtv.subinventory_code,
3236 mcce.cycle_count_entry_id,
3237 trunc (mcce.approval_date),
3238 msi.primary_uom_code,
3239 mcce.count_uom_current,
3240 mcce.system_quantity_current,
3241 subs.asset_inventory,
3242 mcce.item_unit_cost,
3243 mcce.subinventory,
3244 mccc.hit_miss_tolerance_positive,
3245 mcch.hit_miss_tolerance_positive,
3246 mcce.subinventory,
3247 mccc.hit_miss_tolerance_negative,
3248 mcch.hit_miss_tolerance_negative;
3249
3250
3251
3252 RETURN;
3253
3254 EXCEPTION
3255
3256 WHEN OTHERS THEN
3257
3258 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
3259 l_stmt_id));
3260
3261 RAISE ADJUSTMENT_EXTR_ERROR;
3262
3263 END extract_process_adj_incr;
3264
3265
3266
3267
3268 /* extract_exact_matches_incr
3269
3270 Extract the exact match entry data from the MTL_CYCLE_COUNT_ENTRIES
3271 table. Exact match data is easier to handle than adjustment entries
3272 because exact matches are approved on creation and there are no
3273 other transaction tables that need to be visited.
3274
3275 We are only interested in entries completed after the global start
3276 date (i.e. entry_status_code = 5 and approval_date > global start date).
3277
3278 The adjustment quantity is always 0 and hit/miss tolerances are not
3279 needed since all entries are, by definition, hits. We still need to join
3280 to MTL_CYCLE_COUNT_CLASSES to get the count and count class information.
3281
3282 No need to join to MTL_CYCLE_COUNT_HEADERS as we don't need to
3283 get any tolerances.
3284
3285 MCCE does not store quantities in the primary uom_code. If the
3286 transaction uom is not the same as the primary uom, all
3287 quantities will have to be converted to the primary uom.
3288
3289 The date range being extracted is specified in the log table.
3290
3291 However, since we are dealing with last_update_dates, we need to
3292 be sure that the dates in the log tables have timestamps so that
3293 we do not collect partially collected days again.
3294
3295 The one special case is that we need to identify expense
3296 subinventories and set the item cost to 0 for all entries
3297 bearing those subinventory codes.
3298
3299
3300 ------------ R12 Changes
3301 Since Process exact matches will be extracted in the same insert.
3302 Date bounds speific to organization based on first uncosted transaction
3303 cannot be applied. Hence we will use date bounds as that for GTV.
3304 ie Collect all the exact matches between global start date and the initial
3305 program run date.
3306 ------------
3307
3308 Do not commit data. Let the wrapper coordinate committing of data.
3309
3310 Parameters:
3311 None.
3312
3313 History:
3314 Date Author Action
3315 01/19/04 Dinkar Gupta Defined procedure.
3316 02/05/04 Dinkar Gupta Added logic to join to the from and to
3317 transaction dates in the log table.
3318 06/22/05 Vishal Gupta Refer to R12 Changes in the above header.
3319
3320 */
3321
3322 PROCEDURE extract_exact_matches_incr
3323 IS
3324 l_proc_name CONSTANT VARCHAR2 (40) := 'extract_exact_matches_incr';
3325 l_stmt_id NUMBER;
3326 l_from_bound_date OPI_DBI_CONC_PROG_RUN_LOG.from_bound_date%type;
3327 l_to_bound_date OPI_DBI_CONC_PROG_RUN_LOG.to_bound_date%type;
3328
3329 BEGIN
3330
3331 -- initialization block
3332 l_stmt_id := 0;
3333
3334 SELECT from_bound_date, to_bound_date
3335 INTO l_from_bound_date, l_to_bound_date
3336 FROM OPI_DBI_CONC_PROG_RUN_LOG
3337 WHERE driving_table_code = C_LOG_GTV_DRV_TBL
3338 AND etl_type = C_ETL_TYPE
3339 AND load_type = C_LOAD_INCR;
3340
3341
3342 l_stmt_id := 10;
3343 -- This procedure will extract all all exact match data from MCCE
3344 -- and insert it into the staging table. For SQL logic, please refer
3345 -- to the procedure header
3346
3347 -- Join condition justifications
3348 --
3349 -- to MCCC - on org, header and abc_class. The org is redundant,
3350 -- but this should allow use of U1 index on MCCC.
3351 -- Note the explicit check for the 0 adjustment quantity. This should
3352 -- ideally not be required but we found an example (which we have not
3353 -- been able to recreate) where there was an adjustment on a serialized
3354 -- item but the adjustment date was null in MCCE and
3355 -- MTL_CC_SERIAL_NUMBERS. To filter that case, we filter explicitly on
3356 -- on the quantity. Most likely, this is an OLTP bug that will be
3357 -- fixed.
3358 --
3359 -- to MCCI - on header and item. This should allow use of U1 index
3360 -- on MCCI.
3361 --
3362 -- to MCCE - on entry. This should allow use of U1 index.
3363 -- On entry_status_code. This is the leading column of the N6 index.
3364 -- Filter on last_update_date. This should use the newly created
3365 -- N8 (?) index.
3366 --
3367 -- no join needed to log - as we can extract GTV date bounds into variables
3368 -- .
3369
3370
3371 INSERT
3372 INTO opi_dbi_inv_cca_stg (
3373 organization_id,
3374 inventory_item_id,
3375 cycle_count_header_id,
3376 abc_class_id,
3377 subinventory_code,
3378 cycle_count_entry_id,
3379 source,
3380 approval_date,
3381 uom_code,
3382 system_inventory_qty,
3383 positive_adjustment_qty,
3384 negative_adjustment_qty,
3385 item_unit_cost,
3386 item_adj_unit_cost,
3387 hit_miss_pos,
3388 hit_miss_neg,
3389 exact_match)
3390 SELECT
3391 mcce.organization_id,
3392 mcce.inventory_item_id,
3393 mcce.cycle_count_header_id,
3394 to_char (mccc.abc_class_id),
3395 mcce.subinventory,
3396 to_char (mcce.cycle_count_entry_id),
3397 decode(mp.process_enabled_flag,
3398 'Y',C_OPM_SOURCE,
3399 C_OPI_SOURCE ) source,
3400 trunc (mcce.approval_date),
3401 msi.primary_uom_code,
3402 decode (mcce.count_uom_current,
3403 msi.primary_uom_code, mcce.system_quantity_current,
3404 inv_convert.inv_um_convert
3405 (mcce.inventory_item_id, C_MMT_PRECISION,
3406 mcce.system_quantity_current, mcce.count_uom_current,
3407 msi.primary_uom_code, NULL, NULL)),
3408 0,
3409 0,
3410 decode (subs.asset_inventory,
3411 C_EXPENSE_SUBINVENTORY, 0,
3412 mcce.item_unit_cost),
3413 0,
3414 NULL,
3415 NULL,
3416 C_EXACT_MATCH
3417 FROM mtl_cycle_count_entries mcce,
3418 mtl_cycle_count_items mcci,
3419 mtl_cycle_count_classes mccc,
3420 mtl_system_items_b msi,
3421 mtl_secondary_inventories subs,
3422 mtl_parameters mp
3423 WHERE mcce.last_update_date >= l_from_bound_date
3424 AND mcce.last_update_date < l_to_bound_date
3425 AND mcce.organization_id = mp.organization_id
3426 AND mcce.entry_status_code = C_COMPLETED_CCA_ENTRY -- 5
3427 AND mcce.adjustment_date IS NULL
3428 AND mcce.adjustment_quantity = 0
3429 AND mcce.cycle_count_header_id = mcci.cycle_count_header_id
3430 AND mcce.inventory_item_id = mcci.inventory_item_id
3431 AND mcce.organization_id = mccc.organization_id
3432 AND mcce.cycle_count_header_id = mccc.cycle_count_header_id
3433 AND mcci.abc_class_id = mccc.abc_class_id
3434 AND mcce.organization_id = msi.organization_id
3435 AND mcce.inventory_item_id = msi.inventory_item_id
3436 AND mcce.organization_id = subs.organization_id
3437 AND mcce.subinventory = subs.secondary_inventory_name;
3438
3439
3440 -- Do not commit here. Let the wrapper handle commit operations.
3441
3442 RETURN;
3443
3444 EXCEPTION
3445
3446 WHEN OTHERS THEN
3447
3448 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
3449 l_stmt_id));
3450
3451 RAISE EXACT_MATCH_EXTR_ERROR;
3452
3453 END extract_exact_matches_incr;
3454
3455
3456 /* compute_cca_conv_rates_incr
3457
3458 Compute all the conversion rates for all distinct organization,
3459 transaction date pairs in the staging table. The date in the fact
3460 table is already without a timestamp i.e. trunc'ed.
3461
3462 There are two conversion rates to be computed:
3463 1. Primary global
3464 2. Secondary global (if set up)
3465
3466 The conversion rate work table was truncated during
3467 the initialization phase.
3468
3469 Get the currency conversion rates based on the data in
3470 OPI_DBI_INV_CCA_STG using the fii_currency.get_global_rate_primary
3471 API for the primary global currency and
3472 fii_currency.get_global_rate_secondary for the secondary global currency.
3473 The primary currency API:
3474 1. finds the conversion rate if one exists.
3475 2. 'USD' always has a conversion rate of 1 (since it is global currency).
3476 3. returns -1 if there is no conversion rate on that date.
3477 4. returns -2 if the currency code is not found.
3478 5. returns -3 if the transaction_date is prior to 01-JAN-1999,
3479 the functional currency code is EUR and there is no EUR to USD
3480 conversion rate defined on 01-JAN-1999.
3481
3482 The secondary currency API:
3483 1. Finds the global secondary currency rate if one exists.
3484 2. Returns a rate of 1 if the secondary currency has not been set up.
3485 3. Returns -1, -2, -3 in the same way as the primary currency code API.
3486
3487 If the global and secondary currency codes and rate types are identical,
3488 do not call the secondary currency API. Instead update the secondary
3489 rates from the primary.
3490
3491 If the secondary currency has not been set up, set the conversion rate
3492 to null.
3493
3494 If any primary conversion rates are missing, throw an exception.
3495 If any secondary currency rates are missing (after the secondary
3496 currency has been set up) throw an exception.
3497
3498 Need to commit data here due to insert+append.
3499
3500 History:
3501 Date Author Action
3502 01/15/04 Dinkar Gupta Defined procedure.
3503 05/11/04 Dinkar Gupta Specializing this procedure for incr.
3504 load.
3505 08/16/04 Dinkar Gupta Added secondary currency support.
3506 */
3507
3508 PROCEDURE compute_cca_conv_rates_incr (p_global_curr_code IN VARCHAR2,
3509 p_global_rate_type IN VARCHAR2)
3510
3511 IS
3512
3513 l_proc_name CONSTANT VARCHAR2 (60) := 'compute_cca_conv_rates_incr';
3514 l_stmt_id NUMBER;
3515
3516 -- Cursor to see if any rates are missing. See below for details
3517 CURSOR invalid_rates_exist_csr IS
3518 SELECT 1
3519 FROM opi_dbi_inv_cca_conv
3520 WHERE ( nvl (conversion_rate, -999) < 0
3521 OR nvl (sec_conversion_rate, 999) < 0)
3522 AND rownum < 2;
3523
3524 invalid_rates_exist_rec invalid_rates_exist_csr%ROWTYPE;
3525
3526 -- Set up a cursor to get all the invalid rates.
3527 -- By the logic of the fii_currency.get_global_rate_primary
3528 -- and fii_currency.get_global_rate_secondary APIs, the returned value
3529 -- is -ve if no rate exists:
3530 -- -1 for dates with no rate.
3531 -- -2 for unrecognized conversion rates.
3532 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
3533 -- transaction_date is prior to 01-JAN-1999 (when the EUR
3534 -- officially went into circulation).
3535 --
3536 -- However, with the secondary currency, the null rate means it
3537 -- has not been setup and should therefore not be reported as an
3538 -- error.
3539 --
3540 -- Also, cross check with the org-date pairs in the staging table,
3541 -- in case some orgs never had a functional currency code defined.
3542 CURSOR invalid_rates_csr (p_pri_sec_curr_same NUMBER) IS
3543 SELECT DISTINCT
3544 report_order,
3545 curr_code,
3546 rate_type,
3547 approval_date,
3548 func_currency_code
3549 FROM (
3550 SELECT DISTINCT
3551 s_global_curr_code curr_code,
3552 s_global_rate_type rate_type,
3553 1 report_order, -- ordering global currency first
3554 mp.organization_code,
3555 decode (conv.conversion_rate,
3556 C_EURO_MISSING_AT_START, C_EURO_START_DATE,
3557 conv.approval_date) approval_date,
3558 conv.func_currency_code
3559 FROM opi_dbi_inv_cca_conv conv,
3560 mtl_parameters mp,
3561 (SELECT
3562 DISTINCT organization_id, approval_date
3563 FROM opi_dbi_inv_cca_stg) to_conv
3564 WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
3565 AND mp.organization_id = to_conv.organization_id
3566 AND conv.approval_date (+) = to_conv.approval_date
3567 AND conv.organization_id (+) = to_conv.organization_id
3568 UNION ALL
3569 SELECT DISTINCT
3570 s_secondary_curr_code curr_code,
3571 s_secondary_rate_type rate_type,
3572 decode (p_pri_sec_curr_same,
3573 1, 1,
3574 2) report_order, --ordering secondary currency next
3575 mp.organization_code,
3576 decode (conv.sec_conversion_rate,
3577 C_EURO_MISSING_AT_START, C_EURO_START_DATE,
3578 conv.approval_date) approval_date,
3579 conv.func_currency_code
3580 FROM opi_dbi_inv_cca_conv conv,
3581 mtl_parameters mp,
3582 (SELECT
3583 DISTINCT organization_id, approval_date
3584 FROM opi_dbi_inv_cca_stg) to_conv
3585 WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
3586 AND mp.organization_id = to_conv.organization_id
3587 AND conv.approval_date (+) = to_conv.approval_date
3588 AND conv.organization_id (+) = to_conv.organization_id)
3589 ORDER BY
3590 report_order ASC,
3591 approval_date,
3592 func_currency_code;
3593
3594
3595 -- Boolean to ensure all rates have been found.
3596 l_all_rates_found BOOLEAN;
3597
3598 -- Boolean to check if the primary and secondary currencies are the
3599 -- same
3600 l_pri_sec_curr_same NUMBER;
3601
3602 BEGIN
3603
3604 -- initialization block
3605 l_stmt_id := 0;
3606 l_all_rates_found := true;
3607 l_pri_sec_curr_same := 0;
3608
3609 l_stmt_id := 5;
3610 -- check if the primary and secondary currencies and rate types are
3611 -- identical.
3612 IF (s_global_curr_code = nvl (s_secondary_curr_code, '---') AND
3613 s_global_rate_type = nvl (s_secondary_rate_type, '---') ) THEN
3614 l_pri_sec_curr_same := 1;
3615 END IF;
3616
3617
3618 l_stmt_id := 10;
3619 -- Get all the distinct organization and date pairs and the
3620 -- base currency codes for the orgs into the conversion rates
3621 -- work table.
3622
3623 -- Use the fii_currency.get_global_rate_primary function to get the
3624 -- conversion rate given a currency code and a date.
3625 -- The function returns:
3626 -- 1 for currency code of 'USD' which is the global currency
3627 -- -1 for dates for which there is no currency conversion rate
3628 -- -2 for unrecognized currency conversion rates
3629 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
3630 -- transaction_date is prior to 01-JAN-1999 (when the EUR
3631 -- officially went into circulation).
3632
3633 -- Use the fii_currency.get_global_rate_secondary to get the secondary
3634 -- global rate. If the secondary currency has not been set up,
3635 -- make the rate null. If the secondary currency/rate types are the
3636 -- same as the primary, don't call the API but rather use an update
3637 -- statement followed by the insert.
3638
3639 -- By selecting distinct org and currency code from the gl_set_of_books
3640 -- and hr_organization_information, take care of duplicate codes.
3641
3642 INSERT /*+ append */
3643 INTO opi_dbi_inv_cca_conv (
3644 organization_id,
3645 func_currency_code,
3646 approval_date,
3647 conversion_rate,
3648 sec_conversion_rate)
3649 SELECT
3650 to_conv.organization_id,
3651 curr_codes.currency_code,
3652 to_conv.approval_date,
3653 decode (curr_codes.currency_code,
3654 s_global_curr_code, 1,
3655 fii_currency.get_global_rate_primary (
3656 curr_codes.currency_code,
3657 to_conv.approval_date) ),
3658 decode (s_secondary_curr_code,
3659 NULL, NULL,
3660 curr_codes.currency_code, 1,
3661 decode (l_pri_sec_curr_same,
3662 1, C_PRI_SEC_CURR_SAME_MARKER,
3663 fii_currency.get_global_rate_secondary (
3664 curr_codes.currency_code,
3665 to_conv.approval_date)))
3666 FROM
3667 (SELECT
3668 DISTINCT organization_id, approval_date
3669 FROM opi_dbi_inv_cca_stg) to_conv,
3670 (SELECT
3671 DISTINCT hoi.organization_id, gsob.currency_code
3672 FROM hr_organization_information hoi,
3673 gl_sets_of_books gsob
3674 WHERE hoi.org_information_context = 'Accounting Information'
3675 AND hoi.org_information1 = to_char(gsob.set_of_books_id))
3676 curr_codes
3677 WHERE curr_codes.organization_id = to_conv.organization_id;
3678
3679 l_stmt_id := 15;
3680 commit; -- due to insert+append
3681
3682 l_stmt_id := 20;
3683 -- if the primary and secondary currency codes are the same, then
3684 -- update the secondary with the primary
3685 IF (l_pri_sec_curr_same = 1) THEN
3686
3687 l_stmt_id := 30;
3688 UPDATE /*+ parallel (opi_dbi_inv_cca_conv) */ opi_dbi_inv_cca_conv
3689 SET sec_conversion_rate = conversion_rate;
3690
3691 -- safe to commit, as before
3692 l_stmt_id := 40;
3693 commit;
3694
3695 END IF;
3696
3697 -- Check that all rates have been found and are non-negative.
3698 -- If there is a problem, notify user.
3699 l_stmt_id := 50;
3700 OPEN invalid_rates_exist_csr;
3701 FETCH invalid_rates_exist_csr INTO invalid_rates_exist_rec;
3702 IF (invalid_rates_exist_csr%FOUND) THEN
3703
3704 -- print the header out
3705 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
3706
3707 -- all rates not found
3708 l_all_rates_found := false;
3709
3710 FOR invalid_rate_rec IN invalid_rates_csr (l_pri_sec_curr_same)
3711 LOOP
3712
3713 BIS_COLLECTION_UTILITIES.writeMissingRate(
3714 invalid_rate_rec.rate_type,
3715 invalid_rate_rec.func_currency_code,
3716 invalid_rate_rec.curr_code,
3717 invalid_rate_rec.approval_date);
3718
3719 END LOOP;
3720 END IF;
3721
3722 l_stmt_id := 55;
3723 CLOSE invalid_rates_exist_csr;
3724
3725 -- If all rates not found raise an exception
3726 l_stmt_id := 60;
3727 IF (l_all_rates_found = FALSE) THEN
3728 RAISE MISSING_CONV_RATES;
3729 END IF;
3730
3731 RETURN;
3732
3733 EXCEPTION
3734
3735 WHEN MISSING_CONV_RATES THEN
3736
3737 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg
3738 (MISSING_CONV_RATES_MESG,
3739 l_proc_name, l_stmt_id));
3740 RAISE CONV_RATES_ERROR;
3741
3742
3743 WHEN OTHERS THEN
3744
3745 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
3746 l_stmt_id));
3747
3748 RAISE CONV_RATES_ERROR;
3749
3750
3751 END compute_cca_conv_rates_incr;
3752
3753
3754 /* populate_fact_incr
3755
3756 Move all the staging table data into the fact table. The granularity
3757 of the data remains the same as the staging table i.e. the
3758 cycle count entry level.
3759
3760 The fact joins to the conversion rates table to compute the values
3761 in both the functional currency and global currency.
3762
3763 Quantities in the staging table are already the primary UOM.
3764
3765 Adjustment values are computed using the item_adj_unit_cost which
3766 is derived from the MTA base_transaction_value. Thus reported
3767 adjustment values always match MTA.
3768
3769 System inventory values are computed using the item_unit_cost which
3770 is the cost of the item at the time when the entry was first entered.
3771 Thus system inventory value matches that reported in Oracle Forms.
3772 This is to work around the issues of layered costing in LIFO/FIFO
3773 orgs (bug 3471888).
3774
3775 Use the absolute value of system quantity to compute hit/miss because
3776 the system quantity can be negative. The tolerances however are
3777 defined as positive numbers. The sign and value of the adjustment
3778 are otherwise correct to allow for using absolute value of
3779 system quantity.
3780
3781 The computation of whether an entry is a hit or a miss is done in
3782 this step.
3783 1. If the entry is an exact match, it is a hit.
3784 2. If the +ve and -ve hit/miss tolerances are null, it is a hit.
3785 3. If the adjustment is +ve:
3786 - if the +ve tolerance is null, it is a hit.
3787 - else (adjustment_qty/system_inventory_qty) <=
3788 (+ve tolerance)/100, it is a hit.
3789 4. If the adjustment is +ve:
3790 - if the +ve tolerance is null, it is a hit.
3791 - else (adjustment_qty/system_inventory_qty) <=
3792 (+ve tolerance)/100, it is a hit.
3793 This check handles the divide by 0 case.
3794 5. If the adjustment is -ve:
3795 - if the -ve tolerance is null, it is a hit.
3796 - else (adjustment_qty/system_inventory_qty) <=
3797 (-ve tolerance)/100, it is a hit. [since both the negative
3798 adjustment quantities and tolerances are reported as
3799 positive numbers.]
3800 6. Any other case is a miss.
3801
3802 The only thing to be careful of in this step is the condition on the
3803 merge. In case the merge condition needs to insert, there is no
3804 complication. Insert as in the initial load.
3805 In case the merge condition needs to update, the type of entry matters.
3806 For exact matches, the entry can be simply updated.
3807 For adjustments, the entry needs to be appended because of the case
3808 where serial adjustments can create multiple MMT transactions. In that
3809 case, it is possible for one of the transactions to be uncosted. In
3810 next run, these will get picked up and need to be appended to the
3811 previous values. The hit/miss calculations will have to be done
3812 based on the new appended values too.
3813
3814 Do not commit anything in this step. Let the wrapper handle that.
3815
3816 History:
3817 Date Author Action
3818 01/19/04 Dinkar Gupta Defined procedure.
3819 02/09/04 Dinkar Gupta Used absolute value of
3820 system inventory quantity
3821 to compute hit/miss.
3822 03/10/04 Dinkar Gupta Used item_cost from MCCE for system
3823 inventory value and cost from MTA
3824 for adjustment value to account for
3825 for LIFO/FIFO org layered costing.
3826 08/17/04 Dinkar Gupta Added secondary currency support
3827 */
3828
3829 PROCEDURE populate_fact_incr
3830 IS
3831
3832 l_proc_name CONSTANT VARCHAR2(40) := 'populate_fact_incr';
3833 l_stmt_id NUMBER;
3834
3835 BEGIN
3836
3837 -- initialization block
3838 l_stmt_id := 0;
3839
3840 l_stmt_id := 10;
3841 -- Merge data into the fact table. For an explanation of the logic
3842 -- see the procedure header.
3843 MERGE INTO opi_dbi_inv_cca_f base
3844 USING
3845 (SELECT
3846 stg.organization_id,
3847 stg.inventory_item_id,
3848 stg.cycle_count_header_id,
3849 stg.abc_class_id,
3850 stg.subinventory_code,
3851 stg.cycle_count_entry_id,
3852 stg.source,
3853 stg.approval_date,
3854 stg.uom_code,
3855 stg.system_inventory_qty,
3856 (stg.system_inventory_qty * stg.item_unit_cost)
3857 system_inventory_val_b,
3858 (stg.system_inventory_qty * stg.item_unit_cost *
3859 conv.conversion_rate) system_inventory_val_g,
3860 (stg.system_inventory_qty * stg.item_unit_cost *
3861 conv.sec_conversion_rate) system_inventory_val_sg,
3862 stg.positive_adjustment_qty,
3863 (stg.positive_adjustment_qty * stg.item_adj_unit_cost)
3864 positive_adjustment_val_b,
3865 (stg.positive_adjustment_qty * stg.item_adj_unit_cost *
3866 conv.conversion_rate) positive_adjustment_val_g,
3867 (stg.positive_adjustment_qty * stg.item_adj_unit_cost *
3868 conv.sec_conversion_rate) positive_adjustment_val_sg,
3869 stg.negative_adjustment_qty,
3870 (stg.negative_adjustment_qty * stg.item_adj_unit_cost)
3871 negative_adjustment_val_b,
3872 (stg.negative_adjustment_qty * stg.item_adj_unit_cost *
3873 conv.conversion_rate) negative_adjustment_val_g,
3874 (stg.negative_adjustment_qty * stg.item_adj_unit_cost *
3875 conv.sec_conversion_rate) negative_adjustment_val_sg,
3876 conv.conversion_rate,
3877 conv.sec_conversion_rate,
3878 stg.item_unit_cost,
3879 CASE
3880 WHEN stg.exact_match = C_EXACT_MATCH THEN
3881 C_HIT
3882 WHEN stg.hit_miss_pos IS NULL AND stg.hit_miss_neg IS NULL THEN
3883 C_HIT
3884 WHEN stg.hit_miss_pos IS NULL AND
3885 stg.positive_adjustment_qty > 0 THEN
3886 C_HIT
3887 WHEN stg.hit_miss_neg IS NULL AND
3888 stg.negative_adjustment_qty > 0 THEN
3889 C_HIT
3890 WHEN stg.system_inventory_qty = 0 THEN
3891 CASE
3892 WHEN stg.positive_adjustment_qty = 0 AND
3893 stg.negative_adjustment_qty = 0 THEN
3894 C_HIT
3895 ELSE
3896 C_MISS
3897 END
3898 WHEN stg.positive_adjustment_qty > 0 AND
3899 (stg.positive_adjustment_qty/
3900 abs (stg.system_inventory_qty)) <=
3901 (stg.hit_miss_pos/100) THEN
3902 C_HIT
3903 WHEN stg.negative_adjustment_qty > 0 AND
3904 (stg.negative_adjustment_qty/
3905 abs (stg.system_inventory_qty)) <=
3906 (stg.hit_miss_neg/100) THEN
3907 C_HIT
3908 ELSE
3909 C_MISS
3910 END hit_or_miss,
3911 stg.exact_match,
3912 stg.hit_miss_pos,
3913 stg.hit_miss_neg,
3914 sysdate last_update_date,
3915 s_user_id last_updated_by,
3916 s_login_id last_update_login,
3917 sysdate creation_date,
3918 s_user_id created_by,
3919 s_request_id request_id,
3920 s_program_application_id program_application_id,
3921 s_program_id program_id,
3922 sysdate program_update_date
3923 FROM opi_dbi_inv_cca_stg stg,
3924 opi_dbi_inv_cca_conv conv
3925 WHERE stg.organization_id = conv.organization_id
3926 AND stg.approval_date = conv.approval_date) new
3927 ON (
3928 base.cycle_count_entry_id = new.cycle_count_entry_id
3929 AND base.subinventory_code = new.subinventory_code
3930 AND base.source = new.source)
3931 WHEN MATCHED THEN UPDATE
3932 SET
3933 system_inventory_qty = new.system_inventory_qty,
3934 system_inventory_val_b = new.system_inventory_val_b,
3935 system_inventory_val_g = new.system_inventory_val_g,
3936 system_inventory_val_sg = new.system_inventory_val_sg,
3937 positive_adjustment_qty =
3938 CASE WHEN new.exact_match = C_EXACT_MATCH OR
3939 new.source = C_OPM_SOURCE THEN
3940 new.positive_adjustment_qty
3941 ELSE new.positive_adjustment_qty +
3942 base.positive_adjustment_qty
3943 END,
3944 positive_adjustment_val_b =
3945 CASE WHEN new.exact_match = C_EXACT_MATCH OR
3946 new.source = C_OPM_SOURCE THEN
3947 new.positive_adjustment_val_b
3948 ELSE new.positive_adjustment_val_b +
3949 base.positive_adjustment_val_b
3950 END,
3951 positive_adjustment_val_g =
3952 CASE WHEN new.exact_match = C_EXACT_MATCH OR
3953 new.source = C_OPM_SOURCE THEN
3954 new.positive_adjustment_val_g
3955 ELSE new.positive_adjustment_val_g +
3956 base.positive_adjustment_val_g
3957 END,
3958 positive_adjustment_val_sg =
3959 CASE WHEN new.exact_match = C_EXACT_MATCH OR
3960 new.source = C_OPM_SOURCE THEN
3961 new.positive_adjustment_val_sg
3962 ELSE new.positive_adjustment_val_sg +
3963 base.positive_adjustment_val_sg
3964 END,
3965 negative_adjustment_qty =
3966 CASE WHEN new.exact_match = C_EXACT_MATCH OR
3967 new.source = C_OPM_SOURCE THEN
3968 new.negative_adjustment_qty
3969 ELSE new.negative_adjustment_qty +
3970 base.negative_adjustment_qty
3971 END,
3972 negative_adjustment_val_b =
3973 CASE WHEN new.exact_match = C_EXACT_MATCH OR
3974 new.source = C_OPM_SOURCE THEN
3975 new.negative_adjustment_val_b
3976 ELSE new.negative_adjustment_val_b +
3977 base.negative_adjustment_val_b
3978 END,
3979 negative_adjustment_val_g =
3980 CASE WHEN new.exact_match = C_EXACT_MATCH OR
3981 new.source = C_OPM_SOURCE THEN
3982 new.negative_adjustment_val_g
3983 ELSE new.negative_adjustment_val_g +
3984 base.negative_adjustment_val_g
3985 END,
3986 negative_adjustment_val_sg =
3987 CASE WHEN new.exact_match = C_EXACT_MATCH OR
3988 new.source = C_OPM_SOURCE THEN
3989 new.negative_adjustment_val_sg
3990 ELSE new.negative_adjustment_val_sg +
3991 base.negative_adjustment_val_sg
3992 END,
3993 hit_or_miss =
3994 CASE
3995 WHEN new.source = C_OPM_SOURCE THEN
3996 new.hit_or_miss
3997 WHEN new.exact_match = C_EXACT_MATCH THEN
3998 C_HIT
3999 WHEN new.hit_miss_pos IS NULL AND new.hit_miss_neg IS NULL THEN
4000 C_HIT
4001 WHEN new.source = C_OPI_SOURCE THEN
4002 CASE
4003 WHEN new.hit_miss_pos IS NULL AND
4004 base.positive_adjustment_qty +
4005 new.positive_adjustment_qty > 0 THEN
4006 C_HIT
4007 WHEN new.hit_miss_neg IS NULL AND
4008 base.negative_adjustment_qty +
4009 new.negative_adjustment_qty > 0 THEN
4010 C_HIT
4011 WHEN new.system_inventory_qty +
4012 base.system_inventory_qty = 0 THEN
4013 CASE
4014 WHEN new.positive_adjustment_qty +
4015 base.positive_adjustment_qty = 0 AND
4016 new.negative_adjustment_qty +
4017 base.negative_adjustment_qty = 0 THEN
4018 C_HIT
4019 ELSE
4020 C_MISS
4021 END
4022 WHEN new.positive_adjustment_qty > 0 AND
4023 ((new.positive_adjustment_qty +
4024 base.positive_adjustment_qty)/
4025 abs (new.system_inventory_qty)) <=
4026 (new.hit_miss_pos/100) THEN
4027 C_HIT
4028 WHEN new.negative_adjustment_qty > 0 AND
4029 ((new.negative_adjustment_qty +
4030 base.negative_adjustment_qty)/
4031 abs (new.system_inventory_qty)) <=
4032 (new.hit_miss_neg/100) THEN
4033 C_HIT
4034 ELSE
4035 C_MISS
4036 END
4037 ELSE
4038 C_MISS -- should never get here!!
4039 END,
4040 exact_match = new.exact_match,
4041 last_update_date = new.last_update_date,
4042 last_updated_by = new.last_updated_by,
4043 last_update_login = new.last_update_login,
4044 creation_date = new.creation_date,
4045 created_by = new.created_by,
4046 request_id = new.request_id,
4047 program_application_id = new.program_application_id,
4048 program_id = new.program_id,
4049 program_update_date = new.program_update_date
4050 WHEN NOT MATCHED THEN INSERT (
4051 organization_id,
4052 inventory_item_id,
4053 cycle_count_header_id,
4054 abc_class_id,
4055 subinventory_code,
4056 cycle_count_entry_id,
4057 source,
4058 approval_date,
4059 uom_code,
4060 system_inventory_qty,
4061 system_inventory_val_b,
4062 system_inventory_val_g,
4063 system_inventory_val_sg,
4064 positive_adjustment_qty,
4065 positive_adjustment_val_b,
4066 positive_adjustment_val_g,
4067 positive_adjustment_val_sg,
4068 negative_adjustment_qty,
4069 negative_adjustment_val_b,
4070 negative_adjustment_val_g,
4071 negative_adjustment_val_sg,
4072 conversion_rate,
4073 sec_conversion_rate,
4074 item_unit_cost,
4075 hit_or_miss,
4076 exact_match,
4077 last_update_date,
4078 last_updated_by,
4079 last_update_login,
4080 creation_date,
4081 created_by,
4082 request_id,
4083 program_application_id,
4084 program_id,
4085 program_update_date)
4086 VALUES (
4087 new.organization_id,
4088 new.inventory_item_id,
4089 new.cycle_count_header_id,
4090 new.abc_class_id,
4091 new.subinventory_code,
4092 new.cycle_count_entry_id,
4093 new.source,
4094 new.approval_date,
4095 new.uom_code,
4096 new.system_inventory_qty,
4097 new.system_inventory_val_b,
4098 new.system_inventory_val_g,
4099 new.system_inventory_val_sg,
4100 new.positive_adjustment_qty,
4101 new.positive_adjustment_val_b,
4102 new.positive_adjustment_val_g,
4103 new.positive_adjustment_val_sg,
4104 new.negative_adjustment_qty,
4105 new.negative_adjustment_val_b,
4106 new.negative_adjustment_val_g,
4107 new.negative_adjustment_val_sg,
4108 new.conversion_rate,
4109 new.sec_conversion_rate,
4110 new.item_unit_cost,
4111 new.hit_or_miss,
4112 new.exact_match,
4113 new.last_update_date,
4114 new.last_updated_by,
4115 new.last_update_login,
4116 new.creation_date,
4117 new.created_by,
4118 new.request_id,
4119 new.program_application_id,
4120 new.program_id,
4121 new.program_update_date);
4122
4123
4124 -- Do not commit here. Let the wrapper handle that.
4125
4126 RETURN;
4127
4128 EXCEPTION
4129
4130 WHEN OTHERS THEN
4131
4132 BIS_COLLECTION_UTILITIES.PUT_LINE (err_mesg (SQLERRM, l_proc_name,
4133 l_stmt_id));
4134
4135 RAISE FACT_MERGE_ERROR;
4136
4137 END populate_fact_incr;
4138
4139
4140 END opi_dbi_inv_cca_pkg;
[Home] [Help]