CONVR_LST_SS_VW(SQL View) |
Index Back |
|---|---|
Conversation List |
| SELECT DISTINCT SETID , CUST_ID , CONVER_DTTM_INIT , CONVER_SUBJECT , CONVER_SUB_TOPIC , CONVER_STATUS , ' ' , ' ' FROM PS_CUST_CONVER_HDR WHERE EXISTS ( SELECT 'X' FROM PS_CUST_CONVER CR WHERE CR.SETID = PS_CUST_CONVER_HDR.SETID AND CR.CUST_ID = PS_CUST_CONVER_HDR.CUST_ID AND CR.CONVER_DTTM_INIT = PS_CUST_CONVER_HDR.CONVER_DTTM_INIT AND CR.CONVER_VISIBILITY = 'Y') AND NOT EXISTS ( SELECT 'X' FROM PS_CUST_CONVER_DTL B WHERE B.SETID = PS_CUST_CONVER_HDR.SETID AND B.CUST_ID = PS_CUST_CONVER_HDR.CUST_ID AND B.CONVER_DTTM_INIT = PS_CUST_CONVER_HDR.CONVER_DTTM_INIT AND B.CONVR_QUALIFIER_CD IN ('I','BI')) UNION SELECT DISTINCT A.SETID , A.CUST_ID , A.CONVER_DTTM_INIT , A.CONVER_SUBJECT , A.CONVER_SUB_TOPIC , A.CONVER_STATUS , B.CONVR_QUAL_FIELD , B.CONVR_QUALIFIER_CD FROM PS_CUST_CONVER_HDR A , PS_CUST_CONVER_DTL B WHERE EXISTS ( SELECT 'X' FROM PS_CUST_CONVER CR WHERE CR.SETID = A.SETID AND CR.CUST_ID = A.CUST_ID AND CR.CONVER_DTTM_INIT = A.CONVER_DTTM_INIT AND CR.CONVER_VISIBILITY = 'Y') AND A.SETID = B.SETID AND A.CUST_ID = B.CUST_ID AND A.CONVER_DTTM_INIT = B.CONVER_DTTM_INIT AND B.CONVR_QUALIFIER_CD IN ('I', 'BI') |
| # | 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 | CONVER_DTTM_INIT | DateTime(26) | TIMESTAMP | Initial Conversation DateTime |
| 4 | CONVER_SUBJECT | Character(30) | VARCHAR2(30) NOT NULL | Conversation Subject |
| 5 | CONVER_SUB_TOPIC | Character(30) | VARCHAR2(30) NOT NULL | Subject Topic |
| 6 | CONVER_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Conversation Status
C=Closed N=New O=Open |
| 7 | INVOICE_ID | Character(30) | VARCHAR2(30) NOT NULL | Invoice Number |
| 8 | 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 |