[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** 28-NOV-2001 nnaresh 115.12 Updated for GSCC Standards
** 04-Dec-2002 Ragovind 115.13 Added NOCOPY to thr functions check_rollover, etp_prepost_ratios
** ,get_long_service_leave, term_lsl_eligibility_years
** 07-May-2003 Ragovind 115.15 Bug#2819479 - ETP Pre/Post Enhancement.
** 18-May-2003 Ragovind 115.16 Added Reference Bug# for Bug#2819479
** 23-Jul-2003 Nanuradh 115.17 Bug#2984390 - Added an extra parameter p_etp_service_date to the function etp_prepost_ratios
** ETP Pre/post Enhancement
** 26-Jul-2003 Nanuradh 115.18 Bug#2984390 - Modified the function etp_prepost_ratios.
** Pre/post ratios assgined to zero when pre/post 1983 days are zero.
** 23-Dec-2003 punmehta 115.19 Bug#3306112 - Modified the cursor to improve performance and added conditional debug.
** 10-May-2003 Ragovind 115.20 Bug#3263690 - NGE calculation Enhancement.
** 25-Jun-2004 srrajago 115.21 Bug#3603495 - Modified the cursor 'recurring_entries' in the function 'processed' - Performance fix.
** 09-AUG-2004 abhkumar 115.22 Bug#2610141 - Legal Employer enhancement changes.
** 12-AUG-2004 abhkumar 115.23 Bug#2610141 - Modfied cursor c_get_prev_year_max_asg_act_id to consider for action_type='V'
** 08-SEP-2004 abhkumar 115.25 Bug#2610141 - Added a parameter to function calculate_term_asg_nge to support the
** versioning of the tax formula.
** 19-Apr-2004 ksingla 115.26 Bug#4177679 - Added an extra parameter p_le_etp_service_date to the function etp_prepost_ratios.
** 03-AUG-2005 abhkumar 115.27 Bug#4538463 - Modified the function calculate_term_asg_nge to have a logic similar as
** calculate_asg_prev_value
** 01-SEP-2005 abhkumar 115.28 Bug#4474896 - Average Earnings Calculation enhancement
** 02-Apr-2006 abhargav 115.29 Bug#5107059 - Added new function get_total_accrual_hours().
** 17-Apr-2006 abhargav 115.30 Bug#5107059 - Modified to remove gscc error.
** 27-Jun-2006 hnainani 115.31 Bug# 5056831 - Added Function Override_eligibility
** 11-Jul-2006 priupadh 115.32 Bug# 5377591 - Changed cursor c_check_payroll_run to Function CALCULATE_TERM_ASG_NGE
** 17-Jul-2006 avenkatk 115.33 Bug# 5388657 - Modified Dates passed to c_check_payroll_run in Function CALCULATE_TERM_ASG_NGE
**
** 21-Sep-2006 hnainani 115.24 Bug# 5056831 - Removed extra param to Override_eligibility based
** on review comments
** 09-May-2007 priupadh 115.36 Bug# 5956223 Added new function calculate_etp_tax,get_trans_prev_etp
** 16-May-2007 priupadh 115.37 Bug# 5956223 Removed function get_trans_prev_etp,added function get_fin_year_end
** 31-May-2007 priupadh 115.38 Bug# 6071863 Added function get_prev_age Modified cursor csr_get_etp_tax
** 31-May-2007 priupadh 115.39 Bug# 6071863 Corrected version numbers in change history .
** 04-Jun-2007 priupadh 115.40 Bug# 6071863 Modified query csr_get_prev_age in function get_prev_age .
** 23-Aug-2007 priupadh 115.41 Bug# 6192381 Added function au_check_trans
** 17-Sep-2007 avenkatk 115.42 Bug# 6430072 Function calculate_etp_tax - Added condition to convert User Tables value using
** fnd_number.canonical_to_number
** 26-May-2009 dduvvuri 115.43 Bug# 8482224 Cursor csr_get_accrual_plan_id modified in Function get_accrual_plan_by_category
** 20-Jul-2009 skshin 115.45 Bug# 8647962 Added Index(PURF) and ORDERED hint to get correct execution path
** 30-Jul-2009 skshin 115.46 Bug# 8725341 Added Earnings_Leave_Loading balance to be retrieved effective from 01-JUL-2009 in calculate_term_asg_nge function
** 07-Sep-2009 pmatamsr 115.47 Bug# 8769345 Added new input parameter to check_rollover function.
** 12-May-2010 skshin 115.48 Bug# 9507714 Modified to pass l_start_date to get absence for post AUG1993 and post AUG1978 in get_long_service_leave function due to primary assignment change
** 06-Oct-2010 skshin 115.49 Bug# 9775171 Added calculate_etp_lumpsum_d_2010 and calculate_etp_tax_2010 functions
** 16-Nov-2010 avenkatk 115.50 Bug# 9950136 Added changes for foreign workers accruals - added function get_foreign_accrual
** 24-Nov-2010 skshin 115.54 Bug# 10212532 Added function get_days_ppl_suspended to calculate suspended days of PPL for ETP calculation
** 09-Dec-2010 avenkatk 115.55 Bug# 10384820 Modified function get_days_ppl_suspended for retropay case
** 05-MAY-2011 dduvvuri 115.56 Bug# 12400122 Modified the cursor csr_get_etp_entry for performance improvement.
** 25-MAY-2011 jmarupil 115.57 Bug# 12586038 Modified function get_days_ppl_suspended to calculate PPL suspended days for recurring element also
** 25-MAY-2011 skshin 115.59 Bug# 12583457 Added function au_count_etps_sil to count ETP on Termination with Salary in Lieu of Notice input value entered
** 05-Sep-2012 skshin 115.60 Bug# 14358180 Added new functions of calculate_etp_lumpsum_d_2012 and calculate_etp_tax_2012
** 14-Sep-2012 skshin 115.61 Bug# 14469003 Added STAR_TRANSFORMATION hint to csr_get_days_ppl cursor for better performance
** 04-Oct-2012 skshin 115.63 Bug# 14703008 Modified to use ETP Payments Lump Sum D balance
** 16-Oct-2012 ruihuang 115.64 Bug# 14675129 Modified function get_long_service_leave to support more decimal places for Accrual hours in the calculation of Long Service Leave on Termination
** 16-Nov-2012 ruihuang 115.65 Bug# 14675129 Modified function get_long_service_leave to support 3 decimal places for Accrual hours in the calculation of Long Service Leave on Termination
** 19-Nov-2012 skshin 115.66 Bug# 15846744 Modified calculate_etp_lumpsum_d_2012 function to use new record type of g_lump_sum_d for assignment_set usage
** 11-Jan-2013 skshin 115.67 Bug# 15852244 Modifed calculate_etp_tax_2012 function to reduce ETP CAP with Non Excluded amount at under cap rate
** 21-Feb-2013 skshin 115.68 Bug# 16319860 Modifed calculate_marginal_tax to ignore the cents from lv_average_term_pay_period
*/
g_debug constant boolean := hr_utility.debug_enabled;
select nvl(eev.screen_entry_value, 'N')
from pay_element_entry_values_f eev
where eev.input_value_id = p_input_Value_id
and eev.element_entry_id = p_element_entry_id
and p_effective_date between eev.effective_Start_Date and eev.effective_end_date;
select 'Y'
from pay_run_results
where source_id = p_element_entry_id
and status <> 'U';
select 'Y'
from pay_run_results RESULT
where result.source_id = l_source_id --Bug 3306112
and result.status <> 'U'
and exists ( select 1
from pay_assignment_actions ASGT_ACTION
, pay_payroll_actions PAY_ACTION
, per_time_periods PERIOD
where result.assignment_action_id = asgt_action.assignment_action_id
and asgt_action.payroll_action_id = pay_action.payroll_action_id
and pay_action.payroll_id = period.payroll_id
and pay_action.date_earned between period.start_date and period.end_date
and p_effective_date between period.start_date and period.end_date
);
select pap.accrual_plan_id
from pay_accrual_plans pap,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
where pee.assignment_id = p_assignment_id
and p_effective_date between pee.effective_start_date and pee.effective_end_date
and p_effective_date between pel.effective_start_date and pel.effective_end_date /*Added for 8482224*/
and p_effective_date between pet.effective_start_date and pet.effective_end_date /*Added for 8482224*/
and pel.element_link_id = pee.element_link_id
and pel.element_type_id = pet.element_type_id
and pap.accrual_plan_element_type_id = pet.element_type_id
and pap.accrual_category = p_plan_category
AND NVL(pap.information3,'N') = 'N'; /* Bug 9950136 */
select peev.screen_entry_value,
peev1.screen_entry_value,
to_date(peev2.screen_entry_value,'YYYY/MM/DD HH24:MI:SS')
from pay_element_types_f pet,
pay_element_entries_f peef,
pay_element_links_f pel,
pay_input_values_f piv,
pay_input_values_f piv1,
pay_input_values_f piv2,
pay_element_entry_values_f peev,
pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2
where peef.assignment_id = c_assignment_id
and pet.element_name = 'ETP on Termination'
and pet.legislation_code = 'AU'
and pet.element_type_id = pel.element_type_id
and pel.element_link_id = peef.element_link_id
and peef.element_entry_id = peev.element_entry_id
and peef.element_entry_id = peev1.element_entry_id
and peef.element_entry_id = peev2.element_entry_id
and pet.element_type_id = piv.element_type_id
and pet.element_type_id = piv1.element_type_id
and pet.element_type_id = piv2.element_type_id
and peev.input_value_id = piv.input_value_id
and peev1.input_value_id = piv1.input_value_id
and peev2.input_value_id = piv2.input_value_id
and piv.name = 'Pre 1983 Days'
and piv1.name = 'Post 1983 Days'
and piv2.name = 'ETP Service Date';
select to_number(hrl.description)
from pay_element_types_f petf
, pay_input_values_f pivf
, pay_accrual_plans pap
, hr_lookups hrl
, pay_element_classifications pec
where pap.accrual_plan_id = p_accrual_plan_id
and pivf.input_value_id = pap.pto_input_value_id
and petf.element_type_id = pivf.element_type_id
and petf.classification_id = pec.classification_id
and pec.classification_name = 'Long Service Leave'
and hrl.lookup_type (+) = 'AU_TERM_LSL_ELIGIBILITY_YEARS'
and hrl.lookup_code (+) = petf.element_information1
and hrl.enabled_flag (+) = 'Y'
and p_effective_date between petf.effective_start_date and petf.effective_end_date
and p_effective_date between pivf.effective_start_date and pivf.effective_end_date;
SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id = c_business_group_id
AND paa.action_sequence in
(
SELECT MAX(paa.action_sequence)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paaf
WHERE ppa.business_group_id = c_business_group_id
AND paaf.assignment_id = c_assignment_id
AND paa.assignment_id = paaf.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('Q','R','B','I','V') --2610141
AND ppa.effective_date between c_fin_start_date AND c_fin_end_date
AND paa.action_status = 'C'
AND paa.tax_unit_id = p_tax_unit_id --2610141
)
ORDER BY date_earned desc;
SELECT paa.assignment_action_id, paa.tax_unit_id, ppa.payroll_id, ppa.effective_date
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id = c_business_group_id
AND paa.action_sequence in
(
SELECT MAX(paa.action_sequence)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paaf
WHERE ppa.business_group_id = c_business_group_id
AND paaf.assignment_id = c_assignment_id
AND paa.assignment_id = paaf.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('Q','R','B','I','V')
AND paa.action_status = 'C'
AND ppa.effective_date between c_fin_start_date AND c_fin_end_date
)
ORDER BY date_earned desc;
select count(DISTINCT ptp.time_period_id)
from per_time_periods ptp
where exists (select 'EXISTS' from
per_assignments_f paf,
hr_soft_coding_keyflex hsck
where paf.assignment_id = p_assignment_id
and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
and hsck.segment1 = c_tax_unit_id
AND paf.effective_start_date <= c_end_date
AND paf.effective_end_date >= c_start_date
AND paf.effective_start_date <= ptp.end_date
AND paf.effective_end_date >= ptp.start_date)
AND ptp.payroll_id = c_payroll_id
AND ptp.start_date <= c_end_date
AND ptp.end_date >= c_start_date;
SELECT ppa.effective_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id;
SELECT count(paa.assignment_action_id)
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
WHERE ppa.effective_date BETWEEN c_start_date AND c_end_date
AND ppa.business_group_id = c_business_group_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = c_assignment_id
AND paa.assignment_id = paf.assignment_id
AND ppa.effective_date between paf.effective_start_date and paf.effective_end_date
AND paa.action_status = 'C'
AND paa.source_action_id IS NULL /*Bug 4418107 - This join added to only pick master assignment action id*/
AND ppa.action_type IN ('Q','R','I','B'); /*Bug 4474896 - Introduced action_types 'B' and 'I'*/
SELECT pap.accrual_plan_id,pap.accrual_units_of_measure
FROM pay_accrual_plans pap,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
WHERE pee.assignment_id = p_assignment_id
AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND pel.element_link_id = pee.element_link_id
AND pel.element_type_id = pet.element_type_id
AND pap.accrual_plan_element_type_id = pet.element_type_id
AND pap.accrual_category = p_plan_category ;
SELECT /*+ INDEX(PURF) ORDERED */ NVL(SUM(round((least(fnd_number.canonical_to_number(purf.row_high_range),p_amount)-fnd_number.canonical_to_number(purf.row_low_range_or_name))
*(fnd_number.canonical_to_number(pucif.value) + decode(fnd_number.canonical_to_number(pucif.value),0,0,p_med_levy)))),0) -- bug8647962
FROM pay_user_tables put,
pay_user_rows_F purf,
pay_user_columns puc,
pay_user_column_instances_f pucif
where put.legislation_code='AU'
and put.user_table_name=p_user_table
and put.user_table_id=purf.user_table_id
and put.user_table_id=puc.user_table_id
and puc.user_column_id=pucif.user_column_id
and purf.user_row_id=pucif.user_row_id
and p_date_paid between purf.effective_start_date and purf.effective_end_date
and p_date_paid between pucif.effective_start_date and pucif.effective_end_date
and p_amount > fnd_number.canonical_to_number(purf.row_low_range_or_name) /* 6430072 */
order by fnd_number.canonical_to_number(purf.row_low_range_or_name);
SELECT /*+ index(PURF) ORDERED */ to_number(pucif.value) -- bug8647962
FROM pay_user_tables put,
pay_user_rows_F purf,
pay_user_columns puc,
pay_user_column_instances_f pucif
where put.legislation_code='AU'
and put.user_table_name='ETP_PRESERVATION_AGE'
and put.user_table_id=purf.user_table_id
and put.user_table_id=puc.user_table_id
and puc.user_column_id=pucif.user_column_id
and purf.user_row_id=pucif.user_row_id
and p_date_paid between purf.effective_start_date and purf.effective_end_date
and p_date_paid between pucif.effective_start_date and pucif.effective_end_date
and to_number(to_char(p_date_of_birth,'YYYYMMDD')) >= to_number(purf.row_low_range_or_name)
and to_number(to_char(p_date_of_birth,'YYYYMMDD')) <= to_number(purf.row_high_range);
select 1
from pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
where pee.assignment_id = p_assignment_id
and pet.element_type_id = piv.element_type_id
and pet.element_name = 'ETP on Termination'
and piv.name = 'Transitional ETP'
and pet.element_type_id = pee.element_type_id
and pee.element_entry_id = peev.element_entry_id
and piv.input_value_id = peev.input_value_id
and peev.screen_entry_value = 'Y'
and p_date_earned between pee.effective_start_date and pee.effective_end_date
and p_date_earned between peev.effective_start_date and peev.effective_end_date
and p_date_earned between pet.effective_start_date and pet.effective_end_date
and p_date_earned between peev.effective_start_date and peev.effective_end_date;
select peev.screen_entry_value, count(peev.screen_entry_value) etp_no
from pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
where pee.assignment_id = p_assignment_id
and pet.element_type_id = piv.element_type_id
and pet.element_name = 'ETP on Termination'
and pet.legislation_code = 'AU'
and piv.name = 'Transitional ETP'
and pet.element_type_id = pee.element_type_id
and pee.element_entry_id = peev.element_entry_id
and piv.input_value_id = peev.input_value_id
and p_date_earned between pee.effective_start_date and pee.effective_end_date
and p_date_earned between peev.effective_start_date and peev.effective_end_date
and p_date_earned between pet.effective_start_date and pet.effective_end_date
and p_date_earned between piv.effective_start_date and piv.effective_end_date
and not exists ( select 1 from pay_run_results prr
where prr.element_entry_id = pee.element_entry_id
and prr.element_type_id = pet.element_type_id
and prr.element_type_id = pee.element_type_id
and prr.assignment_action_id <> p_assignment_action_id)
group by peev.screen_entry_value;
select pps.actual_termination_date
from per_periods_of_service pps, per_all_assignments_f paaf
where paaf.assignment_id = p_assignment_id
and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
and paaf.period_of_service_id = pps.period_of_service_id ;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between c_year_start and c_year_end
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paa.action_status = 'C'
and paa.tax_unit_id = p_tax_unit_id;
SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id
,c_max_assignment_action_id
,p_tax_unit_id
,NULL,NULL,NULL,NULL,NULL,NULL,NULL),0)
FROM pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pbt.legislation_code = 'AU'
AND pbt.balance_name = c_balance_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = '_ASG_LE_YTD';
select months_between(l_year_end_paid, l_year_end_atd)/12
into l_count_year
from dual;
SELECT /*+ INDEX(PURF) ORDERED */ NVL(SUM(round((least(fnd_number.canonical_to_number(purf.row_high_range),p_amount)-fnd_number.canonical_to_number(purf.row_low_range_or_name))
*(fnd_number.canonical_to_number(pucif.value) + decode(fnd_number.canonical_to_number(pucif.value),0,0,p_med_levy)))),0) -- bug8647962
FROM pay_user_tables put,
pay_user_rows_F purf,
pay_user_columns puc,
pay_user_column_instances_f pucif
where put.legislation_code='AU'
and put.user_table_name=p_user_table
and put.user_table_id=purf.user_table_id
and put.user_table_id=puc.user_table_id
and puc.user_column_id=pucif.user_column_id
and purf.user_row_id=pucif.user_row_id
and p_date_paid between purf.effective_start_date and purf.effective_end_date
and p_date_paid between pucif.effective_start_date and pucif.effective_end_date
and p_amount > fnd_number.canonical_to_number(purf.row_low_range_or_name) /* 6430072 */
order by fnd_number.canonical_to_number(purf.row_low_range_or_name);
select pps.actual_termination_date
from per_periods_of_service pps, per_all_assignments_f paaf
where paaf.assignment_id = p_assignment_id
and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
and paaf.period_of_service_id = pps.period_of_service_id ;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between c_year_start and c_year_end
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paa.action_status = 'C'
and paa.tax_unit_id = p_tax_unit_id;
SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id
,c_max_assignment_action_id
,p_tax_unit_id
,NULL,NULL,NULL,NULL,NULL,NULL,NULL),0)
FROM pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pbt.legislation_code = 'AU'
AND pbt.balance_name = c_balance_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = '_ASG_LE_YTD';
select months_between(l_year_end_paid, l_year_end_atd)/12
into l_count_year
from dual;
SELECT pap.accrual_plan_id,pap.accrual_units_of_measure
FROM pay_accrual_plans pap,
pay_element_entries_f pee,
pay_element_links_f pel,
pay_element_types_f pet
WHERE pee.assignment_id = c_assignment_id
AND c_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
AND pel.element_link_id = pee.element_link_id
AND pel.element_type_id = pet.element_type_id
AND pap.accrual_plan_element_type_id = pet.element_type_id
AND pap.accrual_category = c_abs_category
AND NVL(pap.information3,'N') = c_fw_plan;
select /*+ STAR_TRANSFORMATION */
/*+ LEADING(paa ptp ppa pac pee) */ nvl(sum((1 + decode(pet.element_name, 'Rec Statutory PPL Payment',least(to_date(peev2.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'), ptp.end_date),
'Statutory Paid Parental Leave Payment' , to_date(peev2.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS')))-decode(pet.element_name, 'Rec Statutory PPL Payment',
greatest(to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'), ptp.start_date),
'Statutory Paid Parental Leave Payment' ,to_date(peev1.screen_entry_value, 'YYYY/MM/DD HH24:MI:SS'))),0)
from
per_time_periods ptp,
per_all_assignments_f paa,
pay_assignment_actions pac,
pay_payroll_actions ppa,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_element_entry_values_f peev1,
pay_element_entry_values_f peev2,
pay_element_types_f pet,
pay_input_values_f piv,
pay_input_values_f piv1,
pay_input_values_f piv2
where
ptp.payroll_id = ppa.payroll_id
and ppa.payroll_action_id = pac.payroll_action_id
and pee.assignment_id = p_assignment_id
and pac.assignment_id = pee.assignment_id
and pee.assignment_id = paa.assignment_id
and pac.assignment_id = paa.assignment_id
and pee.element_type_id = pet.element_type_id
and pet.element_name IN ('Statutory Paid Parental Leave Payment','Rec Statutory PPL Payment')
and pet.legislation_code = 'AU'
and pee.element_entry_id = peev.element_entry_id
and pee.effective_start_date = peev.effective_start_date
and pee.effective_end_date = peev.effective_end_date
and peev.input_value_id = piv.input_value_id
and peev.screen_entry_value = 'N'
and piv.name = 'Accrued'
and pee.element_entry_id = peev1.element_entry_id
and pee.effective_start_date = peev1.effective_start_date
and pee.effective_end_date = peev1.effective_end_date
and peev1.input_value_id = piv1.input_value_id
and piv1.name = 'Start Date'
and pee.element_entry_id = peev2.element_entry_id
and pee.effective_start_date = peev2.effective_start_date
and pee.effective_end_date = peev2.effective_end_date
and peev2.input_value_id = piv2.input_value_id
and piv2.name = 'End Date'
and piv.element_type_id = pet.element_type_id
and piv1.element_type_id = pet.element_type_id
and piv2.element_type_id = pet.element_type_id
and pee.effective_end_date >= greatest(to_date('01/01/2011','DD/MM/YYYY'), p_start_date)
and p_end_date between piv.effective_start_date and piv.effective_end_date
and p_end_date between piv1.effective_start_date and piv1.effective_end_date
and p_end_date between piv2.effective_start_date and piv2.effective_end_date
and p_end_date between pet.effective_start_date and pet.effective_end_date
and ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date
and pee.effective_start_date BETWEEN paa.effective_start_date AND paa.effective_end_date
and ppa.date_earned BETWEEN piv1.effective_start_date AND piv1.effective_end_date
and ppa.date_earned BETWEEN piv2.effective_start_date AND piv2.effective_end_date
and ppa.date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date
and ppa.date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
and ppa.date_earned BETWEEN peev1.effective_start_date AND peev1.effective_end_date
and ppa.date_earned BETWEEN peev2.effective_start_date AND peev2.effective_end_date
and ppa.date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
and pee.creator_type NOT IN ('EE','RR'); /* Bug 10384820 */
select count(pee.element_entry_id)
from pay_element_types_f pet,
pay_input_values_f piv,
pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_f piv2,
pay_element_entry_values_f peev2
where pee.assignment_id = p_assignment_id
and pet.element_type_id = piv.element_type_id
and pet.element_name = 'ETP on Termination'
and pet.element_type_id = pee.element_type_id
and piv.name = 'Pay ETP Components'
and pee.element_entry_id = peev.element_entry_id
and piv.input_value_id = peev.input_value_id
and peev.screen_entry_value = 'Y'
and piv2.name = 'Salary in Lieu of Notice'
and pee.element_entry_id = peev2.element_entry_id
and piv2.input_value_id = peev2.input_value_id
and peev2.screen_entry_value is not null
and p_date_earned between pee.effective_start_date and pee.effective_end_date
and p_date_earned between peev.effective_start_date and peev.effective_end_date
and p_date_earned between pet.effective_start_date and pet.effective_end_date
and p_date_earned between peev.effective_start_date and peev.effective_end_date;
select pps.actual_termination_date
from per_periods_of_service pps, per_all_assignments_f paaf
where paaf.assignment_id = p_assignment_id
and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
and paaf.period_of_service_id = pps.period_of_service_id ;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = p_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between c_year_start and c_year_end
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paa.action_status = 'C'
and paa.tax_unit_id = p_tax_unit_id;
SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id
,c_max_assignment_action_id
,p_tax_unit_id
,NULL,NULL,NULL,NULL,NULL,NULL,NULL),0)
FROM pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pbt.legislation_code = 'AU'
AND pbt.balance_name = c_balance_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = '_ASG_LE_YTD';
select months_between(l_year_end_paid, l_year_end_atd)/12
into l_count_year
from dual;
SELECT /*+ INDEX(PURF) ORDERED */ NVL(SUM(round((least(fnd_number.canonical_to_number(purf.row_high_range),p_amount)-fnd_number.canonical_to_number(purf.row_low_range_or_name))
*(fnd_number.canonical_to_number(pucif.value) + decode(fnd_number.canonical_to_number(pucif.value),0,0,p_med_levy)))),0)
FROM pay_user_tables put,
pay_user_rows_F purf,
pay_user_columns puc,
pay_user_column_instances_f pucif
where put.legislation_code='AU'
and put.user_table_name=p_user_table
and put.user_table_id=purf.user_table_id
and put.user_table_id=puc.user_table_id
and puc.user_column_id=pucif.user_column_id
and purf.user_row_id=pucif.user_row_id
and p_date_paid between purf.effective_start_date and purf.effective_end_date
and p_date_paid between pucif.effective_start_date and pucif.effective_end_date
and p_amount > fnd_number.canonical_to_number(purf.row_low_range_or_name)
order by fnd_number.canonical_to_number(purf.row_low_range_or_name);
select pps.actual_termination_date
from per_periods_of_service pps, per_all_assignments_f paaf
where paaf.assignment_id = p_assignment_id
and p_date_paid between paaf.effective_start_date and paaf.effective_end_date
and paaf.period_of_service_id = pps.period_of_service_id ;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between c_year_start and c_year_end
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paa.action_status = 'C'
and paa.tax_unit_id = p_tax_unit_id;
SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id
,c_max_assignment_action_id
,p_tax_unit_id
,NULL,NULL,NULL,NULL,NULL,NULL,NULL),0)
FROM pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pbt.legislation_code = 'AU'
AND pbt.balance_name = c_balance_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = '_ASG_LE_YTD';
select
pr.row_low_range_or_name low_range,
pr.row_high_range high_range,
pci.value
from pay_user_tables pt, PAY_USER_COLUMNS pc, PAY_USER_ROWS_F pr, PAY_USER_COLUMN_INSTANCES_F pci
where pt.user_table_name = c_user_table_name
and pt.user_table_id = pc.user_table_id
and pt.user_table_id = pr.user_table_id
and pr.user_row_id = pci.user_row_id
and pc.user_column_id = pci.user_column_id
and p_date_paid between pr.effective_start_date and pr.effective_end_date
and p_date_paid between pci.effective_start_date and pci.effective_end_date
order by pr.row_low_range_or_name;
select global_value
from ff_globals_f
where global_name = 'ETP_WHOLE_OF_INCOME'
and legislation_code = 'AU'
and p_date_paid between effective_start_date and effective_end_date;
select distinct paa.assignment_id, pps.actual_termination_date, nvl(pps.final_process_date, to_date('31/12/4712','dd/mm/yyyy')) final_process_date
from per_periods_of_service pps
,per_all_assignments_f paa
where pps.person_id = (
select person_id
from per_all_assignments_f paa1
where paa1.assignment_id = p_assignment_id
and rownum = 1)
and paa.period_of_service_id = pps.period_of_service_id
and nvl(pps.final_process_date, to_date('31/12/4712','dd/mm/yyyy')) >= c_year_start_paid
and paa.assignment_id <> p_assignment_id ;
select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between c_year_start and c_year_end
and ppa.payroll_id = paf.payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paa.action_status = 'C' ;
SELECT nvl(pay_balance_pkg.get_value(pdb.defined_balance_id
,c_max_assignment_action_id
,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL),0)
FROM pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pbt.legislation_code = 'AU'
AND pbt.balance_name = c_balance_name
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pbd.dimension_name = '_ASG_YTD';
select pbt.balance_name
from pay_balance_types pbt
where pbt.balance_name in ('Earnings_Standard'
,'Earnings_Leave_Loading'
,'Earnings_Additional'
,'Earnings_Spread'
,'Leave Payments Marginal'
,'Lump Sum A Payments'
,'Lump Sum B Payments'
,'Pre Tax Fixed Deductions'
,'ETP Taxable Payments Excluded'
,'ETP Taxable Payments Non Excluded'
);
select global_value
from ff_globals_f
where global_name = 'LUMP_SUM_B_PERCENTAGE'
and legislation_code = 'AU'
and p_date_paid between effective_start_date and effective_end_date;
select months_between(l_year_end_paid, l_year_end_atd)/12
into l_count_year
from dual;