[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete next change for element type with
input values. Amended del_bf_input_value to
include this message.
11-Jun-01 M.Reid 115.6 Bug 1783351. Removed suppression of BG id
for CBO to allow the view to be merged.
30-JUL-02 RThirlby 115.7 Bug 2430399 Added p_mode parameter to
ins_bf_bal_class, so can be called from
hr_legislation, and not raise an error in
ins_bal_feed, if the feed already exists.
05-AUG-2002 RThirlby 115.8 Removed development debug statements.
31-Oct-02 A.Logue 115.9 Performance fix in cursor
csr_bf_upd_del_sub_class_rule. Bug 2649208.
10-Dec-02 A.Logue 115.10 Performance fix to cursor csr_bal_feed
in bf_chk_proc_run_results. Bug 2668076.
29-JAN-03 RThirlby 115.11 Bug 2767760 - Issues with creation of feeds
due to translated pay value input value name
being used, instead of the base table name.
Search for this bug no. for further details.
NB - also fixed a compilation error caused
by a change in version 115.10.
14-APR-2003 RThirlby 115.12 Bug 2888183. Added p_mode parameter to
ins_bf_sub_class_rule and ins_bf_pay_value,
so they can be called from
hr_legislation_elements, and not raise an
error in ins_bal_feed, if the feed already
exists.
24-FEB-2005 M.Reid 115.13 Bug 4187885. Added no unnest hint as 10g
workaround for ST bug 3120429
22-NOV-2005 A.Logue 115.14 Rewrote csr_proc_run_result due to CBO
choosing non-optimal plan
29-DEC-2005 A.Logue 115.15 Rewrote csr_proc_run_result due to CBO
choosing non-optimal plan. Bug 4914604.
17-JAN-2006 A.Logue 115.16 Reimplemented csr_proc_run_result for
balance feed creation for performance.
Bug 4958471.
15-FEB-2006 A.Logue 115.17 Further performance enhancments to
feed insertion code.
Bug 5040393.
10-AUG-2006 A.Logue 115.18 Disable changed balance value check in
bf_chk_proc_run_results for new balance feeds
if CHANGED_BALANCE_VALUE_CHECK
pay_action_parameter set to N. Bug 5442547.
05-SEP-2011 vpallapo 115.19 Disable changed balance value check in
bf_chk_proc_run_results for the updated feeds
if CHANGED_BALANCE_VALUE_CHECK
pay_action_parameter set to N. Bug 12770789.
07-DEC-2012 pparate 115.20 Applied change to disable balance value check
based on parameter CHANGED_BALANCE_VALUE_CHECK
for SUB_CLASSIFICATION_RULE mode.
Bug 14780261.
============================================================================*/
--
------------------------------------------------------------------------------
-- NAME --
-- hr_balance_feeds.lock_balance_type --
-- --
-- DESCRIPTION --
-- Takes a row level lock out on a specified balance type. --
------------------------------------------------------------------------------
--
procedure lock_balance_type
(
p_balance_type_id number
) is
--
cursor csr_lock_balance
(
p_balance_type_id number
) is
select bt.balance_type_id
from pay_balance_types bt
where bt.balance_type_id = p_balance_type_id
for update;
select bcl.classification_id
from pay_balance_classifications bcl
where bcl.balance_type_id = p_balance_type_id;
select bf.balance_feed_id
from pay_balance_feeds_f bf,
pay_input_values_f inv,
pay_element_types_f elt,
pay_element_classifications ec
where bf.balance_type_id = p_balance_type_id
and bf.input_value_id = inv.input_value_id
and inv.element_type_id = elt.element_type_id
and elt.classification_id = ec.classification_id
and nvl(ec.balance_initialization_flag,'N') = 'N'
and not exists
(select null
from pay_balance_classifications bc
where bc.balance_type_id = bf.balance_type_id);
select hl.meaning
into v_pay_value_name
from hr_lookups hl
where hl.lookup_type = 'NAME_TRANSLATIONS'
and hl.lookup_code = 'PAY VALUE';
select iv.input_value_id
from pay_input_values_f iv
where iv.input_value_id = p_input_value_id
and iv.name = p_pay_value_name;
select ecl.parent_classification_id
into v_parent_classification_id
from pay_element_classifications ecl
where ecl.classification_id = p_classification_id;
select min(bf.effective_start_date)
into v_next_bal_feed_start_date
from pay_balance_feeds_f bf
where bf.balance_type_id = p_balance_type_id
and bf.input_value_id = p_input_value_id
and bf.effective_end_date >= p_session_date
and bf.balance_feed_id <> nvl(p_balance_feed_id,0);
select max(iv.effective_end_date)
into v_max_inp_val_end_date
from pay_input_values_f iv
where iv.input_value_id = p_input_value_id;
insert into pay_balance_feeds_f
(balance_feed_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
balance_type_id,
input_value_id,
scale,
legislation_subgroup,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
select
pay_balance_feeds_s.nextval,
p_effective_start_date,
p_effective_end_date,
p_business_group_id,
p_legislation_code,
p_balance_type_id,
p_input_value_id,
p_scale,
p_legislation_subgroup,
trunc(sysdate),
0,
0,
0,
trunc(sysdate)
from sys.dual
where not exists
(select null
from pay_balance_feeds_f bf
where bf.input_value_id = p_input_value_id
and bf.balance_type_id = p_balance_type_id
and p_effective_start_date <= bf.effective_end_date
and p_effective_end_date >= bf.effective_start_date
and nvl(legislation_code, nvl(p_legislation_code,'~~nvl~~')) = nvl(p_legislation_code,'~~nvl~~')
and nvl(business_group_id,nvl(p_business_group_id,-1)) = nvl(p_business_group_id,-1));
select bt.balance_type_id,
iv.input_value_id,
bc.scale,
min(iv.effective_start_date) effective_start_date,
max(iv.effective_end_date) effective_end_date,
nvl(iv.business_group_id,bt.business_group_id) business_group_id,
decode(nvl(iv.business_group_id,bt.business_group_id),
null, nvl(iv.legislation_code,bt.legislation_code),
null) legislation_code
,bt.balance_name
,ec.classification_name
,et.element_name
from pay_input_values_f iv,
pay_element_types_f et,
pay_element_classifications ec,
pay_balance_classifications bc,
pay_balance_types bt,
per_business_groups_perf ivbg,
per_business_groups_perf btbg
where bc.balance_classification_id = p_balance_classification_id
and ec.classification_id = bc.classification_id
and ec.parent_classification_id is null
and bt.balance_type_id = bc.balance_type_id
and et.classification_id = ec.classification_id
and iv.element_type_id = et.element_type_id
and iv.name = p_pay_value_name
and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
and (bt.balance_uom <> 'M' or
(bt.balance_uom = 'M' and
bt.currency_code = et.output_currency_code))
and iv.effective_start_date between et.effective_start_date
and et.effective_end_date
/*
Join are to get the legislation code for the business groups of the
balance and input value being matched.
*/
and iv.business_group_id = ivbg.business_group_id (+)
and bt.business_group_id = btbg.business_group_id (+)
/*
Match on business group OR
Business groups do not match so try to match on legislation NB.
need to protect against the case where the business groups are
different but share the same legislation code.
*/
and (
nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
(
nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
)
)
group by bt.balance_type_id,
iv.input_value_id,
bc.scale,
nvl(iv.business_group_id,bt.business_group_id),
decode(nvl(iv.business_group_id,bt.business_group_id),
null, nvl(iv.legislation_code,bt.legislation_code),
null)
,bt.balance_name
,ec.classification_name
,et.element_name
union
select bt.balance_type_id,
iv.input_value_id,
bc.scale,
scr.effective_start_date,
scr.effective_end_date,
nvl(iv.business_group_id,
nvl(scr.business_group_id,
bt.business_group_id)) business_group_id,
decode(nvl(iv.business_group_id,
nvl(scr.business_group_id,
bt.business_group_id)),
null, nvl(iv.legislation_code,
nvl(scr.legislation_code,
bt.legislation_code)),
null) legislation_code
,bt.balance_name
,ec.classification_name
,et.element_name
from pay_sub_classification_rules_f scr,
pay_element_types_f et,
pay_input_values_f iv,
pay_element_classifications ec,
pay_balance_classifications bc,
pay_balance_types bt,
per_business_groups_perf ivbg,
per_business_groups_perf btbg
where bc.balance_classification_id = p_balance_classification_id
and ec.classification_id = bc.classification_id
and ec.parent_classification_id is not null
and bt.balance_type_id = bc.balance_type_id
and scr.classification_id = ec.classification_id
and et.element_type_id = scr.element_type_id
and iv.element_type_id = et.element_type_id
and iv.name = p_pay_value_name
and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
and (bt.balance_uom <> 'M' or
(bt.balance_uom = 'M' and
bt.currency_code = et.output_currency_code))
and scr.effective_start_date between et.effective_start_date
and et.effective_end_date
and scr.effective_start_date between iv.effective_start_date
and iv.effective_end_date
/*
Join are to get the legislation code for the business groups of the
balance and input value being matched.
*/
and iv.business_group_id = ivbg.business_group_id (+)
and bt.business_group_id = btbg.business_group_id (+)
/*
Match on business group OR
Business groups do not match so try to match on legislation NB.
need to protect against the case where the business groups are
different but share the same legislation code.
*/
and (
nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
(
nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
)
);
select bf.rowid row_id
from pay_balance_feeds_f bf,
pay_balance_classifications bc,
pay_balance_types bt,
pay_element_classifications ec
where bc.balance_classification_id = p_balance_classification_id
and bt.balance_type_id = bc.balance_type_id
and bf.balance_type_id = bt.balance_type_id
and ec.classification_id = bc.classification_id
and ((ec.parent_classification_id is null and
exists
(select null
from pay_element_types_f et,
pay_input_values_f iv
where iv.input_value_id = bf.input_value_id
and et.element_type_id = iv.element_type_id
and et.classification_id = bc.classification_id))
or (ec.parent_classification_id is not null and
exists
(select null
from pay_sub_classification_rules_f scr,
pay_input_values_f iv
where iv.input_value_id = bf.input_value_id
and scr.element_type_id = iv.element_type_id
and scr.classification_id = bc.classification_id)))
for update;
if p_mode = 'UPDATE' then
--
update pay_balance_feeds_f bf
set bf.scale = p_scale
where bf.rowid = v_bf_rec.row_id;
elsif p_mode = 'DELETE' then
--
delete from pay_balance_feeds_f bf
where bf.rowid = v_bf_rec.row_id;
select bt.balance_type_id,
bc.scale,
iv.effective_start_date,
iv.effective_end_date,
nvl(iv.business_group_id,bt.business_group_id) business_group_id,
decode(nvl(iv.business_group_id,bt.business_group_id),
null, nvl(iv.legislation_code,bt.legislation_code),
null) legislation_code
from pay_balance_types bt,
pay_balance_classifications bc,
pay_element_types_f et,
pay_input_values_f iv,
per_business_groups_perf ivbg,
per_business_groups_perf btbg
where iv.input_value_id = p_input_value_id
and et.element_type_id = iv.element_type_id
and bc.classification_id = et.classification_id
and bt.balance_type_id = bc.balance_type_id
and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
and (bt.balance_uom <> 'M' or
(bt.balance_uom = 'M' and
bt.currency_code = et.output_currency_code))
and iv.effective_start_date between et.effective_start_date
and et.effective_end_date
/*
Join are to get the legislation code for the business groups of the
balance and input value being matched.
*/
and iv.business_group_id = ivbg.business_group_id (+)
and bt.business_group_id = btbg.business_group_id (+)
/*
Match on business group OR
Business groups do not match so try to match on legislation NB.
need to protect against the case where the business groups are
different but share the same legislation code.
*/
and (
nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
(
nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
)
)
for update of bt.balance_type_id;
select bt.balance_type_id,
bc.scale,
scr.effective_start_date,
scr.effective_end_date,
nvl(iv.business_group_id,
nvl(scr.business_group_id,
bt.business_group_id)) business_group_id,
decode(nvl(iv.business_group_id,
nvl(scr.business_group_id,
bt.business_group_id)),
null, nvl(iv.legislation_code,
nvl(scr.legislation_code,
bt.legislation_code)),
null) legislation_code
from pay_input_values_f iv,
pay_balance_classifications bc,
pay_balance_types bt,
pay_element_types_f et,
pay_sub_classification_rules_f scr,
per_business_groups_perf ivbg,
per_business_groups_perf btbg
where iv.input_value_id = p_input_value_id
and et.element_type_id = iv.element_type_id
and scr.element_type_id = et.element_type_id
and bc.classification_id = scr.classification_id
and bt.balance_type_id = bc.balance_type_id
and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
and (bt.balance_uom <> 'M' or
(bt.balance_uom = 'M' and
bt.currency_code = et.output_currency_code))
and iv.effective_start_date between et.effective_start_date
and et.effective_end_date
/*
Join are to get the legislation code for the business groups of the
balance and input value being matched.
*/
and iv.business_group_id = ivbg.business_group_id (+)
and bt.business_group_id = btbg.business_group_id (+)
/*
Match on business group OR
Business groups do not match so try to match on legislation NB.
need to protect against the case where the business groups are
different but share the same legislation code.
*/
and (
nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
(
nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
)
)
for update of bt.balance_type_id;
select bt.balance_type_id,
iv.input_value_id,
bc.scale,
scr.effective_start_date,
scr.effective_end_date,
nvl(iv.business_group_id,
nvl(scr.business_group_id,
bt.business_group_id)) business_group_id,
decode(nvl(iv.business_group_id,
nvl(scr.business_group_id,
bt.business_group_id)),
null, nvl(iv.legislation_code,
nvl(scr.legislation_code,
bt.legislation_code)),
null) legislation_code
from pay_sub_classification_rules_f scr,
pay_element_types_f et,
pay_input_values_f iv,
pay_balance_classifications bc,
pay_balance_types bt,
per_business_groups_perf ivbg,
per_business_groups_perf btbg
where scr.sub_classification_rule_id = p_sub_classification_rule_id
and bc.classification_id = scr.classification_id
and bt.balance_type_id = bc.balance_type_id
and et.element_type_id = scr.element_type_id
and iv.element_type_id = et.element_type_id
and iv.name = p_pay_value_name
and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
and (bt.balance_uom <> 'M' or
(bt.balance_uom = 'M' and
bt.currency_code = et.output_currency_code))
and scr.effective_start_date between et.effective_start_date
and et.effective_end_date
and scr.effective_start_date between iv.effective_start_date
and iv.effective_end_date
/*
Join are to get the legislation code for the business groups of the
balance and input value being matched.
*/
and iv.business_group_id = ivbg.business_group_id (+)
and bt.business_group_id = btbg.business_group_id (+)
/*
Match on business group OR
Business groups do not match so try to match on legislation NB.
need to protect against the case where the business groups are
different but share the same legislation code.
*/
and (
nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
(
nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
)
)
for update of bt.balance_type_id;
select bf.rowid row_id
from pay_balance_feeds_f bf,
pay_balance_types bt
where bf.input_value_id = p_input_value_id
and bt.balance_type_id = bf.balance_type_id
for update;
cursor csr_bal_feeds_delete
(
p_input_value_id number,
p_validation_start_date date
) is
select bf.rowid row_id
from pay_balance_feeds_f bf,
pay_balance_types bt
where bf.input_value_id = p_input_value_id
and bf.effective_start_date >= p_validation_start_date
and bt.balance_type_id = bf.balance_type_id
for update;
cursor csr_bal_feeds_update
(
p_input_value_id number,
p_validation_start_date date
) is
select bf.rowid row_id
from pay_balance_feeds_f bf,
pay_balance_types bt
where bf.input_value_id = p_input_value_id
and bf.effective_end_date >= p_validation_start_date
and bt.balance_type_id = bf.balance_type_id
for update;
select bf.rowid row_id
from pay_balance_feeds_f bf,
pay_balance_types bt
where bf.input_value_id = p_input_value_id
and bt.balance_type_id = bf.balance_type_id
and bf.effective_end_date =
(select max(bf2.effective_end_date)
from pay_balance_feeds_f bf2
where bf2.balance_feed_id = bf.balance_feed_id)
and exists
(select null
from pay_balance_classifications bc
where bc.balance_type_id = bf.balance_type_id)
for update;
delete from pay_balance_feeds_f bf
where bf.rowid = v_bf_rec.row_id;
elsif p_dt_mode = 'DELETE' then
--
for v_bf_rec in csr_bal_feeds_delete
(p_input_value_id,
p_validation_start_date) loop
--
delete from pay_balance_feeds_f bf
where bf.rowid = v_bf_rec.row_id;
for v_bf_rec in csr_bal_feeds_update
(p_input_value_id,
p_validation_start_date) loop
--
update pay_balance_feeds_f bf
set bf.effective_end_date = p_validation_start_date - 1
where bf.rowid = v_bf_rec.row_id;
elsif (p_dt_mode = 'DELETE_NEXT_CHANGE' and
p_validation_end_date = c_eot)
or p_dt_mode = 'FUTURE_CHANGE' then
--
-- See if input value being extended is the Pay Value NB. automatic
-- balance feeds are only created for the Pay Value. Extend the balance
-- feed to the new end date of the input value.
if hr_balance_feeds.is_pay_value(p_input_value_id) then
--
for v_bf_rec in csr_bal_feeds_extend
(p_input_value_id) loop
--
update pay_balance_feeds_f bf
set bf.effective_end_date = p_validation_end_date
where bf.rowid = v_bf_rec.row_id;
if p_dt_mode = 'DELETE_NEXT_CHANGE' then
hr_utility.set_message(801,'HR_72033_CANNOT_DNC_RECORD');
select bf.rowid row_id
from pay_sub_classification_rules_f scr,
pay_input_values_f iv,
pay_balance_feeds_f bf,
pay_balance_classifications bc,
pay_balance_types bt
where scr.sub_classification_rule_id = p_sub_classification_rule_id
and iv.element_type_id = scr.element_type_id
and iv.name = p_pay_value_name
and bc.classification_id = scr.classification_id
and bt.balance_type_id = bc.balance_type_id
and bf.balance_type_id = bt.balance_type_id
and bf.input_value_id = iv.input_value_id
and bf.effective_start_date = scr.effective_start_date
and bf.effective_end_date = scr.effective_end_date
and scr.effective_start_date between iv.effective_start_date
and iv.effective_end_date
for update;
delete from pay_balance_feeds_f bf
where bf.rowid = v_bf_rec.row_id;
elsif p_dt_mode = 'DELETE' then
--
for v_bf_rec in csr_bal_feeds_sub_class_rule
(p_sub_classification_rule_id,
v_pay_value_name) loop
--
update pay_balance_feeds_f bf
set bf.effective_end_date = p_validation_start_date - 1
where bf.rowid = v_bf_rec.row_id;
elsif p_dt_mode in ('DELETE_NEXT_CHANGE','FUTURE_CHANGE') then
--
for v_bf_rec in csr_bal_feeds_sub_class_rule
(p_sub_classification_rule_id,
v_pay_value_name) loop
--
update pay_balance_feeds_f bf
set bf.effective_end_date = p_validation_end_date
where bf.rowid = v_bf_rec.row_id;
select iv.input_value_id,
min(iv.effective_start_date) effective_start_date,
max(iv.effective_end_date) effective_end_date
from pay_input_values_f iv,
pay_element_types_f et,
pay_element_classifications ec,
pay_balance_types bt,
per_business_groups_perf ivbg,
per_business_groups_perf btbg
where bt.balance_type_id = p_balance_type_id
and ec.classification_id = p_classification_id
and ec.parent_classification_id is null
and et.classification_id = ec.classification_id
and iv.element_type_id = et.element_type_id
and iv.name = p_pay_value_name
and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
and (bt.balance_uom <> 'M' or
(bt.balance_uom = 'M' and
bt.currency_code = et.output_currency_code))
and iv.effective_start_date between et.effective_start_date
and et.effective_end_date
/*
Join are to get the legislation code for the business groups of the
balance and input value being matched.
*/
and iv.business_group_id = ivbg.business_group_id (+)
and bt.business_group_id = btbg.business_group_id (+)
/*
Match on business group OR
Business groups do not match so try to match on legislation NB.
need to protect against the case where the business groups are
different but share the same legislation code.
*/
and (
nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
(
nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
)
)
group by iv.input_value_id
union
select iv.input_value_id,
scr.effective_start_date,
scr.effective_end_date
from pay_sub_classification_rules_f scr,
pay_element_types_f et,
pay_input_values_f iv,
pay_element_classifications ec,
pay_balance_types bt,
per_business_groups_perf ivbg,
per_business_groups_perf btbg
where bt.balance_type_id = p_balance_type_id
and ec.classification_id = p_classification_id
and ec.parent_classification_id is not null
and scr.classification_id = ec.classification_id
and et.element_type_id = scr.element_type_id
and iv.element_type_id = et.element_type_id
and iv.name = p_pay_value_name
and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
and (bt.balance_uom <> 'M' or
(bt.balance_uom = 'M' and
bt.currency_code = et.output_currency_code))
and scr.effective_start_date between et.effective_start_date
and et.effective_end_date
and scr.effective_start_date between iv.effective_start_date
and iv.effective_end_date
/*
Join are to get the legislation code for the business groups of the
balance and input value being matched.
*/
and iv.business_group_id = ivbg.business_group_id (+)
and bt.business_group_id = btbg.business_group_id (+)
/*
Match on business group OR
Business groups do not match so try to match on legislation NB.
need to protect against the case where the business groups are
different but share the same legislation code.
*/
and (
nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
(
nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
)
);
select bf.balance_feed_id
from pay_balance_feeds_f bf,
pay_balance_classifications bc,
pay_element_classifications ec
where bc.balance_classification_id = p_balance_classification_id
and bf.balance_type_id = bc.balance_type_id
and ec.classification_id = bc.classification_id
and ((ec.parent_classification_id is null and
exists
(select null
from pay_element_types_f et,
pay_input_values_f iv
where iv.input_value_id = bf.input_value_id
and et.element_type_id = iv.element_type_id
and et.classification_id = bc.classification_id))
or (ec.parent_classification_id is not null and
exists
(select null
from pay_sub_classification_rules_f scr,
pay_input_values_f iv
where iv.input_value_id = bf.input_value_id
and scr.element_type_id = iv.element_type_id
and scr.classification_id = bc.classification_id)))
and exists
(select null
from pay_run_results rr,
pay_run_result_values rrv
where rrv.input_value_id = bf.input_value_id
and rr.run_result_id = rrv.run_result_id
and rr.status like 'P%');
select 1
from dual
where exists
(select /*+ ORDERED USE_NL(rrv rr aa pa)
INDEX(rrv PAY_RUN_RESULT_VALUES_PK) */ null
from pay_run_result_values rrv,
pay_run_results rr,
pay_assignment_actions aa,
pay_payroll_actions pa
where rrv.input_value_id = p_input_value_id
and rr.run_result_id = rrv.run_result_id
and rr.status like 'P%'
and aa.assignment_action_id = rr.assignment_action_id
and pa.payroll_action_id = aa.payroll_action_id
and pa.effective_date between p_validation_start_date
and p_validation_end_date);
select iv.input_value_id,
scr.effective_start_date,
scr.effective_end_date
from pay_sub_classification_rules_f scr,
pay_element_types_f et,
pay_input_values_f iv,
pay_balance_classifications bc,
pay_balance_types bt,
per_business_groups_perf ivbg,
per_business_groups_perf btbg
where bc.classification_id = p_classification_id
and bt.balance_type_id = bc.balance_type_id
and et.element_type_id = scr.element_type_id
and iv.element_type_id = et.element_type_id
and iv.name = p_pay_value_name
and substr(iv.uom,1,1) = substr(bt.balance_uom,1,1)
and (bt.balance_uom <> 'M' or
(bt.balance_uom = 'M' and
bt.currency_code = et.output_currency_code))
and scr.effective_start_date between et.effective_start_date
and et.effective_end_date
and scr.effective_start_date between iv.effective_start_date
and iv.effective_end_date
/*
Join are to get the legislation code for the business groups of the
balance and input value being matched.
*/
and iv.business_group_id = ivbg.business_group_id (+)
and bt.business_group_id = btbg.business_group_id (+)
/*
Match on business group OR
Business groups do not match so try to match on legislation NB.
need to protect against the case where the business groups are
different but share the same legislation code.
*/
and (
nvl(ivbg.business_group_id,-1) = nvl(btbg.business_group_id,-2) or
(
nvl(iv.legislation_code,nvl(ivbg.legislation_code,'GENERIC')) =
nvl(bt.legislation_code,nvl(btbg.legislation_code,'GENERIC')) and
nvl(iv.business_group_id, nvl(bt.business_group_id, -1)) =
nvl(bt.business_group_id, nvl(iv.business_group_id, -1))
)
);
select bf.balance_feed_id
from pay_sub_classification_rules_f scr,
pay_input_values_f iv,
pay_balance_feeds_f bf,
pay_balance_classifications bc
where scr.sub_classification_rule_id = p_sub_classification_rule_id
and iv.element_type_id = scr.element_type_id
and bc.classification_id = scr.classification_id
and bf.balance_type_id = bc.balance_type_id
and bf.input_value_id = iv.input_value_id
and bf.effective_start_date = scr.effective_start_date
and bf.effective_end_date = scr.effective_end_date
and scr.effective_start_date between iv.effective_start_date
and iv.effective_end_date
and exists
(select /*+ ORDERED*/
null
from pay_run_result_values rrv,
pay_run_results rr,
pay_assignment_actions aa,
pay_payroll_actions pa
where rrv.input_value_id = bf.input_value_id
and rr.run_result_id = rrv.run_result_id
and rr.status like 'P%'
and aa.assignment_action_id = rr.assignment_action_id
and pa.payroll_action_id = aa.payroll_action_id
and pa.effective_date between p_validation_start_date
and p_validation_end_date);
select 1
from dual
where
exists (select /*+ FIRST_ROWS ORDERED
USE_NL(rrv rr aa pa)
INDEX(rrv PAY_RUN_RESULT_VALUES_PK)
INDEX(rr PAY_RUN_RESULTS_PK)
INDEX(aa PAY_ASSIGNMENT_ACTIONS_PK)
INDEX(pa PAY_PAYROLL_ACTIONS_PK)
*/ 1
from pay_run_result_values rrv,
pay_run_results rr,
pay_assignment_actions aa,
pay_payroll_actions pa
where rrv.input_value_id = p_input_value_id
and rr.run_result_id = rrv.run_result_id
and rr.status like 'P%'
and aa.assignment_action_id = rr.assignment_action_id
and pa.payroll_action_id = aa.payroll_action_id
and pa.effective_date between p_validation_start_date
and p_validation_end_date);
select /*+ INDEX(pa pay_payroll_actions_n5)*/
payroll_action_id
from pay_payroll_actions pa
where pa.effective_date between p_validation_start_date
and p_validation_end_date
and action_type in ('R', 'Q', 'B', 'I', 'V')
order by payroll_action_id desc;
select 1
from dual
where
exists (select 1
from pay_run_result_values rrv
where rrv.input_value_id = p_input_value_id);
select 1
from dual
where
exists (select /*+ FIRST_ROWS ORDERED
USE_NL(rr aa rrv)
INDEX(rrv PAY_RUN_RESULT_VALUES_N50)
INDEX(rr PAY_RUN_RESULTS_N50)
INDEX(aa PAY_ASSIGNMENT_ACTIONS_N50)
*/ 1
from pay_assignment_actions aa,
pay_run_results rr,
pay_run_result_values rrv
where rrv.input_value_id = p_input_value_id
and rr.run_result_id = rrv.run_result_id
and rr.status like 'P%'
and aa.assignment_action_id = rr.assignment_action_id
and aa.payroll_action_id = p_payroll_action_id);
select parameter_value
into v_check_value
from pay_action_parameters pap
where pap.parameter_name = 'CHANGED_BALANCE_VALUE_CHECK';
p_dml_mode = 'UPDATE_DELETE' and
p_balance_classification_id is not null) then
--
open csr_bf_upd_del_bal_class
(p_balance_classification_id);
p_dml_mode = 'UPDATE_DELETE' and
p_balance_feed_id is not null) then
--
/* Bug 12770789, starts here*/
if v_check_value = 'N' then
v_rrv_found := FALSE;
select distinct input_value_id
into v_iv_id
from pay_balance_feeds_f
where balance_feed_id = p_balance_feed_id;
p_dml_mode = 'UPDATE_DELETE' and
p_sub_classification_rule_id is not null) then
--
/* Bug 14780261, starts here*/
if v_check_value = 'N' then
v_rrv_found := FALSE;
p_dml_mode = 'INSERT' and
p_classification_id is not null) then
--
/* Bug 14780261, starts here*/
if v_check_value = 'N' then
v_rrv_found := FALSE;
p_dml_mode = 'INSERT' and
p_classification_id is not null) then
--
for v_iv_rec in csr_bf_ins_bal_class
(p_balance_type_id,
p_classification_id,
v_pay_value_name) loop
--
open csr_proc_run_result
(v_iv_rec.input_value_id,
v_iv_rec.effective_start_date,
v_iv_rec.effective_end_date);
p_dml_mode = 'INSERT' and
p_input_value_id is not null) then
--
--
-- Check if this warning check has been disabled
--
/* Bug 12770789, This check is moved to the starting of this function */
/*
begin
select parameter_value
into v_check_value
from pay_action_parameters pap
where pap.parameter_name = 'CHANGED_BALANCE_VALUE_CHECK';