GPES_LBRAGRM_VW(SQL View) |
Index Back |
---|---|
Labor agreement dataRetrieves labor agreement data for social security and absences |
SELECT A.SETID , A.LABOR_AGREEMENT , A.EFFDT , A.EFF_STATUS , C.EMPL_CTG , C.SCHEME_ID_ESP , C.SOC_SEC_WRK_GRP_CD , B.WORKING_HOURS , B.VACATION_PERIOD , B.VACN_PERIOD_UNIT , B.ADDTL_PTO_ESP , A.GPES_PIN_SS1_NUM , A.GPES_PIN_TR1_NUM , A.GPES_PIN_TR2_NUM , A.GPES_PIN_TR3_NUM FROM PS_GPES_LBR_AGRMNT A , PS_EMPL_CTG_ESP B , PS_EMPL_CTG_L1 C WHERE A.SETID = B.SETID AND A.LABOR_AGREEMENT = B.LABOR_AGREEMENT AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_EMPL_CTG_ESP WHERE SETID = A.SETID AND LABOR_AGREEMENT = A.LABOR_AGREEMENT AND EFFDT <= A.EFFDT) AND C.SETID = B.SETID AND C.LABOR_AGREEMENT = B.LABOR_AGREEMENT AND C.EFFDT = ( SELECT MAX(EFFDT) FROM PS_EMPL_CTG_L1 WHERE SETID = A.SETID AND LABOR_AGREEMENT = A.LABOR_AGREEMENT AND EMPL_CTG = C.EMPL_CTG AND EFFDT <= A.EFFDT) |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | Character(5) | VARCHAR2(5) NOT NULL | SetID | |
2 | Character(6) | VARCHAR2(6) NOT NULL | Labor Agreement | |
3 | Date(10) | DATE |
Effective Date
Default Value: %date |
|
4 | EFF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Effective Status
A=Active I=Inactive |
5 | EMPL_CTG | Character(6) | VARCHAR2(6) NOT NULL | Employee Category |
6 | SCHEME_ID_ESP | Character(4) | VARCHAR2(4) NOT NULL | Scheme ID - ESP |
7 | SOC_SEC_WRK_GRP_CD | Character(2) | VARCHAR2(2) NOT NULL | Social Security Work Group |
8 | WORKING_HOURS | Number(8,2) | DECIMAL(7,2) NOT NULL | Working Hours |
9 | VACATION_PERIOD | Number(3,0) | SMALLINT NOT NULL | Vacation Period |
10 | VACN_PERIOD_UNIT | Character(1) | VARCHAR2(1) NOT NULL |
Vacation Period Type
D=Work Days M=Months N=Natural Days W=Weeks |
11 | ADDTL_PTO_ESP | Number(2,0) | SMALLINT NOT NULL | Additional PTO - ESP |
12 | GPES_PIN_SS1_NUM | Number(8,0) | INTEGER NOT NULL | This field houses the Employer SS Accumulator Pin Number. |
13 | GPES_PIN_TR1_NUM | Number(8,0) | INTEGER NOT NULL |
Pre-advised Daily Salary
Prompt Table: GP_PIN |
14 | GPES_PIN_TR2_NUM | Number(8,0) | INTEGER NOT NULL |
Severance Daily Salary
Prompt Table: GP_PIN |
15 | GPES_PIN_TR3_NUM | Number(8,0) | INTEGER NOT NULL |
Job Litigation Daily Salary
Prompt Table: GP_PIN |