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