PAY Fringe Benefits Copy link

select
    concatenated_segments,
     avalue ,   
         dt,
    full_name,
    employee_number,
    assignment_number,
    cost_centre,
    element_name
 from (
select  /*+leading (pec pet piv peev)*/ (nvl(pcak.concatenated_segments,0) ) concatenated_segments
    ,to_number (peev1.screen_entry_value) avalue,   
         to_char( to_date(peev.screen_entry_value,'yyyy/mm/dd hh24:mi:ss'),'dd-MON-yyyy') dt,
    per.full_name,
    per.employee_number,
    asg.assignment_number,
    pcak.concatenated_segments cost_centre,
    nvl(pet.reporting_name,pet.element_name) element_name,
    per.person_id,
    asg.period_of_service_id
from
    per_people_f per,
    per_assignments_f asg,
                pay_assignment_actions paa,
                pay_payroll_actions ppa,
    pay_cost_allocation_keyflex pcak,
    pay_cost_allocations_f pca,
    pay_element_Entry_values_f peev,
        Pay_Element_Entries_f pee, 
        Pay_Input_Values_f PIV, 
    Pay_Input_Values_f PIV1,
    pay_element_types_f pet,
    pay_element_classifications pec,
    pay_element_Entry_values_f peev1    
where
    per.person_id=asg.person_id and
    per.business_group_id=:P_BUSINESS_GROUP_ID
    and pcak.cost_allocation_keyflex_id(+)=pca.cost_allocation_keyflex_id and
    pca.assignment_id(+)=asg.assignment_id and
                asg.assignment_id=pee.assignment_id 
               and paa.assignment_id = asg.assignment_id
               and ppa.payroll_action_id = paa.payroll_action_id
               and ppa.action_type in ('R', 'Q')
               and ppa.date_earned between pee.effective_start_date and pee.effective_end_date and
                peev.element_entry_id = pee.element_entry_id and
    peev1.element_entry_id = pee.element_entry_id 
        and PIV.Name = 'Date' 
    and PIV.UOM = 'D' 
    and PIV1.Name = 'Taxable Value' 
            and PIV1.UOM = 'M'
    And PIV.Input_Value_ID = PEEV.INPUT_VALUE_id 
    And PIV1.Input_Value_ID = PEEV1.INPUT_VALUE_id  
               and (substr(peev.screen_entry_value,5,1) ='/' 
and to_date(to_char(peev.screen_Entry_value),'yyyy/mm/dd hh24:mi:ss')  Between :P_START_DATE  and :P_END_DATE
and to_date(to_char(peev.screen_Entry_value),'yyyy/mm/dd hh24:mi:ss') Between per.effective_start_date  and per.effective_end_date
and to_date(to_char(peev.screen_Entry_value),'yyyy/mm/dd hh24:mi:ss') Between asg.effective_start_date  and asg.effective_end_date 
and (to_date(to_char(peev.screen_Entry_value),'yyyy/mm/dd hh24:mi:ss') Between pca.effective_start_date  and pca.effective_end_date
    OR not exists (select null from pay_cost_allocations_f pca2
                    where pca.cost_allocation_id = pca2.cost_allocation_id ))
and :P_START_DATE Between pet.effective_start_date  and pet.effective_end_date )
    And  piv.element_type_id=pet.element_type_id and
                piv1.element_type_id=pet.element_type_id 
                and pec.classification_name ='Fringe Benefits' and
                pet.classification_id=pec.classification_id 
    and pet.business_group_id = :P_BUSINESS_GROUP_ID
    and (pcak.cost_allocation_keyflex_id=:P_COST_CENTRE or :P_COST_CENTRE is null)
)