BUSINESS_UNITAV_MBR_ORG_CODAV_DUES_LVLEFFDTDefault Value: %DATE
DESIGNATIONDefault Value: N
AV_MBR_DES_VW(SQL View) |
Index Back |
|---|---|
Mbrship Dues DesignationView of AV_MBR_DUES_DES table. Used to retrieve the current designation records for a membership organization utilizing effective date logic. ----------------------------------------------------------------------------------------------------------------- Code change 24 July 2000 (for SetId/Business Unit functionality) - old code follows: SELECT A.INSTITUTION, A.AV_MBR_ORG_COD, A.AV_DUES_LVL, A.EFFDT, A.DESIGNATION, A.AV_DES_AMT, A.DESIGNATION_PCT, A.CURRENCY_CD, A.AV_GIFT, A.INTV_CD FROM PS_AV_MBR_DUES_DES A, PS_AV_MBR_DUES_AMT B WHERE A.INSTITUTION=B.INSTITUTION AND A.AV_MBR_ORG_COD=B.AV_MBR_ORG_COD AND A.AV_DUES_LVL=B.AV_DUES_LVL AND A.EFFDT=B.EFFDT AND B.EFFDT IN (SELECT MAX(A1.EFFDT) FROM PS_AV_MBR_DUES_DES A1 WHERE A1.INSTITUTION=A.INSTITUTION AND A1.AV_MBR_ORG_COD=A.AV_MBR_ORG_COD AND A1.AV_DUES_LVL=A.AV_DUES_LVL) AND B.EFF_STATUS='A' ----------------------------------------------------------------------------------------------------------------- |
| SELECT A.BUSINESS_UNIT, A.AV_MBR_ORG_COD, A.AV_DUES_LVL, A.EFFDT, A.AV_DES_BU, A.DESIGNATION, A.AV_DES_AMT, A.DESIGNATION_PCT, A.CURRENCY_CD, A.AV_GIFT, A.INTV_CD FROM PS_AV_MBR_DUES_DES A, PS_AV_MBR_DUES_AMT B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.AV_MBR_ORG_COD = B.AV_MBR_ORG_COD AND A.AV_DUES_LVL = B.AV_DUES_LVL AND A.EFFDT=B.EFFDT AND B.EFF_STATUS='A' AND B.EFFDT IN (SELECT MAX(A1.EFFDT) FROM PS_AV_MBR_DUES_DES A1 WHERE A1.BUSINESS_UNIT = A.BUSINESS_UNIT AND A1.AV_MBR_ORG_COD = A.AV_MBR_ORG_COD AND A1.AV_DUES_LVL = A.AV_DUES_LVL AND A1.EFFDT <= %CURRENTDATEIN) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | AV_MBR_ORG_COD |
Character(10) | VARCHAR2(10) NOT NULL | Member Organization Code |
| 3 | AV_DUES_LVL |
Character(5) | VARCHAR2(5) NOT NULL | Dues Level |
| 4 | EFFDT |
Date(10) | DATE NOT NULL |
Effective Date
Default Value: %DATE |
| 5 | AV_DES_BU | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 6 | DESIGNATION |
Character(11) | VARCHAR2(11) NOT NULL | Designation |
| 7 | AV_DES_AMT | Number(16,2) | DECIMAL(15,2) NOT NULL | Designation Amt |
| 8 | DESIGNATION_PCT | Number(8,4) | DECIMAL(7,4) NOT NULL | Designation Pct |
| 9 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 10 | AV_GIFT | Character(1) | VARCHAR2(1) NOT NULL |
Gift
Default Value: N |
| 11 | INTV_CD | Character(10) | VARCHAR2(10) NOT NULL | Initiative Code |