[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select assignments only if they are on the
chosen payroll in the specified payroll period
for which the Tax Register is being run, and
csr_irp5_balances to
select lump sum balances for an assignment, even
where they were paid in earlier payrolls that
the assignment was on.
A.Sengar 10/12/2002 115.14 2665394 Modified the cursor
csr_processed_assignments to
improve the performance of the
select statement.
L. Kloppers 23/09/2002 115.11 2224332 Added Procedure assignment_nature
Modified Procedure pre_process to call
py_za_tax_certificates.get_sars_code
for correct saving of balance codes for
Foreign- and Directors Income
Removed DEFAULT NULL for two parameters in
public procedure pre_process as per gscc
J.N. Louw 29/05/2002 115.9 1858619 Fixing QA raised issues
2377480 Legal Entity fetch per
assignment and not per
organization
J.N. Louw 28/02/2002 115.8 Added
hr_utility calls
Removed
record creation for
assignment with no
balance values
J.N. Louw 04/02/2002 115.7 Added
include_assignment
J.N. Louw 25/01/2002 115.5 1756600 Register was updated to
1756617 accommodate bug changes
1858619 and merge of both
2117507 current and terminated
2132644 assignments reports
L. Kloppers 01-Mar-2001 115.4 Changed
per_assignment_status_types_tl
back to
per_assignment_status_types
and use PER_SYSTEM_STATUS
i.s.o. USER_STATUS
L. Kloppers 23-Feb-2001 115.3 Changed
per_assignment_status_types
to
per_assignment_status_types_tl
L. Kloppers 06-Feb-2001 115.2 Changed "end_date"
to "ptp.end_date"
L. Kloppers 31-Jan-2001 115.1 Changed attribute1
to prd_information1
A vd Berg 22-Jan-2001 110.11 Amended Version Number
G. Fraser 10-Nov-2000 110.8 Changed Termination
Assignment Cursor
G. Fraser 24-May-2000 110.3-7 Speed improvements
L.J.Kloppers 23-Feb-2000 110.2 Added p_tax_register_id
IN OUT NOCOPY parameter
L.J.Kloppers 13-Feb-2000 110.1 Added p_total_employees
and p_total_assignments
IN OUT NOCOPY parameters
L.J.Kloppers 12-Feb-2000 110.0 Initial Version
*/
-------------------------------------------------------------------------------
-- PACKAGE BODY --
-------------------------------------------------------------------------------
------------------
-- Package Globals
------------------
g_tax_register_id pay_za_tax_registers.tax_register_id%TYPE;
SELECT
pbt.balance_type_id
FROM
pay_balance_types pbt
WHERE
pbt.balance_name = p_balance_name
AND pbt.business_group_id IS NULL
AND pbt.legislation_code = 'ZA';
SELECT
prrv.result_value
, prrv.run_result_id
FROM
pay_element_types_f pet
, pay_input_values_f piv
, pay_run_results prr
, pay_run_result_values prrv
WHERE
pet.element_name = p_element_name
AND pet.legislation_code = 'ZA'
AND pet.element_type_id = piv.element_type_id
AND piv.name = p_value_name
AND piv.input_value_id = prrv.input_value_id
AND prr.element_type_id = pet.element_type_id
AND prr.run_result_id = prrv.run_result_id
AND prr.assignment_action_id =
(
SELECT
MAX(paa2.assignment_action_id)
FROM
pay_run_results prr2
, pay_assignment_actions paa2
, pay_payroll_actions ppa2
WHERE
prr2.element_type_id = pet.element_type_id
AND prr2.run_result_id = nvl(p_run_result_id, prr2.run_result_id)
AND prr2.assignment_action_id = paa2.assignment_action_id
AND paa2.assignment_id = p_assignment_id
AND paa2.payroll_action_id = ppa2.payroll_action_id
AND ppa2.action_type IN ('R', 'Q', 'I', 'B', 'V')
AND ppa2.time_period_id BETWEEN g_start_period_id
AND g_end_period_id
);
SELECT
prrv.result_value
FROM
pay_run_results prr
, pay_input_values_f piv
, pay_run_result_values prrv
WHERE
prr.run_result_id = p_run_result_id
AND prr.element_type_id = piv.element_type_id
AND piv.name = p_value_name
AND piv.input_value_id = prrv.input_value_id
AND prr.run_result_id = prrv.run_result_id;
SELECT hl.meaning
FROM hr_lookups hl
WHERE hl.lookup_type = p_lookup_type
AND hl.lookup_code = p_lookup_code
AND hl.application_id = p_application_id;
SELECT
nvl(fcl.meaning, 'A') nature
FROM
per_all_assignments_f ass
, per_assignment_extra_info aei
, fnd_lookup_values fcl
WHERE ass.assignment_id = c_assignment_id
AND ass.effective_start_date =
(
SELECT max(paf2.effective_start_date)
FROM per_all_assignments_f paf2
WHERE paf2.assignment_id = ass.assignment_id
AND paf2.effective_start_date <= c_effective_date
)
AND ass.assignment_id = aei.assignment_id(+)
AND aei.aei_information_category = 'ZA_SPECIFIC_INFO'
AND fcl.lookup_type(+) = 'ZA_PER_NATURES'
AND fcl.lookup_code(+) = aei.aei_information4
AND fcl.language(+) = 'US';
SELECT MIN(per.effective_start_date)
FROM per_all_assignments_f per
, per_assignment_status_types past
WHERE per.assignment_id = p_assignment_id
AND per.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
SELECT MAX(per.effective_end_date)
FROM per_all_assignments_f per
, per_assignment_status_types past
WHERE per.assignment_id = p_assignment_id
AND per.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
SELECT
count(max(tr.person_id))
FROM
pay_za_tax_registers tr
GROUP BY
tr.person_id;
SELECT
count(max(tr.assignment_id))
FROM
pay_za_tax_registers tr
GROUP BY
tr.assignment_id;
SELECT
MIN(ptp.time_period_id) min_time_period
FROM
per_time_periods ptp
WHERE
ptp.payroll_id = g_payroll_id
AND ptp.prd_information1 =
(
SELECT ptp2.prd_information1
FROM per_time_periods ptp2
WHERE ptp2.payroll_id = g_payroll_id
AND ptp2.time_period_id = g_end_period_id
);
SELECT ptp.period_num
, ptp.start_date
, ptp.end_date
FROM per_time_periods ptp
WHERE ptp.time_period_id = g_end_period_id;
SELECT
pap.payroll_name
FROM
pay_all_payrolls_f pap
WHERE
pap.payroll_id = g_payroll_id
AND g_period_end_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT
pay_za_tax_registers_s.nextval
INTO
g_tax_register_id
FROM
dual;
INSERT INTO pay_za_tax_registers (
tax_register_id
, full_name
, employee_number
, person_id
, date_of_birth
, age
, tax_reference_no
, cmpy_tax_reference_no
, tax_status
, tax_directive_value
, days_worked
, assignment_id
, assignment_action_id
, assignment_number
, assignment_start_date
, assignment_end_date
, bal_name
, bal_code
, tot_ptd
, tot_mtd
, tot_ytd
)
VALUES (
g_tax_register_id
, p_full_name
, p_employee_number
, p_person_id
, p_date_of_birth
, p_age
, p_tax_reference_no
, p_cmpy_tax_reference_no
, p_tax_status
, p_tax_directive_value
, p_days_worked
, p_assignment_id
, p_assignment_action_id
, p_assignment_number
, p_assignment_start_date
, p_assignment_end_date
, p_bal_name
, p_bal_code
, p_tot_ptd
, p_tot_mtd
, p_tot_ytd
);
DELETE
FROM
pay_za_tax_registers ztr
WHERE
ztr.tax_register_id = p_id;
SELECT
paa.assignment_action_id
, paa.assignment_id
, ppa.time_period_id
, ppa.effective_date
, asg.assignment_number
, pap.person_id
, pap.full_name
, pap.date_of_birth
, pap.employee_number
, pap.per_information1 tax_reference_number
, trunc(months_between(g_period_end_date, pap.date_of_birth)/12) age
, oit.org_information3 cmpy_tax_reference_number
FROM
pay_assignment_actions paa
, pay_payroll_actions ppa
, hr_organization_information oit
, per_assignment_extra_info aei
, per_assignments_f asg
, per_people_f pap
, (select end_date from per_time_periods ptp where ptp.time_period_id = p_end_period_id) ptp
WHERE
ppa.payroll_id = p_payroll_id
AND ppa.time_period_id >= p_start_period_id
AND ppa.time_period_id <= p_end_period_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = nvl(p_asg_id, paa.assignment_id)
AND paa.rowid =
(select rowid from pay_assignment_actions paa2 where
paa2.assignment_id=paa.assignment_id
and paa2.action_sequence=
(select MAX(paa3.action_sequence) from pay_assignment_actions paa3,
pay_payroll_actions ppa2
where paa3.assignment_id = paa.assignment_id
and paa3.payroll_action_id = ppa2.payroll_action_id
and ppa2.action_type IN ('R', 'Q', 'I', 'B', 'V')
and ppa2.time_period_id <= p_end_period_id
and ppa2.payroll_id = p_payroll_id
)
)
AND paa.assignment_id = asg.assignment_id
AND (
(
asg.effective_start_date <= ptp.end_date
AND asg.effective_end_date >= ptp.end_date
)
OR
(
asg.effective_end_date <= ptp.end_date
AND asg.effective_end_date = (select max(asg2.effective_end_date)
from per_assignments_f asg2
where asg2.assignment_id = asg.assignment_id)
)
)
AND asg.payroll_id = p_payroll_id
AND asg.assignment_id = aei.assignment_id(+)
AND aei.aei_information_category(+) = 'ZA_SPECIFIC_INFO'
AND aei.aei_information7 = oit.organization_id(+)
AND oit.org_information_context(+) = 'ZA_LEGAL_ENTITY'
AND asg.person_id = pap.person_id
-- important, must be app eff date to get correct data
AND asg.payroll_id = ppa.payroll_id
AND g_period_end_date BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT DISTINCT
pbc.full_balance_name bal_name
, pbc.code bal_code
, pbc.balance_type_id bal_id
FROM pay_za_irp5_bal_codes pbc
, pay_run_result_values prrv
, pay_run_results prr
, pay_balance_feeds_f feed
, pay_payroll_actions ppa
, pay_assignment_actions paa
WHERE prrv.input_value_id = feed.input_value_id
AND prr.run_result_id = prrv.run_result_id
AND paa.assignment_action_id <= p_asg_action_id
AND prr.assignment_action_id = paa.assignment_action_id
AND paa.assignment_id = p_asg_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R', 'I', 'B', 'Q', 'V')
AND ppa.effective_date >= (select start_date from per_time_periods ptp where ptp.time_period_id = p_start_period_id)
AND ppa.effective_date <= (select end_date from per_time_periods ptp where ptp.time_period_id = p_end_period_id)
AND pbc.balance_type_id = feed.balance_type_id
AND pbc.balance_sequence = 1;