[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_business_group_id IN NUMBER, --bug 10402551
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 ppf.full_name per_vendor_name /*Bug9615008 */
, 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 = p_business_group_id --bug 10402551 replaced g_business_group_id with p_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 Ppf.Full_Name <> Pv.Vendor_Name;
SELECT ppf.full_name 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 = p_business_group_id --bug 10402551 replaced g_business_group_id with p_business_group_id
AND trunc(sysdate)
BETWEEN ppf.effective_start_date
And Ppf.Effective_End_Date
AND ppf.full_name <> pv.vendor_name
AND pv.vendor_name between p_from_supplier and p_to_supplier;
SELECT nvl(ppos.final_process_date,ppos.actual_termination_date) per_idate /* bug 9456397*/
, 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 = p_business_group_id --bug 10402551 replaced g_business_group_id with p_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'
/* Added for bug#9828634 Stat */
AND (ppos.last_update_date > p_update_date
OR
(Nvl(Ppos.Final_Process_Date,Ppos.Actual_Termination_Date) >=Trunc(P_Update_Date))
Or
Nvl(Ppos.Final_Process_Date,Ppos.Actual_Termination_Date) is null /*Bug 10098626 */
)
AND (( pv.end_date_active IS NULL
AND (nvl(ppos.final_process_date,ppos.actual_termination_date)) IS NOT NULL) /* bug 9456397*/
Or( Pv.End_Date_Active Is Not Null
AND pv.end_date_active <> (nvl(ppos.final_process_date,nvl(ppos.actual_termination_date,pv.end_date_active-1))) /*Bug 10098626 */
)
);
SELECT nvl(ppos.final_process_date,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 = p_business_group_id --bug 10402551 replaced g_business_group_id with p_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;