eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HR_PAY_INTERFACE_EMPLOYEES_V

Source

View Text - Preformatted


SELECT
  pp.employee_number
, bgr.name
, pp.last_name
, pp.first_name
, pp.middle_names
, pp.full_name
, pp.suffix
, pp.title
, pp.pre_name_adjunct
, pp.date_of_birth
, pp.marital_status
, ppt.system_person_type
, ppt.user_person_type
, pp.sex
, pp.nationality
, pp.national_identifier
, pp.correspondence_language
, pos.leaving_reason
, pp.registered_disabled_flag
, pp.date_of_death
, pp.email_address
, pp.expense_check_send_to_address
, pa.telephone_number_1
, pph.phone_number
, pph2.phone_number
, pa.address_line1
, pa.address_line2
, pa.address_line3
, pa.town_or_city
, pa.country
, pa.region_1
, pa.region_2
, pa.region_3
, pa.postal_code
, decode(pos2.date_start, pos.date_start, 'N','Y')
, pos2.date_start
, pos.date_start
, pos.actual_termination_date
, pos.final_process_date
, pp.person_id
, bgr.organization_id
, pp.per_information1
, pp.per_information2
, pp.per_information3
, pp.per_information4
, pp.per_information5
, pp.per_information6
, pp.per_information7
, pp.per_information8
, pp.per_information9
, pp.per_information10
, pp.per_information11
, pp.per_information12
, pp.per_information13
, pp.per_information14
, pp.per_information15
, pp.per_information16
, pp.per_information17
, pp.per_information18
, pp.per_information19
, pp.per_information20
, pp.per_information21
, pp.per_information22
, pp.per_information23
, pp.per_information24
, pp.per_information25
, pp.per_information26
, pp.per_information27
, pp.per_information28
, pp.per_information29
, pp.per_information30
, pp.attribute1
, pp.attribute2
, pp.attribute3
, pp.attribute4
, pp.attribute5
, pp.attribute6
, pp.attribute7
, pp.attribute8
, pp.attribute9
, pp.attribute10
, pp.attribute11
, pp.attribute12
, pp.attribute13
, pp.attribute14
, pp.attribute15
, pp.attribute16
, pp.attribute17
, pp.attribute18
, pp.attribute19
, pp.attribute20
, pa.addr_attribute1
, pa.addr_attribute2
, pa.addr_attribute3
, pa.addr_attribute4
, pa.addr_attribute5
, pa.addr_attribute6
, pa.addr_attribute7
, pa.addr_attribute8
, pa.addr_attribute9
, pa.addr_attribute10
, pa.addr_attribute11
, pa.addr_attribute12
, pa.addr_attribute13
, pa.addr_attribute14
, pa.addr_attribute15
, pa.addr_attribute16
, pa.addr_attribute17
, pa.addr_attribute18
, pa.addr_attribute19
, pa.addr_attribute20
, pos.attribute1
, pos.attribute2
, pos.attribute3
, pos.attribute4
, pos.attribute5
, pos.attribute6
, pos.attribute7
, pos.attribute8
, pos.attribute9
, pos.attribute10
, pos.attribute11
, pos.attribute12
, pos.attribute13
, pos.attribute14
, pos.attribute15
, pos.attribute16
, pos.attribute17
, pos.attribute18
, pos.attribute19
, pos.attribute20
, pph.attribute1
, pph.attribute2
, pph.attribute3
, pph.attribute4
, pph.attribute5
, pph.attribute6
, pph.attribute7
, pph.attribute8
, pph.attribute9
, pph.attribute10
, pph.attribute11
, pph.attribute12
, pph.attribute13
, pph.attribute14
, pph.attribute15
, pph.attribute16
, pph.attribute17
, pph.attribute18
, pph.attribute19
, pph.attribute20
, pph.attribute21
, pph.attribute22
, pph.attribute23
, pph.attribute24
, pph.attribute25
, pph.attribute26
, pph.attribute27
, pph.attribute28
, pph.attribute29
, pph.attribute30
, greatest(
  GREATEST(NVL(pp.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,pp.effective_start_date)
, GREATEST(NVL(pa.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,pa.date_from)
, NVL(pos.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
, GREATEST(NVL(pph.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,NVL(pph.date_from,TO_DATE('1900/01/01','YYYY/MM/DD')))
, GREATEST(NVL(pph2.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,NVL(pph2.date_from,TO_DATE('1900/01/01','YYYY/MM/DD')))
, NVL(ppt.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
  )
, GREATEST(NVL(pp.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,pp.effective_start_date)
, GREATEST(NVL(pa.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,NVL(pa.date_from,TO_DATE('1900/01/01','YYYY/MM/DD')))
, NVL(pos.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
, GREATEST(NVL(pph.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,NVL(pph.date_from,TO_DATE('1900/01/01','YYYY/MM/DD')))
, GREATEST(NVL(pph2.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,NVL(pph2.date_from,TO_DATE('1900/01/01','YYYY/MM/DD')))
, NVL(ppt.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
FROM
    per_all_people_f pp
  , hr_all_organization_units bgr
  , per_person_types ppt
  , per_addresses pa
  , per_periods_of_service pos2
  , per_periods_of_service pos
  , per_phones pph
  , per_phones pph2
WHERE
-- Join to the organization table to get the business group name
  pp.business_group_id = bgr.organization_id
and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
    pp.effective_start_date and pp.effective_end_date
and pp.person_type_id = ppt.person_type_id
and pp.person_id = pa.person_id (+)
and pa.primary_flag (+) = 'Y'
and pp.person_id = pph.parent_id (+)
and pph.parent_table (+) = 'PER_ALL_PEOPLE_F'
and pph.phone_type (+) = 'W1'
and pp.person_id = pph2.parent_id (+)
and pph2.parent_table (+) = 'PER_ALL_PEOPLE_F'
and pph2.phone_type (+) = 'M'
and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
    pa.date_from(+)
    and NVL(pa.date_to(+),to_date('12-12-4712','DD-MM-YYYY'))
and pp.person_id = pos.person_id
and pos.date_start = (SELECT max(pos3.date_start)
		      FROM per_periods_of_service pos3
                      WHERE pos3.person_id
                          = pos.person_id
                       AND pos3.date_start <=
                          HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE)
and pp.person_id = pos2.person_id
and pos2.date_start = (SELECT min(pos3.date_start)
		      FROM per_periods_of_service pos3
                      WHERE pos3.person_id
                          = pos2.person_id)
and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
    NVL(pph2.date_from(+), HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE)
    and NVL(pph2.date_to(+), to_date('31-12-4712', 'DD-MM-YYYY'))
and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
    NVL(pph.date_from(+), HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE)
    and NVL(pph.date_to(+), to_date('31-12-4712', 'DD-MM-YYYY'))

View Text - HTML Formatted

SELECT PP.EMPLOYEE_NUMBER
, BGR.NAME
, PP.LAST_NAME
, PP.FIRST_NAME
, PP.MIDDLE_NAMES
, PP.FULL_NAME
, PP.SUFFIX
, PP.TITLE
, PP.PRE_NAME_ADJUNCT
, PP.DATE_OF_BIRTH
, PP.MARITAL_STATUS
, PPT.SYSTEM_PERSON_TYPE
, PPT.USER_PERSON_TYPE
, PP.SEX
, PP.NATIONALITY
, PP.NATIONAL_IDENTIFIER
, PP.CORRESPONDENCE_LANGUAGE
, POS.LEAVING_REASON
, PP.REGISTERED_DISABLED_FLAG
, PP.DATE_OF_DEATH
, PP.EMAIL_ADDRESS
, PP.EXPENSE_CHECK_SEND_TO_ADDRESS
, PA.TELEPHONE_NUMBER_1
, PPH.PHONE_NUMBER
, PPH2.PHONE_NUMBER
, PA.ADDRESS_LINE1
, PA.ADDRESS_LINE2
, PA.ADDRESS_LINE3
, PA.TOWN_OR_CITY
, PA.COUNTRY
, PA.REGION_1
, PA.REGION_2
, PA.REGION_3
, PA.POSTAL_CODE
, DECODE(POS2.DATE_START
, POS.DATE_START
, 'N'
, 'Y')
, POS2.DATE_START
, POS.DATE_START
, POS.ACTUAL_TERMINATION_DATE
, POS.FINAL_PROCESS_DATE
, PP.PERSON_ID
, BGR.ORGANIZATION_ID
, PP.PER_INFORMATION1
, PP.PER_INFORMATION2
, PP.PER_INFORMATION3
, PP.PER_INFORMATION4
, PP.PER_INFORMATION5
, PP.PER_INFORMATION6
, PP.PER_INFORMATION7
, PP.PER_INFORMATION8
, PP.PER_INFORMATION9
, PP.PER_INFORMATION10
, PP.PER_INFORMATION11
, PP.PER_INFORMATION12
, PP.PER_INFORMATION13
, PP.PER_INFORMATION14
, PP.PER_INFORMATION15
, PP.PER_INFORMATION16
, PP.PER_INFORMATION17
, PP.PER_INFORMATION18
, PP.PER_INFORMATION19
, PP.PER_INFORMATION20
, PP.PER_INFORMATION21
, PP.PER_INFORMATION22
, PP.PER_INFORMATION23
, PP.PER_INFORMATION24
, PP.PER_INFORMATION25
, PP.PER_INFORMATION26
, PP.PER_INFORMATION27
, PP.PER_INFORMATION28
, PP.PER_INFORMATION29
, PP.PER_INFORMATION30
, PP.ATTRIBUTE1
, PP.ATTRIBUTE2
, PP.ATTRIBUTE3
, PP.ATTRIBUTE4
, PP.ATTRIBUTE5
, PP.ATTRIBUTE6
, PP.ATTRIBUTE7
, PP.ATTRIBUTE8
, PP.ATTRIBUTE9
, PP.ATTRIBUTE10
, PP.ATTRIBUTE11
, PP.ATTRIBUTE12
, PP.ATTRIBUTE13
, PP.ATTRIBUTE14
, PP.ATTRIBUTE15
, PP.ATTRIBUTE16
, PP.ATTRIBUTE17
, PP.ATTRIBUTE18
, PP.ATTRIBUTE19
, PP.ATTRIBUTE20
, PA.ADDR_ATTRIBUTE1
, PA.ADDR_ATTRIBUTE2
, PA.ADDR_ATTRIBUTE3
, PA.ADDR_ATTRIBUTE4
, PA.ADDR_ATTRIBUTE5
, PA.ADDR_ATTRIBUTE6
, PA.ADDR_ATTRIBUTE7
, PA.ADDR_ATTRIBUTE8
, PA.ADDR_ATTRIBUTE9
, PA.ADDR_ATTRIBUTE10
, PA.ADDR_ATTRIBUTE11
, PA.ADDR_ATTRIBUTE12
, PA.ADDR_ATTRIBUTE13
, PA.ADDR_ATTRIBUTE14
, PA.ADDR_ATTRIBUTE15
, PA.ADDR_ATTRIBUTE16
, PA.ADDR_ATTRIBUTE17
, PA.ADDR_ATTRIBUTE18
, PA.ADDR_ATTRIBUTE19
, PA.ADDR_ATTRIBUTE20
, POS.ATTRIBUTE1
, POS.ATTRIBUTE2
, POS.ATTRIBUTE3
, POS.ATTRIBUTE4
, POS.ATTRIBUTE5
, POS.ATTRIBUTE6
, POS.ATTRIBUTE7
, POS.ATTRIBUTE8
, POS.ATTRIBUTE9
, POS.ATTRIBUTE10
, POS.ATTRIBUTE11
, POS.ATTRIBUTE12
, POS.ATTRIBUTE13
, POS.ATTRIBUTE14
, POS.ATTRIBUTE15
, POS.ATTRIBUTE16
, POS.ATTRIBUTE17
, POS.ATTRIBUTE18
, POS.ATTRIBUTE19
, POS.ATTRIBUTE20
, PPH.ATTRIBUTE1
, PPH.ATTRIBUTE2
, PPH.ATTRIBUTE3
, PPH.ATTRIBUTE4
, PPH.ATTRIBUTE5
, PPH.ATTRIBUTE6
, PPH.ATTRIBUTE7
, PPH.ATTRIBUTE8
, PPH.ATTRIBUTE9
, PPH.ATTRIBUTE10
, PPH.ATTRIBUTE11
, PPH.ATTRIBUTE12
, PPH.ATTRIBUTE13
, PPH.ATTRIBUTE14
, PPH.ATTRIBUTE15
, PPH.ATTRIBUTE16
, PPH.ATTRIBUTE17
, PPH.ATTRIBUTE18
, PPH.ATTRIBUTE19
, PPH.ATTRIBUTE20
, PPH.ATTRIBUTE21
, PPH.ATTRIBUTE22
, PPH.ATTRIBUTE23
, PPH.ATTRIBUTE24
, PPH.ATTRIBUTE25
, PPH.ATTRIBUTE26
, PPH.ATTRIBUTE27
, PPH.ATTRIBUTE28
, PPH.ATTRIBUTE29
, PPH.ATTRIBUTE30
, GREATEST( GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(PA.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PA.DATE_FROM)
, NVL(POS.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, GREATEST(NVL(PPH.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPH.DATE_FROM
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL(PPH2.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPH2.DATE_FROM
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(PPT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) )
, GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(PA.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PA.DATE_FROM
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(POS.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, GREATEST(NVL(PPH.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPH.DATE_FROM
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL(PPH2.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPH2.DATE_FROM
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(PPT.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')) FROM PER_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS BGR
, PER_PERSON_TYPES PPT
, PER_ADDRESSES PA
, PER_PERIODS_OF_SERVICE POS2
, PER_PERIODS_OF_SERVICE POS
, PER_PHONES PPH
, PER_PHONES PPH2 WHERE -- JOIN TO THE ORGANIZATION TABLE TO GET THE BUSINESS GROUP NAME PP.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE AND PP.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID AND PP.PERSON_ID = PA.PERSON_ID (+) AND PA.PRIMARY_FLAG (+) = 'Y' AND PP.PERSON_ID = PPH.PARENT_ID (+) AND PPH.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F' AND PPH.PHONE_TYPE (+) = 'W1' AND PP.PERSON_ID = PPH2.PARENT_ID (+) AND PPH2.PARENT_TABLE (+) = 'PER_ALL_PEOPLE_F' AND PPH2.PHONE_TYPE (+) = 'M' AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PA.DATE_FROM(+)
AND NVL(PA.DATE_TO(+)
, TO_DATE('12-12-4712'
, 'DD-MM-YYYY')) AND PP.PERSON_ID = POS.PERSON_ID AND POS.DATE_START = (SELECT MAX(POS3.DATE_START)
FROM PER_PERIODS_OF_SERVICE POS3
WHERE POS3.PERSON_ID = POS.PERSON_ID
AND POS3.DATE_START <= HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE) AND PP.PERSON_ID = POS2.PERSON_ID AND POS2.DATE_START = (SELECT MIN(POS3.DATE_START)
FROM PER_PERIODS_OF_SERVICE POS3
WHERE POS3.PERSON_ID = POS2.PERSON_ID) AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(PPH2.DATE_FROM(+)
, HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE)
AND NVL(PPH2.DATE_TO(+)
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(PPH.DATE_FROM(+)
, HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE)
AND NVL(PPH.DATE_TO(+)
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))