[Home] [Help]
PACKAGE BODY: APPS.PJI_AUDIT_RPT
Source
1 PACKAGE BODY PJI_AUDIT_RPT AS
2 /* $Header: PJIUT05B.pls 115.6 2003/12/19 03:31:08 svermett noship $ */
3
4 g_cnt_set_up_err NUMBER := 0;
5
6 /*
7 This API checks for all the business intelligence
8 set up parameters that are needed for running
9 project intelligence application.
10 */
11 PROCEDURE CHK_BIS_SET_UP
12 IS
13 l_period_set_name VARCHAR2(15) := NULL;
14 l_global_st_date DATE := NULL;
15 l_period_type VARCHAR2(15) := NULL;
16 l_currency_code VARCHAR2(15) := NULL;
17 l_rate_type VARCHAR2(30) := NULL;
18 l_start_dt_of_week VARCHAR2(30) := NULL;
19
20 l_period_set_name_tkn VARCHAR2(50);
21 l_global_st_date_tkn VARCHAR2(50);
22 l_period_type_tkn VARCHAR2(50);
23 l_currency_code_tkn VARCHAR2(50);
24 l_rate_type_tkn VARCHAR2(50);
25 l_start_dt_of_week_tkn VARCHAR2(50);
26
27 l_cnt_set_up_err NUMBER := 0;
28 l_bis_param_name_tbl V_TYPE_TAB;
29 l_no_of_bis_params NUMBER := 6;
30
31 l_all_msg_text VARCHAR2(200);
32 l_param_msg VARCHAR2(30);
33 l_param_msg_text VARCHAR2(400):='';
34 l_bis_msg_text VARCHAR2(200);
35 l_newline varchar2(10) := '
36 ';
37
38 BEGIN
39
40 SELECT BIS_COMMON_PARAMETERS.GET_PERIOD_SET_NAME
41 INTO l_period_set_name
42 FROM dual;
43
44 SELECT PJI_UTILS.GET_EXTRACTION_START_DATE
45 INTO l_global_st_date
46 FROM dual;
47
48 SELECT BIS_COMMON_PARAMETERS.GET_PERIOD_TYPE
49 INTO l_period_type
50 FROM dual;
51
52 SELECT BIS_COMMON_PARAMETERS.GET_CURRENCY_CODE
53 INTO l_currency_code
54 FROM dual;
55
56 SELECT BIS_COMMON_PARAMETERS.GET_RATE_TYPE
57 INTO l_rate_type
58 FROM dual;
59
60 SELECT BIS_COMMON_PARAMETERS.GET_START_DAY_OF_WEEK_ID
61 INTO l_start_dt_of_week
62 FROM dual;
63
64 l_bis_param_name_tbl := V_TYPE_TAB();
65 l_bis_param_name_tbl.DELETE;
66
67 IF l_period_set_name IS NULL THEN
68 l_cnt_set_up_err := l_cnt_set_up_err + 1;
69
70 SELECT meaning
71 INTO l_period_set_name_tkn
72 FROM pji_lookups
73 WHERE lookup_type = 'PJI_CHK_BIS_PARAMS'
74 and lookup_code = 'BIS_ENT_CAL';
75
76 l_bis_param_name_tbl.EXTEND;
77 l_bis_param_name_tbl(l_bis_param_name_tbl.COUNT) := l_period_set_name_tkn;
78 END IF;
79
80 IF l_global_st_date IS NULL THEN
81 l_cnt_set_up_err := l_cnt_set_up_err + 1;
82
83 SELECT meaning
84 INTO l_global_st_date_tkn
85 FROM pji_lookups
86 WHERE lookup_type = 'PJI_CHK_BIS_PARAMS'
87 and lookup_code = 'BIS_GLO_ST_DT';
88
89 l_bis_param_name_tbl.EXTEND;
90 l_bis_param_name_tbl(l_bis_param_name_tbl.COUNT) := l_global_st_date_tkn;
91 END IF;
92
93 IF l_period_type IS NULL THEN
94 l_cnt_set_up_err := l_cnt_set_up_err + 1;
95
96 SELECT meaning
97 INTO l_period_type_tkn
98 FROM pji_lookups
99 WHERE lookup_type = 'PJI_CHK_BIS_PARAMS'
100 and lookup_code = 'BIS_PD_TYPE';
101
102 l_bis_param_name_tbl.EXTEND;
103 l_bis_param_name_tbl(l_bis_param_name_tbl.COUNT) := l_period_type_tkn;
104 END IF;
105
106 IF l_currency_code IS NULL THEN
107 l_cnt_set_up_err := l_cnt_set_up_err + 1;
108
109 SELECT meaning
110 INTO l_currency_code_tkn
111 FROM pji_lookups
112 WHERE lookup_type = 'PJI_CHK_BIS_PARAMS'
113 and lookup_code = 'BIS_PR_CURR';
114
115 l_bis_param_name_tbl.EXTEND;
116 l_bis_param_name_tbl(l_bis_param_name_tbl.COUNT) := l_currency_code_tkn;
117 END IF;
118
119 IF l_rate_type IS NULL THEN
120 l_cnt_set_up_err := l_cnt_set_up_err + 1;
121
122 SELECT meaning
123 INTO l_rate_type_tkn
124 FROM pji_lookups
125 WHERE lookup_type = 'PJI_CHK_BIS_PARAMS'
126 and lookup_code = 'BIS_PR_RATE';
127
128 l_bis_param_name_tbl.EXTEND;
129 l_bis_param_name_tbl(l_bis_param_name_tbl.COUNT) := l_rate_type_tkn;
130 END IF;
131
132 IF l_start_dt_of_week IS NULL THEN
133 l_cnt_set_up_err := l_cnt_set_up_err + 1;
134
135 SELECT meaning
136 INTO l_start_dt_of_week_tkn
137 FROM pji_lookups
138 WHERE lookup_type = 'PJI_CHK_BIS_PARAMS'
139 and lookup_code = 'BIS_ST_DT_WK';
140
141 l_bis_param_name_tbl.EXTEND;
142 l_bis_param_name_tbl(l_bis_param_name_tbl.COUNT) := l_start_dt_of_week_tkn;
143 END IF;
144
145 IF (l_cnt_set_up_err > 0) THEN
146 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_ALL_PARAM_TEXT')
147 INTO l_all_msg_text
148 FROM dual;
149
150 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PARAM_NAME')
151 INTO l_param_msg
152 FROM dual;
153
154 FOR i in l_bis_param_name_tbl.FIRST.. l_bis_param_name_tbl.LAST
155 LOOP
156 l_param_msg_text := l_param_msg_text || l_param_msg || ' ' || l_bis_param_name_tbl(i) || l_newline;
157 END LOOP;
158
159 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_BIS_PARAM_TEXT')
160 INTO l_bis_msg_text
161 FROM dual;
162
163 pji_utils.write2out(l_all_msg_text || l_newline || l_param_msg_text || l_bis_msg_text || l_newline);
164
165 END IF;
166
167 g_cnt_set_up_err := g_cnt_set_up_err + l_cnt_set_up_err;
168
169 END CHK_BIS_SET_UP;
170
171 /*
172 This API checks for all the project intelligence
173 set up parameters that are needed for running
174 project intelligence application.
175 */
176 PROCEDURE CHK_PJI_SET_UP
177 IS
178 l_org_structure_id NUMBER:= NULL;
179 l_org_structure_ver_id NUMBER:= NULL;
180
181 l_org_structure_id_tkn VARCHAR2(50);
182 l_org_structure_ver_tkn VARCHAR2(50);
183
184 l_cnt_set_up_err NUMBER := 0;
185 l_pji_param_name_tbl V_TYPE_TAB;
186
187 l_all_msg_text VARCHAR2(200);
188 l_param_msg VARCHAR2(30);
189 l_param_msg_text VARCHAR2(400):='';
190 l_pji_msg_text VARCHAR2(200);
191 l_newline varchar2(10) := '
192 ';
193
194 BEGIN
195
196 SELECT organization_structure_id,
197 org_structure_version_id
198 INTO l_org_structure_id,
199 l_org_structure_ver_id
200 FROM pji_system_settings;
201
202 l_pji_param_name_tbl := V_TYPE_TAB();
203 l_pji_param_name_tbl.DELETE;
204
205 IF l_org_structure_id IS NULL THEN
206 l_cnt_set_up_err := l_cnt_set_up_err + 1;
207
208 SELECT meaning
209 INTO l_org_structure_id_tkn
210 FROM pji_lookups
211 WHERE lookup_type = 'PJI_CHK_PJI_SET_PARAMS'
212 and lookup_code = 'ORG_STRUC';
213
214 l_pji_param_name_tbl.EXTEND;
215 l_pji_param_name_tbl(l_pji_param_name_tbl.COUNT) := l_org_structure_id_tkn;
216 END IF;
217
218 IF l_org_structure_ver_id IS NULL THEN
219 l_cnt_set_up_err := l_cnt_set_up_err + 1;
220
221 SELECT meaning
222 INTO l_org_structure_ver_tkn
223 FROM pji_lookups
224 WHERE lookup_type = 'PJI_CHK_PJI_SET_PARAMS'
225 and lookup_code = 'ORG_ST_VER';
226
227 l_pji_param_name_tbl.EXTEND;
228 l_pji_param_name_tbl(l_pji_param_name_tbl.COUNT) := l_org_structure_ver_tkn;
229 END IF;
230
231 IF (l_cnt_set_up_err > 0) THEN
232 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_ALL_PARAM_TEXT')
233 INTO l_all_msg_text
234 FROM dual;
235
236 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PARAM_NAME')
237 INTO l_param_msg
238 FROM dual;
239
240 FOR i in l_pji_param_name_tbl.FIRST.. l_pji_param_name_tbl.LAST
241 LOOP
242 l_param_msg_text := l_param_msg_text || l_param_msg || ' ' || l_pji_param_name_tbl(i) || l_newline;
243 END LOOP;
244
245 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PJI_PARAM_TEXT')
246 INTO l_pji_msg_text
247 FROM dual;
248
249 pji_utils.write2out(l_newline || l_all_msg_text || l_newline || l_param_msg_text || l_pji_msg_text || l_newline);
250
251 END IF;
252
253 g_cnt_set_up_err := g_cnt_set_up_err + l_cnt_set_up_err;
254
255 END CHK_PJI_SET_UP;
256
257 /*
258 This API checks for all the organization and
259 time/calendar dimensions that are needed for
260 running project intelligence application.
261 */
262 PROCEDURE CHK_ORG_TIME_CAL_DIM
263 IS
264 l_time_cal_dim NUMBER:= NULL;
265 l_org_dim NUMBER:= NULL;
266
267 l_cnt_set_up_err NUMBER := 0;
268 l_time_cal_msg_text VARCHAR2(200);
269 l_org_msg_text VARCHAR2(200);
270 l_dim_msg_text VARCHAR2(600):='';
271 l_newline varchar2(10) := '
272 ';
273
274 BEGIN
275
276 BEGIN
277 SELECT 1
278 INTO l_time_cal_dim
279 FROM dual
280 WHERE EXISTS (
281 SELECT 1
282 FROM fii_time_day
283 );
284 EXCEPTION
285 WHEN NO_DATA_FOUND THEN
286 l_time_cal_dim := null;
287 l_cnt_set_up_err := l_cnt_set_up_err + 1;
288 END;
289
290 BEGIN
291 SELECT 1
292 INTO l_org_dim
293 FROM dual
294 WHERE EXISTS (
295 SELECT 1
296 FROM hri_org_hrchy_summary
297 );
298 EXCEPTION
299 WHEN NO_DATA_FOUND THEN
300 l_org_dim := null;
301 l_cnt_set_up_err := l_cnt_set_up_err + 1;
302 END;
303
304 IF l_time_cal_dim IS NULL THEN
305 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_TIME_CAL_DIMENSION')
306 INTO l_time_cal_msg_text
307 FROM dual;
308
309 l_dim_msg_text := l_dim_msg_text|| l_time_cal_msg_text || l_newline;
310 END IF;
311
312 IF l_org_dim IS NULL THEN
313 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_ORG_DIMENSION')
314 INTO l_org_msg_text
315 FROM dual;
316
317 l_dim_msg_text := l_dim_msg_text|| l_newline || l_org_msg_text || l_newline;
318 END IF;
319
320 pji_utils.write2out(l_newline || l_dim_msg_text || l_newline);
321
322 g_cnt_set_up_err := g_cnt_set_up_err + l_cnt_set_up_err;
323
324 END CHK_ORG_TIME_CAL_DIM;
325
326
327 /*
328 This API checks for organizations that have transactions but are not
329 reporesented in the PJI hierarchy. Call these organizations "orphan"
330 organizations.
331
332 If PJI summarization has not been run, check against the source. We get a
333 superset approximation of orphan organizations because getting the exact list
334 from the transaction tables would be too expensive.
335
336 If PJI summarization has been run, check against PJI facts. This gives us
337 the exact list of orphan organizations.
338 */
339 procedure CHK_PJI_ORG_HRCHY is
340
341 cursor orphan_organizations
342 (
343 p_org_structure_version_id in number,
344 p_settings_cost_flag in varchar2,
345 p_settings_profit_flag in varchar2,
346 p_settings_util_flag in varchar2,
347 p_params_cost_flag in varchar2,
348 p_params_profit_flag in varchar2,
349 p_params_util_flag in varchar2
350 ) is
351 select /*+ full(org) parallel(org) use_hash(org)
352 full(all_org) parallel(all_org) use_hash(all_org) */
353 all_org.NAME ORGANIZATION_NAME
354 from
355 (
356 select
357 org.ORGANIZATION_ID
358 from
359 (
360 select -- Resource Management
361 org.ORGANIZATION_ID
362 from
363 (
364 select /*+ index_ffs(org, PA_ALL_ORGANIZATIONS_U1)
365 parallel_index(org, PA_ALL_ORGANIZATIONS_U1) */
366 distinct
367 org.ORGANIZATION_ID
368 from
369 PA_ALL_ORGANIZATIONS org
370 where
371 p_settings_util_flag = 'Y' and
372 p_params_util_flag = 'N'
373 ) org
374 where
375 p_settings_util_flag = 'Y' and
376 p_params_util_flag = 'N' and
377 exists (select /*+ index_ffs(fid, PA_FORECAST_ITEM_DETAILS_N2)
378 parallel_index(fid, PA_FORECAST_ITEM_DETAILS_N2) */
379 1
380 from PA_FORECAST_ITEM_DETAILS fid
381 where fid.EXPENDITURE_ORGANIZATION_ID > 0 and
382 fid.EXPENDITURE_ORGANIZATION_ID = org.ORGANIZATION_ID)
383 union -- Financial Management
384 select /*+ ordered
385 use_hash(psc) swap_join_inputs(psc)
386 parallel(prj) use_hash(prj) */
387 distinct
388 prj.ORGANIZATION_ID
389 from
390 (
391 select /*+ no_merge(prj) */
392 prj.PROJECT_STATUS_CODE
393 from
394 (
395 select /*+ index_ffs(prj, PA_PROJECTS_N4)
396 parallel_index(prj, PA_PROJECTS_N4) */
397 distinct
398 prj.PROJECT_STATUS_CODE
399 from
400 PA_PROJECTS_ALL prj
401 where
402 ((p_settings_cost_flag = 'Y' and
403 p_params_cost_flag = 'N') or
404 (p_settings_profit_flag = 'Y' and
405 p_params_profit_flag = 'N'))
406 ) prj
407 where
408 ((p_settings_cost_flag = 'Y' and
409 p_params_cost_flag = 'N') or
410 (p_settings_profit_flag = 'Y' and
411 p_params_profit_flag = 'N')) and
412 PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED
413 (prj.PROJECT_STATUS_CODE, 'STATUS_REPORTING') = 'Y'
414 ) psc,
415 (
416 select /*+ index_ffs(prj, PA_PROJECTS_N2)
417 parallel_index(prj, PA_PROJECTS_N2) */
418 distinct
419 prj.CARRYING_OUT_ORGANIZATION_ID ORGANIZATION_ID,
420 prj.PROJECT_STATUS_CODE
421 from
422 PA_PROJECTS_ALL prj
423 where
424 ((p_settings_cost_flag = 'Y' and
425 p_params_cost_flag = 'N') or
426 (p_settings_profit_flag = 'Y' and
427 p_params_profit_flag = 'N'))
428 -- disregarding CLOSED_DATE massively improves performance
429 -- nvl(closed_date,to_date('01-JAN-1997')) >= to_date('01-JAN-1997')
430 ) prj
431 where
432 ((p_settings_cost_flag = 'Y' and
433 p_params_cost_flag = 'N') or
434 (p_settings_profit_flag = 'Y' and
435 p_params_profit_flag = 'N')) and
436 psc.project_status_code = prj.project_status_code
437 ) org,
438 (
439 select /*+ index_ffs(hrchy, HRI_ORG_HRCHY_SUMMARY_N1)
440 parallel_index(hrchy, HRI_ORG_HRCHY_SUMMARY_N1) */
441 distinct
442 hrchy.ORGANIZATION_ID
443 from
444 HRI_ORG_HRCHY_SUMMARY hrchy
445 where
446 ((p_settings_cost_flag = 'Y' and
447 p_params_cost_flag = 'N') or
448 (p_settings_profit_flag = 'Y' and
449 p_params_profit_flag = 'N') or
450 (p_settings_util_flag = 'Y' and
451 p_params_util_flag = 'N')) and
452 hrchy.ORG_STRUCTURE_VERSION_ID = p_org_structure_version_id
453 ) hrchy
454 where
455 ((p_settings_cost_flag = 'Y' and
456 p_params_cost_flag = 'N') or
457 (p_settings_profit_flag = 'Y' and
458 p_params_profit_flag = 'N') or
459 (p_settings_util_flag = 'Y' and
460 p_params_util_flag = 'N')) and
461 org.ORGANIZATION_ID = hrchy.ORGANIZATION_ID (+) and
462 hrchy.ORGANIZATION_ID is null
463 union
464 select /*+ full(org) parallel(org) use_hash(org)
465 full(denorm) parallel(denorm) use_hash(denorm) */
466 org.ORGANIZATION_ID
467 from
468 (
469 select /*+ parallel(org) */
470 distinct
471 org.ORGANIZATION_ID
472 from
473 (
474 select /*+ index_ffs(rmr, PJI_RM_RES_WT_F_N1)
475 parallel_index(rmr, PJI_RM_RES_WT_F_N1) */
476 rmr.EXPENDITURE_ORGANIZATION_ID ORGANIZATION_ID
477 from
478 PJI_RM_RES_WT_F rmr
479 where
480 p_params_util_flag = 'Y'
481 union all
482 select /*+ index_ffs(fpp, PJI_FP_PROJ_F_N1)
483 parallel_index(fpp, PJI_FP_PROJ_F_N1) */
484 fpp.PROJECT_ORGANIZATION_ID ORGANIZATION_ID
485 from
486 PJI_FP_PROJ_F fpp
487 where
488 p_params_cost_flag = 'Y' and
489 fpp.CALENDAR_TYPE = 'C'
490 union all
491 select /*+ index_ffs(acp, PJI_AC_PROJ_F_N1)
492 parallel_index(acp, PJI_AC_PROJ_F_N1) */
493 acp.PROJECT_ORGANIZATION_ID ORGANIZATION_ID
494 from
495 PJI_AC_PROJ_F acp
496 where
497 p_params_profit_flag = 'Y' and
498 acp.CALENDAR_TYPE = 'C'
499 )org
500 ) org,
501 (
502 select /*+ index_ffs(denorm, PJI_ORG_DENORM_N1)
503 parallel_index(denorm, PJI_ORG_DENORM_N1) */
504 distinct
505 denorm.ORGANIZATION_ID
506 from
507 PJI_ORG_DENORM denorm
508 where
509 (p_params_cost_flag = 'Y' or
510 p_params_profit_flag = 'Y' or
511 p_params_util_flag = 'Y')
512 ) denorm
513 where
514 (p_params_cost_flag = 'Y' or
515 p_params_profit_flag = 'Y' or
516 p_params_util_flag = 'Y') and
517 org.ORGANIZATION_ID = denorm.ORGANIZATION_ID (+) and
518 denorm.ORGANIZATION_ID is null
519 ) org,
520 HR_ALL_ORGANIZATION_UNITS all_org
521 where
522 org.ORGANIZATION_ID = all_org.ORGANIZATION_ID
523 order by all_org.NAME;
524
525 l_cnt_set_up_err number;
526 l_org_structure_version_id number;
527
528 l_settings_proj_perf_flag varchar2(1);
529 l_settings_cost_flag varchar2(1);
530 l_settings_profit_flag varchar2(1);
531 l_settings_util_flag varchar2(1);
532
533 l_params_proj_perf_flag varchar2(1);
534 l_params_cost_flag varchar2(1);
535 l_params_profit_flag varchar2(1);
536 l_params_util_flag varchar2(1);
537
538 l_header_flag varchar2(1);
539 l_newline varchar2(1) := '
540 ';
541
542 begin
543
544 select ORG_STRUCTURE_VERSION_ID
545 into l_org_structure_version_id
546 from PJI_SYSTEM_SETTINGS;
547
548 select
549 nvl(CONFIG_PROJ_PERF_FLAG, 'N'),
550 nvl(CONFIG_COST_FLAG, 'N'),
551 nvl(CONFIG_PROFIT_FLAG, 'N'),
552 nvl(CONFIG_UTIL_FLAG, 'N')
553 into
554 l_settings_proj_perf_flag,
555 l_settings_cost_flag,
556 l_settings_profit_flag,
557 l_settings_util_flag
558 from
559 PJI_SYSTEM_SETTINGS;
560
561 l_params_proj_perf_flag :=
562 nvl(PJI_UTILS.GET_PARAMETER('CONFIG_PROJ_PERF_FLAG'), 'N');
563 l_params_cost_flag :=
564 nvl(PJI_UTILS.GET_PARAMETER('CONFIG_COST_FLAG'), 'N');
565 l_params_profit_flag :=
566 nvl(PJI_UTILS.GET_PARAMETER('CONFIG_PROFIT_FLAG'), 'N');
567 l_params_util_flag :=
568 nvl(PJI_UTILS.GET_PARAMETER('CONFIG_UTIL_FLAG'), 'N');
569
570 if (l_settings_cost_flag = 'N' and
571 l_settings_profit_flag = 'N' and
572 l_settings_util_flag = 'N' and
573 l_params_cost_flag = 'N' and
574 l_params_profit_flag = 'N' and
575 l_params_util_flag = 'N') then
576 return;
577 end if;
578
579 l_header_flag := 'Y';
580 l_cnt_set_up_err := 0;
581
582 for c in orphan_organizations(l_org_structure_version_id,
583 l_settings_cost_flag,
584 l_settings_profit_flag,
585 l_settings_util_flag,
586 l_params_cost_flag,
587 l_params_profit_flag,
588 l_params_util_flag) loop
589
590 if (l_header_flag = 'Y') then
591
592 l_header_flag := 'N';
593
594 fnd_message.set_name('PJI', 'PJI_ORPHAN_ORGANIZATIONS');
595 pji_utils.write2out(fnd_message.get || l_newline);
596
597 l_cnt_set_up_err := l_cnt_set_up_err + 1;
598
599 end if;
600
601 pji_utils.write2out(c.ORGANIZATION_NAME || l_newline);
602
603 end loop;
604
605 if (l_header_flag = 'N') then
606 pji_utils.write2out(l_newline);
607 end if;
608
609 g_cnt_set_up_err := g_cnt_set_up_err + l_cnt_set_up_err;
610
611 end CHK_PJI_ORG_HRCHY;
612
613
614 /*
615 This API checks for all the project intelligence
616 security set up parameters that are needed for
617 running project intelligence application.
618 */
619
620 PROCEDURE CHK_SECURITY_SET_UP
621 (p_username IN VARCHAR2)
622 IS
623 l_pji_security_prof NUMBER:= NULL;
624 l_mo_security_prof NUMBER:= NULL;
625
626 l_pji_security_prof_tkn VARCHAR2(50);
627 l_mo_security_prof_tkn VARCHAR2(50);
628
629 l_org_view_all_flag VARCHAR2(1);
630 l_org_view_all_org_flag VARCHAR2(1);
631
632 l_ou_view_all_flag VARCHAR2(1);
633 l_ou_view_all_org_flag VARCHAR2(1);
634
635 l_org_all_access_msg_text VARCHAR2(200);
636 l_org_par_access_msg_text VARCHAR2(200);
637
638 l_ou_all_access_msg_text VARCHAR2(200);
639 l_ou_par_access_msg_text VARCHAR2(200);
640
641 l_cnt_set_up_err NUMBER := 0;
642 l_sec_prof_param_name_tbl V_TYPE_TAB;
643
644 l_org_name_tbl V_TYPE_TAB;
645 l_ou_name_tbl V_TYPE_TAB;
646
647 l_prof_not_set_msg_text VARCHAR2(200);
648 l_param_msg VARCHAR2(100) :='';
649 l_prof_err_msg_text VARCHAR2(400):='';
650 l_no_access_msg_text VARCHAR2(200);
651 l_newline varchar2(10) := '
652 ';
653
654 BEGIN
655
656 SELECT fnd_profile.value('PJI_SECURITY_PROFILE_LEVEL')
657 INTO l_pji_security_prof
658 FROM dual;
659
660 SELECT fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL')
661 INTO l_mo_security_prof
662 FROM dual;
663
664 l_sec_prof_param_name_tbl := V_TYPE_TAB();
665 l_sec_prof_param_name_tbl.DELETE;
666
667 IF l_pji_security_prof IS NULL THEN
668 l_cnt_set_up_err := l_cnt_set_up_err + 1;
669
670 SELECT meaning
671 INTO l_pji_security_prof_tkn
672 FROM pji_lookups
673 WHERE lookup_type = 'PJI_CHK_SECURITY_PARAMS'
674 and lookup_code = 'PJI_ORG_PROF';
675
676 l_sec_prof_param_name_tbl.EXTEND;
677 l_sec_prof_param_name_tbl(l_sec_prof_param_name_tbl.COUNT) := l_pji_security_prof_tkn;
678 END IF;
679
680 IF l_mo_security_prof IS NULL THEN
681 l_cnt_set_up_err := l_cnt_set_up_err + 1;
682
683 SELECT meaning
684 INTO l_mo_security_prof_tkn
685 FROM pji_lookups
686 WHERE lookup_type = 'PJI_CHK_SECURITY_PARAMS'
687 and lookup_code = 'PJI_MO_PROF';
688
689 l_sec_prof_param_name_tbl.EXTEND;
690 l_sec_prof_param_name_tbl(l_sec_prof_param_name_tbl.COUNT) := l_mo_security_prof_tkn;
691 END IF;
692
693 IF (l_cnt_set_up_err > 0) THEN
694 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_SEC_PROF_NOT_SET')
695 INTO l_prof_not_set_msg_text
696 FROM dual;
697
698 FOR i in l_sec_prof_param_name_tbl.FIRST.. l_sec_prof_param_name_tbl.LAST
699 LOOP
700 l_param_msg := l_param_msg || l_sec_prof_param_name_tbl(i) || l_newline;
701 END LOOP;
702
703 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_SEC_PROF_NO_ACCESS')
704 INTO l_no_access_msg_text
705 FROM dual;
706
707 pji_utils.write2out(l_newline || l_prof_not_set_msg_text || l_newline || l_param_msg || l_no_access_msg_text || l_newline);
708
709 RETURN;
710
711 END IF;
712
713 /*
714 If both the security profiles are set then the program
715 would come here. This part determines the organizations
716 and operating units to which the user has access
717 */
718
719 /* For PJI Organization Security Profile */
720 SELECT view_all_flag,
721 view_all_organizations_flag
722 INTO
723 l_org_view_all_flag,
724 l_org_view_all_org_flag
725 FROM PER_SECURITY_PROFILES
726 where security_profile_id = l_pji_security_prof;
727
728 IF (NVL(l_org_view_all_flag,'N') = 'Y' OR NVL(l_org_view_all_org_flag,'N') = 'Y') THEN
729 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_SEC_PROF_ALL_ACCESS')
730 INTO l_org_all_access_msg_text
731 FROM dual;
732
733 pji_utils.write2out(l_newline || l_org_all_access_msg_text || l_newline);
734 ELSE
735 l_org_name_tbl := V_TYPE_TAB();
736 l_org_name_tbl.DELETE;
737
738 SELECT org.name
739 BULK COLLECT INTO l_org_name_tbl
740 FROM
741 hr_all_organization_units org
742 ,per_organization_list sec
743 WHERE
744 org.organization_id = sec.organization_id
745 AND sec.security_profile_id = l_pji_security_prof;
746
747 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_SEC_PROF_PAR_ORG_ACCS')
748 INTO l_org_par_access_msg_text
749 FROM dual;
750
751 pji_utils.write2out(l_newline || l_org_par_access_msg_text || l_newline || l_newline);
752
753 FOR i in l_org_name_tbl.FIRST.. l_org_name_tbl.LAST
754 LOOP
755 pji_utils.write2out( l_org_name_tbl(i) || l_newline);
756 END LOOP;
757
758 pji_utils.write2out(l_newline);
759
760 END IF;
761
762 /* For MO Security Profile */
763 SELECT view_all_flag,
764 view_all_organizations_flag
765 INTO
766 l_ou_view_all_flag,
767 l_ou_view_all_org_flag
768 FROM PER_SECURITY_PROFILES
769 where security_profile_id = l_mo_security_prof;
770
771 IF (NVL(l_ou_view_all_flag,'N') = 'Y' OR NVL(l_ou_view_all_org_flag,'N') = 'Y') THEN
772 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_SEC_PROF_ALL_MO_ACCESS')
773 INTO l_ou_all_access_msg_text
774 FROM dual;
775
776 pji_utils.write2out(l_newline || l_ou_all_access_msg_text || l_newline);
777 ELSE
778 l_ou_name_tbl := V_TYPE_TAB();
779 l_ou_name_tbl.DELETE;
780
781 SELECT org.name
782 BULK COLLECT INTO l_ou_name_tbl
783 FROM
784 hr_all_organization_units org
785 ,per_organization_list sec
786 WHERE
787 org.organization_id = sec.organization_id
788 AND sec.security_profile_id = l_mo_security_prof
789 AND exists
790 (
791 SELECT 1
792 FROM hr_organization_information info
793 WHERE info.organization_id = org.organization_id
794 AND info.org_information_context = 'Operating Unit Information'
795 );
796
797 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_SEC_PROF_PAR_MO_ACCS')
798 INTO l_ou_par_access_msg_text
799 FROM dual;
800
801 pji_utils.write2out(l_newline || l_ou_par_access_msg_text || l_newline || l_newline);
802
803 FOR i in l_ou_name_tbl.FIRST.. l_ou_name_tbl.LAST
804 LOOP
805 pji_utils.write2out( l_ou_name_tbl(i) || l_newline);
806 END LOOP;
807
808 pji_utils.write2out(l_newline);
809
810 END IF;
811
812 END CHK_SECURITY_SET_UP;
813
814 /*
815 This CONCURRENT PROGRAM prepares the report for
816 PJI/BIS/TIME Calendar Dimension set up parameters
817 that are needed for running project intelligence
818 application.
819 */
820
821 PROCEDURE REPORT_PJI_PARAM_SETUP
822 (errbuff OUT NOCOPY VARCHAR2,
823 retcode OUT NOCOPY VARCHAR2)
824 IS
825 l_newline varchar2(10) := '
826 ';
827 l_pji_report_msg VARCHAR2(100);
828 l_pji_no_err_msg VARCHAR2(100);
829 l_separator VARCHAR2(100);
830 BEGIN
831
832 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_PJI_BIS_REPORT_TEXT')
833 INTO l_pji_report_msg
834 FROM dual;
835
836 l_separator := '---------------------------------------';
837
838 pji_utils.write2out(l_newline || l_pji_report_msg || l_newline || l_separator || l_newline);
839
840 CHK_BIS_SET_UP;
841
842 CHK_PJI_SET_UP;
843
844 CHK_ORG_TIME_CAL_DIM;
845
846 CHK_PJI_ORG_HRCHY;
847
848 IF (g_cnt_set_up_err = 0) THEN
849
850 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_SETUP_NOERR_TEXT')
851 INTO l_pji_no_err_msg
852 FROM dual;
853
854 pji_utils.write2out(l_pji_no_err_msg || l_newline);
855 END IF;
856
857 END REPORT_PJI_PARAM_SETUP;
858
859 /*
860 This CONCURRENT PROGRAM prepares the report for
861 Security set up parameters that are needed for
862 running project intelligence application.
863 */
864
865 PROCEDURE REPORT_PJI_SECURITY_SETUP
866 (p_user_name IN VARCHAR2,
867 errbuff OUT NOCOPY VARCHAR2,
868 retcode OUT NOCOPY VARCHAR2)
869 IS
870
871 l_newline varchar2(10) := '
872 ';
873 l_security_report_msg VARCHAR2(100);
874 l_user_name_msg VARCHAR2(30);
875 l_separator VARCHAR2(100);
876 l_username VARCHAR2(30);
877 l_userid NUMBER := FND_GLOBAL.USER_ID;
878
879 BEGIN
880
881 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_SECURITY_REPORT_TEXT')
882 INTO l_security_report_msg
883 FROM dual;
884
885 l_separator := '------------------------------------------';
886
887 pji_utils.write2out(l_newline || l_security_report_msg || l_newline || l_separator || l_newline);
888
889 IF p_user_name IS NULL THEN
890 SELECT user_name
891 INTO l_username
892 FROM fnd_user
893 WHERE user_id = l_userid;
894 ELSE
895 l_username := p_user_name;
896 END IF;
897
898 SELECT FND_MESSAGE.GET_STRING('PJI','PJI_CHK_USER_NAME')
899 INTO l_user_name_msg
900 FROM dual;
901
902 pji_utils.write2out(l_newline || l_user_name_msg || ' ' || l_username || l_newline );
903
904 CHK_SECURITY_SET_UP
905 (p_username => l_username);
906
907 END REPORT_PJI_SECURITY_SETUP;
908
909 END PJI_AUDIT_RPT;