AV_MBR_TEMP_VW

(SQL View)
 Index Back

Membership Org Temp File

This view holds the Emplid/Ext-org-id of the constituents selected from the Membership Criteria panel. It is deleted each time the Cobol routine is run that updates the Membership History Table with these constituents. SELECT DISTINCT INSTITUTION, EMPLID, EXT_ORG_ID, AV_MBR_ORG_COD FROM PS_AV_MBR_TEMP_TBL WHERE EMPLID<>' ' AND EMPLID NOT IN (SELECT A.EMPLID FROM PS_AV_MEMBERSHIP A, PS_AV_MBR_TEMP_TBL B WHERE A.INSTITUTION=B.INSTITUTION AND A.AV_MBR_ORG_COD=B.AV_MBR_ORG_COD) OR EXT_ORG_ID<>'' AND EXT_ORG_ID NOT IN (SELECT A.EXT_ORG_ID FROM PS_AV_MEMBERSHIP A, PS_AV_MBR_TEMP_TBL B WHERE A.INSTITUTION=B.INSTITUTION AND A.AV_MBR_ORG_COD=B.AV_MBR_ORG_COD)

SELECT DISTINCT A.BUSINESS_UNIT , A.INSTITUTION , A.EMPLID , A.EXT_ORG_ID , A.AV_MBR_ORG_COD FROM PS_AV_MBR_TEMP_TBL A WHERE (A.EMPLID<>' ' AND NOT EXISTS ( SELECT 'X' FROM PS_AV_MEMBERSHIP A1 WHERE A1.EMPLID = A.EMPLID AND A1.BUSINESS_UNIT = A.BUSINESS_UNIT AND A1.AV_MBR_ORG_COD = A.AV_MBR_ORG_COD)) OR (A.EXT_ORG_ID<>' ' AND NOT EXISTS ( SELECT 'X' FROM PS_AV_MEMBERSHIP B1 WHERE B1.EXT_ORG_ID = A.EXT_ORG_ID AND B1.BUSINESS_UNIT=A.BUSINESS_UNIT AND B1.AV_MBR_ORG_COD=A.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 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
5 AV_MBR_ORG_COD Character(10) VARCHAR2(10) NOT NULL Member Organization Code