[Home] [Help]
PACKAGE BODY: APPS.FII_GL_JE_B_C
Source
1 PACKAGE BODY FII_GL_JE_B_C AS
2 /*$Header: FIIGL03B.pls 120.82 2007/12/18 02:45:35 wywong ship $*/
3
4 g_retcode VARCHAR2(20) := NULL;
5 g_sob_id NUMBER := NULL;
6 g_from_date DATE;
7 g_to_date DATE;
8 g_lud_from_date DATE := NULL;
9 g_lud_to_date DATE := NULL;
10 g_has_lud BOOLEAN := FALSE;
11 g_fii_schema VARCHAR2(30);
12 g_prim_currency VARCHAR2(10);
13 g_sec_currency VARCHAR2(10);
14 g_prim_rate_type VARCHAR2(30);
15 g_sec_rate_type VARCHAR2(30);
16 g_prim_rate_type_name VARCHAR2(30);
17 g_sec_rate_type_name VARCHAR2(30);
18 g_primary_mau NUMBER;
19 g_secondary_mau NUMBER;
20 g_worker_num NUMBER;
21 g_phase VARCHAR2(100);
22 g_resume_flag VARCHAR2(1):= 'N';
23 g_child_process_size NUMBER := 20000;
24 g_missing_rates NUMBER := 0;
25 g_missing_time NUMBER := 0;
26 g_fii_user_id NUMBER(15);
27 g_fii_login_id NUMBER(15);
28 g_truncate_stg BOOLEAN;
29 g_truncate_id BOOLEAN;
30 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
31 g_program_type VARCHAR2(1);
32 g_industry VARCHAR2(1) := NVL(FND_PROFILE.value('Industry'), 'C');
33 g_global_start_date DATE := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
34
35 ONE_SECOND CONSTANT NUMBER := 0.000011574; -- 1 second
36 INTERVAL CONSTANT NUMBER := 4; -- 4 days
37 MAX_LOOP CONSTANT NUMBER := 180; -- 180 loops = 180 minutes
38 LAST_PHASE CONSTANT NUMBER := 3;
39
40 G_TABLE_NOT_EXIST EXCEPTION;
41 G_NO_CHILD_PROCESS EXCEPTION;
42 G_CHILD_PROCESS_ISSUE EXCEPTION;
43 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
44 G_CCID_FAILED EXCEPTION;
45 G_MISSING_ENCUM_MAPPING EXCEPTION;
46
47 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
48
49 g_usage_code CONSTANT VARCHAR2(10) := 'DBI';
50
51 -- ---------------------------------------------------------------
52 -- Private procedures and Functions;
53 -- ---------------------------------------------------------------
54
55
56 -- ---------------------------------------------------------------
57 -- PROCEDURE REPORT_MISSING_RATES
58 -- ---------------------------------------------------------------
59 PROCEDURE REPORT_MISSING_RATES IS
60 TYPE cursorType is REF CURSOR;
61
62 l_stmt VARCHAR2(500);
63 l_count NUMBER;
64 l_curr CURSORTYPE;
65
66 /*
67 --bug 3677737: use least(sysdate, effective_date) to replace effective_date
68 cursor PrimMissingRate is
69 SELECT DISTINCT
70 functional_currency,
71 decode( prim_conversion_rate,
72 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
73 least(sysdate, effective_date)) effective_date
74 FROM fii_gl_je_summary_stg
75 WHERE prim_conversion_rate < 0;
76
77 --bug 3677737: use least(sysdate, effective_date) to replace effective_date
78 cursor SecMissingRate is
79 SELECT DISTINCT
80 functional_currency,
81 decode( sec_conversion_rate,
82 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
83 least(sysdate, effective_date) ) effective_date
84 FROM fii_gl_je_summary_stg
85 WHERE sec_conversion_rate < 0;
86 */
87 cursor PSMissingRate is
88 SELECT DISTINCT
89 functional_currency,
90 CASE WHEN prim_conversion_rate < 0 THEN
91 decode( prim_conversion_rate,
92 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
93 least(sysdate, effective_date))
94 ELSE NULL END prim_effective_date,
95 CASE WHEN sec_conversion_rate < 0 THEN
96 decode( sec_conversion_rate,
97 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
98 least(sysdate, effective_date))
99 ELSE NULL END sec_effective_date
100 FROM fii_gl_je_summary_stg
101 WHERE prim_conversion_rate < 0
102 OR sec_conversion_rate < 0;
103
104 BEGIN
105
106 if g_debug_flag = 'Y' then
107 FII_UTIL.put_line('Calling procedure: REPORT_MISSING_RATES');
108 FII_UTIL.put_line('');
109 end if;
110
111 g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRateHeader to write out report header';
112 if g_debug_flag = 'Y' then
113 FII_UTIL.put_line(g_phase);
114 FII_UTIL.put_line('');
115 end if;
116
117 BIS_COLLECTION_UTILITIES.WriteMissingRateHeader;
118
119 g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRate to write out report contents';
120 if g_debug_flag = 'Y' then
121 FII_UTIL.put_line(g_phase);
122 FII_UTIL.put_line('');
123 end if;
124
125 /*
126 FOR rate_record in PrimMissingRate LOOP
127 BIS_COLLECTION_UTILITIES.writemissingrate(
128 g_prim_rate_type_name,
129 rate_record.functional_currency,
130 g_prim_currency,
131 rate_record.effective_date);
132 END LOOP;
133
134 FOR rate_record in SecMissingRate LOOP
135 BIS_COLLECTION_UTILITIES.writemissingrate(
136 g_sec_rate_type_name,
137 rate_record.functional_currency,
138 g_sec_currency,
139 rate_record.effective_date);
140 END LOOP;
141 */
142 FOR rate_record in PSMissingRate LOOP
143
144 IF rate_record.prim_effective_date IS NOT NULL THEN
145 BIS_COLLECTION_UTILITIES.writemissingrate(
146 g_prim_rate_type_name,
147 rate_record.functional_currency,
148 g_prim_currency,
149 rate_record.prim_effective_date);
150 END IF;
151
152 IF rate_record.sec_effective_date IS NOT NULL THEN
153 BIS_COLLECTION_UTILITIES.writemissingrate(
154 g_sec_rate_type_name,
155 rate_record.functional_currency,
156 g_sec_currency,
157 rate_record.sec_effective_date);
158 END IF;
159
160 END LOOP;
161
162 FND_FILE.CLOSE;
163
164 EXCEPTION
165 WHEN NO_DATA_FOUND THEN
166 g_retcode:=-1;
167 FII_UTIL.put_line('
168 ---------------------------------------------------
169 Error in Procedure: REPORT_MISSING_RATES
170 Phase: '||g_phase||'
171 Message: Should have missing rates but found none');
172 raise;
173 WHEN OTHERS THEN
174 g_retcode := -1;
175 FII_UTIL.put_line('
176 ---------------------------------
177 Error in Procedure: REPORT_MISSING_RATES
178 Phase: '||g_phase||'
179 Message: '||sqlerrm);
180 raise;
181 END REPORT_MISSING_RATES;
182
183 -- ---------------------------------------------------------------
184 -- PROCEDURE REPORT_MISSING_RATES_L
185 -- ---------------------------------------------------------------
186 PROCEDURE REPORT_MISSING_RATES_L IS
187 TYPE cursorType is REF CURSOR;
188
189 l_stmt VARCHAR2(500);
190 l_count NUMBER;
191 l_curr CURSORTYPE;
192
193 --bug 3677737: use least(sysdate, trx_date) to replace trx_date
194 cursor PrimMissingRate is
195 SELECT DISTINCT
196 functional_currency,
197 decode( prim_conversion_rate,
198 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
199 least(sysdate, trx_date) ) trx_date
200 FROM fii_gl_revenue_rates_temp
201 WHERE prim_conversion_rate < 0;
202
203 --bug 3677737: use least(sysdate, trx_date) to replace trx_date
204 cursor SecMissingRate is
205 SELECT DISTINCT
206 functional_currency,
207 decode( sec_conversion_rate,
208 -3, to_date( '01/01/1999', 'MM/DD/YYYY' ),
209 least(sysdate, trx_date) ) trx_date
210 FROM fii_gl_revenue_rates_temp
211 WHERE sec_conversion_rate < 0;
212
213 BEGIN
214
215 if g_debug_flag = 'Y' then
216 FII_UTIL.put_line('Calling procedure: REPORT_MISSING_RATES');
217 FII_UTIL.put_line('');
218 end if;
219
220 g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRateHeader to write out report header';
221 if g_debug_flag = 'Y' then
222 FII_UTIL.put_line(g_phase);
223 FII_UTIL.put_line('');
224 end if;
225
226 BIS_COLLECTION_UTILITIES.WriteMissingRateHeader;
227
228 g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRate to write out report contents';
229 if g_debug_flag = 'Y' then
230 FII_UTIL.put_line(g_phase);
231 FII_UTIL.put_line('');
232 end if;
233
234 FOR rate_record in PrimMissingRate LOOP
235 BIS_COLLECTION_UTILITIES.writemissingrate(
236 g_prim_rate_type_name,
237 rate_record.functional_currency,
238 g_prim_currency,
239 rate_record.trx_date);
240 END LOOP;
241
242 FOR rate_record in SecMissingRate LOOP
243 BIS_COLLECTION_UTILITIES.writemissingrate(
244 g_sec_rate_type_name,
245 rate_record.functional_currency,
246 g_sec_currency,
247 rate_record.trx_date);
248 END LOOP;
249
250 FND_FILE.CLOSE;
251
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254 g_retcode:=-1;
255 if g_debug_flag = 'Y' then
256 FII_UTIL.put_line('
257 ---------------------------------------------------
258 Error in Procedure: REPORT_MISSING_RATES_L
259 Phase: '||g_phase||'
260 Message: Should have missing rates but found none');
261 end if;
262 raise;
263 WHEN OTHERS THEN
264 g_retcode := -1;
265 if g_debug_flag = 'Y' then
266 FII_UTIL.put_line('
267 ---------------------------------
268 Error in Procedure: REPORT_MISSING_RATES_L
269 Phase: '||g_phase||'
270 Message: '||sqlerrm);
271 end if;
272 raise;
273 END REPORT_MISSING_RATES_L;
274
275 -----------------------------------------------------------
276 -- PROCEDURE DROP_TABLE
277 -----------------------------------------------------------
278 PROCEDURE Drop_Table (p_table_name in varchar2) is
279 l_stmt varchar2(400);
280
281 Begin
282
283 l_stmt:='drop table '||g_fii_schema||'.'|| p_table_name;
284
285 if g_debug_flag = 'Y' then
286 FII_UTIL.put_line('');
287 FII_UTIL.put_line(l_stmt);
288 end if;
289
290 execute immediate l_stmt;
291
292 Exception
293 WHEN G_TABLE_NOT_EXIST THEN
294 NULL; -- Oracle 942, table does not exist, no actions
295 WHEN OTHERS THEN
296 g_retcode := -1;
297 if g_debug_flag = 'Y' then
298 FII_UTIL.put_line('
299 ---------------------------------
300 Error in Procedure: DROP_TABLE
301 Message: '||sqlerrm);
302 end if;
303 RAISE;
304 End Drop_Table;
305
306 -----------------------------------------------------------------------
307 -- PROCEDURE TRUNCATE_TABLE
308 -----------------------------------------------------------------------
309 PROCEDURE TRUNCATE_TABLE (p_table_name in varchar2) is
310 l_stmt varchar2(400);
311
312 Begin
313
314 l_stmt:='truncate table '||g_fii_schema||'.'|| p_table_name;
315
316 if g_debug_flag = 'Y' then
317 FII_UTIL.put_line('');
318 FII_UTIL.put_line(l_stmt);
319 end if;
320
321 execute immediate l_stmt;
322
323 Exception
324 WHEN OTHERS THEN
325 g_retcode := -1;
326 if g_debug_flag = 'Y' then
327 FII_UTIL.put_line('
328 ---------------------------------
329 Error in Procedure: TRUNCATE_TABLE
330 Message: '||sqlerrm);
331 end if;
332 RAISE;
333 End truncate_Table;
334
335 -----------------------------------------------------------------------
336 -- PROCEDURE INIT
337 -----------------------------------------------------------------------
338 PROCEDURE Init is
339 l_stmt VARCHAR2(50);
340
341 BEGIN
342
343 if g_debug_flag = 'Y' then
344 FII_UTIL.put_line('Calling procedure: INIT');
345 FII_UTIL.put_line('');
346 end if;
347
348 -- -------------------------------------------
349 -- Turn on parallel insert/dml for the session
350 -- Commit to terminate any open transactions
351 -- This will avoid issue with not being able
352 -- to run ddl within a transaction
353 -- -------------------------------------------
354 /*g_phase := 'Altering session to enable parallel DML';
355 commit;
356 l_stmt:='ALTER SESSION ENABLE PARALLEL DML';
357 execute immediate l_stmt;*/
358
359 ----------------------------------------------------------
360 -- Find the schema owner of FII
361 ----------------------------------------------------------
362 g_phase := 'Find FII schema';
363 g_fii_schema := FII_UTIL.get_schema_name ('FII');
364
365 --------------------------------------------------------------
366 -- Find all currency related information
367 --------------------------------------------------------------
368 g_phase := 'Find currency information';
369
370 g_primary_mau := nvl(fii_currency.get_mau_primary, 0.01 );
371 g_secondary_mau:= nvl(fii_currency.get_mau_secondary, 0.01);
372 g_prim_currency := bis_common_parameters.get_currency_code;
373 g_sec_currency := bis_common_parameters.get_secondary_currency_code;
374 g_prim_rate_type := bis_common_parameters.get_rate_type;
375 g_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
376
377 begin
378 g_phase := 'Convert rate_type to rate_type_name';
379
380 select user_conversion_type into g_prim_rate_type_name
381 from gl_daily_conversion_types
382 where conversion_type = g_prim_rate_type;
383
384 if g_sec_rate_type is not null then
385 select user_conversion_type into g_sec_rate_type_name
386 from gl_daily_conversion_types
387 where conversion_type = g_sec_rate_type;
388 else
389 g_sec_rate_type_name := null;
390 end if;
391 exception
392 when others then
393 fii_util.write_log('Failed to convert rate_type to rate_type_name' );
394 raise;
395 end;
396
397 g_phase := 'Find User ID and User Login';
398
399 g_fii_user_id := FND_GLOBAL.User_Id;
400 g_fii_login_id := FND_GLOBAL.Login_Id;
401
402 IF (g_fii_user_id IS NULL OR g_fii_login_id IS NULL) THEN
403 RAISE G_LOGIN_INFO_NOT_AVABLE;
404 END IF;
405
406 if g_debug_flag = 'Y' then
407 FII_UTIL.put_line('User ID: ' || g_fii_user_id || ' Login ID: ' || g_fii_login_id);
408 end if;
409
410 EXCEPTION
411 WHEN G_LOGIN_INFO_NOT_AVABLE THEN
412 g_retcode := -1;
413 FII_UTIL.put_line('Init: can not get User ID and Login ID, program exits');
414 raise;
415 WHEN OTHERS THEN
416 g_retcode := -1;
417 FII_UTIL.put_line('
418 ---------------------------------
419 Error in Procedure: INIT
420 Phase: '||g_phase||'
421 Message: '||sqlerrm);
422 raise;
423 END Init;
424
425 -----------------------------------------------------------------
426 -- FUNCTION CHECK_IF_SLG_SET_UP_CHANGE
427 -----------------------------------------------------------------
428 FUNCTION CHECK_IF_SLG_SET_UP_CHANGE RETURN VARCHAR2 IS
429 l_slg_chg VARCHAR2(10);
430 l_count1 number :=0 ;
431 l_count2 number :=0 ;
432
433 BEGIN
434
435 g_phase := 'Check if Source Legder Assignments setup has changed';
436 if g_debug_flag = 'Y' then
437 FII_UTIL.put_line(g_phase);
438 end if;
439
440 SELECT DECODE(item_value, 'Y', 'TRUE', 'FALSE')
441 INTO l_slg_chg
442 FROM fii_change_log
443 WHERE log_item = 'GL_RESUMMARIZE';
444
445 IF l_slg_chg = 'TRUE' THEN
446
447 g_phase := 'Reach l_slg_chg = TRUE';
448
449 begin
450 SELECT 1
451 INTO l_count1
452 FROM fii_gl_je_summary_b
453 WHERE ROWNUM = 1;
454 exception
455 when NO_DATA_FOUND then
456 l_count1 := 0;
457 end;
458
459 begin
460 SELECT 1
461 INTO l_count2
462 FROM fii_gl_je_summary_stg
463 WHERE ROWNUM = 1;
464 exception
465 when NO_DATA_FOUND then
466 l_count2 := 0;
467 end;
468
469 IF (l_count1 = 0 AND l_count2 = 0) then
470 g_phase := 'Updating fii_change_log for log_item GL_RESUMMARIZE';
471 UPDATE fii_change_log
472 SET item_value = 'N',
473 last_update_date = SYSDATE,
474 last_update_login = g_fii_login_id,
475 last_updated_by = g_fii_user_id
476 WHERE log_item = 'GL_RESUMMARIZE'
477 AND item_value = 'Y';
478
479 COMMIT;
480
481 l_slg_chg := 'FALSE';
482 END IF;
483
484 END IF;
485
486 RETURN l_slg_chg;
487
488 EXCEPTION
489 WHEN NO_DATA_FOUND THEN
490 RETURN 'FALSE';
491 WHEN OTHERS THEN
492 g_retcode := -1;
493 FII_UTIL.put_line('
494 -----------------------------
495 Error occured in Funcation: CHECK_IF_SLG_SET_UP_CHANGE
496 Phase: '||g_phase||'
497 Message: ' || sqlerrm);
498 raise;
499 END CHECK_IF_SLG_SET_UP_CHANGE;
500
501 -----------------------------------------------------------------
502 -- FUNCTION CHECK_IF_PRD_SET_UP_CHANGE
503 -----------------------------------------------------------------
504 FUNCTION CHECK_IF_PRD_SET_UP_CHANGE RETURN VARCHAR2 IS
505 l_prd_chg VARCHAR2(10);
506 l_count1 number :=0 ;
507 l_count2 number :=0 ;
508
509 BEGIN
510 g_phase := 'Check if Product Assignments set up has changed';
511 if g_debug_flag = 'Y' then
512 FII_UTIL.put_line(g_phase);
513 end if;
514
515 SELECT DECODE(item_value, 'Y', 'TRUE', 'FALSE')
516 INTO l_prd_chg
517 FROM fii_change_log
518 WHERE log_item = 'GL_PROD_CHANGE';
519
520 IF l_prd_chg = 'TRUE' THEN
521
522 g_phase := 'Reach l_prd_chg = TRUE';
523
524 begin
525 SELECT 1
526 INTO l_count1
527 FROM fii_gl_je_summary_b
528 WHERE ROWNUM = 1;
529 exception
530 when NO_DATA_FOUND then
531 l_count1 := 0;
532 end;
533
534 begin
535 SELECT 1
536 INTO l_count2
537 FROM fii_gl_je_summary_stg
538 WHERE ROWNUM = 1;
539 exception
540 when NO_DATA_FOUND then
541 l_count2 := 0;
542 end;
543
544 IF (l_count1 = 0 AND l_count2 = 0) then
545 g_phase := 'Updating fii_change_log for log_item GL_PROD_CHANGE';
546 UPDATE fii_change_log
547 SET item_value = 'N',
548 last_update_date = SYSDATE,
549 last_update_login = g_fii_login_id,
550 last_updated_by = g_fii_user_id
551 WHERE log_item = 'GL_PROD_CHANGE'
552 AND item_value = 'Y';
553
554 COMMIT;
555
556 l_prd_chg := 'FALSE';
557 END IF;
558
559 END IF;
560
561 RETURN l_prd_chg;
562
563 EXCEPTION
564 WHEN NO_DATA_FOUND THEN
565 RETURN 'FALSE';
566 WHEN OTHERS THEN
567 g_retcode := -1;
568 FII_UTIL.put_line('
569 -----------------------------
570 Error occured in Funcation: CHECK_IF_PRD_SET_UP_CHANGE
571 Phase: '||g_phase||'
572 Message: ' || sqlerrm);
573 raise;
574 END CHECK_IF_PRD_SET_UP_CHANGE;
575
576
577 -----------------------------------------------------------------
578 -- PROCEDURE REGISTER_JOBS
579 -----------------------------------------------------------------
580 PROCEDURE REGISTER_JOBS IS
581 l_max_number NUMBER;
582 l_start_number NUMBER;
583 l_end_number NUMBER;
584 l_count NUMBER := 0;
585
586 BEGIN
587
588 if g_debug_flag = 'Y' then
589 FII_UTIL.put_line('Calling procedure: REGISTER_JOBS');
590 FII_UTIL.put_line('');
591 end if;
592
593 g_phase := 'Register jobs for workers';
594 if g_debug_flag = 'Y' then
595 FII_UTIL.put_line('Register jobs for workers');
596 end if;
597
598 ------------------------------------------------------------
599 -- select min and max sequence IDs from your ID Temp table
600 ------------------------------------------------------------
601 g_phase := 'select min and max sequence IDs from the ID Temp table';
602 SELECT NVL(max(record_id), 0), nvl(min(record_id),1)
603 INTO l_max_number, l_start_number
604 FROM FII_GL_NEW_JRL_HEADER_IDS;
605
606 WHILE (l_start_number <= l_max_number) LOOP
607 l_end_number:= l_start_number + g_child_process_size;
608 g_phase := 'Loop to insert into FII_GL_WORKER_JOBS: '
609 || l_start_number || ', ' || l_end_number;
610 INSERT INTO FII_GL_WORKER_JOBS (start_range, end_range, worker_number, status)
611 VALUES (l_start_number, least(l_end_number, l_max_number), 0, 'UNASSIGNED');
612 l_count := l_count + 1;
613 l_start_number := least(l_end_number, l_max_number) + 1;
614 END LOOP;
615
616 if g_debug_flag = 'Y' then
617 FII_UTIL.put_line('Inserted ' || l_count || ' jobs into FII_GL_WORKER_JOBS table');
618 end if;
619
620 COMMIT;
621
622 EXCEPTION
623 WHEN OTHERS THEN
624 g_retcode := -1;
625 FII_UTIL.put_line('
626 ---------------------------------
627 Error in Procedure: REGISTER_JOBS
628 Phase: '||g_phase||'
629 Message: '||sqlerrm);
630 RAISE;
631 END REGISTER_JOBS;
632
633 -----------------------------------------------------------------------
634 -- FUNCTION LAUNCH_WORKER
635 -----------------------------------------------------------------------
636 FUNCTION LAUNCH_WORKER(p_worker_no NUMBER) RETURN NUMBER IS
637 l_request_id NUMBER;
638
639 BEGIN
640
641 l_request_id := FND_REQUEST.SUBMIT_REQUEST('FII',
642 'FII_GL_JE_B_C_SUBWORKER',
643 NULL,
644 NULL,
645 FALSE,
646 p_worker_no);
647 IF (l_request_id = 0) THEN
648 rollback;
649 g_retcode := -1;
650 FII_UTIL.put_line('
651 ---------------------------------
652 Error in Procedure: LAUNCH_WORKER
653 Message: '||fnd_message.get);
654 raise G_NO_CHILD_PROCESS;
655 END IF;
656 RETURN l_request_id;
657
658 EXCEPTION
659 WHEN G_NO_CHILD_PROCESS THEN
660 g_retcode := -1;
661 FII_UTIL.put_line('No child process launched');
662 raise;
663 WHEN OTHERS THEN
664 rollback;
665 g_retcode := -1;
666 FII_UTIL.put_line('
667 ---------------------------------
668 Error in Procedure: LAUNCH_WORKER
669 Message: '||sqlerrm);
670 raise;
671 END LAUNCH_WORKER;
672
673 -----------------------------------------------------------------------
674 -- PROCEDURE CHILD_SETUP
675 -----------------------------------------------------------------------
676 PROCEDURE CHILD_SETUP(p_object_name VARCHAR2) IS
677 l_dir VARCHAR2(400);
678 l_stmt VARCHAR2(100);
679
680 BEGIN
681
682 g_phase := 'Calling ALTER SESSION SET global_names = false ';
683 l_stmt := ' ALTER SESSION SET global_names = false';
684 EXECUTE IMMEDIATE l_stmt;
685
686 ------------------------------------------------------
687 -- Set default directory in case if the profile option
688 -- BIS_DEBUG_LOG_DIRECTORY is not set up
689 ------------------------------------------------------
690 l_dir:=FII_UTIL.get_utl_file_dir;
691
692 ----------------------------------------------------------------
693 -- fii_util.initialize will get profile options FII_DEBUG_MODE
694 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
695 -- the log files and output files are written to
696 ----------------------------------------------------------------
697 g_phase := 'Calling FII_UTIL.initialize ';
698 FII_UTIL.initialize(p_object_name||'.log',p_object_name||'.out',l_dir, 'FII_GL_JE_B_C_Worker');
699
700 g_fii_user_id := FND_GLOBAL.User_Id;
701 g_fii_login_id := FND_GLOBAL.Login_Id;
702
703 EXCEPTION
704 WHEN OTHERS THEN
705 rollback;
706 g_retcode := -1;
707 FII_UTIL.put_line('
708 ---------------------------------
709 Error in Procedure: CHILD_SETUP
710 Phase: '||g_phase||'
711 Message: '||sqlerrm);
712 raise;
713 END CHILD_SETUP;
714
715 --------------------------------------------------------------------
716 -- PROCEDURE SUMMARY_ERR_CHECK
717 --------------------------------------------------------------------
718 PROCEDURE SUMMARY_ERR_CHECK (p_program_type IN VARCHAR2)IS
719 l_conv_rate_cnt NUMBER :=0;
720 l_stg_min DATE;
721 l_stg_max DATE;
722 l_row_cnt NUMBER;
723 l_check_time_dim BOOLEAN;
724
725 BEGIN
726
727 g_phase := 'Checking for missing rates';
728 if g_debug_flag = 'Y' then
729 FII_UTIL.put_line(g_phase);
730 end if;
731
732 ------------------------------------------------------
733 -- If there are missing exchange rates indicated in
734 -- the staging table, then call report_missing_rates
735 -- API to print out the missing rates report
736 ------------------------------------------------------
737 IF (p_program_type = 'L') THEN
738 g_phase := 'For p_program_type = L ';
739 SELECT MIN(trx_date), MAX(trx_date), sum(decode(sign(prim_conversion_rate), -1, 1, 0)) +
740 sum(decode(sign(sec_conversion_rate), -1, 1, 0)), count(*)
741 INTO l_stg_min, l_stg_max, l_conv_rate_cnt, l_row_cnt
742 FROM FII_GL_REVENUE_RATES_TEMP;
743
744 ELSE
745
746 g_phase := 'For p_program_type <> L ';
747 SELECT MIN(effective_date), MAX(effective_date), sum(decode(sign(prim_conversion_rate), -1, 1, 0)) +
748 sum(decode(sign(sec_conversion_rate), -1, 1, 0)), count(*)
749 INTO l_stg_min, l_stg_max, l_conv_rate_cnt, l_row_cnt
750 FROM FII_GL_JE_SUMMARY_STG;
751
752 END IF;
753
754 IF l_row_cnt = 0 THEN
755 IF g_debug_flag = 'Y' THEN
756 FII_UTIL.put_line('Summary Error Check completed successfully, no data found!');
757 END IF;
758 RETURN;
759 END IF;
760
761 IF (l_conv_rate_cnt >0) THEN
762 -------------------------------------------------
763 -- Write out translated message to let user know
764 -- there are missing exchange rate information
765 -------------------------------------------------
766 FII_MESSAGE.write_output (msg_name => 'FII_MISS_EXCH_RATE_FOUND', token_num => 0);
767 FII_MESSAGE.write_log (msg_name => 'FII_MISS_EXCH_RATE_FOUND', token_num => 0);
768 FII_MESSAGE.write_log (msg_name => 'FII_REFER_TO_OUTPUT', token_num => 0);
769
770 ----FII_UTIL.put_line('Missing currency conversion rates found, program will exit with error status. Please fix the missing conversion rates');
771
772 g_retcode := -1;
773 g_missing_rates := 1;
774 IF p_program_type = 'L' THEN
775 REPORT_MISSING_RATES_L;
776 ELSE
777 REPORT_MISSING_RATES;
778 END IF;
779 RETURN;
780 END IF;
781
782 g_phase := 'Checking for Time dimension';
783 if g_debug_flag = 'Y' then
784 FII_UTIL.put_line(g_phase);
785 end if;
786
787 -----------------------------------------------------------
788 -- If we find record in the staging table which references
789 -- time records which does not exist in FII_TIME_DAY
790 -- table, then we will exit the program with error status
791 -----------------------------------------------------------
792
793 FII_TIME_API.check_missing_date (l_stg_min, l_stg_max, l_check_time_dim);
794
795 --------------------------------------
796 -- If there are missing time records
797 --------------------------------------
798 IF (l_check_time_dim) THEN
799
800 FII_MESSAGE.write_output (msg_name => 'FII_TIME_DIM_STALE', token_num => 0);
801 FII_MESSAGE.write_log (msg_name => 'FII_TIME_DIM_STALE', token_num => 0);
802 FII_MESSAGE.write_log (msg_name => 'FII_REFER_TO_OUTPUT', token_num => 0);
803
804 ----FII_UTIL.put_line('Time Dimension is not fully populated. Please populate Time dimension to cover the date range you are collecting');
805
806 g_retcode := -1; --we set it error out for missing time
807 g_missing_time := 1;
808 RETURN;
809 END IF;
810
811 if g_debug_flag = 'Y' then
812 FII_UTIL.put_line('Summary Error Check completed successfully, no error found!');
813 end if;
814 RETURN;
815
816 EXCEPTION
817 WHEN OTHERS THEN
818 g_retcode := -1;
819 FII_UTIL.put_line('
820 ---------------------------------
821 Error occured in Summary_err_check function
822 Phase: '||g_phase||'
823 Message: '||sqlerrm);
824 Raise;
825 END Summary_err_check;
826
827 -----------------------------------------------------------------------
828 -- PROCEDURE CLEAN_UP
829 -----------------------------------------------------------------------
830 PROCEDURE Clean_Up IS
831 BEGIN
832
833 if g_debug_flag = 'Y' then
834 FII_UTIL.put_line('Calling procedure: CLEAN_UP');
835 end if;
836
837 TRUNCATE_TABLE('FII_GL_WORKER_JOBS');
838
839 IF (g_truncate_id) THEN
840 TRUNCATE_TABLE('FII_GL_NEW_JRL_HEADER_IDS');
841 END IF;
842
843 IF (g_truncate_stg) THEN
844 TRUNCATE_TABLE('FII_GL_JE_SUMMARY_STG');
845 END IF;
846
847 COMMIT;
848
849 EXCEPTION
850 WHEN OTHERS Then
851 g_retcode:=-1;
852 FII_UTIL.put_line('
853 ---------------------------------
854 Error in Procedure: Clean_Up
855 Message: ' || sqlerrm);
856 RAISE;
857 END Clean_up;
858
859 -----------------------------------------------------------------------
860 -- PROCEDURE SUM_AGGREGATE_WEEK
861 -- Aggregate date to week level (similar to ROLL_UP)
862 -- Note that we need to call Summarize_aggregate first before calling this
863 -- since global amounts need to be updated there.
864 -----------------------------------------------------------------------
865 PROCEDURE Sum_Aggregate_Week IS
866 l_number_of_rows NUMBER :=0;
867
868 BEGIN
869
870 ---------------------------------------------------------------------
871 --Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level
872 ---------------------------------------------------------------------
873 g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level';
874
875 if g_debug_flag = 'Y' then
876 FII_UTIL.put_line('');
877 FII_UTIL.put_line('Inserting weekly aggregated data into FII_GL_JE_SUMMARY_STG table');
878 FII_UTIL.start_timer;
879 end if;
880
881 INSERT INTO fii_gl_je_summary_stg
882 (
883 week,
884 cost_center_id,
885 fin_category_id,
886 company_id,
887 prod_category_id,
888 user_dim1_id,
889 user_dim2_id,
890 je_source,
891 je_category,
892 effective_date,
893 ledger_id,
894 chart_of_accounts_id,
895 functional_currency,
896 amount_b,
897 prim_amount_g,
898 sec_amount_g,
899 committed_amount_b,
900 committed_amount_prim,
901 obligated_amount_b,
902 obligated_amount_prim,
903 other_amount_b,
904 other_amount_prim,
905 posted_date,
906 last_update_date,
907 last_updated_by,
908 creation_date,
909 created_by,
910 last_update_login)
911 SELECT
912 fday.week_id,
913 stg.cost_center_id,
914 stg.fin_category_id,
915 stg.company_id,
916 stg.prod_category_id,
917 stg.user_dim1_id,
918 stg.user_dim2_id,
919 stg.je_source,
920 stg.je_category,
921 MAX(stg.effective_date),
922 stg.ledger_id,
923 stg.chart_of_accounts_id,
924 stg.functional_currency,
925 SUM(stg.amount_b) amount_b,
926 SUM(stg.prim_amount_g) prim_amount_g,
927 SUM(stg.sec_amount_g) sec_amount_g,
928 SUM(stg.committed_amount_b) committed_amount_b,
929 SUM(stg.committed_amount_prim) committed_amount_prim,
930 SUM(stg.obligated_amount_b) obligated_amount_b,
931 SUM(stg.obligated_amount_prim) obligated_amount_prim,
932 SUM(stg.other_amount_b) other_amount_b,
933 SUM(stg.other_amount_prim) other_amount_prim,
934 stg.posted_date,
935 stg.last_update_date,
936 stg.last_updated_by,
937 stg.creation_date,
938 stg.created_by,
939 stg.last_update_login
940 FROM fii_gl_je_summary_stg stg,
941 fii_time_day fday
942 WHERE stg.day = fday.report_date_julian
943 GROUP BY
944 stg.cost_center_id,
945 stg.fin_category_id,
946 stg.company_id,
947 stg.prod_category_id,
948 stg.user_dim1_id,
949 stg.user_dim2_id,
950 stg.je_source,
951 stg.je_category,
952 stg.ledger_id,
953 stg.chart_of_accounts_id,
954 stg.functional_currency,
955 stg.last_update_date,
956 stg.last_updated_by,
957 stg.creation_date,
958 stg.created_by,
959 stg.last_update_login,
960 fday.week_id,
961 stg.posted_date;
962
963
964 if g_debug_flag = 'Y' then
965 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
966 FII_UTIL.stop_timer;
967 FII_UTIL.print_timer('Duration');
968 end if;
969
970 Exception
971 WHEN OTHERS Then
972 g_retcode := -1;
973 FII_UTIL.put_line('
974 Error in phase ' || g_phase || ' of Sum_Aggregate_Week procedure' || '
975 Message: ' || sqlerrm);
976 ROLLBACK;
977 raise;
978 END Sum_Aggregate_Week;
979
980 -----------------------------------------------------------------------
981 -- PROCEDURE SUMMARIZE_AGGREGATE
982 -----------------------------------------------------------------------
983 PROCEDURE Summarize_aggregate IS
984 l_number_of_rows NUMBER :=0;
985
986 BEGIN
987
988 --------------------------------------------------------------------
989 -- Update FII_GL_JE_SUMMARY_STG table for global amount after all error
990 -- checks passed.
991 --------------------------------------------------------------------
992 g_phase := 'Update global amount in FII_GL_JE_SUMMARY_STG table';
993
994 if g_debug_flag = 'Y' then
995 FII_UTIL.start_timer;
996 FII_UTIL.put_line('Updating global amount in FII_GL_JE_SUMMARY_STG');
997 end if;
998
999 Update FII_GL_JE_SUMMARY_STG stg
1000 SET stg.prim_amount_g = round((stg.amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,
1001 stg.sec_amount_g = round((stg.amount_b * sec_conversion_rate)/g_secondary_mau)*g_secondary_mau,
1002 stg.committed_amount_prim = round((stg.committed_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,
1003 stg.obligated_amount_prim = round((stg.obligated_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,
1004 stg.other_amount_prim = round((stg.other_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau;
1005
1006 if g_debug_flag = 'Y' then
1007 FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records in FII_GL_JE_SUMMARY_STG');
1008 FII_UTIL.stop_timer;
1009 FII_UTIL.print_timer('Duration');
1010 end if;
1011
1012 ---------------------------------------------------------------------
1013 --Insert aggregate data into FII_GL_JE_SUMMARY_STG table for higher
1014 --time levels Period, Quarter and Year.
1015 ---------------------------------------------------------------------
1016 g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table';
1017
1018 if g_debug_flag = 'Y' then
1019 FII_UTIL.put_line('');
1020 FII_UTIL.put_line('Inserting aggregated data into FII_GL_JE_SUMMARY_STG table');
1021 FII_UTIL.start_timer;
1022 end if;
1023
1024 --
1025 --bug 3356106: remove rollup by week_id (it's now handled in Sum_aggregate_week)
1026 --
1027
1028 INSERT INTO fii_gl_je_summary_stg
1029 (year,
1030 quarter,
1031 period,
1032 day,
1033 cost_center_id,
1034 fin_category_id,
1035 company_id,
1036 prod_category_id,
1037 user_dim1_id,
1038 user_dim2_id,
1039 je_source,
1040 je_category,
1041 effective_date,
1042 ledger_id,
1043 chart_of_accounts_id,
1044 functional_currency,
1045 amount_b,
1046 prim_amount_g,
1047 sec_amount_g,
1048 committed_amount_b,
1049 committed_amount_prim,
1050 obligated_amount_b,
1051 obligated_amount_prim,
1052 other_amount_b,
1053 other_amount_prim,
1054 posted_date,
1055 last_update_date,
1056 last_updated_by,
1057 creation_date,
1058 created_by,
1059 last_update_login)
1060 SELECT fday.ent_year_id,
1061 fday.ent_qtr_id,
1062 fday.ent_period_id,
1063 TO_NUMBER(NULL),
1064 stg.cost_center_id,
1065 stg.fin_category_id,
1066 stg.company_id,
1067 stg.prod_category_id,
1068 stg.user_dim1_id,
1069 stg.user_dim2_id,
1070 stg.je_source,
1071 stg.je_category,
1072 MAX(stg.effective_date),
1073 stg.ledger_id,
1074 stg.chart_of_accounts_id,
1075 stg.functional_currency,
1076 SUM(stg.amount_b) amount_b,
1077 SUM(stg.prim_amount_g) prim_amount_g,
1078 SUM(stg.sec_amount_g) sec_amount_g,
1079 SUM(committed_amount_b) committed_amount_b,
1080 SUM(committed_amount_prim) committed_amount_prim,
1081 SUM(obligated_amount_b) obligated_amount_b,
1082 SUM(obligated_amount_prim) obligated_amount_prim,
1083 SUM(other_amount_b) other_amount_b,
1084 SUM(other_amount_prim) other_amount_prim,
1085 stg.posted_date,
1086 stg.last_update_date,
1087 stg.last_updated_by,
1088 stg.creation_date,
1089 stg.created_by,
1090 stg.last_update_login
1091 FROM fii_gl_je_summary_stg stg,
1092 fii_time_day fday
1093 WHERE stg.day = fday.report_date_julian
1094 GROUP BY
1095 stg.cost_center_id,
1096 stg.fin_category_id,
1097 stg.company_id,
1098 stg.prod_category_id,
1099 stg.user_dim1_id,
1100 stg.user_dim2_id,
1101 stg.je_source,
1102 stg.je_category,
1103 stg.ledger_id,
1104 stg.chart_of_accounts_id,
1105 stg.functional_currency,
1106 stg.posted_date,
1107 stg.last_update_date,
1108 stg.last_updated_by,
1109 stg.creation_date,
1110 stg.created_by,
1111 stg.last_update_login,
1112 ROLLUP (fday.ent_year_id,
1113 fday.ent_qtr_id,
1114 fday.ent_period_id);
1115
1116
1117 if g_debug_flag = 'Y' then
1118 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
1119 ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
1120 FII_UTIL.stop_timer;
1121 FII_UTIL.print_timer('Duration');
1122 end if;
1123
1124 Exception
1125 WHEN OTHERS Then
1126 g_retcode := -1;
1127 FII_UTIL.put_line('
1128 Error in phase ' || g_phase || ' of Summarize_aggregate procedure' || '
1129 Message: ' || sqlerrm);
1130 ROLLBACK;
1131 raise;
1132 END Summarize_aggregate;
1133
1134 -----------------------------------------------------------------------
1135 -- PROCEDURE MERGE
1136 -----------------------------------------------------------------------
1137 PROCEDURE MERGE IS
1138
1139 BEGIN
1140
1141 ----------------------------------------------------------------------
1142 -- Merges newly collected/summarized records from temporary table
1143 -- FII_GL_JE_SUMMARY_STG into GL base summary table FII_GL_JE_SUMMARY_B.
1144 -- FII_GL_JE_SUMMARY_B uses the nested summary table structure.
1145
1146 -- If the merging record is new in FII_GL_JE_SUMMARY_STG then the record
1147 -- will be inserted into FII_GL_JE_SUMMARY_B table. A merging reord is
1148 -- consoidered if the combination of period, Period_type,
1149 -- Cost center Organization id, natural Account, Journal Entry source,
1150 -- Journal Entry category, Set Of Books Id, Functional Currency Code,
1151 -- Company and Product code feilds is not present in the
1152 -- FII_GL_JE_SUMMARY_B table.
1153 -----------------------------------------------------------------------
1154
1155 g_phase := 'Merging records into FII_GL_JE_SUMMARY_B';
1156
1157 if g_debug_flag = 'Y' then
1158 FII_UTIL.put_line('Merging records into FII_GL_JE_SUMMARY_B');
1159 FII_UTIL.start_timer;
1160 end if;
1161
1162 MERGE INTO fii_gl_je_summary_b bsum
1163 USING
1164 (SELECT NVL(day, NVL(week, NVL(period, NVL(quarter, year)))) TIME_ID,
1165 DECODE(day, null,
1166 DECODE(week, null,
1167 DECODE(period, null,
1168 DECODE(quarter, null, 128, 64), 32), 16), 1)
1169 PERIOD_TYPE_ID,
1170 COST_CENTER_ID,
1171 PROD_CATEGORY_ID,
1172 USER_DIM1_ID,
1173 USER_DIM2_ID,
1174 FIN_CATEGORY_ID,
1175 COMPANY_ID,
1176 JE_SOURCE, JE_CATEGORY, LEDGER_ID,
1177 CHART_OF_ACCOUNTS_ID,
1178 FUNCTIONAL_CURRENCY,
1179 SUM(AMOUNT_B) AMOUNT_B,
1180 SUM(PRIM_AMOUNT_G) PRIM_AMOUNT_G,
1181 SUM(SEC_AMOUNT_G) SEC_AMOUNT_G,
1182 SUM(COMMITTED_AMOUNT_B) COMMITTED_AMOUNT_B,
1183 SUM(COMMITTED_AMOUNT_PRIM) COMMITTED_AMOUNT_PRIM,
1184 SUM(OBLIGATED_AMOUNT_B) OBLIGATED_AMOUNT_B,
1185 SUM(OBLIGATED_AMOUNT_PRIM) OBLIGATED_AMOUNT_PRIM,
1186 SUM(OTHER_AMOUNT_B) OTHER_AMOUNT_B,
1187 SUM(OTHER_AMOUNT_PRIM) OTHER_AMOUNT_PRIM,
1188 POSTED_DATE
1189 FROM fii_gl_je_summary_stg
1190 WHERE year IS NOT NULL
1191 OR week IS NOT NULL
1192 GROUP BY
1193 NVL(day, NVL(week, NVL(period, NVL(quarter, year)))),
1194 DECODE(day, null,
1195 DECODE(week, null,
1196 DECODE(period, null,
1197 DECODE(quarter, null, 128, 64), 32), 16), 1),
1198 COST_CENTER_ID,
1199 PROD_CATEGORY_ID,
1200 USER_DIM1_ID,
1201 USER_DIM2_ID,
1202 FIN_CATEGORY_ID,
1203 COMPANY_ID,
1204 JE_SOURCE, JE_CATEGORY, LEDGER_ID,
1205 CHART_OF_ACCOUNTS_ID,
1206 FUNCTIONAL_CURRENCY,
1207 POSTED_DATE) s
1208 ON (bsum.time_id = s.time_id AND
1209 bsum.period_type_id = s.period_type_id AND
1210 bsum.cost_center_id = s.cost_center_id AND
1211 bsum.fin_category_id = s.fin_category_id AND
1212 bsum.je_source = s.je_source AND
1213 bsum.je_category = s.je_category AND
1214 bsum.ledger_id = s.ledger_id AND
1215 bsum.chart_of_accounts_id = s.chart_of_accounts_id AND
1216 bsum.functional_currency = s.functional_currency AND
1217 bsum.company_id = s.company_id AND
1218 bsum.prod_category_id = s.prod_category_id AND
1219 bsum.user_dim1_id = s.user_dim1_id AND
1220 bsum.user_dim2_id = s.user_dim2_id AND
1221 NVL(bsum.posted_date, g_global_start_date) = NVL(s.posted_date, g_global_start_date))
1222 WHEN MATCHED THEN
1223 UPDATE SET bsum.amount_b = bsum.amount_b+ s.amount_b,
1224 bsum.prim_amount_g = bsum.prim_amount_g + s.prim_amount_g,
1225 bsum.sec_amount_g = bsum.sec_amount_g + s.sec_amount_g,
1226 bsum.committed_amount_b = bsum.committed_amount_b+ s.committed_amount_b,
1227 bsum.committed_amount_prim = bsum.committed_amount_prim + s.committed_amount_prim,
1228 bsum.obligated_amount_b = bsum.obligated_amount_b+ s.obligated_amount_b,
1229 bsum.obligated_amount_prim = bsum.obligated_amount_prim + s.obligated_amount_prim,
1230 bsum.other_amount_b = bsum.other_amount_b+ s.other_amount_b,
1231 bsum.other_amount_prim = bsum.other_amount_prim + s.other_amount_prim,
1232 bsum.last_update_date = sysdate,
1233 bsum.last_update_login = g_fii_login_id,
1234 bsum.last_updated_by = g_fii_user_id
1235 WHEN NOT MATCHED THEN INSERT (bsum.time_id,
1236 bsum.period_type_id,
1237 bsum.company_id,
1238 bsum.cost_center_id,
1239 bsum.fin_category_id,
1240 bsum.prod_category_id,
1241 bsum.user_dim1_id,
1242 bsum.user_dim2_id,
1243 bsum.je_source,
1244 bsum.je_category,
1245 bsum.ledger_id,
1246 bsum.chart_of_accounts_id,
1247 bsum.functional_currency,
1248 bsum.amount_B,
1249 bsum.prim_amount_G,
1250 bsum.sec_amount_G,
1251 bsum.committed_amount_b,
1252 bsum.committed_amount_prim,
1253 bsum.obligated_amount_b,
1254 bsum.obligated_amount_prim,
1255 bsum.other_amount_b,
1256 bsum.other_amount_prim,
1257 bsum.posted_date,
1258 bsum.creation_date,
1259 bsum.created_by,
1260 bsum.last_update_date,
1261 bsum.last_update_login,
1262 bsum.last_updated_by)
1263 values (s.time_id,
1264 s.period_type_id,
1265 s.company_id,
1266 s.cost_center_id,
1267 s.fin_category_id,
1268 s.prod_category_id,
1269 s.user_dim1_id,
1270 s.user_dim2_id,
1271 s.je_source,
1272 s.je_category,
1273 s.ledger_id,
1274 s.chart_of_accounts_id,
1275 s.functional_currency,
1276 s.amount_B,
1277 s.prim_amount_G,
1278 s.sec_amount_G,
1279 s.committed_amount_b,
1280 s.committed_amount_prim,
1281 s.obligated_amount_b,
1282 s.obligated_amount_prim,
1283 s.other_amount_b,
1284 s.other_amount_prim,
1285 s.posted_date,
1286 sysdate,
1287 g_fii_user_id,
1288 sysdate,
1289 g_fii_login_id,
1290 g_fii_user_id);
1291
1292
1293 if g_debug_flag = 'Y' then
1294 FII_UTIL.put_line('Merged ' || SQL%ROWCOUNT || ' rows of records into FII_GL_JE_SUMMARY_B');
1295 FII_UTIL.stop_timer;
1296 FII_UTIL.print_timer('Duration');
1297 end if;
1298
1299 Exception
1300 WHEN OTHERS Then
1301 g_retcode := -1;
1302 FII_UTIL.put_line('
1303 ----------------------------
1304 Error in Function: Merge
1305 Message: '||sqlerrm);
1306 ROLLBACK;
1307 raise;
1308 END MERGE;
1309
1310 ------------------------------------------------------------------------
1311 -- PROCEDURE JOURNALS_PROCESSED
1312 ------------------------------------------------------------------------
1313 PROCEDURE JOURNALS_PROCESSED IS
1314
1315 BEGIN
1316
1317 if g_debug_flag = 'Y' then
1318 FII_UTIL.put_line ('Calling Journals_Processed Procedure');
1319 FII_UTIL.start_timer;
1320 end if;
1321
1322
1323 ---------------------------------------------------------------------
1324 -- Inserting processed JE Header IDs into FII_GL_PROCESSED_HEADER_IDS
1325 -- table. Not all JE Header IDs in FII_GL_NEW_JRLHEADER_IDS are
1326 -- processed. This is because when we select Header IDs to be
1327 -- processed (refer to NEW_JOURNALS function), we only filter by SOB
1328 -- in FII_COMPANY_SETS table, however when we extract data from OLTP
1329 -- tables, we actually filter data by both SOB and Company
1330 ---------------------------------------------------------------------
1331
1332 INSERT INTO fii_gl_processed_header_ids (
1333 je_header_id,
1334 creation_date,
1335 created_by,
1336 last_update_date,
1337 last_update_login,
1338 last_updated_by)
1339 SELECT je_header_id,
1340 sysdate,
1341 g_fii_user_id,
1342 sysdate,
1343 g_fii_login_id,
1344 g_fii_user_id
1345 FROM fii_gl_new_jrl_header_ids;
1346
1347 if g_debug_flag = 'Y' then
1348 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows into FII_GL_PROCESSED_HEADER_IDS');
1349 FII_UTIL.stop_timer;
1350 FII_UTIL.print_timer('Duration');
1351 end if;
1352
1353 Exception
1354 WHEN OTHERS Then
1355 g_retcode := -1;
1356 FII_UTIL.put_line('
1357 ----------------------------
1358 Error in Function: Journal_processed
1359 Message: '||sqlerrm);
1360 ROLLBACK;
1361 raise;
1362 END Journals_Processed;
1363
1364 -----------------------------------------------------------------------
1365 -- FUNCTION NEW_JOURNALS
1366 -----------------------------------------------------------------------
1367 Function New_Journals(P_Start_Date IN DATE ,
1368 P_End_Date IN DATE) RETURN NUMBER IS
1369 l_number_of_rows NUMBER :=0;
1370
1371 BEGIN
1372
1373 ----------------------------------------------------------------------
1374 -- Insert into a table to hold journal header ids which are never
1375 -- processed (Not exist in fii_gl_processed_header_id table.
1376 -- Posted Journals only
1377 -- And Journal entry line effective date falls within user specified
1378 -- date range.
1379 -- In future the header ids will be filtered on given set of books id.
1380 -----------------------------------------------------------------------
1381 if g_debug_flag = 'Y' then
1382 FII_UTIL.put_line(' ');
1383 FII_UTIL.put_line('Inserting New Journal header ids');
1384 FII_UTIL.start_timer;
1385 end if;
1386
1387 --Bug 3121847: changed the hint per performance team suggestion
1388
1389 --Added filtering by JE category/source for DBI 6.0
1390
1391 INSERT /*+ append */ INTO fii_gl_new_jrl_header_ids
1392 (record_id,
1393 je_header_id,
1394 currency_code,
1395 je_source,
1396 je_category,
1397 encumbrance_type,
1398 actual_flag,
1399 posted_date)
1400 SELECT /*+ use_hash(per, jeh, fset,fgph) parallel(jeh) parallel(fgph) */
1401 rownum,
1402 jeh.je_header_id,
1403 jeh.currency_code,
1404 jeh.je_source,
1405 jeh.je_category,
1406 decode(g_industry, 'G', NVL(etype.encumbrance_type, 'OTHERS'),
1407 'C', NULL) encumbrance_type,
1408 jeh.actual_flag,
1409 decode(g_industry,
1410 'G', decode(jeh.actual_flag, --for Government
1411 'A', g_global_start_date, -- for actuals
1412 per2.start_date), --jeh.posted_date), -- for encumbrances
1413 null) -- for Commercial
1414 FROM (
1415 SELECT p.period_name, s.ledger_id
1416 FROM gl_periods p, gl_ledgers_public_v s
1417 WHERE p.start_date <= NVL(P_End_Date, start_date)
1418 AND p.end_date >= P_Start_Date
1419 AND p.period_set_name = s.period_set_name) per,
1420 (SELECT DISTINCT
1421 slga.ledger_id,
1422 DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_SOURCE_NAME) je_source_name,
1423 DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_CATEGORY_NAME) je_category_name
1424 FROM fii_slg_assignments slga,
1425 gl_je_inclusion_rules rule,
1426 fii_source_ledger_groups fslg
1427 WHERE slga.je_rule_set_id = rule.je_rule_set_id (+)
1428 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
1429 AND fslg.usage_code = g_usage_code) fset,
1430 gl_je_headers jeh,
1431 fii_encum_type_mappings etype,
1432 fii_gl_processed_header_ids fgph,
1433 gl_periods per2,
1434 gl_ledgers_public_v s2
1435 WHERE jeh.ledger_id = fset.ledger_id
1436 AND jeh.encumbrance_type_id = etype.encumbrance_type_id (+)
1437 AND (jeh.je_source = fset.je_source_name OR fset.je_source_name = '-1')
1438 -- Bug 5026804: Exclude the journal source - Closing Journal
1439 AND jeh.je_source <> 'Closing Journal'
1440 AND (jeh.je_category = fset.je_category_name OR fset.je_category_name = '-1')
1441 AND jeh.currency_code <> 'STAT'
1442 AND jeh.period_name = per.period_name
1443 AND jeh.ledger_id = per.ledger_id
1444 AND jeh.je_header_id = fgph.je_header_id(+)
1445 AND fgph.je_header_id IS NULL
1446 AND jeh.status = 'P'
1447 AND decode (jeh.actual_flag,
1448 'A',1,
1449 'E',1,
1450 0) = 1
1451 AND jeh.ledger_id = s2.ledger_id
1452 AND s2.period_set_name = per2.period_set_name
1453 AND trunc(jeh.posted_date) between per2.start_date and per2.end_date
1454 AND per2.period_type = s2.accounted_period_type
1455 AND per2.adjustment_period_flag = 'N' ;
1456
1457 l_number_of_rows := SQL%ROWCOUNT;
1458
1459 if g_debug_flag = 'Y' then
1460 FII_UTIL.put_line('Inserted '||l_number_of_rows||
1461 ' JE header IDs into FII_GL_NEW_JRL_HEADER_IDS');
1462 FII_UTIL.stop_timer;
1463 FII_UTIL.print_timer('Duration');
1464 FII_UTIL.put_line('');
1465 end if;
1466
1467 COMMIT;
1468 return(l_number_of_rows);
1469
1470 Exception
1471 WHEN OTHERS Then
1472 g_retcode := -1;
1473 FII_UTIL.put_line('
1474 ----------------------------
1475 Error in New_Journals Procedure
1476 Message: '||sqlerrm);
1477 RAISE;
1478 END New_Journals;
1479
1480 -------------------------------------------------------
1481 -- PROCEDURE SUMMARIZE_DAY
1482 -------------------------------------------------------
1483 PROCEDURE SUMMARIZE_DAY(p_start_range NUMBER,
1484 p_end_range NUMBER) IS
1485 l_number_of_rows NUMBER :=0;
1486 l_stmt VARCHAR2(10000);
1487
1488 BEGIN
1489
1490 ------------------------------------------------------------------
1491 -- Insert summarize journal entry lines at day level whose journal
1492 -- Header IDs are stored in FII_GL_NEW_JRL_HEADER_IDS table into
1493 -- FII_GL_JE_SUMMARY_STG.
1494 ------------------------------------------------------------------
1495 if g_debug_flag = 'Y' then
1496 FII_UTIL.put_line('Processing ID range: ' || p_start_range ||
1497 ' to ' || p_end_range);
1498 end if;
1499
1500 l_stmt:= 'INSERT INTO FII_GL_JE_SUMMARY_STG
1501 (day,
1502 week,
1503 period,
1504 quarter,
1505 year,
1506 company_id,
1507 cost_center_id,
1508 fin_category_id,
1509 prod_category_id,
1510 user_dim1_id,
1511 user_dim2_id,
1512 je_source,
1513 je_category,
1514 ledger_id,
1515 effective_date,
1516 chart_of_accounts_id,
1517 functional_currency,
1518 amount_b,
1519 committed_amount_b,
1520 obligated_amount_b,
1521 other_amount_b,
1522 prim_conversion_rate,
1523 sec_conversion_rate,
1524 posted_date,
1525 last_update_date,
1526 last_updated_by,
1527 creation_date,
1528 created_by,
1529 last_update_login)
1530 SELECT /*+ ORDERED USE_NL(njhi line sob fin) */
1531 to_number(to_char(line.effective_date,''J'')) ,
1532 to_number(NULL, 999),
1533 to_number(NULL, 999) ,
1534 to_number(NULL, 999) ,
1535 999, -- Insert 999 for year field so this record is merged into summary
1536 fin.company_id,
1537 fin.cost_center_id,
1538 fin.natural_account_id,
1539 NVL(fin.prod_category_id, -1),
1540 fin.user_dim1_id,
1541 fin.user_dim2_id,
1542 njhi.je_source ,
1543 njhi.je_category ,
1544 sob.ledger_id,
1545 line.effective_date,
1546 sob.chart_of_accounts_id,
1547 sob.currency_code,
1548 decode(njhi.actual_flag,
1549 ''A'', sum(NVL(line.accounted_cr, 0) - NVL(line.accounted_dr, 0)),
1550 0),
1551 decode(njhi.actual_flag,
1552 ''E'', decode(njhi.encumbrance_type,
1553 ''COMMITMENT'', sum(NVL(line.accounted_cr, 0) - NVL(line.accounted_dr, 0)),
1554 0),
1555 0), -- For encumbrances: requisitions (committed_amount)
1556 decode(njhi.actual_flag,
1557 ''E'', decode(njhi.encumbrance_type,
1558 ''OBLIGATION'', sum(NVL(line.accounted_cr, 0) - NVL(line.accounted_dr, 0)),
1559 0),
1560 0), -- For encumbrances: purchase orders (obligated_amount)
1561 decode(njhi.actual_flag,
1562 ''E'', decode(njhi.encumbrance_type,
1563 ''OTHERS'', sum(NVL(line.accounted_cr, 0) - NVL(line.accounted_dr, 0)),
1564 0),
1565 0), -- For encumbrances: others (other_amount)
1566 fii_currency.get_global_rate_primary(sob.currency_code, least(sysdate, line.effective_date)),
1567 fii_currency.get_global_rate_secondary(sob.currency_code, least(sysdate, line.effective_date)),
1568 decode('''||g_industry||''',
1569 ''G'', decode(njhi.actual_flag, --for Government
1570 ''A'', null, -- for actuals
1571 njhi.posted_date), -- for encumbrances
1572 null), -- for Commercial
1573 sysdate, ' ||
1574 g_fii_user_id || ',
1575 sysdate, ' ||
1576 g_fii_user_id || ',' ||
1577 g_fii_login_id || '
1578 FROM fii_gl_new_jrl_header_ids njhi,
1579 gl_je_lines line,
1580 gl_ledgers_public_v sob,
1581 fii_gl_ccid_dimensions fin,
1582 fii_slg_assignments slga,
1583 fii_source_ledger_groups fslg
1584 WHERE njhi.je_header_id = line.je_header_id
1585 AND line.ledger_id = sob.ledger_id
1586 AND line.code_combination_id = fin.code_combination_id
1587 AND ( fin.company_id = slga.bal_seg_value_id OR slga.bal_seg_value_id = -1 )
1588 AND fin.chart_of_accounts_id = slga.chart_of_accounts_id
1589 AND line.ledger_id = slga.ledger_id
1590 AND njhi.record_id >= '|| p_start_range || '
1591 AND njhi.record_id <= ' || p_end_range || '
1592 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
1593 AND fslg.usage_code = ''' || g_usage_code || '''
1594 GROUP BY line.effective_date,
1595 fin.company_id,
1596 fin.cost_center_id,
1597 fin.natural_account_id,
1598 NVL(fin.prod_category_id, -1),
1599 fin.user_dim1_id,
1600 fin.user_dim2_id,
1601 njhi.je_source,
1602 njhi.je_category,
1603 sob.ledger_id,
1604 sob.chart_of_accounts_id,
1605 sob.currency_code,
1606 njhi.encumbrance_type,
1607 njhi.actual_flag,
1608 decode('''||g_industry||''',
1609 ''G'', decode(njhi.actual_flag, --for Government
1610 ''A'', null, -- for actuals
1611 njhi.posted_date), -- for encumbrances
1612 null)'; -- for Commercial
1613
1614 if g_debug_flag = 'Y' then
1615 FII_UTIL.start_timer;
1616 FII_UTIL.put_line('');
1617 FII_UTIL.put_line(l_stmt);
1618 end if;
1619
1620
1621 EXECUTE IMMEDIATE l_stmt;
1622
1623 l_number_of_rows := SQL%ROWCOUNT;
1624
1625 commit;
1626
1627 if g_debug_flag = 'Y' then
1628 FII_UTIL.put_line('');
1629 FII_UTIL.put_line('Inserted '||l_number_of_rows||' into table FII_GL_JE_SUMMARY_STG with day level data');
1630 FII_UTIL.stop_timer;
1631 FII_UTIL.print_timer('Duration');
1632 end if;
1633
1634 EXCEPTION
1635 WHEN OTHERS Then
1636 g_retcode := -1;
1637 FII_UTIL.put_line('
1638 ----------------------------
1639 Error in Function: Summarize_day
1640 Message: '||sqlerrm);
1641 raise;
1642 END Summarize_day;
1643
1644 ---------------------------------------------------------------
1645 -- PROCEDURE VERIFY_CCID_UP_TO_DATE
1646 ---------------------------------------------------------------
1647 PROCEDURE VERIFY_CCID_UP_TO_DATE IS
1648 l_errbuf VARCHAR2(1000);
1649 l_retcode VARCHAR2(100);
1650 l_request_id NUMBER;
1651 l_result BOOLEAN;
1652 l_phase VARCHAR2(500) := NULL;
1653 l_status VARCHAR2(500) := NULL;
1654 l_devphase VARCHAR2(500) := 'PENDING';
1655 l_devstatus VARCHAR2(500) := NULL;
1656 l_message VARCHAR2(500) := NULL;
1657 l_dummy BOOLEAN;
1658 l_submit_failed EXCEPTION;
1659 l_call_status boolean;
1660
1661 BEGIN
1662
1663 if g_debug_flag = 'Y' then
1664 FII_UTIL.put_line('Calling Procedure: VERIFY_CCID_UP_TO_DATE');
1665 FII_UTIL.put_line('');
1666 end if;
1667
1668 IF(FII_GL_CCID_C.NEW_CCID_IN_GL) THEN
1669 if g_debug_flag = 'Y' then
1670 FII_UTIL.put_line('CCID Dimension is not up to date, calling CCID Dimension update program');
1671 end if;
1672
1673 g_phase := 'Calling CCID Dimension update program';
1674 l_dummy := FND_REQUEST.SET_MODE(TRUE);
1675 l_request_id := FND_REQUEST.SUBMIT_REQUEST('FII', 'FII_GL_CCID_C',
1676 NULL, NULL, FALSE, 'I');
1677 commit;
1678
1679 IF (l_request_id = 0) THEN
1680 rollback;
1681 g_retcode := -1;
1682 FII_UTIL.put_line('
1683 ---------------------------------
1684 Error in Procedure: VERIFY_CCID_UP_TO_DATE
1685 Message: '||fnd_message.get);
1686 raise G_NO_CHILD_PROCESS;
1687 END IF;
1688
1689 g_phase := 'Calling FND_CONCURRENT.wait_for_request';
1690 l_result := FND_CONCURRENT.wait_for_request(request_id => l_request_id,
1691 interval => 30,
1692 max_wait => 3600,
1693 phase => l_phase,
1694 status => l_status,
1695 dev_phase => l_devphase,
1696 dev_status => l_devstatus,
1697 message => l_message);
1698
1699 g_phase := 'Finished calling FND_CONCURRENT.wait_for_request -> ' || l_devphase || ', ' || l_devstatus;
1700 IF (NVL(l_devphase='COMPLETE' AND l_devstatus='NORMAL', FALSE)) THEN
1701 if g_debug_flag = 'Y' then
1702 FII_UTIL.put_line('CCID Dimension populated successfully');
1703 end if;
1704 ELSE
1705 if g_debug_flag = 'Y' then
1706 FII_UTIL.put_line('CCID Dimension populated unsuccessfully');
1707 end if;
1708 raise G_CCID_FAILED;
1709 END IF;
1710
1711 ELSE
1712
1713 if g_debug_flag = 'Y' then
1714 FII_UTIL.put_line('CCID Dimension is up to date');
1715 FII_UTIL.put_line('');
1716 end if;
1717
1718 END IF;
1719
1720 Exception
1721 WHEN G_NO_CHILD_PROCESS THEN
1722 g_retcode := -1;
1723 FII_UTIL.put_line('
1724 ----------------------------
1725 Error in Procedure : VERIFY_CCID_UP_TO_DATE
1726 Phase: Submitting Child process to run CCID program');
1727 raise;
1728 WHEN G_CCID_FAILED THEN
1729 g_retcode := -1;
1730 FII_UTIL.put_line('
1731 ----------------------------
1732 Error in Procedure : VERIFY_CCID_UP_TO_DATE when running CCID program
1733 Phase: ' || g_phase);
1734 raise;
1735 WHEN OTHERS Then
1736 g_retcode := -1;
1737 FII_UTIL.put_line('
1738 ----------------------------
1739 Error in Procedure : VERIFY_CCID_UP_TO_DATE
1740 Phase: ' || g_phase || '
1741 Message: '||sqlerrm);
1742 raise;
1743 END VERIFY_CCID_UP_TO_DATE;
1744
1745
1746 ---------------------------------------------------------------
1747 -- PROCEDURE POPULATE_ENCUM_MAPPING
1748 ---------------------------------------------------------------
1749 PROCEDURE POPULATE_ENCUM_MAPPING IS
1750 l_count NUMBER;
1751
1752 CURSOR invalid_lookup_cur IS
1753 SELECT a.lookup_code,
1754 decode(a.lookup_type, 'FII_PSI_ENCUM_TYPES_OBLIGATION', 'Obligation',
1755 'FII_PSI_ENCUM_TYPES_COMMITMENT', 'Commitment') lookup_type
1756 FROM fnd_lookup_values a
1757 WHERE a.lookup_type in ( 'FII_PSI_ENCUM_TYPES_OBLIGATION',
1758 'FII_PSI_ENCUM_TYPES_COMMITMENT')
1759 AND a.view_application_id = 450
1760 AND a.language = userenv('LANG')
1761 AND upper(a.lookup_code) not in (select upper(encumbrance_type)
1762 from gl_encumbrance_types);
1763 BEGIN
1764
1765 IF g_debug_flag = 'Y' THEN
1766 FII_UTIL.put_line('In procedure POPULATE_ENCUM_MAPPING():');
1767 FII_UTIL.put_line('');
1768 END IF;
1769
1770 ---------------------------------------------------------------------------
1771 -- Truncate fii_encum_type_mappings
1772 ---------------------------------------------------------------------------
1773 IF g_debug_flag = 'Y' THEN
1774 fii_util.put_line(' ');
1775 fii_util.put_line('Truncate fii_encum_type_mappings...');
1776 END IF;
1777 TRUNCATE_TABLE('FII_ENCUM_TYPE_MAPPINGS');
1778
1779 INSERT INTO fii_encum_type_mappings
1780 (encumbrance_type_id,
1781 encumbrance_type,
1782 last_update_date,
1783 last_updated_by,
1784 creation_date,
1785 created_by,
1786 last_update_login)
1787 SELECT b.encumbrance_type_id,
1788 decode(a.lookup_type, 'FII_PSI_ENCUM_TYPES_OBLIGATION', 'OBLIGATION',
1789 'FII_PSI_ENCUM_TYPES_COMMITMENT', 'COMMITMENT'),
1790 sysdate,
1791 g_fii_user_id,
1792 sysdate,
1793 g_fii_user_id,
1794 g_fii_login_id
1795 FROM fnd_lookup_values a,
1796 gl_encumbrance_types b
1797 WHERE a.lookup_type in ( 'FII_PSI_ENCUM_TYPES_OBLIGATION',
1798 'FII_PSI_ENCUM_TYPES_COMMITMENT')
1799 AND a.view_application_id = 450
1800 AND a.language = userenv('LANG')
1801 AND upper(a.lookup_code) = upper(b.encumbrance_type);
1802
1803 IF g_debug_flag = 'Y' THEN
1804 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_encum_type_mappings');
1805 fii_util.stop_timer;
1806 fii_util.print_timer('Duration');
1807 END IF;
1808
1809 -- Print a warning message if there is any lookup_code that does not match the
1810 -- encumbrance_type defined in gl_encumbrance_types
1811 l_count := 0;
1812 FOR invalid_lookup_codes in invalid_lookup_cur LOOP
1813 IF (l_count = 0) THEN
1814 fii_util.put_line(' ');
1815 fii_util.put_line(
1816 'WARNING: Invalid lookup codes found in the encumbrance type mappings. ');
1817 fii_util.put_line('Please make sure these lookup codes are valid GL Encumbrance Types.');
1818 fii_util.put_line('Lookup Type Lookup Code ');
1819 fii_util.put_line('----------- -----------');
1820 l_count := l_count + 1;
1821 END IF;
1822
1823 fii_util.put_line(invalid_lookup_codes.lookup_type ||' '||
1824 invalid_lookup_codes.lookup_code);
1825
1826 END LOOP;
1827
1828 -- Raise an error if the mapping table is empty
1829 IF (SQL%ROWCOUNT = 0) THEN
1830 fii_util.put_line('The mapping table between GL Encumrbance Type and FII Encumbrance bucket (fii_encum_type_mappings) is empty. Please enter the encumbrance type mappings.');
1831 raise G_MISSING_ENCUM_MAPPING;
1832 END IF;
1833
1834 commit;
1835
1836 Exception
1837 WHEN G_MISSING_ENCUM_MAPPING Then
1838 g_retcode := -1;
1839 FII_UTIL.put_line('POPULATE_ENCUM_MAPPING:Encumbrance mapping is missing.');
1840 raise;
1841
1842 WHEN OTHERS Then
1843 g_retcode := -1;
1844 FII_UTIL.put_line('
1845 ----------------------------
1846 Error in Procedure : POPULATE_ENCUM_MAPPING
1847 Phase: ' || g_phase || '
1848 Message: '||sqlerrm);
1849 raise;
1850 END POPULATE_ENCUM_MAPPING;
1851
1852 ------------------------------------------
1853 -- PROCEDURE Insert_Into_Stg
1854 ------------------------------------------
1855
1856
1857 PROCEDURE INSERT_INTO_STG (p_sort_area_size IN NUMBER,
1858 p_hash_area_size IN NUMBER,
1859 l_start_date IN DATE,
1860 l_end_date IN DATE) IS
1861
1862 l_stmt VARCHAR2(1000);
1863
1864 BEGIN
1865
1866 g_phase := 'Calling alter session set sort_area_size';
1867 l_stmt := 'alter session set sort_area_size= '|| p_sort_area_size;
1868 execute immediate l_stmt;
1869
1870 g_phase := 'Calling alter session set hash_area_size';
1871 l_stmt := 'alter session set hash_area_size= ' ||p_hash_area_size;
1872 execute immediate l_stmt;
1873
1874 if g_debug_flag = 'Y' then
1875 fii_util.put_line(' ');
1876 fii_util.put_line('Loading data into staging table');
1877 fii_util.start_timer;
1878 fii_util.put_line('');
1879 end if;
1880
1881 g_phase := 'Inserting into FII_GL_JE_SUMMARY_STG';
1882 INSERT /*+ append parallel(fii_gl_je_summary_stg) */ INTO FII_GL_JE_SUMMARY_STG
1883 (day,
1884 week,
1885 period,
1886 quarter,
1887 year,
1888 company_id,
1889 cost_center_id,
1890 fin_category_id,
1891 prod_category_id,
1892 user_dim1_id,
1893 user_dim2_id,
1894 je_source,
1895 je_category,
1896 ledger_id,
1897 effective_date,
1898 chart_of_accounts_id,
1899 functional_currency,
1900 amount_b,
1901 prim_conversion_rate,
1902 sec_conversion_rate,
1903 committed_amount_b,
1904 obligated_amount_b,
1905 other_amount_b,
1906 posted_date,
1907 last_update_date,
1908 last_updated_by,
1909 creation_date,
1910 created_by,
1911 last_update_login)
1912 SELECT /*+ ORDERED parallel(v1) parallel(line) use_hash(line,fset2) use_nl(fin)
1913 swap_join_inputs(sob) swap_join_inputs(fset2) pq_distribute(fset2,none,broadcast) */
1914 to_number(to_char(line.effective_date,'J')) ,
1915 to_number(NULL, 999),
1916 to_number(NULL, 999) ,
1917 to_number(NULL, 999) ,
1918 999, -- Insert value into YEAR field so this day level record can be inserted into summary table
1919 fin.company_id,
1920 fin.cost_center_id,
1921 fin.natural_account_id,
1922 NVL(fin.prod_category_id, -1),
1923 fin.user_dim1_id,
1924 fin.user_dim2_id,
1925 v1.je_source ,
1926 v1.je_category ,
1927 fset2.set_of_books_id,
1928 line.effective_date,
1929 fset2.chart_accs_id_sob,
1930 fset2.currency_code,
1931 decode(v1.actual_flag,
1932 'A', sum(NVL(line.accounted_cr, 0) - NVL(line.accounted_dr, 0)),
1933 0),
1934 -- fii_currency.get_global_rate_primary(sob.currency_code, line.effective_date),
1935 -- fii_currency.get_global_rate_secondary(sob.currency_code, line.effective_date),
1936 -1,
1937 -1,
1938 decode(v1.actual_flag,
1939 'E', decode(v1.encumbrance_type,
1940 'COMMITMENT', sum(NVL(line.accounted_cr, 0) -
1941 NVL(line.accounted_dr, 0)),
1942 0),
1943 0), -- For encumbrances: requisitions (committed_amount)
1944 decode(v1.actual_flag,
1945 'E', decode(v1.encumbrance_type,
1946 'OBLIGATION', sum(NVL(line.accounted_cr, 0) -
1947 NVL(line.accounted_dr, 0)),
1948 0),
1949 0), -- For encumbrances: purchase orders (obligated_amount)
1950 decode(v1.actual_flag,
1951 'E', decode(v1.encumbrance_type,
1952 'OTHERS', sum(NVL(line.accounted_cr, 0) -
1953 NVL(line.accounted_dr, 0)),
1954 0),
1955 0), -- For encumbrances: others (other_amount)
1956 decode(g_industry,
1957 'G', decode(v1.actual_flag,--for Government
1958 'A', null, -- for actuals
1959 v1.posted_date), -- for encumbrances
1960 null), -- for Commercial
1961 trunc(sysdate), -- bug 4323856
1962 g_fii_user_id,
1963 trunc(sysdate), -- bug 4323856
1964 g_fii_user_id,
1965 g_fii_login_id
1966 -- rewrite the v1 inline view beased on perf team's suggestion bug 4214956
1967 -- [old definition of vi inline view]
1968 --
1969 -- FROM (
1970 -- SELECT /*+ no_merge ordered parallel(jeh) parallel(per) parallel(fset) parallel(fgph) use_hash(jeh,per,fset,fgph) */
1971 /* jeh.je_header_id,
1972 jeh.currency_code,
1973 jeh.je_source,
1974 jeh.je_category,
1975 jeh.posted_date, --Added for PSI
1976 jeh.encumbrance_type_id, --Added for PSI
1977 jeh.actual_flag, --Added for PSI
1978 org.req_encumbrance_type_id,
1979 org.purch_encumbrance_type_id
1980 FROM gl_je_headers jeh,
1981 (select distinct hdrs.ledger_id, hdrs.je_batch_id, bat.org_id
1982 from gl_je_headers hdrs, gl_je_batches bat
1983 where hdrs.je_batch_id = bat.je_batch_id
1984 ) jeb,
1985 financials_system_params_all org,
1986 (
1987 SELECT p.period_name, s.ledger_id
1988 FROM gl_periods p, gl_ledgers_public_v s
1989 WHERE p.start_date <= l_end_date
1990 AND p.end_date >= l_start_date
1991 AND p.period_set_name = s.period_set_name) per,
1992 (SELECT DISTINCT
1993 slga.ledger_id,
1994 DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_SOURCE_NAME) je_source_name,
1995 DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_CATEGORY_NAME) je_category_name
1996 FROM fii_slg_assignments slga,
1997 gl_je_inclusion_rules rule,
1998 fii_source_ledger_groups fslg
1999 WHERE slga.je_rule_set_id = rule.je_rule_set_id (+)
2000 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
2001 AND fslg.usage_code = g_usage_code) fset,
2002 fii_gl_processed_header_ids fgph
2003 WHERE jeh.ledger_id = fset.ledger_id
2004 AND jeh.je_batch_id = jeb.je_batch_id
2005 AND jeb.org_id = org.org_id (+)
2006 AND jeb.ledger_id = org.set_of_books_id (+)
2007 AND (jeh.je_source = fset.je_source_name OR fset.je_source_name = '-1')
2008 AND (jeh.je_category = fset.je_category_name OR fset.je_category_name = '-1')
2009 AND jeh.currency_code <> 'STAT'
2010 AND jeh.period_name = per.period_name
2011 AND jeh.ledger_id = per.set_of_books_id
2012 AND jeh.je_header_id = fgph.je_header_id(+)
2013 AND fgph.je_header_id IS NULL
2014 AND jeh.status = 'P'
2015 AND decode (jeh.actual_flag,
2016 'A', 1,
2017 'E', 1,
2018 0) = 1
2019 ) v1,
2020 */
2021 -- rewrite the v1 inline view beased on perf team's suggestion bug 4214956
2022 -- [new definition of vi inline view]
2023 --
2024 FROM (
2025 SELECT /*+ no_merge ordered parallel(jeh) parallel(s) parallel(p) parallel(fset) parallel(fgph)
2026 use_hash(jeh ,per ,fset ,fgph) swap_join_inputs(fgph) swap_join_inputs(fset) */
2027 jeh.je_header_id,
2028 jeh.currency_code,
2029 jeh.je_source,
2030 jeh.je_category,
2031 p2.start_date posted_date, --jeh.posted_date, --Added for PSI
2032 jeh.encumbrance_type_id, --Added for PSI
2033 jeh.actual_flag, --Added for PSI
2034 decode(g_industry, 'G', NVL(etype.encumbrance_type, 'OTHERS'),
2035 'C', NULL) encumbrance_type
2036 FROM gl_ledgers_public_v s,
2037 gl_periods p,
2038 gl_periods p2,
2039 gl_je_headers jeh,
2040 fii_encum_type_mappings etype,
2041 (SELECT /*+ no_merge */ DISTINCT
2042 slga.ledger_id,
2043 DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_SOURCE_NAME) je_source_name,
2044 DECODE(slga.je_rule_set_id, NULL, '-1', rule.JE_CATEGORY_NAME) je_category_name
2045 FROM fii_slg_assignments slga,
2046 gl_je_inclusion_rules rule,
2047 fii_source_ledger_groups fslg
2048 WHERE slga.je_rule_set_id = rule.je_rule_set_id (+)
2049 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
2050 AND fslg.usage_code = g_usage_code) fset,
2051 fii_gl_processed_header_ids fgph
2052 WHERE jeh.ledger_id = fset.ledger_id
2053 AND (jeh.je_source = fset.je_source_name OR fset.je_source_name = '-1')
2054 -- Bug 5026804: Exclude the journal source - Closing Journal
2055 AND jeh.je_source <> 'Closing Journal'
2056 AND (jeh.je_category = fset.je_category_name OR fset.je_category_name = '-1')
2057 AND jeh.currency_code <> 'STAT'
2058 AND jeh.period_name = p.period_name
2059 AND jeh.ledger_id = s.ledger_id
2060 AND jeh.je_header_id = fgph.je_header_id(+)
2061 AND fgph.je_header_id IS NULL
2062 AND jeh.status = 'P'
2063 AND jeh.actual_flag IN ('A','E')
2064 AND jeh.encumbrance_type_id = etype.encumbrance_type_id (+)
2065 AND p.start_date <= l_end_date --:b3
2066 AND p.end_date >= l_start_date --:b2
2067 AND p.period_set_name = s.period_set_name
2068
2069 AND p2.period_set_name = s.period_set_name
2070 AND trunc(jeh.posted_date) between p2.start_date and p2.end_date
2071 AND p2.period_type = s.accounted_period_type
2072 AND p2.adjustment_period_flag = 'N'
2073
2074 ) v1,
2075 gl_je_lines line,
2076 ( SELECT /*+ no_merge */
2077 SOB.ledger_id set_of_books_id,
2078 SLGA2.ledger_id,
2079 SLGA2.bal_seg_value_id,
2080 SLGA2.chart_of_accounts_id,
2081 SOB.currency_code,
2082 SOB.CHART_OF_ACCOUNTS_ID chart_accs_id_sob
2083 FROM gl_ledgers_public_v SOB,
2084 FII_SLG_ASSIGNMENTS SLGA2,
2085 FII_SOURCE_LEDGER_GROUPS FSLG2
2086 WHERE SOB.LEDGER_ID = SLGA2.LEDGER_ID
2087 AND SLGA2.SOURCE_LEDGER_GROUP_ID = FSLG2.SOURCE_LEDGER_GROUP_ID
2088 AND FSLG2.USAGE_CODE = 'DBI'
2089 ) fset2,
2090 fii_gl_ccid_dimensions fin
2091 WHERE v1.je_header_id = line.je_header_id
2092 AND line.code_combination_id = fin.code_combination_id
2093 AND line.ledger_id = fset2.set_of_books_id
2094 AND line.ledger_id = fset2.ledger_id
2095 AND ( fin.company_id = fset2.bal_seg_value_id
2096 OR fset2.bal_seg_value_id = -1 )
2097 AND fin.chart_of_accounts_id = fset2.chart_of_accounts_id
2098 GROUP BY line.effective_date,
2099 fin.company_id,
2100 fin.cost_center_id,
2101 fin.natural_account_id,
2102 NVL(fin.prod_category_id, -1),
2103 fin.user_dim1_id,
2104 fin.user_dim2_id,
2105 v1.je_source,
2106 v1.je_category,
2107 fset2.set_of_books_id,
2108 fset2.chart_accs_id_sob,
2109 fset2.currency_code,
2110 decode(g_industry,
2111 'G', decode(v1.actual_flag,--for Government
2112 'A', null, -- for actuals
2113 v1.posted_date), -- for encumbrances
2114 null), -- for Commercial
2115 v1.encumbrance_type,
2116 v1.actual_flag;
2117
2118 -- Bug 4545509: Per performance team, we need to commit before we call
2119 -- gather stats so that stats will be gathered at 10% vs 99%.
2120 commit;
2121
2122 --Call FND_STATS to collect statistics after populating the table
2123 g_phase := 'Calling FND_STATS to collect statistics for FII_GL_JE_SUMMARY_STG';
2124 FND_STATS.gather_table_stats
2125 (ownname => g_fii_schema,
2126 tabname => 'FII_GL_JE_SUMMARY_STG');
2127
2128 g_phase := 'Enabling parallel dml';
2129 execute immediate 'alter session enable parallel dml';
2130
2131 if g_debug_flag = 'Y' then
2132 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_je_summary_stg');
2133 fii_util.stop_timer;
2134 fii_util.print_timer('Duration');
2135 end if;
2136
2137 EXCEPTION
2138 WHEN OTHERS Then
2139 g_retcode := -1;
2140 FII_UTIL.put_line('
2141 ----------------------------
2142 Error in Function: INSERT_INTO_STG
2143 Phase: ' || g_phase || '
2144 Message: '||sqlerrm);
2145 raise;
2146
2147 END INSERT_INTO_STG;
2148
2149 -------------------------------------------
2150 -- PROCEDURE Roll_Up
2151 -------------------------------------------
2152
2153 PROCEDURE ROLL_UP (p_sort_area_size IN NUMBER,
2154 p_hash_area_size IN NUMBER) IS
2155
2156 l_stmt VARCHAR2(1000);
2157
2158 BEGIN
2159
2160 g_phase := 'Calling alter session set sort_area_size';
2161 l_stmt := 'alter session set sort_area_size= ' ||p_sort_area_size;
2162 execute immediate l_stmt;
2163
2164 g_phase := 'Calling alter session set hash_area_size';
2165 l_stmt := 'alter session set hash_area_size= ' ||p_hash_area_size;
2166 execute immediate l_stmt;
2167
2168 if g_debug_flag = 'Y' then
2169 fii_util.put_line(' ');
2170 fii_util.put_line('Rolling up data in staging table');
2171 fii_util.start_timer;
2172 fii_util.put_line('');
2173 end if;
2174
2175 g_phase := 'Inserting into fii_gl_je_summary_b';
2176 INSERT /*+ append parallel(fii_gl_je_summary_b) */ INTO fii_gl_je_summary_b
2177 (time_id,
2178 period_type_id,
2179 cost_center_id,
2180 fin_category_id,
2181 company_id,
2182 prod_category_id,
2183 user_dim1_id,
2184 user_dim2_id,
2185 je_source,
2186 je_category,
2187 -- effective_date,
2188 ledger_id,
2189 chart_of_accounts_id,
2190 functional_currency,
2191 amount_b,
2192 prim_amount_g,
2193 sec_amount_g,
2194 committed_amount_b,
2195 committed_amount_prim,
2196 obligated_amount_b,
2197 obligated_amount_prim,
2198 other_amount_b,
2199 other_amount_prim,
2200 posted_date,
2201 last_update_date,
2202 last_updated_by,
2203 creation_date,
2204 created_by,
2205 last_update_login)
2206 SELECT /*+ parallel(bsum) parallel(fday) use_hash(fday,stg) */
2207 fday.week_id,
2208 16,
2209 bsum.cost_center_id,
2210 bsum.fin_category_id,
2211 bsum.company_id,
2212 bsum.prod_category_id,
2213 bsum.user_dim1_id,
2214 bsum.user_dim2_id,
2215 bsum.je_source,
2216 bsum.je_category,
2217 -- MAX(stg.effective_date),
2218 bsum.ledger_id,
2219 bsum.chart_of_accounts_id,
2220 bsum.functional_currency,
2221 SUM(bsum.amount_b) amount_b,
2222 SUM(bsum.prim_amount_g) prim_amount_g,
2223 SUM(bsum.sec_amount_g) sec_amount_g,
2224 SUM(bsum.committed_amount_b) committed_amount_b,
2225 SUM(bsum.committed_amount_prim) committed_amount_prim,
2226 SUM(bsum.obligated_amount_b) obligated_amount_b,
2227 SUM(bsum.obligated_amount_prim) obligated_amount_prim,
2228 SUM(bsum.other_amount_b) other_amount_b,
2229 SUM(bsum.other_amount_prim) other_amount_prim,
2230 bsum.posted_date,
2231 bsum.last_update_date,
2232 bsum.last_updated_by,
2233 bsum.creation_date,
2234 bsum.created_by,
2235 bsum.last_update_login
2236 FROM fii_gl_je_summary_b bsum,
2237 fii_time_day fday
2238 WHERE bsum.time_id = fday.report_date_julian
2239 GROUP BY
2240 bsum.cost_center_id,
2241 bsum.fin_category_id,
2242 bsum.company_id,
2243 bsum.prod_category_id,
2244 bsum.user_dim1_id,
2245 bsum.user_dim2_id,
2246 bsum.je_source,
2247 bsum.je_category,
2248 -- stg.effective_date,
2249 bsum.ledger_id,
2250 bsum.chart_of_accounts_id,
2251 bsum.functional_currency,
2252 bsum.last_update_date,
2253 bsum.last_updated_by,
2254 bsum.creation_date,
2255 bsum.created_by,
2256 bsum.last_update_login,
2257 fday.week_id,
2258 bsum.posted_date ;
2259
2260 if g_debug_flag = 'Y' then
2261 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2262 fii_util.stop_timer;
2263 fii_util.print_timer('Duration');
2264 end if;
2265
2266 commit;
2267
2268 EXCEPTION
2269 WHEN OTHERS Then
2270 g_retcode := -1;
2271 FII_UTIL.put_line('
2272 ----------------------------
2273 Error in Function: Roll_up
2274 Phase: ' || g_phase || '
2275 Message: '||sqlerrm);
2276 raise;
2277
2278 END ROLL_UP;
2279
2280 -------------------------------------------
2281 -- PROCEDURE Roll_Up2
2282 -------------------------------------------
2283
2284 PROCEDURE ROLL_UP2 (p_sort_area_size IN NUMBER,
2285 p_hash_area_size IN NUMBER)
2286 IS
2287
2288 l_stmt VARCHAR2(1000);
2289
2290 BEGIN
2291
2292 g_phase := 'Calling alter session set sort_area_size';
2293 l_stmt := 'alter session set sort_area_size= ' ||p_sort_area_size;
2294 execute immediate l_stmt;
2295
2296 g_phase := 'Calling alter session set hash_area_size';
2297 l_stmt := 'alter session set hash_area_size= ' ||p_hash_area_size;
2298 execute immediate l_stmt;
2299
2300 if g_debug_flag = 'Y' then
2301 fii_util.put_line(' ');
2302 fii_util.put_line('Rolling up data in staging table');
2303 fii_util.start_timer;
2304 fii_util.put_line('');
2305 end if;
2306
2307 --Bug 3121847: removed delete in ROLL_UP2 by filtering it out during insert
2308
2309 g_phase := 'Inserting into fii_gl_je_summary_b';
2310 INSERT /*+ append parallel(fii_gl_je_summary_b) */ INTO fii_gl_je_summary_b
2311 (time_id,
2312 period_type_id,
2313 cost_center_id,
2314 fin_category_id,
2315 company_id,
2316 prod_category_id,
2317 user_dim1_id,
2318 user_dim2_id,
2319 je_source,
2320 je_category,
2321 -- effective_date,
2322 ledger_id,
2323 chart_of_accounts_id,
2324 functional_currency,
2325 amount_b,
2326 prim_amount_g,
2327 sec_amount_g,
2328 committed_amount_b,
2329 committed_amount_prim,
2330 obligated_amount_b,
2331 obligated_amount_prim,
2332 other_amount_b,
2333 other_amount_prim,
2334 posted_date,
2335 last_update_date,
2336 last_updated_by,
2337 creation_date,
2338 created_by,
2339 last_update_login)
2340 Select * From (
2341 SELECT /*+ parallel(bsum) parallel(fday) use_hash(fday,stg) */
2342 NVL(fday.ent_period_id, NVL(fday.ent_qtr_id, fday.ent_year_id)) time_id,
2343 DECODE(fday.ent_period_id, NULL, DECODE(fday.ent_qtr_id, NULL, 128, 64), 32) period_type_id,
2344 bsum.cost_center_id,
2345 bsum.fin_category_id,
2346 bsum.company_id,
2347 bsum.prod_category_id,
2348 bsum.user_dim1_id,
2349 bsum.user_dim2_id,
2350 bsum.je_source,
2351 bsum.je_category,
2352 bsum.ledger_id,
2353 bsum.chart_of_accounts_id,
2354 bsum.functional_currency,
2355 SUM(bsum.amount_b) amount_b,
2356 SUM(bsum.prim_amount_g) prim_amount_g,
2357 SUM(bsum.sec_amount_g) sec_amount_g,
2358 SUM(bsum.committed_amount_b) committed_amount_b,
2359 SUM(bsum.committed_amount_prim) committed_amount_prim,
2360 SUM(bsum.obligated_amount_b) obligated_amount_b,
2361 SUM(bsum.obligated_amount_prim) obligated_amount_prim,
2362 SUM(bsum.other_amount_b) other_amount_b,
2363 SUM(bsum.other_amount_prim) other_amount_prim,
2364 bsum.posted_date,
2365 bsum.last_update_date,
2366 bsum.last_updated_by,
2367 bsum.creation_date,
2368 bsum.created_by,
2369 bsum.last_update_login
2370 FROM fii_gl_je_summary_b bsum,
2371 fii_time_day fday
2372 WHERE bsum.time_id = fday.report_date_julian
2373 GROUP BY
2374 bsum.cost_center_id,
2375 bsum.fin_category_id,
2376 bsum.company_id,
2377 bsum.prod_category_id,
2378 bsum.user_dim1_id,
2379 bsum.user_dim2_id,
2380 bsum.je_source,
2381 bsum.je_category,
2382 bsum.ledger_id,
2383 bsum.chart_of_accounts_id,
2384 bsum.functional_currency,
2385 bsum.posted_date,
2386 bsum.last_update_date,
2387 bsum.last_updated_by,
2388 bsum.creation_date,
2389 bsum.created_by,
2390 bsum.last_update_login,
2391 ROLLUP (fday.ent_year_id,
2392 fday.ent_qtr_id,
2393 fday.ent_period_id))
2394 where time_id is not null;
2395
2396 if g_debug_flag = 'Y' then
2397 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2398 fii_util.stop_timer;
2399 fii_util.print_timer('Duration');
2400 end if;
2401
2402 commit;
2403
2404 -------
2405 --Removed delete: DELETE FROM FII_GL_JE_SUMMARY_B WHERE time_id IS NULL;
2406 -------
2407
2408 EXCEPTION
2409 WHEN OTHERS Then
2410 g_retcode := -1;
2411 FII_UTIL.put_line('
2412 ----------------------------
2413 Error in Function: Roll_up2
2414 Phase: ' || g_phase || '
2415 Message: '||sqlerrm);
2416 raise;
2417
2418 END ROLL_UP2;
2419
2420 ----------------------------------------
2421 -- PROCEDURE Insert_Into_Rates
2422 ----------------------------------------
2423
2424 PROCEDURE INSERT_INTO_RATES IS
2425
2426 l_global_prim_curr_code VARCHAR2(30);
2427 l_global_sec_curr_code VARCHAR2(30);
2428
2429 BEGIN
2430
2431 g_phase := 'Calling bis_common_parameters.get_currency_code';
2432
2433 l_global_prim_curr_code := bis_common_parameters.get_currency_code;
2434 l_global_sec_curr_code := bis_common_parameters.get_secondary_currency_code;
2435
2436 if g_debug_flag = 'Y' then
2437 fii_util.put_line(' ');
2438 fii_util.put_line('Loading data into rates table');
2439 fii_util.start_timer;
2440 fii_util.put_line('');
2441 end if;
2442
2443 g_phase := 'Inserting into fii_gl_revenue_rates_temp';
2444 insert into fii_gl_revenue_rates_temp
2445 (FUNCTIONAL_CURRENCY,
2446 TRX_DATE,
2447 PRIM_CONVERSION_RATE,
2448 SEC_CONVERSION_RATE)
2449 select cc functional_currency,
2450 dt trx_date,
2451 decode(cc, l_global_prim_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_PRIMARY (cc,least(sysdate, dt))) PRIM_CONVERSION_RATE,
2452 decode(cc, l_global_sec_curr_code, 1, FII_CURRENCY.GET_GLOBAL_RATE_SECONDARY(cc,least(sysdate, dt))) SEC_CONVERSION_RATE
2453 from (
2454 select /*+ no_merge parallel(FII_gl_je_summary_STG)*/ distinct
2455 FUNCTIONAL_CURRENCY cc,
2456 effective_date dt
2457 from FII_gl_je_summary_STG
2458 );
2459
2460
2461 --Call FND_STATS to collect statistics after populating the table
2462 g_phase := 'Calling FND_STATS to collect statistics for fii_gl_revenue_rates_temp';
2463 FND_STATS.gather_table_stats
2464 (ownname => g_fii_schema,
2465 tabname => 'FII_GL_REVENUE_RATES_TEMP');
2466
2467 g_phase := 'Enabling parallel dml';
2468 execute immediate 'alter session enable parallel dml';
2469
2470 if g_debug_flag = 'Y' then
2471 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_revenue_rates_temp');
2472 fii_util.stop_timer;
2473 fii_util.print_timer('Duration');
2474 end if;
2475
2476 EXCEPTION
2477 WHEN OTHERS Then
2478 g_retcode := -1;
2479 FII_UTIL.put_line('
2480 ----------------------------
2481 Error in Function: Insert_Into_Rates
2482 Phase: ' || g_phase || '
2483 Message: '||sqlerrm);
2484 raise;
2485
2486 END INSERT_INTO_RATES;
2487
2488 ----------------------------------------
2489 -- PROCEDURE Insert_Into_Summary
2490 -----------------------------------------
2491
2492 PROCEDURE INSERT_INTO_SUMMARY IS
2493
2494 l_stmt VARCHAR2(1000);
2495
2496 BEGIN
2497
2498 if g_debug_flag = 'Y' then
2499 fii_util.put_line(' ');
2500 fii_util.put_line('Loading data into base summary table');
2501 fii_util.start_timer;
2502 fii_util.put_line('');
2503 end if;
2504
2505 --Bug 3121847: changed the second hint per performance team suggestion
2506
2507 insert /*+ append parallel(bsum) */ INTO fii_gl_je_summary_b bsum
2508 (bsum.time_id,
2509 bsum.period_type_id,
2510 bsum.company_id,
2511 bsum.cost_center_id,
2512 bsum.fin_category_id,
2513 bsum.prod_category_id,
2514 bsum.user_dim1_id,
2515 bsum.user_dim2_id,
2516 bsum.je_source,
2517 bsum.je_category,
2518 bsum.ledger_id,
2519 bsum.chart_of_accounts_id,
2520 bsum.functional_currency,
2521 bsum.amount_B,
2522 bsum.prim_amount_G,
2523 bsum.sec_amount_G,
2524 bsum.committed_amount_b,
2525 bsum.committed_amount_prim,
2526 bsum.obligated_amount_b,
2527 bsum.obligated_amount_prim,
2528 bsum.other_amount_b,
2529 bsum.other_amount_prim,
2530 bsum.posted_date,
2531 bsum.creation_date,
2532 bsum.created_by,
2533 bsum.last_update_date,
2534 bsum.last_update_login,
2535 bsum.last_updated_by)
2536 SELECT /*+ leading(r) use_hash(stg) parallel(stg) parallel(r) */
2537 stg.day,
2538 1,
2539 stg.company_id,
2540 stg.cost_center_id,
2541 stg.fin_category_id,
2542 stg.prod_category_id,
2543 stg.user_dim1_id,
2544 stg.user_dim2_id,
2545 stg.je_source,
2546 stg.je_category,
2547 stg.ledger_id,
2548 stg.chart_of_accounts_id,
2549 stg.functional_currency,
2550 sum(stg.amount_B),
2551 sum(round((stg.amount_B * r.prim_conversion_rate)/g_primary_mau) * g_primary_mau),
2552 sum(round((stg.amount_B * r.sec_conversion_rate) /g_secondary_mau)*g_secondary_mau),
2553 sum(stg.committed_amount_B),
2554 sum(round((stg.committed_amount_B * r.prim_conversion_rate)/g_primary_mau) * g_primary_mau),
2555 sum(stg.obligated_amount_B),
2556 sum(round((stg.obligated_amount_B * r.prim_conversion_rate)/g_primary_mau) * g_primary_mau),
2557 sum(stg.other_amount_B),
2558 sum(round((stg.other_amount_B * r.prim_conversion_rate)/g_primary_mau) * g_primary_mau),
2559 stg.posted_date,
2560 stg.creation_date,
2561 stg.created_by,
2562 stg.last_update_date,
2563 stg.last_update_login,
2564 stg.last_updated_by
2565 FROM FII_GL_JE_SUMMARY_STG stg, fii_gl_revenue_rates_temp r
2566 where stg.year IS NOT NULL
2567 AND stg.effective_date = r.trx_date
2568 AND stg.functional_currency = r.functional_currency
2569 GROUP BY stg.day,
2570 stg.cost_center_id,
2571 stg.company_id,
2572 stg.fin_category_id,
2573 stg.prod_category_id,
2574 stg.user_dim1_id,
2575 stg.user_dim2_id,
2576 stg.je_source,
2577 stg.je_category,
2578 stg.ledger_id,
2579 stg.chart_of_accounts_id,
2580 stg.functional_currency,
2581 stg.posted_date,
2582 stg.creation_date,
2583 stg.created_by,
2584 stg.last_update_date,
2585 stg.last_update_login,
2586 stg.last_updated_by;
2587 -------------------------------------------------------------
2588 -- Year field is NULL only for those extra sum records
2589 -- created by the rollup function
2590 -------------------------------------------------------------
2591
2592 --Fix bug 3561245
2593 commit;
2594
2595 --Call FND_STATS to collect statistics after populating the table
2596 g_phase := 'Calling FND_STATS to collect statistics for FII_GL_JE_SUMMARY_B';
2597 FND_STATS.gather_table_stats
2598 (ownname => g_fii_schema,
2599 tabname => 'FII_GL_JE_SUMMARY_B');
2600
2601 g_phase := 'Enabling parallel dml';
2602 execute immediate 'alter session enable parallel dml';
2603
2604 if g_debug_flag = 'Y' then
2605 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2606 fii_util.stop_timer;
2607 fii_util.print_timer('Duration');
2608 end if;
2609
2610 commit;
2611
2612 EXCEPTION
2613 WHEN OTHERS Then
2614 g_retcode := -1;
2615 FII_UTIL.put_line('
2616 ----------------------------
2617 Error in Function: Insert_Into_Summary
2618 Phase: ' || g_phase || '
2619 Message: '||sqlerrm);
2620 raise;
2621
2622 END INSERT_INTO_SUMMARY;
2623
2624
2625 -------------------------------------------
2626 -- PROCEDURE INSERT_CARRYFWD_BASE
2627 -------------------------------------------
2628 PROCEDURE INSERT_CARRYFWD_BASE IS
2629 l_sqlstmt VARCHAR2(5000);
2630 BEGIN
2631
2632 IF g_debug_flag = 'Y' THEN
2633 fii_util.put_line(' ');
2634 fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_f...');
2635 fii_util.start_timer;
2636 fii_util.put_line(' ');
2637 END IF;
2638
2639 g_phase := 'Inserting encumbrance carry forward into fii_gl_enc_carryfwd_f';
2640
2641 l_sqlstmt :=
2642 'INSERT /*+ append parallel(bsum) */ INTO fii_gl_enc_carryfwd_f bsum '||
2643 ' (bsum.time_id, bsum.period_type_id, bsum.company_id, '||
2644 ' bsum.cost_center_id, bsum.fin_category_id, bsum.prod_category_id, '||
2645 ' bsum.user_dim1_id, bsum.user_dim2_id, bsum.je_source, '||
2646 ' bsum.je_category, bsum.ledger_id, bsum.chart_of_accounts_id, '||
2647 ' bsum.functional_currency, '||
2648 ' bsum.committed_amount_b, bsum.committed_amount_prim, '||
2649 ' bsum.obligated_amount_b, bsum.obligated_amount_prim, '||
2650 ' bsum.other_amount_b, bsum.other_amount_prim, '||
2651 ' bsum.posted_date, bsum.creation_date, bsum.created_by, '||
2652 ' bsum.last_update_date, bsum.last_update_login, '||
2653 ' bsum.last_updated_by) '||
2654 ' SELECT '||
2655 ' NVL(stg.day, NVL(stg.period, NVL(stg.quarter, stg.year))), '||
2656 ' DECODE(stg.day, null, '||
2657 ' DECODE(stg.period, null, '||
2658 ' DECODE(stg.quarter, null, 128, 64), 32), 1), '||
2659 ' stg.company_id, '||
2660 ' stg.cost_center_id, stg.fin_category_id, stg.prod_category_id, '||
2661 ' stg.user_dim1_id, stg.user_dim2_id, stg.je_source, '||
2662 ' stg.je_category, stg.ledger_id, stg.chart_of_accounts_id, '||
2663 ' stg.functional_currency, '||
2664 ' stg.committed_amount_B, stg.committed_amount_B, '||
2665 ' stg.obligated_amount_B, stg.obligated_amount_B, '||
2666 ' stg.other_amount_B, stg.other_amount_B, '||
2667 ' stg.posted_date, stg.creation_date, stg.created_by, '||
2668 ' stg.last_update_date, stg.last_update_login, '||
2669 ' stg.last_updated_by '||
2670 ' FROM FII_GL_ENC_CARRYFWD_T stg '||
2671 ' WHERE stg.functional_currency = :global_prim_curr '||
2672 ' AND stg.year IS NOT NULL ';
2673
2674 -- Print out the dynamic SQL statements if running in debug mode
2675 IF g_debug_flag = 'Y' THEN
2676 fii_util.put_line('l_sqlstmt = '|| l_sqlstmt);
2677
2678 FII_MESSAGE.Write_Log
2679 (msg_name => 'FII_ROUTINE_VAL',
2680 token_num => 3 ,
2681 t1 => 'ROUTINE',
2682 v1 => 'FII_GL_JE_B_C.INSERT_CARRYFWD_BASE()',
2683 t2 => 'VARIABLE',
2684 v2 => 'LENGTH(l_sqlstmt)',
2685 t3 => 'VALUE',
2686 v3 => TO_CHAR(LENGTH(l_sqlstmt)));
2687 END IF;
2688
2689 EXECUTE IMMEDIATE l_sqlstmt USING g_prim_currency;
2690
2691 IF g_debug_flag = 'Y' THEN
2692 fii_util.put_line('Inserted '||SQL%ROWCOUNT||
2693 ' rows into FII_GL_ENC_CARRYFWD_F');
2694 fii_util.put_line('');
2695 fii_util.stop_timer;
2696 fii_util.print_timer('Duration');
2697 END IF;
2698
2699 COMMIT;
2700
2701 EXCEPTION
2702 WHEN OTHERS Then
2703 g_retcode := -1;
2704 FII_UTIL.put_line('
2705 ----------------------------
2706 Error in Function: INSERT_CARRYFWD_BASE
2707 Phase: ' || g_phase || '
2708 Message: '||sqlerrm);
2709 raise;
2710
2711 END INSERT_CARRYFWD_BASE;
2712
2713 -------------------------------------------
2714 -- PROCEDURE MERGE_CARRYFWD_BASE
2715 -------------------------------------------
2716 PROCEDURE MERGE_CARRYFWD_BASE IS
2717 l_sqlstmt VARCHAR2(5000);
2718 l_stg_sql VARCHAR2(1000);
2719 l_base_sql VARCHAR2(1000);
2720 BEGIN
2721
2722 ---------------------------------------------------------------------------
2723 -- Delete data from fii_gl_enc_carryfwd_f if time/dimension exists in base
2724 -- table is no longer included in the current run.
2725 ---------------------------------------------------------------------------
2726 IF g_debug_flag = 'Y' THEN
2727 fii_util.put_line(' ');
2728 fii_util.put_line('Delete data from fact table if time/dimension no longer exists in the currency run...');
2729 fii_util.start_timer;
2730 fii_util.put_line('');
2731 END IF;
2732
2733 g_phase := 'Delete carryforward data from fact not included in currency run';
2734
2735 l_sqlstmt :=
2736 ' DELETE '||
2737 ' FROM fii_gl_enc_carryfwd_f '||
2738 ' WHERE (time_id, company_id, cost_center_id, fin_category_id, '||
2739 ' prod_category_id, user_dim1_id, user_dim2_id, '||
2740 ' committed_amount_b, obligated_amount_b, other_amount_b) '||
2741 ' NOT IN '||
2742 ' (SELECT '||
2743 ' NVL(stg.day, NVL(stg.period, NVL(stg.quarter, stg.year))), '||
2744 ' company_id, cost_center_id, fin_category_id, '||
2745 ' prod_category_id, user_dim1_id, user_dim2_id, '||
2746 ' committed_amount_b, obligated_amount_b, other_amount_b '||
2747 ' FROM fii_gl_enc_carryfwd_t stg '||
2748 ' WHERE functional_currency = :global_primary) ';
2749
2750 -- Print out the dynamic SQL statements if running in debug mode
2751 IF g_debug_flag = 'Y' THEN
2752 fii_util.put_line('l_sqlstmt = '|| l_sqlstmt);
2753
2754 FII_MESSAGE.Write_Log
2755 (msg_name => 'FII_ROUTINE_VAL',
2756 token_num => 3 ,
2757 t1 => 'ROUTINE',
2758 v1 => 'FII_GL_JE_B_C.INSERT_CARRYFWD_BASE()',
2759 t2 => 'VARIABLE',
2760 v2 => 'LENGTH(l_sqlstmt)',
2761 t3 => 'VALUE',
2762 v3 => TO_CHAR(LENGTH(l_sqlstmt)));
2763 END IF;
2764
2765 EXECUTE IMMEDIATE l_sqlstmt USING g_prim_currency;
2766
2767 IF g_debug_flag = 'Y' THEN
2768 fii_util.put_line('Deleted '||SQL%ROWCOUNT||
2769 ' rows from FII_GL_ENC_CARRYFWD_F');
2770 fii_util.stop_timer;
2771 fii_util.print_timer('Duration');
2772 END IF;
2773
2774 ---------------------------------------------------------------------------
2775 -- Insert new data from fii_gl_enc_carryfwd_t into fii_gl_enc_carryfwd_f
2776 ---------------------------------------------------------------------------
2777 IF g_debug_flag = 'Y' THEN
2778 fii_util.put_line(' ');
2779 fii_util.put_line('Insert new data into fii_gl_enc_carryfwd_f...');
2780 fii_util.start_timer;
2781 fii_util.put_line('');
2782 END IF;
2783
2784 g_phase := 'Insert new data into fii_gl_enc_carryfwd_f';
2785
2786 -- Sql to delete data from fact table if time/dimension no longer
2787 -- exists in the currency run
2788 l_sqlstmt :=
2789 ' INSERT INTO fii_gl_enc_carryfwd_f '||
2790 ' (time_id, period_type_id, company_id, cost_center_id, '||
2791 ' fin_category_id, prod_category_id, user_dim1_id, '||
2792 ' user_dim2_id, je_source, je_category, '||
2793 ' ledger_id, chart_of_accounts_id, functional_currency, '||
2794 ' committed_amount_b, committed_amount_prim, '||
2795 ' obligated_amount_b, obligated_amount_prim, '||
2796 ' other_amount_b, other_amount_prim, posted_date, '||
2797 ' creation_date, created_by, last_update_date, '||
2798 ' last_update_login, last_updated_by) '||
2799 ' SELECT '||
2800 ' NVL(day, NVL(period, NVL(quarter, year))), '||
2801 ' DECODE(day, null, '||
2802 ' DECODE(period, null, '||
2803 ' DECODE(quarter, null, 128, 64), 32), 1), '||
2804 ' company_id, cost_center_id, fin_category_id, '||
2805 ' prod_category_id, user_dim1_id, user_dim2_id, '||
2806 ' je_source, je_category, ledger_id, '||
2807 ' chart_of_accounts_id, functional_currency, '||
2808 ' committed_amount_B, committed_amount_B, '||
2809 ' obligated_amount_B, obligated_amount_B, '||
2810 ' other_amount_B, other_amount_B, posted_date, '||
2811 ' creation_date, created_by, last_update_date, '||
2812 ' last_update_login, last_updated_by '||
2813 ' FROM FII_GL_ENC_CARRYFWD_T '||
2814 ' WHERE functional_currency = :global_primary '||
2815 ' AND year IS NOT NULL '||
2816 ' AND (NVL(day, NVL(period, NVL(quarter, year))), '||
2817 ' company_id, cost_center_id, fin_category_id, '||
2818 ' prod_category_id, user_dim1_id, user_dim2_id, '||
2819 ' committed_amount_B, obligated_amount_b, other_amount_b) '||
2820 ' NOT IN '||
2821 ' (SELECT '||
2822 ' time_id, company_id, cost_center_id, fin_category_id, '||
2823 ' prod_category_id, user_dim1_id, user_dim2_id, '||
2824 ' committed_amount_b, obligated_amount_b, other_amount_b '||
2825 ' FROM fii_gl_enc_carryfwd_f) ';
2826
2827 -- Print out the dynamic SQL statements if running in debug mode
2828 IF g_debug_flag = 'Y' THEN
2829 fii_util.put_line('l_sqlstmt = '|| l_sqlstmt);
2830
2831 FII_MESSAGE.Write_Log
2832 (msg_name => 'FII_ROUTINE_VAL',
2833 token_num => 3 ,
2834 t1 => 'ROUTINE',
2835 v1 => 'FII_GL_JE_B_C.INSERT_CARRYFWD_BASE()',
2836 t2 => 'VARIABLE',
2837 v2 => 'LENGTH(l_sqlstmt)',
2838 t3 => 'VALUE',
2839 v3 => TO_CHAR(LENGTH(l_sqlstmt)));
2840 END IF;
2841
2842 EXECUTE IMMEDIATE l_sqlstmt USING g_prim_currency;
2843
2844 IF g_debug_flag = 'Y' THEN
2845 fii_util.put_line('Inserted '||SQL%ROWCOUNT||
2846 ' rows into FII_GL_ENC_CARRYFWD_F');
2847 fii_util.stop_timer;
2848 fii_util.print_timer('Duration');
2849 END IF;
2850
2851 EXCEPTION
2852 WHEN OTHERS Then
2853 g_retcode := -1;
2854 FII_UTIL.put_line('
2855 ----------------------------
2856 Error in Function: MERGE_CARRYFWD_BASE
2857 Phase: ' || g_phase || '
2858 Message: '||sqlerrm);
2859 raise;
2860
2861 END MERGE_CARRYFWD_BASE;
2862
2863 -------------------------------------------
2864 -- PROCEDURE INSERT_ENC_CARRYFWD
2865 -------------------------------------------
2866 PROCEDURE INSERT_ENC_CARRYFWD (l_ret_code IN OUT NOCOPY VARCHAR2,
2867 l_program_type IN VARCHAR2,
2868 l_start_date IN DATE,
2869 l_end_date IN DATE) IS
2870
2871 l_sqlstmt VARCHAR2(5000);
2872 l_tmpstmt VARCHAR2(5000);
2873 l_sob_name VARCHAR2(30);
2874 l_currency_code VARCHAR2(15);
2875 l_print_hdr1 BOOLEAN := FALSE;
2876 l_obtype_id NUMBER;
2877 l_comtype_id NUMBER;
2878
2879 -- Cursor for checking if we have encumrbance amounts not in
2880 -- global primary currency
2881 CURSOR fcurrCursor (global_prim_curr VARCHAR2) IS
2882 SELECT DISTINCT sob.name, t.functional_currency
2883 FROM fii_gl_enc_carryfwd_t t,
2884 gl_ledgers_public_v sob
2885 WHERE t.functional_currency NOT IN (global_prim_curr)
2886 AND t.ledger_id = sob.ledger_id;
2887
2888 BEGIN
2889
2890 ---------------------------------------------------------------------------
2891 -- Truncate fii_gl_enc_carrfywd_t
2892 ---------------------------------------------------------------------------
2893 IF g_debug_flag = 'Y' THEN
2894 fii_util.put_line(' ');
2895 fii_util.put_line('Truncate fii_gl_enc_carryfwd_t...');
2896 END IF;
2897 TRUNCATE_TABLE('FII_GL_ENC_CARRYFWD_T');
2898
2899 ---------------------------------------------------------------------------
2900 -- If initial load, truncate fii_gl_enc_carrfywd_f as well
2901 ---------------------------------------------------------------------------
2902 IF (l_program_type = 'L') THEN
2903 IF g_debug_flag = 'Y' THEN
2904 fii_util.put_line(' ');
2905 fii_util.put_line('Truncate fii_gl_enc_carryfwd_f...');
2906 END IF;
2907 TRUNCATE_TABLE('FII_GL_ENC_CARRYFWD_F');
2908 END IF;
2909
2910 ---------------------------------------------------------------------------
2911 -- Insert encumbrance carry forward amounts into staging table
2912 ---------------------------------------------------------------------------
2913 g_phase := 'Inserting encumbrance carry forward into fii_gl_enc_carryfwd_t';
2914 IF g_debug_flag = 'Y' THEN
2915 fii_util.put_line(' ');
2916 fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_t...');
2917 fii_util.start_timer;
2918 fii_util.put_line('');
2919 END IF;
2920
2921 -- Find out the encumbrance type ID for the seeded encumbrance types
2922 SELECT encumbrance_type_id
2923 INTO l_obtype_id
2924 FROM gl_encumbrance_types
2925 WHERE encumbrance_type = 'Obligation';
2926
2927 SELECT encumbrance_type_id
2928 INTO l_comtype_id
2929 FROM gl_encumbrance_types
2930 WHERE encumbrance_type = 'Commitment';
2931
2932 l_tmpstmt :=
2933 'INSERT /*+ append parallel(fii_gl_enc_carryfwd_t) */ '||
2934 ' INTO fii_gl_enc_carryfwd_t '||
2935 ' (day, period, quarter, year, '||
2936 ' company_id, cost_center_id, fin_category_id, '||
2937 ' prod_category_id, user_dim1_id, user_dim2_id, '||
2938 ' je_source, je_category, '||
2939 ' ledger_id, chart_of_accounts_id, '||
2940 ' functional_currency, '||
2941 ' committed_amount_b, obligated_amount_b, '||
2942 ' other_amount_b, posted_date, last_update_date, '||
2943 ' last_updated_by, creation_date, created_by, last_update_login) '||
2944 'SELECT day, to_number(NULL, 999), to_number(NULL, 999), 999, '||
2945 ' company_id, cost_center_id, natural_account_id, '||
2946 ' prod_category_id, user_dim1_id, user_dim2_id, '||
2947 ' ''Manual'', ''Carry Forward'', '||
2948 ' ledger_id, chart_of_accounts_id, '||
2949 ' currency_code, '||
2950 ' sum(committed_amount_b) committed_amount_b, '||
2951 ' sum(obligated_amount_b) obligated_amount_b, '||
2952 ' sum(other_amount_b) other_amount_b, year_start_date, sysdate, '||
2953 ' :user_id, sysdate, :user_id, :login_id '||
2954 ' FROM ( '||
2955 'SELECT /*+ parallel(per) parallel(sob) pq_distribute(sob hash,hash) '||
2956 ' pq_distribute(fset hash,hash) parallel(b) '||
2957 ' use_hash(fin,slga2,fslg2) parallel(fin) parallel(slga2) '||
2958 ' parallel(fslg2) pq_distribute(slga2 hash,hash) '||
2959 ' pq_distribute(fslg2 hash,hash) '||
2960 ' pq_distribute(fin hash,hash) */ '||
2961 ' to_char(per.start_date, ''J'') day, '||
2962 ' fin.company_id, fin.cost_center_id, fin.natural_account_id, '||
2963 ' NVL(fin.prod_category_id, -1) prod_category_id, '||
2964 ' fin.user_dim1_id, fin.user_dim2_id, '||
2965 ' sob.ledger_id, sob.chart_of_accounts_id, '||
2966 ' sob.currency_code, '||
2967 ' decode( '||
2968 ' b.encumbrance_type_id, '||
2969 ' :comtype_id, '||
2970 ' NVL(b.begin_balance_cr, 0) - NVL(b.begin_balance_dr, 0), '||
2971 ' 0) committed_amount_b, '||
2972 ' decode( '||
2973 ' b.encumbrance_type_id, '||
2974 ' :obtype_id, '||
2975 ' NVL(b.begin_balance_cr, 0) - NVL(b.begin_balance_dr, 0), '||
2976 ' 0) obligated_amount_b, '||
2977 ' decode( '||
2978 ' b.encumbrance_type_id, '||
2979 ' :comtype_id, 0, '||
2980 ' :obtype_id, 0, '||
2981 ' NVL(b.begin_balance_cr, 0) - NVL(b.begin_balance_dr, 0)) '||
2982 ' other_amount_b, '||
2983 ' per.year_start_date '||
2984 'FROM gl_balances b, '||
2985 ' gl_ledgers_public_v sob, '||
2986 ' gl_periods per, '||
2987 ' (SELECT /*+ full(slga) */ DISTINCT slga.ledger_id '||
2988 ' FROM fii_slg_assignments slga, '||
2989 ' fii_source_ledger_groups fslg '||
2990 ' WHERE slga.source_ledger_group_id =fslg.source_ledger_group_id '||
2991 ' AND fslg.usage_code = ''DBI'') fset, '||
2992 ' fii_gl_ccid_dimensions fin, '||
2993 ' fii_slg_assignments slga2, '||
2994 ' fii_source_ledger_groups fslg2 '||
2995 ' WHERE sob.ledger_id = fset.ledger_id ';
2996
2997 IF (l_program_type = 'L') THEN
2998 l_sqlstmt := l_tmpstmt ||
2999 ' AND per.start_date <= :end_date '||
3000 ' AND per.end_date >= :start_date ';
3001 ELSE
3002 l_sqlstmt := l_tmpstmt ||
3003 ' AND per.end_date >= :start_date ';
3004 END IF;
3005
3006 l_sqlstmt := l_sqlstmt ||
3007 ' AND per.period_set_name = sob.period_set_name '||
3008 ' AND per.period_type = sob.accounted_period_type '||
3009 ' AND per.period_num = 1 '||
3010 ' AND b.period_name = per.period_name '||
3011 ' AND b.ledger_id = sob.ledger_id '||
3012 ' AND b.currency_code <> ''STAT'' '||
3013 ' AND b.actual_flag = ''E'' '||
3014 ' AND (b.begin_balance_dr <> 0 or b.begin_balance_cr <> 0) '||
3015 ' AND fin.code_combination_id = b.code_combination_id '||
3016 ' AND fslg2.usage_code = ''DBI'' '||
3017 ' AND slga2.source_ledger_group_id = fslg2.source_ledger_group_id '||
3018 ' AND ( fin.company_id = slga2.bal_seg_value_id '||
3019 ' OR slga2.bal_seg_value_id = -1 ) '||
3020 ' AND fin.chart_of_accounts_id = slga2.chart_of_accounts_id )'||
3021 ' GROUP BY '||
3022 ' day, company_id, cost_center_id, natural_account_id, '||
3023 ' prod_category_id, user_dim1_id, user_dim2_id, ledger_id, '||
3024 ' chart_of_accounts_id, currency_code, year_start_date ';
3025
3026 -- Print out the dynamic SQL statements if running in debug mode
3027 IF g_debug_flag = 'Y' THEN
3028 fii_util.put_line('l_sqlstmt = '|| l_sqlstmt);
3029
3030 FII_MESSAGE.Write_Log
3031 (msg_name => 'FII_ROUTINE_VAL',
3032 token_num => 3 ,
3033 t1 => 'ROUTINE',
3034 v1 => 'FII_GL_JE_B_C.INSERT_ENC_CARRYFWD()',
3035 t2 => 'VARIABLE',
3036 v2 => 'LENGTH(l_sqlstmt)',
3037 t3 => 'VALUE',
3038 v3 => TO_CHAR(LENGTH(l_sqlstmt)));
3039 END IF;
3040
3041 IF (l_program_type = 'L') THEN
3042 EXECUTE IMMEDIATE l_sqlstmt
3043 USING g_fii_user_id, g_fii_user_id, g_fii_login_id,
3044 l_comtype_id, l_obtype_id, l_comtype_id, l_obtype_id,
3045 l_end_date, l_start_date;
3046 ELSE
3047 EXECUTE IMMEDIATE l_sqlstmt
3048 USING g_fii_user_id, g_fii_user_id, g_fii_login_id,
3049 l_comtype_id, l_obtype_id, l_comtype_id, l_obtype_id,
3050 l_start_date;
3051 END IF;
3052
3053 IF g_debug_flag = 'Y' THEN
3054 fii_util.put_line('Inserted '||SQL%ROWCOUNT||
3055 ' rows into FII_GL_ENC_CARRYFWD_T');
3056 fii_util.stop_timer;
3057 fii_util.print_timer('Duration');
3058 END IF;
3059
3060 ---------------------------------------------------------------------------
3061 -- Needs to commit before reading from table after inserting in parallel
3062 ---------------------------------------------------------------------------
3063 COMMIT;
3064
3065 ---------------------------------------------------------------------------
3066 -- Validate currencies used in encumbrance carry forward
3067 ---------------------------------------------------------------------------
3068 g_phase := 'Validate currencies used in encumbrance carry forward amounts';
3069
3070 IF g_debug_flag = 'Y' THEN
3071 fii_util.put_line(' ');
3072 fii_util.put_line('Validate currencies used in encumbrance carry forward');
3073 fii_util.put_line('');
3074 END IF;
3075
3076 l_print_hdr1 := FALSE;
3077
3078 FOR rec_csr IN fcurrCursor(g_prim_currency) LOOP
3079 l_sob_name := rec_csr.name;
3080 l_currency_code := rec_csr.functional_currency;
3081
3082 IF (NOT l_print_hdr1) THEN
3083 -- Set the return code so the program will ends with warning.
3084 l_ret_code := 'W';
3085
3086 FII_UTIL.Write_Output (' ');
3087 FII_MESSAGE.Write_Log (msg_name => 'FII_INV_ENC_CURR_CODE',
3088 token_num => 0);
3089 FII_MESSAGE.Write_Log (msg_name => 'FII_REFER_TO_OUTPUT',
3090 token_num => 0);
3091 FII_UTIL.put_line('');
3092 FII_MESSAGE.Write_Output (msg_name => 'FII_INV_ENC_CURR_CODE',
3093 token_num => 0);
3094 l_print_hdr1 := TRUE;
3095 END IF;
3096
3097 FII_UTIL.Write_Output (l_sob_name || ' (' || l_currency_code || ')');
3098 END LOOP;
3099
3100
3101 ---------------------------------------------------------------------------
3102 -- Rollup daily slices into monthly/quarterly/yearly slices
3103 -- in fii_gl_enc_carryfwd_t
3104 --
3105 -- Since we will only include records where functional_currency =
3106 -- global primary for this release, we will only rollup data that satisfy
3107 -- this criteria.
3108 ---------------------------------------------------------------------------
3109 g_phase := 'Roll up encumbrance carry forward data into month/qtr/yr slices';
3110
3111 IF g_debug_flag = 'Y' THEN
3112 fii_util.put_line(' ');
3113 fii_util.put_line(
3114 'Rollup encumbrance carry forward data into month/quarter/year slices...');
3115 fii_util.put_line('');
3116 fii_util.start_timer;
3117 fii_util.put_line('');
3118 END IF;
3119
3120 l_sqlstmt :=
3121 ' INSERT /*+ append parallel(fii_gl_enc_carryfwd_t) */ '||
3122 ' INTO fii_gl_enc_carryfwd_t '||
3123 ' (period, quarter, year, '||
3124 ' company_id, cost_center_id, fin_category_id, '||
3125 ' prod_category_id, user_dim1_id, user_dim2_id, '||
3126 ' je_source, je_category, '||
3127 ' ledger_id, chart_of_accounts_id, functional_currency, '||
3128 ' committed_amount_b, obligated_amount_b, other_amount_b, '||
3129 ' posted_date, last_update_date, '||
3130 ' last_updated_by, creation_date, created_by, last_update_login) '||
3131 ' SELECT * FROM ( '||
3132 ' SELECT /*+ parallel(t) parallel(fday) use_hash(fday,t) */ '||
3133 ' fday.ent_period_id, fday.ent_qtr_id, fday.ent_year_id, '||
3134 ' t.company_id, t.cost_center_id, t.fin_category_id, '||
3135 ' t.prod_category_id, t.user_dim1_id, t.user_dim2_id, '||
3136 ' t.je_source, t.je_category, '||
3137 ' t.ledger_id, t.chart_of_accounts_id, t.functional_currency, '||
3138 ' SUM(t.committed_amount_b) committed_amount_b, '||
3139 ' SUM(t.obligated_amount_b) obligated_amount_b, '||
3140 ' SUM(t.other_amount_b) other_amount_b, '||
3141 ' t.posted_date, t.last_update_date, '||
3142 ' t.last_updated_by, t.creation_date, t.created_by, '||
3143 ' t.last_update_login '||
3144 ' FROM fii_gl_enc_carryfwd_t t, '||
3145 ' fii_time_day fday '||
3146 ' WHERE t.day = fday.report_date_julian '||
3147 ' AND t.functional_currency = :global_primary '||
3148 ' GROUP BY t.company_id, t.cost_center_id, t.fin_category_id, '||
3149 ' t.prod_category_id, t.user_dim1_id, t.user_dim2_id, '||
3150 ' t.je_source, t.je_category, t.ledger_id, '||
3151 ' t.chart_of_accounts_id, t.functional_currency, '||
3152 ' t.posted_date, t.last_update_date, t.last_updated_by, '||
3153 ' t.creation_date, t.created_by, t.last_update_login, '||
3154 ' ROLLUP (fday.ent_year_id, '||
3155 ' fday.ent_qtr_id, '||
3156 ' fday.ent_period_id)) '||
3157 ' WHERE ent_year_id IS NOT NULL ';
3158
3159 -- Print out the dynamic SQL statements if running in debug mode
3160 IF g_debug_flag = 'Y' THEN
3161 fii_util.put_line('l_sqlstmt = '|| l_sqlstmt);
3162
3163 FII_MESSAGE.Write_Log
3164 (msg_name => 'FII_ROUTINE_VAL',
3165 token_num => 3 ,
3166 t1 => 'ROUTINE',
3167 v1 => 'FII_GL_JE_B_C.INSERT_ENC_CARRYFWD()',
3168 t2 => 'VARIABLE',
3169 v2 => 'LENGTH(l_sqlstmt)',
3170 t3 => 'VALUE',
3171 v3 => TO_CHAR(LENGTH(l_sqlstmt)));
3172 END IF;
3173
3174 EXECUTE IMMEDIATE l_sqlstmt USING g_prim_currency;
3175
3176 IF g_debug_flag = 'Y' THEN
3177 fii_util.put_line('Inserted '|| SQL%ROWCOUNT||
3178 ' rows into FII_GL_ENC_CARRYFWD_T');
3179 fii_util.stop_timer;
3180 fii_util.print_timer('Duration');
3181 END IF;
3182
3183 ---------------------------------------------------------------------------
3184 -- Needs to commit before reading from table after inserting in parallel
3185 ---------------------------------------------------------------------------
3186 COMMIT;
3187
3188 ---------------------------------------------------------------------------
3189 -- If initial load, insert carryforward data into fii_gl_enc_carryfwd_f
3190 -- If incremental load,
3191 -- 1. Delete carryforward data not included this run from base
3192 -- 2. Merge carryforward data into fii_gl_enc_carryfwd_f
3193 ---------------------------------------------------------------------------
3194 IF (l_program_type = 'L') THEN
3195 INSERT_CARRYFWD_BASE;
3196 ELSE
3197 MERGE_CARRYFWD_BASE;
3198 END IF;
3199
3200 EXCEPTION
3201 WHEN OTHERS Then
3202 g_retcode := -1;
3203 FII_UTIL.put_line('
3204 ----------------------------
3205 Error in Function: INSERT_ENC_CARRYFWD
3206 Phase: ' || g_phase || '
3207 Message: '||sqlerrm);
3208 raise;
3209
3210 END INSERT_ENC_CARRYFWD;
3211
3212
3213 -- ------------------------------------------------------------
3214 -- Public Functions and Procedures
3215 -- ------------------------------------------------------------
3216
3217 PROCEDURE Main (errbuf IN OUT NOCOPY VARCHAR2,
3218 retcode IN OUT NOCOPY VARCHAR2,
3219 p_start_date IN VARCHAR2,
3220 p_end_date IN VARCHAR2,
3221 p_number_of_process IN NUMBER,
3222 p_program_type IN VARCHAR2,
3223 p_parallel_query IN NUMBER,
3224 p_sort_area_size IN NUMBER,
3225 p_hash_area_size IN NUMBER) IS
3226
3227 return_status BOOLEAN := TRUE;
3228 l_start_date DATE := NULL;
3229 l_end_date DATE :=NULL;
3230 l_period_start_date DATE := NULL;
3231 l_period_end_date DATE := NULL;
3232 p_number_of_rows NUMBER :=0;
3233 p_no_worker NUMBER :=1;
3234 l_conversion_count NUMBER :=0;
3235 l_retcode VARCHAR2(3);
3236 l_errbuf VARCHAR2(500);
3237 l_stmt VARCHAR2(300);
3238 l_dir VARCHAR2(400);
3239 l_ids_count NUMBER:= 0;
3240 stg_count NUMBER:= 0;
3241 l_truncate_stg BOOLEAN := FALSE;
3242
3243 -- Declaring local variables to initialize the dates for the
3244 -- incremental mode
3245 l_last_start_date DATE;
3246 l_last_end_date DATE;
3247 l_last_period_from DATE;
3248 l_last_period_to DATE;
3249 l_lud_hours NUMBER := to_number(NULL);
3250 l_global_start_date DATE;
3251
3252 l_global_param_list dbms_sql.varchar2_table;
3253
3254 TYPE WorkerList is table of NUMBER
3255 index by binary_integer;
3256 l_worker WorkerList;
3257
3258 l_slg_chg VARCHAR2(10);
3259 l_prd_chg VARCHAR2(10);
3260
3261 l_ret_val BOOLEAN;
3262 l_ret_code VARCHAR2(1) := 'N'; -- Default to 'N' for Normal
3263
3264 BEGIN
3265 errbuf := NULL;
3266 retcode := 0;
3267
3268 g_program_type := p_program_type;
3269 -----------------------------------------------
3270 -- Do the necessary setups for logging and
3271 -- output
3272 -----------------------------------------------
3273 l_dir := FII_UTIL.get_utl_file_dir;
3274
3275 ------------------------------------------------
3276 -- Initialize API will fetch the FII_DEBUG_MODE
3277 -- profile option and intialize g_debug variable
3278 -- accordingly. It will also read in profile
3279 -- option BIS_DEBUG_LOG_DIRECTORY to find out
3280 -- the log directory
3281 ------------------------------------------------
3282 g_phase := 'Calling FII_UTIL.initialize';
3283 IF g_program_type = 'I' THEN
3284 FII_UTIL.initialize('FII_GL_SUM.log','FII_GL_SUM.out',l_dir, 'FII_GL_JE_B_C');
3285 ELSIF g_program_type = 'L' THEN
3286 FII_UTIL.initialize('FII_GL_SUM.log','FII_GL_SUM.out',l_dir, 'FII_GL_JE_B_L');
3287 END IF;
3288
3289 -----------------------------------------------------
3290 -- Calling BIS API to do common set ups
3291 -- If it returns false, then program should error out
3292 -----------------------------------------------------
3293 g_phase := 'Calling BIS API to do common set ups';
3294 l_global_param_list(1) := 'BIS_GLOBAL_START_DATE';
3295 l_global_param_list(2) := 'BIS_PRIMARY_CURRENCY_CODE';
3296 l_global_param_list(3) := 'BIS_PRIMARY_RATE_TYPE';
3297 IF (NOT bis_common_parameters.check_global_parameters(l_global_param_list)) THEN
3298 FII_MESSAGE.write_log( msg_name => 'FII_BAD_GLOBAL_PARA',
3299 token_num => 0);
3300 FII_MESSAGE.write_output(msg_name => 'FII_BAD_GLOBAL_PARA',
3301 token_num => 0);
3302
3303 l_ret_val := FND_CONCURRENT.Set_Completion_Status(
3304 status => 'ERROR',
3305 message => 'One of the three global parameters: Global Start Date; Primary Currency Code; Primary Rate Type has not been set up.'
3306 );
3307
3308 return;
3309 ELSIF p_program_type = 'I' THEN
3310 IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_GL_JE_B_C')) THEN
3311 raise_application_error(-20000,errbuf);
3312 return;
3313 END IF;
3314 ELSIF p_program_type = 'L' THEN
3315 IF (NOT BIS_COLLECTION_UTILITIES.setup('FII_GL_JE_B_L')) THEN
3316 raise_application_error(-20000,errbuf);
3317 return;
3318 END IF;
3319 END IF;
3320
3321 ------------------------------------------------
3322 -- Initialize other setups
3323 ------------------------------------------------
3324 g_phase := 'Calling INIT';
3325 INIT();
3326
3327 ------------------------------------------------
3328 -- If running in Initial Load mode, truncate
3329 -- everything before starts.
3330 ------------------------------------------------
3331 IF p_program_type = 'L' THEN
3332 IF g_debug_flag = 'Y' then
3333 FII_UTIL.put_line('Running in Initial Load mode, truncate STG, summary and other processing tables.');
3334 END IF;
3335 TRUNCATE_TABLE('FII_GL_JE_SUMMARY_STG');
3336 TRUNCATE_TABLE('FII_GL_JE_SUMMARY_B');
3337 TRUNCATE_TABLE('FII_GL_PROCESSED_HEADER_IDS');
3338
3339 COMMIT;
3340 END IF;
3341
3342 ------------------------------------------
3343 -- Check setups only if we are running in
3344 -- Incremental Mode, p_program_type = 'I'
3345 ------------------------------------------
3346 IF (p_program_type = 'I') THEN
3347 ---------------------------------------------
3348 -- Check if any set up got changed. If yes,
3349 -- then we need to truncate the summary table
3350 -- and then reload (also see bug 3401590)
3351 ---------------------------------------------
3352 g_phase := 'Check setups if we are running in Incremental Mode';
3353
3354 l_slg_chg := CHECK_IF_SLG_SET_UP_CHANGE;
3355 l_prd_chg := CHECK_IF_PRD_SET_UP_CHANGE;
3356
3357 IF (l_slg_chg = 'TRUE') THEN
3358 FII_MESSAGE.write_output (msg_name => 'FII_TRUNC_SUMMARY', token_num => 0);
3359 FII_MESSAGE.write_log (msg_name => 'FII_TRUNC_SUMMARY', token_num => 0);
3360 ----FII_UTIL.put_line('Source Ledger Group setup has changed. Please run the Request Set in the Initial mode to repopulate the summaries.');
3361 END IF;
3362
3363 IF (l_prd_chg = 'TRUE') THEN
3364 FII_MESSAGE.write_output (msg_name => 'FII_TRUNC_SUMMARY_PRD', token_num => 0);
3365 FII_MESSAGE.write_log (msg_name => 'FII_TRUNC_SUMMARY_PRD', token_num => 0);
3366 ----FII_UTIL.put_line('Product Assignment has changed. Please run the Request Set in the Initial mode to repopulate the summaries.');
3367 END IF;
3368
3369 -- should fail the program if either slg or prd changed
3370 IF l_slg_chg = 'TRUE' OR l_prd_chg = 'TRUE' THEN
3371 retcode := -1;
3372 RETURN;
3373 END IF;
3374
3375 ELSIF (p_program_type = 'L') THEN
3376 ---------------------------------------------
3377 -- If running in Inital Load, then update
3378 -- change log to indicate that resummarization
3379 -- is not necessary since everything is
3380 -- going to be freshly loaded
3381 ---------------------------------------------
3382 g_phase := 'Update fii_change_log if we are running in Inital Load';
3383
3384 UPDATE fii_change_log
3385 SET item_value = 'N',
3386 last_update_date = SYSDATE,
3387 last_update_login = g_fii_login_id,
3388 last_updated_by = g_fii_user_id
3389 WHERE log_item = 'GL_RESUMMARIZE'
3390 AND item_value = 'Y';
3391
3392 UPDATE fii_change_log
3393 SET item_value = 'N',
3394 last_update_date = SYSDATE,
3395 last_update_login = g_fii_login_id,
3396 last_updated_by = g_fii_user_id
3397 WHERE log_item = 'GL_PROD_CHANGE'
3398 AND item_value = 'Y';
3399
3400 COMMIT;
3401
3402 END IF;
3403
3404 -------------------------------------------------
3405 -- Print out useful date range information
3406 -------------------------------------------------
3407 g_phase := 'Get date range information';
3408
3409 IF p_program_type = 'L' THEN
3410
3411 -------------------------------------------------------------
3412 -- When running in Initial mode, the default values of the
3413 -- parameters are defined in the concurrent program seed data
3414 -------------------------------------------------------------
3415 l_start_date := TO_DATE(p_start_date, 'YYYY/MM/DD HH24:MI:SS');
3416 l_end_date := TO_DATE(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
3417
3418 ELSE
3419
3420 -----------------------------------------------------------------
3421 -- When running in Incremental mode, the values of the parameters
3422 -- are derived in the program
3423 -----------------------------------------------------------------
3424 BIS_COLLECTION_UTILITIES.get_last_refresh_dates('FII_GL_JE_B_C',
3425 l_last_start_date,
3426 l_last_end_date,
3427 l_last_period_from,
3428 l_last_period_to);
3429
3430 IF l_last_start_date IS NULL THEN
3431 l_start_date := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
3432 ELSE
3433 -----------------------------------------------------------------------
3434 -- Bug fix 3021099: In this sql to find the earliest period we need to
3435 -- process for incremental, we will only look at periods that is on or
3436 -- after global start date. Thus we will not be processing extra periods
3437 -- which does not have data for us to process.
3438 -----------------------------------------------------------------------
3439 l_global_start_date := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
3440
3441 SELECT trunc(min(stu.start_date))
3442 INTO l_start_date
3443 FROM gl_period_statuses stu,
3444 fii_slg_assignments slga,
3445 fii_source_ledger_groups fslg
3446 WHERE slga.ledger_id = stu.set_of_books_id
3447 AND stu.application_id = 101
3448 AND (stu.closing_status = 'O' OR (stu.closing_status IN ('C', 'P')
3449 AND stu.last_update_date > l_last_start_date))
3450 AND stu.start_date >= l_global_start_date
3451 AND slga.source_ledger_group_id = fslg.source_ledger_group_id
3452 AND fslg.usage_code = g_usage_code;
3453 END IF;
3454
3455 l_end_date := to_date(NULL);
3456
3457 END IF;
3458
3459 if g_debug_flag = 'Y' then
3460 FII_UTIL.put_line('User submitted start date range: ' || l_start_date);
3461 FII_UTIL.put_line('User submitted end date range: ' || l_end_date);
3462 end if;
3463
3464 l_period_start_date := l_start_date;
3465 l_period_end_date := l_end_date;
3466
3467 if g_debug_flag = 'Y' then
3468 FII_UTIL.put_line('Collection Period start date: ' || l_period_start_date);
3469 FII_UTIL.put_line('Collection Period end date: ' || l_period_end_date);
3470 end if;
3471
3472 ----------------------------------------------------------
3473 -- Determine if we need to resume. If there are records
3474 -- in staging table, then that means there are records
3475 -- with missing exchange rate information left from the
3476 -- previous run. In this case, we will not process any
3477 -- more new records, we will only process records already
3478 -- in the staging table
3479 ----------------------------------------------------------
3480 g_phase := 'Determine if we need to resume';
3481 if g_debug_flag = 'Y' then
3482 FII_UTIL.put_line(g_phase);
3483 end if;
3484
3485 SELECT COUNT(*)
3486 INTO stg_count
3487 FROM fii_gl_je_summary_stg;
3488
3489 IF (stg_count > 0) THEN
3490 g_resume_flag := 'Y';
3491 ELSE
3492 g_resume_flag := 'N';
3493 END IF;
3494
3495 -----------------------------------------------------------------
3496 -- If resume flag is 'N', then this program starts from the
3497 -- beginning:
3498 -- 1. Identify GL Header IDs to process
3499 -- 2. Submit child process to insert day-level summarized
3500 -- records into temporary staging table
3501 -- Otherwise, it would first check if all missing rates have been
3502 -- fixed, and then resume the normal process which includes:
3503 -- 3. Insert higher time level summarized records into
3504 -- temporary staging table.
3505 -- 4. Merging summarized records into base summary table
3506 -- 5. Insert processed Header IDs into a processed table
3507 ------------------------------------------------------------------
3508
3509 IF (g_resume_flag = 'N') THEN
3510 g_phase := 'g_resume_flag = N';
3511
3512 ----------------------------------------------------------
3513 -- This variable indicates that if exception occur, do
3514 -- we need to truncate the staging table.
3515 -- We are about to submit the child process which will
3516 -- insert records into staging table. If any exception
3517 -- occured during the child process run, the staging table
3518 -- should be truncated. After all child process are done
3519 -- inserting records into staging table, this flag will
3520 -- be set to FALSE.
3521 ----------------------------------------------------------
3522 g_truncate_stg := TRUE;
3523
3524 ----------------------------------------------------------
3525 -- This variable indicates that if exception occur, do
3526 -- we need to truncate the temporary ID table.
3527 -- We need to truncate this table if the program starts
3528 -- fresh at the beginning.
3529 -- We will reset this variable to FALSE after we have
3530 -- populate it. We will not truncate it until next time
3531 -- when the program starts fresh (non-resume). We want
3532 -- to preserve this table for debugging purpose.
3533 ----------------------------------------------------------
3534 g_truncate_id := TRUE;
3535
3536 --------------------------------------------------------------
3537 -- Calling CLEAN_UP procedure to clean up all processing
3538 -- tables
3539 --------------------------------------------------------------
3540 if g_debug_flag = 'Y' then
3541 FII_UTIL.put_line('');
3542 FII_UTIL.put_line('Cleaning up processing tables before actual processing start');
3543 FII_UTIL.put_line('------------------------------------------------------------');
3544 end if;
3545 CLEAN_UP;
3546
3547 if g_debug_flag = 'Y' then
3548 FII_UTIL.put_line('------------------------------------------------------------');
3549 FII_UTIL.put_line('');
3550 end if;
3551
3552 ---------------------------------------------------------
3553 -- After we do initial clean up, we will set this flag to
3554 -- FALSE to preserve the temporary Revenue ID table for
3555 -- debugging purpose
3556 ---------------------------------------------------------
3557 g_truncate_id := FALSE;
3558
3559 ---------------------------------------------------------------
3560 -- Call New_Journals routine to insert Journal header ids into
3561 -- FII_NEW_GL_HEADER_ID_TEMP
3562 ----------------------------------------------------------------
3563 g_phase := 'Identify New Journal Headers to process';
3564 if g_debug_flag = 'Y' then
3565 FII_UTIL.put_line(g_phase);
3566 end if;
3567
3568 --------------------------------------------------------
3569 -- NEW_JOURNALS will identify the new journals which
3570 -- need to be processed based on the user entered
3571 -- date range. If there are no new journals to process
3572 -- the program will exit immediately with complete
3573 -- successful status
3574 --------------------------------------------------------
3575 l_ids_count := NEW_JOURNALS(l_period_start_date, l_period_end_date);
3576
3577 IF (l_ids_count = 0) THEN
3578 if g_debug_flag = 'Y' then
3579 FII_UTIL.put_line('No Journal Entries to Process, exit.');
3580 end if;
3581 RETURN;
3582 END IF;
3583
3584 ----------------------------------------------------------------
3585 -- After the new journals are identified, we need to call the
3586 -- CCID API to make sure that the CCID dimension is up to date.
3587 -- The reason we call this API after we have identified the
3588 -- new journals instead of calling this API at the beginning of
3589 -- the programs is because that it is possible that after we
3590 -- called the API, new CCIDs are created by new journals, and
3591 -- then we will pull this new journal in the New_Journals API
3592 -- and subsequently treat this new journal as processed even
3593 -- though it is not processed because its corresponding CCID
3594 -- is missing in the CCID dimension.
3595 -- If CCID dimension is not up to date, VERIFY_CCID_UP_TO_DATE
3596 -- will also call the CCID Dimension load program to update
3597 -- CCID dimension.
3598 ----------------------------------------------------------------
3599 g_phase := 'Verifying if CCID Dimension is up to date';
3600 if g_debug_flag = 'Y' then
3601 FII_UTIL.put_line(g_phase);
3602 end if;
3603
3604 VERIFY_CCID_UP_TO_DATE;
3605
3606 IF (g_industry = 'G') THEN
3607 g_phase := 'Populate encumbrance type mapping table';
3608 if g_debug_flag = 'Y' then
3609 FII_UTIL.put_line(g_phase);
3610 end if;
3611
3612 POPULATE_ENCUM_MAPPING;
3613 END IF;
3614
3615 IF (p_program_type = 'L') THEN
3616 INSERT_INTO_STG(p_sort_area_size, p_hash_area_size,l_start_date, l_end_date);
3617 INSERT_INTO_RATES;
3618
3619
3620 ELSE
3621
3622 ----------------------------------------------------------------
3623 -- Register jobs in the table FII_GL_WORKER_JOBS for launching
3624 -- child processes.
3625 ----------------------------------------------------------------
3626 g_phase := 'Calling Routine Register_Jobs';
3627
3628 if g_debug_flag = 'Y' then
3629 FII_UTIL.put_line(g_phase);
3630 end if;
3631
3632 Register_Jobs();
3633
3634 COMMIT;
3635
3636 ----------------------------------------------------------------
3637 -- Launching child processes.
3638 ----------------------------------------------------------------
3639 g_phase := 'Launching child process...';
3640 p_no_worker := p_number_of_process;
3641
3642 -- Launch child process
3643
3644 FOR i IN 1..p_no_worker
3645 LOOP
3646 -- p_no_worker is the parameter user submitted to specify how many
3647 -- workers they want to submit
3648 l_worker(i) := LAUNCH_WORKER(i);
3649 COMMIT;
3650
3651 if g_debug_flag = 'Y' then
3652 FII_util.put_line(' Worker '||i||' request id: '||l_worker(i));
3653 end if;
3654 END LOOP;
3655
3656 -- Monitor Child process after launching them
3657
3658 DECLARE
3659 l_unassigned_cnt NUMBER := 0;
3660 l_completed_cnt NUMBER := 0;
3661 l_wip_cnt NUMBER := 0;
3662 l_failed_cnt NUMBER := 0;
3663 l_tot_cnt NUMBER := 0;
3664 l_last_unassigned_cnt NUMBER := 0;
3665 l_last_completed_cnt NUMBER := 0;
3666 l_last_wip_cnt NUMBER := 0;
3667 l_cycle NUMBER := 0;
3668
3669 BEGIN
3670 g_phase := 'Waiting for child process to complete';
3671 LOOP
3672 SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
3673 NVL(sum(decode(status,'COMPLETED',1,0)),0),
3674 NVL(sum(decode(status,'IN PROCESS',1,0)),0),
3675 NVL(sum(decode(status,'FAILED',1,0)),0),
3676 count(*)
3677 INTO l_unassigned_cnt,
3678 l_completed_cnt,
3679 l_wip_cnt,
3680 l_failed_cnt,
3681 l_tot_cnt
3682 FROM FII_GL_WORKER_JOBS;
3683
3684 if g_debug_flag = 'Y' then
3685 FII_UTIL.put_line('Job status - Unassigned:'||l_unassigned_cnt||
3686 ' In Process:'||l_wip_cnt||
3687 ' Completed:'||l_completed_cnt||
3688 ' Failed:'||l_failed_cnt);
3689 end if;
3690
3691 IF (l_failed_cnt > 0) THEN
3692 g_retcode := -1;
3693 FII_UTIL.put_line('
3694 ---------------------------------
3695 Error in Main Procedure:
3696 Message: At least one of the workers have errored out');
3697 RAISE G_CHILD_PROCESS_ISSUE;
3698 END IF;
3699
3700 -- --------------------------------------------
3701 -- IF the number of complete count equals to
3702 -- the total count, then that means all workers
3703 -- have completed. Then we can exit the loop
3704 -- --------------------------------------------
3705 IF (l_tot_cnt = l_completed_cnt) THEN
3706 if g_debug_flag = 'Y' then
3707 FII_UTIL.put_line ('All jobs have completed');
3708 end if;
3709 EXIT;
3710 END IF;
3711
3712 -- -----------------------
3713 -- Detect infinite loops
3714 -- -----------------------
3715 IF (l_unassigned_cnt = l_last_unassigned_cnt AND
3716 l_completed_cnt = l_last_completed_cnt AND
3717 l_wip_cnt = l_last_wip_cnt) THEN
3718
3719 l_cycle := l_cycle + 1;
3720 ELSE
3721 l_cycle := 1;
3722 END IF;
3723
3724 -- --------------------------------------
3725 -- MAX_LOOP is a global variable you set.
3726 -- It represents the number of minutes
3727 -- you want to wait for each worker to
3728 -- complete. We can set it to 30 minutes
3729 -- for now
3730 -- --------------------------------------
3731 IF (l_cycle > MAX_LOOP) THEN
3732 g_retcode := -1;
3733 FII_UTIL.put_line('
3734 ---------------------------------
3735 Error in Main Procedure:
3736 Message: No progress have been made for '||MAX_LOOP||' minutes.
3737 Terminating');
3738 RAISE G_CHILD_PROCESS_ISSUE;
3739 END IF;
3740
3741 -- -----------------------
3742 -- Sleep 60 Seconds
3743 -- -----------------------
3744 dbms_lock.sleep(60);
3745
3746 l_last_unassigned_cnt := l_unassigned_cnt;
3747 l_last_completed_cnt := l_completed_cnt;
3748 l_last_wip_cnt := l_wip_cnt;
3749 END LOOP;
3750 END; -- Monitor child process Ends here.
3751
3752 END IF;
3753 ----------------------------------------------------
3754 -- Else, running in resume mode
3755 ----------------------------------------------------
3756 ELSE
3757
3758 g_phase := 'g_resume_flag = Y';
3759
3760 -----------------------------------------------------------
3761 -- Setting g_truncate_stg to FALSE to make sure we don't
3762 -- truncate staging table when we are just fixing exchange
3763 -- rates in staging table
3764 -----------------------------------------------------------
3765 g_truncate_stg := FALSE;
3766
3767 g_phase := 'Fixing missing rates in temporary staging table';
3768 if g_debug_flag = 'Y' then
3769 FII_UTIL.put_line(g_phase);
3770
3771 FII_UTIL.start_timer;
3772 end if;
3773
3774 Update FII_GL_JE_SUMMARY_STG stg
3775 SET prim_conversion_rate =
3776 fii_currency.get_global_rate_primary(stg.functional_currency,least(sysdate, stg.effective_date))
3777 WHERE stg.prim_conversion_rate < 0;
3778
3779 if g_debug_flag = 'Y' then
3780 FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
3781 FII_UTIL.stop_timer;
3782 FII_UTIL.print_timer('Duration');
3783
3784 FII_UTIL.start_timer;
3785 end if;
3786
3787 commit; --use commit after print out correct SQL%ROWCOUNT
3788
3789 Update FII_GL_JE_SUMMARY_STG stg
3790 SET sec_conversion_rate =
3791 fii_currency.get_global_rate_secondary(stg.functional_currency,least(sysdate, stg.effective_date))
3792 WHERE stg.sec_conversion_rate < 0;
3793
3794 if g_debug_flag = 'Y' then
3795 FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for secondary currency rates in staging table');
3796 FII_UTIL.stop_timer;
3797 FII_UTIL.print_timer('Duration');
3798 end if;
3799
3800 commit; --use commit after print out correct SQL%ROWCOUNT
3801
3802 END IF; -- IF (g_resume_flag = 'N')
3803
3804
3805 -----------------------------------------------------------------
3806 -- If all the child process completes successfully then Invoke
3807 -- Summary_err_check routine to check for any missing rates record
3808 -- or missing time dimension record in the FII_GL_JE_SUMMARY_STG
3809 -- table.
3810 -----------------------------------------------------------------
3811 g_phase:= 'Summarization Error Check';
3812 if g_debug_flag = 'Y' then
3813 FII_UTIL.put_line(g_phase);
3814 end if;
3815
3816 Summary_err_check (p_program_type);
3817
3818 IF (g_missing_rates = 0 AND g_missing_time = 0) THEN
3819
3820 -------------------------------------------------------------
3821 -- Setting g_truncate_stg to TRUE because during the subsequent
3822 -- processes, if failure occurs, we should truncate staging
3823 ---------------------------------------------------------------
3824 g_truncate_stg := TRUE;
3825
3826 -------------------------------------------------------------
3827 -- Call Summarization_aggreagte routine to insert PTD,QTD and
3828 -- YTD into the FII_GL_JE_SUMMARY_STG table.
3829 -------------------------------------------------------------
3830 g_phase := 'Aggregating summarized data';
3831 if g_debug_flag = 'Y' then
3832 FII_UTIL.put_line('');
3833 FII_UTIL.put_line(g_phase);
3834 end if;
3835
3836 IF p_program_type = 'I' THEN
3837
3838 Summarize_aggregate;
3839
3840 --Bug 3356106: should summarize week level seperately
3841 Sum_Aggregate_Week; --this should be after Summarize_aggregate
3842
3843 ELSIF p_program_type = 'L' THEN
3844
3845 INSERT_INTO_SUMMARY;
3846
3847 END IF;
3848
3849 --------------------------------------------------------
3850 -- Call Merge routine to insert summarized data into
3851 -- FII_GL_JE_SUMMARY_B table.
3852 --------------------------------------------------------
3853 g_phase := 'Merging records into base summary table';
3854 if g_debug_flag = 'Y' then
3855 FII_UTIL.put_line('');
3856 FII_UTIL.put_line(g_phase);
3857 end if;
3858
3859 IF p_program_type = 'I' THEN
3860 Merge;
3861 ELSIF p_program_type = 'L' THEN
3862 ROLL_UP (p_sort_area_size, p_hash_area_size);
3863 ROLL_UP2 (p_sort_area_size, p_hash_area_size);
3864 END IF;
3865
3866 -----------------------------------------------------------------
3867 -- If Merge routine returns true then Insert processed rows into
3868 -- FII_GL_PROCESSED_HEADER_IDS table by calling the routine
3869 -- Jornals_processed.
3870 -----------------------------------------------------------------
3871 g_phase := 'Inserting processed JE Header IDs';
3872 if g_debug_flag = 'Y' then
3873 FII_UTIL.put_line('');
3874 FII_UTIL.put_line(g_phase);
3875 end if;
3876
3877 Journals_processed;
3878
3879 -----------------------------------------------------------------
3880 -- If industry = 'Governemnt'. We should gather the carryforward
3881 -- encumbrances to the new fiscal year from gl_balances
3882 -----------------------------------------------------------------
3883 IF (g_industry = 'G') THEN
3884 INSERT_ENC_CARRYFWD(l_ret_code, p_program_type,
3885 l_start_date, l_end_date);
3886
3887 END IF;
3888
3889 COMMIT;
3890
3891 ------------------------------------------------------------------
3892 -- Cleaning phase
3893 -- Truncate staging summary table if all the processes completed
3894 -- successfully.
3895 ------------------------------------------------------------------
3896 Clean_up;
3897
3898 ----------------------------------------------------------------
3899 -- Calling BIS API to record the range we collect. Only do this
3900 -- when we have a successful collection
3901 ----------------------------------------------------------------
3902 BIS_COLLECTION_UTILITIES.wrapup(p_status => TRUE,
3903 p_period_from => l_period_start_date,
3904 p_period_to => l_period_end_date);
3905
3906 IF (l_ret_code = 'N') THEN
3907 retcode := 0;
3908
3909 ELSIF (l_ret_code = 'W') THEN
3910 -- INSERT_ENC_CARRYFWD has a validation error.
3911 -- Program should completes with warnings.
3912 l_ret_val := FND_CONCURRENT.Set_Completion_Status
3913 (status => 'WARNING', message => NULL);
3914 END IF;
3915
3916 ELSE
3917
3918 retcode := g_retcode;
3919 errbuf := 'There is missing rate or missing time information';
3920
3921 END IF; --g_missing_rates = 0 AND g_missing_time = 0
3922
3923 Exception
3924 WHEN OTHERS Then
3925 g_retcode := -1;
3926 clean_up;
3927 FII_UTIL.put_line('
3928 Error in Function: Main
3929 Phase: '|| g_phase || '
3930 Message: ' || sqlerrm);
3931 retcode := g_retcode;
3932 END Main;
3933
3934 -- ************************************************************************
3935 -- PROCEDURE WORKER
3936 -- ************************************************************************
3937 PROCEDURE WORKER(Errbuf IN OUT NOCOPY VARCHAR2,
3938 Retcode IN OUT NOCOPY VARCHAR2,
3939 p_worker_no IN NUMBER) IS
3940
3941 -- Put any additional developer variables here
3942
3943 l_unassigned_cnt NUMBER := 0;
3944 l_failed_cnt NUMBER := 0;
3945 l_wip_cnt NUMBER := 0;
3946 l_completed_cnt NUMBER := 0;
3947 l_total_cnt NUMBER := 0;
3948 l_count NUMBER :=0;
3949 l_start_range NUMBER :=0;
3950 l_end_range NUMBER :=0;
3951
3952 BEGIN
3953
3954 Errbuf :=NULL;
3955 Retcode:=0;
3956
3957 -- -----------------------------------------------
3958 -- Set up directory structure for child process
3959 -- because child process do not call setup routine
3960 -- from EDWCORE
3961 -- -----------------------------------------------
3962 g_phase := 'Calling child_setup';
3963 CHILD_SETUP('FII_GL_SUM_SUBWORKER'||p_worker_no);
3964
3965 if g_debug_flag = 'Y' then
3966 FII_UTIL.put_line(' ');
3967 FII_UTIL.put_timestamp;
3968 FII_UTIL.put_line('Worker '||p_worker_no||' Starting');
3969 end if;
3970
3971 -- ------------------------------------------
3972 -- Loop thru job list
3973 -- -----------------------------------------
3974 g_phase := 'Loop thru job list';
3975 LOOP
3976 SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
3977 NVL(sum(decode(status,'FAILED', 1, 0)),0),
3978 NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
3979 NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
3980 count(*)
3981 INTO l_unassigned_cnt,
3982 l_failed_cnt,
3983 l_wip_cnt,
3984 l_completed_cnt,
3985 l_total_cnt
3986 FROM FII_GL_WORKER_JOBS;
3987
3988 if g_debug_flag = 'Y' then
3989 FII_UTIL.put_line('Job status - Unassigned: '||l_unassigned_cnt||
3990 ' In Process: '||l_wip_cnt||
3991 ' Completed: '||l_completed_cnt||
3992 ' Failed: '||l_failed_cnt||
3993 ' Total: '|| l_total_cnt);
3994 end if;
3995
3996 IF (l_failed_cnt > 0) THEN
3997 if g_debug_flag = 'Y' then
3998 FII_UTIL.put_line('');
3999 FII_UTIL.put_line('Another worker have errored out. Stop processing.');
4000 end if;
4001 EXIT;
4002 ELSIF (l_unassigned_cnt = 0) THEN
4003 if g_debug_flag = 'Y' then
4004 FII_UTIL.put_line('');
4005 FII_UTIL.put_line('No more jobs left. Terminating.');
4006 end if;
4007 EXIT;
4008 ELSIF (l_completed_cnt = l_total_cnt) THEN
4009 if g_debug_flag = 'Y' then
4010 FII_UTIL.put_line('');
4011 FII_UTIL.put_line('All jobs completed, no more job. Terminating');
4012 end if;
4013 EXIT;
4014 ELSIF (l_unassigned_cnt > 0) THEN
4015 UPDATE FII_GL_WORKER_JOBS
4016 SET status = 'IN PROCESS',
4017 worker_number = p_worker_no
4018 WHERE status = 'UNASSIGNED'
4019 AND rownum < 2;
4020 if g_debug_flag = 'Y' then
4021 FII_UTIL.put_line('Taking job from job queue');
4022 end if;
4023 l_count := sql%rowcount;
4024 COMMIT;
4025 END IF;
4026
4027 -- -----------------------------------
4028 -- There could be rare situations where
4029 -- between Section 30 and Section 50
4030 -- the unassigned job gets taken by
4031 -- another worker. So, if unassigned
4032 -- job no longer exist. Do nothing.
4033 -- -----------------------------------
4034 IF (l_count > 0) THEN
4035 DECLARE
4036 BEGIN
4037 g_phase := 'Getting ID range from FII_GL_WORKER_JOBS table';
4038 if g_debug_flag = 'Y' then
4039 FII_UTIL.put_line(g_phase);
4040 end if;
4041
4042 SELECT start_range,
4043 end_range
4044 INTO l_start_range,
4045 l_end_range
4046 FROM FII_GL_WORKER_JOBS
4047 WHERE worker_number = p_worker_no
4048 AND status = 'IN PROCESS';
4049
4050 --------------------------------------------------
4051 -- Do summarization using the start_range
4052 -- and end_range call the summarization routine
4053 -- Passing start range and end range parameters
4054 --------------------------------------------------
4055 g_phase := 'Inserting day level summarized records';
4056 if g_debug_flag = 'Y' then
4057 FII_UTIL.put_line(g_phase);
4058 end if;
4059
4060 Summarize_Day(l_start_range,
4061 l_end_range);
4062
4063 -----------------------------------------------------
4064 -- Do other work if necessary to finish the child
4065 -- process
4066 -- After completing the work, set the job status
4067 -- to complete
4068 -----------------------------------------------------
4069 g_phase:='Updating job status in FII_GL_WORKER_JOBS table';
4070 if g_debug_flag = 'Y' then
4071 FII_UTIL.put_line(g_phase);
4072 end if;
4073
4074 UPDATE FII_GL_WORKER_JOBS
4075 SET status = 'COMPLETED'
4076 WHERE status = 'IN PROCESS'
4077 AND worker_number = p_worker_no;
4078
4079 COMMIT;
4080
4081 -- Handle any exception that occured during
4082 -- your child process
4083
4084 EXCEPTION
4085 WHEN OTHERS THEN
4086 g_retcode := -1;
4087
4088 UPDATE FII_GL_WORKER_JOBS
4089 SET status = 'FAILED'
4090 WHERE worker_number = p_worker_no
4091 AND status = 'IN PROCESS';
4092
4093 COMMIT;
4094 Raise;
4095 END;
4096
4097 END IF; /* IF (l_count> 0) */
4098 END LOOP;
4099
4100 EXCEPTION
4101 WHEN OTHERS THEN
4102 retcode:= g_retcode;
4103 FII_UTIL.put_line('
4104 ---------------------------------
4105 Error in Procedure: WORKER
4106 Phase: '|| g_phase || '
4107 Message: '||sqlerrm);
4108 END WORKER;
4109
4110 END FII_GL_JE_B_C;