CUST_SECNTCT_VW

(SQL View)
 Index Back

Sec contact credit card info

Secondary contact credit card information.

SELECT CC.SETID ,CC.CUST_ID , CT.SETID , CT.CONTACT_ID , CR.CR_CARD_NBR , CR.CR_CARD_TYPE , CR.CR_CARD_FNAME , CR.CR_CARD_LNAME , CR.CR_CARD_EXPMO , CR.CR_CARD_EXPYR , ADR.ADDRESS1 , ADR.CITY , ADR.STATE , ADR.POSTAL , ADR.COUNTRY , PHN.PHONE , CT.EMAILID FROM PS_CONTACT CT , PS_CONTACT_CUST CC , PS_CONTACT_CARD CR , PS_CUST_ADDRESS ADR , PS_CONTACT_PHN PHN , PS_CUSTOMER CUST WHERE %Join(COMMON_KEYS, CONTACT CT , CONTACT_CUST CC) AND %EffdtCheck(CONTACT CT2, CT, %CurrentDateIn) AND CT.EFF_STATUS = 'A' AND CC.EFF_STATUS = 'A' AND %Join(COMMON_KEYS, CONTACT CT , CONTACT_CARD CR) AND %Join(COMMON_KEYS, CONTACT CT , CONTACT_PHN PHN) AND %EffdtCheck(CONTACT_PHN PHN2, PHN, %CurrentDateIn) AND CC.CUSTOMER_SETID = CUST.SETID AND CC.CUST_ID = CUST.CUST_ID AND %Join(COMMON_KEYS, CONTACT_CUST CC , CONTACT_CARD CR) AND CC.CUSTOMER_SETID=ADR.SETID AND CC.CUST_ID = ADR.CUST_ID AND CC.ADDRESS_SEQ_NUM = ADR.ADDRESS_SEQ_NUM AND CR.CR_CARD_NBR <> ' ' AND CUST.CNTCT_SEQ_NUM<>CC.CNTCT_SEQ_NUM AND CT.SETID = ( SELECT SC.SETID FROM PS_SET_CNTRL_REC SC WHERE SC.RECNAME = 'CONTACT' AND SC.SETCNTRLVALUE = CC.SETID)

# 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 CUSTOMER_SETID Character(5) VARCHAR2(5) NOT NULL Customer SetID
4 CONTACT_ID Character(15) VARCHAR2(15) NOT NULL Contact Identifier for Treasury Contacts
5 CR_CARD_NBR Character(44) VARCHAR2(44) NOT NULL "See OM
6 CR_CARD_TYPE Character(3) VARCHAR2(3) NOT NULL "See OM
01=Visa
02=MasterCard
03=Diners Club/Carte Blanche
04=AMEX
05=Discover
06=EnRoute
7 CR_CARD_FNAME Character(40) VARCHAR2(40) NOT NULL Credit Card First Name
8 CR_CARD_LNAME Character(40) VARCHAR2(40) NOT NULL Credit Card Last Name
9 CR_CARD_EXPMO Character(2) VARCHAR2(2) NOT NULL Card Expiration Month
01=January
02=February
03=March
04=April
05=May
06=June
07=July
08=August
09=September
10=October
11=November
12=December
10 CR_CARD_EXPYR Character(4) VARCHAR2(4) NOT NULL Card Expiration Year
2011=2011
2012=2012
2013=2013
2014=2014
2015=2015
2016=2016
2017=2017
2018=2018
2019=2019
2020=2020
11 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
12 CITY Character(30) VARCHAR2(30) NOT NULL City
13 STATE Character(6) VARCHAR2(6) NOT NULL State
14 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
15 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
16 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
17 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address