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