EMPLIDEMPL_RCDCWR=Contingent Worker
EMP=Employee
POI=Person of Interest
HR_SRCH_PER_ORG(SQL View) |
Index Back |
|---|---|
Per OrgPer Org |
| SELECT a.emplid ,a.empl_rcd ,a.per_org ,x1.xlatlongname ,m1.MESSAGE_TEXT FROM ps_per_org_asgn a ,psxlatitem x1 ,PSMSGCATDEFN m1 WHERE x1.fieldname='PER_ORG' AND x1.fieldvalue=a.PER_ORG AND X1.EFFDT=( SELECT MAX(X11.EFFDT) FROM PSXLATITEM X11 WHERE X11.FIELDNAME=X1.FIELDNAME AND X11.FIELDVALUE=X1.FIELDVALUE AND X11.EFFDT<=%CurrentDateIn) AND X1.EFF_STATUS='A' AND m1. message_set_nbr=1000 AND m1.message_nbr=1744 UNION SELECT b.emplid ,0 ,'POI' ,m21.message_text ,m2.message_text FROM ps_per_poi_type b , PSMSGCATDEFN m2 ,PSMSGCATDEFN m21 WHERE m2. message_set_nbr=1000 AND m2.message_nbr=1745 AND m21. message_set_nbr=1000 AND m21.message_nbr=1747 AND NOT EXISTS ( SELECT 'X' FROM ps_per_org_asgn b1 WHERE b1.emplid=b.emplid) UNION SELECT c.EMPLID ,0 ,'NON' ,m3.message_text ,m3.message_text FROM PS_PERSON C ,PSMSGCATDEFN m3 WHERE m3. message_set_nbr=1000 AND m3.message_nbr=1746 AND NOT EXISTS ( SELECT 'X' FROM ps_per_org_asgn c1 WHERE c1.emplid=c.emplid) AND NOT EXISTS ( SELECT 'X' FROM ps_per_poi_type c2 WHERE c2.emplid=c.emplid) |
| # | 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 Record |
| 3 | PER_ORG | Character(3) | VARCHAR2(3) NOT NULL |
Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker EMP=Employee POI=Person of Interest |
| 4 | HR_SRCH_PER_ORG | Character(50) | VARCHAR2(50) NOT NULL | Description for the PER_ORG field. |
| 5 | PER_ORG_DESCR | Character(50) | VARCHAR2(50) NOT NULL | Description for the PER_ORG field. |