FV GTAS Trial Balance

Description
Categories: BI Publisher
Columns: Ata, Aid, Bpoa, Epoa, Av, Main, Sub, Acct No, Drc, Apc ...
Application: Federal Financials
Source: GTAS Trial Balance Report
Short Name: FVGTASTB
DB package: FV_GTAS_TBAL_TRX_PKG
SELECT ATA,
	   AID,
	   (CASE
	       WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y'  THEN BPOA
		   WHEN PERIOD_YEAR < CANCELLATION_YEAR  THEN BPOA
		   WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN BPOA
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL
		   ELSE NULL
	   END) BPOA,
       (CASE
	       WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN EPOA
		   WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN EPOA
		   WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN EPOA
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL
		   ELSE NULL
	   END) EPOA,
       (CASE
	       WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN AV
		   WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN AV
		   WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN 'C'
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN AV
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN 'C'
		   ELSE 'C'
	   END) AV,	   
	   MAIN,
	   SUB,
	   ACCT_NO,
	   DRC,
	   APC,
	   ACB,
	   PRC,
	   FNF,
	   TPA,
	   TPM,
	   NBC,
	   CSC,
	   BEA,
	   BSC,
	   ENC,
	   CNC,
	   ATC,
	   BII,
	   PYA,
	   CCY,
	   PCI,
	   sum(BEGINNING_BALANCE) 		BEGINNING_BALANCE
		,sum(DR_AMOUNT)					DR_AMOUNT
		,sum(CR_AMOUNT)		 			CR_AMOUNT
		,sum(ENDING_BALANCE)			ENDING_BALANCE
		,RTC		
	   FROM (SELECT      DEPT_TRANSFER					ATA
		,DEPARTMENT_ID					AID
		,ESTABLISHED_FISCAL_YR			BPOA
		,END_YEAR_AVAIL 				EPOA
		,AVAILABILITY_TYPE_CODE			AV
		,TP.PERIOD_YEAR
		,TP.PERIOD_NUM
		,TP.ADJUSTMENT_PERIOD_FLAG
		, (SELECT period_year
		  FROM gl_period_statuses
		 WHERE CANCELLATION_DATE BETWEEN start_date AND end_date
		   AND set_of_books_id = :P_LEDGER_ID
		   AND application_id = 101
		   AND adjustment_period_flag = 'N') CANCELLATION_YEAR
		,(SELECT period_num
		  FROM gl_period_statuses
		 WHERE CANCELLATION_DATE BETWEEN start_date AND end_date
		   AND set_of_books_id = :P_LEDGER_ID
		   AND application_id = 101
		   AND adjustment_period_flag = 'N') CANCEL_NUM
		,FUND_GROUP_CODE				MAIN
		,TAFS_SUB_ACCT					SUB
		,ACCOUNT_NUMBER					ACCT_NO
		,DIRECT_OR_REIMB_CODE			DRC
		,APPOR_CAT_CODE					APC
		,APPOR_CAT_B_CODE				ACB
		,PROGRAM_REPT_CODE				PRC
		,FED_NON_FED					FNF
		,TRADING_PARTNER_AGENCY_ID		TPA
		,TRADING_PARTNER_MAIN_ACCOUNT	TPM
		,NEW_BAL_CODE					NBC
		,CUR_SUBSEQUENT_CODE			CSC
		,BEA_CAT_CODE					BEA
		,BORR_SRC_CODE 					BSC
		,EXCH_NON_EXCH					ENC
		,CUST_NON_CUST					CNC
		,AUTHORITY_TYPE_CODE			ATC
		,BUDGET_IMPACT_IND				BII
		,PYA_CODE						PYA
		,CREDIT_COHORT_YR				CCY
		,PROGRAM_COST_IND				PCI
		,BEGINNING_BALANCE 		BEGINNING_BALANCE
		,DR_AMOUNT					DR_AMOUNT
		,CR_AMOUNT		 			CR_AMOUNT
		,ENDING_BALANCE			ENDING_BALANCE
		,REDUCTION_TYPE_CODE			RTC
		,TB.NO_GTAS_CAN_TAS_FLAG        NGCF
FROM 	FV_GTAS_TB_V 	TB, ( SELECT period_year, period_num, adjustment_period_flag
		  FROM gl_period_statuses 
		 WHERE set_of_books_id = :P_LEDGER_ID
		   AND application_id = 101 
		   AND period_name = :P_PERIOD) TP
WHERE 	TB.SET_OF_BOOKS_ID = :P_LEDGER_ID)
GROUP BY  	ATA,
	   AID,
	   (CASE
	       WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN BPOA
		   WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN BPOA
		   WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN BPOA
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL
		   ELSE NULL
	   END) ,
       (CASE
	       WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN EPOA
		   WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN EPOA
		   WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN NULL
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN EPOA
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN NULL
		   ELSE NULL
	   END) ,
       (CASE
	       WHEN CANCELLATION_YEAR IS NULL OR NGCF = 'Y' THEN AV
		   WHEN PERIOD_YEAR < CANCELLATION_YEAR THEN AV
		   WHEN PERIOD_YEAR > CANCELLATION_YEAR AND NGCF = 'N' THEN 'C'
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM <= CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'N' THEN AV
		   WHEN PERIOD_YEAR = CANCELLATION_YEAR AND PERIOD_NUM > CANCEL_NUM AND ADJUSTMENT_PERIOD_FLAG = 'Y' AND NGCF = 'N' THEN 'C'
		   ELSE 'C'
	   END) ,	   
	   MAIN,
	   SUB,
	   ACCT_NO,
	   DRC,
	   APC,
	   ACB,
	   PRC,
	   FNF,
	   TPA,
	   TPM,
	   NBC,
	   CSC,
	   BEA,
	   BSC,
	   ENC,
	   CNC,
	   ATC,
	   BII,
	   PYA,
	   CCY,
	   PCI			
		,RTC	       
HAVING (sum(BEGINNING_BALANCE) <> 0 OR
        sum(DR_AMOUNT) <> 0 OR
        sum(CR_AMOUNT) <> 0 OR
        sum(ENDING_BALANCE) <>0 )
ORDER BY ATA,AID,BPOA,EPOA,AV,MAIN,SUB,ACCT_NO
Parameter Name SQL text Validation
Ledger Name
 
LOV Oracle
Flexfield Low
 
Flexfield High
 
Period Name
 
LOV Oracle
Agency ID Low
 
LOV Oracle
Agency ID High
 
LOV Oracle
Main Account Low
 
LOV Oracle
Main Account High
 
LOV Oracle
Treasury Account Symbol
 
LOV Oracle
Chart of Accounts ID
 
LOV Oracle