ORG_CHKLST_VW(SQL View) |
Index Back |
|---|---|
Organization Checklist Summ VwORG_CHKLST_VW provides a summary of all the checkists and checklist items assigned to an organization. This view is linked to Oprid so that an operator will see only those checklists to which he/she has access. |
| SELECT DISTINCT a.OPRID , d.common_id , d.seq_3c , d.CHECKLIST_DTTM , d.ADMIN_FUNCTION , d.CHECKLIST_CD , e.STATUS_DT , e.DUE_DT , e.RESPONSIBLE_ID , d.INSTITUTION , b.SCC_CHECKLIST_TYPE , e.CHKLST_ITEM_CD , e.ITEM_STATUS FROM ps_opr_grp_3c_tbl a , PS_CS_CHKLST_TBL b , ps_cs_chklst_3cgrp c , ps_person_checklst d , ps_org_chk_item e WHERE a.institution = b.institution AND b.institution = c.institution AND a.group_3c = c.group_3c AND a.inquiry_use_ind = 'Y' AND c.checklist_cd = b.checklist_cd AND b.effdt = ( SELECT MAX(effdt) FROM PS_CS_CHKLST_TBL x WHERE x.institution = b.institution AND x.checklist_cd = b.checklist_cd AND x.effdt <= %CurrentDateIn) AND b.eff_status = 'A' AND d.checklist_cd = c.checklist_cd AND d.common_id = e.ext_org_id AND d.checklist_dttm = e.checklist_dttm |
| # | 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 |
| 3 | SEQ_3C |
Number(5,0) | INTEGER NOT NULL | Sequence Number |
| 4 | CHECKLIST_DTTM |
DateTime(26) | TIMESTAMP NOT NULL | Checklist Date Time |
| 5 | ADMIN_FUNCTION | Character(4) | VARCHAR2(4) NOT NULL |
Administrative Function
Prompt Table: ADM_FUNCTN_TBL |
| 6 | CHECKLIST_CD | Character(6) | VARCHAR2(6) NOT NULL |
Checklist Code
Prompt Table: CS_CHKLST_TBL |
| 7 | STATUS_DT | Date(10) | DATE | Status Date |
| 8 | DUE_DT | Date(10) | DATE | Expected Birth Date |
| 9 | RESPONSIBLE_ID | Character(11) | VARCHAR2(11) NOT NULL |
Responsible ID
Prompt Table: PEOPLE_SRCH |
| 10 | INSTITUTION | Character(5) | VARCHAR2(5) NOT NULL |
Academic Institution
Default Value: OPR_DEF_TBL_CS.INSTITUTION Prompt Table: INSTITUTION_TBL |
| 11 | SCC_CHECKLIST_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Checklist Type.
ADC=Condition List CML=Communication List HIR=Hiring MED=Medical OTH=Other PRM=Promise RQL=Requirements List SAL=Staff Assignment List TER=Termination TRN=Training XFR=Transfer |
| 12 | CHKLST_ITEM_CD | Character(6) | VARCHAR2(6) NOT NULL | Checklist Item Code |
| 13 | ITEM_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Item Status
A=Active C=Completed I=Initiated N=Notified O=Ordered P=Paid Off R=Received S=Second Notification T=Returned W=Waived X=Cancelled |