[Home] [Help]
1: PACKAGE BODY xla_ac_balances_pkg AS
2: /* $Header: xlaacbal.pkb 120.4 2011/03/04 13:20:15 karamakr ship $ */
3: /*======================================================================+
4: | Copyright (c) 1995-2002 Oracle Corporation |
5: | Redwood Shores, CA, USA |
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: | |
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:
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:
133: cp_period_name gl_period_statuses.period_name%TYPE
134: ) IS
135: SELECT gps.period_year
136: FROM gl_period_statuses gps,
137: xla_ac_balances_int bal
138: WHERE gps.ledger_id = bal.ledger_id
139: -- AND gps.application_id = bal.application_id
140: AND gps.application_id = 101 --bug 11811413
141: AND gps.adjustment_period_flag = 'N'
150: RETURN l_period_year;
151: END get_period_year;
152:
153: PROCEDURE insert_balances_rec(
154: p_ac_balance_int_rec xla_ac_balances%ROWTYPE
155: ) IS
156: l_log_module VARCHAR2(240);
157: l_period_year gl_period_statuses.period_year%TYPE;
158: l_row_count NUMBER;
169: END IF;
170:
171: l_period_year := get_period_year(p_ac_balance_int_rec.period_name);
172:
173: INSERT INTO xla_ac_balances
174: (application_id,
175: ledger_id,
176: code_combination_id,
177: analytical_criterion_code,
241: WHEN xla_exceptions_pkg.application_exception THEN
242: RAISE;
243: WHEN OTHERS THEN
244: xla_exceptions_pkg.raise_message
245: (p_location => 'xla_ac_balances_pkg.insert_balances_rec');
246: END insert_balances_rec;
247:
248: PROCEDURE update_balances_rec(
249: p_ac_balance_int_rec xla_ac_balances%ROWTYPE
245: (p_location => 'xla_ac_balances_pkg.insert_balances_rec');
246: END insert_balances_rec;
247:
248: PROCEDURE update_balances_rec(
249: p_ac_balance_int_rec xla_ac_balances%ROWTYPE
250: ) IS
251: l_log_module VARCHAR2(240);
252: l_period_year gl_period_statuses.period_year%TYPE;
253: l_row_count NUMBER;
262: p_level => c_level_procedure
263: );
264: END IF;
265:
266: UPDATE xla_ac_balances
267: SET period_name = p_ac_balance_int_rec.period_name,
268: beginning_balance_dr = p_ac_balance_int_rec.beginning_balance_dr,
269: beginning_balance_cr = p_ac_balance_int_rec.beginning_balance_cr,
270: initial_balance_flag = p_ac_balance_int_rec.initial_balance_flag,
301: WHEN xla_exceptions_pkg.application_exception THEN
302: RAISE;
303: WHEN OTHERS THEN
304: xla_exceptions_pkg.raise_message
305: (p_location => 'xla_ac_balances_pkg.update_balances_rec');
306: END update_balances_rec;
307:
308: PROCEDURE delete_balances_rec(
309: p_ac_balance_int_rec xla_ac_balances%ROWTYPE
305: (p_location => 'xla_ac_balances_pkg.update_balances_rec');
306: END update_balances_rec;
307:
308: PROCEDURE delete_balances_rec(
309: p_ac_balance_int_rec xla_ac_balances%ROWTYPE
310: ) IS
311: l_log_module VARCHAR2(240);
312: l_period_year gl_period_statuses.period_year%TYPE;
313: l_row_count NUMBER;
322: p_level => c_level_procedure
323: );
324: END IF;
325:
326: DELETE xla_ac_balances xab
327: WHERE xab.application_id = p_ac_balance_int_rec.application_id
328: AND xab.ledger_id = p_ac_balance_int_rec.ledger_id
329: AND xab.code_combination_id = p_ac_balance_int_rec.code_combination_id
330: AND xab.analytical_criterion_code = p_ac_balance_int_rec.analytical_criterion_code
350: WHEN xla_exceptions_pkg.application_exception THEN
351: RAISE;
352: WHEN OTHERS THEN
353: xla_exceptions_pkg.raise_message
354: (p_location => 'xla_ac_balances_pkg.delete_balances_rec');
355: END delete_balances_rec;
356:
357: PROCEDURE merge_balances_rec
358: ( p_ac_balance_int_rec IN xla_ac_balances_int%ROWTYPE
354: (p_location => 'xla_ac_balances_pkg.delete_balances_rec');
355: END delete_balances_rec;
356:
357: PROCEDURE merge_balances_rec
358: ( p_ac_balance_int_rec IN xla_ac_balances_int%ROWTYPE
359: )
360: IS
361:
362: l_log_module VARCHAR2 (2000);
362: l_log_module VARCHAR2 (2000);
363:
364: -- Get existing balance
365: CURSOR c_exist_balance(
366: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
367: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
368: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
369: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
370: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
363:
364: -- Get existing balance
365: CURSOR c_exist_balance(
366: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
367: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
368: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
369: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
370: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
371: cp_ac1 xla_ac_balances.ac1%TYPE,
364: -- Get existing balance
365: CURSOR c_exist_balance(
366: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
367: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
368: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
369: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
370: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
371: cp_ac1 xla_ac_balances.ac1%TYPE,
372: cp_ac2 xla_ac_balances.ac2%TYPE,
365: CURSOR c_exist_balance(
366: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
367: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
368: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
369: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
370: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
371: cp_ac1 xla_ac_balances.ac1%TYPE,
372: cp_ac2 xla_ac_balances.ac2%TYPE,
373: cp_ac3 xla_ac_balances.ac3%TYPE,
366: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
367: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
368: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
369: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
370: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
371: cp_ac1 xla_ac_balances.ac1%TYPE,
372: cp_ac2 xla_ac_balances.ac2%TYPE,
373: cp_ac3 xla_ac_balances.ac3%TYPE,
374: cp_ac4 xla_ac_balances.ac4%TYPE,
367: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
368: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
369: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
370: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
371: cp_ac1 xla_ac_balances.ac1%TYPE,
372: cp_ac2 xla_ac_balances.ac2%TYPE,
373: cp_ac3 xla_ac_balances.ac3%TYPE,
374: cp_ac4 xla_ac_balances.ac4%TYPE,
375: cp_ac5 xla_ac_balances.ac5%TYPE,
368: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
369: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
370: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
371: cp_ac1 xla_ac_balances.ac1%TYPE,
372: cp_ac2 xla_ac_balances.ac2%TYPE,
373: cp_ac3 xla_ac_balances.ac3%TYPE,
374: cp_ac4 xla_ac_balances.ac4%TYPE,
375: cp_ac5 xla_ac_balances.ac5%TYPE,
376: cp_period_name xla_ac_balances.period_name%TYPE
369: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
370: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
371: cp_ac1 xla_ac_balances.ac1%TYPE,
372: cp_ac2 xla_ac_balances.ac2%TYPE,
373: cp_ac3 xla_ac_balances.ac3%TYPE,
374: cp_ac4 xla_ac_balances.ac4%TYPE,
375: cp_ac5 xla_ac_balances.ac5%TYPE,
376: cp_period_name xla_ac_balances.period_name%TYPE
377: ) IS
370: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
371: cp_ac1 xla_ac_balances.ac1%TYPE,
372: cp_ac2 xla_ac_balances.ac2%TYPE,
373: cp_ac3 xla_ac_balances.ac3%TYPE,
374: cp_ac4 xla_ac_balances.ac4%TYPE,
375: cp_ac5 xla_ac_balances.ac5%TYPE,
376: cp_period_name xla_ac_balances.period_name%TYPE
377: ) IS
378: SELECT xab.*
371: cp_ac1 xla_ac_balances.ac1%TYPE,
372: cp_ac2 xla_ac_balances.ac2%TYPE,
373: cp_ac3 xla_ac_balances.ac3%TYPE,
374: cp_ac4 xla_ac_balances.ac4%TYPE,
375: cp_ac5 xla_ac_balances.ac5%TYPE,
376: cp_period_name xla_ac_balances.period_name%TYPE
377: ) IS
378: SELECT xab.*
379: FROM xla_ac_balances xab
372: cp_ac2 xla_ac_balances.ac2%TYPE,
373: cp_ac3 xla_ac_balances.ac3%TYPE,
374: cp_ac4 xla_ac_balances.ac4%TYPE,
375: cp_ac5 xla_ac_balances.ac5%TYPE,
376: cp_period_name xla_ac_balances.period_name%TYPE
377: ) IS
378: SELECT xab.*
379: FROM xla_ac_balances xab
380: WHERE xab.ledger_id = cp_ledger_id
375: cp_ac5 xla_ac_balances.ac5%TYPE,
376: cp_period_name xla_ac_balances.period_name%TYPE
377: ) IS
378: SELECT xab.*
379: FROM xla_ac_balances xab
380: WHERE xab.ledger_id = cp_ledger_id
381: AND xab.code_combination_id = cp_code_combination_id
382: AND xab.analytical_criterion_code = cp_analytical_criterion_code
383: AND xab.analytical_criterion_type_code = cp_criterion_type_code
387: AND NVL(xab.ac2,'*') = NVL(cp_ac2,'*')
388: AND NVL(xab.ac3,'*') = NVL(cp_ac3,'*')
389: AND NVL(xab.ac4,'*') = NVL(cp_ac4,'*')
390: AND NVL(xab.ac5,'*') = NVL(cp_ac5,'*');
391: l_exist_balance xla_ac_balances%ROWTYPE;
392:
393: l_balances_rec xla_ac_balances%ROWTYPE;
394:
395: -- Get subsequent periods
389: AND NVL(xab.ac4,'*') = NVL(cp_ac4,'*')
390: AND NVL(xab.ac5,'*') = NVL(cp_ac5,'*');
391: l_exist_balance xla_ac_balances%ROWTYPE;
392:
393: l_balances_rec xla_ac_balances%ROWTYPE;
394:
395: -- Get subsequent periods
396: CURSOR c_subsequent_periods(
397: cp_application_id xla_ac_balances.application_id%TYPE,
393: l_balances_rec xla_ac_balances%ROWTYPE;
394:
395: -- Get subsequent periods
396: CURSOR c_subsequent_periods(
397: cp_application_id xla_ac_balances.application_id%TYPE,
398: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
399: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
400: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
401: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
394:
395: -- Get subsequent periods
396: CURSOR c_subsequent_periods(
397: cp_application_id xla_ac_balances.application_id%TYPE,
398: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
399: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
400: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
401: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
402: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
395: -- Get subsequent periods
396: CURSOR c_subsequent_periods(
397: cp_application_id xla_ac_balances.application_id%TYPE,
398: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
399: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
400: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
401: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
402: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
403: cp_ac1 xla_ac_balances.ac1%TYPE,
396: CURSOR c_subsequent_periods(
397: cp_application_id xla_ac_balances.application_id%TYPE,
398: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
399: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
400: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
401: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
402: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
403: cp_ac1 xla_ac_balances.ac1%TYPE,
404: cp_ac2 xla_ac_balances.ac2%TYPE,
397: cp_application_id xla_ac_balances.application_id%TYPE,
398: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
399: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
400: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
401: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
402: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
403: cp_ac1 xla_ac_balances.ac1%TYPE,
404: cp_ac2 xla_ac_balances.ac2%TYPE,
405: cp_ac3 xla_ac_balances.ac3%TYPE,
398: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
399: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
400: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
401: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
402: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
403: cp_ac1 xla_ac_balances.ac1%TYPE,
404: cp_ac2 xla_ac_balances.ac2%TYPE,
405: cp_ac3 xla_ac_balances.ac3%TYPE,
406: cp_ac4 xla_ac_balances.ac4%TYPE,
399: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
400: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
401: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
402: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
403: cp_ac1 xla_ac_balances.ac1%TYPE,
404: cp_ac2 xla_ac_balances.ac2%TYPE,
405: cp_ac3 xla_ac_balances.ac3%TYPE,
406: cp_ac4 xla_ac_balances.ac4%TYPE,
407: cp_ac5 xla_ac_balances.ac5%TYPE,
400: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
401: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
402: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
403: cp_ac1 xla_ac_balances.ac1%TYPE,
404: cp_ac2 xla_ac_balances.ac2%TYPE,
405: cp_ac3 xla_ac_balances.ac3%TYPE,
406: cp_ac4 xla_ac_balances.ac4%TYPE,
407: cp_ac5 xla_ac_balances.ac5%TYPE,
408: cp_period_year xla_ac_balances.period_year%TYPE,
401: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
402: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
403: cp_ac1 xla_ac_balances.ac1%TYPE,
404: cp_ac2 xla_ac_balances.ac2%TYPE,
405: cp_ac3 xla_ac_balances.ac3%TYPE,
406: cp_ac4 xla_ac_balances.ac4%TYPE,
407: cp_ac5 xla_ac_balances.ac5%TYPE,
408: cp_period_year xla_ac_balances.period_year%TYPE,
409: cp_period_name xla_ac_balances.period_name%TYPE
402: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
403: cp_ac1 xla_ac_balances.ac1%TYPE,
404: cp_ac2 xla_ac_balances.ac2%TYPE,
405: cp_ac3 xla_ac_balances.ac3%TYPE,
406: cp_ac4 xla_ac_balances.ac4%TYPE,
407: cp_ac5 xla_ac_balances.ac5%TYPE,
408: cp_period_year xla_ac_balances.period_year%TYPE,
409: cp_period_name xla_ac_balances.period_name%TYPE
410: ) IS
403: cp_ac1 xla_ac_balances.ac1%TYPE,
404: cp_ac2 xla_ac_balances.ac2%TYPE,
405: cp_ac3 xla_ac_balances.ac3%TYPE,
406: cp_ac4 xla_ac_balances.ac4%TYPE,
407: cp_ac5 xla_ac_balances.ac5%TYPE,
408: cp_period_year xla_ac_balances.period_year%TYPE,
409: cp_period_name xla_ac_balances.period_name%TYPE
410: ) IS
411: SELECT xab.*
404: cp_ac2 xla_ac_balances.ac2%TYPE,
405: cp_ac3 xla_ac_balances.ac3%TYPE,
406: cp_ac4 xla_ac_balances.ac4%TYPE,
407: cp_ac5 xla_ac_balances.ac5%TYPE,
408: cp_period_year xla_ac_balances.period_year%TYPE,
409: cp_period_name xla_ac_balances.period_name%TYPE
410: ) IS
411: SELECT xab.*
412: FROM xla_ac_balances xab,
405: cp_ac3 xla_ac_balances.ac3%TYPE,
406: cp_ac4 xla_ac_balances.ac4%TYPE,
407: cp_ac5 xla_ac_balances.ac5%TYPE,
408: cp_period_year xla_ac_balances.period_year%TYPE,
409: cp_period_name xla_ac_balances.period_name%TYPE
410: ) IS
411: SELECT xab.*
412: FROM xla_ac_balances xab,
413: gl_ledgers ledger,
408: cp_period_year xla_ac_balances.period_year%TYPE,
409: cp_period_name xla_ac_balances.period_name%TYPE
410: ) IS
411: SELECT xab.*
412: FROM xla_ac_balances xab,
413: gl_ledgers ledger,
414: gl_periods fut_periods,
415: gl_period_types period_types,
416: gl_period_statuses fut_period_statuses,
444: AND NVL(xab.ac5,'*') = NVL(cp_ac5,'*')
445: AND xab.period_year = NVL(cp_period_year,xab.period_year)
446: AND xab.period_name <> cp_period_name
447: ORDER BY fut_periods.start_date;
448: l_subsequent_periods xla_ac_balances%ROWTYPE;
449:
450: l_delta_cr NUMBER := NULL;
451: l_delta_dr NUMBER := NULL;
452:
451: l_delta_dr NUMBER := NULL;
452:
453: -- Get supporting referehnce header
454: CURSOR c_sup_ref_hdr (
455: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
456: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
457: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE
458: ) IS
459: SELECT NVL(xah.balancing_flag,'N') balancing_flag,
452:
453: -- Get supporting referehnce header
454: CURSOR c_sup_ref_hdr (
455: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
456: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
457: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE
458: ) IS
459: SELECT NVL(xah.balancing_flag,'N') balancing_flag,
460: xah.year_end_carry_forward_code
453: -- Get supporting referehnce header
454: CURSOR c_sup_ref_hdr (
455: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
456: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
457: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE
458: ) IS
459: SELECT NVL(xah.balancing_flag,'N') balancing_flag,
460: xah.year_end_carry_forward_code
461: FROM xla_analytical_hdrs_b xah
463: AND xah.analytical_criterion_code = cp_analytical_criterion_code
464: AND xah.analytical_criterion_type_code = cp_criterion_type_code;
465: l_sup_ref_hdr c_sup_ref_hdr%ROWTYPE;
466:
467: l_period_year xla_ac_balances.period_year%TYPE := NULL;
468:
469: -- Get current period end date
470: CURSOR c_current_period_end_date(
471: cp_ledger_id gl_ledgers.ledger_id%TYPE,
498: AND periods.period_name = cp_period_name;
499:
500: -- Get next period's start date
501: CURSOR c_next_period_start_date(
502: cp_application_id xla_ac_balances.application_id%TYPE,
503: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
504: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
505: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
506: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
499:
500: -- Get next period's start date
501: CURSOR c_next_period_start_date(
502: cp_application_id xla_ac_balances.application_id%TYPE,
503: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
504: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
505: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
506: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
507: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
500: -- Get next period's start date
501: CURSOR c_next_period_start_date(
502: cp_application_id xla_ac_balances.application_id%TYPE,
503: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
504: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
505: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
506: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
507: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
508: cp_ac1 xla_ac_balances.ac1%TYPE,
501: CURSOR c_next_period_start_date(
502: cp_application_id xla_ac_balances.application_id%TYPE,
503: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
504: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
505: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
506: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
507: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
508: cp_ac1 xla_ac_balances.ac1%TYPE,
509: cp_ac2 xla_ac_balances.ac2%TYPE,
502: cp_application_id xla_ac_balances.application_id%TYPE,
503: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
504: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
505: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
506: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
507: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
508: cp_ac1 xla_ac_balances.ac1%TYPE,
509: cp_ac2 xla_ac_balances.ac2%TYPE,
510: cp_ac3 xla_ac_balances.ac3%TYPE,
503: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
504: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
505: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
506: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
507: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
508: cp_ac1 xla_ac_balances.ac1%TYPE,
509: cp_ac2 xla_ac_balances.ac2%TYPE,
510: cp_ac3 xla_ac_balances.ac3%TYPE,
511: cp_ac4 xla_ac_balances.ac4%TYPE,
504: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
505: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
506: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
507: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
508: cp_ac1 xla_ac_balances.ac1%TYPE,
509: cp_ac2 xla_ac_balances.ac2%TYPE,
510: cp_ac3 xla_ac_balances.ac3%TYPE,
511: cp_ac4 xla_ac_balances.ac4%TYPE,
512: cp_ac5 xla_ac_balances.ac5%TYPE,
505: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
506: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
507: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
508: cp_ac1 xla_ac_balances.ac1%TYPE,
509: cp_ac2 xla_ac_balances.ac2%TYPE,
510: cp_ac3 xla_ac_balances.ac3%TYPE,
511: cp_ac4 xla_ac_balances.ac4%TYPE,
512: cp_ac5 xla_ac_balances.ac5%TYPE,
513: cp_period_year xla_ac_balances.period_year%TYPE,
506: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
507: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
508: cp_ac1 xla_ac_balances.ac1%TYPE,
509: cp_ac2 xla_ac_balances.ac2%TYPE,
510: cp_ac3 xla_ac_balances.ac3%TYPE,
511: cp_ac4 xla_ac_balances.ac4%TYPE,
512: cp_ac5 xla_ac_balances.ac5%TYPE,
513: cp_period_year xla_ac_balances.period_year%TYPE,
514: cp_period_name xla_ac_balances.period_name%TYPE
507: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
508: cp_ac1 xla_ac_balances.ac1%TYPE,
509: cp_ac2 xla_ac_balances.ac2%TYPE,
510: cp_ac3 xla_ac_balances.ac3%TYPE,
511: cp_ac4 xla_ac_balances.ac4%TYPE,
512: cp_ac5 xla_ac_balances.ac5%TYPE,
513: cp_period_year xla_ac_balances.period_year%TYPE,
514: cp_period_name xla_ac_balances.period_name%TYPE
515: ) IS
508: cp_ac1 xla_ac_balances.ac1%TYPE,
509: cp_ac2 xla_ac_balances.ac2%TYPE,
510: cp_ac3 xla_ac_balances.ac3%TYPE,
511: cp_ac4 xla_ac_balances.ac4%TYPE,
512: cp_ac5 xla_ac_balances.ac5%TYPE,
513: cp_period_year xla_ac_balances.period_year%TYPE,
514: cp_period_name xla_ac_balances.period_name%TYPE
515: ) IS
516: SELECT periods.start_date
509: cp_ac2 xla_ac_balances.ac2%TYPE,
510: cp_ac3 xla_ac_balances.ac3%TYPE,
511: cp_ac4 xla_ac_balances.ac4%TYPE,
512: cp_ac5 xla_ac_balances.ac5%TYPE,
513: cp_period_year xla_ac_balances.period_year%TYPE,
514: cp_period_name xla_ac_balances.period_name%TYPE
515: ) IS
516: SELECT periods.start_date
517: FROM xla_ac_balances xab,
510: cp_ac3 xla_ac_balances.ac3%TYPE,
511: cp_ac4 xla_ac_balances.ac4%TYPE,
512: cp_ac5 xla_ac_balances.ac5%TYPE,
513: cp_period_year xla_ac_balances.period_year%TYPE,
514: cp_period_name xla_ac_balances.period_name%TYPE
515: ) IS
516: SELECT periods.start_date
517: FROM xla_ac_balances xab,
518: gl_ledgers ledger,
513: cp_period_year xla_ac_balances.period_year%TYPE,
514: cp_period_name xla_ac_balances.period_name%TYPE
515: ) IS
516: SELECT periods.start_date
517: FROM xla_ac_balances xab,
518: gl_ledgers ledger,
519: gl_periods periods,
520: gl_period_types period_types,
521: gl_period_statuses period_statuses,
555: l_synchronize_fut_periods BOOLEAN;
556:
557: -- Get future periods
558: CURSOR c_future_periods(
559: cp_application_id xla_ac_balances.application_id%TYPE,
560: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
561: cp_period_year xla_ac_balances.period_year%TYPE,
562: cp_earliest_start_date DATE,
563: cp_latest_end_date DATE
556:
557: -- Get future periods
558: CURSOR c_future_periods(
559: cp_application_id xla_ac_balances.application_id%TYPE,
560: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
561: cp_period_year xla_ac_balances.period_year%TYPE,
562: cp_earliest_start_date DATE,
563: cp_latest_end_date DATE
564: ) IS
557: -- Get future periods
558: CURSOR c_future_periods(
559: cp_application_id xla_ac_balances.application_id%TYPE,
560: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
561: cp_period_year xla_ac_balances.period_year%TYPE,
562: cp_earliest_start_date DATE,
563: cp_latest_end_date DATE
564: ) IS
565: SELECT fut_periods.period_name,
611: l_period_num gl_periods.period_num%TYPE;
612:
613:
614: CURSOR c_future_open_periods(
615: cp_application_id xla_ac_balances.application_id%TYPE,
616: cp_ledger_id gl_ledgers.ledger_id%TYPE,
617: cp_period_name gl_periods.period_name%TYPE,
618: cp_period_year gl_periods.period_year%TYPE
619: ) IS
647: AND ref_period.start_date < fut_periods.start_date;
648: l_future_open_periods c_future_open_periods%ROWTYPE;
649:
650: CURSOR c_closed_periods(
651: cp_application_id xla_ac_balances.application_id%TYPE,
652: cp_ledger_id gl_ledgers.ledger_id%TYPE,
653: cp_period_name gl_periods.period_name%TYPE,
654: cp_period_year gl_periods.period_year%TYPE
655: ) IS
687:
688: l_delete_cr_delta NUMBER;
689: l_delete_dr_delta NUMBER;
690:
691: l_prev_year xla_ac_balances.period_year%TYPE;
692:
693: CURSOR c_delete_records(
694: cp_application_id xla_ac_balances.application_id%TYPE,
695: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
690:
691: l_prev_year xla_ac_balances.period_year%TYPE;
692:
693: CURSOR c_delete_records(
694: cp_application_id xla_ac_balances.application_id%TYPE,
695: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
696: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
697: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
698: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
691: l_prev_year xla_ac_balances.period_year%TYPE;
692:
693: CURSOR c_delete_records(
694: cp_application_id xla_ac_balances.application_id%TYPE,
695: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
696: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
697: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
698: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
699: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
692:
693: CURSOR c_delete_records(
694: cp_application_id xla_ac_balances.application_id%TYPE,
695: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
696: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
697: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
698: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
699: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
700: cp_ac1 xla_ac_balances.ac1%TYPE,
693: CURSOR c_delete_records(
694: cp_application_id xla_ac_balances.application_id%TYPE,
695: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
696: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
697: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
698: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
699: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
700: cp_ac1 xla_ac_balances.ac1%TYPE,
701: cp_ac2 xla_ac_balances.ac2%TYPE,
694: cp_application_id xla_ac_balances.application_id%TYPE,
695: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
696: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
697: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
698: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
699: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
700: cp_ac1 xla_ac_balances.ac1%TYPE,
701: cp_ac2 xla_ac_balances.ac2%TYPE,
702: cp_ac3 xla_ac_balances.ac3%TYPE,
695: cp_ledger_id xla_ac_balances.ledger_id%TYPE,
696: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
697: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
698: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
699: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
700: cp_ac1 xla_ac_balances.ac1%TYPE,
701: cp_ac2 xla_ac_balances.ac2%TYPE,
702: cp_ac3 xla_ac_balances.ac3%TYPE,
703: cp_ac4 xla_ac_balances.ac4%TYPE,
696: cp_code_combination_id xla_ac_balances.code_combination_id%TYPE,
697: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
698: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
699: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
700: cp_ac1 xla_ac_balances.ac1%TYPE,
701: cp_ac2 xla_ac_balances.ac2%TYPE,
702: cp_ac3 xla_ac_balances.ac3%TYPE,
703: cp_ac4 xla_ac_balances.ac4%TYPE,
704: cp_ac5 xla_ac_balances.ac5%TYPE,
697: cp_analytical_criterion_code xla_ac_balances.analytical_criterion_code%TYPE,
698: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
699: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
700: cp_ac1 xla_ac_balances.ac1%TYPE,
701: cp_ac2 xla_ac_balances.ac2%TYPE,
702: cp_ac3 xla_ac_balances.ac3%TYPE,
703: cp_ac4 xla_ac_balances.ac4%TYPE,
704: cp_ac5 xla_ac_balances.ac5%TYPE,
705: cp_period_year xla_ac_balances.period_year%TYPE
698: cp_criterion_type_code xla_ac_balances.analytical_criterion_type_code%TYPE,
699: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
700: cp_ac1 xla_ac_balances.ac1%TYPE,
701: cp_ac2 xla_ac_balances.ac2%TYPE,
702: cp_ac3 xla_ac_balances.ac3%TYPE,
703: cp_ac4 xla_ac_balances.ac4%TYPE,
704: cp_ac5 xla_ac_balances.ac5%TYPE,
705: cp_period_year xla_ac_balances.period_year%TYPE
706: ) IS
699: cp_amb_context_code xla_ac_balances.amb_context_code%TYPE,
700: cp_ac1 xla_ac_balances.ac1%TYPE,
701: cp_ac2 xla_ac_balances.ac2%TYPE,
702: cp_ac3 xla_ac_balances.ac3%TYPE,
703: cp_ac4 xla_ac_balances.ac4%TYPE,
704: cp_ac5 xla_ac_balances.ac5%TYPE,
705: cp_period_year xla_ac_balances.period_year%TYPE
706: ) IS
707: SELECT xab.*
700: cp_ac1 xla_ac_balances.ac1%TYPE,
701: cp_ac2 xla_ac_balances.ac2%TYPE,
702: cp_ac3 xla_ac_balances.ac3%TYPE,
703: cp_ac4 xla_ac_balances.ac4%TYPE,
704: cp_ac5 xla_ac_balances.ac5%TYPE,
705: cp_period_year xla_ac_balances.period_year%TYPE
706: ) IS
707: SELECT xab.*
708: FROM xla_ac_balances xab,
701: cp_ac2 xla_ac_balances.ac2%TYPE,
702: cp_ac3 xla_ac_balances.ac3%TYPE,
703: cp_ac4 xla_ac_balances.ac4%TYPE,
704: cp_ac5 xla_ac_balances.ac5%TYPE,
705: cp_period_year xla_ac_balances.period_year%TYPE
706: ) IS
707: SELECT xab.*
708: FROM xla_ac_balances xab,
709: gl_ledgers ledger,
704: cp_ac5 xla_ac_balances.ac5%TYPE,
705: cp_period_year xla_ac_balances.period_year%TYPE
706: ) IS
707: SELECT xab.*
708: FROM xla_ac_balances xab,
709: gl_ledgers ledger,
710: gl_periods fut_periods,
711: gl_period_types period_types,
712: gl_period_statuses fut_period_statuses,
856: IF (C_LEVEL_STATEMENT >= g_log_level) THEN
857: trace(p_module => l_log_module,p_msg => 'l_period_year:'||l_period_year,p_level => C_LEVEL_STATEMENT);
858: END IF;
859: /*
860: * For maintaining balances, we need to peek into xla_ac_balances table and find which period(ocurring after the period for which
861: * data is being imported) is next. We get this "next period" and see if there are more periods in between the period for which
862: * data is being imported and the "next period". We need to insert new records for such intermediate periods
863: */
864:
1548: WHEN xla_exceptions_pkg.application_exception THEN
1549: RAISE;
1550: WHEN OTHERS THEN
1551: xla_exceptions_pkg.raise_message
1552: (p_location => 'xla_ac_balances_pkg.merge_balances_rec');
1553: END merge_balances_rec;
1554:
1555: FUNCTION validate_balances_rec(
1556: p_balances_int_rec IN OUT NOCOPY xla_ac_balances_int%ROWTYPE,
1552: (p_location => 'xla_ac_balances_pkg.merge_balances_rec');
1553: END merge_balances_rec;
1554:
1555: FUNCTION validate_balances_rec(
1556: p_balances_int_rec IN OUT NOCOPY xla_ac_balances_int%ROWTYPE,
1557: p_message_codes OUT NOCOPY xla_ac_balances_int.message_codes%TYPE
1558: )
1559: RETURN BOOLEAN IS
1560: l_log_module VARCHAR2 (2000);
1553: END merge_balances_rec;
1554:
1555: FUNCTION validate_balances_rec(
1556: p_balances_int_rec IN OUT NOCOPY xla_ac_balances_int%ROWTYPE,
1557: p_message_codes OUT NOCOPY xla_ac_balances_int.message_codes%TYPE
1558: )
1559: RETURN BOOLEAN IS
1560: l_log_module VARCHAR2 (2000);
1561: l_result boolean :=true;
1558: )
1559: RETURN BOOLEAN IS
1560: l_log_module VARCHAR2 (2000);
1561: l_result boolean :=true;
1562: l_rec xla_ac_balances_int%ROWTYPE := p_balances_int_rec;
1563: l_test_value NUMBER;
1564: l_error_codes xla_ac_balances_int.message_codes%TYPE;
1565: l_ledger_category_code gl_ledgers.ledger_category_code%TYPE;
1566: l_code_comb_id xla_ac_balances_int.code_combination_id%TYPE;
1560: l_log_module VARCHAR2 (2000);
1561: l_result boolean :=true;
1562: l_rec xla_ac_balances_int%ROWTYPE := p_balances_int_rec;
1563: l_test_value NUMBER;
1564: l_error_codes xla_ac_balances_int.message_codes%TYPE;
1565: l_ledger_category_code gl_ledgers.ledger_category_code%TYPE;
1566: l_code_comb_id xla_ac_balances_int.code_combination_id%TYPE;
1567: l_coa_id gl_ledgers.chart_of_accounts_id%TYPE;
1568:
1562: l_rec xla_ac_balances_int%ROWTYPE := p_balances_int_rec;
1563: l_test_value NUMBER;
1564: l_error_codes xla_ac_balances_int.message_codes%TYPE;
1565: l_ledger_category_code gl_ledgers.ledger_category_code%TYPE;
1566: l_code_comb_id xla_ac_balances_int.code_combination_id%TYPE;
1567: l_coa_id gl_ledgers.chart_of_accounts_id%TYPE;
1568:
1569: x_err_msg VARCHAR2(1000);
1570: x_ccid NUMBER := 0;
1576: --
1577: -- Cursor to validate application_id
1578: --
1579: --=============================================================================
1580: CURSOR c_is_valid_application(p_app_id xla_ac_balances_int.application_id%TYPE) IS
1581: select 1
1582: from xla_subledgers
1583: where application_id = p_app_id;
1584:
1586: --
1587: -- Cursor to validate ledger
1588: --
1589: --=============================================================================
1590: CURSOR c_is_valid_ledger(p_ledger_id xla_ac_balances_int.ledger_id%TYPE) IS
1591: select ledger_category_code
1592: from gl_ledgers
1593: where ledger_id = p_ledger_id;
1594:
1596: --
1597: -- Cursor to validate secondary or ALC ledger
1598: --
1599: --=============================================================================
1600: CURSOR c_is_valid_sec_ledger(p_ledger_id xla_ac_balances_int.ledger_id%TYPE) IS
1601: select 1
1602: from gl_ledger_relationships
1603: where primary_ledger_id = p_ledger_id
1604: and relationship_type_code='SUBLEDGER';
1607: --
1608: -- Cursor to fetch chart_of_accounts id for a given ledger
1609: --
1610: --=============================================================================
1611: CURSOR c_fetch_coa_id(p_ledger_id xla_ac_balances_int.ledger_id%TYPE) IS
1612: select chart_of_accounts_id
1613: from gl_ledgers
1614: where ledger_id = p_ledger_id;
1615:
1618: -- Cursor to validate code combination id
1619: --
1620: --=============================================================================
1621: CURSOR c_is_valid_code_comb_id(
1622: p_ledger_id xla_ac_balances_int.ledger_id%TYPE
1623: ,p_code_comb_id gl_code_combinations.code_combination_id%TYPE) IS
1624: select 1
1625: from gl_ledgers lg,
1626: gl_code_combinations cc
1632: --
1633: -- Cursor to validate populated code combination id
1634: --
1635: --=============================================================================
1636: CURSOR c_is_valid_pop_code_comb_id(p_rec xla_ac_balances_int%ROWTYPE) IS
1637: select gl.code_combination_id
1638: from gl_code_combinations gl
1639: where NVL(gl.segment1, 'X') = NVL(p_rec.segment1, 'X')
1640: and NVL(gl.segment2, 'X') = NVL(p_rec.segment2, 'X')
1690: -- Cursor to validate period
1691: --
1692: --=============================================================================
1693: CURSOR c_is_valid_period
1694: (p_app_id xla_ac_balances_int.application_id%TYPE
1695: ,p_ledger_id xla_ac_balances_int.ledger_id%TYPE
1696: ,p_period_name gl_period_statuses.period_name%TYPE) IS
1697: select 1
1698: from gl_period_statuses
1691: --
1692: --=============================================================================
1693: CURSOR c_is_valid_period
1694: (p_app_id xla_ac_balances_int.application_id%TYPE
1695: ,p_ledger_id xla_ac_balances_int.ledger_id%TYPE
1696: ,p_period_name gl_period_statuses.period_name%TYPE) IS
1697: select 1
1698: from gl_period_statuses
1699: where application_id =101 --bug 11811413
1705: --
1706: -- Cursor to validate prior journal extries exits or not for the given period name
1707: --
1708: --=============================================================================
1709: CURSOR c_is_prior_je_exists(p_rec xla_ac_balances_int%ROWTYPE) IS
1710: select 1
1711: from xla_ac_balances
1712: where ledger_id = p_rec.ledger_id
1713: and code_combination_id = p_rec.code_combination_id
1707: --
1708: --=============================================================================
1709: CURSOR c_is_prior_je_exists(p_rec xla_ac_balances_int%ROWTYPE) IS
1710: select 1
1711: from xla_ac_balances
1712: where ledger_id = p_rec.ledger_id
1713: and code_combination_id = p_rec.code_combination_id
1714: and analytical_criterion_code = p_rec.analytical_criterion_code
1715: and analytical_criterion_type_code = p_rec.analytical_criterion_type_code
1945: WHEN xla_exceptions_pkg.application_exception THEN
1946: RAISE;
1947: WHEN OTHERS THEN
1948: xla_exceptions_pkg.raise_message
1949: (p_location => 'xla_ac_balances_pkg.validate_balances_rec');
1950: END validate_balances_rec;
1951:
1952: PROCEDURE purge_interface_recs(
1953: p_batch_code VARCHAR2,
1973: * If p_purge_mode = A, then delete all records for the p_batch_code passed
1974: * If p_purge_mode = S, then delete all records that were imported in this run
1975: * If p_purge_mode = N, then do not delete anything
1976: */
1977: DELETE xla_ac_balances_int xib
1978: WHERE ( ( p_batch_code IS NOT NULL
1979: AND p_batch_code = xib.batch_code)
1980: OR ( p_batch_code IS NULL
1981: AND 1 = 1))
1990: WHEN xla_exceptions_pkg.application_exception THEN
1991: RAISE;
1992: WHEN OTHERS THEN
1993: xla_exceptions_pkg.raise_message
1994: (p_location => 'xla_ac_balances_pkg.purge_interface_recs');
1995: END purge_interface_recs;
1996:
1997: PROCEDURE update_balances
1998: ( p_errbuf OUT NOCOPY VARCHAR2
2043:
2044: l_commit_flag VARCHAR2(1);
2045:
2046: l_log_module VARCHAR2 (2000);
2047: l_message_codes xla_ac_balances_int.message_codes%TYPE;
2048:
2049: -- Get interface records
2050: CURSOR c_balances_int(
2051: cp_batch_code VARCHAR2
2050: CURSOR c_balances_int(
2051: cp_batch_code VARCHAR2
2052: ) IS
2053: SELECT xib.*
2054: FROM xla_ac_balances_int xib
2055: WHERE ( xib.status IS NULL
2056: OR xib.status = 'ERROR')
2057: AND (xib.batch_code = NVL(cp_batch_code, xib.batch_code))
2058: ORDER BY batch_code DESC
2057: AND (xib.batch_code = NVL(cp_batch_code, xib.batch_code))
2058: ORDER BY batch_code DESC
2059: FOR UPDATE OF status NOWAIT;
2060:
2061: l_balances_int_rec xla_ac_balances_int%ROWTYPE;
2062: l_sql_err VARCHAR2(1000);
2063:
2064: l_success_rec NUMBER := 0;
2065: l_error_rec NUMBER := 0;
2104: ,p_level => C_LEVEL_STATEMENT);
2105: END IF;
2106: fnd_file.put_line(fnd_file.log,'Import Failed with error codes: '||l_message_codes);
2107: fnd_file.new_line(fnd_file.log,2);
2108: UPDATE xla_ac_balances_int
2109: SET status = 'ERROR',
2110: message_codes = l_message_codes,
2111: last_updated_by = g_user_id,
2112: last_update_date = g_date,
2129: * Successfully merged the records. Update the status of the interface record
2130: */
2131: fnd_file.put_line(fnd_file.log,'Import Succeeded');
2132: fnd_file.new_line(fnd_file.log,2);
2133: UPDATE xla_ac_balances_int
2134: SET status = 'IMPORTED',
2135: message_codes = NULL,
2136: last_updated_by = g_user_id,
2137: last_update_date = g_date,
2140: l_success_rec := l_success_rec + 1;
2141: END IF;
2142: EXCEPTION
2143: WHEN CANT_DELETE_BALANCES THEN
2144: UPDATE xla_ac_balances_int
2145: SET status = 'ERROR',
2146: message_codes = 'IB018',
2147: last_updated_by = g_user_id,
2148: last_update_date = g_date,
2150: WHERE CURRENT OF c_balances_int;
2151: l_error_rec := l_error_rec + 1;
2152: WHEN OTHERS THEN
2153: l_sql_err := SQLERRM;
2154: UPDATE xla_ac_balances_int
2155: SET status = 'ERROR',
2156: message_codes = l_sql_err,
2157: last_updated_by = g_user_id,
2158: last_update_date = g_date,
2173: WHEN xla_exceptions_pkg.application_exception THEN
2174: RAISE;
2175: WHEN OTHERS THEN
2176: xla_exceptions_pkg.raise_message
2177: (p_location => 'xla_ac_balances_pkg.update_balances');
2178: END update_balances;
2179:
2180: BEGIN
2181: g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2193: g_prog_appl_id := xla_environment_pkg.g_prog_appl_id;
2194: g_prog_id := xla_environment_pkg.g_prog_id;
2195: g_req_id := NVL(xla_environment_pkg.g_req_id, -1);
2196:
2197: END xla_ac_balances_pkg;