[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
*/
g_debug boolean;
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 = p_tax_unit_id
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.end_date
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
-- AND ptp1.start_date >= l_year_start /* Commented Bug 6139035 */
AND ptp1.end_date <= l_period_end;
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 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 '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'
/* 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 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;