VLOC_RADDR_VW

(SQL View)
 Index Back

Remit Supplier Location Addres

Remit Supplier Location Address and Contacts

SELECT DISTINCT a.setid , a.vendor_id , a.vndr_loc , a.effdt , 'REMT' , b.address_seq_num , 0 , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' FROM ps_vendor_loc a , ps_vendor_addr b WHERE a.remit_setid = b.setid AND a.remit_vendor = b.vendor_id AND a.remit_addr_seq_num = b.address_seq_num AND b.effdt = ( SELECT MAX(bb.effdt) FROM ps_vendor_addr bb WHERE bb.setid = b.setid AND bb.vendor_id = b.vendor_id AND bb.address_seq_num = b.address_seq_num AND bb.effdt <= %CurrentDateIn) AND NOT EXISTS ( SELECT 'x' FROM PS_VENDOR_CNTCT c WHERE b.setid = c.setid AND b.vendor_id = c.vendor_id AND b.address_seq_num = c.address_seq_num AND c.effdt <= %CurrentDateIn) UNION SELECT DISTINCT d.setid , d.vendor_id , d.vndr_loc , d.effdt , 'REMT' , e.address_seq_num , f.cntct_seq_num , f.contact_name , f.contact_type , f.contact_title , f.emailid , f.preferred_language , f.url FROM ps_vendor_loc d , ps_vendor_addr e , PS_VENDOR_CNTCT f WHERE d.remit_setid = e.setid AND d.remit_vendor = e.vendor_id AND d.remit_addr_seq_num = e.address_Seq_num AND e.effdt = ( SELECT MAX(ee.effdt) FROM ps_vendor_addr ee WHERE ee.setid = e.setid AND ee.vendor_id = e.vendor_id AND ee.address_seq_num = e.address_seq_num AND ee.effdt <= %CurrentDateIn) AND e.setid = f.setid AND e.vendor_id = f.vendor_id AND e.address_seq_num = f.address_seq_num AND f.effdt = ( SELECT MAX(ff.effdt) FROM PS_VENDOR_CNTCT ff WHERE ff.setid = f.setid AND ff.vendor_id = f.vendor_id AND ff.cntct_seq_num = f.cntct_seq_num AND ff.effdt <= %CurrentDateIn) AND f.cntct_seq_num = ( SELECT MIN(fff.cntct_seq_num) FROM PS_VENDOR_CNTCT fff WHERE fff.setid = f.setid AND fff.vendor_id = f.vendor_id AND fff.address_seq_num = e.address_seq_num AND fff.effdt = ( SELECT MAX(ffff.effdt) FROM PS_VENDOR_CNTCT ffff WHERE ffff.setid = fff.setid AND ffff.vendor_id = fff.vendor_id AND ffff.cntct_seq_num = fff.cntct_seq_num AND fff.effdt <= %CurrentDateIn) AND fff.eff_status = 'A')

# 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 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
4 EFFDT Date(10) DATE Effective Date

Default Value: %date

5 VNDR_ADDR_TYPE Character(4) VARCHAR2(4) NOT NULL Vendor Address Type
ORDR=Order Address
PRIM=Primary Address
REMT=Remit Address
6 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
7 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact Sequence Number
8 CONTACT_NAME Character(50) VARCHAR2(50) NOT NULL The individual contact name associated with a given bank/counterparty.
9 CONTACT_TYPE Character(1) VARCHAR2(1) NOT NULL An attribute that describes the specific purpose of a given bank/counterparty contact person.
B=Billing Contact
C=Contract Collaborator
D=Commercial Paper Contact
E=External Contact
F=Cash Forecast
G=General
I=Internal Corporate Contact
L=Line of Credit Contact
M=Executive Management
O=Investment Pool Contact
P=Accounts Payable
R=Broker
S=Sales Contact
V=Service Contact
W=Warehousing/Shipping Contact
10 CONTACT_TITLE Character(35) VARCHAR2(35) NOT NULL The professional title for a given bank/counterparty contact person.
11 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
12 PREFERRED_LANGUAGE Character(3) VARCHAR2(3) NOT NULL Preferred Language
ARA=Arabic
CFR=Canadian French
CZE=Czech
DAN=Danish
DUT=Dutch
E=English
ENG=English
ESP=Spanish
F=French
FIN=Finnish
FRA=French
GER=German
HUN=Hungarian
INE=International English
ITA=Italian
JPN=Japanese
KOR=Korean
NOR=Norwegian
POL=Polish
POR=Portuguese
RUS=Russian
SVE=Swedish
THA=Thai
UKE=UK English
ZHS=Simplified Chinese
ZHT=Traditional Chinese
13 URL Character(254) VARCHAR2(254) NOT NULL Internet URL (Universal Resource Locator)