FV GTAS Transaction Register

Description
Categories: BI Publisher, Financials
Application: Federal Financials
Source: GTAS Transaction Register Report
Short Name: FVGTASTR
DB package: FV_GTAS_TRX_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
Parameter Name SQL text Validation
Chart of Accounts ID
 
LOV Oracle
Main Account High
 
LOV Oracle
Main Account Low
 
LOV Oracle
Agency ID High
 
LOV Oracle
Agency ID Low
 
LOV Oracle
Journal Category
 
LOV Oracle
Journal Source
 
LOV Oracle
Posted Date To
 
Date
Posted Date From
 
Date
End Period
 
LOV Oracle
Start Period
 
LOV Oracle
Flexfield High
 
Flexfield Low
 
Ledger Name
 
LOV Oracle