[Home] [Help]
PACKAGE BODY: APPS.FII_FINANCIAL_DIMENSION_PKG
Source
1 package body FII_FINANCIAL_DIMENSION_PKG as
2 /*$Header: FIIFDIMB.pls 120.2 2006/03/27 19:07:52 juding ship $*/
3
4 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
5
6 /*
7 function range_or_single(p_coa_id in number) return varchar2 as
8 cursor numSeg is
9 select count(*)
10 from fnd_id_flex_segments
11 where application_id = 101
12 and id_flex_code = 'GL#'
13 and id_flex_num = p_coa_id
14 and flex_value_set_id = ENI_VALUESET_CATEGORY.Get_Flex_Value_Set_Id('401', 'MCAT', ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID);
15 l_num number;
16 l_r varchar2(1);
17 begin
18 open numSeg;
19 fetch numSeg into l_num;
20 close numSeg;
21 if l_num > 0 then
22 l_r := 'Y';
23 else
24 l_r := 'N';
25 end if;
26 return l_r;
27 end;
28 */
29 function range_or_single(p_coa_id in number) return varchar2 as
30 l_num number;
31 l_r varchar2(1);
32 begin
33
34 begin
35 select 1 into l_num
36 from fnd_id_flex_segments
37 where application_id = 101
38 and id_flex_code = 'GL#'
39 and id_flex_num = p_coa_id
40 and flex_value_set_id = ENI_VALUESET_CATEGORY.Get_Flex_Value_Set_Id('401', 'MCAT', ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID)
41 and ROWNUM = 1;
42 exception
43 when NO_DATA_FOUND then
44 l_num := 0;
45 end;
46
47 if l_num > 0 then
48 l_r := 'Y';
49 else
50 l_r := 'N';
51 end if;
52 return l_r;
53 end;
54
55 /*
56 Upon completion, check the value of x_status
57 FND_API.G_RET_STS_SUCCESS: OK
58 FND_API.G_RET_STS_ERROR : NOT OK
59 */
60
61 procedure update_dimension( p_short_name in varchar2,
62 p_name in varchar2,
63 p_description in varchar2,
64 p_system_enabled_flag in varchar2,
65 p_dbi_enabled_flag in varchar2,
66 p_master_value_set_id in number,
67 p_dbi_hier_top_node in varchar2,
68 p_dbi_hier_top_node_id in number,
69 x_status out nocopy varchar2,
70 x_message_count out nocopy number,
71 x_error_message out nocopy varchar2) as
72 begin
73 if g_debug_flag = 'Y' then
74 fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.update_demension(+)');
75 end if;
76
77 update FII_FINANCIAL_DIMENSIONS
78 set system_enabled_flag = p_system_enabled_flag,
79 dbi_enabled_flag = p_dbi_enabled_flag,
80 master_value_set_id = p_master_value_set_id,
81 dbi_hier_top_node = p_dbi_hier_top_node,
82 dbi_hier_top_node_id = p_dbi_hier_top_node_id
83 where dimension_short_name = p_short_name;
84
85 -- delete cross-value set ranges if the parent_value_set_id is not
86 -- one of the dimension master value sets
87 /*
88 delete from fii_dim_norm_hierarchy
89 where child_flex_value_set_id <> parent_flex_value_set_id
90 and parent_flex_value_set_id not in
91 ( select master_value_set_id
92 from fii_financial_dimensions_v );
93 */
94 DELETE /*+ index_ffs(fii_dim_norm_hierarchy) */
95 FROM fii_dim_norm_hierarchy
96 WHERE child_flex_value_set_id <> parent_flex_value_set_id
97 AND NOT EXISTS
98 (
99 SELECT
100 MASTER_VALUE_SET_ID
101 FROM
102 (
103 SELECT /*+ NO_MERGE */
104 DECODE(frd.dimension_short_name, 'ENI_ITEM_VBH_CAT',
105 ENI_VALUESET_CATEGORY.GET_FLEX_VALUE_SET_ID(401, 'MCAT',
106 ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID),
107 frd.master_value_set_id) MASTER_VALUE_SET_ID
108 FROM fii_financial_dimensions frd
109 WHERE dimension_short_name is not null
110 )
111 WHERE MASTER_VALUE_SET_ID = parent_flex_value_set_id
112 AND MASTER_VALUE_SET_ID is not null
113 );
114
115 fii_change_log_pkg.set_recollection_for_fii(x_status,
116 x_message_count,
117 x_error_message);
118 x_status := FND_API.G_RET_STS_SUCCESS;
119
120 if g_debug_flag = 'Y' then
121 fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.update_demension(-)');
122 end if;
123
124 exception
125 when others then
126 x_status := FND_API.G_RET_STS_ERROR;
127 x_message_count := 1;
128 FND_MESSAGE.SET_NAME ('FII','FII_ERROR');
129 FND_MESSAGE.SET_TOKEN('FUNCTION', 'FII_FINANCIAL_DIMENSION_PKG.update_dimension');
130 FND_MESSAGE.SET_TOKEN('SQLERRMC', sqlerrm);
131 FND_MSG_PUB.ADD;
132 FND_MSG_PUB.Count_And_Get(p_count => x_message_count, p_data => x_error_message);
133 if g_debug_flag = 'Y' then
134 fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.update_demension(EXCEPTION)');
135 fii_util.debug_line(sqlerrm);
136 end if;
137 end;
138
139 procedure resetProdCateg( x_status OUT nocopy VARCHAR2,
140 x_message_count OUT nocopy NUMBER,
141 x_error_message OUT nocopy VARCHAR2) as
142
143 cursor dim is
144 select chart_of_accounts_id from fii_dim_mapping_rules
145 where dimension_short_name = 'ENI_ITEM_VBH_CAT';
146 n number;
147 vsid number;
148 col_name varchar2(30);
149 begin
150 if g_debug_flag = 'Y' then
151 fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.manage_dimension_map_rules(+)');
152 end if;
153
154 for r in dim loop
155 begin
156 select application_column_name,
157 flex_value_set_id
158 into col_name,
159 vsid
160 from fnd_id_flex_segments
161 where application_id = 101
162 and id_flex_code = 'GL#'
163 and id_flex_num = r.chart_of_accounts_id
164 and flex_value_set_id = ENI_VALUESET_CATEGORY.Get_Flex_Value_Set_Id('401', 'MCAT', ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID);
165
166 update fii_dim_mapping_rules
167 set MAPPING_TYPE_CODE = 'S',
168 application_column_name1 = col_name,
169 flex_value_set_id1 = vsid
170 where DIMENSION_SHORT_NAME = 'ENI_ITEM_VBH_CAT'
171 and CHART_OF_ACCOUNTS_ID = r.chart_of_accounts_id;
172
173 exception
174 when TOO_MANY_ROWS then
175 update fii_dim_mapping_rules
176 set MAPPING_TYPE_CODE = 'R',
177 application_column_name1 = null,
178 flex_value_set_id1 = null
179 where DIMENSION_SHORT_NAME = 'ENI_ITEM_VBH_CAT'
180 and CHART_OF_ACCOUNTS_ID = r.chart_of_accounts_id;
181
182 when NO_DATA_FOUND then
183 update fii_dim_mapping_rules
184 set MAPPING_TYPE_CODE = 'R',
185 application_column_name1 = null,
186 flex_value_set_id1 = null
187 where DIMENSION_SHORT_NAME = 'ENI_ITEM_VBH_CAT'
188 and CHART_OF_ACCOUNTS_ID = r.chart_of_accounts_id;
189
190 when others then
191 raise;
192
193 end;
194 end loop;
195
196 x_status := FND_API.G_RET_STS_SUCCESS;
197 if g_debug_flag = 'Y' then
198 fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.manage_dimension_map_rules(-)');
199 end if;
200
201 exception
202 when others then
203 x_status := FND_API.G_RET_STS_ERROR;
204 x_message_count := 1;
205 FND_MESSAGE.SET_NAME ('FII','FII_ERROR');
206 FND_MESSAGE.SET_TOKEN('FUNCTION', 'FII_FINANCIAL_DIMENSION_PKG.resetProdCateg');
207 FND_MESSAGE.SET_TOKEN('SQLERRMC', sqlerrm);
208 FND_MSG_PUB.ADD;
209 FND_MSG_PUB.Count_And_Get(p_count => x_message_count, p_data => x_error_message);
210 if g_debug_flag = 'Y' then
211 fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.resetProdCateg(EXCEPTION)');
212 fii_util.debug_line(sqlerrm);
213 end if;
214 end;
215
216
217 procedure manage_dimension_map_rules(p_chart_of_accounts_id in number,
218 p_event in varchar2,
219 x_status out nocopy varchar2,
220 x_message_count out nocopy number,
221 x_error_message out nocopy varchar2) as
222
223 cursor rules is
224 select ffd.dimension_short_name dimension_short_name,
225 fdmr.chart_of_accounts_id chart_of_accounts_id,
226 'O' status_code,
227 sysdate creation_date,
228 fnd_global.user_id created_by,
229 sysdate last_update_date,
230 fnd_global.user_id last_updated_by,
231 fnd_global.user_id last_update_login,
232 'S' mapping_type_code,
233 null application_column_name1,
234 null flex_value_set_id1,
235 null application_column_name2,
236 null flex_value_set_id2,
237 null application_column_name3,
238 null flex_value_set_id3
239 from fii_financial_dimensions_v ffd,
240 fii_dim_mapping_rules fdmr
241 where ffd.dimension_short_name = fdmr.dimension_short_name(+)
242 and fdmr.chart_of_accounts_id(+) = p_chart_of_accounts_id;
243
244 rule_rec fii_dim_mapping_rules%rowtype;
245 l_segment_attribute_type varchar2(30);
246
247 cursor segment(p_chart_of_accounts_id number,
248 p_segment_attribute_type varchar2) is
249 select fsav.application_column_name,
250 fifs.flex_value_set_id
251 from fnd_id_flex_segments fifs,
252 fnd_segment_attribute_values fsav
253 where fifs.application_id = 101
254 and fifs.id_flex_code = 'GL#'
255 and fifs.application_column_name = fsav.application_column_name
256 and fifs.id_flex_code = fsav.id_flex_code
257 and fifs.id_flex_num = fsav.id_flex_num
258 and fsav.attribute_value = 'Y'
259 and fifs.id_flex_num = p_chart_of_accounts_id
260 and fsav.segment_attribute_type = p_segment_attribute_type;
261
262 /*
263 cursor coa is
264 select 'X'
265 from fnd_id_flex_structures
266 where application_id = 101
267 and id_flex_code = 'GL#'
268 and id_flex_num = p_chart_of_accounts_id;
269 */
270
271 cursor prod_val is
272 select application_column_name,
273 flex_value_set_id
274 from fnd_id_flex_segments
275 where application_id = 101
276 and id_flex_code = 'GL#'
277 and id_flex_num = p_chart_of_accounts_id
278 and flex_value_set_id = ENI_VALUESET_CATEGORY.Get_Flex_Value_Set_Id('401', 'MCAT', ENI_DENORM_HRCHY.GET_CATEGORY_SET_ID);
279
280 l_x varchar2(1);
281 l_p varchar2(1);
282 l_col_name varchar2(30);
283 l_val_set_id number;
284 l_i number := 0;
285
286 cursor forDBI is
287 select 'x' from dual
288 where exists (select 'x' from fii_source_ledger_groups x, fii_slg_assignments y
289 where x.source_ledger_group_id = y.source_ledger_group_id and
290 y.chart_of_accounts_id = p_chart_of_accounts_id and
291 x.usage_code='DBI');
292
293 begin
294 if g_debug_flag = 'Y' then
295 fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.manage_dimension_map_rules(+)');
296 end if;
297
298 if p_event = 'D' then
299 delete from fii_dim_mapping_rules
300 where chart_of_accounts_id = p_chart_of_accounts_id and
301 not exists(select 'x' from
302 fii_slg_assignments
303 where chart_of_accounts_id = p_chart_of_accounts_id);
304
305 fii_change_log_pkg.set_recollection_for_fii(x_status,
306 x_message_count,
307 x_error_message);
308 elsif p_event = 'I' then
309 /*
310 open coa;
311 fetch coa into l_x;
312 close coa;
313 */
314 begin
315 select 'X' into l_x
316 from fnd_id_flex_structures
317 where application_id = 101
318 and id_flex_code = 'GL#'
319 and id_flex_num = p_chart_of_accounts_id
320 and ROWNUM = 1;
321 exception
322 when NO_DATA_FOUND then
323 l_x := null;
324 end;
325
326 for rule_rec in rules loop
327 if rule_rec.chart_of_accounts_id is null and l_x is not null then
328
329 if rule_rec.dimension_short_name = 'FII_CO' then
330 l_segment_attribute_type := 'GL_BALANCING';
331 elsif rule_rec.dimension_short_name = 'FII_CC' then
332 l_segment_attribute_type := 'FA_COST_CTR';
333 elsif rule_rec.dimension_short_name = 'GL_FII_FIN_ITEM' then
334 l_segment_attribute_type := 'GL_ACCOUNT';
335 else
336 l_segment_attribute_type := null;
337 end if;
338
339 if l_segment_attribute_type is not null then
340 open segment(p_chart_of_accounts_id, l_segment_attribute_type);
341 fetch segment into rule_rec.application_column_name1,
342 rule_rec.flex_value_set_id1;
343 close segment;
344 end if;
345
346
347 open prod_val;
348 loop
349 fetch prod_val into l_col_name, l_val_set_id;
350 exit when prod_val%notfound;
351 l_i := l_i + 1;
352 end loop;
353 close prod_val;
354
355 if rule_rec.dimension_short_name = 'ENI_ITEM_VBH_CAT' then
356 if l_i = 0 then
357 rule_rec.mapping_type_code := 'R';
358 else
359 rule_rec.mapping_type_code := 'S';
360 if l_i = 1 then
361 rule_rec.application_column_name1 := l_col_name;
362 rule_rec.flex_value_set_id1 := l_val_set_id;
363 end if;
364 end if;
365 end if;
366
367 insert into fii_dim_mapping_rules(
368 DIMENSION_SHORT_NAME,
369 CHART_OF_ACCOUNTS_ID,
370 STATUS_CODE,
371 CREATION_DATE,
372 CREATED_BY,
373 LAST_UPDATE_DATE,
374 LAST_UPDATED_BY,
375 LAST_UPDATE_LOGIN,
376 MAPPING_TYPE_CODE,
377 APPLICATION_COLUMN_NAME1,
378 FLEX_VALUE_SET_ID1,
379 APPLICATION_COLUMN_NAME2,
380 FLEX_VALUE_SET_ID2,
381 APPLICATION_COLUMN_NAME3,
382 FLEX_VALUE_SET_ID3
383 )
384 values(
385 rule_rec.DIMENSION_SHORT_NAME,
386 p_chart_of_accounts_id,
387 rule_rec.STATUS_CODE,
388 rule_rec.CREATION_DATE,
389 rule_rec.CREATED_BY,
390 rule_rec.LAST_UPDATE_DATE,
391 rule_rec.LAST_UPDATED_BY,
392 rule_rec.LAST_UPDATE_LOGIN,
393 rule_rec.MAPPING_TYPE_CODE,
394 rule_rec.APPLICATION_COLUMN_NAME1,
395 rule_rec.FLEX_VALUE_SET_ID1,
396 rule_rec.APPLICATION_COLUMN_NAME2,
397 rule_rec.FLEX_VALUE_SET_ID2,
398 rule_rec.APPLICATION_COLUMN_NAME3,
399 rule_rec.FLEX_VALUE_SET_ID3);
400 end if;
401 end loop;
402
403 x_status := FND_API.G_RET_STS_SUCCESS;
404
405 l_x := null;
406 open forDBI;
407 fetch forDBI into l_x;
408 close forDBI;
409
410 if l_x is not null then
411
412 fii_change_log_pkg.set_recollection_for_fii(x_status,
413 x_message_count,
414 x_error_message);
415 end if;
416 end if;
417
418 if g_debug_flag = 'Y' then
419 fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.manage_dimension_map_rules(-)');
420 end if;
421
422 exception
423 when others then
424 x_status := FND_API.G_RET_STS_ERROR;
425 x_message_count := 1;
426 FND_MESSAGE.SET_NAME ('FII','FII_ERROR');
427 FND_MESSAGE.SET_TOKEN('FUNCTION', 'FII_FINANCIAL_DIMENSION_PKG.manage_dimension_map_rules');
428 FND_MESSAGE.SET_TOKEN('SQLERRMC', sqlerrm);
429 FND_MSG_PUB.ADD;
430 FND_MSG_PUB.Count_And_Get(p_count => x_message_count, p_data => x_error_message);
431 if g_debug_flag = 'Y' then
432 fii_util.debug_line('FII_FINANCIAL_DIMENSION_PKG.manage_dimension_map_rules(EXCEPTION)');
433 fii_util.debug_line(sqlerrm);
434 end if;
435 end;
436
437
438 /*****************************************************************************
439 | DESCRIPTION |
440 | Plsql api to delete je inclusion rules associated with a particular |
441 | je rule set id. (When slg assignment is deleted, associated |
442 | je inclusion rules need to be deleted). |
443 | HISTORY |
444 | 21-JUL-03 H.Chung Created |
445 | 05-APR-05 MManasse Bug 4277376: Added update of je_rule_set_id to null in|
446 | fii_slg_assignments. |
447 | |
448 *****************************************************************************/
449 PROCEDURE DeleteJeInclusionRules(p_je_rule_set_id IN NUMBER,
450 x_status OUT nocopy VARCHAR2,
451 x_message_count OUT nocopy NUMBER,
452 x_error_message OUT nocopy VARCHAR2)
453 AS
454 l_msg_count number;
455 l_msg_data varchar2(2000);
456 BEGIN
457 IF g_debug_flag = 'Y' THEN
458 FII_UTIL.debug_line('FII_FINANCIAL_DIMENSION_PKG.DeleteJeInclusionRules(+)');
459 END IF;
460
461 DELETE FROM GL_JE_INCLUSION_RULES
462 WHERE je_rule_set_id = p_je_rule_set_id;
463
464 UPDATE FII_SLG_ASSIGNMENTS
465 SET JE_RULE_SET_ID = NULL WHERE JE_RULE_SET_ID = p_je_rule_set_id;
466
467 x_status := FND_API.G_RET_STS_SUCCESS;
468
469 IF g_debug_flag = 'Y' THEN
470 FII_UTIL.debug_line('FII_FINANCIAL_DIMENSION_PKG.update_demension(-)');
471 END IF;
472
473 EXCEPTION
474 WHEN OTHERS THEN
475 x_status := FND_API.G_RET_STS_ERROR;
476 x_message_count := 1;
477 FND_MESSAGE.SET_NAME ('FII','FII_ERROR');
478 FND_MESSAGE.SET_TOKEN('FUNCTION', 'FII_FINANCIAL_DIMENSION_PKG.DeleteJeInclusionRules');
479 FND_MESSAGE.SET_TOKEN('SQLERRMC', sqlerrm);
480 FND_MSG_PUB.ADD;
481 FND_MSG_PUB.Count_And_Get(p_count => x_message_count, p_data => x_error_message);
482 IF g_debug_flag = 'Y' THEN
483 FII_UTIL.debug_line('FII_FINANCIAL_DIMENSION_PKG.DeleteJeInclusionRules(EXCEPTION)');
484 FII_UTIL.debug_line(sqlerrm);
485 END IF;
486 END;
487
488 end FII_FINANCIAL_DIMENSION_PKG;