DARTS : UM_EARN_ACCT_VW

SELECT /*+ index(c ps_pers_nid ) */
 A.COMPANY,
 A.PAYGROUP,
 A.PAY_END_DT,
 A.OFF_CYCLE,
 A.ERNCD,
 A.FISCAL_YEAR,
 A.ACCOUNTING_PERIOD,
 A.ACCT_CD,
 A.DEPTID,
 A.POSITION_NBR,
 A.JOBCODE,
 C.NATIONAL_ID,
 B.NAME,
 B.EMPLID,
 A.EMPL_RCD,
 A.REVERSED,
 NULL,
 ' ',
 ' ',
 SUM(A.EARNINGS)
  FROM PS_PAY_ERN_DIST A, PS_PAY_CHECK B, PS_PERS_NID C
 WHERE A.COMPANY = B.COMPANY
   AND A.PAYGROUP = B.PAYGROUP
   AND A.PAY_END_DT = B.PAY_END_DT
   AND A.OFF_CYCLE = B.OFF_CYCLE
   AND A.PAGE_NUM = B.PAGE_NUM
   AND A.LINE_NUM = B.LINE_NUM
   AND A.SEPCHK = B.SEPCHK
   AND C.EMPLID = B.EMPLID
   AND C.PRIMARY_NID = 'Y'
   AND C.NATIONAL_ID_TYPE = 'PR'
 GROUP BY A.COMPANY,
          A.PAYGROUP,
          A.PAY_END_DT,
          A.OFF_CYCLE,
          A.ERNCD,
          A.FISCAL_YEAR,
          A.ACCOUNTING_PERIOD,
          A.ACCT_CD,
          A.DEPTID,
          A.POSITION_NBR,
          A.JOBCODE,
          C.NATIONAL_ID,
          B.NAME,
          B.EMPLID,
          A.EMPL_RCD,
          A.REVERSED
UNION
SELECT /*+ index(f ps_pers_nid) */
 D.COMPANY,
 D.PAYGROUP,
 D.PAY_END_DT,
 D.OFF_CYCLE,
 D.ERNCD,
 D.FISCAL_YEAR,
 D.ACCOUNTING_PERIOD,
 D.ACCT_CD,
 D.DEPTID,
 D.POSITION_NBR,
 D.JOBCODE,
 F.NATIONAL_ID,
 E.NAME,
 E.EMPLID,
 D.EMPL_RCD,
 ' ',
 D.RETRO_MOD_DT,
 D.BATCH_ID,
 D.STATUSSTRING,
 SUM(D.EARNINGS)
  FROM PS_UM_RETRO_DTL D, PS_PAY_CHECK E, PS_PERS_NID F
 WHERE D.COMPANY = E.COMPANY
   AND D.PAYGROUP = E.PAYGROUP
   AND D.PAY_END_DT = E.PAY_END_DT
   AND D.OFF_CYCLE = E.OFF_CYCLE
   AND D.PAGE_NUM = E.PAGE_NUM
   AND D.LINE_NUM = E.LINE_NUM
   AND D.SEPCHK = E.SEPCHK
   AND F.EMPLID = E.EMPLID
   AND F.PRIMARY_NID = 'Y'
   AND D.STATUSSTRING <> 'O'
   AND D.PROCESSED = 'Y'
   AND F.PRIMARY_NID = 'Y'
   AND F.NATIONAL_ID_TYPE = 'PR'
 GROUP BY D.COMPANY,
          D.PAYGROUP,
          D.PAY_END_DT,
          D.OFF_CYCLE,
          D.ERNCD,
          D.FISCAL_YEAR,
          D.ACCOUNTING_PERIOD,
          D.ACCT_CD,
          D.DEPTID,
          D.POSITION_NBR,
          D.JOBCODE,
          F.NATIONAL_ID,
          E.NAME,
          E.EMPLID,
          D.EMPL_RCD,
          ' ',
          D.RETRO_MOD_DT,
          D.BATCH_ID,
          D.STATUSSTRING;