COMM_DTL_SRCH(SQL View) |
Index Back |
|---|---|
Communication Maintenance SrchCOMM_SRCH is the search record for Communication Maintenance. It provides the ability to search based on category, context, method, and administrative function as well as the standard name search. |
| SELECT DISTINCT b.oprclass , d.oprid , a.common_id , a.seq_3c , a.var_data_seq , a.comm_dt , g.name , a.institution , a.admin_function , a.comm_category , a.comm_context , a.comm_method , e.country , e.national_id_type , f.descrshort , e.national_id , e.national_id_msk , b.BIRTHDATE , b.BIRTHDATE_MSK , h.SEX , b.CAMPUS_ID , g.last_name_srch , g.first_name_srch FROM PS_COMMUNICATION a , ps_person_msk_vw b , ps_comm_grp_3c_tbl c , ps_opr_grp_3c_tbl d , ps_pers_nid_msk_vw e , ps_nid_type_tbl f , ps_hcr_person_nm_i g , ps_hcr_per_pde_i h WHERE b.oprclass = e.oprclass AND a.common_id = b.emplid AND a.institution = c.institution AND a.comm_category = c.comm_category AND c.institution = d.institution AND c.group_3c = d.group_3c AND d.inquiry_use_ind = 'Y' AND a.common_id = e.emplid AND a.common_id = g.emplid AND a.common_id = h.emplid AND f.country = e.country AND f.national_id_type = e.national_id_type AND h.effdt = ( SELECT MAX(e1.effdt) FROM ps_hcr_per_pde_i e1 WHERE e.emplid = e1.emplid AND e1.effdt <=%CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  OPRCLASS |
Character(30) | VARCHAR2(30) NOT NULL | Operator Class |
| 2 | OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 3 | COMMON_ID |
Character(11) | VARCHAR2(11) NOT NULL |
Common ID to store Personal ID / Ext Org ID value
Prompt Table: PEOPLE_SRCH |
| 4 | SEQ_3C |
Number(5,0) | INTEGER NOT NULL | Sequence Number |
| 5 | VAR_DATA_SEQ | Number(4,0) | SMALLINT NOT NULL | Variable Data Sequence Number |
| 6 | COMM_DT | Date(10) | DATE | Communication Date |
| 7 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 8 | INSTITUTION | Character(5) | VARCHAR2(5) NOT NULL |
Academic Institution
Default Value: OPR_DEF_TBL_CS.INSTITUTION Prompt Table: INSTITUTION_TBL |
| 9 | ADMIN_FUNCTION | Character(4) | VARCHAR2(4) NOT NULL |
Administrative Function
Prompt Table: ADM_FCN_PPL_VW |
| 10 | COMM_CATEGORY | Character(6) | VARCHAR2(6) NOT NULL |
Communication Category
Prompt Table: COMM_CATG_TBL |
| 11 | COMM_CONTEXT | Character(6) | VARCHAR2(6) NOT NULL |
Communication Context
Prompt Table: COMM_CTXT_TBL |
| 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 Prompt Table: COMM_CTX_ME_TBL |
| 13 | NID_COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | National ID Country |
| 14 | NATIONAL_ID_TYPE | Character(6) | VARCHAR2(6) NOT NULL | National ID Type |
| 15 | NID_DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | NID Short Description |
| 16 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 17 | NATIONAL_ID_MSK | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 18 | BIRTHDATE | Date(10) | DATE | Date of Birth |
| 19 | BIRTHDATE_MSK | Character(10) | VARCHAR2(10) NOT NULL | Date of Birth |
| 20 | SEX | Character(1) | VARCHAR2(1) NOT NULL |
Gender
F=Female M=Male U=Unknown X=Indeterminate/Intersex/Unspec |
| 21 | CAMPUS_ID | Character(16) | VARCHAR2(16) NOT NULL | Campus ID |
| 22 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 23 | FIRST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | First Name |