RS_POOL_DIST_VW

(SQL View)
 Index Back

Resource Distribution by Pool


SELECT A.EMPLID , B.NAME , A.BUSINESS_UNIT , A.DEPTID , F.DESCR , G.REGION_CD , C.POOL_ID , E.DESCR , C.MEMBER_ID , D.START_DATE FROM PS_JOB A , PS_PERSONAL_DATA B , PS_RS_POOL_MEMBER C , PS_RS_MEMBER_DATES D , PS_RS_RES_POOL E , PS_DEPT_TBL F , PS_RS_WORKER_TBL G WHERE A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND A.EMPLID = G.EMPLID AND C.POOL_ID = D.POOL_ID AND C.MEMBER_ID = D.MEMBER_ID AND C.POOL_ID = E.POOL_ID AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) AND A.EMPL_STATUS IN ('A','L','P','W') AND D.START_DATE = ( SELECT MAX(D_ED.START_DATE) FROM PS_RS_MEMBER_DATES D_ED WHERE D.POOL_ID = D_ED.POOL_ID AND D.MEMBER_ID = D_ED.MEMBER_ID AND D_ED.START_DATE <= %CurrentDateIn) AND A.SETID_DEPT = F.SETID AND A.DEPTID = F.DEPTID AND F.EFFDT = ( SELECT MAX(F1.EFFDT) FROM PS_DEPT_TBL F1 WHERE F.SETID = F1.SETID AND F.DEPTID = F1.DEPTID AND F1.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 NAME Character(50) VARCHAR2(50) NOT NULL Name
3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
4 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
5 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
6 REGION_CD Character(10) VARCHAR2(10) NOT NULL Identifies the Sales Region. Populated on the Item Table to identify the Sales Region the Item is applied to.
7 POOL_ID Character(10) VARCHAR2(10) NOT NULL Pool ID field.
8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
9 MEMBER_ID Number(6,0) INTEGER NOT NULL Member ID.
10 START_DATE Date(10) DATE Start Date for Gen Standing PO