HRS_WCDEC_SRCH

(SQL View)
Index Back

Works Council - Search Record

Used 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