FV GTAS Bulk File Interface
Description
Categories: BI Publisher
Application: Federal Financials
Source: GTAS Bulk File Interface Report
Short Name: FVGTINTATB
DB package:
Source: GTAS Bulk File Interface Report
Short Name: FVGTINTATB
DB package:
Run
FV GTAS Bulk File Interface and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT dept_transfer ,department_id ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL ELSE NULL END)ESTABLISHED_FISCAL_YR ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN end_year_avail WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN end_year_avail WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN end_year_avail WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL ELSE NULL END) end_year_avail ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN availability_type_code WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN availability_type_code WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN 'C' WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN availability_type_code WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN 'C' ELSE 'C' END) availability_type_code ,fund_group_code ,tafs_sub_acct ,ussgl_account ,authority_type_code ,direct_or_reimb_code ,appor_cat_code ,appor_cat_b_code ,program_rept_code ,bea_cat_code ,borr_src_code ,cust_non_cust ,fed_non_fed ,trading_partner_agency_id ,trading_partner_main_account ,new_bal_code ,cur_subsequent_code ,exch_non_exch ,pya_code ,budget_impact_ind ,credit_cohort_yr ,program_cost_ind ,SUM(amount) amount ,d_c_indicator ,begin_end ,reduction_type_code ,NO_GTAS_CAN_TAS_FLAG FROM (SELECT tas.dept_transfer ,tas.department_id ,tas.ESTABLISHED_FISCAL_YR ,tas.END_YEAR_AVAIL ,tas.availability_type_code ,NVL((SELECT adjustment_period_flag FROM GL_PERIOD_STATUSES_V WHERE set_of_books_id = :P_LEDGER AND application_id = 101 AND period_year = :P_PERIOD_YEAR AND period_num = :P_PERIOD_NUM AND adjustment_period_flag = 'Y'), 'N') adjustment_period_flag ,(SELECT period_year FROM gl_period_statuses WHERE tas.CANCELLATION_DATE BETWEEN start_date AND end_date AND set_of_books_id = :P_LEDGER AND application_id = 101 AND adjustment_period_flag = 'N') CANCELLATION_YEAR ,(SELECT period_num FROM gl_period_statuses WHERE tas.CANCELLATION_DATE BETWEEN start_date AND end_date AND set_of_books_id = :P_LEDGER AND application_id = 101 AND adjustment_period_flag = 'N') CANCEL_NUM ,tas.fund_group_code ,tas.tafs_sub_acct ,f1.ussgl_account ,f1.authority_type_code ,f1.direct_or_reimb_code ,f1.appor_cat_code ,f1.appor_cat_b_code ,f1.program_rept_code ,f1.bea_cat_code ,f1.borr_src_code ,f1.cust_non_cust ,f1.fed_non_fed ,f1.trading_partner_agency_id ,f1.trading_partner_main_account ,f1.new_bal_code ,f1.cur_subsequent_code ,f1.exch_non_exch ,f1.pya_code ,f1.budget_impact_ind ,f1.credit_cohort_yr ,f1.program_cost_ind ,f1.amount ,f1.d_c_indicator ,f1.begin_end ,f1.reduction_type_code ,tas.NO_GTAS_CAN_TAS_FLAG FROM fv_gtas_int_summary_temp f1, fv_treasury_symbols tas WHERE f1.treasury_symbol_id = tas.treasury_symbol_id AND f1.period_year = :P_PERIOD_YEAR AND f1.set_of_books_id = :P_LEDGER AND f1.period_num <= :P_PERIOD_NUM) GROUP BY dept_transfer ,department_id ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN ESTABLISHED_FISCAL_YR WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL ELSE NULL END) ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN end_year_avail WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN end_year_avail WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN end_year_avail WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN NULL ELSE NULL END) ,(CASE WHEN CANCELLATION_YEAR IS NULL OR NO_GTAS_CAN_TAS_FLAG='Y' THEN availability_type_code WHEN :P_PERIOD_YEAR < CANCELLATION_YEAR THEN availability_type_code WHEN :P_PERIOD_YEAR > CANCELLATION_YEAR AND NO_GTAS_CAN_TAS_FLAG='N' THEN 'C' WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN availability_type_code WHEN :P_PERIOD_YEAR = CANCELLATION_YEAR AND :P_PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NO_GTAS_CAN_TAS_FLAG='N' THEN 'C' ELSE 'C' END) ,fund_group_code ,tafs_sub_acct ,ussgl_account ,authority_type_code ,direct_or_reimb_code ,appor_cat_code ,appor_cat_b_code ,program_rept_code ,bea_cat_code ,borr_src_code ,cust_non_cust ,fed_non_fed ,trading_partner_agency_id ,trading_partner_main_account ,new_bal_code ,cur_subsequent_code ,exch_non_exch ,pya_code ,budget_impact_ind ,credit_cohort_yr ,program_cost_ind ,d_c_indicator ,begin_end ,reduction_type_code ,NO_GTAS_CAN_TAS_FLAG ORDER BY dept_transfer ,department_id ,established_fiscal_yr ,end_year_avail ,availability_type_code ,fund_group_code ,tafs_sub_acct ,ussgl_account ,authority_type_code ,direct_or_reimb_code ,appor_cat_code ,appor_cat_b_code ,program_rept_code ,bea_cat_code ,borr_src_code ,cust_non_cust ,fed_non_fed ,trading_partner_agency_id ,trading_partner_main_account ,new_bal_code ,cur_subsequent_code ,exch_non_exch ,pya_code ,budget_impact_ind ,credit_cohort_yr ,program_cost_ind ,d_c_indicator ,begin_end |
Parameter Name | SQL text | Validation | |
---|---|---|---|
SET_OF_BOOKS_ID |
|
LOV Oracle | |
To Period |
|
LOV Oracle | |
PERIOD_NUM |
|
Number | |
Period Year |
|
Number | |
Agency ID Low |
|
Char | |
Agency ID High |
|
Char | |
MAIN_ACCOUNT_LOW |
|
Char | |
Main Account High |
|
Char |