Fusion Analytics Warehouse

Products Banner

Employees with Dependent details

Received Response
37
Views
2
Comments

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

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

  • 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.