1 PACKAGE BODY opi_dbi_wip_comp_opm_pkg AS
2 /*$Header: OPIDCOMPLOB.pls 115.4 2003/11/14 18:46:52 cdaly noship $ */
3
4 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*
5 Package level variables for session info, including schema name
6 for truncating and collecting stats
7 *++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
8
9 s_opi_schema VARCHAR2(30);
10 s_status VARCHAR2(30);
11 s_industry VARCHAR2(30);
12 s_SUCCESS CONSTANT NUMBER := 0; -- concurrent manager success code
13 s_pkg_name CONSTANT VARCHAR2 (50) := 'opi_dbi_wip_comp_opm_pkg';
14 s_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
15 OPM_SOURCE CONSTANT NUMBER := 2;
16 NON_PLANNED_ITEM CONSTANT NUMBER := 6; -- Non planned items have an mrp_planning_code of 6
17 WIP_COMPLETION_ETL CONSTANT NUMBER := 1; -- WIP completions
18
19 /*++++++++++++++++++++++++++++++++++++++++*/
20 /* Package level variables for the logged
21 in user.
22 /*++++++++++++++++++++++++++++++++++++++++*/
23
24 s_user_id NUMBER := nvl(fnd_global.user_id, -1);
25 s_login_id NUMBER := nvl(fnd_global.login_id, -1);
26
27
28 could_not_log_success EXCEPTION;
29 PRAGMA EXCEPTION_INIT (could_not_log_success, -20005);
30
31 schema_info_not_found EXCEPTION;
32 PRAGMA EXCEPTION_INIT (schema_info_not_found, -20000);
33
34 run_common_module EXCEPTION;
35 PRAGMA EXCEPTION_INIT (run_common_module, -20002);
36
37 PROCEDURE collect_opm_led_current (p_global_start_date DATE)
38 AS
39 BEGIN
40
41 /*
42 Note that the inclusion of doc_type in each inline view is not needed functionally,
43 but may benefit the optimizer in supporting the use of an index probe in the join.
44
45 Modifications to this procedure should be applied to collect_opm_tst_current.
46 */
47 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' ||
48 'opi_dbi_opm_wip_led_current');
49
50 INSERT INTO opi_dbi_opm_wip_led_current
51 ( orgn_code,
52 item_id,
53 gl_trans_date,
54 trans_qty,
55 amount_base)
56 SELECT
57 t.orgn_code,
58 t.item_id,
59 led.gl_trans_date,
60 sum (t.trans_qty),
61 sum (led.amount_base)
62 FROM
63 ( SELECT
64 doc_type,
65 doc_id,
66 line_id,
67 TRUNC(trans_date) trans_date,
68 orgn_code,
69 item_id,
70 SUM(trans_qty) trans_qty
71 FROM
72 ic_tran_pnd
73 WHERE
74 doc_type = 'PROD'
75 AND line_type IN (1,2)
76 AND completed_ind = 1
77 AND gl_posted_ind = 1
78 AND trans_date >= p_global_start_date
79 GROUP BY
80 doc_type,
81 doc_id,
82 line_id,
83 TRUNC(trans_date),
84 orgn_code,
85 item_id
86 ) t,
87 ( SELECT
88 sub.doc_type,
89 sub.doc_id,
90 sub.line_id,
91 TRUNC(sub.gl_trans_date) gl_trans_date,
92 SUM(sub.amount_base * sub.debit_credit_sign) amount_base
93 FROM
94 gl_subr_led sub,
95 opi_dbi_run_log_curr log
96 WHERE
97 sub.gl_trans_date >= p_global_start_date
98 AND sub.acct_ttl_type = 1500
99 AND sub.doc_type = 'PROD'
100 AND log.source = OPM_SOURCE
101 AND log.etl_id = WIP_COMPLETION_ETL
102 AND log.organization_id IS NULL
103 AND sub.subledger_id >= log.start_txn_id
104 AND sub.subledger_id < log.next_start_txn_id
105 GROUP BY
106 sub.doc_type,
107 sub.doc_id,
108 sub.line_id,
109 TRUNC(sub.gl_trans_date)
110 ) led
111 WHERE
112 t.doc_type = led.doc_type
113 AND t.doc_id = led.doc_id
114 AND t.line_id = led.line_id
115 AND t.trans_date = led.gl_trans_date
116 GROUP BY
117 t.orgn_code,
118 t.item_id,
119 led.gl_trans_date;
120
121 END collect_opm_led_current;
122
123
124 PROCEDURE capture_opm_tst_prior
125 AS
126 l_prior_populated NUMBER;
127 BEGIN
128
129 -- Modified 11/13/03 by CDALY
130 -- to use stop_reason_code in log to determine if the prior table needes populating
131 -- or if it was already populated by an aborted but committed previous attempt.
132 -- If prior were allowed to be truncated when current was already truncated by an
133 -- aborted but committed previous attempt, then the prior data would be lost.
134
135 select stop_reason_code into l_prior_populated -- get state of prior table from log
136 from opi_dbi_run_log_curr
137 where etl_id = 1
138 and source = 2;
139
140 if NVL(l_prior_populated, 0) <> 9999 --if state = successful
141 then
142 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_prior');
143
144 INSERT INTO opi_dbi_opm_wip_tst_prior
145 (orgn_code, item_id, gl_trans_date, trans_qty, amount_base)
146 SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
147 FROM opi_dbi_opm_wip_tst_current;
148
149 update opi_dbi_run_log_curr -- state = prior populated and current truncated
150 set
151 stop_reason_code = 9999, -- flag to indicate prior tst table has been populated
152 last_update_date = sysdate,
153 last_updated_by = s_user_id,
154 last_update_login = s_login_id
155 where etl_id = 1
156 and source = 2;
157 commit;
158 END IF;
159
160 END capture_opm_tst_prior;
161
162
163 PROCEDURE collect_opm_tst_current (p_global_start_date DATE)
164 AS
165 BEGIN
166 /*
167 Differences between this procedure and collect_opm_LED_current:
168
169 * target table is opi_dbi_opm_wip_TST_current (rather than ... LED_current)
170 * subr table is gl_subr_TST (rather than gl_subr_LED)
171 * ic_tran_pnd.gl_posted_ind = 0 (rather than 1)
172
173 Noting these differences here makes it possible to propagate maintenance to the
174 led_current procedure by copying it and applying the differences. This is a
175 good practice, because the procedures as so similar.
176 */
177
178 INSERT INTO opi_dbi_opm_wip_tst_current
179 ( orgn_code,
180 item_id,
181 gl_trans_date,
182 trans_qty,
183 amount_base)
184 SELECT
185 t.orgn_code,
186 t.item_id,
187 led.gl_trans_date,
188 sum (t.trans_qty),
189 sum (led.amount_base)
190 FROM
191 ( SELECT
192 doc_type,
193 doc_id,
194 line_id,
195 TRUNC(trans_date) trans_date,
196 orgn_code,
197 item_id,
198 SUM(trans_qty) trans_qty
199 FROM
200 ic_tran_pnd
201 WHERE
202 doc_type = 'PROD'
203 AND line_type IN (1,2)
204 AND completed_ind = 1
205 AND gl_posted_ind = 0
206 AND trans_date >= p_global_start_date
207 GROUP BY
208 doc_type,
209 doc_id,
210 line_id,
211 TRUNC(trans_date),
212 orgn_code,
213 item_id
214 ) t,
215 ( SELECT
216 doc_type,
217 doc_id,
218 line_id,
219 TRUNC(gl_trans_date) gl_trans_date,
220 SUM(amount_base * debit_credit_sign) amount_base
221 FROM
222 gl_subr_tst
223 WHERE
224 gl_trans_date >= p_global_start_date
225 AND acct_ttl_type = 1500
226 AND doc_type = 'PROD'
227 GROUP BY
228 doc_type,
229 doc_id,
230 line_id,
231 TRUNC(gl_trans_date)
232 ) led
233 WHERE
234 t.doc_type = led.doc_type
235 AND t.doc_id = led.doc_id
236 AND t.line_id = led.line_id
237 AND t.trans_date = led.gl_trans_date
238 GROUP BY
239 t.orgn_code,
240 t.item_id,
241 led.gl_trans_date;
242
243 END collect_opm_tst_current;
244
245
246 PROCEDURE collect_init_opm_stg
247 IS
248 BEGIN
249 INSERT INTO OPI_DBI_WIP_COMP_STG (
250 organization_id,
251 inventory_item_id,
252 transaction_date,
253 completion_quantity,
254 completion_value_b,
255 uom_code,
256 source,
257 planned_item,
258 creation_date,
259 last_update_date,
260 created_by,
261 last_updated_by,
262 last_update_login)
263 SELECT
264 msi.organization_id,
265 msi.inventory_item_id,
266 t.gl_trans_date,
267 sum (t.trans_qty),
268 sum (t.amount_base),
269 msi.primary_uom_code,
270 OPM_SOURCE, -- this is only for OPI orgs
271 decode (msi.mrp_planning_code,
272 NON_PLANNED_ITEM, 'N',
273 'Y'),
274 sysdate,
275 sysdate,
276 s_user_id,
277 s_user_id,
278 s_login_id
279 FROM
280 sy_orgn_mst_b org,
281 ic_whse_msT w,
282 ic_item_mst_b iim,
283 mtl_system_items_b msi,
284 (
285 SELECT orgn_code, item_id, gl_trans_date,
286 SUM(trans_qty) trans_qty, SUM(amount_base) amount_base
287 FROM
288 (
289 SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
290 FROM opi_dbi_opm_wip_led_current
291 UNION ALL
292 SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
293 FROM opi_dbi_opm_wip_tst_current
294 )
295 GROUP BY orgn_code, item_id, gl_trans_date
296 HAVING SUM(trans_qty) <> 0 OR SUM(amount_base) <> 0
297 ) t
298 WHERE
299 org.orgn_code = t.orgn_code
300 AND w.whse_code = org.resource_whse_code
301 AND iim.item_id = t.item_id
302 AND msi.organization_id = w.mtl_organization_id
303 AND msi.segment1 = iim.item_no
304 GROUP BY
305 msi.organization_id,
306 msi.inventory_item_id,
307 t.gl_trans_date,
308 msi.primary_uom_code,
309 msi.mrp_planning_code;
310
311 END collect_init_opm_stg;
312
313
314 PROCEDURE collect_init_opm_wip_comp (errbuf OUT NOCOPY VARCHAR2,
315 retcode OUT NOCOPY NUMBER,
316 p_global_start_date IN DATE)
317 IS
318
319 l_proc_name VARCHAR2 (60) := 'collect_init_opm_wip_comp';
320 l_stmt_id NUMBER := 0;
321
322 BEGIN
323 -- If all bounds have been set up, extract all the data.
324 -- The data is simply inserted into the staging table, since
325 -- this is the initial load the staging table should be empty.
326 --
327 -- WIP completions transactions (acct_ttl_type = 1500, doc_type = 'PROD',
328 -- line_type = 1,2) cause WIP completion
329 -- quantity/value to increase.
330 --
331 -- GSL acct_ttl_type 1500 represents the INV valuation account.
332 -- To restrict to WIP, doc_type must be restricted to PROD.
333 -- To restrict WIP transactions to completions, line_type must be 1 or 2.
334 -- GSL.amount_base is unsigned, so prior to summing, it must be multiplied
335 -- by GSL.debit_credit_sign.
336 --
337 -- When joining to ITP, ITP should be restricted to GL_POSTED_IND = 1,
338 -- since corrections may have been made (with balancing inventory transactions)
339 -- that have not yet been posted to GSL. Those corrections can only be picked up
340 -- in the extraction from GST (gl_subr_tst).
341 --
342 -- There is no restriction on the appearance of Expense Items in GSL, GST, or ITP.
343 --
344 -- Note that it is necessary to pre-aggregate (via in-line view) GSL and ITP,
345 -- prior to joining, since both have a high granularity than is supported by the
346 -- LINE_ID level join. In ITP, there may be multiple TRANS_IDs per LINE_ID, and in
347 -- GSL, there may be multiple SUBLEDGER_IDs per LINE_ID. Failure to pre-aggregate
348 -- could have the effect of double-counting inventory transactions or subledger
349 -- transactions.
350 --
351 -- There is no concept of a non standard discrete jobs, so no exclusion logig is needed.
352 --
353 -- 3 separate tables are used for the led_current, tst_current, and tst_prior rows.
354 -- This facilitates debugging, and makes it easier to truncate "current" tables
355 -- rather than use delete. The tst_prior table is not used for initial load.
356 --
357
358 IF (NOT (fnd_installation.get_app_info('OPI', s_status, s_industry, s_opi_schema))) THEN
359 RAISE schema_info_not_found;
360 END IF;
361
362 -- Clear OPM-specific work tables
363 l_stmt_id := 10;
364 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_current');
365 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_prior');
366 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_led_current');
367
368 -- Check if all the bounds have been properly set up
369 l_stmt_id := 20;
370 IF (NOT (opi_dbi_common_mod_init_pkg.init_end_bounds_setup
371 (WIP_COMPLETION_ETL, OPM_SOURCE))) THEN
372 RAISE run_common_module;
373 END IF;
374
375 -- Collect WIP Completions from Permanent (led) and Test (tst) Subledgers
376 l_stmt_id := 30;
377 collect_opm_led_current(p_global_start_date);
378 l_stmt_id := 40;
379 collect_opm_tst_current(p_global_start_date);
380
381 -- Sum up WIP Completions, join in other needed tables
382 l_stmt_id := 50;
383 collect_init_opm_stg;
384
385 -- Report success to OPI_DBI_RUN_LOG_CURR.
386 l_stmt_id := 60;
387 IF (NOT (opi_dbi_common_mod_incr_pkg.etl_report_success
388 (WIP_COMPLETION_ETL, OPM_SOURCE))) THEN
389 RAISE could_not_log_success;
390 END IF;
391
392 --Following lines moved to beginning of Incremental ETL by CDALY 11/13/03
393 -- Move tst_current date to tst_prior
394 -- l_stmt_id := 70;
395 -- capture_opm_tst_prior;
396
397 -- Since data pushed to staging table and success logged, commit everything
398 l_stmt_id := 80;
399 COMMIT;
400
401 -- Truncate work tables whose data is no longer needed
402 l_stmt_id := 90;
403
404 -- Following line moved to capture_opm_tst_prior by CDALY 11/13/03
405 -- EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_current');
406
407 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_prior');
408 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_led_current');
409
410 -- all done, so return successfully.
411 l_stmt_id := 100;
412 retcode := s_SUCCESS;
413 errbuf := '';
414 return;
415
416 EXCEPTION
417
418 WHEN could_not_log_success THEN
419 rollback;
420
421 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' || l_proc_name || ' ' ||
422 '#' || l_stmt_id || ': ' ||
423 'WIP Completion ETLs Initial load OPI data extraction success could not be logged into log table. Aborting.');
424
425
426 retcode := s_ERROR;
427 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
428 l_stmt_id || ': ' ||
429 'WIP Completion ETLs Initial load OPI data extraction success could not be logged into log table. Aborting.';
430 RAISE; -- propagate exception to wrapper.
431
432
433 WHEN OTHERS THEN
434 rollback;
435
436 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' || l_proc_name || ' ' ||
437 '#' || l_stmt_id || ': ' || SQLERRM);
438
439 retcode := s_ERROR;
440 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
441 l_stmt_id || ': ' ||
442 'WIP Completion ETLs Initial load OPI data extraction failed.';
443 RAISE; -- propagate exception to wrapper.
444
445 END collect_init_opm_wip_comp;
446
447
448 PROCEDURE collect_incr_opm_stg
449 IS
450 BEGIN
451 INSERT INTO OPI_DBI_WIP_COMP_STG (
452 organization_id,
453 inventory_item_id,
454 transaction_date,
455 completion_quantity,
456 completion_value_b,
457 uom_code,
458 source,
459 planned_item,
460 creation_date,
461 last_update_date,
462 created_by,
463 last_updated_by,
464 last_update_login)
465 SELECT
466 msi.organization_id,
467 msi.inventory_item_id,
468 t.gl_trans_date,
469 sum (t.trans_qty),
470 sum (t.amount_base),
471 msi.primary_uom_code,
472 OPM_SOURCE, -- this is only for OPI orgs
473 decode (msi.mrp_planning_code,
474 NON_PLANNED_ITEM, 'N',
475 'Y'),
476 sysdate,
477 sysdate,
478 s_user_id,
479 s_user_id,
480 s_login_id
481 FROM
482 sy_orgn_mst_b org,
483 ic_whse_msT w,
484 ic_item_mst_b iim,
485 mtl_system_items_b msi,
486 (
487 SELECT orgn_code, item_id, gl_trans_date,
488 SUM(trans_qty) trans_qty, SUM(amount_base) amount_base
489 FROM
490 (
491 SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
492 FROM opi_dbi_opm_wip_led_current
493 UNION ALL
494 SELECT orgn_code, item_id, gl_trans_date, trans_qty, amount_base
495 FROM opi_dbi_opm_wip_tst_current
496 UNION ALL
497 SELECT orgn_code, item_id, gl_trans_date, -trans_qty, -amount_base
498 FROM opi_dbi_opm_wip_tst_prior
499 )
500 GROUP BY orgn_code, item_id, gl_trans_date
501 HAVING SUM(trans_qty) <> 0 OR SUM(amount_base) <> 0
502 ) t
503 WHERE
504 org.orgn_code = t.orgn_code
505 AND w.whse_code = org.resource_whse_code
506 AND iim.item_id = t.item_id
507 AND msi.organization_id = w.mtl_organization_id
508 AND msi.segment1 = iim.item_no
509 GROUP BY
510 msi.organization_id,
511 msi.inventory_item_id,
512 t.gl_trans_date,
513 msi.primary_uom_code,
514 msi.mrp_planning_code;
515
516 END collect_incr_opm_stg;
517
518
519 PROCEDURE collect_incr_opm_wip_comp (errbuf OUT NOCOPY VARCHAR2,
520 retcode OUT NOCOPY NUMBER,
521 p_global_start_date IN DATE)
522 IS
523
524 l_proc_name VARCHAR2 (60) := 'collect_incr_opm_wip_comp';
525 l_stmt_id NUMBER := 0;
526
527 /*
528 This is just like the initial load version with the following exceptions:
529
530 * removal of the TRUNCATE of tst_prior at beginning
531 * call to collect_incr_opm_stg (rather than collect_init_opm_stg)
532 */
533 BEGIN
534 -- If all bounds have been set up, extract all the data.
535 -- The data is simply inserted into the staging table, since
536 -- this is the initial load the staging table should be empty.
537 --
538 -- WIP completions transactions (acct_ttl_type = 1500, doc_type = 'PROD',
539 -- line_type = 1,2) cause WIP completion
540 -- quantity/value to increase.
541 --
542 -- GSL acct_ttl_type 1500 represents the INV valuation account.
543 -- To restrict to WIP, doc_type must be restricted to PROD.
544 -- To restrict WIP transactions to completions, line_type must be 1 or 2.
545 -- GSL.amount_base is unsigned, so prior to summing, it must be multiplied
546 -- by GSL.debit_credit_sign.
547 --
548 -- When joining to ITP, ITP should be restricted to GL_POSTED_IND = 1,
549 -- since corrections may have been made (with balancing inventory transactions)
550 -- that have not yet been posted to GSL. Those corrections can only be picked up
551 -- in the extraction from GST (gl_subr_tst).
552 --
553 -- There is no restriction on the appearance of Expense Items in GSL, GST, or ITP.
554 --
555 -- Note that it is necessary to pre-aggregate (via in-line view) GSL and ITP,
556 -- prior to joining, since both have a high granularity than is supported by the
557 -- LINE_ID level join. In ITP, there may be multiple TRANS_IDs per LINE_ID, and in
558 -- GSL, there may be multiple SUBLEDGER_IDs per LINE_ID. Failure to pre-aggregate
559 -- could have the effect of double-counting inventory transactions or subledger
560 -- transactions.
561 --
562 -- There is no concept of a non standard discrete jobs, so no exclusion logig is needed.
563 --
564 -- 3 separate tables are used for the led_current, tst_current, and tst_prior rows.
565 -- This facilitates debugging, and makes it easier to truncate "current" tables
566 -- rather than use delete. The tst_prior table is not used for initial load.
567 --
568
569 IF (NOT (fnd_installation.get_app_info('OPI', s_status, s_industry, s_opi_schema))) THEN
570 RAISE schema_info_not_found;
571 END IF;
572
573 -- Clear OPM-specific work tables and populate prior table
574 l_stmt_id := 10;
575 --Following lines moved from end of Initial and Incremental ETL by CDALY 11/13/03
576 --Now only called here at the beginning of Incremental ETL
577 -- Move tst_current data to tst_prior
578 capture_opm_tst_prior;
579
580
581 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_current');
582 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_led_current');
583
584 -- Check if all the bounds have been properly set up
585 l_stmt_id := 20;
586 IF (NOT (opi_dbi_common_mod_init_pkg.init_end_bounds_setup
587 (WIP_COMPLETION_ETL, OPM_SOURCE))) THEN
588 RAISE run_common_module;
589 END IF;
590
591 -- Collect WIP Completions from Permanent (led) and Test (tst) Subledgers
592 l_stmt_id := 30;
593 collect_opm_led_current(p_global_start_date);
594 l_stmt_id := 40;
595 collect_opm_tst_current(p_global_start_date);
596
597 -- Sum up WIP Completions, join in other needed tables
598 l_stmt_id := 50;
599 collect_incr_opm_stg;
600
601 -- Report success to OPI_DBI_RUN_LOG_CURR.
602 l_stmt_id := 60;
603 IF (NOT (opi_dbi_common_mod_incr_pkg.etl_report_success
604 (WIP_COMPLETION_ETL, OPM_SOURCE))) THEN
605 RAISE could_not_log_success;
606 END IF;
607
608 -- Added 11/13/03 by CDALY
609 -- set stop_reason_code in log to NULL to indicate to capture_opm_tst_prior that prior table is no longer needed
610 update opi_dbi_run_log_curr -- state = successful
611 set
612 stop_reason_code = NULL, -- flag to indicate prior tst table has been populated
613 last_update_date = sysdate,
614 last_updated_by = s_user_id,
615 last_update_login = s_login_id
616 where etl_id = 1
617 and source = 2;
618
619 -- Following lines moved to beginning of this Incremental ETL by CDALY 11/13/03
620 -- Move tst_current date to tst_prior
621 -- l_stmt_id := 70;
622 -- capture_opm_tst_prior;
623
624 -- Since data pushed to staging table and success logged, commit everything
625 l_stmt_id := 80;
626 COMMIT;
627
628 -- Truncate work tables whose data is no longer needed
629 l_stmt_id := 90;
630
631 -- Following line moved to capture_opm_tst_prior by CDALY 11/13/03
632 -- EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_current');
633
634 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_tst_prior');
635 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || s_opi_schema || '.' || 'opi_dbi_opm_wip_led_current');
636
637 -- all done, so return successfully.
638 l_stmt_id := 100;
639 retcode := s_SUCCESS;
640 errbuf := '';
641 return;
642
643 EXCEPTION
644
645 WHEN could_not_log_success THEN
646 rollback;
647
648 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' || l_proc_name || ' ' ||
649 '#' || l_stmt_id || ': ' ||
650 'WIP Completion ETLs Initial load OPI data extraction success could not be logged into log table. Aborting.');
651
652
653 retcode := s_ERROR;
654 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
655 l_stmt_id || ': ' ||
656 'WIP Completion ETLs Initial load OPI data extraction success could not be logged into log table. Aborting.';
657 RAISE; -- propagate exception to wrapper.
658
659
660 WHEN OTHERS THEN
661 rollback;
662
663 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' || l_proc_name || ' ' ||
664 '#' || l_stmt_id || ': ' || SQLERRM);
665
666 retcode := s_ERROR;
667 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
668 l_stmt_id || ': ' ||
669 'WIP Completion ETLs Initial load OPI data extraction failed.';
670 RAISE; -- propagate exception to wrapper.
671
672 END collect_incr_opm_wip_comp;
673
674
675 END opi_dbi_wip_comp_opm_pkg;
[Home] [Help]