VNDR_LOOKUP_REQ

(SQL View)
 Index Back

Supplier Lookup for Requisitio

Use to Lookup Supplier Address in Requisition, Remit Supplier Address

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.VENDOR_STATUS = 'A' AND A.VNDR_STATUS_PO = 'Y' AND A.SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND D.SETID = A.SETID AND D.VENDOR_ID = A.VENDOR_ID 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' 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'

  • Related Language Record: VNDR_LOOKPL_REQ

    • # 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