1 PACKAGE BODY FII_SETUP_VAL_C AS
2 /* $Header: FIISVALB.pls 120.2.12000000.3 2007/10/16 04:05:05 wywong noship $ */
3
4 g_retcode VARCHAR2(20) := NULL;
5 g_phase VARCHAR2(120);
6
7
8 -------------------------------------------------------------------------------
9 -- FUNCTION detect_unmapped_local_vs
10 -------------------------------------------------------------------------------
11 FUNCTION detect_unmapped_local_vs( p_dim_short_name VARCHAR2 ) RETURN NUMBER IS
12
13 l_master_vs_id NUMBER(15);
14
15 cursor missing_csr is
16 select
17 rpad(fvs.flex_value_set_name, 30, ' ') vs_name,
18 ifs.id_flex_structure_name coa_name
19 from ( select distinct sas.chart_of_accounts_id
20 from fii_slg_assignments sas,
21 fii_source_ledger_groups slg
22 where slg.usage_code = 'DBI'
23 and slg.source_ledger_group_id = sas.source_ledger_group_id
24 ) coa_list,
25 fii_dim_mapping_rules dmr,
26 fnd_flex_value_sets fvs,
27 fnd_id_flex_structures_v ifs
28 where coa_list.chart_of_accounts_id = dmr.chart_of_accounts_id
29 and dmr.dimension_short_name = p_dim_short_name
30 and not exists (
31 select 1
32 from fii_dim_norm_hierarchy dnh
33 where dnh.parent_flex_value_set_id = l_master_vs_id
34 and dnh.child_flex_value_set_id = dmr.flex_value_set_id1
35 and rownum = 1
36 )
37 and dmr.flex_value_set_id1 = fvs.flex_value_set_id
38 and dmr.chart_of_accounts_id = ifs.id_flex_num
39 and ifs.application_id = 101
40 and ifs.id_flex_code = 'GL#'
41 and ifs.enabled_flag = 'Y';
42
43 l_missing_cnt NUMBER := 0;
44
45 BEGIN
46 BEGIN
47 SELECT master_value_set_id
48 INTO l_master_vs_id
49 FROM fii_financial_dimensions
50 WHERE dimension_short_name = p_dim_short_name;
51 EXCEPTION
52 WHEN no_data_found THEN
53 fii_util.write_log('No master_value_set_id found for ' ||
54 p_dim_short_name );
55 raise;
56 WHEN others THEN
57 raise;
58 END;
59
60 FOR missing_csr_rec in missing_csr LOOP
61 l_missing_cnt := l_missing_cnt + 1;
62
63 IF (l_missing_cnt = 1) THEN
64 fii_message.write_log( msg_name => 'FII_UNMAPPED_LVS_LIST',
65 token_num => 0 );
66 END IF;
67
68 fii_util.write_log( missing_csr_rec.vs_name || ' ' ||
69 missing_csr_rec.coa_name );
70 END LOOP;
71
72 RETURN l_missing_cnt;
73
74 EXCEPTION
75 WHEN others THEN
76 fii_util.write_log('Exception in detect_unmapped_local_vs: ' || sqlerrm );
77 fii_message.func_fail('FII_SETUP_VAL_C.detect_unmapped_local_vs');
78 RETURN -1;
79 END detect_unmapped_local_vs;
80
81 -------------------------------------------------------------------------------
82 -- PROCEDURE find_dup_ccc_org
83 -- -- Find out if there are multiple organizations in HR that are assigned
84 -- -- to the same company and cost center combinations.
85 -------------------------------------------------------------------------------
86 FUNCTION FIND_DUP_CCC_ORG RETURN NUMBER IS
87 l_count NUMBER(15) :=0;
88 p_status VARCHAR2(1) := NULL;
89
90 --this cursor prints out all (company, cost_center) with
91 --multiple orgs in table fii_ccc_mgr_gt
92 Cursor c_duplicate_org is
93 select count(*) cnt,
94 company_id,
95 cost_center_id
96 from fii_ccc_mgr_gt
97 where company_id is not null
98 and cost_center_id is not null
99 group by company_id, cost_center_id
100 having count(*) > 1;
101
102 --this cursor prints out all org for a given (company, cost_center)
103 Cursor c_list_dup_org (p_com_id number, p_cc_id number) is
104 select org.name organization,
105 com.flex_value company,
106 cc.flex_value cost_center,
107 per.full_name manager,
108 fnd_date.canonical_to_date(hoi.org_information3) eff_date
109 from fii_ccc_mgr_gt gt,
110 hr_all_organization_units org,
111 fnd_flex_values com,
112 fnd_flex_values cc,
113 per_all_people_f per,
114 hr_organization_information hoi
115 where gt.company_id = p_com_id
116 and gt.cost_center_id = p_cc_id
117 and gt.ccc_org_id = org.organization_id
118 and gt.company_id = com.flex_value_id
119 and gt.cost_center_id = cc.flex_value_id
120 and gt.manager = per.person_id
121 and hoi.org_information_context = 'Organization Name Alias'
122 and hoi.organization_id = gt.ccc_org_id;
123
124 BEGIN
125 -- Populate FII_CCC_MGR_GT
126 FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR (p_status);
127
128 IF p_status = -1 then
129 fii_util.write_log('Error in FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR ...');
130 fii_util.write_log('Table FII_CCC_MGR_GT is not populated');
131 raise NO_DATA_FOUND;
132 END IF;
133
134 l_count := 0;
135 FOR r_dup_org IN c_duplicate_org LOOP
136 IF l_count = 0 THEN
137
138 FII_MESSAGE.write_log (msg_name => 'FII_COM_CC_DUP_ORG',
139 token_num => 0);
140 fii_util.put_line(
141 'Org Name Company Cost Center Manager Effective Date');
142 fii_util.put_line(
143 '----------- -------- ----------- ------------------ --------------');
144 END IF;
145
146 l_count := l_count + 1;
147 FOR r_list_org IN c_list_dup_org (r_dup_org.company_id,
148 r_dup_org.cost_center_id) LOOP
149 FII_UTIL.write_log ( r_list_org.organization || ' ' ||
150 r_list_org.company || ' ' ||
151 r_list_org.cost_center || ' ' ||
152 r_list_org.manager || ' ' ||
153 r_list_org.eff_date );
154 END LOOP;
155 END LOOP;
156
157 RETURN l_count;
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 fii_util.write_log('Exception in find_dup_ccc_org: ' || sqlerrm );
162 fii_message.func_fail('FII_SETUP_VAL_C.find_dup_ccc_org');
163 RETURN -1;
164
165 END FIND_DUP_CCC_ORG;
166
167 -------------------------------------------------------------------------------
168 -- PROCEDURE VALIDATE_USER_SETUP
169 -- This procedure will perform user setup validations.
170 -------------------------------------------------------------------------------
171 PROCEDURE VALIDATE_USER_SETUP (p_user_name VARCHAR2) IS
172 l_user_id NUMBER(15) := NULL;
173 l_status NUMBER(15) := 0;
174 l_security_profile_id NUMBER;
175 l_security_org_id NUMBER;
176 l_business_group_id NUMBER;
177 l_all_org_flag VARCHAR2(30);
178 l_org_id NUMBER;
179 l_org_name VARCHAR2(240);
180 l_sec_profile_name VARCHAR2(240);
181 l_sec_warn_flag VARCHAR2(1);
182 l_cnt NUMBER;
183
184 CURSOR mgr_status_cur(user_id NUMBER) IS
185 SELECT 1
186 FROM dual
187 WHERE NOT EXISTS (
188 SELECT DISTINCT suph.sup_person_id
189 FROM (SELECT to_number (mgr_tbl.org_information2) manager,
190 ccc_tbl.organization_id ccc_org_id
191 FROM hr_organization_information ccc_tbl,
192 ( SELECT organization_id, org_information2
193 FROM hr_organization_information b
194 WHERE org_information_context = 'Organization Name Alias'
195 AND nvl( fnd_date.canonical_to_date( org_information3 ),
196 sysdate + 1 ) <= sysdate
197 AND nvl( fnd_date.canonical_to_date( org_information4 ),
198 sysdate + 1 ) >= sysdate
199 ) mgr_tbl,
200 hr_organization_information org,
201 fnd_flex_values fv1,
202 fnd_flex_values fv2
203 WHERE ccc_tbl.org_information_context = 'CLASS'
204 AND ccc_tbl.org_information1 = 'CC'
205 AND ccc_tbl.org_information2 = 'Y'
206 AND ccc_tbl.organization_id = mgr_tbl.organization_id (+)
207 AND org.org_information_context = 'Company Cost Center'
208 AND org.organization_id = ccc_tbl.organization_id
209 AND fv1.flex_value_set_id = org.org_information2
210 AND fv1.flex_value = org.org_information3
211 AND fv2.flex_value_set_id = org.org_information4
212 AND fv2.flex_value = org.org_information5) ct,
213 hri_cs_suph suph,
214 per_assignment_status_types ast
215 WHERE ct.manager = suph.sub_person_id
216 AND sysdate between suph.effective_start_date and suph.effective_end_date
217 AND suph.sup_assignment_status_type_id = ast.assignment_status_type_id
218 AND ast.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
219 AND ct.manager = user_id);
220
221 CURSOR sec_org_cur IS
222 SELECT security_profile_name
223 FROM per_security_profiles
224 WHERE security_profile_id NOT IN (SELECT security_profile_id
225 FROM per_organization_list);
226
227 BEGIN
228 ----------------------------------------------------------------------
229 -- User Setup Validations
230 ----------------------------------------------------------------------
231 -- Make sure that the username pass in exists in fii_cc_mgr_sup_v
232 ----------------------------------------------------------------------
233 fii_util.put_line(' ');
234 fii_util.put_line(
235 '+---------------------------------------------------------------------+');
236 fii_util.put_line(' ');
237 fii_util.put_line(
238 '***********************************************************************');
239 fii_util.put_line(
240 '********************** User Setup Validations ************************');
241 fii_util.put_line(
242 '***********************************************************************');
243 fii_util.put_line(' ');
244 fii_util.put_line('Performing Check for: User exists in fii_cc_mgr_sup_v');
245 fii_util.put_line(
246 '+---------------------------------------------------------------------+');
247 -- Check if user exists in manager view
248 fii_util.put_line('START CHECK: User exists in fii_cc_mgr_sup_v');
249 fii_util.put_line(' ');
250
251 -- Set a default value for l_user_id
252 l_user_id := -1;
253
254 -- Retrieve fnd user ID from the username if it exists in fii_cc_mgr_sup_v
255 BEGIN
256 SELECT DISTINCT employee_id
257 INTO l_user_id
258 FROM fnd_user a,
259 fii_cc_mgr_sup_v b
260 WHERE a.user_name = UPPER(p_user_name)
261 AND a.employee_id = b.id;
262 EXCEPTION
263 WHEN no_data_found THEN
264 fii_util.put_line('The username '|| p_user_name ||
265 'is not found in fii_cc_mgr_sup_v');
266
267 END;
268
269 fii_util.put_line('Username parameter = ' || p_user_name ||
270 ' and its user_id = ' || l_user_id);
271
272 fii_util.put_line(' ');
273 IF (l_user_id = -1) THEN
274 fii_util.put_line('DIAGNOSIS: The user ' || p_user_name ||
275 ' does not have a person attached to it or the person does not exist in fii_cc_mgr_sup_v');
276
277 ELSE
278 fii_util.put_line('DIAGNOSIS: A person is attached to the user ' || p_user_name ||
279 ' and this person exists in fii_cc_mgr_sup_v');
280 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
281 END IF;
282
283 fii_util.put_line(' ');
284 fii_util.put_line('END CHECK: User exists in fii_cc_mgr_sup_v');
285 fii_util.put_line(
286 '+---------------------------------------------------------------------+');
287
288 ----------------------------------------------------------------------
289 -- Check to see if manager assignment status is either Active Assignment
290 -- or Suspended Assignment for the manager attached to the user logged in
291 ----------------------------------------------------------------------
292 fii_util.put_line(' ');
293 fii_util.put_line(
294 '+---------------------------------------------------------------------+');
295 fii_util.put_line('Performing Check for: Manager Assignment Status');
296 fii_util.put_line(
297 '+---------------------------------------------------------------------+');
298 -- Check the manager assignment status
299 fii_util.put_line('START CHECK: Manager Assignment Status');
300 fii_util.put_line(' ');
301
302 OPEN mgr_status_cur(l_user_id);
303 FETCH mgr_status_cur INTO l_status;
304
305 IF mgr_status_cur%NOTFOUND THEN
306 fii_util.put_line('DIAGNOSIS: Manager Assignment Status for user '''||
307 p_user_name||
308 ''' is ''Active'' or ''Suspended''');
309 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
310 ELSE
311 fii_util.put_line('DIAGNOSIS: Manager Assignment Status for user '''||
312 p_user_name||
313 ''' is neither ''Active'' nor ''Suspended''. Please review.');
314 END IF;
315
316
317 CLOSE mgr_status_cur;
318
319 fii_util.put_line(' ');
320 fii_util.put_line('END CHECK: Manager Assignment Status');
321 fii_util.put_line(
322 '+---------------------------------------------------------------------+');
323
324 ----------------------------------------------------------------------
325 -- Check to see if we have operating units assigned to the security profile
326 ----------------------------------------------------------------------
327 fii_util.put_line(' ');
328 fii_util.put_line(
329 '+---------------------------------------------------------------------+');
330 fii_util.put_line('Performing Check for: Profile-Operating Unit Assignment');
331 fii_util.put_line(
332 '+---------------------------------------------------------------------+');
333 -- Check the profile-operating unit assignment
334 fii_util.put_line('START CHECK: Profile-Operating Unit Assignment Analysis');
335 fii_util.put_line(' ');
336
337 l_cnt := 0;
338 OPEN sec_org_cur;
339 LOOP
340 FETCH sec_org_cur INTO l_sec_profile_name;
341 EXIT WHEN sec_org_cur%NOTFOUND;
342
343 IF (l_cnt = 0) THEN
344 fii_util.put_line('Profile Name ');
345 fii_util.put_line(
346 '-----------------------------------------------------------------------');
347 l_cnt := l_cnt + 1;
348 END IF;
349 fii_util.put_line(l_sec_profile_name);
350 END LOOP;
351 CLOSE sec_org_cur;
352
353 fii_util.put_line(' ');
354 IF l_cnt > 0 THEN
355 fii_util.put_line(
356 'DIAGNOSIS: Please assign Operating units for the above profiles.');
357 ELSE
358 fii_util.put_line(
359 'DIAGNOSIS: Profile-Operating Unit Assignments Verified.');
360 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
361 END IF;
362
363 fii_util.put_line(' ');
364 fii_util.put_line(
365 'END CHECK: Profile-Organization Unit Assignment Analysis.');
366 fii_util.put_line(
367 '+---------------------------------------------------------------------+');
368
369 END VALIDATE_USER_SETUP;
370
371
372 /************************************************************************
373 PUBLIC PROCEDURES
374 ************************************************************************/
375
379 -- Main
376 -------------------------------------------------------------------------------
377
378 -- Procedure
380 -- Purpose
381 -- This is the main routine of the Validate Setup program
382 -- History
383 -- 08-30-06 W Wong Created
384 -- Arguments
385 -- X_User User Name used for checking user setup
386 -- Example
387 -- result := FII_SETUP_VAL_C.Main(Errbuf, Retcode, X_User);
388 -- Notes
389 --
390 PROCEDURE Main (Errbuf IN OUT NOCOPY VARCHAR2,
391 Retcode IN OUT NOCOPY VARCHAR2,
392 X_User IN VARCHAR2) IS
393 l_prim_curr_code VARCHAR2(15) := NULL;
394 l_sec_curr_code VARCHAR2(15) := NULL;
395 l_prim_curr_mau NUMBER := NULL;
396 l_sec_curr_mau NUMBER := NULL;
397 l_budget_time_unit VARCHAR2(1) := NULL;
398 l_forecast_time_unit VARCHAR2(1) := NULL;
399 l_user_id NUMBER(15) := NULL;
400 l_login_id NUMBER(15) := NULL;
401 l_req_id NUMBER(15) := NULL;
402 l_global_start_date VARCHAR2(15) := NULL;
403 l_unassigned_udd_id NUMBER(15) := NULL;
404 l_industry_profile VARCHAR2(1);
405 l_budget_source VARCHAR2(15);
406 l_start_time VARCHAR2(30);
407 l_end_time VARCHAR2(30);
408 l_period_set_name VARCHAR2(15);
409 l_period_type VARCHAR2(15);
410 l_year NUMBER;
411 l_min_start_date DATE;
412 l_max_end_date DATE := sysdate;
413
414 l_db_version VARCHAR2(100);
415 l_sys_date DATE;
416 l_day DATE;
417 l_start_day DATE;
418 l_cnt NUMBER;
419 l_dummy NUMBER;
420 l_fiscal_year_day NUMBER;
421 l_dim_name VARCHAR2(4000);
422 l_dim_short_name VARCHAR2(30);
423 l_fin_type_name VARCHAR2(30);
424 l_co_name VARCHAR2(150);
425 l_co_id NUMBER;
426 l_cc_name VARCHAR2(150);
427 l_cc_id NUMBER;
428 l_fc_name VARCHAR2(150);
429 l_fc_id NUMBER;
430 l_udd1_name VARCHAR2(150);
431 l_udd1_id NUMBER;
432 l_udd2_name VARCHAR2(150);
433 l_udd2_id NUMBER;
434 l_vs_name VARCHAR2(60);
435 l_vs_id NUMBER;
436 l_mgr VARCHAR2(240);
437 l_emp_num NUMBER;
438 l_eff_start VARCHAR2(30);
439 l_eff_end VARCHAR2(30);
440 l_org_name VARCHAR2(240);
441 l_org_id NUMBER;
442 l_status VARCHAR2(30);
443 l_enabled_flag VARCHAR2(1);
444 l_profile_value VARCHAR2(1);
445 l_dashboard_warn_flag VARCHAR2(1);
446 l_mv_name VARCHAR2(100);
447 l_mv_row_ct NUMBER;
448 l_stmt VARCHAR2(2000);
449 l_udd_enabled_flag VARCHAR2(1);
450 l_lookup_code VARCHAR2(30);
451 l_lookup_type VARCHAR2(30);
452
453 CURSOR ent_period_cur (day date) IS
454 select 1
455 from gl_periods
456 where adjustment_period_flag = 'N'
457 and period_set_name = l_period_set_name
458 and period_type = l_period_type
459 and day between start_date and end_date;
460
461 CURSOR fiscal_year_cur (year number) IS
462 select min(a.start_date), max(end_date)
463 from gl_periods a
464 where a.period_set_name = l_period_set_name
465 and a.period_type = l_period_type
466 and a.adjustment_period_flag = 'N'
467 and a.period_year = year;
468
469 CURSOR enabled_dim_cur IS
470 select dimension_name, dimension_short_name
471 from fii_financial_dimensions_v
472 where dbi_enabled_flag = 'Y';
473
474 CURSOR fin_type_cur IS
475 select 'R'
476 from dual
477 where not exists (select 1 from fii_fin_cat_type_assgns
478 where fin_cat_type_code = 'R')
479 union
480 select 'OE'
481 from dual
482 where not exists (select 1 from fii_fin_cat_type_assgns
483 where fin_cat_type_code = 'OE')
484 union
485 select 'TE'
486 from dual
487 where not exists (select 1 from fii_fin_cat_type_assgns
488 where fin_cat_type_code = 'TE')
489 union
490 select 'CGS'
491 from dual
492 where not exists (select 1 from fii_fin_cat_type_assgns
493 where fin_cat_type_code = 'CGS')
494 union
495 select 'DR'
496 from dual
497 where not exists (select 1 from fii_fin_cat_type_assgns
498 where fin_cat_type_code = 'DR');
499
500 --this cursor prints out all company, cost_center with no orgs assigned
501 Cursor no_org_cur is
502 select fv_co.flex_value company,
503 fv_co.flex_value_id com_id,
504 fv_cc.flex_value cost_center,
505 fv_cc.flex_value_id cc_id
506 from (select distinct company_id, cost_center_id
507 from fii_gl_je_summary_b) b,
508 fnd_flex_values fv_co,
512 and not exists (select 1 from hr_organization_information
509 fnd_flex_values fv_cc
510 where fv_co.flex_value_id = b.company_id
511 and fv_cc.flex_value_id = b.cost_center_id
513 where org_information_context = 'Company Cost Center'
514 and org_information2 = fv_co.flex_value_set_id
515 and org_information3 = fv_co.flex_value
516 and org_information4 = fv_cc.flex_value_set_id
517 and org_information5 = fv_cc.flex_value);
518
519 CURSOR org_no_cc_cur IS
520 select fv_co.flex_value_id co_id,
521 fv_co.flex_value co,
522 org.organization_id,
523 org2.name
524 from (select distinct company_id
525 from fii_gl_je_summary_b) b,
526 fnd_flex_values fv_co,
527 hr_organization_information org,
528 hr_all_organization_units org2
529 where fv_co.flex_value_id = b.company_id
530 and org_information_context = 'Company Cost Center'
531 and org_information2 = fv_co.flex_value_set_id
532 and org_information3 = fv_co.flex_value
533 and org_information4 is null
534 and org_information5 is null
535 and org2.organization_id = org.organization_id;
536
537 CURSOR mgr_assgn_cur IS
538 select fv_co.flex_value co,
539 fv_cc.flex_value cc,
540 org.organization_id,
541 org2.name organization
542 from (select distinct company_id, cost_center_id
543 from fii_gl_je_summary_b) b,
544 fnd_flex_values fv_co,
545 fnd_flex_values fv_cc,
546 hr_organization_information org,
547 hr_all_organization_units org2
548 where fv_co.flex_value_id = b.company_id
549 and fv_cc.flex_value_id = b.cost_center_id
550 and org_information_context = 'Company Cost Center'
551 and org_information2 = fv_co.flex_value_set_id
552 and org_information3 = fv_co.flex_value
553 and org_information4 = fv_cc.flex_value_set_id
554 and org_information5 = fv_cc.flex_value
555 and org2.organization_id = org.organization_id
556 and not exists
557 (select 1
558 from hr_organization_information mgr
559 where mgr.org_information_context = 'Organization Name Alias'
560 and (nvl( fnd_date.canonical_to_date( mgr.org_information3 ),
561 sysdate + 1 ) <= sysdate
562 or nvl( fnd_date.canonical_to_date( mgr.org_information4 ),
563 sysdate + 1 ) >= sysdate)
564 and mgr.org_information2 is not null
565 and mgr.organization_id = org.organization_id);
566
567 CURSOR mgr_eff_cur IS
568 select per2.first_name || ' ' || per2.last_name name,
569 org.name organization,
570 per2.effective_end_date
571 from
572 ( select organization_id, org_information2
573 from hr_organization_information b
574 where org_information_context = 'Organization Name Alias'
575 and (nvl( fnd_date.canonical_to_date( org_information3 ),
576 sysdate + 1 ) <= sysdate
577 or nvl( fnd_date.canonical_to_date( org_information4 ),
578 sysdate + 1 ) >= sysdate)
579 and org_information2 is not null
580 ) mgr_tbl,
581 hr_all_organization_units org,
582 per_all_people_f per2
583 where org.organization_id = mgr_tbl.organization_id
584 and per2.person_id = mgr_tbl.org_information2
585 and not exists (select 1
586 from per_all_people_f per
587 where per.person_id = mgr_tbl.org_information2
588 and per.effective_end_date > sysdate);
589
590 CURSOR mgr_status_cur IS
591 select distinct per.full_name, ast.per_system_status
592 from ( select organization_id, org_information2
593 from hr_organization_information b
594 where org_information_context = 'Organization Name Alias'
595 and (nvl( fnd_date.canonical_to_date( org_information3 ),
596 sysdate + 1 ) <= sysdate
597 or nvl( fnd_date.canonical_to_date( org_information4 ),
598 sysdate + 1 ) >= sysdate)
599 and org_information2 is not null) ct,
600 hri_cs_suph suph,
601 per_assignment_status_types ast,
602 per_all_people_f per
603 where ct.org_information2 = suph.sub_person_id
604 and per.person_id = suph.sub_person_id
605 and sysdate between suph.effective_start_date
606 and suph.effective_end_date
607 and suph.sup_assignment_status_type_id = ast.assignment_status_type_id
608 and ast.per_system_status NOT IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
609
610 CURSOR trans_ccc_no_mgr_cur IS
611 SELECT ccc_tbl.organization_id ccc_org_id,
612 rpad(hou.name, 40, ' '), fgj.company_id, fv1.flex_value co,
613 fgj.cost_center_id, fv2.flex_value cc
614 FROM hr_organization_information ccc_tbl,
615 ( select organization_id, org_information2
616 from hr_organization_information
617 where org_information_context = 'Organization Name Alias'
621 sysdate + 1 ) >= sysdate
618 and nvl( fnd_date.canonical_to_date( org_information3 ),
619 sysdate + 1 ) <= sysdate
620 and nvl( fnd_date.canonical_to_date( org_information4 ),
622 ) mgr_tbl,
623 hr_organization_information org,
624 hr_all_organization_units hou,
625 fnd_flex_values fv1,
626 fnd_flex_values fv2,
627 (select distinct company_id, cost_center_id
628 from fii_gl_je_summary_b) fgj
629 WHERE ccc_tbl.org_information_context = 'CLASS'
630 AND ccc_tbl.org_information1 = 'CC'
631 AND ccc_tbl.org_information2 = 'Y'
632 AND ccc_tbl.organization_id = mgr_tbl.organization_id (+)
633 AND org.org_information_context = 'Company Cost Center'
634 AND org.organization_id = ccc_tbl.organization_id
635 AND hou.organization_id = ccc_tbl.organization_id
636 AND fv1.flex_value_set_id = org.org_information2
637 AND fv1.flex_value = org.org_information3
638 AND fv2.flex_value_set_id = org.org_information4
639 AND fv2.flex_value = org.org_information5
640 AND fv1.flex_value_id = fgj.company_id
641 AND fv2.flex_value_id = fgj.cost_center_id
642 AND fgj.company_id IS NOT NULL
643 AND fgj.cost_center_id IS NOT NULL
644 AND mgr_tbl.org_information2 IS NULL;
645
646 CURSOR trans_no_fc_cur IS
647 SELECT b.flex_value_set_id, rpad(e.flex_value_set_name, 40, ' '),
648 c.fin_category_id, d.flex_value
649 FROM fnd_segment_attribute_values a,
650 fnd_id_flex_segments b,
651 (select distinct v.fin_category_id, v.chart_of_accounts_id
652 from
653 ( select distinct fgj.fin_category_id, fgj.chart_of_accounts_id
654 from hr_organization_information ccc_tbl,
655 ( select organization_id, org_information2
656 from hr_organization_information
657 where org_information_context = 'Organization Name Alias'
658 and nvl( fnd_date.canonical_to_date( org_information3 ),
659 sysdate + 1 ) <= sysdate
660 and nvl( fnd_date.canonical_to_date( org_information4 ),
661 sysdate + 1 ) >= sysdate
662 ) mgr_tbl,
663 hr_organization_information org,
664 fnd_flex_values fv1,
665 fnd_flex_values fv2,
666 (select distinct company_id, cost_center_id, fin_category_id,
667 chart_of_accounts_id
668 from fii_gl_je_summary_b) fgj
669 where ccc_tbl.org_information_context = 'CLASS'
670 and ccc_tbl.org_information1 = 'CC'
671 and ccc_tbl.org_information2 = 'Y'
672 and ccc_tbl.organization_id = mgr_tbl.organization_id (+)
673 and org.org_information_context = 'Company Cost Center'
674 and org.organization_id = ccc_tbl.organization_id
675 and fv1.flex_value_set_id = org.org_information2
676 and fv1.flex_value = org.org_information3
677 and fv2.flex_value_set_id = org.org_information4
678 and fv2.flex_value = org.org_information5
679 and fv1.flex_value_id = fgj.company_id
680 and fv2.flex_value_id = fgj.cost_center_id) v
681 where not exists (select fcm.child_fin_cat_id
682 from fii_fin_cat_mappings fcm
683 where fcm.child_fin_cat_id = v.fin_category_id )) c,
684 fnd_flex_values d,
685 fnd_flex_value_sets e
686 WHERE a.application_id = 101
687 AND a.id_flex_code = 'GL#'
688 AND a.id_flex_num = c.chart_of_accounts_id
689 AND a.segment_attribute_type = 'GL_ACCOUNT'
690 AND a.attribute_value = 'Y'
691 AND b.application_id = a.application_id
692 AND b.id_flex_code = a.id_flex_code
693 AND b.id_flex_num = a.id_flex_num
694 AND b.application_column_name = a.application_column_name
695 AND d.flex_value_set_id = b.flex_value_set_id
696 AND d.flex_value_id = c.fin_category_id
697 AND e.flex_value_set_id = b.flex_value_set_id;
698
699 CURSOR unmapped_udd1_cur IS
700 SELECT fvs.flex_value_set_id, fvs.flex_value_set_name,
701 fv.flex_value_id, fv.flex_value
702 FROM fnd_flex_values fv,
703 fnd_flex_value_sets fvs,
704 ( SELECT DISTINCT user_dim1_id flex_value_id
705 FROM fii_gl_ccid_dimensions
706 MINUS
707 SELECT child_value_id flex_value_id
708 FROM fii_full_udd1_hiers
709 WHERE parent_value_id = child_value_id) udd1
710 WHERE fv.flex_value_id = udd1.flex_value_id
711 AND fvs.flex_value_set_id = fv.flex_value_set_id;
712
713 CURSOR unmapped_udd2_cur IS
714 SELECT fvs.flex_value_set_id, fvs.flex_value_set_name,
715 fv.flex_value_id, fv.flex_value
716 FROM fnd_flex_values fv,
717 fnd_flex_value_sets fvs,
718 ( SELECT distinct user_dim2_id flex_value_id
719 From fii_gl_ccid_dimensions
720 MINUS
721 SELECT child_value_id flex_value_id
722 FROM fii_full_udd2_hiers
726
723 WHERE parent_value_id = child_value_id) udd2
724 WHERE fv.flex_value_id = udd2.flex_value_id
725 AND fvs.flex_value_set_id = fv.flex_value_set_id;
727 CURSOR fc_no_fctype_cur IS
728 SELECT b.flex_value_set_id, rpad(e.flex_value_set_name, 40, ' '),
729 c.fin_category_id, d.flex_value
730 FROM fnd_segment_attribute_values a,
731 fnd_id_flex_segments b,
732 (select v.fin_category_id, v.chart_of_accounts_id
733 from
734 (select distinct fgj.fin_category_id,
735 fgj.chart_of_accounts_id
736 from hr_organization_information ccc_tbl,
737 ( select organization_id, org_information2
738 from hr_organization_information
739 where org_information_context = 'Organization Name Alias'
740 and nvl( fnd_date.canonical_to_date( org_information3 ),
741 sysdate + 1 ) <= sysdate
742 and nvl( fnd_date.canonical_to_date( org_information4 ),
743 sysdate + 1 ) >= sysdate
744 ) mgr_tbl,
745 hr_organization_information org,
746 fnd_flex_values fv1,
747 fnd_flex_values fv2,
748 (select distinct company_id, cost_center_id, fin_category_id,
749 chart_of_accounts_id
750 from fii_gl_je_summary_b) fgj,
751 fii_fin_cat_mappings fcm
752 where ccc_tbl.org_information_context = 'CLASS'
753 and ccc_tbl.org_information1 = 'CC'
754 and ccc_tbl.org_information2 = 'Y'
755 and ccc_tbl.organization_id = mgr_tbl.organization_id (+)
756 and org.org_information_context = 'Company Cost Center'
757 and org.organization_id = ccc_tbl.organization_id
758 and fv1.flex_value_set_id = org.org_information2
759 and fv1.flex_value = org.org_information3
760 and fv2.flex_value_set_id = org.org_information4
761 and fv2.flex_value = org.org_information5
762 and fv1.flex_value_id = fgj.company_id
763 and fv2.flex_value_id = fgj.cost_center_id
764 and fcm.child_fin_cat_id = fgj.fin_category_id ) v
765 WHERE NOT EXISTS (select fct.fin_category_id
766 from fii_fin_cat_type_assgns fct
767 where fct.fin_category_id = v.fin_category_id))c,
768 fnd_flex_values d,
769 fnd_flex_value_sets e
770 WHERE a.application_id = 101
771 AND a.id_flex_code = 'GL#'
772 AND a.id_flex_num = c.chart_of_accounts_id
773 AND a.segment_attribute_type = 'GL_ACCOUNT'
774 AND a.attribute_value = 'Y'
775 AND b.application_id = a.application_id
776 AND b.id_flex_code = a.id_flex_code
777 AND b.id_flex_num = a.id_flex_num
778 AND b.application_column_name = a.application_column_name
779 AND d.flex_value_set_id = b.flex_value_set_id
780 AND d.flex_value_id = c.fin_category_id
781 AND e.flex_value_set_id = b.flex_value_set_id;
782
783 CURSOR invalid_lookup_cur IS
784 SELECT a.lookup_code,
785 decode(a.lookup_type,
786 'FII_PSI_ENCUM_TYPES_OBLIGATION', 'Obligation',
787 'FII_PSI_ENCUM_TYPES_COMMITMENT', 'Commitment') lookup_type
788 FROM fnd_lookup_values a
789 WHERE a.lookup_type in ( 'FII_PSI_ENCUM_TYPES_OBLIGATION',
790 'FII_PSI_ENCUM_TYPES_COMMITMENT')
791 AND a.view_application_id = 450
792 AND a.language = userenv('LANG')
793 AND upper(a.lookup_code) not in (select upper(encumbrance_type)
794 from gl_encumbrance_types);
795
796 CURSOR gl_mv_cur IS
797 SELECT object_name
798 FROM sys.dba_objects
799 WHERE object_name IN ( 'FII_GL_AGRT_SUM_MV', 'FII_GL_BASE_MAP_MV',
800 'FII_GL_BASE_MV', 'FII_GL_MGMT_CCC_MV',
801 'FII_GL_MGMT_SUM_MV', 'FII_GL_TREND_SUM_MV')
802 AND object_type = 'MATERIALIZED VIEW';
803
804 CURSOR ap_mv_cur IS
805 SELECT object_name
806 FROM sys.dba_objects
807 WHERE object_name IN ( 'FII_AP_HATY_XB_MV', 'FII_AP_HCAT_B_MV',
808 'FII_AP_HCAT_IB_MV', 'FII_AP_HCAT_I_MV',
809 'FII_AP_HHIST_B_MV', 'FII_AP_HHIST_IB_MV',
810 'FII_AP_HHIST_I_MV', 'FII_AP_HLIA_IB_MV',
811 'FII_AP_HLIA_I_MV', 'FII_AP_HLWAG_IB_MV',
812 'FII_AP_INV_B_MV', 'FII_AP_IVATY_B_MV',
813 'FII_AP_IVATY_XB_MV','FII_AP_LIA_B_MV',
814 'FII_AP_LIA_IB_MV', 'FII_AP_LIA_I_MV',
815 'FII_AP_LIA_KPI_MV', 'FII_AP_LIWAG_IB_MV',
816 'FII_AP_MGT_KPI_MV', 'FII_AP_PAID_XB_MV',
817 'FII_AP_PAYOL_XB_MV')
818 AND object_type = 'MATERIALIZED VIEW';
819
820 CURSOR ar_mv_cur IS
821 SELECT object_name
822 FROM sys.dba_objects
826 'FII_AR_DISPUTES_AGRT_MV',
823 WHERE object_name IN ( 'FII_AR_BILLING_ACT_AGRT_MV',
824 'FII_AR_BILLING_ACT_BASE_MV',
825 'FII_AR_DIMENSIONS_MV',
827 'FII_AR_DISPUTES_BASE_MV',
828 'FII_AR_NET_REC_AGRT_MV',
829 'FII_AR_NET_REC_BASE_MV',
830 'FII_AR_RCT_AGING_AGRT_MV',
831 'FII_AR_RCT_AGING_BASE_MV',
832 'FII_AR_REV_SUM_MV')
833 AND object_type = 'MATERIALIZED VIEW';
834
835 CURSOR dim_enabled_cur1 IS
836 select rpad(dimension_name, 40), dimension_short_name, dbi_enabled_flag
837 from fii_financial_dimensions_v
838 where dimension_short_name in ('FII_LOB', 'GL_FII_FIN_ITEM')
839 order by (decode (dimension_short_name, 'FII_LOB', 1, 'GL_FII_FIN_ITEM', 2));
840
841 CURSOR dim_enabled_cur2 IS
842 select rpad(dimension_name, 40), dimension_short_name, dbi_enabled_flag
843 from fii_financial_dimensions_v
844 where dimension_short_name in ('FII_COMPANIES', 'HRI_CL_ORGCC',
845 'FII_USER_DEFINED_1', 'GL_FII_FIN_ITEM')
846 order by (decode (dimension_short_name,
847 'FII_COMPANIES', 1,
848 'HRI_CL_ORGCC', 2,
849 'FII_USER_DEFINED_1', 3,
850 'GL_FII_FIN_ITEM', 4));
851
852 BEGIN
853
854 -- Retrive current system time as the start time of the program
855 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'), sysdate
856 INTO l_start_time, l_sys_date
857 FROM dual;
858
859 -----------------------------------------------------------------------
860 -- Print report header in logfile
861 -----------------------------------------------------------------------
862 fii_util.put_line(' ');
863 fii_util.put_line(
864 '+=====================================================================+');
865 fii_util.put_line('+ DBI Financials Diagnostics ');
866 fii_util.put_line(
867 '+ DBI_DIAGNOSTIC module: Daily Business Intelligence Diagnostic Program ');
868 fii_util.put_line('+ Current System Time is ' || l_start_time);
869 fii_util.put_line(
870 '+=====================================================================+');
871 fii_util.put_line(' ');
872 fii_util.put_line('** Diagnostics Starts ** '|| l_start_time);
873 fii_util.put_line
874 ('** Start of log messages from DBI Financials Validation ***');
875 fii_util.put_line(' ');
876
877 -----------------------------------------------------------------------
878 -- Retrieve setup information
879 -----------------------------------------------------------------------
880 l_prim_curr_code:= BIS_COMMON_PARAMETERS.get_currency_code;
881 l_sec_curr_code := BIS_COMMON_PARAMETERS.get_secondary_currency_code;
882 l_prim_curr_mau := FII_CURRENCY.get_mau_primary;
883 l_sec_curr_mau := FII_CURRENCY.get_mau_secondary;
884 l_user_id := FND_GLOBAL.User_Id;
885 l_login_id := FND_GLOBAL.Login_Id;
886 l_req_id := FND_GLOBAL.Conc_Request_Id;
887
888 l_global_start_date := FND_PROFILE.Value('BIS_GLOBAL_START_DATE');
889 l_budget_time_unit := FND_PROFILE.Value('FII_BUDGET_TIME_UNIT');
890 l_forecast_time_unit := FND_PROFILE.Value('FII_FORECAST_TIME_UNIT');
891
892 -- Print setup information to the logfile
893 fii_util.put_line(' ');
894 fii_util.put_line('Checking for missing setup information...');
895 fii_util.put_line(
896 '+---------------------------------------------------------------------+');
897 fii_util.put_line('INIT: Global Start Date = '||l_global_start_date);
898 fii_util.put_line('INIT: Primary Currency = '||l_prim_curr_code);
899 fii_util.put_line('INIT: Primary MAU = '||l_prim_curr_mau);
900 fii_util.put_line('INIT: User ID = '||l_user_id);
901 fii_util.put_line('INIT: Login ID = '||l_login_id);
902 fii_util.put_line('INIT: Request ID = '||l_req_id);
903 fii_util.put_line('INIT: Budget Period Type = '||l_budget_time_unit);
904 fii_util.put_line('INIT: Forecast Period Type = '||l_forecast_time_unit);
905 fii_util.put_line(
906 '+---------------------------------------------------------------------+');
907 fii_util.put_line(' ');
908
909 ----------------------------------------------------------------------
910 -- If any of the above values is not set (except secondary currency).
911 -- Note that we will not error out when secondary currency is not set
912 -- because it is optional.
913 ----------------------------------------------------------------------
914 IF (l_user_id is NULL OR
915 l_login_id is NULL OR
916 l_req_id is NULL OR
917 l_prim_curr_code is NULL OR
918 l_prim_curr_mau is NULL OR
919 l_budget_time_unit is NULL OR
920 l_forecast_time_unit is NULL) THEN
921
922 fii_util.put_line(
923 'DIAGNOSIS: Please make sure all of the above setup information are defined.');
924
925 ELSE
926
927 fii_util.put_line('DIAGNOSIS: All mandatory setup information are defined.');
928 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
929 END IF;
930
931 fii_util.put_line(' ');
935 fii_util.put_line('INIT: Secondary Currency = '|| l_sec_curr_code);
932 fii_util.put_line('Listing of other setup information...');
933
934 -- Print out secondary currency info to logfile
936 fii_util.put_line('INIT: Secondary MAU = '|| l_sec_curr_mau);
937
938
939 -- Print out the Enterprise Calendar setup
940 l_period_set_name := bis_common_parameters.get_period_set_name;
941 l_period_type := bis_common_parameters.get_period_type;
942
943 fii_util.put_line('INIT: Enterprise Calendar = '|| l_period_set_name);
944 fii_util.put_line('INIT: Period Type = '|| l_period_type);
945
946 -- Print out if this is commercial or government install
947 l_industry_profile := FND_PROFILE.value('INDUSTRY');
948
949 fii_util.put_line('INIT: Industry profile = '|| l_industry_profile);
950
951 -- Print out budget/forecast source
952 l_budget_source := FND_PROFILE.value('FII_BUDGET_SOURCE');
953
954 fii_util.put_line('INIT: Budget/Forecast Source = '|| l_budget_source);
955 fii_util.put_line(' ');
956
957 IF (X_User IS NULL) THEN
958 fii_util.put_line('INIT: User Name parameter value is not provided.');
959 ELSE
960 fii_util.put_line('INIT: Parameter passed = ' || X_User);
961 END IF;
962
963 fii_util.put_line(' ');
964
965 -- Find out database version
966 SELECT banner
967 INTO l_db_version
968 FROM v$version
969 WHERE upper(banner) like 'ORACLE%';
970
971 fii_util.put_line('INIT: Database version = ' || l_db_version);
972
973 ----------------------------------------------------------------------
974 -- Generic Setup Validations
975 ----------------------------------------------------------------------
976 ----------------------------------------------------------------------
977 -- Check for GL Periods:
978 -- 1. Make sure one year before Global Start Date to sysdate have all
979 -- defined in Enterprise Calendar.
980 ----------------------------------------------------------------------
981 fii_util.put_line(' ');
982 fii_util.put_line(
983 '+---------------------------------------------------------------------+');
984 fii_util.put_line('Performing Check for: GL Periods');
985 fii_util.put_line(
986 '+---------------------------------------------------------------------+');
987 fii_util.put_line(
988 'START CHECK: Looking for GL Periods not defined within start and end range.');
989 fii_util.put_line(' ');
990
991 l_day := to_date(l_global_start_date, 'MM/DD/YYYY');
992 l_cnt := 0;
993
994 BEGIN
995 -- Find out the period year of the global start date
996 SELECT period_year
997 INTO l_year
998 FROM gl_periods a
999 WHERE a.period_set_name = l_period_set_name
1000 AND a.period_type = l_period_type
1001 AND a.adjustment_period_flag = 'N'
1002 AND l_day between a.start_date and a.end_date;
1003
1004 EXCEPTION
1005 WHEN no_data_found THEN
1006 fii_util.put_line(
1007 'Global start date is not defined in your Enterprise Calendar.');
1008 END;
1009
1010 BEGIN
1011 SELECT start_date
1012 INTO l_day
1013 FROM gl_periods
1014 WHERE period_set_name = l_period_set_name
1015 AND period_type = l_period_type
1016 AND period_num = 1
1017 AND period_year = l_year - 1;
1018
1019 EXCEPTION
1020 WHEN no_data_found THEN
1021 fii_util.put_line('The start date of the year (' || l_global_start_date || ') prior to global start date is not defined in your Enterprise Calendar.');
1022
1023 END;
1024
1025 -- Storing the year start day a year prior to global start date
1026 l_start_day := l_day;
1027
1028 fii_util.put_line('Start Range: ' || to_char(l_day, 'DD/MM/YYYY') );
1029 fii_util.put_line(' End Range: ' || to_char(l_sys_date, 'DD/MM/YYYY'));
1030
1031 WHILE l_day <= l_sys_date LOOP
1032 OPEN ent_period_cur(l_day);
1033 FETCH ent_period_cur INTO l_dummy;
1034
1035 IF ent_period_cur%NOTFOUND THEN
1036 IF (l_cnt = 0) THEN
1037 fii_util.put_line(' ');
1038 fii_util.put_line('General Ledger Date');
1039 fii_util.put_line('------------------------------------------');
1040 l_cnt := l_cnt + 1;
1041 END IF;
1042 fii_util.put_line(to_char(l_day, 'DD/MM/YYYY'));
1043 END IF;
1044
1045 CLOSE ent_period_cur;
1046 l_day := l_day + 1;
1047 END LOOP;
1048
1049 fii_util.put_line(' ');
1050 IF (l_cnt = 0) THEN
1051 -- All days between one year prior to global start date and sysdate have
1052 -- been defined in the Enterprise Calendar
1053 fii_util.put_line('All GL Periods between '||to_char(l_start_day, 'DD/MM/YYYY') ||
1054 ' and '||to_char(l_sys_date, 'DD/MM/YYYY') ||
1055 ' have been defined. ');
1056 fii_util.put_line('DIAGNOSIS: GL Periods are valid');
1057 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1058
1059 ELSE
1060 -- Print a message asking customer to define missing dates found
1061 fii_util.put_line(
1065 fii_util.put_line(' ');
1062 'DIAGNOSIS: Please define the above dates in your Enterprise Calendar.');
1063 END IF;
1064
1066 fii_util.put_line('END CHECK: Looking for GL Periods not defined.');
1067 fii_util.put_line(
1068 '+---------------------------------------------------------------------+');
1069
1070 ----------------------------------------------------------------------
1071 -- Check for GL Periods:
1072 -- 2. Also check that Fiscal Year are defined correctly
1073 ----------------------------------------------------------------------
1074 fii_util.put_line(
1075 'START CHECK: Fiscal Year contains valid number of weeks.');
1076 fii_util.put_line(' ');
1077
1078 -- Initialize l_min_start_date to be the minimum start date of the fiscal year
1079 BEGIN
1080
1081 SELECT year_start_date
1082 INTO l_min_start_date
1083 FROM gl_periods
1084 WHERE period_set_name = l_period_set_name
1085 AND period_type = l_period_type
1086 AND period_year = l_year - 1
1087 AND period_num = 1
1088 AND adjustment_period_flag = 'N';
1089
1090 l_cnt := 0;
1091 WHILE (l_min_start_date is NOT NULL and l_min_start_date <=l_sys_date) LOOP
1092
1093 OPEN fiscal_year_cur(l_year);
1094 FETCH fiscal_year_cur INTO l_min_start_date, l_max_end_date;
1095 l_fiscal_year_day := (l_max_end_date - l_min_start_date) +1;
1096
1097 IF (l_fiscal_year_day < 352 OR l_fiscal_year_day > 379) THEN
1098 l_cnt := l_cnt + 1;
1099 fii_util.put_line('FISCAL YEAR: '|| l_year || ' contains '||
1100 round(l_fiscal_year_day/7, 0) || ' weeks.');
1101
1102 ELSE
1103 fii_util.put_line('FISCAL YEAR: '|| l_year || ' contains '||
1104 round(l_fiscal_year_day/7, 0) ||
1105 ' weeks and is OK.');
1106 END IF;
1107
1108 CLOSE fiscal_year_cur;
1109 l_year := l_year + 1;
1110
1111 END LOOP;
1112
1113 fii_util.put_line(' ');
1114 IF (l_cnt > 0) THEN
1115 fii_util.put_line('DIAGNOSIS: Please make sure that the number of weeks in your Fiscal Years are between the ranges of 50 and 54 weeks. Otherwise, the DBI Update Time Dimension program will have issues.');
1116
1117 ELSE
1118 fii_util.put_line(
1119 'DIAGNOSIS: All Fiscal Years are between the ranges of 50 and 54 weeks');
1120 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1121 END IF;
1122
1123 EXCEPTION
1124 WHEN no_data_found THEN
1125 fii_util.put_line('The year prior to the global start date is not defined in your Enterprise Calendar.');
1126
1127 END;
1128
1129
1130 fii_util.put_line(' ');
1131 fii_util.put_line('END CHECK: Fiscal Year contains valid number of weeks.');
1132 fii_util.put_line(
1133 '+---------------------------------------------------------------------+');
1134
1135 ----------------------------------------------------------------------
1136 -- Check for Master Value Set.
1137 -- For each dimension, verify that we have a master value set assigned to it.
1138 ----------------------------------------------------------------------
1139 fii_util.put_line(' ');
1140 fii_util.put_line(
1141 '+---------------------------------------------------------------------+');
1142 fii_util.put_line('Performing Check for: Master value set assignment.');
1143
1144 -- Find out all the enabled dimensions and check if we
1145 -- have done mapping from local value sets to master value set
1146 OPEN enabled_dim_cur;
1147 LOOP
1148 FETCH enabled_dim_cur INTO l_dim_name, l_dim_short_name;
1149 EXIT WHEN enabled_dim_cur%NOTFOUND;
1150
1151 fii_util.put_line(' ');
1152 fii_util.put_line('--------------------------------------------------');
1153 fii_util.put_line('Dimension Enabled: '|| l_dim_name);
1154 fii_util.put_line('--------------------------------------------------');
1155
1156 -- Check if we have unmapped local value set for this dimension
1157 l_cnt := FII_SETUP_VAL_C.detect_unmapped_local_vs(l_dim_short_name);
1158
1159 fii_util.put_line(' ');
1160 IF l_cnt > 0 THEN
1164 fii_util.put_line(
1161 fii_util.put_line('DIAGNOSIS: Please map the above Local Value Sets to a Master Value Set');
1162
1163 ELSE
1165 'DIAGNOSIS: All Local Value Sets have been mapped to Master Value Sets');
1166 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1167 END IF;
1168
1169 END LOOP;
1170 CLOSE enabled_dim_cur;
1171
1172 fii_util.put_line(' ');
1173 fii_util.put_line('END CHECK: Master value set assignment.');
1174 fii_util.put_line(
1175 '+---------------------------------------------------------------------+');
1176
1177 ----------------------------------------------------------------------
1178 -- Check for Financial Category and Type Setup
1179 ----------------------------------------------------------------------
1180 fii_util.put_line(' ');
1181 fii_util.put_line(
1182 '+---------------------------------------------------------------------+');
1183 fii_util.put_line(
1184 'Performing Check for: Financial Category and Type Setup');
1185 fii_util.put_line(
1186 '+---------------------------------------------------------------------+');
1187 fii_util.put_line('START CHECK: Financial Category Types');
1188
1189 -- Find out if we have at least one node assigned per financial category type
1190 l_cnt := 0;
1191 OPEN fin_type_cur;
1192 LOOP
1193 FETCH fin_type_cur INTO l_fin_type_name;
1194 EXIT WHEN fin_type_cur%NOTFOUND;
1195
1196 IF (l_cnt = 0) THEN
1197 fii_util.put_line(' ');
1198 fii_util.put_line('Financial Category Type');
1199 fii_util.put_line('--------------------------------------------------');
1200 l_cnt := l_cnt + 1;
1201 END IF;
1202 fii_util.put_line(l_fin_type_name);
1203 END LOOP;
1204 CLOSE fin_type_cur;
1205
1206 fii_util.put_line(' ');
1207 IF l_cnt > 0 THEN
1208 fii_util.put_line(
1209 'DIAGNOSIS: There are no existing nodes for the above data type(s).');
1210
1211 ELSE
1212 fii_util.put_line(
1213 'DIAGNOSIS: All Financial Category have associated data types (Revenue, COGS,etc.)');
1214 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1215 END IF;
1216
1217 fii_util.put_line(' ');
1218 fii_util.put_line('END CHECK: Financial Category Dimension Types.');
1219 fii_util.put_line(
1220 '+---------------------------------------------------------------------+');
1221
1222 ----------------------------------------------------------------------
1223 -- Check for Duplicate Organization Setup
1224 -- Validate if there are more than one CCC for each company cost center
1225 ----------------------------------------------------------------------
1226 fii_util.put_line(' ');
1227 fii_util.put_line(
1228 '+---------------------------------------------------------------------+');
1229 fii_util.put_line('Performing Check for: More than one Organization for a Company Cost Center');
1230 fii_util.put_line(
1231 '+---------------------------------------------------------------------+');
1232 fii_util.put_line(
1233 'START CHECK: Duplicate Organization Assignments for Company Cost Center');
1234
1235 -- Find out if we have duplicate ccc org assigned to a company cost center
1236 l_cnt := FIND_DUP_CCC_ORG;
1237
1238 fii_util.put_line(' ');
1239 IF (l_cnt > 0 or l_cnt = -1)THEN
1240 fii_util.put_line('DIAGNOSIS: Please resolve the above duplicate Organization and Company Cost Center combinations.');
1241
1242 ELSE
1243 fii_util.put_line(
1244 'DIAGNOSIS: No duplicate Organization - Company Cost Centers found');
1245 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1246 END IF;
1247
1248 fii_util.put_line(' ');
1249 fii_util.put_line(
1250 'END CHECK: Duplicate Organization Assignments for Company Cost Center.');
1251 fii_util.put_line(
1252 '+---------------------------------------------------------------------+');
1253
1254 ----------------------------------------------------------------------
1255 -- Check for Missing Organization Setup
1256 -- Validate if there are company cost centers with no org assigned
1257 ----------------------------------------------------------------------
1258 fii_util.put_line(' ');
1259 fii_util.put_line(
1260 '+---------------------------------------------------------------------+');
1261 fii_util.put_line(
1262 'Performing Check for Organization - Company Cost Center Assignments');
1263 fii_util.put_line(
1264 '+---------------------------------------------------------------------+');
1265 fii_util.put_line('START CHECK: ORG - Company Cost Center Assignments.');
1266
1267 -- Find out if we have company cost centers with no org assigned
1268 l_cnt := 0;
1269 OPEN no_org_cur;
1270 LOOP
1271 FETCH no_org_cur INTO l_co_name, l_co_id, l_cc_name, l_cc_id;
1272 EXIT WHEN no_org_cur%NOTFOUND;
1273
1274 IF (l_cnt = 0) THEN
1275 fii_util.put_line(' ');
1276 fii_util.put_line(
1277 'Company Company ID Cost Center Cost Center ID ');
1278 fii_util.put_line(
1279 '------- ---------- ----------- -------------- ');
1280 l_cnt := l_cnt + 1;
1281 END IF;
1282 FII_UTIL.write_log(l_co_name || ' '|| l_co_id || ' '||
1283 l_cc_name || ' '|| l_cc_id);
1284 END LOOP;
1288 IF (l_cnt > 0 or l_cnt = -1)THEN
1285 CLOSE no_org_cur;
1286
1287 fii_util.put_line(' ');
1289 fii_util.put_line('DIAGNOSIS: Please assign an Organization to the Company Cost Centers listed above.');
1290
1291 ELSE
1292 fii_util.put_line('DIAGNOSIS: No missing Organization - Company Cost Centers found');
1293 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1294 END IF;
1295
1296 fii_util.put_line(' ');
1297 fii_util.put_line('END CHECK: ORG - Company Cost Center Assignments.');
1298 fii_util.put_line(
1299 '+---------------------------------------------------------------------+');
1300
1301 ----------------------------------------------------------------------
1302 -- Validate if there are company cost centers which only has company
1303 -- assigned but cost center is null
1304 ----------------------------------------------------------------------
1305 fii_util.put_line(' ');
1306 fii_util.put_line(
1307 '+---------------------------------------------------------------------+');
1308 fii_util.put_line(
1309 'Performing Check for Company Cost Center Organizations with only Company assigned');
1310 fii_util.put_line(
1311 '+---------------------------------------------------------------------+');
1312 fii_util.put_line('START CHECK: CCC Org with no Cost Center Assigned.');
1313
1314 -- Find out if we have company cost centers with no cost center assigned
1315 l_cnt := 0;
1316 OPEN org_no_cc_cur;
1317 LOOP
1318 FETCH org_no_cc_cur INTO l_co_id, l_co_name, l_org_id, l_org_name;
1319 EXIT WHEN org_no_cc_cur%NOTFOUND;
1320
1321 IF (l_cnt = 0) THEN
1322 fii_util.put_line(' ');
1323 fii_util.put_line(
1324 'Company ID Company Organization ID Organization');
1325 fii_util.put_line(
1326 '------- ---------- ----------- -------------- ');
1327 l_cnt := l_cnt + 1;
1328 END IF;
1329 FII_UTIL.write_log(l_co_id || ' '|| l_co_name || ' '||
1330 l_org_id || ' '|| l_org_name);
1331 END LOOP;
1332 CLOSE org_no_cc_cur;
1333
1334 fii_util.put_line(' ');
1335 IF (l_cnt > 0 or l_cnt = -1)THEN
1336 fii_util.put_line('DIAGNOSIS: Please assign Cost Centers to the above listed Organizations except for the place holder organizations.');
1337
1338 ELSE
1339 fii_util.put_line('DIAGNOSIS: No Company Cost Center Organizations with missing Cost Center values found.');
1340 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1341 END IF;
1342
1343 fii_util.put_line(' ');
1344 fii_util.put_line('END CHECK: CCC Org with no Cost Center Assigned.');
1345 fii_util.put_line(
1346 '+---------------------------------------------------------------------+');
1347
1348 ----------------------------------------------------------------------
1349 -- Validate that company cost center organizations have valid manager
1350 -- assigned to it.
1351 ----------------------------------------------------------------------
1352 fii_util.put_line(' ');
1353 fii_util.put_line(
1354 '+---------------------------------------------------------------------+');
1355 fii_util.put_line('Performing Check for Manager Assignments');
1356 fii_util.put_line(
1357 '+---------------------------------------------------------------------+');
1358 fii_util.put_line('START CHECK: Valid Manager Assignments.');
1359
1360 -- Find out if manager assignment dates are current or not
1361 l_cnt := 0;
1362 OPEN mgr_assgn_cur;
1363 LOOP
1364 FETCH mgr_assgn_cur INTO l_co_name, l_cc_name, l_org_id, l_org_name;
1365 EXIT WHEN mgr_assgn_cur%NOTFOUND;
1366
1367 IF (l_cnt = 0) THEN
1368 fii_util.put_line(' ');
1369 fii_util.put_line(
1370 'The following Company Cost Center Organizations does not have a valid manager assigned:');
1371 fii_util.put_line(
1372 'Company Cost Center Org ID Organization Name ');
1373 fii_util.put_line(
1374 '--------- ------------ -------- -----------------');
1375 l_cnt := l_cnt + 1;
1376 END IF;
1377 fii_util.put_line(l_co_name || ' ' || l_cc_name || ' ' ||
1378 l_org_id || ' ' || l_org_name);
1379 END LOOP;
1380 CLOSE mgr_assgn_cur;
1381
1382 fii_util.put_line(' ');
1383 IF (l_cnt > 0 or l_cnt = -1)THEN
1384 fii_util.put_line('DIAGNOSIS: Please make sure we have valid managers assigned to the above Company Cost Center Organizations.');
1385
1386 ELSE
1387 fii_util.put_line(
1388 'DIAGNOSIS: All Company Cost Center organizations with data have valid managers assigned');
1389 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1390 END IF;
1391
1392 fii_util.put_line(' ');
1393 fii_util.put_line('END CHECK: Valid Manager Assignments');
1394 fii_util.put_line(
1395 '+---------------------------------------------------------------------+');
1396
1397 fii_util.put_line(' ');
1398 fii_util.put_line('START CHECK: Valid Manager Assignment HR Orgs.');
1399
1400 -- Find out if manager assigned to HR organization has a valid
1401 -- effective date or not
1402 l_cnt := 0;
1403 OPEN mgr_eff_cur;
1404 LOOP
1405 FETCH mgr_eff_cur INTO l_mgr, l_org_name, l_eff_end;
1406 EXIT WHEN mgr_eff_cur%NOTFOUND;
1407
1408 IF (l_cnt = 0) THEN
1409 fii_util.put_line(' ');
1413 l_org_name || ''' is not current in HR, END DATE = '||
1410 END IF;
1411
1412 fii_util.put_line('MANAGER: '''|| l_mgr || ''' assigned to '''||
1414 l_eff_end );
1415 l_cnt := l_cnt + 1;
1416
1417 END LOOP;
1418 CLOSE mgr_eff_cur;
1419
1420 fii_util.put_line(' ');
1421 IF (l_cnt > 0 or l_cnt = -1)THEN
1422 fii_util.put_line('DIAGNOSIS: Please check the managers found above for valid employement dates.');
1423
1424 ELSE
1425 fii_util.put_line('DIAGNOSIS: All managers are active employees');
1426 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1427 END IF;
1428
1429 fii_util.put_line(' ');
1430 fii_util.put_line('END CHECK: Valid Manager Assignments HR Orgs');
1431 fii_util.put_line(
1432 '+---------------------------------------------------------------------+');
1433
1434 -- Find out if manager assigned in HR organization has a valid status
1435 -- in HR or not
1436 fii_util.put_line(' ');
1437 fii_util.put_line('START CHECK Valid Manager Assignment HR Status');
1438
1439 -- Find out if manager assignment dates are current or not
1440 l_cnt := 0;
1441 OPEN mgr_status_cur;
1442 LOOP
1443 FETCH mgr_status_cur INTO l_mgr, l_status;
1444 EXIT WHEN mgr_status_cur%NOTFOUND;
1445
1446 IF (l_cnt = 0) THEN
1447 fii_util.put_line(' ');
1448 END IF;
1449
1450 fii_util.put_line('MANAGER: ' || l_mgr ||
1451 ' is not an active employee in HR, STATUS = ' ||
1452 l_status);
1453 l_cnt := l_cnt + 1;
1454
1455 END LOOP;
1456 CLOSE mgr_status_cur;
1457
1458 fii_util.put_line(' ');
1459 IF (l_cnt > 0 or l_cnt = -1)THEN
1460 fii_util.put_line('DIAGNOSIS: Please check the managers found above for valid employment status.');
1461
1462 ELSE
1463 fii_util.put_line('DIAGNOSIS: All managers in HR are active');
1464 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1465 END IF;
1466
1467 fii_util.put_line(' ');
1468 fii_util.put_line('END CHECK: Valid Manager Assignment HR Status');
1469 fii_util.put_line(
1470 '+---------------------------------------------------------------------+');
1471
1472 ----------------------------------------------------------------------
1473 -- Validate if we have any transaction where the corresponding Company
1474 -- Cost Centers does not have a manager assigned to it yet.
1475 ----------------------------------------------------------------------
1476 fii_util.put_line(' ');
1477 fii_util.put_line(
1478 '+---------------------------------------------------------------------+');
1479 fii_util.put_line('Performing Check for: CCC has transactions but no manager assigned.');
1480 fii_util.put_line(
1481 '+---------------------------------------------------------------------+');
1482 -- Check if we have any transactions where CCC org is not yet mapped.
1483 fii_util.put_line('START CHECK: CCC has transactions but no manager assigned.');
1484 fii_util.put_line(' ');
1485
1486 -- Find out if we have CCC that has transactions but no manager assigned
1487 l_cnt := 0;
1488 OPEN trans_ccc_no_mgr_cur;
1489
1490 LOOP
1491 FETCH trans_ccc_no_mgr_cur INTO l_org_id, l_org_name, l_co_id, l_co_name,
1492 l_cc_id, l_cc_name;
1493 EXIT WHEN trans_ccc_no_mgr_cur%NOTFOUND;
1494
1495 IF (l_cnt = 0) THEN
1496 fii_util.put_line('The following Company Cost Center Organization has some GL transactions but their corresponding Company Cost Center Organization does not have a manager assigned to it.');
1497 fii_util.put_line(
1498 'Org ID Organization Company ID Company Cost Center ID Cost Center');
1499 fii_util.put_line(
1500 '------ --------------------- ---------- ------- -------------- -----------');
1501 l_cnt := l_cnt + 1;
1502 END IF;
1503 fii_util.put_line(l_org_id || ' '|| l_org_name || ' ' || l_co_id || ' ' ||
1504 l_co_name || ' '|| l_cc_id || ' ' || l_cc_name);
1505 END LOOP;
1506
1507 CLOSE trans_ccc_no_mgr_cur;
1508
1509 fii_util.put_line(' ');
1510 IF (l_cnt > 0 or l_cnt = -1)THEN
1511 fii_util.put_line('The above Organization has some GL transactions but their corresponding Organization does not have a manager assigned to it. Please review.');
1512
1513 ELSE
1514 fii_util.put_line('DIAGNOSIS: All company and cost center combinations with some GL transactions have a manager assigned to it already.');
1515 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1516 END IF;
1517
1518 fii_util.put_line(' ');
1519 fii_util.put_line('END CHECK: CCC has transactions but no manager assigned.');
1520 fii_util.put_line(
1521 '+---------------------------------------------------------------------+');
1522
1523 ----------------------------------------------------------------------
1524 -- Validate if we have any transaction where the corresponding Financial
1525 -- Categories not mapped using FDHM
1526 ----------------------------------------------------------------------
1527 fii_util.put_line(' ');
1528 fii_util.put_line(
1529 '+---------------------------------------------------------------------+');
1530 fii_util.put_line('Performing Check for: Transactions with FC not mapped');
1534 fii_util.put_line('START CHECK: Transactions with FC not mapped');
1531 fii_util.put_line(
1532 '+---------------------------------------------------------------------+');
1533 -- Check if there is any transactions where the corresponding FC is not mapped
1535 fii_util.put_line(' ');
1536
1537 -- Find out if we have transactions with FC not mapped
1538 l_cnt := 0;
1539 OPEN trans_no_fc_cur;
1540
1541 LOOP
1542 FETCH trans_no_fc_cur INTO l_vs_id, l_vs_name, l_fc_id, l_fc_name;
1543 EXIT WHEN trans_no_fc_cur%NOTFOUND;
1544
1545 IF (l_cnt = 0) THEN
1546 fii_util.put_line('The following Financial Categories has some GL transactions but their corresponding Financial Categories are not set up.');
1547 fii_util.put_line(
1548 'FC Value Set ID FC Value Set Name FC ID Financial Category');
1549 fii_util.put_line(
1550 '--------------- ---------------------------- ----- ---------------------');
1551 l_cnt := l_cnt + 1;
1552 END IF;
1553 fii_util.put_line(l_vs_id || ' ' || l_vs_name || ' ' || l_fc_id || ' ' || l_fc_name);
1554 END LOOP;
1555
1556 CLOSE trans_no_fc_cur;
1557
1558 fii_util.put_line(' ');
1559 IF (l_cnt > 0 or l_cnt = -1)THEN
1560 fii_util.put_line('DIAGNOSIS: The financial category in the above list have been found with a transactions in GL but have not setup the corresponding Financial Category yet. Please review.');
1561
1562 ELSE
1563 fii_util.put_line('DIAGNOSIS: All financial categories with a transactions have been mapped to a corresponding Financial Category');
1564 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1565 END IF;
1566
1567 fii_util.put_line(' ');
1568 fii_util.put_line('END CHECK: Transactions with FC not mapped');
1569 fii_util.put_line(
1570 '+---------------------------------------------------------------------+');
1571
1572 ----------------------------------------------------------------------
1573 -- Validate if we have any UDD1 values that has not been mapped
1574 ----------------------------------------------------------------------
1575 select dbi_enabled_flag
1576 into l_udd_enabled_flag
1577 from fii_financial_dimensions_v
1578 where dimension_short_name = 'FII_USER_DEFINED_1';
1579
1580 IF (l_udd_enabled_flag = 'Y') THEN
1581 fii_util.put_line(' ');
1582 fii_util.put_line(
1583 '+---------------------------------------------------------------------+');
1584 fii_util.put_line('Performing Check for: UDD1 setup in FDHM');
1585 fii_util.put_line(
1586 '+---------------------------------------------------------------------+');
1587 -- Check user defined dimension 1 hierarhichy setup
1588 fii_util.put_line('START CHECK: User-defined dimension 1 that has not been mapped in FDHM');
1589 -- Find out if we have transactions with UDD1 not mapped
1590 l_cnt := 0;
1591 OPEN unmapped_udd1_cur;
1592
1593 LOOP
1594 FETCH unmapped_udd1_cur INTO l_vs_id, l_vs_name, l_udd1_id, l_udd1_name;
1595 EXIT WHEN unmapped_udd1_cur%NOTFOUND;
1596
1597 IF (l_cnt = 0) THEN
1598 fii_util.put_line('The following User-defined dimension 1 values have not been mapped in the user-defined dimension hierarchy.');
1599 fii_util.put_line(
1600 'UDD1 Value Set ID UDD1 Value Set Name UDD1 ID UDD1');
1601 fii_util.put_line(
1602 '----------------- ---------------------------- ------- ---------------------');
1603 l_cnt := l_cnt + 1;
1604 END IF;
1605 fii_util.put_line(l_vs_id || ' ' || l_vs_name || ' ' || l_udd1_id || ' ' || l_udd1_name);
1606 END LOOP;
1607
1608 CLOSE unmapped_udd1_cur;
1609
1610 fii_util.put_line(' ');
1611 IF (l_cnt > 0 or l_cnt = -1)THEN
1612 fii_util.put_line('DIAGNOSIS: The user-defined dimension 1 values in the above list have not been mapped in the UDD1 hierarchy yet. Please review.');
1613
1614 ELSE
1615 fii_util.put_line('DIAGNOSIS: All user-defined dimension 1 values in the above list have been mapped in the UDD1 hierarchy.');
1616 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1617 END IF;
1618
1619 fii_util.put_line(' ');
1620
1621 fii_util.put_line(' ');
1622 fii_util.put_line('END CHECK: User-defined dimension 1 set up in FDHM');
1623 fii_util.put_line(
1624 '+---------------------------------------------------------------------+');
1625 ELSE
1626 fii_util.put_line(' ');
1627 fii_util.put_line(
1628 '+---------------------------------------------------------------------+');
1629 fii_util.put_line('User-defined dimension 1 is not enabled. ');
1630 fii_util.put_line('Skipping the check for UDD1 setup.');
1631 fii_util.put_line(' ');
1632 END IF;
1633
1634 ----------------------------------------------------------------------
1635 -- Validate if we have any UDD2 values that has not been mapped
1636 ----------------------------------------------------------------------
1637 select dbi_enabled_flag
1638 into l_udd_enabled_flag
1639 from fii_financial_dimensions_v
1640 where dimension_short_name = 'FII_USER_DEFINED_2';
1641
1642 IF (l_udd_enabled_flag = 'Y') THEN
1643 fii_util.put_line(' ');
1644 fii_util.put_line(
1648 '+---------------------------------------------------------------------+');
1645 '+---------------------------------------------------------------------+');
1646 fii_util.put_line('Performing Check for: UDD2 setup in FDHM');
1647 fii_util.put_line(
1649 -- Check user defined dimension 2 hierarhichy setup
1650 fii_util.put_line('START CHECK: User-defined dimension 2 that has not been mapped in FDHM');
1651
1652 -- Find out if we have transactions with UDD2 not mapped
1653 l_cnt := 0;
1654 OPEN unmapped_udd2_cur;
1655
1656 LOOP
1657 FETCH unmapped_udd2_cur INTO l_vs_id, l_vs_name, l_udd2_id, l_udd2_name;
1658 EXIT WHEN unmapped_udd2_cur%NOTFOUND;
1659
1660 IF (l_cnt = 0) THEN
1661 fii_util.put_line('The following User-defined dimension 2 values have not been mapped in the user-defined dimension hierarchy.');
1662 fii_util.put_line(
1663 'UDD2 Value Set ID UDD2 Value Set Name UDD2 ID UDD2');
1664 fii_util.put_line(
1665 '----------------- ---------------------------- ------- ---------------------');
1666 l_cnt := l_cnt + 1;
1667 END IF;
1668 fii_util.put_line(l_vs_id || ' ' || l_vs_name || ' ' || l_udd2_id || ' ' || l_udd2_name);
1669 END LOOP;
1670
1671 CLOSE unmapped_udd2_cur;
1672
1673 fii_util.put_line(' ');
1674 IF (l_cnt > 0 or l_cnt = -1)THEN
1675 fii_util.put_line('DIAGNOSIS: The user-defined dimension 2 values in the above list have not been mapped in the UDD2 hierarchy yet. Please review.');
1676
1677 ELSE
1678 fii_util.put_line('DIAGNOSIS: All user-defined dimension 2 values in the above list have been mapped in the UDD2 hierarchy.');
1679 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1680 END IF;
1681
1682 fii_util.put_line(' ');
1683
1684 fii_util.put_line(' ');
1685 fii_util.put_line('END CHECK: User-defined dimension 2 set up in FDHM');
1686 fii_util.put_line(
1687 '+---------------------------------------------------------------------+');
1688 ELSE
1689 fii_util.put_line(' ');
1690 fii_util.put_line(
1691 '+---------------------------------------------------------------------+');
1692 fii_util.put_line('User-defined dimension 2 is not enabled. ');
1693 fii_util.put_line('Skipping the check for UDD2 setup.');
1694 fii_util.put_line(' ');
1695 END IF;
1696
1697 ----------------------------------------------------------------------
1698 -- Validate if we have any Financial Category without a financial
1699 -- category type assigned.
1700 ----------------------------------------------------------------------
1701 fii_util.put_line(' ');
1702 fii_util.put_line(
1703 '+---------------------------------------------------------------------+');
1704 fii_util.put_line('Performing Check for: Financial Category without a financial category type.');
1705 fii_util.put_line(
1706 '+---------------------------------------------------------------------+');
1707 -- Check the user security profile
1708 fii_util.put_line('START CHECK: Financial Category without a financial category type.');
1709 -- Find out if we have transactions with FC not mapped
1710 l_cnt := 0;
1711 OPEN fc_no_fctype_cur;
1712
1713 LOOP
1714 FETCH fc_no_fctype_cur INTO l_vs_id, l_vs_name, l_fc_id, l_fc_name;
1715 EXIT WHEN fc_no_fctype_cur%NOTFOUND;
1716
1717 IF (l_cnt = 0) THEN
1718 fii_util.put_line('The following Financial Categories has some GL transactions but their corresponding financial categoriy types are not set up.');
1719 fii_util.put_line(
1720 'FC Value Set ID FC Value Set Name FC ID Financial Category');
1721 fii_util.put_line(
1722 '--------------- ---------------------------- ----- ---------------------');
1723 l_cnt := l_cnt + 1;
1724 END IF;
1725 fii_util.put_line(l_vs_id || ' ' || l_vs_name || ' ' || l_fc_id || ' ' || l_fc_name);
1726 END LOOP;
1727
1728 CLOSE fc_no_fctype_cur;
1729
1730 fii_util.put_line(' ');
1731 IF (l_cnt > 0 or l_cnt = -1)THEN
1732 fii_util.put_line('DIAGNOSIS: The financial category in the above list have been found with a transactions in GL but have not setup the corresponding Financial Category yet. Please review.');
1733
1734 ELSE
1735 fii_util.put_line('DIAGNOSIS: All financial categories with a transactions have has their corresponding financial category and financial category types set up');
1736 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1737 END IF;
1738
1739 fii_util.put_line(' ');
1740
1741 fii_util.put_line(' ');
1742 fii_util.put_line('END CHECK: Financial Category without a financial category type.');
1743 fii_util.put_line(
1744 '+---------------------------------------------------------------------+');
1745
1746 ----------------------------------------------------------------------
1747 -- Validate encumbrance type mappings for PSI customers (R12 only)
1748 ----------------------------------------------------------------------
1749 SELECT count(*)
1750 INTO l_cnt
1751 FROM sys.dba_objects
1752 WHERE object_name = 'FII_ENCUM_TYPE_MAPPINGS';
1753
1754 IF ((l_cnt > 0) AND (l_industry_profile = 'G')) THEN
1755 fii_util.put_line(' ');
1756 fii_util.put_line(
1760 '+---------------------------------------------------------------------+');
1757 '+---------------------------------------------------------------------+');
1758 fii_util.put_line('Performing Check for: Encumbrance Type Mappings');
1759 fii_util.put_line(
1761 -- Check if encumbrance type mappings have been defined.
1762 fii_util.put_line('START CHECK: Encumbrance Type Mappings');
1763
1764 SELECT count(*)
1765 INTO l_cnt
1766 FROM fnd_lookup_values a,
1767 gl_encumbrance_types b
1768 WHERE a.lookup_type in ( 'FII_PSI_ENCUM_TYPES_OBLIGATION',
1769 'FII_PSI_ENCUM_TYPES_COMMITMENT')
1770 AND a.view_application_id = 450
1771 AND a.language = userenv('LANG')
1772 AND upper(a.lookup_code) = upper(b.encumbrance_type);
1773
1774 IF (l_cnt = 0) THEN
1775 fii_util.put_line('DIAGNOSIS: Encumbrance Type Mappings has not been defined yet. Please define them before running the Funds Management reports.');
1776 ELSE
1777 fii_util.put_line('DIAGNOSIS: Some valid encumbrance type mappings have been defined. NO ACTION is needed.');
1778 END IF;
1779
1780 -- Check for invalid encumbrance type mappings defined
1781 l_cnt := 0;
1782 OPEN invalid_lookup_cur;
1783 LOOP
1784 FETCH invalid_lookup_cur INTO l_lookup_code, l_lookup_type;
1785 EXIT WHEN invalid_lookup_cur%NOTFOUND;
1786
1787 IF (l_cnt = 0) THEN
1788 fii_util.put_line(
1789 'DIAGNOSIS: Invalid lookup codes found in the encumbrance type mappings. ');
1790 fii_util.put_line('Lookup Type Lookup Code ');
1791 fii_util.put_line('----------- -----------');
1792 l_cnt := l_cnt + 1;
1793 END IF;
1794
1795 fii_util.put_line(l_lookup_code ||' ' || l_lookup_type);
1796 END LOOP;
1797 CLOSE invalid_lookup_cur;
1798
1799 IF (l_cnt = 0) THEN
1800 fii_util.put_line('DIAGNOSIS: No invalid lookup codes found in the encumbrance type mappings. NO ACTION is needed.');
1801 END IF;
1802
1803 fii_util.put_line(' ');
1804 fii_util.put_line('END CHECK: Encumbrance Type Mappings');
1805 fii_util.put_line(
1806 '+---------------------------------------------------------------------+');
1807 END IF;
1808
1809 ----------------------------------------------------------------------
1810 -- Validate if the MV has any data
1811 ----------------------------------------------------------------------
1812 fii_util.put_line(' ');
1813 fii_util.put_line(
1814 '+---------------------------------------------------------------------+');
1815 fii_util.put_line(
1816 'Performing Check to see if MVs needed for FII reports have data.');
1817
1818 -- Find out if MV has data for FII reports
1819 -- MVs for GL
1820 fii_util.put_line(
1821 '+---------------------------------------------------------------------+');
1822 fii_util.put_line('START CHECK: Data in GL MV needed for FII reports');
1823
1824 l_cnt := 0;
1825 OPEN gl_mv_cur;
1826 LOOP
1827 FETCH gl_mv_cur INTO l_mv_name;
1828 EXIT WHEN gl_mv_cur%NOTFOUND;
1829
1830 l_stmt := 'SELECT COUNT(*) FROM '|| l_mv_name;
1831 execute immediate l_stmt INTO l_mv_row_ct;
1832
1833 IF (l_mv_row_ct = 0) THEN
1834 l_cnt := l_cnt + 1;
1835 fii_util.put_line(' The MV '''||l_mv_name||''' has no data.');
1836 END IF;
1837 END LOOP;
1838 CLOSE gl_mv_cur;
1839
1840 IF (l_cnt = 0) THEN
1841 fii_util.put_line('DIAGNOSIS:All GL MVs have data. NO ACTION is needed.');
1842 ELSE
1843 fii_util.put_line(
1844 'DIAGNOSIS: Please try to refresh the above MV(s) manually.');
1845 END IF;
1846
1847 fii_util.put_line(' ');
1848 fii_util.put_line('END CHECK: Data in GL MV needed for FII reports');
1849 fii_util.put_line(
1850 '+---------------------------------------------------------------------+');
1851
1852 -- MVs for AP
1853 fii_util.put_line(
1854 '+---------------------------------------------------------------------+');
1855 fii_util.put_line('START CHECK: Data in AP MV needed for FII reports');
1856
1857 l_cnt := 0;
1858 OPEN ap_mv_cur;
1859 LOOP
1860 FETCH ap_mv_cur INTO l_mv_name;
1861 EXIT WHEN ap_mv_cur%NOTFOUND;
1862
1863 l_stmt := 'SELECT COUNT(*) FROM '|| l_mv_name;
1864 execute immediate l_stmt INTO l_mv_row_ct;
1865
1866 IF (l_mv_row_ct = 0) THEN
1867 l_cnt := l_cnt + 1;
1868 fii_util.put_line(' The MV '''|| l_mv_name || ''' has no data.');
1869 END IF;
1870 END LOOP;
1871 CLOSE ap_mv_cur;
1872
1873 IF (l_cnt = 0) THEN
1874 fii_util.put_line('DIAGNOSIS:All AP MVs have data. NO ACTION is needed.');
1875 ELSE
1876 fii_util.put_line(
1877 'DIAGNOSIS: Please try to refresh the above MV(s) manually.');
1878 END IF;
1879
1880 fii_util.put_line(' ');
1881 fii_util.put_line('END CHECK: Data in AP needed for FII reports');
1882 fii_util.put_line(
1883 '+---------------------------------------------------------------------+');
1884
1885 -- MVs for AR
1886 fii_util.put_line(
1887 '+---------------------------------------------------------------------+');
1891 OPEN ar_mv_cur;
1888 fii_util.put_line('START CHECK: Data in AR MV needed for FII reports');
1889
1890 l_cnt := 0;
1892 LOOP
1893 FETCH ar_mv_cur INTO l_mv_name;
1894 EXIT WHEN ar_mv_cur%NOTFOUND;
1895
1896 l_stmt := 'SELECT COUNT(*) FROM '|| l_mv_name;
1897 execute immediate l_stmt INTO l_mv_row_ct;
1898
1899 IF (l_mv_row_ct = 0) THEN
1900 l_cnt := l_cnt + 1;
1901 fii_util.put_line(' The MV '''|| l_mv_name || ''' has no data.');
1902 END IF;
1903 END LOOP;
1904 CLOSE ar_mv_cur;
1905
1906 IF (l_cnt = 0) THEN
1907 fii_util.put_line('DIAGNOSIS:All AR MVs have data. NO ACTION is needed.');
1908 ELSE
1909 fii_util.put_line(
1910 'DIAGNOSIS: Please try to refresh the above MV(s) manually.');
1911 END IF;
1912
1913 fii_util.put_line(' ');
1914 fii_util.put_line('END CHECK: Data in AR needed for FII reports');
1915 fii_util.put_line(
1916 '+---------------------------------------------------------------------+');
1917
1918 ----------------------------------------------------------------------
1919 -- Validate Dimension for Dashboards that are enabled
1920 ----------------------------------------------------------------------
1921 fii_util.put_line(' ');
1922 fii_util.put_line(
1923 '+---------------------------------------------------------------------+');
1924 fii_util.put_line('Performing Dashboard Check for:Dashboard Requirements');
1925 fii_util.put_line(
1926 '+---------------------------------------------------------------------+');
1927 fii_util.put_line(
1928 'START CHECK: Profit and Loss, Profit and Loss by Manager');
1929
1930 -- Check if Profit and Loss, Profit and Loss by Manager Dashboard
1931 -- is enabled or not
1932 SELECT max(implementation_flag)
1933 INTO l_enabled_flag
1934 FROM bis_obj_properties
1935 WHERE object_name in ('FII_GL_PROFIT_AND_LOSS_PAGE', 'FII_PL_BY_MGR_PAGE')
1936 and object_type = 'PAGE';
1937
1938 fii_util.put_line('Dashboard implementation flag = ' || l_enabled_flag );
1939
1940 -- Find out dimensions enabled for each dashboard
1941 IF (l_enabled_flag = 'Y') THEN
1942 l_cnt := 0;
1943 l_dashboard_warn_flag := 'N';
1944
1945 OPEN dim_enabled_cur1;
1946
1947 LOOP
1948 FETCH dim_enabled_cur1
1949 INTO l_dim_name, l_dim_short_name, l_enabled_flag;
1950
1951 EXIT WHEN dim_enabled_cur1%NOTFOUND;
1952
1953 IF (l_cnt = 0) THEN
1954 fii_util.put_line(' ');
1955 fii_util.put_line('Supporting Dimensions Enabled / Disabled? ');
1956 fii_util.put_line('------------------------- --------------------');
1957 l_cnt := l_cnt + 1;
1958 END IF;
1959 fii_util.put_line(l_dim_name || ' '|| l_enabled_flag);
1960
1961 -- Check if the required dimension is disabled
1962 IF (l_dim_short_name = 'GL_FII_FIN_ITEM' and l_enabled_flag = 'N') THEN
1963 l_dashboard_warn_flag := 'Y';
1964 END IF;
1965 END LOOP;
1966
1967 CLOSE dim_enabled_cur1;
1968
1969 fii_util.put_line(' ');
1970 IF (l_dashboard_warn_flag = 'Y') THEN
1971 fii_util.put_line('DIAGNOSIS: The Financial Category Dimension is required for the Profit and Loss Dashboards. Please make sure it is enabled.');
1972
1973 ELSE
1974 fii_util.put_line('DIAGNOSIS: The required dimension (Financial Category) has been enabled for the Profit and Loss Dashboards.');
1975 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
1976 END IF;
1977
1978 ELSE
1979 fii_util.put_line('Skipping check for Profit and Loss, Profit and Loss by Manager page as they are not implementated.');
1980 END IF;
1981
1982 fii_util.put_line(' ');
1983 fii_util.put_line(
1984 'END CHECK: Profit and Loss, Profit and Loss by Manager.');
1985 fii_util.put_line(
1986 '+---------------------------------------------------------------------+');
1987 fii_util.put_line(' ');
1988
1989 fii_util.put_line('START CHECK: Expense Management.');
1990
1991 -- Check if Expense Management Dashboard is enabled or not
1992 SELECT implementation_flag
1993 INTO l_enabled_flag
1994 FROM bis_obj_properties
1995 WHERE object_name = 'FII_EXP_MGMT_PAGE_P'
1996 and object_type = 'PAGE';
1997
1998 fii_util.put_line('Dashboard implementation flag = ' || l_enabled_flag );
1999
2000 -- Find out dimensions enabled for each dashboard
2001 IF (l_enabled_flag = 'Y') THEN
2002 l_cnt := 0;
2003 l_dashboard_warn_flag := 'N';
2004
2005 OPEN dim_enabled_cur1;
2006
2007 LOOP
2008 FETCH dim_enabled_cur1
2009 INTO l_dim_name, l_dim_short_name, l_enabled_flag;
2010
2011 EXIT WHEN dim_enabled_cur1%NOTFOUND;
2012
2013 IF (l_cnt = 0) THEN
2014 fii_util.put_line(' ');
2015 fii_util.put_line('Supporting Dimensions Enabled / Disabled? ');
2016 fii_util.put_line('------------------------- --------------------');
2017 l_cnt := l_cnt + 1;
2018 END IF;
2019 fii_util.put_line(l_dim_name || ' '|| l_enabled_flag);
2020
2024 END IF;
2021 -- Check if the required dimension is disabled
2022 IF (l_dim_short_name = 'GL_FII_FIN_ITEM' and l_enabled_flag = 'N') THEN
2023 l_dashboard_warn_flag := 'Y';
2025 END LOOP;
2026
2027 CLOSE dim_enabled_cur1;
2028
2029 fii_util.put_line(' ');
2030 IF (l_dashboard_warn_flag = 'Y') THEN
2031 fii_util.put_line('DIAGNOSIS: The Financial Category Dimension is required for the Expense Management Dashboards. Please make sure it is enabled.');
2032
2033 ELSE
2034 fii_util.put_line('DIAGNOSIS: The required dimension (Financial Category) has been enabled for the Expense Management Dashboards.');
2035 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
2036 END IF;
2037
2038 ELSE
2039 fii_util.put_line('Skipping check for Expense Management page as they are not implementated');
2040 END IF;
2041
2042 fii_util.put_line(' ');
2043 fii_util.put_line('END CHECK: Expense Management.');
2044 fii_util.put_line(
2045 '+---------------------------------------------------------------------+');
2046 fii_util.put_line(' ');
2047
2048 fii_util.put_line('START CHECK: Expense Analysis.');
2049
2050 -- Check if Expense Analysis Dashboard is enabled or not
2051 SELECT implementation_flag
2052 INTO l_enabled_flag
2053 FROM bis_obj_properties
2054 WHERE object_name = 'FII_EA_EXPENSE_ANALYSIS_PAGE'
2055 and object_type = 'PAGE';
2056
2057 fii_util.put_line('Dashboard implementation flag = ' || l_enabled_flag );
2058
2059 -- Find out dimensions enabled for each dashboard
2060 IF (l_enabled_flag = 'Y') THEN
2061 l_cnt := 0;
2062 l_dashboard_warn_flag := 'N';
2063
2064 OPEN dim_enabled_cur2;
2065
2066 LOOP
2067 FETCH dim_enabled_cur2
2068 INTO l_dim_name, l_dim_short_name, l_enabled_flag;
2069
2070 EXIT WHEN dim_enabled_cur2%NOTFOUND;
2071
2072 IF (l_cnt = 0) THEN
2073 fii_util.put_line(' ');
2074 fii_util.put_line('Supporting Dimensions Enabled / Disabled? ');
2075 fii_util.put_line('------------------------- --------------------');
2076 l_cnt := l_cnt + 1;
2077 END IF;
2078 fii_util.put_line(l_dim_name || ' '|| l_enabled_flag);
2079
2080 -- Check if the required dimension is disabled
2081 IF (l_dim_short_name IN ('FII_COMPANIES', 'HRI_CL_ORGCC', 'GL_FII_FIN_ITEM')
2082 and l_enabled_flag = 'N') THEN
2083 l_dashboard_warn_flag := 'Y';
2084 END IF;
2085
2086 END LOOP;
2087
2088 CLOSE dim_enabled_cur2;
2089
2090 fii_util.put_line(' ');
2091 IF (l_dashboard_warn_flag = 'Y') THEN
2092 fii_util.put_line('DIAGNOSIS: Company, Cost Center and Financial Category Dimensions are required for the Expense Analysis Dashboard. Please make sure they are enabled.');
2093
2094 ELSE
2095 fii_util.put_line('DIAGNOSIS: The required dimensions (Company, Cost Center and Financial Category) have been enabled for the Expense Analysis Dashboards.');
2096 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
2097 END IF;
2098
2099 ELSE
2100 fii_util.put_line('Skipping check for Expense Analysis page as they are not implementated');
2101 END IF;
2102
2103 fii_util.put_line(' ');
2104 fii_util.put_line('END CHECK: Expense Analysis.');
2105 fii_util.put_line(
2106 '+---------------------------------------------------------------------+');
2107 fii_util.put_line(' ');
2108
2109 fii_util.put_line('START CHECK: Funds Management.');
2110
2111 -- Check if Funds Management Dashboard is enabled or not
2112 SELECT implementation_flag
2113 INTO l_enabled_flag
2114 FROM bis_obj_properties
2115 WHERE object_name = 'FII_PSI_FUNDS_MANAGEMENT_PAGE'
2116 and object_type = 'PAGE';
2117
2118 fii_util.put_line('Dashboard implementation flag = ' || l_enabled_flag );
2119
2120 -- Find out dimensions enabled for each dashboard
2121 IF (l_enabled_flag = 'Y') THEN
2122 l_cnt := 0;
2123 l_dashboard_warn_flag := 'N';
2124
2125 OPEN dim_enabled_cur2;
2126
2127 LOOP
2128 FETCH dim_enabled_cur2
2129 INTO l_dim_name, l_dim_short_name, l_enabled_flag;
2130
2131 EXIT WHEN dim_enabled_cur2%NOTFOUND;
2132
2133 IF (l_cnt = 0) THEN
2134 fii_util.put_line(' ');
2135 fii_util.put_line('Supporting Dimensions Enabled / Disabled? ');
2136 fii_util.put_line('------------------------- --------------------');
2137 l_cnt := l_cnt + 1;
2138 END IF;
2139 fii_util.put_line(l_dim_name || ' '|| l_enabled_flag);
2140
2141 -- Check if the required dimension is disabled
2142 IF (l_dim_short_name IN ('FII_COMPANIES', 'HRI_CL_ORGCC', 'GL_FII_FIN_ITEM')
2143 and l_enabled_flag = 'N') THEN
2144 l_dashboard_warn_flag := 'Y';
2145 END IF;
2146
2147 END LOOP;
2148
2149 CLOSE dim_enabled_cur2;
2150
2151 fii_util.put_line(' ');
2152 IF (l_dashboard_warn_flag = 'Y') THEN
2153 fii_util.put_line('DIAGNOSIS: Company, Cost Center and Financial Category Dimensions are required for the Funds Management Dashboard. Please make sure they are enabled.');
2154
2155 ELSE
2156 fii_util.put_line('DIAGNOSIS: The required dimensions (Company, Cost Center and Financial Category) have been enabled for the Funds Management Dashboards.');
2157 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
2158 END IF;
2159
2160 ELSE
2161 fii_util.put_line('Skipping check for Funds Management page as they are not implementated');
2162 END IF;
2163
2164 fii_util.put_line(' ');
2165 fii_util.put_line('END CHECK: Funds Management.');
2166 fii_util.put_line(
2167 '+---------------------------------------------------------------------+');
2168
2169 ----------------------------------------------------------------------
2170 -- Validate Profile Option Settings
2174 '+---------------------------------------------------------------------+');
2171 ----------------------------------------------------------------------
2172 fii_util.put_line(' ');
2173 fii_util.put_line(
2175 fii_util.put_line('Performing Check for: Profile Options');
2176 fii_util.put_line(
2177 '+---------------------------------------------------------------------+');
2178 -- Check the budget profile option
2179 fii_util.put_line('START CHECK: Budget Profile Check');
2180 fii_util.put_line(' ');
2181
2182 l_budget_source := FND_PROFILE.value('FII_BUDGET_SOURCE');
2183 l_budget_time_unit := FND_PROFILE.Value('FII_BUDGET_TIME_UNIT');
2184 l_forecast_time_unit := FND_PROFILE.Value('FII_FORECAST_TIME_UNIT');
2185
2186 fii_util.put_line('The profile option ''FII: Budget/Foreacst Source'' = '
2187 || l_budget_source);
2188
2189 fii_util.put_line('The profile option ''FII: Budget Period Type'' = '
2190 || l_budget_time_unit);
2191
2192 fii_util.put_line('The profile option ''FII: Forecast Period Type'' = '
2193 || l_forecast_time_unit );
2194
2195 fii_util.put_line(' ');
2196 IF ( l_budget_source IS NULL OR l_budget_time_unit IS NULL
2197 OR l_forecast_time_unit IS NULL) THEN
2198 fii_util.put_line('Diagnosis: Warning - some of the above budget profile option has not been set. Please make sure to set the profile options listed.');
2199
2200 ELSE
2201 fii_util.put_line('DIAGNOSIS: Budget profile options have been set correctly');
2202 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
2203 END IF;
2204
2205 fii_util.put_line(' ');
2206 fii_util.put_line('END CHECK: Budget Profile Check');
2207 fii_util.put_line(
2208 '+---------------------------------------------------------------------+');
2209
2210 -- Check the Industry option
2211 fii_util.put_line(' ');
2212 fii_util.put_line('START CHECK: Industry Profile Option Check');
2213 fii_util.put_line(' ');
2214
2215 l_profile_value := FND_PROFILE.value('INDUSTRY');
2216
2217 fii_util.put_line('The profile option ''Industry'' = '
2218 || l_profile_value);
2219
2220 fii_util.put_line(' ');
2221 IF (l_profile_value = 'G') THEN
2222 fii_util.put_line('DIAGNOSIS: The ''Industry'' profile option has been set to ''Government''. The Company dimension will be renamed to ''Fund''. ');
2223 fii_util.put_line('If you are not using the Funds Management Dashboard, it is recommended to set the ''Industry'' profile option to '' ''');
2224
2225 ELSE
2226 fii_util.put_line(
2227 'DIAGNOSIS: The ''Industry'' profile option has been defined correctly.');
2228 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
2229 END IF;
2230
2231 fii_util.put_line(' ');
2232 fii_util.put_line('END CHECK: Industry Profile Option Check');
2233 fii_util.put_line(
2234 '+---------------------------------------------------------------------+');
2235
2236 -- Check the Debug mode option
2237 fii_util.put_line(' ');
2238 fii_util.put_line('START CHECK: ''FII: Debug Mode'' Profile Option Check');
2239 fii_util.put_line(' ');
2240
2241 l_profile_value := FND_PROFILE.value('FII_DEBUG_MODE');
2242
2243 IF (l_profile_value IS NOT NULL) THEN
2244 fii_util.put_line('The profile option ''FII: Debug Mode'' = '
2245 || l_profile_value);
2246 ELSE
2247 fii_util.put_line('The profile option ''FII: Debug Mode'' has not been set up.');
2248 END IF;
2249
2250 fii_util.put_line(' ');
2251 IF (l_profile_value = 'Y') THEN
2252 fii_util.put_line('DIAGNOSIS: The ''FII:Debug Mode'' profile option is set to YES. It is recommended that this profile option is set to ''No''.');
2253
2254 ELSE
2255 fii_util.put_line('DIAGNOSIS: NO ACTION is needed.');
2256 END IF;
2257
2258 fii_util.put_line(' ');
2259 fii_util.put_line('END CHECK: ''FII:Debug Mode'' Profile Option Check');
2260 fii_util.put_line(
2261 '+---------------------------------------------------------------------+');
2262
2263 -- Perform user setup validations if a username parameter is passed in
2264 IF (X_User IS NOT NULL) THEN
2265 VALIDATE_USER_SETUP(X_User);
2266 END IF;
2267
2268 -----------------------------------------------------------------------
2269 -- Print report footer in logfile
2270 -----------------------------------------------------------------------
2271 -- Retrive current system time as the end time of the program
2272 SELECT to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')
2273 INTO l_end_time
2274 FROM dual;
2275
2276 fii_util.put_line(' ');
2277 fii_util.put_line('** Diagnostics Ends ** '|| l_end_time);
2278 fii_util.put_line(
2279 '** End of log messages from DBI Financials Validation **');
2280
2281 fii_util.put_line(' ');
2282 fii_util.put_line(
2283 '+=====================================================================+');
2284 fii_util.put_line('+ End of DBI Financials Diagnostics ');
2285 fii_util.put_line(
2286 '+ DBI_DIAGNOSTIC module: Daily Business Intelligence Diagnostic Program');
2287 fii_util.put_line('+ Current System Time is '|| l_end_time);
2288 fii_util.put_line(
2289 '+=====================================================================+');
2290
2291 END Main;
2292
2293
2294 END FII_SETUP_VAL_C;
[Home] [Help]