Recent site activity

Oracle: People Soft query

YouTube Video


Query Used in Example

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