FV GTAS Bulk File Interface

➜ Back to Library
Description
Categories: BI Publisher
Application: Federal Financials
Source: GTAS Bulk File Interface Report
Short Name: FVGTINTATB
DB package:

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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: