[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select greatest(pa.effective_start_date,p_start_date) effective_start_date,
least(pa.effective_end_date,p_end_date) effective_end_date
from per_assignment_status_types past,
per_assignments_f pa
where pa.assignment_id = p_assignment_id
and pa.effective_end_date >= p_start_date
and pa.effective_start_date <= p_end_date
and past.assignment_status_type_id = pa.assignment_status_type_id
-- This enhancement was denied by core team.
--and past.past_information_context = 'KR'
--and past.past_information1 <> 'Y'
--and past.pay_system_status <> 'P'
and past.per_system_status <> 'ACTIVE_ASSIGN';
g_working_period.start_date.delete;
g_working_period.end_date.delete;
g_working_period.wkpd.delete;
select
nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbf.scale),0)
from pay_balance_feeds_f pbf,
pay_run_result_values prrv,
pay_run_results prr,
(select /*+ INDEX(ppa PAY_PAYROLL_ACTIONS_N5) */
paa.assignment_action_id assignment_action_id,
ppa.effective_date effective_date
from
-- per_time_periods ptp,
per_assignment_status_types past,
per_assignments_f pa,
pay_payroll_actions ppa,
pay_run_types_f prt,
pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.action_status = 'C'
and prt.run_type_id = paa.run_type_id
and p_effective_date
between prt.effective_start_date and prt.effective_end_date
and prt.run_type_name like decode(p_type,
'BON','BON%',
'ALR','BON_ALR',
'MTH')
and not exists(
select null
from pay_run_types_f prt2
where prt2.run_type_id = prt.run_type_id
and prt2.effective_start_date = prt.effective_start_date
and prt2.effective_end_date = prt.effective_end_date
and prt2.run_type_name = decode(p_type,
'BON','BON_ALR',
'XXX'))
and paa.action_sequence >= decode(p_type,
'MTH',p_action_sequence4,
'BON',paa.action_sequence)
and paa.action_sequence < p_base_action_sequence
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date
between p_target_start_date and p_target_end_date
and pa.assignment_id = paa.assignment_id
and ppa.effective_date
between pa.effective_start_date and pa.effective_end_date
and past.assignment_status_type_id = pa.assignment_status_type_id
/* Denied this enhancement by Core Team */
-- and past.past_information_context = 'KR'
-- and past.past_information1 = 'Y'
-- and past.pay_system_status = 'P'
and past.per_system_status = 'ACTIVE_ASSIGN') V1
-- and ptp.time_period_id = ppa.time_period_id) V1
where prr.assignment_action_id = V1.assignment_action_id
and prr.status in ('P','PA')
and prrv.run_result_id = prr.run_result_id
and nvl(prrv.result_value,'0') <> '0'
and pbf.balance_type_id = p_balance_type_id
and pbf.input_value_id = prrv.input_value_id
and V1.effective_date
between pbf.effective_start_date and pbf.effective_end_date;
select balance_type_id
from pay_balance_types
where legislation_code = g_legislation_code
and balance_name = 'EARNINGS_SUBJ_AVG';
select paa.assignment_action_id assignment_action_id,
paa.action_sequence action_sequence,
bpaa.assignment_id assignment_id,
bppa.effective_date effective_date,
bpaa.assignment_action_id base_assignment_action_id,
bpaa.action_sequence base_action_sequence,
to_date('01/04/2002','DD/MM/YYYY') base_start_date,
to_date('30/04/2002','DD/MM/YYYY') base_end_date,
ptp.start_date target_start_date,
ptp.end_date target_end_date
from per_time_periods ptp,
per_assignment_status_types past,
per_assignments_f pa,
pay_payroll_actions ppa,
pay_run_types_f prt,
pay_assignment_actions paa,
-- per_time_periods bptp,
pay_payroll_actions bppa,
pay_assignment_actions bpaa
where bpaa.assignment_action_id = p_assignment_action_id
and bppa.payroll_action_id = bpaa.payroll_action_id
-- and bptp.time_period_id = bppa.time_period_id
and paa.assignment_id = bpaa.assignment_id
and paa.action_status = 'C'
and prt.run_type_id = paa.run_type_id
and prt.run_type_name like decode(l_type,
'BON','BON%',
'ALR','BON_ALR',
'MTH')
and not exists(
select null
from pay_run_types_f prt2
where prt2.run_type_id = prt.run_type_id
and prt2.effective_start_date = prt.effective_start_date
and prt2.effective_end_date = prt.effective_end_date
and prt2.run_type_name = decode(l_type,
'BON','BON_ALR',
'XXX'))
and bppa.effective_date
between prt.effective_start_date and prt.effective_end_date
and paa.action_sequence < bpaa.action_sequence
and ppa.payroll_action_id = paa.payroll_action_id
-- and ppa.effective_date <= bptp.end_date
and ppa.effective_date >= decode(l_type,
'MTH',decode(p_performance_flag,
'N',ppa.effective_date,
add_months(bppa.effective_date -1,-12)),
'BON',add_months(bppa.effective_date -1,-12),
'ALR',add_months(bppa.effective_date -1,-12))
and ppa.action_type <> 'V'
and not exists(
select null
from pay_payroll_actions rppa,
pay_assignment_actions rpaa,
pay_action_interlocks pai
where pai.locked_action_id = paa.assignment_action_id
and rpaa.assignment_action_id = pai.locking_action_id
and rppa.payroll_action_id = rpaa.payroll_action_id
and rppa.action_type = 'V')
and pa.assignment_id = paa.assignment_id
and ppa.effective_date
between pa.effective_start_date and pa.effective_end_date
and past.assignment_status_type_id = pa.assignment_status_type_id
/* Denied this enhancement by Core Team */
-- and past.past_information_context = 'KR'
-- and past.past_information1 = 'Y'
-- and past.pay_system_status = 'P'
and past.per_system_status = 'ACTIVE_ASSIGN'
and ptp.time_period_id = ppa.time_period_id
order by paa.action_sequence desc;
g_mth_past_action.assignment_action_id.delete;
g_mth_past_action.action_sequence.delete;
g_mth_past_action.assignment_id.delete;
g_mth_past_action.effective_date.delete;
g_mth_past_action.base_assignment_action_id.delete;
g_mth_past_action.base_action_sequence.delete;
g_mth_past_action.base_start_date.delete;
g_mth_past_action.base_end_date.delete;
g_mth_past_action.target_start_date.delete;
g_mth_past_action.target_end_date.delete;
g_bon_past_action.assignment_action_id.delete;
g_bon_past_action.action_sequence.delete;
g_bon_past_action.assignment_id.delete;
g_bon_past_action.effective_date.delete;
g_bon_past_action.base_assignment_action_id.delete;
g_bon_past_action.base_action_sequence.delete;
g_bon_past_action.base_start_date.delete;
g_bon_past_action.base_end_date.delete;
g_bon_past_action.target_start_date.delete;
g_bon_past_action.target_end_date.delete;
g_alr_past_action.assignment_action_id.delete;
g_alr_past_action.action_sequence.delete;
g_alr_past_action.assignment_id.delete;
g_alr_past_action.effective_date.delete;
g_alr_past_action.base_assignment_action_id.delete;
g_alr_past_action.base_action_sequence.delete;
g_alr_past_action.base_start_date.delete;
g_alr_past_action.base_end_date.delete;
g_alr_past_action.target_start_date.delete;
g_alr_past_action.target_end_date.delete;