[Home] [Help]
PACKAGE BODY: APPS.PV_OPPORTUNITY_VHUK
Source
1 package body PV_OPPORTUNITY_VHUK as
2 /* $Header: pvxvoptb.pls 120.4 2006/03/28 12:19:45 amaram ship $ */
3
4 -- Start of Comments
5
6 -- Package name : PV_OPPORTUNITY_VHUK
7 -- Purpose : 1. Send out email notification to CM when an opportunity is created by Partner / VAD
8 -- 2. When an Opportunity is created or updated retrieve the partner related information
9 -- associated with the campaign from AMS table and copy into
10 -- AS_LEAD_ASSIGNMENTS table to keep track of the associated partner with the Campaign.
11 -- History :
12 --
13 -- NOTE :
14 -- End of Comments
15 --
16
17
18 G_PKG_NAME CONSTANT VARCHAR2(30):='PV_OPPORTUNITY_VHUK';
19 G_FILE_NAME CONSTANT VARCHAR2(12):='pvxvoptb.pls';
20
21
22 -- --------------------------------------------------------------
23 -- Used for inserting output messages to the message table.
24 -- --------------------------------------------------------------
25 PROCEDURE Debug(
26 p_msg_string IN VARCHAR2
27 );
28
29 -- private to this package
30 procedure CreateRole (
31 p_api_version_number 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 ,p_itemType IN VARCHAR2
36 ,p_itemKey IN VARCHAR2
37 ,p_partner_id IN NUMBER
38 ,p_notify_type IN VARCHAR2
39 ,p_assignment_status IN VARCHAR2
40 ,x_roleName OUT NOCOPY VARCHAR2
41 ,x_msg_count OUT NOCOPY NUMBER
42 ,x_msg_data OUT NOCOPY VARCHAR2
43 ,x_return_status OUT NOCOPY VARCHAR2) is
44
45 l_api_name CONSTANT VARCHAR2(30) := 'CreateRole';
46 l_api_version_number CONSTANT NUMBER := 1.0;
47
48 cursor lc_get_party_for_status (pc_itemType varchar2,
49 pc_itemKey varchar2,
50 pc_notify_type varchar2,
51 pc_assign_status varchar2) is
52 select distinct usr.user_name
53 from pv_lead_assignments aa, pv_party_notifications bb, fnd_user usr
54 where bb.wf_item_key = pc_itemKey
55 and bb.wf_item_type = pc_itemType
56 and bb.notification_type = pc_notify_type
57 and bb.lead_assignment_id = aa.lead_assignment_id
58 and aa.status = pc_assign_status
59 and bb.user_id = usr.user_id;
60
61 cursor lc_role_exist_chk (pc_rolename varchar2) is
62 select name from wf_local_roles
63 where name = pc_rolename;
64
65 l_role_list wf_directory.usertable;
66 l_username_tbl pv_assignment_pub.g_varchar_table_type := pv_assignment_pub.g_varchar_table_type();
67 l_username varchar2(50);
68 l_adhoc_role varchar2(80);
69 l_exist_rolename varchar2(80);
70
71 begin
72 -- Standard call to check for call compatibility.
73
74 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
75 p_api_version_number,
76 l_api_name,
77 G_PKG_NAME) THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
79
80 END IF;
81
82 -- Initialize message list if p_init_msg_list is set to TRUE.
83 IF FND_API.to_Boolean( p_init_msg_list )
84 THEN
85 fnd_msg_pub.initialize;
86 END IF;
87
88 -- Debug Message
89 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
90 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
91 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || '. Roletype: ' || p_notify_type ||
92 '. Itemtype: ' || p_itemtype || '. p_assignemnt_status: ' || p_assignment_status);
93 fnd_msg_pub.Add;
94 END IF;
95
96 x_return_status := FND_API.G_RET_STS_SUCCESS ;
97
98 l_adhoc_role := 'PV2' || p_notify_type || p_itemkey || '+' || nvl(p_partner_id, '0');
99
100 open lc_get_party_for_status (pc_itemType => p_itemtype,
101 pc_itemKey => p_itemKey,
102 pc_notify_type => p_notify_type,
103 pc_assign_status => p_assignment_status);
104
105 loop
106 fetch lc_get_party_for_status into l_username;
107 exit when lc_get_party_for_status%notfound;
108 l_username_tbl.extend;
109 l_username_tbl(l_username_tbl.last) := l_username;
110 end loop;
111 close lc_get_party_for_status;
112
113 for i in 1 .. l_username_tbl.count loop
114 l_role_list(i) := l_username_tbl(i);
115 end loop;
116
117 if l_role_list.count > 0 then
118
119
120 -- Debug Message
121 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
122 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
123 if l_exist_rolename is null then
124 fnd_message.Set_token('TEXT', 'Creating role: '||l_adhoc_role||' with members :--');
125 else
126 fnd_message.Set_token('TEXT', 'Adding to role: '||l_adhoc_role||' with members :--');
127 end if;
128 fnd_msg_pub.Add;
129 END IF;
130
131 FOR i in 1 .. l_role_list.count
132 LOOP
133
134
135
136 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
137 fnd_message.Set_token('TEXT', l_role_list(i) );
138 fnd_msg_pub.Add;
139 END IF;
140
141 END LOOP;
142
143
144 if l_exist_rolename is null then
145 wf_directory.CreateAdHocRole2(role_name => l_adhoc_role,
146 role_display_name => l_adhoc_role,
147 role_users => l_role_list,
148 expiration_date => sysdate + 5);
149 else
150 wf_directory.AddUsersToAdHocRole2(role_name => l_adhoc_role,
151 role_users => l_role_list);
152 end if;
153
154 x_roleName := l_adhoc_role;
155
156 else
157
158 fnd_message.SET_NAME('PV', 'PV_EMPTY_ROLE');
159 fnd_msg_pub.ADD;
160
161 raise FND_API.G_EXC_ERROR;
162
163 end if;
164
165 IF FND_API.To_Boolean ( p_commit ) THEN
166 COMMIT WORK;
167 END IF;
168
169 -- Standard call to get message count and if count is 1, get message info.
170 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
171 p_count => x_msg_count,
172 p_data => x_msg_data);
173 EXCEPTION
174
175 WHEN FND_API.G_EXC_ERROR THEN
176
177 x_return_status := FND_API.G_RET_STS_ERROR ;
178 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
179 p_count => x_msg_count,
180 p_data => x_msg_data);
181
182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
183
184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
185 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
186 p_count => x_msg_count,
187 p_data => x_msg_data);
188
189 WHEN OTHERS THEN
190
191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
192 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
193 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
194 p_count => x_msg_count,
195 p_data => x_msg_data);
196 end CreateRole;
197
198
199
200
201
202 /********************************************************/
203 /* Takes the username table, item type and send */
204 /* email notification. */
205 /********************************************************/
206
207 procedure Send_Email_By_Workflow (
208 p_api_version_number IN NUMBER,
209 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
210 p_commit IN VARCHAR2 := FND_API.G_FALSE,
211 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
212 p_user_name_tbl IN JTF_VARCHAR2_TABLE_100,
213 p_user_type_tbl IN JTF_VARCHAR2_TABLE_100,
214 p_username IN VARCHAR2,
215 p_opp_amt IN VARCHAR2,
216 p_opp_name IN VARCHAR2,
217 p_customer_name IN VARCHAR2,
218 p_lead_number IN NUMBER,
219 p_from_status IN VARCHAR2,
220 p_to_status IN VARCHAR2,
221 p_vendor_org_name IN VARCHAR2,
222 p_partner_names IN VARCHAR2,
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2) is
226
227 l_api_name CONSTANT VARCHAR2(30) := 'Send_Email_By_Workflow';
228 l_api_version_number CONSTANT NUMBER := 1.0;
229
230 l_cm_role_list wf_directory.usertable;
231 l_am_role_list wf_directory.usertable;
232 l_ot_role_list wf_directory.usertable;
233 l_pt_role_list wf_directory.usertable;
234
235 l_am_adhoc_role VARCHAR2(80);
236 l_cm_adhoc_role VARCHAR2(80);
237 l_pt_adhoc_role VARCHAR2(80);
238 l_ot_adhoc_role VARCHAR2(80);
239
240 l_itemType CONSTANT VARCHAR2(30) := g_wf_itemtype_notify;
241 l_itemKey VARCHAR2(30);
242
243 l_send_respond_url VARCHAR2(500);
244 l_vendor_org_name VARCHAR2(50);
245 l_email_enabled VARCHAR2(5);
246
247 begin
248 -- Standard call to check for call compatibility.
249 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
250 p_api_version_number,
251 l_api_name,
252 G_PKG_NAME) THEN
253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
254 END IF;
255
256 -- Initialize message list if p_init_msg_list is set to TRUE.
257 IF FND_API.to_Boolean( p_init_msg_list )
258 THEN
259 fnd_msg_pub.initialize;
260 END IF;
261
262 -- Debug Message
263 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
264 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
265 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || p_from_status || p_to_status);
266 fnd_msg_pub.Add;
267 END IF;
268
269 x_return_status := FND_API.G_RET_STS_SUCCESS ;
270
271 -- check the profile value and return if the value is not Y
272 l_email_enabled := nvl(fnd_profile.value('PV_EMAIL_NOTIFICATION_FLAG'), 'Y');
273
274 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
275 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
276 fnd_message.Set_Token('TEXT', 'Email Notication is Enabled '||l_email_enabled);
277 fnd_msg_pub.Add;
278 END IF;
279
280 if (l_email_enabled <> 'Y') then
281 return;
282 else
283 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
284 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
285 fnd_message.Set_Token('TEXT', 'Email Notication is Enabled ');
286 fnd_msg_pub.Add;
287 END IF;
288 end if;
289
290 SELECT PV_LEAD_WORKFLOWS_S.nextval INTO l_itemKey
291 FROM dual;
292
293 FOR i in 1 .. p_user_name_tbl.count
294 LOOP
295
296 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
297 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
298 fnd_message.Set_Token('TEXT', 'In Loop of p_user_name_tbl '||p_user_name_tbl(i));
299 fnd_msg_pub.Add;
300 END IF;
301
302 IF p_user_type_tbl(i) = 'AM' THEN
303 l_am_role_list(i) := p_user_name_tbl(i);
304 ELSIF p_user_type_tbl(i) = 'CM' THEN
305 l_cm_role_list(i) := p_user_name_tbl(i);
306 ELSIF p_user_type_tbl(i) = 'OTHER' THEN
307 l_ot_role_list(i) := p_user_name_tbl(i);
308 ELSIF p_user_type_tbl(i) = 'PT' THEN
309 l_pt_role_list(i) := p_user_name_tbl(i);
310 END IF;
311
312 END LOOP;
313
314 IF l_am_role_list.count > 0 then
315 l_am_adhoc_role := 'PV_' || l_itemKey || 'AM' || '_' || '0';
316
317 -- Debug Message
318
319 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
320 Debug('Creating role AM : '|| l_am_adhoc_role || ' with members :--');
321 END IF;
322
323 FOR i in 1 .. l_am_role_list.count
324 LOOP
325
326
327
328 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
329 dEBUG( l_am_role_list(i) );
330 END IF;
331
332 END LOOP;
333
334 wf_directory.CreateAdHocRole2(role_name => l_am_adhoc_role,
335 role_display_name => l_am_adhoc_role,
336 role_users => l_am_role_list);
337 END IF;
338
339 IF l_cm_role_list.count > 0 then
340 l_cm_adhoc_role := 'PV_' || l_itemKey || 'CM' || '_' || '0';
341
342 -- Debug Message
343 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
344 debug('Creating role CM : '|| l_cm_adhoc_role || ' with members :-' );
345 END IF;
346
347 FOR i in 1 .. l_cm_role_list.count
348 LOOP
349 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
350 dEBUG( l_cm_role_list(i) );
351 END IF;
352 END LOOP;
353 wf_directory.CreateAdHocRole2(role_name => l_cm_adhoc_role,
354 role_display_name => l_cm_adhoc_role,
355 role_users => l_cm_role_list);
356 END IF;
357
358 IF l_pt_role_list.count > 0 then
359 l_pt_adhoc_role := 'PV_' || l_itemKey || 'PT' || '_' || '0';
360
361 -- Debug Message
362 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
363 debug( 'Creating role PT: '|| l_pt_adhoc_role || ' with members :-' );
364 END IF;
365 FOR i in 1 .. l_pt_role_list.count
366 LOOP
367 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
368 dEBUG( l_pt_role_list(i) );
369 END IF;
370 END LOOP;
371
372 wf_directory.CreateAdHocRole2(role_name => l_pt_adhoc_role,
373 role_display_name => l_pt_adhoc_role,
374 role_users => l_pt_role_list);
375 END IF;
376
377 IF l_ot_role_list.count > 0 then
378 l_ot_adhoc_role := 'PV_' || l_itemKey || 'OTHER' || '_' || '0';
379
380 -- Debug Message
381 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
382 debug('Creating role OT : '|| l_ot_adhoc_role || ' with members:- ' );
383 END IF;
384 FOR i in 1 .. l_ot_role_list.count
385 LOOP
386 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
387 dEBUG( l_ot_role_list(i) );
388 END IF;
389 END LOOP;
390
391 wf_directory.CreateAdHocRole2(role_name => l_ot_adhoc_role,
392 role_display_name => l_ot_adhoc_role,
393 role_users => l_ot_role_list);
394
395 END IF;
396
397 IF l_cm_role_list.count < 1 AND l_am_role_list.count < 1
398 AND l_pt_role_list.count < 1 AND l_ot_role_list.count < 1
399 THEN
400 return;
401 ELSE
402
403 -- Once the parameters for workflow is validated, start the workflow
404 wf_engine.CreateProcess (ItemType => l_itemType,
405 ItemKey => l_itemKey,
406 process => g_wf_pcs_notify_party);
407
408 wf_engine.SetItemUserKey (ItemType => l_itemType,
409 ItemKey => l_itemKey,
410 userKey => l_itemkey);
411
412 /* Coomented out for the wf limitation of owner, that cannot be more than 30 chars
413
414 wf_engine.SetItemOwner (ItemType => l_itemType,
415 ItemKey => l_itemKey,
416 Owner => p_username);
417 */
418
419 wf_engine.SetItemAttrText (ItemType => l_itemType,
420 ItemKey => l_itemKey,
421 aname => g_wf_attr_am_notify_role,
422 avalue => l_am_adhoc_role);
423
424 wf_engine.SetItemAttrText (ItemType => l_itemType,
425 ItemKey => l_itemKey,
426 aname => g_wf_attr_cm_notify_role,
427 avalue => l_cm_adhoc_role);
428
429 wf_engine.SetItemAttrText (ItemType => l_itemType,
430 ItemKey => l_itemKey,
431 aname => g_wf_attr_pt_notify_role,
432 avalue => l_pt_adhoc_role);
433
434 wf_engine.SetItemAttrText (ItemType => l_itemType,
435 ItemKey => l_itemKey,
436 aname => g_wf_attr_ot_notify_role,
437 avalue => l_ot_adhoc_role);
438
439 wf_engine.SetItemAttrText (ItemType => l_itemType,
440 ItemKey => l_itemKey,
441 aname => g_wf_attr_opp_number,
442 avalue => p_lead_number);
443
444 wf_engine.SetItemAttrText ( ItemType => l_itemType,
445 ItemKey => l_itemKey,
446 aname => g_wf_attr_customer_name,
447 avalue => p_customer_name);
448
449 wf_engine.SetItemAttrText ( ItemType => l_itemType,
450 ItemKey => l_itemKey,
451 aname => g_wf_attr_opp_amt,
452 avalue => p_opp_amt);
453
454 wf_engine.SetItemAttrText ( ItemType => l_itemType,
455 ItemKey => l_itemKey,
456 aname => g_wf_attr_opp_name,
457 avalue => p_opp_name);
458
459 wf_engine.SetItemAttrText ( ItemType => l_itemType,
460 ItemKey => l_itemKey,
461 aname => g_wf_attr_vendor_org_name,
462 avalue => p_vendor_org_name);
463
464 l_send_respond_url := fnd_profile.value('PV_WORKFLOW_RESPOND_SELF_SERVICE_URL');
465
466 wf_engine.SetItemAttrText ( ItemType => l_itemType,
467 ItemKey => l_itemKey,
468 aname => g_wf_attr_send_url,
469 avalue => l_send_respond_url);
470
471 wf_engine.SetItemAttrText (ItemType => l_itemType,
472 ItemKey => l_itemKey,
473 aname => g_wf_attr_from_status,
474 avalue => p_from_status);
475
476 wf_engine.SetItemAttrText (ItemType => l_itemType,
477 ItemKey => l_itemKey,
478 aname => g_wf_attr_to_status,
479 avalue => p_to_status);
480
481 wf_engine.SetItemAttrText (ItemType => l_itemType,
482 ItemKey => l_itemKey,
483 aname => g_wf_attr_partner_name,
484 avalue => p_partner_names);
485
486 wf_engine.StartProcess (ItemType => l_itemType,
487 ItemKey => l_itemKey);
488
489 -- Call the following procedure to see whether workflow was able to send notification successfully.
490 PV_ASSIGN_UTIL_PVT.checkforErrors
491 (p_api_version_number => 1.0
492 ,p_init_msg_list => FND_API.G_FALSE
493 ,p_commit => FND_API.G_FALSE
494 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
495 ,p_itemtype => l_itemType
496 ,p_itemkey => l_itemKey
497 ,x_msg_count => x_msg_count
498 ,x_msg_data => x_msg_data
499 ,x_return_status => x_return_status);
500
501 -- Check the x_return_status. If its not successful throw an exception.
502 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
503 raise FND_API.G_EXC_ERROR;
504 end if;
505
506 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
507 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
508 fnd_message.Set_token('TEXT', 'After Checkforerror');
509 fnd_msg_pub.Add;
510 END IF;
511 END IF;
512
513 IF FND_API.To_Boolean ( p_commit ) THEN
514 COMMIT WORK;
515 END IF;
516
517 -- Standard call to get message count and if count is 1, get message info.
518 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
519 p_count => x_msg_count,
520 p_data => x_msg_data);
521 EXCEPTION
522
523 WHEN FND_API.G_EXC_ERROR THEN
524 x_return_status := FND_API.G_RET_STS_ERROR ;
525 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
526 p_count => x_msg_count,
527 p_data => x_msg_data);
528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
530 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
531 p_count => x_msg_count,
532 p_data => x_msg_data);
533 WHEN OTHERS THEN
534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
535 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
536 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
537 p_count => x_msg_count,
538 p_data => x_msg_data);
539 end Send_Email_By_Workflow;
540
541
542 /**********************************************************************/
543 /* General API to start the workflow based on the username list */
544 /* This private methos is used for Opportunity notication module. */
545 /* email notification. */
546 /**********************************************************************/
547 procedure StartWorkflow (
548 p_api_version_number IN NUMBER,
549 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
550 p_commit IN VARCHAR2 := FND_API.G_FALSE,
551 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
552 p_itemKey IN VARCHAR2,
553 p_itemType IN VARCHAR2,
554 p_partner_id IN NUMBER,
555 p_partner_name IN VARCHAR2,
556 p_lead_id IN NUMBER,
557 p_opp_name IN VARCHAR2,
558 p_lead_number IN NUMBER,
559 p_customer_id IN NUMBER,
560 p_address_id IN NUMBER,
561 p_customer_name IN VARCHAR2,
562 p_creating_username IN VARCHAR2,
563 p_bypass_cm_ok_flag IN VARCHAR2,
564 x_return_status OUT NOCOPY VARCHAR2,
565 x_msg_count OUT NOCOPY NUMBER,
566 x_msg_data OUT NOCOPY VARCHAR2) is
567
568 l_api_name CONSTANT VARCHAR2(30) := 'StartWorkflow';
569 l_api_version_number CONSTANT NUMBER := 1.0;
570 l_role_name varchar2(80);
571 l_email_enabled varchar2(30);
572 l_respondURL varchar2(100);
573 l_r_notify_type varchar2(20) := 'MATCHED_TO';
574 l_vendor_org_name VARCHAR2(200);
575 l_send_respond_url VARCHAR2(200);
576
577 cursor lc_get_vendor_org(pc_partner_id NUMBER)
578 is
579 select hp.party_name
580 from hz_relationships porg,
581 hz_parties hp,
582 hz_organization_profiles hzop,
583 pv_partner_profiles pvpp
584 where porg.party_id = pc_partner_id
585 and porg.subject_table_name = 'HZ_PARTIES'
586 and porg.object_table_name = 'HZ_PARTIES'
587 and porg.relationship_code = 'PARTNER_OF'
588 and porg.relationship_type = 'PARTNER'
589 and porg.status = 'A'
590 and PORG.start_date <= SYSDATE
591 and nvl(PORG.end_date, SYSDATE) >= SYSDATE
592 and porg.object_id = hp.party_id
593 and hp.status = 'A'
594 and hp.party_type = 'ORGANIZATION'
595 AND HZOP.party_id = hp.party_id
596 AND HZOP.effective_end_date is null
597 AND HZOP.internal_flag = 'Y'
598 AND PVPP.partner_id = PORG.party_id
599 AND PVPP.SALES_PARTNER_FLAG = 'Y';
600
601 begin
602 -- Standard call to check for call compatibility.
603 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
604 p_api_version_number,
605 l_api_name,
606 G_PKG_NAME) THEN
607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
608
609 END IF;
610
611 -- Initialize message list if p_init_msg_list is set to TRUE.
612 IF FND_API.to_Boolean( p_init_msg_list )
613 THEN
614 fnd_msg_pub.initialize;
615 END IF;
616
617 -- Debug Message
618 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
619 debug( 'In ' || l_api_name);
620 END IF;
621
622 -- Initialize API return status to success
623 x_return_status := FND_API.G_RET_STS_SUCCESS;
624
625 -- check the profile value and return if the value is not Y
626 l_email_enabled := nvl(fnd_profile.value('PV_EMAIL_NOTIFICATION_FLAG'), 'Y');
627
628 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
629 debug('Email Enabled Flag '||l_email_enabled);
630 END IF;
631
632 if (l_email_enabled <> 'Y') then
633 return;
634 end if;
635
636 open lc_get_vendor_org(p_partner_id);
637 fetch lc_get_vendor_org INTO l_vendor_org_name;
638 close lc_get_vendor_org;
639
640 -- Create the role before sending the notification
641 CreateRole (
642 p_api_version_number => 1.0
643 ,p_init_msg_list => FND_API.G_FALSE
644 ,p_commit => FND_API.G_FALSE
645 ,p_validation_level => p_validation_level
646 ,p_itemType => p_itemtype
647 ,p_itemKey => p_itemKey
648 ,p_partner_id => p_partner_id
649 ,p_notify_type => l_r_notify_type
650 ,p_assignment_status => 'PT_CREATED'
651 ,x_roleName => l_role_name
652 ,x_msg_count => x_msg_count
653 ,x_msg_data => x_msg_data
654 ,x_return_status => x_return_status);
655
656 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
657 raise FND_API.G_EXC_ERROR;
658 end if;
659
660 -- Debug Message
661 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
662 debug( 'After Createrole, withing Startworkflow');
663 END IF;
664
665 wf_engine.CreateProcess ( ItemType => p_itemtype,
666 ItemKey => p_itemkey,
667 process => g_wf_pcs_notify_cm);
668
669 wf_engine.SetItemUserKey ( itemType => p_itemtype,
670 itemKey => p_itemkey,
671 userKey => p_itemkey);
672
673 /* Coomented out for the wf limitation of owner, that cannot be more than 30 chars
674 wf_engine.SetItemOwner ( ItemType => p_itemtype,
675 ItemKey => p_itemkey,
676 Owner => p_creating_username);
677 */
678
679 wf_engine.SetItemAttrText ( itemtype => p_itemType,
680 itemkey => p_itemKey,
681 aname => g_wf_attr_vendor_org_name,
682 avalue => l_vendor_org_name);
683
684 wf_engine.SetItemAttrText ( itemtype => p_itemType,
685 itemkey => p_itemKey,
686 aname => g_wf_attr_lead_id,
687 avalue => p_lead_id);
688
689 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
690 itemkey => p_itemkey,
691 aname => g_wf_attr_opp_number,
692 avalue => p_lead_number);
693
694 wf_engine.SetItemAttrText ( itemtype => p_itemType,
695 itemkey => p_itemKey,
696 aname => g_wf_attr_opp_name,
697 avalue => p_opp_name);
698
699 wf_engine.SetItemAttrText ( itemtype => p_itemtype,
700 itemkey => p_itemkey,
701 aname => g_wf_attr_customer_name,
702 avalue => p_customer_name);
703
704 wf_engine.SetItemAttrText ( itemtype => p_itemType,
705 itemkey => p_itemKey,
706 aname => g_wf_attr_notify_role,
707 avalue => l_role_Name);
708
709 wf_engine.SetItemAttrText ( itemtype => p_itemType,
710 itemkey => p_itemKey,
711 aname => g_wf_attr_partner_id,
712 avalue => p_partner_id);
713
714 wf_engine.SetItemAttrText ( itemtype => p_itemType,
715 itemkey => p_itemKey,
716 aname => g_wf_attr_partner_name,
717 avalue => p_partner_name);
718
719 l_send_respond_url := fnd_profile.value('PV_WORKFLOW_RESPOND_SELF_SERVICE_URL');
720
721 wf_engine.SetItemAttrText ( itemtype => p_itemType,
722 itemkey => p_itemKey,
723 aname => g_wf_attr_send_url,
724 avalue => l_send_respond_url);
725
726 wf_engine.StartProcess ( itemtype => p_itemtype,
727 itemkey => p_itemkey);
728
729 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
730
731 debug('wf item attr '|| wf_engine.GetItemAttrText ( itemtype => p_itemType, itemkey => p_itemKey,
732 aname => g_wf_attr_send_url));
733 debug('End of Workflow process');
734
735 END IF;
736
737 -- Debug Message
738
739 IF FND_API.To_Boolean ( p_commit ) THEN
740 COMMIT WORK;
741 END IF;
742
743 -- Standard call to get message count and if count is 1, get message info.
744 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
745 p_count => x_msg_count,
746 p_data => x_msg_data);
747 EXCEPTION
748
749 WHEN FND_API.G_EXC_ERROR THEN
750 x_return_status := FND_API.G_RET_STS_ERROR ;
751 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
752 p_count => x_msg_count,
753 p_data => x_msg_data);
754 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
756 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
757 p_count => x_msg_count,
758 p_data => x_msg_data);
759 WHEN OTHERS THEN
760 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
761 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
762 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
763 p_count => x_msg_count,
764 p_data => x_msg_data);
765 end StartWorkflow;
766
767
768
769
770 /********************************************************/
771 /* Notify the Channel Managers when an Opportunity is */
772 /* created by Partner or VAD. */
773 /********************************************************/
774 procedure Notify_CM_On_Create_Oppty (
775 p_api_version_number IN NUMBER,
776 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
777 p_commit IN VARCHAR2 := FND_API.G_FALSE,
778 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
779 p_oppty_header_rec IN AS_OPPORTUNITY_PUB.header_rec_type,
780 p_salesforce_id IN NUMBER,
781 p_relationship_type IN VARCHAR2,
782 p_party_relation_id IN NUMBER,
783 p_user_name IN VARCHAR2,
784 p_party_name IN VARCHAR2,
785 p_partner_type IN VARCHAR2,
786 x_return_status OUT NOCOPY VARCHAR2,
787 x_msg_count OUT NOCOPY NUMBER,
788 x_msg_data OUT NOCOPY VARCHAR2)
789 is
790
791 l_api_name CONSTANT VARCHAR2(30) := 'Notify_CM_On_Create_Oppty';
792 l_api_version_number CONSTANT NUMBER := 1.0;
793
794 l_access_code_update CONSTANT VARCHAR2(10) := 'UPDATE';
795
796 l_assign_seq NUMBER := 0; -- Assignment sequence is set to 1 as there will always be 1 partner
797 l_party_id NUMBER;
798 l_username fnd_user.user_name%type;
799 l_party_relation_id NUMBER;
800 l_partner_resource_id NUMBER;
801 l_lead_number VARCHAR2(30) := p_oppty_header_rec.lead_id;
802 l_lead_id NUMBER := p_oppty_header_rec.lead_id;
803 l_customer_id NUMBER := p_oppty_header_rec.customer_id;
804 l_customer_name VARCHAR2(500) := p_oppty_header_rec.customer_name;
805 l_opp_name VARCHAR2(500) := p_oppty_header_rec.description;
806 l_opp_amt NUMBER := NVL(p_oppty_header_rec.total_amount,0);
807 l_currency_code VARCHAR2(20);
808 l_opp_amt_curncy VARCHAR2(50);
809 l_address_id NUMBER := p_oppty_header_rec.address_id;
810 l_lead_assignment_id NUMBER;
811 l_access_id NUMBER;
812 l_bypass_cm_ok_flag VARCHAR2(1) := 'N';
813 l_entity VARCHAR2(20) := g_entity;
814 l_itemType CONSTANT VARCHAR2(30) := g_wf_itemtype_notify;
815 l_itemKey VARCHAR2(8);
816 l_wf_status_closed VARCHAR2(20) := g_wf_status_closed;
817 l_relationship_type VARCHAR2(30);
818 l_source_type VARCHAR2(20) := 'SALESTEAM'; --'OPPTYCR';
819 l_vendor_org_name VARCHAR2(50);
820 l_partner_name VARCHAR2(100);
821 l_r_status_active VARCHAR2(20) := g_r_status_active;
822 l_r_status_unassigned VARCHAR2(20) := g_r_status_unassigned;
823
824 l_r_notify_type VARCHAR2(20) := 'MATCHED_TO';
825 l_la_status_pt_created varchar2(20) := g_la_status_pt_created;
826
827 l_lead_workflow_rec pv_assign_util_pvt.lead_workflow_rec_type;
828 l_assignment_rec pv_assign_util_pvt.ASSIGNMENT_REC_TYPE;
829 l_rs_details_tbl pv_assign_util_pvt.resource_details_tbl_type := pv_assign_util_pvt.resource_details_tbl_type();
830 l_party_notify_rec_tbl pv_assignment_pvt.party_notify_rec_tbl_type;
831 l_sales_team_rec as_access_pub.sales_team_rec_type;
832 l_access_profile_rec as_access_pub.access_profile_rec_type;
833
834 l_new_resource_count NUMBER;
835 l_person_id NUMBER;
836 l_related_party_id NUMBER;
837 l_sales_grp_id_str VARCHAR2(200);
838 l_sales_group_id NUMBER;
839 l_category VARCHAR2(20);
840
841 cursor lc_get_group_id(pc_resource_id number) is
842 SELECT max(res.category), DECODE(COUNT(*),
843 0,
844 null,
845 1,
846 TO_CHAR(MAX(grp.group_id)),
847 FND_PROFILE.VALUE_SPECIFIC('ASF_DEFAULT_GROUP_ROLE',
848 MAX(RES.user_id))) salesgroup_id
849 FROM JTF_RS_GROUP_MEMBERS mem,
850 JTF_RS_ROLE_RELATIONS rrel,
851 JTF_RS_ROLES_B role,
852 JTF_RS_GROUP_USAGES u,
853 JTF_RS_GROUPS_B grp,
854 JTF_RS_RESOURCE_EXTNS RES
855 WHERE mem.group_member_id = rrel.role_resource_id AND
856 rrel.role_resource_type = 'RS_GROUP_MEMBER' AND
857 rrel.role_id = role.role_id AND
858 role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM') AND
859 mem.delete_flag <> 'Y' AND
860 rrel.delete_flag <> 'Y' AND
861 sysdate BETWEEN rrel.start_date_active AND
862 NVL(rrel.end_date_active, SYSDATE) AND
863 mem.group_id = u.group_id AND
864 u.usage in ('SALES','PRM') AND
865 mem.group_id = grp.group_id AND
866 sysdate BETWEEN grp.start_date_active AND
867 NVL(grp.end_date_active,sysdate) AND
868 mem.resource_id = RES.resource_id AND
869 RES.resource_id = pc_resource_id;
870
871 lc_cursor pv_assignment_pub.g_ref_cursor_type;
872
873 CURSOR lc_opportunity (pc_lead_id number) is
874 SELECT ld.customer_id, ld.address_id
875 , pt.party_name, ld.currency_code
876 FROM as_leads_all ld, hz_parties pt
877 WHERE ld.customer_id = pt.party_id
878 AND ld.lead_id = pc_lead_id;
879
880 CURSOR lc_get_opp_amt (pc_lead_id NUMBER) is
881 SELECT SUM(NVL(total_amount,0))
882 FROM as_lead_lines
883 WHERE lead_id = pc_lead_id;
884
885 /* CURSOR lc_address (pc_party_relation_id number) is
886 SELECT address_id, resource_id
887 FROM jtf_rs_resource_extns
888 WHERE category = 'PARTNER'
889 and sysdate between start_date_active and nvl(end_date_active,sysdate)
890 AND source_id = pc_party_relation_id; */
891
892 -- --------------------------------------------------------------------------
893 -- This cursor is a modification of the above cursor. The SQL now pulls
894 -- address_id directly from TCA.
895 -- --------------------------------------------------------------------------
896 CURSOR lc_address (pc_party_relation_id number) is
897 SELECT b.address_id, a.resource_id
898 FROM jtf_rs_resource_extns a,
899 as_party_addresses_v b,
900 pv_partner_profiles c
901 WHERE a.category = 'PARTNER'
902 AND sysdate between a.start_date_active and nvl(a.end_date_active,sysdate)
903 AND a.source_id = pc_party_relation_id
904 AND a.source_id = c.partner_id
905 AND c.partner_party_id = b.party_id
906 AND b.primary_address_flag = 'Y';
907
908 BEGIN
909
910 -- Standard call to check for call compatibility.
911 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
912 p_api_version_number,
913 l_api_name,
914 G_PKG_NAME)
915 THEN
916 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
917 END IF;
918
919 -- Initialize message list if p_init_msg_list is set to TRUE.
920 IF FND_API.to_Boolean( p_init_msg_list ) THEN
921 fnd_msg_pub.initialize;
922 END IF;
923
924 -- Debug Message
925 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
926 Debug('In ' || l_api_name);
927 END IF;
928
929 -- Initialize API return status to success
930 x_return_status := FND_API.G_RET_STS_SUCCESS;
931
932 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
933 Debug('Relationship Type ' || p_relationship_type);
934 END IF;
935
936 IF p_relationship_type is not null THEN
937 l_lead_workflow_rec.lead_id := l_lead_id;
938 l_lead_workflow_rec.entity := l_entity;
939 l_lead_workflow_rec.wf_item_type := l_itemtype;
940 l_lead_workflow_rec.wf_status := l_wf_status_closed;
941 l_lead_workflow_rec.bypass_cm_ok_flag := l_bypass_cm_ok_flag;
942 l_lead_workflow_rec.latest_routing_flag := 'Y';
943
944 IF p_relationship_type = 'PARTNER_OF' THEN
945 IF p_partner_type = 'PARTNER' THEN
946 l_lead_workflow_rec.routing_status := l_r_status_active;
947 l_lead_workflow_rec.routing_type := 'SINGLE';
948 ELSIF p_partner_type = 'VAD' THEN
949 l_lead_workflow_rec.routing_status := l_r_status_unassigned;
950 END IF;
951 END IF;
952
953 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
954 Debug('Routing Status ' || l_lead_workflow_rec.routing_status);
955 END IF;
956
957 pv_assign_util_pvt.Create_lead_workflow_row
958 ( p_api_version_number => 1.0
959 ,p_init_msg_list => FND_API.G_FALSE
960 ,p_commit => FND_API.G_FALSE
961 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
962 ,p_workflow_rec => l_lead_workflow_rec
963 ,x_ItemKey => l_itemKey
964 ,x_return_status => x_return_status
965 ,x_msg_count => x_msg_count
966 ,x_msg_data => x_msg_data
967 );
968
969 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
970 RAISE FND_API.G_EXC_ERROR;
971 END IF;
972
973 -- Get the Channel Manager information
974 pv_assign_util_pvt.get_partner_info
975 ( p_api_version_number => 1.0
976 ,p_init_msg_list => FND_API.G_FALSE
977 ,p_commit => FND_API.G_FALSE
978 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
979 ,p_mode => pv_assignment_pub.g_external_org
980 ,p_partner_id => p_party_relation_id
981 ,p_entity => l_entity
982 ,p_entity_id => l_lead_id
983 ,p_retrieve_mode => 'BOTH' -- change from CM to BOTH for 11.5.10
984 ,x_rs_details_tbl => l_rs_details_tbl
985 ,x_vad_id => l_related_party_id
986 ,x_return_status => x_return_status
987 ,x_msg_count => x_msg_count
988 ,x_msg_data => x_msg_data
989 );
990
991 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
992 RAISE FND_API.G_EXC_ERROR;
993 END IF;
994
995 -- If Channel manager found create assignment and notification record
996 If l_rs_details_tbl.count <= 0 THEN
997 FND_MESSAGE.set_name('PV', 'PV_EMPTY_ROLE');
998 FND_MSG_PUB.add;
999 RAISE FND_API.g_exc_unexpected_error;
1000 ELSE
1001 -- Insert into table PV_LEAD_ASSIGNMENTS with STATUS to PT_CREATED.
1002 -- Populate data with the following values.
1003 l_assignment_rec.lead_id := l_lead_id;
1004 l_assignment_rec.partner_id := p_party_relation_id;
1005 l_assignment_rec.source_type := l_source_type;
1006 l_assignment_rec.assign_sequence := l_assign_seq;
1007 l_assignment_rec.object_version_number := 0;
1008 l_assignment_rec.status_date := SYSDATE;
1009 l_assignment_rec.status := l_la_status_pt_created;
1010 l_assignment_rec.related_party_id := l_related_party_id;
1011 l_assignment_rec.partner_access_code := l_access_code_update;
1012 l_assignment_rec.wf_item_type := l_itemType;
1013 l_assignment_rec.wf_item_key := l_itemKey;
1014
1015 pv_assign_util_pvt.Create_lead_assignment_row
1016 ( p_api_version_number => 1.0
1017 ,p_init_msg_list => FND_API.G_FALSE
1018 ,p_commit => FND_API.G_FALSE
1019 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1020 ,p_assignment_rec => l_assignment_rec
1021 ,x_lead_assignment_id => l_lead_assignment_id
1022 ,x_return_status => x_return_status
1023 ,x_msg_count => x_msg_count
1024 ,x_msg_data => x_msg_data
1025 );
1026
1027 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1028 RAISE FND_API.G_EXC_ERROR;
1029 END IF;
1030
1031 -- Insert into table PV_PARTY_NOTIFICATIONS. Populate data with the
1032 -- following values. Extend the table for the number of channel managers
1033
1034 l_new_resource_count := l_rs_details_tbl.count;
1035
1036 l_party_notify_rec_tbl.WF_ITEM_TYPE.extend (l_rs_details_tbl.last);
1037 l_party_notify_rec_tbl.WF_ITEM_KEY.extend (l_rs_details_tbl.last);
1038 l_party_notify_rec_tbl.LEAD_ASSIGNMENT_ID.extend (l_rs_details_tbl.last);
1039 l_party_notify_rec_tbl.NOTIFICATION_TYPE.extend (l_rs_details_tbl.last);
1040 l_party_notify_rec_tbl.RESOURCE_ID.extend (l_rs_details_tbl.last);
1041 l_party_notify_rec_tbl.USER_ID.extend (l_rs_details_tbl.last);
1042 l_party_notify_rec_tbl.USER_NAME.extend (l_rs_details_tbl.last);
1043 l_party_notify_rec_tbl.RESOURCE_RESPONSE.extend (l_new_resource_count);
1044 l_party_notify_rec_tbl.RESPONSE_DATE.extend (l_new_resource_count);
1045 l_party_notify_rec_tbl.DECISION_MAKER_FLAG.extend(l_new_resource_count);
1046
1047 -- Loop through and populate the table
1048
1049 FOR i in 1 .. l_rs_details_tbl.count LOOP
1050 l_party_notify_rec_tbl.WF_ITEM_TYPE(i) := l_itemtype;
1051 l_party_notify_rec_tbl.WF_ITEM_KEY(i) := l_itemkey;
1052 l_party_notify_rec_tbl.LEAD_ASSIGNMENT_ID(i) := l_lead_assignment_id;
1053 l_party_notify_rec_tbl.NOTIFICATION_TYPE(i) := l_rs_details_tbl(i).notification_type;
1054 l_party_notify_rec_tbl.RESOURCE_ID(i) := l_rs_details_tbl(i).resource_id;
1055 l_party_notify_rec_tbl.USER_ID(i) := l_rs_details_tbl(i).user_id;
1056 l_party_notify_rec_tbl.USER_NAME(i) := l_rs_details_tbl(i).user_name;
1057 l_party_notify_rec_tbl.DECISION_MAKER_FLAG(i):= 'Y';
1058
1059 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1060
1061 Debug( 'Assignment ID: ' || l_lead_assignment_id ||
1062 '. Notification type: ' || l_party_notify_rec_tbl.NOTIFICATION_TYPE(i) ||
1063 '. Username: ' || l_party_notify_rec_tbl.USER_NAME(i));
1064
1065 END IF;
1066 END LOOP;
1067
1068 -- Insert in bulk for all the channel manager by calling the procedure
1069 pv_assignment_pvt.bulk_cr_party_notification
1070 ( p_api_version_number => 1.0
1071 ,p_init_msg_list => FND_API.G_FALSE
1072 ,p_commit => FND_API.G_FALSE
1073 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1074 ,p_party_notify_Rec_tbl => l_party_notify_rec_tbl
1075 ,x_return_status => x_return_status
1076 ,x_msg_count => x_msg_count
1077 ,x_msg_data => x_msg_data);
1078
1079 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1080 RAISE FND_API.G_EXC_ERROR;
1081 END IF;
1082
1083 -- For each Channel Manager/partner contact in l_rs_details_tbl call the procedure to
1084 -- update the Sales team. This will insert rows in as_access_all.write
1085 -- access records for the channel managers/contact, partners are later
1086
1087 FOR i in l_rs_details_tbl.first .. l_rs_details_tbl.last LOOP
1088
1089 -- skip if resource already on salesteam
1090 for c_check in (select 1 from as_accesses_all where
1091 salesforce_id <> l_rs_details_tbl(i).resource_id and lead_id = l_lead_id)
1092 loop
1093 -- The returned table has all CM from Vendor and VAD.
1094 -- Since VAD CM does not have person id, we need to populate partner_cont_party_id
1095
1096 if l_rs_details_tbl(i).person_type = pv_assignment_pub.g_resource_employee then
1097 l_sales_team_rec.partner_cont_party_id := null;
1098 l_sales_team_rec.person_id := l_rs_details_tbl(i).person_id;
1099 else
1100 l_sales_team_rec.person_id := null;
1101 l_sales_team_rec.partner_cont_party_id := l_rs_details_tbl(i).person_id;
1102 end if;
1103
1104 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1105 Debug( 'Partner Contact Party ID '||l_sales_team_rec.partner_cont_party_id);
1106 END IF;
1107
1108 l_sales_team_rec.lead_id := l_lead_id;
1109 l_sales_team_rec.customer_id := l_customer_id;
1110 l_sales_team_rec.freeze_flag := 'Y';
1111 l_sales_team_rec.partner_customer_id := null;
1112 l_sales_team_rec.salesforce_id := l_rs_details_tbl(i).resource_id;
1113 l_sales_team_rec.address_id := l_address_id;
1114 l_sales_team_rec.team_leader_flag := 'Y'; --Added per Suresh
1115
1116 l_access_profile_rec := null;
1117
1118 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1119 Debug( 'Lead ID '||l_lead_id ||' Customer ID '||l_customer_id||'salesforce_id'||
1120 l_rs_details_tbl(i).resource_id);
1121 END IF;
1122
1123 open lc_get_group_id ( pc_resource_id => l_rs_details_tbl(i).resource_id);
1124 fetch lc_get_group_id into l_category, l_sales_grp_id_str;
1125 close lc_get_group_id;
1126
1127 begin
1128
1129 if instr(l_sales_grp_id_str, '(') > 0 then
1130 l_sales_group_id := to_number(substr(l_sales_grp_id_str, 1, instr(l_sales_grp_id_str, '(') - 1));
1131 else
1132 l_sales_group_id := to_number(l_sales_grp_id_str);
1133 end if;
1134
1135 exception
1136 when others then
1137 if sqlcode = -6502 then -- string is not a number
1138 l_sales_group_id := null;
1139 else
1140 raise;
1141 end if;
1142 end;
1143
1144 if l_sales_group_id is NULL then
1145
1146 fnd_message.SET_NAME ('PV', 'PV_DEBUG_MESSAGE');
1147 fnd_message.SET_TOKEN ('TEXT' , 'No Default Sales Group for resource id '||
1148 l_rs_details_tbl(i).resource_id);
1149 fnd_msg_pub.ADD;
1150
1151 fnd_message.SET_NAME ('PV', 'PV_DEBUG_MESSAGE');
1152 fnd_message.SET_TOKEN ('TEXT' , 'Not adding to oppty salesteam: '||l_rs_details_tbl(i).user_name);
1153 fnd_msg_pub.ADD;
1154
1155 else
1156
1157 l_sales_team_rec.sales_group_id := l_sales_group_id;
1158 as_access_pub.Create_SalesTeam
1159 (p_api_version_number => 2 -- API Version has been changed
1160 ,p_init_msg_list => FND_API.G_FALSE
1161 ,p_commit => FND_API.G_FALSE
1162 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1163 ,p_access_profile_rec => l_access_profile_rec
1164 ,p_check_access_flag => 'N'
1165 ,p_admin_flag => 'N'
1166 ,p_admin_group_id => null
1167 ,p_identity_salesforce_id => p_salesforce_id
1168 ,p_sales_team_rec => l_sales_team_rec
1169 ,x_return_status => x_return_status
1170 ,x_msg_count => x_msg_count
1171 ,x_msg_data => x_msg_data
1172 ,x_access_id => l_access_id);
1173
1174 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1175 RAISE FND_API.G_EXC_ERROR;
1176 END IF;
1177
1178 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1179 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1180 fnd_message.Set_Token('TEXT', 'After 1st Create Sales team');
1181 fnd_msg_pub.Add;
1182 END IF;
1183
1184 end if;
1185
1186 END LOOP;
1187 END LOOP;
1188
1189 -- Add the partner in the sales team. The first VAD_OF partner is
1190 -- picked up and added in the sales team to give access to all the
1191 -- contacts of the same partner in future.
1192
1193 OPEN lc_address (pc_party_relation_id => p_party_relation_id);
1194 FETCH lc_address
1195 INTO l_address_id, l_partner_resource_id;
1196 CLOSE lc_address;
1197
1198 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1199 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1200 fnd_message.Set_Token('TEXT', 'Salesgroup ID'||l_sales_team_rec.sales_group_id);
1201 fnd_msg_pub.Add;
1202 END IF;
1203
1204 l_sales_team_rec.sales_group_id := null;
1205 l_sales_team_rec.person_id := null;
1206 l_sales_team_rec.lead_id := l_lead_id;
1207 l_sales_team_rec.customer_id := l_customer_id;
1208 l_sales_team_rec.freeze_flag := 'Y';
1209 l_sales_team_rec.partner_cont_party_id := null;
1210 l_sales_team_rec.partner_customer_id:= p_party_relation_id;
1211 l_sales_team_rec.salesforce_id := l_partner_resource_id;
1212 l_sales_team_rec.partner_address_id := l_address_id;
1213 l_sales_team_rec.team_leader_flag := 'N'; --Added per Suresh
1214
1215 as_access_pub.Create_SalesTeam
1216 (p_api_version_number => 2 -- API Version has been changed
1217 ,p_init_msg_list => FND_API.G_FALSE
1218 ,p_commit => FND_API.G_FALSE
1219 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1220 ,p_access_profile_rec => l_access_profile_rec
1221 ,p_check_access_flag => 'N'
1222 ,p_admin_flag => 'N'
1223 ,p_admin_group_id => null
1224 ,p_identity_salesforce_id => p_salesforce_id
1225 ,p_sales_team_rec => l_sales_team_rec
1226 ,x_return_status => x_return_status
1227 ,x_msg_count => x_msg_count
1228 ,x_msg_data => x_msg_data
1229 ,x_access_id => l_access_id);
1230
1231 -- Check the x_return_status. If its not successful throw an exception.
1232 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1233 RAISE FND_API.G_EXC_ERROR;
1234 END IF;
1235
1236 -- Add the VAD id when the opportunity is created by Indirectly Managed Partner
1237 -- Adding VAD organization will allow to add salesteam from that organization
1238 -- from UI later on.
1239 -- added validation to check if its created by IMP and use l_related_party_id
1240 -- for 11.5.10, we are not adding VAD when IMP creates oppty (l_related_party_id
1241 -- will always be null)
1242
1243 IF l_related_party_id IS NOT NULL THEN
1244
1245 OPEN lc_address (pc_party_relation_id => l_related_party_id);
1246 FETCH lc_address INTO l_address_id, l_partner_resource_id;
1247 CLOSE lc_address;
1248
1249 l_sales_team_rec.person_id := null;
1250 l_sales_team_rec.lead_id := l_lead_id;
1251 l_sales_team_rec.customer_id := l_customer_id;
1252 l_sales_team_rec.freeze_flag := 'Y';
1253 l_sales_team_rec.partner_customer_id := p_party_relation_id;
1254 l_sales_team_rec.salesforce_id := l_partner_resource_id;
1255 l_sales_team_rec.address_id := l_address_id;
1256 l_sales_team_rec.team_leader_flag := 'Y'; --Added per Suresh
1257
1258 as_access_pub.Create_SalesTeam
1259 (p_api_version_number => 2 -- API Version has been changed
1260 ,p_init_msg_list => FND_API.G_FALSE
1261 ,p_commit => FND_API.G_FALSE
1262 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1263 ,p_access_profile_rec => l_access_profile_rec
1264 ,p_check_access_flag => 'N'
1265 ,p_admin_flag => 'N'
1266 ,p_admin_group_id => null
1267 ,p_identity_salesforce_id => p_salesforce_id
1268 ,p_sales_team_rec => l_sales_team_rec
1269 ,x_return_status => x_return_status
1270 ,x_msg_count => x_msg_count
1271 ,x_msg_data => x_msg_data
1272 ,x_access_id => l_access_id);
1273
1274 -- Check the x_return_status. If its not successful throw an exception.
1275 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1276 RAISE FND_API.G_EXC_ERROR;
1277 END IF;
1278
1279 END IF;
1280
1281 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1282 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1283 fnd_message.Set_Token('TEXT', 'After 2nd Create Sales team');
1284 fnd_msg_pub.Add;
1285 END IF;
1286
1287 -- Update AS_LEADS_ALL set AUTO_ASSIGNMENT_TYPE in AS_LEADS_ALL to PRM for the lead_id.
1288
1289 IF p_relationship_type = 'PARTNER_OF' THEN
1290
1291 IF p_partner_type = 'PARTNER' THEN
1292
1293 Update AS_LEADS_ALL
1294 SET PRM_ASSIGNMENT_TYPE = 'SINGLE',
1295 AUTO_ASSIGNMENT_TYPE = 'PRM'
1296 WHERE lead_id = l_lead_id;
1297
1298 ELSIF p_partner_type = 'VAD' THEN -- 'VAD_OF'
1299
1300 Update AS_LEADS_ALL
1301 SET AUTO_ASSIGNMENT_TYPE = 'PRM'
1302 WHERE lead_id = l_lead_id;
1303
1304 END IF;
1305
1306 END IF;
1307
1308 OPEN lc_opportunity(l_lead_id);
1309 FETCH lc_opportunity INTO l_customer_id, l_address_id, l_customer_name, l_currency_code;
1310 CLOSE lc_opportunity;
1311
1312 OPEN lc_get_opp_amt(l_lead_id);
1313 FETCH lc_get_opp_amt INTO l_opp_amt;
1314 CLOSE lc_get_opp_amt;
1315
1316 l_opp_amt_curncy := nvl(l_opp_amt,0) ||' '||l_currency_code;
1317
1318 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1319 debug('before starting workflow ...............');
1320 END IF;
1321
1322 -- When all the table are updated start the workflow .
1323 StartWorkflow
1324 ( p_api_version_number => 1.0,
1325 p_init_msg_list => FND_API.G_FALSE,
1326 p_commit => FND_API.G_FALSE,
1327 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1328 p_itemKey => l_itemKey,
1329 p_itemType => l_itemType,
1330 p_partner_id => p_party_relation_id,
1331 p_partner_name => p_party_name,
1332 p_lead_id => l_lead_id,
1333 p_opp_name => l_opp_name,
1334 p_lead_number => l_lead_number,
1335 p_customer_id => l_customer_id,
1336 p_address_id => l_address_id,
1337 p_customer_name => l_customer_name,
1338 p_creating_username => p_user_name,
1339 p_bypass_cm_ok_flag => l_bypass_cm_ok_flag,
1340 x_return_status => x_return_status,
1341 x_msg_count => x_msg_count,
1342 x_msg_data => x_msg_data);
1343
1344 -- Check the x_return_status. If its not successful throw an exception.
1345 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1346 RAISE FND_API.G_EXC_ERROR;
1347 END IF;
1348
1349 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1350 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1351 fnd_message.Set_Token('TEXT', 'After Workflow is started');
1352 fnd_msg_pub.Add;
1353 END IF;
1354
1355 -- Call the following procedure to see whether workflow was able to send notification successfully.
1356 PV_ASSIGN_UTIL_PVT.checkforErrors
1357 (p_api_version_number => 1.0
1358 ,p_init_msg_list => FND_API.G_FALSE
1359 ,p_commit => FND_API.G_FALSE
1360 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1361 ,p_itemtype => l_itemType
1362 ,p_itemkey => l_itemKey
1363 ,x_msg_count => x_msg_count
1364 ,x_msg_data => x_msg_data
1365 ,x_return_status => x_return_status);
1366
1367 -- Check the x_return_status. If its not successful throw an exception.
1368 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1369 raise FND_API.G_EXC_ERROR;
1370 end if;
1371 END IF; -- No Channel Manager found
1372 END IF;
1373
1374 EXCEPTION
1375
1376 WHEN FND_API.G_EXC_ERROR THEN
1377 x_return_status := FND_API.G_RET_STS_ERROR ;
1378 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1379 p_count => x_msg_count,
1380 p_data => x_msg_data);
1381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1383 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1384 p_count => x_msg_count,
1385 p_data => x_msg_data);
1386 WHEN OTHERS THEN
1387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1388 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1389 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1390 p_count => x_msg_count,
1391 p_data => x_msg_data);
1392 END Notify_CM_On_Create_Oppty;
1393
1394
1395 Procedure Set_Oppty_Amt_Wf
1396 ( itemtype in varchar2,
1397 itemkey in varchar2,
1398 actid in number,
1399 funcmode in varchar2,
1400 resultout in OUT NOCOPY varchar2)
1401 is
1402
1403 l_api_name CONSTANT VARCHAR2(30) := 'SET_OPPTY_AMT_WF';
1404 l_api_version_number CONSTANT NUMBER := 1.0;
1405
1406 l_resultout varchar2(50);
1407 l_msg_count number;
1408 l_msg_data varchar2(2000);
1409 l_lead_id number;
1410 l_opp_amt number;
1411 l_currency_code varchar2(100);
1412 l_amt_cny varchar2(100);
1413
1414 CURSOR lc_opp_amt(pc_lead_id NUMBER)
1415 IS
1416 SELECT nvl(total_amount,0), currency_code
1417 FROM as_leads_all
1418 WHERE lead_id = pc_lead_id;
1419
1420 BEGIN
1421
1422 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1423 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1424 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
1425 fnd_msg_pub.Add;
1426 END IF;
1427
1428 IF (funcmode = 'RUN') then
1429
1430 l_lead_id := wf_engine.GetItemAttrText ( itemtype => itemtype,
1431 itemkey => itemkey,
1432 aname => g_wf_attr_lead_id);
1433 OPEN lc_opp_amt(l_lead_id);
1434 FETCH lc_opp_amt INTO l_opp_amt, l_currency_code;
1435 CLOSE lc_opp_amt;
1436
1437 l_amt_cny := l_opp_amt||' '||l_currency_code;
1438
1439 wf_engine.SetItemAttrText ( itemtype => itemtype,
1440 itemkey => itemkey,
1441 aname => g_wf_attr_opp_amt,
1442 avalue => l_amt_cny);
1443
1444 l_resultout := 'COMPLETE';
1445
1446 ELSIF (funcmode = 'CANCEL') then
1447 l_resultout := 'COMPLETE';
1448
1449 ELSIF (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
1450 l_resultout := 'COMPLETE';
1451
1452 ELSIF (funcmode = 'TIMEOUT') then
1453 l_resultout := 'COMPLETE';
1454 END IF;
1455 resultout := l_resultout;
1456 EXCEPTION
1457 WHEN OTHERS THEN
1458
1459 fnd_msg_pub.Count_And_Get(
1460 p_encoded => FND_API.G_TRUE
1461 ,p_count => l_msg_count
1462 ,p_data => l_msg_data);
1463
1464 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
1465 raise;
1466
1467
1468 END;
1469 -- Vansub
1470 -- Rivendell
1471 -- Notify_on_Update_Oppty_from_JBES is called from Java Business Subscription when an opportunity is updated
1472 -- in order to avoid generating rosetta wrapper and for the easy debug
1473 -- Rivendell
1474 procedure NOTIFY_ON_UPDATE_OPPTY_JBES (
1475 p_api_version_number IN NUMBER,
1476 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1477 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1478 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1479 p_lead_id IN NUMBER,
1480 p_status IN VARCHAR2,
1481 p_lead_name IN VARCHAR2,
1482 p_customer_id IN NUMBER,
1483 p_total_amount IN NUMBER,
1484 p_salesforce_id IN NUMBER,
1485 x_return_status OUT NOCOPY VARCHAR2,
1486 x_msg_count OUT NOCOPY NUMBER,
1487 x_msg_data OUT NOCOPY VARCHAR2)
1488 IS
1489 l_api_name CONSTANT VARCHAR2(100) := 'NOTIFY_ON_UPDATE_OPPTY_JBES';
1490 l_api_version_number CONSTANT NUMBER := 1.0;
1491
1492 l_opportunity_rec AS_OPPORTUNITY_PUB.header_rec_type;
1493
1494
1495 CURSOR get_customer_name(pc_party_id NUMBER)
1496 IS
1497 SELECT party_name
1498 FROM hz_parties
1499 WHERE party_id = pc_party_id;
1500
1501 BEGIN
1502 -- Standard call to check for call compatibility.
1503 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
1504 p_api_version_number,
1505 l_api_name,
1506 G_PKG_NAME)
1507 THEN
1508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1509 END IF;
1510
1511 -- Initialize message list if p_init_msg_list is set to TRUE.
1512 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1513 fnd_msg_pub.initialize;
1514 END IF;
1515
1516 -- Debug Message
1517 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1518 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1519 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
1520 fnd_msg_pub.Add;
1521 END IF;
1522
1523 -- Initialize API return status to success
1524 x_return_status := FND_API.G_RET_STS_SUCCESS;
1525
1526 OPEN get_customer_name(p_customer_id);
1527 FETCH get_customer_name INTO l_opportunity_rec.customer_name;
1528 CLOSE get_customer_name;
1529
1530 l_opportunity_rec.lead_id := p_lead_id;
1531 l_opportunity_rec.status_code := p_status;
1532 l_opportunity_rec.lead_number := p_lead_id;
1533 l_opportunity_rec.description := p_lead_name;
1534 l_opportunity_rec.total_amount := p_total_amount;
1535
1536
1537
1538 Notify_Party_On_Update_Oppty (
1539 p_api_version_number => l_api_version_number,
1540 p_init_msg_list => p_init_msg_list,
1541 p_commit => p_commit,
1542 p_validation_level => p_validation_level,
1543 p_oppty_header_rec => l_opportunity_rec,
1544 p_salesforce_id => p_salesforce_id,
1545 x_return_status => x_return_status,
1546 x_msg_count => x_msg_count,
1547 x_msg_data => x_msg_data);
1548
1549 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1550 RAISE FND_API.G_EXC_ERROR;
1551 END IF;
1552
1553 -- Standard call to get message count and if count is 1, get message info.
1554 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1555 p_count => x_msg_count,
1556 p_data => x_msg_data);
1557
1558 EXCEPTION
1559
1560 WHEN FND_API.G_EXC_ERROR THEN
1561
1562 x_return_status := FND_API.G_RET_STS_ERROR ;
1563 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1564 p_count => x_msg_count,
1565 p_data => x_msg_data);
1566
1567 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1568
1569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1570 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1571 p_count => x_msg_count,
1572 p_data => x_msg_data);
1573
1574 WHEN OTHERS THEN
1575 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1576 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1577 fnd_message.Set_Token('TEXT', sqlcode||sqlerrm);
1578 fnd_msg_pub.Add;
1579 END IF;
1580
1581 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1582
1583 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1584 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
1585 p_count => x_msg_count,
1586 p_data => x_msg_data);
1587
1588 END NOTIFY_ON_UPDATE_OPPTY_JBES;
1589
1590 -- Opportunity modify User Hook.
1591 procedure Notify_Party_On_Update_Oppty (
1592 p_api_version_number IN NUMBER,
1593 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1594 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1595 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1596 p_oppty_header_rec IN AS_OPPORTUNITY_PUB.header_rec_type,
1597 p_salesforce_id IN NUMBER,
1598 x_return_status OUT NOCOPY VARCHAR2,
1599 x_msg_count OUT NOCOPY NUMBER,
1600 x_msg_data OUT NOCOPY VARCHAR2) is
1601
1602 l_api_name CONSTANT VARCHAR2(100) := 'Notify_Party_On_Update_Oppty';
1603 l_api_version_number CONSTANT NUMBER := 1.0;
1604
1605 l_lead_id p_oppty_header_rec.lead_id%type := p_oppty_header_rec.lead_id;
1606 l_status p_oppty_header_rec.status_code%type := p_oppty_header_rec.status_code;
1607 l_lead_number p_oppty_header_rec.lead_number%type := p_oppty_header_rec.lead_id;
1608
1609 l_customer_name p_oppty_header_rec.customer_name%type := p_oppty_header_rec.customer_name;
1610 l_opp_name p_oppty_header_rec.description%type := p_oppty_header_rec.description;
1611 l_opp_amt NUMBER := NVL(p_oppty_header_rec.total_amount,0);
1612 l_currency_code VARCHAR2(30);
1613 l_opp_amt_curncy VARCHAR2(30);
1614 l_salesforceid NUMBER := p_salesforce_id;
1615 l_assignment_ids NUMBER;
1616 l_workflow_id NUMBER;
1617 l_user_id NUMBER;
1618 l_user_name fnd_user.user_name%type;
1619 l_creating_username fnd_user.user_name%type;
1620 l_resource_id jtf_rs_resource_extns.resource_id%type;
1621 l_category jtf_rs_resource_extns.category%type;
1622 l_party_id jtf_rs_resource_extns.source_id%type;
1623 l_party_name jtf_rs_resource_extns.source_business_grp_name%type;
1624 l_vendor_org_name jtf_rs_resource_extns.source_business_grp_name%type;
1625 l_customer_id as_leads_all.customer_id%TYPE;
1626 l_address_id as_leads_all.address_id%TYPE;
1627 l_wf_item_type VARCHAR2(20);
1628 l_wf_item_key VARCHAR2(20);
1629 l_partner_id NUMBER;
1630 l_partner_name VARCHAR2(360);
1631 l_partner_names VARCHAR2(2000) := NULL;
1632 l_from_status VARCHAR2(100);
1633 l_to_status VARCHAR2(100);
1634 l_status_from VARCHAR2(100);
1635 l_status_to VARCHAR2(100);
1636 l_status_code VARCHAR2(100);
1637 l_message_name VARCHAR2(30);
1638
1639 l_db_status VARCHAR2(100);
1640 l_entity VARCHAR2(20) := 'OPPORTUNITY';
1641
1642 -- Notification Flags
1643 l_notify_pt_flag CHAR(1) := 'N';
1644 l_notify_am_flag CHAR(1) := 'N';
1645 l_notify_cm_flag CHAR(1) := 'N';
1646 l_notify_others_flag CHAR(1) := 'N';
1647
1648 l_user_id_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1649 l_user_name_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1650 l_resource_id_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1651 l_user_type_tbl JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100();
1652
1653 count_row NUMBER := 1;
1654
1655 l_user_type VARCHAR2(20);
1656
1657 CURSOR lc_users(pc_lead_id NUMBER,
1658 pc_notify_cm_flag VARCHAR2,
1659 pc_notify_am_flag VARCHAR2,
1660 pc_notify_pt_flag VARCHAR2,
1661 pc_notify_ot_flag VARCHAR2)
1662 IS
1663 SELECT pn.user_id, pn.resource_id, fu.user_name ,
1664 decode(pn.notification_type, 'MATCHED_TO', 'CM', 'PT') user_type,
1665 decode(pn.notification_type, 'MATCHED_TO', 0, pa.partner_id) partner_id
1666 FROM pv_lead_workflows pw,
1667 pv_lead_assignments pa,
1668 pv_party_notifications pn,
1669 jtf_rs_resource_extns extn,
1670 as_accesses_all asac,
1671 fnd_user fu
1672 WHERE pw.wf_item_type = pa.wf_item_type
1673 and pw.wf_item_key = pa.wf_item_key
1674 AND pa.lead_assignment_id = pn.lead_assignment_id
1675 AND pw.routing_status = 'ACTIVE'
1676 AND pw.latest_routing_flag = 'Y'
1677 AND pw.lead_id = pc_lead_id
1678 AND ((pn.notification_type = 'MATCHED_TO' and 'Y' = pc_notify_cm_flag)
1679 or (pn.notification_type = 'OFFERED_TO' and 'Y' = pc_notify_pt_flag))
1680 AND pa.status IN ( 'PT_CREATED', 'PT_APPROVED' , 'CM_APP_FOR_PT' )
1681 AND asac.salesforce_id = pn.resource_id
1682 AND asac.lead_id = pw.lead_id
1683 AND asac.sales_lead_id IS NULL
1684 AND asac.customer_id IS NOT NULL
1685 AND asac.salesforce_id = extn.resource_id
1686 AND extn.user_id = fu.user_id
1687 AND sysdate between extn.start_date_active and nvl(extn.end_date_active,sysdate)
1688 AND sysdate between fu.start_date and nvl(fu.end_date,sysdate)
1689 UNION
1690 SELECT js.user_id, js.resource_id, fu.user_name,
1691 decode(pw.created_by - js.user_id,0,'AM','OTHER') user_type, 0 partner_id
1692 FROM as_accesses_all ac, jtf_rs_resource_extns js, fnd_user fu, pv_lead_workflows pw
1693 WHERE (('Y' = pc_notify_ot_flag and pw.created_by <> js.user_id)
1694 or ('Y' = pc_notify_am_flag and pw.created_by = js.user_id))
1695 AND ac.lead_id = pc_lead_id
1696 and ac.lead_id = pw.lead_id
1697 and pw.entity = 'OPPORTUNITY'
1698 AND pw.latest_routing_flag = 'Y'
1699 AND ac.salesforce_id = js.resource_id
1700 AND js.user_id = fu.user_id
1701 AND ac.sales_lead_id IS NULL
1702 AND ac.customer_id IS NOT NULL
1703 and sysdate between js.start_date_active and nvl(js.end_date_active,sysdate)
1704 AND sysdate between fu.start_date and nvl(fu.end_date,sysdate)
1705 AND NOT EXISTS
1706 (SELECT 1
1707 FROM pv_lead_assignments pl, pv_party_notifications pv
1708 WHERE pl.lead_assignment_id = pv.lead_assignment_id
1709 AND pv.resource_id = ac.salesforce_id
1710 and pv.user_id <> pw.created_by
1711 AND pl.wf_item_type = pw.wf_item_type
1712 AND pl.wf_item_key = pw.wf_item_key)
1713 ORDER BY 4;
1714
1715 CURSOR lc_assign_ids (pc_lead_id number) is
1716 SELECT lead_workflow_id, wf_item_key, wf_item_type
1717 FROM pv_lead_workflows pw
1718 WHERE pw.routing_status = 'ACTIVE'
1719 AND pw.latest_routing_flag = 'Y'
1720 AND pw.lead_id = pc_lead_id;
1721
1722 CURSOR lc_status_notify (pc_status_code varchar2) is
1723 SELECT nvl(notify_pt_flag,'N')
1724 ,nvl(notify_am_flag,'N')
1725 ,nvl(notify_cm_flag,'N')
1726 ,nvl(notify_others_flag,'N')
1727 FROM pv_status_notifications
1728 WHERE enabled_flag = 'Y'
1729 AND status_type = 'OPPORTUNITY'
1730 AND status_code = pc_status_code;
1731
1732 CURSOR lc_opportunity (pc_lead_id number) is
1733 SELECT ld.customer_id, ld.address_id, pt.party_name,
1734 nvl(ld.total_amount,0),ld.currency_code, ld.description
1735 FROM as_leads_all ld, hz_parties pt
1736 WHERE ld.customer_id = pt.party_id
1737 AND ld.lead_id = pc_lead_id;
1738
1739 CURSOR lc_get_pt_emp_cat(pc_salesforce_id NUMBER) IS
1740 SELECT js.source_id, js.category, js.source_business_grp_name, fu.user_name
1741 FROM fnd_user fu, jtf_rs_resource_extns js
1742 WHERE fu.user_id = js.user_id
1743 AND js.resource_id = pc_salesforce_id;
1744
1745 CURSOR lc_get_pt_ven_name(pc_party_id NUMBER) IS
1746 SELECT VENDOR.party_name
1747 FROM hz_parties VENDOR,
1748 hz_relationships PCONTACT,
1749 pv_partner_profiles PVPP
1750 WHERE PCONTACT.party_id = pc_party_id
1751 AND PCONTACT.subject_table_name = 'HZ_PARTIES'
1752 AND PCONTACT.object_table_name = 'HZ_PARTIES'
1753 AND PCONTACT.RELATIONSHIP_TYPE = 'EMPLOYMENT'
1754 AND PCONTACT.directional_flag = 'F'
1755 AND PCONTACT.STATUS = 'A'
1756 AND PCONTACT.start_date <= SYSDATE
1757 AND nvl(PCONTACT.end_date, SYSDATE) >= SYSDATE
1758 AND PVPP.partner_party_id = PCONTACT.object_id
1759 AND VENDOR.party_id = PVPP.partner_party_id
1760 AND VENDOR.PARTY_TYPE = 'ORGANIZATION'
1761 AND VENDOR.status = 'A'
1762 AND PVPP.SALES_PARTNER_FLAG = 'Y';
1763
1764 l_partner_id_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
1765
1766 cursor lc_get_pt_org_name(pc_item_type varchar2, pc_item_key varchar2) is
1767 select pt.party_name, pvas.partner_id
1768 from hz_parties pt,
1769 pv_partner_profiles pvpp,
1770 pv_lead_assignments pvas
1771 where pvas.wf_item_type = pc_item_type
1772 and pvas.wf_item_key = pc_item_key
1773 and pvas.partner_id = pvpp.partner_id
1774 and pvpp.partner_party_id = pt.party_id;
1775
1776 cursor lc_get_meaning(pc_status_code VARCHAR2 ,pc_lead_id NUMBER) IS
1777 select decode(a.status_code, t.status, a.meaning, a.status_code),
1778 decode(a.status_code, pc_status_code, a.meaning, a.status_code),
1779 a.status_code, a.win_loss_indicator
1780 from as_statuses_vl a,
1781 (select status from as_leads_all
1782 where lead_id = pc_lead_id) t
1783 where a.enabled_flag = 'Y'
1784 and a.opp_flag = 'Y'
1785 and a.status_code in (t.status, pc_status_code);
1786
1787 l_win_loss_indicator varchar2(1);
1788 l_curr_win_loss_flag varchar2(1);
1789 l_log_params_tbl pvx_utility_pvt.log_params_tbl_type;
1790
1791
1792 BEGIN
1793
1794 -- Standard call to check for call compatibility.
1795 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
1796 p_api_version_number,
1797 l_api_name,
1798 G_PKG_NAME)
1799 THEN
1800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1801 END IF;
1802
1803 -- Initialize message list if p_init_msg_list is set to TRUE.
1804 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1805 fnd_msg_pub.initialize;
1806 END IF;
1807
1808 -- Debug Message
1809 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1810 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1811 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
1812 fnd_msg_pub.Add;
1813 END IF;
1814
1815 -- Initialize API return status to success
1816 x_return_status := FND_API.G_RET_STS_SUCCESS;
1817
1818 OPEN lc_assign_ids (pc_lead_id => l_lead_id);
1819 FETCH lc_assign_ids INTO l_workflow_id,l_wf_item_key, l_wf_item_type;
1820
1821 IF lc_assign_ids%FOUND THEN
1822
1823 open lc_get_pt_emp_cat(p_salesforce_id);
1824 fetch lc_get_pt_emp_cat into l_party_id, l_category,
1825 l_party_name, l_creating_username;
1826 close lc_get_pt_emp_cat;
1827
1828 IF l_category = pv_assignment_pub.g_resource_employee THEN
1829
1830 l_vendor_org_name := l_party_name;
1831
1832 ELSIF l_category = pv_assignment_pub.g_resource_party THEN
1833
1834 open lc_get_pt_ven_name(l_party_id);
1835 fetch lc_get_pt_ven_name into l_vendor_org_name;
1836 close lc_get_pt_ven_name;
1837
1838 END IF;
1839
1840 -- Debug Message
1841 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1842 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1843 fnd_message.Set_Token('TEXT', 'After Found : ' || l_creating_username || ' salesforce id : ' || p_salesforce_id);
1844 fnd_msg_pub.Add;
1845 END IF;
1846
1847 -- Get the Opportunity Status from header and table
1848 -- IF Status is changed
1849 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1850 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1851 fnd_message.Set_Token('TEXT', 'Status '||l_status);
1852 fnd_msg_pub.Add;
1853 END IF;
1854
1855 IF l_status is null THEN
1856 return;
1857 END IF;
1858
1859 open lc_get_meaning(l_status,l_lead_id);
1860 loop
1861 fetch lc_get_meaning into l_status_from, l_status_to, l_db_status, l_curr_win_loss_flag;
1862 exit when lc_get_meaning%notfound;
1863
1864 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1865 Debug( 'l_status_to '||l_status_to ||' l_status_from '||l_status_from ||' DB Status '||l_db_status);
1866 END IF;
1867
1868 IF l_db_status = l_status_from THEN
1869 l_to_status := l_status_to;
1870 l_win_loss_indicator := l_curr_win_loss_flag;
1871 ELSIF l_db_status = l_status_to THEN
1872 l_from_status := l_status_from;
1873 END IF;
1874
1875 end loop;
1876 close lc_get_meaning;
1877
1878 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1879 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1880 fnd_message.Set_Token('TEXT', 'STATUS before : ' || l_from_status || ', After : ' || l_to_status);
1881 fnd_msg_pub.Add;
1882 END IF;
1883
1884 IF l_from_status IS NULL OR l_to_status IS NULL THEN
1885 return;
1886 END IF;
1887
1888 OPEN lc_get_pt_org_name(l_wf_item_type, l_wf_item_key);
1889 LOOP
1890 FETCH lc_get_pt_org_name INTO l_partner_name, l_partner_id;
1891 EXIT WHEN lc_get_pt_org_name%NOTFOUND;
1892
1893 l_partner_id_tbl.extend;
1894 l_partner_id_tbl(l_partner_id_tbl.count) := l_partner_id;
1895
1896 IF l_partner_names is NULL THEN
1897 l_partner_names := l_partner_name ;
1898 ELSE
1899 l_partner_names := l_partner_names || ' ,' || l_partner_name ;
1900 END IF;
1901
1902 END LOOP;
1903 CLOSE lc_get_pt_org_name;
1904
1905 l_log_params_tbl(1).param_name := 'OPP_NUMBER';
1906 l_log_params_tbl(1).param_value := l_lead_number;
1907
1908 l_log_params_tbl(2).param_name := 'STATUS';
1909 l_log_params_tbl(2).param_value := l_to_status;
1910
1911 if l_win_loss_indicator = 'W' then
1912 l_message_name := 'PV_LG_OPPTY_WON';
1913
1914 elsif l_win_loss_indicator = 'L' then
1915 l_message_name := 'PV_LG_OPPTY_LOST';
1916 else
1917 l_message_name := 'PV_LG_OPPTY_STATUS_CHG';
1918 end if;
1919
1920 if l_message_name is not null then
1921
1922 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1923 fnd_message.SET_NAME('PV', 'PV_DEBUG_MESSAGE');
1924 fnd_message.Set_token('TEXT', 'Logging status change message: ' || l_message_name ||
1925 ' for lead_id:' || l_lead_id || ' by resource:' || p_salesforce_id);
1926 fnd_msg_pub.Add;
1927 END IF;
1928
1929 for l_pt_id in 1..l_partner_id_tbl.count loop
1930 PVX_Utility_PVT.create_history_log(
1931 p_arc_history_for_entity_code => 'OPPORTUNITY',
1932 p_history_for_entity_id => l_lead_id,
1933 p_history_category_code => 'GENERAL',
1934 p_message_code => l_message_name,
1935 p_partner_id => l_partner_id_tbl(l_pt_id),
1936 p_access_level_flag => 'V',
1937 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_50,
1938 p_comments => NULL,
1939 p_log_params_tbl => l_log_params_tbl,
1940 x_return_status => x_return_status,
1941 x_msg_count => x_msg_count,
1942 x_msg_data => x_msg_data);
1943
1944 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1945 raise FND_API.G_EXC_ERROR;
1946 end if;
1947 end loop;
1948
1949 end if;
1950
1951 OPEN lc_status_notify (pc_status_code => l_status);
1952 FETCH lc_status_notify
1953 INTO l_notify_pt_flag, l_notify_am_flag, l_notify_cm_flag, l_notify_others_flag;
1954 CLOSE lc_status_notify;
1955
1956 -- Debug Message
1957 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1958 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1959 fnd_message.Set_Token('TEXT', 'After PV Party Notification : ' || l_notify_pt_flag || l_notify_am_flag ||
1960 l_notify_cm_flag || l_notify_others_flag);
1961 fnd_msg_pub.Add;
1962 END IF;
1963
1964 l_partner_id_tbl := JTF_NUMBER_TABLE();
1965
1966 OPEN lc_users(l_lead_id, l_notify_cm_flag, l_notify_am_flag, l_notify_pt_flag, l_notify_others_flag);
1967 LOOP
1968 FETCH lc_users INTO l_user_id, l_resource_id, l_user_name, l_user_type, l_partner_id;
1969 EXIT WHEN lc_users%NOTFOUND;
1970 l_user_id_tbl.extend;
1971 l_user_name_tbl.extend;
1972 l_resource_id_tbl.extend;
1973 l_user_type_tbl.extend;
1974
1975 l_user_id_tbl(count_row) := l_user_id;
1976 l_user_name_tbl(count_row) := l_user_name;
1977 l_resource_id_tbl(count_row) := l_resource_id;
1978 l_user_type_tbl(count_row) := l_user_type;
1979
1980 IF l_partner_id <> 0 THEN
1981 l_partner_id_tbl.extend;
1982 l_partner_id_tbl(l_partner_id_tbl.count) := l_partner_id;
1983 END IF;
1984
1985 count_row := count_row + 1;
1986 END LOOP;
1987 CLOSE lc_users;
1988
1989 -- Debug Message
1990 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1991 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
1992 fnd_message.Set_Token('TEXT', 'Total number of parties to be notified : ' ||
1993 l_user_name_tbl.count || ' lead_id : ' || l_lead_id);
1994 fnd_msg_pub.Add;
1995 END IF;
1996
1997 IF l_user_name_tbl.count > 0 THEN
1998 OPEN lc_opportunity(l_lead_id);
1999 FETCH lc_opportunity INTO l_customer_id, l_address_id, l_customer_name, l_opp_amt, l_currency_code, l_opp_name;
2000 CLOSE lc_opportunity;
2001
2002 l_opp_amt_curncy := l_opp_amt ||' '||l_currency_code;
2003
2004 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2005 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2006 fnd_message.Set_Token('TEXT', 'before calling the send_email ');
2007 fnd_msg_pub.Add;
2008 END IF;
2009
2010 Send_Email_By_Workflow (
2011 p_api_version_number => p_api_version_number,
2012 p_init_msg_list => p_init_msg_list,
2013 p_commit => p_commit,
2014 p_validation_level => p_validation_level,
2015 p_user_name_tbl => l_user_name_tbl,
2016 p_user_type_tbl => l_user_type_tbl,
2017 p_username => l_creating_username,
2018 p_opp_amt => l_opp_amt_curncy,
2019 p_opp_name => l_opp_name,
2020 p_customer_name => l_customer_name,
2021 p_lead_number => l_lead_number,
2022 p_from_status => l_from_status,
2023 p_to_status => l_to_status,
2024 p_vendor_org_name => l_vendor_org_name,
2025 p_partner_names => l_partner_names,
2026 x_return_status => x_return_status,
2027 x_msg_count => x_msg_count,
2028 x_msg_data => x_msg_data);
2029
2030 END IF;
2031
2032 -- Check the x_return_status. If its not successful throw an exception.
2033 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
2034 CLOSE lc_assign_ids;
2035 RAISE FND_API.G_EXC_ERROR;
2036 END IF;
2037
2038 END IF; -- If Assignment has been started
2039 CLOSE lc_assign_ids;
2040
2041 -- Standard call to get message count and if count is 1, get message info.
2042 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2043 p_count => x_msg_count,
2044 p_data => x_msg_data);
2045
2046 EXCEPTION
2047
2048 WHEN FND_API.G_EXC_ERROR THEN
2049
2050 x_return_status := FND_API.G_RET_STS_ERROR ;
2051 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2052 p_count => x_msg_count,
2053 p_data => x_msg_data);
2054
2055 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2056
2057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2058 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2059 p_count => x_msg_count,
2060 p_data => x_msg_data);
2061
2062 WHEN OTHERS THEN
2063 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2064 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2065 fnd_message.Set_Token('TEXT', sqlcode||sqlerrm);
2066 fnd_msg_pub.Add;
2067 END IF;
2068
2069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2070
2071 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2072 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2073 p_count => x_msg_count,
2074 p_data => x_msg_data);
2075
2076 END Notify_Party_On_Update_Oppty;
2077
2078
2079 PROCEDURE Party_Msg_Send_Wf
2080 ( itemtype in varchar2,
2081 itemkey in varchar2,
2082 actid in number,
2083 funcmode in varchar2,
2084 resultout in OUT NOCOPY varchar2)
2085 is
2086
2087 l_api_name CONSTANT VARCHAR2(30) := 'PARTY_MSG_SEND_WF';
2088 l_api_version_number CONSTANT NUMBER := 1.0;
2089
2090 l_resultout varchar2(50);
2091 l_msg_count number;
2092 l_msg_data varchar2(2000);
2093
2094 l_am_adhoc_role VARCHAR2(80) := NULL;
2095 l_cm_adhoc_role VARCHAR2(80) := NULL;
2096 l_pt_adhoc_role VARCHAR2(80) := NULL;
2097 l_ot_adhoc_role VARCHAR2(80) := NULL;
2098
2099
2100 l_group_notify_id NUMBER;
2101 l_pt_msg_name VARCHAR2(80);
2102 l_am_msg_name VARCHAR2(80);
2103 l_cm_msg_name VARCHAR2(80);
2104 l_ot_msg_name VARCHAR2(80);
2105 l_context VARCHAR2(80);
2106
2107 Begin
2108
2109 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2110 Debug( 'In ' || l_api_name || ' Funcmode: ' || funcmode);
2111 END IF;
2112
2113 if (funcmode = 'RUN') then
2114
2115 l_context := itemtype || ':' || itemkey || ':' || actid;
2116
2117 l_pt_adhoc_role := wf_engine.GetItemAttrText( itemtype => itemtype,
2118 itemkey => itemkey,
2119 aname => g_wf_attr_pt_notify_role);
2120
2121 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2122 Debug('Partner Role' || l_pt_adhoc_role);
2123 END IF;
2124
2125 l_cm_adhoc_role := wf_engine.GetItemAttrText( itemtype => itemtype,
2126 itemkey => itemkey,
2127 aname => g_wf_attr_cm_notify_role);
2128
2129 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2130 Debug('CM Role' || l_cm_adhoc_role);
2131 END IF;
2132
2133 l_ot_adhoc_role := wf_engine.GetItemAttrText( itemtype => itemtype,
2134 itemkey => itemkey,
2135 aname => g_wf_attr_ot_notify_role);
2136
2137 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2138 Debug( 'Others Role' || l_ot_adhoc_role);
2139 END IF;
2140
2141 l_am_adhoc_role := wf_engine.GetItemAttrText( itemtype => itemtype,
2142 itemkey => itemkey,
2143 aname => g_wf_attr_am_notify_role);
2144
2145 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2146 Debug('AM Role' || l_am_adhoc_role);
2147 END IF;
2148
2149 IF l_pt_adhoc_role IS NOT NULL THEN
2150
2151 l_pt_msg_name := 'PV_PARTY_OPTYUPD_PT_FYI_MSG';
2152
2153 l_group_notify_id := wf_notification.sendGroup(
2154 role => l_pt_adhoc_role,
2155 msg_type => 'PVOPTYHK',
2156 msg_name => l_pt_msg_name,
2157 due_date => null,
2158 callback => 'wf_engine.cb',
2159 context => l_context,
2160 send_comment => NULL,
2161 priority => NULL );
2162 END IF;
2163
2164 IF l_cm_adhoc_role IS NOT NULL THEN
2165
2166 l_cm_msg_name := 'PV_PARTY_OPTYUPD_CM_FYI_MSG';
2167
2168 l_group_notify_id := wf_notification.sendGroup(
2169 role => l_cm_adhoc_role,
2170 msg_type => 'PVOPTYHK',
2171 msg_name => l_cm_msg_name,
2172 due_date => null,
2173 callback => 'wf_engine.cb',
2174 context => l_context,
2175 send_comment => NULL,
2176 priority => NULL );
2177 END IF;
2178
2179 IF l_ot_adhoc_role IS NOT NULL THEN
2180
2181 l_ot_msg_name := 'PV_PARTY_OPTYUPD_OT_FYI_MSG';
2182
2183 l_group_notify_id := wf_notification.sendGroup(
2184 role => l_ot_adhoc_role,
2185 msg_type => 'PVOPTYHK',
2186 msg_name => l_ot_msg_name,
2187 due_date => null,
2188 callback => 'wf_engine.cb',
2189 context => l_context,
2190 send_comment => NULL,
2191 priority => NULL );
2192 END IF;
2193
2194 IF l_am_adhoc_role IS NOT NULL THEN
2195
2196 l_am_msg_name := 'PV_PARTY_OPTYUPD_AM_FYI_MSG';
2197
2198 l_group_notify_id := wf_notification.sendGroup(
2199 role => l_am_adhoc_role,
2200 msg_type => 'PVOPTYHK',
2201 msg_name => l_am_msg_name,
2202 due_date => null,
2203 callback => 'wf_engine.cb',
2204 context => l_context,
2205 send_comment => NULL,
2206 priority => NULL );
2207
2208 END IF;
2209
2210 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2211
2212 Debug('Group Notify ID' || l_group_notify_id);
2213
2214 END IF;
2215
2216 l_resultout := 'COMPLETE';
2217
2218 ELSIF (funcmode = 'CANCEL') then
2219 l_resultout := 'COMPLETE';
2220
2221 ELSIF (funcmode in ('RESPOND', 'FORWARD', 'TRANSFER')) then
2222 l_resultout := 'COMPLETE';
2223
2224 ELSIF (funcmode = 'TIMEOUT') then
2225 l_resultout := 'COMPLETE';
2226 END IF;
2227 resultout := l_resultout;
2228 EXCEPTION
2229 WHEN OTHERS THEN
2230
2231 fnd_msg_pub.Count_And_Get(
2232 p_encoded => FND_API.G_TRUE
2233 ,p_count => l_msg_count
2234 ,p_data => l_msg_data);
2235
2236 wf_core.context(G_PKG_NAME, l_api_name,l_msg_data);
2237 raise;
2238
2239 END PARTY_MSG_SEND_WF;
2240
2241
2242
2243 /***************************************************/
2244 /* Call the Create Opportunity user hook. *********/
2245 /***************************************************/
2246 procedure Create_Opportunity_Post (
2247 p_api_version_number IN NUMBER,
2248 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2249 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2250 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2251 p_oppty_header_rec IN AS_OPPORTUNITY_PUB.header_rec_type,
2252 p_salesforce_id IN NUMBER,
2253 x_return_status OUT NOCOPY VARCHAR2,
2254 x_msg_count OUT NOCOPY NUMBER,
2255 x_msg_data OUT NOCOPY VARCHAR2) is
2256
2257 l_api_name CONSTANT VARCHAR2(30) := 'Create_Opportunity_Post';
2258 l_api_version_number CONSTANT NUMBER := 1.0;
2259
2260 l_mode VARCHAR2(10) := 'CREATE';
2261 l_relationship_type VARCHAR2(20);
2262 l_party_id NUMBER;
2263 l_party_relation_id NUMBER;
2264 l_username VARCHAR2(1000);
2265 l_party_name VARCHAR2(1000);
2266 l_channel_code VARCHAR2(50) := p_oppty_header_rec.channel_code;
2267 l_partner_type VARCHAR2(100);
2268 l_indirect_channel_flag VARCHAR2(10);
2269
2270
2271 begin
2272
2273 /***************************************************************************/
2274 /** Notify Channel manager if the Opportunity is created by Partner */
2275 /** conatct or VAD contact */
2276 /***************************************************************************/
2277
2278 -- Initialize message list if p_init_msg_list is set to TRUE.
2279 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2280 fnd_msg_pub.initialize;
2281 END IF;
2282
2283 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2284 fnd_msg_pub.initialize;
2285 END IF;
2286
2287 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2288 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2289 fnd_message.Set_Token('TEXT', 'In ' || l_api_name||' Lead ID : '||p_oppty_header_rec.lead_id
2290 ||' Salesforce ID : '||p_salesforce_id);
2291 fnd_msg_pub.Add;
2292 END IF;
2293
2294 -- Initialize API return status to success
2295 x_return_status := FND_API.G_RET_STS_SUCCESS;
2296
2297 get_user_info
2298 ( p_salesforce_id => p_salesforce_id,
2299 p_channel_code => l_channel_code,
2300 x_party_rel_id => l_party_relation_id,
2301 x_relationship_type => l_relationship_type,
2302 x_user_name => l_username,
2303 x_party_name => l_party_name,
2304 x_party_type => l_partner_type,
2305 x_return_status => x_return_status,
2306 x_msg_count => x_msg_count,
2307 x_msg_data => x_msg_data
2308 );
2309
2310 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2311 raise FND_API.G_EXC_ERROR;
2312 end if;
2313
2314 -- -----------------------------------------------------------------
2315 -- Find out the channel type of the opportunity: DIRECT or INDIRECT.
2316 -- -----------------------------------------------------------------
2317 FOR x IN (
2318 select nvl(b.indirect_channel_flag, 'N') indirect_channel_flag
2319 from oe_lookups a, pv_channel_types b
2320 where a.lookup_type = 'SALES_CHANNEL'
2321 and a.lookup_code = l_channel_code
2322 and a.lookup_type = b.channel_lookup_type (+)
2323 and a.lookup_code = b.channel_lookup_code (+))
2324 LOOP
2325 l_indirect_channel_flag := x.indirect_channel_flag;
2326 END LOOP;
2327
2328
2329 -- -----------------------------------------------------------------
2330 -- If the channel type is INDIRECT, notify CM and copy partners
2331 -- from the campaign to the sales team of the opportunity.
2332 -- -----------------------------------------------------------------
2333 IF (l_indirect_channel_flag = 'Y') THEN
2334
2335 -- If not l_relationship_type = 'PARTNER_OF' or l_relationship_type is null then
2336 -- VAD creating opportunity : partners who were not managed by VAD were also added
2337 -- That was creating problem in assignment routing.
2338 -- Fo rnow, partners are not added from campaign while VAD is creating oppty.
2339
2340 If l_relationship_type is null then
2341
2342 if p_oppty_header_rec.source_promotion_id is not null then
2343
2344 PV_BG_PARTNER_MATCHING_PUB.Start_Campaign_Assignment(
2345 p_api_version_number => l_api_version_number,
2346 p_init_msg_list => p_init_msg_list,
2347 p_commit => p_commit,
2348 p_validation_level => p_validation_level,
2349 p_identity_salesforce_id => p_salesforce_id,
2350 P_Lead_id => p_oppty_header_rec.lead_id,
2351 x_return_status => x_return_status,
2352 x_msg_count => x_msg_count,
2353 x_msg_data => x_msg_data);
2354
2355 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2356 raise FND_API.G_EXC_ERROR;
2357 end if;
2358
2359 end if;
2360
2361 end if;
2362 END IF; -- l_indirect_channel_flag = 'Y'
2363
2364 EXCEPTION
2365 WHEN FND_API.G_EXC_ERROR THEN
2366
2367 x_return_status := FND_API.G_RET_STS_ERROR ;
2368 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2369 p_count => x_msg_count,
2370 p_data => x_msg_data);
2371
2372 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2373
2374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2375 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2376 p_count => x_msg_count,
2377 p_data => x_msg_data);
2378
2379 WHEN OTHERS THEN
2380
2381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2382 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2383 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2384 p_count => x_msg_count,
2385 p_data => x_msg_data);
2386
2387 end Create_Opportunity_Post;
2388
2389
2390 /***************************************************/
2391 /* Call the Update Opportunity user hook. *********/
2392 /***************************************************/
2393 procedure Update_Opportunity_Pre (
2394 p_api_version_number IN NUMBER,
2395 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2396 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2397 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2398 p_oppty_header_rec IN AS_OPPORTUNITY_PUB.header_rec_type,
2399 p_salesforce_id IN NUMBER,
2400 x_return_status OUT NOCOPY VARCHAR2,
2401 x_msg_count OUT NOCOPY NUMBER,
2402 x_msg_data OUT NOCOPY VARCHAR2) is
2403
2404 l_api_name CONSTANT VARCHAR2(30) := 'Update_Opportunity_Pre';
2405 l_api_version_number CONSTANT NUMBER := 1.0;
2406
2407 l_mode VARCHAR2(10) := 'UPDATE';
2408
2409 l_channel_code p_oppty_header_rec.channel_code%type := p_oppty_header_rec.channel_code;
2410 l_relationship_type VARCHAR2(20);
2411 l_party_relation_id NUMBER;
2412 l_user_name VARCHAR2(100);
2413 l_party_name VARCHAR2(1000);
2414 l_partner_type VARCHAR2(100);
2415
2416 begin
2417
2418 /***************************************************************************/
2419 /** Notify Channel manager if the Opportunity is updated by Partner */
2420 /** contact or VAD contact */
2421 /***************************************************************************/
2422
2423 -- Initialize message list if p_init_msg_list is set to TRUE.
2424 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2425 fnd_msg_pub.initialize;
2426 END IF;
2427
2428 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2429 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2430 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
2431 fnd_msg_pub.Add;
2432 END IF;
2433
2434 -- Initialize API return status to success
2435 x_return_status := FND_API.G_RET_STS_SUCCESS;
2436
2437 get_user_info
2438 ( p_salesforce_id => p_salesforce_id,
2439 p_channel_code => l_channel_code,
2440 x_party_rel_id => l_party_relation_id,
2441 x_relationship_type => l_relationship_type,
2442 x_user_name => l_user_name,
2443 x_party_name => l_party_name,
2444 x_party_type => l_partner_type,
2445 x_return_status => x_return_status,
2446 x_msg_count => x_msg_count,
2447 x_msg_data => x_msg_data
2448 );
2449
2450 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2451 raise FND_API.G_EXC_ERROR;
2452 end if;
2453
2454 if p_oppty_header_rec.source_promotion_id is not null then
2455
2456 PV_BG_PARTNER_MATCHING_PUB.Start_Campaign_Assignment(
2457 P_Api_Version_Number => l_api_version_number,
2458 P_Init_Msg_List => p_init_msg_list,
2459 P_Commit => p_commit,
2460 P_Validation_Level => p_validation_level,
2461 P_Identity_Salesforce_Id => p_salesforce_id,
2462 P_Lead_id => p_oppty_header_rec.lead_id,
2463 x_return_status => x_return_status,
2464 x_msg_count => x_msg_count,
2465 x_msg_data => x_msg_data);
2466
2467 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2468 raise FND_API.G_EXC_ERROR;
2469 end if;
2470
2471 end if;
2472
2473 Notify_Party_On_Update_Oppty (
2474 p_api_version_number => l_api_version_number,
2475 p_init_msg_list => p_init_msg_list,
2476 p_commit => p_commit,
2477 p_validation_level => p_validation_level,
2478 p_oppty_header_rec => p_oppty_header_rec,
2479 p_salesforce_id => p_salesforce_id,
2480 x_return_status => x_return_status,
2481 x_msg_count => x_msg_count,
2482 x_msg_data => x_msg_data);
2483
2484 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2485 raise FND_API.G_EXC_ERROR;
2486 end if;
2487
2488 exception
2489 WHEN FND_API.G_EXC_ERROR THEN
2490
2491 x_return_status := FND_API.G_RET_STS_ERROR ;
2492 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2493 p_count => x_msg_count,
2494 p_data => x_msg_data);
2495
2496 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2497
2498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2499 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2500 p_count => x_msg_count,
2501 p_data => x_msg_data);
2502
2503 WHEN no_data_found THEN
2504 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2505 fnd_message.Set_Token('TEXT', 'Current resource does not have a login user assigned. '||
2506 'Please use resource manager to assign a login user to this resource ');
2507 fnd_msg_pub.Add;
2508
2509 x_return_status := FND_API.G_RET_STS_ERROR ;
2510 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2511 p_count => x_msg_count,
2512 p_data => x_msg_data);
2513
2514 WHEN OTHERS THEN
2515
2516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2517 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2518 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2519 p_count => x_msg_count,
2520 p_data => x_msg_data);
2521
2522 end Update_Opportunity_Pre;
2523
2524
2525 procedure get_user_info
2526 ( p_salesforce_id IN VARCHAR2,
2527 p_channel_code IN VARCHAR2,
2528 x_party_rel_id OUT NOCOPY NUMBER,
2529 x_relationship_type OUT NOCOPY VARCHAR2,
2530 x_user_name OUT NOCOPY VARCHAR2,
2531 x_party_name OUT NOCOPY VARCHAR2,
2532 x_party_type OUT NOCOPY VARCHAR2,
2533 x_return_status OUT NOCOPY VARCHAR2,
2534 x_msg_count OUT NOCOPY NUMBER,
2535 x_msg_data OUT NOCOPY VARCHAR2
2536 )
2537 IS
2538 l_api_name CONSTANT VARCHAR2(30) := 'get_user_info';
2539 l_api_version_number CONSTANT NUMBER := 1.0;
2540
2541 l_relationship_type VARCHAR2(20);
2542 l_party_id NUMBER;
2543 l_party_relation_id NUMBER;
2544 l_username fnd_user.user_name%type;
2545 l_party_name VARCHAR2(1000);
2546 l_resource_category VARCHAR2(30);
2547
2548 l_channel_flag VARCHAR2(1);
2549
2550 l_partner_type VARCHAR2(100);
2551 l_attr_value VARCHAR2(100);
2552
2553
2554 cursor lc_chk_channel_code (pc_code varchar2) is
2555 --select a.meaning, nvl(b.indirect_channel_flag, 'N')
2556 select nvl(b.indirect_channel_flag, 'N')
2557 from oe_lookups a, pv_channel_types b
2558 where a.lookup_type = 'SALES_CHANNEL'
2559 and a.lookup_code = pc_code
2560 and a.lookup_type = b.channel_lookup_type (+)
2561 and a.lookup_code = b.channel_lookup_code (+);
2562
2563 CURSOR lc_get_rel_type(pc_party_id number) is
2564 SELECT 'PARTNER_OF', PVPP.partner_id, PARTNER.party_name, peav.attr_value
2565 FROM
2566 hz_parties PARTNER,
2567 hz_relationships CONTACT,
2568 pv_partner_profiles PVPP,
2569 pv_enty_attr_values peav
2570 WHERE CONTACT.party_id = pc_party_id
2571 AND CONTACT.subject_table_name = 'HZ_PARTIES'
2572 AND CONTACT.object_table_name = 'HZ_PARTIES'
2573 AND CONTACT.RELATIONSHIP_TYPE = 'EMPLOYMENT'
2574 AND CONTACT.RELATIONSHIP_CODE = 'EMPLOYEE_OF'
2575 AND CONTACT.directional_flag = 'F'
2576 AND CONTACT.STATUS = 'A'
2577 AND CONTACT.start_date <= SYSDATE
2578 AND nvl(CONTACT.end_date, SYSDATE) >= SYSDATE
2579 AND PVPP.partner_party_id = CONTACT.object_id
2580 AND PARTNER.party_id = PVPP.partner_party_id
2581 AND PARTNER.PARTY_TYPE = 'ORGANIZATION'
2582 AND PARTNER.status = 'A'
2583 AND peav.entity_id(+) = PVPP.partner_id
2584 AND peav.entity(+) = 'PARTNER'
2585 AND peav.attribute_id(+) = 3;
2586
2587 begin
2588
2589 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2590 debug('In '||l_api_name);
2591 END IF;
2592
2593 -- Initialize API return status to success
2594 x_return_status := FND_API.G_RET_STS_SUCCESS;
2595
2596 -- Get the Party id of the relation from based on the resource id
2597 SELECT js.source_id, fu.user_name, js.category
2598 INTO l_party_id, x_user_name, l_resource_category
2599 FROM fnd_user fu, jtf_rs_resource_extns js
2600 WHERE fu.user_id = js.user_id
2601 AND js.resource_id = p_salesforce_id;
2602
2603 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2604 debug('Person Party ID '|| l_party_id);
2605 debug('Person User name'|| x_user_name);
2606 END IF;
2607
2608 IF l_resource_category = 'PARTY' then
2609
2610 OPEN lc_get_rel_type (pc_party_id => l_party_id);
2611 LOOP
2612 FETCH lc_get_rel_type
2613 INTO l_relationship_type, l_party_relation_id, l_party_name, l_attr_value;
2614 EXIT WHEN lc_get_rel_type%notfound;
2615 IF l_attr_value = 'VAD' THEN
2616 exit;
2617 END IF;
2618
2619 END LOOP;
2620
2621 end if;
2622
2623 IF l_relationship_type is not null THEN
2624
2625 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2626 Debug('Relationship Type '|| l_relationship_type);
2627 END IF;
2628
2629 x_relationship_type := l_relationship_type;
2630 x_party_rel_id := l_party_relation_id;
2631 x_party_name := l_party_name;
2632
2633 IF l_relationship_type = 'PARTNER_OF' THEN
2634
2635 IF l_attr_value = 'VAD' THEN
2636 x_party_type := 'VAD' ;
2637 ELSE
2638 x_party_type := 'PARTNER';
2639 END IF;
2640
2641 END IF;
2642
2643 END IF;
2644 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
2645 Debug('Partner Type '|| x_party_type);
2646 END IF;
2647
2648 if (x_party_type = 'PARTNER' or x_party_type = 'VAD') THEN
2649 -- Validate if the Channel code is INDIRECT. If so, throw an exception.
2650
2651 open lc_chk_channel_code(pc_code => p_channel_code);
2652 fetch lc_chk_channel_code into l_channel_flag;
2653 close lc_chk_channel_code;
2654
2655 if (l_channel_flag = null or l_channel_flag = 'N') then
2656 fnd_message.SET_NAME('PV', 'PV_INVALID_CHANNEL_CODE');
2657 fnd_msg_pub.ADD;
2658 raise FND_API.G_EXC_ERROR;
2659 end if;
2660
2661 end if;
2662
2663 exception
2664 WHEN FND_API.G_EXC_ERROR THEN
2665
2666 x_return_status := FND_API.G_RET_STS_ERROR ;
2667 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2668 p_count => x_msg_count,
2669 p_data => x_msg_data);
2670
2671 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2672
2673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2674 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2675 p_count => x_msg_count,
2676 p_data => x_msg_data);
2677
2678 WHEN no_data_found THEN
2679 fnd_message.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2680 fnd_message.Set_Token('TEXT', 'Current resource does not have a login user assigned. '||
2681 'Please use resource manager to assign a login user to this resource ');
2682 fnd_msg_pub.Add;
2683
2684 x_return_status := FND_API.G_RET_STS_ERROR ;
2685 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2686 p_count => x_msg_count,
2687 p_data => x_msg_data);
2688
2689
2690
2691 WHEN OTHERS THEN
2692
2693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2694 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2695 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
2696 p_count => x_msg_count,
2697 p_data => x_msg_data);
2698 end get_user_info;
2699
2700 PROCEDURE Debug(
2701 p_msg_string IN VARCHAR2
2702 )
2703 IS
2704
2705 BEGIN
2706 FND_MESSAGE.Set_Name('PV', 'PV_DEBUG_MESSAGE');
2707 FND_MESSAGE.Set_Token('TEXT', p_msg_string);
2708 FND_MSG_PUB.Add;
2709
2710 END Debug;
2711
2712
2713 END PV_OPPORTUNITY_VHUK;