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 , B.WRK_HRS_DAY 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 |  SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | LABOR_AGREEMENT |
Character(6) | VARCHAR2(6) NOT NULL | Labor Agreement |
| 3 | EFFDT |
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
01=01-Engineers 02=02-Technicals 03=03-Administrative 04=04-Assistants 05=05-White Collar 06=06-Subordinates 07=07-Administrative assistants 08=08-1st and 2nd degree 09=09-3rd degree 10=10-Over 18 years 11=11-Under 18 years |
| 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 unit
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 |
| 16 | WRK_HRS_DAY | Number(5,2) | DECIMAL(4,2) NOT NULL | Working hours per day |