AV_AUD_CRIT_VW (SQL View)	Index Back
Audience Selection Criteria VW this table selects the most current Criteria for a defined audience and displays it in the AV_AUD_CRIT_SP for audience detail

AV_AUD_CRIT_VW

(SQL View)

Audience Selection Criteria VW

this table selects the most current Criteria for a defined audience and displays it in the AV_AUD_CRIT_SP for audience detail

SELECT A.INSTITUTION ,A.AV_MBR_CRIT_TYP ,A.AV_AUDIENCE ,A.EFFDT ,A.SEQ_NUM ,A.AV_LT_PAREN ,A.AV_ATTR_CD ,A.AV_OPERATOR ,A.AV_VALID_VALUE ,A.AV_VALID_SINT ,A.AV_VALID_INT ,A.AV_VALID_DEC ,A.AV_VALID_DT ,A.AV_RANGE ,A.AV_VALID_FROM ,A.AV_VALID_FROM_SINT ,A.AV_VALID_FROM_INT ,A.AV_VALID_FROM_DEC ,A.AV_VALID_FROM_DT ,A.AV_VALID_TO ,A.AV_VALID_TO_SINT ,A.AV_VALID_TO_INT ,A.AV_VALID_TO_DEC ,A.AV_VALID_TO_DT ,A.AV_RT_PAREN ,A.AV_LOGIC FROM PS_AV_AUD_CRIT_TBL A WHERE A.EFFDT IN (SELECT MAX(B1.EFFDT) FROM PS_AV_AUDIENCE_TBL B1 WHERE B1.INSTITUTION=A.INSTITUTION AND B1.AV_MBR_CRIT_TYP=A.AV_MBR_CRIT_TYP AND B1.AV_AUDIENCE=A.AV_AUDIENCE AND B1.EFF_STATUS='A')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
Default Value: AV_OPR_DEF_TBL.INSTITUTION
Prompt Table: INSTITUTN_SCRTY

2 AV_MBR_CRIT_TYP Character(1) VARCHAR2(1) NOT NULL This field is used to capture the type of Membership Criteria the user is setting up. It will identify whether to search for Person (people) or Organization (businesses).
B=Both
O=Organization
P=Person

3 AV_AUDIENCE Character(10) VARCHAR2(10) NOT NULL Audience Code

4 EFFDT Date(10) DATE Effective Date
Default Value: %date

5 SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence

6 AV_LT_PAREN Character(1) VARCHAR2(1) NOT NULL Left Parenthesis
Y/N Table Edit
Default Value: N

7 AV_ATTR_CD Character(6) VARCHAR2(6) NOT NULL Attribute Code

8 AV_OPERATOR Character(4) VARCHAR2(4) NOT NULL Operator values - = and <>
EQL=Equal
GEQL=Greater Than or Equal To
GTHN=Greater Than
LEQL=Less Than or Equal To
LTHN=Less Than
NEQL=Not Equal

9 AV_VALID_VALUE Character(15) VARCHAR2(15) NOT NULL Valid Value
Prompt Table: %EDITTABLE

10 AV_VALID_SINT Number(5,0) INTEGER NOT NULL Valid Value
Prompt Table: %EDITTABLE

11 AV_VALID_INT Number(10,0) DECIMAL(10) NOT NULL Valid Value
Prompt Table: %EDITTABLE

12 AV_VALID_DEC Signed Number(16,2) DECIMAL(14,2) NOT NULL Valid Value
Prompt Table: %EDITTABLE

13 AV_VALID_DT Date(10) DATE Date

14 AV_RANGE Character(1) VARCHAR2(1) NOT NULL Range of Values
Y/N Table Edit
Default Value: N

15 AV_VALID_FROM Character(15) VARCHAR2(15) NOT NULL Valid From
Prompt Table: %EDITTABLE

16 AV_VALID_FROM_SINT Number(5,0) INTEGER NOT NULL Valid Value
Prompt Table: %EDITTABLE

17 AV_VALID_FROM_INT Number(10,0) DECIMAL(10) NOT NULL Valid Value
Prompt Table: %EDITTABLE

18 AV_VALID_FROM_DEC Signed Number(16,2) DECIMAL(14,2) NOT NULL Valid Value
Prompt Table: %EDITTABLE

19 AV_VALID_FROM_DT Date(10) DATE Date

20 AV_VALID_TO Character(15) VARCHAR2(15) NOT NULL Valid To
Prompt Table: %EDITTABLE

21 AV_VALID_TO_SINT Number(5,0) INTEGER NOT NULL Valid Value
Prompt Table: %EDITTABLE

22 AV_VALID_TO_INT Number(10,0) DECIMAL(10) NOT NULL Valid Value
Prompt Table: %EDITTABLE

23 AV_VALID_TO_DEC Signed Number(16,2) DECIMAL(14,2) NOT NULL Valid Value
Prompt Table: %EDITTABLE

24 AV_VALID_TO_DT Date(10) DATE Date

25 AV_RT_PAREN Character(1) VARCHAR2(1) NOT NULL Right Parenthesis
Y/N Table Edit
Default Value: N

26 AV_LOGIC Character(3) VARCHAR2(3) NOT NULL logical operator - values AND / OR
AND=And
OR=Or

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	INSTITUTION	Character(5)	VARCHAR2(5) NOT NULL	Academic Institution Default Value: AV_OPR_DEF_TBL.INSTITUTION Prompt Table: INSTITUTN_SCRTY
2	AV_MBR_CRIT_TYP	Character(1)	VARCHAR2(1) NOT NULL	This field is used to capture the type of Membership Criteria the user is setting up. It will identify whether to search for Person (people) or Organization (businesses). B=Both O=Organization P=Person
3	AV_AUDIENCE	Character(10)	VARCHAR2(10) NOT NULL	Audience Code
4	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
5	SEQ_NUM	Number(3,0)	SMALLINT NOT NULL	Sequence
6	AV_LT_PAREN	Character(1)	VARCHAR2(1) NOT NULL	Left Parenthesis Y/N Table Edit Default Value: N
7	AV_ATTR_CD	Character(6)	VARCHAR2(6) NOT NULL	Attribute Code
8	AV_OPERATOR	Character(4)	VARCHAR2(4) NOT NULL	Operator values - = and <> EQL=Equal GEQL=Greater Than or Equal To GTHN=Greater Than LEQL=Less Than or Equal To LTHN=Less Than NEQL=Not Equal
9	AV_VALID_VALUE	Character(15)	VARCHAR2(15) NOT NULL	Valid Value Prompt Table: %EDITTABLE
10	AV_VALID_SINT	Number(5,0)	INTEGER NOT NULL	Valid Value Prompt Table: %EDITTABLE
11	AV_VALID_INT	Number(10,0)	DECIMAL(10) NOT NULL	Valid Value Prompt Table: %EDITTABLE
12	AV_VALID_DEC	Signed Number(16,2)	DECIMAL(14,2) NOT NULL	Valid Value Prompt Table: %EDITTABLE
13	AV_VALID_DT	Date(10)	DATE	Date
14	AV_RANGE	Character(1)	VARCHAR2(1) NOT NULL	Range of Values Y/N Table Edit Default Value: N
15	AV_VALID_FROM	Character(15)	VARCHAR2(15) NOT NULL	Valid From Prompt Table: %EDITTABLE
16	AV_VALID_FROM_SINT	Number(5,0)	INTEGER NOT NULL	Valid Value Prompt Table: %EDITTABLE
17	AV_VALID_FROM_INT	Number(10,0)	DECIMAL(10) NOT NULL	Valid Value Prompt Table: %EDITTABLE
18	AV_VALID_FROM_DEC	Signed Number(16,2)	DECIMAL(14,2) NOT NULL	Valid Value Prompt Table: %EDITTABLE
19	AV_VALID_FROM_DT	Date(10)	DATE	Date
20	AV_VALID_TO	Character(15)	VARCHAR2(15) NOT NULL	Valid To Prompt Table: %EDITTABLE
21	AV_VALID_TO_SINT	Number(5,0)	INTEGER NOT NULL	Valid Value Prompt Table: %EDITTABLE
22	AV_VALID_TO_INT	Number(10,0)	DECIMAL(10) NOT NULL	Valid Value Prompt Table: %EDITTABLE
23	AV_VALID_TO_DEC	Signed Number(16,2)	DECIMAL(14,2) NOT NULL	Valid Value Prompt Table: %EDITTABLE
24	AV_VALID_TO_DT	Date(10)	DATE	Date
25	AV_RT_PAREN	Character(1)	VARCHAR2(1) NOT NULL	Right Parenthesis Y/N Table Edit Default Value: N
26	AV_LOGIC	Character(3)	VARCHAR2(3) NOT NULL	logical operator - values AND / OR AND=And OR=Or