GM_CNTCT_TYP_VW (SQL View)	Index Back
Grants Contact Type and Code

GM_CNTCT_TYP_VW

(SQL View)

Grants Contact Type and Code

SELECT A.SETID , A.CUST_ID , A.CNTCT_SEQ_NUM , A.EFFDT , C.CONTACT_TYPE , C.CONTACT_CODE , B.EFF_STATUS FROM PS_CUST_CNTCT_TYPE A ,PS_CUST_CONTACT B , PS_CUST_CNTCT_CODE C WHERE A.SETID = B.SETID AND A.CUST_ID = B.CUST_ID AND A.CNTCT_SEQ_NUM = B.CNTCT_SEQ_NUM AND A.EFFDT = ( SELECT MAX(AA.EFFDT) FROM PS_CUST_CNTCT_TYPE AA WHERE A.SETID = AA.SETID AND A.CUST_ID = AA.CUST_ID AND A.CNTCT_SEQ_NUM = AA.CNTCT_SEQ_NUM AND AA.EFFDT <= %CurrentDateIn) AND B.EFFDT = ( SELECT MAX(BB.EFFDT) FROM PS_CUST_CONTACT BB WHERE B.SETID = BB.SETID AND B.CUST_ID = BB.CUST_ID AND B.CNTCT_SEQ_NUM = BB.CNTCT_SEQ_NUM AND BB.EFFDT <= A.EFFDT) AND C.SETID = A.SETID AND C.CONTACT_CODE = A.CONTACT_CODE

# 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: SETID_TBL

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 EFFDT Date(10) DATE NOT NULL Effective Date

5 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

6 CONTACT_CODE Character(6) VARCHAR2(6) NOT NULL Contact Type Code

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

#	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: SETID_TBL
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	EFFDT	Date(10)	DATE NOT NULL	Effective Date
5	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
6	CONTACT_CODE	Character(6)	VARCHAR2(6) NOT NULL	Contact Type Code
7	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive