[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This program also updates the party and partner information from TCA model
******************************************************************************************/
/* Package variables. */
G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_CONC_RES_PUB';
select job_id, effective_start_date
from per_all_assignments_f
where person_id = c_person_id
-- and assignment_type = 'E'
and assignment_type = c_assignment_type
and primary_flag = 'Y'
-- and job_id = c_job_id
and assignment_id = c_assignment_id
and trunc(effective_start_date) <= g_run_date
order by effective_start_date desc;
select job_id, effective_start_date
from per_all_assignments_f
where person_id = c_person_id
-- and assignment_type = 'E'
and assignment_type = c_assignment_type
and primary_flag = 'Y'
-- and job_id = c_job_id
and assignment_id = c_assignment_id
--and trunc(effective_start_date) <= trunc(sysdate)
order by effective_start_date desc;
select effective_end_date
from per_all_assignments_f
where person_id = c_person_id
-- and assignment_type = 'E'
and assignment_type = c_assignment_type
and primary_flag = 'Y'
and job_id = c_job_id
and assignment_id = c_assignment_id
and trunc(effective_start_date) <= g_run_date
order by effective_start_date desc;
PROCEDURE UPDATE_EMP_PHONE;
jtf_rs_conc_res_pub.update_employee
(P_OVERWRITE_NAME => P_OVERWRITE_NAME);
SELECT salesrep_id FROM jtf_rs_salesreps
WHERE salesrep_number = l_salesrep_number;
SELECT role_id
FROM jtf_rs_job_roles
WHERE job_id = l_job_id;
SELECT role_name
FROM jtf_rs_roles_vl
WHERE role_id = l_role_id;
SELECT role_type_code
FROM jtf_rs_roles_b
WHERE role_id = l_role_id;
SELECT sales_credit_type_id
FROM oe_sales_credit_types
WHERE name = l_sales_credit_type;
SELECT ppl.person_id person_id
, ppl.employee_number employee_number
, ppl.npw_number npw_number
, ppl.known_as known_as
, ppl.full_name full_name
, ppl.first_name first_name
, ppl.last_name last_name
, ppl.middle_names middle_name
, ppl.work_telephone phone
, ppl.email_address email
, job.job_id job_id
, job.name job_title
, ppl.effective_start_date effective_start_date
, ppl.effective_end_date effective_end_date
, ppl.business_group_id org_id
, org.name org_name
, loc.address_line_1 address1
, loc.address_line_2 address2
, loc.address_line_3 address3
, null address4
, loc.town_or_city city
, null county
, loc.country country
, loc.postal_code postal_code
, null state
, null province
, asg.supervisor_id mgr_id
, emp.full_name mgr_name
, ppl.office_number office
, ppl.internal_location location
, ppl.mailstop mailstop
, asg.location_id address_id
, asg.assignment_id assignment_id
, asg.assignment_type assignment_type
FROM per_all_people_f ppl
, hr_all_organization_units org
, per_all_assignments_f asg
, per_jobs job
--, hr_locations loc (SQL rep perf bug 4956611 Nishant 28-Mar-2006)
, hr_locations_all loc
--, per_employees_current_x emp
, per_all_people_f emp
-- WHERE NOT EXISTS (select source_id
WHERE NOT EXISTS (select 1
from jtf_rs_resource_extns rsc
where category = 'EMPLOYEE'
and ppl.person_id = rsc.source_id)
and ppl.business_group_id = org.organization_id
and ppl.person_id = asg.person_id
and trunc(sysdate ) between ppl.effective_start_date and ppl.effective_end_date
-- and ppl.employee_number is not null
and (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y')
and asg.primary_flag = 'Y'
-- and asg.assignment_type = 'E'
and (asg.assignment_type = 'E' OR asg.assignment_type = 'C')
and trunc(sysdate ) between asg.effective_start_date and asg.effective_end_date
and asg.job_id = job.job_id(+)
and asg.location_id = loc.location_id (+)
--and asg.supervisor_id = emp.employee_id(+)
and asg.supervisor_id = emp.person_id(+)
and trunc(sysdate ) between emp.effective_start_date(+) and emp.effective_end_date(+);
SELECT ppl.person_id person_id
, ppl.employee_number employee_number
, ppl.npw_number npw_number
, ppl.known_as known_as
, ppl.full_name full_name
, ppl.first_name first_name
, ppl.last_name last_name
, ppl.middle_names middle_name
, ppl.work_telephone phone
, ppl.email_address email
, job.job_id job_id
, job.name job_title
, ppl.effective_start_date effective_start_date
, ppl.effective_end_date effective_end_date
, ppl.business_group_id org_id
, org.name org_name
, loc.address_line_1 address1
, loc.address_line_2 address2
, loc.address_line_3 address3
, null address4
, loc.town_or_city city
, null county
, loc.country country
, loc.postal_code postal_code
, null state
, null province
, asg.supervisor_id mgr_id
, emp.full_name mgr_name
, ppl.office_number office
, ppl.internal_location location
, ppl.mailstop mailstop
, asg.location_id address_id
, asg.assignment_id assignment_id
, asg.assignment_type assignment_type
FROM per_all_people_f ppl
, hr_all_organization_units org
, per_all_assignments_f asg
, per_jobs job
--, hr_locations loc (SQL rep perf bug 4956611 Nishant 28-Mar-2006)
, hr_locations_all loc
--, per_employees_current_x emp
, per_all_people_f emp
-- WHERE NOT EXISTS (select source_id
WHERE NOT EXISTS (select 1
from jtf_rs_resource_extns rsc
where category = 'EMPLOYEE'
and ppl.person_id = rsc.source_id)
and ppl.business_group_id = org.organization_id
and ppl.person_id = asg.person_id
and trunc(sysdate) <= ppl.effective_end_date
and ppl.effective_start_date > trunc(sysdate )
and (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y')
and asg.primary_flag = 'Y'
and (asg.assignment_type = 'E' OR asg.assignment_type = 'C')
-- and trunc(sysdate ) <= asg.effective_end_date -- (not required as start_date >= sysdate is already checked)
and asg.effective_start_date = (select min(asg1.effective_start_date)
from per_all_assignments_f asg1
where ppl.person_id = asg1.person_id
and asg1.effective_start_date >= trunc(sysdate)
and asg.primary_flag = 'Y')
and asg.job_id = job.job_id(+)
and asg.location_id = loc.location_id (+)
and asg.supervisor_id = emp.person_id(+);
SELECT user_id,
user_name
FROM fnd_user
WHERE employee_id = l_employee_id;
select phone_number
from per_phones p1
where parent_table = 'PER_ALL_PEOPLE_F'
and parent_id = l_person_id
and phone_type = 'W1'
and trunc(sysdate) between date_from and nvl(date_to, to_date('31/12/4712', 'DD/MM/YYYY'));
select phone_number
from per_phones p1
where parent_table = 'PER_ALL_PEOPLE_F'
and parent_id = l_person_id
and phone_type = 'W1'
and date_from = (select max(date_from)
from per_phones p2
where parent_table = 'PER_ALL_PEOPLE_F'
and parent_id = l_person_id
and phone_type = 'W1');
SELECT date_start, actual_termination_date
FROM per_periods_of_service
WHERE person_id = l_person_id
ORDER BY date_start asc;
select organization_id
from hr_operating_units
where business_group_id = c_business_group_id
and organization_id = c_org_id;
l_query := 'SELECT date_start, actual_termination_date '||
'FROM per_periods_of_placement ' ||
'WHERE person_id = :b_person_id '||
'ORDER BY date_start asc';
fnd_msg_pub.delete_msg;
SELECT jtf_rs_salesrep_number_s.nextval
INTO l_salesperson_number
FROM DUAL;
p_wh_update_date => null,
p_sales_tax_geocode => null,
p_sales_tax_inside_city_limits => null,
x_return_status => s_return_status,
x_msg_count => s_msg_count,
x_msg_data => s_msg_data,
x_salesrep_id => l_salesrep_id
);
fnd_msg_pub.delete_msg;
fnd_msg_pub.delete_msg;
fnd_msg_pub.delete_msg;
SELECT jtf_rs_salesrep_number_s.nextval
INTO l_salesperson_number
FROM DUAL;
p_wh_update_date => null,
p_sales_tax_geocode => null,
p_sales_tax_inside_city_limits => null,
x_return_status => s_return_status,
x_msg_count => s_msg_count,
x_msg_data => s_msg_data,
x_salesrep_id => l_salesrep_id
);
fnd_msg_pub.delete_msg;
fnd_msg_pub.delete_msg;
SELECT rsc.resource_id
-- , rsc.resource_number
, rsc.resource_name
, rsc.source_id
, rsc.object_version_number
-- , rsc.start_date_active
-- , rsc.end_date_active
FROM jtf_rs_resource_extns_vl rsc
WHERE rsc.category = 'EMPLOYEE'
/* AND not exists (select employee_id
from per_employees_current_x emp
where emp.employee_id = rsc.source_id
and rsc.category = 'EMPLOYEE')*/
/*
AND not exists (select ppl.person_id
from per_all_people_f ppl,
per_all_assignments_f asg,
per_periods_of_service ser
where ppl.person_id = rsc.source_id
-- and rsc.category = 'EMPLOYEE'
and trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
and ppl.employee_number is not null
and ppl.person_id = asg.person_id
and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
and asg.primary_flag = 'Y'
and asg.assignment_type = 'E'
and asg.period_of_service_id = ser.period_of_service_id
and (ser.actual_termination_date >= trunc(sysdate) or ser.actual_termination_date is NULL))
*/
AND not exists (select ppl.person_id
from per_all_people_f ppl
where ppl.person_id = rsc.source_id
--Enh 3947611 2-dec-2004: changed the date check so that future dated employees are not terminated
--and ( trunc(sysdate) between trunc(ppl.effective_start_date) and trunc(ppl.effective_end_date)
and trunc(sysdate) <= trunc(ppl.effective_end_date )
and (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y'))
AND trunc( nvl(end_date_active, sysdate+l_active_days)) >= trunc(sysdate+l_active_days);
SELECT actual_termination_date
FROM per_periods_of_service
WHERE person_id = l_person_id
AND trunc(date_start) <= trunc(sysdate)
ORDER BY period_of_service_id desc;
l_query := 'SELECT actual_termination_date '||
'FROM per_periods_of_placement ' ||
'WHERE person_id = :b_person_id '||
'AND trunc(date_start) <= trunc(sysdate) '||
'ORDER BY period_of_placement_id desc';
fnd_msg_pub.delete_msg;
PROCEDURE update_employee
(P_OVERWRITE_NAME IN VARCHAR2 )
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_EMPLOYEE';
SELECT resource_id,
source_id,
source_number,
source_job_id,
start_date_active,
end_date_active
FROM jtf_rs_resource_extns
WHERE category = 'EMPLOYEE';
SELECT 'Y'
FROM jtf_rs_resource_extns rsc
WHERE rsc.category = 'EMPLOYEE'
AND rsc.source_id = p_person_id
AND l_sysdate not between trunc(rsc.start_date_active) and trunc(nvl(rsc.end_date_active,sysdate))
-- AND exists ( select cur.employee_id from PER_EMPLOYEES_CURRENT_X cur where cur.employee_id = rsc.source_id);
AND exists ( select ppl.person_id
from per_all_people_f ppl
where ppl.person_id = p_person_id
and trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
and (ppl.current_employee_flag = 'Y' OR ppl.current_npw_flag = 'Y'));
SELECT salesrep_id
FROM jtf_rs_salesreps
WHERE resource_id = c_resource_id;
SELECT employee_number,
full_name,
known_as,
first_name,
middle_names,
last_name,
email_address,
business_group_id,
office_number,
internal_location,
mailstop,
npw_number,
party_id,
current_employee_flag
FROM per_all_people_f
WHERE person_id = p_person_id
and g_run_date between effective_start_date and effective_end_date;
SELECT name
FROM hr_all_organization_units
WHERE organization_id = p_org_id;
SELECT supervisor_id,
location_id,
job_id,
assignment_id
FROM per_all_assignments_f
WHERE person_id = p_person_id
AND primary_flag = 'Y'
-- AND assignment_type = 'E'
AND assignment_type in ('E','C')
AND g_run_date between effective_start_date and effective_end_date;
SELECT name
FROM per_jobs
WHERE job_id = p_job_id;
SELECT address_line_1,
address_line_2,
address_line_3,
town_or_city,
country,
postal_code
FROM hr_locations
WHERE location_id = p_loc_id;
SELECT full_name
FROM per_all_people_f
WHERE person_id = p_person_id
and g_run_date between effective_start_date and effective_end_date;
SELECT phone_number
FROM per_phones
WHERE parent_table = 'PER_ALL_PEOPLE_F'
AND parent_id = p_person_id
AND phone_type = 'W1'
AND g_run_date between date_from and nvl(date_to, to_date('31/12/4712', 'DD/MM/YYYY'));
SELECT phone_number
FROM per_phones
WHERE parent_table = 'PER_ALL_PEOPLE_F'
AND parent_id = p_person_id
AND phone_type = 'M'
AND g_run_date between date_from and nvl(date_to, to_date('31/12/4712', 'DD/MM/YYYY'));
SELECT phone_number
FROM per_phones
WHERE parent_table = 'PER_ALL_PEOPLE_F'
AND parent_id = p_person_id
AND phone_type = 'P'
AND g_run_date between date_from and nvl(date_to, to_date('31/12/4712', 'DD/MM/YYYY'));
SELECT
source_number,
source_name,
source_first_name,
source_middle_name,
source_last_name,
source_phone,
source_email,
source_job_id,
source_job_title,
address_id,
source_address1,
source_address2,
source_address3,
source_city,
source_country,
source_postal_code,
source_mgr_id,
source_mgr_name,
source_business_grp_id,
source_business_grp_name,
source_office,
source_location,
source_mailstop,
source_mobile_phone,
source_pager,
resource_name,
end_date_active,
person_party_id
FROM jtf_rs_resource_extns_vl
WHERE resource_id = l_resource_id;
l_is_to_be_updated varchar2(1);
l_update_extn varchar2(1);
SELECT job_id
FROM per_jobs
WHERE NAME = l_job_name
AND business_group_id = l_business_group_id;
PROCEDURE update_job_roles
(p_old_job_id IN NUMBER,
p_new_job_title IN VARCHAR2,
p_new_job_id IN NUMBER,
p_new_job_assignment_id IN NUMBER,
p_person_id IN NUMBER,
p_resource_id IN NUMBER,
p_assignment_type IN VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_JOB_ROLES';
SELECT role_id
FROM jtf_rs_job_roles
WHERE job_id = l_job_id;
SELECT role_type_code
FROM jtf_rs_roles_b
WHERE role_id = l_role_id;
select 'Y'
from jtf_rs_salesreps
where resource_id = c_resource_id;
SELECT rr.role_relate_id, rr.object_version_number
FROM jtf_rs_role_relations rr
WHERE rr.role_resource_type = 'RS_INDIVIDUAL'
AND rr.role_resource_id = l_resource_id
AND rr.role_id = l_role_id
AND nvl(rr.delete_flag,'N') <> 'Y'
AND g_run_date between trunc(start_date_active) and trunc(nvl(end_date_active,g_run_date));
SELECT rr.role_relate_id, rr.object_version_number, rr.role_id
FROM jtf_rs_role_relations rr
WHERE exists (select null
from jtf_rs_job_roles jr
where jr.role_id = rr.role_id
and jr.job_id not in (nvl(p_old_job_id,-99)))
AND rr.role_resource_type = 'RS_INDIVIDUAL'
AND rr.role_resource_id = l_resource_id
AND nvl(rr.delete_flag,'N') <> 'Y'
AND trunc(nvl(end_date_active,g_run_date)) >= trunc(l_new_role_st_dt);
SELECT role_name
FROM jtf_rs_roles_vl
WHERE role_id = l_role_id;
SELECT assignment_id
FROM PER_ALL_ASSIGNMENTS_F
WHERE job_id = p_job_id
AND primary_flag = 'Y'
-- AND assignment_type = 'E'
AND assignment_type = p_assignment_type
AND person_id = p_person_id
ORDER BY assignment_id desc;
JTF_RS_ROLE_RELATE_PVT.UPDATE_RESOURCE_ROLE_RELATE(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_role_relate_id => l_job_role_relations_rec.role_relate_id,
p_end_date_active => l_asg_end_date,
p_object_version_num => l_job_role_relations_rec.object_version_number,
x_return_status => r_return_status,
x_msg_count => r_msg_count,
x_msg_data => r_msg_data
);
fnd_msg_pub.delete_msg;
JTF_RS_ROLE_RELATE_PVT.UPDATE_RESOURCE_ROLE_RELATE(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_role_relate_id => i.role_relate_id,
p_end_date_active => l_asg_start_date-1,
p_object_version_num => i.object_version_number,
x_return_status => r_return_status,
x_msg_count => r_msg_count,
x_msg_data => r_msg_data
);
fnd_msg_pub.delete_msg;
fnd_msg_pub.delete_msg;
JTF_RS_ROLE_RELATE_PVT.UPDATE_RESOURCE_ROLE_RELATE(
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_false,
p_role_relate_id => i.role_relate_id,
p_end_date_active => l_asg_start_date-1,
p_object_version_num => i.object_version_number,
x_return_status => r_return_status,
x_msg_count => r_msg_count,
x_msg_data => r_msg_data
);
fnd_msg_pub.delete_msg;
fnd_msg_pub.delete_msg;
END update_job_roles;
l_is_to_be_updated := 'N';
l_update_extn := 'N';
SAVEPOINT UPDATE_EMPLOYEE_SP;
SAVEPOINT update_resource_sp;
l_is_to_be_updated := 'N';
l_update_extn := 'N';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
l_is_to_be_updated := 'Y';
if (l_is_to_be_updated = 'Y') then
l_update_extn := 'Y';
if (l_update_extn = 'N') then
l_is_to_be_updated := 'Y';
if (l_is_to_be_updated = 'Y') then
---fnd_file.put_line(fnd_file.log,'resource id:' || l_c_resource.resource_id);
select jtf_rs_resource_extn_aud_s.nextval
into l_resource_extn_aud_id
from dual;
JTF_RS_RESOURCE_EXTN_AUD_PKG.INSERT_ROW
(
X_ROWID => l_row_id ,
x_resource_audit_id => l_resource_extn_aud_id ,
x_resource_id => l_c_resource.resource_id ,
x_new_category => null ,
x_old_category => null ,
x_new_resource_number => null ,
x_old_resource_number => null ,
x_new_source_id => null ,
x_old_source_id => null ,
x_new_address_id => l_address_id_n ,
x_old_address_id => l_address_id_o ,
x_new_contact_id => null ,
x_old_contact_id => null ,
x_new_managing_employee_id => null ,
x_old_managing_employee_id => null ,
x_new_start_date_active => null ,
x_old_start_date_active => null ,
x_new_end_date_active => l_end_date_active_n ,
x_old_end_date_active => l_end_date_active_o ,
x_new_time_zone => null ,
x_old_time_zone => null ,
x_new_cost_per_hr => null ,
x_old_cost_per_hr => null ,
x_new_primary_language => null ,
x_old_primary_language => null ,
x_new_secondary_language => null ,
x_old_secondary_language => null ,
x_new_support_site_id => null ,
x_old_support_site_id => null ,
x_new_ies_agent_login => null ,
x_old_ies_agent_login => null ,
x_new_server_group_id => null ,
x_old_server_group_id => null ,
x_new_assigned_to_group_id => null ,
x_old_assigned_to_group_id => null ,
x_new_cost_center => null ,
x_old_cost_center => null ,
x_new_charge_to_cost_center => null ,
x_old_charge_to_cost_center => null ,
x_new_compensation_currency_co => null ,
x_old_compensation_currency_co => null ,
x_new_commissionable_flag => null ,
x_old_commissionable_flag => null ,
x_new_hold_reason_code => null ,
x_old_hold_reason_code => null ,
x_new_hold_payment => null ,
x_old_hold_payment => null ,
x_new_comp_service_team_id => null ,
x_old_comp_service_team_id => null ,
x_new_transaction_number => null ,
x_old_transaction_number => null ,
x_new_object_version_number => null ,
x_old_object_version_number => null ,
x_new_user_id => null ,
x_old_user_id => null ,
x_new_resource_name => l_resource_name_n ,
x_old_resource_name => l_resource_name_o ,
x_new_source_name => l_source_name_n,
x_old_source_name => l_source_name_o,
x_new_source_number => l_source_number_n,
x_old_source_number => l_source_number_o,
x_new_source_job_id => l_source_job_id_n,
x_old_source_job_id => l_source_job_id_o,
x_new_source_job_title => l_source_job_title_n,
x_old_source_job_title => l_source_job_title_o,
x_new_source_email => l_source_email_n,
x_old_source_email => l_source_email_o,
x_new_source_phone => l_source_phone_n,
x_old_source_phone => l_source_phone_o,
x_new_source_org_id => null,
x_old_source_org_id => null,
x_new_source_org_name => null,
x_old_source_org_name => null,
x_new_source_address1 => l_source_address1_n,
x_old_source_address1 => l_source_address1_o,
x_new_source_address2 => l_source_address2_n,
x_old_source_address2 => l_source_address2_o,
x_new_source_address3 => l_source_address3_n,
x_old_source_address3 => l_source_address3_o,
x_new_source_address4 => null,
x_old_source_address4 => null,
x_new_source_city => l_source_city_n,
x_old_source_city => l_source_city_o,
x_new_source_postal_code => l_source_postal_code_n,
x_old_source_postal_code => l_source_postal_code_o,
x_new_source_state => null,
x_old_source_state => null,
x_new_source_province => null,
x_old_source_province => null,
x_new_source_county => null,
x_old_source_county => null,
x_new_source_country => l_source_country_n,
x_old_source_country => l_source_country_o,
x_new_source_mgr_id => l_source_mgr_id_n,
x_old_source_mgr_id => l_source_mgr_id_o,
x_new_source_mgr_name => l_source_mgr_name_n,
x_old_source_mgr_name => l_source_mgr_name_o,
x_new_source_business_grp_id => l_source_business_grp_id_n,
x_old_source_business_grp_id => l_source_business_grp_id_o,
x_new_source_business_grp_name => l_source_business_grp_name_n,
x_old_source_business_grp_name => l_source_business_grp_name_o,
x_new_source_first_name => l_source_first_name_n,
x_old_source_first_name => l_source_first_name_o,
x_new_source_middle_name => l_source_middle_name_n,
x_old_source_middle_name => l_source_middle_name_o,
x_new_source_last_name => l_source_last_name_n,
x_old_source_last_name => l_source_last_name_o,
x_new_source_category => null,
x_old_source_category => null,
x_new_source_status => null,
x_old_source_status => null,
x_new_source_office => l_source_office_n,
x_old_source_office => l_source_office_o,
x_new_source_location => l_source_location_n,
x_old_source_location => l_source_location_o,
x_new_source_mailstop => l_source_mailstop_n,
x_old_source_mailstop => l_source_mailstop_o,
x_new_user_name => null,
x_old_user_name => null,
x_new_party_id => l_party_id_n,
x_old_party_id => l_party_id_o,
x_new_source_mobile_phone => l_source_mobile_phone_n,
x_old_source_mobile_phone => l_source_mobile_phone_o,
x_new_source_pager => l_source_pager_n,
x_old_source_pager => l_source_pager_o,
x_creation_date => l_sysdate,
x_created_by => l_user_id,
x_last_update_date => l_sysdate,
x_last_updated_by => l_user_id,
x_last_update_login => l_login );
if (l_update_extn = 'Y') then
UPDATE jtf_rs_resource_extns
SET last_update_date = l_sysdate,
last_update_login = l_login,
last_updated_by = l_user_id,
source_number = l_source_number,
source_name = l_ppf_rec.full_name,
source_first_name = l_ppf_rec.first_name,
source_last_name = l_ppf_rec.last_name,
source_middle_name = l_ppf_rec.middle_names,
source_phone = l_phone,
source_email = l_ppf_rec.email_address,
source_job_id = l_new_job_id,
source_job_title = l_job_name,
address_id = l_asg_rec.location_id,
source_address1 = l_loc_rec.address_line_1,
source_address2 = l_loc_rec.address_line_2,
source_address3 = l_loc_rec.address_line_3,
source_city = l_loc_rec.town_or_city,
source_country = l_loc_rec.country,
source_postal_code = l_loc_rec.postal_code,
source_mgr_id = l_asg_rec.supervisor_id,
source_mgr_name = l_mgr_name,
source_business_grp_id = l_ppf_rec.business_group_id,
source_business_grp_name = l_org_name,
source_office = l_ppf_rec.office_number,
source_location = l_ppf_rec.internal_location,
source_mailstop = l_ppf_rec.mailstop,
source_mobile_phone = l_mobile_phone,
source_pager = l_pager,
person_party_id = l_ppf_rec.party_id,
end_date_active = l_end_date_active
WHERE RESOURCE_ID = l_c_resource.resource_id;
update jtf_rs_resource_extns_tl
SET last_update_date = l_sysdate,
last_update_login = l_login,
last_updated_by = l_user_id,
resource_name = l_resource_name_n,
source_lang = userenv('LANG')
where resource_id = l_c_resource.resource_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
update jtf_rs_resource_extns_tl
SET last_update_date = l_sysdate,
last_update_login = l_login,
last_updated_by = l_user_id,
resource_name = l_resource_name_n,
source_lang = userenv('LANG')
where resource_id = l_c_resource.resource_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
UPDATE jtf_rs_salesreps
SET end_date_active = NULL,
last_update_date = l_sysdate,
last_update_login = l_login,
last_updated_by = l_user_id
WHERE resource_id = l_c_resource.resource_id;
update_job_roles(
p_old_job_id => l_derived_job_id, --l_c_resource.source_job_id,
p_new_job_title => l_new_job_title,
p_new_job_id => l_new_job_id,
p_new_job_assignment_id => l_new_job_assignment_id,
p_person_id => l_c_resource.source_id,
p_resource_id => l_c_resource.resource_id,
p_assignment_type => l_assignment_type
);
ROLLBACK TO update_resource_sp;
ROLLBACK TO update_resource_sp;
ROLLBACK TO update_resource_sp;
ROLLBACK TO UPDATE_EMPLOYEE_SP;
ROLLBACK TO UPDATE_EMPLOYEE_SP;
END update_employee;
PROCEDURE update_terminated_employee
IS
cursor term_res_cur
is
SELECT RESOURCE_ID, SOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS RES
WHERE res.category = 'EMPLOYEE'
AND not exists (select ppl.person_id
from per_all_people_f ppl,
per_all_assignments_f asg
where ppl.person_id = res.source_id
and res.category = 'EMPLOYEE'
and trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
and ppl.employee_number is not null
and ppl.person_id = asg.person_id
and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
and asg.primary_flag = 'Y')
/*AND NOT EXISTS (SELECT EMPLOYEE_ID
FROM PER_EMPLOYEES_CURRENT_X
WHERE EMPLOYEE_ID = RES.SOURCE_ID)*/
union
SELECT RESOURCE_ID, SOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS RES
WHERE res.category = 'EMPLOYEE'
AND (res.end_date_active IS NOT NULL AND res.end_date_active < sysdate);
SELECT res.resource_id resource_id
, res.resource_number resource_number
, res.object_version_number object_version_num
, ppl.person_id person_id
, ppl.employee_number employee_number
, ppl.full_name full_name
, ppl.first_name first_name
, ppl.middle_names middle_name
, ppl.last_name last_name
, ppl.work_telephone phone
, ppl.email_address email
, ppl.business_group_id org_id
, org.name org_name
, ppl.office_number office
, ppl.internal_location location
, ppl.mailstop mailstop
, ppl.party_id party_id
FROM jtf_rs_resource_extns res
, per_all_people_f ppl
, hr_all_organization_units org
WHERE res.resource_id = l_resource_id
AND res.source_id = ppl.person_id
and ppl.effective_start_date = (select max(effective_start_date)
from per_all_people_f where person_id = ppl.person_id)
and ppl.effective_end_date = (select max(effective_end_date)
from per_all_people_f where person_id = ppl.person_id)
and ppl.business_group_id = org.organization_id;
UPDATE jtf_rs_resource_extns RES
SET RES.LAST_UPDATE_DATE = sysdate,
RES.LAST_UPDATED_BY = fnd_global.user_id,
(RES.SOURCE_NUMBER ,
RES.SOURCE_NAME ,
RES.SOURCE_PHONE ,
RES.SOURCE_EMAIL ,
--RES.SOURCE_JOB_TITLE ,
--RES.SOURCE_ORG_ID ,
--RES.SOURCE_ORG_NAME ,
--RES.SOURCE_ADDRESS1,
--RES.SOURCE_ADDRESS2 ,
--RES.SOURCE_ADDRESS3 ,
--RES.SOURCE_ADDRESS4 ,
--RES.SOURCE_CITY ,
--RES.SOURCE_POSTAL_CODE ,
--RES.SOURCE_STATE ,
--RES.SOURCE_PROVINCE ,
--RES.SOURCE_COUNTY ,
--RES.SOURCE_COUNTRY ,
--RES.SOURCE_MGR_ID ,
--RES.SOURCE_MGR_NAME ,
RES.SOURCE_BUSINESS_GRP_ID ,
RES.SOURCE_BUSINESS_GRP_NAME,
RES.SOURCE_FIRST_NAME,
RES.SOURCE_MIDDLE_NAME,
RES.SOURCE_LAST_NAME,
RES.SOURCE_OFFICE,
RES.SOURCE_LOCATION,
RES.PERSON_PARTY_ID,
RES.SOURCE_MAILSTOP)
= ( SELECT
ppl.employee_number employee_number
, ppl.full_name full_name
, ppl.work_telephone phone
, ppl.email_address email
--, job.name job_title
--, NULL
--, NULL
--, loc.address_line_1 address1
--, loc.address_line_2 address2
--, loc.address_line_3 address3
--, null address4
--, loc.town_or_city city
--, loc.postal_code postal_code
--, null state
--, null province
--, null county
--, loc.country country
--, asg.supervisor_id mgr_id
--, emp.full_name mgr_name
, ppl.business_group_id org_id
, org.name org_name
, ppl.first_name
, ppl.middle_names
, ppl.last_name
, ppl.office_number
, ppl.internal_location
, ppl.party_id
, ppl.mailstop
FROM per_all_people_f ppl
, hr_all_organization_units org
--, per_all_assignments_f asg
--, per_jobs job
--, hr_locations loc
--, per_employees_current_x emp
WHERE res.source_id = ppl.person_id
/* AND NOT EXISTS (SELECT EMPLOYEE_ID
FROM PER_EMPLOYEES_CURRENT_X
WHERE EMPLOYEE_ID = RES.SOURCE_ID) */
AND ppl.effective_start_date = (select max(effective_start_date)
from per_all_people_f
where person_id = ppl.person_id)
and ppl.business_group_id = org.organization_id
--and ppl.person_id = asg.person_id
--and asg.primary_flag = 'Y'
--and asg.assignment_type = 'E'
/*and asg.effective_start_date = (select max(effective_start_date)
from per_all_assignments_f
where person_id = ppl.person_id
and primary_flag = 'Y'
and assignment_type = 'E'
) */
--and asg.job_id = job.job_id(+)
--and asg.location_id = loc.location_id (+)
--and asg.supervisor_id = emp.employee_id(+)
)
WHERE res.resource_id = term_res_rec.resource_id ;
UPDATE jtf_rs_resource_extns RES
SET RES.LAST_UPDATE_DATE = sysdate,
RES.LAST_UPDATED_BY = fnd_global.user_id,
RES.SOURCE_NUMBER = emp_rec.employee_number ,
RES.SOURCE_NAME = emp_rec.full_name,
RES.SOURCE_PHONE = emp_rec.phone,
RES.SOURCE_EMAIL = emp_rec.email,
--RES.SOURCE_JOB_TITLE = emp_rec.job_title ,
RES.SOURCE_BUSINESS_GRP_ID = emp_rec.org_id ,
RES.SOURCE_BUSINESS_GRP_NAME = emp_rec.org_name,
RES.SOURCE_FIRST_NAME = emp_rec.first_name,
RES.SOURCE_LAST_NAME = emp_rec.last_name,
RES.SOURCE_MIDDLE_NAME = emp_rec.middle_name,
RES.SOURCE_OFFICE = emp_rec.office,
RES.SOURCE_LOCATION = emp_rec.location,
RES.person_party_id = emp_rec.party_id,
RES.SOURCE_MAILSTOP = emp_rec.mailstop
WHERE RES.resource_id = emp_rec.resource_id;
END update_terminated_employee;
PROCEDURE update_emp_phone
IS
BEGIN
update jtf_rs_resource_extns res
SET RES.LAST_UPDATE_DATE = sysdate,
RES.LAST_UPDATED_BY = fnd_global.user_id,
RES.SOURCE_PHONE
= (select per.phone_number
from per_phones per
where parent_table = 'PER_ALL_PEOPLE_F'
and parent_id = res.source_id
and phone_type = 'W1'
AND trunc(sysdate) between date_from and nvl(date_to, to_date('31/12/4712', 'DD/MM/YYYY')))
WHERE RES.CATEGORY = 'EMPLOYEE';
END update_emp_phone;
jtf_rs_conc_res_pub.update_party
(p_overwrite_name => P_OVERWRITE_NAME);
SELECT rsc.resource_id
, rsc.object_version_number
, hpr.end_date
, hpr.status
FROM jtf_rs_resource_extns rsc
,hz_relationships hpr
WHERE rsc.category = 'PARTNER'
AND rsc.source_id = hpr.party_id
AND hpr.directional_flag = 'F'
--
AND hpr.RELATIONSHIP_CODE IN
('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
AND hpr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND hpr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
--
-- AND hpr.relationship_code in ('PARTNER_OF','VAD_OF','CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
AND ((hpr.status = 'I')
OR
(nvl(trunc(hpr.end_date),trunc(sysdate)) < trunc(sysdate)))
AND nvl(trunc(end_date_active),trunc(sysdate+1+l_active_days)) > trunc(sysdate+l_active_days);
PROCEDURE update_party
(P_OVERWRITE_NAME IN VARCHAR2 )
IS
l_too_many_rows_query EXCEPTION; --exception to handle too many rows returned by inner query;
SELECT resource_id
, source_id
, address_id
, category
FROM jtf_rs_resource_extns
WHERE category in ('PARTY', 'PARTNER');
SELECT PARTY.PARTY_NUMBER,
PARTY.PARTY_NAME,
PARTY.EMAIL_ADDRESS,
CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
TO_NUMBER(NULL) ORG_ID,
NULL ORG_NAME,
PARTY.PERSON_FIRST_NAME FIRST_NAME,
PARTY.PERSON_MIDDLE_NAME MIDDLE_NAME,
PARTY.PERSON_LAST_NAME LAST_NAME
FROM
HZ_PARTIES PARTY,
HZ_CONTACT_POINTS CT_POINT1
WHERE PARTY.PARTY_ID = l_party_id
AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
AND CT_POINT1.STATUS (+)= 'A'
AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE';
SELECT PARTY.PARTY_NUMBER,
PARTY.PARTY_NAME,
PARTY.EMAIL_ADDRESS,
CT_POINT1.PHONE_AREA_CODE||CT_POINT1.PHONE_NUMBER PHONE,
REL.OBJECT_ID ORG_ID,
PARTY.PARTY_NAME ORG_NAME,
PARTY.PERSON_FIRST_NAME FIRST_NAME,
PARTY.PERSON_MIDDLE_NAME MIDDLE_NAME,
PARTY.PERSON_LAST_NAME LAST_NAME
FROM
HZ_PARTIES PARTY,
HZ_PARTIES PARTY2,
HZ_PARTIES PARTY3,
HZ_CONTACT_POINTS CT_POINT1,
HZ_RELATIONSHIPS REL
WHERE PARTY.PARTY_ID = l_party_id
AND (
(
PARTY.PARTY_TYPE = 'ORGANIZATION'
AND
PARTY.PARTY_ID = REL.SUBJECT_ID
)
OR
(
PARTY.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND
PARTY.PARTY_ID = REL.PARTY_ID
)
)
--
--
AND REL.RELATIONSHIP_CODE IN
('PARTNER_OF', 'VAD_OF', 'VAD_VENDOR_OF',
'THIRD_PARTY_FOR', 'INDIRECTLY_MANAGES_CUSTOMER', 'CUSTOMER_INDIRECTLY_MANAGED_BY','POM_THIRDPARTY_AUTHOR_FOR')
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG = 'F'
AND REL.STATUS = 'A'
--
AND REL.SUBJECT_ID = PARTY2.PARTY_ID
AND (PARTY2.PARTY_TYPE = 'PERSON'
OR PARTY2.PARTY_TYPE = 'ORGANIZATION')
AND REL.OBJECT_ID = PARTY3.PARTY_ID
AND PARTY3.PARTY_TYPE = 'ORGANIZATION'
AND CT_POINT1.OWNER_TABLE_NAME (+)= 'HZ_PARTIES'
AND CT_POINT1.OWNER_TABLE_ID (+)= PARTY.PARTY_ID
AND CT_POINT1.PRIMARY_FLAG (+)= 'Y'
AND CT_POINT1.STATUS (+)= 'A'
AND CT_POINT1.CONTACT_POINT_TYPE (+)= 'PHONE'
ORDER BY PARTY.LAST_UPDATE_DATE DESC ; -- so that we can pick up latest record.
SELECT LOC.ADDRESS1
,LOC.ADDRESS2
,LOC.ADDRESS3
,LOC.ADDRESS4
,LOC.CITY
,LOC.POSTAL_CODE
,LOC.STATE
,LOC.PROVINCE
,LOC.COUNTY
,LOC.COUNTRY
FROM HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
WHERE PARTY_SITE.PARTY_SITE_ID = l_address_id
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID;
UPDATE JTF_RS_RESOURCE_EXTNS RES
SET RES.LAST_UPDATE_DATE = l_sysdate,
RES.LAST_UPDATED_BY = l_user_id,
RES.LAST_UPDATE_LOGIN = l_login,
(RES.ADDRESS_ID)
= ( SELECT prt.party_site_id
FROM hz_party_sites prt
WHERE prt.party_id = RES.source_id
AND prt.identifying_address_flag = 'Y'
-- added status flag check 20 nov 2001
AND prt.status = 'A')
WHERE RES.CATEGORY = 'PARTY'
AND RES.ADDRESS_ID IS NULL;
UPDATE JTF_RS_RESOURCE_EXTNS RES
SET RES.LAST_UPDATE_DATE = l_sysdate,
RES.LAST_UPDATED_BY = l_user_id,
RES.LAST_UPDATE_LOGIN = l_login,
(RES.ADDRESS_ID)
= ( SELECT prt.party_site_id
FROM hz_party_sites prt
WHERE prt.party_id = RES.source_id
AND prt.identifying_address_flag = 'Y'
-- added status flag check 20 nov 2001
AND prt.status = 'A')
WHERE RES.CATEGORY = 'PARTNER'
AND exists (select 'A'
from hz_parties par
where par.party_id = res.source_id
and par.party_type = 'PARTY_RELATIONSHIP');
UPDATE JTF_RS_RESOURCE_EXTNS RES
SET RES.LAST_UPDATE_DATE = l_sysdate,
RES.LAST_UPDATED_BY = l_user_id,
RES.LAST_UPDATE_LOGIN = l_login,
RES.SOURCE_NUMBER = r_party.party_number,
RES.SOURCE_NAME = r_party.party_name,
RES.SOURCE_EMAIL = r_party.email_address ,
RES.SOURCE_ADDRESS1 = r_address.address1,
RES.SOURCE_ADDRESS2 = r_address.address2,
RES.SOURCE_ADDRESS3 = r_address.address3,
RES.SOURCE_ADDRESS4 = r_address.address4,
RES.SOURCE_CITY = r_address.city,
RES.SOURCE_POSTAL_CODE = r_address.postal_code ,
RES.SOURCE_STATE = r_address.state,
RES.SOURCE_PROVINCE = r_address.province,
RES.SOURCE_COUNTY = r_address.county,
RES.SOURCE_COUNTRY = r_address.country,
RES.SOURCE_PHONE = r_party.phone,
RES.SOURCE_ORG_ID = r_party.org_id,
RES.SOURCE_ORG_NAME = r_party.org_name,
RES.SOURCE_FIRST_NAME = r_party.first_name,
RES.SOURCE_MIDDLE_NAME = r_party.middle_name,
RES.SOURCE_LAST_NAME = r_party.last_name
WHERE RES.RESOURCE_ID = r_res.resource_id;
update jtf_rs_resource_extns_tl res
SET RES.LAST_UPDATE_DATE = l_sysdate,
RES.LAST_UPDATED_BY = l_user_id,
RES.LAST_UPDATE_LOGIN = l_login,
resource_name = r_party.party_name,
SOURCE_LANG = userenv('LANG')
where resource_id = r_res.resource_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
END update_party;
jtf_rs_conc_res_pub.update_supp_contact
(p_overwrite_name => P_OVERWRITE_NAME);
PROCEDURE update_supp_contact
(P_OVERWRITE_NAME IN VARCHAR2 )
is
CURSOR c_res
IS
SELECT resource_id,
source_id
FROM jtf_rs_resource_extns res
WHERE res.category = 'SUPPLIER_CONTACT';
SELECT POV.SEGMENT1 SOURCE_NUMBER,
POC.LAST_NAME || ' , ' || POC.MIDDLE_NAME ||' '|| POC.FIRST_NAME|| ' - '|| POV.VENDOR_NAME SOURCE_NAME ,
NULL EMAIL,
POS.ADDRESS_LINE1 ADDRESS1 ,
POS.ADDRESS_LINE2 ADDRESS2 ,
POS.ADDRESS_LINE3 ADDRESS3 ,
NULL ADDRESS4 ,
POS.CITY CITY ,
POS.ZIP POSTAL_CODE ,
POS.STATE STATE ,
POS.PROVINCE PROVINCE,
POS.COUNTY COUNTY ,
POS.COUNTRY COUNTRY ,
POC.AREA_CODE || ' ' ||POC.PHONE PHONE,
POS.ORG_ID ORG_ID,
-- ORG.NAME ORG_NAME,
POC.FIRST_NAME FIRST_NAME,
POC.MIDDLE_NAME MIDDLE_NAME,
POC.LAST_NAME LAST_NAME
FROM PO_VENDOR_CONTACTS POC,
PO_VENDOR_SITES_ALL POS,
PO_VENDORS POV
-- HR_OPERATING_UNITS ORG
WHERE POC.VENDOR_CONTACT_ID = l_supplier_contact_id
AND POC.VENDOR_SITE_ID = POS.VENDOR_SITE_ID
AND POS.VENDOR_ID = POV.VENDOR_ID;
SELECT name
FROM hr_operating_units
WHERE organization_id = l_org_id;
UPDATE JTF_RS_RESOURCE_EXTNS RES
SET RES.LAST_UPDATE_DATE = l_sysdate,
RES.LAST_UPDATED_BY = l_user_id,
RES.LAST_UPDATE_LOGIN = l_login,
RES.SOURCE_NUMBER = r_supp.source_number,
RES.SOURCE_NAME = r_supp.source_name,
RES.SOURCE_EMAIL = r_supp.email ,
RES.SOURCE_ADDRESS1 = r_supp.address1,
RES.SOURCE_ADDRESS2 = r_supp.address2,
RES.SOURCE_ADDRESS3 = r_supp.address3,
RES.SOURCE_ADDRESS4 = r_supp.address4,
RES.SOURCE_CITY = r_supp.city,
RES.SOURCE_POSTAL_CODE = r_supp.postal_code ,
RES.SOURCE_STATE = r_supp.state,
RES.SOURCE_PROVINCE = r_supp.province,
RES.SOURCE_COUNTY = r_supp.county,
RES.SOURCE_COUNTRY = r_supp.country,
RES.SOURCE_PHONE = r_supp.phone,
RES.SOURCE_ORG_ID = r_supp.org_id,
RES.SOURCE_ORG_NAME = l_org_name,
RES.SOURCE_FIRST_NAME = r_supp.first_name,
RES.SOURCE_MIDDLE_NAME = r_supp.middle_name,
RES.SOURCE_LAST_NAME = r_supp.last_name
WHERE RES.RESOURCE_ID = r_res.resource_id;
update jtf_rs_resource_extns_tl res
SET RES.LAST_UPDATE_DATE = l_sysdate,
RES.LAST_UPDATED_BY = l_user_id,
RES.LAST_UPDATE_LOGIN = l_login,
SOURCE_LANG = userenv('LANG'),
resource_name = r_supp.source_name
where resource_id = r_res.resource_id
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
end update_supp_contact;
SELECT rsc.resource_id
, rsc.object_version_number
, pvc.inactive_date
FROM jtf_rs_resource_extns rsc
,po_vendor_contacts pvc
WHERE rsc.category = 'SUPPLIER_CONTACT'
AND rsc.source_id = pvc.vendor_contact_id
AND trunc(nvl(inactive_date, sysdate)) < trunc(sysdate)
AND trunc(nvl(end_date_active,inactive_date+1+l_active_days)) > trunc(inactive_date+l_active_days);
PROCEDURE update_username
IS
BEGIN
UPDATE jtf_rs_resource_extns res
SET user_name = (SELECT user_name
FROM fnd_user fu
WHERE res.user_id = fu.user_id)
WHERE user_id IS NOT NULL;
END update_username;
PROCEDURE update_userid
IS
l_RETURN_STATUS VARCHAR2(2);
SELECT
RESOURCE_ID,
CATEGORY,
RESOURCE_NUMBER,
SOURCE_ID,
OBJECT_VERSION_NUMBER,
USER_ID,
SOURCE_NAME,
USER_NAME
FROM JTF_RS_RESOURCE_EXTNS_VL jres
WHERE CATEGORY in ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')
AND jres.USER_ID is not null
AND NOT EXISTS
( SELECT 'x'
FROM fnd_user f
WHERE f.user_id = jres.user_id
AND decode(category,'EMPLOYEE', employee_id,'SUPPLIER_CONTACT',supplier_id,customer_id) = jres.source_id
);
SELECT
RESOURCE_ID,
CATEGORY,
RESOURCE_NUMBER,
SOURCE_ID,
OBJECT_VERSION_NUMBER,
USER_ID,
SOURCE_NAME,
USER_NAME
FROM JTF_RS_RESOURCE_EXTNS_VL jres
WHERE CATEGORY in ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')
AND USER_ID is not null
AND trunc(nvl(END_DATE_ACTIVE,SYSDATE)) >= trunc(SYSDATE)
AND NOT EXISTS
( SELECT 'x'
FROM fnd_user f
WHERE f.user_id = jres.user_id
AND decode(category,'EMPLOYEE', employee_id,'SUPPLIER_CONTACT',supplier_id,customer_id) = jres.source_id
AND trunc(sysdate) between trunc(start_date) and trunc(nvl(end_date,sysdate))
);
SELECT
RESOURCE_ID,
CATEGORY,
RESOURCE_NUMBER,
SOURCE_ID,
OBJECT_VERSION_NUMBER,
USER_ID,
SOURCE_NAME,
USER_NAME
FROM JTF_RS_RESOURCE_EXTNS_VL jres
WHERE CATEGORY in ('EMPLOYEE','PARTY','PARTNER','SUPPLIER_CONTACT')
AND trunc(nvl(END_DATE_ACTIVE,SYSDATE)) >= trunc(SYSDATE)
AND jres.USER_ID is null
AND exists (select 'x'
from fnd_user f
where jres.source_id = decode(jres.category,'EMPLOYEE',
employee_id,'SUPPLIER_CONTACT',supplier_id,customer_id)
);
SELECT
RESOURCE_ID,
CATEGORY,
RESOURCE_NUMBER,
SOURCE_ID,
OBJECT_VERSION_NUMBER,
USER_ID,
SOURCE_NAME,
USER_NAME
FROM JTF_RS_RESOURCE_EXTNS_VL jres
WHERE CATEGORY = 'EMPLOYEE'
AND trunc(nvl(END_DATE_ACTIVE,SYSDATE)) >= trunc(SYSDATE)
AND jres.USER_ID is null
AND exists (select 'x'
from fnd_user f
where jres.source_id = employee_id)
union all
SELECT
RESOURCE_ID,
CATEGORY,
RESOURCE_NUMBER,
SOURCE_ID,
OBJECT_VERSION_NUMBER,
USER_ID,
SOURCE_NAME,
USER_NAME
FROM JTF_RS_RESOURCE_EXTNS_VL jres
WHERE CATEGORY = 'SUPPLIER_CONTACT'
AND trunc(nvl(END_DATE_ACTIVE,SYSDATE)) >= trunc(SYSDATE)
AND jres.USER_ID is null
AND exists (select 'x'
from fnd_user f
where jres.source_id = supplier_id)
union all
SELECT
RESOURCE_ID,
CATEGORY,
RESOURCE_NUMBER,
SOURCE_ID,
OBJECT_VERSION_NUMBER,
USER_ID,
SOURCE_NAME,
USER_NAME
FROM JTF_RS_RESOURCE_EXTNS_VL jres
WHERE CATEGORY in ('PARTY','PARTNER')
AND trunc(nvl(END_DATE_ACTIVE,SYSDATE)) >= trunc(SYSDATE)
AND jres.USER_ID is null
AND exists (select 'x'
from fnd_user f
where jres.source_id = customer_id);
SELECT user_id,
user_name
FROM fnd_user
WHERE customer_id = p_source_id
AND trunc(sysdate) BETWEEN trunc(start_date) AND trunc(nvl(end_date,sysdate));
SELECT user_id,
user_name
FROM fnd_user
WHERE supplier_id = p_source_id
AND trunc(sysdate) BETWEEN trunc(start_date) AND trunc(nvl(end_date,sysdate));
SELECT user_id,
user_name
FROM fnd_user
WHERE employee_id = p_source_id
AND trunc(sysdate) BETWEEN trunc(start_date) AND trunc(nvl(end_date,sysdate));
SAVEPOINT UPDATE_USERID_SP;
jtf_rs_resource_pub.update_resource
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_resource_id => l_c_invalid_userid.resource_id,
p_resource_number => l_c_invalid_userid.resource_number,
p_user_id => l_user_id,
p_source_name => l_c_invalid_userid.source_name,
p_object_version_num => l_c_invalid_userid.object_version_number,
p_user_name => l_user_name,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
) ;
fnd_message.set_name('JTF', 'JTF_RS_CONC_UPDATE_USERID_ERR');
jtf_rs_resource_pub.update_resource
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_resource_id => l_c_invalid_active_userid.resource_id,
p_resource_number => l_c_invalid_active_userid.resource_number,
p_user_id => l_user_id,
p_source_name => l_c_invalid_active_userid.source_name,
p_object_version_num => l_c_invalid_active_userid.object_version_number,
p_user_name => l_user_name,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
) ;
fnd_message.set_name('JTF', 'JTF_RS_CONC_UPDATE_USERID_ERR');
jtf_rs_resource_pub.update_resource
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_resource_id => l_c_null_userid.resource_id,
p_resource_number => l_c_null_userid.resource_number,
p_user_id => l_user_id,
p_source_name => l_c_null_userid.source_name,
p_object_version_num => l_c_null_userid.object_version_number,
p_user_name => l_user_name,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
) ;
fnd_message.set_name('JTF', 'JTF_RS_CONC_UPDATE_USERID_ERR');
ROLLBACK TO UPDATE_USERID_SP;
ROLLBACK TO UPDATE_USERID_SP;
END update_userid;
jtf_rs_conc_res_pub.update_username;
jtf_rs_conc_res_pub.update_userid;