AV_TRNS_PPNP_VW

(SQL View)
 Index Back

Pledge Schedule VW

Displays Pledge Payments by recognition ----------------------------------------------------------------------------------------------------------------- Code change 31 July 2000 (for SetId/Business Unit functionality) - old code follows: SELECT A.INSTITUTION, A.SESSION_NO, A.GIFT_NO, A.EXPECT_DT, B.EMPLID, B.EXT_ORG_ID, B.RECOGNITION_TYPE, A.FISCAL_YEAR, A.RCVD_DT, (A.RCVD_AMT * B.RECOGNITION_PCT * C.DESIGNATION_PCT) / 10000, A.NTFY_FLG, A.GIFT_REF_NO, (A.EXPECT_AMT * B.RECOGNITION_PCT * C.DESIGNATION_PCT) / 10000, C.DESIGNATION, C.INTV_CD, 'EXP-PP' FROM PS_AV_PLDG_SCHD A, PS_AV_RECOGNITION B, PS_AV_DESIGNATION C WHERE A.SESSION_NO = B.SESSION_NO AND A.SESSION_NO = C.SESSION_NO AND A.INSTITUTION = B.INSTITUTION AND A.INSTITUTION = C.INSTITUTION AND A.GIFT_NO = B. GIFT_NO AND A.GIFT_NO = C. GIFT_NO -----------------------------------------------------------------------------------------------------------------

SELECT B.INSTITUTION , B.BUSINESS_UNIT , A.SESSION_NO , A.GIFT_NO , A.EXPECT_DT , B.EMPLID , B.EXT_ORG_ID , B.RECOGNITION_TYPE , A.FISCAL_YEAR , A.RCVD_DT , %Round(%DecDiv(%DecMult(A.RCVD_AMT , %DecMult(B.RECOGNITION_PCT , C.DESIGNATION_PCT)) , 10000 ) , 2) , A.CURRENCY_CD , A.NTFY_FLG , A.GIFT_REF_NO , %Round(%DecDiv(%DecMult(A.RCVD_AMT , %DecMult(B.RECOGNITION_PCT , C.DESIGNATION_PCT)) , 10000 ) , 2) , C.DESIGNATION , C.INTV_CD , 'EXP-PP' , C.AV_DES_BU , D.ADJUSTMENT_FLG FROM PS_AV_PLDG_SCHD A , PS_AV_RECOGNITION B , PS_AV_DESIGNATION C , PS_AV_PLEDGE_DTL D , PS_AV_SESSION_TBL G , PS_AV_GIFT_DTL H , PS_AV_SESSION_TBL J WHERE A.SESSION_NO = B.SESSION_NO AND A.SESSION_NO = C.SESSION_NO AND C.SESSION_NO = D.SESSION_NO AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND C.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.GIFT_NO = B. GIFT_NO AND A.GIFT_NO = C. GIFT_NO AND C.GIFT_NO = D.GIFT_NO AND D.BUSINESS_UNIT = G.BUSINESS_UNIT AND D.SESSION_NO = G.SESSION_NO AND A.BUSINESS_UNIT = H.BUSINESS_UNIT AND A.GIFT_REF_NO = H.GIFT_NO AND H.SEQ_NO = ( SELECT MAX(H1.SEQ_NO) FROM PS_AV_GIFT_DTL H1 WHERE H1.BUSINESS_UNIT = H.BUSINESS_UNIT AND H1.SESSION_NO = H.SESSION_NO AND H1.GIFT_NO = H.GIFT_NO) AND A.BUSINESS_UNIT = J.BUSINESS_UNIT AND H.SESSION_NO = J.SESSION_NO AND G.SESS_STATUS = 'P' AND D.ADJUSTMENT_FLG = 'N' AND A.GIFT_REF_NO <> ' ' AND J.SESS_STATUS <> 'P'

  • Parent record: AV_PLEDGE_DTL

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
    2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    3 SESSION_NO Character(11) VARCHAR2(11) NOT NULL Session Nbr
    4 GIFT_NO Character(11) VARCHAR2(11) NOT NULL Gift Nbr
    5 GIFT_DT Date(10) DATE Gift Date
    6 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    7 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
    8 RECOGNITION_TYPE Character(1) VARCHAR2(1) NOT NULL Recognition Type
    9 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year
    10 RCVD_DT Date(10) DATE Received Date
    11 RCVD_AMT Number(16,2) DECIMAL(15,2) NOT NULL Received Amount
    12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    13 NTFY_FLG Character(1) VARCHAR2(1) NOT NULL Notification Sent

    Y/N Table Edit

    14 GIFT_REF_NO Character(11) VARCHAR2(11) NOT NULL Gift Reference Number
    15 RCG_DES_AMT Signed Number(17,2) DECIMAL(15,2) NOT NULL Amount
    16 DESIGNATION Character(11) VARCHAR2(11) NOT NULL Designation
    17 INTV_CD Character(10) VARCHAR2(10) NOT NULL Initiative Code
    18 GIFT_TYPE Character(6) VARCHAR2(6) NOT NULL Gift Type
    19 AV_DES_BU Character(5) VARCHAR2(5) NOT NULL Business Unit
    20 ADJUSTMENT_FLG Character(1) VARCHAR2(1) NOT NULL Adjusted Flag
    N=No
    Y=Yes