[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;
--be updated with proper values.
--Bug15934522
--Current_employee-flag = 'Y' or current_npw_flag = 'Y' condition
--added to avoid 'Contact' type data.
CURSOR c_ppf(p_person_id IN NUMBER) IS
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,
current_npw_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;
Before passing end_date_active to update_resource_role_relate
verifying whether start_date is greater than current role assignment start date or not.
New CURSOR c_existing_role_relations created to get Role Relations, which are between
Job Assignment Start and Date.
Job changed Case :
When Role relations found for CC program date between StartDateActive and EndDateActive
then (no action before this fix) then verify whether the same role falling between
JobAssignmentStartDate and JobAssignmentEndDate then verify role relate start date
and end date is as required (review IF condition introduced). If not then modify
StartDate and EndDate of existing role related.
Job Not Changed Case :
When No role relate found with Program Run Date between Role related Start and End Date
then we were creating Role Relate(current behaviour).
When the same role is falling between AssignmentStartDate and AssignmentEndDate then
while creating throwing an error.
To avoid this, before creating role relate verifying whether role relate existing
between assignment start date and assignment end date then updating existing record
end date with Maximum(Job assignment end date, role relation end date)
and start date with Minimum (Job Assignment Start Date,Role relation start date).
*/
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,start_date_active
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,start_date_active,end_date_active
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 (( l_asg_start_date between trunc(start_date_active) and trunc(nvl(end_date_active,g_run_date)))
OR
( l_asg_end_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 ,start_date_active
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 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;
debug(p_message=>' update_job_roles (+)',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
debug(p_message=>' Old job Update role Relate call for Resource :'||p_resource_id ||' and Role Id :'||l_job_roles_rec.role_id,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
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_role_relate_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;
debug(p_message=>'Update role Relate is Success ',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
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_existing_role_relations.role_relate_id,
p_start_date_active => least(l_asg_start_date,l_existing_role_relations.start_date_active),
p_end_date_active => greatest(l_asg_new_end_date,NVL(l_existing_role_relations.end_date_active,to_date('31/12/4712', 'DD/MM/YYYY'))),
p_object_version_num => l_existing_role_relations.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;
debug(p_message=>' New job Update role Relate call for Resource :'||p_resource_id ||' and Role Id :'||l_job_roles_rec.role_id,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
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_role_relate_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
);
debug(p_message=>'Update role Relate is errored out',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
fnd_msg_pub.delete_msg;
debug(p_message=>'Update role Relate is Success ',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
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 => l_existing_role_relations.role_relate_id,
p_start_date_active => least(l_asg_start_date,l_existing_role_relations.start_date_active),
p_end_date_active => greatest(l_asg_new_end_date,NVL(l_existing_role_relations.end_date_active,to_date('31/12/4712', 'DD/MM/YYYY'))),
p_object_version_num => l_existing_role_relations.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;
debug(p_message=>' Update_job_roles (-)',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
END update_job_roles;
l_is_to_be_updated := 'N';
l_update_extn := 'N';
debug(p_message=>' update_employee (+)',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
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,
poc.EMAIL_ADDRESS 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;