CTTA_CNT_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.CONTRACT_NUM , A.EFFDT , A.EFFSEQ , A.CONTRACT_TYPE , A.CONTRACT_ID_ESP , A.CTTA_BASICCOPY_ESP , A.CTTA_CONTRACT_ESP , A.CTTA_EXTENSION_ESP , A.CTTA_GROUP_ESP , A.CTTA_TRANSFORM_ESP , A.CTTA_CALLING_ESP , B.COMPANY , C.SSN_EMPLOYER , A.CNT_TEMPLT_ID_ESP , A.CNT_ACTION_ESP , A.BC_MANAGEMNT_ESP FROM PS_WKF_CNT_TYP_ESP A ,PS_JOB B ,PS_JOB_JR C WHERE A.EMPLID = B.EMPLID AND A.CONTRACT_NUM = B.CONTRACT_NUM AND (B.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B.EMPLID = B2.EMPLID AND B.EMPL_RCD = B2.EMPL_RCD AND B.CONTRACT_NUM = B2.CONTRACT_NUM AND B2.EFFDT <= A.EFFDT AND B.EFFSEQ=( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B.EMPLID = B3.EMPLID AND B.EMPL_RCD = B3.EMPL_RCD AND B.CONTRACT_NUM = B3.CONTRACT_NUM AND B.EFFDT=B3.EFFDT ) ) ) AND B.EMPLID = C.EMPLID AND B.EMPL_RCD = C.EMPL_RCD AND B.EFFDT = C.EFFDT AND B.EFFSEQ = C.EFFSEQ

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
3 EFFDT Date(10) DATE Effective Date

Default Value: %date

4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
5 CONTRACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contract Type

Prompt Table: PERS_CNTRCT_TYP

6 CONTRACT_ID_ESP Character(18) VARCHAR2(18) NOT NULL Contract ID assigned by the Employment Agency
C=Company
E=Employee
N=None
S=SS Contribution Center
7 CTTA_BASICCOPY_ESP Character(1) VARCHAR2(1) NOT NULL Report as Basic Copy flag
0=Not Communicated
1=XML Generated
2=XML Sent/Confirmated
N=Not Available
8 CTTA_CONTRACT_ESP Character(1) VARCHAR2(1) NOT NULL Report as Contract flag
0=Not Communicated
1=XML Generated
2=XML Sent/Confirmated
N=Not Available
9 CTTA_EXTENSION_ESP Character(1) VARCHAR2(1) NOT NULL Report as Extension flag
0=Not Communicated
1=XML Generated
2=XML Sent/Confirmated
N=Not Available
10 CTTA_GROUP_ESP Character(1) VARCHAR2(1) NOT NULL Report as Contract Group flag
11 CTTA_TRANSFORM_ESP Character(1) VARCHAR2(1) NOT NULL Report as Transformation flag
0=Not Communicated
1=XML Generated
2=XML Sent/Confirmated
N=Not Available
12 CTTA_CALLING_ESP Character(1) VARCHAR2(1) NOT NULL Report as Calling flag
0=Not Communicated
1=XML Generated
2=XML Sent/Confirmated
N=Not Available
13 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
14 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number
15 CNT_TEMPLT_ID_ESP Character(11) VARCHAR2(11) NOT NULL SPEE Template ID
16 CNT_ACTION_ESP Character(3) VARCHAR2(3) NOT NULL Contract Action
001=New Contract
002=Termination
003=Extension
004=Calling
005=End Calling
006=Calling
007=Remote Working Agreement

Default Value: 001

17 BC_MANAGEMNT_ESP Character(1) VARCHAR2(1) NOT NULL Manage Basic Copy