[Home] [Help]
PACKAGE BODY: APPS.FII_GL_EXTRACTION_UTIL
Source
1 PACKAGE BODY FII_GL_EXTRACTION_UTIL AS
2 /* $Header: FIIGLXUB.pls 120.3 2005/08/16 14:19:19 arcdixit noship $ */
3
4 g_debug_flag varchar2(1) :=
5 nvl( fnd_profile.value( 'FII_DEBUG_MODE' ), 'N' );
6 g_state varchar2(200) := null;
7 g_errbuf varchar2(2000) := null;
8 g_retcode varchar2(200) := null;
9 g_exception_msg varchar2(4000) := null;
10
11 ----------------------------------------
12 -- procedure load_ccc_mgr
13 ----------------------------------------
14 PROCEDURE LOAD_CCC_MGR(
15 p_retcode out nocopy varchar2
16 ) IS
17
18 l_count number;
19
20 BEGIN
21
22 if g_debug_flag = 'Y' then
23 FII_MESSAGE.Func_Ent('FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR');
24 end if;
25
26 -- set output parameters to no error by default
27
28 p_retcode := 0;
29
30 -- initialize global variables
31
32 g_state := 'Loading fii_ccc_mgr_gt';
33
34 -- real job start here
35
36 select count(*) into l_count from fii_ccc_mgr_gt;
37
38 if l_count > 0 then
39 if g_debug_flag = 'Y' then
40 fii_util.put_line( 'Detected ' || l_count || ' rows' );
41 FII_MESSAGE.Func_Succ('FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR');
42 end if;
43
44 return;
45 end if;
46
47 if g_debug_flag = 'Y' then
48 fii_util.put_line( 'Populating fii_ccc_mgr_gt' );
49 end if;
50
51 /* Table fii_ccc_mgr_gt has a row for each ccc_org_id.
52 Column manager is the ID of the current manager of a ccc_org_id.
53 If a ccc_org_id doesn't have a current manager, its manager column
54 is set to null.
55 */
56
57 --bug 3560006: populate company_id, cost_center_id
58 insert /*+ append parallel(a) */ into fii_ccc_mgr_gt a (manager, ccc_org_id, company_id, cost_center_id)
59 select /*+ use_hash(ccc_tbl,mgr_tbl,org,fv1,fv2) parallel(ccc_tbl) parallel(org) parallel(fv1) parallel(fv2) pq_distribute(fv1 hash,hash) pq_distribute(fv2 hash,hash) */
60 to_number (mgr_tbl.org_information2) manager,
61 ccc_tbl.organization_id ccc_org_id,
62 fv1.flex_value_id com_id,
63 fv2.flex_value_id cc_id
64 from hr_organization_information ccc_tbl,
65 ( select /*+ parallel(b) */ organization_id, org_information2
66 from hr_organization_information b
67 where org_information_context = 'Organization Name Alias'
68 and nvl( fnd_date.canonical_to_date( org_information3 ),
69 sysdate + 1 ) <= sysdate
70 and nvl( fnd_date.canonical_to_date( org_information4 ),
71 sysdate + 1 ) >= sysdate
72 ) mgr_tbl,
73 hr_organization_information org,
74 fnd_flex_values fv1,
75 fnd_flex_values fv2
76 where ccc_tbl.org_information_context = 'CLASS'
77 and ccc_tbl.org_information1 = 'CC'
78 and ccc_tbl.org_information2 = 'Y'
79 and ccc_tbl.organization_id = mgr_tbl.organization_id (+)
80 and org.org_information_context = 'Company Cost Center'
81 and org.organization_id = ccc_tbl.organization_id
82 and fv1.flex_value_set_id = org.org_information2
83 and fv1.flex_value = org.org_information3
84 and fv2.flex_value_set_id = org.org_information4
85 and fv2.flex_value = org.org_information5;
86
87 l_count := sql%rowcount;
88
89 if g_debug_flag = 'Y' then
90 fii_util.put_line( 'Inserted ' || l_count || ' rows' );
91 FII_MESSAGE.Func_Succ('FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR');
92 end if;
93
94 commit;
95
96 EXCEPTION
97
98 when others then
99
100 -- set global variables
101
102 g_errbuf := sqlerrm;
103 g_retcode := -1;
104 g_exception_msg := g_retcode || ':' || g_errbuf;
105
106 fii_util.put_line( 'Error occured while ' || g_state );
107 fii_util.put_line( g_exception_msg );
108
109 -- set output parameters
110
111 p_retcode := g_retcode;
112
113 FII_MESSAGE.Func_Fail('FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR');
114
115 raise;
116
117 END LOAD_CCC_MGR;
118
119 ----------------------------------------
120 -- function check_missing_ccc_mgr
121 ----------------------------------------
122 FUNCTION CHECK_MISSING_CCC_MGR RETURN NUMBER IS
123
124 l_retcode varchar2(128);
125
126 -- Bug 3916910. Added distinct and two and conitions for date_to column.
127 -- date_to null is also considered because the organizations having null
128 -- to date will not be considered as inactive.
129 cursor missing_csr is
130 select distinct ou.name
131 from fii_ccc_mgr_gt gt, hr_all_organization_units ou
132 where gt.manager is null
133 and gt.ccc_org_id = ou.organization_id
134 and (to_date(to_char(date_to,'mm/dd/yyyy'),'mm/dd/yyyy') >= to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'mm/dd/yyyy')
135 or date_to is null);
136
137 l_count number :=0;
138
139 BEGIN
140
141 if g_debug_flag = 'Y' then
142 FII_MESSAGE.Func_Ent('FII_GL_EXTRACTION_UTIL.CHECK_MISSING_CCC_MGR');
143 end if;
144
145 load_ccc_mgr ( l_retcode ); -- in case fii_ccc_mgr_gt has not been loaded
146
147 for missing_csr_rec in missing_csr loop
148 l_count := l_count +1;
149
150 IF l_count = 1 THEN
151 -- We have found some ccc_org_id with missing current manager.
152 fii_message.write_log( msg_name => 'FII_MISSING_CCC_MGR',
153 token_num => 0 );
154
155 fii_message.write_log( msg_name => 'FII_REFER_TO_OUTPUT',
156 token_num => 0 );
157
158 fii_message.write_output( msg_name => 'FII_MISSING_CCC_MGR',
159 token_num => 0 );
160
161 fii_message.write_output( msg_name => 'FII_CCC_ORG_LIST',
162 token_num => 0 );
163 END IF;
164
165 fii_util.write_output( missing_csr_rec.name );
166 end loop;
167
168 if g_debug_flag = 'Y' then
169 FII_MESSAGE.Func_Fail('FII_GL_EXTRACTION_UTIL.CHECK_MISSING_CCC_MGR');
170 end if;
171
172 return l_count;
173
174 END CHECK_MISSING_CCC_MGR;
175
176 ----------------------------------------
177 -- PROCEDURE Get_UNASSIGNED_ID
178 ----------------------------------------
179 PROCEDURE GET_UNASSIGNED_ID(p_UNASSIGNED_ID out nocopy number, p_UNASSIGNED_VSET_ID out nocopy number,
180 p_retcode out nocopy varchar2
181 ) IS
182
183 BEGIN
184
185 if g_debug_flag = 'Y' then
186 FII_MESSAGE.Func_Ent('FII_GL_EXTRACTION_UTIL.GET_UNASSIGNED_ID');
187 end if;
188
189 g_state := 'Getting the unassigned id and value set id';
190
191 p_retcode := 0;
192
193 select f1.FLEX_VALUE_SET_ID ,flex_value_id
194 INTO p_UNASSIGNED_VSET_ID, p_UNASSIGNED_ID
195 from fnd_flex_value_sets f1 ,fnd_flex_values f2
196 where flex_value_set_name = 'Financials Intelligence Internal Value Set'
197 and f1.flex_value_set_id = f2.flex_value_set_id
198 and flex_value = 'UNASSIGNED';
199
200 g_state := 'Retreived the unassigned id and value set id ';
201
202 if g_debug_flag = 'Y' then
203 FII_MESSAGE.Func_Succ('FII_GL_EXTRACTION_UTIL.GET_UNASSIGNED_ID');
204 end if;
205
206
207 EXCEPTION
208
209 when no_data_found then
210 -- set global variables
211
212 g_errbuf := sqlerrm;
213 g_retcode := -1;
214 g_exception_msg := g_retcode || ':' || g_errbuf;
215
216 fii_util.put_line( 'Error occured while ' || g_state );
217 fii_util.put_line( g_exception_msg );
218
219 -- set output parameters
220 p_retcode := g_retcode;
221 FII_MESSAGE.Func_Fail('FII_GL_EXTRACTION_UTIL.Get_UNASSIGNED_ID');
222
223 when others then
224 -- set global variables
225
226 g_errbuf := sqlerrm;
227 g_retcode := -1;
228 g_exception_msg := g_retcode || ':' || g_errbuf;
229
230 fii_util.put_line( 'Error occured while ' || g_state );
231 fii_util.put_line( g_exception_msg );
232
233 -- set output parameters
234 p_retcode := g_retcode;
235 FII_MESSAGE.Func_Fail('FII_GL_EXTRACTION_UTIL.Get_UNASSIGNED_ID');
236
237 END GET_UNASSIGNED_ID;
238
239 END FII_GL_EXTRACTION_UTIL;