AR_WC_CONR_VW

(SQL View)
 Index Back

AR WorkCenter Conversations

Conversations Search Record

SELECT DISTINCT B.SETID ,B.BUSINESS_UNIT , B.CUST_ID , B.CONVER_DTTM_INIT , B.CONVER_DT , B.NAME1 , B.DESCR , B.CONVER_SUBJECT , B.CONVER_STATUS , B.CONVER_SUB_TOPIC , B.THREAD_COUNT1 , B.ATTACH_EXIST , B.PROMISE_DT , B.PROMISE_AMT , B.PAY_PROMISE_AMT , B.CURRENCY_CD , B.AR_PROMISE_STATUS , A.CONVR_QUAL_FIELD , %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) , L.CONVR_QUAL_FIELD , B.KEYWORD1 , B.FOLLOW_UP_ACTION , B.REVIEW_COMP , B.SUP_REVIEW , B.SUP_STATUS , 0 , B.CONTACT_ID ,B.OPRID ,B.REVIEW_OPRID ,B.REVIEW_NEXT_DT ,B.FOLLOW_UP_OPRID ,B.FOLLOW_UP_COMP ,B.PROMISE_OPRID ,B.PROMISE_ACTION ,B.PROMISE_OVRIDE ,B.PROMISE_RVW_DT ,B.PROMISE_COMP , B.CONVER_DTTM_INIT FROM PS_AR_CUST_AL_WCVW B LEFT OUTER JOIN PS_CUST_CONVER_DTL A ON B.SETID = A.SETID AND B.CONVER_DTTM_INIT = A.CONVER_DTTM_INIT AND B.CUST_ID = A.CUST_ID AND A.CONVR_QUALIFIER_CD = 'I' LEFT OUTER JOIN PS_CUST_CONVER_DTL E ON 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' LEFT OUTER JOIN PS_CUST_CONVER_DTL F ON 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' LEFT OUTER JOIN PS_CUST_CONVER_DTL G ON 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' LEFT OUTER JOIN PS_CUST_CONVER_DTL H ON 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' LEFT OUTER JOIN PS_CUST_CONVER_DTL I ON 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' LEFT OUTER JOIN PS_CUST_CONVER_DTL J ON 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' LEFT OUTER JOIN PS_CUST_CONVER_DTL K ON 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' LEFT OUTER JOIN PS_CUST_CONVER_DTL L ON B.SETID = L.SETID AND B.CONVER_DTTM_INIT = L.CONVER_DTTM_INIT AND B.CUST_ID = L.CUST_ID AND L.CONVR_QUALIFIER_CD = 'SC' WHERE B.CONVER_STATUS IN ('N','O') AND ((B.REVIEW_COMP = 'N' AND B.PROMISE_TO_PAY = 'N' AND B.REVIEW_NEXT_DT <= %CurrentDateIn or B.REVIEW_NEXT_DT is null) OR (B.FOLLOW_UP_ACTION<> ' ' AND B.FOLLOW_UP_COMP='N') OR (B.SUP_REVIEW='Y' AND B.SUP_STATUS <>'R') OR (B.PROMISE_TO_PAY = 'Y' AND B.FOLLOW_UP_ACTION <> ' ' AND B.FOLLOW_UP_COMP = 'N') OR (B.AR_PROMISE_STATUS ='B' AND B.PROMISE_COMP = 'N' AND B.PROMISE_TO_PAY = 'Y'))

# 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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
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: AR_CUST_AL2_VW

4 CONVER_DTTM_INIT DateTime(26) TIMESTAMP NOT NULL Initial Conversation 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_SUBJECT Character(30) VARCHAR2(30) NOT NULL Conversation Subject

Prompt Table: CONVER_SUBJ_VW

9 CONVER_STATUS Character(1) VARCHAR2(1) NOT NULL Status
C=Closed
N=New
O=Open
10 CONVER_SUB_TOPIC Character(30) VARCHAR2(30) NOT NULL Subject Topic

Prompt Table: CONVER_TOPIC_VW

11 THREAD_COUNT1 Number(6,0) INTEGER NOT NULL Number of Entries
12 ATTACH_EXIST Character(1) VARCHAR2(1) NOT NULL Attachments

Y/N Table Edit

13 PROMISE_DT Date(10) DATE Promise Date
14 PROMISE_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Cash Forecast
15 PAY_PROMISE_AMT Number(27,3) DECIMAL(26,3) NOT NULL Promise to Pay amount
16 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
17 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
18 ITEM Character(30) VARCHAR2(30) NOT NULL Uniquely identifies an invoice credit memo or debit memo. The item ID is also called the invoice number.
19 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice
20 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
21 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.
22 BILL_OF_LADING Character(30) VARCHAR2(30) NOT NULL Specifies a bill of lading number associated with a Receivables item.
23 PO_REF Character(30) VARCHAR2(30) NOT NULL Specifies the purchase order number associated with a receivables item.
24 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.
25 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
26 SUBROG_CASE_NUM Character(30) VARCHAR2(30) NOT NULL Subrogation case number used for Insurance
27 KEYWORD1 Character(10) VARCHAR2(10) NOT NULL Keyword
28 FOLLOW_UP_ACTION Character(10) VARCHAR2(10) NOT NULL Follow-Up Action
29 REVIEW_COMP Character(1) VARCHAR2(1) NOT NULL Review Completed
30 SUP_REVIEW Character(1) VARCHAR2(1) NOT NULL Supervisor Review
31 SUP_STATUS Character(1) VARCHAR2(1) NOT NULL Supervisor Review Status
I=Incomplete
N=New
R=Reviewed
32 CONTRACT_LINE_NUM Number(3,0) SMALLINT NOT NULL Contract Line Num
33 CONTACT_ID Character(15) VARCHAR2(15) NOT NULL Contact Identifier for Treasury Contacts

Prompt Table: AR_CONVRSND_SCR

34 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

Prompt Table: PSOPRDEFN_SRCH

35 REVIEW_OPRID Character(30) VARCHAR2(30) NOT NULL User ID
36 REVIEW_NEXT_DT Date(10) DATE Next Review Date
37 FOLLOW_UP_OPRID Character(30) VARCHAR2(30) NOT NULL User ID
38 FOLLOW_UP_COMP Character(1) VARCHAR2(1) NOT NULL Follow Up Completed
39 PROMISE_OPRID Character(30) VARCHAR2(30) NOT NULL Promise Operator ID
40 PROMISE_ACTION Character(4) VARCHAR2(4) NOT NULL CWB Broken Promise Action
41 PROMISE_OVRIDE Character(1) VARCHAR2(1) NOT NULL CWB Override Promise Status
42 PROMISE_RVW_DT Date(10) DATE Promise Review Date
43 PROMISE_COMP Character(1) VARCHAR2(1) NOT NULL CWB Promise Complete
44 CONVER_DTTM DateTime(26) TIMESTAMP Conversation DateTime