AV_PLDG_P_VW(SQL View) |
Index Back |
---|---|
Posted Pledge DetailReturns all non adjustment pledges that have been posted. Used as a prompt for Pledge No on AV_ADJ_PLEDGE Previous SQL: SELECT DISTINCT C.NAME , A.EMPLID , A.GIFT_NO , A.PLDG_EFFDT , A.GIFT_AMT , A.DATA_ENTRY_DT , A.CURRENCY_CD , A.PLEDGE_PMT_RCVD , A.LAST_PMT_DT , A.FULF_DT , A.PMT_SCHD , A.INSTITUTION FROM PS_AV_PLEDGE_DTL A , PS_AV_SESSION_TBL B , PS_PERSONAL_DATA C WHERE A.SESSION_NO = B.SESSION_NO AND B.SESS_STATUS = 'P' AND A.INSTITUTION = B.INSTITUTION AND A.ADJUSTMENT_FLG = 'N' AND A.EMPLID=C.EMPLID |
SELECT DISTINCT A.BUSINESS_UNIT , C.NAME , A.EMPLID , A.GIFT_NO , A.PLDG_EFFDT , A.GIFT_AMT , A.DATA_ENTRY_DT , A.CURRENCY_CD , A.PLEDGE_PMT_RCVD , A.LAST_PMT_DT , A.FULF_DT , A.PMT_SCHD FROM PS_AV_PLEDGE_DTL A , PS_AV_SESSION_TBL B , PS_HCR_PERSON_NM_I C WHERE A.BUSINESS_UNIT= B.BUSINESS_UNIT AND A.SESSION_NO = B.SESSION_NO AND B.SESS_STATUS = 'P' AND A.ADJUSTMENT_FLG = 'N' AND A.EMPLID = C.EMPLID AND A.SA_ID_TYPE = 'P' AND NOT EXISTS( SELECT 'X' FROM PS_AV_PLEDGE_DTL X , PS_AV_GIFT_DTL G , PS_AV_SESSION_TBL J WHERE X.BUSINESS_UNIT = A.BUSINESS_UNIT AND X.SESSION_NO = A.SESSION_NO AND X.GIFT_NO = A.GIFT_NO AND X.BUSINESS_UNIT = G.BUSINESS_UNIT AND X.GIFT_NO = G.PLEDGE_GIFT_NO AND G.SEQ_NO = ( SELECT MAX(G1.SEQ_NO) FROM PS_AV_GIFT_DTL G1 WHERE G1.BUSINESS_UNIT = G.BUSINESS_UNIT AND G1.SESSION_NO = G.SESSION_NO AND G1.GIFT_NO = G.GIFT_NO) AND X.BUSINESS_UNIT = J.BUSINESS_UNIT AND G.SESSION_NO = J.SESSION_NO AND J.SESS_STATUS <> 'P' ) |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
2 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
3 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PEOPLE_SRCH |
4 | PLEDGE_GIFT_NO | Character(11) | VARCHAR2(11) NOT NULL | Pledge Number |
5 | PLDG_EFFDT | Date(10) | DATE | Effective Date |
6 | GIFT_AMT | Signed Number(17,2) | DECIMAL(15,2) NOT NULL | Gift Amount |
7 | DATA_ENTRY_DT | Date(10) | DATE NOT NULL |
Data Entry Date
Default Value: %date |
8 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL |
Currency Code
Prompt Table: CURRENCY_CD_TBL |
9 | PLEDGE_PMT_RCVD | Number(16,2) | DECIMAL(15,2) NOT NULL | Payments Received |
10 | LAST_PMT_DT | Date(10) | DATE | Last Payment Date |
11 | FULF_DT | Date(10) | DATE NOT NULL |
Fulfillment Date
Default Value: %date |
12 | PMT_SCHD | Character(2) | VARCHAR2(2) NOT NULL |
Payment Schedule
A=Annual BM=Bi-Monthly I=Irregular M=Monthly Q=Quarterly SA=Semi-Annually W=Weekly |