RE_SUM_VN_CNTCT

(SQL View)
 Index Back

LS Customer Contact Detail


SELECT PRINCIPAL.PARTY_KEY ,VENDOR.SETID ,VENDOR.VENDOR_ID ,VENDOR.CNTCT_SEQ_NUM ,VENDOR.CONTACT_NAME ,VENDOR.CONTACT_TITLE ,VENDOR.CONTACT_TYPE ,VENDOR.ADDRESS_SEQ_NUM ,VENDOR.PREFERRED_LANGUAGE ,VENDOR.EMAILID ,PHONE.PHONE_TYPE ,PHONE.COUNTRY_CODE ,PHONE.PHONE ,PHONE.EXTENSION , VENDOR_ADDR.COUNTRY , VENDOR_ADDR.ADDRESS1 , VENDOR_ADDR.ADDRESS2 , VENDOR_ADDR.ADDRESS3 , VENDOR_ADDR.ADDRESS4 , VENDOR_ADDR.CITY , VENDOR_ADDR.NUM1 , VENDOR_ADDR.NUM2 , VENDOR_ADDR.HOUSE_TYPE , VENDOR_ADDR.ADDR_FIELD1 , VENDOR_ADDR.ADDR_FIELD2 , VENDOR_ADDR.ADDR_FIELD3 , VENDOR_ADDR.COUNTY , VENDOR_ADDR.STATE , VENDOR_ADDR.POSTAL , VENDOR_ADDR.GEO_CODE , VENDOR_ADDR.IN_CITY_LIMIT FROM PS_RE_LS_PRNC_PTY PRINCIPAL , PS_VNDR_CNTCT_PHN PHONE RIGHT OUTER JOIN PS_VENDOR_CNTCT VENDOR ON (PHONE.SETID = VENDOR.SETID AND PHONE.VENDOR_ID=VENDOR.VENDOR_ID AND PHONE.CNTCT_SEQ_NUM = VENDOR.CNTCT_SEQ_NUM AND PHONE.EFFDT = VENDOR.EFFDT AND PHONE.PHONE_TYPE = 'BUSN') LEFT OUTER JOIN PS_VENDOR_ADDR VENDOR_ADDR ON (VENDOR_ADDR.SETID = VENDOR.SETID AND VENDOR_ADDR.VENDOR_ID= VENDOR.VENDOR_ID AND VENDOR_ADDR.ADDRESS_SEQ_NUM =VENDOR.ADDRESS_SEQ_NUM) WHERE VENDOR.VENDOR_ID =PRINCIPAL.PRINCIPAL_ID AND VENDOR.CNTCT_SEQ_NUM = PRINCIPAL.CNTCT_SEQ_NUM AND PRINCIPAL.PRNCPL_TYPE= '1' AND VENDOR.EFFDT = ( SELECT MAX(EFFDT) FROM PS_VENDOR_CNTCT VENDOR_EFF WHERE VENDOR_EFF.SETID = VENDOR.SETID AND VENDOR_EFF.VENDOR_ID = VENDOR.VENDOR_ID AND VENDOR_EFF.CNTCT_SEQ_NUM = VENDOR.CNTCT_SEQ_NUM AND VENDOR_EFF.EFFDT <= %CurrentDateIn AND VENDOR_EFF.EFF_STATUS = 'A' ) AND %Coalesce(VENDOR_ADDR.EFFDT, %CurrentDateIn) = CASE WHEN VENDOR_ADDR.EFFDT IS NOT NULL THEN ( SELECT MAX(VENDOR_ADDR_EFF.EFFDT) FROM PS_VENDOR_ADDR VENDOR_ADDR_EFF WHERE VENDOR_ADDR_EFF.SETID = VENDOR_ADDR.SETID AND VENDOR_ADDR_EFF.VENDOR_ID = VENDOR_ADDR.VENDOR_ID AND VENDOR_ADDR_EFF.ADDRESS_SEQ_NUM = VENDOR_ADDR.ADDRESS_SEQ_NUM AND VENDOR_ADDR_EFF.EFFDT <= %CurrentDateIn AND VENDOR_ADDR_EFF.EFF_STATUS = 'A') ELSE %CurrentDateIn END

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 PARTY_KEY Number(10,0) DECIMAL(10) NOT NULL Party Key
2 SETID Character(5) VARCHAR2(5) NOT NULL SetID
3 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
4 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact Sequence Number
5 CONTACT_NAME Character(50) VARCHAR2(50) NOT NULL The individual contact name associated with a given bank/counterparty.
6 CONTACT_TITLE Character(35) VARCHAR2(35) NOT NULL The professional title for a given bank/counterparty contact person.
7 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
8 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number

Default Value: CUSTOMER.ADDRESS_SEQ_NUM

Prompt Table: CUST_ADDR_EF_VW

9 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
10 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
11 PHONE_TYPE Character(4) VARCHAR2(4) NOT NULL Phone Type
BUSN=Business Phone
CAMP=Campus Phone
CELL=Cellular Phone
DORM=Dormitory Phone
FAX=FAX
HOME=Home Phone
MAIN=Main
OTR=Other
PGR1=Pager 1
PGR2=Pager 2
TELX=Telex
12 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix
13 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
14 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension
15 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

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

Prompt Table: %EDIT_STATE

29 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
30 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
31 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit