PER COBRA Coverage

Description
Categories: BI Publisher
Columns: Org Id, Business Group Id ...
Application: Human Resources
Source: COBRA Coverage Report (XML)
Short Name: PERRPCCR_XML
DB package: PER_PERUSCCR_XMLP_PKG
select p.organization_id  org_id,
       p.business_group_id
from       per_organization_units p
where   (  p.organization_id = :p_parent_organization_id
         OR
          :p_parent_organization_id IS NULL )
and    p.business_Group_id = :p_business_Group_id
AND EXISTS
( SELECT 'x'
FROM   hr_lookups                                hrl,
       hr_lookups                                hrl2,
       hr_lookups                                hrl3,
       per_cobra_coverage_statuses               ccs_not,
       per_cobra_coverage_statuses               ccs,
       per_people_f                              pp1,
       per_contact_relationships                              pcs,
       per_people_f                              per,
       per_assignments_f                         asg,
       pay_element_types_f                       et,
       per_cobra_coverage_benefits_f             ccb,
       per_cobra_cov_enrollments                 cce
WHERE
       cce.qualifying_date
        BETWEEN NVL(:p_ql_date_from,TO_DATE('01-01-1901','DD-MM-YYYY')) AND
                NVL(:p_ql_date_to,  :C_END_OF_TIME) AND
       (   :p_qualifying_event IS NULL
        OR
          (:p_qualifying_event IS NOT NULL AND
           cce.qualifying_event     = :p_qualifying_event)
       ) AND
        NVL(cce.coverage_start_date, :p_session_date1)
        BETWEEN NVL(:p_cov_start_from1,TO_DATE('01-01-1901','DD-MM-YYYY')) AND
                NVL(:p_cov_start_to1,  :C_END_OF_TIME) AND
       NVL(cce.coverage_end_date, :p_session_date1)
        BETWEEN NVL(:p_cov_end_from1,TO_DATE('01-01-1901','DD-MM-YYYY')) AND
                NVL(:p_cov_end_to1, :C_END_OF_TIME)
AND
       ccb.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND
       :p_session_date1
	BETWEEN ccb.effective_start_date AND ccb.effective_end_date AND
       ccb.element_type_id          = NVL(:p_benefit_plan_type_id, ccb.element_type_id)    AND
       ccb.accept_reject_flag       = 'ACC'
AND
       et.element_type_id = ccb.element_type_id AND
       ET.PROCESSING_TYPE = 'R'                 AND
       :p_session_date1
        BETWEEN et.effective_start_date AND
                et.effective_end_date
AND
       asg.assignment_id             = cce.assignment_id AND
       asg.organization_id           = p.organization_id AND
       cce.qualifying_date
        BETWEEN asg.effective_start_date AND asg.effective_end_date
AND
       per.person_id                 = asg.person_id AND
       :p_session_date1
        BETWEEN per.effective_start_date AND per.effective_end_date
AND
       pcs.CONTACT_RELATIONSHIP_ID(+)= cce.CONTACT_RELATIONSHIP_ID
AND
       pp1.person_id            (+)  = pcs.contact_person_id AND
       pp1.effective_start_date (+) <= :p_session_date1     AND
       pp1.effective_end_date   (+) >= :p_session_date1
AND
       ccs.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND
       (   :p_cobra_status IS NULL
        OR
           ( :p_cobra_status IS NOT NULL AND
             ccs.cobra_coverage_status_type = :p_cobra_status
           )
       ) AND
       ccs.cobra_coverage_status_id =
             ( SELECT MAX(pc2.cobra_coverage_status_id)
               FROM  per_cobra_coverage_statuses pc2
               WHERE pc2.cobra_coverage_enrollment_id =
                     cce.cobra_coverage_enrollment_id AND
                     pc2.effective_date <= :p_session_date1
             )
AND
       ccs_not.cobra_coverage_enrollment_id(+) = cce.cobra_coverage_enrollment_id AND
       ccs_not.cobra_coverage_status_type  (+) = 'NOT'
AND
       hrl.lookup_code          (+)  = pcs.contact_type AND
       hrl.lookup_type          (+)  = 'CONTACT'
AND
       hrl2.lookup_code              = cce.qualifying_event AND
       hrl2.lookup_type              = 'US_COBRA_EVENT'
AND
       hrl3.lookup_code              = ccs.cobra_coverage_status_type AND
       hrl3.lookup_type              = 'US_COBRA_STATUS' )
UNION
select str.organization_id_child org_id,
       str.organization_id_child
from per_org_structure_elements str
where  str.business_group_id = :p_business_group_id
AND EXISTS
( SELECT 'x'
FROM   hr_lookups                                hrl,
       hr_lookups                                hrl2,
       hr_lookups                                hrl3,
       per_cobra_coverage_statuses               ccs_not,
       per_cobra_coverage_statuses               ccs,
       per_people_f                              pp1,
       per_contact_relationships                              pcs,
       per_people_f                              per,
       per_assignments_f                         asg,
       pay_element_types_f                       et,
       per_cobra_coverage_benefits_f             ccb,
       per_cobra_cov_enrollments                 cce
WHERE
       cce.qualifying_date
        BETWEEN NVL(:p_ql_date_from,TO_DATE('01-01-1901','DD-MM-YYYY')) AND
                NVL(:p_ql_date_to,  :C_END_OF_TIME) AND
       (   :p_qualifying_event IS NULL
        OR
          (:p_qualifying_event IS NOT NULL AND
           cce.qualifying_event     = :p_qualifying_event)
       ) AND
        NVL(cce.coverage_start_date, :p_session_date1)
        BETWEEN NVL(:p_cov_start_from1,TO_DATE('01-01-1901','DD-MM-YYYY')) AND
                NVL(:p_cov_start_to1,  :C_END_OF_TIME) AND
       NVL(cce.coverage_end_date, :p_session_date1)
        BETWEEN NVL(:p_cov_end_from1,TO_DATE('01-01-1901','DD-MM-YYYY')) AND
                NVL(:p_cov_end_to1, :C_END_OF_TIME)
AND
       ccb.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND
       :p_session_date1
	BETWEEN ccb.effective_start_date AND ccb.effective_end_date AND
       ccb.element_type_id          = NVL(:p_benefit_plan_type_id, ccb.element_type_id)    AND
       ccb.accept_reject_flag       = 'ACC'
AND
       et.element_type_id = ccb.element_type_id AND
       ET.PROCESSING_TYPE = 'R'                 AND
       :p_session_date1
        BETWEEN et.effective_start_date AND
                et.effective_end_date
AND
       asg.assignment_id             = cce.assignment_id AND
       asg.organization_id           = str.organization_id_child AND
       cce.qualifying_date
        BETWEEN asg.effective_start_date AND asg.effective_end_date
AND
       per.person_id                 = asg.person_id AND
       :p_session_date1
        BETWEEN per.effective_start_date AND per.effective_end_date
AND
       pcs.CONTACT_RELATIONSHIP_ID(+)= cce.CONTACT_RELATIONSHIP_ID
AND
       pp1.person_id            (+)  = pcs.contact_person_id AND
       pp1.effective_start_date (+) <= :p_session_date1     AND
       pp1.effective_end_date   (+) >= :p_session_date1
AND
       ccs.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND
       (   :p_cobra_status IS NULL
        OR
           ( :p_cobra_status IS NOT NULL AND
             ccs.cobra_coverage_status_type = :p_cobra_status
           )
       ) AND
       ccs.cobra_coverage_status_id =
             ( SELECT MAX(pc2.cobra_coverage_status_id)
               FROM  per_cobra_coverage_statuses pc2
               WHERE pc2.cobra_coverage_enrollment_id =
                     cce.cobra_coverage_enrollment_id AND
                     pc2.effective_date <= :p_session_date1
             )
AND
       ccs_not.cobra_coverage_enrollment_id(+) = cce.cobra_coverage_enrollment_id AND
       ccs_not.cobra_coverage_status_type  (+) = 'NOT'
AND
       hrl.lookup_code          (+)  = pcs.contact_type AND
       hrl.lookup_type          (+)  = 'CONTACT'
AND
       hrl2.lookup_code              = cce.qualifying_event AND
       hrl2.lookup_type              = 'US_COBRA_EVENT'
AND
       hrl3.lookup_code              = ccs.cobra_coverage_status_type AND
       hrl3.lookup_type              = 'US_COBRA_STATUS' )
connect by str.organization_id_parent = prior str.organization_id_child
and   str.org_structure_version_id    = :p_org_structure_version_id
start with str.organization_id_parent = :p_parent_organization_id
and   str.org_structure_version_id    = :p_org_structure_version_id
Parameter Name SQL text Validation
Organization Structure
 
LOV Oracle
Version
 
LOV Oracle
Parent Organization
 
LOV Oracle
Qualifying Event
 
LOV Oracle
Qualifying Date From
 
Date
Qualifying Date To
 
Date
COBRA Status
 
LOV Oracle
Benefit Plan
 
LOV Oracle
Coverage Start From
 
Date
Coverage Start To
 
Date
Coverage End From
 
Date
Coverage End To
 
Date
PER_ID
 
Number
PER_DATES_STANDARD
 
Date