FS_CUST_AL_SRCH

(SQL View)
 Index Back

All Active Customers


SELECT DISTINCT B.SETID ,B.BUSINESS_UNIT , B.CUST_ID , B.CONVER_DTTM_INIT , B.CONVER_DT , B.NAME1 , B.DESCR , B.CONVER_STATUS , B.THREAD_COUNT1 , B.ATTACH_EXIST , %Substring(E.CONVR_QUAL_FIELD ,1 ,22) , %Substring(F.CONVR_QUAL_FIELD ,1 ,25) , G.CONVR_QUAL_FIELD , H.CONVR_QUAL_FIELD , I.CONVR_QUAL_FIELD , %Substring(J.CONVR_QUAL_FIELD ,1 ,15) , %Substring(K.CONVR_QUAL_FIELD ,1 ,10) , B.CONVER_SUBJECT , B.CONVER_SUB_TOPIC , B.SUP_REVIEW , B.SUP_STATUS , B.REVIEW_COMP , B.REVIEW_NEXT_DT , B.PROMISE_AMT , B.PAY_PROMISE_AMT , B.PROMISE_DT , B.AR_PROMISE_STATUS FROM PS_AR_CUST_AL_SRVW B , PS_CUST_CONVER_DTL A , PS_CUST_CONVER_DTL E , PS_CUST_CONVER_DTL F , PS_CUST_CONVER_DTL G , PS_CUST_CONVER_DTL H , PS_CUST_CONVER_DTL I , PS_CUST_CONVER_DTL J , PS_CUST_CONVER_DTL K WHERE B.SETID = A.SETID(+) AND B.CUST_ID = A.CUST_ID(+) AND B.CONVER_DTTM_INIT = A.CONVER_DTTM_INIT(+) AND A.CONVR_QUALIFIER_CD(+) = 'I' AND B.SETID = E.SETID(+) AND B.CONVER_DTTM_INIT = E.CONVER_DTTM_INIT(+) AND B.CUST_ID = E.CUST_ID(+) AND E.CONVR_QUALIFIER_CD(+) = 'BI' AND B.SETID = F.SETID (+) AND B.CONVER_DTTM_INIT = F.CONVER_DTTM_INIT (+) AND B.CUST_ID = F.CUST_ID (+) AND F.CONVR_QUALIFIER_CD(+) = 'CT' AND B.SETID = G.SETID (+) AND B.CONVER_DTTM_INIT = G.CONVER_DTTM_INIT (+) AND B.CUST_ID = G.CUST_ID (+) AND G.CONVR_QUALIFIER_CD(+) = 'D' AND B.SETID = H.SETID(+) AND B.CONVER_DTTM_INIT = H.CONVER_DTTM_INIT(+) AND B.CUST_ID = H.CUST_ID(+) AND H.CONVR_QUALIFIER_CD(+) = 'L' AND B.SETID = I.SETID(+) AND B.CONVER_DTTM_INIT = I.CONVER_DTTM_INIT(+) AND B.CUST_ID = I.CUST_ID(+) AND I.CONVR_QUALIFIER_CD(+) = 'P' AND B.SETID = J.SETID (+) AND B.CONVER_DTTM_INIT = J.CONVER_DTTM_INIT (+) AND B.CUST_ID = J.CUST_ID (+) AND J.CONVR_QUALIFIER_CD(+) = 'PY' AND B.SETID = K.SETID (+) AND B.CONVER_DTTM_INIT = K.CONVER_DTTM_INIT (+) AND B.CUST_ID = K.CUST_ID (+) AND K.CONVR_QUALIFIER_CD(+) = 'SO'

  • Related Language Record: FS_CUST_AL_LVW

    • # 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: SP_SETID_NONVW

    2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

    Prompt Table: SP_AR_SET_NONVW

    3 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.

    Prompt Table: CUST_ALL_VW

    4 CONVER_DTTM_INIT DateTime(26) TIMESTAMP Initial Conversation DateTime

    Default Value: %datetime

    5 CONVER_DT Date(10) DATE Conversation Date
    6 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    8 CONVER_STATUS Character(1) VARCHAR2(1) NOT NULL Status
    C=Closed
    N=New
    O=Open
    9 THREAD_COUNT1 Number(6,0) INTEGER NOT NULL Number of Entries
    10 ATTACH_EXIST Character(1) VARCHAR2(1) NOT NULL Attachments

    Y/N Table Edit

    Default Value: N

    11 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice

    Prompt Table: CONVR_BI_IN_VW

    12 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract

    Prompt Table: CONVR_CT_CN_VW

    13 DOCUMENT Character(30) VARCHAR2(30) NOT NULL Specifies a document used as a reference number for a Receivables item. Document ID is used by the Receivables Update program and the Maintenance Worksheet to "match" related invoices debit memos and credit memos.

    Prompt Table: CONVR_DOC_A_VW

    14 BILL_OF_LADING Character(30) VARCHAR2(30) NOT NULL Specifies a bill of lading number associated with a Receivables item.

    Prompt Table: CONVR_LDNG_A_VW

    15 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.

    Prompt Table: CONVR_PO_A_VW

    16 PAYMENT_ID Character(15) VARCHAR2(15) NOT NULL Identifies a customer payment. This is usually a check number but may be some other identifying number.

    Prompt Table: CONVR_ALPY_P_VW

    17 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

    Prompt Table: CONVR_SO_ON_VW

    18 CONVER_SUBJECT Character(30) VARCHAR2(30) NOT NULL Subject

    Prompt Table: CONVER_SUBJ_VW

    19 CONVER_SUB_TOPIC Character(30) VARCHAR2(30) NOT NULL Subject Topic

    Prompt Table: CONVER_TOPIC_VW

    20 SUP_REVIEW Character(1) VARCHAR2(1) NOT NULL Supervisor Review

    Y/N Table Edit

    21 SUP_STATUS Character(1) VARCHAR2(1) NOT NULL Supervisor Review Status
    I=Incomplete
    N=New
    R=Reviewed
    22 REVIEW_COMP Character(1) VARCHAR2(1) NOT NULL Review Completed

    Y/N Table Edit

    23 REVIEW_NEXT_DT Date(10) DATE Next Review Date
    24 PROMISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Cash Forecast
    25 PAY_PROMISE_AMT Number(27,3) DECIMAL(26,3) NOT NULL Promise to Pay amount
    26 PROMISE_DT Date(10) DATE Promise Date
    27 AR_PROMISE_STATUS Character(1) VARCHAR2(1) NOT NULL Promise Status field used in Collections Workbench
    B=Broken
    C=Cancelled
    K=Kept
    N=None
    O=Open