EM_CONVER_VW

(SQL View)
 Index Back

Conversation View

Conversation View

SELECT A.BUSINESS_UNIT , A.VOUCHER_ID , A.CONVER_HDR_SEQ_NUM , B.CONVER_LN_SEQ_NUM , A.CONVER_HDR_DTTM , A.EM_MESSAGE_TOPIC , A.OPRID_FROM , A.OPRID_TO , B.OPRID , B.CONVER_LN_DTTM FROM PS_EM_CONVER_HDR A , PS_EM_CONVER_LINE B WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND A.EM_TOPIC_STATUS = 'O' AND A.CONVER_HDR_SEQ_NUM = B.CONVER_HDR_SEQ_NUM AND B.CONVER_LN_SEQ_NUM = ( SELECT MAX(C1.CONVER_LN_SEQ_NUM) FROM PS_EM_CONVER_LINE C1 WHERE C1.BUSINESS_UNIT = B.BUSINESS_UNIT AND C1.VOUCHER_ID = B.VOUCHER_ID AND C1.CONVER_HDR_SEQ_NUM = B.CONVER_HDR_SEQ_NUM ) AND A.OPRID_TO <> ' ' AND ((EXISTS ( SELECT 'X' FROM PS_VCHR_HDR_QV QV WHERE QV.BUSINESS_UNIT = A.BUSINESS_UNIT AND QV.VOUCHER_ID = A.VOUCHER_ID)) OR (EXISTS ( SELECT 'X' FROM PS_VOUCHER VCHR , PS_PYMNT_VCHR_XREF XREF WHERE VCHR.BUSINESS_UNIT = A.BUSINESS_UNIT AND VCHR.VOUCHER_ID = A.VOUCHER_ID AND VCHR.BUSINESS_UNIT = XREF.BUSINESS_UNIT AND VCHR.VOUCHER_ID = XREF.VOUCHER_ID AND XREF.PYMNT_SELCT_STATUS = 'N'))) UNION SELECT A2.BUSINESS_UNIT , A2.VOUCHER_ID , A2.CONVER_HDR_SEQ_NUM , B2.CONVER_LN_SEQ_NUM , A2.CONVER_HDR_DTTM , A2.EM_MESSAGE_TOPIC , A2.OPRID_FROM , C.OPRID , B2.OPRID , B2.CONVER_LN_DTTM FROM PS_EM_CONVER_HDR A2 , PS_EM_CONVER_LINE B2 , PS_EM_PYM_DISP_USR C WHERE A2.BUSINESS_UNIT = B2.BUSINESS_UNIT AND A2.VOUCHER_ID = B2.VOUCHER_ID AND A2.EM_TOPIC_STATUS = 'O' AND A2.CONVER_HDR_SEQ_NUM = B2.CONVER_HDR_SEQ_NUM AND (B2.OPRID = A2.OPRID_FROM OR B2.OPRID = C.OPRID) AND B2.CONVER_LN_SEQ_NUM = ( SELECT MAX(C2.CONVER_LN_SEQ_NUM) FROM PS_EM_CONVER_LINE C2 WHERE C2.BUSINESS_UNIT = B2.BUSINESS_UNIT AND C2.VOUCHER_ID = B2.VOUCHER_ID AND C2.CONVER_HDR_SEQ_NUM = B2.CONVER_HDR_SEQ_NUM ) AND A2.OPRID_TO = ' ' AND A2.BUSINESS_UNIT = C.BUSINESS_UNIT AND A2.VOUCHER_ID = C.VOUCHER_ID AND A2.CONVER_HDR_SEQ_NUM = C.CONVER_HDR_SEQ_NUM

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
3 CONVER_HDR_SEQ_NUM Number(6,0) INTEGER NOT NULL Conversation Header Sequence Number
4 CONVER_LN_SEQ_NUM Number(6,0) INTEGER NOT NULL Conversation Sequence Number
5 CONVER_HDR_DTTM DateTime(26) TIMESTAMP Conversation Datetime
6 EM_MESSAGE_TOPIC Character(50) VARCHAR2(50) NOT NULL Message Topic
7 OPRID_FROM Character(30) VARCHAR2(30) NOT NULL A user's ID
8 OPRID_TO Character(30) VARCHAR2(30) NOT NULL A user's ID
9 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
10 CONVER_LN_DTTM DateTime(26) TIMESTAMP Conversation Datetime