[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_INTERFACE_PVT
Source
1 PACKAGE BODY jtf_rs_interface_pvt AS
2 /* $Header: jtfrsvxb.pls 120.6.12020000.2 2013/01/23 05:54:57 rgokavar ship $ */
3 /*****************************************************************************************
4 This package body defines the procedures for importing Resources and Sales Rep.
5 Its main procedures are as following:
6 Import Resource
7 Import Salesreps
8 This package validates the input parameters to these procedures and then
9 Calls corresponding private procedures from jtf_rs_resource_pub to do business
10 validations and to do actual create and update into tables.
11 ******************************************************************************************/
12
13
14
15 /* Package variables. */
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_INTERFACE_PVT';
18
19
20 l_miss_char VARCHAR2(1) := FND_API.G_MISS_CHAR;
21 l_miss_num NUMBER := FND_API.G_MISS_NUM;
22 l_miss_date DATE := FND_API.G_MISS_DATE;
23
24 l_null_char VARCHAR2(1) := FND_API.G_NULL_CHAR;
25 l_null_num NUMBER := FND_API.G_NULL_NUM;
26 l_null_date DATE := FND_API.G_NULL_DATE;
27 l_trans_message VARCHAR2(250);
28 /**
29 * PROCEDURE debug
30 *
31 * DESCRIPTION
32 * Put debug message.
33 *
34 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
35 *
36 * ARGUMENTS
37 * IN:
38 * p_message Message you want to put in log.
39 * p_prefix Prefix of the message. Default value is
40 * DEBUG.
41 * p_msg_level Message Level.Default value is 1 and the value should be between
42 * 1 and 6 corresponding to FND_LOG's
43 * LEVEL_UNEXPECTED CONSTANT NUMBER := 6;
44 * LEVEL_ERROR CONSTANT NUMBER := 5;
45 * LEVEL_EXCEPTION CONSTANT NUMBER := 4;
46 * LEVEL_EVENT CONSTANT NUMBER := 3;
47 * LEVEL_PROCEDURE CONSTANT NUMBER := 2;
48 * LEVEL_STATEMENT CONSTANT NUMBER := 1;
49 * p_module_prefix Module prefix to store package name,form name.Default value is
50 * HZ_Package.
51 * p_module Module to store Procedure Name. Default value is HZ_Module.
52 * NOTES
53 *
54 * MODIFICATION HISTORY
55 *
56 * 12th Aug 2009 Sudhir Gokavarapu Bug8786536-During Build compilation error
57 * Commented CONTINUE statement, which is new feature in 11g
58 * and build was in 10g. IF loop added accordingly.
59 * 25th Sep 2009 Sudhir Gokavarapu Bug8945146 Before calling Public APIs to update resource
60 * verification of Input dates are done by calling
61 * JTF_RESOURCE_UTL.validate_input_dates API.
62 * 23rd Jan 2013 Sudhir Gokavarapu Bug16190251 In do_update_resource procedure corrected
63 * c_resource_int cursor.
64 *
65 */
66
67 PROCEDURE debug (
68 p_message IN VARCHAR2,
69 p_prefix IN VARCHAR2 DEFAULT 'DEBUG',
70 p_msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
71 p_module_prefix IN VARCHAR2 DEFAULT 'JTF_RS_Package',
72 p_module IN VARCHAR2 DEFAULT 'JTF_RS_Module'
73 ) IS
74
75 l_message VARCHAR2(4000);
76 l_module VARCHAR2(255);
77
78 BEGIN
79
80 l_module :=SUBSTRB('jtf.rs.plsql.'||p_module_prefix||'.'||p_module,1,255);
81
82 IF p_prefix IS NOT NULL THEN
83 l_message :=SUBSTRB(p_prefix||'-'||p_message,1,4000);
84 ELSE
85 l_message :=SUBSTRB(p_message,1,4000);
86 END IF;
87
88 if( p_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
89 FND_LOG.STRING(p_msg_level,l_module,l_message);
90 end if;
91
92 END debug;
93
94 /**
95 * PROCEDURE do_create_resource
96 *
97 * DESCRIPTION
98 * Create Resource.
99 *
100 * Private PROCEDURES/FUNCTIONS
101 *
102 * ARGUMENTS
103 * IN:
104 * p_batch_id Batch Id to process records.
105 *
106 * NOTES
107 *
108 * MODIFICATION HISTORY
109 * 10-June-2009 Sudhir Gokavarapu Created.
110 *
111 */
112
113
114 PROCEDURE do_create_resource
115 (p_batch_id IN NUMBER,
116 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
117 X_MSG_COUNT OUT NOCOPY NUMBER,
118 X_MSG_DATA OUT NOCOPY VARCHAR2
119 ) IS
120
121
122 --Cursor to get Resource records for Create operation.
123 CURSOR c_resource_int (p_batch_id IN NUMBER)
124 IS
125 SELECT
126 interface_id, start_date_active, end_date_active, compensation_currency_code,
127 commissionable_flag, hold_reason_code, hold_payment, resource_name,
128 source_id, address_id, contact_id, managing_employee_id,
129 time_zone, cost_per_hr, primary_language, secondary_language,
130 support_site_id, ies_agent_login, server_group_id, interaction_center_name,
131 assigned_to_group_id, cost_center, charge_to_cost_center, comp_service_team_id,
132 user_id, transaction_number, user_name, attribute_category,
133 attribute1, attribute2, attribute3, attribute4,
134 attribute5, attribute6, attribute7, attribute8,
135 attribute9, attribute10, attribute11, attribute12,
136 attribute13, attribute14, attribute15, category
137 FROM
138 jtf_rs_resource_extns_int
139 WHERE
140 batch_id = p_batch_id AND
141 category = 'OTHER' AND
142 operation = 'CREATE' AND
143 operation_status IS NULL
144 ORDER BY interface_id;
145
146 l_commit_count NUMBER ;
147
148 /*=================Variables for Resource Import========================*/
149 l_api_name VARCHAR2(30);
150 l_init_msg_list VARCHAR2(1);
151 l_commit VARCHAR2(1);
152 l_interface_id JTF_RS_RESOURCE_EXTNS_INT.INTERFACE_ID%TYPE;
153 l_source_id JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE;
154 l_address_id JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE;
155 l_contact_id JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE;
156 l_managing_emp_id JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE;
157 l_start_date_active JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE;
158 l_end_date_active JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE;
159 l_time_zone JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE;
160 l_cost_per_hr JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE;
161 l_primary_language JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE;
162 l_secondary_language JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE;
163 l_support_site_id JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE;
164 l_ies_agent_login JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE;
165 l_server_group_id JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE;
166 l_interaction_center_name VARCHAR2(256);
167 l_assigned_to_group_id JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE;
168 l_cost_center JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE;
169 l_charge_to_cost_center JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE;
170 l_comp_currency_code JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE;
171 l_commissionable_flag JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE;
172 l_hold_reason_code JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE;
173 l_hold_payment JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE;
174 l_comp_service_team_id JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE;
175 l_user_id JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE;
176 l_transaction_number JTF_RS_RESOURCE_EXTNS.TRANSACTION_NUMBER%TYPE;
177 x_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
178 x_resource_number JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE;
179 l_resource_name JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE;
180 l_user_name JTF_RS_RESOURCE_EXTNS.USER_NAME%TYPE;
181 l_attribute1 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE;
182 l_attribute2 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE;
183 l_attribute3 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE;
184 l_attribute4 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE;
185 l_attribute5 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE;
186 l_attribute6 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE;
187 l_attribute7 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE;
188 l_attribute8 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE;
189 l_attribute9 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE;
190 l_attribute10 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE;
191 l_attribute11 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE;
192 l_attribute12 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE;
193 l_attribute13 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE;
194 l_attribute14 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE;
195 l_attribute15 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE;
196 l_attribute_category JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE;
197 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
198 l_resource_number JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE;
199 l_source_name JTF_RS_RESOURCE_EXTNS.source_name%TYPE;
200 l_category JTF_RS_RESOURCE_EXTNS.category%TYPE;
201 l_debug_prefix VARCHAR2(30) := '';
202 l_batch_id NUMBER;
203
204 l_return_status VARCHAR2(1);
205 l_msg_count NUMBER;
206 l_msg_data VARCHAR2(4000);
207 l_msg_data1 VARCHAR2(4000);
208 l_api_version CONSTANT NUMBER := 1.0;
209 l_status_error CONSTANT VARCHAR2(10) := fnd_api.g_ret_sts_error;
210 l_status_success CONSTANT VARCHAR2(10) := fnd_api.g_ret_sts_success;
211 BEGIN
212
213 SAVEPOINT do_create_resource;
214
215 l_api_name := 'DO_CREATE_RESOURCE';
216
217 -- Debug info.
218 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
219 debug(p_message=>'do_create_resource (+)',
220 p_prefix =>l_debug_prefix,
221 p_msg_level=>fnd_log.level_statement);
222 END IF;
223
224 -- initialize variables
225 x_return_status := fnd_api.g_ret_sts_success;
226 l_init_msg_list := fnd_api.g_true;
227 l_commit := fnd_api.g_false;
228 l_commit_count := 0;
229 l_batch_id := p_batch_id;
230
231 -- Debug info.
232 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
233 debug(p_message=>'Batch Id : '||l_batch_id,
234 p_prefix =>l_debug_prefix,
235 p_msg_level=>fnd_log.level_statement);
236 END IF;
237
238 -- Debug info.
239 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
240 debug(p_message=>'Validating Start date active ',
241 p_prefix =>l_debug_prefix,
242 p_msg_level=>fnd_log.level_statement);
243 END IF;
244
245 --validate Start Date Active value.
246 -- Get translated value for 'Start Date Active cannot be null'
247 l_trans_message := fnd_message.get_string('JTF','JTF_RS_START_DATE_NULL');
248
249 UPDATE jtf_rs_resource_extns_int
250 SET OPERATION_STATUS = l_status_error,
251 OPERATION_MESSAGE = l_trans_message,
252 OPERATION_PROCESS_DATE = SYSDATE
253 WHERE batch_id = p_batch_id AND
254 category = 'OTHER' AND
255 operation = 'CREATE' AND
256 operation_status IS NULL AND
257 start_date_active IS NULL ;
258
259 -- Get the number of rows updated
260 l_commit_count := SQL%ROWCOUNT ;
261
262 -- Debug info.
263 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
264 DEBUG(p_message=>'Records having Start date null in CREATE mode:'||
265 to_char(l_commit_count),
266 p_prefix =>l_debug_prefix,
267 p_msg_level=>fnd_log.level_statement);
268 END IF;
269
270 IF l_commit_count >= 1000 THEN -- Commit if more than 1000 records.
271 COMMIT;
272 l_commit_count := 0 ; -- reset the counter
273 END IF;
274
275 -- Debug info.
276 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
277 debug(p_message=>'Validating Resource Name ',
278 p_prefix =>l_debug_prefix,
279 p_msg_level=>fnd_log.level_statement);
280 END IF;
281
282 --validate Resource Name value.
283 --Get traslated Resource Name to set prompts.
284 l_trans_message := fnd_message.get_string('JTF','JTF_RS_ISET_RESOURCE_NAME');
285
286 -- Get translated value for 'Resource Name cannot be null'
287 fnd_message.set_name('JTF', 'JTF_RS_NOT_NULL');
288 fnd_message.set_token('PROMPTS', l_trans_message);
289 fnd_msg_pub.add;
290
291 l_trans_message := FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE);
292
293 UPDATE jtf_rs_resource_extns_int
294 SET OPERATION_STATUS = l_status_error,
295 OPERATION_MESSAGE = l_trans_message,
296 OPERATION_PROCESS_DATE = SYSDATE
297 WHERE batch_id = p_batch_id AND
298 CATEGORY = 'OTHER' AND
299 operation = 'CREATE' AND
300 operation_status IS NULL AND
301 resource_name IS NULL ;
302
303 -- Get the number of rows updated
304 l_commit_count := l_commit_count + SQL%ROWCOUNT ;
305
306 -- Debug info.
307 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
308 DEBUG(p_message=>'Records having Resource Name NULL in CREATE mode:'||
309 to_char(SQL%ROWCOUNT),
310 p_prefix =>l_debug_prefix,
311 p_msg_level=>fnd_log.level_statement);
312 END IF;
313
314 IF l_commit_count >= 1000 THEN -- Commit if more than 1000 records.
315 COMMIT;
316 l_commit_count := 0 ; -- reset the counter
317 END IF;
318
319 -- Open cursor for remaining records after Not Null value validation.
320 OPEN c_resource_int(p_batch_id);
321 LOOP
322 FETCH c_resource_int INTO
323 l_interface_id, l_start_date_active, l_end_date_active, l_comp_currency_code,
324 l_commissionable_flag, l_hold_reason_code, l_hold_payment, l_resource_name,
325 l_source_id, l_address_id, l_contact_id, l_managing_emp_id,
326 l_time_zone, l_cost_per_hr, l_primary_language, l_secondary_language,
327 l_support_site_id, l_ies_agent_login, l_server_group_id, l_interaction_center_name,
328 l_assigned_to_group_id, l_cost_center, l_charge_to_cost_center, l_comp_service_team_id,
329 l_user_id, l_transaction_number, l_user_name, l_attribute_category,
330 l_attribute1, l_attribute2, l_attribute3, l_attribute4,
331 l_attribute5, l_attribute6, l_attribute7, l_attribute8,
332 l_attribute9, l_attribute10, l_attribute11, l_attribute12,
333 l_attribute13, l_attribute14, l_attribute15, l_category;
334
335 EXIT WHEN c_resource_int%NOTFOUND ;
336
337 BEGIN
338 SAVEPOINT do_create_resource_loop;
339 -- Debug info.
340 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
341 debug(p_message=>'Before Create Resource call for Interface ID :'
342 ||l_interface_id,
343 p_prefix =>l_debug_prefix,
344 p_msg_level=>fnd_log.level_statement);
345 END IF;
346
347 --Call the Public procedure to Create Resource.
348 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
349 p_api_version => l_api_version,
350 p_init_msg_list => l_init_msg_list,
351 p_commit => l_commit,
352 p_category => l_category,
353 p_source_id => l_source_id,
354 p_address_id => l_address_id,
355 p_contact_id => l_contact_id,
356 p_managing_emp_id => l_managing_emp_id,
357 p_start_date_active => l_start_date_active,
358 p_end_date_active => l_end_date_active,
359 p_transaction_number => l_transaction_number,
360 p_user_id => l_user_id,
361 p_time_zone => l_time_zone,
362 p_primary_language => l_primary_language,
363 p_secondary_language => l_secondary_language,
364 p_source_name => l_source_name,
365 p_resource_name => l_resource_name,
366 p_user_name => l_user_name,
367 p_attribute_category => l_attribute_category,
368 p_attribute1 => l_attribute1,
369 p_attribute2 => l_attribute2,
370 p_attribute3 => l_attribute3,
371 p_attribute4 => l_attribute4,
372 p_attribute5 => l_attribute5,
373 p_attribute6 => l_attribute6,
374 p_attribute7 => l_attribute7,
375 p_attribute8 => l_attribute8,
376 p_attribute9 => l_attribute9,
377 p_attribute10 => l_attribute10,
378 p_attribute11 => l_attribute11,
379 p_attribute12 => l_attribute12,
380 p_attribute13 => l_attribute13,
381 p_attribute14 => l_attribute14,
382 p_attribute15 => l_attribute15,
383 p_cost_center => l_cost_center,
384 p_charge_to_cost_center => l_charge_to_cost_center,
385 p_comp_service_team_id => l_comp_service_team_id,
386 p_server_group_id => l_server_group_id,
387 p_interaction_center_name => l_interaction_center_name,
388 p_assigned_to_group_id => l_assigned_to_group_id,
389 p_support_site_id => l_support_site_id,
390 p_ies_agent_login => l_ies_agent_login,
391 p_cost_per_hr => l_cost_per_hr,
392 p_comp_currency_code => l_comp_currency_code,
393 p_commissionable_flag => l_commissionable_flag,
394 p_hold_reason_code => l_hold_reason_code,
395 p_hold_payment => l_hold_payment,
396 x_return_status => l_return_status,
397 x_msg_count => l_msg_count,
398 x_msg_data => l_msg_data,
399 x_resource_id => x_resource_id,
400 x_resource_number => x_resource_number
401 );
402
403 -- Debug info.
404 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
405 debug(p_message=>'After Create Reource call Return Status : '
406 ||l_return_status,
407 p_prefix =>l_debug_prefix,
408 p_msg_level=>fnd_log.level_statement);
409 END IF;
410
411 -- Message data reading logic
412 IF (l_return_status <> l_status_success
413 AND l_msg_count > 0)
414 THEN
415 l_msg_data1 := '';
416 FOR i IN 1..l_msg_count LOOP
417 l_msg_data1 := l_msg_data1||fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F')||', ';
418 END LOOP;
419
420
421 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
422 debug(p_message=>'Message Count:'||l_msg_count,
423 p_prefix =>l_debug_prefix,
424 p_msg_level=>fnd_log.level_statement);
425 END IF;
426 -- Debug info.
427 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
428 debug(p_message=>'Message:'||l_msg_data1,
429 p_prefix =>l_debug_prefix,
430 p_msg_level=>fnd_log.level_statement);
431 END IF;
432 END IF;
433 ---- End of Message data reading logic
434
435 --When return status is success then update Resource Id to Intf Table.
436 IF l_return_status = l_status_success THEN
437 UPDATE jtf_rs_resource_extns_int
438 SET operation_status = l_status_success,
439 operation_process_date = SYSDATE,
440 resource_id = x_resource_id
441 WHERE interface_id = l_interface_id;
442 ELSE
443 -- When return status is NOT success,update Error details to Intf Table.
444 UPDATE jtf_rs_resource_extns_int
445 SET operation_status = l_return_status,
446 operation_message = l_msg_data1,
447 operation_process_date = SYSDATE
448 WHERE interface_id = l_interface_id;
449 END IF;
450
451 l_commit_count := l_commit_count + 1;
452
453 EXCEPTION
454 WHEN OTHERS THEN
455
456 -- When any other unexpected error then try to capture it
457 l_msg_data1 := SQLERRM;
458 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
459
460 -- Debug info.
461 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
462 debug(p_message=>'Unexpected Error in Create Resource loop at'
463 ||' Batch Id :'||l_batch_id
464 ||' Interface Id :'||l_interface_id,
465 p_prefix =>l_debug_prefix,
466 p_msg_level=>fnd_log.level_statement);
467 END IF;
468
469 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
470 debug(p_message=>'Error is : '||l_msg_data1,
471 p_prefix =>l_debug_prefix,
472 p_msg_level=>fnd_log.level_statement);
473 END IF;
474
475 ROLLBACK TO do_create_resource_loop;
476
477 UPDATE jtf_rs_resource_extns_int
478 SET operation_status = l_return_status,
479 operation_message = l_msg_data1,
480 operation_process_date = SYSDATE
481 WHERE interface_id = l_interface_id;
482
483 l_commit_count := l_commit_count + 1;
484
485 END; -- End of BEGIN BLOCK for EACH record in LOOP
486
487 -- commit should be outside individual record processing block
488 IF MOD(l_commit_count,1000) = 0 THEN -- Commit after every 1000 records.
489 COMMIT;
490 l_commit_count := 0 ; -- reset the counter
491 END IF;
492
493 END LOOP; --End of Cursor loop.
494 CLOSE c_resource_int;
495
496 COMMIT;
497
498 -- Debug info.
499 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
500 debug(p_message =>' do_create_resource (-)',
501 p_prefix =>l_debug_prefix,
502 p_msg_level=>fnd_log.level_statement);
503 END IF;
504 EXCEPTION
505 WHEN OTHERS THEN
506 -- Debug info.
507 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
508 debug(p_message=>'Unexpected Error at do_create_resource procedure:'
509 ||SQLERRM,
510 p_prefix =>l_debug_prefix,
511 p_msg_level=>fnd_log.level_statement);
512 END IF;
513
514 -- if commit is there after 1000 recs and in update stmt, savepoint will
515 -- not be established.
516 -- ROLLBACK TO do_create_resource;
517 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
518 fnd_message.set_token('P_SQLCODE',SQLCODE);
519 fnd_message.set_token('P_SQLERRM',SQLERRM);
520 fnd_message.set_token('P_API_NAME', l_api_name);
521 FND_MSG_PUB.add;
522 x_return_status := fnd_api.g_ret_sts_unexp_error;
523 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
524 p_data => x_msg_data);
525 END do_create_resource;
526
527 /**
528 * PROCEDURE do_update_resource
529 *
530 * DESCRIPTION
531 * Create Resource.
532 *
533 * Private PROCEDURES/FUNCTIONS
534 *
535 * ARGUMENTS
536 * IN:
537 * p_batch_id Batch Id to process records.
538 *
539 * NOTES
540 *
541 * MODIFICATION HISTORY
542 * 10-June-2009 Sudhir Gokavarapu Created.
543 *
544 */
545
546 PROCEDURE do_update_resource
547 (P_BATCH_ID IN NUMBER,
548 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
549 X_MSG_COUNT OUT NOCOPY NUMBER,
550 X_MSG_DATA OUT NOCOPY VARCHAR2
551 ) IS
552
553 l_api_version CONSTANT NUMBER := 1.0;
554 l_status_error CONSTANT VARCHAR2(10) := fnd_api.g_ret_sts_error;
555 l_status_success CONSTANT VARCHAR2(10) := fnd_api.g_ret_sts_success;
556 -- l_miss_char VARCHAR2(1) := FND_API.G_MISS_CHAR;
557 -- l_miss_num NUMBER := FND_API.G_MISS_NUM;
558 -- l_miss_date DATE ;-- := FND_API.G_MISS_DATE;
559
560 -- l_null_char VARCHAR2(1) := FND_API.G_NULL_CHAR;
561 -- l_null_num NUMBER := FND_API.G_NULL_NUM;
562 -- l_null_date DATE := FND_API.G_NULL_DATE;
563 l_other_value BOOLEAN;
564 l_start_date_active_char VARCHAR2(20);
565
566 --Cursor to get Resource records for Create mode.
567 --If User wants to set to NULL from Not Null then provide FND_API.G_NULL_xxx
568 --When it match with FND_API.G_NULL_xxx then will provide NULL to public API.
569 --When it is NULL then will provide FND_API.G_MISS_xxx to public API to retain existing value.
570 --Bug16190251 -- Corrected attribute information in DECODE statements.
571 CURSOR c_resource_int (p_batch_id IN NUMBER)
572 IS
573 SELECT
574 interface_id,
575 DECODE(start_date_active,NULL,l_miss_date,l_null_date,NULL,start_date_active) start_date_active,
576 DECODE(end_date_active ,NULL,l_miss_date,l_null_date,NULL,end_date_active) end_date_active,
577 DECODE(compensation_currency_code,l_null_char,NULL,NULL,l_miss_char,compensation_currency_code) compensation_currency_code,
578 DECODE(commissionable_flag,l_null_char,NULL,NULL,l_miss_char,commissionable_flag) commissionable_flag,
579 DECODE(hold_reason_code,l_null_char,NULL,NULL,l_miss_char,hold_reason_code) hold_reason_code,
580 DECODE(hold_payment,l_null_char,NULL,NULL,l_miss_char,hold_payment) hold_payment,
581 DECODE(resource_name,l_null_char,NULL,NULL,l_miss_char,resource_name) resource_name,
582 DECODE(address_id,L_NULL_NUM,NULL,NULL,l_miss_num,address_id) address_id,
583 DECODE(contact_id,L_NULL_NUM,NULL,NULL,l_miss_num,contact_id) contact_id,
584 DECODE(managing_employee_id,L_NULL_NUM,NULL,NULL,l_miss_num,managing_employee_id) managing_employee_id,
585 DECODE(time_zone,L_NULL_NUM,NULL,NULL,l_miss_num,time_zone) time_zone,
586 DECODE(cost_per_hr,L_NULL_NUM,NULL,NULL,l_miss_num,cost_per_hr) cost_per_hr,
587 DECODE(primary_language,l_null_char,NULL,NULL,l_miss_char,primary_language) primary_language,
588 DECODE(secondary_language,l_null_char,NULL,NULL,l_miss_char,secondary_language) secondary_language,
589 DECODE(support_site_id,L_NULL_NUM,NULL,NULL,l_miss_num,support_site_id) support_site_id,
590 DECODE(ies_agent_login,l_null_char,NULL,NULL,l_miss_char,ies_agent_login) ies_agent_login,
591 DECODE(server_group_id,L_NULL_NUM,NULL,NULL,l_miss_num,server_group_id) server_group_id,
592 DECODE(assigned_to_group_id,L_NULL_NUM,NULL,NULL,l_miss_num,assigned_to_group_id) assigned_to_group_id,
593 DECODE(cost_center,l_null_char,NULL,NULL,l_miss_char,cost_center) cost_center,
594 DECODE(charge_to_cost_center,l_null_char,NULL,NULL,l_miss_char,charge_to_cost_center) charge_to_cost_center,
595 DECODE(comp_service_team_id,L_NULL_NUM,NULL,NULL,l_miss_num,comp_service_team_id) comp_service_team_id,
596 DECODE(user_id,L_NULL_NUM,NULL,NULL,l_miss_num,user_id) user_id,
597 DECODE(user_name,l_null_char,NULL,NULL,l_miss_char,user_name) user_name,
598 DECODE(attribute_category,l_null_char,NULL,NULL,l_miss_char,attribute_category) attribute_category,
599 DECODE(attribute1,l_null_char,NULL,NULL,l_miss_char,attribute1) attribute1,
600 DECODE(attribute2,l_null_char,NULL,NULL,l_miss_char,attribute2) attribute2,
601 DECODE(attribute3,l_null_char,NULL,NULL,l_miss_char,attribute3) attribute3,
602 DECODE(attribute4,l_null_char,NULL,NULL,l_miss_char,attribute4) attribute4,
603 DECODE(attribute5,l_null_char,NULL,NULL,l_miss_char,attribute5) attribute5,
604 DECODE(attribute6,l_null_char,NULL,NULL,l_miss_char,attribute6) attribute6,
605 DECODE(attribute7,l_null_char,NULL,NULL,l_miss_char,attribute7) attribute7,
606 DECODE(attribute8,l_null_char,NULL,NULL,l_miss_char,attribute8) attribute8,
607 DECODE(attribute9,l_null_char,NULL,NULL,l_miss_char,attribute9) attribute9,
608 DECODE(attribute10,l_null_char,NULL,NULL,l_miss_char,attribute10) attribute10,
609 DECODE(attribute11,l_null_char,NULL,NULL,l_miss_char,attribute11) attribute11,
610 DECODE(attribute12,l_null_char,NULL,NULL,l_miss_char,attribute12) attribute12,
611 DECODE(attribute13,l_null_char,NULL,NULL,l_miss_char,attribute13) attribute13,
612 DECODE(attribute14,l_null_char,NULL,NULL,l_miss_char,attribute14) attribute14,
613 DECODE(attribute15,l_null_char,NULL,NULL,l_miss_char,attribute15) attribute15,
614 resource_id,
615 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
616 --Character converted date To compare with l_miss_date.
617 FROM
618 jtf_rs_resource_extns_int
619 WHERE
620 batch_id = p_batch_id AND
621 category = 'OTHER' AND
622 operation = 'UPDATE' AND
623 operation_status IS NULL
624 ORDER BY interface_id;
625
626
627 l_category JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE;
628 l_object_version_number JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE;
629 l_end_date_active_db JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE;
630 l_start_date_active_db JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE;
631
632 x_object_version_num_res NUMBER;
633 l_commit_count NUMBER;
634
635 /*=================Variables for Resource Import========================*/
636 l_api_name VARCHAR2(30);
637 l_init_msg_list VARCHAR2(1);
638 l_commit VARCHAR2(1);
639 l_interface_id JTF_RS_RESOURCE_EXTNS_INT.INTERFACE_ID%TYPE;
640 l_source_id JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE;
641 l_address_id JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE;
642 l_contact_id JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE;
643 l_managing_emp_id JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE;
644 l_start_date_active JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE;
645 l_end_date_active JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE;
646 l_time_zone JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE;
647 l_cost_per_hr JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE;
648 l_primary_language JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE;
649 l_secondary_language JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE;
650 l_support_site_id JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE;
651 l_ies_agent_login JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE;
652 l_server_group_id JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE;
653 l_interaction_center_name VARCHAR2(256);
654 l_assigned_to_group_id JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE;
655 l_cost_center JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE;
656 l_charge_to_cost_center JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE;
657 l_comp_currency_code JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE;
658 l_commissionable_flag JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE;
659 l_hold_reason_code JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE;
660 l_hold_payment JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE;
661 l_comp_service_team_id JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE;
662 l_user_id JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE;
663 l_transaction_number JTF_RS_RESOURCE_EXTNS.TRANSACTION_NUMBER%TYPE;
664 x_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
665 x_resource_number JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE;
666 l_resource_name JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE;
667 l_user_name JTF_RS_RESOURCE_EXTNS.USER_NAME%TYPE;
668 l_attribute1 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE;
669 l_attribute2 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE;
670 l_attribute3 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE;
671 l_attribute4 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE;
672 l_attribute5 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE;
673 l_attribute6 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE;
674 l_attribute7 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE;
675 l_attribute8 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE;
676 l_attribute9 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE;
677 l_attribute10 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE;
678 l_attribute11 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE;
679 l_attribute12 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE;
680 l_attribute13 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE;
681 l_attribute14 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE;
682 l_attribute15 JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE;
683 l_attribute_category JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE;
684 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
685 l_resource_number JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE;
686 l_source_name JTF_RS_RESOURCE_EXTNS.source_name%TYPE;
687 l_category JTF_RS_RESOURCE_EXTNS.category%TYPE;
688 l_debug_prefix VARCHAR2(30) := 'RS_UPD:';
689 l_batch_id NUMBER;
690
691 l_return_status VARCHAR2(1);
692 l_msg_count NUMBER;
693 l_msg_data VARCHAR2(4000);
694 l_msg_data1 VARCHAR2(4000);
695
696 BEGIN
697
698 SAVEPOINT do_update_resource;
699 -- Debug info.
700 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
701 debug(p_message=>' do_update_resource (+)',
702 p_prefix =>l_debug_prefix,
703 p_msg_level=>fnd_log.level_statement);
704 END IF;
705
706 -- initialize variables
707 l_api_name := 'DO_UPDATE_RESOURCE';
708 l_miss_date := FND_API.G_MISS_DATE;
709 x_return_status := fnd_api.g_ret_sts_success;
710 l_init_msg_list := fnd_api.g_true;
711 l_commit := fnd_api.g_false;
712 l_commit_count := 0;
713 l_batch_id := p_batch_id;
714
715 -- Debug info.
716 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
717 debug(p_message=>'Batch Id : '||l_batch_id,
718 p_prefix =>l_debug_prefix,
719 p_msg_level=>fnd_log.level_statement);
720 END IF;
721
722
723 -- Debug info.
724 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
725 debug(p_message=>'Validating Resource Id ',
726 p_prefix =>l_debug_prefix,
727 p_msg_level=>fnd_log.level_statement);
728 END IF;
729
730 --Validate if Resource Id value is NULL.
731 -- Get translated value for 'Resource Id cannot be null in Update mode.'
732
733 l_trans_message := fnd_message.get_string('JTF','JTF_RS_RESOURCE_ID_NULL');
734
735 UPDATE jtf_rs_resource_extns_int
736 SET operation_status = l_status_error,
737 operation_message = l_trans_message,
738 operation_process_date = SYSDATE
739 WHERE batch_id = p_batch_id AND
740 operation = 'UPDATE' AND
741 operation_status IS NULL AND
742 resource_id IS NULL ;
743
744 -- Get the number of rows updated
745 l_commit_count := SQL%ROWCOUNT ;
746
747 -- Debug info.
748 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
749 DEBUG(p_message=>'Resource Id cannot be null in UPDATE mode:'||
750 to_char(l_commit_count),
751 p_prefix =>l_debug_prefix,
752 p_msg_level=>fnd_log.level_statement);
753 END IF;
754
755 IF l_commit_count >= 1000 THEN -- Commit if more than 1000 records.
756 COMMIT;
757 l_commit_count := 0 ; -- reset the counter
758 END IF;
759
760 --If Resource Id value is there then Validate is it valid or not.
761 -- Get translated value for 'Resource Id is not valid'
762
763 l_trans_message := fnd_message.get_string('JTF','JTF_RS_RES_ID_INVALID');
764
765 UPDATE jtf_rs_resource_extns_int rs_int
766 SET operation_status = l_status_error,
767 operation_message = l_trans_message,
768 operation_process_date = SYSDATE
769 WHERE batch_id = p_batch_id AND
770 operation = 'UPDATE' AND
771 operation_status IS NULL AND
772 resource_id IS NOT NULL
773 AND NOT EXISTS
774 (SELECT 1 FROM jtf_rs_resource_extns rs
775 WHERE rs.resource_id = rs_int.resource_id);
776
777 -- Get the number of rows updated
778 l_commit_count := l_commit_count + SQL%ROWCOUNT ;
779
780 -- Debug info.
781 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
782 DEBUG(p_message=>'Resource Id is not valid:'||
783 to_char(SQL%ROWCOUNT),
784 p_prefix =>l_debug_prefix,
785 p_msg_level=>fnd_log.level_statement);
786 END IF;
787
788 IF l_commit_count >= 1000 THEN -- Commit if more than 1000 records.
789 COMMIT;
790 l_commit_count := 0 ; -- reset the counter
791 END IF;
792
793 -- As per ciurrent scope only resource of CATEGORY OTHER is supported
794 -- by Resource manager interface
795 --If Resource Id is valid then check if it is belong to OTHER category.
796
797 -- Get translated value for 'Resource Id is not of OTHER category.'
798 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RES_ID_CAT');
799 fnd_message.set_token('P_CATEGORY','OTHER');
800 fnd_msg_pub.add;
801
802 l_trans_message := FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE);
803
804 UPDATE jtf_rs_resource_extns_int rs_int
805 SET operation_status = l_status_error,
806 operation_message = l_trans_message,
807 operation_process_date = SYSDATE
808 WHERE batch_id = p_batch_id AND
809 operation = 'UPDATE' AND
810 operation_status IS NULL AND
811 resource_id IS NOT NULL
812 AND NOT EXISTS
813 (SELECT 1 FROM jtf_rs_resource_extns rs
814 WHERE rs.resource_id = rs_int.resource_id
815 AND rs.category = 'OTHER' );
816
817 -- Get the number of rows updated
818 l_commit_count := l_commit_count + SQL%ROWCOUNT ;
819
820 -- Debug info.
821 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
822 DEBUG(p_message=>'Resource Id is not of OTHER category.'||
823 to_char(SQL%ROWCOUNT),
824 p_prefix =>l_debug_prefix,
825 p_msg_level=>fnd_log.level_statement);
826 END IF;
827
828 IF l_commit_count >= 1000 THEN -- Commit if more than 1000 records.
829 COMMIT;
830 l_commit_count := 0 ; -- reset the counter
831 END IF;
832
833
834 -- Debug info.
835 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
836 debug(p_message=>'Validating - Resource Name cannot be updated to null ',
837 p_prefix =>l_debug_prefix,
838 p_msg_level=>fnd_log.level_statement);
839 END IF;
840
841 --Validate if Resource Id value is NULL.
842 --'Resource Name should not be updated to NULL.'
843 --Get traslated Resource Name to set prompts.
844
845 l_trans_message := fnd_message.get_string('JTF','JTF_RS_ISET_RESOURCE_NAME');
846
847 -- Get translated value for 'Resource Name cannot be null'
848 fnd_message.set_name('JTF', 'JTF_RS_NOT_NULL');
849 fnd_message.set_token('PROMPTS', l_trans_message);
850 fnd_msg_pub.add;
851
852 l_trans_message := FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE);
853
854 UPDATE jtf_rs_resource_extns_int
855 SET operation_status = l_status_error,
856 operation_message = l_trans_message,
857 operation_process_date = SYSDATE
858 WHERE batch_id = p_batch_id AND
859 operation = 'UPDATE' AND
860 operation_status IS NULL AND
861 resource_name = l_null_char;
862
863 -- Get the number of rows updated
864 l_commit_count := SQL%ROWCOUNT ;
865
866 -- Debug info.
867 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
868 DEBUG(p_message=>'Resource Name should not be updated to NULL:'||
869 to_char(l_commit_count),
870 p_prefix =>l_debug_prefix,
871 p_msg_level=>fnd_log.level_statement);
872 END IF;
873
874 IF l_commit_count >= 1000 THEN -- Commit if more than 1000 records.
875 COMMIT;
876 l_commit_count := 0 ; -- reset the counter
877 END IF;
878
879 -- Debug info.
880 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
881 debug(p_message=>'Validating - Active Start Date cannot be updated to null ',
882 p_prefix =>l_debug_prefix,
883 p_msg_level=>fnd_log.level_statement);
884 END IF;
885
886 --Validate if Resource Id value is NULL.
887 -- Get translated value for 'Start Date Active cannot be null'
888 l_trans_message := fnd_message.get_string('JTF','JTF_RS_START_DATE_NULL');
889
890 --'Active Start Date should not be updated to NULL.'
891 UPDATE jtf_rs_resource_extns_int
892 SET operation_status = l_status_error,
893 operation_message = l_trans_message,
894 operation_process_date = SYSDATE
895 WHERE batch_id = p_batch_id AND
896 operation = 'UPDATE' AND
897 operation_status IS NULL AND
898 start_date_active = l_null_date;
899
900 -- Get the number of rows updated
901 l_commit_count := SQL%ROWCOUNT ;
902
903 -- Debug info.
904 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
905 DEBUG(p_message=>'Active Start Date should not be updated to NULL:'||
906 to_char(l_commit_count),
907 p_prefix =>l_debug_prefix,
908 p_msg_level=>fnd_log.level_statement);
909 END IF;
910
911 IF l_commit_count >= 1000 THEN -- Commit if more than 1000 records.
912 COMMIT;
913 l_commit_count := 0 ; -- reset the counter
914 END IF;
915
916 OPEN c_resource_int(p_batch_id);
917 LOOP
918 FETCH c_resource_int
919 INTO
920 l_interface_id, l_start_date_active, l_end_date_active, l_comp_currency_code,
921 l_commissionable_flag, l_hold_reason_code, l_hold_payment, l_resource_name,
922 l_address_id, l_contact_id, l_managing_emp_id,
923 l_time_zone, l_cost_per_hr, l_primary_language, l_secondary_language,
924 l_support_site_id, l_ies_agent_login, l_server_group_id,
925 l_assigned_to_group_id, l_cost_center, l_charge_to_cost_center, l_comp_service_team_id,
926 l_user_id, l_user_name, l_attribute_category,
927 l_attribute1, l_attribute2, l_attribute3, l_attribute4,
928 l_attribute5, l_attribute6, l_attribute7, l_attribute8,
929 l_attribute9, l_attribute10, l_attribute11, l_attribute12,
930 l_attribute13, l_attribute14, l_attribute15,
931 l_resource_id, l_start_date_active_char;
932 EXIT WHEN c_resource_int%NOTFOUND ;
933
934 BEGIN
935 SAVEPOINT do_update_resource_loop;
936
937 -- Debug info.
938 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
939 debug(p_message=>'Update Resource mode Interface Id :'||
940 l_interface_id ||' Resource Id :'||l_resource_id ,
941 p_prefix =>l_debug_prefix,
942 p_msg_level=>fnd_log.level_statement);
943 END IF;
944
945 --Get Object Version Number and End Date Active.
946 SELECT object_version_number,end_date_active,start_date_active
947 INTO l_object_version_number,l_end_date_active_db,l_start_date_active_db
948 FROM jtf_rs_resource_extns
949 WHERE resource_id = l_resource_id;
950
951 -- If Start Date Active is NULL then we are getting l_miss_date into l_start_date.
952 -- While validating Input dates l_miss_date is always greater than equal to
953 -- any value in l_end_date_active and system will throw error.
954 -- To validate actual values we are assigning Actual value of START_DATE_ACTIVE in
955 -- JTF_RS_RESOURCE_EXTN table to l_start_date_active.
956 IF l_start_date_active_char = to_char(l_miss_date,'dd/mm/yyyy hh24:mi:ss') THEN
957 l_start_date_active := l_start_date_active_db;
958 END IF;
959
960 -- If end_date_active contains value then
961 -- if resource was active or had end date greater than end date passed in the interface table,
962 -- we have to end date resource and related child entities
963 -- like resource role, group member role, salesrep etc.
964 -- To accomplish this
965 -- If there is any additional attribute other than end date to be updated,
966 -- first call JTF_RS_RESOURCE_PUB.update_resource procedure without end date,
967 -- then call jtf_rs_resource_utl_pub.end_date_employee procedure for end dating.
968
969
970 IF (
971 ((l_end_date_active IS NOT NULL AND l_end_date_active <> l_miss_date)
972 OR ( to_date(to_char(l_end_date_active,'DD-MM-RRRR hh24:mi:ss'),'DD-MM-RRRR hh24:mi:ss')
973 < l_end_date_active_db ))
974 AND
975 fnd_profile.value('JTF_RS_INTF_END_DATE_CHILD') <> 'N'
976 )
977 THEN
978 -- Debug info.
979 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
980 debug(p_message=>'End date active is having value. '||
981 'l_end_date_active:'||TO_CHAR(l_end_date_active,'dd-MM-RRRR hh24:mi:ss')||
982 'l_end_date_active_db:'||TO_CHAR(l_end_date_active_db,'dd-MM-RRRR hh24:mi:ss'),
983 p_prefix =>l_debug_prefix,
984 p_msg_level=>fnd_log.level_statement);
985 END IF;
986 JTF_RESOURCE_UTL.validate_input_dates
987 (p_start_date_active => l_start_date_active,
988 p_end_date_active => l_end_date_active,
989 x_return_status => l_return_status
990 );
991 -- Debug info.
992 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
993 debug(p_message=>'After Validating Date values Return Status : '||l_return_status,
994 p_prefix =>l_debug_prefix,
995 p_msg_level=>fnd_log.level_statement);
996 END IF;
997
998 IF l_return_status <> l_status_success THEN
999 -- l_start_date_active cannot be null so if this API returns error status means
1000 -- only chance of JTF_RS_ERR_STDT_GREATER_EDDT
1001
1002 l_trans_message := fnd_message.get_string('JTF','JTF_RS_ERR_STDT_GREATER_EDDT');
1003
1004 --'Date values should be valid'
1005 UPDATE jtf_rs_resource_extns_int
1006 SET operation_status = l_return_status,
1007 operation_message = l_trans_message,
1008 operation_process_date = SYSDATE
1009 WHERE interface_id = l_interface_id;
1010
1011 -- When return status is NOT success then update Error details to Interface Table.
1012 -- Debug info.
1013 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1014 debug(p_message=>'Error in Update Resource loop at Resource Id,Batch Id, Interface Id : '||l_resource_id||' '||l_batch_id||' '||l_interface_id,
1015 p_prefix =>l_debug_prefix,
1016 p_msg_level=>fnd_log.level_statement);
1017 END IF;
1018 -- Debug info.
1019 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1020 debug(p_message=>'Message:'||l_trans_message,
1021 p_prefix =>l_debug_prefix,
1022 p_msg_level=>fnd_log.level_statement);
1023 END IF;
1024 END IF;
1025
1026 IF l_return_status = l_status_success THEN
1027
1028
1029
1030 -- Check if there is any additional attribute other than end date to be updated.
1031 -- As we already converted Null to MISS_xxx, here we are comparing with MISS_xxx.
1032 -- Start date is converted to character and verifying with l_miss_date.
1033 -- If it is not converted then it is taking default date format (Ex : Year YY format).
1034 IF l_start_date_active_char <> to_char(l_miss_date,'dd/mm/yyyy hh24:mi:ss') OR
1035 l_comp_currency_code <> l_miss_char OR
1036 l_commissionable_flag <> l_miss_char OR l_hold_reason_code <> l_miss_char OR
1037 l_hold_payment <> l_miss_char OR l_resource_name <> l_miss_char OR
1038 l_address_id <> l_miss_num OR l_contact_id <> l_miss_num OR
1039 l_managing_emp_id <> l_miss_num OR l_time_zone <> l_miss_num OR
1040 l_cost_per_hr <> l_miss_num OR l_primary_language <> l_miss_char OR
1041 l_secondary_language <> l_miss_char OR l_support_site_id <> l_miss_num OR
1042 l_ies_agent_login <> l_miss_char OR l_server_group_id <> l_miss_num OR
1043 l_assigned_to_group_id <> l_miss_num OR l_cost_center <> l_miss_char OR
1044 l_charge_to_cost_center <> l_miss_char OR l_comp_service_team_id <> l_miss_num OR
1045 l_user_id <> l_miss_num OR l_user_name <> l_miss_char OR
1046 l_attribute_category <> l_miss_char OR l_attribute1 <> l_miss_char OR
1047 l_attribute2 <> l_miss_char OR l_attribute3 <> l_miss_char OR
1048 l_attribute4 <> l_miss_char OR l_attribute5 <> l_miss_char OR
1049 l_attribute6 <> l_miss_char OR l_attribute7 <> l_miss_char OR
1050 l_attribute8 <> l_miss_char OR l_attribute9 <> l_miss_char OR
1051 l_attribute10 <> l_miss_char OR l_attribute11 <> l_miss_char OR
1052 l_attribute12 <> l_miss_char OR l_attribute13 <> l_miss_char OR
1053 l_attribute14 <> l_miss_char OR l_attribute15 <> l_miss_char
1054 THEN
1055 l_other_value := TRUE; -- Other_value flag set to True.
1056 -- Debug info.
1057 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1058 debug(p_message=>'l_other_value set to TRUE',
1059 p_prefix =>l_debug_prefix,
1060 p_msg_level=>fnd_log.level_statement);
1061 END IF;
1062
1063 ELSE
1064 -- Other_value flag set to False. No value other than end date to be updated.
1065 l_other_value := FALSE;
1066 -- Debug info.
1067 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1068 debug(p_message=>'l_other_value set to FALSE',
1069 p_prefix =>l_debug_prefix,
1070 p_msg_level=>fnd_log.level_statement);
1071 END IF;
1072
1073 END IF;
1074
1075 IF NOT l_other_value THEN
1076
1077 -- Debug info.
1078 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1079 debug(p_message=>'No value other than end date to be updated. ',
1080 p_prefix =>l_debug_prefix,
1081 p_msg_level=>fnd_log.level_statement);
1082 END IF;
1083
1084 -- Other_value flag value is False. No value other than end date to be updated.
1085 -- Call only end_date_employee and move to next record.
1086 jtf_rs_resource_utl_pub.end_date_employee(
1087 p_api_version => l_api_version,
1088 p_init_msg_list => l_init_msg_list,
1089 p_commit => l_commit,
1090 p_resource_id => l_resource_id,
1091 p_end_date_active => l_end_date_active,
1092 X_OBJECT_VER_NUMBER => x_object_version_num_res,
1093 x_return_status => l_return_status,
1094 x_msg_count => l_msg_count,
1095 x_msg_data => l_msg_data );
1096
1097 -- Debug info.
1098 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1099 debug(p_message=>'After end_date_employee call Return Status : '||l_return_status,
1100 p_prefix =>l_debug_prefix,
1101 p_msg_level=>fnd_log.level_statement);
1102 END IF;
1103
1104 ---- Message data reading logic
1105 IF (l_return_status <> l_status_success
1106 AND l_msg_count > 0)
1107 THEN
1108 l_msg_data1 := '';
1109 FOR i IN 1..l_msg_count LOOP
1110 l_msg_data1 := l_msg_data1||fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F')||', ';
1111 END LOOP;
1112
1113 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1114 debug(p_message=>'Message Count:'||l_msg_count,
1115 p_prefix =>l_debug_prefix,
1116 p_msg_level=>fnd_log.level_statement);
1117 END IF;
1118 -- Debug info.
1119 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1120 debug(p_message=>'Message:'||l_msg_data1,
1121 p_prefix =>l_debug_prefix,
1122 p_msg_level=>fnd_log.level_statement);
1123 END IF;
1124 END IF;
1125 ---- End of Message data reading logic
1126
1127 --When return status is success then update Status to Interface Table.
1128 IF l_return_status = l_status_success THEN
1129 UPDATE jtf_rs_resource_extns_int
1130 SET operation_status = l_status_success,
1131 operation_process_date = SYSDATE
1132 WHERE interface_id = l_interface_id;
1133 ELSE-- When return status is NOT success then update Error details to Interface Table.
1134 UPDATE jtf_rs_resource_extns_int
1135 SET operation_status = l_return_status,
1136 operation_message = l_msg_data1,
1137 operation_process_date = SYSDATE
1138 WHERE interface_id = l_interface_id;
1139 END IF;
1140
1141 ELSE -- having other than end date to be updated
1142 -- Other_value flag value is True.
1143 -- Call update_resource with end_date_active value and then
1144 -- call end_date_employee before moving to next record.
1145 -- Debug info.
1146 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1147 debug(p_message=>'Having other than end date to be updated. ',
1148 p_prefix =>l_debug_prefix,
1149 p_msg_level=>fnd_log.level_statement);
1150 END IF;
1151 jtf_rs_resource_pub.update_resource(
1152 p_api_version => l_api_version,
1153 p_init_msg_list => l_init_msg_list,
1154 p_commit => l_commit,
1155 p_address_id => l_address_id,
1156 p_managing_emp_id => l_managing_emp_id,
1157 p_start_date_active => l_start_date_active,
1158 p_user_id => l_user_id,
1159 p_time_zone => l_time_zone,
1160 p_primary_language => l_primary_language,
1161 p_secondary_language => l_secondary_language,
1162 x_return_status => l_return_status,
1163 x_msg_count => l_msg_count,
1164 x_msg_data => l_msg_data,
1165 p_resource_id => l_resource_id,
1166 p_resource_number => x_resource_number,
1167 p_source_name => l_source_name,
1168 p_resource_name => l_RESOURCE_NAME,
1169 p_user_name => l_user_name,
1170 p_object_version_num => l_object_version_number,
1171 p_attribute_category => l_attribute_category,
1172 p_attribute1 => l_attribute1,
1173 p_attribute2 => l_attribute2,
1174 p_attribute3 => l_attribute3,
1175 p_attribute4 => l_attribute4,
1176 p_attribute5 => l_attribute5,
1177 p_attribute6 => l_attribute6,
1178 p_attribute7 => l_attribute7,
1179 p_attribute8 => l_attribute8,
1180 p_attribute9 => l_attribute9,
1181 p_attribute10 => l_attribute10,
1182 p_attribute11 => l_attribute11,
1183 p_attribute12 => l_attribute12,
1184 p_attribute13 => l_attribute13,
1185 p_attribute14 => l_attribute14,
1186 p_attribute15 => l_attribute15,
1187 p_cost_center => l_cost_center,
1188 p_charge_to_cost_center => l_charge_to_cost_center,
1189 p_comp_service_team_id => l_comp_service_team_id,
1190 p_server_group_id => l_server_group_id,
1191 p_assigned_to_group_id => l_assigned_to_group_id,
1192 p_support_site_id => l_support_site_id,
1193 p_ies_agent_login => l_ies_agent_login,
1194 p_cost_per_hr => l_cost_per_hr,
1195 p_comp_currency_code => l_comp_currency_code,
1196 p_commissionable_flag => l_commissionable_flag,
1197 p_hold_reason_code => l_hold_reason_code,
1198 p_hold_payment => l_hold_payment
1199 );
1200 -- Debug info.
1201 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1202 debug(p_message=>'After Update Resource (without end date) call Return Status : '||l_return_status,
1203 p_prefix =>l_debug_prefix,
1204 p_msg_level=>fnd_log.level_statement);
1205 END IF;
1206
1207 ---- Message data reading logic
1208 IF (l_return_status <> l_status_success
1209 AND l_msg_count > 0)
1210 THEN
1211
1212 l_msg_data1 := '';
1213 FOR i IN 1..l_msg_count LOOP
1214 l_msg_data1 := l_msg_data1||fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F')||', ';
1215 END LOOP;
1216
1217 -- Debug info.
1218 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1219 debug(p_message=>'Message:'||l_msg_data1,
1220 p_prefix =>l_debug_prefix,
1221 p_msg_level=>fnd_log.level_statement);
1222 END IF;
1223 END IF;
1224 ---- End of Message data reading logic
1225
1226
1227 --When return status is success then update Status to Interface Table.
1228 IF l_return_status = l_status_success THEN
1229 UPDATE jtf_rs_resource_extns_int
1230 SET operation_status = l_status_success,
1231 operation_process_date = SYSDATE
1232 WHERE interface_id = l_interface_id;
1233 ELSE-- When return status is NOT success then update Error details to Interface Table.
1234 -- Debug info.
1235 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1236 debug(p_message=>'Error in Update Resource loop at Resource Id,Batch Id, Interface Id : '||l_resource_id||' '||l_batch_id||' '||l_interface_id,
1237 p_prefix =>l_debug_prefix,
1238 p_msg_level=>fnd_log.level_statement);
1239 END IF;
1240 UPDATE jtf_rs_resource_extns_int
1241 SET operation_status = l_return_status,
1242 operation_message = l_msg_data1,
1243 operation_process_date = SYSDATE
1244 WHERE interface_id = l_interface_id;
1245 -- CONTINUE;
1246 END IF;
1247 --Bug#8786536
1248 --Commented CONTINUE statement in above loop and added IF loop accordingly.
1249 --If update_resource without end_date value returns success
1250 --then only proceed further with end_date_employee call for current record.
1251 IF l_return_status = l_status_success THEN
1252 -- Debug info.
1253 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1254 debug(p_message=>'Before calling end_date_employee and after Update Resource (without end date) call.',
1255 p_prefix =>l_debug_prefix,
1256 p_msg_level=>fnd_log.level_statement);
1257 END IF;
1258
1259 jtf_rs_resource_utl_pub.end_date_employee(
1260 p_api_version => 1.0,
1261 p_init_msg_list => l_init_msg_list,
1262 p_commit => l_commit,
1263 p_resource_id => l_resource_id,
1264 p_end_date_active => l_end_date_active,
1265 X_OBJECT_VER_NUMBER => x_object_version_num_res,
1266 x_return_status => l_return_status,
1267 x_msg_count => l_msg_count,
1268 x_msg_data => l_msg_data );
1269
1270 -- Debug info.
1271 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1272 debug(p_message=>'After end_date_employee call Return Status : '||l_return_status,
1273 p_prefix =>l_debug_prefix,
1274 p_msg_level=>fnd_log.level_statement);
1275 END IF;
1276
1277 ---- Message data reading logic
1278 IF (l_return_status <> l_status_success
1279 AND l_msg_count > 0)
1280 THEN
1281 l_msg_data1 := '';
1282 FOR i IN 1..l_msg_count LOOP
1283 l_msg_data1 := l_msg_data1||fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F')||', ';
1284 END LOOP;
1285
1286 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1287 debug(p_message=>'Message Count:'||l_msg_count,
1288 p_prefix =>l_debug_prefix,
1289 p_msg_level=>fnd_log.level_statement);
1290 END IF;
1291 -- Debug info.
1292 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1293 debug(p_message=>'Message:'||l_msg_data1,
1294 p_prefix =>l_debug_prefix,
1295 p_msg_level=>fnd_log.level_statement);
1296 END IF;
1297 END IF;
1298 ---- End of Message data reading logic
1299
1300
1301 --When return status is success then update Status to Interface Table.
1302 IF l_return_status = l_status_success THEN
1303 UPDATE jtf_rs_resource_extns_int
1304 SET operation_status = l_status_success,
1305 operation_process_date = SYSDATE
1306 WHERE interface_id = l_interface_id;
1307 ELSE-- When return status is NOT success then update Error details to Interface Table.
1308 UPDATE jtf_rs_resource_extns_int
1309 SET operation_status = l_return_status,
1310 operation_message = l_msg_data1,
1311 operation_process_date = SYSDATE
1312 WHERE interface_id = l_interface_id;
1313 END IF;
1314 END IF;
1315 END IF; --l_other_value
1316 END IF; -- Date value validation
1317 ELSE --end_date_active check, Normal call
1318
1319 --Call the Public procedure to Update Resource.
1320 JTF_RS_RESOURCE_PUB.UPDATE_RESOURCE(
1321 p_api_version => 1.0,
1322 p_init_msg_list => l_init_msg_list,
1323 p_commit => l_commit,
1324 p_address_id => l_address_id,
1325 p_managing_emp_id => l_managing_emp_id,
1326 p_start_date_active => l_start_date_active,
1327 p_end_date_active => l_end_date_active,
1328 p_user_id => l_user_id,
1329 p_time_zone => l_time_zone,
1330 p_primary_language => l_primary_language,
1331 p_secondary_language => l_secondary_language,
1332 x_return_status => l_return_status,
1333 x_msg_count => l_msg_count,
1334 x_msg_data => l_msg_data,
1335 p_resource_id => l_resource_id,
1336 p_resource_number => x_resource_number,
1337 p_source_name => l_source_name,
1338 p_resource_name => l_RESOURCE_NAME,
1339 p_user_name => l_user_name,
1340 p_object_version_num => l_object_version_number,
1341 p_attribute_category => l_attribute_category,
1342 p_attribute1 => l_attribute1,
1343 p_attribute2 => l_attribute2,
1344 p_attribute3 => l_attribute3,
1345 p_attribute4 => l_attribute4,
1346 p_attribute5 => l_attribute5,
1347 p_attribute6 => l_attribute6,
1348 p_attribute7 => l_attribute7,
1349 p_attribute8 => l_attribute8,
1350 p_attribute9 => l_attribute9,
1351 p_attribute10 => l_attribute10,
1352 p_attribute11 => l_attribute11,
1353 p_attribute12 => l_attribute12,
1354 p_attribute13 => l_attribute13,
1355 p_attribute14 => l_attribute14,
1356 p_attribute15 => l_attribute15,
1357 p_cost_center => l_cost_center,
1358 p_charge_to_cost_center => l_charge_to_cost_center,
1359 p_comp_service_team_id => l_comp_service_team_id,
1360 p_server_group_id => l_server_group_id,
1361 p_assigned_to_group_id => l_assigned_to_group_id,
1362 p_support_site_id => l_support_site_id,
1363 p_ies_agent_login => l_ies_agent_login,
1364 p_cost_per_hr => l_cost_per_hr,
1365 p_comp_currency_code => l_comp_currency_code,
1366 p_commissionable_flag => l_commissionable_flag,
1367 p_hold_reason_code => l_hold_reason_code,
1368 p_hold_payment => l_hold_payment
1369 );
1370 -- Debug info.
1371 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1372 debug(p_message=>'After Update Resource call Return Status : '||l_return_status,
1373 p_prefix =>l_debug_prefix,
1374 p_msg_level=>fnd_log.level_statement);
1375 END IF;
1376
1377 ---- Message data reading logic
1378 IF (l_return_status <> l_status_success
1379 AND l_msg_count > 0)
1380 THEN
1381 l_msg_data1 := '';
1382 FOR i IN 1..l_msg_count LOOP
1383 l_msg_data1 := l_msg_data1||fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F')||', ';
1384 END LOOP;
1385
1386 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1387 debug(p_message=>'Message Count:'||l_msg_count,
1388 p_prefix =>l_debug_prefix,
1389 p_msg_level=>fnd_log.level_statement);
1390 END IF;
1391 -- Debug info.
1392 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1393 debug(p_message=>'Message:'||l_msg_data1,
1394 p_prefix =>l_debug_prefix,
1395 p_msg_level=>fnd_log.level_statement);
1396 END IF;
1397 END IF;
1398 ---- End of Message data reading logic
1399
1400 --When return status is success then update Status to Interface Table.
1401 IF l_return_status = l_status_success THEN
1402 UPDATE jtf_rs_resource_extns_int
1403 SET operation_status = l_status_success,
1404 operation_process_date = SYSDATE
1405 WHERE interface_id = l_interface_id;
1406 ELSE-- When return status is NOT success then update Error details to Interface Table.
1407 UPDATE jtf_rs_resource_extns_int
1408 SET operation_status = l_return_status,
1409 operation_message = l_msg_data1,
1410 operation_process_date = SYSDATE
1411 WHERE interface_id = l_interface_id;
1412
1413 END IF;
1414 END IF; --end_date_active check
1415
1416 l_commit_count := l_commit_count + 1;
1417
1418 EXCEPTION
1419 WHEN OTHERS THEN
1420
1421 -- When any other unexpected error then try to capture it
1422 l_msg_data1 := SQLERRM;
1423 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1424
1425 -- Debug info.
1426 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1427 debug(p_message=>'Unexpected Error in Update Resource loop at'
1428 ||' Batch Id :'||l_batch_id
1429 ||' Interface Id :'||l_interface_id,
1430 p_prefix =>l_debug_prefix,
1431 p_msg_level=>fnd_log.level_statement);
1432 END IF;
1433
1434 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1435 debug(p_message=>'Error is : '||l_msg_data1,
1436 p_prefix =>l_debug_prefix,
1437 p_msg_level=>fnd_log.level_statement);
1438 END IF;
1439
1440 ROLLBACK TO do_create_resource_loop;
1441
1442 UPDATE jtf_rs_resource_extns_int
1443 SET operation_status = l_return_status,
1444 operation_message = l_msg_data1,
1445 operation_process_date = SYSDATE
1446 WHERE interface_id = l_interface_id;
1447
1448 l_commit_count := l_commit_count + 1;
1449
1450 END; -- End of BEGIN BLOCK for EACH record in LOOP
1451
1452 -- commit should be outside individual record processing block
1453 IF MOD(l_commit_count,1000) = 0 THEN -- Commit after every 1000 records.
1454 COMMIT;
1455 l_commit_count := 0 ; -- reset the counter
1456 END IF;
1457
1458 END LOOP;
1459 CLOSE c_resource_int;
1460
1461 COMMIT;
1462 -- Debug info.
1463 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1464 debug(p_message=>' do_update_resource (-)',
1465 p_prefix =>l_debug_prefix,
1466 p_msg_level=>fnd_log.level_statement);
1467 END IF;
1468
1469 EXCEPTION
1470 WHEN fnd_api.g_exc_error THEN
1471 ROLLBACK TO do_update_resource;
1472 x_return_status := fnd_api.g_ret_sts_error;
1473 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1474 p_data => x_msg_data);
1475 WHEN fnd_api.g_exc_unexpected_error THEN
1476 ROLLBACK TO do_update_resource;
1477 x_return_status := fnd_api.g_ret_sts_unexp_error;
1478 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1479 p_data => x_msg_data);
1480 WHEN OTHERS THEN
1481 -- Debug info.
1482 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1483 debug(p_message=>'Unexpected Error at do_update_resource procedure '||SQLERRM,
1484 p_prefix =>l_debug_prefix,
1485 p_msg_level=>fnd_log.level_statement);
1486 END IF;
1487 ROLLBACK TO do_update_resource;
1488 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1489 fnd_message.set_token('P_SQLCODE',SQLCODE);
1490 fnd_message.set_token('P_SQLERRM',SQLERRM);
1491 fnd_message.set_token('P_API_NAME', l_api_name);
1492 FND_MSG_PUB.add;
1493 x_return_status := fnd_api.g_ret_sts_unexp_error;
1494 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1495 p_data => x_msg_data);
1496 END;
1497
1498 /**
1499 * PROCEDURE do_create_salesrep
1500 *
1501 * DESCRIPTION
1502 * Create SalesRep.
1503 *
1504 * Private PROCEDURES/FUNCTIONS
1505 *
1506 * ARGUMENTS
1507 * IN:
1508 * p_batch_id Batch Id to process records.
1509 *
1510 * NOTES
1511 *
1512 * MODIFICATION HISTORY
1513 * 10-June-2009 Sudhir Gokavarapu Created.
1514 *
1515 */
1516 PROCEDURE do_create_salesrep
1517 (p_batch_id IN NUMBER,
1518 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1519 X_MSG_COUNT OUT NOCOPY NUMBER,
1520 X_MSG_DATA OUT NOCOPY VARCHAR2
1521 ) IS
1522
1523
1524 --Cursor to get Salesrep records for Create mode.
1525 CURSOR c_salesrep_int (p_batch_id IN NUMBER)
1526 IS
1527 SELECT
1528 interface_id, salesrep_id,
1529 resource_id, sales_credit_type_id, status,
1530 start_date_active, end_date_active, salesrep_number,
1531 org_id, email_address, gl_id_rev,
1532 gl_id_freight, gl_id_rec, set_of_books_id,
1533 sales_tax_geocode, sales_tax_inside_city_limits
1534 FROM
1535 jtf_rs_salesreps_int
1536 WHERE
1537 batch_id = p_batch_id AND
1538 operation = 'CREATE' AND
1539 operation_status IS NULL
1540 ORDER BY interface_id;
1541
1542 l_salesrep_id JTF_RS_SALESREPS.salesrep_id%TYPE;
1543 l_sales_credit_type_id JTF_RS_SALESREPS.sales_credit_type_id%TYPE;
1544 l_status JTF_RS_SALESREPS.status%TYPE;
1545 l_salesrep_number JTF_RS_SALESREPS.salesrep_number%TYPE;
1546 l_org_id JTF_RS_SALESREPS.org_id%TYPE;
1547 l_email_address JTF_RS_SALESREPS.email_address%TYPE;
1548 l_gl_id_rev JTF_RS_SALESREPS.gl_id_rev%TYPE;
1549 l_gl_id_freight JTF_RS_SALESREPS.gl_id_freight%TYPE;
1550 l_gl_id_rec JTF_RS_SALESREPS.gl_id_rec%TYPE;
1551 l_set_of_books_id JTF_RS_SALESREPS.set_of_books_id%TYPE;
1552 l_sales_tax_geocode JTF_RS_SALESREPS.sales_tax_geocode%TYPE;
1553 l_sales_tax_inside_city_limits JTF_RS_SALESREPS.sales_tax_inside_city_limits%TYPE;
1554 x_salesrep_id JTF_RS_SALESREPS.salesrep_id%TYPE;
1555 l_start_date_active DATE;--JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE;
1556 l_end_date_active JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE;
1557 l_interface_id JTF_RS_RESOURCE_EXTNS_INT.INTERFACE_ID%TYPE;
1558 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
1559 l_commit_count NUMBER ;
1560 l_api_name VARCHAR2(30);
1561 l_init_msg_list VARCHAR2(1);
1562 l_commit VARCHAR2(1);
1563 l_api_version CONSTANT NUMBER := 1.0;
1564 l_status_error CONSTANT VARCHAR2(10) := fnd_api.g_ret_sts_error;
1565 l_status_success CONSTANT VARCHAR2(10) := fnd_api.g_ret_sts_success;
1566 l_debug_prefix VARCHAR2(30) := 'RS_UPD:';
1567 l_batch_id NUMBER;
1568 l_return_status VARCHAR2(1);
1569 l_msg_count NUMBER;
1570 l_msg_data VARCHAR2(4000);
1571 l_msg_data1 VARCHAR2(4000);
1572 BEGIN
1573
1574 SAVEPOINT do_create_salesrep;
1575
1576 l_api_name := 'DO_CREATE_SALESREP';
1577 -- Debug info.
1578 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1579 debug(p_message=>'do_create_salesrep (+)',
1580 p_prefix =>l_debug_prefix,
1581 p_msg_level=>fnd_log.level_statement);
1582 END IF;
1583
1584 -- initialize variables
1585 x_return_status := fnd_api.g_ret_sts_success;
1586 l_init_msg_list := fnd_api.g_true;
1587 l_commit := fnd_api.g_false;
1588 l_commit_count := 0;
1589 l_batch_id := p_batch_id;
1590
1591 -- Debug info.
1592 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1593 debug(p_message=>'Batch Id : '||l_batch_id,
1594 p_prefix =>l_debug_prefix,
1595 p_msg_level=>fnd_log.level_statement);
1596 END IF;
1597
1598
1599
1600 -- Debug info.
1601 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1602 debug(p_message=>'Validating Resource Id ',
1603 p_prefix =>l_debug_prefix,
1604 p_msg_level=>fnd_log.level_statement);
1605 END IF;
1606
1607 --validate Resource Id value.
1608 --Getting Translated 'Resource Id cannot be null'.
1609 l_trans_message := fnd_message.get_string('JTF','JTF_RS_RESOURCE_ID_NULL');
1610
1611 UPDATE jtf_rs_salesreps_int
1612 SET OPERATION_STATUS = l_status_error,
1613 OPERATION_MESSAGE = l_trans_message,
1614 OPERATION_PROCESS_DATE = SYSDATE
1615 WHERE batch_id = p_batch_id AND
1616 operation = 'CREATE' AND
1617 operation_status IS NULL AND
1618 resource_id IS NULL ;
1619
1620 -- Get the number of rows updated
1621 l_commit_count := SQL%ROWCOUNT ;
1622
1623 -- Debug info.
1624 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1625 DEBUG(p_message=>'Records having Resource Id null in CREATE mode:'||
1626 to_char(l_commit_count),
1627 p_prefix =>l_debug_prefix,
1628 p_msg_level=>fnd_log.level_statement);
1629 END IF;
1630
1631 IF l_commit_count >= 1000 THEN -- Commit if more than 1000 records.
1632 COMMIT;
1633 l_commit_count := 0 ; -- reset the counter
1634 END IF;
1635 -- Debug info.
1636 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1637 debug(p_message=>'Validating Salesperson number ',
1638 p_prefix =>l_debug_prefix,
1639 p_msg_level=>fnd_log.level_statement);
1640 END IF;
1641
1642
1643 --validate Sales Number value.
1644 --Getting Translated 'Salesperson number is required to setup a salesperson.'.
1645 l_trans_message := fnd_message.get_string('JTF','JTF_RS_SALESREP_NUMBER_NULL');
1646
1647 UPDATE jtf_rs_salesreps_int
1648 SET OPERATION_STATUS = l_status_error,
1649 OPERATION_MESSAGE = l_trans_message,
1650 OPERATION_PROCESS_DATE = SYSDATE
1651 WHERE batch_id = p_batch_id AND
1652 operation = 'CREATE' AND
1653 operation_status IS NULL AND
1654 salesrep_number IS NULL ;
1655
1656 -- Get the number of rows updated
1657 l_commit_count := SQL%ROWCOUNT ;
1658
1659 -- Debug info.
1660 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1661 DEBUG(p_message=>'Records having Sales Person Number null in CREATE mode:'||
1662 to_char(l_commit_count),
1663 p_prefix =>l_debug_prefix,
1664 p_msg_level=>fnd_log.level_statement);
1665 END IF;
1666
1667 IF l_commit_count >= 1000 THEN -- Commit if more than 1000 records.
1668 COMMIT;
1669 l_commit_count := 0 ; -- reset the counter
1670 END IF;
1671
1672 -- Open cursor for remaining records after Not Null value validation.
1673 OPEN c_salesrep_int(p_batch_id);
1674 LOOP
1675 FETCH c_salesrep_int INTO
1676 l_interface_id, l_salesrep_id,
1677 l_resource_id, l_sales_credit_type_id, l_status,
1678 l_start_date_active, l_end_date_active, l_salesrep_number,
1679 l_org_id, l_email_address, l_gl_id_rev,
1680 l_gl_id_freight, l_gl_id_rec, l_set_of_books_id,
1681 l_sales_tax_geocode, l_sales_tax_inside_city_limits;
1682 EXIT WHEN c_salesrep_int%NOTFOUND ;
1683
1684 BEGIN
1685 SAVEPOINT do_create_salesrep_loop;
1686
1687 -- Debug info.
1688 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1689 debug(p_message=>'Before Create Salesrep call for Interface ID : '||l_interface_id,
1690 p_prefix =>l_debug_prefix,
1691 p_msg_level=>fnd_log.level_statement);
1692 END IF;
1693
1694 --Call the Public procedure to Create Salesrep.
1695 JTF_RS_SALESREPS_PUB.create_salesrep
1696 (p_api_version => 1.0,
1697 p_init_msg_list => l_init_msg_list,
1698 p_commit => l_commit,
1699 p_resource_id => l_resource_id,
1700 p_sales_credit_type_id => l_sales_credit_type_id,
1701 p_status => l_status,
1702 p_start_date_active => l_start_date_active,
1703 p_end_date_active => l_end_date_active,
1704 p_salesrep_number => l_salesrep_number,
1705 p_org_id => l_org_id,
1706 p_email_address => l_email_address,
1707 p_gl_id_rev => l_gl_id_rev,
1708 p_gl_id_freight => l_gl_id_freight,
1709 p_gl_id_rec => l_gl_id_rec,
1710 p_set_of_books_id => l_set_of_books_id,
1711 p_sales_tax_geocode => l_sales_tax_geocode,
1712 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
1713 x_return_status => l_return_status,
1714 x_msg_count => l_msg_count,
1715 x_msg_data => l_msg_data,
1716 x_salesrep_id => x_salesrep_id
1717 );
1718
1719 -- Debug info.
1720 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1721 debug(p_message=>'After Create Salesrep call Return Status : '||l_return_status,
1722 p_prefix =>l_debug_prefix,
1723 p_msg_level=>fnd_log.level_statement);
1724 END IF;
1725
1726 -- Message data reading logic
1727 IF (l_return_status <> l_status_success AND l_msg_count > 0)
1728 THEN
1729 l_msg_data1 := '';
1730 FOR i IN 1..l_msg_count LOOP
1731 l_msg_data1 := l_msg_data1||fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F')||', ';
1732 END LOOP;
1733
1734 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1735 debug(p_message=>'Message Count:'||l_msg_count,
1736 p_prefix =>l_debug_prefix,
1737 p_msg_level=>fnd_log.level_statement);
1738 END IF;
1739 -- Debug info.
1740 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1741 debug(p_message=>'Message:'||l_msg_data1,
1742 p_prefix =>l_debug_prefix,
1743 p_msg_level=>fnd_log.level_statement);
1744 END IF;
1745 END IF;
1746 ---- End of Message data reading logic
1747
1748
1749 IF l_return_status = l_status_success THEN
1750 UPDATE jtf_rs_salesreps_int
1751 SET operation_status = l_status_success,
1752 operation_process_date = SYSDATE,
1753 salesrep_id = x_salesrep_id
1754 WHERE interface_id = l_interface_id;
1755 ELSE
1756 UPDATE jtf_rs_salesreps_int
1757 SET operation_status = l_return_status,
1758 operation_message = l_msg_data1,
1759 operation_process_date = SYSDATE
1760 WHERE interface_id = l_interface_id;
1761
1762 END IF;
1763
1764 l_commit_count := l_commit_count + 1;
1765 IF MOD(l_commit_count,1000) = 0 THEN
1766 COMMIT;
1767 END IF;
1768
1769 EXCEPTION
1770 WHEN OTHERS THEN
1771 -- When any other unexpected error then try to capture it
1772 l_msg_data1 := SQLERRM;
1773 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1774 -- Debug info.
1775 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1776 debug(p_message=>'Unexpected Error in Create Salesrep loop at '||
1777 'Batch Id ' ||l_batch_id||' Interface Id : '||l_interface_id,
1778 p_prefix =>l_debug_prefix,
1779 p_msg_level=>fnd_log.level_statement);
1780 END IF;
1781 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1782 debug(p_message=>'Error is : '||l_msg_data1,
1783 p_prefix =>l_debug_prefix,
1784 p_msg_level=>fnd_log.level_statement);
1785 END IF;
1786
1787 ROLLBACK TO do_create_salesrep_loop;
1788
1789 UPDATE jtf_rs_salesreps_int
1790 SET operation_status = l_return_status,
1791 operation_message = l_msg_data1,
1792 operation_process_date = SYSDATE
1793 WHERE interface_id = l_interface_id;
1794
1795 l_commit_count := l_commit_count + 1;
1796
1797 END; -- End of BEGIN BLOCK for EACH record in LOOP
1798
1799 -- commit should be outside individual record processing block
1800 IF MOD(l_commit_count,1000) = 0 THEN -- Commit after every 1000 records.
1801 COMMIT;
1802 l_commit_count := 0 ; -- reset the counter
1803 END IF;
1804
1805 END LOOP;
1806 CLOSE c_salesrep_int;
1807
1808 COMMIT;
1809
1810 -- Debug info.
1811 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1812 debug(p_message=>' do_create_salesrep (-)',
1813 p_prefix =>l_debug_prefix,
1814 p_msg_level=>fnd_log.level_statement);
1815 END IF;
1816
1817 EXCEPTION
1818 WHEN OTHERS THEN
1819 -- Debug info.
1820 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1821 debug(p_message=>'Unexpected Error at do_create_salesrep procedure '||SQLERRM,
1822 p_prefix =>l_debug_prefix,
1823 p_msg_level=>fnd_log.level_statement);
1824 END IF;
1825 -- if commit is there after 1000 recs and in update stmt, savepoint will
1826 -- not be established.
1827 -- ROLLBACK TO do_create_salesrep;
1828 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1829 fnd_message.set_token('P_SQLCODE',SQLCODE);
1830 fnd_message.set_token('P_SQLERRM',SQLERRM);
1831 fnd_message.set_token('P_API_NAME', l_api_name);
1832 FND_MSG_PUB.add;
1833 x_return_status := fnd_api.g_ret_sts_unexp_error;
1834 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1835 p_data => x_msg_data);
1836
1837 END do_create_salesrep;
1838
1839 /**
1840 * PROCEDURE do_update_salesrep
1841 *
1842 * DESCRIPTION
1843 * Create Salesrep.
1844 *
1845 * Private PROCEDURES/FUNCTIONS
1846 *
1847 * ARGUMENTS
1848 * IN:
1849 * p_batch_id Batch Id to process records.
1850 *
1851 * NOTES
1852 *
1853 * MODIFICATION HISTORY
1854 * 10-June-2009 Sudhir Gokavarapu Created.
1855 *
1856 */
1857
1858 PROCEDURE do_update_salesrep
1859 (p_batch_id IN NUMBER,
1860 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1861 X_MSG_COUNT OUT NOCOPY NUMBER,
1862 X_MSG_DATA OUT NOCOPY VARCHAR2
1863 ) IS
1864 --Cursor to get Salesrep records for Update mode.
1865 CURSOR c_salesrep_int (p_batch_id IN NUMBER)
1866 IS
1867 SELECT
1868 interface_id,salesrep_id,resource_id,
1869 DECODE(sales_credit_type_id,l_null_num,NULL,NULL,l_miss_num,sales_credit_type_id) sales_credit_type_id,
1870 DECODE(status,l_null_char,NULL,NULL,l_miss_char,status) status,
1871 DECODE(start_date_active,NULL,l_miss_date,l_null_date,NULL,start_date_active) start_date_active,
1872 DECODE(end_date_active ,NULL,l_miss_date,l_null_date,NULL,end_date_active) end_date_active,
1873 DECODE(salesrep_number,l_null_char,NULL,NULL,l_miss_char,salesrep_number) salesrep_number,
1874 DECODE(org_id,l_null_num,NULL,NULL,l_miss_num,org_id) org_id,
1875 DECODE(email_address,l_null_char,NULL,NULL,l_miss_char,email_address) email_address,
1876 DECODE(gl_id_rev,l_null_num,NULL,NULL,l_miss_num,gl_id_rev) gl_id_rev,
1877 DECODE(gl_id_freight,l_null_num,NULL,NULL,l_miss_num,gl_id_freight) gl_id_freight,
1878 DECODE(gl_id_rec,l_null_num,NULL,NULL,l_miss_num,gl_id_rec) gl_id_rec,
1879 DECODE(set_of_books_id,l_null_num,NULL,NULL,l_miss_num,set_of_books_id) set_of_books_id,
1880 DECODE( sales_tax_geocode,l_null_char,NULL,NULL,l_miss_char, sales_tax_geocode) sales_tax_geocode,
1881 DECODE(sales_tax_inside_city_limits,l_null_char,NULL,NULL,l_miss_char,sales_tax_inside_city_limits) sales_tax_inside_city_limits
1882 FROM
1883 jtf_rs_salesreps_int
1884 WHERE
1885 batch_id = p_batch_id AND
1886 operation = 'UPDATE' AND
1887 operation_status IS NULL
1888 ORDER BY interface_id;
1889
1890 l_salesrep_id JTF_RS_SALESREPS.salesrep_id%TYPE;
1891 l_sales_credit_type_id JTF_RS_SALESREPS.sales_credit_type_id%TYPE;
1892 l_status JTF_RS_SALESREPS.status%TYPE;
1893 l_salesrep_number JTF_RS_SALESREPS.salesrep_number%TYPE;
1894 l_org_id JTF_RS_SALESREPS.org_id%TYPE;
1895 l_email_address JTF_RS_SALESREPS.email_address%TYPE;
1896 l_gl_id_rev JTF_RS_SALESREPS.gl_id_rev%TYPE;
1897 l_gl_id_freight JTF_RS_SALESREPS.gl_id_freight%TYPE;
1898 l_gl_id_rec JTF_RS_SALESREPS.gl_id_rec%TYPE;
1899 l_set_of_books_id JTF_RS_SALESREPS.set_of_books_id%TYPE;
1900 l_sales_tax_geocode JTF_RS_SALESREPS.sales_tax_geocode%TYPE;
1901 l_sales_tax_inside_city_limits JTF_RS_SALESREPS.sales_tax_inside_city_limits%TYPE;
1902 l_object_version_number JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE;
1903 l_start_date_active JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE;
1904 l_end_date_active JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE;
1905 l_interface_id JTF_RS_RESOURCE_EXTNS_INT.INTERFACE_ID%TYPE;
1906 l_resource_id JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
1907 l_commit_count NUMBER ;
1908 l_api_name VARCHAR2(30);
1909 l_init_msg_list VARCHAR2(1);
1910 l_commit VARCHAR2(1);
1911 l_api_version CONSTANT NUMBER := 1.0;
1912 l_status_error CONSTANT VARCHAR2(10) := fnd_api.g_ret_sts_error;
1913 l_status_success CONSTANT VARCHAR2(10) := fnd_api.g_ret_sts_success;
1914 l_debug_prefix VARCHAR2(30) := 'RS_UPD:';
1915 l_batch_id NUMBER;
1916 l_return_status VARCHAR2(1);
1917 l_msg_count NUMBER;
1918 l_msg_data VARCHAR2(4000);
1919 l_msg_data1 VARCHAR2(4000);
1920 BEGIN
1921
1922 SAVEPOINT do_update_salesrep;
1923
1924 -- Debug info.
1925 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1926 debug(p_message=>'do_update_salesrep (+)',
1927 p_prefix =>l_debug_prefix,
1928 p_msg_level=>fnd_log.level_statement);
1929 END IF;
1930 -- initialize variables
1931 l_miss_date := FND_API.G_MISS_DATE;
1932 l_init_msg_list := fnd_api.g_true;
1933 l_commit := fnd_api.g_false;
1934 l_commit_count := 0;
1935 l_batch_id := p_batch_id;
1936 l_api_name := 'DO_UPDATE_SALESREP';
1937 x_return_status := fnd_api.g_ret_sts_success;
1938 l_batch_id := p_batch_id;
1939
1940 -- Debug info.
1941 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1942 debug(p_message=>'Batch Id : '||l_batch_id,
1943 p_prefix =>l_debug_prefix,
1944 p_msg_level=>fnd_log.level_statement);
1945 END IF;
1946
1947 -- Debug info.
1948 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1949 debug(p_message=>'Validating SalesRep Id ',
1950 p_prefix =>l_debug_prefix,
1951 p_msg_level=>fnd_log.level_statement);
1952 END IF;
1953
1954 --validate SalesRep Id value.
1955 --Getting Translated 'Salesrep Id cannot be null.'.
1956
1957 l_trans_message := fnd_message.get_string('JTF','JTF_RS_SALESREP_ID_NULL');
1958
1959 UPDATE jtf_rs_salesreps_int
1960 SET OPERATION_STATUS = l_status_error,
1961 OPERATION_MESSAGE = l_trans_message,
1962 OPERATION_PROCESS_DATE = SYSDATE
1963 WHERE batch_id = p_batch_id AND
1964 operation = 'UPDATE' AND
1965 operation_status IS NULL AND
1966 salesrep_id IS NULL ;
1967
1968 -- Get the number of rows updated
1969 l_commit_count := SQL%ROWCOUNT ;
1970
1971 -- Debug info.
1972 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1973 DEBUG(p_message=>'Records having Salesrep Id null in Update mode:'||
1974 to_char(l_commit_count),
1975 p_prefix =>l_debug_prefix,
1976 p_msg_level=>fnd_log.level_statement);
1977 END IF;
1978
1979 IF l_commit_count >= 1000 THEN -- Commit if more than 1000 records.
1980 COMMIT;
1981 l_commit_count := 0 ; -- reset the counter
1982 END IF;
1983
1984 --validate SalesRep Id value.
1985 --Getting Translated 'Salesperson Id is Invalid'.
1986 fnd_message.set_name('JTF', 'JTF_RS_INVALID_SALESREP_ID');
1987 fnd_message.set_token('P_SALESREP_ID','');
1988 fnd_msg_pub.add;
1989
1990 l_trans_message := FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE);
1991
1992 UPDATE jtf_rs_salesreps_int a
1993 SET OPERATION_STATUS = l_status_error,
1994 OPERATION_MESSAGE = l_trans_message,
1995 OPERATION_PROCESS_DATE = SYSDATE
1996 WHERE batch_id = p_batch_id AND
1997 operation = 'UPDATE' AND
1998 operation_status IS NULL AND
1999 NOT EXISTS (SELECT 1 FROM jtf_rs_salesreps B
2000 WHERE A.salesrep_id = B.salesrep_id);
2001
2002 -- Get the number of rows updated
2003 l_commit_count := SQL%ROWCOUNT ;
2004
2005 -- Debug info.
2006 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2007 DEBUG(p_message=>'Records having Invalid Salesrep Id in Update mode:'||
2008 to_char(l_commit_count),
2009 p_prefix =>l_debug_prefix,
2010 p_msg_level=>fnd_log.level_statement);
2011 END IF;
2012
2013 IF l_commit_count >= 1000 THEN -- Commit if more than 1000 records.
2014 COMMIT;
2015 l_commit_count := 0 ; -- reset the counter
2016 END IF;
2017
2018 l_commit_count := 1;--Initial Count
2019
2020 -- Open cursor for remaining records after Not Null value validation.
2021 OPEN c_salesrep_int(p_batch_id);
2022 LOOP
2023 FETCH c_salesrep_int
2024 INTO
2025 l_interface_id, l_salesrep_id,
2026 l_resource_id, l_sales_credit_type_id, l_status,
2027 l_start_date_active, l_end_date_active, l_salesrep_number,
2028 l_org_id, l_email_address, l_gl_id_rev,
2029 l_gl_id_freight, l_gl_id_rec, l_set_of_books_id,
2030 l_sales_tax_geocode, l_sales_tax_inside_city_limits;
2031
2032 EXIT WHEN c_salesrep_int%NOTFOUND ;
2033 BEGIN
2034 SAVEPOINT do_update_salesrep_loop;
2035
2036 SELECT object_version_number,org_id
2037 INTO l_object_version_number,l_org_id
2038 FROM JTF_RS_SALESREPS
2039 WHERE salesrep_id = l_salesrep_id;
2040
2041 -- Debug info.
2042 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2043 debug(p_message=>'Before Update Salesrep call for Interface ID : '
2044 ||l_interface_id||' SalesRep Id : '||l_salesrep_id,
2045 p_prefix =>l_debug_prefix,
2046 p_msg_level=>fnd_log.level_statement);
2047 END IF;
2048
2049 --Call the Public procedure to Update Salesrep.
2050 JTF_RS_SALESREPS_PUB.update_salesrep
2051 (p_api_version => 1.0,
2052 p_init_msg_list => l_init_msg_list,
2053 p_commit => l_commit,
2054 p_sales_credit_type_id => l_sales_credit_type_id,
2055 p_status => l_status,
2056 p_start_date_active => l_start_date_active,
2057 p_end_date_active => l_end_date_active,
2058 p_salesrep_number => l_salesrep_number,
2059 p_org_id => l_org_id,
2060 p_email_address => l_email_address,
2061 p_gl_id_rev => l_gl_id_rev,
2062 p_gl_id_freight => l_gl_id_freight,
2063 p_gl_id_rec => l_gl_id_rec,
2064 p_set_of_books_id => l_set_of_books_id,
2065 p_sales_tax_geocode => l_sales_tax_geocode,
2066 p_sales_tax_inside_city_limits => l_sales_tax_inside_city_limits,
2067 x_return_status => l_return_status,
2068 x_msg_count => l_msg_count,
2069 x_msg_data => l_msg_data,
2070 p_salesrep_id => l_salesrep_id,
2071 p_object_version_number => l_object_version_number
2072 );
2073
2074
2075 -- Debug info.
2076 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2077 debug(p_message=>'After Update Salesrep call Return Status : '||l_return_status,
2078 p_prefix =>l_debug_prefix,
2079 p_msg_level=>fnd_log.level_statement);
2080 END IF;
2081
2082 -- Message data reading logic
2083 IF (l_return_status <> l_status_success AND l_msg_count > 0)
2084 THEN
2085 l_msg_data1 := '';
2086 FOR i IN 1..l_msg_count LOOP
2087 l_msg_data1 := l_msg_data1||fnd_msg_pub.get(p_msg_index => i, p_encoded => 'F')||', ';
2088 END LOOP;
2089
2090 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2091 debug(p_message=>'Message Count:'||l_msg_count,
2092 p_prefix =>l_debug_prefix,
2093 p_msg_level=>fnd_log.level_statement);
2094 END IF;
2095 -- Debug info.
2096 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2097 debug(p_message=>'Message:'||l_msg_data1,
2098 p_prefix =>l_debug_prefix,
2099 p_msg_level=>fnd_log.level_statement);
2100 END IF;
2101 END IF;
2102 ---- End of Message data reading logic
2103
2104 IF l_return_status = l_status_success THEN
2105 UPDATE jtf_rs_salesreps_int
2106 SET operation_status = l_status_success,
2107 operation_process_date = SYSDATE
2108 WHERE interface_id = l_interface_id;
2109 ELSE
2110 UPDATE jtf_rs_salesreps_int
2111 SET operation_status = l_return_status,
2112 operation_message = l_msg_data1,
2113 operation_process_date = SYSDATE
2114 WHERE interface_id = l_interface_id;
2115
2116 END IF;
2117
2118 l_commit_count := l_commit_count + 1;
2119
2120 EXCEPTION
2121 WHEN OTHERS THEN
2122
2123 -- When any other unexpected error then try to capture it
2124 l_msg_data1 := SQLERRM;
2125 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2126
2127 -- Debug info.
2128 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2129 debug(p_message=>'Unexpected Error in Update Salesrep loop at'
2130 ||' Batch Id :'||l_batch_id
2131 ||' Interface Id :'||l_interface_id,
2132 p_prefix =>l_debug_prefix,
2133 p_msg_level=>fnd_log.level_statement);
2134 END IF;
2135
2136 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2137 debug(p_message=>'Error is : '||l_msg_data1,
2138 p_prefix =>l_debug_prefix,
2139 p_msg_level=>fnd_log.level_statement);
2140 END IF;
2141
2142 ROLLBACK TO do_update_salesrep_loop;
2143
2144 UPDATE jtf_rs_salesreps_int
2145 SET operation_status = l_return_status,
2146 operation_message = l_msg_data1,
2147 operation_process_date = SYSDATE
2148 WHERE interface_id = l_interface_id;
2149
2150 l_commit_count := l_commit_count + 1;
2151
2152 END; -- End of BEGIN BLOCK for EACH record in LOOP
2153
2154 -- commit should be outside individual record processing block
2155 IF MOD(l_commit_count,1000) = 0 THEN -- Commit after every 1000 records.
2156 COMMIT;
2157 l_commit_count := 0 ; -- reset the counter
2158 END IF;
2159
2160 END LOOP; --End of Cursor loop.
2161 CLOSE c_salesrep_int;
2162
2163 COMMIT;
2164
2165 -- Debug info.
2166 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2167 debug(p_message =>' do_create_salesrep (-)',
2168 p_prefix =>l_debug_prefix,
2169 p_msg_level=>fnd_log.level_statement);
2170 END IF;
2171 EXCEPTION
2172 WHEN OTHERS THEN
2173 -- Debug info.
2174 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2175 debug(p_message=>'Unexpected Error at do_update_salesrep procedure:'
2176 ||SQLERRM,
2177 p_prefix =>l_debug_prefix,
2178 p_msg_level=>fnd_log.level_statement);
2179 END IF;
2180
2181 -- if commit is there after 1000 recs and in update stmt, savepoint will
2182 -- not be established.
2183 -- ROLLBACK TO do_create_resource;
2184 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2185 fnd_message.set_token('P_SQLCODE',SQLCODE);
2186 fnd_message.set_token('P_SQLERRM',SQLERRM);
2187 fnd_message.set_token('P_API_NAME', l_api_name);
2188 FND_MSG_PUB.add;
2189 x_return_status := fnd_api.g_ret_sts_unexp_error;
2190 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2191 p_data => x_msg_data);
2192
2193 END do_update_salesrep;
2194
2195 /**
2196 * PROCEDURE import_resource
2197 *
2198 * DESCRIPTION
2199 * Create Resource.
2200 *
2201 * Public PROCEDURES/FUNCTIONS
2202 *
2203 * ARGUMENTS
2204 * IN:
2205 * p_batch_id Batch Id to process records.
2206 * OUT:
2207 * x_return_status Get Status.
2208 * x_msg_count Get count of loaded messages.
2209 * x_msg_data Get info of loaded messages.
2210 *
2211 * NOTES
2212 *
2213 * MODIFICATION HISTORY
2214 * 10-June-2009 Sudhir Gokavarapu Created.
2215 *
2216 */
2217
2218 PROCEDURE import_resource
2219 (P_BATCH_ID IN NUMBER,
2220 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2221 X_MSG_COUNT OUT NOCOPY NUMBER,
2222 X_MSG_DATA OUT NOCOPY VARCHAR2
2223 ) IS
2224
2225 l_api_name CONSTANT VARCHAR2(30) := 'IMPORT_RESOURCE';
2226 l_debug_prefix CONSTANT VARCHAR2(30) := 'RS_IMP:';
2227
2228 BEGIN
2229
2230 x_return_status := fnd_api.g_ret_sts_success;
2231
2232 --Call Create Resource and then Update Resource along with Batch Id
2233 /* Call Create Resource */
2234 do_create_resource(p_batch_id => p_batch_id,
2235 x_return_status => x_return_status,
2236 x_msg_count => x_msg_count,
2237 x_msg_data => x_msg_data
2238 );
2239
2240 -- Debug Info
2241 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2242 debug(p_message=> 'Create Resource API Return Values:'||
2243 ' p_batch_id :'||p_batch_id||
2244 ' x_return_status :'||x_return_status||
2245 ' x_msg_count :'||x_msg_count||
2246 ' x_msg_data :'||x_msg_data,
2247 p_prefix =>l_debug_prefix,
2248 p_msg_level=>fnd_log.level_statement);
2249 END IF;
2250
2251 IF (X_RETURN_STATUS = fnd_api.g_ret_sts_success) THEN
2252 /* Call Update Resource */
2253 do_update_resource(p_batch_id => p_batch_id,
2254 x_return_status => x_return_status,
2255 x_msg_count => x_msg_count,
2256 x_msg_data => x_msg_data
2257 );
2258
2259 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2260 debug(p_message=> 'Update Resource API Return Values:'||
2261 ' p_batch_id :'||p_batch_id||
2262 ' x_return_status :'||x_return_status||
2263 ' x_msg_count :'||x_msg_count||
2264 ' x_msg_data :'||x_msg_data,
2265 p_prefix =>l_debug_prefix,
2266 p_msg_level=>fnd_log.level_statement);
2267 END IF;
2268 ELSE
2269 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2270 debug(p_message=> 'Create Resource API Failed. Update Resource not executed.',
2271 p_prefix =>l_debug_prefix,
2272 p_msg_level=>fnd_log.level_statement);
2273 END IF;
2274 END IF ;
2275
2276 EXCEPTION WHEN OTHERS THEN
2277 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2278 fnd_message.set_token('P_SQLCODE',SQLCODE);
2279 fnd_message.set_token('P_SQLERRM',SQLERRM);
2280 fnd_message.set_token('P_API_NAME', l_api_name);
2281 FND_MSG_PUB.add;
2282
2283 x_return_status := fnd_api.g_ret_sts_unexp_error;
2284 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2285 p_data => x_msg_data);
2286
2287 IF fnd_log.LEVEL_UNEXPECTED >=fnd_log.g_current_runtime_level THEN
2288 debug(p_message=> 'Import Resource API raised EXCEPTION:'||
2289 ' p_batch_id :'||p_batch_id||
2290 ' Error :'||SQLERRM,
2291 p_prefix => l_debug_prefix,
2292 p_msg_level=> fnd_log.LEVEL_UNEXPECTED);
2293 END IF;
2294
2295 END import_resource;
2296 /**
2297 * PROCEDURE import_salesreps
2298 *
2299 * DESCRIPTION
2300 * Create Resource.
2301 *
2302 * Public PROCEDURES/FUNCTIONS
2303 *
2304 * ARGUMENTS
2305 * IN:
2306 * p_batch_id Batch Id to process records.
2307 * OUT:
2308 * x_return_status Get Status.
2309 * x_msg_count Get count of loaded messages.
2310 * x_msg_data Get info of loaded messages.
2311 *
2312 * NOTES
2313 *
2314 * MODIFICATION HISTORY
2315 * 10-June-2009 Sudhir Gokavarapu Created.
2316 *
2317 */
2318
2319 PROCEDURE import_salesreps
2320 (P_BATCH_ID IN NUMBER,
2321 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2322 X_MSG_COUNT OUT NOCOPY NUMBER,
2323 X_MSG_DATA OUT NOCOPY VARCHAR2
2324 ) IS
2325 l_api_name CONSTANT VARCHAR2(30) := 'IMPORT_SALESREP';
2326 l_debug_prefix CONSTANT VARCHAR2(30) := 'SR_IMP:';
2327
2328 BEGIN
2329
2330
2331 x_return_status := fnd_api.g_ret_sts_success;
2332
2333 --Call Create Salesrep and then Update Salesrep along with Batch Id
2334 /* Call Create Salesrep */
2335 do_create_salesrep(p_batch_id => p_batch_id,
2336 x_return_status => x_return_status,
2337 x_msg_count => x_msg_count,
2338 x_msg_data => x_msg_data
2339 );
2340
2341 -- Debug Info
2342 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2343 debug(p_message=> 'Create Salesrep API Return Values:'||
2344 ' p_batch_id :'||p_batch_id||
2345 ' x_return_status :'||x_return_status||
2346 ' x_msg_count :'||x_msg_count||
2347 ' x_msg_data :'||x_msg_data,
2348 p_prefix =>l_debug_prefix,
2349 p_msg_level=>fnd_log.level_statement);
2350 END IF;
2351
2352 IF (X_RETURN_STATUS = fnd_api.g_ret_sts_success) THEN
2353 /* Call Update Salesrep */
2354 do_update_salesrep(p_batch_id => p_batch_id,
2355 x_return_status => x_return_status,
2356 x_msg_count => x_msg_count,
2357 x_msg_data => x_msg_data
2358 );
2359
2360 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2361 debug(p_message=> 'Update Salesrep API Return Values:'||
2362 ' p_batch_id :'||p_batch_id||
2363 ' x_return_status :'||x_return_status||
2364 ' x_msg_count :'||x_msg_count||
2365 ' x_msg_data :'||x_msg_data,
2366 p_prefix =>l_debug_prefix,
2367 p_msg_level=>fnd_log.level_statement);
2368 END IF;
2369 ELSE
2370 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
2371 debug(p_message=> 'Create Salesrep API Failed. Update Salesrep not executed.',
2372 p_prefix =>l_debug_prefix,
2373 p_msg_level=>fnd_log.level_statement);
2374 END IF;
2375 END IF ;
2376
2377 EXCEPTION WHEN OTHERS THEN
2378 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
2379 fnd_message.set_token('P_SQLCODE',SQLCODE);
2380 fnd_message.set_token('P_SQLERRM',SQLERRM);
2381 fnd_message.set_token('P_API_NAME', l_api_name);
2382 FND_MSG_PUB.add;
2383
2384 x_return_status := fnd_api.g_ret_sts_unexp_error;
2385 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
2386 p_data => x_msg_data);
2387
2388 IF fnd_log.LEVEL_UNEXPECTED >=fnd_log.g_current_runtime_level THEN
2389 debug(p_message=> 'Import Salesrep API raised EXCEPTION:'||
2390 ' p_batch_id :'||p_batch_id||
2391 ' Error :'||SQLERRM,
2392 p_prefix => l_debug_prefix,
2393 p_msg_level=> fnd_log.LEVEL_UNEXPECTED);
2394 END IF;
2395
2396
2397 END import_salesreps;
2398
2399
2400 END jtf_rs_interface_pvt;