[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
The Insertion of data into the PER_ASSIGNMENT_EXTRA_INFO and Submition
and completion of the Payroll Simulation Concurrent Program
History
02 Feb 2013 apudiped Initial Version Created
04 Feb 2013 apudiped Removed GSCC errors
04 Feb 2013 apudiped Removed Debug Messages
06 Feb 2013 apudiped Removed Element Set Id
reference to the Concurrent
program of Simulation
11 Feb 2013 apudiped 16305540 Modified check_eligibilty
19 Feb 2013 apudiped Modified Check Eligibility
28 Feb 2013 apudiped Added check Actions
04 Apr 2013 apudiped Added Validate Input Values
25 Apr 2013 apudiped 16691978 Added nvl for date earned and
effective date for costing
and prepayments check
*/
PROCEDURE clearpreviousdata
(p_assignment_id IN NUMBER)
is
begin
DELETE FROM pay_simulation_information
WHERE assignment_id = p_assignment_id
AND ACTION_CONTEXT_TYPE = 'ERROR';
DELETE FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND aei_information_category like '%/_SIMULATION/_REGION_' escape '/';
PROCEDURE insert_row
(p_assignment_id IN varchar2
, p_info_category IN varchar2
, p_info_type IN varchar2
, p_val1 IN varchar2
, p_val2 IN varchar2
, p_val3 IN varchar2
, p_val4 IN varchar2
, p_val5 IN varchar2
, p_val6 IN varchar2
, p_val7 IN varchar2
, p_val8 IN varchar2
, p_val9 IN varchar2
, p_val10 IN varchar2
, p_val11 IN varchar2
, p_val12 IN varchar2
, p_val13 IN varchar2
, p_val14 IN varchar2
, p_val15 IN varchar2
, p_val16 IN varchar2
, p_val17 IN varchar2
, p_val18 IN varchar2 , p_val19 IN varchar2
, p_val20 IN varchar2
, p_val21 IN varchar2
, p_val22 IN varchar2
, p_val23 IN varchar2
, p_val24 IN varchar2
, p_val25 IN varchar2
, p_val26 IN varchar2
, p_val27 IN varchar2
, p_val28 IN varchar2
, p_val29 IN varchar2
, p_val30 IN varchar2
, p_del_flag IN varchar2) IS
BEGIN
IF p_del_flag = 'true' THEN
DELETE
FROM per_assignment_extra_info
WHERE assignment_id = p_assignment_id
AND aei_information_category = p_info_category;
INSERT
INTO per_assignment_extra_info (assignment_extra_info_id
, assignment_id
, information_type
, aei_information_category
, aei_information1
, aei_information2
, aei_information3
, aei_information4
, aei_information5
, aei_information6
, aei_information7
, aei_information8
, aei_information9
, aei_information10
, aei_information11
, aei_information12
, aei_information13
, aei_information14
, aei_information15
, aei_information16
, aei_information17
, aei_information18
, aei_information19
, aei_information20
, aei_information21
, aei_information22
, aei_information23
, aei_information24
, aei_information25
, aei_information26
, aei_information27
, aei_information28
, aei_information29
, aei_information30)
VALUES (per_assignment_extra_info_s.nextval
, p_assignment_id
, p_info_category
, p_info_category
, p_val1
, p_val2
, p_val3
, p_val4
, p_val5
, p_val6
, p_val7
, p_val8
, p_val9
, p_val10
, p_val11
, p_val12
, p_val13
, p_val14
, p_val15
, p_val16
, p_val17
, p_val18
, p_val19
, p_val20
, p_val21
, p_val22
, p_val23
, p_val24
, p_val25
, p_val26
, p_val27
, p_val28
, p_val29
, p_val30);
END insert_row;
SELECT full_name
INTO l_name
FROM per_all_people_f
WHERE person_id = p_person_id
AND sysdate
BETWEEN effective_start_date
AND effective_end_date
AND rownum = 1;
FUNCTION insert_assignment_set
(p_assignment_id IN number
, p_business_group_id IN number
, p_payroll_id IN number) RETURN number IS
l_assignment_set_id number;
SELECT hr_assignment_sets_s.nextval
INTO l_assignment_set_id
FROM sys.dual;
INSERT
INTO hr_assignment_sets (assignment_set_id
, business_group_id
, payroll_id
, assignment_set_name)
VALUES (l_assignment_set_id
, p_business_group_id
, p_payroll_id
, l_assignment_set_name);
INSERT
INTO hr_assignment_set_amendments (assignment_id
, assignment_set_id
, include_or_exclude)
VALUES (p_assignment_id
, l_assignment_set_id
, 'I');
END insert_assignment_set;
SELECT ptp.start_date
, ptp.end_date
, ptp.regular_payment_date
FROM pay_all_payrolls_f papf
, per_time_periods ptp
WHERE papf.payroll_id = p_payroll_id
AND trunc(p_run_date)
BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.payroll_id = ptp.payroll_id
AND trunc(p_run_date)
BETWEEN ptp.start_date
AND ptp.end_date;
SELECT 1
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND ppa.action_type IN ('C', 'P', 'U')
AND nvl(ppa.date_earned,ppa.effective_date)
BETWEEN p_start_date
AND p_end_date;
select legislation_code
from per_business_groups
where business_group_id = p_business_group_id;
l_assignment_set_id := insert_assignment_set (p_assignment_id, p_business_group_id
, p_payroll_id);
SELECT 1
FROM pay_simulation_information
WHERE assignment_id = p_assignment_id
AND action_context_type = 'ERROR';
SELECT payroll_action_id,assignment_set_id
from pay_payroll_actions
where request_id = p_request_id;
SELECT lookup_code
INTO l_phase_code
FROM fnd_lookups
WHERE lookup_type = 'CP_PHASE_CODE'
AND meaning = l_phase;
SELECT lookup_code
INTO l_status_code
FROM fnd_lookups
WHERE lookup_type = 'CP_STATUS_CODE'
AND meaning = l_status;
delete from hr_assignment_set_amendments where assignment_set_id = l_assignment_set_id;
delete from hr_assignment_sets where assignment_set_id = l_assignment_set_id;
SELECT application_column_name
FROM fnd_descr_flex_column_usages
WHERE descriptive_flexfield_name = 'Payroll Developer DF'
AND descriptive_flex_context_code = 'Global Data Elements'
AND END_USER_COLUMN_NAME = 'Modeling Availability Rule';
SELECT application_column_name
FROM fnd_descr_flex_column_usages
WHERE descriptive_flexfield_name = 'Payroll Developer DF'
AND descriptive_flex_context_code = 'Global Data Elements'
AND END_USER_COLUMN_NAME = 'Days After Period Start';
SELECT application_column_name
FROM fnd_descr_flex_column_usages
WHERE descriptive_flexfield_name = 'Payroll Developer DF'
AND descriptive_flex_context_code = 'Global Data Elements'
AND END_USER_COLUMN_NAME = 'Days Before Period End';
open c_avail_ref for 'select '||l_col_rule||'
from pay_payrolls_f ppf, per_Assignments_f paf
where paf.assignment_id = '||p_assignment_id||' '||'
and trunc(sysdate) between paf.effective_Start_Date and paf.effective_end_date
and paf.payroll_id = ppf.payroll_id
and trunc(sysdate) between ppf.effective_start_Date and ppf.effective_end_date';
open c_ref for 'select 1
from pay_payrolls_f ppf, per_Assignments_f paf, per_time_periods ptp
where paf.assignment_id = '||p_assignment_id||' '||'
and trunc(sysdate) between paf.effective_Start_Date and paf.effective_end_date
and paf.payroll_id = ppf.payroll_id
and trunc(sysdate) between ppf.effective_start_Date and ppf.effective_end_date
and ptp.payroll_id = ppf.payroll_id
and trunc(sysdate) >= ptp.start_date + nvl(to_number('||l_from_date||'),0)
and trunc(sysdate) <= ptp.end_date - nvl(to_number('||l_to_date||'),0)
and trunc(sysdate) between ptp.start_Date and ptp.end_date';
open c_ref for 'select 1
from pay_payrolls_f ppf, per_Assignments_f paf, per_time_periods ptp
where paf.assignment_id = '||p_assignment_id||' '||'
and trunc(sysdate) between paf.effective_Start_Date and paf.effective_end_date
and paf.payroll_id = ppf.payroll_id
and trunc(sysdate) between ppf.effective_start_Date and ppf.effective_end_date
and ptp.payroll_id = ppf.payroll_id
and trunc(sysdate) >= ptp.start_date + nvl(to_number('||l_from_date||'),0)
and trunc(sysdate) <= ptp.end_date - nvl(to_number('||l_to_date||'),0)
and trunc(sysdate) between ptp.start_Date and ptp.end_date';
SELECT ptp.start_date
, ptp.end_date
, ptp.regular_payment_date
FROM pay_all_payrolls_f papf
, per_time_periods ptp
, per_all_assignments_f paf
WHERE papf.payroll_id = paf.payroll_id
AND paf.assignment_id = p_assignment_id
AND trunc(sysdate)
BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.payroll_id = ptp.payroll_id
AND trunc(sysdate)
BETWEEN ptp.start_date
AND ptp.end_date;
SELECT 1
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = p_assignment_id
AND ppa.action_type IN ('C', 'P', 'U')
AND nvl(ppa.date_earned,ppa.effective_date)
BETWEEN p_start_date
AND p_end_date;
select 1
from per_business_groups_perf pbg,
per_all_assignments_f asg,
per_all_assignments_f as2,
per_periods_of_service pos
where asg.assignment_id = p_assignment_id
and pos.period_of_service_id = asg.period_of_service_id
and l_date_paid between
asg.effective_start_date and asg.effective_end_date
and as2.assignment_id = asg.assignment_id
and l_date_paid between
as2.effective_start_date and as2.effective_end_date
and pbg.business_group_id = asg.business_group_id
and not exists (
select /*+ INDEX (pa2 PAY_PAYROLL_ACTIONS_PK)
INDEX (ac2 PAY_ASSIGNMENT_ACTIONS_N1) */ null
from pay_action_classifications acl,
pay_assignment_actions ac2,
pay_payroll_actions pa2,
per_all_assignments_f as2
where as2.period_of_service_id = pos.period_of_service_id
and ac2.assignment_id = as2.assignment_id
and pa2.payroll_action_id = ac2.payroll_action_id
and acl.classification_name = 'SEQUENCED'
and pa2.action_type = acl.action_type
and (pa2.effective_date > l_date_paid
or (ac2.action_status not in ('C', 'S')
and pa2.effective_date <= l_date_paid)));
select pet.element_name||'-'||inv.name,inv.uom,inv.lookup_type,inv.value_set_id,inv.min_value,inv.max_value,pet.input_currency_code
from pay_input_values_f inv,pay_element_types_f pet
where inv.input_value_id = p_input_value_id
and trunc(sysdate) between inv.effective_start_date
and inv.effective_end_date
and inv.element_type_id = pet.element_type_id
and trunc(sysdate) between pet.effective_start_date
and pet.effective_end_date;
select ACTION_INFORMATION1
from pay_simulation_information
where assignment_id = p_assignment_id
and ACTION_CONTEXT_TYPE = 'INPUTSLOV'
and ACTION_INFORMATION3 = to_char(p_input_value_id);