[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION Update_Employee(
p_update_date IN DATE,
p_from_supplier IN VARCHAR2,
p_to_supplier IN VARCHAR2,
p_debug_mode IN VARCHAR2,
p_calling_sequence IN VARCHAR2)
RETURN BOOLEAN IS
-- ARU 2628027 , change for 115.9 Performance
-- Removed reference to FINANCIALS_SYSTEM_PARAMETERS in all the CURSORS below
-- to avoid CARTESIAN JOIN. Since join to FINANCIALS_SYSTEM_PARAMETERS was only to
-- get the business_group_id , the value is obtained beforehand
-- and referenced in each query below as a bind variable
-- BUG 4065699 - The vendor site code will be compared with the lookup value from
-- hr_lookups and not the hardcoded value as earlier
/* Cursor For Name Change where p_from_supplier and p_to_supplier is null*/
CURSOR name_cur IS
SELECT DECODE(ppf.middle_names,
null, ppf.last_name||', '||ppf.first_name,
ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
per_vendor_name
, pv.vendor_id ven_vendor_id
, pv.vendor_name ven_vendor_name
FROM per_all_people_f ppf
, ap_suppliers pv
WHERE pv.employee_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND trunc(sysdate)
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.last_update_date > p_update_date
AND DECODE(ppf.middle_names,
null, ppf.last_name||', '||ppf.first_name,
ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
<> pv.vendor_name
AND not exists (SELECT 'duplicate name exists'
FROM ap_suppliers pv1
WHERE DECODE(ppf.middle_names,
null, ppf.last_name||', '||ppf.first_name,
ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
= pv1.vendor_name)
--Bug 2987396 eliminate employees with same name
AND not exists (SELECT 'Different Emp with Same Name'
FROM per_all_people_f ppf1
WHERE ppf1.person_id <> ppf.person_id
AND trunc(sysdate) BETWEEN ppf1.effective_start_date
AND ppf1.effective_end_date
AND DECODE(ppf1.middle_names, null, ppf1.last_name||
', '||ppf1.first_name,ppf1.last_name||', '||
ppf1.first_name||' '||ppf1.middle_names)
= DECODE(ppf.middle_names,
null, ppf.last_name||', '||ppf.first_name,
ppf.last_name||', '||ppf.first_name||' '||
ppf.middle_names));
SELECT DECODE(ppf.middle_names,
null, ppf.last_name||', '||ppf.first_name,
ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
per_vendor_name
, pv.vendor_id ven_vendor_id
, pv.vendor_name ven_vendor_name
FROM per_all_people_f ppf
, ap_suppliers pv
WHERE pv.employee_id = ppf.person_id
AND ppf.business_group_id = g_business_group_id
AND trunc(sysdate)
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND DECODE(ppf.middle_names,
null, ppf.last_name||', '||ppf.first_name,
ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
<> pv.vendor_name
AND not exists (SELECT 'duplicate name exists'
FROM ap_suppliers pv1
WHERE DECODE(ppf.middle_names,
null, ppf.last_name||', '||ppf.first_name,
ppf.last_name||', '||ppf.first_name||' '||ppf.middle_names)
= pv1.vendor_name)
AND pv.vendor_name between p_from_supplier and p_to_supplier
--Bug 2987396 eliminate employees with same name.
AND not exists (SELECT 'Different Emp with Same Name'
FROM per_all_people_f ppf1
WHERE ppf1.person_id <> ppf.person_id
AND trunc(sysdate) BETWEEN ppf1.effective_start_date
AND ppf1.effective_end_date
AND DECODE(ppf1.middle_names, null, ppf1.last_name||
', '||ppf1.first_name,ppf1.last_name||', '||
ppf1.first_name||' '||ppf1.middle_names)
= DECODE(ppf.middle_names,
null, ppf.last_name||', '||ppf.first_name,
ppf.last_name||', '||ppf.first_name||' '||
ppf.middle_names));
SELECT DECODE(greatest(nvl(ppos.actual_termination_date,to_date('4712/12/31',
'YYYY/MM/DD')),trunc(sysdate)),
trunc(sysdate),ppos.actual_termination_date)
per_idate
, pv.vendor_id ven_vendor_id
, pv.vendor_name ven_vendor_name
, pv.end_date_active ven_idate
FROM per_all_assignments_f paf
, per_periods_of_service ppos
, per_all_people_f ppf
, ap_suppliers pv
WHERE pv.employee_id = ppf.person_id
AND ppf.person_id = paf.person_id
AND ppf.person_id = ppos.person_id
AND ppf.business_group_id = g_business_group_id
AND DECODE(ppos.actual_termination_date,
null, trunc(sysdate),
ppos.actual_termination_date)
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppos.date_start = (SELECT max(ppos2.date_start)
FROM per_periods_of_service ppos2
WHERE ppos2.person_id = ppos.person_id
AND ppos2.date_start <= trunc(sysdate))
AND trunc(sysdate)
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND paf.assignment_type = 'E'
AND ppos.last_update_date > p_update_date
AND nvl(ppos.actual_termination_date,trunc(sysdate)) <>
nvl(pv.end_date_active,trunc(sysdate));
SELECT DECODE(greatest(nvl(ppos.actual_termination_date,to_date('4712/12/31',
'YYYY/MM/DD')),trunc(sysdate)),
trunc(sysdate),ppos.actual_termination_date)
per_idate
, pv.vendor_id ven_vendor_id
, pv.vendor_name ven_vendor_name
, pv.end_date_active ven_idate
FROM per_all_assignments_f paf
, per_periods_of_service ppos
, per_all_people_f ppf
, ap_suppliers pv
WHERE pv.employee_id = ppf.person_id
AND ppf.person_id = paf.person_id
AND ppf.person_id = ppos.person_id
AND ppf.business_group_id = g_business_group_id
AND DECODE(ppos.actual_termination_date,
null, trunc(sysdate),
ppos.actual_termination_date)
BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppos.date_start = (SELECT max(ppos2.date_start)
FROM per_periods_of_service ppos2
WHERE ppos2.person_id = ppos.person_id
AND ppos2.date_start <= trunc(sysdate))
AND trunc(sysdate)
BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND paf.assignment_type = 'E'
AND nvl(ppos.actual_termination_date,trunc(sysdate)) <>
nvl(pv.end_date_active,trunc(sysdate))
AND pv.vendor_name between p_from_supplier and p_to_supplier;
current_calling_sequence := 'AP_EMPLOYEE_UPDATE_PKG.Update_Employee-> '
||p_calling_sequence;
SELECT business_group_id
INTO g_business_group_id
FROM financials_system_parameters ;
UPDATE ap_suppliers
SET vendor_name = name_rec.per_vendor_name
,last_update_date = sysdate -- Bug 3191168
,last_updated_by = fnd_global.user_id -- Bug 3191168
,last_update_login = fnd_global.login_id -- Bug 3191168
WHERE vendor_id = name_rec.ven_vendor_id;
UPDATE ap_supplier_sites_all --bug 3162861
SET inactive_date = inactive_rec.per_idate
,last_update_date = sysdate -- Bug 3191168
,last_updated_by = fnd_global.user_id -- Bug 3191168
,last_update_login = fnd_global.login_id -- Bug 3191168
WHERE vendor_id = inactive_rec.ven_vendor_id;
UPDATE ap_suppliers
SET end_date_active = inactive_rec.per_idate
,last_update_date = sysdate -- Bug 3191168
,last_updated_by = fnd_global.user_id -- Bug 3191168
,last_update_login = fnd_global.login_id -- Bug 3191168
WHERE vendor_id = inactive_rec.ven_vendor_id;
UPDATE ap_suppliers
SET vendor_name = name_rec1.per_vendor_name
,last_update_date = sysdate -- Bug 3191168
,last_updated_by = fnd_global.user_id -- Bug 3191168
,last_update_login = fnd_global.login_id -- Bug 3191168
WHERE vendor_id = name_rec1.ven_vendor_id;
UPDATE ap_supplier_sites_all --bug 3162861
SET inactive_date = inactive_rec1.per_idate
,last_update_date = sysdate -- Bug 3191168
,last_updated_by = fnd_global.user_id -- Bug 3191168
,last_update_login = fnd_global.login_id -- Bug 3191168
WHERE vendor_id = inactive_rec1.ven_vendor_id;
UPDATE ap_suppliers
SET end_date_active = inactive_rec1.per_idate
,last_update_date = sysdate -- Bug 3191168
,last_updated_by = fnd_global.user_id -- Bug 3191168
,last_update_login = fnd_global.login_id -- Bug 3191168
WHERE vendor_id = inactive_rec1.ven_vendor_id;
END Update_Employee;