COMM_REL_ENC_VW

(SQL View)
 Index Back

Communication Enclosure View

COMM_ENCL_VW provides a summary of all communications including enclousre codes for an individual. This view is linked with Oprid so that the summary can be limited to only the 3C Groups that the operator is authorized to see.

SELECT DISTINCT a.OPRID , c.EMPLID_RELATED , c.COMMON_ID , c.SEQ_3C , d.ENCL_SEQ_NUM , a.ENTRY_USE_IND , d.ENCL_LTR_CD , c.COMM_DTTM , c.ADMIN_FUNCTION , c.COMM_CATEGORY , c.COMM_CONTEXT , c.COMM_METHOD , c.DEPTID , c.COMM_ID , c.COMM_DT , c.COMM_BEGIN_TM , c.COMM_END_TM , c.COMPLETED_COMM , c.COMPLETED_ID , c.COMPLETED_DT , c.COMM_DIRECTION , c.UNSUCCESSFUL , c.OUTCOME_REASON , c.LETTER_PRINTED_DT , c.LETTER_PRINTED_TM , c.CHECKLIST_SEQ_3C , c.CHECKLIST_SEQ , C.INSTITUTION , C.VAR_DATA_SEQ , c.COMMENT_PRINT_FLAG , c.PROCESS_INSTANCE , c.EXT_ORG_ID , c.SA_ID_TYPE , C.JOINT_COMM FROM ps_opr_grp_3c_tbl a , ps_comm_grp_3c_tbl b , PS_COMMUNICATION c , ps_enclosure_comm d WHERE c.common_id = d.common_id AND c.seq_3c = d.seq_3c AND a.institution = b.institution AND a.group_3c = b.group_3c AND a.inquiry_use_ind = 'Y' AND b.institution = c.institution AND b.comm_category = c.comm_category AND c.joint_comm = 'Y'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

Prompt Table: PSOPRDEFN

2 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value
3 EMPLID_RELATED Character(11) VARCHAR2(11) NOT NULL Related People ID
4 SEQ_3C Number(5,0) INTEGER NOT NULL Sequence Number
5 ENCL_SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence Number
6 ENTRY_USE_IND Character(1) VARCHAR2(1) NOT NULL Update Indicator

Y/N Table Edit

Default Value: N

7 SCC_LETTER_CD Character(3) VARCHAR2(3) NOT NULL Letter Code
8 COMM_DTTM DateTime(26) TIMESTAMP Communication DateTime
9 ADMIN_FUNCTION Character(4) VARCHAR2(4) NOT NULL Administrative Function
10 COMM_CATEGORY Character(6) VARCHAR2(6) NOT NULL Communication Category
11 COMM_CONTEXT Character(6) VARCHAR2(6) NOT NULL Communication Context
12 COMM_METHOD Character(1) VARCHAR2(1) NOT NULL Communication Method
B=Publication
D=Document
E=Email
F=Fax
L=Letter
P=Personal Contact
T=Telephone Call
W=Web
13 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
14 COMM_ID Character(11) VARCHAR2(11) NOT NULL Person responsible for the communication

Prompt Table: PEOPLE_SRCH

15 COMM_DT Date(10) DATE Communication Date
16 COMM_BEGIN_TM Time(15) TIMESTAMP Communication Begin Time
17 COMM_END_TM Time(15) TIMESTAMP Communication End Time
18 COMPLETED_COMM Character(1) VARCHAR2(1) NOT NULL Communication Completed

Y/N Table Edit

19 COMPLETED_ID Character(11) VARCHAR2(11) NOT NULL Completed ID

Prompt Table: PEOPLE_SRCH

20 COMPLETED_DT Date(10) DATE Date Activity Completed
21 COMM_DIRECTION Character(4) VARCHAR2(4) NOT NULL Communication Direction
IN=Incoming Communication
OUT=Outgoing Communication
PERS=In Person
22 UNSUCCESSFUL Character(1) VARCHAR2(1) NOT NULL Unsuccessful Outcome
23 OUTCOME_REASON Character(1) VARCHAR2(1) NOT NULL Outcome Reason
C=Missing Critical Data
E=Invalid Email Address
M=Left Message on Machine
N=No Answer
O=No Org Recipients found
P=Left Message with Person
R=Returned Mail
S=Email Send Error
24 LETTER_PRINTED_DT Date(10) DATE Date letter was printed
25 LETTER_PRINTED_TM Time(15) TIMESTAMP Time Letter Printed
26 CHECKLIST_SEQ_3C Number(5,0) INTEGER NOT NULL Checklist Seq Number
27 CHECKLIST_SEQ Number(6,0) INTEGER NOT NULL Checklist Sequence
28 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Default Value: OPR_DEF_TBL_CS.INSTITUTION

Prompt Table: INSTITUTION_TBL

29 VAR_DATA_SEQ Number(4,0) SMALLINT NOT NULL Variable Data Sequence Number
30 COMMENT_PRINT_FLAG Character(1) VARCHAR2(1) NOT NULL Print Comments for Communications

Y/N Table Edit

Default Value: N

31 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
32 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
33 SA_ID_TYPE Character(1) VARCHAR2(1) NOT NULL ID Type
O=Organization
P=Person
34 JOINT_COMM Character(1) VARCHAR2(1) NOT NULL Create Joint Communications

Y/N Table Edit

Default Value: N