1 PACKAGE BODY opi_dbi_wip_comp_init_pkg AS
2 /*$Header: OPIDCOMPLIB.pls 120.0 2005/05/24 17:59:10 appldev noship $ */
3
4 /*++++++++++++++++++++++++++++++++++++++++*/
5 /* Function and procedure declarations in this file but not in spec*/
6 /*++++++++++++++++++++++++++++++++++++++++*/
7
8 PROCEDURE collect_init_opi_wip_comp (errbuf OUT NOCOPY VARCHAR2,
9 retcode OUT NOCOPY NUMBER,
10 p_global_start_date IN DATE);
11
12 PROCEDURE update_wip_comp_fact_init (errbuf OUT NOCOPY VARCHAR2,
13 retcode OUT NOCOPY NUMBER);
14
15 /*----------------------------------------*/
16
17 /* All DBI ETLs have a numeric ETL ID for identification. For
18 WIP Completions, the ID is 1. */
19 WIP_COMPLETION_ETL CONSTANT NUMBER := 1; -- WIP completions
20
21 /* All ETLs can have one of two sources: */
22 OPI_SOURCE CONSTANT NUMBER := 1;
23 OPM_SOURCE CONSTANT NUMBER := 2;
24
25
26 /* Non planned items have an mrp_planning_code of 6 */
27 NON_PLANNED_ITEM CONSTANT NUMBER := 6;
28
29 /* The WIP valuation account is accouting line type 7 */
30 WIP_VALUATION_ACCT CONSTANT NUMBER := 7;
31
32 /* Standard Jobs have Job type of 1 */
33 WIP_DISCRETE_STANDARD_JOB CONSTANT NUMBER := 1;
34
35 /* Following entity types need to be collected */
36 WIP_DISCRETE_JOB CONSTANT NUMBER := 1;
37 WIP_REPETITIVE_ASSEMBLY_JOB CONSTANT NUMBER := 2;
38 WIP_CLOSED_DISCRETE_JOB CONSTANT NUMBER := 3;
39 WIP_FLOW_SCHEDULE_JOB CONSTANT NUMBER := 4;
40
41 /* EURO currency became official on 1st Jan 1999 */
42 EURO_START_DATE CONSTANT DATE := to_date ('01/01/1999', 'mm/dd/yyyy');
43
44 /* GL API returns -3 if EURO rate missing on 01-JAN-1999 */
45 EURO_MISSING_AT_START CONSTANT NUMBER := -3;
46
47 /* Marker for secondary conv. rate if the primary and secondary curr codes
48 and rate types are identical. Can't be -1, -2, -3 since the FII APIs
49 return those values. */
50 C_PRI_SEC_CURR_SAME_MARKER CONSTANT NUMBER := -9999;
51
52
53 /*++++++++++++++++++++++++++++++++++++++++*/
54 /* PACKAGE LEVEL CONSTANTS */
55 /*++++++++++++++++++++++++++++++++++++++++*/
56
57 s_pkg_name CONSTANT VARCHAR2 (50) := 'opi_dbi_wip_comp_init_pkg';
58 s_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
59 s_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
60 s_SUCCESS CONSTANT NUMBER := 0; -- concurrent manager success code
61
62
63 /*++++++++++++++++++++++++++++++++++++++++*/
64 /* Package level variables for session info-
65 including schema name for truncating and
66 collecting stats */
67 /*++++++++++++++++++++++++++++++++++++++++*/
68
69 s_opi_schema VARCHAR2(30);
70 s_status VARCHAR2(30);
71 s_industry VARCHAR2(30);
72
73 /*----------------------------------------*/
74
75 /*++++++++++++++++++++++++++++++++++++++++*/
76 /* Package level variables for the logged
77 in user.
78 /*++++++++++++++++++++++++++++++++++++++++*/
79
80 s_user_id NUMBER;
81 s_login_id NUMBER;
82
83 /*----------------------------------------*/
84
85
86 /*++++++++++++++++++++++++++++++++++++++++*/
87 /* Package level exceptions defined for
88 clearer error handling. */
89 /*++++++++++++++++++++++++++++++++++++++++*/
90
91 -- exception to raise if unable to get schema information
92 schema_info_not_found EXCEPTION;
93 PRAGMA EXCEPTION_INIT (schema_info_not_found, -20000);
94
95 -- exception to raise if one or both of OPI and OPM data extraction
96 -- fails
97 data_extraction_failed EXCEPTION;
98 PRAGMA EXCEPTION_INIT (data_extraction_failed, -20001);
99
100 -- exception to throw if user needs to run common module initial
101 -- load again - e.g. if bounds have not been set up correctly.
102 run_common_module EXCEPTION;
103 PRAGMA EXCEPTION_INIT (run_common_module, -20002);
104
105 -- exception to raise if DBI global currency code not found
106 global_curr_code_not_found EXCEPTION;
107 PRAGMA EXCEPTION_INIT (global_curr_code_not_found, -20003);
108
109 -- exception to raise if missing conversion rates exist
110 missing_conversion_rates EXCEPTION;
111 PRAGMA EXCEPTION_INIT (missing_conversion_rates, -20004);
112
113 -- exception to raise unable to log successful data extraction to
114 -- log table.
115 could_not_log_success EXCEPTION;
116 PRAGMA EXCEPTION_INIT (could_not_log_success, -20005);
117
118 -- exception to raise if global parameters such as global
119 -- start date and global currency code are not available
120 global_setup_missing EXCEPTION;
121 PRAGMA EXCEPTION_INIT (global_setup_missing, -20006);
122
123 -- exception to raise if it is not time for the initial load to run
124 -- i.e. the incremental load has not been run yet
125 cannot_run_initial_load EXCEPTION;
126 PRAGMA EXCEPTION_INIT (cannot_run_initial_load, -20007);
127
128 -- exception to raise if DBI global currency code not found
129 global_rate_type_not_found EXCEPTION;
130 PRAGMA EXCEPTION_INIT (global_rate_type_not_found, -20008);
131
132 /*----------------------------------------*/
133
134 /* collect_wip_completions_init
135
136 Wrapper routine for OPI + OPM wip completion data extraction for
137 initial load.
138
139 To begin with, this routine truncates the staging table,
140 OPI_DBI_WIP_COMP_STG and the fact table OPI_DBI_WIP_COMP_F.
141
142 Both OPI and OPM ETLs can run independently upto the staging table level,
143 even if the other fails. That way, errors found in extracting OPI and OPM
144 data in the initial run can be reported simultaneously.
145
146 If either OPI or OPM fails before the staging table level, then the
147 routine ends with error.
148
149 If not, then conversion rates have to be calculated for
150 all the OPI and OPM data in the staging table.
151
152 If all conversion rates are found, data is merged to the fact table,
153 following which the staging table can be truncated.
154
155 This wrapper will only commit data implicitly through the DDL that
156 truncates the staging table. That way, it ensures that the merge is
157 committed and the staging table is emptied simultaneously.
158 This is consistent with the incremental load where we cannot
159 avoid this by truncating the staging table at the start of the function,
160 since it might have data from a previous run that failed half way. That
161 data will never be recollected and should not be thrown away.
162
163 This function does not return with an exception in case of error
164 but ends with a retcode of error. However helper functions are
165 expected to throw exceptions. We do not look at the retcode/errbuf
166 for helper functions. If a helper function fails, it is expected
167 to write a error message to the log and to throw an exception
168 back to this wrapper function.
169
170 Date Author Action
171 04/23/2003 Dinkar Gupta Wrote procedure
172
173 */
174
175 PROCEDURE collect_wip_completions_init (errbuf OUT NOCOPY VARCHAR2,
176 retcode OUT NOCOPY NUMBER)
177 IS
178
179 l_proc_name CONSTANT VARCHAR2 (60) := 'collect_wip_completions_init';
180 l_stmt_id NUMBER;
181
182 l_opi_success BOOLEAN; -- OPI extraction successful?
183 l_opm_success BOOLEAN; -- OPM extraction successful?
184
185 l_global_start_date DATE;
186
187 BEGIN
188
189 -- initialization block
190 l_opi_success := false;
191 l_opm_success := false;
192 l_global_start_date := NULL;
193 l_stmt_id := 0;
194
195 -- session parameters
196 l_stmt_id := 5;
197 s_user_id := nvl(fnd_global.user_id, -1);
198 s_login_id := nvl(fnd_global.login_id, -1);
199
200 -- get session parameters
201 l_stmt_id := 10;
202 IF (NOT (fnd_installation.get_app_info('OPI', s_status, s_industry,
203 s_opi_schema))) THEN
204 RAISE schema_info_not_found;
205 END IF;
206
207 -- check if the global set up is good
208 l_stmt_id := 11;
209 IF (NOT (check_global_setup ())) THEN
210 RAISE global_setup_missing;
211 END IF;
212
213 -- truncate the fact and staging tables.
214 l_stmt_id := 20;
215 -- also truncate the MV log explicitly because the purge MV log
216 -- directive on the fact is still causing a delete on the MV log
217 --
218 -- bug 3863905- mv log is now dropped before initial load
219 -- we shouldnt be truncating mv log anymore
220 --
221 -- EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
222 -- 'MLOG$_OPI_DBI_WIP_COMP_F');
223
224 l_stmt_id := 25;
225 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
226 'OPI_DBI_WIP_COMP_F PURGE MATERIALIZED VIEW LOG');
227
228 l_stmt_id := 30;
229 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
230 'OPI_DBI_WIP_COMP_STG');
231
232 -- get the DBI global start date
233 l_stmt_id := 40;
234 l_global_start_date := trunc (bis_common_parameters.get_global_start_date);
235
236 -- Collect the WIP completions for OPI
237 BEGIN
238
239 l_stmt_id := 50;
240 -- COMMIT DATA AS NEEDED!!! WRAPPER WILL NOT COMMIT DATA FOR
241 -- STAGING TABLE.
242 collect_init_opi_wip_comp (errbuf, retcode, l_global_start_date);
243 -- OPI collection into staging table successful
244
245 l_stmt_id := 60;
246 l_opi_success := true;
247
248 EXCEPTION
249
250 WHEN OTHERS THEN
251 rollback;
252
253 -- opi data was not found successfully
254 l_opi_success := false;
255
256 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
257 l_proc_name || ' ' ||
258 '#' || l_stmt_id ||
259 ': ' || SQLERRM);
260 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
261 l_proc_name || ' ' ||
262 '#' || l_stmt_id || ': ' ||
263 'Unable to collect OPI WIP completions data in initial load into staging table.');
264
265 END;
266
267 -- Collect the WIP completions for OPM
268 BEGIN
269
270
271 l_stmt_id := 70;
272 -- COMMIT DATA AS NEEDED!!! WRAPPER WILL NOT COMMIT DATA FOR
273 -- STAGING TABLE.
274 opi_dbi_wip_comp_opm_pkg.collect_init_opm_wip_comp (errbuf, retcode,
275 l_global_start_date);
276 -- OPM collection into staging table successful
277 l_stmt_id := 80;
278 l_opm_success := true;
279
280 EXCEPTION
281
282 WHEN OTHERS THEN
283 rollback;
284
285 -- opm data was not found successfully
286 l_opm_success := false;
287
288 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
289 l_proc_name || ' ' ||
290 '#' || l_stmt_id ||
291 ': ' || SQLERRM);
292 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
293 l_proc_name || ' ' ||
294 '#' || l_stmt_id || ': ' ||
295 'Unable to collect OPM WIP completions data in initial load into staging table.');
296
297 END;
298
299
300 -- If either OPI or OPM failed, then abort here
301 l_stmt_id := 90;
302 IF (NOT (l_opi_success AND l_opm_success)) THEN
303 RAISE data_extraction_failed;
304 END IF;
305
306 -- Compute the conversions rates for all the data in the staging table.
307 -- COMMIT DATA SO THAT THE ROLLBACK SEGMENT DOES BECOME TOO LARGE IN
308 -- THE FUNCTION. WRAPPER ONLY COMMITS FOR FACT TABLE.
309 l_stmt_id := 100;
310 compute_wip_comp_conv_rates (errbuf, retcode, s_opi_schema);
311
312 -- Merge all the data to the fact table.
313 -- DO NOT COMMIT DATA IN THIS ONE FUNCTION.
314 -- LET THE WRAPPER COORDINATE THE LAST COMMIT.
315 l_stmt_id := 110;
316 update_wip_comp_fact_init (errbuf, retcode);
317
318 -- Finally truncate the staging table if we have got this far, because
319 -- all data in the fact.
320 -- The truncate will implicitly also commit data to the fact table.
321 -- This is important because the staging table is "persistent" i.e.
322 -- failures midway do not cause data stored in the staging table to be
323 -- deleted and data once collected in staging table is not collected
324 -- again. In the initial load this does not matter, but is consistent
325 -- with the incremental load. In the incremental load, the staging
326 -- table cannot be blindly truncated before every collection, so it is
327 -- imperative the commit on the fact table and truncate on staging table
328 -- happen as one operation.
329 l_stmt_id := 120;
330 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
331 'OPI_DBI_WIP_COMP_STG');
332
333 l_stmt_id := 130;
334 BIS_COLLECTION_UTILITIES.PUT_LINE
335 ('WIP Completions Initial load terminated successfully.');
336 retcode := s_SUCCESS;
337 errbuf := '';
338 return;
339
340 EXCEPTION
341
342 WHEN schema_info_not_found THEN
343 rollback;
344
345 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
346 l_proc_name || ' ' ||
347 '#' || l_stmt_id ||
348 ': ' || SQLERRM);
349
350 retcode := s_ERROR;
351 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
352 l_stmt_id || ': ' ||
353 'WIP Completion ETL Initial Load failed to get OPI schema info.';
354 return;
355
356 WHEN global_setup_missing THEN
357 rollback;
358
359 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
360 l_proc_name || ' ' ||
361 '#' || l_stmt_id ||
362 ': ' || SQLERRM);
363
364 retcode := s_ERROR;
365 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
366 l_stmt_id || ': ' ||
367 'WIP Completions Initial Load could not find global setup of global start date and global currency code.';
368 return;
369
370 WHEN data_extraction_failed THEN
371 rollback;
372
373 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
374 l_proc_name || ' ' ||
375 '#' || l_stmt_id ||
376 ': ' || SQLERRM);
377
378 retcode := s_ERROR;
379 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
380 l_stmt_id || ': ' ||
381 'WIP Completion Initial Load data extraction failed for OPI, OPM or both. Check previous messages for errors. ';
382 return;
383
384 WHEN OTHERS THEN
385 rollback;
386
387 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
388 l_proc_name || ' ' ||
389 '#' || l_stmt_id ||
390 ': ' || SQLERRM);
391
392 retcode := s_ERROR;
393 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
394 l_stmt_id || ': ' ||
395 'WIP Completion ETLs Initial load Failed.';
396 return;
397
398
399 END collect_wip_completions_init;
400
401
402 /* collect_init_opi_wip_comp
403
404 Initial collection of WIP completion data from MMT/MTA for discrete (OPI)
405 organizations.
406
407 Collections begin from the global start for every discrete organization
408 for the transaction range recorded in the current log table,
409 OPI_DBI_RUN_LOG_CURR. The collection therefore proceeds to extract
410 every transaction per discrete org upto the first uncosted transaction.
411
412 To ensure that the bounds are good, we call the init_end_bounds_setup API
413 from the Common Module Initial load.
414
415 Data is not committed to the staging table until the bounds in the
416 current log table have been updated successfully using the Common Module
417 API etl_report_success.
418
419 WIP Completions ETL needs to extract two types of transactions from
420 MMT:
421
422 44 - WIP completion transaction increases the quantity/value
423 of WIP completions
424 17 - Assembly return transaction decreases the quantity/value of
425 of WIP completions.
426
427 The WIP valuation account has an accounting line type of 7 in MTA, but
428 the WIP valuation account decreases on WIP completions and increases
429 on assembly returns. Thus for every transaction, the corresponding
430 value we pick is -1 * (sum of all accouting line type 7) because this
431 ETL must report increases completion value on completions and decreased
432 value on returns.
433
434
435 Parameters:
436 p_global_start_date - global start date for DBI collection.
437 Expect this to be trunc'ed
438
439 Date Author Action
440 04/23/2003 Dinkar Gupta Wrote procedure
441
442 */
443
444 PROCEDURE collect_init_opi_wip_comp (errbuf OUT NOCOPY VARCHAR2,
445 retcode OUT NOCOPY NUMBER,
446 p_global_start_date IN DATE)
447 IS
448
449 l_proc_name CONSTANT VARCHAR2 (60) := 'collect_init_opi_wip_comp';
450 l_stmt_id NUMBER;
451
452 BEGIN
453
454 -- initialization block
455 l_stmt_id := 0;
456
457 -- Check if all the bounds have been properly set up
458 l_stmt_id := 10;
459 -- check if it is ok to run initial load
460 IF (NOT (opi_dbi_common_mod_init_pkg.run_initial_load
461 (WIP_COMPLETION_ETL, OPI_SOURCE))) THEN
462 RAISE cannot_run_initial_load;
463 END IF;
464
465 l_stmt_id := 15;
466 IF (NOT (opi_dbi_common_mod_init_pkg.init_end_bounds_setup
467 (WIP_COMPLETION_ETL, OPI_SOURCE))) THEN
468 RAISE run_common_module;
469 END IF;
470
471
472 -- If all bounds have been set up, extract all the data.
473 -- The data is simply inserted into the staging table, since
474 -- this is the initial load the staging table should be empty.
475 --
476 -- WIP completions transactions (MMT type 44) cause WIP completion
477 -- quantity/value to increase.
478 -- Assembly return transactions (MMT type 17) cause WIP completion
479 -- quantity/value to decrease.
480 --
481 -- MTA accounting line type 7 represents the WIP valuation account.
482 -- Since the WIP account decreases on completions and increases on
483 -- returns, we need to use -1 * value from MTA.
484 --
485 -- The join to MTA has to be an outer join since MTA does not
486 -- have any rows for expense items.
487 --
488 -- Note also that the inner query groups on mmt.transaction_id.
489 -- This is to ensure that all MTA rows for an MMT entry are
490 -- summed before quantity is summed, else we miscount quantity.
491 --
492 -- In addition, exclude all non standard discrete jobs. Discrete jobs
493 -- have an entity_type = 1 and standard jobs have a job_type = 1.
494 -- entity_type and job_type are both not null columns, so it is safe
495 -- to outer join and NVL to them.
496 --
497 -- Because of OSFM etc. we pick only the following types of jobs:
498 -- Discrete Jobs
499 -- Repetitive Assemblies
500 -- Closed discrete Jobs
501 -- Flow Schedules.
502
503 l_stmt_id := 20;
504 INSERT /*+ append parallel(opi_dbi_wip_comp_stg) */
505 INTO opi_dbi_wip_comp_stg (
506 organization_id,
507 inventory_item_id,
508 transaction_date,
509 completion_quantity,
510 completion_value_b,
511 uom_code,
512 source,
513 planned_item,
514 creation_date,
515 last_update_date,
516 created_by,
517 last_updated_by,
518 last_update_login)
519 SELECT /*+ use_hash(msi) use_hash(compl) parallel(msi) parallel(compl) */
520 compl.organization_id,
521 compl.inventory_item_id,
522 compl.trx_date,
523 sum (compl.mmt_quantity),
524 sum (compl.mta_value),
525 msi.primary_uom_code,
526 OPI_SOURCE, -- this is only for OPI orgs
527 decode (msi.mrp_planning_code,
528 NON_PLANNED_ITEM, 'N',
529 'Y'),
530 sysdate,
531 sysdate,
532 s_user_id,
533 s_user_id,
534 s_login_id
535 FROM mtl_system_items_b msi,
536 (SELECT /*+ use_hash(mmt) use_hash(mta) use_hash(we) use_hash(wdj) use_hash(log)
537 parallel(mmt) parallel(mta) parallel(we) parallel(wdj) parallel(log) */
538 mmt.organization_id,
539 mmt.inventory_item_id,
540 trunc (mmt.transaction_date) trx_date,
541 mmt.primary_quantity mmt_quantity,
542 -1 * sum (nvl (mta.base_transaction_value, 0)) mta_value
543 FROM mtl_material_transactions mmt,
544 mtl_transaction_accounts mta,
545 wip_entities we,
546 wip_discrete_jobs wdj,
547 opi_dbi_run_log_curr log
548 WHERE log.source = OPI_SOURCE
549 AND log.etl_id = WIP_COMPLETION_ETL
550 AND mmt.organization_id = log.organization_id
551 AND mmt.transaction_id >= log.start_txn_id
552 AND mmt.transaction_id < log.next_start_txn_id
553 AND mmt.transaction_date >= p_global_start_date -- (date trunc'ed)
554 AND mmt.transaction_type_id IN (44, 17)
555 AND mta.transaction_id(+) = mmt.transaction_id
556 AND nvl (mta.accounting_line_type, WIP_VALUATION_ACCT) =
557 WIP_VALUATION_ACCT
558 AND we.wip_entity_id = mmt.transaction_source_id
559 AND we.entity_type IN (WIP_DISCRETE_JOB,
560 WIP_REPETITIVE_ASSEMBLY_JOB,
561 WIP_CLOSED_DISCRETE_JOB,
562 WIP_FLOW_SCHEDULE_JOB)
563 AND wdj.wip_entity_id(+) = we.wip_entity_id
564 AND nvl (wdj.job_type, WIP_DISCRETE_STANDARD_JOB) =
565 WIP_DISCRETE_STANDARD_JOB
566 GROUP BY mmt.organization_id,
567 mmt.inventory_item_id,
568 trunc (mmt.transaction_date),
569 mmt.primary_quantity,
570 mmt.transaction_id) compl
571 WHERE msi.organization_id = compl.organization_id
572 AND msi.inventory_item_id = compl.inventory_item_id
573 GROUP BY
574 compl.organization_id,
575 compl.inventory_item_id,
576 compl.trx_date,
577 msi.primary_uom_code,
578 decode (msi.mrp_planning_code,
579 NON_PLANNED_ITEM, 'N',
580 'Y');
581
582
583
584 -- If the entire collection was successful, then try and report this
585 -- success to the OPI_DBI_RUN_LOG_CURR.
586 l_stmt_id := 30;
587 IF (NOT (opi_dbi_common_mod_incr_pkg.etl_report_success
588 (WIP_COMPLETION_ETL, OPI_SOURCE))) THEN
589
590 RAISE could_not_log_success;
591 END IF;
592
593 -- Since data pushed to staging table and success logged, commit
594 -- everything
595 l_stmt_id := 40;
596 commit;
597
598 -- all done, so return successfully.
599 l_stmt_id := 50;
600 retcode := s_SUCCESS;
601 errbuf := '';
602 return;
603
604 EXCEPTION
605
606 WHEN could_not_log_success THEN
607 rollback;
608
609 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
610 l_proc_name || ' ' ||
611 '#' || l_stmt_id || ': ' ||
612 'WIP Completion ETLs Initial load OPI data extraction success could not be logged into log table. Aborting.');
613
614 retcode := s_ERROR;
615 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
616 l_stmt_id || ': ' ||
617 'WIP Completion ETLs Initial load OPI data extraction success could not be logged into log table. Aborting.';
618 RAISE; -- propagate exception to wrapper.
619
620
621 WHEN cannot_run_initial_load THEN
622 rollback;
623 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
624 l_proc_name || ' ' ||
625 '#' || l_stmt_id || ': ' ||
626 'WIP Completion initial load concurrent program should not be running. Try running the incremental load request set if the initial request set has already been run.');
627 BIS_COLLECTION_UTILITIES.PUT_LINE ('If not, you will need to run the initial load request set.');
628
629 retcode := s_ERROR;
630 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
631 l_stmt_id || ': ' ||
632 'WIP Completion initial load concurrent program should not be running. Try running the incremental load request set if the initial request set has already been run. If not, you will need to run the initial load request set.';
633 RAISE; -- propagate exception to wrapper.
634
635 WHEN run_common_module THEN
636 rollback;
637 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
638 l_proc_name || ' ' ||
639 '#' || l_stmt_id || ': ' ||
640 'WIP Completion initial load concurrent program is running out of turn. Please submit the initial load request set for initial data collection.');
641
642 retcode := s_ERROR;
643 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
644 l_stmt_id || ': ' ||
645 'WIP Completion initial load concurrent program is running out of turn. Please submit the initial load request set for initial data collection.';
646 RAISE; -- propagate exception to wrapper.
647
648
649 WHEN OTHERS THEN
650 rollback;
651
652 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
653 l_proc_name || ' ' ||
654 '#' || l_stmt_id ||
655 ': ' || SQLERRM);
656
657 retcode := s_ERROR;
658 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
659 l_stmt_id || ': ' ||
660 'WIP Completion ETLs Initial load OPI data extraction failed.';
661 RAISE; -- propagate exception to wrapper.
662
663 END collect_init_opi_wip_comp;
664
665
666 /* update_wip_comp_fact_init
667
668 Merge data from the staging table to the fact table. For the
669 initial load, we are guaranteed that the fact table is empty,
670 so the update of the fact table is actually a simple insert.
671
672 The granularity of the staging table will item-org-transaction_date
673 and implicitly the source, since an org is never discrete and
674 process at the same time.
675
676 THIS FUNCTION WILL NOT COMMIT ANY DATA, SINCE THE WRAPPER IS
677 TAKING RESPONSIBILITY FOR COMMITTING DATA TO THE FACT TABLE.
678
679 Date Author Action
680 04/23/2003 Dinkar Gupta Wrote procedure
681
682 */
683
684 PROCEDURE update_wip_comp_fact_init (errbuf OUT NOCOPY VARCHAR2,
685 retcode OUT NOCOPY NUMBER)
686 IS
687
688 l_proc_name CONSTANT VARCHAR2 (60) := 'update_wip_comp_fact_init';
689 l_stmt_id NUMBER;
690
691
692 BEGIN
693
694 -- initialization block
695 l_stmt_id := 0;
696
697 -- Just insert everything in staging table into the fact table,
698 -- grouping by item-org-transaction_date and source.
699 --
700 -- It is assumed that the date stored in the fact table has
701 -- already been truncated at the start of the initial load.
702 -- Depending on how the staging table extraction SQL has been written
703 -- it might not be necessary to perform the group by operation here (at
704 -- least not for initial load) but it is being done for consistency.
705 l_stmt_id := 10;
706 INSERT /*+ append parallel(opi_dbi_wip_comp_f) */
707 INTO opi_dbi_wip_comp_f (
708 organization_id,
709 inventory_item_id,
710 transaction_date,
711 completion_quantity,
712 completion_value_b,
713 uom_code,
714 conversion_rate,
715 sec_conversion_rate,
716 source,
717 planned_item,
718 creation_date,
719 last_update_date,
720 created_by,
721 last_updated_by,
722 last_update_login)
723 SELECT /*+ use_hash(stg conv) parallel(stg) parallel(conv) */
724 stg.organization_id,
725 stg.inventory_item_id,
726 stg.transaction_date,
727 sum (stg.completion_quantity),
728 sum (stg.completion_value_b),
729 stg.uom_code,
730 conv.conversion_rate,
731 conv.sec_conversion_rate,
732 stg.source,
733 stg.planned_item,
734 sysdate,
735 sysdate,
736 s_user_id,
737 s_user_id,
738 s_login_id
739 FROM opi_dbi_wip_comp_stg stg,
740 opi_dbi_wip_comp_conv_rates conv
741 WHERE stg.organization_id = conv.organization_id
742 AND stg.transaction_date = conv.transaction_date
743 GROUP BY stg.organization_id,
744 stg.inventory_item_id,
745 stg.transaction_date,
746 stg.uom_code,
747 conv.conversion_rate,
748 conv.sec_conversion_rate,
749 stg.source,
750 stg.planned_item;
751
752 -- merge successful, so return
753 retcode := s_SUCCESS;
754 errbuf := '';
755 return;
756
757 EXCEPTION
758
759 WHEN OTHERS THEN
760 rollback;
761
762 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
763 l_proc_name || ' ' ||
764 '#' || l_stmt_id ||
765 ': ' || SQLERRM);
766
767 retcode := s_ERROR;
768 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
769 l_stmt_id || ': ' ||
770 'WIP Completion ETLs Initial merge to fact table failed.';
771 RAISE; -- propagate exception to wrapper.
772
773 END update_wip_comp_fact_init;
774
775
776 /*++++++++++++++++++++++++++++++++++++++++*/
777 /* Utilities for initial and incremental load
778 /*++++++++++++++++++++++++++++++++++++++++*/
779
780 /* compute_wip_comp_conv_rates
781
782 Compute all the conversion rates for all distinct organization,
783 transaction date pairs in the staging table. The date in the fact
784 table is already without a timestamp i.e. trunc'ed.
785
786 There are two conversion rates to be computed:
787 1. Primary global
788 2. Secondary global (if set up)
789
790 The conversion rate work table was truncated during
791 the initialization phase.
792
793 Get the currency conversion rates based on the data in
794 OPI_DBI_WIP_COMP_STG using the fii_currency.get_global_rate_primary
795 API for the primary global currency and
796 fii_currency.get_global_rate_secondary for the secondary global currency.
797 The primary currency API:
798 1. finds the conversion rate if one exists.
799 2. returns -1 if there is no conversion rate on that date.
800 3. returns -2 if the currency code is not found.
801 4. returns -3 if the transaction_date is prior to 01-JAN-1999,
802 the functional currency code is EUR and there is no EUR to USD
803 conversion rate defined on 01-JAN-1999.
804
805 The secondary currency API:
806 1. Finds the global secondary currency rate if one exists.
807 2. Returns a rate of 1 if the secondary currency has not been set up.
808 3. Returns -1, -2, -3 in the same way as the primary currency code API.
809
810 If the global and secondary currency codes and rate types are identical,
811 do not call the secondary currency API. Instead update the secondary
812 rates from the primary.
813
814 If the secondary currency has not been set up, set the conversion rate
815 to null.
816
817 If any primary conversion rates are missing, throw an exception.
818 If any secondary currency rates are missing (after the secondary
819 currency has been set up) throw an exception.
820
821 Need to commit data here due to insert+append.
822
823 Date Author Action
824 04/23/2003 Dinkar Gupta Wrote procedure
825 04/28/2003 Dinkar Gupta In the check for missing currency
826 rates, actually cross checking with
827 all org-date pairs in the staging
828 table, in case the functional
829 currency code is missing for some org.
830 06/03/2003 Dinkar Gupta Added OPI schema as parameter
831 08/25/2004 Dinkar Gupta Modified to provide secondary
832 currency support.
833 */
834
835 PROCEDURE compute_wip_comp_conv_rates (errbuf OUT NOCOPY VARCHAR2,
836 retcode OUT NOCOPY NUMBER,
837 p_opi_schema IN VARCHAR2)
838 IS
839
840 l_proc_name CONSTANT VARCHAR2 (60) := 'compute_wip_comp_conv_rates';
841 l_stmt_id NUMBER;
842
843 l_global_currency_code VARCHAR2 (10);
844 l_global_rate_type VARCHAR2(15);
845
846 l_secondary_currency_code VARCHAR2 (10);
847 l_secondary_rate_type VARCHAR2(15);
848
849 l_all_rates_found BOOLEAN;
850
851 -- Flag to check if the primary and secondary currencies are the
852 -- same
853 l_pri_sec_curr_same NUMBER;
854
855 -- Cursor to see if any rates are missing. See below for details
856 CURSOR invalid_rates_exist_csr IS
857 SELECT 1
858 FROM opi_dbi_wip_comp_conv_rates
859 WHERE ( nvl (conversion_rate, -999) < 0
860 OR nvl (sec_conversion_rate, 999) < 0)
861 AND rownum < 2;
862
863 invalid_rates_exist_rec invalid_rates_exist_csr%ROWTYPE;
864
865
866 -- Set up a cursor to get all the invalid rates.
867 -- By the logic of the fii_currency.get_global_rate_primary
868 -- and fii_currency.get_global_rate_secondary APIs, the returned value
869 -- is -ve if no rate exists:
870 -- -1 for dates with no rate.
871 -- -2 for unrecognized conversion rates.
872 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
873 -- transaction_date is prior to 01-JAN-1999 (when the EUR
874 -- officially went into circulation).
875 --
876 -- However, with the secondary currency, the null rate means it
877 -- has not been setup and should therefore not be reported as an
878 -- error.
879 --
880 -- Also, cross check with the org-date pairs in the staging table,
881 -- in case some orgs never had a functional currency code defined.
882 CURSOR get_missing_rates_c (p_pri_sec_curr_same NUMBER,
883 p_global_currency_code VARCHAR2,
884 p_global_rate_type VARCHAR2,
885 p_secondary_currency_code VARCHAR2,
886 p_secondary_rate_type VARCHAR2) IS
887 SELECT DISTINCT
888 report_order,
889 curr_code,
890 rate_type,
891 transaction_date,
892 func_currency_code
893 FROM (
894 SELECT DISTINCT
895 p_global_currency_code curr_code,
896 p_global_rate_type rate_type,
897 1 report_order, -- ordering global currency first
898 mp.organization_code,
899 decode (conv.conversion_rate,
900 EURO_MISSING_AT_START, EURO_START_DATE,
901 conv.transaction_date) transaction_date,
902 conv.base_currency_code func_currency_code
903 FROM opi_dbi_wip_comp_conv_rates conv,
904 mtl_parameters mp,
905 (SELECT /*+ index_ffs(opi_dbi_wip_comp_stg) */
906 DISTINCT organization_id, transaction_date
907 FROM opi_dbi_wip_comp_stg) to_conv
908 WHERE nvl (conv.conversion_rate, -999) < 0 -- null is not fine
909 AND mp.organization_id = to_conv.organization_id
910 AND conv.transaction_date (+) = to_conv.transaction_date
911 AND conv.organization_id (+) = to_conv.organization_id
912 UNION ALL
913 SELECT DISTINCT
914 p_secondary_currency_code curr_code,
915 p_secondary_rate_type rate_type,
916 decode (p_pri_sec_curr_same,
917 1, 1,
918 2) report_order, --ordering secondary currency next
919 mp.organization_code,
920 decode (conv.sec_conversion_rate,
921 EURO_MISSING_AT_START, EURO_START_DATE,
922 conv.transaction_date) transaction_date_date,
923 conv.base_currency_code func_currency_code
924 FROM opi_dbi_wip_comp_conv_rates conv,
925 mtl_parameters mp,
926 (SELECT /*+ index_ffs(opi_dbi_wip_comp_stg) */
927 DISTINCT organization_id, transaction_date
928 FROM opi_dbi_wip_comp_stg) to_conv
929 WHERE nvl (conv.sec_conversion_rate, 999) < 0 -- null is fine
930 AND mp.organization_id = to_conv.organization_id
931 AND conv.transaction_date (+) = to_conv.transaction_date
932 AND conv.organization_id (+) = to_conv.organization_id)
933 ORDER BY
934 report_order ASC,
935 transaction_date,
936 func_currency_code;
937
938 BEGIN
939
940 -- initialization block
941 l_stmt_id := 0;
942 l_global_currency_code := NULL;
943 l_global_rate_type := NULL;
944 l_secondary_currency_code := NULL;
945 l_secondary_rate_type := NULL;
946 l_all_rates_found := true;
947 l_pri_sec_curr_same := 0;
948
949
950 -- Truncate the conversion rates work table
951 l_stmt_id := 10;
952 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || p_opi_schema || '.' ||
953 'OPI_DBI_WIP_COMP_CONV_RATES');
954
955 -- It is assumed that the setup of the global currency data has been
956 -- validated at the start of the program by a call to the
957 -- check_global_setup procedure.
958 -- Global currency codes -- already checked if primary is set up
959 l_stmt_id := 20;
960 l_global_currency_code := bis_common_parameters.get_currency_code;
961 l_secondary_currency_code :=
962 bis_common_parameters.get_secondary_currency_code;
963
964 -- Global rate types -- already checked if primary is set up
965 l_stmt_id := 25;
966 l_global_rate_type := bis_common_parameters.get_rate_type;
967 l_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
968
969 l_stmt_id := 27;
970 -- check if the primary and secondary currencies and rate types are
971 -- identical.
972 IF (l_global_currency_code = nvl (l_secondary_currency_code, '---') AND
973 l_global_rate_type = nvl (l_secondary_rate_type, '---') ) THEN
974 l_pri_sec_curr_same := 1;
975 END IF;
976
977 -- By selecting distinct org and currency code from the gl_set_of_books
978 -- and hr_organization_information, take care of duplicate codes.
979 -- Use the fii_currency.get_global_rate_primary function to get the
980 -- conversion rate given a currency code and a date.
981 -- The function returns:
982 -- rate if found
983 -- -1 for dates for which there is no currency conversion rate
984 -- -2 for unrecognized currency conversion rates
985 -- -3 for missing EUR to USD rates on 01-JAN-1999 when the
986 -- transaction_date is prior to 01-JAN-1999 (when the EUR
987 -- officially went into circulation).
988
989 -- Use the fii_currency.get_global_rate_secondary to get the secondary
990 -- global rate. If the secondary currency has not been set up,
991 -- make the rate null. If the secondary currency/rate types are the
992 -- same as the primary, don't call the API but rather use an update
993 -- statement followed by the insert.
994
995 -- By selecting distinct org and currency code from the gl_set_of_books
996 -- and hr_organization_information, take care of duplicate codes.
997
998 INSERT /*+ append */
999 INTO opi_dbi_wip_comp_conv_rates rates (
1000 organization_id,
1001 base_currency_code,
1002 transaction_date,
1003 conversion_rate,
1004 sec_conversion_rate,
1005 last_update_date,
1006 creation_date,
1007 created_by,
1008 last_updated_by,
1009 last_update_login)
1010 SELECT
1011 to_conv.organization_id,
1012 curr_codes.currency_code,
1013 to_conv.transaction_date,
1014 decode (curr_codes.currency_code,
1015 l_global_currency_code, 1,
1016 fii_currency.get_global_rate_primary (
1017 curr_codes.currency_code,
1018 to_conv.transaction_date) ),
1019 decode (l_secondary_currency_code,
1020 NULL, NULL,
1021 curr_codes.currency_code, 1,
1022 decode (l_pri_sec_curr_same,
1023 1, C_PRI_SEC_CURR_SAME_MARKER,
1024 fii_currency.get_global_rate_secondary (
1025 curr_codes.currency_code,
1026 to_conv.transaction_date))),
1027 sysdate,
1028 sysdate,
1029 s_user_id,
1030 s_user_id,
1031 s_login_id
1032 FROM
1033 (SELECT /*+ index_ffs(opi_dbi_wip_comp_stg) */
1034 DISTINCT organization_id, transaction_date
1035 FROM opi_dbi_wip_comp_stg) to_conv,
1036 (SELECT
1037 DISTINCT hoi.organization_id, gsob.currency_code
1038 FROM hr_organization_information hoi,
1039 gl_sets_of_books gsob
1040 WHERE hoi.org_information_context = 'Accounting Information'
1041 AND hoi.org_information1 = to_char(gsob.set_of_books_id))
1042 curr_codes
1043 WHERE curr_codes.organization_id = to_conv.organization_id;
1044
1045
1046 --Introduced commit because of append parallel in the insert stmt above.
1047 commit;
1048
1049 l_stmt_id := 40;
1050 -- if the primary and secondary currency codes are the same, then
1051 -- update the secondary with the primary
1052 IF (l_pri_sec_curr_same = 1) THEN
1053
1054 UPDATE /*+ parallel (opi_dbi_wip_comp_conv_rates) */
1055 opi_dbi_wip_comp_conv_rates
1056 SET sec_conversion_rate = conversion_rate;
1057
1058 -- safe to commit, as before
1059 commit;
1060
1061 END IF;
1062
1063
1064 -- report missing rate
1065 l_stmt_id := 50;
1066
1067 OPEN invalid_rates_exist_csr;
1068 FETCH invalid_rates_exist_csr INTO invalid_rates_exist_rec;
1069 IF (invalid_rates_exist_csr%FOUND) THEN
1070
1071 -- there are missing rates - prepare to report them.
1072 l_all_rates_found := false;
1073 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
1074
1075 l_stmt_id := 60;
1076 FOR get_missing_rates_rec IN get_missing_rates_c
1077 (l_pri_sec_curr_same,
1078 l_global_currency_code,
1079 l_global_rate_type,
1080 l_secondary_currency_code,
1081 l_secondary_rate_type)
1082 LOOP
1083
1084 BIS_COLLECTION_UTILITIES.writemissingrate (
1085 get_missing_rates_rec.rate_type,
1086 get_missing_rates_rec.func_currency_code,
1087 get_missing_rates_rec.curr_code,
1088 get_missing_rates_rec.transaction_date);
1089
1090 END LOOP;
1091
1092 END IF;
1093 CLOSE invalid_rates_exist_csr;
1094
1095 -- If all rates not found, then raise an exception
1096 l_stmt_id := 50;
1097 IF (l_all_rates_found = false) THEN
1098 RAISE missing_conversion_rates;
1099 END IF;
1100
1101 l_stmt_id := 60;
1102 retcode := s_SUCCESS;
1103 errbuf := '';
1104 return;
1105
1106 EXCEPTION
1107
1108 WHEN global_curr_code_not_found THEN
1109 rollback;
1110
1111 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1112 l_proc_name || ' ' ||
1113 '#' || l_stmt_id || ': ' ||
1114 'WIP Completion ETLs global currency code not found.');
1115
1116 retcode := s_ERROR;
1117 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
1118 l_stmt_id || ': ' ||
1119 'WIP Completion ETLs global currency code not found.';
1120 RAISE; -- propagate exception to wrapper.
1121
1122 WHEN global_rate_type_not_found THEN
1123 rollback;
1124
1125 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1126 l_proc_name || ' ' ||
1127 '#' || l_stmt_id || ': ' ||
1128 'WIP Completions ETL Initial load global rate type not found.');
1129
1130 retcode := s_ERROR;
1131 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
1132 l_stmt_id || ': ' ||
1133 'WIP Completions ETL Initial load global rate type not found.';
1134 RAISE; -- propagate exception to wrapper.
1135
1136 WHEN missing_conversion_rates THEN
1137 rollback;
1138
1139 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1140 l_proc_name || ' ' ||
1141 '#' || l_stmt_id || ': ' ||
1142 'WIP Completion ETLs Initial Load found missing currency rates.');
1143
1144 retcode := s_ERROR;
1145 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
1146 l_stmt_id || ': ' ||
1147 'WIP Completion ETLs found missing currency rates.';
1148
1149 RAISE; -- propagate exception to wrapper.
1150
1151 WHEN OTHERS THEN
1152 rollback;
1153
1154 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1155 l_proc_name || ' ' ||
1156 '#' || l_stmt_id ||
1157 ': ' || SQLERRM);
1158
1159 retcode := s_ERROR;
1160 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
1161 l_stmt_id || ': ' ||
1162 'WIP Completion ETLs conversion rate computation failed.';
1163 RAISE; -- propagate exception to wrapper.
1164
1165 END compute_wip_comp_conv_rates;
1166
1167 /* check_global_setup
1168
1169 Checks to see if basic global parameters are set up.
1170 Currently these include the:
1171 1. Global start date
1172 2. Global currency code
1173
1174 Parameters: None
1175
1176 Date Author Action
1177 04/23/03 Dinkar Gupta Wrote Function
1178 08/24/04 Dinkar Gupta Added checking for primary rate type
1179 and secondary currency setup.
1180 */
1181 FUNCTION check_global_setup
1182 RETURN BOOLEAN
1183 IS
1184 l_proc_name CONSTANT VARCHAR2 (60) := 'check_global_setup';
1185 l_stmt_id NUMBER;
1186
1187 l_setup_good BOOLEAN := false;
1188
1189 l_list dbms_sql.varchar2_table;
1190
1191 l_secondary_currency_code VARCHAR2(10);
1192 l_secondary_rate_type VARCHAR2(15);
1193
1194 BEGIN
1195
1196 -- initializaton block
1197 l_stmt_id := 0;
1198 l_secondary_currency_code := NULL;
1199 l_secondary_rate_type := NULL;
1200 l_setup_good := FALSE;
1201
1202 -- Parameters we want to check for
1203 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
1204 l_list(2) := 'BIS_GLOBAL_START_DATE';
1205 l_list(3) := 'BIS_PRIMARY_RATE_TYPE';
1206 l_setup_good := bis_common_parameters.check_global_parameters(l_list);
1207
1208 IF (NOT (l_setup_good)) THEN
1209 BIS_COLLECTION_UTILITIES.PUT_LINE (
1210 'Global setup is not correct. Please setup up the global start date, primary currency code and primary rate type.');
1211 END IF;
1212
1213 -- check the secondary currency setup
1214 l_secondary_currency_code :=
1215 bis_common_parameters.get_secondary_currency_code;
1216 l_secondary_rate_type := bis_common_parameters.get_secondary_rate_type;
1217
1218 -- check that either both the secondary rate type and secondary
1219 -- rate are null, or that neither are null.
1220 IF ( (l_secondary_currency_code IS NULL AND
1221 l_secondary_rate_type IS NOT NULL)
1222 OR (l_secondary_currency_code IS NOT NULL AND
1223 l_secondary_rate_type IS NULL) ) THEN
1224
1225 BIS_COLLECTION_UTILITIES.PUT_LINE ('The global secondary currency code setup is incorrect. The secondary currency code cannot be null when the secondary rate type is defined and vice versa.');
1226
1227 l_setup_good := FALSE;
1228
1229 END IF;
1230
1231
1232 return l_setup_good;
1233
1234 EXCEPTION
1235
1236 WHEN OTHERS THEN
1237 rollback;
1238
1239 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1240 l_proc_name || ' ' ||
1241 '#' || l_stmt_id ||
1242 ': ' || SQLERRM);
1243
1244 l_setup_good := false;
1245 return l_setup_good;
1246
1247
1248 END check_global_setup;
1249
1250
1251 END opi_dbi_wip_comp_init_pkg;
[Home] [Help]