Query 4

From DB Optimizer
Jump to: navigation, search

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
/