[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select element_reporting_name /* Modified for bug 4753806 summed up hours and payment using group by */
, classification_name
, SUM(payment) payment
, SUM( hours ) hours
, rate /* 5914696 */
from pay_au_asg_element_payments_v
where assignment_action_id = p_assignment_action_id
and classification_name is not null
group by element_reporting_name,rate,classification_name;
select ap.accrual_plan_id
, ap.accrual_plan_name
, hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',ap.accrual_category) accrual_category
, ap.accrual_units_of_measure
, pa.payroll_id payroll_id
, ap.business_group_id business_group_id
from pay_accrual_plans ap
, pay_element_types_f et
, pay_element_links_f el
, pay_element_entries_f ee
, pay_assignment_actions aa
, pay_payroll_actions pa
where et.element_type_id = ap.accrual_plan_element_type_id -- select the accrual plan elements
and el.element_type_id = et.element_type_id -- select accrual plan element entries
and aa.assignment_id = ee.assignment_id -- select element entries for this assignment
and ee.element_link_id = el.element_link_id -- join element to element entries via element links
and pa.payroll_action_id = aa.payroll_action_id -- need the payroll action to check the action_type
and pa.action_type in ('R','Q') -- select only payroll/quikpay runs
and pa.action_status = 'C' -- select only successfully completed runs
and pa.date_earned between et.effective_start_date and et.effective_end_date
and pa.date_earned between el.effective_start_date and el.effective_end_date
and pa.date_earned between ee.effective_start_date and ee.effective_end_date
and ap.accrual_category = 'AUAL' -- select only annual leave accrual plans
and aa.assignment_action_id = p_assignment_action_id
;
/* Bug 5036580- Modified procedure to fetch and insert values for absence based on split views .
l_exists and tab_row declared to ensure duplicate rows fetched from different views are not inserted again
into pay_action_information */
procedure archive_absences
(p_assignment_id in pay_assignment_actions.assignment_id%type
,p_pre_effective_date in pay_payroll_actions.effective_date%type
,p_time_period_id in per_time_periods.time_period_id%type
,p_arc_assignment_action_id in pay_assignment_actions.assignment_action_id%type
,p_run_assignment_action_id in pay_assignment_actions.assignment_action_id%type
) is
cursor csr_leave_taken1
(p_time_period_id per_time_periods.time_period_id%type
,p_assignment_id pay_assignment_actions.assignment_id%type
) is
select row_id
, element_reporting_name
, start_date
, end_date
, absence_hours
, payment
from pay_au_asg_leave_taken_v1
where time_period_id = p_time_period_id
and assignment_id = p_assignment_id
and action_type IN ('R','Q','V');
select row_id,
element_reporting_name
, start_date
, end_date
, absence_hours
, payment
from pay_au_asg_leave_taken_v2
where time_period_id = p_time_period_id
and assignment_id = p_assignment_id
and action_type IN ('R','Q','V');
select row_id,element_reporting_name
, start_date
, end_date
, absence_hours
, payment
from pay_au_asg_leave_taken_v3
where time_period_id = p_time_period_id
and assignment_id = p_assignment_id
and action_type IN ('R','Q','V');
select row_id,element_reporting_name
, start_date
, end_date
, absence_hours
, payment
from pay_au_asg_leave_taken_v4
where time_period_id = p_time_period_id
and assignment_id = p_assignment_id;
select row_id,element_reporting_name
, start_date
, end_date
, absence_hours
, payment
from pay_au_asg_leave_taken_v5
where time_period_id = p_time_period_id
and assignment_id = p_assignment_id
and action_type IN ('R','Q','V');
select row_id,element_reporting_name
, start_date
, end_date
, absence_hours
, payment
from pay_au_asg_leave_taken_v6
where time_period_id = p_time_period_id
and assignment_id = p_assignment_id
and action_type IN ('R','Q','V');
select row_id,element_reporting_name
, start_date
, end_date
, absence_hours
, payment
from pay_au_asg_leave_taken_v7
where time_period_id = p_time_period_id
and assignment_id = p_assignment_id
and action_type IN ('R','Q','V');
select pea.segment1 seg1,
pea.segment2 seg2,
pea.segment3 seg3,
pea.segment4 seg4,
pea.segment5 seg5,
pea.segment6 seg6,
pea.segment7 seg7,
pea.segment8 seg8,
pea.segment9 seg9,
pea.segment10 seg10,
ppp.value amount,
ppp.pre_payment_id,
popm.org_payment_method_id,
popm.org_payment_method_name,
pppm.personal_payment_method_id
from pay_assignment_actions paa,
pay_pre_payments ppp,
pay_org_payment_methods_f popm ,
pay_personal_payment_methods_f pppm,
pay_external_accounts pea
where paa.assignment_action_id = cp_pre_pay_action_id
and ppp.assignment_action_id = paa.assignment_action_id
and paa.assignment_id = cp_assignment_id
and ( ( ppp.source_action_id is null
and cp_ppp_source_action_id is null)
or
-- is it a Normal or Process Separate specific
-- Payments should be included in the Standard
-- SOE. Only Separate Payments should be in
-- a Separate SOE.
(ppp.source_action_id is not null
and cp_ppp_source_action_id is null
and exists (
select ''
from pay_run_types_f prt,
pay_assignment_actions paa_run,
pay_payroll_actions ppa_run
where paa_run.assignment_action_id
= ppp.source_action_id
and paa_run.payroll_action_id
= ppa_run.payroll_action_id
and paa_run.run_type_id = prt.run_type_id
and prt.run_method in ('P', 'N')
and ppa_run.effective_date
between prt.effective_start_date
and prt.effective_end_date
)
)
or
(cp_ppp_source_action_id is not null
and ppp.source_action_id = cp_ppp_source_action_id)
)
and ppp.org_payment_method_id = popm.org_payment_method_id
and popm.defined_balance_id is not null
and pppm.personal_payment_method_id(+)
= ppp.personal_payment_method_id
and pea.external_account_id(+) = pppm.external_account_id
and cp_curr_pymt_eff_date between popm.effective_start_date
and popm.effective_end_date
and cp_curr_pymt_eff_date between nvl(pppm.effective_start_date,
cp_curr_pymt_eff_date)
and nvl(pppm.effective_end_date,
cp_curr_pymt_eff_date)
/* Bug 6962336 - Add NOT EXISTS Clause */
AND NOT EXISTS
( SELECT pai.action_information_id
FROM pay_action_information pai
WHERE pai.action_context_id = cp_action_context_id
AND pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
AND pai.action_context_type = 'AAP'
AND pai.action_information15 = ppp.pre_payment_id
);
select tax_unit_id
from pay_assignment_actions
where assignment_action_id = p_assignment_action_id;
select org.org_information12 abn
from per_assignments_f paaf
, hr_soft_coding_keyflex flex
, hr_organization_information org
where paaf.soft_coding_keyflex_id = flex.soft_coding_keyflex_id
and to_char(org.organization_id) = flex.segment1
and org.org_information_context = 'AU_LEGAL_EMPLOYER'
and paaf.assignment_id = p_assignment_id
and p_effective_date between paaf.effective_start_date and paaf.effective_end_date;
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa,
pay_action_interlocks pai,
pay_run_types_f prt
WHERE pai.locking_action_id = p_prepay_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.source_action_id = p_source_action_id
and paa.run_type_id = prt.run_type_id;
select nvl(ptp.default_dd_date,p_pre_effective_date)
from pay_payroll_actions ppa,
per_time_periods ptp,
pay_assignment_actions paa
where p_run_date_earned between ptp.start_date and ptp.end_date
and paa.assignment_action_id=p_run_assignment_action_id
and paa.payroll_action_id=ppa.payroll_action_id
and ppa.payroll_id=ptp.payroll_id;
select distinct business_group_id
from per_all_assignments_f
where assignment_id=p_assignment_id
and p_effective_date between effective_start_date and effective_end_date;
select count(*)
from per_spinal_point_steps_f psp,
per_spinal_point_placements_f pspp,
per_spinal_point_steps_f psp2
where psp.sequence>= psp2.sequence
and pspp.step_id=psp.step_id
and pspp.assignment_id=p_assignment_id
and psp.grade_spine_id=psp2.grade_spine_id
and pspp.business_group_id=p_business_group_id
and psp.business_group_id=p_business_group_id
and psp2.business_group_id=p_business_group_id
and p_effective_date between
psp.effective_start_date and psp.effective_end_date
and p_effective_date between
psp2.effective_start_date and psp2.effective_end_date
and p_effective_date between
pspp.effective_start_date and pspp.effective_end_date;
procedure insert_quickpay_pa_info
(p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE
,p_effective_date in pay_action_information.effective_date%TYPE
) is
cursor csr_get_payroll_id
(p_payroll_action_id pay_payroll_actions.payroll_id%TYPE)
is
select payroll_id
from pay_payroll_actions paa
where paa.payroll_action_id = p_payroll_action_id;
END insert_quickpay_pa_info;
select payroll_action_id
from pay_assignment_actions paa
where paa.assignment_action_id = p_aa_id;
select count(*)
from pay_action_information pai
where pai.action_context_type = 'PA'
and pai.action_information_category = 'ADDRESS DETAILS'
and pai.action_information14 = 'Employer Address'
and pai.action_context_id = p_payroll_action_id
and pai.effective_date = p_effective_date;
insert_quickpay_pa_info
(p_payroll_action_id => l_payroll_action_id
,p_effective_date => p_effective_date);
select paa_arch_chd.assignment_action_id chld_arc_assignment_action_id,
paa_pre.assignment_action_id pre_assignment_action_id,
paa_run.assignment_action_id run_assignment_action_id,
ppa_pre.effective_date pre_effective_date,
paa_arch_chd.assignment_id,
ppa_run.effective_date run_effective_date,
ppa_run.date_earned run_date_earned,
ptp.regular_payment_date, /* 5681819 */ptp.end_date period_end_date,
ptp.time_period_id
from pay_assignment_actions paa_arch_chd,
pay_assignment_actions paa_arch_mst,
pay_assignment_actions paa_pre,
pay_action_interlocks pai_pre,
pay_assignment_actions paa_run,
pay_action_interlocks pai_run,
pay_payroll_actions ppa_pre,
pay_payroll_actions ppa_run,
per_time_periods ptp
where paa_arch_mst.assignment_action_id = p_master_aa_id
and paa_arch_chd.source_action_id = paa_arch_mst.assignment_action_id
and paa_arch_chd.payroll_action_id = paa_arch_mst.payroll_action_id
and paa_arch_chd.assignment_id = paa_arch_mst.assignment_id
and pai_pre.locking_action_id = paa_arch_mst.assignment_action_id
and pai_pre.locked_action_id = paa_pre.assignment_action_id
and pai_run.locking_action_id = paa_arch_chd.assignment_action_id
and pai_run.locked_action_id = paa_run.assignment_action_id
and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
and ppa_pre.action_type in ('P','U')
and ppa_run.payroll_action_id = paa_run.payroll_action_id
and ppa_run.action_type in ('R','Q')
and ptp.payroll_id = ppa_run.payroll_id
and ppa_run.date_earned between ptp.start_date
and ptp.end_date
-- Get the highest in sequence for this payslip
and paa_run.action_sequence = (select max(paa_run2.action_sequence)
from pay_assignment_actions paa_run2,
pay_action_interlocks pai_run2
where pai_run2.locking_action_id =
paa_arch_chd.assignment_action_id
and pai_run2.locked_action_id =
paa_run2.assignment_action_id
);