GPFR_DUCS_EN4VW

(SQL View)
 Index Back

Headcount per Establishment

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

SELECT C.ESTABID , B.END_DT , 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 = C.EFFDT 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 )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
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