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"