HR_OWS_JOB_VW(SQL View) |
Index Back |
|---|---|
Job OWSOracle Workforce Scheduling Job Data |
| SELECT A.EMPLID , A.EMPL_RCD , A.EFFDT , A.HR_STATUS , CASE WHEN A.HIRE_DT IS NULL THEN %DateIn('1902-02-02') ELSE A.HIRE_DT END , A.REG_REGION , A.BUSINESS_UNIT , A.POSITION_NBR , A.COMPANY , A.DEPTID , A.SETID_DEPT , A.JOBCODE , A.SETID_JOBCODE , A.LOCATION , A.SETID_LOCATION , A.ESTABID , A.FULL_PART_TIME , A.STD_HOURS , A.STD_HRS_FREQUENCY , A.HOURLY_RT , A.CURRENCY_CD FROM PS_JOB A , PS_PER_ORG_ASG_INT B , PS_INSTALL_INTEGR C WHERE C.INT_APP_TYPE = 'SCH' AND C.INTEGR_PROD_CD = 'OWS' AND C.INSTALLED_FLAG = 'Y' AND B.INT_APP_TYPE = C.INT_APP_TYPE AND B.INTEGR_PROD_CD = C.INTEGR_PROD_CD AND A.HR_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND A1.HR_STATUS = 'A' AND A1.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX (EFFSEQ) FROM PS_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.EFFDT = A.EFFDT AND A2.HR_STATUS = 'A') AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD |
| # | 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 | EFFDT |
Date(10) | DATE NOT NULL |
Effective Date
Default Value: %date |
| 4 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 5 | HIRE_DT | Date(10) | DATE | First Start Date |
| 6 | REG_REGION | Character(5) | VARCHAR2(5) NOT NULL | Regulatory Region |
| 7 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 8 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL |
Position Number
Prompt Table: POSN_DATA_VW |
| 9 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 10 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: DEPT_TBL |
| 11 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 12 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL |
Job Code
Prompt Table: JOBCODE_TBL |
| 13 | SETID_JOBCODE | Character(5) | VARCHAR2(5) NOT NULL | Job Code Set ID |
| 14 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL |
Location Code
Prompt Table: LOCATION_TBL |
| 15 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location Set ID |
| 16 | ESTABID | Character(12) | VARCHAR2(12) NOT NULL | Establishment ID |
| 17 | FULL_PART_TIME | Character(1) | VARCHAR2(1) NOT NULL |
Full/Part Time
D=On Demand F=Full-Time P=Part-Time Default Value: F |
| 18 | STD_HOURS | Number(7,2) | DECIMAL(6,2) NOT NULL | Standard Hours |
| 19 | STD_HRS_FREQUENCY | Character(5) | VARCHAR2(5) NOT NULL |
Standard Work Period
Prompt Table: STDHRS_FREQ_VW |
| 20 | HOURLY_RT | Number(19,6) | DECIMAL(18,6) NOT NULL | Hourly Rate |
| 21 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL |
Currency Code
Prompt Table: CURRENCY_CD_TBL |