FV GTAS Bulk File Interface

Description

Application: Federal Financials
Source: GTAS Bulk File Interface Report
Short Name: FVGTINTATB
DB package:

FV GTAS Bulk File Interface and other Oracle EBS reports with Blitz Report™ on our demo environment

Copy

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

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

Download

Related Blitz Reports

FV GTAS Transaction Register FV GTAS Trial Balance ECC G-Invoicing, Projects: G-Invoicing FV GTAS Interface Exception FV Data Act File C Activity FV GTAS Activity Balance Exception ECC Payables, AP Closing, SQL1 ECC Receivables, AR Closing, SQL5 ECC Receivables, AR Closing, SQL2

Blitz Report™

Blitz Report™ provides multiple benefits:

Perfectly formatted Excel reports
Create new or ad-hoc reports in minutes
User configurable reports
Includes seeded reports for all core areas
Toolkits for tuning, management and support
Data governance and audit tracking
Central report repository & version control
Imports BI-Publisher and Discoverer reports
EBS forms interface for user adoption

Find out more

Features

Download

FAQ