AV_MBRSHP_VW

(SQL View)
 Index Back

Membership Entry View

This view selects the max expiration date from the Membership History table associated with the emplid/ext_org_id in context. ----------------------------------------------------------------------------------------------------------------- Code change 25 July 2000 (for SetId/Business Unit functionality) - old code follows: SELECT A.INSTITUTION, A.EMPLID, A.EXT_ORG_ID, A.AV_MBR_ORG_COD, A.AV_DUES_LVL, A.EXPIRATION_DT FROM PS_AV_MEMBERSHIP A WHERE A.EXPIRATION_DT = (SELECT MAX(EXPIRATION_DT) FROM PS_AV_MEMBERSHIP B WHERE A.INSTITUTION = B.INSTITUTION AND A.EMPLID = B.EMPLID AND A.EXT_ORG_ID = B.EXT_ORG_ID AND A.AV_MBR_ORG_COD = B.AV_MBR_ORG_COD AND A.AV_DUES_LVL = B.AV_DUES_LVL) -----------------------------------------------------------------------------------------------------------------

SELECT A.BUSINESS_UNIT, A.EMPLID, A.EXT_ORG_ID, A.AV_MBR_ORG_COD, A.AV_DUES_LVL, A.EXPIRATION_DT FROM PS_AV_MEMBERSHIP A WHERE A.EXPIRATION_DT = (SELECT MAX(B.EXPIRATION_DT) FROM PS_AV_MEMBERSHIP B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.EMPLID = B.EMPLID AND A.EXT_ORG_ID = B.EXT_ORG_ID AND A.AV_MBR_ORG_COD = B.AV_MBR_ORG_COD AND A.AV_DUES_LVL = B.AV_DUES_LVL)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
4 AV_MBR_ORG_COD Character(10) VARCHAR2(10) NOT NULL Member Organization Code

Prompt Table: AV_MBR_ORG_TBL

5 AV_DUES_LVL Character(5) VARCHAR2(5) NOT NULL Dues Level

Prompt Table: AV_MBR_DUES_TBL

6 EXPIRATION_DT Date(10) DATE Expiration Date