GVT_ECS_DEDC_VW(SQL View) |
Index Back |
|---|---|
GVT Time Sensitive Ded View |
| SELECT B.COMPANY ,B.PAYGROUP ,B.PAY_END_DT ,B.OFF_CYCLE ,B.PAGE_NUM ,B.LINE_NUM ,B.SEPCHK ,B.PLAN_TYPE ,B.BENEFIT_PLAN ,B.DEDCD ,B.DED_CLASS ,B.DED_SLSTX_CLASS ,B.DED_CUR ,B.GVT_SCHEDULE_NO ,B.GVT_PAY_ID_LINE_1 ,B.GVT_PAY_ID_LINE_2 ,B.GVT_ECS_OFF_ACCT ,B.GVT_ECS_LTD_INDIC FROM PS_PAY_CHECK A , PS_PAY_DEDUCTION B , PS_DEDUCTION_TBL C , PS_GENL_DEDUCTION D WHERE A.OFF_CYCLE = 'Y' AND A.PAYCHECK_STATUS = 'F' AND B.COMPANY = A.COMPANY AND B.PAYGROUP = A.PAYGROUP AND B.PAY_END_DT = A.PAY_END_DT AND B.OFF_CYCLE = A.OFF_CYCLE AND B.PAGE_NUM = A.PAGE_NUM AND B.LINE_NUM = A.LINE_NUM AND B.SEPCHK = A.SEPCHK AND B.GVT_SCHEDULE_NO = ' ' AND C.PLAN_TYPE = B.PLAN_TYPE AND C.DEDCD = B.DEDCD AND C.GVT_TIME_SENSITIVE = 'Y' AND C.GVT_DIST_DEST = 'T' AND C.EFFDT = ( SELECT MAX(CC.EFFDT) FROM PS_DEDUCTION_TBL CC WHERE CC.PLAN_TYPE = C.PLAN_TYPE AND CC.DEDCD = C.DEDCD AND CC.EFFDT <= A.PAY_END_DT) AND D.EMPLID = A.EMPLID AND D.DEDCD = B.DEDCD AND D.EFFDT = ( SELECT MAX(DD1.EFFDT) FROM PS_GENL_DEDUCTION DD1 WHERE DD1.EMPLID = D.EMPLID AND DD1.DEDCD = D.DEDCD AND DD1.EFFDT <= A.PAY_END_DT) AND (D.GVT_DED_DIST_CD = ' ' AND (D.GVT_PYMNT_METHOD = 'C' OR C.SPCL_PROCESS <> 'G') OR D.GVT_DED_DIST_CD = ( SELECT DD2.GVT_DED_DIST_CD FROM PS_GVT_DED_DIST DD2 WHERE DD2.DEDCD = D.DEDCD AND DD2.GVT_DED_DIST_CD = D.GVT_DED_DIST_CD AND DD2.GVT_PYMNT_METHOD = 'C' AND DD2.EFFDT = ( SELECT MAX(DD3.EFFDT) FROM PS_GVT_DED_DIST DD3 WHERE DD3.DEDCD = DD2.DEDCD AND DD3.GVT_DED_DIST_CD = DD2.GVT_DED_DIST_CD AND DD3.GVT_PYMNT_METHOD = DD2.GVT_PYMNT_METHOD AND DD3.EFFDT <= A.PAY_END_DT))) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  COMPANY |
Character(3) | VARCHAR2(3) NOT NULL | Company |
| 2 | PAYGROUP |
Character(3) | VARCHAR2(3) NOT NULL | Pay Group |
| 3 | PAY_END_DT |
Date(10) | DATE | Pay Period End Date |
| 4 | OFF_CYCLE |
Character(1) | VARCHAR2(1) NOT NULL | Off Cycle ? |
| 5 | PAGE_NUM |
Number(4,0) | SMALLINT NOT NULL | Page # |
| 6 | LINE_NUM |
Number(2,0) | SMALLINT NOT NULL | Line # |
| 7 | SEPCHK |
Number(1,0) | SMALLINT NOT NULL | Separate check number. |
| 8 | PLAN_TYPE |
Character(2) | VARCHAR2(2) NOT NULL | Identifies a category of benefit plan, such as Medical, Dental, and Life (Insurance). A set of plan type codes, as well as rules for creating new codes, is provided by PeopleSoft. Plan Type codes determine how the system processes and defines eligibility for the various benefit plans. |
| 9 | BENEFIT_PLAN |
Character(6) | VARCHAR2(6) NOT NULL | Benefit Plan |
| 10 | DEDCD |
Character(6) | VARCHAR2(6) NOT NULL | Deduction Code |
| 11 | DED_CLASS |
Character(1) | VARCHAR2(1) NOT NULL |
Deduction Classification
A=After-Tax B=Before-Tax L=QC Taxable Benefit N=Nontaxable Benefit P=Nontaxable Btax Benefit T=Taxable Benefit |
| 12 | DED_SLSTX_CLASS |
Character(1) | VARCHAR2(1) NOT NULL |
Sales Tax Type
B=None G=Goods and Services Tax H=Harmonized Sales Tax I=Provincial Sales Tax Insurance P=Provincial Sales Tax Q=QC Prov Sales Tax Insurance S=QC Provincial Sales Tax T=Provincial Premium Tax U=QC Provincial Premium Tax |
| 13 | DED_CUR | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Current Deduction |
| 14 | GVT_SCHEDULE_NO | Character(14) | VARCHAR2(14) NOT NULL | Schedule Number |
| 15 | GVT_PAY_ID_LINE_1 | Character(40) | VARCHAR2(40) NOT NULL | Payment ID Line 1 |
| 16 | GVT_PAY_ID_LINE_2 | Character(40) | VARCHAR2(40) NOT NULL | Payment ID Line 2 |
| 17 | GVT_ECS_OFF_ACCT | Character(16) | VARCHAR2(16) NOT NULL | Account Symbol |
| 18 | GVT_ECS_LTD_INDIC | Character(1) | VARCHAR2(1) NOT NULL | LTD Pay Indicator |