GPDE_SI_STA_VW

(SQL View)
 Index Back

SI Coverage Status Table

This table records, which combination of coverage types and SI status codes are valid for the various SI Types.

SELECT A1.GPDE_SI_TYPE , A1.GPDE_SI_COVERAGE , A1.GPDE_SI_STATUS , A2.EFFDT , %Substring(X1.FIELDVALUE, 1, 1) , B1.DESCR FROM PS_GPDE_SI_COV_STA A1 , PS_GPDE_SI_COV_STA A2 , PS_GPDE_SI_STAT_CD B1 , PSXLATITEM X1 WHERE A2.GPDE_SI_TYPE = A1.GPDE_SI_TYPE AND A2.GPDE_SI_COVERAGE = ( SELECT MIN(A4.GPDE_SI_COVERAGE) FROM PS_GPDE_SI_COV_STA A4 WHERE A4.GPDE_SI_TYPE = A2.GPDE_SI_TYPE AND A4.EFFDT = A2.EFFDT ) AND A2.GPDE_SI_STATUS = ( SELECT MIN(A5.GPDE_SI_STATUS) FROM PS_GPDE_SI_COV_STA A5 WHERE A5.GPDE_SI_TYPE = A2.GPDE_SI_TYPE AND A5.EFFDT = A2.EFFDT AND A5.GPDE_SI_COVERAGE = A2.GPDE_SI_COVERAGE ) AND A1.EFFDT = ( SELECT MAX(A3.EFFDT) FROM PS_GPDE_SI_COV_STA A3 WHERE A3.GPDE_SI_TYPE = A1.GPDE_SI_TYPE AND A3.GPDE_SI_COVERAGE = A3.GPDE_SI_COVERAGE AND A3.GPDE_SI_STATUS = A1.GPDE_SI_STATUS AND A3.EFFDT <= A2.EFFDT ) AND B1.GPDE_SI_TYPE = A1.GPDE_SI_TYPE AND B1.GPDE_SI_STATUS = A1.GPDE_SI_STATUS AND B1.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_GPDE_SI_STAT_CD B2 WHERE B2.GPDE_SI_TYPE = A1.GPDE_SI_TYPE AND B2.GPDE_SI_STATUS = A1.GPDE_SI_STATUS AND B2.EFFDT <= A1.EFFDT ) AND X1.FIELDNAME = 'EFF_STATUS' AND ( (X1.FIELDVALUE = 'A' AND A1.EFFDT = A2.EFFDT) OR (X1.FIELDVALUE = 'I' AND A1.EFFDT <> A2.EFFDT) ) AND X1.EFFDT = ( SELECT MAX(X2.EFFDT) FROM PSXLATITEM X2 WHERE X2.FIELDNAME = X1.FIELDNAME AND X2.FIELDVALUE = X1.FIELDVALUE AND X2.EFFDT <= A1.EFFDT ) AND X1.EFF_STATUS <> 'I'

  • Related Language Record: GPDE_SI_STATLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 GPDE_SI_TYPE Character(2) VARCHAR2(2) NOT NULL SI Coverage Type
    01=Health Insurance
    02=Nursing Care Insurance
    03=Old Age Pension Insurance
    04=Unemployment Insurance
    2 GPDE_SI_COVERAGE Character(1) VARCHAR2(1) NOT NULL Insurance Coverage Type
    0=None
    1=Mandatory
    2=Voluntary
    3=Private
    3 GPDE_SI_STATUS Character(1) VARCHAR2(1) NOT NULL Contribution Group
    4 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    5 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description