CTTA_CLS_VW_ESP

(SQL View)
 Index Back

XLAT Table for HR Spain

This table store the Social Security tables for look up.

SELECT A.EMPLID , A.EFFDT , A.CONTRACT_NUM , A.COMPANY , E.SSN_EMPLOYER , C.CONTRACT_TYPE , C.EXTEND_CONTRACT_SW , D.XML_NODE_ID , C.EFFDT , D.XML_TEMPLATE_ID , COUNT(*) FROM PS_JOB A ,PS_CONTRACT_DATA B ,PS_WKF_CNT_TYPE C ,PS_CTTA_COMMBR_ESP D ,PS_JOB_JR E ,PS_WKF_CNT_CLAUSE F WHERE A.EMPLID = B.EMPLID AND A.CONTRACT_NUM = B.CONTRACT_NUM AND A.EMPLID = C.EMPLID AND A.CONTRACT_NUM = C.CONTRACT_NUM AND (A.EFFDT >= %CurrentDateIn OR (A.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE A.EMPLID = B2.EMPLID AND A.EMPL_RCD = B2.EMPL_RCD AND B2.EFFDT <= %CurrentDateIn) AND A.EFFSEQ=( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE A.EMPLID = B3.EMPLID AND A.EMPL_RCD = B3.EMPL_RCD AND A.EFFDT=B3.EFFDT ) ) AND C.EFFDT <= A.EFFDT ) AND A.EMPLID = E.EMPLID AND A.EMPL_RCD = E.EMPL_RCD AND A.EFFDT = E.EFFDT AND A.EFFSEQ = E.EFFSEQ AND C.CONTRACT_TYPE = D.CONTRACT_TYPE AND B.CONTRACT_NUM = C.CONTRACT_NUM AND B.EMPLID = C.EMPLID AND B.REG_REGION = 'ESP' AND D.XML_TEMPLATE_ID = 'C03' AND C.EMPLID = F.EMPLID AND C.CONTRACT_NUM = F.CONTRACT_NUM AND C.EFFDT = F.EFFDT GROUP BY A.EMPLID , A.EFFDT , A.CONTRACT_NUM , A.COMPANY , E.SSN_EMPLOYER , C.CONTRACT_TYPE , C.EXTEND_CONTRACT_SW , D.XML_NODE_ID, C.EFFDT, D.XML_TEMPLATE_ID

# 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 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
4 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
5 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
6 CONTRACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contract Type

Prompt Table: PERS_CNTRCT_TYP

7 EXTEND_CONTRACT_SW Character(1) VARCHAR2(1) NOT NULL Extend Contract

Y/N Table Edit

Default Value: N

8 XML_NODE_ID Character(6) VARCHAR2(6) NOT NULL Root node

Prompt Table: XML_NODE_VW

9 CONTRACT_BEGIN_DT Date(10) DATE Contract Begin Date
10 XML_TEMPLATE_ID Character(3) VARCHAR2(3) NOT NULL Template type
11 CONTRACT_SEQ Number(3,0) SMALLINT NOT NULL Contract Seq#