CTTA_CNT_VW_ESP

(SQL View)
 Index Back

Contrata Search View ESP

This view retreives the Contract Data to be printed by Contrata.

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 , F.CONTRACT_ID_ESP , F.CTTA_BASICCOPY_ESP , F.CTTA_CONTRACT_ESP , F.CTTA_EXTENSION_ESP , F.CTTA_GROUP_ESP , F.CTTA_TRANSFORM_ESP , F.CTTA_CALLING_ESP 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_TYP_ESP 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.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 C.EMPLID = F.EMPLID AND C.CONTRACT_NUM = F.CONTRACT_NUM AND C.EFFDT = F.EFFDT AND (A.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE A.EMPLID = B2.EMPLID AND A.EMPL_RCD = B2.EMPL_RCD AND A.CONTRACT_NUM = B2.CONTRACT_NUM 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.CONTRACT_NUM = B3.CONTRACT_NUM AND A.EFFDT=B3.EFFDT ) ) )

# 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_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
12 CTTA_BASICCOPY_ESP Character(1) VARCHAR2(1) NOT NULL Report as Basic Copy flag
13 CTTA_CONTRACT_ESP Character(1) VARCHAR2(1) NOT NULL Report as Contract flag
14 CTTA_EXTENSION_ESP Character(1) VARCHAR2(1) NOT NULL Report as Extension flag
15 CTTA_GROUP_ESP Character(1) VARCHAR2(1) NOT NULL Report as Contract Group flag
16 CTTA_TRANSFORM_ESP Character(1) VARCHAR2(1) NOT NULL Report as Transformation flag
17 CTTA_CALLING_ESP Character(1) VARCHAR2(1) NOT NULL Report as Calling flag