[Home] [Help]
PACKAGE BODY: APPS.AST_UWQ_LLIST_WORK_ACTION
Source
1 PACKAGE BODY AST_UWQ_LLIST_WORK_ACTION AS
2 /* $Header: astulacb.pls 115.29 2004/03/26 12:14:21 sumishar ship $ */
3
4 G_Debug BOOLEAN;
5
6 l_called_node VARCHAR2(10) := 'LLIST';
7
8 l_name VARCHAR2 (500);
9 l_value VARCHAR2 (4000);
10 l_type VARCHAR2 (500);
11 l_data_set_type VARCHAR2 (50);
12 l_data_set_id NUMBER;
13 l_prev_data_set_id NUMBER;
14
15 l_temp_lead_id NUMBER;
16
17 PROCEDURE Log_Mesg
18 (p_message IN VARCHAR2,
19 p_date IN VARCHAR2) IS
20 BEGIN
21 IF G_Debug THEN
22 AST_DEBUG_PUB.LogMessage(debug_msg => p_message,
23 print_date => p_date);
24 END IF;
25 END; -- End procedure Log_Mesg
26
27 PROCEDURE LLIST_WORK_NODE_REFRESH
28 ( p_action_key IN VARCHAR2,
29 p_lead_id IN NUMBER,
30 p_sales_lead_id IN NUMBER,
31 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST
32 ) IS
33 l_uwq_actions_list IEU_UWQ_WORK_PANEL_PUB.UWQ_ACTION_REC_LIST;
34 l_uwq_action_data_list IEU_UWQ_WORK_PANEL_PUB.UWQ_ACTION_DATA_REC_LIST;
35 l_action_data VARCHAR2(4000);
36 l_launch_opp_lead VARCHAR2(200);
37 BEGIN
38 l_uwq_actions_list(1).uwq_action_key := 'UWQ_WORK_DETAILS_REFRESH';
39 l_uwq_actions_list(1).action_data := '';
40 l_uwq_actions_list(1).dialog_style := 1;
41 l_uwq_actions_list(1).message := '';
42
43 IF p_lead_id IS NOT NULL OR
44 p_sales_lead_id IS NOT NULL THEN
45 Log_Mesg('Inside Lauch App Settings');
46 Log_Mesg('Lead Id = '||p_lead_id);
47 Log_Mesg('Sales Lead Id = '||p_sales_lead_id);
48 l_uwq_action_data_list(1).name := 'ACTION_NAME';
49 l_uwq_action_data_list(1).type := 'VARCHAR2';
50 IF p_action_key = 'LEAD_CONVERT_LEAD_TO_OPP' THEN
51 IF p_lead_id IS NOT NULL THEN
52 l_uwq_action_data_list(1).value := 'ASTOPCNT';
53 ELSIF p_sales_lead_id IS NOT NULL THEN
54 l_uwq_action_data_list(1).value := 'ASTSLTOP';
55 END IF;
56 END IF;
57
58 l_uwq_action_data_list(2).name := 'ACTION_TYPE';
59 l_uwq_action_data_list(2).value := 1;
60 l_uwq_action_data_list(2).type := 'NUMBER';
61
62 l_uwq_action_data_list(3).name := 'ACTION_PARAMS';
63 l_uwq_action_data_list(3).type := 'VARCHAR2';
64 IF p_action_key = 'LEAD_CONVERT_LEAD_TO_OPP' THEN
65 IF p_lead_id IS NOT NULL THEN
66 l_uwq_action_data_list(3).value := 'lead_id='||p_lead_id;
67 ELSIF p_sales_lead_id IS NOT NULL THEN
68 l_uwq_action_data_list(3).value := 'sales_lead_id='||p_sales_lead_id;
69 END IF;
70 END IF;
71
72 IEU_UWQ_WORK_PANEL_PUB.SET_UWQ_ACTION_DATA(l_uwq_action_data_list, l_action_data);
73
74 l_uwq_actions_list(2).uwq_action_key := 'UWQ_LAUNCH_APP';
75 l_uwq_actions_list(2).action_data := l_action_data;
76 IF NVL(FND_PROFILE.Value('AST_LAUNCH_OPP_LEAD'), 'N') = 'Y' THEN
77 l_uwq_actions_list(2).dialog_style := 1;
78 ELSE
79 l_uwq_actions_list(2).dialog_style := 3;
80 END IF;
81 IF p_action_key = 'LEAD_CONVERT_LEAD_TO_OPP' THEN
82 IF p_lead_id IS NOT NULL THEN
83 fnd_message.set_name('AST','AST_UWQ_LAUNCH_OPP_CENTER');
84 l_launch_opp_lead := fnd_message.get;
85 l_uwq_actions_list(2).message := l_launch_opp_lead;
86 ELSIF p_sales_lead_id IS NOT NULL THEN
87 --From now on, Lead Linking screen will be launched without asking for user's consent.
88 --Bug 2774004 .
89
90 -- fnd_message.set_name('AST','AST_UWQ_LAUNCH_LEAD_LINK');
91 -- l_launch_opp_lead := fnd_message.get;
92 -- l_uwq_actions_list(2).message := l_launch_opp_lead;
93 l_uwq_actions_list(2).dialog_style := 1;
94 END IF;
95 END IF;
96 Log_Mesg('All Lauch App Settings done.');
97 END IF;
98
99 IEU_UWQ_WORK_PANEL_PUB.SET_UWQ_ACTIONS(l_uwq_actions_list, x_uwq_actions_list);
100 END; -- End procedure LLIST_WORK_NODE_REFRESH
101
102 PROCEDURE LLIST_WORK_ITEM_ACTION
103 ( p_resource_id IN NUMBER,
104 p_language IN VARCHAR2,
105 p_source_lang IN VARCHAR2,
106 p_action_key IN VARCHAR2,
107 p_action_input_data IN SYSTEM.ACTION_INPUT_DATA_NST,
108 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST,
109 x_msg_count OUT NOCOPY NUMBER,
110 x_msg_data OUT NOCOPY VARCHAR2,
111 x_return_status OUT NOCOPY VARCHAR2
112 ) IS
113
114 l_uwq_action_data_list IEU_UWQ_WORK_PANEL_PUB.UWQ_ACTION_DATA_REC_LIST;
115 x_action_data VARCHAR2 (4000);
116
117 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
118 l_msg_count NUMBER;
119 l_msg_data VARCHAR2(2000);
120 BEGIN
121
122 IF FND_PROFILE.Value('AST_DEBUG') = 'Y' THEN
123 G_Debug := TRUE;
124 ELSE
125 G_Debug := FALSE;
126 END IF;
127
128 Log_Mesg('Start Log', 'Y');
129 Log_Mesg('Action Key: '||p_action_key);
130 IF p_action_key = 'LEAD_NEW_TASK' THEN
131 Log_Mesg('Calling New Task Action Procedure');
132 LLIST_NEW_TASK
133 ( p_action_key => p_action_key,
134 p_resource_id => p_resource_id,
135 p_work_action_data => p_action_input_data,
136 x_uwq_actions_list => x_uwq_actions_list,
137 x_return_status => l_return_status,
138 x_msg_count => l_msg_count,
139 x_msg_data => l_msg_data
140 );
141 ELSIF p_action_key = 'LEAD_UPDATE_LEAD' THEN
142 LLIST_UPDATE_LEAD
143 ( p_action_key => p_action_key,
144 p_resource_id => p_resource_id,
145 p_work_action_data => p_action_input_data,
146 x_uwq_actions_list => x_uwq_actions_list,
147 x_return_status => l_return_status,
148 x_msg_count => l_msg_count,
149 x_msg_data => l_msg_data
150 );
151 ELSIF p_action_key = 'LEAD_REASSIGN_LEAD' THEN
152 LLIST_REASSIGN_LEAD
153 ( p_action_key => p_action_key,
154 p_resource_id => p_resource_id,
155 p_work_action_data => p_action_input_data,
156 x_uwq_actions_list => x_uwq_actions_list,
157 x_return_status => l_return_status,
158 x_msg_count => l_msg_count,
159 x_msg_data => l_msg_data
160 );
161 ELSIF p_action_key = 'LEAD_CONVERT_LEAD_TO_OPP' THEN
162 LLIST_LEAD_TO_OPPORTUNITY
163 ( p_action_key => p_action_key,
164 p_resource_id => p_resource_id,
165 p_work_action_data => p_action_input_data,
166 x_uwq_actions_list => x_uwq_actions_list,
167 x_return_status => l_return_status,
168 x_msg_count => l_msg_count,
169 x_msg_data => l_msg_data
170 );
171 ELSIF p_action_key = 'LEAD_CLOSE_LEAD' THEN
172 LLIST_UPDATE_LEAD
173 ( p_action_key => p_action_key,
174 p_resource_id => p_resource_id,
175 p_work_action_data => p_action_input_data,
176 x_uwq_actions_list => x_uwq_actions_list,
177 x_return_status => l_return_status,
178 x_msg_count => l_msg_count,
179 x_msg_data => l_msg_data
180 );
181 ELSIF p_action_key = 'LEAD_CREATE_NOTE' THEN
182 Log_Mesg('Calling Create Note Action Procedure');
183 LLIST_CREATE_NOTE
184 ( p_action_key => p_action_key,
185 p_resource_id => p_resource_id,
186 p_work_action_data => p_action_input_data,
187 x_uwq_actions_list => x_uwq_actions_list,
188 x_return_status => l_return_status,
189 x_msg_count => l_msg_count,
190 x_msg_data => l_msg_data
191 );
192 END IF;
193
194 x_return_status := l_return_status;
195 Log_Mesg('End Log', 'Y');
196 EXCEPTION WHEN OTHERS THEN
197 x_return_status := l_return_status;
198 Log_Mesg('End Log in Exception', 'Y');
199 END; -- End procedure LLIST_WORK_ITEM_ACTION
200
201 PROCEDURE LLIST_NEW_TASK
202 ( p_action_key IN VARCHAR2,
203 p_resource_id IN NUMBER,
204 p_work_action_data IN SYSTEM.ACTION_INPUT_DATA_NST,
205 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2,
208 x_return_status OUT NOCOPY VARCHAR2
209 ) IS
210
211 l_creation_date DATE := SYSDATE;
212 l_last_update_date DATE := SYSDATE;
213 l_last_updated_by NUMBER := FND_PROFILE.Value('USER_ID'); --REVIEW
214 l_created_by NUMBER := FND_PROFILE.Value('USER_ID'); --REVIEW
215 l_last_update_login NUMBER := FND_PROFILE.Value('LOGIN_ID'); -- REVIEW
216
217 l_task_name VARCHAR2(80); --P
218 l_task_type_id NUMBER; --P
219 l_description VARCHAR2(4000); --P
220 l_owner_id NUMBER; --W RESOURCE_ID Resource_Id --REVIEW
221 l_customer_id NUMBER; --W
222 l_contact_id NUMBER; --W
223 l_date_type VARCHAR2(30); --P --REVIEW
224 l_start_date DATE; --P
225 l_end_date DATE; --P
226 l_source_object_type_code VARCHAR2(60);
227 l_source_object_id NUMBER; --P (Customer_party_id)
228 l_source_object_name VARCHAR2(80); --P (Customer_Name)
229 l_phone VARCHAR2(30); --P
230 l_phone_id NUMBER; --P --ADD TO VIEW
231 l_address_id NUMBER; --W
232 l_duration NUMBER;
233 l_duration_uom VARCHAR2(3);
234 l_status_code VARCHAR2(30);
235
236 l_notes VARCHAR2(2000);
237 l_note_type VARCHAR2(30);
238 l_note_status VARCHAR2(1);
239 l_party_id NUMBER;
240
241 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
242 l_msg_count NUMBER;
243 l_msg_data VARCHAR2(2000);
244 l_task_id NUMBER;
245 l_jtf_note_id NUMBER;
246 l_err_mesg VARCHAR2(500);
247 BEGIN
248 Log_Mesg('Inside New Task');
249 l_data_set_id := NULL;
250 l_prev_data_set_id := NULL;
251 l_owner_id := p_resource_id;
252 Log_Mesg('Owner ID: '||TO_CHAR(l_owner_id));
253
254 Log_Mesg('Looping to get Param Item Data only.');
255 FOR I IN 1.. p_work_action_data.COUNT LOOP
256 l_data_set_type := p_work_action_data(i).datasettype;
257
258 IF l_data_set_type = 'ACTION_PARAM_DATA' THEN
259 l_name := p_work_action_data(i).name;
260 l_value := p_work_action_data(i).value;
261 l_type := p_work_action_data(i).type;
262
263 Log_Mesg('Action Param Data Name: '||l_name||' ('||l_value||')');
264 IF l_name = 'TASK_NAME' THEN
265 l_task_name := l_value;
266 ELSIF l_name = 'TASK_TYPE_ID' THEN
267 l_task_type_id := TO_NUMBER(l_value);
268 ELSIF l_name = 'TASK_DESC' THEN
269 l_description := l_value;
270 ELSIF l_name = 'DATE_TYPE' THEN
271 l_date_type := l_value;
272 ELSIF l_name = 'START_DATE' THEN
273 l_start_date := TO_DATE(l_value, 'DD-MM-YYYY HH24:MI:SS');
274 ELSIF l_name = 'END_DATE' THEN
275 l_end_date := TO_DATE(l_value, 'DD-MM-YYYY HH24:MI:SS');
276 ELSIF l_name = 'NEW_NOTE' THEN
277 --Bug # 3525736
278 BEGIN
279 l_notes := l_value;
280 EXCEPTION
281 WHEN VALUE_ERROR THEN
282 l_return_status := 'E';
283 x_return_status := l_return_status;
284 FND_MESSAGE.Set_Name('AST', 'AST_NOTE_LENGTH_ERROR');
285 FND_MSG_PUB.ADD;
286 RAISE FND_API.G_EXC_ERROR;
287 END;
288 END IF;
289
290 Log_Mesg('Start parameter validation');
291 IF l_name = 'END_DATE' AND
292 l_end_date < l_start_date THEN
293 --l_err_mesg := 'End date must be greater than start date.';
294 l_return_status := 'E';
295 END IF;
296
297 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
298 x_return_status := l_return_status;
299 FND_MESSAGE.Set_Name('AST', 'AST_OPP_TASK_DATE');
300 --FND_MESSAGE.Set_Token('TEXT', l_err_mesg, FALSE);
301 FND_MSG_PUB.ADD;
302 RAISE FND_API.G_EXC_ERROR;
303 END IF;
304 Log_Mesg('End parameter validation');
305 ELSE
306 EXIT;
307 END IF;
308 END LOOP;
309 Log_Mesg('Get Parameter Data Loop Ended');
310
311 Log_Mesg('Looping to get Work Item and Param Item Data');
312 FOR I IN 1.. p_work_action_data.COUNT LOOP
313 l_data_set_type := p_work_action_data(i).datasettype;
314 l_data_set_id := p_work_action_data(i).dataSetId;
315
316 Log_Mesg('Data Set Type: '||l_data_set_type);
317 IF l_data_set_type = 'WORK_ITEM_DATA' THEN
318 l_name := p_work_action_data(i).name;
319 l_value := p_work_action_data(i).value;
320 l_type := p_work_action_data(i).type;
321
322 Log_Mesg('Work Data Name: '||l_name||' ('||l_value||')');
323 IF l_name = 'PARTY_ID' THEN
324 l_customer_id := TO_NUMBER(l_value);
325 ELSIF l_name = 'SALES_LEAD_ID' THEN
326 l_source_object_id := l_value;
327 ELSIF l_name = 'LEAD_NUMBER' THEN
328 l_source_object_name := l_value;
329 ELSIF l_name = 'CONTACT_PARTY_ID' THEN
330 l_contact_id := TO_NUMBER(l_value);
331 -- Begin Mod. Raam on 07.12.2002
332 ELSIF l_name = 'PHONE_ID' THEN
333 l_phone_id := l_value;
334 -- End Mod.
335 -- Begin Mod. Raam on 07.25.2002
336 ELSIF l_name = 'ADDRESS_ID' THEN
337 l_address_id := l_value;
338 -- End Mod.
339 END IF;
340 END IF;
341
342 Log_Mesg('Contact Id: '||l_contact_id);
343 l_source_object_type_code := 'LEAD';
344 Log_Mesg('Object Type Code: '||l_source_object_type_code);
345 Log_Mesg('Object Type id: '||l_source_object_id);
346 Log_Mesg('Object Type name: '||l_source_object_name);
347
348 IF l_prev_data_set_id <> l_data_set_id OR
349 i = p_work_action_data.COUNT THEN
350 Log_Mesg('Start Create Task');
351 AST_UWQ_WRAPPER_PKG.CREATE_TASK
352 ( p_task_name => l_task_name,
353 p_task_type_name => NULL,
354 p_task_type_id => l_task_type_id,
355 p_description => l_description,
356 p_owner_id => l_owner_id,
357 p_customer_id => l_customer_id,
358 p_contact_id => l_contact_id,
359 p_date_type => l_date_type,
360 p_start_date => l_start_date,
361 p_end_date => l_end_date,
362 p_source_object_type_code => l_source_object_type_code,
363 p_source_object_id => l_source_object_id,
364 p_source_object_name => l_source_object_name,
365 p_phone_id => l_phone_id,
366 p_address_id => l_address_id,
367 p_duration => l_duration,
368 p_duration_uom => l_duration_uom,
369 p_called_node => l_called_node,
370 x_return_status => l_return_status,
371 x_msg_count => l_msg_count,
372 x_msg_data => l_msg_data,
373 x_task_id => l_task_id
374 );
375 Log_Mesg('End Create Task');
376
377 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
378 x_return_status := l_return_status;
379 x_msg_count := l_msg_count;
380 x_msg_data := l_msg_data;
381 --x_task_id := l_task_id;
382 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
383 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Creat Task: ', FALSE);
384 -- FND_MSG_PUB.ADD;
385 RAISE FND_API.G_EXC_ERROR;
386 ELSE
387 x_return_status := l_return_status;
388 x_msg_count := l_msg_count;
389 x_msg_data := l_msg_data;
390 --x_task_id := l_task_id;
391 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
392 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Created Task: '||TO_CHAR(l_task_id), FALSE);
393 -- FND_MSG_PUB.ADD;
394 END IF;
395
396 Log_Mesg('Task Id: '||l_task_id);
397 IF l_notes IS NOT NULL THEN
398 l_source_object_type_code := 'TASK';
399 l_source_object_id := l_task_id;
400 l_party_id := l_customer_id; --REVIEW
401
402 Log_Mesg('Start Create Note');
403 AST_UWQ_WRAPPER_PKG.CREATE_NOTE
404 ( p_source_object_id => l_source_object_id,
405 p_source_object_code => l_source_object_type_code,
406 p_notes => l_notes,
407 p_notes_detail => NULL,
408 p_entered_by => l_last_updated_by,
409 p_entered_date => l_last_update_date,
410 p_last_update_date => l_last_update_date,
411 p_last_updated_by => l_last_updated_by,
412 p_creation_date => l_creation_date,
413 p_created_by => l_created_by,
414 p_last_update_login => l_last_update_login,
415 p_party_id => l_party_id,
416 x_jtf_note_id => l_jtf_note_id,
417 x_return_status => l_return_status,
418 x_msg_count => l_msg_count,
419 x_msg_data => l_msg_data
420 );
421 Log_Mesg('End Create Note');
422
423 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
424 x_return_status := l_return_status;
425 x_msg_count := l_msg_count;
426 x_msg_data := l_msg_data;
427 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
428 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Creat Note', FALSE);
429 -- FND_MSG_PUB.ADD;
430 RAISE FND_API.G_EXC_ERROR;
431 ELSE
432 x_return_status := l_return_status;
433 x_msg_count := l_msg_count;
434 x_msg_data := l_msg_data;
435 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
436 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Created Note: '||TO_CHAR(l_jtf_note_id), FALSE);
437 -- FND_MSG_PUB.ADD;
438 END IF;
439 ELSE
440 Log_Mesg('User did not enter any note to create.');
441 END IF;
442 END IF;
443
444 l_prev_data_set_id := l_data_set_id;
445 END LOOP;
446 Log_Mesg('Get Work Data Loop Ended');
447
448 LLIST_WORK_NODE_REFRESH
449 ( p_action_key => p_action_key,
450 p_lead_id => NULL,
451 p_sales_lead_id => NULL,
452 x_uwq_actions_list => x_uwq_actions_list
453 );
454
455 END; -- End procedure LLIST_NEW_TASK
456
457 PROCEDURE LLIST_UPDATE_LEAD
458 ( p_action_key IN VARCHAR2,
459 p_resource_id IN NUMBER,
460 p_work_action_data IN SYSTEM.ACTION_INPUT_DATA_NST,
461 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST,
462 x_msg_count OUT NOCOPY NUMBER,
463 x_msg_data OUT NOCOPY VARCHAR2,
464 x_return_status OUT NOCOPY VARCHAR2
465 ) IS
466
467 l_last_update_date DATE;
468 l_last_updated_by NUMBER := FND_PROFILE.Value('USER_ID'); --REVIEW
469 l_created_by NUMBER := FND_PROFILE.Value('USER_ID'); --REVIEW
470 l_last_update_login NUMBER := FND_PROFILE.Value('LOGIN_ID'); -- REVIEW
471
472 l_admin_group_id NUMBER; --P From GLOBAL.AST_ADMIN_GROUP_ID
473 l_identity_salesforce_id NUMBER; --P From GLOBAL.AST_RESOURCE_ID
474 l_p_status_code VARCHAR2(30); --P
475 l_status_code VARCHAR2(30); --P
476 l_customer_id NUMBER; --P
477 l_contact_party_id NUMBER; --P
478 l_admin_flag VARCHAR2(1); --P From GLOBAL.AST_ADMIN_FLAG
479 l_assign_to_salesforce_id NUMBER; --P From GLOBAL.AST_RESOURCE_ID
480 l_assign_sales_group_id NUMBER; --P From GLOBAL.AST_MEM_GROUP_ID
481 l_p_budget_status_code VARCHAR2(30); --P
482 l_budget_status_code VARCHAR2(30); --P
483
484 --Bug # 3491443
485 l_p_description VARCHAR2(2000); --P
486 l_description VARCHAR2(2000); --P
487
488 l_source_promotion_id NUMBER; --W
489 l_p_lead_rank_id NUMBER; --P
490 l_lead_rank_id NUMBER; --P
491 l_p_decision_timeframe_code VARCHAR2(30); --P
492 l_decision_timeframe_code VARCHAR2(30); --P
493 l_p_accept_flag VARCHAR2(1);
494 l_accept_flag VARCHAR2(1);
495 l_p_qualified_flag VARCHAR2(1);
496 l_qualified_flag VARCHAR2(1);
497 l_initiating_contact_id NUMBER; --REVIEW
498 l_phone_id NUMBER; --P --ADD TO VIEW
499 l_close_reason_code VARCHAR2(30);
500 l_person_id NUMBER;
501
502 l_source_object_type_code VARCHAR2(60);
503 l_source_object_id NUMBER; -- Lead Id
504
505 l_notes VARCHAR2(2000);
506 l_note_type VARCHAR2(30);
507 l_note_status VARCHAR2(1);
508 l_party_id NUMBER;
509 l_sales_lead_id NUMBER;
510
511 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
512 l_msg_count NUMBER;
513 l_msg_data VARCHAR2(2000);
514 l_jtf_note_id NUMBER;
515 BEGIN
516 Log_Mesg('Inside Update Lead');
517 l_data_set_id := NULL;
518 l_prev_data_set_id := NULL;
519 l_identity_salesforce_id := p_resource_id;
520
521 Log_Mesg('Looping to get Param Item Data only.');
522 FOR I IN 1.. p_work_action_data.COUNT LOOP
523 l_data_set_type := p_work_action_data(i).datasettype;
524
525 IF l_data_set_type = 'ACTION_PARAM_DATA' THEN
526 l_name := p_work_action_data(i).name;
527 l_value := p_work_action_data(i).value;
528 l_type := p_work_action_data(i).type;
529
530 Log_Mesg('Action Param Data Name: '||l_name||' ('||l_value||')');
531 IF l_name = 'ADMIN_GROUP_ID' THEN
532 l_admin_group_id := TO_NUMBER(l_value);
533 ELSIF l_name = 'MEM_GROUP_ID' THEN
534 l_assign_sales_group_id := TO_NUMBER(l_value);
535 ELSIF l_name = 'ADMIN_FLAG' THEN
536 l_admin_flag := l_value;
537 ELSIF l_name = 'LEAD_NAME' THEN
538 l_description := l_value;
539 l_p_description := l_value;
540 ELSIF l_name = 'STATUS_CODE' THEN
541 l_status_code := l_value;
542 l_p_status_code := l_value;
543 ELSIF l_name = 'LEAD_RANK' THEN
544 l_lead_rank_id := l_value;
545 l_p_lead_rank_id := l_value;
546 ELSIF l_name = 'BUDGET_STATUS' THEN
547 l_budget_status_code := l_value;
548 l_p_budget_status_code := l_value;
549 ELSIF l_name = 'TIME_FRAME' THEN
550 l_decision_timeframe_code := l_value;
551 l_p_decision_timeframe_code := l_value;
552 ELSIF l_name = 'CLOSE_REASON' THEN
553 l_close_reason_code := l_value;
554 ELSIF l_name = 'ACCEPT_FLAG' THEN
555 l_accept_flag := l_value;
556 l_p_accept_flag := l_value;
557 ELSIF l_name = 'QUALIFIED_FLAG' THEN
558 l_qualified_flag := l_value;
559 l_p_qualified_flag := l_value;
560 ELSIF l_name = 'NEW_NOTE' THEN
561 --Bug # 3525736
562 BEGIN
563 l_notes := l_value;
564 EXCEPTION
565 WHEN VALUE_ERROR THEN
566 l_return_status := 'E';
567 x_return_status := l_return_status;
568 FND_MESSAGE.Set_Name('AST', 'AST_NOTE_LENGTH_ERROR');
569 FND_MSG_PUB.ADD;
570 RAISE FND_API.G_EXC_ERROR;
571 END;
572 ELSIF l_name = 'PERSON_ID' THEN
573 l_person_id := l_value;
574 END IF;
575 ELSE
576 EXIT;
577 END IF;
578 END LOOP;
579 Log_Mesg('Get Parameter Data Loop Ended');
580
581 Log_Mesg('Looping to get Work Item Data only.');
582 FOR I IN 1.. p_work_action_data.COUNT LOOP
583 l_data_set_type := p_work_action_data(i).dataSetType;
584 l_data_set_id := p_work_action_data(i).dataSetID;
585
586 IF l_data_set_type = 'WORK_ITEM_DATA' THEN
587 l_name := p_work_action_data(i).name;
588 l_value := p_work_action_data(i).value;
589 l_type := p_work_action_data(i).type;
590
591 Log_Mesg('Work Data Name: '||l_name||' ('||l_value||')');
592 IF l_name = 'SALES_LEAD_ID' THEN
593 l_sales_lead_id := l_value;
594 ELSIF l_name = 'SOURCE_CODE_FOR_ID' THEN
595 l_source_promotion_id := l_value; --REVIEW
596 ELSIF l_name = 'PARTY_ID' THEN
597 l_customer_id := l_value;
598 ELSIF l_name = 'CONTACT_PARTY_ID' THEN
599 l_initiating_contact_id := l_value;
600 ELSIF l_name = 'PHONE_ID' THEN
601 l_phone_id := l_value;
602 ELSIF l_name = 'DESCRIPTION' AND
603 l_p_description IS NULL THEN
604 l_description := l_value;
605 ELSIF l_name = 'STATUS_CODE' AND
606 l_p_status_code IS NULL THEN
607 l_status_code := l_value;
608 ELSIF l_name = 'LEAD_RANK_ID' AND
609 l_p_lead_rank_id IS NULL THEN
610 l_lead_rank_id := l_value;
611 ELSIF l_name = 'BUDGET_STATUS_CODE' AND
612 l_p_budget_status_code IS NULL THEN
613 l_budget_status_code := l_value;
614 ELSIF l_name = 'ACCEPT_FLAG' AND
615 l_p_accept_flag IS NULL THEN
616 l_accept_flag := l_value;
617 ELSIF l_name = 'QUALIFIED_FLAG' AND
618 l_p_qualified_flag IS NULL THEN
619 l_qualified_flag := l_value;
620 ELSIF l_name = 'DECISION_TIMEFRAME_CODE' AND
621 l_p_decision_timeframe_code IS NULL THEN
622 l_decision_timeframe_code := l_value;
623 ELSIF l_name = 'ASSIGN_TO_SALESFORCE_ID' THEN
624 l_assign_to_salesforce_id := l_value;
625 ELSIF l_name = 'ASSIGN_SALES_GROUP_ID' THEN
626 l_assign_sales_group_id := TO_NUMBER(l_value);
627 ELSIF l_name = 'LAST_UPDATE_DATE' THEN
628 l_last_update_date := TO_DATE(l_value, 'DD-MM-YYYY HH24:MI:SS');
629 END IF;
630 END IF;
631
632 -- Begin Mod. Raam on 08.16.2002
633 IF l_data_set_id <= 1 AND
634 i = p_work_action_data.COUNT THEN
635 IF l_p_status_code IS NULL THEN
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 FND_MESSAGE.Set_Name('AST', 'AST_LEAD_STATUS_REQUIRED');
638 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Update Lead: ', FALSE);
639 FND_MSG_PUB.ADD;
640 RAISE FND_API.G_EXC_ERROR;
641 END IF;
642 END IF;
643 -- End Mod.
644
645 IF l_prev_data_set_id <> l_data_set_id OR
646 i = p_work_action_data.COUNT THEN
647
648 Log_Mesg('Before Territory check Has_updatelead ');
649 AST_ACCESS.Has_UpdateLeadAccess
650 ( p_sales_lead_id => l_sales_lead_id,
651 p_admin_flag => l_admin_flag,
652 p_admin_group_id => l_admin_group_id,
653 p_person_id => l_person_id,
654 p_resource_id => l_identity_salesforce_id,
655 x_return_status => l_return_status,
656 x_msg_count => l_msg_count,
657 x_msg_data => l_msg_data
658 );
659 Log_Mesg('After Territory check ' ||l_return_status);
660
661 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
662 x_return_status := l_return_status;
663 x_msg_count := l_msg_count;
664 x_msg_data := l_msg_data;
665 RAISE FND_API.G_EXC_ERROR;
666 END IF;
667
668 Log_Mesg(l_sales_lead_id||' - '||l_status_code);
669 AST_UWQ_WRAPPER_PKG.UPDATE_LEAD
670 (
671 p_sales_lead_id => l_sales_lead_id,
672 p_admin_group_id => l_admin_group_id,
673 p_identity_salesforce_id => l_identity_salesforce_id,
674 p_last_update_date => l_last_update_date,
675 p_status_code => l_status_code,
676 p_customer_id => l_customer_id,
677 p_admin_flag => l_admin_flag,
678 p_assign_to_salesforce_id => l_assign_to_salesforce_id,
679 p_assign_sales_group_id => l_assign_sales_group_id,
680 p_budget_status_code => l_budget_status_code,
681 p_description => l_description,
682 p_source_promotion_id => l_source_promotion_id,
683 p_lead_rank_id => l_lead_rank_id,
684 p_decision_timeframe_code => l_decision_timeframe_code,
685 p_initiating_contact_id => l_initiating_contact_id,
686 p_phone_id => l_phone_id,
687 p_close_reason_code => l_close_reason_code,
688 p_accept_flag => l_accept_flag,
689 p_qualified_flag => l_qualified_flag,
690 p_called_node => l_called_node,
691 x_return_status => l_return_status,
692 x_msg_count => l_msg_count,
693 x_msg_data => l_msg_data
694 );
695
696 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
697 x_return_status := l_return_status;
698 x_msg_count := l_msg_count;
699 x_msg_data := l_msg_data;
700 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
701 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Update Lead: ', FALSE);
702 -- FND_MSG_PUB.ADD;
703 RAISE FND_API.G_EXC_ERROR;
704 ELSE
705 x_return_status := l_return_status;
706 x_msg_count := l_msg_count;
707 x_msg_data := l_msg_data;
708 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
709 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Updated Lead: '||TO_CHAR(l_sales_lead_id), FALSE);
710 -- FND_MSG_PUB.ADD;
711 END IF;
712
713 IF l_notes IS NOT NULL THEN
714 l_source_object_type_code := 'LEAD';
715 l_source_object_id := l_sales_lead_id;
716 l_party_id := l_customer_id; --REVIEW
717
718 AST_UWQ_WRAPPER_PKG.CREATE_NOTE
719 ( p_source_object_id => l_source_object_id,
720 p_source_object_code => l_source_object_type_code,
721 p_notes => l_notes,
722 p_notes_detail => NULL,
723 p_entered_by => l_last_updated_by,
724 p_entered_date => sysdate,
725 p_last_update_date => sysdate,
726 p_last_updated_by => l_last_updated_by,
727 p_creation_date => sysdate,
728 p_created_by => l_created_by,
729 p_last_update_login => l_last_update_login,
730 p_party_id => l_party_id,
731 x_jtf_note_id => l_jtf_note_id,
732 x_return_status => l_return_status,
733 x_msg_count => l_msg_count,
734 x_msg_data => l_msg_data
735 );
736
737 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
738 Log_Mesg('Note Creation Failed.');
739 x_return_status := l_return_status;
740 x_msg_count := l_msg_count;
741 x_msg_data := l_msg_data;
742 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
743 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Creat Note', FALSE);
744 -- FND_MSG_PUB.ADD;
745 RAISE FND_API.G_EXC_ERROR;
746 ELSE
747 x_return_status := l_return_status;
748 x_msg_count := l_msg_count;
749 x_msg_data := l_msg_data;
750 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
751 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Created Note: '||TO_CHAR(l_jtf_note_id), FALSE);
752 -- FND_MSG_PUB.ADD;
753 END IF;
754 ELSE
755 Log_Mesg('User did not enter any note to create.');
756 END IF;
757 END IF;
758 l_prev_data_set_id := l_data_set_id;
759 END LOOP;
760 Log_Mesg('Get Work Data Loop Ended');
761
762 LLIST_WORK_NODE_REFRESH
763 ( p_action_key => p_action_key,
764 p_lead_id => NULL,
765 p_sales_lead_id => NULL,
766 x_uwq_actions_list => x_uwq_actions_list
767 );
768
769 END; -- End procedure LEAD_UPDATE_LEAD
770
771 PROCEDURE LLIST_REASSIGN_LEAD
772 ( p_action_key IN VARCHAR2,
773 p_resource_id IN NUMBER,
774 p_work_action_data IN SYSTEM.ACTION_INPUT_DATA_NST,
775 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST,
776 x_msg_count OUT NOCOPY NUMBER,
777 x_msg_data OUT NOCOPY VARCHAR2,
778 x_return_status OUT NOCOPY VARCHAR2
779 ) IS
780
781 l_creation_date DATE := SYSDATE;
782 l_last_update_date DATE := SYSDATE;
783 l_last_updated_by NUMBER := FND_PROFILE.Value('USER_ID'); --REVIEW
784 l_created_by NUMBER := FND_PROFILE.Value('USER_ID'); --REVIEW
785 l_last_update_login NUMBER := FND_PROFILE.Value('LOGIN_ID'); -- REVIEW
786
787 l_admin_flag VARCHAR2(1); --P From GLOBAL.AST_ADMIN_FLAG
788 l_admin_group_id NUMBER; --P From GLOBAL.AST_ADMIN_GROUP_ID
789 l_default_group_id NUMBER; --P
790 l_person_id NUMBER; --P
791 l_customer_id NUMBER; --P
792 l_sales_lead_id NUMBER;
793 l_new_salesforce_id NUMBER; --P
794 l_new_sales_group_id NUMBER; --P
795 l_new_owner_id NUMBER; --P
796 l_resource_id NUMBER;
797 l_first_pos NUMBER;
798 l_second_pos NUMBER;
799
800 l_phone_id NUMBER; --P
801 l_source_object_type_code VARCHAR2(60);
802 l_source_object_id NUMBER; -- Lead Id
803
804 l_notes VARCHAR2(2000);
805 l_note_type VARCHAR2(30);
806 l_note_status VARCHAR2(1);
807 l_party_id NUMBER;
808 l_jtf_note_id NUMBER;
809
810 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
811 l_msg_count NUMBER;
812 l_msg_data VARCHAR2(2000);
813 BEGIN
814 Log_Mesg('Inside Reassign Lead');
815 l_data_set_id := NULL;
816 l_prev_data_set_id := NULL;
817 l_resource_id := p_resource_id;
818
819 Log_Mesg('Looping to get Param Item Data only.');
820 FOR I IN 1.. p_work_action_data.COUNT LOOP
821 l_data_set_type := p_work_action_data(i).datasettype;
822
823 IF l_data_set_type = 'ACTION_PARAM_DATA' THEN
824 l_name := p_work_action_data(i).name;
825 l_value := p_work_action_data(i).value;
826 l_type := p_work_action_data(i).type;
827
828 Log_Mesg('Action Param Data Name: '||l_name||' ('||l_value||')');
829 IF l_name = 'LEAD_OWNER' THEN
830 l_first_pos := INSTR(l_value, '-');
831 l_second_pos := INSTR(l_value, '-', -1);
832 l_new_salesforce_id := SUBSTR(l_value, 1, (l_first_pos - 1));
833 l_new_sales_group_id := SUBSTR(l_value, (l_first_pos + 1), (l_second_pos - l_first_pos)-1);
834 l_new_owner_id := SUBSTR(l_value, (l_second_pos + 1));
835 ELSIF l_name = 'NEW_NOTE' THEN
836 --Bug # 3525736
837 BEGIN
838 l_notes := l_value;
839 EXCEPTION
840 WHEN VALUE_ERROR THEN
841 l_return_status := 'E';
842 x_return_status := l_return_status;
843 FND_MESSAGE.Set_Name('AST', 'AST_NOTE_LENGTH_ERROR');
844 FND_MSG_PUB.ADD;
845 RAISE FND_API.G_EXC_ERROR;
846 END;
847 ELSIF l_name = 'ADMIN_GROUP_ID' THEN
848 l_admin_group_id := TO_NUMBER(l_value);
849 ELSIF l_name = 'ADMIN_FLAG' THEN
850 l_admin_flag := l_value;
851 ELSIF l_name = 'DEFAULT_GROUP_ID' THEN
852 l_default_group_id := l_value;
853 ELSIF l_name = 'PERSON_ID' THEN
854 l_person_id := l_value;
855 END IF;
856 ELSE
857 EXIT;
858 END IF;
859 END LOOP;
860 Log_Mesg('Get Parameter Data Loop Ended');
861
862 Log_Mesg('l_new_sales_force_id : '||l_new_salesforce_id);
863 Log_Mesg('l_new_sales_group_id : '||l_new_sales_group_id);
864 Log_Mesg('l_new_owner_id : '||l_new_owner_id);
865
866 Log_Mesg('Looping to get Work Item Data only.');
867 FOR I IN 1.. p_work_action_data.COUNT LOOP
868 l_data_set_type := p_work_action_data(i).dataSetType;
869 l_data_set_id := p_work_action_data(i).dataSetID;
870
871 IF l_data_set_type = 'WORK_ITEM_DATA' THEN
872 l_name := p_work_action_data(i).name;
873 l_value := p_work_action_data(i).value;
874 l_type := p_work_action_data(i).type;
875
876 Log_Mesg('Work Data Name: '||l_name||' ('||l_value||')');
877 IF l_name = 'SALES_LEAD_ID' THEN
878 l_sales_lead_id := l_value;
879 ELSIF l_name = 'PARTY_ID' THEN
880 l_customer_id := l_value;
881 ELSIF l_name = 'PHONE_ID' THEN
882 l_phone_id := l_value;
883 ELSIF l_name = 'LAST_UPDATE_DATE' THEN
884 l_last_update_date := TO_DATE(l_value, 'DD-MM-YYYY HH24:MI:SS');
885 END IF;
886 END IF;
887
888 IF l_prev_data_set_id <> l_data_set_id OR
889 i = p_work_action_data.COUNT THEN
890
891 Log_Mesg('Before Territory check');
892 AST_ACCESS.Has_LeadOwnerAccess
893 ( p_sales_lead_id => l_sales_lead_id,
894 p_admin_flag => l_admin_flag,
895 p_admin_group_id => l_admin_group_id,
896 p_person_id => l_person_id,
897 p_resource_id => l_resource_id,
898 x_return_status => l_return_status,
899 x_msg_count => l_msg_count,
900 x_msg_data => l_msg_data
901 );
902 Log_Mesg('After Territory check');
903
904 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
905 x_return_status := l_return_status;
906 x_msg_count := l_msg_count;
907 x_msg_data := l_msg_data;
908 RAISE FND_API.G_EXC_ERROR;
909 END IF;
910
911 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
912 x_return_status := l_return_status;
913 x_msg_count := l_msg_count;
914 x_msg_data := l_msg_data;
915 RAISE FND_API.G_EXC_ERROR;
916 END IF;
917
918 AST_UWQ_WRAPPER_PKG.REASSIGN_LEAD
919 (
920 p_admin_flag => l_admin_flag,
921 p_admin_group_id => l_admin_group_id,
922 p_default_group_id => l_default_group_id,
923 p_person_id => l_person_id,
924 p_resource_id => l_resource_id,
925 p_sales_lead_id => l_sales_lead_id,
926 p_new_salesforce_id => l_new_salesforce_id,
927 p_last_update_date => l_last_update_date,
928 p_new_sales_group_id => l_new_sales_group_id,
929 p_new_owner_id => l_new_owner_id,
930 p_called_node => l_called_node,
931 x_return_status => l_return_status,
932 x_msg_count => l_msg_count,
933 x_msg_data => l_msg_data
934 );
935
936 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
937 x_return_status := l_return_status;
938 x_msg_count := l_msg_count;
939 x_msg_data := l_msg_data;
940 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
941 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Reassign Lead: ', FALSE);
942 -- FND_MSG_PUB.ADD;
943 RAISE FND_API.G_EXC_ERROR;
944 ELSE
945 x_return_status := l_return_status;
946 x_msg_count := l_msg_count;
947 x_msg_data := l_msg_data;
948 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
949 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Reassigned Lead: ', FALSE);
950 -- FND_MSG_PUB.ADD;
951 END IF;
952
953 IF l_notes IS NOT NULL THEN
954 l_source_object_type_code := 'LEAD';
955 l_source_object_id := l_sales_lead_id;
956 l_party_id := l_customer_id; --REVIEW
957
958 AST_UWQ_WRAPPER_PKG.CREATE_NOTE
959 ( p_source_object_id => l_source_object_id,
960 p_source_object_code => l_source_object_type_code,
961 p_notes => l_notes,
962 p_notes_detail => NULL,
963 p_entered_by => l_last_updated_by,
964 p_entered_date => l_last_update_date,
965 p_last_update_date => l_last_update_date,
966 p_last_updated_by => l_last_updated_by,
967 p_creation_date => l_creation_date,
968 p_created_by => l_created_by,
969 p_last_update_login => l_last_update_login,
970 p_party_id => l_party_id,
971 x_jtf_note_id => l_jtf_note_id,
972 x_return_status => l_return_status,
973 x_msg_count => l_msg_count,
974 x_msg_data => l_msg_data
975 );
976
977 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
978 Log_Mesg('Note Creation Failed.');
979 x_return_status := l_return_status;
980 x_msg_count := l_msg_count;
981 x_msg_data := l_msg_data;
982 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
983 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Creat Note', FALSE);
984 -- FND_MSG_PUB.ADD;
985 RAISE FND_API.G_EXC_ERROR;
986 ELSE
987 x_return_status := l_return_status;
988 x_msg_count := l_msg_count;
989 x_msg_data := l_msg_data;
990 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
991 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Created Note: '||TO_CHAR(l_jtf_note_id), FALSE);
992 -- FND_MSG_PUB.ADD;
993 END IF;
994 ELSE
995 Log_Mesg('User did not enter any note to create.');
996 END IF;
997 END IF;
998 l_prev_data_set_id := l_data_set_id;
999 END LOOP;
1000 Log_Mesg('Get Work Data Loop Ended');
1001
1002 LLIST_WORK_NODE_REFRESH
1003 ( p_action_key => p_action_key,
1004 p_lead_id => NULL,
1005 p_sales_lead_id => NULL,
1006 x_uwq_actions_list => x_uwq_actions_list
1007 );
1008
1009 END; -- End procedure LLIST_LEAD_REASSIGN_LEAD
1010
1011 PROCEDURE LLIST_LEAD_TO_OPPORTUNITY
1012 ( p_action_key IN VARCHAR2,
1013 p_resource_id IN NUMBER,
1014 p_work_action_data IN SYSTEM.ACTION_INPUT_DATA_NST,
1015 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST,
1016 x_msg_count OUT NOCOPY NUMBER,
1017 x_msg_data OUT NOCOPY VARCHAR2,
1018 x_return_status OUT NOCOPY VARCHAR2
1019 ) IS
1020
1021 --moved creation date as sysdate for created objects here. jraj 9/4/03
1022 -- last update date is now obtained as datetime from bali...fix for bug 2614503.
1023 -- used for update lead.
1024 l_last_update_date DATE;
1025 l_last_updated_by NUMBER := FND_PROFILE.Value('USER_ID'); --REVIEW
1026 l_created_by NUMBER := FND_PROFILE.Value('USER_ID'); --REVIEW
1027 l_last_update_login NUMBER := FND_PROFILE.Value('LOGIN_ID'); -- REVIEW
1028
1029 l_admin_flag VARCHAR2(1); --P From GLOBAL.AST_ADMIN_FLAG
1030 l_sales_lead_id NUMBER;
1031 l_sales_group_id NUMBER; --W
1032 l_resource_id NUMBER;
1033
1034 l_admin_group_id NUMBER; --P From GLOBAL.AST_ADMIN_GROUP_ID
1035 l_identity_salesforce_id NUMBER; --P From GLOBAL.AST_RESOURCE_ID
1036 l_p_status_code VARCHAR2(30); --P
1037 l_status_code VARCHAR2(30); --P
1038 l_customer_id NUMBER; --P
1039 l_contact_party_id NUMBER; --P
1040 l_assign_to_salesforce_id NUMBER; --P From GLOBAL.AST_RESOURCE_ID
1041 l_assign_sales_group_id NUMBER; --P From GLOBAL.AST_MEM_GROUP_ID
1042 l_p_budget_status_code VARCHAR2(30); --P
1043 l_budget_status_code VARCHAR2(30); --P
1044
1045 --Bug # 3491443
1046 l_p_description VARCHAR2(2000); --P
1047 l_description VARCHAR2(2000); --P
1048
1049 l_source_promotion_id NUMBER; --W
1050 l_p_lead_rank_id NUMBER; --P
1051 l_lead_rank_id NUMBER; --P
1052 l_p_decision_timeframe_code VARCHAR2(30); --P
1053 l_decision_timeframe_code VARCHAR2(30); --P
1054 l_p_accept_flag VARCHAR2(1);
1055 l_accept_flag VARCHAR2(1);
1056 l_p_qualified_flag VARCHAR2(1);
1057 l_qualified_flag VARCHAR2(1);
1058 l_initiating_contact_id NUMBER; --REVIEW
1059 l_phone_id NUMBER; --P --ADD TO VIEW
1060 l_close_reason_code VARCHAR2(30);
1061 l_person_id NUMBER;
1062
1063 l_source_object_type_code VARCHAR2(60);
1064 l_source_object_id NUMBER; -- Lead Id
1065
1066 l_notes VARCHAR2(2000);
1067 l_note_type VARCHAR2(30);
1068 l_note_status VARCHAR2(1);
1069 l_party_id NUMBER;
1070 l_jtf_note_id NUMBER;
1071
1072 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1073 l_msg_count NUMBER;
1074 l_msg_data VARCHAR2(2000);
1075 l_opp_id NUMBER;
1076 l_app_launch VARCHAR2(1);
1077 BEGIN
1078 Log_Mesg('Inside convert lead to opportunity.');
1079 l_data_set_id := NULL;
1080 l_prev_data_set_id := NULL;
1081 l_resource_id := p_resource_id;
1082 l_identity_salesforce_id := l_resource_id;
1083
1084 Log_Mesg('Looping to get Param Item Data only.');
1085 FOR I IN 1.. p_work_action_data.COUNT LOOP
1086 l_data_set_type := p_work_action_data(i).datasettype;
1087
1088 IF l_data_set_type = 'ACTION_PARAM_DATA' THEN
1089 l_name := p_work_action_data(i).name;
1090 l_value := p_work_action_data(i).value;
1091 l_type := p_work_action_data(i).type;
1092
1093 Log_Mesg('Action Param Data Name: '||l_name||' ('||LENGTH(l_value)||')');
1094 IF l_name = 'ADMIN_GROUP_ID' THEN
1095 l_admin_group_id := TO_NUMBER(l_value);
1096 ELSIF l_name = 'MEM_GROUP_ID' THEN
1097 l_sales_group_id := TO_NUMBER(l_value);
1098 l_assign_sales_group_id := TO_NUMBER(l_value);
1099 ELSIF l_name = 'ADMIN_FLAG' THEN
1100 l_admin_flag := l_value;
1101 ELSIF l_name = 'LEAD_NAME' THEN
1102 l_description := l_value;
1103 l_p_description := l_value;
1104 ELSIF l_name = 'STATUS_CODE' THEN
1105 l_status_code := l_value;
1106 l_p_status_code := l_value;
1107 ELSIF l_name = 'LEAD_RANK' THEN
1108 l_lead_rank_id := l_value;
1109 l_p_lead_rank_id := l_value;
1110 ELSIF l_name = 'BUDGET_STATUS' THEN
1111 l_budget_status_code := l_value;
1112 l_p_budget_status_code := l_value;
1113 ELSIF l_name = 'TIME_FRAME' THEN
1114 l_decision_timeframe_code := l_value;
1115 l_p_decision_timeframe_code := l_value;
1116 ELSIF l_name = 'CLOSE_REASON' THEN
1117 l_close_reason_code := l_value;
1118 ELSIF l_name = 'ACCEPT_FLAG' THEN
1119 l_accept_flag := l_value;
1120 l_p_accept_flag := l_value;
1121 ELSIF l_name = 'QUALIFIED_FLAG' THEN
1122 l_qualified_flag := l_value;
1123 l_p_qualified_flag := l_value;
1124 ELSIF l_name = 'NEW_NOTE' THEN
1125 --Bug # 3525736
1126 BEGIN
1127 l_notes := l_value;
1128 EXCEPTION
1129 WHEN VALUE_ERROR THEN
1130 l_return_status := 'E';
1131 x_return_status := l_return_status;
1132 FND_MESSAGE.Set_Name('AST', 'AST_NOTE_LENGTH_ERROR');
1133 FND_MSG_PUB.ADD;
1134 RAISE FND_API.G_EXC_ERROR;
1135 END;
1136 ELSIF l_name = 'PERSON_ID' THEN
1137 l_person_id := l_value;
1138 END IF;
1139 ELSE
1140 EXIT;
1141 END IF;
1142 END LOOP;
1143 Log_Mesg('Get Parameter Data Loop Ended');
1144
1145 Log_Mesg('Looping to get Work Item Data only.');
1146 FOR I IN 1.. p_work_action_data.COUNT LOOP
1147 l_data_set_type := p_work_action_data(i).dataSetType;
1148 l_data_set_id := p_work_action_data(i).dataSetID;
1149
1150 IF l_data_set_type = 'WORK_ITEM_DATA' THEN
1151 l_name := p_work_action_data(i).name;
1152 l_value := p_work_action_data(i).value;
1153 l_type := p_work_action_data(i).type;
1154
1155 Log_Mesg('Work Data Name: '||l_name||' ('||LENGTH(l_value)||')');
1156 IF l_name = 'SALES_LEAD_ID' THEN
1157 l_sales_lead_id := l_value;
1158 ELSIF l_name = 'SOURCE_CODE_FOR_ID' THEN
1159 l_source_promotion_id := l_value; --REVIEW
1160 ELSIF l_name = 'PARTY_ID' THEN
1161 l_customer_id := l_value;
1162 ELSIF l_name = 'CONTACT_PARTY_ID' THEN
1163 l_initiating_contact_id := l_value;
1164 ELSIF l_name = 'PHONE_ID' THEN
1165 l_phone_id := l_value;
1166 ELSIF l_name = 'DESCRIPTION' AND
1167 l_p_description IS NULL THEN
1168 l_description := l_value;
1169 ELSIF l_name = 'STATUS_CODE' AND
1170 l_p_status_code IS NULL THEN
1171 l_status_code := l_value;
1172 ELSIF l_name = 'LEAD_RANK_ID' AND
1173 l_p_lead_rank_id IS NULL THEN
1174 l_lead_rank_id := l_value;
1175 ELSIF l_name = 'BUDGET_STATUS_CODE' AND
1176 l_p_budget_status_code IS NULL THEN
1177 l_budget_status_code := l_value;
1178 ELSIF l_name = 'ACCEPT_FLAG' AND
1179 l_p_accept_flag IS NULL THEN
1180 l_accept_flag := l_value;
1181 ELSIF l_name = 'QUALIFIED_FLAG' AND
1182 l_p_qualified_flag IS NULL THEN
1183 l_qualified_flag := l_value;
1184 ELSIF l_name = 'DECISION_TIMEFRAME_CODE' AND
1185 l_p_decision_timeframe_code IS NULL THEN
1186 l_decision_timeframe_code := l_value;
1187 ELSIF l_name = 'ASSIGN_TO_SALESFORCE_ID' THEN
1188 l_assign_to_salesforce_id := l_value;
1189 ELSIF l_name = 'ASSIGN_SALES_GROUP_ID' THEN
1190 l_assign_sales_group_id := TO_NUMBER(l_value);
1191 -- last update date is now obtained as datetime from bali...fix for bug 2614503.
1192 -- used for update lead.
1193 ELSIF l_name = 'LAST_UPDATE_DATE' THEN
1194 l_last_update_date := TO_DATE(l_value, 'DD-MM-YYYY HH24:MI:SS');
1195 END IF;
1196 END IF;
1197
1198 IF l_data_set_id <= 1 AND
1199 i = p_work_action_data.COUNT THEN
1200 IF l_p_status_code IS NULL THEN
1201 x_return_status := FND_API.G_RET_STS_ERROR;
1202 FND_MESSAGE.Set_Name('AST', 'AST_LEAD_STATUS_REQUIRED');
1203 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Update Lead: ', FALSE);
1204 FND_MSG_PUB.ADD;
1205 RAISE FND_API.G_EXC_ERROR;
1206 END IF;
1207 END IF;
1208
1209 IF l_prev_data_set_id <> l_data_set_id OR
1210 i = p_work_action_data.COUNT THEN
1211
1212 Log_Mesg('Before Territory check Has_updatelead ');
1213 AST_ACCESS.Has_UpdateLeadAccess
1214 ( p_sales_lead_id => l_sales_lead_id,
1215 p_admin_flag => l_admin_flag,
1216 p_admin_group_id => l_admin_group_id,
1217 p_person_id => l_person_id,
1218 p_resource_id => l_identity_salesforce_id,
1219 x_return_status => l_return_status,
1220 x_msg_count => l_msg_count,
1221 x_msg_data => l_msg_data
1222 );
1223 Log_Mesg('After Territory check ' ||l_return_status);
1224
1225 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1226 x_return_status := l_return_status;
1227 x_msg_count := l_msg_count;
1228 x_msg_data := l_msg_data;
1229 RAISE FND_API.G_EXC_ERROR;
1230 END IF;
1231
1232 Log_Mesg(l_sales_lead_id||' - '||l_status_code);
1233 AST_UWQ_WRAPPER_PKG.UPDATE_LEAD
1234 (
1235 p_sales_lead_id => l_sales_lead_id,
1236 p_admin_group_id => l_admin_group_id,
1237 p_identity_salesforce_id => l_identity_salesforce_id,
1238 p_last_update_date => l_last_update_date,
1239 p_status_code => l_status_code,
1240 p_customer_id => l_customer_id,
1241 p_admin_flag => l_admin_flag,
1242 p_assign_to_salesforce_id => l_assign_to_salesforce_id,
1243 p_assign_sales_group_id => l_assign_sales_group_id,
1244 p_budget_status_code => l_budget_status_code,
1245 p_description => l_description,
1246 p_source_promotion_id => l_source_promotion_id,
1247 p_lead_rank_id => l_lead_rank_id,
1248 p_decision_timeframe_code => l_decision_timeframe_code,
1249 p_initiating_contact_id => l_initiating_contact_id,
1250 p_phone_id => l_phone_id,
1251 p_close_reason_code => l_close_reason_code,
1252 p_accept_flag => l_accept_flag,
1253 p_qualified_flag => l_qualified_flag,
1254 p_called_node => l_called_node,
1255 x_return_status => l_return_status,
1256 x_msg_count => l_msg_count,
1257 x_msg_data => l_msg_data
1258 );
1259
1260 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1261 x_return_status := l_return_status;
1262 x_msg_count := l_msg_count;
1263 x_msg_data := l_msg_data;
1264 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
1265 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Update Lead: ', FALSE);
1266 -- FND_MSG_PUB.ADD;
1267 RAISE FND_API.G_EXC_ERROR;
1268 ELSE
1269 x_return_status := l_return_status;
1270 x_msg_count := l_msg_count;
1271 x_msg_data := l_msg_data;
1272 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
1273 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Updated Lead: '||TO_CHAR(l_sales_lead_id), FALSE);
1274 -- FND_MSG_PUB.ADD;
1275 END IF;
1276
1277 Log_Mesg('Before calling girish API to convert lead');
1278 AST_UWQ_WRAPPER_PKG.CREATE_OPP_FOR_LEAD
1279 (
1280 p_admin_flag => l_admin_flag,
1281 p_sales_lead_id => l_sales_lead_id,
1282 p_resource_id => l_resource_id,
1283 p_salesgroup_id => l_sales_group_id,
1284 p_called_node => l_called_node,
1285 x_return_status => l_return_status,
1286 x_msg_count => l_msg_count,
1287 x_msg_data => l_msg_data,
1288 x_app_launch => l_app_launch,
1289 x_opportunity_id => l_opp_id
1290 );
1291
1292 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1293 x_return_status := l_return_status;
1294 x_msg_count := l_msg_count;
1295 x_msg_data := l_msg_data;
1296 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
1297 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Convert Lead to Opportunity: ', FALSE);
1298 -- FND_MSG_PUB.ADD;
1299 RAISE FND_API.G_EXC_ERROR;
1300 ELSE
1301 x_return_status := l_return_status;
1302 x_msg_count := l_msg_count;
1303 x_msg_data := l_msg_data;
1304 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
1305 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Converted Lead to Opportuniy: '||TO_CHAR(l_opp_id), FALSE);
1306 -- FND_MSG_PUB.ADD;
1307 END IF;
1308 Log_Mesg('After calling girish API : l_app_launch' || l_app_launch);
1309
1310 IF l_notes IS NOT NULL THEN
1311 l_source_object_type_code := 'LEAD';
1312 l_source_object_id := l_sales_lead_id;
1313 l_party_id := l_customer_id; --REVIEW
1314
1315 AST_UWQ_WRAPPER_PKG.CREATE_NOTE
1316 ( p_source_object_id => l_source_object_id,
1317 p_source_object_code => l_source_object_type_code,
1318 p_notes => l_notes,
1319 p_notes_detail => NULL,
1320 p_entered_by => l_last_updated_by,
1321 p_entered_date => sysdate,
1322 p_last_update_date => sysdate,
1323 p_last_updated_by => l_last_updated_by,
1324 p_creation_date => sysdate,
1325 p_created_by => l_created_by,
1326 p_last_update_login => l_last_update_login,
1327 p_party_id => l_party_id,
1328 x_jtf_note_id => l_jtf_note_id,
1329 x_return_status => l_return_status,
1330 x_msg_count => l_msg_count,
1331 x_msg_data => l_msg_data
1332 );
1333
1334 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1335 Log_Mesg('Note Creation Failed.');
1336 x_return_status := l_return_status;
1337 x_msg_count := l_msg_count;
1338 x_msg_data := l_msg_data;
1339 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
1340 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Creat Note', FALSE);
1341 -- FND_MSG_PUB.ADD;
1342 RAISE FND_API.G_EXC_ERROR;
1343 ELSE
1344 x_return_status := l_return_status;
1345 x_msg_count := l_msg_count;
1346 x_msg_data := l_msg_data;
1347 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
1348 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Created Note: '||TO_CHAR(l_jtf_note_id), FALSE);
1349 -- FND_MSG_PUB.ADD;
1350 END IF;
1351 ELSE
1352 Log_Mesg('User did not enter any note to create.');
1353 END IF;
1354
1355 END IF;
1356 l_prev_data_set_id := l_data_set_id;
1357 END LOOP;
1358 Log_Mesg('Get Work Data Loop Ended');
1359
1360 IF l_app_launch = 'Y' THEN
1361 LLIST_WORK_NODE_REFRESH
1362 ( p_action_key => p_action_key,
1363 p_lead_id => NULL,
1364 p_sales_lead_id => l_sales_lead_id,
1365 x_uwq_actions_list => x_uwq_actions_list
1366 );
1367 ELSE
1368 LLIST_WORK_NODE_REFRESH
1369 ( p_action_key => p_action_key,
1370 p_sales_lead_id => NULL,
1371 p_lead_id => l_opp_id,
1372 x_uwq_actions_list => x_uwq_actions_list
1373 );
1374 END IF;
1375 END; -- End procedure LLIST_LEAD_TO_OPPORTUNITY
1376
1377 PROCEDURE LLIST_CREATE_NOTE
1378 ( p_action_key IN VARCHAR2,
1379 p_resource_id IN NUMBER,
1380 p_work_action_data IN SYSTEM.ACTION_INPUT_DATA_NST,
1381 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST,
1382 x_msg_count OUT NOCOPY NUMBER,
1383 x_msg_data OUT NOCOPY VARCHAR2,
1384 x_return_status OUT NOCOPY VARCHAR2
1385 ) IS
1386
1387 l_creation_date DATE := SYSDATE;
1388 l_last_update_date DATE := SYSDATE;
1389 l_last_updated_by NUMBER := FND_PROFILE.Value('USER_ID'); --REVIEW
1390 l_created_by NUMBER := FND_PROFILE.Value('USER_ID'); --REVIEW
1391 l_last_update_login NUMBER := FND_PROFILE.Value('LOGIN_ID'); -- REVIEW
1392
1393 l_customer_id NUMBER; --W
1394 l_source_object_type_code VARCHAR2(60);
1395 l_source_object_id NUMBER; --P (Customer_party_id)
1396 l_source_object_name VARCHAR2(80); --P (Customer_Name)
1397
1398 l_notes VARCHAR2(2000);
1399 l_party_id NUMBER;
1400
1401 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1402 l_msg_count NUMBER;
1403 l_msg_data VARCHAR2(2000);
1404 l_task_id NUMBER;
1405 l_jtf_note_id NUMBER;
1406 l_err_mesg VARCHAR2(500);
1407 BEGIN
1408 Log_Mesg('Inside Create Note');
1409 l_data_set_id := NULL;
1410 l_prev_data_set_id := NULL;
1411
1412 Log_Mesg('Looping to get Param Item Data only.');
1413 FOR I IN 1.. p_work_action_data.COUNT LOOP
1414 l_data_set_type := p_work_action_data(i).datasettype;
1415
1416 IF l_data_set_type = 'ACTION_PARAM_DATA' THEN
1417 l_name := p_work_action_data(i).name;
1418 l_value := p_work_action_data(i).value;
1419 l_type := p_work_action_data(i).type;
1420
1421 Log_Mesg('Action Param Data Name: '||l_name||' ('||l_value||')');
1422 IF l_name = 'NEW_NOTE' THEN
1423 --Bug # 3525736
1424 BEGIN
1425 l_notes := l_value;
1426 EXCEPTION
1427 WHEN VALUE_ERROR THEN
1428 l_return_status := 'E';
1429 x_return_status := l_return_status;
1430 FND_MESSAGE.Set_Name('AST', 'AST_NOTE_LENGTH_ERROR');
1431 FND_MSG_PUB.ADD;
1432 RAISE FND_API.G_EXC_ERROR;
1433 END;
1434 END IF;
1435 ELSE
1436 EXIT;
1437 END IF;
1438 END LOOP;
1439 Log_Mesg('Get Parameter Data Loop Ended');
1440
1441 Log_Mesg('Looping to get Work Item and Param Item Data');
1442 FOR I IN 1.. p_work_action_data.COUNT LOOP
1443 l_data_set_type := p_work_action_data(i).datasettype;
1444 l_data_set_id := p_work_action_data(i).dataSetId;
1445
1446 Log_Mesg('Data Set Type: '||l_data_set_type);
1447 IF l_data_set_type = 'WORK_ITEM_DATA' THEN
1448 l_name := p_work_action_data(i).name;
1449 l_value := p_work_action_data(i).value;
1450 l_type := p_work_action_data(i).type;
1451
1452 Log_Mesg('Work Data Name: '||l_name||' ('||l_value||')');
1453 IF l_name = 'PARTY_ID' THEN
1454 l_customer_id := TO_NUMBER(l_value);
1455 ELSIF l_name = 'SALES_LEAD_ID' THEN
1456 l_source_object_id := l_value;
1457 END IF;
1458 END IF;
1459
1460 Log_Mesg('Object Type Code: '||l_source_object_type_code);
1461 Log_Mesg('Object Type id: '||l_source_object_id);
1462
1463 IF l_prev_data_set_id <> l_data_set_id OR
1464 i = p_work_action_data.COUNT THEN
1465
1466 IF l_notes IS NOT NULL THEN
1467 l_source_object_type_code := 'LEAD';
1468 l_party_id := l_customer_id; --REVIEW
1469
1470 Log_Mesg('Start Create Note');
1471 AST_UWQ_WRAPPER_PKG.CREATE_NOTE
1472 ( p_source_object_id => l_source_object_id,
1473 p_source_object_code => l_source_object_type_code,
1474 p_notes => l_notes,
1475 p_notes_detail => NULL,
1476 p_entered_by => l_last_updated_by,
1477 p_entered_date => l_last_update_date,
1478 p_last_update_date => l_last_update_date,
1479 p_last_updated_by => l_last_updated_by,
1480 p_creation_date => l_creation_date,
1481 p_created_by => l_created_by,
1482 p_last_update_login => l_last_update_login,
1483 p_party_id => l_party_id,
1484 x_jtf_note_id => l_jtf_note_id,
1485 x_return_status => l_return_status,
1486 x_msg_count => l_msg_count,
1487 x_msg_data => l_msg_data
1488 );
1489 Log_Mesg('End Create Note');
1490
1491 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1492 x_return_status := l_return_status;
1493 x_msg_count := l_msg_count;
1494 x_msg_data := l_msg_data;
1495 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
1496 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Creat Note', FALSE);
1497 -- FND_MSG_PUB.ADD;
1498 RAISE FND_API.G_EXC_ERROR;
1499 ELSE
1500 x_return_status := l_return_status;
1501 x_msg_count := l_msg_count;
1502 x_msg_data := l_msg_data;
1503 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
1504 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Created Note: '||TO_CHAR(l_jtf_note_id), FALSE);
1505 -- FND_MSG_PUB.ADD;
1506 END IF;
1507 ELSE
1508 Log_Mesg('User did not enter any note to create.');
1509 END IF;
1510 END IF;
1511
1512 l_prev_data_set_id := l_data_set_id;
1513 END LOOP;
1514 Log_Mesg('Get Work Data Loop Ended');
1515
1516 LLIST_WORK_NODE_REFRESH
1517 ( p_action_key => p_action_key,
1518 p_lead_id => NULL,
1519 p_sales_lead_id => NULL,
1520 x_uwq_actions_list => x_uwq_actions_list
1521 );
1522
1523 END; -- End procedure LLIST_CREATE_NOTE
1524
1525 END; -- Package Body AST_UWQ_LLIST_WORK_ACTION