VNDR_LOOKUP_VW2

(SQL View)
 Index Back

Supplier Lookup No Supplier Ty

Use to Lookup Supplier Address, Remit Supplier Address. Used for RFQ Supplier Quote Group Date Initials Reference # Description 03/01/2007 SID 1621990003 Modified Record to cleanup audit issues on FSCM 9.0 build C307

SELECT a.setid , a.vendor_id , d.vndr_loc , b.address_seq_num , b.effdt , a.vendor_name_short , a.name1 , a.name2 , a.vendor_status , a.vendor_class , a.vendor_persistence , a.vndr_status_po , a.wthd_sw , a.vat_sw , a.remit_setid , a.remit_vendor , a.corporate_setid , a.corporate_vendor , a.hrms_class ,A.NAME1_AC , %subrec(address_sbr2, b) FROM ps_vendor a , PS_VENDOR_ADDR b , ps_vendor_loc d WHERE a.setid = b.setid AND a.vendor_id = b.vendor_id AND a.setid = d.setid AND a.vendor_id = d.vendor_id AND b.effdt = ( SELECT MAX(c.effdt) FROM PS_VENDOR_ADDR c WHERE b.setid = c.setid AND b.vendor_id = c.vendor_id AND b.address_seq_num = c.address_seq_num AND b.address_seq_num = d.addr_seq_num_ordr AND c.effdt <= %CurrentDateIn) AND b.eff_status = 'A' AND D.effdt = ( SELECT MAX(E.effdt) FROM ps_vendor_loc e WHERE d.setid = e.setid AND d.vendor_id = e.vendor_id AND d.VNDR_LOC = e.VNDR_LOC AND e.effdt <= %CurrentDateIn) AND d.eff_status = 'A'

  • Related Language Record: VNDR_LOOKPL_VW2

    • # 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 DEFAULT_LOC Character(10) VARCHAR2(10) NOT NULL Default Location
    4 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
    5 EFFDT Date(10) DATE NOT NULL Effective Date
    6 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
    7 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    8 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
    9 VENDOR_STATUS Character(1) VARCHAR2(1) NOT NULL Vendor Status
    A=Approved
    D=Denied
    E=Unapproved
    I=Inactive
    X=To Be Archived
    10 VENDOR_CLASS Character(1) VARCHAR2(1) NOT NULL Vendor Classification
    A=Attorney
    E=Employee
    H=HCM
    R=Outside Party
    11 VENDOR_PERSISTENCE Character(1) VARCHAR2(1) NOT NULL Persistence
    O=One Time
    P=Permanent
    R=Regular
    S=Single Payment Supplier
    12 VNDR_STATUS_PO Character(1) VARCHAR2(1) NOT NULL Open For Ordering
    N=No
    Y=Yes

    Y/N Table Edit

    13 WTHD_SW Character(1) VARCHAR2(1) NOT NULL Withholding Applicable Flag
    B=Global and 1099
    G=Global
    T=1099

    Y/N Table Edit

    14 VAT_SW Character(1) VARCHAR2(1) NOT NULL Display VAT Flag
    H=Hide VAT checkbox
    N=Display VAT checkbox off
    Y=Display VAT checkbox on

    Y/N Table Edit

    15 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID
    16 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
    17 CORPORATE_SETID Character(5) VARCHAR2(5) NOT NULL SetID
    18 CORPORATE_VENDOR Character(10) VARCHAR2(10) NOT NULL Corporate Vendor
    19 HRMS_CLASS Character(1) VARCHAR2(1) NOT NULL Human Resources Classification
    D=General Deduction
    G=Garn Payee
    H=National Health Serv Provider
    I=Industrial Ins Board Provider
    M=Imp Partner Provider
    P=General Provider
    T=Tax Collector
    20 NAME1_AC Character(40) VARCHAR2(40) NOT NULL Name 1 Alternate Char Set
    21 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

    22 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    23 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    24 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    25 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    26 CITY Character(30) VARCHAR2(30) NOT NULL City
    27 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    28 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    29 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat
    WW=Trailer
    30 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    31 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    32 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    33 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    34 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: STATE_TBL

    35 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    36 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    37 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit