C360_PRIMCNC2VW

(SQL View)
Index Back

Current Primary Contact


SELECT A.SETID , A.CUST_ID , A.CNTCT_SEQ_NUM , B.CONTACT_ID , A.NAME1 , A.TITLE , A.ADDRESS_SEQ_NUM , A.EMAILID , A.COMM_METHOD , D.XLATLONGNAME , P1.PHONE , P1.EXTENSION , P2.PHONE , P1.PHONE %Concat '/'%Concat P1.EXTENSION FROM PS_CONTACT_CUST B , PS_CUSTOMER C ,XLATTABLE_VW D , PS_CUST_CONTACT A LEFT OUTER JOIN PS_CUST_CNTCT_PHN P1 ON A.SETID = P1.SETID AND A.CUST_ID = P1.CUST_ID AND A.CNTCT_SEQ_NUM = P1.CNTCT_SEQ_NUM AND P1.PHONE_TYPE='BUSN' LEFT OUTER JOIN PS_CUST_CNTCT_PHN P2 ON A.SETID = P2.SETID AND A.CUST_ID = P2.CUST_ID AND A.CNTCT_SEQ_NUM = P2.CNTCT_SEQ_NUM AND P2.PHONE_TYPE='FAX' WHERE %EffdtCheck(CUST_CONTACT CA,A,%CurrentDateIn) AND A.EFF_STATUS='A' AND A.SETID = B.CUSTOMER_SETID AND A.CUST_ID = B.CUST_ID AND A.CNTCT_SEQ_NUM = B.CNTCT_SEQ_NUM AND %EffdtCheck(CONTACT_CUST CB,B,%CurrentDateIn) AND B.EFF_STATUS = 'A' AND A.SETID = C.SETID AND A.CUST_ID = C.CUST_ID AND A.CNTCT_SEQ_NUM = C.CNTCT_SEQ_NUM AND %EffdtCheck(XLATTABLE_VW XV,D,%CurrentDateIn) AND D.FIELDNAME='COMM_METHOD' AND D.FIELDVALUE=A.COMM_METHOD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

Default Value: OPR_DEF_TBL_FS.SETID

Prompt Table: SP_SETID_NONVW

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.

Prompt Table: CUSTOMER

3 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact Sequence Number
4 CONTACT_ID Character(15) VARCHAR2(15) NOT NULL Contact Identifier for Treasury Contacts

Prompt Table: CNTCT_CUST_VW2

5 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
6 TITLE Character(35) VARCHAR2(35) NOT NULL Title
7 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
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 COMM_METHOD1 Character(30) VARCHAR2(30) NOT NULL Preferred Communication
11 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
12 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension
13 FAX Character(24) VARCHAR2(24) NOT NULL Fax Number
14 C360_PHONE_EXT Character(31) VARCHAR2(31) NOT NULL