[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
balance_type_id select in defined_balance_id
function.
smrobins 27-FEB-2002 - Added get_uk_term_dates. Called by HREMEA
to default the last standard process and
final process dates.
smrobins 01-MAR-2002 - Change to get_uk_term_dates to only return
a value for final close, which has been
pushed out nocopy for end date of period
regular payment date resides in. Change for
Positive Offsets.
rmakhija 115.4 01-MAY-2002 - Changed context and database items for tax
details
rmakhija 115.5 05-JUL-2002 - Changed get_database_items procedure to
get statutory details from run result
values before fetching them from DBIs
rmakhija 115.6 08-JUL-2002 - Changed DBI names for Previous Pay and Tax
gbutler 115.7 27-JAN-2003 - nocopy and gscc fixes
amills 115.8 21-JUL-2003 - Agg PAYE changes.
amills 115.9 02-MAR-2004 - 3473274. changed get_database_items and
get_balance_items to handle NDFs.
amills 115.10 02-MAR-2004 - Added nocopy hints.
npershad 115.11 14-OCT-2005 - 4428406. Removed reference to redundant index
PAY_ASSIGNMENT_ACTIONS_N1 used in hints.
rmakhija 115.12 07-NOV-2006 - 5144323, replaced PER_TD_YTD dimension with
PER_TD_CPE_YTD
*/
/* Constants */
-- DataBase Items
-- these are the database items used for the values displayed
--
G_TAX_REFNO_ITEM varchar2(30) := 'SCL_PAY_GB_TAX_REFERENCE';
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = 'PAYE Details'
AND c_effective_date BETWEEN effective_start_date
AND effective_end_date;
select scl.segment1
from per_all_assignments_f paf,
pay_all_payrolls_f ppf,
hr_soft_coding_keyflex scl
where paf.assignment_id = c_assignment_id
and paf.payroll_id = ppf.payroll_id
and scl.soft_coding_keyflex_id = ppf.soft_coding_keyflex_id
and c_effective_date between
paf.effective_start_date and paf.effective_end_date
and c_effective_date between
ppf.effective_start_date and ppf.effective_end_date;
SELECT max(decode(iv.name,'Tax Code',screen_entry_value)) tax_code,
max(decode(iv.name,'Tax Basis',screen_entry_value)) tax_basis,
max(decode(iv.name,'Pay Previous',screen_entry_value))
pay_previous,
max(decode(iv.name,'Tax Previous',screen_entry_value))
tax_previous
FROM pay_element_entries_f e,
pay_element_entry_values_f v,
pay_input_values_f iv,
pay_element_links_f link
WHERE e.assignment_id = c_assignment_id
AND link.element_type_id = c_paye_id
AND e.element_link_id = link.element_link_id
AND e.element_entry_id = v.element_entry_id
AND iv.input_value_id = v.input_value_id
AND c_effective_date
BETWEEN link.effective_start_date AND link.effective_end_date
AND c_effective_date
BETWEEN e.effective_start_date AND e.effective_end_date
AND c_effective_date
BETWEEN iv.effective_start_date AND iv.effective_end_date
AND c_effective_date
BETWEEN v.effective_start_date AND v.effective_end_date;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = 'PAYE';
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = l_paye_element_id
AND name = p_ipv_name;
SELECT result_value
FROM pay_run_result_values
WHERE run_result_id = l_max_run_result_id
AND input_value_id = p_ipv_id;
SELECT /*+ ORDERED INDEX (assact2 PAY_ASSIGNMENT_ACTIONS_N51,
pact PAY_PAYROLL_ACTIONS_PK,
r2 PAY_RUN_RESULTS_N50)
USE_NL(assact2, pact, r2) */
to_number(substr(max(lpad(assact2.action_sequence,15,'0')||r2.source_type||
r2.run_result_id),17))
FROM pay_assignment_actions assact2,
pay_payroll_actions pact,
pay_run_results r2
WHERE assact2.assignment_id = p_assignment_id
AND r2.element_type_id+0 = l_paye_element_id
AND r2.assignment_action_id = assact2.assignment_action_id
AND r2.status IN ('P', 'PA')
AND pact.payroll_action_id = assact2.payroll_action_id
AND pact.action_type IN ( 'Q','R','B','I')
AND assact2.action_status = 'C'
AND pact.effective_date <= to_date(p_date_earned,'YYYY/MM/DD')
AND NOT EXISTS(
SELECT '1'
FROM pay_action_interlocks pai,
pay_assignment_actions assact3,
pay_payroll_actions pact3
WHERE pai.locked_action_id = assact2.assignment_action_id
AND pai.locking_action_id = assact3.assignment_action_id
AND pact3.payroll_action_id = assact3.payroll_action_id
AND pact3.action_type = 'V'
AND assact3.action_status = 'C');
select defined_balance_id
from pay_defined_balances
--
where balance_type_id = (select balance_type_id
from pay_balance_types
where upper(balance_name) = p_balance_type
and legislation_code = 'GB')
--
and balance_dimension_id = (select balance_dimension_id
from pay_balance_dimensions
where upper(database_item_suffix) =
p_dimension_suffix);
select ptp2.end_date regular_payment_end_date
from per_time_periods ptp1,
per_time_periods ptp2
where p_act_term_date between ptp1.start_date and ptp1.end_date
and ptp1.payroll_id IN (select pa.payroll_id
from per_assignments pa
where pa.period_of_Service_id = p_period_of_service_id
and pa.person_id = p_person_id)
and ptp1.regular_payment_date between ptp2.start_date and ptp2.end_date
and ptp2.payroll_id IN (select pa2.payroll_id
from per_assignments pa2
where pa2.period_of_service_id = p_period_of_Service_id
and pa2.person_id = p_person_id);