[Home] [Help]
PACKAGE BODY: APPS.AST_UWQ_WRAPPER_PKG
Source
1 PACKAGE BODY ast_uwq_wrapper_pkg AS
2 /* $Header: astugenb.pls 120.3 2005/08/30 22:28:20 appldev ship $ */
3
4 FUNCTION Convert_to_server_time(p_client_time IN date) return DATE
5 IS
6 l_client_tz_id number;
7 l_server_tz_id number;
8 l_msg_count number;
9 l_msg_data varchar2(2000);
10 l_start_tz_id number;
11 l_end_tz_id number;
12 l_status varchar2(2);
13 s_GMT_dev number;
14 e_GMT_dev number;
15 x_server_time date;
16
17 BEGIN
18
19 l_client_tz_id := to_number(fnd_profile.value('CLIENT_TIMEZONE_ID'));
20 l_server_tz_id := to_number(fnd_profile.value('SERVER_TIMEZONE_ID'));
21
22 HZ_TIMEZONE_PUB.Get_Time(1.0, 'F', l_server_tz_id, l_client_tz_id, p_client_time, x_server_time, l_status, l_msg_count, l_msg_data);
23
24 return x_server_time;
25
26 END;
27
28
29 PROCEDURE create_contact(
30 p_admin_flag IN VARCHAR2,
31 p_admin_group_id IN NUMBER,
32 p_resource_id IN NUMBER,
33 p_customer_id IN NUMBER,
34 p_lead_id IN NUMBER,
35 p_contact_party_id IN NUMBER,
36 p_address_id IN NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER,
39 x_msg_data OUT NOCOPY VARCHAR2
40 )
41 IS
42 l_admin_id NUMBER := p_admin_group_id;
43 v_lead_id NUMBER := p_lead_id;
44 v_contact_id NUMBER;
45 l_salesforce_id NUMBER := p_resource_id;
46 v_validation_level_full NUMBER := 100;
47 l_admin_flag VARCHAR2(1) := nvl(p_admin_flag, 'N');
48 v_true VARCHAR2(5) := 'T';
49
50 v_profile_tbl AS_UTILITY_PUB.profile_tbl_type := as_api_records_pkg.get_p_profile_tbl;
51 v_contact_tbl AS_OPPORTUNITY_PUB.contact_tbl_Type := as_api_records_pkg.get_p_contact_tbl;
52 v_contact_out_tbl AS_OPPORTUNITY_PUB.contact_out_tbl_Type := as_api_records_pkg.get_p_contact_out_tbl;
53 v_header_rec AS_OPPORTUNITY_PUB.header_rec_Type := as_api_records_pkg.get_p_header_rec;
54 BEGIN
55 v_header_rec.lead_id := v_lead_id;
56 v_header_rec.last_update_date := sysdate;
57
58 v_contact_tbl(1).contact_party_id := p_contact_party_id;
59 -- v_contact_tbl(1).rank := name_in('astopovw_header.role');
60 -- v_contact_tbl(1).phone_id := to_number(name_in('astopovw_header.phone_id'));
61 v_contact_tbl(1).address_id := p_address_id;
62 -- v_contact_tbl(1).contact_id := to_number(name_in('ASTOPOVW_HEADER.CONTACT_ID'));
63 v_contact_tbl(1).lead_id := v_lead_id;
64 v_contact_tbl(1).customer_id := p_customer_id;
65 -- v_contact_tbl(1).created_by := p_created_by;
66 v_contact_tbl(1).enabled_flag := 'Y';
67 v_contact_tbl(1).primary_contact_flag := 'Y';
68
69 AS_OPPORTUNITY_PUB.CREATE_CONTACTS(
70 p_api_version_number => 2.0,
71 p_init_msg_list => v_true,
72 p_commit => v_true,
73 p_validation_level => v_validation_level_full,
74 p_identity_salesforce_id => l_salesforce_id,
75 p_contact_tbl => v_contact_tbl,
76 p_header_rec => v_header_rec,
77 p_check_access_flag => 'N',
78 p_admin_flag => l_admin_flag,
79 p_admin_group_id => l_admin_id,
80 p_partner_cont_party_id => NULL,
81 p_profile_tbl => v_profile_tbl,
82 x_contact_out_tbl => v_contact_out_tbl,
83 x_return_status => x_return_status,
84 x_msg_count => x_msg_count,
85 x_msg_data => x_msg_data
86 );
87
88 IF x_return_status <> 'S' THEN
89 rollback;
90 return;
91 END IF;
92
93 EXCEPTION
94 WHEN OTHERS THEN
95 rollback;
96 return;
97 END Create_Contact;
98
99 PROCEDURE create_task (
100 p_task_name IN VARCHAR2,
101 p_task_type_name IN VARCHAR2,
102 p_task_type_id IN NUMBER,
103 p_description IN VARCHAR2,
104 p_owner_id IN NUMBER,
105 p_customer_id IN NUMBER,
106 p_contact_id IN NUMBER,
107 p_date_type IN VARCHAR2,
108 p_start_date IN DATE,
109 p_end_date IN DATE,
110 p_source_object_type_code IN VARCHAR2,
111 p_source_object_id IN NUMBER,
112 p_source_object_name IN VARCHAR2,
113 p_phone_id IN NUMBER,
114 p_address_id IN NUMBER,
115 p_duration IN NUMBER,
116 p_duration_uom IN VARCHAR2,
117 p_called_node IN VARCHAR2,
118 x_return_status OUT NOCOPY VARCHAR2,
119 x_msg_count OUT NOCOPY NUMBER,
120 x_msg_data OUT NOCOPY VARCHAR2,
121 x_task_id OUT NOCOPY NUMBER
122 )
123 IS
124
125 l_counter BINARY_INTEGER := 0;
126 l_person_id NUMBER;
127 l_org_id NUMBER;
128 l_address_id NUMBER;
129 l_phone_id NUMBER;
130 l_task_contact_id NUMBER;
131 l_task_or_contact_party_id NUMBER;
132 l_task_phone_id NUMBER;
133 l_api_version NUMBER := 1.0;
134 l_timezone_id NUMBER := fnd_profile.value('CLIENT_TIMEZONE_ID');
135 l_task_status_id NUMBER := fnd_profile.value('JTF_TASK_DEFAULT_TASK_STATUS');
136 l_assigned_by_id NUMBER := FND_PROFILE.VALUE('USER_ID');
137 l_task_priority_id NUMBER := fnd_profile.value('JTF_TASK_DEFAULT_TASK_PRIORITY');
138 l_init_msg_list VARCHAR2(5) := 'T';
139 l_commit VARCHAR2(5) := 'T';
140 l_task_status_name VARCHAR2(30);
141 l_date_type VARCHAR2(30);
142 l_task_priority_name VARCHAR2(30) ;
143 l_task_phone_owner_table VARCHAR2(30) := 'JTF_TASKS_B' ;
144 l_owner_type_code VARCHAR2(100);
145 l_assigned_by_name VARCHAR2(100);
146 l_source_object_name VARCHAR2(100);
147 l_owner_type_name VARCHAR2(200);
148 l_person_name VARCHAR2(100);
149 l_org_name VARCHAR2(100);
150 l_start_date DATE;
151 l_end_date DATE;
152 l_scheduled_start_date DATE;
153 l_scheduled_end_date DATE;
154 l_planned_start_date DATE;
155 l_planned_end_date DATE;
156 l_actual_start_date DATE;
157 l_actual_end_date DATE;
158 v_miss_task_assign_tbl JTF_TASKS_PUB.TASK_ASSIGN_TBL;
159 v_miss_task_depends_tbl JTF_TASKS_PUB.TASK_DEPENDS_TBL;
160 v_miss_task_rsrc_req_tbl JTF_TASKS_PUB.TASK_RSRC_REQ_TBL;
161 v_miss_task_refer_tbl JTF_TASKS_PUB.TASK_REFER_TBL;
162 v_miss_task_dates_tbl JTF_TASKS_PUB.TASK_DATES_TBL;
163 v_miss_task_notes_tbl JTF_TASKS_PUB.TASK_NOTES_TBL;
164 v_miss_task_recur_rec JTF_TASKS_PUB.TASK_RECUR_REC;
165 v_miss_task_contacts_tbl JTF_TASKS_PUB.TASK_CONTACTS_TBL;
166 v_task_refer_tbl JTF_TASKS_PUB.TASK_REFER_TBL;
167
168 --These variables are used for debugging. To be removed
169 l_count NUMBER;
170 l_index NUMBER;
171 my_message VARCHAR2(1000);
172
173
174 CURSOR C_GetDefaultStatus(x_status_id Number) IS
175 SELECT name
176 FROM jtf_task_statuses_vl
177 WHERE task_status_id = x_status_id
178 AND trunc(sysdate) BETWEEN
179 trunc(nvl(start_date_active, sysdate)) AND
180 trunc(nvl(end_date_active, sysdate));
181
182 CURSOR C_GetDefaultPriority(x_priority_id Number) IS
183 SELECT name
184 FROM jtf_task_priorities_vl
185 WHERE task_priority_id = x_priority_id
186 AND trunc(sysdate) BETWEEN
187 trunc(nvl(start_date_active, sysdate)) AND
188 trunc(nvl(end_date_active, sysdate));
189
190 CURSOR Get_OwnerType IS
191 SELECT object_code, name
192 FROM jtf_objects_vl
193 WHERE object_code in ( SELECT object_code FROM jtf_object_usages WHERE object_user_code = 'RESOURCES' )
194 AND object_code = 'RS_EMPLOYEE';
195
196 CURSOR C_GetUserName(x_user_id Number) is
197 SELECT user_name
198 FROM fnd_user
199 WHERE user_id = x_user_id;
200
201 CURSOR C_subject_and_object(x_party_id NUMBER) IS
202 SELECT subject_id, object_id
203 FROM hz_relationships
204 WHERE party_id = x_party_id
205 AND directional_flag = 'F'
206 AND status = 'A';
207
208 CURSOR C_Object_name(x_party_id NUMBER) IS
209 SELECT party_name
210 FROM Hz_parties
211 WHERE party_id = x_party_id;
212
213 CURSOR c_get_address_id (x_party_id NUMBER, x_location_id NUMBER) IS
214 SELECT party_site_id
215 FROM ast_locations_v
216 WHERE party_id = x_party_id
217 AND location_id = x_location_id;
218
219 CURSOR c_phone_id(x_owner_table_id number) IS
220 SELECT contact_point_id
221 FROM hz_contact_points
222 WHERE owner_table_id = x_owner_table_id
223 and owner_table_name = 'HZ_PARTIES'
224 and contact_point_type = 'PHONE'
225 and status = 'A'
226 and primary_flag = 'Y';
227
228 BEGIN
229 l_date_type := upper(p_date_type);
230 l_start_date := convert_to_server_time(p_start_date);
231 l_end_date := convert_to_server_time(p_end_date);
232
233 IF l_date_type = 'SCHEDULED' THEN
234 l_scheduled_start_date := l_start_date;
235 l_scheduled_end_date := l_end_date;
236 ELSIF l_date_type = 'PLANNED' THEN
237 l_planned_start_date := l_start_date;
238 l_planned_end_date := l_end_date;
239 ELSIF l_date_type = 'ACTUAL' THEN
240 l_actual_start_date := l_start_date;
241 l_actual_end_date := l_end_date;
242 END IF;
243
244 IF l_task_status_id IS NOT NULL THEN
245 OPEN C_GetDefaultStatus(l_task_status_id);
246 FETCH C_GetDefaultStatus INTO l_task_status_name;
247 CLOSE C_GetDefaultStatus;
248 END IF;
249
250 IF l_task_priority_id IS NOT NULL THEN
251 OPEN C_GetDefaultPriority(l_task_priority_id);
252 FETCH C_GetDefaultPriority INTO l_task_priority_name;
253 CLOSE C_GetDefaultPriority;
254 END IF;
255
256 IF fnd_profile.value('JTF_TASK_DEFAULT_OWNER_TYPE') <> 'RS_EMPLOYEE' THEN
257 FND_MESSAGE.Set_Name('AST', 'AST_UWQ_IMPROPER_OWNER_TYPE');
258 FND_MSG_PUB.ADD;
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 -- x_msg_count := l_msg_count;
261 -- x_msg_data := l_msg_data;
262 RETURN;
263 ELSE
264 OPEN Get_OwnerType;
265 FETCH Get_OwnerType INTO l_owner_type_code, l_owner_type_name;
266 CLOSE Get_OwnerType;
267 END IF;
268
269 IF (l_assigned_by_id IS NOT NULL) THEN
270 OPEN C_GetUserName(l_assigned_by_id);
271 FETCH C_GetUserName INTO l_assigned_by_name;
272 CLOSE C_GetUserName;
273 END IF;
274
275 l_source_object_name := p_source_object_name;
276 l_address_id := p_address_id;
277 l_phone_id := p_phone_id;
278 IF p_called_node = 'MLIST' THEN
279 IF p_address_id IS NOT NULL THEN
280 OPEN c_get_address_id(p_source_object_id, p_address_id);
281 FETCH c_get_address_id INTO l_address_id;
282 CLOSE c_get_address_id;
283 END IF;
284
285 IF p_source_object_name IS NULL THEN
286 OPEN C_Object_name(p_source_object_id);
287 FETCH C_Object_name INTO l_source_object_name;
288 CLOSE C_Object_name;
289 END IF;
290
291 /**Bug 2854526. If phone_id is null, default it with primary_phone_id **/
292 /* Fix for bug#3526419
293 Comment out the code here and moved it down as the phone number should be defaulted irrespective
294 of which node the task is created.
295 */
296
297 /*
298 IF l_phone_id IS NULL THEN
299 OPEN c_phone_id(p_source_object_id);
300 FETCH c_phone_id INTO l_phone_id;
301 CLOSE c_phone_id;
302 END IF;
303 */
304 END IF;
305
306 /**Fix for bug#3526419. If phone_id is null, default it with primary_phone_id **/
307 if p_contact_id is not null then
308 IF l_phone_id IS NULL THEN
309 OPEN c_phone_id(p_contact_id);
310 FETCH c_phone_id INTO l_phone_id;
311 CLOSE c_phone_id;
312 END IF;
313 elsif p_contact_id is null then
314 IF l_phone_id IS NULL THEN
315 OPEN c_phone_id(p_customer_id);
316 FETCH c_phone_id INTO l_phone_id;
317 CLOSE c_phone_id;
318 END IF;
319 end if;
320
321 JTF_TASKS_PUB.CREATE_TASK(
322 P_API_VERSION => l_api_version,
323 P_INIT_MSG_LIST => l_init_msg_list,
324 P_COMMIT => l_commit,
325 P_TASK_ID => null,
326 P_TASK_NAME => p_task_name,
327 P_TASK_TYPE_NAME => p_task_type_name,
328 P_TASK_TYPE_ID => p_task_type_id,
329 P_DESCRIPTION => p_description,
330 P_TASK_STATUS_NAME => l_task_status_name,
331 P_TASK_STATUS_ID => l_task_status_id,
332 P_TASK_PRIORITY_NAME => l_task_priority_name,
333 P_TASK_PRIORITY_ID => l_task_priority_id,
334 P_OWNER_TYPE_NAME => l_owner_type_name,
335 P_OWNER_TYPE_CODE => l_owner_type_code,
336 P_OWNER_ID => nvl(fnd_profile.value('JTF_TASK_DEFAULT_OWNER'),p_owner_id), --Bug # 3626890
337 P_OWNER_TERRITORY_ID => null,
338 P_ASSIGNED_BY_NAME => l_assigned_by_name,
339 P_ASSIGNED_BY_ID => l_assigned_by_id,
340 P_CUSTOMER_NUMBER => null,
341 P_CUSTOMER_ID => p_customer_id,
342 P_CUST_ACCOUNT_NUMBER => null,
343 P_CUST_ACCOUNT_ID => null,
344 P_ADDRESS_ID => l_address_id,
345 P_ADDRESS_NUMBER => null,
346 P_PLANNED_START_DATE => l_planned_start_date,
347 P_PLANNED_END_DATE => l_planned_end_date,
348 P_SCHEDULED_START_DATE => l_scheduled_start_date,
349 P_SCHEDULED_END_DATE => l_scheduled_end_date,
350 P_ACTUAL_START_DATE => l_actual_start_date,
351 P_ACTUAL_END_DATE => l_actual_end_date,
352 P_TIMEZONE_ID => l_timezone_id,
353 P_TIMEZONE_NAME => null,
354 P_SOURCE_OBJECT_TYPE_CODE => p_source_object_type_code,
355 P_SOURCE_OBJECT_ID => p_source_object_id,
356 P_SOURCE_OBJECT_NAME => l_source_object_name,
357 P_DURATION => p_duration,
358 P_DURATION_UOM => p_duration_uom,
359 P_PLANNED_EFFORT => null,
360 P_PLANNED_EFFORT_UOM => null,
361 P_ACTUAL_EFFORT => null,
362 P_ACTUAL_EFFORT_UOM => null,
363 P_PERCENTAGE_COMPLETE => null,
364 P_REASON_CODE => null,
365 P_PRIVATE_FLAG => null,
366 P_PUBLISH_FLAG => null,
367 P_RESTRICT_CLOSURE_FLAG => null,
368 P_MULTI_BOOKED_FLAG => null,
369 P_MILESTONE_FLAG => null,
370 P_HOLIDAY_FLAG => null,
371 P_BILLABLE_FLAG => null,
372 P_BOUND_MODE_CODE => null,
373 P_SOFT_BOUND_FLAG => null,
374 P_WORKFLOW_PROCESS_ID => null,
375 P_NOTIFICATION_FLAG => null,
376 P_NOTIFICATION_PERIOD => null,
377 P_NOTIFICATION_PERIOD_UOM => null,
378 P_ALARM_START => null,
379 P_ALARM_START_UOM => null,
380 P_ALARM_ON => null,
381 P_ALARM_COUNT => null,
382 P_ALARM_INTERVAL => null,
383 P_ALARM_INTERVAL_UOM => null,
384 P_PALM_FLAG => null,
385 P_WINCE_FLAG => null,
386 P_LAPTOP_FLAG => null,
387 P_DEVICE1_FLAG => null,
388 P_DEVICE2_FLAG => null,
389 P_DEVICE3_FLAG => null,
390 P_COSTS => null,
391 P_CURRENCY_CODE => null,
392 P_ESCALATION_LEVEL => null,
393 p_task_assign_tbl => v_miss_task_assign_tbl,
394 p_task_depends_tbl => v_miss_task_depends_tbl,
395 p_task_rsrc_req_tbl => v_miss_task_rsrc_req_tbl,
396 p_task_refer_tbl => v_task_refer_tbl,
397 p_task_dates_tbl => v_miss_task_dates_tbl,
398 p_task_notes_tbl => v_miss_task_notes_tbl,
399 p_task_recur_rec => v_miss_task_recur_rec,
400 p_task_contacts_tbl => v_miss_task_contacts_tbl,
401 X_RETURN_STATUS => x_return_status,
402 X_MSG_COUNT => x_msg_count,
403 X_MSG_DATA => x_msg_data,
404 X_TASK_ID => x_task_id,
405 p_attribute_category => null
406 );
407
408 IF x_return_status in ('E','U') THEN
409 ROLLBACK;
410 RETURN;
411 ELSIF x_return_status = 'S' THEN
412 l_task_or_contact_party_id := x_task_id;
413 END IF;
414
415 IF x_task_id IS NOT NULL AND p_contact_id IS NOT NULL THEN
416 JTF_TASK_CONTACTS_PUB.CREATE_TASK_CONTACTS (
417 P_API_VERSION => l_api_version,
418 P_INIT_MSG_LIST => l_init_msg_list,
419 P_COMMIT => l_commit,
420 P_TASK_ID => x_task_id,
421 P_TASK_NUMBER => NULL,
422 P_CONTACT_ID => p_contact_id,
423 P_CONTACT_TYPE_CODE => 'CUST',
424 p_ESCALATION_NOTIFY_FLAG => NULL,
425 P_ESCALATION_REQUESTER_FLAG => NULL,
426 X_TASK_CONTACT_ID => l_task_contact_id,
427 X_RETURN_STATUS => x_return_status,
428 X_MSG_DATA => x_msg_data,
429 X_MSG_COUNT => x_msg_count,
430 P_PRIMARY_FLAG => 'Y'
431 );
432
433 IF x_return_status IN ('E','U') THEN
434 ROLLBACK;
435 RETURN;
436 ELSE
437 l_task_or_contact_party_id := l_task_contact_id;
438 END IF;
439 END IF;
440
441 IF l_task_or_contact_party_id IS NOT NULL and l_phone_id IS NOT NULL THEN
442 IF p_contact_id IS NOT NULL THEN
443 l_task_phone_owner_table := 'JTF_TASK_CONTACTS';
444 ELSE
445 l_task_phone_owner_table := 'JTF_TASKS_B';
446 END IF;
447
448 JTF_TASK_PHONES_PUB.CREATE_TASK_PHONES (
449 p_api_version => l_api_version,
450 p_init_msg_list => l_init_msg_list,
451 p_commit => l_commit,
452 p_task_contact_id => l_task_or_contact_party_id,
453 p_phone_id => l_phone_id,
454 x_task_phone_id => l_task_phone_id,
455 x_return_status => x_return_status,
456 x_msg_data => x_msg_data,
457 x_msg_count => x_msg_count,
458 p_owner_table_name => l_task_phone_owner_table,
459 p_primary_flag => 'Y'
460 );
461
462 IF x_return_status IN ('E','U') THEN
463 ROLLBACK;
464 RETURN;
465 END IF;
466 END IF;
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 ROLLBACK;
471 RAISE;
472 END create_task;
473
474 PROCEDURE add_context_to_table(
475 p_counter IN BINARY_INTEGER,
476 p_context_id IN NUMBER,
477 p_context_type IN VARCHAR2,
478 p_last_update_date IN DATE,
479 p_last_updated_by IN NUMBER,
480 p_last_update_login IN NUMBER,
481 p_creation_date IN DATE,
482 p_created_by IN NUMBER
483 )
484 IS
485 BEGIN
486 g_jtf_note_contexts_tab(p_counter).note_context_type := p_context_type;
487 g_jtf_note_contexts_tab(p_counter).note_context_type_id := p_context_id;
488 g_jtf_note_contexts_tab(p_counter).last_update_date := p_last_update_date;
489 g_jtf_note_contexts_tab(p_counter).last_updated_by := p_last_updated_by;
490 g_jtf_note_contexts_tab(p_counter).last_update_login := p_last_update_login;
491 g_jtf_note_contexts_tab(p_counter).creation_date := p_creation_date;
492 g_jtf_note_contexts_tab(p_counter).created_by := p_created_by;
493 END add_context_to_table;
494
495 PROCEDURE create_note (
496 p_source_object_id IN NUMBER,
497 p_source_object_code IN VARCHAR2,
498 p_notes IN VARCHAR2,
499 p_notes_detail IN VARCHAR2,
500 p_entered_by IN NUMBER,
501 p_entered_date IN DATE,
502 p_last_update_date IN DATE,
503 p_last_updated_by IN NUMBER,
504 p_creation_date IN DATE,
505 p_created_by IN NUMBER,
506 p_last_update_login IN NUMBER,
507 p_party_id IN NUMBER,
508 x_jtf_note_id OUT NOCOPY NUMBER,
509 x_return_status OUT NOCOPY VARCHAR2,
510 x_msg_count OUT NOCOPY NUMBER,
511 x_msg_data OUT NOCOPY VARCHAR2
512 )
513 IS
514 l_api_version NUMBER := 1.0;
515 l_valid_level_full NUMBER := 100;
516 l_contact_id NUMBER;
517 l_counter NUMBER;
518 l_cust_account_id NUMBER;
519 l_subject_id NUMBER;
520 l_object_id NUMBER;
521 l_note_status VARCHAR2(3) := nvl(fnd_profile.value('JTF_NTS_NOTE_STATUS'),'I');
522 l_init_msg_list VARCHAR2(5) := 'T';
523 l_commit VARCHAR2(5) := 'T';
524 l_note_type VARCHAR2(30) := fnd_profile.value('AST_NOTES_DEFAULT_TYPE');
525
526 l_count NUMBER;
527 l_index NUMBER;
528 my_message VARCHAR2(1000);
529
530 CURSOR C_opp_contact (p_opp_id NUMBER) IS
531 SELECT contact_party_id
532 FROM as_lead_contacts
533 WHERE lead_id = p_opp_id
534 and primary_contact_flag = 'Y';
535
536 CURSOR C_lead_contact (p_lead_id NUMBER) IS
537 SELECT contact_party_id
538 FROM as_sales_lead_contacts
539 WHERE sales_lead_id = p_lead_id
540 and primary_contact_flag = 'Y';
541
542 CURSOR C_task_references(p_task_id NUMBER) IS
543 SELECT distinct object_type_code, object_id
544 FROM jtf_task_references_b
545 WHERE task_id = p_task_id;
546
547 l_task_ref_row C_task_references%ROWTYPE;
548
549 CURSOR C_del_account(p_delinquency_id NUMBER) IS
550 SELECT cust_account_id
551 FROM iex_delinquencies
552 WHERE delinquency_id = p_delinquency_id;
553
554 CURSOR C_subject_and_object(p_party_id NUMBER) IS
555 SELECT subject_id, object_id
556 FROM hz_relationships
557 WHERE party_id = p_party_id
558 AND directional_flag = 'F'
559 AND status = 'A';
560
561 BEGIN
562 g_jtf_note_contexts_tab.delete;
563 l_counter := 0;
564 IF p_source_object_code = 'OPPORTUNITY' THEN
565 IF nvl(fnd_profile.value('AS_NOTES_OPP_CONTACT'),'N') = 'Y' THEN
566 OPEN C_opp_contact(p_source_object_id);
567 FETCH C_opp_contact into l_contact_id;
568 IF C_opp_contact%FOUND THEN
569 l_counter := l_counter + 1;
570 add_context_to_table(l_counter, l_contact_id, 'PARTY',
571 p_last_update_date, p_last_updated_by, p_last_update_login,
572 p_creation_date, p_created_by);
573 END IF;
574 CLOSE C_opp_contact;
575 END IF;
576
577 IF nvl(fnd_profile.value('AS_NOTES_OPP_CUSTOMER'),'N') = 'Y' THEN
578 l_counter := l_counter + 1;
579 add_context_to_table(l_counter, p_party_id, 'PARTY',
580 p_last_update_date, p_last_updated_by, p_last_update_login,
581 p_creation_date, p_created_by);
582 END IF;
583 ELSIF p_source_object_code = 'LEAD' THEN
584 IF nvl(fnd_profile.value('AS_NOTES_LEAD_CONTACT'),'N') = 'Y' THEN
585 OPEN C_lead_contact(p_source_object_id);
586 FETCH C_lead_contact into l_contact_id;
587 IF C_lead_contact%FOUND THEN
588 l_counter := l_counter + 1;
589 add_context_to_table(l_counter, l_contact_id, 'PARTY',
590 p_last_update_date, p_last_updated_by, p_last_update_login,
591 p_creation_date, p_created_by);
592 END IF;
593 CLOSE C_lead_contact;
594 END IF;
595 IF nvl(fnd_profile.value('AS_NOTES_LEAD_CUSTOMER'),'N') = 'Y' THEN
596 l_counter := l_counter + 1;
597 add_context_to_table(l_counter, p_party_id, 'PARTY',
598 p_last_update_date, p_last_updated_by, p_last_update_login,
599 p_creation_date, p_created_by);
600 END IF;
601 ELSIF p_source_object_code = 'PARTY' THEN
602 OPEN c_subject_and_object(p_source_object_id);
603 FETCH c_subject_and_object INTO l_subject_id, l_object_id;
604 IF c_subject_and_object%FOUND THEN
605 IF nvl(fnd_profile.value('AS_NOTES_REL_OBJECT'),'N') = 'Y' THEN
606 l_counter := l_counter + 1;
607 add_context_to_table(l_counter, l_object_id, 'PARTY',
608 p_last_update_date, p_last_updated_by, p_last_update_login,
609 p_creation_date, p_created_by);
610 END IF;
611 IF nvl(fnd_profile.value('AS_NOTES_REL_SUBJECT'),'N') = 'Y' THEN
612 l_counter := l_counter + 1;
613 add_context_to_table(l_counter, l_subject_id, 'PARTY',
614 p_last_update_date, p_last_updated_by, p_last_update_login,
615 p_creation_date, p_created_by);
616 END IF;
617 END IF;
618 CLOSE c_subject_and_object;
619 ELSIF p_source_object_code = 'TASK' THEN
620 FOR l_task_ref_row in C_task_references(p_source_object_id) LOOP
621 l_counter := l_counter + 1;
622 add_context_to_table(l_counter, l_task_ref_row.object_id, l_task_ref_row.object_type_code,
623 p_last_update_date, p_last_updated_by, p_last_update_login,
624 p_creation_date, p_created_by);
625 END LOOP;
626 ELSIF p_source_object_code = 'IEX_ACCOUNT' THEN
627 l_counter := l_counter + 1;
628 add_context_to_table(l_counter, p_party_id, 'PARTY',
629 p_last_update_date, p_last_updated_by, p_last_update_login,
630 p_creation_date, p_created_by);
631 ELSIF p_source_object_code = 'IEX_DELINQUENCY' THEN
632 l_counter := l_counter + 1;
633 add_context_to_table(l_counter, p_party_id, 'PARTY',
634 p_last_update_date, p_last_updated_by, p_last_update_login,
635 p_creation_date, p_created_by);
636
637 OPEN C_del_account(p_source_object_id);
638 FETCH C_del_account into l_cust_account_id;
639 IF C_del_account%FOUND THEN
640 l_counter := l_counter + 1;
641 add_context_to_table(l_counter, l_cust_account_id, 'IEX_ACCOUNT',
642 p_last_update_date, p_last_updated_by, p_last_update_login,
643 p_creation_date, p_created_by);
644 END IF;
645 CLOSE C_del_account;
646 END IF;
647
648 JTF_NOTES_PUB.CREATE_NOTE(
649 P_API_VERSION => l_api_version,
650 P_INIT_MSG_LIST => l_init_msg_list,
651 P_COMMIT => l_commit,
652 P_JTF_NOTE_ID => null,
653 P_VALIDATION_LEVEL => l_valid_level_full,
654 P_SOURCE_OBJECT_ID => p_source_object_id,
655 P_SOURCE_OBJECT_CODE => p_source_object_code,
656 P_NOTES => p_notes,
657 P_NOTES_DETAIL => p_notes_detail,
658 P_ENTERED_BY => p_entered_by,
659 P_ENTERED_DATE => p_entered_date,
660 P_LAST_UPDATE_DATE => p_last_update_date,
661 P_LAST_UPDATED_BY => p_last_updated_by,
662 P_CREATION_DATE => p_creation_date,
663 P_CREATED_BY => p_created_by,
664 P_LAST_UPDATE_LOGIN => p_last_update_login,
665 X_JTF_NOTE_ID => x_jtf_note_id,
666 P_NOTE_TYPE => l_note_type,
667 P_NOTE_STATUS => l_note_status,
668 X_RETURN_STATUS => x_return_status,
669 X_MSG_COUNT => x_msg_count,
670 X_MSG_DATA => x_msg_data,
671 P_JTF_NOTE_CONTEXTS_TAB => g_jtf_note_contexts_tab
672 );
673
674 IF x_return_status in ('E','U') THEN
675 ROLLBACK;
676 RETURN;
677 END IF;
678
679 EXCEPTION
680 WHEN OTHERS THEN
681 ROLLBACK;
682 RAISE;
683 END create_note;
684
685 PROCEDURE header_rec_set (
686 p_last_update_date IN DATE,
687 p_lead_id IN NUMBER,
688 p_lead_number IN VARCHAR2,
689 p_description IN VARCHAR2,
690 p_status_code IN VARCHAR2,
691 p_source_promotion_id IN NUMBER,
692 p_customer_id IN NUMBER,
693 p_address_id IN NUMBER,
694 p_sales_stage_id IN NUMBER,
695 p_win_probability IN NUMBER,
696 p_total_amount IN NUMBER,
697 --New parameter added for R12 forecast amount enhancement
698 p_total_revenue_forecast_amt IN NUMBER,
699 p_channel_code IN VARCHAR2,
700 p_decision_date IN DATE,
701 p_currency_code IN VARCHAR2,
702 p_vehicle_response_code IN VARCHAR2,
703 p_customer_budget IN NUMBER,
704
705 --Code commented for R12 Enhancement --Start
706 /* p_close_competitor_code IN VARCHAR2,
707 p_close_competitor_id IN NUMBER,
708 p_close_competitor IN VARCHAR2, */
709 --Code commented for R12 Enhancement --End
710
711 p_close_comment IN VARCHAR2,
712 p_parent_project IN VARCHAR2,
713 p_freeze_flag IN VARCHAR2,
714 header_rec IN OUT NOCOPY AS_OPPORTUNITY_PUB.Header_Rec_type
715 )
716 AS
717 l_num NUMBER := AS_FOUNDATION_PUB.Get_Constant('FND_API.G_MISS_NUM');
718 l_char VARCHAR2(1) := AS_FOUNDATION_PUB.Get_Constant('FND_API.G_MISS_CHAR');
719 l_date DATE := AS_FOUNDATION_PUB.Get_Constant('FND_API.G_MISS_DATE');
720 l_last_update_date DATE;
721 --Code commented for R12 Enhancement --Start
722 --l_close_competitor VARCHAR2(4000);
723 --Code commented for R12 Enhancement --End
724 l_sales_methodology_id NUMBER := fnd_profile.value('AS_SALES_METHODOLOGY'); -- Updated by Sumita for bug # 4100911
725
726 CURSOR c_close_comp(p_close_party_id in number) IS
727 SELECT party_name
728 FROM hz_parties
729 WHERE party_id = p_close_party_id;
730 BEGIN
731
732 --To be removed once we are able to pass p_close_competitor
733 --Code modified for R12 enhancement --Start
734 /* if p_close_competitor_id is not null then
735 OPEN c_close_comp(p_close_competitor_id);
736 FETCH c_close_comp into l_close_competitor;
737 CLOSE c_close_comp;
738 end if; */
739 --Code modified for R12 enhancement --end
740
741 l_last_update_date := NVL(p_last_update_date, l_date);
742 header_rec.lead_id := NVL(p_lead_id, l_num);
743 header_rec.description := NVL(p_description, l_char);
744 header_rec.status_code := NVL(p_status_code, l_char);
745 header_rec.lead_number := NVL(p_lead_number, l_char );
746 header_rec.source_promotion_id := NVL(p_source_promotion_id, l_num);
747 header_rec.customer_id := NVL(p_customer_id, l_num);
748 header_rec.address_id := NVL(p_address_id, l_num);
749 header_rec.sales_stage_id := NVL(p_sales_stage_id, l_num);
750 header_rec.win_probability := NVL(p_win_probability, l_num);
751 -- need not set this either for creation nor updation..jraj 9/5/03.
752 --header_rec.total_amount := NVL(p_total_amount, l_num);
753 header_rec.channel_code := NVL(p_channel_code, l_char);
754 header_rec.decision_date := NVL(p_decision_date, l_date);
755 header_rec.currency_code := NVL(p_currency_code, l_char);
756 header_rec.vehicle_response_code := NVL(p_vehicle_response_code, l_char);
757 header_rec.customer_budget := NVL(p_customer_budget, l_num);
758 header_rec.close_reason := null;
759 --Code modified for R12 enhancement --Start
760 /* header_rec.close_competitor_code := NVL(p_close_competitor_code, l_char);
761 header_rec.close_competitor_id := NVL(p_close_competitor_id, l_num);
762 header_rec.close_competitor := NVL(l_close_competitor, l_char); */
763 --Code modified for R12 enhancement --end
764 header_rec.close_comment := NVL(p_close_comment, l_char);
765 -- header_rec.end_user_customer_id := null;
766 -- header_rec.end_user_address_id := null;
767 -- header_rec.end_user_customer_name := null;
768 header_rec.parent_project := NVL(p_parent_project, l_char);
769
770 -- Updated by Sumita for bug # 4100911
771 if nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'),'N') = 'Y' and p_lead_id is NULL then
772 header_rec.sales_methodology_id := NVL(l_sales_methodology_id,l_char);
773 end if;
774
775 -- header_rec.sales_methodology_id := null;
776 -- header_rec.offer_id := null;
777 header_rec.last_update_date := l_last_update_date;
778 -- header_rec.freeze_flag := NVL(p_freeze_flag, l_char);
779 /** commented by magesh the above one line for freeze flag for bug.3357959**/
780 -- header_rec.price_list_id := null;
781
782 header_rec.attribute_category := FND_API.G_MISS_CHAR;
783 header_rec.attribute1 := FND_API.G_MISS_CHAR;
784 header_rec.attribute2 := FND_API.G_MISS_CHAR;
785 header_rec.attribute3 := FND_API.G_MISS_CHAR;
786 header_rec.attribute4 := FND_API.G_MISS_CHAR;
787 header_rec.attribute5 := FND_API.G_MISS_CHAR;
788 header_rec.attribute6 := FND_API.G_MISS_CHAR;
789 header_rec.attribute7 := FND_API.G_MISS_CHAR;
790 header_rec.attribute8 := FND_API.G_MISS_CHAR;
791 header_rec.attribute9 := FND_API.G_MISS_CHAR;
792 header_rec.attribute10 := FND_API.G_MISS_CHAR;
793 header_rec.attribute11 := FND_API.G_MISS_CHAR;
794 header_rec.attribute12 := FND_API.G_MISS_CHAR;
795 header_rec.attribute13 := FND_API.G_MISS_CHAR;
796 header_rec.attribute14 := FND_API.G_MISS_CHAR;
797 header_rec.attribute15 := FND_API.G_MISS_CHAR;
798 --Code added for R12 Enhancement ---Start
799 header_rec.TOTAL_REVENUE_OPP_FORECAST_AMT := NVL(p_total_revenue_forecast_amt, l_num);
800 --Code added for R12 Enhancement ---End
801
802 END header_rec_set;
803
804 PROCEDURE create_opportunity (
805 p_admin_flag IN VARCHAR2,
806 p_admin_group_id IN NUMBER,
807 p_resource_id IN NUMBER,
808 p_last_update_date IN DATE,
809 p_lead_id IN NUMBER,
810 p_lead_number IN VARCHAR2,
811 p_description IN VARCHAR2,
812 p_status_code IN VARCHAR2,
813
814 -- Added by Sumita on 10.14.2004 for bug # 3812865
815 -- Adding source code as it is required while creating a lead if the profile OS: Source Code Required for Opportunity is set to 'yes' as we get the source
816 -- code from the view defined for bali for marketing list but in case of personal node - contacts, list is generated from the universal search where we
817 -- do not get the source code in the Bali.
818 p_source_code IN VARCHAR2,
819
820 p_source_code_id IN NUMBER,
821 -- End Mod.
822 p_customer_id IN NUMBER,
823 p_contact_party_id IN NUMBER,
824 p_address_id IN NUMBER,
825 p_sales_stage_id IN NUMBER,
826 p_win_probability IN NUMBER,
827 p_total_amount IN NUMBER,
828 p_total_revenue_forecast_amt IN NUMBER,
829 p_channel_code IN VARCHAR2,
830 p_decision_date IN DATE,
831 p_currency_code IN VARCHAR2,
832 p_vehicle_response_code IN VARCHAR2,
833 p_customer_budget IN NUMBER,
834
835 --Code commented for R12 Enhancement --Start
836 /* p_close_competitor_code IN VARCHAR2,
837 p_close_competitor_id IN NUMBER,
838 p_close_competitor IN VARCHAR2, */
839 --Code commented for R12 Enhancement --End
840
841 p_close_comment IN VARCHAR2,
842 p_parent_project IN VARCHAR2,
843 p_freeze_flag IN VARCHAR2,
844 p_salesgroup_id IN NUMBER,
845 p_called_node IN VARCHAR2,
846 p_action_key IN VARCHAR2,
847 x_return_status OUT NOCOPY VARCHAR2,
848 x_msg_count OUT NOCOPY NUMBER,
849 x_msg_data OUT NOCOPY VARCHAR2,
850 x_lead_id OUT NOCOPY NUMBER
851 )
852 AS
853 l_api_version NUMBER := 2.0;
854 l_valid_level_full NUMBER := 100;
855 l_address_id NUMBER;
856 l_address_profile VARCHAR2(3) := FND_PROFILE.value('AST_WP_USE_ADDRESS_FOR_OPP');
857 l_admin_flag VARCHAR2(1) := nvl(p_admin_flag, 'N');
858 l_init_msg_list VARCHAR2(5) := 'T';
859 l_commit VARCHAR2(5) := 'T';
860 header_rec AS_OPPORTUNITY_PUB.Header_Rec_Type := AS_API_RECORDS_PKG.get_p_header_rec;
861 v_profile_tbl AS_UTILITY_PUB.PROFILE_TBL_TYPE := AS_API_RECORDS_PKG.get_p_profile_tbl;
862 l_vehicle_response_code VARCHAR2(200) := nvl(p_vehicle_response_code, FND_PROFILE.value('AS_OPP_RESPONSE_CODE'));
863
864 -- Added by Sumita on 10.14.2004 for bug # 3812865
865 l_source_promotion_id NUMBER;
866 l_action_key VARCHAR2(30) := p_action_key;
867 s_source_code_id number;
868 -- End Mod.
869
870 l_count NUMBER;
871 l_index NUMBER;
872 my_message VARCHAR2(1000);
873
874 CURSOR c_get_primary_address_id IS
875 SELECT party_site_id
876 FROM hz_party_sites
877 WHERE party_id = p_customer_id
878 AND identifying_address_flag = 'Y'
879 AND status = 'A';
880
881 CURSOR c_get_address_id (x_party_id NUMBER, x_location_id NUMBER) IS
882 SELECT party_site_id
883 FROM ast_locations_v
884 WHERE party_id = x_party_id
885 AND location_id = x_location_id;
886
887
888 -- Added by Sumita on 10.14.2004 for bug # 3812865
889 -- Cursor c_source_prom_id is required for personal list - contact, so that source_code_id can be retrieved from source_code
890
891 CURSOR c_source_prom_id (p_source_code VARCHAR2) IS
892 SELECT source_code_id
893 FROM Ams_source_codes
894 WHERE source_code = p_source_code;
895
896 -- End Mod.
897 /* Added for R12 */
898 l_default_org_id number;
899 l_default_ou_name varchar2(240);
900 l_ou_count number;
901 BEGIN
902 l_address_id := p_address_id;
903
904 if nvl(l_address_profile, 'N') = 'Y' then
905 open c_get_primary_address_id;
906 fetch c_get_primary_address_id into l_address_id;
907 close c_get_primary_address_id;
908 else
909 IF p_customer_id IS NOT NULL AND p_address_id IS NOT NULL THEN
910 OPEN c_get_address_id(p_customer_id, p_address_id);
911 FETCH c_get_address_id INTO l_address_id;
912 CLOSE c_get_address_id;
913 END IF;
914 end if;
915
916
917 -- Added by Sumita on 10.14.2004 for bug # 3812865
918 -- Cursor c_source_prom_id is required for personal list - contact, so that source_code_id can be retrieved from source_code
919 IF l_action_key = 'PLIST_CREATE_OPPORTUNITY' THEN
920 IF p_source_code IS NOT NULL THEN
921 OPEN c_source_prom_id(p_source_code);
922 FETCH c_source_prom_id INTO l_source_promotion_id;
923 CLOSE c_source_prom_id;
924 END IF;
925 s_source_code_id := l_source_promotion_id;
926 ELSE
927 s_source_code_id := p_source_code_id;
928 END IF;
929 -- End Mod.
930 /* Added for R12 */
931 MO_GLOBAL.INIT('AST');
932 mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count);
933 header_rec.org_id := l_default_org_id;
934 Header_Rec_Set(
935 p_last_update_date,
936 p_lead_id ,
937 p_lead_number,
938 p_description,
939 p_status_code,
940 s_source_code_id,
941 p_customer_id,
942 l_address_id,
943 p_sales_stage_id,
944 p_win_probability,
945 p_total_amount,
946 p_total_revenue_forecast_amt,
947 p_channel_code,
948 p_decision_date,
949 p_currency_code,
950 l_vehicle_response_code,
951 p_customer_budget,
952 --Code commented for R12 Enhancement --Start
953 /* p_close_competitor_code,
954 p_close_competitor_id,
955 p_close_competitor , */
956 --Code commented for R12 Enhancement --End
957 p_close_comment,
958 p_parent_project,
959 p_freeze_flag ,
960 header_rec
961 );
962
963
964 AS_OPPORTUNITY_PUB.Create_Opp_Header
965 (
966 p_api_version_number => l_api_version,
967 p_init_msg_list => l_init_msg_list,
968 p_commit => l_commit,
969 p_validation_level => l_valid_level_full,
970 p_header_rec => header_rec,
971 p_check_access_flag => 'N',
972 p_admin_flag => l_admin_flag,
973 p_admin_group_id => p_admin_group_id,
974 p_salesgroup_id => p_salesgroup_id,
975 p_identity_salesforce_id => p_resource_id,
976 p_profile_tbl => v_profile_tbl,
977 p_partner_cont_party_id => null,
978 x_return_status => x_return_status,
979 x_msg_count => x_msg_count,
980 x_msg_data => x_msg_data,
981 x_lead_id => x_lead_id
982 );
983
984 IF x_return_status IN ('E','U') THEN
985 ROLLBACK;
986 RETURN;
987 END IF;
988
989 IF p_contact_party_id IS NOT NULL THEN
990 ast_uwq_wrapper_pkg.create_contact(
991 p_admin_flag => l_admin_flag,
992 p_admin_group_id => p_admin_group_id,
993 p_resource_id => p_resource_id,
994 p_customer_id => p_customer_id,
995 p_lead_id => x_lead_id,
996 p_contact_party_id => p_contact_party_id,
997 p_address_id => l_address_id,
998 x_return_status => x_return_status,
999 x_msg_count => x_msg_count,
1000 x_msg_data => x_msg_data
1001 );
1002
1003 END IF;
1004
1005 EXCEPTION
1006 when OTHERS THEN
1007 ROLLBACK;
1008 RAISE;
1009 END create_opportunity;
1010
1011 PROCEDURE create_lead (
1012 p_admin_group_id IN NUMBER,
1013 p_identity_salesforce_id IN NUMBER,
1014 p_status_code IN VARCHAR2,
1015 p_customer_id IN NUMBER,
1016 p_contact_party_id IN NUMBER,
1017 p_address_id IN NUMBER,
1018 p_admin_flag IN VARCHAR2,
1019 p_assign_to_salesforce_id IN NUMBER,
1020 p_assign_sales_group_id IN NUMBER,
1021 p_budget_status_code IN VARCHAR2,
1022 p_description IN VARCHAR2,
1023
1024 -- Added by Sumita on 10.14.2004 for bug # 3812865
1025 -- Adding source code as it is required while creating a lead if the profile OS: Source Code Mandatory for Leads is set to 'yes' as we get the source
1026 -- code from the view defined for bali for marketing list but in case of personal node - contacts, list is generated from the universal search where we
1027 -- do not get the source code in the Bali.
1028
1029 p_source_code IN VARCHAR2,
1030
1031 p_source_code_id IN NUMBER,
1032 -- End Mod.
1033 p_lead_rank_id IN NUMBER,
1034 p_decision_timeframe_code IN VARCHAR2,
1035 p_initiating_contact_id IN NUMBER,
1036 p_phone_id IN NUMBER,
1037 p_called_node IN VARCHAR2,
1038 --sumita
1039 p_action_key IN VARCHAR2,
1040
1041 x_sales_lead_id OUT NOCOPY NUMBER,
1042 x_return_status OUT NOCOPY VARCHAR2,
1043 x_msg_count OUT NOCOPY NUMBER,
1044 x_msg_data OUT NOCOPY VARCHAR2
1045 )
1046 AS
1047 l_last_update_date DATE;
1048 l_address_id NUMBER;
1049 l_access_id NUMBER;
1050 v_access_id NUMBER;
1051 l_api_version NUMBER := 2.0;
1052 l_assign_to_person_id NUMBER;
1053 l_assign_to_salesforce_id NUMBER;
1054 l_assign_sales_group_id NUMBER;
1055 l_validation_level NUMBER := NVL(FND_PROFILE.VALUE('AST_SL_DEBUG_VALID_LEVEL'),0);
1056 l_org_contact_id NUMBER;
1057 l_phone_id NUMBER := null;
1058 l_admin_flag VARCHAR2(1) := nvl(p_admin_flag, 'N');
1059 l_address_profile VARCHAR2(3) := FND_PROFILE.value('AST_WP_USE_ADDRESS_FOR_LEAD');
1060 l_init_msg_list VARCHAR2(5) := 'T';
1061 l_commit VARCHAR2(5) := 'F';
1062 l_channel_code VARCHAR2(30) := FND_PROFILE.VALUE('AS_DEFAULT_LEAD_CHANNEL');
1063 l_vehicle_response_code VARCHAR2(30) := FND_PROFILE.VALUE('AS_DEFAULT_LEAD_VEHICLE_RESPONSE_CODE');
1064 l_contact_role_code VARCHAR2(30) := FND_PROFILE.VALUE('AS_DEFAULT_CONTACT_ROLE');
1065 l_full_name VARCHAR2(240);
1066
1067 -- Added by Sumita on 10.14.2004 for bug # 3812865
1068 l_source_promotion_id NUMBER;
1069 -- End Mod.
1070
1071 l_sales_lead_profile_tbl AS_UTILITY_PUB.profile_tbl_type;
1072 l_sales_lead_line_tbl AS_SALES_LEADS_PUB.sales_lead_line_tbl_type;
1073 l_sales_lead_rec AS_SALES_LEADS_PUB.sales_lead_rec_type;
1074 l_sales_lead_contact_tbl AS_SALES_LEADS_PUB.sales_lead_contact_tbl_type;
1075 l_sales_lead_contact_rec AS_SALES_LEADS_PUB.sales_lead_contact_rec_type;
1076 l_sales_lead_line_out_tbl AS_SALES_LEADS_PUB.sales_lead_line_out_tbl_type;
1077 l_sales_lead_contact_out_tbl AS_SALES_LEADS_PUB.sales_lead_cnt_out_tbl_type;
1078 G_Access_Rec_Type AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
1079 l_salesteam_rec as_access_pub.sales_team_rec_type:=as_api_records_pkg.get_p_sales_team_rec;
1080
1081 -- Added by Sumita on 10.14.2004 for bug # 3812865
1082 l_action_key varchar2(100) := p_action_key;
1083 -- End Mod.
1084
1085 l_count NUMBER;
1086 l_index NUMBER;
1087 my_message VARCHAR2(1000);
1088
1089
1090 CURSOR c_get_org_contact_id(X_PARTY_ID NUMBER) IS
1091 SELECT o.org_contact_id
1092 FROM hz_relationships r,
1093 hz_org_contacts o
1094 WHERE o.party_relationship_id = r.relationship_id
1095 AND r.party_id = X_PARTY_ID
1096 AND r.directional_flag = 'F';
1097
1098 CURSOR c_phone_id(x_owner_table_id number) IS
1099 SELECT contact_point_id
1100 FROM hz_contact_points
1101 WHERE owner_table_id = x_owner_table_id
1102 and owner_table_name = 'HZ_PARTIES'
1103 and contact_point_type = 'PHONE'
1104 and primary_flag = 'Y';
1105
1106 CURSOR C_GET_LEAD_NUMBER(p_sales_lead_id number) IS
1107 SELECT LEAD_NUMBER FROM AS_SALES_LEADS
1108 WHERE SALES_LEAD_ID = p_sales_lead_id;
1109
1110 CURSOR C_RESOURCE(p_resource_id NUMBER) IS
1111 SELECT SOURCE_NAME, SOURCE_ID
1112 FROM JTF_RS_RESOURCE_EXTNS
1113 WHERE RESOURCE_ID = p_resource_id;
1114
1115 CURSOR c_get_primary_address_id IS
1116 SELECT party_site_id
1117 FROM hz_party_sites
1118 WHERE party_id = p_customer_id
1119 AND identifying_address_flag = 'Y'
1120 AND status = 'A';
1121
1122 CURSOR c_get_address_id (x_party_id NUMBER, x_location_id NUMBER) IS
1123 SELECT party_site_id
1124 FROM ast_locations_v
1125 WHERE party_id = x_party_id
1126 AND location_id = x_location_id;
1127
1128
1129 -- Added by Sumita on 10.14.2004 for bug # 3812865
1130 -- Cursor c_source_prom_id is required for personal list - contact, so that source_code_id can be retrieved from source_code
1131
1132 CURSOR c_source_prom_id (p_source_code VARCHAR2) IS
1133 SELECT source_code_id
1134 FROM Ams_source_codes
1135 WHERE source_code = p_source_code;
1136
1137 -- End Mod.
1138
1139 /**
1140 CURSOR c_get_access_id (x_sales_lead_id NUMBER) IS
1141 SELECT access_id, last_update_datE
1142 FROM as_accesses_all
1143 WHERE sales_lead_id = x_sales_lead_id;
1144 **/
1145 BEGIN
1146 IF (p_assign_to_salesforce_id IS NOT NULL) THEN
1147 OPEN c_resource(p_assign_to_salesforce_id);
1148 FETCH c_resource into l_full_name, l_assign_to_person_id;
1149 CLOSE c_resource;
1150 END IF;
1151
1152 IF l_admin_flag = 'N' THEN
1153 l_assign_sales_group_id := p_assign_sales_group_id;
1154 END IF;
1155
1156 l_address_id := p_address_id;
1157 IF nvl(l_address_profile, 'N') = 'Y' THEN
1158 OPEN c_get_primary_address_id;
1159 FETCH c_get_primary_address_id INTO l_address_id;
1160 CLOSE c_get_primary_address_id;
1161 ELSE
1162 IF p_customer_id IS NOT NULL AND p_address_id IS NOT NULL THEN
1163 OPEN c_get_address_id(p_customer_id, p_address_id);
1164 FETCH c_get_address_id INTO l_address_id;
1165 CLOSE c_get_address_id;
1166 END IF;
1167 END IF;
1168
1169
1170 -- Added by Sumita on 10.14.2004 for bug # 3812865
1171 -- Cursor c_source_prom_id is required for personal list - contact, so that source_prom_id can be retrieved from source_code
1172
1173 IF l_action_key = 'PLIST_CREATE_LEAD' THEN
1174 IF p_source_code IS NOT NULL THEN
1175 OPEN c_source_prom_id(p_source_code);
1176 FETCH c_source_prom_id INTO l_source_promotion_id;
1177 CLOSE c_source_prom_id;
1178 END IF;
1179 END IF;
1180
1181 -- End Mod.
1182
1183 l_sales_lead_rec.status_code := p_status_code;
1184 l_sales_lead_rec.customer_id := p_customer_id;
1185
1186 l_sales_lead_rec.address_id := l_address_id;
1187 l_sales_lead_rec.assign_to_person_id := l_assign_to_person_id;
1188 l_sales_lead_rec.assign_to_salesforce_id := p_assign_to_salesforce_id;
1189 l_sales_lead_rec.assign_sales_group_id := l_assign_sales_group_id;
1190 l_sales_lead_rec.channel_code := l_channel_code;
1191 l_sales_lead_rec.close_reason := null;
1192 l_sales_lead_rec.reject_reason_code := null;
1193 l_sales_lead_rec.budget_amount := null;
1194 l_sales_lead_rec.budget_status_code := p_budget_status_code;
1195 l_sales_lead_rec.currency_code := null;
1196 l_sales_lead_rec.decision_timeframe_code := p_decision_timeframe_code;
1197 l_sales_lead_rec.description := p_description;
1198
1199
1200 -- Added by Sumita on 10.14.2004 for bug # 3812865
1201 -- If condition is added so that if lead is created from the personal list - contacts, l_sales_lead_rec will store source_promotion_id from the
1202 -- cursor else it will get the value from the view directly
1203 IF l_action_key = 'PLIST_CREATE_LEAD' THEN
1204 l_sales_lead_rec.source_promotion_id := l_source_promotion_id;
1205 ELSE
1206 l_sales_lead_rec.source_promotion_id := p_source_code_id;
1207 END IF;
1208 -- End Mod.
1209
1210 l_sales_lead_rec.offer_id := null;
1211 l_sales_lead_rec.parent_project := null;
1212 l_sales_lead_rec.lead_rank_id := p_lead_rank_id;
1213 l_sales_lead_rec.initiating_contact_id := p_initiating_contact_id; --check
1214 l_sales_lead_rec.urgent_flag := null;
1215 l_sales_lead_rec.accept_flag := null;
1216 l_sales_lead_rec.qualified_flag := null;
1217 l_sales_lead_rec.vehicle_response_code := l_vehicle_response_code; --check
1218
1219 l_sales_lead_contact_rec.enabled_flag := 'Y';
1220 l_sales_lead_contact_rec.customer_id := p_customer_id;
1221 l_sales_lead_contact_rec.address_id := l_address_id;
1222
1223 IF l_sales_lead_contact_rec.contact_id is null and l_sales_lead_contact_rec.contact_party_id IS NOT NULL THEN
1224 Open c_get_org_contact_id(l_sales_lead_contact_rec.contact_party_id);
1225 FETCH c_get_org_contact_id into l_org_contact_id;
1226 if c_get_org_contact_id%FOUND THEN
1227 l_sales_lead_contact_rec.contact_id := l_org_contact_id;
1228 END IF;
1229 CLOSE c_get_org_contact_id;
1230 END IF;
1231
1232 l_sales_lead_contact_rec.primary_contact_flag := 'Y';
1233 l_sales_lead_contact_rec.contact_party_id := p_contact_party_id;
1234 l_sales_lead_contact_rec.contact_role_code := l_contact_role_code;
1235 l_sales_lead_contact_rec.phone_id := p_phone_id;
1236
1237 if l_sales_lead_contact_rec.phone_id is null and l_sales_lead_contact_rec.contact_party_id IS NOT NULL THEN
1238 Open c_phone_id(l_sales_lead_contact_rec.contact_party_id);
1239 FETCH c_phone_id into l_phone_id;
1240 if c_phone_id%FOUND THEN
1241 l_sales_lead_contact_rec.phone_id := l_phone_id;
1242 END IF;
1243 CLOSE c_phone_id;
1244 END IF;
1245
1246 l_sales_lead_contact_rec.primary_contact_flag := 'Y';
1247
1248 if (l_sales_lead_contact_rec.contact_party_id IS NOT NULL) THEN
1249 l_sales_lead_contact_tbl(1) := l_sales_lead_contact_rec;
1250 END IF;
1251
1252 G_Access_Rec_Type.cust_access_profile_value := nvl(FND_PROFILE.VALUE('AS_CUST_ACCESS'),'F');
1253 G_Access_Rec_Type.lead_access_profile_value := nvl(FND_PROFILE.VALUE('AS_LEAD_ACCESS'),'F');
1254 G_Access_Rec_Type.opp_access_profile_value := nvl(FND_PROFILE.VALUE('AS_OPP_ACCESS'),'F');
1255 G_Access_Rec_Type.mgr_update_profile_value := nvl(FND_PROFILE.VALUE('AS_MGR_UPDATE'),'R');
1256 G_Access_Rec_Type.admin_update_profile_value := nvl(FND_PROFILE.VALUE('AS_ADMIN_UPDATE'),'R');
1257
1258 l_sales_lead_profile_tbl(1).profile_name := 'AS_CUST_ACCESS';
1259 l_sales_lead_profile_tbl(1).profile_value := G_access_rec_type.cust_access_profile_value;
1260 l_sales_lead_profile_tbl(2).profile_name := 'AS_LEAD_ACCESS';
1261 l_sales_lead_profile_tbl(2).profile_value := G_access_rec_type.lead_access_profile_value;
1262 l_sales_lead_profile_tbl(3).profile_name := 'AS_OPP_ACCESS';
1263 l_sales_lead_profile_tbl(3).profile_value := G_access_rec_type.opp_access_profile_value;
1264 l_sales_lead_profile_tbl(4).profile_name := 'AS_MGR_UPDATE';
1265 l_sales_lead_profile_tbl(4).profile_value := G_access_rec_type.mgr_update_profile_value;
1266 l_sales_lead_profile_tbl(5).profile_name := 'AS_ADMIN_UPDATE';
1267 l_sales_lead_profile_tbl(5).profile_value := G_access_rec_type.admin_update_profile_value;
1268
1269 AS_SALES_LEADS_PUB.create_sales_lead(
1270 P_API_VERSION_NUMBER => l_api_version
1271 ,P_INIT_MSG_LIST => l_init_msg_list
1272 ,P_COMMIT => l_commit
1273 ,P_VALIDATION_LEVEL => l_validation_level
1274 ,P_CHECK_ACCESS_FLAG => 'N'
1275 ,P_ADMIN_FLAG => l_admin_flag
1276 ,P_ADMIN_GROUP_ID => p_admin_group_id
1277 ,P_IDENTITY_SALESFORCE_ID => p_identity_salesforce_id
1278 ,P_SALES_LEAD_PROFILE_TBL => l_sales_lead_profile_tbl
1279 ,P_SALES_LEAD_REC => l_sales_lead_rec
1280 ,P_SALES_LEAD_LINE_TBL => l_sales_lead_line_tbl
1281 ,P_SALES_LEAD_CONTACT_TBL => l_sales_lead_contact_tbl
1282 ,X_SALES_LEAD_ID => x_sales_lead_id
1283 ,X_RETURN_STATUS => x_return_status
1284 ,X_MSG_COUNT => x_msg_count
1285 ,X_MSG_DATA => x_msg_data
1286 ,X_SALES_LEAD_LINE_OUT_TBL=> l_sales_lead_line_out_tbl
1287 ,X_SALES_LEAD_CNT_OUT_TBL => l_sales_lead_contact_out_tbl
1288 );
1289
1290 IF x_return_status in ('E','U') THEN
1291 ROLLBACK;
1292 RETURN;
1293 END IF;
1294
1295 /** Commenting out following lines. Keep Flag (freeze_flag) should
1296 be set to 'Y'.
1297 When we create a sales lead it is set to 'Y' by default. To follow 11.5.7 logic freeze_flag was
1298 explicitely set to 'N'. Now for the later releases we just have to comment out this
1299 part. **/
1300
1301 /**
1302 OPEN c_get_access_id (x_sales_lead_id);
1303 FETCH c_get_access_id INTO l_access_id, l_last_update_date;
1304 CLOSE c_get_access_id;
1305
1306 --Updating SalesTeam to set Keep Flag (freeze_flag) to 'N'
1307
1308 l_salesteam_rec.access_id := l_access_id;
1309 l_salesteam_rec.sales_group_id := l_assign_sales_group_id;
1310 l_salesteam_rec.freeze_flag := 'N';
1311 -- l_salesteam_rec.last_update_date := sysdate;
1312 l_salesteam_rec.last_update_date := l_last_update_date;
1313 l_salesteam_rec.customer_id := p_customer_id;
1314 l_salesteam_rec.sales_lead_id := x_sales_lead_id;
1315 l_salesteam_rec.salesforce_id := p_assign_to_salesforce_id;
1316 l_salesteam_rec.person_id := l_assign_to_person_id;
1317 l_salesteam_rec.address_id := p_address_id;
1318
1319 AS_ACCESS_PUB.Update_SalesTeam
1320 (
1321 p_api_version_number => l_api_version,
1322 p_init_msg_list => l_init_msg_list,
1323 p_commit => l_commit,
1324 p_validation_level => l_validation_level,
1325 p_access_profile_rec => G_Access_Rec_Type,
1326 p_check_access_flag => 'Y',
1327 p_admin_flag => l_admin_flag,
1328 p_admin_group_id => p_admin_group_id,
1329 p_identity_salesforce_id => p_identity_salesforce_id,
1330 p_sales_team_rec => l_salesteam_rec,
1331 x_return_status => x_return_status,
1332 x_msg_count => x_msg_count,
1333 x_msg_data => x_msg_data,
1334 x_access_id => v_access_id
1335 );
1336
1337 IF x_return_status <> 'S' THEN
1338 ROLLBACK;
1339 RETURN;
1340 END IF;
1341 **/
1342 /** Adding this to fix bug 2918647. We should call Lead_Process_After_Create
1343 after create_sales_lead.
1344 **/
1345 AS_SALES_LEADS_PUB.Lead_Process_After_Create(
1346 P_Api_Version_Number => l_api_version
1347 ,P_Init_Msg_List => l_init_msg_list
1348 ,P_Commit => l_commit
1349 ,P_Validation_Level => l_validation_level
1350 ,P_check_access_flag => 'N'
1351 ,P_admin_flag => l_admin_flag
1352 ,P_Admin_Group_Id => p_admin_group_id
1353 ,P_identity_salesforce_id => p_identity_salesforce_id
1354 ,P_SalesGroup_Id => l_assign_sales_group_id
1355 ,P_Sales_Lead_Id => x_sales_lead_id
1356 ,X_Return_Status => x_return_status
1357 ,X_Msg_Count => x_msg_count
1358 ,X_Msg_Data => x_msg_data
1359 );
1360
1361 if x_return_status not in ('W','S') then
1362 rollback;
1363 return;
1364 end if;
1365
1366 EXCEPTION
1367 WHEN OTHERS THEN
1368 ROLLBACK;
1369 RAISE;
1370 END create_lead;
1371
1372 PROCEDURE update_lead (
1373 p_sales_lead_id IN NUMBER := FND_API.G_MISS_NUM,
1374 p_admin_group_id IN NUMBER := FND_API.G_MISS_NUM,
1375 p_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
1376 p_last_update_date IN DATE,
1377 p_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1378 p_customer_id IN NUMBER := FND_API.G_MISS_NUM,
1379 p_address_id IN NUMBER := FND_API.G_MISS_NUM,
1380 p_assign_to_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
1381 p_admin_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1382 p_assign_sales_group_id IN NUMBER := FND_API.G_MISS_NUM,
1383 p_budget_status_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1384 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
1385 p_source_promotion_id IN NUMBER := FND_API.G_MISS_NUM,
1386 p_lead_rank_id IN NUMBER := FND_API.G_MISS_NUM,
1387 p_decision_timeframe_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1388 p_initiating_contact_id IN NUMBER := FND_API.G_MISS_NUM,
1389 p_accept_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1390 p_qualified_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1391 p_phone_id IN NUMBER := FND_API.G_MISS_NUM,
1392 p_close_reason_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
1393 p_called_node IN VARCHAR2 := FND_API.G_MISS_CHAR,
1394 x_return_status OUT NOCOPY VARCHAR2,
1395 x_msg_count OUT NOCOPY NUMBER,
1396 x_msg_data OUT NOCOPY VARCHAR2
1397 )
1398 IS
1399 l_api_version NUMBER := 2.0;
1400 l_validation_level NUMBER := NVL(FND_PROFILE.VALUE('AST_SL_DEBUG_VALID_LEVEL'),0);
1401 l_assign_sales_group_id NUMBER;
1402 l_assign_to_person_id NUMBER;
1403 l_admin_flag VARCHAR2(1) := nvl(p_admin_flag, 'N');
1404 l_init_msg_list VARCHAR2(5) := 'T';
1405 l_commit VARCHAR2(5) := 'T';
1406 l_channel_code VARCHAR2(30) := FND_PROFILE.VALUE('AS_DEFAULT_LEAD_CHANNEL');
1407 l_vehicle_response_code VARCHAR2(30) := FND_PROFILE.VALUE('AS_DEFAULT_LEAD_VEHICLE_RESPONSE_CODE');
1408 l_sales_lead_profile_tbl AS_UTILITY_PUB.profile_tbl_type;
1409 l_sales_lead_line_tbl AS_SALES_LEADS_PUB.sales_lead_line_tbl_type;
1410 l_sales_lead_rec AS_SALES_LEADS_PUB.sales_lead_rec_type := AS_API_RECORDS_PKG.get_p_sales_lead_rec;
1411 l_sales_lead_contact_tbl AS_SALES_LEADS_PUB.sales_lead_contact_tbl_type;
1412 l_sales_lead_line_out_tbl AS_SALES_LEADS_PUB.sales_lead_line_out_tbl_type;
1413
1414 G_Access_Rec_Type AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
1415
1416 l_count NUMBER;
1417 l_index NUMBER;
1418 my_message VARCHAR2(1000);
1419
1420 CURSOR C_RESOURCE(p_resource_id NUMBER) is
1421 SELECT SOURCE_ID
1422 FROM JTF_RS_RESOURCE_EXTNS
1423 WHERE RESOURCE_ID = p_resource_id;
1424 BEGIN
1425
1426
1427 IF (p_assign_to_salesforce_id IS NOT NULL) THEN
1428 OPEN c_resource(p_assign_to_salesforce_id);
1429 FETCH c_resource into l_assign_to_person_id;
1430 CLOSE c_resource;
1431 END IF;
1432
1433
1434 IF l_admin_flag = 'N' THEN
1435 l_assign_sales_group_id := p_assign_sales_group_id;
1436 END IF;
1437
1438 l_sales_lead_rec.sales_lead_id := p_sales_lead_id;
1439 l_sales_lead_rec.status_code := p_status_code;
1440 l_sales_lead_rec.customer_id := p_customer_id;
1441 -- l_sales_lead_rec.address_id := p_address_id;
1442 l_sales_lead_rec.last_update_date := p_last_update_date;
1443 l_sales_lead_rec.assign_to_person_id := l_assign_to_person_id;
1444 l_sales_lead_rec.assign_to_salesforce_id := p_assign_to_salesforce_id;
1445 l_sales_lead_rec.assign_sales_group_id := l_assign_sales_group_id;
1446 -- l_sales_lead_rec.channel_code := l_channel_code;
1447 l_sales_lead_rec.close_reason := p_close_reason_code;
1448 -- l_sales_lead_rec.reject_reason_code := p_reject_reason_code;
1449 -- l_sales_lead_rec.budget_amount := p_budget_amount;
1450 l_sales_lead_rec.budget_status_code := p_budget_status_code;
1451 -- l_sales_lead_rec.currency_code := p_currency_code;
1452 l_sales_lead_rec.decision_timeframe_code := p_decision_timeframe_code;
1453 l_sales_lead_rec.description := p_description;
1454 -- l_sales_lead_rec.source_promotion_id := p_source_promotion_id;
1455 -- l_sales_lead_rec.offer_id := p_offer_id;
1456 -- l_sales_lead_rec.parent_project := p_parent_project;
1457 l_sales_lead_rec.lead_rank_id := p_lead_rank_id;
1458 l_sales_lead_rec.initiating_contact_id := p_initiating_contact_id; --
1459 -- l_sales_lead_rec.urgent_flag := p_urgent_flag;
1460 l_sales_lead_rec.accept_flag := p_accept_flag;
1461 l_sales_lead_rec.qualified_flag := p_qualified_flag;
1462 -- l_sales_lead_rec.vehicle_response_code := l_vehicle_response_code;
1463
1464 G_Access_Rec_Type.cust_access_profile_value := nvl(FND_PROFILE.VALUE('AS_CUST_ACCESS'),'F');
1465 G_Access_Rec_Type.lead_access_profile_value := nvl(FND_PROFILE.VALUE('AS_LEAD_ACCESS'),'F');
1466 G_Access_Rec_Type.opp_access_profile_value := nvl(FND_PROFILE.VALUE('AS_OPP_ACCESS'),'F');
1467 G_Access_Rec_Type.mgr_update_profile_value := nvl(FND_PROFILE.VALUE('AS_MGR_UPDATE'),'R');
1468 G_Access_Rec_Type.admin_update_profile_value := nvl(FND_PROFILE.VALUE('AS_ADMIN_UPDATE'),'R');
1469
1470 l_sales_lead_profile_tbl(1).profile_name := 'AS_CUST_ACCESS';
1471 l_sales_lead_profile_tbl(1).profile_value := G_access_rec_type.cust_access_profile_value;
1472 l_sales_lead_profile_tbl(2).profile_name := 'AS_LEAD_ACCESS';
1473 l_sales_lead_profile_tbl(2).profile_value := G_access_rec_type.lead_access_profile_value;
1474 l_sales_lead_profile_tbl(3).profile_name := 'AS_OPP_ACCESS';
1475 l_sales_lead_profile_tbl(3).profile_value := G_access_rec_type.opp_access_profile_value;
1476 l_sales_lead_profile_tbl(4).profile_name := 'AS_MGR_UPDATE';
1477 l_sales_lead_profile_tbl(4).profile_value := G_access_rec_type.mgr_update_profile_value;
1478 l_sales_lead_profile_tbl(5).profile_name := 'AS_ADMIN_UPDATE';
1479 l_sales_lead_profile_tbl(5).profile_value := G_access_rec_type.admin_update_profile_value;
1480
1481 AS_SALES_LEADS_PUB.update_sales_lead(
1482 P_API_VERSION_NUMBER => l_api_version
1483 ,P_INIT_MSG_LIST => l_init_msg_list
1484 ,P_COMMIT => l_commit
1485 ,P_VALIDATION_LEVEL => l_validation_level
1486 ,P_CHECK_ACCESS_FLAG => 'N'
1487 ,P_ADMIN_FLAG => l_admin_flag
1488 ,P_ADMIN_GROUP_ID => p_admin_group_id
1489 ,P_IDENTITY_SALESFORCE_ID => p_identity_salesforce_id
1490 ,P_SALES_LEAD_PROFILE_TBL => l_sales_lead_profile_tbl
1491 ,P_SALES_LEAD_REC => l_sales_lead_rec
1492 ,X_RETURN_STATUS => x_return_status
1493 ,X_MSG_COUNT => x_msg_count
1494 ,X_MSG_DATA => x_msg_data
1495 );
1496
1497 if x_return_status in ('E','U') THEN
1498 rollback;
1499 RETURN;
1500 END IF;
1501
1502 /** Adding this to fix bug 2918647. We should call Lead_Process_After_Update
1503 after update_sales_lead.
1504 **/
1505 AS_SALES_LEADS_PUB.Lead_Process_After_Update(
1506 P_Api_Version_Number => l_api_version
1507 ,P_Init_Msg_List => l_init_msg_list
1508 ,P_Commit => l_commit
1509 ,P_Validation_Level => l_validation_level
1510 ,P_check_access_flag => 'N'
1511 ,P_admin_flag => l_admin_flag
1512 ,P_Admin_Group_Id => p_admin_group_id
1513 ,P_identity_salesforce_id => p_identity_salesforce_id
1514 ,P_SalesGroup_Id => l_assign_sales_group_id
1515 ,P_Sales_Lead_Id => p_sales_lead_id
1516 ,X_Return_Status => x_return_status
1517 ,X_Msg_Count => x_msg_count
1518 ,X_Msg_Data => x_msg_data
1519 );
1520
1521 if x_return_status not in ('W','S') then
1522 rollback;
1523 return;
1524 end if;
1525
1526 exception
1527 when OTHERS THEN
1528 rollback;
1529 RAISE;
1530 END;
1531
1532 PROCEDURE create_opp_for_lead (
1533 p_admin_flag IN VARCHAR2,
1534 p_sales_lead_id IN NUMBER,
1535 p_resource_id IN NUMBER,
1536 p_salesgroup_id IN NUMBER,
1537 p_called_node IN VARCHAR2,
1538 x_app_launch OUT NOCOPY VARCHAR2,
1539 x_return_status OUT NOCOPY VARCHAR2,
1540 x_msg_count OUT NOCOPY NUMBER,
1541 x_msg_data OUT NOCOPY VARCHAR2,
1542 x_opportunity_id OUT NOCOPY NUMBER
1543 )
1544 as
1545 l_api_version NUMBER := 2.0;
1546 l_admin_flag VARCHAR2(1) := nvl(p_admin_flag, 'N');
1547 l_init_msg_list VARCHAR2(5) := 'T';
1548 l_commit VARCHAR2(5) := 'T';
1549 l_validation_level NUMBER := NVL(FND_PROFILE.VALUE('AST_SL_DEBUG_VALID_LEVEL'),0);
1550 l_opp_status VARCHAR2(30) := nvl(FND_PROFILE.value('AS_OPP_STATUS'), null);
1551 l_query_string varchar2(200);
1552 l_Sales_Lead_Profile_Tbl AS_UTILITY_PUB.Profile_Tbl_Type;
1553 G_Access_Rec_Type AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
1554
1555 l_count NUMBER;
1556 l_index NUMBER;
1557 my_message VARCHAR2(1000);
1558 BEGIN
1559 /*
1560 Check if there are any potential matching opportunities.
1561 If No, call create_opportunity_for_lead else
1562 launch ASTSLTOP
1563 */
1564 x_app_launch := 'N';
1565 l_query_string := null;
1566 get_potential_opportunity (p_sales_lead_id, l_admin_flag, null, p_resource_id, l_query_string);
1567
1568 if l_query_string is not null then
1569 x_return_status := 'S';
1570 x_app_launch := 'Y';
1571 return;
1572 else
1573 x_app_launch := 'N';
1574 end if;
1575
1576 G_Access_Rec_Type.cust_access_profile_value := nvl(FND_PROFILE.VALUE('AS_CUST_ACCESS'),'F');
1577 G_Access_Rec_Type.lead_access_profile_value := nvl(FND_PROFILE.VALUE('AS_LEAD_ACCESS'),'F');
1578 G_Access_Rec_Type.opp_access_profile_value := nvl(FND_PROFILE.VALUE('AS_OPP_ACCESS'),'F');
1579 G_Access_Rec_Type.mgr_update_profile_value := nvl(FND_PROFILE.VALUE('AS_MGR_UPDATE'),'R');
1580 G_Access_Rec_Type.admin_update_profile_value := nvl(FND_PROFILE.VALUE('AS_ADMIN_UPDATE'),'R');
1581
1582 l_sales_lead_profile_tbl(1).profile_name := 'AS_CUST_ACCESS';
1583 l_sales_lead_profile_tbl(1).profile_value := G_access_rec_type.cust_access_profile_value;
1584 l_sales_lead_profile_tbl(2).profile_name := 'AS_LEAD_ACCESS';
1585 l_sales_lead_profile_tbl(2).profile_value := G_access_rec_type.lead_access_profile_value;
1586 l_sales_lead_profile_tbl(3).profile_name := 'AS_OPP_ACCESS';
1587 l_sales_lead_profile_tbl(3).profile_value := G_access_rec_type.opp_access_profile_value;
1588 l_sales_lead_profile_tbl(4).profile_name := 'AS_MGR_UPDATE';
1589 l_sales_lead_profile_tbl(4).profile_value := G_access_rec_type.mgr_update_profile_value;
1590 l_sales_lead_profile_tbl(5).profile_name := 'AS_ADMIN_UPDATE';
1591 l_sales_lead_profile_tbl(5).profile_value := G_access_rec_type.admin_update_profile_value;
1592
1593 AS_SALES_LEADS_PUB.Create_Opportunity_For_Lead(
1594 p_api_version_NUMBER => l_api_version
1595 ,p_init_msg_list => l_init_msg_list
1596 ,p_commit => l_commit
1597 ,p_validation_level => l_validation_level
1598 ,P_Check_Access_Flag => 'Y'
1599 ,P_Admin_Flag => l_admin_flag
1600 ,P_Admin_Group_Id => null
1601 ,P_identity_salesforce_id => p_resource_id
1602 ,P_identity_salesgroup_id => p_salesgroup_id
1603 ,P_Sales_Lead_Profile_Tbl => l_Sales_Lead_Profile_Tbl
1604 ,P_SALES_LEAD_ID => p_sales_lead_id
1605 ,P_Opp_Status => l_opp_status
1606 ,x_return_status => x_return_status
1607 ,x_msg_count => x_msg_count
1608 ,x_msg_data => x_msg_data
1609 ,x_opportunity_id => x_opportunity_id
1610 );
1611
1612 if x_return_status in ('E','U') THEN
1613 rollback;
1614 RETURN;
1615 END IF;
1616
1617 exception
1618 when OTHERS THEN
1619 rollback;
1620 RAISE;
1621 end create_opp_for_lead;
1622
1623 PROCEDURE reassign_lead (
1624 p_admin_flag IN VARCHAR2,
1625 p_admin_group_id IN NUMBER,
1626 p_default_group_id IN NUMBER,
1627 p_person_id IN NUMBER, --global.ast_person_id
1628 p_resource_id IN NUMBER,
1629 p_sales_lead_id IN NUMBER,
1630 p_new_salesforce_id IN NUMBER,
1631 p_last_update_date IN DATE,
1632 p_new_sales_group_id IN NUMBER,
1633 p_new_owner_id IN NUMBER, --person_id of new owner
1634 p_called_node IN VARCHAR2,
1635 x_return_status OUT NOCOPY VARCHAR2,
1636 x_msg_count OUT NOCOPY NUMBER,
1637 x_msg_data OUT NOCOPY VARCHAR2
1638 )
1639 as
1640 l_old_access_id NUMBER; --remove
1641 l_api_version NUMBER := 2.0;
1642 l_access_flag VARCHAR2(1);
1643 l_admin_flag VARCHAR2(1) := nvl(p_admin_flag, 'N');
1644 l_init_msg_list VARCHAR2(5) := 'T';
1645 l_commit VARCHAR2(5) := 'T';
1646 l_message_name VARCHAR2(100);
1647 l_validation_level CONSTANT NUMBER := NVL(FND_PROFILE.VALUE('AST_SL_DEBUG_VALID_LEVEL'),0);
1648 G_Access_Rec_Type AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
1649
1650 l_count NUMBER;
1651 l_index NUMBER;
1652 my_message VARCHAR2(1000);
1653 BEGIN
1654 /** Check if the user has access to change owner **/
1655 /**
1656 Commenting out the validation part as this is being done in the calling procedure.
1657 Procedure LLIST_REASSIGN_LEAD in astulacb.pls
1658 **/
1659 /**
1660 l_message_name := 'AST_NO_LEAD_OWNR_CHANGE_ACCESS';
1661 l_access_flag := nvl(fnd_profile.value('AS_ALLOW_CHANGE_LEAD_OWNER'),'N');
1662
1663 if (l_access_flag <> 'Y') then
1664 if (p_sales_lead_id is not null) then
1665 G_Access_Rec_Type.cust_access_profile_value := nvl(FND_PROFILE.VALUE('AS_CUST_ACCESS'),'F');
1666 G_Access_Rec_Type.lead_access_profile_value := nvl(FND_PROFILE.VALUE('AS_LEAD_ACCESS'),'F');
1667 G_Access_Rec_Type.opp_access_profile_value := nvl(FND_PROFILE.VALUE('AS_OPP_ACCESS'),'F');
1668 G_Access_Rec_Type.mgr_update_profile_value := nvl(FND_PROFILE.VALUE('AS_MGR_UPDATE'),'R');
1669 G_Access_Rec_Type.admin_update_profile_value := nvl(FND_PROFILE.VALUE('AS_ADMIN_UPDATE'),'R');
1670
1671 AS_ACCESS_PVT.has_LeadOwnerAccess(
1672 p_api_version_Number => 2.0,
1673 p_init_msg_list => 'N',
1674 p_validation_level => 100,
1675 p_access_profile_rec => G_Access_Rec_Type,
1676 p_admin_flag => p_admin_flag,
1677 p_admin_group_id => p_admin_group_id,
1678 p_person_id => p_person_id,
1679 p_sales_lead_id => p_sales_lead_Id,
1680 p_check_access_flag => 'Y',
1681 p_identity_salesforce_id => p_resource_id,
1682 p_partner_cont_party_id => NULL,
1683 x_return_status => x_return_status,
1684 x_msg_count => x_msg_count,
1685 x_msg_data => x_msg_data,
1686 x_update_access_flag => l_access_flag
1687 );
1688 end if;
1689
1690 if (l_access_flag <> 'Y') then
1691 x_return_status := 'E';
1692 FND_MSG_PUB.INITIALIZE;
1693 FND_MESSAGE.Set_Name('AST',l_message_name);
1694 FND_MSG_PUB.ADD;
1695 return;
1696 end if;
1697 end if;
1698 **/
1699
1700
1701 /**
1702 Update the sales lead by assigning it to new owner. Then call
1703 Rebuild_Lead_Sales_Team to insert a row into as_accesses_all if needed.
1704 **/
1705 ast_uwq_wrapper_pkg.update_lead(
1706 p_sales_lead_id => p_sales_lead_id,
1707 p_admin_group_id => p_admin_group_id,
1708 p_identity_salesforce_id => p_resource_id,
1709 p_last_update_date => p_last_update_date,
1710 p_assign_to_salesforce_id => p_new_salesforce_id,
1711 p_admin_flag => l_admin_flag,
1712 p_assign_sales_group_id => p_new_sales_group_id,
1713 x_return_status => x_return_status,
1714 x_msg_count => x_msg_count,
1715 x_msg_data => x_msg_data
1716 );
1717
1718 IF x_return_status <> 'S' THEN
1719 rollback;
1720 return;
1721 END IF;
1722 END reassign_lead;
1723
1724
1725 PROCEDURE get_potential_opportunity (
1726 p_sales_lead_id IN NUMBER,
1727 p_admin_flag IN VARCHAR2,
1728 p_admin_group_id IN NUMBER,
1729 p_resource_id IN NUMBER,
1730 x_query_string OUT NOCOPY varchar2
1731 )
1732 AS
1733 l_api_version NUMBER := 2.0;
1734 l_msg_count NUMBER;
1735 l_opp_id NUMBER;
1736 l_index NUMBER;
1737 l_admin_id NUMBER;
1738 l_sales_lead_id NUMBER;
1739 l_admin_flag VARCHAR2(1) := nvl(p_admin_flag, 'N');
1740 l_init_msg_list VARCHAR2(5) := 'T';
1741 l_commit VARCHAR2(10) := 'F';
1742 l_return_status VARCHAR2(10);
1743 l_msg_data VARCHAR2(2000);
1744 l_Sales_Lead_Profile_Tbl AS_UTILITY_PUB.Profile_Tbl_Type;
1745 l_sales_lead_rec AS_SALES_LEADS_PUB.SALES_LEAD_rec_type := AS_API_RECORDS_PKG.Get_P_Sales_Lead_Rec;
1746 l_header_tbl AS_OPPORTUNITY_PUB.HEADER_TBL_TYPE := AS_API_RECORDS_PKG.Get_P_Header_Tbl;
1747 l_purchase_tbl AS_OPPORTUNITY_PUB.LINE_TBL_TYPE := AS_API_RECORDS_PKG.Get_P_Line_Tbl;
1748
1749 l_validation_level CONSTANT NUMBER := NVL(FND_PROFILE.VALUE('AST_SL_DEBUG_VALID_LEVEL'),0);
1750 G_Access_Rec_Type AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
1751
1752 CURSOR c_lead_info (p_sales_lead_id NUMBER) is
1753 SELECT customer_id, source_promotion_id
1754 FROM as_sales_leads
1755 WHERE sales_lead_id = p_sales_lead_id;
1756
1757 BEGIN
1758 l_sales_lead_id := p_sales_lead_id;
1759
1760 if l_admin_flag = 'Y' then
1761 l_admin_id := p_admin_group_id;
1762 else
1763 l_admin_id := null;
1764 end if;
1765
1766 open c_lead_info(l_sales_lead_id);
1767 fetch c_lead_info into l_sales_lead_rec.customer_id, l_sales_lead_rec.source_promotion_id;
1768 close c_lead_info;
1769
1770 l_sales_lead_profile_tbl(1).profile_name := 'AS_CUST_ACCESS';
1771 l_sales_lead_profile_tbl(1).profile_value := G_Access_Rec_Type.cust_access_profile_value;
1772 l_sales_lead_profile_tbl(2).profile_name := 'AS_LEAD_ACCESS';
1773 l_sales_lead_profile_tbl(2).profile_value := G_Access_Rec_Type.lead_access_profile_value;
1774 l_sales_lead_profile_tbl(3).profile_name := 'AS_OPP_ACCESS';
1775 l_sales_lead_profile_tbl(3).profile_value := G_Access_Rec_Type.opp_access_profile_value;
1776 l_sales_lead_profile_tbl(4).profile_name := 'AS_MGR_UPDATE';
1777 l_sales_lead_profile_tbl(4).profile_value := G_Access_Rec_Type.mgr_update_profile_value;
1778 l_sales_lead_profile_tbl(5).profile_name := 'AS_ADMIN_UPDATE';
1779 l_sales_lead_profile_tbl(5).profile_value := G_Access_Rec_Type.admin_update_profile_value;
1780
1781 /** 11.5.7 should use AS_SALES_LEADS_PUB.Get_Potential_Opportunity **/
1782
1783 AS_LINK_LEAD_OPP_PUB.Get_Potential_Opportunity(
1784 p_api_version_NUMBER => l_api_version
1785 ,p_init_msg_list => l_init_msg_list
1786 ,p_commit => l_commit
1787 ,p_validation_level => l_validation_level
1788 ,P_Check_Access_Flag => 'Y'
1789 ,P_Admin_Flag => l_admin_flag
1790 ,P_Admin_Group_Id => l_admin_id
1791 ,P_identity_salesforce_id => p_resource_id
1792 ,P_Sales_Lead_Profile_Tbl => l_Sales_Lead_Profile_Tbl
1793 ,P_SALES_LEAD_rec => l_sales_lead_rec
1794 ,x_return_status => l_return_status
1795 ,x_msg_count => l_msg_count
1796 ,x_msg_data => l_msg_data
1797 ,x_opportunity_tbl => l_header_tbl
1798 ,x_opp_lines_tbl => l_purchase_tbl );
1799
1800 IF l_return_status = 'S' THEN
1801 IF l_header_tbl.count > 0 THEN -- Matching Opportunity exists
1802 x_query_string := 'Matching Opportunity exists';
1803 ELSE
1804 x_query_string := null;
1805 END IF;
1806 ELSE
1807 x_query_string := null;
1808 END IF;
1809 END Get_Potential_Opportunity;
1810
1811 PROCEDURE update_opportunity (
1812 p_admin_flag IN VARCHAR2,
1813 p_admin_group_id IN NUMBER,
1814 p_resource_id IN NUMBER,
1815 p_last_update_date IN DATE,
1816 p_lead_id IN NUMBER,
1817 p_lead_number IN VARCHAR2,
1818 p_description IN VARCHAR2,
1819 p_status_code IN VARCHAR2,
1820 p_close_reason_code IN VARCHAR2,
1821 p_source_promotion_id IN NUMBER,
1822 p_customer_id IN NUMBER,
1823 p_contact_party_id IN NUMBER,
1824 p_address_id IN NUMBER,
1825 p_sales_stage_id IN NUMBER,
1826 p_win_probability IN NUMBER,
1827 p_total_amount IN NUMBER,
1828 p_total_revenue_forecast_amt IN NUMBER, --added for R12
1829 p_channel_code IN VARCHAR2,
1830 p_decision_date IN DATE,
1831 p_currency_code IN VARCHAR2,
1832 p_vehicle_response_code IN VARCHAR2,
1833 p_customer_budget IN NUMBER,
1834 --Code commented for R12 Enhancement --Start
1835 /* p_close_competitor_code IN VARCHAR2,
1836 p_close_competitor_id IN NUMBER,
1837 p_close_competitor IN VARCHAR2, */
1838 --Code commented for R12 Enhancement --End
1839
1840 p_close_comment IN VARCHAR2,
1841 p_parent_project IN VARCHAR2,
1842 p_freeze_flag IN VARCHAR2,
1843 p_called_node IN VARCHAR2,
1844 x_return_status OUT NOCOPY VARCHAR2,
1845 x_msg_count OUT NOCOPY NUMBER,
1846 x_msg_data OUT NOCOPY VARCHAR2,
1847 x_lead_id OUT NOCOPY NUMBER
1848 )
1849 AS
1850 l_api_version NUMBER := 2.0;
1851 l_valid_level_full NUMBER := 100;
1852 l_address_profile VARCHAR2(3) := FND_PROFILE.value('AST_WP_USE_ADDRESS_FOR_OPP');
1853 l_admin_flag VARCHAR2(1) := nvl(p_admin_flag, 'N');
1854 l_init_msg_list VARCHAR2(5) := 'T';
1855 l_commit VARCHAR2(5) := 'T';
1856 header_rec AS_OPPORTUNITY_PUB.Header_Rec_Type := AS_API_RECORDS_PKG.get_p_header_rec;
1857 v_profile_tbl AS_UTILITY_PUB.PROFILE_TBL_TYPE := AS_API_RECORDS_PKG.get_p_profile_tbl;
1858
1859 l_count NUMBER;
1860 l_index NUMBER;
1861 my_message VARCHAR2(1000);
1862
1863 l_commit_BE VARCHAR2(5) := 'F';
1864 x_event_key VARCHAR2(240);
1865
1866 BEGIN
1867
1868
1869 Header_Rec_Set(
1870 p_last_update_date,
1871 p_lead_id ,
1872 p_lead_number,
1873 p_description,
1874 p_status_code,
1875 p_source_promotion_id,
1876 p_customer_id,
1877 p_address_id,
1878 p_sales_stage_id,
1879 p_win_probability,
1880 p_total_amount,
1881 p_total_revenue_forecast_amt, --added for R12
1882 p_channel_code,
1883 p_decision_date,
1884 p_currency_code,
1885 p_vehicle_response_code,
1886 p_customer_budget,
1887 --Code commented for R12 Enhancement --Start
1888 /* p_close_competitor_code,
1889 p_close_competitor_id,
1890 p_close_competitor , */
1891 --Code commented for R12 Enhancement --End
1892 p_close_comment,
1893 p_parent_project,
1894 p_freeze_flag ,
1895 header_rec
1896 );
1897
1898 header_rec.close_reason := p_close_reason_code;
1899
1900 -- the following call added for Buiness event by subabu(bug#3499750)
1901 AS_BUSINESS_EVENT_PUB.before_Oppty_update(
1902 p_api_version_number => l_api_version,
1903 p_init_msg_list=>l_init_msg_list,
1904 p_commit=>l_commit_BE,
1905 p_validation_level=>l_valid_level_full,
1906 p_lead_id=>p_lead_id,
1907 x_return_status=>x_return_status,
1908 x_msg_count=>x_msg_count,
1909 x_msg_data=>x_msg_data,
1910 x_event_key=>x_event_key);
1911 AS_OPPORTUNITY_PUB.Update_Opp_Header
1912 (
1913 p_api_version_number => l_api_version,
1914 p_init_msg_list => l_init_msg_list,
1915 p_commit => l_commit,
1916 p_validation_level => l_valid_level_full,
1917 p_header_rec => header_rec,
1918 p_check_access_flag => 'N',
1919 p_admin_flag => l_admin_flag,
1920 p_admin_group_id => p_admin_group_id,
1921 p_identity_salesforce_id => p_resource_id,
1922 p_profile_tbl => v_profile_tbl,
1923 p_partner_cont_party_id => null,
1924 x_return_status => x_return_status,
1925 x_msg_count => x_msg_count,
1926 x_msg_data => x_msg_data,
1927 x_lead_id => x_lead_id
1928 );
1929
1930 IF x_return_status IN ('E','U') THEN
1931 ROLLBACK;
1932 RETURN;
1933 END IF;
1934 -- the following call added for Buiness event by subabu(bug#3499750)
1935 if x_event_key is not null then /* Added for Bug#3522912 */
1936 AS_BUSINESS_EVENT_PUB.Update_oppty_post_event(
1937 p_api_version_number => l_api_version,
1938 p_init_msg_list=>l_init_msg_list,
1939 p_commit=>l_commit,
1940 p_validation_level=>l_valid_level_full,
1941 p_lead_id=>p_lead_id,
1942 p_event_key=>x_event_key,
1943 x_return_status=>x_return_status,
1944 x_msg_count=>x_msg_count,
1945 x_msg_data=>x_msg_data);
1946 end if;
1947
1948 /**
1949 if x_return_status in ('E','U') then
1950 l_count := FND_MSG_PUB.Count_Msg;
1951 dbms_output.put_line('Update Opp: There are ' || l_count || ' messages.');
1952 FOR l_index IN 1..l_count LOOP
1953 my_message := FND_MSG_PUB.Get(
1954 p_msg_index => l_index,
1955 p_encoded => FND_API.G_FALSE);
1956 dbms_output.put_line(substr(my_message,1,255));
1957 END LOOP;
1958
1959 rollback;
1960 return;
1961 end if;
1962 **/
1963 EXCEPTION
1964 when OTHERS THEN
1965 ROLLBACK;
1966 RAISE;
1967 END update_opportunity;
1968
1969 END ast_uwq_wrapper_pkg;