TI_HIRE_PHONE

(SQL View)
 Index Back

Applicant Phone - For Hire

Applicant Data - Phone Numbers - Used in Hire Integration

SELECT HRS_PERSON_ID ,TI_REQUISITION_NBR , 'HOME' , home_phone ,'Y' FROM PS_TI_PERS_INFO WHERE home_phone IS NOT NULL AND home_phone<>' ' UNION SELECT HRS_PERSON_ID , TI_REQUISITION_NBR , 'CELL' , mobile_phone ,CASE WHEN (home_phone IS NOT NULL AND home_phone<>' ' ) THEN 'N' ELSE 'Y' END FROM PS_TI_PERS_INFO WHERE mobile_phone IS NOT NULL AND mobile_phone<>' ' UNION SELECT HRS_PERSON_ID ,TI_REQUISITION_NBR , 'WORK' , work_phone ,CASE WHEN (home_phone IS NOT NULL AND home_phone<>' ' AND mobile_phone IS NOT NULL AND mobile_phone<>' ' ) THEN 'N' ELSE 'Y' END FROM PS_TI_PERS_INFO WHERE work_phone IS NOT NULL AND work_phone <>' ' UNION SELECT HRS_PERSON_ID , TI_REQUISITION_NBR , 'PGR1' , TI_PAGER_NUMBER ,CASE WHEN (home_phone IS NOT NULL AND home_phone<>' ' AND mobile_phone IS NOT NULL AND mobile_phone<>' ' AND work_phone IS NOT NULL AND work_phone <>' ' ) THEN 'N' ELSE 'Y' END FROM PS_TI_PERS_INFO WHERE TI_PAGER_NUMBER IS NOT NULL AND TI_PAGER_NUMBER <>' '

  • Parent record: HRS_APPLICANT

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 HRS_PERSON_ID Number(15,0) DECIMAL(15) NOT NULL Person Id
    2 TI_REQUISITION_NBR Character(20) VARCHAR2(20) NOT NULL Taleo Requisition Number
    3 PHONE_TYPE Character(4) VARCHAR2(4) NOT NULL Phone Type
    BUSN=Business
    CAMP=Campus
    CELL=Mobile
    DORM=Dormitory
    FAX=FAX
    HOME=Home
    MAIN=Main
    OTR=Other
    PGR1=Pager 1
    PGR2=Pager 2
    TELX=Telex
    WORK=Work
    4 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
    5 PREF_PHONE_FLAG Character(1) VARCHAR2(1) NOT NULL Preferred
    N=No
    Y=Yes