[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
115.1 JARTHURT 05-JAN-2001 Updates to add required Canadian balance
dimensions and comply with new dynamic
SQL calls from pay_balance_upload.
115.2 JARTHURT 15-JAN-2001 Corrected balance dimension list and
month truncation.
115.3 JARTHURT 22-JAN-2001 Corrected type of jurisdiction_code
115.3 PGANGULY 27-MAR-2003 Added the following dimensions in the
is_supported, expiry_date functions:
Assignment within Reporting Unit Year to
Date/Month. Fixed Bug# 2859270. Added
dbdrv, set verify off for GSCC.
*/
--
-- Date constants.
--
START_OF_TIME constant date := to_date('01/01/0001','DD/MM/YYYY');
select nvl(PTP.start_date, END_OF_TIME)
from per_time_periods PTP
,per_assignments_f ASS
where ASS.assignment_id = p_assignment_id
and p_upload_date between ASS.effective_start_date
and ASS.effective_end_date
and PTP.payroll_id = ASS.payroll_id
and p_upload_date between PTP.start_date
and PTP.end_date;
select nvl(min(ASG.effective_start_date), END_OF_TIME)
from per_all_assignments_f ASG
where ASG.assignment_id = p_assignment_id
and ASG.effective_start_date <= p_upload_date;
select nvl(greatest(min(ASS.effective_start_date), p_expiry_date),
END_OF_TIME)
from per_all_assignments_f ASS
where ASS.assignment_id = p_assignment_id
and ASS.effective_start_date <= p_upload_date
and ASS.effective_end_date >= p_expiry_date;
select BT.balance_type_id
from pay_balance_types BT
where BT.balance_type_id = p_balance_type_id
--
-- JURISDICTION_CODE context NB. if the jurisdiction code is
-- used then only those adjustments which are for the same
-- jurisdiction code can be included.
--
and ((p_jurisdiction_code is null) or
(p_jurisdiction_code is not null and
substr(p_bal_adj_jurisdiction_code, 1, BT.jurisdiction_level) =
substr(p_jurisdiction_code , 1, BT.jurisdiction_level)))
--
-- TAX_UNIT_ID context NB. if the tax unit is used then only those
-- adjustments which are for the same tax unit can be included.
--
and nvl(p_tax_unit_id, nvl(p_bal_adj_tax_unit_id, -1)) =
nvl(p_bal_adj_tax_unit_id, -1)
--
-- ORIGINAL_ENTRY_ID context NB. this context controls the expiry
-- date of the dimension in the same way as the QTD dimension. Any
-- existing balance adjustments that lie between the upload date
-- and the expiry date are all included. There is no special
-- criteria that has to be met.
--
and 1 = 1;
select htuv.tax_unit_id
from pay_balance_batch_lines pbbl
,hr_tax_units_v htuv
where pbbl.batch_line_id = p_batch_line_id
and pbbl.tax_unit_id = htuv.tax_unit_id
and pbbl.tax_unit_id is not null
union all
select htuv.tax_unit_id
from pay_balance_batch_lines pbbl
,hr_tax_units_v htuv
where pbbl.batch_line_id = p_batch_line_id
and upper(pbbl.gre_name) = upper(htuv.name)
and pbbl.tax_unit_id is null;
select prov.province_abbrev
from pay_balance_batch_lines pbbl
,pay_ca_provinces_v prov
where pbbl.batch_line_id = p_batch_line_id
and pbbl.jurisdiction_code = prov.province_abbrev
and pbbl.jurisdiction_code is not null;
select tax_unit_id
,jurisdiction_code
,original_entry_id
from pay_temp_balance_adjustments
where batch_line_id = p_test_batch_line_id;