XML_CTPR_VW(SQL View) |
Index Back |
|---|---|
Contract Printing Vw ESPContract Printing Vw ESP |
| SELECT CD.EMPLID , CD.CONTRACT_BEGIN_DT , JB.EFFDT , CD.CONTRACT_NUM , JB.FULL_PART_TIME , C.CONTRACT_TYPE , EMPLA.NAME , EMPLB.NATIONAL_ID , EMPLA.BIRTHDATE , EMPLA.BIRTHCOUNTRY , JB.STD_HOURS , CD.BEGIN_WEEKDAY , CD.END_WEEKDAY , CD.PROBATION_PERIOD , CD.DURATION_TYPE , JB.COMPRATE , JB.COMP_FREQUENCY , CD.COMP_RATE_DESCR , CD.VACATION_PERIOD , CD.VACN_PERIOD_UNIT , JC.DESCR , CD.SIGNATURE_DT , A.FISCAL_ID_CD , A.DESCR , EMPLA.CITY , EMPLA.COUNTRY , %subrec(ADDRESS_SBR,A) , CD.RESPONSIBLE_ID , A.COMPANY , JB.ESTABID , JB.LOCATION , JB.REG_REGION , JB.LABOR_AGREEMENT , JB.EMPL_CTG , JB.EMPL_CTG_L1 , JB.EMPL_CTG_L2 FROM PS_CONTRACT_DATA CD , PS_JOB JB , PS_JOB_JR JR , PS_JOBCODE_TBL JC , PS_WKF_CNT_TYPE C , PS_COMPANY_TBL A , PS_PERSONAL_DATA EMPLA , PS_PERS_NID EMPLB WHERE EMPLA.EMPLID = EMPLB.EMPLID AND EMPLB.PRIMARY_NID = 'Y' AND EMPLA.EMPLID = JB.EMPLID AND JB.EMPLID = JR.EMPLID AND JB.EMPL_RCD = JR.EMPL_RCD AND JB.EFFSEQ = JR.EFFSEQ AND JB.EFFDT = JR.EFFDT AND JB.EMPLID = CD.EMPLID AND JB.CONTRACT_NUM = CD.CONTRACT_NUM AND CD.EMPLID = JB.EMPLID AND JB.JOBCODE = JC.JOBCODE AND JB.SETID_JOBCODE = JC.SETID AND CD.EMPLID = C.EMPLID AND CD.CONTRACT_NUM = C.CONTRACT_NUM AND JB.HR_STATUS = 'A' AND JB.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE JB.EMPLID = A_ED.EMPLID AND JB.EMPL_RCD = A_ED.EMPL_RCD AND JB.CONTRACT_NUM = A_ED.CONTRACT_NUM AND A_ED.HR_STATUS = 'A' AND (A_ED.EFFDT >= CD.CONTRACT_BEGIN_DT OR (A_ED.EFFDT <= %CurrentDateIn AND CD.CONTRACT_BEGIN_DT IS NULL)) AND JB.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE JB.EMPLID = A_ES.EMPLID AND JB.EMPL_RCD = A_ES.EMPL_RCD AND JB.CONTRACT_NUM = A_ES.CONTRACT_NUM AND A_ES.HR_STATUS = 'A' AND JB.EFFDT = A_ES.EFFDT) ) AND CD.EMPLID = EMPLA.EMPLID AND CD.EMPLID = JB.EMPLID AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_WKF_CNT_TYPE C1 WHERE C1.EMPLID = C.EMPLID AND C1.CONTRACT_NUM = CD.CONTRACT_NUM AND (C1.EFFDT >= CD.CONTRACT_BEGIN_DT OR (C1.EFFDT <= %CurrentDateIn AND CD.CONTRACT_BEGIN_DT IS NULL)) ) AND A.COMPANY = JB.COMPANY AND A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_COMPANY_TBL A1 WHERE A1.COMPANY = A.COMPANY AND A1.EFF_STATUS = 'A' AND A1.EFFDT <= CD.CONTRACT_BEGIN_DT ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | CONTRACT_BEGIN_DT |
Date(10) | DATE | Contract Begin Date |
| 3 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 4 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract Number |
| 5 | FULL_PART_TIME | Character(1) | VARCHAR2(1) NOT NULL |
Full/Part Time
D=On Demand F=Full-Time P=Part-Time |
| 6 | CONTRACT_TYPE | Character(3) | VARCHAR2(3) NOT NULL | Contract Type |
| 7 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 8 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 9 | BIRTHDATE | Date(10) | DATE | Date of Birth |
| 10 | BIRTHCOUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Birth Country |
| 11 | STD_HOURS | Number(7,2) | DECIMAL(6,2) NOT NULL | Standard Hours |
| 12 | BEGIN_WEEKDAY | Character(1) | VARCHAR2(1) NOT NULL |
Begin Day of Week
1=Sunday 2=Monday 3=Tuesday 4=Wednesday 5=Thursday 6=Friday 7=Saturday |
| 13 | END_WEEKDAY | Character(1) | VARCHAR2(1) NOT NULL |
End Day of Week
1=Sunday 2=Monday 3=Tuesday 4=Wednesday 5=Thursday 6=Friday 7=Saturday |
| 14 | PROBATION_PERIOD | Number(3,0) | SMALLINT NOT NULL | Probation Period |
| 15 | DURATION_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Type of Duration
D=Days M=Months Y=Years |
| 16 | COMPRATE | Number(19,6) | DECIMAL(18,6) NOT NULL | Compensation Rate |
| 17 | COMP_FREQUENCY | Character(5) | VARCHAR2(5) NOT NULL |
Compensation Frequency
A=Annual B=Biweekly C=Contract D=Daily H=Hourly M=Monthly S=Semimonthly W=Weekly |
| 18 | COMP_RATE_DESCR | Character(100) | VARCHAR2(100) NOT NULL | Compensation Rate Description |
| 19 | VACATION_PERIOD | Number(3,0) | SMALLINT NOT NULL | Vacation Period |
| 20 | VACN_PERIOD_UNIT | Character(1) | VARCHAR2(1) NOT NULL |
Vacation Period Type
D=Work Days M=Months N=Natural Days W=Weeks |
| 21 | JOBCODE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Job Code Description |
| 22 | SIGNATURE_DT | Date(10) | DATE | Signature Date |
| 23 | FISCAL_ID_CD | Character(10) | VARCHAR2(10) NOT NULL | Fiscal Identification Code |
| 24 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |
| 25 | CITY2 | Character(30) | VARCHAR2(30) NOT NULL | City |
| 26 | COUNTRY2 | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 27 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL |
Country
Prompt Table: SCC_CNT_ADFMTVW |
| 28 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 29 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
| 30 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
| 31 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
| 32 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 33 | NUM1 | Character(6) | VARCHAR2(6) NOT NULL | Number 1 |
| 34 | NUM2 | Character(6) | VARCHAR2(6) NOT NULL | Number 2 |
| 35 | HOUSE_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
House Type
AB=House Boat Reference WW=House Trailer Reference |
| 36 | ADDR_FIELD1 | Character(2) | VARCHAR2(2) NOT NULL | Address Field 1 |
| 37 | ADDR_FIELD2 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 2 |
| 38 | ADDR_FIELD3 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 3 |
| 39 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
| 40 | STATE | Character(6) | VARCHAR2(6) NOT NULL |
State
Prompt Table: STATE_TBL |
| 41 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 42 | GEO_CODE | Character(11) | VARCHAR2(11) NOT NULL | Geo Code |
| 43 | IN_CITY_LIMIT | Character(1) | VARCHAR2(1) NOT NULL |
In City Limit
Y/N Table Edit |
| 44 | RESPONSIBLE_ID | Character(11) | VARCHAR2(11) NOT NULL | Responsible ID |
| 45 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 46 | ESTABID | Character(12) | VARCHAR2(12) NOT NULL | Establishment ID |
| 47 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 48 | REG_REGION | Character(5) | VARCHAR2(5) NOT NULL | Regulatory Region |
| 49 | LABOR_AGREEMENT | Character(6) | VARCHAR2(6) NOT NULL | Labor Agreement |
| 50 | EMPL_CTG | Character(6) | VARCHAR2(6) NOT NULL | Employee Category |
| 51 | EMPL_CTG_L1 | Character(6) | VARCHAR2(6) NOT NULL | Employee Subcategory |
| 52 | EMPL_CTG_L2 | Character(6) | VARCHAR2(6) NOT NULL | Employee Subcategory 2 |