[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select address_line_1,
address_line_2,
address_line_3 ,
town_or_city ,
country,
postal_code
from hr_locations
where location_id = l_address_id;
select name
from hr_all_organization_units
where l_business_group_id = organization_id;
select full_name, person_id
from per_all_people_f
where trunc(sysdate ) between effective_start_date and effective_end_date
and person_id = (select source_id from jtf_rs_resource_extns
and resource_id = p_source_mgr_id);
select source_name, source_id
from jtf_rs_resource_extns
where resource_id = p_source_mgr_id;
select user_id
from fnd_user
where user_name = l_user_name;
/* Cursor Variables to insert Job and Manager Deatils */
cursor job_cur(l_person_id number)
is
select object_version_number,
assignment_id,
effective_start_date,
effective_end_date,
business_group_id,
location_id,
special_ceiling_step_id,
last_update_date,
job_id
from per_all_assignments_f
where person_id = l_person_id
and effective_start_date = (select max(effective_start_date)
from per_all_assignments_f
where person_id = l_person_id
and assignment_type = 'E'
and primary_flag = 'Y')
and effective_end_date = (select max(effective_end_date)
from per_all_assignments_f
where person_id = l_person_id
and assignment_type = 'E'
and primary_flag = 'Y')
and assignment_type = 'E'
and primary_flag = 'Y';
l_datetrack_update_mode varchar2(2000) := 'UPDATE';
l_spp_delete_warning boolean;
select name
from per_jobs
where job_id = l_job_id;
if(trunc(job_rec.last_update_date)= trunc(sysdate)) then
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
hr_assignment_api.update_emp_asg
(p_effective_date => trunc(sysdate)
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => job_rec.assignment_id
,p_object_version_number => l_object_version_number_assg
,p_supervisor_id => l_mgr_source_id
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_start_date
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning );
if(trunc(job_rec.last_update_date)= trunc(sysdate)) then
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
hr_assignment_api.update_emp_asg_criteria
(p_effective_date => trunc(sysdate)
,p_datetrack_update_mode => l_datetrack_update_mode
,p_assignment_id => job_rec.assignment_id
,p_object_version_number => l_object_version_number_assg
,p_job_id => l_job_id
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_start_date
,p_people_group_id => l_people_group_id
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_earning
,p_tax_district_changed_warning => l_tax_district_changed_earning
);
,p_wh_update_date => null
,p_sales_tax_geocode => null
,p_sales_tax_inside_city_limits => '1'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_salesrep_id => l_salesrep_id
);
PROCEDURE update_resource
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
P_RESOURCE_ID IN NUMBER,
P_RESOURCE_NUMBER IN VARCHAR2,
P_RESOURCE_NAME IN VARCHAR2 ,
P_SOURCE_NAME IN VARCHAR2 ,
P_ADDRESS_ID IN VARCHAR2 ,
P_SOURCE_OFFICE IN VARCHAR2 ,
P_SOURCE_MAILSTOP IN VARCHAR2 ,
P_SOURCE_LOCATION IN VARCHAR2 ,
P_SOURCE_PHONE IN VARCHAR2 ,
P_SOURCE_EMAIL IN VARCHAR2 ,
P_OBJECT_VERSION_NUMBER IN NUMBER,
P_APPROVED IN VARCHAR2 DEFAULT 'N',
P_SOURCE_JOB_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
P_SOURCE_JOB_TITLE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
P_SALESREP_NUMBER IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
P_SALES_CREDIT_TYPE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
P_END_DATE_ACTIVE IN DATE DEFAULT FND_API.G_MISS_DATE,
P_USER_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
P_USER_NAME IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
P_MGR_RESOURCE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
P_ORG_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
P_TIME_ZONE IN NUMBER DEFAULT FND_API.G_MISS_NUM,
P_COST_PER_HR IN NUMBER DEFAULT FND_API.G_MISS_NUM,
P_PRIMARY_LANGUAGE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
P_SECONDARY_LANGUAGE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
P_SUPPORT_SITE_ID IN NUMBER DEFAULT FND_API.G_MISS_NUM,
P_SOURCE_MOBILE_PHONE IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR,
P_SOURCE_PAGER IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
select RESOURCE_NAME
, SOURCE_NAME
, SOURCE_NUMBER
, SOURCE_MGR_NAME
, SOURCE_JOB_TITLE
, SOURCE_ADDRESS1
, SOURCE_ADDRESS2
, SOURCE_ADDRESS3
, SOURCE_ADDRESS4
, SOURCE_CITY
, SOURCE_STATE
, SOURCE_PROVINCE
, SOURCE_POSTAL_CODE
, SOURCE_COUNTY
, SOURCE_COUNTRY
, SOURCE_PHONE
, SOURCE_EMAIL
, ADDRESS_ID
, SOURCE_OFFICE
, SOURCE_MAILSTOP
, SOURCE_LOCATION
, SOURCE_ID
, SOURCE_MGR_ID
, START_DATE_ACTIVE
, END_DATE_ACTIVE
, TIME_ZONE
, COST_PER_HR
, PRIMARY_LANGUAGE
, SECONDARY_LANGUAGE
, SUPPORT_SITE_ID
, SOURCE_MOBILE_PHONE
, SOURCE_PAGER
, USER_ID
from jtf_rs_resource_extns_vl
where resource_id = p_resource_id;
select salesrep_id,
object_version_number,
sales_credit_type_id,
salesrep_number
from jtf_rs_salesreps
where resource_id = l_resource_id
and org_id = l_org_id;
select user_id
from fnd_user
where employee_id = l_mgr_id;
select user_name
from fnd_user
where user_id = l_user_id;
select attribute_name
, attribute_access_level
from jtf_rs_table_attributes_b;
select object_version_number,
assignment_id,
effective_start_date,
effective_end_date,
business_group_id,
location_id,
special_ceiling_step_id,
last_update_date,
job_id
from per_all_assignments_f
where person_id = l_person_id
and effective_start_date = (select max(effective_start_date)
from per_all_assignments_f
where person_id = l_person_id
and assignment_type = 'E'
and primary_flag = 'Y')
and effective_end_date = (select max(effective_end_date)
from per_all_assignments_f
where person_id = l_person_id
and assignment_type = 'E'
and primary_flag = 'Y')
and assignment_type = 'E'
and primary_flag = 'Y';
select person_id,
employee_number,
last_update_date,
object_version_number
from per_all_people_f
where person_id = l_person_id
and effective_start_date = (select max(effective_start_date)
from per_all_people_f
where person_id = l_person_id
and employee_number is not null )
and effective_end_date = (select max(effective_end_date)
from per_all_people_f
where person_id = l_person_id
and employee_number is not null )
and employee_number is not null;
l_datetrack_update_mode varchar2(2000) := 'UPDATE';
l_spp_delete_warning boolean;
select ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3 ,
TOWN_OR_CITY ,
COUNTRY,
POSTAL_CODE
from hr_locations
where location_id = l_address_id;
select phone_id,
object_version_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 phone_id,
object_version_number
from per_phones p1
where parent_table = 'PER_ALL_PEOPLE_F'
and parent_id = l_person_id
and phone_type = 'M'
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 = 'M' );
select phone_id,
object_version_number
from per_phones p1
where parent_table = 'PER_ALL_PEOPLE_F'
and parent_id = l_person_id
and phone_type = 'P'
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 = 'P' );
select source_id,
source_name
from jtf_rs_resource_extns
where resource_id = l_resource_id;
-- does not understand G_MISS and inserts G_MISS into
-- the database which causes form crash/junk value
-- bug # 3114608
ok_source_office := old_rec.source_office;
-- does not understand G_MISS and inserts G_MISS into
-- the database which causes form crash/junk value
-- bug # 3114608
ok_source_mailstop := old_rec.source_mailstop;
-- does not understand G_MISS and inserts G_MISS into
-- the database which causes form crash/junk value
-- bug # 3114608
ok_source_location := old_rec.source_location;
-- does not understand G_MISS and inserts G_MISS into
-- the database which causes form crash/junk value
-- bug # 3114608
ok_source_phone := old_rec.source_phone;
-- does not understand G_MISS and inserts G_MISS into
-- the database which causes form crash/junk value
-- bug # 3114608
ok_source_mobile_phone := old_rec.source_mobile_phone;
-- does not understand G_MISS and inserts G_MISS into
-- the database which causes form crash/junk value
-- bug # 3114608
ok_source_pager := old_rec.source_pager;
-- does not understand G_MISS and inserts G_MISS into
-- the database which causes form crash/junk value
-- bug # 3114608
ok_source_email := old_rec.source_email;
jtf_rs_resource_wf_pub.start_update_resource_wf
(P_API_VERSION => 1.0,
P_COMMIT => p_commit,
P_RESOURCE_ID => p_resource_id,
P_CATEGORY => 'EMPLOYEE',
P_RESOURCE_NUMBER => null,
P_RESOURCE_NAME => ok_resource_name,
P_ADDRESS_ID => ok_address_id,
P_SOURCE_EMAIL => ok_source_email,
P_SOURCE_PHONE => ok_source_phone,
P_SOURCE_OFFICE => ok_source_office,
P_SOURCE_LOCATION => ok_source_location,
P_SOURCE_MAILSTOP => ok_source_mailstop,
P_TIME_ZONE => ok_time_zone,
P_SUPPORT_SITE_ID => ok_support_site_id,
P_PRIMARY_LANGUAGE => ok_primary_language,
P_SECONDARY_LANGUAGE => ok_secondary_language,
P_COST_PER_HR => ok_cost_per_hr,
P_SOURCE_MOBILE_PHONE => ok_source_mobile_phone,
P_SOURCE_PAGER => ok_source_pager,
P_ATTRIBUTE_ACCESS_LEVEL => 'UWN',
P_OBJECT_VERSION_NUMBER => p_object_version_number,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
if(trunc(assg_rec.last_update_date)= trunc(sysdate))
then
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
hr_assignment_api.update_emp_asg
(p_effective_date => trunc(sysdate)
,p_datetrack_update_mode => l_datetrack_update_mode --in ??
,p_assignment_id => assg_rec.assignment_id
,p_object_version_number => l_object_version_number_assg
,p_supervisor_id => l_mgr_source_id
,p_concatenated_segments => l_concatenated_segments
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_comment_id => l_comment_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_start_date
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning );
if(trunc(assg_rec.last_update_date)= trunc(sysdate))
then
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
hr_assignment_api.update_emp_asg_criteria
(p_effective_date => trunc(sysdate)
,p_datetrack_update_mode => l_datetrack_update_mode --in ??
,p_assignment_id => assg_rec.assignment_id
,p_object_version_number => l_object_version_number_assg
,p_location_id => ok_address_id
,p_job_id => l_job_id
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_start_date
,p_people_group_id => l_people_group_id
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_earning
,p_tax_district_changed_warning => l_tax_district_changed_earning
);
if(trunc(per_rec.last_update_date)= trunc(sysdate))
then
l_datetrack_update_mode := 'CORRECTION';
l_datetrack_update_mode := 'UPDATE';
hr_person_api.update_person
(p_effective_date => trunc(sysdate)
,p_datetrack_update_mode => l_datetrack_update_mode
,p_person_id => per_rec.person_id
,p_object_version_number => l_object_version_number_per
,p_employee_number => l_employee_number
,p_internal_location => ok_source_location
,p_mailstop => ok_source_mailstop
,p_office_number => ok_source_office
,p_email_address => ok_source_email
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_full_name => l_full_name
,p_comment_id => l_comment_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning);
hr_phone_api.update_phone(p_phone_id => phone_rec.phone_id,
p_object_version_number => l_object_version_number_phone,
p_effective_date => sysdate,
p_phone_number => ok_source_phone);
hr_phone_api.delete_phone
(p_phone_id => phone_rec.phone_id,
p_object_version_number => l_object_version_number_phone
);
hr_phone_api.update_phone(p_phone_id => mobile_phone_rec.phone_id,
p_object_version_number => l_object_ver_num_mobile_ph,
p_effective_date => sysdate,
p_phone_number => ok_source_mobile_phone);
hr_phone_api.delete_phone
(p_phone_id => mobile_phone_rec.phone_id,
p_object_version_number => l_object_ver_num_mobile_ph
);
hr_phone_api.update_phone(p_phone_id => pager_rec.phone_id,
p_object_version_number => l_object_version_number_pager,
p_effective_date => sysdate,
p_phone_number => ok_source_pager);
hr_phone_api.delete_phone
(p_phone_id => pager_rec.phone_id,
p_object_version_number => l_object_version_number_pager
);
jtf_rs_resource_pub.update_resource
(p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => p_commit
, p_resource_id => p_resource_id
, p_resource_number => null
, p_resource_name => ok_resource_name
, p_SOURCE_NAME => ok_source_name
, p_SOURCE_EMAIL => ok_source_email
, p_SOURCE_PHONE => ok_source_phone
, p_address_id => ok_address_id
, p_source_mailstop => ok_source_mailstop
, p_source_office => ok_source_office
, p_source_location => ok_source_location
, p_source_address1 => address_rec.address_line_1
, p_source_address2 => address_rec.address_line_2
, p_source_address3 => address_rec.address_line_3
, p_source_city => address_rec.town_or_city
, p_source_country => address_rec.country
, p_source_postal_code => address_rec.postal_code
, p_object_version_num => l_object_version_number
, p_source_job_title => l_job_title --p_source_job_title
, p_end_date_active => p_end_date_active
, p_source_mgr_id => l_mgr_source_id
, p_source_mgr_name => l_mgr_source_name
, p_user_id => p_user_id
, p_user_name => l_user_name
, p_time_zone => ok_time_zone
, p_cost_per_hr => ok_cost_per_hr
, p_primary_language => ok_primary_language
, p_secondary_language => ok_secondary_language
, p_support_site_id => ok_support_site_id
, p_source_mobile_phone => ok_source_mobile_phone
, p_source_pager => ok_source_pager
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data);
jtf_rs_salesreps_pub.update_salesrep
(P_API_VERSION => 1.0,
P_SALESREP_ID => salesrep_rec.salesrep_id,
P_SALES_CREDIT_TYPE_ID => p_sales_credit_type_id,
P_NAME => ok_resource_name,
P_ORG_ID => p_org_id,
p_salesrep_number => p_salesrep_number,
P_OBJECT_VERSION_NUMBER => l_srp_object_version_number,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data);
jtf_rs_resource_wf_pub.start_update_resource_wf
(P_API_VERSION => 1.0,
P_COMMIT => p_commit,
P_RESOURCE_ID => p_resource_id,
P_CATEGORY => 'EMPLOYEE',
P_RESOURCE_NUMBER => null,
P_RESOURCE_NAME => aprvl_resource_name,
P_ADDRESS_ID => aprvl_address_id,
P_SOURCE_EMAIL => aprvl_source_email,
P_SOURCE_PHONE => aprvl_source_phone,
P_SOURCE_OFFICE => aprvl_source_office,
P_SOURCE_LOCATION => aprvl_source_location,
P_SOURCE_MAILSTOP => aprvl_source_mailstop,
P_TIME_ZONE => aprvl_time_zone,
P_SUPPORT_SITE_ID => aprvl_support_site_id,
P_PRIMARY_LANGUAGE => aprvl_primary_language,
P_SECONDARY_LANGUAGE => aprvl_secondary_language,
P_COST_PER_HR => aprvl_cost_per_hr,
P_SOURCE_MOBILE_PHONE => aprvl_source_mobile_phone,
P_SOURCE_PAGER => aprvl_source_pager,
P_ATTRIBUTE_ACCESS_LEVEL => 'UWA',
P_OBJECT_VERSION_NUMBER => p_object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
fnd_message.set_name ('JTF', 'JTF_RS_UPDATED');
fnd_message.set_name ('JTF', 'JTF_RS_UPDATED');
fnd_message.set_name ('JTF', 'JTF_RS_UPDATED');
END update_resource;