FV GTAS Transaction Register
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
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 |