EMPLIDEMPL_RCDEFFDTDefault Value: %date
END_DTGPFR_DUCS_PAYVW(SQL View) |
Index Back |
|---|---|
Payroll headcount per companyUsed in a array in the payroll, giving the headcount in the company of each payee, based on the DUCS result tables. |
| SELECT D.EMPLID , D.EMPL_RCD , D.EFFDT , B.END_DT , SUM(A.GPFR_DUCS_R4_QTY01) FROM PS_GPFR_DUCS_DEB B , PS_GPFR_DUCS_R A , PS_ESTAB_TBL C , PS_JOB D , PS_ESTAB_TBL_FRA G , PS_GPFR_DUCS_R E WHERE B.GPFR_DUCS_DEB_SLIP IN ('913', '914') AND A.GPFR_DUCS_TYPE = '901' AND A.GPFR_DUCS_RECORD = 'EN1' AND A.GPFR_DUCS_R2_REF1 <> ' ' AND G.ESTABID = C.ESTABID AND G.EFFDT = C.EFFDT AND ((A.GPFR_DUCS_R2_REF1 = G.URSSAF_NM_FRA AND E.GPFR_DUCS_R2_REF1 = A.GPFR_DUCS_R2_REF1) OR (E.GPFR_DUCS_R2_REF1 = G.URSSAF_NM_FRA AND E.GPFR_DUCS_R2_REF1 <> A.GPFR_DUCS_R2_REF1)) AND A.GPFR_DUCS_NUMBER = B.GPFR_DUCS_NUMBER AND A.GPFR_DUCS_CD = B.GPFR_DUCS_CD AND A.GPFR_DUCS_TYPE = B.GPFR_DUCS_TYPE AND A.GPFR_DUCS_REG_FLG = B.GPFR_DUCS_REG_FLG AND A.GPFR_ROW_NUM = B.GPFR_ROW_NUM AND E.GPFR_DUCS_TYPE = '901' AND E.GPFR_DUCS_RECORD = 'EN1' AND E.GPFR_DUCS_R2_REF1 <> ' ' AND E.GPFR_DUCS_NUMBER = B.GPFR_DUCS_NUMBER AND E.GPFR_DUCS_CD = B.GPFR_DUCS_CD AND E.GPFR_DUCS_TYPE = B.GPFR_DUCS_TYPE AND E.GPFR_DUCS_REG_FLG = B.GPFR_DUCS_REG_FLG AND E.GPFR_ROW_NUM = B.GPFR_ROW_NUM AND C.EFF_STATUS = 'A' AND C.EFFDT <= B.END_DT AND C.EFFDT = ( SELECT MAX(MC.EFFDT) FROM PS_ESTAB_TBL MC WHERE MC.ESTABID = C.ESTABID AND MC.EFFDT <= B.END_DT) AND D.COMPANY = C.COMPANY AND D.ESTABID = C.ESTABID AND D.EFFDT <= B.END_DT AND D.EFFSEQ = ( SELECT MAX(MD.EFFSEQ) FROM PS_JOB MD WHERE MD.EMPLID = D.EMPLID AND MD.EMPL_RCD = D.EMPL_RCD AND MD.EFFDT = D.EFFDT) AND B.GPFR_ASS_DATETIME IN ( SELECT MAX(MB.GPFR_ASS_DATETIME) FROM PS_GPFR_DUCS_R MA , PS_GPFR_DUCS_DEB MB WHERE MB.GPFR_DUCS_DEB_SLIP IN ('913', '914') AND MA.GPFR_DUCS_TYPE = '901' AND MA.GPFR_DUCS_RECORD = 'EN1' AND MA.GPFR_DUCS_NUMBER = MB.GPFR_DUCS_NUMBER AND MA.GPFR_DUCS_CD = MB.GPFR_DUCS_CD AND MA.GPFR_DUCS_TYPE = MB.GPFR_DUCS_TYPE AND MA.GPFR_DUCS_REG_FLG = MB.GPFR_DUCS_REG_FLG AND MA.GPFR_ROW_NUM = MB.GPFR_ROW_NUM AND MA.GPFR_DUCS_R2_REF1 = A.GPFR_DUCS_R2_REF1 AND MB.END_DT = B.END_DT) AND B.GPFR_ASS_DATETIME IN ( SELECT MAX(MF.GPFR_ASS_DATETIME) FROM PS_GPFR_DUCS_R ME , PS_GPFR_DUCS_DEB MF WHERE MF.GPFR_DUCS_DEB_SLIP IN ('913', '914') AND ME.GPFR_DUCS_TYPE = '901' AND ME.GPFR_DUCS_RECORD = 'EN1' AND ME.GPFR_DUCS_NUMBER = MF.GPFR_DUCS_NUMBER AND ME.GPFR_DUCS_CD = MF.GPFR_DUCS_CD AND ME.GPFR_DUCS_TYPE = MF.GPFR_DUCS_TYPE AND ME.GPFR_DUCS_REG_FLG = MF.GPFR_DUCS_REG_FLG AND ME.GPFR_ROW_NUM = MF.GPFR_ROW_NUM AND ME.GPFR_DUCS_R2_REF1 = E.GPFR_DUCS_R2_REF1 AND MF.END_DT = B.END_DT) GROUP BY D.EMPLID, D.EMPL_RCD, D.EFFDT, B.END_DT |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Rcd Nbr |
| 3 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 4 | END_DT |
Date(10) | DATE | end date |
| 5 | GPFR_DUCS_WA_BAS | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Contribution Base in the DUCS WA |