GPAU_STP_RPT_VW(SQL View) |
Index Back |
|---|---|
STP Report Data ViewReport Data Input View - Single Touch Payroll |
| SELECT DISTINCT GP.PIN_NUM , GP.PIN_NM , SOVR.SOVR_VAL_CHAR , SOVR.PIN_SOVR_NUM , MBR.PIN_MBR_NUM , CAT.EFFDT , SOVR.BGN_DT , SOVR.END_DT , CAT.GPAU_STP_CAT , CAT.GPAU_STP_SUB_CAT , CAT.GPAU_STP_SUB_CAT_1 , CAT.GPAU_STP_S_CAT_DES , CAT.ACM_SIGN , MBR.BGN_DT , MBR.END_DT , AC.ACM_PRD_OPTN , CASE WHEN (AC.ACM_PRD_OPTN = '5' OR AC.ACM_PRD_OPTN = '2') AND (CAT.GPAU_STP_CAT = 'ETP' AND (SOVR.SOVR_VAL_CHAR = 'E1' OR SOVR.SOVR_VAL_CHAR = 'E2' OR SOVR.SOVR_VAL_CHAR = 'E3' OR SOVR.SOVR_VAL_CHAR = 'E4' OR SOVR.SOVR_VAL_CHAR = 'ET' )) THEN 'R' WHEN (AC.ACM_PRD_OPTN = '5' OR AC.ACM_PRD_OPTN = '2') AND (CAT.GPAU_STP_CAT = 'ETP' AND (SOVR.SOVR_VAL_CHAR = 'E7' OR SOVR.SOVR_VAL_CHAR = 'E8' OR SOVR.SOVR_VAL_CHAR = 'TR')) THEN 'T' WHEN (AC.ACM_PRD_OPTN = '5' OR AC.ACM_PRD_OPTN = '2') AND (CAT.GPAU_STP_CAT = 'ETP' AND (SOVR.SOVR_VAL_CHAR = 'E1W' OR SOVR.SOVR_VAL_CHAR = 'E4W' OR SOVR.SOVR_VAL_CHAR = 'ETW')) THEN 'O' ELSE ' ' END FROM PS_GP_ACM_MBR MBR ,PS_GP_ACCUMULATOR AC ,PS_GP_PIN GP ,PS_GP_ELM_DFN_SOVR SOVR ,PS_GPAU_STP_SOVRCT CAT WHERE AC.ACM_PRD_OPTN IN ('2','5') AND (GP.PIN_NM LIKE '%_FPTDA%' OR GP.PIN_NM LIKE '%_FYTDA%') AND AC.PIN_NUM = MBR.PIN_NUM AND GP.PIN_NUM = MBR.PIN_NUM AND MBR.PIN_MBR_NUM = SOVR.PIN_NUM AND GP.PIN_NUM < 50000000 AND SOVR.SOVR_VAL_CHAR <> ' ' AND SOVR.SOVR_VAL_CHAR = CAT.SOVR_VAL_CHAR AND SOVR.PIN_SOVR_NUM = CAT.PIN_NUM AND SOVR.SOVR_VAL_CHAR IN ( SELECT X.SOVR_VAL_CHAR FROM PS_GPAU_STP_SOVRCT X) AND SOVR.PIN_SOVR_NUM IN ( SELECT PIN_NUM VR FROM PS_GPAU_STP_VAR_VW VR) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | PIN_NUM | Number(8,0) | INTEGER NOT NULL | PIN Number |
| 2 | PIN_NM | Character(18) | VARCHAR2(18) NOT NULL | Element Name field |
| 3 | SOVR_VAL_CHAR | Character(25) | VARCHAR2(25) NOT NULL | Supporting Element Character Value |
| 4 | PIN_SOVR_NUM | Number(8,0) | INTEGER NOT NULL | Supporting Element PIN Number |
| 5 | PIN_MBR_NUM | Number(8,0) | INTEGER NOT NULL | Element number field |
| 6 | EFFDT | Date(10) | DATE |
Effective Date
Default Value: %date |
| 7 | BGN_DT | Date(10) | DATE | Begin Date |
| 8 | END_DT | Date(10) | DATE | end date |
| 9 | GPAU_STP_CAT | Character(30) | VARCHAR2(30) NOT NULL | Category Information - Single Touch Payroll |
| 10 | GPAU_STP_SUB_CAT | Character(30) | VARCHAR2(30) NOT NULL | STP Sub-Category |
| 11 | GPAU_STP_SUB_CAT_1 | Character(30) | VARCHAR2(30) NOT NULL | STP Sub-Category to maintain Key Structure |
| 12 | GPAU_STP_S_CAT_DES | Character(40) | VARCHAR2(40) NOT NULL | STP Category Description |
| 13 | ACM_SIGN | Character(1) | VARCHAR2(1) NOT NULL |
Accumulator Sign
A=Add S=Subtract |
| 14 | BEGIN_DT | Date(10) | DATE | Begin Date |
| 15 | END_DATE | Date(10) | DATE | End Date |
| 16 | ACM_PRD_OPTN | Character(1) | VARCHAR2(1) NOT NULL |
Accumulator Period
1=Segment 2=Calendar Period 3=Month to Date 4=Quarter to Date 5=Year to Date 6=Custom Period |
| 17 | GPAU_ETP_PY_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
ETP Payment Type.
N=Non Transitional O=Non-Excluded ETP R=Excluded ETP T=Transitional |