[Home] [Help]
PACKAGE BODY: APPS.HRI_OPL_BDGTS
Source
1 PACKAGE BODY HRI_OPL_BDGTS AS
2 /* $Header: hriobdgt.pkb 120.1 2005/06/29 07:00:42 ddutta noship $ */
3 --
4 -- Global variables representing parameters
5 --
6 g_refresh_start_date DATE;
7 g_refresh_end_date DATE;
8 g_full_refresh VARCHAR2(5);
9 --
10 -- Global flag which determines whether debugging is turned on
11 --
12 g_debug_flag VARCHAR2(5);
13 --
14 -- Whether called from a concurrent program
15 --
16 g_concurrent_flag VARCHAR2(5);
17 -- ----------------------------------------------------------------------------
18 -- Inserts row into concurrent program log
19 --
20 --
21 PROCEDURE output(p_text VARCHAR2) IS
22 BEGIN
23 --
24 IF (g_concurrent_flag = 'Y') THEN
25 --
26 -- Write to the concurrent request log
27 --
28 fnd_file.put_line(fnd_file.log, p_text);
29 --
30 ELSE
31 --
32 hr_utility.trace(p_text);
33 --
34 END IF;
35 --
36 END output;
37 --
38 -- -----------------------------------------------------------------------------
39 -- Inserts row into concurrent program log if debugging is enabled
40 -- -----------------------------------------------------------------------------
41 --
42 PROCEDURE dbg(p_text VARCHAR2) IS
43 --
44 BEGIN
45 --
46 IF (g_debug_flag = 'Y') THEN
47 --
48 -- Write to output
49 --
50 output(p_text);
51 --
52 END IF;
53 --
54 END dbg;
55 --
56 -- ----------------------------------------------------------------------------
57 -- Runs given sql statement dynamically without raising an exception
58 -- ----------------------------------------------------------------------------
59 --
60 PROCEDURE run_sql_stmt_noerr( p_sql_stmt VARCHAR2 )
61 IS
62 --
63 BEGIN
64 --
65 EXECUTE IMMEDIATE p_sql_stmt;
66 --
67 EXCEPTION WHEN OTHERS THEN
68 --
69 output('Could not run the following sql:');
70 output(SUBSTR(p_sql_stmt,1,230));
71 --
72 END run_sql_stmt_noerr;
73 --
74 -- ----------------------------------------------------------------------------
75 -- Function to retrieve the budget_measurement_type
76 -- ----------------------------------------------------------------------------
77 --
78 FUNCTION get_budget_measurement_type(p_unit_of_measure_id IN NUMBER )
79 RETURN VARCHAR2
80 IS
81 --
82 l_budget_measurement_type VARCHAR2(30);
83 --
84 CURSOR csr_budget_measurement_type is
85 SELECT system_type_cd
86 FROM per_shared_types
87 WHERE shared_type_id = p_unit_of_measure_id
88 AND lookup_type = 'BUDGET_MEASUREMENT_TYPE';
89 BEGIN
90 OPEN csr_budget_measurement_type;
91 FETCH csr_budget_measurement_type INTO l_budget_measurement_type;
92 CLOSE csr_budget_measurement_type;
93 --
94 RETURN l_budget_measurement_type;
95 --
96 --
97 END get_budget_measurement_type;
98 --
99 -- ----------------------------------------------------------------------------
100 -- This procedure inserts headcount budgets
101 -- ----------------------------------------------------------------------------
102 --
103 PROCEDURE insert_headcount_budgets (p_budget_id IN NUMBER
104 ,p_business_group_id IN NUMBER
105 ,p_budgeted_entity_cd IN VARCHAR2
106 ,p_budget_measurement_type IN VARCHAR2
107 ,p_currency_code IN VARCHAR2
108 ,p_budget_aggregate IN VARCHAR2
109 ,p_budget_start_date IN DATE
110 ,p_budget_end_date IN DATE
111 ,p_budget_version_id IN NUMBER
112 ,p_version_start_date IN DATE
113 ,p_version_end_date IN DATE
114 ,p_unit IN NUMBER)
115 IS
116 --
117 --
118 -- Variables to populate WHO Columns
119 --
120 l_current_time DATE;
121 l_user_id NUMBER;
122
123 BEGIN
124 --
125 --
126 dbg('Inside insert_headcount_budgets');
127 --
128 l_current_time := SYSDATE;
129 l_user_id := fnd_global.user_id;
130 --
131 --
132 INSERT INTO HRI_MB_BDGTS_CT (
133 HRI_MB_BDGTS_CT_ID
134 ,BUDGET_ID
135 ,BUSINESS_GROUP_ID
136 ,BUDGETED_ENTITY_CD
137 ,BUDGET_MEASUREMENT_TYPE
138 ,BUDGET_CURRENCY_CODE
139 ,BUDGET_AGGREGATE
140 ,BUDGET_START_DATE
141 ,BUDGET_END_DATE
142 ,BUDGET_VERSION_ID
143 ,VERSION_START_DATE
144 ,VERSION_END_DATE
145 ,BUDGET_DETAIL_ID
146 ,ORGANIZATION_ID
147 ,JOB_ID
148 ,POSITION_ID
149 ,GRADE_ID
150 ,BUDGET_PERIOD_ID
151 ,PERIOD_START_DATE
152 ,PERIOD_END_DATE
153 ,BUDGET_VALUE
154 --
155 -- WHO Columns
156 --
157 ,last_update_date
158 ,last_update_login
159 ,last_updated_by
160 ,created_by
161 ,creation_date)
162 SELECT
163 hri_mb_bdgts_ct_s.nextval
164 ,p_budget_id
165 ,p_business_group_id
166 ,p_budgeted_entity_cd
167 ,p_budget_measurement_type
168 ,p_currency_code
169 ,p_budget_aggregate
170 ,p_budget_start_date
171 ,p_budget_end_date
172 ,p_budget_version_id
173 ,p_version_start_date
174 ,p_version_end_date
175 ,det.budget_detail_id
176 ,nvl(det.organization_id, -1)
177 ,nvl(det.job_id, -1)
178 ,nvl(det.position_id, -1)
179 ,nvl(det.grade_id,-1)
180 ,prd.budget_period_id
181 ,ptps.start_date
182 ,ptpe.end_date
183 ,CASE WHEN p_unit = 1 THEN prd.budget_unit1_value
184 WHEN p_unit = 2 THEN prd.budget_unit2_value
185 ELSE prd.budget_unit3_value
186 END budget_value
187 ,SYSDATE
188 ,l_user_id
189 ,l_user_id
190 ,l_user_id
191 ,SYSDATE
192 FROM pqh_budget_details det,
193 pqh_budget_periods prd,
194 per_time_periods ptps,
195 per_time_periods ptpe
196 WHERE det.budget_version_id = p_budget_version_id
197 AND det.budget_detail_id = prd.budget_detail_id
198 AND prd.start_time_period_id = ptps.time_period_id
199 AND prd.end_time_period_id = ptpe.time_period_id ;
200 --
201 dbg(SQL%ROWCOUNT||' headcount records inserted into HRI_MB_BDGTS_CT');
202 dbg('Exiting insert_headcount_budgets');
203 --
204 EXCEPTION
205 WHEN OTHERS THEN
206 --
207 output(sqlerrm);
208 --
209 -- RAISE;
210 --
211 --
212 END insert_headcount_budgets;
213 --
214 -- ----------------------------------------------------------------------------
215 -- This procedure inserts labor cost budgets
216 -- ----------------------------------------------------------------------------
217 --
218 PROCEDURE insert_laborcost_budgets (p_budget_id IN NUMBER
219 ,p_business_group_id IN NUMBER
220 ,p_budgeted_entity_cd IN VARCHAR2
221 ,p_budget_measurement_type IN VARCHAR2
222 ,p_currency_code IN VARCHAR2
223 ,p_budget_aggregate IN VARCHAR2
224 ,p_budget_start_date IN DATE
225 ,p_budget_end_date IN DATE
226 ,p_budget_version_id IN NUMBER
227 ,p_version_start_date IN DATE
228 ,p_version_end_date IN DATE
229 ,p_unit IN NUMBER)
230 IS
231 --
232 --
233 -- Variables to populate WHO Columns
234 --
235 l_current_time DATE;
236 l_user_id NUMBER;
237
238 BEGIN
239 --
240 --
241 dbg('Inside insert_laborcost_budgets');
242 --
243 l_current_time := SYSDATE;
244 l_user_id := fnd_global.user_id;
245 --
246 --
247 INSERT INTO HRI_MB_BDGTS_CT (
248 HRI_MB_BDGTS_CT_ID
249 ,BUDGET_ID
250 ,BUSINESS_GROUP_ID
251 ,BUDGETED_ENTITY_CD
252 ,BUDGET_MEASUREMENT_TYPE
253 ,BUDGET_CURRENCY_CODE
254 ,BUDGET_AGGREGATE
255 ,BUDGET_START_DATE
256 ,BUDGET_END_DATE
257 ,BUDGET_VERSION_ID
258 ,VERSION_START_DATE
259 ,VERSION_END_DATE
260 ,BUDGET_DETAIL_ID
261 ,ORGANIZATION_ID
262 ,JOB_ID
263 ,POSITION_ID
264 ,GRADE_ID
265 ,BUDGET_PERIOD_ID
266 ,PERIOD_START_DATE
267 ,PERIOD_END_DATE
268 ,BUDGET_SET_ID
269 ,BUDGET_ELEMENT_ID
270 ,ELEMENT_TYPE_ID
271 ,BUDGET_FUND_SRC_ID
272 ,COST_ALLOCATION_KEYFLEX_ID
273 ,BUDGET_VALUE
274 --
275 -- WHO Columns
276 --
277 ,last_update_date
278 ,last_update_login
279 ,last_updated_by
280 ,created_by
281 ,creation_date)
282 SELECT
283 hri_mb_bdgts_ct_s.nextval
284 ,p_budget_id
285 ,p_business_group_id
286 ,p_budgeted_entity_cd
287 ,p_budget_measurement_type
288 ,p_currency_code
289 ,p_budget_aggregate
290 ,p_budget_start_date
291 ,p_budget_end_date
292 ,p_budget_version_id
293 ,p_version_start_date
294 ,p_version_end_date
295 ,det.budget_detail_id
296 ,nvl(det.organization_id, -1)
297 ,nvl(det.job_id, -1)
298 ,nvl(det.position_id, -1)
299 ,nvl(det.grade_id,-1)
300 ,prd.budget_period_id
301 ,ptps.start_date
302 ,ptpe.end_date
303 ,bset.budget_set_id
304 ,ele.budget_element_id
305 ,ele.element_type_id
306 ,src.budget_fund_src_id
307 ,src.cost_allocation_keyflex_id
308 ,CASE WHEN p_unit = 1 THEN
309 (src.distribution_percentage * ( ele.distribution_percentage * bset.budget_unit1_value ) / 100 ) / 100
310 WHEN p_unit = 2 THEN
311 (src.distribution_percentage * ( ele.distribution_percentage * bset.budget_unit2_value ) / 100 ) / 100
312 ELSE (src.distribution_percentage * ( ele.distribution_percentage * bset.budget_unit3_value ) / 100 ) / 100
313 END budget_value
314 ,SYSDATE
315 ,l_user_id
316 ,l_user_id
317 ,l_user_id
318 ,SYSDATE
319 FROM pqh_budget_details det,
320 pqh_budget_periods prd,
321 per_time_periods ptps,
322 per_time_periods ptpe,
323 pqh_budget_sets bset,
324 pqh_budget_elements ele,
325 pqh_budget_fund_srcs src
326 WHERE det.budget_version_id = p_budget_version_id
327 AND det.budget_detail_id = prd.budget_detail_id
328 AND prd.budget_period_id = bset.budget_period_id
329 AND prd.start_time_period_id = ptps.time_period_id
330 AND prd.end_time_period_id = ptpe.time_period_id
331 AND bset.budget_set_id = ele.budget_set_id
332 AND ele.budget_element_id = src.budget_element_id;
333 --
334 dbg(SQL%ROWCOUNT||' labor cost records inserted into HRI_MB_BDGTS_CT');
335 dbg('Exiting insert_laborcost_budgets');
336 --
337 EXCEPTION
338 WHEN OTHERS THEN
339 --
340 output(sqlerrm);
341 --
342 -- RAISE;
343 --
344 --
345 END insert_laborcost_budgets;
346 --
347 -- ----------------------------------------------------------------------------
348 -- SET_PARAMETERS
349 -- sets up parameters required for the process.
350 -- ----------------------------------------------------------------------------
351 --
352 PROCEDURE set_parameters IS
353 --
354 BEGIN
355 --
356 g_refresh_start_date := bis_common_parameters.get_global_start_date;
357 g_refresh_end_date := hr_general.end_of_time;
358 g_full_refresh := 'Y';
359 g_concurrent_flag := 'Y';
360 g_debug_flag := 'Y';
361 --
362 END set_parameters;
363 --
364 -- ----------------------------------------------------------------------------
365 -- PROCESS
366 -- Processes actions and inserts data into summary table
367 -- This procedure is executed for every person in a chunk
368 -- ----------------------------------------------------------------------------
369 --
370 PROCEDURE process(p_full_refresh_flag IN VARCHAR2)
371 IS
372 --
373 -- Variables to populate WHO Columns
374 --
375 l_current_time DATE;
376 l_user_id NUMBER;
377 l_unit1_measure VARCHAR2(30);
378 l_unit2_measure VARCHAR2(30);
379 l_unit3_measure VARCHAR2(30);
380 --
381 --
382 CURSOR csr_budgets IS
383 SELECT bdgt.budget_id,
384 bdgt.budget_name,
385 bdgt.business_group_id,
386 bdgt.budgeted_entity_cd,
387 NVL(bdgt.currency_code, pqh_budget.get_currency_cd(bdgt.budget_id)) CURRENCY_CODE,
388 bdgt.budget_unit1_id,
389 bdgt.budget_unit2_id,
390 bdgt.budget_unit3_id,
391 bdgt.budget_unit1_aggregate,
392 bdgt.budget_unit2_aggregate,
393 bdgt.budget_unit3_aggregate,
394 bdgt.budget_start_date,
395 bdgt.budget_end_date,
396 ver.budget_version_id,
397 ver.date_from,
398 ver.date_to
399 FROM pqh_budgets bdgt,
400 pqh_budget_versions ver
401 WHERE bdgt.position_control_flag = 'Y'
402 AND bdgt.budgeted_entity_cd IN ('ORGANIZATION','POSITION')
403 AND bdgt.budget_id = ver.budget_id
404 AND (( bdgt.budget_start_date BETWEEN g_refresh_start_date AND g_refresh_end_date )
405 OR ( g_refresh_start_date BETWEEN bdgt.budget_start_date AND bdgt.budget_end_date ))
406 AND ver.budget_version_id = ( SELECT max(budget_version_id)
407 FROM pqh_budget_versions pbv
408 WHERE pbv.budget_id = bdgt.budget_id );
409 BEGIN
410 --
411 dbg('Inside process');
412 --
413 l_current_time := SYSDATE;
414 l_user_id := fnd_global.user_id;
415 --
416 --
417 FOR csr_budget_rec IN csr_budgets LOOP
418 --
419 l_unit1_measure := get_budget_measurement_type(csr_budget_rec.budget_unit1_id);
420 l_unit2_measure := get_budget_measurement_type(csr_budget_rec.budget_unit2_id);
421 l_unit3_measure := get_budget_measurement_type(csr_budget_rec.budget_unit3_id);
422 --
423 IF l_unit1_measure = 'HEAD' THEN
424 insert_headcount_budgets(p_budget_id => csr_budget_rec.budget_id
425 ,p_business_group_id => csr_budget_rec.business_group_id
426 ,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
427 ,p_budget_measurement_type => l_unit1_measure
428 ,p_currency_code => csr_budget_rec.currency_code
429 ,p_budget_aggregate => csr_budget_rec.budget_unit1_aggregate
430 ,p_budget_start_date => csr_budget_rec.budget_start_date
434 ,p_version_end_date => csr_budget_rec.date_to
431 ,p_budget_end_date => csr_budget_rec.budget_end_date
432 ,p_budget_version_id => csr_budget_rec.budget_version_id
433 ,p_version_start_date => csr_budget_rec.date_from
435 ,p_unit => 1 );
436 ELSIF l_unit1_measure = 'MONEY' THEN
437 insert_laborcost_budgets(p_budget_id => csr_budget_rec.budget_id
438 ,p_business_group_id => csr_budget_rec.business_group_id
439 ,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
440 ,p_budget_measurement_type => l_unit1_measure
441 ,p_currency_code => csr_budget_rec.currency_code
442 ,p_budget_aggregate => csr_budget_rec.budget_unit1_aggregate
443 ,p_budget_start_date => csr_budget_rec.budget_start_date
444 ,p_budget_end_date => csr_budget_rec.budget_end_date
445 ,p_budget_version_id => csr_budget_rec.budget_version_id
446 ,p_version_start_date => csr_budget_rec.date_from
447 ,p_version_end_date => csr_budget_rec.date_to
448 ,p_unit => 1 );
449
450 END IF;
451
452 IF l_unit2_measure = 'HEAD' THEN
453 insert_headcount_budgets(p_budget_id => csr_budget_rec.budget_id
454 ,p_business_group_id => csr_budget_rec.business_group_id
455 ,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
456 ,p_budget_measurement_type => l_unit2_measure
457 ,p_currency_code => csr_budget_rec.currency_code
458 ,p_budget_aggregate => csr_budget_rec.budget_unit2_aggregate
459 ,p_budget_start_date => csr_budget_rec.budget_start_date
460 ,p_budget_end_date => csr_budget_rec.budget_end_date
461 ,p_budget_version_id => csr_budget_rec.budget_version_id
462 ,p_version_start_date => csr_budget_rec.date_from
463 ,p_version_end_date => csr_budget_rec.date_to
464 ,p_unit => 2 );
465 ELSIF l_unit2_measure = 'MONEY' THEN
466 insert_laborcost_budgets(p_budget_id => csr_budget_rec.budget_id
467 ,p_business_group_id => csr_budget_rec.business_group_id
468 ,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
469 ,p_budget_measurement_type => l_unit2_measure
470 ,p_currency_code => csr_budget_rec.currency_code
471 ,p_budget_aggregate => csr_budget_rec.budget_unit2_aggregate
472 ,p_budget_start_date => csr_budget_rec.budget_start_date
473 ,p_budget_end_date => csr_budget_rec.budget_end_date
474 ,p_budget_version_id => csr_budget_rec.budget_version_id
475 ,p_version_start_date => csr_budget_rec.date_from
476 ,p_version_end_date => csr_budget_rec.date_to
477 ,p_unit => 2 );
478
479 END IF;
480 --
481 IF l_unit3_measure = 'HEAD' THEN
482 insert_headcount_budgets(p_budget_id => csr_budget_rec.budget_id
483 ,p_business_group_id => csr_budget_rec.business_group_id
484 ,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
485 ,p_budget_measurement_type => l_unit3_measure
486 ,p_currency_code => csr_budget_rec.currency_code
487 ,p_budget_aggregate => csr_budget_rec.budget_unit1_aggregate
488 ,p_budget_start_date => csr_budget_rec.budget_start_date
489 ,p_budget_end_date => csr_budget_rec.budget_end_date
490 ,p_budget_version_id => csr_budget_rec.budget_version_id
491 ,p_version_start_date => csr_budget_rec.date_from
492 ,p_version_end_date => csr_budget_rec.date_to
493 ,p_unit => 3 );
494 ELSIF l_unit3_measure = 'MONEY' THEN
495 insert_laborcost_budgets(p_budget_id => csr_budget_rec.budget_id
496 ,p_business_group_id => csr_budget_rec.business_group_id
497 ,p_budgeted_entity_cd => csr_budget_rec.budgeted_entity_cd
498 ,p_budget_measurement_type => l_unit3_measure
499 ,p_currency_code => csr_budget_rec.currency_code
500 ,p_budget_aggregate => csr_budget_rec.budget_unit3_aggregate
501 ,p_budget_start_date => csr_budget_rec.budget_start_date
502 ,p_budget_end_date => csr_budget_rec.budget_end_date
503 ,p_budget_version_id => csr_budget_rec.budget_version_id
504 ,p_version_start_date => csr_budget_rec.date_from
505 ,p_version_end_date => csr_budget_rec.date_to
506 ,p_unit => 3 );
507
508 END IF;
509 --
510 COMMIT;
514 dbg('Exiting process');
511 --
512 END LOOP;
513 --
515 --
516 EXCEPTION
517 WHEN OTHERS THEN
518 --
519 output(sqlerrm);
520 --
521 -- RAISE;
522 --
523 --
524 END process;
525 --
526 -- ----------------------------------------------------------------------------
527 -- PRE_PROCESS
528 -- ----------------------------------------------------------------------------
529 --
530 PROCEDURE PRE_PROCESS IS
531 --
532 l_dummy1 VARCHAR2(2000);
533 l_dummy2 VARCHAR2(2000);
534 l_schema VARCHAR2(400);
535 --
536 BEGIN
537 --
538 -- Record the process start
539 --
540 --
541 -- Set up the parameters
542 --
543 set_parameters;
544 --
545 -- Disable the WHO trigger
546 --
547 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_BDGTS_CT_WHO DISABLE');
548 --
549 -- ---------------------------------------------------------------------------
550 -- Full Refresh Section
551 -- ---------------------------------------------------------------------------
552 --
553 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
554 --
555 -- If it's a full refresh
556 --
557 IF (g_full_refresh = 'Y') THEN
558 --
559 -- Drop Indexes
560 --
561 hri_utl_ddl.log_and_drop_indexes(
562 p_application_short_name => 'HRI',
563 p_table_name => 'HRI_MB_BDGTS_CT',
564 p_table_owner => l_schema);
565 --
566 -- Truncate the table
567 --
568 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.HRI_MB_BDGTS_CT';
569 --
570 END IF;
571 --
572 END IF;
573 --
574 --
575 END PRE_PROCESS;
576 --
577 -- ----------------------------------------------------------------------------
578 -- POST_PROCESS
579 -- It finishes the processing by updating the BIS_REFRESH_LOG table
580 -- ----------------------------------------------------------------------------
581 --
582 PROCEDURE post_process IS
583 --
584 l_dummy1 VARCHAR2(2000);
585 l_dummy2 VARCHAR2(2000);
586 l_schema VARCHAR2(400);
587 --
588 --
589 BEGIN
590 --
591 dbg('Inside post_process');
592 --
593 hri_bpl_conc_log.record_process_start('HRI_OPL_BDGTS');
594 --
595 -- Collect stats for full refresh
596 --
597 IF (g_full_refresh = 'Y') THEN
598 --
599 IF (fnd_installation.get_app_info('HRI',l_dummy1, l_dummy2, l_schema)) THEN
600 --
601 -- Create indexes
602 --
603 dbg('Full Refresh selected - Creating indexes');
604 --
605 hri_utl_ddl.recreate_indexes(
606 p_application_short_name => 'HRI',
607 p_table_name => 'HRI_MB_BDGTS_CT',
608 p_table_owner => l_schema);
609 --
610 -- Collect the statistics only when the process is NOT invoked by a concurrent manager
611 --
612 IF fnd_global.conc_request_id is null THEN
613 --
614 dbg('Full Refresh selected - gathering stats');
615 fnd_stats.gather_table_stats(l_schema,'HRI_MB_BDGTS_CT');
616 --
617 END IF;
618 --
619 END IF;
620 --
621 ELSE
622 --
623 -- Incremental Refresh will be supported later.
624 --
625 NULL;
626 --
627 END IF;
628 --
629 -- Enable the WHO trigger on the fact table
630 --
631 dbg('Enabling the who trigger');
632 run_sql_stmt_noerr('ALTER TRIGGER HRI_MB_BDGTS_CT_WHO ENABLE');
633 --
634 hri_bpl_conc_log.log_process_end(
635 p_status => TRUE
636 ,p_period_from => TRUNC(g_refresh_start_date)
637 ,p_period_to => TRUNC(SYSDATE)
638 ,p_attribute1 => g_full_refresh);
639 --
640 dbg('Exiting post_process');
641 --
642 END post_process;
643 --
644 -- ----------------------------------------------------------------------------
645 -- PROCESS
646 -- ----------------------------------------------------------------------------
647 --
648 PROCEDURE process(
649 errbuf OUT NOCOPY VARCHAR2
650 ,retcode OUT NOCOPY NUMBER
651 ,p_full_refresh_flag IN VARCHAR2)
652 IS
653 --
654 l_error_step NUMBER;
655 --
656 BEGIN
657 --
658 -- Initialize the global variables
659 --
660 pre_process;
661 --
662 -- Depending on the refresh type call the corresponding refresh program
663 --
664 IF g_full_refresh = 'Y' THEN
665 --
666 process(p_full_refresh_flag => g_full_refresh);
667 --
668 ELSE
669 --
670 -- Incremental Refresh will be supported later.
671 --
672 NULL;
673 --
674 END IF;
675 --
676 post_process;
677
678 errbuf := 'SUCCESS';
679 retcode := 0;
680 EXCEPTION
681 WHEN others THEN
682 output('Error encountered while processing ...');
683 output(sqlerrm);
684 errbuf := SQLERRM;
685 retcode := SQLCODE;
686 --
687 RAISE;
688 --
689 END process;
690
691 --
692 -- ----------------------------------------------------------------------------
693 -- LOAD_TABLE
694 -- This procedure can be called from the Test harness to populate the table.
695 -- ----------------------------------------------------------------------------
696 --
697 PROCEDURE load_table
698 IS
699 --
700 BEGIN
701 --
702 dbg('Inside load_table');
703 --
704 -- Call Pre Process
705 --
706 pre_process;
707 --
708 -- Call Process
709 --
710 process(p_full_refresh_flag => g_full_refresh);
711 --
712 -- Call Post Process
713 --
714 post_process;
715 --
716 dbg('Exiting load_table');
717 --
718 EXCEPTION
719 --
720 WHEN OTHERS THEN
721 --
722 output('Error in load_table = ');
723 output(SQLERRM);
724 RAISE;
725 --
726 END load_table;
727 --
728 END HRI_OPL_BDGTS;