WITH FILEC_GRP AS(SELECT distinct C.DEPARTMENT_ID AID,
C.DEPT_TRANSFER ATA,
C.AVAILABILITY_TYPE_CODE AV,
C.ESTABLISHED_FISCAL_YR BPOA,
C.END_YEAR_AVAIL EPOA,C.MAIN_ACCOUNT_CODE MAIN,
C.SUB_ACCOUNT_CODE SUB,C.TREASURY_SYMBOL_ID,
C.PROGRAM_ACTIVITY_RPT_CODE PARC,
C.PROGRAM_ACTIVITY_RPT_DESC PARD,
C.PROGRAM_OBJECT_CLASS POC,
C.REIMB_INDICATOR RI,
C.PIID PIID,
C.PARENT_AWARD_ID PARENT_AWARD_ID,
C.FAIN FAIN,
C.URI URI
FROM
FV_DACT_FILEC_TEMP C
where c.ledger_id=:P_LEDGER_ID
and c.reporting_period = :P_PERIOD_YEAR)
SELECT FG.AID,FG.ATA,FG.AV,FG.BPOA,FG.EPOA,FG.MAIN,FG.SUB,FG.TREASURY_SYMBOL_ID,
FG.PIID, FG.PARENT_AWARD_ID,FG.FAIN,FG.URI,FG.PARC, FG.PARD,FG.POC,FG.RI,fdv.JE_SOURCE JS,
fdv.JE_CATEGORY JC,fdv.DOC_NUM DN,gjh.default_effective_date GD,sum(fdv.amount) AMOUNT
FROM
FV_DACT_PERIOD_BALANCES_V fdv,
FV_DACT_REPORTED_JE_HEADERS fgp,
gl_je_headers gjh,
FILEC_GRP FG
where fdv.set_of_books_id=:P_LEDGER_ID
and fdv.period_year=:P_PERIOD_YEAR
and fdv.period_num >= :PERIOD_LOW
and fdv.period_num <= :P_PERIOD_NUM
and fdv.dact_report_ind in ('P','F')
and gjh.je_header_id=fdv.je_header_id
and fdv.je_header_id = fgp.je_header_id
and fdv.treasury_symbol_id=FG.TREASURY_SYMBOL_ID
and NVL(fdv.file_c_flag,'Y')='Y'
and NVL(fdv.award_flag,'Y')='Y'
and NVL(DECODE(:P_INCL_PROGACT,'Y',fdv.PROGRAM_ACTIVITY_RPT_CODE,NULL),'-99')=NVL(FG.PARC,'-99')
and NVL(DECODE(:P_INCL_PROGACT,'Y',fdv.PROGRAM_ACTIVITY_RPT_DESC,NULL),'-99')=NVL(FG.PARD,'-99')
and NVL(fdv.PROGRAM_OBJECT_CLASS,'-99')=NVL(FG.POC,'-99')
and NVL(fdv.DIRECT_OR_REIMB_CODE,'-99')=NVL(FG.RI,'-99')
and NVL(fdv.PIID,'-99')=NVL(FG.PIID,'-99')
and NVL(fdv.PARENT_AWARD_ID,'-99')=NVL(FG.PARENT_AWARD_ID,'-99')
and NVL(fdv.FAIN,'-99')=NVL(FG.FAIN,'-99')
and NVL(fdv.URI,'-99')=NVL(FG.URI,'-99')
group by FG.AID,FG.ATA,FG.AV,FG.BPOA,FG.EPOA,FG.MAIN,FG.SUB,FG.TREASURY_SYMBOL_ID,
FG.PIID, FG.PARENT_AWARD_ID,FG.FAIN,FG.URI,FG.PARC, FG.PARD,FG.POC,FG.RI,
fdv.JE_SOURCE,fdv.JE_CATEGORY,fdv.DOC_NUM,gjh.default_effective_date
order by FG.AID,FG.ATA,FG.AV,FG.BPOA,FG.EPOA,FG.MAIN,FG.SUB,FG.TREASURY_SYMBOL_ID,
FG.PIID, FG.PARENT_AWARD_ID,FG.FAIN,FG.URI,FG.PARC, FG.PARD,FG.POC,FG.RI,
fdv.JE_SOURCE,fdv.JE_CATEGORY,fdv.DOC_NUM,gjh.default_effective_date |
