[Home] [Help]
PACKAGE BODY: APPS.AS_SALES_LEAD_OPP_PVT
Source
1 PACKAGE BODY AS_SALES_LEAD_OPP_PVT as
2 /* $Header: asxvslob.pls 120.4 2006/04/20 02:56:34 subabu ship $ */
3 -- Start of Comments
4 -- Package name : AS_SALES_LEAD_OPP_PVT
5 -- Purpose : Sales Lead and Opportunity
6 -- NOTE :
7 -- History :
8 -- 04/09/2001 FFANG Created.
9 --
10 -- END of Comments
11
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_SALES_LEAD_OPP_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxvslob.pls';
15
16
17
18 PROCEDURE Create_Lead_Ctx(
19 p_sales_lead_id IN NUMBER,
20 p_opportunity_id IN NUMBER,
21 x_return_status OUT NOCOPY VARCHAR2
22 );
23
24
25 -- API Name: Get_Potential_Opportunity
26
27 PROCEDURE Get_Potential_Opportunity(
28 P_Api_Version_Number IN NUMBER,
29 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
30 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
31 P_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
32 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
33 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
34 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
35 P_identity_salesforce_id IN NUMBER := NULL,
36 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
37 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
38 P_SALES_LEAD_rec IN AS_SALES_LEADS_PUB.SALES_LEAD_rec_type,
39 X_Return_Status OUT NOCOPY VARCHAR2,
40 X_Msg_Count OUT NOCOPY NUMBER,
41 X_Msg_Data OUT NOCOPY VARCHAR2,
42 X_OPPORTUNITY_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.HEADER_TBL_TYPE,
43 X_OPP_LINES_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.LINE_TBL_TYPE
44 )
45 IS
46 -- Bug 1558460
47 -- solin, use customer_id to find opportunity. source_promotion_id is not
48 -- needed any more.
49 -- ffang 012501, PM wants all opportunities with same customer_id, don't
50 -- need access checking on this.
51
52 --Modified By Francis on 08/22/2001 for bug#1950681 to replace the view.
53
54 CURSOR C_Get_Opportunity (x_Customer_ID NUMBER) IS
55 SELECT
56
57 opp.last_update_date
58 ,opp.last_updated_by
59 ,opp.creation_Date
60 ,opp.created_by
61 ,opp.last_update_login
62 ,opp.request_id
63 ,opp.program_application_id
64 ,opp.program_id
65 ,opp.program_update_date
66 ,opp.lead_id
67 ,opp.lead_number
68 ,opp.orig_system_reference
69 ,opp.lead_source_code
70 -- ,opp.lead_source
71 ,opp.description
72 ,opp.source_promotion_id
73 ,AMS1.NAME source_promotion_code
74 ,opp.customer_id
75 ,PARTY.PARTY_NAME CUSTOMER_NAME
76 ,NVL(ORGANIZATION_NAME_PHONETIC, decode(PERSON_LAST_NAME_PHONETIC,Null,Null,PERSON_LAST_NAME_PHONETIC||', ')||PERSON_FIRST_NAME_PHONETIC) CUSTOMER_NAME_PHONETIC
77 ,opp.address_id
78 -- ,opp.address1 address
79 -- ,opp.address2
80 -- ,opp.address3
81 -- ,opp.address4
82 -- ,opp.city
83 -- ,opp.state
84 -- ,opp.country
85 -- ,opp.province
86 ,opp.sales_stage_id
87 ,STGTL.NAME SALES_STAGE
88 ,opp.win_probability
89 ,opp.status STATUS_CODE
90 ,ASSTATUSES.MEANING status
91 ,opp.total_amount
92 -- ,opp.converted_total_amount
93 ,opp.channel_code
94 ,ASOCHANNELS.MEANING CHANNEL
95 ,opp.decision_date
96 ,opp.currency_code
97 -- ,opp.to_currency_code
98 ,opp.close_reason CLOSE_REASON_CODE
99 -- ,opp.close_reason
100 -- ,opp.close_competitor_code
101 ,opp.close_competitor_id
102 ,opp.close_competitor
103 ,opp.close_comment
104 ,opp.end_user_customer_id
105 ,opp.end_user_customer_name
106 ,opp.end_user_address_id
107 ,opp.parent_project
108 -- ,opp.parent_project_code
109 -- ,opp.updateable_flag
110 ,opp.price_list_id
111 -- ,opp.initiating_contact_id
112 -- ,opp.rank
113 -- ,opp.member_access
114 -- ,opp.member_role
115 -- ,opp.deleted_flag
116 -- ,opp.auto_assignment_type
117 -- ,opp.prm_assignment_type
118 -- ,opp.customer_budget
119 ,opp.methodology_code
120 -- ,opp.original_lead_id
121 -- ,opp.decision_timeframe_code
122 -- ,opp.incumbent_partner_resource_id
123 -- ,opp.incumbent_partner_party_id
124 ,opp.offer_id
125 -- ,opp.vehicle_response_code
126 -- ,opp.budget_status_code
127 -- ,opp.followup_date
128 ,opp.no_opp_allowed_flag
129 ,opp.delete_allowed_flag
130 -- ,opp.prm_exec_sponsor_flag
131 -- ,opp.prm_prj_lead_in_place_flag
132 -- ,opp.prm_ind_classIFication_code
133 -- ,opp.prm_lead_type
134 -- ,opp.org_id
135 ,opp.attribute_category
136 ,opp.attribute1
137 ,opp.attribute2
138 ,opp.attribute3
139 ,opp.attribute4
140 ,opp.attribute5
141 ,opp.attribute6
142 ,opp.attribute7
143 ,opp.attribute8
144 ,opp.attribute9
145 ,opp.attribute10
146 ,opp.attribute11
147 ,opp.attribute12
148 ,opp.attribute13
149 ,opp.attribute14
150 ,opp.attribute15
151
152
153 FROM
154 AS_LEADS_ALL OPP,
155 HZ_PARTIES PARTY,
156 AMS_P_SOURCE_CODES_V AMS1,
157 OE_LOOKUPS ASOCHANNELS,
158 AS_SALES_STAGES_ALL_B STGB,
159 AS_SALES_STAGES_ALL_TL STGTL,
160 AS_STATUSES_TL ASSTATUSES,
161 AS_STATUSES_B ASSTB
162
163 WHERE
164 OPP.CUSTOMER_ID = x_Customer_Id AND
165 OPP.CUSTOMER_ID = PARTY.PARTY_ID AND
166 OPP.SOURCE_PROMOTION_ID = AMS1.SOURCE_CODE_ID(+) AND
167 OPP.CHANNEL_CODE = ASOCHANNELS.LOOKUP_CODE(+) AND
168 ASOCHANNELS.LOOKUP_TYPE(+) = 'SALES_CHANNEL' AND
169 OPP.SALES_STAGE_ID = STGB.SALES_STAGE_ID(+) AND
170 STGB.SALES_STAGE_ID = STGTL.SALES_STAGE_ID(+) AND
171 STGTL.LANGUAGE(+) = USERENV('LANG') AND
172 OPP.STATUS = ASSTB.STATUS_CODE AND
173 ASSTB.OPP_OPEN_STATUS_FLAG = 'Y' AND
174 ASSTB.STATUS_CODE = ASSTATUSES.STATUS_CODE AND
175 ASSTATUSES.LANGUAGE = USERENV('LANG') ;
176
177
178
179 CURSOR C_Get_Opp_Line (x_Lead_ID NUMBER) IS
180 SELECT
181 last_update_date
182 ,last_updated_by
183 ,creation_Date
184 ,created_by
185 ,last_update_login
186 ,request_id
187 ,program_application_id
188 ,program_id
189 ,program_update_date
190 ,lead_id
191 ,lead_line_id
192 ,original_lead_line_id
193 ,interest_type_id
194 -- ,interest_type
195 -- ,interest_status_code
196 ,primary_interest_code_id
197 -- ,primary_interest_code
198 ,secondary_interest_code_id
199 -- ,secondary_interest_code
200 ,inventory_item_id
201 -- ,inventory_item_conc_segs
202 ,organization_id
203 ,uom_code
204 -- ,uom
205 ,quantity
206 ,ship_date
207 ,total_amount
208 -- ,sales_stage_id
209 -- ,sales_stage
210 -- ,win_probability
211 -- ,status_code
212 -- ,status
213 -- ,decision_date
214 -- ,channel_code
215 -- ,channel
216 -- ,unit_price
217 -- ,price
218 -- ,price_volume_margin
219 -- ,quoted_line_flag
220 -- ,member_access
221 -- ,member_role
222 -- ,currency_code
223 -- ,owner_scredit_percent
224 -- ,source_promotion_id
225 -- ,offer_id
226 -- ,org_id
227 ,attribute_category
228 ,attribute1
229 ,attribute2
230 ,attribute3
231 ,attribute4
232 ,attribute5
233 ,attribute6
234 ,attribute7
235 ,attribute8
236 ,attribute9
237 ,attribute10
238 ,attribute11
239 ,attribute12
240 ,attribute13
241 ,attribute14
242 ,attribute15
243 ,product_category_id
244 ,product_cat_set_id
245 From AS_LEAD_LINES_ALL
246 Where lead_id = x_Lead_Id;
247
248 l_api_name CONSTANT VARCHAR2(30) := 'Get_Potential_Opportunity';
249 l_api_version_number CONSTANT NUMBER := 2.0;
250 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
251 l_header_rec AS_OPPORTUNITY_PUB.HEADER_REC_TYPE;
252 l_line_rec AS_OPPORTUNITY_PUB.LINE_REC_TYPE;
253 l_SALES_LEAD_rec AS_SALES_LEADS_PUB.SALES_LEAD_REC_type;
254 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
255 l_cnt NUMBER := 0;
256 l_cnt_line NUMBER := 0;
257 l_validation_mode VARCHAR2(30) := AS_UTILITY_PVT.G_CREATE;
258 l_update_access_flag VARCHAR2(1);
259 l_member_role VARCHAR2(5);
260 l_member_access VARCHAR2(5);
261 l_debug BOOLEAN;
262 l_module CONSTANT VARCHAR2(255) := 'as.plsql.slopv.Get_Potential_Opportunity';
263
264 BEGIN
265 -- Standard Start of API savepoint
266 SAVEPOINT GET_POTENTIAL_OPPORTUNITY_PVT;
267
268 -- Standard call to check for call compatibility.
269 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
270 p_api_version_number,
271 l_api_name,
272 G_PKG_NAME)
273 THEN
274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
275 END IF;
276 l_debug := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
277 -- Initialize message list IF p_init_msg_list is set to TRUE.
278 IF FND_API.to_Boolean( p_init_msg_list )
279 THEN
280 FND_MSG_PUB.initialize;
281 END IF;
282
283 -- Debug Message
284
285 IF l_debug THEN
286 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'PVT:' || l_api_name || 'start');
287 END IF;
288
289 -- Initialize API return status to SUCCESS
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291
292 --
293 -- Api body
294 --
295 -- ******************************************************************
296 -- Validate Environment
297 -- ******************************************************************
298 IF FND_GLOBAL.User_Id IS NULL
299 THEN
300 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
301 THEN
302 AS_UTILITY_PVT.Set_Message(
303 p_module => l_module,
304 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
305 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
306 p_token1 => 'PROFILE',
307 p_token1_value => 'USER_ID');
308 END IF;
309
310 RAISE FND_API.G_EXC_ERROR;
311 END IF;
312
313 IF (p_validation_level = fnd_api.g_valid_level_full)
314 THEN
315 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
316 p_api_version_number => 2.0
317 ,p_init_msg_list => p_init_msg_list
318 ,p_salesforce_id => P_Identity_Salesforce_Id
319 ,p_admin_group_id => p_admin_group_id
320 ,x_return_status => x_return_status
321 ,x_msg_count => x_msg_count
322 ,x_msg_data => x_msg_data
323 ,x_sales_member_rec => l_identity_sales_member_rec);
324 END IF;
325
326 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
327 RAISE FND_API.G_EXC_ERROR;
328 END IF;
329
330 For C_Get_Opp_Rec IN C_Get_Opportunity (P_SALES_LEAD_rec.Customer_Id)
331 Loop
332 l_cnt := l_cnt + 1;
333 l_header_rec.last_update_date := C_Get_Opp_Rec.last_update_date;
334 l_header_rec.last_updated_by := C_Get_Opp_Rec.last_updated_by;
335 l_header_rec.creation_Date := C_Get_Opp_Rec.creation_Date;
336 l_header_rec.created_by := C_Get_Opp_Rec.created_by;
337 l_header_rec.last_update_login := C_Get_Opp_Rec.last_update_login;
338 l_header_rec.lead_id := C_Get_Opp_Rec.lead_id;
339 l_header_rec.lead_number := C_Get_Opp_Rec.lead_number;
340 l_header_rec.orig_system_reference
341 := C_Get_Opp_Rec.orig_system_reference;
342 l_header_rec.lead_source_code := C_Get_Opp_Rec.lead_source_code;
343 -- l_header_rec.lead_source := C_Get_Opp_Rec.lead_source;
344 l_header_rec.description := C_Get_Opp_Rec.description;
345 l_header_rec.source_promotion_id
346 := C_Get_Opp_Rec.source_promotion_id;
347 l_header_rec.customer_id := C_Get_Opp_Rec.customer_id;
348 l_header_rec.customer_name := C_Get_Opp_Rec.customer_name;
349 l_header_rec.address_id := C_Get_Opp_Rec.address_id;
350 -- l_header_rec.city := C_Get_Opp_Rec.city;
351 l_header_rec.sales_stage := C_Get_Opp_Rec.sales_stage;
352 l_header_rec.sales_stage_id := C_Get_Opp_Rec.sales_stage_id;
353 l_header_rec.win_probability := C_Get_Opp_Rec.win_probability;
354 l_header_rec.status_code := C_Get_Opp_Rec.status_code;
355
356 -- ffang 092302, for bug 2567661, status in opportunity header record
357 -- has wrong definition (varchar2(80), it should be varchar2(240)),
358 -- comment this out to avoid exception.
359 -- l_header_rec.status := C_Get_Opp_Rec.status;
360 -- end ffang 092302, for bug 2567661
361
362 -- l_header_rec.initiating_contact_id
363 -- := C_Get_Opp_Rec.initiating_contact_id;
364 -- l_header_rec.rank := C_Get_Opp_Rec.rank;
365 l_header_rec.channel_code := C_Get_Opp_Rec.channel_code;
366 l_header_rec.channel := C_Get_Opp_Rec.channel;
367 l_header_rec.decision_date := C_Get_Opp_Rec.decision_date;
368 l_header_rec.currency_code := C_Get_Opp_Rec.currency_code;
369 l_header_rec.price_list_id := C_Get_Opp_Rec.price_list_id;
370 l_header_rec.close_reason_code := C_Get_Opp_Rec.close_reason_code;
371 -- l_header_rec.close_reason := C_Get_Opp_Rec.close_reason;
372 -- l_header_rec.close_competitor_code
373 -- := C_Get_Opp_Rec.close_competitor_code;
374 l_header_rec.close_competitor_id
375 := C_Get_Opp_Rec.close_competitor_id;
376 l_header_rec.close_competitor := C_Get_Opp_Rec.close_competitor;
377 l_header_rec.close_comment := C_Get_Opp_Rec.close_comment;
378 l_header_rec.end_user_customer_id
379 := C_Get_Opp_Rec.end_user_customer_id;
380 l_header_rec.end_user_customer_name
381 := C_Get_Opp_Rec.end_user_customer_name;
382 l_header_rec.end_user_address_id
383 := C_Get_Opp_Rec.end_user_address_id;
384 l_header_rec.total_amount := C_Get_Opp_Rec.total_amount;
385 l_header_rec.attribute_category
386 := C_Get_Opp_Rec.attribute_category;
387 l_header_rec.attribute1 := C_Get_Opp_Rec.attribute1;
388 l_header_rec.attribute2 := C_Get_Opp_Rec.attribute2;
389 l_header_rec.attribute3 := C_Get_Opp_Rec.attribute3;
390 l_header_rec.attribute4 := C_Get_Opp_Rec.attribute4;
391 l_header_rec.attribute5 := C_Get_Opp_Rec.attribute5;
392 l_header_rec.attribute6 := C_Get_Opp_Rec.attribute6;
393 l_header_rec.attribute7 := C_Get_Opp_Rec.attribute7;
394 l_header_rec.attribute8 := C_Get_Opp_Rec.attribute8;
395 l_header_rec.attribute9 := C_Get_Opp_Rec.attribute9;
396 l_header_rec.attribute10 := C_Get_Opp_Rec.attribute10;
397 l_header_rec.attribute11 := C_Get_Opp_Rec.attribute11;
398 l_header_rec.attribute12 := C_Get_Opp_Rec.attribute12;
399 l_header_rec.attribute13 := C_Get_Opp_Rec.attribute13;
400 l_header_rec.attribute14 := C_Get_Opp_Rec.attribute14;
401 l_header_rec.attribute15 := C_Get_Opp_Rec.attribute15;
402 l_header_rec.parent_project := C_Get_Opp_Rec.parent_project;
403 l_header_rec.updateable_flag := 'N';
404 -- l_header_rec.member_access := FND_API.G_MISS_CHAR;
405 -- l_header_rec.member_role := FND_API.G_MISS_CHAR;
406
407 X_Opportunity_tbl(l_cnt) := l_header_rec;
408
409
410 -- Move Opportunity Lines to X_OPP_LINES_TBL parameter
411 For C_Get_Opp_Line_Rec In C_Get_Opp_Line (l_header_Rec.lead_id)
412 Loop
413 l_cnt_line := l_cnt_line + 1;
414 l_line_rec.last_update_date
415 := C_Get_Opp_Line_Rec.last_update_date;
416 l_line_rec.last_updated_by := C_Get_Opp_Line_Rec.last_updated_by;
417 l_line_rec.creation_Date := C_Get_Opp_Line_Rec.creation_Date;
418 l_line_rec.created_by := C_Get_Opp_Line_Rec.created_by;
419 l_line_rec.last_update_login
420 := C_Get_Opp_Line_Rec.last_update_login;
421 l_line_rec.lead_line_id := C_Get_Opp_Line_Rec.lead_line_id;
422 l_line_rec.interest_type_id
423 := C_Get_Opp_Line_Rec.interest_type_id;
424 -- l_line_rec.interest_type:= C_Get_Opp_Line_Rec.interest_type;
425 l_line_rec.primary_interest_code_id
426 := C_Get_Opp_Line_Rec.primary_interest_code_id;
427 -- l_line_rec.primary_interest_code
428 -- := C_Get_Opp_Line_Rec.primary_interest_code;
429 l_line_rec.secondary_interest_code_id
430 := C_Get_Opp_Line_Rec.secondary_interest_code_id;
431 -- l_line_rec.secondary_interest_code
432 -- := C_Get_Opp_Line_Rec.secondary_interest_code;
433 l_line_rec.inventory_item_id
434 := C_Get_Opp_Line_Rec.inventory_item_id;
435 -- l_line_rec.inventory_item_conc_segs
436 -- := C_Get_Opp_Line_Rec.inventory_item_conc_segs;
437 l_line_rec.organization_id := C_Get_Opp_Line_Rec.organization_id;
438 l_line_rec.uom_code := C_Get_Opp_Line_Rec.uom_code;
439 -- l_line_rec.uom := C_Get_Opp_Line_Rec.uom;
440 l_line_rec.quantity := C_Get_Opp_Line_Rec.quantity;
441 l_line_rec.ship_date := C_Get_Opp_Line_Rec.ship_date;
442 l_line_rec.total_amount := C_Get_Opp_Line_Rec.total_amount;
443 -- l_line_rec.sales_stage_id:= C_Get_Opp_Line_Rec.sales_stage_id;
444 -- l_line_rec.sales_stage := C_Get_Opp_Line_Rec.sales_stage;
445 -- l_line_rec.win_probability
446 -- := C_Get_Opp_Line_Rec.win_probability;
447 -- l_line_rec.status_code := C_Get_Opp_Line_Rec.status_code;
448 -- l_line_rec.status := C_Get_Opp_Line_Rec.status;
449 -- l_line_rec.decision_date := C_Get_Opp_Line_Rec.decision_date;
450 -- l_line_rec.channel_code := C_Get_Opp_Line_Rec.channel_code;
451 -- l_line_rec.channel := C_Get_Opp_Line_Rec.channel;
452 -- l_line_rec.unit_price := C_Get_Opp_Line_Rec.unit_price;
453 -- l_line_rec.quoted_line_flag
454 -- := C_Get_Opp_Line_Rec.quoted_line_flag;
455 -- l_line_rec.member_access := C_Get_Opp_Line_Rec.member_access;
456 -- l_line_rec.member_role := C_Get_Opp_Line_Rec.member_role;
457 -- l_line_rec.currency_code := C_Get_Opp_Line_Rec.currency_code;
458 -- l_line_rec.owner_scredit_percent
459 -- := C_Get_Opp_Line_Rec.owner_scredit_percent;
460 l_line_rec.attribute_category
461 := C_Get_Opp_Line_Rec.attribute_category;
462 l_line_rec.attribute1 := C_Get_Opp_Line_Rec.attribute1;
463 l_line_rec.attribute2 := C_Get_Opp_Line_Rec.attribute2;
464 l_line_rec.attribute3 := C_Get_Opp_Line_Rec.attribute3;
465 l_line_rec.attribute4 := C_Get_Opp_Line_Rec.attribute4;
466 l_line_rec.attribute5 := C_Get_Opp_Line_Rec.attribute5;
467 l_line_rec.attribute6 := C_Get_Opp_Line_Rec.attribute6;
468 l_line_rec.attribute7 := C_Get_Opp_Line_Rec.attribute7;
469 l_line_rec.attribute8 := C_Get_Opp_Line_Rec.attribute8;
470 l_line_rec.attribute9 := C_Get_Opp_Line_Rec.attribute9;
471 l_line_rec.attribute10 := C_Get_Opp_Line_Rec.attribute10;
472 l_line_rec.attribute11 := C_Get_Opp_Line_Rec.attribute11;
473 l_line_rec.attribute12 := C_Get_Opp_Line_Rec.attribute12;
474 l_line_rec.attribute13 := C_Get_Opp_Line_Rec.attribute13;
475 l_line_rec.attribute14 := C_Get_Opp_Line_Rec.attribute14;
476 l_line_rec.attribute15 := C_Get_Opp_Line_Rec.attribute15;
477
478 l_line_rec.product_category_id := C_Get_Opp_Line_Rec.product_category_id;
479 l_line_rec.product_cat_set_id := C_Get_Opp_Line_Rec.product_cat_set_id;
480
481 X_OPP_LINES_tbl(l_cnt_line) := l_line_rec;
482
483 END Loop;
484
485 END Loop;
486
487 --
488 -- END of API body
489 --
490
491 -- Standard check for p_commit
492 IF FND_API.to_Boolean( p_commit )
493 THEN
494 COMMIT WORK;
495 END IF;
496
497
498 -- Debug Message
499 IF l_debug THEN
500 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'PVT: ' || l_api_name || ' End');
501 END IF;
502
503 -- Standard call to get message count and IF count is 1, get message info.
504 FND_MSG_PUB.Count_And_Get
505 ( p_count => x_msg_count,
506 p_data => x_msg_data );
507
508 EXCEPTION
509 WHEN FND_API.G_EXC_ERROR THEN
510 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
511 P_MODULE => l_module
512 ,P_API_NAME => L_API_NAME
513 ,P_PKG_NAME => G_PKG_NAME
514 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
515 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
516 ,X_MSG_COUNT => X_MSG_COUNT
517 ,X_MSG_DATA => X_MSG_DATA
518 ,X_RETURN_STATUS => X_RETURN_STATUS);
519
520 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
521 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
522 P_MODULE => l_module
523 ,P_API_NAME => L_API_NAME
524 ,P_PKG_NAME => G_PKG_NAME
525 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
526 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
527 ,X_MSG_COUNT => X_MSG_COUNT
528 ,X_MSG_DATA => X_MSG_DATA
529 ,X_RETURN_STATUS => X_RETURN_STATUS);
530
531 WHEN OTHERS THEN
532 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
533 P_MODULE => l_module
534 ,P_API_NAME => L_API_NAME
535 ,P_PKG_NAME => G_PKG_NAME
536 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
537 ,P_SQLCODE => SQLCODE
538 ,P_SQLERRM => SQLERRM
539 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
540 ,X_MSG_COUNT => X_MSG_COUNT
541 ,X_MSG_DATA => X_MSG_DATA
542 ,X_RETURN_STATUS => X_RETURN_STATUS);
543 END Get_Potential_Opportunity;
544
545
546 -- API Name: Copy_Lead_To_Opportunity new
547 /* API renamed by Francis on 06/26/2001 from Link_Lead_To_Opportunity to Copy_Lead_To_Opportunity */
548
549 PROCEDURE Copy_Lead_To_Opportunity(
550 P_Api_Version_Number IN NUMBER,
551 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
552 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
553 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
554 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
555 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
556 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
557 P_identity_salesforce_id IN NUMBER, --:= NULL,
558 P_identity_salesgroup_id IN NUMBER := FND_API.G_MISS_NUM,
559 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
560 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
561 P_SALES_LEAD_ID IN NUMBER,
562 P_SALES_LEAD_LINE_TBL IN AS_SALES_LEADS_PUB.SALES_LEAD_LINE_TBL_TYPE
563 := AS_SALES_LEADS_PUB.G_MISS_SALES_LEAD_LINE_TBL,
564 P_OPPORTUNITY_ID IN NUMBER,
565 X_Return_Status OUT NOCOPY VARCHAR2,
566 X_Msg_Count OUT NOCOPY NUMBER,
567 X_Msg_Data OUT NOCOPY VARCHAR2
568 )
569 IS
570
571 CURSOR C_lead_link_Exists (X_Sales_Lead_Id NUMBER, X_Opportunity_Id NUMBER) IS
572 SELECT 'X'
573 FROM as_sales_lead_opportunity
574 WHERE sales_lead_id = X_Sales_Lead_Id
575 AND opportunity_id = X_Opportunity_Id;
576
577 CURSOR C_Sales_Owner_Check (X_Identity_Salesforce_Id NUMBER,
578 X_Opportunity_Id NUMBER,
579 X_Team_Leader_Flag VARCHAR2) IS
580 SELECT salesforce_id
581 FROM as_accesses_all
582 WHERE salesforce_id = X_Identity_Salesforce_Id
583 AND lead_id = X_Opportunity_Id
584 AND team_leader_flag = X_Team_Leader_Flag;
585
586 CURSOR C_Get_Sales_Owner (X_Opportunity_Id NUMBER) IS
587 SELECT salesforce_id
588 FROM as_accesses_all
589 WHERE lead_id = X_Opportunity_Id
590 AND team_leader_flag = 'Y';
591
592 /*
593 CURSOR C_Get_Last_Update_Date (X_Sales_Lead_Id NUMBER) IS
594 SELECT last_update_date,channel_code
595 FROM as_sales_leads
596 WHERE sales_lead_id = X_Sales_Lead_Id;
597 */
598
599 CURSOR C_Get_Sales_Lead (X_Sales_Lead_Id NUMBER) IS
600 SELECT sales_lead_id
601 ,last_update_date
602 ,last_updated_by
603 ,creation_date
604 ,created_by
605 ,last_update_login
606 ,request_id
607 ,program_application_id
608 ,program_id
609 ,program_update_date
610 ,lead_number
611 ,status_code
612 ,customer_id
613 ,address_id
614 ,source_promotion_id
615 ,initiating_contact_id
616 ,orig_system_reference
617 ,contact_role_code
618 ,channel_code
619 ,budget_amount
620 ,currency_code
621 ,decision_timeframe_code
622 ,close_reason
623 ,lead_rank_code
624 ,parent_project
625 ,description
626 ,attribute_category
627 ,attribute1
628 ,attribute2
629 ,attribute3
630 ,attribute4
631 ,attribute5
632 ,attribute6
633 ,attribute7
634 ,attribute8
635 ,attribute9
636 ,attribute10
637 ,attribute11
638 ,attribute12
639 ,attribute13
640 ,attribute14
641 ,attribute15
642 ,assign_to_person_id
643 ,assign_to_salesforce_id
644 ,budget_status_code
645 ,assign_date
646 ,accept_flag
647 ,vehicle_response_code
648 ,total_score
649 ,scorecard_id
650 ,keep_flag
651 ,urgent_flag
652 ,import_flag
653 ,reject_reason_code
654 ,lead_rank_id
655 ,deleted_flag
656 ,assign_sales_group_id
657 ,offer_id
658 -- ,security_group_id
659 ,incumbent_partner_party_id
660 ,incumbent_partner_resource_id
661 FROM as_sales_leads
662 WHERE sales_lead_id = X_Sales_Lead_Id;
663
664 CURSOR C_Get_Opportunity (x_Opportunity_Id NUMBER) IS
665 SELECT last_update_date
666 ,last_updated_by
667 ,creation_Date
668 ,created_by
669 ,last_update_login
670 ,lead_id
671 ,lead_number
672 ,orig_system_reference
673 ,lead_source_code
674 ,description
675 ,source_promotion_id
676 ,customer_id
677 ,address_id
678 ,sales_stage_id
679 ,win_probability
680 ,status status_code
681 -- ,initiating_contact_id
682 -- ,rank
683 ,channel_code
684 ,decision_date
685 ,currency_code
686 ,price_list_id
687 ,close_reason close_reason_code
688 -- ,close_competitor_code
689 ,close_competitor_id
690 ,close_competitor
691 ,close_comment
692 ,end_user_customer_id
693 ,end_user_customer_name
694 ,end_user_address_id
695 ,total_amount
696 ,attribute_category
697 ,attribute1
698 ,attribute2
699 ,attribute3
700 ,attribute4
701 ,attribute5
702 ,attribute6
703 ,attribute7
704 ,attribute8
705 ,attribute9
706 ,attribute10
707 ,attribute11
708 ,attribute12
709 ,attribute13
710 ,attribute14
711 ,attribute15
712 ,parent_project
713 -- ,FND_API.G_MISS_NUM -- ,security_group_id
714 From AS_LEADS_ALL
715 Where lead_id = X_Opportunity_Id;
716
717 CURSOR C_Get_Sales_lead_lines (X_Sales_Lead_Id NUMBER) IS
718 SELECT sales_lead_line_id
719 ,last_update_date
720 ,last_updated_by
721 ,creation_Date
722 ,created_by
723 ,last_update_login
724 ,sales_lead_id
725 ,interest_type_id
726 ,primary_interest_code_id
727 ,secondary_interest_code_id
728 ,inventory_item_id
729 ,organization_id
730 ,uom_code
731 ,quantity
732 ,budget_amount --total_amount
733 ,source_promotion_id
734 ,attribute_category
735 ,attribute1
736 ,attribute2
737 ,attribute3
738 ,attribute4
739 ,attribute5
740 ,attribute6
741 ,attribute7
742 ,attribute8
743 ,attribute9
744 ,attribute10
745 ,attribute11
746 ,attribute12
747 ,attribute13
748 ,attribute14
749 ,attribute15
750 ,offer_id
751 -- ,security_group_id
752 ,category_id
753 ,category_set_id
754 FROM as_sales_lead_lines
755 WHERE sales_lead_id = X_Sales_Lead_Id;
756
757 -- 102700 FFANG for bug 1478517, get sales lead contacts information
758 CURSOR C_Get_Sales_Lead_Contacts(c_sales_lead_id number) IS
759 SELECT contact_id
760 ,contact_party_id
761 ,last_update_date
762 ,last_updated_by
763 ,creation_Date
764 ,created_by
765 ,last_update_login
766 ,enabled_flag
767 ,rank
768 ,customer_id
769 ,address_id
770 ,phone_id
771 ,contact_role_code
772 ,primary_contact_flag
773 ,attribute_category
774 ,attribute1
775 ,attribute2
776 ,attribute3
777 ,attribute4
778 ,attribute5
779 ,attribute6
780 ,attribute7
781 ,attribute8
782 ,attribute9
783 ,attribute10
784 ,attribute11
785 ,attribute12
786 ,attribute13
787 ,attribute14
788 ,attribute15
789 -- ,security_group_id
790 FROM as_sales_lead_contacts
791 WHERE sales_lead_id = c_sales_lead_id;
792 -- end 102700 FFANG
793
794 -- ffang 020601, for bug 1628894, check duplicate contact before calling
795 -- create_opp_contact
796 CURSOR c_check_dup_contact (x_contact_party_id NUMBER) IS
797 SELECT 'X'
798 FROM AS_LEAD_CONTACTS_ALL
799 WHERE contact_party_id = x_contact_party_id
800 and lead_id = p_opportunity_id;
801 l_dup_contact VARCHAR2(1);
802 -- end ffang 020601
803
804 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Lead_To_Opportunity';
805 l_api_version_number CONSTANT NUMBER := 2.0;
806 l_Identity_Sales_Member_Rec AS_SALES_MEMBER_PUB.Sales_Member_Rec_Type;
807 l_Sales_Lead_Rec AS_SALES_LEADS_PUB.Sales_Lead_Rec_Type;
808 l_sales_lead_line_tbl AS_SALES_LEADS_PUB.Sales_Lead_Line_Tbl_Type;
809 l_header_rec AS_OPPORTUNITY_PUB.Header_Rec_Type;
810 l_line_tbl AS_OPPORTUNITY_PUB.Line_Tbl_Type;
811 l_line_out_tbl AS_OPPORTUNITY_PUB.Line_Out_Tbl_Type;
812 l_contact_tbl AS_OPPORTUNITY_PUB.Contact_Tbl_Type;
813 l_contact_out_tbl AS_OPPORTUNITY_PUB.Contact_Out_Tbl_Type;
814 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
815 l_val VARCHAR2(1) default null;
816 --axavier l_val_id NUMBER;
817 l_lead_line_id NUMBER;
818 l_last_update_date DATE := FND_API.G_MISS_DATE;
819 l_channel_code VARCHAR2(30);
820 l_Lead_Opportunity_Id NUMBER;
821 l_Lead_Opp_Line_Id NUMBER;
822 l_sales_lead_line_id NUMBER default null;
823 -- l_line_security_group_id NUMBER := FND_API.G_MISS_NUM;
824 -- l_opp_security_group_id NUMBER := FND_API.G_MISS_NUM;
825 l_update_access_flag VARCHAR2(1);
826 l_member_role VARCHAR2(5);
827 l_member_access VARCHAR2(5);
828 l_cnt NUMBER := 0;
829 l_debug BOOLEAN;
830 l_module CONSTANT VARCHAR2(255) := 'as.plsql.slopv.Copy_Lead_To_Opportunity';
831
832 BEGIN
833 -- Standard Start of API savepoint
834 SAVEPOINT COPY_LEAD_TO_OPPORTUNITY_PVT;
835
836 -- Standard call to check for call compatibility.
837 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
838 p_api_version_number,
839 l_api_name,
840 G_PKG_NAME)
841 THEN
842 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
843 END IF;
844 l_debug := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
845
846 -- Initialize message list IF p_init_msg_list is set to TRUE.
847 IF FND_API.to_Boolean( p_init_msg_list )
848 THEN
849 FND_MSG_PUB.initialize;
850 END IF;
851
852 -- Debug Message
853 IF l_debug THEN
854 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'PVT:' || l_api_name || ' Start');
855 END IF;
856
857 -- Initialize API return status to SUCCESS
858 x_return_status := FND_API.G_RET_STS_SUCCESS;
859
860 --
861 -- Api body
862 --
863
864 -- ******************************************************************
865 -- Validate Environment
866 -- ******************************************************************
867 IF FND_GLOBAL.User_Id IS NULL
868 THEN
869 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
870 THEN
871 AS_UTILITY_PVT.Set_Message(
872 p_module => l_module,
873 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
874 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
875 p_token1 => 'PROFILE',
876 p_token1_value => 'USER_ID');
877 END IF;
878 RAISE FND_API.G_EXC_ERROR;
879 END IF;
880
881 IF (p_validation_level = fnd_api.g_valid_level_full)
882 THEN
883 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
884 p_api_version_number => 2.0
885 ,p_init_msg_list => p_init_msg_list
886 ,p_salesforce_id => P_Identity_Salesforce_Id
887 ,p_admin_group_id => p_admin_group_id
888 ,x_return_status => x_return_status
889 ,x_msg_count => x_msg_count
890 ,x_msg_data => x_msg_data
891 ,x_sales_member_rec => l_identity_sales_member_rec);
892 END IF;
893
894 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
895 RAISE FND_API.G_EXC_ERROR;
896 END IF;
897
898 -- Get sales lead header based on parameter P_Sales_Lead_Id
899 OPEN C_Get_Sales_Lead (P_Sales_Lead_Id);
900 FETCH C_Get_Sales_Lead INTO
901 l_Sales_Lead_Rec.sales_lead_id
902 ,l_Sales_Lead_Rec.last_update_date
903 ,l_Sales_Lead_Rec.last_updated_by
904 ,l_Sales_Lead_Rec.creation_date
905 ,l_Sales_Lead_Rec.created_by
906 ,l_Sales_Lead_Rec.last_update_login
907 ,l_Sales_Lead_Rec.request_id
908 ,l_Sales_Lead_Rec.program_application_id
909 ,l_Sales_Lead_Rec.program_id
910 ,l_Sales_Lead_Rec.program_update_date
911 ,l_Sales_Lead_Rec.lead_number
912 ,l_Sales_Lead_Rec.status_code
913 ,l_Sales_Lead_Rec.customer_id
914 ,l_Sales_Lead_Rec.address_id
915 ,l_Sales_Lead_Rec.source_promotion_id
916 ,l_Sales_Lead_Rec.initiating_contact_id
917 ,l_Sales_Lead_Rec.orig_system_reference
918 ,l_Sales_Lead_Rec.contact_role_code
919 ,l_Sales_Lead_Rec.channel_code
920 ,l_Sales_Lead_Rec.budget_amount
921 ,l_Sales_Lead_Rec.currency_code
922 ,l_Sales_Lead_Rec.decision_timeframe_code
923 ,l_Sales_Lead_Rec.close_reason
924 ,l_Sales_Lead_Rec.lead_rank_code
925 ,l_Sales_Lead_Rec.parent_project
926 ,l_Sales_Lead_Rec.description
927 ,l_Sales_Lead_Rec.attribute_category
928 ,l_Sales_Lead_Rec.attribute1
929 ,l_Sales_Lead_Rec.attribute2
930 ,l_Sales_Lead_Rec.attribute3
931 ,l_Sales_Lead_Rec.attribute4
932 ,l_Sales_Lead_Rec.attribute5
933 ,l_Sales_Lead_Rec.attribute6
934 ,l_Sales_Lead_Rec.attribute7
935 ,l_Sales_Lead_Rec.attribute8
936 ,l_Sales_Lead_Rec.attribute9
937 ,l_Sales_Lead_Rec.attribute10
938 ,l_Sales_Lead_Rec.attribute11
939 ,l_Sales_Lead_Rec.attribute12
940 ,l_Sales_Lead_Rec.attribute13
941 ,l_Sales_Lead_Rec.attribute14
942 ,l_Sales_Lead_Rec.attribute15
943 ,l_Sales_Lead_Rec.assign_to_person_id
944 ,l_Sales_Lead_Rec.assign_to_salesforce_id
945 ,l_Sales_Lead_Rec.budget_status_code
946 ,l_Sales_Lead_Rec.assign_date
947 ,l_Sales_Lead_Rec.accept_flag
948 ,l_Sales_Lead_Rec.vehicle_response_code
949 ,l_Sales_Lead_Rec.total_score
950 ,l_Sales_Lead_Rec.scorecard_id
951 ,l_Sales_Lead_Rec.keep_flag
952 ,l_Sales_Lead_Rec.urgent_flag
953 ,l_Sales_Lead_Rec.import_flag
954 ,l_Sales_Lead_Rec.reject_reason_code
955 ,l_Sales_Lead_Rec.lead_rank_id
956 ,l_Sales_Lead_Rec.deleted_flag
957 ,l_Sales_Lead_Rec.assign_sales_group_id
958 ,l_Sales_Lead_Rec.offer_id
959 -- ,l_Sales_Lead_Rec.security_group_id
960 ,l_Sales_Lead_Rec.incumbent_partner_party_id
961 ,l_Sales_Lead_Rec.incumbent_partner_resource_id;
962
963 IF ( C_Get_Sales_Lead%NOTFOUND) THEN
964 IF l_debug THEN
965 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Private API: SALES_LEAD_ID is invalid');
966 END IF;
967
968 AS_UTILITY_PVT.Set_Message(
969 p_module => l_module,
970 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
971 p_msg_name => 'API_INVALID_ID',
972 p_token1 => 'COLUMN',
973 p_token1_value => 'SALES_LEAD_ID',
974 p_token2 => 'VALUE',
975 p_token2_value => P_Sales_Lead_Id );
976
977 x_return_status := FND_API.G_RET_STS_ERROR;
978
979 END IF;
980
981 CLOSE C_Get_Sales_Lead;
982
983 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
984 RAISE FND_API.G_EXC_ERROR;
985 END IF;
986
987
988
989
990 -- Get opportunity header based on parameter P_Opportunity_Id
991 OPEN C_Get_Opportunity (P_Opportunity_Id);
992 FETCH C_Get_Opportunity INTO
993 l_header_rec.last_update_date
994 ,l_header_rec.last_updated_by
995 ,l_header_rec.creation_Date
996 ,l_header_rec.created_by
997 ,l_header_rec.last_update_login
998 ,l_header_rec.lead_id
999 ,l_header_rec.lead_number
1000 ,l_header_rec.orig_system_reference
1001 ,l_header_rec.lead_source_code
1002 ,l_header_rec.description
1003 ,l_header_rec.source_promotion_id
1004 ,l_header_rec.customer_id
1005 ,l_header_rec.address_id
1006 ,l_header_rec.sales_stage_id
1007 ,l_header_rec.win_probability
1008 ,l_header_rec.status_code
1009 -- ,l_header_rec.initiating_contact_id
1010 -- ,l_header_rec.rank
1011 ,l_header_rec.channel_code
1012 ,l_header_rec.decision_date
1013 ,l_header_rec.currency_code
1014 ,l_header_rec.price_list_id
1015 ,l_header_rec.close_reason_code
1016 -- ,l_header_rec.close_competitor_code
1017 ,l_header_rec.close_competitor_id
1018 ,l_header_rec.close_competitor
1019 ,l_header_rec.close_comment
1020 ,l_header_rec.end_user_customer_id
1021 ,l_header_rec.end_user_customer_name
1022 ,l_header_rec.end_user_address_id
1023 ,l_header_rec.total_amount
1024 ,l_header_rec.attribute_category
1025 ,l_header_rec.attribute1
1026 ,l_header_rec.attribute2
1027 ,l_header_rec.attribute3
1028 ,l_header_rec.attribute4
1029 ,l_header_rec.attribute5
1030 ,l_header_rec.attribute6
1031 ,l_header_rec.attribute7
1032 ,l_header_rec.attribute8
1033 ,l_header_rec.attribute9
1034 ,l_header_rec.attribute10
1035 ,l_header_rec.attribute11
1036 ,l_header_rec.attribute12
1037 ,l_header_rec.attribute13
1038 ,l_header_rec.attribute14
1039 ,l_header_rec.attribute15
1040 ,l_header_rec.parent_project;
1041 -- ,l_opp_security_group_id;
1042
1043 IF ( C_Get_Opportunity%NOTFOUND) THEN
1044 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1045 THEN
1046 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
1047 FND_MESSAGE.Set_Token ('INFO', 'Opportunity', FALSE);
1048 FND_MSG_PUB.Add;
1049 END IF;
1050 --commented for bug 2013040 raise FND_API.G_EXC_ERROR;
1051 x_return_status := FND_API.G_RET_STS_ERROR;
1052
1053 END IF;
1054 -- Debug Message
1055 IF l_debug THEN
1056 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Close Cursor C_Get_Opportunity');
1057 END IF;
1058 Close C_Get_Opportunity;
1059
1060 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1061 RAISE FND_API.G_EXC_ERROR;
1062 END IF;
1063
1064 -- Invoke validation procedures
1065 -- Debug message
1066 IF l_debug THEN
1067 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling Validate_sales_lead_id');
1068 END IF;
1069
1070 -- Validate sales lead id whther it is a valid ID.
1071 AS_SALES_LEADS_PVT.Validate_Sales_Lead_Id (
1072 P_Init_Msg_List => FND_API.G_FALSE
1073 ,P_Validation_mode => FND_API.G_MISS_CHAR
1074 ,P_Sales_Lead_Id => P_Sales_Lead_Id
1075 ,X_Return_Status => X_Return_Status
1076 ,X_Msg_Count => X_Msg_Count
1077 ,X_Msg_Data => X_Msg_Data
1078 );
1079
1080 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1081 raise FND_API.G_EXC_ERROR;
1082 END IF;
1083
1084 -- Validate one sales lead can only be link to one opportunity
1085 -- Debug message
1086 IF l_debug THEN
1087 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Validate existing link');
1088 END IF;
1089
1090 /*
1091 OPEN C_Lead_Link_Exists (P_SALES_LEAD_ID, P_OPPORTUNITY_ID);
1092 FETCH C_Lead_Link_Exists into l_val;
1093
1094 IF l_val IS NOT NULL
1095 THEN
1096 -- ffang 020301, we want the error message in every case, don't need
1097 -- to check message level.
1098 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1099 -- THEN
1100 -- ffang 100900: For bug 1448995
1101 -- Use error message #45661 instead of API_INVALID_ID
1102 FND_MESSAGE.Set_Name('AS', 'API_DUPLICATE_LINK');
1103 FND_MESSAGE.Set_Token('SLD_ID', p_sales_lead_id, FALSE);
1104 -- end ffang 100900
1105 FND_MSG_PUB.ADD;
1106 -- END IF;
1107 x_return_status := FND_API.G_RET_STS_ERROR;
1108 END IF;
1109
1110 CLOSE C_Lead_Link_Exists;
1111
1112 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1113 raise FND_API.G_EXC_ERROR;
1114 END IF;
1115 */
1116
1117 l_Sales_Lead_Rec.Status_Code := nvl(FND_PROFILE.Value('AS_LEAD_LINK_STATUS'),'CONVERTED_TO_OPPORTUNITY');
1118 -- has to be changed once the profile problem is solved
1119 --l_Sales_Lead_Rec.Status_Code := 'QUALIFIED';
1120
1121
1122 -- Validate if the sales lead owner is the opportunity team leader
1123 -- Debug message
1124 IF l_debug THEN
1125 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Validate sales lead owner');
1126 END IF;
1127
1128 /*
1129 -- axavier commented this. This check is not required, as the as_sales_leads salesforce_id is not updated
1130 OPEN C_Sales_Owner_Check (l_Sales_Lead_Rec.assign_to_salesforce_id,
1131 P_Opportunity_Id,'Y');
1132 FETCH C_Sales_Owner_Check INTO l_val_id;
1133
1134 IF C_Sales_Owner_Check%NOTFOUND
1135 THEN
1136 OPEN C_Get_Sales_Owner (P_Opportunity_Id);
1137 FETCH C_Get_Sales_Owner INTO l_val_id;
1138
1139 IF C_Get_Sales_Owner%NOTFOUND
1140 THEN
1141 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1142 THEN
1143 -- ffang 100900: For bug 1448995
1144 -- Use error message #45662 instead of API_INVALID_ID
1145 FND_MESSAGE.Set_Name('AS', 'API_INVALID_OPP');
1146 FND_MESSAGE.Set_Token('OPP_ID', P_Opportunity_Id, FALSE);
1147 -- end ffang 100900
1148 FND_MSG_PUB.ADD;
1149 END IF;
1150
1151 x_return_status := FND_API.G_RET_STS_ERROR;
1152 END IF;
1153
1154 CLOSE C_Get_Sales_Owner;
1155
1156 -- ffang 020301, we don't want to update Assign_To_salesforce_id
1157 -- to be the team leader's salesforce_id
1158 -- l_Sales_Lead_Rec.Assign_To_salesforce_id := l_val_id;
1159 END IF;
1160
1161 CLOSE C_Sales_Owner_Check;
1162
1163 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1164 raise FND_API.G_EXC_ERROR;
1165 END IF;
1166 */
1167 l_header_rec.updateable_flag := 'N';
1168 l_header_rec.member_access := FND_API.G_MISS_CHAR;
1169 l_header_rec.member_role := FND_API.G_MISS_CHAR;
1170
1171 l_cnt := 0;
1172 -- For C_Sales_Lead_lines_Rec In C_Get_Sales_lead_lines(P_Sales_Lead_Id)
1173 for i in 1 .. P_SALES_LEAD_LINE_TBL.count
1174 Loop
1175 l_cnt := l_cnt + 1;
1176 l_sales_lead_line_tbl(l_cnt).sales_lead_line_id
1177 := P_SALES_LEAD_LINE_TBL(i).sales_lead_line_id;
1178
1179 l_line_tbl(l_cnt).last_update_date
1180 := P_SALES_LEAD_LINE_TBL(i).last_update_date;
1181 l_line_tbl(l_cnt).last_updated_by
1182 := P_SALES_LEAD_LINE_TBL(i).last_updated_by;
1183 l_line_tbl(l_cnt).creation_Date
1184 := P_SALES_LEAD_LINE_TBL(i).creation_Date;
1185 l_line_tbl(l_cnt).created_by
1186 := P_SALES_LEAD_LINE_TBL(i).created_by;
1187 l_line_tbl(l_cnt).last_update_login
1188 := P_SALES_LEAD_LINE_TBL(i).last_update_login;
1189 -- ffang 101200 for bug 1449308
1190 -- Should not use sales_lead_line_id as lead_line_id to create an
1191 -- opportunity line.
1192 /*
1193 l_line_tbl(l_cnt).lead_line_id
1194 := P_SALES_LEAD_LINE_TBL(i).sales_lead_line_id;
1195 */
1196 l_line_tbl(l_cnt).lead_line_id := NULL;
1197 -- end ffang 101200
1198 l_line_tbl(l_cnt).lead_id := p_opportunity_id;
1199 -- 103000 FFANG as_lead_lines_all.status_code has been obsolete
1200 -- l_line_tbl(l_cnt).status_code := 'PRELIMINARY';
1201 -- end 103000 FFANG
1202 /* Commented by gbatra for product hierarchy uptake
1203 l_line_tbl(l_cnt).interest_type_id
1204 := P_SALES_LEAD_LINE_TBL(i).interest_type_id;
1205 l_line_tbl(l_cnt).primary_interest_code_id
1206 := P_SALES_LEAD_LINE_TBL(i).primary_interest_code_id;
1207 l_line_tbl(l_cnt).secondary_interest_code_id
1208 := P_SALES_LEAD_LINE_TBL(i).secondary_interest_code_id;
1209 */
1210
1211 -- l_line_tbl(l_cnt).interest_status_code -- obsolete
1212 l_line_tbl(l_cnt).inventory_item_id
1213 := P_SALES_LEAD_LINE_TBL(i).inventory_item_id;
1214 l_line_tbl(l_cnt).organization_id
1215 := P_SALES_LEAD_LINE_TBL(i).organization_id;
1216 l_line_tbl(l_cnt).uom_code
1217 := P_SALES_LEAD_LINE_TBL(i).uom_code;
1218 l_line_tbl(l_cnt).quantity
1219 := P_SALES_LEAD_LINE_TBL(i).quantity;
1220 l_line_tbl(l_cnt).total_amount
1221 := P_SALES_LEAD_LINE_TBL(i).budget_amount;
1222 -- l_line_tbl(l_cnt).sales_stage_id -- obsolete
1223 -- l_line_tbl(l_cnt).ship_date -- not exist in sales lead lines
1224 -- l_line_tbl(l_cnt).win_probability -- obsolete
1225 -- l_line_tbl(l_cnt).decision_date -- obsolete
1226 -- 103000 FFANG as_lead_lines_all.channel_code has been obsolete
1227 -- l_line_tbl(l_cnt).channel_code := l_header_rec.channel_code;
1228 -- end 103000 FFANG
1229 -- l_line_tbl(l_cnt).quoted_line_flag -- not exist in sales lead lines
1230 -- l_line_tbl(l_cnt).original_lead_line_id -- not exist in sl lines
1231 -- l_line_tbl(l_cnt).org_id -- not exist in sales lead lines
1232 -- l_line_tbl(l_cnt).price -- not exist in sales lead lines
1233 -- 103000 FFANG for bug 1479671
1234 l_line_tbl(l_cnt).source_promotion_id
1235 := P_SALES_LEAD_LINE_TBL(i).source_promotion_id;
1236 --end 103000 FFANG
1237 -- l_line_tbl(l_cnt).price_volume_margin -- not exist in sl lines
1238
1239 l_line_tbl(l_cnt).attribute_category
1240 := P_SALES_LEAD_LINE_TBL(i).attribute_category;
1241 l_line_tbl(l_cnt).attribute1 := P_SALES_LEAD_LINE_TBL(i).attribute1;
1242 l_line_tbl(l_cnt).attribute2 := P_SALES_LEAD_LINE_TBL(i).attribute2;
1243 l_line_tbl(l_cnt).attribute3 := P_SALES_LEAD_LINE_TBL(i).attribute3;
1244 l_line_tbl(l_cnt).attribute4 := P_SALES_LEAD_LINE_TBL(i).attribute4;
1245 l_line_tbl(l_cnt).attribute5 := P_SALES_LEAD_LINE_TBL(i).attribute5;
1246 l_line_tbl(l_cnt).attribute6 := P_SALES_LEAD_LINE_TBL(i).attribute6;
1247 l_line_tbl(l_cnt).attribute7 := P_SALES_LEAD_LINE_TBL(i).attribute7;
1248 l_line_tbl(l_cnt).attribute8 := P_SALES_LEAD_LINE_TBL(i).attribute8;
1249 l_line_tbl(l_cnt).attribute9 := P_SALES_LEAD_LINE_TBL(i).attribute9;
1250 l_line_tbl(l_cnt).attribute10 := P_SALES_LEAD_LINE_TBL(i).attribute10;
1251 l_line_tbl(l_cnt).attribute11 := P_SALES_LEAD_LINE_TBL(i).attribute11;
1252 l_line_tbl(l_cnt).attribute12 := P_SALES_LEAD_LINE_TBL(i).attribute12;
1253 l_line_tbl(l_cnt).attribute13 := P_SALES_LEAD_LINE_TBL(i).attribute13;
1254 l_line_tbl(l_cnt).attribute14 := P_SALES_LEAD_LINE_TBL(i).attribute14;
1255 l_line_tbl(l_cnt).attribute15 := P_SALES_LEAD_LINE_TBL(i).attribute15;
1256 l_line_tbl(l_cnt).member_access := FND_API.G_MISS_CHAR;
1257 l_line_tbl(l_cnt).member_role := FND_API.G_MISS_CHAR;
1258 l_line_tbl(l_cnt).owner_scredit_percent := FND_API.G_MISS_NUM;
1259 -- l_line_security_group_id
1260 -- := P_SALES_LEAD_LINE_TBL(i).security_group_id;
1261 -- 103000 FFANG for bug 1479671
1262 l_line_tbl(l_cnt).offer_id := P_SALES_LEAD_LINE_TBL(i).offer_id;
1263 -- end 103000 FFANG
1264 l_line_tbl(l_cnt).product_category_id := P_SALES_LEAD_LINE_TBL(i).category_id;
1265 l_line_tbl(l_cnt).product_cat_set_id := P_SALES_LEAD_LINE_TBL(i).category_set_id;
1266
1267 END Loop;
1268
1269 IF l_line_tbl.count > 0
1270 THEN
1271 -- ffang 030503, bug 2826512, call PUB instead of PVT
1272 -- AS_OPP_line_PVT.Create_opp_lines (
1273 AS_OPPORTUNITY_PUB.Create_Opp_Lines (
1274 p_api_version_number => 2.0,
1275 p_init_msg_list => FND_API.G_FALSE,
1276 p_commit => FND_API.G_FALSE, -- ffang020100
1277 -- p_commit => FND_API.G_TRUE,
1278 p_validation_level => p_validation_level,
1279 -- ffang 012501, When adding a opportunity line, enforce security
1280 -- checking
1281 P_Check_Access_Flag => 'Y', -- P_Check_Access_Flag,
1282 -- P_Check_Access_Flag => FND_API.G_FALSE,
1283 P_Admin_Flag => P_Admin_Flag, -- FND_API.G_FALSE,
1284 P_Admin_Group_Id => P_Admin_Group_Id, -- NULL,
1285 P_Identity_Salesforce_Id => p_identity_salesforce_id,
1286 -- l_Sales_Lead_Rec.assign_to_salesforce_id,
1287 P_salesgroup_id => P_identity_salesgroup_id,
1288 P_profile_tbl => AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1289 P_Partner_Cont_Party_id => null,
1290 P_line_tbl => l_line_tbl,
1291 P_Header_Rec => l_header_rec,
1292 X_LINE_OUT_TBL => l_line_out_tbl,
1293 x_return_status => x_return_status,
1294 x_msg_count => x_msg_count,
1295 x_msg_data => x_msg_data);
1296
1297 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1298 raise FND_API.G_EXC_ERROR;
1299 END IF;
1300
1301 For l_index In 1..l_line_out_tbl.count Loop
1302 -- ffang 101200 for bug 1449308
1303 -- l_Lead_Opp_Line_Id should be initialized.
1304 l_Lead_Opp_Line_Id := NULL;
1305 -- end ffang 101200
1306
1307 AS_SALES_LEAD_OPP_PKG.Lead_Opp_Lines_Insert_Row (
1308 px_LEAD_OPP_LINE_ID => l_Lead_Opp_Line_Id
1309 ,p_SALES_LEAD_LINE_ID =>
1310 l_sales_lead_line_tbl(l_index).sales_lead_line_id
1311 ,p_OPP_LINE_ID => l_line_out_tbl(l_index).lead_line_id
1312 ,p_LAST_UPDATE_DATE => SYSDATE
1313 ,p_LAST_UPDATED_BY => FND_GLOBAL.User_Id
1314 ,p_CREATION_DATE => SYSDATE
1315 ,p_CREATED_BY => FND_GLOBAL.User_Id
1316 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
1317 ,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
1318 ,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
1319 ,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
1320 ,p_PROGRAM_UPDATE_DATE => SYSDATE
1321 ,p_ATTRIBUTE_CATEGORY => l_line_tbl(l_index).attribute_category
1322 ,p_ATTRIBUTE1 => l_line_tbl(l_index).attribute1
1323 ,p_ATTRIBUTE2 => l_line_tbl(l_index).attribute2
1324 ,p_ATTRIBUTE3 => l_line_tbl(l_index).attribute3
1325 ,p_ATTRIBUTE4 => l_line_tbl(l_index).attribute4
1326 ,p_ATTRIBUTE5 => l_line_tbl(l_index).attribute5
1327 ,p_ATTRIBUTE6 => l_line_tbl(l_index).attribute6
1328 ,p_ATTRIBUTE7 => l_line_tbl(l_index).attribute7
1329 ,p_ATTRIBUTE8 => l_line_tbl(l_index).attribute8
1330 ,p_ATTRIBUTE9 => l_line_tbl(l_index).attribute9
1331 ,p_ATTRIBUTE10 => l_line_tbl(l_index).attribute10
1332 ,p_ATTRIBUTE11 => l_line_tbl(l_index).attribute11
1333 ,p_ATTRIBUTE12 => l_line_tbl(l_index).attribute12
1334 ,p_ATTRIBUTE13 => l_line_tbl(l_index).attribute13
1335 ,p_ATTRIBUTE14 => l_line_tbl(l_index).attribute14
1336 ,p_ATTRIBUTE15 => l_line_tbl(l_index).attribute15
1337 -- ,p_SECURITY_GROUP_ID => l_line_security_group_id
1338 );
1339 END Loop;
1340
1341 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1342 raise FND_API.G_EXC_ERROR;
1343 END IF;
1344 END IF;
1345
1346 -- 102700 FFANG for bug 1478517, copy sales lead contacts information to
1347 -- opportunity contact table
1348
1349 -- Copy sales lead contacts data to opportunity record contacts
1350 IF l_debug THEN
1351 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Copy sales lead contacts data to opp');
1352 END IF;
1353 l_cnt := 0;
1354 /*
1355 For C_SL_Contacts_Rec In C_Get_Sales_lead_Contacts(P_Sales_Lead_Id) Loop
1356
1357 -- ffang 020601, for bug 1628894, check if contact_party_id already
1358 -- existed in as_lead_contacts_all, if not then copy values.
1359 OPEN c_check_dup_contact (C_SL_Contacts_Rec.contact_party_id);
1360 FETCH c_check_dup_contact INTO l_dup_contact;
1361
1362 IF c_check_dup_contact%NOTFOUND THEN
1363 l_cnt := l_cnt + 1;
1364
1365 l_contact_tbl(l_cnt).lead_id := p_opportunity_id;
1366 l_contact_tbl(l_cnt).contact_id := C_SL_Contacts_Rec.contact_id;
1367 l_contact_tbl(l_cnt).contact_party_id
1368 := C_SL_Contacts_Rec.contact_party_id;
1369 l_contact_tbl(l_cnt).last_update_date := SYSDATE;
1370 l_contact_tbl(l_cnt).last_updated_by := FND_GLOBAL.USER_ID;
1371 l_contact_tbl(l_cnt).creation_Date := SYSDATE;
1372 l_contact_tbl(l_cnt).created_by := FND_GLOBAL.USER_ID;
1373 l_contact_tbl(l_cnt).last_update_login :=FND_GLOBAL.CONC_LOGIN_ID;
1374 l_contact_tbl(l_cnt).enabled_flag:=C_SL_Contacts_Rec.enabled_flag;
1375 l_contact_tbl(l_cnt).customer_id := C_SL_Contacts_Rec.customer_id;
1376 l_contact_tbl(l_cnt).address_id := C_SL_Contacts_Rec.address_id;
1377 l_contact_tbl(l_cnt).phone_id := C_SL_Contacts_Rec.phone_id;
1378 -- ffang 041802, for bug 2251391, opp contact role stores in rank
1379 l_contact_tbl(l_cnt).rank := C_SL_Contacts_Rec.contact_role_code;
1380 -- end ffang 041802
1381 l_contact_tbl(l_cnt).primary_contact_flag
1382 := C_SL_Contacts_Rec.primary_contact_flag;
1383 l_contact_tbl(l_cnt).role := C_SL_Contacts_Rec.contact_role_code;
1384 l_contact_tbl(l_cnt).attribute_category
1385 := C_SL_Contacts_Rec.attribute_category;
1386 l_contact_tbl(l_cnt).attribute1 := C_SL_Contacts_Rec.attribute1;
1387 l_contact_tbl(l_cnt).attribute2 := C_SL_Contacts_Rec.attribute2;
1388 l_contact_tbl(l_cnt).attribute3 := C_SL_Contacts_Rec.attribute3;
1389 l_contact_tbl(l_cnt).attribute4 := C_SL_Contacts_Rec.attribute4;
1390 l_contact_tbl(l_cnt).attribute5 := C_SL_Contacts_Rec.attribute5;
1391 l_contact_tbl(l_cnt).attribute6 := C_SL_Contacts_Rec.attribute6;
1392 l_contact_tbl(l_cnt).attribute7 := C_SL_Contacts_Rec.attribute7;
1393 l_contact_tbl(l_cnt).attribute8 := C_SL_Contacts_Rec.attribute8;
1394 l_contact_tbl(l_cnt).attribute9 := C_SL_Contacts_Rec.attribute9;
1395 l_contact_tbl(l_cnt).attribute10 := C_SL_Contacts_Rec.attribute10;
1396 l_contact_tbl(l_cnt).attribute11 := C_SL_Contacts_Rec.attribute11;
1397 l_contact_tbl(l_cnt).attribute12 := C_SL_Contacts_Rec.attribute12;
1398 l_contact_tbl(l_cnt).attribute13 := C_SL_Contacts_Rec.attribute13;
1399 l_contact_tbl(l_cnt).attribute14 := C_SL_Contacts_Rec.attribute14;
1400 l_contact_tbl(l_cnt).attribute15 := C_SL_Contacts_Rec.attribute15;
1401 -- l_contact_tbl(l_cnt).security_group_id
1402 -- := C_SL_Contacts_Rec.security_group_id;
1403 END IF;
1404
1405 CLOSE c_check_dup_contact;
1406 END Loop;
1407
1408 IF l_contact_tbl.count > 0
1409 THEN
1410 IF l_debug THEN
1411 AS_UTILITY_PVT.Debug_Message(NULL, 'Calling AS_OPP_CONTACT_PVT.Create_Opp_contacts');
1412 END IF;
1413
1414 -- ffang 030503, bug 2826512, call PUB instead of PVT
1415 -- AS_OPP_CONTACT_PVT.Create_opp_contacts (
1416 AS_OPPORTUNITY_PUB.Create_Contacts (
1417 p_api_version_number => 2.0,
1418 p_init_msg_list => FND_API.G_FALSE,
1419 p_commit => FND_API.G_FALSE,
1420 p_validation_level => p_validation_level,
1421 -- ffang 012501, When adding a opportunity contact, enforce
1422 -- security checking
1423 P_Check_Access_Flag => 'Y', -- p_check_access_flag,
1424 -- P_Check_Access_Flag => FND_API.G_FALSE,
1425 P_Admin_Flag => p_admin_flag,
1426 -- P_Admin_Flag => FND_API.G_FALSE, -- p_admin_flag,
1427 P_Admin_Group_Id => P_Admin_Group_Id,
1428 P_Identity_Salesforce_Id => p_identity_salesforce_id,
1429 P_profile_tbl => AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1430 P_Partner_Cont_Party_id => null,
1431 P_contact_tbl => l_contact_tbl,
1432 X_contact_OUT_TBL => l_contact_out_tbl,
1433 x_return_status => x_return_status,
1434 x_msg_count => x_msg_count,
1435 x_msg_data => x_msg_data);
1436
1437 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1438 raise FND_API.G_EXC_ERROR;
1439 END IF;
1440 -- end 102700 FFANG
1441 END IF;
1442 */
1443 -- Update Sales Leads for Status Code and Sales lead owner IF needed
1444 -- ffang 030503, bug 2826512, call PUB instead of PVT
1445 -- AS_SALES_LEADS_PVT.Update_sales_lead(
1446 AS_SALES_LEADS_PUB.Update_sales_lead(
1447 P_Api_Version_Number => l_api_version_number,
1448 P_Init_Msg_List => FND_API.G_FALSE,
1449 P_Commit => FND_API.G_FALSE,
1450 P_Validation_Level => P_Validation_Level,
1451 P_Check_Access_Flag => 'Y', -- P_Check_Access_Flag,
1452 P_Admin_Flag => P_Admin_Flag,
1453 P_Admin_Group_Id => P_Admin_Group_Id,
1454 P_identity_salesforce_id => P_identity_salesforce_id,
1455 P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
1456 P_SALES_LEAD_Rec => l_Sales_Lead_Rec,
1457 X_Return_Status => X_Return_Status,
1458 X_Msg_Count => X_Msg_Count,
1459 X_Msg_Data => X_Msg_Data
1460 );
1461
1462 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1463 raise FND_API.G_EXC_ERROR;
1464 END IF;
1465
1466
1467 OPEN C_Lead_Link_Exists (P_SALES_LEAD_ID, P_OPPORTUNITY_ID);
1468 FETCH C_Lead_Link_Exists into l_val;
1469
1470 IF l_val IS NOT NULL
1471 THEN
1472 -- Francis. May be we want to update the record wtth the update date
1473 NULL;
1474 ELSE
1475 -- ffang 071202, bug 2451983
1476 l_Lead_Opportunity_Id := NULL;
1477 -- end ffang 071202, bug 2451983
1478
1479 -- Insert Interaction Data for linking Sales Lead to Opportunity
1480 AS_SALES_LEAD_OPP_PKG.Lead_Opportunity_Insert_Row (
1481 px_LEAD_OPPORTUNITY_ID => l_Lead_Opportunity_Id
1482 ,p_SALES_LEAD_ID => P_Sales_Lead_Id
1483 ,p_OPPORTUNITY_ID => P_Opportunity_Id
1484 ,p_LAST_UPDATE_DATE => SYSDATE
1485 ,p_LAST_UPDATED_BY => FND_GLOBAL.User_Id
1486 ,p_CREATION_DATE => SYSDATE
1487 ,p_CREATED_BY => FND_GLOBAL.User_Id
1488 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
1489 ,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
1490 ,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
1491 ,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
1492 ,p_PROGRAM_UPDATE_DATE => SYSDATE
1493 ,p_ATTRIBUTE_CATEGORY => l_header_rec.attribute_category
1494 ,p_ATTRIBUTE1 => l_header_rec.attribute1
1495 ,p_ATTRIBUTE2 => l_header_rec.attribute2
1496 ,p_ATTRIBUTE3 => l_header_rec.attribute3
1497 ,p_ATTRIBUTE4 => l_header_rec.attribute4
1498 ,p_ATTRIBUTE5 => l_header_rec.attribute5
1499 ,p_ATTRIBUTE6 => l_header_rec.attribute6
1500 ,p_ATTRIBUTE7 => l_header_rec.attribute7
1501 ,p_ATTRIBUTE8 => l_header_rec.attribute8
1502 ,p_ATTRIBUTE9 => l_header_rec.attribute9
1503 ,p_ATTRIBUTE10 => l_header_rec.attribute10
1504 ,p_ATTRIBUTE11 => l_header_rec.attribute11
1505 ,p_ATTRIBUTE12 => l_header_rec.attribute12
1506 ,p_ATTRIBUTE13 => l_header_rec.attribute13
1507 ,p_ATTRIBUTE14 => l_header_rec.attribute14
1508 ,p_ATTRIBUTE15 => l_header_rec.attribute15
1509 -- ,p_SECURITY_GROUP_ID => l_opp_security_group_id
1510 );
1511 END IF;
1512
1513 CLOSE C_Lead_Link_Exists;
1514
1515 Create_Lead_Ctx(
1516 P_SALES_LEAD_ID,
1517 P_OPPORTUNITY_ID,
1518 X_Return_Status
1519 );
1520
1521 --
1522 -- END of API body
1523 --
1524
1525 -- Standard check for p_commit
1526 IF FND_API.to_Boolean( p_commit )
1527 THEN
1528 COMMIT WORK;
1529 END IF;
1530
1531
1532 -- Debug Message
1533 IF l_debug THEN
1534 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'PVT: ' || l_api_name || ' End');
1535 END IF;
1536
1537 -- Standard call to get message count and IF count is 1, get message info.
1538 FND_MSG_PUB.Count_And_Get
1539 ( p_count => x_msg_count,
1540 p_data => x_msg_data );
1541
1542 EXCEPTION
1543 WHEN FND_API.G_EXC_ERROR THEN
1544 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1545 P_MODULE => l_module
1546 ,P_API_NAME => L_API_NAME
1547 ,P_PKG_NAME => G_PKG_NAME
1548 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1549 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1550 ,X_MSG_COUNT => X_MSG_COUNT
1551 ,X_MSG_DATA => X_MSG_DATA
1552 ,X_RETURN_STATUS => X_RETURN_STATUS);
1553
1554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1555 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1556 P_MODULE => l_module
1557 ,P_API_NAME => L_API_NAME
1558 ,P_PKG_NAME => G_PKG_NAME
1559 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1560 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1561 ,X_MSG_COUNT => X_MSG_COUNT
1562 ,X_MSG_DATA => X_MSG_DATA
1563 ,X_RETURN_STATUS => X_RETURN_STATUS);
1564
1565 WHEN OTHERS THEN
1566 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1567 P_MODULE => l_module
1568 ,P_API_NAME => L_API_NAME
1569 ,P_PKG_NAME => G_PKG_NAME
1570 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1571 ,P_SQLCODE => SQLCODE
1572 ,P_SQLERRM => SQLERRM
1573 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1574 ,X_MSG_COUNT => X_MSG_COUNT
1575 ,X_MSG_DATA => X_MSG_DATA
1576 ,X_RETURN_STATUS => X_RETURN_STATUS);
1577 END Copy_Lead_To_Opportunity;
1578
1579 -- API Name: Link_Lead_To_Opportunity
1580 /* API added by Francis on 06/26/2001 */
1581
1582 PROCEDURE Link_Lead_To_Opportunity(
1583 P_Api_Version_Number IN NUMBER,
1584 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1585 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
1586 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1587 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1588 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
1589 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
1590 P_identity_salesforce_id IN NUMBER, --:= NULL,
1591 P_identity_salesgroup_id IN NUMBER := FND_API.G_MISS_NUM,
1592 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
1593 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
1594 P_SALES_LEAD_ID IN NUMBER,
1595 P_OPPORTUNITY_ID IN NUMBER,
1596 X_Return_Status OUT NOCOPY VARCHAR2,
1597 X_Msg_Count OUT NOCOPY NUMBER,
1598 X_Msg_Data OUT NOCOPY VARCHAR2
1599 )
1600 IS
1601
1602 --NULL;
1603
1604 CURSOR C_lead_link_Exists (X_Sales_Lead_Id NUMBER, X_Opportunity_Id NUMBER) IS
1605 SELECT 'X'
1606 FROM as_sales_lead_opportunity
1607 WHERE sales_lead_id = X_Sales_Lead_Id
1608 AND opportunity_id = X_Opportunity_Id;
1609
1610
1611 CURSOR C_Sales_Owner_Check (X_Identity_Salesforce_Id NUMBER,
1612 X_Opportunity_Id NUMBER,
1613 X_Team_Leader_Flag VARCHAR2) IS
1614 SELECT salesforce_id
1615 FROM as_accesses_all
1616 WHERE salesforce_id = X_Identity_Salesforce_Id
1617 AND lead_id = X_Opportunity_Id
1618 AND team_leader_flag = X_Team_Leader_Flag;
1619
1620 CURSOR C_Get_Sales_Owner (X_Opportunity_Id NUMBER) IS
1621 SELECT salesforce_id
1622 FROM as_accesses_all
1623 WHERE lead_id = X_Opportunity_Id
1624 AND team_leader_flag = 'Y';
1625
1626
1627 CURSOR C_Get_Sales_Lead (X_Sales_Lead_Id NUMBER) IS
1628 SELECT sales_lead_id
1629 ,last_update_date
1630 ,last_updated_by
1631 ,creation_date
1632 ,created_by
1633 ,last_update_login
1634 ,request_id
1635 ,program_application_id
1636 ,program_id
1637 ,program_update_date
1638 ,lead_number
1639 ,status_code
1640 ,customer_id
1641 ,address_id
1642 ,source_promotion_id
1643 ,initiating_contact_id
1644 ,orig_system_reference
1645 ,contact_role_code
1646 ,channel_code
1647 ,budget_amount
1648 ,currency_code
1649 ,decision_timeframe_code
1650 ,close_reason
1651 ,lead_rank_code
1652 ,parent_project
1653 ,description
1654 ,attribute_category
1655 ,attribute1
1656 ,attribute2
1657 ,attribute3
1658 ,attribute4
1659 ,attribute5
1660 ,attribute6
1661 ,attribute7
1662 ,attribute8
1663 ,attribute9
1664 ,attribute10
1665 ,attribute11
1666 ,attribute12
1667 ,attribute13
1668 ,attribute14
1669 ,attribute15
1670 ,assign_to_person_id
1671 ,assign_to_salesforce_id
1672 ,budget_status_code
1673 ,assign_date
1674 ,accept_flag
1675 ,vehicle_response_code
1676 ,total_score
1677 ,scorecard_id
1678 ,keep_flag
1679 ,urgent_flag
1680 ,import_flag
1681 ,reject_reason_code
1682 ,lead_rank_id
1683 ,deleted_flag
1684 ,assign_sales_group_id
1685 ,offer_id
1686 -- ,security_group_id
1687 ,incumbent_partner_party_id
1688 ,incumbent_partner_resource_id
1689 FROM as_sales_leads
1690 WHERE sales_lead_id = X_Sales_Lead_Id;
1691
1692 CURSOR C_Get_Opportunity (x_Opportunity_Id NUMBER) IS
1693 SELECT last_update_date
1694 ,last_updated_by
1695 ,creation_Date
1696 ,created_by
1697 ,last_update_login
1698 ,lead_id
1699 ,lead_number
1700 ,orig_system_reference
1701 ,lead_source_code
1702 ,description
1703 ,source_promotion_id
1704 ,customer_id
1705 ,address_id
1706 ,sales_stage_id
1707 ,win_probability
1708 ,status status_code
1709 -- ,initiating_contact_id
1710 -- ,rank
1711 ,channel_code
1712 ,decision_date
1713 ,currency_code
1714 ,price_list_id
1715 ,close_reason close_reason_code
1716 -- ,close_competitor_code
1717 ,close_competitor_id
1718 ,close_competitor
1719 ,close_comment
1720 ,end_user_customer_id
1721 ,end_user_customer_name
1722 ,end_user_address_id
1723 ,total_amount
1724 ,attribute_category
1725 ,attribute1
1726 ,attribute2
1727 ,attribute3
1728 ,attribute4
1729 ,attribute5
1730 ,attribute6
1731 ,attribute7
1732 ,attribute8
1733 ,attribute9
1734 ,attribute10
1735 ,attribute11
1736 ,attribute12
1737 ,attribute13
1738 ,attribute14
1739 ,attribute15
1740 ,parent_project
1741 -- ,FND_API.G_MISS_NUM -- ,security_group_id
1742 From AS_LEADS_ALL
1743 Where lead_id = X_Opportunity_Id;
1744
1745 l_api_name CONSTANT VARCHAR2(30) := 'Link_Lead_To_Opportunity';
1746 l_api_version_number CONSTANT NUMBER := 2.0;
1747 l_Identity_Sales_Member_Rec AS_SALES_MEMBER_PUB.Sales_Member_Rec_Type;
1748 l_Sales_Lead_Rec AS_SALES_LEADS_PUB.Sales_Lead_Rec_Type;
1749 -- l_sales_lead_line_tbl AS_SALES_LEADS_PUB.Sales_Lead_Line_Tbl_Type;
1750 l_header_rec AS_OPPORTUNITY_PUB.Header_Rec_Type;
1751 -- l_line_tbl AS_OPPORTUNITY_PUB.Line_Tbl_Type;
1752 -- l_line_out_tbl AS_OPPORTUNITY_PUB.Line_Out_Tbl_Type;
1753 -- l_contact_tbl AS_OPPORTUNITY_PUB.Contact_Tbl_Type;
1754 -- l_contact_out_tbl AS_OPPORTUNITY_PUB.Contact_Out_Tbl_Type;
1755 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
1756 l_val VARCHAR2(1) default null;
1757 --axavier l_val_id NUMBER;
1758 -- l_lead_line_id NUMBER;
1759 l_last_update_date DATE := FND_API.G_MISS_DATE;
1760 l_channel_code VARCHAR2(30);
1761 l_Lead_Opportunity_Id NUMBER;
1762 -- l_Lead_Opp_Line_Id NUMBER;
1763 -- l_sales_lead_line_id NUMBER default null;
1764 l_update_access_flag VARCHAR2(1);
1765 l_member_role VARCHAR2(5);
1766 l_member_access VARCHAR2(5);
1767 -- l_cnt NUMBER := 0;
1768 l_debug BOOLEAN;
1769 l_module CONSTANT VARCHAR2(255) := 'as.plsql.slopv.Link_Lead_To_Opportunity';
1770
1771 BEGIN
1772 -- Standard Start of API savepoint
1773 SAVEPOINT LINK_LEAD_TO_OPPORTUNITY_PVT;
1774 -- Standard call to check for call compatibility.
1775 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1776 p_api_version_number,
1777 l_api_name,
1778 G_PKG_NAME)
1779 THEN
1780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1781 END IF;
1782 l_debug := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1783
1784 -- Initialize message list IF p_init_msg_list is set to TRUE.
1785 IF FND_API.to_Boolean( p_init_msg_list )
1786 THEN
1787 FND_MSG_PUB.initialize;
1788 END IF;
1789
1790 -- Debug Message
1791 IF l_debug THEN
1792 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'PVT:' || l_api_name || ' Start');
1793 END IF;
1794
1795 -- Initialize API return status to SUCCESS
1796 x_return_status := FND_API.G_RET_STS_SUCCESS;
1797 --
1798 -- Api body
1799 --
1800
1801 -- ******************************************************************
1802 -- Validate Environment
1803 -- ******************************************************************
1804
1805
1806 IF FND_GLOBAL.User_Id IS NULL
1807 THEN
1808 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1809 THEN
1810 AS_UTILITY_PVT.Set_Message(
1811 p_module => l_module,
1812 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1813 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
1814 p_token1 => 'PROFILE',
1815 p_token1_value => 'USER_ID');
1816 END IF;
1817 RAISE FND_API.G_EXC_ERROR;
1818 END IF;
1819
1820 IF (p_validation_level = fnd_api.g_valid_level_full)
1821 THEN
1822 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
1823 p_api_version_number => 2.0
1824 ,p_init_msg_list => p_init_msg_list
1825 ,p_salesforce_id => P_Identity_Salesforce_Id
1826 ,p_admin_group_id => p_admin_group_id
1827 ,x_return_status => x_return_status
1828 ,x_msg_count => x_msg_count
1829 ,x_msg_data => x_msg_data
1830 ,x_sales_member_rec => l_identity_sales_member_rec);
1831 END IF;
1832
1833 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1834 RAISE FND_API.G_EXC_ERROR;
1835 END IF;
1836
1837 -- Get sales lead header based on parameter P_Sales_Lead_Id
1838 OPEN C_Get_Sales_Lead (P_Sales_Lead_Id);
1839 FETCH C_Get_Sales_Lead INTO
1840 l_Sales_Lead_Rec.sales_lead_id
1841 ,l_Sales_Lead_Rec.last_update_date
1842 ,l_Sales_Lead_Rec.last_updated_by
1843 ,l_Sales_Lead_Rec.creation_date
1844 ,l_Sales_Lead_Rec.created_by
1845 ,l_Sales_Lead_Rec.last_update_login
1846 ,l_Sales_Lead_Rec.request_id
1847 ,l_Sales_Lead_Rec.program_application_id
1848 ,l_Sales_Lead_Rec.program_id
1849 ,l_Sales_Lead_Rec.program_update_date
1850 ,l_Sales_Lead_Rec.lead_number
1851 ,l_Sales_Lead_Rec.status_code
1852 ,l_Sales_Lead_Rec.customer_id
1853 ,l_Sales_Lead_Rec.address_id
1854 ,l_Sales_Lead_Rec.source_promotion_id
1855 ,l_Sales_Lead_Rec.initiating_contact_id
1856 ,l_Sales_Lead_Rec.orig_system_reference
1857 ,l_Sales_Lead_Rec.contact_role_code
1858 ,l_Sales_Lead_Rec.channel_code
1859 ,l_Sales_Lead_Rec.budget_amount
1860 ,l_Sales_Lead_Rec.currency_code
1861 ,l_Sales_Lead_Rec.decision_timeframe_code
1862 ,l_Sales_Lead_Rec.close_reason
1863 ,l_Sales_Lead_Rec.lead_rank_code
1864 ,l_Sales_Lead_Rec.parent_project
1865 ,l_Sales_Lead_Rec.description
1866 ,l_Sales_Lead_Rec.attribute_category
1867 ,l_Sales_Lead_Rec.attribute1
1868 ,l_Sales_Lead_Rec.attribute2
1869 ,l_Sales_Lead_Rec.attribute3
1870 ,l_Sales_Lead_Rec.attribute4
1871 ,l_Sales_Lead_Rec.attribute5
1872 ,l_Sales_Lead_Rec.attribute6
1873 ,l_Sales_Lead_Rec.attribute7
1874 ,l_Sales_Lead_Rec.attribute8
1875 ,l_Sales_Lead_Rec.attribute9
1876 ,l_Sales_Lead_Rec.attribute10
1877 ,l_Sales_Lead_Rec.attribute11
1878 ,l_Sales_Lead_Rec.attribute12
1879 ,l_Sales_Lead_Rec.attribute13
1880 ,l_Sales_Lead_Rec.attribute14
1881 ,l_Sales_Lead_Rec.attribute15
1882 ,l_Sales_Lead_Rec.assign_to_person_id
1883 ,l_Sales_Lead_Rec.assign_to_salesforce_id
1884 ,l_Sales_Lead_Rec.budget_status_code
1885 ,l_Sales_Lead_Rec.assign_date
1886 ,l_Sales_Lead_Rec.accept_flag
1887 ,l_Sales_Lead_Rec.vehicle_response_code
1888 ,l_Sales_Lead_Rec.total_score
1889 ,l_Sales_Lead_Rec.scorecard_id
1890 ,l_Sales_Lead_Rec.keep_flag
1891 ,l_Sales_Lead_Rec.urgent_flag
1892 ,l_Sales_Lead_Rec.import_flag
1893 ,l_Sales_Lead_Rec.reject_reason_code
1894 ,l_Sales_Lead_Rec.lead_rank_id
1895 ,l_Sales_Lead_Rec.deleted_flag
1896 ,l_Sales_Lead_Rec.assign_sales_group_id
1897 ,l_Sales_Lead_Rec.offer_id
1898 -- ,l_Sales_Lead_Rec.security_group_id
1899 ,l_Sales_Lead_Rec.incumbent_partner_party_id
1900 ,l_Sales_Lead_Rec.incumbent_partner_resource_id;
1901
1902 IF ( C_Get_Sales_Lead%NOTFOUND) THEN
1903 IF l_debug THEN
1904 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Private API: SALES_LEAD_ID is invalid');
1905 END IF;
1906
1907 AS_UTILITY_PVT.Set_Message(
1908 p_module => l_module,
1909 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1910 p_msg_name => 'API_INVALID_ID',
1911 p_token1 => 'COLUMN',
1912 p_token1_value => 'SALES_LEAD_ID',
1913 p_token2 => 'VALUE',
1914 p_token2_value => P_Sales_Lead_Id );
1915
1916 x_return_status := FND_API.G_RET_STS_ERROR;
1917
1918 END IF;
1919
1920 CLOSE C_Get_Sales_Lead;
1921
1922 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1923 RAISE FND_API.G_EXC_ERROR;
1924 END IF;
1925
1926
1927
1928
1929 -- Get opportunity header based on parameter P_Opportunity_Id
1930 OPEN C_Get_Opportunity (P_Opportunity_Id);
1931 FETCH C_Get_Opportunity INTO
1932 l_header_rec.last_update_date
1933 ,l_header_rec.last_updated_by
1934 ,l_header_rec.creation_Date
1935 ,l_header_rec.created_by
1936 ,l_header_rec.last_update_login
1937 ,l_header_rec.lead_id
1938 ,l_header_rec.lead_number
1939 ,l_header_rec.orig_system_reference
1940 ,l_header_rec.lead_source_code
1941 ,l_header_rec.description
1942 ,l_header_rec.source_promotion_id
1943 ,l_header_rec.customer_id
1944 ,l_header_rec.address_id
1945 ,l_header_rec.sales_stage_id
1946 ,l_header_rec.win_probability
1947 ,l_header_rec.status_code
1948 -- ,l_header_rec.initiating_contact_id
1949 -- ,l_header_rec.rank
1950 ,l_header_rec.channel_code
1951 ,l_header_rec.decision_date
1952 ,l_header_rec.currency_code
1953 ,l_header_rec.price_list_id
1954 ,l_header_rec.close_reason_code
1955 -- ,l_header_rec.close_competitor_code
1956 ,l_header_rec.close_competitor_id
1957 ,l_header_rec.close_competitor
1958 ,l_header_rec.close_comment
1959 ,l_header_rec.end_user_customer_id
1960 ,l_header_rec.end_user_customer_name
1961 ,l_header_rec.end_user_address_id
1962 ,l_header_rec.total_amount
1963 ,l_header_rec.attribute_category
1964 ,l_header_rec.attribute1
1965 ,l_header_rec.attribute2
1966 ,l_header_rec.attribute3
1967 ,l_header_rec.attribute4
1968 ,l_header_rec.attribute5
1969 ,l_header_rec.attribute6
1970 ,l_header_rec.attribute7
1971 ,l_header_rec.attribute8
1972 ,l_header_rec.attribute9
1973 ,l_header_rec.attribute10
1974 ,l_header_rec.attribute11
1975 ,l_header_rec.attribute12
1976 ,l_header_rec.attribute13
1977 ,l_header_rec.attribute14
1978 ,l_header_rec.attribute15
1979 ,l_header_rec.parent_project;
1980 -- ,l_opp_security_group_id;
1981
1982 IF ( C_Get_Opportunity%NOTFOUND) THEN
1983 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1984 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
1985 FND_MESSAGE.Set_Token ('INFO', 'Opportunity', FALSE);
1986 FND_MSG_PUB.Add;
1987 END IF;
1988 -- commented by axavier for bug 2013040 raise FND_API.G_EXC_ERROR;
1989 x_return_status := FND_API.G_RET_STS_ERROR;
1990
1991 END IF;
1992 -- Debug Message
1993 IF l_debug THEN
1994 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Close Cursor C_Get_Opportunity');
1995 END IF;
1996 Close C_Get_Opportunity;
1997
1998 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1999 RAISE FND_API.G_EXC_ERROR;
2000 END IF;
2001
2002 -- Invoke validation procedures
2003 -- Debug message
2004 IF l_debug THEN
2005 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling Validate_sales_lead_id');
2006 END IF;
2007
2008 -- Validate sales lead id whther it is a valid ID.
2009 AS_SALES_LEADS_PVT.Validate_Sales_Lead_Id (
2010 P_Init_Msg_List => FND_API.G_FALSE
2011 ,P_Validation_mode => FND_API.G_MISS_CHAR
2012 ,P_Sales_Lead_Id => P_Sales_Lead_Id
2013 ,X_Return_Status => X_Return_Status
2014 ,X_Msg_Count => X_Msg_Count
2015 ,X_Msg_Data => X_Msg_Data
2016 );
2017
2018 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2019 raise FND_API.G_EXC_ERROR;
2020 END IF;
2021
2022 -- Validate one sales lead can only be link to one opportunity
2023 -- Debug message
2024 IF l_debug THEN
2025 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Validate existing link');
2026 END IF;
2027
2028 OPEN C_Lead_Link_Exists (P_SALES_LEAD_ID , P_OPPORTUNITY_ID);
2029 FETCH C_Lead_Link_Exists into l_val;
2030
2031 IF l_val IS NOT NULL
2032 THEN
2033 -- ffang 020301, we want the error message in every case, don't need
2034 -- to check message level.
2035 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2036 -- THEN
2037 -- ffang 100900: For bug 1448995
2038 -- Use error message #45661 instead of API_INVALID_ID
2039 FND_MESSAGE.Set_Name('AS', 'API_DUPLICATE_LINK');
2040 FND_MESSAGE.Set_Token('SLD_ID', p_sales_lead_id, FALSE);
2041 FND_MESSAGE.Set_Token('OPP_ID', p_opportunity_id, FALSE);
2042 -- end ffang 100900
2043 FND_MSG_PUB.ADD;
2044 -- END IF;
2045 x_return_status := FND_API.G_RET_STS_ERROR;
2046 END IF;
2047
2048 CLOSE C_Lead_Link_Exists;
2049
2050 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2051 raise FND_API.G_EXC_ERROR;
2052 END IF;
2053
2054 l_Sales_Lead_Rec.Status_Code := nvl(FND_PROFILE.Value('AS_LEAD_LINK_STATUS'),'CONVERTED_TO_OPPORTUNITY');
2055 -- has to be changed once the profile problem is solved
2056 --l_Sales_Lead_Rec.Status_Code := 'QUALIFIED';
2057
2058 -- Validate if the sales lead owner is the opportunity team leader
2059 -- Debug message
2060 IF l_debug THEN
2061 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Validate sales lead owner');
2062 END IF;
2063 /*
2064 -- axavier commented this. This check is not required, as the as_sales_leads salesforce_id is not updated
2065
2066 OPEN C_Sales_Owner_Check (l_Sales_Lead_Rec.assign_to_salesforce_id,
2067 P_Opportunity_Id,'Y');
2068 FETCH C_Sales_Owner_Check INTO l_val_id;
2069
2070 IF C_Sales_Owner_Check%NOTFOUND
2071 THEN
2072 OPEN C_Get_Sales_Owner (P_Opportunity_Id);
2073 FETCH C_Get_Sales_Owner INTO l_val_id;
2074
2075 IF C_Get_Sales_Owner%NOTFOUND
2076 THEN
2077 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2078 THEN
2079 -- ffang 100900: For bug 1448995
2080 -- Use error message #45662 instead of API_INVALID_ID
2081 FND_MESSAGE.Set_Name('AS', 'API_INVALID_OPP');
2082 FND_MESSAGE.Set_Token('OPP_ID', P_Opportunity_Id, FALSE);
2083 -- end ffang 100900
2084 FND_MSG_PUB.ADD;
2085 END IF;
2086
2087 x_return_status := FND_API.G_RET_STS_ERROR;
2088 END IF;
2089
2090 CLOSE C_Get_Sales_Owner;
2091
2092 -- ffang 020301, we don't want to update Assign_To_salesforce_id
2093 -- to be the team leader's salesforce_id
2094 -- l_Sales_Lead_Rec.Assign_To_salesforce_id := l_val_id;
2095 END IF;
2096
2097 CLOSE C_Sales_Owner_Check;
2098
2099 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2100 raise FND_API.G_EXC_ERROR;
2101 END IF;
2102 */
2103 l_header_rec.updateable_flag := 'N';
2104 l_header_rec.member_access := FND_API.G_MISS_CHAR;
2105 l_header_rec.member_role := FND_API.G_MISS_CHAR;
2106
2107 -- Update Sales Leads for Status Code and Sales lead owner IF needed
2108 -- ffang 030503, bug 2826512, call PUB instead of PVT
2109 -- AS_SALES_LEADS_PVT.Update_sales_lead(
2110 AS_SALES_LEADS_PUB.Update_sales_lead(
2111 P_Api_Version_Number => l_api_version_number,
2112 P_Init_Msg_List => FND_API.G_FALSE,
2113 P_Commit => FND_API.G_FALSE,
2114 P_Validation_Level => P_Validation_Level,
2115 -- P_Check_Access_Flag => 'Y', -- P_Check_Access_Flag, commented by axavier for oppty
2116 P_Check_Access_Flag => P_Check_Access_Flag,
2117 P_Admin_Flag => P_Admin_Flag,
2118 P_Admin_Group_Id => P_Admin_Group_Id,
2119 P_identity_salesforce_id => P_identity_salesforce_id,
2120 P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
2121 P_SALES_LEAD_Rec => l_Sales_Lead_Rec,
2122 X_Return_Status => X_Return_Status,
2123 X_Msg_Count => X_Msg_Count,
2124 X_Msg_Data => X_Msg_Data
2125 );
2126
2127 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2128 raise FND_API.G_EXC_ERROR;
2129 END IF;
2130
2131 -- ffang 062802, bug 2422212, LEAD_OPPORTUNITY_ID need to be initialized
2132 l_Lead_Opportunity_Id := NULL;
2133 -- end ffang 062802
2134
2135 -- Insert Interaction Data for linking Sales Lead to Opportunity
2136 AS_SALES_LEAD_OPP_PKG.Lead_Opportunity_Insert_Row (
2137 px_LEAD_OPPORTUNITY_ID => l_Lead_Opportunity_Id
2138 ,p_SALES_LEAD_ID => P_Sales_Lead_Id
2139 ,p_OPPORTUNITY_ID => P_Opportunity_Id
2140 ,p_LAST_UPDATE_DATE => SYSDATE
2141 ,p_LAST_UPDATED_BY => FND_GLOBAL.User_Id
2142 ,p_CREATION_DATE => SYSDATE
2143 ,p_CREATED_BY => FND_GLOBAL.User_Id
2144 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
2145 ,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
2146 ,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
2147 ,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
2148 ,p_PROGRAM_UPDATE_DATE => SYSDATE
2149 ,p_ATTRIBUTE_CATEGORY => l_header_rec.attribute_category
2150 ,p_ATTRIBUTE1 => l_header_rec.attribute1
2151 ,p_ATTRIBUTE2 => l_header_rec.attribute2
2152 ,p_ATTRIBUTE3 => l_header_rec.attribute3
2153 ,p_ATTRIBUTE4 => l_header_rec.attribute4
2154 ,p_ATTRIBUTE5 => l_header_rec.attribute5
2155 ,p_ATTRIBUTE6 => l_header_rec.attribute6
2156 ,p_ATTRIBUTE7 => l_header_rec.attribute7
2157 ,p_ATTRIBUTE8 => l_header_rec.attribute8
2158 ,p_ATTRIBUTE9 => l_header_rec.attribute9
2159 ,p_ATTRIBUTE10 => l_header_rec.attribute10
2160 ,p_ATTRIBUTE11 => l_header_rec.attribute11
2161 ,p_ATTRIBUTE12 => l_header_rec.attribute12
2162 ,p_ATTRIBUTE13 => l_header_rec.attribute13
2163 ,p_ATTRIBUTE14 => l_header_rec.attribute14
2164 ,p_ATTRIBUTE15 => l_header_rec.attribute15
2165 -- ,p_SECURITY_GROUP_ID => l_opp_security_group_id
2166 );
2167
2168
2169
2170
2171 Create_Lead_Ctx(
2172 P_SALES_LEAD_ID,
2173 P_OPPORTUNITY_ID,
2174 X_Return_Status
2175 );
2176 --
2177 -- END of API body
2178 --
2179
2180 -- Standard check for p_commit
2181 IF FND_API.to_Boolean( p_commit )
2182 THEN
2183 COMMIT WORK;
2184 END IF;
2185
2186
2187 -- Debug Message
2188 IF l_debug THEN
2189 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'PVT: ' || l_api_name || ' End');
2190 END IF;
2191
2192 -- Standard call to get message count and IF count is 1, get message info.
2193 FND_MSG_PUB.Count_And_Get
2194 ( p_count => x_msg_count,
2195 p_data => x_msg_data );
2196
2197 EXCEPTION
2198 WHEN FND_API.G_EXC_ERROR THEN
2199 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2200 P_MODULE => l_module
2201 ,P_API_NAME => L_API_NAME
2202 ,P_PKG_NAME => G_PKG_NAME
2203 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2204 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2205 ,X_MSG_COUNT => X_MSG_COUNT
2206 ,X_MSG_DATA => X_MSG_DATA
2207 ,X_RETURN_STATUS => X_RETURN_STATUS);
2208
2209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2210 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2211 P_MODULE => l_module
2212 ,P_API_NAME => L_API_NAME
2213 ,P_PKG_NAME => G_PKG_NAME
2214 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2215 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2216 ,X_MSG_COUNT => X_MSG_COUNT
2217 ,X_MSG_DATA => X_MSG_DATA
2218 ,X_RETURN_STATUS => X_RETURN_STATUS);
2219
2220 WHEN OTHERS THEN
2221 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
2222 P_MODULE => l_module
2223 ,P_API_NAME => L_API_NAME
2224 ,P_PKG_NAME => G_PKG_NAME
2225 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
2226 ,P_SQLCODE => SQLCODE
2227 ,P_SQLERRM => SQLERRM
2228 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
2229 ,X_MSG_COUNT => X_MSG_COUNT
2230 ,X_MSG_DATA => X_MSG_DATA
2231 ,X_RETURN_STATUS => X_RETURN_STATUS);
2232
2233 END Link_Lead_To_Opportunity;
2234
2235
2236 -- API Name: Create_Opportunity_For_Lead
2237
2238 PROCEDURE Create_Opportunity_For_Lead(
2239 P_Api_Version_Number IN NUMBER,
2240 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
2241 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
2242 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2243 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
2244 P_Admin_Flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
2245 P_Admin_Group_Id IN NUMBER := FND_API.G_MISS_NUM,
2246 P_identity_salesforce_id IN NUMBER := FND_API.G_MISS_NUM,
2247 P_identity_salesgroup_id IN NUMBER := FND_API.G_MISS_NUM,
2248 P_Sales_Lead_Profile_Tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
2249 := AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
2250 P_SALES_LEAD_ID IN NUMBER,
2251 P_OPP_STATUS IN VARCHAR2 := FND_API.G_MISS_CHAR,
2252 X_Return_Status OUT NOCOPY VARCHAR2,
2253 X_Msg_Count OUT NOCOPY NUMBER,
2254 X_Msg_Data OUT NOCOPY VARCHAR2,
2255 X_OPPORTUNITY_ID OUT NOCOPY NUMBER
2256 )
2257 IS
2258 CURSOR C_Get_Sales_Lead (X_Sales_Lead_Id NUMBER) IS
2259 SELECT sales_lead_id
2260 ,last_update_date
2261 ,last_updated_by
2262 ,creation_date
2263 ,created_by
2264 ,last_update_login
2265 ,request_id
2266 ,program_application_id
2267 ,program_id
2268 ,program_update_date
2269 ,lead_number
2270 ,status_code
2271 ,customer_id
2272 ,address_id
2273 ,source_promotion_id
2274 ,initiating_contact_id
2275 ,orig_system_reference
2276 ,contact_role_code
2277 ,channel_code
2278 ,budget_amount
2279 ,currency_code
2280 ,decision_timeframe_code
2281 ,close_reason
2282 ,lead_rank_id
2283 ,lead_rank_code
2284 ,parent_project
2285 ,description
2286 ,attribute_category
2287 ,attribute1
2288 ,attribute2
2289 ,attribute3
2290 ,attribute4
2291 ,attribute5
2292 ,attribute6
2293 ,attribute7
2294 ,attribute8
2295 ,attribute9
2296 ,attribute10
2297 ,attribute11
2298 ,attribute12
2299 ,attribute13
2300 ,attribute14
2301 ,attribute15
2302 ,assign_to_person_id
2303 ,assign_to_salesforce_id
2304 ,assign_sales_group_id
2305 ,assign_date
2306 ,budget_status_code
2307 ,accept_flag
2308 ,vehicle_response_code
2309 ,total_score
2310 ,scorecard_id
2311 ,keep_flag
2312 ,urgent_flag
2313 ,import_flag
2314 ,reject_reason_code
2315 ,deleted_flag
2316 ,offer_id
2317 -- ,security_group_id
2318 ,incumbent_partner_party_id
2319 ,incumbent_partner_resource_id
2320 ,prm_exec_sponsor_flag
2321 ,prm_prj_lead_in_place_flag
2322 ,prm_sales_lead_type
2323 ,prm_ind_classification_code
2324 -- the following 2 fields added for bug#3613374
2325 ,sales_methodology_id
2326 ,sales_stage_id
2327 FROM as_sales_leads
2328 WHERE sales_lead_id = X_Sales_Lead_Id;
2329
2330 CURSOR C_Get_Sales_lead_lines (X_Sales_Lead_Id NUMBER) IS
2331 SELECT sales_lead_line_id
2332 ,last_update_date
2333 ,last_updated_by
2334 ,creation_date
2335 ,created_by
2336 ,last_update_login
2337 ,status_code
2338 ,interest_type_id
2339 ,primary_interest_code_id
2340 ,secondary_interest_code_id
2341 ,inventory_item_id
2342 ,organization_id
2343 ,uom_code
2344 ,quantity
2345 ,budget_amount
2346 ,source_promotion_id
2347 ,attribute_category
2348 ,attribute1
2349 ,attribute2
2350 ,attribute3
2351 ,attribute4
2352 ,attribute5
2353 ,attribute6
2354 ,attribute7
2355 ,attribute8
2356 ,attribute9
2357 ,attribute10
2358 ,attribute11
2359 ,attribute12
2360 ,attribute13
2361 ,attribute14
2362 ,attribute15
2363 ,offer_id
2364 ,security_group_id
2365 ,category_id
2366 ,category_set_id
2367 FROM as_sales_lead_lines
2368 WHERE sales_lead_id = X_Sales_Lead_Id;
2369
2370 -- 102700 FFANG for bug 1478517, get sales lead contacts information
2371 CURSOR C_Get_Sales_Lead_Contacts(c_sales_lead_id number) IS
2372 SELECT contact_id
2373 ,contact_party_id
2374 ,last_update_date
2375 ,last_updated_by
2376 ,creation_Date
2377 ,created_by
2378 ,last_update_login
2379 ,enabled_flag
2380 ,rank
2381 ,customer_id
2382 ,address_id
2383 ,phone_id
2384 ,contact_role_code
2385 ,primary_contact_flag
2386 ,attribute_category
2387 ,attribute1
2388 ,attribute2
2389 ,attribute3
2390 ,attribute4
2391 ,attribute5
2392 ,attribute6
2393 ,attribute7
2394 ,attribute8
2395 ,attribute9
2396 ,attribute10
2397 ,attribute11
2398 ,attribute12
2399 ,attribute13
2400 ,attribute14
2401 ,attribute15
2402 -- ,security_group_id
2403 FROM as_sales_lead_contacts
2404 WHERE sales_lead_id = c_sales_lead_id;
2405 -- end 102700 FFANG
2406
2407 CURSOR C_Get_Opportunity(X_Lead_Id NUMBER) IS
2408 SELECT last_update_date
2409 FROM as_leads_all
2410 WHERE lead_id = X_Lead_Id;
2411
2412 -- ffang 020301, for checking if the linking has already existed.
2413 /*
2414 CURSOR C_lead_link_Exists (X_Sales_Lead_Id NUMBER) IS
2415 SELECT 'X'
2416 FROM as_sales_lead_opportunity
2417 WHERE sales_lead_id = X_Sales_Lead_Id ;
2418 */
2419
2420 -- ffang 051602, bug 2278318, copy lead's sales team to oppty
2421 CURSOR c_get_lead_salesteam (c_sl_id NUMBER) IS
2422 SELECT freeze_flag, reassign_flag, team_leader_flag,
2423 customer_id, address_id, salesforce_id, person_id,
2424 partner_customer_id, sales_group_id, partner_address_id,
2425 created_person_id, freeze_date, reassign_reason,
2426 salesforce_role_code, salesforce_relationship_code,
2427 attribute_category, attribute1, attribute2, attribute3,
2428 attribute4, attribute5, attribute6, attribute7, attribute8,
2429 attribute9, attribute10, attribute11, attribute12, attribute13,
2430 attribute14, attribute15, reassign_request_date,
2431 reassign_requested_person_id, security_group_id,
2432 partner_cont_party_id, created_by_tap_flag, prm_keep_flag
2433 -- ffang 062502, bug 2432561
2434 , owner_flag
2435 -- end ffang 062502
2436 FROM as_accesses_all
2437 WHERE sales_lead_id = c_sl_id;
2438 --Code added for Enhancement convert lead to opportunity - Attachment
2439 --Enhancement related Bug#3913225 @ @ Callling the procedure Copy_Attachments--changes for ASN.B--Start--
2440 CURSOR C_Attachment_Exists(X_Sales_Lead_Id VARCHAR2) IS
2441 SELECT 'X'
2442 FROM fnd_attached_documents
2443 WHERE pk1_value=X_Sales_Lead_Id;
2444 l_val_exists VARCHAR2(1) default null;
2445 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
2446 --Enhancement related Bug#3913225 @ @ Changes for ASN.B--End
2447 -- Start Bug#3966128
2448
2449 CURSOR c_address_id(x_partner_party_id NUMBER) is
2450 SELECT hz.party_site_id
2451 FROM PV_PARTNERS_V pv,
2452 hz_party_sites hz
2453 WHERE pv.partner_id = x_partner_party_id
2454 AND hz.party_id =pv.partner_party_id
2455 AND pv.PARTNER_PARTY_NAME IS NOT NULL
2456 AND pv.INTERNAL_FLAG||'' = 'Y'
2457 AND pv.PARTNER_RESOURCE_ID IS NOT NULL
2458 AND pv.INTERNAL_STATUS = 'A'
2459 AND pv.PARTNER_STATUS = 'A'
2460 AND pv.RELATIONSHIP_STATUS = 'A'
2461 AND pv.SALES_PARTNER_FLAG = 'Y'
2462 and hz.identifying_address_flag = 'Y'
2463 and hz.status = 'A';
2464
2465 l_address_id NUMBER;
2466 l_partner_party_id NUMBER;
2467 l_psalesteam_rec as_access_pub.sales_team_rec_type:=as_api_records_pkg.get_p_sales_team_rec;
2468 -- End Bug#3966128
2469
2470 l_sales_team_rec AS_ACCESS_PUB.SALES_TEAM_REC_TYPE;
2471 l_proceed_st_flag VARCHAR2(1) := 'Y';
2472 l_access_id NUMBER;
2473 -- end ffang 051602, bug 2278318
2474
2475 l_api_name CONSTANT VARCHAR2(30) := 'Create_Opp_For_Lead';
2476 l_api_version_number CONSTANT NUMBER := 2.0;
2477 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
2478 l_sales_lead_rec AS_SALES_LEADS_PUB.Sales_Lead_Rec_Type;
2479 -- ffang 020301
2480 l_sl_rec AS_SALES_LEADS_PUB.Sales_Lead_Rec_Type
2481 := AS_SALES_LEADS_PUB.G_MISS_SALES_LEAD_REC;
2482 l_val VARCHAR2(1) default null;
2483 -- end ffang 020301
2484 l_sales_lead_line_tbl AS_SALES_LEADS_PUB.Sales_Lead_Line_Tbl_Type;
2485 l_header_rec AS_OPPORTUNITY_PUB.Header_Rec_Type;
2486 l_line_tbl AS_OPPORTUNITY_PUB.Line_Tbl_Type;
2487 l_line_out_tbl AS_OPPORTUNITY_PUB.Line_Out_Tbl_Type;
2488 l_contact_tbl AS_OPPORTUNITY_PUB.Contact_Tbl_Type;
2489 l_contact_out_tbl AS_OPPORTUNITY_PUB.Contact_Out_Tbl_Type;
2490 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
2491 l_lead_line_id NUMBER;
2492 l_lead_id NUMBER;
2493 l_last_update_date DATE;
2494 l_cnt NUMBER;
2495 l_lead_opp_line_id NUMBER;
2496 l_sales_lead_line_id NUMBER default null;
2497 -- l_line_security_group_id NUMBER;
2498 -- l_opp_security_group_id NUMBER;
2499 l_update_access_flag VARCHAR2(1);
2500 l_member_role VARCHAR2(5);
2501 l_member_access VARCHAR2(5);
2502
2503 -- ffang 071202
2504 l_Lead_Opportunity_Id NUMBER;
2505 -- end ffang 071202
2506 l_debug BOOLEAN;
2507 l_debug_high BOOLEAN;
2508 l_debug_err BOOLEAN;
2509 -- Begin Added for Bug#3613374
2510 l_sales_lead_methodology_id NUMBER;
2511 l_sales_methodology_id NUMBER;
2512 l_sales_lead_stage_id NUMBER;
2513 l_sales_stage_id NUMBER;
2514 -- End Added for Bug#3613374
2515 l_default_org_id number;
2516 l_default_ou_name varchar2(240);
2517 l_ou_count number;
2518 l_module CONSTANT VARCHAR2(255) := 'as.plsql.slopv.Create_Opportunity_For_Lead';
2519 BEGIN
2520 -- Standard Start of API savepoint
2521 SAVEPOINT CREATE_OPP_FOR_LEAD_PVT;
2522
2523 -- Standard call to check for call compatibility.
2524 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2525 p_api_version_number,
2526 l_api_name,
2527 G_PKG_NAME)
2528 THEN
2529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2530 END IF;
2531 l_debug := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
2532 l_debug_high := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
2533 l_debug_err := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR);
2534
2535 -- Initialize message list IF p_init_msg_list is set to TRUE.
2536 IF FND_API.to_Boolean( p_init_msg_list )
2537 THEN
2538 FND_MSG_PUB.initialize;
2539 END IF;
2540
2541 -- Debug Message
2542 IF l_debug THEN
2543 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'PVT:' || l_api_name || 'start');
2544 END IF;
2545
2546
2547
2548 -- Initialize API return status to SUCCESS
2549 x_return_status := FND_API.G_RET_STS_SUCCESS;
2550
2551 --
2552 -- Api body
2553 --
2554 -- ******************************************************************
2555 -- Validate Environment
2556 -- ******************************************************************
2557 IF FND_GLOBAL.User_Id IS NULL
2558 THEN
2559 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
2560 THEN
2561 AS_UTILITY_PVT.Set_Message(
2562 p_module => l_module,
2563 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2564 p_msg_name => 'UT_CANNOT_GET_PROFILE_VALUE',
2565 p_token1 => 'PROFILE',
2566 p_token1_value => 'USER_ID');
2567 END IF;
2568 RAISE FND_API.G_EXC_ERROR;
2569 END IF;
2570
2571 IF (p_validation_level = fnd_api.g_valid_level_full)
2572 THEN
2573 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
2574 p_api_version_number => 2.0
2575 ,p_init_msg_list => p_init_msg_list
2576 ,p_salesforce_id => P_Identity_Salesforce_Id
2577 ,p_admin_group_id => p_admin_group_id
2578 ,x_return_status => x_return_status
2579 ,x_msg_count => x_msg_count
2580 ,x_msg_data => x_msg_data
2581 ,x_sales_member_rec => l_identity_sales_member_rec);
2582 END IF;
2583
2584 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2585 RAISE FND_API.G_EXC_ERROR;
2586 END IF;
2587
2588 IF(P_Check_Access_Flag = 'Y') THEN
2589 -- Call Get_Access_Profiles to get access_profile_rec
2590 IF l_debug THEN
2591 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling Get_Access_Profiles');
2592 END IF;
2593
2594 AS_SALES_LEADS_PUB.Get_Access_Profiles(
2595 p_profile_tbl => p_sales_lead_profile_tbl,
2596 x_access_profile_rec => l_access_profile_rec);
2597
2598 IF l_debug THEN
2599 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling Has_updateLeadAccess');
2600 END IF;
2601
2602 AS_ACCESS_PUB.Has_updateLeadAccess(
2603 p_api_version_number => 2.0
2604 ,p_init_msg_list => FND_API.G_FALSE
2605 ,p_validation_level => p_validation_level
2606 ,p_access_profile_rec => l_access_profile_rec
2607 ,p_admin_flag => p_admin_flag
2608 ,p_admin_group_id => p_admin_group_id
2609 ,p_person_id => l_identity_sales_member_rec.employee_person_id
2610 ,p_sales_lead_id => p_sales_lead_id
2611 ,p_check_access_flag => 'Y'
2612 ,p_identity_salesforce_id => p_identity_salesforce_id
2613 ,p_partner_cont_party_id => NULL
2614 ,x_return_status => x_return_status
2615 ,x_msg_count => x_msg_count
2616 ,x_msg_data => x_msg_data
2617 ,x_update_access_flag => l_update_access_flag);
2618
2619 IF l_update_access_flag <> 'Y' THEN
2620 IF l_debug_err THEN
2621 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'API_NO_CREATE_PRIVILEGE');
2622 END IF;
2623 RAISE FND_API.G_EXC_ERROR;
2624 END IF;
2625
2626 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2627 RAISE FND_API.G_EXC_ERROR;
2628 END IF;
2629
2630 END IF;
2631
2632 -- ffang 020301, validate one sales lead can only create/link one
2633 -- opportunity
2634 IF l_debug THEN
2635 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Validate existing link');
2636 END IF;
2637
2638 /*
2639 OPEN C_Lead_Link_Exists (P_SALES_LEAD_ID);
2640 FETCH C_Lead_Link_Exists into l_val;
2641
2642 IF l_val IS NOT NULL
2643 THEN
2644 FND_MESSAGE.Set_Name('AS', 'API_DUPLICATE_LINK');
2645 FND_MESSAGE.Set_Token('SLD_ID', p_sales_lead_id, FALSE);
2646 FND_MSG_PUB.ADD;
2647 x_return_status := FND_API.G_RET_STS_ERROR;
2648 END IF;
2649
2650 CLOSE C_Lead_Link_Exists;
2651 */
2652 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2653 RAISE FND_API.G_EXC_ERROR;
2654 END IF;
2655 -- Get sales lead header based on parameter P_Sales_Lead_Id
2656 OPEN C_Get_Sales_Lead (P_Sales_Lead_Id);
2657 FETCH C_Get_Sales_Lead INTO
2658 l_Sales_Lead_Rec.sales_lead_id
2659 ,l_Sales_Lead_Rec.last_update_date
2660 ,l_Sales_Lead_Rec.last_updated_by
2661 ,l_Sales_Lead_Rec.creation_date
2662 ,l_Sales_Lead_Rec.created_by
2663 ,l_Sales_Lead_Rec.last_update_login
2664 ,l_Sales_Lead_Rec.request_id
2665 ,l_Sales_Lead_Rec.program_application_id
2666 ,l_Sales_Lead_Rec.program_id
2667 ,l_Sales_Lead_Rec.program_update_date
2668 ,l_Sales_Lead_Rec.lead_number
2669 ,l_Sales_Lead_Rec.status_code
2670 ,l_Sales_Lead_Rec.customer_id
2671 ,l_Sales_Lead_Rec.address_id
2672 ,l_Sales_Lead_Rec.source_promotion_id
2673 ,l_Sales_Lead_Rec.initiating_contact_id
2674 ,l_Sales_Lead_Rec.orig_system_reference
2675 ,l_Sales_Lead_Rec.contact_role_code
2676 ,l_Sales_Lead_Rec.channel_code
2677 ,l_Sales_Lead_Rec.budget_amount
2678 ,l_Sales_Lead_Rec.currency_code
2679 ,l_Sales_Lead_Rec.decision_timeframe_code
2680 ,l_Sales_Lead_Rec.close_reason
2681 ,l_Sales_Lead_Rec.lead_rank_id
2682 ,l_Sales_Lead_Rec.lead_rank_code
2683 ,l_Sales_Lead_Rec.parent_project
2684 ,l_Sales_Lead_Rec.description
2685 ,l_Sales_Lead_Rec.attribute_category
2686 ,l_Sales_Lead_Rec.attribute1
2687 ,l_Sales_Lead_Rec.attribute2
2688 ,l_Sales_Lead_Rec.attribute3
2689 ,l_Sales_Lead_Rec.attribute4
2690 ,l_Sales_Lead_Rec.attribute5
2691 ,l_Sales_Lead_Rec.attribute6
2692 ,l_Sales_Lead_Rec.attribute7
2693 ,l_Sales_Lead_Rec.attribute8
2694 ,l_Sales_Lead_Rec.attribute9
2695 ,l_Sales_Lead_Rec.attribute10
2696 ,l_Sales_Lead_Rec.attribute11
2697 ,l_Sales_Lead_Rec.attribute12
2698 ,l_Sales_Lead_Rec.attribute13
2699 ,l_Sales_Lead_Rec.attribute14
2700 ,l_Sales_Lead_Rec.attribute15
2701 ,l_Sales_Lead_Rec.assign_to_person_id
2702 ,l_Sales_Lead_Rec.assign_to_salesforce_id
2703 ,l_Sales_Lead_Rec.assign_sales_group_id
2704 ,l_Sales_Lead_Rec.assign_date
2705 ,l_Sales_Lead_Rec.budget_status_code
2706 ,l_Sales_Lead_Rec.accept_flag
2707 ,l_Sales_Lead_Rec.vehicle_response_code
2708 ,l_Sales_Lead_Rec.total_score
2709 ,l_Sales_Lead_Rec.scorecard_id
2710 ,l_Sales_Lead_Rec.keep_flag
2711 ,l_Sales_Lead_Rec.urgent_flag
2712 ,l_Sales_Lead_Rec.import_flag
2713 ,l_Sales_Lead_Rec.reject_reason_code
2714 ,l_Sales_Lead_Rec.deleted_flag
2715 ,l_Sales_Lead_Rec.offer_id
2716 -- ,l_Sales_Lead_Rec.security_group_id
2717 ,l_Sales_Lead_Rec.incumbent_partner_party_id
2718 ,l_Sales_Lead_Rec.incumbent_partner_resource_id
2719 ,l_Sales_Lead_Rec.prm_exec_sponsor_flag
2720 ,l_Sales_Lead_Rec.prm_prj_lead_in_place_flag
2721 ,l_Sales_Lead_Rec.prm_sales_lead_type
2722 ,l_Sales_Lead_Rec.prm_ind_classification_code
2723 -- 2 fields added for Bug#3613374
2724 ,l_sales_lead_methodology_id
2725 ,l_sales_lead_stage_id;
2726
2727
2728 IF ( C_Get_Sales_Lead%NOTFOUND) THEN
2729 IF l_debug THEN
2730 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Private API: SALES_LEAD_ID is invalid');
2731 END IF;
2732
2733 AS_UTILITY_PVT.Set_Message(
2734 p_module => l_module,
2735 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
2736 p_msg_name => 'API_INVALID_ID',
2737 p_token1 => 'COLUMN',
2738 p_token1_value => 'SALES_LEAD_ID',
2739 p_token2 => 'VALUE',
2740 p_token2_value => P_Sales_Lead_Id );
2741
2742 x_return_status := FND_API.G_RET_STS_ERROR;
2743
2744 END IF;
2745
2746 CLOSE C_Get_Sales_Lead;
2747
2748 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2749 RAISE FND_API.G_EXC_ERROR;
2750 END IF;
2751
2752 -- Begin Added for Bug#3613374
2753 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y' THEN
2754 CHECK_SALES_STAGE(
2755 p_api_version_number => 2.0
2756 ,p_init_msg_list => FND_API.G_FALSE
2757 ,p_validation_level => p_validation_level
2758 ,p_sales_lead_id =>P_Sales_Lead_Id
2759 ,P_sales_lead_stage_id =>l_sales_lead_stage_id
2760 ,P_sales_lead_methodology_id =>l_sales_lead_methodology_id
2761 ,X_sales_stage_id =>l_sales_stage_id
2762 ,X_sales_methodology_id =>l_sales_methodology_id
2763 ,X_Return_Status =>x_return_status
2764 ,X_Msg_Count =>x_msg_count
2765 ,X_Msg_Data =>x_msg_data);
2766 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2767 RAISE FND_API.G_EXC_ERROR;
2768 ELSE
2769 l_header_rec.Sales_Methodology_Id := l_sales_methodology_id;
2770 l_header_rec.sales_stage_id := l_sales_stage_id;
2771 null;
2772 END IF;
2773 END IF;
2774 -- End Added for Bug#3613374
2775
2776 l_last_update_date := l_Sales_Lead_Rec.last_update_Date;
2777
2778
2779 -- Copy the sales lead information (header level) to opportunity
2780 -- l_header_rec.last_update_date := SYSDATE;
2781 -- l_header_rec.last_updated_by := FND_GLOBAL.USER_ID;
2782 -- l_header_rec.creation_Date := SYSDATE;
2783 -- l_header_rec.created_by := FND_GLOBAL.USER_ID;
2784 -- l_header_rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
2785 -- l_header_rec.lead_number := l_Sales_Lead_Rec.lead_number;
2786
2787 -- ffang 121200 for bug 1529866: opportunity's status should be get from
2788 -- p_opp_status.
2789 -- IF l_Sales_Lead_Rec.prm_sales_lead_type = 'INDIRECT' THEN
2790 -- l_header_rec.status_code := 'UNASSIGNED';
2791 -- ELSE
2792 -- l_header_rec.status_code := 'NEW_OPPORTUNITY';
2793 -- END IF;
2794
2795 l_header_rec.status_code := p_opp_status;
2796 -- end ffang 121200
2797
2798 l_header_rec.customer_id := l_Sales_Lead_Rec.customer_id;
2799 l_header_rec.address_id := l_Sales_Lead_Rec.address_id;
2800 -- l_header_rec.lead_source_code -- Not exist in sales leads Table
2801 l_header_rec.orig_system_reference
2802 := l_Sales_Lead_Rec.orig_system_reference;
2803 -- l_header_rec.sales_stage_id -- Not exist in sales leads
2804 l_header_rec.initiating_contact_id
2805 := l_Sales_Lead_Rec.initiating_contact_id;
2806 l_header_rec.channel_code := l_Sales_Lead_Rec.channel_code;
2807 -- l_header_rec.Total_amount -- will be the sum of lines' total_amount
2808 l_header_rec.currency_code := l_Sales_Lead_Rec.currency_code;
2809 -- l_header_rec.decision_date -- Not exist in sales leads Table
2810 -- l_header_rec.win_probability -- Not exist in sales leads Table
2811 -- 102600 FFANG, pass close_reason to opportunity's close_reason_code
2812 -- l_header_rec.close_reason_code -- Not exist in sales leads Table
2813 l_header_rec.close_reason_code := l_Sales_Lead_Rec.close_reason;
2814 -- end 102600 FFANG
2815 -- l_header_rec.close_competitor_code -- Not exist in sales leads Table
2816 -- l_header_rec.close_competitor_id -- Not exist in sales leads Table
2817 -- l_header_rec.close_competitor -- Not exist in sales leads Table
2818 -- l_header_rec.close_comment -- Not exist in sales leads Table
2819 l_header_rec.rank := l_Sales_Lead_Rec.lead_rank_code;
2820 l_header_rec.description := l_Sales_Lead_Rec.description;
2821 -- l_header_rec.end_user_customer_name -- Not exist in sales leads Table
2822 l_header_rec.source_promotion_id := l_Sales_Lead_Rec.source_promotion_id;
2823 -- l_header_rec.end_user_customer_id -- Not exist in sales leads Table
2824 -- l_header_rec.end_user_address_id -- Not exist in sales leads Table
2825 -- l_header_rec.org_id -- Not exist in sales leads Table
2826 -- l_header_rec.no_opp_allowed_flag -- Not exist in sales leads Table
2827 -- l_header_rec.delete_allowed_flag -- Not exist in sales leads Table
2828 l_header_rec.parent_project := l_Sales_Lead_Rec.parent_project;
2829 -- l_header_rec.price_list_id -- Not exist in sales leads Table
2830 -- l_header_rec.deleted_flag -- Not exist in sales leads Table
2831 -- l_header_rec.auto_assignment_type -- Not exist in sales leads Table
2832 -- l_header_rec.prm_assignment_type -- Not exist in sales leads Table
2833 -- 102600 FFANG for BUG 1478517, pass budget_amount to opportunity's
2834 -- customer_budget instead of total_amount, since create_opp_lines will
2835 -- update total_amount according to lines' total amount
2836 l_header_rec.customer_budget := l_Sales_Lead_Rec.budget_amount;
2837 -- end 102600 FFANG
2838 -- l_header_rec.methodology_code -- Not exist in sales leads Table
2839 -- l_header_rec.original_lead_id -- Not exist in sales leads Table
2840 l_header_rec.decision_timeframe_code
2841 := l_Sales_Lead_Rec.decision_timeframe_code;
2842 l_header_rec.attribute_category := l_Sales_Lead_Rec.attribute_category;
2843 l_header_rec.attribute1 := l_Sales_Lead_Rec.attribute1;
2844 l_header_rec.attribute2 := l_Sales_Lead_Rec.attribute2;
2845 l_header_rec.attribute3 := l_Sales_Lead_Rec.attribute3;
2846 l_header_rec.attribute4 := l_Sales_Lead_Rec.attribute4;
2847 l_header_rec.attribute5 := l_Sales_Lead_Rec.attribute5;
2848 l_header_rec.attribute6 := l_Sales_Lead_Rec.attribute6;
2849 l_header_rec.attribute7 := l_Sales_Lead_Rec.attribute7;
2850 l_header_rec.attribute8 := l_Sales_Lead_Rec.attribute8;
2851 l_header_rec.attribute9 := l_Sales_Lead_Rec.attribute9;
2852 l_header_rec.attribute10 := l_Sales_Lead_Rec.attribute10;
2853 l_header_rec.attribute11 := l_Sales_Lead_Rec.attribute11;
2854 l_header_rec.attribute12 := l_Sales_Lead_Rec.attribute12;
2855 l_header_rec.attribute13 := l_Sales_Lead_Rec.attribute13;
2856 l_header_rec.attribute14 := l_Sales_Lead_Rec.attribute14;
2857 l_header_rec.attribute15 := l_Sales_Lead_Rec.attribute15;
2858 l_header_rec.parent_project := l_Sales_Lead_Rec.parent_project;
2859 -- l_header_rec.security_group_id := l_Sales_Lead_Rec.security_group_id;
2860 l_header_rec.incumbent_partner_resource_id
2861 := l_Sales_Lead_Rec.incumbent_partner_resource_id;
2862 l_header_rec.incumbent_partner_party_id
2863 := l_Sales_Lead_Rec.incumbent_partner_party_id;
2864 l_header_rec.offer_id := l_Sales_Lead_Rec.offer_id;
2865 l_header_rec.vehicle_response_code
2866 := l_Sales_Lead_Rec.vehicle_response_code;
2867 l_header_rec.budget_status_code
2868 := l_Sales_Lead_Rec.budget_status_code;
2869 -- l_header_rec.followup_date -- Not exist in sales leads Table
2870 l_header_rec.prm_exec_sponsor_flag
2871 := l_Sales_Lead_Rec.prm_exec_sponsor_flag;
2872 l_header_rec.prm_prj_lead_in_place_flag
2873 := l_Sales_Lead_Rec.prm_prj_lead_in_place_flag;
2874 l_header_rec.prm_ind_classification_code
2875 := l_Sales_Lead_Rec.prm_ind_classification_code;
2876 l_header_rec.prm_lead_type := l_Sales_Lead_Rec.prm_sales_lead_type;
2877 mo_utils.get_default_ou(l_default_org_id, l_default_ou_name, l_ou_count);
2878 l_header_rec.org_id := l_default_org_id;
2879 IF l_debug THEN
2880 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling AS_OPP_HEADER_PVT.Create_opp_header');
2881 END IF;
2882 IF l_debug_high THEN
2883 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'P_Ident_SF_Id : ' || P_Identity_Salesforce_Id);
2884 END IF;
2885 -- ffang 030503, bug 2826512, call PUB instead of PVT
2886 -- AS_OPP_HEADER_PVT.Create_opp_header (
2887 AS_OPPORTUNITY_PUB.Create_Opp_Header (
2888 p_api_version_number => 2.0,
2889 p_init_msg_list => FND_API.G_FALSE,
2890 p_commit => FND_API.G_FALSE,
2891 p_validation_level => p_validation_level,
2892 P_Check_Access_Flag => 'N', -- 'Y',
2893 -- P_Check_Access_Flag => P_Check_Access_Flag,
2894 P_Admin_Flag => P_Admin_Flag,
2895 P_Admin_Group_Id => P_Admin_Group_Id,
2896 P_Identity_Salesforce_Id => P_identity_Salesforce_id,
2897 P_salesgroup_id => P_identity_salesgroup_id,
2898 P_profile_tbl => AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
2899 P_Partner_Cont_Party_id => FND_API.G_MISS_NUM,
2900 P_Header_Rec => l_header_rec,
2901 X_LEAD_ID => l_lead_id,
2902 x_return_status => x_return_status,
2903 x_msg_count => x_msg_count,
2904 x_msg_data => x_msg_data);
2905
2906 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2907 raise FND_API.G_EXC_ERROR;
2908 END IF;
2909
2910
2911 IF l_debug THEN
2912 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Open Cursor C_Get_Opportunity:' || l_lead_id);
2913 END IF;
2914
2915 OPEN C_Get_Opportunity(l_lead_id);
2916 FETCH C_Get_Opportunity INTO l_header_rec.last_update_date;
2917 CLOSE C_Get_Opportunity;
2918
2919 l_header_rec.lead_id := l_lead_id;
2920
2921
2922 IF l_debug THEN
2923 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Copy lines data to opp, sales_lead_id='||P_Sales_Lead_Id);
2924 END IF;
2925
2926 -- Copy sales lead lines data to opportunity record lines
2927 l_cnt := 0;
2928 For C_Sales_Lead_lines_Rec In C_Get_Sales_lead_lines(P_Sales_Lead_Id) Loop
2929 IF l_debug THEN
2930 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'open lines cursor successfully');
2931 END IF;
2932 l_cnt := l_cnt + 1;
2933
2934 l_sales_lead_line_tbl(l_cnt).sales_lead_line_id
2935 := C_Sales_Lead_lines_Rec.sales_lead_line_id;
2936
2937 l_line_tbl(l_cnt).last_update_date := SYSDATE;
2938 l_line_tbl(l_cnt).last_updated_by := FND_GLOBAL.USER_ID;
2939 l_line_tbl(l_cnt).creation_Date := SYSDATE;
2940 l_line_tbl(l_cnt).created_by := FND_GLOBAL.USER_ID;
2941 l_line_tbl(l_cnt).last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
2942 l_line_tbl(l_cnt).lead_id := l_lead_id;
2943 -- 103000 FFANG as_lead_lines_all.status_code has been obsolete
2944 -- l_line_tbl(l_cnt).status_code := 'NEW_OPPORTUNITY';
2945 -- end 103000 FFANG
2946 l_line_tbl(l_cnt).interest_type_id
2947 := C_Sales_Lead_lines_Rec.interest_type_id;
2948 l_line_tbl(l_cnt).primary_interest_code_id
2949 := C_Sales_Lead_lines_Rec.primary_interest_code_id;
2950 l_line_tbl(l_cnt).secondary_interest_code_id
2951 := C_Sales_Lead_lines_Rec.secondary_interest_code_id;
2952 -- l_line_tbl(l_cnt).interest_status_code -- obsolete
2953 l_line_tbl(l_cnt).inventory_item_id
2954 := C_Sales_Lead_lines_Rec.inventory_item_id;
2955 l_line_tbl(l_cnt).organization_id
2956 := C_Sales_Lead_lines_Rec.organization_id;
2957 l_line_tbl(l_cnt).uom_code := C_Sales_Lead_lines_Rec.uom_code;
2958 l_line_tbl(l_cnt).quantity := C_Sales_Lead_lines_Rec.quantity;
2959 l_line_tbl(l_cnt).total_amount
2960 := C_Sales_Lead_lines_Rec.budget_amount;
2961 -- l_line_tbl(l_cnt).sales_stage_id -- obsolete
2962 -- l_line_tbl(l_cnt).ship_date -- not exist in sales lead lines
2963 -- l_line_tbl(l_cnt).win_probability -- obsolete
2964 -- l_line_tbl(l_cnt).decision_date -- obsolete
2965 -- 103000 FFANG as_lead_lines_all.channel_code has been obsolete
2966 -- l_line_tbl(l_cnt).channel_code := l_header_rec.channel_code;
2967 -- end 103000 FFANG
2968 -- l_line_tbl(l_cnt).quoted_line_flag -- not exist in sales lead lines
2969 -- l_line_tbl(l_cnt).original_lead_line_id -- not exist in sl lines
2970 -- l_line_tbl(l_cnt).org_id -- not exist in sales lead lines
2971 -- l_line_tbl(l_cnt).price -- not exist in sales lead lines
2972 -- 103000 FFANG for bug 1479671
2973 l_line_tbl(l_cnt).source_promotion_id
2974 := C_Sales_Lead_lines_Rec.source_promotion_id;
2975 --end 103000 FFANG
2976 -- l_line_tbl(l_cnt).price_volume_margin -- not exist in sl lines
2977 l_line_tbl(l_cnt).attribute_category
2978 := C_Sales_Lead_lines_Rec.attribute_category;
2979 l_line_tbl(l_cnt).attribute1 := C_Sales_Lead_lines_Rec.attribute1;
2980 l_line_tbl(l_cnt).attribute2 := C_Sales_Lead_lines_Rec.attribute2;
2981 l_line_tbl(l_cnt).attribute3 := C_Sales_Lead_lines_Rec.attribute3;
2982 l_line_tbl(l_cnt).attribute4 := C_Sales_Lead_lines_Rec.attribute4;
2983 l_line_tbl(l_cnt).attribute5 := C_Sales_Lead_lines_Rec.attribute5;
2984 l_line_tbl(l_cnt).attribute6 := C_Sales_Lead_lines_Rec.attribute6;
2985 l_line_tbl(l_cnt).attribute7 := C_Sales_Lead_lines_Rec.attribute7;
2986 l_line_tbl(l_cnt).attribute8 := C_Sales_Lead_lines_Rec.attribute8;
2987 l_line_tbl(l_cnt).attribute9 := C_Sales_Lead_lines_Rec.attribute9;
2988 l_line_tbl(l_cnt).attribute10 := C_Sales_Lead_lines_Rec.attribute10;
2989 l_line_tbl(l_cnt).attribute11 := C_Sales_Lead_lines_Rec.attribute11;
2990 l_line_tbl(l_cnt).attribute12 := C_Sales_Lead_lines_Rec.attribute12;
2991 l_line_tbl(l_cnt).attribute13 := C_Sales_Lead_lines_Rec.attribute13;
2992 l_line_tbl(l_cnt).attribute14 := C_Sales_Lead_lines_Rec.attribute14;
2993 l_line_tbl(l_cnt).attribute15 := C_Sales_Lead_lines_Rec.attribute15;
2994 l_line_tbl(l_cnt).member_access := FND_API.G_MISS_CHAR;
2995 l_line_tbl(l_cnt).member_role := FND_API.G_MISS_CHAR;
2996 l_line_tbl(l_cnt).owner_scredit_percent := FND_API.G_MISS_NUM;
2997 -- l_line_tbl(l_cnt).security_group_id
2998 -- := C_Sales_Lead_lines_Rec.security_group_id;
2999 -- 103000 FFANG for bug 1479671
3000 l_line_tbl(l_cnt).offer_id := C_Sales_Lead_lines_Rec.offer_id;
3001 -- end 103000 FFANG
3002 l_line_tbl(l_cnt).product_category_id := C_Sales_Lead_lines_Rec.category_id;
3003 l_line_tbl(l_cnt).product_cat_set_id := C_Sales_Lead_lines_Rec.category_set_id;
3004
3005 END Loop;
3006
3007 if l_line_tbl.count > 0
3008 then
3009 IF l_debug THEN
3010 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling AS_OPP_LINE_PVT.Create_Opp_Lines');
3011 END IF;
3012
3013 -- ffang 030503, bug 2826512, call PUB instead of PVT
3014 -- AS_OPP_line_PVT.Create_opp_lines (
3015 AS_OPPORTUNITY_PUB.Create_Opp_Lines (
3016 p_api_version_number => 2.0,
3017 p_init_msg_list => FND_API.G_FALSE,
3018 p_commit => FND_API.G_FALSE,
3019 p_validation_level => p_validation_level,
3020 P_Check_Access_Flag => 'N', -- p_check_access_flag,
3021 -- P_Check_Access_Flag => FND_API.G_FALSE,
3022 P_Admin_Flag => p_admin_flag,
3023 P_Admin_Group_Id => P_Admin_Group_Id,
3024 P_Identity_Salesforce_Id => p_identity_salesforce_id,
3025 P_salesgroup_id => P_identity_salesgroup_id,
3026 P_profile_tbl => AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
3027 P_Partner_Cont_Party_id => null,
3028 P_line_tbl => l_line_tbl,
3029 P_Header_Rec => l_header_rec,
3030 X_LINE_OUT_TBL => l_line_out_tbl,
3031 x_return_status => x_return_status,
3032 x_msg_count => x_msg_count,
3033 x_msg_data => x_msg_data);
3034
3035 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3036 raise FND_API.G_EXC_ERROR;
3037 END IF;
3038
3039 For l_index In 1..l_line_out_tbl.count Loop
3040 IF l_debug THEN
3041 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling AS_SALES_LEAD_OPP_PKG.Lead_Opp_Lines_Insert_Row' ||
3042 l_sales_lead_line_tbl(l_index).sales_lead_line_id ||
3043 ',' || l_line_out_tbl(l_index).lead_line_id);
3044 END IF;
3045
3046 l_lead_opp_line_id := null;
3047 AS_SALES_LEAD_OPP_PKG.Lead_Opp_Lines_Insert_Row (
3048 px_LEAD_OPP_LINE_ID => l_Lead_Opp_Line_Id
3049 ,p_SALES_LEAD_LINE_ID =>
3050 l_sales_lead_line_tbl(l_index).sales_lead_line_id
3051 ,p_OPP_LINE_ID => l_line_out_tbl(l_index).lead_line_id
3052 ,p_LAST_UPDATE_DATE => SYSDATE
3053 ,p_LAST_UPDATED_BY => FND_GLOBAL.User_Id
3054 ,p_CREATION_DATE => SYSDATE
3055 ,p_CREATED_BY => FND_GLOBAL.User_Id
3056 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
3057 ,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
3058 ,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
3059 ,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
3060 ,p_PROGRAM_UPDATE_DATE => SYSDATE
3061 ,p_ATTRIBUTE_CATEGORY => l_line_tbl(l_index).attribute_category
3062 ,p_ATTRIBUTE1 => l_line_tbl(l_index).attribute1
3063 ,p_ATTRIBUTE2 => l_line_tbl(l_index).attribute2
3064 ,p_ATTRIBUTE3 => l_line_tbl(l_index).attribute3
3065 ,p_ATTRIBUTE4 => l_line_tbl(l_index).attribute4
3066 ,p_ATTRIBUTE5 => l_line_tbl(l_index).attribute5
3067 ,p_ATTRIBUTE6 => l_line_tbl(l_index).attribute6
3068 ,p_ATTRIBUTE7 => l_line_tbl(l_index).attribute7
3069 ,p_ATTRIBUTE8 => l_line_tbl(l_index).attribute8
3070 ,p_ATTRIBUTE9 => l_line_tbl(l_index).attribute9
3071 ,p_ATTRIBUTE10 => l_line_tbl(l_index).attribute10
3072 ,p_ATTRIBUTE11 => l_line_tbl(l_index).attribute11
3073 ,p_ATTRIBUTE12 => l_line_tbl(l_index).attribute12
3074 ,p_ATTRIBUTE13 => l_line_tbl(l_index).attribute13
3075 ,p_ATTRIBUTE14 => l_line_tbl(l_index).attribute14
3076 ,p_ATTRIBUTE15 => l_line_tbl(l_index).attribute15
3077 -- ,p_security_group_id => l_line_rec.security_group_id
3078 );
3079
3080 END Loop;
3081 end if;
3082
3083 -- 102700 FFANG for bug 1478517, copy sales lead contacts information to
3084 -- opportunity contact table
3085
3086 -- Copy sales lead contacts data to opportunity record contacts
3087 IF l_debug THEN
3088 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Copy sales lead contacts data to opp');
3089 END IF;
3090 l_cnt := 0;
3091 For C_SL_Contacts_Rec In C_Get_Sales_lead_Contacts(P_Sales_Lead_Id) Loop
3092 l_cnt := l_cnt + 1;
3093
3094 l_contact_tbl(l_cnt).lead_id := l_lead_id;
3095 l_contact_tbl(l_cnt).contact_id := C_SL_Contacts_Rec.contact_id;
3096 l_contact_tbl(l_cnt).contact_party_id
3097 := C_SL_Contacts_Rec.contact_party_id;
3098 l_contact_tbl(l_cnt).last_update_date := SYSDATE;
3099 l_contact_tbl(l_cnt).last_updated_by := FND_GLOBAL.USER_ID;
3100 l_contact_tbl(l_cnt).creation_Date := SYSDATE;
3101 l_contact_tbl(l_cnt).created_by := FND_GLOBAL.USER_ID;
3102 l_contact_tbl(l_cnt).last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
3103 l_contact_tbl(l_cnt).enabled_flag := C_SL_Contacts_Rec.enabled_flag;
3104 l_contact_tbl(l_cnt).customer_id := C_SL_Contacts_Rec.customer_id;
3105 l_contact_tbl(l_cnt).address_id := C_SL_Contacts_Rec.address_id;
3106 l_contact_tbl(l_cnt).phone_id := C_SL_Contacts_Rec.phone_id;
3107 -- ffang 041802, for bug 2251391, opp contact role stores in rank
3108 l_contact_tbl(l_cnt).rank := C_SL_Contacts_Rec.contact_role_code;
3109 -- end ffang 041802
3110 l_contact_tbl(l_cnt).primary_contact_flag
3111 := C_SL_Contacts_Rec.primary_contact_flag;
3112 l_contact_tbl(l_cnt).role := C_SL_Contacts_Rec.contact_role_code;
3113 l_contact_tbl(l_cnt).attribute_category
3114 := C_SL_Contacts_Rec.attribute_category;
3115 l_contact_tbl(l_cnt).attribute1 := C_SL_Contacts_Rec.attribute1;
3116 l_contact_tbl(l_cnt).attribute2 := C_SL_Contacts_Rec.attribute2;
3117 l_contact_tbl(l_cnt).attribute3 := C_SL_Contacts_Rec.attribute3;
3118 l_contact_tbl(l_cnt).attribute4 := C_SL_Contacts_Rec.attribute4;
3119 l_contact_tbl(l_cnt).attribute5 := C_SL_Contacts_Rec.attribute5;
3120 l_contact_tbl(l_cnt).attribute6 := C_SL_Contacts_Rec.attribute6;
3121 l_contact_tbl(l_cnt).attribute7 := C_SL_Contacts_Rec.attribute7;
3122 l_contact_tbl(l_cnt).attribute8 := C_SL_Contacts_Rec.attribute8;
3123 l_contact_tbl(l_cnt).attribute9 := C_SL_Contacts_Rec.attribute9;
3124 l_contact_tbl(l_cnt).attribute10 := C_SL_Contacts_Rec.attribute10;
3125 l_contact_tbl(l_cnt).attribute11 := C_SL_Contacts_Rec.attribute11;
3126 l_contact_tbl(l_cnt).attribute12 := C_SL_Contacts_Rec.attribute12;
3127 l_contact_tbl(l_cnt).attribute13 := C_SL_Contacts_Rec.attribute13;
3128 l_contact_tbl(l_cnt).attribute14 := C_SL_Contacts_Rec.attribute14;
3129 l_contact_tbl(l_cnt).attribute15 := C_SL_Contacts_Rec.attribute15;
3130 -- l_contact_tbl(l_cnt).security_group_id
3131 -- := C_SL_Contacts_Rec.security_group_id;
3132
3133 END Loop;
3134
3135 IF l_contact_tbl.count > 0
3136 THEN
3137 IF l_debug THEN
3138 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling AS_OPP_CONTACT_PVT.Create_Opp_contacts');
3139 END IF;
3140
3141 -- ffang 030503, bug 2826512, call PUB instead of PVT
3142 -- AS_OPP_CONTACT_PVT.Create_opp_contacts (
3143 AS_OPPORTUNITY_PUB.Create_Contacts (
3144 p_api_version_number => 2.0,
3145 p_init_msg_list => FND_API.G_FALSE,
3146 p_commit => FND_API.G_FALSE,
3147 p_validation_level => p_validation_level,
3148 P_Check_Access_Flag => 'N', -- P_Check_Access_Flag,
3149 -- P_Check_Access_Flag => FND_API.G_FALSE,
3150 P_Admin_Flag => P_Admin_Flag,
3151 P_Admin_Group_Id => P_Admin_Group_Id,
3152 P_Identity_Salesforce_Id => p_identity_salesforce_id,
3153 P_profile_tbl => AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
3154 P_Partner_Cont_Party_id => null,
3155 P_contact_tbl => l_contact_tbl,
3156 X_contact_OUT_TBL => l_contact_out_tbl,
3157 x_return_status => x_return_status,
3158 x_msg_count => x_msg_count,
3159 x_msg_data => x_msg_data);
3160
3161 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3162 raise FND_API.G_EXC_ERROR;
3163 END IF;
3164 -- end 102700 FFANG
3165 END IF;
3166
3167 -- Update Sales Leads for Status Code
3168
3169 -- ffang 020301, AS_API_RECORDS_PKG.Get_P_Sales_Lead_Rec will initialize
3170 -- l_Sales_Lead_Rec, assign_to_xxx_id will be initialized as g_miss_num.
3171 -- Use another record instead (l_sl_rec). And we don't need to call
3172 -- AS_API_RECORDS_PKG.Get_P_Sales_Lead_Rec to initialize l_sl_rec,
3173 -- since it was default as miss_rec.
3174
3175 -- l_Sales_Lead_Rec := AS_API_RECORDS_PKG.Get_P_Sales_Lead_Rec;
3176 -- l_Sales_Lead_Rec.Sales_Lead_Id := p_Sales_Lead_Id;
3177 -- l_Sales_Lead_Rec.last_update_date := l_last_update_date;
3178 -- l_Sales_Lead_Rec.Status_Code := 'QUALIFIED';
3179
3180 l_SL_Rec.Sales_Lead_Id := p_Sales_Lead_Id;
3181 l_SL_Rec.last_update_date := l_last_update_date;
3182
3183 -- has to be changed once the profile problem is solved
3184 --l_SL_Rec.Status_Code := nvl(FND_PROFILE.Value('AS_LEAD_LINK_STATUS'),'QUALIFIED');
3185 l_SL_Rec.Status_Code := nvl(FND_PROFILE.Value('AS_LEAD_LINK_STATUS'),'CONVERTED_TO_OPPORTUNITY');
3186 --l_SL_Rec.Status_Code := 'QUALIFIED';
3187
3188 l_SL_Rec.assign_to_salesforce_id
3189 := l_Sales_Lead_Rec.assign_to_salesforce_id;
3190 l_SL_Rec.assign_to_person_id
3191 := l_Sales_Lead_Rec.assign_to_person_id;
3192 l_SL_Rec.assign_sales_group_id
3193 := l_Sales_Lead_Rec.assign_sales_group_id;
3194 -- end ffang 020301
3195
3196 IF l_debug THEN
3197 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling Update_sales_lead');
3198 END IF;
3199
3200 -- ffang 030503, bug 2826512, call PUB instead of PVT
3201 -- AS_SALES_LEADS_PVT.Update_sales_lead(
3202 AS_SALES_LEADS_PUB.Update_sales_lead(
3203 P_Api_Version_Number => l_api_version_number,
3204 P_Init_Msg_List => FND_API.G_FALSE,
3205 P_Commit => FND_API.G_FALSE,
3206 P_Validation_Level => P_Validation_Level,
3207 P_Check_Access_Flag => 'Y', -- P_Check_Access_Flag,
3208 P_Admin_Flag => P_Admin_Flag,
3209 P_Admin_Group_Id => P_Admin_Group_Id,
3210 P_identity_salesforce_id => P_identity_salesforce_id,
3211 P_Sales_Lead_Profile_Tbl => P_Sales_Lead_Profile_Tbl,
3212 P_SALES_LEAD_Rec => l_SL_Rec, -- ffang 020301
3213 -- P_SALES_LEAD_Rec => l_Sales_Lead_Rec,
3214 X_Return_Status => X_Return_Status,
3215 X_Msg_Count => X_Msg_Count,
3216 X_Msg_Data => X_Msg_Data
3217 );
3218
3219 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3220 raise FND_API.G_EXC_ERROR;
3221 END IF;
3222
3223 -- ffang 071202, bug 2451983
3224 l_Lead_Opportunity_Id := NULL;
3225 -- end ffang 071202, bug 2451983
3226
3227 -- Insert Interaction Data for linking Sales Lead to Opportunity
3228 AS_SALES_LEAD_OPP_PKG.Lead_Opportunity_Insert_Row (
3229 -- ffang 071202, why use l_lead_id as lead_opportunity_id output?
3230 -- px_LEAD_OPPORTUNITY_ID => l_Lead_Id
3231 px_LEAD_OPPORTUNITY_ID => l_Lead_Opportunity_Id
3232 ,p_SALES_LEAD_ID => P_Sales_Lead_Id
3233 ,p_OPPORTUNITY_ID => l_Lead_Id
3234 ,p_LAST_UPDATE_DATE => SYSDATE
3235 ,p_LAST_UPDATED_BY => FND_GLOBAL.User_Id
3236 ,p_CREATION_DATE => SYSDATE
3237 ,p_CREATED_BY => FND_GLOBAL.User_Id
3238 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
3239 ,p_REQUEST_ID => FND_GLOBAL.Conc_Request_Id
3240 ,p_PROGRAM_APPLICATION_ID => FND_GLOBAL.Prog_Appl_Id
3241 ,p_PROGRAM_ID => FND_GLOBAL.Conc_Program_Id
3242 ,p_PROGRAM_UPDATE_DATE => SYSDATE
3243 ,p_ATTRIBUTE_CATEGORY => l_header_rec.attribute_category
3244 ,p_ATTRIBUTE1 => l_header_rec.attribute1
3245 ,p_ATTRIBUTE2 => l_header_rec.attribute2
3246 ,p_ATTRIBUTE3 => l_header_rec.attribute3
3247 ,p_ATTRIBUTE4 => l_header_rec.attribute4
3248 ,p_ATTRIBUTE5 => l_header_rec.attribute5
3249 ,p_ATTRIBUTE6 => l_header_rec.attribute6
3250 ,p_ATTRIBUTE7 => l_header_rec.attribute7
3251 ,p_ATTRIBUTE8 => l_header_rec.attribute8
3252 ,p_ATTRIBUTE9 => l_header_rec.attribute9
3253 ,p_ATTRIBUTE10 => l_header_rec.attribute10
3254 ,p_ATTRIBUTE11 => l_header_rec.attribute11
3255 ,p_ATTRIBUTE12 => l_header_rec.attribute12
3256 ,p_ATTRIBUTE13 => l_header_rec.attribute13
3257 ,p_ATTRIBUTE14 => l_header_rec.attribute14
3258 ,p_ATTRIBUTE15 => l_header_rec.attribute15
3259 -- ,p_security_group_id => l_opp_security_group_id
3260 -- l_header_rec.security_group_id
3261 );
3262
3263 x_opportunity_id := l_lead_id;
3264
3265 Create_Lead_Ctx(
3266 P_SALES_LEAD_ID,
3267 l_lead_id,
3268 X_Return_Status
3269 );
3270
3271 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3272 raise FND_API.G_EXC_ERROR;
3273 END IF;
3274
3275 -- ffang 051602, bug 2278318, copy lead's sales team to oppty
3276 IF nvl(FND_PROFILE.Value('AS_LEADLINK_MOVE_ST'), 'N') = 'Y' THEN
3277 FOR ST IN c_get_lead_salesteam (P_Sales_Lead_Id)
3278 LOOP
3279 l_sales_team_rec.freeze_flag := ST.freeze_flag;
3280 l_sales_team_rec.reassign_flag := ST.reassign_flag;
3281 l_sales_team_rec.team_leader_flag := ST.team_leader_flag;
3282 l_sales_team_rec.customer_id := ST.customer_id;
3283 l_sales_team_rec.address_id := ST.address_id;
3284 l_sales_team_rec.salesforce_id := ST.salesforce_id;
3285 l_sales_team_rec.person_id := ST.person_id;
3286 l_sales_team_rec.sales_group_id := ST.sales_group_id;
3287 l_sales_team_rec.partner_customer_id := ST.partner_customer_id;
3288 l_sales_team_rec.partner_address_id := ST.partner_address_id;
3289 l_sales_team_rec.created_person_id := ST.created_person_id;
3290 l_sales_team_rec.freeze_date := ST.freeze_date;
3291 l_sales_team_rec.reassign_reason := ST.reassign_reason;
3292 l_sales_team_rec.salesforce_role_code := ST.salesforce_role_code;
3293 l_sales_team_rec.salesforce_relationship_code :=
3294 ST.salesforce_relationship_code;
3295 l_sales_team_rec.attribute_category := ST.attribute_category;
3296 l_sales_team_rec.attribute1 := ST.attribute1;
3297 l_sales_team_rec.attribute2 := ST.attribute2;
3298 l_sales_team_rec.attribute3 := ST.attribute3;
3299 l_sales_team_rec.attribute4 := ST.attribute4;
3300 l_sales_team_rec.attribute5 := ST.attribute5;
3301 l_sales_team_rec.attribute6 := ST.attribute6;
3302 l_sales_team_rec.attribute7 := ST.attribute7;
3303 l_sales_team_rec.attribute8 := ST.attribute8;
3304 l_sales_team_rec.attribute9 := ST.attribute9;
3305 l_sales_team_rec.attribute10 := ST.attribute10;
3306 l_sales_team_rec.attribute11 := ST.attribute11;
3307 l_sales_team_rec.attribute12 := ST.attribute12;
3308 l_sales_team_rec.attribute13 := ST.attribute13;
3309 l_sales_team_rec.attribute14 := ST.attribute14;
3310 l_sales_team_rec.attribute15 := ST.attribute15;
3311 l_sales_team_rec.reassign_request_date:= ST.reassign_request_date;
3312 l_sales_team_rec.reassign_requested_person_id :=
3313 ST.reassign_requested_person_id;
3314 l_sales_team_rec.partner_cont_party_id:= ST.partner_cont_party_id;
3315 l_sales_team_rec.created_by_tap_flag := ST.created_by_tap_flag;
3316 l_sales_team_rec.prm_keep_flag := ST.prm_keep_flag;
3317
3318 l_sales_team_rec.access_id := NULL;
3319 l_sales_team_rec.sales_lead_id := NULL;
3320 l_sales_team_rec.lead_id := l_lead_id;
3321 -- ffang 062502, bug 2432561
3322 -- ffang 031103, bug 2844916, don't pass lead's owner flag to opp st
3323 l_sales_team_rec.owner_flag := 'N';
3324 -- l_sales_team_rec.owner_flag := ST.owner_flag;
3325 -- end ffang 031103, bug 2844916
3326 -- end ffang 062502
3327
3328 IF l_debug THEN
3329 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'Calling Create_SalesTeam');
3330 END IF;
3331
3332 AS_ACCESS_PUB.Create_SalesTeam (
3333 p_api_version_number => 2.0
3334 ,p_init_msg_list => FND_API.G_FALSE
3335 ,p_commit => FND_API.G_FALSE
3336 ,p_validation_level => p_Validation_Level
3337 ,p_access_profile_rec => l_access_profile_rec
3338 ,p_check_access_flag => 'N'
3339 ,p_admin_flag => P_Admin_Flag
3340 ,p_admin_group_id => P_Admin_Group_Id
3341 ,p_identity_salesforce_id => P_Identity_Salesforce_Id
3342 ,p_sales_team_rec => l_sales_team_rec
3343 ,X_Return_Status => x_Return_Status
3344 ,X_Msg_Count => X_Msg_Count
3345 ,X_Msg_Data => X_Msg_Data
3346 ,x_access_id => l_Access_Id
3347 );
3348
3349 IF l_debug THEN
3350 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'access_id : ' || l_Access_Id);
3351 END IF;
3352 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3353 RAISE FND_API.G_EXC_ERROR;
3354 END IF;
3355 END LOOP;
3356 END IF;
3357 -- end ffang 051602, bug 2278318
3358 --Code added for Enhancement convert lead to opportunity - Attachment
3359 --Enhancement related Bug#3913225 @ @ Callling the procedure Copy_Attachments--changes for ASN.B--Start--
3360 OPEN C_Attachment_Exists(to_char(P_SALES_LEAD_ID));
3361 FETCH C_Attachment_Exists into l_val_exists;
3362 CLOSE C_Attachment_Exists;
3363
3364 IF l_val_exists IS NOT NULL THEN
3365 --Copy attachments procedure calling
3366 FND_ATTACHED_DOCUMENTS2_PKG.Copy_Attachments(
3367 x_from_entity_name => 'AS_LEAD_ATTCH',
3368 x_from_pk1_value => to_char(P_SALES_LEAD_ID),
3369 x_to_entity_name => 'AS_OPPORTUNITY_ATTCH',
3370 x_to_pk1_value => to_char(x_opportunity_id),
3371 x_automatically_added_flag => null,
3372 x_created_by => G_USER_ID);
3373
3374 ELSE
3375 NULL;
3376 END IF;
3377 --Enhancement related Bug#3913225 @ @ Callling the procedure Copy_Attachments--changes for ASN.B--End--
3378 -- Start Bug#3966128
3379 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y'
3380 AND l_Sales_Lead_Rec.incumbent_partner_party_id IS NOT NULL
3381 AND l_Sales_Lead_Rec.incumbent_partner_resource_id IS NOT NULL
3382 THEN
3383 open c_address_id(l_Sales_Lead_Rec.incumbent_partner_party_id);
3384 fetch c_address_id into l_address_id;
3385 close c_address_id;
3386 l_psalesteam_rec.customer_id:=l_Sales_Lead_Rec.customer_id;
3387 l_psalesteam_rec.partner_customer_id:=l_Sales_Lead_Rec.incumbent_partner_party_id;
3388 l_psalesteam_rec.partner_address_id:=l_address_id;
3389 l_psalesteam_rec.lead_id:=x_opportunity_id;
3390 l_psalesteam_rec.freeze_flag:='Y';
3391 l_psalesteam_rec.salesforce_id:=l_Sales_Lead_Rec.incumbent_partner_resource_id;
3392
3393 AS_ACCESS_PUB.Create_SalesTeam(
3394 p_api_version_number => 2.0,
3395 p_init_msg_list => FND_API.G_FALSE,
3396 p_commit => FND_API.G_FALSE,
3397 p_validation_level => p_Validation_Level,
3398 p_access_profile_rec => l_access_profile_rec,
3399 p_check_access_flag => 'N',
3400 p_admin_flag => P_Admin_Flag,
3401 p_admin_group_id => P_Admin_Group_Id,
3402 p_identity_salesforce_id => P_Identity_Salesforce_Id,
3403 p_sales_team_rec => l_psalesteam_rec,
3404 x_return_status => x_Return_Status,
3405 x_msg_count => X_Msg_Count,
3406 x_msg_data => X_Msg_Data,
3407 x_access_id => l_Access_Id);
3408 IF l_debug THEN
3409 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'access_id of preferred partner: ' || l_Access_Id);
3410 END IF;
3411 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3412 RAISE FND_API.G_EXC_ERROR;
3413 END IF;
3414 END IF;
3415 -- End Bug#3966128
3416
3417 --
3418 -- END of API body
3419 --
3420
3421 -- Standard check for p_commit
3422 IF FND_API.to_Boolean( p_commit )
3423 THEN
3424 COMMIT WORK;
3425 END IF;
3426
3427
3428 -- Debug Message
3429 IF l_debug THEN
3430 AS_UTILITY_PVT.Debug_Message(l_module, NULL, 'PVT: ' || l_api_name || ' End');
3431 END IF;
3432
3433 -- Standard call to get message count and IF count is 1, get message info.
3434 FND_MSG_PUB.Count_And_Get
3435 ( p_count => x_msg_count,
3436 p_data => x_msg_data );
3437
3438 EXCEPTION
3439 WHEN FND_API.G_EXC_ERROR THEN
3440 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3441 P_MODULE => l_module
3442 ,P_API_NAME => L_API_NAME
3443 ,P_PKG_NAME => G_PKG_NAME
3444 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3445 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3446 ,X_MSG_COUNT => X_MSG_COUNT
3447 ,X_MSG_DATA => X_MSG_DATA
3448 ,X_RETURN_STATUS => X_RETURN_STATUS);
3449
3450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3451 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3452 P_MODULE => l_module
3453 ,P_API_NAME => L_API_NAME
3454 ,P_PKG_NAME => G_PKG_NAME
3455 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3456 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3457 ,X_MSG_COUNT => X_MSG_COUNT
3458 ,X_MSG_DATA => X_MSG_DATA
3459 ,X_RETURN_STATUS => X_RETURN_STATUS);
3460
3461 WHEN OTHERS THEN
3462 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3463 P_MODULE => l_module
3464 ,P_API_NAME => L_API_NAME
3465 ,P_PKG_NAME => G_PKG_NAME
3466 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
3467 ,P_SQLCODE => SQLCODE
3468 ,P_SQLERRM => SQLERRM
3469 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3470 ,X_MSG_COUNT => X_MSG_COUNT
3471 ,X_MSG_DATA => X_MSG_DATA
3472 ,X_RETURN_STATUS => X_RETURN_STATUS);
3473
3474 END Create_Opportunity_For_Lead;
3475
3476 PROCEDURE Create_Lead_Ctx(
3477 p_sales_lead_id IN NUMBER,
3478 p_opportunity_id IN NUMBER,
3479 x_return_status OUT NOCOPY VARCHAR2
3480 )
3481 IS
3482 l_jtf_notes_context_id NUMBER;
3483
3484 -- Selecting the JTF_NOTES_CONTEXT_ID.
3485 -- SELECT JTF_NOTES_S.NEXTVAL INTO l_jtf_notes_context_id FROM DUAL;
3486
3487 CURSOR c_get_notes (x_sales_lead_id NUMBER, x_opportunity_id NUMBER) IS
3488 SELECT
3489 notes.jtf_note_id
3490 FROM
3491 JTF_NOTES_B notes
3492 WHERE
3493 notes.source_object_id = x_sales_lead_id AND
3494 notes.source_object_code = 'LEAD' AND
3495 NOT EXISTS (
3496 SELECT
3497 context.jtf_note_id
3498 FROM
3499 JTF_NOTE_CONTEXTS context
3500 WHERE
3501 notes.jtf_note_id = context.jtf_note_id AND
3502 context.note_context_type = 'OPPORTUNITY' AND
3503 context.note_context_type_id = p_opportunity_id
3504 );
3505
3506
3507 BEGIN
3508 -- API savepoint
3509 -- commented for now may be break in a trigger
3510 -- SAVEPOINT Create_Lead_Ctx;
3511
3512 -- Initialize return status to SUCCESS
3513 x_return_status := fnd_api.g_ret_sts_success;
3514
3515 For notes_rec In c_get_notes (p_sales_lead_id, p_opportunity_id)
3516 Loop
3517 SELECT JTF_NOTES_S.NEXTVAL INTO l_jtf_notes_context_id FROM DUAL;
3518
3519 -- Inserting into JTF_NOTES_CONTEXTS table
3520 INSERT INTO JTF_NOTE_CONTEXTS (
3521 NOTE_CONTEXT_ID
3522 , JTF_NOTE_ID
3523 , NOTE_CONTEXT_TYPE
3524 , NOTE_CONTEXT_TYPE_ID
3525 , LAST_UPDATE_DATE
3526 , LAST_UPDATED_BY
3527 , CREATION_DATE
3528 , CREATED_BY
3529 , LAST_UPDATE_LOGIN)
3530 values (
3531 l_jtf_notes_context_id,
3532 notes_rec.jtf_note_id ,
3533 'OPPORTUNITY'
3534 ,p_opportunity_id,
3535 SYSDATE,
3536 FND_GLOBAL.USER_ID,
3537 SYSDATE,
3538 FND_GLOBAL.USER_ID,
3539 FND_GLOBAL.USER_ID);
3540
3541 End Loop;
3542
3543 EXCEPTION
3544 WHEN NO_DATA_FOUND THEN
3545 NULL;
3546 WHEN OTHERS THEN
3547 -- ROLLBACK TO Create_Lead_Ctx;
3548 x_return_status := fnd_api.g_ret_sts_error;
3549
3550
3551 END Create_Lead_Ctx;
3552
3553 -- This procedure Added for Bug#3613374
3554 PROCEDURE CHECK_SALES_STAGE(
3555 p_api_version_number IN NUMBER,
3556 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3557 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
3558 p_sales_lead_id IN NUMBER,
3559 P_sales_lead_stage_id IN NUMBER,
3560 P_sales_lead_methodology_id IN NUMBER,
3561 X_sales_stage_id OUT NOCOPY NUMBER,
3562 X_sales_methodology_id OUT NOCOPY NUMBER,
3563 X_Return_Status OUT NOCOPY VARCHAR2,
3564 X_Msg_Count OUT NOCOPY NUMBER,
3565 X_Msg_Data OUT NOCOPY VARCHAR2)
3566 IS
3567
3568 cursor c_sales_stage(p_sales_stage_id NUMBER) is
3569 select applicability
3570 from as_sales_stages_all_vl
3571 where sales_stage_id = p_sales_stage_id;
3572
3573 cursor c_first_sales_stage(p_sales_method_id NUMBER) is
3574 SELECT stage.sales_stage_id
3575 FROM as_sales_stages_all_vl stage, as_sales_meth_stage_map map1
3576 WHERE stage.sales_stage_id = map1.sales_stage_id
3577 AND nvl(stage.applicability,'BOTH') in ('OPPORTUNITY', 'BOTH')
3578 AND nvl(stage.ENABLED_FLAG,'Y') = 'Y'
3579 AND trunc(sysdate) between trunc(nvl(START_DATE_ACTIVE,sysdate))
3580 AND trunc(nvl(END_DATE_ACTIVE,sysdate))
3581 AND map1.sales_methodology_id = p_sales_method_id
3582 ORDER BY STAGE_SEQUENCE;
3583
3584 l_sales_methodology_id NUMBER := P_sales_lead_methodology_id;
3585 l_sales_stage_id NUMBER := P_sales_lead_stage_id;
3586 l_applicability VARCHAR2(100);
3587 l_api_name CONSTANT VARCHAR2(40) := 'CHECK_SALES_STAGE';
3588 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
3589 l_last_update_date date;
3590 l_module CONSTANT VARCHAR2(255) := 'as.plsql.slopv.CHECK_SALES_STAGE';
3591 BEGIN
3592 -- Standard Start of API savepoint
3593 SAVEPOINT CHECK_SALES_STAGE_PVT;
3594 -- Initialize API return status to SUCCESS
3595 x_return_status := FND_API.G_RET_STS_SUCCESS;
3596 -- Initialize message list if p_init_msg_list is set to TRUE.
3597 IF FND_API.to_Boolean( p_init_msg_list )
3598 THEN
3599 FND_MSG_PUB.initialize;
3600 END IF;
3601
3602 l_sales_methodology_id := P_sales_lead_methodology_id;
3603 l_sales_stage_id := P_sales_lead_stage_id;
3604 IF l_sales_methodology_id IS NULL THEN
3605 l_sales_methodology_id := to_number(FND_PROFILE.VALUE ('AS_SALES_METHODOLOGY'));
3606 l_sales_stage_id := to_number(FND_PROFILE.VALUE ('AS_OPP_SALES_STAGE'));
3607 END IF;
3608
3609 IF l_sales_methodology_id IS NULL THEN
3610 l_sales_stage_id := NULL;
3611 END IF;
3612 IF l_sales_methodology_id IS NOT NULL THEN
3613 IF l_sales_stage_id IS NOT NULL THEN
3614 OPEN c_sales_stage(l_sales_stage_id);
3615 FETCH c_sales_stage INTO l_applicability;
3616 CLOSE c_sales_stage;
3617 END IF;
3618 IF l_sales_stage_id IS NULL or
3619 nvl(l_applicability,'BOTH') NOT IN ('OPPORTUNITY', 'BOTH') THEN
3620 OPEN c_first_sales_stage(l_sales_methodology_id);
3621 FETCH c_first_sales_stage INTO l_sales_stage_id;
3622 IF c_first_sales_stage%NOTFOUND THEN
3623 AS_UTILITY_PVT.Set_Message(
3624 p_module => l_module,
3625 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
3626 p_msg_name => 'AS_STAGE_NOT_SETUP_FOR_METH');
3627 x_return_status :=FND_API.G_RET_STS_ERROR;
3628 END IF;
3629 CLOSE c_first_sales_stage;
3630 END IF;
3631 END IF;
3632 X_sales_stage_id := l_sales_stage_id;
3633 X_sales_methodology_id := l_sales_methodology_id;
3634 EXCEPTION
3635 WHEN FND_API.G_EXC_ERROR THEN
3636 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3637 P_MODULE => l_module
3638 ,P_API_NAME => L_API_NAME
3639 ,P_PKG_NAME => G_PKG_NAME
3640 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3641 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3642 ,X_MSG_COUNT => X_MSG_COUNT
3643 ,X_MSG_DATA => X_MSG_DATA
3644 ,X_RETURN_STATUS => X_RETURN_STATUS);
3645
3646 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3647 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3648 P_MODULE => l_module
3649 ,P_API_NAME => L_API_NAME
3650 ,P_PKG_NAME => G_PKG_NAME
3651 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3652 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3653 ,X_MSG_COUNT => X_MSG_COUNT
3654 ,X_MSG_DATA => X_MSG_DATA
3655 ,X_RETURN_STATUS => X_RETURN_STATUS);
3656
3657 WHEN OTHERS THEN
3658 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
3659 P_MODULE => l_module
3660 ,P_API_NAME => L_API_NAME
3661 ,P_PKG_NAME => G_PKG_NAME
3662 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
3663 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
3664 ,X_MSG_COUNT => X_MSG_COUNT
3665 ,X_MSG_DATA => X_MSG_DATA
3666 ,X_RETURN_STATUS => X_RETURN_STATUS);
3667 END CHECK_SALES_STAGE;
3668
3669 END AS_SALES_LEAD_OPP_PVT;