EMPLIDEFFDTDefault Value: %date
Prompt Table: INDSTRY_ACT_ESP
R=Regular
T=Temporary
XML_JOBDATA_VW(SQL View) |
Index Back |
|---|---|
View XML JobDataView XML JobData |
| SELECT A.EMPLID , A.EFFDT , A.HIRE_DT , A.LOCATION , A.ESTABID , A.SOC_SEC_RISK_CODE , B.SSN_EMPLOYER , C.CONTRACT_NUM , D.COMPANY , D.FISCAL_ID_CD , D.DESCR , D.ADDRESS1 , D.STATE , D.CITY , D.POSTAL , E.PHONE , H.INDSTRY_CD_ESP , H.INDSTRY_CD_ESP , G.CONTRACT_TYPE , H.SCHEME_ID_ESP , A.REG_TEMP FROM PS_JOB A , PS_JOB_JR B , PS_CONTRACT_DATA C , PS_COMPANY_TBL D , PS_COMP_PHONE_TBL E , PS_WKF_CNT_TYPE G , PS_ESTAB_SSEC_ESP H WHERE 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.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_JOB_JR B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.EMPL_RCD = B_ED.EMPL_RCD AND B_ED.EFFDT <= A.EFFDT) AND B.EFFSEQ = ( SELECT MAX(B_ES.EFFSEQ) FROM PS_JOB_JR B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.EMPL_RCD = B_ES.EMPL_RCD AND B.EFFDT = B_ES.EFFDT) AND A.EMPLID = G.EMPLID AND G.EMPLID = B.EMPLID AND B.EMPLID = C.EMPLID AND D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_COMPANY_TBL D_ED WHERE D.COMPANY = D_ED.COMPANY AND D_ED.EFFDT <= %CurrentDateIn) AND A.COMPANY = E.COMPANY AND E.COMPANY = D.COMPANY AND E.EFFDT = ( SELECT MAX(E_ED.EFFDT) FROM PS_COMP_PHONE_TBL E_ED WHERE E.COMPANY = E_ED.COMPANY AND E_ED.EFFDT <= D.EFFDT) AND G.EFFDT = ( SELECT MAX(G_ED.EFFDT) FROM PS_WKF_CNT_TYPE G_ED WHERE G.EMPLID = G_ED.EMPLID AND G.CONTRACT_NUM = G_ED.CONTRACT_NUM AND G_ED.EFFDT <= %CurrentDateIn) AND H.EFFDT = ( SELECT MAX(H_ED.EFFDT) FROM PS_ESTAB_SSEC_ESP H_ED WHERE H.ESTABID = H_ED.ESTABID AND H_ED.EFFDT <= A.EFFDT) AND H.ESTABID = A.ESTABID AND A.CONTRACT_NUM = C.CONTRACT_NUM AND C.CONTRACT_NUM = G.CONTRACT_NUM AND H.SSN_EMPLOYER = B.SSN_EMPLOYER |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 3 | HIRE_DT | Date(10) | DATE | First Start Date |
| 4 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 5 | ESTABID | Character(12) | VARCHAR2(12) NOT NULL | Establishment ID |
| 6 | SOC_SEC_RISK_CODE | Character(3) | VARCHAR2(3) NOT NULL | Occupation Code |
| 7 | SSN_EMPLOYER | Character(13) | VARCHAR2(13) NOT NULL | Employer Social Security number |
| 8 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract Number |
| 9 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 10 | FISCAL_ID_CD | Character(10) | VARCHAR2(10) NOT NULL | Fiscal Identification Code |
| 11 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 12 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
| 13 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 14 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
| 15 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
| 16 | PHONE | Character(24) | VARCHAR2(24) NOT NULL | Telephone |
| 17 | INDSTRY_CD_ESP | Character(5) | VARCHAR2(5) NOT NULL |
Industry Activity Code
Prompt Table: INDSTRY_ACT_ESP |
| 18 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
| 19 | CONTRACT_TYPE | Character(3) | VARCHAR2(3) NOT NULL | Contract Type |
| 20 | SCHEME_ID_ESP | Character(4) | VARCHAR2(4) NOT NULL | Scheme ID - ESP |
| 21 | REG_TEMP | Character(1) | VARCHAR2(1) NOT NULL |
Regular/Temporary
R=Regular T=Temporary |