ORD_SLDTOSUM_VW

(SQL View)
 Index Back

Orders by soldto custid

Date Initials Issue Description 110501 dms R-JMILLE-QC4A5 Exclude cancelled orders 120302 dms F-CJORGENS-43 Add cntrct_id 021203 dms IR65621002 Exclude quotes on hold 111506 dms 1576506002 Use ORD_HEADER_LOCK in SQL

SELECT c.setid , o.sold_to_cust_id , o.business_unit , o.order_no , o.order_date , c.name1 , c.cust_level , o.order_type_cd , o.customer_po , o.cntrct_id , o.order_status FROM PS_CUSTOMER c , ps_ord_header o , ps_ord_header_lock L WHERE c.setid = ( SELECT setid FROM ps_set_cntrl_rec WHERE setcntrlvalue = o.business_unit AND recname = 'CUSTOMER') AND c.cust_id = o.sold_to_cust_id AND %Join(COMMON_KEYS, ord_header o, ord_header_lock L) AND (o.order_type_cd = 'SO' OR (o.order_type_cd = 'QUO' AND o.quote_expire_dt >= %CurrentDateIn AND NOT EXISTS( SELECT 'X' FROM PS_ORD_HOLD H WHERE H.BUSINESS_UNIT = O.BUSINESS_UNIT AND H.ORDER_NO = O.ORDER_NO AND H.EFF_STATUS = 'A'))) AND L.credit_reqd = 'N' AND L.hold_reqd = 'N' AND L.background_reqd = 'N' AND L.in_process_flg = 'N' AND o.order_status <> 'X'

  • Related Language Record: ORD_SOLDTO_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    2 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
    3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    4 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
    5 ORDER_DATE Date(10) DATE Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
    6 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    7 CUST_LEVEL Character(1) VARCHAR2(1) NOT NULL Customer Level
    N=New
    P=Prospect
    R=Regular
    S=Short-Term
    T=Temporary
    8 ORDER_TYPE_CD Character(3) VARCHAR2(3) NOT NULL Order Type
    QUO=Sales Quote
    SO=Sales Order
    9 CUSTOMER_PO Character(25) VARCHAR2(25) NOT NULL Customer PO
    10 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
    11 ORDER_STATUS Character(3) VARCHAR2(3) NOT NULL Order Status
    C=Closed
    O=Open
    P=Pending
    X=Canceled