ORG_COMM_VW

(SQL View)
 Index Back

Organizatin Communication View

ORG_COMM_VW provides a summary of all communication with an organization. This view is linked with Oprid so that the summary can be limited to only the Inquiry Groups that the operator is authorized to see.

SELECT DISTINCT a.OPRID , c.common_id , c.seq_3c , 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.SCC_LETTER_CD , c.LETTER_PRINTED_DT , c.LETTER_PRINTED_TM , c.CHECKLIST_SEQ , c.CHECKLIST_SEQ_3C , c.INSTITUTION , C.VAR_DATA_SEQ , c.COMMENT_PRINT_FLAG , c.org_contact , c.org_department , c.org_location 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 AND c.sa_id_type = 'O'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value

Prompt Table: EXT_ORG_TBL

3 SEQ_3C Number(5,0) INTEGER NOT NULL Sequence Number
4 ADMIN_FUNCTION Character(4) VARCHAR2(4) NOT NULL Administrative Function

Prompt Table: ADM_FUNCTN_TBL

5 COMM_CATEGORY Character(6) VARCHAR2(6) NOT NULL Communication Category

Prompt Table: COMM_CATG_VW

6 COMM_CONTEXT Character(6) VARCHAR2(6) NOT NULL Communication Context

Prompt Table: COMM_CA_CTX_VW

7 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

Prompt Table: COMM_CTX_ME_TBL

8 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
9 COMM_ID Character(11) VARCHAR2(11) NOT NULL Person responsible for the communication

Prompt Table: PEOPLE_SRCH

10 COMM_DT Date(10) DATE Communication Date
11 COMM_BEGIN_TM Time(15) TIMESTAMP Communication Begin Time
12 COMM_END_TM Time(15) TIMESTAMP Communication End Time
13 COMPLETED_COMM Character(1) VARCHAR2(1) NOT NULL Communication Completed
14 COMPLETED_ID Character(11) VARCHAR2(11) NOT NULL Completed ID

Prompt Table: PEOPLE_SRCH

15 COMPLETED_DT Date(10) DATE Date Activity Completed
16 COMM_DIRECTION Character(4) VARCHAR2(4) NOT NULL Communication Direction
IN=Incoming Communication
OUT=Outgoing Communication
PERS=In Person
17 UNSUCCESSFUL Character(1) VARCHAR2(1) NOT NULL Unsuccessful Outcome
18 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
19 SCC_LETTER_CD Character(3) VARCHAR2(3) NOT NULL Letter Code
20 LETTER_PRINTED_DT Date(10) DATE Date letter was printed
21 LETTER_PRINTED_TM Time(15) TIMESTAMP Time Letter Printed
22 CHECKLIST_SEQ Number(6,0) INTEGER NOT NULL Checklist Sequence
23 CHECKLIST_SEQ_3C Number(5,0) INTEGER NOT NULL Checklist Seq Number
24 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Default Value: OPR_DEF_TBL_CS.INSTITUTION

Prompt Table: INSTITUTION_TBL

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

Y/N Table Edit

Default Value: N

27 ORG_CONTACT Number(4,0) SMALLINT NOT NULL Contact Nbr
28 ORG_DEPARTMENT Number(3,0) SMALLINT NOT NULL Department Nbr
29 ORG_LOCATION Number(3,0) SMALLINT NOT NULL Location Nbr