PYCYCL_LOC_VW

(SQL View)
 Index Back

Supplier List View

Valid supplier list for sending payment advice through email

SELECT DISTINCT D.USERID , A.SETID , A.VENDOR_ID , C.VNDR_LOC , A.VENDOR_NAME_SHORT , A.NAME1 , B.EMAILID FROM PS_VENDOR A , PS_VENDOR_PAY B , PS_PAYMENT_TBL C , PS_PYCYCL_SEL_LST D WHERE B.EMAIL_ADVICE = 'Y' AND (B.PRENOTE_REQD = 'N' OR (B.PRENOTE_REQD = 'Y' AND B.PRENOTE_STATUS = 'C')) AND A.SETID = B.SETID AND A.VENDOR_ID = B.VENDOR_ID AND C.REMIT_SETID = A.SETID AND C.REMIT_VENDOR = A.VENDOR_ID AND C.VNDR_LOC = B.VNDR_LOC AND C.bank_setid = D.bank_setid AND C.bank_cd = D.bank_cd AND C.bank_acct_key = D.bank_acct_key AND C.pymnt_method = D.pymnt_method AND C.pay_cycle = D.pay_cycle AND C.pay_cycle_seq_num = D.pay_cycle_seq_num AND C.STL_THROUGH = '02' AND C.PYMNT_TYPE <> 'Z' AND B.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_VENDOR_PAY E WHERE E.SETID = B.SETID AND E.VENDOR_ID = B.VENDOR_ID AND E.VNDR_LOC = B.VNDR_LOC AND E.EFF_STATUS = 'A') AND C.PYMNT_METHOD IN ( SELECT F.PYMNT_METHOD FROM PS_EMAIL_PAY_MTHD F WHERE F.SETID = B.SETID AND F.VENDOR_ID= B.VENDOR_ID AND F.VNDR_LOC = B.VNDR_LOC AND F.EFFDT = B.EFFDT AND F.SEQ_NUM = B.SEQ_NUM)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 USERID Character(30) VARCHAR2(30) NOT NULL A user's ID
2 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
3 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
4 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
5 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
6 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
7 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address