FV GTAS Transaction Register

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,
       PERIOD_YEAR,
       PERIOD_NUM,
       CANCELLATION_YEAR,
	   CANCEL_NUM,
	   ADJUSTMENT_PERIOD_FLAG,
       MAIN,
       SUB,
       EVENT,
       USSGL_ACCT,
       PERIOD,
       SUM(DEBIT),
       SUM(CREDIT),
       POSTED_DATE,
       GL_DATE,
       ATC,
       DRC,
       APC,
       ACB,
       PRC,
       FNF,
       TPA,
       TPM,
       NBC,
       CSC   		
      ,BEA
      ,BSC
      ,ENC
      ,CNC		
      ,BII
      ,PYA
      ,CCY
      ,PCI
	  ,DOCUMENT_NUMBER
	  ,DOCUMENT_SOURCE
	  ,DOCUMENT_CATEGORY
      ,CONCATENATED_SEGMENTS
      ,RTC
	  ,NGCF
      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
        ,(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 NVL((SELECT max(b.period_num)
              FROM gl_period_statuses_v b
             WHERE b.set_of_books_id = :P_LEDGER_ID
               AND b.application_id = 101
               AND cancellation_date BETWEEN b.start_date AND b.end_date
               AND b.period_num<(SELECT max(a.period_num)
                                   FROM gl_period_statuses_v a
                                  WHERE a.set_of_books_id =:P_LEDGER_ID
                                    AND a.application_id = 101
                                    AND cancellation_date BETWEEN a.start_date and a.end_date)),
									(SELECT c.period_num
                                                                                                  FROM gl_period_statuses_v c
                                                                                                 WHERE CANCELLATION_DATE BETWEEN c.start_date AND c.end_date
                                                                                                   AND c.set_of_books_id = :P_LEDGER_ID
                                                                                                   AND c.application_id = 101
                                                                                                   AND c.adjustment_period_flag= 'N')) from DUAL) CANCEL_NUM,
		(SELECT DECODE(tp.period_num,(
              SELECT max(b.period_num) FROM gl_period_statuses_v b
               WHERE b.set_of_books_id = :P_LEDGER_ID
                 AND b.application_id = 101
                 AND b.period_year=tp.period_year
                 AND adjustment_period_flag = 'Y'),'Y','N') FROM dual) ADJUSTMENT_PERIOD_FLAG
        ,FUND_GROUP_CODE				MAIN
        ,TAFS_SUB_ACCT					SUB
        ,SLA_HDR_EVENT_ID				EVENT
        ,USSGL_ACCOUNT				USSGL_ACCT
        ,PERIOD_NAME					PERIOD
        ,DEBIT						DEBIT
        ,CREDIT					CREDIT
        ,GL_POSTED_DATE				POSTED_DATE
        ,GL_DATE				      GL_DATE
        ,AUTHORITY_TYPE_CODE			ATC
        ,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		
        ,BUDGET_IMPACT_IND				BII
        ,PYA_CODE						PYA
        ,CREDIT_COHORT_YR				CCY
        ,PROGRAM_COST_IND				PCI
	    ,DOCUMENT_NUMBER
	    ,DOCUMENT_SOURCE
	    ,DOCUMENT_CATEGORY
        ,CONCATENATED_SEGMENTS
        ,REDUCTION_TYPE_CODE RTC
		,TB.NO_GTAS_CAN_TAS_FLAG        NGCF
        FROM 	FV_GTAS_TRX_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_TO_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),
       PERIOD_YEAR,
       PERIOD_NUM,
       CANCELLATION_YEAR,
	   CANCEL_NUM,
	   ADJUSTMENT_PERIOD_FLAG,
       MAIN,
       SUB,
       EVENT,
       USSGL_ACCT,
       PERIOD,
       POSTED_DATE,
       GL_DATE,
       ATC,
       DRC,
       APC,
       ACB,
       PRC,
       FNF,
       TPA,
       TPM,
       NBC,
       CSC   		
      ,BEA
      ,BSC
      ,ENC
      ,CNC		
      ,BII
      ,PYA
      ,CCY
      ,PCI
	  ,DOCUMENT_NUMBER
	  ,DOCUMENT_SOURCE
	  ,DOCUMENT_CATEGORY
	  ,CONCATENATED_SEGMENTS
	  ,RTC
	  ,NGCF
ORDER BY ATA,AID,BPOA, EPOA,AV,PERIOD_YEAR,
       PERIOD_NUM,
       CANCELLATION_YEAR, CANCEL_NUM, ADJUSTMENT_PERIOD_FLAG,MAIN,SUB,DOCUMENT_SOURCE,DOCUMENT_CATEGORY,DOCUMENT_NUMBER,USSGL_ACCT