VNDR_REMIT_VW

(SQL View)
 Index Back

Remit Supplier address view

Remit Supplier address view Added CASE statement to get Short Name when Remit Supplier is different than Invoicing Supplier

SELECT VNDR.SETID , VNDR.VENDOR_ID , LOC.REMIT_SETID , LOC.REMIT_VENDOR , LOC.REMIT_ADDR_SEQ_NUM , LOC.REMIT_LOC , CASE WHEN ADDR.VENDOR_ID = VNDR.VENDOR_ID THEN VNDR.VENDOR_NAME_SHORT ELSE ( SELECT VENDOR_NAME_SHORT FROM PS_VENDOR WHERE SETID = LOC.REMIT_SETID AND VENDOR_ID = LOC.REMIT_VENDOR) END , ADDR.DESCR , ADDR.ADDRESS1 , ADDR.ADDRESS2 , ADDR.ADDRESS3 , ADDR.ADDRESS4 , ADDR.CITY , ADDR.STATE , ADDR.POSTAL , ADDR.DESCR60 FROM PS_VENDOR_LOC LOC , PS_VENDOR VNDR , PS_VENDOR_ADDR_VW5 ADDR WHERE VNDR.SETID = LOC.SETID AND VNDR.VENDOR_ID = LOC.VENDOR_ID AND VNDR.DEFAULT_LOC = LOC.VNDR_LOC AND LOC.EFFDT = ( SELECT MAX(Z.EFFDT) FROM PS_VENDOR_LOC Z WHERE LOC.SETID = Z.SETID AND LOC.VENDOR_ID = Z.VENDOR_ID AND LOC.VNDR_LOC = Z.VNDR_LOC AND Z.EFFDT <= %CurrentDateIn) AND LOC.REMIT_SETID = ADDR.SETID AND LOC.REMIT_VENDOR = ADDR.VENDOR_ID AND LOC.REMIT_ADDR_SEQ_NUM = ADDR.ADDRESS_SEQ_NUM

  • Related Language Record: VNDR_REMIT_VWL

    • # 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 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID
    4 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
    5 REMIT_ADDR_SEQ_NUM Number(5,0) INTEGER NOT NULL Remitting Location
    6 REMIT_LOC Character(10) VARCHAR2(10) NOT NULL Remit to Location
    7 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    10 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    11 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    12 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    13 CITY Character(30) VARCHAR2(30) NOT NULL City
    14 STATE Character(6) VARCHAR2(6) NOT NULL State
    15 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    16 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description