Oracle Fusion Data Intelligence

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion Data Intelligence› Oracle Fusion Data Intelligence

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Employees with Dependent details

Received Response
198
Views
2
Comments
Bilalemail
Bilalemail Rank 2 - Community Beginner
edited Aug 13, 2024 7:52PM in Oracle Fusion Data Intelligence

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. 

Tagged:

Answers

  • Sowdhaminya Murugan
    Sowdhaminya Murugan Rank 1 - Community Starter

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

  • Koteswararao Yenikepalli
    Koteswararao Yenikepalli Rank 3 - Community Apprentice

    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.