CAN_DED_SUM_VW

(SQL View)
 Index Back

EE CAN Deduction Balance

This view shows summary of employee deduction Year-to-date, Quarterly and monthly balances accross benefit record numbers.

SELECT EMPLID , COMPANY , BALANCE_ID , BALANCE_YEAR , WAGE_LOSS_PLAN , PROVINCE , BALANCE_QTR , BALANCE_PERIOD , PLAN_TYPE , BENEFIT_PLAN , DEDCD , DED_CLASS , DED_SLSTX_CLASS , SUM(DED_YTD) , SUM(DED_QTD) , SUM(DED_MTD) FROM PS_CAN_DED_BALANCE GROUP BY EMPLID ,COMPANY ,BALANCE_ID ,BALANCE_YEAR ,WAGE_LOSS_PLAN ,PROVINCE ,BALANCE_QTR ,BALANCE_PERIOD ,PLAN_TYPE ,BENEFIT_PLAN ,DEDCD ,DED_CLASS ,DED_SLSTX_CLASS

  • Parent record: PERSON

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

    Prompt Table: PERSON

    2 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

    Prompt Table: COMPANY_TBL

    3 BALANCE_ID Character(2) VARCHAR2(2) NOT NULL Balance ID

    Prompt Table: BALANCE_ID_TBL

    4 BALANCE_YEAR Number(4,0) SMALLINT NOT NULL Balances for Year

    Prompt Table: BALANCE_YR_VW

    5 WAGE_LOSS_PLAN Character(3) VARCHAR2(3) NOT NULL Wage Loss Replacement Plan

    Prompt Table: WAGELS_PLAN_TBL

    6 PROVINCE Character(6) VARCHAR2(6) NOT NULL Province
    7 BALANCE_QTR Number(3,0) SMALLINT NOT NULL Balances for Quarter

    Prompt Table: BALANCE_ID_QTR

    8 BALANCE_PERIOD Number(3,0) SMALLINT NOT NULL Balances for Period

    Prompt Table: BALANCE_ID_DEF

    9 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.
    10 BENEFIT_PLAN Character(6) VARCHAR2(6) NOT NULL Benefit Plan

    Prompt Table: BEN_PROG_BENPLN

    11 DEDCD Character(6) VARCHAR2(6) NOT NULL Deduction Code

    Prompt Table: BEN_PROG_BENDED

    12 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
    13 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
    14 DED_YTD Signed Number(12,2) DECIMAL(10,2) NOT NULL Deduction Balance YTD
    15 DED_QTD Signed Number(12,2) DECIMAL(10,2) NOT NULL Deduction Balance QTD
    16 DED_MTD Signed Number(12,2) DECIMAL(10,2) NOT NULL Deduction Balance MTD