PER_ORG_ASGN_VW(SQL View) |
Index Back |
|---|---|
Per Org/Inst/Current JobView of PER_ORG_ASGN joined to PER_ORG_INST, and the current row in JOB and JOB_DATES The HIRE, LAST_HIRE_DT come from the Controlling instance for the EMPL_RCD |
| SELECT A.EMPLID ,A.EMPL_RCD ,A.PER_ORG , A.POI_TYPE ,A.BENEFIT_RCD_NBR ,A.ORG_INSTANCE_ERN ,J.HR_STATUS ,J.EMPL_STATUS ,A.HOME_HOST_CLASS ,ORG.HIRE_DT ,ORG.LAST_HIRE_DT ,ORG.TERMINATION_DT ,J.ASGN_START_DT ,J.ASGN_END_DT ,J.LST_ASGN_START_DT ,A.CMPNY_SENIORITY_DT ,A.SERVICE_DT ,A.SENIORITY_PAY_DT ,A.PROF_EXPERIENCE_DT ,A.LAST_VERIFICATN_DT ,A.PROBATION_DT ,A.LAST_INCREASE_DT ,A.OWN_5PERCENT_CO ,A.BUSINESS_TITLE , J.LAST_DATE_WORKED ,J.EXPECTED_RETURN_DT , J.EXPECTED_END_DATE ,A.POSITION_PHONE , J.EFFDT , J.EFFSEQ FROM PS_PER_ORG_ASGN A , PS_JOB J , PS_JOB ORG WHERE A.EMPLID = J.EMPLID AND A.EMPL_RCD = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE J.EMPLID = JOB2.EMPLID AND J.EMPL_RCD = JOB2.EMPL_RCD AND (( JOB2.EFFDT <= %CurrentDateIn) OR (JOB2.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = J.EMPLID AND J2.EMPL_RCD = J.EMPL_RCD) ) )) AND J.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = J.EMPLID AND JOB3.EMPL_RCD = J.EMPL_RCD AND JOB3.EFFDT = J.EFFDT ) AND A.EMPLID = ORG.EMPLID AND A.ORG_INSTANCE_ERN = ORG.EMPL_RCD AND ORG.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB ORG2 WHERE ORG.EMPLID = ORG2.EMPLID AND ORG.EMPL_RCD = ORG2.EMPL_RCD AND (( ORG2.EFFDT <= %CurrentDateIn) OR (ORG2.EFFDT > %CurrentDateIn AND %CurrentDateIn < ( SELECT MIN(J2ORG.EFFDT) FROM PS_JOB J2ORG WHERE J2ORG.EMPLID = ORG.EMPLID AND J2ORG.EMPL_RCD = ORG.EMPL_RCD) ) )) AND ORG.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB ORG3 WHERE ORG3.EMPLID = ORG.EMPLID AND ORG3.EMPL_RCD = ORG.EMPL_RCD AND ORG3.EFFDT = ORG.EFFDT ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL |
Empl Record
Default Value: 0 |
| 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 Default Value: POI Prompt Table: PER_ORG_VW |
| 4 | POI_TYPE | Character(5) | VARCHAR2(5) NOT NULL |
Defines the Transactional usage of POIs in HRMS.
Prompt Table: POI_TYPE_TBL |
| 5 | BENEFIT_RCD_NBR | Number(3,0) | SMALLINT NOT NULL | Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number. |
| 6 | ORG_INSTANCE_ERN | Number(3,0) | SMALLINT NOT NULL | Indicates which EMPL_RCD (ERN) is considered the Organizational Instance for this particular ERN. An Organizational Instance defines an Instance of Employment or an Instance of Contingent Workforce. Hire, Termination, and Service dates are captured at the Organiational Instance level. |
| 7 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 8 | EMPL_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Payroll Status
A=Active D=Deceased L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration |
| 9 | HOME_HOST_CLASS | Character(1) | VARCHAR2(1) NOT NULL |
Home/Host Classification
M=Home S=Host Default Value: M |
| 10 | HIRE_DT | Date(10) | DATE | First Start Date |
| 11 | LAST_HIRE_DT | Date(10) | DATE | Latest Start Date - The latest time that an Employee or a Contingent Worker has started - based on EMPLID/EMPL_RCD. |
| 12 | TERMINATION_DT | Date(10) | DATE | Termination Date |
| 13 | ASGN_START_DT | Date(10) | DATE | Start date of the specific EMPL_RCD assignment. This is different from the Person's Hire date. |
| 14 | ASGN_END_DT | Date(10) | DATE | End date of the specific EMPL_RCD assignment. This is different from the Person's Termination date. |
| 15 | LST_ASGN_START_DT | Date(10) | DATE | Lastest start date of the specific EMPL_RCD assignment. This is different from the Person's organizational instance Last Hire Date. |
| 16 | CMPNY_SENIORITY_DT | Date(10) | DATE | Company Seniority Date |
| 17 | SERVICE_DT | Date(10) | DATE | Service Date |
| 18 | SENIORITY_PAY_DT | Date(10) | DATE | Seniority Pay Calc Date |
| 19 | PROF_EXPERIENCE_DT | Date(10) | DATE | Professional Experience Date |
| 20 | LAST_VERIFICATN_DT | Date(10) | DATE | Last Verification Date |
| 21 | PROBATION_DT | Date(10) | DATE | Probation Date |
| 22 | LAST_INCREASE_DT | Date(10) | DATE | Date Last Increase |
| 23 | OWN_5PERCENT_CO | Character(1) | VARCHAR2(1) NOT NULL |
A flag that indicates whether or not (Y or N) an employee owns 5% or more of the company. This value is used in nondiscrimination testing.
Y/N Table Edit Default Value: N |
| 24 | BUSINESS_TITLE | Character(30) | VARCHAR2(30) NOT NULL | Business Title |
| 25 | LAST_DATE_WORKED | Date(10) | DATE | Last Date Worked |
| 26 | EXPECTED_RETURN_DT | Date(10) | DATE | Expected Return Date |
| 27 | EXPECTED_END_DATE | Date(10) | DATE | Expected End Date of a job. |
| 28 | POSITION_PHONE | Character(24) | VARCHAR2(24) NOT NULL | Phone number for the Position |
| 29 | EFFDT_NOKEY | Date(10) | DATE | Used in Records /Views where the EFFDT won't be a key |
| 30 | EFFSEQ_NOKEY | Number(3,0) | SMALLINT NOT NULL | Field used in views where EFFSEQ won't be a key |