SELECT CAL_RUN_ID ,CAL_ID , COMPANY , COUNT(*) FROM PS_JOB A , PS_GP_CAL_RUN_DTL B WHERE A.HR_STATUS = 'A' AND A.GP_PAYGROUP = B.GP_PAYGROUP AND B.COUNTRY = 'FRA' AND A.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_JOB X WHERE A.EMPLID = X.EMPLID AND A.EMPL_RCD = X.EMPL_RCD AND X.EFFDT <= %Sql(FUNCLIB_HR_DATE_DAY_ADD, %Sql(FUNCLIB_HR_DATE_YEAR_BEGIN, B.PRD_END_DT), -1) ) AND A.EFFSEQ = ( SELECT MAX(X.EFFSEQ) FROM PS_JOB X WHERE A.EMPLID = X.EMPLID AND A.EMPL_RCD = X.EMPL_RCD AND A.EFFDT = X.EFFDT) AND CAL_IDNT_TS IS NOT NULL GROUP BY CAL_RUN_ID,CAL_ID , COMPANY
|