[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_RESOURCE_PVT
Source
1 PACKAGE BODY jtf_rs_resource_pvt AS
2 /* $Header: jtfrsvrb.pls 120.3 2006/02/07 18:02:45 baianand ship $ */
3
4 /*****************************************************************************************
5 This package body defines the procedures for managing resources.
6 Its main procedures are as following:
7 Create Resource
8 Update Resource
9 These procedures do the business validations and then call the appropriate
10 table handlers to do the actual inserts and updates.
11 ******************************************************************************************/
12
13 /* Package variables. */
14
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JTF_RS_RESOURCE_PVT';
16 PROCEDURE validate_party_address(p_source_id in number,
17 p_address_id in number,
18 p_action in varchar2,
19 p_found out NOCOPY boolean,
20 p_return_status out NOCOPY varchar);
21
22 PROCEDURE validate_party_address(p_source_id in number,
23 p_address_id in number,
24 p_action in varchar2,
25 p_found out NOCOPY boolean,
26 p_return_status out NOCOPY varchar)
27 IS
28
29 cursor address_cur(l_party_id number)
30 is
31 select party_site_id
32 from hz_party_sites
33 where party_id = l_party_id
34 and identifying_address_flag = 'Y'
35 and status = 'A';
36
37 l_party_type VARCHAR2(2000);
38 l_address_id NUMBER;
39 /* Moved the initial assignment of below variable to inside begin */
40 l_api_name VARCHAR2(100);
41 BEGIN
42
43 l_api_name := 'VALIDATE_PARTY_ADDRESS';
44
45 p_return_status := fnd_api.g_ret_sts_success;
46 p_found := true;
47 open address_cur(p_source_id);
48 fetch address_cur into l_address_id;
49 close address_cur;
50
51 if(nvl(p_address_id, fnd_api.g_miss_num) <> nvl(l_address_id, fnd_api.g_miss_num))
52 then
53 p_found := false;
54 end if;
55
56 EXCEPTION
57 WHEN OTHERS
58 THEN
59 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
60 fnd_message.set_token('P_SQLCODE',SQLCODE);
61 fnd_message.set_token('P_SQLERRM',SQLERRM);
62 fnd_message.set_token('P_API_NAME', l_api_name);
63 FND_MSG_PUB.add;
64 p_return_status := fnd_api.g_ret_sts_unexp_error;
65
66 END;
67
68 /* Procedure to create the resource based on input values passed by calling routines. */
69
70 PROCEDURE create_resource
71 (P_API_VERSION IN NUMBER,
72 P_INIT_MSG_LIST IN VARCHAR2,
73 P_COMMIT IN VARCHAR2,
74 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
75 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
76 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
77 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE,
78 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
79 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
80 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
81 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
82 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
83 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
84 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
85 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
86 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
87 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
88 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
89 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
90 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
91 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
92 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
93 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
94 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
95 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
96 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
97 P_TRANSACTION_NUMBER IN JTF_RS_RESOURCE_EXTNS.TRANSACTION_NUMBER%TYPE,
98 --P_LOCATION IN MDSYS.SDO_GEOMETRY,
99 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
100 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
101 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
102 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
103 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
104 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
105 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
106 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
107 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
108 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
109 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
110 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
111 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
112 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
113 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
114 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE,
115 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
116 X_MSG_COUNT OUT NOCOPY NUMBER,
117 X_MSG_DATA OUT NOCOPY VARCHAR2,
118 X_RESOURCE_ID OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
119 X_RESOURCE_NUMBER OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE
120 ) IS
121
122 l_api_version CONSTANT NUMBER := 1.0;
123 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE';
124 /* Moved the initial assignment of below variables to inside begin */
125 l_category jtf_rs_resource_extns.category%TYPE;
126 l_source_id jtf_rs_resource_extns.source_id%TYPE;
127 l_address_id jtf_rs_resource_extns.address_id%TYPE;
128 l_contact_id jtf_rs_resource_extns.contact_id%TYPE;
129 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%TYPE;
130 -- added trunc in both dates feb 6 2002
131 l_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
132 l_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
133 l_time_zone jtf_rs_resource_extns.time_zone%TYPE;
134 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%TYPE;
135 l_primary_language jtf_rs_resource_extns.primary_language%TYPE;
136 l_secondary_language jtf_rs_resource_extns.secondary_language%TYPE;
137 l_support_site_id jtf_rs_resource_extns.support_site_id%TYPE;
138 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%TYPE;
139 l_server_group_id jtf_rs_resource_extns.server_group_id%TYPE;
140 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%TYPE;
141 l_cost_center jtf_rs_resource_extns.cost_center%TYPE;
142 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%TYPE;
143 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%TYPE;
144 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%TYPE;
145 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%TYPE;
146 l_hold_payment jtf_rs_resource_extns.hold_payment%TYPE;
147 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%TYPE;
148 l_user_id jtf_rs_resource_extns.user_id%TYPE;
149 l_transaction_number jtf_rs_resource_extns.transaction_number%TYPE;
150 --l_location MDSYS.SDO_GEOMETRY := p_location;
151
152 l_check_char VARCHAR2(1);
153 l_check_dup_id VARCHAR2(1);
154 l_rowid ROWID;
155 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
156 l_resource_number jtf_rs_resource_extns.resource_number%TYPE;
157 l_bind_data_id NUMBER;
158
159 CURSOR c_jtf_rs_resource_extns( l_rowid IN ROWID ) IS
160 SELECT 'Y'
161 FROM jtf_rs_resource_extns
162 WHERE ROWID = l_rowid;
163
164 CURSOR c_dup_resource_id (l_resource_id IN jtf_rs_resource_extns.resource_id%type) IS
165 SELECT 'X'
166 FROM jtf_rs_resource_extns
167 WHERE resource_id = l_resource_id;
168
169 l_value VARCHAR2(100);
170 l_address_ret_status varchar2(10);
171 l_address_found boolean := true;
172
173 BEGIN
174
175 l_category := upper(p_category);
176 l_source_id := p_source_id;
177 l_address_id := p_address_id;
178 l_contact_id := p_contact_id;
179 l_managing_emp_id := p_managing_emp_id;
180 l_start_date_active := trunc(p_start_date_active);
181 l_end_date_active := trunc(p_end_date_active);
182 l_time_zone := p_time_zone;
183 l_cost_per_hr := p_cost_per_hr;
184 l_primary_language := p_primary_language;
185 l_secondary_language := p_secondary_language;
186 l_support_site_id := p_support_site_id;
187 l_ies_agent_login := p_ies_agent_login;
188 l_server_group_id := p_server_group_id;
189 l_assigned_to_group_id := p_assigned_to_group_id;
190 l_cost_center := p_cost_center;
191 l_charge_to_cost_center := p_charge_to_cost_center;
192 l_comp_currency_code := p_comp_currency_code;
193 l_commissionable_flag := p_commissionable_flag;
194 l_hold_reason_code := p_hold_reason_code;
195 l_hold_payment := p_hold_payment;
196 l_comp_service_team_id := p_comp_service_team_id;
197 l_user_id := p_user_id;
198 l_transaction_number := p_transaction_number;
199
200 SAVEPOINT create_resource_pvt;
201
202 x_return_status := fnd_api.g_ret_sts_success;
203
204 -- DBMS_OUTPUT.put_line(' Started Create Resource Pvt ');
205
206
207
208 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
209
210 RAISE fnd_api.g_exc_unexpected_error;
211
212 END IF;
213
214
215 IF fnd_api.to_boolean(p_init_msg_list) THEN
216
217 fnd_msg_pub.initialize;
218
219 END IF;
220
221
222 /* Make the pre processing call to the user hooks */
223
224 /* Pre Call to the Customer Type User Hook */
225
226 IF jtf_resource_utl.ok_to_execute(
227 'JTF_RS_RESOURCE_PVT',
228 'CREATE_RESOURCE',
229 'B',
230 'C')
231 THEN
232 IF jtf_usr_hks.ok_to_execute(
233 'JTF_RS_RESOURCE_PVT',
234 'CREATE_RESOURCE',
235 'B',
236 'C')
237 THEN
238
239 jtf_rs_resource_cuhk.create_resource_pre(
240 p_category => l_category,
241 p_source_id => l_source_id,
242 p_address_id => l_address_id,
243 p_contact_id => l_contact_id,
244 p_managing_emp_id => l_managing_emp_id,
245 p_start_date_active => l_start_date_active,
246 p_end_date_active => l_end_date_active,
247 p_time_zone => l_time_zone,
248 p_cost_per_hr => l_cost_per_hr,
249 p_primary_language => l_primary_language,
250 p_secondary_language => l_secondary_language,
251 p_support_site_id => l_support_site_id,
252 p_ies_agent_login => l_ies_agent_login,
253 p_server_group_id => l_server_group_id,
254 p_assigned_to_group_id => l_assigned_to_group_id,
255 p_cost_center => l_cost_center,
256 p_charge_to_cost_center=> l_charge_to_cost_center,
257 p_comp_currency_code => l_comp_currency_code,
258 p_commissionable_flag => l_commissionable_flag,
259 p_hold_reason_code => l_hold_reason_code,
260 p_hold_payment => l_hold_payment,
261 p_comp_service_team_id => l_comp_service_team_id,
262 p_user_id => l_user_id,
263 --p_location => l_location,
264 x_return_status => x_return_status);
265
266 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
267
268 -- dbms_output.put_line('Returned Error status from the Pre Customer User Hook');
269
270
271 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
272 fnd_msg_pub.add;
273
274 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
275 RAISE FND_API.G_EXC_ERROR;
276 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
278 END IF;
279
280 END IF;
281
282 END IF;
283 END IF;
284
285
286 /* Pre Call to the Vertical Type User Hook */
287
288 IF jtf_resource_utl.ok_to_execute(
289 'JTF_RS_RESOURCE_PVT',
290 'CREATE_RESOURCE',
291 'B',
292 'V')
293 THEN
294 IF jtf_usr_hks.ok_to_execute(
295 'JTF_RS_RESOURCE_PVT',
296 'CREATE_RESOURCE',
297 'B',
298 'V')
299 THEN
300
301 jtf_rs_resource_vuhk.create_resource_pre(
302 p_category => l_category,
303 p_source_id => l_source_id,
304 p_address_id => l_address_id,
305 p_contact_id => l_contact_id,
306 p_managing_emp_id => l_managing_emp_id,
307 p_start_date_active => l_start_date_active,
308 p_end_date_active => l_end_date_active,
309 p_time_zone => l_time_zone,
310 p_cost_per_hr => l_cost_per_hr,
311 p_primary_language => l_primary_language,
312 p_secondary_language => l_secondary_language,
313 p_support_site_id => l_support_site_id,
314 p_ies_agent_login => l_ies_agent_login,
315 p_server_group_id => l_server_group_id,
316 p_assigned_to_group_id => l_assigned_to_group_id,
317 p_cost_center => l_cost_center,
318 p_charge_to_cost_center => l_charge_to_cost_center,
319 p_comp_currency_code => l_comp_currency_code,
320 p_commissionable_flag => l_commissionable_flag,
321 p_hold_reason_code => l_hold_reason_code,
322 p_hold_payment => l_hold_payment,
323 p_comp_service_team_id => l_comp_service_team_id,
324 p_user_id => l_user_id,
325 --p_location => l_location,
326 x_return_status => x_return_status);
327
328 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
329
330 -- dbms_output.put_line('Returned Error status from the Pre Vertical User Hook');
331
332
333 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
334 fnd_msg_pub.add;
335
336 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
337 RAISE FND_API.G_EXC_ERROR;
338 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
340 END IF;
341
342
343 END IF;
344
345 END IF;
346 END IF;
347
348
349 /* Pre Call to the Internal Type User Hook */
350
351 IF jtf_resource_utl.ok_to_execute(
352 'JTF_RS_RESOURCE_PVT',
353 'CREATE_RESOURCE',
354 'B',
355 'I')
356 THEN
357 IF jtf_usr_hks.ok_to_execute(
358 'JTF_RS_RESOURCE_PVT',
359 'CREATE_RESOURCE',
360 'B',
361 'I')
362 THEN
363
364 jtf_rs_resource_iuhk.create_resource_pre(
365 p_category => l_category,
366 p_source_id => l_source_id,
367 p_address_id => l_address_id,
368 p_contact_id => l_contact_id,
369 p_managing_emp_id => l_managing_emp_id,
370 p_start_date_active => l_start_date_active,
371 p_end_date_active => l_end_date_active,
372 p_time_zone => l_time_zone,
373 p_cost_per_hr => l_cost_per_hr,
374 p_primary_language => l_primary_language,
375 p_secondary_language => l_secondary_language,
376 p_support_site_id => l_support_site_id,
377 p_ies_agent_login => l_ies_agent_login,
378 p_server_group_id => l_server_group_id,
379 p_assigned_to_group_id => l_assigned_to_group_id,
380 p_cost_center => l_cost_center,
381 p_charge_to_cost_center => l_charge_to_cost_center,
382 p_comp_currency_code => l_comp_currency_code,
383 p_commissionable_flag => l_commissionable_flag,
384 p_hold_reason_code => l_hold_reason_code,
385 p_hold_payment => l_hold_payment,
386 p_comp_service_team_id => l_comp_service_team_id,
387 p_user_id => l_user_id,
388 --p_location => l_location,
389 x_return_status => x_return_status);
390
391 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
392
393
394 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
395 fnd_msg_pub.add;
396
397 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
398 RAISE FND_API.G_EXC_ERROR;
399 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
400 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401 END IF;
402
403 END IF;
404
405 END IF;
406 END IF;
407
408
409
410 /* Validate the Input Dates */
411
412 jtf_resource_utl.validate_input_dates(
413 p_start_date_active => l_start_date_active,
414 p_end_date_active => l_end_date_active,
415 x_return_status => x_return_status
416 );
417
418 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
419
420 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
421 RAISE FND_API.G_EXC_ERROR;
422 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
424 END IF;
425
426 END IF;
427
428 /* Validate for address id in case of PARTNER and PARTY */
429 if(p_category IN ('PARTNER', 'PARTY'))
430 then
431 if (p_address_id is NOT NULL) then
432 validate_party_address(p_source_id => p_source_id,
433 p_address_id => p_address_id,
434 p_action => 'I',
435 p_found => l_address_found,
436 p_return_status => l_address_ret_status);
437
438 if(l_address_ret_status <> fnd_api.g_ret_sts_success)
439 then
440 IF L_ADDRESS_RET_STATUS = FND_API.G_RET_STS_ERROR THEN
441 RAISE FND_API.G_EXC_ERROR;
442 ELSIF L_ADDRESS_RET_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
444 END IF;
445
446 end if;
447
448 if not(l_address_found)
449 then
450 fnd_message.set_name('JTF', 'JTF_RS_NOT_PRIMARY_ADDR');
451 fnd_msg_pub.add;
452 RAISE fnd_api.g_exc_error;
453
454 end if;
455 end if;
456 end if; -- end of partner address check
457
458 /* This portion of the code was modified to accomodate the calls to Migration API */
459 /* Check if the Global Variable Flag for Resource ID is Y or N */
460
461 -- dbms_output.put_line ('Before checkin the Global flag in PVT API');
462
463 IF (G_RS_ID_PVT_FLAG = 'Y') OR (G_RS_ID_PVT_FLAG = 'N' AND JTF_RS_RESOURCE_PUB.G_RESOURCE_ID IS NULL) THEN
464
465 /* Get the next value of the Resource_id from the sequence. */
466
467 LOOP
468 SELECT jtf_rs_resource_extns_s.nextval
469 INTO l_resource_id
470 FROM dual;
471 --dbms_output.put_line ('After Select - Resource ID ' || l_resource_id);
472 OPEN c_dup_resource_id (l_resource_id);
473 FETCH c_dup_resource_id INTO l_check_dup_id;
474 EXIT WHEN c_dup_resource_id%NOTFOUND;
475 CLOSE c_dup_resource_id;
476 END LOOP;
477 CLOSE c_dup_resource_id;
478 ELSE
479 l_resource_id := JTF_RS_RESOURCE_PUB.G_RESOURCE_ID;
480 END IF;
481
482 /* Get the next value of the Resource_number from the sequence. */
483
484 SELECT jtf_rs_resource_number_s.nextval
485 INTO l_resource_number
486 FROM dual;
487
488 /* Make a call to the Resource Audit API */
489
490 jtf_rs_resource_extns_aud_pvt.insert_resource
491 (P_API_VERSION => 1,
492 P_INIT_MSG_LIST => fnd_api.g_false,
493 P_COMMIT => fnd_api.g_false,
494 P_RESOURCE_ID => l_resource_id,
495 P_RESOURCE_NUMBER => l_resource_number,
496 P_CATEGORY => l_category,
497 P_SOURCE_ID => l_source_id,
498 P_ADDRESS_ID => l_address_id,
499 P_CONTACT_ID => l_contact_id,
500 P_MANAGING_EMP_ID => l_managing_emp_id,
501 P_START_DATE_ACTIVE => l_start_date_active,
502 P_END_DATE_ACTIVE => l_end_date_active,
503 P_TIME_ZONE => l_time_zone,
504 P_COST_PER_HR => l_cost_per_hr,
505 P_PRIMARY_LANGUAGE => l_primary_language,
506 P_SECONDARY_LANGUAGE => l_secondary_language,
507 P_SUPPORT_SITE_ID => l_support_site_id,
508 P_IES_AGENT_LOGIN => l_ies_agent_login,
509 P_SERVER_GROUP_ID => l_server_group_id,
510 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
511 P_COST_CENTER => l_cost_center,
512 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
513 P_COMP_CURRENCY_CODE => l_comp_currency_code,
514 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
515 P_HOLD_REASON_CODE => l_hold_reason_code,
516 P_HOLD_PAYMENT => l_hold_payment,
517 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
518 P_USER_ID => l_user_id,
519 P_TRANSACTION_NUMBER => l_transaction_number,
520 --P_LOCATION => l_location,
521 P_OBJECT_VERSION_NUMBER => 1,
522 X_RETURN_STATUS => x_return_status,
523 X_MSG_COUNT => x_msg_count,
524 X_MSG_DATA => x_msg_data
525 );
526
527 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
528 -- dbms_output.put_line('Failed status from call to audit procedure');
529 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
530 RAISE FND_API.G_EXC_ERROR;
531 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
533 END IF;
534
535 END IF;
536
537 /* Insert the row into the table by calling the table handler. */
538
539 jtf_rs_resource_extns_pkg.insert_row(
540 x_rowid => l_rowid,
541 x_resource_id => l_resource_id,
542 x_category => l_category,
543 x_resource_number => l_resource_number,
544 x_source_id => l_source_id,
545 x_address_id => l_address_id,
546 x_contact_id => l_contact_id,
547 x_managing_employee_id => l_managing_emp_id,
548 x_start_date_active => l_start_date_active,
549 x_end_date_active => l_end_date_active,
550 x_time_zone => l_time_zone,
551 x_cost_per_hr => l_cost_per_hr,
552 x_primary_language => l_primary_language,
553 x_secondary_language => l_secondary_language,
554 x_support_site_id => l_support_site_id,
555 x_ies_agent_login => l_ies_agent_login,
556 x_server_group_id => l_server_group_id,
557 x_assigned_to_group_id => l_assigned_to_group_id,
558 x_cost_center => l_cost_center,
559 x_charge_to_cost_center => l_charge_to_cost_center,
560 x_compensation_currency_code => l_comp_currency_code,
561 x_commissionable_flag => l_commissionable_flag,
562 x_hold_reason_code => l_hold_reason_code,
563 x_hold_payment => l_hold_payment,
564 x_comp_service_team_id => l_comp_service_team_id,
565 x_user_id => l_user_id,
566 --x_location => l_location,
567 x_transaction_number => l_transaction_number,
568 x_attribute1 => p_attribute1,
569 x_attribute2 => p_attribute2,
570 x_attribute3 => p_attribute3,
571 x_attribute4 => p_attribute4,
572 x_attribute5 => p_attribute5,
573 x_attribute6 => p_attribute6,
574 x_attribute7 => p_attribute7,
575 x_attribute8 => p_attribute8,
576 x_attribute9 => p_attribute9,
577 x_attribute10 => p_attribute10,
578 x_attribute11 => p_attribute11,
579 x_attribute12 => p_attribute12,
580 x_attribute13 => p_attribute13,
581 x_attribute14 => p_attribute14,
582 x_attribute15 => p_attribute15,
583 x_attribute_category => p_attribute_category,
584 x_creation_date => SYSDATE,
585 x_created_by => jtf_resource_utl.created_by,
586 x_last_update_date => SYSDATE,
587 x_last_updated_by => jtf_resource_utl.updated_by,
588 x_last_update_login => jtf_resource_utl.login_id
589 );
590
591 -- dbms_output.put_line('After Insert Procedure');
592 OPEN c_jtf_rs_resource_extns(l_rowid);
593 FETCH c_jtf_rs_resource_extns INTO l_check_char;
594
595 IF c_jtf_rs_resource_extns%NOTFOUND THEN
596 -- dbms_output.put_line('Error in Table Handler');
597 IF c_jtf_rs_resource_extns%ISOPEN THEN
598 CLOSE c_jtf_rs_resource_extns;
599 END IF;
600
601 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
602 fnd_msg_pub.add;
603 RAISE fnd_api.g_exc_error;
604
605 ELSE
606
607 -- dbms_output.put_line('Resource Successfully Created');
608 x_resource_id := l_resource_id;
609 x_resource_number := l_resource_number;
610
611 END IF;
612
613
614 /* Close the cursors */
615
616 IF c_jtf_rs_resource_extns%ISOPEN THEN
617 CLOSE c_jtf_rs_resource_extns;
618 END IF;
619
620
621 /* Make the post processing call to the user hooks */
622
623 /* Post Call to the Customer Type User Hook */
624
625 IF jtf_usr_hks.ok_to_execute(
626 'JTF_RS_RESOURCE_PVT',
627 'CREATE_RESOURCE',
628 'A',
629 'C')
630 THEN
631
632 jtf_rs_resource_cuhk.create_resource_post(
633 p_resource_id => l_resource_id,
634 p_category => l_category,
635 p_source_id => l_source_id,
636 p_address_id => l_address_id,
637 p_contact_id => l_contact_id,
638 p_managing_emp_id => l_managing_emp_id,
639 p_start_date_active => l_start_date_active,
640 p_end_date_active => l_end_date_active,
641 p_time_zone => l_time_zone,
642 p_cost_per_hr => l_cost_per_hr,
643 p_primary_language => l_primary_language,
644 p_secondary_language => l_secondary_language,
645 p_support_site_id => l_support_site_id,
646 p_ies_agent_login => l_ies_agent_login,
647 p_server_group_id => l_server_group_id,
648 p_assigned_to_group_id => l_assigned_to_group_id,
649 p_cost_center => l_cost_center,
650 p_charge_to_cost_center => l_charge_to_cost_center,
651 p_comp_currency_code => l_comp_currency_code,
652 p_commissionable_flag => l_commissionable_flag,
653 p_hold_reason_code => l_hold_reason_code,
654 p_hold_payment => l_hold_payment,
655 p_comp_service_team_id => l_comp_service_team_id,
656 p_user_id => l_user_id,
657 --p_location => l_location,
658 x_return_status => x_return_status);
659
660 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
661
662 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
663
664
665 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
666 fnd_msg_pub.add;
667
668 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
669 RAISE FND_API.G_EXC_ERROR;
670 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
671 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
672 END IF;
673
674 END IF;
675
676 END IF;
677
678
679 /* Post Call to the Vertical Type User Hook */
680
681 IF jtf_usr_hks.ok_to_execute(
682 'JTF_RS_RESOURCE_PVT',
683 'CREATE_RESOURCE',
684 'A',
685 'V')
686 THEN
687
688 jtf_rs_resource_vuhk.create_resource_post(
689 p_resource_id => l_resource_id,
690 p_category => l_category,
691 p_source_id => l_source_id,
692 p_address_id => l_address_id,
693 p_contact_id => l_contact_id,
694 p_managing_emp_id => l_managing_emp_id,
695 p_start_date_active => l_start_date_active,
696 p_end_date_active => l_end_date_active,
697 p_time_zone => l_time_zone,
698 p_cost_per_hr => l_cost_per_hr,
699 p_primary_language => l_primary_language,
700 p_secondary_language => l_secondary_language,
701 p_support_site_id => l_support_site_id,
702 p_ies_agent_login => l_ies_agent_login,
703 p_server_group_id => l_server_group_id,
704 p_assigned_to_group_id => l_assigned_to_group_id,
705 p_cost_center => l_cost_center,
706 p_charge_to_cost_center => l_charge_to_cost_center,
707 p_comp_currency_code => l_comp_currency_code,
708 p_commissionable_flag => l_commissionable_flag,
709 p_hold_reason_code => l_hold_reason_code,
710 p_hold_payment => l_hold_payment,
711 p_comp_service_team_id => l_comp_service_team_id,
712 p_user_id => l_user_id,
713 --p_location => l_location,
714 x_return_status => x_return_status);
715
716 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
717
718 -- dbms_output.put_line('Returned Error status from the Post Vertical User Hook');
719
720
721 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
722 fnd_msg_pub.add;
723
724 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
725 RAISE FND_API.G_EXC_ERROR;
726 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
728 END IF;
729
730 END IF;
731
732 END IF;
733
734
735 /* Post Call to the Internal Type User Hook */
736
737 IF jtf_resource_utl.ok_to_execute(
738 'JTF_RS_RESOURCE_PVT',
739 'CREATE_RESOURCE',
740 'A',
741 'I')
742 THEN
743 IF jtf_usr_hks.ok_to_execute(
744 'JTF_RS_RESOURCE_PVT',
745 'CREATE_RESOURCE',
746 'A',
747 'I')
748 THEN
749
750 jtf_rs_resource_iuhk.create_resource_post(
751 p_resource_id => l_resource_id,
752 p_category => l_category,
753 p_source_id => l_source_id,
754 p_address_id => l_address_id,
755 p_contact_id => l_contact_id,
756 p_managing_emp_id => l_managing_emp_id,
757 p_start_date_active => l_start_date_active,
758 p_end_date_active => l_end_date_active,
759 p_time_zone => l_time_zone,
760 p_cost_per_hr => l_cost_per_hr,
761 p_primary_language => l_primary_language,
762 p_secondary_language => l_secondary_language,
763 p_support_site_id => l_support_site_id,
764 p_ies_agent_login => l_ies_agent_login,
765 p_server_group_id => l_server_group_id,
766 p_assigned_to_group_id => l_assigned_to_group_id,
767 p_cost_center => l_cost_center,
768 p_charge_to_cost_center => l_charge_to_cost_center,
769 p_comp_currency_code => l_comp_currency_code,
770 p_commissionable_flag => l_commissionable_flag,
771 p_hold_reason_code => l_hold_reason_code,
772 p_hold_payment => l_hold_payment,
773 p_comp_service_team_id => l_comp_service_team_id,
774 p_user_id => l_user_id,
775 -- p_location => l_location,
776 x_return_status => x_return_status);
777
778 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
779
780
781 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
782 fnd_msg_pub.add;
783
784 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
785 RAISE FND_API.G_EXC_ERROR;
786 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
788 END IF;
789
790 END IF;
791
792 END IF;
793 END IF;
794
795
796 /* Standard call for Message Generation */
797
798 IF jtf_resource_utl.ok_to_execute(
799 'JTF_RS_RESOURCE_PVT',
800 'CREATE_RESOURCE',
801 'M',
802 'M')
803 THEN
804 IF jtf_usr_hks.ok_to_execute(
805 'JTF_RS_RESOURCE_PVT',
806 'CREATE_RESOURCE',
807 'M',
808 'M')
809 THEN
810
811 IF (jtf_rs_resource_cuhk.ok_to_generate_msg(
812 p_resource_id => l_resource_id,
813 x_return_status => x_return_status) )
814 THEN
815
816 /* Get the bind data id for the Business Object Instance */
817
818 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
819
820
821 /* Set bind values for the bind variables in the Business Object SQL */
822
823 jtf_usr_hks.load_bind_data(l_bind_data_id, 'resource_id', l_resource_id, 'S', 'N');
824
825
826 /* Call the message generation API */
827
828 jtf_usr_hks.generate_message(
829 p_prod_code => 'JTF',
830 p_bus_obj_code => 'RS_RES',
831 p_action_code => 'I',
832 p_bind_data_id => l_bind_data_id,
833 x_return_code => x_return_status);
834
835
836 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
837
838 -- dbms_output.put_line('Returned Error status from the Message Generation API');
839
840
841 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
842 fnd_msg_pub.add;
843
844 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
845 RAISE FND_API.G_EXC_ERROR;
846 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
847 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
848 END IF;
849
850 END IF;
851
852 END IF;
853
854 END IF;
855 END IF;
856
857
858 IF fnd_api.to_boolean(p_commit) THEN
859
860 COMMIT WORK;
861
862 END IF;
863
864 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
865
866
867 EXCEPTION
868
869 WHEN fnd_api.g_exc_error THEN
870 ROLLBACK TO create_resource_pvt;
871 x_return_status := fnd_api.g_ret_sts_error;
872 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
873 p_data => x_msg_data);
874 WHEN fnd_api.g_exc_unexpected_error THEN
875 ROLLBACK TO create_resource_pvt;
876 x_return_status := fnd_api.g_ret_sts_unexp_error;
877 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
878 p_data => x_msg_data);
879 WHEN OTHERS THEN
880 ROLLBACK TO create_resource_pvt;
881 -- The below lines removed as a part of fixing GSCC errors in R12 for jtfrspub.pls
882 -- IF NOT(jtf_resource_utl.check_access(l_value))
883 -- THEN
884 -- IF(l_value = 'XMLGEN')
885 -- THEN
886 -- fnd_message.set_name ('JTF', 'JTF_RS_XMLGEN_ERR');
887 -- FND_MSG_PUB.add;
888 -- ELSIF(l_value = 'JTF_USR_HKS')
889 -- THEN
890 -- fnd_message.set_name ('JTF', 'JTF_RS_JUHK_ERR');
891 -- FND_MSG_PUB.add;
892 -- END IF;
893 -- ELSE
894 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
895 fnd_message.set_token('P_SQLCODE',SQLCODE);
896 fnd_message.set_token('P_SQLERRM',SQLERRM);
897 fnd_message.set_token('P_API_NAME', l_api_name);
898 FND_MSG_PUB.add;
899 -- END IF;
900 x_return_status := fnd_api.g_ret_sts_unexp_error;
901 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
902 p_data => x_msg_data);
903
904 END create_resource;
905
906 /* Create Resource Procedure Overloaded, for Resource Synchronization Purposes */
907
908 PROCEDURE create_resource
909 (P_API_VERSION IN NUMBER,
910 P_INIT_MSG_LIST IN VARCHAR2,
911 P_COMMIT IN VARCHAR2,
912 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
913 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
914 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
915 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE,
916 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
917 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
918 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
919 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
920 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
921 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
922 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
923 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
924 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
925 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
926 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
927 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
928 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
929 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
930 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
931 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
932 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
933 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
934 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
935 P_TRANSACTION_NUMBER IN JTF_RS_RESOURCE_EXTNS.TRANSACTION_NUMBER%TYPE,
936 --P_LOCATION IN MDSYS.SDO_GEOMETRY,
937 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
938 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
939 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
940 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
941 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
942 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
943 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
944 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
945 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
946 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
947 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
948 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
949 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
950 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
951 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
952 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE,
953 P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE,
954 P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE ,
955 P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE,
956 P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE,
957 P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE,
958 P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE,
959 P_SOURCE_ORG_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_ID%TYPE,
960 P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE,
961 P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE,
962 P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE,
963 P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE,
964 P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE,
965 P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE,
966 P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE,
967 P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE,
968 P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE,
969 P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE,
970 P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE,
971 P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%type,
972 P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%type,
973 P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%type,
974 P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%type,
975 P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE,
976 P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE,
977 P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE,
978 P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE,
979 P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE,
980 P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE,
981 P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE,
982 P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE,
983 P_USER_NAME IN VARCHAR2,
984 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
985 X_MSG_COUNT OUT NOCOPY NUMBER,
986 X_MSG_DATA OUT NOCOPY VARCHAR2,
987 X_RESOURCE_ID OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
988 X_RESOURCE_NUMBER OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE,
989 P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE,
990 P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE
991 ) IS
992
993 l_api_version CONSTANT NUMBER := 1.0;
994 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE';
995 /* Moved the initial assignment of below variables to inside begin */
996 l_category jtf_rs_resource_extns.category%TYPE;
997 l_source_id jtf_rs_resource_extns.source_id%TYPE;
998 l_address_id jtf_rs_resource_extns.address_id%TYPE;
999 l_contact_id jtf_rs_resource_extns.contact_id%TYPE;
1000 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%TYPE;
1001 l_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
1002 l_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
1003 l_time_zone jtf_rs_resource_extns.time_zone%TYPE;
1004 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%TYPE;
1005 l_primary_language jtf_rs_resource_extns.primary_language%TYPE;
1006 l_secondary_language jtf_rs_resource_extns.secondary_language%TYPE;
1007 l_support_site_id jtf_rs_resource_extns.support_site_id%TYPE;
1008 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%TYPE;
1009 l_server_group_id jtf_rs_resource_extns.server_group_id%TYPE;
1010 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%TYPE;
1011 l_cost_center jtf_rs_resource_extns.cost_center%TYPE;
1012 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%TYPE;
1013 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%TYPE;
1014 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%TYPE;
1015 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%TYPE;
1016 l_hold_payment jtf_rs_resource_extns.hold_payment%TYPE;
1017 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%TYPE;
1018 l_user_id jtf_rs_resource_extns.user_id%TYPE;
1019 l_transaction_number jtf_rs_resource_extns.transaction_number%TYPE;
1020 --l_location MDSYS.SDO_GEOMETRY := p_location;
1021
1022 l_check_char VARCHAR2(1);
1023 l_check_dup_id VARCHAR2(1);
1024 l_rowid ROWID;
1025 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
1026 l_resource_number jtf_rs_resource_extns.resource_number%TYPE;
1027 l_bind_data_id NUMBER;
1028
1029 /* Moved the initial assignment of below variables to inside begin */
1030 l_resource_name jtf_rs_resource_extns_tl.resource_name%type;
1031 l_source_name jtf_rs_resource_extns.source_name%type;
1032 l_source_number jtf_rs_resource_extns.source_number%type;
1033 l_source_job_title jtf_rs_resource_extns.source_job_title%type;
1034 l_source_email jtf_rs_resource_extns.source_email%type;
1035 l_source_phone jtf_rs_resource_extns.source_phone%type;
1036 l_source_org_id jtf_rs_resource_extns.source_org_id%type;
1037 l_source_org_name jtf_rs_resource_extns.source_org_name%type;
1038 l_source_address1 jtf_rs_resource_extns.source_address1%type;
1039 l_source_address2 jtf_rs_resource_extns.source_address2%type;
1040 l_source_address3 jtf_rs_resource_extns.source_address3%type;
1041 l_source_address4 jtf_rs_resource_extns.source_address4%type;
1042 l_source_city jtf_rs_resource_extns.source_city%type;
1043 l_source_postal_code jtf_rs_resource_extns.source_postal_code%type;
1044 l_source_state jtf_rs_resource_extns.source_state%type;
1045 l_source_province jtf_rs_resource_extns.source_province%type;
1046 l_source_county jtf_rs_resource_extns.source_county%type;
1047 l_source_country jtf_rs_resource_extns.source_country%type;
1048 l_source_mgr_id jtf_rs_resource_extns.source_mgr_id%type;
1049 l_source_mgr_name jtf_rs_resource_extns.source_mgr_name%type;
1050 l_source_business_grp_id jtf_rs_resource_extns.source_business_grp_id%type;
1051 l_source_business_grp_name jtf_rs_resource_extns.source_business_grp_name%type;
1052 l_source_first_name jtf_rs_resource_extns.source_first_name%type;
1053 l_source_last_name jtf_rs_resource_extns.source_last_name%type;
1054 l_source_middle_name jtf_rs_resource_extns.source_middle_name%type;
1055 l_source_category jtf_rs_resource_extns.source_category%type;
1056 l_source_status jtf_rs_resource_extns.source_status%type;
1057 l_source_office jtf_rs_resource_extns.source_office%type;
1058 l_source_location jtf_rs_resource_extns.source_location%type;
1059 l_source_mailstop jtf_rs_resource_extns.source_mailstop%type;
1060 l_source_mobile_phone jtf_rs_resource_extns.source_mobile_phone%type;
1061 l_source_pager jtf_rs_resource_extns.source_pager%type;
1062 l_user_name jtf_rs_resource_extns.user_name%type;
1063 l_source_job_id jtf_rs_resource_extns.source_job_id%type;
1064 l_party_id jtf_rs_resource_extns.person_party_id%type;
1065
1066 l_return_status VARCHAR2(2000);
1067 l_msg_count NUMBER;
1068 l_msg_data VARCHAR2(2000);
1069
1070 CURSOR c_jtf_rs_resource_extns( l_rowid IN ROWID ) IS
1071 SELECT 'Y'
1072 FROM jtf_rs_resource_extns
1073 WHERE ROWID = l_rowid;
1074
1075 CURSOR c_dup_resource_id (l_resource_id IN jtf_rs_resource_extns.resource_id%type) IS
1076 SELECT 'X'
1077 FROM jtf_rs_resource_extns
1078 WHERE resource_id = l_resource_id;
1079
1080 CURSOR c_asg(p_person_id IN NUMBER) IS
1081 SELECT job_id
1082 FROM per_all_assignments_f
1083 WHERE person_id = p_source_id
1084 AND primary_flag = 'Y'
1085 AND assignment_type in ('E','C')
1086 AND trunc(sysdate) between effective_start_date and effective_end_date;
1087
1088 CURSOR c_party_id(p_person_id IN NUMBER) IS
1089 SELECT ppf.party_id
1090 FROM per_all_people_f ppf
1091 WHERE ppf.person_id = p_person_id
1092 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date;
1093
1094 l_value VARCHAR2(100);
1095 l_address_ret_status varchar2(10);
1096 l_address_found boolean := true;
1097
1098 BEGIN
1099
1100 l_category := upper(p_category);
1101 l_source_id := p_source_id;
1102 l_address_id := p_address_id;
1103 l_contact_id := p_contact_id;
1104 l_managing_emp_id := p_managing_emp_id;
1105 l_start_date_active := trunc(p_start_date_active);
1106 l_end_date_active := trunc(p_end_date_active);
1107 l_time_zone := p_time_zone;
1108 l_cost_per_hr := p_cost_per_hr;
1109 l_primary_language := p_primary_language;
1110 l_secondary_language := p_secondary_language;
1111 l_support_site_id := p_support_site_id;
1112 l_ies_agent_login := p_ies_agent_login;
1113 l_server_group_id := p_server_group_id;
1114 l_assigned_to_group_id := p_assigned_to_group_id;
1115 l_cost_center := p_cost_center;
1116 l_charge_to_cost_center := p_charge_to_cost_center;
1117 l_comp_currency_code := p_comp_currency_code;
1118 l_commissionable_flag := p_commissionable_flag;
1119 l_hold_reason_code := p_hold_reason_code;
1120 l_hold_payment := p_hold_payment;
1121 l_comp_service_team_id := p_comp_service_team_id;
1122 l_user_id := p_user_id;
1123 l_transaction_number := p_transaction_number;
1124 l_resource_name := p_resource_name;
1125 l_source_name := p_source_name;
1126 l_source_number := p_source_number;
1127 l_source_job_title := p_source_job_title;
1128 l_source_email := p_source_email;
1129 l_source_phone := p_source_phone;
1130 l_source_org_id := p_source_org_id;
1131 l_source_org_name := p_source_org_name;
1132 l_source_address1 := p_source_address1;
1133 l_source_address2 := p_source_address2;
1134 l_source_address3 := p_source_address3;
1135 l_source_address4 := p_source_address4;
1136 l_source_city := p_source_city;
1137 l_source_postal_code := p_source_postal_code;
1138 l_source_state := p_source_state;
1139 l_source_province := p_source_province;
1140 l_source_county := p_source_county;
1141 l_source_country := p_source_country;
1142 l_source_mgr_id := p_source_mgr_id;
1143 l_source_mgr_name := p_source_mgr_name;
1144 l_source_business_grp_id := p_source_business_grp_id;
1145 l_source_business_grp_name := p_source_business_grp_name;
1146 l_source_first_name := p_source_first_name;
1147 l_source_last_name := p_source_last_name;
1148 l_source_middle_name := p_source_middle_name;
1149 l_source_category := p_source_category;
1150 l_source_status := p_source_status;
1151 l_source_office := p_source_office;
1152 l_source_location := p_source_location;
1153 l_source_mailstop := p_source_mailstop;
1154 l_source_mobile_phone := p_source_mobile_phone;
1155 l_source_pager := p_source_pager;
1156 l_user_name := p_user_name;
1157
1158 SAVEPOINT create_resource_pvt;
1159 x_return_status := fnd_api.g_ret_sts_success;
1160 -- DBMS_OUTPUT.put_line(' Started Create Resource Pvt ');
1161
1162 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1163 RAISE fnd_api.g_exc_unexpected_error;
1164 END IF;
1165
1166 IF fnd_api.to_boolean(p_init_msg_list) THEN
1167 fnd_msg_pub.initialize;
1168 END IF;
1169
1170 IF l_category = 'EMPLOYEE' THEN
1171 OPEN c_asg(l_source_Id);
1172 FETCH c_asg INTO l_source_job_id;
1173 CLOSE c_asg;
1174 OPEN c_party_id(l_source_Id);
1175 FETCH c_party_id INTO l_party_id;
1176 CLOSE c_party_id;
1177 END IF;
1178 /* Make the pre processing call to the user hooks */
1179
1180 /* Pre Call to the Customer Type User Hook */
1181
1182 IF jtf_resource_utl.ok_to_execute(
1183 'JTF_RS_RESOURCE_PVT',
1184 'CREATE_RESOURCE',
1185 'B',
1186 'C')
1187 THEN
1188 IF jtf_usr_hks.ok_to_execute(
1189 'JTF_RS_RESOURCE_PVT',
1190 'CREATE_RESOURCE',
1191 'B',
1192 'C')
1193 THEN
1194
1195 jtf_rs_resource_cuhk.create_resource_pre(
1196 p_category => l_category,
1197 p_source_id => l_source_id,
1198 p_address_id => l_address_id,
1199 p_contact_id => l_contact_id,
1200 p_managing_emp_id => l_managing_emp_id,
1201 p_start_date_active => l_start_date_active,
1202 p_end_date_active => l_end_date_active,
1203 p_time_zone => l_time_zone,
1204 p_cost_per_hr => l_cost_per_hr,
1205 p_primary_language => l_primary_language,
1206 p_secondary_language => l_secondary_language,
1207 p_support_site_id => l_support_site_id,
1208 p_ies_agent_login => l_ies_agent_login,
1209 p_server_group_id => l_server_group_id,
1210 p_assigned_to_group_id => l_assigned_to_group_id,
1211 p_cost_center => l_cost_center,
1212 p_charge_to_cost_center => l_charge_to_cost_center,
1213 p_comp_currency_code => l_comp_currency_code,
1214 p_commissionable_flag => l_commissionable_flag,
1215 p_hold_reason_code => l_hold_reason_code,
1216 p_hold_payment => l_hold_payment,
1217 p_comp_service_team_id => l_comp_service_team_id,
1218 p_user_id => l_user_id,
1219 --p_location => l_location,
1220 x_return_status => x_return_status);
1221
1222 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1223
1224 -- dbms_output.put_line('Returned Error status from the Pre Customer User Hook');
1225
1226
1227 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
1228 fnd_msg_pub.add;
1229
1230 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1231 RAISE FND_API.G_EXC_ERROR;
1232 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1233 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1234 END IF;
1235
1236
1237 END IF;
1238
1239 END IF;
1240 END IF;
1241
1242
1243 /* Pre Call to the Vertical Type User Hook */
1244
1245 IF jtf_resource_utl.ok_to_execute(
1246 'JTF_RS_RESOURCE_PVT',
1247 'CREATE_RESOURCE',
1248 'B',
1249 'V')
1250 THEN
1251 IF jtf_usr_hks.ok_to_execute(
1252 'JTF_RS_RESOURCE_PVT',
1253 'CREATE_RESOURCE',
1254 'B',
1255 'V')
1256 THEN
1257
1258 jtf_rs_resource_vuhk.create_resource_pre(
1259 p_category => l_category,
1260 p_source_id => l_source_id,
1261 p_address_id => l_address_id,
1262 p_contact_id => l_contact_id,
1263 p_managing_emp_id => l_managing_emp_id,
1264 p_start_date_active => l_start_date_active,
1265 p_end_date_active => l_end_date_active,
1266 p_time_zone => l_time_zone,
1267 p_cost_per_hr => l_cost_per_hr,
1268 p_primary_language => l_primary_language,
1269 p_secondary_language => l_secondary_language,
1270 p_support_site_id => l_support_site_id,
1271 p_ies_agent_login => l_ies_agent_login,
1272 p_server_group_id => l_server_group_id,
1273 p_assigned_to_group_id => l_assigned_to_group_id,
1274 p_cost_center => l_cost_center,
1275 p_charge_to_cost_center => l_charge_to_cost_center,
1276 p_comp_currency_code => l_comp_currency_code,
1277 p_commissionable_flag => l_commissionable_flag,
1278 p_hold_reason_code => l_hold_reason_code,
1279 p_hold_payment => l_hold_payment,
1280 p_comp_service_team_id => l_comp_service_team_id,
1281 p_user_id => l_user_id,
1282 --p_location => l_location,
1283 x_return_status => x_return_status);
1284
1285 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1286
1287 -- dbms_output.put_line('Returned Error status from the Pre Vertical User Hook');
1288
1289
1290 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
1291 fnd_msg_pub.add;
1292
1293 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1294 RAISE FND_API.G_EXC_ERROR;
1295 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1296 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1297 END IF;
1298
1299
1300 END IF;
1301
1302 END IF;
1303 END IF;
1304
1305
1306 /* Pre Call to the Internal Type User Hook */
1307
1308 IF jtf_resource_utl.ok_to_execute(
1309 'JTF_RS_RESOURCE_PVT',
1310 'CREATE_RESOURCE',
1311 'B',
1312 'I')
1313 THEN
1314 IF jtf_usr_hks.ok_to_execute(
1315 'JTF_RS_RESOURCE_PVT',
1316 'CREATE_RESOURCE',
1317 'B',
1318 'I')
1319 THEN
1320
1321 jtf_rs_resource_iuhk.create_resource_pre(
1322 p_category => l_category,
1323 p_source_id => l_source_id,
1324 p_address_id => l_address_id,
1325 p_contact_id => l_contact_id,
1326 p_managing_emp_id => l_managing_emp_id,
1327 p_start_date_active => l_start_date_active,
1328 p_end_date_active => l_end_date_active,
1329 p_time_zone => l_time_zone,
1330 p_cost_per_hr => l_cost_per_hr,
1331 p_primary_language => l_primary_language,
1332 p_secondary_language => l_secondary_language,
1333 p_support_site_id => l_support_site_id,
1334 p_ies_agent_login => l_ies_agent_login,
1335 p_server_group_id => l_server_group_id,
1336 p_assigned_to_group_id => l_assigned_to_group_id,
1337 p_cost_center => l_cost_center,
1338 p_charge_to_cost_center => l_charge_to_cost_center,
1339 p_comp_currency_code => l_comp_currency_code,
1340 p_commissionable_flag => l_commissionable_flag,
1341 p_hold_reason_code => l_hold_reason_code,
1342 p_hold_payment => l_hold_payment,
1343 p_comp_service_team_id => l_comp_service_team_id,
1344 p_user_id => l_user_id,
1345 --p_location => l_location,
1346 x_return_status => x_return_status);
1347
1348 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1349
1350
1351 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
1352 fnd_msg_pub.add;
1353
1354 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1355 RAISE FND_API.G_EXC_ERROR;
1356 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1358 END IF;
1359
1360
1361 END IF;
1362
1363 END IF;
1364 END IF;
1365
1366
1367
1368 /* Validate the Input Dates */
1369
1370 jtf_resource_utl.validate_input_dates(
1371 p_start_date_active => l_start_date_active,
1372 p_end_date_active => l_end_date_active,
1373 x_return_status => x_return_status
1374 );
1375
1376 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1377
1378 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1379 RAISE FND_API.G_EXC_ERROR;
1380 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1382 END IF;
1383
1384 END IF;
1385
1386 /* Validate for address id in case in PARTNER */
1387 if(p_category IN ('PARTNER', 'PARTY'))
1388 then
1389 if (p_address_id is NOT NULL) then
1390 validate_party_address(p_source_id => p_source_id,
1391 p_address_id => p_address_id,
1392 p_action => 'I',
1393 p_found => l_address_found,
1394 p_return_status => l_address_ret_status);
1395
1396 if(l_address_ret_status <> fnd_api.g_ret_sts_success)
1397 then
1398 IF L_ADDRESS_RET_STATUS = FND_API.G_RET_STS_ERROR THEN
1399 RAISE FND_API.G_EXC_ERROR;
1400 ELSIF L_ADDRESS_RET_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1402 END IF;
1403
1404 end if;
1405
1406 if not(l_address_found)
1407 then
1408 fnd_message.set_name('JTF', 'JTF_RS_NOT_PRIMARY_ADDR');
1409 fnd_msg_pub.add;
1410 RAISE fnd_api.g_exc_error;
1411
1412 end if;
1413 end if;
1414 end if; -- end of partner address check
1415
1416 /* This portion of the code was modified to accomodate the calls to Migration API */
1417 /* Check if the Global Variable Flag for Resource ID is Y or N */
1418
1419 -- dbms_output.put_line ('Before checkin the Global flag in PVT API');
1420
1421 IF (G_RS_ID_PVT_FLAG = 'Y') OR (G_RS_ID_PVT_FLAG = 'N' AND JTF_RS_RESOURCE_PUB.G_RESOURCE_ID IS NULL) THEN
1422
1423 /* Get the next value of the Resource_id from the sequence. */
1424
1425 LOOP
1426 SELECT jtf_rs_resource_extns_s.nextval
1427 INTO l_resource_id
1428 FROM dual;
1429 --dbms_output.put_line ('After Select - Resource ID ' || l_resource_id);
1430 OPEN c_dup_resource_id (l_resource_id);
1431 FETCH c_dup_resource_id INTO l_check_dup_id;
1432 EXIT WHEN c_dup_resource_id%NOTFOUND;
1433 CLOSE c_dup_resource_id;
1434 END LOOP;
1435 CLOSE c_dup_resource_id;
1436 ELSE
1437 l_resource_id := JTF_RS_RESOURCE_PUB.G_RESOURCE_ID;
1438 END IF;
1439
1440 /* Get the next value of the Resource_number from the sequence. */
1441
1442 SELECT jtf_rs_resource_number_s.nextval
1443 INTO l_resource_number
1444 FROM dual;
1445
1446 /* Make a call to the Resource Audit API */
1447
1448 jtf_rs_resource_extns_aud_pvt.insert_resource
1449 (P_API_VERSION => 1,
1450 P_INIT_MSG_LIST => fnd_api.g_false,
1451 P_COMMIT => fnd_api.g_false,
1452 P_RESOURCE_ID => l_resource_id,
1453 P_RESOURCE_NUMBER => l_resource_number,
1454 P_CATEGORY => l_category,
1455 P_SOURCE_ID => l_source_id,
1456 P_ADDRESS_ID => l_address_id,
1457 P_CONTACT_ID => l_contact_id,
1458 P_MANAGING_EMP_ID => l_managing_emp_id,
1459 P_START_DATE_ACTIVE => l_start_date_active,
1460 P_END_DATE_ACTIVE => l_end_date_active,
1461 P_TIME_ZONE => l_time_zone,
1462 P_COST_PER_HR => l_cost_per_hr,
1463 P_PRIMARY_LANGUAGE => l_primary_language,
1464 P_SECONDARY_LANGUAGE => l_secondary_language,
1465 P_SUPPORT_SITE_ID => l_support_site_id,
1466 P_IES_AGENT_LOGIN => l_ies_agent_login,
1467 P_SERVER_GROUP_ID => l_server_group_id,
1468 P_ASSIGNED_TO_GROUP_ID => l_assigned_to_group_id,
1469 P_COST_CENTER => l_cost_center,
1470 P_CHARGE_TO_COST_CENTER => l_charge_to_cost_center,
1471 P_COMP_CURRENCY_CODE => l_comp_currency_code,
1472 P_COMMISSIONABLE_FLAG => l_commissionable_flag,
1473 P_HOLD_REASON_CODE => l_hold_reason_code,
1474 P_HOLD_PAYMENT => l_hold_payment,
1475 P_COMP_SERVICE_TEAM_ID => l_comp_service_team_id,
1476 P_USER_ID => l_user_id,
1477 P_TRANSACTION_NUMBER => l_transaction_number,
1478 --P_LOCATION => l_location,
1479 P_OBJECT_VERSION_NUMBER => 1,
1480 P_RESOURCE_NAME => l_RESOURCE_NAME,
1481 P_SOURCE_NAME => l_SOURCE_NAME,
1482 P_SOURCE_NUMBER => l_SOURCE_NUMBER,
1483 P_SOURCE_JOB_TITLE => l_SOURCE_JOB_TITLE ,
1484 P_SOURCE_EMAIL => l_SOURCE_EMAIL ,
1485 P_SOURCE_PHONE => l_SOURCE_PHONE ,
1486 P_SOURCE_ORG_ID => l_SOURCE_ORG_ID ,
1487 P_SOURCE_ORG_NAME => l_SOURCE_ORG_NAME ,
1488 P_SOURCE_ADDRESS1 => l_SOURCE_ADDRESS1 ,
1489 P_SOURCE_ADDRESS2 => l_SOURCE_ADDRESS2 ,
1490 P_SOURCE_ADDRESS3 => l_SOURCE_ADDRESS3 ,
1491 P_SOURCE_ADDRESS4 => l_SOURCE_ADDRESS4 ,
1492 P_SOURCE_CITY => l_SOURCE_CITY ,
1493 P_SOURCE_POSTAL_CODE => l_SOURCE_POSTAL_CODE ,
1494 P_SOURCE_STATE => l_SOURCE_STATE ,
1495 P_SOURCE_PROVINCE => l_SOURCE_PROVINCE ,
1496 P_SOURCE_COUNTY => l_SOURCE_COUNTY ,
1497 P_SOURCE_COUNTRY => l_SOURCE_COUNTRY ,
1498 P_SOURCE_MGR_ID => l_SOURCE_MGR_ID ,
1499 P_SOURCE_MGR_NAME => l_SOURCE_MGR_NAME ,
1500 P_SOURCE_BUSINESS_GRP_ID => l_SOURCE_BUSINESS_GRP_ID ,
1501 P_SOURCE_BUSINESS_GRP_NAME => l_SOURCE_BUSINESS_GRP_NAME ,
1502 P_SOURCE_FIRST_NAME => l_source_first_name ,
1503 P_SOURCE_LAST_NAME => l_source_last_name ,
1504 P_SOURCE_MIDDLE_NAME => l_source_middle_name ,
1505 P_SOURCE_CATEGORY => l_source_category ,
1506 P_SOURCE_STATUS => l_source_status ,
1507 P_SOURCE_OFFICE => l_source_office ,
1508 P_SOURCE_LOCATION => l_source_location ,
1509 P_SOURCE_MAILSTOP => l_source_mailstop ,
1510 P_SOURCE_JOB_ID => l_source_job_id ,
1511 P_PARTY_ID => l_party_id ,
1512 X_RETURN_STATUS => x_return_status,
1513 X_MSG_COUNT => x_msg_count,
1514 X_MSG_DATA => x_msg_data,
1515 P_SOURCE_MOBILE_PHONE => l_source_mobile_phone ,
1516 P_SOURCE_PAGER => l_source_pager
1517 );
1518
1519 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1520 -- dbms_output.put_line('Failed status from call to audit procedure');
1521 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1522 RAISE FND_API.G_EXC_ERROR;
1523 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1525 END IF;
1526
1527 END IF;
1528
1529 /* Insert the row into the table by calling the table handler. */
1530
1531 jtf_rs_resource_extns_pkg.insert_row(
1532 x_rowid => l_rowid,
1533 x_resource_id => l_resource_id,
1534 x_category => l_category,
1535 x_resource_number => l_resource_number,
1536 x_source_id => l_source_id,
1537 x_address_id => l_address_id,
1538 x_contact_id => l_contact_id,
1539 x_managing_employee_id => l_managing_emp_id,
1540 x_start_date_active => l_start_date_active,
1541 x_end_date_active => l_end_date_active,
1542 x_time_zone => l_time_zone,
1543 x_cost_per_hr => l_cost_per_hr,
1544 x_primary_language => l_primary_language,
1545 x_secondary_language => l_secondary_language,
1546 x_support_site_id => l_support_site_id,
1547 x_ies_agent_login => l_ies_agent_login,
1548 x_server_group_id => l_server_group_id,
1549 x_assigned_to_group_id => l_assigned_to_group_id,
1550 x_cost_center => l_cost_center,
1551 x_charge_to_cost_center => l_charge_to_cost_center,
1552 x_compensation_currency_code => l_comp_currency_code,
1553 x_commissionable_flag => l_commissionable_flag,
1554 x_hold_reason_code => l_hold_reason_code,
1555 x_hold_payment => l_hold_payment,
1556 x_comp_service_team_id => l_comp_service_team_id,
1557 x_user_id => l_user_id,
1558 --x_location => l_location,
1559 x_transaction_number => l_transaction_number,
1560 x_attribute1 => p_attribute1,
1561 x_attribute2 => p_attribute2,
1562 x_attribute3 => p_attribute3,
1563 x_attribute4 => p_attribute4,
1564 x_attribute5 => p_attribute5,
1565 x_attribute6 => p_attribute6,
1566 x_attribute7 => p_attribute7,
1567 x_attribute8 => p_attribute8,
1568 x_attribute9 => p_attribute9,
1569 x_attribute10 => p_attribute10,
1570 x_attribute11 => p_attribute11,
1571 x_attribute12 => p_attribute12,
1572 x_attribute13 => p_attribute13,
1573 x_attribute14 => p_attribute14,
1574 x_attribute15 => p_attribute15,
1575 x_attribute_category => p_attribute_category,
1576 x_creation_date => SYSDATE,
1577 x_created_by => jtf_resource_utl.created_by,
1578 x_last_update_date => SYSDATE,
1579 x_last_updated_by => jtf_resource_utl.updated_by,
1580 x_last_update_login => jtf_resource_utl.login_id,
1581 x_resource_name => l_resource_name,
1582 x_source_name => l_source_name,
1583 x_source_number => l_source_number,
1584 x_source_job_title => l_source_job_title,
1585 x_source_email => l_source_email,
1586 x_source_phone => l_source_phone,
1587 x_source_org_id => l_source_org_id,
1588 x_source_org_name => l_source_org_name,
1589 x_source_address1 => l_source_address1,
1590 x_source_address2 => l_source_address2,
1591 x_source_address3 => l_source_address3,
1592 x_source_address4 => l_source_address4,
1593 x_source_city => l_source_city,
1594 x_source_postal_code => l_source_postal_code,
1595 x_source_state => l_source_state,
1596 x_source_province => l_source_province,
1597 x_source_county => l_source_county,
1598 x_source_country => l_source_country,
1599 x_source_mgr_id => l_source_mgr_id,
1600 x_source_mgr_name => l_source_mgr_name,
1601 x_source_business_grp_id => l_source_business_grp_id,
1602 x_source_business_grp_name=> l_source_business_grp_name,
1603 x_source_first_name => l_source_first_name,
1604 x_source_last_name => l_source_last_name,
1605 x_source_middle_name => l_source_middle_name,
1606 x_source_category => l_source_category,
1607 x_source_status => l_source_status,
1608 x_source_office => l_source_office,
1609 x_source_location => l_source_location,
1610 x_source_mailstop => l_source_mailstop,
1611 x_source_mobile_phone => l_source_mobile_phone,
1612 x_source_pager => l_source_pager,
1613 x_source_job_id => l_source_job_id,
1614 x_party_id => l_party_id,
1615 x_user_name => l_user_name
1616 );
1617
1618 -- dbms_output.put_line('After Insert Procedure');
1619 OPEN c_jtf_rs_resource_extns(l_rowid);
1620 FETCH c_jtf_rs_resource_extns INTO l_check_char;
1621 IF c_jtf_rs_resource_extns%NOTFOUND THEN
1622 -- dbms_output.put_line('Error in Table Handler');
1623 IF c_jtf_rs_resource_extns%ISOPEN THEN
1624 CLOSE c_jtf_rs_resource_extns;
1625 END IF;
1626 x_return_status := fnd_api.g_ret_sts_unexp_error;
1627 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
1628 fnd_msg_pub.add;
1629 RAISE fnd_api.g_exc_unexpected_error;
1630 ELSE
1631 -- dbms_output.put_line('Resource Successfully Created');
1632 x_resource_id := l_resource_id;
1633 x_resource_number := l_resource_number;
1634 END IF;
1635
1636 /* Close the cursors */
1637
1638 IF c_jtf_rs_resource_extns%ISOPEN THEN
1639 CLOSE c_jtf_rs_resource_extns;
1640 END IF;
1641
1642 /* Make the post processing call to the user hooks */
1643
1644 /* Post Call to the Customer Type User Hook */
1645
1646 IF jtf_usr_hks.ok_to_execute(
1647 'JTF_RS_RESOURCE_PVT',
1648 'CREATE_RESOURCE',
1649 'A',
1650 'C')
1651 THEN
1652
1653 jtf_rs_resource_cuhk.create_resource_post(
1654 p_resource_id => l_resource_id,
1655 p_category => l_category,
1656 p_source_id => l_source_id,
1657 p_address_id => l_address_id,
1658 p_contact_id => l_contact_id,
1659 p_managing_emp_id => l_managing_emp_id,
1660 p_start_date_active => l_start_date_active,
1661 p_end_date_active => l_end_date_active,
1662 p_time_zone => l_time_zone,
1663 p_cost_per_hr => l_cost_per_hr,
1664 p_primary_language => l_primary_language,
1665 p_secondary_language => l_secondary_language,
1666 p_support_site_id => l_support_site_id,
1667 p_ies_agent_login => l_ies_agent_login,
1668 p_server_group_id => l_server_group_id,
1669 p_assigned_to_group_id => l_assigned_to_group_id,
1670 p_cost_center => l_cost_center,
1671 p_charge_to_cost_center => l_charge_to_cost_center,
1672 p_comp_currency_code => l_comp_currency_code,
1673 p_commissionable_flag => l_commissionable_flag,
1674 p_hold_reason_code => l_hold_reason_code,
1675 p_hold_payment => l_hold_payment,
1676 p_comp_service_team_id => l_comp_service_team_id,
1677 p_user_id => l_user_id,
1678 --p_location => l_location,
1679 x_return_status => x_return_status);
1680
1681 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1682 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
1683
1684 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
1685 fnd_msg_pub.add;
1686 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1687 RAISE FND_API.G_EXC_ERROR;
1688 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1689 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1690 END IF;
1691
1692 END IF;
1693 END IF;
1694
1695 /* Post Call to the Vertical Type User Hook */
1696
1697 IF jtf_resource_utl.ok_to_execute(
1698 'JTF_RS_RESOURCE_PVT',
1699 'CREATE_RESOURCE',
1700 'A',
1701 'V')
1702 THEN
1703 IF jtf_usr_hks.ok_to_execute(
1704 'JTF_RS_RESOURCE_PVT',
1705 'CREATE_RESOURCE',
1706 'A',
1707 'V')
1708 THEN
1709
1710 jtf_rs_resource_vuhk.create_resource_post(
1711 p_resource_id => l_resource_id,
1712 p_category => l_category,
1713 p_source_id => l_source_id,
1714 p_address_id => l_address_id,
1715 p_contact_id => l_contact_id,
1716 p_managing_emp_id => l_managing_emp_id,
1717 p_start_date_active => l_start_date_active,
1718 p_end_date_active => l_end_date_active,
1719 p_time_zone => l_time_zone,
1720 p_cost_per_hr => l_cost_per_hr,
1721 p_primary_language => l_primary_language,
1722 p_secondary_language => l_secondary_language,
1723 p_support_site_id => l_support_site_id,
1724 p_ies_agent_login => l_ies_agent_login,
1725 p_server_group_id => l_server_group_id,
1726 p_assigned_to_group_id => l_assigned_to_group_id,
1727 p_cost_center => l_cost_center,
1728 p_charge_to_cost_center => l_charge_to_cost_center,
1729 p_comp_currency_code => l_comp_currency_code,
1730 p_commissionable_flag => l_commissionable_flag,
1731 p_hold_reason_code => l_hold_reason_code,
1732 p_hold_payment => l_hold_payment,
1733 p_comp_service_team_id => l_comp_service_team_id,
1734 p_user_id => l_user_id,
1735 --p_location => l_location,
1736 x_return_status => x_return_status);
1737
1738 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1739
1740 -- dbms_output.put_line('Returned Error status from the Post Vertical User Hook');
1741
1742
1743 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
1744 fnd_msg_pub.add;
1745
1746 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1747 RAISE FND_API.G_EXC_ERROR;
1748 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1749 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1750 END IF;
1751
1752
1753 END IF;
1754
1755 END IF;
1756 END IF;
1757
1758
1759 /* Post Call to the Internal Type User Hook */
1760
1761 IF jtf_resource_utl.ok_to_execute(
1762 'JTF_RS_RESOURCE_PVT',
1763 'CREATE_RESOURCE',
1764 'A',
1765 'I')
1766 THEN
1767 IF jtf_usr_hks.ok_to_execute(
1768 'JTF_RS_RESOURCE_PVT',
1769 'CREATE_RESOURCE',
1770 'A',
1771 'I')
1772 THEN
1773
1774 jtf_rs_resource_iuhk.create_resource_post(
1775 p_resource_id => l_resource_id,
1776 p_category => l_category,
1777 p_source_id => l_source_id,
1778 p_address_id => l_address_id,
1779 p_contact_id => l_contact_id,
1780 p_managing_emp_id => l_managing_emp_id,
1781 p_start_date_active => l_start_date_active,
1782 p_end_date_active => l_end_date_active,
1783 p_time_zone => l_time_zone,
1784 p_cost_per_hr => l_cost_per_hr,
1785 p_primary_language => l_primary_language,
1786 p_secondary_language => l_secondary_language,
1787 p_support_site_id => l_support_site_id,
1788 p_ies_agent_login => l_ies_agent_login,
1789 p_server_group_id => l_server_group_id,
1790 p_assigned_to_group_id => l_assigned_to_group_id,
1791 p_cost_center => l_cost_center,
1792 p_charge_to_cost_center => l_charge_to_cost_center,
1793 p_comp_currency_code => l_comp_currency_code,
1794 p_commissionable_flag => l_commissionable_flag,
1795 p_hold_reason_code => l_hold_reason_code,
1796 p_hold_payment => l_hold_payment,
1797 p_comp_service_team_id => l_comp_service_team_id,
1798 p_user_id => l_user_id,
1799 -- p_location => l_location,
1800 x_return_status => x_return_status);
1801
1802 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1803
1804 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
1805 fnd_msg_pub.add;
1806
1807 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1808 RAISE FND_API.G_EXC_ERROR;
1809 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1810 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1811 END IF;
1812
1813
1814 END IF;
1815
1816 END IF;
1817 END IF;
1818
1819
1820 /* Standard call for Message Generation */
1821
1822 IF jtf_resource_utl.ok_to_execute(
1823 'JTF_RS_RESOURCE_PVT',
1824 'CREATE_RESOURCE',
1825 'M',
1826 'M')
1827 THEN
1828 IF jtf_usr_hks.ok_to_execute(
1829 'JTF_RS_RESOURCE_PVT',
1830 'CREATE_RESOURCE',
1831 'M',
1832 'M')
1833 THEN
1834
1835 IF (jtf_rs_resource_cuhk.ok_to_generate_msg(
1836 p_resource_id => l_resource_id,
1837 x_return_status => x_return_status) )
1838 THEN
1839
1840 /* Get the bind data id for the Business Object Instance */
1841
1842 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
1843
1844
1845 /* Set bind values for the bind variables in the Business Object SQL */
1846
1847 jtf_usr_hks.load_bind_data(l_bind_data_id, 'resource_id', l_resource_id, 'S', 'N');
1848
1849
1850 /* Call the message generation API */
1851
1852 jtf_usr_hks.generate_message(
1853 p_prod_code => 'JTF',
1854 p_bus_obj_code => 'RS_RES',
1855 p_action_code => 'I',
1856 p_bind_data_id => l_bind_data_id,
1857 x_return_code => x_return_status);
1858
1859
1860 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
1861
1862 -- dbms_output.put_line('Returned Error status from the Message Generation API');
1863
1864
1865 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
1866 fnd_msg_pub.add;
1867
1868 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
1869 RAISE FND_API.G_EXC_ERROR;
1870 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
1871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1872 END IF;
1873
1874
1875 END IF;
1876
1877 END IF;
1878
1879 END IF;
1880 END IF;
1881
1882
1883 IF fnd_api.to_boolean(p_commit) THEN
1884
1885 COMMIT WORK;
1886
1887 END IF;
1888
1889 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1890
1891 /* Calling publish API to raise create resource event. */
1892 /* added by baianand on 11/04/2002 */
1893
1894 begin
1895 jtf_rs_wf_events_pub.create_resource
1896 (p_api_version => 1.0
1897 ,p_init_msg_list => fnd_api.g_false
1898 ,p_commit => fnd_api.g_false
1899 ,p_resource_id => l_resource_id
1900 ,p_resource_name => l_resource_name
1901 ,p_category => l_category
1902 ,p_user_id => l_user_id
1903 ,p_start_date_active => l_start_date_active
1904 ,p_end_date_active => l_end_date_active
1905 ,x_return_status => l_return_status
1906 ,x_msg_count => l_msg_count
1907 ,x_msg_data => l_msg_data);
1908
1909 EXCEPTION when others then
1910 null;
1911 end;
1912
1913 /* End of publish API call */
1914
1915
1916 /* Calling work API for insert record into wf_local tables. */
1917 /* added by baianand on 08/12/2002 */
1918
1919 begin
1920 jtf_rs_wf_integration_pub.create_resource
1921 (p_api_version => 1.0
1922 ,p_init_msg_list => fnd_api.g_false
1923 ,p_commit => fnd_api.g_false
1924 ,p_resource_id => l_resource_id
1925 ,p_resource_name => l_resource_name
1926 ,p_category => l_category
1927 ,p_user_id => l_user_id
1928 ,p_email_address => l_source_email
1929 ,p_start_date_active => l_start_date_active
1930 ,p_end_date_active => l_end_date_active
1931 ,x_return_status => l_return_status
1932 ,x_msg_count => l_msg_count
1933 ,x_msg_data => l_msg_data);
1934
1935 EXCEPTION when others then
1936 null;
1937 end;
1938
1939 /* End of work API call */
1940
1941
1942 EXCEPTION
1943
1944
1945 WHEN fnd_api.g_exc_error THEN
1946 ROLLBACK TO create_resource_pvt;
1947 x_return_status := fnd_api.g_ret_sts_error;
1948 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1949 p_data => x_msg_data);
1950 WHEN fnd_api.g_exc_unexpected_error THEN
1951 ROLLBACK TO create_resource_pvt;
1952 x_return_status := fnd_api.g_ret_sts_unexp_error;
1953 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1954 p_data => x_msg_data);
1955 WHEN OTHERS THEN
1956 ROLLBACK TO create_resource_pvt;
1957 -- The below lines removed as a part of fixing GSCC errors in R12 for jtfrspub.pls
1958 -- IF NOT(jtf_resource_utl.check_access(l_value))
1959 -- THEN
1960 -- IF(l_value = 'XMLGEN')
1961 -- THEN
1962 -- fnd_message.set_name ('JTF', 'JTF_RS_XMLGEN_ERR');
1963 -- FND_MSG_PUB.add;
1964 -- ELSIF(l_value = 'JTF_USR_HKS')
1965 -- THEN
1966 -- fnd_message.set_name ('JTF', 'JTF_RS_JUHK_ERR');
1967 -- FND_MSG_PUB.add;
1968 -- END IF;
1969 -- ELSE
1970 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
1971 fnd_message.set_token('P_SQLCODE',SQLCODE);
1972 fnd_message.set_token('P_SQLERRM',SQLERRM);
1973 fnd_message.set_token('P_API_NAME', l_api_name);
1974 FND_MSG_PUB.add;
1975 -- END IF;
1976 x_return_status := fnd_api.g_ret_sts_unexp_error;
1977 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
1978 p_data => x_msg_data);
1979
1980 END create_resource;
1981
1982
1983 PROCEDURE create_resource_migrate (
1984 P_API_VERSION IN NUMBER,
1985 P_INIT_MSG_LIST IN VARCHAR2,
1986 P_COMMIT IN VARCHAR2,
1987 P_CATEGORY IN JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE,
1988 P_SOURCE_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_ID%TYPE,
1989 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
1990 P_CONTACT_ID IN JTF_RS_RESOURCE_EXTNS.CONTACT_ID%TYPE,
1991 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
1992 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
1993 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
1994 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
1995 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
1996 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
1997 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
1998 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
1999 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
2000 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
2001 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
2002 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
2003 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
2004 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
2005 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
2006 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
2007 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
2008 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
2009 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
2010 P_TRANSACTION_NUMBER IN JTF_RS_RESOURCE_EXTNS.TRANSACTION_NUMBER%TYPE,
2011 --P_LOCATION IN MDSYS.SDO_GEOMETRY,
2012 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
2013 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
2014 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
2015 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
2016 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
2017 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
2018 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
2019 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
2020 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
2021 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
2022 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
2023 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
2024 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
2025 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
2026 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
2027 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE,
2028 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
2029 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2030 X_MSG_COUNT OUT NOCOPY NUMBER,
2031 X_MSG_DATA OUT NOCOPY VARCHAR2,
2032 X_RESOURCE_ID OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
2033 X_RESOURCE_NUMBER OUT NOCOPY JTF_RS_RESOURCE_EXTNS.RESOURCE_NUMBER%TYPE
2034 )
2035 IS
2036
2037 BEGIN
2038
2039 G_RS_ID_PVT_FLAG := 'N';
2040
2041 JTF_RS_RESOURCE_PVT.CREATE_RESOURCE (
2042 P_API_VERSION => P_API_VERSION,
2043 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
2044 P_COMMIT => P_COMMIT,
2045 P_CATEGORY => P_CATEGORY,
2046 P_SOURCE_ID => P_SOURCE_ID,
2047 P_ADDRESS_ID => P_ADDRESS_ID,
2048 P_CONTACT_ID => P_CONTACT_ID,
2049 P_MANAGING_EMP_ID => P_MANAGING_EMP_ID,
2050 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
2051 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
2052 P_TIME_ZONE => P_TIME_ZONE,
2053 P_COST_PER_HR => P_COST_PER_HR,
2054 P_PRIMARY_LANGUAGE => P_PRIMARY_LANGUAGE,
2055 P_SECONDARY_LANGUAGE => P_SECONDARY_LANGUAGE,
2056 P_SUPPORT_SITE_ID => P_SUPPORT_SITE_ID,
2057 P_IES_AGENT_LOGIN => P_IES_AGENT_LOGIN,
2058 P_SERVER_GROUP_ID => P_SERVER_GROUP_ID,
2059 P_ASSIGNED_TO_GROUP_ID => P_ASSIGNED_TO_GROUP_ID,
2060 P_COST_CENTER => P_COST_CENTER,
2061 P_CHARGE_TO_COST_CENTER => P_CHARGE_TO_COST_CENTER,
2062 P_COMP_CURRENCY_CODE => P_COMP_CURRENCY_CODE,
2063 P_COMMISSIONABLE_FLAG => P_COMMISSIONABLE_FLAG,
2064 P_HOLD_REASON_CODE => P_HOLD_REASON_CODE,
2065 P_HOLD_PAYMENT => P_HOLD_PAYMENT,
2066 P_COMP_SERVICE_TEAM_ID => P_COMP_SERVICE_TEAM_ID,
2067 P_USER_ID => P_USER_ID,
2068 P_TRANSACTION_NUMBER => P_TRANSACTION_NUMBER,
2069 --P_LOCATION => P_LOCATION,
2070 P_ATTRIBUTE1 => P_ATTRIBUTE1,
2071 P_ATTRIBUTE2 => P_ATTRIBUTE2,
2072 P_ATTRIBUTE3 => P_ATTRIBUTE3,
2073 P_ATTRIBUTE4 => P_ATTRIBUTE4,
2074 P_ATTRIBUTE5 => P_ATTRIBUTE5,
2075 P_ATTRIBUTE6 => P_ATTRIBUTE6,
2076 P_ATTRIBUTE7 => P_ATTRIBUTE7,
2077 P_ATTRIBUTE8 => P_ATTRIBUTE8,
2078 P_ATTRIBUTE9 => P_ATTRIBUTE9,
2079 P_ATTRIBUTE10 => P_ATTRIBUTE10,
2080 P_ATTRIBUTE11 => P_ATTRIBUTE11,
2081 P_ATTRIBUTE12 => P_ATTRIBUTE12,
2082 P_ATTRIBUTE13 => P_ATTRIBUTE13,
2083 P_ATTRIBUTE14 => P_ATTRIBUTE14,
2084 P_ATTRIBUTE15 => P_ATTRIBUTE15,
2085 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
2086 P_SOURCE_NAME => JTF_RESOURCE_UTL.G_SOURCE_NAME,
2087 P_RESOURCE_NAME => JTF_RESOURCE_UTL.G_SOURCE_NAME,
2088 X_RETURN_STATUS => X_RETURN_STATUS,
2089 X_MSG_COUNT => X_MSG_COUNT,
2090 X_MSG_DATA => X_MSG_DATA,
2091 X_RESOURCE_ID => X_RESOURCE_ID,
2092 X_RESOURCE_NUMBER => X_RESOURCE_NUMBER
2093 );
2094
2095 END create_resource_migrate;
2096
2097 /* Procedure to update the resource based on input values passed by calling routines. */
2098
2099 PROCEDURE update_resource
2100 (P_API_VERSION IN NUMBER,
2101 P_INIT_MSG_LIST IN VARCHAR2,
2102 P_COMMIT IN VARCHAR2,
2103 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
2104 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
2105 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
2106 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
2107 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
2108 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
2109 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
2110 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
2111 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
2112 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
2113 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
2114 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
2115 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
2116 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
2117 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
2118 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
2119 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
2120 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
2121 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
2122 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
2123 --P_LOCATION IN MDSYS.SDO_GEOMETRY,
2124 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
2125 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
2126 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
2127 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
2128 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
2129 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
2130 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
2131 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
2132 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
2133 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
2134 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
2135 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
2136 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
2137 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
2138 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
2139 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE,
2140 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
2141 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2142 X_MSG_COUNT OUT NOCOPY NUMBER,
2143 X_MSG_DATA OUT NOCOPY VARCHAR2
2144 ) IS
2145
2146 l_api_version CONSTANT NUMBER := 1.0;
2147 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
2148 /* Moved the initial assignment of below variables to inside begin */
2149 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
2150 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%TYPE;
2151 -- added trunc for the dates 6feb2002
2152 l_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
2153 l_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
2154 l_time_zone jtf_rs_resource_extns.time_zone%TYPE;
2155 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%TYPE;
2156 l_primary_language jtf_rs_resource_extns.primary_language%TYPE;
2157 l_secondary_language jtf_rs_resource_extns.secondary_language%TYPE;
2158 l_support_site_id jtf_rs_resource_extns.support_site_id%TYPE;
2159 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%TYPE;
2160 l_server_group_id jtf_rs_resource_extns.server_group_id%TYPE;
2161 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%TYPE;
2162 l_cost_center jtf_rs_resource_extns.cost_center%TYPE;
2163 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%TYPE;
2164 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%TYPE;
2165 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%TYPE;
2166 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%TYPE;
2167 l_hold_payment jtf_rs_resource_extns.hold_payment%TYPE;
2168 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%TYPE;
2169 l_user_id jtf_rs_resource_extns.user_id%TYPE;
2170 --l_location mdsys.sdo_geometry := p_location;
2171 l_object_version_num jtf_rs_resource_extns.object_version_number%TYPE;
2172
2173 -- added for NOCOPY to handle in JTF_RESOURCE_UTL
2174 l_managing_emp_id_out jtf_rs_resource_extns.managing_employee_id%TYPE ;
2175 l_server_group_id_out jtf_rs_resource_extns.server_group_id%TYPE ;
2176 l_comp_service_team_id_out jtf_rs_resource_extns.comp_service_team_id%TYPE;
2177
2178 l_max_end_date DATE;
2179 l_min_start_date DATE;
2180 l_bind_data_id NUMBER;
2181 l_check_flag VARCHAR2(1);
2182
2183
2184
2185 CURSOR c_resource_update(
2186 l_resource_id IN NUMBER )
2187 IS
2188 SELECT
2189 DECODE(p_managing_emp_id, fnd_api.g_miss_num, managing_employee_id, p_managing_emp_id) managing_emp_id,
2190 -- added trunc on the dates 6feb 2002
2191 DECODE(p_start_date_active, fnd_api.g_miss_date, start_date_active, trunc(p_start_date_active)) start_date_active,
2192 DECODE(p_end_date_active, fnd_api.g_miss_date, end_date_active, trunc(p_end_date_active)) end_date_active,
2193 DECODE(p_time_zone, fnd_api.g_miss_num, time_zone, p_time_zone) time_zone,
2194 DECODE(p_cost_per_hr, fnd_api.g_miss_num, cost_per_hr, p_cost_per_hr) cost_per_hr,
2195 DECODE(p_primary_language, fnd_api.g_miss_char, primary_language, p_primary_language) primary_language,
2196 DECODE(p_secondary_language, fnd_api.g_miss_char, secondary_language, p_secondary_language) secondary_language,
2197 DECODE(p_support_site_id, fnd_api.g_miss_num, support_site_id, p_support_site_id) support_site_id,
2198 DECODE(p_ies_agent_login, fnd_api.g_miss_char, ies_agent_login, p_ies_agent_login) ies_agent_login,
2199 DECODE(p_server_group_id, fnd_api.g_miss_num, server_group_id, p_server_group_id) server_group_id,
2200 DECODE(p_assigned_to_group_id, fnd_api.g_miss_num, assigned_to_group_id, p_assigned_to_group_id) assigned_to_group_id,
2201 DECODE(p_cost_center, fnd_api.g_miss_char, cost_center, p_cost_center) cost_center,
2202 DECODE(p_charge_to_cost_center, fnd_api.g_miss_char, charge_to_cost_center, p_charge_to_cost_center) charge_to_cost_center,
2203 DECODE(p_comp_currency_code, fnd_api.g_miss_char, compensation_currency_code, p_comp_currency_code) comp_currency_code,
2204 DECODE(p_commissionable_flag, fnd_api.g_miss_char, commissionable_flag, p_commissionable_flag) commissionable_flag,
2205 DECODE(p_hold_reason_code, fnd_api.g_miss_char, hold_reason_code, p_hold_reason_code) hold_reason_code,
2206 DECODE(p_hold_payment, fnd_api.g_miss_char, hold_payment, p_hold_payment) hold_payment,
2207 DECODE(p_comp_service_team_id, fnd_api.g_miss_num, comp_service_team_id, p_comp_service_team_id) comp_service_team_id,
2208 DECODE(p_user_id, fnd_api.g_miss_num, user_id, p_user_id) user_id,
2209 --DECODE(p_location, jtf_rs_resource_pub.g_miss_location, location, p_location) location,
2210 DECODE(p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1) attribute1,
2211 DECODE(p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2) attribute2,
2212 DECODE(p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3) attribute3,
2213 DECODE(p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4) attribute4,
2214 DECODE(p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5) attribute5,
2215 DECODE(p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6) attribute6,
2216 DECODE(p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7) attribute7,
2217 DECODE(p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8) attribute8,
2218 DECODE(p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9) attribute9,
2219 DECODE(p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10) attribute10,
2220 DECODE(p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11) attribute11,
2221 DECODE(p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12) attribute12,
2222 DECODE(p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13) attribute13,
2223 DECODE(p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14) attribute14,
2224 DECODE(p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15) attribute15,
2225 DECODE(p_attribute_category, fnd_api.g_miss_char, attribute_category, p_attribute_category) attribute_category,
2226 category,
2227 resource_number,
2228 source_id,
2229 address_id,
2230 contact_id,
2231 transaction_number
2232 FROM jtf_rs_resource_extns_vl
2233 WHERE resource_id = l_resource_id;
2234
2235 resource_rec c_resource_update%ROWTYPE;
2236
2237
2238 -- Modtfying the below query for bug # 4956644
2239 -- New query logic is given in bug # 4052112
2240 -- OIC expanded the definition of compensation analyst to include any active user in the
2241 -- system regardless of their assignment to a CN responsibility.
2242 CURSOR c_assigned_to_group_id(
2243 l_assigned_to_group_id IN NUMBER)
2244 IS
2245 SELECT u.user_id
2246 FROM fnd_user u,
2247 jtf_rs_resource_extns r
2248 WHERE u.user_id = r.user_id
2249 AND u.user_id = l_assigned_to_group_id;
2250
2251
2252 CURSOR c_get_resource_info(
2253 l_resource_id IN NUMBER )
2254 IS
2255 SELECT start_date_active
2256 FROM jtf_rs_resource_extns
2257 WHERE resource_id = l_resource_id;
2258
2259
2260 CURSOR c_related_role_dates_first(
2261 l_resource_id IN NUMBER )
2262 IS
2263 SELECT min(start_date_active),
2264 max(end_date_active)
2265 FROM jtf_rs_role_relations
2266 WHERE role_resource_type = 'RS_INDIVIDUAL'
2267 AND role_resource_id = l_resource_id
2268 AND nvl(delete_flag, 'N') <> 'Y'
2269 AND end_date_active is not null;
2270
2271
2272 CURSOR c_related_role_dates_sec(
2273 l_resource_id IN NUMBER )
2274 IS
2275 SELECT min(start_date_active)
2276 FROM jtf_rs_role_relations
2277 WHERE role_resource_type = 'RS_INDIVIDUAL'
2278 AND role_resource_id = l_resource_id
2279 AND nvl(delete_flag, 'N') <> 'Y'
2280 AND end_date_active is null;
2281
2282
2283 CURSOR c_grp_mbr_role_dates_first(
2284 l_resource_id IN NUMBER )
2285 IS
2286 SELECT min(jrrr.start_date_active),
2287 max(jrrr.end_date_active)
2288 FROM jtf_rs_group_members jrgm,
2289 jtf_rs_role_relations jrrr
2290 WHERE jrgm.group_member_id = jrrr.role_resource_id
2291 AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
2292 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
2293 AND nvl(jrgm.delete_flag, 'N') <> 'Y'
2294 AND jrgm.resource_id = l_resource_id
2295 AND jrrr.end_date_active is not null;
2296
2297
2298 CURSOR c_grp_mbr_role_dates_sec(
2299 l_resource_id IN NUMBER )
2300 IS
2301 SELECT min(jrrr.start_date_active)
2302 FROM jtf_rs_group_members jrgm,
2303 jtf_rs_role_relations jrrr
2304 WHERE jrgm.group_member_id = jrrr.role_resource_id
2305 AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
2306 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
2307 AND nvl(jrgm.delete_flag, 'N') <> 'Y'
2308 AND jrgm.resource_id = l_resource_id
2309 AND jrrr.end_date_active is null;
2310
2311
2312 CURSOR c_team_mbr_role_dates_first(
2313 l_resource_id IN NUMBER )
2314 IS
2315 SELECT min(jrrr.start_date_active),
2316 max(jrrr.end_date_active)
2317 FROM jtf_rs_team_members jrtm,
2318 jtf_rs_role_relations jrrr
2319 WHERE jrtm.team_member_id = jrrr.role_resource_id
2320 AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
2321 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
2322 AND nvl(jrtm.delete_flag, 'N') <> 'Y'
2323 AND jrtm.team_resource_id = l_resource_id
2324 AND jrtm.resource_type = 'INDIVIDUAL'
2325 AND jrrr.end_date_active is not null;
2326
2327
2328 CURSOR c_team_mbr_role_dates_sec(
2329 l_resource_id IN NUMBER )
2330 IS
2331 SELECT min(jrrr.start_date_active)
2332 FROM jtf_rs_team_members jrtm,
2333 jtf_rs_role_relations jrrr
2334 WHERE jrtm.team_member_id = jrrr.role_resource_id
2335 AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
2336 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
2337 AND nvl(jrtm.delete_flag, 'N') <> 'Y'
2338 AND jrtm.team_resource_id = l_resource_id
2339 AND jrtm.resource_type = 'INDIVIDUAL'
2340 AND jrrr.end_date_active is null;
2341
2342
2343 CURSOR c_salesrep_dates_first(
2344 l_resource_id IN NUMBER )
2345 IS
2346 SELECT min(start_date_active),
2347 max(end_date_active)
2348 FROM jtf_rs_salesreps
2349 WHERE resource_id = l_resource_id
2350 AND end_date_active is not null;
2351
2352
2353 CURSOR c_salesrep_dates_sec(
2354 l_resource_id IN NUMBER )
2355 IS
2356 SELECT min(start_date_active)
2357 FROM jtf_rs_salesreps
2358 WHERE resource_id = l_resource_id
2359 AND end_date_active is null;
2360
2361 CURSOR c_validate_user_id(
2362 l_resource_id IN NUMBER,
2363 l_user_id IN NUMBER)
2364 IS
2365 SELECT 'Y'
2366 FROM jtf_rs_resource_extns
2367 WHERE user_id = l_user_id
2368 AND resource_id <> l_resource_id;
2369
2370 l_value VARCHAR2(100);
2371
2372 l_address_ret_status varchar2(10);
2373 l_address_found boolean := true;
2374 BEGIN
2375
2376 l_resource_id := p_resource_id;
2377 l_managing_emp_id := p_managing_emp_id;
2378 l_start_date_active := trunc(p_start_date_active);
2379 l_end_date_active := trunc(p_end_date_active);
2380 l_time_zone := p_time_zone;
2381 l_cost_per_hr := p_cost_per_hr;
2382 l_primary_language := p_primary_language;
2383 l_secondary_language := p_secondary_language;
2384 l_support_site_id := p_support_site_id;
2385 l_ies_agent_login := p_ies_agent_login;
2386 l_server_group_id := p_server_group_id;
2387 l_assigned_to_group_id := p_assigned_to_group_id;
2388 l_cost_center := p_cost_center;
2389 l_charge_to_cost_center := p_charge_to_cost_center;
2390 l_comp_currency_code := p_comp_currency_code;
2391 l_commissionable_flag := p_commissionable_flag;
2392 l_hold_reason_code := p_hold_reason_code;
2393 l_hold_payment := p_hold_payment;
2394 l_comp_service_team_id := p_comp_service_team_id;
2395 l_user_id := p_user_id;
2396 l_object_version_num := p_object_version_num;
2397
2398 SAVEPOINT update_resource_pvt;
2399
2400 x_return_status := fnd_api.g_ret_sts_success;
2401
2402 -- DBMS_OUTPUT.put_line('Started Update Resource Pvt ');
2403
2404
2405 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2406
2407 RAISE fnd_api.g_exc_unexpected_error;
2408
2409 END IF;
2410
2411
2412 IF fnd_api.to_boolean(p_init_msg_list) THEN
2413
2414 fnd_msg_pub.initialize;
2415
2416 END IF;
2417
2418 /* Make the pre processing call to the user hooks */
2419
2420 /* Pre Call to the Customer Type User Hook */
2421
2422 IF jtf_resource_utl.ok_to_execute(
2423 'JTF_RS_RESOURCE_PVT',
2424 'UPDATE_RESOURCE',
2425 'B',
2426 'C')
2427 THEN
2428 IF jtf_usr_hks.ok_to_execute(
2429 'JTF_RS_RESOURCE_PVT',
2430 'UPDATE_RESOURCE',
2431 'B',
2432 'C')
2433 THEN
2434
2435 jtf_rs_resource_cuhk.update_resource_pre(
2436 p_resource_id => l_resource_id,
2437 p_managing_emp_id => l_managing_emp_id,
2438 p_start_date_active => l_start_date_active,
2439 p_end_date_active => l_end_date_active,
2440 p_time_zone => l_time_zone,
2441 p_cost_per_hr => l_cost_per_hr,
2442 p_primary_language => l_primary_language,
2443 p_secondary_language => l_secondary_language,
2444 p_support_site_id => l_support_site_id,
2445 p_ies_agent_login => l_ies_agent_login,
2446 p_server_group_id => l_server_group_id,
2447 p_assigned_to_group_id => l_assigned_to_group_id,
2448 p_cost_center => l_cost_center,
2449 p_charge_to_cost_center => l_charge_to_cost_center,
2450 p_comp_currency_code => l_comp_currency_code,
2451 p_commissionable_flag => l_commissionable_flag,
2452 p_hold_reason_code => l_hold_reason_code,
2453 p_hold_payment => l_hold_payment,
2454 p_comp_service_team_id => l_comp_service_team_id,
2455 p_user_id => l_user_id,
2456 -- p_location => l_location,
2457 x_return_status => x_return_status);
2458
2459 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2460
2461 -- dbms_output.put_line('Returned Error status from the Pre Customer User Hook');
2462
2463
2464 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
2465 fnd_msg_pub.add;
2466
2467 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2468 RAISE FND_API.G_EXC_ERROR;
2469 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2471 END IF;
2472
2473
2474 END IF;
2475
2476 END IF;
2477 END IF;
2478
2479
2480 /* Pre Call to the Vertical Type User Hook */
2481
2482 IF jtf_resource_utl.ok_to_execute(
2483 'JTF_RS_RESOURCE_PVT',
2484 'UPDATE_RESOURCE',
2485 'B',
2486 'V')
2487 THEN
2488 IF jtf_usr_hks.ok_to_execute(
2489 'JTF_RS_RESOURCE_PVT',
2490 'UPDATE_RESOURCE',
2491 'B',
2492 'V')
2493 THEN
2494
2495 jtf_rs_resource_vuhk.update_resource_pre(
2496 p_resource_id => l_resource_id,
2497 p_managing_emp_id => l_managing_emp_id,
2498 p_start_date_active => l_start_date_active,
2499 p_end_date_active => l_end_date_active,
2500 p_time_zone => l_time_zone,
2501 p_cost_per_hr => l_cost_per_hr,
2502 p_primary_language => l_primary_language,
2503 p_secondary_language => l_secondary_language,
2504 p_support_site_id => l_support_site_id,
2505 p_ies_agent_login => l_ies_agent_login,
2506 p_server_group_id => l_server_group_id,
2507 p_assigned_to_group_id => l_assigned_to_group_id,
2508 p_cost_center => l_cost_center,
2509 p_charge_to_cost_center => l_charge_to_cost_center,
2510 p_comp_currency_code => l_comp_currency_code,
2511 p_commissionable_flag => l_commissionable_flag,
2512 p_hold_reason_code => l_hold_reason_code,
2513 p_hold_payment => l_hold_payment,
2514 p_comp_service_team_id => l_comp_service_team_id,
2515 p_user_id => l_user_id,
2516 -- p_location => l_location,
2517 x_return_status => x_return_status);
2518
2519 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2520
2521 -- dbms_output.put_line('Returned Error status from the Pre Vertical User Hook');
2522
2523
2524 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
2525 fnd_msg_pub.add;
2526
2527 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2528 RAISE FND_API.G_EXC_ERROR;
2529 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2530 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2531 END IF;
2532
2533
2534 END IF;
2535
2536 END IF;
2537 END IF;
2538
2539
2540 /* Pre Call to the Internal Type User Hook */
2541
2542 IF jtf_resource_utl.ok_to_execute(
2543 'JTF_RS_RESOURCE_PVT',
2544 'UPDATE_RESOURCE',
2545 'B',
2546 'I')
2547 THEN
2548 IF jtf_usr_hks.ok_to_execute(
2549 'JTF_RS_RESOURCE_PVT',
2550 'UPDATE_RESOURCE',
2551 'B',
2552 'I')
2553 THEN
2554
2555 jtf_rs_resource_iuhk.update_resource_pre(
2556 p_resource_id => l_resource_id,
2557 p_managing_emp_id => l_managing_emp_id,
2558 p_start_date_active => l_start_date_active,
2559 p_end_date_active => l_end_date_active,
2560 p_time_zone => l_time_zone,
2561 p_cost_per_hr => l_cost_per_hr,
2562 p_primary_language => l_primary_language,
2563 p_secondary_language => l_secondary_language,
2564 p_support_site_id => l_support_site_id,
2565 p_ies_agent_login => l_ies_agent_login,
2566 p_server_group_id => l_server_group_id,
2567 p_assigned_to_group_id => l_assigned_to_group_id,
2568 p_cost_center => l_cost_center,
2569 p_charge_to_cost_center => l_charge_to_cost_center,
2570 p_comp_currency_code => l_comp_currency_code,
2571 p_commissionable_flag => l_commissionable_flag,
2572 p_hold_reason_code => l_hold_reason_code,
2573 p_hold_payment => l_hold_payment,
2574 p_comp_service_team_id => l_comp_service_team_id,
2575 p_user_id => l_user_id,
2576 --p_location => l_location,
2577 x_return_status => x_return_status);
2578
2579 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2580
2581
2582 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
2583 fnd_msg_pub.add;
2584
2585 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2586 RAISE FND_API.G_EXC_ERROR;
2587 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2589 END IF;
2590
2591 END IF;
2592
2593 END IF;
2594 END IF;
2595
2596
2597 OPEN c_resource_update(l_resource_id);
2598
2599 FETCH c_resource_update INTO resource_rec;
2600
2601
2602 IF c_resource_update%NOTFOUND THEN
2603
2604 IF c_resource_update%ISOPEN THEN
2605
2606 CLOSE c_resource_update;
2607
2608 END IF;
2609
2610 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE');
2611 fnd_message.set_token('P_RESOURCE_ID', l_resource_id);
2612 fnd_msg_pub.add;
2613
2614 RAISE fnd_api.g_exc_error;
2615
2616
2617 END IF;
2618
2619 /* Validate the Managing Employee Id if specified */
2620
2621 IF l_managing_emp_id <> fnd_api.g_miss_num THEN
2622
2623 jtf_resource_utl.validate_employee_resource(
2624 p_emp_resource_id => l_managing_emp_id,
2625 p_emp_resource_number => null,
2626 x_return_status => x_return_status,
2627 x_emp_resource_id => l_managing_emp_id_out
2628 );
2629 -- added for NOCOPY
2630 l_managing_emp_id := l_managing_emp_id_out;
2631
2632 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2633
2634 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2635 RAISE FND_API.G_EXC_ERROR;
2636 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2638 END IF;
2639
2640 END IF;
2641
2642 END IF;
2643
2644
2645 /* Validate the Input Dates */
2646
2647 IF l_start_date_active <> fnd_api.g_miss_date OR
2648 l_end_date_active <> fnd_api.g_miss_date THEN
2649
2650 -- Code changes to fix bug 4171623. (G_MISS_DATE DOESN'T WORK PROPERLY ON JTF_RS_RESOURCE_PUB).
2651
2652 -- Changing the values being passed to "validate_input_dates" procedure,
2653 -- from l_end_date_active to resource_rec.end_date_active (same for start date)
2654 -- so that it validates the correct dates which its supposed to validate.
2655
2656 jtf_resource_utl.validate_input_dates(
2657 p_start_date_active => resource_rec.start_date_active,
2658 p_end_date_active => resource_rec.end_date_active,
2659 x_return_status => x_return_status
2660 );
2661
2662 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
2663
2664 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
2665 RAISE FND_API.G_EXC_ERROR;
2666 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
2667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2668 END IF;
2669
2670
2671 END IF;
2672
2673 END IF;
2674
2675 /* Validate that the resource dates cover the role related dates for the
2676 resource */
2677
2678 /* First part of the validation where the role relate end date active
2679 is not null */
2680
2681 OPEN c_related_role_dates_first(l_resource_id);
2682
2683 FETCH c_related_role_dates_first INTO l_min_start_date, l_max_end_date;
2684
2685
2686 IF l_min_start_date IS NOT NULL THEN
2687
2688 IF l_start_date_active <> FND_API.G_MISS_DATE AND
2689 l_min_start_date < l_start_date_active THEN
2690
2691 /* Resource Start Date out of range for the role related start dates of the resource */
2692
2693 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_ROLE_START_DATE');
2694 fnd_msg_pub.add;
2695
2696 RAISE fnd_api.g_exc_error;
2697
2698
2699 END IF;
2700
2701 IF ( l_end_date_active <> FND_API.G_MISS_DATE AND
2702 l_max_end_date > l_end_date_active AND
2703 l_end_date_active IS NOT NULL ) THEN
2704
2705 /* Resource End Date out of range for the role related End dates of the Resource */
2706
2707 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_ROLE_END_DATE');
2708 fnd_msg_pub.add;
2709
2710 RAISE fnd_api.g_exc_error;
2711
2712
2713 END IF;
2714
2715 END IF;
2716
2717
2718 /* Close the cursor */
2719
2720 IF c_related_role_dates_first%ISOPEN THEN
2721
2722 CLOSE c_related_role_dates_first;
2723
2724 END IF;
2725
2726
2727
2728 /* Second part of the validation where the role relate end date active
2729 is null */
2730
2731 OPEN c_related_role_dates_sec(l_resource_id);
2732
2733 FETCH c_related_role_dates_sec INTO l_min_start_date;
2734
2735
2736 IF l_min_start_date IS NOT NULL THEN
2737
2738 IF l_start_date_active <> FND_API.G_MISS_DATE AND
2739 l_min_start_date < l_start_date_active THEN
2740
2741 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_ROLE_START_DATE');
2742 fnd_msg_pub.add;
2743
2744 RAISE fnd_api.g_exc_error;
2745
2746
2747 END IF;
2748
2749 IF l_end_date_active <> FND_API.G_MISS_DATE AND
2750 l_end_date_active IS NOT NULL THEN
2751
2752 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_ROLE_END_DATE');
2753 fnd_msg_pub.add;
2754
2755 RAISE fnd_api.g_exc_error;
2756
2757
2758 END IF;
2759
2760 END IF;
2761
2762
2763 /* Close the cursor */
2764
2765 IF c_related_role_dates_sec%ISOPEN THEN
2766
2767 CLOSE c_related_role_dates_sec;
2768
2769 END IF;
2770
2771
2772
2773 /* Validate that the resource dates cover the group member role related dates for the
2774 resource */
2775
2776 /* First part of the validation where the group member role relate end date active
2777 is not null */
2778
2779 OPEN c_grp_mbr_role_dates_first(l_resource_id);
2780
2781 FETCH c_grp_mbr_role_dates_first INTO l_min_start_date, l_max_end_date;
2782
2783
2784 IF l_min_start_date IS NOT NULL THEN
2785
2786 IF l_start_date_active <> FND_API.G_MISS_DATE AND
2787 l_min_start_date < l_start_date_active THEN
2788
2789 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_GMBR_START_DATE');
2790 fnd_msg_pub.add;
2791
2792 RAISE fnd_api.g_exc_error;
2793
2794
2795 END IF;
2796
2797 IF ( l_end_date_active <> FND_API.G_MISS_DATE AND
2798 l_max_end_date > l_end_date_active AND
2799 l_end_date_active IS NOT NULL ) THEN
2800
2801 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_GMBR_END_DATE');
2802 fnd_msg_pub.add;
2803
2804 RAISE fnd_api.g_exc_error;
2805
2806 END IF;
2807
2808 END IF;
2809
2810
2811 /* Close the cursor */
2812
2813 IF c_grp_mbr_role_dates_first%ISOPEN THEN
2814
2815 CLOSE c_grp_mbr_role_dates_first;
2816
2817 END IF;
2818
2819
2820
2821 /* Second part of the validation where the member role relate end date active
2822 is null */
2823
2824 OPEN c_grp_mbr_role_dates_sec(l_resource_id);
2825
2826 FETCH c_grp_mbr_role_dates_sec INTO l_min_start_date;
2827
2828
2829 IF l_min_start_date IS NOT NULL THEN
2830
2831 IF l_start_date_active <> FND_API.G_MISS_DATE AND
2832 l_min_start_date < l_start_date_active THEN
2833
2834 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_GMBR_START_DATE');
2835 fnd_msg_pub.add;
2836
2837 RAISE fnd_api.g_exc_error;
2838
2839
2840 END IF;
2841
2842 IF l_end_date_active <> FND_API.G_MISS_DATE AND
2843 l_end_date_active IS NOT NULL THEN
2844
2845 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_GMBR_END_DATE');
2846 fnd_msg_pub.add;
2847
2848 RAISE fnd_api.g_exc_error;
2849
2850
2851 END IF;
2852
2853 END IF;
2854
2855
2856 /* Close the cursor */
2857
2858 IF c_grp_mbr_role_dates_sec%ISOPEN THEN
2859
2860 CLOSE c_grp_mbr_role_dates_sec;
2861
2862 END IF;
2863
2864
2865
2866 /* Validate that the resource dates cover the team member role related dates for the
2867 resource, where the team member is a resource */
2868
2869 /* First part of the validation where the team member role relate end date active
2870 is not null */
2871
2872 OPEN c_team_mbr_role_dates_first(l_resource_id);
2873
2874 FETCH c_team_mbr_role_dates_first INTO l_min_start_date, l_max_end_date;
2875
2876
2877 IF l_min_start_date IS NOT NULL THEN
2878
2879 IF l_start_date_active <> FND_API.G_MISS_DATE AND
2880 l_min_start_date < l_start_date_active THEN
2881
2882 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_TMBR_START_DATE');
2883 fnd_msg_pub.add;
2884
2885 RAISE fnd_api.g_exc_error;
2886
2887
2888 END IF;
2889
2890 IF ( l_end_date_active <> FND_API.G_MISS_DATE AND
2891 l_max_end_date > l_end_date_active AND
2892 l_end_date_active IS NOT NULL ) THEN
2893
2894 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_TMBR_END_DATE');
2895 fnd_msg_pub.add;
2896
2897 RAISE fnd_api.g_exc_error;
2898
2899
2900 END IF;
2901
2902 END IF;
2903
2904
2905
2906 /* Close the cursor */
2907
2908 IF c_team_mbr_role_dates_first%ISOPEN THEN
2909
2910 CLOSE c_team_mbr_role_dates_first;
2911
2912 END IF;
2913
2914
2915
2916 /* Second part of the validation where the member role relate end date active
2917 is null */
2918
2919 OPEN c_team_mbr_role_dates_sec(l_resource_id);
2920
2921 FETCH c_team_mbr_role_dates_sec INTO l_min_start_date;
2922
2923
2924 IF l_min_start_date IS NOT NULL THEN
2925
2926 IF l_start_date_active <> FND_API.G_MISS_DATE AND
2927 l_min_start_date < l_start_date_active THEN
2928
2929 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_TMBR_START_DATE');
2930 fnd_msg_pub.add;
2931
2932 RAISE fnd_api.g_exc_error;
2933
2934
2935 END IF;
2936
2937 IF l_end_date_active <> FND_API.G_MISS_DATE AND
2938 l_end_date_active IS NOT NULL THEN
2939
2940 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_TMBR_END_DATE');
2941 fnd_msg_pub.add;
2942
2943 RAISE fnd_api.g_exc_error;
2944
2945
2946 END IF;
2947
2948 END IF;
2949
2950
2951 /* Close the cursor */
2952
2953 IF c_team_mbr_role_dates_sec%ISOPEN THEN
2954
2955 CLOSE c_team_mbr_role_dates_sec;
2956
2957 END IF;
2958
2959
2960
2961 /* Validate that the resource dates cover the salesrep related dates for the
2962 resource */
2963
2964 /* First part of the validation where the salesrep end date active
2965 is not null */
2966
2967 OPEN c_salesrep_dates_first(l_resource_id);
2968
2969 FETCH c_salesrep_dates_first INTO l_min_start_date, l_max_end_date;
2970
2971
2972 IF l_min_start_date IS NOT NULL THEN
2973
2974 IF l_start_date_active <> FND_API.G_MISS_DATE AND
2975 l_min_start_date < l_start_date_active THEN
2976
2977 /* Resource Start Date out of range for the salesrep related start dates of the resource */
2978
2979 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_SRP_START_DATE');
2980 fnd_msg_pub.add;
2981
2982 RAISE fnd_api.g_exc_error;
2983
2984
2985 END IF;
2986
2987 IF ( l_end_date_active <> FND_API.G_MISS_DATE AND
2988 l_max_end_date > l_end_date_active AND
2989 l_end_date_active IS NOT NULL ) THEN
2990
2991 /* Resource End Date out of range for the salesrep related End dates of the Resource */
2992
2993 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_SRP_END_DATE');
2994 fnd_msg_pub.add;
2995
2996 RAISE fnd_api.g_exc_error;
2997
2998
2999 END IF;
3000
3001 END IF;
3002
3003
3004 /* Close the cursor */
3005
3006 IF c_salesrep_dates_first%ISOPEN THEN
3007
3008 CLOSE c_salesrep_dates_first;
3009
3010 END IF;
3011
3012
3013
3014 /* Second part of the validation where the role relate end date active
3015 is null */
3016
3017 OPEN c_salesrep_dates_sec(l_resource_id);
3018
3019 FETCH c_salesrep_dates_sec INTO l_min_start_date;
3020
3021
3022 IF l_min_start_date IS NOT NULL THEN
3023
3024 IF l_start_date_active <> FND_API.G_MISS_DATE AND
3025 l_min_start_date < l_start_date_active THEN
3026
3027 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_SRP_START_DATE');
3028 fnd_msg_pub.add;
3029
3030 RAISE fnd_api.g_exc_error;
3031
3032
3033 END IF;
3034
3035 IF l_end_date_active <> FND_API.G_MISS_DATE AND
3036 l_end_date_active IS NOT NULL THEN
3037
3038 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_SRP_END_DATE');
3039 fnd_msg_pub.add;
3040
3041 RAISE fnd_api.g_exc_error;
3042
3043
3044 END IF;
3045
3046 END IF;
3047
3048
3049 /* Close the cursor */
3050
3051 IF c_salesrep_dates_sec%ISOPEN THEN
3052
3053 CLOSE c_salesrep_dates_sec;
3054
3055 END IF;
3056
3057
3058
3059 /* Validate the Time Zone */
3060
3061 IF l_time_zone <> fnd_api.g_miss_num THEN
3062
3063 IF l_time_zone IS NOT NULL THEN
3064
3065 jtf_resource_utl.validate_time_zone(
3066 p_time_zone_id => l_time_zone,
3067 x_return_status => x_return_status
3068 );
3069
3070 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3071
3072 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3073 RAISE FND_API.G_EXC_ERROR;
3074 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3075 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3076 END IF;
3077
3078
3079 END IF;
3080
3081 END IF;
3082
3083 END IF;
3084
3085
3086
3087 /* Validate the Primary Language */
3088
3089 IF l_primary_language <> fnd_api.g_miss_char THEN
3090
3091 IF l_primary_language IS NOT NULL THEN
3092
3093 jtf_resource_utl.validate_nls_language(
3094 p_nls_language => l_primary_language,
3095 x_return_status => x_return_status
3096 );
3097
3098 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3099
3100 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3101 RAISE FND_API.G_EXC_ERROR;
3102 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3104 END IF;
3105
3106
3107 END IF;
3108
3109 END IF;
3110
3111 END IF;
3112
3113
3114
3115 /* Validate the Secondary Language */
3116
3117 IF l_secondary_language <> fnd_api.g_miss_char THEN
3118
3119 IF l_secondary_language IS NOT NULL THEN
3120
3121 jtf_resource_utl.validate_nls_language(
3122 p_nls_language => l_secondary_language,
3123 x_return_status => x_return_status
3124 );
3125
3126 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3127
3128 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3129 RAISE FND_API.G_EXC_ERROR;
3130 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3132 END IF;
3133
3134
3135 END IF;
3136
3137 END IF;
3138
3139 END IF;
3140
3141
3142
3143 /* Validate the Support Site */
3144
3145 IF l_support_site_id <> fnd_api.g_miss_num THEN
3146
3147 IF l_support_site_id IS NOT NULL THEN
3148
3149 jtf_resource_utl.validate_support_site_id(
3150 p_support_site_id => l_support_site_id,
3151 x_return_status => x_return_status
3152 );
3153
3154 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3155
3156 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3157 RAISE FND_API.G_EXC_ERROR;
3158 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3160 END IF;
3161
3162
3163 END IF;
3164
3165 END IF;
3166
3167 END IF;
3168
3169
3170
3171 /* Validate the Server Group. */
3172
3173 IF l_server_group_id <> fnd_api.g_miss_num THEN
3174
3175 jtf_resource_utl.validate_server_group(
3176 p_server_group_id => l_server_group_id,
3177 p_server_group_name => null,
3178 x_return_status => x_return_status,
3179 x_server_group_id => l_server_group_id_out
3180 );
3181 -- added for NOCOPY
3182 l_server_group_id := l_server_group_id_out;
3183
3184 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3185
3186 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3187 RAISE FND_API.G_EXC_ERROR;
3188 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3189 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3190 END IF;
3191
3192
3193 END IF;
3194
3195 END IF;
3196
3197
3198
3199 /* Validate the assigned_to_group_id if specified */
3200
3201 IF l_assigned_to_group_id <> fnd_api.g_miss_num THEN
3202
3203 IF l_assigned_to_group_id IS NOT NULL THEN
3204
3205 OPEN c_assigned_to_group_id(l_assigned_to_group_id);
3206
3207 FETCH c_assigned_to_group_id INTO l_assigned_to_group_id;
3208
3209
3210 IF c_assigned_to_group_id%NOTFOUND THEN
3211
3212 -- dbms_output.put_line('Invalid Assigned To Group Id');
3213
3214 fnd_message.set_name('JTF', 'JTF_RS_ERR_ASSIGN_TO_GRP_ID');
3215 fnd_message.set_token('P_ASSIGNED_TO_GROUP_ID', l_assigned_to_group_id);
3216 fnd_msg_pub.add;
3217
3218 RAISE fnd_api.g_exc_error;
3219
3220
3221 END IF;
3222
3223
3224 /* Close the cursor */
3225
3226 IF c_assigned_to_group_id%ISOPEN THEN
3227
3228 CLOSE c_assigned_to_group_id;
3229
3230 END IF;
3231
3232 END IF;
3233
3234 END IF;
3235
3236
3237
3238 /* Validate the Comp Currency Code */
3239
3240 IF l_comp_currency_code <> fnd_api.g_miss_char THEN
3241
3242 IF l_comp_currency_code IS NOT NULL THEN
3243
3244 jtf_resource_utl.validate_currency_code(
3245 p_currency_code => l_comp_currency_code,
3246 x_return_status => x_return_status
3247 );
3248
3249 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3250
3251 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3252 RAISE FND_API.G_EXC_ERROR;
3253 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3255 END IF;
3256
3257
3258 END IF;
3259
3260 END IF;
3261
3262 END IF;
3263
3264
3265
3266 /* Validate the value of the commisionable flag */
3267
3268 IF l_commissionable_flag <> fnd_api.g_miss_char THEN
3269
3270 IF l_commissionable_flag <> 'Y' AND l_commissionable_flag <> 'N' THEN
3271
3272 -- dbms_output.put_line('Commissionable Flag should either be ''Y'' or ''N'' ');
3273
3274 fnd_message.set_name('JTF', 'JTF_RS_INVALID_FLAG_VALUE');
3275 fnd_msg_pub.add;
3276
3277 RAISE fnd_api.g_exc_error;
3278
3279
3280 END IF;
3281
3282 END IF;
3283
3284
3285
3286 /* Validate the value of the Hold Payment flag */
3287
3288 IF l_hold_payment <> fnd_api.g_miss_char THEN
3289
3290 IF l_hold_payment <> 'Y' AND l_hold_payment <> 'N' THEN
3291
3292 -- dbms_output.put_line('Hold Payment should either be ''Y'' or ''N'' ');
3293
3294 fnd_message.set_name('JTF', 'JTF_RS_INVALID_FLAG_VALUE');
3295 fnd_msg_pub.add;
3296
3297 RAISE fnd_api.g_exc_error;
3298
3299
3300 END IF;
3301
3302 END IF;
3303
3304
3305
3306 /* Validate the Hold Reason Code */
3307
3308 IF l_hold_reason_code <> fnd_api.g_miss_char THEN
3309
3310 IF l_hold_reason_code IS NOT NULL THEN
3311
3312 jtf_resource_utl.validate_hold_reason_code(
3313 p_hold_reason_code => l_hold_reason_code,
3314 x_return_status => x_return_status
3315 );
3316
3317 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3318
3319 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3320 RAISE FND_API.G_EXC_ERROR;
3321 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3322 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3323 END IF;
3324
3325
3326 END IF;
3327
3328 END IF;
3329
3330 END IF;
3331
3332
3333 /* Validate that the user_id should only be specified in case of
3334 'EMPLOYEE', 'PARTY', 'SUPPLIER_CONTACT' categories */
3335
3336 IF l_user_id <> fnd_api.g_miss_num THEN
3337
3338 /* Removed 'WORKER' from the below code to fix bug # 3455951 */
3339 IF resource_rec.category NOT IN ('EMPLOYEE', 'PARTY', 'SUPPLIER_CONTACT') AND l_user_id IS NOT NULL THEN
3340
3341 fnd_message.set_name('JTF', 'JTF_RS_USERID_ERROR');
3342 fnd_msg_pub.add;
3343
3344 RAISE fnd_api.g_exc_error;
3345
3346
3347 END IF;
3348
3349 END IF;
3350
3351
3352 IF l_user_id <> fnd_api.g_miss_num THEN
3353
3354 /* Validate the User Id if specified */
3355
3356 IF l_user_id IS NOT NULL THEN
3357
3358 jtf_resource_utl.validate_user_id(
3359 p_user_id => l_user_id,
3360 p_category => resource_rec.category,
3361 p_source_id => resource_rec.source_id,
3362 x_return_status => x_return_status
3363 );
3364
3365 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3366
3367 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3368 RAISE FND_API.G_EXC_ERROR;
3369 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3371 END IF;
3372
3373 else
3374
3375 OPEN c_validate_user_id(l_resource_id,l_user_id);
3376
3377 FETCH c_validate_user_id INTO l_check_flag
3378 ;
3379
3380
3381 IF c_validate_user_id%FOUND THEN
3382
3383 -- dbms_output.put_line('duplicate user Id');
3384
3385 fnd_message.set_name('JTF', 'JTF_RS_ERR_DUPLICATE_USER_ID');
3386 fnd_message.set_token('P_USER_ID', l_user_id);
3387 fnd_msg_pub.add;
3388
3389 RAISE fnd_api.g_exc_error;
3390
3391 END IF;
3392
3393
3394 /* Close the cursor */
3395
3396 CLOSE c_validate_user_id;
3397
3398
3399
3400 END IF;
3401
3402 END IF;
3403
3404 END IF;
3405
3406
3407 /* Validate the Comp Service Team Id if specified */
3408
3409 IF l_comp_service_team_id <> fnd_api.g_miss_num THEN
3410
3411 IF l_comp_service_team_id IS NOT NULL THEN
3412
3413 jtf_resource_utl.validate_resource_team(
3414 p_team_id => l_comp_service_team_id,
3415 p_team_number => null,
3416 x_return_status => x_return_status,
3417 x_team_id => l_comp_service_team_id_out
3418 );
3419 -- added for NOCOPY
3420 l_comp_service_team_id := l_comp_service_team_id_out;
3421
3422
3423 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3424
3425 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3426 RAISE FND_API.G_EXC_ERROR;
3427 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3429 END IF;
3430
3431
3432 END IF;
3433
3434 END IF;
3435
3436 END IF;
3437
3438
3439 /* Call the lock row procedure to ensure that the object version number
3440 is still valid. */
3441
3442 BEGIN
3443
3444 jtf_rs_resource_extns_pkg.lock_row(
3445 x_resource_id => l_resource_id,
3446 x_object_version_number => p_object_version_num
3447 );
3448
3449 --dbms_output.put_line ('After Call to Lock Row Procedure');
3450
3451
3452 EXCEPTION
3453
3454 WHEN OTHERS THEN
3455
3456 -- dbms_output.put_line('Error in Locking the Row');
3457
3458 x_return_status := fnd_api.g_ret_sts_unexp_error;
3459
3460 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
3461 fnd_msg_pub.add;
3462
3463 RAISE fnd_api.g_exc_error;
3464
3465
3466 END;
3467
3468 --dbms_output.put_line ('Before Call to Audit API');
3469
3470
3471 /* Make a call to the Resource Audit API */
3472
3473 jtf_rs_resource_extns_aud_pvt.update_resource
3474 (P_API_VERSION => 1,
3475 P_INIT_MSG_LIST => fnd_api.g_false,
3476 P_COMMIT => fnd_api.g_false,
3477 P_RESOURCE_ID => l_resource_id,
3478 P_RESOURCE_NUMBER => resource_rec.resource_number,
3479 P_CATEGORY => resource_rec.category,
3480 P_SOURCE_ID => resource_rec.source_id,
3481 P_ADDRESS_ID => resource_rec.address_id,
3482 P_CONTACT_ID => resource_rec.contact_id,
3483 P_MANAGING_EMP_ID => resource_rec.managing_emp_id,
3484 P_START_DATE_ACTIVE => resource_rec.start_date_active,
3485 P_END_DATE_ACTIVE => resource_rec.end_date_active,
3486 P_TIME_ZONE => resource_rec.time_zone,
3487 P_COST_PER_HR => resource_rec.cost_per_hr,
3488 P_PRIMARY_LANGUAGE => resource_rec.primary_language,
3489 P_SECONDARY_LANGUAGE => resource_rec.secondary_language,
3490 P_SUPPORT_SITE_ID => resource_rec.support_site_id,
3491 P_IES_AGENT_LOGIN => resource_rec.ies_agent_login,
3492 P_SERVER_GROUP_ID => resource_rec.server_group_id,
3493 P_ASSIGNED_TO_GROUP_ID => resource_rec.assigned_to_group_id,
3494 P_COST_CENTER => resource_rec.cost_center,
3495 P_CHARGE_TO_COST_CENTER => resource_rec.charge_to_cost_center,
3496 P_COMP_CURRENCY_CODE => resource_rec.comp_currency_code,
3497 P_COMMISSIONABLE_FLAG => resource_rec.commissionable_flag,
3498 P_HOLD_REASON_CODE => resource_rec.hold_reason_code,
3499 P_HOLD_PAYMENT => resource_rec.hold_payment,
3500 P_COMP_SERVICE_TEAM_ID => resource_rec.comp_service_team_id,
3501 P_USER_ID => resource_rec.user_id,
3502 P_TRANSACTION_NUMBER => resource_rec.transaction_number,
3503 -- P_LOCATION => resource_rec.location,
3504 P_OBJECT_VERSION_NUMBER => p_object_version_num + 1,
3505 X_RETURN_STATUS => x_return_status,
3506 X_MSG_COUNT => x_msg_count,
3507 X_MSG_DATA => x_msg_data
3508 );
3509
3510 --dbms_output.put_line ('After Call to Audit API');
3511
3512 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3513
3514 -- dbms_output.put_line('Failed status from call to audit procedure');
3515
3516 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3517 RAISE FND_API.G_EXC_ERROR;
3518 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3519 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3520 END IF;
3521
3522
3523 END IF;
3524
3525
3526 BEGIN
3527
3528 /* Increment the object version number */
3529
3530 l_object_version_num := p_object_version_num + 1;
3531
3532 --dbms_output.put_line ('Before Call to Update Row Table Handler ');
3533
3534
3535 /* Update the row into the table by calling the table handler. */
3536
3537 jtf_rs_resource_extns_pkg.update_row(
3538 x_resource_id => l_resource_id,
3539 x_category => resource_rec.category,
3540 x_resource_number => resource_rec.resource_number,
3541 x_source_id => resource_rec.source_id,
3542 x_address_id => resource_rec.address_id,
3543 x_contact_id => resource_rec.contact_id,
3544 x_managing_employee_id => resource_rec.managing_emp_id,
3545 x_start_date_active => resource_rec.start_date_active,
3546 x_end_date_active => resource_rec.end_date_active,
3547 x_time_zone => resource_rec.time_zone,
3548 x_cost_per_hr => resource_rec.cost_per_hr,
3549 x_primary_language => resource_rec.primary_language,
3550 x_secondary_language => resource_rec.secondary_language,
3551 x_support_site_id => resource_rec.support_site_id,
3552 x_ies_agent_login => resource_rec.ies_agent_login,
3553 x_server_group_id => resource_rec.server_group_id,
3554 x_assigned_to_group_id => resource_rec.assigned_to_group_id,
3555 x_cost_center => resource_rec.cost_center,
3556 x_charge_to_cost_center => resource_rec.charge_to_cost_center,
3557 x_compensation_currency_code => resource_rec.comp_currency_code,
3558 x_commissionable_flag => resource_rec.commissionable_flag,
3559 x_hold_reason_code => resource_rec.hold_reason_code,
3560 x_hold_payment => resource_rec.hold_payment,
3561 x_comp_service_team_id => resource_rec.comp_service_team_id,
3562 x_user_id => resource_rec.user_id,
3563 --x_location => resource_rec.location,
3564 x_transaction_number => resource_rec.transaction_number,
3565 x_object_version_number => l_object_version_num,
3566 x_attribute1 => resource_rec.attribute1,
3567 x_attribute2 => resource_rec.attribute2,
3568 x_attribute3 => resource_rec.attribute3,
3569 x_attribute4 => resource_rec.attribute4,
3570 x_attribute5 => resource_rec.attribute5,
3571 x_attribute6 => resource_rec.attribute6,
3572 x_attribute7 => resource_rec.attribute7,
3573 x_attribute8 => resource_rec.attribute8,
3574 x_attribute9 => resource_rec.attribute9,
3575 x_attribute10 => resource_rec.attribute10,
3576 x_attribute11 => resource_rec.attribute11,
3577 x_attribute12 => resource_rec.attribute12,
3578 x_attribute13 => resource_rec.attribute13,
3579 x_attribute14 => resource_rec.attribute14,
3580 x_attribute15 => resource_rec.attribute15,
3581 x_attribute_category => resource_rec.attribute_category,
3582 x_last_update_date => SYSDATE,
3583 x_last_updated_by => jtf_resource_utl.updated_by,
3584 x_last_update_login => jtf_resource_utl.login_id
3585 );
3586
3587 --dbms_output.put_line ('After Call to Update Row Table Handler ');
3588
3589 /* Return the new value of the object version number */
3590
3591 p_object_version_num := l_object_version_num;
3592
3593 EXCEPTION
3594
3595 WHEN NO_DATA_FOUND THEN
3596
3597 -- dbms_output.put_line('Error in Table Handler');
3598
3599 IF c_resource_update%ISOPEN THEN
3600
3601 CLOSE c_resource_update;
3602
3603 END IF;
3604
3605 x_return_status := fnd_api.g_ret_sts_unexp_error;
3606
3607 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
3608 fnd_msg_pub.add;
3609
3610 RAISE fnd_api.g_exc_error;
3611
3612
3613 END;
3614
3615 -- dbms_output.put_line('Resource Successfully Updated');
3616
3617
3618 /* Close the cursors */
3619
3620 IF c_resource_update%ISOPEN THEN
3621
3622 CLOSE c_resource_update;
3623
3624 END IF;
3625
3626
3627 /* Make the post processing call to the user hooks */
3628
3629 /* Post Call to the Customer Type User Hook */
3630
3631 IF jtf_resource_utl.ok_to_execute(
3632 'JTF_RS_RESOURCE_PVT',
3633 'UPDATE_RESOURCE',
3634 'A',
3635 'C')
3636 THEN
3637 IF jtf_usr_hks.ok_to_execute(
3638 'JTF_RS_RESOURCE_PVT',
3639 'UPDATE_RESOURCE',
3640 'A',
3641 'C')
3642 THEN
3643
3644 jtf_rs_resource_cuhk.update_resource_post(
3645 p_resource_id => l_resource_id,
3646 p_managing_emp_id => l_managing_emp_id,
3647 p_start_date_active => l_start_date_active,
3648 p_end_date_active => l_end_date_active,
3649 p_time_zone => l_time_zone,
3650 p_cost_per_hr => l_cost_per_hr,
3651 p_primary_language => l_primary_language,
3652 p_secondary_language => l_secondary_language,
3653 p_support_site_id => l_support_site_id,
3654 p_ies_agent_login => l_ies_agent_login,
3655 p_server_group_id => l_server_group_id,
3656 p_assigned_to_group_id => l_assigned_to_group_id,
3657 p_cost_center => l_cost_center,
3658 p_charge_to_cost_center => l_charge_to_cost_center,
3659 p_comp_currency_code => l_comp_currency_code,
3660 p_commissionable_flag => l_commissionable_flag,
3661 p_hold_reason_code => l_hold_reason_code,
3662 p_hold_payment => l_hold_payment,
3663 p_comp_service_team_id => l_comp_service_team_id,
3664 p_user_id => l_user_id,
3665 --p_location => l_location,
3666 x_return_status => x_return_status);
3667
3668 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3669
3670 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
3671
3672 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
3673 fnd_msg_pub.add;
3674
3675 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3676 RAISE FND_API.G_EXC_ERROR;
3677 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3679 END IF;
3680
3681
3682 END IF;
3683
3684 END IF;
3685 END IF;
3686
3687
3688 /* Post Call to the Vertical Type User Hook */
3689
3690 IF jtf_resource_utl.ok_to_execute(
3691 'JTF_RS_RESOURCE_PVT',
3692 'UPDATE_RESOURCE',
3693 'A',
3694 'V')
3695 THEN
3696 IF jtf_usr_hks.ok_to_execute(
3697 'JTF_RS_RESOURCE_PVT',
3698 'UPDATE_RESOURCE',
3699 'A',
3700 'V')
3701 THEN
3702
3703 jtf_rs_resource_vuhk.update_resource_post(
3704 p_resource_id => l_resource_id,
3705 p_managing_emp_id => l_managing_emp_id,
3706 p_start_date_active => l_start_date_active,
3707 p_end_date_active => l_end_date_active,
3708 p_time_zone => l_time_zone,
3709 p_cost_per_hr => l_cost_per_hr,
3710 p_primary_language => l_primary_language,
3711 p_secondary_language => l_secondary_language,
3712 p_support_site_id => l_support_site_id,
3713 p_ies_agent_login => l_ies_agent_login,
3714 p_server_group_id => l_server_group_id,
3715 p_assigned_to_group_id => l_assigned_to_group_id,
3716 p_cost_center => l_cost_center,
3717 p_charge_to_cost_center => l_charge_to_cost_center,
3718 p_comp_currency_code => l_comp_currency_code,
3719 p_commissionable_flag => l_commissionable_flag,
3720 p_hold_reason_code => l_hold_reason_code,
3721 p_hold_payment => l_hold_payment,
3722 p_comp_service_team_id => l_comp_service_team_id,
3723 p_user_id => l_user_id,
3724 --p_location => l_location,
3725 x_return_status => x_return_status);
3726
3727 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3728
3729 -- dbms_output.put_line('Returned Error status from the Post Vertical User Hook');
3730
3731
3732 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
3733 fnd_msg_pub.add;
3734
3735 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3736 RAISE FND_API.G_EXC_ERROR;
3737 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3738 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3739 END IF;
3740
3741
3742 END IF;
3743
3744 END IF;
3745 END IF;
3746
3747
3748 /* Post Call to the Internal Type User Hook */
3749
3750 IF jtf_resource_utl.ok_to_execute(
3751 'JTF_RS_RESOURCE_PVT',
3752 'UPDATE_RESOURCE',
3753 'A',
3754 'I')
3755 THEN
3756 IF jtf_usr_hks.ok_to_execute(
3757 'JTF_RS_RESOURCE_PVT',
3758 'UPDATE_RESOURCE',
3759 'A',
3760 'I')
3761 THEN
3762
3763 jtf_rs_resource_iuhk.update_resource_post(
3764 p_resource_id => l_resource_id,
3765 p_managing_emp_id => l_managing_emp_id,
3766 p_start_date_active => l_start_date_active,
3767 p_end_date_active => l_end_date_active,
3768 p_time_zone => l_time_zone,
3769 p_cost_per_hr => l_cost_per_hr,
3770 p_primary_language => l_primary_language,
3771 p_secondary_language => l_secondary_language,
3772 p_support_site_id => l_support_site_id,
3773 p_ies_agent_login => l_ies_agent_login,
3774 p_server_group_id => l_server_group_id,
3775 p_assigned_to_group_id => l_assigned_to_group_id,
3776 p_cost_center => l_cost_center,
3777 p_charge_to_cost_center => l_charge_to_cost_center,
3778 p_comp_currency_code => l_comp_currency_code,
3779 p_commissionable_flag => l_commissionable_flag,
3780 p_hold_reason_code => l_hold_reason_code,
3781 p_hold_payment => l_hold_payment,
3782 p_comp_service_team_id => l_comp_service_team_id,
3783 p_user_id => l_user_id,
3784 --p_location => l_location,
3785 x_return_status => x_return_status);
3786
3787 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3788
3789 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
3790
3791
3792 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
3793 fnd_msg_pub.add;
3794
3795 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3796 RAISE FND_API.G_EXC_ERROR;
3797 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3799 END IF;
3800
3801 END IF;
3802
3803 END IF;
3804 END IF;
3805
3806
3807 /* Standard call for Message Generation */
3808
3809 IF jtf_resource_utl.ok_to_execute(
3810 'JTF_RS_RESOURCE_PVT',
3811 'UPDATE_RESOURCE',
3812 'M',
3813 'M')
3814 THEN
3815 IF jtf_usr_hks.ok_to_execute(
3816 'JTF_RS_RESOURCE_PVT',
3817 'UPDATE_RESOURCE',
3818 'M',
3819 'M')
3820 THEN
3821
3822 IF (jtf_rs_resource_cuhk.ok_to_generate_msg(
3823 p_resource_id => l_resource_id,
3824 x_return_status => x_return_status) )
3825 THEN
3826
3827 /* Get the bind data id for the Business Object Instance */
3828
3829 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
3830
3831
3832 /* Set bind values for the bind variables in the Business Object SQL */
3833
3834 jtf_usr_hks.load_bind_data(l_bind_data_id, 'resource_id', l_resource_id, 'S', 'N');
3835
3836
3837 /* Call the message generation API */
3838
3839 jtf_usr_hks.generate_message(
3840 p_prod_code => 'RS',
3841 p_bus_obj_code => 'RES',
3842 p_action_code => 'U',
3843 p_bind_data_id => l_bind_data_id,
3844 x_return_code => x_return_status);
3845
3846
3847 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
3848
3849 -- dbms_output.put_line('Returned Error status from the Message Generation API');
3850
3851
3852 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
3853 fnd_msg_pub.add;
3854
3855 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
3856 RAISE FND_API.G_EXC_ERROR;
3857 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
3858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3859 END IF;
3860
3861
3862 END IF;
3863
3864 END IF;
3865
3866 END IF;
3867 END IF;
3868
3869
3870 IF fnd_api.to_boolean(p_commit) THEN
3871
3872 COMMIT WORK;
3873
3874 END IF;
3875
3876 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
3877
3878
3879 EXCEPTION
3880
3881 WHEN fnd_api.g_exc_error THEN
3882 ROLLBACK TO update_resource_pvt;
3883 x_return_status := fnd_api.g_ret_sts_error;
3884 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3885 p_data => x_msg_data);
3886 WHEN fnd_api.g_exc_unexpected_error THEN
3887 ROLLBACK TO update_resource_pvt;
3888 x_return_status := fnd_api.g_ret_sts_unexp_error;
3889 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3890 p_data => x_msg_data);
3891 WHEN OTHERS THEN
3892 ROLLBACK TO update_resource_pvt;
3893 -- The below lines removed as a part of fixing GSCC errors in R12 for jtfrspub.pls
3894 -- IF NOT(jtf_resource_utl.check_access(l_value))
3895 -- THEN
3896 -- IF(l_value = 'XMLGEN')
3897 -- THEN
3898 -- fnd_message.set_name ('JTF', 'JTF_RS_XMLGEN_ERR');
3899 -- FND_MSG_PUB.add;
3900 -- ELSIF(l_value = 'JTF_USR_HKS')
3901 -- THEN
3902 -- fnd_message.set_name ('JTF', 'JTF_RS_JUHK_ERR');
3903 -- FND_MSG_PUB.add;
3904 -- END IF;
3905 -- ELSE
3906 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
3907 fnd_message.set_token('P_SQLCODE',SQLCODE);
3908 fnd_message.set_token('P_SQLERRM',SQLERRM);
3909 fnd_message.set_token('P_API_NAME', l_api_name);
3910 FND_MSG_PUB.add;
3911 -- END IF;
3912 x_return_status := fnd_api.g_ret_sts_unexp_error;
3913 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
3914 p_data => x_msg_data);
3915
3916
3917 END update_resource;
3918
3919 /* Overloaded Procedure to update the resource for Resource Synchronization */
3920
3921 PROCEDURE update_resource
3922 (P_API_VERSION IN NUMBER,
3923 P_INIT_MSG_LIST IN VARCHAR2,
3924 P_COMMIT IN VARCHAR2,
3925 P_RESOURCE_ID IN JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE,
3926 P_MANAGING_EMP_ID IN JTF_RS_RESOURCE_EXTNS.MANAGING_EMPLOYEE_ID%TYPE,
3927 P_START_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.START_DATE_ACTIVE%TYPE,
3928 P_END_DATE_ACTIVE IN JTF_RS_RESOURCE_EXTNS.END_DATE_ACTIVE%TYPE,
3929 P_TIME_ZONE IN JTF_RS_RESOURCE_EXTNS.TIME_ZONE%TYPE,
3930 P_COST_PER_HR IN JTF_RS_RESOURCE_EXTNS.COST_PER_HR%TYPE,
3931 P_PRIMARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.PRIMARY_LANGUAGE%TYPE,
3932 P_SECONDARY_LANGUAGE IN JTF_RS_RESOURCE_EXTNS.SECONDARY_LANGUAGE%TYPE,
3933 P_SUPPORT_SITE_ID IN JTF_RS_RESOURCE_EXTNS.SUPPORT_SITE_ID%TYPE,
3934 P_IES_AGENT_LOGIN IN JTF_RS_RESOURCE_EXTNS.IES_AGENT_LOGIN%TYPE,
3935 P_SERVER_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.SERVER_GROUP_ID%TYPE,
3936 P_ASSIGNED_TO_GROUP_ID IN JTF_RS_RESOURCE_EXTNS.ASSIGNED_TO_GROUP_ID%TYPE,
3937 P_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.COST_CENTER%TYPE,
3938 P_CHARGE_TO_COST_CENTER IN JTF_RS_RESOURCE_EXTNS.CHARGE_TO_COST_CENTER%TYPE,
3939 P_COMP_CURRENCY_CODE IN JTF_RS_RESOURCE_EXTNS.COMPENSATION_CURRENCY_CODE%TYPE,
3940 P_COMMISSIONABLE_FLAG IN JTF_RS_RESOURCE_EXTNS.COMMISSIONABLE_FLAG%TYPE,
3941 P_HOLD_REASON_CODE IN JTF_RS_RESOURCE_EXTNS.HOLD_REASON_CODE%TYPE,
3942 P_HOLD_PAYMENT IN JTF_RS_RESOURCE_EXTNS.HOLD_PAYMENT%TYPE,
3943 P_COMP_SERVICE_TEAM_ID IN JTF_RS_RESOURCE_EXTNS.COMP_SERVICE_TEAM_ID%TYPE,
3944 P_USER_ID IN JTF_RS_RESOURCE_EXTNS.USER_ID%TYPE,
3945 --P_LOCATION IN MDSYS.SDO_GEOMETRY,
3946 P_ATTRIBUTE1 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE1%TYPE,
3947 P_ATTRIBUTE2 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE2%TYPE,
3948 P_ATTRIBUTE3 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE3%TYPE,
3949 P_ATTRIBUTE4 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE4%TYPE,
3950 P_ATTRIBUTE5 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE5%TYPE,
3951 P_ATTRIBUTE6 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE6%TYPE,
3952 P_ATTRIBUTE7 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE7%TYPE,
3953 P_ATTRIBUTE8 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE8%TYPE,
3954 P_ATTRIBUTE9 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE9%TYPE,
3955 P_ATTRIBUTE10 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE10%TYPE,
3956 P_ATTRIBUTE11 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE11%TYPE,
3957 P_ATTRIBUTE12 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE12%TYPE,
3958 P_ATTRIBUTE13 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE13%TYPE,
3959 P_ATTRIBUTE14 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE14%TYPE,
3960 P_ATTRIBUTE15 IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE15%TYPE,
3961 P_ATTRIBUTE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.ATTRIBUTE_CATEGORY%TYPE,
3962 P_RESOURCE_NAME IN JTF_RS_RESOURCE_EXTNS_TL.RESOURCE_NAME%TYPE,
3963 P_SOURCE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_NAME%TYPE,
3964 P_SOURCE_NUMBER IN JTF_RS_RESOURCE_EXTNS.SOURCE_NUMBER%TYPE,
3965 P_SOURCE_JOB_TITLE IN JTF_RS_RESOURCE_EXTNS.SOURCE_JOB_TITLE%TYPE,
3966 P_SOURCE_EMAIL IN JTF_RS_RESOURCE_EXTNS.SOURCE_EMAIL%TYPE,
3967 P_SOURCE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PHONE%TYPE,
3968 P_SOURCE_ORG_ID IN NUMBER,
3969 P_SOURCE_ORG_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_ORG_NAME%TYPE,
3970 P_SOURCE_ADDRESS1 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS1%TYPE,
3971 P_SOURCE_ADDRESS2 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS2%TYPE,
3972 P_SOURCE_ADDRESS3 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS3%TYPE,
3973 P_SOURCE_ADDRESS4 IN JTF_RS_RESOURCE_EXTNS.SOURCE_ADDRESS4%TYPE,
3974 P_SOURCE_CITY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CITY%TYPE,
3975 P_SOURCE_POSTAL_CODE IN JTF_RS_RESOURCE_EXTNS.SOURCE_POSTAL_CODE%TYPE,
3976 P_SOURCE_STATE IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATE%TYPE,
3977 P_SOURCE_PROVINCE IN JTF_RS_RESOURCE_EXTNS.SOURCE_PROVINCE%TYPE,
3978 P_SOURCE_COUNTY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTY%TYPE,
3979 P_SOURCE_COUNTRY IN JTF_RS_RESOURCE_EXTNS.SOURCE_COUNTRY%TYPE,
3980 P_SOURCE_MGR_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_ID%type,
3981 P_SOURCE_MGR_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MGR_NAME%type,
3982 P_SOURCE_BUSINESS_GRP_ID IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_ID%type,
3983 P_SOURCE_BUSINESS_GRP_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_BUSINESS_GRP_NAME%type,
3984 P_SOURCE_FIRST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_FIRST_NAME%TYPE,
3985 P_SOURCE_LAST_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_LAST_NAME%TYPE,
3986 P_SOURCE_MIDDLE_NAME IN JTF_RS_RESOURCE_EXTNS.SOURCE_MIDDLE_NAME%TYPE,
3987 P_SOURCE_CATEGORY IN JTF_RS_RESOURCE_EXTNS.SOURCE_CATEGORY%TYPE,
3988 P_SOURCE_STATUS IN JTF_RS_RESOURCE_EXTNS.SOURCE_STATUS%TYPE,
3989 P_SOURCE_OFFICE IN JTF_RS_RESOURCE_EXTNS.SOURCE_OFFICE%TYPE,
3990 P_SOURCE_LOCATION IN JTF_RS_RESOURCE_EXTNS.SOURCE_LOCATION%TYPE,
3991 P_SOURCE_MAILSTOP IN JTF_RS_RESOURCE_EXTNS.SOURCE_MAILSTOP%TYPE,
3992 P_ADDRESS_ID IN JTF_RS_RESOURCE_EXTNS.ADDRESS_ID%TYPE,
3993 P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_RESOURCE_EXTNS.OBJECT_VERSION_NUMBER%TYPE,
3994 P_USER_NAME IN VARCHAR2,
3995 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
3996 X_MSG_COUNT OUT NOCOPY NUMBER,
3997 X_MSG_DATA OUT NOCOPY VARCHAR2,
3998 P_SOURCE_MOBILE_PHONE IN JTF_RS_RESOURCE_EXTNS.SOURCE_MOBILE_PHONE%TYPE,
3999 P_SOURCE_PAGER IN JTF_RS_RESOURCE_EXTNS.SOURCE_PAGER%TYPE
4000 ) IS
4001
4002 l_api_version CONSTANT NUMBER := 1.0;
4003 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
4004 /* Moved the initial assignment of below variables to inside begin */
4005 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
4006 l_managing_emp_id jtf_rs_resource_extns.managing_employee_id%TYPE;
4007 l_start_date_active jtf_rs_resource_extns.start_date_active%TYPE;
4008 l_end_date_active jtf_rs_resource_extns.end_date_active%TYPE;
4009 l_time_zone jtf_rs_resource_extns.time_zone%TYPE;
4010 l_cost_per_hr jtf_rs_resource_extns.cost_per_hr%TYPE;
4011 l_primary_language jtf_rs_resource_extns.primary_language%TYPE;
4012 l_secondary_language jtf_rs_resource_extns.secondary_language%TYPE;
4013 l_support_site_id jtf_rs_resource_extns.support_site_id%TYPE;
4014 l_ies_agent_login jtf_rs_resource_extns.ies_agent_login%TYPE;
4015 l_server_group_id jtf_rs_resource_extns.server_group_id%TYPE;
4016 l_assigned_to_group_id jtf_rs_resource_extns.assigned_to_group_id%TYPE;
4017 l_cost_center jtf_rs_resource_extns.cost_center%TYPE;
4018 l_charge_to_cost_center jtf_rs_resource_extns.charge_to_cost_center%TYPE;
4019 l_comp_currency_code jtf_rs_resource_extns.compensation_currency_code%TYPE;
4020 l_commissionable_flag jtf_rs_resource_extns.commissionable_flag%TYPE;
4021 l_hold_reason_code jtf_rs_resource_extns.hold_reason_code%TYPE;
4022 l_hold_payment jtf_rs_resource_extns.hold_payment%TYPE;
4023 l_comp_service_team_id jtf_rs_resource_extns.comp_service_team_id%TYPE;
4024 l_user_id jtf_rs_resource_extns.user_id%TYPE;
4025 --l_location mdsys.sdo_geometry := p_location;
4026 l_object_version_num jtf_rs_resource_extns.object_version_number%TYPE;
4027
4028 l_max_end_date DATE;
4029 l_min_start_date DATE;
4030 l_bind_data_id NUMBER;
4031 l_check_flag VARCHAR2(1);
4032
4033 /* Moved the initial assignment of below variables to inside begin */
4034 l_resource_name jtf_rs_resource_extns_tl.resource_name%type;
4035 l_source_name jtf_rs_resource_extns.source_name%type;
4036 l_source_number jtf_rs_resource_extns.source_number%type;
4037 l_source_job_title jtf_rs_resource_extns.source_job_title%type;
4038 l_source_email jtf_rs_resource_extns.source_email%type;
4039 l_source_phone jtf_rs_resource_extns.source_phone%type;
4040 l_source_org_id number;
4041 l_source_org_name jtf_rs_resource_extns.source_org_name%type;
4042 l_source_address1 jtf_rs_resource_extns.source_address1%type;
4043 l_source_address2 jtf_rs_resource_extns.source_address2%type;
4044 l_source_address3 jtf_rs_resource_extns.source_address3%type;
4045 l_source_address4 jtf_rs_resource_extns.source_address4%type;
4046 l_source_city jtf_rs_resource_extns.source_city%type;
4047 l_source_postal_code jtf_rs_resource_extns.source_postal_code%type;
4048 l_source_state jtf_rs_resource_extns.source_state%type;
4049 l_source_province jtf_rs_resource_extns.source_province%type;
4050 l_source_county jtf_rs_resource_extns.source_county%type;
4051 l_source_country jtf_rs_resource_extns.source_country%type;
4052 l_source_mgr_id jtf_rs_resource_extns.source_mgr_id%type;
4053 l_source_mgr_name jtf_rs_resource_extns.source_mgr_name%type;
4054 l_source_business_grp_id jtf_rs_resource_extns.source_business_grp_id%type;
4055 l_source_business_grp_name jtf_rs_resource_extns.source_business_grp_name%type;
4056 l_source_first_name jtf_rs_resource_extns.source_first_name%type;
4057 l_source_last_name jtf_rs_resource_extns.source_last_name%type;
4058 l_source_middle_name jtf_rs_resource_extns.source_middle_name%type;
4059 l_source_status jtf_rs_resource_extns.source_status%type;
4060 l_source_office jtf_rs_resource_extns.source_office%type;
4061 l_source_location jtf_rs_resource_extns.source_location%type;
4062 l_source_mailstop jtf_rs_resource_extns.source_mailstop%type;
4063 l_source_mobile_phone jtf_rs_resource_extns.source_mobile_phone%type;
4064 l_source_pager jtf_rs_resource_extns.source_pager%type;
4065 l_address_id jtf_rs_resource_extns.address_id%type;
4066 l_source_category jtf_rs_resource_extns.source_category%type;
4067 l_user_name jtf_rs_resource_extns.user_name%type;
4068
4069
4070 --added for NOCOPY to handle in JTF_RESOURCE_UTL
4071 l_managing_emp_id_out jtf_rs_resource_extns.managing_employee_id%TYPE ;
4072 l_server_group_id_out jtf_rs_resource_extns.server_group_id%TYPE ;
4073 l_comp_service_team_id_out jtf_rs_resource_extns.comp_service_team_id%TYPE;
4074
4075
4076 CURSOR c_resource_update(
4077 l_resource_id IN NUMBER )
4078 IS
4079 SELECT
4080 DECODE(p_managing_emp_id, fnd_api.g_miss_num, managing_employee_id, p_managing_emp_id) managing_emp_id,
4081 DECODE(p_start_date_active, fnd_api.g_miss_date, start_date_active, trunc(p_start_date_active)) start_date_active,
4082 DECODE(p_end_date_active, fnd_api.g_miss_date, end_date_active,trunc(p_end_date_active)) end_date_active,
4083 DECODE(p_time_zone, fnd_api.g_miss_num, time_zone, p_time_zone) time_zone,
4084 DECODE(p_cost_per_hr, fnd_api.g_miss_num, cost_per_hr, p_cost_per_hr) cost_per_hr,
4085 DECODE(p_primary_language, fnd_api.g_miss_char, primary_language, p_primary_language) primary_language,
4086 DECODE(p_secondary_language, fnd_api.g_miss_char, secondary_language, p_secondary_language) secondary_language,
4087 DECODE(p_support_site_id, fnd_api.g_miss_num, support_site_id, p_support_site_id) support_site_id,
4088 DECODE(p_ies_agent_login, fnd_api.g_miss_char, ies_agent_login, p_ies_agent_login) ies_agent_login,
4089 DECODE(p_server_group_id, fnd_api.g_miss_num, server_group_id, p_server_group_id) server_group_id,
4090 DECODE(p_assigned_to_group_id, fnd_api.g_miss_num, assigned_to_group_id, p_assigned_to_group_id) assigned_to_group_id,
4091 DECODE(p_cost_center, fnd_api.g_miss_char, cost_center, p_cost_center) cost_center,
4092 DECODE(p_charge_to_cost_center, fnd_api.g_miss_char, charge_to_cost_center, p_charge_to_cost_center) charge_to_cost_center,
4093 DECODE(p_comp_currency_code, fnd_api.g_miss_char, compensation_currency_code, p_comp_currency_code) comp_currency_code,
4094 DECODE(p_commissionable_flag, fnd_api.g_miss_char, commissionable_flag, p_commissionable_flag) commissionable_flag,
4095 DECODE(p_hold_reason_code, fnd_api.g_miss_char, hold_reason_code, p_hold_reason_code) hold_reason_code,
4096 DECODE(p_hold_payment, fnd_api.g_miss_char, hold_payment, p_hold_payment) hold_payment,
4097 DECODE(p_comp_service_team_id, fnd_api.g_miss_num, comp_service_team_id, p_comp_service_team_id) comp_service_team_id,
4098 DECODE(p_user_id, fnd_api.g_miss_num, user_id, p_user_id) user_id,
4099 --DECODE(p_location, jtf_rs_resource_pub.g_miss_location, location, p_location) location,
4100 DECODE(p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1) attribute1,
4101 DECODE(p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2) attribute2,
4102 DECODE(p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3) attribute3,
4103 DECODE(p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4) attribute4,
4104 DECODE(p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5) attribute5,
4105 DECODE(p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6) attribute6,
4106 DECODE(p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7) attribute7,
4107 DECODE(p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8) attribute8,
4108 DECODE(p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9) attribute9,
4109 DECODE(p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10) attribute10,
4110 DECODE(p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11) attribute11,
4111 DECODE(p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12) attribute12,
4112 DECODE(p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13) attribute13,
4113 DECODE(p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14) attribute14,
4114 DECODE(p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15) attribute15,
4115 DECODE(p_attribute_category, fnd_api.g_miss_char, attribute_category, p_attribute_category) attribute_category,
4116 DECODE(p_resource_name, fnd_api.g_miss_char, resource_name, p_resource_name) resource_name,
4117 DECODE(p_source_name, fnd_api.g_miss_char, source_name, p_source_name) source_name,
4118 DECODE(p_source_number, fnd_api.g_miss_char, source_number, p_source_number) source_number,
4119 DECODE(p_source_job_title, fnd_api.g_miss_char, source_job_title, p_source_job_title) source_job_title,
4120 DECODE(p_source_email, fnd_api.g_miss_char, source_email, p_source_email) source_email,
4121 DECODE(p_source_phone, fnd_api.g_miss_char, source_phone, p_source_phone) source_phone,
4122 DECODE(p_source_org_id, fnd_api.g_miss_num, source_org_id, p_source_org_id) source_org_id,
4123 DECODE(p_source_org_name, fnd_api.g_miss_char, source_org_name, p_source_org_name) source_org_name,
4124 DECODE(p_source_address1, fnd_api.g_miss_char, source_address1, p_source_address1) source_address1,
4125 DECODE(p_source_address2, fnd_api.g_miss_char, source_address2, p_source_address2) source_address2,
4126 DECODE(p_source_address3, fnd_api.g_miss_char, source_address3, p_source_address3) source_address3,
4127 DECODE(p_source_address4, fnd_api.g_miss_char, source_address4, p_source_address4) source_address4,
4128 DECODE(p_source_city, fnd_api.g_miss_char, source_city, p_source_city) source_city,
4129 DECODE(p_source_postal_code, fnd_api.g_miss_char, source_postal_code, p_source_postal_code) source_postal_code,
4130 DECODE(p_source_state, fnd_api.g_miss_char, source_state, p_source_state) source_state,
4131 DECODE(p_source_province, fnd_api.g_miss_char, source_province, p_source_province) source_province,
4132 DECODE(p_source_county, fnd_api.g_miss_char, source_county, p_source_county) source_county,
4133 DECODE(p_source_country, fnd_api.g_miss_char, source_country, p_source_country) source_country,
4134 DECODE(p_source_mgr_id, fnd_api.g_miss_num, source_mgr_id, p_source_mgr_id) source_mgr_id,
4135 DECODE(p_source_mgr_name, fnd_api.g_miss_char, source_mgr_name, p_source_mgr_name) source_mgr_name,
4136 DECODE(p_source_business_grp_id, fnd_api.g_miss_num, source_business_grp_id, p_source_business_grp_id) source_business_grp_id,
4137 DECODE(p_source_business_grp_name, fnd_api.g_miss_char, source_business_grp_name, p_source_business_grp_name) source_business_grp_name,
4138 DECODE(p_source_first_name, fnd_api.g_miss_char, source_first_name, p_source_first_name) source_first_name,
4139 DECODE(p_source_middle_name, fnd_api.g_miss_char, source_middle_name, p_source_middle_name) source_middle_name,
4140 DECODE(p_source_last_name, fnd_api.g_miss_char, source_last_name, p_source_last_name) source_last_name,
4141 DECODE(p_source_category, fnd_api.g_miss_char, source_category, p_source_category) source_category,
4142 DECODE(p_source_status, fnd_api.g_miss_char, source_status, p_source_status) source_status,
4143 DECODE(p_source_office, fnd_api.g_miss_char, source_office, p_source_office) source_office,
4144 DECODE(p_source_location, fnd_api.g_miss_char, source_location, p_source_location) source_location,
4145 DECODE(p_source_mailstop, fnd_api.g_miss_char, source_mailstop, p_source_mailstop) source_mailstop,
4146 DECODE(p_source_mobile_phone, fnd_api.g_miss_char, source_mobile_phone, p_source_mobile_phone) source_mobile_phone,
4147 DECODE(p_source_pager, fnd_api.g_miss_char, source_pager, p_source_pager) source_pager,
4148 DECODE(p_address_id, fnd_api.g_miss_num, address_id, p_address_id) address_id,
4149 DECODE(p_user_name, fnd_api.g_miss_char, user_name, p_user_name) user_name,
4150 PERSON_PARTY_ID,
4151 SOURCE_JOB_ID,
4152 category,
4153 resource_number,
4154 source_id,
4155 contact_id,
4156 transaction_number,
4157 address_id old_address_id
4158 FROM jtf_rs_resource_extns_vl
4159 WHERE resource_id = l_resource_id;
4160
4161 resource_rec c_resource_update%ROWTYPE;
4162
4163 -- Modtfying the below query for bug # 4956644
4164 -- New query logic is given in bug # 4052112
4165 -- OIC expanded the definition of compensation analyst to include any active user in the
4166 -- system regardless of their assignment to a CN responsibility.
4167 CURSOR c_assigned_to_group_id(
4168 l_assigned_to_group_id IN NUMBER)
4169 IS
4170 SELECT u.user_id
4171 FROM fnd_user u,
4172 jtf_rs_resource_extns r
4173 WHERE u.user_id = r.user_id
4174 AND u.user_id = l_assigned_to_group_id;
4175
4176
4177 CURSOR c_get_resource_info(
4178 l_resource_id IN NUMBER )
4179 IS
4180 SELECT start_date_active
4181 FROM jtf_rs_resource_extns
4182 WHERE resource_id = l_resource_id;
4183
4184
4185 CURSOR c_related_role_dates_first(
4186 l_resource_id IN NUMBER )
4187 IS
4188 SELECT min(start_date_active),
4189 max(end_date_active)
4190 FROM jtf_rs_role_relations
4191 WHERE role_resource_type = 'RS_INDIVIDUAL'
4192 AND role_resource_id = l_resource_id
4193 AND nvl(delete_flag, 'N') <> 'Y'
4194 AND end_date_active is not null;
4195
4196
4197 CURSOR c_related_role_dates_sec(
4198 l_resource_id IN NUMBER )
4199 IS
4200 SELECT min(start_date_active)
4201 FROM jtf_rs_role_relations
4202 WHERE role_resource_type = 'RS_INDIVIDUAL'
4203 AND role_resource_id = l_resource_id
4204 AND nvl(delete_flag, 'N') <> 'Y'
4205 AND end_date_active is null;
4206
4207
4208 CURSOR c_grp_mbr_role_dates_first(
4209 l_resource_id IN NUMBER )
4210 IS
4211 SELECT min(jrrr.start_date_active),
4212 max(jrrr.end_date_active)
4213 FROM jtf_rs_group_members jrgm,
4214 jtf_rs_role_relations jrrr
4215 WHERE jrgm.group_member_id = jrrr.role_resource_id
4216 AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
4217 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
4218 AND nvl(jrgm.delete_flag, 'N') <> 'Y'
4219 AND jrgm.resource_id = l_resource_id
4220 AND jrrr.end_date_active is not null;
4221
4222
4223 CURSOR c_grp_mbr_role_dates_sec(
4224 l_resource_id IN NUMBER )
4225 IS
4226 SELECT min(jrrr.start_date_active)
4227 FROM jtf_rs_group_members jrgm,
4228 jtf_rs_role_relations jrrr
4229 WHERE jrgm.group_member_id = jrrr.role_resource_id
4230 AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
4231 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
4232 AND nvl(jrgm.delete_flag, 'N') <> 'Y'
4233 AND jrgm.resource_id = l_resource_id
4234 AND jrrr.end_date_active is null;
4235
4236
4237 CURSOR c_team_mbr_role_dates_first(
4238 l_resource_id IN NUMBER )
4239 IS
4240 SELECT min(jrrr.start_date_active),
4241 max(jrrr.end_date_active)
4242 FROM jtf_rs_team_members jrtm,
4243 jtf_rs_role_relations jrrr
4244 WHERE jrtm.team_member_id = jrrr.role_resource_id
4245 AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
4246 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
4247 AND nvl(jrtm.delete_flag, 'N') <> 'Y'
4248 AND jrtm.team_resource_id = l_resource_id
4249 AND jrtm.resource_type = 'INDIVIDUAL'
4250 AND jrrr.end_date_active is not null;
4251
4252
4253 CURSOR c_team_mbr_role_dates_sec(
4254 l_resource_id IN NUMBER )
4255 IS
4256 SELECT min(jrrr.start_date_active)
4257 FROM jtf_rs_team_members jrtm,
4258 jtf_rs_role_relations jrrr
4259 WHERE jrtm.team_member_id = jrrr.role_resource_id
4260 AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
4261 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
4262 AND nvl(jrtm.delete_flag, 'N') <> 'Y'
4263 AND jrtm.team_resource_id = l_resource_id
4264 AND jrtm.resource_type = 'INDIVIDUAL'
4265 AND jrrr.end_date_active is null;
4266
4267
4268 CURSOR c_salesrep_dates_first(
4269 l_resource_id IN NUMBER )
4270 IS
4271 SELECT min(start_date_active),
4272 max(end_date_active)
4273 FROM jtf_rs_salesreps
4274 WHERE resource_id = l_resource_id
4275 AND end_date_active is not null;
4276
4277
4278 CURSOR c_salesrep_dates_sec(
4279 l_resource_id IN NUMBER )
4280 IS
4281 SELECT min(start_date_active)
4282 FROM jtf_rs_salesreps
4283 WHERE resource_id = l_resource_id
4284 AND end_date_active is null;
4285
4286 CURSOR c_validate_user_id(
4287 l_resource_id IN NUMBER,
4288 l_user_id IN NUMBER)
4289 IS
4290 SELECT 'Y'
4291 FROM jtf_rs_resource_extns
4292 WHERE user_id = l_user_id
4293 AND resource_id <> l_resource_id;
4294
4295 l_value VARCHAR2(100);
4296 l_address_ret_status varchar2(10);
4297 l_address_found boolean := true;
4298
4299 l_return_status VARCHAR2(2000);
4300 l_msg_count NUMBER;
4301 l_msg_data VARCHAR2(2000);
4302
4303 l_resource_rec jtf_rs_resource_pvt.resource_rec_type;
4304
4305 BEGIN
4306
4307 l_resource_id := p_resource_id;
4308 l_managing_emp_id := p_managing_emp_id;
4309 l_start_date_active := trunc(p_start_date_active);
4310 l_end_date_active := trunc(p_end_date_active);
4311 l_time_zone := p_time_zone;
4312 l_cost_per_hr := p_cost_per_hr;
4313 l_primary_language := p_primary_language;
4314 l_secondary_language := p_secondary_language;
4315 l_support_site_id := p_support_site_id;
4316 l_ies_agent_login := p_ies_agent_login;
4317 l_server_group_id := p_server_group_id;
4318 l_assigned_to_group_id := p_assigned_to_group_id;
4319 l_cost_center := p_cost_center;
4320 l_charge_to_cost_center := p_charge_to_cost_center;
4321 l_comp_currency_code := p_comp_currency_code;
4322 l_commissionable_flag := p_commissionable_flag;
4323 l_hold_reason_code := p_hold_reason_code;
4324 l_hold_payment := p_hold_payment;
4325 l_comp_service_team_id := p_comp_service_team_id;
4326 l_user_id := p_user_id;
4327 l_object_version_num := p_object_version_num;
4328 l_resource_name := p_resource_name;
4329 l_source_name := p_source_name;
4330 l_source_number := p_source_number;
4331 l_source_job_title := p_source_job_title;
4332 l_source_email := p_source_email;
4333 l_source_phone := p_source_phone;
4334 l_source_org_id := p_source_org_id;
4335 l_source_org_name := p_source_org_name;
4336 l_source_address1 := p_source_address1;
4337 l_source_address2 := p_source_address2;
4338 l_source_address3 := p_source_address3;
4339 l_source_address4 := p_source_address4;
4340 l_source_city := p_source_city;
4341 l_source_postal_code := p_source_postal_code;
4342 l_source_state := p_source_state;
4343 l_source_province := p_source_province;
4344 l_source_county := p_source_county;
4345 l_source_country := p_source_country;
4346 l_source_mgr_id := p_source_mgr_id;
4347 l_source_mgr_name := p_source_mgr_name;
4348 l_source_business_grp_id := p_source_business_grp_id;
4349 l_source_business_grp_name := p_source_business_grp_name;
4350 l_source_first_name := p_source_first_name;
4351 l_source_last_name := p_source_last_name;
4352 l_source_middle_name := p_source_middle_name;
4353 l_source_status := p_source_status;
4354 l_source_office := p_source_office;
4355 l_source_location := p_source_location;
4356 l_source_mailstop := p_source_mailstop;
4357 l_source_mobile_phone := p_source_mobile_phone;
4358 l_source_pager := p_source_pager;
4359 l_address_id := p_address_id;
4360 l_source_category := p_source_category;
4361 l_user_name := p_user_name;
4362
4363 SAVEPOINT update_resource_pvt;
4364
4365 x_return_status := fnd_api.g_ret_sts_success;
4366
4367 -- DBMS_OUTPUT.put_line('Started Update Resource Pvt ');
4368
4369
4370 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
4371
4372 RAISE fnd_api.g_exc_unexpected_error;
4373
4374 END IF;
4375
4376
4377 IF fnd_api.to_boolean(p_init_msg_list) THEN
4378
4379 fnd_msg_pub.initialize;
4380
4381 END IF;
4382
4383
4384 /* Make the pre processing call to the user hooks */
4385
4386 /* Pre Call to the Customer Type User Hook */
4387
4388 IF jtf_resource_utl.ok_to_execute(
4389 'JTF_RS_RESOURCE_PVT',
4390 'UPDATE_RESOURCE',
4391 'B',
4392 'C')
4393 THEN
4394 IF jtf_usr_hks.ok_to_execute(
4395 'JTF_RS_RESOURCE_PVT',
4396 'UPDATE_RESOURCE',
4397 'B',
4398 'C')
4399 THEN
4400
4401 jtf_rs_resource_cuhk.update_resource_pre(
4402 p_resource_id => l_resource_id,
4403 p_managing_emp_id => l_managing_emp_id,
4404 p_start_date_active => l_start_date_active,
4405 p_end_date_active => l_end_date_active,
4406 p_time_zone => l_time_zone,
4407 p_cost_per_hr => l_cost_per_hr,
4408 p_primary_language => l_primary_language,
4409 p_secondary_language => l_secondary_language,
4410 p_support_site_id => l_support_site_id,
4411 p_ies_agent_login => l_ies_agent_login,
4412 p_server_group_id => l_server_group_id,
4413 p_assigned_to_group_id => l_assigned_to_group_id,
4414 p_cost_center => l_cost_center,
4415 p_charge_to_cost_center => l_charge_to_cost_center,
4416 p_comp_currency_code => l_comp_currency_code,
4417 p_commissionable_flag => l_commissionable_flag,
4418 p_hold_reason_code => l_hold_reason_code,
4419 p_hold_payment => l_hold_payment,
4420 p_comp_service_team_id => l_comp_service_team_id,
4421 p_user_id => l_user_id,
4422 -- p_location => l_location,
4423 x_return_status => x_return_status);
4424
4425 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
4426
4427 -- dbms_output.put_line('Returned Error status from the Pre Customer User Hook');
4428
4429
4430 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
4431 fnd_msg_pub.add;
4432
4433 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
4434 RAISE FND_API.G_EXC_ERROR;
4435 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
4436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4437 END IF;
4438
4439
4440 END IF;
4441
4442 END IF;
4443 END IF;
4444
4445
4446 /* Pre Call to the Vertical Type User Hook */
4447
4448 IF jtf_resource_utl.ok_to_execute(
4449 'JTF_RS_RESOURCE_PVT',
4450 'UPDATE_RESOURCE',
4451 'B',
4452 'V')
4453 THEN
4454 IF jtf_usr_hks.ok_to_execute(
4455 'JTF_RS_RESOURCE_PVT',
4456 'UPDATE_RESOURCE',
4457 'B',
4458 'V')
4459 THEN
4460
4461 jtf_rs_resource_vuhk.update_resource_pre(
4462 p_resource_id => l_resource_id,
4463 p_managing_emp_id => l_managing_emp_id,
4464 p_start_date_active => l_start_date_active,
4465 p_end_date_active => l_end_date_active,
4466 p_time_zone => l_time_zone,
4467 p_cost_per_hr => l_cost_per_hr,
4468 p_primary_language => l_primary_language,
4469 p_secondary_language => l_secondary_language,
4470 p_support_site_id => l_support_site_id,
4471 p_ies_agent_login => l_ies_agent_login,
4472 p_server_group_id => l_server_group_id,
4473 p_assigned_to_group_id => l_assigned_to_group_id,
4474 p_cost_center => l_cost_center,
4475 p_charge_to_cost_center => l_charge_to_cost_center,
4476 p_comp_currency_code => l_comp_currency_code,
4477 p_commissionable_flag => l_commissionable_flag,
4478 p_hold_reason_code => l_hold_reason_code,
4479 p_hold_payment => l_hold_payment,
4480 p_comp_service_team_id => l_comp_service_team_id,
4481 p_user_id => l_user_id,
4482 -- p_location => l_location,
4483 x_return_status => x_return_status);
4484
4485 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
4486
4487 -- dbms_output.put_line('Returned Error status from the Pre Vertical User Hook');
4488
4489 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
4490 fnd_msg_pub.add;
4491
4492 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
4493 RAISE FND_API.G_EXC_ERROR;
4494 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
4495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4496 END IF;
4497
4498
4499 END IF;
4500
4501 END IF;
4502 END IF;
4503
4504
4505 /* Pre Call to the Internal Type User Hook */
4506
4507 IF jtf_resource_utl.ok_to_execute(
4508 'JTF_RS_RESOURCE_PVT',
4509 'UPDATE_RESOURCE',
4510 'B',
4511 'I')
4512 THEN
4513 IF jtf_usr_hks.ok_to_execute(
4514 'JTF_RS_RESOURCE_PVT',
4515 'UPDATE_RESOURCE',
4516 'B',
4517 'I')
4518 THEN
4519
4520 jtf_rs_resource_iuhk.update_resource_pre(
4521 p_resource_id => l_resource_id,
4522 p_managing_emp_id => l_managing_emp_id,
4523 p_start_date_active => l_start_date_active,
4524 p_end_date_active => l_end_date_active,
4525 p_time_zone => l_time_zone,
4526 p_cost_per_hr => l_cost_per_hr,
4527 p_primary_language => l_primary_language,
4528 p_secondary_language => l_secondary_language,
4529 p_support_site_id => l_support_site_id,
4530 p_ies_agent_login => l_ies_agent_login,
4531 p_server_group_id => l_server_group_id,
4532 p_assigned_to_group_id => l_assigned_to_group_id,
4533 p_cost_center => l_cost_center,
4534 p_charge_to_cost_center => l_charge_to_cost_center,
4535 p_comp_currency_code => l_comp_currency_code,
4536 p_commissionable_flag => l_commissionable_flag,
4537 p_hold_reason_code => l_hold_reason_code,
4538 p_hold_payment => l_hold_payment,
4539 p_comp_service_team_id => l_comp_service_team_id,
4540 p_user_id => l_user_id,
4541 --p_location => l_location,
4542 x_return_status => x_return_status);
4543
4544 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
4545
4546
4547 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
4548 fnd_msg_pub.add;
4549
4550 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
4551 RAISE FND_API.G_EXC_ERROR;
4552 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
4553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4554 END IF;
4555
4556
4557 END IF;
4558
4559 END IF;
4560 END IF;
4561
4562
4563 OPEN c_resource_update(l_resource_id);
4564
4565 FETCH c_resource_update INTO resource_rec;
4566
4567
4568 IF c_resource_update%NOTFOUND THEN
4569
4570 IF c_resource_update%ISOPEN THEN
4571
4572 CLOSE c_resource_update;
4573
4574 END IF;
4575
4576 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE');
4577 fnd_message.set_token('P_RESOURCE_ID', l_resource_id);
4578 fnd_msg_pub.add;
4579
4580 RAISE fnd_api.g_exc_error;
4581
4582
4583 END IF;
4584
4585
4586
4587 /* Validate the Managing Employee Id if specified */
4588
4589 IF l_managing_emp_id <> fnd_api.g_miss_num THEN
4590
4591 jtf_resource_utl.validate_employee_resource(
4592 p_emp_resource_id => l_managing_emp_id,
4593 p_emp_resource_number => null,
4594 x_return_status => x_return_status,
4595 x_emp_resource_id => l_managing_emp_id_out
4596 );
4597
4598 -- added for NOCOPY
4599 l_managing_emp_id := l_managing_emp_id_out;
4600
4601 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
4602
4603 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
4604 RAISE FND_API.G_EXC_ERROR;
4605 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
4606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4607 END IF;
4608
4609
4610 END IF;
4611
4612 END IF;
4613
4614
4615
4616 /* Validate the Input Dates */
4617
4618 IF l_start_date_active <> fnd_api.g_miss_date OR
4619 l_end_date_active <> fnd_api.g_miss_date THEN
4620
4621 -- Code changes to fix bug 4171623. (G_MISS_DATE DOESN'T WORK PROPERLY ON JTF_RS_RESOURCE_PUB).
4622
4623 -- Changing the values being passed to "validate_input_dates" procedure,
4624 -- from l_end_date_active to resource_rec.end_date_active (same for start date)
4625 -- so that it validates the correct dates which its supposed to validate.
4626
4627 jtf_resource_utl.validate_input_dates(
4628 p_start_date_active => resource_rec.start_date_active,
4629 p_end_date_active => resource_rec.end_date_active,
4630 x_return_status => x_return_status
4631 );
4632
4633 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
4634
4635 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
4636 RAISE FND_API.G_EXC_ERROR;
4637 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
4638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4639 END IF;
4640
4641
4642 END IF;
4643
4644 END IF;
4645
4646
4647 /* Validate that the resource dates cover the role related dates for the resource */
4648
4649 /* First part of the validation where the role relate end date active is not null */
4650
4651 OPEN c_related_role_dates_first(l_resource_id);
4652
4653 FETCH c_related_role_dates_first INTO l_min_start_date, l_max_end_date;
4654
4655
4656 IF l_min_start_date IS NOT NULL THEN
4657
4658 IF l_start_date_active <> FND_API.G_MISS_DATE AND
4659 l_min_start_date < l_start_date_active THEN
4660
4661 /* Resource Start Date out of range for the role related start dates of the resource */
4662
4663 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_ROLE_START_DATE');
4664 fnd_msg_pub.add;
4665
4666 RAISE fnd_api.g_exc_error;
4667
4668
4669 END IF;
4670
4671 IF ( l_end_date_active <> FND_API.G_MISS_DATE AND
4672 l_max_end_date > l_end_date_active AND
4673 l_end_date_active IS NOT NULL ) THEN
4674
4675 /* Resource End Date out of range for the role related End dates of the Resource */
4676
4677 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_ROLE_END_DATE');
4678 fnd_msg_pub.add;
4679
4680 RAISE fnd_api.g_exc_error;
4681
4682
4683 END IF;
4684
4685 END IF;
4686
4687
4688 /* Close the cursor */
4689
4690 IF c_related_role_dates_first%ISOPEN THEN
4691
4692 CLOSE c_related_role_dates_first;
4693
4694 END IF;
4695
4696
4697
4698 /* Second part of the validation where the role relate end date active is null */
4699
4700 OPEN c_related_role_dates_sec(l_resource_id);
4701
4702 FETCH c_related_role_dates_sec INTO l_min_start_date;
4703
4704
4705 IF l_min_start_date IS NOT NULL THEN
4706
4707 IF l_start_date_active <> FND_API.G_MISS_DATE AND
4708 l_min_start_date < l_start_date_active THEN
4709
4710 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_ROLE_START_DATE');
4711 fnd_msg_pub.add;
4712
4713 RAISE fnd_api.g_exc_error;
4714
4715
4716 END IF;
4717
4718 IF l_end_date_active <> FND_API.G_MISS_DATE AND
4719 l_end_date_active IS NOT NULL THEN
4720
4721 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_ROLE_END_DATE');
4722 fnd_msg_pub.add;
4723
4724 RAISE fnd_api.g_exc_error;
4725
4726
4727 END IF;
4728
4729 END IF;
4730
4731 /* Close the cursor */
4732
4733 IF c_related_role_dates_sec%ISOPEN THEN
4734
4735 CLOSE c_related_role_dates_sec;
4736
4737 END IF;
4738
4739
4740 /* Validate that the resource dates cover the group member role related dates for the resource */
4741
4742 /* First part of the validation where the group member role relate end date active is not null */
4743
4744 OPEN c_grp_mbr_role_dates_first(l_resource_id);
4745
4746 FETCH c_grp_mbr_role_dates_first INTO l_min_start_date, l_max_end_date;
4747
4748
4749 IF l_min_start_date IS NOT NULL THEN
4750
4751 IF l_start_date_active <> FND_API.G_MISS_DATE AND
4752 l_min_start_date < l_start_date_active THEN
4753
4754 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_GMBR_START_DATE');
4755 fnd_msg_pub.add;
4756
4757 RAISE fnd_api.g_exc_error;
4758
4759
4760 END IF;
4761
4762 IF ( l_end_date_active <> FND_API.G_MISS_DATE AND
4763 l_max_end_date > l_end_date_active AND
4764 l_end_date_active IS NOT NULL ) THEN
4765
4766 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_GMBR_END_DATE');
4767 fnd_msg_pub.add;
4768
4769 RAISE fnd_api.g_exc_error;
4770
4771
4772 END IF;
4773
4774 END IF;
4775
4776
4777 /* Close the cursor */
4778
4779 IF c_grp_mbr_role_dates_first%ISOPEN THEN
4780
4781 CLOSE c_grp_mbr_role_dates_first;
4782
4783 END IF;
4784
4785
4786
4787 /* Second part of the validation where the member role relate end date active is null */
4788
4789 OPEN c_grp_mbr_role_dates_sec(l_resource_id);
4790
4791 FETCH c_grp_mbr_role_dates_sec INTO l_min_start_date;
4792
4793
4794 IF l_min_start_date IS NOT NULL THEN
4795
4796 IF l_start_date_active <> FND_API.G_MISS_DATE AND
4797 l_min_start_date < l_start_date_active THEN
4798
4799 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_GMBR_START_DATE');
4800 fnd_msg_pub.add;
4801
4802 RAISE fnd_api.g_exc_error;
4803
4804
4805 END IF;
4806
4807 IF l_end_date_active <> FND_API.G_MISS_DATE AND
4808 l_end_date_active IS NOT NULL THEN
4809
4810 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_GMBR_END_DATE');
4811 fnd_msg_pub.add;
4812
4813 RAISE fnd_api.g_exc_error;
4814
4815
4816 END IF;
4817
4818 END IF;
4819
4820 /* Close the cursor */
4821
4822 IF c_grp_mbr_role_dates_sec%ISOPEN THEN
4823
4824 CLOSE c_grp_mbr_role_dates_sec;
4825
4826 END IF;
4827
4828
4829
4830 /* Validate that the resource dates cover the team member role related dates for the
4831 resource, where the team member is a resource */
4832
4833 /* First part of the validation where the team member role relate end date active
4834 is not null */
4835
4836 OPEN c_team_mbr_role_dates_first(l_resource_id);
4837
4838 FETCH c_team_mbr_role_dates_first INTO l_min_start_date, l_max_end_date;
4839
4840
4841 IF l_min_start_date IS NOT NULL THEN
4842
4843 IF l_start_date_active <> FND_API.G_MISS_DATE AND
4844 l_min_start_date < l_start_date_active THEN
4845
4846 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_TMBR_START_DATE');
4847 fnd_msg_pub.add;
4848
4849 RAISE fnd_api.g_exc_error;
4850
4851
4852 END IF;
4853
4854 IF ( l_end_date_active <> FND_API.G_MISS_DATE AND
4855 l_max_end_date > l_end_date_active AND
4856 l_end_date_active IS NOT NULL ) THEN
4857
4858 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_TMBR_END_DATE');
4859 fnd_msg_pub.add;
4860
4861 RAISE fnd_api.g_exc_error;
4862
4863
4864 END IF;
4865
4866 END IF;
4867
4868
4869 /* Close the cursor */
4870
4871 IF c_team_mbr_role_dates_first%ISOPEN THEN
4872
4873 CLOSE c_team_mbr_role_dates_first;
4874
4875 END IF;
4876
4877
4878 /* Second part of the validation where the member role relate end date active is null */
4879
4880 OPEN c_team_mbr_role_dates_sec(l_resource_id);
4881
4882 FETCH c_team_mbr_role_dates_sec INTO l_min_start_date;
4883
4884
4885 IF l_min_start_date IS NOT NULL THEN
4886
4887 IF l_start_date_active <> FND_API.G_MISS_DATE AND
4888 l_min_start_date < l_start_date_active THEN
4889
4890 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_TMBR_START_DATE');
4891 fnd_msg_pub.add;
4892
4893 RAISE fnd_api.g_exc_error;
4894
4895
4896 END IF;
4897
4898 IF l_end_date_active <> FND_API.G_MISS_DATE AND
4899 l_end_date_active IS NOT NULL THEN
4900
4901 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_TMBR_END_DATE');
4902 fnd_msg_pub.add;
4903
4904 RAISE fnd_api.g_exc_error;
4905
4906
4907 END IF;
4908
4909 END IF;
4910
4911
4912 /* Close the cursor */
4913
4914 IF c_team_mbr_role_dates_sec%ISOPEN THEN
4915
4916 CLOSE c_team_mbr_role_dates_sec;
4917
4918 END IF;
4919
4920
4921
4922 /* Validate that the resource dates cover the salesrep related dates for the resource */
4923
4924 /* First part of the validation where the salesrep end date active is not null */
4925
4926 OPEN c_salesrep_dates_first(l_resource_id);
4927
4928 FETCH c_salesrep_dates_first INTO l_min_start_date, l_max_end_date;
4929
4930
4931 IF l_min_start_date IS NOT NULL THEN
4932
4933 IF l_start_date_active <> FND_API.G_MISS_DATE AND
4934 l_min_start_date < l_start_date_active THEN
4935
4936 /* Resource Start Date out of range for the salesrep related start dates of the resource */
4937
4938 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_SRP_START_DATE');
4939 fnd_msg_pub.add;
4940
4941 RAISE fnd_api.g_exc_error;
4942
4943 END IF;
4944
4945 IF ( l_end_date_active <> FND_API.G_MISS_DATE AND
4946 l_max_end_date > l_end_date_active AND
4947 l_end_date_active IS NOT NULL ) THEN
4948
4949 /* Resource End Date out of range for the salesrep related End dates of the Resource */
4950
4951 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_SRP_END_DATE');
4952 fnd_msg_pub.add;
4953
4954 RAISE fnd_api.g_exc_error;
4955
4956
4957 END IF;
4958
4959 END IF;
4960
4961
4962 /* Close the cursor */
4963
4964 IF c_salesrep_dates_first%ISOPEN THEN
4965
4966 CLOSE c_salesrep_dates_first;
4967
4968 END IF;
4969
4970
4971
4972 /* Second part of the validation where the role relate end date active
4973 is null */
4974
4975 OPEN c_salesrep_dates_sec(l_resource_id);
4976
4977 FETCH c_salesrep_dates_sec INTO l_min_start_date;
4978
4979
4980 IF l_min_start_date IS NOT NULL THEN
4981
4982 IF l_start_date_active <> FND_API.G_MISS_DATE AND
4983 l_min_start_date < l_start_date_active THEN
4984
4985 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_SRP_START_DATE');
4986 fnd_msg_pub.add;
4987
4988 RAISE fnd_api.g_exc_error;
4989
4990
4991 END IF;
4992
4993 IF l_end_date_active <> FND_API.G_MISS_DATE AND
4994 l_end_date_active IS NOT NULL THEN
4995
4996 fnd_message.set_name('JTF', 'JTF_RS_ERR_RES_SRP_END_DATE');
4997 fnd_msg_pub.add;
4998
4999 RAISE fnd_api.g_exc_error;
5000
5001
5002 END IF;
5003
5004 END IF;
5005
5006 /* Close the cursor */
5007
5008 IF c_salesrep_dates_sec%ISOPEN THEN
5009
5010 CLOSE c_salesrep_dates_sec;
5011
5012 END IF;
5013
5014
5015
5016 /* Validate the Time Zone */
5017
5018 IF l_time_zone <> fnd_api.g_miss_num THEN
5019
5020 IF l_time_zone IS NOT NULL THEN
5021
5022 jtf_resource_utl.validate_time_zone(
5023 p_time_zone_id => l_time_zone,
5024 x_return_status => x_return_status
5025 );
5026
5027 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5028
5029 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5030 RAISE FND_API.G_EXC_ERROR;
5031 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5032 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5033 END IF;
5034
5035
5036 END IF;
5037
5038 END IF;
5039
5040 END IF;
5041
5042
5043
5044 /* Validate the Primary Language */
5045
5046 IF l_primary_language <> fnd_api.g_miss_char THEN
5047
5048 IF l_primary_language IS NOT NULL THEN
5049
5050 jtf_resource_utl.validate_nls_language(
5051 p_nls_language => l_primary_language,
5052 x_return_status => x_return_status
5053 );
5054
5055 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5056
5057 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5058 RAISE FND_API.G_EXC_ERROR;
5059 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5060 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5061 END IF;
5062
5063
5064 END IF;
5065
5066 END IF;
5067
5068 END IF;
5069
5070
5071
5072 /* Validate the Secondary Language */
5073
5074 IF l_secondary_language <> fnd_api.g_miss_char THEN
5075
5076 IF l_secondary_language IS NOT NULL THEN
5077
5078 jtf_resource_utl.validate_nls_language(
5079 p_nls_language => l_secondary_language,
5080 x_return_status => x_return_status
5081 );
5082
5083 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5084
5085 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5086 RAISE FND_API.G_EXC_ERROR;
5087 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5089 END IF;
5090
5091
5092 END IF;
5093
5094 END IF;
5095
5096 END IF;
5097
5098
5099
5100 /* Validate the Support Site */
5101
5102 IF l_support_site_id <> fnd_api.g_miss_num THEN
5103
5104 IF l_support_site_id IS NOT NULL THEN
5105
5106 jtf_resource_utl.validate_support_site_id(
5107 p_support_site_id => l_support_site_id,
5108 x_return_status => x_return_status
5109 );
5110
5111 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5112
5113 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5114 RAISE FND_API.G_EXC_ERROR;
5115 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5117 END IF;
5118
5119
5120 END IF;
5121
5122 END IF;
5123
5124 END IF;
5125
5126
5127
5128 /* Validate the Server Group. */
5129
5130 IF l_server_group_id <> fnd_api.g_miss_num THEN
5131
5132 jtf_resource_utl.validate_server_group(
5133 p_server_group_id => l_server_group_id,
5134 p_server_group_name => null,
5135 x_return_status => x_return_status,
5136 x_server_group_id => l_server_group_id_out
5137 );
5138
5139 -- added for NOCOPY
5140 l_server_group_id := l_server_group_id_out;
5141
5142 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5143
5144 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5145 RAISE FND_API.G_EXC_ERROR;
5146 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5147 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5148 END IF;
5149
5150
5151 END IF;
5152
5153 END IF;
5154
5155
5156
5157 /* Validate the assigned_to_group_id if specified */
5158
5159 IF l_assigned_to_group_id <> fnd_api.g_miss_num THEN
5160
5161 IF l_assigned_to_group_id IS NOT NULL THEN
5162
5163 OPEN c_assigned_to_group_id(l_assigned_to_group_id);
5164
5165 FETCH c_assigned_to_group_id INTO l_assigned_to_group_id;
5166
5167
5168 IF c_assigned_to_group_id%NOTFOUND THEN
5169
5170 -- dbms_output.put_line('Invalid Assigned To Group Id');
5171
5172 fnd_message.set_name('JTF', 'JTF_RS_ERR_ASSIGN_TO_GRP_ID');
5173 fnd_message.set_token('P_ASSIGNED_TO_GROUP_ID', l_assigned_to_group_id);
5174 fnd_msg_pub.add;
5175
5176 RAISE fnd_api.g_exc_error;
5177
5178
5179 END IF;
5180
5181
5182 /* Close the cursor */
5183
5184 IF c_assigned_to_group_id%ISOPEN THEN
5185
5186 CLOSE c_assigned_to_group_id;
5187
5188 END IF;
5189
5190 END IF;
5191
5192 END IF;
5193
5194
5195
5196 /* Validate the Comp Currency Code */
5197
5198 IF l_comp_currency_code <> fnd_api.g_miss_char THEN
5199
5200 IF l_comp_currency_code IS NOT NULL THEN
5201
5202 jtf_resource_utl.validate_currency_code(
5203 p_currency_code => l_comp_currency_code,
5204 x_return_status => x_return_status
5205 );
5206
5207 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5208
5209 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5210 RAISE FND_API.G_EXC_ERROR;
5211 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5213 END IF;
5214
5215
5216 END IF;
5217
5218 END IF;
5219
5220 END IF;
5221
5222
5223
5224 /* Validate the value of the commisionable flag */
5225
5226 IF l_commissionable_flag <> fnd_api.g_miss_char THEN
5227
5228 IF l_commissionable_flag <> 'Y' AND l_commissionable_flag <> 'N' THEN
5229
5230 -- dbms_output.put_line('Commissionable Flag should either be ''Y'' or ''N'' ');
5231
5232 fnd_message.set_name('JTF', 'JTF_RS_INVALID_FLAG_VALUE');
5233 fnd_msg_pub.add;
5234
5235 RAISE fnd_api.g_exc_error;
5236
5237
5238 END IF;
5239
5240 END IF;
5241
5242
5243
5244 /* Validate the value of the Hold Payment flag */
5245
5246 IF l_hold_payment <> fnd_api.g_miss_char THEN
5247
5248 IF l_hold_payment <> 'Y' AND l_hold_payment <> 'N' THEN
5249
5250 -- dbms_output.put_line('Hold Payment should either be ''Y'' or ''N'' ');
5251
5252 fnd_message.set_name('JTF', 'JTF_RS_INVALID_FLAG_VALUE');
5253 fnd_msg_pub.add;
5254
5255 RAISE fnd_api.g_exc_error;
5256
5257
5258 END IF;
5259
5260 END IF;
5261
5262
5263
5264 /* Validate the Hold Reason Code */
5265
5266 IF l_hold_reason_code <> fnd_api.g_miss_char THEN
5267
5268 IF l_hold_reason_code IS NOT NULL THEN
5269
5270 jtf_resource_utl.validate_hold_reason_code(
5271 p_hold_reason_code => l_hold_reason_code,
5272 x_return_status => x_return_status
5273 );
5274
5275 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5276
5277 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5278 RAISE FND_API.G_EXC_ERROR;
5279 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5281 END IF;
5282
5283
5284 END IF;
5285
5286 END IF;
5287
5288 END IF;
5289
5290
5291 /* Validate that the user_id should only be specified in case of
5292 'EMPLOYEE', 'PARTY', 'SUPPLIER_CONTACT' categories */
5293
5294 IF l_user_id <> fnd_api.g_miss_num THEN
5295
5296 IF resource_rec.category NOT IN ('EMPLOYEE', 'PARTY', 'SUPPLIER_CONTACT') AND l_user_id IS NOT NULL THEN
5297
5298 fnd_message.set_name('JTF', 'JTF_RS_USERID_ERROR');
5299 fnd_msg_pub.add;
5300
5301 RAISE fnd_api.g_exc_error;
5302
5303
5304 END IF;
5305
5306 END IF;
5307
5308
5309 IF l_user_id <> fnd_api.g_miss_num THEN
5310
5311 /* Validate the User Id if specified */
5312
5313 IF l_user_id IS NOT NULL THEN
5314
5315 jtf_resource_utl.validate_user_id(
5316 p_user_id => l_user_id,
5317 p_category => resource_rec.category,
5318 p_source_id => resource_rec.source_id,
5319 x_return_status => x_return_status
5320 );
5321
5322 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5323
5324 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5325 RAISE FND_API.G_EXC_ERROR;
5326 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5328 END IF;
5329
5330 else
5331
5332 OPEN c_validate_user_id(l_resource_id,l_user_id);
5333
5334 FETCH c_validate_user_id INTO l_check_flag
5335 ;
5336
5337
5338 IF c_validate_user_id%FOUND THEN
5339
5340 -- dbms_output.put_line('duplicate user Id');
5341
5342 fnd_message.set_name('JTF', 'JTF_RS_ERR_DUPLICATE_USER_ID');
5343 fnd_message.set_token('P_USER_ID', l_user_id);
5344 fnd_msg_pub.add;
5345
5346 RAISE fnd_api.g_exc_error;
5347
5348
5349 END IF;
5350
5351
5352 /* Close the cursor */
5353
5354 CLOSE c_validate_user_id;
5355
5356
5357
5358 END IF;
5359
5360 END IF;
5361
5362 END IF;
5363
5364
5365 /* Validate the Comp Service Team Id if specified */
5366
5367 IF l_comp_service_team_id <> fnd_api.g_miss_num THEN
5368
5369 IF l_comp_service_team_id IS NOT NULL THEN
5370
5371 jtf_resource_utl.validate_resource_team(
5372 p_team_id => l_comp_service_team_id,
5373 p_team_number => null,
5374 x_return_status => x_return_status,
5375 x_team_id => l_comp_service_team_id_out
5376 );
5377 -- added for NOCOPY
5378 l_comp_service_team_id := l_comp_service_team_id_out;
5379
5380 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5381
5382 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5383 RAISE FND_API.G_EXC_ERROR;
5384 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5386 END IF;
5387
5388
5389 END IF;
5390
5391 END IF;
5392
5393 END IF;
5394
5395 if(resource_rec.category IN ('PARTNER', 'PARTY'))
5396 THEN
5397 if(p_address_id <> fnd_api.g_miss_num)
5398 then
5399 if (nvl(p_address_id, fnd_api.g_miss_num) <> nvl(resource_rec.old_address_id, fnd_api.g_miss_num))
5400 then
5401 validate_party_address(p_source_id => resource_rec.source_id,
5402 p_address_id => resource_rec.address_id,
5403 p_action => 'U',
5404 p_found => l_address_found,
5405 p_return_status => l_address_ret_status);
5406
5407 if(l_address_ret_status <> fnd_api.g_ret_sts_success)
5408 then
5409 IF L_ADDRESS_RET_STATUS = FND_API.G_RET_STS_ERROR THEN
5410 RAISE FND_API.G_EXC_ERROR;
5411 ELSIF L_ADDRESS_RET_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5413 END IF;
5414
5415 end if;
5416
5417 if not(l_address_found)
5418 then
5419 fnd_message.set_name('JTF', 'JTF_RS_NOT_PRIMARY_ADDR');
5420 fnd_msg_pub.add;
5421 RAISE fnd_api.g_exc_error;
5422
5423 end if;
5424 end if; -- end of nvl check
5425 end if; -- end of f_miss_num check
5426 END IF;
5427
5428 /* Call the lock row procedure to ensure that the object version number
5429 is still valid. */
5430
5431 BEGIN
5432
5433 jtf_rs_resource_extns_pkg.lock_row(
5434 x_resource_id => l_resource_id,
5435 x_object_version_number => p_object_version_num
5436 );
5437
5438 EXCEPTION
5439
5440 WHEN OTHERS THEN
5441
5442 -- dbms_output.put_line('Error in Locking the Row');
5443
5444
5445 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
5446 fnd_msg_pub.add;
5447
5448 RAISE fnd_api.g_exc_error;
5449
5450
5451 END;
5452
5453
5454 /* Make a call to the Resource Audit API */
5455
5456 jtf_rs_resource_extns_aud_pvt.update_resource
5457 (P_API_VERSION => 1,
5458 P_INIT_MSG_LIST => fnd_api.g_false,
5459 P_COMMIT => fnd_api.g_false,
5460 P_RESOURCE_ID => l_resource_id,
5461 P_RESOURCE_NUMBER => resource_rec.resource_number,
5462 P_CATEGORY => resource_rec.category,
5463 P_SOURCE_ID => resource_rec.source_id,
5464 P_ADDRESS_ID => resource_rec.address_id,
5465 P_CONTACT_ID => resource_rec.contact_id,
5466 P_MANAGING_EMP_ID => resource_rec.managing_emp_id,
5467 P_START_DATE_ACTIVE => resource_rec.start_date_active,
5468 P_END_DATE_ACTIVE => resource_rec.end_date_active,
5469 P_TIME_ZONE => resource_rec.time_zone,
5470 P_COST_PER_HR => resource_rec.cost_per_hr,
5471 P_PRIMARY_LANGUAGE => resource_rec.primary_language,
5472 P_SECONDARY_LANGUAGE => resource_rec.secondary_language,
5473 P_SUPPORT_SITE_ID => resource_rec.support_site_id,
5474 P_IES_AGENT_LOGIN => resource_rec.ies_agent_login,
5475 P_SERVER_GROUP_ID => resource_rec.server_group_id,
5476 P_ASSIGNED_TO_GROUP_ID => resource_rec.assigned_to_group_id,
5477 P_COST_CENTER => resource_rec.cost_center,
5478 P_CHARGE_TO_COST_CENTER => resource_rec.charge_to_cost_center,
5479 P_COMP_CURRENCY_CODE => resource_rec.comp_currency_code,
5480 P_COMMISSIONABLE_FLAG => resource_rec.commissionable_flag,
5481 P_HOLD_REASON_CODE => resource_rec.hold_reason_code,
5482 P_HOLD_PAYMENT => resource_rec.hold_payment,
5483 P_COMP_SERVICE_TEAM_ID => resource_rec.comp_service_team_id,
5484 P_USER_ID => resource_rec.user_id,
5485 P_TRANSACTION_NUMBER => resource_rec.transaction_number,
5486 -- P_LOCATION => resource_rec.location,
5487 P_OBJECT_VERSION_NUMBER => p_object_version_num + 1,
5488 P_RESOURCE_NAME => resource_rec.RESOURCE_NAME ,
5489 P_SOURCE_NAME => resource_rec.SOURCE_NAME ,
5490 P_SOURCE_NUMBER => resource_rec.SOURCE_NUMBER ,
5491 P_SOURCE_JOB_TITLE => resource_rec.SOURCE_JOB_TITLE ,
5492 P_SOURCE_EMAIL => resource_rec.SOURCE_EMAIL ,
5493 P_SOURCE_PHONE => resource_rec.SOURCE_PHONE ,
5494 P_SOURCE_ORG_ID => resource_rec.SOURCE_ORG_ID ,
5495 P_SOURCE_ORG_NAME => resource_rec.SOURCE_ORG_NAME ,
5496 P_SOURCE_ADDRESS1 => resource_rec.SOURCE_ADDRESS1 ,
5497 P_SOURCE_ADDRESS2 => resource_rec.SOURCE_ADDRESS2 ,
5498 P_SOURCE_ADDRESS3 => resource_rec.SOURCE_ADDRESS3 ,
5499 P_SOURCE_ADDRESS4 => resource_rec.SOURCE_ADDRESS4 ,
5500 P_SOURCE_CITY => resource_rec.SOURCE_CITY ,
5501 P_SOURCE_POSTAL_CODE => resource_rec.SOURCE_POSTAL_CODE ,
5502 P_SOURCE_STATE => resource_rec.SOURCE_STATE ,
5503 P_SOURCE_PROVINCE => resource_rec.SOURCE_PROVINCE ,
5504 P_SOURCE_COUNTY => resource_rec.SOURCE_COUNTY ,
5505 P_SOURCE_COUNTRY => resource_rec.SOURCE_COUNTRY ,
5506 P_SOURCE_MGR_ID => resource_rec.SOURCE_MGR_ID ,
5507 P_SOURCE_MGR_NAME => resource_rec.SOURCE_MGR_NAME ,
5508 P_SOURCE_BUSINESS_GRP_ID => resource_rec.SOURCE_BUSINESS_GRP_ID ,
5509 P_SOURCE_BUSINESS_GRP_NAME => resource_rec.SOURCE_BUSINESS_GRP_NAME ,
5510 P_SOURCE_FIRST_NAME => resource_rec.SOURCE_FIRST_NAME ,
5511 P_SOURCE_MIDDLE_NAME => resource_rec.SOURCE_MIDDLE_NAME ,
5512 P_SOURCE_LAST_NAME => resource_rec.SOURCE_LAST_NAME ,
5513 P_SOURCE_CATEGORY => resource_rec.SOURCE_CATEGORY ,
5514 P_SOURCE_STATUS => resource_rec.SOURCE_STATUS ,
5515 P_SOURCE_OFFICE => resource_rec.SOURCE_OFFICE ,
5516 P_SOURCE_LOCATION => resource_rec.SOURCE_LOCATION ,
5517 P_SOURCE_MAILSTOP => resource_rec.SOURCE_MAILSTOP ,
5518 X_RETURN_STATUS => x_return_status,
5519 X_MSG_COUNT => x_msg_count,
5520 X_MSG_DATA => x_msg_data,
5521 P_SOURCE_MOBILE_PHONE => resource_rec.SOURCE_MOBILE_PHONE ,
5522 P_SOURCE_PAGER => resource_rec.SOURCE_PAGER,
5523 P_USER_NAME => resource_rec.USER_NAME
5524 );
5525
5526 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5527
5528 -- dbms_output.put_line('Failed status from call to audit procedure');
5529
5530 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5531 RAISE FND_API.G_EXC_ERROR;
5532 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5534 END IF;
5535
5536 END IF;
5537
5538 /* Calling publish API to raise update resource event. */
5539 /* added by baianand on 11/04/2002 */
5540
5541 begin
5542
5543 l_resource_rec.resource_id := l_resource_id;
5544 l_resource_rec.category := resource_rec.category;
5545 l_resource_rec.user_id := resource_rec.user_id;
5546 l_resource_rec.resource_name := resource_rec.resource_name;
5547 l_resource_rec.start_date_active := resource_rec.start_date_active;
5548 l_resource_rec.end_date_active := resource_rec.end_date_active;
5549 l_resource_rec.time_zone := resource_rec.time_zone;
5550 l_resource_rec.cost_per_hr := resource_rec.cost_per_hr;
5551 l_resource_rec.primary_language := resource_rec.primary_language;
5552 l_resource_rec.secondary_language := resource_rec.secondary_language;
5553 l_resource_rec.ies_agent_login := resource_rec.ies_agent_login;
5554 l_resource_rec.server_group_id := resource_rec.server_group_id;
5555 l_resource_rec.assigned_to_group_id := resource_rec.assigned_to_group_id;
5556 l_resource_rec.cost_center := resource_rec.cost_center;
5557 l_resource_rec.charge_to_cost_center := resource_rec.charge_to_cost_center;
5558 l_resource_rec.comp_currency_code := resource_rec.comp_currency_code;
5559 l_resource_rec.commissionable_flag := resource_rec.commissionable_flag;
5560 l_resource_rec.hold_reason_code := resource_rec.hold_reason_code;
5561 l_resource_rec.hold_payment := resource_rec.hold_payment;
5562 l_resource_rec.comp_service_team_id := resource_rec.comp_service_team_id;
5563 l_resource_rec.support_site_id := resource_rec.support_site_id;
5564
5565 jtf_rs_wf_events_pub.update_resource
5566 (p_api_version => 1.0
5567 ,p_init_msg_list => fnd_api.g_false
5568 ,p_commit => fnd_api.g_false
5569 ,p_resource_rec => l_resource_rec
5570 ,x_return_status => l_return_status
5571 ,x_msg_count => l_msg_count
5572 ,x_msg_data => l_msg_data);
5573
5574 EXCEPTION when others then
5575 null;
5576 end;
5577
5578 /* End of publish API call */
5579
5580
5581 /* Calling work API for insert/update record into wf_local tables. */
5582 /* added by baianand on 08/15/2002 */
5583
5584 begin
5585 jtf_rs_wf_integration_pub.update_resource
5586 (p_api_version => 1.0
5587 ,p_init_msg_list => fnd_api.g_false
5588 ,p_commit => fnd_api.g_false
5589 ,p_resource_id => l_resource_id
5590 ,p_resource_name => resource_rec.resource_name
5591 ,p_user_id => resource_rec.user_id
5592 ,p_email_address => resource_rec.source_email
5593 ,p_start_date_active => resource_rec.start_date_active
5594 ,p_end_date_active => resource_rec.end_date_active
5595 ,x_return_status => l_return_status
5596 ,x_msg_count => l_msg_count
5597 ,x_msg_data => l_msg_data);
5598
5599 EXCEPTION when others then
5600 null;
5601 end;
5602
5603 /* End of work API call */
5604
5605 BEGIN
5606
5607 /* Increment the object version number */
5608
5609 l_object_version_num := p_object_version_num + 1;
5610
5611
5612 /* Update the row into the table by calling the table handler. */
5613
5614 jtf_rs_resource_extns_pkg.update_row(
5615 x_resource_id => l_resource_id,
5616 x_category => resource_rec.category,
5617 x_resource_number => resource_rec.resource_number,
5618 x_source_id => resource_rec.source_id,
5619 x_address_id => resource_rec.address_id,
5620 x_contact_id => resource_rec.contact_id,
5621 x_managing_employee_id => resource_rec.managing_emp_id,
5622 x_start_date_active => resource_rec.start_date_active,
5623 x_end_date_active => resource_rec.end_date_active,
5624 x_time_zone => resource_rec.time_zone,
5625 x_cost_per_hr => resource_rec.cost_per_hr,
5626 x_primary_language => resource_rec.primary_language,
5627 x_secondary_language => resource_rec.secondary_language,
5628 x_support_site_id => resource_rec.support_site_id,
5629 x_ies_agent_login => resource_rec.ies_agent_login,
5630 x_server_group_id => resource_rec.server_group_id,
5631 x_assigned_to_group_id => resource_rec.assigned_to_group_id,
5632 x_cost_center => resource_rec.cost_center,
5633 x_charge_to_cost_center => resource_rec.charge_to_cost_center,
5634 x_compensation_currency_code => resource_rec.comp_currency_code,
5635 x_commissionable_flag => resource_rec.commissionable_flag,
5636 x_hold_reason_code => resource_rec.hold_reason_code,
5637 x_hold_payment => resource_rec.hold_payment,
5638 x_comp_service_team_id => resource_rec.comp_service_team_id,
5639 x_user_id => resource_rec.user_id,
5640 --x_location => resource_rec.location,
5641 x_transaction_number => resource_rec.transaction_number,
5642 x_object_version_number => l_object_version_num,
5643 x_attribute1 => resource_rec.attribute1,
5644 x_attribute2 => resource_rec.attribute2,
5645 x_attribute3 => resource_rec.attribute3,
5646 x_attribute4 => resource_rec.attribute4,
5647 x_attribute5 => resource_rec.attribute5,
5648 x_attribute6 => resource_rec.attribute6,
5649 x_attribute7 => resource_rec.attribute7,
5650 x_attribute8 => resource_rec.attribute8,
5651 x_attribute9 => resource_rec.attribute9,
5652 x_attribute10 => resource_rec.attribute10,
5653 x_attribute11 => resource_rec.attribute11,
5654 x_attribute12 => resource_rec.attribute12,
5655 x_attribute13 => resource_rec.attribute13,
5656 x_attribute14 => resource_rec.attribute14,
5657 x_attribute15 => resource_rec.attribute15,
5658 x_attribute_category => resource_rec.attribute_category,
5659 x_last_update_date => SYSDATE,
5660 x_last_updated_by => jtf_resource_utl.updated_by,
5661 x_last_update_login => jtf_resource_utl.login_id,
5662 x_resource_name => resource_rec.resource_name,
5663 x_source_name => resource_rec.source_name,
5664 x_source_number => resource_rec.source_number,
5665 x_source_job_title => resource_rec.source_job_title,
5666 x_source_email => resource_rec.source_email,
5667 x_source_phone => resource_rec.source_phone,
5668 x_source_org_id => resource_rec.source_org_id,
5669 x_source_org_name => resource_rec.source_org_name,
5670 x_source_address1 => resource_rec.source_address1,
5671 x_source_address2 => resource_rec.source_address2,
5672 x_source_address3 => resource_rec.source_address3,
5673 x_source_address4 => resource_rec.source_address4,
5674 x_source_city => resource_rec.source_city,
5675 x_source_postal_code => resource_rec.source_postal_code,
5676 x_source_state => resource_rec.source_state,
5677 x_source_province => resource_rec.source_province,
5678 x_source_county => resource_rec.source_county,
5679 x_source_country => resource_rec.source_country,
5680 x_source_mgr_id => resource_rec.source_mgr_id,
5681 x_source_mgr_name => resource_rec.source_mgr_name,
5682 x_source_business_grp_id => resource_rec.source_business_grp_id,
5683 x_source_business_grp_name=> resource_rec.source_business_grp_name,
5684 x_SOURCE_FIRST_NAME => resource_rec.SOURCE_FIRST_NAME ,
5685 x_SOURCE_MIDDLE_NAME => resource_rec.SOURCE_MIDDLE_NAME ,
5686 x_SOURCE_LAST_NAME => resource_rec.SOURCE_LAST_NAME ,
5687 x_SOURCE_CATEGORY => resource_rec.SOURCE_CATEGORY ,
5688 x_SOURCE_STATUS => resource_rec.SOURCE_STATUS ,
5689 x_SOURCE_OFFICE => resource_rec.SOURCE_OFFICE ,
5690 x_SOURCE_LOCATION => resource_rec.SOURCE_LOCATION ,
5691 x_SOURCE_MAILSTOP => resource_rec.SOURCE_MAILSTOP ,
5692 x_USER_NAME => resource_rec.USER_NAME ,
5693 x_SOURCE_JOB_ID => resource_rec.SOURCE_JOB_ID,
5694 x_PARTY_ID => resource_rec.PERSON_PARTY_ID,
5695 x_SOURCE_MOBILE_PHONE => resource_rec.SOURCE_MOBILE_PHONE ,
5696 x_SOURCE_PAGER => resource_rec.SOURCE_PAGER
5697 );
5698
5699
5700
5701 /* Return the new value of the object version number */
5702
5703 p_object_version_num := l_object_version_num;
5704
5705
5706
5707 EXCEPTION
5708
5709 WHEN NO_DATA_FOUND THEN
5710
5711 -- dbms_output.put_line('Error in Table Handler');
5712
5713 IF c_resource_update%ISOPEN THEN
5714
5715 CLOSE c_resource_update;
5716
5717 END IF;
5718
5719 x_return_status := fnd_api.g_ret_sts_unexp_error;
5720
5721 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
5722 fnd_msg_pub.add;
5723
5724 RAISE fnd_api.g_exc_error;
5725
5726
5727 END;
5728
5729 -- dbms_output.put_line('Resource Successfully Updated');
5730
5731
5732 /* Close the cursors */
5733
5734 IF c_resource_update%ISOPEN THEN
5735
5736 CLOSE c_resource_update;
5737
5738 END IF;
5739
5740
5741 /* Make the post processing call to the user hooks */
5742
5743 /* Post Call to the Customer Type User Hook */
5744
5745 IF jtf_resource_utl.ok_to_execute(
5746 'JTF_RS_RESOURCE_PVT',
5747 'UPDATE_RESOURCE',
5748 'A',
5749 'C')
5750 THEN
5751 IF jtf_usr_hks.ok_to_execute(
5752 'JTF_RS_RESOURCE_PVT',
5753 'UPDATE_RESOURCE',
5754 'A',
5755 'C')
5756 THEN
5757
5758 jtf_rs_resource_cuhk.update_resource_post(
5759 p_resource_id => l_resource_id,
5760 p_managing_emp_id => l_managing_emp_id,
5761 p_start_date_active => l_start_date_active,
5762 p_end_date_active => l_end_date_active,
5763 p_time_zone => l_time_zone,
5764 p_cost_per_hr => l_cost_per_hr,
5765 p_primary_language => l_primary_language,
5766 p_secondary_language => l_secondary_language,
5767 p_support_site_id => l_support_site_id,
5768 p_ies_agent_login => l_ies_agent_login,
5769 p_server_group_id => l_server_group_id,
5770 p_assigned_to_group_id => l_assigned_to_group_id,
5771 p_cost_center => l_cost_center,
5772 p_charge_to_cost_center=> l_charge_to_cost_center,
5773 p_comp_currency_code => l_comp_currency_code,
5774 p_commissionable_flag => l_commissionable_flag,
5775 p_hold_reason_code => l_hold_reason_code,
5776 p_hold_payment => l_hold_payment,
5777 p_comp_service_team_id => l_comp_service_team_id,
5778 p_user_id => l_user_id,
5779 --p_location => l_location,
5780 x_return_status => x_return_status);
5781
5782 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5783
5784 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
5785
5786
5787 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
5788 fnd_msg_pub.add;
5789
5790 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5791 RAISE FND_API.G_EXC_ERROR;
5792 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5794 END IF;
5795
5796 END IF;
5797
5798 END IF;
5799 END IF;
5800
5801
5802 /* Post Call to the Vertical Type User Hook */
5803
5804 IF jtf_resource_utl.ok_to_execute(
5805 'JTF_RS_RESOURCE_PVT',
5806 'UPDATE_RESOURCE',
5807 'A',
5808 'V')
5809 THEN
5810 IF jtf_usr_hks.ok_to_execute(
5811 'JTF_RS_RESOURCE_PVT',
5812 'UPDATE_RESOURCE',
5813 'A',
5814 'V')
5815 THEN
5816
5817 jtf_rs_resource_vuhk.update_resource_post(
5818 p_resource_id => l_resource_id,
5819 p_managing_emp_id => l_managing_emp_id,
5820 p_start_date_active => l_start_date_active,
5821 p_end_date_active => l_end_date_active,
5822 p_time_zone => l_time_zone,
5823 p_cost_per_hr => l_cost_per_hr,
5824 p_primary_language => l_primary_language,
5825 p_secondary_language => l_secondary_language,
5826 p_support_site_id => l_support_site_id,
5827 p_ies_agent_login => l_ies_agent_login,
5828 p_server_group_id => l_server_group_id,
5829 p_assigned_to_group_id => l_assigned_to_group_id,
5830 p_cost_center => l_cost_center,
5831 p_charge_to_cost_center => l_charge_to_cost_center,
5832 p_comp_currency_code => l_comp_currency_code,
5833 p_commissionable_flag => l_commissionable_flag,
5834 p_hold_reason_code => l_hold_reason_code,
5835 p_hold_payment => l_hold_payment,
5836 p_comp_service_team_id => l_comp_service_team_id,
5837 p_user_id => l_user_id,
5838 --p_location => l_location,
5839 x_return_status => x_return_status);
5840
5841 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5842
5843 -- dbms_output.put_line('Returned Error status from the Post Vertical User Hook');
5844
5845
5846 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
5847 fnd_msg_pub.add;
5848
5849 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5850 RAISE FND_API.G_EXC_ERROR;
5851 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5853 END IF;
5854
5855
5856 END IF;
5857
5858 END IF;
5859 END IF;
5860
5861
5862 /* Post Call to the Internal Type User Hook */
5863
5864 IF jtf_resource_utl.ok_to_execute(
5865 'JTF_RS_RESOURCE_PVT',
5866 'UPDATE_RESOURCE',
5867 'A',
5868 'I')
5869 THEN
5870 IF jtf_usr_hks.ok_to_execute(
5871 'JTF_RS_RESOURCE_PVT',
5872 'UPDATE_RESOURCE',
5873 'A',
5874 'I')
5875 THEN
5876
5877 jtf_rs_resource_iuhk.update_resource_post(
5878 p_resource_id => l_resource_id,
5879 p_managing_emp_id => l_managing_emp_id,
5880 p_start_date_active => l_start_date_active,
5881 p_end_date_active => l_end_date_active,
5882 p_time_zone => l_time_zone,
5883 p_cost_per_hr => l_cost_per_hr,
5884 p_primary_language => l_primary_language,
5885 p_secondary_language => l_secondary_language,
5886 p_support_site_id => l_support_site_id,
5887 p_ies_agent_login => l_ies_agent_login,
5888 p_server_group_id => l_server_group_id,
5889 p_assigned_to_group_id => l_assigned_to_group_id,
5890 p_cost_center => l_cost_center,
5891 p_charge_to_cost_center => l_charge_to_cost_center,
5892 p_comp_currency_code => l_comp_currency_code,
5893 p_commissionable_flag => l_commissionable_flag,
5894 p_hold_reason_code => l_hold_reason_code,
5895 p_hold_payment => l_hold_payment,
5896 p_comp_service_team_id => l_comp_service_team_id,
5897 p_user_id => l_user_id,
5898 --p_location => l_location,
5899 x_return_status => x_return_status);
5900
5901 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5902
5903 -- dbms_output.put_line('Returned Error status from the Post Customer User Hook');
5904
5905
5906 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
5907 fnd_msg_pub.add;
5908
5909 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5910 RAISE FND_API.G_EXC_ERROR;
5911 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5912 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5913 END IF;
5914
5915
5916 END IF;
5917
5918 END IF;
5919 END IF;
5920
5921
5922 /* Standard call for Message Generation */
5923
5924 IF jtf_resource_utl.ok_to_execute(
5925 'JTF_RS_RESOURCE_PVT',
5926 'UPDATE_RESOURCE',
5927 'M',
5928 'M')
5929 THEN
5930 IF jtf_usr_hks.ok_to_execute(
5931 'JTF_RS_RESOURCE_PVT',
5932 'UPDATE_RESOURCE',
5933 'M',
5934 'M')
5935 THEN
5936
5937 IF (jtf_rs_resource_cuhk.ok_to_generate_msg(
5938 p_resource_id => l_resource_id,
5939 x_return_status => x_return_status) )
5940 THEN
5941
5942 /* Get the bind data id for the Business Object Instance */
5943
5944 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
5945
5946
5947 /* Set bind values for the bind variables in the Business Object SQL */
5948
5949 jtf_usr_hks.load_bind_data(l_bind_data_id, 'resource_id', l_resource_id, 'S', 'N');
5950
5951
5952 /* Call the message generation API */
5953
5954 jtf_usr_hks.generate_message(
5955 p_prod_code => 'RS',
5956 p_bus_obj_code => 'RES',
5957 p_action_code => 'U',
5958 p_bind_data_id => l_bind_data_id,
5959 x_return_code => x_return_status);
5960
5961
5962 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
5963
5964 -- dbms_output.put_line('Returned Error status from the Message Generation API');
5965
5966
5967 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
5968 fnd_msg_pub.add;
5969
5970 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
5971 RAISE FND_API.G_EXC_ERROR;
5972 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
5973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5974 END IF;
5975
5976
5977 END IF;
5978
5979 END IF;
5980
5981 END IF;
5982 END IF;
5983
5984
5985 IF fnd_api.to_boolean(p_commit) THEN
5986
5987 COMMIT WORK;
5988
5989 END IF;
5990
5991 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
5992
5993 EXCEPTION
5994
5995
5996 WHEN fnd_api.g_exc_error THEN
5997 ROLLBACK TO update_resource_pvt;
5998 x_return_status := fnd_api.g_ret_sts_error;
5999 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
6000 p_data => x_msg_data);
6001 WHEN fnd_api.g_exc_unexpected_error THEN
6002 ROLLBACK TO update_resource_pvt;
6003 x_return_status := fnd_api.g_ret_sts_unexp_error;
6004 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
6005 p_data => x_msg_data);
6006 WHEN OTHERS THEN
6007 ROLLBACK TO update_resource_pvt;
6008 -- The below lines removed as a part of fixing GSCC errors in R12 for jtfrspub.pls
6009 -- IF NOT(jtf_resource_utl.check_access(l_value))
6010 -- THEN
6011 -- IF(l_value = 'XMLGEN')
6012 -- THEN
6013 -- fnd_message.set_name ('JTF', 'JTF_RS_XMLGEN_ERR');
6014 -- FND_MSG_PUB.add;
6015 -- ELSIF(l_value = 'JTF_USR_HKS')
6016 -- THEN
6017 -- fnd_message.set_name ('JTF', 'JTF_RS_JUHK_ERR');
6018 -- FND_MSG_PUB.add;
6019 -- END IF;
6020 -- ELSE
6021 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
6022 fnd_message.set_token('P_SQLCODE',SQLCODE);
6023 fnd_message.set_token('P_SQLERRM',SQLERRM);
6024 fnd_message.set_token('P_API_NAME', l_api_name);
6025 FND_MSG_PUB.add;
6026 -- END IF;
6027 x_return_status := fnd_api.g_ret_sts_unexp_error;
6028 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
6029 p_data => x_msg_data);
6030
6031
6032 END update_resource;
6033
6034
6035 /* Procedure to delete the resource of category = TBH */
6036
6037 PROCEDURE DELETE_RESOURCE(
6038 P_API_VERSION IN NUMBER,
6039 P_INIT_MSG_LIST IN VARCHAR2,
6040 P_COMMIT IN VARCHAR2,
6041 P_RESOURCE_ID IN NUMBER,
6042 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
6043 X_MSG_COUNT OUT NOCOPY NUMBER,
6044 X_MSG_DATA OUT NOCOPY VARCHAR2 )
6045 IS
6046 CURSOR res_cur(L_RESOURCE_ID NUMBER)
6047 IS
6048 SELECT category
6049 FROM jtf_rs_resource_extns
6050 WHERE resource_id = l_resource_id;
6051
6052 l_api_version CONSTANT NUMBER := 1.0;
6053 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE';
6054 l_category JTF_RS_RESOURCE_EXTNS.CATEGORY%TYPE ;
6055 /* Moved the initial assignment of below variable to inside begin */
6056 L_RESOURCE_ID JTF_RS_RESOURCE_EXTNS.RESOURCE_ID%TYPE;
6057 l_bind_data_id NUMBER;
6058 l_value varchar2(100);
6059
6060 l_return_status VARCHAR2(2000);
6061 l_msg_count NUMBER;
6062 l_msg_data VARCHAR2(2000);
6063
6064 BEGIN
6065
6066 L_RESOURCE_ID := p_resource_id;
6067
6068 SAVEPOINT delete_resource_pvt;
6069
6070 x_return_status := fnd_api.g_ret_sts_success;
6071
6072
6073 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
6074
6075 RAISE fnd_api.g_exc_unexpected_error;
6076
6077 END IF;
6078
6079
6080 IF fnd_api.to_boolean(p_init_msg_list) THEN
6081
6082 fnd_msg_pub.initialize;
6083
6084 END IF;
6085
6086
6087 /* Make the pre processing call to the user hooks */
6088
6089 /* Pre Call to the Customer Type User Hook */
6090
6091 IF jtf_resource_utl.ok_to_execute(
6092 'JTF_RS_RESOURCE_PVT',
6093 'DELETE_RESOURCE',
6094 'B',
6095 'C')
6096 THEN
6097 IF jtf_usr_hks.ok_to_execute(
6098 'JTF_RS_RESOURCE_PVT',
6099 'DELETE_RESOURCE',
6100 'B',
6101 'C')
6102 THEN
6103
6104 jtf_rs_resource_cuhk.delete_resource_pre(
6105 p_resource_id => l_resource_id,
6106 x_return_status => x_return_status);
6107
6108 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
6109 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_CUST_USR_HOOK');
6110 fnd_msg_pub.add;
6111 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
6112 RAISE FND_API.G_EXC_ERROR;
6113 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
6114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6115 END IF;
6116
6117 END IF;
6118
6119 END IF;
6120 END IF;
6121
6122
6123 /* Pre Call to the Vertical Type User Hook */
6124
6125 IF jtf_resource_utl.ok_to_execute(
6126 'JTF_RS_RESOURCE_PVT',
6127 'DELETE_RESOURCE',
6128 'B',
6129 'V')
6130 THEN
6131 IF jtf_usr_hks.ok_to_execute(
6132 'JTF_RS_RESOURCE_PVT',
6133 'DELETE_RESOURCE',
6134 'B',
6135 'V')
6136 THEN
6137
6138 jtf_rs_resource_vuhk.delete_resource_pre(
6139 p_resource_id => l_resource_id,
6140 x_return_status => x_return_status);
6141
6142 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
6143 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_VERT_USR_HOOK');
6144 fnd_msg_pub.add;
6145 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
6146 RAISE FND_API.G_EXC_ERROR;
6147 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
6148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6149 END IF;
6150
6151 END IF;
6152 END IF;
6153 END IF;
6154
6155
6156 /* Pre Call to the Internal Type User Hook */
6157
6158 IF jtf_usr_hks.ok_to_execute(
6159 'JTF_RS_RESOURCE_PVT',
6160 'DELETE_RESOURCE',
6161 'B',
6162 'I')
6163 THEN
6164 IF jtf_usr_hks.ok_to_execute(
6165 'JTF_RS_RESOURCE_PVT',
6166 'DELETE_RESOURCE',
6167 'B',
6168 'I')
6169 THEN
6170
6171 jtf_rs_resource_iuhk.delete_resource_pre(
6172 p_resource_id => l_resource_id,
6173 x_return_status => x_return_status);
6174
6175 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
6176 fnd_message.set_name('JTF', 'JTF_RS_ERR_PRE_INT_USR_HOOK');
6177 fnd_msg_pub.add;
6178
6179 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
6180 RAISE FND_API.G_EXC_ERROR;
6181 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
6182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6183 END IF;
6184
6185
6186 END IF;
6187
6188 END IF;
6189 END IF;
6190
6191
6192 OPEN res_cur(l_resource_id);
6193 FETCH res_cur INTO l_category;
6194 CLOSE res_cur;
6195
6196 --allow delete only for category of type TBH
6197 IF (l_category = 'TBH')
6198 THEN
6199 /* Make a call to the Resource Audit API */
6200
6201 jtf_rs_resource_extns_aud_pvt.delete_resource
6202 (P_API_VERSION => 1,
6203 P_INIT_MSG_LIST => fnd_api.g_false,
6204 P_COMMIT => fnd_api.g_false,
6205 P_RESOURCE_ID => l_resource_id,
6206 X_RETURN_STATUS => x_return_status,
6207 X_MSG_COUNT => x_msg_count,
6208 X_MSG_DATA => x_msg_data
6209 );
6210
6211 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
6212 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
6213 RAISE FND_API.G_EXC_ERROR;
6214 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
6215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6216 END IF;
6217
6218
6219 END IF;
6220
6221 --delete the row from the table
6222 jtf_rs_resource_extns_pkg.delete_row(
6223 x_resource_id => l_resource_id );
6224
6225
6226 END IF; --end of category check for TBH
6227
6228
6229 /* Make the post processing call to the user hooks */
6230
6231 /* Post Call to the Customer Type User Hook */
6232
6233 IF jtf_usr_hks.ok_to_execute(
6234 'JTF_RS_RESOURCE_PVT',
6235 'DELETE_RESOURCE',
6236 'A',
6237 'C')
6238 THEN
6239
6240 jtf_rs_resource_cuhk.delete_resource_post(
6241 p_resource_id => l_resource_id,
6242 x_return_status => x_return_status);
6243 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
6244 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_CUST_USR_HOOK');
6245 fnd_msg_pub.add;
6246 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
6247 RAISE FND_API.G_EXC_ERROR;
6248 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
6249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6250 END IF;
6251
6252 END IF;
6253
6254 END IF;
6255
6256
6257 /* Post Call to the Vertical Type User Hook */
6258
6259 IF jtf_resource_utl.ok_to_execute(
6260 'JTF_RS_RESOURCE_PVT',
6261 'DELETE_RESOURCE',
6262 'A',
6263 'V')
6264 THEN
6265 IF jtf_usr_hks.ok_to_execute(
6266 'JTF_RS_RESOURCE_PVT',
6267 'DELETE_RESOURCE',
6268 'A',
6269 'V')
6270 THEN
6271
6272 jtf_rs_resource_vuhk.delete_resource_post(
6273 p_resource_id => l_resource_id,
6274 x_return_status => x_return_status);
6275 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
6276 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_VERT_USR_HOOK');
6277 fnd_msg_pub.add;
6278 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
6279 RAISE FND_API.G_EXC_ERROR;
6280 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
6281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6282 END IF;
6283
6284 END IF;
6285 END IF;
6286 END IF;
6287
6288
6289 /* Post Call to the Internal Type User Hook */
6290
6291 IF jtf_resource_utl.ok_to_execute(
6292 'JTF_RS_RESOURCE_PVT',
6293 'DELETE_RESOURCE',
6294 'A',
6295 'I')
6296 THEN
6297 IF jtf_usr_hks.ok_to_execute(
6298 'JTF_RS_RESOURCE_PVT',
6299 'DELETE_RESOURCE',
6300 'A',
6301 'I')
6302 THEN
6303
6304 jtf_rs_resource_iuhk.delete_resource_post(
6305 p_resource_id => l_resource_id,
6306 x_return_status => x_return_status);
6307
6308 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
6309 fnd_message.set_name('JTF', 'JTF_RS_ERR_POST_INT_USR_HOOK');
6310 fnd_msg_pub.add;
6311 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
6312 RAISE FND_API.G_EXC_ERROR;
6313 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
6314 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6315 END IF;
6316
6317 END IF;
6318
6319 END IF;
6320 END IF;
6321
6322
6323 /* Standard call for Message Generation */
6324
6325 IF jtf_resource_utl.ok_to_execute(
6326 'JTF_RS_RESOURCE_PVT',
6327 'DELETE_RESOURCE',
6328 'M',
6329 'M')
6330 THEN
6331 IF jtf_usr_hks.ok_to_execute(
6332 'JTF_RS_RESOURCE_PVT',
6333 'DELETE_RESOURCE',
6334 'M',
6335 'M')
6336 THEN
6337
6338 IF (jtf_rs_resource_cuhk.ok_to_generate_msg(
6339 p_resource_id => l_resource_id,
6340 x_return_status => x_return_status) )
6341 THEN
6342
6343 /* Get the bind data id for the Business Object Instance */
6344
6345 l_bind_data_id := jtf_usr_hks.get_bind_data_id;
6346
6347
6348 /* Set bind values for the bind variables in the Business Object SQL */
6349
6350 jtf_usr_hks.load_bind_data(l_bind_data_id, 'resource_id', l_resource_id, 'S', 'N');
6351
6352
6353 /* Call the message generation API */
6354
6355 jtf_usr_hks.generate_message(
6356 p_prod_code => 'RS',
6357 p_bus_obj_code => 'RES',
6358 p_action_code => 'D',
6359 p_bind_data_id => l_bind_data_id,
6360 x_return_code => x_return_status);
6361
6362
6363 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
6364
6365 -- dbms_output.put_line('Returned Error status from the Message Generation API');
6366
6367
6368 fnd_message.set_name('JTF', 'JTF_RS_ERR_MESG_GENERATE_API');
6369 fnd_msg_pub.add;
6370
6371 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
6372 RAISE FND_API.G_EXC_ERROR;
6373 ELSIF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
6374 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6375 END IF;
6376
6377
6378 END IF;
6379
6380 END IF;
6381
6382 END IF;
6383 END IF;
6384
6385
6386 IF fnd_api.to_boolean(p_commit) THEN
6387
6388 COMMIT WORK;
6389
6390 END IF;
6391
6392 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
6393
6394 if (l_category = 'TBH') then
6395
6396 /* Calling publish API to raise create resource event. */
6397 /* added by baianand on 11/04/2002 */
6398
6399 begin
6400 jtf_rs_wf_events_pub.delete_resource
6401 (p_api_version => 1.0
6402 ,p_init_msg_list => fnd_api.g_false
6403 ,p_commit => fnd_api.g_false
6404 ,p_resource_id => l_resource_id
6405 ,x_return_status => l_return_status
6406 ,x_msg_count => l_msg_count
6407 ,x_msg_data => l_msg_data);
6408
6409 EXCEPTION when others then
6410 null;
6411 end;
6412
6413 /* End of publish API call */
6414
6415 /* Calling work API for delete record from wf_local tables. */
6416 /* added by baianand on 08/13/2002 */
6417
6418 begin
6419 jtf_rs_wf_integration_pub.delete_resource
6420 (p_api_version => 1.0
6421 ,p_init_msg_list => fnd_api.g_false
6422 ,p_commit => fnd_api.g_false
6423 ,p_resource_id => l_resource_id
6424 ,x_return_status => l_return_status
6425 ,x_msg_count => l_msg_count
6426 ,x_msg_data => l_msg_data);
6427
6428 EXCEPTION when others then
6429 null;
6430 end;
6431 end if;
6432
6433 /* End of work API call */
6434
6435 EXCEPTION
6436
6437
6438 WHEN fnd_api.g_exc_error THEN
6439 ROLLBACK TO delete_resource_pvt;
6440 x_return_status := fnd_api.g_ret_sts_error;
6441 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
6442 p_data => x_msg_data);
6443 WHEN fnd_api.g_exc_unexpected_error THEN
6444 ROLLBACK TO delete_resource_pvt;
6445 x_return_status := fnd_api.g_ret_sts_unexp_error;
6446 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
6447 p_data => x_msg_data);
6448 WHEN OTHERS THEN
6449 ROLLBACK TO delete_resource_pvt;
6450 -- The below lines removed as a part of fixing GSCC errors in R12 for jtfrspub.pls
6451 -- IF NOT(jtf_resource_utl.check_access(l_value))
6452 -- THEN
6453 -- IF(l_value = 'XMLGEN')
6454 -- THEN
6455 -- fnd_message.set_name ('JTF', 'JTF_RS_XMLGEN_ERR');
6456 -- FND_MSG_PUB.add;
6457 -- ELSIF(l_value = 'JTF_USR_HKS')
6458 -- THEN
6459 -- fnd_message.set_name ('JTF', 'JTF_RS_JUHK_ERR');
6460 -- FND_MSG_PUB.add;
6461 -- END IF;
6462 -- ELSE
6463 fnd_message.set_name ('JTF', 'JTF_RS_UNEXP_ERROR');
6464 fnd_message.set_token('P_SQLCODE',SQLCODE);
6465 fnd_message.set_token('P_SQLERRM',SQLERRM);
6466 fnd_message.set_token('P_API_NAME', l_api_name);
6467 FND_MSG_PUB.add;
6468 -- END IF;
6469 x_return_status := fnd_api.g_ret_sts_unexp_error;
6470 FND_MSG_PUB.count_and_get (p_count => x_msg_count,
6471 p_data => x_msg_data);
6472
6473 END delete_resource;
6474
6475 END jtf_rs_resource_pvt;