The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM A.STANDER 12-MAR-2000 110.2 Change not to update people that
REM have been updated manualy
REM L. Kloppers 23-SEP-2002 115.1 2224332 Modify to cater for new
REM Tax Statuses 'N' and 'P'
REM ========================================================================
*/
/* This procedure sets the Tax Certificate number
(input value on the ZA_Tax element) to 'N', clears the
Tax Directive Number and Tax Directive Value (also input
values on the ZA_Tax element) and set the input value
Tax Status on the ZA_Tax element to 'A' - Normal where the Tax Status is
'C' or 'D', and set it to 'M' - Private Director where the Tax Status is
'N' or 'P'*/
PROCEDURE reset_all_ind
(
p_errmsg OUT NOCOPY VARCHAR2,
p_errcode OUT NOCOPY NUMBER,
p_payroll NUMBER,
p_tax_year VARCHAR2)
AS
l_tax_year NUMBER;
l_tax_year_start := Pay_Za_Update_Pkg.get_tax_year_end(p_payroll, p_tax_year);
SELECT element_type_id
INTO l_element_type_id
FROM pay_element_types_f
WHERE element_name = 'ZA_Tax'
AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
INTO tax_status_id
FROM pay_input_values_f
WHERE element_type_id = l_element_type_id
AND name = 'Tax Status'
AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
INTO tax_directive_number_id
FROM pay_input_values_f
WHERE element_type_id = l_element_type_id
AND name = 'Tax Directive Number'
AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
INTO tax_directive_value_id
FROM pay_input_values_f
WHERE element_type_id = l_element_type_id
AND name = 'Tax Directive Value'
AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
IF Pay_Za_Update_Pkg.payroll_updateble(p_payroll, l_tax_year) THEN
FOR v_assignments IN c_assignments(p_payroll, l_tax_year_end)
LOOP
OPEN c_entry_details(l_element_type_id,tax_status_id,v_assignments.assignment_id,p_payroll,l_tax_year_end);
IF NOT Pay_Za_Update_Pkg.entry_valid(l_one_record,'NEXT_DAY_CHANGE') THEN
l_new_value := 'Normal';
Pay_Za_Update_Pkg.update_this_record
(
l_one_record,
l_new_value,
'UPDATE_CHANGE_INSERT'
);
IF NOT Pay_Za_Update_Pkg.entry_valid(l_one_record,'NEXT_DAY_CHANGE') THEN
l_new_value := 'Private Director';
Pay_Za_Update_Pkg.update_this_record
(
l_one_record,
l_new_value,
'UPDATE_CHANGE_INSERT'
);
IF NOT Pay_Za_Update_Pkg.entry_valid(l_one_record,'NEXT_DAY_CHANGE') THEN
l_new_value := ' ';
Pay_Za_Update_Pkg.update_this_record
(
l_one_record,
l_new_value,
'UPDATE_CHANGE_INSERT'
);
IF NOT Pay_Za_Update_Pkg.entry_valid(l_one_record,'NEXT_DAY_CHANGE') THEN
-- l_new_value := ' ';
Pay_Za_Update_Pkg.update_this_record
(
l_one_record,
l_new_value,
'UPDATE_CHANGE_INSERT'
);
Pay_Za_Update_Pkg.update_tysp_table
(
p_payroll,
l_tax_year
);
l_tax_year_start := Pay_Za_Update_Pkg.get_tax_year_end(p_payroll, p_tax_year);
SELECT element_type_id
INTO l_element_type_id
FROM pay_element_types_f
WHERE element_name = 'ZA_Tax'
AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
INTO tax_status_id
FROM pay_input_values_f
WHERE element_type_id = l_element_type_id
AND name = 'Tax Status'
AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
INTO tax_directive_number_id
FROM pay_input_values_f
WHERE element_type_id = l_element_type_id
AND name = 'Tax Directive Number'
AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
INTO tax_directive_value_id
FROM pay_input_values_f
WHERE element_type_id = l_element_type_id
AND name = 'Tax Directive Value'
AND l_tax_year_end BETWEEN effective_start_date AND effective_end_date;
IF Pay_Za_Update_Pkg.payroll_rollbackable(p_payroll, l_tax_year) THEN
FOR v_assignments IN c_assignments(p_payroll, l_tax_year_end)
LOOP
-- Check and update tax_status if tax_status in ('C','D','N','P') --
OPEN c_entry_details(l_element_type_id,tax_status_id,v_assignments.assignment_id,p_payroll,l_tax_year_end);
Pay_Za_Update_Pkg.rollback_this_record(l_one_record);
Pay_Za_Update_Pkg.rollback_this_record(l_one_record);
Pay_Za_Update_Pkg.rollback_this_record(l_one_record);
Pay_Za_Update_Pkg.delete_tysp_table
(
p_payroll,
l_tax_year
);