[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO l_facts1_eb_count
FROM fv_facts_ending_balances
WHERE fiscal_year = g_fiscal_year - 1;
SELECT count(*)
INTO l_facts2_eb_count
FROM fv_factsii_ending_balances
WHERE fiscal_year = g_fiscal_year - 1;
DELETE FROM fv_gtas_ending_balances;
l_statement := 'select
''SET_OF_BOOKS_ID, FISCAL_YEAR, CCID,FUND_VALUE, ACCOUNT_NUMBER,
D_C_INDICATOR,' ||
'AUTHORITY_TYPE_CODE, FED_NON_FED, TRADING_PARTNER_AGENCY_ID,' ||
'TRADING_PARTNER_MAIN_ACCOUNT, AMOUNT''
from dual';
l_statement := 'select
SET_OF_BOOKS_ID||'',''||FISCAL_YEAR||'',''||CCID||'',''||FUND_VALUE||'',''||
ACCOUNT_NUMBER||'',''||D_C_INDICATOR||'',''||AUTHORITY_TYPE_CODE||'',''||
FED_NON_FED||'',''||TRADING_PARTNER_AGENCY_ID||'',''||
TRADING_PARTNER_MAIN_ACCOUNT||'',''||AMOUNT
from fv_gtas_ending_balances
order by 1';
SELECT SUBSTR(parent_flex_value, 1, 6)
INTO p_gtas_sgl_acct_num
FROM fnd_flex_value_hierarchies
WHERE p_acct_num BETWEEN child_flex_value_low AND
child_flex_value_high
AND flex_value_set_id = g_acc_value_set_id
AND parent_flex_value <> 'T'
--AND SUBSTR(parent_flex_value,1,6) IN
and exists
(SELECT ussgl_account
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = SUBSTR(parent_flex_value, 1, 6))
AND parent_flex_value IN
(SELECT flex_value
FROM fnd_flex_values
WHERE flex_value_set_id = g_acc_value_set_id
AND enabled_flag = 'Y');
l_fednonfed_select VARCHAR2(200);
SELECT 'Y'
INTO p_code_exists
FROM fv_gtas_attributes
WHERE gtas_acct_number = p_account_number
AND set_of_books_id = g_sob_id
AND (fed_non_fed1 = p_fed_nonfed_type OR
fed_non_fed2 = p_fed_nonfed_type OR
fed_non_fed3 = p_fed_nonfed_type);
SELECT chart_of_accounts_id
INTO g_coa_id
FROM gl_ledgers_public_v
WHERE ledger_id = g_sob_id;
SELECT flex_value_set_id
INTO g_acc_value_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = g_acc_segment
AND id_flex_code = 'GL#'
AND id_flex_num = g_coa_id;
SELECT MAX(period_num)
INTO g_period_num_high
FROM gl_period_statuses
WHERE period_year = g_fiscal_year - 1
AND application_id = 101
AND closing_status <> 'F'
AND closing_status <> 'N'
AND ledger_id = g_sob_id;
SELECT DISTINCT account_number, set_of_books_id
FROM fv_gtas_fed_accounts fgfa
WHERE fiscal_year = g_fiscal_year
AND fed_non_fed_flag = 'Y'
AND EXISTS
(SELECT 1
FROM fv_facts_ending_balances ff1e
WHERE ff1e.set_of_books_id = fgfa.set_of_books_id
AND ff1e.account_number = fgfa.account_number
AND ff1e.fiscal_year = g_fiscal_year - 1)
ORDER BY set_of_books_id;
SELECT set_of_books_id,
fiscal_year,
ccid,
fund_value,
account_number,
d_c_indicator,
g_ng_indicator,
amount,
eliminations_dept
FROM fv_facts_ending_balances
WHERE set_of_books_id = v_sob_id
AND account_number = v_acct_number
AND fiscal_year = g_fiscal_year - 1;
SELECT gtas_acct_number
INTO l_temp_acct_num
FROM fv_gtas_attributes
WHERE set_of_books_id = fed_acct_rec.set_of_books_id
AND gtas_acct_number = fed_acct_rec.account_number;
SELECT ussgl_acct_number
INTO l_temp_acct_num
FROM fv_gtas_attributes
WHERE set_of_books_id = fed_acct_rec.set_of_books_id
AND gtas_acct_number = l_ussgl_acct_number;
SELECT aid
INTO l_aid
FROM fv_agency_id_map
--WHERE star_dept_reg = NVL(f1_eb_rec.eliminations_dept, '-ZZ')
WHERE star_dept_reg = NVL(SUBSTR(f1_eb_rec.eliminations_dept,1,2), '-ZZ')
AND rownum = 1;
INSERT INTO fv_gtas_ending_balances
(set_of_books_id,
fiscal_year,
ccid,
fund_value,
account_number,
d_c_indicator,
fed_non_fed,
trading_partner_agency_id,
trading_partner_main_account,
amount)
VALUES
(f1_eb_rec.set_of_books_id,
f1_eb_rec.fiscal_year,
f1_eb_rec.ccid,
f1_eb_rec.fund_value,
f1_eb_rec.account_number,
f1_eb_rec.d_c_indicator,
f1_eb_rec.g_ng_indicator,
l_aid,
l_main_account,
f1_eb_rec.amount);
'Inserted facts1 row: Account number: ' ||
f1_eb_rec.account_number || ' Fiscal Year: ' ||
f1_eb_rec.fiscal_year);
SELECT DISTINCT account_number,
set_of_books_id,
authority_type_flag,
fed_non_fed_flag
FROM fv_gtas_fed_accounts fgfa
WHERE fiscal_year = g_fiscal_year
AND (fed_non_fed_flag = 'Y' OR authority_type_flag = 'Y')
AND EXISTS((SELECT account
FROM fv_factsii_ending_balances ff2e
WHERE ff2e.set_of_books_id = fgfa.set_of_books_id
AND ff2e.account = fgfa.account_number
AND ff2e.fiscal_year = g_fiscal_year - 1)
MINUS
(SELECT account_number
FROM fv_facts_ending_balances ff1e
WHERE ff1e.set_of_books_id = fgfa.set_of_books_id
AND ff1e.account_number = fgfa.account_number
AND ff1e.fiscal_year = g_fiscal_year - 1));
SELECT set_of_books_id,
fiscal_year,
ccid,
fund,
account,
(CASE
WHEN (ending_balance_dr - ending_balance_cr) >= 0 THEN
'D'
ELSE
'C'
END) d_c_ind,
(ending_balance_dr - ending_balance_cr) amount,
DECODE(transfer_dept_id, NULL, NULL, 'F') fed_non_fed,
transfer_dept_id,
transfer_main_acct
FROM fv_factsii_ending_balances
WHERE set_of_books_id = v_sob_id
AND account = v_acct_number
AND fiscal_year = g_fiscal_year - 1;
SELECT aid
INTO l_aid
FROM fv_agency_id_map
WHERE star_dept_reg = f2_eb_rec.transfer_dept_id
AND rownum = 1;
SELECT authority_type
INTO l_authority_type_code
FROM fv_facts_attributes
WHERE set_of_books_id = fed_acct_rec.set_of_books_id
AND facts_acct_number = fed_acct_rec.account_number;
SELECT gtas_acct_number
INTO l_temp_acct_num
FROM fv_gtas_attributes
WHERE set_of_books_id = fed_acct_rec.set_of_books_id
AND gtas_acct_number = fed_acct_rec.account_number;
SELECT ussgl_acct_number
INTO l_temp_acct_num
FROM fv_gtas_attributes
WHERE set_of_books_id = fed_acct_rec.set_of_books_id
AND gtas_acct_number = l_ussgl_acct_number;
SELECT aid
INTO l_aid
FROM fv_agency_id_map
--WHERE star_dept_reg = NVL(f2_eb_rec.transfer_dept_id, '-ZZ')
WHERE star_dept_reg = NVL(SUBSTR(f2_eb_rec.transfer_dept_id,1,2), '-ZZ')
AND rownum = 1;
INSERT INTO fv_gtas_ending_balances
(set_of_books_id,
fiscal_year,
ccid,
fund_value,
account_number,
d_c_indicator,
authority_type_code,
fed_non_fed,
trading_partner_agency_id,
trading_partner_main_account,
amount)
VALUES
(f2_eb_rec.set_of_books_id,
f2_eb_rec.fiscal_year,
f2_eb_rec.ccid,
f2_eb_rec.fund,
f2_eb_rec.account,
f2_eb_rec.d_c_ind,
l_authority_type_code,
l_fed_non_fed,
l_aid,
l_main_account,
f2_eb_rec.amount);
'Inserted facts2 row: Account number: ' || f2_eb_rec.account ||
' Fiscal Year: ' || f2_eb_rec.fiscal_year);
SELECT DISTINCT account_number, sgl_account_number, set_of_books_id,
authority_type_flag
FROM fv_gtas_fed_accounts fgfa
WHERE fiscal_year = g_fiscal_year
AND (fgfa.fed_non_fed_flag = 'Y' OR
fgfa.authority_type_flag = 'Y')
AND NOT EXISTS((SELECT account
FROM fv_factsii_ending_balances ff2e
WHERE ff2e.set_of_books_id = fgfa.set_of_books_id
AND ff2e.account = fgfa.account_number
AND ff2e.fiscal_year = g_fiscal_year - 1) UNION ALL
(SELECT account_number
FROM fv_facts_ending_balances ff1e
WHERE ff1e.set_of_books_id = fgfa.set_of_books_id
AND ff1e.account_number = fgfa.account_number
AND ff1e.fiscal_year = g_fiscal_year - 1));
l_select VARCHAR2(1000);
l_select := 'SELECT glb.code_combination_id, glc.' || g_bal_segment ||
' , glc.' || g_acc_segment || ', SUM (begin_balance_dr - begin_balance_cr
+ NVL(period_net_dr,0) - NVL(period_net_cr,0)) end_bal
FROM gl_balances glb,gl_code_combinations GLC
WHERE glb.actual_flag = ' || '''' || 'A' || '''' || '
AND period_year = :g_fiscal_year
AND period_num = :g_period_num_high
AND glb.ledger_id = :g_set_of_books_id
AND glb.template_id is NULL
AND glb.currency_code <> ''STAT''
AND glc.code_combination_id = glb.code_combination_id
AND glc.' || g_acc_segment || '= ' || '''' ||
acct_rec.account_number || '''' ||
' GROUP BY glb.code_combination_id,' || 'glc.' ||
g_bal_segment || ', glc.' || g_acc_segment || ' HAVING SUM (begin_balance_dr - begin_balance_cr
+ NVL(period_net_dr,0) - NVL(period_net_cr,0)) <> 0
ORDER BY ' || 'glc.' || g_bal_segment || ', glc.' ||
g_acc_segment;
log(l_module, l_select);
OPEN gl_record FOR l_select
USING g_fiscal_year - 1, g_period_num_high, g_sob_id;
SELECT gtas_acct_number
INTO l_temp_acct_num
FROM fv_gtas_attributes
WHERE set_of_books_id = g_sob_id
AND gtas_acct_number = l_account_number;
SELECT ussgl_acct_number
INTO l_temp_acct_num
FROM fv_gtas_attributes
WHERE set_of_books_id = g_sob_id
AND gtas_acct_number = l_ussgl_acct_number;
SELECT COUNT(*)
INTO l_fednonfed_count
FROM (SELECT fed_non_fed1
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_temp_acct_num
AND fed_non_fed1 IS NOT NULL
AND set_of_books_id = g_sob_id
UNION
SELECT fed_non_fed2
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_temp_acct_num
AND fed_non_fed2 IS NOT NULL
AND set_of_books_id = g_sob_id
UNION
SELECT fed_non_fed3
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_temp_acct_num
AND fed_non_fed3 IS NOT NULL
AND set_of_books_id = g_sob_id);
SELECT authority_type
INTO l_authority_type_code
FROM fv_facts_attributes
WHERE set_of_books_id = acct_rec.set_of_books_id
AND facts_acct_number = acct_rec.account_number;
INSERT INTO fv_gtas_ending_balances
(set_of_books_id,
fiscal_year,
ccid,
fund_value,
account_number,
d_c_indicator,
fed_non_fed,
trading_partner_agency_id,
trading_partner_main_account,
amount,
authority_type_code)
VALUES
(g_sob_id,
g_fiscal_year - 1,
l_ccid,
l_fund_value,
l_account_number,
l_d_c_indicator,
l_fed_nonfed_code,
l_aid,
l_main_account,
l_amount,
l_authority_type_code);