HRS_WCDEC_SRCH(SQL View) |
Index Back |
---|---|
Works Council - Search RecordUsed as search record for the Works Council Decision Page |
SELECT A.HRS_PERSON_ID ,A.HRS_PROFILE_SEQ ,D.NAME ,D.LAST_NAME_SRCH ,D.NAME_AC ,B.APP_PER_STATUS ,B.STATUS_CODE , POS.HRS_JOB_OPENING_ID , B.EMPLID , SPOKESPERSON_ID FROM PS_HRS_APP_PROF_I A , PS_HRS_APPLICANT_I B , PS_HRS_RCMNT POS , PS_HRS_JO_I JOB4 , PS_WC_CNCLSPK_TBL C , PS_HRS_APP_NAME_I D WHERE A.HRS_PERSON_ID = B.HRS_PERSON_ID AND POS.HRS_PERSON_ID = A.HRS_PERSON_ID AND A.HRS_PERSON_ID = D.HRS_PERSON_ID AND POS.HRS_PROFILE_SEQ = A.HRS_PROFILE_SEQ AND JOB4.HRS_JOB_OPENING_ID = POS.HRS_JOB_OPENING_ID AND (POS.STATUS_CODE = '073' OR ( EXISTS ( SELECT 'X' FROM PS_HRS_REC_STATDEC S WHERE S.STATUS_AREA = '3' AND S.EFFDT = ( SELECT MAX(S1.EFFDT) FROM PS_HRS_REC_STATDEC S1 WHERE S.STATUS_AREA = S1.STATUS_AREA AND S1.EFFDT <= %DatePart(A.HRS_SUBMITTED_DTTM)) AND S.WC_DECISIONGRP_ID = 'KDH' AND POS.STATUS_CODE = S.STATUS_CODE AND POS.STATUS_REASON = S.STATUS_REASON) ) ) AND C.SETID = JOB4.SETID_DEPT AND C.WC_COUNCIL_ID = JOB4.WC_COUNCIL_ID |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | HRS_PERSON_ID | Number(15,0) | DECIMAL(15) NOT NULL | Person Id |
2 | HRS_PROFILE_SEQ | Number(3,0) | SMALLINT NOT NULL | Profile sequece |
3 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
4 | LAST_NAME_SRCH | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
5 | NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Alternate Character Name |
6 | APP_PER_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Identifies the type of applicant - External Applicant, Employee or Non-Employee
A=External Applicant E=Employee N=Non-Employee |
7 | STATUS_CODE | Character(3) | VARCHAR2(3) NOT NULL |
Identifies a status.
Prompt Table: HRS_STS_APP_I |
8 | HRS_JOB_OPENING_ID | Number(15,0) | DECIMAL(15) NOT NULL | Job Opening Clone |
9 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: HRS_PERSON_NM_I |
10 | SPOKESPERSON_ID | Character(11) | VARCHAR2(11) NOT NULL | Works Council Setup Table - Spokesperson |