BEN Benefits ACA Persons Report for 1095C Printed Forms

Description
Categories: BI Publisher, Human Resources
Application: Advanced Benefits
Source: Benefits ACA Persons Report for 1095C Printed Forms
Short Name: BEN1095PRINTPRSNS
DB package: BEN_1095PRINTPERSONS_XMLP_PKG
SELECT  ppf.first_name
       ,ppf.last_name
       ,ppf.employee_number
       ,hr_person_type_usage_info.get_user_person_type(trunc(:P_EFFECTIVE_DATE),person_id) user_person_type
FROM    per_all_people_f ppf
WHERE   ppf.person_id IN
        (
        SELECT  person_id
        FROM    ben_rep_archive rep
        WHERE   rep_action_id = :P_REP_ACTION_ID
        AND     ben_attribute_category = '1SERIES'
        AND     NOT EXISTS
                    (
                    SELECT  1
                    FROM    ben_per_cm_f pcm
                    WHERE   rep.person_id = pcm.person_id
                    AND     pcm.cm_typ_id = :P_CM_TYP_ID
                    AND     rep.business_group_id = pcm.business_group_id
                    AND     trunc(:P_EFFECTIVE_DATE) BETWEEN pcm.effective_start_date
                                                                          AND     pcm.effective_end_date
                    )
        )
AND     trunc(:P_EFFECTIVE_DATE) BETWEEN ppf.effective_start_date
                                                      AND     ppf.effective_end_date order by ppf.person_id
Parameter Name SQL text Validation
Business Group ID
 
Number
Effective Date
 
Date
GRE
 
LOV Oracle
Reporting Year
 
LOV Oracle