[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ppa.effective_date
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id;
SELECT element_reporting_name
,classification_name
,amount
,round(hours,2) hours
FROM pay_kr_asg_elements_v
WHERE assignment_action_id = p_pre_assignment_action_id
ORDER BY element_reporting_name;
SELECT pap.accrual_plan_id accrual_plan_id
,pap.business_group_id business_group_id
,pap.accrual_plan_element_type_id accrual_plan_element_type_id
,pap.accrual_plan_name accrual_plan_name
,pap.accrual_category accrual_category
,pap.accrual_start accrual_start_date
,ppa.payroll_id payroll_id
,pap.accrual_units_of_measure accrual_units_of_measure
,hoi.org_information13 leave_taken_dim
,pac.assignment_id
FROM pay_accrual_plans pap
,pay_assignment_actions pac
,pay_payroll_actions ppa
,hr_organization_information hoi
,pay_element_links_f pel
,pay_element_entries_f pee
,pay_element_types_f pet
WHERE pac.assignment_action_id = p_run_assignment_action_id
AND pac.assignment_id = p_assignment_id
AND pac.payroll_action_id = ppa.payroll_action_id
/* AND pel.payroll_id = ppa.payroll_id */ -- Bug 2891590
AND ppa.action_type IN ('R','Q')
AND ppa.action_status = 'C'
AND pel.element_type_id = pet.element_type_id
AND pee.element_link_id = pel.element_link_id
AND pee.assignment_id = pac.assignment_id
AND pet.element_type_id = pap. accrual_plan_element_type_id
AND pac.tax_unit_id = hoi.organization_id
AND hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION'
AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND ppa.effective_date BETWEEN pel.effective_start_date AND pel.effective_end_date
AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
hr_utility.trace(' Inserting the multiple run types message into PAY_MESSAGE_LINES table');
INSERT INTO pay_message_lines(line_sequence,
payroll_id,
message_level,
source_id,
source_type,
line_text)
VALUES(
p_line_sequence
,p_payroll_id
,p_message_level
,p_arch_assignment_action_id
,p_source_type
,l_message_text
);
SELECT paa.assignment_id
,hr_general.decode_lookup('KR_JOB_TITLE',hsck.segment2) kr_job_title
,hr_general.decode_lookup('KR_SENIORITY',hsck.segment3) kr_seniority
,pkg.grade_name second_grade
,pgp.grade_point_name grade_point
FROM hr_soft_coding_keyflex hsck
,per_assignments_f paa
,per_kr_grades pkg
,per_kr_g_points pgp
WHERE hsck.soft_coding_keyflex_id(+) = paa.soft_coding_keyflex_id
AND paa.assignment_id = p_assignment_id
AND pkg.grade_id (+) = hsck.segment4 -- This segement4 stores the grade_id
AND pgp.grade_point_id (+) = hsck.segment5 -- This segement5 stores the grade point id
AND p_run_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date;
SELECT prtl.run_type_name
FROM pay_run_types_f_vl prtl
WHERE prtl.run_type_id = p_run_type_id;
SELECT pai.action_information_id
,pai.action_information5
,nvl(hr_general_utilities.get_lookup_Meaning('KR_BANK',substr(pai.action_information5,1,2)),' ') bank_name
FROM pay_action_information pai
WHERE action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
AND action_context_id = p_assignment_action_id
AND action_context_type = 'AAP';
SELECT pai.action_information_id
,pai.action_information18
FROM pay_action_information pai
WHERE action_information_category = 'EMPLOYEE DETAILS'
AND action_context_id = p_assignment_action_id
AND action_context_type = 'AAP';
SELECT hoi.org_information1 business_place
FROM hr_organization_information hoi
WHERE hoi.organization_id = p_tax_unit_id
AND hoi.org_information_context = 'KR_BUSINESS_PLACE_REGISTRATION';
pay_action_information_api.update_action_information (
p_action_information_id => net_pay_rec.action_information_id
,p_object_version_number => l_ovn
,p_action_information9 => net_pay_rec.bank_name );
pay_action_information_api.update_action_information (
p_action_information_id => emp_rec.action_information_id
,p_object_version_number => l_ovn
,p_action_information18 => l_kr_business_place);
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 prepayment_effective_date,
paa_arch_chd.assignment_id assignment_id,
ppa_run.effective_date run_effective_date,
ppa_run.date_earned date_earned,
ptp.end_date period_end_date,
ptp.time_period_id time_period_id,
paa_run.payroll_action_id run_payroll_action_id,
ppa_run.payroll_id payroll_id,
ppa_run.run_type_id run_type_id,
paa_run.tax_unit_id tax_unit_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
);
SELECT ptp.end_date end_date,
ptp.regular_payment_date regular_payment_date,
ptp.time_period_id time_period_id
FROM per_time_periods ptp
,pay_payroll_actions ppa
WHERE ptp.payroll_id = ppa.payroll_id
AND ppa.payroll_action_id = ( SELECT payroll_action_id
FROM pay_assignment_actions
WHERE assignment_action_id =p_assignment_action_id)
AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
SELECT pps.actual_termination_date
FROM per_all_assignments_f paf,
per_periods_of_service pps,
pay_assignment_actions paa,
per_time_periods ptp,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_assignment_action_id
AND ptp.payroll_id = ppa.payroll_id
AND paf.assignment_id = paa.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND pps.period_of_service_id = paf.period_of_service_id
AND pps.actual_termination_date between paf.effective_start_date and paf.effective_end_date
AND pps.actual_termination_date between ptp.start_date AND ptp.end_date;
SELECT pay_message_lines_s.nextval
FROM dual;