RE_LS_CUS_CNTCT

(SQL View)
 Index Back

Customer Contact Detail

Used by Lease Contacts to get consolidated customer contact information.

SELECT CONTACT.SETID ,CONTACT.CUST_ID ,CONTACT.CNTCT_SEQ_NUM ,CONTACT.NAME1 ,CONTACT.TITLE ,CONTACT.ADDRESS_SEQ_NUM ,CONTACT.LANGUAGE_CD ,CONTACT.EMAILID ,CONTACT.COMM_METHOD ,CUST_ADDRESS.COUNTRY ,CUST_ADDRESS.ADDRESS1 ,CUST_ADDRESS.ADDRESS2 ,CUST_ADDRESS.ADDRESS3 ,CUST_ADDRESS.ADDRESS4 ,CUST_ADDRESS.CITY ,CUST_ADDRESS.NUM1 ,CUST_ADDRESS.NUM2 ,CUST_ADDRESS.HOUSE_TYPE ,CUST_ADDRESS.ADDR_FIELD1 ,CUST_ADDRESS.ADDR_FIELD2 ,CUST_ADDRESS.ADDR_FIELD3 ,CUST_ADDRESS.COUNTY ,CUST_ADDRESS.STATE ,CUST_ADDRESS.POSTAL ,CUST_ADDRESS.GEO_CODE ,CUST_ADDRESS.IN_CITY_LIMIT ,CONTACT_PHN.COUNTRY_CODE ,CONTACT_PHN.PHONE ,CONTACT_PHN.EXTENSION ,( SELECT PHN.PHONE FROM PS_CONTACT_PHN PHN WHERE CONTACT_PHN.CONTACT_ID = PHN.CONTACT_ID AND PHN.PHONE_TYPE = 'FAX' AND CONTACT_PHN.EFFDT = PHN.EFFDT) AS FAX ,CONTACT.NAME1 FROM PS_CUST_CONTACT CONTACT , PS_CUST_ADDRESS CUST_ADDRESS , PS_CONTACT_CUST CONTACT_CUST , PS_CONTACT_PHN CONTACT_PHN WHERE CUST_ADDRESS.SETID= CONTACT.SETID AND CUST_ADDRESS.CUST_ID= CONTACT.CUST_ID AND CUST_ADDRESS.ADDRESS_SEQ_NUM =CONTACT.ADDRESS_SEQ_NUM AND CONTACT_CUST.SETID = CONTACT.SETID AND CONTACT_CUST.CUST_ID = CONTACT.CUST_ID AND CONTACT_CUST.CNTCT_SEQ_NUM = CONTACT.CNTCT_SEQ_NUM AND CONTACT_CUST.CONTACT_ID = CONTACT_PHN.CONTACT_ID AND %EffdtCheck(CUST_CONTACT B, CONTACT, %CurrentDateIn AND CONTACT.EFF_STATUS = 'A' ) AND %EffdtCheck(CUST_ADDRESS B, CUST_ADDRESS, %CurrentDateIn AND CUST_ADDRESS.EFF_STATUS = 'A') AND %EffdtCheck(CONTACT_CUST B, CONTACT_CUST, %CurrentDateIn AND CONTACT_CUST.EFF_STATUS = 'A' ) AND CONTACT_PHN.EFFDT = CONTACT_CUST.EFFDT AND CONTACT_PHN.PHONE_TYPE = 'BUSN'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
3 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact Sequence Number
4 CONTACT_NAME Character(50) VARCHAR2(50) NOT NULL The individual contact name associated with a given bank/counterparty.
5 TITLE Character(35) VARCHAR2(35) NOT NULL Title
6 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number

Prompt Table: CUST_ADDR_EF_VW

7 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
8 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
9 COMM_METHOD Character(1) VARCHAR2(1) NOT NULL Preferred Communication
A=Email Invoice as Attachment
C=Call
E=Email only
F=Fax
I=XML only
M=Standard Mail
N=Email Notification
P=Email & Print
R=XML & Print
10 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

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

Prompt Table: %EDIT_STATE

24 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
25 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
26 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

27 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix
28 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
29 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension
30 FAX Character(24) VARCHAR2(24) NOT NULL Fax Number
31 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1