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