VIEW: APPS.HR_PAY_INTERFACE_CONTACTS_V

Source

View Text - Preformatted

select cr.business_group_id
     , cr.person_id
     , cr.contact_person_id
     , cr.contact_type
     , hr.meaning
     , cr.primary_contact_flag
     , cr.dependent_flag
     , pp.last_name
     , pp.first_name
     , pp.middle_names
     , pp.full_name
     , pp.suffix
     , pp.title
     , pp.employee_number
     , pp.date_of_birth
     , cr.cont_attribute1
     , cr.cont_attribute2
     , cr.cont_attribute3
     , cr.cont_attribute4
     , cr.cont_attribute5
     , cr.cont_attribute6
     , cr.cont_attribute7
     , cr.cont_attribute8
     , cr.cont_attribute9
     , cr.cont_attribute10
     , cr.cont_attribute11
     , cr.cont_attribute12
     , cr.cont_attribute13
     , cr.cont_attribute14
     , cr.cont_attribute15
     , cr.cont_attribute16
     , cr.cont_attribute17
     , cr.cont_attribute18
     , cr.cont_attribute19
     , cr.cont_attribute20
     , GREATEST(NVL(pp.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,pp.effective_start_date)
     , GREATEST(NVL(cr.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,NVL(cr.date_start, to_date('1900/01/01','YYYY/MM/DD')))
     , GREATEST(
       GREATEST(NVL(pp.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,pp.effective_start_date)
     , GREATEST(NVL(cr.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,NVL(cr.date_start, to_date('1900/01/01','YYYY/MM/DD')))
       )
from hr_lookups hr
   , per_all_people_f pp
   , per_contact_relationships cr
where pp.person_id = cr.contact_person_id
and   hr.lookup_type = 'CONTACT'
and   hr.lookup_code = cr.contact_type
and   HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
        pp.effective_start_date and pp.effective_end_date
and   HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
        NVL(cr.date_start,to_date('01/01/1900','DD/MM/YYYY'))
      and
        NVL(cr.date_end,to_date('31/12/4712','DD/MM/YYYY'))

View Text - HTML Formatted

SELECT CR.BUSINESS_GROUP_ID
, CR.PERSON_ID
, CR.CONTACT_PERSON_ID
, CR.CONTACT_TYPE
, HR.MEANING
, CR.PRIMARY_CONTACT_FLAG
, CR.DEPENDENT_FLAG
, PP.LAST_NAME
, PP.FIRST_NAME
, PP.MIDDLE_NAMES
, PP.FULL_NAME
, PP.SUFFIX
, PP.TITLE
, PP.EMPLOYEE_NUMBER
, PP.DATE_OF_BIRTH
, CR.CONT_ATTRIBUTE1
, CR.CONT_ATTRIBUTE2
, CR.CONT_ATTRIBUTE3
, CR.CONT_ATTRIBUTE4
, CR.CONT_ATTRIBUTE5
, CR.CONT_ATTRIBUTE6
, CR.CONT_ATTRIBUTE7
, CR.CONT_ATTRIBUTE8
, CR.CONT_ATTRIBUTE9
, CR.CONT_ATTRIBUTE10
, CR.CONT_ATTRIBUTE11
, CR.CONT_ATTRIBUTE12
, CR.CONT_ATTRIBUTE13
, CR.CONT_ATTRIBUTE14
, CR.CONT_ATTRIBUTE15
, CR.CONT_ATTRIBUTE16
, CR.CONT_ATTRIBUTE17
, CR.CONT_ATTRIBUTE18
, CR.CONT_ATTRIBUTE19
, CR.CONT_ATTRIBUTE20
, GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(CR.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(CR.DATE_START
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST( GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(CR.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(CR.DATE_START
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) ) FROM HR_LOOKUPS HR
, PER_ALL_PEOPLE_F PP
, PER_CONTACT_RELATIONSHIPS CR WHERE PP.PERSON_ID = CR.CONTACT_PERSON_ID AND HR.LOOKUP_TYPE = 'CONTACT' AND HR.LOOKUP_CODE = CR.CONTACT_TYPE AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(CR.DATE_START
, TO_DATE('01/01/1900'
, 'DD/MM/YYYY')) AND NVL(CR.DATE_END
, TO_DATE('31/12/4712'
, 'DD/MM/YYYY'))