[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update Resource
These procedures do the business validations and then call the appropriate
table handlers to do the actual inserts and updates.
******************************************************************************************/
/* Package variables. */
G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_RESOURCE_PVT';
select party_site_id
from hz_party_sites
where party_id = l_party_id
and identifying_address_flag = 'Y'
and status = 'A';
SELECT 'Y'
FROM jtf_rs_resource_extns
WHERE ROWID = l_rowid;
SELECT 'X'
FROM jtf_rs_resource_extns
WHERE resource_id = l_resource_id;
SELECT jtf_rs_resource_extns_s.nextval
INTO l_resource_id
FROM dual;
SELECT jtf_rs_resource_number_s.nextval
INTO l_resource_number
FROM dual;
jtf_rs_resource_extns_aud_pvt.insert_resource
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => l_resource_id,
P_RESOURCE_NUMBER => l_resource_number,
P_CATEGORY => l_category,
P_SOURCE_ID => l_source_id,
P_ADDRESS_ID => l_address_id,
P_CONTACT_ID => l_contact_id,
P_MANAGING_EMP_ID => l_managing_emp_id,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_TIME_ZONE => l_time_zone,
P_COST_PER_HR => l_cost_per_hr,
P_PRIMARY_LANGUAGE => l_primary_language,
P_SECONDARY_LANGUAGE => l_secondary_language,
P_SUPPORT_SITE_ID => l_support_site_id,
P_IES_AGENT_LOGIN => l_ies_agent_login,
P_SERVER_GROUP_ID => l_server_group_id,
P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
P_COST_CENTER => l_cost_center,
P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
P_COMP_CURRENCY_CODE => l_comp_currency_code,
P_COMMISSIONABLE_FLAG => l_commissionable_flag,
P_HOLD_REASON_CODE => l_hold_reason_code,
P_HOLD_PAYMENT => l_hold_payment,
P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
P_USER_ID => l_user_id,
P_TRANSACTION_NUMBER => l_transaction_number,
--P_LOCATION => l_location,
P_OBJECT_VERSION_NUMBER => 1,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
/* Insert the row into the table by calling the table handler. */
jtf_rs_resource_extns_pkg.insert_row(
x_rowid => l_rowid,
x_resource_id => l_resource_id,
x_category => l_category,
x_resource_number => l_resource_number,
x_source_id => l_source_id,
x_address_id => l_address_id,
x_contact_id => l_contact_id,
x_managing_employee_id => l_managing_emp_id,
x_start_date_active => l_start_date_active,
x_end_date_active => l_end_date_active,
x_time_zone => l_time_zone,
x_cost_per_hr => l_cost_per_hr,
x_primary_language => l_primary_language,
x_secondary_language => l_secondary_language,
x_support_site_id => l_support_site_id,
x_ies_agent_login => l_ies_agent_login,
x_server_group_id => l_server_group_id,
x_assigned_to_group_id => l_assigned_to_group_id,
x_cost_center => l_cost_center,
x_charge_to_cost_center => l_charge_to_cost_center,
x_compensation_currency_code => l_comp_currency_code,
x_commissionable_flag => l_commissionable_flag,
x_hold_reason_code => l_hold_reason_code,
x_hold_payment => l_hold_payment,
x_comp_service_team_id => l_comp_service_team_id,
x_user_id => l_user_id,
--x_location => l_location,
x_transaction_number => l_transaction_number,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_attribute11 => p_attribute11,
x_attribute12 => p_attribute12,
x_attribute13 => p_attribute13,
x_attribute14 => p_attribute14,
x_attribute15 => p_attribute15,
x_attribute_category => p_attribute_category,
x_creation_date => SYSDATE,
x_created_by => jtf_resource_utl.created_by,
x_last_update_date => SYSDATE,
x_last_updated_by => jtf_resource_utl.updated_by,
x_last_update_login => jtf_resource_utl.login_id
);
SELECT 'Y'
FROM jtf_rs_resource_extns
WHERE ROWID = l_rowid;
SELECT 'X'
FROM jtf_rs_resource_extns
WHERE resource_id = l_resource_id;
SELECT job_id
FROM per_all_assignments_f
WHERE person_id = p_source_id
AND primary_flag = 'Y'
AND assignment_type in ('E','C')
AND trunc(sysdate) between effective_start_date and effective_end_date;
SELECT ppf.party_id
FROM per_all_people_f ppf
WHERE ppf.person_id = p_person_id
AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
SELECT jtf_rs_resource_extns_s.nextval
INTO l_resource_id
FROM dual;
SELECT jtf_rs_resource_number_s.nextval
INTO l_resource_number
FROM dual;
jtf_rs_resource_extns_aud_pvt.insert_resource
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => l_resource_id,
P_RESOURCE_NUMBER => l_resource_number,
P_CATEGORY => l_category,
P_SOURCE_ID => l_source_id,
P_ADDRESS_ID => l_address_id,
P_CONTACT_ID => l_contact_id,
P_MANAGING_EMP_ID => l_managing_emp_id,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_TIME_ZONE => l_time_zone,
P_COST_PER_HR => l_cost_per_hr,
P_PRIMARY_LANGUAGE => l_primary_language,
P_SECONDARY_LANGUAGE => l_secondary_language,
P_SUPPORT_SITE_ID => l_support_site_id,
P_IES_AGENT_LOGIN => l_ies_agent_login,
P_SERVER_GROUP_ID => l_server_group_id,
P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
P_COST_CENTER => l_cost_center,
P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
P_COMP_CURRENCY_CODE => l_comp_currency_code,
P_COMMISSIONABLE_FLAG => l_commissionable_flag,
P_HOLD_REASON_CODE => l_hold_reason_code,
P_HOLD_PAYMENT => l_hold_payment,
P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
P_USER_ID => l_user_id,
P_TRANSACTION_NUMBER => l_transaction_number,
--P_LOCATION => l_location,
P_OBJECT_VERSION_NUMBER => 1,
P_RESOURCE_NAME => l_RESOURCE_NAME,
P_SOURCE_NAME => l_SOURCE_NAME,
P_SOURCE_NUMBER => l_SOURCE_NUMBER,
P_SOURCE_JOB_TITLE => l_SOURCE_JOB_TITLE ,
P_SOURCE_EMAIL => l_SOURCE_EMAIL ,
P_SOURCE_PHONE => l_SOURCE_PHONE ,
P_SOURCE_ORG_ID => l_SOURCE_ORG_ID ,
P_SOURCE_ORG_NAME => l_SOURCE_ORG_NAME ,
P_SOURCE_ADDRESS1 => l_SOURCE_ADDRESS1 ,
P_SOURCE_ADDRESS2 => l_SOURCE_ADDRESS2 ,
P_SOURCE_ADDRESS3 => l_SOURCE_ADDRESS3 ,
P_SOURCE_ADDRESS4 => l_SOURCE_ADDRESS4 ,
P_SOURCE_CITY => l_SOURCE_CITY ,
P_SOURCE_POSTAL_CODE => l_SOURCE_POSTAL_CODE ,
P_SOURCE_STATE => l_SOURCE_STATE ,
P_SOURCE_PROVINCE => l_SOURCE_PROVINCE ,
P_SOURCE_COUNTY => l_SOURCE_COUNTY ,
P_SOURCE_COUNTRY => l_SOURCE_COUNTRY ,
P_SOURCE_MGR_ID => l_SOURCE_MGR_ID ,
P_SOURCE_MGR_NAME => l_SOURCE_MGR_NAME ,
P_SOURCE_BUSINESS_GRP_ID => l_SOURCE_BUSINESS_GRP_ID ,
P_SOURCE_BUSINESS_GRP_NAME => l_SOURCE_BUSINESS_GRP_NAME ,
P_SOURCE_FIRST_NAME => l_source_first_name ,
P_SOURCE_LAST_NAME => l_source_last_name ,
P_SOURCE_MIDDLE_NAME => l_source_middle_name ,
P_SOURCE_CATEGORY => l_source_category ,
P_SOURCE_STATUS => l_source_status ,
P_SOURCE_OFFICE => l_source_office ,
P_SOURCE_LOCATION => l_source_location ,
P_SOURCE_MAILSTOP => l_source_mailstop ,
P_SOURCE_JOB_ID => l_source_job_id ,
P_PARTY_ID => l_party_id ,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data,
P_SOURCE_MOBILE_PHONE => l_source_mobile_phone ,
P_SOURCE_PAGER => l_source_pager
);
/* Insert the row into the table by calling the table handler. */
jtf_rs_resource_extns_pkg.insert_row(
x_rowid => l_rowid,
x_resource_id => l_resource_id,
x_category => l_category,
x_resource_number => l_resource_number,
x_source_id => l_source_id,
x_address_id => l_address_id,
x_contact_id => l_contact_id,
x_managing_employee_id => l_managing_emp_id,
x_start_date_active => l_start_date_active,
x_end_date_active => l_end_date_active,
x_time_zone => l_time_zone,
x_cost_per_hr => l_cost_per_hr,
x_primary_language => l_primary_language,
x_secondary_language => l_secondary_language,
x_support_site_id => l_support_site_id,
x_ies_agent_login => l_ies_agent_login,
x_server_group_id => l_server_group_id,
x_assigned_to_group_id => l_assigned_to_group_id,
x_cost_center => l_cost_center,
x_charge_to_cost_center => l_charge_to_cost_center,
x_compensation_currency_code => l_comp_currency_code,
x_commissionable_flag => l_commissionable_flag,
x_hold_reason_code => l_hold_reason_code,
x_hold_payment => l_hold_payment,
x_comp_service_team_id => l_comp_service_team_id,
x_user_id => l_user_id,
--x_location => l_location,
x_transaction_number => l_transaction_number,
x_attribute1 => p_attribute1,
x_attribute2 => p_attribute2,
x_attribute3 => p_attribute3,
x_attribute4 => p_attribute4,
x_attribute5 => p_attribute5,
x_attribute6 => p_attribute6,
x_attribute7 => p_attribute7,
x_attribute8 => p_attribute8,
x_attribute9 => p_attribute9,
x_attribute10 => p_attribute10,
x_attribute11 => p_attribute11,
x_attribute12 => p_attribute12,
x_attribute13 => p_attribute13,
x_attribute14 => p_attribute14,
x_attribute15 => p_attribute15,
x_attribute_category => p_attribute_category,
x_creation_date => SYSDATE,
x_created_by => jtf_resource_utl.created_by,
x_last_update_date => SYSDATE,
x_last_updated_by => jtf_resource_utl.updated_by,
x_last_update_login => jtf_resource_utl.login_id,
x_resource_name => l_resource_name,
x_source_name => l_source_name,
x_source_number => l_source_number,
x_source_job_title => l_source_job_title,
x_source_email => l_source_email,
x_source_phone => l_source_phone,
x_source_org_id => l_source_org_id,
x_source_org_name => l_source_org_name,
x_source_address1 => l_source_address1,
x_source_address2 => l_source_address2,
x_source_address3 => l_source_address3,
x_source_address4 => l_source_address4,
x_source_city => l_source_city,
x_source_postal_code => l_source_postal_code,
x_source_state => l_source_state,
x_source_province => l_source_province,
x_source_county => l_source_county,
x_source_country => l_source_country,
x_source_mgr_id => l_source_mgr_id,
x_source_mgr_name => l_source_mgr_name,
x_source_business_grp_id => l_source_business_grp_id,
x_source_business_grp_name=> l_source_business_grp_name,
x_source_first_name => l_source_first_name,
x_source_last_name => l_source_last_name,
x_source_middle_name => l_source_middle_name,
x_source_category => l_source_category,
x_source_status => l_source_status,
x_source_office => l_source_office,
x_source_location => l_source_location,
x_source_mailstop => l_source_mailstop,
x_source_mobile_phone => l_source_mobile_phone,
x_source_pager => l_source_pager,
x_source_job_id => l_source_job_id,
x_party_id => l_party_id,
x_user_name => l_user_name
);
/* Calling work API for insert record into wf_local tables. */
/* added by baianand on 08/12/2002 */
begin
jtf_rs_wf_integration_pub.create_resource
(p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_resource_id => l_resource_id
,p_resource_name => l_resource_name
,p_category => l_category
,p_user_id => l_user_id
,p_email_address => l_source_email
,p_start_date_active => l_start_date_active
,p_end_date_active => l_end_date_active
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
/* Procedure to update the resource based on input values passed by calling routines. */
PROCEDURE update_resource
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
--P_LOCATION IN MDSYS.SDO_GEOMETRY,
P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE,
P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
CURSOR c_resource_update(
l_resource_id IN NUMBER )
IS
SELECT
DECODE(p_managing_emp_id, fnd_api.g_miss_num, managing_employee_id, p_managing_emp_id) managing_emp_id,
-- added trunc on the dates 6feb 2002
DECODE(p_start_date_active, fnd_api.g_miss_date, start_date_active, trunc(p_start_date_active)) start_date_active,
DECODE(p_end_date_active, fnd_api.g_miss_date, end_date_active, trunc(p_end_date_active)) end_date_active,
DECODE(p_time_zone, fnd_api.g_miss_num, time_zone, p_time_zone) time_zone,
DECODE(p_cost_per_hr, fnd_api.g_miss_num, cost_per_hr, p_cost_per_hr) cost_per_hr,
DECODE(p_primary_language, fnd_api.g_miss_char, primary_language, p_primary_language) primary_language,
DECODE(p_secondary_language, fnd_api.g_miss_char, secondary_language, p_secondary_language) secondary_language,
DECODE(p_support_site_id, fnd_api.g_miss_num, support_site_id, p_support_site_id) support_site_id,
DECODE(p_ies_agent_login, fnd_api.g_miss_char, ies_agent_login, p_ies_agent_login) ies_agent_login,
DECODE(p_server_group_id, fnd_api.g_miss_num, server_group_id, p_server_group_id) server_group_id,
DECODE(p_assigned_to_group_id, fnd_api.g_miss_num, assigned_to_group_id, p_assigned_to_group_id) assigned_to_group_id,
DECODE(p_cost_center, fnd_api.g_miss_char, cost_center, p_cost_center) cost_center,
DECODE(p_charge_to_cost_center, fnd_api.g_miss_char, charge_to_cost_center, p_charge_to_cost_center) charge_to_cost_center,
DECODE(p_comp_currency_code, fnd_api.g_miss_char, compensation_currency_code, p_comp_currency_code) comp_currency_code,
DECODE(p_commissionable_flag, fnd_api.g_miss_char, commissionable_flag, p_commissionable_flag) commissionable_flag,
DECODE(p_hold_reason_code, fnd_api.g_miss_char, hold_reason_code, p_hold_reason_code) hold_reason_code,
DECODE(p_hold_payment, fnd_api.g_miss_char, hold_payment, p_hold_payment) hold_payment,
DECODE(p_comp_service_team_id, fnd_api.g_miss_num, comp_service_team_id, p_comp_service_team_id) comp_service_team_id,
DECODE(p_user_id, fnd_api.g_miss_num, user_id, p_user_id) user_id,
--DECODE(p_location, jtf_rs_resource_pub.g_miss_location, location, p_location) location,
DECODE(p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1) attribute1,
DECODE(p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2) attribute2,
DECODE(p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3) attribute3,
DECODE(p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4) attribute4,
DECODE(p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5) attribute5,
DECODE(p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6) attribute6,
DECODE(p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7) attribute7,
DECODE(p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8) attribute8,
DECODE(p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9) attribute9,
DECODE(p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10) attribute10,
DECODE(p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11) attribute11,
DECODE(p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12) attribute12,
DECODE(p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13) attribute13,
DECODE(p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14) attribute14,
DECODE(p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15) attribute15,
DECODE(p_attribute_category, fnd_api.g_miss_char, attribute_category, p_attribute_category) attribute_category,
category,
resource_number,
source_id,
address_id,
contact_id,
transaction_number
FROM jtf_rs_resource_extns_vl
WHERE resource_id = l_resource_id;
resource_rec c_resource_update%ROWTYPE;
SELECT u.user_id
FROM fnd_user u,
jtf_rs_resource_extns r
WHERE u.user_id = r.user_id
AND u.user_id = l_assigned_to_group_id;
SELECT start_date_active
FROM jtf_rs_resource_extns
WHERE resource_id = l_resource_id;
SELECT min(start_date_active),
max(end_date_active)
FROM jtf_rs_role_relations
WHERE role_resource_type = 'RS_INDIVIDUAL'
AND role_resource_id = l_resource_id
AND nvl(delete_flag, 'N') <> 'Y'
AND end_date_active is not null;
SELECT min(start_date_active)
FROM jtf_rs_role_relations
WHERE role_resource_type = 'RS_INDIVIDUAL'
AND role_resource_id = l_resource_id
AND nvl(delete_flag, 'N') <> 'Y'
AND end_date_active is null;
SELECT min(jrrr.start_date_active),
max(jrrr.end_date_active)
FROM jtf_rs_group_members jrgm,
jtf_rs_role_relations jrrr
WHERE jrgm.group_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrgm.delete_flag, 'N') <> 'Y'
AND jrgm.resource_id = l_resource_id
AND jrrr.end_date_active is not null;
SELECT min(jrrr.start_date_active)
FROM jtf_rs_group_members jrgm,
jtf_rs_role_relations jrrr
WHERE jrgm.group_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrgm.delete_flag, 'N') <> 'Y'
AND jrgm.resource_id = l_resource_id
AND jrrr.end_date_active is null;
SELECT min(jrrr.start_date_active),
max(jrrr.end_date_active)
FROM jtf_rs_team_members jrtm,
jtf_rs_role_relations jrrr
WHERE jrtm.team_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrtm.delete_flag, 'N') <> 'Y'
AND jrtm.team_resource_id = l_resource_id
AND jrtm.resource_type = 'INDIVIDUAL'
AND jrrr.end_date_active is not null;
SELECT min(jrrr.start_date_active)
FROM jtf_rs_team_members jrtm,
jtf_rs_role_relations jrrr
WHERE jrtm.team_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrtm.delete_flag, 'N') <> 'Y'
AND jrtm.team_resource_id = l_resource_id
AND jrtm.resource_type = 'INDIVIDUAL'
AND jrrr.end_date_active is null;
SELECT min(start_date_active),
max(end_date_active)
FROM jtf_rs_salesreps
WHERE resource_id = l_resource_id
AND end_date_active is not null;
SELECT min(start_date_active)
FROM jtf_rs_salesreps
WHERE resource_id = l_resource_id
AND end_date_active is null;
SELECT 'Y'
FROM jtf_rs_resource_extns
WHERE user_id = l_user_id
AND resource_id <> l_resource_id;
SAVEPOINT update_resource_pvt;
'UPDATE_RESOURCE',
'B',
'C')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'B',
'C')
THEN
jtf_rs_resource_cuhk.update_resource_pre(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
-- p_location => l_location,
x_return_status => x_return_status);
'UPDATE_RESOURCE',
'B',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'B',
'V')
THEN
jtf_rs_resource_vuhk.update_resource_pre(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
-- p_location => l_location,
x_return_status => x_return_status);
'UPDATE_RESOURCE',
'B',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'B',
'I')
THEN
jtf_rs_resource_iuhk.update_resource_pre(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
--p_location => l_location,
x_return_status => x_return_status);
OPEN c_resource_update(l_resource_id);
FETCH c_resource_update INTO resource_rec;
IF c_resource_update%NOTFOUND THEN
IF c_resource_update%ISOPEN THEN
CLOSE c_resource_update;
jtf_rs_resource_extns_aud_pvt.update_resource
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => l_resource_id,
P_RESOURCE_NUMBER => resource_rec.resource_number,
P_CATEGORY => resource_rec.category,
P_SOURCE_ID => resource_rec.source_id,
P_ADDRESS_ID => resource_rec.address_id,
P_CONTACT_ID => resource_rec.contact_id,
P_MANAGING_EMP_ID => resource_rec.managing_emp_id,
P_START_DATE_ACTIVE => resource_rec.start_date_active,
P_END_DATE_ACTIVE => resource_rec.end_date_active,
P_TIME_ZONE => resource_rec.time_zone,
P_COST_PER_HR => resource_rec.cost_per_hr,
P_PRIMARY_LANGUAGE => resource_rec.primary_language,
P_SECONDARY_LANGUAGE => resource_rec.secondary_language,
P_SUPPORT_SITE_ID => resource_rec.support_site_id,
P_IES_AGENT_LOGIN => resource_rec.ies_agent_login,
P_SERVER_GROUP_ID => resource_rec.server_group_id,
P_ASSIGNED_TO_GROUP_ID => resource_rec.assigned_to_group_id,
P_COST_CENTER => resource_rec.cost_center,
P_CHARGE_TO_COST_CENTER => resource_rec.charge_to_cost_center,
P_COMP_CURRENCY_CODE => resource_rec.comp_currency_code,
P_COMMISSIONABLE_FLAG => resource_rec.commissionable_flag,
P_HOLD_REASON_CODE => resource_rec.hold_reason_code,
P_HOLD_PAYMENT => resource_rec.hold_payment,
P_COMP_SERVICE_TEAM_ID => resource_rec.comp_service_team_id,
P_USER_ID => resource_rec.user_id,
P_TRANSACTION_NUMBER => resource_rec.transaction_number,
-- P_LOCATION => resource_rec.location,
P_OBJECT_VERSION_NUMBER => p_object_version_num + 1,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
/* Update the row into the table by calling the table handler. */
jtf_rs_resource_extns_pkg.update_row(
x_resource_id => l_resource_id,
x_category => resource_rec.category,
x_resource_number => resource_rec.resource_number,
x_source_id => resource_rec.source_id,
x_address_id => resource_rec.address_id,
x_contact_id => resource_rec.contact_id,
x_managing_employee_id => resource_rec.managing_emp_id,
x_start_date_active => resource_rec.start_date_active,
x_end_date_active => resource_rec.end_date_active,
x_time_zone => resource_rec.time_zone,
x_cost_per_hr => resource_rec.cost_per_hr,
x_primary_language => resource_rec.primary_language,
x_secondary_language => resource_rec.secondary_language,
x_support_site_id => resource_rec.support_site_id,
x_ies_agent_login => resource_rec.ies_agent_login,
x_server_group_id => resource_rec.server_group_id,
x_assigned_to_group_id => resource_rec.assigned_to_group_id,
x_cost_center => resource_rec.cost_center,
x_charge_to_cost_center => resource_rec.charge_to_cost_center,
x_compensation_currency_code => resource_rec.comp_currency_code,
x_commissionable_flag => resource_rec.commissionable_flag,
x_hold_reason_code => resource_rec.hold_reason_code,
x_hold_payment => resource_rec.hold_payment,
x_comp_service_team_id => resource_rec.comp_service_team_id,
x_user_id => resource_rec.user_id,
--x_location => resource_rec.location,
x_transaction_number => resource_rec.transaction_number,
x_object_version_number => l_object_version_num,
x_attribute1 => resource_rec.attribute1,
x_attribute2 => resource_rec.attribute2,
x_attribute3 => resource_rec.attribute3,
x_attribute4 => resource_rec.attribute4,
x_attribute5 => resource_rec.attribute5,
x_attribute6 => resource_rec.attribute6,
x_attribute7 => resource_rec.attribute7,
x_attribute8 => resource_rec.attribute8,
x_attribute9 => resource_rec.attribute9,
x_attribute10 => resource_rec.attribute10,
x_attribute11 => resource_rec.attribute11,
x_attribute12 => resource_rec.attribute12,
x_attribute13 => resource_rec.attribute13,
x_attribute14 => resource_rec.attribute14,
x_attribute15 => resource_rec.attribute15,
x_attribute_category => resource_rec.attribute_category,
x_last_update_date => SYSDATE,
x_last_updated_by => jtf_resource_utl.updated_by,
x_last_update_login => jtf_resource_utl.login_id
);
IF c_resource_update%ISOPEN THEN
CLOSE c_resource_update;
IF c_resource_update%ISOPEN THEN
CLOSE c_resource_update;
'UPDATE_RESOURCE',
'A',
'C')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'A',
'C')
THEN
jtf_rs_resource_cuhk.update_resource_post(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
--p_location => l_location,
x_return_status => x_return_status);
'UPDATE_RESOURCE',
'A',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'A',
'V')
THEN
jtf_rs_resource_vuhk.update_resource_post(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
--p_location => l_location,
x_return_status => x_return_status);
'UPDATE_RESOURCE',
'A',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'A',
'I')
THEN
jtf_rs_resource_iuhk.update_resource_post(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
--p_location => l_location,
x_return_status => x_return_status);
'UPDATE_RESOURCE',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'M',
'M')
THEN
IF (jtf_rs_resource_cuhk.ok_to_generate_msg(
p_resource_id => l_resource_id,
x_return_status => x_return_status) )
THEN
/* Get the bind data id for the Business Object Instance */
l_bind_data_id := jtf_usr_hks.get_bind_data_id;
ROLLBACK TO update_resource_pvt;
ROLLBACK TO update_resource_pvt;
ROLLBACK TO update_resource_pvt;
END update_resource;
/* Overloaded Procedure to update the resource for Resource Synchronization */
PROCEDURE update_resource
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
--P_LOCATION IN MDSYS.SDO_GEOMETRY,
P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE,
P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE,
P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE,
P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE,
P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE,
P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE,
P_SOURCE_ORG_ID IN NUMBER,
P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE,
P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE,
P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE,
P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE,
P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE,
P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE,
P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE,
P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE,
P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE,
P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE,
P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE,
P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%type,
P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%type,
P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%type,
P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%type,
P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE,
P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE,
P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE,
P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE,
P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE,
P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE,
P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE,
P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE,
P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
P_USER_NAME IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE,
P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
CURSOR c_resource_update(
l_resource_id IN NUMBER )
IS
SELECT
DECODE(p_managing_emp_id, fnd_api.g_miss_num, managing_employee_id, p_managing_emp_id) managing_emp_id,
DECODE(p_start_date_active, fnd_api.g_miss_date, start_date_active, trunc(p_start_date_active)) start_date_active,
DECODE(p_end_date_active, fnd_api.g_miss_date, end_date_active,trunc(p_end_date_active)) end_date_active,
DECODE(p_time_zone, fnd_api.g_miss_num, time_zone, p_time_zone) time_zone,
DECODE(p_cost_per_hr, fnd_api.g_miss_num, cost_per_hr, p_cost_per_hr) cost_per_hr,
DECODE(p_primary_language, fnd_api.g_miss_char, primary_language, p_primary_language) primary_language,
DECODE(p_secondary_language, fnd_api.g_miss_char, secondary_language, p_secondary_language) secondary_language,
DECODE(p_support_site_id, fnd_api.g_miss_num, support_site_id, p_support_site_id) support_site_id,
DECODE(p_ies_agent_login, fnd_api.g_miss_char, ies_agent_login, p_ies_agent_login) ies_agent_login,
DECODE(p_server_group_id, fnd_api.g_miss_num, server_group_id, p_server_group_id) server_group_id,
DECODE(p_assigned_to_group_id, fnd_api.g_miss_num, assigned_to_group_id, p_assigned_to_group_id) assigned_to_group_id,
DECODE(p_cost_center, fnd_api.g_miss_char, cost_center, p_cost_center) cost_center,
DECODE(p_charge_to_cost_center, fnd_api.g_miss_char, charge_to_cost_center, p_charge_to_cost_center) charge_to_cost_center,
DECODE(p_comp_currency_code, fnd_api.g_miss_char, compensation_currency_code, p_comp_currency_code) comp_currency_code,
DECODE(p_commissionable_flag, fnd_api.g_miss_char, commissionable_flag, p_commissionable_flag) commissionable_flag,
DECODE(p_hold_reason_code, fnd_api.g_miss_char, hold_reason_code, p_hold_reason_code) hold_reason_code,
DECODE(p_hold_payment, fnd_api.g_miss_char, hold_payment, p_hold_payment) hold_payment,
DECODE(p_comp_service_team_id, fnd_api.g_miss_num, comp_service_team_id, p_comp_service_team_id) comp_service_team_id,
DECODE(p_user_id, fnd_api.g_miss_num, user_id, p_user_id) user_id,
--DECODE(p_location, jtf_rs_resource_pub.g_miss_location, location, p_location) location,
DECODE(p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1) attribute1,
DECODE(p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2) attribute2,
DECODE(p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3) attribute3,
DECODE(p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4) attribute4,
DECODE(p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5) attribute5,
DECODE(p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6) attribute6,
DECODE(p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7) attribute7,
DECODE(p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8) attribute8,
DECODE(p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9) attribute9,
DECODE(p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10) attribute10,
DECODE(p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11) attribute11,
DECODE(p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12) attribute12,
DECODE(p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13) attribute13,
DECODE(p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14) attribute14,
DECODE(p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15) attribute15,
DECODE(p_attribute_category, fnd_api.g_miss_char, attribute_category, p_attribute_category) attribute_category,
DECODE(p_resource_name, fnd_api.g_miss_char, resource_name, p_resource_name) resource_name,
DECODE(p_source_name, fnd_api.g_miss_char, source_name, p_source_name) source_name,
DECODE(p_source_number, fnd_api.g_miss_char, source_number, p_source_number) source_number,
DECODE(p_source_job_title, fnd_api.g_miss_char, source_job_title, p_source_job_title) source_job_title,
DECODE(p_source_email, fnd_api.g_miss_char, source_email, p_source_email) source_email,
DECODE(p_source_phone, fnd_api.g_miss_char, source_phone, p_source_phone) source_phone,
DECODE(p_source_org_id, fnd_api.g_miss_num, source_org_id, p_source_org_id) source_org_id,
DECODE(p_source_org_name, fnd_api.g_miss_char, source_org_name, p_source_org_name) source_org_name,
DECODE(p_source_address1, fnd_api.g_miss_char, source_address1, p_source_address1) source_address1,
DECODE(p_source_address2, fnd_api.g_miss_char, source_address2, p_source_address2) source_address2,
DECODE(p_source_address3, fnd_api.g_miss_char, source_address3, p_source_address3) source_address3,
DECODE(p_source_address4, fnd_api.g_miss_char, source_address4, p_source_address4) source_address4,
DECODE(p_source_city, fnd_api.g_miss_char, source_city, p_source_city) source_city,
DECODE(p_source_postal_code, fnd_api.g_miss_char, source_postal_code, p_source_postal_code) source_postal_code,
DECODE(p_source_state, fnd_api.g_miss_char, source_state, p_source_state) source_state,
DECODE(p_source_province, fnd_api.g_miss_char, source_province, p_source_province) source_province,
DECODE(p_source_county, fnd_api.g_miss_char, source_county, p_source_county) source_county,
DECODE(p_source_country, fnd_api.g_miss_char, source_country, p_source_country) source_country,
DECODE(p_source_mgr_id, fnd_api.g_miss_num, source_mgr_id, p_source_mgr_id) source_mgr_id,
DECODE(p_source_mgr_name, fnd_api.g_miss_char, source_mgr_name, p_source_mgr_name) source_mgr_name,
DECODE(p_source_business_grp_id, fnd_api.g_miss_num, source_business_grp_id, p_source_business_grp_id) source_business_grp_id,
DECODE(p_source_business_grp_name, fnd_api.g_miss_char, source_business_grp_name, p_source_business_grp_name) source_business_grp_name,
DECODE(p_source_first_name, fnd_api.g_miss_char, source_first_name, p_source_first_name) source_first_name,
DECODE(p_source_middle_name, fnd_api.g_miss_char, source_middle_name, p_source_middle_name) source_middle_name,
DECODE(p_source_last_name, fnd_api.g_miss_char, source_last_name, p_source_last_name) source_last_name,
DECODE(p_source_category, fnd_api.g_miss_char, source_category, p_source_category) source_category,
DECODE(p_source_status, fnd_api.g_miss_char, source_status, p_source_status) source_status,
DECODE(p_source_office, fnd_api.g_miss_char, source_office, p_source_office) source_office,
DECODE(p_source_location, fnd_api.g_miss_char, source_location, p_source_location) source_location,
DECODE(p_source_mailstop, fnd_api.g_miss_char, source_mailstop, p_source_mailstop) source_mailstop,
DECODE(p_source_mobile_phone, fnd_api.g_miss_char, source_mobile_phone, p_source_mobile_phone) source_mobile_phone,
DECODE(p_source_pager, fnd_api.g_miss_char, source_pager, p_source_pager) source_pager,
DECODE(p_address_id, fnd_api.g_miss_num, address_id, p_address_id) address_id,
DECODE(p_user_name, fnd_api.g_miss_char, user_name, p_user_name) user_name,
PERSON_PARTY_ID,
SOURCE_JOB_ID,
category,
resource_number,
source_id,
contact_id,
transaction_number,
address_id old_address_id
FROM jtf_rs_resource_extns_vl
WHERE resource_id = l_resource_id;
resource_rec c_resource_update%ROWTYPE;
SELECT u.user_id
FROM fnd_user u,
jtf_rs_resource_extns r
WHERE u.user_id = r.user_id
AND u.user_id = l_assigned_to_group_id;
SELECT start_date_active
FROM jtf_rs_resource_extns
WHERE resource_id = l_resource_id;
SELECT min(start_date_active),
max(end_date_active)
FROM jtf_rs_role_relations
WHERE role_resource_type = 'RS_INDIVIDUAL'
AND role_resource_id = l_resource_id
AND nvl(delete_flag, 'N') <> 'Y'
AND end_date_active is not null;
SELECT min(start_date_active)
FROM jtf_rs_role_relations
WHERE role_resource_type = 'RS_INDIVIDUAL'
AND role_resource_id = l_resource_id
AND nvl(delete_flag, 'N') <> 'Y'
AND end_date_active is null;
SELECT min(jrrr.start_date_active),
max(jrrr.end_date_active)
FROM jtf_rs_group_members jrgm,
jtf_rs_role_relations jrrr
WHERE jrgm.group_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrgm.delete_flag, 'N') <> 'Y'
AND jrgm.resource_id = l_resource_id
AND jrrr.end_date_active is not null;
SELECT min(jrrr.start_date_active)
FROM jtf_rs_group_members jrgm,
jtf_rs_role_relations jrrr
WHERE jrgm.group_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrgm.delete_flag, 'N') <> 'Y'
AND jrgm.resource_id = l_resource_id
AND jrrr.end_date_active is null;
SELECT min(jrrr.start_date_active),
max(jrrr.end_date_active)
FROM jtf_rs_team_members jrtm,
jtf_rs_role_relations jrrr
WHERE jrtm.team_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrtm.delete_flag, 'N') <> 'Y'
AND jrtm.team_resource_id = l_resource_id
AND jrtm.resource_type = 'INDIVIDUAL'
AND jrrr.end_date_active is not null;
SELECT min(jrrr.start_date_active)
FROM jtf_rs_team_members jrtm,
jtf_rs_role_relations jrrr
WHERE jrtm.team_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrtm.delete_flag, 'N') <> 'Y'
AND jrtm.team_resource_id = l_resource_id
AND jrtm.resource_type = 'INDIVIDUAL'
AND jrrr.end_date_active is null;
SELECT min(start_date_active),
max(end_date_active)
FROM jtf_rs_salesreps
WHERE resource_id = l_resource_id
AND end_date_active is not null;
SELECT min(start_date_active)
FROM jtf_rs_salesreps
WHERE resource_id = l_resource_id
AND end_date_active is null;
SELECT 'Y'
FROM jtf_rs_resource_extns
WHERE user_id = l_user_id
AND resource_id <> l_resource_id;
SAVEPOINT update_resource_pvt;
'UPDATE_RESOURCE',
'B',
'C')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'B',
'C')
THEN
jtf_rs_resource_cuhk.update_resource_pre(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
-- p_location => l_location,
x_return_status => x_return_status);
'UPDATE_RESOURCE',
'B',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'B',
'V')
THEN
jtf_rs_resource_vuhk.update_resource_pre(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
-- p_location => l_location,
x_return_status => x_return_status);
'UPDATE_RESOURCE',
'B',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'B',
'I')
THEN
jtf_rs_resource_iuhk.update_resource_pre(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
--p_location => l_location,
x_return_status => x_return_status);
OPEN c_resource_update(l_resource_id);
FETCH c_resource_update INTO resource_rec;
IF c_resource_update%NOTFOUND THEN
IF c_resource_update%ISOPEN THEN
CLOSE c_resource_update;
jtf_rs_resource_extns_aud_pvt.update_resource
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => l_resource_id,
P_RESOURCE_NUMBER => resource_rec.resource_number,
P_CATEGORY => resource_rec.category,
P_SOURCE_ID => resource_rec.source_id,
P_ADDRESS_ID => resource_rec.address_id,
P_CONTACT_ID => resource_rec.contact_id,
P_MANAGING_EMP_ID => resource_rec.managing_emp_id,
P_START_DATE_ACTIVE => resource_rec.start_date_active,
P_END_DATE_ACTIVE => resource_rec.end_date_active,
P_TIME_ZONE => resource_rec.time_zone,
P_COST_PER_HR => resource_rec.cost_per_hr,
P_PRIMARY_LANGUAGE => resource_rec.primary_language,
P_SECONDARY_LANGUAGE => resource_rec.secondary_language,
P_SUPPORT_SITE_ID => resource_rec.support_site_id,
P_IES_AGENT_LOGIN => resource_rec.ies_agent_login,
P_SERVER_GROUP_ID => resource_rec.server_group_id,
P_ASSIGNED_TO_GROUP_ID => resource_rec.assigned_to_group_id,
P_COST_CENTER => resource_rec.cost_center,
P_CHARGE_TO_COST_CENTER => resource_rec.charge_to_cost_center,
P_COMP_CURRENCY_CODE => resource_rec.comp_currency_code,
P_COMMISSIONABLE_FLAG => resource_rec.commissionable_flag,
P_HOLD_REASON_CODE => resource_rec.hold_reason_code,
P_HOLD_PAYMENT => resource_rec.hold_payment,
P_COMP_SERVICE_TEAM_ID => resource_rec.comp_service_team_id,
P_USER_ID => resource_rec.user_id,
P_TRANSACTION_NUMBER => resource_rec.transaction_number,
-- P_LOCATION => resource_rec.location,
P_OBJECT_VERSION_NUMBER => p_object_version_num + 1,
P_RESOURCE_NAME => resource_rec.RESOURCE_NAME ,
P_SOURCE_NAME => resource_rec.SOURCE_NAME ,
P_SOURCE_NUMBER => resource_rec.SOURCE_NUMBER ,
P_SOURCE_JOB_TITLE => resource_rec.SOURCE_JOB_TITLE ,
P_SOURCE_EMAIL => resource_rec.SOURCE_EMAIL ,
P_SOURCE_PHONE => resource_rec.SOURCE_PHONE ,
P_SOURCE_ORG_ID => resource_rec.SOURCE_ORG_ID ,
P_SOURCE_ORG_NAME => resource_rec.SOURCE_ORG_NAME ,
P_SOURCE_ADDRESS1 => resource_rec.SOURCE_ADDRESS1 ,
P_SOURCE_ADDRESS2 => resource_rec.SOURCE_ADDRESS2 ,
P_SOURCE_ADDRESS3 => resource_rec.SOURCE_ADDRESS3 ,
P_SOURCE_ADDRESS4 => resource_rec.SOURCE_ADDRESS4 ,
P_SOURCE_CITY => resource_rec.SOURCE_CITY ,
P_SOURCE_POSTAL_CODE => resource_rec.SOURCE_POSTAL_CODE ,
P_SOURCE_STATE => resource_rec.SOURCE_STATE ,
P_SOURCE_PROVINCE => resource_rec.SOURCE_PROVINCE ,
P_SOURCE_COUNTY => resource_rec.SOURCE_COUNTY ,
P_SOURCE_COUNTRY => resource_rec.SOURCE_COUNTRY ,
P_SOURCE_MGR_ID => resource_rec.SOURCE_MGR_ID ,
P_SOURCE_MGR_NAME => resource_rec.SOURCE_MGR_NAME ,
P_SOURCE_BUSINESS_GRP_ID => resource_rec.SOURCE_BUSINESS_GRP_ID ,
P_SOURCE_BUSINESS_GRP_NAME => resource_rec.SOURCE_BUSINESS_GRP_NAME ,
P_SOURCE_FIRST_NAME => resource_rec.SOURCE_FIRST_NAME ,
P_SOURCE_MIDDLE_NAME => resource_rec.SOURCE_MIDDLE_NAME ,
P_SOURCE_LAST_NAME => resource_rec.SOURCE_LAST_NAME ,
P_SOURCE_CATEGORY => resource_rec.SOURCE_CATEGORY ,
P_SOURCE_STATUS => resource_rec.SOURCE_STATUS ,
P_SOURCE_OFFICE => resource_rec.SOURCE_OFFICE ,
P_SOURCE_LOCATION => resource_rec.SOURCE_LOCATION ,
P_SOURCE_MAILSTOP => resource_rec.SOURCE_MAILSTOP ,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data,
P_SOURCE_MOBILE_PHONE => resource_rec.SOURCE_MOBILE_PHONE ,
P_SOURCE_PAGER => resource_rec.SOURCE_PAGER,
P_USER_NAME => resource_rec.USER_NAME
);
/* Calling publish API to raise update resource event. */
/* added by baianand on 11/04/2002 */
begin
l_resource_rec.resource_id := l_resource_id;
jtf_rs_wf_events_pub.update_resource
(p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_resource_rec => l_resource_rec
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
/* Calling work API for insert/update record into wf_local tables. */
/* added by baianand on 08/15/2002 */
begin
jtf_rs_wf_integration_pub.update_resource
(p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_resource_id => l_resource_id
,p_resource_name => resource_rec.resource_name
,p_user_id => resource_rec.user_id
,p_email_address => resource_rec.source_email
,p_start_date_active => resource_rec.start_date_active
,p_end_date_active => resource_rec.end_date_active
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
/* Update the row into the table by calling the table handler. */
jtf_rs_resource_extns_pkg.update_row(
x_resource_id => l_resource_id,
x_category => resource_rec.category,
x_resource_number => resource_rec.resource_number,
x_source_id => resource_rec.source_id,
x_address_id => resource_rec.address_id,
x_contact_id => resource_rec.contact_id,
x_managing_employee_id => resource_rec.managing_emp_id,
x_start_date_active => resource_rec.start_date_active,
x_end_date_active => resource_rec.end_date_active,
x_time_zone => resource_rec.time_zone,
x_cost_per_hr => resource_rec.cost_per_hr,
x_primary_language => resource_rec.primary_language,
x_secondary_language => resource_rec.secondary_language,
x_support_site_id => resource_rec.support_site_id,
x_ies_agent_login => resource_rec.ies_agent_login,
x_server_group_id => resource_rec.server_group_id,
x_assigned_to_group_id => resource_rec.assigned_to_group_id,
x_cost_center => resource_rec.cost_center,
x_charge_to_cost_center => resource_rec.charge_to_cost_center,
x_compensation_currency_code => resource_rec.comp_currency_code,
x_commissionable_flag => resource_rec.commissionable_flag,
x_hold_reason_code => resource_rec.hold_reason_code,
x_hold_payment => resource_rec.hold_payment,
x_comp_service_team_id => resource_rec.comp_service_team_id,
x_user_id => resource_rec.user_id,
--x_location => resource_rec.location,
x_transaction_number => resource_rec.transaction_number,
x_object_version_number => l_object_version_num,
x_attribute1 => resource_rec.attribute1,
x_attribute2 => resource_rec.attribute2,
x_attribute3 => resource_rec.attribute3,
x_attribute4 => resource_rec.attribute4,
x_attribute5 => resource_rec.attribute5,
x_attribute6 => resource_rec.attribute6,
x_attribute7 => resource_rec.attribute7,
x_attribute8 => resource_rec.attribute8,
x_attribute9 => resource_rec.attribute9,
x_attribute10 => resource_rec.attribute10,
x_attribute11 => resource_rec.attribute11,
x_attribute12 => resource_rec.attribute12,
x_attribute13 => resource_rec.attribute13,
x_attribute14 => resource_rec.attribute14,
x_attribute15 => resource_rec.attribute15,
x_attribute_category => resource_rec.attribute_category,
x_last_update_date => SYSDATE,
x_last_updated_by => jtf_resource_utl.updated_by,
x_last_update_login => jtf_resource_utl.login_id,
x_resource_name => resource_rec.resource_name,
x_source_name => resource_rec.source_name,
x_source_number => resource_rec.source_number,
x_source_job_title => resource_rec.source_job_title,
x_source_email => resource_rec.source_email,
x_source_phone => resource_rec.source_phone,
x_source_org_id => resource_rec.source_org_id,
x_source_org_name => resource_rec.source_org_name,
x_source_address1 => resource_rec.source_address1,
x_source_address2 => resource_rec.source_address2,
x_source_address3 => resource_rec.source_address3,
x_source_address4 => resource_rec.source_address4,
x_source_city => resource_rec.source_city,
x_source_postal_code => resource_rec.source_postal_code,
x_source_state => resource_rec.source_state,
x_source_province => resource_rec.source_province,
x_source_county => resource_rec.source_county,
x_source_country => resource_rec.source_country,
x_source_mgr_id => resource_rec.source_mgr_id,
x_source_mgr_name => resource_rec.source_mgr_name,
x_source_business_grp_id => resource_rec.source_business_grp_id,
x_source_business_grp_name=> resource_rec.source_business_grp_name,
x_SOURCE_FIRST_NAME => resource_rec.SOURCE_FIRST_NAME ,
x_SOURCE_MIDDLE_NAME => resource_rec.SOURCE_MIDDLE_NAME ,
x_SOURCE_LAST_NAME => resource_rec.SOURCE_LAST_NAME ,
x_SOURCE_CATEGORY => resource_rec.SOURCE_CATEGORY ,
x_SOURCE_STATUS => resource_rec.SOURCE_STATUS ,
x_SOURCE_OFFICE => resource_rec.SOURCE_OFFICE ,
x_SOURCE_LOCATION => resource_rec.SOURCE_LOCATION ,
x_SOURCE_MAILSTOP => resource_rec.SOURCE_MAILSTOP ,
x_USER_NAME => resource_rec.USER_NAME ,
x_SOURCE_JOB_ID => resource_rec.SOURCE_JOB_ID,
x_PARTY_ID => resource_rec.PERSON_PARTY_ID,
x_SOURCE_MOBILE_PHONE => resource_rec.SOURCE_MOBILE_PHONE ,
x_SOURCE_PAGER => resource_rec.SOURCE_PAGER
);
IF c_resource_update%ISOPEN THEN
CLOSE c_resource_update;
IF c_resource_update%ISOPEN THEN
CLOSE c_resource_update;
'UPDATE_RESOURCE',
'A',
'C')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'A',
'C')
THEN
jtf_rs_resource_cuhk.update_resource_post(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center=> l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
--p_location => l_location,
x_return_status => x_return_status);
'UPDATE_RESOURCE',
'A',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'A',
'V')
THEN
jtf_rs_resource_vuhk.update_resource_post(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
--p_location => l_location,
x_return_status => x_return_status);
'UPDATE_RESOURCE',
'A',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'A',
'I')
THEN
jtf_rs_resource_iuhk.update_resource_post(
p_resource_id => l_resource_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_time_zone => l_time_zone,
p_cost_per_hr => l_cost_per_hr,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_currency_code => l_comp_currency_code,
p_commissionable_flag => l_commissionable_flag,
p_hold_reason_code => l_hold_reason_code,
p_hold_payment => l_hold_payment,
p_comp_service_team_id => l_comp_service_team_id,
p_user_id => l_user_id,
--p_location => l_location,
x_return_status => x_return_status);
'UPDATE_RESOURCE',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'UPDATE_RESOURCE',
'M',
'M')
THEN
IF (jtf_rs_resource_cuhk.ok_to_generate_msg(
p_resource_id => l_resource_id,
x_return_status => x_return_status) )
THEN
/* Get the bind data id for the Business Object Instance */
l_bind_data_id := jtf_usr_hks.get_bind_data_id;
ROLLBACK TO update_resource_pvt;
ROLLBACK TO update_resource_pvt;
ROLLBACK TO update_resource_pvt;
END update_resource;
/* Procedure to delete the resource of category = TBH */
PROCEDURE DELETE_RESOURCE(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_RESOURCE_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
CURSOR res_cur(L_RESOURCE_ID NUMBER)
IS
SELECT category
FROM jtf_rs_resource_extns
WHERE resource_id = l_resource_id;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE';
SAVEPOINT delete_resource_pvt;
'DELETE_RESOURCE',
'B',
'C')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'DELETE_RESOURCE',
'B',
'C')
THEN
jtf_rs_resource_cuhk.delete_resource_pre(
p_resource_id => l_resource_id,
x_return_status => x_return_status);
'DELETE_RESOURCE',
'B',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'DELETE_RESOURCE',
'B',
'V')
THEN
jtf_rs_resource_vuhk.delete_resource_pre(
p_resource_id => l_resource_id,
x_return_status => x_return_status);
'DELETE_RESOURCE',
'B',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'DELETE_RESOURCE',
'B',
'I')
THEN
jtf_rs_resource_iuhk.delete_resource_pre(
p_resource_id => l_resource_id,
x_return_status => x_return_status);
jtf_rs_resource_extns_aud_pvt.delete_resource
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => l_resource_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
jtf_rs_resource_extns_pkg.delete_row(
x_resource_id => l_resource_id );
'DELETE_RESOURCE',
'A',
'C')
THEN
jtf_rs_resource_cuhk.delete_resource_post(
p_resource_id => l_resource_id,
x_return_status => x_return_status);
'DELETE_RESOURCE',
'A',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'DELETE_RESOURCE',
'A',
'V')
THEN
jtf_rs_resource_vuhk.delete_resource_post(
p_resource_id => l_resource_id,
x_return_status => x_return_status);
'DELETE_RESOURCE',
'A',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'DELETE_RESOURCE',
'A',
'I')
THEN
jtf_rs_resource_iuhk.delete_resource_post(
p_resource_id => l_resource_id,
x_return_status => x_return_status);
'DELETE_RESOURCE',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_RESOURCE_PVT',
'DELETE_RESOURCE',
'M',
'M')
THEN
IF (jtf_rs_resource_cuhk.ok_to_generate_msg(
p_resource_id => l_resource_id,
x_return_status => x_return_status) )
THEN
/* Get the bind data id for the Business Object Instance */
l_bind_data_id := jtf_usr_hks.get_bind_data_id;
jtf_rs_wf_events_pub.delete_resource
(p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_resource_id => l_resource_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
/* Calling work API for delete record from wf_local tables. */
/* added by baianand on 08/13/2002 */
begin
jtf_rs_wf_integration_pub.delete_resource
(p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => fnd_api.g_false
,p_resource_id => l_resource_id
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data);
ROLLBACK TO delete_resource_pvt;
ROLLBACK TO delete_resource_pvt;
ROLLBACK TO delete_resource_pvt;
END delete_resource;