QUICK_CUST_VW

(SQL View)
 Index Back

Customer/Contact/Address View

This record is used in capturing all the contact/customer/address profile. This is what is used for quick customer search on customer, contact or address. Date Initials Issue Description 071906 VS 1519747002 Modified PeopleCode so that correct contact details will populate when create sales order from CSR desktop.

SELECT cnt.SETID , cnt.CONTACT_ID , cst.CUSTOMER_SETID , cst.CUST_ID , cnt.EMAILID , cst.CNTCT_SEQ_NUM , cnt.NAME1 , cnt.CONTACT_FLAG , cust.NAME1 , addr.ADDRESS_SEQ_NUM , %subrec(ADDRESS_SBR,addr) , %subrec(BUS_PHONE_SBR,addr) , cust.CUST_FIELD_C10_A , cust.CUST_FIELD_N12_A , cust.OFAC_STATUS FROM ps_contact cnt , ps_contact_cust cst , ps_customer cust , PS_CUST_ADDRESS addr WHERE %EffdtCheck(contact,cnt,%currentdatein) AND cnt.eff_status = 'A' AND cst.setid=cnt.setid AND cst.contact_id=cnt.contact_id AND cst.effdt=cnt.effdt AND cst.customer_setid=cust.setid AND cst.cust_id=cust.cust_id AND cust.setid = addr.setid AND cust.cust_id = addr.cust_id AND cst.eff_status = 'A' AND %EffdtCheck(cust_address,addr,%currentdatein)

  • Related Language Record: CONTACT_CUST_LN

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    2 CONTACT_ID Character(15) VARCHAR2(15) NOT NULL Contact Identifier for Treasury Contacts
    3 CUSTOMER_SETID Character(5) VARCHAR2(5) NOT NULL Customer SetID
    4 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.
    5 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
    6 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact Sequence Number
    7 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    8 CONTACT_FLAG Character(1) VARCHAR2(1) NOT NULL An attribute that describes the specific purpose of a given bank/counterparty contact person.
    E=External Contact
    I=Internal Contact
    9 CUST_NAME Character(40) VARCHAR2(40) NOT NULL Name
    10 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
    11 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

    Prompt Table: COUNTRY_TBL

    12 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
    13 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
    14 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
    15 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
    16 CITY Character(30) VARCHAR2(30) NOT NULL City
    17 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
    18 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
    19 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
    AB=House Boat
    WW=Trailer
    20 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
    21 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
    22 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
    23 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    24 STATE Character(6) VARCHAR2(6) NOT NULL State

    Prompt Table: %EDIT_STATE

    25 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    26 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
    27 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

    Y/N Table Edit

    28 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix
    29 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
    30 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension
    31 FAX Character(24) VARCHAR2(24) NOT NULL Fax Number
    32 CUST_FIELD_C10_A Character(10) VARCHAR2(10) NOT NULL Customer Char 10 A
    33 CUST_FIELD_N12_A Number(16,3) DECIMAL(15,3) NOT NULL Customer Nbr 12.3 A
    34 OFAC_STATUS Character(1) VARCHAR2(1) NOT NULL OFAC Status
    B=Blocked
    C=Cleared
    R=Review
    V=Valid