[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION update_sscwt_rate
(p_sscwt_rate IN NUMBER
,p_sscwt_element_entry_id IN pay_element_entries_f.element_entry_id%TYPE
,p_effective_date IN DATE)
RETURN BOOLEAN
IS
--
-- Local variables
--
l_correction BOOLEAN;
l_update BOOLEAN;
l_update_override BOOLEAN;
l_update_change_insert BOOLEAN;
l_update_mode VARCHAR2(30);
SELECT object_version_number
FROM pay_element_entries_f
WHERE element_entry_id = p_sscwt_element_entry_id
AND p_effective_date between effective_start_date
and effective_end_date;
l_procedure := g_package || 'update_sscwt_rate';
,p_update => l_update
,p_update_override => l_update_override
,p_update_change_insert=> l_update_change_insert);
IF l_update THEN
l_update_mode := hr_api.g_update;
ELSIF l_update_override THEN
l_update_mode := hr_api.g_update_override;
l_update_mode := hr_api.g_correction;
hr_utility.set_location('Update Mode -> ' || l_update_mode, 30);
pay_element_entry_api.update_element_entry
(p_datetrack_update_mode => l_update_mode
,p_effective_date => p_effective_date
,p_business_group_id => g_business_group_id
,p_element_entry_id => p_sscwt_element_entry_id
,p_object_version_number => l_object_version_number
,p_input_value_id1 => g_input_value_id
,p_entry_value1 => p_sscwt_rate
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_update_warning => l_warning);
hr_utility.set_location('After calling update_element_entry ', 60);
END update_sscwt_rate;
SELECT pay_core_utils.get_parameter('PAYROLL_ID'
,legislative_parameters) payroll_id,
pay_core_utils.get_parameter('ASSIGNMENT_SET'
,legislative_parameters) assignment_set_id,
pay_core_utils.get_parameter('BUSINESS_GROUP_ID'
,legislative_parameters) business_group_id,
pay_core_utils.get_parameter('FINANCIAL_YEAR'
,legislative_parameters) financial_year,
pay_core_utils.get_parameter('PROCESSING_MODE'
,legislative_parameters) processing_mode
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id;
SELECT count(*)
FROM pay_payroll_actions ppa
, per_time_periods ptp
, pay_assignment_actions paa
, per_assignments_f paf
, pay_payrolls_f ppf
WHERE paa.assignment_id = paf.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('R', 'Q')
AND ptp.time_period_id = ppa.time_period_id
AND ppf.payroll_id = ppa.payroll_id
AND ppf.payroll_id = ptp.payroll_id
AND ppf.payroll_id = paf.payroll_id
AND paa.action_status = 'C'
AND ppa.action_status = 'C'
AND ppa.payroll_id = ptp.payroll_id
AND ptp.end_date BETWEEN c_start_date
AND p_end_date
AND p_end_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND p_end_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND paf.assignment_id = p_assignment_id;
p_sql := ' SELECT distinct ppf.person_id' ||
' FROM per_people_f ppf' ||
',pay_payroll_actions ppa' ||
' WHERE ppa.payroll_action_id = :payroll_action_id' ||
' AND ppa.business_group_id = ppf.business_group_id' ||
' ORDER BY ppf.person_id';
SELECT pay_assignment_actions_s.nextval FROM dual;
SELECT DISTINCT assignment.assignment_id
FROM per_people_f person
,per_assignments_f assignment
,per_periods_of_service service
,pay_element_types_f element
,pay_element_links_f link
,pay_element_entries_f entry
WHERE person.person_id BETWEEN p_start_person
AND p_end_person
AND assignment.person_id = person.person_id
AND assignment.business_group_id = person.business_group_id
AND service.period_of_service_id = assignment.period_of_service_id
AND element.element_name = 'SSCWT Information'
AND element.element_type_id = link.element_type_id
AND entry.element_link_id = link.element_link_id
AND entry.assignment_id = assignment.assignment_id
AND link.business_group_id = person.business_group_id
AND (g_payroll_id is null OR assignment.payroll_id = g_payroll_id)
AND hr_assignment_set.assignment_in_set(g_assignment_set_id, assignment.assignment_id) = 'Y'
AND g_financial_year BETWEEN person.effective_start_date
AND person.effective_end_date
AND g_financial_year BETWEEN assignment.effective_start_date
AND assignment.effective_end_date
AND g_financial_year BETWEEN element.effective_start_date
AND element.effective_end_date
AND g_financial_year BETWEEN link.effective_start_date
AND link.effective_end_date
AND g_financial_year BETWEEN entry.effective_start_date
AND entry.effective_end_date
AND g_financial_year BETWEEN service.date_start
AND NVL(service.actual_termination_date, TO_DATE('31-12-4712', 'DD-MM-YYYY')) ;
SELECT pet.element_type_id, piv.input_value_id
FROM pay_element_types_f pet
,pay_input_values_f piv
WHERE pet.element_name = 'SSCWT Information'
AND pet.element_type_id = piv.element_type_id
AND piv.name = 'SSCWT Rate'
AND c_financial_year BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND c_financial_year BETWEEN piv.effective_start_date
AND piv.effective_end_date ;
SELECT defined.defined_balance_id
,bal.balance_name balance_name
FROM pay_balance_types bal
, pay_balance_dimensions dim
, pay_defined_balances defined
WHERE bal.legislation_code = g_legislation_code
AND bal.balance_name IN ( 'Ordinary Taxable Earnings'
,'Extra Emolument Taxable Earnings'
,'KiwiSaver Employer Contributions'
)
AND dim.legislation_code = g_legislation_code
AND dim.dimension_name = '_ASG_YTD'
AND bal.balance_type_id = defined.balance_type_id
AND dim.balance_dimension_id = defined.balance_dimension_id;
SELECT person.full_name
,assignment.assignment_number
,assignment.assignment_id
,periods_in_span(service.date_start, g_financial_year
, assignment.assignment_id) periods
,period_types.number_per_fiscal_year total_periods
FROM per_people_f person,
per_assignments_f assignment,
per_periods_of_service service,
pay_payrolls_f payroll,
per_time_period_types period_types,
pay_assignment_actions actions
WHERE person.business_group_id = c_business_group_id
AND actions.assignment_action_id = c_assignment_action_id
AND assignment.assignment_id = actions.assignment_id
AND assignment.person_id = person.person_id
AND assignment.business_group_id = person.business_group_id
AND service.period_of_service_id = assignment.period_of_service_id
AND payroll.business_group_id = person.business_group_id
AND payroll.payroll_id = assignment.payroll_id
AND period_types.period_type = payroll.period_type
AND g_financial_year BETWEEN person.effective_start_date
AND person.effective_end_date
AND g_financial_year BETWEEN assignment.effective_start_date
AND assignment.effective_end_date
AND g_financial_year BETWEEN payroll.effective_start_date
AND payroll.effective_end_date
AND g_financial_year BETWEEN service.date_start
AND NVL(service.actual_termination_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'));
SELECT max_asg_act.assignment_action_id
FROM pay_assignment_actions max_asg_act
WHERE max_asg_act.assignment_id = c_assignment_id
AND max_asg_act.action_sequence = (
SELECT max(asg_action.action_sequence) action_sequence
FROM pay_assignment_actions asg_action,
pay_payroll_actions pay_action
WHERE asg_action.assignment_id = c_assignment_id
AND asg_action.payroll_action_id = pay_action.payroll_action_id
AND asg_action.action_status = 'C'
AND pay_action.action_status = 'C'
AND pay_action.action_type in ('R', 'Q', 'B')
AND pay_action.effective_date BETWEEN add_months(c_financial_year,-12)
AND c_financial_year-1);
SELECT DECODE(inputv.hot_default_flag,'Y'
,NVL(entry_value.screen_entry_value, NVL(link.default_value
,inputv.default_value)),'N',entry_value.screen_entry_value) value
,entry.element_entry_id element_entry_id
FROM pay_element_entry_values_f entry_value,
pay_element_entries_f entry,
pay_link_input_values_f link,
pay_input_values_f inputv
WHERE inputv.input_value_id = c_input_value_id
AND g_financial_year between inputv.effective_start_date
and inputv.effective_end_date
AND inputv.element_type_id + 0 = c_element_type_id
AND link.input_value_id = inputv.input_value_id
AND g_financial_year between link.effective_start_date
and link.effective_end_date
AND entry_value.input_value_id + 0 = inputv.input_value_id
AND entry_value.element_entry_id = entry.element_entry_id
AND entry_value.effective_start_date = entry.effective_start_date
AND entry_value.effective_end_date = entry.effective_end_date
AND entry.element_link_id = link.element_link_id
AND entry.assignment_id = c_assignment_id
AND g_financial_year between entry.effective_start_date
and entry.effective_end_date
AND NVL(entry.entry_type, 'E') = 'E';
l_flag := update_sscwt_rate(p_sscwt_rate => l_sscwt_new_rate
,p_sscwt_element_entry_id => l_element_entry_id
,p_effective_date => g_financial_year);
hr_utility.set_location('On successful update of element entry value', 100);
hr_utility.set_location('On failure of update of element entry value', 110);