[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
14 May 99 Anusree Sen 115.2 Deleted DBMS_OUTPUT
17 May 99 Isen 115.3 Removed null others exception handler
- bug 2422
25 May 99 Isen 115.4 Added procedure to initialize globals
01 Jul 99 Asen 115.5 Added code for decode fields.
14 jub 91 tilak 115.6 pay_payroll_actions.effective_Date
extracted for payroll end date criteria
30 nov 01 tjesumic 115.7 for performance cursor changed bug 2129704
14 dec 01 tjesumic 115.8 for performance cursor changed bug 2129704
06 feb 02 tjesumic 115.9 comment added ,why the global assignment id is not
used to control the assignment table
18 nov 02 lakrish 115.10 Bug 2669594,Added assignment for input value name
24-Dec-05 tjesumic 115.11 formula added for payroll
15-Feb-07 tjesumic 115.12 Hint added for c_asg cursor
--------------------------------------------------------------------------------
*/
--
g_package varchar2(33) := ' ben_ext_runrslt.'; -- Global package name
select
et.element_name element_name,
et.element_type_id element_id,
et.reporting_name reporting_name,
et.description description,
et.classification_id class_id,
ec.classification_name class_name,
et.processing_type process_type,
et.input_currency_code input_currency,
et.output_currency_code output_currency,
et.formula_id skip_rule_id, -- skip rule
ff.formula_name skip_rule_name,
iv.name value_name,
iv.uom value_unit,
iv.display_sequence value_seq,
iv.input_value_id value_id,
rr.run_result_id result_id,
rr.jurisdiction_code juris_code,
rr.status status,
rr.source_type source_type,
rr.source_id source_id,
rr.entry_type entry_type,
rv.result_value result_value,
ppa.effective_Date effective_Date
from
per_all_assignments_f asg,
pay_assignment_actions aac,
pay_input_values_f iv,
pay_element_types_f et,
pay_element_classifications ec,
ff_formulas_f ff,
pay_run_results rr,
pay_run_result_values rv,
pay_payroll_actions ppa
where
asg.person_id = p_person_id
--and asg.assignment_id = ben_ext_person.g_assignment_id --1969853
and asg.assignment_id = aac.assignment_id
and aac.assignment_action_id = rr.assignment_action_id
and iv.input_value_id = nvl(ben_ext_person.g_chg_input_value_id,iv.input_value_id)
and iv.element_type_id = et.element_type_id
and aac.payroll_action_id = ppa.payroll_action_id
and rr.element_type_id = et.element_type_id
and rr.run_result_id = rv.run_result_id
and rv.input_value_id = iv.input_value_id
and et.classification_id = ec.classification_id (+)
and et.formula_id = ff.formula_id (+)
and p_effective_date between nvl(iv.effective_start_date,p_effective_date)
and nvl(iv.effective_end_date ,p_effective_date)
and p_effective_date between nvl(et.effective_start_date,p_effective_date)
and nvl(et.effective_end_date ,p_effective_date)
and p_effective_date between nvl(asg.effective_start_date,p_effective_date)
and nvl(asg.effective_end_date ,p_effective_date)
and p_effective_date between nvl(ff.effective_start_date,p_effective_date)
and nvl(ff.effective_end_date,p_effective_date)
and rv.result_value is not null;
select /*+ ordered */ aac.assignment_action_id ,
ppa.effective_Date effective_Date
from per_all_assignments_f asg,
pay_assignment_actions aac,
pay_payroll_actions ppa
where asg.person_id = p_person_id
--and asg.assignment_id = ben_ext_person.g_assignment_id --1969853
and asg.assignment_id = aac.assignment_id
and aac.payroll_action_id = ppa.payroll_action_id
and p_effective_date between asg.effective_start_date and asg.effective_end_date
and (p_start_date is null or (ppa.effective_date between
p_start_date and p_end_date ) ) ;
select iv.name value_name,
iv.uom value_unit,
iv.display_sequence value_seq,
iv.input_value_id value_id,
rr.run_result_id result_id,
rr.jurisdiction_code juris_code,
rr.status status,
rr.source_type source_type,
rr.source_id source_id,
rr.entry_type entry_type,
rv.result_value result_value,
iv.element_type_id
from pay_run_results rr,
pay_input_values_f iv,
pay_run_result_values rv
where rr.assignment_action_id = c_assignment_action_id
and rr.element_type_id = iv.element_type_id
and (ben_ext_person.g_chg_input_value_id is null
or (iv.input_value_id = ben_ext_person.g_chg_input_value_id))
and rr.run_result_id = rv.run_result_id
and rv.input_value_id = iv.input_value_id
and rv.result_value is not null
and p_effective_date between iv.effective_start_date
and iv.effective_end_date ;
select iv.name value_name,
iv.uom value_unit,
iv.display_sequence value_seq,
iv.input_value_id value_id,
rr.run_result_id result_id,
rr.jurisdiction_code juris_code,
rr.status status,
rr.source_type source_type,
rr.source_id source_id,
rr.entry_type entry_type,
rv.result_value result_value,
iv.element_type_id
from pay_run_results rr,
pay_input_values_f iv,
pay_run_result_values rv
where rr.assignment_action_id = c_assignment_action_id
and rr.element_type_id = iv.element_type_id
and (ben_ext_person.g_chg_input_value_id is null
or (iv.input_value_id = ben_ext_person.g_chg_input_value_id))
and iv.input_value_id = c_input_value_id
and iv.element_type_id = c_element_type_id
and rr.run_result_id = rv.run_result_id
and rv.input_value_id = iv.input_value_id
and rv.result_value is not null
and p_effective_date between iv.effective_start_date
and iv.effective_end_date ;
select et.element_name element_name,
et.element_type_id element_id,
et.reporting_name reporting_name,
et.description description,
et.classification_id class_id,
ec.classification_name class_name,
et.processing_type process_type,
et.input_currency_code input_currency,
et.output_currency_code output_currency,
et.formula_id skip_rule_id -- skip rule
from pay_element_types_f et,
pay_element_classifications ec
where et.element_type_id = c_element_type_id
and et.classification_id = ec.classification_id
and p_effective_date between nvl(et.effective_start_date,p_effective_date)
and nvl(et.effective_end_date ,p_effective_date);
select ff.formula_name skip_rule_name
from ff_formulas_f ff
where ff.formula_id = c_formula_id
and p_effective_date between ff.effective_start_date
and ff.effective_end_date ;