HR_CNT_TASK_VW(SQL View) |
Index Back |
---|---|
Contract Task ViewThis is a view used to fetch Contract View information for Expiration Inquiry Page. |
SELECT DISTINCT D.EMPLID , A.EMPL_RCD , D.CONTRACT_NUM , C.CNT_TASK_ORDER_NUM , F.PER_ORG , B.DESCR50 , E.NAME , E.LAST_NAME , E.SECOND_LAST_NAME , E.NAME_AC , D.CONTRACT_END_DT , D.CNT_TEMPLATE_ID , B.END_DT FROM PS_JOB_EXPIR_NT_VW A , PS_CNT_TASK_ORDER B , PS_HR_EE_TASK_ORD C , PS_CONTRACT_DATA D , PS_PERSONAL_DATA E , PS_JOB F WHERE A.EMPLID = C.EMPLID AND C.EMPLID=D.EMPLID AND D.EMPLID=E.EMPLID AND F.EMPLID = A.EMPLID AND A.EMPL_RCD = F.EMPL_RCD AND D.CONTRACT_NUM = C.CONTRACT_NUM AND A.CONTRACT_NUM = C.CONTRACT_NUM AND D.CNT_TEMPLATE_ID = B.CNT_TEMPLATE_ID AND C.CNT_TASK_ORDER_NUM = B.CNT_TASK_ORDER_NUM AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_CNT_TASK_ORDER B1 WHERE B1.SETID = B.SETID AND B1.CNT_TEMPLATE_ID = B.CNT_TEMPLATE_ID AND B1.EFFDT <= %CurrentDateIn AND B1.CNT_TASK_ORDER_NUM = B.CNT_TASK_ORDER_NUM) AND D.CONTRACT_STATUS = 'A' |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON |
2 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record |
3 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract Number |
4 | CNT_TASK_ORDER_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract Task Order Number |
5 | PER_ORG | Character(3) | VARCHAR2(3) NOT NULL |
Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker EMP=Employee POI=Person of Interest Default Value: PER_ORG_ASGN.PER_ORG |
6 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
7 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
8 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
9 | SECOND_LAST_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Second Last Name |
10 | NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Alternate Character Name |
11 | CONTRACT_END_DT | Date(10) | DATE | Contract End Date |
12 | CNT_TEMPLATE_ID | Character(11) | VARCHAR2(11) NOT NULL |
Contract Template ID
Prompt Table: CNT_TEMPL_VW |
13 | END_DT | Date(10) | DATE | end date |