[Home] [Help]
PACKAGE BODY: APPS.AST_UWQ_OLIST_WORK_ACTION
Source
1 PACKAGE BODY AST_UWQ_OLIST_WORK_ACTION AS
2 /* $Header: astuoacb.pls 120.6 2005/10/18 23:02:42 appldev ship $ */
3
4 G_Debug BOOLEAN;
5
6 l_called_node VARCHAR2(10) := 'OLIST';
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 PROCEDURE Log_Mesg
16 (p_message IN VARCHAR2,
17 p_date IN VARCHAR2 DEFAULT 'N') IS
18 BEGIN
19 IF G_Debug THEN
20 AST_DEBUG_PUB.LogMessage(debug_msg => p_message,
21 print_date => p_date);
22 END IF;
23 END; -- End procedure Log_Mesg
24
25 PROCEDURE OLIST_WORK_NODE_REFRESH
26 ( p_action_key IN VARCHAR2,
27 p_lead_id IN NUMBER DEFAULT NULL,
28 -- p_sales_lead_id IN NUMBER DEFAULT NULL,
29 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST
30 ) IS
31 l_uwq_actions_list IEU_UWQ_WORK_PANEL_PUB.UWQ_ACTION_REC_LIST;
32 l_uwq_action_data_list IEU_UWQ_WORK_PANEL_PUB.UWQ_ACTION_DATA_REC_LIST;
33 l_action_data VARCHAR2(4000);
34 BEGIN
35 l_uwq_actions_list(1).uwq_action_key := 'UWQ_WORK_DETAILS_REFRESH';
36 l_uwq_actions_list(1).action_data := '';
37 l_uwq_actions_list(1).dialog_style := 1;
38 l_uwq_actions_list(1).message := '';
39
40 --fix for bug # 3484366
41 /*
42 IF p_lead_id IS NOT NULL THEN
43 -- OR p_sales_lead_id IS NOT NULL THEN
44 Log_Mesg('Inside Lauch App Settings');
45 Log_Mesg('Lead Id = '||p_lead_id);
46 -- Log_Mesg('Sales Lead Id = '||p_sales_lead_id);
47 l_uwq_action_data_list(1).name := 'ACTION_NAME';
48 l_uwq_action_data_list(1).type := 'VARCHAR2';
49
50 l_uwq_action_data_list(2).name := 'ACTION_TYPE';
51 l_uwq_action_data_list(2).value := 1;
52 l_uwq_action_data_list(2).type := 'NUMBER';
53
54 l_uwq_action_data_list(3).name := 'ACTION_PARAMS';
55 l_uwq_action_data_list(3).type := 'VARCHAR2';
56
57 IEU_UWQ_WORK_PANEL_PUB.SET_UWQ_ACTION_DATA(l_uwq_action_data_list, l_action_data);
58
59 l_uwq_actions_list(2).uwq_action_key := 'UWQ_LAUNCH_APP';
60 l_uwq_actions_list(2).action_data := l_action_data;
61 Log_Mesg('All Lauch App Settings done.');
62 END IF;
63 */
64
65 IEU_UWQ_WORK_PANEL_PUB.SET_UWQ_ACTIONS(l_uwq_actions_list, x_uwq_actions_list);
66 END; -- End procedure OLIST_WORK_NODE_REFRESH
67
68 --Code added for R12 Ehnancement Change customer name ---Start
69 PROCEDURE Contacts_Delete(P_LEAD_ID IN NUMBER,
70 p_resource_id IN NUMBER,
71 p_admin_flag IN VARCHAR2,
72 p_admin_group_id IN NUMBER,
73 x_msg_count OUT NOCOPY NUMBER,
74 x_msg_data OUT NOCOPY VARCHAR2,
75 x_return_status OUT NOCOPY VARCHAR2) IS
76 v_profile_tbl AS_UTILITY_PUB.profile_tbl_type:=as_api_records_pkg.get_p_profile_tbl;
77 v_contact_tbl AS_OPPORTUNITY_PUB.contact_tbl_Type:=as_api_records_pkg.get_p_contact_tbl;
78 v_contact_out_tbl AS_OPPORTUNITY_PUB.contact_out_tbl_Type:=as_api_records_pkg.get_p_contact_out_tbl;
79 v_header_rec AS_OPPORTUNITY_PUB.header_rec_Type:=as_api_records_pkg.get_p_header_rec;
80
81 v_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
82
83
84 v_msg_count NUMBER;
85 v_msg_data VARCHAR2(2000);
86 v_comp_num NUMBER:=1;
87
88
89 -- v_validation_level_full number := ast_api.G_VALID_LEVEL_FULL ;
90 v_true varchar2(5) := 'T';
91 v_false varchar2(5) := 'F';
92 -- v_ret_sts_success varchar2(1) := ast_api.G_RET_STS_SUCCESS;
93 -- v_ret_sts_error varchar2(1) := ast_api.G_RET_STS_ERROR;
94 -- v_ret_sts_unexp_error varchar2(1) := ast_api.G_RET_STS_UNEXP_ERROR;
95
96
97 v_contact_id NUMBER;
98
99 CURSOR LEAD_CONTACT_ID_CUR IS
100 SELECT LEAD_CONTACT_ID
101 FROM
102 AS_OPPORTUNITY_CONTACTS_V
103 WHERE LEAD_ID= P_lead_id;
104
105 BEGIN
106
107
108 v_contact_tbl(1).lead_id := P_lead_id;
109 FOR LEAD_CONTACT_ID_REC IN LEAD_CONTACT_ID_CUR
110 LOOP
111 v_contact_tbl(1).lead_contact_id := LEAD_CONTACT_ID_REC.LEAD_CONTACT_ID;
112
113
114 BEGIN
115
116 AS_OPPORTUNITY_PUB.DELETE_CONTACTS(
117 p_api_version_number => 2.0,
118 p_init_msg_list => v_true,
119 p_commit => v_true,
120 p_validation_level => 100,
121 p_identity_salesforce_id => p_resource_id,
122 p_contact_tbl => v_contact_tbl,
123 p_check_access_flag => 'N',
124 p_admin_flag => nvl(p_admin_flag,'N'),
125 p_admin_group_id => p_admin_group_id,
126 p_partner_cont_party_id => NULL,
127 p_profile_tbl => v_profile_tbl,
128 x_contact_out_tbl => v_contact_out_tbl,
129 x_return_status => v_return_status,
130 x_msg_count => v_msg_count,
131 x_msg_data => v_msg_data
132 );
133
134
135 IF v_return_status <> FND_API.G_RET_STS_SUCCESS THEN
136 x_return_status := v_return_status;
137 x_msg_count := v_msg_count;
138 x_msg_data := v_msg_data;
139 RAISE FND_API.G_EXC_ERROR;
140 ELSE
141 x_return_status := v_return_status;
142 x_msg_count := v_msg_count;
143 x_msg_data := v_msg_data;
144 END IF;
145
146 END;
147
148 END LOOP;
149 x_return_status := v_return_status;
150 END Contacts_Delete;
151
152 PROCEDURE Salesteam_Update(
153 p_customer_id IN NUMBER,
154 p_lead_id IN NUMBER) IS
155 BEGIN
156
157
158 UPDATE AS_ACCESSES_ALL SET CUSTOMER_ID = p_customer_id
159 WHERE LEAD_ID=p_lead_id;
160
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 NULL;
165
166 END Salesteam_Update;
167
168 PROCEDURE Notes_Update( p_lead_id IN NUMBER,
169 p_last_update_date IN DATE,
170 p_customer_id IN NUMBER
171 ) IS
172 l_lead_id number := p_lead_id;
173
174 CURSOR CUR_NOTE IS
175 SELECT C.NOTE_CONTEXT_ID,
176 C.JTF_NOTE_ID,
177 C.OBJECT_ID
178 FROM AST_NOTES_DETAILS_VL NT,
179 AST_NOTES_CONTEXTS_V C
180 WHERE NT.SOURCE_OBJECT_ID = p_lead_id
181 AND C.OBJECT_CODE='PARTY'
182 AND NT.JTF_NOTE_ID = C.JTF_NOTE_ID;
183
184 l_return_status varchar2(1);
185 --l_date date := to_date(name_in('ASTOPOVW_HEADER.last_update_date'),'DD-MON-YYYY HH24:MI:SS');
186
187 BEGIN
188
189 FOR CUR_NOTE_REC IN CUR_NOTE
190 LOOP
191 JTF_NOTES_PUB.Update_note_context(
192 p_validation_level => 100
193 , x_return_status => l_return_status
194 , p_note_context_id => CUR_NOTE_REC.NOTE_CONTEXT_ID
195 , p_jtf_note_id => CUR_NOTE_REC.JTF_NOTE_ID
196 , p_note_context_type_id => p_customer_id
197 , p_note_context_type => 'PARTY'
198 , p_last_updated_by => FND_PROFILE.Value('USER_ID')
199 , p_last_update_date => p_last_update_date
200 , p_last_update_login =>FND_PROFILE.Value('LOGIN_ID')
201 );
202
203 END LOOP;
204
205 END Notes_Update;
206
207 --Code added for R12 Ehnancement Change customer name ---End
208
209 PROCEDURE OLIST_WORK_ITEM_ACTION
210 ( p_resource_id IN NUMBER,
211 p_language IN VARCHAR2 DEFAULT NULL,
212 p_source_lang IN VARCHAR2 DEFAULT NULL,
213 p_action_key IN VARCHAR2,
214 p_action_input_data IN SYSTEM.ACTION_INPUT_DATA_NST,
215 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST,
216 x_msg_count OUT NOCOPY NUMBER,
217 x_msg_data OUT NOCOPY VARCHAR2,
218 x_return_status OUT NOCOPY VARCHAR2
219 ) IS
220
221 l_uwq_action_data_list IEU_UWQ_WORK_PANEL_PUB.UWQ_ACTION_DATA_REC_LIST;
222 x_action_data VARCHAR2 (4000);
223
224 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
225 l_msg_count NUMBER;
226 l_msg_data VARCHAR2(2000);
227 BEGIN
228
229 IF FND_PROFILE.Value('AST_DEBUG') = 'Y' THEN
230 G_Debug := TRUE;
231 ELSE
232 G_Debug := FALSE;
233 END IF;
234
235 Log_Mesg('Start Log', 'Y');
236 Log_Mesg('Action Key: '||p_action_key);
237 IF p_action_key = 'OPP_NEW_TASK' THEN
238 Log_Mesg('Calling New Task Action Procedure');
239 OLIST_NEW_TASK
240 ( p_action_key => p_action_key,
241 p_resource_id => p_resource_id,
242 p_work_action_data => p_action_input_data,
243 x_uwq_actions_list => x_uwq_actions_list,
244 x_return_status => l_return_status,
245 x_msg_count => l_msg_count,
246 x_msg_data => l_msg_data
247 );
248 ELSIF p_action_key = 'OPP_UPDATE_OPPORTUNITY' THEN
249 OLIST_UPDATE_OPPORTUNITY
250 ( p_action_key => p_action_key,
251 p_resource_id => p_resource_id,
252 p_work_action_data => p_action_input_data,
253 x_uwq_actions_list => x_uwq_actions_list,
254 x_msg_count => l_msg_count,
255 x_msg_data => l_msg_data,
256 x_return_status => l_return_status
257 );
258 ELSIF p_action_key = 'OPP_CLOSE_OPPORTUNITY' THEN
259 OLIST_UPDATE_OPPORTUNITY
260 ( p_action_key => p_action_key,
261 p_resource_id => p_resource_id,
262 p_work_action_data => p_action_input_data,
263 x_uwq_actions_list => x_uwq_actions_list,
264 x_msg_count => l_msg_count,
265 x_msg_data => l_msg_data,
266 x_return_status => l_return_status
267 );
268 ELSIF p_action_key = 'OPP_CREATE_NOTE' THEN
269 Log_Mesg('Calling Create Note Action Procedure');
270 OLIST_CREATE_NOTE
271 ( p_action_key => p_action_key,
272 p_resource_id => p_resource_id,
273 p_work_action_data => p_action_input_data,
274 x_uwq_actions_list => x_uwq_actions_list,
275 x_return_status => l_return_status,
276 x_msg_count => l_msg_count,
277 x_msg_data => l_msg_data
278 );
279 END IF;
280 x_return_status := l_return_status;
281 Log_Mesg('End Log', 'Y');
282 EXCEPTION WHEN OTHERS THEN
283 x_return_status := l_return_status;
284 Log_Mesg('End Log in Exception', 'Y');
285 END; -- End procedure OLIST_WORK_ITEM_ACTION
286
287 PROCEDURE OLIST_NEW_TASK
288 ( p_action_key IN VARCHAR2,
289 p_resource_id IN NUMBER,
290 p_work_action_data IN SYSTEM.ACTION_INPUT_DATA_NST DEFAULT NULL,
291 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST,
292 x_msg_count OUT NOCOPY NUMBER,
293 x_msg_data OUT NOCOPY VARCHAR2,
294 x_return_status OUT NOCOPY VARCHAR2
295 ) IS
296
297 l_creation_date DATE := SYSDATE;
298 l_last_update_date DATE := SYSDATE;
299 l_last_updated_by NUMBER := FND_PROFILE.Value('USER_ID');
300 l_created_by NUMBER := FND_PROFILE.Value('USER_ID');
301 l_last_update_login NUMBER := FND_PROFILE.Value('LOGIN_ID');
302
303 l_task_name VARCHAR2(80);
304 l_task_type_id NUMBER;
305 l_description VARCHAR2(4000);
306 l_owner_id NUMBER;
307 l_customer_id NUMBER;
308 l_contact_id NUMBER;
309 l_date_type VARCHAR2(30);
310 l_start_date DATE;
311 l_end_date DATE;
312 l_source_object_type_code VARCHAR2(60);
313 l_source_object_id NUMBER;
314 l_source_object_name VARCHAR2(80);
315 l_phone_id NUMBER;
316 l_address_id NUMBER;
317 l_duration NUMBER;
318 l_duration_uom VARCHAR2(3);
319 l_status_code VARCHAR2(30);
320
321 l_notes VARCHAR2(2000);
322 l_party_id NUMBER;
323
324 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
325 l_msg_count NUMBER;
326 l_msg_data VARCHAR2(2000);
327 l_task_id NUMBER;
328 l_jtf_note_id NUMBER;
329 l_err_mesg VARCHAR2(500);
330 BEGIN
331 Log_Mesg('Inside New Task');
332 l_data_set_id := NULL;
333 l_prev_data_set_id := NULL;
334 l_owner_id := p_resource_id;
335 Log_Mesg('Owner ID: '||TO_CHAR(l_owner_id));
336
337 Log_Mesg('Looping to get Param Item Data only.');
338 FOR I IN 1.. p_work_action_data.COUNT LOOP
339 l_data_set_type := p_work_action_data(i).datasettype;
340
341 IF l_data_set_type = 'ACTION_PARAM_DATA' THEN
342 l_name := p_work_action_data(i).name;
343 l_value := p_work_action_data(i).value;
344 l_type := p_work_action_data(i).type;
345
346 Log_Mesg('Action Param Data Name: '||l_name||' ('||l_value||')');
347 IF l_name = 'TASK_NAME' THEN
348 l_task_name := l_value;
349 ELSIF l_name = 'TASK_TYPE_ID' THEN
350 l_task_type_id := TO_NUMBER(l_value);
351 ELSIF l_name = 'TASK_DESC' THEN
352 l_description := l_value;
353 ELSIF l_name = 'DATE_TYPE' THEN
354 l_date_type := l_value;
355 ELSIF l_name = 'START_DATE' THEN
356 l_start_date := TO_DATE(l_value, 'DD-MM-YYYY HH24:MI:SS');
357 ELSIF l_name = 'END_DATE' THEN
358 l_end_date := TO_DATE(l_value, 'DD-MM-YYYY HH24:MI:SS');
359 ELSIF l_name = 'NEW_NOTE' THEN
360 --Bug # 3525736
361 BEGIN
362 l_notes := l_value;
363 EXCEPTION
364 WHEN VALUE_ERROR THEN
365 l_return_status := 'E';
366 x_return_status := l_return_status;
367 FND_MESSAGE.Set_Name('AST', 'AST_NOTE_LENGTH_ERROR');
368 FND_MSG_PUB.ADD;
369 RAISE FND_API.G_EXC_ERROR;
370 END;
371 END IF;
372
373 Log_Mesg('Start parameter validation');
374 IF l_name = 'END_DATE' AND
375 l_end_date < l_start_date THEN
376 --l_err_mesg := 'End date must be greater than start date.';
377 l_return_status := 'E';
378 END IF;
379
380 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
381 x_return_status := l_return_status;
382 FND_MESSAGE.Set_Name('AST', 'AST_OPP_TASK_DATE');
383 --FND_MESSAGE.Set_Token('TEXT', l_err_mesg, FALSE);
384 FND_MSG_PUB.ADD;
385 RAISE FND_API.G_EXC_ERROR;
386 END IF;
387 Log_Mesg('End parameter validation');
388 ELSE
389 EXIT;
390 END IF;
391 END LOOP;
392 Log_Mesg('Get Parameter Data Loop Ended');
393
394 Log_Mesg('Looping to get Work Item and Param Item Data');
395 FOR I IN 1.. p_work_action_data.COUNT LOOP
396 l_data_set_type := p_work_action_data(i).datasettype;
397 l_data_set_id := p_work_action_data(i).dataSetId;
398
399 Log_Mesg('Data Set Type: '||l_data_set_type);
400 IF l_data_set_type = 'WORK_ITEM_DATA' THEN
401 l_name := p_work_action_data(i).name;
402 l_value := p_work_action_data(i).value;
403 l_type := p_work_action_data(i).type;
404
405 Log_Mesg('Work Data Name: '||l_name||' ('||l_value||')');
406 IF l_name = 'PARTY_ID' THEN
407 l_customer_id := TO_NUMBER(l_value);
408 ELSIF l_name = 'LEAD_ID' THEN
409 l_source_object_id := l_value;
410 ELSIF l_name = 'LEAD_NUMBER' THEN
411 l_source_object_name := l_value;
412 ELSIF l_name = 'CONTACT_PARTY_ID' THEN
413 l_contact_id := TO_NUMBER(l_value);
414 ELSIF l_name = 'PHONE_ID' THEN
415 l_phone_id := l_value;
416 ELSIF l_name = 'ADDRESS_ID' THEN
417 l_address_id := l_value;
418 END IF;
419 END IF;
420
421 Log_Mesg('Contact Id: '||l_contact_id);
422 l_source_object_type_code := 'OPPORTUNITY';
423 Log_Mesg('Object Type Code: '||l_source_object_type_code);
424 Log_Mesg('Object Type id: '||l_source_object_id);
425 Log_Mesg('Object Type name: '||l_source_object_name);
426
427 IF l_prev_data_set_id <> l_data_set_id OR
428 i = p_work_action_data.COUNT THEN
429 Log_Mesg('Start Create Task');
430 AST_UWQ_WRAPPER_PKG.CREATE_TASK
431 ( p_task_name => l_task_name,
432 p_task_type_name => NULL,
433 p_task_type_id => l_task_type_id,
434 p_description => l_description,
435 p_owner_id => l_owner_id,
436 p_customer_id => l_customer_id,
437 p_contact_id => l_contact_id,
438 p_date_type => l_date_type,
439 p_start_date => l_start_date,
440 p_end_date => l_end_date,
441 p_source_object_type_code => l_source_object_type_code,
442 p_source_object_id => l_source_object_id,
443 p_source_object_name => l_source_object_name,
444 p_phone_id => l_phone_id,
445 p_address_id => l_address_id,
446 p_duration => l_duration,
447 p_duration_uom => l_duration_uom,
448 p_called_node => l_called_node,
449 x_return_status => l_return_status,
450 x_msg_count => l_msg_count,
451 x_msg_data => l_msg_data,
452 x_task_id => l_task_id
453 );
454 Log_Mesg('End Create Task');
455
456 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
457 x_return_status := l_return_status;
458 x_msg_count := l_msg_count;
459 x_msg_data := l_msg_data;
460 --x_task_id := l_task_id;
461 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
462 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Creat Task: ', FALSE);
463 -- FND_MSG_PUB.ADD;
464 RAISE FND_API.G_EXC_ERROR;
465 ELSE
466 x_return_status := l_return_status;
467 x_msg_count := l_msg_count;
468 x_msg_data := l_msg_data;
469 --x_task_id := l_task_id;
470 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
471 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Created Task: '||TO_CHAR(l_task_id), FALSE);
472 -- FND_MSG_PUB.ADD;
473 END IF;
474
475 Log_Mesg('Task Id: '||l_task_id);
476 IF l_notes IS NOT NULL THEN
477 l_source_object_type_code := 'TASK';
478 l_source_object_id := l_task_id;
479 l_party_id := l_customer_id;
480
481 Log_Mesg('Start Create Note');
482 AST_UWQ_WRAPPER_PKG.CREATE_NOTE
483 ( p_source_object_id => l_source_object_id,
484 p_source_object_code => l_source_object_type_code,
485 p_notes => l_notes,
486 p_notes_detail => NULL,
487 p_entered_by => l_last_updated_by,
488 p_entered_date => l_last_update_date,
489 p_last_update_date => l_last_update_date,
490 p_last_updated_by => l_last_updated_by,
491 p_creation_date => l_creation_date,
492 p_created_by => l_created_by,
493 p_last_update_login => l_last_update_login,
494 p_party_id => l_party_id,
495 x_jtf_note_id => l_jtf_note_id,
496 x_return_status => l_return_status,
497 x_msg_count => l_msg_count,
498 x_msg_data => l_msg_data
499 );
500 Log_Mesg('End Create Note');
501
502 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
503 x_return_status := l_return_status;
504 x_msg_count := l_msg_count;
505 x_msg_data := l_msg_data;
506 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
507 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Creat Note', FALSE);
508 -- FND_MSG_PUB.ADD;
509 RAISE FND_API.G_EXC_ERROR;
510 ELSE
511 x_return_status := l_return_status;
512 x_msg_count := l_msg_count;
513 x_msg_data := l_msg_data;
514 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
515 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Created Note: '||TO_CHAR(l_jtf_note_id), FALSE);
516 -- FND_MSG_PUB.ADD;
517 END IF;
518 ELSE
519 Log_Mesg('User did not enter any note to create.');
520 END IF;
521 END IF;
522
523 l_prev_data_set_id := l_data_set_id;
524 END LOOP;
525 Log_Mesg('Get Work Data Loop Ended');
526
527 OLIST_WORK_NODE_REFRESH
528 ( p_action_key => p_action_key,
529 x_uwq_actions_list => x_uwq_actions_list
530 );
531
532 END; -- End procedure OLIST_NEW_TASK
533
534
535 PROCEDURE OLIST_UPDATE_OPPORTUNITY
536 ( p_action_key IN VARCHAR2,
537 p_resource_id IN NUMBER,
538 p_work_action_data IN SYSTEM.ACTION_INPUT_DATA_NST DEFAULT NULL,
539 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST,
540 x_msg_count OUT NOCOPY NUMBER,
541 x_msg_data OUT NOCOPY VARCHAR2,
542 x_return_status OUT NOCOPY VARCHAR2
543 ) IS
544
545 l_list_source_type VARCHAR2(30);
546
547 l_creation_date DATE := SYSDATE;
548 l_last_update_date DATE;
549 l_p_last_update_date DATE;
550 l_last_updated_by NUMBER := FND_PROFILE.Value('USER_ID');
551 --created by used for creating note..
552 l_created_by NUMBER := FND_PROFILE.Value('USER_ID');
553 l_last_update_login NUMBER := FND_PROFILE.Value('LOGIN_ID');
554
555 l_lead_number VARCHAR2(30);
556 l_lead_id NUMBER;
557 l_p_lead_id NUMBER; --added For R12
558 l_temp VARCHAR2(1); --added For R12
559 l_temp_id NUMBER;
560 l_admin_group_id NUMBER;
561 l_person_id NUMBER;
562 l_total_amount NUMBER;
563 l_p_total_amount NUMBER;
564 l_total_revenue_forecast_amt NUMBER; --added For R12
565 l_p_total_revenue_fore_amt NUMBER;--added For R12
566 l_admin_flag VARCHAR2(1);
567 l_resource_id NUMBER;
568 l_description VARCHAR2(240);
569 l_p_description VARCHAR2(240);
570 l_status_code VARCHAR2(30);
571 l_p_status_code VARCHAR2(30);
572 l_close_reason_code VARCHAR2(30);
573 l_p_close_reason_code VARCHAR2(30);
574 l_customer_id NUMBER;
575 l_old_customer_id NUMBER; --added for R12
576 l_contact_party_id NUMBER;
577 l_address_id NUMBER;
578 l_new_address_id NUMBER; --added for R12
579 l_sales_stage_id NUMBER;
580 l_p_sales_stage_id NUMBER;
581 l_win_probability NUMBER;
582 l_p_win_probability NUMBER;
583 l_channel_code VARCHAR2(30);
584 l_p_channel_code VARCHAR2(30);
585 l_decision_date DATE;
586 l_p_decision_date DATE;
587 --code commented for R12 Enhancement --Start
588 /* l_close_competitor_code VARCHAR2(30);
589 l_close_competitor_id NUMBER;
590 l_close_competitor VARCHAR2(80);
591 l_p_close_competitor_id NUMBER; */
592 --code commented for R12 Enhancement --End
593 l_close_comment VARCHAR2(240);
594 l_p_close_comment VARCHAR2(240);
595 l_parent_project VARCHAR2(80);
596 l_freeze_flag VARCHAR2(1);
597 l_access_flag VARCHAR2(1);
598 l_currency_code VARCHAR2(60);
599 l_vehicle_response_code VARCHAR2(2000);
600 l_customer_budget NUMBER;
601 l_p_currency_code VARCHAR2(60);
602 l_p_vehicle_response_code VARCHAR2(200);
603 l_p_customer_budget NUMBER;
604 l_salesgroup_id NUMBER;
605 l_source_promotion_id NUMBER;
606 l_p_source_promotion_id NUMBER;
607 l_validation_level_full NUMBER := 100;
608
609 l_source_object_type_code VARCHAR2(60);
610 l_source_object_id NUMBER;
611
612 l_notes VARCHAR2(2000);
613 l_note_type VARCHAR2(30);
614 l_note_status VARCHAR2(1);
615 l_party_id NUMBER;
616
617 l_outcome_id NUMBER;
618 l_reason_id NUMBER;
619 l_result_id NUMBER;
620
621 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
622 l_msg_count NUMBER;
623 l_msg_data VARCHAR2(2000);
624 l_jtf_note_id NUMBER;
625 l_err_mesg VARCHAR2(500);
626 l_err_mesg_temp VARCHAR2(500); --added for R12
627 l_dataSetId NUMBER; --added for bug#4676975
628 --Code added for R12 enhancement ---Start
629
630 l_uwq_actions_list IEU_UWQ_WORK_PANEL_PUB.UWQ_ACTION_REC_LIST;
631 l_uwq_action_data_list IEU_UWQ_WORK_PANEL_PUB.UWQ_ACTION_DATA_REC_LIST;
632 l_action_data VARCHAR2(4000);
633
634 cursor c_loc(p_cust_id in number) is select
635 party_site_id from
636 hz_party_sites site
637 where site.party_id = p_cust_id
638 and site.identifying_address_flag = 'Y';
639
640 cursor c_prop(p_lead_id number) is select 'x'
641 from PRP_PROP_OBJECTS_V
642 where object_id=p_lead_id
643 and object_type='OPPORTUNITY';
644
645 cursor c_quote(p_lead_id number) is select 'x'
646 from ASO_I_QUOTE_HEADERS_BALI_V
647 where QUOTE_HEADER_ID in
648 (select QUOTE_OBJECT_ID
649 from aso_quote_related_objects_v
650 where object_type_code = 'LDID'
651 and object_id = p_lead_id);
652 --Code added for R12 enhancement ---End
653 BEGIN
654 Log_Mesg('Inside Update Opportunity Action');
655 l_data_set_id := NULL;
656 l_prev_data_set_id := NULL;
657 l_dataSetId := NULL; --added for bug#4676975
658
659 l_resource_id := p_resource_id;
660 --freeze flag should not be reset corrected by removing jraj 08/22/03.
661
662 --added for bug#4676975 --start
663 FOR I IN 1.. p_work_action_data.COUNT LOOP
664 l_dataSetId := p_work_action_data(i).dataSetId;
665 END LOOP;
666 --added for bug#4676975 --end
667
668 FOR I IN 1.. p_work_action_data.COUNT LOOP
669 l_data_set_type := p_work_action_data(i).datasettype;
670
671 IF l_data_set_type = 'ACTION_PARAM_DATA' THEN
672 l_name := p_work_action_data(i).name;
673 l_value := p_work_action_data(i).value;
674 l_type := p_work_action_data(i).type;
675
676 Log_Mesg('Action Param Data Name: '||l_name||' ('||l_value||')');
677 IF l_name = 'PARTY_ID' THEN
678 l_customer_id := l_value;
679 ELSIF l_name = 'CUSTOMER_NAME' THEN
680
681 --added for bug#4676975 --start
682 IF l_dataSetId =1 then
683 BEGIN
684 l_customer_id := l_value;
685 EXCEPTION
686 WHEN VALUE_ERROR THEN
687 x_return_status := 'E';
688 FND_MESSAGE.Set_Name('AST', 'AST_OPP_CUST_SET_CHECK');
689 FND_MSG_PUB.ADD;
690 RAISE FND_API.G_EXC_ERROR;
691 END;
692 END if;
693 --added for bug#4676975 --End
694
695 ELSIF l_name = 'LEAD_ID' THEN
696 l_p_lead_id := l_value;
697 ELSIF l_name = 'OPPORTUNITY_NAME' THEN
698 l_description := l_value;
699 l_p_description := l_value;
700 ELSIF l_name = 'STATUS_CODE' THEN
701 l_status_code := l_value;
702 l_p_status_code := l_value;
703 ELSIF l_name = 'CLOSE_REASON' THEN
704 l_close_reason_code := l_value;
705 l_p_close_reason_code := l_value;
706 ELSIF l_name = 'SALES_STAGE' THEN
707 l_sales_stage_id := l_value;
708 l_p_sales_stage_id := l_value;
709 ELSIF l_name = 'WIN_PROBABILITY' THEN
710 l_win_probability := l_value;
711 l_p_win_probability := l_value;
712 ELSIF l_name = 'SALES_CHANNEL' THEN
713 l_channel_code := l_value;
714 l_p_channel_code := l_value;
715 ELSIF l_name = 'TOTAL_AMOUNT' THEN
716 l_total_amount := TO_NUMBER(l_value);
717 l_p_total_amount := TO_NUMBER(l_value);
718 --Code added for R12 Enhancement --Start
719 ELSIF l_name = 'FORECAST_AMOUNT' THEN
720 l_total_revenue_forecast_amt := TO_NUMBER(l_value);
721 l_p_total_revenue_fore_amt := TO_NUMBER(l_value);
722 --Code added for R12 Enhancement --End
723 ELSIF l_name = 'CLOSE_DATE' THEN
724 l_decision_date := TO_DATE(l_value, 'DD-MM-YYYY HH24:MI:SS');
725 l_p_decision_date := TO_DATE(l_value, 'DD-MM-YYYY HH24:MI:SS');
726 ELSIF l_name = 'SOURCE_CODE' THEN
727 l_source_promotion_id := l_value;
728 l_p_source_promotion_id := l_value;
729 --code modified for R12 enhancement --Start
730 /* ELSIF l_name = 'KEY_COMPETITOR_ID' THEN
731 l_close_competitor_id := l_value;
732 l_p_close_competitor_id := l_value; */
733 --code modified for R12 enhancement --end
734 ELSIF l_name = 'ADMIN_GROUP_ID' THEN
735 l_admin_group_id := l_value;
736 ELSIF l_name = 'ADMIN_FLAG' THEN
737 l_admin_flag := l_value;
738 ELSIF l_name = 'MEM_GROUP_ID' THEN
739 l_salesgroup_id := l_value;
740 ELSIF l_name = 'PERSON_ID' THEN
741 l_person_id := l_value;
742 ELSIF l_name = 'CURRENCY_CODE' THEN
743 l_p_currency_code := l_value;
744 l_currency_code := l_value;
745 ELSIF l_name = 'VEHICLE_RESPONSE_CODE' THEN
746 l_p_vehicle_response_code := l_value;
747 l_vehicle_response_code := l_value;
748 ELSIF l_name = 'CUSTOMER_BUDGET' THEN
749 l_p_customer_budget := l_value;
750 l_customer_budget := l_value;
751 /**
752 ELSIF l_name = 'LAST_UPDATE_DATE' THEN
753 l_last_update_date := TO_DATE(l_value, 'dd/mon/yyyy hh24:mi:ss');
754 l_p_last_update_date := TO_DATE(l_value, 'dd/mon/yyyy hh24:mi:ss');
755 **/
756 ELSIF l_name = 'NEW_NOTE' THEN
757 --Bug # 3525736
758 BEGIN
759 l_notes := l_value;
760 EXCEPTION
761 WHEN VALUE_ERROR THEN
762 l_return_status := 'E';
763 x_return_status := l_return_status;
764 FND_MESSAGE.Set_Name('AST', 'AST_NOTE_LENGTH_ERROR');
765 FND_MSG_PUB.ADD;
766 RAISE FND_API.G_EXC_ERROR;
767 END;
768 END IF;
769 END IF;
770 END LOOP;
771 Log_Mesg('Get Parameter Data Loop Ended');
772
773 Log_Mesg('Looping to get Work Item Data only: ' || p_work_action_data.COUNT);
774 FOR I IN 1.. p_work_action_data.COUNT LOOP
775 l_data_set_type := p_work_action_data(i).datasettype;
776 l_data_set_id := p_work_action_data(i).dataSetID;
777
778 IF l_data_set_type = 'WORK_ITEM_DATA' THEN
779 l_name := p_work_action_data(i).name;
780 l_value := p_work_action_data(i).value;
781 l_type := p_work_action_data(i).type;
782
783 Log_Mesg('Work Data Name: '||l_name||' ('||l_value||')');
784 IF l_name = 'LIST_SOURCE_TYPE' THEN
785 l_list_source_type := l_value;
786 ELSIF l_name = 'LEAD_ID' THEN
787 l_lead_id := l_value;
788 ELSIF l_name = 'LEAD_NUMBER' THEN
789 l_lead_number := l_value;
790 ELSIF l_name = 'PARTY_ID' THEN
791 -- l_customer_id := l_value; --Commented for R12
792 l_old_customer_id := l_value; --Added for R12
793
794 --added for bug#4676975 --Start
795 if l_dataSetId >1 then
796 l_customer_id := l_old_customer_id; --for test needs to be removed
797 end if;
798 --added for bug#4676975 --End
799
800 ELSIF l_name = 'CONTACT_PARTY_ID' THEN
801 l_contact_party_id := l_value;
802 ELSIF l_name = 'DESCRIPTION' AND
803 l_p_description IS NULL THEN
804 l_description := l_value;
805 ELSIF l_name = 'STATUS_CODE' AND
806 l_p_status_code IS NULL THEN
807 l_status_code := l_value;
808 ELSIF l_name = 'CLOSE_REASON' AND
809 l_p_close_reason_code IS NULL THEN
810 l_close_reason_code := l_value;
811 ELSIF l_name = 'SOURCE_PROMOTION_ID' AND
812 l_p_source_promotion_id IS NULL THEN
813 l_source_promotion_id := l_value;
814 ELSIF l_name = 'SALES_STAGE_ID' AND
815 l_p_sales_stage_id IS NULL THEN
816 l_sales_stage_id := l_value;
817 ELSIF l_name = 'WIN_PROBABILITY' AND
818 l_p_win_probability IS NULL THEN
819 l_win_probability := l_value;
820 ELSIF l_name = 'TOTAL_AMOUNT' AND
821 l_p_total_amount IS NULL THEN
822 l_total_amount := l_value;
823 --Code added for R12 Enhancement ---Start
824 ELSIF l_name = 'FORECAST_AMOUNT' AND
825 l_p_total_revenue_fore_amt IS NULL THEN
826 l_total_revenue_forecast_amt := l_value;
827 --Code added for R12 Enhancement ---End
828
829 ELSIF l_name = 'CHANNEL_CODE' AND
830 l_p_channel_code IS NULL THEN
831 l_channel_code := l_value;
832 ELSIF l_name = 'DECISION_DATE' AND
833 l_p_decision_date IS NULL THEN
834 l_decision_date := TO_DATE(l_value, 'DD-MM-YYYY HH24:MI:SS');
835 ELSIF l_name = 'LAST_UPDATE_DATE' AND
836 l_p_last_update_date IS NULL THEN
837 l_last_update_date := TO_DATE(l_value, 'DD-MM-YYYY HH24:MI:SS');
838 -- ELSIF l_name = 'LAST_UPDATED_BY' THEN
839 -- l_last_updated_by := l_value;
840 ELSIF l_name = 'ADDRESS_ID' THEN
841 l_address_id := l_value;
842 ELSIF l_name = 'PARENT_PROJECT' THEN
843 l_parent_project := l_value;
844 ELSIF l_name = 'CLOSE_COMMENT' THEN
845 l_close_comment := l_value;
846 --code modified for R12 enhancement --Start
847 /* ELSIF l_name = 'CLOSE_COMPETITOR_CODE' THEN
848 l_close_competitor_code := l_value;
849 ELSIF l_name = 'CLOSE_COMPETITOR_ID' AND
850 l_p_close_competitor_id IS NULL THEN
851 l_close_competitor_id := TO_NUMBER(l_value);
852 ELSIF l_name = 'CLOSE_COMPETITOR' THEN
853 l_close_competitor := l_value; */
854 --code modified for R12 enhancement --end
855 ELSIF l_name = 'CURRENCY_CODE' AND
856 l_p_currency_code is null THEN
857 l_currency_code := l_value;
858 ELSIF l_name = 'VEHICLE_RESPONSE_CODE' AND
859 l_p_vehicle_response_code is null THEN
860 l_vehicle_response_code := l_value;
861 ELSIF l_name = 'CUSTOMER_BUDGET' AND
862 l_p_customer_budget is null THEN
863 l_customer_budget := l_value;
864 END IF;
865 END IF;
866
867
868 --code added for R12 enhancement --Start
869 if l_customer_id is not null and
870 l_old_customer_id <> l_customer_id then
871
872
873 open c_loc(l_customer_id);
874 fetch c_loc into l_new_address_id;
875 l_address_id := l_new_address_id;
876 close c_loc;
877
878 open c_prop(l_lead_id);
879 fetch c_prop into l_temp;
880 close c_prop;
881
882 open c_quote(l_lead_id);
883 fetch c_quote into l_temp;
884 close c_quote;
885
886 if l_temp is not null then
887 l_return_status := 'E';
888 end if;
889 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
890 x_return_status := l_return_status;
891 FND_MESSAGE.Set_Name('ASN', 'ASN_PROPOSALS_QUOTES_ERR');
892 FND_MSG_PUB.ADD;
893 RAISE FND_API.G_EXC_ERROR;
894 END IF;
895
896 end if;
897 --code added for R12 enhancement --End
898
899 --Bug # 3516066
900 Log_Mesg('Start parameter validation');
901 --code modified for R12 enhancement --Start
902 /*
903 IF l_name = 'CLOSE_COMPETITOR_ID' AND
904 NVL(FND_PROFILE.Value('AS_COMPETITOR_REQUIRED'), 'N') = 'Y' AND
905 l_p_close_competitor_id IS NULL THEN
906 l_return_status := 'E';
907 END IF;
908
909 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
910 x_return_status := l_return_status;
911 FND_MESSAGE.Set_Name('AS', 'API_CLOSE_COMPETITOR_REQUIRED');
912 FND_MSG_PUB.ADD;
913 RAISE FND_API.G_EXC_ERROR;
914 END IF; */
915 --code modified for R12 enhancement --end
916 Log_Mesg('End parameter validation');
917 --End of bug # 3516066
918
919 IF l_prev_data_set_id <> l_data_set_id OR
920 i = p_work_action_data.COUNT THEN
921
922 Log_Mesg('Before has_updateOpportunityAccess ');
923 AST_ACCESS.has_updateOpportunityAccess
924 (
925 p_lead_id => l_lead_id,
926 p_admin_flag => l_admin_flag,
927 p_admin_group_id => l_admin_group_id,
928 p_person_id => l_person_id,
929 p_resource_id => p_resource_id,
930 x_return_status => l_return_status,
931 x_msg_count => l_msg_count,
932 x_msg_data => l_msg_data
933 );
934
935 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
936 x_return_status := l_return_status;
937 x_msg_count := l_msg_count;
938 x_msg_data := l_msg_data;
939 RAISE FND_API.G_EXC_ERROR;
940 END IF;
941
942
943
944
945 AST_UWQ_WRAPPER_PKG.UPDATE_OPPORTUNITY
946 ( p_admin_group_id => l_admin_group_id,
947 p_admin_flag => l_admin_flag,
948 p_resource_id => l_resource_id,
949 p_last_update_date => l_last_update_date,
950 p_lead_id => l_lead_id,
951 p_lead_number => l_lead_number,
952 p_description => l_description,
953 p_status_code => l_status_code,
954 p_close_reason_code => l_close_reason_code,
955 p_source_promotion_id => l_source_promotion_id,
956 p_customer_id => l_customer_id,
957 p_contact_party_id => l_contact_party_id,
958 p_address_id => l_address_id,
959 p_sales_stage_id => l_sales_stage_id,
960 p_win_probability => l_win_probability,
961 p_total_amount => l_total_amount,
962 p_total_revenue_forecast_amt => l_total_revenue_forecast_amt ,--Code added for R12
963 p_channel_code => l_channel_code,
964 p_decision_date => l_decision_date,
965 p_currency_code => l_currency_code,
966 p_vehicle_response_code => l_vehicle_response_code,
967 p_customer_budget => l_customer_budget,
968 --Code commented for R12 Enhancement --Start
969 /* p_close_competitor_code => l_close_competitor_code,
970 p_close_competitor_id => l_p_close_competitor_id,--Earlier l_close_competitor_id was passed. Bug # 3516066
971 p_close_competitor => l_close_competitor, */
972 --Code commented for R12 Enhancement --End
973 p_close_comment => l_close_comment,
974 p_parent_project => l_parent_project,
975 p_freeze_flag => l_freeze_flag,
976 p_called_node => l_called_node,
977 x_return_status => l_return_status,
978 x_msg_count => l_msg_count,
979 x_msg_data => l_msg_data,
980 x_lead_id => l_temp_id
981 );
982
983 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
984 x_return_status := l_return_status;
985 x_msg_count := l_msg_count;
986 x_msg_data := l_msg_data;
987 RAISE FND_API.G_EXC_ERROR;
988 ELSE
989 --Code added for R12 enhancement ---Start
990 if l_customer_id is not null and
991 l_old_customer_id <> l_customer_id then
992 null;
993 Contacts_Delete(
994 p_lead_id => l_lead_id,
995 p_resource_id => l_resource_id,
996 p_admin_flag => l_admin_flag,
997 p_admin_group_id => l_admin_group_id,
998 x_msg_count => l_msg_count,
999 x_msg_data => l_msg_data,
1000 x_return_status => l_return_status);
1001
1002 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1003 x_return_status := l_return_status;
1004 x_msg_count := l_msg_count;
1005 x_msg_data := l_msg_data;
1006 RAISE FND_API.G_EXC_ERROR;
1007 END IF;
1008
1009 Salesteam_Update(p_customer_id => l_customer_id,
1010 p_lead_id=> l_lead_id);
1011
1012 Notes_Update(
1013 p_lead_id =>l_lead_id,
1014 p_last_update_date => l_last_update_date,
1015 p_customer_id => l_customer_id
1016 );
1017 end if;
1018 --Code added for R12 enhancement ---Start
1019
1020 x_return_status := l_return_status;
1021 x_msg_count := l_msg_count;
1022 x_msg_data := l_msg_data;
1023 END IF;
1024
1025 IF l_notes IS NOT NULL THEN
1026 l_source_object_type_code := 'OPPORTUNITY';
1027 l_source_object_id := l_lead_id;
1028 l_party_id := l_customer_id;
1029
1030 AST_UWQ_WRAPPER_PKG.CREATE_NOTE
1031 ( p_source_object_id => l_source_object_id,
1032 p_source_object_code => l_source_object_type_code,
1033 p_notes => l_notes,
1034 p_notes_detail => NULL,
1035 p_entered_by => l_last_updated_by,
1036 p_entered_date => SYSDATE,
1037 p_last_update_date => SYSDATE,
1038 p_last_updated_by => l_last_updated_by,
1039 p_creation_date => l_creation_date,
1040 p_created_by => l_created_by,
1041 p_last_update_login => l_last_update_login,
1042 p_party_id => l_party_id,
1043 x_jtf_note_id => l_jtf_note_id,
1044 x_return_status => l_return_status,
1045 x_msg_count => l_msg_count,
1046 x_msg_data => l_msg_data
1047 );
1048 Log_Mesg('End Create Note');
1049
1050 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1051 x_return_status := l_return_status;
1052 x_msg_count := l_msg_count;
1053 x_msg_data := l_msg_data;
1054 RAISE FND_API.G_EXC_ERROR;
1055 ELSE
1056 x_return_status := l_return_status;
1057 x_msg_count := l_msg_count;
1058 x_msg_data := l_msg_data;
1059 END IF;
1060 ELSE
1061 Log_Mesg('User did not enter any note to create.');
1062 END IF;
1063 END IF;
1064 l_prev_data_set_id := l_data_set_id;
1065 END LOOP;
1066
1067 --Code added for R12 enhancement ---Start
1068
1069 if l_customer_id is not null and
1070 l_old_customer_id <> l_customer_id then
1071
1072 l_uwq_actions_list(1).uwq_action_key := 'UWQ_WORK_DETAILS_REFRESH';
1073 l_uwq_actions_list(1).action_data := '';
1074 l_uwq_actions_list(1).dialog_style := 2;
1075 l_uwq_actions_list(1).message := 'Please note, changing the customer for this opportunity will update the
1076 address, remove all contacts and allow the sales team to be reassigned.';
1077 IEU_UWQ_WORK_PANEL_PUB.SET_UWQ_ACTIONS(l_uwq_actions_list, x_uwq_actions_list);
1078 else
1079 OLIST_WORK_NODE_REFRESH
1080 ( p_action_key => p_action_key,
1081 p_lead_id => l_lead_id,
1082 x_uwq_actions_list => x_uwq_actions_list
1083 );
1084 end if;
1085 --Code added for R12 enhancement ---End
1086
1087 END; -- End procedure OLIST_UPDATE_OPPORTUNITY
1088
1089 PROCEDURE OLIST_CREATE_NOTE
1090 ( p_action_key IN VARCHAR2,
1091 p_resource_id IN NUMBER,
1092 p_work_action_data IN SYSTEM.ACTION_INPUT_DATA_NST DEFAULT NULL,
1093 x_uwq_actions_list OUT NOCOPY SYSTEM.IEU_UWQ_WORK_ACTIONS_NST,
1094 x_msg_count OUT NOCOPY NUMBER,
1095 x_msg_data OUT NOCOPY VARCHAR2,
1096 x_return_status OUT NOCOPY VARCHAR2
1097 ) IS
1098
1099 l_creation_date DATE := SYSDATE;
1100 l_last_update_date DATE := SYSDATE;
1101 l_last_updated_by NUMBER := FND_PROFILE.Value('USER_ID');
1102 l_created_by NUMBER := FND_PROFILE.Value('USER_ID');
1103 l_last_update_login NUMBER := FND_PROFILE.Value('LOGIN_ID');
1104
1105 l_customer_id NUMBER;
1106 l_source_object_type_code VARCHAR2(60);
1107 l_source_object_id NUMBER;
1108 l_source_object_name VARCHAR2(80);
1109
1110 l_notes VARCHAR2(2000);
1111 l_party_id NUMBER;
1112
1113 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1114 l_msg_count NUMBER;
1115 l_msg_data VARCHAR2(2000);
1116 l_task_id NUMBER;
1117 l_jtf_note_id NUMBER;
1118 l_err_mesg VARCHAR2(500);
1119 BEGIN
1120 Log_Mesg('Inside Create Note');
1121 l_data_set_id := NULL;
1122 l_prev_data_set_id := NULL;
1123
1124 Log_Mesg('Looping to get Param Item Data only.');
1125 FOR I IN 1.. p_work_action_data.COUNT LOOP
1126 l_data_set_type := p_work_action_data(i).datasettype;
1127
1128 IF l_data_set_type = 'ACTION_PARAM_DATA' THEN
1129 l_name := p_work_action_data(i).name;
1130 l_value := p_work_action_data(i).value;
1131 l_type := p_work_action_data(i).type;
1132
1133 Log_Mesg('Action Param Data Name: '||l_name||' ('||l_value||')');
1134 IF l_name = 'NEW_NOTE' THEN
1135 --Bug # 3525736
1136 BEGIN
1137 l_notes := l_value;
1138 EXCEPTION
1139 WHEN VALUE_ERROR THEN
1140 l_return_status := 'E';
1141 x_return_status := l_return_status;
1142 FND_MESSAGE.Set_Name('AST', 'AST_NOTE_LENGTH_ERROR');
1143 FND_MSG_PUB.ADD;
1144 RAISE FND_API.G_EXC_ERROR;
1145 END;
1146 END IF;
1147 ELSE
1148 EXIT;
1149 END IF;
1150 END LOOP;
1151 Log_Mesg('Get Parameter Data Loop Ended');
1152
1153 Log_Mesg('Looping to get Work Item and Param Item Data');
1154 FOR I IN 1.. p_work_action_data.COUNT LOOP
1155 l_data_set_type := p_work_action_data(i).datasettype;
1156 l_data_set_id := p_work_action_data(i).dataSetId;
1157
1158 Log_Mesg('Data Set Type: '||l_data_set_type);
1159 IF l_data_set_type = 'WORK_ITEM_DATA' THEN
1160 l_name := p_work_action_data(i).name;
1161 l_value := p_work_action_data(i).value;
1162 l_type := p_work_action_data(i).type;
1163
1164 Log_Mesg('Work Data Name: '||l_name||' ('||l_value||')');
1165 IF l_name = 'PARTY_ID' THEN
1166 l_customer_id := TO_NUMBER(l_value);
1167 ELSIF l_name = 'LEAD_ID' THEN
1168 l_source_object_id := l_value;
1169 END IF;
1170 END IF;
1171
1172 Log_Mesg('Object Type Code: '||l_source_object_type_code);
1173 Log_Mesg('Object Type id: '||l_source_object_id);
1174
1175 IF l_prev_data_set_id <> l_data_set_id OR
1176 i = p_work_action_data.COUNT THEN
1177
1178 IF l_notes IS NOT NULL THEN
1179 l_source_object_type_code := 'OPPORTUNITY';
1180 l_party_id := l_customer_id;
1181
1182 Log_Mesg('Start Create Note');
1183 AST_UWQ_WRAPPER_PKG.CREATE_NOTE
1184 ( p_source_object_id => l_source_object_id,
1185 p_source_object_code => l_source_object_type_code,
1186 p_notes => l_notes,
1187 p_notes_detail => NULL,
1188 p_entered_by => l_last_updated_by,
1189 p_entered_date => l_last_update_date,
1190 p_last_update_date => l_last_update_date,
1191 p_last_updated_by => l_last_updated_by,
1192 p_creation_date => l_creation_date,
1193 p_created_by => l_created_by,
1194 p_last_update_login => l_last_update_login,
1195 p_party_id => l_party_id,
1196 x_jtf_note_id => l_jtf_note_id,
1197 x_return_status => l_return_status,
1198 x_msg_count => l_msg_count,
1199 x_msg_data => l_msg_data
1200 );
1201 Log_Mesg('End Create Note');
1202
1203 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1204 x_return_status := l_return_status;
1205 x_msg_count := l_msg_count;
1206 x_msg_data := l_msg_data;
1207 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
1208 -- FND_MESSAGE.Set_Token('TEXT', 'Failed to Creat Note', FALSE);
1209 -- FND_MSG_PUB.ADD;
1210 RAISE FND_API.G_EXC_ERROR;
1211 ELSE
1212 x_return_status := l_return_status;
1213 x_msg_count := l_msg_count;
1214 x_msg_data := l_msg_data;
1215 -- FND_MESSAGE.Set_Name('AST', 'AST_API_ERR');
1216 -- FND_MESSAGE.Set_Token('TEXT', 'Successfully Created Note: '||TO_CHAR(l_jtf_note_id), FALSE);
1217 -- FND_MSG_PUB.ADD;
1218 END IF;
1219 ELSE
1220 Log_Mesg('User did not enter any note to create.');
1221 END IF;
1222 END IF;
1223
1224 l_prev_data_set_id := l_data_set_id;
1225 END LOOP;
1226 Log_Mesg('Get Work Data Loop Ended');
1227
1228 OLIST_WORK_NODE_REFRESH
1229 ( p_action_key => p_action_key,
1230 x_uwq_actions_list => x_uwq_actions_list
1231 );
1232
1233 END; -- End procedure OLIST_CREATE_NOTE
1234
1235 END; -- Package Body AST_UWQ_OLIST_WORK_ACTION