AV_VOL_SRCH_VW (SQL View)	Index Back
Vol Group Assignment SRCH Rec View of the Volunteer Group Table used as search record for Initiative Volunteer Group Asignments. ======================================================== jpm 01/2001 - added "and eff_status = 'a' " check to where clause, so that you can only select from Active Vol Structure groups. ======================================================== JPM 05/02/01 - Modified view. I moved the eff_status check to the sub-select. old sql: SELECT A.INTV_TYPE , A.INTV_CD , A.INSTITUTION , A.VOL_GROUP_NAME FROM PS_AV_VOL_GRP_TBL A WHERE A.INTV_CD <> ' ' AND A.VOL_GRP_LVL = 1 AND A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT (MAX(A1.EFFDT)) FROM PS_AV_VOL_GRP_TBL A1 WHERE A1.INTV_CD = A.INTV_CD AND A1.INSTITUTION = A.INSTITUTION AND A1.VOL_GRP_NBR = A.VOL_GRP_NBR AND A1.EFFDT <= %CurrentDateIn) ======================================================== 5/3/01 (JPM) - Moved Institution to the top (as 1st key). old sql: SELECT A.INTV_TYPE , A.INTV_CD , A.INSTITUTION , A.VOL_GROUP_NAME FROM PS_AV_VOL_GRP_TBL A WHERE A.INTV_CD <> ' ' AND A.VOL_GRP_LVL = 1 AND A.EFFDT = ( SELECT (MAX(A1.EFFDT)) FROM PS_AV_VOL_GRP_TBL A1 WHERE A1.INTV_CD = A.INTV_CD AND A1.INSTITUTION = A.INSTITUTION AND A1.VOL_GRP_NBR = A.VOL_GRP_NBR AND A1.EFFDT <= %CurrentDateIn AND A1.EFF_STATUS = 'A') ========================================================

AV_VOL_SRCH_VW

(SQL View)

Vol Group Assignment SRCH Rec

View of the Volunteer Group Table used as search record for Initiative Volunteer Group Asignments. ======================================================== jpm 01/2001 - added "and eff_status = 'a' " check to where clause, so that you can only select from Active Vol Structure groups. ======================================================== JPM 05/02/01 - Modified view. I moved the eff_status check to the sub-select. old sql: SELECT A.INTV_TYPE , A.INTV_CD , A.INSTITUTION , A.VOL_GROUP_NAME FROM PS_AV_VOL_GRP_TBL A WHERE A.INTV_CD <> ' ' AND A.VOL_GRP_LVL = 1 AND A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT (MAX(A1.EFFDT)) FROM PS_AV_VOL_GRP_TBL A1 WHERE A1.INTV_CD = A.INTV_CD AND A1.INSTITUTION = A.INSTITUTION AND A1.VOL_GRP_NBR = A.VOL_GRP_NBR AND A1.EFFDT <= %CurrentDateIn) ======================================================== 5/3/01 (JPM) - Moved Institution to the top (as 1st key). old sql: SELECT A.INTV_TYPE , A.INTV_CD , A.INSTITUTION , A.VOL_GROUP_NAME FROM PS_AV_VOL_GRP_TBL A WHERE A.INTV_CD <> ' ' AND A.VOL_GRP_LVL = 1 AND A.EFFDT = ( SELECT (MAX(A1.EFFDT)) FROM PS_AV_VOL_GRP_TBL A1 WHERE A1.INTV_CD = A.INTV_CD AND A1.INSTITUTION = A.INSTITUTION AND A1.VOL_GRP_NBR = A.VOL_GRP_NBR AND A1.EFFDT <= %CurrentDateIn AND A1.EFF_STATUS = 'A') ========================================================

SELECT A.INSTITUTION , A.INTV_TYPE , A.INTV_CD , A.VOL_GROUP_NAME FROM PS_AV_VOL_GRP_TBL A WHERE A.INTV_CD <> ' ' AND A.VOL_GRP_LVL = 1 AND A.EFFDT = ( SELECT (MAX(A1.EFFDT)) FROM PS_AV_VOL_GRP_TBL A1 WHERE A1.INSTITUTION = A.INSTITUTION AND A1.INTV_CD = A.INTV_CD AND A1.VOL_GRP_NBR = A.VOL_GRP_NBR)

# 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 INTV_TYPE Character(10) VARCHAR2(10) NOT NULL Initiative Type
Prompt Table: AV_INTV_TYP_TBL

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

4 VOL_GROUP_NAME Character(50) VARCHAR2(50) NOT NULL Volunteer Group

#	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	INTV_TYPE	Character(10)	VARCHAR2(10) NOT NULL	Initiative Type Prompt Table: AV_INTV_TYP_TBL
3	INTV_CD	Character(10)	VARCHAR2(10) NOT NULL	Initiative Code
4	VOL_GROUP_NAME	Character(50)	VARCHAR2(50) NOT NULL	Volunteer Group