[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;
select 'Y'
from pay_payroll_actions pya
, pay_assignment_actions asa
where pya.action_type = 'Q'
and pya.payroll_action_id = asa.payroll_action_id
and asa.assignment_action_id = p_assignment_action_id;
select 'Y'
from dual
where exists (select 'Y'
from pay_element_entries_f
where element_entry_id = p_rec.element_entry_id);
select pya.date_earned
from
pay_element_types_f et
, pay_element_links_f el
, pay_element_entries_f ent
, pay_payroll_actions pya
, pay_assignment_actions aga
where ent.element_entry_id = p_rec.element_entry_id
and ent.effective_start_date <= pya.date_earned
and ent.effective_end_date >= decode(et.proration_group_id, null, pya.date_earned,
pay_interpreter_pkg.prorate_start_date
(aga.assignment_action_id,
et.proration_group_id
))
and ent.element_link_id = el.element_link_id
and el.element_type_id = et.element_type_id
and pya.date_earned between el.effective_start_date
and el.effective_end_date
and pya.date_earned between et.effective_start_date
and et.effective_end_date
and pya.payroll_action_id = aga.payroll_action_id
and aga.assignment_action_id = p_rec.assignment_action_id;
select 'Y'
from
pay_element_types_f et
, pay_element_links_f el
, pay_element_entries_f ent
, pay_payroll_actions pya
, pay_assignment_actions aga
where ent.element_entry_id = p_rec.element_entry_id
and ent.assignment_id = aga.assignment_id
and ent.effective_start_date <= pya.date_earned
and ent.effective_end_date >= decode(et.proration_group_id, null, pya.date_earned,
pay_interpreter_pkg.prorate_start_date
(aga.assignment_action_id,
et.proration_group_id
))
and ent.element_link_id = el.element_link_id
and el.element_type_id = et.element_type_id
and pya.date_earned between el.effective_start_date
and el.effective_end_date
and pya.date_earned between et.effective_start_date
and et.effective_end_date
and pya.payroll_action_id = aga.payroll_action_id
and aga.assignment_action_id = p_rec.assignment_action_id;
select 'Y'
from pay_element_types_f ety
, pay_element_links_f elk
, pay_element_entries_f ent
where /* Element Types */
ety.process_in_run_flag = 'Y'
and ety.element_type_id = elk.element_type_id
and p_date_earned between ety.effective_start_date
and ety.effective_end_date
/* Element Links */
and elk.element_link_id = ent.element_link_id
and p_date_earned between elk.effective_start_date
and elk.effective_end_date
/* Element Entries */
and ent.effective_start_date <= p_date_earned
and ent.effective_end_date >= decode(ety.proration_group_id, null, p_date_earned,
pay_interpreter_pkg.prorate_start_date
(p_rec.assignment_action_id,
ety.proration_group_id
))
and ent.element_entry_id = p_rec.element_entry_id;
select 'Y'
from pay_element_entries_f
where p_date_earned between effective_start_date
and effective_end_date
and entry_type in ('B', 'A', 'R')
and element_entry_id = p_rec.element_entry_id;
select 'Y'
from pay_element_types_f ety
, pay_element_links_f elk
, pay_element_entries_f ent
where /*
* Element Types
*/
ety.element_type_id = elk.element_type_id
and p_date_earned between ety.effective_start_date
and ety.effective_end_date
/*
* Element Links
*/
and elk.element_link_id = ent.element_link_id
and p_date_earned between elk.effective_start_date
and elk.effective_end_date
/*
* Element Entries, further checks
*/
and ent.element_entry_id = p_rec.element_entry_id
and ent.effective_start_date <= p_date_earned
and ent.effective_end_date >= decode(ety.proration_group_id, null, p_date_earned,
pay_interpreter_pkg.prorate_start_date
(p_rec.assignment_action_id,
ety.proration_group_id
))
/*
* Non-recurring entries can only be included if they have not
* been processed.
*/
and ( ( ( (ety.processing_type = 'N'
)
/*
* Recurring, additional or override entries can only be
* included if they have not been processed. (These types of
* recurring entry are handled as if they were non-recurring.)
*/
or ( ety.processing_type = 'R'
and ent.entry_type <> 'E'
)
)
and (not exists (select null
from pay_run_results pr1
where pr1.source_id = ent.element_entry_id
and pr1.source_type = 'E'
and pr1.status in ('P', 'PA')
and not exists (select ''
from pay_run_results pr2
where pr2.source_id = pr1.run_result_id
and pr2.source_type = 'R'
)
)
)
)
/*
* Include other recurring entries.
* i.e. Those which are not additional or overrides entries.
*/
or ( ety.processing_type = 'R'
and ent.entry_type = 'E'
)
);
select 'Y'
from pay_quickpay_inclusions
where element_entry_id = p_rec.element_entry_id
and assignment_action_id = p_rec.assignment_action_id;
select 'Y'
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id
and action_status in ('C', 'S');
select 'Y'
from pay_payroll_actions pya
, pay_assignment_actions aga
where aga.assignment_action_id = p_assignment_action_id
and pya.payroll_action_id = aga.payroll_action_id
and pya.current_task is not null;
procedure insert_dml(p_rec in out nocopy pay_quickpay_inclusions%ROWTYPE) is
--
v_proc varchar2(72) := g_package||'insert_dml';
insert into pay_quickpay_inclusions
(element_entry_id
,assignment_action_id
)
values
(p_rec.element_entry_id
,p_rec.assignment_action_id
);
end insert_dml;
Procedure delete_dml(p_rec in pay_quickpay_inclusions%ROWTYPE) is
--
v_proc varchar2(72) := g_package||'delete_dml';
delete from pay_quickpay_inclusions
where element_entry_id = p_rec.element_entry_id
and assignment_action_id = p_rec.assignment_action_id;
End delete_dml;
select *
from pay_quickpay_inclusions
where element_entry_id = p_element_entry_id
and assignment_action_id = p_assignment_action_id
for update nowait;
procedure insert_validate(p_rec in pay_quickpay_inclusions%ROWTYPE) is
--
v_proc varchar2(72) := g_package||'insert_validate';
end insert_validate;
procedure delete_validate(p_rec in pay_quickpay_inclusions%ROWTYPE) is
--
v_proc varchar2(72) := g_package||'delete_validate';
end delete_validate;
insert_validate(p_rec);
insert_dml(p_rec);
insert into pay_quickpay_inclusions
(element_entry_id
,assignment_action_id)
select distinct
ent.element_entry_id
, asa.assignment_action_id
from pay_element_types_f ety
, pay_element_links_f elk
, pay_element_entries_f ent
, pay_payroll_actions pya
, pay_assignment_actions asa
where /*
* Element Type:
* Only include those which can be processed in the run.
*/
ety.process_in_run_flag = 'Y'
and ety.element_type_id = elk.element_type_id
and pya.date_earned between ety.effective_start_date
and ety.effective_end_date
/*
* Element Link:
* Only include those that exist as of QuickPay date earned.
*/
and elk.element_link_id = ent.element_link_id
and pya.date_earned between elk.effective_start_date
and elk.effective_end_date
/*
* Element Entry:
* Do not include balance adjustment, replacement adjustment
* or additive adjustment.
*/
and ent.entry_type not in ('B', 'A', 'R')
and ent.assignment_id = asa.assignment_id
and ent.effective_start_date <= pya.date_earned
and ent.effective_end_date >= decode(ety.proration_group_id, null, pya.date_earned,
pay_interpreter_pkg.prorate_start_date
(asa.assignment_action_id,
ety.proration_group_id
))
/*
* Non-recurring entries can only be included if they have not
* been processed.
*/
and ( ( ( (ety.processing_type = 'N'
)
/*
* Recurring, additional or override entries can only be
* included if they have not been processed. (These types of
* recurring entry are handled as if they were non-recurring.)
*/
or ( ety.processing_type = 'R'
and ent.entry_type <> 'E'
)
)
and (not exists (select null
from pay_run_results pr1
where pr1.source_id = ent.element_entry_id
and pr1.source_type = 'E'
and pr1.status in ('P', 'PA')
and not exists (select ''
from pay_run_results pr2
where pr2.source_id = pr1.run_result_id
and pr2.source_type = 'R'
)
)
)
)
/*
* Include other recurring entries.
* i.e. Those which are not additional or overrides entries.
*/
or ( ety.processing_type = 'R'
and ent.entry_type = 'E'
)
)
/*
* Payroll Action:
* Ensure the action is for a QuickPay Run.
*/
and pya.action_type = 'Q'
and pya.payroll_action_id = asa.payroll_action_id
/*
* Assignment Action:
*/
and asa.assignment_action_id = p_assignment_action_id;
delete_validate(p_rec);
delete_dml(p_rec);