[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
validations and to do actual create and update into tables.
******************************************************************************************/
/* Package variables. */
G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_INTERFACE_PVT';
* 25th Sep 2009 Sudhir Gokavarapu Bug8945146 Before calling Public APIs to update resource
* verification of Input dates are done by calling
* JTF_RESOURCE_UTL.validate_input_dates API.
* 23rd Jan 2013 Sudhir Gokavarapu Bug16190251 In do_update_resource procedure corrected
* c_resource_int cursor.
*
*/
PROCEDURE debug (
p_message IN VARCHAR2,
p_prefix IN VARCHAR2 DEFAULT 'DEBUG',
p_msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
p_module_prefix IN VARCHAR2 DEFAULT 'JTF_RS_Package',
p_module IN VARCHAR2 DEFAULT 'JTF_RS_Module'
) IS
l_message VARCHAR2(4000);
SELECT
interface_id, start_date_active, end_date_active, compensation_currency_code,
commissionable_flag, hold_reason_code, hold_payment, resource_name,
source_id, address_id, contact_id, managing_employee_id,
time_zone, cost_per_hr, primary_language, secondary_language,
support_site_id, ies_agent_login, server_group_id, interaction_center_name,
assigned_to_group_id, cost_center, charge_to_cost_center, comp_service_team_id,
user_id, transaction_number, user_name, attribute_category,
attribute1, attribute2, attribute3, attribute4,
attribute5, attribute6, attribute7, attribute8,
attribute9, attribute10, attribute11, attribute12,
attribute13, attribute14, attribute15, category
FROM
jtf_rs_resource_extns_int
WHERE
batch_id = p_batch_id AND
category = 'OTHER' AND
operation = 'CREATE' AND
operation_status IS NULL
ORDER BY interface_id;
UPDATE jtf_rs_resource_extns_int
SET OPERATION_STATUS = l_status_error,
OPERATION_MESSAGE = l_trans_message,
OPERATION_PROCESS_DATE = SYSDATE
WHERE batch_id = p_batch_id AND
category = 'OTHER' AND
operation = 'CREATE' AND
operation_status IS NULL AND
start_date_active IS NULL ;
UPDATE jtf_rs_resource_extns_int
SET OPERATION_STATUS = l_status_error,
OPERATION_MESSAGE = l_trans_message,
OPERATION_PROCESS_DATE = SYSDATE
WHERE batch_id = p_batch_id AND
CATEGORY = 'OTHER' AND
operation = 'CREATE' AND
operation_status IS NULL AND
resource_name IS NULL ;
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_status_success,
operation_process_date = SYSDATE,
resource_id = x_resource_id
WHERE interface_id = l_interface_id;
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_return_status,
operation_message = l_msg_data1,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_return_status,
operation_message = l_msg_data1,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
* PROCEDURE do_update_resource
*
* DESCRIPTION
* Create Resource.
*
* Private PROCEDURES/FUNCTIONS
*
* ARGUMENTS
* IN:
* p_batch_id Batch Id to process records.
*
* NOTES
*
* MODIFICATION HISTORY
* 10-June-2009 Sudhir Gokavarapu Created.
*
*/
PROCEDURE do_update_resource
(P_BATCH_ID IN NUMBER,
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;
SELECT
interface_id,
DECODE(start_date_active,NULL,l_miss_date,l_null_date,NULL,start_date_active) start_date_active,
DECODE(end_date_active ,NULL,l_miss_date,l_null_date,NULL,end_date_active) end_date_active,
DECODE(compensation_currency_code,l_null_char,NULL,NULL,l_miss_char,compensation_currency_code) compensation_currency_code,
DECODE(commissionable_flag,l_null_char,NULL,NULL,l_miss_char,commissionable_flag) commissionable_flag,
DECODE(hold_reason_code,l_null_char,NULL,NULL,l_miss_char,hold_reason_code) hold_reason_code,
DECODE(hold_payment,l_null_char,NULL,NULL,l_miss_char,hold_payment) hold_payment,
DECODE(resource_name,l_null_char,NULL,NULL,l_miss_char,resource_name) resource_name,
DECODE(address_id,L_NULL_NUM,NULL,NULL,l_miss_num,address_id) address_id,
DECODE(contact_id,L_NULL_NUM,NULL,NULL,l_miss_num,contact_id) contact_id,
DECODE(managing_employee_id,L_NULL_NUM,NULL,NULL,l_miss_num,managing_employee_id) managing_employee_id,
DECODE(time_zone,L_NULL_NUM,NULL,NULL,l_miss_num,time_zone) time_zone,
DECODE(cost_per_hr,L_NULL_NUM,NULL,NULL,l_miss_num,cost_per_hr) cost_per_hr,
DECODE(primary_language,l_null_char,NULL,NULL,l_miss_char,primary_language) primary_language,
DECODE(secondary_language,l_null_char,NULL,NULL,l_miss_char,secondary_language) secondary_language,
DECODE(support_site_id,L_NULL_NUM,NULL,NULL,l_miss_num,support_site_id) support_site_id,
DECODE(ies_agent_login,l_null_char,NULL,NULL,l_miss_char,ies_agent_login) ies_agent_login,
DECODE(server_group_id,L_NULL_NUM,NULL,NULL,l_miss_num,server_group_id) server_group_id,
DECODE(assigned_to_group_id,L_NULL_NUM,NULL,NULL,l_miss_num,assigned_to_group_id) assigned_to_group_id,
DECODE(cost_center,l_null_char,NULL,NULL,l_miss_char,cost_center) cost_center,
DECODE(charge_to_cost_center,l_null_char,NULL,NULL,l_miss_char,charge_to_cost_center) charge_to_cost_center,
DECODE(comp_service_team_id,L_NULL_NUM,NULL,NULL,l_miss_num,comp_service_team_id) comp_service_team_id,
DECODE(user_id,L_NULL_NUM,NULL,NULL,l_miss_num,user_id) user_id,
DECODE(user_name,l_null_char,NULL,NULL,l_miss_char,user_name) user_name,
DECODE(attribute_category,l_null_char,NULL,NULL,l_miss_char,attribute_category) attribute_category,
DECODE(attribute1,l_null_char,NULL,NULL,l_miss_char,attribute1) attribute1,
DECODE(attribute2,l_null_char,NULL,NULL,l_miss_char,attribute2) attribute2,
DECODE(attribute3,l_null_char,NULL,NULL,l_miss_char,attribute3) attribute3,
DECODE(attribute4,l_null_char,NULL,NULL,l_miss_char,attribute4) attribute4,
DECODE(attribute5,l_null_char,NULL,NULL,l_miss_char,attribute5) attribute5,
DECODE(attribute6,l_null_char,NULL,NULL,l_miss_char,attribute6) attribute6,
DECODE(attribute7,l_null_char,NULL,NULL,l_miss_char,attribute7) attribute7,
DECODE(attribute8,l_null_char,NULL,NULL,l_miss_char,attribute8) attribute8,
DECODE(attribute9,l_null_char,NULL,NULL,l_miss_char,attribute9) attribute9,
DECODE(attribute10,l_null_char,NULL,NULL,l_miss_char,attribute10) attribute10,
DECODE(attribute11,l_null_char,NULL,NULL,l_miss_char,attribute11) attribute11,
DECODE(attribute12,l_null_char,NULL,NULL,l_miss_char,attribute12) attribute12,
DECODE(attribute13,l_null_char,NULL,NULL,l_miss_char,attribute13) attribute13,
DECODE(attribute14,l_null_char,NULL,NULL,l_miss_char,attribute14) attribute14,
DECODE(attribute15,l_null_char,NULL,NULL,l_miss_char,attribute15) attribute15,
resource_id,
DECODE(start_date_active,to_char(l_null_date,'dd/mm/yyyy hh24:mi:ss'),NULL,NULL,to_char(l_miss_date,'dd/mm/yyyy hh24:mi:ss'),to_char(start_date_active,'dd/mm/yyyy hh24:mi:ss')) start_date_active_char
--Character converted date To compare with l_miss_date.
FROM
jtf_rs_resource_extns_int
WHERE
batch_id = p_batch_id AND
category = 'OTHER' AND
operation = 'UPDATE' AND
operation_status IS NULL
ORDER BY interface_id;
SAVEPOINT do_update_resource;
debug(p_message=>' do_update_resource (+)',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
l_api_name := 'DO_UPDATE_RESOURCE';
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_status_error,
operation_message = l_trans_message,
operation_process_date = SYSDATE
WHERE batch_id = p_batch_id AND
operation = 'UPDATE' AND
operation_status IS NULL AND
resource_id IS NULL ;
DEBUG(p_message=>'Resource Id cannot be null in UPDATE mode:'||
to_char(l_commit_count),
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE jtf_rs_resource_extns_int rs_int
SET operation_status = l_status_error,
operation_message = l_trans_message,
operation_process_date = SYSDATE
WHERE batch_id = p_batch_id AND
operation = 'UPDATE' AND
operation_status IS NULL AND
resource_id IS NOT NULL
AND NOT EXISTS
(SELECT 1 FROM jtf_rs_resource_extns rs
WHERE rs.resource_id = rs_int.resource_id);
UPDATE jtf_rs_resource_extns_int rs_int
SET operation_status = l_status_error,
operation_message = l_trans_message,
operation_process_date = SYSDATE
WHERE batch_id = p_batch_id AND
operation = 'UPDATE' AND
operation_status IS NULL AND
resource_id IS NOT NULL
AND NOT EXISTS
(SELECT 1 FROM jtf_rs_resource_extns rs
WHERE rs.resource_id = rs_int.resource_id
AND rs.category = 'OTHER' );
debug(p_message=>'Validating - Resource Name cannot be updated to null ',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_status_error,
operation_message = l_trans_message,
operation_process_date = SYSDATE
WHERE batch_id = p_batch_id AND
operation = 'UPDATE' AND
operation_status IS NULL AND
resource_name = l_null_char;
DEBUG(p_message=>'Resource Name should not be updated to NULL:'||
to_char(l_commit_count),
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
debug(p_message=>'Validating - Active Start Date cannot be updated to null ',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_status_error,
operation_message = l_trans_message,
operation_process_date = SYSDATE
WHERE batch_id = p_batch_id AND
operation = 'UPDATE' AND
operation_status IS NULL AND
start_date_active = l_null_date;
DEBUG(p_message=>'Active Start Date should not be updated to NULL:'||
to_char(l_commit_count),
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
SAVEPOINT do_update_resource_loop;
debug(p_message=>'Update Resource mode Interface Id :'||
l_interface_id ||' Resource Id :'||l_resource_id ,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
SELECT object_version_number,end_date_active,start_date_active
INTO l_object_version_number,l_end_date_active_db,l_start_date_active_db
FROM jtf_rs_resource_extns
WHERE resource_id = l_resource_id;
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_return_status,
operation_message = l_trans_message,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
debug(p_message=>'Error in Update Resource loop at Resource Id,Batch Id, Interface Id : '||l_resource_id||' '||l_batch_id||' '||l_interface_id,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
debug(p_message=>'No value other than end date to be updated. ',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_status_success,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
ELSE-- When return status is NOT success then update Error details to Interface Table.
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_return_status,
operation_message = l_msg_data1,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
ELSE -- having other than end date to be updated
-- Other_value flag value is True.
-- Call update_resource with end_date_active value and then
-- call end_date_employee before moving to next record.
-- Debug info.
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
debug(p_message=>'Having other than end date to be updated. ',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
jtf_rs_resource_pub.update_resource(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_address_id => l_address_id,
p_managing_emp_id => l_managing_emp_id,
p_start_date_active => l_start_date_active,
p_user_id => l_user_id,
p_time_zone => l_time_zone,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_resource_id => l_resource_id,
p_resource_number => x_resource_number,
p_source_name => l_source_name,
p_resource_name => l_RESOURCE_NAME,
p_user_name => l_user_name,
p_object_version_num => l_object_version_number,
p_attribute_category => l_attribute_category,
p_attribute1 => l_attribute1,
p_attribute2 => l_attribute2,
p_attribute3 => l_attribute3,
p_attribute4 => l_attribute4,
p_attribute5 => l_attribute5,
p_attribute6 => l_attribute6,
p_attribute7 => l_attribute7,
p_attribute8 => l_attribute8,
p_attribute9 => l_attribute9,
p_attribute10 => l_attribute10,
p_attribute11 => l_attribute11,
p_attribute12 => l_attribute12,
p_attribute13 => l_attribute13,
p_attribute14 => l_attribute14,
p_attribute15 => l_attribute15,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_service_team_id => l_comp_service_team_id,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_cost_per_hr => l_cost_per_hr,
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
);
debug(p_message=>'After Update Resource (without end date) call Return Status : '||l_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_status_success,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
ELSE-- When return status is NOT success then update Error details to Interface Table.
-- Debug info.
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
debug(p_message=>'Error in Update Resource loop at Resource Id,Batch Id, Interface Id : '||l_resource_id||' '||l_batch_id||' '||l_interface_id,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_return_status,
operation_message = l_msg_data1,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
debug(p_message=>'Before calling end_date_employee and after Update Resource (without end date) call.',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_status_success,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
ELSE-- When return status is NOT success then update Error details to Interface Table.
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_return_status,
operation_message = l_msg_data1,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
JTF_RS_RESOURCE_PUB.UPDATE_RESOURCE(
p_api_version => 1.0,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_address_id => l_address_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_user_id => l_user_id,
p_time_zone => l_time_zone,
p_primary_language => l_primary_language,
p_secondary_language => l_secondary_language,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_resource_id => l_resource_id,
p_resource_number => x_resource_number,
p_source_name => l_source_name,
p_resource_name => l_RESOURCE_NAME,
p_user_name => l_user_name,
p_object_version_num => l_object_version_number,
p_attribute_category => l_attribute_category,
p_attribute1 => l_attribute1,
p_attribute2 => l_attribute2,
p_attribute3 => l_attribute3,
p_attribute4 => l_attribute4,
p_attribute5 => l_attribute5,
p_attribute6 => l_attribute6,
p_attribute7 => l_attribute7,
p_attribute8 => l_attribute8,
p_attribute9 => l_attribute9,
p_attribute10 => l_attribute10,
p_attribute11 => l_attribute11,
p_attribute12 => l_attribute12,
p_attribute13 => l_attribute13,
p_attribute14 => l_attribute14,
p_attribute15 => l_attribute15,
p_cost_center => l_cost_center,
p_charge_to_cost_center => l_charge_to_cost_center,
p_comp_service_team_id => l_comp_service_team_id,
p_server_group_id => l_server_group_id,
p_assigned_to_group_id => l_assigned_to_group_id,
p_support_site_id => l_support_site_id,
p_ies_agent_login => l_ies_agent_login,
p_cost_per_hr => l_cost_per_hr,
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
);
debug(p_message=>'After Update Resource call Return Status : '||l_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_status_success,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
ELSE-- When return status is NOT success then update Error details to Interface Table.
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_return_status,
operation_message = l_msg_data1,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
debug(p_message=>'Unexpected Error in Update Resource loop at'
||' Batch Id :'||l_batch_id
||' Interface Id :'||l_interface_id,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE jtf_rs_resource_extns_int
SET operation_status = l_return_status,
operation_message = l_msg_data1,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
debug(p_message=>' do_update_resource (-)',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
ROLLBACK TO do_update_resource;
ROLLBACK TO do_update_resource;
debug(p_message=>'Unexpected Error at do_update_resource procedure '||SQLERRM,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
ROLLBACK TO do_update_resource;
SELECT
interface_id, salesrep_id,
resource_id, sales_credit_type_id, status,
start_date_active, end_date_active, salesrep_number,
org_id, email_address, gl_id_rev,
gl_id_freight, gl_id_rec, set_of_books_id,
sales_tax_geocode, sales_tax_inside_city_limits
FROM
jtf_rs_salesreps_int
WHERE
batch_id = p_batch_id AND
operation = 'CREATE' AND
operation_status IS NULL
ORDER BY interface_id;
UPDATE jtf_rs_salesreps_int
SET OPERATION_STATUS = l_status_error,
OPERATION_MESSAGE = l_trans_message,
OPERATION_PROCESS_DATE = SYSDATE
WHERE batch_id = p_batch_id AND
operation = 'CREATE' AND
operation_status IS NULL AND
resource_id IS NULL ;
UPDATE jtf_rs_salesreps_int
SET OPERATION_STATUS = l_status_error,
OPERATION_MESSAGE = l_trans_message,
OPERATION_PROCESS_DATE = SYSDATE
WHERE batch_id = p_batch_id AND
operation = 'CREATE' AND
operation_status IS NULL AND
salesrep_number IS NULL ;
UPDATE jtf_rs_salesreps_int
SET operation_status = l_status_success,
operation_process_date = SYSDATE,
salesrep_id = x_salesrep_id
WHERE interface_id = l_interface_id;
UPDATE jtf_rs_salesreps_int
SET operation_status = l_return_status,
operation_message = l_msg_data1,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
UPDATE jtf_rs_salesreps_int
SET operation_status = l_return_status,
operation_message = l_msg_data1,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
* PROCEDURE do_update_salesrep
*
* DESCRIPTION
* Create Salesrep.
*
* Private PROCEDURES/FUNCTIONS
*
* ARGUMENTS
* IN:
* p_batch_id Batch Id to process records.
*
* NOTES
*
* MODIFICATION HISTORY
* 10-June-2009 Sudhir Gokavarapu Created.
*
*/
PROCEDURE do_update_salesrep
(p_batch_id IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
--Cursor to get Salesrep records for Update mode.
CURSOR c_salesrep_int (p_batch_id IN NUMBER)
IS
SELECT
interface_id,salesrep_id,resource_id,
DECODE(sales_credit_type_id,l_null_num,NULL,NULL,l_miss_num,sales_credit_type_id) sales_credit_type_id,
DECODE(status,l_null_char,NULL,NULL,l_miss_char,status) status,
DECODE(start_date_active,NULL,l_miss_date,l_null_date,NULL,start_date_active) start_date_active,
DECODE(end_date_active ,NULL,l_miss_date,l_null_date,NULL,end_date_active) end_date_active,
DECODE(salesrep_number,l_null_char,NULL,NULL,l_miss_char,salesrep_number) salesrep_number,
DECODE(org_id,l_null_num,NULL,NULL,l_miss_num,org_id) org_id,
DECODE(email_address,l_null_char,NULL,NULL,l_miss_char,email_address) email_address,
DECODE(gl_id_rev,l_null_num,NULL,NULL,l_miss_num,gl_id_rev) gl_id_rev,
DECODE(gl_id_freight,l_null_num,NULL,NULL,l_miss_num,gl_id_freight) gl_id_freight,
DECODE(gl_id_rec,l_null_num,NULL,NULL,l_miss_num,gl_id_rec) gl_id_rec,
DECODE(set_of_books_id,l_null_num,NULL,NULL,l_miss_num,set_of_books_id) set_of_books_id,
DECODE( sales_tax_geocode,l_null_char,NULL,NULL,l_miss_char, sales_tax_geocode) sales_tax_geocode,
DECODE(sales_tax_inside_city_limits,l_null_char,NULL,NULL,l_miss_char,sales_tax_inside_city_limits) sales_tax_inside_city_limits
FROM
jtf_rs_salesreps_int
WHERE
batch_id = p_batch_id AND
operation = 'UPDATE' AND
operation_status IS NULL
ORDER BY interface_id;
SAVEPOINT do_update_salesrep;
debug(p_message=>'do_update_salesrep (+)',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
l_api_name := 'DO_UPDATE_SALESREP';
UPDATE jtf_rs_salesreps_int
SET OPERATION_STATUS = l_status_error,
OPERATION_MESSAGE = l_trans_message,
OPERATION_PROCESS_DATE = SYSDATE
WHERE batch_id = p_batch_id AND
operation = 'UPDATE' AND
operation_status IS NULL AND
salesrep_id IS NULL ;
DEBUG(p_message=>'Records having Salesrep Id null in Update mode:'||
to_char(l_commit_count),
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE jtf_rs_salesreps_int a
SET OPERATION_STATUS = l_status_error,
OPERATION_MESSAGE = l_trans_message,
OPERATION_PROCESS_DATE = SYSDATE
WHERE batch_id = p_batch_id AND
operation = 'UPDATE' AND
operation_status IS NULL AND
NOT EXISTS (SELECT 1 FROM jtf_rs_salesreps B
WHERE A.salesrep_id = B.salesrep_id);
DEBUG(p_message=>'Records having Invalid Salesrep Id in Update mode:'||
to_char(l_commit_count),
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
SAVEPOINT do_update_salesrep_loop;
SELECT object_version_number,org_id
INTO l_object_version_number,l_org_id
FROM JTF_RS_SALESREPS
WHERE salesrep_id = l_salesrep_id;
debug(p_message=>'Before Update Salesrep call for Interface ID : '
||l_interface_id||' SalesRep Id : '||l_salesrep_id,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
JTF_RS_SALESREPS_PUB.update_salesrep
(p_api_version => 1.0,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_sales_credit_type_id => l_sales_credit_type_id,
p_status => l_status,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_salesrep_number => l_salesrep_number,
p_org_id => l_org_id,
p_email_address => l_email_address,
p_gl_id_rev => l_gl_id_rev,
p_gl_id_freight => l_gl_id_freight,
p_gl_id_rec => l_gl_id_rec,
p_set_of_books_id => l_set_of_books_id,
p_sales_tax_geocode => l_sales_tax_geocode,
p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_salesrep_id => l_salesrep_id,
p_object_version_number => l_object_version_number
);
debug(p_message=>'After Update Salesrep call Return Status : '||l_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
UPDATE jtf_rs_salesreps_int
SET operation_status = l_status_success,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
UPDATE jtf_rs_salesreps_int
SET operation_status = l_return_status,
operation_message = l_msg_data1,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
debug(p_message=>'Unexpected Error in Update Salesrep loop at'
||' Batch Id :'||l_batch_id
||' Interface Id :'||l_interface_id,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
ROLLBACK TO do_update_salesrep_loop;
UPDATE jtf_rs_salesreps_int
SET operation_status = l_return_status,
operation_message = l_msg_data1,
operation_process_date = SYSDATE
WHERE interface_id = l_interface_id;
debug(p_message=>'Unexpected Error at do_update_salesrep procedure:'
||SQLERRM,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
END do_update_salesrep;
/* Call Update Resource */
do_update_resource(p_batch_id => p_batch_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
debug(p_message=> 'Update Resource API Return Values:'||
' p_batch_id :'||p_batch_id||
' x_return_status :'||x_return_status||
' x_msg_count :'||x_msg_count||
' x_msg_data :'||x_msg_data,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
debug(p_message=> 'Create Resource API Failed. Update Resource not executed.',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
/* Call Update Salesrep */
do_update_salesrep(p_batch_id => p_batch_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
debug(p_message=> 'Update Salesrep API Return Values:'||
' p_batch_id :'||p_batch_id||
' x_return_status :'||x_return_status||
' x_msg_count :'||x_msg_count||
' x_msg_data :'||x_msg_data,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
debug(p_message=> 'Create Salesrep API Failed. Update Salesrep not executed.',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);