[Home] [Help]
PACKAGE BODY: APPS.FII_FA_EXP_B_C
Source
1 PACKAGE BODY FII_FA_EXP_B_C AS
2 /*$Header: FIIFA01B.pls 120.11 2006/08/21 17:22:38 bridgway noship $*/
3
4 G_SOLE boolean;
5 G_PARENT boolean;
6 G_CHILD boolean;
7
8 G_NUMBER_OF_PROCESS NUMBER;
9 G_WORKER_NUM NUMBER;
10
11 g_retcode VARCHAR2(20) := NULL;
12 g_sob_id NUMBER := NULL;
13 g_from_date DATE;
14 g_to_date DATE;
15 g_lud_from_date DATE := NULL;
16 g_lud_to_date DATE := NULL;
17 g_has_lud BOOLEAN := FALSE;
18 g_fii_schema VARCHAR2(30);
19 g_prim_currency VARCHAR2(10);
20 g_sec_currency VARCHAR2(10);
21 g_prim_rate_type VARCHAR2(30);
22 g_sec_rate_type VARCHAR2(30);
23 g_prim_rate_type_name VARCHAR2(30);
24 g_sec_rate_type_name VARCHAR2(30);
25 g_primary_mau NUMBER;
26 g_secondary_mau NUMBER;
27 g_phase VARCHAR2(100);
28 g_resume_flag VARCHAR2(1) := 'N';
29 g_child_process_size NUMBER := 1000;
30 g_missing_rates NUMBER := 0;
31 g_missing_time NUMBER := 0;
32 g_fii_user_id NUMBER(15);
33 g_fii_login_id NUMBER(15);
34 g_truncate_stg BOOLEAN;
35 g_truncate_id BOOLEAN;
36 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
37 g_program_type VARCHAR2(1);
38 g_global_start_date DATE;
39
40 g_non_upgraded_ledgers BOOLEAN := FALSE;
41
42 ONE_SECOND CONSTANT NUMBER := 0.000011574; -- 1 second
43 INTERVAL CONSTANT NUMBER := 4; -- 4 days
44 MAX_LOOP CONSTANT NUMBER := 180; -- 180 loops = 180 minutes
45 LAST_PHASE CONSTANT NUMBER := 3;
46
47 G_NO_CHILD_PROCESS EXCEPTION;
48 G_CHILD_PROCESS_ISSUE EXCEPTION;
49 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
50 G_CAT_ID_FAILED EXCEPTION;
51
52 g_usage_code CONSTANT VARCHAR2(10) := 'DBI';
53
54 -- ---------------------------------------------------------------
55 -- Private procedures and Functions;
56 -- ---------------------------------------------------------------
57
58 -- ---------------------------------------------------------------
59 -- PROCEDURE CHECK_XLA_CONVERSION_STATUS
60 -- ---------------------------------------------------------------
61 PROCEDURE CHECK_XLA_CONVERSION_STATUS IS
62
63 CURSOR c_non_upgraded_ledgers IS
64 SELECT DISTINCT
65 s.ledger_id,
66 s.name
67 FROM gl_period_statuses ps,
68 gl_ledgers_public_v s,
69 fa_deprn_periods dp,
70 fa_book_controls bc,
71 (SELECT DISTINCT slga.ledger_id
72 FROM fii_slg_assignments slga,
73 fii_source_ledger_groups fslg
74 WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
75 AND fslg.usage_code = g_usage_code) fset
76 WHERE s.ledger_id = fset.ledger_id
77 AND ps.application_id = 101
78 AND ps.set_of_books_id = fset.ledger_id
79 AND ps.end_date >= g_global_Start_Date
80 AND bc.set_of_books_id = fset.ledger_id
81 AND dp.book_type_code = bc.book_type_code
82 AND dp.period_name = ps.period_name
83 AND nvl(dp.xla_conversion_status, 'UA') <> 'UA';
84
85 BEGIN
86
87 if g_debug_flag = 'Y' then
88 FII_UTIL.put_line('Calling procedure: CHECK_XLA_CONVERSION_STATUS');
89 FII_UTIL.put_line('');
90 end if;
91
92 FOR ledger_record in c_non_upgraded_ledgers LOOP
93 g_non_upgraded_ledgers := TRUE;
94
95 FII_MESSAGE.write_log(
96 msg_name => 'FII_XLA_NON_UPGRADED_LEDGER',
97 token_num => 3,
98 t1 => 'PRODUCT',
99 v1 => 'Assets',
100 t2 => 'LEDGER',
101 v2 => ledger_record.name,
102 t3 => 'START_DATE',
103 v3 => g_global_Start_Date);
104 END LOOP;
105
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 g_retcode := -1;
110 FII_UTIL.put_line('
111 ---------------------------------
112 Error in Procedure: CHECK_XLA_CONVERSION_STATUS
113 Phase: '||g_phase||'
114 Message: '||sqlerrm);
115
116 raise;
117
118
119
120 END CHECK_XLA_CONVERSION_STATUS;
121
122 -- ---------------------------------------------------------------
123 -- PROCEDURE REPORT_MISSING_RATES
124 -- ---------------------------------------------------------------
125 PROCEDURE REPORT_MISSING_RATES IS
126 TYPE cursorType is REF CURSOR;
127
128 l_stmt VARCHAR2(500);
129 l_count NUMBER;
130 l_curr CURSORTYPE;
131
132 /*
133 cursor PrimMissingRate is
134 SELECT DISTINCT
135 currency_code,
136 decode( prim_conversion_rate,
137 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
138 least(sysdate, effective_date)) effective_date
139 FROM fii_fa_exp_t
140 WHERE prim_conversion_rate < 0;
141
142 cursor SecMissingRate is
143 SELECT DISTINCT
144 currency_code,
145 decode( sec_conversion_rate,
146 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
147 least(sysdate, effective_date) ) effective_date
148 FROM fii_fa_exp_t
149 WHERE sec_conversion_rate < 0;
150 */
151
152 BEGIN
153
154 -- for first phase, just return
155 return;
156
157 /*
158
159 if g_debug_flag = 'Y' then
160 FII_UTIL.put_line('Calling procedure: REPORT_MISSING_RATES');
161 FII_UTIL.put_line('');
162 end if;
163
164 g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRateHeader to write out report header';
165 if g_debug_flag = 'Y' then
166 FII_UTIL.put_line(g_phase);
167 FII_UTIL.put_line('');
168 end if;
169
170 BIS_COLLECTION_UTILITIES.WriteMissingRateHeader;
171
172 g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRate to write out report contents';
173 if g_debug_flag = 'Y' then
174 FII_UTIL.put_line(g_phase);
175 FII_UTIL.put_line('');
176 end if;
177
178 FOR rate_record in PrimMissingRate LOOP
179 BIS_COLLECTION_UTILITIES.writemissingrate(
180 g_prim_rate_type_name,
181 rate_record.currency_code,
182 g_prim_currency,
183 rate_record.effective_7);
184 END LOOP;
185
186 FOR rate_record in SecMissingRate LOOP
187 BIS_COLLECTION_UTILITIES.writemissingrate(
188 g_sec_rate_type_name,
189 rate_record.currency_code,
190 g_sec_currency,
191 rate_record.effective_date);
192 END LOOP;
193
194 FND_FILE.CLOSE;
195 */
196
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 g_retcode:=-1;
200 FII_UTIL.put_line('
201 ---------------------------------------------------
202 Error in Procedure: REPORT_MISSING_RATES
203 Phase: '||g_phase||'
204 Message: Should have missing rates but found none');
205
206 raise;
207
208 WHEN OTHERS THEN
209 g_retcode := -1;
210 FII_UTIL.put_line('
211 ---------------------------------
212 Error in Procedure: REPORT_MISSING_RATES
213 Phase: '||g_phase||'
214 Message: '||sqlerrm);
215
216 raise;
217
218 END REPORT_MISSING_RATES;
219
220
221 -----------------------------------------------------------------------
222 -- PROCEDURE GET_ACCT_CLASSES
223 -----------------------------------------------------------------------
224
225 PROCEDURE get_acct_classes is
226
227 l_stmt VARCHAR2(50);
228
229 BEGIN
230
231 if g_debug_flag = 'Y' then
232 FII_UTIL.put_line('Calling procedure: GET_ACCT_CLASSES');
233 FII_UTIL.put_line('');
234 end if;
235
236 insert into FII_FA_ACCT_CLASS_CODE_GT
237 (accounting_class_code, ledger_id)
238 SELECT XACA.accounting_class_code,
239 fset.ledger_id
240 FROM xla_post_acct_progs_b XPAP,
241 xla_assignment_defns_b XAD,
242 xla_acct_class_assgns XACA,
243 (SELECT DISTINCT slga.ledger_id
244 FROM fii_slg_assignments slga,
245 fii_source_ledger_groups fslg
246 WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
247 AND fslg.usage_code = g_usage_code) fset
248 WHERE XPAP.program_owner_code = 'S'
249 AND XPAP.program_code = 'ASSETS DBI EXPENSES'
250 AND XPAP.application_id = 450
251 AND XAD.program_code = XPAP.program_code
252 AND XAD.enabled_flag = 'Y'
253 AND XAD.ledger_id = fset.ledger_id
254 AND XACA.program_code = XAD.program_code
255 AND XACA.assignment_code = XAD.assignment_code
256 UNION
257 SELECT XACA.accounting_class_code,
258 fset.ledger_id
259 FROM xla_post_acct_progs_b XPAP,
260 xla_assignment_defns_b XAD,
261 xla_acct_class_assgns XACA,
262 (SELECT DISTINCT slga.ledger_id
263 FROM fii_slg_assignments slga,
264 fii_source_ledger_groups fslg
265 WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
266 AND fslg.usage_code = g_usage_code) fset
267 WHERE XPAP.program_owner_code = 'S'
268 AND XPAP.program_code = 'ASSETS DBI EXPENSES'
269 AND XPAP.application_id = 450
270 AND XAD.program_code = XPAP.program_code
271 AND XAD.enabled_flag = 'Y'
272 AND XAD.ledger_id is null
273 AND XACA.program_code = XAD.program_code
274 AND XACA.assignment_code = XAD.assignment_code
275 AND not exists
276 (select 1
277 from xla_assignment_defns_b XAD2
278 where xad2.ledger_id = fset.ledger_id);
279
280 if g_debug_flag = 'Y' then
281 FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' rows into FII_FA_ACCT_CLASS_CODE_GT');
282 FII_UTIL.stop_timer;
283 FII_UTIL.print_timer('Duration');
284 end if;
285
286 EXCEPTION
287 WHEN OTHERS THEN
288 g_retcode := -1;
289 FII_UTIL.put_line('
290 ---------------------------------
291 Error in Procedure: GET_ACCT_CLASSES
292 Phase: '||g_phase||'
293 Message: '||sqlerrm);
294
295 raise;
296
297
298 END GET_ACCT_CLASSES;
299
300 -----------------------------------------------------------------------
301 -- PROCEDURE INIT
302 -----------------------------------------------------------------------
303 PROCEDURE Init is
304
305 l_stmt VARCHAR2(50);
306
307 BEGIN
308
309 if g_debug_flag = 'Y' then
310 FII_UTIL.put_line('Calling procedure: INIT');
311 FII_UTIL.put_line('');
312 end if;
313
314 -- -------------------------------------------
315 -- Turn on parallel insert/dml for the session
316 -- Commit to terminate any open transactions
317 -- This will avoid issue with not being able
318 -- to run ddl within a transaction
319 -- -------------------------------------------
320 /*g_phase := 'Altering session to enable parallel DML';
321 commit;
322
323 -- *** avoiding this as it's causing:
324 -- ORA-12838: cannot read/modify an object after modifying it in parallel
325
326 if (G_ = 'L') then
327 l_stmt :='ALTER SESSION ENABLE PARALLEL DML';
328
329 execute immediate l_stmt;
330 end if;
331
332 ----------------------------------------------------------
333 -- Find the schema owner of FII
334 ----------------------------------------------------------
335 g_phase := 'Find FII schema';
336 g_fii_schema := FII_UTIL.get_schema_name ('FII');
337
338 /*
339 --------------------------------------------------------------
340 -- Find all currency related information
341 --------------------------------------------------------------
342 g_phase := 'Find currency information';
343
344 g_primary_mau := nvl(fii_currency.get_mau_primary, 0.01 );
345 g_secondary_mau := nvl(fii_currency.get_mau_secondary, 0.01);
346 g_prim_currency := bis_common_parameters.get_currency_code;
347 g_sec_currency := bis_common_parameters.get_secondary_currency_code;
348 g_prim_rate_type := bis_common_parameters.get_rate_type;
349 g_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
350
351 begin
352 g_phase := 'Convert rate_type to rate_type_name';
353
354 select user_conversion_type into g_prim_rate_type_name
355 from gl_daily_conversion_types
356 where conversion_type = g_prim_rate_type;
357
358 if g_sec_rate_type is not null then
359 select user_conversion_type into g_sec_rate_type_name
360 from gl_daily_conversion_types
361 where conversion_type = g_sec_rate_type;
362 else
363 g_sec_rate_type_name := null;
364 end if;
365
366 exception
367 when others then
368 fii_util.write_log('Failed to convert rate_type to rate_type_name' );
369 raise;
370 end;
371 */
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 g_retcode := -1;
376 FII_UTIL.put_line('
377 ---------------------------------
378 Error in Procedure: INIT
379 Phase: '||g_phase||'
380 Message: '||sqlerrm);
381
382 raise;
383
384 END Init;
385
386 -----------------------------------------------------------------
387 -- FUNCTION CHECK_IF_SLG_SET_UP_CHANGE
388 --
389 -- FA NOTE: this will hopefully become and API if still needed
390 -- so we're not dupicating code from FIIGL03B.pls
391 -----------------------------------------------------------------
392 FUNCTION CHECK_IF_SLG_SET_UP_CHANGE RETURN VARCHAR2 IS
393
394 l_slg_chg VARCHAR2(10);
395 l_count1 number := 0;
396 l_count2 number := 0;
397
398 BEGIN
399
400 g_phase := 'Check if Source Legder Assignments setup has changed';
401 if g_debug_flag = 'Y' then
402 FII_UTIL.put_line(g_phase);
403 end if;
404
405 SELECT DECODE(item_value, 'Y', 'TRUE', 'FALSE')
406 INTO l_slg_chg
407 FROM fii_change_log
408 WHERE log_item = 'FA_RESUMMARIZE';
409
410 IF l_slg_chg = 'TRUE' THEN
411
412 g_phase := 'Reach l_slg_chg = TRUE';
413
414 begin
415
416 SELECT 1
417 INTO l_count1
418 FROM fii_fa_exp_f
419 WHERE ROWNUM = 1;
420 exception
421 when NO_DATA_FOUND then
422 l_count1 := 0;
423 end;
424
425 begin
426 SELECT 1
427 INTO l_count2
428 FROM fii_fa_exp_t
429 WHERE ROWNUM = 1;
430 exception
431 when NO_DATA_FOUND then
432 l_count2 := 0;
433 end;
434
435
436 IF (l_count1 = 0 AND l_count2 = 0) then
437 g_phase := 'Updating fii_change_log for log_item FA_RESUMMARIZE';
438 UPDATE fii_change_log
439 SET item_value = 'N',
440 last_update_date = SYSDATE,
441 last_update_login = g_fii_login_id,
442 last_updated_by = g_fii_user_id
443 WHERE log_item = 'FA_RESUMMARIZE'
444 AND item_value = 'Y';
445
446 COMMIT;
447
448 l_slg_chg := 'FALSE';
449 END IF;
450
451 END IF;
452
453 RETURN l_slg_chg;
454
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 RETURN 'FALSE';
458 WHEN OTHERS THEN
459 g_retcode := -1;
460 FII_UTIL.put_line('
461 -----------------------------
462 Error occured in Funcation: CHECK_IF_SLG_SET_UP_CHANGE
463 Phase: '||g_phase||'
464 Message: ' || sqlerrm);
465
466 raise;
467
468 END CHECK_IF_SLG_SET_UP_CHANGE;
469
470
471 -----------------------------------------------------------------
472 -- PROCEDURE REGISTER_JOBS
473 --
474 -- FA NOTE: this currently NOT used for our method of paralization
475 -- pending DBI/perf review, as we may change and need this,
476 -- so keeping it and it's original GL layout here
477 -----------------------------------------------------------------
478 PROCEDURE REGISTER_JOBS IS
479
480 l_max_number NUMBER;
481 l_start_number NUMBER;
482 l_end_number NUMBER;
483 l_count NUMBER := 0;
484
485 BEGIN
486
487 if g_debug_flag = 'Y' then
488 FII_UTIL.put_line('Calling procedure: REGISTER_JOBS');
489 FII_UTIL.put_line('');
490 end if;
491
492 g_phase := 'Register jobs for workers';
493 if g_debug_flag = 'Y' then
494 FII_UTIL.put_line('Register jobs for workers');
495 end if;
496
497 ------------------------------------------------------------
498 -- select min and max sequence IDs from your ID Temp table
499 ------------------------------------------------------------
500 g_phase := 'select min and max dist ids';
501
502 SELECT NVL(max(record_id), 0), nvl(min(record_id),1)
503 INTO l_max_number, l_start_number
504 FROM FII_FA_NEW_EXP_HDR_IDS;
505
506 WHILE (l_start_number <= l_max_number) LOOP
507 l_end_number:= l_start_number + g_child_process_size;
508 g_phase := 'Loop to insert into FII_FA_WORKER_JOBS: '
509 || l_start_number || ', ' || l_end_number;
510 INSERT INTO FII_FA_WORKER_JOBS (start_range, end_range, worker_number, status)
511 VALUES (l_start_number, least(l_end_number, l_max_number), 0, 'UNASSIGNED');
512 l_count := l_count + 1;
513 l_start_number := least(l_end_number, l_max_number) + 1;
514 END LOOP;
515
516 if g_debug_flag = 'Y' then
517 FII_UTIL.put_line('Inserted ' || l_count || ' jobs into FII_FA_WORKER_JOBS table');
518 end if;
519
520 COMMIT;
521
522 EXCEPTION
523 WHEN OTHERS THEN
524 g_retcode := -1;
525 FII_UTIL.put_line('
526 ---------------------------------
527 Error in Procedure: REGISTER_JOBS
528 Phase: '||g_phase||'
529 Message: '||sqlerrm);
530 RAISE;
531
532 END REGISTER_JOBS;
533
534 -----------------------------------------------------------------------
535 -- FUNCTION LAUNCH_WORKERS
536 --
537 -- FA NOTE: different from FII's utilization in that we
538 -- are reusing the same conc definition for parent and child
539 --
540 -----------------------------------------------------------------------
541 PROCEDURE LAUNCH_WORKERS(p_number_of_workers NUMBER) IS
542
543 l_request_id NUMBER;
544
545 BEGIN
546
547 FOR i IN 1..p_number_of_workers LOOP
548
549
550 l_request_id := FND_REQUEST.SUBMIT_REQUEST('FII',
551 'FII_FA_EXP_B_C',
552 NULL,
553 NULL,
554 FALSE,
555 p_number_of_workers,
556 i,
557 'I',
558 1,
559 20000000,
560 20000000);
561
562 if g_debug_flag = 'Y' then
563 FII_util.put_line(' Worker '||i||' request id: '||l_request_id);
564 end if;
565
566 IF (l_request_id = 0) THEN
567 rollback;
568 g_retcode := -1;
569 FII_UTIL.put_line('
570 ---------------------------------
571 Error in Procedure: LAUNCH_WORKERS
572 Message: '||fnd_message.get);
573 raise G_NO_CHILD_PROCESS;
574 END IF;
575
576
577 END LOOP;
578
579 COMMIT; -- moved from iteration level per Renu
580
581 EXCEPTION
582 WHEN G_NO_CHILD_PROCESS THEN
583 g_retcode := -1;
584 FII_UTIL.put_line('No child process launched');
585 raise;
586
587 WHEN OTHERS THEN
588 rollback;
589 g_retcode := -1;
590 FII_UTIL.put_line('
591 ---------------------------------
592 Error in Procedure: LAUNCH_WORKERS
593 Message: '||sqlerrm);
594
595 raise;
596
597 END LAUNCH_WORKERS;
598
599 -----------------------------------------------------------------------
600 -- PROCEDURE CHILD_SETUP
601 --
602 -- FA NOTE: unsure if this is needed????
603 -----------------------------------------------------------------------
604 PROCEDURE CHILD_SETUP(p_object_name VARCHAR2) IS
605
606 l_dir VARCHAR2(400);
607 l_stmt VARCHAR2(100);
608
609 BEGIN
610
611 g_phase := 'Calling ALTER SESSION SET global_names = false ';
612 l_stmt := 'ALTER SESSION SET global_names = false';
613 EXECUTE IMMEDIATE l_stmt;
614
615 g_fii_user_id := FND_GLOBAL.User_Id;
616 g_fii_login_id := FND_GLOBAL.Login_Id;
617
618 EXCEPTION
619 WHEN OTHERS THEN
620 rollback;
621 g_retcode := -1;
622 FII_UTIL.put_line('
623 ---------------------------------
624 Error in Procedure: CHILD_SETUP
625 Phase: '||g_phase||'
626 Message: '||sqlerrm);
627
628 raise;
629
630 END CHILD_SETUP;
631
632 --------------------------------------------------------------------
633 -- PROCEDURE SUMMARY_ERR_CHECK
634 --
635 -- FA NOTE: not used in first phase since we won't do global
636 -- currencies yet, but retaining for when we do currently
637 -- references original GL tables which would change
638 --------------------------------------------------------------------
639 PROCEDURE SUMMARY_ERR_CHECK IS
640
641 l_conv_rate_cnt NUMBER :=0;
642 l_stg_min DATE;
643 l_stg_max DATE;
644 l_row_cnt NUMBER;
645 l_check_time_dim BOOLEAN;
646
647 BEGIN
648
649 g_phase := 'Checking for missing rates';
650 if g_debug_flag = 'Y' then
651 FII_UTIL.put_line(g_phase);
652 end if;
653
654
655 --------------------------------------------------------
656 -- FA's initial version doesn't handle global currencies
657 -- skipping this
658 ------------------------------------------------------
659
660 /*
661
662 ------------------------------------------------------
663 -- If there are missing exchange rates indicated in
664 -- the staging table, then call report_missing_rates
665 -- API to print out the missing rates report
666 ------------------------------------------------------
667 IF (g_program_type = 'L') THEN
668 g_phase := 'For g_program_type = L ';
669 SELECT MIN(trx_date), MAX(trx_date), sum(decode(sign(prim_conversion_rate), -1, 1, 0)) +
670 sum(decode(sign(sec_conversion_rate), -1, 1, 0)), count(*)
671 INTO l_stg_min, l_stg_max, l_conv_rate_cnt, l_row_cnt
672 FROM FII_GL_REVENUE_RATES_TEMP;
673
674 ELSE
675
676 g_phase := 'For g_program_type <> L ';
677 SELECT MIN(effective_date), MAX(effective_date), sum(decode(sign(prim_conversion_rate), -1, 1, 0)) +
678 sum(decode(sign(sec_conversion_rate), -1, 1, 0)), count(*)
679 INTO l_stg_min, l_stg_max, l_conv_rate_cnt, l_row_cnt
680 FROM FII_GL_JE_SUMMARY_STG;
681
682 END IF;
683
684 IF l_row_cnt = 0 THEN
685 IF g_debug_flag = 'Y' THEN
686 FII_UTIL.put_line('Summary Error Check completed successfully, no data found!');
687 END IF;
688 RETURN;
689 END IF;
690
691 IF (l_conv_rate_cnt >0) THEN
692 -------------------------------------------------
693 -- Write out translated message to let user know
694 -- there are missing exchange rate information
695 -------------------------------------------------
696 FII_MESSAGE.write_output (msg_name => 'FII_MISS_EXCH_RATE_FOUND', token_num => 0);
697 FII_MESSAGE.write_log (msg_name => 'FII_MISS_EXCH_RATE_FOUND', token_num => 0);
698 FII_MESSAGE.write_log (msg_name => 'FII_REFER_TO_OUTPUT', token_num => 0);
699
700 --FII_UTIL.put_line('Missing currency conversion rates found, program will exit with error status. Please fix the missing conversion rates');
701
702 g_retcode := -1;
703 g_missing_rates := 1;
704 IF g_program_type = 'L' THEN
705 REPORT_MISSING_RATES_L;
706 ELSE
707 REPORT_MISSING_RATES;
708 END IF;
709 RETURN;
710 END IF;
711
712 g_phase := 'Checking for Time dimension';
713 if g_debug_flag = 'Y' then
714 FII_UTIL.put_line(g_phase);
715 end if;
716
717 */
718
719
720 -----------------------------------------------------------
721 -- If we find record in the staging table which references
722 -- time records which does not exist in FII_TIME_DAY
723 -- table, then we will exit the program with error status
724 --
725 -- moving this out from comments per Renu
726 -----------------------------------------------------------
727
728 -- FII_TIME_API.check_missing_date (l_stg_min, l_stg_max, l_check_time_dim);
729
730 --------------------------------------
731 -- If there are missing time records
732 --------------------------------------
733 IF (l_check_time_dim) THEN
734
735 FII_MESSAGE.write_output (msg_name => 'FII_TIME_DIM_STALE', token_num => 0);
736 FII_MESSAGE.write_log (msg_name => 'FII_TIME_DIM_STALE', token_num => 0);
737 FII_MESSAGE.write_log (msg_name => 'FII_REFER_TO_OUTPUT', token_num => 0);
738
739 --FII_UTIL.put_line('Time Dimension is not fully populated. Please populate Time dimension to cover the date range you are collecting');
740
741 g_retcode := -1; --we set it error out for missing time
742 g_missing_time := 1;
743 RETURN;
744 END IF;
745
746 if g_debug_flag = 'Y' then
747 FII_UTIL.put_line('Summary Error Check completed successfully, no error found!');
748 end if;
749 RETURN;
750
751 EXCEPTION
752 WHEN OTHERS THEN
753 g_retcode := -1;
754 FII_UTIL.put_line('
755 ---------------------------------
756 Error occured in Summary_err_check function
757 Phase: '||g_phase||'
758 Message: '||sqlerrm);
759
760 Raise;
761
762 END Summary_err_check;
763
764 -----------------------------------------------------------------------
765 -- PROCEDURE CLEAN_UP
766 -----------------------------------------------------------------------
767 PROCEDURE Clean_Up IS
768
769 l_ret_code varchar2(30);
770
771 BEGIN
772
773 if g_debug_flag = 'Y' then
774 FII_UTIL.put_line('Calling procedure: CLEAN_UP');
775 end if;
776
777 ------------------------------------------------------
778 -- Current plan is to not use a worker table
779 ------------------------------------------------------
780 FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_FA_WORKER_JOBS',
781 P_RETCODE => l_ret_code);
782
783 IF (g_truncate_id) THEN
784 FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_FA_NEW_EXP_HDR_IDS',
785 P_RETCODE => l_ret_code);
786 END IF;
787
788 IF (g_truncate_stg) THEN
789 FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_FA_EXP_T',
790 P_RETCODE => l_ret_code);
791
792 END IF;
793
794 COMMIT;
795
796 EXCEPTION
797 WHEN OTHERS Then
798 g_retcode:=-1;
799 FII_UTIL.put_line('
800 ---------------------------------
801 Error in Procedure: Clean_Up
802 Message: ' || sqlerrm);
803
804 RAISE;
805
806 END Clean_up;
807
808 ------------------------------------------------------------------------
809 -- PROCEDURE JOURNALS_PROCESSED
810 --
811 -- NOTE: simply moves lines from the initial table used for selection
812 -- to the table which will permanently flag them as processed
813 ------------------------------------------------------------------------
814 PROCEDURE JOURNALS_PROCESSED IS
815
816 BEGIN
817
818 if g_debug_flag = 'Y' then
819 FII_UTIL.put_line ('Calling Journals_Processed Procedure');
820 FII_UTIL.start_timer;
821 end if;
822
823 ---------------------------------------------------------------------
824 -- Inserting processed JE Header IDs into FII_FA_EXP_HDR_IDS
825 -- table. Not all JE Header IDs in FII_FA_NEW_EXP_HDR_IDS are
826 -- processed. This is because when we select Header IDs to be
827 -- processed (refer to NEW_JOURNALS function), we only filter by SOB
828 -- in FII_COMPANY_SETS table, however when we extract data from OLTP
829 -- tables, we actually filter data by both SOB and Company
830 ---------------------------------------------------------------------
831
832 INSERT INTO fii_fa_exp_hdr_ids (
833 je_header_id,
834 creation_date,
835 created_by,
836 last_update_date,
837 last_update_login,
838 last_updated_by)
839 SELECT distinct
840 je_header_id,
841 sysdate,
842 g_fii_user_id,
843 sysdate,
844 g_fii_login_id,
845 g_fii_user_id
846 FROM fii_fa_new_exp_hdr_ids;
847
848 if g_debug_flag = 'Y' then
849 FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' rows into FII_FA_EXP_HDR_IDS');
850 FII_UTIL.stop_timer;
851 FII_UTIL.print_timer('Duration');
852 end if;
853
854 Exception
855 WHEN OTHERS Then
856 g_retcode := -1;
857 FII_UTIL.put_line('
858 ----------------------------
859 Error in Function: Journal_processed
860 Message: '||sqlerrm);
861 ROLLBACK;
862 raise;
863 END Journals_Processed;
864
865 -----------------------------------------------------------------------
866 -- FUNCTION NEW_JOURNALS
867 --
868 -- FA NOTE: find new lines to processed based on a combination of
869 -- our GL/SLA/FA tables for tracking posted journals
870 -----------------------------------------------------------------------
871 Function New_Journals(P_Start_Date IN DATE,
872 P_End_Date IN DATE) RETURN NUMBER IS
873
874 l_number_of_rows NUMBER :=0;
875
876 BEGIN
877
878 ----------------------------------------------------------------------
879 -- Insert into a table to hold GL and AE header ids which have not been
880 -- processed (and would not exist in the table)
881 --
882 -- The Journal must be posted (and not reversed/rolled back)
883 -- And Journal entry line effective date falls within user specified
884 -- date range.
885 --
886 -- For assets, this selection breaks into 4 pieces:
887 -- 1) current logic using the FA_JOURNAL_ENTRIES table for audit
888 -- where status would be C
889 -- 2) older logic where this table did not exist
890 -- (this only needs to be concidered in the Initial Load
891 -- 3) journals already extracted to base summary but since rolled
892 -- back (status = B)
893 --
894 -----------------------------------------------------------------------
895 if g_debug_flag = 'Y' then
896 FII_UTIL.put_line(' ');
897 FII_UTIL.put_line('Inserting New Journal header ids');
898 FII_UTIL.start_timer;
899 end if;
900
901 if (g_program_type <> 'L') then
902
903 -- incremental mode only!
904 -- fetch any journal runs which have been previously extracted to
905 -- DBI but rolled back since
906
907 -- NOTE FIX THIS - should probably put in stage then update later (update or new row?)
908
909 insert into
910 fii_fa_new_exp_hdr_ids
911 (JE_HEADER_ID ,
912 AE_HEADER_ID ,
913 EVENT_TYPE_CODE ,
914 EVENT_ID ,
915 LEDGER_ID ,
916 CREATION_DATE ,
917 CREATED_BY ,
918 LAST_UPDATE_DATE ,
919 LAST_UPDATED_BY ,
920 LAST_UPDATE_LOGIN,
921 RECORD_ID)
922 select nid.je_header_id,
923 nid.ae_header_id,
924 nid.event_type_code,
925 nid.event_id,
926 nid.ledger_id,
927 sysdate,
928 1,
929 sysdate,
930 1,
931 1,
932 rownum
933 from (select distinct
934 glh.JE_HEADER_ID ,
935 xlah.ae_Header_id ,
936 xlah.event_type_code ,
937 xlah.event_id ,
938 glh.ledger_id
939 from fii_gl_processed_header_ids fiiglh,
940 gl_je_headers glh,
941 gl_import_references gir,
942 xla_ae_lines xlal,
943 xla_ae_headers xlah,
944 xla_subledgers xlasl,
945 (SELECT p.period_name,
946 s.ledger_id
947 FROM gl_periods p,
948 gl_ledgers_public_v s
949 WHERE p.end_date >= g_global_Start_Date
950 AND p.period_set_name = s.period_set_name) per,
951 (SELECT DISTINCT slga.ledger_id
952 FROM fii_slg_assignments slga,
953 fii_source_ledger_groups fslg
954 WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
955 AND fslg.usage_code = g_usage_code) fset
956 where xlasl.application_id = 140
957 and glh.JE_SOURCE = xlasl.je_source_name
958 and fiiglh.je_header_id = glh.je_header_id
959 and gir.je_header_id = glh.je_header_id
960 and gir.gl_sl_link_id = xlal.gl_sl_link_id
961 and xlal.ae_header_id = xlah.ae_header_id
962 and xlal.application_id = 140
963 and xlah.application_id = 140
964 and glh.period_name = per.period_name
965 and glh.ledger_id = per.ledger_id
966 and glh.ledger_id = fset.ledger_id
967 and glh.ledger_id = xlah.ledger_id
968 and not exists
969 (select 1
970 from fii_fa_exp_hdr_ids faph
971 where faph.je_header_id = fiiglh.je_header_id)) nid;
972
973 l_number_of_rows := SQL%ROWCOUNT;
974
975 if g_debug_flag = 'Y' then
976 FII_UTIL.put_line('Inserted '||l_number_of_rows||
977 ' JE header IDs into FII_FA_NEW_EXP_HDR_IDS for new entries');
978 FII_UTIL.stop_timer;
979 FII_UTIL.print_timer('Duration');
980 FII_UTIL.put_line('');
981 end if;
982
983 commit;
984
985 else -- initial
986
987 -- Fetch all rows from FA_JOURNAL_ENTRIES which are not rolled back
988 -- and insert into FII_FA_EXP_HDR_IDS table.
989
990 -- R12: for efficiency, we will use group_id instead of ae_header_id
991 -- for access in initial mode
992
993 -- R12: note that there is a potential for multiple deprn events for
994 -- the same entity as well as the rollback events to be picked up here
995 -- we want to insure we only pick up events which have not been reversed
996 -- Since this is the header level, we will do this later on
997
998 -- BUG# 4996218
999 -- remove reliance on group_id
1000
1001 insert /*+ append parallel(i) */
1002 into fii_fa_new_exp_hdr_ids i
1003 (JE_HEADER_ID ,
1004 LEDGER_ID ,
1005 CREATION_DATE ,
1006 CREATED_BY ,
1007 LAST_UPDATE_DATE ,
1008 LAST_UPDATED_BY ,
1009 LAST_UPDATE_LOGIN)
1010 select /*+ parallel(fiiglh) parallel(glh) parallel(xlash) parallel(per) parallel(fset) */
1011 distinct glh.JE_HEADER_ID ,
1012 glh.ledger_id ,
1013 sysdate,
1014 1,
1015 sysdate,
1016 1,
1017 1
1018 from fii_gl_processed_header_ids fiiglh,
1019 gl_je_headers glh,
1020 xla_subledgers xlasl,
1021 (SELECT p.period_name,
1022 s.ledger_id
1023 FROM gl_periods p,
1024 gl_ledgers_public_v s
1025 WHERE p.end_date >= g_global_Start_Date
1026 AND p.period_set_name = s.period_set_name) per,
1027 (SELECT DISTINCT slga.ledger_id
1028 FROM fii_slg_assignments slga,
1029 fii_source_ledger_groups fslg
1030 WHERE slga.source_ledger_group_id = fslg.source_ledger_group_id
1031 AND fslg.usage_code = g_usage_code) fset
1032 where xlasl.application_id = 140
1033 and glh.JE_SOURCE = xlasl.je_source_name
1034 and fiiglh.je_header_id = glh.je_header_id
1035 and glh.period_name = per.period_name
1036 and glh.ledger_id = per.ledger_id
1037 and glh.ledger_id = fset.ledger_id;
1038
1039 l_number_of_rows := SQL%ROWCOUNT;
1040 if g_debug_flag = 'Y' then
1041 FII_UTIL.put_line('Inserted '||l_number_of_rows||
1042 ' JE header IDs into FII_FA_NEW_EXP_HDR_IDS for main processing');
1043 FII_UTIL.stop_timer;
1044 FII_UTIL.print_timer('Duration');
1045 FII_UTIL.put_line('');
1046 end if;
1047
1048 commit;
1049
1050 end if;
1051
1052 COMMIT;
1053 return(l_number_of_rows);
1054
1055 Exception
1056 WHEN OTHERS Then
1057 g_retcode := -1;
1058 FII_UTIL.put_line('
1059 ----------------------------
1060 Error in New_Journals Procedure
1061 Message: '||sqlerrm);
1062 RAISE;
1063 END New_Journals;
1064
1065
1066 ---------------------------------------------------------------
1067 -- PROCEDURE DELETE_FROM_BASE_SUMMARY
1068 ---------------------------------------------------------------
1069 PROCEDURE DELETE_FROM_BASE_SUMMARY (p_start_range IN NUMBER,
1070 p_end_range IN NUMBER) IS
1071
1072 l_count number;
1073
1074 BEGIN
1075
1076 if g_debug_flag = 'Y' then
1077 FII_UTIL.put_line ('Calling Delete_From_Base_Summary Procedure');
1078 FII_UTIL.start_timer;
1079 end if;
1080
1081 delete from fii_fa_exp_f
1082 where xla_event_id in
1083 (select ev_dep.event_id
1084 from fii_fa_new_exp_hdr_ids nid,
1085 xla_events ev_rb,
1086 xla_events ev_dep
1087 where nid.record_id between p_start_range and p_end_range
1088 and nid.event_type_code = 'ROLLBACK_DEPRECIATION'
1089 and ev_rb.event_id = nid.event_id
1090 and ev_rb.application_id = 140
1091 and ev_dep.entity_id = ev_rb.entity_id
1092 and ev_dep.application_id = 140
1093 and ev_rb.event_id > ev_dep.event_id);
1094
1095 l_count := SQL%ROWCOUNT;
1096
1097 commit;
1098
1099 if g_debug_flag = 'Y' then
1100 FII_UTIL.put_line('Deleted '|| l_count ||
1101 ' lines from FII_FA_EXP_F for rolled back entries');
1102 FII_UTIL.stop_timer;
1103 FII_UTIL.print_timer('Duration');
1104 FII_UTIL.put_line('');
1105 end if;
1106
1107
1108 Exception
1109 WHEN OTHERS Then
1110 g_retcode := -1;
1111 FII_UTIL.put_line('
1112 ----------------------------
1113 Error in Delete_From_Base_Summary Procedure
1114 Message: '||sqlerrm);
1115 RAISE;
1116 END Delete_From_Base_Summary;
1117
1118
1119 ---------------------------------------------------------------
1120 -- PROCEDURE VERIFY_CAT_ID_UP_TO_DATE
1121 ---------------------------------------------------------------
1122 PROCEDURE VERIFY_CAT_ID_UP_TO_DATE IS
1123
1124 l_errbuf VARCHAR2(1000);
1125 l_retcode VARCHAR2(100);
1126 l_request_id NUMBER;
1127 l_result BOOLEAN;
1128 l_phase VARCHAR2(500) := NULL;
1129 l_status VARCHAR2(500) := NULL;
1130 l_devphase VARCHAR2(500) := 'PENDING';
1131 l_devstatus VARCHAR2(500) := NULL;
1132 l_message VARCHAR2(500) := NULL;
1133 l_dummy BOOLEAN;
1134 l_call_status boolean;
1135
1136 BEGIN
1137
1138 if g_debug_flag = 'Y' then
1139 FII_UTIL.put_line('Calling Procedure: VERIFY_CAT_ID_UP_TO_DATE');
1140 FII_UTIL.put_line('');
1141 end if;
1142
1143 IF(FII_FA_CAT_C.NEW_CAT_IN_FA) THEN
1144
1145 if g_debug_flag = 'Y' then
1146 FII_UTIL.put_line('CAT_ID Dimension is not up to date, calling CAT_ID Dimension update program');
1147 end if;
1148
1149 g_phase := 'Calling CAT_ID Dimension update program';
1150 l_dummy := FND_REQUEST.SET_MODE(TRUE);
1151 l_request_id := FND_REQUEST.SUBMIT_REQUEST('FII', 'FII_FA_CAT_ID_C',
1152 NULL, NULL, FALSE, 'I');
1153 commit;
1154
1155 IF (l_request_id = 0) THEN
1156 rollback;
1157 g_retcode := -1;
1158 FII_UTIL.put_line('
1159 ---------------------------------
1160 Error in Procedure: VERIFY_CAT_ID_UP_TO_DATE
1161 Message: '||fnd_message.get);
1162 raise G_NO_CHILD_PROCESS;
1163 END IF;
1164
1165 g_phase := 'Calling FND_CONCURRENT.wait_for_request';
1166 l_result := FND_CONCURRENT.wait_for_request(request_id => l_request_id,
1167 interval => 30,
1168 max_wait => 3600,
1169 phase => l_phase,
1170 status => l_status,
1171 dev_phase => l_devphase,
1172 dev_status => l_devstatus,
1173 message => l_message);
1174
1175 g_phase := 'Finished calling FND_CONCURRENT.wait_for_request -> ' || l_devphase || ', ' || l_devstatus;
1176
1177 IF (NVL(l_devphase='COMPLETE' AND l_devstatus='NORMAL', FALSE)) THEN
1178 if g_debug_flag = 'Y' then
1179 FII_UTIL.put_line('CAT_ID Dimension populated successfully');
1180 end if;
1181 ELSE
1182 if g_debug_flag = 'Y' then
1183 FII_UTIL.put_line('CAT_ID Dimension populated unsuccessfully');
1184 end if;
1185 raise G_CAT_ID_FAILED;
1186 END IF;
1187
1188 ELSE
1189
1190 if g_debug_flag = 'Y' then
1191 FII_UTIL.put_line('CAT_ID Dimension is up to date');
1192 FII_UTIL.put_line('');
1193 end if;
1194
1195 END IF;
1196
1197 EXCEPTION
1198 WHEN G_CAT_ID_FAILED THEN
1199 g_retcode := -1;
1200 FII_UTIL.put_line('
1201 ----------------------------
1202 Error in Procedure : VERIFY_CAT_ID_UP_TO_DATE when running CAT_ID program
1203 Phase: ' || g_phase);
1204 raise;
1205
1206 WHEN OTHERS Then
1207 g_retcode := -1;
1208 FII_UTIL.put_line('
1209 ----------------------------
1210 Error in Procedure : VERIFY_CAT_ID_UP_TO_DATE
1211 Phase: ' || g_phase || '
1212 Message: '||sqlerrm);
1213
1214 raise;
1215
1216 END VERIFY_CAT_ID_UP_TO_DATE;
1217
1218
1219
1220
1221 ----------------------------------------
1222 -- PROCEDURE Insert_Into_Rates
1223 --
1224 -- FA NOTE: not used in first phase - still refresnces orignal gl tables
1225 ----------------------------------------
1226
1227 PROCEDURE INSERT_INTO_RATES IS
1228
1229 l_global_prim_curr_code VARCHAR2(30);
1230 l_global_sec_curr_code VARCHAR2(30);
1231
1232 BEGIN
1233
1234 -----------------------
1235 -- for now, no rates
1236 -----------------------
1237 return;
1238
1239 /*
1240 g_phase := 'Calling bis_common_parameters.get_currency_code';
1241
1242 l_global_prim_curr_code := bis_common_parameters.get_currency_code;
1243 l_global_sec_curr_code := bis_common_parameters.get_secondary_currency_code;
1244
1245 if g_debug_flag = 'Y' then
1246 fii_util.put_line(' ');
1247 fii_util.put_line('Loading data into rates table');
1248 fii_util.start_timer;
1249 fii_util.put_line('');
1250 end if;
1251
1252 g_phase := 'Inserting into fii_fa_exp_rates_temp';
1253
1254 insert into fii_fa_exp_rates_temp
1255 (FUNCTIONAL_CURRENCY,
1256 TRX_DATE,
1257 PRIM_CONVERSION_RATE,
1258 SEC_CONVERSION_RATE)
1259 select cc functional_currency,
1260 dt trx_date,
1261 decode(cc, l_global_prim_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY (cc,least(sysdate, dt))) PRIM_CONVERSION_RATE,
1262 decode(cc, l_global_sec_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(cc,least(sysdate, dt))) SEC_CONVERSION_RATE
1263 from (
1264 select distinct
1265 FUNCTIONAL_CURRENCY cc,
1266 account_date dt
1267 from fii_fa_exp_t
1268 );
1269
1270
1271 --Call FND_STATS to collect statistics after populating the table
1272 g_phase := 'Calling FND_STATS to collect statistics for fii_gl_revenue_rates_temp';
1273 FND_STATS.gather_table_stats
1274 (ownname => g_fii_schema,
1275 tabname => 'FII_GL_REVENUE_RATES_TEMP');
1276
1277 if g_debug_flag = 'Y' then
1278 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_revenue_rates_temp');
1279 fii_util.stop_timer;
1280 fii_util.print_timer('Duration');
1281 end if;
1282
1283 */
1284
1285 /* + no_merge parallel(fii_fa_exp_t)*/
1286
1287 EXCEPTION
1288 WHEN OTHERS Then
1289 g_retcode := -1;
1290 FII_UTIL.put_line('
1291 ----------------------------
1292 Error in Function: Insert_Into_Rates
1293 Phase: ' || g_phase || '
1294 Message: '||sqlerrm);
1295
1296 raise;
1297
1298 END INSERT_INTO_RATES;
1299
1300
1301 ------------------------------------------
1302 -- PROCEDURE Insert_Into_Summary
1303 --
1304 -- NOTE: picks up all lines at detail level and puts them
1305 -- directly into fact table for incremental mode
1306 --
1307 -- this may need to change later - we'll see
1308 ------------------------------------------
1309
1310 PROCEDURE INSERT_INTO_SUMMARY (p_start_range IN NUMBER,
1311 p_end_range IN NUMBER) IS
1312
1313 l_stmt VARCHAR2(1000);
1314
1315 BEGIN
1316
1317 g_phase := 'Inserting into fii_fa_exp_f-periodic deprn';
1318
1319 if g_debug_flag = 'Y' then
1320 fii_util.put_line('g_number_of_process: ' || to_char(g_number_of_process));
1321 fii_util.put_line('g_worker_num: ' || to_char(g_worker_num));
1322
1323 fii_util.put_line(' ');
1324 fii_util.put_line(g_phase);
1325 fii_util.start_timer;
1326 fii_util.put_line('');
1327 end if;
1328
1329 insert into fii_fa_exp_f
1330 (LEDGER_ID ,
1331 ACCOUNT_DATE ,
1332 CURRENCY_CODE ,
1333 CHART_OF_ACCOUNTS_ID ,
1334 COMPANY_ID ,
1335 COST_CENTER_ID ,
1336 NATURAL_ACCOUNT_ID ,
1337 user_dim1_id ,
1338 user_dim2_id ,
1339 ASSET_CAT_FLEX_STRUCTURE_ID ,
1340 asset_CAT_ID ,
1341 asset_cat_MAJOR_ID ,
1342 asset_cat_MAJOR_VALUE ,
1343 asset_cat_MINOR_ID ,
1344 asset_cat_MINOR_VALUE ,
1345 BOOK_TYPE_CODE ,
1346 ASSET_ID ,
1347 ASSET_NUMBER ,
1348 DISTRIBUTION_ID ,
1349 DISTRIBUTION_CCID ,
1350 EXPENSE_CCID ,
1351 SOURCE_CODE ,
1352 DEPRN_TYPE ,
1353 AMOUNT_T ,
1354 AMOUNT_B ,
1355 CREATION_DATE ,
1356 CREATED_BY ,
1357 LAST_UPDATE_DATE ,
1358 LAST_UPDATED_BY ,
1359 LAST_UPDATE_LOGIN ,
1360 XLA_EVENT_ID ,
1361 XLA_AE_HEADER_ID
1362 )
1363 select bc.set_of_books_id,
1364 dp.calendar_period_close_date,
1365 sob.currency_code,
1366 bc.accounting_flex_structure,
1367 ccid.company_id,
1368 ccid.cost_center_id,
1369 ccid.natural_account_id,
1370 ccid.user_dim1_id,
1371 ccid.user_dim2_id,
1372 cat.flex_structure_id,
1373 cat.category_id,
1374 cat.major_id,
1375 cat.major_value,
1376 cat.minor_id,
1377 cat.minor_value,
1378 bc.book_type_code,
1379 dh.ASSET_ID,
1380 ad.asset_number,
1381 dh.DISTRIBUTION_ID,
1382 dh.CODE_COMBINATION_ID,
1383 lines.code_combination_id,
1384 'DEPRN',
1385 links.source_distribution_type, --decode to this possibly? was EXPENSE
1386 nvl(lines.accounted_dr, 0) - nvl(lines.accounted_cr, 0),
1387 nvl(lines.accounted_dr, 0) - nvl(lines.accounted_cr, 0),
1388 sysdate,
1389 g_fii_user_id,
1390 sysdate,
1391 g_fii_user_id,
1392 g_fii_login_id,
1393 nid.event_id,
1394 nid.ae_header_id
1395 from fii_fa_new_exp_hdr_ids nid,
1396 xla_ae_lines lines,
1397 fii_fa_acct_class_code_gt acls,
1398 fii_gl_ccid_dimensions ccid,
1399 gl_ledgers_public_v sob,
1400 gl_import_references gir,
1401 xla_distribution_links links,
1402 fa_deprn_detail dd,
1403 fa_distribution_history dh,
1404 fa_additions_b ad,
1405 fa_asset_history ah,
1406 fii_fa_cat_dimensions cat,
1407 fa_deprn_periods dp,
1408 fa_book_controls bc
1409 where nid.record_id between p_start_range and p_end_range
1410 and nid.event_type_code = 'DEPRECIATION'
1411 and lines.ae_header_id = nid.ae_header_id
1412 and lines.application_id = 140
1413 and acls.accounting_class_code = lines.accounting_class_code
1414 and acls.ledger_id = nid.ledger_id
1415 and gir.je_header_id = nid.je_header_id
1416 and gir.gl_sl_link_id = lines.gl_sl_link_id
1417 and sob.ledger_id = nid.ledger_id
1418 and ccid.code_combination_id = lines.code_combination_id
1419 and links.ae_header_id = lines.ae_header_id
1420 and links.ae_line_num = lines.ae_line_num
1421 and links.application_id = 140
1422 and dd.asset_id = links.Source_distribution_id_num_1
1423 and dd.distribution_id = links.Source_distribution_id_num_5
1424 and dd.deprn_run_id = links.Source_distribution_id_num_3
1425 and dd.book_type_code = links.Source_distribution_id_char_4
1426 and dd.period_counter = links.Source_distribution_id_num_2
1427 and dd.distribution_id = dh.distribution_id
1428 and ad.asset_id = dh.asset_id
1429 and ah.asset_id = dh.asset_id
1430 and ah.date_effective <= dh.date_effective
1431 and nvl(ah.date_ineffective, sysdate + 1) > nvl(dh.date_ineffective, sysdate)
1432 and ah.transaction_header_id_in <= dh.transaction_header_id_in
1433 and nvl(ah.transaction_header_id_out,
1434 nvl(dh.transaction_header_id_out + 1, 1)) >
1435 nvl(dh.transaction_header_id_out, 0)
1436 and cat.category_id = ah.category_id
1437 and dp.book_type_code = dd.book_type_code
1438 and dp.period_counter = dd.period_counter
1439 and bc.book_type_code = dp.book_type_code
1440 and bc.set_of_books_id = sob.ledger_id;
1441
1442 if g_debug_flag = 'Y' then
1443 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_fa_exp_f');
1444 fii_util.stop_timer;
1445 fii_util.print_timer('Duration');
1446 end if;
1447
1448 commit;
1449
1450 g_phase := 'Inserting into fii_fa_exp_f-catchup deprn';
1451
1452 if g_debug_flag = 'Y' then
1453 fii_util.put_line(' ');
1454 fii_util.put_line(g_phase);
1455 fii_util.start_timer;
1456 fii_util.put_line('');
1457 end if;
1458
1459 insert into fii_fa_exp_f
1460 (LEDGER_ID ,
1461 ACCOUNT_DATE ,
1462 CURRENCY_CODE ,
1463 CHART_OF_ACCOUNTS_ID ,
1464 COMPANY_ID ,
1465 COST_CENTER_ID ,
1466 NATURAL_ACCOUNT_ID ,
1467 user_dim1_id ,
1468 user_dim2_id ,
1469 ASSET_CAT_FLEX_STRUCTURE_ID ,
1470 asset_CAT_ID ,
1471 asset_cat_MAJOR_ID ,
1472 asset_cat_MAJOR_VALUE ,
1473 asset_cat_MINOR_ID ,
1474 asset_cat_MINOR_VALUE ,
1475 BOOK_TYPE_CODE ,
1476 ASSET_ID ,
1477 ASSET_NUMBER ,
1478 DISTRIBUTION_ID ,
1479 DISTRIBUTION_CCID ,
1480 EXPENSE_CCID ,
1481 SOURCE_CODE ,
1482 DEPRN_TYPE ,
1483 AMOUNT_T ,
1484 AMOUNT_B ,
1485 CREATION_DATE ,
1486 CREATED_BY ,
1487 LAST_UPDATE_DATE ,
1488 LAST_UPDATED_BY ,
1489 LAST_UPDATE_LOGIN ,
1490 XLA_EVENT_ID ,
1491 XLA_AE_HEADER_ID
1492 )
1493 select bc.set_of_books_id,
1494 dp.calendar_period_close_date,
1495 sob.currency_code,
1496 bc.accounting_flex_structure,
1497 ccid.company_id,
1498 ccid.cost_center_id,
1499 ccid.natural_account_id,
1500 ccid.user_dim1_id,
1501 ccid.user_dim2_id,
1502 cat.flex_structure_id,
1503 cat.category_id,
1504 cat.major_id,
1505 cat.major_value,
1506 cat.minor_id,
1507 cat.minor_value,
1508 bc.book_type_code,
1509 dh.ASSET_ID,
1510 ad.asset_number,
1511 dh.DISTRIBUTION_ID,
1512 dh.CODE_COMBINATION_ID,
1513 lines.code_combination_id,
1514 'TRX',
1515 adj.adjustment_type,
1516 sum(nvl(lines.accounted_dr, 0)) - sum(nvl(lines.accounted_cr, 0)),
1517 sum(nvl(lines.accounted_dr, 0)) - sum(nvl(lines.accounted_cr, 0)),
1518 sysdate,
1519 g_fii_user_id,
1520 sysdate,
1521 g_fii_user_id,
1522 g_fii_login_id,
1523 nid.event_id,
1524 nid.ae_header_id
1525 from fii_fa_new_exp_hdr_ids nid,
1526 xla_ae_lines lines,
1527 fii_fa_acct_class_code_gt acls,
1528 fii_gl_ccid_dimensions ccid,
1529 gl_ledgers_public_v sob,
1530 gl_import_references gir,
1531 xla_distribution_links links,
1532 fa_adjustments adj,
1533 fa_distribution_history dh,
1534 fa_additions_b ad,
1535 fa_asset_history ah,
1536 fii_fa_cat_dimensions cat,
1537 fa_deprn_periods dp,
1538 fa_book_controls bc
1539 where nid.record_id between p_start_range and p_end_range
1540 and nid.event_type_code not in ('DEPRECIATION', 'ROLLBACK_DEPRECIATION')
1541 and lines.ae_header_id = nid.ae_header_id
1542 and lines.application_id = 140
1543 and acls.accounting_class_code = lines.accounting_class_code
1544 and acls.ledger_id = nid.ledger_id
1545 and gir.je_header_id = nid.je_header_id
1546 and gir.gl_sl_link_id = lines.gl_sl_link_id
1547 and ccid.code_combination_id = lines.code_combination_id
1548 and sob.ledger_id = nid.ledger_id
1549 and links.ae_header_id = lines.ae_header_id
1550 and links.ae_line_num = lines.ae_line_num
1551 and links.application_id = 140
1552 and links.source_distribution_type = 'TRX'
1553 and adj.transaction_header_id = links.Source_distribution_id_num_1
1554 and adj.adjustment_line_id = links.Source_distribution_id_num_2
1555 and dh.asset_id = ah.asset_id
1556 and ah.date_effective <= dh.date_effective
1557 and nvl(ah.date_ineffective, sysdate + 1) > nvl(dh.date_ineffective, sysdate)
1558 and ah.transaction_header_id_in <= dh.transaction_header_id_in
1559 and nvl(ah.transaction_header_id_out,
1560 nvl(dh.transaction_header_id_out + 1, 1)) >
1561 nvl(dh.transaction_header_id_out, 0)
1562 and dh.asset_id = ad.asset_id
1563 and ah.category_id = cat.category_id
1564 and dh.asset_id = adj.asset_id
1565 and dp.book_type_code = adj.book_type_code
1566 and dp.period_counter = adj.period_counter_created
1567 and dh.distribution_id = adj.distribution_id
1568 and nvl(adj.track_member_flag,'N') = 'N'
1569 and adj.adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
1570 and bc.book_type_code = dp.book_type_code
1571 and bc.set_of_books_id = sob.ledger_id
1572 group by bc.set_of_books_id,
1573 dp.calendar_period_close_date,
1574 NULL,
1575 sob.currency_code,
1576 bc.accounting_flex_structure,
1577 ccid.company_id,
1578 ccid.cost_center_id,
1579 ccid.natural_account_id,
1580 ccid.user_dim1_id,
1581 ccid.user_dim2_id,
1582 cat.flex_structure_id,
1583 cat.category_id,
1584 cat.major_id,
1585 cat.major_value,
1586 cat.minor_id,
1587 cat.minor_value,
1588 bc.book_type_code,
1589 dh.ASSET_ID,
1590 ad.asset_number,
1591 dh.DISTRIBUTION_ID,
1592 dh.CODE_COMBINATION_ID,
1593 lines.code_combination_id,
1594 'TRX',
1595 adj.adjustment_type,
1596 sysdate,
1597 g_fii_user_id,
1598 g_fii_login_id,
1599 nid.event_id,
1600 nid.ae_header_id;
1601
1602
1603
1604 if g_debug_flag = 'Y' then
1605 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_fa_exp_f');
1606 fii_util.stop_timer;
1607 fii_util.print_timer('Duration');
1608 end if;
1609
1610 commit;
1611
1612 --Call FND_STATS to collect statistics after populating the table
1613 /*
1614 g_phase := 'Calling FND_STATS to collect statistics for fii_fa_exp_f';
1615 FND_STATS.gather_table_stats
1616 (ownname => g_fii_schema,
1617 tabname => 'fii_fa_exp_f');
1618 */
1619 commit;
1620
1621 EXCEPTION
1622 WHEN OTHERS Then
1623 g_retcode := -1;
1624 FII_UTIL.put_line('
1625 ----------------------------
1626 Error in Function: INSERT_INTO_SUMMARY
1627 Phase: ' || g_phase || '
1628 Message: '||sqlerrm);
1629
1630 raise;
1631
1632 END INSERT_INTO_SUMMARY;
1633
1634
1635
1636 ----------------------------------------
1637 -- PROCEDURE Insert_Into_Summary_Par
1638 --
1639 -- NOTE: moves staging info into base summary
1640 -- for Initial Mode
1641 -----------------------------------------
1642
1643 PROCEDURE INSERT_INTO_SUMMARY_PAR IS
1644
1645 l_stmt VARCHAR2(1000);
1646
1647 BEGIN
1648
1649 -- R12: determine accounting classes
1650 GET_ACCT_CLASSES;
1651
1652 if g_debug_flag = 'Y' then
1653 fii_util.put_line(' ');
1654 fii_util.put_line('Loading data into base summary table - catchup');
1655 fii_util.start_timer;
1656 fii_util.put_line('');
1657 end if;
1658
1659 /*+ append parallel(bsum) */
1660
1661 insert
1662 into fii_fa_exp_f bsum
1663 (LEDGER_ID ,
1664 ACCOUNT_DATE ,
1665 CURRENCY_CODE ,
1666 CHART_OF_ACCOUNTS_ID ,
1667 COMPANY_ID ,
1668 COST_CENTER_ID ,
1669 NATURAL_ACCOUNT_ID ,
1670 user_dim1_id ,
1671 user_dim2_id ,
1672 ASSET_CAT_FLEX_STRUCTURE_ID ,
1673 asset_CAT_ID ,
1674 asset_cat_MAJOR_ID ,
1675 asset_cat_MAJOR_VALUE ,
1676 asset_cat_MINOR_ID ,
1677 asset_cat_MINOR_VALUE ,
1678 BOOK_TYPE_CODE ,
1679 ASSET_ID ,
1680 ASSET_NUMBER ,
1681 DISTRIBUTION_ID ,
1682 DISTRIBUTION_CCID ,
1683 EXPENSE_CCID ,
1684 SOURCE_CODE ,
1685 DEPRN_TYPE ,
1686 AMOUNT_T ,
1687 AMOUNT_B ,
1688 CREATION_DATE ,
1689 CREATED_BY ,
1690 LAST_UPDATE_DATE ,
1691 LAST_UPDATED_BY ,
1692 LAST_UPDATE_LOGIN ,
1693 XLA_EVENT_ID ,
1694 XLA_AE_HEADER_ID
1695 )
1696 select bc.set_of_books_id,
1697 dp.calendar_period_close_date,
1698 sob.currency_code,
1699 bc.accounting_flex_structure,
1700 ccid.company_id,
1701 ccid.cost_center_id,
1702 ccid.natural_account_id,
1703 ccid.user_dim1_id,
1704 ccid.user_dim2_id,
1705 cat.flex_structure_id,
1706 cat.category_id,
1707 cat.major_id,
1708 cat.major_value,
1709 cat.minor_id,
1710 cat.minor_value,
1711 bc.book_type_code,
1712 dh.ASSET_ID,
1713 ad.asset_number,
1714 dh.DISTRIBUTION_ID,
1715 dh.CODE_COMBINATION_ID,
1716 lines.code_combination_id,
1717 'TRX',
1718 adj.adjustment_type,
1719 sum(nvl(lines.accounted_dr, 0)) - sum(nvl(lines.accounted_cr, 0)),
1720 sum(nvl(lines.accounted_dr, 0)) - sum(nvl(lines.accounted_cr, 0)),
1721 sysdate,
1722 g_fii_user_id,
1723 sysdate,
1724 g_fii_user_id,
1725 g_fii_login_id,
1726 headers.event_id,
1727 headers.ae_header_id
1728 from fii_fa_new_exp_hdr_ids nid,
1729 gl_import_references gir,
1730 fii_fa_acct_class_code_gt acls,
1731 xla_ae_lines lines,
1732 xla_ae_headers headers,
1733 fii_gl_ccid_dimensions ccid,
1734 gl_ledgers_public_v sob,
1735 xla_distribution_links links,
1736 fa_adjustments adj,
1737 fa_distribution_history dh,
1738 fa_additions_b ad,
1739 fa_asset_history ah,
1740 fii_fa_cat_dimensions cat,
1741 fa_deprn_periods dp,
1742 fa_book_controls bc
1743 where gir.je_header_id = nid.je_header_id
1744 and acls.ledger_id = nid.ledger_id
1745 and lines.application_id = 140
1746 and lines.gl_sl_link_id = gir.gl_sl_link_id
1747 and lines.accounting_class_code = acls.accounting_class_code
1748 and headers.application_id = 140
1749 and headers.ae_header_id = lines.ae_header_id
1750 and headers.ledger_id = nid.ledger_id
1751 and headers.event_type_code not in ('DEPRECIATION', 'ROLLBACK_DEPRECIATION', 'DEFERRED_DEPRECIATION')
1752 and sob.ledger_id = nid.ledger_id
1753 and ccid.code_combination_id = lines.code_combination_id
1754 and links.application_id = 140
1755 and links.source_distribution_type = 'TRX'
1756 and links.ae_header_id = lines.ae_header_id
1757 and links.ae_line_num = lines.ae_line_num
1758 and adj.transaction_header_id = links.Source_distribution_id_num_1
1759 and adj.adjustment_line_id = links.Source_distribution_id_num_2
1760 and dh.asset_id = ah.asset_id
1761 and ah.date_effective <= dh.date_effective
1762 and nvl(ah.date_ineffective, sysdate + 1) > nvl(dh.date_ineffective, sysdate)
1763 and ah.transaction_header_id_in <= dh.transaction_header_id_in
1764 and nvl(ah.transaction_header_id_out,
1765 nvl(dh.transaction_header_id_out + 1, 1)) >
1766 nvl(dh.transaction_header_id_out, 0)
1767 and dh.asset_id = ad.asset_id
1768 and ah.category_id = cat.category_id
1769 and dh.asset_id = adj.asset_id
1770 and dp.book_type_code = adj.book_type_code
1771 and dp.period_counter = adj.period_counter_created
1772 and dh.distribution_id = adj.distribution_id
1773 and nvl(adj.track_member_flag,'N') = 'N'
1774 and adj.adjustment_type in ('EXPENSE', 'BONUS EXPENSE')
1775 and dp.book_type_code = bc.book_type_code
1776 and bc.set_of_books_id = sob.ledger_id
1777 group by bc.set_of_books_id,
1778 dp.calendar_period_close_date,
1779 NULL,
1780 sob.currency_code,
1781 bc.accounting_flex_structure,
1782 ccid.company_id,
1783 ccid.cost_center_id,
1784 ccid.natural_account_id,
1785 ccid.user_dim1_id,
1786 ccid.user_dim2_id,
1787 cat.flex_structure_id,
1788 cat.category_id,
1789 cat.major_id,
1790 cat.major_value,
1791 cat.minor_id,
1792 cat.minor_value,
1793 bc.book_type_code,
1794 dh.ASSET_ID,
1795 ad.asset_number,
1796 dh.DISTRIBUTION_ID,
1797 dh.CODE_COMBINATION_ID,
1798 lines.code_combination_id,
1799 'TRX',
1800 adj.adjustment_type,
1801 sysdate,
1802 g_fii_user_id,
1803 g_fii_login_id,
1804 headers.event_id,
1805 headers.ae_header_id;
1806
1807 if g_debug_flag = 'Y' then
1808 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_fa_exp_f');
1809 fii_util.stop_timer;
1810 fii_util.print_timer('Duration');
1811 end if;
1812
1813 commit;
1814
1815
1816 -- R12: adding seperate insert for DD based amounts
1817 if g_debug_flag = 'Y' then
1818 fii_util.put_line(' ');
1819 fii_util.put_line('Loading data into base summary table - periodic deprn');
1820 fii_util.start_timer;
1821 fii_util.put_line('');
1822 end if;
1823
1824 insert
1825 into fii_fa_exp_f bsum
1826 (LEDGER_ID ,
1827 ACCOUNT_DATE ,
1828 CURRENCY_CODE ,
1829 CHART_OF_ACCOUNTS_ID ,
1830 COMPANY_ID ,
1831 COST_CENTER_ID ,
1832 NATURAL_ACCOUNT_ID ,
1833 user_dim1_id ,
1834 user_dim2_id ,
1835 ASSET_CAT_FLEX_STRUCTURE_ID ,
1836 asset_CAT_ID ,
1837 asset_cat_MAJOR_ID ,
1838 asset_cat_MAJOR_VALUE ,
1839 asset_cat_MINOR_ID ,
1840 asset_cat_MINOR_VALUE ,
1841 BOOK_TYPE_CODE ,
1842 ASSET_ID ,
1843 ASSET_NUMBER ,
1844 DISTRIBUTION_ID ,
1845 DISTRIBUTION_CCID ,
1846 EXPENSE_CCID ,
1847 SOURCE_CODE ,
1848 DEPRN_TYPE ,
1849 AMOUNT_T ,
1850 AMOUNT_B ,
1851 CREATION_DATE ,
1852 CREATED_BY ,
1853 LAST_UPDATE_DATE ,
1854 LAST_UPDATED_BY ,
1855 LAST_UPDATE_LOGIN ,
1856 XLA_EVENT_ID ,
1857 XLA_AE_HEADER_ID
1858 )
1859 select bc.set_of_books_id,
1860 dp.calendar_period_close_date,
1861 sob.currency_code,
1862 bc.accounting_flex_structure,
1863 ccid.company_id,
1864 ccid.cost_center_id,
1865 ccid.natural_account_id,
1866 ccid.user_dim1_id,
1867 ccid.user_dim2_id,
1868 cat.flex_structure_id,
1869 cat.category_id,
1870 cat.major_id,
1871 cat.major_value,
1872 cat.minor_id,
1873 cat.minor_value,
1874 bc.book_type_code,
1875 dh.ASSET_ID,
1876 ad.asset_number,
1877 dh.DISTRIBUTION_ID,
1878 dh.CODE_COMBINATION_ID,
1879 lines.code_combination_id,
1880 'DEPRN',
1881 links.source_distribution_type, --decode to this possibly? was EXPENSE
1882 nvl(lines.accounted_dr, 0) - nvl(lines.accounted_cr, 0),
1883 nvl(lines.accounted_dr, 0) - nvl(lines.accounted_cr, 0),
1884 sysdate,
1885 g_fii_user_id,
1886 sysdate,
1887 g_fii_user_id,
1888 g_fii_login_id,
1889 headers.event_id,
1890 headers.ae_header_id
1891 from fii_fa_new_exp_hdr_ids nid,
1892 gl_import_references gir,
1893 fii_fa_acct_class_code_gt acls,
1894 xla_ae_lines lines,
1895 xla_ae_headers headers,
1896 fii_gl_ccid_dimensions ccid,
1897 gl_ledgers_public_v sob,
1898 xla_distribution_links links,
1899 fa_deprn_detail dd,
1900 fa_distribution_history dh,
1901 fa_additions_b ad,
1902 fa_asset_history ah,
1903 fii_fa_cat_dimensions cat,
1904 fa_deprn_periods dp,
1905 fa_book_controls bc
1906 where gir.je_header_id = nid.je_header_id
1907 and acls.ledger_id = nid.ledger_id
1908 and lines.application_id = 140
1909 and lines.gl_sl_link_id = gir.gl_sl_link_id
1910 and lines.accounting_class_code = acls.accounting_class_code
1911 and headers.application_id = 140
1912 and headers.ae_header_id = lines.ae_header_id
1913 and headers.event_type_code = 'DEPRECIATION'
1914 and ccid.code_combination_id = lines.code_combination_id
1915 and sob.ledger_id = nid.ledger_id
1916 and links.application_id = 140
1917 and links.ae_header_id = lines.ae_header_id
1918 and links.ae_line_num = lines.ae_line_num
1919 and dd.asset_id = links.Source_distribution_id_num_1
1920 and dd.distribution_id = links.Source_distribution_id_num_5
1921 and dd.deprn_run_id = links.Source_distribution_id_num_3
1922 and dd.book_type_code = links.Source_distribution_id_char_4
1923 and dd.period_counter = links.Source_distribution_id_num_2
1924 and dd.distribution_id = dh.distribution_id
1925 and ad.asset_id = dh.asset_id
1926 and ah.asset_id = dh.asset_id
1927 and ah.date_effective <= dh.date_effective
1928 and nvl(ah.date_ineffective, sysdate + 1) > nvl(dh.date_ineffective, sysdate)
1929 and ah.transaction_header_id_in <= dh.transaction_header_id_in
1930 and nvl(ah.transaction_header_id_out,
1931 nvl(dh.transaction_header_id_out + 1, 1)) >
1932 nvl(dh.transaction_header_id_out, 0)
1933 and cat.category_id = ah.category_id
1934 and dp.book_type_code = dd.book_type_code
1935 and dp.period_counter = dd.period_counter
1936 and bc.book_type_code = dp.book_type_code
1937 and bc.set_of_books_id = sob.ledger_id
1938 and headers.ae_header_id not in
1939 (select /*+ hash_aj parallel(headers2, ev_rb, ev_dep) */
1940 headers2.ae_header_id
1941 from xla_ae_headers headers2,
1942 xla_events ev_rb,
1943 xla_events ev_dep
1944 where headers2.application_id = 140
1945 and headers2.event_type_code = 'DEPRECIATION'
1946 and ev_dep.event_id = headers2.event_id
1947 and ev_dep.application_id = 140
1948 and ev_rb.entity_id = ev_dep.entity_id
1949 and ev_rb.application_id = 140
1950 and ev_rb.event_id > ev_dep.event_id);
1951
1952 if g_debug_flag = 'Y' then
1953 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_fa_exp_f');
1954 fii_util.stop_timer;
1955 fii_util.print_timer('Duration');
1956 end if;
1957
1958 commit;
1959
1960
1961 --per DBI, no need to Call FND_STATS to collect statistics after populating the table
1962
1963
1964 EXCEPTION
1965 WHEN OTHERS Then
1966 g_retcode := -1;
1967 FII_UTIL.put_line('
1968 ----------------------------
1969 Error in Function: Insert_Into_Summary_Par
1970 Phase: ' || g_phase || '
1971 Message: '||sqlerrm);
1972
1973 raise;
1974
1975 END INSERT_INTO_SUMMARY_PAR;
1976
1977
1978
1979 -- ------------------------------------------------------------
1980 -- Public Functions and Procedures
1981 -- ------------------------------------------------------------
1982
1983 PROCEDURE Main (errbuf IN OUT NOCOPY VARCHAR2,
1984 retcode IN OUT NOCOPY VARCHAR2,
1985 p_number_of_process IN NUMBER,
1986 p_worker_num IN NUMBER,
1987 p_program_type IN VARCHAR2,
1988 p_parallel_query IN NUMBER,
1989 p_sort_area_size IN NUMBER,
1990 p_hash_area_size IN NUMBER) IS
1991
1992 return_status BOOLEAN := TRUE;
1993 p_number_of_rows NUMBER := 0;
1994 p_no_worker NUMBER := 1;
1995 l_conversion_count NUMBER := 0;
1996 l_retcode VARCHAR2(3);
1997 l_errbuf VARCHAR2(500);
1998 l_stmt VARCHAR2(300);
1999 l_dir VARCHAR2(100);
2000 l_ids_count NUMBER := 0;
2001 stg_count NUMBER := 0;
2002 l_truncate_stg BOOLEAN := FALSE;
2003
2004 -- used fort paralization - new method
2005 l_unassigned_cnt NUMBER := 0;
2006 l_failed_cnt NUMBER := 0;
2007 l_wip_cnt NUMBER := 0;
2008 l_completed_cnt NUMBER := 0;
2009 l_total_cnt NUMBER := 0;
2010 l_count NUMBER := 0;
2011 l_start_range NUMBER := 0;
2012 l_end_range NUMBER := 0;
2013
2014 l_global_start_date DATE;
2015 l_global_param_list dbms_sql.varchar2_table;
2016 L_PERIOD_START_DATE date;
2017 L_PERIOD_END_DATE date;
2018
2019 TYPE WorkerList is table of NUMBER
2020 index by binary_integer;
2021 l_worker WorkerList;
2022
2023 l_slg_chg VARCHAR2(10);
2024 l_prd_chg VARCHAR2(10);
2025
2026 l_ret_val BOOLEAN;
2027 l_ret_code VARCHAR2(30);
2028
2029 BEGIN
2030
2031 errbuf := NULL;
2032 retcode := 0;
2033
2034 g_fii_user_id := FND_GLOBAL.User_Id;
2035 g_fii_login_id := FND_GLOBAL.Login_Id;
2036
2037 IF (g_fii_user_id IS NULL OR g_fii_login_id IS NULL) THEN
2038 RAISE G_LOGIN_INFO_NOT_AVABLE;
2039 END IF;
2040
2041 if g_debug_flag = 'Y' then
2042 FII_UTIL.put_line('User ID: ' || g_fii_user_id || ' Login ID: ' || g_fii_login_id);
2043 end if;
2044
2045 g_program_type := p_program_type;
2046 -----------------------------------------------
2047 -- Do the necessary setups for logging and
2048 -- output
2049 -----------------------------------------------
2050 l_dir := FII_UTIL.get_utl_file_dir;
2051
2052 ------------------------------------------------
2053 -- Initialize API will fetch the FII_DEBUG_MODE
2054 -- profile option and intialize g_debug variable
2055 -- accordingly. It will also read in profile
2056 -- option BIS_DEBUG_LOG_DIRECTORY to find out
2057 -- the log directory
2058 ------------------------------------------------
2059 g_phase := 'Calling FII_UTIL.initialize';
2060 IF g_program_type = 'I' THEN
2061 FII_UTIL.initialize('FII_FA_EXP_SUM.log','FII_FA_EXP_SUM.out',l_dir, 'FII_FA_EXP_B_C');
2062 ELSIF g_program_type = 'L' THEN
2063 FII_UTIL.initialize('FII_FA_EXP_SUM.log','FII_FA_EXP_SUM.out',l_dir, 'FII_FA_EXP_F_L');
2064 END IF;
2065
2066
2067 ------------------------------------------------
2068 -- For initial mode, always 1 process...
2069 -- For incremental mode:
2070 -- Determine whether this is parent / child,etc
2071 ------------------------------------------------
2072 if (g_program_type = 'I') then
2073
2074 g_number_of_process := nvl(p_number_of_process, 1);
2075 g_worker_num := nvl(p_worker_num, 1);
2076
2077 if (nvl(p_number_of_process, 1) = 1) then
2078
2079 G_sole := TRUE;
2080 G_child := FALSE;
2081
2082 elsif (nvl(p_number_of_process, 1) > 1 and
2083 p_worker_num is null) then
2084
2085 G_parent := TRUE;
2086 G_child := FALSE;
2087
2088 else
2089
2090 G_child := TRUE;
2091 G_sole := FALSE;
2092 G_parent := FALSE;
2093
2094 end if;
2095
2096 else
2097
2098 G_child := FALSE;
2099 G_sole := TRUE;
2100 G_parent := FALSE;
2101
2102 end if;
2103
2104
2105 -----------------------------------------------------
2106 -- only process the main checks, etc if this is sole
2107 -- or parent request
2108 -----------------------------------------------------
2109 if (G_sole or G_parent) then
2110
2111 -----------------------------------------------------
2112 -- Calling BIS API to do common set ups
2113 -- If it returns false, then program should error out
2114 -----------------------------------------------------
2115 g_phase := 'Calling BIS API to do common set ups';
2116 l_global_param_list(1) := 'BIS_GLOBAL_START_DATE';
2117 l_global_param_list(2) := 'BIS_PRIMARY_CURRENCY_CODE';
2118 l_global_param_list(3) := 'BIS_PRIMARY_RATE_TYPE';
2119
2120 IF (NOT bis_common_parameters.check_global_parameters(l_global_param_list)) THEN
2121 FII_MESSAGE.write_log (msg_name => 'FII_BAD_GLOBAL_PARA',
2122 token_num => 0);
2123 FII_MESSAGE.write_output(msg_name => 'FII_BAD_GLOBAL_PARA',
2124 token_num => 0);
2125
2126 l_ret_val := FND_CONCURRENT.Set_Completion_Status(
2127 status => 'ERROR',
2128 message => 'One of the three global parameters: Global Start Date; Primary Currency Code; Primary Rate Type has not been set up.'
2129 );
2130
2131 return;
2132 ELSIF g_program_type = 'I' THEN
2133 IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_FA_EXP_B_C')) THEN
2134 raise_application_error(-20000,errbuf);
2135 return;
2136 END IF;
2137 ELSIF g_program_type = 'L' THEN
2138 IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_FA_EXP_F_L')) THEN
2139 raise_application_error(-20000,errbuf);
2140 return;
2141 END IF;
2142 END IF;
2143
2144 ------------------------------------------------
2145 -- Initialize other setups
2146 ------------------------------------------------
2147 g_phase := 'Calling INIT';
2148 INIT();
2149
2150 ------------------------------------------------
2151 -- If running in Initial Load mode, truncate
2152 -- everything before starts.
2153 ------------------------------------------------
2154 IF g_program_type = 'L' THEN
2155
2156 IF g_debug_flag = 'Y' then
2157 FII_UTIL.put_line('Running in Initial Load mode, truncate STG, summary and other processing tables.');
2158 END IF;
2159
2160 FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_FA_EXP_T',
2161 P_RETCODE => l_ret_code);
2162
2163 FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_FA_EXP_F',
2164 P_RETCODE => l_ret_code);
2165
2166 FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_FA_EXP_HDR_IDS',
2167 P_RETCODE => l_ret_code);
2168
2169 FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_FA_NEW_EXP_HDR_IDS',
2170 P_RETCODE => l_ret_code);
2171
2172
2173 COMMIT;
2174 END IF;
2175
2176 ------------------------------------------
2177 -- Check setups only if we are running in
2178 -- Incremental Mode, g_program_type = 'I'
2179 ------------------------------------------
2180 IF (g_program_type = 'I') THEN
2181 ---------------------------------------------
2182 -- Check if any set up got changed. If yes,
2183 -- then we need to truncate the summary table
2184 -- and then reload (also see bug 3401590)
2185 --
2186 -- FA doesn't need a check PRD change,
2187 -- just the SLG check
2188 ---------------------------------------------
2189 g_phase := 'Check setups if we are running in Incremental Mode';
2190
2191 l_slg_chg := CHECK_IF_SLG_SET_UP_CHANGE;
2192 -- l_prd_chg := CHECK_IF_PRD_SET_UP_CHANGE;
2193
2194 -- should fail the program if either slg or prd changed
2195 IF (l_slg_chg = 'TRUE') THEN
2196 FII_MESSAGE.write_output (msg_name => 'FII_TRUNC_SUMMARY', token_num => 0);
2197 FII_MESSAGE.write_log (msg_name => 'FII_TRUNC_SUMMARY', token_num => 0);
2198 --FII_UTIL.put_line('Source Ledger Group setup has changed. Please run the Request Set in the Initial mode to repopulate the summaries.');
2199 retcode := -1;
2200 RETURN;
2201 END IF;
2202
2203 ELSIF (g_program_type = 'L') THEN
2204 ---------------------------------------------
2205 -- If running in Inital Load, then update
2206 -- change log to indicate that resummarization
2207 -- is not necessary since everything is
2208 -- going to be freshly loaded
2209 --
2210 -- FA will only be using the resummarize log
2211 -- item, not product change since we can handle
2212 -- new books, etc
2213 ---------------------------------------------
2214 g_phase := 'Update fii_change_log if we are running in Inital Load';
2215
2216 UPDATE fii_change_log
2217 SET item_value = 'N',
2218 last_update_date = SYSDATE,
2219 last_update_login = g_fii_login_id,
2220 last_updated_by = g_fii_user_id
2221 WHERE log_item = 'FA_RESUMMARIZE'
2222 AND item_value = 'Y';
2223
2224 COMMIT;
2225
2226 END IF;
2227
2228 -------------------------------------------------
2229 -- Print out useful date range information
2230 -- FA will not use start and end date ranges
2231 -- as we only need the global start date
2232 -------------------------------------------------
2233 g_phase := 'Get date range information';
2234
2235 l_global_start_date := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
2236 g_global_start_date := l_global_start_date;
2237
2238 if g_debug_flag = 'Y' then
2239 FII_UTIL.put_line('BIS Global Start Date: ' || l_global_start_date);
2240 end if;
2241
2242 ----------------------------------------------------------
2243 -- FA DOES NOT need to Determine if we need to resume.
2244 ----------------------------------------------------------
2245
2246 g_phase := 'g_resume_flag = N';
2247
2248 ----------------------------------------------------------
2249 -- This variable indicates that if exception occur, do
2250 -- we need to truncate the staging table.
2251 -- We are about to submit the child process which will
2252 -- insert records into staging table. If any exception
2253 -- occured during the child process run, the staging table
2254 -- should be truncated. After all child process are done
2255 -- inserting records into staging table, this flag will
2256 -- be set to FALSE.
2257 ----------------------------------------------------------
2258 g_truncate_stg := TRUE;
2259
2260 ----------------------------------------------------------
2261 -- This variable indicates that if exception occur, do
2262 -- we need to truncate the temporary ID table.
2263 -- We need to truncate this table if the program starts
2264 -- fresh at the beginning.
2265 -- We will reset this variable to FALSE after we have
2266 -- populate it. We will not truncate it until next time
2267 -- when the program starts fresh (non-resume). We want
2268 -- to preserve this table for debugging purpose.
2269 ----------------------------------------------------------
2270 g_truncate_id := TRUE;
2271
2272 --------------------------------------------------------------
2273 -- Calling CLEAN_UP procedure to clean up all processing
2274 -- tables
2275 --------------------------------------------------------------
2276 if g_debug_flag = 'Y' then
2277 FII_UTIL.put_line('');
2278 FII_UTIL.put_line('Cleaning up processing tables before actual processing start');
2279 FII_UTIL.put_line('------------------------------------------------------------');
2280 end if;
2281
2282 -- hold off *** testing parallel ****
2283 CLEAN_UP;
2284
2285 if g_debug_flag = 'Y' then
2286 FII_UTIL.put_line('------------------------------------------------------------');
2287 FII_UTIL.put_line('');
2288 end if;
2289
2290 ---------------------------------------------------------
2291 -- After we do initial clean up, we will set this flag to
2292 -- FALSE to preserve the temporary Revenue ID table for
2293 -- debugging purpose
2294 ---------------------------------------------------------
2295 g_truncate_id := FALSE;
2296
2297 ---------------------------------------------------------------
2298 -- Call New_Journals routine to insert Journal header ids into
2299 -- FII_FA_NEW_EXP_HDR_IDS
2300 ----------------------------------------------------------------
2301 g_phase := 'Identify New Journal Headers to process';
2302 if g_debug_flag = 'Y' then
2303 FII_UTIL.put_line(g_phase);
2304 end if;
2305
2306 --------------------------------------------------------
2307 -- NEW_JOURNALS will identify the new journals which
2308 -- need to be processed based on the user entered
2309 -- date range. If there are no new journals to process
2310 -- the program will exit immediately with complete
2311 -- successful status
2312 --------------------------------------------------------
2313 l_ids_count := NEW_JOURNALS(l_period_start_date,
2314 l_period_end_date);
2315
2316 IF (l_ids_count = 0) THEN
2317 -- purge the new ids table for deleted lines
2318 FII_UTIL.TRUNCATE_TABLE(p_table_name => 'FII_FA_NEW_EXP_HDR_IDS',
2319 P_RETCODE => l_ret_code);
2320
2321 if g_debug_flag = 'Y' then
2322 FII_UTIL.put_line('No Journal Entries to Process, exit.');
2323 end if;
2324 RETURN;
2325 END IF;
2326
2327 ----------------------------------------------------------------
2328 -- After the new journals are identified, we need to call the
2329 -- CAT ID API to make sure that the CAT dimension is up to date.
2330 --
2331 -- OPEN Issue - do we need this for GL CCID????
2332 -- The reason we call this API after we have identified the
2333 -- new journals instead of calling this API at the beginning of
2334 -- the programs is because that it is possible that after we
2335 -- called the API, new CCIDs are created by new journals, and
2336 -- then we will pull this new journal in the New_Journals API
2337 -- and subsequently treat this new journal as processed even
2338 -- though it is not processed because its corresponding CCID
2339 -- is missing in the CCID dimension.
2340 -- If CCID dimension is not up to date, VERIFY_CCID_UP_TO_DATE
2341 -- will also call the CCID Dimension load program to update
2342 -- CCID dimension.
2343 --
2344 ----------------------------------------------------------------
2345 g_phase := 'Verifying if CCID Dimension is up to date';
2346 if g_debug_flag = 'Y' then
2347 FII_UTIL.put_line(g_phase);
2348 end if;
2349
2350 VERIFY_CAT_ID_UP_TO_DATE;
2351
2352 g_phase := 'Verifying if all FA periods have been upgraded for XLA';
2353 if g_debug_flag = 'Y' then
2354 FII_UTIL.put_line(g_phase);
2355 end if;
2356
2357 CHECK_XLA_CONVERSION_STATUS;
2358
2359 ----------------------------------------------------------------
2360 -- Register jobs in the table FII_FA_WORKER_JOBS for launching
2361 -- child processes - needed for both parallel and sole for incremental
2362 ----------------------------------------------------------------
2363
2364 if (p_program_type = 'I') then
2365 g_phase := 'Calling Routine Register_Jobs for incremental mode';
2366
2367 if g_debug_flag = 'Y' then
2368 FII_UTIL.put_line(g_phase);
2369 end if;
2370
2371 Register_Jobs();
2372
2373 COMMIT;
2374 end if;
2375
2376 ----------------------------------------------------------------
2377 -- Launching child processes if this is parent not sole
2378 ----------------------------------------------------------------
2379 if (G_parent) then
2380
2381 g_phase := 'In G_Parent Logic...';
2382
2383 if g_debug_flag = 'Y' then
2384 FII_UTIL.put_line(g_phase);
2385 end if;
2386
2387 ----------------------------------------------------------------
2388 -- Launching child processes.
2389 ----------------------------------------------------------------
2390 g_phase := 'Launching child process...';
2391 p_no_worker := p_number_of_process;
2392
2393 -- Launch child process
2394 LAUNCH_WORKERS(p_number_of_process);
2395
2396 -- Monitor Child process after launching them
2397
2398 DECLARE
2399
2400 l_unassigned_cnt NUMBER := 0;
2401 l_completed_cnt NUMBER := 0;
2402 l_wip_cnt NUMBER := 0;
2403 l_failed_cnt NUMBER := 0;
2404 l_tot_cnt NUMBER := 0;
2405 l_last_unassigned_cnt NUMBER := 0;
2406 l_last_completed_cnt NUMBER := 0;
2407 l_last_wip_cnt NUMBER := 0;
2408 l_cycle NUMBER := 0;
2409
2410 BEGIN
2411 g_phase := 'Waiting for child process to complete';
2412 LOOP
2413 SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
2414 NVL(sum(decode(status,'COMPLETED',1,0)),0),
2415 NVL(sum(decode(status,'IN PROCESS',1,0)),0),
2416 NVL(sum(decode(status,'FAILED',1,0)),0),
2417 count(*)
2418 INTO l_unassigned_cnt,
2419 l_completed_cnt,
2420 l_wip_cnt,
2421 l_failed_cnt,
2422 l_tot_cnt
2423 FROM FII_FA_WORKER_JOBS;
2424
2425 if g_debug_flag = 'Y' then
2426 FII_UTIL.put_line('Job status - Unassigned:'||l_unassigned_cnt||
2427 ' In Process:'||l_wip_cnt||
2428 ' Completed:'||l_completed_cnt||
2429 ' Failed:'||l_failed_cnt);
2430 end if;
2431
2432 IF (l_failed_cnt > 0) THEN
2433 g_retcode := -1;
2434 FII_UTIL.put_line('
2435 ---------------------------------
2436 Error in Main Procedure:
2437 Message: At least one of the workers have errored out');
2438 RAISE G_CHILD_PROCESS_ISSUE;
2439 END IF;
2440
2441 -- --------------------------------------------
2442 -- IF the number of complete count equals to
2443 -- the total count, then that means all workers
2444 -- have completed. Then we can exit the loop
2445 -- --------------------------------------------
2446 IF (l_tot_cnt = l_completed_cnt) THEN
2447 if g_debug_flag = 'Y' then
2448 FII_UTIL.put_line ('All jobs have completed');
2449 end if;
2450 EXIT;
2451 END IF;
2452
2453 -------------------------
2454 -- Detect infinite loops
2455 -------------------------
2456 IF (l_unassigned_cnt = l_last_unassigned_cnt AND
2457 l_completed_cnt = l_last_completed_cnt AND
2458 l_wip_cnt = l_last_wip_cnt) THEN
2459 l_cycle := l_cycle + 1;
2460 ELSE
2461 l_cycle := 1;
2462 END IF;
2463
2464 -----------------------------------------
2465 -- MAX_LOOP is a global variable you set.
2466 -- It represents the number of minutes
2467 -- you want to wait for each worker to
2468 -- complete. We can set it to 30 minutes
2469 -- for now
2470 -----------------------------------------
2471 IF (l_cycle > MAX_LOOP) THEN
2472 g_retcode := -1;
2473 FII_UTIL.put_line('
2474 ---------------------------------
2475 Error in Main Procedure:
2476 Message: No progress have been made for '||MAX_LOOP||' minutes.
2477 Terminating');
2478 RAISE G_CHILD_PROCESS_ISSUE;
2479 END IF;
2480
2481 -------------------------
2482 -- Sleep 60 Seconds
2483 -------------------------
2484 dbms_lock.sleep(60);
2485
2486 l_last_unassigned_cnt := l_unassigned_cnt;
2487 l_last_completed_cnt := l_completed_cnt;
2488 l_last_wip_cnt := l_wip_cnt;
2489 END LOOP;
2490 END; -- Monitor child process BLOCK Ends here.
2491
2492 END IF; -- end if parent
2493
2494 END IF; -- end parent / sole
2495
2496
2497 -----------------------------------------------------------------
2498 -- assign work to the child workers or to the worker if not
2499 -- submitted in parallel
2500 -----------------------------------------------------------------
2501 IF (p_program_type = 'I' and
2502 (G_sole or G_child)) THEN
2503
2504 g_phase := 'In G_Sole / G_Child Logic...';
2505
2506 if g_debug_flag = 'Y' then
2507 FII_UTIL.put_line(g_phase);
2508 end if;
2509
2510 l_stmt := ' ALTER SESSION SET global_names = false';
2511 EXECUTE IMMEDIATE l_stmt;
2512
2513 FII_UTIL.initialize;
2514
2515 -- R12: determine accounting classes
2516 GET_ACCT_CLASSES;
2517
2518 -- ------------------------------------------
2519 -- Loop thru job list
2520 -- -----------------------------------------
2521 g_phase := 'Loop thru job list';
2522 LOOP
2523 SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
2524 NVL(sum(decode(status,'FAILED', 1, 0)),0),
2525 NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
2526 NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
2527 count(*)
2528 INTO l_unassigned_cnt,
2529 l_failed_cnt,
2530 l_wip_cnt,
2531 l_completed_cnt,
2532 l_total_cnt
2533 FROM FII_FA_WORKER_JOBS;
2534
2535 if g_debug_flag = 'Y' then
2536 FII_UTIL.put_line('Job status - Unassigned: '||l_unassigned_cnt||
2537 ' In Process: '||l_wip_cnt||
2538 ' Completed: '||l_completed_cnt||
2539 ' Failed: '||l_failed_cnt||
2540 ' Total: '|| l_total_cnt);
2541 end if;
2542
2543 IF (l_failed_cnt > 0) THEN
2544 if g_debug_flag = 'Y' then
2545 FII_UTIL.put_line('');
2546 FII_UTIL.put_line('Another worker have errored out. Stop processing.');
2547 end if;
2548 EXIT;
2549 ELSIF (l_unassigned_cnt = 0) THEN
2550 if g_debug_flag = 'Y' then
2551 FII_UTIL.put_line('');
2552 FII_UTIL.put_line('No more jobs left. Terminating.');
2553 end if;
2554 EXIT;
2555 ELSIF (l_completed_cnt = l_total_cnt) THEN
2556 if g_debug_flag = 'Y' then
2557 FII_UTIL.put_line('');
2558 FII_UTIL.put_line('All jobs completed, no more job. Terminating');
2559 end if;
2560 EXIT;
2561 ELSIF (l_unassigned_cnt > 0) THEN
2562 UPDATE FII_FA_WORKER_JOBS
2563 SET status = 'IN PROCESS',
2564 worker_number = g_worker_num
2565 WHERE status = 'UNASSIGNED'
2566 AND rownum < 2;
2567 if g_debug_flag = 'Y' then
2568 FII_UTIL.put_line('Taking job from job queue');
2569 FII_UTIL.put_line('count: ' || sql%rowcount);
2570 end if;
2571 l_count := sql%rowcount;
2572 COMMIT;
2573 END IF;
2574
2575 -- -----------------------------------
2576 -- There could be rare situations where
2577 -- between Section 30 and Section 50
2578 -- the unassigned job gets taken by
2579 -- another worker. So, if unassigned
2580 -- job no longer exist. Do nothing.
2581 -- -----------------------------------
2582 IF (l_count > 0) THEN
2583 DECLARE
2584 BEGIN
2585 g_phase := 'Getting ID range from FII_FA_WORKER_JOBS table';
2586
2587 if g_debug_flag = 'Y' then
2588 FII_UTIL.put_line(g_phase);
2589 end if;
2590
2591 SELECT start_range,
2592 end_range
2593 INTO l_start_range,
2594 l_end_range
2595 FROM FII_FA_WORKER_JOBS
2596 WHERE worker_number = g_worker_num
2597 AND status = 'IN PROCESS';
2598
2599 --------------------------------------------------
2600 -- Do summarization using the start_range
2601 -- and end_range call the summarization routine
2602 -- Passing start range and end range parameters
2603 --------------------------------------------------
2604 g_phase := 'Inserting into summary table';
2605 if g_debug_flag = 'Y' then
2606 FII_UTIL.put_line(g_phase);
2607 end if;
2608
2609 INSERT_INTO_SUMMARY(l_start_range,
2610 l_end_range);
2611
2612 --------------------------------------------------
2613 -- Delete any rolled back entries
2614 --------------------------------------------------
2615
2616 DELETE_FROM_BASE_SUMMARY(l_start_range,
2617 l_end_range);
2618
2619 -----------------------------------------------------
2620 -- Do other work if necessary to finish the child
2621 -- process
2622 -- After completing the work, set the job status
2623 -- to complete
2624 -----------------------------------------------------
2625 g_phase:='Updating job status in FII_FA_WORKER_JOBS table';
2626 if g_debug_flag = 'Y' then
2627 FII_UTIL.put_line(g_phase);
2628 end if;
2629
2630 UPDATE FII_FA_WORKER_JOBS
2631 SET status = 'COMPLETED'
2632 WHERE status = 'IN PROCESS'
2633 AND worker_number = g_worker_num;
2634
2635 COMMIT;
2636
2637 -- Handle any exception that occured during
2638 -- your child process
2639
2640 EXCEPTION
2641 WHEN OTHERS THEN
2642 g_retcode := -1;
2643
2644 UPDATE FII_FA_WORKER_JOBS
2645 SET status = 'FAILED'
2646 WHERE worker_number = g_worker_num
2647 AND status = 'IN PROCESS';
2648
2649 COMMIT;
2650 Raise;
2651 END;
2652
2653 END IF; /* IF (l_count> 0) */
2654
2655 END LOOP;
2656
2657 -- FA is not using this for now, commenting out per Renu
2658 -- INSERT_INTO_RATES;
2659
2660 ELSIF (p_program_type = 'L') THEN
2661 --------------------------------------------------
2662 -- this is a sole request in initial mode
2663 -- Do summarization using the start_range
2664 -- and end_range call the summarization routine
2665 -- Passing start range and end range parameters
2666 --------------------------------------------------
2667 g_phase := 'Inserting into staging table';
2668 if g_debug_flag = 'Y' then
2669 FII_UTIL.put_line(g_phase);
2670 end if;
2671
2672 END IF; -- child or sole
2673
2674
2675 IF g_parent or G_sole THEN
2676
2677 g_phase := 'In G_Parent / G_Sole...';
2678
2679 if g_debug_flag = 'Y' then
2680 FII_UTIL.put_line(g_phase);
2681 end if;
2682
2683 -----------------------------------------------------------------
2684 -- If all the child process completes successfully then Invoke
2685 -- Summary_err_check routine to check for any missing rates record
2686 -- or missing time dimension record in the fii_fa_exp_t
2687 -- table.
2688 -----------------------------------------------------------------
2689 g_phase:= 'Summarization Error Check';
2690 if g_debug_flag = 'Y' then
2691 FII_UTIL.put_line(g_phase);
2692 end if;
2693
2694 Summary_err_check;
2695
2696 IF (g_missing_rates = 0 AND g_missing_time = 0) THEN
2697
2698 -------------------------------------------------------------
2699 -- Setting g_truncate_stg to TRUE because during the subsequent
2700 -- processes, if failure occurs, we should truncate staging
2701 ---------------------------------------------------------------
2702 g_truncate_stg := TRUE;
2703
2704 -------------------------------------------------------------
2705 -- Call Summarization_aggreagte routine to insert from
2706 -- the staging table to the base summary
2707 --
2708 -- NOTE: only doing this for initial as incremental for
2709 -- now will go directly into the summary table
2710 -------------------------------------------------------------
2711 g_phase := 'Aggregating summarized data';
2712 if g_debug_flag = 'Y' then
2713 FII_UTIL.put_line('');
2714 FII_UTIL.put_line(g_phase);
2715 end if;
2716
2717 if (g_program_type = 'L') then
2718 INSERT_INTO_SUMMARY_PAR;
2719 end if;
2720
2721 -----------------------------------------------------------------
2722 -- If Merge routine returns true then Insert processed rows into
2723 -- FII_FA_PROCESSED_HDR_IDS table by calling the routine
2724 -- Jornals_processed.
2725 -----------------------------------------------------------------
2726 g_phase := 'Inserting processed JE Header IDs';
2727 if g_debug_flag = 'Y' then
2728 FII_UTIL.put_line('');
2729 FII_UTIL.put_line(g_phase);
2730 end if;
2731
2732 Journals_processed;
2733
2734 COMMIT;
2735
2736 ------------------------------------------------------------------
2737 -- Cleaning phase
2738 -- Truncate staging summary table if all the processes completed
2739 -- successfully.
2740 ------------------------------------------------------------------
2741 -- **** hold off for testing ****
2742 Clean_up;
2743
2744 ----------------------------------------------------------------
2745 -- Calling BIS API to record the range we collect. Only do this
2746 -- when we have a successful collection
2747 ----------------------------------------------------------------
2748 BIS_COLLECTION_UTILITIES.wrapup(p_status => TRUE,
2749 p_period_from => l_period_start_date,
2750 p_period_to => l_period_end_date);
2751
2752 -- end in warning if any non-sla-upgraded data exists
2753 if (g_non_upgraded_ledgers) then
2754 retcode := 1;
2755 else
2756 retcode := 0;
2757 end if;
2758
2759 ELSE
2760
2761 retcode := g_retcode;
2762 errbuf := 'There is missing rate or missing time information';
2763
2764 END IF; --g_missing_rates = 0 AND g_missing_time = 0
2765
2766 END IF; -- parent or sole
2767
2768 Exception
2769 WHEN G_LOGIN_INFO_NOT_AVABLE THEN
2770 g_retcode := -1;
2771 FII_UTIL.put_line('Init: can not get User ID and Login ID, program exits');
2772 retcode := g_retcode;
2773
2774
2775 WHEN OTHERS Then
2776 g_retcode := -1;
2777 -- ****
2778 --
2779 -- temporarily removing this in order to test child perf
2780 -- via scripts
2781 clean_up;
2782 FII_UTIL.put_line('
2783 Error in Function: Main
2784 Phase: '|| g_phase || '
2785 Message: ' || sqlerrm);
2786 retcode := g_retcode;
2787
2788 END Main;
2789
2790
2791 END FII_FA_EXP_B_C;