[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_cohort_select Varchar2(20) ;
PROCEDURE select_group_by_columns(x_report_id IN number,
x_attribute_set IN VARCHAR2,
x_group_by out NOCOPY varchar2)
is
l_module_name VARCHAR2(200) := g_module_name || 'select_group_by_columns';
cursor c_group IS SELECT COLUMN_NAME
from fa_rx_rep_columns_b
WHERE REPORT_id = x_report_id
and attribute_set = x_attribute_set
AND BREAK = 'Y';
Procedure Build_Appor_select (ccid NUMBER,
Acct_number Varchar2,
Fund_Value Varchar2,
fiscal_year Varchar2,
Appor_period Varchar2,
select_stmt OUT NOCOPY Varchar2) ;
DELETE FROM fv_facts_temp
WHERE fct_int_record_type = 'TB';
Select period_year,period_num,start_date,end_date
Into vp_report_fiscal_yr,v_period_num,v_period_start_dt,v_period_end_dt
From gl_period_statuses
Where ledger_id = vp_set_of_books_id
And application_id = 101
And period_name = vp_period_name;
'.select1', vp_errbuf) ;
Select period_name,
start_date,
end_date,
period_num
Into v_begin_period_name,
v_begin_period_start_dt,
v_begin_period_end_dt,
v_begin_period_num
from gl_period_statuses
where (start_date,period_num) IN (Select MIN(year_start_date),MIN(period_num)
from gl_period_statuses
where period_year = vp_report_fiscal_yr
and ledger_id = vp_set_of_books_id)
and application_id = 101
and ledger_id = vp_set_of_books_id ;
Select
FTS.resource_type,
RPAD(FFFA.Treasury_dept_code, 2),
FTS.Time_Frame,
FTS.Established_Fiscal_yr,
FFFA.financing_account,
FFFA.cohort_segment_name,
RPAD(FFFA.Treasury_acct_code, 4),
NVL(LPAD(FTS.Tafs_sub_acct,3, '0'),'000'),
NVL(LPAD(FTS.Tafs_split_code, 3, '0'),'000'),
FTS.years_available,
fts.dept_transfer
Into
vl_resource_type,
vc_dept_regular,
vl_time_frame,
vl_established_fy,
vl_financing_acct,
v_cohort_seg_name,
vc_main_account,
vc_sub_acct_symbol,
vc_acct_split_seq_num,
vl_years_available,
vc_dept_transfer
From
FV_FACTS_FEDERAL_ACCOUNTS FFFA,
FV_TREASURY_SYMBOLS FTS
Where FFFA.Federal_acct_symbol_id = FTS.Federal_acct_symbol_id
AND FTS.treasury_symbol = vp_treasury_symbol
AND FTS.set_of_books_id = vp_set_of_books_id
AND FFFA.set_of_books_id = vp_set_of_books_id ;
SELECT to_char(count(*) , '09')
INTO vl_fiscal_month_count
FROM gl_period_statuses
WHERE ledger_id = vp_set_of_books_id
AND application_id = 101
AND period_year = vp_report_fiscal_yr
AND adjustment_period_flag = 'N'
AND period_num <= v_period_num ;
vl_main_select Varchar2(6000) ;
vl_legis_select Varchar2(6000) ;
vl_appor_select Varchar2(6000) ;
vl_cohort_select Varchar2(25) ;
select vl_legis_ref transaction_id, vl_legis_amount amount
from dual
where nvl(vl_je_sla_flag ,'N') = 'N'
union all
SELECT to_char(xd.source_distribution_id_num_1) transaction_id,
(NVL(xd.unrounded_accounted_dr,0) -
NVL(xd.unrounded_accounted_cr,0)) amount
FROM gl_import_references gli,
xla_ae_lines xl,
xla_ae_headers xh,
xla_distribution_links xd
WHERE gli.je_batch_id = vl_je_batch_id
AND gli.je_header_id = vl_je_header_id
AND gli.je_line_num = vl_je_line_num
AND xl.gl_sl_link_id = gli.gl_sl_link_id
AND xl.application_id = 8901
AND xh.ae_header_id = xl.ae_header_id
AND xl.ledger_id = vp_set_of_books_id
AND xd.event_id = xh.event_id
and xd.ae_header_id = xh.ae_header_id
and xd.ae_line_num = xl.ae_line_num
and nvl(vl_je_sla_flag ,'N') = 'Y';
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'Selecting FACTS Transactions.....') ;
v_cohort_select := ', GLCC.' || v_cohort_seg_name ;
v_cohort_select := ' ' ;
vl_main_select :=
'Select
GLCC.code_combination_id , GLCC.' || v_acc_seg_name ||
', GLCC.' || v_bal_seg_name ||
', GLCC.' || v_fyr_segment_name ||
', SUM((glb.begin_balance_dr - glb.begin_balance_cr) +
(glb.period_net_dr - period_net_cr)) '||
v_segment ||
v_cohort_select ||
' From GL_BALANCES GLB,
GL_CODE_COMBINATIONS GLCC
WHERE GLB.code_combination_id = GLCC.code_combination_id ';
vl_main_select := vl_main_select || 'AND ' || das_where;
vl_main_select := vl_main_select ||
' AND glb.actual_flag = :actual_flag
AND GLB.TEMPLATE_ID IS NULL
AND GLCC.' || v_bal_seg_name || ' = :fund_value
AND GLB.ledger_id = :set_of_books_id
AND GLB.PERIOD_YEAR = :report_fiscal_yr
AND glb.currency_code = :currency_code
GROUP BY GLCC.code_combination_id ,
GLCC.' || v_acc_seg_name ||
', GLCC.' || v_bal_seg_name ||
', GLCC.' || v_fyr_segment_name
|| v_segment ||v_cohort_select ||
' ORDER BY GLCC.' || v_acc_seg_name ;
dbms_sql.parse(vl_main_cursor, vl_main_select, DBMS_SQL.V7) ;
SELECT TO_NUMBER(vl_cohort_yr)
INTO v_dummy_cohort
FROM DUAL;
FOR begin_balance_rec IN (SELECT SUM(NVL(f.ending_balance_dr, 0) - NVL(f.ending_balance_cr, 0)) amount,
f.public_law,
f.advance_flag,
f.transfer_dept_id,
f.transfer_main_acct
FROM fv_factsii_ending_balances f
WHERE f.set_of_books_id = vp_set_of_books_id
AND f.fiscal_year = vp_report_fiscal_yr-1
AND f.ccid = vl_ccid
GROUP BY f.public_law,
f.advance_flag,
f.transfer_dept_id,
f.transfer_main_acct) LOOP
v_amount := begin_balance_rec.amount;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'vl_legis_Select') ;
vl_legis_select :=
'Select gjl.reference_1,
Nvl(gjl.entered_dr, 0) - Nvl(gjl.entered_cr, 0),
gjl.effective_date , gjl.period_name,
Nvl(gjl.entered_dr, 0) period_dr , Nvl(gjl.entered_cr, 0) period_cr,
gjh.je_source ,gjh.je_header_id , gjl.je_line_num , gjh.je_batch_id,je_from_sla_flag '||
va_pl_code_col || va_tr_main_acct_col || va_tr_dept_id_col ||
va_advance_type_col ||
' From gl_je_lines gjl,
gl_code_combinations glcc,
gl_je_headers gjh
Where gjl.code_combination_id = glcc.code_combination_id
AND glcc.code_combination_id = :ccid ';
vl_legis_select := vl_legis_select ||
' AND gjl.status = :je_status
AND (gjl.effective_date between
:begin_period_start_dt
AND :period_end_dt)
AND gjl.ledger_id = :set_of_books_id
AND glcc.' || v_acc_seg_name || ' = :acct_num
AND Nvl(gjl.entered_dr, 0) - Nvl(gjl.entered_cr, 0) <> 0
AND glcc.' || v_bal_seg_name || ' = :fund_value ' ||
v_cohort_where ||
' AND glcc.'||v_fyr_segment_name || ' = :fiscal_yr
AND gjh.je_header_id = gjl.je_header_id
AND gjh.currency_code = :currency_code
AND NOT EXISTS
(SELECT ''x''
FROM gl_period_statuses glp
WHERE glp.ledger_id = :set_of_books_id
AND glp.application_id = 101
AND glp.period_name = gjl.period_name
AND glp.period_year = :report_fiscal_yr
AND glp.period_num > :period_num) ';
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, vl_legis_select) ;
dbms_sql.parse(vl_legis_cursor,vl_legis_select,DBMS_SQL.V7);
SELECT adjustment_period_flag, period_num
INTO vl_adj_flag , vl_adj_num
FROM gl_period_statuses
WHERE ledger_id = vp_set_of_books_id
AND application_id = 101
AND period_name = vl_period_name;
SELECT public_law_code
INTO va_public_law_code_val
FROM fv_be_trx_dtls
WHERE transaction_id = vl_legis_ref
AND set_of_books_id = vp_set_of_books_id ;
SELECT advance_type
INTO va_advance_type_val
FROM fv_be_trx_dtls
WHERE transaction_id = vl_legis_ref
AND set_of_books_id = vp_set_of_books_id ;
SELECT dept_id, main_account
INTO va_transfer_dept_id, va_transfer_main_acct
FROM fv_be_trx_dtls
WHERE transaction_id = vl_legis_ref
AND set_of_books_id = vp_set_of_books_id ;
Build_Appor_Select(vl_ccid,
vl_acct_num,
vl_fund_value,
v_fiscal_yr,
vl_appor_period,
vl_appor_select) ;
dbms_sql.parse(vl_appor_cursor,vl_appor_select,
DBMS_SQL.V7);
SELECT balance_type,
public_law_code,
reimburseable_flag,
Decode(availability_time, 'N', ' ', availability_time),
bea_category,
apportionment_category,
Decode(substr(transaction_partner,1,1),'N',' ',
substr(transaction_partner,1,1)),
borrowing_source,
definite_indefinite_flag,
legislative_indicator,
authority_type,
deficiency_flag,
function_flag,
advance_flag,
transfer_flag
INTO
va_balance_type_flag,
va_public_law_code_flag,
va_reimburseable_flag,
va_availability_flag,
va_bea_category_flag,
va_appor_cat_flag,
va_transaction_partner_val,
va_borrowing_source_flag,
va_def_indef_flag,
va_legis_ind_flag,
va_authority_type_flag,
va_deficiency_flag,
va_function_flag,
va_advance_flag,
va_transfer_ind
FROM FV_FACTS_ATTRIBUTES
WHERE Facts_Acct_Number = acct_num
and set_of_books_id = vp_set_of_books_id ;
SELECT factsII_pub_law_code_attribute,
factsII_advance_type_attribute,
factsII_tr_main_acct_attribute,
factsII_tr_dept_id_attribute
INTO va_pl_code_col, va_advance_type_col,
va_tr_main_acct_col, va_tr_dept_id_col
FROM fv_system_parameters;
Select UPPER(fts.resource_type),
def_indef_flag,
ffp.fund_category,
RPAD(substr(bea_category,1,5), 5)
INTO vl_resource_type,
va_def_indef_val,
vl_fund_category,
va_bea_category_val
From fv_treasury_symbols fts,
fv_fund_parameters ffp
WHERE ffp.treasury_symbol_id = fts.treasury_symbol_id
AND ffp.fund_value = fund_val
AND fts.treasury_symbol = vp_treasury_symbol
AND fts.set_of_books_id = vp_set_of_books_id
AND ffp.set_of_books_id = vp_set_of_books_id ;
Select RPAD(substr(ffba.bea_category,1,5), 5)
Into va_bea_category_val
from fv_fund_parameters_all
where fund_value = vl_fund_value
and set_of_books_id = vp_set_of_books_id;
Select RPAD(substr(ffba.budget_function,1,3), 3)
Into va_budget_function
From fv_facts_budget_accounts ffba,
fv_facts_federal_accounts fffa,
fv_treasury_symbols fts ,
fv_facts_bud_fed_accts ffbfa
Where fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
AND fts.treasury_symbol = vp_treasury_symbol
AND fts.set_of_books_id = vp_set_of_books_id
AND fffa.set_of_books_id = vp_set_of_books_id
AND ffbfa.set_of_books_id = vp_set_of_books_id
AND ffba.set_of_books_id = vp_set_of_books_id ;
Select RPAD(substr(ffba.borrowing_source,1,6), 6)
Into va_borrowing_source_val
From fv_facts_budget_accounts ffba,
fv_facts_federal_accounts fffa,
fv_treasury_symbols fts ,
fv_facts_bud_fed_accts ffbfa
Where fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
AND fts.treasury_symbol = vp_treasury_symbol
AND fts.set_of_books_id = vp_set_of_books_id
AND fffa.set_of_books_id = vp_set_of_books_id
AND ffbfa.set_of_books_id = vp_set_of_books_id
AND ffba.set_of_books_id = vp_set_of_books_id ;
vl_bal_select Varchar2(2000) ;
vl_bal_select :=
'Select Nvl(Decode(' || '''' || Balance_type || '''' ||
',' || '''' || 'B' || '''' ||
', SUM(GLB.BEGIN_BALANCE_DR - GLB.BEGIN_BALANCE_CR), ' ||
'''' || 'E' || '''' || ', SUM((GLB.BEGIN_BALANCE_DR -
GLB.BEGIN_BALANCE_CR) + (GLB.PERIOD_NET_DR - PERIOD_NET_CR ))),0),
SUM(glb.period_net_dr - glb.period_net_cr) ,
SUM(glb.period_net_dr) , sum(glb.period_net_cr)
From GL_BALANCES GLB,
GL_CODE_COMBINATIONS GLCC
WHERE GLB.code_combination_id = GLCC.code_combination_id ';
vl_bal_select := vl_bal_select || 'AND ' || das_where;
vl_bal_select := vl_bal_select || 'AND glcc.code_combination_id = to_char(:ccid) ';
vl_bal_select := vl_bal_select ||' AND glb.actual_flag =:actual_flag
AND GLCC.' || v_bal_seg_name || ' = :fund_value
AND GLCC.' || v_acc_seg_name || ' = :acct_num
AND GLCC.' || v_fyr_segment_name || ' = :fiscal_year '||
v_cohort_where ||
' AND GLB.ledger_id = :set_of_books_id
AND GLB.PERIOD_NUM = :period_num
AND GLB.PERIOD_YEAR = :period_year
AND glb.currency_code = :currency_code ' ;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'mg calc '||vl_bal_select) ;
dbms_sql.parse(vl_bal_cursor, vl_bal_select, DBMS_SQL.V7) ;
SELECT disbursements_flag INTO vl_disbursements_flag
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = v_sgl_acct_num;
SELECT fyr_segment_value INTO vl_fyr_segment_value
FROM fv_pya_fiscalyear_map
WHERE period_year = vp_report_fiscal_yr
AND set_of_books_id = vp_set_of_books_id;
INSERT INTO FV_FACTS_TEMP
(code_combination_id,
SGL_ACCT_NUMBER ,
COHORT ,
BEGIN_END ,
INDEF_DEF_FLAG ,
APPOR_CAT_B_DTL ,
APPOR_CAT_B_TXT ,
PUBLIC_LAW ,
APPOR_CAT_CODE ,
AUTHORITY_TYPE ,
TRANSACTION_PARTNER ,
REIMBURSEABLE_FLAG ,
BEA_CATEGORY ,
BORROWING_SOURCE ,
DEF_LIQUID_FLAG ,
DEFICIENCY_FLAG ,
AVAILABILITY_FLAG ,
LEGISLATION_FLAG ,
AMOUNT ,
DEBIT_CREDIT ,
TREASURY_SYMBOL_ID ,
FCT_INT_RECORD_CATEGORY ,
FCT_INT_RECORD_TYPE ,
TBAL_FUND_VALUE ,
TBAL_ACCT_NUM ,
BUDGET_FUNCTION ,
ADVANCE_FLAG ,
TRANSFER_DEPT_ID ,
TRANSFER_MAIN_ACCT ,
YEAR_BUDGET_AUTH ,
period_activity ,
amount1 ,
amount2 ,
parent_sgl_acct_number ,
PROGRAM_RPT_CAT_NUM,
PROGRAM_RPT_CAT_TXT)
Values (vl_ccid ,
v_sgl_acct_num ,
va_cohort ,
va_balance_type_val ,
va_def_indef_val ,
va_appor_cat_b_dtl ,
va_appor_cat_b_txt ,
va_public_law_code_val ,
va_appor_cat_val ,
va_authority_type_val ,
va_transaction_partner_val,
va_reimburseable_val ,
va_bea_category_val ,
va_borrowing_source_val ,
va_def_liquid_flag ,
va_deficiency_val ,
va_availability_flag ,
va_legis_ind_val ,
v_amount ,
NULL ,
v_treasury_symbol_id ,
v_record_category ,
'TB' ,
v_tbal_fund_value ,
v_tbal_acct_num,
va_budget_function ,
va_advance_type_val ,
va_transfer_dept_id ,
va_transfer_main_acct ,
v_year_budget_auth ,
v_period_activity ,
v_period_dr ,
v_period_cr ,
vl_parent_sgl_acct_num,
va_prn_num,
va_prn_txt) ;
SELECT program_segment, prc_mapping_flag,
prc_header_id
INTO vl_prg_seg_name, vl_prc_flag,
vl_prc_header_id
FROM fv_facts_prc_hdr
WHERE treasury_symbol_id = v_treasury_symbol_id
AND code_type = vl_code_type
AND fund_value = v_fund_value
AND set_of_books_id = vp_set_of_books_id;
SELECT program_segment, prc_mapping_flag,
prc_header_id
INTO vl_prg_seg_name, vl_prc_flag,
vl_prc_header_id
FROM fv_facts_prc_hdr
WHERE treasury_symbol_id = v_treasury_symbol_id
AND fund_value = 'ALL-A'
AND code_type = vl_code_type
AND va_appor_cat_val = 'A'
AND set_of_books_id = vp_set_of_books_id;
SELECT program_segment, prc_mapping_flag,
prc_header_id
INTO vl_prg_seg_name, vl_prc_flag,
vl_prc_header_id
FROM fv_facts_prc_hdr
WHERE treasury_symbol_id = v_treasury_symbol_id
AND fund_value = 'ALL-B'
AND code_type = vl_code_type
AND va_appor_cat_val = 'B'
AND set_of_books_id = vp_set_of_books_id;
SELECT program_segment, prc_mapping_flag,
prc_header_id
INTO vl_prg_seg_name, vl_prc_flag,
vl_prc_header_id
FROM fv_facts_prc_hdr
WHERE treasury_symbol_id = v_treasury_symbol_id
AND code_type = vl_code_type
AND fund_value = 'ALL-FUNDS'
AND set_of_books_id = vp_set_of_books_id;
SELECT program_segment, prc_mapping_flag,
prc_header_id
INTO vl_prg_seg_name, vl_prc_flag,
vl_prc_header_id
FROM fv_facts_prc_hdr
WHERE treasury_symbol_id = -1
AND code_type = vl_code_type
AND fund_value = 'ALL-A'
AND va_appor_cat_val = 'A'
AND set_of_books_id = vp_set_of_books_id;
SELECT program_segment, prc_mapping_flag,
prc_header_id
INTO vl_prg_seg_name, vl_prc_flag,
vl_prc_header_id
FROM fv_facts_prc_hdr
WHERE treasury_symbol_id = -1
AND fund_value = 'ALL-B'
AND code_type = vl_code_type
AND va_appor_cat_val = 'B'
AND set_of_books_id = vp_set_of_books_id;
SELECT program_segment, prc_mapping_flag,
prc_header_id
INTO vl_prg_seg_name, vl_prc_flag,
vl_prc_header_id
FROM fv_facts_prc_hdr
WHERE treasury_symbol_id = -1
AND fund_value = 'ALL-FUNDS'
AND code_type = vl_code_type
AND set_of_books_id = vp_set_of_books_id;
select flex_value_set_id
into vl_prg_val_set_id
from fnd_id_flex_segments
where application_column_name = vl_prg_seg_name
and application_id = 101
and id_flex_code = 'GL#'
and id_flex_num = vp_coa_id ;
Select Decode(ffvl.Description,
NULL, RPAD(' ',25,' '), RPAD(ffvl.Description,25,' '))
Into p_seg_txt
From fnd_flex_values_tl ffvl,
fnd_flex_values ffv
where ffvl.flex_value_id = ffv.flex_value_id
AND ffv.flex_value_set_id = p_prg_val_set_id
AND ffv.flex_value = p_program
AND ffvl.language = userenv('LANG');
Select substr(compiled_value_attributes, 5, 1)
Into acct_type
From fnd_flex_values
where flex_value_set_id = v_acc_val_set_id
and flex_value = acct_num ;
SELECT parent_flex_value
INTO parent_ac
FROM fnd_flex_value_hierarchies
WHERE (acct_num Between child_flex_value_low
and child_flex_value_high)
AND flex_value_set_id = v_acc_val_set_id
AND parent_flex_value <> 'T'
AND parent_flex_value IN
(SELECT ussgl_account
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = parent_flex_value);
SELECT ussgl_account
INTO sgl_acct_num
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = acct_num ;
SELECT ussgl_account
INTO sgl_acct_num
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = parent_ac ;
Procedure Build_Appor_select ( ccid number,
Acct_number Varchar2,
Fund_Value Varchar2,
fiscal_year Varchar2,
appor_period Varchar2,
select_stmt OUT NOCOPY Varchar2)
IS
l_module_name VARCHAR2(200) := g_module_name || 'Build_Appor_select';
select_stmt :=
'Select GLCC.' || v_acc_seg_name ||
', GLCC.' || v_bal_seg_name;
select_stmt := select_stmt ||
', GLCC.' || v_catb_prg_seg_name ;
select_stmt := select_stmt ||
', GLCC.' || v_prn_prg_seg_name ;
select_stmt := select_stmt ||
', SUM(GLB.BEGIN_BALANCE_DR - GLB.BEGIN_BALANCE_CR), ' ||
' SUM(GLB.PERIOD_NET_DR - PERIOD_NET_CR ), '||
' SUM(GLB.PERIOD_NET_DR) period_dr , sum( PERIOD_NET_CR ) period_cr '||
v_cohort_select ||
' From GL_BALANCES GLB,
GL_CODE_COMBINATIONS GLCC
WHERE GLB.code_combination_id = GLCC.code_combination_id
AND glcc.code_combination_id = :ccid
AND glb.actual_flag = :actual_flag
AND GLCC.'|| v_bal_seg_name ||' = :Fund_Value
AND GLCC.' || v_acc_seg_name ||' = :acct_number
AND GLCC.' || v_fyr_segment_name ||' = :fiscal_year '||
appor_period || v_cohort_where ||
' AND GLB.ledger_id = :set_of_books_id
AND glb.currency_code = :currency_code ';
select_stmt := select_stmt || 'AND ' || das_where;
select_stmt := select_stmt || 'GROUP BY GLCC.' || v_acc_seg_name ||
', GLCC.' || v_bal_seg_name;
select_stmt := select_stmt ||
', GLCC.' || v_prn_prg_seg_name ;
select_stmt := select_stmt ||
', GLCC.' || v_fyr_segment_name || v_cohort_select;
vp_errbuf := sqlerrm ||'[ build_appor_select]';
End build_appor_select ;
Select Treasury_Symbol_id
Into v_treasury_symbol_id
From fv_treasury_symbols
where treasury_symbol = vp_treasury_symbol
and set_of_books_id = vp_set_of_books_id ;
select_group_by_columns(vp_report_id,vp_attribute_set,vl_group_by);
INSERT INTO FV_FACTS_TEMP
(TREASURY_SYMBOL_ID ,
SGL_ACCT_NUMBER ,
COHORT ,
INDEF_DEF_FLAG ,
APPOR_CAT_B_DTL ,
APPOR_CAT_B_TXT ,
PROGRAM_RPT_CAT_NUM,
PROGRAM_RPT_CAT_TXT,
PUBLIC_LAW ,
APPOR_CAT_CODE ,
AUTHORITY_TYPE ,
TRANSACTION_PARTNER ,
REIMBURSEABLE_FLAG ,
BEA_CATEGORY ,
BORROWING_SOURCE ,
DEF_LIQUID_FLAG ,
DEFICIENCY_FLAG ,
AVAILABILITY_FLAG ,
LEGISLATION_FLAG ,
AMOUNT ,
TBAL_FUND_VALUE ,
TBAL_ACCT_NUM ,
fct_int_record_category,
fct_int_record_type,
YEAR_BUDGET_AUTH ,
BUDGET_FUNCTION ,
ADVANCE_FLAG ,
TRANSFER_DEPT_ID ,
TRANSFER_MAIN_ACCT ,
AMOUNT1,
AMOUNT2,
period_activity ' ||
replace(vl_group_by ,'glcc.' ) || ')' ||
' SELECT
TREASURY_SYMBOL_ID,
SGL_ACCT_NUMBER,
COHORT,
INDEF_DEF_FLAG,
APPOR_CAT_B_DTL,
APPOR_CAT_B_TXT,
PROGRAM_RPT_CAT_NUM,
PROGRAM_RPT_CAT_TXT,
PUBLIC_LAW,
APPOR_CAT_CODE,
AUTHORITY_TYPE,
TRANSACTION_PARTNER,
REIMBURSEABLE_FLAG,
BEA_CATEGORY,
BORROWING_SOURCE,
DEF_LIQUID_FLAG,
DEFICIENCY_FLAG,
AVAILABILITY_FLAG,
LEGISLATION_FLAG,
SUM(decode(begin_end , ''P'', AMOUNT+PERIOD_ACTIVITY , AMOUNT)),
tbal_fund_value,
tbal_acct_num,
''REPORTED_NEW'',
''TB'',
YEAR_BUDGET_AUTH,
BUDGET_FUNCTION ,
ADVANCE_FLAG ,
TRANSFER_DEPT_ID,
TRANSFER_MAIN_ACCT,
SUM(AMOUNT1),
SUM(AMOUNT2),
SUM(decode(begin_end , ''P'' , 0 , period_activity )) '
|| vl_group_by ||
' From FV_FACTS_TEMP fvt, gl_code_combinations glcc
WHERE fct_int_record_category = ''REPORTED''
AND fct_int_record_type = ''TB''
AND tbal_fund_value = ' || '''' || v_fund_value || ''''
|| ' and glcc.code_combination_id = fvt.code_combination_id
GROUP BY TREASURY_SYMBOL_ID,
SGL_ACCT_NUMBER,
COHORT,
INDEF_DEF_FLAG,
APPOR_CAT_B_DTL,
APPOR_CAT_B_TXT,
PROGRAM_RPT_CAT_NUM,
PROGRAM_RPT_CAT_TXT,
PUBLIC_LAW,
APPOR_CAT_CODE,
AUTHORITY_TYPE,
TRANSACTION_PARTNER,
REIMBURSEABLE_FLAG,
BEA_CATEGORY,
BORROWING_SOURCE,
DEF_LIQUID_FLAG,
DEFICIENCY_FLAG,
AVAILABILITY_FLAG,
LEGISLATION_FLAG ,
TBAL_FUND_VALUE ,
TBAL_ACCT_NUM,
YEAR_BUDGET_AUTH,
BUDGET_FUNCTION ,
ADVANCE_FLAG ,
TRANSFER_DEPT_ID,
TRANSFER_MAIN_ACCT ' || vl_group_by ;
DELETE FROM FV_FACTS_TEMP
WHERE (fct_int_record_category = 'REPORTED'
-- OR fct_int_record_category = 'REPORTED_NEW' )
AND AMOUNT = 0 AND NVL(PERIOD_ACTIVITY,0) = 0
AND treasury_symbol_id = v_treasury_symbol_id ) ;
SELECT chart_of_accounts_id
INTO v_chart_of_accounts_id
FROM gl_ledgers_public_v
WHERE ledger_id = vp_set_of_books_id;
SELECT application_column_name
INTO v_fyr_segment_name
FROM fv_pya_fiscalyear_segment
WHERE set_of_books_id = vp_set_of_books_id;
SELECT flex_value_set_id
INTO v_acc_val_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = v_acc_seg_name
AND application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = vp_coa_id;
SELECT flex_value
FROM fnd_flex_values_vl
WHERE flex_value_set_id = vl_bal_flex_id
AND flex_value between vp_fund_low and vp_fund_high
AND summary_flag = 'N';
SELECT count(*) cnt
FROM fnd_flex_values_vl
WHERE flex_value_set_id = vl_bal_flex_id
AND flex_value between vp_fund_low and vp_fund_high
AND summary_flag = 'N';
Select flex_value_set_id
Into vl_bal_flex_id
From fnd_id_flex_segments
Where application_id = 101
And application_column_name = v_bal_seg_name
And id_flex_code = 'GL#'
And id_flex_num = vp_coa_id;
Select treasury_symbol
Into vp_treasury_symbol
From fv_treasury_symbols
Where treasury_symbol_id = (Select treasury_symbol_id
From fv_fund_parameters
Where fund_value = C_Get_Fund_Values_Rec.flex_value
And set_of_books_id = vp_set_of_books_id);
SELECT reporting_code, reporting_desc
INTO vl_prc_val, vl_prc_desc
FROM fv_facts_prc_dtl
WHERE prc_header_id = vl_prc_header_id
AND program_value = vl_program_val
AND set_of_books_id = vp_set_of_books_id;
SELECT reporting_code, reporting_desc
INTO vl_prc_val, vl_prc_desc
FROM fv_facts_prc_dtl
WHERE prc_header_id = vl_prc_header_id
AND program_value = 'ALL'
AND set_of_books_id = vp_set_of_books_id;
SELECT rowid, tbal_fund_value, sgl_acct_number, appor_cat_b_txt
FROM fv_facts_temp
WHERE fct_int_record_category = reported_type
AND appor_cat_code = 'B'
AND TRIM(appor_cat_b_txt) IS NOT NULL
ORDER BY tbal_fund_value, sgl_acct_number, appor_cat_b_txt ;
UPDATE fv_facts_temp
SET appor_cat_b_dtl = LPAD(to_char(l_seq), 3, '0')
WHERE rowid = cat_b_rec.rowid;