COMM_VW

(SQL View)
 Index Back

Personal Communication View

COMM_VW provides a summary of all communication with 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.COMMON_ID , ' ' , c.SEQ_3C , 0 , a.ENTRY_USE_IND , c.SCC_LETTER_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.EXT_ORG_ID , c.PROCESS_INSTANCE , c.SA_ID_TYPE , C.JOINT_COMM , C.ORG_DEPARTMENT , C.ORG_LOCATION , C.ORG_CONTACT , C.SCC_COMM_LANG , C.SCC_COMM_MTHD , C.SCC_COMM_PROC FROM ps_opr_grp_3c_tbl a , ps_comm_grp_3c_tbl b , PS_COMMUNICATION c WHERE 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

# 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 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
32 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
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

35 ORG_DEPARTMENT Number(3,0) SMALLINT NOT NULL Department Nbr
36 ORG_LOCATION Number(3,0) SMALLINT NOT NULL Location Nbr
37 ORG_CONTACT Number(4,0) SMALLINT NOT NULL Contact Nbr
38 SCC_COMM_LANG Character(3) VARCHAR2(3) NOT NULL Language Used
CFR=Canadian French
DAN=Danish
DUT=Dutch
ENG=English
ESP=Spanish
FRA=French
GER=German
GRK=Greek
INE=International English
ITA=Italian
JPN=Japanese
KOR=Korean
POR=Portuguese
SVE=Swedish
THA=Thai
ZHS=Simplified Chinese
ZHT=Traditional Chinese
39 SCC_COMM_MTHD Character(1) VARCHAR2(1) NOT NULL Method Used
B=Publication
D=Document
E=E-Mail
F=Fax
L=Letter
P=Personal Contact
T=Telephone Call
W=Web
40 SCC_COMM_PROC Character(1) VARCHAR2(1) NOT NULL Process Used
C=Communication Generation
E=Envelope and Label Generation
L=Letter Generation
M=Manual Completion