Employees with Dependent details

Bilalemail

Summary

OTBI Report for Employees with Dependent details

Content

Is there standard or custom report for employees with their dependent details as we required for Insurance. 

Sowdhaminya Murugan

There is enrollment dashboards delivered by Oracle Please check in Shared Folders/Human Capital Management/Benefits folder.

Koteswararao Yenikepalli

Hello ,

I have recently created a report for a similar requirement,

I hope my report will work for you.

 

WITH payroll_data

AS (

SELECT p.person_number pay_person_number

,p.person_id pay_person_id

,py.payroll_name

,py.payroll_id

FROM PAY_ASSIGNED_PAYROLLS_DN ap

,pay_PAYROLL_TERMS pt

,PAY_PAY_RELATIONSHIPS_DN pr

,PAY_ALL_PAYROLLS_F py

,PER_ALL_PEOPLE_F p

WHERE ap.payroll_term_id = pt.payroll_term_id

AND pt.payroll_relationship_id = pr.payroll_relationship_id

AND ap.payroll_id = py.payroll_id

AND pr.person_id = p.person_id

-- AND py.payroll_name = NVL(:P_PAYROLL_NAME, py.payroll_name)

)

SELECT F.PERSON_NUMBER

,(

SELECT pp.full_name

FROM per_person_names_f pp

WHERE pp.person_id = pcr.contact_person_id

AND pp.name_type = 'GLOBAL'

AND rownum = 1

) FULL_NAME

,(

SELECT MAX(fl.meaning)

FROM fnd_lookup_values_tl fl

WHERE fl.lookup_type = 'NATIONALITY'

AND fl.lookup_code(+) = pcz.legislation_code

AND fl.LANGUAGE = USERENV('LANG')

) nationality

,(

SELECT MEANING

FROM hr_lookups hrlms

WHERE hrlms.lookup_code = ppl.MARITAL_STATUS

AND hrlms.lookup_type = 'MAR_STATUS'

AND ROWNUM = 1

) marital_status

,houf.name dept

,payroll_data.payroll_name

,TO_CHAR(pni.national_identifier_number) national_identifier_number

,(

SELECT MAX(ft.nls_territory)

FROM fnd_territories_b ft

WHERE ft.territory_code = pni.legislation_code

) national_identifier_country

,PAP.PERSON_NUMBER CONTACT_PERSON_NUMBER

,(

SELECT meaning

FROM hr_lookups hl

WHERE hl.lookup_type = 'CONTACT'

AND hl.lookup_code = pcr.contact_type

AND rownum = 1

) contact_type

,

-- pcr.EFFECTIVE_START_DATE contact_Date,

NULL GRADE_ID

,decode(BENEFICIARY_FLAG, 'N', 'No', 'Yes') BENEFICIARY_FLAG

,decode(DEPENDENT_FLAG, 'N', 'No', 'Yes') DEPENDENT_FLAG

,decode(PERSONAL_FLAG, 'N', 'No', 'Yes') PERSONAL_FLAG

,NULL Family_count

,(

SELECT CASE 

WHEN TRUNC(MONTHS_BETWEEN(SYSDATE, DATE_OF_BIRTH) / 12) <> 0

THEN TRUNC(MONTHS_BETWEEN(SYSDATE, DATE_OF_BIRTH) / 12)

ELSE to_char(trunc(MONTHS_BETWEEN(SYSDATE, DATE_OF_BIRTH))) / 100

END

FROM PER_PERSONS P

WHERE P.person_id = pap.PERSON_ID

AND ROWNUM = 1

) AGE

,(

SELECT DATE_OF_BIRTH

FROM PER_PERSONS P

WHERE P.person_id = pap.PERSON_ID

AND ROWNUM = 1

) CONTACT_dATE

,NULL email

,pcr.effective_end_date end_Date

,NULL mobile

FROM PER_ALL_PEOPLE_F F

,per_person_names_f PPN

,PER_ALL_ASSIGNMENTS_F S

,per_citizenships pcz

,per_people_legislative_f ppl

,HR_ORG_UNIT_CLASSIFICATIONS_F hou

,HR_ORGANIZATION_UNITS_F_TL houf

,payroll_data

,per_national_identifiers pni

,per_contact_relationships pcr

,per_all_people_f pap

WHERE 1 = 1

-- AND F.person_number = '18653'

-- AND f.person_id = '300000002008373'

AND PPN.name_type = 'GLOBAL'

AND SYSDATE BETWEEN PPN.EFFECTIVE_START_DATE

AND PPN.EFFECTIVE_END_DATE

AND F.PERSON_ID = S.PERSON_ID

AND PPN.PERSON_ID = S.PERSON_ID

AND SYSDATE BETWEEN S.EFFECTIVE_START_DATE

AND S.EFFECTIVE_END_DATE

AND pcz.person_id(+) = F.person_id

AND ASSIGNMENT_TYPE = 'E'

AND ppl.person_id = ppn.person_id

AND hou.organization_id = s.organization_id

AND houf.organization_id = s.organization_id

AND houf.LANGUAGE = 'US'

AND hou.CLASSIFICATION_CODE = 'DEPARTMENT'

AND payroll_data.pay_person_number = F.PERSON_NUMBER

AND payroll_data.pay_person_id = F.person_id

AND pni.person_id(+) = f.person_id

AND pni.national_identifier_id(+) = f.primary_nid_id

AND f.person_id = pcr.person_id

AND pcr.contact_person_id = pap.person_id

AND nvl(:P_effective, sysdate) BETWEEN pap.effective_start_date

AND pap.effective_end_date

-- AND Sysdate BETWEEN pap.effective_start_date

-- AND pap.effective_end_date

 

UNION ALL

 

SELECT DISTINCT F.PERSON_NUMBER

,PPN.FULL_NAME

,(

SELECT MAX(fl.meaning)

FROM fnd_lookup_values_tl fl

WHERE fl.lookup_type = 'NATIONALITY'

AND fl.lookup_code(+) = pcz.legislation_code

AND fl.LANGUAGE = USERENV('LANG')

) nationality

,(

SELECT MEANING

FROM hr_lookups hrlms

WHERE hrlms.lookup_code = ppl.MARITAL_STATUS

AND hrlms.lookup_type = 'MAR_STATUS'

AND ROWNUM = 1

) marital_status

,houf.name dept

,payroll_data.payroll_name

,TO_CHAR(pni.national_identifier_number) national_identifier_number

,(

SELECT MAX(ft.nls_territory)

FROM fnd_territories_b ft

WHERE ft.territory_code = pni.legislation_code

) national_identifier_country

,NULL CONTACT_PERSON_NUMBER

,'Employee' contact_type

,

-- null contact_Date,

(

SELECT NAME

FROM per_grades GRD

WHERE GRD.GRADE_ID = s.GRADE_ID

) GRADE_ID

,NULL BENEFICIARY_FLAG

,NULL DEPENDENT_FLAG

,NULL PERSONAL_FLAG

,(

SELECT COUNT(pcr.contact_person_id)

FROM per_contact_relationships P

WHERE P.person_id = F.person_id

-- AND NVL(:effective, SYSDATE) BETWEEN NVL(pcr.date_start, NVL(:effective, SYSDATE))

-- AND NVL(pcr.date_end, NVL(:effective, SYSDATE)

) Family_count

,(

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, DATE_OF_BIRTH) / 12)

FROM PER_PERSONS P

WHERE P.person_id = F.PERSON_ID

AND ROWNUM = 1

) AGE

,(

SELECT DATE_OF_BIRTH

FROM PER_PERSONS P

WHERE P.person_id = F.PERSON_ID

AND ROWNUM = 1

) CONTACT_dATE

,(

SELECT EMAIL_ADDRESS

FROM PER_EMAIL_ADDRESSES pe

WHERE pe.person_id = f.person_id

AND MASTERED_IN_LDAP_FLAG = 'Y'

AND EMAIL_TYPE = 'W1'

) email

,NULL end_date

,(

SELECT PHONE_NUMBER

FROM PER_PHONES

WHERE person_id = f.person_id

AND PHONE_TYPE = 'WM'

) MOBILE

FROM PER_ALL_PEOPLE_F F

,per_person_names_f PPN

,PER_ALL_ASSIGNMENTS_F S

,per_citizenships pcz

,per_people_legislative_f ppl

,HR_ORG_UNIT_CLASSIFICATIONS_F hou

,HR_ORGANIZATION_UNITS_F_TL houf

,payroll_data

,per_national_identifiers pni

,per_contact_relationships pcr

,per_all_people_f pap

WHERE 1 = 1

-- AND F.person_number = '18653'

-- AND PPN.person_id = '300000002008373'

AND PPN.name_type = 'GLOBAL'

AND SYSDATE BETWEEN PPN.EFFECTIVE_START_DATE

AND PPN.EFFECTIVE_END_DATE

AND F.PERSON_ID = S.PERSON_ID

AND PPN.PERSON_ID = S.PERSON_ID

AND SYSDATE BETWEEN S.EFFECTIVE_START_DATE

AND S.EFFECTIVE_END_DATE

AND pcz.person_id(+) = F.person_id

AND ASSIGNMENT_TYPE = 'E'

AND ppl.person_id = ppn.person_id

AND hou.organization_id = s.organization_id

AND houf.organization_id = s.organization_id

AND houf.LANGUAGE = 'US'

AND hou.CLASSIFICATION_CODE = 'DEPARTMENT'

AND payroll_data.pay_person_number = F.PERSON_NUMBER

AND payroll_data.pay_person_id = F.person_id

AND pni.person_id(+) = f.person_id

AND pni.national_identifier_id(+) = f.primary_nid_id

AND f.person_id = pcr.person_id

AND pcr.contact_person_id = pap.person_id

AND nvl(:P_effective, sysdate) BETWEEN pap.effective_start_date

AND pap.effective_end_date

-- AND Sysdate BETWEEN pap.effective_start_date

-- AND pap.effective_end_date

-- ORDER BY F.PERSON_NUMBER

ORDER BY PERSON_NUMBER

,FULL_NAME DESC

 

Thank You.