[Home] [Help]
PACKAGE BODY: APPS.PJI_UTILS
Source
1 PACKAGE BODY PJI_UTILS AS
2 /* $Header: PJIUT01B.pls 120.8.12000000.2 2007/07/25 11:01:46 bifernan ship $ */
3
4 -- Global variables -----------------------------------
5 g_pji_settings pji_system_settings%rowtype;
6 g_settings_init_flag boolean := FALSE;
7 g_pji_enabled varchar2(1);
8 g_apps_schema varchar2(30);
9 g_pa_schema varchar2(30);
10 g_pji_schema varchar2(30);
11 g_pji_data_tspace varchar2(30);
12 g_pji_index_tspace varchar2(30);
13 g_session_sid number;
14 g_session_osuser varchar2(30);
15 g_session_user_id number;
16 g_module varchar2(60);
17 g_output_dest varchar2(60);
18 g_debug_context varchar2(60);
19 g_pa_debug_mode varchar2(1);
20
21 -- Global variables for dangling rates report ---------
22 g_space varchar2(30) := ' ';
23 g_line varchar2(30) := '-------------------------';
24 g_indenting varchar2(10) := ' ';
25 g_length_rate_type number := 12;
26 g_length_from_currency number := 17;
27 g_length_to_currency number := 15;
28 g_length_date number := 20;
29
30 -- Private program units ------------------------------
31
32 procedure init_settings_cache;
33 procedure init_session_cache;
34
35 -- ------------------------------------------------------
36 -- function GET_PARAMETER
37 -- ------------------------------------------------------
38 function GET_PARAMETER (p_name varchar2) return varchar2 is
39
40 l_result varchar2(240);
41
42 begin
43
44 select VALUE
45 into l_result
46 from PJI_SYSTEM_PARAMETERS
47 where NAME = p_name;
48
49 return l_result;
50
51 exception
52 when no_data_found
53 then return null;
54 when others
55 then raise;
56
57 end GET_PARAMETER;
58
59
60 -- ------------------------------------------------------
61 -- procedure SET_PARAMETER
62 -- ------------------------------------------------------
63 procedure SET_PARAMETER(p_name varchar2, p_value varchar2) is
64
65 begin
66
67 update PJI_SYSTEM_PARAMETERS
68 set VALUE = p_value
69 where NAME = p_name;
70
71 if (sql%rowcount = 0) then
72 insert
73 into PJI_SYSTEM_PARAMETERS (NAME, VALUE)
74 values (p_name, p_value);
75 end if;
76
77 end SET_PARAMETER;
78
79
80 -- ------------------------------------------------------
81 -- function GET_APPS_SCHEMA_NAME
82 -- ------------------------------------------------------
83 function GET_APPS_SCHEMA_NAME return varchar2 is
84
85 begin
86
87 if (g_apps_schema is null) then
88
89 select ORACLE_USERNAME
90 into g_apps_schema
91 from FND_ORACLE_USERID
92 where ORACLE_ID = 900;
93
94 end if;
95
96 return g_apps_schema;
97
98 end GET_APPS_SCHEMA_NAME;
99
100
101 -- ------------------------------------------------------
102 -- function GET_PA_SCHEMA_NAME
103 --
104 -- History
105 -- 19-MAR-2004 SVERMETT Created
106 --
107 -- Internal PJP Summarization API.
108 --
109 -- ------------------------------------------------------
110 function GET_PA_SCHEMA_NAME return varchar2 is
111
112 l_status varchar2(30);
113 l_industry varchar2(30);
114 excp_get_app_info exception;
115
116 begin
117
118 if (g_pa_schema is null) then
119
120 if (not FND_INSTALLATION.GET_APP_INFO('PA', l_status, l_industry, g_pa_schema)) then
121 raise excp_get_app_info;
122 end if;
123
124 end if;
125
126 return g_pa_schema;
127
128 end GET_Pa_SCHEMA_NAME;
129
130
131 -- ------------------------------------------------------
132 -- function GET_PJI_SCHEMA_NAME
133 -- ------------------------------------------------------
134 function GET_PJI_SCHEMA_NAME return varchar2 is
135
136 l_status varchar2(30);
137 l_industry varchar2(30);
138 excp_get_app_info exception;
139
140 begin
141
142 if (g_pji_schema is null) then
143
144 if (not FND_INSTALLATION.GET_APP_INFO('PJI', l_status, l_industry, g_pji_schema)) then
145 raise excp_get_app_info;
146 end if;
147
148 end if;
149
150 return g_pji_schema;
151
152 end GET_PJI_SCHEMA_NAME;
153
154
155 -- ------------------------------------------------------
156 -- procedure SET_CURR_FUNCTION
157 -- ------------------------------------------------------
158 procedure SET_CURR_FUNCTION( p_function varchar2 ) is
159 begin
160 g_module := p_function;
161 pa_debug.set_curr_function( p_function );
162 end;
163
164
165 -- ------------------------------------------------------
166 -- procedure RESET_CURR_FUNCTION
167 -- ------------------------------------------------------
168 procedure RESET_CURR_FUNCTION is
169 begin
170 pa_debug.reset_curr_function;
171 end;
172
173
174 -- ------------------------------------------------------
175 -- function GET_EXTRACTION_START_DATE
176 -- ------------------------------------------------------
177 function GET_EXTRACTION_START_DATE return date is
178
179 l_global_start_date date;
180 l_override varchar2(30);
181 l_override_date date;
182 l_error_msg varchar2(255):= 'Please ensure that the PJI_GLOBAL_START_DATE_OVERRIDE and BIS_GLOBAL_START_DATE profile options are entered in the following format: MM/DD/YYYY';
183
184 begin
185
186 l_global_start_date :=
187 to_date(FND_PROFILE.VALUE('BIS_GLOBAL_START_DATE'),
188 'MM/DD/YYYY');
189 l_override := FND_PROFILE.VALUE('PJI_GLOBAL_START_DATE_OVERRIDE');
190
191 if (l_override is not null) then
192
193 begin
194
195 l_override_date := to_date(l_override, 'MM/DD/YYYY');
196
197 exception when others then
198 dbms_standard.raise_application_error(-20050, l_error_msg);
199
200 end;
201
202 return greatest(l_global_start_date, l_override_date);
203
204 end if;
205
206 return l_global_start_date;
207
208 end GET_EXTRACTION_START_DATE;
209
210
211 -- ------------------------------------------------------
212 -- procedure SET_OUTPUT_DEST
213 -- ------------------------------------------------------
214 procedure SET_OUTPUT_DEST
215 (
216 p_debug_dest varchar2,
217 p_debug_context varchar2 default NULL
218 ) is
219 begin
220 g_output_dest := p_debug_dest;
221 g_debug_context := p_debug_context;
222 end;
223
224
225 -- ------------------------------------------------------
226 -- procedure WRITE2LOG
227 --
228 -- A message can have MESSAGE_LEVEL in (1, 2, 3, 4, 5) where
229 -- a level 1 message is a low level (detail) message and a
230 -- level 5 message is a high level (overview) message. High
231 -- level messages will always be outputted to the output
232 -- destination. If the user wishes to see lower level messages
233 -- the user must lower the value of the profile option
234 -- PJI_DEBUG_LEVEL.
235 --
236 -- ------------------------------------------------------
237 procedure WRITE2LOG
238 (
239 p_msg in varchar2,
240 p_timer_flag in boolean default null,
241 p_debug_level in number default 1
242 ) is
243
244 l_timestamp varchar2(30);
245 l_output_dest varchar2(15);
246 begin
247
248 if (g_session_sid is null) then
249 init_session_cache;
250 end if;
251
252 if (g_pa_debug_mode = 'Y' or
253 g_debug_level = 5) then
254
255 l_output_dest := nvl(FND_PROFILE.VALUE('PJI_OUTPUT_DESTINATION'),
256 g_output_dest);
257
258 if (p_timer_flag) then
259 l_timestamp := ' ' || to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS');
260 else
261 l_timestamp := NULL;
262 end if;
263
264 if (l_output_dest is null or l_output_dest = 'TABLE') then
265
266 if (p_debug_level >= g_debug_level) then
267
268 insert into PJI_SYSTEM_DEBUG_MSG
269 (
270 MESSAGE_ID,
271 MESSAGE_LEVEL,
272 MESSAGE_CONTEXT,
273 MESSAGE_TEXT,
274 MESSAGE_TYPE,
275 MODULE,
276 CREATED_BY,
277 CREATION_DATE
278 )
279 values
280 (
281 PJI_SYSTEM_DEBUG_MSG_S.NEXTVAL,
282 p_debug_level,
283 nvl(g_debug_context, g_session_osuser || '$' || g_session_sid),
284 p_msg || l_timestamp,
285 'LOG',
286 g_module,
287 g_session_user_id,
288 sysdate
289 );
290
291 end if;
292
293 elsif (l_output_dest = 'DBMS_OUTPUT') then
294 null;
295 -- for GSCC standards
296 -- dbms_ output.put_ line(p_msg || l_timestamp);
297 else
298 -- in all other cases write the message into the log file
299 pa_debug.log_message(p_message => p_msg || l_timestamp);
300 end if;
301
302 end if;
303
304 end WRITE2LOG;
305
306
307 -- ------------------------------------------------------
308 -- procedure WRITE2OUT
309 --
310 -- In PJI_SYSTEM_DEBUG_MSG, output file lines have MESSAGE_LEVEL = 6 to
311 -- distiguish from debugging messages.
312 --
313 -- ------------------------------------------------------
314 procedure WRITE2OUT (p_msg in varchar2) is
315
316 l_output_dest varchar2(15);
317
318 begin
319
320 l_output_dest := nvl(FND_PROFILE.VALUE('PJI_OUTPUT_DESTINATION'),
321 g_output_dest);
322
323 if (l_output_dest = 'TABLE') then
324
325 insert into PJI_SYSTEM_DEBUG_MSG
326 (
327 MESSAGE_ID,
328 MESSAGE_LEVEL,
329 MESSAGE_CONTEXT,
330 MESSAGE_TEXT,
331 MESSAGE_TYPE,
332 MODULE,
333 CREATED_BY,
334 CREATION_DATE
335 )
336 values
337 (
338 PJI_SYSTEM_DEBUG_MSG_S.NEXTVAL,
339 6,
340 nvl(g_debug_context, g_session_osuser || '$' || g_session_sid),
341 p_msg,
342 'OUT',
343 g_module,
344 g_session_user_id,
345 sysdate
346 );
347
348 elsif (l_output_dest = 'DBMS_OUTPUT') then
349 null;
350 -- for GSCC standards
351 -- dbms_ output.put_ line('OUT: ' || p_msg);
352 else
353 -- in all other cases write the message into the output file
354 FND_FILE.PUT(FND_FILE.OUTPUT, p_msg);
355 end if;
356
357 exception
358
359 when UTL_FILE.INVALID_PATH then
360 raise_application_error(-20010,
361 'INVALID PATH exception from UTL_FILE');
362
363 when UTL_FILE.INVALID_MODE then
364 raise_application_error(-20010,
365 'INVALID MODE exception from UTL_FILE');
366
367 when UTL_FILE.INVALID_FILEHANDLE then
368 raise_application_error(-20010,
369 'INVALID FILEHANDLE exception from UTL_FILE');
370
371 when UTL_FILE.INVALID_OPERATION then
372 raise_application_error(-20010,
373 'INVALID OPERATION exception from UTL_FILE');
374
375 when UTL_FILE.READ_ERROR then
376 raise_application_error(-20010,
377 'READ ERROR exception from UTL_FILE');
378
379 when UTL_FILE.WRITE_ERROR then
380 raise_application_error(-20010,
381 'WRITE ERROR exception from UTL_FILE');
382
383 when UTL_FILE.INTERNAL_ERROR then
384 raise_application_error(-20010,
385 'INTERNAL ERROR exception from UTL_FILE');
386
387 when others then raise;
388
389 end WRITE2OUT;
390
391
392 -- ------------------------------------------------------
393 -- procedure WRITE2SSWALOG
394 -- ------------------------------------------------------
395 procedure WRITE2SSWALOG
396 (
397 p_msg in varchar2,
398 p_debug_level in number default 0,
399 p_module in varchar2 default NULL
400 ) is
401 begin
402
403 if g_session_sid is null then
404 init_session_cache;
405 end if;
406
407 if g_pa_debug_mode = 'Y' then
408
409 insert into PJI_SYSTEM_DEBUG_MSG
410 (
411 MESSAGE_ID
412 , MESSAGE_LEVEL
413 , MESSAGE_CONTEXT
414 , MESSAGE_TEXT
415 , MESSAGE_TYPE
416 , MODULE
417 , CREATED_BY
418 , CREATION_DATE
419 )
420 values
421 (
422 PJI_SYSTEM_DEBUG_MSG_S.NEXTVAL
423 , p_debug_level
424 , fnd_global.user_id || '$' || g_session_sid
425 , p_msg
426 , 'SSWA'
427 , p_module
428 , fnd_global.user_id
429 , sysdate
430 );
431
432 end if;
433
434 end;
435
436
437 -- ------------------------------------------------------
438 -- procedure RESET_SSWA_SESSION_CACHE
439 -- ------------------------------------------------------
440 procedure RESET_SSWA_SESSION_CACHE is
441 begin
442 init_session_cache;
443 end;
444
445
446 -- ------------------------------------------------------
447 -- function GET_PJI_DATA_TSPACE
448 -- ------------------------------------------------------
449 function GET_PJI_DATA_TSPACE return varchar2 is
450
451 l_pji_schema varchar2(30);
452
453 begin
454
455 if (g_pji_data_tspace is null) then
456
457 l_pji_schema := get_pji_schema_name;
458
459 select TABLESPACE_NAME
460 into g_pji_data_tspace
461 from ALL_TABLES
462 where OWNER = l_pji_schema and
463 TABLE_NAME = 'PJI_SYSTEM_PARAMETERS';
464
465 end if;
466
467 return g_pji_data_tspace;
468
469 end GET_PJI_DATA_TSPACE;
470
471
472 -- ------------------------------------------------------
473 -- function GET_PJI_IDX_TSPACE
474 -- ------------------------------------------------------
475 function GET_PJI_INDEX_TSPACE return varchar2 is
476
477 l_pji_schema varchar2(30);
478
479 begin
480
481 if (g_pji_index_tspace is null) then
482
483 l_pji_schema := get_pji_schema_name;
484
485 select TABLESPACE_NAME
486 into g_pji_index_tspace
487 from ALL_INDEXES
488 where OWNER = l_pji_schema and
489 INDEX_NAME = 'PJI_SYSTEM_PARAMETERS_U1';
490
491 end if;
492
493 return g_pji_index_tspace;
494
495 end GET_PJI_INDEX_TSPACE;
496
497
498 -- ------------------------------------------------------
499 -- function GET_SETUP_PARAMETER
500 -- ------------------------------------------------------
501 function GET_SETUP_PARAMETER (p_name in varchar2) return varchar2 is
502 begin
503
504 if ( NOT g_settings_init_flag ) then
505 init_settings_cache;
506 end if;
507
508 if p_name = 'ORGANIZATION_STRUCTURE_ID' then
509 return to_char(g_pji_settings.ORGANIZATION_STRUCTURE_ID);
510 elsif p_name = 'ORG_STRUCTURE_VERSION_ID' then
511 return to_char(g_pji_settings.ORG_STRUCTURE_VERSION_ID);
512 elsif p_name = 'PA_PERIOD_FLAG' then
513 return g_pji_settings.PA_PERIOD_FLAG;
514 elsif p_name = 'GL_PERIOD_FLAG' then
515 return g_pji_settings.GL_PERIOD_FLAG;
516 elsif p_name = 'CONVERSION_RATIO_DAYS' then
517 return to_char(g_pji_settings.CONVERSION_RATIO_DAYS);
518 elsif p_name = 'BOOK_TO_BILL_DAYS' then
519 return to_char(g_pji_settings.BOOK_TO_BILL_DAYS);
520 elsif p_name = 'DSO_DAYS' then
521 return to_char(g_pji_settings.DSO_DAYS);
522 elsif p_name = 'DORMANT_BACKLOG_DAYS' then
523 return to_char(g_pji_settings.DORMANT_BACKLOG_DAYS);
524 elsif p_name = 'REPORT_COST_TYPE' then
525 return g_pji_settings.REPORT_COST_TYPE;
526 elsif p_name = 'COST_BUDGET_TYPE_CODE' then
527 return g_pji_settings.COST_BUDGET_TYPE_CODE;
528 elsif p_name = 'COST_BUDGET_CONV_RULE' then
529 return g_pji_settings.COST_BUDGET_CONV_RULE;
530 elsif p_name = 'REVENUE_BUDGET_TYPE_CODE' then
531 return g_pji_settings.REVENUE_BUDGET_TYPE_CODE;
532 elsif p_name = 'REVENUE_BUDGET_CONV_RULE' then
533 return g_pji_settings.REVENUE_BUDGET_CONV_RULE;
534 elsif p_name = 'COST_FORECAST_TYPE_CODE' then
535 return g_pji_settings.COST_FORECAST_TYPE_CODE;
536 elsif p_name = 'COST_FORECAST_CONV_RULE' then
537 return g_pji_settings.COST_FORECAST_CONV_RULE;
538 elsif p_name = 'REVENUE_FORECAST_TYPE_CODE' then
539 return g_pji_settings.REVENUE_FORECAST_TYPE_CODE;
540 elsif p_name = 'REVENUE_FORECAST_CONV_RULE' then
541 return g_pji_settings.REVENUE_FORECAST_CONV_RULE;
542 elsif p_name = 'COST_FP_TYPE_ID' then
543 return g_pji_settings.COST_FP_TYPE_ID;
544 elsif p_name = 'REVENUE_FP_TYPE_ID' then
545 return g_pji_settings.REVENUE_FP_TYPE_ID;
546 elsif p_name = 'COST_FORECAST_FP_TYPE_ID' then
547 return g_pji_settings.COST_FORECAST_FP_TYPE_ID;
548 elsif p_name = 'REVENUE_FORECAST_FP_TYPE_ID' then
549 return g_pji_settings.REVENUE_FORECAST_FP_TYPE_ID;
550 elsif p_name = 'REPORT_LABOR_UNITS' then
551 return g_pji_settings.REPORT_LABOR_UNITS;
552 elsif p_name = 'GLOBAL_START_DATE' then
553 return to_char(GET_EXTRACTION_START_DATE, '1990/01/01');
554 elsif p_name = 'GLOBAL_CURR2_FLAG' then
555 return NVL(g_pji_settings.GLOBAL_CURR2_FLAG, 'N');
556 elsif p_name = 'TXN_CURR_FLAG' then
557 return NVL(g_pji_settings.TXN_CURR_FLAG,'N') ;
558 else
559 return NULL;
560 end if;
561
562 end GET_SETUP_PARAMETER;
563
564
565 -- ******************************************************
566 -- FUNCTION spread_amount
567 -- ******************************************************
568
569 FUNCTION spread_amount (
570 x_type_of_spread IN VARCHAR2,
571 x_start_date IN DATE,
572 x_end_date IN DATE,
573 x_start_pa_date IN DATE,
574 x_end_pa_date IN DATE,
575 x_amount IN NUMBER)
576 RETURN NUMBER
577 IS
578 BEGIN
579
580 IF x_type_of_spread = 'L' THEN
581
582 -- Linear Spread
583
584 IF ( x_start_date <= x_start_pa_date ) AND
585 ( x_end_date >= x_End_pa_date ) THEN
586
587 -- PA_PERIOD is within or identical to other period
588
589 RETURN (x_end_pa_date - x_start_pa_date + 1) * x_amount/
590 (x_end_date - x_start_date+ 1);
591
592 ELSIF ( x_start_pa_date <= x_start_date) AND
593 ( x_end_pa_date <= x_End_date ) THEN
594
595 RETURN ( x_end_pa_date - x_start_date+ 1) * x_amount /
596 (x_end_date - x_start_date + 1) ;
597
598 ELSIF ( x_start_pa_date >= x_start_date) AND
599 ( x_end_pa_date >= x_End_date ) THEN
600
601 RETURN ( x_end_date - x_start_pa_date + 1) * x_amount /
602 (x_end_date - x_start_date + 1) ;
603
604 ELSIF ( x_start_pa_date <= x_start_date ) AND
605 ( x_end_pa_date >= x_End_date ) THEN
606
607 -- PA_PERIOD bigger or identical to other period
608
609 RETURN x_amount;
610
611 ELSIF ( x_end_pa_date <= x_start_date ) OR
612 ( x_start_pa_Date >= x_end_date ) OR
613 ( x_start_pa_date = x_end_pa_date )OR
614 ( x_start_date = x_end_date ) THEN
615
616 -- Non Overlapping PA period and amount periods
617 -- OR Zero Days PA period
618
619 RETURN 0;
620
621 END IF;
622
623 END IF;
624
625 RETURN 0;
626 EXCEPTION
627 WHEN OTHERS THEN
628 RETURN NULL;
629 END spread_amount;
630
631
632 -- ------------------------------------------------------
633 -- function GET_GLOBAL_RATE_PRIMARY
634 --
635 -- History
636 -- 19-MAR-2004 SVERMETT Created
637 --
638 -- Internal Summarization API.
639 --
640 -- ------------------------------------------------------
641 function GET_GLOBAL_RATE_PRIMARY(p_from_currency_code varchar2,
642 p_exchange_date date) return number is
643
644 l_global_currency_code varchar2(30);
645 l_global_rate_type varchar2(15);
646 l_max_roll_days number;
647 l_exchange_date date;
648 l_rate number;
649
650 begin
651
652 l_global_currency_code := FND_PROFILE.VALUE('BIS_PRIMARY_CURRENCY_CODE');
653 l_global_rate_type := FND_PROFILE.VALUE('BIS_PRIMARY_RATE_TYPE');
654 l_max_roll_days := NVL(PJI_UTILS.g_max_roll_days,32);
655 /* 5155692 Introduced the global variable g_max_roll_days, so that for plans we can
656 set it to 1500 and for actuals or default it will be 32 */
657
658 l_exchange_date := p_exchange_date;
659
660 if (p_from_currency_code = 'EUR' and
661 l_exchange_date < to_date('01/01/1999','DD/MM/RRRR')) then
662 l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
663 elsif (l_global_currency_code = 'EUR' and
664 l_exchange_date < to_date('01/01/1999','DD/MM/RRRR')) then
665 l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
666 end if;
667
668 if (l_global_currency_code is null) then
669 l_rate := 1;
670 elsif (p_from_currency_code = l_global_currency_code) then
671 l_rate := 1;
672 else
673 l_rate := GL_CURRENCY_API.GET_CLOSEST_RATE_SQL(p_from_currency_code,
674 l_global_currency_code,
675 l_exchange_date,
676 l_global_rate_type,
677 l_max_roll_days);
678 end if;
679
680 if (p_from_currency_code = 'EUR' and
681 p_exchange_date < to_date('01/01/1999','DD/MM/RRRR') and
682 l_rate = -1 ) then
683 l_rate := -3;
684 elsif (l_global_currency_code = 'EUR' and
685 p_exchange_date < to_date('01/01/1999','DD/MM/RRRR') and
686 l_rate = -1) then
687 l_rate := -3;
688 end if;
689
690 return (l_rate);
691
692 exception when others then return -4;
693
694 end GET_GLOBAL_RATE_PRIMARY;
695
696
697 -- ------------------------------------------------------
698 -- function GET_MAU_PRIMARY
699 --
700 -- History
701 -- 19-MAR-2004 SVERMETT Created
702 --
703 -- Internal Summarization API.
704 --
705 -- ------------------------------------------------------
706 function GET_MAU_PRIMARY return number is
707
708 l_mau number;
709 l_warehouse_currency_code varchar2(15);
710
711 begin
712
713 l_warehouse_currency_code := FND_PROFILE.VALUE('BIS_PRIMARY_CURRENCY_CODE');
714
715 select nvl(curr.MINIMUM_ACCOUNTABLE_UNIT, power(10, (-1 * curr.PRECISION)))
716 into l_mau
717 from FND_CURRENCIES curr
718 where curr.CURRENCY_CODE = l_warehouse_currency_code;
719
720 if l_mau is null then
721 l_mau := 0.01; -- assign default value if null;
722 elsif l_mau = 0 then
723 l_mau := 1;
724 end if;
725
726 return l_mau;
727
728 exception when others then return null;
729
730 end GET_MAU_PRIMARY;
731
732
733 -- ------------------------------------------------------
734 -- function GET_GLOBAL_PRIMARY_CURRENCY
735 --
736 -- History
737 -- 19-MAR-2004 SVERMETT Created
738 --
739 -- Internal Summarization API.
740 --
741 -- ------------------------------------------------------
742 function GET_GLOBAL_PRIMARY_CURRENCY
743 return varchar2 is
744
745 l_currency_code varchar2(30);
746
747 begin
748
749 l_currency_code := FND_PROFILE.VALUE('BIS_PRIMARY_CURRENCY_CODE');
750
751 return l_currency_code;
752
753 end GET_GLOBAL_PRIMARY_CURRENCY;
754
755
756 -- ------------------------------------------------------
757 -- function GET_GLOBAL_RATE_SECONDARY
758 --
759 -- History
760 -- 19-MAR-2004 SVERMETT Created
761 --
762 -- Internal Summarization API.
763 --
764 -- ------------------------------------------------------
765 function GET_GLOBAL_RATE_SECONDARY(p_from_currency_code varchar2,
766 p_exchange_date date) return number is
767
768 l_global_currency_code varchar2(30);
769 l_global_rate_type varchar2(15);
770 l_max_roll_days number;
771 l_exchange_date date;
772 l_rate number;
773
774 begin
775
776 l_global_currency_code := FND_PROFILE.VALUE('BIS_SECONDARY_CURRENCY_CODE');
777 l_global_rate_type := FND_PROFILE.VALUE('BIS_SECONDARY_RATE_TYPE');
778 l_max_roll_days := NVL(PJI_UTILS.g_max_roll_days,32);
779 /* 5155692 Introduced the global variable g_max_roll_days, so that for plans we can
780 set it to 1500 and for actuals or default it will be 32 */
781
782 l_exchange_date := p_exchange_date;
783
784 if (p_from_currency_code = 'EUR' and
785 l_exchange_date < to_date('01/01/1999','DD/MM/RRRR')) then
786 l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
787 elsif (l_global_currency_code = 'EUR' and
788 l_exchange_date < to_date('01/01/1999','DD/MM/RRRR')) then
789 l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
790 end if;
791
792 if (l_global_currency_code IS NULL) then
793 l_rate := 1;
794 elsif (p_from_currency_code = l_global_currency_code) then
795 l_rate := 1;
796 else
797 l_rate := GL_CURRENCY_API.GET_CLOSEST_RATE_SQL(p_from_currency_code,
798 l_global_currency_code,
799 l_exchange_date,
800 l_global_rate_type,
801 l_max_roll_days);
802
803 end if;
804
805 if (p_from_currency_code = 'EUR' and
806 p_exchange_date < to_date('01/01/1999','DD/MM/RRRR') and
807 l_rate = -1) then
808 l_rate := -3;
809 elsif (l_global_currency_code = 'EUR' and
810 p_exchange_date < to_date('01/01/1999','DD/MM/RRRR') and
811 l_rate = -1) then
812 l_rate := -3;
813 end if;
814
815 return (l_rate);
816
817 exception when others then return -4;
818
819 end GET_GLOBAL_RATE_SECONDARY;
820
821
822 -- ------------------------------------------------------
823 -- function GET_MAU_SECONDARY
824 --
825 -- History
826 -- 19-MAR-2004 SVERMETT Created
827 --
828 -- Internal Summarization API.
829 --
830 -- ------------------------------------------------------
831 function GET_MAU_SECONDARY return number is
832
833 l_mau number;
834 l_warehouse_currency_code varchar2(15);
835
836 BEGIN
837
838 l_warehouse_currency_code := FND_PROFILE.VALUE('BIS_SECONDARY_CURRENCY_CODE');
839
840 select nvl(curr.MINIMUM_ACCOUNTABLE_UNIT, power(10, (-1 * curr.PRECISION)))
841 into l_mau
842 from FND_CURRENCIES curr
843 where curr.CURRENCY_CODE = l_warehouse_currency_code;
844
845 if l_mau is null then
846 l_mau := 0.01; -- assign default value if null;
847 elsif l_mau = 0 then
848 l_mau := 1;
849 end if;
850
851 return l_mau;
852
853 exception when others then return null;
854
855 end GET_MAU_SECONDARY;
856
857
858 -- ------------------------------------------------------
859 -- function GET_GLOBAL_SECONDARY_CURRENCY
860 --
861 -- History
862 -- 19-MAR-2004 SVERMETT Created
863 --
864 -- Internal Summarization API.
865 --
866 -- ------------------------------------------------------
867 function GET_GLOBAL_SECONDARY_CURRENCY
868 return varchar2 is
869
870 l_currency_code varchar2(30);
871
872 begin
873
874 l_currency_code := FND_PROFILE.VALUE('BIS_SECONDARY_CURRENCY_CODE');
875
876 return l_currency_code;
877
878 end GET_GLOBAL_SECONDARY_CURRENCY;
879
880
881 -- ------------------------------------------------------
882 -- function GET_RATE
883 --
884 -- History
885 -- 19-MAR-2004 SVERMETT Created
886 --
887 -- Internal Summarization API.
888 --
889 -- ------------------------------------------------------
890 function GET_RATE(p_from_currency_code varchar2,
891 p_to_currency_code varchar2,
892 p_exchange_date date) return number is
893
894 l_exchange_rate_type varchar2(255) := null;
895 l_exchange_date date;
896 l_max_roll_days number := 32;
897 l_rate number;
898
899 begin
900
901 l_exchange_date := p_exchange_date;
902
903 if (p_from_currency_code = 'EUR' and
904 l_exchange_date < to_date('01/01/1999','DD/MM/RRRR')) then
905 l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
906 elsif (p_to_currency_code = 'EUR' and
907 l_exchange_date < to_date('01/01/1999','DD/MM/RRRR')) then
908 l_exchange_date := to_date('01/01/1999','DD/MM/RRRR');
909 end if;
910
911 if (p_from_currency_code = p_to_currency_code) then
912 l_rate := 1;
913 else
914 l_rate := GL_CURRENCY_API.GET_CLOSEST_RATE_SQL(p_from_currency_code,
915 p_to_currency_code,
916 l_exchange_date,
917 l_exchange_rate_type,
918 l_max_roll_days);
919 end if;
920
921 if (p_from_currency_code = 'EUR' and
922 p_exchange_date < to_date('01/01/1999','DD/MM/RRRR') and
923 l_rate = -1) then
924 l_rate := -3;
925 elsif (p_to_currency_code = 'EUR' and
926 p_exchange_date < to_date('01/01/1999','DD/MM/RRRR') and
927 l_rate = -1) then
928 l_rate := -3;
929 end if;
930
931 return l_rate;
932
933 exception when others then return -4;
934
935 end GET_RATE;
936
937
938 -- ------------------------------------------------------
939 -- function GET_RATE_TYPE
940 --
941 -- History
942 -- 19-MAR-2004 SVERMETT Created
943 --
944 -- Internal Summarization API.
945 --
946 -- ------------------------------------------------------
947 function GET_RATE_TYPE return varchar2 is
948
949 begin
950
951 return FND_PROFILE.VALUE('BIS_PRIMARY_RATE_TYPE');
952
953 end GET_RATE_TYPE;
954
955
956 -- ------------------------------------------------------
957 -- function GET_MAU
958 --
959 -- History
960 -- 19-MAR-2004 SVERMETT Created
961 --
962 -- Internal Summarization API.
963 --
964 -- ------------------------------------------------------
965 function GET_MAU (p_currency_code varchar2) return number is
966
967 l_mau number;
968
969 begin
970
971 select nvl(MINIMUM_ACCOUNTABLE_UNIT, power(10, (-1 * PRECISION)))
972 into l_mau
973 from FND_CURRENCIES
974 where CURRENCY_CODE = p_currency_code;
975
976 if (l_mau is null) then
977 l_mau := 0.01;
978 elsif (l_mau = 0) then
979 l_mau := 1;
980 end if;
981
982 return l_mau;
983
984 exception when others then return null;
985
986 end GET_MAU;
987
988
989 -- ------------------------------------------------------
990 -- function GET_DEGREE_OF_PARALLELISM
991 --
992 -- History
993 -- 19-MAR-2004 SVERMETT Created
994 --
995 -- Internal Summarization API.
996 --
997 -- ------------------------------------------------------
998 function GET_DEGREE_OF_PARALLELISM return number is
999
1000 l_parallel number;
1001
1002 begin
1003
1004 l_parallel := null;
1005 l_parallel := floor(fnd_profile.value('EDW_PARALLEL_SRC')); -- gets value of profile option
1006
1007 /* Set by the customer, return this value */
1008
1009 IF (l_parallel IS NOT NULL and l_parallel > 0) THEN
1010 return l_parallel;
1011 END IF;
1012
1013 /* Not set by customer, so query v$pq_sysstat */
1014
1015 begin
1016
1017 select value INTO l_parallel
1018 from v$pq_sysstat where trim(statistic) = 'Servers Idle';
1019
1020 exception when no_data_found then
1021 l_parallel := 1;
1022 end;
1023
1024 IF (l_parallel IS NULL) THEN
1025 l_parallel:=1;
1026 END IF;
1027
1028 l_parallel := floor(l_parallel/2);
1029 IF (l_parallel = 0) THEN
1030 l_parallel := 1;
1031 END IF;
1032
1033 return l_parallel;
1034
1035 end GET_DEGREE_OF_PARALLELISM;
1036
1037
1038 -----------------------------------------------------
1039 -- function get_period_set_name
1040 -- -----------------------------------------------------
1041 FUNCTION get_period_set_name RETURN VARCHAR2 IS
1042 l_period_set_name VARCHAR2(15);
1043 --
1044 -- History
1045 -- 18-MAR-2004 VMANGULU Created
1046 --
1047 -- return: NULL = profile not defined
1048 -- return: rate type = value defined for the profile
1049 -- option
1050 --
1051 -- *** This API returns value for BIS: Enterprise Calendar
1052 -- *** profile.
1053 --
1054 BEGIN
1055 l_period_set_name:=Fnd_Profile.VALUE('BIS_ENTERPRISE_CALENDAR');
1056 RETURN l_period_set_name;
1057 EXCEPTION
1058 WHEN NO_DATA_FOUND THEN
1059 RETURN NULL;
1060 WHEN OTHERS THEN
1061 RAISE;
1062 END;
1063
1064
1065 -- -----------------------------------------------------
1066 -- function get_START_DAY_OF_WEEK_ID
1067 -- -----------------------------------------------------
1068 FUNCTION get_START_DAY_OF_WEEK_ID RETURN VARCHAR2 IS
1069 --
1070 -- History
1071 -- 18-MAR-2004 VMANGULU Created
1072 --
1073 -- return: NULL = profile not defined
1074 -- return: rate type = value defined for the profile
1075 -- option
1076 --
1077 -- *** This API returns value for BIS: Start Day of Week
1078 -- *** profile.
1079 --
1080 l_start_dayofweek VARCHAR2(30);
1081 BEGIN
1082 l_start_dayofweek:=Fnd_Profile.VALUE('BIS_START_DAY_OF_WEEK');
1083 RETURN l_start_dayofweek;
1084 EXCEPTION
1085 WHEN NO_DATA_FOUND THEN
1086 RETURN NULL;
1087 WHEN OTHERS THEN
1088 RAISE;
1089 END;
1090
1091
1092 -- -----------------------------------------------------
1093 -- function get_period_type
1094 -- -----------------------------------------------------
1095 FUNCTION get_period_type RETURN VARCHAR2 IS
1096 --
1097 -- History
1098 -- 18-MAR-2004 VMANGULU Created
1099 --
1100 -- return: NULL = profile not defined
1101 -- return: rate type = value defined for the profile
1102 -- option
1103 --
1104 -- *** This API returns value for BIS: Period Type
1105 -- *** profile.
1106 --
1107 l_period_type VARCHAR2(15);
1108 BEGIN
1109 l_period_type:=Fnd_Profile.VALUE('BIS_PERIOD_TYPE');
1110 RETURN l_period_type;
1111 EXCEPTION
1112 WHEN NO_DATA_FOUND THEN
1113 RETURN NULL;
1114 WHEN OTHERS THEN
1115 RAISE;
1116 END;
1117
1118
1119 -- -----------------------------------------------------
1120 -- function getMissingRateHeader
1121 --
1122 -- History
1123 -- 19-MAR-2004 SVERMETT From BIS_COLLECTION_UTILITIES
1124 --
1125 -- Internal Summarization API.
1126 --
1127 -- -----------------------------------------------------
1128 FUNCTION getMissingRateHeader return VARCHAR2 IS
1129 l_msg varchar2(3000) := null;
1130 l_newline varchar2(10) := '
1131 ';
1132 l_temp varchar2(1000) := null;
1133 BEGIN
1134
1135
1136 fnd_message.set_name('PJI','PJI_SUM_DBI_CURR_OUTPUT_HDR');
1137 l_msg := fnd_message.get || l_newline || l_newline;
1138
1139
1140 fnd_message.set_name('PJI','PJI_SUM_DBI_COL_RATE_TYPE');
1141 l_temp:=substr(fnd_message.get, 1,g_length_rate_type );
1142 l_temp := l_temp|| substr(g_space, 1, g_length_rate_type - length(l_temp))||g_indenting;
1143 l_msg := l_msg || l_temp;
1144
1145
1146
1147 fnd_message.set_name('PJI','PJI_SUM_DBI_COL_FROM_CURRENCY');
1148 l_temp := substr(fnd_message.get, 1, g_length_from_currency);
1149 l_temp := l_temp || substr(g_space, 1, g_length_from_currency - length(l_temp)) || g_indenting;
1150 l_msg := l_msg || l_temp;
1151
1152 fnd_message.set_name('PJI','PJI_SUM_DBI_COL_TO_CURRENCY');
1153 l_temp:=substr(fnd_message.get, 1,g_length_to_currency );
1154 l_temp := l_temp || substr(g_space, 1, g_length_to_currency - length(l_temp)) || g_indenting;
1155 l_msg := l_msg || l_temp;
1156
1157 fnd_message.set_name('PJI','PJI_SUM_DBI_COL_DATE');
1158 l_temp:=substr(fnd_message.get, 1,g_length_date );
1159 l_temp := l_temp || substr(g_space, 1, g_length_date - length(l_temp));
1160 l_msg := l_msg || l_temp || l_newline;
1161
1162 l_temp := substr(g_line, 1, g_length_rate_type)||g_indenting||
1163 substr(g_line, 1, g_length_from_currency)||g_indenting||
1164 substr(g_line, 1, g_length_to_currency)||g_indenting||
1165 substr(g_line, 1, g_length_date);
1166
1167 /*'------------'||g_indenting ||'-----------------'||g_indenting||
1168 '---------------'||g_indenting||'-------------';*/
1169 l_msg := l_msg || l_temp||l_newline;
1170
1171 return l_msg;
1172 END;
1173
1174
1175 -- -----------------------------------------------------
1176 -- function getMissingRateText
1177 --
1178 -- History
1179 -- 19-MAR-2004 SVERMETT From BIS_COLLECTION_UTILITIES
1180 --
1181 -- Internal Summarization API.
1182 --
1183 -- -----------------------------------------------------
1184 FUNCTION getMissingRateText(
1185 p_rate_type IN VARCHAR2, /* Rate type */
1186 p_from_currency IN VARCHAR2, /* From Currency */
1187 p_to_currency in VARCHAR2, /* To Currency */
1188 p_date IN DATE, /* Date in default format */
1189 p_date_override IN VARCHAR2) return VARCHAR2 /* Formatted date, will output this instead of p_date */
1190 IS
1191
1192 l_msg varchar2(1000) := null;
1193 l_temp varchar2(1000) := null;
1194 l_user_rate_type varchar2(30):=null;
1195
1196 cursor c_user_rate_type is
1197 SELECT user_conversion_type
1198 FROM gl_daily_conversion_types
1199 WHERE conversion_type = p_rate_type;
1200
1201 BEGIN
1202
1203 open c_user_rate_type;
1204 fetch c_user_rate_type into l_user_rate_type;
1205 if c_user_rate_type%notfound then
1206 l_user_rate_type:=p_rate_type;
1207 end if;
1208 close c_user_rate_type;
1209
1210 ---l_msg:=substr(p_rate_type, 1,g_length_rate_type );
1211 l_msg:=substr(l_user_rate_type, 1,g_length_rate_type );
1212
1213 l_msg := l_msg || substr(g_space, 1, g_length_rate_type - length(l_msg))|| g_indenting;
1214
1215
1216 l_temp:=substr(p_from_currency, 1, g_length_from_currency);
1217 l_temp := l_temp || substr(g_space, 1, g_length_from_currency - length(l_temp)) || g_indenting;
1218 l_msg := l_msg||l_temp;
1219
1220
1221 l_temp:=substr(p_to_currency, 1,g_length_to_currency );
1222 l_temp := l_temp || substr(g_space, 1, g_length_to_currency - length(l_temp)) || g_indenting;
1223 l_msg := l_msg ||l_temp;
1224
1225
1226 IF (p_date_override IS NULL) THEN
1227 l_temp:=substr(fnd_date.date_to_displayDT(p_date), 1,g_length_date );
1228 ELSE
1229 l_temp := substr(p_date_override, 1,g_length_date );
1230 END IF;
1231
1232 l_temp := l_temp || substr(g_space, 1, g_length_date - length(l_temp)) || g_indenting;
1233 l_msg := l_msg||l_temp;
1234
1235 return l_msg;
1236
1237 END;
1238
1239
1240 -- -----------------------------------------------------
1241 -- function CHECK_PROGRAM_RBS
1242 --
1243 -- History
1244 -- 19-MAR-2004 SVERMETT Created
1245 --
1246 --
1247 -- return: 0 = okay to remove project / RBS association
1248 -- return: -1 = RBS is pushed down from parent project
1249 --
1250 --
1251 -- -----------------------------------------------------
1252 function CHECK_PROGRAM_RBS (p_project_id in number,
1253 p_rbs_version_id in number)
1254 return number is
1255
1256 l_count number;
1257
1258 begin
1259
1260 select /*+ index(rel, PA_OBJECT_RELATIONSHIPS_N2) */
1261 count(*)
1262 into
1263 l_count
1264 from
1265 PA_OBJECT_RELATIONSHIPS rel,
1266 PA_RBS_PRJ_ASSIGNMENTS rbs
1267 where
1268 ROWNUM = 1 and
1269 rel.OBJECT_TYPE_TO = 'PA_STRUCTURES' and
1270 rel.OBJECT_ID_TO1 is not null and
1271 rel.OBJECT_ID_TO2 = p_project_id and
1272 rel.RELATIONSHIP_TYPE in ('LW', 'LF') and
1273 rbs.RBS_VERSION_ID = p_rbs_version_id and
1274 rbs.PROG_REP_USAGE_FLAG = 'Y' and
1275 rbs.PROJECT_ID = rel.OBJECT_ID_FROM2;
1276
1277 if (l_count > 0) then
1278
1279 return -1;
1280
1281 end if;
1282
1283 return 0;
1284
1285 end CHECK_PROGRAM_RBS;
1286
1287 -- -----------------------------------------------------
1288 -- Funtion Derive_curr_rep_Info
1289 -- For project performance setups Audit report
1290 -- function used to get the current_rep_period
1291 -- History
1292 -- 19-APR-2006 DEGUPTA Created
1293 -- -----------------------------------------------------
1294 FUNCTION Derive_Curr_rep_Info(p_org_id NUMBER
1295 , p_calendar_type VARCHAR2
1296 , p_active_rep VARCHAR2
1297 )
1298 return VARCHAR2
1299 IS
1300 l_specific_pa_period VARCHAR2(30);
1301 l_specific_gl_period VARCHAR2(30);
1302 l_specific_ent_period VARCHAR2(30);
1303 l_report_date DATE;
1304 l_period_name VARCHAR2(100);
1305 l_application_id NUMBER;
1306 l_gl_calendar_id NUMBER;
1307 l_pa_calendar_id NUMBER;
1308 l_specific_period VARCHAR2(30);
1309 l_calendar_id NUMBER;
1310 Begin
1311 IF p_active_rep = 'SPECIFIC' THEN
1312 BEGIN
1313 SELECT
1314 info.pa_curr_rep_period,
1315 info.gl_curr_rep_period,
1316 params.value
1317 INTO l_specific_pa_period, l_specific_gl_period, l_specific_ent_period
1318 FROM pji_org_extr_info info,
1319 pji_system_parameters params
1320 WHERE info.org_id = p_org_id
1321 AND params.name = 'PJI_PJP_ENT_CURR_REP_PERIOD';
1322 EXCEPTION WHEN NO_DATA_FOUND THEN
1323 NULL;
1324 END;
1325 END IF;
1326
1327 IF p_calendar_type = 'E' THEN
1328 IF p_active_rep IN ('CURRENT','PRIOR') THEN
1329 SELECT start_date
1330 INTO l_report_date
1331 FROM pji_time_ent_period_v
1332 WHERE TRUNC(SYSDATE) BETWEEN start_date AND end_date;
1333 END IF;
1334
1335 IF p_active_rep = 'PRIOR' THEN
1336 SELECT MAX(start_date)
1337 INTO l_report_date
1338 FROM pji_time_ent_period_v
1339 WHERE end_date <l_report_date;
1340 END IF;
1341 IF p_active_rep = 'SPECIFIC' THEN
1342 l_period_name := l_specific_ent_period;
1343 ELSE
1344 SELECT name
1345 INTO l_period_name
1346 FROM pji_time_ent_period_v
1347 WHERE l_report_date BETWEEN start_date AND end_date;
1348 END IF;
1349
1350 ELSE
1351 SELECT info.gl_calendar_id, info.pa_calendar_id
1352 INTO l_gl_calendar_id, l_pa_calendar_id
1353 FROM pji_org_extr_info info
1354 WHERE info.org_id = p_org_id;
1355
1356 IF p_calendar_type = 'G' THEN
1357 l_calendar_id := l_gl_calendar_id;
1358 l_application_id := 101;
1359 l_specific_period := l_specific_gl_period;
1360 ELSE
1361 l_calendar_id := l_pa_calendar_id;
1362 l_application_id := 275;
1363 l_specific_period := l_specific_pa_period;
1364 END IF;
1365
1366 IF p_active_rep ='FIRST_OPEN' THEN
1367 SELECT MIN(TIM.start_date) first_open
1368 INTO l_report_date
1369 FROM
1370 pji_time_cal_period_v TIM
1371 , gl_period_statuses glps
1372 , pa_implementations_all paimp
1373 WHERE 1=1
1374 AND TIM.calendar_id = l_calendar_id
1375 AND paimp.set_of_books_id = glps.set_of_books_id
1376 AND paimp.org_id = p_org_id
1377 AND glps.application_id = l_application_id
1378 AND glps.period_name = TIM.NAME
1379 AND closing_status = 'O';
1380 ELSIF p_active_rep = 'LAST_OPEN' THEN
1381 SELECT MAX(TIM.start_date) last_open
1382 INTO l_report_date
1383 FROM
1384 pji_time_cal_period_v TIM
1385 , gl_period_statuses glps
1386 , pa_implementations_all paimp
1387 WHERE 1=1
1388 AND TIM.calendar_id = l_calendar_id
1389 AND paimp.set_of_books_id = glps.set_of_books_id
1390 AND paimp.org_id = p_org_id
1391 AND glps.application_id = 275
1392 AND glps.period_name = TIM.NAME
1393 AND closing_status = 'O';
1394 ELSIF p_active_rep = 'LAST_CLOSED' THEN
1395 SELECT MAX(TIM.start_date) last_closed
1396 INTO l_report_date
1397 FROM
1398 pji_time_cal_period_v TIM
1399 , gl_period_statuses glps
1400 , pa_implementations_all paimp
1401 WHERE 1=1
1402 AND TIM.calendar_id = l_calendar_id
1403 AND paimp.set_of_books_id = glps.set_of_books_id
1404 AND paimp.org_id = p_org_id
1405 AND glps.application_id = l_application_id
1406 AND glps.period_name = TIM.NAME
1407 AND closing_status = 'C';
1408 ELSIF p_active_rep IN ('CURRENT','PRIOR') THEN
1409 SELECT start_date
1410 INTO l_report_date
1411 FROM pji_time_cal_period_v
1412 WHERE TRUNC(SYSDATE) BETWEEN start_date
1413 AND end_date
1414 AND calendar_id = l_calendar_id;
1415 END IF;
1416
1417 IF p_active_rep = 'PRIOR' THEN
1418 SELECT MAX(start_date)
1419 INTO l_report_date
1420 FROM pji_time_cal_period_v
1421 WHERE end_date < l_report_date
1422 AND calendar_id = l_calendar_id;
1423 END IF;
1424
1425 IF p_active_rep = 'SPECIFIC' THEN
1426 l_period_name := l_specific_period;
1427 ELSE
1428 SELECT name
1429 INTO l_period_name
1430 FROM pji_time_cal_period_v
1431 WHERE l_report_date BETWEEN start_date AND end_date
1432 AND calendar_id = l_calendar_id;
1433 END IF;
1434
1435
1436 END IF;
1437 return l_period_name;
1438 Exception when others then
1439 return NULL;
1440 END;
1441
1442 -- -----------------------------------------------------
1443 -- Procedure REPORT_PJP_PARAM_SETUP
1444 -- For project performance setups Audit report
1445 -- History
1446 -- 14-MAR-2006 DEGUPTA Created
1447 -- -----------------------------------------------------
1448 PROCEDURE REPORT_PJP_PARAM_SETUP
1449 (errbuff OUT NOCOPY VARCHAR2,
1450 retcode OUT NOCOPY VARCHAR2)
1451 IS
1452 l_err_msg VARCHAR2(240);
1453 l_prm_miss number(10) := 0;
1454 /* l_prm_miss = 1 For Error
1455 2 Only Mandatory Setup Options are missing
1456 3 Only Optional Setup options are missing
1457 4 Both Mandatory and Optional setup options are missing */
1458 l_newline varchar2(10) := '
1459 ';
1460 l_pji_report_msg VARCHAR2(240);
1461 l_pji_head1 VARCHAR2(240);
1462 l_pji_head2 VARCHAR2(240);
1463 l_pji_head3 VARCHAR2(240);
1464 l_pji_head4 VARCHAR2(240);
1465 l_pji_head5 VARCHAR2(240);
1466 l_pji_line2 VARCHAR2(240) := '-';
1467 l_pji_line3 VARCHAR2(240) := '-';
1468 l_pji_line4 VARCHAR2(240) := '-';
1469 l_pji_line5 VARCHAR2(240) := '-';
1470 l_pji_tline VARCHAR2(240) := '-';
1471 l_pji_foot1 VARCHAR2(240);
1472 l_pji_foot2 VARCHAR2(240);
1473 l_pji_foot3 VARCHAR2(240);
1474 l_pji_foot4 VARCHAR2(240);
1475 l_bis_note number(10) := 0;
1476 l_pjp_note number(10) := 0;
1477 l_sp_note number(10) := 0;
1478 l_separator VARCHAR2(240) := ' ';
1479 prof_opt_tbl SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
1480 prof_val_tbl SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
1481 -- For BIS Report
1482 l_bis_pri_curr_code VARCHAR2(240);
1483 l_bis_pri_rate_type VARCHAR2(240);
1484 l_bis_sec_curr_code VARCHAR2(240);
1485 l_bis_sec_rate_type VARCHAR2(240);
1486 l_bis_ent_calendar VARCHAR2(240);
1487 l_bis_period_type VARCHAR2(240);
1488 l_bis_global_start_date VARCHAR2(240);
1489 l_pji_global_start_date VARCHAR2(240);
1490 l_p_bis_pri_curr_code VARCHAR2(100);
1491 l_p_bis_pri_rate_type VARCHAR2(100);
1492 l_p_bis_sec_curr_code VARCHAR2(100);
1493 l_p_bis_sec_rate_type VARCHAR2(100);
1494 l_p_bis_ent_calendar VARCHAR2(100);
1495 l_p_bis_period_type VARCHAR2(100);
1496 l_p_bis_global_start_date VARCHAR2(100);
1497 l_p_pji_global_start_date VARCHAR2(240);
1498 --- For PJP Report
1499 l_glb_curr_flag VARCHAR2(1);
1500 l_txn_curr_flag VARCHAR2(1);
1501 l_planamt_conv_date VARCHAR2 (30);
1502 l_planamt_alloc_method VARCHAR2 (30);
1503 l_curr_rep_pa_period VARCHAR2(30);
1504 l_curr_rep_gl_period VARCHAR2(30);
1505 l_curr_rep_ent_period VARCHAR2(30);
1506 l_sp_curr_rep_org_tbl SYSTEM.pa_varchar2_240_tbl_type := SYSTEM.pa_varchar2_240_tbl_type();
1507 l_sp_curr_rep_pa_period_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
1508 l_sp_curr_rep_gl_period_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
1509 l_sp_curr_rep_ent_period_tbl SYSTEM.pa_varchar2_30_tbl_type := SYSTEM.pa_varchar2_30_tbl_type();
1510 l_p_glb_curr_flag VARCHAR2(100);
1511 l_p_txn_curr_flag VARCHAR2(100);
1512 l_p_planamt_conv_date VARCHAR2 (100);
1513 l_p_planamt_alloc_method VARCHAR2 (100);
1514 l_p_curr_rep_pa_period VARCHAR2(100);
1515 l_p_curr_rep_gl_period VARCHAR2(100);
1516 l_p_curr_rep_ent_period VARCHAR2(100);
1517 l_p_curr_rep_not VARCHAR2(100);
1518 BEGIN
1519 -- For Bis Setup Options
1520 BEGIN
1521
1522 SELECT OP.PROFILE_OPTION_NAME,VAL.PROFILE_OPTION_VALUE BULK COLLECT
1523 INTO prof_opt_tbl,prof_val_tbl
1524 FROM FND_PROFILE_OPTIONS OP , FND_PROFILE_OPTION_VALUES VAL
1525 WHERE OP.PROFILE_OPTION_NAME
1526 IN ('BIS_PRIMARY_CURRENCY_CODE','BIS_PRIMARY_RATE_TYPE','BIS_SECONDARY_CURRENCY_CODE',
1527 'BIS_SECONDARY_RATE_TYPE','BIS_ENTERPRISE_CALENDAR','BIS_PERIOD_TYPE','BIS_GLOBAL_START_DATE',
1528 'PJI_GLOBAL_START_DATE_OVERRIDE')
1529 AND VAL.PROFILE_OPTION_ID = OP.PROFILE_OPTION_ID
1530 AND VAL.APPLICATION_ID = OP.APPLICATION_ID
1531 AND LEVEL_ID = 10001
1532 AND LEVEL_VALUE = 0;
1533
1534 FOR I IN 1..prof_opt_tbl.COUNT LOOP
1535 if prof_opt_tbl(i) = 'BIS_PRIMARY_CURRENCY_CODE' then
1536 l_bis_pri_curr_code := prof_val_tbl(i);
1537 end if ;
1538 if prof_opt_tbl(i) = 'BIS_PRIMARY_RATE_TYPE' then
1539 l_bis_pri_rate_type := prof_val_tbl(i);
1540 end if ;
1541 if prof_opt_tbl(i) = 'BIS_SECONDARY_CURRENCY_CODE' then
1542 l_bis_sec_curr_code := prof_val_tbl(i);
1543 end if ;
1544 if prof_opt_tbl(i) = 'BIS_SECONDARY_RATE_TYPE' then
1545 l_bis_sec_rate_type := prof_val_tbl(i);
1546 end if ;
1547 if prof_opt_tbl(i) = 'BIS_ENTERPRISE_CALENDAR' then
1548 l_bis_ent_calendar := prof_val_tbl(i);
1549 end if ;
1550 if prof_opt_tbl(i) = 'BIS_PERIOD_TYPE' then
1551 l_bis_period_type := prof_val_tbl(i);
1552 end if ;
1553 if prof_opt_tbl(i) = 'BIS_GLOBAL_START_DATE' then
1554 l_bis_global_start_date := prof_val_tbl(i);
1555 end if ;
1556 if prof_opt_tbl(i) = 'PJI_GLOBAL_START_DATE_OVERRIDE' then
1557 l_pji_global_start_date := prof_val_tbl(i);
1558 end if ;
1559 end loop;
1560 EXCEPTION WHEN NO_DATA_FOUND then
1561 l_err_msg := 'BIS Setup Options are not available';
1562 l_prm_miss := 1;
1563 raise;
1564 END;
1565 -- For Bis Setup Options Prompts or captions
1566 BEGIN
1567 prof_opt_tbl := SYSTEM.pa_varchar2_240_tbl_type();
1568 prof_opt_tbl := SYSTEM.pa_varchar2_240_tbl_type();
1569
1570 SELECT lookup_code , RPAD(MEANING,45,' ')||': ' BULK COLLECT
1571 INTO prof_opt_tbl,prof_val_tbl
1572 FROM pji_lookups
1573 WHERE lookup_type = 'PJI_CHK_BIS_PARAMS'
1574 and lookup_code in ( 'BIS_PR_CURR',
1575 'BIS_PR_RATE',
1576 'BIS_SE_CURR',
1577 'BIS_SE_RATE',
1578 'BIS_ENT_CAL',
1579 'BIS_GLO_ST_DT',
1580 'BIS_PD_TYPE',
1581 'PJI_GLO_ST_DT');
1582
1583 FOR I IN 1..prof_opt_tbl.COUNT LOOP
1584 if prof_opt_tbl(i) = 'BIS_PR_CURR' then
1585 l_p_bis_pri_curr_code := prof_val_tbl(i);
1586 end if ;
1587 if prof_opt_tbl(i) = 'BIS_PR_RATE' then
1588 l_p_bis_pri_rate_type := prof_val_tbl(i);
1589 end if ;
1590 if prof_opt_tbl(i) = 'BIS_SE_CURR' then
1591 l_p_bis_sec_curr_code := prof_val_tbl(i);
1592 end if ;
1593 if prof_opt_tbl(i) = 'BIS_SE_RATE' then
1594 l_p_bis_sec_rate_type := prof_val_tbl(i);
1595 end if ;
1596 if prof_opt_tbl(i) = 'BIS_ENT_CAL' then
1597 l_p_bis_ent_calendar := prof_val_tbl(i);
1598 end if ;
1599 if prof_opt_tbl(i) = 'BIS_PD_TYPE' then
1600 l_p_bis_period_type := prof_val_tbl(i);
1601 end if ;
1602 if prof_opt_tbl(i) = 'BIS_GLO_ST_DT' then
1603 l_p_bis_global_start_date := prof_val_tbl(i);
1604 end if ;
1605 if prof_opt_tbl(i) = 'PJI_GLO_ST_DT' then
1606 l_p_pji_global_start_date := prof_val_tbl(i);
1607 end if ;
1608 end loop;
1609 EXCEPTION WHEN NO_DATA_FOUND THEN
1610 l_err_msg := 'BIS Profile Options prompts are not available in lookup table';
1611 l_prm_miss := 1;
1612 raise;
1613 END;
1614 --- For checking the missing mandatory or optional setup options
1615 if l_bis_pri_curr_code is NULL or l_bis_pri_rate_type is NULL
1616 or l_bis_ent_calendar is NULL or l_bis_period_type is NULL
1617 or l_bis_global_start_date is NULL then
1618 l_prm_miss := 2;
1619 l_bis_note := 1;
1620 end if;
1621 if l_bis_sec_curr_code is NULL or l_bis_sec_rate_type is NULL
1622 or l_pji_global_start_date is NULL then
1623 if nvl(l_prm_miss,0) = 2 then
1624 l_prm_miss := 4;
1625 l_bis_note := 1;
1626 else
1627 l_prm_miss := 3;
1628 l_bis_note := 1;
1629 end if;
1630 end if;
1631
1632 -- For Getting the headings of the Audit report
1633 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PJP_BIS_REPORT_TEXT'),
1634 FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PJP_BIS_REPORT_HEAD1'),
1635 FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PJP_BIS_REPORT_HEAD2'),
1636 FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PJP_BIS_REPORT_HEAD3'),
1637 FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PJP_BIS_REPORT_HEAD4'),
1638 FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PJP_BIS_REPORT_HEAD5'),
1639 FND_MESSAGE.GET_STRING('PJI','PJI_PJP_PERF_SETUP'),
1640 FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PJP_BIS_SETUP'),
1641 FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PJP_SETUP'),
1642 FND_MESSAGE.GET_STRING('PJI','PJI_PJP_CUR_PERIOD'),
1643 FND_MESSAGE.GET_STRING('PJI','PJI_CURR_REP_DEF')
1644 INTO l_pji_report_msg,l_pji_head1,l_pji_head2,l_pji_head3,l_pji_head4,l_pji_head5,
1645 l_pji_foot1,l_pji_foot2,l_pji_foot3,l_pji_foot4,l_p_curr_rep_not
1646 FROM dual;
1647
1648 select
1649 RPAD(l_separator,Length(l_separator)+Length(l_pji_report_msg),'*'),
1650 RPAD(l_pji_line2,length(l_pji_head2),'-'),
1651 RPAD(l_pji_line3,length(l_pji_head3),'-'),
1652 RPAD(l_pji_line4,length(l_pji_head4),'-'),
1653 RPAD(l_pji_line5,length(l_pji_head5),'-')
1654 into
1655 l_separator,l_pji_line2,l_pji_line3,l_pji_line4,l_pji_line5
1656 from dual;
1657
1658 -- Audit Report printing starts
1659 l_pji_report_msg := ' '||l_pji_report_msg;
1660
1661
1662 pji_utils.write2out(l_newline || l_pji_report_msg || l_newline || l_separator || l_newline
1663 ||l_pji_head1||l_newline||l_newline||l_pji_head2||l_newline||l_pji_line2||l_newline);
1664
1665 pji_utils.write2out(l_p_bis_pri_curr_code||l_bis_pri_curr_code||l_newline);
1666 pji_utils.write2out(l_p_bis_pri_rate_type||l_bis_pri_rate_type||l_newline);
1667 pji_utils.write2out(l_p_bis_sec_curr_code||l_bis_sec_curr_code||l_newline);
1668 pji_utils.write2out(l_p_bis_sec_rate_type||l_bis_sec_rate_type||l_newline);
1669 pji_utils.write2out(l_p_bis_ent_calendar||l_bis_ent_calendar||l_newline);
1670 pji_utils.write2out(l_p_bis_period_type||l_bis_period_type||l_newline);
1671 pji_utils.write2out(l_p_bis_global_start_date||l_bis_global_start_date||l_newline);
1672 pji_utils.write2out(l_p_pji_global_start_date||l_pji_global_start_date||l_newline);
1673
1674 -- For PJP Setup options
1675 BEGIN
1676 SELECT
1677 GLOBAL_CURR2_FLAG GLB_CURR_FLAG, -- Secondary global Currency (Optional)
1678 TXN_CURR_FLAG TXN_CURR_FLAG, -- Transaction Currency (Optional)
1679 PLANAMT_CONV_DATE PLANAMT_CONV_DATE, -- Planned amount conversion (Mandatory)
1680 PLANAMT_ALLOC_METHOD PLANAMT_ALLOC_METHOD, -- Planning amount allocation (Mandatory)
1681 CURR_REP_PA_PERIOD CURR_REP_PA_PERIOD, -- GL periods (Mandatory)-
1682 CURR_REP_GL_PERIOD CURR_REP_GL_PERIOD, -- PA periods (Mandatory)-
1683 CURR_REP_ENT_PERIOD CURR_REP_ENT_PERIOD -- Enterprise (global) periods (Mandatory)
1684 INTO
1685 l_glb_curr_flag,
1686 l_txn_curr_flag,
1687 l_planamt_conv_date,
1688 l_planamt_alloc_method,
1689 l_curr_rep_pa_period,
1690 l_curr_rep_gl_period,
1691 l_curr_rep_ent_period
1692 FROM PJI_SYSTEM_SETTINGS;
1693
1694 --- IF Periods are specific then getting the specific values
1695 /* If l_curr_rep_pa_period = 'SPECIFIC' or l_curr_rep_gl_period = 'SPECIFIC'
1696 or l_curr_rep_ent_period = 'SPECIFIC' then 5207578 */
1697 begin
1698
1699 /*SELECT INFO.PA_CURR_REP_PERIOD PROJECT_PERIOD_NAME, -- PA periods (Mandatory)-
1700 INFO.GL_CURR_REP_PERIOD FISCAL_PERIOD_NAME, -- GL periods (Mandatory)-
1701 PARAMS.VALUE GLOBAL_PERIOD_NAME -- Enterprise (global) periods (Mandatory)
1702 INTO
1703 l_sp_curr_rep_pa_period,
1704 l_sp_curr_rep_gl_period,
1705 l_sp_curr_rep_ent_period
1706 FROM PJI_ORG_EXTR_INFO INFO,
1707 PJI_SYSTEM_PARAMETERS PARAMS
1708 WHERE 1=1
1709 AND ORG_ID = NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO'),1,1),
1710 ' ',NULL,SUBSTR(USERENV('CLIENT_INFO'),1,10))),-99)
1711 AND PARAMS.NAME = 'PJI_PJP_ENT_CURR_REP_PERIOD'; */
1712 SELECT ORG.name,
1713 DECODE(l_curr_rep_pa_period,'SPECIFIC',INFO.PA_CURR_REP_PERIOD, Derive_curr_rep_Info(ORG_ID,'P',l_curr_rep_pa_period)) PROJECT_PERIOD_NAME, -- PA periods (Mandatory)-
1714 DECODE(l_curr_rep_gl_period,'SPECIFIC',INFO.GL_CURR_REP_PERIOD, Derive_curr_rep_Info(ORG_ID,'G',l_curr_rep_gl_period)) FISCAL_PERIOD_NAME, -- GL periods (Mandatory)
1715 DECODE(l_curr_rep_ent_period,'SPECIFIC',pji_utils.get_parameter('PJI_PJP_ENT_CURR_REP_PERIOD'), Derive_curr_rep_Info(ORG_ID,'E',l_curr_rep_ent_period)) GLOBAL_PERIOD_NAME -- Enterprise (global) periods (Mandatory)
1716 BULK COLLECT INTO
1717 l_sp_curr_rep_org_tbl,
1718 l_sp_curr_rep_pa_period_tbl,
1719 l_sp_curr_rep_gl_period_tbl,
1720 l_sp_curr_rep_ent_period_tbl
1721 FROM PJI_ORG_EXTR_INFO INFO,
1722 HR_ALL_ORGANIZATION_UNITS_VL ORG
1723 WHERE ORGANIZATION_ID = ORG_ID
1724 ORDER BY ORG.NAME;
1725 EXCEPTION WHEN NO_DATA_FOUND then
1726 Null;
1727 END;
1728 -- end if; /* 5207578 */
1729 EXCEPTION WHEN NO_DATA_FOUND then
1730 l_err_msg := 'PJP Setup Options are not available';
1731 l_prm_miss := 1;
1732 return;
1733 END;
1734
1735 -- For PJP setup options prompt or caption
1736 BEGIN
1737 prof_opt_tbl := SYSTEM.pa_varchar2_240_tbl_type();
1738 prof_opt_tbl := SYSTEM.pa_varchar2_240_tbl_type();
1739
1740 SELECT lookup_code , RPAD(MEANING,45,' ')||': ' BULK COLLECT
1741 INTO prof_opt_tbl,prof_val_tbl
1742 FROM pji_lookups
1743 WHERE lookup_type = 'PJI_CHK_PJP_SET_PARAMS'
1744 and lookup_code in ( 'PJP_SE_GLO_CURR',
1745 'PJP_TXN_CURR',
1746 'PJP_PLN_AMT_ALC',
1747 'PJP_PLN_AMT_CON',
1748 'PJP_CURR_REP_PA_PD',
1749 'PJP_CURR_REP_GL_PD',
1750 'PJP_CURR_REP_EN_PD');
1751
1752
1753
1754 FOR I IN 1..prof_opt_tbl.COUNT LOOP
1755 if prof_opt_tbl(i) = 'PJP_SE_GLO_CURR' then
1756 l_p_glb_curr_flag := prof_val_tbl(i);
1757 end if ;
1758 if prof_opt_tbl(i) = 'PJP_TXN_CURR' then
1759 l_p_txn_curr_flag := prof_val_tbl(i);
1760 end if ;
1761 if prof_opt_tbl(i) = 'PJP_PLN_AMT_ALC' then
1762 l_p_planamt_alloc_method := prof_val_tbl(i);
1763 end if ;
1764 if prof_opt_tbl(i) = 'PJP_PLN_AMT_CON' then
1765 l_p_planamt_conv_date := prof_val_tbl(i);
1766 end if ;
1767 if prof_opt_tbl(i) = 'PJP_CURR_REP_PA_PD' then
1768 l_p_curr_rep_pa_period := prof_val_tbl(i);
1769 end if ;
1770 if prof_opt_tbl(i) = 'PJP_CURR_REP_GL_PD' then
1771 l_p_curr_rep_gl_period := prof_val_tbl(i);
1772 end if ;
1773 if prof_opt_tbl(i) = 'PJP_CURR_REP_EN_PD' then
1774 l_p_curr_rep_ent_period := prof_val_tbl(i);
1775 end if ;
1776 end loop;
1777 EXCEPTION WHEN NO_DATA_FOUND THEN
1778 l_err_msg := 'PJP Setup Options prompts are not available in lookup table';
1779 l_prm_miss := 1;
1780 return;
1781 END;
1782 -- For checking the missing options mandatory or optional
1783 if (l_planamt_conv_date is null or l_planamt_alloc_method is null or
1784 l_curr_rep_pa_period is null or l_curr_rep_gl_period is null or
1785 l_curr_rep_ent_period is null) then
1786 if l_prm_miss < 2 then
1787 l_prm_miss := 2;
1788 end if;
1789 l_pjp_note := 1;
1790 end if;
1791 if (l_glb_curr_flag is null or l_txn_curr_flag is null ) then
1792 l_pjp_note := 1;
1793 if l_prm_miss < 3 then
1794 if nvl(l_prm_miss,0) = 2 then
1795 l_prm_miss := 4;
1796 else
1797 l_prm_miss := 3;
1798 end if;
1799 end if;
1800 end if;
1801 --- PJP Report printing
1802 pji_utils.write2out(l_newline ||l_pji_head3||l_newline||l_pji_line3||l_newline);
1803 pji_utils.write2out(l_p_glb_curr_flag||PJI_LOOKUP_VALUE('PJI_YES_NO',l_glb_curr_flag)||l_newline);
1804 pji_utils.write2out(l_p_txn_curr_flag||PJI_LOOKUP_VALUE('PJI_YES_NO',l_txn_curr_flag)||l_newline);
1805 pji_utils.write2out(l_p_planamt_conv_date||PJI_LOOKUP_VALUE('PJI_PLN_AMT_CON',l_planamt_conv_date)||l_newline);
1806 pji_utils.write2out(l_p_planamt_alloc_method||PJI_LOOKUP_VALUE('PJI_PLN_AMT_ALC',l_planamt_alloc_method)||l_newline);
1807 pji_utils.write2out(l_newline||FND_MESSAGE.GET_STRING('PJI','PJI_CURR_REP_PD')||l_newline);
1808 pji_utils.write2out(l_p_curr_rep_pa_period||PJI_LOOKUP_VALUE('PJI_CURR_REP_PD',l_curr_rep_pa_period)||l_newline);
1809 pji_utils.write2out(l_p_curr_rep_gl_period||PJI_LOOKUP_VALUE('PJI_CURR_REP_PD',l_curr_rep_gl_period)||l_newline);
1810 pji_utils.write2out(l_p_curr_rep_ent_period||PJI_LOOKUP_VALUE('PJI_CURR_REP_PD',l_curr_rep_ent_period)||l_newline||l_newline||l_newline);
1811 --- Organization wise current reporting period table
1812 /* If l_curr_rep_pa_period = 'SPECIFIC' or l_curr_rep_gl_period = 'SPECIFIC'
1813 or l_curr_rep_ent_period = 'SPECIFIC' then 5207578 */
1814 pji_utils.write2out(FND_MESSAGE.GET_STRING('PJI','PJI_CURR_REP_TBL_H')||l_newline);
1815 pji_utils.write2out(RPAD(l_pji_tline,81,'-')||l_newline);
1816 pji_utils.write2out('|'||RPAD(FND_MESSAGE.GET_STRING('PJI','PJI_CURR_REP_OU'),27,' ')||'|'||RPAD(l_p_curr_rep_pa_period,16,' ')||'|'||
1817 RPAD(l_p_curr_rep_gl_period,16,' ')||'|'||RPAD(l_p_curr_rep_ent_period,17,' ')||'|'||l_newline);
1818 pji_utils.write2out(RPAD(l_pji_tline,81,'-')||l_newline);
1819 For i in 1..l_sp_curr_rep_org_tbl.count loop
1820 pji_utils.write2out('|'||RPAD(l_sp_curr_rep_org_tbl(i),27,' ')||'|'||RPAD(nvl(l_sp_curr_rep_pa_period_tbl(i),l_p_curr_rep_not),16,' ')||'|'||
1821 RPAD(nvl(l_sp_curr_rep_gl_period_tbl(i),l_p_curr_rep_not),16,' ')||'|'||RPAD(nvl(l_sp_curr_rep_ent_period_tbl(i),l_p_curr_rep_not),17,' ')||'|'||l_newline);
1822 for j in 1..ceil(length(l_sp_curr_rep_org_tbl(i))/27)-1 loop
1823 pji_utils.write2out('|'||RPAD(substr(l_sp_curr_rep_org_tbl(i),(j*27)+1,j+1*27),27,' ')||'|'||RPAD(' ',16,' ')||'|'||
1824 RPAD(' ',16,' ')||'|'||RPAD(' ',17,' ')||'|'||l_newline);
1825 end loop;
1826
1827 if l_sp_curr_rep_pa_period_tbl(i) is null or l_sp_curr_rep_gl_period_tbl(i) is null
1828 or l_sp_curr_rep_ent_period_tbl(i) is null then
1829 l_sp_note := 1;
1830 if l_prm_miss < 2 then
1831 l_prm_miss := 2;
1832 end if;
1833 end if;
1834 end loop;
1835 pji_utils.write2out(RPAD(l_pji_tline,81,'-')||l_newline);
1836
1837 -- end if; /* 5207578 */
1838
1839
1840 -- Exception reporting for all setup missing
1841 -- Mandatory Setup
1842 if l_prm_miss = 2 or l_prm_miss = 4 then
1843 pji_utils.write2out(l_newline ||l_pji_head4||l_newline||l_pji_line4||l_newline);
1844 if l_bis_pri_curr_code is NULL then
1845 pji_utils.write2out(l_p_bis_pri_curr_code||l_newline);
1846 end if;
1847 if l_bis_pri_rate_type is NULL then
1848 pji_utils.write2out(l_p_bis_pri_rate_type||l_newline);
1849 end if;
1850 if l_bis_ent_calendar is NULL then
1851 pji_utils.write2out(l_p_bis_ent_calendar||l_newline);
1852 end if;
1853 if l_bis_period_type is NULL then
1854 pji_utils.write2out(l_p_bis_period_type||l_newline);
1855 end if;
1856 if l_bis_global_start_date is NULL then
1857 pji_utils.write2out(l_p_bis_global_start_date||l_newline);
1858 end if;
1859 if l_planamt_conv_date is NULL then
1860 pji_utils.write2out(l_p_planamt_conv_date||l_newline);
1861 end if;
1862 if l_planamt_alloc_method is NULL then
1863 pji_utils.write2out(l_p_planamt_alloc_method||l_newline);
1864 end if;
1865 --- For Current reporting periods
1866 if l_curr_rep_pa_period is NULL or l_curr_rep_gl_period is NULL or
1867 l_curr_rep_ent_period is NULL then
1868 pji_utils.write2out(l_newline||FND_MESSAGE.GET_STRING('PJI','PJI_CURR_REP_PD')||l_newline);
1869 end if;
1870 if l_curr_rep_pa_period is NULL then
1871 pji_utils.write2out(l_p_curr_rep_pa_period||l_newline);
1872 end if;
1873 if l_curr_rep_gl_period is NULL then
1874 pji_utils.write2out(l_p_curr_rep_gl_period||l_newline);
1875 end if;
1876 if l_curr_rep_ent_period is NULL then
1877 pji_utils.write2out(l_p_curr_rep_ent_period||l_newline);
1878 end if;
1879 --- For Current reporting period when SPECIFIC
1880 if l_sp_note = 1 then
1881 pji_utils.write2out(l_newline||FND_MESSAGE.GET_STRING('PJI','PJI_CURR_REP_MISS')||l_newline);
1882 end if;
1883
1884 end if;
1885 -- Optional Set up
1886 if l_prm_miss = 3 or l_prm_miss = 4 then
1887 pji_utils.write2out(l_newline ||l_pji_head5||l_newline||l_pji_line5||l_newline);
1888 if l_bis_sec_curr_code is NULL then
1889 pji_utils.write2out(l_p_bis_sec_curr_code||l_newline);
1890 end if;
1891 if l_bis_sec_rate_type is NULL then
1892 pji_utils.write2out(l_p_bis_sec_rate_type||l_newline);
1893 end if;
1894 if l_pji_global_start_date is NULL then
1895 pji_utils.write2out(l_p_pji_global_start_date||l_newline);
1896 end if;
1897 if l_glb_curr_flag is null then
1898 pji_utils.write2out(l_p_glb_curr_flag||l_newline);
1899 end if;
1900 if l_txn_curr_flag is null then
1901 pji_utils.write2out(l_p_txn_curr_flag||l_newline);
1902 end if;
1903 end if;
1904 --- Notes at the end of the report if any thing missing
1905 if l_prm_miss > 1 then
1906 pji_utils.write2out(l_newline||l_pji_foot1||l_newline);
1907 if l_bis_note > 0 then
1908 pji_utils.write2out(l_pji_foot2||l_newline);
1909 end if ;
1910 if l_pjp_note > 0 then
1911 pji_utils.write2out(l_pji_foot3||l_newline);
1912 end if ;
1913 if l_sp_note > 0 then
1914 pji_utils.write2out(l_pji_foot4||l_newline);
1915 end if ;
1916 end if;
1917 Exception when others then
1918 pji_utils.write2out('Error: '||l_err_msg);
1919 END;
1920
1921 -- -----------------------------------------------------
1922 -- Function PJI_LOOKUP_VALUE
1923 -- For getting the lookup values in project performance setups Audit report
1924 -- History
1925 -- 14-MAR-2006 DEGUPTA Created
1926 -- -----------------------------------------------------
1927
1928
1929 FUNCTION PJI_LOOKUP_VALUE (p_lookup_type VARCHAR2,p_lookup_code VARCHAR2)
1930 return VARCHAR2
1931 IS
1932 l_meaning varchar2(100);
1933 BEGIN
1934 IF p_lookup_code is not null then
1935 SELECT meaning into l_meaning
1936 FROM pji_lookups
1937 WHERE lookup_type = p_lookup_type
1938 and lookup_code = p_lookup_code;
1939 return l_meaning;
1940 else
1941 return NULL;
1942 end if;
1943 EXCEPTION WHEN OTHERS THEN
1944 return p_lookup_code;
1945 end;
1946
1947 -- -----------------------------------------------------
1948 -- Function Is_plantype_upgrade_pending
1949 -- For checkingif plantype upgrad epending for the program
1950 -- History
1951 -- 07-APR-2006 AJDAS Created
1952 -- -----------------------------------------------------
1953
1954
1955 FUNCTION Is_plantype_upgrade_pending (p_project_id IN NUMBER)
1956 return VARCHAR2
1957 IS
1958 l_return varchar2(1):='N';
1959 BEGIN
1960 if (PJI_UTILS.GET_PARAMETER('PJI_PTC_UPGRADE') = 'P') then
1961
1962 select 'Y'
1963 into l_return
1964 from dual
1965 where exists (SELECT 1
1966 FROM pa_proj_element_versions pa
1967 ,pa_proj_element_versions pap
1968 ,pa_pji_proj_events_log log
1969 WHERE pa.project_id=p_project_id
1970 and pa.prg_group=pap.prg_group
1971 and pa.OBJECT_TYPE=pap.OBJECT_TYPE
1972 and pa.OBJECT_TYPE='PA_STRUCTURES'
1973 and log.event_object=to_char(pap.project_id)
1974 and log.event_type='PLANTYPE_UPG'
1975 );
1976 end if;
1977 return l_return;
1978 EXCEPTION WHEN NO_DATA_FOUND THEN
1979 return l_return;
1980 WHEN OTHERS THEN
1981 return l_return;
1982
1983 END;
1984
1985 -- -----------------------------------------------------
1986 -- Function Fin_Summ_Upgrade_Status
1987 -- For checking the status of financial summary upgrade
1988 -- History
1989 -- 23-JUL-2006 PSCHANDR Created
1990 -- -----------------------------------------------------
1991
1992 FUNCTION Fin_Summ_Upgrade_Status
1993 return VARCHAR2
1994 IS
1995 l_status VARCHAR2(1);
1996 l_count NUMBER(10);
1997 begin
1998 select PJI_UTILS.GET_PARAMETER('PJI_FPM_UPGRADE') into l_status from dual;
1999 IF l_status = 'P' THEN
2000 select count(1) into l_count from PJI_SYSTEM_CONFIG_HIST
2001 where PROCESS_NAME = 'STAGE3'
2002 and END_DATE is null and RUN_TYPE = 'CLEANALL';
2003
2004 if l_count > 0 then
2005 return 'R';
2006 else
2007 return 'E';
2008 end if;
2009 ELSIF l_status is null THEN
2010 return 'P';
2011 ELSE
2012 return 'C';
2013
2014 END IF;
2015
2016 EXCEPTION
2017 WHEN NO_DATA_FOUND THEN
2018 return 'P';
2019 WHEN OTHERS THEN
2020 return 'P';
2021
2022 END;
2023
2024 -- ******************************************************
2025 -- Private procedures and functions
2026 -- ******************************************************
2027
2028 procedure init_settings_cache is
2029 begin
2030
2031 select *
2032 into g_pji_settings
2033 from pji_system_settings;
2034
2035 g_settings_init_flag := TRUE;
2036
2037 end init_settings_cache;
2038
2039 procedure init_session_cache is
2040 begin
2041
2042 select sid, osuser
2043 into g_session_sid, g_session_osuser
2044 from v$session
2045 where audsid = userenv('SESSIONID');
2046
2047 g_session_user_id := fnd_global.user_id;
2048 g_pa_debug_mode := fnd_profile.value('PA_DEBUG_MODE');
2049 g_debug_level := nvl(FND_PROFILE.VALUE('PJI_DEBUG_LEVEL'), 5);
2050
2051 end;
2052
2053
2054
2055 end PJI_UTILS;