SCC_TM_LIST_IDSCC_TM_ITEM_IDEMPLIDINSTITUTIONCA=Cancelled
CP=Completed
IP=In Progress
SCC_TM_AGRVW(SQL View) |
Index Back |
|---|---|
View Agreements Record view |
| SELECT A.SCC_TM_LIST_ID ,A.SCC_TM_ITEM_ID ,A.EMPLID ,A.INSTITUTION ,A.SCC_TM_ACCEPT ,A.SCC_TM_ACP_DT , B.PORTAL_NAME , %Coalesce(B.SCC_TM_LABEL , TMPL.SCC_TM_LABEL) ,B.SCC_TM_LIST_STATUS ,%Coalesce(B.SCC_TM_IS_TMPL ,'N') ,D.SCC_TM_TASK_ID ,C.PTAI_FIELD1 , D.scc_tm_page_cd , E.scc_tm_page_name FROM %Table(scc_tm_tran_agr) A JOIN %Table(scc_tm_cfg_reg) D ON A.institution=D.institution JOIN %Table(SCC_TM_PG_CD_VW) E ON E.institution=D.institution AND E.scc_tm_page_cd=D.scc_tm_page_cd LEFT OUTER JOIN %Table(scc_tm_ptailist) B ON A.SCC_TM_LIST_ID = B.SCC_TM_LIST_ID JOIN %Table(scc_tm_ptaitmvw) C ON D.SCC_TM_TASK_ID = C.SCC_TM_LIST_ID AND Replace(C.SCC_TM_ITEM_ID ,'_' ,'') = Replace (%Substring(A.SCC_TM_ITEM_ID, 1, 12) , '_' , '') JOIN %Table(scc_tm_ptailist) TMPL ON C.SCC_TM_LIST_ID = TMPL.SCC_TM_LIST_ID WHERE D.SCC_TM_TASK_ID = %Substring(A.SCC_TM_LIST_ID,1, 10) AND D.SCC_TM_REG_NAME= C.ptai_field1 AND D.effdt=( SELECT MAX(D1.effdt) FROM ps_scc_tm_CONFIG D1 WHERE D1.institution=D.institution AND D1.scc_tm_task_id=D.scc_tm_task_id AND D1.EFFDT <= %CurrentDateIn) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | SCC_TM_LIST_ID |
Character(31) | VARCHAR2(31) NOT NULL | Template ID |
| 2 | SCC_TM_ITEM_ID |
Character(31) | VARCHAR2(31) NOT NULL | Item ID |
| 3 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 4 | INSTITUTION |
Character(5) | VARCHAR2(5) NOT NULL | Academic Institution |
| 5 | SCC_TM_ACCEPT | Character(1) | VARCHAR2(1) NOT NULL | Acceptance Status |
| 6 | SCC_TM_ACP_DT | Date(10) | DATE | Acceptance Date |
| 7 | PORTAL_NAME | Character(30) | VARCHAR2(30) NOT NULL | Portal Name (see PSPRDMDEFN). |
| 8 | SCC_TM_LABEL | Character(100) | VARCHAR2(100) NOT NULL | Title |
| 9 | SCC_TM_LIST_STATUS | Character(2) | VARCHAR2(2) NOT NULL |
Status
CA=Cancelled CP=Completed IP=In Progress |
| 10 | SCC_TM_IS_TMPL | Character(1) | VARCHAR2(1) NOT NULL | Template |
| 11 | SCC_TM_PARENT_TMPL | Character(31) | VARCHAR2(31) NOT NULL | Parent Template ID |
| 12 | PTAI_FIELD1 | Character(100) | VARCHAR2(100) NOT NULL | PTAI_FIELD1 |
| 13 | SCC_TM_PAGE_CD | Character(15) | VARCHAR2(15) NOT NULL | Page Code |
| 14 | SCC_TM_PAGE_NAME | Character(60) | VARCHAR2(60) NOT NULL | Agreement |