[Home] [Help]
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 |
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.adjustment_period_flag = 'N'
141: AND gps.period_name = cp_period_name;
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;
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,
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
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;
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,
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
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;
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
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
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:
361: l_log_module VARCHAR2 (2000);
361: l_log_module VARCHAR2 (2000);
362:
363: -- Get existing balance
364: CURSOR c_exist_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,
362:
363: -- Get existing balance
364: CURSOR c_exist_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,
363: -- Get existing balance
364: CURSOR c_exist_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,
364: CURSOR c_exist_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,
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,
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,
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
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
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.*
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
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
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
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
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,
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,
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,
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,
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,
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,
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,
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,
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,
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,
408: cp_period_name xla_ac_balances.period_name%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,
408: cp_period_name xla_ac_balances.period_name%TYPE
409: ) IS
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,
408: cp_period_name xla_ac_balances.period_name%TYPE
409: ) IS
410: SELECT xab.*
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,
408: cp_period_name xla_ac_balances.period_name%TYPE
409: ) IS
410: SELECT xab.*
411: FROM xla_ac_balances xab,
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,
408: cp_period_name xla_ac_balances.period_name%TYPE
409: ) IS
410: SELECT xab.*
411: FROM xla_ac_balances xab,
412: gl_ledgers ledger,
407: cp_period_year xla_ac_balances.period_year%TYPE,
408: cp_period_name xla_ac_balances.period_name%TYPE
409: ) IS
410: SELECT xab.*
411: FROM xla_ac_balances xab,
412: gl_ledgers ledger,
413: gl_periods fut_periods,
414: gl_period_types period_types,
415: gl_period_statuses fut_period_statuses,
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:
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,
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
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
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,
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,
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,
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,
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,
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,
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,
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,
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,
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
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
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
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,
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,
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,
518: gl_period_statuses period_statuses,
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
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
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,
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
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
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,
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,
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,
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,
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,
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,
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,
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,
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
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
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.*
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,
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,
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,
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:
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,
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);
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;
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;
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:
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;
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:
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:
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';
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:
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
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')
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
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
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
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
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,
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))
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
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
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
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;
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,
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,
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,
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,
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;
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;