CONVER_LS_SS_VW

(SQL View)
Index Back

Conversation List


SELECT DISTINCT C.SETID , D.BUSINESS_UNIT , B.CUST_ID , D.ITEM , D.ITEM_LINE , C.CONVER_DTTM_INIT , C.CONVER_SUBJECT , C.CONVER_SUB_TOPIC , C.CONVER_STATUS , B.CONVR_QUAL_FIELD3 , B.CONVR_QUALIFIER_CD , A.NAME1 , C.DESCR FROM PS_CUSTOMER A , PS_CUST_CONVER_DTL B , PS_CUST_CONVER_HDR C , PS_ITEM D WHERE A.SETID = B.SETID AND A.SETID = C.SETID AND A.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = D.BUSINESS_UNIT AND RECNAME = 'CUST_CONVER') AND A.CUST_ID = B.CUST_ID AND A.CUST_ID = C.CUST_ID AND B.CONVR_QUAL_FIELD3 = D.CUST_ID AND B.CONVER_DTTM_INIT = C.CONVER_DTTM_INIT AND B.CONVR_QUAL_FIELD = D.ITEM AND B.ITEM_LINE = D.ITEM_LINE AND A.CUST_STATUS = 'A' AND A.CUST_LEVEL <> 'P'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
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.
4 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.
5 ITEM_LINE Number(6,0) INTEGER NOT NULL Item Line
6 CONVER_DTTM_INIT DateTime(26) TIMESTAMP Initial Conversation DateTime
7 CONVER_SUBJECT Character(30) VARCHAR2(30) NOT NULL Conversation Subject
8 CONVER_SUB_TOPIC Character(30) VARCHAR2(30) NOT NULL Subject Topic
9 CONVER_STATUS Character(1) VARCHAR2(1) NOT NULL Conversation Status
C=Closed
N=New
O=Open
10 CONVR_QUAL_FIELD3 Character(15) VARCHAR2(15) NOT NULL Conversation Qualifier Field3
11 CONVR_QUALIFIER_CD Character(2) VARCHAR2(2) NOT NULL Conversation Qualifier Code
BA=Buy Agremt
BI=Invoice #
CP=Cust Pay
CT=Contract
D=Document
I=Item
L=Lading
P=PO Number
PY=Payment ID
QU=Quote
RM=RMA
SC=Case Num
SO=Sale Order
12 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
13 DESCR Character(30) VARCHAR2(30) NOT NULL Description