SELECT A.AID,A.ATA,A.AV,A.BPOA,A.EPOA,A.MAIN,A.SUB,B.AMOUNT_LABEL AL,B.AMOUNT_NUMBER AN, SUM(B.AMOUNT) AMOUNT FROM (
SELECT TB.DEPARTMENT_ID AID
,TB.DEPT_TRANSFER ATA
,TB.AVAILABILITY_TYPE_CODE AV
,TB.ESTABLISHED_FISCAL_YR BPOA
,TB.END_YEAR_AVAIL EPOA
,TB.MAIN_ACCOUNT_CODE MAIN
,TB.SUB_ACCOUNT_CODE SUB,TB.TREASURY_SYMBOL_ID
FROM FV_DACT_FILEA_TEMP TB
WHERE TB.LEDGER_ID = :P_LEDGER_ID AND TB.TREASURY_SYMBOL_ID IS NOT NULL
AND TB.REPORTING_PERIOD = :P_PERIOD_YEAR
GROUP BY TB.DEPT_TRANSFER, TB.DEPARTMENT_ID,
TB.ESTABLISHED_FISCAL_YR, TB.END_YEAR_AVAIL,
TB.AVAILABILITY_TYPE_CODE,TB.MAIN_ACCOUNT_CODE,
TB.SUB_ACCOUNT_CODE,TB.TREASURY_SYMBOL_ID ) A,
FV_DACT_FILEA_TEMP B
WHERE A.TREASURY_SYMBOL_ID = B.TREASURY_SYMBOL_ID
GROUP BY A.AID,A.ATA,A.AV,A.BPOA,A.EPOA,A.MAIN,A.SUB,B.AMOUNT_LABEL,B.AMOUNT_NUMBER,B.SEQ_NUM
ORDER BY A.AID,A.ATA,A.AV,A.BPOA,A.EPOA,A.MAIN,A.SUB,B.SEQ_NUM |