[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEAD_REFERRAL
Source
1 PACKAGE BODY AS_SALES_LEAD_REFERRAL AS
2 /* $Header: asxvlrpb.pls 120.1 2005/06/24 17:10:46 appldev ship $ */
3
4 -- PROCEDURE
5 -- Update_sales_referral_lead
6 --
7 -- PURPOSE
8 -- Update sales lead from referral screen.
9 --
10 -- PARAMETERS
11
12 --
13 -- NOTES
14 --
15 ----------------------------------------------------------------------
16
17 PROCEDURE Update_sales_referral_lead(
18 P_Api_Version_Number IN NUMBER,
19 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
20 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
21 P_Validation_Level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
22 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
23 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
24 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
25 P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
26 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
27 P_SALES_LEAD_Rec IN AS_SALES_LEADS_PUB.SALES_LEAD_Rec_Type
28 DEFAULT AS_SALES_LEADS_PUB.G_MISS_SALES_LEAD_REC,
29 p_overriding_usernames IN t_overriding_usernames,
30 X_Return_Status OUT NOCOPY VARCHAR2,
31 X_Msg_Count OUT NOCOPY NUMBER,
32 X_Msg_Data OUT NOCOPY VARCHAR2
33 )
34 IS
35
36 l_api_version CONSTANT NUMBER := 2.0;
37 l_api_name CONSTANT VARCHAR2(30) := 'Update_sales_referral_lead';
38 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
39
40 l_return_status VARCHAR2(1);
41
42
43 l_sales_lead_id AS_SALES_LEADS.sales_lead_id%type;
44 l_referral_status AS_SALES_LEADS.referral_status%type;
45 l_referred_by AS_SALES_LEADS.referred_by%type;
46 l_sales_lead_log_id number;
47 l_lead_referral_status VARCHAR2(100);
48 l_msg_data VARCHAR2(10000):='';
49 my_message VARCHAR2(2000);
50 CURSOR lc_sales_lead (pc_sales_lead_id number) IS
51 SELECT lead.referred_by, lead.referral_status
52 FROM AS_SALES_LEADS LEAD
53 WHERE LEAD.SALES_LEAD_ID = pc_sales_lead_id;
54
55 BEGIN
56
57 -------------------- initialize -------------------------
58 SAVEPOINT Update_sales_referral_lead;
59
60 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name||': start');
61
62 IF FND_API.to_boolean(p_init_msg_list) THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65
66 IF NOT FND_API.compatible_api_call(
67 l_api_version,
68 P_Api_Version_Number,
69 l_api_name,
70 g_pkg_name
71 ) THEN
72 RAISE FND_API.g_exc_unexpected_error;
73 END IF;
74
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77
78 ----------------------- validate ----------------------
79 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': validate');
80
81 IF (P_SALES_LEAD_Rec.sales_lead_id is null) THEN
82 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
83 FND_MESSAGE.set_name('PV', 'PV_NO_LEAD_FOUND');
84 FND_MSG_PUB.add;
85 END IF;
86 RAISE FND_API.g_exc_error;
87 END IF;
88
89
90 OPEN lc_sales_lead (pc_sales_lead_id => P_SALES_LEAD_Rec.sales_lead_id);
91 FETCH lc_sales_lead INTO l_referred_by, l_referral_status;
92 CLOSE lc_sales_lead;
93
94 --l_msg_data:=l_msg_data || '** Referral Status from table :'|| l_referral_status;
95 --l_msg_data:=l_msg_data || '** Referral Status from record :'|| P_SALES_LEAD_Rec.REFERRAL_STATUS;
96
97 -------------------------- call API --------------------
98 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': update');
99 l_lead_referral_status:=P_SALES_LEAD_Rec.REFERRAL_STATUS;
100
101 --main if,
102 --if referral type is null, do nothing.
103 IF( P_SALES_LEAD_Rec.referral_type is null) THEN
104 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
105 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
106 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || 'Referral Type is null');
107 fnd_msg_pub.Add;
108 END IF;
109 --l_msg_data:=l_msg_data || ' Referral Type is null ';
110 end if;
111
112 IF( P_SALES_LEAD_Rec.referral_type is not null) THEN
113
114 --If referral status is changed, we need to update lead and insert row in log table
115 --and send notification based on status
116 IF (l_referral_status is null OR l_referral_status <> l_lead_referral_status) THEN
117
118 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': status changed');
119 -- Call API to create log entry
120
121
122 AS_SALES_LEADS_LOG_PKG.Insert_Row(
123 px_log_id => l_sales_lead_log_id ,
124 p_sales_lead_id => P_SALES_LEAD_Rec.sales_lead_id,
125 p_created_by => fnd_global.user_id,
126 p_creation_date => sysdate,
127 p_last_updated_by => fnd_global.user_id,
128 p_last_update_date => sysdate,
129 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
130 p_request_id => P_SALES_LEAD_Rec.request_id,
131 p_program_application_id => P_SALES_LEAD_Rec.program_application_id,
132 p_program_id => P_SALES_LEAD_Rec.program_id,
133 p_program_update_date => P_SALES_LEAD_Rec.program_update_date,
134 p_status_code => P_SALES_LEAD_Rec.REFERRAL_STATUS,
135 p_assign_to_person_id => P_SALES_LEAD_Rec.assign_to_person_id,
136 p_assign_to_salesforce_id=> P_SALES_LEAD_Rec.assign_to_salesforce_id,
137 p_reject_reason_code => P_SALES_LEAD_Rec.reject_reason_code,
138 p_assign_sales_group_id => P_SALES_LEAD_Rec.assign_sales_group_id,
139 p_lead_rank_id => P_SALES_LEAD_Rec.lead_rank_id,
140 p_qualified_flag => P_SALES_LEAD_Rec.qualified_flag,
141 p_category => g_log_lead_referral_category
142 );
143
144 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147 -- l_msg_data:=l_msg_data || '** after inserted log row';
148 IF (--l_lead_referral_status = g_referral_status_sub or
149 l_lead_referral_status = g_referral_status_acc or
150 l_lead_referral_status = g_referral_status_dec or
151 --l_lead_referral_status = g_referral_status_comm_ltr or
152 l_lead_referral_status = g_referral_status_comm_acc or
153 l_lead_referral_status = g_referral_status_comm_rej
154 ) then
155
156 -- Send OUT NOCOPY email notification
157 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': notification');
158 l_msg_data:=l_msg_data || '** while notifying';
159
160 AS_SALES_LEAD_REFERRAL.Notify_Party (
161 p_api_version => 1.0
162 ,p_init_msg_list => FND_API.g_false
163 ,p_commit => FND_API.g_false
164 ,p_validation_level => FND_API.g_valid_level_full
165
166 ,p_lead_id => P_SALES_LEAD_Rec.sales_lead_id
167 ,p_lead_status => l_lead_referral_status
168 ,p_salesforce_id => P_identity_salesforce_id
169
170 ,p_overriding_usernames => AS_SALES_LEAD_REFERRAL.G_MISS_OVER_USERNAMES_TBL
171 ,x_Msg_Count => x_msg_count
172 ,x_Msg_Data => x_msg_data
173 ,x_Return_Status => x_return_status
174 );
175
176 l_msg_data:=l_msg_data || x_msg_Data;
177 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180 END IF;
181
182 END IF;
183
184 -- Update the referral status in as_sales_leads table
185
186 l_msg_data:=l_msg_data || '** before calling Update sales lead';
187
188 as_sales_leads_pub.update_sales_lead(
189
190 P_Api_Version_Number => l_api_version,
191 P_Init_Msg_List => FND_API.G_FALSE,
192 P_Commit => FND_API.G_FALSE,
193 P_Validation_Level => P_Validation_Level,
194 P_Check_Access_Flag => P_Check_Access_Flag,
195 P_Admin_Flag => P_Admin_Flag,
196 P_Admin_Group_Id => P_Admin_Group_Id,
197 P_identity_salesforce_id => P_identity_salesforce_id,
198 P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
199 P_SALES_LEAD_Rec => P_SALES_LEAD_Rec,
200 X_Return_Status => x_return_status,
201 X_Msg_Count => x_msg_count,
202 X_Msg_Data => x_msg_data
203 );
204 l_msg_data:=l_msg_data || x_msg_Data;
205
206 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
207 RAISE FND_API.G_EXC_ERROR;
208 END IF;
209 END IF; -- end of main if (referral type is not null)
210
211
212
213 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
214 RAISE FND_API.g_exc_unexpected_error;
215 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
216 RAISE FND_API.g_exc_error;
217 END IF;
218
219 -------------------- finish --------------------------
220
221
222 -- Check for commit
223 IF FND_API.to_boolean(p_commit) THEN
224 COMMIT;
225 END IF;
226
227 FND_MSG_PUB.count_and_get(
228 p_encoded => FND_API.g_false,
229 p_count => x_msg_count,
230 p_data => x_msg_data
231 );
232
233 AS_Utility_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, l_full_name ||': end');
234 -- x_msg_data:=l_msg_data;
235
236 EXCEPTION
237
238 WHEN FND_API.g_exc_error THEN
239 ROLLBACK TO Update_sales_referral_lead;
240 x_return_status := FND_API.g_ret_sts_error;
241 FND_MSG_PUB.count_and_get(
242 p_encoded => FND_API.g_false,
243 p_count => x_msg_count,
244 p_data => x_msg_data
245 );
246 /*FOR l_index IN 1..FND_MSG_PUB.Count_Msg LOOP
247 my_message := FND_MSG_PUB.Get(
248 p_msg_index => l_index,
249 p_encoded => FND_API.G_FALSE);
250
251 loop
252 exit when my_message is null;
253 l_msg_data:= l_msg_data || substr( my_message, 1, 250 );
254 my_message := substr( my_message, 251 );
255 end loop;
256
257 END LOOP;*/
258 --x_msg_data:=l_msg_data;
259 WHEN FND_API.g_exc_unexpected_error THEN
260 ROLLBACK TO Update_sales_referral_lead;
261 x_return_status := FND_API.g_ret_sts_unexp_error ;
262 FND_MSG_PUB.count_and_get(
263 p_encoded => FND_API.g_false,
264 p_count => x_msg_count,
265 p_data => x_msg_data
266 );
267
268 --x_msg_data:=l_msg_data;
269 WHEN OTHERS THEN
270 ROLLBACK TO Update_sales_referral_lead;
271 x_return_status := FND_API.g_ret_sts_unexp_error ;
272
273 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
274 THEN
275 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
276 END IF;
277
278 FND_MSG_PUB.count_and_get(
279 p_encoded => FND_API.g_false,
280 p_count => x_msg_count,
281 p_data => x_msg_data
282 );
283 --x_msg_data:=l_msg_data;
284 END Update_sales_referral_lead;
285
286
287
288 ---------------------------------------------------------------------
289 -- PROCEDURE
290 -- Notify_Party
291 --
292 -- PURPOSE
293 -- Notifies people when referral status is changed
294 --
295 -- PARAMETERS
296 --
297 -- NOTES
298 ---------------------------------------------------------------------
299
300 PROCEDURE Notify_Party (
301 p_api_version IN NUMBER := 1.0
302 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
303 ,p_commit IN VARCHAR2 := FND_API.g_false
304 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
305 ,p_lead_id IN NUMBER
306 ,p_lead_status IN VARCHAR2
307 ,p_salesforce_id IN NUMBER
308 ,p_overriding_usernames IN t_overriding_usernames default G_MISS_OVER_USERNAMES_TBL
309 ,x_msg_count OUT NOCOPY NUMBER
310 ,x_msg_data OUT NOCOPY VARCHAR2
311 ,x_return_status OUT NOCOPY VARCHAR2
312 )
313 IS
314
315 l_api_name CONSTANT VARCHAR2(30) := 'Notify_Party';
316 l_api_version CONSTANT NUMBER := 1.0;
317 l_ptnr_user_name VARCHAR2(50) :='';
318 l_ptnr_org_name VARCHAR2(100) :='';
319 l_notify_role_list VARCHAR2(2000);
320 l_notify_role VARCHAR2(80);
321 l_notify_ptnr_role VARCHAR2(80);
322 l_itemType CONSTANT VARCHAR2(30) := g_wf_itemtype_notify;
323 l_itemKey VARCHAR2(30);
324 l_user_id NUMBER;
325 l_user_name fnd_user.user_name%type;
326 l_person_id NUMBER;--as_accesses_all.person_id%type;
327 l_resource_id NUMBER;
328 l_customer_name VARCHAR2(80);
329 l_customer_city VARCHAR2(50);
330 l_customer_state VARCHAR2(30);
331 l_customer_country VARCHAR2(50);
332 l_lead_id NUMBER ;
333 l_wf_status_closed VARCHAR2(20) := g_wf_status_closed;
334 l_source_type VARCHAR2(20) := g_source_type;
335 l_user_count NUMBER := 1;
336 l_lead_assignment_id NUMBER;
337 l_party_id NUMBER;
338
339 TYPE UserRecType IS RECORD (
340 user_id NUMBER,
341 user_name fnd_user.user_name%type
342 );
343
344 TYPE UserTableType IS TABLE OF UserRecType INDEX BY BINARY_INTEGER;
345 l_user_table UserTableType;
346 l_lead_status varchar2(50);
347
348 -- Get lead info
349 CURSOR lc_leads (pc_lead_id NUMBER) IS
350 select asl.description, asl.referred_by, asl.referral_type,lkp1.meaning referral_type_meaning,
351 asl.referral_status,lkp2.meaning referral_status_meaning, asl.ref_order_number, asl.ref_order_amt,
352 asl.ref_comm_amt, asl.ref_decline_reason,lkp3.meaning decline_reason_meaning,
353 asl.ref_comm_ltr_status,asl.created_by
354 from as_sales_leads asl, as_lookups lkp1, as_lookups lkp2, as_lookups lkp3
355 where asl.sales_lead_id = pc_lead_id
356 and asl.referral_type = lkp1.lookup_code(+)
357 and lkp1.lookup_type(+) = 'REFERRAL_TYPE'
358 and asl.referral_status = lkp2.lookup_code(+)
359 and lkp2.lookup_type(+) = 'REFERRAL_STATUS'
360 and asl.ref_decline_reason = lkp3.lookup_code(+)
361 and lkp3.lookup_type(+) = 'DECLINE_REASON';
362
363 -- get lead owner user name from fnd_user
364 CURSOR lc_lead_owner (pc_lead_id number) IS
365 SELECT lead.assign_to_person_id, usr.user_name, usr.user_id, res.source_id
366 FROM as_sales_leads lead, fnd_user usr, jtf_rs_resource_extns res
367 WHERE lead.sales_lead_id = pc_lead_id
368 and lead.assign_to_person_id = res.source_id
369 and res.user_id = usr.user_id;
370
371 -- Get External Sales Team People - Partner user name
372 CURSOR lc_ptnr_users (pc_lead_id number) IS
373 SELECT acc.salesforce_id, usr.user_name, usr.user_id
374 FROM as_accesses_all acc, jtf_rs_resource_extns res, fnd_user usr
375 WHERE acc.sales_lead_id = pc_lead_id
376 -- AND acc. salesforce_role_code = 'REFERRAL'
377 AND acc.person_id IS NULL
378 AND acc.salesforce_id = res.resource_id
379 AND res.user_id = usr.user_id;
380
381 -- Get Customer Information
382 CURSOR lc_customer (pc_lead_id number) IS
383 SELECT hp.party_name, hp.city, hp.state, hp.country
384 FROM as_sales_leads asl, hz_parties hp
385 WHERE asl.sales_lead_id = pc_lead_id
386 AND hp.party_id = asl.customer_id;
387
388
389 CURSOR lc_ptnr_org_name(pc_party_id NUMBER) IS
390 SELECT ORG.PARTY_ID, partner.party_name
391 from AS_SALES_LEADS LEAD, HZ_PARTIES PARTNER, HZ_RELATIONSHIPS REL,HZ_ORGANIZATION_PROFILES ORG
392 where LEAD.REFERRED_BY=REL.PARTY_ID and REL.SUBJECT_ID=PARTNER.PARTY_ID
393 and REL.OBJECT_ID=ORG.PARTY_ID
394 and ORG.internal_flag = 'Y'
395 and ORG.effective_end_date is null
396 and LEAD.referred_by = pc_party_id;
397
398 cursor c_get_meaning (c_lookup_type varchar2, c_lookup_code varchar2) is
399 select meaning
400 from ar_lookups
401 where lookup_type = c_lookup_type
402 and lookup_code = c_lookup_code;
403
404
405 cursor c_get_category (c_user_id number) is
406 select category
407 from jtf_rs_resource_extns
408 where user_id = c_user_id;
409
410 cursor c_get_ptnr_full_name (c_user_id number) is
411 select ARLKP.meaning ||' '||puser.person_first_name||' ' || puser.person_last_name partner_contact_name
412 from jtf_rs_resource_extns JS,
413 hz_relationships PCONTACT, hz_relationships PORG,
414 hz_parties PUSER, hz_parties PARTNER, hz_parties VENDOR,
415 hz_organization_profiles HZOP, pv_partner_profiles PVPP,
416 hz_org_contacts HZOC, ar_lookups ARLKP
417 where JS.user_id = c_user_id
418 AND JS.source_id = pcontact.party_id
419 AND PCONTACT.subject_table_name = 'HZ_PARTIES'
420 AND PCONTACT.object_table_name = 'HZ_PARTIES'
421 AND PCONTACT.RELATIONSHIP_TYPE in ('EMPLOYMENT')
422 AND PCONTACT.directional_flag = 'F'
423 AND PCONTACT.STATUS = 'A'
424 AND PCONTACT.start_date <= SYSDATE
425 AND nvl(PCONTACT.end_date, SYSDATE) >= SYSDATE
426 AND PUSER.party_id = PCONTACT.subject_id
427 AND PUSER.PARTY_TYPE = 'PERSON'
428 AND PUSER.status = 'A'
429 AND HZOC.party_relationship_id = PCONTACT.relationship_id
430 AND PORG.subject_id = PCONTACT.object_id
431 AND PORG.subject_table_name = 'HZ_PARTIES'
432 AND PORG.object_table_name = 'HZ_PARTIES'
433 AND PORG.RELATIONSHIP_TYPE in ('PARTNER', 'VAD')
434 AND PORG.STATUS = 'A'
435 AND PORG.start_date <= SYSDATE
436 AND nvl(PORG.end_date, SYSDATE) >= SYSDATE
437 AND PARTNER.party_id = PORG.subject_id
438 AND PARTNER.PARTY_TYPE = 'ORGANIZATION'
439 AND PARTNER.status = 'A'
440 AND VENDOR.party_id = PORG.object_id
441 AND VENDOR.PARTY_TYPE ='ORGANIZATION'
442 AND VENDOR.status = 'A'
443 AND HZOP.party_id = VENDOR.party_id
444 AND HZOP.effective_end_date is null
445 AND HZOP.internal_flag = 'Y'
446 AND PVPP.partner_id = PORG.party_id
447 AND PVPP.SALES_PARTNER_FLAG = 'Y'
448 AND PUSER.person_title = ARLKP.lookup_code (+)
449 and ARLKP.lookup_type(+) = 'CONTACT_TITLE';
450
451 cursor c_get_ptnr_user_id (c_created_by number) is
452 select user_name,user_id
453 from jtf_rs_resource_extns
454 where user_id = c_created_by;
455
456
457
458 l_lead_name as_sales_leads.description%TYPE;
459 l_referred_by as_sales_leads.referred_by%TYPE;
460 l_referral_type as_sales_leads.referral_type%TYPE;
461 l_referral_type_meaning varchar2(100);
462 l_referral_status as_sales_leads.referral_status%TYPE;
463 l_referral_status_meaning varchar2(100);
464 l_ref_order_number as_sales_leads.ref_order_number%TYPE;
465 l_ref_order_amt as_sales_leads.ref_order_amt%TYPE;
466 l_ref_comm_amt as_sales_leads.ref_comm_amt%TYPE;
467 l_ref_decline_reason as_sales_leads.ref_decline_reason%TYPE;
468 l_ref_decline_reason_meaning varchar2(100);
469 l_ref_comm_ltr_status as_sales_leads.ref_comm_ltr_status%TYPE;
470 l_ptnr_salesforce_id as_accesses_all.salesforce_id%TYPE;
471 l_assign_to_person_id number;
472 l_owner_user_name fnd_user.user_name%TYPE;
473 l_owner_user_id fnd_user.user_id%TYPE;
474 l_owner_source_id number;
475 l_ptnr_user_id number;
476 l_overriding_usercount number;
477 l_count number;
478 l_mesg_data varchar2(4000):= 'SWKHANNA';
479 l_workflow_respond_url varchar2(4000) := fnd_profile.value('PV_WORKFLOW_RESPOND_URL');
480 l_cust_state_meaning varchar2(100);
481 l_cust_country_meaning varchar2(100);
482 l_referral_closedate date;
483 l_created_by number;
484 l_category varchar2(100);
485 l_ptnr_full_name varchar2(100);
486
487 BEGIN
488 l_mesg_data := l_mesg_data ||'start notify party';
489 -- Standard call to check for call compatibility.
490 IF NOT FND_API.Compatible_API_Call ( l_api_version,
491 p_api_version,
492 l_api_name,
493 G_PKG_NAME)
494 THEN
495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496 END IF;
497
498 -- Initialize message list if p_init_msg_list is set to TRUE.
499 IF FND_API.to_Boolean( p_init_msg_list )
500 THEN
501 FND_MSG_PUB.initialize;
502 END IF;
503
504 -- Debug Message
505 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
506 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
507 fnd_message.Set_Token('TEXT', 'In ' || l_api_name);
508 fnd_msg_pub.Add;
509 END IF;
510
511
512
513 -- Initialize API return status to SUCCESS
514 x_return_status := FND_API.G_RET_STS_SUCCESS;
515
516 -- Notify_Party processing steps
517 -- Get Lead Details using sales_lead_id from as_sales_leads
518 -- Get a workflow item key from the sequence
519 -- Create a workflow process
520 -- Set Item Attributes i.e all the info reqd by the wf for that lead. eg. status
521 -- Based on the referral_status, figure OUT NOCOPY who to send the notification to
522 -- Launch the process
523
524 -- Get Lead information
525 OPEN lc_leads(p_lead_id);
526 FETCH lc_leads
527 INTO l_lead_name, l_referred_by, l_referral_type,l_referral_type_meaning,
528 l_referral_status,l_referral_status_meaning,
529 l_ref_order_number, l_ref_order_amt, l_ref_comm_amt,
530 l_ref_decline_reason,l_ref_decline_reason_meaning, l_ref_comm_ltr_status,l_created_by;
531 CLOSE lc_leads;
532
533
534 if p_lead_status is not null then
535 l_referral_status := p_lead_status;
536 end if;
537
538
539 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
540 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
541 fnd_message.Set_Token('TEXT', 'In notify party ref_status' || l_referral_status);
542 fnd_msg_pub.Add;
543 END IF;
544
545 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
546 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
547 fnd_message.Set_Token('TEXT', 'In notify party l_ref_decline_reason' || l_ref_decline_reason);
548 fnd_msg_pub.Add;
549 END IF;
550
551 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
552 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
553 fnd_message.Set_Token('TEXT', 'In notify party l_ref_decline_reason meaning' || l_ref_decline_reason_meaning);
554 fnd_msg_pub.Add;
555 END IF;
556
557
558 l_lead_id := p_lead_id;
559
560 -- Get Customer Information
561 OPEN lc_customer (pc_lead_id => l_lead_id);
562 FETCH lc_customer
563 INTO l_customer_name,l_customer_city,l_customer_state,l_customer_country;
564 CLOSE lc_customer;
565
566 -- Get Partner Org Name
567 OPEN lc_ptnr_org_name(pc_party_id => l_referred_by);
568 FETCH lc_ptnr_org_name
569 INTO l_party_id, l_ptnr_org_name;
570 CLOSE lc_ptnr_org_name;
571
572
573 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
574 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
575 fnd_message.Set_token('TEXT', 'partner_org_name : '|| l_ptnr_org_name || ' Party_id: ' || l_party_id );
576 fnd_msg_pub.Add;
577 END IF;
578
579 -- Get Lookup ,meaning for country and state
580 open c_get_meaning('STATE',l_customer_state);
581 fetch c_get_meaning into l_cust_state_meaning;
582 close c_get_meaning;
583
584 open c_get_meaning('COUNTRY',l_customer_country);
585 fetch c_get_meaning into l_cust_country_meaning;
586 close c_get_meaning;
587
588 open c_get_meaning('REFERRAL_STATUS',l_referral_status);
589 fetch c_get_meaning into l_referral_status_meaning;
590 close c_get_meaning;
591
592 -- check to see if lead created by partner or vendor
593 open c_get_category(l_created_by);
594 fetch c_get_category into l_category;
595 close c_get_category;
596
597 --setting item key
598 SELECT PV_LEAD_WORKFLOWS_S.nextval
599 INTO l_itemKey
600 FROM dual;
601
602
603 --setting notify role to a unique id
604 l_notify_role := 'AS_' || l_itemKey || '_' || '0';
605 l_notify_ptnr_role := 'AS_' || l_itemKey || '_' || 'P';
606
607
608
609 -- Get recipients of the messages
610
611 -- If overriding_users passed in, then use that list instead
612
613 IF (p_overriding_usernames is not null and p_overriding_usernames.count > 0 ) THEN
614 l_overriding_usercount := p_overriding_usernames.count ;
615 l_count := 1;
616 LOOP
617
618 EXIT WHEN l_count = p_overriding_usernames.count+1;
619 l_notify_role_list := l_notify_role_list||','||UPPER(p_overriding_usernames(l_count));
620 l_count := l_count + 1;
621
622
623 END LOOP;
624
625 l_notify_role_list := substr(l_notify_role_list,2);
626
627
628 --
629 ELSE
630 -- If l_referral_status not in ('ACCEPTED','DECLINED') then
631 -- get lead owner user name from fnd_user
632 -- There can be only one owner.
633 OPEN lc_lead_owner (pc_lead_id => l_lead_id);
634 FETCH lc_lead_owner INTO l_assign_to_person_id, l_owner_user_name, l_owner_user_id, l_owner_source_id;
635 CLOSE lc_lead_owner;
636
637 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
638 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
639 fnd_message.Set_token('TEXT', 'owner user name : '|| l_owner_user_name || ' l_owner_user_id: ' || l_owner_user_id);
640 fnd_msg_pub.Add;
641 END IF;
642
643 --end if;
644 l_user_table(l_user_count).user_id := l_owner_user_id;
645 l_user_table(l_user_count).user_name := l_owner_user_name;
646 l_user_count := l_user_count +1;
647 -- members of external sales team
648 -- Get Partner User Name
649
650 -- People from external sales team only need to be added only in followig cases:
651 -- If Lead is accepted i.e new opportunity created
652 -- If Lead is rejected i.e. link to existing opportunity
653 IF l_referral_status in ('ACCEPTED','DECLINED') then
654 OPEN lc_ptnr_users (pc_lead_id => l_lead_id) ;
655 LOOP
656 FETCH lc_ptnr_users INTO l_ptnr_salesforce_id, l_ptnr_user_name, l_ptnr_user_id;
657 EXIT WHEN lc_ptnr_users%NOTFOUND;
658 l_user_table(l_user_count).user_id := l_ptnr_user_id;
659 l_user_table(l_user_count).user_name := l_ptnr_user_name;
660 l_user_count := l_user_count +1;
661 END LOOP;
662 -- just clearing these so they don't mess up later
663 l_ptnr_user_name := null;
664 l_ptnr_user_id := null;
665 CLOSE lc_ptnr_users;
666 END IF;
667 if l_category = 'PARTY' then
668 -- get partner contact user id
669 open c_get_ptnr_user_id (l_created_by);
670 fetch c_get_ptnr_user_id into l_ptnr_user_name,l_ptnr_user_id;
671 close c_get_ptnr_user_id;
672 --
673
674 end if;
675 -- Get Partner Contact Full Name
676 open c_get_ptnr_full_name (l_ptnr_user_id);
677 fetch c_get_ptnr_full_name into l_ptnr_full_name;
678 close c_get_ptnr_full_name;
679
680
681 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
682 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
683 fnd_message.Set_token('TEXT', 'l_ptnr_user_name : '|| l_ptnr_user_name|| ' l_ptnr_user_id: ' || l_ptnr_user_id);
684 fnd_msg_pub.Add;
685 END IF;
686
687 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
688 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
689 fnd_message.Set_token('TEXT', 'l_ptnr_full_name : '|| l_ptnr_full_name);
690 fnd_msg_pub.Add;
691 END IF;
692
693 --Forming notify list form user table
694 FOR i in 1 .. l_user_table.count LOOP
695
696 if(
697 l_user_table(i).user_id is not null
698 ) then
699 l_notify_role_list := l_notify_role_list || ',' || UPPER(l_user_table(i).user_name);
700 end if;
701
702 END LOOP;
703
704 --taking first , OUT NOCOPY of list
705 l_notify_role_list := substr(l_notify_role_list,2);
706
707 END IF; -- overring username clause
708
709 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
710 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
711 fnd_message.Set_token('TEXT', 'partner_user_name : '|| l_ptnr_user_name );
712 fnd_msg_pub.Add;
713 END IF;
714
715
716 -- Create Role
717 wf_directory.CreateAdHocRole(role_name => l_notify_role,
718 role_display_name => l_notify_role,
719 role_users => l_notify_role_list);
720
721
722 -- Create extra role for partner contact if partner created the lead
723 wf_directory.CreateAdHocRole(role_name => l_notify_ptnr_role,
724 role_display_name => l_notify_ptnr_role,
725 role_users => UPPER(l_ptnr_user_name));
726
727
728 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
729 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
730 fnd_message.Set_token('TEXT', 'Created role : '|| l_notify_role || ' with members ' || l_notify_role_list);
731 fnd_msg_pub.Add;
732 END IF;
733
734
735 IF (l_referral_status = g_referral_status_sub
736 or l_referral_status = g_referral_status_acc
737 or l_referral_status = g_referral_status_dec
738 or l_referral_status = g_referral_status_comm_ltr
739 or l_referral_status = g_referral_status_comm_acc
740 or l_referral_status = g_referral_status_comm_rej
741 ) then
742
743 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
744 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
745 fnd_message.Set_token('TEXT', 'Referral Status changed: ');
746 fnd_msg_pub.Add;
747 END IF;
748 -- Once the parameters for workflow is validated, start the workflow
749 wf_engine.CreateProcess (ItemType => l_itemType,
750 ItemKey => l_itemKey,
751 process => g_wf_pcs_notify_ptnr);
752
753 wf_engine.SetItemUserKey (ItemType => l_itemType,
754 ItemKey => l_itemKey,
755 userKey => l_itemkey);
756
757 --setting lead status so that it can be used in notify function
758 wf_engine.SetItemAttrText (ItemType => l_itemType,
759 ItemKey => l_itemKey,
760 aname => g_wf_attr_lead_status,
761 avalue => l_referral_status);
762
763
764 --setting lead status so that it can be used in notify function
765 wf_engine.SetItemAttrText (ItemType => l_itemType,
766 ItemKey => l_itemKey,
767 aname => g_wf_attr_lead_status_mean,
768 avalue => l_referral_status_meaning);
769
770
771 wf_engine.SetItemAttrText ( ItemType => l_itemType,
772 ItemKey => l_itemKey,
773 aname => g_wf_attr_cust_name,
774 avalue => l_customer_name);
775
776 wf_engine.SetItemAttrText (ItemType => l_itemType,
777 ItemKey => l_itemKey,
778 aname => g_wf_attr_referral_type,
779 avalue => l_referral_type);
780
781 wf_engine.SetItemAttrText (ItemType => l_itemType,
782 ItemKey => l_itemKey,
783 aname => g_wf_attr_referral_type_mean,
784 avalue => l_referral_type_meaning);
785
786 wf_engine.SetItemAttrText ( ItemType => l_itemType,
787 ItemKey => l_itemKey,
788 aname => g_wf_attr_ptnr_user_name,
789 avalue => l_ptnr_user_name);
790
791 wf_engine.SetItemAttrText ( ItemType => l_itemType,
792 ItemKey => l_itemKey,
793 aname => g_wf_attr_cust_state,
794 avalue => l_customer_state);
795
796 wf_engine.SetItemAttrText ( ItemType => l_itemType,
797 ItemKey => l_itemKey,
798 aname => g_wf_attr_cust_country,
799 avalue => l_customer_country);
800
801 wf_engine.SetItemAttrText ( ItemType => l_itemType,
802 ItemKey => l_itemKey,
803 aname => g_wf_attr_declined_reason,
804 avalue => l_ref_decline_reason);
805
806 wf_engine.SetItemAttrText ( ItemType => l_itemType,
807 ItemKey => l_itemKey,
808 aname => g_wf_attr_dec_reason_mean,
809 avalue => l_ref_decline_reason_meaning);
810
811 wf_engine.SetItemAttrText ( ItemType => l_itemType,
812 ItemKey => l_itemKey,
813 aname => g_wf_attr_ptnr_org_name,
814 avalue => l_ptnr_org_name);
815
816
817 wf_engine.SetItemAttrText ( ItemType => l_itemType,
818 ItemKey => l_itemKey,
819 aname => g_wf_attr_referral_commission,
820 avalue => l_ref_comm_amt);
821
822
823 wf_engine.SetItemAttrText (ItemType => l_itemType,
824 ItemKey => l_itemKey,
825 aname => g_wf_attr_create_notify_role,
826 avalue => l_notify_role);
827
828 wf_engine.SetItemAttrText (ItemType => l_itemType,
829 ItemKey => l_itemKey,
830 aname => g_wf_attr_create_ptnr_role,
831 avalue => l_notify_ptnr_role);
832
833
834 wf_engine.SetItemAttrText (ItemType => l_itemType,
835 ItemKey => l_itemKey,
836 aname => g_wf_attr_accept_notify_role,
837 avalue => l_notify_role);
838
839 wf_engine.SetItemAttrText (ItemType => l_itemType,
840 ItemKey => l_itemKey,
841 aname => g_wf_attr_reject_notify_role,
842 avalue => l_notify_role);
843
844 wf_engine.SetItemAttrText (ItemType => l_itemType,
845 ItemKey => l_itemKey,
846 aname => g_wf_attr_referral_notify_role,
847 avalue => l_notify_role);
848
849 wf_engine.SetItemAttrText (ItemType => l_itemType,
850 ItemKey => l_itemKey,
851 aname => g_wf_attr_respond_url,
852 avalue =>l_workflow_respond_url );
853
854
855 wf_engine.SetItemAttrText (ItemType => l_itemType,
856 ItemKey => l_itemKey,
857 aname => g_wf_attr_sales_lead_id,
858 avalue =>l_lead_id );
859
860 wf_engine.SetItemAttrText (ItemType => l_itemType,
861 ItemKey => l_itemKey,
862 aname => g_wf_attr_referred_by,
863 avalue =>l_referred_by );
864
865 wf_engine.SetItemAttrText ( ItemType => l_itemType,
866 ItemKey => l_itemKey,
867 aname =>g_wf_attr_lead_name ,
868 avalue => l_lead_name);
869
870 wf_engine.SetItemAttrText ( ItemType => l_itemType,
871 ItemKey => l_itemKey,
872 aname =>g_wf_attr_created_by ,
873 avalue => l_created_by);
874
875
876 wf_engine.SetItemAttrText ( ItemType => l_itemType,
877 ItemKey => l_itemKey,
878 aname =>g_wf_attr_category ,
879 avalue => l_category);
880
881 wf_engine.SetItemAttrText ( ItemType => l_itemType,
882 ItemKey => l_itemKey,
883 aname =>g_wf_attr_ptnr_full_name ,
884 avalue => l_ptnr_full_name);
885
886 /* wf_engine.SetItemAttrText ( ItemType => l_itemType,
887 ItemKey => l_itemKey,
888 aname => g_wf_attr_referral_closedate,
889 avalue => l_referral_closedate);*/
890
891 -- l_mesg_data := l_mesg_data ||'End';
892
893 -- Debug Message
894 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
895 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
896 fnd_message.Set_token('TEXT', 'Before Start Process ');
897 fnd_msg_pub.Add;
898 END IF;
899
900 wf_engine.StartProcess (ItemType => l_itemType,
901 ItemKey => l_itemKey);
902
903 -- dbms_output.put_line('started process');
904
905 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
906 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
907 fnd_message.Set_token('TEXT', 'After Start Process ');
908 fnd_msg_pub.Add;
909 END IF;
910
911 end if;
912
913
914 -- Standard call to get message count and if count is 1, get message info.
915 FND_MSG_PUB.Count_And_Get
916 ( p_count => x_msg_count,
917 p_data => x_msg_data
918 );
919 -- x_msg_data := l_mesg_data;
920 EXCEPTION
921
922 WHEN FND_API.G_EXC_ERROR THEN
923
924 x_return_status := FND_API.G_RET_STS_ERROR ;
925 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
926 p_count => x_msg_count,
927 p_data => x_msg_data);
928 --x_msg_data := 'SWKHANNA1'||l_mesg_data;
929 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
930
931 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
932 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_TRUE,
933 p_count => x_msg_count,
934 p_data => x_msg_data);
935 --x_msg_data := 'SWKHANNA2'||l_mesg_data;
936 WHEN OTHERS THEN
937
938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
939 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
940 fnd_msg_pub.Count_And_Get( p_encoded => FND_API.G_FALSE,
941 p_count => x_msg_count,
942 p_data => x_msg_data);
943 --x_msg_data :='SWKHANNA3'|| l_mesg_data;
944 End Notify_Party;
945
946 /* This procedure is called from the workflow process to decide whether to send notification to partner*/
947 PROCEDURE SEND_PTNR_NTF(
948 itemtype in varchar2,
949 itemkey in varchar2,
950 actid in number,
951 funcmode in varchar2,
952 resultout IN OUT NOCOPY varchar2
953 ) IS
954 l_api_name CONSTANT VARCHAR2(30) := 'SEND_PTNR_NTF';
955 l_api_version_number CONSTANT NUMBER := 1.0;
956
957
958 l_return_status varchar2(1);
959 l_msg_count number;
960 l_msg_data varchar2(2000);
961
962 l_category varchar2(100);
963 l_ptnr_user_name varchar2(100);
964 l_resultout varchar2(50);
965
966 BEGIN
967 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
968 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
969 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
970 fnd_msg_pub.Add;
971 END IF;
972
973 if (funcmode = 'RUN') then
974 -- Figure OUT NOCOPY if the lead was created by the partner. Only if the lead was created by the
975 -- partner , only then the lead creation email will be sent to the partner contact in addition
976 -- to the lead owner from vendor side. The vendor notification is sent both times i.e when the
977 -- the lead is created by vendor or partner
978
979 l_category := wf_engine.GetItemAttrText( itemtype => itemtype,
980 itemkey => itemkey,
981 aname => g_wf_attr_category);
982
983 l_ptnr_user_name := wf_engine.GetItemAttrText( itemtype => itemtype,
984 itemkey => itemkey,
985 aname => g_wf_attr_ptnr_user_name);
986
987 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
988 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
989 fnd_message.Set_Token('TEXT', 'Category ' || l_category);
990 fnd_msg_pub.Add;
991 END IF;
992 IF (l_category = 'PARTY' and l_ptnr_user_name is not null ) THEN
993 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
994 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
995 fnd_message.Set_Token('TEXT', 'Category is ' || l_category);
996 fnd_msg_pub.Add;
997 END IF;
998 l_resultout := 'COMPLETE:' || 'Y';
999 END IF;
1000
1001 else
1002
1003 l_resultout := 'COMPLETE:'||'N';
1004 end if;
1005 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1006 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
1007 fnd_message.Set_Token('TEXT', 'Function output : ' || l_resultout);
1008 fnd_msg_pub.Add;
1009 END IF;
1010 resultout := l_resultout;
1011
1012 END SEND_PTNR_NTF;
1013
1014
1015
1016
1017 /* This procedure is called from the workflow process to get the current status of the Lead */
1018 PROCEDURE AS_LEAD_NOTIFY(
1019 itemtype in varchar2,
1020 itemkey in varchar2,
1021 actid in number,
1022 funcmode in varchar2,
1023 resultout IN OUT NOCOPY varchar2
1024 ) IS
1025
1026 l_api_name CONSTANT VARCHAR2(30) := 'AS_LEAD_NOTIFY';
1027 l_api_version_number CONSTANT NUMBER := 1.0;
1028
1029
1030 l_return_status varchar2(1);
1031 l_msg_count number;
1032 l_msg_data varchar2(2000);
1033
1034 l_temp_status varchar2(40);
1035 l_resultout varchar2(50);
1036
1037 BEGIN
1038
1039 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1040 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
1041 fnd_message.Set_Token('TEXT', 'In ' || l_api_name || ' Funcmode: ' || funcmode);
1042 fnd_msg_pub.Add;
1043 END IF;
1044
1045 if (funcmode = 'RUN') then
1046 l_temp_status := wf_engine.GetItemAttrText( itemtype => itemtype,
1047 itemkey => itemkey,
1048 aname => g_wf_attr_lead_status);
1049
1050
1051 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1052 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
1053 fnd_message.Set_Token('TEXT', 'Lead Status in AS_lead_notify ' || l_temp_status);
1054 fnd_msg_pub.Add;
1055 END IF;
1056 -- Lead Submitted
1057 IF (l_temp_status = g_referral_status_sub ) THEN
1058 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1059 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
1060 fnd_message.Set_Token('TEXT', 'Lead Status in AS_lead_notify ' || l_temp_status);
1061 fnd_msg_pub.Add;
1062 END IF;
1063
1064 l_resultout := 'COMPLETE:' || g_wf_lkup_lead_status_sub;
1065 END IF;
1066 -- Lead Accepted
1067 IF ( l_temp_status = g_referral_status_acc) THEN
1068 l_resultout := 'COMPLETE:' || g_wf_lkup_lead_status_acc;
1069 END IF;
1070 -- Lead Declined
1071 IF ( l_temp_status = g_referral_status_dec) THEN
1072 l_resultout := 'COMPLETE:' || g_wf_lkup_lead_status_dec;
1073 END IF;
1074 -- Lead Commision Letter Sent
1075 IF ( l_temp_status = g_referral_status_comm_ltr) THEN
1076 l_resultout := 'COMPLETE:' || g_wf_lkup_lead_status_comm_ltr;
1077 END IF;
1078 -- Commission Accepted
1079 IF ( l_temp_status = g_referral_status_comm_acc) THEN
1080 l_resultout := 'COMPLETE:' || g_wf_lkup_lead_status_comm_acc;
1081 END IF;
1082 -- Commission Rejected
1083 IF ( l_temp_status = g_referral_status_comm_rej) THEN
1084 l_resultout := 'COMPLETE:' || g_wf_lkup_lead_status_comm_rej;
1085 END IF;
1086
1087 /*
1088 IF(l_temp_status = g_action_referral) THEN
1089 l_resultout := 'COMPLETE:' || g_wf_lkup_lead_status_ref;
1090 END IF;
1091 */
1092 --l_resultout := 'COMPLETE:' || l_temp_status;
1093 else
1094
1095 l_resultout := 'COMPLETE';
1096 end if;
1097 IF fnd_msg_pub.Check_Msg_Level (fnd_msg_pub.G_MSG_LVL_DEBUG_LOW) THEN
1098 fnd_message.Set_Name('AS', 'AS_DEBUG_MESSAGE');
1099 fnd_message.Set_Token('TEXT', 'Function output : ' || l_resultout);
1100 fnd_msg_pub.Add;
1101 END IF;
1102 resultout := l_resultout;
1103
1104 END AS_LEAD_NOTIFY;
1105
1106
1107
1108
1109
1110 End AS_SALES_LEAD_REFERRAL;
1111
1112