[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_cohort_select VARCHAR2(20) ;
PROCEDURE build_appor_select (acct_number VARCHAR2,
fund_value VARCHAR2,
fiscal_year VARCHAR2,
appor_period VARCHAR2,
select_stmt OUT NOCOPY VARCHAR2);
SELECT fv_facts_submission.rowid,
fv_facts_submission.Set_Of_Books_Id ,
fv_facts_submission.Run_Mode,
fv_treasury_symbols.Treasury_Symbol,
fv_facts_submission.rep_fyr ,
fv_facts_submission.rep_period_num,
fv_facts_submission.first_name,
fv_facts_submission.last_name,
fv_facts_submission.phone_no,
fv_facts_submission.phone_ext,
fv_facts_submission.email_address,
fv_facts_submission.fax_num ,
fv_facts_submission.mothers_m_name ,
fv_facts_submission.supervisor_name ,
fv_facts_submission.supervisor_phone,
fv_facts_submission.supervisor_ext ,
fv_facts_submission.agency_name_1 ,
fv_facts_submission.agency_name_2 ,
fv_facts_submission.address_1 ,
fv_facts_submission.address_2 ,
fv_facts_submission.city ,
fv_facts_submission.state ,
fv_facts_submission.zip ,
fv_facts_submission.currency_code,
fv_facts_submission.treasury_symbol_id
FROM fv_facts_submission ,
fv_treasury_symbols
WHERE submit_flag = 'Y'
AND fv_treasury_symbols.set_of_books_id = sob
AND fv_facts_submission.set_of_books_id = sob
AND fv_facts_submission.treasury_symbol_id =
fv_treasury_symbols.treasury_symbol_id;
SELECT count(*)
INTO vl_fiscalyear_count
FROM fv_pya_fiscalyear_map
WHERE set_of_books_id = sob;
SELECT count(*)
INTO vl_prc_map_count
FROM fv_facts_prc_hdr
WHERE set_of_books_id = sob;
SELECT count(1)
INTO vl_prc_no_code_count
FROM fv_facts_prc_hdr
WHERE set_of_books_id = sob
AND code_type IS NULL ;
errbuf:= 'Reporting Code Type has not been updated for existing ' ||
'Records. Please update the records and resubmit!';
UPDATE fv_facts_submission
SET submitted_by = fnd_global.user_name,
facts2_status = 'NO PRC MAPPED',
submit_flag = 'N'
WHERE rowid = crec.rowid;
UPDATE fv_facts_submission
SET submitted_by = fnd_global.user_name,
facts2_status = 'NO CATB PRC MAPPED',
submit_flag = 'N'
WHERE rowid = crec.rowid;
UPDATE fv_facts_submission
SET submitted_by = fnd_global.user_name,
facts2_status = 'NO PRN PRC MAPPED',
submit_flag = 'N'
WHERE rowid = crec.rowid;
UPDATE fv_facts_submission
SET submitted_by = fnd_global.user_name,
submitted_id = req_id,
facts2_status = 'IN PROCESS'
WHERE rowid = crec.rowid;
UPDATE fv_facts_submission
SET submit_flag = 'N',
facts2_status = vl_facts2_status
WHERE submit_flag = 'Y'
AND treasury_symbol_id = v_treasury_symbol_id ;
UPDATE fv_facts_submission
SET submit_flag = 'N',
facts2_status = 'ERROR'
WHERE submit_flag = 'Y'
AND treasury_symbol_id = v_treasury_symbol_id ;
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 ;
DELETE FROM fv_facts_temp
WHERE treasury_symbol_id = v_treasury_symbol_id ;
DELETE FROM fv_facts_edit_check_status
WHERE 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 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 = v_chart_of_accounts_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
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,
NVL(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 ;
SELECT period_name,
start_date,
end_date ,
quarter_num
INTO v_period_name,
v_period_start_dt,
v_period_end_dt,
vp_report_qtr
FROM gl_period_statuses
WHERE ledger_id = vp_set_of_books_id
AND application_id = 101
AND period_year = vp_report_fiscal_yr
AND period_num = v_period_num ;
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 period_num =
(SELECT 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 adjustment_period_flag = 'N')
AND application_id = 101
-- AND adjustment_period_flag = 'N'
AND ledger_id = vp_set_of_books_id
AND period_year = vp_report_fiscal_yr ;
vl_main_select VARCHAR2(2000) ;
vl_legis_select VARCHAR2(5000) ;
vl_appor_select VARCHAR2(2000) ;
CURSOR footnote_select
(p_tsymbol_id fv_treasury_symbols.treasury_symbol_id%TYPE)
IS
SELECT treasury_symbol_id,
sgl_acct_number
FROM fv_facts_temp
WHERE fct_int_record_type = 'BLK_DTL'
AND fct_int_record_category = 'REPORTED_NEW'
AND document_number = 'Y'
AND treasury_symbol_id = p_tsymbol_id ;
'Selecting FACTS Transactions.....') ;
v_cohort_select := ', GLCC.' || v_cohort_seg_name ;
v_cohort_select := ' ' ;
vl_main_select :=
'SELECT
GLCC.' || v_acc_seg_name ||
', GLCC.' || v_bal_seg_name ||
', GLCC.' || v_fyr_segment_name ||
v_cohort_select ||
' FROM GL_BALANCES GLB,
GL_CODE_COMBINATIONS GLCC,
FV_FUND_PARAMETERS FFP,
FV_TREASURY_SYMBOLS FTS
WHERE FTS.TREASURY_SYMBOL = :treasury_symbol
AND GLB.code_combination_id = GLCC.code_combination_id
AND glb.actual_flag = :actual_flag
AND FTS.TREASURY_SYMBOL_ID = FFP.TREASURY_SYMBOL_ID
AND GLB.TEMPLATE_ID IS NULL
AND GLCC.' || v_bal_seg_name || '= FFP.FUND_VALUE
AND GLB.SET_OF_BOOKS_ID = :set_of_books_id
AND FFP.SET_OF_BOOKS_ID = :set_of_books_id
AND FTS.SET_OF_BOOKS_ID = :set_of_books_id
AND GLB.PERIOD_YEAR = :report_fiscal_yr
AND glb.currency_code = :currency_code
GROUP BY GLCC.' || v_acc_seg_name ||
', GLCC.' || v_bal_seg_name ||
', GLCC.' || v_fyr_segment_name ||v_cohort_select ||
' ORDER BY GLCC.' || v_acc_seg_name ;
vl_main_select :=
'SELECT
GLCC.' || v_acc_seg_name ||
', GLCC.' || v_bal_seg_name ||
', GLCC.' || v_fyr_segment_name ||
v_cohort_select ||
',SUM(decode(glb.period_name,:b_period_name,glb.begin_balance_dr - glb.begin_balance_cr,0)) beg_amt
,SUM(decode(glb.period_name,:e_period_name,glb.begin_balance_dr - glb.begin_balance_cr +
glb.period_net_dr - glb.period_net_cr,0)) end_amount
FROM GL_BALANCES GLB,
GL_CODE_COMBINATIONS GLCC,
FV_FUND_PARAMETERS FFP,
FV_TREASURY_SYMBOLS FTS
WHERE FTS.TREASURY_SYMBOL = :treasury_symbol
AND FTS.SET_OF_BOOKS_ID = :set_of_books_id
AND FFP.TREASURY_SYMBOL_ID = FTS.TREASURY_SYMBOL_ID
AND FFP.SET_OF_BOOKS_ID = :set_of_books_id
AND GLCC.' || v_bal_seg_name || '= FFP.FUND_VALUE
AND GLB.code_combination_id = GLCC.code_combination_id
AND glb.actual_flag = :actual_flag
AND GLB.TEMPLATE_ID IS NULL
AND GLB.ledger_id = :set_of_books_id
AND GLB.PERIOD_NAME in(:b_period_name , :e_period_name)
AND glb.currency_code = :currency_code
GROUP BY GLCC.' || v_acc_seg_name ||
', GLCC.' || v_bal_seg_name ||
', GLCC.' || v_fyr_segment_name ||v_cohort_select ||
' ORDER BY GLCC.' || v_acc_seg_name ;
'Main Select: '||vl_main_select);
dbms_sql.parse(vl_main_cursor, vl_main_select, DBMS_SQL.V7) ;
SELECT TO_NUMBER(vl_cohort_yr)
INTO v_dummy_cohort
FROM DUAL;
SELECT disbursements_flag
INTO vl_disbursements_flag
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = vl_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;
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.account = vl_acct_num
AND f.fund = vl_fund_value
AND f.fyr = v_fiscal_yr
AND NVL(f.cohort, '-1') = DECODE (v_cohort_seg_name, NULL, NVL(f.cohort,'-1'), vl_cohort_yr)
GROUP BY f.public_law,
f.advance_flag,
f.transfer_dept_id,
f.transfer_main_acct) LOOP
v_amount := begin_balance_rec.amount;
'vl_legis_Select') ;
vl_legis_select :=
'SELECT gjl.reference_1,
NVL(gjl.entered_dr, 0) - NVL(gjl.entered_cr, 0) amout,
gjl.effective_date , gjl.period_name, gjh.je_source '||
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 gjl.status = :je_status
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 NVL(gjh.je_from_sla_flag, ''N'') = ''N''
AND gjh.currency_code = :currency_code ';
vl_legis_select :=
vl_legis_select || ' AND gjl.period_name in ' ||
' ( SELECT period_name
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = :set_of_books_id
AND period_num BETWEEN :begin_period_num AND :period_num
AND period_year = :report_fiscal_yr) ' ;
vl_legis_select := vl_legis_select || 'AND ' || das_where;
vl_legis_select := vl_legis_select || ' UNION ALL ';
vl_legis_select := vl_legis_select ||
'SELECT TO_CHAR(SOURCE_DISTRIBUTION_ID_NUM_1),
NVL(xl.accounted_dr, 0) - NVL(xl.accounted_cr, 0) amount,
gjl.effective_date , gjl.period_name, gjh.je_source '||
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,
gl_import_references gli,
xla_distribution_links xdl,
xla_ae_lines xl
WHERE gjl.code_combination_id = glcc.code_combination_id
AND gjl.status = :je_status
AND NVL(gjh.je_from_sla_flag, ''N'') = ''Y''
AND gjl.ledger_id = :set_of_books_id
AND glcc.'||v_acc_seg_name|| ' = :acct_num
AND NVL(xl.accounted_dr, 0) - NVL(xl.accounted_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 gli.je_batch_id = gjh.je_batch_id
and gli.je_header_id = gjh.je_header_id
and gli.je_line_num = gjl.je_line_num
AND xl.gl_sl_link_id = gli.gl_sl_link_id
AND xdl.ae_header_id = xl.ae_header_id
AND xdl.ae_line_num = xl.ae_line_num ';
vl_legis_select :=
vl_legis_select || ' AND gjl.period_name in ' ||
' ( SELECT period_name
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = :set_of_books_id
AND period_num BETWEEN :begin_period_num AND :period_num
AND period_year = :report_fiscal_yr) ' ;
vl_legis_select := vl_legis_select || 'AND ' || das_where;
vl_legis_select) ;
dbms_sql.parse(vl_legis_cursor,vl_legis_select,DBMS_SQL.V7);
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 transaction_type_id
INTO vl_tran_type
FROM fv_be_trx_dtls
WHERE transaction_id = vl_legis_ref
AND set_of_books_id = vp_set_of_books_id ;
SELECT legislative_indicator
INTO va_legis_ind_val
FROM fv_be_transaction_types
WHERE be_tt_id = vl_tran_type
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_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);
build_appor_select(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);
DELETE FROM FV_FACTS_TEMP
WHERE (fct_int_record_category = 'REPORTED' OR
fct_int_record_category = 'REPORTED_NEW')
AND amount = 0
AND sgl_acct_number like '4%'
AND treasury_symbol_id = v_treasury_symbol_id ;
SELECT TO_CHAR(DECODE(vp_run_mode,'F',
(DECODE(MAX(maf_seq_num), NULL, 0,MAX(maf_seq_num)+1)),'P',
(NVL(MAX(maf_seq_num), 0))))
FROM fv_facts_run
WHERE treasury_symbol = vp_treasury_symbol
AND treasury_symbol_id = v_treasury_symbol_id
AND facts_run_period = v_period_num
AND facts_run_year = vp_report_fiscal_yr ;
UPDATE fv_facts_temp
SET facts_report_info =
vc_dept_regular || vc_dept_transfer || vc_fiscal_yr ||
vc_main_account|| vc_sub_acct_symbol||lpad(vc_maf_seq_num,3,'0')||
RPAD(NVL(program_rpt_cat_num, ' '), 3, ' ') ||
RPAD(NVL(program_rpt_cat_txt, ' '), 25, ' ') ||
LPAD(sgl_acct_number,4) || vc_record_indicator ||
cohort || begin_end || indef_def_flag ||
RPAD(NVL(appor_cat_b_dtl,' '),3,' ') ||
RPAD(NVL(appor_cat_b_txt, ' '), 25, ' ') ||
rpad(NVL(public_law, ' '),7,' ') ||
appor_cat_code ||
authority_type || transaction_partner || transfer_dept_id ||
transfer_main_acct || vc_transfer_to_from || reimburseable_flag||
RPAD(year_budget_auth,4) || bea_category || borrowing_source ||
advance_flag ||vc_current_permanent_flag|| def_liquid_flag||
deficiency_flag || availability_flag || legislation_flag ||
RPAD(NVL(budget_function,' '),3) ||
LPAD(DECODE(INSTR(TO_CHAR(ABS(amount)),'.',1,1), 0,
TO_CHAR(ABS(amount))||'00',(SUBSTR(TO_CHAR(ABS(amount))
, 1, instr(to_char(abs(amount)),'.',1,1) - 1) ||
RPAD(substr(to_char(abs(amount)), instr(to_char(abs
(amount)), '.',1,1) + 1, 2),2,'0'))), 17, '0') ||
debit_credit || RPAD(' ', 258)
WHERE fct_int_record_category = 'REPORTED_NEW'
AND treasury_symbol_id = v_treasury_symbol_id ;
INSERT INTO FV_FACTS_TEMP
(treasury_symbol_id,
fct_int_record_category,
fct_int_record_type,
facts_report_info)
VALUES ( v_treasury_symbol_id ,
'REPORTED_NEW',
'CNT_HDR',
vc_rpt_fiscal_yr || vc_rpt_fiscal_month ||
vp_contact_fname || vp_contact_lname || vp_contact_phone ||
vp_contact_extn || vp_agency_name_1 || vp_agency_name_2 ||
vp_address_1 || vp_address_2 || vp_city || vp_state || vp_zip||
vp_supervisor_name || vp_supervisor_phone||vp_supervisor_extn||
vp_contact_email || vp_contact_fax || vp_contact_maiden ) ;
UPDATE fv_facts_run
SET maf_seq_num = to_number(vc_maf_seq_num),
last_run_date = sysdate
WHERE treasury_symbol_id = v_treasury_symbol_id ;
INSERT INTO fv_facts_run
(treasury_symbol_id,
treasury_symbol,
facts_run_period,
facts_run_year,
maf_seq_num,
last_run_date)
VALUES
(v_treasury_symbol_id,
vp_treasury_symbol,
v_period_num,
vp_report_fiscal_yr,
to_number(vc_maf_seq_num),
sysdate) ;
vp_errbuf := sqlerrm || '[FACTS RUN UPDATE]' ;
l_module_name||'.insert_fv_facts_run', vp_errbuf) ;
FOR footnote_rec IN footnote_select(v_treasury_symbol_id)
LOOP
SELECT count(*)
INTO footnote_count
FROM fv_facts_footnote_hdr ffh,
fv_facts_footnote_lines ffl
WHERE ffh.treasury_symbol_id = footnote_rec.treasury_symbol_id
AND ffh.sgl_acct_number = footnote_rec.sgl_acct_number
AND ffh.footnote_header_id = ffl.footnote_header_id ;
INSERT INTO fv_facts_footnote_hdr
(footnote_header_id,
treasury_symbol_id,
sgl_acct_number)
VALUES
(fv_facts_footnote_hdr_s.nextval,
footnote_rec.treasury_symbol_id,
footnote_rec.sgl_acct_number);
INSERT INTO fv_facts_footnote_lines
(footnote_header_id,
footnote_line_id,
footnote_seq_number,
footnote_text)
VALUES
(fv_facts_footnote_hdr_s.currval,
fv_facts_footnote_lines_s.nextval,
1,
'Footnotes should be entered in FACTS II '||
'Online system');
SELECT 'X'
INTO vl_exists
FROM fv_facts_attributes
WHERE facts_acct_number = acct_num
AND set_of_books_id = vp_set_of_books_id ;
SELECT 'X'
INTO vl_exists
FROM FV_FACTS_ATTRIBUTES
WHERE facts_acct_number = acct_num
AND set_of_books_id = vp_set_of_books_id ;
SELECT parent_flex_value
INTO vl_ussgl_acct_num
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 'X'
INTO vl_exists
FROM fv_facts_attributes
WHERE facts_acct_number = vl_ussgl_acct_num
AND set_of_books_id = vp_set_of_books_id ;
INSERT INTO FV_FACTS_TEMP
(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 ,
FACTS_REPORT_INFO ,
TBAL_FUND_VALUE ,
TBAL_INDICATOR ,
BUDGET_FUNCTION ,
ADVANCE_FLAG ,
TRANSFER_DEPT_ID ,
TRANSFER_MAIN_ACCT ,
YEAR_BUDGET_AUTH ,
period_activity ,
parent_sgl_acct_number,
PROGRAM_RPT_CAT_NUM,
PROGRAM_RPT_CAT_TXT,
SEGMENT1,
SEGMENT2)
VALUES (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 ,
'BLK_DTL' ,
NULL ,
v_tbal_fund_value ,
v_tbal_indicator ,
va_budget_function ,
va_advance_type_val ,
va_transfer_dept_id ,
va_transfer_main_acct ,
l_year_budget_auth ,
v_period_activity ,
vl_parent_sgl_acct_num,
va_prn_num,
va_prn_txt,
v_catb_program_value,
v_prn_program_value) ;
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;
l_module_name||'.select1', vp_errbuf) ;
SELECT UPPER(fts.resource_type),
def_indef_flag,
ffp.fund_category
INTO vl_resource_type,
va_def_indef_val,
vl_fund_category
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 ;
l_module_name||'.select2', vp_errbuf) ;
l_module_name||'.select2', vp_errbuf) ;
SELECT RPAD(substr(bea_category,1,5), 5)
INTO va_bea_category_val
from fv_fund_parameters
where fund_value = fund_val
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 ;
SELECT ussgl_enabled_flag,
reporting_type
INTO enabled_flag,
reporting_type
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = ussgl_acct_num ;
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 sgl_acct_num
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 'X'
INTO vl_exists
FROM fv_facts_attributes
WHERE facts_acct_number = sgl_acct_num
AND 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)
FROM gl_balances glb,
gl_code_combinations glcc
WHERE glb.code_combination_id = glcc.code_combination_id ';
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 ';
'Calc bal: '||vl_bal_select) ;
dbms_sql.parse(vl_bal_cursor, vl_bal_select, DBMS_SQL.V7) ;
SELECT flex_value_set_id
INTO vl_prg_val_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = v_segs_array(i).segment
AND application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = v_chart_of_accounts_id;
PROCEDURE build_appor_select (acct_number VARCHAR2,
fund_value VARCHAR2,
fiscal_year VARCHAR2,
appor_period VARCHAR2,
select_stmt OUT NOCOPY VARCHAR2)
IS
l_module_name VARCHAR2(200);
l_module_name := 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 ||
', nvl(DECODE(' || '''' || va_balance_type_val || '''' ||
',' || '''' || '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) '||
v_cohort_select ||
' FROM gl_balances glb,
gl_code_combinations glcc
WHERE glb.code_combination_id = GLCC.code_combination_id
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
GROUP BY 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 ||
', GLCC.' || v_fyr_segment_name || v_cohort_select;
END build_appor_select ;
SELECT DECODE(ffvl.description,NULL,RPAD(' ',25,' '),
RPAD(SUBSTR(ffvl.description,1,25),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 rowid, sgl_acct_number, tbal_fund_value,
fct_int_record_category, begin_end,
treasury_symbol_id
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 ;
INSERT INTO FV_FACTS_TEMP
(TREASURY_SYMBOL_ID ,
SGL_ACCT_NUMBER ,
PARENT_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 ,
FCT_INT_RECORD_CATEGORY ,
FCT_INT_RECORD_TYPE ,
FACTS_REPORT_INFO ,
--TBAL_FUND_VALUE , --Bug#4515907
TBAL_ACCT_NUM ,
TBAL_INDICATOR ,
YEAR_BUDGET_AUTH ,
BUDGET_FUNCTION ,
ADVANCE_FLAG ,
TRANSFER_DEPT_ID ,
TRANSFER_MAIN_ACCT ,
PERIOD_ACTIVITY ,
PROGRAM_RPT_CAT_NUM,
PROGRAM_RPT_CAT_TXT
-- SEGMENT1,
-- SEGMENT2
)
SELECT
TREASURY_SYMBOL_ID,
SGL_ACCT_NUMBER,
PARENT_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,
SUM(AMOUNT),
NULL,
'REPORTED_NEW',
'BLK_DTL',
NULL ,
-- DECODE(v_tbal_run_flag, 'Y', v_tbal_fund_value, NULL),
--tbal_fund_value, --Bug#4515907
NULL ,
TBAL_INDICATOR ,
YEAR_BUDGET_AUTH,
BUDGET_FUNCTION ,
ADVANCE_FLAG ,
TRANSFER_DEPT_ID,
TRANSFER_MAIN_ACCT,
--SUM(period_activity),
0, -- bug 5065974 (as period acitivity not needed for ATB)
PROGRAM_RPT_CAT_NUM,
PROGRAM_RPT_CAT_TXT
-- SEGMENT1,
-- SEGMENT2
FROM fv_facts_temp
WHERE fct_int_record_category = 'REPORTED'
AND fct_int_record_type = 'BLK_DTL'
AND treasury_symbol_id = v_treasury_symbol_id
GROUP BY TREASURY_SYMBOL_ID,
SGL_ACCT_NUMBER,
PARENT_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 ,
--TBAL_FUND_VALUE , --Bug#4515907
TBAL_INDICATOR ,
YEAR_BUDGET_AUTH,
BUDGET_FUNCTION ,
ADVANCE_FLAG ,
TRANSFER_DEPT_ID,
TRANSFER_MAIN_ACCT,
PROGRAM_RPT_CAT_NUM,
PROGRAM_RPT_CAT_TXT;
SELECT count(*) INTO l_count
FROM fv_facts_temp
WHERE begin_end = 'B'
AND sgl_acct_number = facts_temp_rec.sgl_acct_number
AND tbal_fund_value = facts_temp_rec.tbal_fund_value
AND treasury_symbol_id = facts_temp_rec.treasury_symbol_id
AND fct_int_record_category = 'REPORTED_NEW'
AND amount <> 0;
DELETE FROM fv_facts_temp
WHERE rowid = facts_temp_rec.rowid;
UPDATE fv_facts_temp
SET debit_credit = 'C'
WHERE amount < 0
AND fct_int_record_category = 'REPORTED_NEW'
AND treasury_symbol_id = v_treasury_symbol_id ;
UPDATE fv_facts_temp
SET debit_credit = 'D'
WHERE amount >= 0
AND fct_int_record_category = 'REPORTED_NEW'
AND treasury_symbol_id = v_treasury_symbol_id ;
SELECT fund_value,
DECODE(fund_category,'S','A','T','B',fund_category) fund_category
FROM fv_fund_parameters ffp
WHERE ffp.treasury_symbol_id = cv_ts_id
AND ffp.set_of_books_id = cv_sob_id
AND ffp.fund_category IN ('A', 'B', 'S', 'T');
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 = p_treasury_symbol_id
AND ffh.code_type = vl_code_type
AND ffh.set_of_books_id = p_sob_id
AND ffh.fund_value = fund_rec.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 = p_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'
AND fund_rec.fund_category = '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 = p_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'
AND fund_rec.fund_category = '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 = p_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'
AND fund_rec.fund_category = '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'
AND fund_rec.fund_category = '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 LPAD(TO_CHAR(reporting_code), 3, '0'), 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 LPAD(TO_CHAR(reporting_code), 3, '0'), 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 fv_facts_submission_s.nextval
INTO v3_req_id
FROM DUAL;
UPDATE fv_facts_submission
SET bulk_file_sub_id = v3_req_id,
submitted_by = fnd_global.user_name,
facts2_status = 'CREATING BULK FILE'
WHERE bulk_flag = 'Y';
UPDATE fv_facts_submission
SET run_mode = 'P',
submitted_by = NULL,
submitted_id = NULL,
facts2_status = NULL,
bulk_flag = 'N'
WHERE bulk_file_sub_id = v3_req_id ;