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