SELECT CONCAT(CONCAT(program, '-'), "CAManager") AS "eacgroup", ca1, ca2, ca3, wp, descrip, CONCAT('''', a.cecode) AS "apcecode", cedesc, "res", "FiscalDate", "direct", pmt, "StatusDate"

FROM

(

SELECT program, "CAManager", "WPManager", ca1, ca2, ca3, wp, pmt, descrip, cecode, "res", "FiscalDate", "StatusDate", SUM("direct") AS "direct"

FROM

(

SELECT tc.program, "CAManager", "WPManager", tc.ca1, tc.ca2, tc.ca3, tc.wp, tc.pmt, tc.descrip, tc.cecode, "res", "direct", "TphaseDate", "StatusDate", TO_CHAR(MIN("FiscalDate"), 'YYYY-MM-DD') AS "FiscalDate"

FROM

(

SELECT FirstGroup.program, "CAManager", "WPManager", FirstGroup.ca1, FirstGroup.ca2, FirstGroup.ca3, FirstGroup.wp, FirstGroup.pmt, FirstGroup.descrip, FirstGroup.cecode, "res", "TphaseDate", "StatusDate", SUM("direct") AS "direct"

FROM

(

SELECT DirectTP.program, "CAManager", "WPManager", DirectTP.ca1, DirectTP.ca2, DirectTP.ca3, DirectTP.wp, DirectTP.pmt, DirectTP.descrip, DirectTP.cecode, "res", "direct", "TphaseDate", TO_CHAR(p.statusdate, 'YYYY-MM-DD') AS "StatusDate"

FROM

                           program p,

(

SELECT program, ca1, ca2, ca3, manager AS "CAManager"

FROM cawp

WHERE TRIM(wp) IS NULL

AND program IN

(

'COBRAPROGRAMNAME'

)

) CAInfo,

(

SELECT tphase.program, manager AS "WPManager", ca1, ca2, ca3, wp, pmt, descrip, cecode,

CASE

WHEN HOURS <> 0 THEN 'HOURS'

WHEN DIRECT <> 0 AND HOURS = 0 THEN 'DIRECT'

END AS "res",

CASE

WHEN HOURS <> 0 THEN HOURS

WHEN DIRECT <> 0 AND HOURS = 0 THEN DIRECT

END AS "direct",

                                  df_date AS "TphaseDate"

FROM

                                  tphase, cawp

WHERE cawp.program IN

(

'COBRAPROGRAMNAME'

)

AND tphase.program IN

(

'COBRAPROGRAMNAME'

)

AND cawp.program = tphase.program

AND cawp.cawpid = tphase.cawpid

AND class IN

(

SELECT class

FROM costrepclass

WHERE program = 'COBRAPROGRAMNAME'

AND cost = 'EAC'

)

AND HOURS + DIRECT <> 0

AND df_date > TO_DATE('01/01/2019', 'mm/dd/yyyy')

AND cecode NOT IN ('0')

AND PMT NOT IN ('J')

) DirectTP

WHERE p.program = DirectTP.program

AND p.program = CAInfo.program 

AND DirectTP.program = CAInfo.program

AND DirectTP.ca1 = CAInfo.ca1                         

AND DirectTP.ca2 = CAInfo.ca2

AND DirectTP.ca3 = CAInfo.ca3

) FirstGroup

GROUP BY FirstGroup.program, "CAManager", "WPManager", FirstGroup.ca1, FirstGroup.ca2, FirstGroup.ca3, FirstGroup.wp, FirstGroup.pmt, FirstGroup.descrip, FirstGroup.cecode, "res", "TphaseDate", "StatusDate"

) tc

LEFT OUTER JOIN

(

SELECT fsc_date AS "FiscalDate"

FROM fiscdetl

WHERE fiscfile =

(

SELECT fisc_file

FROM program

WHERE program = 'COBRAPROGRAMNAME'

)

) cal

ON "TphaseDate" <= "FiscalDate"

GROUP BY tc.program, "CAManager", "WPManager", tc.ca1, tc.ca2, tc.ca3, tc.wp, tc.pmt, tc.descrip, tc.cecode, "res", "direct", "TphaseDate", "StatusDate"

) newd

GROUP BY program, "CAManager", "WPManager", ca1, ca2, ca3, wp, pmt, descrip, newd.cecode, "res", "FiscalDate", "StatusDate"

) a LEFT OUTER JOIN

(

SELECT cecode, cedesc

FROM calcdesc

WHERE calcfile =

(

SELECT calc_file

FROM program

WHERE program = 'COBRAPROGRAMNAME'

)

) f ON a.cecode = f.cecode

ORDER BY "eacgroup", program, ca1, ca2, ca3, wp, descrip, "apcecode", "FiscalDate"