[Home] [Help]
PACKAGE BODY: APPS.IEX_COLLECTORS_TO_RESOURCE
Source
1 PACKAGE BODY IEX_COLLECTORS_TO_RESOURCE AS
2 /* $Header: iexctrab.pls 120.6 2006/07/18 21:19:47 acaraujo noship $ */
3 /*-----------------------------------------------------------------------
4 UPDATE_COLLECTORS
5
6 This procedure takes the following primary parameters in the process
7 1. P_debug to enable or disable the log messages.
8 ------------------------------------------------------------------------*/
9
10 PROCEDURE MERGE_COLLECTORS(P_DEBUG IN VARCHAR2);
11
12 PROCEDURE UPDATE_COLLECTORS
13 (
14 ERRBUF OUT NOCOPY VARCHAR2,
15 RETCODE OUT NOCOPY VARCHAR2,
16 P_RESP1 IN VARCHAR2,
17 P_RESP2 IN VARCHAR2,
18 P_RESP3 IN VARCHAR2,
19 P_RESP4 IN VARCHAR2,
20 P_RESP5 IN VARCHAR2,
21 P_debug IN VARCHAR2
22 )
23 IS
24 l_date_time VARCHAR2(25);
25 l_colcount NUMBER;
26 BEGIN
27
28 IF (p_debug = 'Y') THEN
29 dbms_session.set_sql_trace(TRUE);
30 END IF;
31
32 fnd_file.put_line(FND_FILE.LOG,'Updating collector resources started ');
33 fnd_file.put_line(FND_FILE.LOG,'Parameter : p_debug := ' || P_debug);
34
35 fnd_file.put_line(FND_FILE.LOG,' Parameter : Responsibility 1 := '|| P_resp1);
36 fnd_file.put_line(FND_FILE.LOG,' Parameter : Responsibility 2 := '|| p_resp2);
37 fnd_file.put_line(FND_FILE.LOG,' Parameter : Responsibility 3 := '|| p_resp3);
38 fnd_file.put_line(FND_FILE.LOG,' Parameter : Responsibility 4 := '|| p_resp4);
39 fnd_file.put_line(FND_FILE.LOG,' Parameter : Responsibility 5 := '|| p_resp5);
40
41 If (P_debug = 'Y') then
42 Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
43 fnd_file.put_line(FND_FILE.LOG,'Date and Time Before starting the update on ar_collectors '|| l_date_time );
44 End If;
45
46 /* kasreeni 07/14/2005 Changed to Merge_Collectors as function */
47 /* kasreeni 07/14/2005 Missed a COMMIT, when no responsibility selected */
48 MERGE_COLLECTORS(P_DEBUG);
49
50 IF P_RESP1 IS NOT NULL THEN
51 UPDATE_COLLECTORS_PVT(p_resp => P_RESP1,p_debug => p_debug);
52 END IF;
53
54 IF P_RESP2 IS NOT NULL THEN
55 UPDATE_COLLECTORS_PVT(p_resp => P_RESP2,p_debug => p_debug);
56 END IF;
57
58 IF P_RESP3 IS NOT NULL THEN
59 UPDATE_COLLECTORS_PVT(p_resp => P_RESP3,p_debug => p_debug);
60 END IF;
61
62 IF P_RESP4 IS NOT NULL THEN
63 UPDATE_COLLECTORS_PVT(p_resp => P_RESP4,p_debug => p_debug);
64 END IF;
65
66 IF P_RESP5 IS NOT NULL THEN
67 UPDATE_COLLECTORS_PVT(p_resp => P_RESP5,p_debug => p_debug);
68 END IF;
69
70 IF (P_RESP1 IS NULL AND P_RESP2 IS NULL AND P_RESP3 IS NULL AND P_RESP4 IS NULL AND P_RESP5 IS NULL) THEN
71 fnd_file.put_line(FND_FILE.LOG,' Select atleast one responsibility in the parameters ');
72 END IF;
73
74 END;
75
76 /* BEGIN KASREENI 05/17/2005 Made it as procedure to normalize */
77 PROCEDURE MERGE_COLLECTORS ( P_DEBUG IN VARCHAR2)
78 IS
79 l_colcount NUMBER;
80 l_date_time VARCHAR2(25);
81 BEGIN
82 If (P_debug = 'Y') then
83 Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
84 fnd_file.put_line(FND_FILE.LOG,'Date and Time Before starting the update on ar_collectors '|| l_date_time );
85 End If;
86
87 UPDATE ar_collectors ARC set
88 resource_type = 'RS_RESOURCE',
89 resource_id =
90 ( SELECT max(jtfrs.resource_id)
91 FROM jtf_rs_resource_extns jtfrs
92 WHERE
93 jtfrs.source_id is NOT NULL
94 AND jtfrs.category = 'EMPLOYEE'
95 AND Trunc(start_date_active) <= Trunc(sysdate)
96 AND Nvl(Trunc(end_date_active),sysdate) >= Trunc(sysdate)
97 AND arc.employee_id = jtfrs.source_id)
98 WHERE employee_id is not null
99 AND resource_id is null;
100
101 l_colcount := sql%rowcount;
102 fnd_file.put_line(FND_FILE.LOG, l_colcount || ' Record(s) updated in ar_collectors by merging Resource' ) ;
103 Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
104 If (P_debug = 'Y') then
105 fnd_file.put_line(FND_FILE.LOG,'Date and Time After finishing the update on ar_collectors'|| l_date_time );
106 End If;
107 if (l_colcount > 0) then
108 COMMIT;
109 end if;
110
111 Exception when others then
112 fnd_file.put_line(FND_FILE.LOG, 'Error while update the ar_collectors' || sqlerrm);
113 END;
114 /* END KASREENI 05/17/2005 MADE IT AS PROCEDURE TO NORMALIZE */
115
116 PROCEDURE UPDATE_COLLECTORS_PVT
117 (
118 p_resp IN VARCHAR2,
119 P_debug IN VARCHAR2
120 )
121 IS
122 l_date_time VARCHAR2(20);
123 l_return_status VARCHAR2(5);
124 l_msg_count NUMBER;
125 l_msg_data VARCHAR2(100);
126 l_resource_id NUMBER;
127 l_resource_number NUMBER;
128
129 l_Role_id NUMBER;
130 l_role_relate_id NUMBER;
131 l_responsibility_id NUMBER;
132
133 CURSOR C_ROLE_ID IS
134 SELECT role_id from JTF_RS_ROLES_B WHERE ROLE_CODE = 'IEX_AGENT' and ROLE_TYPE_CODE = 'COLLECTIONS';
135
136 --Bug4930348. Use Responsibility Id. Fix by LKKUMAR on 16-Jan-2006. Start.
137 CURSOR C_RESPONSIBILITY IS
138 SELECT RESPONSIBILITY_ID FROM fnd_responsibility_vl fr
139 WHERE responsibility_name LIKE P_RESP;
140
141
142 CURSOR INSERT_RESOURCE IS
143 SELECT jtfrs.resource_id,SOURCE_ID,SOURCE_NAME,jtfrs.user_name
144 from fnd_user_resp_groups furg, fnd_responsibility_vl fr, jtf_rs_resource_extns jtfrs
145 WHERE fr.responsibility_id = furg.responsibility_id
146 AND jtfrs.user_id = furg.user_id
147 AND furg.start_date < sysdate and (furg.end_date is null or furg.end_date > sysdate)
148 AND jtfrs.source_id is not null and jtfrs.user_id is not null
149 AND jtfrs.category = 'EMPLOYEE'
150 -- AND fr.responsibility_name like P_RESP --Bug4930348.
151 AND fr.responsibility_id = l_responsibility_id --Bug4930348.
152 AND jtfrs.RESOURCE_ID NOT IN
153 (SELECT resource_id FROM
154 ar_collectors ac
155 WHERE employee_id is not null
156 AND ac.employee_id = jtfrs.source_id
157 AND ac.resource_id = jtfrs.resource_id
158 AND ac.resource_type = 'RS_RESOURCE' )
159 AND jtfrs.user_id is not null
160 ORDER BY 1;
161 --Bug4930348. Use Responsibility Id. Fix by LKKUMAR on 16-Jan-2006. End.
162
163 l_collector_id NUMBER;
164
165 l_colcount NUMBER;
166 l_check NUMBER;
167
168 BEGIN
169
170 fnd_file.put_line(FND_FILE.LOG,'Updating collector resource pvt started for Responsibility ' || P_RESP);
171 --RETCODE := 'TRUE';
172 --Bug4930348. Use Responsibility Id. Fix by LKKUMAR on 16-Jan-2006. Start.
173 OPEN C_RESPONSIBILITY;
174 FETCH C_RESPONSIBILITY INTO l_responsibility_id;
175 CLOSE C_RESPONSIBILITY;
176
177 If (P_debug = 'Y') then
178 fnd_file.put_line(FND_FILE.LOG, 'Responsibility Id Fetched is ' || l_responsibility_id );
179 End If;
180 --Bug4930348. Use Responsibility Id. Fix by LKKUMAR on 16-Jan-2006. End.
181
182
183
184 SELECT to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
185 fnd_file.put_line(FND_FILE.LOG,'Starting the conversion ' || l_date_time);
186
187
188 -- Inserting in to AR_COLLECTORS
189
190 If (P_debug = 'Y') then
191 Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
192 fnd_file.put_line(FND_FILE.LOG,'Date and Time After finishing the update '|| l_date_time );
193 End If;
194
195
196 -- Inserting in to AR_COLLECTORS
197 FOR I IN INSERT_RESOURCE LOOP
198 If (P_debug = 'Y') then
199 Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
200 fnd_file.put_line(FND_FILE.LOG,'Date and Time Before starting the Insert '|| l_date_time );
201 End If;
202 BEGIN
203 SELECT AR_COLLECTORS_S.NEXTVAL
204 INTO L_COLLECTOR_ID
205 FROM DUAL;
206 Exception when others then
207 If (P_debug = 'Y') then
208 fnd_file.put_line(FND_FILE.LOG,'Error while selecting the colector_id sequence ' || SQLERRM);
209 End If;
210 END;
211 If (p_debug = 'Y') then
212 fnd_file.put_line(FND_FILE.LOG,'Before Starting to insert Resouce_id ' || i.resource_id || ' For Responsibility ' || p_resp);
213 End If;
214
215 BEGIN
216 INSERT INTO AR_COLLECTORS
217 (COLLECTOR_ID ,
218 LAST_UPDATED_BY ,
219 LAST_UPDATE_DATE ,
220 LAST_UPDATE_LOGIN ,
221 CREATION_DATE ,
222 CREATED_BY ,
223 NAME ,
224 EMPLOYEE_ID ,
225 DESCRIPTION ,
226 STATUS ,
227 RESOURCE_ID ,
228 RESOURCE_TYPE )
229 VALUES
230 (l_collector_id ,
231 FND_GLOBAL.user_id ,
232 sysdate ,
233 FND_GLOBAL.login_id ,
234 sysdate ,
235 FND_GLOBAL.user_id ,
236 i.user_name ,
237 i.source_id ,
238 i.source_name ,
239 'A',
240 i.resource_id,
241 'RS_RESOURCE' ) ;
242
243 l_colcount := NVL(l_colcount,0) + 1;
244
245 Exception When Others then
246 fnd_file.put_line(FND_FILE.LOG, 'Error while Inserting into AR_COLLECTORS' || SQLERRM);
247 END;
248 BEGIN
249 SELECT 1
250 INTO l_check
251 FROM
252 jtf_rs_resource_extns jtfrs,JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtv
253 WHERE
254 jtrr.role_resource_id = jtfrs.resource_id
255 AND jtv.role_id = jtrr.role_id
256 AND jtv.ROLE_CODE = 'IEX_AGENT'
257 AND jtrr.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
258 AND jtfrs.source_id is not null
259 AND jtfrs.resource_id = i.resource_id;
260
261 If (p_debug = 'Y') then
262 fnd_file.put_line(FND_FILE.LOG,'Role found for resource id ' || i.resource_id);
263 End If;
264
265 Exception when no_data_found then
266 If (p_debug = 'Y') then
267 fnd_file.put_line(FND_FILE.LOG,'Role not found for resource id ' || i.resource_id);
268 fnd_file.put_line(FND_FILE.LOG,'Creating Role for resource id ' || i.resource_id);
269 End If;
270 OPEN C_ROLE_ID;
271 FETCH C_ROLE_ID into l_Role_id;
272 CLOSE C_ROLE_ID;
273 BEGIN
274 JTF_RS_ROLE_RELATE_PUB.create_resource_role_relate
275 (P_API_VERSION => 1.0,
276 P_INIT_MSG_LIST => 'T',
277 P_COMMIT => 'F',
278 P_ROLE_RESOURCE_TYPE => 'RS_INDIVIDUAL' ,
279 P_ROLE_RESOURCE_ID => i.RESOURCE_ID,
280 P_ROLE_ID => l_Role_ID,
281 P_ROLE_CODE => 'COLLECTIONS',
282 P_START_DATE_ACTIVE => TRUNC(SYSDATE),
283 X_RETURN_STATUS => l_return_status,
284 X_MSG_COUNT => l_msg_count,
285 X_MSG_DATA => l_msg_data,
286 X_ROLE_RELATE_ID => l_role_relate_id
287 );
288 If (p_debug = 'Y') then
289 fnd_file.put_line(FND_FILE.LOG,'After Creating role for resource_id: ' || i.resource_id || ' Responsibility: ' ||
290 p_resp || ' Return Status ' || l_return_status || ' Role Relate id ' || l_role_relate_id);
291 End If;
292 Exception when others then
293 fnd_file.put_line(FND_FILE.LOG,'Error while creating the Role for the resource_id ' || i.resource_id || SQLERRM);
294 END;
295 END;
296
297 End Loop;
298
299 COMMIT;
300
301 Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
302 fnd_file.put_line(FND_FILE.LOG,'Date and Time Finish '|| l_date_time );
303 fnd_file.put_line(FND_FILE.LOG,'No. of Collector(s) Added '|| l_colcount);
304
305
306 EXCEPTION WHEN OTHERS THEN
307 fnd_file.put_line(FND_FILE.LOG,'Error in update_colectors_pvt for Responsibility ' || P_RESP);
308 -- Begin - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
309 FND_FILE.put_line(FND_FILE.LOG, 'EXCEPTION!!!!! -> ' || SQLERRM );
310 -- End - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
311
312 END UPDATE_COLLECTORS_PVT;
313
314
315
316 PROCEDURE UPDATE_RESOURCES
317 (
318 ERRBUF OUT NOCOPY VARCHAR2,
319 RETCODE OUT NOCOPY VARCHAR2,
320 P_debug IN VARCHAR2
321 )
322 IS
323 l_return_status VARCHAR2(5);
324 l_msg_count NUMBER;
325 l_msg_data VARCHAR2(100);
326 l_resource_id NUMBER;
327 l_resource_number NUMBER;
328 l_date_time VARCHAR2(20);
329 l_Role_id NUMBER;
330 l_role_relate_id NUMBER;
331
332 /*
333
334 CURSOR INSERT_RESOURCES IS
335 SELECT employee_id,inactive_date,description,name,resource_id FROM
336 ar_collectors ac
337 WHERE employee_id is not null
338 AND employee_id not in (SELECT jtr.source_id
339 FROM JTF_RS_ROLE_RELATIONS jtrr, JTF_RS_ROLES_B jtv,
340 JTF_RS_RESOURCE_EXTNS jtr
341 WHERE jtv.ROLE_CODE = 'IEX_AGENT' AND jtv.role_id = jtrr.role_id
342 AND jtrr.role_resource_id = jtr.resource_id
343 AND jtrr.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL'
344 AND jtr.source_id is not null)
345 AND employee_id not in (SELECT jtr.source_id from
346 jtf_rs_resource_extns jtr where source_id is not null)
347 AND resource_id is not null
348 ORDER BY 1;
349
350 */
351
352 /* PROGRAM: To convert Collectors without Resource ID into Collector with Resource ID.
353 1.Get the Collector IN ar_collector without Resource ID
354 2.Create the Resource
355 3.Add the Collector Agent for Role for created Resource
356 3.Update the resource in AR_COLLECTOR. */
357
358 --Bug4929658. Fix By LKKUMAR on 12-Jan-2005. Include source phone, job title, email, addresses. Start.
359 CURSOR INSERT_RESOURCES IS
360 SELECT ac.collector_id, ac.name, ac.employee_id, ac.inactive_date, ac.description, ac.resource_id,
361 fuser.user_id, fuser.user_name, pemp.employee_number,
362 pemp.full_name, pemp.first_name, pemp.last_name ,
363 --New Columns
364 hp.primary_phone_number,
365 hp.person_title,
366 hp.address1,
367 hp.address2,
368 hp.address3,
369 hp.address4,
370 hp.city,
371 hp.postal_code,
372 hp.state,
373 hp.county,
374 hp.country,
375 hp.email_address
376 FROM
377 ar_collectors ac, FND_USER fuser, per_all_people_f pemp ,hz_parties hp
378 WHERE ac.employee_id is not null
379 AND ac.employee_id not in (SELECT jtr.source_id from
380 jtf_rs_resource_extns jtr where source_id is not null)
381 AND ac.resource_id is null
382 AND ac.employee_id = fuser.employee_id
383 and ac.employee_id = pemp.person_id
384 and pemp.party_id = hp.party_id
385 ORDER BY 1;
386 --Bug4929658. Fix By LKKUMAR on 12-Jan-2005. Include source phone, job title, email, addresses. End.
387
388
389 CURSOR c_ppf(p_person_id IN NUMBER) IS
390 SELECT employee_number,
391 full_name,
392 first_name,
393 middle_names,
394 last_name,
395 email_address,
396 business_group_id,
397 office_number,
398 internal_location,
399 mailstop
400 FROM per_all_people_f
401 WHERE person_id = p_person_id
402 ORDER BY effective_start_date DESC;
403
404 CURSOR C_ROLE_ID is
405 SELECT role_id from JTF_RS_ROLES_B WHERE ROLE_CODE = 'IEX_AGENT' and ROLE_TYPE_CODE = 'COLLECTIONS';
406
407 l_colcount NUMBER; /* Counter for no. of collectors */
408
409 CURSOR update_resource IS
410 SELECT
411 DISTINCT
412 FU.USER_NAME,
413 FU.USER_ID,
414 FU.EMPLOYEE_ID,
415 JRS.RESOURCE_ID
416 FROM
417 FND_USER_RESP_GROUPS FURG,
418 FND_RESPONSIBILITY FR ,
419 FND_USER FU,
420 JTF_RS_RESOURCE_EXTNS JRS
421 WHERE
422 FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID
423 AND JRS.SOURCE_ID = FU.EMPLOYEE_ID
424 AND FU.USER_ID = FURG.USER_ID
425 AND TRUNC(NVL(FU.START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
426 AND TRUNC(NVL(FU.END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
427 AND TRUNC(NVL(FURG.END_DATE,SYSDATE)) >= TRUNC(SYSDATE)
428 AND TRUNC(NVL(JRS.START_DATE_ACTIVE,SYSDATE)) <= TRUNC(SYSDATE)
429 AND TRUNC(NVL(JRS.END_DATE_ACTIVE,SYSDATE)) >= TRUNC(SYSDATE)
430 AND FU.EMPLOYEE_ID IS NOT NULL
431 AND JRS.USER_ID IS NULL
432 AND JRS.USER_NAME IS NULL
433 AND JRS.CATEGORY = 'EMPLOYEE'
434 AND FR.MENU_ID IN (
435 SELECT DISTINCT FCMF.MENU_ID
436 FROM
437 FND_COMPILED_MENU_FUNCTIONS FCMF, FND_FORM_FUNCTIONS FFF
438 WHERE FCMF.FUNCTION_ID = FFF.FUNCTION_ID
439 AND FFF.FUNCTION_NAME = 'IEXRCALL');
440
441
442 TYPE EMP_ID_LIST is TABLE of NUMBER INDEX BY BINARY_INTEGER;
443 TYPE USER_ID_LIST is TABLE of NUMBER INDEX BY BINARY_INTEGER;
444 TYPE USER_NAME_LIST is TABLE of VARCHAR2(240) INDEX BY BINARY_INTEGER;
445 TYPE RESOURCE_ID_LIST is TABLE of NUMBER INDEX BY BINARY_INTEGER;
446 g_bulk_fetch_rows NUMBER := 10000;
447 L_CHECK_ROLE NUMBER;
448 L_EMPLOYEE_ID EMP_ID_LIST;
449 L_USER_NAME USER_NAME_LIST;
450 L_USER_ID USER_ID_LIST;
451 L_RESOURCE_ID_LST RESOURCE_ID_LIST;
452
453
454 BEGIN
455 Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
456 fnd_file.put_line(FND_FILE.LOG,'Date and Time Start conversion '|| l_date_time );
457
458 OPEN C_ROLE_ID;
459 FETCH C_ROLE_ID INTO l_Role_id;
460 CLOSE C_ROLE_ID;
461
462 l_colcount := 0;
463
464 if l_Role_id is NULL then
465 FND_FILE.put_line(FND_FILE.LOG, 'No COLLECTOR ROLE seeded in the system ' );
466 FND_FILE.put_line(FND_FILE.LOG, ' ');
467 RETCODE := 'FALSE';
468 return;
469 end if;
470
471 OPEN update_resource;
472 LOOP
473 FETCH update_resource BULK COLLECT INTO
474 L_USER_NAME,
475 L_USER_ID,
476 L_EMPLOYEE_ID,
477 L_RESOURCE_ID_LST
478 LIMIT g_bulk_fetch_rows;
479 IF L_USER_NAME.COUNT = 0 THEN
480 EXIT;
481 END IF;
482
483 FOR i IN L_USER_ID.FIRST .. L_USER_ID.LAST
484 LOOP
485
486 BEGIN
487 --UPdate the JTF_RS_RESOURCE_EXTNS Table with the value from FND_USER.
488 UPDATE JTF_RS_RESOURCE_EXTNS
489 SET USER_ID = L_USER_ID(i),
490 USER_NAME = L_USER_NAME(i),
491 last_update_date = SYSDATE,
492 last_updated_by = -1
493 WHERE SOURCE_ID = L_EMPLOYEE_ID(I)
494 AND CATEGORY = 'EMPLOYEE'
495 -- Begin - Bug#5383877 - Andre Araujo - 07/18/2006 - With this statement there will be no updates
496 --AND L_USER_ID(I) NOT IN (SELECT NVL(USER_ID,-1) FROM JTF_RS_RESOURCE_EXTNS WHERE CATEGORY = 'EMPLOYEE');
497 and user_id is null;
498 -- End - Bug#5383877 - Andre Araujo - 07/18/2006 - With this statement there will be no updates
499 EXCEPTION WHEN OTHERS THEN
500 -- Begin - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
501 --NULL;
502 FND_FILE.put_line(FND_FILE.LOG, 'EXCEPTION!!!!! -> ' || SQLERRM );
503 -- End - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
504 END;
505
506 -- Check whether Collections Role Exists for the Resource and Create if Role does'nt exist.
507 BEGIN
508 SELECT ROLE_ID INTO L_CHECK_ROLE
509 FROM JTF_RS_ROLE_RELATIONS_VL JRR,
510 JTF_RS_RESOURCE_EXTNS JRE
511 WHERE
512 JRE.SOURCE_ID = L_EMPLOYEE_ID(I)
513 AND JRR.ROLE_RESOURCE_ID = JRE.RESOURCE_ID
514 AND JRR.ROLE_CODE = 'IEX_AGENT';
515 EXCEPTION WHEN NO_DATA_FOUND THEN
516 BEGIN
517 JTF_RS_ROLE_RELATE_PUB.create_resource_role_relate
518 (P_API_VERSION => 1.0,
519 P_INIT_MSG_LIST => 'T',
520 P_COMMIT => 'T',
521 P_ROLE_RESOURCE_TYPE => 'RS_INDIVIDUAL' ,
522 P_ROLE_RESOURCE_ID => L_RESOURCE_ID_LST(I),
523 P_ROLE_ID => l_Role_ID,
524 P_ROLE_CODE => 'COLLECTIONS',
525 P_START_DATE_ACTIVE => TRUNC(SYSDATE),
526 X_RETURN_STATUS => l_return_status,
527 X_MSG_COUNT => l_msg_count,
528 X_MSG_DATA => l_msg_data,
529 X_ROLE_RELATE_ID => l_role_relate_id
530 );
531 COMMIT;
532 EXCEPTION
533 WHEN OTHERS THEN
534 FND_FILE.put_line(FND_FILE.LOG, 'Error while creting roles' || l_msg_data );
535 -- Begin - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
536 FND_FILE.put_line(FND_FILE.LOG, SQLERRM );
537 -- End - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
538 END;
539 WHEN OTHERS THEN --NULL;
540 -- Begin - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
541 FND_FILE.put_line(FND_FILE.LOG, 'EXCEPTION!!!!! -> ' || SQLERRM );
542 -- End - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
543 END;
544 END LOOP;
545 END LOOP;
546 CLOSE update_resource;
547 COMMIT;
548
549
550
551 If (P_debug = 'Y') then
552 fnd_file.put_line(FND_FILE.LOG,'Role id ' || l_Role_ID);
553 end if;
554
555 /* Begin Kasreeni 05/17/2004 Merge Existing Collectors */
556 MERGE_COLLECTORS(P_DEBUG);
557 /* End Kasreeni 05/17/2004 Merge Existing Collectors */
558
559 FOR I in insert_resources loop
560 BEGIN
561 --Bug4929658. Fix By LKKUMAR on 12-Jan-2005. Include source phone, job title, email, addresses. Start.
562 /*
563 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
564 P_API_VERSION => 1.0,
565 P_INIT_MSG_LIST => FND_API.G_TRUE,
566 P_COMMIT => FND_API.G_TRUE,
567 P_CATEGORY => 'EMPLOYEE',
568 P_SOURCE_ID => i.employee_id,
569 P_START_DATE_ACTIVE => SYSDATE,
570 P_END_DATE_ACTIVE => i.inactive_date,
571 P_COMMISSIONABLE_FLAG => 'Y',
572 P_HOLD_PAYMENT => 'N',
573 P_USER_ID => i.user_id,
574 P_USER_NAME => i.user_name,
575 P_RESOURCE_NAME => i.full_name,
576 P_SOURCE_NUMBER => i.employee_number,
577 P_SOURCE_NAME => i.full_name,
578 P_SOURCE_FIRST_NAME => i.first_name,
579 P_SOURCE_LAST_NAME => i.last_name,
580 P_TRANSACTION_NUMBER => NULL,
581 X_RETURN_STATUS => l_return_status,
582 X_MSG_COUNT => l_msg_count,
583 X_MSG_DATA => l_msg_data,
584 X_RESOURCE_ID => l_resource_id,
585 X_RESOURCE_NUMBER => l_resource_number
586 );
587 */
588 JTF_RS_RESOURCE_PUB.CREATE_RESOURCE(
589 P_API_VERSION => 1.0,
590 P_INIT_MSG_LIST => FND_API.G_TRUE,
591 P_COMMIT => FND_API.G_TRUE,
592 P_CATEGORY => 'EMPLOYEE',
593 P_SOURCE_ID => i.employee_id,
594 P_ADDRESS_ID => NULL,
595 P_CONTACT_ID => NULL,
596 P_MANAGING_EMP_ID => NULL,
597 P_MANAGING_EMP_NUM => NULL,
598 P_START_DATE_ACTIVE => SYSDATE,
599 P_END_DATE_ACTIVE => i.inactive_date,
600 P_TIME_ZONE => NULL,
601 P_COST_PER_HR => NULL,
602 P_PRIMARY_LANGUAGE => NULL,
603 P_SECONDARY_LANGUAGE => NULL,
604 P_SUPPORT_SITE_ID => NULL,
605 P_IES_AGENT_LOGIN => NULL,
606 P_SERVER_GROUP_ID => NULL,
607 P_INTERACTION_CENTER_NAME => NULL,
608 P_ASSIGNED_TO_GROUP_ID => NULL,
609 P_COST_CENTER => NULL,
610 P_CHARGE_TO_COST_CENTER => NULL,
611 P_COMP_CURRENCY_CODE => NULL,
612 P_COMMISSIONABLE_FLAG => 'Y',
613 P_HOLD_REASON_CODE => NULL,
614 P_HOLD_PAYMENT => 'N',
615 P_COMP_SERVICE_TEAM_ID => NULL,
616 P_USER_ID => i.user_id,
617 P_TRANSACTION_NUMBER => NULL,
618 X_RETURN_STATUS => l_return_status,
619 X_MSG_COUNT => l_msg_count,
620 X_MSG_DATA => l_msg_data,
621 X_RESOURCE_ID => l_resource_id,
622 X_RESOURCE_NUMBER => l_resource_number,
623 P_RESOURCE_NAME => i.full_name,
624 P_SOURCE_NAME => i.full_name,
625 P_SOURCE_NUMBER => i.employee_number,
626 P_SOURCE_JOB_TITLE => i.person_title,
627 P_SOURCE_EMAIL => i.email_address,
628 P_SOURCE_PHONE => i.primary_phone_number,
629 P_SOURCE_ORG_ID => NULL,
630 P_SOURCE_ORG_NAME => NULL,
631 P_SOURCE_ADDRESS1 => i.address1,
632 P_SOURCE_ADDRESS2 => i.address2,
633 P_SOURCE_ADDRESS3 => i.address3,
634 P_SOURCE_ADDRESS4 => i.address4,
635 P_SOURCE_CITY => i.city,
636 P_SOURCE_POSTAL_CODE => i.postal_code,
637 P_SOURCE_STATE => i.state,
638 P_SOURCE_PROVINCE => NULL,
639 P_SOURCE_COUNTY => i.county,
640 P_SOURCE_COUNTRY => i.country,
641 P_SOURCE_MGR_ID => NULL,
642 P_SOURCE_MGR_NAME => NULL,
643 P_SOURCE_BUSINESS_GRP_ID => NULL,
644 P_SOURCE_BUSINESS_GRP_NAME => NULL,
645 P_SOURCE_FIRST_NAME => i.first_name,
646 P_SOURCE_LAST_NAME => i.last_name,
647 P_SOURCE_MIDDLE_NAME => NULL,
648 P_SOURCE_CATEGORY => NULL,
649 P_SOURCE_STATUS => NULL,
650 P_SOURCE_OFFICE => NULL,
651 P_SOURCE_LOCATION => NULL,
652 P_SOURCE_MAILSTOP => NULL,
653 P_USER_NAME => i.user_name,
654 P_SOURCE_MOBILE_PHONE => NULL,
655 P_SOURCE_PAGER => NULL,
656 P_ATTRIBUTE1 => NULL,
657 P_ATTRIBUTE2 => NULL,
658 P_ATTRIBUTE3 => NULL,
659 P_ATTRIBUTE4 => NULL,
660 P_ATTRIBUTE5 => NULL,
661 P_ATTRIBUTE6 => NULL,
662 P_ATTRIBUTE7 => NULL,
663 P_ATTRIBUTE8 => NULL,
664 P_ATTRIBUTE9 => NULL,
665 P_ATTRIBUTE10 => NULL,
666 P_ATTRIBUTE11 => NULL,
667 P_ATTRIBUTE12 => NULL,
668 P_ATTRIBUTE13 => NULL,
669 P_ATTRIBUTE14 => NULL,
670 P_ATTRIBUTE15 => NULL,
671 P_ATTRIBUTE_CATEGORY => NULL
672 );
673 --Bug4929658. Fix By LKKUMAR on 12-Jan-2005. Include source phone, job title, email, addresses. End.
674
675 IF (l_return_status <> 'S') then
676 fnd_file.put_line(FND_FILE.LOG,'Error while creating resource ' || i.name || ' Status ' || l_return_status);
677
678 ELSE
679 l_colcount := l_colcount + 1;
680 If (P_debug = 'Y') then
681 fnd_file.put_line(FND_FILE.LOG,'Successfully completed for employee id ' || i.employee_id ||
682 'Status ' || l_return_status || ' ' || 'Resource id '
683 || l_resource_id || ' Resource Number ' || l_resource_number);
684 END IF;
685
686 BEGIN
687 UPDATE AR_COLLECTORS
688 SET resource_id = l_resource_id
689 WHERE collector_id = i.collector_id;
690
691 If (p_debug = 'Y') then
692 fnd_file.put_line(FND_FILE.LOG,'After updating for AR_COLLECTORS with resource_id ' || i.employee_id || ' ' || i.resource_id);
693 End If;
694
695 EXCEPTION WHEN OTHERS THEN
696 fnd_file.put_line(FND_FILE.LOG,'Error occured while updating AR_COLLECTORS '
697 || ' with employee id ' || i.employee_id );
698 FND_FILE.put_line(FND_FILE.log, ' ');
699 -- Begin - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
700 FND_FILE.put_line(FND_FILE.LOG, 'EXCEPTION!!!!! -> ' || SQLERRM );
701 -- End - Bug#5383877 - Andre Araujo - 07/18/2006 - Since I am here correcting the exception
702
703 END;
704
705 JTF_RS_ROLE_RELATE_PUB.create_resource_role_relate
706 (P_API_VERSION => 1.0,
707 P_INIT_MSG_LIST => 'T',
708 P_COMMIT => 'F',
709 P_ROLE_RESOURCE_TYPE => 'RS_INDIVIDUAL' ,
710 P_ROLE_RESOURCE_ID => L_RESOURCE_ID,
711 P_ROLE_ID => l_Role_ID,
712 P_ROLE_CODE => 'COLLECTIONS',
713 P_START_DATE_ACTIVE => TRUNC(SYSDATE),
714 X_RETURN_STATUS => l_return_status,
715 X_MSG_COUNT => l_msg_count,
716 X_MSG_DATA => l_msg_data,
717 X_ROLE_RELATE_ID => l_role_relate_id
718 );
719 If (P_debug = 'Y') then
720 fnd_file.put_line(FND_FILE.LOG,' Role API returns = ' || l_return_status ||
721 ' ' || l_role_relate_id);
722 end if;
723 end if;
724
725
726 EXCEPTION WHEN OTHERS THEN
727 fnd_file.put_line(FND_FILE.LOG,'Error while creating resource ' || i.name || ' ' || SQLERRM);
728 FND_FILE.put_line(FND_FILE.log, ' ');
729 END;
730
731
732 l_resource_id := NULL;
733 l_resource_number := NULL;
734 END LOOP;
735 Select to_char(sysdate,'DD-MON-YYYY HH24:MM:SS') into l_date_time from dual;
736 fnd_file.put_line(FND_FILE.LOG,'Date and Time Finish '|| l_date_time );
737 FND_FILE.put_line(FND_FILE.LOG,'No. of. Resources Added := ' || l_colcount);
738
739 COMMIT;
740
741 END;
742
743 End IEX_COLLECTORS_TO_RESOURCE;