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"