[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_fed_accounts(p_set_of_books_id IN VARCHAR2,
p_period_year IN VARCHAR2);
PROCEDURE update_gtas_run(p_period_year in VARCHAR2,
p_set_of_books_id in VARCHAR2);
SELECT period_year
INTO gbl_period_year
FROM gl_period_statuses p
WHERE p.application_id = 101
AND p.ledger_id = gbl_set_of_books_id
AND p.period_name = gbl_period_name;
SELECT MIN(period_num)
INTO gbl_period_num_low
FROM gl_period_statuses
WHERE period_year = gbl_period_year
AND application_id = 101
AND closing_status <> 'F'
AND closing_status <> 'N'
AND adjustment_period_flag = 'N'
AND ledger_id = gbl_set_of_books_id;
SELECT period_num,
CASE
WHEN to_char(end_date, 'MM')between 1 and 9 then (to_char(end_date, 'MM')+3)
WHEN to_char(end_date, 'MM')between 10 and 12 then (to_char(end_date, 'MM')-9)
END
INTO gbl_period_num_high, gbl_reporting_period
FROM gl_period_statuses p
WHERE period_name = gbl_period_name
AND p.application_id = 101
AND p.ledger_id = gbl_set_of_books_id
AND p.period_year = gbl_period_year;
SELECT count(*)
Into L_Row_Exists
FROM fv_gtas_ending_balances
WHERE fiscal_year = gbl_period_year
AND set_of_books_id = gbl_set_of_books_id
AND rownum = 1;
DELETE FROM fv_gtas_ending_balances
WHERE set_of_books_id = gbl_set_of_books_id
AND fiscal_year = gbl_period_year;
log(l_module_name, 'Deleted '||SQL%ROWCOUNT ||
' recs from fv_gtas_ending_balances.');
UPDATE fv_gtas_run
SET status = 'Y',
run_fed_flag = 'I',
process_date = sysdate,
begin_bal_diff_flag = 'Y',
period_num = gbl_period_num_high
WHERE set_of_books_id = gbl_set_of_books_id
AND fiscal_year = gbl_fiscal_year
AND table_indicator = 'N';
SELECT chart_of_accounts_id
INTO gbl_coa_id
FROM gl_ledgers_public_v
WHERE ledger_id = gbl_set_of_books_id;
SELECT flex_value_set_id
INTO gbl_acc_value_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = gbl_acc_segment_name
AND id_flex_code = 'GL#'
AND id_flex_num = gbl_coa_id;
'Inserting a record in T2 for record_category :'||
p_record_category||' for ccid: '||p_ccid);
INSERT INTO fv_gtas1_report_t2
(account_number ,
amount ,
d_c_indicator ,
fiscal_year ,
record_category ,
ussgl_account ,
set_of_books_id ,
reported_status ,
exch_non_exch ,
cust_non_cust ,
fund_value ,
beginning_balance ,
ccid ,
account_type ,
dr_amount ,
cr_amount ,
DIRECT_OR_REIMB_CODE ,
APPOR_CAT_CODE ,
APPOR_CAT_B_CODE ,
PROGRAM_REPT_CODE ,
BEA_CAT_CODE ,
BORR_SRC_CODE ,
NEW_BAL_CODE ,
CUR_SUBSEQUENT_CODE ,
PYA_CODE ,
CREDIT_COHORT_YR ,
PROGRAM_COST_IND ,
TREASURY_SYMBOL_ID )
VALUES
( p_account_number,
nvl(p_amount, 0),
DECODE(SIGN(nvl(p_amount, 0)), 0 ,'D', 1, 'D', -1, 'C'),
p_fiscal_year,
p_record_category,
p_ussgl_account,
p_set_of_books_id,
'E',--p_reported_status,
p_exch_non_exch,
p_cust_non_cust,
p_fund_value,
p_beginning_bal,
p_ccid,
p_account_type,
p_dr_amount,
p_cr_amount,
p_DIRECT_OR_REIMB_CODE ,
p_APPOR_CAT_CODE ,
p_APPOR_CAT_B_CODE ,
p_PROGRAM_REPT_CODE ,
p_BEA_CAT_CODE ,
p_BORR_SRC_CODE ,
p_NEW_BAL_CODE ,
p_CUR_SUBSEQUENT_CODE ,
p_PYA_CODE ,
p_CREDIT_COHORT_YR ,
p_PROGRAM_COST_IND ,
p_TREASURY_SYMBOL_ID );
select_stmt VARCHAR2(10000);
Select fp.treasury_symbol_id, fp.Outlays_Bea_Category_Code,
fp.Bea_Category, fp.Fund_Category,
fp.Cust_Non_Cust, fp.Borrowing_Source_Code,
fp.Direct_Or_Reimb_Code, tas.financing_account
into l_treasury_symbol_id, l_Outlays_Bea_Category_Code,
l_Bea_Category, l_appor_cat,
l_c_nc, l_bsc,
l_drc, l_financing_account
From Fv_Fund_Parameters fp,
fv_treasury_symbols tas
Where fp.Fund_Value = P_Fund_Value
AND fp.set_of_books_id = gbl_set_of_books_id
AND fp.treasury_symbol_id = tas.treasury_symbol_id;
SELECT SUBSTR(parent_flex_value,1,6)
INTO l_ussgl_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 = gbl_acc_value_set_id
AND parent_flex_value <> 'T'
AND SUBSTR(parent_flex_value,1,6) IN
(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 = gbl_acc_value_set_id
AND enabled_flag = 'Y'
);
Select count(*) into cnt From Fv_Gtas_Attributes Where
GTAS_ACCT_NUMBER = l_Acct_Num;
select decode(BALANCE_TYPE, 'BE','S',BALANCE_TYPE )
into p_end_bal_ind
from FV_GTAS_ATTRIBUTES
Where GTAS_ACCT_NUMBER = l_Acct_Num
AND set_of_books_id = gbl_set_of_books_id;
SELECT gtas_acct_number
INTO l_temp_acct_num
FROM fv_gtas_attributes
WHERE gtas_acct_number = p_acct_num;
Select Exch_Non_Exch1, Exch_Non_Exch2, Exch_Non_Exch3,
Pya_Code1,
Cust_Non_Cust1, Cust_Non_Cust2, borrowing_source_flag,
direct_or_reimb_flag,
Apportionment_Category1, Apportionment_Category2, Apportionment_Category3,
appor_cat_b_prog_flag,
prog_rep_cat_flag,
bea_category_flag,
Bea_Category_Acct_Type,
program_cost_ind_flag,
new_or_bal_flag,
Cur_Sub_Code1
Into L_Exch_Non_Exch1, L_Exch_Non_Exch2, L_Exch_Non_Exch3,
L_Pya_Code1,
L_Cust_Non_Cust1, L_Cust_Non_Cust2,
l_borrowing_source_flag,
l_dir_or_reimb_flag,
L_Apportionment_Category1,L_Apportionment_Category2, L_Apportionment_Category3,
l_appor_cat_b_prog_flag,
l_prog_rep_cat_flag,
l_bea_category_flag,
L_Bea_Category_Acct_Type,
l_program_cost_ind_flag,
l_new_or_bal_flag,
l_Cur_Sub_Code1
From Fv_Gtas_Attributes
--WHERE GTAS_ACCT_NUMBER = l_Acct_Num
WHERE GTAS_ACCT_NUMBER = l_temp_acct_num
AND set_of_books_id = gbl_set_of_books_id;
SELECT cohort_segment_name
INTO l_cohort_seg_name
FROM fv_pya_fiscalyear_segment
Where Set_Of_Books_Id = Gbl_Set_Of_Books_Id;
Select_Stmt := 'SELECT ' || l_cohort_seg_name ||
' from gl_code_combinations where code_combination_id=' || p_ccid;
Dbms_Sql.Parse(vl_cursor,Select_Stmt, Dbms_Sql.V7);
SELECT fyr_segment_value
INTO vl_fyr_segment_value
FROM fv_pya_fiscalyear_map
Where Period_Year = gbl_period_year
AND set_of_books_id = gbl_set_of_books_id;
SELECT fund_value,
fund_category,
treasury_symbol_id
From Fv_Fund_Parameters
WHERE fund_value = p_fund_value
AND set_of_books_id = p_sob_id;
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
From Fv_Facts_Prc_Hdr Ffh
WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
AND ffh.code_type = vl_code_type
AND ffh.set_of_books_id = p_sob_id
AND ffh.fund_value = p_fund_value;
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
AND ffh.code_type = vl_code_type
And Ffh.Set_Of_Books_Id = P_Sob_Id
AND ffh.fund_value = 'ALL-A';
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
AND ffh.code_type = vl_code_type
And Ffh.Set_Of_Books_Id = P_Sob_Id
AND ffh.fund_value = 'ALL-B';
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
AND ffh.code_type = vl_code_type
AND ffh.set_of_books_id = p_sob_id
AND ffh.fund_value = 'ALL-FUNDS';
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = -1
AND ffh.code_type = vl_code_type
And Ffh.Set_Of_Books_Id = P_Sob_Id
AND ffh.fund_value = 'ALL-A';
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = -1
AND ffh.code_type = vl_code_type
And Ffh.Set_Of_Books_Id = P_Sob_Id
AND ffh.fund_value = 'ALL-B';
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = -1
AND ffh.set_of_books_id = p_sob_id
AND ffh.code_type = vl_code_type
AND ffh.fund_value = 'ALL-FUNDS';
Select_Stmt varchar2(100);
Select_Stmt := 'SELECT ' || p_Segment_Name ||
' from gl_code_combinations where code_combination_id=' || p_Ccid;
log(l_module_name, 'Select_Stmt:'||Select_Stmt);
EXECUTE IMMEDIATE Select_Stmt INTO l_prg_val;
SELECT reporting_code
INTO p_prc_val
From Fv_Facts_Prc_Dtl
WHERE prc_header_id = P_Prc_Header_Id
And Program_Value = l_prg_val
And Set_Of_Books_Id = Gbl_Set_Of_Books_Id;
SELECT reporting_code
INTO p_prc_val
FROM fv_facts_prc_dtl
WHERE prc_header_id = P_Prc_Header_Id
And Program_Value = 'ALL'
And Set_Of_Books_Id = Gbl_Set_Of_Books_Id;
SELECT SUBSTR(compiled_value_attributes, 5, 1)
INTO l_account_type
FROM fnd_flex_values
WHERE flex_value = p_account_number
AND flex_value_set_id = gbl_acc_value_set_id;
SELECT ussgl_enabled_flag
INTO p_enabled_flag
FROM fv_facts_ussgl_accounts
Where Ussgl_Account = P_Ussgl_Acct_Num;
Insert Into Fv_Gtas_Ending_Balances
(Ccid,
Set_Of_Books_Id,
Fund_Value,
Account_Number,
Authority_Type_Code,
Fed_Non_Fed,
Trading_Partner_Agency_Id,
Trading_Partner_Main_Account,
Budget_Impact_Ind,
Fiscal_Year,
D_C_INDICATOR,
AMOUNT,
end_bal_ind)
(SELECT /*+ PARALLEL(T2) */
T2.Ccid,
Gbl_Set_Of_Books_Id,
t2.fund_value,
T2.Account_Number,
T2.Authority_Type_Code,
T2.Fed_Non_Fed,
T2.Trading_Partner_Agency_Id,
T2.Trading_Partner_Main_Account,
t2.Budget_Impact_Ind,
Gbl_Fiscal_Year,
T2.D_C_Indicator,
Sum(Nvl(Amount,0)),
'Y'
FROM fv_gtas1_period_balances_v t2
WHERE t2.set_of_books_id = gbl_set_of_books_id
And Period_Year = Gbl_Fiscal_Year
and period_num <= gbl_period_num_high
AND t2.begin_end = 'E'
AND t2.balance_type IN ('P','L')
AND nvl(t2.amount,0) <> 0
group by T2.Ccid, Gbl_Set_Of_Books_Id, t2.fund_value, T2.Account_Number,
T2.Authority_Type_Code, T2.Fed_Non_Fed, T2.Trading_Partner_Agency_Id,
T2.Trading_Partner_Main_Account, t2.Budget_Impact_Ind,
Gbl_Fiscal_Year, T2.D_C_Indicator
HAVING SUM(NVL(amount,0)) <> 0) ;
log(l_module_name, 'Inserted '||SQL%ROWCOUNT ||' recs into fv_gtas_ending_bal');
DELETE FROM fv_gtas1_report_t2
WHERE set_of_books_id = gbl_set_of_books_id;
DELETE FROM fv_gtas_diff_balances
WHERE set_of_books_id = gbl_set_of_books_id
and period_year = gbl_period_year
and balance_type IN ('B', 'D');
log(l_module_name, 'Deleted '||SQL%ROWCOUNT||
' records from fv_gtas_diff_balances.');
UPDATE fv_gtas1_period_attributes
SET reported_group = NULL
WHERE set_of_books_id = gbl_set_of_books_id
AND period_year = gbl_period_year;
l_select_stmt VARCHAR2(10000);
l_select_stmt2 VARCHAR2(10000);
select decode(period_num,null,'Y',0,'Y','N'),period_num into
l_populate_flag, l_int_run_month
from fv_gtas_run
WHERE set_of_books_id = gbl_set_of_books_id
AND fiscal_year = p_period_year;
DELETE FROM fv_gtas1_report_t2
WHERE set_of_books_id = gbl_set_of_books_id;
l_select_stmt2 := ' glb.code_combination_id, ' ||
' glc.' || Gbl_Bal_Segment_Name || ' , glc.' || Gbl_Acc_Segment_Name ||
', ''NO'', ''#'', ''#'', ''E'', ''#'',''N'', ''#'', ''#'', ''#'', ''#'', ''#'', ''#'',
''#'', ''#'', ''#'', ''#'', ''#'', -99, ''#'',
SUM (DECODE (period_num, :gbl_period_num_high,
(begin_balance_dr - begin_balance_cr + NVL(period_net_dr,0)
- NVL(period_net_cr,0)),0)) curr_year_bal,
SUM (DECODE (period_num, :gbl_period_num_low,
(begin_balance_dr - begin_balance_cr),0)) begin_bal,
SUM (DECODE (period_num, :gbl_period_num_high,
(NVL(period_net_dr,0)),0)) period_cy_bal,
SUM (DECODE (period_num, :gbl_period_num_high,
(NVL(period_net_cr,0)),0)) period_cy_cr_bal,
SUM (DECODE (period_num, :gbl_period_num_high,
(begin_balance_dr - begin_balance_cr),0)) period_begin_bal '||
' FROM gl_balances glb,gl_code_combinations GLC
WHERE glb.actual_flag = '||''''||'A'||''''||'
AND period_year = :gbl_fiscal_year
AND period_num IN (:gbl_period_num_low, :gbl_period_num_high)
AND glb.ledger_id = :gbl_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.'||gbl_bal_segment_name||' in
(select ffp.fund_value
from fv_fund_parameters ffp,
fv_treasury_symbols fts
where fts.treasury_symbol_id = ffp.treasury_symbol_id
and fts.set_of_books_id = '||gbl_set_of_books_id||
' and fts.gtas_reportable_indicator = ''Y'' '
||l_aid_condition||l_main_account_condition
||') GROUP BY glb.code_combination_id ,'||'glc.'||gbl_bal_segment_name
||', glc.' || gbl_acc_segment_name
||' ORDER BY '||'glc.'||gbl_bal_segment_name ||', glc.' || gbl_acc_segment_name;
l_select_stmt2 := ' SELECT ' || l_select_stmt2;
log(l_module_name, l_select_stmt2);
select NVL(begin_bal_diff_flag , 'N') into l_diff_flag
from fv_gtas_run
where set_of_books_id = gbl_set_of_books_id
and fiscal_year = gbl_fiscal_year;
DELETE FROM fv_gtas_diff_balances
WHERE set_of_books_id = gbl_set_of_books_id
and period_year = gbl_fiscal_year
and balance_type = 'B';
OPEN t1_record_c for l_select_stmt2 USING
l_period_num_high,
l_period_num_low,
l_period_num_high, l_period_num_high, l_period_num_high,
gbl_fiscal_year, l_period_num_low, l_period_num_high,gbl_set_of_books_id;
select 'N' into l_new_record_l(i)
from fv_gtas1_period_attributes
where ccid = l_ccid_l(i)
and period_year = gbl_fiscal_year
and set_of_books_id = gbl_set_of_books_id;
SELECT '2'
INTO l_exception_status_l(i)
FROM fv_gtas_fed_accounts
WHERE set_of_books_id = gbl_set_of_books_id
AND account_number = l_account_number
AND fiscal_year = gbl_fiscal_year;
SELECT NVL(SUM(NVL(t2.amount, 0)), 0)
INTO l_t2_detail_amount
FROM fv_gtas_activity_balances t2
WHERE t2.ccid = l_ccid_l(i)
AND t2.set_of_books_id = gbl_set_of_books_id
AND period_num <= gbl_period_num_high
AND period_year = gbl_fiscal_year;
log(l_module_name, '*****inserting detail difference record');
'Inserting into fv_gtas_diff_balances values: ');
INSERT INTO fv_gtas_diff_balances
(
ccid,period_num,period_year,set_of_books_id,
amount,
d_c_indicator,
balance_type,
account_number,
fund_value)
VALUES
(l_ccid_l(i),gbl_period_num_high,gbl_fiscal_year,gbl_set_of_books_id,
(l_curr_year_balance - l_t2_detail_amount),
DECODE(SIGN(l_curr_year_balance - l_t2_detail_amount),
0, 'D', 1, 'D', -1, 'C'),
'D', l_account_number, l_fund_value);
SELECT NVL(SUM(amount), 0)
Into L_Ending_Amount
FROM fv_gtas_ending_balances
WHERE ccid = l_ccid_l(i)
AND set_of_books_id = gbl_set_of_books_id
AND fiscal_year = (gbl_fiscal_year - 1)
AND record_category = 'ENDING_BAL'
AND account_number = l_account_number
And Fund_Value = L_Fund_Value
AND account_type IN ('A','L','O');
log(l_module_name, '*****inserting end bal difference record');
'Inserting into fv_gtas_diff_balances values: ');
INSERT INTO fv_gtas_diff_balances
(ccid,period_num,period_year,set_of_books_id,
amount,
d_c_indicator,
balance_type,
account_number,
fund_value)
VALUES
(l_ccid_l(i),gbl_period_num_low,gbl_fiscal_year,gbl_set_of_books_id,
l_begin_bal_l(i) - l_ending_amount,
DECODE(SIGN(l_begin_bal_l(i) - l_ending_amount), 0, 'D', 1, 'D', -1, 'C'),
'B', l_account_number, l_fund_value);
log(l_module_name, 'afterinsert fv_Gtas_diff');
log(l_module_name, 'Inserting no of new records ' || l_indx);
INSERT INTO fv_gtas1_period_attributes
( period_year,
period_num,
period_name,
set_of_books_id,
ccid,
fund_value,
account_number,
ussgl_account,
exch_non_exch ,
cust_non_cust,
account_type ,
reported_group,
new_rec_flag,
BALANCE_AMOUNT,
BEGIN_BALANCE,
PERIOD_CY_DR_BAL,
PERIOD_CY_CR_BAL ,
PERIOD_BEGIN_BAL,
DIRECT_OR_REIMB_CODE ,
APPOR_CAT_CODE ,
APPOR_CAT_B_CODE ,
PROGRAM_REPT_CODE ,
BEA_CAT_CODE,
BORR_SRC_CODE,
NEW_BAL_CODE,
CUR_SUBSEQUENT_CODE,
PYA_CODE,
CREDIT_COHORT_YR,
PROGRAM_COST_IND,
treasury_symbol_id,
end_bal_ind
)
values (
gbl_fiscal_year,
l_period_num_high,
gbl_period_name,
gbl_set_of_books_id,
l_ccid_n(i),
l_fund_value_n(i),
l_account_number_n(i),
l_sgl_acct_num_n(i),
l_exch_non_exch_n(i),
l_cust_non_cust_n(i),
l_account_type_n(i),
l_exception_status_n(i),
'Y',
l_balance_amoun_n(i),
l_begin_bal_n(i),
l_cy_dr_bal_n(i),
l_cy_cr_bal_n(i),
l_per_begin_bal_n(i),
l_DIRECT_OR_REIMB_CODE_n(i),
l_APPOR_CAT_CODE_n(i) ,
l_APPOR_CAT_B_CODE_n(i) ,
l_PROGRAM_REPT_CODE_n(i) ,
l_BEA_CAT_CODE_n(i),
l_BORR_SRC_CODE_n(i),
l_NEW_BAL_CODE_n(i),
l_CUR_SUBSEQUENT_CODE_n(i),
l_PYA_CODE_n(i),
l_CREDIT_COHORT_YR_n(i),
l_PROGRAM_COST_IND_n(i),
l_treasury_symbol_id_n(i),
l_end_bal_ind_n(i)
);
UPDATE fv_gtas1_period_attributes
SET ussgl_account = l_sgl_acct_num_l(i),
exch_non_exch = l_exch_non_exch_l(i),
cust_non_cust = l_cust_non_cust_l(i),
account_type = l_account_type_l(i),
reported_group = l_exception_status_l(i),
BALANCE_AMOUNT = l_balance_amoun_l(i),
BEGIN_BALANCE = l_begin_bal_l(i),
PERIOD_CY_DR_BAL = l_cy_dr_bal_l(i),
PERIOD_CY_CR_BAL = l_cy_cr_bal_l(i),
PERIOD_BEGIN_BAL = l_per_begin_bal_l(i),
period_num = l_period_num_high,
period_name = gbl_period_name,
end_bal_ind = l_end_bal_ind_l(i),
DIRECT_OR_REIMB_CODE = l_DIRECT_OR_REIMB_CODE_l(i),
APPOR_CAT_B_CODE = l_APPOR_CAT_B_CODE_l(i),
APPOR_CAT_CODE= l_APPOR_CAT_CODE_l(i) ,
PROGRAM_REPT_CODE = l_PROGRAM_REPT_CODE_l(i) ,
BEA_CAT_CODE = l_BEA_CAT_CODE_l(i),
BORR_SRC_CODE = l_BORR_SRC_CODE_l(i),
NEW_BAL_CODE = l_NEW_BAL_CODE_l(i),
CUR_SUBSEQUENT_CODE = l_CUR_SUBSEQUENT_CODE_l(i),
PYA_CODE = l_PYA_CODE_l(i),
CREDIT_COHORT_YR = l_CREDIT_COHORT_YR_l(i),
PROGRAM_COST_IND = l_PROGRAM_COST_IND_l(i),
treasury_symbol_id = l_treasury_symbol_id_l(i)
WHERE ccid = l_ccid_l(i)
and period_year = gbl_fiscal_year
and set_of_books_id = gbl_set_of_books_id
and l_new_record_l(i) = 'N';
SELECT COUNT(*)
INTO l_exception_count
FROM fv_gtas1_report_t2
WHERE set_of_books_id = gbl_set_of_books_id
AND reported_status = 'E'
AND amount <> 0 ;
UPDATE fv_gtas_run
SET status = l_run_status,
process_date = sysdate,
run_fed_flag = 'I',
begin_bal_diff_flag = 'Y',
period_num = l_period_num_high
WHERE set_of_books_id = gbl_set_of_books_id
AND fiscal_year = p_period_year
AND table_indicator = 'N';
SELECT count(*)
INTO l_exception_count
FROM fv_gtas1_report_t2
WHERE set_of_books_id = gbl_set_of_books_id
AND fiscal_year = gbl_fiscal_year;
SELECT gtas_acct_number, fed_non_fed1, fed_non_fed2, fed_non_fed3,
authority_type1, authority_type2, authority_type3,
authority_type4, authority_type5, authority_type6,
bud_impact_ind1, bud_impact_ind2, trading_partner_flag
FROM fv_gtas_attributes
WHERE set_of_books_id = p_sob_id
AND (
(fed_non_fed1 IS NOT NULL OR fed_non_fed2 IS NOT NULL OR fed_non_fed3 IS NOT NULL)
OR
(authority_type1 IS NOT NULL OR authority_type2 IS NOT NULL OR authority_type3 IS NOT NULL OR
authority_type4 IS NOT NULL OR authority_type5 IS NOT NULL OR authority_type6 IS NOT NULL)
OR
(bud_impact_ind1 IS NOT NULL OR bud_impact_ind2 IS NOT NULL)
)
ORDER BY 1 ;
SELECT child_flex_value_low, child_flex_value_high
FROM fnd_flex_value_hierarchies
WHERE flex_value_set_id = gbl_acc_value_set_id
AND SUBSTR(parent_flex_value,1,6) = vg_sgl_acct_number;
SELECT flex_value
FROM fnd_flex_values
WHERE flex_value_set_id = gbl_acc_value_set_id
AND flex_value BETWEEN vl_child_flex_value_low AND vl_child_flex_value_high
ORDER BY 1;
DELETE_FED_ACCOUNTS(p_sob_id, p_run_year) ;
SELECT SUBSTR(parent_flex_value,1,6)
INTO vg_sgl_acct_number
FROM fnd_flex_value_hierarchies
WHERE vg_acct_number
BETWEEN child_flex_value_low AND child_flex_value_high
AND flex_value_set_id = gbl_acc_value_set_id
AND parent_flex_value <> 'T'
AND SUBSTR(parent_flex_value,1,6) IN
(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 = gbl_acc_value_set_id
AND enabled_flag = 'Y'
);
UPDATE_GTAS_RUN(P_RUN_YEAR, GBL_SET_OF_BOOKS_ID);
PROCEDURE DELETE_FED_ACCOUNTS(p_set_of_books_id IN VARCHAR2,
p_period_year IN VARCHAR2) IS
l_module_name VARCHAR2(200) := g_module_name||'DELETE_FED_ACCOUNTS';
DELETE FROM fv_gtas_fed_accounts
WHERE set_of_books_id = p_set_of_books_id
AND fiscal_year = p_period_year;
log(l_module_name, 'No data found to delete.');
'When others error in DELETE_FED_ACCOUNTS - '||SQLERRM;
END DELETE_FED_ACCOUNTS;
SELECT 1
INTO l_dummy
FROM fnd_flex_value_hierarchies
WHERE vg_acct_number
BETWEEN child_flex_value_low AND child_flex_value_high
AND flex_value_set_id = gbl_acc_value_set_id
AND parent_flex_value <> 'T'
AND SUBSTR(parent_flex_value,1,6) IN
(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 = gbl_acc_value_set_id
AND enabled_flag = 'Y'
);
SELECT name
INTO l_ledger_name
FROM GL_LEDGERS_V
WHERE ledger_id = gbl_set_of_books_id;
SELECT fed_non_fed_flag, authority_type_flag, budget_impact_ind_flag, trading_partner_flag
INTO vl_fed_non_fed_flag, vl_authority_type_flag, vl_bud_impact_ind_flag,
vl_trading_partner_flag
FROM fv_gtas_fed_accounts
WHERE account_number = vg_acct_number
AND set_of_books_id = gbl_set_of_books_id
AND fiscal_year = gbl_fiscal_year; --vg_sob_id;
UPDATE fv_gtas_fed_accounts
SET fed_non_fed_flag = vg_fed_nonfed_flag,
authority_type_flag = vg_authority_type_flag,
budget_impact_ind_flag = vg_bud_impact_ind_flag,
trading_partner_flag = vg_trading_partner_flag
WHERE account_number = vg_acct_number
AND set_of_books_id = gbl_set_of_books_id
AND fiscal_year = gbl_fiscal_year;
INSERT INTO fv_gtas_fed_accounts
(account_number,
sgl_account_number,
set_of_books_id,
fed_non_fed_flag,
last_run_date,
jc_flag,
fiscal_year,
authority_type_flag,
budget_impact_ind_flag,
trading_partner_flag
)
VALUES
(vg_acct_number,
vg_sgl_acct_number,
gbl_set_of_books_id,
vg_fed_nonfed_flag,
sysdate,
'N',
gbl_fiscal_year,
vg_authority_type_flag,
vg_bud_impact_ind_flag,
vg_trading_partner_flag
);
PROCEDURE UPDATE_GTAS_RUN(p_period_year in VARCHAR2,
p_set_of_books_id in VARCHAR2) IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'UPDATE_GTAS_RUN';
UPDATE fv_gtas_run
SET run_fed_flag = 'A',
process_date = sysdate
WHERE set_of_books_id = p_set_of_books_id
AND fiscal_year = p_period_year;
INSERT INTO fv_gtas_run(set_of_books_id, fiscal_year,
status, process_date,
run_fed_flag,posted_date)
VALUES(gbl_set_of_books_id, p_period_year, 'A', sysdate,'A' ,
l_posted_date);
gbl_error_buf := SQLERRM || 'In UPDATE_GTAS_RUN - '|| l_stage ;
gbl_error_buf := SQLERRM || 'When others error in UPDATE_GTAS_RUN - '||SQLERRM;
END UPDATE_GTAS_RUN;
SELECT substr(compiled_value_attributes, 5, 1)
INTO l_acct_type
FROM fnd_flex_values
WHERE flex_value_set_id = gbl_acc_value_set_id
AND flex_value = p_acct_num ;
DELETE FROM fv_gtas1_period_attributes
WHERE period_year = gbl_period_year
AND set_of_books_id = gbl_set_of_books_id
AND account_number = p_acct_num ;