1 PACKAGE BODY opi_dbi_wip_comp_incr_pkg AS
2 /*$Header: OPIDCOMPLRB.pls 120.0 2005/05/24 18:25:23 appldev noship $ */
3
4 /*++++++++++++++++++++++++++++++++++++++++*/
5 /* Function and procedure declarations in this file but not in spec*/
6 /*++++++++++++++++++++++++++++++++++++++++*/
7
8 PROCEDURE collect_incr_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_incr (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 /*++++++++++++++++++++++++++++++++++++++++*/
42 /* PACKAGE LEVEL CONSTANTS */
43 /*++++++++++++++++++++++++++++++++++++++++*/
44
45 s_pkg_name CONSTANT VARCHAR2 (50) := 'opi_dbi_wip_comp_incr_pkg';
46 s_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
47 s_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
48 s_SUCCESS CONSTANT NUMBER := 0; -- concurrent manager success code
49
50
51 /*++++++++++++++++++++++++++++++++++++++++*/
52 /* Package level variables for session info-
53 including schema name for truncating and
54 collecting stats */
55 /*++++++++++++++++++++++++++++++++++++++++*/
56
57 s_opi_schema VARCHAR2(30);
58 s_status VARCHAR2(30);
59 s_industry VARCHAR2(30);
60
61 /*----------------------------------------*/
62
63 /*++++++++++++++++++++++++++++++++++++++++*/
64 /* Package level variables for the logged
65 in user.
66 /*++++++++++++++++++++++++++++++++++++++++*/
67
68 s_user_id NUMBER;
69 s_login_id NUMBER;
70
71 /*----------------------------------------*/
72
73 /*++++++++++++++++++++++++++++++++++++++++*/
74 /* Package level exceptions defined for
75 clearer error handling. */
76 /*++++++++++++++++++++++++++++++++++++++++*/
77
78 -- exception to raise if unable to get schema information
79 schema_info_not_found EXCEPTION;
80 PRAGMA EXCEPTION_INIT (schema_info_not_found, -20000);
81
82 -- exception to raise if one or both of OPI and OPM data extraction
83 -- fails
84 data_extraction_failed EXCEPTION;
85 PRAGMA EXCEPTION_INIT (data_extraction_failed, -20001);
86
87 -- exception to throw if user needs to run common module incremental
88 -- or initial load again - e.g. if bounds have not been set up correctly.
89 run_common_module EXCEPTION;
90 PRAGMA EXCEPTION_INIT (run_common_module, -20002);
91
92 -- exception to raise if DBI global currency code not found
93 global_curr_code_not_found EXCEPTION;
94 PRAGMA EXCEPTION_INIT (global_curr_code_not_found, -20003);
95
96 -- exception to raise if missing conversion rates exist
97 missing_conversion_rates EXCEPTION;
98 PRAGMA EXCEPTION_INIT (missing_conversion_rates, -20004);
99
100 -- exception to raise unable to log successful data extraction to
101 -- log table.
102 could_not_log_success EXCEPTION;
103 PRAGMA EXCEPTION_INIT (could_not_log_success, -20005);
104
105 -- exception to raise if global parameters such as global
106 -- start date and global currency code are not available
107 global_setup_missing EXCEPTION;
108 PRAGMA EXCEPTION_INIT (global_setup_missing, -20006);
109
110 -- exception to raise if it is not time for the incremental load to run
111 -- i.e. the initial load has not been run yet
112 cannot_run_incr_load EXCEPTION;
113 PRAGMA EXCEPTION_INIT (cannot_run_incr_load, -20007);
114
115
116 /*----------------------------------------*/
117
118 /* collect_wip_completions_incr
119
120 Wrapper routine for OPI + OPM wip completion data extraction for
121 incremental load.
122
123 When this routine runs, the staging table is not guaranteed to be
124 empty, because there might be data left over from an errored-out
125 incremental run. So do not truncate any tables at start.
126
127 Both OPI and OPM ETLs can run independently upto the staging table level,
128 even if the other fails. That way, errors found in extracting OPI and OPM
129 data in the incremental run can be reported simultaneously.
130
131 If either OPI or OPM fails before the staging table level, then the
132 routine ends with error.
133
134 If not, then conversion rates have to be calculated for
135 all the OPI and OPM data in the staging table.
136
137 If all conversion rates are found, data is merged to the fact table,
138 following which the staging table can be truncated.
139
140 This wrapper will only commit data implicitly through the DDL that
141 truncates the staging table. That way, it ensures that the merge is
142 committed and the staging table is emptied simultaneously. We cannot
143 avoid this by truncating the staging table at the start of the function,
144 since it might have data from a previous run that failed half way. That
145 data will never be recollected and should not be thrown away.
146
147 This function does not return with an exception in case of error
148 but ends with a retcode of error. However helper functions are
149 expected to throw exceptions. We do not look at the retcode/errbuf
150 for helper functions. If a helper function fails, it is expected
151 to write a error message to the log and to throw an exception
152 back to this wrapper function.
153
154 Date Author Action
155 04/23/2003 Dinkar Gupta Wrote procedure
156
157 */
158
159 PROCEDURE collect_wip_completions_incr (errbuf OUT NOCOPY VARCHAR2,
160 retcode OUT NOCOPY NUMBER)
161 IS
162
163 l_proc_name CONSTANT VARCHAR2 (60) := 'collect_wip_completions_incr';
164 l_stmt_id NUMBER;
165
166 l_opi_success BOOLEAN; -- OPI extraction successful?
167 l_opm_success BOOLEAN; -- OPM extraction successful?
168
169 l_global_start_date DATE;
170
171 BEGIN
172
173 -- initialization block
174 l_stmt_id := 0;
175 l_opi_success := false;
176 l_opm_success := false;
177 l_global_start_date := NULL;
178
179 -- session parameters
180 l_stmt_id := 5;
181 s_user_id := nvl(fnd_global.user_id, -1);
182 s_login_id := nvl(fnd_global.login_id, -1);
183
184
185 -- get session parameters
186 l_stmt_id := 10;
187 IF (NOT (fnd_installation.get_app_info('OPI', s_status, s_industry,
188 s_opi_schema))) THEN
189 RAISE schema_info_not_found;
190 END IF;
191
192 -- check if the global set up is good
193 l_stmt_id := 11;
194 IF (NOT (opi_dbi_wip_comp_init_pkg.check_global_setup ())) THEN
195 RAISE global_setup_missing;
196 END IF;
197
198 -- get the DBI global start date
199 l_stmt_id := 20;
200 l_global_start_date := trunc (bis_common_parameters.get_global_start_date);
201
202 -- Collect the WIP completions for OPI
203 BEGIN
204
205 l_stmt_id := 30;
206 -- COMMIT DATA AS NEEDED!!! WRAPPER WILL NOT COMMIT DATA FOR
207 -- STAGING TABLE.
208 collect_incr_opi_wip_comp (errbuf, retcode, l_global_start_date);
209 -- OPI collection into staging table successful
210 l_stmt_id := 40;
211 l_opi_success := true;
212
213 EXCEPTION
214
215 WHEN OTHERS THEN
216 rollback;
217
218 -- opi data was not found successfully
219 l_opi_success := false;
220
221 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
222 l_proc_name || ' ' ||
223 '#' || l_stmt_id ||
224 ': ' || SQLERRM);
225 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
226 l_proc_name || ' ' ||
227 '#' || l_stmt_id || ': ' ||
228 'Unable to collect OPI WIP completions data in incremental load into staging table.');
229
230 END;
231
232 -- Collect the WIP completions for OPM
233 BEGIN
234
235
236 l_stmt_id := 50;
237 -- COMMIT DATA AS NEEDED!!! WRAPPER WILL NOT COMMIT DATA FOR
238 -- STAGING TABLE.
239 opi_dbi_wip_comp_opm_pkg.collect_incr_opm_wip_comp (errbuf, retcode,
240 l_global_start_date);
241 -- OPM collection into staging table successful
242 l_stmt_id := 60;
243 l_opm_success := true;
244
245 EXCEPTION
246
247 WHEN OTHERS THEN
248 rollback;
249
250 -- opm data was not found successfully
251 l_opm_success := false;
252
253 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
254 l_proc_name || ' ' ||
255 '#' || l_stmt_id ||
256 ': ' || SQLERRM);
257 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
258 l_proc_name || ' ' ||
259 '#' || l_stmt_id || ': ' ||
260 'Unable to collect OPM WIP completions data in incremental load into staging table.');
261
262 END;
263
264
265 -- If either OPI or OPM failed, then abort here
266 l_stmt_id := 70;
267 IF (NOT (l_opi_success AND l_opm_success)) THEN
268 RAISE data_extraction_failed;
269 END IF;
270
271 -- Compute the conversions rates for all the data in the staging table.
272 -- Use the same function as the initial load since conversion rate
273 -- computation is identical.
274 -- COMMIT DATA SO THAT THE ROLLBACK SEGMENT DOES BECOME TOO LARGE IN
275 -- THE FUNCTION. WRAPPER ONLY COMMITS FOR FACT TABLE.
276 l_stmt_id :=80;
277 opi_dbi_wip_comp_init_pkg.compute_wip_comp_conv_rates (errbuf, retcode,
278 s_opi_schema);
279
280 -- Merge all the data to the fact table.
281 -- DO NOT COMMIT DATA IN THIS ONE FUNCTION.
282 -- LET THE WRAPPER COORDINATE THE LAST COMMIT.
283 l_stmt_id := 90;
284 update_wip_comp_fact_incr (errbuf, retcode);
285
286 -- Finally truncate the staging table if we have got this far, because
287 -- all data in the fact.
288 -- The truncate will implicitly also commit data to the fact table.
289 -- This is important because the staging table is "persistent" i.e.
290 -- failures midway do not cause data stored in the staging table to be
291 -- deleted and data once collected in staging table is not collected
292 -- again. So it is imperative the commit on the fact table and
293 -- truncate on staging table happen as one operation.
294 l_stmt_id := 100;
295 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
296 'OPI_DBI_WIP_COMP_STG');
297
298 l_stmt_id := 110;
299 BIS_COLLECTION_UTILITIES.PUT_LINE
300 ('WIP Completions Incremental load terminated successfully.');
301 retcode := s_SUCCESS;
302 errbuf := '';
303 return;
304
305 EXCEPTION
306
307 WHEN schema_info_not_found THEN
308 rollback;
309
310 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
311 l_proc_name || ' ' ||
312 '#' || l_stmt_id ||
313 ': ' || SQLERRM);
314
315 retcode := s_ERROR;
316 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
317 l_stmt_id || ': ' ||
318 'WIP Completion ETL Incremental Load failed to get OPI schema info.';
319 return;
320
321 WHEN global_setup_missing THEN
322 rollback;
323
324 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
325 l_proc_name || ' ' ||
326 '#' || l_stmt_id ||
327 ': ' || SQLERRM);
328
329 retcode := s_ERROR;
330 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
331 l_stmt_id || ': ' ||
332 'WIP Completions Incremental Load could not find global setup of global start date and global currency code.';
333 return;
334
335 WHEN data_extraction_failed THEN
336 rollback;
337
338 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
339 l_proc_name || ' ' ||
340 '#' || l_stmt_id ||
341 ': ' || SQLERRM);
342
343 retcode := s_ERROR;
344 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
345 l_stmt_id || ': ' ||
346 'WIP Completion Incremental Load data extraction failed for OPI, OPM or both. Check previous messages for errors. ';
347 return;
348
349 WHEN OTHERS THEN
350 rollback;
351
352 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
353 l_proc_name || ' ' ||
354 '#' || l_stmt_id ||
355 ': ' || SQLERRM);
356
357 retcode := s_ERROR;
358 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
359 l_stmt_id || ': ' ||
360 'WIP Completion ETLs Incremental load Failed.';
361 return;
362
363
364 END collect_wip_completions_incr;
365
366
367 /* collect_incr_opi_wip_comp
368
369 Incremental collection of WIP completion data from MMT/MTA for discrete
370 (OPI) organizations.
371
372 Collected transactions must be past the global start for every discrete
373 organization and lie in the transaction range recorded in the current
374 log table, OPI_DBI_RUN_LOG_CURR. The collection therefore proceeds to
375 extract every transaction per discrete org upto the first uncosted
376 transaction.
377
378 To ensure that the bounds are good, we call the incr_end_bounds_setup API
379 from the Common Module Incremental load.
380
381 Data is not committed to the staging table until the bounds in the
382 current log table have been updated successfully using the Common Module
383 API etl_report_success.
384
385 WIP Completions ETL needs to extract two types of transactions from
386 MMT:
387
388 44 - WIP completion transaction increases the quantity/value
389 of WIP completions
390 17 - Assembly return transaction decreases the quantity/value of
394 the WIP valuation account decreases on WIP completions and increases
391 of WIP completions.
392
393 The WIP valuation account has an accounting line type of 7 in MTA, but
395 on assembly returns. Thus for every transaction, the corresponding
396 value we pick is -1 * (sum of all accouting line type 7) because this
397 ETL must report increases completion value on completions and decreased
398 value on returns.
399
400
401 Parameters:
402 p_global_start_date - global start date for DBI collection.
403 Expect this to be trunc'ed
404
405 Date Author Action
406 04/23/2003 Dinkar Gupta Wrote procedure
407
408 */
409
410 PROCEDURE collect_incr_opi_wip_comp (errbuf OUT NOCOPY VARCHAR2,
411 retcode OUT NOCOPY NUMBER,
412 p_global_start_date IN DATE)
413 IS
414
415 l_proc_name CONSTANT VARCHAR2 (60) := 'collect_incr_opi_wip_comp';
416 l_stmt_id NUMBER;
417
418 BEGIN
419
420 -- initialization block
421 l_stmt_id := 0;
422
423 -- Check if all the bounds have been properly set up
424 l_stmt_id := 10;
425 IF (NOT (opi_dbi_common_mod_incr_pkg.incr_end_bounds_setup
426 (WIP_COMPLETION_ETL, OPI_SOURCE))) THEN
427 RAISE run_common_module;
428 END IF;
429
430 l_stmt_id := 15;
431 -- check if it is ok to run incremental load
432 IF (NOT (opi_dbi_common_mod_incr_pkg.run_incr_load
433 (WIP_COMPLETION_ETL, OPI_SOURCE))) THEN
434 RAISE cannot_run_incr_load;
435 END IF;
436
437 -- If all bounds have been set up, extract all the data.
438 -- The data is simply inserted into the staging table,
439 -- which means that the org-item-date key may no longer remain unique
440 -- if there was data from an prior run that errored out too
441 -- after extracting data.
442 --
443 -- WIP completions transactions (MMT type 44) cause WIP completion
444 -- quantity/value to increase.
445 -- Assembly return transactions (MMT type 17) cause WIP completion
446 -- quantity/value to decrease.
447 --
448 -- MTA accounting line type 7 represents the WIP valuation account.
449 -- Since the WIP account decreases on completions and increases on
450 -- returns, we need to use -1 * value from MTA.
451 --
452 -- The join to MTA has to be an outer join since MTA does not
453 -- have any rows for expense items.
454 --
455 -- Note also that the inner query groups on mmt.transaction_id.
456 -- This is to ensure that all MTA rows for an MMT entry are
457 -- summed before quantity is summed, else we miscount quantity.
458 --
459 -- In addition, exclude all non standard discrete jobs. Discrete jobs
460 -- have an entity_type = 1 and standard jobs have a job_type = 1.
461 -- entity_type and job_type are both not null columns, so it is safe
462 -- to outer join and NVL to them.
463 --
464 -- Because of OSFM etc. we pick only the following types of jobs:
465 -- Discrete Jobs
466 -- Repetitive Assemblies
467 -- Closed discrete Jobs
468 -- Flow Schedules.
469
470 l_stmt_id := 20;
471 INSERT /*+ append */
472 INTO opi_dbi_wip_comp_stg (
473 organization_id,
474 inventory_item_id,
475 transaction_date,
476 completion_quantity,
477 completion_value_b,
478 uom_code,
479 source,
480 planned_item,
481 creation_date,
482 last_update_date,
483 created_by,
484 last_updated_by,
485 last_update_login)
486 SELECT
487 compl.organization_id,
488 compl.inventory_item_id,
489 compl.trx_date,
490 sum (compl.mmt_quantity),
491 sum (compl.mta_value),
492 msi.primary_uom_code,
493 OPI_SOURCE, -- this is only for OPI orgs
494 decode (msi.mrp_planning_code,
495 NON_PLANNED_ITEM, 'N',
496 'Y'),
497 sysdate,
498 sysdate,
499 s_user_id,
500 s_user_id,
501 s_login_id
502 FROM mtl_system_items_b msi,
503 (SELECT /*+ leading(log) use_nl(log mmt) index(log, OPI_DBI_RUN_LOG_CURR_N1) index(mmt, mtl_material_transactions_u1) */
504 mmt.organization_id,
505 mmt.inventory_item_id,
506 trunc (mmt.transaction_date) trx_date,
507 mmt.primary_quantity mmt_quantity,
508 -1 * sum (nvl (mta.base_transaction_value, 0)) mta_value
509 FROM mtl_material_transactions mmt,
510 mtl_transaction_accounts mta,
511 wip_entities we,
512 wip_discrete_jobs wdj,
513 opi_dbi_run_log_curr log
514 WHERE log.source = OPI_SOURCE
515 AND log.etl_id = WIP_COMPLETION_ETL
516 AND mmt.organization_id = log.organization_id
517 AND mmt.transaction_id >= log.start_txn_id
518 AND mmt.transaction_id < log.next_start_txn_id
519 AND mmt.transaction_date >= p_global_start_date -- (date trunc'ed)
520 AND mmt.transaction_type_id IN (44, 17)
524 AND we.wip_entity_id = mmt.transaction_source_id
521 AND mta.transaction_id(+) = mmt.transaction_id
522 AND nvl (mta.accounting_line_type, WIP_VALUATION_ACCT) =
523 WIP_VALUATION_ACCT
525 AND we.entity_type IN (WIP_DISCRETE_JOB,
526 WIP_REPETITIVE_ASSEMBLY_JOB,
527 WIP_CLOSED_DISCRETE_JOB,
528 WIP_FLOW_SCHEDULE_JOB)
529 AND wdj.wip_entity_id(+) = we.wip_entity_id
530 AND nvl (wdj.job_type, WIP_DISCRETE_STANDARD_JOB) =
531 WIP_DISCRETE_STANDARD_JOB
532 GROUP BY mmt.organization_id,
533 mmt.inventory_item_id,
534 trunc (mmt.transaction_date),
535 mmt.primary_quantity,
536 mmt.transaction_id) compl
537 WHERE msi.organization_id = compl.organization_id
538 AND msi.inventory_item_id = compl.inventory_item_id
539 GROUP BY
540 compl.organization_id,
541 compl.inventory_item_id,
542 compl.trx_date,
543 msi.primary_uom_code,
544 decode (msi.mrp_planning_code,
545 NON_PLANNED_ITEM, 'N',
546 'Y');
547
548 -- If the entire collection was successful, then try and report this
549 -- success to the OPI_DBI_RUN_LOG_CURR.
550 l_stmt_id := 30;
551 IF (NOT (opi_dbi_common_mod_incr_pkg.etl_report_success
552 (WIP_COMPLETION_ETL, OPI_SOURCE))) THEN
553
554 RAISE could_not_log_success;
555 END IF;
556
557 -- Since data pushed to staging table and success logged, commit
558 -- everything
559 l_stmt_id := 40;
560 commit;
561
562 -- all done, so return successfully.
563 l_stmt_id := 50;
564 retcode := s_SUCCESS;
565 errbuf := '';
566 return;
567
568 EXCEPTION
569
570 WHEN could_not_log_success THEN
571 rollback;
572
573 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
574 l_proc_name || ' ' ||
575 '#' || l_stmt_id || ': ' ||
576 'WIP Completion ETLs Incremental load OPI data extraction success could not be logged into log table. Aborting.');
577
578 retcode := s_ERROR;
579 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
580 l_stmt_id || ': ' ||
581 'WIP Completion ETLs Incremental load OPI data extraction success could not be logged into log table. Aborting.';
582 RAISE; -- propagate exception to wrapper.
583
584
585 WHEN cannot_run_incr_load THEN
586 rollback;
587 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
588 l_proc_name || ' ' ||
589 '#' || l_stmt_id || ': ' ||
590 'WIP Completion incremental load concurrent program should not be running.
591 If the initial load request set has already been run successfully, please submit the incremental load request set. If not, please run the initial load request set.');
592
593 retcode := s_ERROR;
594 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
595 l_stmt_id || ': ' ||
596 'WIP Completion incremental load concurrent program should not be running.
597 If the initial load request set has already been run successfully, please submit the incremental load request set. If not, please run the initial load request set.';
598 RAISE; -- propagate exception to wrapper.
599
600 WHEN run_common_module THEN
601 rollback;
602 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
603 l_proc_name || ' ' ||
604 '#' || l_stmt_id || ': ' ||
605 'WIP Completions incremental load concurrent program is running out of turn. Please submit the incremental load request set for incremental data collection.');
606
607 retcode := s_ERROR;
608 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
609 l_stmt_id || ': ' ||
610 'WIP Completions incremental load concurrent program is running out of turn. Please submit the incremental load request set for incremental data collection.';
611 RAISE; -- propagate exception to wrapper.
612
613
614 WHEN OTHERS THEN
615 rollback;
616
617 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
618 l_proc_name || ' ' ||
619 '#' || l_stmt_id ||
620 ': ' || SQLERRM);
621
622 retcode := s_ERROR;
623 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
624 l_stmt_id || ': ' ||
625 'WIP Completion ETLs Incremental load OPI data extraction failed.';
626 RAISE; -- propagate exception to wrapper.
627
628 END collect_incr_opi_wip_comp;
629
630 /* update_wip_comp_fact_incr
631
632 MERGE data from the staging table to the fact table since the fact
633 table already has some data in it.
634
635
636 The granularity of the staging table will item-org-transaction_date
637 and implicitly the source, since an org is never discrete and
638 process at the same time.
639
640 The item-org-date key will be unique at the fact level.
641
642 THIS FUNCTION WILL NOT COMMIT ANY DATA, SINCE THE WRAPPER IS
643 TAKING RESPONSIBILITY FOR COMMITTING DATA TO THE FACT TABLE.
644
645 Date Author Action
646 04/23/2003 Dinkar Gupta Wrote procedure
647 08/25/2004 Dinkar Gupta Secondary Currency Support
648 */
649
650 PROCEDURE update_wip_comp_fact_incr (errbuf OUT NOCOPY VARCHAR2,
651 retcode OUT NOCOPY NUMBER)
652 IS
653
654 l_proc_name CONSTANT VARCHAR2 (60) := 'update_wip_comp_fact_incr';
655 l_stmt_id NUMBER;
656
657
658 BEGIN
659
660 -- initialization block
661 l_stmt_id := 0;
662
663 -- Merge data into fact table while
664 -- grouping by item-org-transaction_date and source.
665 --
666 -- The merge is essential because there is already data in
667 -- the fact table from previous runs, and backdated transactions
668 -- could mean that the staging table has the same item-org-date
669 -- combination of an existing row in the fact table.
670 l_stmt_id := 10;
671 MERGE INTO opi_dbi_wip_comp_f base
672 USING
673 (SELECT /*+ use_nl(stg, conv) */
674 stg.organization_id,
675 stg.inventory_item_id,
676 stg.transaction_date,
677 sum (stg.completion_quantity) completion_qty,
678 sum (stg.completion_value_b) completion_val,
679 stg.uom_code,
680 conv.conversion_rate,
681 conv.sec_conversion_rate,
682 stg.source,
683 stg.planned_item,
684 sysdate creation_date,
685 sysdate update_date,
686 s_user_id creator,
687 s_user_id updator,
688 s_login_id update_login
689 FROM opi_dbi_wip_comp_stg stg,
690 opi_dbi_wip_comp_conv_rates conv
691 WHERE stg.organization_id = conv.organization_id
692 AND stg.transaction_date = conv.transaction_date
693 GROUP BY stg.organization_id,
694 stg.inventory_item_id,
695 stg.transaction_date,
696 stg.uom_code,
697 conv.conversion_rate,
698 conv.sec_conversion_rate,
699 stg.source,
700 stg.planned_item) new
701 ON
702 ( base.organization_id = new.organization_id
703 AND base.inventory_item_id = new.inventory_item_id
704 AND base.transaction_date = new.transaction_date
705 AND base.source = new.source)
706 WHEN MATCHED THEN UPDATE
707 SET base.completion_value_b = base.completion_value_b +
708 new.completion_val,
709 base.completion_quantity = base.completion_quantity +
710 new.completion_qty,
711 base.last_update_date = new.update_date,
712 base.last_updated_by = new.updator,
713 base.last_update_login = new.update_login
714 WHEN NOT MATCHED THEN INSERT(
715 organization_id,
716 inventory_item_id,
717 transaction_date,
718 completion_quantity,
719 completion_value_b,
720 uom_code,
721 conversion_rate,
722 sec_conversion_rate,
723 source,
724 planned_item,
725 creation_date,
726 last_update_date,
727 created_by,
728 last_updated_by,
729 last_update_login)
730 VALUES (
731 new.organization_id,
732 new.inventory_item_id,
733 new.transaction_date,
734 new.completion_qty,
735 new.completion_val,
736 new.uom_code,
737 new.conversion_rate,
738 new.sec_conversion_rate,
739 new.source,
740 new.planned_item,
741 new.creation_date,
742 new.update_date,
743 new.creator,
744 new.updator,
745 new.update_login);
746
747 -- merge successful, so return
748 l_stmt_id := 20;
749 retcode := s_SUCCESS;
750 errbuf := '';
751 return;
752
753 EXCEPTION
754
755 WHEN OTHERS THEN
756 rollback;
757
758 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
759 l_proc_name || ' ' ||
760 '#' || l_stmt_id ||
761 ': ' || SQLERRM);
762
763 retcode := s_ERROR;
764 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
765 l_stmt_id || ': ' ||
766 'WIP Completion ETLs Incremental merge to fact table failed.';
767 RAISE; -- propagate exception to wrapper.
768
769 END update_wip_comp_fact_incr;
770
771 END opi_dbi_wip_comp_incr_pkg;
[Home] [Help]