CTTA_DIS_ESP_VW

(SQL View)
 Index Back

Disability View for Contrata

This view retrieves data from 5 tables (WKF_CNT_TYP_ESP, DISABILITY_ESP, WKF_CNT_AFI_ESP , CNT_TYPE_VW_ESP, and PERS_DATA_EFFDT), and based on the values of the fields CONTRACT_TYPE, CNT_REASON_ESP, LABOR_RLTNSHIP_ESP, DISABLE_TYPE_ESP, HANDICAP_PCNT_ESP and SEX, the view generates a value for the field DISABLED_IND_ESP. In case of the Contract Type for disable people (130, 230, 330, 430 and 530) the value �S� will be assigned to the field DISABLED_IND_ESP. If Special Relationship is 0600, the system assigns the value �C� to the field. If Special Relationship is 0601 (Labor Enclave) the system retrieves the data from Disability and check in which case is the employee (E, F or G values).

SELECT A.EMPLID , A.EFFDT , A.CONTRACT_TYPE , C.CNT_REASON_ESP , B.LABOR_RLTNSHIP_ESP , D.DISABLE_TYPE_ESP , D.HANDICAP_PCNT_ESP , E.SEX , CASE WHEN B.LABOR_RLTNSHIP_ESP = '0600' THEN 'C' WHEN B.LABOR_RLTNSHIP_ESP = '0601' THEN CASE WHEN D.DISABLE_TYPE_ESP IN ('F' , 'S') AND D.HANDICAP_PCNT_ESP > 64 THEN 'F' WHEN D.DISABLE_TYPE_ESP IN ( 'P') AND D.HANDICAP_PCNT_ESP > 32 THEN 'E' WHEN E.SEX IN ('F') AND D.HANDICAP_PCNT_ESP > 32 THEN 'G' END WHEN C.CNT_REASON_ESP = 'DIS' THEN 'S' END FROM PS_WKF_CNT_TYP_ESP A LEFT OUTER JOIN PS_DISABILITY_ESP D ON A.EMPLID = D.EMPLID , PS_WKF_CNT_AFI_ESP B , PS_CNT_TYPE_VW_ESP C , PS_PERS_DATA_EFFDT E WHERE A.CONTRACT_TYPE = C.CONTRACT_TYPE AND A.EMPLID = B.EMPLID AND B.EFFDT = ( SELECT MAX(ED.EFFDT) FROM PS_WKF_CNT_AFI_ESP ED WHERE ED.EMPLID = B.EMPLID AND B.EFFDT <= A.EFFDT) AND A.EMPLID = E.EMPLID AND E.EFFDT = ( SELECT MAX(ED.EFFDT) FROM PS_PERS_DATA_EFFDT ED WHERE ED.EMPLID = A.EMPLID AND E.EFFDT <= A.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

Prompt Table: PERSON

2 EFFDT Date(10) DATE Effective Date

Default Value: %date

3 CONTRACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contract Type

Default Value: 100

Prompt Table: CNT_TYPE_VW_ESP

4 CNT_REASON_ESP Character(3) VARCHAR2(3) NOT NULL Contract Reason
DIS=Disabled People
FEU=European Funds
FOM=Employment Promotion
FOR=Training
INS=Insertion
INT=Interinity
INT=Replacement
JUB=Partial Retirement
ORD=Regular
PRO=Production
REL=Relieve
SER=Service
5 LABOR_RLTNSHIP_ESP Character(4) VARCHAR2(4) NOT NULL Special Labor Relationship
6 DISABLE_TYPE_ESP Character(3) VARCHAR2(3) NOT NULL Disability Type
F=Physical Disability
N=Non Disability
P=Psychological Disability
S=Sensorial Disability
7 HANDICAP_PCNT_ESP Number(3,0) SMALLINT NOT NULL Handicap Percent
8 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
9 DISABLED_IND_ESP Character(1) VARCHAR2(1) NOT NULL Disability Type for Contrata. With the above setup we will just comply with the value S --> the contract type belongs to Disability Contract list. C -->whenever the employee has this information in AFI data ,field: Special Labor Relation: 0600 ( Disabled EE Spec. Employ.Center) Values E, F and G in case of Special Labor Relation: 0601 -> Disabled EE from Employ Center: E --> employee with psychic disability up 33% F --> employee with phisical / sensorial disability up 65% G --> woman with disability up 33%
C=Specials Employment Centers
E=Enclaves. Psychic. Up 33%
F=Enclaves. Physical Up 65%
G=Enclaves. Women. Up 33%
S=Disabled Contract