[Home] [Help]
PACKAGE BODY: APPS.PVX_MISC_PVT
Source
1 PACKAGE BODY PVX_Misc_PVT AS
2 /* $Header: pvxvmisb.pls 120.4 2008/01/17 00:52:46 hekkiral ship $ */
3
4
5
6 g_pkg_name CONSTANT VARCHAR2(30):='PVX_Misc_PVT';
7
8
9
10 ---------------------------------------------------------------------
11 -- PROCEDURE
12 -- Admin_Access
13 --
14 -- HISTORY
15 -- 08/24/2000 Shitij Vatsa Create.
16 -- 03/12/2001 Shitij Vatsa Update.
17 -- In Admin_Access API changed the
18 -- logic for the default sales group.
19 -- It is now passed to the API from
20 -- the UI.
21 -- Search String : "BUG:1668567"
22 -- 03/27/2001 Shitij Vatsa Update
23 -- In Admin_Access API further changed
24 -- the logic for CM and phone support rep
25 -- Sales_Group_ID. It is not defaulted
26 -- from the UI now.
27 -- Created a new cursor c_sales_group
28 -- Search String : "BUG:1706709"
29 ---------------------------------------------------------------------
30 PROCEDURE Admin_Access(
31 p_api_version IN NUMBER
32 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
33 ,p_commit IN VARCHAR2 := FND_API.g_false
34 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
35
36 ,x_return_status OUT NOCOPY VARCHAR2
37 ,x_msg_count OUT NOCOPY NUMBER
38 ,x_msg_data OUT NOCOPY VARCHAR2
39
40 ,p_admin_rec IN admin_rec_type
41 ,p_mode IN VARCHAR2
42 ,x_access_id OUT NOCOPY NUMBER
43 )
44 IS
45
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_api_name CONSTANT VARCHAR2(30) := 'Admin_Access';
48 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
49 l_source_name VARCHAR2(25);
50
51 l_return_status VARCHAR2(1);
52 -- Create record variables
53 l_admin_rec admin_rec_type := p_admin_rec;
54 l_sales_team_rec AS_ACCESS_PUB.sales_team_rec_type;
55
56 l_object_version_number NUMBER := 1;
57
58 -- For reference
59 -- FND_API. return value constants
60 -- G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := 'S';
61 -- G_RET_STS_ERROR CONSTANT VARCHAR2(1) := 'E';
62 -- G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := 'U';
63
64 -- Cursor : c_resource_detail
65 CURSOR c_resource_detail (cv_resource_id IN NUMBER) IS
66 SELECT source_id,user_id, substr(source_name,1,20)
67 FROM JTF_RS_RESOURCE_EXTNS
68 WHERE resource_id = cv_resource_id;
69 -- Cursor related variables
70 l_source_id NUMBER;
71 l_user_id NUMBER;
72
73 /*
74 -- Cursor : c_partner_detail
75 CURSOR c_partner_detail (cv_partner_profile_id IN NUMBER) IS
76 SELECT partner_party_id
77 FROM PV_PARTNERS_V
78 WHERE partner_profile_id = cv_partner_profile_id;
79 */
80 -- Cursor : c_partner_detail
81 CURSOR c_partner_detail (cv_partner_profile_id IN NUMBER) IS
82 SELECT partner_party_id
83 FROM PV_PARTNER_PROFILES
84 WHERE partner_profile_id = cv_partner_profile_id;
85
86
87 -- Cursor records
88 l_partner_party_id NUMBER;
89
90 -- Cursor : c_party_site
91 CURSOR c_party_site (cv_party_id IN NUMBER) IS
92 SELECT party_site_id
93 FROM HZ_PARTY_SITES
94 WHERE party_id = cv_party_id
95 AND identifying_address_flag = 'Y'
96 AND NVL(start_date_active,SYSDATE) <= SYSDATE
97 AND NVL(end_date_active,SYSDATE) >= SYSDATE;
98 -- Cursor records
99 l_party_site_id NUMBER;
100
101 -- "BUG:1706709"
102 -- Cursor : c_sales_group
103 CURSOR c_sales_group (cv_salesforce_id IN NUMBER) IS
104 SELECT GRPREL.group_id
105 FROM JTF_RS_RESOURCE_EXTNS RES ,JTF_RS_ROLE_RELATIONS RREL ,JTF_RS_ROLES_VL ROLE
106 ,JTF_RS_GROUPS_TL GROUPS ,JTF_RS_GROUP_USAGES U ,JTF_RS_GROUP_MEMBERS GRPREL
107 WHERE RES.category = 'EMPLOYEE' AND ROLE.role_type_code in ('SALES','TELESALES','FIELDSALES','PRM')
108 AND RREL.role_id = ROLE.role_id AND GROUPS.language = userenv('LANG') AND GRPREL.group_id = GROUPS.group_id
109 AND NVL(RREL.delete_flag,'N') = 'N' AND RREL.start_date_active <= sysdate
110 AND NVL(RREL.end_date_active,sysdate) >= sysdate AND U.group_id = GRPREL.group_id
111 AND U.usage = 'SALES' AND (ROLE.member_flag = 'Y' or ROLE.manager_flag='Y')
112 AND RREL.role_resource_type = 'RS_GROUP_MEMBER' AND GRPREL.group_member_id = RREL.role_resource_id
113 AND NVL(GRPREL.delete_flag,'N') = 'N' AND RES.resource_id = GRPREL.resource_id
114 AND RES.resource_id = cv_salesforce_id;
115 -- Cursor records
116 l_group_id NUMBER;
117
118 BEGIN
119
120 --------------------- initialize -----------------------
121 SAVEPOINT Admin_Access;
122
123 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
124 PVX_Utility_PVT.debug_message(l_full_name||': start');
125 END IF;
126
127 IF FND_API.to_boolean(p_init_msg_list) THEN
128 FND_MSG_PUB.initialize;
129 END IF;
130
131 IF NOT FND_API.compatible_api_call(
132 l_api_version,
133 p_api_version,
134 l_api_name,
135 g_pkg_name
136 ) THEN
137 RAISE FND_API.g_exc_unexpected_error;
138 END IF;
139
140 x_return_status := FND_API.g_ret_sts_success;
141
142
143 -------------------------- create --------------------------
144 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
145 PVX_Utility_PVT.debug_message(l_full_name ||': create');
146 END IF;
147
148
149
150 -- If partner_profile_id IS NULL, raise an error
151 IF l_admin_rec.partner_profile_id IS NULL THEN
152 FND_MESSAGE.set_name('PV', 'PV_MISC_NO_PROFILE_ID');
153 --FND_MESSAGE.set_token('ID',to_char(p_prtnr_prfls_rec.partner_id) );
154 FND_MSG_PUB.add;
155 x_return_status := FND_API.g_ret_sts_error;
156 RAISE FND_API.g_exc_error;
157 END IF;
158
159
160 -- Get the Partner Details
161 IF l_admin_rec.cm_id IS NOT NULL OR
162 l_admin_rec.ph_support_rep IS NOT NULL OR
163 l_admin_rec.cmm_id IS NOT NULL OR
164 l_admin_rec.logged_resource_id IS NOT NULL THEN
165 -- Get the partner details
166 OPEN c_partner_detail(l_admin_rec.partner_profile_id);
167 FETCH c_partner_detail INTO l_partner_party_id;
168 CLOSE c_partner_detail;
169 END IF;
170
171 -- Get the party_site_id of the partner party
172 OPEN c_party_site(l_partner_party_id);
173 FETCH c_party_site INTO l_party_site_id;
174 CLOSE c_party_site;
175
176
177 -- Add Logged Resource to the Sales Team Access List
178 IF l_admin_rec.logged_resource_id IS NOT NULL THEN
179
180 -- Get the resource details
181 OPEN c_resource_detail(l_admin_rec.logged_resource_id);
182 FETCH c_resource_detail INTO l_source_id, l_user_id, l_source_name;
183 CLOSE c_resource_detail;
184
185
186 -- Populate the record
187 l_sales_team_rec.salesforce_id := l_admin_rec.logged_resource_id;
188 l_sales_team_rec.person_id := l_source_id;
189 l_sales_team_rec.customer_id := l_partner_party_id;
190 l_sales_team_rec.address_id := l_party_site_id;
191 -- get the default group for the user
192 --l_sales_team_rec.sales_group_id := fnd_profile.value_specific('PV_DEFAULT_GROUP', l_user_id);
193 -- "BUG:1668567"
194 -- Update by svatsa on 03/09/2001
195 -- This is holds good only for the logged resource and NOT for
196 -- Channel manager or the Phone Support Rep
197 l_sales_team_rec.sales_group_id := l_admin_rec.group_id;
198
199 --DBMS_OUTPUT.PUT_LINE('***** Logged Resource *****');
200 --DBMS_OUTPUT.PUT_LINE('salesforce_id = '||to_char(l_sales_team_rec.salesforce_id));
201 --DBMS_OUTPUT.PUT_LINE('person_id = '||to_char(l_sales_team_rec.person_id));
202 --DBMS_OUTPUT.PUT_LINE('customer_id = '||to_char(l_sales_team_rec.customer_id));
203 --DBMS_OUTPUT.PUT_LINE('address_id = '||to_char(l_sales_team_rec.address_id));
204 --DBMS_OUTPUT.PUT_LINE('sales_group_id = '||to_char(l_sales_team_rec.sales_group_id));
205
206
207 AS_ACCESS_PUB.Create_SalesTeam (
208 p_api_version_number => 2.0
209 ,p_init_msg_list => FND_API.g_true
210 -- ,p_commit => FND_API.g_true
211 ,p_validation_level => FND_API.g_valid_level_full
212
213 ,p_access_profile_rec => NULL
214 ,p_check_access_flag => 'N'
215 ,p_admin_flag => 'N'
216 ,p_admin_group_id => NULL
217 ,p_identity_salesforce_id => NULL
218 ,p_sales_team_rec => l_sales_team_rec
219
220 ,x_return_status => x_return_status
221 ,x_msg_count => x_msg_count
222 ,x_msg_data => x_msg_data
223 ,x_access_id => x_access_id
224 );
225
226 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
227 FND_MESSAGE.set_name('PV', 'PV_MISC_ERROR_LOGD_RES_ID');
228 FND_MESSAGE.set_token('ID',to_char(l_admin_rec.logged_resource_id) );
229 FND_MSG_PUB.add;
230 END IF;
231
232
233 IF x_return_status = FND_API.g_ret_sts_error THEN
234 RAISE FND_API.g_exc_error;
235 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
236 RAISE FND_API.g_exc_unexpected_error;
237 END IF;
238
239 END IF; -- IF logged_resource_id IS NOT NULL
240
241
242
243 -- Add Channel Manager to the Sales Team Access List
244 IF l_admin_rec.cm_id IS NOT NULL THEN
245
246 -- Get the resource details
247 OPEN c_resource_detail(l_admin_rec.cm_id);
248 FETCH c_resource_detail INTO l_source_id, l_user_id, l_source_name;
249 CLOSE c_resource_detail;
250
251 -- bug no 1651872 Chandra Sekhar.C
252
253 -- If user_id IS NULL, raise an error
254 IF l_user_id IS NULL THEN
255 FND_MESSAGE.set_name('PV', 'PV_NOT_VALID_USER');
256 FND_MESSAGE.set_token('ENTITY',l_source_name );
257 FND_MSG_PUB.add;
258 x_return_status := FND_API.g_ret_sts_error;
259 RAISE FND_API.g_exc_error;
260 END IF;
261
262
263 -- end bug.
264
265
266
267 -- Populate the record
268 l_sales_team_rec.salesforce_id := l_admin_rec.cm_id;
269 l_sales_team_rec.person_id := l_source_id;
270 l_sales_team_rec.customer_id := l_partner_party_id;
271 l_sales_team_rec.address_id := l_party_site_id;
272 -- get the default group for the user
273 --l_sales_team_rec.sales_group_id := fnd_profile.value_specific('PV_DEFAULT_GROUP', l_user_id);
274 -- Update by svatsa on 03/09/2001
275 -- l_sales_team_rec.sales_group_id := l_admin_rec.group_id;
276 -- "BUG:1706709"; Since the above statement is not true for CM as it is selected from the LOV which
277 -- already has a salesgroup_id, open the cursor to pass the sales_group_id
278 -- corresponding to the resource_id of the CM.
279 -- Updated on 03/27/2001
280
281 -- Get the sales_group_id for the Channel Manager
282 OPEN c_sales_group(l_admin_rec.cm_id);
283 FETCH c_sales_group INTO l_group_id;
284 CLOSE c_sales_group;
285
286 l_sales_team_rec.sales_group_id := l_group_id;
287
288 --DBMS_OUTPUT.PUT_LINE('***** Channel Maneger *****');
289 --DBMS_OUTPUT.PUT_LINE('salesforce_id = '||to_char(l_sales_team_rec.salesforce_id));
290 --DBMS_OUTPUT.PUT_LINE('person_id = '||to_char(l_sales_team_rec.person_id));
291 --DBMS_OUTPUT.PUT_LINE('customer_id = '||to_char(l_sales_team_rec.customer_id));
292 --DBMS_OUTPUT.PUT_LINE('address_id = '||to_char(l_sales_team_rec.address_id));
293 --DBMS_OUTPUT.PUT_LINE('sales_group_id = '||to_char(l_sales_team_rec.sales_group_id));
294
295
296 AS_ACCESS_PUB.Create_SalesTeam (
297 p_api_version_number => 2.0
298 ,p_init_msg_list => FND_API.g_true
299 -- ,p_commit => FND_API.g_true
300 ,p_validation_level => FND_API.g_valid_level_full
301
302 ,p_access_profile_rec => NULL
303 ,p_check_access_flag => 'N'
304 ,p_admin_flag => 'N'
305 ,p_admin_group_id => NULL
306 ,p_identity_salesforce_id => NULL
307 ,p_sales_team_rec => l_sales_team_rec
308
309 ,x_return_status => x_return_status
310 ,x_msg_count => x_msg_count
311 ,x_msg_data => x_msg_data
312 ,x_access_id => x_access_id
313 );
314
315 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
316 FND_MESSAGE.set_name('PV', 'PV_MISC_ERROR_CM_ID');
317 FND_MESSAGE.set_token('ID',to_char(l_admin_rec.cm_id) );
318 FND_MSG_PUB.add;
319 END IF;
320
321
322 IF x_return_status = FND_API.g_ret_sts_error THEN
323 RAISE FND_API.g_exc_error;
324 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
325 RAISE FND_API.g_exc_unexpected_error;
326 END IF;
327
328 END IF; -- IF cm_id IS NOT NULL
329
330 -- Add Phone Support Representative to the Sales Team Access List
331 IF l_admin_rec.ph_support_rep IS NOT NULL THEN
332
333 -- Get the resource details
334 OPEN c_resource_detail(l_admin_rec.ph_support_rep);
335 FETCH c_resource_detail INTO l_source_id, l_user_id, l_source_name;
336 CLOSE c_resource_detail;
337
338 -- bug no. 1651872 Chandra Sekhar.C
339
340 -- If user_id IS NULL, raise an error
341 IF l_user_id IS NULL THEN
342 FND_MESSAGE.set_name('PV', 'PV_NOT_VALID_USER');
343 FND_MESSAGE.set_token('ENTITY',l_source_name );
344 FND_MSG_PUB.add;
345 x_return_status := FND_API.g_ret_sts_error;
346 RAISE FND_API.g_exc_error;
347 END IF;
348
349
350 -- end bug.
351
352 -- Populate the record
353 l_sales_team_rec.salesforce_id := l_admin_rec.ph_support_rep;
354 l_sales_team_rec.person_id := l_source_id;
355 l_sales_team_rec.customer_id := l_partner_party_id;
356 l_sales_team_rec.address_id := l_party_site_id;
357 -- get the default group for the user
358 --l_sales_team_rec.sales_group_id := fnd_profile.value_specific('PV_DEFAULT_GROUP', l_user_id);
359 -- Update by svatsa on 03/09/2001
360 -- l_sales_team_rec.sales_group_id := l_admin_rec.group_id;
361 -- "BUG:1706709"; Since the above statement is not true for Phone Support rep as it is selected from the LOV which
362 -- already has a salesgroup_id, open the cursor to pass the sales_group_id
363 -- corresponding to the resource_id of the Phone Support Rep.
364 -- Updated on 03/27/2001
365
366 -- Get the sales_group_id for the Phone Support Rep
367 OPEN c_sales_group(l_admin_rec.ph_support_rep);
368 FETCH c_sales_group INTO l_group_id;
369 CLOSE c_sales_group;
370
371 l_sales_team_rec.sales_group_id := l_group_id;
372
373 --DBMS_OUTPUT.PUT_LINE('***** Sales Rep *****');
374 --DBMS_OUTPUT.PUT_LINE('salesforce_id = '||to_char(l_sales_team_rec.salesforce_id));
375 --DBMS_OUTPUT.PUT_LINE('person_id = '||to_char(l_sales_team_rec.person_id));
376 --DBMS_OUTPUT.PUT_LINE('customer_id = '||to_char(l_sales_team_rec.customer_id));
377 --DBMS_OUTPUT.PUT_LINE('address_id = '||to_char(l_sales_team_rec.address_id));
378 --DBMS_OUTPUT.PUT_LINE('sales_group_id = '||to_char(l_sales_team_rec.sales_group_id));
379
380
381 AS_ACCESS_PUB.Create_SalesTeam (
382 p_api_version_number => 2.0
383 ,p_init_msg_list => FND_API.g_true
384 -- ,p_commit => FND_API.g_true
385 ,p_validation_level => FND_API.g_valid_level_full
386
387 ,p_access_profile_rec => NULL
388 ,p_check_access_flag => 'N'
389 ,p_admin_flag => 'N'
390 ,p_admin_group_id => NULL
391 ,p_identity_salesforce_id => NULL
392 ,p_sales_team_rec => l_sales_team_rec
393
394 ,x_return_status => x_return_status
395 ,x_msg_count => x_msg_count
396 ,x_msg_data => x_msg_data
397 ,x_access_id => x_access_id
398 );
399
400 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
401 FND_MESSAGE.set_name('PV', 'PV_MISC_ERROR_REP_ID');
402 FND_MESSAGE.set_token('ID',to_char(l_admin_rec.ph_support_rep) );
403 FND_MSG_PUB.add;
404 END IF;
405
406 IF x_return_status = FND_API.g_ret_sts_error THEN
407 RAISE FND_API.g_exc_error;
408 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
409 RAISE FND_API.g_exc_unexpected_error;
410 END IF;
411
412 END IF; -- IF l_admin_rec.ph_support_rep IS NOT NULL
413
414 -- Begin Enh# 2188684 : by Achal.Anjaria
415 -- Add Channel Marketing Manager to the Sales Team Access List
416
417 IF l_admin_rec.cmm_id IS NOT NULL THEN
418
419 -- Get the resource details
420 OPEN c_resource_detail(l_admin_rec.cmm_id);
421 FETCH c_resource_detail INTO l_source_id, l_user_id, l_source_name;
422 CLOSE c_resource_detail;
423
424 -- If user_id IS NULL, raise an error
425 IF l_user_id IS NULL THEN
426 FND_MESSAGE.set_name('PV', 'PV_NOT_VALID_USER');
427 FND_MESSAGE.set_token('ENTITY',l_source_name );
428 FND_MSG_PUB.add;
429 x_return_status := FND_API.g_ret_sts_error;
430 RAISE FND_API.g_exc_error;
431 END IF;
432
433 -- Populate the record
434 l_sales_team_rec.salesforce_id := l_admin_rec.cmm_id;
435 l_sales_team_rec.person_id := l_source_id;
436 l_sales_team_rec.customer_id := l_partner_party_id;
437 l_sales_team_rec.address_id := l_party_site_id;
438 -- get the default group for the user
439 -- Get the sales_group_id for the Channel Marketing Manager
440
441 OPEN c_sales_group(l_admin_rec.cmm_id);
442 FETCH c_sales_group INTO l_group_id;
443 CLOSE c_sales_group;
444
445 l_sales_team_rec.sales_group_id := l_group_id;
446
447 AS_ACCESS_PUB.Create_SalesTeam (
448 p_api_version_number => 2.0
449 ,p_init_msg_list => FND_API.g_true
450 -- ,p_commit => FND_API.g_true
451 ,p_validation_level => FND_API.g_valid_level_full
452
453 ,p_access_profile_rec => NULL
454 ,p_check_access_flag => 'N'
455 ,p_admin_flag => 'N'
456 ,p_admin_group_id => NULL
457 ,p_identity_salesforce_id => NULL
458 ,p_sales_team_rec => l_sales_team_rec
459
460 ,x_return_status => x_return_status
461 ,x_msg_count => x_msg_count
462 ,x_msg_data => x_msg_data
463 ,x_access_id => x_access_id
464 );
465
466 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
467 FND_MESSAGE.set_name('PV', 'PV_MISC_ERROR_CMM_ID');
468 FND_MESSAGE.set_token('ID',to_char(l_admin_rec.cmm_id) );
469 FND_MSG_PUB.add;
470 END IF;
471
472
473 IF x_return_status = FND_API.g_ret_sts_error THEN
474 RAISE FND_API.g_exc_error;
475 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
476 RAISE FND_API.g_exc_unexpected_error;
477 END IF;
478
479 END IF; -- IF cmm_id IS NOT NULL
480
481 -- End Enh# 2188684
482 ------------------------- finish -------------------------------
483
484
485 -- Check for commit
486 IF FND_API.to_boolean(p_commit) THEN
487 COMMIT;
488 END IF;
489
490 FND_MSG_PUB.count_and_get(
491 p_encoded => FND_API.g_false,
492 p_count => x_msg_count,
493 p_data => x_msg_data
494 );
495
496 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
497 PVX_Utility_PVT.debug_message(l_full_name ||': end');
498 END IF;
499
500
501 EXCEPTION
502
503 WHEN FND_API.g_exc_error THEN
504 ROLLBACK TO Admin_Access;
505 x_return_status := FND_API.g_ret_sts_error;
506 FND_MSG_PUB.count_and_get (
507 p_encoded => FND_API.g_false
508 ,p_count => x_msg_count
509 ,p_data => x_msg_data
510 );
511
512 WHEN FND_API.g_exc_unexpected_error THEN
513 ROLLBACK TO Admin_Access;
514 x_return_status := FND_API.g_ret_sts_unexp_error ;
515 FND_MSG_PUB.count_and_get (
516 p_encoded => FND_API.g_false
517 ,p_count => x_msg_count
518 ,p_data => x_msg_data
519 );
520
521
522 WHEN OTHERS THEN
523 ROLLBACK TO Admin_Access;
524 x_return_status := FND_API.g_ret_sts_unexp_error ;
525
526 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
527 THEN
528 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
529 END IF;
530
531 FND_MSG_PUB.count_and_get(
532 p_encoded => FND_API.g_false
533 ,p_count => x_msg_count
534 ,p_data => x_msg_data
535 );
536
537 END Admin_Access;
538
539
540 ---------------------------------------------------------------------
541 -- PROCEDURE
542 -- Complete_Admin_Rec
543 --
544 -- HISTORY
545 -- 24/12/2002 svnathan Create.
546 ---------------------------------------------------------------------
547
548
549 PROCEDURE Complete_Admin_Rec (
550 p_admin_rec IN admin_rec_type
551 ,x_complete_rec OUT NOCOPY admin_rec_type
552 )
553 IS
554 l_return_status VARCHAR2(1);
555
556 CURSOR c_complete IS
557 SELECT jrre.resource_number,jrret.resource_name,jrre.source_name,jrre.source_org_id,
558 jrre.source_first_name,jrre.source_last_name,jrre.source_middle_name
559 FROM jtf_rs_resource_extns jrre ,jtf_rs_resource_extns_tl jrret
560 WHERE jrre.resource_id = p_admin_rec.role_resource_id
561 AND jrre.resource_id = jrret.resource_id ;
562
563 l_admin_rec c_complete%ROWTYPE;
564 BEGIN
565 x_complete_rec := p_admin_rec;
566
567
568 OPEN c_complete;
569 FETCH c_complete INTO l_admin_rec;
570 CLOSE c_complete;
571
572 -- resource number
573 IF p_admin_rec.resource_number = FND_API.g_miss_num THEN
574 x_complete_rec.resource_number := l_admin_rec.resource_number;
575 END IF;
576
577 -- resource name
578 IF p_admin_rec.resource_name = FND_API.g_miss_char THEN
579 x_complete_rec.resource_name := l_admin_rec.source_name;
580 END IF;
581
582 -- source name
583 IF p_admin_rec.source_name = FND_API.g_miss_char THEN
584 x_complete_rec.source_name := l_admin_rec.source_name;
585 END IF;
586
587 -- source org id
588 IF p_admin_rec.source_org_id = FND_API.g_miss_num THEN
589 x_complete_rec.source_org_id := l_admin_rec.source_org_id;
590 END IF;
591
592 -- first name
593 IF p_admin_rec.source_first_name = FND_API.g_miss_char THEN
594 x_complete_rec.source_first_name := l_admin_rec.source_first_name;
595 END IF;
596
597 -- last name
598 IF p_admin_rec.source_last_name = FND_API.g_miss_char THEN
599 x_complete_rec.source_last_name := l_admin_rec.source_last_name;
600 END IF;
601
602
603 -- middle name
604 IF p_admin_rec.source_middle_name = FND_API.g_miss_char THEN
605 x_complete_rec.source_middle_name := l_admin_rec.source_middle_name;
606 END IF;
607
608 -- Note: Developers need to modify the procedure
609 -- to handle any business specific requirements.
610 END Complete_Admin_Rec;
611
612
613
614
615
616
617
618
619
620
621
622 ---------------------------------------------------------------------
623 -- PROCEDURE
624 -- Admin_Resource
625 --
626 -- HISTORY
627 -- 09/07/2000 Shitij Vatsa Create.
628 -- 10/05/2001 shitij.vatsa Updated:
629 -- 1. Removed CREATE_RESOURCE_GROUP_MEMBER
630 -- because Admin_Group_Member does
631 -- the same function.
632 -- 2. Modified the record type admin_rec_type
633 -- with two new values source_name and resource_name
634 -- Which are now required for resource creation in
635 -- Admin_Resource.
636 -- 04-SEP-2001 shitij.vatsa Updated:
637 -- 1. Modified the record type admin_rec_type
638 -- with : user_name, source_first_name,
639 -- source_middle_name and source_last_name.
640 -- 2. Get the address of the source_id and populate the
641 -- address fields.
642 ---------------------------------------------------------------------
643 PROCEDURE Admin_Resource(
644 p_api_version IN NUMBER
645 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
646 ,p_commit IN VARCHAR2 := FND_API.g_false
647
648 ,x_return_status OUT NOCOPY VARCHAR2
649 ,x_msg_count OUT NOCOPY NUMBER
650 ,x_msg_data OUT NOCOPY VARCHAR2
651
652 ,p_admin_rec IN admin_rec_type
653 ,p_mode IN VARCHAR2
654 ,x_resource_id OUT NOCOPY NUMBER
655 ,x_resource_number OUT NOCOPY VARCHAR2
656 )
657 IS
658
659 l_api_version CONSTANT NUMBER := 1.0;
660 l_api_name CONSTANT VARCHAR2(30) := 'Admin_Resource';
661 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
662
663 l_return_status VARCHAR2(1);
664 -- Create record variables
665 l_admin_rec admin_rec_type := p_admin_rec;
666 l_admin_complete_rec admin_rec_type ;
667 l_resource_id NUMBER;
668 l_resource_number VARCHAR2(30);
669 l_version_no NUMBER;
670
671 -- Cursor : cur_resource_address
672 CURSOR cur_resource_address (curvar_party_site_id IN NUMBER) IS
673 SELECT
674 HZL.address1
675 ,HZL.address2
676 ,HZL.address3
677 ,HZL.address4
678 ,HZL.city
679 ,HZL.postal_code
680 ,HZL.state
681 ,HZL.province
682 ,HZL.county
683 ,HZL.country
684 FROM hz_party_sites HZPS
685 ,hz_locations HZL
686 WHERE
687 HZPS.location_id = HZL.location_id(+)
688 AND HZPS.identifying_address_flag(+) = 'Y'
689 AND HZPS.party_site_id = curvar_party_site_id;
690
691 -- Cursor Record Type
692 currec_resource_address cur_resource_address%ROWTYPE;
693
694 BEGIN
695
696 --------------------- initialize -----------------------
697 SAVEPOINT Admin_Resource;
698
699 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
700 PVX_Utility_PVT.debug_message(l_full_name||': start');
701 END IF;
702
703 IF FND_API.to_boolean(p_init_msg_list) THEN
704 FND_MSG_PUB.initialize;
705 END IF;
706
707 IF NOT FND_API.compatible_api_call(
708 l_api_version,
709 p_api_version,
710 l_api_name,
711 g_pkg_name
712 ) THEN
713 RAISE FND_API.g_exc_unexpected_error;
714 END IF;
715
716 x_return_status := FND_API.g_ret_sts_success;
717
718
719 -------------------------- create --------------------------
720 /*
721 -- If the source_name is null then error out
722 IF l_admin_rec.source_name IS NULL THEN
723 FND_MESSAGE.set_name('PV', 'PV_MISC_NO_SOURCE_NAME');
724 FND_MESSAGE.set_token('ID',to_char(l_admin_rec.partner_id) );
725 FND_MSG_PUB.add;
726 RAISE FND_API.g_exc_error;
727 END IF;
728 */
729
730 -- Get the address for the resource
731 OPEN cur_resource_address(l_admin_rec.party_site_id);
732 FETCH cur_resource_address INTO currec_resource_address;
733
734 --DBMS_OUTPUT.PUT_LINE('currec_resource_address.address1 = '||currec_resource_address.address1);
735 --DBMS_OUTPUT.PUT_LINE('currec_resource_address.city = '||currec_resource_address.city);
736 --DBMS_OUTPUT.PUT_LINE('currec_resource_address.postal_code = '||currec_resource_address.postal_code);
737 --DBMS_OUTPUT.PUT_LINE('currec_resource_address.state = '||currec_resource_address.state);
738 --DBMS_OUTPUT.PUT_LINE('currec_resource_address.country = '||currec_resource_address.country);
739
740
741
742 -- Call the resource API to create the resource
743 if (p_mode <> 'UPDATE') then
744
745 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
746 PVX_Utility_PVT.debug_message(l_full_name ||': create');
747 END IF;
748
749 JTF_RS_RESOURCE_PUB.Create_Resource(
750 p_api_version => 1.0
751 ,p_init_msg_list => FND_API.g_false
752 -- ,p_commit => FND_API.g_true
753 ,x_return_status => x_return_status
754 ,x_msg_count => x_msg_count
755 ,x_msg_data => x_msg_data
756
757 ,p_category => l_admin_rec.resource_type
758 ,p_source_id => l_admin_rec.partner_id -- source_id
759 ,p_user_id => l_admin_rec.user_id -- user_id
760 ,p_source_name => l_admin_rec.source_name -- source_name
761 ,p_resource_name => l_admin_rec.resource_name -- resource_name
762 ,p_source_org_name => l_admin_rec.source_org_name -- source_org_name
763 ,p_source_org_id => l_admin_rec.source_org_id -- source_org_id
764 ,p_contact_id => l_admin_rec.contact_id
765 ,p_start_date_active => SYSDATE
766 -- ,p_address_id => l_admin_rec.party_site_id
767 ,p_source_address1 => currec_resource_address.address1
768 ,p_source_address2 => currec_resource_address.address2
769 ,p_source_address3 => currec_resource_address.address3
770 ,p_source_address4 => currec_resource_address.address4
771 ,p_source_city => currec_resource_address.city
772 ,p_source_postal_code => currec_resource_address.postal_code
773 ,p_source_state => currec_resource_address.state
774 ,p_source_province => currec_resource_address.province
775 ,p_source_county => currec_resource_address.county
776 ,p_source_country => currec_resource_address.country
777
778 ,p_user_name => l_admin_rec.user_name
779 ,p_source_first_name => l_admin_rec.source_first_name
780 ,p_source_middle_name => l_admin_rec.source_middle_name
781 ,p_source_last_name => l_admin_rec.source_last_name
782
783
784 ,x_resource_id => x_resource_id
785 ,x_resource_number => x_resource_number
786 );
787
788 elsif (p_mode = 'UPDATE') then
789
790 Complete_Admin_Rec (
791 p_admin_rec =>l_admin_rec
792 ,x_complete_rec =>l_admin_complete_rec
793 );
794
795 JTF_RS_RESOURCE_PUB.Update_Resource(
796 p_api_version => 1.0
797 ,p_init_msg_list => FND_API.g_false
798 ,p_resource_id => l_admin_complete_rec.role_resource_id
799 ,p_resource_number => l_admin_complete_rec.resource_number
800 -- ,p_start_date_active => SYSDATE
801 -- ,p_end_date_active => FND_API.g_miss_date
802 ,p_resource_name => l_admin_complete_rec.resource_name -- resource_name
803 ,p_source_name => l_admin_complete_rec.source_name -- source_name
804
805 ,p_source_org_id => l_admin_complete_rec.source_org_id -- source_org_id
806 /*,p_source_org_name => l_admin_complete_rec.source_org_name -- source_org_name
807 ,p_source_address1 => currec_resource_address.address1
808 ,p_source_address2 => currec_resource_address.address2
809 ,p_source_address3 => currec_resource_address.address3
810 ,p_source_address4 => currec_resource_address.address4
811 ,p_source_city => currec_resource_address.city
812 ,p_source_postal_code => currec_resource_address.postal_code
813 ,p_source_state => currec_resource_address.state
814 ,p_source_province => currec_resource_address.province
815 ,p_source_county => currec_resource_address.county
816 ,p_source_country => currec_resource_address.country
817 */
818 ,p_source_first_name => l_admin_complete_rec.source_first_name
819 ,p_source_last_name => l_admin_complete_rec.source_last_name
820 ,p_source_middle_name => l_admin_complete_rec.source_middle_name
821 --,p_source_category => l_admin_complete_rec.resource_type
822 --,p_source_id => l_admin_complete_rec.partner_id -- source_id
823 --,p_user_id => l_admin_complete_rec.user_id -- user_id
824 --,p_contact_id => l_admin_complete_rec.contact_id
825 --,p_address_id => l_admin_complete_rec.party_site_id
826 ,p_object_version_num => l_admin_complete_rec.object_version_number
827 --,p_user_name => l_admin_complete_rec.user_name
828 ,x_return_status => x_return_status
829 ,x_msg_count => x_msg_count
830 ,x_msg_data => x_msg_data
831
832 );
833
834 end if;
835
836
837
838 CLOSE cur_resource_address;
839
840 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
841 PVX_Utility_PVT.debug_message(l_full_name ||': x_return_status : ' || x_return_status);
842 PVX_Utility_PVT.debug_message(l_full_name ||': x_msg_count : ' || x_msg_count);
843 PVX_Utility_PVT.debug_message(l_full_name ||': x_msg_data : ' || x_msg_data);
844 END IF;
845
846
847 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
848 FND_MESSAGE.set_name('PV', 'PV_MISC_ERROR_RES_CREATION');
849 FND_MESSAGE.set_token('ID',to_char(l_admin_rec.partner_id) );
850 FND_MSG_PUB.add;
851 END IF;
852
853 IF x_return_status = FND_API.g_ret_sts_error THEN
854 RAISE FND_API.g_exc_error;
855 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
856 RAISE FND_API.g_exc_unexpected_error;
857 END IF;
858
859 ------------------------- finish -------------------------------
860
861
862 -- Check for commit
863 IF FND_API.to_boolean(p_commit) THEN
864 COMMIT;
865 END IF;
866
867 FND_MSG_PUB.count_and_get(
868 p_encoded => FND_API.g_false,
869 p_count => x_msg_count,
870 p_data => x_msg_data
871 );
872
873 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
874 PVX_Utility_PVT.debug_message(l_full_name ||': end');
875 END IF;
876
877 EXCEPTION
878
879 WHEN FND_API.g_exc_error THEN
880 ROLLBACK TO Admin_Resource;
881 x_return_status := FND_API.g_ret_sts_error;
882 FND_MSG_PUB.count_and_get (
883 p_encoded => FND_API.g_false
884 ,p_count => x_msg_count
885 ,p_data => x_msg_data
886 );
887
888 WHEN FND_API.g_exc_unexpected_error THEN
889 ROLLBACK TO Admin_Resource;
890 x_return_status := FND_API.g_ret_sts_unexp_error ;
891 FND_MSG_PUB.count_and_get (
892 p_encoded => FND_API.g_false
893 ,p_count => x_msg_count
894 ,p_data => x_msg_data
895 );
896
897
898 WHEN OTHERS THEN
899 ROLLBACK TO Admin_Resource;
900 x_return_status := FND_API.g_ret_sts_unexp_error ;
901
902 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
903 THEN
904 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
905 END IF;
906
907 FND_MSG_PUB.count_and_get(
908 p_encoded => FND_API.g_false
909 ,p_count => x_msg_count
910 ,p_data => x_msg_data
911 );
912
913 END Admin_Resource;
914
915 ---------------------------------------------------------------------
916 -- PROCEDURE
917 -- Admin_Role
918 --
919 -- HISTORY
920 -- 09/07/2000 Shitij Vatsa Create.
921 ---------------------------------------------------------------------
922 PROCEDURE Admin_Role(
923 p_api_version IN NUMBER
924 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
925 ,p_commit IN VARCHAR2 := FND_API.g_false
926
927 ,x_return_status OUT NOCOPY VARCHAR2
928 ,x_msg_count OUT NOCOPY NUMBER
929 ,x_msg_data OUT NOCOPY VARCHAR2
930
931 ,p_admin_rec IN admin_rec_type
932 ,p_mode IN VARCHAR2
933 ,x_role_relate_id OUT NOCOPY NUMBER
934 )
935 IS
936
937 l_api_version CONSTANT NUMBER := 1.0;
938 l_api_name CONSTANT VARCHAR2(30) := 'Admin_Role';
939 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
940
941 l_return_status VARCHAR2(1);
942 -- Create record variables
943 l_admin_rec admin_rec_type := p_admin_rec;
944
945 -- Cursor : c_role_detail
946 CURSOR c_role_detail (cv_role_code IN VARCHAR2) IS
947 SELECT role_id
948 FROM JTF_RS_ROLES_VL
949 WHERE role_code = cv_role_code;
950 -- Cursor related variables
951 l_role_id NUMBER;
952
953 BEGIN
954
955 --------------------- initialize -----------------------
956 SAVEPOINT Admin_Role;
957
958 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
959 PVX_Utility_PVT.debug_message(l_full_name||': start');
960 END IF;
961
962 IF FND_API.to_boolean(p_init_msg_list) THEN
963 FND_MSG_PUB.initialize;
964 END IF;
965
966 IF NOT FND_API.compatible_api_call(
967 l_api_version,
968 p_api_version,
969 l_api_name,
970 g_pkg_name
971 ) THEN
972 RAISE FND_API.g_exc_unexpected_error;
973 END IF;
974
975 x_return_status := FND_API.g_ret_sts_success;
976
977
978 -------------------------- create resource role relate --------------------------
979 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
980 PVX_Utility_PVT.debug_message(l_full_name ||': Create_Resource_Role_Relate');
981 END IF;
982
983 -- Get the role detail
984 OPEN c_role_detail(l_admin_rec.role_code);
985 FETCH c_role_detail INTO l_role_id;
986 CLOSE c_role_detail;
987
988
989 -- Call the resource API to create the resource
990 JTF_RS_ROLE_RELATE_PUB.Create_Resource_Role_Relate(
991 p_api_version => 1.0
992 ,p_init_msg_list => FND_API.g_false
993 -- ,p_commit => FND_API.g_true
994 ,x_return_status => x_return_status
995 ,x_msg_count => x_msg_count
996 ,x_msg_data => x_msg_data
997
998 ,p_role_resource_type => l_admin_rec.role_resource_type
999 ,p_role_resource_id => l_admin_rec.role_resource_id
1000 ,p_role_id => l_role_id
1001 ,p_role_code => l_admin_rec.role_code
1002 ,p_start_date_active => SYSDATE
1003
1004 ,x_role_relate_id => x_role_relate_id
1005
1006 );
1007
1008 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1009 FND_MESSAGE.set_name('PV', 'PV_MISC_ERROR_ROLE_RELATE');
1010 FND_MESSAGE.set_token('ID',to_char(l_admin_rec.role_resource_id));
1011 FND_MSG_PUB.add;
1012 END IF;
1013
1014 IF x_return_status = FND_API.g_ret_sts_error THEN
1015 RAISE FND_API.g_exc_error;
1016 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1017 RAISE FND_API.g_exc_unexpected_error;
1018 END IF;
1019
1020 ------------------------- finish -------------------------------
1021
1022
1023 -- Check for commit
1024 IF FND_API.to_boolean(p_commit) THEN
1025 COMMIT;
1026 END IF;
1027
1028 FND_MSG_PUB.count_and_get(
1029 p_encoded => FND_API.g_false,
1030 p_count => x_msg_count,
1031 p_data => x_msg_data
1032 );
1033
1034 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1035 PVX_Utility_PVT.debug_message(l_full_name ||': end');
1036 END IF;
1037
1038 EXCEPTION
1039
1040 WHEN FND_API.g_exc_error THEN
1041 ROLLBACK TO Admin_Role;
1042 x_return_status := FND_API.g_ret_sts_error;
1043 FND_MSG_PUB.count_and_get (
1044 p_encoded => FND_API.g_false
1045 ,p_count => x_msg_count
1046 ,p_data => x_msg_data
1047 );
1048
1049 WHEN FND_API.g_exc_unexpected_error THEN
1050 ROLLBACK TO Admin_Role;
1051 x_return_status := FND_API.g_ret_sts_unexp_error ;
1052 FND_MSG_PUB.count_and_get (
1053 p_encoded => FND_API.g_false
1054 ,p_count => x_msg_count
1055 ,p_data => x_msg_data
1056 );
1057
1058
1059 WHEN OTHERS THEN
1060 ROLLBACK TO Admin_Role;
1061 x_return_status := FND_API.g_ret_sts_unexp_error ;
1062
1063 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1064 THEN
1065 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1066 END IF;
1067
1068 FND_MSG_PUB.count_and_get(
1069 p_encoded => FND_API.g_false
1070 ,p_count => x_msg_count
1071 ,p_data => x_msg_data
1072 );
1073
1074 END Admin_Role;
1075
1076 ---------------------------------------------------------------------
1077 -- PROCEDURE
1078 -- Admin_Group
1079 --
1080 -- HISTORY
1081 -- 10/10/2000 Shitij Vatsa Create.
1082 ---------------------------------------------------------------------
1083 PROCEDURE Admin_Group(
1084 p_api_version IN NUMBER
1085 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1086 ,p_commit IN VARCHAR2 := FND_API.g_false
1087
1088 ,x_return_status OUT NOCOPY VARCHAR2
1089 ,x_msg_count OUT NOCOPY NUMBER
1090 ,x_msg_data OUT NOCOPY VARCHAR2
1091
1092 ,p_admin_rec IN admin_rec_type
1093 ,p_mode IN VARCHAR2
1094 ,x_group_id OUT NOCOPY NUMBER
1095 ,x_group_number OUT NOCOPY VARCHAR2
1096 ,x_group_usage_id OUT NOCOPY NUMBER
1097 ,x_group_member_id OUT NOCOPY NUMBER
1098 )
1099 IS
1100
1101 l_api_version CONSTANT NUMBER := 1.0;
1102 l_api_name CONSTANT VARCHAR2(30) := 'Admin_Group';
1103 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1104
1105 l_return_status VARCHAR2(1);
1106
1107 -- Create record variables
1108 l_admin_rec admin_rec_type := p_admin_rec;
1109
1110 -- Initialize the group name : 'PRM_GRP' plus the passed partner_id
1111 --l_group_name VARCHAR2(30) := 'PRM_GRP'||TO_CHAR(l_admin_rec.partner_id);
1112
1113 -- Cursor : c_party_detail
1114 CURSOR c_party_detail (cv_partner_id IN NUMBER) IS
1115 SELECT SUBSTR(party_name,1,80)
1116 FROM hz_parties
1117 WHERE party_id = cv_partner_id;
1118 -- Cursor related variables
1119 l_group_desc VARCHAR2(240);
1120
1121
1122 -- Cursor : c_party_name
1123 CURSOR c_party_name (cv_partner_id IN NUMBER) IS
1124 SELECT SUBSTRB(PARTNER.party_name,1,44)||'('||PARTNER.party_id||')'
1125 FROM hz_relationships HZR, hz_parties PARTNER, hz_organization_profiles HZOP
1126 WHERE HZR.party_id = cv_partner_id
1127 AND HZR.subject_id = PARTNER.party_id
1128 AND PARTNER.party_id = HZOP.party_id
1129 AND NVL(HZOP.internal_flag,'N') = 'N'
1130 AND HZOP.effective_end_date IS NULL;
1131 -- Cursor related variables
1132 l_group_name VARCHAR2(60);
1133
1134 BEGIN
1135
1136 --------------------- initialize -----------------------
1137 SAVEPOINT Admin_Group;
1138
1139 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1140 PVX_Utility_PVT.debug_message(l_full_name||': start');
1141 END IF;
1142
1143 IF FND_API.to_boolean(p_init_msg_list) THEN
1144 FND_MSG_PUB.initialize;
1145 END IF;
1146
1147 IF NOT FND_API.compatible_api_call(
1148 l_api_version,
1149 p_api_version,
1150 l_api_name,
1151 g_pkg_name
1152 ) THEN
1153 RAISE FND_API.g_exc_unexpected_error;
1154 END IF;
1155
1156 x_return_status := FND_API.g_ret_sts_success;
1157
1158
1159 -------------------------- create resource group --------------------------
1160 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1161 PVX_Utility_PVT.debug_message(l_full_name ||': Create_Resource_Group');
1162 END IF;
1163
1164 -- Get the party detail
1165 OPEN c_party_detail(l_admin_rec.partner_id);
1166 FETCH c_party_detail INTO l_group_desc;
1167 CLOSE c_party_detail;
1168
1169 -- Get the party name
1170 OPEN c_party_name(l_admin_rec.partner_id);
1171 FETCH c_party_name INTO l_group_name;
1172 -- CLOSE c_party_name;
1173
1174 -- Fix for the bug # 2535467 begin
1175
1176 IF c_party_name%NOTFOUND THEN
1177 CLOSE c_party_name;
1178 FND_MESSAGE.set_name('PV', 'PV_DENY_INTERNAL_ORG_PROFILE');
1179 FND_MSG_PUB.add;
1180 RAISE FND_API.g_exc_error;
1181 ELSE
1182 CLOSE c_party_name;
1183 END IF;
1184
1185 -- Fix for the bug # 2535467 end
1186
1187 -- Call the Create_Resource_Group API to create the Group
1188
1189 JTF_RS_GROUPS_PUB.Create_Resource_Group(
1190 p_api_version => 1.0
1191 ,p_init_msg_list => FND_API.g_false
1192 -- ,p_commit => FND_API.g_true
1193 ,x_return_status => x_return_status
1194 ,x_msg_count => x_msg_count
1195 ,x_msg_data => x_msg_data
1196
1197 ,p_group_name => l_group_name
1198 ,p_group_desc => l_group_desc
1199 ,p_start_date_active => SYSDATE
1200 ,x_group_id => x_group_id
1201 ,x_group_number => x_group_number
1202 );
1203 --DBMS_OUTPUT.PUT_LINE('x_group_id : '||TO_CHAR(x_group_id));
1204
1205 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1206 FND_MESSAGE.set_name('PV', 'PV_MISC_ERROR_GROUP_CREATE');
1207 FND_MESSAGE.set_token('ID',to_char(l_admin_rec.partner_id));
1208 FND_MSG_PUB.add;
1209 END IF;
1210
1211 IF x_return_status = FND_API.g_ret_sts_error THEN
1212 RAISE FND_API.g_exc_error;
1213 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1214 RAISE FND_API.g_exc_unexpected_error;
1215 END IF;
1216
1217 -------------------------- create group usage --------------------------
1218 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1219 PVX_Utility_PVT.debug_message(l_full_name ||': Create_Group_Usage');
1220 END IF;
1221
1222 -- Call the Create_Group_Usage API to create the Group Usage
1223 JTF_RS_GROUP_USAGES_PUB.Create_Group_Usage(
1224 p_api_version => 1.0
1225 ,p_init_msg_list => FND_API.g_false
1226 -- ,p_commit => FND_API.g_true
1227 ,x_return_status => x_return_status
1228 ,x_msg_count => x_msg_count
1229 ,x_msg_data => x_msg_data
1230
1231 ,p_group_id => x_group_id
1232 ,p_group_number => x_group_number
1233 ,p_usage => 'PRM'
1234 ,x_group_usage_id => x_group_usage_id
1235 );
1236
1237 --DBMS_OUTPUT.PUT_LINE('x_group_usage_id : '||TO_CHAR(x_group_usage_id));
1238
1239 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1240 FND_MESSAGE.set_name('PV', 'PV_MISC_ERROR_GROUP_USAGE');
1241 FND_MESSAGE.set_token('ID',to_char(x_group_id));
1242 FND_MSG_PUB.add;
1243 END IF;
1244
1245 IF x_return_status = FND_API.g_ret_sts_error THEN
1246 RAISE FND_API.g_exc_error;
1247 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1248 RAISE FND_API.g_exc_unexpected_error;
1249 END IF;
1250
1251 -------------------------- create resource group member --------------------------
1252 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1253 PVX_Utility_PVT.debug_message(l_full_name ||': Create_Resource_Group_Members');
1254 END IF;
1255
1256 -- Call the Create_Group_Usage API to create the Group Usage
1257 JTF_RS_GROUP_MEMBERS_PUB.Create_Resource_Group_Members(
1258 p_api_version => 1.0
1259 ,p_init_msg_list => FND_API.g_false
1260 -- ,p_commit => FND_API.g_true
1261 ,x_return_status => x_return_status
1262 ,x_msg_count => x_msg_count
1263 ,x_msg_data => x_msg_data
1264
1265 ,p_group_id => x_group_id
1266 ,p_group_number => x_group_number
1267 ,p_resource_id => l_admin_rec.role_resource_id
1268 ,p_resource_number => l_admin_rec.resource_number
1269 ,x_group_member_id => x_group_member_id
1270 );
1271
1272 --DBMS_OUTPUT.PUT_LINE('x_group_member_id : '||TO_CHAR(x_group_member_id));
1273
1274 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1275 FND_MESSAGE.set_name('PV', 'PV_MISC_ERROR_GROUP_MEMBER');
1276 FND_MESSAGE.set_token('ID1',to_char(l_admin_rec.role_resource_id));
1277 FND_MESSAGE.set_token('ID2',to_char(x_group_id));
1278 FND_MSG_PUB.add;
1279 END IF;
1280
1281 IF x_return_status = FND_API.g_ret_sts_error THEN
1282 RAISE FND_API.g_exc_error;
1283 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1284 RAISE FND_API.g_exc_unexpected_error;
1285 END IF;
1286
1287
1288
1289 ------------------------- finish -------------------------------
1290
1291
1292 -- Check for commit
1293 IF FND_API.to_boolean(p_commit) THEN
1294 COMMIT;
1295 END IF;
1296
1297 FND_MSG_PUB.count_and_get(
1298 p_encoded => FND_API.g_false,
1299 p_count => x_msg_count,
1300 p_data => x_msg_data
1301 );
1302
1303 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1304 PVX_Utility_PVT.debug_message(l_full_name ||': end');
1305 END IF;
1306
1307 EXCEPTION
1308
1309 WHEN FND_API.g_exc_error THEN
1310 ROLLBACK TO Admin_Group;
1311 x_return_status := FND_API.g_ret_sts_error;
1312 FND_MSG_PUB.count_and_get (
1313 p_encoded => FND_API.g_false
1314 ,p_count => x_msg_count
1315 ,p_data => x_msg_data
1316 );
1317
1318 WHEN FND_API.g_exc_unexpected_error THEN
1319 ROLLBACK TO Admin_Group;
1320 x_return_status := FND_API.g_ret_sts_unexp_error ;
1321 FND_MSG_PUB.count_and_get (
1322 p_encoded => FND_API.g_false
1323 ,p_count => x_msg_count
1324 ,p_data => x_msg_data
1325 );
1326
1327
1328 WHEN OTHERS THEN
1329 ROLLBACK TO Admin_Group;
1330 x_return_status := FND_API.g_ret_sts_unexp_error ;
1331
1332 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1333 THEN
1334 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1335 END IF;
1336
1337 FND_MSG_PUB.count_and_get(
1338 p_encoded => FND_API.g_false
1339 ,p_count => x_msg_count
1340 ,p_data => x_msg_data
1341 );
1342 END Admin_Group;
1343
1344 ---------------------------------------------------------------------
1345 -- PROCEDURE
1346 -- Admin_Group_Member
1347 --
1348 -- HISTORY
1349 -- 10/11/2000 Shitij Vatsa Create.
1350 ---------------------------------------------------------------------
1351 PROCEDURE Admin_Group_Member(
1352 p_api_version IN NUMBER
1353 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1354 ,p_commit IN VARCHAR2 := FND_API.g_false
1355
1356 ,x_return_status OUT NOCOPY VARCHAR2
1357 ,x_msg_count OUT NOCOPY NUMBER
1358 ,x_msg_data OUT NOCOPY VARCHAR2
1359
1360 ,p_admin_rec IN admin_rec_type
1361 ,p_mode IN VARCHAR2
1362 ,x_group_member_id OUT NOCOPY NUMBER
1363 )
1364 IS
1365
1366 l_api_version CONSTANT NUMBER := 1.0;
1367 l_api_name CONSTANT VARCHAR2(30) := 'Admin_Group_Member';
1368 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1369
1370 l_return_status VARCHAR2(1);
1371 -- Create record variables
1372 l_admin_rec admin_rec_type := p_admin_rec;
1373
1374 -- Cursor : c_role_detail
1375 CURSOR c_role_detail (cv_role_code IN VARCHAR2) IS
1376 SELECT role_id
1377 FROM JTF_RS_ROLES_VL
1378 WHERE role_code = cv_role_code;
1379 -- Cursor related variables
1380 l_role_id NUMBER;
1381
1382 BEGIN
1383
1384 --------------------- initialize -----------------------
1385 SAVEPOINT Admin_Group_Member;
1386
1387 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1388 PVX_Utility_PVT.debug_message(l_full_name||': start');
1389 END IF;
1390
1391 IF FND_API.to_boolean(p_init_msg_list) THEN
1392 FND_MSG_PUB.initialize;
1393 END IF;
1394
1395 IF NOT FND_API.compatible_api_call(
1396 l_api_version,
1397 p_api_version,
1398 l_api_name,
1399 g_pkg_name
1400 ) THEN
1401 RAISE FND_API.g_exc_unexpected_error;
1402 END IF;
1403
1404 x_return_status := FND_API.g_ret_sts_success;
1405
1406
1407 -------------------------- create resource group member --------------------------
1408 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1409 PVX_Utility_PVT.debug_message(l_full_name ||': Create_Resource_Group_Members');
1410 END IF;
1411
1412 -- Call the Create_Group_Usage API to create the Group Usage
1413 JTF_RS_GROUP_MEMBERS_PUB.Create_Resource_Group_Members(
1414 p_api_version => 1.0
1415 ,p_init_msg_list => FND_API.g_false
1416 -- ,p_commit => FND_API.g_true
1417 ,x_return_status => x_return_status
1418 ,x_msg_count => x_msg_count
1419 ,x_msg_data => x_msg_data
1420
1421 ,p_group_id => l_admin_rec.group_id
1422 ,p_group_number => l_admin_rec.group_number
1423 ,p_resource_id => l_admin_rec.role_resource_id
1424 ,p_resource_number => l_admin_rec.resource_number
1425 ,x_group_member_id => x_group_member_id
1426 );
1427
1428
1429 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1430 FND_MESSAGE.set_name('PV', 'PV_MISC_ERROR_GROUP_MEMBER');
1431 FND_MESSAGE.set_token('ID1',to_char(l_admin_rec.role_resource_id));
1432 FND_MESSAGE.set_token('ID2',to_char(l_admin_rec.group_id));
1433 FND_MSG_PUB.add;
1434 END IF;
1435
1436 IF x_return_status = FND_API.g_ret_sts_error THEN
1437 RAISE FND_API.g_exc_error;
1438 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1439 RAISE FND_API.g_exc_unexpected_error;
1440 END IF;
1441
1442 ------------------------- finish -------------------------------
1443
1444
1445 -- Check for commit
1446 IF FND_API.to_boolean(p_commit) THEN
1447 COMMIT;
1448 END IF;
1449
1450 FND_MSG_PUB.count_and_get(
1451 p_encoded => FND_API.g_false,
1452 p_count => x_msg_count,
1453 p_data => x_msg_data
1454 );
1455
1456 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1457 PVX_Utility_PVT.debug_message(l_full_name ||': end');
1458 END IF;
1459
1460 EXCEPTION
1461
1462 WHEN FND_API.g_exc_error THEN
1463 ROLLBACK TO Admin_Group_Member;
1464 x_return_status := FND_API.g_ret_sts_error;
1465 FND_MSG_PUB.count_and_get (
1466 p_encoded => FND_API.g_false
1467 ,p_count => x_msg_count
1468 ,p_data => x_msg_data
1469 );
1470
1471 WHEN FND_API.g_exc_unexpected_error THEN
1472 ROLLBACK TO Admin_Group_Member;
1473 x_return_status := FND_API.g_ret_sts_unexp_error ;
1474 FND_MSG_PUB.count_and_get (
1475 p_encoded => FND_API.g_false
1476 ,p_count => x_msg_count
1477 ,p_data => x_msg_data
1478 );
1479
1480
1481 WHEN OTHERS THEN
1482 ROLLBACK TO Admin_Group_Member;
1483 x_return_status := FND_API.g_ret_sts_unexp_error ;
1484
1485 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1486 THEN
1487 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1488 END IF;
1489
1490 FND_MSG_PUB.count_and_get(
1491 p_encoded => FND_API.g_false
1492 ,p_count => x_msg_count
1493 ,p_data => x_msg_data
1494 );
1495 END Admin_Group_Member;
1496
1497
1498 ---------------------------------------------------------------------
1499 -- PROCEDURE
1500 -- Update_User
1501 --
1502 -- HISTORY
1503 ---------------------------------------------------------------------
1504 PROCEDURE Update_User(
1505 p_api_version IN NUMBER
1506 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1507 ,p_commit IN VARCHAR2 := FND_API.g_false
1508 ,x_return_status OUT NOCOPY VARCHAR2
1509 ,x_msg_count OUT NOCOPY NUMBER
1510 ,x_msg_data OUT NOCOPY VARCHAR2
1511 ,p_fnd_rec IN fnd_rec_type
1512
1513 )
1514 IS
1515
1516 l_api_version CONSTANT NUMBER := 1.0;
1517 l_api_name CONSTANT VARCHAR2(30) := 'Update_User';
1518 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1519
1520 l_return_status VARCHAR2(1);
1521 -- Create record variables
1522 l_fnd_rec fnd_rec_type := p_fnd_rec;
1523
1524 l_object_version_number NUMBER := 1;
1525 l_owner VARCHAR2(10) := 'PRM';
1526
1527 -- For reference
1528 -- FND_API. return value constants
1529 -- G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := 'S';
1530 -- G_RET_STS_ERROR CONSTANT VARCHAR2(1) := 'E';
1531 -- G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := 'U';
1532
1533
1534
1535 BEGIN
1536
1537 --------------------- initialize -----------------------
1538 SAVEPOINT Update_User;
1539
1540 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1541 PVX_Utility_PVT.debug_message(l_full_name||': start');
1542 END IF;
1543
1544 IF FND_API.to_boolean(p_init_msg_list) THEN
1545 FND_MSG_PUB.initialize;
1546 END IF;
1547
1548 IF NOT FND_API.compatible_api_call(
1549 l_api_version,
1550 p_api_version,
1551 l_api_name,
1552 g_pkg_name
1553 ) THEN
1554 RAISE FND_API.g_exc_unexpected_error;
1555 END IF;
1556
1557 x_return_status := FND_API.g_ret_sts_success;
1558
1559
1560 -------------------------- create --------------------------
1561 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1562 PVX_Utility_PVT.debug_message(l_full_name ||': create');
1563 END IF;
1564
1565
1566 -- If user_name IS NULL, raise an error
1567 IF l_fnd_rec.user_name IS NULL THEN
1568 FND_MESSAGE.set_name('PV', 'PV_FND_NO_USER_NAME');
1569 FND_MSG_PUB.add;
1570 x_return_status := FND_API.g_ret_sts_error;
1571 RAISE FND_API.g_exc_error;
1572 END IF;
1573
1574
1575
1576 FND_USER_PKG.UpdateUser
1577 (
1578 X_USER_NAME => l_fnd_rec.user_name
1579 ,X_OWNER => l_owner
1580 ,X_END_DATE => l_fnd_rec.end_date
1581 ,X_EMAIL_ADDRESS => l_fnd_rec.email_address
1582 );
1583
1584 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1585 FND_MESSAGE.set_name('PV', 'PV_ERROR_UPDATE_USER');
1586 FND_MSG_PUB.add;
1587 END IF;
1588
1589
1590 IF x_return_status = FND_API.g_ret_sts_error THEN
1591 RAISE FND_API.g_exc_error;
1592 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
1593 RAISE FND_API.g_exc_unexpected_error;
1594 END IF;
1595
1596 -- Check for commit
1597 IF FND_API.to_boolean(p_commit) THEN
1598 COMMIT;
1599 END IF;
1600
1601 FND_MSG_PUB.count_and_get(
1602 p_encoded => FND_API.g_false,
1603 p_count => x_msg_count,
1604 p_data => x_msg_data
1605 );
1606
1607 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1608 PVX_Utility_PVT.debug_message(l_full_name ||': end');
1609 END IF;
1610
1611 EXCEPTION
1612
1613 WHEN FND_API.g_exc_error THEN
1614 ROLLBACK TO Update_User;
1615 x_return_status := FND_API.g_ret_sts_error;
1616 FND_MSG_PUB.count_and_get (
1617 p_encoded => FND_API.g_false
1618 ,p_count => x_msg_count
1619 ,p_data => x_msg_data
1620 );
1621
1622 WHEN FND_API.g_exc_unexpected_error THEN
1623 ROLLBACK TO Update_User;
1624 x_return_status := FND_API.g_ret_sts_unexp_error ;
1625 FND_MSG_PUB.count_and_get (
1626 p_encoded => FND_API.g_false
1627 ,p_count => x_msg_count
1628 ,p_data => x_msg_data
1629 );
1630
1631
1632 WHEN OTHERS THEN
1633 ROLLBACK TO Update_User;
1634 x_return_status := FND_API.g_ret_sts_unexp_error ;
1635
1636 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1637 THEN
1638 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1639 END IF;
1640
1641 FND_MSG_PUB.count_and_get(
1642 p_encoded => FND_API.g_false
1643 ,p_count => x_msg_count
1644 ,p_data => x_msg_data
1645 );
1646
1647 END Update_User;
1648
1649 ---------------------------------------------------------------------
1650 -- PROCEDURE
1651 -- Disable_Responsibility
1652 --
1653 -- HISTORY
1654 -- 18/05/2001 Shitij Vatsa Create.
1655 ---------------------------------------------------------------------
1656 PROCEDURE Disable_Responsibility(
1657 p_api_version IN NUMBER
1658 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
1659 ,p_commit IN VARCHAR2 := FND_API.g_false
1660
1661 ,x_return_status OUT NOCOPY VARCHAR2
1662 ,x_msg_count OUT NOCOPY NUMBER
1663 ,x_msg_data OUT NOCOPY VARCHAR2
1664
1665 ,p_fnd_rec IN fnd_rec_type
1666 ,p_mode IN VARCHAR2
1667 )
1668 IS
1669
1670 l_api_version CONSTANT NUMBER := 1.0;
1671 l_api_name CONSTANT VARCHAR2(30) := 'Disable_Responsibility';
1672 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1673
1674 l_return_status VARCHAR2(1);
1675 -- Create record variables
1676 l_fnd_rec fnd_rec_type := p_fnd_rec;
1677
1678
1679 -- Define API cursors
1680
1681 -- Cursor : cur_user_resp_detail
1682 CURSOR cur_user_resp_detail (cv_user_id IN NUMBER
1683 ,cv_resp_id IN NUMBER
1684 ,cv_resp_app_id IN NUMBER) IS
1685 SELECT FNDU.user_name
1686 ,FNDR.responsibility_key
1687 ,FNDSG.security_group_key
1688 FROM fnd_user FNDU, fnd_responsibility FNDR, fnd_user_resp_groups FNDURG, fnd_security_groups FNDSG
1689 WHERE FNDU.user_id = cv_user_id
1690 AND FNDR.responsibility_id = cv_resp_id
1691 AND FNDR.application_id = cv_resp_app_id
1692 AND FNDU.user_id = FNDURG.user_id
1693 AND FNDR.responsibility_id = FNDURG.responsibility_id
1694 AND FNDR.application_id = FNDURG.responsibility_application_id
1695 AND FNDURG.security_group_id = FNDSG.security_group_id
1696 ;
1697 -- Cursor Record
1698 l_crec_user_resp_detail cur_user_resp_detail%ROWTYPE;
1699
1700 BEGIN
1701
1702 --------------------- initialize -----------------------
1703 SAVEPOINT Disable_Responsibility;
1704
1705 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1706 PVX_Utility_PVT.debug_message(l_full_name||': start');
1707 END IF;
1708
1709 IF FND_API.to_boolean(p_init_msg_list) THEN
1710 FND_MSG_PUB.initialize;
1711 END IF;
1712
1713 IF NOT FND_API.compatible_api_call(
1714 l_api_version,
1715 p_api_version,
1716 l_api_name,
1717 g_pkg_name
1718 ) THEN
1719 RAISE FND_API.g_exc_unexpected_error;
1720 END IF;
1721
1722 x_return_status := FND_API.g_ret_sts_success;
1723
1724
1725 -------------------------- create --------------------------
1726 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1727 PVX_Utility_PVT.debug_message(l_full_name ||': create');
1728 END IF;
1729
1730
1731 -- Get the user and responsibility details
1732 OPEN cur_user_resp_detail(l_fnd_rec.user_id
1733 ,l_fnd_rec.resp_id
1734 ,l_fnd_rec.resp_app_id);
1735 FETCH cur_user_resp_detail INTO l_crec_user_resp_detail;
1736 CLOSE cur_user_resp_detail;
1737
1738 -- Call the FND API to disable the user responsibility mapping.
1739 -- You need to pass the following mandatory parameters
1740 -- username: User Name
1741 -- resp_app: Application Short Name
1742 -- resp_key: Responsibility Key
1743 -- security_group: Security Group Key
1744
1745 FND_USER_PKG.DelResp(
1746 username => l_crec_user_resp_detail.user_name
1747 ,resp_app => l_fnd_rec.resp_app_short_name -- Application_Short_name from POL (Partners On-Line)
1748 ,resp_key => l_crec_user_resp_detail.responsibility_key
1749 ,security_group => l_crec_user_resp_detail.security_group_key
1750 );
1751
1752
1753
1754 ------------------------- finish -------------------------------
1755
1756
1757 -- Check for commit
1758 IF FND_API.to_boolean(p_commit) THEN
1759 COMMIT;
1760 END IF;
1761
1762 FND_MSG_PUB.count_and_get(
1763 p_encoded => FND_API.g_false,
1764 p_count => x_msg_count,
1765 p_data => x_msg_data
1766 );
1767
1768 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1769 PVX_Utility_PVT.debug_message(l_full_name ||': end');
1770 END IF;
1771
1772 EXCEPTION
1773
1774 WHEN FND_API.g_exc_error THEN
1775 ROLLBACK TO Disable_Responsibility;
1776 x_return_status := FND_API.g_ret_sts_error;
1777 FND_MSG_PUB.count_and_get (
1778 p_encoded => FND_API.g_false
1779 ,p_count => x_msg_count
1780 ,p_data => x_msg_data
1781 );
1782
1783 WHEN FND_API.g_exc_unexpected_error THEN
1784 ROLLBACK TO Disable_Responsibility;
1785 x_return_status := FND_API.g_ret_sts_unexp_error ;
1786 FND_MSG_PUB.count_and_get (
1787 p_encoded => FND_API.g_false
1788 ,p_count => x_msg_count
1789 ,p_data => x_msg_data
1790 );
1791
1792
1793 WHEN OTHERS THEN
1794 ROLLBACK TO Disable_Responsibility;
1795 x_return_status := FND_API.g_ret_sts_unexp_error ;
1796
1797 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1798 THEN
1799 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1800 END IF;
1801
1802 FND_MSG_PUB.count_and_get(
1803 p_encoded => FND_API.g_false
1804 ,p_count => x_msg_count
1805 ,p_data => x_msg_data
1806 );
1807
1808 END Disable_Responsibility;
1809
1810 ---------------------------------------------------------------------
1811 -- PROCEDURE
1812 -- Update_Partner_Status
1813 --
1814 -- HISTORY
1815 -- 04-JUL-2003 Narasimha Ramu Create.
1816 ---------------------------------------------------------------------
1817 PROCEDURE update_partner_status (
1818 ERRBUF OUT NOCOPY VARCHAR2,
1819 RETCODE OUT NOCOPY VARCHAR2 ) IS
1820
1821 CURSOR c_partners IS
1822 SELECT *
1823 FROM pv_partner_profiles
1824 Where status <> 'M'; -- hekkiral for bug fix 6694939. Merged records
1825 -- should not be touched.
1826
1827 CURSOR c_relationship_status (p_party_id IN NUMBER, p_partner_party_id IN NUMBER) IS
1828 SELECT subject_id vendor_party_id,
1829 start_date,
1830 end_date,
1831 status
1832 FROM hz_relationships
1833 WHERE party_id = p_party_id
1834 AND object_id = p_partner_party_id;
1835
1836 CURSOR c_party_status (p_party_id IN NUMBER) IS
1837 SELECT NVL(status, 'A') party_status
1838 FROM hz_parties
1839 WHERE party_id = p_party_id;
1840
1841 CURSOR c_resource_status (p_resource_id IN NUMBER) IS
1842 SELECT start_date_active,
1843 end_date_active
1844 FROM jtf_rs_resource_extns
1845 WHERE resource_id = p_resource_id;
1846
1847 CURSOR c_party_name (p_party_id IN NUMBER) IS
1848 SELECT SUBSTRB(party_name, 1, 100) partner_name,
1849 party_number partner_number
1850 FROM hz_parties
1851 WHERE party_id = p_party_id;
1852
1853 l_vendor_party_id NUMBER;
1854 l_start_date DATE;
1855 l_end_date DATE;
1856 l_status VARCHAR2(1);
1857 l_new_partner_status VARCHAR2(1);
1858 l_api_version CONSTANT NUMBER := 1.0;
1859 l_return_status VARCHAR2(1);
1860 l_msg_count NUMBER;
1861 l_prtnr_prfls_rec PVX_PRTNR_PRFLS_PVT.prtnr_prfls_rec_type;
1862 l_message VARCHAR2(32000);
1863 l_msg_data VARCHAR2(32000);
1864 l_total_partners NUMBER := 0;
1865 l_error_partners NUMBER := 0;
1866 l_partner_name VARCHAR2(360);
1867 l_partner_number VARCHAR2(30);
1868
1869 BEGIN
1870
1871 FND_MESSAGE.SET_NAME( application => 'PV'
1872 ,name => 'PV_PARTNER_STATUS_BATCH_START');
1873
1874 FND_MESSAGE.SET_TOKEN( token => 'P_DATE_TIME'
1875 ,value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') );
1876
1877 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
1878 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
1879
1880
1881 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1882
1883 FOR l_partners_rec in c_partners LOOP
1884
1885 l_total_partners := l_total_partners + 1;
1886
1887 l_new_partner_status := 'A';
1888
1889 OPEN c_relationship_status ( l_partners_rec.partner_id, l_partners_rec.partner_party_id );
1890 FETCH c_relationship_status INTO l_vendor_party_id, l_start_date, l_end_date, l_status;
1891 IF c_relationship_status%FOUND THEN
1892 IF l_status = 'I' THEN
1893 l_new_partner_status := 'I';
1894 ELSE
1895 IF l_start_date > SYSDATE OR NVL(l_end_date, SYSDATE) < SYSDATE THEN
1896 l_new_partner_status := 'I';
1897 END IF;
1898 END IF;
1899 END IF;
1900 CLOSE c_relationship_status;
1901
1902 IF l_new_partner_status <> 'I' THEN
1903 OPEN c_party_status (l_partners_rec.partner_party_id);
1904 FETCH c_party_status INTO l_status;
1905 IF c_party_status%FOUND THEN
1906 IF l_status = 'I' THEN
1907 l_new_partner_status := 'I';
1908 END IF;
1909 END IF;
1910 CLOSE c_party_status;
1911 END IF;
1912
1913 IF l_new_partner_status <> 'I' THEN
1914 OPEN c_party_status (l_vendor_party_id);
1915 FETCH c_party_status INTO l_status;
1916 IF c_party_status%FOUND THEN
1917 IF l_status = 'I' THEN
1918 l_new_partner_status := 'I';
1919 END IF;
1920 END IF;
1921 CLOSE c_party_status;
1922 END IF;
1923
1924 IF l_new_partner_status <> 'I' THEN
1925 OPEN c_resource_status (l_partners_rec.partner_resource_id);
1926 FETCH c_resource_status INTO l_start_date, l_end_date;
1927 IF c_resource_status%FOUND THEN
1928 IF l_start_date > SYSDATE OR NVL(l_end_date, SYSDATE) < SYSDATE THEN
1929 l_new_partner_status := 'I';
1930 END IF;
1931 END IF;
1932 CLOSE c_resource_status;
1933 END IF;
1934
1935 IF l_partners_rec.status IS NULL OR l_partners_rec.status <> l_new_partner_status THEN
1936
1937 l_prtnr_prfls_rec.partner_profile_id := l_partners_rec.partner_profile_id;
1938 l_prtnr_prfls_rec.object_version_number := l_partners_rec.object_version_number;
1939 l_prtnr_prfls_rec.partner_id := l_partners_rec.partner_id;
1940 l_prtnr_prfls_rec.target_revenue_amt := l_partners_rec.target_revenue_amt;
1941 l_prtnr_prfls_rec.actual_revenue_amt := l_partners_rec.actual_revenue_amt;
1942 l_prtnr_prfls_rec.target_revenue_pct := l_partners_rec.target_revenue_pct;
1943 l_prtnr_prfls_rec.actual_revenue_pct := l_partners_rec.actual_revenue_pct;
1944 l_prtnr_prfls_rec.orig_system_reference := l_partners_rec.orig_system_reference;
1945 l_prtnr_prfls_rec.orig_system_type := l_partners_rec.orig_system_type;
1946 l_prtnr_prfls_rec.capacity_size := l_partners_rec.capacity_size;
1947 l_prtnr_prfls_rec.capacity_amount := l_partners_rec.capacity_amount;
1948 l_prtnr_prfls_rec.auto_match_allowed_flag := l_partners_rec.auto_match_allowed_flag;
1949 l_prtnr_prfls_rec.purchase_method := l_partners_rec.purchase_method;
1950 l_prtnr_prfls_rec.cm_id := l_partners_rec.cm_id;
1951 l_prtnr_prfls_rec.ph_support_rep := l_partners_rec.ph_support_rep;
1952 l_prtnr_prfls_rec.lead_sharing_status := l_partners_rec.lead_sharing_status;
1953 l_prtnr_prfls_rec.lead_share_appr_flag := l_partners_rec.lead_share_appr_flag;
1954 l_prtnr_prfls_rec.partner_relationship_id := l_partners_rec.partner_relationship_id;
1955 l_prtnr_prfls_rec.partner_level := l_partners_rec.partner_level;
1956 l_prtnr_prfls_rec.preferred_vad_id := l_partners_rec.preferred_vad_id;
1957 l_prtnr_prfls_rec.partner_group_id := l_partners_rec.partner_group_id;
1958 l_prtnr_prfls_rec.partner_resource_id := l_partners_rec.partner_resource_id;
1959 l_prtnr_prfls_rec.partner_group_number := l_partners_rec.partner_group_number;
1960 l_prtnr_prfls_rec.partner_resource_number := l_partners_rec.partner_resource_number;
1961 l_prtnr_prfls_rec.sales_partner_flag := l_partners_rec.sales_partner_flag;
1962 l_prtnr_prfls_rec.indirectly_managed_flag := l_partners_rec.indirectly_managed_flag;
1963 l_prtnr_prfls_rec.channel_marketing_manager := l_partners_rec.channel_marketing_manager;
1964 l_prtnr_prfls_rec.related_partner_id := l_partners_rec.related_partner_id;
1965 l_prtnr_prfls_rec.max_users := l_partners_rec.max_users;
1966 l_prtnr_prfls_rec.partner_party_id := l_partners_rec.partner_party_id;
1967 l_prtnr_prfls_rec.status := l_new_partner_status;
1968
1969 SAVEPOINT update_partner_status;
1970
1971 PVX_PRTNR_PRFLS_PVT.Update_Prtnr_Prfls(
1972 p_api_version => l_api_version
1973 ,p_init_msg_list => FND_API.g_true
1974 ,p_commit => FND_API.g_false
1975 ,p_validation_level => FND_API.g_valid_level_full
1976 ,x_return_status => l_return_status
1977 ,x_msg_count => l_msg_count
1978 ,x_msg_data => l_msg_data
1979 ,p_prtnr_prfls_rec => l_prtnr_prfls_rec
1980 );
1981
1982 IF (l_return_status <> fnd_api.G_RET_STS_SUCCESS) THEN
1983
1984 ROLLBACK TO update_partner_status;
1985
1986 l_error_partners := l_error_partners + 1;
1987
1988 OPEN c_party_name (l_partners_rec.partner_party_id);
1989 FETCH c_party_name INTO l_partner_name, l_partner_number;
1990 CLOSE c_party_name;
1991
1992 FND_MESSAGE.SET_NAME( application => 'PV'
1993 ,name => 'PV_PARTNER_STATUS_ERR_PARTNER');
1994 FND_MESSAGE.SET_TOKEN( token => 'PARTNER'
1995 ,value => l_partner_name || ' (' || l_partner_number || ')');
1996 l_message := FND_MESSAGE.get;
1997
1998 FND_FILE.PUT_LINE( FND_FILE.LOG, l_message );
1999 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2000
2001 IF l_msg_count > 0 THEN
2002 l_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first, p_encoded => FND_API.g_false);
2003 while (l_message is not null)
2004 LOOP
2005 fnd_file.put_line(FND_FILE.LOG,substr(l_message,1,200));
2006 l_message := fnd_msg_pub.get(p_encoded => FND_API.g_false);
2007 END LOOP;
2008 END IF;
2009
2010 END IF;
2011
2012 END IF;
2013
2014 END LOOP;
2015
2016 COMMIT;
2017
2018 IF l_total_partners = l_error_partners THEN
2019
2020 FND_MESSAGE.SET_NAME( application => 'PV'
2021 ,name => 'PV_PARTNER_STATUS_COMPLET_FAIL');
2022 FND_MSG_PUB.ADD;
2023 l_message := FND_MESSAGE.get;
2024
2025 FND_FILE.PUT_LINE( FND_FILE.LOG, l_message );
2026 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2027
2028 RETCODE := 2;
2029 ERRBUF := l_message;
2030
2031 ELSIF l_total_partners <> l_error_partners AND l_error_partners <> 0 THEN
2032
2033 FND_MESSAGE.SET_NAME( application => 'PV'
2034 ,name => 'PV_PARTNER_STATUS_PARTIAL_FAIL');
2035 FND_MESSAGE.SET_TOKEN( token => 'TOTAL_PARTNERS'
2036 ,value => l_total_partners);
2037 FND_MESSAGE.SET_TOKEN( token => 'ERROR_PARTNERS'
2038 ,value => l_error_partners);
2039 l_message := FND_MESSAGE.get;
2040
2041 FND_FILE.PUT_LINE( FND_FILE.LOG, l_message );
2042 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2043
2044 RETCODE := 1;
2045 ERRBUF := l_message;
2046
2047 ELSIF l_error_partners = 0 THEN
2048 FND_MESSAGE.SET_NAME( application => 'PV'
2049 ,name => 'PV_PARTNER_STATUS_SUCCESS');
2050 l_message := FND_MESSAGE.get;
2051
2052 FND_FILE.PUT_LINE( FND_FILE.LOG, l_message );
2053 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2054
2055 RETCODE := 0;
2056 ERRBUF := l_message;
2057 END IF;
2058
2059 FND_MESSAGE.SET_NAME( application => 'PV'
2060 ,name => 'PV_PARTNER_STATUS_BATCH_END');
2061
2062 FND_MESSAGE.SET_TOKEN( token => 'P_DATE_TIME'
2063 ,value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') );
2064
2065 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
2066 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2067
2068 EXCEPTION
2069 WHEN OTHERS THEN
2070 ROLLBACK;
2071 FND_MESSAGE.SET_NAME( application => 'PV'
2072 ,name => 'PV_UNKNOWN_ERROR');
2073 FND_MESSAGE.SET_TOKEN( token => 'TEXT'
2074 ,value => 'Database Error:'||sqlcode||' '||sqlerrm);
2075 FND_MSG_PUB.ADD;
2076 l_message := FND_MESSAGE.get;
2077
2078 FND_FILE.PUT_LINE( FND_FILE.LOG, l_message );
2079 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2080
2081 FND_MESSAGE.SET_NAME( application => 'PV'
2082 ,name => 'PV_UNKNOWN_ERROR');
2083 RETCODE := 1;
2084 ERRBUF := fnd_message.get;
2085
2086 END Update_Partner_Status;
2087
2088 -------------------------------------------------------------------
2089 -- PROCEDURE
2090 -- Cr_As_Accts_With_Partner_User
2091 --
2092 -- PURPOSE
2093 -- Procedure to Create and Associate the customer Accounts with
2094 -- Partner Users.
2095 --
2096 -- PARAMETERS
2097 -- None
2098 --
2099 -- NOTES
2100 --
2101 --
2102 --------------------------------------------------------------------
2103 PROCEDURE Cr_As_Accts_With_Partner_User (
2104 ERRBUF OUT NOCOPY VARCHAR2,
2105 RETCODE OUT NOCOPY VARCHAR2 ) IS
2106
2107 CURSOR c_get_partner_wo_accounts IS
2108 select distinct pvpp.partner_party_id, hzp.party_name
2109 from pv_partner_profiles pvpp, hz_parties hzp
2110 where pvpp.status = 'A'
2111 and hzp.party_id = pvpp.partner_party_id
2112 and hzp.status = 'A'
2113 and not exists
2114 (select 1
2115 from hz_cust_accounts hzca
2116 where hzca.status = 'A'
2117 and hzca.party_id = pvpp.partner_party_id);
2118
2119 CURSOR c_get_prtnrcntct_wo_role IS
2120 select hzr.party_id, max(hzca.cust_account_id) cust_account_id
2121 from jtf_rs_resource_extns jtfre, hz_relationships hzr, hz_cust_accounts hzca
2122 where jtfre.category= 'PARTY'
2123 and jtfre.user_id is not null
2124 and jtfre.source_id = hzr.party_id
2125 and hzr.object_id in (select pvpp.partner_party_id
2126 from pv_partner_profiles pvpp
2127 where status = 'A')
2128 and hzca.party_id = hzr.object_id
2129 and hzca.status = 'A'
2130 and hzr.relationship_code = 'EMPLOYEE_OF'
2131 and hzr.directional_flag = 'F'
2132 and hzr.status = 'A'
2133 and hzr.start_date <= sysdate
2134 and nvl(hzr.end_date,sysdate) >= sysdate
2135 and not exists
2136 ( select 1
2137 from hz_cust_account_roles hzcar
2138 where hzcar.cust_account_id IN (select cust_account_id from hz_cust_accounts where party_id = hzr.object_id)
2139 and nvl(hzcar.status,'A')='A'
2140 and nvl(hzcar.begin_date,sysdate) <= sysdate
2141 and nvl(hzcar.end_date,sysdate) >= sysdate
2142 and hzr.party_id = hzcar.party_id
2143 )
2144 group by hzr.party_id;
2145
2146
2147 account_rec HZ_CUST_ACCOUNT_V2PUB.cust_account_rec_type;
2148 organization_rec HZ_PARTY_V2PUB.organization_rec_type;
2149 cust_profile_rec HZ_CUSTOMER_PROFILE_V2PUB.customer_profile_rec_type;
2150 cust_acct_roles_rec HZ_CUST_ACCOUNT_ROLE_V2PUB.cust_account_role_rec_type;
2151
2152 l_cust_account_id NUMBER;
2153 l_account_number VARCHAR2(30);
2154 l_party_id NUMBER;
2155 l_party_number VARCHAR2(30);
2156 l_profile_id NUMBER;
2157 l_cust_account_role_id NUMBER;
2158 l_message VARCHAR2(4000);
2159 l_partner_info VARCHAR2(2000);
2160 i NUMBER;
2161 l_gen_cust_num VARCHAR2(3);
2162
2163 type numArray is table of number;
2164 type VCHAR2ARRAY is table of VARCHAR2(360);
2165
2166
2167 l_ptnr_party_id_array numArray;
2168 l_ptnr_party_name_array VCHAR2ARRAY;
2169 l_ptnr_cntct_id_array numArray;
2170 l_account_id_array numArray;
2171
2172 l_msg_count NUMBER;
2173 l_msg_data VARCHAR2(200);
2174 l_return_status VARCHAR2(1);
2175 l_error_partners NUMBER;
2176
2177 BEGIN
2178
2179 FND_MESSAGE.SET_NAME(
2180 application => 'PV'
2181 ,name => 'PV_CR_CUST_ACCT_BATCH_START');
2182
2183 FND_MESSAGE.SET_TOKEN(
2184 token => 'P_DATE_TIME'
2185 ,value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') );
2186
2187 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
2188 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2189
2190 /** Create a TCA account for partners if it does not exist **/
2191 account_rec.Created_by_Module := 'TCA_V2_API';
2192 organization_rec.Created_by_Module := 'TCA_V2_API';
2193 cust_profile_rec.Created_by_Module := 'TCA_V2_API';
2194
2195 SELECT generate_customer_number INTO l_gen_cust_num FROM ar_system_parameters;
2196
2197 open c_get_partner_wo_accounts;
2198 LOOP
2199 FETCH c_get_partner_wo_accounts bulk collect
2200 INTO l_ptnr_party_id_array,
2201 l_ptnr_party_name_array LIMIT 100;
2202 FOR k in 1 .. l_ptnr_party_id_array.count
2203 LOOP
2204
2205 BEGIN
2206
2207 -- Set the l_return_status to SUCCESS before starting the processing of each Parnter.
2208 l_return_status := FND_API.g_ret_sts_success;
2209 SAVEPOINT create_cust_account;
2210 l_partner_info := l_ptnr_party_name_array(k) || ' (' || l_ptnr_party_id_array(k) || ')';
2211
2212 IF (l_gen_cust_num is null or l_gen_cust_num = 'N') THEN
2213 select TO_CHAR( HZ_ACCOUNT_NUM_S.NEXTVAL) into account_rec.account_number from dual ;
2214 END IF;
2215
2216 organization_rec.party_rec.party_id := l_ptnr_party_id_array(k);
2217 account_rec.account_name := 'System Generated Account';
2218
2219 HZ_CUST_ACCOUNT_V2PUB.create_cust_account
2220 (
2221 p_init_msg_list => FND_API.G_FALSE,
2222 p_cust_account_rec => account_rec,
2223 p_organization_rec => organization_rec,
2224 p_customer_profile_rec => cust_profile_rec,
2225 x_cust_account_id => l_cust_account_id,
2226 x_account_number => l_account_number,
2227 x_party_id => l_party_id,
2228 x_party_number => l_party_number,
2229 x_profile_id => l_profile_id,
2230 x_return_status => l_return_status,
2231 x_msg_count => l_msg_count,
2232 x_msg_data => l_msg_data
2233 );
2234
2235 IF (l_return_status <> fnd_api.G_RET_STS_SUCCESS) THEN
2236 RAISE FND_API.G_EXC_ERROR;
2237 END IF;
2238
2239 EXCEPTION
2240
2241 WHEN Fnd_Api.G_EXC_ERROR THEN
2242
2243 ROLLBACK TO create_cust_account;
2244 l_message := null;
2245 l_error_partners := l_error_partners + 1;
2246 l_partner_info := l_ptnr_party_name_array(k) || ' (' || l_ptnr_party_id_array(k) || ')';
2247
2248 IF l_msg_count > 0 THEN
2249 l_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first, p_encoded => FND_API.g_false);
2250 while (l_message is not null)
2251 LOOP
2252 l_message := l_message||fnd_msg_pub.get(p_encoded => FND_API.g_false);
2253 END LOOP;
2254 END IF;
2255
2256 FND_FILE.PUT_LINE( FND_FILE.LOG, substr(l_partner_info,1,50)||substr(l_message,1,500) );
2257 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2258 RETCODE := '2';
2259
2260 WHEN OTHERS THEN
2261 ROLLBACK TO create_cust_account;
2262 l_message := null;
2263 l_error_partners := l_error_partners + 1;
2264
2265
2266 IF l_msg_count > 0 THEN
2267 l_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first, p_encoded => FND_API.g_false);
2268 while (l_message is not null)
2269 LOOP
2270 l_message := l_message||fnd_msg_pub.get(p_encoded => FND_API.g_false);
2271 END LOOP;
2272 END IF;
2273
2274 FND_FILE.PUT_LINE( FND_FILE.LOG, substr(l_partner_info,1,50)||substr(l_message,1,500) );
2275 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2276 RETCODE := '2';
2277 END ; /* END of the Internal BEGIN */
2278
2279 END LOOP; /* k in 1 .. l_ptnr_party_id_array.count */
2280 exit when c_get_partner_wo_accounts%notfound;
2281 END LOOP; /* FETCH c_get_partner_wo_accounts bulk collect */
2282 CLOSE c_get_partner_wo_accounts;
2283
2284 FND_MESSAGE.SET_NAME(
2285 application => 'PV'
2286 ,name => 'PV_CR_CUST_ACCT_BATCH_END');
2287
2288 FND_MESSAGE.SET_TOKEN(
2289 token => 'P_DATE_TIME'
2290 ,value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') );
2291 --------------------------------------------------------------------------
2292
2293 FND_MESSAGE.SET_NAME(
2294 application => 'PV'
2295 ,name => 'PV_AS_ACCT_TO_CNTCT_BTCH_START');
2296
2297 FND_MESSAGE.SET_TOKEN(
2298 token => 'P_DATE_TIME'
2299 ,value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') );
2300
2301 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
2302 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2303
2304 /** Assign an account to contact **/
2305 open c_get_prtnrcntct_wo_role;
2306 LOOP
2307 fetch c_get_prtnrcntct_wo_role bulk collect into l_ptnr_cntct_id_array, l_account_id_array LIMIT 100;
2308 for k in 1 .. l_ptnr_cntct_id_array.count
2309 LOOP
2310
2311 BEGIN
2312 l_return_status := FND_API.g_ret_sts_success;
2313 SAVEPOINT create_cust_account_role;
2314 l_partner_info := substr(l_ptnr_cntct_id_array(k),1,30) ||' '|| substr(l_account_id_array(k),1,30);
2315
2316 cust_acct_roles_rec.party_id := l_ptnr_cntct_id_array(k);
2317 cust_acct_roles_rec.cust_account_id := l_account_id_array(k);
2318 cust_acct_roles_rec.role_type := 'CONTACT';
2319 cust_acct_roles_rec.Created_by_Module := 'TCA_V2_API';
2320
2321 HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role(
2322 p_init_msg_list => FND_API.G_FALSE,
2323 p_cust_account_role_rec => cust_acct_roles_rec,
2324 x_return_status => l_return_status,
2325 x_msg_count => l_msg_count,
2326 x_msg_data => l_msg_data ,
2327 x_cust_account_role_id => l_cust_account_role_id
2328 );
2329
2330 IF (l_return_status <> fnd_api.G_RET_STS_SUCCESS) THEN
2331 RAISE FND_API.G_EXC_ERROR;
2332 END IF;
2333
2334 FND_FILE.PUT_LINE( FND_FILE.LOG, substr(l_partner_info,1,60)||fnd_api.G_RET_STS_SUCCESS );
2335 COMMIT;
2336 EXCEPTION
2337
2338 WHEN Fnd_Api.G_EXC_ERROR THEN
2339
2340 ROLLBACK TO create_cust_account_role;
2341 l_message := null;
2342
2343 IF l_msg_count > 0 THEN
2344 l_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first, p_encoded => FND_API.g_false);
2345 while (l_message is not null)
2346 LOOP
2347 l_message := l_message||fnd_msg_pub.get(p_encoded => FND_API.g_false);
2348 END LOOP;
2349 END IF;
2350
2351 FND_FILE.PUT_LINE( FND_FILE.LOG, substr(l_partner_info,1,60)||substr(l_message,1,500) );
2352 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2353
2354 WHEN OTHERS THEN
2355 ROLLBACK TO create_cust_account_role;
2356 l_message := null;
2357 l_error_partners := l_error_partners + 1;
2358 l_partner_info := substr(l_ptnr_cntct_id_array(k),1,30) ||' '|| substr(l_account_id_array(k),1,30);
2359
2360 IF l_msg_count > 0 THEN
2361 l_message := fnd_msg_pub.get(p_msg_index => fnd_msg_pub.g_first, p_encoded => FND_API.g_false);
2362 while (l_message is not null)
2363 LOOP
2364 l_message := l_message||fnd_msg_pub.get(p_encoded => FND_API.g_false);
2365 END LOOP;
2366 END IF;
2367
2368 FND_FILE.PUT_LINE( FND_FILE.LOG, substr(l_partner_info,1,60)||substr(l_message,1,500) );
2369 FND_FILE.NEW_LINE( FND_FILE.LOG, 1 );
2370
2371 END ; /* END of the Internal BEGIN */
2372 end loop;
2373 exit when c_get_prtnrcntct_wo_role%notfound;
2374 end loop;
2375 Close c_get_prtnrcntct_wo_role;
2376
2377 FND_MESSAGE.SET_NAME(
2378 application => 'PV'
2379 ,name => 'PV_AS_ACCT_TO_CNTCT_BTCH_END');
2380
2381 FND_MESSAGE.SET_TOKEN(
2382 token => 'P_DATE_TIME'
2383 ,value => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') );
2384
2385 END Cr_As_Accts_With_Partner_User;
2386
2387 END PVX_Misc_PVT;