AV_MEMBER7_VW

(SQL View)
 Index Back

Membership View

This view displays all the unique occurences for a particular member org code. The data is selected with the max of the expiration date for a particular member org type. ========================================================= view modified on 8/12/99, old view is below. The new view WHERE clause was modified to also pull in records with a NULL EXPIRATION_DT. SELECT DISTINCT INSTITUTION, EMPLID, EXT_ORG_ID, MEMBERSHIP_NBR, AV_MBR_ORG_COD, AV_DUES_LVL, AV_MBRSHP_TYP, START_DT, CURRENCY_CD, MEMBERSHIP_AMT, EXPIRATION_DT, MBR_TYPE, MOTIVATION_CD, SESSION_NO, MBR_PMT_NO FROM PS_AV_MEMBERSHIP A WHERE A.EXPIRATION_DT = (SELECT MAX(A_ED.EXPIRATION_DT) FROM PS_AV_MEMBERSHIP A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EXT_ORG_ID = A_ED.EXT_ORG_ID AND A.INSTITUTION = A_ED.INSTITUTION AND A.AV_MBR_ORG_COD = A_ED.AV_MBR_ORG_COD)

SELECT DISTINCT BUSINESS_UNIT , EMPLID , EXT_ORG_ID , AV_MBR_ORG_COD , MEMBERSHIP_NBR , AV_DUES_LVL , AV_MBRSHP_TYP , START_DT , CURRENCY_CD , MEMBERSHIP_AMT , EXPIRATION_DT , MBR_TYPE , MOTIVATION_CD , SESSION_NO , MBR_PMT_NO , INSTITUTION FROM PS_AV_MEMBERSHIP A WHERE A.EXPIRATION_DT IS NULL OR A.EXPIRATION_DT = ( SELECT MAX(A_ED.EXPIRATION_DT) FROM PS_AV_MEMBERSHIP A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EXT_ORG_ID = A_ED.EXT_ORG_ID AND A.INSTITUTION = A_ED.INSTITUTION AND A.AV_MBR_ORG_COD = A_ED.AV_MBR_ORG_COD)

# 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 MEMBERSHIP_NBR Character(12) VARCHAR2(12) NOT NULL Membership Number
6 AV_DUES_LVL Character(5) VARCHAR2(5) NOT NULL Dues Level

Prompt Table: AV_MBR_DUES_TBL

7 AV_MBRSHP_TYP Character(15) VARCHAR2(15) NOT NULL Membership Type

Prompt Table: AV_MBR_TYP_TBL

8 START_DT Date(10) DATE Start Date
9 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Prompt Table: CURRENCY_CD_TBL

10 MEMBERSHIP_AMT Number(16,2) DECIMAL(15,2) NOT NULL Membership Amount
11 EXPIRATION_DT Date(10) DATE Expiration Date
12 MBR_TYPE Character(1) VARCHAR2(1) NOT NULL Membership type
G=Gift
J=Joint
R=Referral
13 MOTIVATION_CD Character(9) VARCHAR2(9) NOT NULL Appeal Code

Prompt Table: AV_MTVTN_MBR_VW

14 SESSION_NO Character(11) VARCHAR2(11) NOT NULL Session Nbr
15 MBR_PMT_NO Character(11) VARCHAR2(11) NOT NULL Member Payment Number
16 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution