[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pay_element_type_code ,
person_id,
assignment_id
from pa_pay_ext_interface_details
where interface_header_id =l_header_id
and nvl(transfer_status_flag,'K') in ('P','R','K')
and person_id = nvl(x_person_id,person_id)
for update of rejection_code,
transfer_status_flag;
select interface_header_id,time_period_start_date,
time_period_end_date,payroll_batch_id,payroll_id,
payroll_name,business_group_id,business_group_name
from pa_pay_ext_interface_header
where time_period_start_date >=x_start_date and
time_period_end_date <=x_end_date
and payroll_id = nvl(x_payroll_id,payroll_id)
for update of rejection_code;
select payroll_name,interface_header_id
from pa_pay_ext_interface_header
where payroll_id is null
and time_period_start_date >=x_start_date and
time_period_end_date <=x_end_date
for update of payroll_id;
select payroll_id into l_payroll_id from pa_pay_external_payroll
where payroll_name = Pay_rec.payroll_name;
update pa_pay_ext_interface_header
set payroll_id = l_payroll_id
where payroll_name = Pay_rec.payroll_name
and interface_header_id = Pay_rec.interface_header_id
and payroll_id is null;
select business_group_id into l_group_id from hr_organization_units
where name = Hdr_rec.business_group_name;
Update pa_pay_ext_interface_header eih
set rejection_code = 'RC 150'
where interface_header_id = Hdr_rec.interface_header_id
and not exists (select null from hr_organization_units hou
where hou.business_group_id = nvl(Hdr_rec.business_group_id,l_group_id)
and hou.business_group_id = nvl(eih.business_group_id,l_group_id));
update pa_pay_ext_interface_details
set rejection_code = 'RC 150',
transfer_status_flag = 'R',
request_id = l_request_id
where interface_header_id = Hdr_rec.interface_header_id;
select payroll_id into l_payroll_id from pa_pay_external_payroll
where payroll_name = Hdr_rec.payroll_name;
Update pa_pay_ext_interface_header eih
set rejection_code = 'RC 155'
where interface_header_id = Hdr_rec.interface_header_id
and not exists (select null from pa_pay_external_payroll ppep
where ppep.business_group_id = nvl(Hdr_rec.business_group_id,l_group_id)
and ppep.payroll_id = nvl(eih.payroll_id,l_payroll_id)
and effective_start_date <= Hdr_rec.time_period_start_date
and nvl(effective_end_date,Hdr_rec.time_period_end_date+1) >= Hdr_rec.time_period_end_date
and active_flag = 'Y');
update pa_pay_ext_interface_details
set rejection_code = 'RC 155',
transfer_status_flag = 'R',
request_id = l_request_id
where interface_header_id = Hdr_rec.interface_header_id;
select count(1) into l_value_exists from pa_pay_ext_interface_header
where time_period_start_date = Hdr_rec.time_period_start_date
and time_period_end_date = Hdr_rec.time_period_end_date
and payroll_batch_id = Hdr_rec.payroll_batch_id
and payroll_id = nvl(Hdr_rec.payroll_id,l_payroll_id)
and business_group_id = NVL(Hdr_rec.business_group_id,l_group_id)
and rejection_code is null;
update pa_pay_ext_interface_header
set rejection_code = 'RC 160'
where current of Int_header_details;
update pa_pay_ext_interface_details
set rejection_code = 'RC 160',
transfer_status_flag = 'R',
request_id = l_request_id
where interface_header_id = Hdr_rec.interface_header_id;
select count(1) into l_value_exists from pa_pay_ext_interface_header
where payroll_id = nvl(Hdr_rec.payroll_id,l_payroll_id)
and business_group_id = NVL(Hdr_rec.business_group_id,l_group_id)
and rejection_code is null
and ((Hdr_rec.time_period_start_date between time_period_start_date and time_period_end_date)
OR (Hdr_rec.time_period_end_date between time_period_start_date and time_period_end_date)
OR (Hdr_rec.time_period_start_date <= time_period_start_date
and Hdr_rec.time_period_end_date >= time_period_end_date));
update pa_pay_ext_interface_header
set rejection_code = 'RC 165'
where current of Int_header_details;
update pa_pay_ext_interface_details
set rejection_code = 'RC 165',
transfer_status_flag = 'R',
request_id = l_request_id
where interface_header_id = Hdr_rec.interface_header_id;
select count(1) into l_value_exists from pa_lookups
where lookup_type = 'PA_THIRD_PARTY_PAY_ELEMENTS'
and lookup_code = Details_rec.pay_element_type_code
and enabled_flag = 'Y'
and NVL(predefined_flag,'O') IN ('E','I','S','T','L','O') /* Bug 12998980 */
--AND PAY_PERIOD_END_DATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE ,PAY_PERIOD_END_DATE);
update pa_pay_ext_interface_details
set rejection_code = 'RC 170',
transfer_status_flag ='R',
request_id = l_request_id
where current of Interface_details;
select count(1) into l_value_exists
from per_all_people_f
where person_id = Details_rec.PERSON_ID
and nvl(current_employee_flag, 'N') = 'Y'
and effective_start_date <= Hdr_rec.time_period_start_date
and nvl(effective_end_date,Hdr_rec.time_period_end_date+1) >= Hdr_rec.time_period_end_date;
update pa_pay_ext_interface_details
set rejection_code = 'RC 175',
transfer_status_flag = 'R' ,
request_id = l_request_id
where current of Interface_details ;
select count(1) into l_value_exists from
per_assignment_status_types s
, per_all_assignments_f a
WHERE
a.person_id = Details_rec.PERSON_ID
AND a.primary_flag = 'Y'
AND a.assignment_type in ('E', 'C')
AND a.assignment_status_type_id = s.assignment_status_type_id
AND s.per_system_status in ('ACTIVE_ASSIGN', 'ACTIVE_CWK','TERM_ASSIGN')
AND trunc(Hdr_rec.time_period_end_date) BETWEEN trunc( a.effective_start_date )
AND trunc( a.effective_end_date )
AND (( l_group_id2 = a.business_group_id+0) )
and a.assignment_id =Details_rec.assignment_id;
update pa_pay_ext_interface_details
set rejection_code = 'RC 180',
transfer_status_flag = 'R',
request_id = l_request_id
where current of Interface_details ;
/* Inserting a record into time period table */
select count(1) into l_value_exists from PA_PAY_EXTERNAL_TIME_PERIODS where
time_period_start_date = Hdr_rec.time_period_start_date
and time_period_end_date = Hdr_rec.time_period_end_date
and payroll_id = Hdr_rec.payroll_id;
Insert into PA_PAY_EXTERNAL_TIME_PERIODS(PAYROLL_ID,
TIME_PERIOD_ID,
TIME_PERIOD_START_DATE,
TIME_PERIOD_END_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,CREATION_DATE,
CREATED_BY,LAST_UPDATE_LOGIN) values (Hdr_rec.payroll_id,
PA_PAY_EXTERNAL_TIME_PERIODS_S.nextval,
Hdr_rec.time_period_start_date,
Hdr_rec.time_period_end_date,
sysdate,-1,sysdate,-1,null);
update pa_pay_ext_interface_details
set transfer_status_flag = 'P',
request_id = l_request_id
where current of Interface_details;
update pa_pay_ext_interface_details eid1
set transfer_status_flag = 'R',
rejection_code = nvl(rejection_code,'RC 185'),
request_id = l_request_id
where interface_header_id = Hdr_rec.interface_header_id
and exists (select null from pa_pay_ext_interface_details eid2
where eid1.interface_header_id = eid2.interface_header_id
and interface_header_id = Hdr_rec.interface_header_id
and transfer_status_flag = 'R');