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 |
