[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FOR factsii_rec IN (SELECT *
FROM fv_factsii_ending_balances
WHERE set_of_books_id = p_ledger_id
AND fiscal_year = p_fiscal_year) LOOP
report ('|'||
RPAD (factsii_rec.ussgl_account, 7, ' ')||
'|'||
RPAD (NVL(factsii_rec.public_law, ' '), 10, ' ')||
'|'||
RPAD (NVL(factsii_rec.advance_flag, ' '), 7, ' ')||
'|'||
RPAD (NVL(factsii_rec.transfer_dept_id, ' '), 9, ' ')||
'|'||
RPAD (NVL(factsii_rec.transfer_main_acct, ' '), 14, ' ')||
'|'||
LPAD (NVL(TO_CHAR(factsii_rec.ending_balance_dr), ' '), 17, ' ')||
'|'||
LPAD (NVL(TO_CHAR(factsii_rec.ending_balance_cr), ' '), 17, ' ')||
'|');
SELECT child_flex_value_low,
child_flex_value_high
FROM fnd_flex_value_hierarchies
WHERE parent_flex_value = c_account
AND flex_value_set_id = c_flex_value_set_id;
SELECT flex_value account
FROM fnd_flex_values_vl
WHERE flex_value_set_id = c_flex_value_set_id
AND flex_value BETWEEN c_flex_value_low AND c_flex_value_hi;
FOR account_rec IN (SELECT fsgl.ussgl_account,
fa.public_law_code,
fa.advance_flag,
fa.transfer_flag
FROM fv_facts_attributes fa,
fv_facts_ussgl_accounts fsgl
WHERE fa.set_of_books_id = p_ledger_id
AND fsgl.reporting_type in (2,3)
AND fsgl.ussgl_account = fa.ussgl_acct_number
AND (fa.public_law_code = 'Y' OR
fa.advance_flag = 'Y' OR
fa.transfer_flag = 'Y')) LOOP
BEGIN
INSERT INTO fv_factsii_accounts_gt
(
ussgl_account,
account,
public_law_code,
advance_flag,
transfer_flag
)
VALUES
(
account_rec.ussgl_account,
account_rec.ussgl_account,
account_rec.public_law_code,
account_rec.advance_flag,
account_rec.transfer_flag
);
l_location := l_module_name||'insert_fv_factsii_accounts_gt';
INSERT INTO fv_factsii_accounts_gt
(
ussgl_account,
account,
public_law_code,
advance_flag,
transfer_flag
)
VALUES
(
account_rec.ussgl_account,
get_child_values_rec.account,
account_rec.public_law_code,
account_rec.advance_flag,
account_rec.transfer_flag
);
l_location := l_module_name||'insert_fv_factsii_accounts_gt2';
SELECT glp.chart_of_accounts_id
INTO p_chart_of_accounts_id
FROM gl_ledgers_public_v glp
WHERE glp.ledger_id = p_ledger_id;
l_location := l_module_name||'select chart_of_accounts_id';
SELECT flex_value_set_id
INTO p_acct_value_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = p_acct_segment
AND application_id = l_application_id
AND id_flex_code = l_id_flex_code
AND id_flex_num = p_chart_of_accounts_id
AND enabled_flag = 'Y';
l_location := l_module_name||'select_fnd_id_flex_segments';
SELECT MAX(period_num)
INTO p_last_period_num
FROM gl_period_statuses gps
WHERE gps.ledger_id = p_ledger_id
AND gps.application_id = l_application_id
AND gps.period_year = p_fiscal_year;
l_location := l_module_name||'select_gl_period_statuses';
SELECT factsii_pub_law_code_attribute,
factsii_advance_type_attribute,
factsii_tr_main_acct_attribute,
factsii_tr_dept_id_attribute
INTO p_pub_law_code_col,
p_advance_type_col,
p_tr_main_acct_col,
p_tr_dept_id_col
FROM fv_system_parameters;
l_location := l_module_name||'select_gl_period_statuses';
SELECT application_column_name
INTO p_fyr_segment
FROM fv_pya_fiscalyear_segment
WHERE set_of_books_id = p_ledger_id;
l_location := l_module_name||'select_fv_pya_fiscalyear_segment';
DELETE fv_factsii_ending_balances
WHERE set_of_books_id = p_ledger_id
AND fiscal_year = p_fiscal_year;
l_location := l_module_name||'delete_fv_factsii_ending_balances';
SELECT fp.treasury_symbol_id,
ffa.cohort_segment_name
INTO p_treasury_symbol_id,
p_cohort_segment
FROM fv_fund_parameters fp,
fv_treasury_symbols fts,
fv_facts_federal_accounts ffa
WHERE fp.set_of_books_id = p_ledger_id
AND fp.fund_value = p_fund_value
AND fts.treasury_symbol_id = fp.treasury_symbol_id
AND fts.federal_acct_symbol_id = ffa.federal_acct_symbol_id
AND fts.set_of_books_id = p_ledger_id
AND ffa.set_of_books_id = p_ledger_id;
l_location := l_module_name||'select_fv_fund_parameters';
l_insert_required BOOLEAN := FALSE;
l_gl_cursor_str := 'INSERT INTO fv_factsii_ending_balances
(
set_of_books_id,
fiscal_year,
ccid,
account,
ussgl_account,
fund,
fyr,
cohort,
ending_balance_cr,
ending_balance_dr,
public_law,
advance_flag,
transfer_main_acct,
transfer_dept_id,
record_category,
creation_date,
created_by,
last_update_date,
last_update_by,
request_id,
treasury_symbol_id
)
SELECT :b_set_of_books_id,
:b_period_year,
:b_code_combination_id,
:b_account,
:b_ussgl_account,
:b_fund,
:b_fyr,
:b_cohort,
SUM(NVL(gll.entered_cr, 0)),
SUM(NVL(gll.entered_dr, 0)),'||
l_attribute_columns||
'''E'',
:b_curr_date,
:b_user_id,
:b_curr_date,
:b_user_id,
:b_request_id,
:b_treasury_symbol_id
FROM gl_je_lines gll,
gl_je_headers gjh,
gl_period_statuses gps,
fv_be_trx_dtls be
WHERE gjh.ledger_id = gps.ledger_id
AND gll.code_combination_id = :b_code_combination_id
AND gjh.status=''P''
AND gll.ledger_id = gjh.ledger_id
AND gll.je_header_id = gjh.je_header_id
AND NVL(gjh.je_from_sla_flag, ''N'') IN (''N'',''U'')
AND gjh.actual_flag = ''A''
AND gps.application_id = 101
AND gps.ledger_id = :b_set_of_books_id
AND gjh.period_name = gps.period_name '||
l_where_columns ||'
AND gps.period_year = :b_period_year
AND gll.reference_1 = be.transaction_id (+)
AND be.set_of_books_id (+) = :b_set_of_books_id
GROUP BY '|| l_attr_group_columns||
' HAVING SUM(NVL(gll.entered_dr, 0))-SUM(NVL(gll.entered_cr, 0)) <> 0
UNION
SELECT :b_set_of_books_id,
:b_period_year,
:b_code_combination_id,
:b_account,
:b_ussgl_account,
:b_fund,
:b_fyr,
:b_cohort,
SUM(NVL(gll.entered_cr, 0)),
SUM(NVL(gll.entered_dr, 0)),'||
l_attribute_columns||
'''E'',
:b_curr_date,
:b_user_id,
:b_curr_date,
:b_user_id,
:b_request_id,
:b_treasury_symbol_id
FROM gl_je_lines gll,
gl_je_headers gjh,
xla_ae_lines xl ,
xla_distribution_links xdl,
gl_period_statuses gps,
fv_be_trx_dtls be,
gl_import_references glir
WHERE xl.code_combination_id = :b_code_combination_id
AND xl.ae_header_id = xdl.ae_header_id
AND xl.ae_line_num = xdl.ae_line_num
AND NVL(gjh.je_from_sla_flag, ''N'') = ''Y''
AND gjh.ledger_id = gps.ledger_id
AND gjh.status=''P''
AND gll.ledger_id = gjh.ledger_id
AND gll.je_header_id = gjh.je_header_id
AND gjh.actual_flag = ''A''
AND gps.application_id = 101
AND gps.ledger_id = :b_set_of_books_id
AND glir.je_header_id = gjh.je_header_id
AND glir.je_line_num = gll.je_line_num
AND glir.je_batch_id = gjh.je_batch_id
AND glir.gl_sl_link_id = xl.gl_sl_link_id
AND glir.gl_sl_link_table = xl.gl_sl_link_table
AND gjh.period_name = gps.period_name '||
l_where_columns ||'
AND gps.period_year = :b_period_year
AND nvl(xdl.SOURCE_DISTRIBUTION_ID_NUM_1,-99) = be.transaction_id (+)
AND be.set_of_books_id (+) = :b_set_of_books_id
GROUP BY '|| l_attr_group_columns||
' HAVING SUM(NVL(gll.entered_dr, 0))-SUM(NVL(gll.entered_cr, 0)) <> 0';
FOR 'SELECT glbal.code_combination_id,
NVL(glbal.begin_balance_dr,0)+NVL(glbal.period_net_dr,0),
NVL(glbal.begin_balance_cr,0)+NVL(glbal.period_net_cr,0),
glbal.period_num,
fa.public_law_code,
fa.advance_flag,
fa.transfer_flag,
fa.account,
fa.ussgl_account,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gcc.segment9,
gcc.segment10,
gcc.segment11,
gcc.segment12,
gcc.segment13,
gcc.segment14,
gcc.segment15,
gcc.segment16,
gcc.segment17,
gcc.segment18,
gcc.segment19,
gcc.segment20,
gcc.segment21,
gcc.segment22,
gcc.segment23,
gcc.segment24,
gcc.segment25,
gcc.segment26,
gcc.segment27,
gcc.segment28,
gcc.segment29,
gcc.segment30,'||
'gcc.'||p_bal_segment||'
FROM gl_balances glbal,
gl_code_combinations gcc,
fv_factsii_accounts_gt fa
WHERE glbal.ledger_id = :b_set_of_books_id
AND glbal.period_year = :b_period_year
AND glbal.period_num = :b_period_num
AND glbal.template_id IS NULL
AND glbal.actual_flag = ''A''
AND glbal.currency_code = :b_currency_code
AND gcc.code_combination_id = glbal.code_combination_id
AND gcc.'||p_acct_segment||' = fa.account
AND ((NVL(glbal.begin_balance_dr,0)+NVL(glbal.period_net_dr,0))-
(NVL(glbal.begin_balance_cr,0)+NVL(glbal.period_net_cr,0))) <> 0
order by gcc.'||p_bal_segment
USING p_ledger_id,
p_fiscal_year,
p_last_period_num,
'USD';
FOR previous_year_rec IN (SELECT *
FROM fv_factsii_ending_balances ffeb
WHERE ffeb.set_of_books_id = p_ledger_id
AND ffeb.fiscal_year = p_fiscal_year-1
AND ffeb.ccid = l_code_combination_id) LOOP
BEGIN
UPDATE fv_factsii_ending_balances ffeb
SET ffeb.ending_balance_cr = NVL(ffeb.ending_balance_cr, 0) + NVL(previous_year_rec.ending_balance_cr, 0),
ffeb.ending_balance_dr = NVL(ffeb.ending_balance_dr, 0) + NVL(previous_year_rec.ending_balance_dr, 0)
WHERE ffeb.set_of_books_id = previous_year_rec.set_of_books_id
AND ffeb.fiscal_year = p_fiscal_year
AND ffeb.ccid = previous_year_rec.ccid
AND ffeb.public_law = previous_year_rec.public_law
AND ffeb.advance_flag = previous_year_rec.advance_flag
AND ffeb.transfer_dept_id = previous_year_rec.transfer_dept_id
AND ffeb.transfer_main_acct = previous_year_rec.transfer_main_acct;
l_insert_required := FALSE;
l_insert_required := TRUE;
l_location := l_module_name||'update_fv_factsii_ending_balances';
IF (l_insert_required) THEN
BEGIN
INSERT INTO fv_factsii_ending_balances
(
set_of_books_id,
fiscal_year,
ccid,
account,
fund,
fyr,
cohort,
ussgl_account,
ending_balance_cr,
ending_balance_dr,
public_law,
advance_flag,
transfer_dept_id,
transfer_main_acct,
record_category,
creation_date,
created_by,
last_update_date,
last_update_by,
request_id,
treasury_symbol_id
)
VALUES
(
previous_year_rec.set_of_books_id,
p_fiscal_year,
previous_year_rec.ccid,
previous_year_rec.account,
previous_year_rec.fund,
previous_year_rec.fyr,
previous_year_rec.cohort,
previous_year_rec.ussgl_account,
previous_year_rec.ending_balance_cr,
previous_year_rec.ending_balance_dr,
previous_year_rec.public_law,
previous_year_rec.advance_flag,
previous_year_rec.transfer_dept_id,
previous_year_rec.transfer_main_acct,
'E',
l_system_date,
g_user_id,
l_system_date,
g_user_id,
g_request_id,
previous_year_rec.treasury_symbol_id
);
l_location := l_module_name||'insert_fv_factsii_ending_balances1';
SELECT COUNT (*)
INTO l_count_ccid
FROM fv_factsii_ending_balances ffeb
WHERE ffeb.set_of_books_id = p_ledger_id
AND ffeb.fiscal_year = p_fiscal_year
AND ccid = l_code_combination_id;
l_location := l_module_name||'select_fv_factsii_ending_balances';
INSERT INTO fv_factsii_ending_balances
(
set_of_books_id,
fiscal_year,
ccid,
account,
fund,
fyr,
cohort,
ussgl_account,
ending_balance_cr,
ending_balance_dr,
public_law,
advance_flag,
transfer_dept_id,
transfer_main_acct,
record_category,
creation_date,
created_by,
last_update_date,
last_update_by,
request_id,
treasury_symbol_id
)
SELECT p_ledger_id,
p_fiscal_year,
ccid,
l_account,
l_fund,
l_fyr,
l_cohort,
l_ussgl_account,
l_ending_balance_cr-SUM(ending_balance_cr),
l_ending_balance_dr-SUM(ending_balance_dr),
DECODE (l_public_law_code, 'Y', ' ', NULL),
DECODE (l_advance_flag, 'Y', 'X', NULL),
DECODE (l_transfer_flag, 'Y',' ',NULL),
DECODE (l_transfer_flag, 'Y',' ',NULL),
'D',
l_system_date,
g_user_id,
l_system_date,
g_user_id,
g_request_id,
l_treasury_symbol_id
FROM fv_factsii_ending_balances ffeb
WHERE ffeb.set_of_books_id = p_ledger_id
AND ffeb.fiscal_year = p_fiscal_year
AND ccid = l_code_combination_id
GROUP BY ccid
HAVING (((l_ending_balance_cr-SUM(ending_balance_cr)) <> 0) OR
((l_ending_balance_dr-SUM(ending_balance_dr)) <> 0)) AND
(l_ending_balance_cr-SUM(ending_balance_cr)) <> (l_ending_balance_dr-SUM(ending_balance_dr));
l_location := l_module_name||'INSERT INTO fv_factsii_ending_balances';
DELETE fv_factsii_ending_balances ffeb1
WHERE EXISTS (SELECT ffeb2.set_of_books_id,
ffeb2.ccid,
ffeb2.fiscal_year,
count(*)
FROM fv_factsii_ending_balances ffeb2
WHERE ffeb2.set_of_books_id = ffeb1.set_of_books_id
AND ffeb2.ccid = ffeb1.ccid
AND ffeb2.fiscal_year = ffeb1.fiscal_year
GROUP BY ffeb2.set_of_books_id,
ffeb2.ccid,
ffeb2.fiscal_year
HAVING count(*) = 1)
AND RTRIM(ffeb1.public_law) IS NULL
AND RTRIM(ffeb1.advance_flag) IS NULL
AND RTRIM(ffeb1.transfer_dept_id) IS NULL
AND RTRIM(ffeb1.transfer_main_acct) IS NULL;
l_location := l_module_name||'DELETE FROM fv_factsii_ending_balances1';