VNDR_ADDR_SR_EF

(SQL View)
 Index Back

Supplier Addr EffDt Search VW


SELECT a.setid , a.vendor_id , a.address_seq_num , a.effdt , a.eff_status , a.name1 , a.name2 , a.emailid , a.country , c.descr , a.address1 , a.address2 , a.address3 , a.address4 , (CASE WHEN A.CITY <> ' ' THEN A.CITY ELSE %Substring(X.MESSAGE_TEXT, 1, 25) END) , a.num1 , a.num2 , a.house_type , a.addr_field1 , a.addr_field2 , a.addr_field3 , a.county , a.state , %Coalesce(D.DESCR, X.MESSAGE_TEXT) , a.postal , a.geo_code , a.in_city_limit , a.name1_wthd , a.name2_wthd , b.descr , b.vndr_address_type , b.ccr_addr_type FROM ((((PS_VNDR_ADDR_EF_VW a INNER JOIN PS_VNDR_ADDR_SCROL b ON a.setid = b.setid AND a.vendor_id = b.vendor_id AND a.address_seq_num = b.address_seq_num) LEFT OUTER JOIN PS_COUNTRY_TBL c ON a.country = c.country) LEFT OUTER JOIN PS_STATE_TBL d ON a.country = d.country AND a.state = d.state) INNER JOIN PSMSGCATDEFN X ON (X.MESSAGE_SET_NBR = 95 AND X.MESSAGE_NBR = 430))

  • Parent record: VENDOR

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    2 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    3 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
    4 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    5 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive

    Default Value: A

    6 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    7 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
    8 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
    9 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    10 COUNTRY_NAME Character(30) VARCHAR2(30) NOT NULL Country
    11 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    12 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    13 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    14 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    15 CITY Character(30) VARCHAR2(30) NOT NULL City
    16 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    17 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    18 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat
    WW=Trailer
    19 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    20 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    21 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    22 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    23 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: %EDIT_STATE

    24 STATE_NAME Character(30) VARCHAR2(30) NOT NULL State Name
    25 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    26 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    27 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

    28 NAME1_WTHD Character(40) VARCHAR2(40) NOT NULL Name 1
    29 NAME2_WTHD Character(40) VARCHAR2(40) NOT NULL Name 2
    30 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    31 VNDR_ADDRESS_TYPE Character(4) VARCHAR2(4) NOT NULL Address Type
    BILL=Billing
    BUSN=Business
    CAMP=Campus
    CLMS=Claims
    CSRV=Customer Service
    DORM=Dormitory
    HOME=Home
    LEGL=Legal
    MAIL=Mailing
    OTHR=Other
    OTR2=Other 2
    PERM=Permanent
    PREF=Preferred
    PREM=Premium Payment
    RMIT=Remittance
    VTRN=Veteran
    WORK=Work

    Default Value: BUSN

    32 CCR_ADDR_TYPE Character(3) VARCHAR2(3) NOT NULL SAM Address Type