CHECKLIST_ITEMS

(SQL View)
 Index Back

CommGen Person Checklist

CommGen Person Checklist view is used to build the Communication Data Source with the person checklist data.

SELECT A.COMMON_ID ,A.SEQ_3C ,A.CHECKLIST_SEQ ,C.INSTITUTION ,C.ADMIN_FUNCTION ,C.VAR_DATA_SEQ ,C.TRACKING_SEQ ,C.CHECKLIST_CD ,C.CHECKLIST_STATUS ,D.SCC_CHECKLIST_TYPE ,A.CHKLST_ITEM_CD ,B.DESCR ,B.DESCRSHORT ,B.DESCRLONG ,A.ITEM_STATUS ,A.STATUS_DT ,A.DUE_DT ,A.CURRENCY_CD ,A.DUE_AMT ,A.RESPONSIBLE_ID ,A.ASSOC_ID ,E.DESCR ,A.NAME FROM PS_PERSON_CHK_ITEM A LEFT OUTER JOIN PS_EXT_ORG_EFF_VW E ON A.ASSOC_ID = E.EXT_ORG_ID , PS_SCC_CKLSITM_TBL B , PS_PERSON_CHECKLST C , PS_CS_CHKLST_TBL D WHERE A.CHKLST_ITEM_CD = B.CHKLST_ITEM_CD AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_SCC_CKLSITM_TBL B1 WHERE B1.CHKLST_ITEM_CD = B.CHKLST_ITEM_CD AND B1.EFFDT <= %CurrentDateIn ) AND B.EFF_STATUS = 'A' AND A.COMMON_ID = C.COMMON_ID AND C.ADMIN_FUNCTION = D.ADMIN_FUNCTION AND C.SEQ_3C = A.SEQ_3C AND D.INSTITUTION = C.INSTITUTION AND D.CHECKLIST_CD = C.CHECKLIST_CD AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_CS_CHKLST_TBL D1 WHERE D1.INSTITUTION = D.INSTITUTION AND D1.CHECKLIST_CD = D.CHECKLIST_CD AND D1.EFFDT <= %CurrentDateIn ) AND D.EFF_STATUS = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value
2 SEQ_3C Number(5,0) INTEGER NOT NULL Sequence Number

Default Value: 99999

3 CHECKLIST_SEQ Number(6,0) INTEGER NOT NULL Checklist Sequence
4 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
5 ADMIN_FUNCTION Character(4) VARCHAR2(4) NOT NULL Administrative Function
6 VAR_DATA_SEQ Number(4,0) SMALLINT NOT NULL Variable Data Sequence Number
7 TRACKING_SEQ Number(3,0) SMALLINT NOT NULL Tracking Sequence
8 CHECKLIST_CD Character(6) VARCHAR2(6) NOT NULL Checklist Code
9 CHECKLIST_STATUS Character(1) VARCHAR2(1) NOT NULL Checklist Status
C=Completed
I=Initiated
10 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
11 CHKLST_ITEM_CD Character(6) VARCHAR2(6) NOT NULL Checklist Item Code

Prompt Table: CHKLST_ITEM_VW

12 CHKLST_ITM_DESCR Character(30) VARCHAR2(30) NOT NULL Checklist Item Description
13 CHKLST_ITM_DESCRSH Character(10) VARCHAR2(10) NOT NULL Checklist Item Short Description
14 CHKLST_ITM_DESCRLG Long Character CLOB Checklist Item Long Description
15 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

Default Value: I

16 STATUS_DT Date(10) DATE NOT NULL Status Date

Default Value: PERSON_CHECKLST.STATUS_DT

17 DUE_DT Date(10) DATE NOT NULL Due Date
18 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Prompt Table: CURRENCY_CD_TBL

19 DUE_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Due Amount
20 RESPONSIBLE_ID Character(11) VARCHAR2(11) NOT NULL Responsible ID

Default Value: PERSON_CHK_ITEM.STATUS_CHANGE_ID

Prompt Table: PEOPLE_SRCH

21 ASSOC_ID Character(11) VARCHAR2(11) NOT NULL Association ID
22 ASSOC_ID_DESCR Character(30) VARCHAR2(30) NOT NULL Associated ID Description
23 NAME Character(50) VARCHAR2(50) NOT NULL Name