AV_RCG_DES_B_VW

(SQL View)
 Index Back

Endowment to Date 2

This is a view of AV_RCG_DES and AV_ACCOUNT_TBL that sums the amount of Pledge Payments given to the campaign to designations for endowments only. This view is used in conjunction with AV_RCG_DES_A_VW (which sums all endowment amounts) to produce the correct total. The Pledge Payment sum from this view is subtacted from AV_RCG_DES_A_VW to produce the Endowment total. BEFORE 8.2.00 SELECT A.INSTITUTION, A.INTV_CD, SUM(A.RCG_DES_AMT) FROM PS_AV_RCG_DES A, PS_AV_ACCOUNT_TBL B, PS_AV_GIFT_DTL C WHERE A.INSTITUTION = C.INSTITUTION AND A.SESSION_NO = C.SESSION_NO AND A.GIFT_NO = C.GIFT_NO AND A.DESIGNATION = B.DESIGNATION AND B.EFFDT = (SELECT MAX(B1.EFFDT) FROM PS_AV_ACCOUNT_TBL B1 WHERE B1.DESIGNATION = B.DESIGNATION AND B1.EFF_STATUS = 'A') AND A.STATUS = 'A' AND A.RECOGNITION_TYPE = 'H' AND B.AV_CASE_TYPE = 'E' AND C.GIFT_TYPE = 'PP' GROUP BY A.INSTITUTION, A.INTV_CD

SELECT A.INSTITUTION, A.INTV_CD, SUM(A.RCG_DES_AMT) FROM PS_AV_RCG_DES A, PS_AV_ACCOUNT_TBL B, PS_AV_GIFT_DTL C WHERE A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.SESSION_NO = C.SESSION_NO AND A.GIFT_NO = C.GIFT_NO AND A.DESIGNATION = B.DESIGNATION AND B.EFFDT = (SELECT MAX(B1.EFFDT) FROM PS_AV_ACCOUNT_TBL B1 WHERE B1.DESIGNATION = B.DESIGNATION AND B1.EFF_STATUS = 'A') AND A.STATUS = 'A' AND A.RECOGNITION_TYPE = 'H' AND B.AV_CASE_TYPE = 'E' AND C.GIFT_TYPE = 'PP' GROUP BY A.INSTITUTION, A.INTV_CD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
2 INTV_CD Character(10) VARCHAR2(10) NOT NULL Initiative Code
3 RCG_DES_AMT_TOT Number(16,2) DECIMAL(15,2) NOT NULL Field used to sum RCG_DES_AMT from PS_AV_RCG_DES.