Update Employee Organization At PA Expenditure Item Level
SELECT hou.organization_id,hou.name
INTO l_new_organization_id, l_incurred_org
FROM per_all_assignments_f papf,hr_all_organization_units hou
WHERE papf.organization_id=hou.organization_id
AND trunc(sysdate) between papf.effective_start_date and trunc(nvl(papf.effective_end_date,sysdate))
and papf.person_id=:p_person_id
AND hou.business_group_id = 571;
UPDATE pa_expenditure_items_all
SET cc_prvdr_organization_id = l_new_organization_id,
last_update_date = SYSDATE,
last_updated_by = :p_user_id
WHERE expenditure_id = :p_expenditure_id AND expenditure_item_id = :p_expenditure_item_id AND org_id = :p_org_id
RETURNING cc_prvdr_organization_id
INTO l_cc_prvdr_organization_id;
UPDATE pa_expenditures_all
set incurred_by_organization_id=l_new_organization_id,
last_update_date = SYSDATE,
last_updated_by = :p_user_id
WHERE expenditure_id = :p_expenditure_id AND org_id = :p_org_id
RETURNING incurred_by_organization_id
INTO l_incurred_by_organization_id;
INTO l_new_organization_id, l_incurred_org
FROM per_all_assignments_f papf,hr_all_organization_units hou
WHERE papf.organization_id=hou.organization_id
AND trunc(sysdate) between papf.effective_start_date and trunc(nvl(papf.effective_end_date,sysdate))
and papf.person_id=:p_person_id
AND hou.business_group_id = 571;
UPDATE pa_expenditure_items_all
SET cc_prvdr_organization_id = l_new_organization_id,
last_update_date = SYSDATE,
last_updated_by = :p_user_id
WHERE expenditure_id = :p_expenditure_id AND expenditure_item_id = :p_expenditure_item_id AND org_id = :p_org_id
RETURNING cc_prvdr_organization_id
INTO l_cc_prvdr_organization_id;
UPDATE pa_expenditures_all
set incurred_by_organization_id=l_new_organization_id,
last_update_date = SYSDATE,
last_updated_by = :p_user_id
WHERE expenditure_id = :p_expenditure_id AND org_id = :p_org_id
RETURNING incurred_by_organization_id
INTO l_incurred_by_organization_id;