[Home] [Help]
PACKAGE BODY: APPS.XLA_TAB_ACCT_DEFS_PKG
Source
1 PACKAGE BODY xla_tab_acct_defs_pkg AS
2 /* $Header: xlatabtad.pkb 120.1 2005/04/18 22:12:06 dcshah ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_tab_acct_defs_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Tab Acct Defs Package |
13 | |
14 | HISTORY |
15 | 01-May-01 Dimple Shah Created |
16 | |
17 +======================================================================*/
18
19 /*======================================================================+
20 | |
21 | Private Function |
22 | |
23 | invalid_seg_rule |
24 | |
25 | Returns true if sources for the seg rule are invalid |
26 | |
27 +======================================================================*/
28
29 FUNCTION invalid_seg_rule
30 (p_application_id IN NUMBER
31 ,p_amb_context_code IN VARCHAR2
32 ,p_account_type_code IN VARCHAR2
33 ,p_segment_rule_type_code IN VARCHAR2
34 ,p_segment_rule_code IN VARCHAR2
35 ,p_message_name IN OUT NOCOPY VARCHAR2
36 ,p_source_name IN OUT NOCOPY VARCHAR2
37 ,p_source_type IN OUT NOCOPY VARCHAR2)
38 RETURN BOOLEAN
39 IS
40
41 l_return BOOLEAN;
42 l_exist VARCHAR2(1);
43 l_application_id NUMBER(38) := p_application_id;
44 l_account_type_code VARCHAR2(30) := p_account_type_code;
45 l_source_name varchar2(80) := null;
46 l_source_type varchar2(80) := null;
47
48 CURSOR c_seg_details
49 IS
50 SELECT 'x'
51 FROM xla_seg_rule_details d
52 WHERE application_id = p_application_id
53 AND amb_context_code = p_amb_context_code
54 AND segment_rule_type_code = p_segment_rule_type_code
55 AND segment_rule_code = p_segment_rule_code;
56
57 CURSOR c_seg_value_sources
58 IS
59 SELECT value_source_type_code source_type_code, value_source_code source_code
60 FROM xla_seg_rule_details d
61 WHERE application_id = p_application_id
62 AND amb_context_code = p_amb_context_code
63 AND segment_rule_type_code = p_segment_rule_type_code
64 AND segment_rule_code = p_segment_rule_code
65 AND value_source_code is not null
66 AND value_source_type_code = 'S'
67 AND NOT EXISTS (SELECT 'y'
68 FROM xla_tab_acct_type_srcs s
69 WHERE s.source_application_id = d.value_source_application_id
70 AND s.source_type_code = d.value_source_type_code
71 AND s.source_code = d.value_source_code
72 AND s.application_id = p_application_id
73 AND s.account_type_code = p_account_type_code)
74 UNION
75 SELECT input_source_type_code source_type_code, input_source_code source_code
76 FROM xla_seg_rule_details d
77 WHERE application_id = p_application_id
78 AND amb_context_code = p_amb_context_code
79 AND segment_rule_type_code = p_segment_rule_type_code
80 AND segment_rule_code = p_segment_rule_code
81 AND input_source_code is not null
82 AND input_source_type_code = 'S'
83 AND NOT EXISTS (SELECT 'y'
84 FROM xla_tab_acct_type_srcs s
85 WHERE s.source_application_id = d.input_source_application_id
86 AND s.source_type_code = d.input_source_type_code
87 AND s.source_code = d.input_source_code
88 AND s.application_id = p_application_id
89 AND s.account_type_code = p_account_type_code);
90
91 l_seg_value_sources c_seg_value_sources%rowtype;
92
93 CURSOR c_seg_condition_sources
94 IS
95 SELECT c.source_type_code, c.source_code
96 FROM xla_conditions c, xla_seg_rule_details d
97 WHERE c.segment_rule_detail_id = d.segment_rule_detail_id
98 AND d.application_id = p_application_id
99 AND d.amb_context_code = p_amb_context_code
100 AND d.segment_rule_type_code = p_segment_rule_type_code
101 AND d.segment_rule_code = p_segment_rule_code
102 AND c.source_code is not null
103 AND c.source_type_code = 'S'
104 AND NOT EXISTS (SELECT 'y'
105 FROM xla_tab_acct_type_srcs s
106 WHERE s.source_application_id = c.source_application_id
107 AND s.source_type_code = c.source_type_code
108 AND s.source_code = c.source_code
109 AND s.application_id = p_application_id
110 AND s.account_type_code = p_account_type_code)
111 UNION
112 SELECT c.value_source_type_code source_type_code, c.value_source_code source_code
113 FROM xla_conditions c, xla_seg_rule_details d
114 WHERE c.segment_rule_detail_id = d.segment_rule_detail_id
115 AND d.application_id = p_application_id
116 AND d.amb_context_code = p_amb_context_code
117 AND d.segment_rule_type_code = p_segment_rule_type_code
118 AND d.segment_rule_code = p_segment_rule_code
119 AND c.value_source_code is not null
120 AND c.value_source_type_code = 'S'
121 AND NOT EXISTS (SELECT 'y'
122 FROM xla_tab_acct_type_srcs s
123 WHERE s.source_application_id = c.value_source_application_id
124 AND s.source_type_code = c.value_source_type_code
125 AND s.source_code = c.value_source_code
126 AND s.application_id = p_application_id
127 AND s.account_type_code = p_account_type_code);
128
129 l_seg_condition_sources c_seg_condition_sources%rowtype;
130
131 CURSOR c_seg_value_der_sources
132 IS
133 SELECT value_source_type_code source_type_code, value_source_code source_code
134 FROM xla_seg_rule_details d
135 WHERE application_id = p_application_id
136 AND amb_context_code = p_amb_context_code
137 AND segment_rule_type_code = p_segment_rule_type_code
138 AND segment_rule_code = p_segment_rule_code
139 AND value_source_code is not null
140 AND value_source_type_code = 'D'
141 UNION
142 SELECT input_source_type_code source_type_code, input_source_code source_code
143 FROM xla_seg_rule_details d
144 WHERE application_id = p_application_id
145 AND amb_context_code = p_amb_context_code
146 AND segment_rule_type_code = p_segment_rule_type_code
147 AND segment_rule_code = p_segment_rule_code
148 AND input_source_code is not null
149 AND input_source_type_code = 'D';
150
151 l_seg_value_der_sources c_seg_value_der_sources%rowtype;
152
153 CURSOR c_seg_condition_der_sources
154 IS
155 SELECT c.source_type_code source_type_code, c.source_code source_code
156 FROM xla_conditions c, xla_seg_rule_details d
157 WHERE c.segment_rule_detail_id = d.segment_rule_detail_id
158 AND d.application_id = p_application_id
159 AND d.amb_context_code = p_amb_context_code
160 AND d.segment_rule_type_code = p_segment_rule_type_code
161 AND d.segment_rule_code = p_segment_rule_code
162 AND c.source_code is not null
163 AND c.source_type_code = 'D'
164 UNION
165 SELECT c.value_source_type_code source_type_code, c.value_source_code source_code
166 FROM xla_conditions c, xla_seg_rule_details d
167 WHERE c.segment_rule_detail_id = d.segment_rule_detail_id
168 AND d.application_id = p_application_id
169 AND d.amb_context_code = p_amb_context_code
170 AND d.segment_rule_type_code = p_segment_rule_type_code
171 AND d.segment_rule_code = p_segment_rule_code
172 AND c.value_source_code is not null
173 AND c.value_source_type_code = 'D';
174
175 l_seg_condition_der_sources c_seg_condition_der_sources%rowtype;
176
177 BEGIN
178
179 xla_utility_pkg.trace('> xla_tab_acct_defs_pkg.invalid_seg_rule' , 10);
180
181 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
182 xla_utility_pkg.trace('segment_rule_type_code = '||p_segment_rule_type_code , 20);
183 xla_utility_pkg.trace('segment_rule_code = '||p_segment_rule_code , 20);
184
185 --
186 -- check if segment rules has details existing
187 --
188 OPEN c_seg_details;
189 FETCH c_seg_details
190 INTO l_exist;
191 IF c_seg_details%notfound then
192 p_message_name := 'XLA_AB_SR_NO_DETAIL';
193 l_return := TRUE;
194 ELSE
195 p_message_name := NULL;
196 l_return := FALSE;
197 END IF;
198 CLOSE c_seg_details;
199
200 IF l_return = FALSE THEN
201 --
202 -- check if segment rules has sources that do not belong to the event class
203 --
204
205 OPEN c_seg_value_sources;
206 FETCH c_seg_value_sources
207 INTO l_seg_value_sources;
208 IF c_seg_value_sources%found then
209
210 xla_validations_pkg.get_source_info
211 (p_application_id => l_application_id
212 ,p_source_type_code => l_seg_value_sources.source_type_code
213 ,p_source_code => l_seg_value_sources.source_code
214 ,p_source_name => l_source_name
215 ,p_source_type => l_source_type);
216
217 p_message_name := 'XLA_AB_SR_UNASSN_SOURCE';
218 p_source_name := l_source_name;
219 p_source_type := l_source_type;
220
221 l_return := TRUE;
222 ELSE
223 p_message_name := NULL;
224 l_return := FALSE;
225 END IF;
226 CLOSE c_seg_value_sources;
227 END IF;
228
229 IF l_return = FALSE THEN
230 OPEN c_seg_condition_sources;
231 FETCH c_seg_condition_sources
232 INTO l_seg_condition_sources;
233 IF c_seg_condition_sources%found then
234
235 xla_validations_pkg.get_source_info
236 (p_application_id => l_application_id
237 ,p_source_type_code => l_seg_condition_sources.source_type_code
238 ,p_source_code => l_seg_condition_sources.source_code
239 ,p_source_name => l_source_name
240 ,p_source_type => l_source_type);
241
242 p_message_name := 'XLA_AB_SR_CON_UNASN_SRCE';
243 p_source_name := l_source_name;
244 p_source_type := l_source_type;
245
246 l_return := TRUE;
247 ELSE
248 p_message_name := NULL;
249 l_return := FALSE;
250 END IF;
251 CLOSE c_seg_condition_sources;
252 END IF;
253
254 /* IF l_return = FALSE THEN
255 OPEN c_seg_value_der_sources;
256 LOOP
257 FETCH c_seg_value_der_sources
258 INTO l_seg_value_der_sources;
259 EXIT WHEN c_seg_value_der_sources%notfound or l_return = TRUE;
260
261 IF xla_sources_pkg.derived_source_is_invalid
262 (p_application_id => l_application_id
263 ,p_derived_source_code => l_seg_value_der_sources.source_code
264 ,p_derived_source_type_code => 'D'
265 ,p_account_type_code => l_account_type_code
266 ,p_level => 'L') = 'TRUE' THEN
267
268 xla_validations_pkg.get_source_info
269 (p_application_id => l_application_id
270 ,p_source_type_code => l_seg_value_der_sources.source_type_code
271 ,p_source_code => l_seg_value_der_sources.source_code
272 ,p_source_name => l_source_name
273 ,p_source_type => l_source_type);
274
275 p_message_name := 'XLA_AB_SR_UNASSN_SOURCE';
276 p_source_name := l_source_name;
277 p_source_type := l_source_type;
278
279 l_return := TRUE;
280 ELSE
281 p_message_name := NULL;
282 l_return := FALSE;
283 END IF;
284 END LOOP;
285 CLOSE c_seg_value_der_sources;
286 END IF;
287
288 IF l_return = FALSE THEN
289 OPEN c_seg_condition_der_sources;
290 LOOP
291 FETCH c_seg_condition_der_sources
292 INTO l_seg_condition_der_sources;
293 EXIT WHEN c_seg_condition_der_sources%notfound or l_return = TRUE;
294
295 IF xla_sources_pkg.derived_source_is_invalid
296 (p_application_id => l_application_id
297 ,p_derived_source_code => l_seg_condition_der_sources.source_code
298 ,p_derived_source_type_code => 'D'
299 ,p_account_type_code => l_account_type_code
300 ,p_level => 'L') = 'TRUE' THEN
301
302 xla_validations_pkg.get_source_info
303 (p_application_id => l_application_id
304 ,p_source_type_code => l_seg_condition_der_sources.source_type_code
305 ,p_source_code => l_seg_condition_der_sources.source_code
306 ,p_source_name => l_source_name
307 ,p_source_type => l_source_type);
308
309 p_message_name := 'XLA_AB_SR_CON_UNASN_SRCE';
310 p_source_name := l_source_name;
311 p_source_type := l_source_type;
312
313 l_return := TRUE;
314 ELSE
315 p_message_name := NULL;
316 l_return := FALSE;
317 END IF;
318 END LOOP;
319 CLOSE c_seg_condition_der_sources;
320 END IF;
321 */
322
323 xla_utility_pkg.trace('< xla_tab_acct_defs_pkg.invalid_seg_rule' , 10);
324
325 return l_return;
326
327 EXCEPTION
328 WHEN xla_exceptions_pkg.application_exception THEN
329 IF c_seg_condition_sources%ISOPEN THEN
330 CLOSE c_seg_condition_sources;
331 END IF;
332 IF c_seg_value_sources%ISOPEN THEN
333 CLOSE c_seg_value_sources;
334 END IF;
335 IF c_seg_condition_der_sources%ISOPEN THEN
336 CLOSE c_seg_condition_der_sources;
337 END IF;
338 IF c_seg_value_der_sources%ISOPEN THEN
339 CLOSE c_seg_value_der_sources;
340 END IF;
341 RAISE;
342
343 WHEN OTHERS THEN
344 IF c_seg_condition_sources%ISOPEN THEN
345 CLOSE c_seg_condition_sources;
346 END IF;
347 IF c_seg_value_sources%ISOPEN THEN
348 CLOSE c_seg_value_sources;
349 END IF;
350 IF c_seg_condition_der_sources%ISOPEN THEN
351 CLOSE c_seg_condition_der_sources;
352 END IF;
353 IF c_seg_value_der_sources%ISOPEN THEN
354 CLOSE c_seg_value_der_sources;
355 END IF;
356
357 xla_exceptions_pkg.raise_message
358 (p_location => 'xla_tab_acct_defs_pkg.invalid_seg_rule');
359
360 END invalid_seg_rule;
361
362 /*======================================================================+
363 | |
364 | Public Function |
365 | |
366 | invalid_segment_rule |
367 | |
368 | Returns true if sources for the seg rule are invalid |
369 | |
370 +======================================================================*/
371
372 FUNCTION invalid_segment_rule
373 (p_application_id IN NUMBER
374 ,p_amb_context_code IN VARCHAR2
375 ,p_account_type_code IN VARCHAR2
376 ,p_segment_rule_type_code IN VARCHAR2
377 ,p_segment_rule_code IN VARCHAR2)
378 RETURN VARCHAR2
379 IS
380 l_return VARCHAR2(30);
381 l_exist VARCHAR2(1);
382 l_application_id NUMBER(38) := p_application_id;
383 l_account_type_code VARCHAR2(30) := p_account_type_code;
384 l_amb_context_code VARCHAR2(30) := p_amb_context_code;
385 l_segment_rule_type_code VARCHAR2(1) := p_segment_rule_type_code;
386 l_segment_rule_code VARCHAR2(30) := p_segment_rule_code;
387 l_message_name VARCHAR2(30);
388
389 l_source_name varchar2(80) := null;
390 l_source_type varchar2(80) := null;
391
392 BEGIN
393
394 xla_utility_pkg.trace('> xla_tab_acct_defs_pkg.invalid_segment_rule' , 10);
395
396 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
397 xla_utility_pkg.trace('segment_rule_type_code = '||p_segment_rule_type_code , 20);
398 xla_utility_pkg.trace('segment_rule_code = '||p_segment_rule_code , 20);
399
400 --
401 -- call invalid_seg_rule to see if segment rule is invalid
402 --
403 IF xla_tab_acct_defs_pkg.invalid_seg_rule
404 (p_application_id => l_application_id
405 ,p_amb_context_code => l_amb_context_code
406 ,p_account_type_code => l_account_type_code
407 ,p_segment_rule_type_code => l_segment_rule_type_code
408 ,p_segment_rule_code => l_segment_rule_code
409 ,p_message_name => l_message_name
410 ,p_source_name => l_source_name
411 ,p_source_type => l_source_type) THEN
412
413 l_return := 'TRUE';
414 ELSE
415 l_return := 'FALSE';
416 END IF;
417
418 xla_utility_pkg.trace('< xla_tab_acct_defs_pkg.invalid_segment_rule' , 10);
419 return l_return;
420
421 EXCEPTION
422 WHEN xla_exceptions_pkg.application_exception THEN
423 RAISE;
424
425 WHEN OTHERS THEN
426 xla_exceptions_pkg.raise_message
427 (p_location => 'xla_tab_acct_defs_pkg.invalid_segment_rule');
428
429 END invalid_segment_rule;
430
431 /*======================================================================+
432 | |
433 | Public Function |
434 | |
435 | uncompile_tran_acct_def |
436 | |
437 | Returns true if the transaction account definition is uncompiled |
438 | |
439 +======================================================================*/
440
441 FUNCTION uncompile_tran_acct_def
442 (p_application_id IN NUMBER
443 ,p_amb_context_code IN VARCHAR2
444 ,p_account_definition_type_code IN VARCHAR2
445 ,p_account_definition_code IN VARCHAR2)
446 RETURN BOOLEAN
447 IS
448
449 l_return BOOLEAN;
450 l_exist VARCHAR2(1);
451
452 CURSOR c_prod_rules
453 IS
454 SELECT 'x'
455 FROM xla_tab_acct_defs_b
456 WHERE application_id = p_application_id
457 AND amb_context_code = p_amb_context_code
458 AND account_definition_type_code = p_account_definition_type_code
459 AND account_definition_code = p_account_definition_code
460 AND compile_status_code in ('E','N','Y')
461 AND locking_status_flag = 'N'
462 FOR UPDATE of compile_status_code NOWAIT;
463
464 BEGIN
465
466
467 xla_utility_pkg.trace('> xla_tab_acct_defs_pkg.uncompile_tran_acct_def' , 10);
468
469 xla_utility_pkg.trace('application_id = '||p_application_id , 20);
470
471 OPEN c_prod_rules;
472 FETCH c_prod_rules
473 INTO l_exist;
474 IF c_prod_rules%found then
475
476 UPDATE xla_tab_acct_defs_b
477 SET compile_status_code = 'N'
478 WHERE current of c_prod_rules;
479
480 l_return := TRUE;
481 ELSE
482 l_return := FALSE;
483 END IF;
484 CLOSE c_prod_rules;
485
486
487 xla_utility_pkg.trace('< xla_tab_acct_defs_pkg.uncompile_tran_acct_def' , 10);
488
489 return l_return;
490
491 EXCEPTION
492 WHEN xla_exceptions_pkg.application_exception THEN
493 RAISE;
494 WHEN OTHERS THEN
495 xla_exceptions_pkg.raise_message
496 (p_location => 'xla_tab_acct_defs_pkg.uncompile_tran_acct_def');
497
498 END uncompile_tran_acct_def;
499
500 END xla_tab_acct_defs_pkg;