GM_CNTCT_TYP_VW

(SQL View)
Index Back

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