GPFR_DUCS_COMVW

(SQL View)
 Index Back

Headcount per Company

Calculated during the DUCS process, including the adjusted values per company

SELECT C.COMPANY , 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_ESTAB_TBL_FRA D 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 A.GPFR_DUCS_R2_REF1 = D.URSSAF_NM_FRA AND D.ESTABID = C.ESTABID AND D.EFFDT = ( SELECT MAX(DD.EFFDT) FROM PS_ESTAB_TBL_FRA DD WHERE DD.ESTABID = D.ESTABID AND A.GPFR_DUCS_R2_REF1 = D.URSSAF_NM_FRA) 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 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 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 ) GROUP BY C.COMPANY, B.END_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
2 END_DT Date(10) DATE NOT NULL end date
3 GPFR_DUCS_R4_QTY01 Signed Number(9,2) DECIMAL(7,2) NOT NULL Quantity 1 for the DUCS EN4 Record