HR_PG_CURCNT_VW

(SQL View)
 Index Back

Position Headcount


SELECT POS.Position_Nbr , POS.EFFDT ,POS.MAX_HEAD_COUNT , CASE WHEN ( SELECT COUNT(*) FROM PS_Job B WHERE B.HR_STATUS = 'A' AND B.Position_Nbr <> ' ' AND B.Position_Nbr = POS.Position_Nbr AND B.Effdt = ( SELECT MAX(C.EffDt) FROM PS_Job C WHERE C.Emplid = B.Emplid AND C.EMPL_RCD = B.EMPL_RCD AND C.EffDt <= %CurrentDateIn) AND B.EffSeq = ( SELECT MAX(D.EffSeq) FROM PS_Job D WHERE D.Emplid = B.Emplid AND D.EMPL_RCD = B.EMPL_RCD AND D.Effdt = B.Effdt) GROUP BY B.Position_Nbr) IS NOT NULL THEN ( SELECT COUNT(*) FROM PS_Job B WHERE B.HR_STATUS = 'A' AND B.Position_Nbr <> ' ' AND B.Position_Nbr = POS.Position_Nbr AND B.Effdt = ( SELECT MAX(C.EffDt) FROM PS_Job C WHERE C.Emplid = B.Emplid AND C.EMPL_RCD = B.EMPL_RCD AND C.EffDt <= %CurrentDateIn) AND B.EffSeq = ( SELECT MAX(D.EffSeq) FROM PS_Job D WHERE D.Emplid = B.Emplid AND D.EMPL_RCD = B.EMPL_RCD AND D.Effdt = B.Effdt) GROUP BY B.Position_Nbr) ELSE 0 END Currheadcnt FROM PS_POSITION_DATA POS WHERE POS.EFFDT = ( SELECT MAX(C.EffDt) FROM PS_POSITION_DATA C WHERE C.Position_Nbr = POS.Position_Nbr AND C.EffDt <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 POSITION_NBR Character(8) VARCHAR2(8) NOT NULL Position Number

Default Value: 00000000

2 EFFDT Date(10) DATE NOT NULL Effective Date

Default Value: %date

3 MAX_HEAD_COUNT Number(4,0) SMALLINT NOT NULL Max Head Count

Default Value: 1

4 CURR_HEAD_COUNT Number(4,0) SMALLINT NOT NULL Current Head Count

Default Value: 0