[Home] [Help]
PACKAGE BODY: APPS.XLA_AC_BALANCES_PKG
Source
1 PACKAGE BODY xla_ac_balances_pkg AS
2 /* $Header: xlaacbal.pkb 120.4 2011/03/04 13:20:15 karamakr ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_ac_balances_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Account Balances Package |
13 | |
14 | HISTORY |
15 +======================================================================*/
16
17 --Generic Procedure/Function template
18 /*======================================================================+
19 | |
20 | Private Function |
21 | |
22 | Description |
23 | ----------- |
24 | |
25 | |
26 | Pseudo-code |
27 | ----------- |
28 | |
29 | |
30 | Open issues |
31 | ----------- |
32 | |
33 | MUST SOLVE |
34 | |
35 | |
36 | NICE TO SOLVE |
37 | |
38 | |
39 +======================================================================*/
40
41 --
42 -- Global variables
43 --
44 g_user_id INTEGER;
45 g_login_id INTEGER;
46 g_date DATE;
47 g_prog_appl_id INTEGER;
48 g_prog_id INTEGER;
49 g_req_id INTEGER;
50
51
52 --
53
54 -- Cursor declarations
55 --
56
57
58
59 --=============================================================================
60 -- *********** Local Trace Routine **********
61 --=============================================================================
62 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
63 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
64 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
65 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
66 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
67 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
68
69 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
70 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_ac_balances_pkg';
71
72 g_log_level NUMBER;
73 g_log_enabled BOOLEAN;
74
75 --1-STATEMENT, 2-PROCEDURE, 3-EVENT, 4-EXCEPTION, 5-ERROR, 6-UNEXPECTED
76 CANT_DELETE_BALANCES EXCEPTION;
77
78 PROCEDURE trace
79 ( p_module IN VARCHAR2
80 ,p_msg IN VARCHAR2
81 ,p_level IN NUMBER
82 ) IS
83 BEGIN
84 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
85 fnd_log.message(p_level, p_module);
86 ELSIF p_level >= g_log_level THEN
87 fnd_log.string(p_level, p_module, p_msg);
88 END IF;
89
90 EXCEPTION
91 WHEN xla_exceptions_pkg.application_exception THEN
92 RAISE;
93 WHEN OTHERS THEN
94 xla_exceptions_pkg.raise_message
95 (p_location => 'xla_ac_balances_pkg.trace');
96 END trace;
97
98 FUNCTION call_update_balances RETURN BOOLEAN IS
99
100 l_batch_code VARCHAR2(100) := p_batch_code;
101 l_purge_mode VARCHAR2(1) := p_purge_mode;
102 BEGIN
103 update_balances(l_batch_code,l_purge_mode);
104 RETURN TRUE;
105 END call_update_balances;
106
107 FUNCTION call_purge_interface_recs RETURN BOOLEAN IS
108
109 l_batch_code VARCHAR2(100) := p_batch_code;
110 l_purge_mode VARCHAR2(1) := p_purge_mode;
111 BEGIN
112 purge_interface_recs(l_batch_code,l_purge_mode);
113 RETURN TRUE;
114 END call_purge_interface_recs;
115
116 FUNCTION get_period_year(
117 p_period_name gl_period_statuses.period_name%TYPE
118 )
119 RETURN VARCHAR2 AS
120 ------------------------------------------------------------------
121 --Created by : veramach, Oracle India
122 --Date created: 29-Nov-2007
123 --
124 --Purpose:
125 --
126 --
127 --Known limitations/enhancements and/or remarks:
128 --
129 --Change History:
130 --Who When What
131 -------------------------------------------------------------------
132 CURSOR c_get_period(
133 cp_period_name gl_period_statuses.period_name%TYPE
134 ) IS
135 SELECT gps.period_year
136 FROM gl_period_statuses gps,
137 xla_ac_balances_int bal
138 WHERE gps.ledger_id = bal.ledger_id
139 -- AND gps.application_id = bal.application_id
140 AND gps.application_id = 101 --bug 11811413
141 AND gps.adjustment_period_flag = 'N'
142 AND gps.period_name = cp_period_name;
143
144 l_period_year gl_period_statuses.period_year%TYPE;
145 BEGIN
146 OPEN c_get_period(p_period_name);
147 FETCH c_get_period INTO l_period_year;
148 CLOSE c_get_period;
149
150 RETURN l_period_year;
151 END get_period_year;
152
153 PROCEDURE insert_balances_rec(
154 p_ac_balance_int_rec xla_ac_balances%ROWTYPE
155 ) IS
156 l_log_module VARCHAR2(240);
157 l_period_year gl_period_statuses.period_year%TYPE;
158 l_row_count NUMBER;
159 BEGIN
160 IF g_log_enabled THEN
161 l_log_module := c_default_module || '.insert';
162 END IF;
163
164 IF (c_level_procedure >= g_log_level) THEN
165 TRACE(p_msg => 'BEGIN of function insert',
166 p_module => l_log_module,
167 p_level => c_level_procedure
168 );
169 END IF;
170
171 l_period_year := get_period_year(p_ac_balance_int_rec.period_name);
172
173 INSERT INTO xla_ac_balances
174 (application_id,
175 ledger_id,
176 code_combination_id,
177 analytical_criterion_code,
178 analytical_criterion_type_code,
179 amb_context_code,
180 ac1,
181 ac2,
182 ac3,
183 ac4,
184 ac5,
185 period_name,
186 beginning_balance_dr,
187 beginning_balance_cr,
188 period_balance_dr,
189 period_balance_cr,
190 initial_balance_flag,
191 first_period_flag,
192 period_year,
193 creation_date,
194 created_by,
195 last_update_date,
196 last_updated_by,
197 last_update_login,
198 program_update_date,
199 program_application_id,
200 program_id,
201 request_id
202 )
203 VALUES (p_ac_balance_int_rec.application_id,
204 p_ac_balance_int_rec.ledger_id,
205 p_ac_balance_int_rec.code_combination_id,
206 p_ac_balance_int_rec.analytical_criterion_code,
207 p_ac_balance_int_rec.analytical_criterion_type_code,
208 p_ac_balance_int_rec.amb_context_code,
209 p_ac_balance_int_rec.ac1,
210 p_ac_balance_int_rec.ac2,
211 p_ac_balance_int_rec.ac3,
212 p_ac_balance_int_rec.ac4,
213 p_ac_balance_int_rec.ac5,
214 p_ac_balance_int_rec.period_name,
215 p_ac_balance_int_rec.beginning_balance_dr,
216 p_ac_balance_int_rec.beginning_balance_cr,
217 p_ac_balance_int_rec.period_balance_dr,
218 p_ac_balance_int_rec.period_balance_cr,
219 p_ac_balance_int_rec.initial_balance_flag,
220 p_ac_balance_int_rec.first_period_flag,
221 l_period_year,
222 g_date,
223 g_user_id,
224 g_date,
225 g_user_id,
226 g_login_id,
227 g_date,
228 g_prog_appl_id,
229 g_prog_id,
230 g_req_id
231 );
232 l_row_count := SQL%ROWCOUNT;
233 IF (c_level_statement >= g_log_level) THEN
234 TRACE(p_module => l_log_module,
235 p_msg => l_row_count || ' initial balances inserted',
236 p_level => c_level_statement
237 );
238 END IF;
239
240 EXCEPTION
241 WHEN xla_exceptions_pkg.application_exception THEN
242 RAISE;
243 WHEN OTHERS THEN
244 xla_exceptions_pkg.raise_message
245 (p_location => 'xla_ac_balances_pkg.insert_balances_rec');
246 END insert_balances_rec;
247
248 PROCEDURE update_balances_rec(
249 p_ac_balance_int_rec xla_ac_balances%ROWTYPE
250 ) IS
251 l_log_module VARCHAR2(240);
252 l_period_year gl_period_statuses.period_year%TYPE;
253 l_row_count NUMBER;
254 BEGIN
255 IF g_log_enabled THEN
256 l_log_module := c_default_module || '.update_balances_rec';
257 END IF;
258
259 IF (c_level_procedure >= g_log_level) THEN
260 TRACE(p_msg => 'BEGIN of function update_balances_rec',
261 p_module => l_log_module,
262 p_level => c_level_procedure
263 );
264 END IF;
265
266 UPDATE xla_ac_balances
267 SET period_name = p_ac_balance_int_rec.period_name,
268 beginning_balance_dr = p_ac_balance_int_rec.beginning_balance_dr,
269 beginning_balance_cr = p_ac_balance_int_rec.beginning_balance_cr,
270 initial_balance_flag = p_ac_balance_int_rec.initial_balance_flag,
271 first_period_flag = p_ac_balance_int_rec.first_period_flag,
272 period_year = p_ac_balance_int_rec.period_year,
273 last_update_date = g_date,
274 program_update_date = g_date,
275 last_updated_by = g_user_id,
276 last_update_login = g_login_id,
277 program_application_id = g_prog_appl_id,
278 program_id = g_prog_id,
279 request_id = g_req_id
280 WHERE application_id = p_ac_balance_int_rec.application_id
281 AND ledger_id = p_ac_balance_int_rec.ledger_id
282 AND code_combination_id = p_ac_balance_int_rec.code_combination_id
283 AND analytical_criterion_code = p_ac_balance_int_rec.analytical_criterion_code
284 AND analytical_criterion_type_code = p_ac_balance_int_rec.analytical_criterion_type_code
285 AND amb_context_code = p_ac_balance_int_rec.amb_context_code
286 AND period_name = p_ac_balance_int_rec.period_name
287 AND NVL(ac1,'*') = NVL(p_ac_balance_int_rec.ac1,'*')
288 AND NVL(ac2,'*') = NVL(p_ac_balance_int_rec.ac2,'*')
289 AND NVL(ac3,'*') = NVL(p_ac_balance_int_rec.ac3,'*')
290 AND NVL(ac4,'*') = NVL(p_ac_balance_int_rec.ac4,'*')
291 AND NVL(ac5,'*') = NVL(p_ac_balance_int_rec.ac5,'*');
292 l_row_count := SQL%ROWCOUNT;
293 IF (c_level_statement >= g_log_level) THEN
294 TRACE(p_module => l_log_module,
295 p_msg => l_row_count || ' initial balances updated',
296 p_level => c_level_statement
297 );
298 END IF;
299
300 EXCEPTION
301 WHEN xla_exceptions_pkg.application_exception THEN
302 RAISE;
303 WHEN OTHERS THEN
304 xla_exceptions_pkg.raise_message
305 (p_location => 'xla_ac_balances_pkg.update_balances_rec');
306 END update_balances_rec;
307
308 PROCEDURE delete_balances_rec(
309 p_ac_balance_int_rec xla_ac_balances%ROWTYPE
310 ) IS
311 l_log_module VARCHAR2(240);
312 l_period_year gl_period_statuses.period_year%TYPE;
313 l_row_count NUMBER;
314 BEGIN
315 IF g_log_enabled THEN
316 l_log_module := c_default_module || '.delete_balances_rec';
317 END IF;
318
319 IF (c_level_procedure >= g_log_level) THEN
320 TRACE(p_msg => 'BEGIN of function delete_balances_rec',
321 p_module => l_log_module,
322 p_level => c_level_procedure
323 );
324 END IF;
325
326 DELETE xla_ac_balances xab
327 WHERE xab.application_id = p_ac_balance_int_rec.application_id
328 AND xab.ledger_id = p_ac_balance_int_rec.ledger_id
329 AND xab.code_combination_id = p_ac_balance_int_rec.code_combination_id
330 AND xab.analytical_criterion_code = p_ac_balance_int_rec.analytical_criterion_code
331 AND xab.analytical_criterion_type_code = p_ac_balance_int_rec.analytical_criterion_type_code
332 AND xab.amb_context_code = p_ac_balance_int_rec.amb_context_code
333 AND NVL(xab.ac1,'*') = NVL(p_ac_balance_int_rec.ac1,'*')
334 AND NVL(xab.ac2,'*') = NVL(p_ac_balance_int_rec.ac2,'*')
335 AND NVL(xab.ac3,'*') = NVL(p_ac_balance_int_rec.ac3,'*')
336 AND NVL(xab.ac4,'*') = NVL(p_ac_balance_int_rec.ac4,'*')
337 AND NVL(xab.ac5,'*') = NVL(p_ac_balance_int_rec.ac5,'*')
338 AND xab.period_name = p_ac_balance_int_rec.period_name;
339 l_row_count := SQL%ROWCOUNT;
340
341
342 IF (c_level_statement >= g_log_level) THEN
343 TRACE(p_module => l_log_module,
344 p_msg => l_row_count || ' initial balances deleted',
345 p_level => c_level_statement
346 );
347 END IF;
348
349 EXCEPTION
350 WHEN xla_exceptions_pkg.application_exception THEN
351 RAISE;
352 WHEN OTHERS THEN
353 xla_exceptions_pkg.raise_message
354 (p_location => 'xla_ac_balances_pkg.delete_balances_rec');
355 END delete_balances_rec;
356
357 PROCEDURE merge_balances_rec
358 ( p_ac_balance_int_rec IN xla_ac_balances_int%ROWTYPE
359 )
360 IS
361
362 l_log_module VARCHAR2 (2000);
363
364 -- Get existing balance
365 CURSOR c_exist_balance(
366 cp_ledger_id xla_ac_balances.ledger_id%TYPE,
367 cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
368 cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
369 cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
370 cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
371 cp_ac1 xla_ac_balances.ac1%TYPE,
375 cp_ac5 xla_ac_balances.ac5%TYPE,
372 cp_ac2 xla_ac_balances.ac2%TYPE,
373 cp_ac3 xla_ac_balances.ac3%TYPE,
374 cp_ac4 xla_ac_balances.ac4%TYPE,
376 cp_period_name xla_ac_balances.period_name%TYPE
377 ) IS
378 SELECT xab.*
379 FROM xla_ac_balances xab
380 WHERE xab.ledger_id = cp_ledger_id
381 AND xab.code_combination_id = cp_code_combination_id
382 AND xab.analytical_criterion_code = cp_analytical_criterion_code
383 AND xab.analytical_criterion_type_code = cp_criterion_type_code
384 AND xab.amb_context_code = cp_amb_context_code
385 AND xab.period_name = cp_period_name
386 AND NVL(xab.ac1,'*') = NVL(cp_ac1,'*')
387 AND NVL(xab.ac2,'*') = NVL(cp_ac2,'*')
388 AND NVL(xab.ac3,'*') = NVL(cp_ac3,'*')
389 AND NVL(xab.ac4,'*') = NVL(cp_ac4,'*')
390 AND NVL(xab.ac5,'*') = NVL(cp_ac5,'*');
391 l_exist_balance xla_ac_balances%ROWTYPE;
392
393 l_balances_rec xla_ac_balances%ROWTYPE;
394
395 -- Get subsequent periods
396 CURSOR c_subsequent_periods(
397 cp_application_id xla_ac_balances.application_id%TYPE,
398 cp_ledger_id xla_ac_balances.ledger_id%TYPE,
399 cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
400 cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
401 cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
402 cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
403 cp_ac1 xla_ac_balances.ac1%TYPE,
404 cp_ac2 xla_ac_balances.ac2%TYPE,
405 cp_ac3 xla_ac_balances.ac3%TYPE,
406 cp_ac4 xla_ac_balances.ac4%TYPE,
407 cp_ac5 xla_ac_balances.ac5%TYPE,
408 cp_period_year xla_ac_balances.period_year%TYPE,
409 cp_period_name xla_ac_balances.period_name%TYPE
410 ) IS
411 SELECT xab.*
412 FROM xla_ac_balances xab,
413 gl_ledgers ledger,
414 gl_periods fut_periods,
415 gl_period_types period_types,
416 gl_period_statuses fut_period_statuses,
417 gl_period_sets period_sets
418 WHERE ledger.accounted_period_type = period_types.period_type
419 AND period_types.period_type = fut_periods.period_type
420 AND fut_period_statuses.ledger_id = ledger.ledger_id
421 AND fut_period_statuses.period_name = fut_periods.period_name
422 AND fut_period_statuses.period_type = period_types.period_type
423 AND fut_period_statuses.closing_status IN('O','C','F')
424 AND fut_period_statuses.adjustment_period_flag = 'N'
425 AND fut_period_statuses.period_type = period_types.period_type
426 AND fut_period_statuses.period_name = fut_periods.period_name
427 AND period_sets.period_set_name = fut_periods.period_set_name
428 AND ledger.period_set_name = period_sets.period_set_name
429 AND ledger.accounted_period_type = period_types.period_type
430 AND ledger.ledger_id = cp_ledger_id
431 --AND fut_period_statuses.application_id = cp_application_id
432 AND fut_period_statuses.application_id = 101 --bug 11811413
433 AND xab.ledger_id = ledger.ledger_id
434 AND fut_periods.period_name = xab.period_name
435 AND xab.ledger_id = cp_ledger_id
436 AND xab.code_combination_id = cp_code_combination_id
437 AND xab.analytical_criterion_code = cp_analytical_criterion_code
438 AND xab.analytical_criterion_type_code = cp_criterion_type_code
439 AND xab.amb_context_code = cp_amb_context_code
440 AND NVL(xab.ac1,'*') = NVL(cp_ac1,'*')
441 AND NVL(xab.ac2,'*') = NVL(cp_ac2,'*')
442 AND NVL(xab.ac3,'*') = NVL(cp_ac3,'*')
443 AND NVL(xab.ac4,'*') = NVL(cp_ac4,'*')
444 AND NVL(xab.ac5,'*') = NVL(cp_ac5,'*')
445 AND xab.period_year = NVL(cp_period_year,xab.period_year)
446 AND xab.period_name <> cp_period_name
447 ORDER BY fut_periods.start_date;
448 l_subsequent_periods xla_ac_balances%ROWTYPE;
449
450 l_delta_cr NUMBER := NULL;
451 l_delta_dr NUMBER := NULL;
452
453 -- Get supporting referehnce header
454 CURSOR c_sup_ref_hdr (
455 cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
456 cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
457 cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE
458 ) IS
459 SELECT NVL(xah.balancing_flag,'N') balancing_flag,
460 xah.year_end_carry_forward_code
461 FROM xla_analytical_hdrs_b xah
462 WHERE xah.amb_context_code = cp_amb_context_code
463 AND xah.analytical_criterion_code = cp_analytical_criterion_code
464 AND xah.analytical_criterion_type_code = cp_criterion_type_code;
465 l_sup_ref_hdr c_sup_ref_hdr%ROWTYPE;
466
467 l_period_year xla_ac_balances.period_year%TYPE := NULL;
468
469 -- Get current period end date
470 CURSOR c_current_period_end_date(
471 cp_ledger_id gl_ledgers.ledger_id%TYPE,
472 cp_application_id gl_period_statuses.application_id%TYPE,
473 cp_period_name gl_periods.period_name%TYPE,
474 cp_period_year gl_periods.period_year%TYPE
475 ) IS
476 SELECT periods.end_date
477 FROM gl_ledgers ledger,
478 gl_periods periods,
479 gl_period_types period_types,
480 gl_period_statuses period_statuses,
481 gl_period_sets period_sets
482 WHERE ledger.accounted_period_type = period_types.period_type
483 AND period_types.period_type = periods.period_type
487 --AND period_statuses.closing_status IN('O', 'C', 'P')
484 AND period_statuses.ledger_id = ledger.ledger_id
485 AND period_statuses.period_name = periods.period_name
486 AND period_statuses.period_type = period_types.period_type
488 AND period_statuses.adjustment_period_flag = 'N'
489 AND period_statuses.period_type = period_types.period_type
490 AND period_statuses.period_name = periods.period_name
491 AND period_sets.period_set_name = periods.period_set_name
492 AND ledger.period_set_name = period_sets.period_set_name
493 AND ledger.accounted_period_type = period_types.period_type
494 AND ledger.ledger_id = cp_ledger_id
495 --AND period_statuses.application_id = cp_application_id
496 AND period_statuses.application_id = 101
497 AND periods.period_year = NVL(cp_period_year, periods.period_year)
498 AND periods.period_name = cp_period_name;
499
500 -- Get next period's start date
501 CURSOR c_next_period_start_date(
502 cp_application_id xla_ac_balances.application_id%TYPE,
503 cp_ledger_id xla_ac_balances.ledger_id%TYPE,
504 cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
505 cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
506 cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
507 cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
508 cp_ac1 xla_ac_balances.ac1%TYPE,
509 cp_ac2 xla_ac_balances.ac2%TYPE,
510 cp_ac3 xla_ac_balances.ac3%TYPE,
511 cp_ac4 xla_ac_balances.ac4%TYPE,
512 cp_ac5 xla_ac_balances.ac5%TYPE,
513 cp_period_year xla_ac_balances.period_year%TYPE,
514 cp_period_name xla_ac_balances.period_name%TYPE
515 ) IS
516 SELECT periods.start_date
517 FROM xla_ac_balances xab,
518 gl_ledgers ledger,
519 gl_periods periods,
520 gl_period_types period_types,
521 gl_period_statuses period_statuses,
522 gl_period_sets period_sets
523 WHERE ledger.accounted_period_type = period_types.period_type
524 AND period_types.period_type = periods.period_type
525 AND period_statuses.ledger_id = ledger.ledger_id
526 AND period_statuses.period_name = periods.period_name
527 AND period_statuses.period_type = period_types.period_type
528 AND period_statuses.adjustment_period_flag = 'N'
529 AND period_statuses.period_type = period_types.period_type
530 AND period_statuses.period_name = periods.period_name
531 AND period_sets.period_set_name = periods.period_set_name
532 AND ledger.period_set_name = period_sets.period_set_name
533 AND ledger.accounted_period_type = period_types.period_type
534 AND ledger.ledger_id = cp_ledger_id
535 AND periods.period_year = NVL(cp_period_year, periods.period_year)
536 AND xab.ledger_id = ledger.ledger_id
537 AND periods.period_name = xab.period_name
538 AND xab.ledger_id = cp_ledger_id
539 AND xab.code_combination_id = cp_code_combination_id
540 AND xab.analytical_criterion_code = cp_analytical_criterion_code
541 AND xab.analytical_criterion_type_code = cp_criterion_type_code
542 AND xab.amb_context_code = cp_amb_context_code
543 --AND period_statuses.application_id = cp_application_id
544 AND period_statuses.application_id = 101 --bug 11811413
545 AND NVL(xab.ac1,'*') = NVL(cp_ac1,'*')
546 AND NVL(xab.ac2,'*') = NVL(cp_ac2,'*')
547 AND NVL(xab.ac3,'*') = NVL(cp_ac3,'*')
548 AND NVL(xab.ac4,'*') = NVL(cp_ac4,'*')
549 AND NVL(xab.ac5,'*') = NVL(cp_ac5,'*')
550 AND xab.period_name <> cp_period_name
551 ORDER BY periods.start_date;
552
553 l_current_period_end_date DATE;
554 l_next_period_start_date DATE;
555 l_synchronize_fut_periods BOOLEAN;
556
557 -- Get future periods
558 CURSOR c_future_periods(
559 cp_application_id xla_ac_balances.application_id%TYPE,
560 cp_ledger_id xla_ac_balances.ledger_id%TYPE,
561 cp_period_year xla_ac_balances.period_year%TYPE,
562 cp_earliest_start_date DATE,
563 cp_latest_end_date DATE
564 ) IS
565 SELECT fut_periods.period_name,
566 fut_periods.period_year,
567 fut_periods.period_num
568 FROM gl_ledgers ledger,
569 gl_periods fut_periods,
570 gl_period_types period_types,
571 gl_period_statuses fut_period_statuses,
572 gl_period_sets period_sets
573 WHERE ledger.accounted_period_type = period_types.period_type
574 AND period_types.period_type = fut_periods.period_type
575 AND fut_period_statuses.ledger_id = ledger.ledger_id
576 AND fut_period_statuses.period_name = fut_periods.period_name
577 AND fut_period_statuses.period_type = period_types.period_type
578 AND fut_period_statuses.adjustment_period_flag = 'N'
579 AND fut_period_statuses.period_type = period_types.period_type
580 AND fut_period_statuses.period_name = fut_periods.period_name
581 AND period_sets.period_set_name = fut_periods.period_set_name
582 AND ledger.period_set_name = period_sets.period_set_name
583 AND ledger.accounted_period_type = period_types.period_type
584 AND ledger.ledger_id = cp_ledger_id
585 --AND fut_period_statuses.application_id = cp_application_id
586 AND fut_period_statuses.application_id = 101 --bug 11811413
587 AND fut_periods.period_year = NVL(cp_period_year, fut_periods.period_year)
588 AND fut_periods.start_date > cp_earliest_start_date
589 AND fut_periods.end_date < cp_latest_end_date;
590
594 ) IS
591 -- Get account type for a ccid
592 CURSOR c_account_type(
593 cp_cc_id gl_code_combinations.code_combination_id%TYPE
595 SELECT account_type
596 FROM gl_code_combinations
597 WHERE code_combination_id = cp_cc_id;
598 l_account_type gl_code_combinations.account_type%TYPE;
599
600 -- Get period num
601 CURSOR c_period_num(
602 cp_application_id gl_ledgers.ledger_id%TYPE,
603 cp_period_name gl_periods.period_name%TYPE
604 ) IS
605 SELECT periods.period_num
606 FROM gl_periods periods,
607 gl_ledgers ledger
608 WHERE ledger.ledger_id = cp_application_id
609 AND ledger.period_set_name = periods.period_set_name
610 AND periods.period_name = cp_period_name;
611 l_period_num gl_periods.period_num%TYPE;
612
613
614 CURSOR c_future_open_periods(
615 cp_application_id xla_ac_balances.application_id%TYPE,
616 cp_ledger_id gl_ledgers.ledger_id%TYPE,
617 cp_period_name gl_periods.period_name%TYPE,
618 cp_period_year gl_periods.period_year%TYPE
619 ) IS
620 SELECT fut_periods.period_name,
621 fut_periods.period_num
622 FROM gl_ledgers ledger,
623 gl_periods fut_periods,
624 gl_period_types period_types,
625 gl_period_statuses fut_period_statuses,
626 gl_period_sets period_sets,
627 gl_periods ref_period
628 WHERE ledger.accounted_period_type = period_types.period_type
629 AND period_types.period_type = fut_periods.period_type
630 AND fut_period_statuses.ledger_id = ledger.ledger_id
631 AND fut_period_statuses.period_name = fut_periods.period_name
632 AND fut_period_statuses.period_type = period_types.period_type
633 AND fut_period_statuses.closing_status IN('O', 'F')
634 AND fut_period_statuses.adjustment_period_flag = 'N'
635 AND fut_period_statuses.period_type = period_types.period_type
636 AND fut_period_statuses.period_name = fut_periods.period_name
637 AND period_sets.period_set_name = fut_periods.period_set_name
638 AND ledger.period_set_name = period_sets.period_set_name
639 AND ledger.accounted_period_type = period_types.period_type
640 AND ledger.ledger_id = cp_ledger_id
641 --AND fut_period_statuses.application_id = cp_application_id
642 AND fut_period_statuses.application_id = 101 --bug 11811413
643 AND fut_periods.period_year = NVL(cp_period_year, fut_periods.period_year)
644 AND ref_period.period_name = cp_period_name
645 AND ref_period.period_type = period_types.period_type
646 AND period_sets.period_set_name = ref_period.period_set_name
647 AND ref_period.start_date < fut_periods.start_date;
648 l_future_open_periods c_future_open_periods%ROWTYPE;
649
650 CURSOR c_closed_periods(
651 cp_application_id xla_ac_balances.application_id%TYPE,
652 cp_ledger_id gl_ledgers.ledger_id%TYPE,
653 cp_period_name gl_periods.period_name%TYPE,
654 cp_period_year gl_periods.period_year%TYPE
655 ) IS
656 SELECT fut_periods.period_name,
657 fut_periods.period_num
658 FROM gl_ledgers ledger,
659 gl_periods fut_periods,
660 gl_period_types period_types,
661 gl_period_statuses fut_period_statuses,
662 gl_period_sets period_sets,
663 gl_periods ref_period
664 WHERE ledger.accounted_period_type = period_types.period_type
665 AND period_types.period_type = fut_periods.period_type
666 AND fut_period_statuses.ledger_id = ledger.ledger_id
667 AND fut_period_statuses.period_name = fut_periods.period_name
668 AND fut_period_statuses.period_type = period_types.period_type
669 AND fut_period_statuses.closing_status = 'C'
670 AND fut_period_statuses.adjustment_period_flag = 'N'
671 AND fut_period_statuses.period_type = period_types.period_type
672 AND fut_period_statuses.period_name = fut_periods.period_name
673 AND period_sets.period_set_name = fut_periods.period_set_name
674 AND ledger.period_set_name = period_sets.period_set_name
675 AND ledger.accounted_period_type = period_types.period_type
676 AND ledger.ledger_id = cp_ledger_id
677 --AND fut_period_statuses.application_id = cp_application_id
678 AND fut_period_statuses.application_id = 101 --bug 11811413
679 AND fut_periods.period_year = NVL(cp_period_year, fut_periods.period_year)
680 AND ref_period.period_name = cp_period_name
681 AND ref_period.period_type = period_types.period_type
682 AND period_sets.period_set_name = ref_period.period_set_name
683 AND ref_period.start_date < fut_periods.start_date;
684
685 l_dr NUMBER;
686 l_cr NUMBER;
687
688 l_delete_cr_delta NUMBER;
689 l_delete_dr_delta NUMBER;
690
691 l_prev_year xla_ac_balances.period_year%TYPE;
692
693 CURSOR c_delete_records(
694 cp_application_id xla_ac_balances.application_id%TYPE,
695 cp_ledger_id xla_ac_balances.ledger_id%TYPE,
696 cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
697 cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
698 cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
699 cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
700 cp_ac1 xla_ac_balances.ac1%TYPE,
701 cp_ac2 xla_ac_balances.ac2%TYPE,
702 cp_ac3 xla_ac_balances.ac3%TYPE,
703 cp_ac4 xla_ac_balances.ac4%TYPE,
707 SELECT xab.*
704 cp_ac5 xla_ac_balances.ac5%TYPE,
705 cp_period_year xla_ac_balances.period_year%TYPE
706 ) IS
708 FROM xla_ac_balances xab,
709 gl_ledgers ledger,
710 gl_periods fut_periods,
711 gl_period_types period_types,
712 gl_period_statuses fut_period_statuses,
713 gl_period_sets period_sets
714 WHERE ledger.accounted_period_type = period_types.period_type
715 AND period_types.period_type = fut_periods.period_type
716 AND fut_period_statuses.ledger_id = ledger.ledger_id
717 AND fut_period_statuses.period_name = fut_periods.period_name
718 AND fut_period_statuses.period_type = period_types.period_type
719 AND fut_period_statuses.adjustment_period_flag = 'N'
720 AND fut_period_statuses.period_type = period_types.period_type
721 AND fut_period_statuses.period_name = fut_periods.period_name
722 AND period_sets.period_set_name = fut_periods.period_set_name
723 AND ledger.period_set_name = period_sets.period_set_name
724 AND ledger.accounted_period_type = period_types.period_type
725 AND ledger.ledger_id = cp_ledger_id
726 --AND fut_period_statuses.application_id = cp_application_id
727 AND fut_period_statuses.application_id = 101 --bug 11811413
728 AND xab.ledger_id = ledger.ledger_id
729 AND fut_periods.period_name = xab.period_name
730 AND xab.ledger_id = cp_ledger_id
731 AND xab.code_combination_id = cp_code_combination_id
732 AND xab.analytical_criterion_code = cp_analytical_criterion_code
733 AND xab.analytical_criterion_type_code = cp_criterion_type_code
734 AND xab.amb_context_code = cp_amb_context_code
735 AND NVL(xab.ac1, '*') = NVL(cp_ac1, '*')
736 AND NVL(xab.ac2, '*') = NVL(cp_ac2, '*')
737 AND NVL(xab.ac3, '*') = NVL(cp_ac3, '*')
738 AND NVL(xab.ac4, '*') = NVL(cp_ac4, '*')
739 AND NVL(xab.ac5, '*') = NVL(cp_ac5, '*')
740 AND fut_periods.period_year <> cp_period_year
741 ORDER BY fut_periods.start_date;
742
743 BEGIN
744
745 IF g_log_enabled THEN
746 l_log_module := C_DEFAULT_MODULE||'.merge_balances_rec';
747 END IF;
748
749 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
750 trace
751 (p_module => l_log_module
752 ,p_msg => 'BEGIN ' || l_log_module
753 ,p_level => C_LEVEL_PROCEDURE);
754 END IF;
755
756 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
757 trace(p_module => l_log_module,p_msg => 'batch_code :'|| p_ac_balance_int_rec.batch_code,p_level => C_LEVEL_PROCEDURE);
758 trace(p_module => l_log_module,p_msg => 'application_id :'|| p_ac_balance_int_rec.application_id,p_level => C_LEVEL_STATEMENT);
759 trace(p_module => l_log_module,p_msg => 'ledger_id :'|| p_ac_balance_int_rec.ledger_id,p_level => C_LEVEL_STATEMENT);
760 trace(p_module => l_log_module,p_msg => 'code_combination_id :'|| p_ac_balance_int_rec.code_combination_id,p_level => C_LEVEL_STATEMENT);
761 trace(p_module => l_log_module,p_msg => 'analytical_criterion_code :'|| p_ac_balance_int_rec.analytical_criterion_code,p_level => C_LEVEL_STATEMENT);
762 trace(p_module => l_log_module,p_msg => 'analytical_criterion_type_code :'|| p_ac_balance_int_rec.analytical_criterion_type_code,p_level => C_LEVEL_STATEMENT);
763 trace(p_module => l_log_module,p_msg => 'amb_context_code :'|| p_ac_balance_int_rec.amb_context_code,p_level => C_LEVEL_STATEMENT);
764 trace(p_module => l_log_module,p_msg => 'ac1 :'|| p_ac_balance_int_rec.ac1,p_level => C_LEVEL_STATEMENT);
765 trace(p_module => l_log_module,p_msg => 'ac2 :'|| p_ac_balance_int_rec.ac2,p_level => C_LEVEL_STATEMENT);
766 trace(p_module => l_log_module,p_msg => 'ac3 :'|| p_ac_balance_int_rec.ac3,p_level => C_LEVEL_STATEMENT);
767 trace(p_module => l_log_module,p_msg => 'ac4 :'|| p_ac_balance_int_rec.ac4,p_level => C_LEVEL_STATEMENT);
768 trace(p_module => l_log_module,p_msg => 'ac5 :'|| p_ac_balance_int_rec.ac5,p_level => C_LEVEL_STATEMENT);
769 trace(p_module => l_log_module,p_msg => 'period_name :'|| p_ac_balance_int_rec.period_name,p_level => C_LEVEL_STATEMENT);
770 trace(p_module => l_log_module,p_msg => 'init_balance_dr :'|| p_ac_balance_int_rec.init_balance_dr,p_level => C_LEVEL_STATEMENT);
771 trace(p_module => l_log_module,p_msg => 'init_balance_cr :'|| p_ac_balance_int_rec.init_balance_cr,p_level => C_LEVEL_STATEMENT);
772 trace(p_module => l_log_module,p_msg => 'segment1 :'|| p_ac_balance_int_rec.segment1,p_level => C_LEVEL_STATEMENT);
773 trace(p_module => l_log_module,p_msg => 'segment2 :'|| p_ac_balance_int_rec.segment2,p_level => C_LEVEL_STATEMENT);
774 trace(p_module => l_log_module,p_msg => 'segment3 :'|| p_ac_balance_int_rec.segment3,p_level => C_LEVEL_STATEMENT);
775 trace(p_module => l_log_module,p_msg => 'segment4 :'|| p_ac_balance_int_rec.segment4,p_level => C_LEVEL_STATEMENT);
776 trace(p_module => l_log_module,p_msg => 'segment5 :'|| p_ac_balance_int_rec.segment5,p_level => C_LEVEL_STATEMENT);
777 trace(p_module => l_log_module,p_msg => 'segment6 :'|| p_ac_balance_int_rec.segment6,p_level => C_LEVEL_STATEMENT);
778 trace(p_module => l_log_module,p_msg => 'segment7 :'|| p_ac_balance_int_rec.segment7,p_level => C_LEVEL_STATEMENT);
779 trace(p_module => l_log_module,p_msg => 'segment8 :'|| p_ac_balance_int_rec.segment8,p_level => C_LEVEL_STATEMENT);
780 trace(p_module => l_log_module,p_msg => 'segment9 :'|| p_ac_balance_int_rec.segment9,p_level => C_LEVEL_STATEMENT);
781 trace(p_module => l_log_module,p_msg => 'segment10 :'|| p_ac_balance_int_rec.segment10,p_level => C_LEVEL_STATEMENT);
782 trace(p_module => l_log_module,p_msg => 'segment11 :'|| p_ac_balance_int_rec.segment11,p_level => C_LEVEL_STATEMENT);
783 trace(p_module => l_log_module,p_msg => 'segment12 :'|| p_ac_balance_int_rec.segment12,p_level => C_LEVEL_STATEMENT);
784 trace(p_module => l_log_module,p_msg => 'segment13 :'|| p_ac_balance_int_rec.segment13,p_level => C_LEVEL_STATEMENT);
785 trace(p_module => l_log_module,p_msg => 'segment14 :'|| p_ac_balance_int_rec.segment14,p_level => C_LEVEL_STATEMENT);
789 trace(p_module => l_log_module,p_msg => 'segment18 :'|| p_ac_balance_int_rec.segment18,p_level => C_LEVEL_STATEMENT);
786 trace(p_module => l_log_module,p_msg => 'segment15 :'|| p_ac_balance_int_rec.segment15,p_level => C_LEVEL_STATEMENT);
787 trace(p_module => l_log_module,p_msg => 'segment16 :'|| p_ac_balance_int_rec.segment16,p_level => C_LEVEL_STATEMENT);
788 trace(p_module => l_log_module,p_msg => 'segment17 :'|| p_ac_balance_int_rec.segment17,p_level => C_LEVEL_STATEMENT);
790 trace(p_module => l_log_module,p_msg => 'segment19 :'|| p_ac_balance_int_rec.segment19,p_level => C_LEVEL_STATEMENT);
791 trace(p_module => l_log_module,p_msg => 'segment20 :'|| p_ac_balance_int_rec.segment20,p_level => C_LEVEL_STATEMENT);
792 trace(p_module => l_log_module,p_msg => 'segment21 :'|| p_ac_balance_int_rec.segment21,p_level => C_LEVEL_STATEMENT);
793 trace(p_module => l_log_module,p_msg => 'segment22 :'|| p_ac_balance_int_rec.segment22,p_level => C_LEVEL_STATEMENT);
794 trace(p_module => l_log_module,p_msg => 'segment23 :'|| p_ac_balance_int_rec.segment23,p_level => C_LEVEL_STATEMENT);
795 trace(p_module => l_log_module,p_msg => 'segment24 :'|| p_ac_balance_int_rec.segment24,p_level => C_LEVEL_STATEMENT);
796 trace(p_module => l_log_module,p_msg => 'segment25 :'|| p_ac_balance_int_rec.segment25,p_level => C_LEVEL_STATEMENT);
797 trace(p_module => l_log_module,p_msg => 'segment26 :'|| p_ac_balance_int_rec.segment26,p_level => C_LEVEL_STATEMENT);
798 trace(p_module => l_log_module,p_msg => 'segment27 :'|| p_ac_balance_int_rec.segment27,p_level => C_LEVEL_STATEMENT);
799 trace(p_module => l_log_module,p_msg => 'segment28 :'|| p_ac_balance_int_rec.segment28,p_level => C_LEVEL_STATEMENT);
800 trace(p_module => l_log_module,p_msg => 'segment29 :'|| p_ac_balance_int_rec.segment29,p_level => C_LEVEL_STATEMENT);
801 trace(p_module => l_log_module,p_msg => 'segment30 :'|| p_ac_balance_int_rec.segment30,p_level => C_LEVEL_STATEMENT);
802 trace(p_module => l_log_module,p_msg => 'status :'|| p_ac_balance_int_rec.status,p_level => C_LEVEL_STATEMENT);
803 trace(p_module => l_log_module,p_msg => 'message_codes :'|| p_ac_balance_int_rec.message_codes,p_level => C_LEVEL_STATEMENT);
804 END IF;
805
806 /*
807 * first, fetch the header detail for the supporting reference
808 * Ignoring c_sup_ref_hdr%NOTFOUND assuming that validation_balances_rec has already validated it
809 */
810 OPEN c_sup_ref_hdr(p_ac_balance_int_rec.amb_context_code,p_ac_balance_int_rec.analytical_criterion_code,p_ac_balance_int_rec.analytical_criterion_type_code);
811 FETCH c_sup_ref_hdr INTO l_sup_ref_hdr;
812 CLOSE c_sup_ref_hdr;
813
814 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
815 trace(p_module => l_log_module,p_msg => 'l_sup_ref_hdr.balancing_flag:'||l_sup_ref_hdr.balancing_flag,p_level => C_LEVEL_STATEMENT);
816 trace(p_module => l_log_module,p_msg => 'l_sup_ref_hdr.year_end_carry_forward_code:'||l_sup_ref_hdr.year_end_carry_forward_code,p_level => C_LEVEL_STATEMENT);
817 END IF;
818
819 /*
820 * We balance accounts for subsequent periods only if balancing_flag='Y'
821 */
822 IF l_sup_ref_hdr.balancing_flag = 'Y' THEN
823 /*
824 * if l_sup_ref_hdr.year_end_carry_forward_code = 'A', always carry forward the balances
825 * if l_sup_ref_hdr.year_end_carry_forward_code = 'N', never forward the balances
826 * if l_sup_ref_hdr.year_end_carry_forward_code = 'B', carry forward the balances based on account
827 * For l_sup_ref_hdr.year_end_carry_forward_code = 'B',
828 * A Asset - All periods
829 * E Expense - Current year
830 * R Revenue - Current year
831 * C Budgetary (CR) - Current year
832 * D Budgetary (DR) - Current year
833 * O Owners' equity - All periods
834 * L Liability - All Periods
835 * N - All Periods
836 */
837 IF l_sup_ref_hdr.year_end_carry_forward_code = 'A' THEN
838 l_period_year := NULL;
839 ELSIF l_sup_ref_hdr.year_end_carry_forward_code = 'N' THEN
840 l_period_year := get_period_year(p_ac_balance_int_rec.period_name);
841 ELSIF l_sup_ref_hdr.year_end_carry_forward_code = 'B' THEN
842 OPEN c_account_type(p_ac_balance_int_rec.code_combination_id);
843 FETCH c_account_type INTO l_account_type;
844 CLOSE c_account_type;
845
846 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
847 trace(p_module => l_log_module,p_msg => 'l_account_type:'||l_account_type,p_level => C_LEVEL_STATEMENT);
848 END IF;
849
850 IF l_account_type IN ('A','O','L') OR l_account_type IS NULL THEN
851 l_period_year := NULL;
852 ELSIF l_account_type IN ('E','R','C','D') THEN
853 l_period_year := get_period_year(p_ac_balance_int_rec.period_name);
854 END IF;
855 END IF;
856 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
857 trace(p_module => l_log_module,p_msg => 'l_period_year:'||l_period_year,p_level => C_LEVEL_STATEMENT);
858 END IF;
859 /*
860 * For maintaining balances, we need to peek into xla_ac_balances table and find which period(ocurring after the period for which
861 * data is being imported) is next. We get this "next period" and see if there are more periods in between the period for which
862 * data is being imported and the "next period". We need to insert new records for such intermediate periods
863 */
864
865 OPEN c_next_period_start_date(p_ac_balance_int_rec.application_id,
866 p_ac_balance_int_rec.ledger_id,
867 p_ac_balance_int_rec.code_combination_id,
868 p_ac_balance_int_rec.analytical_criterion_code,
869 p_ac_balance_int_rec.analytical_criterion_type_code,
870 p_ac_balance_int_rec.amb_context_code,
871 p_ac_balance_int_rec.ac1,
872 p_ac_balance_int_rec.ac2,
873 p_ac_balance_int_rec.ac3,
877 p_ac_balance_int_rec.period_name);
874 p_ac_balance_int_rec.ac4,
875 p_ac_balance_int_rec.ac5,
876 l_period_year,
878 FETCH c_next_period_start_date INTO l_next_period_start_date;
879 CLOSE c_next_period_start_date;
880 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
881 trace(p_module => l_log_module,p_msg => 'l_next_period_start_date:'||l_next_period_start_date,p_level => C_LEVEL_STATEMENT);
882 END IF;
883
884 l_synchronize_fut_periods := TRUE;
885 IF l_next_period_start_date IS NULL THEN
886 l_synchronize_fut_periods := FALSE;
887 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
888 trace(p_module => l_log_module,p_msg => 'l_synchronize_fut_periods set to FALSE',p_level => C_LEVEL_STATEMENT);
889 END IF;
890 END IF;
891
892 /*
893 * get current period end date
894 */
895 OPEN c_current_period_end_date(p_ac_balance_int_rec.ledger_id,p_ac_balance_int_rec.application_id,p_ac_balance_int_rec.period_name,l_period_year);
896 FETCH c_current_period_end_date INTO l_current_period_end_date;
897 CLOSE c_current_period_end_date;
898 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
899 trace(p_module => l_log_module,p_msg => 'l_current_period_end_date:'||l_current_period_end_date,p_level => C_LEVEL_STATEMENT);
900 END IF;
901 END IF;
902
903 /*
904 * a new record
905 */
906 -- find out if there is an existing record
907 OPEN c_exist_balance(
908 p_ac_balance_int_rec.ledger_id,
909 p_ac_balance_int_rec.code_combination_id,
910 p_ac_balance_int_rec.analytical_criterion_code,
911 p_ac_balance_int_rec.analytical_criterion_type_code,
912 p_ac_balance_int_rec.amb_context_code,
913 p_ac_balance_int_rec.ac1,
914 p_ac_balance_int_rec.ac2,
915 p_ac_balance_int_rec.ac3,
916 p_ac_balance_int_rec.ac4,
917 p_ac_balance_int_rec.ac5,
918 p_ac_balance_int_rec.period_name
919 );
920 FETCH c_exist_balance INTO l_exist_balance;
921 IF c_exist_balance%FOUND THEN
922 CLOSE c_exist_balance;
923 /*
924 * Record exists
925 */
926 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
927 trace(p_module => l_log_module,p_msg => 'c_exist_balance%FOUND',p_level => C_LEVEL_STATEMENT);
928 END IF;
929 -- update the cr/dr
930 IF p_ac_balance_int_rec.init_balance_dr IS NOT NULL AND p_ac_balance_int_rec.init_balance_dr <> 0 THEN
931 -- update dr
932 l_delta_dr := p_ac_balance_int_rec.init_balance_dr - NVL(l_exist_balance.beginning_balance_dr,0);
933 l_delta_cr := 0;
934 l_exist_balance.beginning_balance_dr := p_ac_balance_int_rec.init_balance_dr;
935 --l_exist_balance.initial_balance_flag := 'N';
936
937 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
938 trace(p_module => l_log_module,p_msg => 'case 1',p_level => C_LEVEL_STATEMENT);
939 trace(p_module => l_log_module,p_msg => 'l_delta_dr:'||l_delta_dr,p_level => C_LEVEL_STATEMENT);
940 trace(p_module => l_log_module,p_msg => 'l_delta_cr:'||l_delta_cr,p_level => C_LEVEL_STATEMENT);
941 trace(p_module => l_log_module,p_msg => 'l_exist_balance.beginning_balance_dr:'||l_exist_balance.beginning_balance_dr,p_level => C_LEVEL_STATEMENT);
942 trace(p_module => l_log_module,p_msg => 'l_exist_balance.initial_balance_flag:'||l_exist_balance.initial_balance_flag,p_level => C_LEVEL_STATEMENT);
943 END IF;
944 update_balances_rec(l_exist_balance);
945 ELSIF p_ac_balance_int_rec.init_balance_cr IS NOT NULL AND p_ac_balance_int_rec.init_balance_cr <> 0 THEN
946 -- update cr
947 l_delta_cr := p_ac_balance_int_rec.init_balance_cr - NVL(l_exist_balance.beginning_balance_cr,0);
948 l_delta_dr := 0;
949 l_exist_balance.beginning_balance_cr := p_ac_balance_int_rec.init_balance_cr;
950 --l_exist_balance.initial_balance_flag := 'N';
951
952 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
953 trace(p_module => l_log_module,p_msg => 'case 2',p_level => C_LEVEL_STATEMENT);
954 trace(p_module => l_log_module,p_msg => 'l_delta_dr:'||l_delta_dr,p_level => C_LEVEL_STATEMENT);
955 trace(p_module => l_log_module,p_msg => 'l_delta_cr:'||l_delta_cr,p_level => C_LEVEL_STATEMENT);
956 trace(p_module => l_log_module,p_msg => 'l_exist_balance.beginning_balance_cr:'||l_exist_balance.beginning_balance_cr,p_level => C_LEVEL_STATEMENT);
957 trace(p_module => l_log_module,p_msg => 'l_exist_balance.initial_balance_flag:'||l_exist_balance.initial_balance_flag,p_level => C_LEVEL_STATEMENT);
958 END IF;
959 update_balances_rec(l_exist_balance);
960 ELSIF p_ac_balance_int_rec.init_balance_dr = 0 AND p_ac_balance_int_rec.init_balance_dr = 0 THEN
961 IF l_exist_balance.initial_balance_flag = 'Y' AND (l_exist_balance.period_balance_dr IS NOT NULL OR l_exist_balance.period_balance_cr IS NOT NULL) THEN
962 -- (logical) delete record
963 l_delta_cr := NVL(-1 * l_exist_balance.beginning_balance_cr,0);
964 l_delta_dr := NVL(-1 * l_exist_balance.beginning_balance_dr,0);
965
966 l_exist_balance.beginning_balance_dr := NULL;
967 l_exist_balance.beginning_balance_cr := NULL;
968 l_exist_balance.initial_balance_flag := 'N';
969
970 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
971 trace(p_module => l_log_module,p_msg => 'case 3',p_level => C_LEVEL_STATEMENT);
972 trace(p_module => l_log_module,p_msg => 'l_delta_dr:'||l_delta_dr,p_level => C_LEVEL_STATEMENT);
973 trace(p_module => l_log_module,p_msg => 'l_delta_cr:'||l_delta_cr,p_level => C_LEVEL_STATEMENT);
977 END IF;
974 trace(p_module => l_log_module,p_msg => 'l_exist_balance.beginning_balance_dr:'||l_exist_balance.beginning_balance_dr,p_level => C_LEVEL_STATEMENT);
975 trace(p_module => l_log_module,p_msg => 'l_exist_balance.beginning_balance_cr:'||l_exist_balance.beginning_balance_cr,p_level => C_LEVEL_STATEMENT);
976 trace(p_module => l_log_module,p_msg => 'l_exist_balance.initial_balance_flag:'||l_exist_balance.initial_balance_flag,p_level => C_LEVEL_STATEMENT);
978 update_balances_rec(l_exist_balance);
979 ELSIF l_exist_balance.initial_balance_flag = 'Y' AND l_exist_balance.period_balance_dr IS NULL AND l_exist_balance.period_balance_cr IS NULL THEN
980 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
981 trace(p_module => l_log_module,p_msg => 'calling delete_balances_rec',p_level => C_LEVEL_STATEMENT);
982 END IF;
983 l_delta_cr := NVL(-1 * l_exist_balance.beginning_balance_cr,0);
984 l_delta_dr := NVL(-1 * l_exist_balance.beginning_balance_dr,0);
985 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
986 trace(p_module => l_log_module,p_msg => 'l_delta_cr:'||l_delta_cr,p_level => C_LEVEL_STATEMENT);
987 trace(p_module => l_log_module,p_msg => 'l_delta_dr:'||l_delta_dr,p_level => C_LEVEL_STATEMENT);
988 END IF;
989 delete_balances_rec(l_exist_balance);
990
991 ELSIF l_exist_balance.initial_balance_flag = 'N' THEN
992 RAISE CANT_DELETE_BALANCES;
993 END IF;
994 END IF;
995
996 IF l_synchronize_fut_periods THEN
997 l_cr := NVL(l_delta_cr,0);
998 l_dr := NVL(l_delta_dr,0);
999 OPEN c_subsequent_periods(p_ac_balance_int_rec.application_id,
1000 p_ac_balance_int_rec.ledger_id,
1001 p_ac_balance_int_rec.code_combination_id,
1002 p_ac_balance_int_rec.analytical_criterion_code,
1003 p_ac_balance_int_rec.analytical_criterion_type_code,
1004 p_ac_balance_int_rec.amb_context_code,
1005 p_ac_balance_int_rec.ac1,
1006 p_ac_balance_int_rec.ac2,
1007 p_ac_balance_int_rec.ac3,
1008 p_ac_balance_int_rec.ac4,
1009 p_ac_balance_int_rec.ac5,
1010 l_period_year,
1011 p_ac_balance_int_rec.period_name);
1012 FETCH c_subsequent_periods INTO l_subsequent_periods;
1013 WHILE c_subsequent_periods%FOUND LOOP
1014 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1015 trace(p_module => l_log_module,p_msg => '1.c_subsequent_periods fetched period_name:'||l_subsequent_periods.period_name||'/'||'period_year:'||l_subsequent_periods.period_year,p_level => C_LEVEL_STATEMENT);
1016 END IF;
1017
1018 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1019 trace(p_module => l_log_module,p_msg => '1-l_subsequent_periods.beginning_balance_dr:'||l_subsequent_periods.beginning_balance_dr,p_level => C_LEVEL_STATEMENT);
1020 trace(p_module => l_log_module,p_msg => '1-l_subsequent_periods.beginning_balance_cr:'||l_subsequent_periods.beginning_balance_cr,p_level => C_LEVEL_STATEMENT);
1021 trace(p_module => l_log_module,p_msg => '1-l_delta_dr:'||l_delta_dr,p_level => C_LEVEL_STATEMENT);
1022 trace(p_module => l_log_module,p_msg => '1-l_delta_cr:'||l_delta_cr,p_level => C_LEVEL_STATEMENT);
1023 trace(p_module => l_log_module,p_msg => '1-l_subsequent_periods.period_balance_dr:'||l_subsequent_periods.period_balance_dr,p_level => C_LEVEL_STATEMENT);
1024 trace(p_module => l_log_module,p_msg => '1-l_subsequent_periods.period_balance_cr:'||l_subsequent_periods.period_balance_cr,p_level => C_LEVEL_STATEMENT);
1025 END IF;
1026
1027 IF l_cr IS NULL AND l_dr IS NULL THEN
1028 l_subsequent_periods.beginning_balance_dr := NVL(l_delta_dr,0);
1029 l_subsequent_periods.beginning_balance_cr := NVL(l_delta_cr,0);
1030
1031 l_dr := NVL(l_subsequent_periods.beginning_balance_dr,0) + NVL(l_subsequent_periods.period_balance_dr,0);
1032 l_cr := NVL(l_subsequent_periods.beginning_balance_cr,0) + NVL(l_subsequent_periods.period_balance_cr,0);
1033
1034 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1035 trace(p_module => l_log_module,p_msg => '1.1-l_subsequent_periods.beginning_balance_dr:'||l_subsequent_periods.beginning_balance_dr,p_level => C_LEVEL_STATEMENT);
1036 trace(p_module => l_log_module,p_msg => '1.1-l_subsequent_periods.beginning_balance_cr:'||l_subsequent_periods.beginning_balance_cr,p_level => C_LEVEL_STATEMENT);
1037 END IF;
1038 ELSE
1039 l_subsequent_periods.beginning_balance_dr := NVL(l_subsequent_periods.beginning_balance_dr,0) + NVL(l_delta_dr,0);
1040 l_subsequent_periods.beginning_balance_cr := NVL(l_subsequent_periods.beginning_balance_cr,0) + NVL(l_delta_cr,0);
1041
1042 l_dr := NVL(l_dr,0) + NVL(l_subsequent_periods.period_balance_dr,0);
1043 l_cr := NVL(l_cr,0) + NVL(l_subsequent_periods.period_balance_cr,0);
1044
1045 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1046 trace(p_module => l_log_module,p_msg => '1.2-l_subsequent_periods.beginning_balance_dr:'||l_subsequent_periods.beginning_balance_dr,p_level => C_LEVEL_STATEMENT);
1047 trace(p_module => l_log_module,p_msg => '1.2-l_subsequent_periods.beginning_balance_cr:'||l_subsequent_periods.beginning_balance_cr,p_level => C_LEVEL_STATEMENT);
1048 END IF;
1049 END IF;
1050 l_subsequent_periods.initial_balance_flag := 'N';
1051
1052 IF l_subsequent_periods.beginning_balance_cr = 0 THEN
1053 l_subsequent_periods.beginning_balance_cr := NULL;
1054 END IF;
1055 IF l_subsequent_periods.beginning_balance_dr = 0 THEN
1056 l_subsequent_periods.beginning_balance_dr := NULL;
1057 END IF;
1058 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1062 trace(p_module => l_log_module,p_msg => 'l_subsequent_periods.period_balance_cr:'||l_subsequent_periods.period_balance_cr,p_level => C_LEVEL_STATEMENT);
1059 trace(p_module => l_log_module,p_msg => 'l_subsequent_periods.beginning_balance_dr:'||l_subsequent_periods.beginning_balance_dr,p_level => C_LEVEL_STATEMENT);
1060 trace(p_module => l_log_module,p_msg => 'l_subsequent_periods.beginning_balance_cr:'||l_subsequent_periods.beginning_balance_cr,p_level => C_LEVEL_STATEMENT);
1061 trace(p_module => l_log_module,p_msg => 'l_subsequent_periods.period_balance_dr:'||l_subsequent_periods.period_balance_dr,p_level => C_LEVEL_STATEMENT);
1063 END IF;
1064
1065 /*
1066 * If we are deleting a balances record, we need to delete future balances records
1067 * which don't have any period activity
1068 */
1069 IF (l_subsequent_periods.beginning_balance_dr IS NULL AND l_subsequent_periods.beginning_balance_cr IS NULL AND NVL(l_subsequent_periods.period_balance_cr,0) = 0 AND NVL(l_subsequent_periods.period_balance_dr,0) = 0) THEN
1070 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1071 trace(p_module => l_log_module,p_msg => 'calling delete_balances_rec for period:'||l_subsequent_periods.period_name,p_level => C_LEVEL_STATEMENT);
1072 END IF;
1073 delete_balances_rec(l_subsequent_periods);
1074 ELSE
1075 l_period_num := NULL;
1076 OPEN c_period_num(l_subsequent_periods.ledger_id,l_subsequent_periods.period_name);
1077 FETCH c_period_num INTO l_period_num;
1078 CLOSE c_period_num;
1079 IF l_period_num = 1 THEN
1080 l_subsequent_periods.first_period_flag := 'Y';
1081 ELSE
1082 l_subsequent_periods.first_period_flag := 'N';
1083 END IF;
1084 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1085 trace(p_module => l_log_module,p_msg => 'l_subsequent_periods.first_period_flag:'||l_subsequent_periods.first_period_flag||' for '||l_subsequent_periods.period_name,p_level => C_LEVEL_STATEMENT);
1086 END IF;
1087 update_balances_rec(l_subsequent_periods);
1088 END IF;
1089 FETCH c_subsequent_periods INTO l_subsequent_periods;
1090 END LOOP;
1091
1092
1093 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1094 trace(p_module => l_log_module,p_msg => 'opening c_future_open_periods with l_subsequent_periods.period_name:'||l_subsequent_periods.period_name||',l_period_year:'||l_period_year,p_level => C_LEVEL_STATEMENT);
1095 END IF;
1096 OPEN c_future_open_periods(p_ac_balance_int_rec.application_id,
1097 p_ac_balance_int_rec.ledger_id,
1098 l_subsequent_periods.period_name,
1099 l_period_year);
1100 FETCH c_future_open_periods INTO l_future_open_periods;
1101 IF c_future_open_periods%FOUND THEN
1102 /*
1103 * There are future open periods. so insert new records for them with zero period activity
1104 */
1105 WHILE c_future_open_periods%FOUND LOOP
1106 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1107 trace(p_module => l_log_module,p_msg => 'c_future_open_periods fetched period:'||l_future_open_periods.period_name,p_level => C_LEVEL_STATEMENT);
1108 END IF;
1109 l_balances_rec := NULL;
1110 l_balances_rec.application_id := p_ac_balance_int_rec.application_id;
1111 l_balances_rec.ledger_id := p_ac_balance_int_rec.ledger_id;
1112 l_balances_rec.code_combination_id := p_ac_balance_int_rec.code_combination_id;
1113 l_balances_rec.analytical_criterion_code := p_ac_balance_int_rec.analytical_criterion_code;
1114 l_balances_rec.analytical_criterion_type_code := p_ac_balance_int_rec.analytical_criterion_type_code;
1115 l_balances_rec.amb_context_code := p_ac_balance_int_rec.amb_context_code;
1116 l_balances_rec.ac1 := p_ac_balance_int_rec.ac1;
1117 l_balances_rec.ac2 := p_ac_balance_int_rec.ac2;
1118 l_balances_rec.ac3 := p_ac_balance_int_rec.ac3;
1119 l_balances_rec.ac4 := p_ac_balance_int_rec.ac4;
1120 l_balances_rec.ac5 := p_ac_balance_int_rec.ac5;
1121 l_balances_rec.period_name := l_future_open_periods.period_name;
1122 l_balances_rec.beginning_balance_dr := l_subsequent_periods.beginning_balance_dr;
1123 l_balances_rec.beginning_balance_cr := l_subsequent_periods.beginning_balance_cr;
1124 l_balances_rec.period_balance_dr := NULL;
1125 l_balances_rec.period_balance_cr := NULL;
1126 l_balances_rec.initial_balance_flag := 'N';
1127 l_balances_rec.period_year := NVL(l_period_year,get_period_year(l_future_open_periods.period_name));
1128 IF l_future_open_periods.period_num = 1 THEN
1129 l_balances_rec.first_period_flag := 'Y';
1130 ELSE
1131 l_balances_rec.first_period_flag := 'N';
1132 END IF;
1133 insert_balances_rec(l_balances_rec);
1134 FETCH c_future_open_periods INTO l_future_open_periods;
1135 END LOOP;
1136 END IF;
1137
1138 IF l_account_type IN ('E','R','C','D') AND l_sup_ref_hdr.year_end_carry_forward_code = 'B' THEN
1139 l_period_year := get_period_year(p_ac_balance_int_rec.period_name);
1140 l_prev_year := NULL;
1141 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1142 trace(p_module => l_log_module,p_msg => 'opening c_delete_records for year :'||l_period_year,p_level => C_LEVEL_STATEMENT);
1143 END IF;
1144 FOR l_delete_records IN c_delete_records(p_ac_balance_int_rec.application_id,
1148 p_ac_balance_int_rec.analytical_criterion_type_code,
1145 p_ac_balance_int_rec.ledger_id,
1146 p_ac_balance_int_rec.code_combination_id,
1147 p_ac_balance_int_rec.analytical_criterion_code,
1149 p_ac_balance_int_rec.amb_context_code,
1150 p_ac_balance_int_rec.ac1,
1151 p_ac_balance_int_rec.ac2,
1152 p_ac_balance_int_rec.ac3,
1153 p_ac_balance_int_rec.ac4,
1154 p_ac_balance_int_rec.ac5,
1155 l_period_year) LOOP
1156 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1157 trace(p_module => l_log_module,p_msg => 'c_delete_records fetched period:'||l_delete_records.period_name,p_level => C_LEVEL_STATEMENT);
1158 END IF;
1159 IF c_delete_records%ROWCOUNT = 1 AND (l_delete_records.period_balance_dr IS NOT NULL OR l_delete_records.period_balance_cr IS NOT NULL) THEN
1160 EXIT;
1161 END IF;
1162
1163 IF c_delete_records%ROWCOUNT = 1 AND l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
1164 delete_balances_rec(l_delete_records);
1165 l_delete_cr_delta := l_delete_records.beginning_balance_cr;
1166 l_delete_dr_delta := l_delete_records.beginning_balance_dr;
1167 l_prev_year := l_delete_records.period_year;
1168 ELSE
1169 IF l_prev_year = l_delete_records.period_year THEN
1170 IF l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
1171 delete_balances_rec(l_delete_records);
1172 ELSE
1173 l_delete_records.beginning_balance_cr := NVL(l_delete_records.beginning_balance_cr,0) + NVL(l_delete_cr_delta,0);
1174 l_delete_records.beginning_balance_dr := NVL(l_delete_records.beginning_balance_dr,0) + NVL(l_delete_dr_delta,0);
1175 update_balances_rec(l_delete_records);
1176 END IF;
1177 END IF;
1178 END IF;
1179 END LOOP;
1180 END IF;
1181 ELSE
1182 IF l_account_type IN ('E','R','C','D') AND l_sup_ref_hdr.year_end_carry_forward_code = 'B' THEN
1183 l_period_year := get_period_year(p_ac_balance_int_rec.period_name);
1184 l_prev_year := NULL;
1185 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1186 trace(p_module => l_log_module,p_msg => 'opening c_delete_records for year :'||l_period_year,p_level => C_LEVEL_STATEMENT);
1187 END IF;
1188 FOR l_delete_records IN c_delete_records(p_ac_balance_int_rec.application_id,
1189 p_ac_balance_int_rec.ledger_id,
1190 p_ac_balance_int_rec.code_combination_id,
1191 p_ac_balance_int_rec.analytical_criterion_code,
1192 p_ac_balance_int_rec.analytical_criterion_type_code,
1193 p_ac_balance_int_rec.amb_context_code,
1194 p_ac_balance_int_rec.ac1,
1195 p_ac_balance_int_rec.ac2,
1196 p_ac_balance_int_rec.ac3,
1197 p_ac_balance_int_rec.ac4,
1198 p_ac_balance_int_rec.ac5,
1199 l_period_year) LOOP
1200 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1201 trace(p_module => l_log_module,p_msg => 'c_delete_records fetched period:'||l_delete_records.period_name,p_level => C_LEVEL_STATEMENT);
1202 END IF;
1203 IF c_delete_records%ROWCOUNT = 1 AND (l_delete_records.period_balance_dr IS NOT NULL OR l_delete_records.period_balance_cr IS NOT NULL) THEN
1204 EXIT;
1205 END IF;
1206
1207 IF c_delete_records%ROWCOUNT = 1 AND l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
1208 delete_balances_rec(l_delete_records);
1209 l_delete_cr_delta := l_delete_records.beginning_balance_cr;
1210 l_delete_dr_delta := l_delete_records.beginning_balance_dr;
1211 l_prev_year := l_delete_records.period_year;
1212 ELSE
1213 IF l_prev_year = l_delete_records.period_year THEN
1214 IF l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
1215 delete_balances_rec(l_delete_records);
1216 ELSE
1217 l_delete_records.beginning_balance_cr := NVL(l_delete_records.beginning_balance_cr,0) + NVL(l_delete_cr_delta,0);
1218 l_delete_records.beginning_balance_dr := NVL(l_delete_records.beginning_balance_dr,0) + NVL(l_delete_dr_delta,0);
1219 update_balances_rec(l_delete_records);
1220 END IF;
1221 END IF;
1222 END IF;
1223 END LOOP;
1224 END IF;
1225 END IF;
1226 ELSE
1227 CLOSE c_exist_balance;
1228 /*
1229 * Record does not exist
1230 */
1231 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1232 trace(p_module => l_log_module,p_msg => 'c_exist_balance%NOTFOUND',p_level => C_LEVEL_STATEMENT);
1233 END IF;
1234 IF l_balances_rec.beginning_balance_dr = 0 AND l_balances_rec.beginning_balance_cr = 0 THEN
1235 RAISE CANT_DELETE_BALANCES;
1236 END IF;
1237 -- insert record here
1241 l_balances_rec.analytical_criterion_code := p_ac_balance_int_rec.analytical_criterion_code;
1238 l_balances_rec.application_id := p_ac_balance_int_rec.application_id;
1239 l_balances_rec.ledger_id := p_ac_balance_int_rec.ledger_id;
1240 l_balances_rec.code_combination_id := p_ac_balance_int_rec.code_combination_id;
1242 l_balances_rec.analytical_criterion_type_code := p_ac_balance_int_rec.analytical_criterion_type_code;
1243 l_balances_rec.amb_context_code := p_ac_balance_int_rec.amb_context_code;
1244 l_balances_rec.ac1 := p_ac_balance_int_rec.ac1;
1245 l_balances_rec.ac2 := p_ac_balance_int_rec.ac2;
1246 l_balances_rec.ac3 := p_ac_balance_int_rec.ac3;
1247 l_balances_rec.ac4 := p_ac_balance_int_rec.ac4;
1248 l_balances_rec.ac5 := p_ac_balance_int_rec.ac5;
1249 l_balances_rec.period_name := p_ac_balance_int_rec.period_name;
1250 l_balances_rec.beginning_balance_dr := p_ac_balance_int_rec.init_balance_dr;
1251 l_balances_rec.beginning_balance_cr := p_ac_balance_int_rec.init_balance_cr;
1252 l_balances_rec.period_balance_dr := NULL;
1253 l_balances_rec.period_balance_cr := NULL;
1254 l_balances_rec.initial_balance_flag := 'Y';
1255
1256 l_balances_rec.period_year := get_period_year(p_ac_balance_int_rec.period_name);
1257
1258 l_period_num := NULL;
1259 OPEN c_period_num(l_balances_rec.ledger_id,l_balances_rec.period_name);
1260 FETCH c_period_num INTO l_period_num;
1261 CLOSE c_period_num;
1262 IF l_period_num = 1 THEN
1263 l_balances_rec.first_period_flag := 'Y';
1264 ELSE
1265 l_balances_rec.first_period_flag := 'N';
1266 END IF;
1267
1268 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1269 trace(p_module => l_log_module,p_msg => 'p_ac_balance_int_rec.period_name:'||p_ac_balance_int_rec.period_name,p_level => C_LEVEL_STATEMENT);
1270 trace(p_module => l_log_module,p_msg => 'p_ac_balance_int_rec.init_balance_dr:'||p_ac_balance_int_rec.init_balance_dr,p_level => C_LEVEL_STATEMENT);
1271 trace(p_module => l_log_module,p_msg => 'p_ac_balance_int_rec.init_balance_cr:'||p_ac_balance_int_rec.init_balance_cr,p_level => C_LEVEL_STATEMENT);
1272 trace(p_module => l_log_module,p_msg => 'l_balances_rec.initial_balance_flag:'||l_balances_rec.initial_balance_flag,p_level => C_LEVEL_STATEMENT);
1273 trace(p_module => l_log_module,p_msg => 'calling insert_balances_rec',p_level => C_LEVEL_STATEMENT);
1274 END IF;
1275 insert_balances_rec(l_balances_rec);
1276
1277 IF l_synchronize_fut_periods THEN
1278 l_cr := NULL;
1279 l_dr := NULL;
1280 FOR l_subsequent_periods IN c_subsequent_periods(p_ac_balance_int_rec.application_id,
1281 p_ac_balance_int_rec.ledger_id,
1282 p_ac_balance_int_rec.code_combination_id,
1283 p_ac_balance_int_rec.analytical_criterion_code,
1284 p_ac_balance_int_rec.analytical_criterion_type_code,
1285 p_ac_balance_int_rec.amb_context_code,
1286 p_ac_balance_int_rec.ac1,
1287 p_ac_balance_int_rec.ac2,
1288 p_ac_balance_int_rec.ac3,
1289 p_ac_balance_int_rec.ac4,
1290 p_ac_balance_int_rec.ac5,
1291 l_period_year,
1292 p_ac_balance_int_rec.period_name) LOOP
1293 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1294 trace(p_module => l_log_module,p_msg => '2.c_subsequent_periods fetched period_name:'||l_subsequent_periods.period_name||'/'||'period_year:'||l_subsequent_periods.period_year,p_level => C_LEVEL_STATEMENT);
1295 END IF;
1296 l_delta_cr := NVL(p_ac_balance_int_rec.init_balance_cr,0);
1297 l_delta_dr := NVL(p_ac_balance_int_rec.init_balance_dr,0);
1298
1299 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1300 trace(p_module => l_log_module,p_msg => '2-l_subsequent_periods.beginning_balance_dr:'||l_subsequent_periods.beginning_balance_dr,p_level => C_LEVEL_STATEMENT);
1301 trace(p_module => l_log_module,p_msg => '2-l_subsequent_periods.beginning_balance_cr:'||l_subsequent_periods.beginning_balance_cr,p_level => C_LEVEL_STATEMENT);
1302 trace(p_module => l_log_module,p_msg => '2-l_delta_dr:'||l_delta_dr,p_level => C_LEVEL_STATEMENT);
1303 trace(p_module => l_log_module,p_msg => '2-l_delta_cr:'||l_delta_cr,p_level => C_LEVEL_STATEMENT);
1304 trace(p_module => l_log_module,p_msg => '2-l_subsequent_periods.period_balance_dr:'||l_subsequent_periods.period_balance_dr,p_level => C_LEVEL_STATEMENT);
1305 trace(p_module => l_log_module,p_msg => '2-l_subsequent_periods.period_balance_cr:'||l_subsequent_periods.period_balance_cr,p_level => C_LEVEL_STATEMENT);
1306 END IF;
1307
1308
1309 IF l_cr IS NULL AND l_dr IS NULL THEN
1310 l_subsequent_periods.beginning_balance_dr := NVL(l_delta_dr,0);
1311 l_subsequent_periods.beginning_balance_cr := NVL(l_delta_cr,0);
1312
1313 l_dr := NVL(l_subsequent_periods.beginning_balance_dr,0) + NVL(l_subsequent_periods.period_balance_dr,0);
1314 l_cr := NVL(l_subsequent_periods.beginning_balance_cr,0) + NVL(l_subsequent_periods.period_balance_cr,0);
1315 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1316 trace(p_module => l_log_module,p_msg => '2.1-l_subsequent_periods.beginning_balance_dr:'||l_subsequent_periods.beginning_balance_dr,p_level => C_LEVEL_STATEMENT);
1320 l_subsequent_periods.beginning_balance_dr := l_dr;
1317 trace(p_module => l_log_module,p_msg => '2.1-l_subsequent_periods.beginning_balance_cr:'||l_subsequent_periods.beginning_balance_cr,p_level => C_LEVEL_STATEMENT);
1318 END IF;
1319 ELSE
1321 l_subsequent_periods.beginning_balance_cr := l_cr;
1322
1323 l_dr := NVL(l_dr,0) + NVL(l_subsequent_periods.period_balance_dr,0);
1324 l_cr := NVL(l_cr,0) + NVL(l_subsequent_periods.period_balance_cr,0);
1325 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1326 trace(p_module => l_log_module,p_msg => '2.2-l_subsequent_periods.beginning_balance_dr:'||l_subsequent_periods.beginning_balance_dr,p_level => C_LEVEL_STATEMENT);
1327 trace(p_module => l_log_module,p_msg => '2.2-l_subsequent_periods.beginning_balance_cr:'||l_subsequent_periods.beginning_balance_cr,p_level => C_LEVEL_STATEMENT);
1328 END IF;
1329 END IF;
1330
1331 IF l_subsequent_periods.beginning_balance_cr = 0 THEN
1332 l_subsequent_periods.beginning_balance_cr := NULL;
1333 END IF;
1334 IF l_subsequent_periods.beginning_balance_dr = 0 THEN
1335 l_subsequent_periods.beginning_balance_dr := NULL;
1336 END IF;
1337 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1338 trace(p_module => l_log_module,p_msg => 'l_subsequent_periods.beginning_balance_dr:'||l_subsequent_periods.beginning_balance_dr,p_level => C_LEVEL_STATEMENT);
1339 trace(p_module => l_log_module,p_msg => 'l_subsequent_periods.beginning_balance_cr:'||l_subsequent_periods.beginning_balance_cr,p_level => C_LEVEL_STATEMENT);
1340 END IF;
1341
1342 l_subsequent_periods.initial_balance_flag := 'N';
1343
1344 l_period_num := NULL;
1345 OPEN c_period_num(l_subsequent_periods.ledger_id,l_subsequent_periods.period_name);
1346 FETCH c_period_num INTO l_period_num;
1347 CLOSE c_period_num;
1348 IF l_period_num = 1 THEN
1349 l_subsequent_periods.first_period_flag := 'Y';
1350 ELSE
1351 l_subsequent_periods.first_period_flag := 'N';
1352 END IF;
1353 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1354 trace(p_module => l_log_module,p_msg => 'l_subsequent_periods.first_period_flag:'||l_subsequent_periods.first_period_flag||' for '||l_subsequent_periods.period_name,p_level => C_LEVEL_STATEMENT);
1355 END IF;
1356 update_balances_rec(l_subsequent_periods);
1357 END LOOP;
1358
1359 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1360 trace(p_module => l_log_module,p_msg => 'starting insert/update for future periods',p_level => C_LEVEL_STATEMENT);
1361 END IF;
1362 FOR l_future_periods IN c_future_periods(p_ac_balance_int_rec.application_id,
1363 p_ac_balance_int_rec.ledger_id,
1364 l_period_year,
1365 l_current_period_end_date,
1366 l_next_period_start_date
1367 ) LOOP
1368 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1369 trace(p_module => l_log_module,p_msg => 'c_future_periods fetched period_name:'||l_future_periods.period_name||'/'||'period_year:'||l_future_periods.period_year,p_level => C_LEVEL_STATEMENT);
1370 END IF;
1371 l_balances_rec := NULL;
1372 l_balances_rec.application_id := p_ac_balance_int_rec.application_id;
1373 l_balances_rec.ledger_id := p_ac_balance_int_rec.ledger_id;
1374 l_balances_rec.code_combination_id := p_ac_balance_int_rec.code_combination_id;
1375 l_balances_rec.analytical_criterion_code := p_ac_balance_int_rec.analytical_criterion_code;
1376 l_balances_rec.analytical_criterion_type_code := p_ac_balance_int_rec.analytical_criterion_type_code;
1377 l_balances_rec.amb_context_code := p_ac_balance_int_rec.amb_context_code;
1378 l_balances_rec.ac1 := p_ac_balance_int_rec.ac1;
1379 l_balances_rec.ac2 := p_ac_balance_int_rec.ac2;
1380 l_balances_rec.ac3 := p_ac_balance_int_rec.ac3;
1381 l_balances_rec.ac4 := p_ac_balance_int_rec.ac4;
1382 l_balances_rec.ac5 := p_ac_balance_int_rec.ac5;
1383 l_balances_rec.period_name := l_future_periods.period_name;
1384 l_balances_rec.beginning_balance_dr := p_ac_balance_int_rec.init_balance_dr;
1385 l_balances_rec.beginning_balance_cr := p_ac_balance_int_rec.init_balance_cr;
1386 l_balances_rec.period_balance_dr := NULL;
1387 l_balances_rec.period_balance_cr := NULL;
1388 l_balances_rec.initial_balance_flag := 'N';
1389
1390 IF l_future_periods.period_num = 1 THEN
1391 l_balances_rec.first_period_flag := 'Y';
1392 ELSE
1393 l_balances_rec.first_period_flag := 'N';
1394 END IF;
1395 l_balances_rec.period_year := l_future_periods.period_year;
1396 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1397 trace(p_module => l_log_module,p_msg => 'l_balances_rec.first_period_flag:'||l_balances_rec.first_period_flag||' for '||l_future_periods.period_name,p_level => C_LEVEL_STATEMENT);
1398 END IF;
1399 insert_balances_rec(l_balances_rec);
1400 END LOOP;
1401 ELSE
1402 /*
1403 * l_synchronize_fut_periods = FALSE means there are no future periods which have balances
1404 * In that case, insert new balances for periods until the last open or future entry period
1405 * If no open/future entry periods exist, then go until the last closed period
1406 */
1407 OPEN c_future_open_periods(p_ac_balance_int_rec.application_id,
1408 p_ac_balance_int_rec.ledger_id,
1412 IF c_future_open_periods%FOUND THEN
1409 p_ac_balance_int_rec.period_name,
1410 l_period_year);
1411 FETCH c_future_open_periods INTO l_future_open_periods;
1413 /*
1414 * There are future open periods. so insert new records for them with zero period activity
1415 */
1416 WHILE c_future_open_periods%FOUND LOOP
1417 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1418 trace(p_module => l_log_module,p_msg => 'c_future_open_periods fetched period:'||l_future_open_periods.period_name,p_level => C_LEVEL_STATEMENT);
1419 END IF;
1420 l_balances_rec := NULL;
1421 l_balances_rec.application_id := p_ac_balance_int_rec.application_id;
1422 l_balances_rec.ledger_id := p_ac_balance_int_rec.ledger_id;
1423 l_balances_rec.code_combination_id := p_ac_balance_int_rec.code_combination_id;
1424 l_balances_rec.analytical_criterion_code := p_ac_balance_int_rec.analytical_criterion_code;
1425 l_balances_rec.analytical_criterion_type_code := p_ac_balance_int_rec.analytical_criterion_type_code;
1426 l_balances_rec.amb_context_code := p_ac_balance_int_rec.amb_context_code;
1427 l_balances_rec.ac1 := p_ac_balance_int_rec.ac1;
1428 l_balances_rec.ac2 := p_ac_balance_int_rec.ac2;
1429 l_balances_rec.ac3 := p_ac_balance_int_rec.ac3;
1430 l_balances_rec.ac4 := p_ac_balance_int_rec.ac4;
1431 l_balances_rec.ac5 := p_ac_balance_int_rec.ac5;
1432 l_balances_rec.period_name := l_future_open_periods.period_name;
1433 l_balances_rec.beginning_balance_dr := p_ac_balance_int_rec.init_balance_dr;
1434 l_balances_rec.beginning_balance_cr := p_ac_balance_int_rec.init_balance_cr;
1435 l_balances_rec.period_balance_dr := NULL;
1436 l_balances_rec.period_balance_cr := NULL;
1437 l_balances_rec.initial_balance_flag := 'N';
1438 l_balances_rec.period_year := NVL(l_period_year,get_period_year(l_future_open_periods.period_name));
1439 IF l_future_open_periods.period_num = 1 THEN
1440 l_balances_rec.first_period_flag := 'Y';
1441 ELSE
1442 l_balances_rec.first_period_flag := 'N';
1443 END IF;
1444 insert_balances_rec(l_balances_rec);
1445 FETCH c_future_open_periods INTO l_future_open_periods;
1446 END LOOP;
1447 ELSE
1448 /*
1449 * There are no future open periods. so insert new records for closed periods in the current year with zero period activity, only for
1450 * E Expense
1451 * R Revenue
1452 * C Budgetary (CR)
1453 * D Budgetary (DR)
1454 */
1455 IF l_account_type IN ('E','R','C','D') THEN
1456 FOR l_closed_periods IN c_closed_periods(p_ac_balance_int_rec.application_id,p_ac_balance_int_rec.ledger_id,p_ac_balance_int_rec.period_name,l_period_year) LOOP
1457 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1458 trace(p_module => l_log_module,p_msg => 'c_closed_periods fetched period:'||l_closed_periods.period_name,p_level => C_LEVEL_STATEMENT);
1459 END IF;
1460 l_balances_rec := NULL;
1461 l_balances_rec.application_id := p_ac_balance_int_rec.application_id;
1462 l_balances_rec.ledger_id := p_ac_balance_int_rec.ledger_id;
1463 l_balances_rec.code_combination_id := p_ac_balance_int_rec.code_combination_id;
1464 l_balances_rec.analytical_criterion_code := p_ac_balance_int_rec.analytical_criterion_code;
1465 l_balances_rec.analytical_criterion_type_code := p_ac_balance_int_rec.analytical_criterion_type_code;
1466 l_balances_rec.amb_context_code := p_ac_balance_int_rec.amb_context_code;
1467 l_balances_rec.ac1 := p_ac_balance_int_rec.ac1;
1468 l_balances_rec.ac2 := p_ac_balance_int_rec.ac2;
1469 l_balances_rec.ac3 := p_ac_balance_int_rec.ac3;
1470 l_balances_rec.ac4 := p_ac_balance_int_rec.ac4;
1471 l_balances_rec.ac5 := p_ac_balance_int_rec.ac5;
1472 l_balances_rec.period_name := l_closed_periods.period_name;
1473 l_balances_rec.beginning_balance_dr := p_ac_balance_int_rec.init_balance_dr;
1474 l_balances_rec.beginning_balance_cr := p_ac_balance_int_rec.init_balance_cr;
1475 l_balances_rec.period_balance_dr := NULL;
1476 l_balances_rec.period_balance_cr := NULL;
1477 l_balances_rec.initial_balance_flag := 'N';
1478 l_balances_rec.period_year := NVL(l_period_year,get_period_year(l_closed_periods.period_name));
1479 IF l_future_open_periods.period_num = 1 THEN
1480 l_balances_rec.first_period_flag := 'Y';
1481 ELSE
1482 l_balances_rec.first_period_flag := 'N';
1483 END IF;
1484 insert_balances_rec(l_balances_rec);
1485 END LOOP;
1486 END IF;
1487 END IF;
1488
1489 IF l_account_type IN ('E','R','C','D') AND l_sup_ref_hdr.year_end_carry_forward_code = 'B' THEN
1490 l_period_year := get_period_year(p_ac_balance_int_rec.period_name);
1491 l_prev_year := NULL;
1492 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1493 trace(p_module => l_log_module,p_msg => 'opening c_delete_records for year :'||l_period_year,p_level => C_LEVEL_STATEMENT);
1494 END IF;
1498 p_ac_balance_int_rec.analytical_criterion_code,
1495 FOR l_delete_records IN c_delete_records(p_ac_balance_int_rec.application_id,
1496 p_ac_balance_int_rec.ledger_id,
1497 p_ac_balance_int_rec.code_combination_id,
1499 p_ac_balance_int_rec.analytical_criterion_type_code,
1500 p_ac_balance_int_rec.amb_context_code,
1501 p_ac_balance_int_rec.ac1,
1502 p_ac_balance_int_rec.ac2,
1503 p_ac_balance_int_rec.ac3,
1504 p_ac_balance_int_rec.ac4,
1505 p_ac_balance_int_rec.ac5,
1506 l_period_year) LOOP
1507 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
1508 trace(p_module => l_log_module,p_msg => 'c_delete_records fetched period:'||l_delete_records.period_name,p_level => C_LEVEL_STATEMENT);
1509 END IF;
1510 IF c_delete_records%ROWCOUNT = 1 AND (l_delete_records.period_balance_dr IS NOT NULL OR l_delete_records.period_balance_cr IS NOT NULL) THEN
1511 EXIT;
1512 END IF;
1513
1514 IF c_delete_records%ROWCOUNT = 1 AND l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
1515 delete_balances_rec(l_delete_records);
1516 l_delete_cr_delta := l_delete_records.beginning_balance_cr;
1517 l_delete_dr_delta := l_delete_records.beginning_balance_dr;
1518 l_prev_year := l_delete_records.period_year;
1519 ELSE
1520 IF l_prev_year = l_delete_records.period_year THEN
1521 IF l_delete_records.period_balance_dr IS NULL AND l_delete_records.period_balance_cr IS NULL THEN
1522 delete_balances_rec(l_delete_records);
1523 ELSE
1524 l_delete_records.beginning_balance_cr := NVL(l_delete_records.beginning_balance_cr,0) + NVL(l_delete_cr_delta,0);
1525 l_delete_records.beginning_balance_dr := NVL(l_delete_records.beginning_balance_dr,0) + NVL(l_delete_dr_delta,0);
1526 update_balances_rec(l_delete_records);
1527 END IF;
1528 END IF;
1529 END IF;
1530 END LOOP;
1531 END IF;
1532
1533 END IF;
1534 END IF;
1535
1536
1537 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
1538 trace
1539 (p_module => l_log_module
1540 ,p_msg => 'END ' || l_log_module
1541 ,p_level => C_LEVEL_PROCEDURE);
1542 END IF;
1543
1544
1545 EXCEPTION
1546 WHEN CANT_DELETE_BALANCES THEN
1547 RAISE;
1548 WHEN xla_exceptions_pkg.application_exception THEN
1549 RAISE;
1550 WHEN OTHERS THEN
1551 xla_exceptions_pkg.raise_message
1552 (p_location => 'xla_ac_balances_pkg.merge_balances_rec');
1553 END merge_balances_rec;
1554
1555 FUNCTION validate_balances_rec(
1556 p_balances_int_rec IN OUT NOCOPY xla_ac_balances_int%ROWTYPE,
1557 p_message_codes OUT NOCOPY xla_ac_balances_int.message_codes%TYPE
1558 )
1559 RETURN BOOLEAN IS
1560 l_log_module VARCHAR2 (2000);
1561 l_result boolean :=true;
1562 l_rec xla_ac_balances_int%ROWTYPE := p_balances_int_rec;
1563 l_test_value NUMBER;
1564 l_error_codes xla_ac_balances_int.message_codes%TYPE;
1565 l_ledger_category_code gl_ledgers.ledger_category_code%TYPE;
1566 l_code_comb_id xla_ac_balances_int.code_combination_id%TYPE;
1567 l_coa_id gl_ledgers.chart_of_accounts_id%TYPE;
1568
1569 x_err_msg VARCHAR2(1000);
1570 x_ccid NUMBER := 0;
1571 x_templgrid NUMBER := 0;
1572 x_acct_type VARCHAR2(1);
1573 x_result boolean := true;
1574
1575 --=============================================================================
1576 --
1577 -- Cursor to validate application_id
1578 --
1579 --=============================================================================
1580 CURSOR c_is_valid_application(p_app_id xla_ac_balances_int.application_id%TYPE) IS
1581 select 1
1582 from xla_subledgers
1583 where application_id = p_app_id;
1584
1585 --=============================================================================
1586 --
1587 -- Cursor to validate ledger
1588 --
1589 --=============================================================================
1590 CURSOR c_is_valid_ledger(p_ledger_id xla_ac_balances_int.ledger_id%TYPE) IS
1591 select ledger_category_code
1592 from gl_ledgers
1593 where ledger_id = p_ledger_id;
1594
1595 --=============================================================================
1596 --
1597 -- Cursor to validate secondary or ALC ledger
1598 --
1599 --=============================================================================
1600 CURSOR c_is_valid_sec_ledger(p_ledger_id xla_ac_balances_int.ledger_id%TYPE) IS
1601 select 1
1602 from gl_ledger_relationships
1603 where primary_ledger_id = p_ledger_id
1604 and relationship_type_code='SUBLEDGER';
1605
1606 --=============================================================================
1607 --
1608 -- Cursor to fetch chart_of_accounts id for a given ledger
1609 --
1610 --=============================================================================
1611 CURSOR c_fetch_coa_id(p_ledger_id xla_ac_balances_int.ledger_id%TYPE) IS
1612 select chart_of_accounts_id
1613 from gl_ledgers
1617 --
1614 where ledger_id = p_ledger_id;
1615
1616 --=============================================================================
1618 -- Cursor to validate code combination id
1619 --
1620 --=============================================================================
1621 CURSOR c_is_valid_code_comb_id(
1622 p_ledger_id xla_ac_balances_int.ledger_id%TYPE
1623 ,p_code_comb_id gl_code_combinations.code_combination_id%TYPE) IS
1624 select 1
1625 from gl_ledgers lg,
1626 gl_code_combinations cc
1627 where lg.ledger_id = p_ledger_id
1628 and lg.chart_of_accounts_id = cc.chart_of_accounts_id
1629 and cc.code_combination_id = p_code_comb_id;
1630
1631 --=============================================================================
1632 --
1633 -- Cursor to validate populated code combination id
1634 --
1635 --=============================================================================
1636 CURSOR c_is_valid_pop_code_comb_id(p_rec xla_ac_balances_int%ROWTYPE) IS
1637 select gl.code_combination_id
1638 from gl_code_combinations gl
1639 where NVL(gl.segment1, 'X') = NVL(p_rec.segment1, 'X')
1640 and NVL(gl.segment2, 'X') = NVL(p_rec.segment2, 'X')
1641 and NVL(gl.segment3, 'X') = NVL(p_rec.segment3, 'X')
1642 and NVL(gl.segment4, 'X') = NVL(p_rec.segment4, 'X')
1643 and NVL(gl.segment5, 'X') = NVL(p_rec.segment5, 'X')
1644 and NVL(gl.segment6, 'X') = NVL(p_rec.segment6, 'X')
1645 and NVL(gl.segment7, 'X') = NVL(p_rec.segment7, 'X')
1646 and NVL(gl.segment8, 'X') = NVL(p_rec.segment8, 'X')
1647 and NVL(gl.segment9, 'X') = NVL(p_rec.segment9, 'X')
1648 and NVL(gl.segment10, 'X') = NVL(p_rec.segment10, 'X')
1649 and NVL(gl.segment11, 'X') = NVL(p_rec.segment11, 'X')
1650 and NVL(gl.segment12, 'X') = NVL(p_rec.segment12, 'X')
1651 and NVL(gl.segment13, 'X') = NVL(p_rec.segment13, 'X')
1652 and NVL(gl.segment14, 'X') = NVL(p_rec.segment14, 'X')
1653 and NVL(gl.segment15, 'X') = NVL(p_rec.segment15, 'X')
1654 and NVL(gl.segment16, 'X') = NVL(p_rec.segment16, 'X')
1655 and NVL(gl.segment17, 'X') = NVL(p_rec.segment17, 'X')
1656 and NVL(gl.segment18, 'X') = NVL(p_rec.segment18, 'X')
1657 and NVL(gl.segment19, 'X') = NVL(p_rec.segment19, 'X')
1658 and NVL(gl.segment20, 'X') = NVL(p_rec.segment20, 'X')
1659 and NVL(gl.segment21, 'X') = NVL(p_rec.segment21, 'X')
1660 and NVL(gl.segment22, 'X') = NVL(p_rec.segment22, 'X')
1661 and NVL(gl.segment23, 'X') = NVL(p_rec.segment23, 'X')
1662 and NVL(gl.segment24, 'X') = NVL(p_rec.segment24, 'X')
1663 and NVL(gl.segment25, 'X') = NVL(p_rec.segment25, 'X')
1664 and NVL(gl.segment26, 'X') = NVL(p_rec.segment26, 'X')
1665 and NVL(gl.segment27, 'X') = NVL(p_rec.segment27, 'X')
1666 and NVL(gl.segment28, 'X') = NVL(p_rec.segment28, 'X')
1667 and NVL(gl.segment29, 'X') = NVL(p_rec.segment29, 'X')
1668 and NVL(gl.segment30, 'X') = NVL(p_rec.segment30, 'X');
1669
1670
1671 --=============================================================================
1672 --
1673 -- Cursor to validate analytical criterion code
1674 --
1675 --=============================================================================
1676 CURSOR c_is_valid_anal_crit_code
1677 (p_anal_crit_code XLA_ANALYTICAL_HDRS_B.analytical_criterion_code%TYPE
1678 ,p_anal_crit_type_code XLA_ANALYTICAL_HDRS_B.analytical_criterion_type_code%TYPE
1679 ,p_amb_context_code XLA_ANALYTICAL_HDRS_B.amb_context_code%TYPE) IS
1680 SELECT 1
1681 FROM XLA_ANALYTICAL_HDRS_B
1682 WHERE analytical_criterion_code = p_anal_crit_code
1683 AND analytical_criterion_type_code = p_anal_crit_type_code
1684 AND amb_context_code = p_amb_context_code
1685 AND balancing_flag = 'Y'
1686 AND enabled_flag = 'Y';
1687
1688 --=============================================================================
1689 --
1690 -- Cursor to validate period
1691 --
1692 --=============================================================================
1693 CURSOR c_is_valid_period
1694 (p_app_id xla_ac_balances_int.application_id%TYPE
1695 ,p_ledger_id xla_ac_balances_int.ledger_id%TYPE
1696 ,p_period_name gl_period_statuses.period_name%TYPE) IS
1697 select 1
1698 from gl_period_statuses
1699 where application_id =101 --bug 11811413
1700 and ledger_id = p_ledger_id
1701 and period_name=p_period_name
1702 AND closing_status IN ('O','C');
1703
1704 --=============================================================================
1705 --
1706 -- Cursor to validate prior journal extries exits or not for the given period name
1707 --
1708 --=============================================================================
1709 CURSOR c_is_prior_je_exists(p_rec xla_ac_balances_int%ROWTYPE) IS
1710 select 1
1711 from xla_ac_balances
1712 where ledger_id = p_rec.ledger_id
1713 and code_combination_id = p_rec.code_combination_id
1714 and analytical_criterion_code = p_rec.analytical_criterion_code
1715 and analytical_criterion_type_code = p_rec.analytical_criterion_type_code
1716 and amb_context_code = p_rec.amb_context_code
1717 AND NVL(ac1,'*') = NVL(p_rec.ac1,'*')
1718 AND NVL(ac2,'*') = NVL(p_rec.ac2,'*')
1719 AND NVL(ac3,'*') = NVL(p_rec.ac3,'*')
1720 AND NVL(ac4,'*') = NVL(p_rec.ac4,'*')
1721 AND NVL(ac5,'*') = NVL(p_rec.ac5,'*')
1722 and period_name in (
1723 select per.period_name
1724 from gl_periods per,
1725 gl_ledgers led,
1726 gl_periods ref_per
1727 where per.adjustment_period_flag = 'N'
1728 and led.accounted_period_type = per.period_type
1729 and led.period_set_name = per.period_set_name
1730 and led.ledger_id = p_rec.ledger_id
1731 and per.start_date < ref_per.start_date
1732 and ref_per.period_name = p_rec.period_name
1736
1733 and ref_per.period_type = per.period_type
1734 and ref_per.period_set_name = per.period_set_name
1735 );
1737
1738 BEGIN
1739 l_error_codes := '';
1740 -- validate application id
1741 OPEN c_is_valid_application(l_rec.application_id);
1742 FETCH c_is_valid_application INTO l_test_value;
1743
1744 IF(c_is_valid_application%NOTFOUND) THEN
1745 l_error_codes := 'IB001,';
1746 l_result := false;
1747 END IF;
1748
1749 CLOSE c_is_valid_application;
1750
1751 -- validate ledger
1752 OPEN c_is_valid_ledger(l_rec.ledger_id);
1753 FETCH c_is_valid_ledger INTO l_ledger_category_code;
1754
1755 IF (c_is_valid_ledger%FOUND) THEN
1756 IF (l_ledger_category_code <> 'PRIMARY') THEN
1757 OPEN c_is_valid_sec_ledger(l_rec.ledger_id);
1758
1759 FETCH c_is_valid_sec_ledger INTO l_test_value;
1760
1761 IF (c_is_valid_sec_ledger%NOTFOUND) THEN
1762 l_error_codes := l_error_codes || 'IB004,';
1763 l_result := false;
1764 END IF;
1765 CLOSE c_is_valid_sec_ledger;
1766 END IF;
1767 ELSE
1768 l_error_codes := l_error_codes || 'IB003,';
1769 l_result := false;
1770 END IF;
1771
1772 CLOSE c_is_valid_ledger;
1773
1774 IF (l_rec.code_combination_id IS NOT NULL) THEN
1775 -- validate code combination id
1776
1777 OPEN c_fetch_coa_id(l_rec.ledger_id);
1778 FETCH c_fetch_coa_id INTO l_coa_id;
1779 CLOSE c_fetch_coa_id;
1780
1781 IF NOT FND_FLEX_KEYVAL.validate_ccid
1782 (
1783 appl_short_name => 'SQLGL'
1784 ,key_flex_code => 'GL#'
1785 ,structure_number => l_coa_id
1786 ,combination_id => l_rec.code_combination_id
1787 ,displayable => 'ALL'
1788 ,data_set => NULL
1789 ,vrule => NULL
1790 ,security => 'IGNORE'
1791 ,get_columns => NULL
1792 ,resp_appl_id => NULL
1793 ,resp_id => NULL
1794 ,user_id => NULL
1795 ,select_comb_from_view => NULL
1796 )
1797 THEN
1798 l_error_codes := l_error_codes || 'IB005,';
1799 l_result := false;
1800 END IF;
1801
1802 ELSE
1803 -- populate code combination id and validate it
1804 OPEN c_fetch_coa_id(l_rec.ledger_id);
1805 FETCH c_fetch_coa_id INTO l_coa_id;
1806 CLOSE c_fetch_coa_id;
1807
1808 x_result := GL_RECURRING_RULES_PKG.get_ccid
1809 (
1810 l_rec.ledger_id
1811 ,l_coa_id
1812 ,NULL -- concat segs
1813 ,x_err_msg
1814 ,x_ccid
1815 ,x_templgrid
1816 ,x_acct_type
1817 ,l_rec.segment1
1818 ,l_rec.segment2
1819 ,l_rec.segment3
1820 ,l_rec.segment4
1821 ,l_rec.segment5
1822 ,l_rec.segment6
1823 ,l_rec.segment7
1824 ,l_rec.segment8
1825 ,l_rec.segment9
1826 ,l_rec.segment10
1827 ,l_rec.segment11
1828 ,l_rec.segment12
1829 ,l_rec.segment13
1830 ,l_rec.segment14
1831 ,l_rec.segment15
1832 ,l_rec.segment16
1833 ,l_rec.segment17
1834 ,l_rec.segment18
1835 ,l_rec.segment19
1836 ,l_rec.segment20
1837 ,l_rec.segment21
1838 ,l_rec.segment22
1839 ,l_rec.segment23
1840 ,l_rec.segment24
1841 ,l_rec.segment25
1842 ,l_rec.segment26
1843 ,l_rec.segment27
1844 ,l_rec.segment28
1845 ,l_rec.segment29
1846 ,l_rec.segment30
1847 );
1848
1849 IF (x_result = true AND x_ccid IS NOT NULL) THEN
1850 IF NOT FND_FLEX_KEYVAL.validate_ccid
1851 (
1852 appl_short_name => 'SQLGL'
1853 ,key_flex_code => 'GL#'
1854 ,structure_number => l_coa_id
1855 ,combination_id => x_ccid
1856 ,displayable => 'ALL'
1857 ,data_set => NULL
1858 ,vrule => NULL
1859 ,security => 'IGNORE'
1860 ,get_columns => NULL
1861 ,resp_appl_id => NULL
1862 ,resp_id => NULL
1863 ,user_id => NULL
1864 ,select_comb_from_view => NULL
1865 )
1866 THEN
1867 l_error_codes := l_error_codes || 'IB006,';
1868 l_result := false;
1869 ELSE
1870 l_rec.code_combination_id := x_ccid;
1871 END IF;
1872 ELSE
1873 l_error_codes := l_error_codes || 'IB005,';
1874 l_result := false;
1875 END IF;
1876
1877
1878 END IF;
1879
1880 -- valiadate analytical criterions
1881 OPEN c_is_valid_anal_crit_code
1882 (l_rec.analytical_criterion_code
1883 ,l_rec.analytical_criterion_type_code
1884 ,l_rec.amb_context_code);
1885 FETCH c_is_valid_anal_crit_code INTO l_test_value;
1886
1887 IF (c_is_valid_anal_crit_code%NOTFOUND) THEN
1888 l_error_codes := l_error_codes || 'IB016,';
1889 l_result := false;
1890 END IF;
1891
1892 CLOSE c_is_valid_anal_crit_code;
1893
1897 l_result := false;
1894 -- validate all AC rows 1-5 cannot be null
1895 IF (l_rec.AC1 IS NULL AND l_rec.AC2 IS NULL AND l_rec.AC3 IS NULL AND l_rec.AC4 IS NULL AND l_rec.AC5 IS NULL) THEN
1896 l_error_codes := l_error_codes || 'IB017,';
1898 END IF;
1899
1900 -- validate period
1901 OPEN c_is_valid_period(l_rec.application_id, l_rec.ledger_id, l_rec.period_name);
1902 FETCH c_is_valid_period INTO l_test_value;
1903
1904 IF(c_is_valid_period%NOTFOUND) THEN
1905 l_error_codes := l_error_codes || 'IB011,';
1906 l_result := false;
1907 END IF;
1908
1909 CLOSE c_is_valid_period;
1910
1911 -- validate prior journal entries for a given period_name
1912 OPEN c_is_prior_je_exists(l_rec);
1913 FETCH c_is_prior_je_exists INTO l_test_value;
1914
1915 IF (c_is_prior_je_exists%FOUND) THEN
1916 l_error_codes := l_error_codes || 'IB012,';
1917 l_result := false;
1918 END IF;
1919
1920 CLOSE c_is_prior_je_exists;
1921
1922 --validate balances
1923 IF (l_rec.init_balance_dr IS NULL AND l_rec.init_balance_cr IS NULL ) THEN
1924 l_error_codes := l_error_codes || 'IB013,';
1925 l_result := false;
1926 END IF;
1927
1928 IF (l_rec.init_balance_dr < 0 OR l_rec.init_balance_cr < 0) THEN
1929 l_error_codes := l_error_codes || 'IB014,';
1930 l_result := false;
1931 END IF;
1932
1933 /*
1934 * Remove the last character if it is a comma
1935 */
1936 IF INSTR(l_error_codes,',',-1) = LENGTH(l_error_codes) THEN
1937 l_error_codes := SUBSTR(l_error_codes,1,LENGTH(l_error_codes)-1);
1938 END IF;
1939 p_message_codes := l_error_codes;
1940 p_balances_int_rec := l_rec;
1941
1942 RETURN l_result;
1943
1944 EXCEPTION
1945 WHEN xla_exceptions_pkg.application_exception THEN
1946 RAISE;
1947 WHEN OTHERS THEN
1948 xla_exceptions_pkg.raise_message
1949 (p_location => 'xla_ac_balances_pkg.validate_balances_rec');
1950 END validate_balances_rec;
1951
1952 PROCEDURE purge_interface_recs(
1953 p_batch_code VARCHAR2,
1954 p_purge_mode VARCHAR2
1955 ) AS
1956 ------------------------------------------------------------------
1957 --Created by : veramach, Oracle India
1958 --Date created:
1959 --
1960 --Purpose:
1961 --
1962 --
1963 --Known limitations/enhancements and/or remarks:
1964 --
1965 --Change History:
1966 --Who When What
1967 -------------------------------------------------------------------
1968
1969 BEGIN
1970
1971 /*
1972 * Delete records based on p_purge_mode
1973 * If p_purge_mode = A, then delete all records for the p_batch_code passed
1974 * If p_purge_mode = S, then delete all records that were imported in this run
1975 * If p_purge_mode = N, then do not delete anything
1976 */
1977 DELETE xla_ac_balances_int xib
1978 WHERE ( ( p_batch_code IS NOT NULL
1979 AND p_batch_code = xib.batch_code)
1980 OR ( p_batch_code IS NULL
1981 AND 1 = 1))
1982 AND ( ( p_purge_mode = 'N'
1983 AND 1 = 2)
1984 OR ( p_purge_mode = 'S'
1985 AND xib.status = 'IMPORTED')
1986 OR ( p_purge_mode = 'A'
1987 AND xib.status IN('IMPORTED', 'ERROR')));
1988
1989 EXCEPTION
1990 WHEN xla_exceptions_pkg.application_exception THEN
1991 RAISE;
1992 WHEN OTHERS THEN
1993 xla_exceptions_pkg.raise_message
1994 (p_location => 'xla_ac_balances_pkg.purge_interface_recs');
1995 END purge_interface_recs;
1996
1997 PROCEDURE update_balances
1998 ( p_errbuf OUT NOCOPY VARCHAR2
1999 ,p_retcode OUT NOCOPY NUMBER
2000 ,p_batch_code IN VARCHAR2
2001 ,p_purge_mode IN VARCHAR2
2002 )
2003 IS
2004 BEGIN
2005 update_balances(p_batch_code,p_purge_mode);
2006
2007 EXCEPTION
2008 WHEN xla_exceptions_pkg.application_exception THEN
2009 p_retcode := 2;
2010 p_errbuf := sqlerrm;
2011 WHEN OTHERS THEN
2012 p_retcode := 2;
2013 p_errbuf := sqlerrm;
2014 END update_balances;
2015
2016
2017 PROCEDURE update_balances
2018 ( p_batch_code IN VARCHAR2
2019 ,p_purge_mode IN VARCHAR2
2020 )
2021 IS
2022 /*======================================================================+
2023 | |
2024 | Public Function |
2025 | |
2026 | Description |
2027 | ----------- |
2028 | Just the SRS wrapper |
2029 | |
2030 | Pseudo-code |
2031 | ----------- |
2032 | Call update_balances and assign its return code to |
2033 | p_retcode |
2034 | RETURN p_retcode (0=success, 1=warning, 2=error) |
2035 | |
2039 | |
2036 | Open issues |
2037 | ----------- |
2038 | |
2040 | |
2041 | |
2042 +======================================================================*/
2043
2044 l_commit_flag VARCHAR2(1);
2045
2046 l_log_module VARCHAR2 (2000);
2047 l_message_codes xla_ac_balances_int.message_codes%TYPE;
2048
2049 -- Get interface records
2050 CURSOR c_balances_int(
2051 cp_batch_code VARCHAR2
2052 ) IS
2053 SELECT xib.*
2054 FROM xla_ac_balances_int xib
2055 WHERE ( xib.status IS NULL
2056 OR xib.status = 'ERROR')
2057 AND (xib.batch_code = NVL(cp_batch_code, xib.batch_code))
2058 ORDER BY batch_code DESC
2059 FOR UPDATE OF status NOWAIT;
2060
2061 l_balances_int_rec xla_ac_balances_int%ROWTYPE;
2062 l_sql_err VARCHAR2(1000);
2063
2064 l_success_rec NUMBER := 0;
2065 l_error_rec NUMBER := 0;
2066 BEGIN
2067
2068 fnd_file.put_line(fnd_file.log,'------------Parameters-----------');
2069 fnd_file.put_line(fnd_file.log,'Batch Code : '||p_batch_code);
2070 fnd_file.put_line(fnd_file.log,'Purge Mode : '||p_purge_mode);
2071 fnd_file.put_line(fnd_file.log,'---------------------------------');
2072
2073 IF g_log_enabled THEN
2074 l_log_module := C_DEFAULT_MODULE||'.update_balances';
2075 END IF;
2076 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2077 trace
2078 (p_module => l_log_module
2079 ,p_msg => 'BEGIN ' || l_log_module
2080 ,p_level => C_LEVEL_PROCEDURE);
2081 END IF;
2082
2083 FOR l_balances_int_rec IN c_balances_int(p_batch_code) LOOP
2084 BEGIN
2085 fnd_file.new_line(fnd_file.log,2);
2086 fnd_file.put_line(fnd_file.log,'Processing record with:');
2087 fnd_file.put_line(fnd_file.log,'Supporting Reference Name : '||l_balances_int_rec.analytical_criterion_code);
2088 fnd_file.put_line(fnd_file.log,'Supporting Reference Type : '||l_balances_int_rec.analytical_criterion_type_code);
2089 fnd_file.put_line(fnd_file.log,'Supporting Reference 1 : '||l_balances_int_rec.ac1);
2090 fnd_file.put_line(fnd_file.log,'Supporting Reference 2 : '||l_balances_int_rec.ac2);
2091 fnd_file.put_line(fnd_file.log,'Supporting Reference 3 : '||l_balances_int_rec.ac3);
2092 fnd_file.put_line(fnd_file.log,'Supporting Reference 4 : '||l_balances_int_rec.ac4);
2093 fnd_file.put_line(fnd_file.log,'Supporting Reference 5 : '||l_balances_int_rec.ac5);
2094 fnd_file.put_line(fnd_file.log,'Period : '||l_balances_int_rec.period_name);
2095
2096 IF NOT validate_balances_rec(l_balances_int_rec,l_message_codes) THEN
2097 /*
2098 * Some validations failed
2099 */
2100 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2101 trace
2102 (p_module => l_log_module
2103 ,p_msg => 'validations failed with l_message_codes:'||l_message_codes
2104 ,p_level => C_LEVEL_STATEMENT);
2105 END IF;
2106 fnd_file.put_line(fnd_file.log,'Import Failed with error codes: '||l_message_codes);
2107 fnd_file.new_line(fnd_file.log,2);
2108 UPDATE xla_ac_balances_int
2109 SET status = 'ERROR',
2110 message_codes = l_message_codes,
2111 last_updated_by = g_user_id,
2112 last_update_date = g_date,
2113 last_update_login = g_login_id
2114 WHERE CURRENT OF c_balances_int;
2115 l_error_rec := l_error_rec + 1;
2116 ELSE
2117 /*
2118 * All validations passed
2119 */
2120
2121 IF (C_LEVEL_STATEMENT >= g_log_level) THEN
2122 trace
2123 (p_module => l_log_module
2124 ,p_msg => 'validations succeeded'
2125 ,p_level => C_LEVEL_STATEMENT);
2126 END IF;
2127 merge_balances_rec(l_balances_int_rec);
2128 /*
2129 * Successfully merged the records. Update the status of the interface record
2130 */
2131 fnd_file.put_line(fnd_file.log,'Import Succeeded');
2132 fnd_file.new_line(fnd_file.log,2);
2133 UPDATE xla_ac_balances_int
2134 SET status = 'IMPORTED',
2135 message_codes = NULL,
2136 last_updated_by = g_user_id,
2137 last_update_date = g_date,
2138 last_update_login = g_login_id
2139 WHERE CURRENT OF c_balances_int;
2140 l_success_rec := l_success_rec + 1;
2141 END IF;
2142 EXCEPTION
2143 WHEN CANT_DELETE_BALANCES THEN
2144 UPDATE xla_ac_balances_int
2145 SET status = 'ERROR',
2146 message_codes = 'IB018',
2147 last_updated_by = g_user_id,
2148 last_update_date = g_date,
2149 last_update_login = g_login_id
2150 WHERE CURRENT OF c_balances_int;
2151 l_error_rec := l_error_rec + 1;
2152 WHEN OTHERS THEN
2153 l_sql_err := SQLERRM;
2154 UPDATE xla_ac_balances_int
2155 SET status = 'ERROR',
2156 message_codes = l_sql_err,
2157 last_updated_by = g_user_id,
2158 last_update_date = g_date,
2159 last_update_login = g_login_id
2160 WHERE CURRENT OF c_balances_int;
2161 l_error_rec := l_error_rec + 1;
2162 END;
2163 END LOOP;
2164
2165 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
2166 trace
2167 (p_module => l_log_module
2168 ,p_msg => 'END ' || l_log_module
2169 ,p_level => C_LEVEL_PROCEDURE);
2170 END IF;
2171
2172 EXCEPTION
2173 WHEN xla_exceptions_pkg.application_exception THEN
2174 RAISE;
2175 WHEN OTHERS THEN
2176 xla_exceptions_pkg.raise_message
2177 (p_location => 'xla_ac_balances_pkg.update_balances');
2178 END update_balances;
2179
2180 BEGIN
2181 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2182 g_log_enabled := fnd_log.test
2183 (log_level => g_log_level
2184 ,module => C_DEFAULT_MODULE);
2185
2186 IF NOT g_log_enabled THEN
2187 g_log_level := C_LEVEL_LOG_DISABLED;
2188 END IF;
2189
2190 g_user_id := xla_environment_pkg.g_usr_id;
2191 g_login_id := xla_environment_pkg.g_login_id;
2192 g_date := SYSDATE;
2193 g_prog_appl_id := xla_environment_pkg.g_prog_appl_id;
2194 g_prog_id := xla_environment_pkg.g_prog_id;
2195 g_req_id := NVL(xla_environment_pkg.g_req_id, -1);
2196
2197 END xla_ac_balances_pkg;