FV GTAS Trial Balance

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