[Home] [Help]
PACKAGE BODY: APPS.PV_REFERRAL_GENERAL_PUB
Source
1 package body PV_REFERRAL_GENERAL_PUB as
2 /* $Header: pvxvrfgb.pls 120.6 2005/11/08 16:02:59 pklin ship $*/
3
4 /*************************************************************************************/
5 /* */
6 /* */
7 /* */
8 /* Global Variable Declaration */
9 /* */
10 /* */
11 /* */
12 /*************************************************************************************/
13 g_log_to_file VARCHAR2(5) := 'N';
14 g_pkg_name VARCHAR2(30) := 'PV_REFERRAL_GENERAL_PUB';
15 g_api_name VARCHAR2(30);
16 g_RETCODE VARCHAR2(10) := '0';
17 g_module_name VARCHAR2(48);
18
19
20 PV_DEBUG_HIGH_ON boolean :=
21 FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
22 PV_DEBUG_LOW_ON boolean :=
23 FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
24 PV_DEBUG_MEDIUM_ON boolean :=
25 FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
26 PV_DEBUG_ERROR_ON boolean :=
27 FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_ERROR);
28
29
30
31 /*************************************************************************************/
32 /* */
33 /* */
34 /* */
35 /* private procedure declaration */
36 /* */
37 /* */
38 /* */
39 /*************************************************************************************/
40 PROCEDURE Debug(
41 p_msg_string IN VARCHAR2,
42 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE',
43 p_token_type IN VARCHAR2 := 'TEXT',
44 p_statement_level IN NUMBER := FND_LOG.LEVEL_PROCEDURE
45 );
46
47 PROCEDURE Set_Message(
48 p_msg_level IN NUMBER,
49 p_msg_name IN VARCHAR2,
50 p_token1 IN VARCHAR2 := NULL,
51 p_token1_value IN VARCHAR2 := NULL,
52 p_token2 IN VARCHAR2 := NULL ,
53 p_token2_value IN VARCHAR2 := NULL,
54 p_token3 IN VARCHAR2 := NULL,
55 p_token3_value IN VARCHAR2 := NULL
56 );
57
58 PROCEDURE Retrieve_Attribute_Value(
59 p_entity_type IN VARCHAR2,
60 p_referral_id IN VARCHAR2,
61 p_attribute_id IN VARCHAR2,
62 x_attr_value OUT NOCOPY VARCHAR2
63 );
64
65 FUNCTION Get_Salesgroup_ID (
66 p_resource_id NUMBER
67 )
68 RETURN NUMBER;
69
70
71 --=============================================================================+
72 --| Public Procedure |
73 --| Update_Referral_Status |
74 --| |
75 --| Parameters |
76 --| IN |
77 --| OUT |
78 --| |
79 --| |
80 --| NOTES |
81 --| |
82 --| HISTORY |
83 --| |
84 --==============================================================================
85 --
86 -- This procedure does the following things:
87 -- (1) Update the status of referrals/deal registration to the following status
88 -- codes: CLOSED_LOST_OPPTY, CLOSED_OPPTY_WON, CLOSED_DEAD_LEAD,
89 -- EXPIRED.
90 --
91 -- (2) To log a message in pv_ge_history_log_b table to record the fact that
92 -- an order has been created as a result of a quote, which is linked to an
93 -- opportunity, being generated.
94 --
95 --==============================================================================
96
97 PROCEDURE Update_Referral_Status (
98 ERRBUF OUT NOCOPY VARCHAR2,
99 RETCODE OUT NOCOPY VARCHAR2,
100 p_log_to_file IN VARCHAR2 := 'Y'
101 )
102 IS
103 i NUMBER;
104 l_return_status VARCHAR2(100);
105 l_msg_count NUMBER;
106 l_msg_data VARCHAR2(500);
107 l_total_start NUMBER;
108 l_elapsed_time NUMBER;
109 l_log_params_tbl pvx_utility_pvt.log_params_tbl_type;
110
111 l_event_name VARCHAR2(200) := 'oracle.apps.pv.benefit.referral.statusChange';
112 l_event_key VARCHAR2(200);
113 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
114 l_parameter_t wf_parameter_t := wf_parameter_t(null, null);
115
116 CURSOR c_closed_lost_oppty IS
117 SELECT REF.referral_id, REF.referral_name, REF.benefit_id, REF.partner_id
118 FROM pv_referrals_vl REF,
119 as_leads_all OPPTY,
120 as_statuses_b STATUS
121 WHERE REF.referral_status = 'APPROVED' AND
122 REF.entity_type IN ('LEAD') AND
123 REF.entity_id_linked_to = OPPTY.lead_id AND
124 OPPTY.status = STATUS.status_code AND
125 STATUS.opp_flag = 'Y' AND
126 STATUS.win_loss_indicator = 'L';
127
128
129 CURSOR c_closed_oppty_won IS
130 SELECT REF.referral_id, REF.referral_name, REF.benefit_id, REF.partner_id
131 FROM pv_referrals_vl REF,
132 as_leads_all OPPTY,
133 as_statuses_b STATUS
134 WHERE REF.referral_status = 'APPROVED' AND
135 REF.entity_type IN ('LEAD') AND
136 REF.entity_id_linked_to = OPPTY.lead_id AND
137 OPPTY.status = STATUS.status_code AND
138 STATUS.opp_flag = 'Y' AND
139 STATUS.win_loss_indicator = 'W';
140
141 CURSOR c_closed_dead_lead IS
142 SELECT REF.referral_id, REF.referral_name, REF.benefit_id, REF.partner_id
143 FROM pv_referrals_vl REF,
144 as_sales_leads LEAD
145 WHERE REF.referral_status = 'APPROVED' AND
146 REF.entity_type = 'SALES_LEAD' AND
147 REF.entity_id_linked_to = LEAD.sales_lead_id AND
148 LEAD.status_code = 'DEAD_LEAD';
149
150 CURSOR c_expired IS
151 SELECT REF.referral_id, REF.referral_name, REF.benefit_id, REF.partner_id
152 FROM pv_referrals_vl REF,
153 pv_benft_thresholds THR,
154 jtf_terr_all TR,
155 jtf_terr_qual_all TQ,
156 jtf_terr_values_all TV
157 WHERE REF.referral_status = 'APPROVED' AND
158 REF.status_change_date + THR.expiration < SYSDATE AND
159 REF.order_id IS NULL AND
160 REF.benefit_id = THR.benefit_id AND
161 THR.territory_id = TR.terr_id AND
162 TR.terr_id = TQ.terr_id AND
163 TQ.qual_usg_id = -1065 AND
164 TQ.terr_qual_id = TV.terr_qual_id AND
165 TV.comparison_operator = '=' AND
166 TV.low_value_char = REF.customer_country;
167
168
169 CURSOR c_opportunity_order IS
170 -- These opportunities are generated through referrals
171 SELECT REF.entity_id_linked_to lead_id,
172 REF.referral_id,
173 REF.partner_id,
174 A.LEAD_NUMBER,
175 C.ORDER_ID,
176 C.QUOTE_HEADER_ID,
177 E.PARTY_NAME
178 FROM pv_referrals_b REF,
179 AS_LEADS_ALL A,
180 AS_STATUSES_B AA,
181 ASO_QUOTE_RELATED_OBJECTS B,
182 ASO_QUOTE_HEADERS_ALL C,
183 HZ_CUST_ACCOUNTS D,
184 HZ_PARTIES E
185 WHERE REF.entity_type IN ('LEAD') AND
186 REF.entity_id_linked_to IS NOT NULL AND
187 NOT EXISTS (
188 SELECT 'x'
189 FROM pv_ge_history_log_b b
190 WHERE REF.entity_id_linked_to = b.history_for_entity_id AND
191 b.arc_history_for_entity_code = 'OPPORTUNITY' AND
192 b.history_category_code = 'GENERAL' AND
193 b.message_code = 'PV_LG_OPPTY_ORDER_PLACED'
194 ) AND
195 A.lead_id = REF.entity_id_linked_to AND
196 A.status = AA.STATUS_CODE AND
197 AA.WIN_LOSS_INDICATOR = 'W' AND
198 OPP_FLAG = 'Y' AND
199 A.LEAD_ID = B.OBJECT_ID AND
200 B.object_type_code = 'LDID' AND
201 B.relationship_type_code = 'OPP_QUOTE' AND
202 B.quote_object_type_code = 'HEADER' AND
203 B.quote_object_id = C.quote_header_id AND
204 C.CUST_ACCOUNT_ID = D.CUST_ACCOUNT_ID AND
205 D.PARTY_ID = E.PARTY_ID
206 UNION ALL
207 -- These opportunities are NOT generated through referrals
208 SELECT a.lead_id,
209 -1 referral_id,
210 b.partner_id,
211 c.lead_number,
212 g.ORDER_ID,
213 g.QUOTE_HEADER_ID,
214 d.party_name
215 FROM pv_lead_workflows a,
216 pv_lead_assignments b,
217 as_leads_all c,
218 hz_parties d,
219 AS_STATUSES_B e,
220 ASO_QUOTE_RELATED_OBJECTS f,
221 ASO_QUOTE_HEADERS_ALL g
222 WHERE a.latest_routing_flag = 'Y' AND
223 a.routing_status = 'ACTIVE' AND
224 a.WF_ITEM_TYPE = 'PVASGNMT' AND -- indicates vendor routing
225 a.ENTITY = 'OPPORTUNITY' AND
226 a.wf_item_type = b.wf_item_type AND
227 a.wf_item_key = b.wf_item_key AND
228 b.STATUS IN ('PT_APPROVED','CM_APP_FOR_PT') AND
229 a.lead_id = c.lead_id AND
230 c.customer_id = d.party_id AND
231 NOT EXISTS (
232 SELECT 'x'
233 FROM pv_ge_history_log_b LOG
234 WHERE c.lead_id = LOG.history_for_entity_id AND
235 LOG.arc_history_for_entity_code = 'OPPORTUNITY' AND
236 LOG.history_category_code = 'GENERAL' AND
237 LOG.message_code = 'PV_LG_OPPTY_ORDER_PLACED'
238 ) AND
239 c.status = e.status_code AND
240 e.WIN_LOSS_INDICATOR = 'W' AND
241 e.OPP_FLAG = 'Y' AND
242 c.lead_id = f.object_id AND
243 f.object_type_code = 'LDID' AND
244 f.relationship_type_code = 'OPP_QUOTE' AND
245 f.quote_object_type_code = 'HEADER' AND
246 f.quote_object_id = g.quote_header_id;
247
248 BEGIN
249 g_api_name := 'Update_Referral_Status';
250
251 -- -----------------------------------------------------------------------
252 -- Set variables.
253 -- -----------------------------------------------------------------------
254 l_total_start := dbms_utility.get_time;
255
256 g_module_name := 'Referral: Update Referral Status';
257
258 dbms_application_info.set_module(
259 module_name => g_module_name,
260 action_name => 'STARTUP'
261 );
262
263 IF (p_log_to_file <> 'Y') THEN
264 g_log_to_file := 'N';
265 ELSE
266 g_log_to_file := 'Y';
267 END IF;
268
269
270 -- -----------------------------------------------------------------------
271 -- Start time message...
272 -- -----------------------------------------------------------------------
273 Debug(p_msg_string => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),
274 p_msg_type => 'PV_REFERRAL_STATUS_START_TIME',
275 p_token_type => 'P_DATE_TIME',
276 p_statement_level => FND_LOG.LEVEL_EVENT
277 );
278
279
280 -- -----------------------------------------------------------------------
281 -- Update referral_status to 'CLOSED_LOST_OPPTY' for all the 'APPROVED'
282 -- referrals/deal registrations whose associated opportunity is lost.
283 -- -----------------------------------------------------------------------
284 Debug('-------------------------------------------------------------------------');
285 Debug('Update referral_status to ''CLOSED_LOST_OPPTY'' for all the ''APPROVED''');
286 Debug('referrals/deal registrations whose associated opportunity is lost.');
287
288 -- -------------------------------------------------
289 -- Update referral status
290 -- -------------------------------------------------
291 FOR x IN c_closed_lost_oppty LOOP
292 BEGIN
293 Debug('Updating ''' || x.referral_name || '''(referral_id: ' ||
294 x.referral_id || ')');
295
296 UPDATE pv_referrals_b
297 SET referral_status = 'CLOSED_LOST_OPPTY',
298 status_change_date = SYSDATE
299 WHERE referral_id = x.referral_id;
300
301
302 -- -------------------------------------------------
303 -- Raise business event
304 -- oracle.apps.pv.benefit.referral.statusChange
305 -- -------------------------------------------------
306 Debug('Calling pv_benft_status_change.status_change_raise...');
307 pv_benft_status_change.status_change_raise(
308 p_api_version_number => 1.0,
309 p_init_msg_list => FND_API.G_FALSE,
310 p_commit => FND_API.G_FALSE,
311 p_event_name => 'oracle.apps.pv.benefit.referral.statusChange',
312 p_benefit_id => x.benefit_id,
313 p_entity_id => x.referral_id,
314 p_status_code => 'CLOSED_LOST_OPPTY',
315 p_partner_id => x.partner_id,
316 p_msg_callback_api => 'pv_benft_status_change.REFERRAL_SET_MSG_ATTRS',
317 p_user_callback_api => 'pv_benft_status_change.REFERRAL_RETURN_USERLIST',
318 x_return_status => l_return_status,
319 x_msg_count => l_msg_count,
320 x_msg_data => l_msg_data);
321
322 Debug('Return Status: ' || l_return_status);
323
324 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
325 RAISE FND_API.G_EXC_ERROR;
326
327 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
328 RAISE FND_API.g_exc_unexpected_error;
329 END IF;
330
331
332 -- -------------------------------------------------
333 -- Log the event.
334 -- -------------------------------------------------
335 Debug('Calling pv_benft_status_change.STATUS_CHANGE_LOGGING...');
336 pv_benft_status_change.STATUS_CHANGE_LOGGING(
337 p_api_version_number => 1.0,
338 p_init_msg_list => FND_API.G_FALSE,
339 p_commit => FND_API.G_FALSE,
340 p_benefit_id => x.benefit_id,
341 P_STATUS => 'CLOSED_LOST_OPPTY',
342 p_entity_id => x.referral_id,
343 p_partner_id => x.partner_id,
344 x_return_status => l_return_status,
345 x_msg_count => l_msg_count,
346 x_msg_data => l_msg_data
347 );
348
349 Debug('Return Status: ' || l_return_status);
350
351 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
352 RAISE FND_API.G_EXC_ERROR;
353
354 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
355 RAISE FND_API.g_exc_unexpected_error;
356 END IF;
357
358 --------------------------- Exception --------------------------------
359 EXCEPTION
360 WHEN FND_API.G_EXC_ERROR THEN
361 g_RETCODE := '1';
362
363 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
364 p_count => l_msg_count,
365 p_data => l_msg_data);
366
367 WHEN FND_API.g_exc_unexpected_error THEN
368 g_RETCODE := '1';
369
370 FND_MSG_PUB.count_and_get(
371 p_encoded => FND_API.g_false,
372 p_count => l_msg_count,
373 p_data => l_msg_data
374 );
375
376 WHEN OTHERS THEN
377 g_RETCODE := '1';
378
379 FND_MSG_PUB.count_and_get(
380 p_encoded => FND_API.g_false,
381 p_count => l_msg_count,
382 p_data => l_msg_data
383 );
384
385 END;
386 END LOOP;
387
388
389
390 -- -----------------------------------------------------------------------
391 -- Update referral_status to 'CLOSED_OPPTY_WON' for all the 'APPROVED'
392 -- deal registrations whose associated opportunity is won.
393 -- -----------------------------------------------------------------------
394 Debug('-------------------------------------------------------------------------');
395 Debug('Update referral_status to ''CLOSED_OPPTY_WON'' for all the ''APPROVED''');
396 Debug('referrals/deal registrations whose associated opportunity is won.');
397
398 FOR x IN c_closed_oppty_won LOOP
399 BEGIN
400 Debug('Updating ''' || x.referral_name || '''(referral_id: ' ||
401 x.referral_id || ')');
402
403 UPDATE pv_referrals_b
404 SET referral_status = 'CLOSED_OPPTY_WON',
405 status_change_date = SYSDATE
406 WHERE referral_id = x.referral_id;
407
408 -- -------------------------------------------------
409 -- Raise business event
410 -- oracle.apps.pv.benefit.referral.statusChange
411 -- -------------------------------------------------
412 Debug('Calling pv_benft_status_change.status_change_raise...');
413 pv_benft_status_change.status_change_raise(
414 p_api_version_number => 1.0,
415 p_init_msg_list => FND_API.G_FALSE,
416 p_commit => FND_API.G_FALSE,
417 p_event_name => 'oracle.apps.pv.benefit.referral.statusChange',
418 p_benefit_id => x.benefit_id,
419 p_entity_id => x.referral_id,
420 p_status_code => 'CLOSED_OPPTY_WON',
421 p_partner_id => x.partner_id,
422 p_msg_callback_api => 'pv_benft_status_change.REFERRAL_SET_MSG_ATTRS',
423 p_user_callback_api => 'pv_benft_status_change.REFERRAL_RETURN_USERLIST',
424 x_return_status => l_return_status,
425 x_msg_count => l_msg_count,
426 x_msg_data => l_msg_data);
427
428 Debug('Return Status: ' || l_return_status);
429
430 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
431 RAISE FND_API.G_EXC_ERROR;
432
433 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
434 RAISE FND_API.g_exc_unexpected_error;
435 END IF;
436
437 -- -------------------------------------------------
438 -- Log the event.
439 -- -------------------------------------------------
440 Debug('Calling pv_benft_status_change.STATUS_CHANGE_LOGGING...');
441 pv_benft_status_change.STATUS_CHANGE_LOGGING(
442 p_api_version_number => 1.0,
443 p_init_msg_list => FND_API.G_FALSE,
444 p_commit => FND_API.G_FALSE,
445 p_benefit_id => x.benefit_id,
446 P_STATUS => 'CLOSED_OPPTY_WON',
447 p_entity_id => x.referral_id,
448 p_partner_id => x.partner_id,
449 x_return_status => l_return_status,
450 x_msg_count => l_msg_count,
451 x_msg_data => l_msg_data
452 );
453
454 Debug('Return Status: ' || l_return_status);
455
456 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
457 RAISE FND_API.G_EXC_ERROR;
458
459 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
460 RAISE FND_API.g_exc_unexpected_error;
461 END IF;
462
463 --------------------------- Exception --------------------------------
464 EXCEPTION
465 WHEN FND_API.G_EXC_ERROR THEN
466 g_RETCODE := '1';
467
468 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
469 p_count => l_msg_count,
470 p_data => l_msg_data);
471
472 WHEN FND_API.g_exc_unexpected_error THEN
473 g_RETCODE := '1';
474
475 FND_MSG_PUB.count_and_get(
476 p_encoded => FND_API.g_false,
477 p_count => l_msg_count,
478 p_data => l_msg_data
479 );
480
481 WHEN OTHERS THEN
482 g_RETCODE := '1';
483
484 FND_MSG_PUB.count_and_get(
485 p_encoded => FND_API.g_false,
486 p_count => l_msg_count,
487 p_data => l_msg_data
488 );
489
490 END;
491 END LOOP;
492
493 -- -----------------------------------------------------------------------
494 -- Update referral_status to 'CLOSED_DEAD_LEAD' for all the 'APPROVED'
495 -- referrals/deal registrations whose associated lead is closed.
496 -- -----------------------------------------------------------------------
497 Debug('-------------------------------------------------------------------------');
498 Debug('Update referral_status to ''CLOSED_DEAD_LEAD'' for all the ''APPROVED''');
499 Debug('referrals/deal registrations whose associated lead is closed.');
500
501 FOR x IN c_closed_dead_lead LOOP
502 BEGIN
503 Debug('Updating ''' || x.referral_name || '''(referral_id: ' ||
504 x.referral_id || ')');
505
506 UPDATE pv_referrals_b
507 SET referral_status = 'CLOSED_DEAD_LEAD',
508 status_change_date = SYSDATE
509 WHERE referral_id = x.referral_id;
510
511 -- -------------------------------------------------
512 -- Raise business event
513 -- oracle.apps.pv.benefit.referral.statusChange
514 -- -------------------------------------------------
515 Debug('Calling pv_benft_status_change.status_change_raise...');
516 pv_benft_status_change.status_change_raise(
517 p_api_version_number => 1.0,
518 p_init_msg_list => FND_API.G_FALSE,
519 p_commit => FND_API.G_FALSE,
520 p_event_name => 'oracle.apps.pv.benefit.referral.statusChange',
521 p_benefit_id => x.benefit_id,
522 p_entity_id => x.referral_id,
523 p_status_code => 'CLOSED_DEAD_LEAD',
524 p_partner_id => x.partner_id,
525 p_msg_callback_api => 'pv_benft_status_change.REFERRAL_SET_MSG_ATTRS',
526 p_user_callback_api => 'pv_benft_status_change.REFERRAL_RETURN_USERLIST',
527 x_return_status => l_return_status,
528 x_msg_count => l_msg_count,
529 x_msg_data => l_msg_data);
530
531 Debug('Return Status: ' || l_return_status);
532
533 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
534 RAISE FND_API.G_EXC_ERROR;
535
536 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
537 RAISE FND_API.g_exc_unexpected_error;
538 END IF;
539
540 -- -------------------------------------------------
541 -- Log the event.
542 -- -------------------------------------------------
543 Debug('Calling pv_benft_status_change.STATUS_CHANGE_LOGGING...');
544 pv_benft_status_change.STATUS_CHANGE_LOGGING(
545 p_api_version_number => 1.0,
546 p_init_msg_list => FND_API.G_FALSE,
547 p_commit => FND_API.G_FALSE,
548 p_benefit_id => x.benefit_id,
549 P_STATUS => 'CLOSED_DEAD_LEAD',
550 p_entity_id => x.referral_id,
551 p_partner_id => x.partner_id,
552 x_return_status => l_return_status,
553 x_msg_count => l_msg_count,
554 x_msg_data => l_msg_data
555 );
556
557 Debug('Return Status: ' || l_return_status);
558
559 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
560 RAISE FND_API.G_EXC_ERROR;
561
562 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
563 RAISE FND_API.g_exc_unexpected_error;
564 END IF;
565
566 --------------------------- Exception --------------------------------
567 EXCEPTION
568 WHEN FND_API.G_EXC_ERROR THEN
569 g_RETCODE := '1';
570
571 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
572 p_count => l_msg_count,
573 p_data => l_msg_data);
574
575 WHEN FND_API.g_exc_unexpected_error THEN
576 g_RETCODE := '1';
577
578 FND_MSG_PUB.count_and_get(
579 p_encoded => FND_API.g_false,
580 p_count => l_msg_count,
581 p_data => l_msg_data
582 );
583
584 WHEN OTHERS THEN
585 g_RETCODE := '1';
586
587 FND_MSG_PUB.count_and_get(
588 p_encoded => FND_API.g_false,
589 p_count => l_msg_count,
590 p_data => l_msg_data
591 );
592
593 END;
594 END LOOP;
595
596 -- -----------------------------------------------------------------------
597 -- Expired referrals/deal registrations.
598 --
599 -- Note that jtf territory tables are org-striped. We will just use the
600 -- base table jtf_xxx_all.
601 -- -----------------------------------------------------------------------
602 Debug('-------------------------------------------------------------------------');
603 Debug('Update referral status for expired referrals and deal registrations');
604
605 FOR x IN c_expired LOOP
606 BEGIN
607 Debug('Updating ''' || x.referral_name || '''(referral_id: ' ||
608 x.referral_id || ')');
609
610 UPDATE pv_referrals_b
611 SET referral_status = 'EXPIRED',
612 status_change_date = SYSDATE
613 WHERE referral_id = x.referral_id;
614
615 -- -------------------------------------------------
616 -- Raise business event
617 -- oracle.apps.pv.benefit.referral.statusChange
618 -- -------------------------------------------------
619 Debug('Calling pv_benft_status_change.status_change_raise...');
620 pv_benft_status_change.status_change_raise(
621 p_api_version_number => 1.0,
622 p_init_msg_list => FND_API.G_FALSE,
623 p_commit => FND_API.G_FALSE,
624 p_event_name => 'oracle.apps.pv.benefit.referral.statusChange',
625 p_benefit_id => x.benefit_id,
626 p_entity_id => x.referral_id,
627 p_status_code => 'EXPIRED',
628 p_partner_id => x.partner_id,
629 p_msg_callback_api => 'pv_benft_status_change.REFERRAL_SET_MSG_ATTRS',
630 p_user_callback_api => 'pv_benft_status_change.REFERRAL_RETURN_USERLIST',
631 x_return_status => l_return_status,
632 x_msg_count => l_msg_count,
633 x_msg_data => l_msg_data);
634
635 Debug('Return Status: ' || l_return_status);
636
637 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
638 RAISE FND_API.G_EXC_ERROR;
639
640 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
641 RAISE FND_API.g_exc_unexpected_error;
642 END IF;
643
644 -- -------------------------------------------------
645 -- Log the event.
646 -- -------------------------------------------------
647 Debug('Calling pv_benft_status_change.STATUS_CHANGE_LOGGING...');
648 pv_benft_status_change.STATUS_CHANGE_LOGGING(
649 p_api_version_number => 1.0,
650 p_init_msg_list => FND_API.G_FALSE,
651 p_commit => FND_API.G_FALSE,
652 p_benefit_id => x.benefit_id,
653 P_STATUS => 'EXPIRED',
654 p_entity_id => x.referral_id,
655 p_partner_id => x.partner_id,
656 x_return_status => l_return_status,
657 x_msg_count => l_msg_count,
658 x_msg_data => l_msg_data
659 );
660
661 Debug('Return Status: ' || l_return_status);
662
663 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
664 RAISE FND_API.G_EXC_ERROR;
665
666 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
667 RAISE FND_API.g_exc_unexpected_error;
668 END IF;
669
670
671
672 --------------------------- Exception --------------------------------
673 EXCEPTION
674 WHEN FND_API.G_EXC_ERROR THEN
675 g_RETCODE := '1';
676
677 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
678 p_count => l_msg_count,
679 p_data => l_msg_data);
680
681 WHEN FND_API.g_exc_unexpected_error THEN
682 g_RETCODE := '1';
683
684 FND_MSG_PUB.count_and_get(
685 p_encoded => FND_API.g_false,
686 p_count => l_msg_count,
687 p_data => l_msg_data
688 );
689
690 WHEN OTHERS THEN
691 g_RETCODE := '1';
692
693 FND_MSG_PUB.count_and_get(
694 p_encoded => FND_API.g_false,
695 p_count => l_msg_count,
696 p_data => l_msg_data
697 );
698
699 END;
700 END LOOP;
701
702
703 -- -----------------------------------------------------------------------
704 -- Log records in pv_ge_history_log_b for
705 -- opportunity --> quote --> order links.
706 -- -----------------------------------------------------------------------
707 Debug('-------------------------------------------------------------------------');
708 Debug('Log records in pv_ge_history_log_b to report the fact that');
709 Debug('an order has been created as the result of generating a quote linked');
710 Debug('to an opportunity.');
711
712 FOR x IN c_opportunity_order LOOP
713 BEGIN
714 Debug('Logging for ==========================>');
715 Debug('Referral ID: ' || x.referral_id);
716 Debug('Lead ID: ' || x.lead_id);
717 Debug('Partner ID: ' || x.partner_id);
718 Debug('Quote Header ID: ' || x.quote_header_id);
719 Debug('Order ID: ' || x.order_id);
720
721 l_log_params_tbl.DELETE;
722 l_log_params_tbl(1).param_name := 'OPP_NUMBER';
723 l_log_params_tbl(1).param_value := x.lead_number;
724
725 l_log_params_tbl(2).param_name := 'CUSTOMER_NAME';
726 l_log_params_tbl(2).param_value := x.party_name;
727
728 PVX_Utility_PVT.create_history_log(
729 p_arc_history_for_entity_code => 'OPPORTUNITY',
730 p_history_for_entity_id => x.lead_id,
731 p_history_category_code => 'GENERAL',
732 p_message_code => 'PV_LG_OPPTY_ORDER_PLACED',
733 p_partner_id => x.partner_id,
734 p_access_level_flag => 'V',
735 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_50,
736 p_comments => NULL,
737 p_log_params_tbl => l_log_params_tbl,
738 x_return_status => l_return_status,
739 x_msg_count => l_msg_count,
740 x_msg_data => l_msg_data);
741
742 Debug('Return Status: ' || l_return_status);
743
744 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
745 RAISE FND_API.G_EXC_ERROR;
746
747 ELSIF (l_return_status = FND_API.g_ret_sts_unexp_error) THEN
748 RAISE FND_API.g_exc_unexpected_error;
749 END IF;
750
751
752 --------------------------- Exception --------------------------------
753 EXCEPTION
754 WHEN FND_API.G_EXC_ERROR THEN
755 g_RETCODE := '1';
756
757 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
758 p_count => l_msg_count,
759 p_data => l_msg_data);
760
761 WHEN FND_API.g_exc_unexpected_error THEN
762 g_RETCODE := '1';
763
764 FND_MSG_PUB.count_and_get(
765 p_encoded => FND_API.g_false,
766 p_count => l_msg_count,
767 p_data => l_msg_data
768 );
769
770 WHEN OTHERS THEN
771 g_RETCODE := '1';
772
773 FND_MSG_PUB.count_and_get(
774 p_encoded => FND_API.g_false,
775 p_count => l_msg_count,
776 p_data => l_msg_data
777 );
778
779 END;
780 END LOOP;
781
782
783 -- -------------------------------------------------------------------------
784 -- Display End Time Message.
785 -- -------------------------------------------------------------------------
786 Debug(p_msg_string => TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'),
787 p_msg_type => 'PV_REFERRAL_STATUS_END_TIME',
788 p_token_type => 'P_DATE_TIME',
789 p_statement_level => FND_LOG.LEVEL_EVENT
790 );
791
792
793 l_elapsed_time := DBMS_UTILITY.get_time - l_total_start;
794 Debug('=====================================================================');
795 Debug('Total Elapsed Time: ' || l_elapsed_time || ' hsec' || ' = ' ||
796 ROUND((l_elapsed_time/6000), 2) || ' minutes');
797 Debug('=====================================================================');
798
799
800 END Update_Referral_Status;
801 -- ======================End of Update_Referral_Status===========================
802
803
804 --=============================================================================+
805 --| Public Procedure |
806 --| Create_Lead_Opportunity |
807 --| |
808 --| Parameters |
809 --| IN |
810 --| OUT |
811 --| |
812 --| |
813 --| NOTES |
814 --| |
815 --| HISTORY |
816 --| |
817 --==============================================================================
818 PROCEDURE Create_Lead_Opportunity (
819 p_api_version IN NUMBER,
820 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
821 p_commit IN VARCHAR2 := FND_API.g_false,
822 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
823 p_referral_id IN NUMBER,
824 p_customer_party_id IN NUMBER := NULL,
825 p_customer_party_site_id IN NUMBER := NULL,
826 p_customer_org_contact_id IN NUMBER := NULL,
827 p_customer_contact_party_id IN NUMBER := NULL,
828 p_get_from_db_flag IN VARCHAR2 := 'Y',
829 x_entity_type OUT NOCOPY VARCHAR2,
830 x_entity_id OUT NOCOPY NUMBER,
831 x_return_status OUT NOCOPY VARCHAR2,
832 x_msg_count OUT NOCOPY NUMBER,
833 x_msg_data OUT NOCOPY VARCHAR2
834 )
835 IS
836 l_api_version NUMBER := 1;
837 l_benefit_type VARCHAR2(30);
838 l_sales_transaction_type VARCHAR2(30);
839 r_header_rec AS_OPPORTUNITY_PUB.HEADER_REC_TYPE;
840 r_opp_header_rec AS_OPPORTUNITY_PUB.HEADER_REC_TYPE;
841 r_empty_header_rec AS_OPPORTUNITY_PUB.HEADER_REC_TYPE;
842 l_line_tbl AS_OPPORTUNITY_PUB.Line_Tbl_Type;
843 l_line_out_tbl AS_OPPORTUNITY_PUB.Line_Out_Tbl_Type;
844 l_contact_tbl AS_OPPORTUNITY_PUB.Contact_Tbl_Type;
845 l_contact_out_tbl AS_OPPORTUNITY_PUB.Contact_Out_Tbl_Type;
846 r_lead_header_rec AS_SALES_LEADS_PUB.sales_lead_rec_type;
847 l_lead_line_tbl AS_SALES_LEADS_PUB.sales_lead_line_tbl_type;
848 l_lead_contact_tbl AS_SALES_LEADS_PUB.sales_lead_contact_tbl_type;
849 l_lead_line_out_tbl AS_SALES_LEADS_PUB.SALES_LEAD_LINE_OUT_Tbl_Type;
850 l_lead_contact_out_tbl AS_SALES_LEADS_PUB.SALES_LEAD_CNT_OUT_Tbl_Type;
851 l_lead_note_id NUMBER;
852
853 l_customer_party_id NUMBER;
854 l_customer_party_site_id NUMBER;
855 l_customer_org_contact_id NUMBER;
856 l_customer_contact_party_id NUMBER;
857 l_partner_contact_rs_id NUMBER;
858 l_partner_contact_party_id NUMBER;
859 l_partner_id NUMBER;
860 l_partner_org_name VARCHAR2(100);
861 l_partner_contact_username VARCHAR2(100);
862 l_customer_name VARCHAR2(250);
863 l_invoker_user_id NUMBER := FND_GLOBAL.USER_ID();
864 l_referral_code VARCHAR2(50);
865 l_currency_code VARCHAR2(10);
866 l_invoker_resource_id NUMBER;
867 l_invoker_salesgroup_id NUMBER;
868 l_pt_salesgroup_id NUMBER;
869 i NUMBER := 1;
870 l_sales_team_rec AS_ACCESS_PUB.SALES_TEAM_REC_TYPE;
871 l_empty_sales_team_rec AS_ACCESS_PUB.SALES_TEAM_REC_TYPE;
872 l_access_profile_rec AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
873 l_access_id NUMBER;
874 l_channel_manager_tbl pv_assign_util_pvt.resource_details_tbl_type :=
875 pv_assign_util_pvt.resource_details_tbl_type();
876 l_vad_id NUMBER;
877 l_entity_type VARCHAR2(20);
878 l_org_id NUMBER;
879
880 -- ------------------------------------------------------------------
881 -- Variables for storing seeded attributes.
882 -- If values stored for these attributes exceed the length set
883 -- below we get the following error:
884 -- "ORA-06502: PL/SQL: numeric or value error in Package
885 -- PV_REFERRAL_GENERAL_PUB Procedure Create_Lead_Opportunity"
886 -- ------------------------------------------------------------------
887 l_decision_date VARCHAR2(30);
888 l_customer_budget VARCHAR2(100); -- Bug 4369314
889 l_sales_stage_id VARCHAR2(30);
890 l_vehicle_response_code VARCHAR2(50);
891 l_source_promotion_id VARCHAR2(30);
892 l_offer_id VARCHAR2(30);
893 l_purchase_timeframe VARCHAR2(30);
894 l_budget_status VARCHAR2(50);
895 l_opportunity_description VARCHAR2(240);
896 l_lead_description VARCHAR2(240);
897 l_exists_in_sales_team_count NUMBER;
898
899
900 -- ------------------------------------------------------------------
901 -- Variables for storing profile values.
902 -- ------------------------------------------------------------------
903 l_ASSNG_APPROVERS_TO_LEAD_OPP VARCHAR2(10);
904 l_ASSIGN_CM_TO_SALES_TRANS VARCHAR2(10);
905 l_COPY_OWNER_ON_NOTIFICATION VARCHAR2(10);
906 l_PV_INDIRECT_CHANNEL_TYPE VARCHAR2(20);
907
908 -- ------------------------------------------------------------------
909 -- Retrieves the salesforce_id (resource_id)
910 -- Is this the right query? See the one in David's script.
911 -- ------------------------------------------------------------------
912 CURSOR c_resource_id (pc_user_id NUMBER) IS
913 SELECT resource_id salesforce_id
914 FROM jtf_rs_resource_extns
915 WHERE user_id = pc_user_id;
916
917
918 -- ------------------------------------------------------------------
919 -- Retrieves the user_name for a resource.
920 -- ------------------------------------------------------------------
921 CURSOR c_user_name (pc_resource_id NUMBER) IS
922 SELECT U.user_name
923 FROM fnd_user U,
924 jtf_rs_resource_extns RES
925 WHERE U.user_id = RES.user_id AND
926 RES.resource_id = pc_resource_id;
927
928 -- ------------------------------------------------------------------
929 -- Check if a resource is already on the sales team.
930 -- ------------------------------------------------------------------
931 CURSOR c_lead_in_sales_team (pc_sales_lead_id NUMBER, pc_resource_id NUMBER) IS
932 SELECT COUNT(*) st_count
933 FROM as_accesses_all
934 WHERE sales_lead_id = pc_sales_lead_id AND
935 salesforce_id = pc_resource_id;
936
937 CURSOR c_oppty_in_sales_team (pc_lead_id NUMBER, pc_resource_id NUMBER) IS
938 SELECT COUNT(*) st_count
939 FROM as_accesses_all
940 WHERE lead_id = pc_lead_id AND
941 salesforce_id = pc_resource_id;
942
943
944 -- ------------------------------------------------------------------
945 -- Retrieves approvers
946 -- ------------------------------------------------------------------
947 CURSOR c_approvers (pc_entity_code VARCHAR2, pc_referral_id NUMBER) IS
948 SELECT RES.resource_id approver_resource_id,
949 RES.source_id person_id
950 FROM pv_ge_temp_approvers APP,
951 jtf_rs_resource_extns RES
952 WHERE APP.arc_appr_for_entity_code = pc_entity_code AND
953 APP.appr_for_entity_id = pc_referral_id AND
954 APP.approver_id = RES.user_id
955 ORDER BY APP.creation_date;
956
957
958 -- ------------------------------------------------------------------
959 -- Retrieves referral details.
960 --
961 -- Decode is make sure that if the current entity is deal registration,
962 -- set the sales transaction type to LEAD_PARTNER.
963 -- ------------------------------------------------------------------
964 CURSOR c_referral_type IS
965 SELECT BEN.benefit_type_code,
966 DECODE(BEN.benefit_type_code, 'PVDEALRN', 'LEAD_PARTNER',
967 BEN.additional_info_2) sales_transaction_type,
968 REF.customer_party_id,
969 REF.customer_party_site_id,
970 REF.customer_org_contact_id,
971 REF.customer_contact_party_id,
972 REF.currency_code,
973 REF.partner_contact_resource_id,
974 REF.partner_id,
975 REF.referral_code,
976 HZP.party_name partner_name,
977 REF.CUSTOMER_NAME customer_name
978 FROM pv_referrals_b REF,
979 pv_ge_benefits_b BEN,
980 pv_partner_profiles PROF,
981 hz_parties HZP
982 WHERE REF.referral_id = p_referral_id AND
983 REF.benefit_id = BEN.benefit_id AND
984 REF.partner_id = PROF.partner_id AND
985 PROF.partner_party_id = HZP.party_id;
986
987
988 -- ------------------------------------------------------------------
989 -- Retrieves partner contact party_id.
990 -- ------------------------------------------------------------------
991 CURSOR c_partner_contact_party_id (pc_resource_id NUMBER) IS
992 SELECT source_id
993 FROM jtf_rs_resource_extns
994 WHERE resource_id = pc_resource_id;
995
996
997 -- ------------------------------------------------------------------
998 -- Retrieves products on the referral.
999 -- ------------------------------------------------------------------
1000 CURSOR c_products IS
1001 SELECT product_category_set_id, product_category_id,
1002 quantity, amount
1003 FROM pv_referred_products
1004 WHERE referral_id = p_referral_id;
1005
1006
1007
1008 BEGIN
1009 g_api_name := 'Create_Lead_Opportunity';
1010 Debug('API called: ' || g_pkg_name || '.' || g_api_name);
1011
1012
1013 -------------------- initialize -------------------------
1014 IF FND_API.to_boolean(p_init_msg_list) THEN
1015 FND_MSG_PUB.initialize;
1016 END IF;
1017
1018 IF NOT FND_API.compatible_api_call(
1019 l_api_version,
1020 p_api_version,
1021 g_api_name,
1022 g_pkg_name
1023 ) THEN
1024 RAISE FND_API.g_exc_unexpected_error;
1025 END IF;
1026
1027 x_return_status := FND_API.G_RET_STS_SUCCESS;
1028
1029
1030 Debug('user_id : ' || fnd_global.user_id());
1031 Debug('resp_id : ' || fnd_global.resp_id());
1032 Debug('appl_id : ' || fnd_global.resp_appl_id());
1033
1034
1035 ---------------------- Source code -----------------------
1036 FOR x IN c_referral_type LOOP
1037 l_benefit_type := x.benefit_type_code;
1038 l_sales_transaction_type := x.sales_transaction_type;
1039 l_currency_code := x.currency_code;
1040 l_customer_party_id := x.customer_party_id;
1041 l_customer_party_site_id := x.customer_party_site_id;
1042 l_customer_org_contact_id := x.customer_org_contact_id;
1043 l_customer_contact_party_id := x.customer_contact_party_id;
1044 l_partner_contact_rs_id := x.partner_contact_resource_id;
1045 l_referral_code := x.referral_code;
1046 l_partner_id := x.partner_id;
1047 l_partner_org_name := x.partner_name;
1048 l_customer_name := x.customer_name;
1049 END LOOP;
1050
1051 Debug('l_benefit_type:--' || l_benefit_type);
1052
1053 IF (p_get_from_db_flag = 'N') THEN
1054 l_customer_party_id := p_customer_party_id;
1055 l_customer_party_site_id := p_customer_party_site_id;
1056 l_customer_org_contact_id := p_customer_org_contact_id;
1057 l_customer_contact_party_id := p_customer_contact_party_id;
1058 END IF;
1059
1060
1061
1062 FOR x IN c_partner_contact_party_id(l_partner_contact_rs_id) LOOP
1063 l_partner_contact_party_id := x.source_id;
1064 END LOOP;
1065
1066
1067 -- ----------------------------------------------------------------------
1068 -- Retrieve profile values
1069 -- ----------------------------------------------------------------------
1070 l_ASSNG_APPROVERS_TO_LEAD_OPP :=
1071 NVL(FND_PROFILE.VALUE('PV_ASSNG_APPROVERS_TO_LEAD_OPP'), 'N');
1072 l_ASSIGN_CM_TO_SALES_TRANS :=
1073 NVL(FND_PROFILE.VALUE('PV_ASSIGN_CM_TO_SALES_TRANS'), 'N');
1074 l_PV_INDIRECT_CHANNEL_TYPE :=
1075 FND_PROFILE.VALUE('PV_DEFAULT_INDIRECT_CHANNEL_TYPE');
1076
1077
1078
1079 Debug('PV: Assign Approvers to lead or opportunity ==> ' ||
1080 l_ASSNG_APPROVERS_TO_LEAD_OPP);
1081 Debug('PV: Assign Channel Manager to sales transactions ==> ' ||
1082 l_ASSIGN_CM_TO_SALES_TRANS);
1083 Debug('PV: Default Indirect Channel Type ==> ' ||
1084 l_PV_INDIRECT_CHANNEL_TYPE);
1085
1086 /*
1087 l_COPY_OWNER_ON_NOTIFICATION :=
1088 NVL(FND_PROFILE.VALUE('PV_COPY_OWNER_ON_NOTIFICATION'), 'N');
1089 Debug('PV_COPY_OWNER_ON_NOTIFICATION ==> ' ||
1090 l_COPY_OWNER_ON_NOTIFICATION);
1091 */
1092
1093 -- -------------------------------------------------------------------
1094 -- Retrieve the salesforce_id of the invoker.
1095 -- -------------------------------------------------------------------
1096 FOR x IN c_resource_id (l_invoker_user_id) LOOP
1097 l_invoker_resource_id := x.salesforce_id;
1098 END LOOP;
1099
1100 -- -------------------------------------------------------------------
1101 -- Retrieve the salesgroup_id of the invoker.
1102 -- -------------------------------------------------------------------
1103 l_invoker_salesgroup_id := Get_Salesgroup_ID(l_invoker_resource_id);
1104
1105 IF (l_invoker_salesgroup_id IS NULL) THEN
1106 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1107 p_msg_name => 'PV_RESOURCE_NO_SALES_GROUP',
1108 p_token1 => 'Invoker Resource ID',
1109 p_token1_value => l_invoker_resource_id);
1110
1111 --RAISE FND_API.G_EXC_ERROR;
1112 END IF;
1113
1114
1115 Debug('l_invoker_resource_id = ' || l_invoker_resource_id);
1116 Debug('l_invoker_salesgroup_id = ' || l_invoker_salesgroup_id);
1117
1118
1119 -- ----------------------------------------------------------------------
1120 -- Retrieve attribute values for seeded attributes.
1121 -- ----------------------------------------------------------------------
1122 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1123 -- Attributes for both PVDEALRN and PVREFFRL
1124 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1125 Retrieve_Attribute_Value(l_benefit_type, p_referral_id,
1126 16, l_source_promotion_id);
1127 Retrieve_Attribute_Value(l_benefit_type, p_referral_id,
1128 509, l_customer_budget);
1129 Retrieve_Attribute_Value(l_benefit_type, p_referral_id,
1130 513, l_vehicle_response_code);
1131 Retrieve_Attribute_Value(l_benefit_type, p_referral_id,
1132 603, l_offer_id);
1133
1134 -- Parse out the currency amount
1135 IF (INSTR(l_customer_budget, ':::') > 0) THEN
1136 /* For bug # 3696517*/
1137 /* We are conversinig currency value in to value in referral currency code
1138 */
1139
1140 /*l_customer_budget := SUBSTR(l_customer_budget,
1141 1,
1142 INSTR(l_customer_budget, ':::') - 1);
1143 */
1144 l_customer_budget := '' || pv_check_match_pub.Currency_Conversion(
1145 l_customer_budget,
1146 l_currency_code);
1147
1148 END IF;
1149
1150 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1151 -- Attributes for Opportunity
1152 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1153 IF (l_sales_transaction_type IN ('LEAD', 'LEAD_PARTNER')) THEN
1154 Retrieve_Attribute_Value(l_benefit_type, p_referral_id,
1155 103, l_opportunity_description);
1156 Retrieve_Attribute_Value(l_benefit_type, p_referral_id,
1157 602, l_decision_date);
1158 Retrieve_Attribute_Value(l_benefit_type, p_referral_id,
1159 601, l_sales_stage_id);
1160
1161 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1162 -- Attributes for Lead
1163 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1164 ELSIF (l_sales_transaction_type = 'SALES_LEAD') THEN
1165 Retrieve_Attribute_Value(l_benefit_type, p_referral_id,
1166 102, l_lead_description);
1167 Retrieve_Attribute_Value(l_benefit_type, p_referral_id,
1168 505, l_purchase_timeframe);
1169 Retrieve_Attribute_Value(l_benefit_type, p_referral_id,
1170 506, l_budget_status);
1171 END IF;
1172
1173
1174 -- ----------------------------------------------------------------------
1175 -- Create an opportunity for the referral/deal registration.
1176 -- ----------------------------------------------------------------------
1177 IF (l_benefit_type = 'PVDEALRN' OR
1178 (l_benefit_type = 'PVREFFRL' AND
1179 l_sales_transaction_type IN ('LEAD', 'LEAD_PARTNER')))
1180 THEN
1181 -- -------------------------------------------------------------------
1182 -- Create Opportunity Header.
1183 -- -------------------------------------------------------------------
1184 r_header_rec.customer_id := l_customer_party_id;
1185 r_header_rec.address_id := l_customer_party_site_id;
1186 r_header_rec.currency_code := l_currency_code;
1187 r_header_rec.description := l_referral_code;
1188 r_header_rec.prm_referral_code := l_referral_code;
1189
1190
1191 -- -------------------------------------------------------------------
1192 -- The channel type for 'LEAD_PARTNER' is indirect because a partner
1193 -- can only work on indirect opportunities.
1194 -- We will not set the channel type for 'LEAD'. Just leave it empty.
1195 -- -------------------------------------------------------------------
1196 IF (l_benefit_type = 'PVREFFRL') THEN
1197 IF (l_sales_transaction_type = 'LEAD_PARTNER') THEN
1198
1199 IF (l_PV_INDIRECT_CHANNEL_TYPE IS NULL) THEN
1200 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1201 p_msg_name => 'PV_NO_INDIRECT_CHANNEL_TYPE',
1202 p_token1 => null,
1203 p_token1_value => null);
1204 RAISE FND_API.G_EXC_ERROR;
1205 ELSE
1206 r_header_rec.channel_code := l_PV_INDIRECT_CHANNEL_TYPE;
1207 END IF;
1208
1209 END IF;
1210
1211 -- -------------------------------------------------------------------
1212 -- The channel type for deal registration is always indirect.
1213 -- -------------------------------------------------------------------
1214 ELSE
1215 IF (l_PV_INDIRECT_CHANNEL_TYPE IS NULL) THEN
1216 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1217 p_msg_name => 'PV_NO_INDIRECT_CHANNEL_TYPE',
1218 p_token1 => null,
1219 p_token1_value => null);
1220 RAISE FND_API.G_EXC_ERROR;
1221 ELSE
1222 r_header_rec.channel_code := l_PV_INDIRECT_CHANNEL_TYPE;
1223 END IF;
1224
1225 END IF;
1226
1227 -- -------------------------------------------------------------------
1228 -- Seeded attributes.
1229 -- -------------------------------------------------------------------
1230 r_header_rec.decision_date := TO_DATE(l_decision_date, 'YYYYMMDDHH24MISS');
1231 r_header_rec.sales_stage_id := TO_NUMBER(l_sales_stage_id);
1232 r_header_rec.vehicle_response_code := l_vehicle_response_code;
1233 --r_header_rec.source_promotion_id := 14886;
1234 r_header_rec.source_promotion_id := TO_NUMBER(l_source_promotion_id);
1235 r_header_rec.offer_id := TO_NUMBER(l_offer_id);
1236 r_header_rec.customer_budget := TO_NUMBER(l_customer_budget);
1237
1238
1239 -- -------------------------------------------------------------------
1240 -- Create Opportunity Header.
1241 -- -------------------------------------------------------------------
1242 Debug('Creating opportunity header.........................................');
1243
1244 BEGIN
1245 MO_GLOBAL.Init('PV');
1246 l_org_id := mo_utils.get_default_oRG_ID();
1247 MO_GLOBAL.set_policy_context('S', l_org_id);
1248 EXCEPTION
1249 WHEN OTHERS THEN
1250 RAISE FND_API.G_EXC_ERROR;
1251 END;
1252 r_header_rec.org_id := l_org_id;
1253
1254
1255 AS_OPPORTUNITY_PUB.Create_Opp_Header (
1256 p_api_version_number => 2.0,
1257 p_header_rec => r_header_rec,
1258 p_check_access_flag => 'N',
1259 p_admin_flag => 'N',
1260 p_admin_group_id => null,
1261 p_identity_salesforce_id => l_invoker_resource_id,
1262 p_salesgroup_id => l_invoker_salesgroup_id,
1263 p_partner_cont_party_id => null,
1264 p_profile_tbl => AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1265 x_return_status => x_return_status,
1266 x_msg_count => x_msg_count,
1267 x_msg_data => x_msg_data,
1268 x_lead_id => x_entity_id
1269 );
1270
1271 Debug('Return Status: ' || x_return_status);
1272
1273 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1274 RAISE FND_API.G_EXC_ERROR;
1275
1276 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1277 RAISE FND_API.g_exc_unexpected_error;
1278 END IF;
1279
1280 x_entity_type := 'LEAD';
1281
1282
1283 -- -------------------------------------------------------------------
1284 -- Create Opportunity Lines.
1285 -- -------------------------------------------------------------------
1286 r_header_rec := r_empty_header_rec;
1287 r_header_rec.lead_id := x_entity_id;
1288
1289 FOR x IN c_products LOOP
1290 l_line_tbl(i).lead_id := x_entity_id;
1291 l_line_tbl(i).product_category_id := x.product_category_id;
1292 l_line_tbl(i).product_cat_set_id := x.product_category_set_id;
1293 l_line_tbl(i).quantity := x.quantity;
1294 l_line_tbl(i).total_amount := x.amount;
1295
1296 i := i + 1;
1297 END LOOP;
1298
1299 Debug('Creating Opportunity lines.........................................');
1300
1301 AS_OPPORTUNITY_PUB.Create_Opp_Lines (
1302 p_api_version_number => 2.0,
1303 p_line_tbl => l_line_tbl,
1304 p_header_rec => r_header_rec,
1305 p_check_access_flag => 'N',
1306 p_admin_flag => 'N',
1307 p_admin_group_id => null,
1308 p_identity_salesforce_id => l_invoker_resource_id,
1309 p_salesgroup_id => l_invoker_salesgroup_id,
1310 p_partner_cont_party_id => null,
1311 p_profile_tbl => AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1312 x_line_out_tbl => l_line_out_tbl,
1313 x_return_status => x_return_status,
1314 x_msg_count => x_msg_count,
1315 x_msg_data => x_msg_data
1316 );
1317
1318 Debug('Return Status: ' || x_return_status);
1319
1320 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1321 RAISE FND_API.G_EXC_ERROR;
1322
1323 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1324 RAISE FND_API.g_exc_unexpected_error;
1325 END IF;
1326
1327
1328 -- -------------------------------------------------------------------
1329 -- Create Opportunity Contact.
1330 -- -------------------------------------------------------------------
1331 l_contact_tbl(1).lead_id := x_entity_id;
1332 l_contact_tbl(1).customer_id := l_customer_party_id;
1333 l_contact_tbl(1).enabled_flag := 'Y';
1334 l_contact_tbl(1).address_id := l_customer_party_site_id;
1335 l_contact_tbl(1).contact_id := l_customer_org_contact_id;
1336 l_contact_tbl(1).contact_party_id := l_customer_contact_party_id;
1337 l_contact_tbl(1).primary_contact_flag := 'Y';
1338
1339 Debug('Creating Opportunity contacts.........................................');
1340
1341 AS_OPPORTUNITY_PUB.Create_Contacts (
1342 p_api_version_number => 2.0,
1343 p_identity_salesforce_id => l_invoker_resource_id,
1344 p_contact_tbl => l_contact_tbl,
1345 p_header_rec => r_header_rec,
1346 p_check_access_flag => 'N',
1347 p_admin_flag => 'N',
1348 p_admin_group_id => null,
1349 p_partner_cont_party_id => null,
1350 p_profile_tbl => AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1351 x_contact_out_tbl => l_contact_out_tbl,
1352 x_return_status => x_return_status,
1353 x_msg_count => x_msg_count,
1354 x_msg_data => x_msg_data
1355 );
1356
1357 Debug('Return Status: ' || x_return_status);
1358 Debug('# of Contacts: ' || l_contact_out_tbl.COUNT);
1359
1360 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1361 RAISE FND_API.G_EXC_ERROR;
1362
1363 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1364 RAISE FND_API.g_exc_unexpected_error;
1365 END IF;
1366
1367
1368 -- -------------------------------------------------------------------
1369 -- Log the fact that the opportunity has been created.
1370 -- -------------------------------------------------------------------
1371 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1372 p_msg_name => 'PV_REFERRAL_OPPTY_CREATION',
1373 p_token1 => 'OPPTY_NAME',
1374 p_token1_value => l_referral_code,
1375 p_token2 => 'USER',
1376 p_token2_value => FND_GLOBAL.USER_NAME());
1377
1378
1379 -- 0000000000000000000000000000000000000000000000000000000000000000000000
1380 -- 0000000000000000000000000000000000000000000000000000000000000000000000
1381 -- Create a lead for the referral.
1382 -- 0000000000000000000000000000000000000000000000000000000000000000000000
1383 -- 0000000000000000000000000000000000000000000000000000000000000000000000
1384 ELSIF (l_benefit_type = 'PVREFFRL' AND
1385 l_sales_transaction_type = 'SALES_LEAD')
1386 THEN
1387 Debug('Creating a lead for the referral...............................');
1388
1389 -- -------------------------------------------------------------------
1390 -- Set lead header record.
1391 -- -------------------------------------------------------------------
1392 r_lead_header_rec.last_update_date := SYSDATE;
1393 r_lead_header_rec.last_updated_by := l_invoker_user_id;
1394 r_lead_header_rec.creation_date := SYSDATE;
1395 r_lead_header_rec.created_by := l_invoker_user_id;
1396 r_lead_header_rec.last_update_login := l_invoker_user_id;
1397 r_lead_header_rec.status_Code := 'NEW';
1398 r_lead_header_rec.customer_id := l_customer_party_id;
1399 r_lead_header_rec.address_id := l_customer_party_site_id;
1400 r_lead_header_rec.currency_code := l_currency_code;
1401
1402 -- the lead engine will determine the channel code.
1403 -- r_lead_header_rec.channel_code := 'DIRECT';
1404
1405 r_lead_header_rec.description := l_referral_code;
1406 r_lead_header_rec.budget_amount := TO_NUMBER(l_customer_budget);
1407 r_lead_header_rec.vehicle_response_code := l_vehicle_response_code;
1408 r_lead_header_rec.budget_status_code := l_budget_status;
1409 r_lead_header_rec.decision_timeframe_code := l_purchase_timeframe;
1410 r_lead_header_rec.source_promotion_id := TO_NUMBER(l_source_promotion_id);
1411 r_lead_header_rec.offer_id := TO_NUMBER(l_offer_id);
1412 r_lead_header_rec.source_system := 'REFERRAL';
1413 r_lead_header_rec.source_primary_reference := l_referral_code;
1414
1415
1416 -- -------------------------------------------------------------------
1417 -- Profile: PV_ASSNG_APPROVERS_TO_LEAD_OPP (Profile #1)
1418 -- Profile: PV_ASSIGN_CM_TO_SALES_TRANS (Profile #2)
1419 -- -------------------------------------------------------------------
1420 --
1421 -- Set the lead owner to the channel manager or the first approver of
1422 -- the referral. Profile #2 overrides Profile #1. THat is, if
1423 -- Profile #2 is set to 'YES', the owner of the lead would be the
1424 -- channel manager. In case there are more than one channel managers,
1425 -- just randomly pick one.
1426 -- -------------------------------------------------------------------
1427 IF (l_ASSIGN_CM_TO_SALES_TRANS = 'Y') THEN
1428 Debug('====================================================================');
1429 Debug('Profile: PV_ASSIGN_CM_TO_SALES_TRANS (Profile #2) = ''Y''');
1430 Debug('Make channel manager the owner of the lead................');
1431 Debug('====================================================================');
1432
1433 -- --------------------------------------------------------------------------
1434 -- Retrieve all channel managers of the partner.
1435 -- --------------------------------------------------------------------------
1436 pv_assign_util_pvt.get_partner_info (
1437 p_api_version_number => 1.0,
1438 p_mode => null,
1439 p_partner_id => l_partner_id,
1440 p_entity => 'LEAD',
1441 p_entity_id => null,
1442 p_retrieve_mode => 'CM',
1443 x_rs_details_tbl => l_channel_manager_tbl,
1444 x_vad_id => l_vad_id,
1445 x_return_status => x_return_status,
1446 x_msg_count => x_msg_count,
1447 x_msg_data => x_msg_data
1448 );
1449
1450 -- --------------------------------------------------------------------
1451 -- Set the lead owner to the first channel manager returned.
1452 -- --------------------------------------------------------------------
1453 r_lead_header_rec.assign_to_salesforce_id := l_channel_manager_tbl(1).resource_id;
1454 r_lead_header_rec.assign_to_person_id := l_channel_manager_tbl(1).person_id;
1455 r_lead_header_rec.assign_sales_group_id := Get_Salesgroup_ID(l_channel_manager_tbl(1).resource_id);
1456
1457
1458 ELSIF (l_ASSNG_APPROVERS_TO_LEAD_OPP = 'Y') THEN
1459 Debug('====================================================================');
1460 Debug('Profile: PV_ASSNG_APPROVERS_TO_LEAD_OPP (Profile #1) = ''Y''');
1461 Debug('Set the lead owner to the first approver of the referral');
1462 Debug('====================================================================');
1463
1464 FOR x IN c_approvers (l_benefit_type, p_referral_id) LOOP
1465 Debug('Approver Resource ID: ' || x.approver_resource_id);
1466
1467 -- ----------------------------------------------------------------
1468 -- lead owner - assign_to_salesforce_id
1469 -- ----------------------------------------------------------------
1470 r_lead_header_rec.assign_to_salesforce_id := x.approver_resource_id;
1471 r_lead_header_rec.assign_to_person_id := x.person_id;
1472 r_lead_header_rec.assign_sales_group_id := Get_Salesgroup_ID(x.approver_resource_id);
1473
1474 EXIT; -- we only need to get the first approver.
1475 END LOOP;
1476 END IF;
1477
1478
1479 -- -------------------------------------------------------------------
1480 -- Set lead line record.
1481 -- -------------------------------------------------------------------
1482 i := 1;
1483 FOR x IN c_products LOOP
1484 l_lead_line_tbl(i).last_update_date := SYSDATE;
1485 l_lead_line_tbl(i).last_updated_by := l_invoker_user_id;
1486 l_lead_line_tbl(i).creation_date := SYSDATE;
1487 l_lead_line_tbl(i).created_by := l_invoker_user_id;
1488 l_lead_line_tbl(i).last_update_login := l_invoker_user_id;
1489 l_lead_line_tbl(i).category_id := x.product_category_id;
1490 l_lead_line_tbl(i).category_set_id := x.product_category_set_id;
1491 l_lead_line_tbl(i).quantity := x.quantity;
1492 l_lead_line_tbl(i).budget_amount := x.amount;
1493
1494 i := i + 1;
1495 END LOOP;
1496
1497 -- -------------------------------------------------------------------
1498 -- Set lead contact record.
1499 -- -------------------------------------------------------------------
1500 l_lead_contact_tbl(1).last_update_date := SYSDATE;
1501 l_lead_contact_tbl(1).last_updated_by := l_invoker_user_id;
1502 l_lead_contact_tbl(1).creation_date := SYSDATE;
1503 l_lead_contact_tbl(1).created_by := l_invoker_user_id;
1504 l_lead_contact_tbl(1).last_update_login := l_invoker_user_id;
1505 l_lead_contact_tbl(1).customer_id := l_customer_party_id;
1506 l_lead_contact_tbl(1).enabled_flag := 'Y';
1507 l_lead_contact_tbl(1).address_id := l_customer_party_site_id;
1508 l_lead_contact_tbl(1).contact_id := l_customer_org_contact_id;
1509 l_lead_contact_tbl(1).contact_party_id := l_customer_contact_party_id;
1510 l_lead_contact_tbl(1).primary_contact_flag := 'Y';
1511
1512
1513 Debug('Calling AML_SALES_LEADS_V2_PUB.Create_SALES_LEAD API.............');
1514
1515 AML_SALES_LEADS_V2_PUB.Create_SALES_LEAD (
1516 p_api_version_number => 2.0,
1517 p_Check_Access_Flag => 'N', -- does not require the invoker to be on the
1518 -- sales team (as_accesses_all).
1519 p_Admin_Flag => 'N',
1520 p_admin_group_id => null,
1521 p_identity_salesforce_id => l_invoker_resource_id,
1522 p_salesgroup_id => l_invoker_salesgroup_id,
1523 p_Sales_Lead_Profile_Tbl => AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1524 p_sales_lead_rec => r_lead_header_rec,
1525 p_sales_lead_line_tbl => l_lead_line_tbl,
1526 p_sales_lead_contact_tbl => l_lead_contact_tbl,
1527 x_Sales_lead_id => x_entity_id,
1528 x_SALES_LEAD_LINE_OUT_Tbl => l_lead_line_out_tbl,
1529 x_SALES_LEAD_CNT_OUT_Tbl => l_lead_contact_out_tbl,
1530 x_note_id => l_lead_note_id,
1531 x_return_status => x_return_status,
1532 x_msg_count => x_msg_count,
1533 x_msg_data => x_msg_data
1534 );
1535
1536 Debug('Return Status: ' || x_return_status);
1537
1538 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1539 RAISE FND_API.G_EXC_ERROR;
1540
1541 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1542 RAISE FND_API.g_exc_unexpected_error;
1543 END IF;
1544
1545
1546 x_entity_type := 'SALES_LEAD';
1547
1548 -- -------------------------------------------------------------------
1549 -- Log the fact that the lead has been created.
1550 -- -------------------------------------------------------------------
1551 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1552 p_msg_name => 'PV_REFERRAL_LEAD_CREATION',
1553 p_token1 => 'LEAD_NAME',
1554 p_token1_value => l_referral_code,
1555 p_token2 => 'USER',
1556 p_token2_value => FND_GLOBAL.USER_NAME());
1557
1558 END IF;
1559
1560
1561 -- 00000000000000000000000000000000000000000000000000000000000000000000000
1562 -- 00000000000000000000000000000000000000000000000000000000000000000000000
1563 -- Sales Team
1564 -- 00000000000000000000000000000000000000000000000000000000000000000000000
1565 -- 00000000000000000000000000000000000000000000000000000000000000000000000
1566
1567 -- -------------------------------------------------------------------
1568 -- Profile: PV_ASSNG_APPROVERS_TO_LEAD_OPP (Profile #1)
1569 -- -------------------------------------------------------------------
1570 --
1571 -- Add the approvers to the sales team of the opportunity.
1572 -- -------------------------------------------------------------------
1573 IF (l_ASSNG_APPROVERS_TO_LEAD_OPP = 'Y') THEN
1574 Debug('====================================================================');
1575 Debug('Profile: PV_ASSNG_APPROVERS_TO_LEAD_OPP (Profile #1) = ''Y''');
1576 Debug('Add approvers to the sales team of the opportunity/lead.............');
1577 Debug('====================================================================');
1578
1579 IF (l_sales_transaction_type IN ('LEAD', 'LEAD_PARTNER')) THEN
1580 l_sales_team_rec.lead_id := x_entity_id;
1581 ELSE
1582 l_sales_team_rec.sales_lead_id := x_entity_id;
1583 END IF;
1584
1585 l_sales_team_rec.customer_id := l_customer_party_id;
1586 l_sales_team_rec.address_id := l_customer_party_site_id;
1587 l_sales_team_rec.freeze_flag := 'Y'; -- keep_flag
1588 l_sales_team_rec.team_leader_flag := 'Y'; -- full access
1589
1590 l_access_profile_rec := null; -- always set it to null
1591
1592 -- --------------------------------------------------------------------------
1593 -- Add all approvers to the sales team.
1594 -- --------------------------------------------------------------------------
1595 FOR x IN c_approvers (l_benefit_type, p_referral_id) LOOP
1596 -- -----------------------------------------------------------------------
1597 -- Check if the resource (the approver) already exists on the sales team.
1598 -- -----------------------------------------------------------------------
1599 IF (l_sales_transaction_type IN ('LEAD', 'LEAD_PARTNER')) THEN
1600 FOR z IN c_oppty_in_sales_team(x_entity_id, x.approver_resource_id) LOOP
1601 l_exists_in_sales_team_count := z.st_count;
1602 END LOOP;
1603 ELSE
1604 FOR z IN c_lead_in_sales_team(x_entity_id, x.approver_resource_id) LOOP
1605 l_exists_in_sales_team_count := z.st_count;
1606 END LOOP;
1607 END IF;
1608
1609
1610 IF (l_exists_in_sales_team_count = 0) THEN
1611 Debug('Approver Resource ID: ' || x.approver_resource_id);
1612 l_sales_team_rec.salesforce_id := x.approver_resource_id;
1613 l_sales_team_rec.sales_group_id := Get_Salesgroup_ID(x.approver_resource_id);
1614 l_sales_team_rec.person_id := x.person_id;
1615
1616 -- -----------------------------------------------------------------------
1617 -- Add the resource to the sales team only if the resource belongs to
1618 -- a sales group.
1619 -- -----------------------------------------------------------------------
1620 IF (l_sales_team_rec.sales_group_id IS NULL) THEN
1621 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1622 p_msg_name => 'PV_RESOURCE_NO_SALES_GROUP',
1623 p_token1 => 'Approver Resource ID',
1624 p_token1_value => x.approver_resource_id);
1625
1626 --RAISE FND_API.G_EXC_ERROR;
1627
1628 ELSE
1629 as_access_pub.Create_SalesTeam(
1630 p_api_version_number => 2,
1631 p_init_msg_list => FND_API.G_FALSE,
1632 p_commit => FND_API.G_FALSE,
1633 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1634 p_access_profile_rec => l_access_profile_rec,
1635 p_check_access_flag => 'N',
1636 p_admin_flag => 'N',
1637 p_admin_group_id => null,
1638 p_identity_salesforce_id => l_invoker_resource_id,
1639 p_sales_team_rec => l_sales_team_rec,
1640 x_return_status => x_return_status,
1641 x_msg_count => x_msg_count,
1642 x_msg_data => x_msg_data,
1643 x_access_id => l_access_id);
1644
1645 Debug('Return Status: ' || x_return_status);
1646
1647 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1648 RAISE FND_API.G_EXC_ERROR;
1649
1650 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1651 RAISE FND_API.g_exc_unexpected_error;
1652 END IF;
1653 END IF;
1654
1655 END IF;
1656 END LOOP;
1657 END IF;
1658 -- ----------------------------------------------------------------------------------
1659 -- -----------------Profile: PV_ASSNG_APPROVERS_TO_LEAD_OPP--------------------------
1660 -- -----------------Profile #1 --------------------------
1661 -- ----------------------------------------------------------------------------------
1662
1663
1664 -- ----------------------------------------------------------------------------------
1665 -- Profile: PV_ASSIGN_CM_TO_SALES_TRANS (Profile #2)
1666 -- ----------------------------------------------------------------------------------
1667 --
1668 -- Add the channel managers to the sales team of the opportunity.
1669 --
1670 -- Note that we will only add channel managers to the sales team if
1671 -- the sales transaction type is 'LEAD'. This is because if the type
1672 -- is 'LEAD_PARTNER', the channel managers will automatically be added
1673 -- in the following step when Notify_CM_On_Create_Oppty is invoked.
1674 --
1675 -- ----------------------------------------------------------------------------------
1676 IF (l_ASSIGN_CM_TO_SALES_TRANS = 'Y' AND l_sales_transaction_type = 'LEAD') THEN
1677 Debug('====================================================================');
1678 Debug('Profile: PV_ASSIGN_CM_TO_SALES_TRANS (Profile #2) = ''Y''');
1679 Debug('Add channel managers to the sales team of the opportunity/lead......');
1680 Debug('====================================================================');
1681
1682 l_sales_team_rec := l_empty_sales_team_rec;
1683
1684 IF (l_sales_transaction_type IN ('LEAD', 'LEAD_PARTNER')) THEN
1685 l_sales_team_rec.lead_id := x_entity_id;
1686 ELSE
1687 l_sales_team_rec.sales_lead_id := x_entity_id;
1688 END IF;
1689
1690 l_sales_team_rec.customer_id := l_customer_party_id;
1691 l_sales_team_rec.address_id := l_customer_party_site_id;
1692 l_sales_team_rec.freeze_flag := 'Y'; -- keep_flag
1693 l_sales_team_rec.team_leader_flag := 'Y'; -- full access
1694
1695 l_access_profile_rec := null; -- always set it to null
1696
1697 -- --------------------------------------------------------------------------
1698 -- Retrieve all channel managers of the partner.
1699 -- --------------------------------------------------------------------------
1700 IF (l_sales_transaction_type IN ('LEAD', 'LEAD_PARTNER')) THEN
1701 l_entity_type := 'OPPORTUNITY';
1702 ELSE
1703 l_entity_type := 'LEAD';
1704 END IF;
1705
1706 pv_assign_util_pvt.get_partner_info (
1707 p_api_version_number => 1.0,
1708 p_mode => null,
1709 p_partner_id => l_partner_id,
1710 p_entity => l_entity_type,
1711 p_entity_id => x_entity_id,
1712 p_retrieve_mode => 'CM',
1713 x_rs_details_tbl => l_channel_manager_tbl,
1714 x_vad_id => l_vad_id,
1715 x_return_status => x_return_status,
1716 x_msg_count => x_msg_count,
1717 x_msg_data => x_msg_data
1718 );
1719
1720 -- --------------------------------------------------------------------------
1721 -- Add all channel managers of the partner to the sales team.
1722 -- --------------------------------------------------------------------------
1723 FOR i IN 1..l_channel_manager_tbl.COUNT LOOP
1724 -- -----------------------------------------------------------------------
1725 -- Check if the resource (the CM) already exists on the sales team.
1726 -- -----------------------------------------------------------------------
1727 IF (l_sales_transaction_type IN ('LEAD', 'LEAD_PARTNER')) THEN
1728 FOR z IN c_oppty_in_sales_team(x_entity_id, l_channel_manager_tbl(i).resource_id) LOOP
1729 l_exists_in_sales_team_count := z.st_count;
1730 END LOOP;
1731 ELSE
1732 FOR z IN c_lead_in_sales_team(x_entity_id, l_channel_manager_tbl(i).resource_id) LOOP
1733 l_exists_in_sales_team_count := z.st_count;
1734 END LOOP;
1735 END IF;
1736
1737 -- -----------------------------------------------------------------------
1738 -- Add the CM to the sales team only if it's not already on the team.
1739 -- -----------------------------------------------------------------------
1740 IF (l_exists_in_sales_team_count = 0) THEN
1741 Debug('Channel Manager Resource ID: ' || l_channel_manager_tbl(i).resource_id);
1742 l_sales_team_rec.salesforce_id := l_channel_manager_tbl(i).resource_id;
1743 l_sales_team_rec.sales_group_id := Get_Salesgroup_ID(l_channel_manager_tbl(i).resource_id);
1744
1745 l_sales_team_rec.person_id := l_channel_manager_tbl(i).person_id;
1746
1747 IF (l_sales_team_rec.sales_group_id IS NULL) THEN
1748 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1749 p_msg_name => 'PV_RESOURCE_NO_SALES_GROUP',
1750 p_token1 => 'Channel Manager Resource ID',
1751 p_token1_value => l_channel_manager_tbl(i).resource_id);
1752
1753 --RAISE FND_API.G_EXC_ERROR;
1754 END IF;
1755
1756
1757 as_access_pub.Create_SalesTeam(
1758 p_api_version_number => 2,
1759 p_init_msg_list => FND_API.G_FALSE,
1760 p_commit => FND_API.G_FALSE,
1761 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1762 p_access_profile_rec => l_access_profile_rec,
1763 p_check_access_flag => 'N',
1764 p_admin_flag => 'N',
1765 p_admin_group_id => null,
1766 p_identity_salesforce_id => l_invoker_resource_id,
1767 p_sales_team_rec => l_sales_team_rec,
1768 x_return_status => x_return_status,
1769 x_msg_count => x_msg_count,
1770 x_msg_data => x_msg_data,
1771 x_access_id => l_access_id);
1772
1773 Debug('Return Status: ' || x_return_status);
1774
1775 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1776 RAISE FND_API.G_EXC_ERROR;
1777
1778 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1779 RAISE FND_API.g_exc_unexpected_error;
1780 END IF;
1781 END IF;
1782 END LOOP;
1783 END IF;
1784 -- ----------------------------------------------------------------------------------
1785 -- -----------------Profile: PV_ASSIGN_CM_TO_SALES_TRANS-----------------------------
1786 -- -----------------Profile #2 --------------------------
1787 -- ----------------------------------------------------------------------------------
1788
1789
1790
1791 -- -------------------------------------------------------------------
1792 -- Add the partner contact to the sales team of the opportunity.
1793 -- -------------------------------------------------------------------
1794 IF (l_benefit_type = 'PVDEALRN' OR
1795 (l_benefit_type = 'PVREFFRL' AND l_sales_transaction_type = 'LEAD_PARTNER'))
1796 THEN
1797 Debug('====================================================================');
1798 Debug('Add the partner contact to the sales team of the opportunity........');
1799 Debug('====================================================================');
1800
1801 -- -------------------------------------------------------------------
1802 -- Retrieve the salesgroup_id of the partner contact.
1803 -- -------------------------------------------------------------------
1804 l_pt_salesgroup_id := Get_Salesgroup_ID(l_partner_contact_rs_id);
1805
1806 IF (l_pt_salesgroup_id IS NULL) THEN
1807 Set_Message(p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1808 p_msg_name => 'PV_RESOURCE_NO_SALES_GROUP',
1809 p_token1 => 'Partner Contact Resource ID',
1810 p_token1_value => l_partner_contact_rs_id);
1811
1812 --RAISE FND_API.G_EXC_ERROR;
1813 END IF;
1814
1815
1816 -- ----------------------------------------------------------
1817 -- Note: Both opportunity and lead uses this field: lead_id
1818 -- ----------------------------------------------------------
1819 l_sales_team_rec := l_empty_sales_team_rec;
1820 l_sales_team_rec.lead_id := x_entity_id;
1821 l_sales_team_rec.person_id := null; -- external user does not have this id
1822 l_sales_team_rec.partner_cont_party_id := l_partner_contact_party_id;
1823 l_sales_team_rec.salesforce_id := l_partner_contact_rs_id;
1824 l_sales_team_rec.customer_id := l_customer_party_id;
1825 l_sales_team_rec.freeze_flag := 'Y'; -- keep_flag
1826 l_sales_team_rec.address_id := l_customer_party_site_id;
1827 l_sales_team_rec.team_leader_flag := 'Y'; -- full access
1828 l_sales_team_rec.sales_group_id := l_pt_salesgroup_id;
1829
1830 l_access_profile_rec := null; -- always set it to null
1831
1832 as_access_pub.Create_SalesTeam(
1833 p_api_version_number => 2,
1834 p_init_msg_list => FND_API.G_FALSE,
1835 p_commit => FND_API.G_FALSE,
1836 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1837 p_access_profile_rec => l_access_profile_rec,
1838 p_check_access_flag => 'N', -- disable certain validations.
1839 -- use this option for running in the background
1840 p_admin_flag => 'N',
1841 p_admin_group_id => null,
1842 p_identity_salesforce_id => l_invoker_resource_id,
1843 p_sales_team_rec => l_sales_team_rec,
1844 x_return_status => x_return_status,
1845 x_msg_count => x_msg_count,
1846 x_msg_data => x_msg_data,
1847 x_access_id => l_access_id); -- primary key of the as_accesses_all.
1848 -- we don't need it.
1849
1850 Debug('Return Status: ' || x_return_status);
1851
1852 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1853 RAISE FND_API.G_EXC_ERROR;
1854
1855 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1856 RAISE FND_API.g_exc_unexpected_error;
1857 END IF;
1858
1859
1860
1861 -- -------------------------------------------------------------------
1862 -- Notify channel managers and take care of PV Security.
1863 --
1864 -- PV has a layer of security on top of OSO security. In order for
1865 -- partners to see the opportunities on the summary page, we have to
1866 -- add a row to the following tables:
1867 -- * pv_lead_workflows
1868 -- * pv_lead_assignments
1869 -- * pv_party_notifications
1870 --
1871 -- pv_opportunity_vhuk.Notify_CM_On_Create_Oppty API will do all of the
1872 -- above. In addition, it will identify the channel managers and
1873 -- partner contacts and notify them. Moreover, it will add the channel
1874 -- managers to the sales team of the opportunity.
1875 --
1876 -- Note that in order for the API to do all of the above,
1877 -- p_salesforce_id (partner contact resource id) has to be an external
1878 -- user!!!
1879 -- -------------------------------------------------------------------
1880 Debug('Notify channel managers of the created opportunity.............');
1881
1882 r_opp_header_rec.lead_id := x_entity_id;
1883 r_opp_header_rec.customer_id := l_customer_party_id;
1884 r_opp_header_rec.customer_name := l_customer_name;
1885 r_opp_header_rec.description := l_referral_code;
1886 r_opp_header_rec.address_id := l_customer_party_site_id;
1887
1888 -- retrieves partner contact user_name
1889 FOR x IN c_user_name (l_partner_contact_rs_id) LOOP
1890 l_partner_contact_username := x.user_name;
1891 END LOOP;
1892
1893 Debug('l_partner_contact_rs_id : ' || l_partner_contact_rs_id);
1894 Debug('l_partner_contact_username: ' || l_partner_contact_username);
1895
1896 pv_opportunity_vhuk.Notify_CM_On_Create_Oppty (
1897 p_api_version_number => 1.0,
1898 p_init_msg_list => FND_API.G_FALSE,
1899 p_commit => FND_API.G_FALSE,
1900 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1901 p_oppty_header_rec => r_opp_header_rec,
1902 p_salesforce_id => l_partner_contact_rs_id,
1903 p_relationship_type => 'PARTNER_OF',
1904 p_party_relation_id => l_partner_id,
1905 p_user_name => l_partner_contact_username,
1906 p_party_name => l_partner_org_name,
1907 p_partner_type => 'PARTNER',
1908 x_return_status => x_return_status,
1909 x_msg_count => x_msg_count,
1910 x_msg_data => x_msg_data
1911 );
1912
1913 Debug('Return Status: ' || x_return_status);
1914
1915 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1916 RAISE FND_API.G_EXC_ERROR;
1917
1918 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1919 RAISE FND_API.g_exc_unexpected_error;
1920 END IF;
1921
1922 END IF;
1923
1924 -- -------------------------------------------------------------------
1925 --settting it to Success because to avoid returing Warning return status which is being thrown by SALes LEad APIS.
1926 --for bug# 3899855
1927 -- -------------------------------------------------------------------
1928 x_return_status := FND_API.G_RET_STS_SUCCESS;
1929
1930 -------------------- Exception --------------------------
1931 EXCEPTION
1932 WHEN FND_API.G_EXC_ERROR THEN
1933 ROLLBACK;
1934 x_return_status := FND_API.G_RET_STS_ERROR;
1935 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1936 p_count => x_msg_count,
1937 p_data => x_msg_data);
1938
1939 WHEN FND_API.g_exc_unexpected_error THEN
1940 ROLLBACK;
1941 x_return_status := FND_API.g_ret_sts_unexp_error;
1942 FND_MSG_PUB.count_and_get(
1943 p_encoded => FND_API.g_false,
1944 p_count => x_msg_count,
1945 p_data => x_msg_data
1946 );
1947
1948 WHEN OTHERS THEN
1949 ROLLBACK;
1950 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1951 FND_MSG_PUB.add_exc_msg(g_pkg_name, g_api_name);
1952 END IF;
1953
1954 x_return_status := FND_API.G_RET_STS_ERROR;
1955 FND_MSG_PUB.count_and_get(
1956 p_encoded => FND_API.g_false,
1957 p_count => x_msg_count,
1958 p_data => x_msg_data
1959 );
1960
1961 END;
1962 -- ======================End of Create_Lead_Opportunity==========================
1963
1964
1965
1966 --=============================================================================+
1967 --| Private Function |
1968 --| |
1969 --| Link_Lead_Opportunity |
1970 --| |
1971 --| Parameters |
1972 --| IN |
1973 --| OUT |
1974 --| |
1975 --| |
1976 --| NOTES: |
1977 --| |
1978 --| HISTORY |
1979 --| |
1980 --==============================================================================
1981 PROCEDURE Link_Lead_Opportunity (
1982 p_api_version IN NUMBER,
1983 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1984 p_commit IN VARCHAR2 := FND_API.g_false,
1985 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1986 p_referral_id IN VARCHAR2,
1987 p_entity_type IN VARCHAR2, -- 'LEAD', 'SALES_LEAD'
1988 p_entity_id IN NUMBER,
1989 x_a_link_already_exists OUT NOCOPY VARCHAR2,
1990 x_return_status OUT NOCOPY VARCHAR2,
1991 x_msg_count OUT NOCOPY NUMBER,
1992 x_msg_data OUT NOCOPY VARCHAR2
1993 )
1994 IS
1995 l_count NUMBER;
1996 l_api_version NUMBER := 1;
1997 l_benefit_type VARCHAR2(30);
1998 l_log_params_tbl pvx_utility_pvt.log_params_tbl_type;
1999 l_referral_id NUMBER;
2000 l_partner_id NUMBER;
2001 l_referral_code VARCHAR2(50);
2002
2003 -- ----------------------------------------------------------------------
2004 -- Is this opportunity already linked to another referral?
2005 -- ----------------------------------------------------------------------
2006 CURSOR c_opportunity(pc_entity_id NUMBER) IS
2007 SELECT COUNT(*) lead_count
2008 FROM as_leads_all
2009 WHERE lead_id = pc_entity_id AND
2010 prm_referral_code IS NOT NULL;
2011
2012 -- ----------------------------------------------------------------------
2013 -- Is this lead already linked to another referral?
2014 -- ----------------------------------------------------------------------
2015 CURSOR c_lead(pc_entity_id NUMBER) IS
2016 SELECT source_system, source_primary_reference
2017 FROM as_sales_leads
2018 WHERE sales_lead_id = pc_entity_id;
2019
2020 CURSOR c_benefit_type IS
2021 SELECT a.benefit_type_code, b.referral_id, b.partner_id
2022 FROM pv_ge_benefits_vl a, pv_referrals_vl b
2023 WHERE a.benefit_id = b.benefit_id AND
2024 b.referral_id = p_referral_id;
2025
2026 BEGIN
2027 g_api_name := 'Link_Lead_Opportunity';
2028 Debug('API called: ' || g_pkg_name || '.' || g_api_name);
2029
2030 -------------------- initialize -------------------------
2031 IF FND_API.to_boolean(p_init_msg_list) THEN
2032 FND_MSG_PUB.initialize;
2033 END IF;
2034
2035 IF NOT FND_API.compatible_api_call(
2036 l_api_version,
2037 p_api_version,
2038 g_api_name,
2039 g_pkg_name
2040 ) THEN
2041 RAISE FND_API.g_exc_unexpected_error;
2042 END IF;
2043
2044 x_return_status := FND_API.G_RET_STS_SUCCESS;
2045 x_a_link_already_exists := 'N';
2046
2047 FOR x IN (SELECT referral_code
2048 FROM pv_referrals_b
2049 WHERE referral_id = p_referral_id)
2050 LOOP
2051 l_referral_code := x.referral_code;
2052 END LOOP;
2053
2054 -- -----------------------------------------------------------------------
2055 -- Validation: Is this lead/opportunity already linked to a referral.
2056 -- -----------------------------------------------------------------------
2057 IF (p_entity_type = 'LEAD') THEN
2058 FOR x IN c_opportunity(p_entity_id) LOOP
2059 l_count := x.lead_count;
2060 END LOOP;
2061
2062 -- --------------------------------------------------------------------
2063 -- Link the opporutnity to the referral only if it's not already linked
2064 -- to another referral.
2065 -- --------------------------------------------------------------------
2066 IF (l_count = 0) THEN
2067 UPDATE as_leads_all
2068 SET prm_referral_code = l_referral_code
2069 WHERE lead_id = p_entity_id;
2070 ELSE
2071 x_a_link_already_exists := 'Y';
2072
2073 -- ------------------------------------------------------------------
2074 -- LOG
2075 -- ------------------------------------------------------------------
2076 FOR z IN c_benefit_type LOOP
2077 l_benefit_type := z.benefit_type_code;
2078 l_referral_id := z.referral_id;
2079 l_partner_id := z.partner_id;
2080 END LOOP;
2081
2082 Debug('l_benefit_type = ' || l_benefit_type);
2083
2084 l_log_params_tbl.DELETE;
2085 l_log_params_tbl(1).param_name := 'REFERRAL_CODE';
2086 l_log_params_tbl(1).param_value := l_referral_code;
2087
2088
2089 PVX_Utility_PVT.create_history_log(
2090 p_arc_history_for_entity_code => l_benefit_type,
2091 p_history_for_entity_id => l_referral_id,
2092 p_history_category_code => 'GENERAL',
2093 p_message_code => 'PV_OPPTY_LINKED_TO_REFERRAL',
2094 p_partner_id => l_partner_id,
2095 p_access_level_flag => 'V',
2096 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_50,
2097 p_comments => NULL,
2098 p_log_params_tbl => l_log_params_tbl,
2099 x_return_status => x_return_status,
2100 x_msg_count => x_msg_count,
2101 x_msg_data => x_msg_data);
2102
2103 Debug('Return Status: ' || x_return_status);
2104
2105 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2106 RAISE FND_API.G_EXC_ERROR;
2107
2108 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2109 RAISE FND_API.g_exc_unexpected_error;
2110 END IF;
2111
2112
2113 END IF;
2114
2115 ELSE
2116 FOR x IN c_lead(p_entity_id) LOOP
2117 IF (x.source_system = 'REFERRAL' AND x.source_primary_reference IS NOT NULL) THEN
2118 l_count := 1;
2119 x_a_link_already_exists := 'Y';
2120
2121 -- ------------------------------------------------------------------
2122 -- LOG
2123 -- ------------------------------------------------------------------
2124 FOR z IN c_benefit_type LOOP
2125 l_benefit_type := z.benefit_type_code;
2126 l_referral_id := z.referral_id;
2127 l_partner_id := z.partner_id;
2128 END LOOP;
2129
2130 Debug('l_benefit_type = ' || l_benefit_type);
2131
2132 l_log_params_tbl.DELETE;
2133 l_log_params_tbl(1).param_name := 'REFERRAL_CODE';
2134 l_log_params_tbl(1).param_value := l_referral_code;
2135
2136
2137 PVX_Utility_PVT.create_history_log(
2138 p_arc_history_for_entity_code => l_benefit_type,
2139 p_history_for_entity_id => l_referral_id,
2140 p_history_category_code => 'GENERAL',
2141 p_message_code => 'PV_LEAD_LINKED_TO_REFERRAL',
2142 p_partner_id => l_partner_id,
2143 p_access_level_flag => 'V',
2144 p_interaction_level => pvx_utility_pvt.G_INTERACTION_LEVEL_50,
2145 p_comments => NULL,
2146 p_log_params_tbl => l_log_params_tbl,
2147 x_return_status => x_return_status,
2148 x_msg_count => x_msg_count,
2149 x_msg_data => x_msg_data);
2150
2151 Debug('Return Status: ' || x_return_status);
2152
2153 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2154 RAISE FND_API.G_EXC_ERROR;
2155
2156 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2157 RAISE FND_API.g_exc_unexpected_error;
2158 END IF;
2159
2160 ELSE
2161 l_count := 0;
2162 END IF;
2163
2164 /* --------------------------------------------------------------------
2165 IF (x.source_system IS NOT NULL AND x.source_primary_reference IS NOT NULL) THEN
2166 throw an error? can we overwrite what they have there?
2167 END IF;
2168 * -------------------------------------------------------------------- */
2169 END LOOP;
2170
2171 -- --------------------------------------------------------------------
2172 -- Link the lead to the referral only if it's not already linked
2173 -- to another referral.
2174 -- --------------------------------------------------------------------
2175 IF (l_count = 0) THEN
2176 UPDATE as_sales_leads
2177 SET source_primary_reference = l_referral_code,
2178 source_system = 'REFERRAL'
2179 WHERE sales_lead_id = p_entity_id;
2180 END IF;
2181 END IF;
2182
2183 -------------------- Exception --------------------------
2184 EXCEPTION
2185 WHEN FND_API.G_EXC_ERROR THEN
2186 ROLLBACK;
2187 x_return_status := FND_API.G_RET_STS_ERROR;
2188 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
2189 p_count => x_msg_count,
2190 p_data => x_msg_data);
2191
2192 WHEN FND_API.g_exc_unexpected_error THEN
2193 ROLLBACK;
2194 x_return_status := FND_API.g_ret_sts_unexp_error;
2195 FND_MSG_PUB.count_and_get(
2196 p_encoded => FND_API.g_false,
2197 p_count => x_msg_count,
2198 p_data => x_msg_data
2199 );
2200
2201 WHEN OTHERS THEN
2202 ROLLBACK;
2203 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
2204 FND_MSG_PUB.add_exc_msg(g_pkg_name, g_api_name);
2205 END IF;
2206
2207 x_return_status := FND_API.G_RET_STS_ERROR;
2208 FND_MSG_PUB.count_and_get(
2209 p_encoded => FND_API.g_false,
2210 p_count => x_msg_count,
2211 p_data => x_msg_data
2212 );
2213 END Link_Lead_Opportunity;
2214
2215
2216
2217 --=============================================================================+
2218 --| Private Function |
2219 --| |
2220 --| Get_Salesgroup_ID |
2221 --| |
2222 --| Parameters |
2223 --| IN |
2224 --| OUT |
2225 --| |
2226 --| |
2227 --| NOTES: |
2228 --| |
2229 --| HISTORY |
2230 --| |
2231 --==============================================================================
2232 FUNCTION Get_Salesgroup_ID (
2233 p_resource_id IN NUMBER
2234 )
2235 RETURN NUMBER
2236 IS
2237 l_sales_group_id_str VARCHAR2(100);
2238 l_sales_group_id NUMBER;
2239
2240 -- ------------------------------------------------------------------
2241 -- Retrieves the salesgroup_id of a resource.
2242 -- IF the resource belongs to more than one sales group, get the
2243 -- sales group from the profile: ASF_DEFAULT_GROUP_ROLE.
2244 -- ------------------------------------------------------------------
2245 CURSOR c_salesgroup_id IS
2246 SELECT MAX(grp.group_id) salesgroup_id
2247 FROM JTF_RS_GROUP_MEMBERS mem,
2248 JTF_RS_ROLE_RELATIONS rrel,
2249 JTF_RS_ROLES_B role,
2250 JTF_RS_GROUP_USAGES u,
2251 JTF_RS_GROUPS_B grp,
2252 JTF_RS_RESOURCE_EXTNS RES
2253 WHERE mem.group_member_id = rrel.role_resource_id AND
2254 rrel.role_resource_type = 'RS_GROUP_MEMBER' AND
2255 rrel.role_id = role.role_id AND
2256 role.role_type_code IN ('SALES','TELESALES','FIELDSALES','PRM') AND
2257 mem.delete_flag <> 'Y' AND
2258 rrel.delete_flag <> 'Y' AND
2259 sysdate BETWEEN rrel.start_date_active AND
2260 NVL(rrel.end_date_active, SYSDATE) AND
2261 mem.group_id = u.group_id AND
2262 u.usage in ('SALES','PRM') AND
2263 mem.group_id = grp.group_id AND
2264 sysdate BETWEEN grp.start_date_active AND
2265 NVL(grp.end_date_active,sysdate) AND
2266 mem.resource_id = RES.resource_id AND
2267 RES.resource_id = p_resource_id;
2268
2269 BEGIN
2270 Debug('Calling Get_Salesgroup_ID function...........');
2271 Debug('resource_id = ' || p_resource_id);
2272
2273 FOR x IN c_salesgroup_id LOOP
2274 BEGIN
2275 l_sales_group_id := x.salesgroup_id;
2276 --l_sales_group_id_str := x.salesgroup_id;
2277
2278 Debug('l_sales_group_id = ' || l_sales_group_id);
2279
2280 -- -------------------------------------------------------------
2281 -- Parse out the string into an ID.
2282 -- The string could look like this: "100000100(Member)"
2283 -- -------------------------------------------------------------
2284 --IF (INSTR(l_sales_group_id_str, ')') > 0) THEN
2285 -- l_sales_group_id :=
2286 -- TO_NUMBER(SUBSTR(l_sales_group_id_str, 1,
2287 -- INSTR(l_sales_group_id_str, '(') - 1));
2288
2289 --ELSE
2290 -- l_sales_group_id := TO_NUMBER(l_sales_group_id_str);
2291 --END IF;
2292
2293 EXCEPTION
2294 WHEN OTHERS THEN
2295 l_sales_group_id := null;
2296 END;
2297 END LOOP;
2298
2299 RETURN l_sales_group_id;
2300 END Get_Salesgroup_ID;
2301 -- ========================End of Get_Salesgroup_ID=============================
2302
2303
2304
2305 --=============================================================================+
2306 --| Private Procedure |
2307 --| |
2308 --| Retrieve_Attribute_Value |
2309 --| |
2310 --| Parameters |
2311 --| IN |
2312 --| OUT |
2313 --| |
2314 --| |
2315 --| NOTES: |
2316 --| |
2317 --| HISTORY |
2318 --| |
2319 --==============================================================================
2320 PROCEDURE Retrieve_Attribute_Value(
2321 p_entity_type IN VARCHAR2,
2322 p_referral_id IN VARCHAR2,
2323 p_attribute_id IN VARCHAR2,
2324 x_attr_value OUT NOCOPY VARCHAR2
2325 )
2326 IS
2327 CURSOR c_sql_text IS
2328 SELECT a.sql_text
2329 FROM pv_entity_attrs a
2330 WHERE a.entity = p_entity_type AND
2331 a.attribute_id = p_attribute_id;
2332
2333 BEGIN
2334 FOR x IN c_sql_text LOOP
2335 BEGIN
2336 EXECUTE IMMEDIATE x.sql_text
2337 INTO x_attr_value
2338 USING p_attribute_id, p_entity_type, p_referral_id;
2339
2340 EXCEPTION
2341 WHEN NO_DATA_FOUND THEN
2342 null;
2343 END;
2344 END LOOP;
2345
2346 END Retrieve_Attribute_Value;
2347 -- =======================End of Retrieve_Attribute_Value=======================
2348
2349
2350 --=============================================================================+
2351 --| Private Procedure |
2352 --| |
2353 --| Debug |
2354 --| |
2355 --| Parameters |
2356 --| IN |
2357 --| OUT |
2358 --| |
2359 --| |
2360 --| NOTES: |
2361 --| |
2362 --| HISTORY |
2363 --| |
2364 --==============================================================================
2365 PROCEDURE Debug(
2366 p_msg_string IN VARCHAR2,
2367 p_msg_type IN VARCHAR2 := 'PV_DEBUG_MESSAGE',
2368 p_token_type IN VARCHAR2 := 'TEXT',
2369 p_statement_level IN NUMBER := FND_LOG.LEVEL_PROCEDURE
2370 )
2371 IS
2372 BEGIN
2373 IF (PV_DEBUG_LOW_ON) THEN
2374 FND_MESSAGE.Set_Name('PV', p_msg_type);
2375 FND_MESSAGE.Set_Token(p_token_type, p_msg_string);
2376
2377 IF (g_log_to_file = 'N') THEN
2378 FND_MSG_PUB.Add;
2379
2380 ELSIF (g_log_to_file = 'Y') THEN
2381 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );
2382 END IF;
2383 END IF;
2384
2385 IF (p_statement_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2386 FND_LOG.STRING(p_statement_level,
2387 'pv.plsql.' || g_pkg_name || '.' || g_api_name,
2388 p_msg_string
2389 );
2390 END IF;
2391 END Debug;
2392 -- =================================End of Debug================================
2393
2394
2395 --=============================================================================+
2396 --| Public Procedure |
2397 --| |
2398 --| Set_Message |
2399 --| |
2400 --| Parameters |
2401 --| IN |
2402 --| OUT |
2403 --| |
2404 --| |
2405 --| NOTES: |
2406 --| |
2407 --| HISTORY |
2408 --| |
2409 --==============================================================================
2410 PROCEDURE Set_Message(
2411 p_msg_level IN NUMBER,
2412 p_msg_name IN VARCHAR2,
2413 p_token1 IN VARCHAR2 := NULL,
2414 p_token1_value IN VARCHAR2 := NULL,
2415 p_token2 IN VARCHAR2 := NULL ,
2416 p_token2_value IN VARCHAR2 := NULL,
2417 p_token3 IN VARCHAR2 := NULL,
2418 p_token3_value IN VARCHAR2 := NULL
2419 )
2420 IS
2421 BEGIN
2422 -- --------------------------------------------------------------------------
2423 -- 11.5.10 debug - messages logged to fnd_log_messages table.
2424 -- --------------------------------------------------------------------------
2425 IF (FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2426 FND_MESSAGE.Set_Name('PV', p_msg_name);
2427
2428 IF (p_token1 IS NOT NULL) THEN
2429 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
2430 END IF;
2431
2432 IF (p_token2 IS NOT NULL) THEN
2433 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
2434 END IF;
2435
2436 IF (p_token3 IS NOT NULL) THEN
2437 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
2438 END IF;
2439
2440
2441 FND_LOG.MESSAGE(FND_LOG.LEVEL_ERROR,
2442 'pv.plsql.' || g_pkg_name || '.' || g_api_name,
2443 FALSE
2444 );
2445 END IF;
2446
2447 -- --------------------------------------------------------------------------
2448 -- Pre-11.5.10 debug message
2449 -- --------------------------------------------------------------------------
2450 FND_MESSAGE.Set_Name('PV', p_msg_name);
2451
2452 IF (p_token1 IS NOT NULL) THEN
2453 FND_MESSAGE.Set_Token(p_token1, p_token1_value);
2454 END IF;
2455
2456 IF (p_token2 IS NOT NULL) THEN
2457 FND_MESSAGE.Set_Token(p_token2, p_token2_value);
2458 END IF;
2459
2460 IF (p_token3 IS NOT NULL) THEN
2461 FND_MESSAGE.Set_Token(p_token3, p_token3_value);
2462 END IF;
2463
2464 FND_MSG_PUB.Add;
2465 END Set_Message;
2466 -- ==============================End of Set_Message==============================
2467
2468
2469 END PV_REFERRAL_GENERAL_PUB;