CONVR_REC_N_VW

(SQL View)
 Index Back

Recent Conver w/Contact Info


SELECT c.setid , c.cust_id , c.conver_dt , c.conver_seq_num , a.cntct_seq_num , b.name1 , b.title , c.descrlong FROM PS_CUST_CONVER_HDR a , ps_cust_conver c , ps_cust_contact b WHERE a.setid = c.setid AND a.cust_id = c.cust_id AND a.conver_dttm_init = c.conver_dttm_init AND a.conver_dt = ( SELECT MAX(x.conver_dt) FROM ps_cust_conver x WHERE x.setid = c.setid AND x.cust_id = c.cust_id) AND conver_seq_num = ( SELECT MAX(conver_seq_num) FROM ps_cust_conver y WHERE y.setid = c.setid AND y.cust_id = c.cust_id AND y.conver_dt = c.conver_dt) AND b.setid = c.setid AND b.cust_id = c.cust_id AND b.cntct_seq_num = a.cntct_seq_num AND effdt = ( SELECT MAX(effdt) FROM ps_cust_contact z WHERE z.setid=c.setid AND z.cust_id=c.cust_id AND z.cntct_seq_num=a.cntct_seq_num AND z.effdt <= c.conver_dt AND z.eff_status = 'A')

# 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 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: CUSTOMER

3 CONVER_DT Date(10) DATE NOT NULL Conversation Date

Default Value: %date

4 CONVER_SEQ_NUM Number(6,0) INTEGER NOT NULL Conversation Sequence Number

Prompt Table: CUST_CONVER

5 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact Sequence Number

Default Value: CUSTOMER.CNTCT_SEQ_NUM

Prompt Table: CUST_CONTACT

6 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
7 TITLE Character(35) VARCHAR2(35) NOT NULL Title
8 DESCRLONG Long Character CLOB NOT NULL Long Description