[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(sum(nvl(gb.begin_balance_dr_beq, 0) - nvl (gb.begin_balance_cr_beq, 0)),0) YTD_AMOUNT,
nvl(sum(nvl (gb.period_net_dr_beq, 0) - nvl (gb.period_net_cr_beq, 0)),0) PTD_AMOUNT
into p_ytd,p_ptd
FROM gl_balances gb
WHERE gb.ledger_id = p_set_of_books_id
AND gb.code_combination_id = p_code_combination_id
AND gb.currency_code <> set_of_books_currency_code
AND gb.Period_name = p_period_name
AND gb.actual_flag = 'A'
AND gb.translated_flag is not null
AND gb.template_id is null;
SELECT a.period_num,
a.period_year,
a.period_type
INTO curr_period_num ,
curr_period_year,
curr_period_type
FROM gl_periods a
WHERE a.period_set_name = p_period_set_name
AND a.period_name = p_period_name;
SELECT count(period_num)
INTO period_counter
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND period_year = curr_period_year
AND period_num < curr_period_num
AND period_type = curr_period_type
AND adjustment_period_flag <> 'Y';
SELECT period_name
INTO p_prev_period_name
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND period_year = curr_period_year
AND period_type = curr_period_type
AND period_num = (SELECT max(period_num)
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND period_year = curr_period_year
AND period_num < curr_period_num
AND period_type = curr_period_type
AND adjustment_period_flag <> 'Y');
SELECT period_name
INTO p_prev_period_name
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND period_year = curr_period_year - 1
AND period_type = curr_period_type
AND period_num = (SELECT max(period_num)
FROM gl_periods
WHERE period_set_name = p_period_set_name
AND period_year = curr_period_year - 1
AND period_type = curr_period_type
AND adjustment_period_flag <> 'Y');
SELECT name, currency_code, chart_of_accounts_id, period_set_name
INTO p_set_of_books_name, p_sob_currency_code, p_chart_of_accounts_id,
p_period_set_name
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
SELECT precision
INTO p_curr_precision
FROM fnd_currencies_vl
WHERE currency_code = p_sob_currency_code;
SELECT price_index_value
INTO p_index_value
FROM fa_price_index_values fpiv
, gl_periods gp
WHERE fpiv.price_index_id = p_index_id
AND gp.period_name = ix_period_name
ANd gp.period_set_name = p_period_set_name
AND gp.end_date BETWEEN fpiv.from_date
AND nvl (fpiv.to_date, gp.end_date);
SELECT gl_interface_control_s.nextval
INTO p_group_id
FROM sys.dual;
SELECT code_combination_id
INTO p_REI_ccid
FROM jl_zz_gl_axi
WHERE set_of_books_id = p_set_of_books_id;
statement := 'DELETE';
DELETE FROM jl_zz_gl_axi_tmp
WHERE axi_run_id = p_axi_run_id;
SELECT user_je_source_name
INTO p_user_je_source_name
FROM gl_je_sources
WHERE je_source_name = 'Inflation';
SELECT user_je_category_name
INTO p_user_je_category_name
FROM gl_je_categories
WHERE je_category_name = 'Adjustment';
SELECT end_date
INTO p_accounting_date
FROM gl_period_statuses
WHERE application_id = GL_APPS_ID
AND set_of_books_id = p_set_of_books_id
AND period_name = p_to_period_name;
SELECT period_year, period_num
INTO p_period_year, p_period_num
FROM gl_periods
WHERE period_name = p_period_name
AND period_set_name = p_period_set_name;
SELECT application_column_name name
FROM fnd_id_flex_segments
WHERE application_id = 101 AND
id_flex_code = 'GL#' AND
id_flex_num = chart_of_accounts_id AND
enabled_flag = 'Y'
ORDER by segment_num;
'SELECT gcc.code_combination_id'
|| ', gcc.' || p_balancing_segment || ' balancing_sement'
|| ', nvl (gb.begin_balance_dr, 0) - nvl (gb.begin_balance_cr, 0) '
|| ' ytd_balance'
|| ', nvl (gb.period_net_dr, 0) - nvl (gb.period_net_cr, 0) ptd_balance'
|| ', gb.period_name'
|| ', gb.period_year'
|| ', gb.period_num'
|| ' FROM gl_code_combinations gcc'
|| ' , gl_balances gb'
|| ' , jl_zz_gl_axi_accounts jagaa'
|| ' WHERE gcc.code_combination_id = gb.code_combination_id'
|| ' AND gb.ledger_id = ' || p_set_of_books_id
|| ' AND gb.currency_code = ''' || set_of_books_currency_code||''''
|| ' AND gb.actual_flag = ''A'''
|| ' AND gb.translated_flag is null'
|| ' AND gb.template_id is null'
|| ' AND gb.period_year BETWEEN ' || period_year_from
|| ' AND ' || period_year_to
|| ' AND gb.period_num BETWEEN ' || period_num_from
|| ' AND ' || period_num_to
|| ' AND jagaa.axi_run_id = ' || p_axi_run_id;
'SELECT gcc.code_combination_id'
|| ', gcc.' || p_balancing_segment || ' balancing_sement'
|| ', nvl (gb.begin_balance_dr, 0) - nvl (gb.begin_balance_cr, 0) '
|| ' ytd_balance'
|| ', nvl (gb.period_net_dr, 0) - nvl (gb.period_net_cr, 0) ptd_balance'
|| ', gb.period_name'
|| ', gb.period_year'
|| ', gb.period_num'
|| ' FROM gl_code_combinations gcc'
|| ' , gl_balances gb'
|| ' , jl_zz_gl_axi_accounts jagaa'
|| ' WHERE gcc.code_combination_id = gb.code_combination_id'
|| ' AND gb.ledger_id = :1'
--|| ' AND gb.currency_code = ''' || ':2' ||'''' --Bug 3183432
|| ' AND gb.currency_code = :2'
|| ' AND gb.actual_flag = ''A'''
|| ' AND gb.translated_flag is null'
|| ' AND gb.template_id is null'
|| ' AND gb.period_year BETWEEN :3' || ' AND :4 '
|| ' AND gb.period_num BETWEEN :5' || ' AND :6 '
|| ' AND jagaa.axi_run_id = :7';
FUNCTION insert_gl_interface (p_set_of_books_id IN NUMBER
, p_code_combination_id IN NUMBER
, p_accounting_date IN DATE
, p_currency_code IN VARCHAR2
, p_amount IN NUMBER
, p_group_id IN NUMBER
, p_user_je_category_name IN VARCHAR2
, p_user_je_source_name IN VARCHAR2)
RETURN BOOLEAN IS
insert_failed EXCEPTION;
INSERT INTO gl_interface (status
, set_of_books_id
, accounting_date
, currency_code
, date_created
, created_by
, actual_flag
, user_je_category_name
, user_je_source_name
, entered_dr
, entered_cr
, reference1
, code_combination_id
, group_id)
VALUES ('NEW'
, p_set_of_books_id
, p_accounting_date
, p_currency_code
, sysdate
, fnd_global.user_id
, 'A'
, p_user_je_category_name
, p_user_je_source_name
, decode (sign (p_amount), -1, 0, p_amount)
, decode (sign (p_amount), -1, p_amount * (-1), 0)
, 'JL'
, p_code_combination_id
, p_group_id );
RAISE insert_failed;
END insert_gl_interface;
SELECT sum (nvl (accounted_dr, 0)) -
sum (nvl (accounted_cr, 0)) FV_total
, gps.period_name
FROM gl_je_lines gjl
, gl_je_headers gjh
, gl_period_statuses gps
WHERE gjh.status = 'P'
AND gjh.ledger_id = p_set_of_books_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.code_combination_id = p_code_combination_id
AND gjl.period_name = p_period_name
AND gjh.actual_flag = 'A'
AND gjh.currency_conversion_date is not null
AND gjh.currency_conversion_date < gjl.effective_date
AND gps.application_id = GL_APPS_ID
AND nvl (gps.adjustment_period_flag, 'N') = 'N'
AND gjh.currency_conversion_date
BETWEEN gps.start_date AND gps.end_date
AND gps.set_of_books_id = p_set_of_books_id
AND gps.period_name <> p_period_name
AND decode(profile_country_code,'CL',set_of_books_currency_code,gjh.currency_code) = gjh.currency_code
GROUP BY gps.period_name;
FUNCTION insert_axi_tmp (p_code_combination_id IN NUMBER
, p_inflation_adj_run_id IN NUMBER
, p_group_id IN NUMBER
, p_from_period IN VARCHAR2
, p_to_period IN VARCHAR2
, p_ytd_balance IN NUMBER
, p_ptd_balance IN NUMBER
, p_acct_total_adj_amount IN NUMBER
, p_fv_flag IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
INSERT INTO jl_zz_gl_axi_tmp (code_combination_id
, axi_run_id
, group_id
, period_from
, period_to
, ytd_balance
, ptd_balance
, adjustment_amount
, fv_flag)
VALUES (p_code_combination_id
, p_inflation_adj_run_id
, p_group_id
, p_from_period
, p_to_period
, p_ytd_balance
, p_ptd_balance
, p_acct_total_adj_amount
, p_fv_flag);
END insert_axi_tmp;
IF NOT insert_gl_interface (p_set_of_books_id
, previous_code_combination_id
, accounting_date
, set_of_books_currency_code
, acct_total_adj_amount
, p_group_id
, user_je_category_name
, user_je_source_name) THEN
RAISE program_abort;
IF NOT insert_axi_tmp (prevrec.r_ccid
, p_inflation_adj_run_id
, p_group_id
, prevrec.r_period_name
, prevrec.r_period_name
, prevrec.r_ytd_balance
, prevrec.r_ptd_balance
, acct_total_adj_amount
, fv_flag) THEN
RAISE program_abort;
IF NOT insert_gl_interface (p_set_of_books_id
, infl_adj_gla
, accounting_date
, set_of_books_currency_code
, balancing_segment_tot_amount
, p_group_id
, user_je_category_name
, user_je_source_name) THEN
RAISE program_abort;
IF NOT insert_gl_interface (p_set_of_books_id
, previous_code_combination_id
, accounting_date
, set_of_books_currency_code
, acct_total_adj_amount
, p_group_id
, user_je_category_name
, user_je_source_name) THEN
RAISE program_abort;
IF NOT insert_axi_tmp (prevrec.r_ccid
, p_inflation_adj_run_id
, p_group_id
, prevrec.r_period_name
, prevrec.r_period_name
, prevrec.r_ytd_balance
, prevrec.r_ptd_balance
, acct_total_adj_amount
, fv_flag) THEN
RAISE program_abort;
IF NOT insert_gl_interface (p_set_of_books_id
, infl_adj_gla
, accounting_date
, set_of_books_currency_code
, balancing_segment_tot_amount
, p_group_id
, user_je_category_name
, user_je_source_name) THEN
RAISE program_abort;