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"