Recent site activity

SQL Tuning‎ > ‎

wg


 SELECT 
    A.COMPANY
  , A.PAYGROUP
  , E.OFF_CYCLE
  , E.SEPCHK_FLAG
  , E.TAX_METHOD
  , E.TAX_PERIODS
  , C.RETROPAY_ERNCD
  , sum(C.AMOUNT_DIFF) SUM_AMOUNT
from PS_PAY_CALENDAR A
  , WB_JOB B
  , WB_RETROPAY_EARNS C
  , PS_RETROPAY_RQST D
  , PS_RETROPAYPGM_TBL E
where A.RUN_ID = 'PD2'
  and A.PAY_CONFIRM_RUN = 'N'
  and B.COMPANY = A.COMPANY
  and B.PAYGROUP = A.PAYGROUP
  and E.OFF_CYCLE = A.PAY_OFF_CYCLE_CAL
  and B.EFFDT = (SELECT 
/*+ qb_name(wb_hj) */
MAX(F.EFFDT)
      from WB_JOB F
      where F.EMPLID = B.EMPLID
        and F.EMPL_RCD# = B.EMPL_RCD#
        and F.EFFDT< = A.PAY_END_DT)
  and B.EFFSEQ = (SELECT MAX(G.EFFSEQ)
      from WB_JOB G
      where G.EMPLID = B.EMPLID
        and G.EMPL_RCD# = B.EMPL_RCD#
        and G.EFFDT = B.EFFDT)
  and C.EMPLID = B.EMPLID
  and C.EMPL_RCD# = B.EMPL_RCD#
  and C.RETROPAY_PRCS_FLAG = 'C'
  and C.RETROPAY_LOAD_SW = 'Y'
  and D.RETROPAY_SEQ_NO = C.RETROPAY_SEQ_NO
  and E.RETROPAY_PGM_ID = D.RETROPAY_PGM_ID
group by A.COMPANY
  , A.PAYGROUP
  , E.OFF_CYCLE
  , E.SEPCHK_FLAG
  , E.TAX_METHOD
  , E.TAX_PERIODS
  , C.RETROPAY_ERNCD
/

Comments