FV GTAS Transaction Register

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Federal Financials
Source: GTAS Transaction Register Report
Short Name: FVGTASTR
DB package: FV_GTAS_TRX_REGISTER
Run FV GTAS Transaction Register and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Ledger Name 
 
 LOV Oracle
Flexfield Low 
 
 Char
Flexfield High 
 
 Char
Start Period 
 
 LOV Oracle
End Period 
 
 LOV Oracle
Posted Date From 
 
 Date
Posted Date To 
 
 Date
Journal Source 
 
 LOV Oracle
Journal Category 
 
 LOV Oracle
Agency ID Low 
 
 LOV Oracle
Agency ID High 
 
 LOV Oracle
Main Account Low 
 
 LOV Oracle
Main Account High 
 
 LOV Oracle
Download
Related Blitz Reports
GL Balance by Account Hierarchy JE German Payables VAT Reconciliation Detail Report- Not Supported: Reserved For Future Use FV GTAS Trial Balance INV Inventory Item Upload CAC Accounting Period Status (Financials) PSB Budget Revisions Transaction Report By Responsibility COPY OF: INV Inventory Item Upload ECC Projects, Projects Costing: Transactions, SQL1 PSB Budget Revisions Transaction
Blitz Report™

Blitz Report™ provides multiple benefits: