[Home] [Help]
PACKAGE BODY: APPS.XLA_CONTROL_ACCOUNTS_PKG
Source
1 PACKAGE BODY xla_control_accounts_pkg AS
2 /* $Header: xlabacta.pkb 120.13 2006/07/17 16:41:13 weshen ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_control_accounts_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Control Accounts Package |
13 | |
14 | HISTORY |
15 | 27-AUG-02 A. Quaglia Created |
16 | 15-NOV-02 A. Quaglia 'N' not allowed for balance_flags |
17 | 03-DEC-02 A. Quaglia update_balance_flag: decoupled update |
18 | statements. Locking removed. |
19 | 10-DEC-02 A. Quaglia Overloaded update_balance_flag with |
20 | p_event_id,p_entity_id,p_application_id |
21 | 11-DEC-02 A. Quaglia update_balance_flag added where condition |
22 | on accounting_entry_status_code and |
23 | balance_type_code. |
24 | 12-DEC-02 A. Quaglia update_balance_flag: added parameter |
25 | p_application_id where missing, added |
26 | NOT NULL check. |
27 | 27-MAY-03 A. Quaglia replaced XLA_95100_COMMON_ERROR with |
28 | XLA_COMMON_ERROR. |
29 | 05-MAR-04 A.Quaglia Changed trace handling as per Sandeep's |
30 | code. |
31 | 25-MAR-04 A.Quaglia Fixed debug changes issues: |
32 | -Replaced global variable for trace |
33 | with local one |
34 | -Fixed issue with SQL%ROWCOUNT which is |
35 | modified after calling debug proc |
36 +======================================================================*/
37
38 --Generic Procedure/Function template
39 /*======================================================================+
40 | |
41 | Private Function |
42 | |
43 | Description |
44 | ----------- |
45 | |
46 | |
47 | Pseudo-code |
48 | ----------- |
49 | |
50 | |
51 | Open issues |
52 | ----------- |
53 | |
54 | |
55 +======================================================================*/
56
57 --
58 -- Private exceptions
59 --
60 le_resource_busy EXCEPTION;
61 PRAGMA exception_init(le_resource_busy, -00054);
62 --
63 -- Private constants
64 --
65
66 --
67 -- Private variables
68 --
69
70 --
71 -- Cursor declarations
72 --
73
74 --=============================================================================
75 -- *********** Local Trace Routine **********
76 --=============================================================================
77 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
78 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
79 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
80 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
81 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
82 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
83
84 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
85 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_control_accounts_pkg';
86
87 g_log_level NUMBER;
88 g_log_enabled BOOLEAN;
89
90 --1-STATEMENT, 2-PROCEDURE, 3-EVENT, 4-EXCEPTION, 5-ERROR, 6-UNEXPECTED
91
92 PROCEDURE trace
93 ( p_module IN VARCHAR2
94 ,p_msg IN VARCHAR2
95 ,p_level IN NUMBER
96 ) IS
97 BEGIN
98 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
99 fnd_log.message(p_level, p_module);
100 ELSIF p_level >= g_log_level THEN
101 fnd_log.string(p_level, p_module, p_msg);
102 END IF;
103
104 EXCEPTION
105 WHEN xla_exceptions_pkg.application_exception THEN
106 RAISE;
107 WHEN OTHERS THEN
108 xla_exceptions_pkg.raise_message
109 (p_location => 'xla_control_accounts_pkg.trace');
110 END trace;
111
112
113
114 FUNCTION is_control_account
115 ( p_code_combination_id IN INTEGER
116 ,p_natural_account IN VARCHAR2
117 ,p_ledger_id IN INTEGER
118 ,p_application_id IN INTEGER
119 ) RETURN INTEGER
120 IS
121 l_qualifier_value VARCHAR2(25);
122 l_je_source_name VARCHAR2(30);
123 l_chart_of_accounts_id INTEGER;
124
125 l_log_module VARCHAR2 (2000);
126
127 BEGIN
128 IF g_log_enabled THEN
129 l_log_module := C_DEFAULT_MODULE||'.is_control_account';
130 END IF;
131
132 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
133 trace
134 (p_module => l_log_module
135 ,p_msg => 'BEGIN ' || l_log_module
136 ,p_level => C_LEVEL_PROCEDURE);
137 END IF;
138
139 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
140 trace
141 ( p_module => l_log_module
142 ,p_msg => 'p_code_combination_id :' || p_code_combination_id
143 ,p_level => C_LEVEL_STATEMENT
144 );
145 trace
146 ( p_module => l_log_module
147 ,p_msg => 'p_natural_account :' || p_natural_account
148 ,p_level => C_LEVEL_STATEMENT
149 );
150 trace
151 ( p_module => l_log_module
152 ,p_msg => 'p_ledger_id :' || p_ledger_id
153 ,p_level => C_LEVEL_STATEMENT
154 );
155 trace
156 ( p_module => l_log_module
157 ,p_msg => 'p_application_id :' || p_application_id
158 ,p_level => C_LEVEL_STATEMENT
159 );
160 END IF;
161
162 IF p_code_combination_id IS NOT NULL
163 AND p_natural_account IS NULL
164 -- AND p_ledger_id IS NULL
165 THEN
166 BEGIN
167 SELECT gcc.reference3
168 INTO l_qualifier_value
169 FROM gl_code_combinations gcc
170 WHERE gcc.code_combination_id = p_code_combination_id;
171 EXCEPTION
172 WHEN NO_DATA_FOUND THEN
173 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
174 trace
175 ( p_module => l_log_module
176 ,p_msg => 'EXCEPTION: ' ||
177 'Code combination id '||p_code_combination_id ||
178 ' not found. in the table gl_code_combinations'
179 ,p_level => C_LEVEL_EXCEPTION
180 );
181 END IF;
182 xla_exceptions_pkg.raise_message
183 ('XLA'
184 ,'XLA_COMMON_ERROR'
185 ,'ERROR'
186 ,'Code combination id '||p_code_combination_id || ' not found.'
187 || ' in the table gl_code_combinations'
188 ,'LOCATION'
189 ,'xla_control_accounts_pkg.is_control_account');
190 WHEN OTHERS THEN
191 xla_exceptions_pkg.raise_message
192 (p_location => 'xla_control_accounts_pkg.is_control_account');
193 END;
194
195 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
196 trace
197 ( p_module => l_log_module
198 ,p_msg => 'Qualifier value: ' || l_qualifier_value
199 ,p_level => C_LEVEL_STATEMENT
200 );
201 END IF;
202
203 IF NVL(l_qualifier_value, 'N') = 'N'
204 THEN
205 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
206 trace
207 ( p_module => l_log_module
208 ,p_msg => 'END ' || l_log_module
209 ,p_level => C_LEVEL_PROCEDURE);
210 END IF;
211 RETURN C_NOT_CONTROL_ACCOUNT;
212 END IF;
213 IF p_application_id IS NOT NULL
214 THEN
215 BEGIN
216 SELECT xsl.control_account_type_code
217 INTO l_je_source_name
218 FROM xla_subledgers xsl
219 WHERE xsl.application_id = p_application_id;
220 EXCEPTION
221 WHEN NO_DATA_FOUND THEN
222 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
223 trace
224 ( p_module => l_log_module
225 ,p_msg => 'EXCEPTION: ' ||
226 'Application id '|| p_application_id || ' not found.' ||
227 ' in the table xla_subledgers'
228 ,p_level => C_LEVEL_EXCEPTION
229 );
230 END IF;
231 xla_exceptions_pkg.raise_message
232 ('XLA'
233 ,'XLA_COMMON_ERROR'
234 ,'ERROR'
235 ,'Application id '||p_application_id || ' not found.'
236 || ' in the table xla_subledgers'
237 ,'LOCATION'
238 ,'xla_control_accounts_pkg.is_control_account');
239 WHEN OTHERS THEN
240 xla_exceptions_pkg.raise_message
241 (p_location => 'xla_control_accounts_pkg.is_control_account');
242 END;
243
244 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
245 trace
246 ( p_module => l_log_module
247 ,p_msg => 'Source name: ' || l_je_source_name
248 ,p_level => C_LEVEL_STATEMENT
249 );
250 END IF;
251
252 IF (l_qualifier_value = l_je_source_name
253 OR (l_qualifier_value = 'Y' and nvl(l_je_source_name, 'N') <> 'N')
254 or l_je_source_name = 'Y' )
255 THEN
256 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
257 trace
258 ( p_module => l_log_module
259 ,p_msg => 'END ' || l_log_module
260 ,p_level => C_LEVEL_PROCEDURE);
261 END IF;
262 RETURN C_IS_CONTROL_ACCOUNT;
263 ELSE
264 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
265 trace
266 ( p_module => l_log_module
267 ,p_msg => 'END ' || l_log_module
268 ,p_level => C_LEVEL_PROCEDURE);
269 END IF;
270 RETURN C_IS_CONTROL_ACCOUNT_OTHER_APP;
271 END IF;
272 ELSE --p_application_id IS NULL
273 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
274 trace
275 ( p_module => l_log_module
276 ,p_msg => 'END ' || l_log_module
277 ,p_level => C_LEVEL_PROCEDURE);
278 END IF;
279 RETURN C_IS_CONTROL_ACCOUNT;
280 END IF;
281
282 ELSIF p_natural_account IS NOT NULL
283 AND p_code_combination_id IS NULL
284 THEN
285 xla_exceptions_pkg.raise_message
286 ('XLA'
287 ,'XLA_COMMON_ERROR'
288 ,'ERROR'
289 ,'p_natural_account NOT NULL: functionality not implemented'
290 ,'LOCATION'
291 ,'xla_control_accounts_pkg.is_control_account');
292 END IF;
293
294 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
295 trace
296 ( p_module => l_log_module
297 ,p_msg => 'END ' || l_log_module
298 ,p_level => C_LEVEL_PROCEDURE);
299 END IF;
300
301 EXCEPTION
302 WHEN xla_exceptions_pkg.application_exception THEN
303 RAISE;
304 WHEN OTHERS THEN
305 xla_exceptions_pkg.raise_message
306 (p_location => 'xla_control_accounts_pkg.is_control_account');
307 END is_control_account;
308
309
310 FUNCTION update_balance_flag ( p_application_id IN INTEGER
311 ,p_ae_header_id IN INTEGER
312 ,p_ae_line_num IN INTEGER
313 )
314 RETURN BOOLEAN
315 IS
316 l_log_module VARCHAR2 (2000);
317
318 BEGIN
319 IF g_log_enabled THEN
320 l_log_module := C_DEFAULT_MODULE||'.update_balance_flag';
321 END IF;
322
323 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
324 trace
325 ( p_module => l_log_module
326 ,p_msg => 'BEGIN ' || l_log_module
327 ,p_level => C_LEVEL_PROCEDURE);
328 END IF;
329
330 IF p_application_id IS NULL
331 OR p_ae_header_id IS NULL
332 THEN
333 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
334 trace
335 ( p_module => l_log_module
336 ,p_msg => 'EXCEPTION' ||
337 'The foll. params cannot be NULL:'
338 ,p_level => C_LEVEL_EXCEPTION
339 );
340 trace
341 ( p_module => l_log_module
342 ,p_msg => 'EXCEPTION' ||
343 'p_application_id: ' || p_application_id
344 ,p_level => C_LEVEL_EXCEPTION
345 );
346 trace
347 ( p_module => l_log_module
348 ,p_msg => 'EXCEPTION' ||
349 'p_ae_header_id : ' || p_ae_header_id
350 ,p_level => C_LEVEL_EXCEPTION
351 );
352 END IF;
353 xla_exceptions_pkg.raise_message
354 (p_location => 'xla_control_accounts_pkg.update_balance_flag');
355 END IF;
356
357 IF p_ae_line_num IS NULL
358 THEN
359 --WARNING: This is 1 of 3 similar update statements
360 -- Ensure changes are propagated
361 UPDATE xla_ae_lines xal
362 SET xal.control_balance_flag = C_CONTROL_BALANCE_FLAG_PENDING
363 WHERE xal.ROWID IN
364 ( SELECT ael.ROWID
365 FROM xla_ae_headers aeh
366 ,gl_ledgers xgl
367 ,xla_subledgers xsb
368 ,xla_ae_lines ael
369 ,gl_code_combinations gcc
370 WHERE aeh.ae_header_id = p_ae_header_id
371 AND aeh.application_id = p_application_id
372 AND aeh.balance_type_code = 'A'
373 AND aeh.accounting_entry_status_code IN ('D', 'F')
374 AND ael.ae_header_id = aeh.ae_header_id
375 AND ael.application_id = aeh.application_id
376 AND ael.party_type_code IS NOT NULL
377 AND ael.party_id IS NOT NULL
378 AND ael.control_balance_flag IS NULL
379 AND xgl.ledger_id = aeh.ledger_id
380 AND xsb.application_id = aeh.application_id
381 AND nvl(xsb.control_account_type_code, 'N') <> 'N'
382 AND gcc.chart_of_accounts_id = xgl.chart_of_accounts_id
383 AND gcc.code_combination_id = ael.code_combination_id
384 AND gcc.reference3 = xsb.control_account_type_code
385 );
386 ELSE
387 --WARNING: This is 2 of 3 similar update statements
388 -- Ensure changes are propagated
389 UPDATE xla_ae_lines xal
393 FROM xla_ae_headers aeh
390 SET xal.control_balance_flag = C_CONTROL_BALANCE_FLAG_PENDING
391 WHERE xal.ROWID IN
392 ( SELECT ael.ROWID
394 ,gl_ledgers xgl
395 ,xla_subledgers xsb
396 ,xla_ae_lines ael
397 ,gl_code_combinations gcc
398 WHERE aeh.ae_header_id = p_ae_header_id
399 AND aeh.application_id = p_application_id
400 AND aeh.balance_type_code = 'A'
401 AND aeh.accounting_entry_status_code IN ('D', 'F')
402 AND ael.ae_header_id = aeh.ae_header_id
403 AND ael.application_id = aeh.application_id
404 AND ael.ae_line_num = p_ae_line_num
405 AND ael.party_type_code IS NOT NULL
406 AND ael.party_id IS NOT NULL
407 AND ael.control_balance_flag IS NULL
408 AND xgl.ledger_id = aeh.ledger_id
409 AND xsb.application_id = aeh.application_id
410 AND nvl(xsb.control_account_type_code, 'N') <> 'N'
411 AND gcc.chart_of_accounts_id = xgl.chart_of_accounts_id
412 AND gcc.code_combination_id = ael.code_combination_id
413 AND gcc.reference3 = xsb.control_account_type_code
414 );
415 END IF;
416
417 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
418 trace
419 ( p_module => l_log_module
420 ,p_msg => 'END ' || l_log_module
421 ,p_level => C_LEVEL_PROCEDURE);
422 END IF;
423
424 RETURN TRUE;
425
426 EXCEPTION
427
428 WHEN xla_exceptions_pkg.application_exception THEN
429 RAISE;
430 WHEN OTHERS THEN
431 xla_exceptions_pkg.raise_message
432 (p_location => 'xla_control_accounts_pkg.update_balance_flag');
433 END update_balance_flag;
434
435
436 FUNCTION update_balance_flag ( p_event_id IN INTEGER
437 ,p_entity_id IN INTEGER
438 ,p_application_id IN INTEGER
439 )
440 RETURN BOOLEAN
441 IS
442 l_log_module VARCHAR2 (2000);
443
444 BEGIN
445 IF g_log_enabled THEN
446 l_log_module := C_DEFAULT_MODULE||'.update_balance_flag';
447 END IF;
448
449 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
450 trace
451 ( p_module => l_log_module
452 ,p_msg => 'BEGIN ' || l_log_module
453 ,p_level => C_LEVEL_PROCEDURE);
454 END IF;
455
456 IF p_event_id IS NULL
457 OR p_entity_id IS NULL
458 OR p_application_id IS NULL
459 THEN
460 IF (C_LEVEL_EXCEPTION >= g_log_level) THEN
461 trace
462 ( p_module => l_log_module
463 ,p_msg => 'EXCEPTION' ||
464 'The foll. params cannot be NULL:'
465 ,p_level => C_LEVEL_EXCEPTION
466 );
467 trace
468 ( p_module => l_log_module
469 ,p_msg => 'EXCEPTION' ||
470 'p_event_id : ' || p_event_id
471 ,p_level => C_LEVEL_EXCEPTION
472 );
473 trace
474 ( p_module => l_log_module
475 ,p_msg => 'EXCEPTION' ||
476 'p_entity_id : ' || p_entity_id
477 ,p_level => C_LEVEL_EXCEPTION
478 );
479 trace
480 ( p_module => l_log_module
481 ,p_msg => 'EXCEPTION' ||
482 'p_application_id: ' || p_application_id
483 ,p_level => C_LEVEL_EXCEPTION
484 );
485 END IF;
486 xla_exceptions_pkg.raise_message
487 (p_location => 'xla_control_accounts_pkg.update_balance_flag');
488 END IF;
489
490 --WARNING: This is 3 of 3 similar update statements
491 -- Ensure changes are propagated
492 UPDATE xla_ae_lines xal
493 SET xal.control_balance_flag = C_CONTROL_BALANCE_FLAG_PENDING
494 WHERE xal.ROWID IN
495 ( SELECT ael.ROWID
496 FROM xla_ae_headers aeh
497 ,gl_ledgers xgl
498 ,xla_subledgers xsb
499 ,xla_ae_lines ael
500 ,gl_code_combinations gcc
501 WHERE aeh.event_id = p_event_id
502 AND aeh.entity_id = p_entity_id
503 AND aeh.application_id = p_application_id
504 AND aeh.balance_type_code = 'A'
505 AND aeh.accounting_entry_status_code IN ('D', 'F')
506 AND ael.ae_header_id = aeh.ae_header_id
507 AND ael.application_id = aeh.application_id
508 AND ael.party_type_code IS NOT NULL
509 AND ael.party_id IS NOT NULL
510 AND ael.control_balance_flag IS NULL
511 AND xgl.ledger_id = aeh.ledger_id
512 AND xsb.application_id = aeh.application_id
513 AND nvl(xsb.control_account_type_code, 'N') <> 'N'
514 AND gcc.code_combination_id = ael.code_combination_id
515 AND gcc.reference3 = xsb.control_account_type_code
516 );
517
518 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
519 trace
520 ( p_module => l_log_module
521 ,p_msg => 'END ' || l_log_module
522 ,p_level => C_LEVEL_PROCEDURE);
523 END IF;
524
525 RETURN TRUE;
526
527 EXCEPTION
528
529 WHEN xla_exceptions_pkg.application_exception THEN
530 RAISE;
531 WHEN OTHERS THEN
532 xla_exceptions_pkg.raise_message
533 (p_location => 'xla_control_accounts_pkg.update_balance_flag');
534 END update_balance_flag;
535
536 BEGIN
537 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
538 g_log_enabled := fnd_log.test
539 (log_level => g_log_level
540 ,module => C_DEFAULT_MODULE);
541
542 IF NOT g_log_enabled THEN
543 g_log_level := C_LEVEL_LOG_DISABLED;
544 END IF;
545
546 END xla_control_accounts_pkg;