[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select start_date
into l_return_date
from per_time_periods
where payroll_id = p_payroll_id
and p_effective_date between start_date
and end_date;
select min(effective_date)
into l_return_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = p_asg_action
and pai.locked_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id;
select end_date
into l_return_date
from per_time_periods
where payroll_id = p_payroll_id
and p_effective_date between start_date
and end_date;
select max(effective_date)
into l_return_date
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = p_asg_action
and pai.locked_action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id;
select context_name
from ff_contexts fc,
ff_route_context_usages frcu,
pay_balance_dimensions pbd,
pay_defined_balances pdb
where pdb.defined_balance_id = p_def_bal_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.route_id = frcu.route_id
and frcu.context_id = fc.context_id;
select pdr.run_dimension_id
, pdr.priority
, pdr.route_type
, null run_def_bal_id
, pdr.retrieval_column
from pay_dimension_routes pdr
, pay_defined_balances pdb -- balance defined balance
where pdb.balance_dimension_id = pdr.balance_dimension_id
and pdb.defined_balance_id = p_def_bal_id
and pdr.route_type = 'RR';
select pdr.run_dimension_id
, pdr.priority
, pdr.route_type
, rdb.defined_balance_id run_def_bal_id
, pdr.retrieval_column
from pay_dimension_routes pdr
, pay_defined_balances pdb -- balance defined balance
, pay_defined_balances rdb -- run defined balance
where pdb.balance_dimension_id = pdr.balance_dimension_id
and pdb.defined_balance_id = p_def_bal_id
and rdb.balance_type_id = pdb.balance_type_id
and rdb.balance_dimension_id = pdr.run_dimension_id
and pdr.route_type = 'SRB'
order by 2;
select DIM.dimension_type,
DIM.expiry_checking_code,
DIM.expiry_checking_level,
DIM.dimension_name,
nvl(TYP.jurisdiction_level, 0),
DEF.balance_dimension_id,
DEF.balance_type_id,
DIM.dimension_level,
DIM.period_type
into g_defbal_cache(p_defined_balance_id).dimension_type,
g_defbal_cache(p_defined_balance_id).expiry_check_code,
g_defbal_cache(p_defined_balance_id).expiry_check_lvl,
g_defbal_cache(p_defined_balance_id).dimension_name,
g_defbal_cache(p_defined_balance_id).jurisdiction_lvl,
g_defbal_cache(p_defined_balance_id).balance_dimension_id,
g_defbal_cache(p_defined_balance_id).balance_type_id,
g_defbal_cache(p_defined_balance_id).dimension_lvl,
g_defbal_cache(p_defined_balance_id).period_type
from pay_balance_dimensions DIM
, pay_defined_balances DEF
, pay_balance_types TYP
where DIM.balance_dimension_id = DEF.balance_dimension_id
and TYP.balance_type_id = DEF.balance_type_id
and DEF.defined_balance_id = p_defined_balance_id;
select pdr.balance_type_column,
pdr.decode_required,
pdr.route_id
into g_defbal_cache(p_defined_balance_id).balance_type_column,
g_defbal_cache(p_defined_balance_id).decode_required,
g_defbal_cache(p_defined_balance_id).dim_rou_rr_route_id
from pay_dimension_routes pdr
, pay_defined_balances pdb
where pdb.balance_dimension_id = pdr.balance_dimension_id
and pdb.defined_balance_id = p_defined_balance_id
and pdr.route_type = 'RR';
select 'Y'
from ff_route_context_usages frc
where frc.route_id = p_route_id
and frc.context_id = p_context_id;
Subsequent calls to this routine with the same context name will update the
relevant row in the pl/sql tables.
Since the context name is converted to upper case, the calling routine may
pass the context name in either case.
*/
procedure set_context
(
p_context_name in varchar2,
p_context_value in varchar2
) is
l_context_id ff_contexts.context_id%type;
select context_id,
data_type
into l_context_id,
l_context_type
from ff_contexts
where context_name = l_context_name;
g_context_mapping.delete;
select fc.context_name
into l_context_name
from ff_contexts fc,
pay_legislation_contexts plc
where plc.legislation_name = p_context_name
and plc.context_id = fc.context_id
and plc.legislation_code = p_legislation_code;
replaced. The text string is then updated with the definition text
information from ff_database_items (the part of the sql statement after
the 'SELECT' and before the 'FROM'). The complete text string is then
executed using dynamic pl/sql.
*/
function run_db_item
(
p_database_name in varchar2,
p_bus_group_id in number,
p_legislation_code in varchar2
) return varchar2 is
p_db_output varchar2(240);
select context_id,
sequence_no
from ff_route_context_usages
where route_id = p_route_id
order by sequence_no;
select RP.sequence_no,
replace(RPV.value, '''', null) value
from ff_route_parameter_values RPV
, ff_route_parameters RP
where RPV.user_entity_id = p_user_entity_id
and RP.route_id = p_route_id
and RPV.route_parameter_id = RP.route_parameter_id
order by RP.sequence_no;
select DBI.definition_text,
DBI.data_type,
ENT.user_entity_id,
ENT.creator_type,
ENT.notfound_allowed_flag,
RO.route_id,
RO.text,
RO.optimizer_hint
into l_definition_text,
l_data_type,
l_user_entity_id,
l_creator_type,
l_notfound_allowed_flag,
l_route_id,
l_text,
l_o_hint
from ff_database_items DBI
, ff_user_entities ENT
, ff_routes RO
where DBI.user_name = p_database_name
and DBI.user_entity_id = ENT.user_entity_id
and ( (ENT.legislation_code is null and ENT.business_group_id is null)
or (ENT.business_group_id is null
and p_legislation_code = ENT.legislation_code )
or ENT.business_group_id + 0 = p_bus_group_id
)
and ENT.route_id = RO.route_id;
select context_name
into l_context_name
from ff_contexts
where context_id = c1rec.context_id;
l_text := 'SELECT fnd_date.date_to_canonical(' ||
l_definition_text || ') FROM ' || l_text;
l_text := 'SELECT /*+ '||l_o_hint||
' */ fnd_date.date_to_canonical(' ||
l_definition_text || ') FROM ' || l_text;
select parameter_value
into g_low_volume
from pay_action_parameters
where parameter_name = 'LOW_VOLUME';
l_text := 'SELECT /*+ RULE*/ fnd_number.number_to_canonical(' ||
l_definition_text || ') FROM ' || l_text;
l_text := 'SELECT fnd_number.number_to_canonical(' ||
l_definition_text || ') FROM ' || l_text;
l_text := 'SELECT fnd_number.number_to_canonical(' ||
l_definition_text || ') FROM ' || l_text;
l_text := 'SELECT /*+ '||l_o_hint||
' */ fnd_number.number_to_canonical(' ||
l_definition_text || ') FROM ' || l_text;
l_text := 'SELECT ' || l_definition_text || ' FROM ' || l_text;
l_text := 'SELECT /*+ ' ||l_o_hint||' */'||
l_definition_text || ' FROM ' || l_text;
select context_name
into l_context_name
from ff_contexts
where context_id = route_contexts_cache.cxt_id(l_cxt_num);
l_select_clause varchar2(20000); -- large array for route text
l_select_component varchar2(20000); -- large array for route text
l_select_clause := 'TARGET.result_value';
l_select_clause := p_retrieval_column;
l_select_clause := 'nvl(sum(fnd_number.canonical_to_number('||l_select_clause||') * FEED.scale),0)';
l_select_clause := 'SELECT /*+ RULE*/ fnd_number.number_to_canonical(' ||
l_select_clause || ')';
l_select_clause := 'SELECT fnd_number.number_to_canonical(' ||
l_select_clause || ')';
l_select_clause := 'SELECT /*+ '||p_o_hint||
' */ fnd_number.number_to_canonical(' ||
l_select_clause || ')';
hr_utility.trace(l_select_clause);
l_select_component := ', '||p_balance_type_column;
hr_utility.trace(l_select_component);
l_select_clause := l_select_clause||l_select_component;
l_from_clause := l_select_clause||l_from_clause;
l_from_clause := l_select_clause||l_from_clause;
l_from_clause := l_select_clause||l_from_clause;
select context_id,
sequence_no
from ff_route_context_usages
where route_id = p_route_id
order by sequence_no;
select context_name
into l_context_name
from ff_contexts
where context_id = c1rec.context_id;
select context_id,
sequence_no
from ff_route_context_usages
where route_id = p_route_id
order by sequence_no;
select RO.text,
RO.optimizer_hint
into l_from_clause,
l_o_hint
from ff_routes RO
where RO.route_id = p_route_id;
select context_name
into l_context_name
from ff_contexts
where context_id = c1rec.context_id;
select parameter_value
into g_low_volume
from pay_action_parameters
where parameter_name = 'LOW_VOLUME';
select context_id,
sequence_no
from ff_route_context_usages
where route_id = p_route_id
order by sequence_no;
select RP.sequence_no,
RPV.value
from ff_route_parameter_values RPV
, ff_route_parameters RP
where RPV.user_entity_id = p_user_entity_id
and RP.route_id = p_route_id
and RPV.route_parameter_id = RP.route_parameter_id
order by RP.sequence_no;
select ENT.user_entity_id,
ENT.creator_type,
ENT.notfound_allowed_flag,
RO.route_id,
RO.text,
RO.optimizer_hint
into l_user_entity_id,
l_creator_type,
l_notfound_allowed_flag,
l_route_id,
l_text,
l_o_hint
from ff_user_entities ENT
, ff_routes RO
where ent.user_entity_name = p_user_name
and ( (ENT.legislation_code is null and ENT.business_group_id is null)
or (ENT.business_group_id is null
and p_legislation_code = ENT.legislation_code )
or ENT.business_group_id + 0 = p_business_group_id
)
and ENT.route_id = RO.route_id;
select context_name
into l_context_name
from ff_contexts
where context_id = c1rec.context_id;
l_text := 'SELECT NVL(SUM(prb.balance_value),0) FROM ' || l_text;
l_text := 'SELECT /*+ '||l_o_hint||
' */ NVL(SUM(prb.balance_value),0) FROM ' || l_text;
select context_name
into l_context_name
from ff_contexts
where context_id = route_contexts_cache.cxt_id(l_cxt_num);
select dbi.user_name
, nvl(ent.business_group_id, -1)
, nvl(ent.legislation_code, ' ')
from ff_database_items dbi
, ff_user_entities ent
where ent.creator_id = p_def_bal
and ent.creator_type = 'B'
and ent.user_entity_id = dbi.user_entity_id;
select fue.user_entity_name
, nvl(fue.business_group_id, -1)
, nvl(fue.legislation_code, ' ')
from ff_user_entities fue
, ff_user_entities fue_b
where fue.creator_id = p_def_bal
and fue_b.creator_type = 'B'
and fue.creator_id = fue_b.creator_id
and fue.creator_type = 'RB'
and fue.user_entity_name = fue_b.user_entity_name||'_'||to_char(p_prty)
;
select pdb.balance_type_id,
pdr.route_id,
pdr.balance_type_column,
pdr.retrieval_column,
nvl(pdr.decode_required, 'N'),
nvl(jurisdiction_level, 0)
into l_balance_type_id,
l_route_id,
l_balance_type_column,
l_retrieval_column,
l_decode_required_chr,
l_jur_lvl
from pay_defined_balances pdb,
pay_dimension_routes pdr,
pay_balance_types pbt
where pdb.defined_balance_id = p_def_bal_id
and pdb.balance_dimension_id = pdr.balance_dimension_id
and pbt.balance_type_id = pdb.balance_type_id
and pdr.priority = p_priority;
l_balance_list.delete;
select ppa.effective_date,
ppa.business_group_id,
ppa.payroll_id,
ppa.action_type
into g_retrieval_date,
g_bus_grp_id,
g_payroll_id,
g_action_type
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_asg_action_id
and paa.payroll_action_id = ppa.payroll_action_id;
select pbv.run_balance_status, pbv.balance_load_date
into l_run_bal_status, l_load_date
from pay_balance_validation pbv
where pbv.defined_balance_id = p_run_db_id
and pbv.business_group_id = l_bus_grp_id;
select nvl(pbd.period_type, 'NULL'),
pbd.start_date_code
into l_period_type,
l_start_date_code
from pay_balance_dimensions pbd,
pay_defined_balances pdb
where pdb.defined_balance_id = p_retreival_db_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
select pdr.run_dimension_id
, pdr.priority
, pdr.route_type
, rdb.defined_balance_id run_def_bal_id
from pay_dimension_routes pdr
, pay_defined_balances pdb -- balance defined balance
, pay_defined_balances rdb -- run defined balance
where pdb.balance_dimension_id = pdr.balance_dimension_id
and pdb.defined_balance_id = p_def_bal_id
and rdb.balance_type_id = pdb.balance_type_id
and rdb.balance_dimension_id = pdr.run_dimension_id
and pdr.route_type = 'SRB'
order by 2;
select PAY.payroll_action_id,
PAY.effective_date
into l_bal_owner_pay_action,
l_bal_owner_eff_date
from pay_assignment_actions ASG
, pay_payroll_actions PAY
where ASG.assignment_action_id = p_bal_owner_asg_action
and PAY.payroll_action_id = ASG.payroll_action_id;
select PAY.payroll_action_id,
PAY.effective_date
into l_payroll_action,
l_effective_date
from pay_assignment_actions ASG
, pay_payroll_actions PAY
where ASG.assignment_action_id = p_assignment_action_id
and PAY.payroll_action_id = ASG.payroll_action_id;
select ASGBAL.latest_balance_id,
ASGBAL.assignment_action_id,
ASGBAL.value,
ACT.action_sequence current_action_seq,
OWN_ACT.action_sequence owner_action_seq,
ASGBAL.expired_assignment_action_id,
ASGBAL.expired_value,
ASGBAL.prev_assignment_action_id,
ASGBAL.prev_balance_value
from pay_assignment_latest_balances ASGBAL
, pay_assignment_actions ACT
, pay_assignment_actions OWN_ACT
where ASGBAL.defined_balance_id = p_defined_balance_id
and ACT.assignment_action_id = p_assignment_action_id
and ASGBAL.assignment_id = ACT.assignment_id
and OWN_ACT.assignment_action_id = ASGBAL.assignment_action_id
--and ACT.action_sequence >= OWN_ACT.action_sequence
order by ASGBAL.latest_balance_id;
select PERBAL.latest_balance_id,
PERBAL.assignment_action_id,
PERBAL.value,
ACT.action_sequence current_action_seq,
OWN_ACT.action_sequence owner_action_seq,
PERBAL.expired_assignment_action_id,
PERBAL.expired_value,
PERBAL.prev_assignment_action_id,
PERBAL.prev_balance_value
from pay_person_latest_balances PERBAL
, per_all_assignments_f ASSIGN
, pay_assignment_actions ACT
, pay_payroll_actions PACT
, pay_assignment_actions OWN_ACT
where PERBAL.defined_balance_id = p_defined_balance_id
and ACT.assignment_action_id = p_assignment_action_id
and ASSIGN.assignment_id = ACT.assignment_id
and PERBAL.person_id = ASSIGN.person_id
and PACT.payroll_action_id = ACT.payroll_action_id
and PACT.effective_date between ASSIGN.effective_start_date
and ASSIGN.effective_end_date
and OWN_ACT.assignment_action_id = PERBAL.assignment_action_id
--and ACT.action_sequence >= OWN_ACT.action_sequence
order by PERBAL.latest_balance_id;
select CONVAL.context_id,
CONVAL.value,
CON.context_name
from pay_balance_context_values CONVAL
, ff_contexts CON
where latest_balance_id = p_latest_balance_id
and CON.context_id = CONVAL.context_id
order by 1;
select count(*)
into l_num_of_runs
from
pay_payroll_actions ppa2,
pay_assignment_actions paa2,
pay_assignment_actions paa
where paa.assignment_action_id = l_bal_expired_action
and paa2.assignment_id = paa.assignment_id
and ppa2.payroll_action_id = paa2.payroll_action_id
and ppa2.action_type in ('R', 'Q', 'B', 'V')
and paa2.action_sequence between paa.action_sequence
and l_bal_asg_act_seq;
select count(*)
into l_num_of_runs
from
pay_payroll_actions ppa2,
pay_assignment_actions paa2,
pay_assignment_actions paa
where paa.assignment_action_id = l_prev_action
and paa2.assignment_id = paa.assignment_id
and ppa2.payroll_action_id = paa2.payroll_action_id
and ppa2.action_type in ('R', 'Q', 'B', 'V')
and paa2.action_sequence between paa.action_sequence
and l_bal_asg_act_seq;
select plb.latest_balance_id,
plb.assignment_action_id,
plb.value,
plb.expiry_date,
ACT.action_sequence current_action_seq,
OWN_ACT.action_sequence owner_action_seq,
PACT.effective_date current_effective_date,
plb.expired_assignment_action_id,
plb.expired_value,
plb.expired_date,
plb.prev_assignment_action_id,
plb.prev_balance_value,
plb.prev_expiry_date,
plb.tax_unit_id,
plb.jurisdiction_code,
plb.original_entry_id,
plb.source_id,
plb.source_text,
plb.source_text2,
plb.source_number,
plb.tax_group,
plb.payroll_id,
plb.local_unit_id,
plb.organization_id,
plb.source_number2
from pay_latest_balances plb
, per_all_assignments_f ASSIGN
, pay_assignment_actions ACT
, pay_payroll_actions PACT
, pay_assignment_actions OWN_ACT
where plb.defined_balance_id = p_defined_balance_id
and ACT.assignment_action_id = p_assignment_action_id
and ASSIGN.assignment_id = ACT.assignment_id
and exists (select ''
from pay_object_groups POG
where POG.source_id (+) = ACT.assignment_id
and POG.source_type (+) = 'PAF'
and nvl(plb.process_group_id,
nvl(POG.parent_object_group_id, -1)) =
nvl(POG.parent_object_group_id,-1)
)
and plb.person_id = ASSIGN.person_id
and nvl(plb.assignment_id, ACT.assignment_id) = ACT.assignment_id
and PACT.payroll_action_id = ACT.payroll_action_id
and PACT.effective_date between ASSIGN.effective_start_date
and ASSIGN.effective_end_date
and OWN_ACT.assignment_action_id = plb.assignment_action_id
--and ACT.action_sequence >= OWN_ACT.action_sequence
and nvl(plb.tax_unit_id, nvl(g_con_tax_unit_id, -1))
= nvl(g_con_tax_unit_id, -1)
and nvl(substr(nvl(plb.jurisdiction_code,
nvl(g_con_jurisdiction_code, -1)),
1, p_defbal_rec.jurisdiction_lvl),
-1)
= nvl(
substr(nvl(g_con_jurisdiction_code, -1),
1, p_defbal_rec.jurisdiction_lvl),
-1)
and nvl(plb.original_entry_id, nvl(g_con_original_entry_id, -1))
= nvl(g_con_original_entry_id, -1)
and nvl(plb.source_id, nvl(g_con_source_id, -1))
= nvl(g_con_source_id, -1)
and nvl(plb.source_text, nvl(g_con_source_text, -1))
= nvl(g_con_source_text, -1)
and nvl(plb.source_text2, nvl(g_con_source_text2, -1))
= nvl(g_con_source_text2, -1)
and nvl(plb.source_number, nvl(g_con_source_number, -1))
= nvl(g_con_source_number, -1)
and nvl(plb.tax_group, nvl(g_con_tax_group, -1))
= nvl(g_con_tax_group, -1)
and nvl(plb.payroll_id, nvl(g_con_payroll_id, -1))
= nvl(g_con_payroll_id, -1)
and nvl(plb.local_unit_id, nvl(g_con_local_unit_id, -1))
= nvl(g_con_local_unit_id, -1)
and nvl(plb.organization_id, nvl(g_con_organization_id, -1))
= nvl(g_con_organization_id, -1)
and nvl(plb.source_number2, nvl(g_con_source_number2, -1))
= nvl(g_con_source_number2, -1)
order by plb.latest_balance_id;
select count(*)
into l_num_of_runs
from
pay_payroll_actions ppa2,
pay_assignment_actions paa2,
pay_assignment_actions paa
where paa.assignment_action_id = lbrec.expired_assignment_action_id
and paa2.assignment_id = paa.assignment_id
and ppa2.payroll_action_id = paa2.payroll_action_id
and ppa2.action_type in ('R', 'Q', 'B', 'V')
and paa2.action_sequence between paa.action_sequence
and lbrec.current_action_seq;
select count(*)
into l_num_of_runs
from
pay_payroll_actions ppa2,
pay_assignment_actions paa2,
pay_assignment_actions paa
where paa.assignment_action_id = lbrec.prev_assignment_action_id
and paa2.assignment_id = paa.assignment_id
and ppa2.payroll_action_id = paa2.payroll_action_id
and ppa2.action_type in ('R', 'Q', 'B', 'V')
and paa2.action_sequence between paa.action_sequence
and lbrec.current_action_seq;
select ppa.business_group_id
into l_business_group_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = p_assignment_action_id;
||'updated.');
l_retrieval_list.delete;
l_retrieval_list.delete;
l_retrieval_list.delete;
l_transform_cache.delete;
l_batch_bal_cache.delete;
l_balance_cache.delete;
l_batch_list.delete;
inserting a dummy payroll action for the required date, and calling the
hrassact.inassact procedure to insert the corresponding assignment action
and shuffle any later assignment actions along. The get_value
function is then called with the dummy assignment action, which returns the
balance value for the required date. A rollback then removes the temporary
payroll action and assignment action.
--
The parameter 'p_always_get_db_item' if set to TRUE will always derive the
balance from the route and not even try to find a latest balance value.
This parameter is used for testing purposes, to verify the latest balance
value.
--
There are 2 overloaded versions of the get_value function below. The first
one is called by the forms/ reports. The second has the option of setting
the 'p_always_get_db_item' value. This is necessary since the forms do not
support default parameters.
*/
function get_value
(
p_defined_balance_id in number,
p_assignment_id in number,
p_virtual_date in date
) return number is
p_balance_result number;
select ASSIGN.payroll_id,
ASSIGN.business_group_id,
PAYROLL.consolidation_set_id
into l_payroll_id,
l_bus_grp_id,
l_consol_set_id
from per_all_assignments_f ASSIGN
, pay_all_payrolls_f PAYROLL
where ASSIGN.assignment_id = p_assignment_id
and p_virtual_date between ASSIGN.effective_start_date
and ASSIGN.effective_end_date
and PAYROLL.payroll_id = ASSIGN.payroll_id
and p_virtual_date between PAYROLL.effective_start_date
and PAYROLL.effective_end_date;
select TIMEP.time_period_id
into l_time_period_id
from per_time_periods TIMEP
where TIMEP.payroll_id = l_payroll_id
and p_virtual_date between TIMEP.start_date
and TIMEP.end_date;
select pay_payroll_actions_s.nextval
into l_pay_action_id
from dual;
insert into pay_payroll_actions
(payroll_action_id,
action_type,
business_group_id,
consolidation_set_id,
payroll_id,
action_population_status,
action_status,
effective_date,
date_earned,
time_period_id,
object_version_number)
values
(l_pay_action_id,
'N', -- not tracked action type
l_bus_grp_id,
l_consol_set_id,
l_payroll_id,
'U',
'U',
p_virtual_date,
p_virtual_date,
l_time_period_id,
1);
select assignment_action_id
into l_ass_action_id
from pay_assignment_actions
where payroll_action_id = l_pay_action_id;
SELECT /*+ USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'
0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE
paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date <= c_effective_date
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
SELECT /*+ INDEX(PAF1 PER_ASSIGNMENTS_F_PK)
INDEX(PAF2 PER_ASSIGNMENTS_F_N12)
INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N51)
INDEX(PPA PAY_PAYROLL_ACTIONS_PK)
USE_NL(paa, ppa) */
fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paf1,
per_all_assignments_f paf2
WHERE
paf1.assignment_id = c_assignment_id
AND c_effective_date between paf1.effective_start_date
and paf1.effective_end_date
AND paf2.person_id = paf1.person_id
AND paf2.effective_start_date = (select max(paf3.effective_start_date)
from per_all_assignments_f paf3
where paf3.assignment_id = paf2.assignment_id
and paf3.effective_start_date <= c_effective_date)
AND paf2.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date <= c_effective_date
AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
select ppa.effective_date,
ppa.payroll_id
into l_pact_eff_date,
l_pact_payroll_id
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = l_assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id;
select pdb.defined_balance_id
from pay_defined_balances pdb,
pay_balance_validation pbv
where pdb.balance_type_id = p_bal_id
and pdb.save_run_balance = 'Y'
and pbv.defined_balance_id = pdb.defined_balance_id
and pbv.run_balance_status in ('P', 'V');
select '1' from dual
where exists
(select /*+ ORDERED INDEX(RRV PAY_RUN_RESULT_VALUES_PK)
INDEX(PRR PAY_RUN_RESULTS_PK) */
1
from pay_run_result_values rrv,
pay_run_results prr
where rrv.input_value_id = p_input_value_id
and prr.run_result_id = rrv.run_result_id
and prr.status in ('P', 'PA')
and nvl(rrv.result_value, '0') <> '0');
update pay_balance_validation
set run_balance_status = 'I'
where defined_balance_id = each_row.defined_balance_id;
select pdb.defined_balance_id
from pay_defined_balances pdb
where pdb.balance_type_id = p_bal_id
and pdb.save_run_balance = 'Y';
update pay_balance_validation
set run_balance_status = 'I'
where defined_balance_id = each_row.defined_balance_id;
select
prr.run_result_id
,prr.element_type_id
,prr.jurisdiction_code
,paa.assignment_action_id
,paa.assignment_id
,paa.tax_unit_id
,paa.payroll_action_id
,prr.time_definition_id
,prr.end_date
,prr.local_unit_id
from
pay_assignment_actions paa
,pay_run_results prr
where
paa.assignment_action_id = prr.assignment_action_id
and prr.run_result_id = p_run_result_id
;
select
ppa.effective_date
,pbg.legislation_code
,ppa.business_group_id
from
per_business_groups_perf pbg
,pay_payroll_actions ppa
where
pbg.business_group_id = ppa.business_group_id
and ppa.payroll_action_id = p_payroll_action_id
;
select prrv.result_value
from pay_run_result_values prrv,
pay_input_values_f piv
where prrv.run_result_id = p_rr_id
and piv.name = p_iv_name
and piv.input_value_id = prrv.input_value_id
and piv.element_type_id = p_ele_id
and p_effdate between piv.effective_start_date
and piv.effective_end_date;
cursor get_row_to_update(p_defined_balance_id number
,p_pact_id number
,p_gre number
,p_jd varchar2
,p_src_id number
,p_src_tx varchar2
,p_src_num number
,p_src_txt2 varchar2
,p_time_def_id number
,p_balance_date date
,p_local_unit_id number
,p_source_number2 number
,p_organization_id number)
is
select run_balance_id
, balance_value
from pay_run_balances
where defined_balance_id = p_defined_balance_id
and payroll_action_id = p_pact_id
and nvl(tax_unit_id, -1) = nvl(p_gre, -1)
and nvl(jurisdiction_code, 'null') = nvl(p_jd, 'null')
and nvl(source_id, -1) = nvl(p_src_id, -1)
and nvl(source_text, 'null') = nvl(p_src_tx, 'null')
and nvl(source_number, -1) = nvl(p_src_num, -1)
and nvl(source_text2, 'null') = nvl(p_src_txt2, 'null')
and nvl(time_definition_id, -1) = nvl(p_time_def_id, -1)
and nvl(local_unit_id, -1) = nvl(p_local_unit_id, -1)
and nvl(source_number2, -1) = nvl(p_source_number2, -1)
and nvl(organization_id, -1) = nvl(p_organization_id, -1)
and nvl(balance_date, to_date('0001/01/01', 'YYYY/MM/DD'))
= nvl(p_balance_date, to_date('0001/01/01', 'YYYY/MM/DD'))
and rownum = 1;
open get_row_to_update(p_grp_def_bal_id
,p_payroll_action_id
,p_tax_unit_id
,p_jur_code
,p_src_id
,p_src_txt
,p_src_num
,p_src_txt2
,p_time_def_id
,p_balance_date
,p_local_unit_id
,p_source_number2
,p_organization_id
);
fetch get_row_to_update into l_grp_run_bal_id, l_grp_run_bal_val;
if get_row_to_update%notfound then
--
-- error as should find a row for updating
--
close get_row_to_update;
close get_row_to_update;
update pay_run_balances
set balance_value = (balance_value - p_amount)
where run_balance_id = l_grp_run_bal_id;
hr_utility.trace('MULTI THREADED UPDATE');
update pay_run_balances
set balance_value = balance_value - p_amount
where run_balance_id = p_int_mlt_thrd_cache(l_current_ptr).run_balance_id;
hr_utility.trace('MULTI THREADED INSERT');
select pay_run_balances_s.nextval
into l_grp_run_bal_id
from dual;
insert into pay_run_balances
(run_balance_id
,defined_balance_id
,payroll_action_id
,effective_date
,balance_value
,tax_unit_id
,jurisdiction_code
,jurisdiction_comp1
,jurisdiction_comp2
,jurisdiction_comp3
,source_id
,source_text
,source_number
,source_text2
,time_definition_id
,balance_date
,local_unit_id
,source_number2
,organization_id
)
values
(l_grp_run_bal_id
,p_grp_def_bal_id
,p_payroll_action_id
,p_effective_date
,- p_amount
,p_tax_unit_id
,p_jur_code
,l_jur1
,l_jur2
,l_jur3
,p_src_id
,p_src_txt
,p_src_num
,p_src_txt2
,p_time_def_id
,p_balance_date
,p_local_unit_id
,p_source_number2
,p_organization_id
);
select distinct defined_balance_id
from pay_run_balances
where payroll_action_id = p_pact;
select /*+ ORDERED USE_NL(prr) INDEX(prr pay_run_results_n50)*/
distinct
paa.tax_unit_id tax_unit_id
, prr.jurisdiction_code jurisdiction_code
, decode(p_si_needed,
'Y', find_context('SOURCE_ID', prr.run_result_id),
null) source_id
, decode(p_st_needed,
'Y', find_context('SOURCE_TEXT', prr.run_result_id),
null) source_text
, decode(p_sn_needed,
'Y', find_context('SOURCE_NUMBER', prr.run_result_id),
null) source_number
, decode(p_st2_needed,
'Y', find_context('SOURCE_TEXT2', prr.run_result_id),
null) source_text2
, decode(p_sn2_needed,
'Y', find_context('SOURCE_NUMBER2', prr.run_result_id),
null) source_number2
, decode(p_org_needed,
'Y', find_context('ORGANIZATION_ID', prr.run_result_id),
null) organization_id
, prr.time_definition_id
, nvl(prr.end_date, ptp.end_date) balance_date
, prr.local_unit_id local_unit_id
, ppa.effective_date
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_run_results prr
where paa.assignment_action_id = p_asg_action
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = prr.assignment_action_id
and ptp.payroll_id = ppa.payroll_id
and ppa.date_earned between ptp.start_date
and ptp.end_date
order by 1, 2, 3, 4;
select pbg.business_group_id, pbg.legislation_code
from pay_payroll_actions ppa,
per_business_groups_perf pbg
where payroll_action_id = pactid
and pbg.business_group_id = ppa.business_group_id;
select plr.rule_mode
into g_save_run_bals
from pay_legislation_rules plr,
per_business_groups_perf pbg,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_payroll_action_id
and ppa.business_group_id = pbg.business_group_id
and pbg.legislation_code = plr.legislation_code
and plr.rule_type = 'SAVE_RUN_BAL';
g_rlb_grp_defbals.delete;
g_rlb_asg_defbals.delete;
select pdb.defined_balance_id
into g_rlb_asg_defbals(l_cnt).defined_balance_id
from pay_defined_balances pdb,
pay_defined_balances pdb_grp,
pay_balance_dimensions pbd
where pdb_grp.defined_balance_id = g_rlb_grp_defbals(l_cnt).defined_balance_id
and pdb_grp.balance_dimension_id = pbd.balance_dimension_id
and pdb.balance_type_id = pdb_grp.balance_type_id
and pdb.balance_dimension_id = pbd.asg_action_balance_dim_id;
g_grp_maintained_rb.delete;
g_grp_rb_ptr_list.delete;
insert into pay_run_balances
(
run_balance_id,
defined_balance_id,
assignment_action_id,
payroll_action_id,
assignment_id,
action_sequence,
effective_date,
balance_value,
tax_unit_id,
jurisdiction_code,
jurisdiction_comp1,
jurisdiction_comp2,
jurisdiction_comp3,
source_id,
source_text,
source_number,
source_text2,
time_definition_id,
balance_date,
local_unit_id,
source_number2,
organization_id,
tax_group
)
values (
pay_run_balances_s.nextval,
p_defined_balance_id,
p_asg_act_id,
p_payroll_act_id,
p_asg_id,
p_act_seq,
p_eff_date,
p_bal_val,
p_tax_unit,
p_jurisdiction,
l_jur1,
l_jur2,
l_jur3,
p_source_id,
p_source_text,
p_source_number,
p_source_text2,
p_time_def_id,
p_balance_date,
p_local_unit_id,
p_source_number2,
p_organization_id,
p_tax_group
);
select assignment_id
, action_sequence
from pay_assignment_actions
where assignment_action_id = p_asg_act_id;
select
/*+ ORDERED USE_NL(pbf, pdb, pbd, prrv) */
distinct pdb.defined_balance_id
from
pay_input_values_f piv
,pay_balance_feeds_f pbf
,pay_defined_balances pdb
,pay_balance_dimensions pbd
,pay_run_result_values prrv
where
pbd.dimension_level = 'ASG'
and pdb.save_run_balance = 'Y'
and ( (pdb.business_group_id = p_bus_grp_id
and pdb.legislation_code is null)
or (pdb.legislation_code = p_leg_code
and pdb.business_group_id is null)
or (pdb.legislation_code is null
and pdb.business_group_id is null))
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.balance_type_id = pbf.balance_type_id
and p_effective_date between pbf.effective_start_date
and pbf.effective_end_date
and pbf.input_value_id = piv.input_value_id
and piv.element_type_id = p_ele_id
and p_effective_date between piv.effective_start_date
and piv.effective_end_date
and prrv.input_value_id = piv.input_value_id
and prrv.run_result_id = p_rr_id
and prrv.result_value is not null
;
delete from pay_run_balances
where
defined_balance_id = l_rec.defined_balance_id
and assignment_action_id = l_rr_info.assignment_action_id
and payroll_action_id is null
and ( (l_defbal_ctx.tax_unit_id is null)
or (tax_unit_id = l_defbal_ctx.tax_unit_id))
and ( (l_defbal_ctx.jurisdiction_code is null)
or (substr(jurisdiction_code, 1, l_defbal_rec.jurisdiction_lvl))
= substr(l_defbal_ctx.jurisdiction_code
,1, l_defbal_rec.jurisdiction_lvl))
and ( (l_defbal_ctx.source_id is null)
or (source_id = l_defbal_ctx.source_id))
and ( (l_defbal_ctx.source_text is null)
or (source_text = l_defbal_ctx.source_text))
and ( (l_defbal_ctx.source_text2 is null)
or (source_text2 = l_defbal_ctx.source_text2))
and ( (l_defbal_ctx.source_number is null)
or (source_number = l_defbal_ctx.source_number))
and ( (l_defbal_ctx.time_def_id is null)
or (time_definition_id = l_defbal_ctx.time_def_id))
and ( (l_defbal_ctx.local_unit_id is null)
or (local_unit_id = l_defbal_ctx.local_unit_id))
and ( (l_defbal_ctx.source_number2 is null)
or (source_number2 = l_defbal_ctx.source_number2))
and ( (l_defbal_ctx.organization_id is null)
or (organization_id = l_defbal_ctx.organization_id))
and ( (l_defbal_ctx.balance_date is null)
or (balance_date = l_defbal_ctx.balance_date))
;
select /*+ ORDERED USE_NL(prr) INDEX(prr pay_run_results_n50)*/
distinct
paa.tax_unit_id tax_unit_id
, prr.jurisdiction_code jurisdiction_code
, decode(p_si_needed,
'Y', find_context('SOURCE_ID', prr.run_result_id),
null) source_id
, decode(p_st_needed,
'Y', find_context('SOURCE_TEXT', prr.run_result_id),
null) source_text
, decode(p_sn_needed,
'Y', find_context('SOURCE_NUMBER', prr.run_result_id),
null) source_number
, decode(p_st2_needed,
'Y', find_context('SOURCE_TEXT2', prr.run_result_id),
null) source_text2
, decode(p_sn2_needed,
'Y', find_context('SOURCE_NUMBER2', prr.run_result_id),
null) source_number2
, decode(p_org_needed,
'Y', find_context('ORGANIZATION_ID', prr.run_result_id),
null) organization_id
, prr.local_unit_id
, prr.time_definition_id
, nvl(prr.end_date, ptp.end_date) balance_date
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_run_results prr
where paa.assignment_action_id = asgact
and paa.assignment_action_id = prr.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_id = ptp.payroll_id
and ppa.date_earned between ptp.start_date
and ptp.end_date
order by 1, 2, 3, 4;
select paa.assignment_action_id,
ppa.effective_date,
paa.assignment_id,
paa.action_sequence
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = asgact;
select pbg.business_group_id, pbg.legislation_code
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_business_groups_perf pbg
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = aaid
and pbg.business_group_id = ppa.business_group_id;
delete from pay_run_balances
where defined_balance_id = p_defined_balance_lst(cnt).defined_balance_id
and assignment_action_id = p_asgact_id;
select 1
into l_dummy
from dual
where exists (select ''
from pay_run_balances
where defined_balance_id = p_defined_balance_lst(cnt).defined_balance_id
and assignment_action_id = p_asgact_id
and balance_value <> 0);
select /*+ ORDERED USE_NL (prr) INDEX(prr pay_run_results_n50)*/
distinct
decode(p_tu_needed,
'Y', paa.tax_unit_id,
null) tax_unit_id
, decode(p_jc_needed,
'Y', substr(prr.jurisdiction_code, 1, p_jur_lvl),
null) jurisdiction_code
, decode(p_si_needed,
'Y', find_context('SOURCE_ID', prr.run_result_id),
null) source_id
, decode(p_st_needed,
'Y', find_context('SOURCE_TEXT', prr.run_result_id),
null) source_text
, decode(p_sn_needed,
'Y', find_context('SOURCE_NUMBER', prr.run_result_id),
null) source_number
, decode(p_st2_needed,
'Y', find_context('SOURCE_TEXT2', prr.run_result_id),
null) source_text2
, decode(p_td_needed,
'Y', prr.time_definition_id,
null) time_definition_id
, decode(p_bd_needed,
'Y', nvl(prr.end_date, ptp.end_date),
null) balance_date
, decode(p_lu_needed,
'Y', prr.local_unit_id,
null) local_unit_id
, decode(p_sn2_needed,
'Y', find_context('SOURCE_NUMBER2', prr.run_result_id),
null) source_number2
, decode(p_org_needed,
'Y', find_context('ORGANIZATION_ID', prr.run_result_id),
null) organization_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_run_results prr
where paa.payroll_action_id = p_pact_id
and paa.assignment_action_id = prr.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_id = ptp.payroll_id
and ppa.date_earned between ptp.start_date
and ptp.end_date
order by 1, 2, 3, 4;
delete from pay_run_balances
where defined_balance_id = p_def_bal_id
and payroll_action_id = p_pactid;
select 1
into l_dummy
from dual
where exists (select ''
from pay_run_balances
where defined_balance_id = p_def_bal_id
and payroll_action_id = p_pactid
and balance_value <> 0);
select /*+ ORDERED USE_NL(piv pbf pbt pdb pbd prrv)
INDEX (prrv PAY_RUN_RESULT_VALUES_PK)*/
distinct pdb.defined_balance_id,
pbt.jurisdiction_level,
pbt.balance_type_id,
pbd.balance_dimension_id
from
pay_assignment_actions paa,
pay_run_results prr,
pay_input_values_f piv,
pay_balance_feeds_f pbf,
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_run_result_values prrv
where pbd.dimension_level = 'GRP'
and pdb.save_run_balance|| decode (pbt.balance_type_id, 0, '', '')= 'Y'
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.balance_type_id = pbt.balance_type_id
and ((pdb.business_group_id = p_bus_grp_id
and pdb.legislation_code is null)
or
(pdb.legislation_code = p_leg_code
and pdb.business_group_id is null)
or
(pdb.legislation_code is null
and pdb.business_group_id is null)
)
and ( p_bal_list <> 'INVALID'
or ( p_bal_list = 'INVALID'
and exists (select ''
from pay_balance_validation pbv
where pbv.defined_balance_id = pdb.defined_balance_id
and pbv.business_group_id = p_bus_grp_id
and pbv.run_balance_status = 'P')
)
)
and ( p_def_bal is null
or p_def_bal = pdb.defined_balance_id
)
and paa.payroll_action_id = p_pact_id
and prr.assignment_action_id = paa.assignment_action_id
and piv.element_type_id = prr.element_type_id
and p_eff_date between piv.effective_start_date
and piv.effective_end_date
and pbf.input_value_id = piv.input_value_id
and p_eff_date between pbf.effective_start_date
and pbf.effective_end_date
and pbt.balance_type_id = pbf.balance_type_id
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = piv.input_value_id
and prrv.result_value is not null;
select /*+ ORDERED */
pdb.defined_balance_id,
pdb.balance_type_id,
pdb.balance_dimension_id
from
pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where
pbad.attribute_name = p_bal_list
and pbad.attribute_id = pba.attribute_id
and pba.defined_balance_id = pdb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_level = 'GRP'
and p_def_bal_id = pba.defined_balance_id
and exists (select /*+ ORDERED */ ''
from pay_assignment_actions paa,
pay_run_results prr,
pay_run_result_values prrv,
pay_balance_feeds_f pbf
where paa.payroll_action_id = p_pact_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = pbf.input_value_id
and pdb.balance_type_id = pbf.balance_type_id
)
UNION ALL
select pdb.defined_balance_id,
pdb.balance_type_id,
pdb.balance_dimension_id
from
pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where
pbad.attribute_name = p_bal_list
and pbad.attribute_id = pba.attribute_id
and pba.defined_balance_id = pdb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_level = 'GRP'
and p_def_bal_id is null
and pdb.balance_type_id in
(select /*+ USE_NL(paa,prr,prrv,pbf) ORDERED */
distinct pbf.balance_type_id
from pay_assignment_actions paa,
pay_run_results prr,
pay_run_result_values prrv,
pay_balance_feeds_f pbf
where paa.payroll_action_id = p_pact_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = pbf.input_value_id
);
select /*+ ORDERED */
pdb.defined_balance_id,
pdb.balance_type_id,
pdb.balance_dimension_id
from
pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where
pbad.attribute_name = p_bal_list
and pbad.attribute_id = pba.attribute_id
and pba.defined_balance_id = pdb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_level = 'GRP'
and exists (select ''
from pay_balance_validation pbv
where pdb.defined_balance_id + decode(PDB.LEGISLATION_SUBGROUP, ' ', 0, 0) = pbv.defined_balance_id
and pbv.run_balance_status = 'V'
and pbv.business_group_id = p_bus_grp_id
and pbv.balance_load_date is not null
and pbv.balance_load_date > p_eff_date)
and pdb.balance_type_id in
(select /*+ USE_NL(paa,prr,prrv,pbf) ORDERED */
distinct pbf.balance_type_id
from pay_assignment_actions paa,
pay_run_results prr,
pay_run_result_values prrv,
pay_balance_feeds_f pbf
where paa.payroll_action_id = p_pact_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = pbf.input_value_id
);
select paa.assignment_action_id,
ppa.effective_date,
paa.assignment_id,
paa.action_sequence
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_action_id = p_pact_id
and rownum = 1;
select /*+ ORDERED USE_NL (prr) INDEX(prr pay_run_results_n50)*/
distinct
paa.tax_unit_id tax_unit_id
, prr.jurisdiction_code jurisdiction_code
, decode(p_si_needed,
'Y', find_context('SOURCE_ID', prr.run_result_id),
null) source_id
, decode(p_st_needed,
'Y', find_context('SOURCE_TEXT', prr.run_result_id),
null) source_text
, decode(p_sn_needed,
'Y', find_context('SOURCE_NUMBER', prr.run_result_id),
null) source_number
, decode(p_st2_needed,
'Y', find_context('SOURCE_TEXT2', prr.run_result_id),
null) source_text2
, decode(p_sn2_needed,
'Y', find_context('SOURCE_NUMBER2', prr.run_result_id),
null) source_number2
, decode(p_org_needed,
'Y', find_context('ORGANIZATION_ID', prr.run_result_id),
null) organization_id
, prr.time_definition_id
, nvl(prr.end_date, ptp.end_date) balance_date
, prr.local_unit_id local_unit_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_run_results prr
where paa.payroll_action_id = p_pact_id
and paa.assignment_action_id = prr.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_id = ptp.payroll_id
and ppa.date_earned between ptp.start_date
and ptp.end_date
order by 1, 2, 3, 4;
select pbg.business_group_id, pbg.legislation_code
from pay_payroll_actions ppa,
per_business_groups_perf pbg
where ppa.payroll_action_id = p_pact_id
and pbg.business_group_id = ppa.business_group_id;
select pbg.business_group_id,
pbg.legislation_code,
ppa.effective_date
into l_bg_id,
l_leg_code,
l_eff_date
from per_business_groups_perf pbg,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pact_id
and ppa.business_group_id = pbg.business_group_id;
select /*+ ORDERED USE_NL(prr) INDEX(prr pay_run_results_n50)*/
distinct
decode(p_tu_needed,
'Y', paa.tax_unit_id,
null) tax_unit_id
, decode(p_jc_needed,
'Y', substr(prr.jurisdiction_code, 1, p_jur_lvl),
null) jurisdiction_code
, decode(p_si_needed,
'Y', find_context('SOURCE_ID', prr.run_result_id),
null) source_id
, decode(p_st_needed,
'Y', find_context('SOURCE_TEXT', prr.run_result_id),
null) source_text
, decode(p_sn_needed,
'Y', find_context('SOURCE_NUMBER', prr.run_result_id),
null) source_number
, decode(p_st2_needed,
'Y', find_context('SOURCE_TEXT2', prr.run_result_id),
null) source_text2
, decode(p_sn2_needed,
'Y', find_context('SOURCE_NUMBER2', prr.run_result_id),
null) source_number2
, decode(p_org_needed,
'Y', find_context('ORGANIZATION_ID', prr.run_result_id),
null) organization_id
, decode(p_td_needed,
'Y', prr.time_definition_id,
null) time_definition_id
, decode(p_bd_needed,
'Y', nvl(prr.end_date, ptp.end_date),
null) balance_date
, decode(p_lu_needed,
'Y', prr.local_unit_id,
null) local_unit_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_run_results prr
where paa.assignment_action_id = asgact
and paa.assignment_action_id = prr.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_id = ptp.payroll_id
and ppa.date_earned between ptp.start_date
and ptp.end_date
order by 1, 2, 3, 4;
delete from pay_run_balances
where defined_balance_id = p_def_bal_id
and assignment_action_id = p_asgact_id;
select 1
into l_dummy
from dual
where exists (select ''
from pay_run_balances
where defined_balance_id = p_def_bal_id
and assignment_action_id = p_asgact_id
and balance_value <> 0);
select /*+ ORDERED USE_NL(pbf pbt pdb pbd prrv)
INDEX(prrv PAY_RUN_RESULT_VALUES_N50)*/
distinct pdb.defined_balance_id,
pbt.jurisdiction_level,
pbt.balance_type_id,
pbd.balance_dimension_id
from
pay_run_results prr,
pay_run_result_values prrv,
pay_balance_feeds_f pbf,
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbd.dimension_level = 'ASG'
and pdb.save_run_balance|| decode (pbt.balance_type_id, 0, '', '')= 'Y'
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pdb.balance_type_id = pbt.balance_type_id
and ( p_bal_list <> 'INVALID'
or ( p_bal_list = 'INVALID'
and exists (select ''
from pay_balance_validation pbv
where pbv.defined_balance_id = pdb.defined_balance_id
and pbv.business_group_id = p_bus_grp_id
and pbv.run_balance_status = 'P')
)
)
and ( p_def_bal is null
or p_def_bal = pdb.defined_balance_id
)
and ((pdb.business_group_id = p_bus_grp_id
and pdb.legislation_code is null)
or
(pdb.legislation_code = p_leg_code
and pdb.business_group_id is null)
or
(pdb.legislation_code is null
and pdb.business_group_id is null)
)
and prr.assignment_action_id = p_asg_act_id
and prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = pbf.input_value_id
and pbt.balance_type_id = pbf.balance_type_id;
select /*+ ORDERED */
pdb.defined_balance_id,
pdb.balance_type_id,
pdb.balance_dimension_id
from
pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where
pbad.attribute_name = p_bal_list
and pbad.attribute_id = pba.attribute_id
and pba.defined_balance_id = pdb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_level = 'ASG'
and p_def_bal_id = pba.defined_balance_id
and exists (select /*+ ORDERED */ ''
from pay_run_results prr,
pay_run_result_values prrv,
pay_balance_feeds_f pbf
where prr.assignment_action_id = p_asg_act_id
and prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = pbf.input_value_id
and pdb.balance_type_id = pbf.balance_type_id
)
union all
select
pdb.defined_balance_id,
pdb.balance_type_id,
pdb.balance_dimension_id
from
pay_bal_attribute_definitions pbad,
pay_balance_attributes pba,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where
pbad.attribute_name = p_bal_list
and pbad.attribute_id = pba.attribute_id
and pba.defined_balance_id = pdb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_level = 'ASG'
and p_def_bal_id is null
and pdb.balance_type_id in
(select /*+ USE_NL(paa,prr,prrv,pbf) ORDERED */
distinct pbf.balance_type_id
from pay_run_results prr,
pay_run_result_values prrv,
pay_balance_feeds_f pbf
where prr.assignment_action_id = p_asg_act_id
and prr.run_result_id = prrv.run_result_id
and prrv.input_value_id = pbf.input_value_id);
select /*+ ORDERED
use_nl(piv pbf pdb pbd pbv prrv)
*/
distinct
pdb.defined_balance_id,
pdb.balance_type_id,
pdb.balance_dimension_id
from
pay_run_results prr,
pay_input_values_f piv,
pay_balance_feeds_f pbf,
pay_defined_balances pdb,
pay_balance_attributes pba,
pay_balance_dimensions pbd,
pay_balance_validation pbv,
pay_run_result_values prrv
where
prr.assignment_action_id = p_asg_act_id
and piv.element_type_id = prr.element_type_id
and p_eff_date between piv.effective_start_date
and piv.effective_end_date
and pbf.input_value_id = piv.input_value_id
and p_eff_date between pbf.effective_start_date
and pbf.effective_end_date
and pdb.balance_type_id = pbf.balance_type_id
and pdb.save_run_balance = 'Y'
and pba.attribute_id = p_att_id
and pba.defined_balance_id = pdb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_level = 'ASG'
and pbv.defined_balance_id = pdb.defined_balance_id
and pbv.run_balance_status = 'V'
and pbv.business_group_id = p_bus_grp_id
and pbv.balance_load_date > p_eff_date
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = pbf.input_value_id
and nvl(prrv.result_value,'0') <> '0'
;
select paa.assignment_action_id,
ppa.effective_date,
paa.assignment_id,
paa.action_sequence
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = asgact;
select /*+ ORDERED USE_NL(prr) INDEX(prr pay_run_results_n50)*/
distinct
paa.tax_unit_id tax_unit_id
, prr.jurisdiction_code jurisdiction_code
, decode(p_si_needed,
'Y', find_context('SOURCE_ID', prr.run_result_id),
null) source_id
, decode(p_st_needed,
'Y', find_context('SOURCE_TEXT', prr.run_result_id),
null) source_text
, decode(p_sn_needed,
'Y', find_context('SOURCE_NUMBER', prr.run_result_id),
null) source_number
, decode(p_st2_needed,
'Y', find_context('SOURCE_TEXT2', prr.run_result_id),
null) source_text2
, decode(p_sn2_needed,
'Y', find_context('SOURCE_NUMBER2', prr.run_result_id),
null) source_number2
, decode(p_org_needed,
'Y', find_context('ORGANIZATION_ID', prr.run_result_id),
null) organization_id
, prr.time_definition_id
, nvl(prr.end_date, ptp.end_date) balance_date
, prr.local_unit_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp,
pay_run_results prr
where paa.assignment_action_id = asgact
and paa.assignment_action_id = prr.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.payroll_id = ptp.payroll_id
and ppa.date_earned between ptp.start_date
and ptp.end_date
order by 1, 2, 3, 4;
select pbg.business_group_id, pbg.legislation_code
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_business_groups_perf pbg
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = aaid
and pbg.business_group_id = ppa.business_group_id;
select pbg.business_group_id,
pbg.legislation_code
into l_bg_id,
l_leg_code
from per_business_groups_perf pbg,
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_asgact_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.business_group_id = pbg.business_group_id;
select attribute_id into l_bal_attid
from pay_bal_attribute_definitions
where attribute_name = p_bal_list;
select pdb.defined_balance_id
from pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_assignment_actions paa,
pay_payroll_actions ppa,
per_business_groups_perf pbg
where pbd.dimension_level = 'ASG'
and pdb.save_run_balance = 'Y'
and pdb.balance_dimension_id = pbd.balance_dimension_id
and paa.assignment_action_id = p_asg_act_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id = pbg.business_group_id
and ((pdb.business_group_id = pbg.business_group_id
and pdb.legislation_code is null)
or
(pdb.legislation_code = pbg.legislation_code
and pdb.business_group_id is null)
or
(pdb.legislation_code is null
and pdb.business_group_id is null)
);
select /*+ ORDERED */
pdb.defined_balance_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_business_groups_perf pbg,
pay_balance_validation pbv,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbd.dimension_level = 'ASG'
and pdb.save_run_balance = 'Y'
and pdb.balance_dimension_id = pbd.balance_dimension_id
and paa.assignment_action_id = p_asg_act_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.business_group_id = pbg.business_group_id
and pbv.defined_balance_id = pdb.defined_balance_id
and pbv.business_group_id = ppa.business_group_id
and pbv.run_balance_status <> 'V'
and ((pdb.business_group_id = pbg.business_group_id
and pdb.legislation_code is null)
or
(pdb.legislation_code = pbg.legislation_code
and pdb.business_group_id is null)
or
(pdb.legislation_code is null
and pdb.business_group_id is null)
);
l_balance_lst.delete;
select plr.rule_mode
into save_run_bals
from pay_legislation_rules plr,
per_business_groups_perf pbg,
pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = p_asgact_id
and ppa.business_group_id = pbg.business_group_id
and pbg.legislation_code = plr.legislation_code
and plr.rule_type = 'SAVE_ASG_RUN_BAL';
l_balance_lst.delete;
l_balance_lst.delete;
select distinct
paa.tax_unit_id tax_unit_id
, prr.jurisdiction_code jurisdiction_code
, decode(p_si_needed,
'Y', find_context('SOURCE_ID', prr.run_result_id),
null) source_id
, decode(p_st_needed,
'Y', find_context('SOURCE_TEXT', prr.run_result_id),
null) source_text
, decode(p_sn_needed,
'Y', find_context('SOURCE_NUMBER', prr.run_result_id),
null) source_number
, decode(p_st2_needed,
'Y', find_context('SOURCE_TEXT2', prr.run_result_id),
null) source_text2
, decode(p_sn2_needed,
'Y', find_context('SOURCE_NUMBER2', prr.run_result_id),
null) source_number2
, decode(p_org_needed,
'Y', find_context('ORGANIZATION_ID', prr.run_result_id),
null) organization_id
, prr.time_definition_id
, nvl(prr.end_date, ptp.end_date) balance_date
, prr.local_unit_id
from pay_assignment_actions paa,
pay_run_results prr,
pay_payroll_actions ppa,
per_time_periods ptp
where ppa.payroll_action_id = p_pact_id
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_action_id = prr.assignment_action_id
and ptp.payroll_id = ppa.payroll_id
and ppa.date_earned between ptp.start_date
and ptp.end_date
order by 1, 2, 3, 4;
select paa.assignment_action_id,
ppa.effective_date
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pact_id
and ppa.payroll_action_id = paa.payroll_action_id
and rownum = 1;
select pbg.business_group_id, pbg.legislation_code
from pay_payroll_actions ppa,
per_business_groups_perf pbg
where payroll_action_id = pactid
and pbg.business_group_id = ppa.business_group_id;
delete from pay_run_balances
where defined_balance_id = p_defined_balance_lst(cnt).defined_balance_id
and payroll_action_id = p_pact_id;
select 1
into l_dummy
from dual
where exists (select 1
from pay_run_balances
where defined_balance_id = p_defined_balance_lst(cnt).defined_balance_id
and payroll_action_id = p_pact_id
and balance_value <> 0);
select pdb.defined_balance_id
from pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_payroll_actions ppa,
per_business_groups_perf pbg
where pbd.dimension_level = 'GRP'
and pdb.save_run_balance = 'Y'
and pdb.balance_dimension_id = pbd.balance_dimension_id
and ppa.payroll_action_id = p_pact_id
and ppa.business_group_id = pbg.business_group_id
and ((pdb.business_group_id = pbg.business_group_id
and pdb.legislation_code is null)
or
(pdb.legislation_code = pbg.legislation_code
and pdb.business_group_id is null)
or
(pdb.legislation_code is null
and pdb.business_group_id is null)
);
select /*+ ORDERED */
pdb.defined_balance_id
from pay_payroll_actions ppa,
per_business_groups_perf pbg,
pay_balance_validation pbv,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbd.dimension_level = 'GRP'
and pdb.save_run_balance = 'Y'
and pdb.balance_dimension_id = pbd.balance_dimension_id
and ppa.payroll_action_id = p_pact_id
and ppa.business_group_id = pbg.business_group_id
and pbv.defined_balance_id = pdb.defined_balance_id
and pbv.business_group_id = ppa.business_group_id
and pbv.run_balance_status <> 'V'
and ((pdb.business_group_id = pbg.business_group_id
and pdb.legislation_code is null)
or
(pdb.legislation_code = pbg.legislation_code
and pdb.business_group_id is null)
or
(pdb.legislation_code is null
and pdb.business_group_id is null)
);
l_balance_lst.delete;
select plr.rule_mode
into save_run_bals
from pay_legislation_rules plr,
per_business_groups_perf pbg,
pay_payroll_actions ppa
where ppa.payroll_action_id = p_pact_id
and ppa.business_group_id = pbg.business_group_id
and pbg.legislation_code = plr.legislation_code
and plr.rule_type = 'SAVE_RUN_BAL';
l_balance_lst.delete;
l_balance_lst.delete;
select dimension_level
from pay_balance_dimensions
where dimension_type = 'R'
and balance_dimension_id = p_balance_dimension_id;
select legislation_code
from per_business_groups_perf
where business_group_id = p_bg_id;
select pbg.business_group_id
from per_business_groups_perf pbg
, pay_legislation_rules plr
where pbg.legislation_code = plr.legislation_code
and plr.legislation_code = nvl(p_leg_code, plr.legislation_code)
and pbg.business_group_id = nvl(p_bg_id, pbg.business_group_id)
and plr.rule_type = decode(p_dim_level, 'ASG', 'SAVE_ASG_RUN_BAL'
, 'GRP', 'SAVE_RUN_BAL')
and plr.rule_mode = 'Y';
select 1
from dual
where exists (select /*+ ORDERED*/
null
from pay_balance_feeds_f bf
, pay_run_result_values rrv
where bf.balance_type_id = p_baltype_id
and bf.input_value_id = rrv.input_value_id);
select nvl((max(ppa.effective_date)+1),fnd_date.canonical_to_date('0001/01/01')) bal_load_date
from pay_payroll_actions ppa
, pay_action_classifications pac
where ppa.action_type = pac.action_type
and pac.classification_name = 'SEQUENCED'
and ppa.business_group_id = p_business_group_id;
insert into pay_balance_validation
(BALANCE_VALIDATION_ID
,DEFINED_BALANCE_ID
,BUSINESS_GROUP_ID
,RUN_BALANCE_STATUS
,BALANCE_LOAD_DATE)
select pay_balance_validation_s.nextval
,p_defbal_id
,each_bg.business_group_id
,'V'
,l_bal_load_date
from dual
where not exists (select 1
from pay_balance_validation
where defined_balance_id = p_defbal_id
and business_group_id = each_bg.business_group_id);
select prr.run_result_id,
paa.tax_unit_id,
prr.local_unit_id,
prr.jurisdiction_code,
prr.source_id original_entry_id,
ppa.payroll_id,
decode(p_si_needed,
'Y', pay_balance_pkg.find_context('SOURCE_ID', prr.run_result_id),
null) source_id,
decode(p_st_needed,
'Y', pay_balance_pkg.find_context('SOURCE_TEXT', prr.run_result_id),
null) source_text,
decode(p_sn_needed,
'Y', pay_balance_pkg.find_context('SOURCE_NUMBER', prr.run_result_id),
null) source_number,
decode(p_st2_needed,
'Y', pay_balance_pkg.find_context('SOURCE_TEXT2', prr.run_result_id),
null) source_text2,
decode(p_sn2_needed,
'Y', pay_balance_pkg.find_context('SOURCE_NUMBER2', prr.run_result_id),
null) source_number2,
decode(p_org_needed,
'Y', pay_balance_pkg.find_context('ORGANIZATION_ID', prr.run_result_id),
null) organization_id,
ppa.effective_date
from pay_assignment_actions paa,
pay_run_results prr,
pay_payroll_actions ppa
where paa.assignment_action_id = revassactid
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = prr.assignment_action_id;
select paa.assignment_id,
ppa.effective_date,
ppa.payroll_action_id,
ppa.action_type,
ppa.business_group_id
into l_asg_id,
l_eff_date,
l_pactid,
l_act_type,
l_bus_grp
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_asg_action
and ppa.payroll_action_id = paa.payroll_action_id;
select parameter_value
into l_value
from pay_action_parameters pap
where pap.parameter_name = 'REV_LAT_BAL';
select rule_mode, pbg.legislation_code
into l_rule_mode, leg_code
from pay_legislation_rules plr,
per_business_groups_perf pbg
where plr.legislation_code = pbg.legislation_code
and rule_type = 'BAL_ADJ_LAT_BAL'
and pbg.business_group_id = l_bus_grp;
select hoi.org_information5
into l_tax_group
from hr_organization_information hoi,
pay_assignment_actions paa
where UPPER(hoi.org_information_context) = 'FEDERAL TAX RULES'
and hoi.organization_id = paa.tax_unit_id
and paa.assignment_action_id = p_asg_action
and hoi.org_information5 is not null;
select hoi.org_information4
into l_tax_group
from hr_organization_information hoi,
pay_assignment_actions paa
where UPPER(hoi.org_information_context) = 'CANADA EMPLOYER IDENTIFICATION'
and hoi.organization_id = paa.tax_unit_id
and paa.assignment_action_id = p_asg_action
and hoi.org_information4 is not null;