SP_CUSRCH_NONVW

(SQL View)
 Index Back

Customer Search


SELECT DISTINCT A.SETID , A.CUST_ID , A.NAMESHORT , A.NAME1 , A.NAME2 , B.ALT_NAME1 , C.CR_ANALYST , C.COLLECTOR , A.REMIT_FROM_CUST_ID , A.REMIT_FROM_SETID , A.SHIP_TO_FLG , A.CORPORATE_CUST_ID , A.CORPORATE_SETID , B.CITY , B.STATE , B.POSTAL , B.PHONE FROM PS_CUSTOMER A , PS_CUST_ADDRESS B , PS_CUST_OPTION C WHERE A.CUST_STATUS = 'A' AND A.BILL_TO_FLG = 'Y' AND A.CUST_LEVEL <> 'P' AND A.SETID = B.SETID AND A.CUST_ID = B.CUST_ID AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_CUST_ADDRESS X WHERE B.SETID = X.SETID AND B.CUST_ID = X.CUST_ID AND B.ADDRESS_SEQ_NUM = X.ADDRESS_SEQ_NUM AND EFFDT <= %CurrentDateIn AND EFF_STATUS = 'A' ) AND A.SETID = C.SETID AND A.CUST_ID = C.CUST_ID AND C.EFFDT = ( SELECT MAX(C2.EFFDT) FROM PS_CUST_OPTION C2 WHERE C2.SETID = C.SETID AND C2.CUST_ID = C.CUST_ID AND C2.EFFDT <= %CurrentDateIn AND C2.EFF_STATUS = 'A')

  • Related Language Record: SP_CSRCVW1_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL 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 NAMESHORT Character(10) VARCHAR2(10) NOT NULL Short Name
    4 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    5 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
    6 ALT_NAME1 Character(40) VARCHAR2(40) NOT NULL Alternate Name 1
    7 CR_ANALYST Character(8) VARCHAR2(8) NOT NULL Specifies the personnel code for the person who works with a customer to establish credit limits and payment terms. This field is required for a bill-to customer.
    8 COLLECTOR Character(8) VARCHAR2(8) NOT NULL Specifies the personnel code for the person who works with a customer to collect overdue payments.
    9 REMIT_FROM_CUST_ID Character(15) VARCHAR2(15) NOT NULL Remit From Customer
    10 REMIT_FROM_SETID Character(5) VARCHAR2(5) NOT NULL SetID
    11 SHIP_TO_FLG Character(1) VARCHAR2(1) NOT NULL Ship To Customer
    N=No
    Y=Yes
    12 CORPORATE_CUST_ID Character(15) VARCHAR2(15) NOT NULL Corporate Customer
    13 CORPORATE_SETID Character(5) VARCHAR2(5) NOT NULL SetID
    14 CITY Character(30) VARCHAR2(30) NOT NULL City
    15 STATE Character(6) VARCHAR2(6) NOT NULL State
    16 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    17 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone