[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** 28-NOV-2001 115.10 Updated for GSCC Standards
** 07-DEC-2001 rsirigir 115.12 Update for GSCC Standards, added
** REM checkfile:~PROD:~PATH:~FILE
** 8-JAN-2002 apunekar 115.13 Added new functions
** 18-May-2002 apunekar 115.14 Added new function
** 20-May-2002 apunekar 115.15 Updated function due to review
** 13-Jun-2002 nanuradh 115.21 Changed the cursor get_retro_period_ee (Bug 2415213)
** 10-Jul-2002 srussell 115.22 Change periods_since_hire_date to allow for
** payrolls which go across financial year.
** Bug 2450059.
** 06-Aug-2002 shoskatt 115.23 Cursor check_fixed_deduction has been tuned for
** improving performance. Bug #2491328
** 17-Sep-2002 Ragovind 115.24 Modified the cursor check_fixed_deduction for performance. Bug#2563515
** 03-Dec-2002 Ragovind 115.25 Added NOCOPY for the function get_retro_period.
** 14-Apr-2003 Vgsriniv 115.26 Modified the function periods_since_hire_date. Bug:2900253
** 19-Aug-2003 punmehta 115.27 Modified conversion functions to support Quarterly Payroll. Bug:2888114
** 22-Aug-2003 srrajago 115.28 Added the new function 'validate_data_magtape'. Refer to Bug no : 3091834
** This function will be used by Payment Summary Data File.
** 26-Aug-2003 srrajago 115.29 Modified function 'validate_data_magtape'.If the return value is null then
** space is returned.
** 27-Aug-2003 srrajago 115.30 Function 'validate_data_magtape' has been modified to return ' '(space)
** if value of the input string 'p_data' passed is Null.
** 03-Nov-2003 punmehta 115.31 Bug# 2977425 - Added the new formula function
** 19-Nov-2003 punmehta 115.32 Bug# 2977425 - Modified message name.
** 11-Dec-2003 jkarouza 115.33 Bug# 3172950 - Removed blank spaces from addresses when two or more
** spaces between words.
** 23-Dec-2003 punmehta 115.34 Bug# 3306112 - Added the new formula function
** 24-Dec-2003 punmehta 115.35 Bug# 3306112 - Used cursor in the new function 'get_salary_basis_hours'
** 06-Feb-2004 punmehta 115.36 Bug# 3245909 - Added a new function get_pp_action for AU_Payments route
** 09-Feb-2004 punmehta 115.37 Bug# 3245909 - Coding Standards in get_pp_action
** 02-FEB-2004 abhkumar 115.38 Bug# 3665680 - Coding Standards in Cr_element_type_id
** 02-FEB-2004 abhkumar 115.39 Bug# 3665680 - Modfied Code to remove gscc warnings
** 07-JUL-2004 srrajago 115.40 Bug# 3724089 - Modified the cursor 'c_get_unprocessed_periods_num' to include table
** per_assignments_f and its joins in the sub-query - Performance Fix.
** 09-AUG-2004 abhkumar 115.41 Bug# 2610141 - Modfied the code to support Legal Employer changes for an assignment.
** 12-AUG-2004 abhkumar 115.42 Bug# 2610141 - Modfied the code to use cursors instead of select query
** 08-SEP-2004 abhkumar 115.43 Bug# 2610141 - Added a flag "p_use_tax_flag" to function periods_since_hire_date and paid_periods_since_hire_date
** to support the versioning of the payroll tax formula.
*** 26-Apr-2005 abhkumar 115.44 Bug#3935471 - Changes due to Retro Tax enhancement.
*** 05-May-2005 abhkumar 115.45 Bug#3935471 - File Modified to put proper comments.
*** 10-May-2005 abhkumar 115.46 Bug#4357306 - Modified function count_retro_periods.
*** 06-Jun-1005 srussell 115.47 Bug#4412537 - Modified count_retro_periods
so that INDEX BY is binary_integer, not varchar2 so that it
doesnt get compile error on 8.1.7.4 data bases.
*** 06-Jun-1005 srussell 115.48 Bug#4412537 - Updated comments.
*** 06-Jun-2005 avenkatk 115.49 Bug#4412537 - Changed to_number to to_number(to_char()) to get l_retro_end_date.
*** 06-Jun-2005 avenkatk 115.50 Bug#4412537 - Removed commented code and Removed redundant to_date() to resolve gscc errors.
*** 07-Jun-2005 abhkumar 115.51 Bug#4415795 - Added new parameter to function count_retro_periods.
*** 23-Jun-2005 abhkumar 115.52 Bug#4438644 - Modified function paid_periods_since_hire_date
*** 26-Jun-2005 avenkatk 115.53 Bug#4451088 - Modified function periods_since_hire_date
*** 26-Jun-2005 avenkatk 115.54 Bug#4451088 - Removed the trace fucntion call.
*** 27-Jun-2005 abhkumar 115.55 Bug#4456941 - Modified function count_retro_periods
*** 27-Jun-2005 ksingla 115.56 Bug#4456720 - Added a new function CALCULATE_ASG_PREV_VALUE for negative retro earnings
*** 05-JuL-2005 abhkumar 115.57 Bug#4467198 - Modified function CALCULATE_ASG_PREV_VALUE for zero average earnings
*** 05-JuL-2005 abhkumar 115.58 Bug#4467198 - Modified cursor c_get_paid_periods and c_check_payroll_run for performance fix.
*** 13-Jul-2005 abhargav 115.59 Bug#4363057 - Modified function CALCULATE_ASG_PREV_VALUE to include fix for bug# 3855355 .
*** 14-Jul-2005 abhkumar 115.60 Bug#4418107 - Modified function count_retro_periods and get_retro_periods to consider Legal Employer changes
*** 08-Aug-2005 abhargav 115.62 Bug#4521653 - Modified the function CALCULATE_ASG_PREV_VALUE .
*** 01-SEP-2005 abhkumar 115.63 Bug#4474896 - Average Earnings enhancement
*** 09-Sep-2005 avenkatk 115.64 Bug#4374115 - Added check in check_fixed_deduction for Reverse Runs.
*** 05-Oct-2005 abhargav 115.65 Bug#4588483 - Modified Cursor check_fixed_deduction.
*** 05-Jul-2006 srussell 115.66 Bug#5374076 - Modified function count_retro_periods to check the retro amounts for each
*** period. If they're zero then don't count the period.
***10-JUL-2006 hnainani 115.67 Bug#5371901 Removed Date_Earned check from function Periods_Since_Hire_date to force
*** code to use Effective Date to calculate numberof periods
*** 11-JUl-2006 hnainani 115.68 Bug#5371901 Modified Comments in function Periods_Since_Hire_Date to correctly
**
** reflect reason for changes.
*** 19-Jul-2006 hnainani 115.69 Bug#5397711 Changed tot_period_amount_type in Countr_Retro_Periods to Number
instead of Number(10) to cater for decimals
*** 09-Oct-2006 avenkatk 115.71 Bug#5586445 Included function get_enhanced_retro_period.
*** 01-Dec-2006 priupadh 115.72 Bug#5676709 Added debug messages to functions
*** 01-Dec-2006 priupadh 115.73 Bug#5676709 removed the occurence of to_date and dd-mon-yyyy format from debug message for GSCC compliance.
*** 17-Jan-2006 avenkatk 115.74 Bug#5846272 Introduced new functions,
** i. check_if_enhanced_retro
** ii. get_retro_time_span
** 16-FEB-2006 priupadh 115.75 N/A Version to restore triple maintanence between 11i,R12 Branch and R12 Mainline
** 10-Apr-2007 abhargav 115.77 Bug#5934468 Added new function get_spread_earning() this function gets called from
formula AU_HECS_DEDUCTION and AU_SFSS_DEDUCTION.
** 18-Apr-2007 avenkatk 115.78 Bug#6001930 Modified Function periods_since_hire_date for
** Postive Offset Payrolls.
** 18-Jun-2007 avenkatk 115.79 Bug#6139035 Modified Function count_retro_periods and get_enhanced_retro_perio - Function
** modified to mark retro time spans based on Date Paid(Effective Date) of
** Payroll run/Quickpay.
** 17-Jan-2008 skshin 115.80 Bug#6669058 Modified function get_spread_earning and added new function get_retro_spread_earning.
** 18-FEB-2008 skshin 115.81 Bug#6809877 Added new function get_etp_pay_component.
** 09-OCT-2008 skshin 115.83 Bug#7228256 Removed DISTINCT from c_get_le_period_num cursor
** 20-APR-2009 skshin 115.85 Bug#7665727 Created count_retro_periods_2009 called from count_retro_periods and modified get_spread_earning function for HECS/SFSS calculation after 01-JUL-2009.
** 20-APR-2009 skshin 115.87 Bug#8406009 Added new function calc_average_earnings and calc_lt12_prev_spread_tax
** 15-JUL-2009 skshin 115.89 Bug#8630738 Modified cursor get_element_entries in count_retro_periods_2009 to return rows for Pre Tax Deduction
** 15-JUL-2009 skshin 115.90 Bug#8682739 Modified cursor c_get_le_period_num in periods_since_hire_date to be based on date_earned
** 30-JUL-2009 skshin 115.91 Bug#8725341 Added Earnings_Leave_Loading balance to be retrieved effective from 01-JUL-2009 in calculate_asg_prev_value function
** 01-SEP-2009 skshin 115.92 Bug#8847457 Added check to select assignment action_status = 'C' to check_fixed_deduction cursor in check_fixed_deduction function
** 08-Oct-2009 avenkatk 115.93 Bug#8765082 Added New Function get_retro_leave_load
** 22-Jul-2010 avenkatk 115.94 Bug#9922156 Modified count_retro_periods_2009 for Leave Loading Tax free case in first period
** 30-Mar-2011 skshin 115.96 Bug#11681524 Modified to fetch original average earnings for retro spread calculation
** 28-Jul-2011 skshin 115.99 Bug#12753997 Modifed c_get_periods cursor and add c_get_periods2 cursor to fetch number of pay periods based on payment date
** 24-Aug-2011 skshin 115.101 Bug#12873456 Added new function get_retro_tax_var_fixed
** 21-Sep-2011 skshin 115.102 Bug#12989692 Modifed calc_lt12_prev_spread_tax function for flood levy deduction
** 11-Oct-2011 dduvvuri 115.103 Bug#12767287 Added a new formula function get_earns_flood_levy for Qantas special case.
** 14-Oct-2011 dduvvuri 115.105 Bug#12767287 Resolved gscc errors with the function get_earns_flood_levy
** 02-Mar-2012 rrajaman 115.106 Bug#13793176 Added number_to_canonical conversion in cursor get_legal_emp_start_date
** 26-Mar-2012 skshin 115.107 Bug#13885520 Made l_period_number to 1 when zero is returned when no date_earned exists in the new financial year
** 17-Apr-2012 skshin 115.108 Bug#13879232 Added a new function get_addtl_message
** 14-May-2012 dduvvuri 115.109 Bug#13906256 Added a new function get_mls_rate
** 22-May-2012 dduvvuri 115.110 Bug#14100809 Added new function get_tax_scale_on_dp and new procedure upgrade_tax_scale
** 23-May-2012 dduvvuri 115.112 Bug#14100809 Fixed GSCC error in package
** 24-May-2012 dduvvuri 115.113 Bug#14100809 Fixed a QA issue in upgrade_tax_scale procedure
** 29-May-2012 dduvvuri 115.115 Bug#14100809 Fixed GSCC error in package
** 01-JUN-2012 dduvvuri 115.116 BUg#14100809 modified the cursor csr_asg_curr_scale to display only tax scale upgraded employees from 7 to 2
** 01-JUN-2012 dduvvuri 115.117 Bug#14155616 resolved errors in upgrade for employees in multiple secured business groups
** 01-JUN-2012 dduvvuri 115.118 BUg#14164637 added functionality for upgrading future or end-dated employee records from tax scale 7 to 2
** 25-JUN-2012 skshin 115.119 BUg#14207669 Modified c_get_le_period_num cursor to compare assignment record with ptp.regular_payment_date because of assignment update on 01-JUL
*/
g_debug boolean;
SELECT pet.element_type_id
FROM pay_element_types_f pet
WHERE pet.element_name = 'Tax Information'
AND p_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND legislation_code = 'AU';
SELECT piv.input_value_id
FROM pay_input_values_f piv
WHERE piv.element_type_id = p_element_type_id
AND p_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.name = 'Spouse';
select
eev0.screen_entry_value
FROM
pay_element_entries_f pee,
pay_element_entry_values_f eev0
WHERE
eev0.element_entry_id = pee.element_entry_id
and eev0.input_value_id = p_inp_value_id
and pee.element_entry_id = p_element_entry_id
AND p_date between eev0.effective_start_date and eev0.effective_end_date;
SELECT MEANING
INTO l_rate
FROM HR_LOOKUPS
WHERE lookup_type = 'AU_MLS_RATES'
AND lookup_code = l_code
AND enabled_flag = 'Y'
AND p_date between START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE,to_date('31-12-4712','DD-MM-YYYY'));
SELECT pet.element_type_id
FROM pay_element_types_f pet
WHERE pet.element_name = 'Tax Information'
AND p_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND legislation_code = 'AU';
SELECT piv.input_value_id
FROM pay_input_values_f piv
WHERE piv.element_type_id = p_element_type_id
AND p_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.name = 'Tax Scale';
select
eev0.screen_entry_value
FROM
pay_element_entries_f pee,
pay_element_entry_values_f eev0
WHERE
eev0.element_entry_id = pee.element_entry_id
and eev0.input_value_id = p_inp_value_id
and pee.element_entry_id = p_element_entry_id
AND p_date between eev0.effective_start_date and eev0.effective_end_date;
select min(effective_start_date)
from per_all_assignments_f paf,
hr_soft_coding_keyflex hsck
where paf.assignment_id = c_assignment_id
and paf.SOFT_CODING_KEYFLEX_ID = hsck.soft_coding_keyflex_id
and hsck.segment1 = fnd_number.number_to_canonical(p_tax_unit_id) -- Bug 13793176
AND paf.effective_start_date <= l_period_end
AND paf.effective_end_date >= l_year_start;
select count(ptp1.time_period_id) /*Bug 4438644, 6001930, 7228256*/
from per_time_periods ptp
,per_time_periods ptp1
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 = p_tax_unit_id
AND paf.effective_start_date <= l_period_end
AND paf.effective_end_date >= l_year_start
AND paf.effective_start_date <= ptp.regular_payment_date /* bug 14207669 */
AND paf.effective_end_date >= ptp.start_date)
AND ptp.payroll_id = p_payroll_id
AND ptp.start_date <= l_period_end
-- AND ptp.end_date >= l_year_start /* Commented Bug 6001930 */
/* Bug 6001930 - Start Changes */
AND ptp.regular_payment_date >= l_year_start
AND ptp.payroll_id = ptp1.payroll_id
AND ptp.regular_payment_date BETWEEN ptp1.start_date AND ptp1.end_date;
select period_num
from per_time_periods
where payroll_id = v_payroll_id
and v_hire_date between start_date and end_date;
select ptp.end_date
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and c_date between
ptp.start_date and ptp.end_date;
select ppa.date_earned,
ppa.effective_date
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = p_assignment_action_id;
select count(*)
from per_time_periods ptp
where payroll_id = p_payroll_id
and ptp.end_date
between c_start_date and c_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 paa.action_status='C'
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 /*4521653 replaced the date_earned with effective date*/
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.action_status='C'
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 ppa.effective_date between c_fin_start_date AND c_fin_end_date /*4521653 replaced the date_earned with effective 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 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.regular_payment_date BETWEEN c_start_date AND c_end_date; -- bug12753997
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.end_date <= c_end_date
AND ptp.regular_payment_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');
select time_period_id
from per_time_periods
where payroll_id = p_payroll_id
and start_Date = p_period_start;
select DISTINCT ptp.time_period_id, ptp.period_num /*Bug 4438644*/
from per_time_periods ptp
where exists (select 'EXISTS' from
per_assignments_f paf,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_id = v_payroll_id
and ppa.action_type in ('R','Q')
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and paf.assignment_id = v_assignment_id
and paa.assignment_id = paf.assignment_id
and paa.tax_unit_id = decode(p_use_tax_flag,'N',paa.tax_unit_id,v_tax_unit_id) --2610141
AND ppa.effective_date BETWEEN v_start_date and v_end_date /*Bug 4438644*/
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date /*Bug 4438644*/
and ppa.date_earned between paf.effective_start_date and paf.effective_end_date)
and ptp.payroll_id = v_payroll_id;
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 ptp.start_date,ptp.end_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and prr.run_result_id = pee.source_id
and paa.assignment_action_id=prr.assignment_action_id
AND paa.tax_unit_id = p_tax_unit_id /*Bug 4418107*/
and ppa.payroll_action_id=paa.payroll_action_id
and ptp.payroll_id=ppa.payroll_id
and pee.creator_type='RR'
and ppa.date_earned between ptp.start_date and ptp.end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT ptp.start_date,ptp.end_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
and paa.assignment_action_id=pee.source_asg_action_id
AND paa.tax_unit_id = p_tax_unit_id /*Bug 4418107*/
and ppa.payroll_action_id=paa.payroll_action_id
and ptp.payroll_id=ppa.payroll_id
and pee.creator_type='EE'
and ppa.date_earned between ptp.start_date and ptp.end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT pee.creator_type,
ppa.effective_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_entries_f pee
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = pee.creator_id
AND pee.element_entry_id = c_element_entry_id
AND ppa.action_type ='L';
SELECT ptp.start_date
,ptp.end_date
,ppa.effective_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_run_results prr,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
AND prr.run_result_id = pee.source_id
AND paa.assignment_action_id=prr.assignment_action_id
AND paa.tax_unit_id = p_tax_unit_id
AND ppa.payroll_action_id=paa.payroll_action_id
AND ptp.payroll_id=ppa.payroll_id
AND pee.creator_type='RR'
AND ppa.date_earned between ptp.start_date and ptp.end_date
AND c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT ptp.start_date
,ptp.end_date
,ppa.effective_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_element_entries_f pee
WHERE pee.element_entry_id=c_element_entry_id
AND paa.assignment_action_id=pee.source_asg_action_id
AND paa.tax_unit_id = p_tax_unit_id
AND ppa.payroll_action_id=paa.payroll_action_id
AND ptp.payroll_id=ppa.payroll_id
AND pee.creator_type='EE'
AND ppa.date_earned between ptp.start_date and ptp.end_date
AND c_date_earned between pee.effective_start_date and pee.effective_end_date;
SELECT rule_mode
FROM pay_legislation_rules plr
,per_business_groups pbg
WHERE plr.legislation_code = pbg.legislation_code
AND pbg.business_group_id = c_business_group_id
AND plr.rule_type = 'ADVANCED_RETRO'
AND pbg.legislation_code = 'AU';
SELECT pee.element_entry_id
,ppa.retro_definition_id
,pepd.retro_component_id
FROM pay_element_entries_f pee
,pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_entry_process_details pepd
WHERE pee.element_entry_id = c_element_entry_id
AND pee.element_entry_id = pepd.element_entry_id
AND pee.creator_id = paa.assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = 'L'
AND c_date_earned between pee.effective_start_date and pee.effective_end_date;
select pee.element_entry_id from pay_element_entries_f pee,
pay_assignment_actions paa
where paa.assignment_action_id=p_assignment_action_id
and pee.assignment_id=paa.assignment_id
and pee.creator_type IN ('EE','RR')
and p_date_earned between pee.effective_start_date and pee.effective_end_date;
select pee.element_entry_id,
peev.screen_entry_value retro_amount,
pec.classification_name
from pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_element_links_f pelf,
pay_element_types_f pet,
pay_element_classifications pec,
pay_input_values_f piv,
pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and pee.assignment_id = paa.assignment_id
and pee.creator_type IN ('EE','RR')
and p_date_earned between pee.effective_start_date and pee.effective_end_date
-- Only Earnings.
and pelf.element_link_id = pee.element_link_id
and p_date_earned between pelf.effective_start_date and pelf.effective_end_date
and pet.element_type_id = pelf.element_type_id
and p_date_earned between pet.effective_start_date and pet.effective_end_date
and pec.classification_id = pet.classification_id
and pec.classification_name in ('Earnings', 'Pre Tax Deductions')
-- Only Pay Value
and peev.element_entry_id = pee.element_entry_id
and p_date_earned between peev.effective_start_date and peev.effective_end_date
and peev.input_value_id = piv.input_value_id
and p_date_earned between piv.effective_start_date and piv.effective_end_date
and piv.name = 'Pay Value';
SELECT plr.rule_mode
FROM pay_legislation_rules plr
WHERE plr.legislation_code = 'AU'
AND plr.rule_type ='ADVANCED_RETRO';
SELECT ppa.effective_date
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paa.assignment_action_id = c_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id;
select pee.element_entry_id,
peev.screen_entry_value retro_amount,
pec.classification_name,
DECODE(pec.classification_name,'Earnings',pec2.classification_name,NULL) label
from pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_element_links_f pelf,
pay_element_types_f pet,
pay_element_classifications pec,
pay_input_values_f piv,
pay_assignment_actions paa,
pay_sub_classification_rules_f psc,
pay_element_classifications pec2 /* Bug 9922156 */
where paa.assignment_action_id = p_assignment_action_id
and pee.assignment_id = paa.assignment_id
and pee.creator_type IN ('EE','RR')
and p_date_earned between pee.effective_start_date and pee.effective_end_date
and pelf.element_link_id = pee.element_link_id
and p_date_earned between pelf.effective_start_date and pelf.effective_end_date
and pet.element_type_id = pelf.element_type_id
and p_date_earned between pet.effective_start_date and pet.effective_end_date
and pec.classification_id = pet.classification_id
and pec.classification_name in ('Earnings', 'Pre Tax Deductions')
and pet.element_type_id = psc.element_type_id(+)
/* Start Bug 9922156 */
and psc.classification_id = pec2.classification_id(+)
and NVL(pec2.classification_name,'Standard') <> 'Spread'
/* End Bug 9922156 */
-- Only Pay Value
and peev.element_entry_id = pee.element_entry_id
and p_date_earned between peev.effective_start_date and peev.effective_end_date
and peev.input_value_id = piv.input_value_id
and p_date_earned between piv.effective_start_date and piv.effective_end_date
and piv.name = 'Pay Value';
select pee.element_entry_id,
peev.screen_entry_value retro_amount,
pec.classification_name
from pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_element_links_f pelf,
pay_element_types_f pet,
pay_element_classifications pec,
pay_input_values_f piv,
pay_assignment_actions paa,
pay_sub_classification_rules_f psc,
pay_element_classifications pec2
where paa.assignment_action_id = p_assignment_action_id
and pee.assignment_id = paa.assignment_id
and pee.creator_type IN ('EE','RR')
and p_date_earned between pee.effective_start_date and pee.effective_end_date
-- Only Earnings Spread
and pelf.element_link_id = pee.element_link_id
and p_date_earned between pelf.effective_start_date and pelf.effective_end_date
and pet.element_type_id = pelf.element_type_id
and p_date_earned between pet.effective_start_date and pet.effective_end_date
and pec.classification_id = pet.classification_id
and pec.classification_name = 'Earnings'
and pet.element_type_id = psc.element_type_id
and psc.classification_id = pec2.classification_id
and pec2.classification_name = 'Spread'
-- Only Pay Value
and peev.element_entry_id = pee.element_entry_id
and p_date_earned between peev.effective_start_date and peev.effective_end_date
and peev.input_value_id = piv.input_value_id
and p_date_earned between piv.effective_start_date and piv.effective_end_date
and piv.name = 'Pay Value';
select pee.element_entry_id,
peev.screen_entry_value retro_amount,
pec.classification_name
from pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_element_links_f pelf,
pay_element_types_f pet,
pay_element_classifications pec,
pay_input_values_f piv,
pay_assignment_actions paa
where paa.assignment_action_id = p_assignment_action_id
and pee.assignment_id = paa.assignment_id
and pee.creator_type IN ('EE','RR')
and p_date_earned between pee.effective_start_date and pee.effective_end_date
and pelf.element_link_id = pee.element_link_id
and p_date_earned between pelf.effective_start_date and pelf.effective_end_date
and pet.element_type_id = pelf.element_type_id
and p_date_earned between pet.effective_start_date and pet.effective_end_date
and pec.classification_id = pet.classification_id
and pec.classification_name in ('Earnings', 'Pre Tax Deductions')
-- Only Pay Value
and peev.element_entry_id = pee.element_entry_id
and p_date_earned between peev.effective_start_date and peev.effective_end_date
and peev.input_value_id = piv.input_value_id
and p_date_earned between piv.effective_start_date and piv.effective_end_date
and piv.name = 'Pay Value';
SELECT plr.rule_mode
FROM pay_legislation_rules plr
WHERE plr.legislation_code = 'AU'
AND plr.rule_type ='ADVANCED_RETRO';
SELECT ppa.effective_date
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paa.assignment_action_id = c_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id;
select 'Y'
from
pay_element_types_f pet,
pay_input_values_f piv,
pay_run_result_values prrv,
pay_run_results prr,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
per_all_assignments_f paaf
where pet.element_name = 'Extra Withholding Payments'
and piv.name='Withholding Amount'
and pet.element_type_id=piv.element_type_id
and piv.input_value_id=prrv.input_value_id
and prrv.run_result_id=prr.run_result_id
and nvl(prrv.result_value,'0') > '0' /*Bug 4588483 */
and prr.assignment_action_id=paa.assignment_action_id
and paa.payroll_action_id=ppa.payroll_action_id
and ptp.payroll_id = ppa.payroll_id
and paa.assignment_id = p_assignment_id /* Bug#2563515 */
and paa.assignment_id = paaf.assignment_id /* Bug#2563515 */
and p_date_earned between paaf.effective_start_date and paaf.effective_end_date /* Bug#2563515 */
and p_date_earned between ptp.start_date and ptp.end_date
and ppa.date_earned between ptp.start_date and ptp.end_date
/* Bug - 2491328 Join added for improving the performance */
and pet.element_type_id = prr.element_type_id
and pet.legislation_code = 'AU'
and piv.legislation_code = 'AU'
and paa.action_status = 'C' /*bug 8847457*/
/* Bug - 2491328 Join added for improving the performance */
/* Bug 4374115 - Start */
and not exists(
select pai.locking_action_id
from pay_assignment_actions paa1,
pay_payroll_actions ppa1,
pay_action_interlocks pai
where ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.action_type = 'V'
and paa1.assignment_id = p_assignment_id
and pai.locking_action_id = paa1.assignment_action_id
and pai.locked_action_id = paa.assignment_action_id
)
/* Bug 4374115 - End */
and not exists(
select piv.name
from
pay_element_types_f pet,
pay_input_values_f piv,
pay_input_values_f piv1,
pay_element_links_f pel, /* Bug#2563515 */
pay_element_entries_f peef, /* Bug#2563515 */
pay_element_entry_values_f peev,
pay_element_entry_values_f peev1
where pet.element_name = 'Extra Withholding Payments'
and pet.element_type_id= piv.element_type_id
and pet.element_type_id = pel.element_type_id /* Bug#2563515 */
and pel.element_link_id = peef.element_link_id /* Bug#2563515 */
and peef.element_entry_id = peev.element_entry_id /* Bug#2563515 */
and peef.element_entry_id = peev1.element_entry_id /* Bug#2563515 */
and piv.name='Withholding Percentage'
and piv.input_value_id=peev.input_value_id
and piv1.name='Withholding Amount'
and nvl(peev1.screen_entry_value,'0') ='0'
and piv1.input_value_id=peev1.input_value_id
and peev.screen_entry_value is not null
and peef.assignment_id = paaf.assignment_id /* Bug#2563515 */
and p_date_earned between pet.effective_start_date and pet.effective_end_date
and p_date_earned between peef.effective_start_date and peef.effective_end_date /* Bug#2563515 */
and p_date_earned between pel.effective_start_date and pel.effective_end_date /* Bug#2563515 */
and p_date_earned between peev1.effective_start_date and peev1.effective_end_date
and p_date_earned between peev.effective_start_date and peev.effective_end_date
/*Bug - 2491328 Join added for improving the performance */
and pet.element_type_id=piv1.element_type_id
);
SELECT prv.result_value
from pay_run_results prr,
pay_run_result_values prv,
pay_element_types_f pet,
pay_input_values_f piv
where prr.assignment_action_id = p_assignment_action_id
and prv.run_result_id = prr.run_result_id
and prv.input_value_id = piv.input_value_id
and prr.element_type_id = pet.element_type_id
and piv.uom like 'H_%'
and piv.element_type_id= pet.element_type_id
and pet.element_name= 'Normal Hours');
SELECT pivf.element_type_id /*Bug# 3665680*/
FROM pay_input_values_f pivf, per_pay_bases ppb
WHERE pivf.input_value_id = ppb.input_value_id
AND ppb.pay_basis_id = p_pay_bases_id);
select INTLK.locking_action_id
from pay_action_interlocks INTLK,
pay_assignment_actions paa,
pay_payroll_actions ppa
where INTLK.locked_action_id = p_action_id
and INTLK.locking_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('P', 'U')
and paa.source_action_id is null;
SELECT paa_master.tax_unit_id
FROM pay_assignment_actions paa_child,
pay_assignment_actions paa_master
WHERE paa_child.assignment_action_id = p_assignment_action_id
AND paa_master.assignment_action_id = paa_child.source_action_id;
select prv.RESULT_VALUE period_spread_over, prr.run_result_id, pee.creator_type
from pay_element_types_f pet,
pay_input_values_f piv,
pay_run_result_values prv,
pay_run_results prr,
pay_element_entries_f pee
where prr.assignment_action_id=p_assignment_action_id
and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
and prv.input_value_id = piv.input_value_id
and piv.name ='Periods Spread Over'
and piv.legislation_code='AU'
and piv.element_type_id = pet.element_type_id
and pet.legislation_code='AU'
and pet.element_type_id = prr.element_type_id
and pet.element_name='Spread Deduction'
and prr.source_id = pee.element_entry_id
and prr.status in ('P','PA') /* bug7665727 to ensure to pick up processed run results only */
and pee.creator_type not in ('EE','RR')
and p_date_paid between pet.effective_start_date and pet.effective_end_date
and p_date_paid between piv.effective_start_date and piv.effective_end_date
and p_date_paid between pee.effective_start_date and pee.effective_end_date;
select prv.RESULT_VALUE
from pay_input_values_f piv,
pay_run_result_values prv,
pay_run_results prr
where prr.RUN_RESULT_ID = p_run_result_id
and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
and prv.input_value_id = piv.input_value_id
and piv.name ='Total Payment'
and piv.legislation_code='AU'
and p_date_paid between piv.effective_start_date and piv.effective_end_date;
select nvl(sum(prv.RESULT_VALUE),0)
from pay_element_types_f pet,
pay_input_values_f piv,
pay_run_result_values prv,
pay_run_results prr,
pay_element_entries_f pee
where prr.assignment_action_id=p_assignment_action_id
and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
and prv.input_value_id = piv.input_value_id
and piv.name ='Total Payment'
and piv.legislation_code='AU'
and piv.element_type_id = pet.element_type_id
and pet.legislation_code='AU'
and pet.element_type_id = prr.element_type_id
and pet.element_name='Spread Deduction'
and prr.source_id = pee.element_entry_id
and pee.creator_type in ('EE','RR')
and p_date_paid between pet.effective_start_date and pet.effective_end_date
and p_date_paid between piv.effective_start_date and piv.effective_end_date
and p_date_paid between pee.effective_start_date and pee.effective_end_date;
select nvl(sum(prv.RESULT_VALUE),0)
from pay_element_types_f pet,
pay_input_values_f piv,
pay_run_result_values prv,
pay_run_results prr,
pay_element_entries_f pee
where prr.assignment_action_id=p_assignment_action_id
and prr.RUN_RESULT_ID = prv.RUN_RESULT_ID
and prv.input_value_id = piv.input_value_id
and piv.name ='Total Payment'
and piv.legislation_code='AU'
and piv.element_type_id = pet.element_type_id
and pet.legislation_code='AU'
and pet.element_type_id = prr.element_type_id
and pet.element_name='Spread Deduction'
and prr.source_id = pee.element_entry_id
and pee.creator_type in ('EE','RR')
and p_date_paid between pet.effective_start_date and pet.effective_end_date
and p_date_paid between piv.effective_start_date and piv.effective_end_date
and p_date_paid between pee.effective_start_date and pee.effective_end_date;
select peev.screen_entry_value
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 = c_assignment_id
and piv.name ='Pay ETP Components'
and piv.legislation_code='AU'
and piv.element_type_id = pet.element_type_id
and pet.legislation_code='AU'
and pet.element_name='ETP on Termination'
and piv.input_value_id = peev.input_value_id
and peev.element_entry_id = pee.element_entry_id
and c_date_earned between pet.effective_start_date and pet.effective_end_date
and c_date_earned between piv.effective_start_date and piv.effective_end_date
and c_date_earned between pee.effective_start_date and pee.effective_end_date
and c_date_earned between peev.effective_start_date and peev.effective_end_date
and rownum = 1;
SELECT COUNT(*)
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = c_assignment_action_id
AND paa.action_status = 'B';
select prv.result_value
from pay_run_results prr,
pay_run_result_values prv,
pay_element_types_f pet,
pay_input_values_f piv
where prr.assignment_action_id = c_assignment_action_id
and prr.element_type_id = pet.element_type_id
and pet.element_name = 'AU Average Earnings'
and pet.legislation_code = 'AU'
and pet.element_type_id = piv.element_type_id
and piv.name = 'Spread'
and piv.input_value_id = prv.input_value_id
and prv.run_result_id = prr.run_result_id
and prr.status = 'B';
SELECT ppa.effective_date
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paa.assignment_action_id = c_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id;
SELECT pee.element_entry_id,
peev.screen_entry_value retro_amount,
pee.creator_type
FROM pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_element_links_f pelf,
pay_element_types_f pet,
pay_element_classifications pec,
pay_input_values_f piv,
pay_sub_classification_rules_f psc,
pay_element_classifications pec2
WHERE pee.assignment_id = c_assignment_id
AND pee.creator_type IN ('EE','RR')
AND c_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
AND pelf.element_link_id = pee.element_link_id
AND c_date_earned BETWEEN pelf.effective_start_date AND pelf.effective_end_date
AND pet.element_type_id = pelf.element_type_id
AND c_date_earned BETWEEN pet.effective_start_date AND pet.effective_end_date
AND pec.classification_id = pet.classification_id
AND pec.classification_name = 'Earnings'
AND pet.element_type_id = psc.element_type_id
AND psc.classification_id = pec2.classification_id
AND pec2.classification_name = 'Spread'
AND peev.element_entry_id = pee.element_entry_id
AND c_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
AND peev.input_value_id = piv.input_value_id
AND c_date_earned BETWEEN piv.effective_start_date AND piv.effective_end_date
AND piv.name = 'Pay Value';
SELECT peev.screen_entry_value
FROM pay_element_entries_f pee
,pay_element_entry_values_f peev
,pay_input_values_f pivf
WHERE pee.element_entry_id = c_element_entry_id
AND pee.assignment_id = c_assignment_id
AND pee.creator_type = 'EE'
AND pee.element_entry_id = peev.element_entry_id
AND pee.element_type_id = pivf.element_type_id
AND pivf.name = 'Periods Spread Over'
AND peev.input_value_id = pivf.input_value_id
AND c_date_earned BETWEEN pee.effective_start_date AND pee.effective_end_date
AND c_date_earned BETWEEN peev.effective_start_date AND peev.effective_end_date
AND c_date_earned BETWEEN pivf.effective_start_date AND pivf.effective_end_date;
SELECT prrv.result_value
FROM pay_run_results prr
,pay_run_result_values prrv
,pay_input_values_f pivf
,pay_element_entries_f pee
WHERE pee.element_entry_id = c_element_entry_id
AND pee.assignment_id = c_assignment_id
AND pee.creator_type = 'RR'
AND prr.run_result_id = pee.source_id
AND prr.run_result_id = prrv.run_result_id
AND prr.assignment_action_id = pee.source_asg_action_id
AND prrv.input_value_id = pivf.input_value_id
AND pivf.name = 'Periods Spread Over'
AND c_date_earned BETWEEN pee.effective_start_date and pee.effective_end_date;
SELECT plr.rule_mode
FROM pay_legislation_rules plr
WHERE plr.legislation_code = 'AU'
AND plr.rule_type ='ADVANCED_RETRO';
SELECT ppa.effective_date
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paa.assignment_action_id = c_assignment_action_id
AND paa.payroll_action_id = ppa.payroll_action_id;
/* New entry comes from an Inserted entry, get the
Periods spread over from retro entry
Assumption: The retro entry has the input 'Periods Spread Over' */
OPEN get_spread_over_ee(l_element_entry_tab(i)
,p_assignment_id
,p_date_earned);
AU has RETRO_DELETE legislation rule enabled, therefore all results are marked
with status 'B' during Retropay recalculation.
This function checks if any Retro Leave Loading GT12, LT12 Prev have been processed
until now and if so, adjust it against the Retro Tax Free annual amount ($320)
Inputs : p_assignment_action_id - Assignment Action ID
p_tax_unit_id - Tax Unit ID
Outputs : p_retro_adj_leave_load - Retro Leave Loading Amount
*/
FUNCTION get_retro_leave_load
(p_assignment_action_id IN NUMBER
,p_tax_unit_id IN NUMBER
,p_retro_adj_leave_load OUT NOCOPY NUMBER)
RETURN NUMBER
AS
/* Cursor replicates RR Route for _ASG_LE_YTD with status = 'B' check added */
CURSOR get_retro_ytd_results(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
,c_tax_unit_id pay_assignment_actions.tax_unit_id%TYPE
,c_balance_type_id pay_balance_types.balance_type_id%TYPE
)
IS
SELECT SUM(NVL(TARGET.result_value,0))
FROM
pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results prr
, pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_payroll_actions ppas
, pay_assignment_actions paas
where paas.assignment_action_id = c_assignment_action_id
and paas.payroll_action_id = ppas.payroll_action_id
and FEED.input_value_id = TARGET.input_value_id
and TARGET.run_result_id = prr.run_result_id
and nvl(TARGET.result_value,'0') <> '0'
and prr.assignment_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.effective_date between FEED.effective_start_date and FEED.effective_end_date
and prr.status = 'B'
and ppa.effective_date >= hr_au_routes.span_start(ppas.effective_date, 1, '01-07-')
and paa.action_sequence <= paas.action_sequence
and paa.assignment_id = paas.assignment_id
and paa.tax_unit_id = c_tax_unit_id
and FEED.balance_type_id = c_balance_type_id ;
SELECT PBT.balance_name
,PBT.balance_type_id
FROM pay_balance_types PBT
WHERE PBT.balance_name IN ('Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount'
,'Retro Earnings Leave Loading GT 12 Mths Amount')
AND PBT.legislation_code ='AU';
SELECT COUNT(*)
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = c_assignment_action_id
AND paa.action_status = 'B';
select prv.result_value
from pay_run_results prr,
pay_run_result_values prv,
pay_input_values_f piv,
pay_element_types_f pet
where prr.assignment_action_id = p_assignment_action_id
and prr.run_result_id = prv.run_result_id
and prv.input_value_id = piv.input_value_id
and piv.name = 'Spread'
and pet.element_name = 'Retropay Tax Variation Amount'
and pet.element_type_id = piv.element_type_id
and prr.element_type_id = piv.element_type_id
and pet.element_type_id = prr.element_type_id
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 ;
SELECT COUNT(*)
FROM pay_assignment_actions paa
WHERE paa.assignment_action_id = p_assignment_action_id
AND paa.action_status = 'B';
select prv.result_value
from pay_run_results prr,
pay_run_result_values prv,
pay_input_values_f piv,
pay_element_types_f pet
where prr.assignment_action_id = p_assignment_action_id
and prr.run_result_id = prv.run_result_id
and prv.input_value_id = piv.input_value_id
and piv.name = 'Total'
and pet.element_name = 'Retropay Tax Variation Amount'
and prr.status = 'B'
and pet.element_type_id = piv.element_type_id
and prr.element_type_id = piv.element_type_id
and pet.element_type_id = prr.element_type_id
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 ;
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
, per_time_periods ptp
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.payroll_id = paf.payroll_id
and ppa.action_type in ('R', 'Q', 'I', 'V', 'B')
and ppa.effective_date between to_date('01-07-2010','dd-mm-yyyy') and to_date('30-06-2011','dd-mm-yyyy')
and ppa.time_period_id = ptp.time_period_id
and P_DATE between ptp.start_date and ptp.end_date
and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
and paa.action_status='C'
AND paa.tax_unit_id = TAX_UNIT_ID;
select pdb.defined_balance_id defined_balance_id
from pay_balance_types pbt
, pay_balance_dimensions pbd
, pay_defined_balances pdb
where pbt.balance_name = p_bal_name
and pbd.database_item_suffix = '_ASG_LE_PTD'
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id
and pbt.legislation_code = 'AU';
SELECT decode(pbt.balance_name,'Earnings_Standard',1
, 'Earnings_Progressive',2
, 'Earnings_Fixed',3
, 'Pre Tax Fixed Deductions',4
, 'Pre Tax Progressive Deductions',5
, 'Earnings_Leave_Loading',6
, 'Retro Earnings Leave Loading GT 12 Mths Amount',7
, 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount',8
, 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount',9
, 'Retro LT 12 Mths Curr Yr Amount',10
, 'Retro LT 12 Mths Prev Yr Amount',11
, 'Lump Sum E Payments',12
, 'ETP Taxable Payments Life Benefit Not Part of Prev Term',13
, 'ETP Taxable Payments Life Benefit Part of Prev Term',14
, 'ETP Taxable Payments Transitional Not Part of Prev Term',15
, 'ETP Taxable Payments Transitional Part of Prev Term',16
, 'Lump Sum A Payments',17
, 'Lump Sum B Payments',18
, 'Leave Payments Marginal',19
, 'Foreign Leave Payments Marginal',20
, 'Foreign Lump Sum A Payments',21
) sort_index,
pdb.defined_balance_id def_bal_id
FROM pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
WHERE pbt.legislation_code = 'AU'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbd.legislation_code = 'AU'
AND pdb.legislation_code = 'AU'
AND pbd.dimension_name = '_ASG_LE_PTD'
and pbt.balance_name in ('Earnings_Standard'
, 'Earnings_Progressive'
, 'Earnings_Fixed'
, 'Pre Tax Fixed Deductions'
, 'Pre Tax Progressive Deductions'
, 'Earnings_Leave_Loading'
, 'Retro Earnings Leave Loading GT 12 Mths Amount'
, 'Retro Earnings Leave Loading LT 12 Mths Curr Yr Amount'
, 'Retro Earnings Leave Loading LT 12 Mths Prev Yr Amount'
, 'Retro LT 12 Mths Curr Yr Amount'
, 'Retro LT 12 Mths Prev Yr Amount'
, 'Lump Sum E Payments'
, 'ETP Taxable Payments Life Benefit Not Part of Prev Term'
, 'ETP Taxable Payments Life Benefit Part of Prev Term'
, 'ETP Taxable Payments Transitional Not Part of Prev Term'
, 'ETP Taxable Payments Transitional Part of Prev Term'
, 'Lump Sum A Payments'
, 'Lump Sum B Payments'
, 'Leave Payments Marginal'
, 'Foreign Leave Payments Marginal'
, 'Foreign Lump Sum A Payments'
)
order by sort_index;
l_update_warning BOOLEAN;
not_inserted boolean := TRUE;
SELECT piv.input_value_id
,piv.name
FROM pay_input_values_f piv
WHERE piv.element_type_id = p_element_type_id
AND g_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT pet.element_type_id
FROM pay_element_types_f pet
WHERE pet.element_name = 'Tax Information'
AND g_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND legislation_code = 'AU';
select pee.assignment_id
,pee.element_entry_id
,pee.effective_start_date
,pee.effective_end_date
,pee.object_version_number
from pay_element_entries_f pee
where pee.ELEMENT_TYPE_ID = p_element_type_id
and g_date between pee.effective_start_date and pee.effective_end_date
and pee.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
and exists (
select 1 from pay_element_entry_values_f peev
where peev.element_entry_id = pee.element_entry_id
and peev.INPUT_VALUE_ID = p_input_value_id and peev.SCREEN_ENTRY_VALUE = '7'
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
)
order by assignment_id;
select pee.assignment_id
from pay_element_entries_f pee
where pee.ELEMENT_TYPE_ID = p_element_type_id
and g_date between pee.effective_start_date and pee.effective_end_date
and pee.effective_start_date = g_date
and exists (
select 1 from pay_element_entry_values_f peev
where peev.element_entry_id = pee.element_entry_id
and peev.INPUT_VALUE_ID = p_input_value_id and peev.SCREEN_ENTRY_VALUE = '2'
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
)
and exists (
select 1 from
pay_element_entry_values_f peev1,
pay_element_entries_f pee1
where pee.ELEMENT_TYPE_ID = p_element_type_id
and g_date-1 between pee1.effective_start_date and pee1.effective_end_date
and peev1.element_entry_id = pee1.element_entry_id
and pee1.element_entry_id = pee.element_entry_id
and peev1.INPUT_VALUE_ID = p_input_value_id and peev1.SCREEN_ENTRY_VALUE = '7'
and peev1.effective_start_date = pee1.effective_start_date
and peev1.effective_end_date = pee1.effective_end_date
)
order by assignment_id;
select pee.assignment_id,pee.element_entry_id,pee.effective_start_date,pee.effective_end_date
from pay_element_entries_f pee
where pee.ELEMENT_TYPE_ID = p_element_type_id
and g_date between pee.effective_start_date and pee.effective_end_date
and exists (
select 1 from pay_element_entry_values_f peev
where peev.element_entry_id = pee.element_entry_id
and peev.INPUT_VALUE_ID = p_input_value_id and peev.SCREEN_ENTRY_VALUE = '7'
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
)
and exists ( select 1 from pay_element_entries_f pee1
where pee1.element_type_id = p_element_type_id
and pee1.assignment_id = pee.assignment_id
and pee1.element_entry_id = pee.element_entry_id
and ( pee1.effective_start_date > pee.effective_start_date
or (pee1.effective_end_date > g_date and pee1.effective_end_date <> to_date('31-12-4712','dd-mm-yyyy')))
)
order by assignment_id;
select
eev0.screen_entry_value,
eev1.screen_entry_value,
eev2.screen_entry_value,
eev3.screen_entry_value,
eev4.screen_entry_value,
eev5.screen_entry_value,
eev6.screen_entry_value,
eev7.screen_entry_value,
eev8.screen_entry_value,
eev9.screen_entry_value,
eev10.screen_entry_value,
eev11.screen_entry_value,
eev12.screen_entry_value,
eev13.screen_entry_value,
eev14.screen_entry_value,
pee.entry_information1
FROM
pay_element_entries_f pee,
pay_element_entry_values_f eev0 ,
pay_element_entry_values_f eev1 ,
pay_element_entry_values_f eev2 ,
pay_element_entry_values_f eev3 ,
pay_element_entry_values_f eev4 ,
pay_element_entry_values_f eev5 ,
pay_element_entry_values_f eev6 ,
pay_element_entry_values_f eev7 ,
pay_element_entry_values_f eev8 ,
pay_element_entry_values_f eev9 ,
pay_element_entry_values_f eev10 ,
pay_element_entry_values_f eev11 ,
pay_element_entry_values_f eev12 ,
pay_element_entry_values_f eev13 ,
pay_element_entry_values_f eev14
WHERE
eev0.element_entry_id = pee.element_entry_id
and eev0.input_value_id = p_inp_value_id_table(1)
and eev1.element_entry_id = pee.element_entry_id
and eev1.input_value_id = p_inp_value_id_table(2)
and eev2.element_entry_id = pee.element_entry_id
and eev2.input_value_id = p_inp_value_id_table(3)
and eev3.element_entry_id = pee.element_entry_id
and eev3.input_value_id = p_inp_value_id_table(4)
and eev4.element_entry_id = pee.element_entry_id
and eev4.input_value_id = p_inp_value_id_table(5)
and eev5.element_entry_id = pee.element_entry_id
and eev5.input_value_id = p_inp_value_id_table(6)
and eev6.element_entry_id = pee.element_entry_id
and eev6.input_value_id = p_inp_value_id_table(7)
and eev7.element_entry_id = pee.element_entry_id
and eev7.input_value_id = p_inp_value_id_table(8)
and eev8.element_entry_id = pee.element_entry_id
and eev8.input_value_id = p_inp_value_id_table(9)
and eev9.element_entry_id = pee.element_entry_id
and eev9.input_value_id = p_inp_value_id_table(10)
and eev10.element_entry_id = pee.element_entry_id
and eev10.input_value_id = p_inp_value_id_table(11)
and eev11.element_entry_id = pee.element_entry_id
and eev11.input_value_id = p_inp_value_id_table(12)
and eev12.element_entry_id = pee.element_entry_id
and eev12.input_value_id = p_inp_value_id_table(13)
and eev13.element_entry_id = pee.element_entry_id
and eev13.input_value_id = p_inp_value_id_table(14)
and eev14.element_entry_id = pee.element_entry_id
and eev14.input_value_id = p_inp_value_id_table(15)
and pee.element_entry_id = p_element_entry_id
AND p_effective_start_date between eev0.effective_start_date and eev0.effective_end_date
AND p_effective_start_date between eev1.effective_start_date and eev1.effective_end_date
AND p_effective_start_date between eev2.effective_start_date and eev2.effective_end_date
AND p_effective_start_date between eev3.effective_start_date and eev3.effective_end_date
AND p_effective_start_date between eev4.effective_start_date and eev4.effective_end_date
AND p_effective_start_date between eev5.effective_start_date and eev5.effective_end_date
AND p_effective_start_date between eev6.effective_start_date and eev6.effective_end_date
AND p_effective_start_date between eev7.effective_start_date and eev7.effective_end_date
AND p_effective_start_date between eev8.effective_start_date and eev8.effective_end_date
AND p_effective_start_date between eev9.effective_start_date and eev9.effective_end_date
AND p_effective_start_date between eev10.effective_start_date and eev10.effective_end_date
AND p_effective_start_date between eev11.effective_start_date and eev11.effective_end_date
AND p_effective_start_date between eev12.effective_start_date and eev12.effective_end_date
AND p_effective_start_date between eev13.effective_start_date and eev13.effective_end_date
AND p_effective_start_date between eev14.effective_start_date and eev14.effective_end_date
AND p_effective_start_date between pee.effective_start_date and pee.effective_end_date;
select distinct business_group_id
from per_all_assignments_f
where assignment_id = p_assignment_id;
SELECT pap.FULL_NAME,pap.EMPLOYEE_NUMBER
from per_all_people_f pap
where pap.person_id in
( select distinct person_id
from per_all_assignments_f
where assignment_id = p_assignment_id
)
and pap.effective_end_date = ( select max(papf.effective_end_date)
from per_all_people_f papf
where papf.person_id in
( select distinct person_id
from per_all_assignments_f
where assignment_id = p_assignment_id
));
SELECT COUNT(*)
FROM per_all_assignments_f
where assignment_id = p_assignment_id
and ( effective_start_date > g_date or ( effective_end_date > g_date and effective_end_date <> to_date('31-12-4712','dd-mm-yyyy'))) ;
SELECT object_version_number,effective_start_date,soft_coding_keyflex_id
from per_all_assignments_f
where assignment_id = p_assignment_id
and g_date between effective_start_date and effective_end_date
and effective_end_date = to_date('31-12-4712','dd-mm-yyyy');
SELECT SEGMENT1
from hr_soft_coding_keyflex
where SOFT_CODING_KEYFLEX_ID = p_hr_soft_id;
SELECT SEGMENT2
from hr_soft_coding_keyflex
where SOFT_CODING_KEYFLEX_ID = p_hr_soft_id;
SELECT assignment_id,effective_start_date,effective_end_date,object_version_number,soft_coding_keyflex_id
FROM per_all_assignments_f
where assignment_id = p_assignment_id
and g_date between effective_start_date and effective_end_date;
SELECT assignment_id,effective_start_date,effective_end_date,object_version_number,soft_coding_keyflex_id
FROM per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date > g_date;
SELECT count(*)
FROM per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date > g_date;
select assignment_id,element_entry_id,effective_start_date,effective_end_date,object_version_number
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and g_date between effective_start_date and effective_end_date;
select assignment_id,element_entry_id,effective_start_date,effective_end_date,object_version_number
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and effective_start_date > g_date;
select count(*)
from pay_element_entries_f
where element_entry_id = p_element_entry_id
and effective_start_date > g_date;
select distinct name into l_business_group_name
from per_business_groups
where business_group_id = p_business_group_id;
select decode(p_mode, 'L', 'Listing Mode', 'A','All','U','Upgrading Mode','Future Upgrade') into l_run_mode from dual;
fnd_file.put_line(FND_FILE.LOG,'Please delete the future-dated/end-dated element entries and (or) future-dated/end-dated assignment changes before running program in UPGRADE mode');
fnd_file.put_line(FND_FILE.LOG,'Please check the data setup for the assignment on Assignment Form and insert the assignment details appropriately' );
fnd_file.put_line(FND_FILE.LOG,'This mode will update the Assignment Leave Loading Flag from No to Yes effective 01-JUL-2012' );
fnd_file.put_line(FND_FILE.LOG,'This mode will also update the Assignment Tax Scale in element entries from 7 to 2 effective 01-JUL-2012' );
l_asg_mode := 'UPDATE';
l_mode := 'UPDATE';
IF not_inserted THEN
INSERT INTO FND_SESSIONS(SESSION_ID,EFFECTIVE_DATE) VALUES(USERENV('SESSIONID'),SYSDATE);
not_inserted := false;
hr_au_assignment_api.update_au_emp_asg
(p_validate => false
,p_effective_date => g_date
,p_datetrack_update_mode => l_asg_mode
,p_assignment_id => asgrec.assignment_id
,p_object_version_number => l_ovn -- in/out parameter
,p_legal_employer_id => l_legal_employer_id
,p_lev_lod_flg => 'Y'
,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
,p_comment_id => l_comment_id -- out parameter
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
,p_effective_start_date => l_effective_start_date -- out parameter
,p_effective_end_date => l_effective_end_date -- out parameter
,p_concatenated_segments => l_concatenated_segments -- out parameter
,p_no_managers_warning => l_no_managers_warning -- out parameter
,p_other_manager_warning => l_other_manager_warning -- out parameter
);
pay_element_entry_api.update_element_entry
(p_validate => false
,p_datetrack_update_mode => l_mode
,p_effective_date => g_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => asgrec.element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_inp_value_id_table(1)
,p_input_value_id2 => l_inp_value_id_table(2)
,p_input_value_id3 => l_inp_value_id_table(3)
,p_input_value_id4 => l_inp_value_id_table(4)
,p_input_value_id5 => l_inp_value_id_table(5)
,p_input_value_id6 => l_inp_value_id_table(6)
,p_input_value_id7 => l_inp_value_id_table(7)
,p_input_value_id8 => l_inp_value_id_table(8)
,p_input_value_id9 => l_inp_value_id_table(9)
,p_input_value_id10 => l_inp_value_id_table(10)
,p_input_value_id11 => l_inp_value_id_table(11)
,p_input_value_id12 => l_inp_value_id_table(12)
,p_input_value_id13 => l_inp_value_id_table(13)
,p_input_value_id14 => l_inp_value_id_table(14)
,p_input_value_id15 => l_inp_value_id_table(15)
,p_entry_value1 => l_screen_entry_values(1)
,p_entry_value2 => l_screen_entry_values(2)
,p_entry_value3 => l_screen_entry_values(3)
,p_entry_value4 => l_screen_entry_values(4)
,p_entry_value5 => l_screen_entry_values(5)
,p_entry_value6 => l_screen_entry_values(6)
,p_entry_value7 => fnd_date.canonical_to_date(l_screen_entry_values(7))
,p_entry_value8 => l_screen_entry_values(8)
,p_entry_value9 => l_screen_entry_values(9)
,p_entry_value10 => l_screen_entry_values(10)
,p_entry_value11 => l_screen_entry_values(11)
,p_entry_value12 => l_screen_entry_values(12)
,p_entry_value13 => l_new_value
,p_entry_value14 => l_screen_entry_values(14)
,p_entry_value15 => l_screen_entry_values(15)
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_update_warning
,p_override_user_ent_chk => 'Y'
);
fnd_file.put_line(FND_FILE.LOG,'This mode will update the Assignment Leave Loading Flag from No to Yes effective 01-JUL-2012' );
fnd_file.put_line(FND_FILE.LOG,'This mode will also update the Assignment Tax Scale in element entries from 7 to 2 effective 01-JUL-2012' );
l_mode := 'UPDATE';
l_mode := 'UPDATE_CHANGE_INSERT';
l_mode := 'UPDATE';
IF not_inserted THEN
INSERT INTO FND_SESSIONS(SESSION_ID,EFFECTIVE_DATE) VALUES(USERENV('SESSIONID'),SYSDATE);
not_inserted := false;
hr_au_assignment_api.update_au_emp_asg
(p_validate => false
,p_effective_date => g_date
,p_datetrack_update_mode => l_mode
,p_assignment_id => fir.assignment_id
,p_object_version_number => l_object_version_number -- out parameter
,p_legal_employer_id => l_le_id
,p_lev_lod_flg => 'Y'
,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
,p_comment_id => l_comment_id -- out parameter
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
,p_effective_start_date => l_effective_start_date -- out parameter
,p_effective_end_date => l_effective_end_date -- out parameter
,p_concatenated_segments => l_concatenated_segments -- out parameter
,p_no_managers_warning => l_no_managers_warning -- out parameter
,p_other_manager_warning => l_other_manager_warning -- out parameter
);
IF not_inserted THEN
INSERT INTO FND_SESSIONS(SESSION_ID,EFFECTIVE_DATE) VALUES(USERENV('SESSIONID'),SYSDATE);
not_inserted := false;
hr_au_assignment_api.update_au_emp_asg
(p_validate => false
,p_effective_date => nxt.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => nxt.assignment_id
,p_object_version_number => l_object_version_number -- out parameter
,p_legal_employer_id => l_le_id
,p_lev_lod_flg => 'Y'
,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
,p_comment_id => l_comment_id -- out parameter
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
,p_effective_start_date => l_effective_start_date -- out parameter
,p_effective_end_date => l_effective_end_date -- out parameter
,p_concatenated_segments => l_concatenated_segments -- out parameter
,p_no_managers_warning => l_no_managers_warning -- out parameter
,p_other_manager_warning => l_other_manager_warning -- out parameter
);
l_mode := 'UPDATE';
l_mode := 'UPDATE_CHANGE_INSERT';
l_mode := 'UPDATE';
pay_element_entry_api.update_element_entry
(p_validate => false
,p_datetrack_update_mode => l_mode
,p_effective_date => g_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => elerec.element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_inp_value_id_table(1)
,p_input_value_id2 => l_inp_value_id_table(2)
,p_input_value_id3 => l_inp_value_id_table(3)
,p_input_value_id4 => l_inp_value_id_table(4)
,p_input_value_id5 => l_inp_value_id_table(5)
,p_input_value_id6 => l_inp_value_id_table(6)
,p_input_value_id7 => l_inp_value_id_table(7)
,p_input_value_id8 => l_inp_value_id_table(8)
,p_input_value_id9 => l_inp_value_id_table(9)
,p_input_value_id10 => l_inp_value_id_table(10)
,p_input_value_id11 => l_inp_value_id_table(11)
,p_input_value_id12 => l_inp_value_id_table(12)
,p_input_value_id13 => l_inp_value_id_table(13)
,p_input_value_id14 => l_inp_value_id_table(14)
,p_input_value_id15 => l_inp_value_id_table(15)
,p_entry_value1 => l_screen_entry_values(1)
,p_entry_value2 => l_screen_entry_values(2)
,p_entry_value3 => l_screen_entry_values(3)
,p_entry_value4 => l_screen_entry_values(4)
,p_entry_value5 => l_screen_entry_values(5)
,p_entry_value6 => l_screen_entry_values(6)
,p_entry_value7 => fnd_date.canonical_to_date(l_screen_entry_values(7))
,p_entry_value8 => l_screen_entry_values(8)
,p_entry_value9 => l_screen_entry_values(9)
,p_entry_value10 => l_screen_entry_values(10)
,p_entry_value11 => l_screen_entry_values(11)
,p_entry_value12 => l_screen_entry_values(12)
,p_entry_value13 => '2'
,p_entry_value14 => l_screen_entry_values(14)
,p_entry_value15 => l_screen_entry_values(15)
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_update_warning
,p_override_user_ent_chk => 'Y'
);
pay_element_entry_api.update_element_entry
(p_validate => false
,p_datetrack_update_mode => 'CORRECTION'
,p_effective_date => nxtele.effective_start_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => nxtele.element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_inp_value_id_table(1)
,p_input_value_id2 => l_inp_value_id_table(2)
,p_input_value_id3 => l_inp_value_id_table(3)
,p_input_value_id4 => l_inp_value_id_table(4)
,p_input_value_id5 => l_inp_value_id_table(5)
,p_input_value_id6 => l_inp_value_id_table(6)
,p_input_value_id7 => l_inp_value_id_table(7)
,p_input_value_id8 => l_inp_value_id_table(8)
,p_input_value_id9 => l_inp_value_id_table(9)
,p_input_value_id10 => l_inp_value_id_table(10)
,p_input_value_id11 => l_inp_value_id_table(11)
,p_input_value_id12 => l_inp_value_id_table(12)
,p_input_value_id13 => l_inp_value_id_table(13)
,p_input_value_id14 => l_inp_value_id_table(14)
,p_input_value_id15 => l_inp_value_id_table(15)
,p_entry_value1 => l_screen_entry_values(1)
,p_entry_value2 => l_screen_entry_values(2)
,p_entry_value3 => l_screen_entry_values(3)
,p_entry_value4 => l_screen_entry_values(4)
,p_entry_value5 => l_screen_entry_values(5)
,p_entry_value6 => l_screen_entry_values(6)
,p_entry_value7 => fnd_date.canonical_to_date(l_screen_entry_values(7))
,p_entry_value8 => l_screen_entry_values(8)
,p_entry_value9 => l_screen_entry_values(9)
,p_entry_value10 => l_screen_entry_values(10)
,p_entry_value11 => l_screen_entry_values(11)
,p_entry_value12 => l_screen_entry_values(12)
,p_entry_value13 => '2'
,p_entry_value14 => l_screen_entry_values(14)
,p_entry_value15 => l_screen_entry_values(15)
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_update_warning
,p_override_user_ent_chk => 'Y'
);
l_mode := 'UPDATE';
l_mode := 'UPDATE_CHANGE_INSERT';
l_mode := 'UPDATE';
IF not_inserted THEN
INSERT INTO FND_SESSIONS(SESSION_ID,EFFECTIVE_DATE) VALUES(USERENV('SESSIONID'),SYSDATE);
not_inserted := false;
hr_au_assignment_api.update_au_emp_asg
(p_validate => false
,p_effective_date => g_date
,p_datetrack_update_mode => l_mode
,p_assignment_id => fir.assignment_id
,p_object_version_number => l_object_version_number -- out parameter
,p_legal_employer_id => l_le_id
,p_lev_lod_flg => 'Y'
,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
,p_comment_id => l_comment_id -- out parameter
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
,p_effective_start_date => l_effective_start_date -- out parameter
,p_effective_end_date => l_effective_end_date -- out parameter
,p_concatenated_segments => l_concatenated_segments -- out parameter
,p_no_managers_warning => l_no_managers_warning -- out parameter
,p_other_manager_warning => l_other_manager_warning -- out parameter
);
IF not_inserted THEN
INSERT INTO FND_SESSIONS(SESSION_ID,EFFECTIVE_DATE) VALUES(USERENV('SESSIONID'),SYSDATE);
not_inserted := false;
hr_au_assignment_api.update_au_emp_asg
(p_validate => false
,p_effective_date => nxt.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_assignment_id => nxt.assignment_id
,p_object_version_number => l_object_version_number -- out parameter
,p_legal_employer_id => l_le_id
,p_lev_lod_flg => 'Y'
,p_cagr_grade_def_id => l_cagr_grade_def_id -- out parameter
,p_cagr_concatenated_segments => l_cagr_concatenated_segments -- out parameter
,p_comment_id => l_comment_id -- out parameter
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id -- out parameter
,p_effective_start_date => l_effective_start_date -- out parameter
,p_effective_end_date => l_effective_end_date -- out parameter
,p_concatenated_segments => l_concatenated_segments -- out parameter
,p_no_managers_warning => l_no_managers_warning -- out parameter
,p_other_manager_warning => l_other_manager_warning -- out parameter
);
l_mode := 'UPDATE';
pay_element_entry_api.update_element_entry
(p_validate => false
,p_datetrack_update_mode => l_mode
,p_effective_date => g_date
,p_business_group_id => l_business_group_id
,p_element_entry_id => asgrec.element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => l_inp_value_id_table(1)
,p_input_value_id2 => l_inp_value_id_table(2)
,p_input_value_id3 => l_inp_value_id_table(3)
,p_input_value_id4 => l_inp_value_id_table(4)
,p_input_value_id5 => l_inp_value_id_table(5)
,p_input_value_id6 => l_inp_value_id_table(6)
,p_input_value_id7 => l_inp_value_id_table(7)
,p_input_value_id8 => l_inp_value_id_table(8)
,p_input_value_id9 => l_inp_value_id_table(9)
,p_input_value_id10 => l_inp_value_id_table(10)
,p_input_value_id11 => l_inp_value_id_table(11)
,p_input_value_id12 => l_inp_value_id_table(12)
,p_input_value_id13 => l_inp_value_id_table(13)
,p_input_value_id14 => l_inp_value_id_table(14)
,p_input_value_id15 => l_inp_value_id_table(15)
,p_entry_value1 => l_screen_entry_values(1)
,p_entry_value2 => l_screen_entry_values(2)
,p_entry_value3 => l_screen_entry_values(3)
,p_entry_value4 => l_screen_entry_values(4)
,p_entry_value5 => l_screen_entry_values(5)
,p_entry_value6 => l_screen_entry_values(6)
,p_entry_value7 => fnd_date.canonical_to_date(l_screen_entry_values(7))
,p_entry_value8 => l_screen_entry_values(8)
,p_entry_value9 => l_screen_entry_values(9)
,p_entry_value10 => l_screen_entry_values(10)
,p_entry_value11 => l_screen_entry_values(11)
,p_entry_value12 => l_screen_entry_values(12)
,p_entry_value13 => '2'
,p_entry_value14 => l_screen_entry_values(14)
,p_entry_value15 => l_screen_entry_values(15)
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_update_warning
,p_override_user_ent_chk => 'Y'
);
fnd_file.put_line(FND_FILE.LOG,'assignment record does not exist for assignment id '||asgrec.assignment_id|| '. probably a case of data corruption. please insert record and then upgrade');