AV_INTV_TMP_VW (SQL View)	Index Back
Initiative Audience Temp view This view holds the Emplid/Ext-org-id of the constituents selected from the Audience Criteria panel. It is deleted each time the Cobol routine is run that updates the Initiative History Table with these constituents. SELECT DISTINCT INSTITUTION, EMPLID, EXT_ORG_ID, AV_AUDIENCE, AUD_TYPE, INTV_CD FROM PS_AV_INTV_TMP_TBL WHERE EMPLID<>' ' AND EMPLID NOT IN (SELECT A.EMPLID FROM PS_AV_INITIATIVE A, PS_AV_INTV_TMP_TBL B WHERE A.INSTITUTION=B.INSTITUTION AND A.INTV_CD=B.INTV_CD AND A.AV_AUDIENCE=B.AV_AUDIENCE AND A.AUD_TYPE=B.AUD_TYPE) OR EXT_ORG_ID<>'' AND EXT_ORG_ID NOT IN (SELECT A.EXT_ORG_ID FROM PS_AV_INITIATIVE A, PS_AV_INTV_TMP_TBL B WHERE A.INSTITUTION=B.INSTITUTION AND A.INTV_CD=B.INTV_CD AND A.AV_AUDIENCE=B.AV_AUDIENCE AND A.AUD_TYPE=B.AUD_TYPE)

AV_INTV_TMP_VW

(SQL View)

Initiative Audience Temp view

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

SELECT DISTINCT A.INSTITUTION , A.EMPLID , A.EXT_ORG_ID , A.AV_AUDIENCE , A.AUD_TYPE , A.INTV_CD FROM PS_AV_INTV_TMP_TBL A WHERE (A.EMPLID<>' ' AND NOT EXISTS ( SELECT 'X' FROM PS_AV_INITIATIVE A1 WHERE A1.INSTITUTION = A.INSTITUTION AND A1.INTV_CD = A.INTV_CD AND A1.AV_AUDIENCE = A.AV_AUDIENCE AND A1.AUD_TYPE= A.AUD_TYPE AND A1.EMPLID = A.EMPLID)) OR (A.EXT_ORG_ID<>' ' AND NOT EXISTS ( SELECT 'X' FROM PS_AV_INITIATIVE B1 WHERE B1.INSTITUTION = A.INSTITUTION AND B1.INTV_CD = A.INTV_CD AND B1.AV_AUDIENCE = A.AV_AUDIENCE AND B1.AUD_TYPE = A.AUD_TYPE AND B1.EXT_ORG_ID = A.EXT_ORG_ID ))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

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_AUDIENCE Character(10) VARCHAR2(10) NOT NULL Audience Code

5 AUD_TYPE Character(1) VARCHAR2(1) NOT NULL Indicates whether the population is being identified for Segmentation, Reporting, or Administration.
A=Administrative
R=Reporting
S=Segmentation

6 INTV_CD Character(10) VARCHAR2(10) NOT NULL Initiative Code

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	INSTITUTION	Character(5)	VARCHAR2(5) NOT NULL	Academic Institution
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_AUDIENCE	Character(10)	VARCHAR2(10) NOT NULL	Audience Code
5	AUD_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Indicates whether the population is being identified for Segmentation, Reporting, or Administration. A=Administrative R=Reporting S=Segmentation
6	INTV_CD	Character(10)	VARCHAR2(10) NOT NULL	Initiative Code