1 PACKAGE BODY opi_dbi_common_mod_init_pkg AS
2 /*$Header: OPIDCMODIB.pls 120.1 2005/08/10 01:59:36 sberi noship $ */
3
4 /*++++++++++++++++++++++++++++++++++++++++*/
5 /* Function and procedure declarations in this file but not in spec*/
6 /*++++++++++++++++++++++++++++++++++++++++*/
7
8 PROCEDURE seed_run_log_initial (errbuf OUT NOCOPY VARCHAR2,
9 retcode OUT NOCOPY NUMBER,
10 p_global_start_date IN DATE);
11
12 FUNCTION txn_id_init_setup (p_etl_id IN NUMBER, p_source IN NUMBER)
13 RETURN BOOLEAN;
14
15 FUNCTION collect_date_init_setup (p_etl_id IN NUMBER, p_source IN NUMBER)
16 RETURN BOOLEAN;
17
18 /*----------------------------------------*/
19
20
21 /*++++++++++++++++++++++++++++++++++++++++*/
22 /* PACKAGE LEVEL CONSTANTS */
23 /*++++++++++++++++++++++++++++++++++++++++*/
24
25 s_pkg_name CONSTANT VARCHAR2 (50) := 'opi_dbi_common_mod_init_pkg';
26 s_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
27 s_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
28 s_SUCCESS CONSTANT NUMBER := 0; -- concurrent manager success code
29
30 /*++++++++++++++++++++++++++++++++++++++++*/
31 /* Package level variables for session info-
32 including schema name for truncating and
33 collecting stats */
34 /*++++++++++++++++++++++++++++++++++++++++*/
35
36 s_opi_schema VARCHAR2(30);
37 s_status VARCHAR2(30);
38 s_industry VARCHAR2(30);
39
40 /*----------------------------------------*/
41
42 /*++++++++++++++++++++++++++++++++++++++++*/
43 /* Package level variables for the logged
44 in user.
45 /*++++++++++++++++++++++++++++++++++++++++*/
46
47 s_user_id NUMBER := nvl(fnd_global.user_id, -1);
48 s_login_id NUMBER := nvl(fnd_global.login_id, -1);
49 s_program_id NUMBER:= nvl (fnd_global.conc_program_id, -1);
50 s_program_login_id NUMBER := nvl (fnd_global.conc_login_id, -1);
51 s_program_application_id NUMBER := nvl (fnd_global.prog_appl_id, -1);
52 s_request_id NUMBER := nvl (fnd_global.conc_request_id, -1);
53
54 /*----------------------------------------*/
55
56 /*++++++++++++++++++++++++++++++++++++++++*/
57 /* Package level exceptions defined for
58 clearer error handling. */
59 /*++++++++++++++++++++++++++++++++++++++++*/
60
61 -- exception to raise if unable to get schema information
62 schema_info_not_found EXCEPTION;
63 PRAGMA EXCEPTION_INIT (schema_info_not_found, -20000);
64
65 -- exception to raise if unable to ge
66 txn_id_bounds_missing EXCEPTION;
67 PRAGMA EXCEPTION_INIT (txn_id_bounds_missing, -20001);
68
69 -- exception to raise if unable find NULL collection dates
70 collect_date_bounds_missing EXCEPTION;
71 PRAGMA EXCEPTION_INIT (collect_date_bounds_missing, -20002);
72
73 -- exception to raise if global parameters such as global
74 -- start date and global currency code are not available
75 global_setup_missing EXCEPTION;
76 PRAGMA EXCEPTION_INIT (global_setup_missing, -20003);
77
78
79 -- Stage failure.
80 stage_failure EXCEPTION;
81 PRAGMA EXCEPTION_INIT (stage_failure, -20004);
82
83 -- Common Module Initial Load has not been run
84 run_common_mod_init EXCEPTION;
85 PRAGMA EXCEPTION_INIT (run_common_mod_init, -20005);
86
87 /*----------------------------------------*/
88
89 /* run_common_module_init
90
91 The common Module initial load is responsible for:
92 1. Computing ETL bounds for the first time
93
94 This function does not return with an exception in case of error
95 but ends with a retcode of error. However helper functions are
96 expected to throw exceptions. We do not look at the retcode/errbuf
97 for helper functions. If a helper function fails, it is expected
98 to write a error message to the log and to throw an exception
99 back to this wrapper function.
100
101 Date Author Action
102 04/17/03 Dinkar Gupta Wrote Function
103 07/01/05 Sandeep Beri Modified Procedure for R12
104 Commom Module does not call WIP
105 Completions and Job Master after
106 its successful completion.
107 */
108
109 PROCEDURE run_common_module_init (errbuf OUT NOCOPY VARCHAR2,
110 retcode OUT NOCOPY NUMBER)
111 IS
112
113 l_proc_name VARCHAR2 (60) := 'run_common_module_init';
114 l_stmt_id NUMBER := 0;
115
116 l_global_start_date DATE := NULL;
117
118 l_bounds_warning BOOLEAN := false;
119
120 BEGIN
121
122 -- get session parameters
123 l_stmt_id := 10;
124 IF (NOT (fnd_installation.get_app_info('OPI', s_status, s_industry,
125 s_opi_schema))) THEN
126 RAISE schema_info_not_found;
127 END IF;
128
129 -- check if the global set up is good
130 l_stmt_id := 20;
131 IF (NOT (check_global_setup ())) THEN
132 RAISE global_setup_missing;
133 END IF;
134
135 -- get the DBI global start date
136 l_stmt_id := 30;
137 l_global_start_date := trunc (bis_common_parameters.get_global_start_date);
138
139 -- compute the ETL bounds for the first time.
140 l_stmt_id := 40;
141 compute_initial_etl_bounds (errbuf, retcode, l_global_start_date,
142 s_opi_schema);
143
144 -- check if stage succeeded
145 l_stmt_id := 41;
146 IF (retcode = s_ERROR) THEN
147 RAISE stage_failure;
148 END IF;
149
150 -- check if some bounds are uncosted before calling any other
151 -- procedure that can wipe out the stop reason code
152 l_stmt_id := 50;
153 l_bounds_warning := bounds_uncosted ();
154
155 -- Print the discrete org collection bounds
156 l_stmt_id := 60;
157 print_opi_org_bounds;
158
159 -- if uncosted transactions were found, return a warning.
160 l_stmt_id := 70;
161 IF (l_bounds_warning) THEN
162
163 BIS_COLLECTION_UTILITIES.PUT_LINE
164 ('Common Module Initial Load terminated with warnings.');
165 retcode := s_WARNING;
166 errbuf := 'Common Module Initial Load Found Uncosted Transactions. ';
167
168 ELSE
169 -- terminate successfully
170 BIS_COLLECTION_UTILITIES.PUT_LINE
171 ('Common Module Initial Load terminated successfully.');
172 retcode := s_SUCCESS;
173 errbuf := '';
174 END IF;
175
176 EXCEPTION
177
178 WHEN schema_info_not_found THEN
179 rollback;
180
181 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
182 l_proc_name || ' ' ||
183 '#' || l_stmt_id ||
184 ': ' || SQLERRM);
185
186 retcode := s_ERROR;
187 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
188 l_stmt_id || ': ' ||
189 'Common Module Initial Load failed to get OPI schema info.';
190 return;
191
192 WHEN global_setup_missing THEN
193 rollback;
194
195 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
196 l_proc_name || ' ' ||
197 '#' || l_stmt_id ||
198 ': ' || SQLERRM);
199
200 retcode := s_ERROR;
201 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
202 l_stmt_id || ': ' ||
203 'Common Module Initial Load could not find global setup of global start date and global currency code.';
204 return;
205
206 WHEN stage_failure THEN
207 rollback;
208
209 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
210 l_proc_name || ' ' ||
211 '#' || l_stmt_id ||
212 ': ' || SQLERRM);
213 retcode := s_ERROR;
214 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
215 l_stmt_id || ': ' ||
216 'Common Module Initial Load failed.';
217 return;
218
219 WHEN OTHERS THEN
220 rollback;
221
222 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
223 l_proc_name || ' ' ||
224 '#' || l_stmt_id ||
225 ': ' || SQLERRM);
226
227 retcode := s_ERROR;
228 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
229 l_stmt_id || ': ' || 'Common Module Initial Load Failed.';
230 return;
231
232 END run_common_module_init;
233
234
235 /* compute_initial_etl_bounds
236
237 Computing the initial bounds for the all the ETLs.
238
239 These include:
240 Job Transactions ETL - WIP Completions, Actual Usage and Scrap
241 Actual Resource Usage
242 Resource Variance
243 Job Master
244
245 The computation can be broken down into the following stages:
246
247 1. Truncate the log and audit tables, OPI_DBI_RUN_LOG_CURR and
248 OPI_DBI_RUN_LOG_AUDIT respectively.
249 2. Populate the initial data for all the different ETLs in the
250 current log table OPI_DBI_RUN_LOG_CURR.
251 3. Compute the end bounds for all the rows in the current log table,
252 OPI_DBI_RUN_LOG_CURR.
253
254 Data is committed when all steps are successful.
255
256 Parameters:
257 p_global_start_date - DBI global start date. Expecting it to be
258 trunc'ed already.
259 p_opi_schema - Schema name for OPI.
260
261 Date Author Action
262 04/17/03 Dinkar Gupta Wrote Function
263 08/14/03 Dinkar Gupta Changed the bounds computation to
264 perform only inserts for the initial load.
265 All updates removed on recommendation
266 of performance team.
267 */
268
269
270 PROCEDURE compute_initial_etl_bounds (errbuf OUT NOCOPY VARCHAR2,
271 retcode OUT NOCOPY NUMBER,
272 p_global_start_date IN DATE,
273 p_opi_schema IN VARCHAR2)
274 IS
275 l_proc_name VARCHAR2 (60) := 'compute_initial_etl_bounds';
276 l_stmt_id NUMBER := 0;
277
278
279 BEGIN
280
281 -- initial load requires clean tables.
282 l_stmt_id := 10;
283 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || p_opi_schema || '.' ||
284 'OPI_DBI_RUN_LOG_AUDIT');
285
286 l_stmt_id := 20;
287 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' || p_opi_schema || '.' ||
288 'OPI_DBI_RUN_LOG_CURR');
289
290 -- Create all the data for all the different MAnufacuturing Management
291 -- ETLs in OPI_DBI_RUN_LOG_CURR
292 l_stmt_id := 30;
293 seed_run_log_initial (errbuf, retcode, p_global_start_date);
294
295 -- success so far, then commit everything in one shot
296 l_stmt_id := 50;
297 commit;
298
299 -- terminate successfully
300 retcode := s_SUCCESS;
301 errbuf := '';
302
303 EXCEPTION
304
305 WHEN OTHERS THEN
306 rollback;
307
308 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
309 l_proc_name || ' ' ||
310 '#' || l_stmt_id ||
311 ': ' || SQLERRM);
312
313 retcode := s_ERROR;
314 errbuf := s_pkg_name || '.' || l_proc_name || ' ' ||
315 '#' || l_stmt_id || ': ' ||
316 'Failed to compute bounds for the initial load.';
317 RAISE; -- propagate exception to wrapper
318
319 END compute_initial_etl_bounds;
320
321
322 /* seed_run_log_initial
323
324 The following ETL bounds need to be computed and seeded in the
325 OPI_DBI_RUN_LOG_CURR table:
326
327 1. Job Transaction ETL (WIP Completions, Actual Usage and Scrap)
328 OPI sourced rows --
329 One row per discrete org with the start_txn_id as the first
330 transaction id in MMT after global start date.
331
332 OPM sourced rows --
333 One row with the from bound date as Global Start Date and to
334 bound date as the sysdate with date and time taken during the
335 run of this program. The organization id for thsi one row would
336 be NULL.
337
338 4. Actual Resource Usage ETL
339 OPI sourced rows --
340 One row with start_txn_id as the first transaction id
341 in WT after global start date.
342
343 OPM sourced rows --
344 One row with the from bound date as Global Start Date and to
345 bound date as the sysdate with date and time taken during the
346 run of this program. The organization id for thsi one row would
347 be NULL.
348
349 5. Resource Variance ETL
350 OPI sourced rows --
351 One row with the from bound date as Global Start Date and to
352 bound date as the sysdate with date and time taken during the
353 run of this program.
354
355 OPM sourced rows --
356 One row with the from bound date as Global Start Date and to
357 bound date as the sysdate with date and time taken during the
358 run of this program.
359
360 6. Resource Variance ETL
361 OPI sourced rows --
362 One row with the from bound date as Global Start Date and to
363 bound date as the sysdate with date and time taken during the
364 run of this program.
365
369 run of this program.
366 OPM sourced rows --
367 One row with the from bound date as Global Start Date and to
368 bound date as the sysdate with date and time taken during the
370
371
372 The goal of one centralized seeding function is to minimize the
373 number of SQLs to populate the data.
374
375 Thus for OPI sourced rows:
376 The Job Transactions ETL -Scrap, WIP Completion and Actual Usage
377 rows will be populated in the one SQL from MMT.
378
379 The Actual Resource ETL row will be populated in one SQL from WT.
380
381 For the OPM rows:
382 All OPM Rows for all ETL's would be populated in one SQL which would set
383 the from bound date as the Global Start Date and the to bound date as
384 the sysdate.
385
386 Comment on max bounds on tables (OPI):
387 Note that for all bounds, we pick max + 1. That is because the
388 the upper bound is meant to be strictly higher than the
389 id's to be collected. Since all the id sequences are discrete
390 and increasing, just adding +1 ensures that if we start next time
391 at the max + 1 of this run, then no transaction_id will be
392 ignored.
393 Also, max + 1 is not a real transaction_id and may never be.
394 Since the bounds should never be NULL, and the start id's are
395 always seeded to be 0, next_Start must be 0 for empty tables.
396
397
398 DO NOT COMMIT ANY DATA HERE!!! LEAVE THAT FOR THE CALLING FUNCTION.
399
400 Paramters:
401 p_global_start_date - DBI global start date. Expect it to be
402 trunc'ed already.
403
404 Date Author Action
405 04/17/03 Dinkar Gupta Wrote Function
406 07/01/05 Sandeep Beri Modified OPM logic of computing bounds
407 Bounds for all OPM ETL's are now date bounds.
408 Store GSD (trunc'd) as the from bound date
409 and a snapshot of the sysdate(with date time)
410 as the to bound date. OPM bounds no longer come
411 from GL_SUBR_LED.
412 */
413
414
415 PROCEDURE seed_run_log_initial (errbuf OUT NOCOPY VARCHAR2,
416 retcode OUT NOCOPY NUMBER,
417 p_global_start_date IN DATE)
418 IS
419
420 l_proc_name VARCHAR2 (60) := 'seed_run_log_initial';
421 l_stmt_id NUMBER := 0;
422
423 -- the highest transaction in MMT + 1.
424 l_max_mmt_plus_one NUMBER := NULL;
425
426 -- the first transaction in MMT past global start date
427 l_mmt_start_txn_id NUMBER := NULL;
428
429 -- the highest transaction in WT + 1.
430 l_max_wt_plus_one NUMBER := NULL;
431
432 -- the first transaction in WT past global start date
433 l_wt_start_txn_id NUMBER := NULL;
434
435 -- Snapshotting sysdate with date time in a local variable
436 l_to_bound_date DATE;
437
438 BEGIN
439
440 -- Select the max transaction id from MMT
441 -- for the transaction id bounds.
442 -- If the table is empty, then make the start at transaction id 0.
443
444 l_stmt_id := 10;
445 SELECT nvl (max (transaction_id), -1) + 1
446 INTO l_max_mmt_plus_one
447 FROM mtl_material_transactions;
448
449 -- Storing sysdate in a local variable for insertion into log as we do not want to miss any
450 -- horizon between OPM and OPI inserts.
451 l_stmt_id := 20;
452 SELECT sysdate
453 INTO l_to_bound_date
454 FROM DUAL;
455
456 -- Select the start transaction id's in MMT
457 -- past the global start date.
458 -- If there are no transactions past the global start date,
459 -- then just pick a transaction_id one higher than the max of
460 -- of MMT.
461
462 l_stmt_id := 30;
463 SELECT /*+ parallel(mtl_material_transactions) */
464 nvl (min (transaction_id), l_max_mmt_plus_one)
465 INTO l_mmt_start_txn_id
466 FROM mtl_material_transactions
467 WHERE transaction_date >= p_global_start_date;
468
469 -- Create a row for each discrete org for the Job Transactions ETL
470 --(Scrap, WIP completions, Material Usage) with the start
471 -- transaction_id set to the first transaction in MMT after the
472 -- global_start_date.
473 -- Note the last_transaction_date here, it is computed for PTP etl.
474 -- Note that this is max transaction date between the start and stop
475 -- transactions, because the last transaction itself could have been
476 -- backdated.
477
478 l_stmt_id := 50;
479 INSERT /*+ append parallel (opi_dbi_run_log_curr) */
480 INTO opi_dbi_run_log_curr (
481 organization_id,
482 source,
483 last_collection_date,
484 start_txn_id,
485 next_start_txn_id,
486 from_bound_date,
487 to_bound_date,
488 etl_id,
489 stop_reason_code,
490 last_transaction_date,
491 last_update_date,
492 creation_date,
493 last_updated_by,
494 created_by,
495 last_update_login,
496 program_id,
500 SELECT /*+ parallel (bounds) parallel (etls) */
497 program_login_id,
498 program_application_id,
499 request_id)
501 bounds.organization_id,
502 OPI_SOURCE, -- OPI rows
503 NULL,
504 bounds.start_txn_id,
505 bounds.next_start_txn_id,
506 NULL,
507 NULL,
508 JOB_TXN_ETL,
509 bounds.stop_reason_code,
510 decode (bounds.stop_reason_code,
511 STOP_UNCOSTED, bounds.last_transaction_date,
512 sysdate),
513 sysdate,
514 sysdate,
515 s_user_id,
516 s_user_id,
517 s_login_id,
518 s_program_id,
519 s_program_login_id,
520 s_program_application_id,
521 s_request_id
522 FROM
523 (SELECT /*+ parallel (mmt_bounds) parallel (mmt) */
524 mmt_bounds.organization_id,
525 mmt_bounds.start_txn_id,
526 mmt_bounds.next_start_txn_id,
527 trunc (max (mmt.transaction_date))
528 last_transaction_date,
529 mmt_bounds.stop_reason_code
530 FROM
531 (SELECT /* parallel (uncosted) parallel (orgs) */
532 orgs.organization_id,
533 l_mmt_start_txn_id start_txn_id,
534 nvl (uncosted.uncosted_id, l_max_mmt_plus_one)
535 next_start_txn_id,
536 decode (uncosted.uncosted_id,
537 NULL, STOP_ALL_COSTED,
538 STOP_UNCOSTED) stop_reason_code
539 FROM (SELECT /*+ PARALLEL (mtl_material_transactions) */
540 min (transaction_id) uncosted_id,
541 organization_id
542 FROM mtl_material_transactions
543 WHERE costed_flag IN ('N', 'E')
544 AND transaction_id > l_mmt_start_txn_id
545 GROUP BY organization_id) uncosted,
546 (SELECT /*+ parallel (mtl_parameters) */
547 organization_id
548 FROM mtl_parameters
549 WHERE process_enabled_flag <> 'Y') orgs
550 WHERE orgs.organization_id = uncosted.organization_id (+))
551 mmt_bounds,
552 mtl_material_transactions mmt
553 WHERE mmt_bounds.organization_id = mmt.organization_id (+)
554 AND (mmt.transaction_id BETWEEN mmt_bounds.start_txn_id AND
555 mmt_bounds.next_start_txn_id
556 OR mmt.transaction_id IS NULL)
557 GROUP BY
558 mmt_bounds.organization_id,
559 mmt_bounds.start_txn_id,
560 mmt_bounds.next_start_txn_id,
561 mmt_bounds.stop_reason_code) bounds;
562
563 -- commit due to insert append
564 l_stmt_id := 52;
565 commit;
566
567 -- Create a row for process for each ETL with the
568 -- from bound date set to the GSD and the to bound
569 -- date as the date time snapshot taken above in
570 -- the local variable l_to_bound_date.
571 -- Organization id would be null for such OPM rows.
572 l_stmt_id := 60;
573 INSERT INTO opi_dbi_run_log_curr (
574 organization_id,
575 source,
576 last_collection_date,
580 to_bound_date,
577 start_txn_id,
578 next_start_txn_id,
579 from_bound_date,
581 etl_id,
582 stop_reason_code,
583 last_transaction_date,
584 last_update_date,
585 creation_date,
586 last_updated_by,
587 created_by,
588 last_update_login,
589 program_id,
590 program_login_id,
591 program_application_id,
592 request_id)
593 SELECT
594 NULL,
595 OPM_SOURCE, -- OPM rows
596 NULL,
597 NULL,
598 NULL,
599 p_global_start_date,
600 l_to_bound_date,
601 etls.etl_id,
602 NULL,
603 sysdate,
604 sysdate,
605 sysdate,
606 s_user_id,
607 s_user_id,
608 s_login_id,
609 s_program_id,
610 s_program_login_id,
611 s_program_application_id,
612 s_request_id
613 FROM
614 (SELECT JOB_TXN_ETL etl_id FROM dual
615 UNION ALL
616 SELECT ACTUAL_RES_ETL FROM dual
617 UNION ALL
618 SELECT RESOURCE_VAR_ETL FROM dual
619 UNION ALL
620 SELECT JOB_MASTER_ETL FROM dual) etls;
621
622
623 -- Max bounds for the Actual Resource Utilization ETL
624 l_stmt_id := 70;
625 SELECT nvl (max (transaction_id), -1) + 1
626 INTO l_max_wt_plus_one
627 FROM wip_transactions;
628
629 -- start bound for actual resource utilization ETL
630 l_stmt_id := 90;
631 SELECT /*+ index_ffs(wip_transactions) parallel_index(wip_transactions) */
632 nvl (min (transaction_id), l_max_wt_plus_one)
633 INTO l_wt_start_txn_id
634 FROM wip_transactions
635 WHERE transaction_date >= p_global_start_date;
636
637 -- For the Actual Resource Usage create a row:
638 -- for OPI with start_txn_id as the first transaction past global
639 -- start date in WT. There cannot be any uncosted resource transactions.
640
641 l_stmt_id := 110;
642 INSERT INTO opi_dbi_run_log_curr (
643 organization_id,
644 source,
645 last_collection_date,
646 start_txn_id,
647 next_start_txn_id,
648 from_bound_date,
649 to_bound_date,
650 etl_id,
651 stop_reason_code,
652 last_transaction_date,
653 last_update_date,
654 creation_date,
655 last_updated_by,
656 created_by,
657 last_update_login,
658 program_id,
659 program_login_id,
660 program_application_id,
661 request_id)
662 SELECT
663 NULL,
664 src.source_type,
665 NULL,
666 l_wt_start_txn_id,
667 l_max_wt_plus_one,
668 NULL,
669 NULL,
670 ACTUAL_RES_ETL,
671 NULL,
672 sysdate,
673 sysdate,
674 sysdate,
675 s_user_id,
676 s_user_id,
677 s_login_id,
678 s_program_id,
679 s_program_login_id,
680 s_program_application_id,
681 s_request_id
682 FROM
683 (SELECT OPI_SOURCE source_type FROM dual
684 ) src;
685
686
687 -- For Resource and Job Master ETLs, we need from bound date
688 -- and to bound date for OPI also.
689
690 l_stmt_id := 120;
691
692 INSERT INTO opi_dbi_run_log_curr (
693 organization_id,
694 source,
695 last_collection_date,
696 start_txn_id,
697 next_start_txn_id,
698 from_bound_date,
699 to_bound_date,
703 last_update_date,
700 etl_id,
701 stop_reason_code,
702 last_transaction_date,
704 creation_date,
705 last_updated_by,
706 created_by,
707 last_update_login,
708 program_id,
709 program_login_id,
710 program_application_id,
711 request_id)
712 SELECT
713 NULL,
714 OPI_SOURCE, -- OPI rows
715 NULL,
716 NULL,
717 NULL,
718 p_global_start_date,
719 l_to_bound_date,
720 etls.etl_id,
721 NULL,
722 sysdate,
723 sysdate,
724 sysdate,
725 s_user_id,
726 s_user_id,
727 s_login_id,
728 s_program_id,
729 s_program_login_id,
730 s_program_application_id,
731 s_request_id
732 FROM
733 (SELECT RESOURCE_VAR_ETL etl_id FROM dual
734 UNION ALL
735 SELECT JOB_MASTER_ETL FROM dual) etls;
736 -- terminate successfully
737 l_stmt_id := 130;
738 retcode := s_SUCCESS;
739 errbuf := '';
740
741
742 EXCEPTION
743
744 WHEN OTHERS THEN
745 rollback;
746
747 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
748 l_proc_name || ' ' ||
749 '#' || l_stmt_id ||
750 ': ' || SQLERRM);
751
752 retcode := s_ERROR;
753 errbuf := s_pkg_name || '.' || l_proc_name || ' ' ||
754 '#' || l_stmt_id || ': ' ||
755 'Failed to seed initial data into the run log tables.';
756 RAISE; -- propagate exception to wrapper
757
758
759 END seed_run_log_initial;
760
761
762 /* init_end_bounds_setup
763
764 API called by ETLs to ensure that the bounds they are running for are
765 set up correctly.
766
767 OPI : For the Material and Actual Resource Usage ETLs,
768 This requires checking if all the next_start_txn_id values are not null
769 for the given ETL and the source.
770
771 OPM : All ETL's and OPI: Job Master and Resource Variance
772 This requires checking if all the dates voz. from_bound_date,
773 to_bound_date and last_collection_date are set up correctly or not.
774
775 Parameters:
776 p_etl_id - etl_id of ETL invoking API.
777 p_source - 1 for OPI, 2 for OPM
778
779 Return:
780 l_bounds_valid - true if the bounds are valid
781 false o.w.
782
783 Date Author Action
784 04/23/03 Dinkar Gupta Wrote Function
785 07/01/05 Sandeep Beri Modified the IF conditions to the
786 transaction id set uo check call as
787 in R12, no OPM ETL would have txn id bounds.
788 */
789 FUNCTION init_end_bounds_setup (p_etl_id IN NUMBER, p_source IN NUMBER)
790 RETURN BOOLEAN
791 IS
792
793 l_proc_name VARCHAR2 (60) := 'init_end_bounds_setup';
794 l_stmt_id NUMBER := 0;
795
796 l_exists NUMBER := NULL;
797 l_bounds_valid BOOLEAN := true;
798
799 BEGIN
800
801 -- Ensure the log table is not empty
802 l_stmt_id := 5;
803 BEGIN
804 SELECT 1
805 INTO l_exists
806 FROM dual
807 WHERE (EXISTS (SELECT source
808 FROM opi_dbi_run_log_curr
809 WHERE rownum = 1));
810
811 EXCEPTION
812 WHEN NO_DATA_FOUND THEN
813 RAISE run_common_mod_init;
814
815 END;
816
817 -- For the Job Transactions ETL
818 -- and Actual Resource Usage (OPI), the behaviour is based on transaction
819 -- id's
820 l_stmt_id := 10;
821 IF ( ((p_etl_id = JOB_TXN_ETL) OR
822 (p_etl_id = ACTUAL_RES_ETL)) AND p_source = OPI_SOURCE) THEN
823 --{
824 l_bounds_valid := txn_id_init_setup (p_etl_id, p_source);
825 --}
826
827 -- For Resource Variance and Job Master in OPI and all OPM ETL's, the behaviour is
828 -- based on dates.
829 ELSE
830 --{
831 l_bounds_valid := collect_date_init_setup (p_etl_id, p_source);
832 --}
833 END IF;
834
835
836 return l_bounds_valid;
837
838 EXCEPTION
839
840
841 WHEN run_common_mod_init THEN
842 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
843 l_proc_name || ' ' ||
844 '#' || l_stmt_id ||
845 ': ' || 'Run time bounds have not been set up. Please run the initial load request set.');
846
850
847 l_bounds_valid := false;
848 return l_bounds_valid;
849
851 WHEN OTHERS THEN
852 rollback;
853
854 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
855 l_proc_name || ' ' ||
856 '#' || l_stmt_id ||
857 ': ' || SQLERRM);
858
859 l_bounds_valid := false;
860 return l_bounds_valid;
861
862 END init_end_bounds_setup;
863
864 /* txn_id_init_setup
865
866 Ensure that all the txn_id bounds are correctly setup for
867 the initial load of the ETL with the source passed in as arguments.
868
869 Right now, this requires checking that the start_txn_id
870 and next_start_txn_id columns are non-null for all the rows of the ETL
871 and source.
872
873 Parameters:
874 p_etl_id - etl_id of ETL invoking API.
875 p_source - 1 for OPI, 2 for OPM
876
877 Return:
878 l_bounds_valid - true if the bounds are valid
879 false o.w.
880
881 Date Author Action
882 04/23/03 Dinkar Gupta Wrote Function
883 07/23/03 Dinkar Gupta Also make sure that the common
884 module log table is not empty i.e.
885 the basic data has been seeded.
886 */
887 FUNCTION txn_id_init_setup (p_etl_id IN NUMBER, p_source IN NUMBER)
888 RETURN BOOLEAN
889 IS
890
891 l_proc_name VARCHAR2 (60) := 'txn_id_init_setup';
892 l_stmt_id NUMBER := 0;
893
894 l_bounds_valid BOOLEAN := true;
895
896 l_exists NUMBER := NULL;
897
898 BEGIN
899
900 -- Ensure that all the start_txn_id's are non-null
901 l_stmt_id := 10;
902 BEGIN
903 SELECT 1
904 INTO l_exists
905 FROM dual
906 WHERE (EXISTS (SELECT start_txn_id
907 FROM opi_dbi_run_log_curr
908 WHERE start_txn_id IS NULL
909 AND source = p_source
910 AND etl_id = p_etl_id));
911
912 RAISE txn_id_bounds_missing; -- found a missing next_start_txn_id
913
914 EXCEPTION
915 WHEN NO_DATA_FOUND THEN
916 l_bounds_valid := true;
917 END;
918
919 -- Ensure that all the next_start_txn_id's are non-null
920 l_stmt_id := 20;
921 BEGIN
922 SELECT 1
923 INTO l_exists
924 FROM dual
925 WHERE (EXISTS (SELECT next_start_txn_id
926 FROM opi_dbi_run_log_curr
927 WHERE next_start_txn_id IS NULL
928 AND source = p_source
929 AND etl_id = p_etl_id));
930
931 RAISE txn_id_bounds_missing; -- found a missing start_txn_id
932
933 EXCEPTION
934 WHEN NO_DATA_FOUND THEN
935 l_bounds_valid := true;
936 END;
937
938
939 return l_bounds_valid;
940
941 EXCEPTION
942
943 WHEN txn_id_bounds_missing THEN
944 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' || l_proc_name || ' ' ||
945 '#' || l_stmt_id || ': ' ||
946 'Found missing transaction_id bounds for ETL ' ||
947 p_etl_id || ' source ' || p_source || '.');
948 l_bounds_valid := false;
949 return l_bounds_valid;
950
951 WHEN OTHERS THEN
952 rollback;
953
954 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
955 l_proc_name || ' ' ||
956 '#' || l_stmt_id ||
957 ': ' || SQLERRM);
958
959 l_bounds_valid := false;
960 return l_bounds_valid;
961
962 END txn_id_init_setup;
963
964 /* collect_date_init_setup
965
966 Ensure that all the collection date bounds are correctly setup for
967 the initial load of the ETL with the source passed in as arguments.
968
972
969 This requires checking that the last_collection_date is
970 NULL. Also it checks that the from_bound_date and the to_bound_date
971 are not null.
973 Parameters:
974 p_etl_id - etl_id of ETL invoking API.
975 p_source - 1 for OPI, 2 for OPM
976
977 Return:
978 l_bounds_valid - true if the bounds are valid
979 false o.w.
980
981 Date Author Action
982 04/23/03 Dinkar Gupta Wrote Function
983
984 */
985 FUNCTION collect_date_init_setup (p_etl_id IN NUMBER, p_source IN NUMBER)
986 RETURN BOOLEAN
987 IS
988
989 l_proc_name VARCHAR2 (60) := 'collect_date_init_setup';
990 l_stmt_id NUMBER := 0;
991
992 l_bounds_valid BOOLEAN := true;
993
994 l_exists NUMBER := NULL;
995
996 BEGIN
997
998 -- Ensure that all the last_collection_date's are null
999 l_stmt_id := 10;
1000 BEGIN
1001 SELECT 1
1002 INTO l_exists
1003 FROM opi_dbi_run_log_curr
1004 WHERE last_collection_date IS NOT NULL
1005 AND source = p_source
1006 AND etl_id = p_etl_id
1007 AND rownum = 1;
1008
1009 -- found a non-null last_collection_date
1010 RAISE collect_date_bounds_missing;
1011
1012 EXCEPTION
1013 WHEN NO_DATA_FOUND THEN
1014 l_bounds_valid := true;
1015 END;
1016
1017 -- Ensure that the from_bound_date's are non null
1018 l_stmt_id := 20;
1019 BEGIN
1020 SELECT 1
1021 INTO l_exists
1022 FROM dual
1023 WHERE (EXISTS (SELECT from_bound_date
1024 FROM opi_dbi_run_log_curr
1025 WHERE from_bound_date IS NULL
1026 AND source = p_source
1027 AND etl_id = p_etl_id));
1028
1029 RAISE collect_date_bounds_missing; -- found a null from bound date
1030
1031 EXCEPTION
1032 WHEN NO_DATA_FOUND THEN
1033 l_bounds_valid := true;
1034 END;
1035
1036 -- Ensure that the to_bound_date's are non null
1037 l_stmt_id := 30;
1038 BEGIN
1039 SELECT 1
1040 INTO l_exists
1041 FROM dual
1042 WHERE (EXISTS (SELECT to_bound_date
1043 FROM opi_dbi_run_log_curr
1044 WHERE to_bound_date IS NULL
1045 AND source = p_source
1046 AND etl_id = p_etl_id));
1047
1048 RAISE collect_date_bounds_missing; -- found a null to bound date
1049
1050 EXCEPTION
1051 WHEN NO_DATA_FOUND THEN
1052 l_bounds_valid := true;
1053 END;
1054
1055 return l_bounds_valid;
1056
1057 EXCEPTION
1058
1059 WHEN collect_date_bounds_missing THEN
1060 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1061 l_proc_name || ' ' ||
1062 '#' || l_stmt_id || ': ' ||
1063 'Found missing dates for ETL ' ||
1064 p_etl_id || ' source ' ||
1065 p_source ||
1066 ' before initial load was run.');
1067 l_bounds_valid := false;
1068 return l_bounds_valid;
1069
1070 WHEN OTHERS THEN
1071 rollback;
1072
1073 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1074 l_proc_name || ' ' ||
1075 '#' || l_stmt_id ||
1076 ': ' || SQLERRM);
1077
1078 l_bounds_valid := false;
1079 return l_bounds_valid;
1080
1081 END collect_date_init_setup;
1082
1083
1084 /* run_initial_load
1085
1086 API for ETL initial loads to that they are mean to run and not the
1087 incremental loads.
1088
1089 The initial load of an ETL should call this which when it returns true
1090 indicates that it is time to run the initial load and when it returns
1091 false indicates that it is time to run the incremental load
1092 */
1093 FUNCTION run_initial_load (p_etl_id IN NUMBER, p_source IN NUMBER)
1094 RETURN BOOLEAN
1095 IS
1096
1097 l_proc_name VARCHAR2 (60) := 'run_initial_load';
1098 l_stmt_id NUMBER := 0;
1099
1100 l_run_init BOOLEAN := false;
1101 l_num_non_init_rows NUMBER := -1;
1102
1103 BEGIN
1104
1105 -- All that needs to be done is to ensure that the last collection
1106 -- date is NULL everywhere. If any row does not match is condition, then
1107 -- the initial load cannot be run.
1108 l_stmt_id := 10;
1109 SELECT sum (1)
1110 INTO l_num_non_init_rows
1111 FROM opi_dbi_run_log_curr
1112 WHERE source = p_source
1113 AND etl_id = p_etl_id
1114 AND last_collection_date IS NOT NULL;
1115
1116
1117 l_stmt_id := 20;
1118 IF (l_num_non_init_rows IS NULL) THEN
1119 l_run_init := true;
1120 ELSE
1121 l_run_init := false;
1122 BIS_COLLECTION_UTILITIES.PUT_LINE ('The initial load of this concurrent program cannot be run independently. Please run the initial load request set if it has not already been run successfully.');
1123 BIS_COLLECTION_UTILITIES.PUT_LINE (' Alternatively, run the incremental load request set if the initial load request set has already run.');
1124
1125 END IF;
1126
1127 l_stmt_id := 30;
1128 return l_run_init;
1129
1130 EXCEPTION
1134
1131
1132 WHEN OTHERS THEN
1133 rollback;
1135 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1136 l_proc_name || ' ' ||
1137 '#' || l_stmt_id ||
1138 ': ' || SQLERRM);
1139
1140 l_run_init := false;
1141 return l_run_init;
1142
1143 END run_initial_load;
1144
1145
1146 /* check_global_setup
1147
1148 Checks to see if basic global parameters are set up.
1149 Currently these include the:
1150 1. Global start date
1151 2. Global currency code
1152
1153 Parameters: None
1154
1155 Date Author Action
1156 04/23/03 Dinkar Gupta Wrote Function
1157 */
1158 FUNCTION check_global_setup
1159 RETURN BOOLEAN
1160 IS
1161 l_proc_name VARCHAR2 (60) := 'check_global_setup';
1162 l_stmt_id NUMBER := 0;
1163
1164 l_setup_good BOOLEAN := false;
1165
1166 l_list dbms_sql.varchar2_table;
1167
1168 BEGIN
1169
1170 -- Parameters we want to check for
1171 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
1172 l_list(2) := 'BIS_GLOBAL_START_DATE';
1173
1174 l_setup_good := bis_common_parameters.check_global_parameters(l_list);
1175 return l_setup_good;
1176
1177 EXCEPTION
1178
1179 WHEN OTHERS THEN
1180 rollback;
1181
1182 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1183 l_proc_name || ' ' ||
1184 '#' || l_stmt_id ||
1185 ': ' || SQLERRM);
1186
1187 l_setup_good := false;
1188 return l_setup_good;
1189
1190 END check_global_setup;
1191
1192
1193 /* bounds_uncosted
1194
1195 Return true if some rows have bounds that show uncosted transactions.
1196 This can only happen for OPI sourced Material ETLs.
1197
1198 Such rows will be distinguished by the fact that their stop reason
1199 code will be STOP_UNCOSTED. This means that the stop reason code
1200 must not have been wiped out by the etl_report_success API
1201
1202 Date Author Action
1203 04/23/03 Dinkar Gupta Wrote Function
1204
1205 */
1206 FUNCTION bounds_uncosted
1207 RETURN BOOLEAN
1208 IS
1209
1210 l_proc_name VARCHAR2 (60) := 'bounds_uncosted';
1211 l_stmt_id NUMBER := 0;
1212 l_bounds_uncosted BOOLEAN := false;
1213 l_warning NUMBER := s_SUCCESS;
1214
1215 BEGIN
1216
1217 -- check if any row has uncosted transactions
1218 l_stmt_id := 10;
1219 BEGIN
1220 SELECT s_WARNING
1221 INTO l_warning
1222 FROM OPI_DBI_RUN_LOG_CURR
1223 WHERE stop_reason_code = STOP_UNCOSTED
1224 AND rownum = 1;
1225 EXCEPTION
1226 WHEN NO_DATA_FOUND THEN
1227 l_warning := s_SUCCESS;
1228 END;
1229
1230 -- If there are uncosted transactions, return true
1231 l_stmt_id := 20;
1232 IF (l_warning = s_WARNING) THEN
1233 l_bounds_uncosted := true;
1234 END IF;
1235
1236 RETURN l_bounds_uncosted;
1237
1238 END bounds_uncosted;
1239
1240
1241 /* print_opi_org_bounds
1242
1243 Print the MMT bounds before which the OPI discrete orgs stopped, and the
1244 reason for stopping
1245 */
1246 PROCEDURE print_opi_org_bounds
1247 IS
1248
1249 l_proc_name VARCHAR2 (60) := 'bounds_uncosted';
1250 l_stmt_id NUMBER := 0;
1251
1252 -- Cursor for all the org bounds
1253 CURSOR opi_org_bounds_csr IS
1254 SELECT /*+ index(log, OPI_DBI_RUN_LOG_CURR_N1) use_nl(log mp)*/
1255 mp.organization_code,
1256 log.next_start_txn_id,
1257 decode (log.stop_reason_code,
1258 STOP_ALL_COSTED, 'All Costed',
1259 STOP_UNCOSTED, 'Uncosted',
1260 'Data Issue?') stop_reason,
1261 nvl (mmt.transaction_date, sysdate) data_until
1262 FROM opi_dbi_run_log_curr log,
1263 mtl_parameters mp,
1264 mtl_material_transactions mmt
1265 WHERE source = OPI_SOURCE
1266 AND etl_id = JOB_TXN_ETL -- any ETL is good enough
1267 AND log.next_start_txn_id = mmt.transaction_id (+)
1268 AND log.organization_id = mp.organization_id;
1269 BEGIN
1270
1271 -- print the header
1272 l_stmt_id := 10;
1273
1274 BIS_COLLECTION_UTILITIES.PUT_LINE (
1275 RPAD ('Organization Code', 20) ||
1276 RPAD ('Txn Id Stopped Before', 25) ||
1277 RPAD ('Data Collected Until', 25) ||
1278 RPAD ('Reason Stopped', 20));
1279
1280
1281 BIS_COLLECTION_UTILITIES.PUT_LINE (
1282 RPAD ('-----------------', 20) ||
1283 RPAD ('---------------------', 25) ||
1284 RPAD ('--------------------', 25) ||
1285 RPAD ('--------------', 20));
1286
1287
1288 -- just print all the bounds
1289 l_stmt_id := 20;
1290 FOR opi_org_bounds_rec IN opi_org_bounds_csr
1291 LOOP
1292
1293 BIS_COLLECTION_UTILITIES.PUT_LINE (
1294 RPAD (opi_org_bounds_rec.organization_code, 20) ||
1295 RPAD (opi_org_bounds_rec.next_start_txn_id, 25) ||
1296 RPAD (opi_org_bounds_rec.data_until, 25) ||
1297 RPAD (opi_org_bounds_rec.stop_reason, 20));
1298
1299 END LOOP;
1300
1301
1305
1302 -- print table end
1303 l_stmt_id := 30;
1304 BIS_COLLECTION_UTILITIES.PUT_LINE (LPAD ('', 90, '-'));
1306 RETURN;
1307
1308 EXCEPTION
1309
1310 WHEN OTHERS THEN
1311 rollback;
1312
1313 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1314 l_proc_name || ' ' ||
1315 'Error when printing org bounds.');
1316
1317 RAISE; -- propagate exception to wrapper
1318
1319
1320 END print_opi_org_bounds;
1321
1322
1323 END opi_dbi_common_mod_init_pkg;
[Home] [Help]