RE_VC_CNTCT_LVW

(SQL View)
 Index Back

Supplier and Customer Contacts

customer and Supplier contacts view

SELECT A.SETID , 'C' , A.CUST_ID , A.CONTACT_ID , BL.LANGUAGE_CD , CU.NAME1 , BL.NAME1 , BL.TITLE , B.EMAILID FROM PS_CONTACT_CUST A , PS_CONTACT B , PS_CONTACT_LANG BL , PS_CUSTOMER CU WHERE A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(AA.EFFDT) FROM PS_CONTACT_CUST AA WHERE AA.EFFDT <= %CurrentDateIn AND A.SETID = AA.SETID AND A.CONTACT_ID = AA.CONTACT_ID) AND A.SETID = B.SETID AND A.CONTACT_ID = B.CONTACT_ID AND B.SETID = BL.SETID AND B.CONTACT_ID = BL.CONTACT_ID AND B.EFFDT = BL.EFFDT AND B.EFF_STATUS = 'A' AND B.EFFDT = ( SELECT MAX(BB.EFFDT) FROM PS_CONTACT BB WHERE B.SETID = BB.SETID AND B.CONTACT_ID = BB.CONTACT_ID AND B.EFFDT <= %CurrentDateIn) AND CU.SETID = A.SETID AND A.CUST_ID = CU.CUST_ID UNION SELECT A.SETID , 'V' , A.VENDOR_ID , %NumToChar(A.CNTCT_SEQ_NUM) , V.LANGUAGE_CD ,V.NAME1 , A.CONTACT_NAME , A.CONTACT_TITLE , EMAILID FROM PS_VENDOR_CNTCT A , PS_VENDOR_LANG V WHERE A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(AA.EFFDT) FROM PS_VENDOR_CNTCT AA WHERE A.SETID = AA.SETID AND A.VENDOR_ID = AA.VENDOR_ID AND A.CNTCT_SEQ_NUM = AA.CNTCT_SEQ_NUM AND AA.EFFDT <= %CurrentDateIn) AND A.SETID = V.SETID AND A.VENDOR_ID = V.VENDOR_ID AND A.CONTACT_NAME <> ' ' UNION SELECT A.SETID , 'R' , '99999' , A.CONTACT_ID , B.LANGUAGE_CD , ' ' , B.NAME1 , B.TITLE , A.EMAILID FROM PS_RE_CONTACT A ,PS_RE_CONTACT_LNG B WHERE %Join(COMMON_KEYS, RE_CONTACT A, RE_CONTACT_LNG B)

  • Related Language Record for RE_VC_CNTCT_VW

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

    Prompt Table: SP_SETID_NONVW

    2 VC_TYPE Character(1) VARCHAR2(1) NOT NULL Vendor or Customer flag
    C=Customer
    R=My Contacts
    V=Supplier
    3 VC_ID Character(15) VARCHAR2(15) NOT NULL Vendor or Customer ID
    4 CONTACT_ID Character(15) VARCHAR2(15) NOT NULL Contact Identifier for Treasury Contacts
    5 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    6 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    7 CONTACT_NAME Character(50) VARCHAR2(50) NOT NULL The individual contact name associated with a given bank/counterparty.
    8 TITLE Character(35) VARCHAR2(35) NOT NULL Title
    9 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address