[Home] [Help]
PACKAGE BODY: APPS.FII_EXCEPTION_CHECK_PKG
Source
1 PACKAGE BODY FII_EXCEPTION_CHECK_PKG AS
2 /* $Header: FIIGLECB.pls 120.1 2005/10/30 05:13:20 appldev noship $ */
3
4 g_debug_flag varchar2(1) :=
5 nvl( fnd_profile.value( 'FII_DEBUG_MODE' ), 'N' );
6
7 ----------------------------------------
8 -- function check_slg_setup
9 ----------------------------------------
10 FUNCTION check_slg_setup RETURN NUMBER IS
11
12 l_count number;
13
14 BEGIN
15
16 if g_debug_flag = 'Y' then
17 FII_MESSAGE.Func_Ent('FII_EXCEPTION_CHECK_PKG.check_slg_setup');
18 end if;
19
20 select count( distinct ledger_id ) into l_count
21 from fii_slg_assignments slga, fii_source_ledger_groups fslg
22 where slga.source_ledger_group_id = fslg.source_ledger_group_id
23 and fslg.usage_code = 'DBI';
24
25 if l_count > 0 then
26
27 if g_debug_flag = 'Y' then
28 FII_MESSAGE.Func_Succ('FII_EXCEPTION_CHECK_PKG.check_slg_setup');
29 end if;
30
31 return 0;
32 end if;
33
34 -- No source ledger(s) setup for DBI
35
36 fii_message.write_log( msg_name => 'FII_NO_SLG_SETUP',
37 token_num => 0 );
38
39 fii_message.write_output( msg_name => 'FII_NO_SLG_SETUP',
40 token_num => 0 );
41
42 if g_debug_flag = 'Y' then
43 FII_MESSAGE.Func_Fail('FII_EXCEPTION_CHECK_PKG.check_slg_setup');
44 end if;
45
46 return 1;
47
48 END check_slg_setup;
49
50 FUNCTION detect_unmapped_local_vs( p_dim_short_name VARCHAR2 ) RETURN NUMBER IS
51
52 l_master_vs_id NUMBER(15);
53
54 cursor missing_csr is
55 select
56 fvs.flex_value_set_name vs_name,
57 ifs.id_flex_structure_name coa_name
58 from ( select distinct sas.chart_of_accounts_id
59 from fii_slg_assignments sas,
60 fii_source_ledger_groups slg
61 where slg.usage_code = 'DBI'
62 and slg.source_ledger_group_id = sas.source_ledger_group_id
63 ) coa_list,
64 fii_dim_mapping_rules dmr,
65 fnd_flex_value_sets fvs,
66 fnd_id_flex_structures_v ifs
67 where coa_list.chart_of_accounts_id = dmr.chart_of_accounts_id
68 and dmr.dimension_short_name = p_dim_short_name
69 --
70 -- Column dmr.status_code is not used by FC and LOB
71 --
72 -- and dmr.status_code = 'C'
73 --
74 and not exists (
75 select 1
76 from fii_dim_norm_hierarchy dnh
77 where dnh.parent_flex_value_set_id = l_master_vs_id
78 and dnh.child_flex_value_set_id = dmr.flex_value_set_id1
79 and rownum = 1
80 )
81 and dmr.flex_value_set_id1 = fvs.flex_value_set_id
82 and dmr.chart_of_accounts_id = ifs.id_flex_num
83 and ifs.application_id = 101
84 and ifs.id_flex_code = 'GL#'
85 and ifs.enabled_flag = 'Y';
86
87 l_missing_cnt NUMBER := 0;
88
89 BEGIN
90
91 if g_debug_flag = 'Y' then
92 fii_message.func_ent(
93 'FII_EXCEPTION_CHECK_PKG.detect_unmapped_local_vs');
94 end if;
95
96 begin
97 select master_value_set_id
98 into l_master_vs_id
99 from fii_financial_dimensions
100 where dimension_short_name = p_dim_short_name;
101 exception
102 when no_data_found then
103 fii_util.write_log(
104 'No master_value_set_id found for ' || p_dim_short_name );
105 raise;
106 when others then
107 raise;
108 end;
109
110 for missing_csr_rec in missing_csr loop
111
112 l_missing_cnt := l_missing_cnt + 1;
113
114 if l_missing_cnt = 1 then
115 fii_message.write_log( msg_name => 'FII_UNMAPPED_LOCAL_VS',
116 token_num => 0 );
117 fii_message.write_log( msg_name => 'FII_REFER_TO_OUTPUT',
118 token_num => 0 );
119 fii_message.write_output( msg_name => 'FII_UNMAPPED_LOCAL_VS',
120 token_num => 0 );
121 fii_message.write_output( msg_name => 'FII_UNMAPPED_LVS_LIST',
122 token_num => 0 );
123 end if;
124
125 fii_util.write_output( missing_csr_rec.vs_name || ' ' ||
126 missing_csr_rec.coa_name );
127 end loop;
128
129 if g_debug_flag = 'Y' then
130 fii_message.func_succ(
131 'FII_EXCEPTION_CHECK_PKG.detect_unmapped_local_vs');
132 end if;
133
134 return l_missing_cnt;
135
136 EXCEPTION
137
138 when others then
139 fii_util.write_log(
140 'Exception in detect_unmapped_local_vs: ' || sqlerrm );
141 fii_message.func_fail(
142 'FII_EXCEPTION_CHECK_PKG.detect_unmapped_local_vs');
143 return -1;
144
145 END detect_unmapped_local_vs;
146
147 END FII_EXCEPTION_CHECK_PKG;