SELECT manager As "eacgroup", ca1, ca2, ca3, wp, descrip, CONCAT('''',a.cecode) AS "cecode", cedesc, "result", "Date", "direct", pmt, program, "statusdate"

FROM

(

(

SELECT manager, d.*, TO_CHAR(p.statusdate, 'YYYY-MM-DD') AS "statusdate" from cawp, program p,

(

SELECT program, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "Date", SUM("direct") AS "direct"

FROM

(

SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "direct", df_date, TO_CHAR(MIN(fsc_date), 'YYYY-MM-DD') AS "Date"

FROM

(

SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", df_date, SUM(Hours) AS "direct"

FROM

(

SELECT cawp.program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, 'Hours' AS "result", Hours, df_date

FROM tphase, cawp

WHERE cawp.program = tphase.program

AND cawp.cawpid = tphase.cawpid

AND cawp.program = 'COBRAPROGRAMNAME'

AND tphase.program = 'COBRAPROGRAMNAME'

AND class IN

(

SELECT class

FROM costrepclass

WHERE program = 'COBRAPROGRAMNAME'

AND cost = 'EAC'

)

AND Hours <> 0

)

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", df_date

) tc

LEFT OUTER JOIN

(

SELECT fsc_date

FROM fiscdetl

WHERE fiscfile =

(

SELECT fisc_file

FROM program

WHERE program = 'COBRAPROGRAMNAME'

)

) cal

ON tc.df_date <= cal.fsc_date

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "direct", df_date

) newd

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, newd.cecode, "result", "Date"

) d

WHERE d.program = p.program

AND d.program = cawp.program

AND cawp.ca1 = d.ca1

AND cawp.ca2 = d.ca2

AND cawp.ca3 = d.ca3

AND TRIM(cawp.wp) IS NULL

AND cawp.program = 'COBRAPROGRAMNAME'

GROUP BY manager, p.statusdate, d.program, d.ca1, d.ca2, d.ca3, d.wp, d.pmt, d.descrip, d.cecode, "result", "Date", "direct"

)

UNION ALL

(

SELECT manager, d.*, TO_CHAR(p.statusdate, 'YYYY-MM-DD') AS "statusdate" from cawp, program p,

(

SELECT program, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "Date", SUM("direct") AS "direct"

FROM

(

SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "direct", df_date, TO_CHAR(MIN(fsc_date), 'YYYY-MM-DD') AS "Date"

FROM

(

SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", df_date, SUM(ODC) AS "direct"

FROM

(

SELECT cawp.program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, 'ODC' AS "result", ODC, df_date

FROM tphase, cawp

WHERE cawp.program = tphase.program

AND cawp.cawpid = tphase.cawpid

AND cawp.program = 'COBRAPROGRAMNAME'

AND tphase.program = 'COBRAPROGRAMNAME'

AND class IN

(

SELECT class

FROM costrepclass

WHERE program = 'COBRAPROGRAMNAME'

AND cost = 'EAC'

)

AND ODC <> 0

)

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", df_date

) tc

LEFT OUTER JOIN

(

SELECT fsc_date

FROM fiscdetl

WHERE fiscfile =

(

SELECT fisc_file

FROM program

WHERE program = 'COBRAPROGRAMNAME'

)

) cal

ON tc.df_date <= cal.fsc_date

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "direct", df_date

) newd

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, newd.cecode, "result", "Date"

) d

WHERE d.program = p.program

AND d.program = cawp.program

AND cawp.ca1 = d.ca1

AND cawp.ca2 = d.ca2

AND cawp.ca3 = d.ca3

AND TRIM(cawp.wp) IS NULL

AND cawp.program = 'COBRAPROGRAMNAME'

GROUP BY manager, p.statusdate, d.program, d.ca1, d.ca2, d.ca3, d.wp, d.pmt, d.descrip, d.cecode, "result", "Date", "direct"

)

UNION ALL

(

SELECT manager, d.*, TO_CHAR(P.statusdate, 'YYYY-MM-DD') AS "statusdate" from cawp, program p,

(

SELECT program, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "Date", SUM("direct") AS "direct"

FROM

(

SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "direct", df_date, TO_CHAR(MIN(fsc_date), 'YYYY-MM-DD') AS "Date"

FROM

(

SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", df_date, SUM(MATERIAL) AS "direct"

FROM

(

SELECT cawp.program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, 'MATERIAL' AS "result", MATERIAL, df_date

FROM tphase, cawp

WHERE cawp.program = tphase.program

AND cawp.cawpid = tphase.cawpid

AND cawp.program = 'COBRAPROGRAMNAME'

AND tphase.program = 'COBRAPROGRAMNAME'

AND class IN

(

SELECT class

FROM costrepclass

WHERE program = 'COBRAPROGRAMNAME'

AND cost = 'EAC'

)

AND MATERIAL <> 0

)

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", df_date

) tc

LEFT OUTER JOIN

(

SELECT fsc_date

FROM fiscdetl

WHERE fiscfile =

(

SELECT fisc_file

FROM program

WHERE program = 'COBRAPROGRAMNAME'

)

) cal

ON tc.df_date <= cal.fsc_date

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "direct", df_date

) newd

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, newd.cecode, "result", "Date"

) d

WHERE d.program = p.program

AND d.program = cawp.program

AND cawp.ca1 = d.ca1

AND cawp.ca2 = d.ca2

AND cawp.ca3 = d.ca3

AND TRIM(cawp.wp) IS NULL

AND cawp.program = 'COBRAPROGRAMNAME'

GROUP BY manager, p.statusdate, d.program, d.ca1, d.ca2, d.ca3, d.wp, d.pmt, d.descrip, d.cecode, "result", "Date", "direct"

)

UNION ALL

(

SELECT manager, d.*, TO_CHAR(p.statusdate, 'YYYY-MM-DD') AS "statusdate" from cawp, program p,

(

SELECT program, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "Date", SUM("direct") AS "direct"

FROM

(

SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "direct", df_date, TO_CHAR(MIN(fsc_date), 'YYYY-MM-DD') AS "Date"

FROM

(

SELECT program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", df_date, SUM(SUBCONT) AS "direct"

FROM

(

SELECT cawp.program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, 'SUBCONT' AS "result", SUBCONT, df_date

FROM tphase, cawp

WHERE cawp.program = tphase.program

AND cawp.cawpid = tphase.cawpid

AND cawp.program = 'COBRAPROGRAMNAME'

AND tphase.program = 'COBRAPROGRAMNAME'

AND class IN

(

SELECT class

FROM costrepclass

WHERE program = 'COBRAPROGRAMNAME'

AND cost = 'EAC'

)

AND SUBCONT <> 0

)

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", df_date

) tc

LEFT OUTER JOIN

(

SELECT fsc_date

FROM fiscdetl

WHERE fiscfile =

(

SELECT fisc_file

FROM program

WHERE program = 'COBRAPROGRAMNAME'

)

) cal

ON tc.df_date <= cal.fsc_date

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, cecode, "result", "direct", df_date

) newd

GROUP BY program, manager, ca1, ca2, ca3, wp, pmt, descrip, newd.cecode, "result", "Date"

) d

WHERE d.program = p.program

AND d.program = cawp.program

AND cawp.ca1 = d.ca1

AND cawp.ca2 = d.ca2

AND cawp.ca3 = d.ca3

AND TRIM(cawp.wp) IS NULL

AND cawp.program = 'COBRAPROGRAMNAME'

GROUP BY manager, p.statusdate, d.program, d.ca1, d.ca2, d.ca3, d.wp, d.pmt, d.descrip, d.cecode, "result", "Date", "direct"

)

) 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

WHERE a.cecode NOT IN ('0')

ORDER BY "eacgroup", program, ca1, ca2, ca3, wp, descrip, a.cecode, "Date"