Query 4

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

/