[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(amount,0) amount, debit_credit,
sgl_acct_number
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL'
AND sgl_acct_number like '4%'
AND begin_end = 'E'
AND amount <> 0
ORDER BY sgl_acct_number;
SELECT ffa.ussgl_account,
ffa.total_resource_be_flag, ffa.total_resource_dc_flag,
ffa.resource_status_be_flag, ffa.resource_status_dc_flag
FROM fv_facts_ussgl_accounts ffa
WHERE ffa.ussgl_account like '4%'
AND EXISTS
(SELECT 'x'
FROM fv_facts_temp fft
WHERE fft.treasury_symbol_id = g_treasury_symbol_id
AND fft.sgl_acct_number = ffa.ussgl_account);
SELECT nvl(sum(amount),0)
INTO l_begin_bal
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL'
AND sgl_acct_number = l_ussgl_account
AND begin_end = 'B';
SELECT nvl(sum(amount),0)
INTO l_end_bal
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL'
AND sgl_acct_number = l_ussgl_account
AND begin_end = 'E';
SELECT nvl(amount,0) amount, debit_credit, sgl_acct_number
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL'
AND sgl_acct_number like '4%'
AND begin_end = 'B';
SELECT nvl(sum(fft.amount),0), fft.sgl_acct_number
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffa
WHERE treasury_symbol_id = g_treasury_symbol_id
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL'
AND ffa.ussgl_account = fft.sgl_acct_number
AND ffa.ye_anticipated_flag = 'Y'
AND fft.begin_end = 'E'
GROUP BY fft.sgl_acct_number;
SELECT fft.sgl_acct_number, nvl(amount,0) amount,
ffa.ye_resource_equity_flag
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffa
WHERE fft.treasury_symbol_id = g_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND ffa.ussgl_account = fft.sgl_acct_number
AND ffa.ye_resource_equity_flag in ('R','E')
AND fft.begin_end = ffa.ye_resource_equity_be_flag;
SELECT ffa.rt7_code_id,
ffc.rt7_code,
ffa.preclosing_unexpended_amt
FROM fv_facts_authorizations ffa,
fv_facts_rt7_codes ffc
WHERE ffa.treasury_symbol_id = g_treasury_symbol_id
AND ffa.rt7_code_id = ffc.rt7_code_id
AND ffa.set_of_books_id = p_ledger_id;
SELECT NVL(sum(fft.amount),0)
INTO l_accounts_balance
FROM fv_facts_temp fft,
fv_facts_rt7_accounts rta
WHERE rta.rt7_code_id = l_rt7_code_id
AND rta.rt7_ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = g_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND fft.begin_end = decode(rta.rt7_ussgl_account, '4139','B','4149','B','E');
SELECT nvl(fft.amount,0) amount, fft.sgl_acct_number
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffa
WHERE ffa.fund_balance_account_flag = 'Y'
AND fft.sgl_acct_number = ffa.ussgl_account
AND fft.treasury_symbol_id = g_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND fft.begin_end = 'E';
SELECT preclosing_unexpended_amt
INTO l_unexp_amount
FROM fv_preclosing_amounts
WHERE treasury_symbol_id = g_treasury_symbol_id;
SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0), NVL(fft.amount,0))), 0) obligations_incurred_s_amt
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffa
WHERE ffa.obligations_incurred_flag = 'Y'
AND ffa.ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = p_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL';
SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0), NVL(fft.amount,0))), 0) spndng_from_coll_and_pya_s_amt
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffa
WHERE ffa.spndng_from_coll_and_pya_flag = 'Y'
AND ffa.ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = p_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL';
SELECT SUM(NVL(amount,0)) obligations_as_of_10_1_amt
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffa
WHERE ffa.obligations_as_of_10_1_flag = 'Y'
AND ffa.ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = p_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND fft.begin_end = 'B';
SELECT NVL(SUM(DECODE(ffa.obligations_transferred_flag, 'Y', NVL(amount,0), 0)),0) obligations_transferred_amt,
NVL(SUM(DECODE(ffa.obligations_period_end_flag, 'Y', NVL(amount,0), 0)),0) obligations_period_end_amt
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffa
WHERE (
ffa.obligations_transferred_flag = 'Y' OR
ffa.obligations_period_end_flag = 'Y'
)
AND ffa.ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = p_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND fft.begin_end = 'E';
SELECT NVL(SUM(DECODE(ffa.disbursements_flag, 'Y', NVL(amount,0), 0)),0) disbursements_amt,
NVL(SUM(DECODE(ffa.collections_flag, 'Y', NVL(amount,0), 0)),0) collections_amt
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffa
WHERE (
ffa.disbursements_flag = 'Y' OR
ffa.collections_flag = 'Y'
)
AND ffa.ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = p_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND fft.begin_end = 'E'
AND ffa.edck12_balance_type IN ('E' ,'S');-- by ks for bug bug 5328107
SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0),0)), 0) beg_disbursements_amt
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffa
WHERE ffa.disbursements_flag = 'Y'
AND ffa.ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = p_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND ffa.edck12_balance_type = 'S';
SELECT NVL(SUM(DECODE(fft.begin_end, 'B', -1*NVL(fft.amount,0), 0)), 0) beg_collections_amt
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffa
WHERE ffa.collections_flag = 'Y'
AND ffa.ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = p_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND ffa.edck12_balance_type = 'S';
/*SELECT NVL(preclosing_subclass_amt, 0)
INTO l_subclass_amt
FROM fv_treasury_symbols
WHERE treasury_symbol_id = g_treasury_symbol_id
AND set_of_books_id = l_set_of_books_id;*/
SELECT NVL(PRECLOSING_SUBCLASS43_AMT, 0)
INTO l_subclass_amt
FROM FV_PRECLOSING_AMOUNTS
WHERE treasury_symbol_id = g_treasury_symbol_id
AND ledger_id = l_set_of_books_id;
SELECT NVL(sum(fft.amount),0)
INTO l_accounts_balance
FROM fv_facts_temp fft,
fv_facts_rt7_accounts rta,
fv_facts_rt7_codes rtc
WHERE rtc.factsII_edit_code = 'Subclass 43'
AND rta.rt7_code_id = rtc.rt7_code_id
AND rta.rt7_ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = g_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND fft.begin_end = rta.rt7_be_flag
AND DECODE(rta.rt7_transaction_partner,'N',fft.transaction_partner,
rta.rt7_transaction_partner) = fft.transaction_partner;
SELECT fft.sgl_acct_number,
SUM(NVL(fft.amount,0)) amount
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffacc
WHERE ffacc.ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = g_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND fft.begin_end = 'E'
AND ffacc.cancelled_flag = 'Y'
GROUP BY fft.sgl_acct_number;
SELECT ffacc.ussgl_account,
NVL(sum(fft.amount),0),
ffacc.ye_neg_receivables_flag,
ffacc.natural_balance
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffacc
WHERE (ffacc.ye_neg_receivables_flag = 'Y'
OR ffacc.ye_neg_payables_flag = 'Y')
AND ffacc.ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = g_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND fft.begin_end = 'E'
GROUP BY ffacc.ussgl_account, ffacc.ye_neg_receivables_flag, ffacc.natural_balance;
SELECT ffacc.ussgl_account, nvl(sum(fft.amount),0),
ffacc.natural_balance
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffacc
WHERE ffacc.ye_general_flag = 'Y'
AND ffacc.ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = g_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND fft.begin_end = 'E'
GROUP BY ffacc.ussgl_account, ffacc.natural_balance;
SELECT DECODE (l_neg_receivables_flag,'Y','NR','NP')
INTO l_acc_type
FROM DUAL;
update fv_facts_temp
set document_number = 'Y'
where sgl_acct_number = l_ussgl_account
and treasury_symbol_id = g_treasury_symbol_id
and fct_int_record_category = 'REPORTED_NEW'
and fct_int_record_type = 'BLK_DTL';
UPDATE fv_facts_submission
SET foot_note_flag = 'Y'
WHERE treasury_symbol_id = g_treasury_symbol_id;
update fv_facts_temp
set document_number = 'Y'
where sgl_acct_number = l_ussgl_account
and treasury_symbol_id = g_treasury_symbol_id
and fct_int_record_category = 'REPORTED_NEW'
and fct_int_record_type = 'BLK_DTL';
UPDATE fv_facts_submission
SET foot_note_flag = 'Y'
WHERE treasury_symbol_id = g_treasury_symbol_id;
UPDATE fv_facts_submission
SET foot_note_flag = 'N'
WHERE treasury_symbol_id = g_treasury_symbol_id;
SELECT fft.sgl_acct_number,
ffa.disbursements_flag,
ffa.collections_flag,
ffa.edck12_balance_type,
sum(nvl(amount,0)) amount
FROM fv_facts_temp fft,
fv_facts_ussgl_accounts ffa
WHERE (ffa.disbursements_flag = 'Y' or ffa.collections_flag = 'Y')
AND ffa.ussgl_account = fft.sgl_acct_number
AND fft.treasury_symbol_id = g_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND fft.begin_end = 'E'
group by
fft.sgl_acct_number,
ffa.disbursements_flag,
ffa.collections_flag,
ffa.edck12_balance_type;
select decode(p_facts_run_quarter,1,sf224_qtr1_outlay,
2,sf224_qtr2_outlay,3,sf224_qtr3_outlay,sf224_qtr4_outlay)
into l_224_outlays
from fv_treasury_symbols
where treasury_symbol_id = g_treasury_symbol_id;
SELECT sum(nvl(fft.amount,0))
INTO v_begin_amount
FROM fv_facts_temp fft
WHERE fft.sgl_acct_number = check12_rec.sgl_acct_number
AND fft.treasury_symbol_id = g_treasury_symbol_id
AND fft.fct_int_record_category = 'REPORTED_NEW'
AND fft.fct_int_record_type = 'BLK_DTL'
AND fft.begin_end = 'B';
l_facts_insert_flg VARCHAR2(1);
SELECT SUM(tmp.amount) amt,
clos.authority_code auth_type
FROM fv_facts_temp tmp,
fv_facts2_closing_validation clos
WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
and tmp.fct_int_record_category='REPORTED_NEW'
and tmp.fct_int_record_type='BLK_DTL'
AND tmp.sgl_acct_number = clos.ussgl_account
AND clos.closing_grp = p_closing_grp
AND clos.closing_acct_flag ='Y'
AND tmp.begin_end = 'B'
group by clos.authority_code;
SELECT bal.ending_bal,
bal.ussgl_account ,
bal.authority_type
FROM fv_facts2_retain_bal bal,
fv_facts2_closing_validation clos
WHERE bal.treasury_symbol_id = g_treasury_symbol_id
AND clos.closing_grp = p_closing_grp
AND bal.closing_grp = p_closing_grp
AND bal.period_year = l_prior_year
--AND bal.period_num = p_period_num
AND clos.ussgl_account = bal.ussgl_account
AND (clos.authority_code is null or bal.authority_type=clos.authority_code)
AND (clos.expired_unexpired IS NULL
OR clos.expired_unexpired = p_whether_exp);
SELECT closing_grp, ussgl_account
FROM fv_facts2_closing_validation
WHERE closing_acct_flag='Y'
ORDER BY closing_grp;
SELECT expiration_date
INTO exp_date
FROM fv_treasury_symbols
WHERE treasury_symbol_id = g_treasury_symbol_id;
SELECT start_date,
end_date
INTO beg_date,
close_date
FROM gl_period_statuses
WHERE period_year = g_period_year
AND period_num = g_period_num
AND application_id =101
AND set_of_books_id = g_ledger_id;
SELECT COUNT(*)
INTO l_temp_count
FROM fv_facts_temp tmp,
fv_facts2_closing_validation clos
WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
AND tmp.fct_int_record_category='REPORTED_NEW'
AND tmp.fct_int_record_type ='BLK_DTL'
AND tmp.sgl_acct_number = clos.ussgl_account
AND clos.closing_grp IS NOT NULL
AND clos.closing_acct_flag ='Y'
AND tmp.begin_end = 'B';
SELECT hard_edit_13_flag
INTO flg
FROM fv_facts2_Edit_params
WHERE set_of_books_id = g_ledger_id;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,' Is hard edit 13 flag on Federal financial options form selected ? '||flg);
l_facts_insert_flg:='N';
l_facts_insert_flg:='Y';
l_facts_insert_flg:='Y';
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,' l_facts_insert_flg :: Total Ending Balance -- '|| l_facts_insert_flg||
'::'||sum_ending_bal);
IF l_facts_insert_flg = 'Y' THEN
v_sgl_acct_number:=end_bal_rec.ussgl_account;
* check flag selected on Federal Financial options forms
*/
IF (sum_ending_bal <> l_beg_bal) THEN
l_edit_check_status:='N';
SELECT count(*) into l_has_data_count
FROM fv_facts_temp
WHERE treasury_symbol_id =g_treasury_symbol_id
AND edit_check_number=13
AND closing_grp =l_closing_gp;
SELECT nvl(amount,0) amt, facts.sgl_acct_number control_acct
FROM fv_facts_temp facts, fv_facts_ussgl_accounts uss
WHERE facts.treasury_symbol_id = g_treasury_symbol_id
AND uss.ussgl_account = facts.sgl_acct_number
AND uss.reclassification_ctrl_flag = 'Y' AND begin_end = 'E'
AND amount <> 0 and facts.fct_int_record_category = 'REPORTED_NEW'
AND facts.fct_int_record_type = 'BLK_DTL' ORDER BY sgl_acct_number;
SELECT nvl(amount,0) amt, facts.sgl_acct_number summation_acct
FROM fv_facts_temp facts, fv_facts_ussgl_accounts uss
WHERE facts.treasury_symbol_id = g_treasury_symbol_id
AND uss.ussgl_account = facts.sgl_acct_number
AND uss.reclassification_sum_acc_flag = 'Y' AND begin_end = 'E'
AND amount <> 0 and facts.fct_int_record_category = 'REPORTED_NEW'
AND facts.fct_int_record_type = 'BLK_DTL' ORDER BY sgl_acct_number;
select hard_edit_14_flag into flg from fv_facts2_Edit_params where set_of_books_id = g_ledger_id ;
SELECT nvl(amount,0) amt, sgl_acct_number acct
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND sgl_acct_number = '4157'
AND begin_end = 'E'
AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL';
SELECT nvl(amount,0) amt, sgl_acct_number acct
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND sgl_acct_number = '4397'
AND begin_end = 'E'
AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL';
SELECT nvl(amount,0) amt, sgl_acct_number acct
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND sgl_acct_number = '4158'
AND begin_end = 'E'
AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL';
SELECT nvl(amount,0) amt, sgl_acct_number acct
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND sgl_acct_number = '4398'
AND begin_end = 'E'
AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL';
SELECT nvl(amount,0) amt , sgl_acct_number
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND sgl_acct_number in (4871, 4971)
AND begin_end = 'E'
AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL' ORDER BY sgl_acct_number;
SELECT nvl(amount,0) amt , sgl_acct_number
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND sgl_acct_number in (4134, 4144)
AND begin_end = 'E'
AND amount <> 0 and fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL' ORDER BY sgl_acct_number;
SELECT nvl(amount,0) amt , sgl_acct_number, transaction_partner,
treasury_dept_code, treasury_acct_code, decode(fffa.non_fed_exc_flag,'Y', 'Allowed','Not Allowed')non_fed_exc_flag_desc
FROM fv_facts_temp facts, fv_facts_federal_accounts fffa, fv_treasury_symbols fts
WHERE facts.treasury_symbol_id = g_treasury_symbol_id
AND facts.treasury_symbol_id = fts.treasury_symbol_id
AND fts.federal_Acct_symbol_id = fffa.federal_Acct_symbol_id
AND ((transaction_partner = 'E'
AND (fffa.NON_FED_EXC_FLAG IS NULL OR fffa.NON_FED_EXC_FLAG ='N')
AND sgl_acct_number IN (4221, 4251, 4230, 4233, 4212))
OR (transaction_partner = 'X'
AND sgl_acct_number IN (4221, 4251, 4230, 4233)) )
AND begin_end = 'E'
AND amount <> 0
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL'
ORDER BY sgl_acct_number;
SELECT sum(amount) amt INTO v_amount1
FROM fv_facts_temp temp, fv_facts_ussgl_accounts ussgl
WHERE treasury_symbol_id = g_treasury_symbol_id
AND trim(bea_category) = 'D'
AND pya_flag = 'X'
AND temp.sgl_acct_number = ussgl.ussgl_account
AND ussgl.SF133_1700_1800 = 'Y'
AND (ussgl.BEG_END = 'E' OR ussgl.BEG_END ='EB')
AND (ussgl.UNEXP_EXP ='U' OR ussgl.UNEXP_EXP ='UE')
AND amount <> 0
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL';
SELECT sum(amount) amt INTO v_amount1
FROM fv_facts_temp temp, fv_facts_ussgl_accounts ussgl
WHERE treasury_symbol_id = g_treasury_symbol_id
AND trim(bea_category) = 'M'
AND pya_flag = 'X'
AND temp.sgl_acct_number = ussgl.ussgl_account
AND ussgl.SF133_1700_1800 = 'Y'
AND (ussgl.BEG_END = 'E' OR ussgl.BEG_END ='EB')
AND (ussgl.UNEXP_EXP ='U' OR ussgl.UNEXP_EXP ='UE')
AND amount <> 0
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL' ;
SELECT sum(amount) amt INTO v_amount1
FROM fv_facts_temp temp, fv_facts_ussgl_accounts ussgl
WHERE treasury_symbol_id = g_treasury_symbol_id
AND trim(REIMBURSEABLE_FLAG) = 'D'
AND pya_flag = 'X'
AND temp.sgl_acct_number = ussgl.ussgl_account
AND ussgl.SF133_2004_2104 = 'Y'
AND (ussgl.BEG_END = 'E' OR ussgl.BEG_END ='EB')
AND (ussgl.UNEXP_EXP ='UE' OR
(ussgl.UNEXP_EXP ='U' AND temp.APPOR_CAT_CODE IN('A','C','E')))
AND amount <> 0
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL';
SELECT sum(amount) amt INTO v_amount1
FROM fv_facts_temp temp, fv_facts_ussgl_accounts ussgl
WHERE treasury_symbol_id = g_treasury_symbol_id
AND trim(REIMBURSEABLE_FLAG) = 'R'
AND pya_flag = 'X'
AND temp.sgl_acct_number = ussgl.ussgl_account
AND ussgl.SF133_2004_2104 = 'Y'
AND (ussgl.BEG_END = 'E' OR ussgl.BEG_END ='EB')
AND (ussgl.UNEXP_EXP ='UE' OR
(ussgl.UNEXP_EXP ='U' AND temp.APPOR_CAT_CODE IN('A','C','E')))
AND amount <> 0
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL';
SELECT sgl_acct_number, amount
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL'
AND pya_flag = pya_val
AND begin_end = 'E'
AND amount <> 0
AND sgl_acct_number LIKE '4%'
ORDER BY sgl_acct_number;
SELECT sgl_acct_number, amount
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL'
AND pya_flag = pya_val
AND begin_end = 'E'
AND amount <> 0
AND sgl_acct_number NOT LIKE '4%'
ORDER BY sgl_acct_number;
SELECT SUM(nvl(tempbeg.amount,0)) begamt , SUM( nvl(tempend.amount,0)) endamt,
tempbeg.sgl_acct_number begsgl_acct_number, tempend.sgl_acct_number endsgl_acct_number
FROM (SELECT amount, sgl_acct_number
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL'
AND sgl_acct_number IN('4201','4139','4149')
AND begin_end = 'B') tempbeg FULL OUTER JOIN
(SELECT amount, sgl_acct_number
FROM fv_facts_temp
WHERE treasury_symbol_id = g_treasury_symbol_id
AND fct_int_record_category = 'REPORTED_NEW'
AND fct_int_record_type = 'BLK_DTL'
AND sgl_acct_number IN('4201','4139','4149')
AND begin_end = 'E') tempend
ON tempbeg.sgl_acct_number = tempend.sgl_acct_number
GROUP BY tempbeg.sgl_acct_number, tempend.sgl_acct_number
ORDER BY tempbeg.sgl_acct_number, tempend.sgl_acct_number;
SELECT to_number(to_char(cancellation_date,'YYYY')),sub_level_prefix_code
INTO l_cancel_date, l_subclass_prefix_code
FROM fv_treasury_symbols
WHERE treasury_symbol_id = g_treasury_symbol_id;
SELECT COUNT(*)
INTO no_rec
FROM fv_facts2_edit_params
WHERE set_of_books_id = p_ledger_id
AND period_year = p_rep_fiscal_yr
AND period_num = p_period_num;
INSERT INTO fv_facts_edit_check_status
(treasury_symbol_id,
edit_check_number,
edit_check_status)
VALUES (g_treasury_symbol_id,
p_edit_check_number,
p_edit_check_status) ;
Insert into FV_FACTS_TEMP
(FCT_INT_RECORD_CATEGORY ,
TREASURY_SYMBOL_ID ,
TBAL_ACCT_NUM ,
FACTS_REPORT_INFO ,
edit_check_number ,
amount ,
amount1 ,
amount2 ,
sgl_acct_number ,
budget_function,
closing_grp ,
SGL_BEG_BAL_ACCT_NUM,
TRANSACTION_PARTNER,
TRANSFER_DEPT_ID,
TRANSFER_MAIN_ACCT,
NON_FED_EXC_FLAG
)
Values
('FACTS2_EDIT_CHECK_LOG' ,
g_treasury_symbol_id ,
v_log_counter ,
text ,
v_edit_check_number ,
v_amount ,
v_amount1 ,
v_amount2 ,
v_sgl_acct_number ,
v_dummy_var ,
v_closing_grp ,
v_beg_bal_sggl_acc,
v_transaction_partner,
v_aid,
v_main,
v_non_fed_exc_flag
) ;
SELECT tmp.amount ,
tmp.sgl_acct_number ,
tmp.treasury_symbol_id,
att.authority_type,
clos.closing_grp
FROM fv_facts_temp tmp,
fv_facts_attributes att,
fv_facts2_closing_validation clos
WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
AND tmp.begin_end = 'E'
AND tmp.fct_int_record_category='REPORTED_NEW'
AND tmp.fct_int_record_type='BLK_DTL'
AND tmp.sgl_acct_number = att.ussgl_acct_number
AND clos.ussgl_Account = att.ussgl_acct_number
AND att.authority_type = tmp.authority_type
AND att.set_of_books_id = g_ledger_id
AND clos.closing_grp IS NOT NULL
AND (clos.authority_code is null or tmp.authority_type = clos.authority_code) ;
SELECT tmp.amount ,
tmp.sgl_acct_number ,
tmp.treasury_symbol_id,
att.authority_type,
clos.closing_grp
FROM fv_facts_temp tmp,
fv_facts_attributes att,
fv_facts2_closing_validation clos
WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
AND tmp.begin_end = 'E'
AND tmp.fct_int_record_category='REPORTED_NEW'
AND tmp.fct_int_record_type='BLK_DTL'
AND tmp.sgl_acct_number = att.facts_acct_number
AND clos.ussgl_Account = att.facts_acct_number
AND att.set_of_books_id = g_ledger_id
AND clos.closing_grp IS NOT NULL
AND (nvl(clos.authority_code,'N')='N' or tmp.authority_type = clos.authority_code)
AND (nvl(att.authority_type,'N')='N' or att.authority_type = tmp.authority_type);
SELECT tmp.amount ,
tmp.sgl_acct_number ,
tmp.treasury_symbol_id,
tmp.authority_type,
clos.closing_grp
FROM fv_facts_temp tmp,
fv_facts2_closing_validation clos
WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
AND tmp.begin_end = 'E'
AND tmp.fct_int_record_category='REPORTED_NEW'
AND tmp.fct_int_record_type='BLK_DTL'
and clos.ussgl_Account = tmp.sgl_acct_number
-- Added by Vijay
AND ( nvl(tmp.authority_type,'N')='N' or nvl(clos.authority_code,'N')='N'
or tmp.authority_type=clos.authority_code)
AND clos.closing_grp IS NOT NULL;
SELECT COUNT(*)
INTO l_rec_exists
FROM fv_Facts2_edit_params
WHERE set_of_books_id = g_ledger_id
AND period_year = g_period_year
AND period_num = g_period_num;
/*select period_name into l_period_name from gl_period_statuses
where period_name = p_rep_fiscal_yr and period_num = p_period_num
and application_id=101 and set_of_books_id = p_ledger_id;*/
DELETE
FROM FV_FACTS2_RETAIN_BAL
WHERE treasury_symbol_id = g_treasury_symbol_id
AND period_year <> g_period_year-1;
/* SELECT COUNT(*)
INTO l_count
FROM fv_facts_temp tmp,
fv_facts_attributes att,
fv_facts2_closing_validation clos
WHERE tmp.treasury_symbol_id = g_treasury_symbol_id
AND tmp.begin_end = 'E'
AND tmp.sgl_acct_number = att.ussgl_acct_number
AND tmp.sgl_acct_number = clos.ussgl_Account
AND clos.closing_grp IS NOT NULL;*/
INSERT
INTO FV_FACTS2_RETAIN_BAL
(
USSGL_ACCOUNT ,
TREASURY_SYMBOL_ID,
AUTHORITY_TYPE ,
ENDING_BAL ,
period_num,
closing_grp,
period_year,
LAST_UPDATE_DATE ,
CREATION_DATE ,
CREATED_BY ,
last_updated_by
)
VALUES
(
l_ussgl_account,
l_treasury_symb,
l_auth_type ,
l_end_bal ,
g_period_num ,
l_closing_grp,
g_period_year ,
sysdate ,
sysdate ,
l_user_id ,
l_user_id
);