HRS_DA_APP_LNG

(SQL View)
 Index Back

Diversity Analytics Overview


SELECT DA.HRS_PERSON_ID , DA.HRS_RCMNT_ID , DA.HRS_PROFILE_SEQ , DA.HRS_DIV_TYPE , STS.LANGUAGE_CD , CASE WHEN DA.HRS_DIV_TYPE = 'G' THEN COALESCE(( SELECT A.XLATLONGNAME FROM PSXLATITEMLANG A , PS_HRS_APPLICANT APL WHERE A.FIELDNAME = 'SEX' AND A.FIELDVALUE = APL.SEX AND A.LANGUAGE_CD = STS.LANGUAGE_CD AND APL.HRS_PERSON_ID = DA.HRS_PERSON_ID AND %EffdtCheck(PSXLATITEM,A,%CURRENTDATEIN)), ' ')ELSE CASE WHEN DA.HRS_DIV_TYPE = 'D' THEN COALESCE(( SELECT A.XLATLONGNAME FROM PSXLATITEMLANG A , PS_HRS_PG_DIV_DIS DIS WHERE DA.HRS_PERSON_ID = DIS.HRS_PERSON_ID AND A.FIELDNAME = 'DISABILITY_STS_USA' AND A.FIELDVALUE = DIS.DISABILITY_STS_USA AND A.LANGUAGE_CD = STS.LANGUAGE_CD AND %EffdtCheck(PSXLATITEM,A,%CURRENTDATEIN)), ( SELECT L.XLATLONGNAME FROM PSXLATITEMLANG L WHERE L.FIELDNAME = 'DISABILITY_STS_USA' AND L.FIELDVALUE = 'X' AND %EffdtCheck(PSXLATITEMLANG,L,%CURRENTDATEIN)))ELSE CASE WHEN DA.HRS_DIV_TYPE = 'V' THEN COALESCE(( SELECT A.XLATLONGNAME FROM PSXLATITEMLANG A , PS_HRS_APP_USA VET WHERE DA.HRS_PERSON_ID = VET.HRS_PERSON_ID AND A.FIELDNAME = 'MILITARY_STATUS' AND A.FIELDVALUE = VET.MILITARY_STATUS AND A.LANGUAGE_CD = STS.LANGUAGE_CD AND VET.EFFDT = ( SELECT MAX(VET1.EFFDT) FROM PS_HRS_APP_USA VET1 WHERE VET.HRS_PERSON_ID = VET1.HRS_PERSON_ID AND VET1.EFFDT <= %CurrentDateIn) AND %EffdtCheck(PSXLATITEM,A,%CURRENTDATEIN)), ( SELECT L.XLATLONGNAME FROM PSXLATITEMLANG L WHERE L.FIELDNAME = 'MILITARY_STATUS' AND L.FIELDVALUE = '1' AND %EffdtCheck(PSXLATITEMLANG,L,%CURRENTDATEIN))) ELSE CASE WHEN DA.HRS_DIV_TYPE = 'E' THEN COALESCE (( SELECT LNG.DESCR50 FROM PS_ETHNIC_GRP_LANG LNG , PS_HRS_DIV_PER_ETH ETH WHERE LNG.SETID = ETH.SETID AND LNG.ETHNIC_GRP_CD = ETH.HRS_ETHNIC_GRP_CD AND ETH.HRS_PERSON_ID = DA.HRS_PERSON_ID AND LNG.LANGUAGE_CD = STS.LANGUAGE_CD), ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 18177 AND MESSAGE_NBR = 3069 AND LANGUAGE_CD = STS.LANGUAGE_CD)) END END END END, ( SELECT STS2.DESCR FROM PS_HRS_STS_LNG STS2 WHERE STS2.STATUS_AREA = 2 AND STS2.STATUS_CODE = APPL.STATUS_CODE AND STS2.LANGUAGE_CD = STS.LANGUAGE_CD) , COALESCE(( SELECT JO.HRS_PRM_PST_TITLE FROM PS_HRS_JO_LNG JO WHERE JO.HRS_JOB_OPENING_ID = DA.HRS_JOB_OPENING_ID AND JO.LANGUAGE_CD = STS.LANGUAGE_CD), ( SELECT ML.MESSAGE_TEXT FROM PSMSGCATLANG ML WHERE ML.MESSAGE_SET_NBR = 18177 AND ML.MESSAGE_NBR = 3070 AND ML.LANGUAGE_CD = STS.LANGUAGE_CD)) , COALESCE(( SELECT JC.DESCR FROM PS_JOBCODE_LANG JC , PS_HRS_JOB_OPENING JO1 WHERE JO1.HRS_JOB_OPENING_ID = DA.HRS_JOB_OPENING_ID AND JC.SETID = JO1.SETID_DEPT AND JC.JOBCODE = JO1.HRS_PRM_JOBCODE AND JC.LANGUAGE_CD = STS.LANGUAGE_CD AND JC.EFFDT = ( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_LANG JC1 WHERE JC1.SETID = JC.SETID AND JC1.JOBCODE = JC.JOBCODE AND JC1.LANGUAGE_CD = JC.LANGUAGE_CD AND JC1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT POS.DESCR FROM PS_POSN_DATA_LANG POS WHERE DA.HRS_PRM_POSITION = POS.POSITION_NBR AND POS.EFFDT = ( SELECT MAX(POS1.EFFDT) FROM PS_POSN_DATA_LANG POS1 WHERE POS.POSITION_NBR = POS1.POSITION_NBR AND POS1.EFFDT <= %CurrentDateIn) AND POS.LANGUAGE_CD = STS.LANGUAGE_CD), ' ') , COALESCE(( SELECT JF.DESCR FROM PS_JOB_FAMILY_LNG JF WHERE DA.JOB_FAMILY = JF.JOB_FAMILY AND JF.LANGUAGE_CD = STS.LANGUAGE_CD AND JF.EFFDT = ( SELECT MAX(JF1.EFFDT) FROM PS_JOB_FAMILY_LNG JF1 WHERE JF1.JOB_FAMILY = JF.JOB_FAMILY AND JF.LANGUAGE_CD = JF1.LANGUAGE_CD AND JF1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT LOC.DESCR FROM PS_HRS_LOC_LNG LOC , PS_HRS_JOB_OPENING JO1 WHERE JO1.HRS_JOB_OPENING_ID = DA.HRS_JOB_OPENING_ID AND LOC.SETID = JO1.SETID_DEPT AND LOC.HRS_LOCATION_ID = JO1.HRS_PRM_LOC_AREA AND LOC.LANGUAGE_CD = STS.LANGUAGE_CD), ' ') , COALESCE(( SELECT DEPT.DESCR FROM PS_DEPT_TBL_LANG DEPT , PS_HRS_JOB_OPENING JO1 WHERE JO1.HRS_JOB_OPENING_ID = DA.HRS_JOB_OPENING_ID AND JO1.SETID_DEPT = DEPT.SETID AND JO1.DEPTID = DEPT.DEPTID AND DEPT.LANGUAGE_CD = STS.LANGUAGE_CD AND DEPT.EFFDT = ( SELECT MAX(DEPT1.EFFDT) FROM PS_DEPT_TBL_LANG DEPT1 WHERE DEPT.SETID = DEPT1.SETID AND DEPT.LANGUAGE_CD = DEPT1.LANGUAGE_CD AND DEPT.DEPTID = DEPT1.DEPTID AND DEPT1.EFFDT <= %CurrentDateIn)),' ') , COALESCE(( SELECT BU.DESCR FROM PS_BUS_UNIT_HR_LNG BU WHERE DA.BUSINESS_UNIT = BU.BUSINESS_UNIT AND BU.LANGUAGE_CD = STS.LANGUAGE_CD),' ') , COALESCE(( SELECT CMPNY.DESCR FROM PS_COMPNY_TBL_LANG CMPNY WHERE DA.COMPANY = CMPNY.COMPANY AND CMPNY.LANGUAGE_CD = STS.LANGUAGE_CD AND CMPNY.EFFDT = ( SELECT MAX(CMPNY1.EFFDT) FROM PS_COMPNY_TBL_LANG CMPNY1 WHERE CMPNY1.COMPANY = CMPNY.COMPANY AND CMPNY1.EFFDT <= (%CurrentDateIn))) ,' ') , COALESCE (( SELECT LNG.DESCR50 FROM PS_ETHNIC_GRP_LANG LNG , PS_HRS_DA_PER_ETH ETH WHERE LNG.SETID = ETH.SETID AND LNG.ETHNIC_GRP_CD = ETH.HRS_ETHNIC_GRP_CD AND ETH.HRS_PERSON_ID = DA.HRS_PERSON_ID AND LNG.LANGUAGE_CD = STS.LANGUAGE_CD) , ( SELECT MESSAGE_TEXT FROM PSMSGCATLANG WHERE MESSAGE_SET_NBR = 18177 AND MESSAGE_NBR = 3069 AND LANGUAGE_CD = STS.LANGUAGE_CD)) , STS.DESCR FROM PS_HRS_PG_DA_APPLN DA, PS_HRS_RCMNT RCMNT, PS_HRS_STS_LNG STS, PS_HRS_APPLICANT APPL WHERE DA.HRS_PERSON_ID = RCMNT.HRS_PERSON_ID AND DA.HRS_RCMNT_ID = RCMNT.HRS_RCMNT_ID AND DA.HRS_PROFILE_SEQ = RCMNT.HRS_PROFILE_SEQ AND RCMNT.STATUS_CODE = STS.STATUS_CODE AND STS.STATUS_AREA = 3 AND DA.HRS_PERSON_ID = APPL.HRS_PERSON_ID

  • Related Language Record for HRS_PG_DA_APPLN

    • # 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_RCMNT_ID Number(15,0) DECIMAL(15) NOT NULL App Tracking Recruitment ID
    3 HRS_PROFILE_SEQ Number(3,0) SMALLINT NOT NULL Profile sequece
    4 HRS_DIV_TYPE Character(1) VARCHAR2(1) NOT NULL Field used to list diversity Types.
    D=Disability
    E=Ethnicity
    G=Gender
    V=Veterans
    5 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    6 HRS_DESCR90 Character(90) VARCHAR2(90) NOT NULL content item description
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    8 HRS_PRM_PST_TITLE Character(200) VARCHAR2(200) NOT NULL Job posting title
    9 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Code Description
    10 POSN_DESCR Character(30) VARCHAR2(30) NOT NULL Position Description
    11 JOB_DESCR Character(30) VARCHAR2(30) NOT NULL Job Description
    12 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
    13 DEPT_DESCR Character(30) VARCHAR2(30) NOT NULL Department Description
    14 BUSINESS_DESCR Character(60) VARCHAR2(60) NOT NULL Business Description
    15 COMPANY_DESCR Character(30) VARCHAR2(30) NOT NULL Company Descr
    16 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    17 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr