CUST_EXP_CNT_VW

(SQL View)

SELECT A.CUSTOMER_SETID ,A.CUST_ID ,A.EFFDT ,A.SETID ,A.CONTACT_ID ,A.EFF_STATUS ,A.ADDRESS_SEQ_NUM ,A.CNTCT_SEQ_NUM ,A.BILL_TO_FLG ,A.SOLD_TO_FLG ,A.SHIP_TO_FLG ,A.BROKER_FLG ,A.CRSPD_CUST ,A.ADDRESS_MAINT ,A.ALL_ORDERS_FLG ,A.LAST_MAINT_OPRID ,A.DATE_LAST_MAINT FROM PS_CONTACT_CUST A WHERE A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_CONTACT_CUST B WHERE B.SETID = A.SETID AND B.CONTACT_ID = A.CONTACT_ID AND B.CUSTOMER_SETID = A.CUSTOMER_SETID AND B.CUST_ID = A.CUST_ID AND A.EFFDT <= %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 CUSTOMER_SETID Character(5) VARCHAR2(5) NOT NULL Customer 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 EFFDT Date(10) DATE Effective Date
Default Value: %date

4 CONTACT_SETID Character(5) VARCHAR2(5) NOT NULL Contact SetID

5 CONTACT_ID Character(15) VARCHAR2(15) NOT NULL Contact Identifier for Treasury Contacts

6 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

7 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number

8 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact Sequence Number

9 BILL_TO_FLG Character(1) VARCHAR2(1) NOT NULL Bill To Customer
N=No
Y=Yes

10 SOLD_TO_FLG Character(1) VARCHAR2(1) NOT NULL Sold To Customer

11 SHIP_TO_FLG Character(1) VARCHAR2(1) NOT NULL Ship To Customer
N=No
Y=Yes

12 BROKER_FLG Character(1) VARCHAR2(1) NOT NULL Broker Customer

13 CRSPD_CUST Character(1) VARCHAR2(1) NOT NULL Correspondence Customer

14 ADDRESS_MAINT Character(1) VARCHAR2(1) NOT NULL Maintain Addresses

15 ALL_ORDERS_FLG Character(1) VARCHAR2(1) NOT NULL All Orders Flag
N=No
Y=Yes

16 LAST_MAINT_OPRID Character(30) VARCHAR2(30) NOT NULL Last Maintained By Operator ID

17 DATE_LAST_MAINT Date(10) DATE Date Last Maintained

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	CUSTOMER_SETID	Character(5)	VARCHAR2(5) NOT NULL	Customer 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	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
4	CONTACT_SETID	Character(5)	VARCHAR2(5) NOT NULL	Contact SetID
5	CONTACT_ID	Character(15)	VARCHAR2(15) NOT NULL	Contact Identifier for Treasury Contacts
6	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive
7	ADDRESS_SEQ_NUM	Number(5,0)	INTEGER NOT NULL	Address Sequence Number
8	CNTCT_SEQ_NUM	Number(5,0)	INTEGER NOT NULL	Contact Sequence Number
9	BILL_TO_FLG	Character(1)	VARCHAR2(1) NOT NULL	Bill To Customer N=No Y=Yes
10	SOLD_TO_FLG	Character(1)	VARCHAR2(1) NOT NULL	Sold To Customer
11	SHIP_TO_FLG	Character(1)	VARCHAR2(1) NOT NULL	Ship To Customer N=No Y=Yes
12	BROKER_FLG	Character(1)	VARCHAR2(1) NOT NULL	Broker Customer
13	CRSPD_CUST	Character(1)	VARCHAR2(1) NOT NULL	Correspondence Customer
14	ADDRESS_MAINT	Character(1)	VARCHAR2(1) NOT NULL	Maintain Addresses
15	ALL_ORDERS_FLG	Character(1)	VARCHAR2(1) NOT NULL	All Orders Flag N=No Y=Yes
16	LAST_MAINT_OPRID	Character(30)	VARCHAR2(30) NOT NULL	Last Maintained By Operator ID
17	DATE_LAST_MAINT	Date(10)	DATE	Date Last Maintained