1 PACKAGE BODY opi_dbi_common_mod_incr_pkg AS
2 /*$Header: OPIDCMODRB.pls 120.2 2005/08/16 01:36:32 sberi noship $ */
3
4 /*++++++++++++++++++++++++++++++++++++++++*/
5 /* Function and procedure declarations in this file but not in spec*/
6 /*++++++++++++++++++++++++++++++++++++++++*/
7
8 PROCEDURE compute_incr_etl_bounds (errbuf OUT NOCOPY VARCHAR2,
9 retcode OUT NOCOPY NUMBER);
10
11 PROCEDURE compute_incr_end_bounds (errbuf OUT NOCOPY VARCHAR2,
12 retcode OUT NOCOPY NUMBER);
13
14 FUNCTION txn_id_success (p_etl_id IN NUMBER, p_source IN NUMBER,
15 p_completion_date IN DATE)
16 RETURN BOOLEAN;
17
18 FUNCTION collect_date_success (p_etl_id IN NUMBER, p_source IN NUMBER,
19 p_completion_date IN DATE)
20 RETURN BOOLEAN;
21
22
23 FUNCTION txn_id_incr_setup (p_etl_id IN NUMBER, p_source IN NUMBER)
24 RETURN BOOLEAN;
25
26 FUNCTION collect_date_incr_setup (p_etl_id IN NUMBER, p_source IN NUMBER)
27 RETURN BOOLEAN;
28
29 /*----------------------------------------*/
30
31
32 /*++++++++++++++++++++++++++++++++++++++++*/
33 /* PACKAGE LEVEL CONSTANTS */
34 /*++++++++++++++++++++++++++++++++++++++++*/
35
36 s_pkg_name CONSTANT VARCHAR2 (50) := 'opi_dbi_common_mod_incr_pkg';
37 s_ERROR CONSTANT NUMBER := -1; -- concurrent manager error code
38 s_WARNING CONSTANT NUMBER := 1; -- concurrent manager warning code
39 s_SUCCESS CONSTANT NUMBER := 0; -- concurrent manager success code
40
41 /* All DBI ETLs have a numeric ETL ID for identification. The ETL
42 functionality to etl_id mapping is defined as: */
43
44 /* Get these constants from the init load package, but rewrite them
45 so they dont have to be invoked with package name.
46 */
47
48 -- Job Transactions Staging
49 JOB_TXN_ETL CONSTANT NUMBER :=
50 opi_dbi_common_mod_init_pkg.JOB_TXN_ETL;
51
52 -- Actual Resource Usage
53 ACTUAL_RES_ETL CONSTANT NUMBER :=
54 opi_dbi_common_mod_init_pkg.ACTUAL_RES_ETL;
55
56 -- Resource Variance
57 RESOURCE_VAR_ETL CONSTANT NUMBER :=
58 opi_dbi_common_mod_init_pkg.RESOURCE_VAR_ETL;
59
60 -- Job Master
61 JOB_MASTER_ETL CONSTANT NUMBER :=
62 opi_dbi_common_mod_init_pkg.JOB_MASTER_ETL;
63
64 /* All ETLs can have one of two sources: */
65 OPI_SOURCE CONSTANT NUMBER :=
66 opi_dbi_common_mod_init_pkg.OPI_SOURCE;
67
68 OPM_SOURCE CONSTANT NUMBER :=
69 opi_dbi_common_mod_init_pkg.OPM_SOURCE;
70
71 /* ETLs can have to stop for multiple reasons. The stop reason
72 codes are defined as follows: */
73 STOP_UNCOSTED CONSTANT NUMBER :=
74 opi_dbi_common_mod_init_pkg.STOP_UNCOSTED;
75
76 STOP_ALL_COSTED CONSTANT NUMBER :=
77 opi_dbi_common_mod_init_pkg.STOP_ALL_COSTED;
78
79 /*----------------------------------------*/
80
81
82 /*++++++++++++++++++++++++++++++++++++++++*/
83 /* Package level variables for session info-
84 including schema name for truncating and
85 collecting stats */
86 /*++++++++++++++++++++++++++++++++++++++++*/
87
88 s_opi_schema VARCHAR2(30);
89 s_status VARCHAR2(30);
90 s_industry VARCHAR2(30);
91
92 /*----------------------------------------*/
93
94 /*++++++++++++++++++++++++++++++++++++++++*/
95 /* Package level variables for the logged
96 in user.
97 /*++++++++++++++++++++++++++++++++++++++++*/
98
99 s_user_id NUMBER := nvl(fnd_global.user_id, -1);
100 s_login_id NUMBER := nvl(fnd_global.login_id, -1);
101 s_program_id NUMBER:= nvl (fnd_global.conc_program_id, -1);
102 s_program_login_id NUMBER := nvl (fnd_global.conc_login_id, -1);
103 s_program_application_id NUMBER := nvl (fnd_global.prog_appl_id, -1);
104 s_request_id NUMBER := nvl (fnd_global.conc_request_id, -1);
105
106 /*----------------------------------------*/
107
108 /*++++++++++++++++++++++++++++++++++++++++*/
109 /* Package level exceptions defined for
110 clearer error handling. */
111 /*++++++++++++++++++++++++++++++++++++++++*/
112
113 -- exception to raise if unable to get schema information
114 schema_info_not_found EXCEPTION;
115 PRAGMA EXCEPTION_INIT (schema_info_not_found, -20000);
116
117 -- exception to raise if unable to ge
118 txn_id_bounds_missing EXCEPTION;
119 PRAGMA EXCEPTION_INIT (txn_id_bounds_missing, -20001);
120
121 -- exception to raise if unable find NULL collection dates
122 collect_date_bounds_missing EXCEPTION;
123 PRAGMA EXCEPTION_INIT (collect_date_bounds_missing, -20002);
124
125 -- exception to raise if global parameters such as global
126 -- start date and global currency code are not available
127 global_setup_missing EXCEPTION;
128 PRAGMA EXCEPTION_INIT (global_setup_missing, -20003);
129
130 -- Stage failure.
131 stage_failure EXCEPTION;
132 PRAGMA EXCEPTION_INIT (stage_failure, -20004);
133
134 -- Common Module Initial Load has not been run
135 run_common_mod_init EXCEPTION;
136 PRAGMA EXCEPTION_INIT (run_common_mod_init, -20005);
137
138 -- Found a null global start date
139 global_start_date_null EXCEPTION;
140 PRAGMA EXCEPTION_INIT (global_start_date_null, -20006);
141
142 /*----------------------------------------*/
143
144 /* run_common_module_incr
145
146
147 This function does not return with an exception in case of error
148 but ends with a retcode of error. However helper functions are
149 expected to throw exceptions. We do not look at the retcode/errbuf
150 for helper functions. If a helper function fails, it is expected
151 to write a error message to the log and to throw an exception
152 back to this wrapper function.
153
154 Date Author Action
155 04/21/03 Dinkar Gupta Wrote Function
156 07/01/05 Sandeep Beri Modified Procedure for R12
157 Commom Module does not call WIP
158 Completions and Job Master after
159 its successful completion.
160 */
161
162 PROCEDURE run_common_module_incr (errbuf OUT NOCOPY VARCHAR2,
163 retcode OUT NOCOPY NUMBER)
164 IS
165
166 l_proc_name VARCHAR2 (60) := 'run_common_module_incr';
167 l_stmt_id NUMBER := 0;
168 l_bounds_warning BOOLEAN := false;
169
170 l_run_log_size NUMBER := 0;
171 BEGIN
172
173 execute immediate 'alter session set events ''10046 trace name context forever, level 8''';
174
175 -- ensure that the initial common module has been run once
176 l_stmt_id := 5;
177 BEGIN
178 SELECT 1
179 INTO l_run_log_size
180 FROM opi_dbi_run_log_curr
181 WHERE rownum = 1;
182 EXCEPTION
183 WHEN NO_DATA_FOUND THEN
184 RAISE run_common_mod_init;
185 END;
186
187
188 -- get session parameters
189 l_stmt_id := 10;
190 IF (NOT (fnd_installation.get_app_info('OPI', s_status, s_industry,
191 s_opi_schema))) THEN
192 RAISE schema_info_not_found;
193 END IF;
194
195 -- check if the global set up is good
196 -- Use the initial load package
197 l_stmt_id := 20;
198 IF (NOT (opi_dbi_common_mod_init_pkg.check_global_setup ())) THEN
199 RAISE global_setup_missing;
200 END IF;
201
202
203
204 -- compute the incremental bounds for the ETLs as needed
205 l_stmt_id := 30;
206 compute_incr_etl_bounds (errbuf, retcode);
207
208 -- check if stage succeeded
209 l_stmt_id := 31;
210 IF (retcode = s_ERROR) THEN
211 RAISE stage_failure;
212 END IF;
213
214 -- check if some bounds are uncosted before calling any other
215 -- procedure that can wipe out the stop reason code
216 l_stmt_id := 40;
217 l_bounds_warning := opi_dbi_common_mod_init_pkg.bounds_uncosted ();
218
219
220 -- Print the discrete org collection bounds
221 l_stmt_id := 50;
222 opi_dbi_common_mod_init_pkg.print_opi_org_bounds;
223
224 -- if uncosted transactions were found, return a warning.
225 l_stmt_id := 80;
226 IF (l_bounds_warning) THEN
227
228 BIS_COLLECTION_UTILITIES.PUT_LINE
229 ('Common Module Incremental Load terminated with warnings.');
230 retcode := s_WARNING;
231 errbuf := 'Common Module Incremental Load Found Uncosted Transactions.';
232 ELSE
233 -- terminate successfully
234 BIS_COLLECTION_UTILITIES.PUT_LINE
235 ('Common Module Incremental Load terminated successfully.');
236 retcode := s_SUCCESS;
237 errbuf := '';
238 END IF;
239
240 EXCEPTION
241
242 WHEN run_common_mod_init THEN
243 rollback;
244
245 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
246 l_proc_name || ' ' ||
247 '#' || l_stmt_id ||
248 ': ' ||
249 'Manufacturing Page common module Initial load has not been run. Please run the initial load (Initial Load - Update Job Details Base Summary) first.');
250
251 retcode := s_ERROR;
252 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
253 l_stmt_id || ': ' ||
254 'Manufacturing Page common module Initial load has not been run. Please run the initial load (Initial Load - Update Job Details Base Summary) first.';
255 return;
256
257
258 WHEN schema_info_not_found THEN
259 rollback;
260
261 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' || l_proc_name || ' ' ||
262 '#' || l_stmt_id || ': ' || SQLERRM);
263
264 retcode := s_ERROR;
265 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
266 l_stmt_id || ': ' ||
267 'Common Module Incremental Load failed to get OPI schema info.';
268 return;
269
270 WHEN global_setup_missing THEN
271 rollback;
272
273 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
274 l_proc_name || ' ' ||
275 '#' || l_stmt_id ||
276 ': ' || SQLERRM);
277
278 retcode := s_ERROR;
279 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
280 l_stmt_id || ': ' ||
281 'Common Module Incremental Load could not find global setup of global start date and global currency code.';
282 return;
283
284 WHEN stage_failure THEN
285 rollback;
286
287 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
288 l_proc_name || ' ' ||
289 '#' || l_stmt_id ||
290 ': ' || SQLERRM);
291 retcode := s_ERROR;
292 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
293 l_stmt_id || ': ' ||
294 'Common Module Incremental Load failed.';
295 return;
296
297
298 WHEN OTHERS THEN
299 rollback;
300
301 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
302 l_proc_name || ' ' ||
303 '#' || l_stmt_id ||
304 ': ' || SQLERRM);
305
306 retcode := s_ERROR;
307 errbuf := s_pkg_name || '.' || l_proc_name || ' ' || '#' ||
308 l_stmt_id || ': ' ||
309 'Common Module Incremental Load Failed.';
310 return;
311
312 END run_common_module_incr;
313
314
315 /* compute_incr_etl_bounds
316
317 Computing the incremental bounds for the Job Transactions ETL and Actual resource usage ETL
318
319 Job Transaction ETL includes:
320 WIP Completions
321 Scrap
322 Material Usage Variance
323
324
325 Compute the end bounds for all the rows corresponding to these ETLs
326 in the current log table, OPI_DBI_RUN_LOG_CURR
327
328 Data is committed at the end of this procedure but not in any
329 of it's helper routines.
330
331 Parameters:
332 None.
333
334 Date Author Action
335 04/21/03 Dinkar Gupta Wrote Function
336 */
337
338
339 PROCEDURE compute_incr_etl_bounds (errbuf OUT NOCOPY VARCHAR2,
340 retcode OUT NOCOPY NUMBER)
341 IS
342 l_proc_name VARCHAR2 (60) := 'compute_incr_etl_bounds';
343 l_stmt_id NUMBER := 0;
344
345 BEGIN
346
347 -- compute the termination bounds for the different ETLs as needed
348 l_stmt_id := 10;
349 compute_incr_end_bounds (errbuf, retcode);
350
351 -- success so far, then commit everything in one shot
352 l_stmt_id := 20;
353 commit;
354
355 -- terminate successfully
356 retcode := s_SUCCESS;
357 errbuf := '';
358
359 EXCEPTION
360
361 WHEN OTHERS THEN
362 rollback;
363
364 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
365 l_proc_name || ' ' ||
366 '#' || l_stmt_id ||
367 ': ' || SQLERRM);
368
369 retcode := s_ERROR;
370 errbuf := s_pkg_name || '.' || l_proc_name || ' ' ||
371 '#' || l_stmt_id || ': ' ||
372 'Failed to compute bounds for the initial load.';
373 RAISE; -- propagate exception to wrapper
374
375 END compute_incr_etl_bounds;
376
377 /* compute_incr_end_bounds
378
379 Termination bounds need to be computed for:
380 Job Transactions ETL which includes the following
381 WIP Completions
382 Scrap
383 Material Usage
384 Actual Resource Variance.
385
386 OPI Sourced rows:
387 For Job Transaction ETL:
388 Every row either gets the first uncosted transaction for
389 that organization_id in MMT, or 1 past the highest row
390 in MMT. Do this in 1 SQL.
391 For Actual Resource Usage:
392 Pick the highest transaction_id + 1 in WT.
393
394 OPM Sourced Rows:
395 All OPM Rows for all ETL's would be populated in one SQL which
396 would set the to bound date as the sysdate snapshot. From bound date
397 would be the to bound date of the previous initial/incremental run.
398
399 -- Commit after computing the temp bounds and then the final bounds
400
401 The choice of +1 over the highest transaction_id's is to ensure
402 that the upper bound is strictly higher than all transactions to be
403 collected.
404
405 For the initial load, we need not worry about finding newly defined orgs
406 in MTL_PARAMETERS for the discrete org rows of the Material transactions,
407 because the data was seeded a few minutes ago.
408
409 Date Author Action
410 04/21/03 Dinkar Gupta Wrote Function
411 07/01/05 Sandeep Beri Modified OPM bound logic for R12. All
412 OPM ETL bounds and OPI resource variance and
413 job master bounds would be date based.
414
415 */
416
417 PROCEDURE compute_incr_end_bounds (errbuf OUT NOCOPY VARCHAR2,
418 retcode OUT NOCOPY NUMBER)
419 IS
420 l_proc_name VARCHAR2 (60) := 'compute_incr_end_bounds';
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 highest transaction in WT + 1.
427 l_max_wt_plus_one NUMBER := NULL;
428
429 -- the lowest starting transaction_id for OPI orgs doing
430 -- WIP completions, Scrap or Material Usage variance (Job Activity ETL)
431 l_min_start_id_opi_orgs NUMBER := NULL;
432
433 -- global start date
434 l_global_start_date DATE := NULL;
435
436 -- Date Type Variable to hold the sysdate for upper bound
437 l_to_bound_date DATE;
438
439 BEGIN
440
441 -- All the following SQLs select the max transaction_id's from
442 -- certain transaction tables. These id columns are primary keys
443 -- and unique indexes on these tables. Therefore these statements
444 -- are not full table scans but min-max index scans.
445
446 -- Note that for all bounds, we pick max + 1. That is because the
447 -- the upper bound is meant to be strictly higher than the
448 -- id's to be collected. Since all the id sequences are discrete
449 -- and increasing, just adding +1 ensures that if we start next time
450 -- at the max + 1 of this run, then no transaction_id will be
451 -- ignored.
452 -- Also, max + 1 is not a real transaction_id and may never be.
453 --
454 -- For empty tables, we are keeping the start and end at 0.
455
456 /* Truncate temp table */
457 l_stmt_id := 5;
458 EXECUTE IMMEDIATE ('TRUNCATE TABLE ' ||
459 s_opi_schema || '.opi_dbi_run_log_curr_tmp');
460
461 l_stmt_id := 10;
462 SELECT nvl (max (transaction_id), -1) + 1
463 INTO l_max_mmt_plus_one
464 FROM mtl_material_transactions;
465
466 l_stmt_id := 30;
467 SELECT nvl (max (transaction_id), -1) + 1
468 INTO l_max_wt_plus_one
469 FROM wip_transactions;
470
471 -- Storing sysdate in a local variable for updating the log as we do not want to miss any
475 INTO l_to_bound_date
472 -- horizon between OPM and OPI updates.
473 l_stmt_id := 35;
474 SELECT sysdate
476 FROM DUAL;
477
478 -- It is possible that a new discrete org was defined in
479 -- MTL_PARAMETERS since the last incremental run. This org must
480 -- get a record in current log table, OPI_DBI_RUN_LOG_CURR so that
481 -- it can be picked up in subsequent extractions. This need only be
482 -- done for OPI orgs.
483
484 -- The new org can start with least of the starting id's because
485 -- that is a lower bound to the transaction not collected yet.
486 -- This SELECT .. INTO .. need not be put in a BEGIN .. EXCEPTION
487 -- block because we never expected a NULL value here in the
488 -- incremental load.
489 l_stmt_id := 48;
490 SELECT min (start_txn_id)
491 INTO l_min_start_id_opi_orgs
492 FROM opi_dbi_run_log_curr
493 WHERE source = OPI_SOURCE
494 AND etl_id = JOB_TXN_ETL;
495
496 -- Get the global start date which will be used as the last collection
497 -- date for the new orgs. This is important because the run_incr_bounds
498 -- API checks to make sure that all orgs have non-null
499 -- last_collection_date before running the incremental load.
500 l_stmt_id := 45;
501 l_global_start_date := trunc (bis_common_parameters.get_global_start_date);
502 IF (l_global_start_date IS NULL) THEN
503 RAISE global_start_date_null;
504 END IF;
505
506
507 -- Note that all distinct orgs in OPI_DBI_RUN_LOG_CURR can be queried
508 -- by looking at all the orgs for Material ETL with OPI source
509 -- (we look at the JOBH_TXN_ETL below). This is because
510 -- we have exactly one row per discrete org for the Job Txn ETL
511 -- Note that we are making the last_collection_date for new orgs
512 -- to be the global start date so that the run_incr_bounds API
513 -- performs correctly.
514 l_stmt_id := 50;
515 INSERT INTO opi_dbi_run_log_curr (
516 organization_id,
517 source,
518 last_collection_date,
519 start_txn_id,
520 next_start_txn_id,
521 from_bound_date,
522 to_bound_date,
523 etl_id,
524 last_update_date,
525 creation_date,
526 last_updated_by,
527 created_by,
528 last_update_login,
529 program_id,
530 program_login_id,
531 program_application_id,
532 request_id)
533 SELECT new_orgs.organization_id,
534 OPI_SOURCE,
535 l_global_start_date, -- never collected yet
536 l_min_start_id_opi_orgs, -- least collected transaction id
537 NULL, -- no next_start_txn_id yet
538 NULL,
539 NULL,
540 etls.etl_id, -- All material ETLs
541 sysdate,
542 sysdate,
543 s_user_id,
544 s_user_id,
545 s_login_id,
546 s_program_id,
547 s_program_login_id,
548 s_program_application_id,
549 s_request_id
550 FROM (SELECT JOB_TXN_ETL etl_id FROM dual
551 ) etls,
552 (SELECT organization_id
553 FROM mtl_parameters
554 WHERE process_enabled_flag <> 'Y' -- not OPM org
555 MINUS
556 SELECT organization_id -- all distinct orgs
557 FROM opi_dbi_run_log_curr
558 WHERE etl_id = JOB_TXN_ETL
559 AND source = OPI_SOURCE) new_orgs;
560
561 -- For Job Transactions ETL the OPI sourced
562 -- rows either have a next_start_txn_id as the first
563 -- uncosted transaction in MMT or the max + 1 of mmt.
564 -- The uncosted transaction for has be to past the global start date,
565 -- and at the moment in the initial load, every org has a start_txn_id
566 -- So we can use this transaction_id limit the scan on MMT.
567 -- Do not update the last transaction date here. That should only
568 -- be done when the ETL reports success using the ETL report success
569 -- API.
570 -- Set the last transaction_date to sysdate and then
571 -- update it for the OPI sourced MMT ETLs
572
573 l_stmt_id := 60;
574 INSERT /*+ append */
575 INTO opi_dbi_run_log_curr_tmp
576 (ORGANIZATION_ID,
577 ETL_ID, SOURCE,
578 NEXT_START_TXN_ID,
579 STOP_REASON_CODE,
580 LAST_TRANSACTION_DATE)
581 SELECT /*+ use_nl(curr mmt_bounds) */
582 curr_log.organization_id,
583 curr_log.etl_id,
584 curr_log.source,
585 nvl (mmt_bounds.next_start_txn_id,
586 l_max_mmt_plus_one) next_start_txn_id,
587 decode (mmt_bounds.next_start_txn_id,
588 NULL, STOP_ALL_COSTED,
589 STOP_UNCOSTED) stop_reason_code,
590 decode (mmt_bounds.next_start_txn_id,
591 NULL, sysdate,
592 mmt_bounds.last_transaction_date) last_transaction_date
593 FROM
594 (SELECT /*+ use_nl(uncosted mmt1)
595 index(mmt1, MTL_MATERIAL_TRANSACTIONS_U1) */
596 uncosted.organization_id,
597 uncosted.etl_id,
598 uncosted.source,
602 leading(log) use_nl(log mmt) */
599 uncosted.uncosted_id next_start_txn_id,
600 max (mmt1.transaction_date) last_transaction_date
601 FROM (SELECT /*+ index(log, OPI_DBI_RUN_LOG_CURR_N1)
603 min (mmt.transaction_id) uncosted_id,
604 log.organization_id,
605 log.etl_id,
606 log.source,
607 log.start_txn_id
608 FROM mtl_material_transactions mmt,
609 (SELECT organization_id,
610 etl_id,
611 source,
612 start_txn_id
613 FROM opi_dbi_run_log_curr
614 WHERE source = OPI_SOURCE
615 AND etl_id = JOB_TXN_ETL) log
616 WHERE mmt.costed_flag IN ('N', 'E')
617 AND mmt.transaction_id >= log.start_txn_id
618 AND mmt.organization_id = log.organization_id
619 GROUP BY
620 log.organization_id,
621 log.etl_id,
622 log.source,
623 log.start_txn_id) uncosted,
624 mtl_material_transactions mmt1
625 WHERE mmt1.organization_id+0 = uncosted.organization_id
626 AND mmt1.transaction_id BETWEEN uncosted.start_txn_id
627 AND uncosted.uncosted_id
628 GROUP BY
629 uncosted.organization_id,
630 uncosted.etl_id,
631 uncosted.source,
632 uncosted.uncosted_id) mmt_bounds,
633 (SELECT organization_id, etl_id, source, start_txn_id
634 FROM opi_dbi_run_log_curr
635 WHERE source = OPI_SOURCE
636 AND etl_id = JOB_TXN_ETL) curr_log
637 WHERE curr_log.organization_id = mmt_bounds.organization_id (+)
638 AND curr_log.etl_id = mmt_bounds.etl_id (+)
639 AND curr_log.source = mmt_bounds.source (+);
640
641 -- commit the temp table
642 l_stmt_id := 62;
643 commit;
644
645 l_stmt_id := 65;
646
647 UPDATE /*+ index(opi_curr_log, opi_dbi_run_log_curr_n1) */
648 opi_dbi_run_log_curr opi_curr_log
649 SET last_update_date = sysdate,
650 (next_start_txn_id, stop_reason_code, last_transaction_date) =
651 (SELECT next_start_txn_id,
652 stop_reason_code,
653 last_transaction_date
654 FROM opi_dbi_run_log_curr_tmp bounds
655 WHERE bounds.organization_id = opi_curr_log.organization_id
656 AND bounds.etl_id = opi_curr_log.etl_id
657 AND bounds.source = opi_curr_log.source)
658 WHERE opi_curr_log.source = OPI_SOURCE
659 AND opi_curr_log.etl_id = JOB_TXN_ETL;
660
661
662 -- For all OPM ETL's, we would have to set the to_bound_date as the sysdate.
663 -- By default, everything is costed
664 l_stmt_id := 70;
665 UPDATE opi_dbi_run_log_curr log
666 SET last_update_date = sysdate,
667 last_transaction_date = sysdate,
668 to_bound_date = l_to_bound_date
669 WHERE log.source = OPM_SOURCE;
670
671 -- For Actual Resource Usage the next_start_txn_id for:
672 -- 1. OPI sourced row needs max + 1 from WT.
673 -- By default, everything is costed.
674 l_stmt_id := 80;
675 UPDATE opi_dbi_run_log_curr log
676 SET last_update_date = sysdate,
677 last_transaction_date = sysdate,
678 next_start_txn_id = l_max_wt_plus_one
679 WHERE log.source = OPI_SOURCE
680 AND log.etl_id = ACTUAL_RES_ETL;
681
682 -- For OPI Job Master and Resource Availibility ETL's, we would have to set the to_bound_date as the sysdate.
683 l_stmt_id := 90;
684 UPDATE opi_dbi_run_log_curr log
685 SET last_update_date = sysdate,
686 last_transaction_date = sysdate,
687 to_bound_date = l_to_bound_date
688 WHERE log.source = OPI_SOURCE
689 AND log.etl_id IN (RESOURCE_VAR_ETL, JOB_MASTER_ETL);
690
691 -- commit all the computed bounds
692 l_stmt_id := 90;
693 commit;
694
695 -- terminate successfully
696 retcode := s_SUCCESS;
697 errbuf := '';
698
699 EXCEPTION
700
701 WHEN global_start_date_null THEN
702 rollback;
703
704 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
705 l_proc_name || ' ' ||
706 '#' || l_stmt_id ||
707 ': ' ||
708 'Global Start date was null');
709
710 retcode := s_ERROR;
711 errbuf := s_pkg_name || '.' || l_proc_name || ' ' ||
712 '#' || l_stmt_id || ': ' ||
713 'Failed to compute incremental termination bounds because global start date is null.';
714 RAISE; -- propagate exception to wrapper
715
716
717 WHEN OTHERS THEN
718 rollback;
719
723 ': ' || SQLERRM);
720 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
721 l_proc_name || ' ' ||
722 '#' || l_stmt_id ||
724
725 retcode := s_ERROR;
726 errbuf := s_pkg_name || '.' || l_proc_name || ' ' ||
727 '#' || l_stmt_id || ': ' ||
728 'Failed to compute incremental termination bounds.';
729 RAISE; -- propagate exception to wrapper
730
731 END compute_incr_end_bounds;
732
733
734 /* etl_report_success
735
736 Interface API for all ETLs that have collected data for the bounds
737 stored in the log table OPI_DBI_RUN_LOG_CURR.
738
739 This API is an indication from the ETL that its current rows in the
740 OPI_DBI_RUN_LOG_CURR should be copied into the history table,
741 OPI_DBI_RUN_LOG_AUDIT and then updated so that they can be populated
742 with new bounds when the common module runs again.
743
744 There are 4 different ETLs that can call the API:
745 Job Transactions ETL - WIP Completions, Scrap and Material Usage
746 Actual Resource Usage
747 Resource Variance
748 Job Master
749
750 Each of the ETLs can have an OPI or OPM source. The behaviour of
751 the API depends on both the invoking ETL and the source.
752
753 The general behaviour is that all rows for the ETL-source pair
754 get a last_collection_date of when this API is invoked and are copied
755 to the audit table. Then for the ETLs that use transaction_id's the
756 start and next_start txn_id's are updated.
757
758 In particular, the following types of behaviours can occur:
759 1. txn_id_success - For transaction_id based highwatermark ETLs:
760 Job Transactions ETL and OPI Source,
761 Actual Resource Usage and OPI Source.
762 Sets the last_collection_date to when this API
763 is called and copies all rows for the etl-source
764 pair to the audit table. The start and next_start
765 txn_id columns are updated.
766 2. collect_date_success - For last_collection_date based highwatermark
767 ETLs: Resource Usage and Job Master and all OPM ETL's.
768 Sets the last_collection_date to when this API
769 is called and copies all rows for the
770 etl-source pair to the audit table.
771 Also sets the from and to bound dates.
772
773 DO NOT COMMIT ANY DATA IN THIS API. IT IS THE RESPONSIBILITY OF THE
774 MODULE INVOKING THIS API TO COMMIT!!!!
775
776 Parameters:
777 p_etl_id - ETL id of the ETL invoking API.
778 p_source - data source of ETL (1 = OPI, 2 = OPM).
779
780 Return Value:
781 l_retval - true if the function returns with no errors.
782 false otherwise.
783
784 Date Author Action
785 04/21/03 Dinkar Gupta Wrote Function
786 07/01/05 Sandeep Beri Modified the IF conditions to the
787 transaction id set uo check call as
788 in R12, no OPM ETL would have txn id bounds.
789
790 */
791 FUNCTION etl_report_success (p_etl_id IN NUMBER, p_source IN NUMBER)
792 RETURN BOOLEAN
793 IS
794 l_proc_name VARCHAR2 (60) := 'etl_report_success';
795 l_stmt_id NUMBER := 0;
796
797 l_retval BOOLEAN := false;
798 l_now DATE := sysdate;
799
800 BEGIN
801
802 l_stmt_id := 10;
803 IF ( ((p_etl_id = JOB_TXN_ETL) OR
804 (p_etl_id = ACTUAL_RES_ETL)) AND p_source = OPI_SOURCE) THEN
805 --{
806 l_retval := txn_id_success (p_etl_id, p_source, l_now);
807 --}
808 -- For Resource Variance and Job Master and all OPM ETL's, the behaviour is
809 -- based on last collection date.
810 ELSE
811 --{
812 l_retval := collect_date_success (p_etl_id, p_source, l_now);
813 --}
814 END IF;
815
816
817 -- success
818 return l_retval;
819
820 EXCEPTION
821
822 WHEN OTHERS THEN
823 rollback;
824
825 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
826 l_proc_name || ' ' ||
827 '#' || l_stmt_id ||
828 ': ' || SQLERRM);
829
830 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
831 l_proc_name || ' ' ||
832 '#' || l_stmt_id || ': ' ||
833 'Failed to log successful collection for ETL #' ||
834 p_etl_id || ' source #' ||
835 p_source || '.');
836
837 return false;
838
839 END etl_report_success;
840
841 /* txn_id_success
842
843 Log success for an ETL that uses transaction_id's as the high watermark.
844
845 The behaviour is simple. Copy out all rows in the current log table,
846 OPI_DBI_RUN_LOG_CURR with the same ETL_id-source values,
850 Then for all existing rows in OPI_DBI_RUN_LOG_CURR with the same ETL_id-
847 and copy them to the audit table with the
848 last_collection_date set to the completion date passed in.
849
851 source pairs as passed as arguments:
852 Set the start_txn_id to the next_start_txn_id because for the next
853 run we need to start at where we stopped this time.
854 Set the next_start_txn_id to NULL since it will have to be recomputed.
855 Set the last_collection_date to the completion date passed in.
856
857 DO NOT COMMIT ANYTHING HERE. THAT WILL BE THE RESPONSIBILITY OF THE
858 CALLING FUNCTIONS !!!
859
860 Parameters:
861 p_etl_id - ETL id of the ETL invoking API.
862 p_source - data source of ETL (1 = OPI, 2 = OPM).
863 p_completion_date - date on which the ETL completed.
864
865 Return Value:
866 l_retval - true if the function returns with no errors.
867 false otherwise.
868
869 Date Author Action
870 04/21/03 Dinkar Gupta Wrote Function
871
872 */
873 FUNCTION txn_id_success (p_etl_id IN NUMBER, p_source IN NUMBER,
874 p_completion_date IN DATE)
875 RETURN BOOLEAN
876 IS
877 l_proc_name VARCHAR2 (60) := 'txn_id_success';
878 l_stmt_id NUMBER := 0;
879
880 l_retval BOOLEAN := true;
881
882 BEGIN
883
884 -- copy all the rows for that ETL and source into
885 -- the audit table with the last_collection_date set
886 -- to the completion date
887 l_stmt_id := 10;
888 INSERT INTO opi_dbi_run_log_audit (
889 organization_id,
890 source,
891 last_collection_date,
892 start_txn_id,
893 next_start_txn_id,
894 from_bound_date,
895 to_bound_date,
896 etl_id,
897 stop_reason_code,
898 last_transaction_date,
899 last_update_date,
900 creation_date,
901 last_updated_by,
902 created_by,
903 last_update_login,
904 program_id,
905 program_login_id,
906 program_application_id,
907 request_id
908 )
909 SELECT
910 organization_id,
911 p_source,
912 p_completion_date,
913 start_txn_id,
914 next_start_txn_id,
915 from_bound_date,
916 to_bound_date,
917 p_etl_id,
918 stop_reason_code,
919 last_transaction_date,
920 sysdate,
921 sysdate,
922 s_user_id,
923 s_user_id,
924 s_login_id,
925 s_program_id,
926 s_program_login_id,
927 s_program_application_id,
928 s_request_id
929 FROM opi_dbi_run_log_curr
930 WHERE etl_id = p_etl_id
931 AND source = p_source;
932
933
934 -- update the start_txn_id to the next_start_txn_id and
935 -- the last_collection_date to the completion date in the
936 -- OPI_DBI_RUN_LOG_CURR.
937 -- update the next_start_txn_id
938 -- since they must be recomputed when the common module runs
939 -- again.
940 -- Do not change the stop reason code since PTP will need it later.
941 -- Performance change (09/09/2003): Merged previous two updates into one.
942
943 l_stmt_id := 20;
944 UPDATE opi_dbi_run_log_curr log
945 SET last_collection_date = p_completion_date,
946 start_txn_id = next_start_txn_id,
947 next_start_txn_id = NULL
948 WHERE log.source = p_source
949 AND log.etl_id = p_etl_id;
950
951 return l_retval;
952
953 EXCEPTION
954
955 WHEN OTHERS THEN
956 rollback;
957
958 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
959 l_proc_name || ' ' ||
960 '#' || l_stmt_id ||
961 ': ' || SQLERRM);
962
963 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
964 l_proc_name || ' ' ||
965 '#' || l_stmt_id || ': ' ||
966 'Failed to log transaction id success for ETL #' ||
967 p_etl_id || ' source #' ||
968 p_source || '.');
969
970 return false;
971
972 END txn_id_success;
973
974
975 /* collect_date_success
976
977 Log success for an ETL that uses from and to bound dates as the high watermark.
978
979 The behaviour is simple. Copy out all rows in the current log table,
980 OPI_DBI_RUN_LOG_CURR with the same ETL_id-source values,
981 and copy them to the audit table with the
982 last_collection_date set to the completion date passed in.
983
984 Then for all existing rows in OPI_DBI_RUN_LOG_CURR with the same ETL_id-
985 source pairs as passed as arguments:
986 Set the last_collection_date to the completion date passed in.
987
988 DO NOT COMMIT ANYTHING HERE. THAT WILL BE THE RESPONSIBILITY OF THE
989 CALLING FUNCTIONS !!!
990
991 Parameters:
992 p_etl_id - ETL id of the ETL invoking API.
993 p_source - data source of ETL (1 = OPI, 2 = OPM).
997 l_retval - true if the function returns with no errors.
994 p_completion_date - date on which the ETL completed.
995
996 Return Value:
998 false otherwise.
999
1000 Date Author Action
1001 04/21/03 Dinkar Gupta Wrote Function
1002 07/01/05 Sandeep Beri Set the from bound date of the next
1003 run to the to bound date of this run.
1004 And set to bound date as NULL.
1005
1006 */
1007 FUNCTION collect_date_success (p_etl_id IN NUMBER, p_source IN NUMBER,
1008 p_completion_date IN DATE)
1009 RETURN BOOLEAN
1010 IS
1011 l_proc_name VARCHAR2 (60) := 'collect_date_success';
1012 l_stmt_id NUMBER := 0;
1013
1014 l_retval BOOLEAN := true;
1015
1016 BEGIN
1017
1018 -- copy all the rows for that ETL and source into
1019 -- the audit table with the last_collection_date set
1020 -- to the completion date
1021 l_stmt_id := 10;
1022 INSERT INTO opi_dbi_run_log_audit (
1023 organization_id,
1024 source,
1025 last_collection_date,
1026 start_txn_id,
1027 next_start_txn_id,
1028 from_bound_date,
1029 to_bound_date,
1030 etl_id,
1031 stop_reason_code,
1032 last_update_date,
1033 creation_date,
1034 last_updated_by,
1035 created_by,
1036 last_update_login,
1037 program_id,
1038 program_login_id,
1039 program_application_id,
1040 request_id
1041 )
1042 SELECT
1043 organization_id,
1044 p_source,
1045 p_completion_date,
1046 start_txn_id,
1047 next_start_txn_id,
1048 from_bound_date,
1049 to_bound_date,
1050 p_etl_id,
1051 stop_reason_code,
1052 sysdate,
1053 sysdate,
1054 s_user_id,
1055 s_user_id,
1056 s_login_id,
1057 s_program_id,
1058 s_program_login_id,
1059 s_program_application_id,
1060 s_request_id
1061 FROM opi_dbi_run_log_curr
1062 WHERE etl_id = p_etl_id
1063 AND source = p_source;
1064
1065 -- update the last_collection_date to the completion date in the
1066 -- OPI_DBI_RUN_LOG_CURR.
1067 -- Also set the stop_reason_code to NULL for consistency.
1068 -- Also set the from_bound_date of the to_bound_date of this run
1069 -- and set the to_bound_date to NULL as it would be computed in the
1070 -- next run.
1071 l_stmt_id := 20;
1072 UPDATE opi_dbi_run_log_curr log
1073 SET last_collection_date = p_completion_date,
1074 from_bound_date = to_bound_date,
1075 to_bound_date = NULL,
1076 stop_reason_code = NULL
1077 WHERE log.source = p_source
1078 AND log.etl_id = p_etl_id;
1079
1080 return l_retval;
1081
1082 EXCEPTION
1083
1084 WHEN OTHERS THEN
1085 rollback;
1086
1087 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1088 l_proc_name || ' ' ||
1089 '#' || l_stmt_id ||
1090 ': ' || SQLERRM);
1091
1092 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1093 l_proc_name || ' ' ||
1094 '#' || l_stmt_id || ': ' ||
1095 'Failed to log collection date success for ETL #' ||
1096 p_etl_id || ' source #' ||
1097 p_source || '.');
1098
1099 return false;
1100
1101 END collect_date_success;
1102
1103 /* incr_end_bounds_setup
1104
1105 API called by ETLs to ensure that the bounds they are running for are
1106 set up correctly.
1107
1108 For the Material and Actual Resource Usage ETLs for OPI,
1109 this requires checking if all the next_start_txn_id values are not null
1110 for the given ETL and the source.
1111
1112 For the Resource and Job variance ETLs in OPI and all OPM ETL's, need to ensure that the
1113 last_collection_date is NOT NULL sice this is the incremental extraction.
1114 Parameters:
1115 p_etl_id - etl_id of ETL invoking API.
1116 p_source - 1 for OPI, 2 for OPM
1117
1118 Return:
1119 l_bounds_valid - true if the bounds are valid
1120 false o.w.
1121
1122 Date Author Action
1123 04/23/03 Dinkar Gupta Wrote Function
1124
1125 */
1126 FUNCTION incr_end_bounds_setup (p_etl_id IN NUMBER, p_source IN NUMBER)
1127 RETURN BOOLEAN
1128 IS
1129
1130 l_proc_name VARCHAR2 (60) := 'incr_end_bounds_setup';
1131 l_stmt_id NUMBER := 0;
1132
1133 l_exists NUMBER := NULL;
1134 l_bounds_valid BOOLEAN := true;
1135
1136 BEGIN
1137
1138 -- Ensure the log table is not empty
1139 l_stmt_id := 5;
1140 BEGIN
1141 SELECT 1
1142 INTO l_exists
1143 FROM dual
1144 WHERE (EXISTS (SELECT source
1148 EXCEPTION
1145 FROM opi_dbi_run_log_curr
1146 WHERE rownum = 1));
1147
1149 WHEN NO_DATA_FOUND THEN
1150 RAISE run_common_mod_init;
1151
1152 END;
1153
1154
1155 -- For Discrete Job Transactions ETL
1156 -- and Actual Resource Usage, the behaviour is based on transaction
1157 -- id's
1158 l_stmt_id := 10;
1159 IF ( ((p_etl_id = JOB_TXN_ETL) OR
1160 (p_etl_id = ACTUAL_RES_ETL)) AND p_source = OPI_SOURCE ) THEN
1161 --{
1162 l_bounds_valid := txn_id_incr_setup (p_etl_id, p_source);
1163 --}
1164 ELSE
1165 --{
1166 l_bounds_valid := collect_date_incr_setup (p_etl_id, p_source);
1167 --}
1168 END IF;
1169
1170
1171 return l_bounds_valid;
1172
1173 EXCEPTION
1174
1175 WHEN run_common_mod_init THEN
1176 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1177 l_proc_name || ' ' ||
1178 '#' || l_stmt_id ||
1179 ': ' || 'The initial load request set has not been run yet. Please run the initial load request set before running the incremental load request set.');
1180
1181 l_bounds_valid := false;
1182 return l_bounds_valid;
1183
1184
1185 WHEN OTHERS THEN
1186 rollback;
1187
1188 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1189 l_proc_name || ' ' ||
1190 '#' || l_stmt_id ||
1191 ': ' || SQLERRM);
1192
1193 l_bounds_valid := false;
1194 return l_bounds_valid;
1195
1196 END incr_end_bounds_setup;
1197
1198 /* txn_id_incr_setup
1199
1200 Ensure that all the txn_id bounds are correctly setup for
1201 the incremental load of the ETL with the source passed in as arguments.
1202
1203 Right now, this requires checking that the start_txn_id
1204 and next_start_txn_id columns are non-null for all the rows of the ETL
1205 and source.
1206
1207 Parameters:
1208 p_etl_id - etl_id of ETL invoking API.
1209 p_source - 1 for OPI, 2 for OPM
1210
1211 Return:
1212 l_bounds_valid - true if the bounds are valid
1213 false o.w.
1214
1215 Date Author Action
1216 04/23/03 Dinkar Gupta Wrote Function
1217
1218 */
1219 FUNCTION txn_id_incr_setup (p_etl_id IN NUMBER, p_source IN NUMBER)
1220 RETURN BOOLEAN
1221 IS
1222
1223 l_proc_name VARCHAR2 (60) := 'txn_id_incr_setup';
1224 l_stmt_id NUMBER := 0;
1225
1226 l_bounds_valid BOOLEAN := true;
1227
1228 l_exists NUMBER := NULL;
1229
1230 BEGIN
1231
1232 -- Ensure that all the start_txn_id's are non-null
1233 l_stmt_id := 10;
1234 BEGIN
1235 SELECT 1
1236 INTO l_exists
1237 FROM dual
1238 WHERE (EXISTS (SELECT start_txn_id
1239 FROM opi_dbi_run_log_curr
1240 WHERE start_txn_id IS NULL
1241 AND source = p_source
1242 AND etl_id = p_etl_id));
1243
1244 RAISE txn_id_bounds_missing; -- found a missing next_start_txn_id
1245
1246 EXCEPTION
1247 WHEN NO_DATA_FOUND THEN
1248 l_bounds_valid := true;
1249 END;
1250
1251 -- Ensure that all the next_start_txn_id's are non-null
1252 l_stmt_id := 20;
1253 BEGIN
1254 SELECT 1
1255 INTO l_exists
1256 FROM opi_dbi_run_log_curr
1257 WHERE next_start_txn_id IS NULL
1258 AND source = p_source
1259 AND etl_id = p_etl_id
1260 AND rownum = 1;
1261
1262 RAISE txn_id_bounds_missing; -- found a missing start_txn_id
1263
1264 EXCEPTION
1265 WHEN NO_DATA_FOUND THEN
1266 l_bounds_valid := true;
1267 END;
1268
1269
1270 return l_bounds_valid;
1271
1272 EXCEPTION
1273
1274 WHEN txn_id_bounds_missing THEN
1275 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1276 l_proc_name || ' ' ||
1277 '#' || l_stmt_id || ': ' ||
1278 'Found missing transaction_id bounds for ETL ' ||
1279 p_etl_id || ' source ' ||
1280 p_source || '. This means that some concurrent program is running out of turn. Please run the incremental load request set.');
1281 l_bounds_valid := false;
1282 return l_bounds_valid;
1283
1284 WHEN OTHERS THEN
1285 rollback;
1286
1287 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1288 l_proc_name || ' ' ||
1289 '#' || l_stmt_id ||
1290 ': ' || SQLERRM);
1291
1292 l_bounds_valid := false;
1293 return l_bounds_valid;
1294
1295 END txn_id_incr_setup;
1296
1297 /* collect_date_incr_setup
1298
1299 Ensure that all the collection date bounds are correctly setup for
1300 the initial load of the ETL with the source passed in as arguments.
1301
1302 Right now, this requires checking that the last_collection_date is
1303 not NULL. And even the to and the from date bounds are not null.
1304
1305 Parameters:
1306 p_etl_id - etl_id of ETL invoking API.
1307 p_source - 1 for OPI, 2 for OPM
1308
1309 Return:
1310 l_bounds_valid - true if the bounds are valid
1311 false o.w.
1312
1313 Date Author Action
1314 04/23/03 Dinkar Gupta Wrote Function
1315
1316 */
1317 FUNCTION collect_date_incr_setup (p_etl_id IN NUMBER, p_source IN NUMBER)
1318 RETURN BOOLEAN
1319 IS
1320
1321 l_proc_name VARCHAR2 (60) := 'collect_date_incr_setup';
1322 l_stmt_id NUMBER := 0;
1323
1324 l_bounds_valid BOOLEAN := true;
1325
1326 l_exists NUMBER := NULL;
1327
1328 BEGIN
1329
1330 -- Ensure that all the last_collection_date's are non-null
1331 l_stmt_id := 10;
1332 BEGIN
1333 SELECT 1
1334 INTO l_exists
1335 FROM opi_dbi_run_log_curr
1336 WHERE last_collection_date IS NULL
1337 AND source = p_source
1338 AND etl_id = p_etl_id
1339 AND rownum = 1;
1340
1341 -- found a null last_collection_date
1342 RAISE collect_date_bounds_missing;
1343
1344 EXCEPTION
1345 WHEN NO_DATA_FOUND THEN
1346 l_bounds_valid := true;
1347 END;
1348 -- Ensure that the from_bound_date's are non null
1349 l_stmt_id := 20;
1350 BEGIN
1351 SELECT 1
1352 INTO l_exists
1353 FROM dual
1354 WHERE (EXISTS (SELECT from_bound_date
1355 FROM opi_dbi_run_log_curr
1356 WHERE from_bound_date IS NULL
1357 AND source = p_source
1358 AND etl_id = p_etl_id));
1359
1360 RAISE collect_date_bounds_missing; -- found a null from bound date
1361
1362 EXCEPTION
1363 WHEN NO_DATA_FOUND THEN
1364 l_bounds_valid := true;
1365 END;
1366
1367 -- Ensure that the to_bound_date's are non null
1368 l_stmt_id := 30;
1369 BEGIN
1370 SELECT 1
1371 INTO l_exists
1372 FROM dual
1373 WHERE (EXISTS (SELECT to_bound_date
1374 FROM opi_dbi_run_log_curr
1375 WHERE to_bound_date IS NULL
1376 AND source = p_source
1377 AND etl_id = p_etl_id));
1378
1379 RAISE collect_date_bounds_missing; -- found a null to bound date
1380
1381 EXCEPTION
1382 WHEN NO_DATA_FOUND THEN
1383 l_bounds_valid := true;
1384 END;
1385
1386 return l_bounds_valid;
1387
1388 EXCEPTION
1389
1390 WHEN collect_date_bounds_missing THEN
1391 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1392 l_proc_name || ' ' ||
1393 '#' || l_stmt_id || ': ' ||
1394 'Found null last_collection_date for ETL ' ||
1395 p_etl_id || ' source ' ||
1396 p_source ||
1397 ' which means initial loads were never run. Please run initial load request set before running the incremental request set.');
1398 l_bounds_valid := false;
1399 return l_bounds_valid;
1400
1401 WHEN OTHERS THEN
1402 rollback;
1403
1404 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1405 l_proc_name || ' ' ||
1406 '#' || l_stmt_id ||
1407 ': ' || SQLERRM);
1408
1409 l_bounds_valid := false;
1410 return l_bounds_valid;
1411
1412 END collect_date_incr_setup;
1413
1414
1415 /* run_incr_load
1416
1417 API for ETL incremntal loads to that they are mean to run and not the
1418 incremental loads.
1419
1420 The incremental load of an ETL should call this which when it returns true
1421 indicates that it is time to run the initial load and when it returns
1422 false indicates that it is time to run the incremental load
1423 */
1424 FUNCTION run_incr_load (p_etl_id IN NUMBER, p_source IN NUMBER)
1425 RETURN BOOLEAN
1426 IS
1427
1428 l_proc_name VARCHAR2 (60) := 'run_incr_load';
1429 l_stmt_id NUMBER := 0;
1430
1431 l_run_incr BOOLEAN := false;
1432 l_num_non_incr_rows NUMBER := -1;
1433
1434 BEGIN
1435
1436 -- All that needs to be done is to ensure that for all rows, the last
1437 -- collection date is not NULL. If any row does not match is condition,
1438 -- then the incremental load cannot be run.
1439 l_stmt_id := 10;
1440 SELECT sum (1)
1441 INTO l_num_non_incr_rows
1442 FROM opi_dbi_run_log_curr
1443 WHERE source = p_source
1444 AND etl_id = p_etl_id
1445 AND last_collection_date IS NULL;
1446
1447
1448 l_stmt_id := 20;
1449 IF (l_num_non_incr_rows IS NULL) THEN
1450 l_run_incr := true;
1451 ELSE
1452 l_run_incr := false;
1453 BIS_COLLECTION_UTILITIES.PUT_LINE ('The incremental request set cannot be run because ' || l_num_non_incr_rows || ' rows in the runtime bounds log indicate that the initial load has not been run.');
1454 BIS_COLLECTION_UTILITIES.PUT_LINE (' Please run the initial load request set before running the incremental load request set.');
1455
1456 END IF;
1457
1458 l_stmt_id := 30;
1459 return l_run_incr;
1460
1461 EXCEPTION
1462
1463 WHEN OTHERS THEN
1464 rollback;
1465
1466 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
1467 l_proc_name || ' ' ||
1468 '#' || l_stmt_id ||
1469 ': ' || SQLERRM);
1470
1471 l_run_incr := false;
1472 return l_run_incr;
1473
1474 END run_incr_load;
1475
1476
1477 END opi_dbi_common_mod_incr_pkg;
[Home] [Help]