[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** 08-NOV-2000 rayyadev 115.2 updated the sql for payment.
** 11-NOV-2000 rayyadev 115.3 added legislation code
** 07-JUL-2001 apunekar 115.4 added function to calculate invalidity balance.
** 03-OCT-2001 apunekar 115.5 Made Changes for Bug2021219
** 10-OCT-2001 ragovind 115.6 Added parameter p_invalidity_component to the** ETP_Prepayment_information function
** 05-DEC-2001 nnaresh 115.9 Updated for GSCC Standards.
** 04-DEC-2002 Ragovind 115.10 Added NOCOPY for the functions etp_payment_information,etp_prepayment_information
** 15-May-2003 Ragovind 115.11 Bug#2819479 - ETP Pre/Post Enhancement.
** 23-JUL-2003 Nanuradh 115.12 Bug#2984390 - Added an extra parameter to the function call etp_prepost_ratios - ETP Pre/post Enhancement
** 22-Apr-2005 ksingla 115.13 Bug#4177679 -Added an extra parameter to the function call etp_prepost_ratios .
** 25-Apr-2005 abhargav 115.14 Bug#4322599 - For ETP Tax modified the package hr_aubal call,
** now calling the package in action mode rather then date mode
** 21-Nov-2007 tbakashi 115.15 Bug#6470561 - STATUTORY UPDATE: MUTIPLE ETP IMPACT ON TERMINATION REPORT
** ============== Formula Fuctions ====================
** Package contains Reporting Details for the Termination
** report in AU localisatons.
** 07-Sep-2009 pmatamsr 115.16 Bug#8769345 - Added a new function get_etp_pre_post_components
** as part of Statutory changes to ETP Super rollover
** 07-Sep-2009 pmatamsr 115.17 Bug#8769345 - Added code in ETP_prepayment_information and ETP_payment_information functions for
** fetching the taxable and tax free Super Rollover amounts.
** 28-Jan-2009 pmatamsr 115.18 Bug#9322314 - Added logic to support reporting of values in termination report
** for the terminated employees processed before applying the patch 8769345.
** 20-Jul-2011 skshin 115.21 Bug#12583457 - Added p_etp_pretax_SIL parameter to ETP_payment_information function
** ETP_gross is deducted by ETP Pre Tax on Slary in Lieu amount
** 05-Sep-2012 skshin 115.22 Bug#14358180 - Modifed ETP_payment_information function get_etp_pre_post_components function to return new element results for Excluded and Non Excluded
*/
--
-------------------------------------------------------------------------------------------------
--
-- FUNCTION ETP_prepayment_information
--
-- Returns :
-- 1 if function runs successfully
-- 0 otherwise
--
-- Purpose : Return the Values of ETP Prepayment information
--
-- In : p_assignment_id - assignment which is terminated for
-- which report is requiered
-- p_Hire_date - date Of commencement of the assignment
-- p_Termination_date - date Of Termination Date
--
-- Out : p_pre_01Jul1983_days - no Of Days in the Pre Jul 1983
-- p_post_30jun1983_days - no Of Days in the Post Jul 1983
-- p_pre_01jul1983_ratio -ratio Of Days in the Pre Jul 1983
-- p_post_30jun1983_ratio -ratio Of Days in the Post Jul 1983
-- P_Gross_ETP -gross ETP With out super annuation
-- P_Maximum_Rollover -Maximum rollover amount
-- p_Lump_sum_d -Lump sum D Tax free amount
--
-- Uses :
-- pay_au_terminations
-- hr_utility
--
------------------------------------------------------------------------------------------------
function ETP_prepayment_information
(p_assignment_id in number
,P_hire_date in Date
,p_Termination_date in date
,P_Assignment_action_id in Number
,p_pre_01Jul1983_days out NOCOPY number
,p_post_30jun1983_days out NOCOPY number
,p_pre_01jul1983_ratio out NOCOPY number
,p_post_30jun1983_ratio out NOCOPY number
,P_Gross_ETP out NOCOPY number
,P_Maximum_Rollover out NOCOPY number
,p_Lump_sum_d out NOCOPY number
,p_invalidity_component out NOCOPY number
,p_etp_service_date out NOCOPY date /* Bug#2984390 */
,p_taxable_max_rollover out NOCOPY number /* Start 8769345 */
,p_tax_free_max_rollover out NOCOPY number /* End 8769345 */
)
return number
is
l_procedure constant varchar2(100) := 'ETP_prepayment_information';
select
nvl(To_Number( prrv.result_value),0)
from
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
pay_run_results prr,
pay_assignment_Actions paa
where
prrv.input_value_id=piv.input_value_id
and piv.element_type_id=pet.element_type_id
and prr.element_type_id = pet.element_type_id
and prr.run_result_id = prrv.run_result_id
and prr.assignment_action_id = paa.assignment_Action_id
and paa.assignment_id = P_assignment_id
and pet.element_name=Lv_Element_Name
and piv.name = Lv_Input_Name
and piv.legislation_code = 'AU'
and pet.legislation_code = piv.legislation_code
and P_Termination_Date between piv.effective_start_date and piv.effective_end_date
and P_Termination_Date between pet.effective_start_date and Pet.effective_end_date; /* 6470561 */
select
sum(To_Number( prrv.result_value))
from
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
pay_run_results prr,
pay_assignment_Actions paa
where
prrv.input_value_id=piv.input_value_id
and piv.element_type_id=pet.element_type_id
and prr.element_type_id = pet.element_type_id
and prr.run_result_id = prrv.run_result_id
and prr.assignment_action_id = paa.assignment_Action_id
and paa.assignment_id = p_assignment_id
and pet.element_name= Lv_Element_Name
and piv.name = Lv_Input_Name
and piv.legislation_code ='AU'
and piv.legislation_code = pet.legislation_code
and p_termination_date between piv.effective_start_date and piv.effective_end_date
and p_termination_date between pet.effective_start_date and Pet.effective_end_date
and prr.run_result_id in (
select unique(prr3.run_result_id)
from pay_run_results prr2,
pay_input_values_f piv2,
pay_element_entries_f pee2,
pay_run_result_values prrv2,
pay_assignment_actions paa2,
pay_run_results prr3
where
prr2.element_type_id = pee2.element_type_id and
piv2.element_type_id = pee2.element_type_id and
prr2.run_result_id = prrv2.run_result_id and
prrv2.input_value_id = piv2.input_value_id and
paa2.assignment_action_id = prr2.assignment_action_id and
piv2.name = 'Transitional ETP' and
prrv2.result_value = 'Y' and
paa2.assignment_id = p_assignment_id and
prr2.source_id = prr3.source_id ) ;
select
nvl(sum(To_Number( prrv.result_value)),0) /* 6470561 */
from
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
pay_run_results prr,
pay_assignment_Actions paa
where
prrv.input_value_id=piv.input_value_id
and piv.element_type_id=pet.element_type_id
and prr.element_type_id = pet.element_type_id
and prr.run_result_id = prrv.run_result_id
and prr.assignment_action_id = paa.assignment_Action_id
and paa.assignment_id = P_assignment_id
and pet.element_name=Lv_Element_Name
and piv.name = Lv_Input_Name
and piv.legislation_code ='AU'
and piv.legislation_code = pet.legislation_code
and p_termination_date between piv.effective_start_date and piv.effective_end_date
and p_termination_date between pet.effective_start_date and Pet.effective_end_date;
select
sum(To_Number( prrv.result_value))
from
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
pay_run_results prr,
pay_assignment_Actions paa
where
prrv.input_value_id=piv.input_value_id
and piv.element_type_id=pet.element_type_id
and prr.element_type_id = pet.element_type_id
and prr.run_result_id = prrv.run_result_id
and prr.assignment_action_id = paa.assignment_Action_id
and paa.assignment_id = p_assignment_id
and pet.element_name= Lv_Element_Name
and piv.name = Lv_Input_Name
and piv.legislation_code ='AU'
and piv.legislation_code = pet.legislation_code
and p_termination_date between piv.effective_start_date and piv.effective_end_date
and p_termination_date between pet.effective_start_date and Pet.effective_end_date
and prr.run_result_id in (
select unique(prr3.run_result_id)
from pay_run_results prr2,
pay_input_values_f piv2,
pay_element_entries_f pee2,
pay_run_result_values prrv2,
pay_assignment_actions paa2,
pay_run_results prr3
where
prr2.element_type_id = pee2.element_type_id and
piv2.element_type_id = pee2.element_type_id and
prr2.run_result_id = prrv2.run_result_id and
prrv2.input_value_id = piv2.input_value_id and
paa2.assignment_action_id = prr2.assignment_action_id and
piv2.name = 'Transitional ETP' and
prrv2.result_value = p_transitional and
paa2.assignment_id = p_assignment_id and
prr2.source_id = prr3.source_id ) ;
select
nvl(To_Number( prrv.result_value),0) result_value, nvl(pee.entry_information4,'N') entry_information
from
pay_run_result_values prrv,
pay_input_values_f piv,
pay_element_types_f pet,
pay_run_results prr,
pay_assignment_actions paa,
pay_element_entries_f pee,
pay_element_types_f pet2
where
prrv.input_value_id=piv.input_value_id
and piv.element_type_id=pet.element_type_id
and prr.element_type_id = pet.element_type_id
and prr.run_result_id = prrv.run_result_id
and prr.assignment_action_id = paa.assignment_Action_id
and paa.assignment_id = p_assignment_id
and pet.element_name=Lv_Element_Name
and piv.name = Lv_Input_Name
and piv.legislation_code ='AU'
and piv.legislation_code = pet.legislation_code
and p_termination_date between piv.effective_start_date and piv.effective_end_date
and p_termination_date between pet.effective_start_date and Pet.effective_end_date
and prr.source_id = pee.element_entry_id
and pee.element_type_id = pet2.element_type_id
and pet2.element_name = 'ETP on Termination'
--and p_termination_date between pee.effective_start_date and pee.effective_end_date
and p_termination_date between pet2.effective_start_date and pet2.effective_end_date;
cursor get_date_earned is select date_earned
from pay_payroll_actions ppa
,pay_assignment_actions paa
where paa.assignment_action_id=p_assignment_action_id
and paa.payroll_action_id=ppa.payroll_action_id;
select Balance_Type_id Into Lv_Balance_Type_id
from Pay_Balance_Types
Where Balance_Name = 'Lump Sum C Deductions'
and Legislation_code = 'AU';
cursor get_date_earned is select date_earned
from pay_payroll_actions ppa
,pay_assignment_actions paa
where paa.assignment_action_id=p_assignment_action_id
and paa.payroll_action_id=ppa.payroll_action_id;
select Balance_Type_id Into Lv_Balance_Type_id_1
from Pay_Balance_Types
Where Balance_Name = 'Invalidity Payments Transitional Not Part of Prev Term'
and Legislation_code = 'AU';
select Balance_Type_id Into Lv_Balance_Type_id_2
from Pay_Balance_Types
Where Balance_Name = 'Invalidity Payments Transitional Part of Prev Term'
and Legislation_code = 'AU';
select date_earned
from pay_payroll_actions ppa
,pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id;
select Balance_Type_id Into Lv_Balance_Type_id_1
from Pay_Balance_Types
Where Balance_Name = 'ETP Tax Free Payments Excluded'
and Legislation_code = 'AU';
select Balance_Type_id Into Lv_Balance_Type_id_2
from Pay_Balance_Types
Where Balance_Name = 'ETP Taxable Payments Excluded'
and Legislation_code = 'AU';
select Balance_Type_id Into Lv_Balance_Type_id_3
from Pay_Balance_Types
Where Balance_Name = 'ETP Payments Excluded'
and Legislation_code = 'AU';