[Home] [Help]
PACKAGE BODY: APPS.AS_OPP_COPY_PVT
Source
1 PACKAGE BODY AS_OPP_COPY_PVT as
2 /* $Header: asxvlcpb.pls 120.7.12010000.1 2008/07/29 03:11:39 appldev ship $ */
3 -- Start of Comments
4 -- Package name : AS_OPP_COPY_PVT
5 -- Purpose :
6 -- History :
7 -- 09-OCT-00 XDING Created
8 --
9 -- NOTE :
10 -- End of Comments
11 --
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_OPP_COPY_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxvlcpb.pls';
15
16 PROCEDURE Copy_Opportunity
17 ( p_api_version_number IN NUMBER,
18 p_init_msg_list IN VARCHAR2 :=FND_API.G_FALSE,
19 p_commit IN VARCHAR2 := FND_API.G_FALSE,
20 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
21 p_lead_id IN NUMBER,
22 p_description IN VARCHAR2,
23 p_copy_salesteam IN VARCHAR2 :=FND_API.G_FALSE,
24 p_copy_opp_lines IN VARCHAR2 :=FND_API.G_FALSE,
25 p_copy_lead_contacts IN VARCHAR2 :=FND_API.G_FALSE,
26 p_copy_lead_competitors IN VARCHAR2 :=FND_API.G_FALSE,
27 p_copy_sales_credits IN VARCHAR2 :=FND_API.G_FALSE,
28 p_copy_methodology IN VARCHAR2 :=FND_API.G_FALSE,
29 p_new_customer_id IN NUMBER,
30 p_new_address_id IN NUMBER,
31 p_check_access_flag IN VARCHAR2,
32 p_admin_flag IN VARCHAR2,
33 p_admin_group_id IN NUMBER,
34 p_identity_salesforce_id IN NUMBER,
35 p_salesgroup_id IN NUMBER := NULL,
36 p_partner_cont_party_id IN NUMBER,
37 p_profile_tbl IN AS_UTILITY_PUB.Profile_Tbl_Type
38 :=AS_UTILITY_PUB.G_MISS_PROFILE_TBL,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2,
42 x_lead_id OUT NOCOPY NUMBER
43 )
44 IS
45 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Opportunity';
46 l_api_version_number CONSTANT NUMBER := 2.0;
47
48 l_header_rec AS_OPPORTUNITY_PUB.Header_Rec_Type;
49 l_line_tbl AS_OPPORTUNITY_PUB.Line_Tbl_Type;
50 l_Sales_Team_Rec AS_ACCESS_PUB.Sales_Team_Rec_Type;
51
52 l_index NUMBER;
53 l_lead_number NUMBER;
54 l_rowid ROWID;
55 l_access_id NUMBER;
56 l_lead_line_id NUMBER;
57 l_sales_credit_id NUMBER;
58 l_lead_contact_id NUMBER;
59 l_lead_competitor_id NUMBER;
60 l_close_competitor_id NUMBER;
61 l_lead_competitor_prod_id NUMBER;
62 l_lead_decision_factor_id NUMBER;
63
64 l_new_sales_methodology_id NUMBER;
65 l_view_access_flag VARCHAR2(1);
66 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
67 l_access_profile_rec AS_ACCESS_PUB.Access_Profile_Rec_Type;
68 l_customer_id NUMBER;
69 l_new_status VARCHAR2(30);
70 l_default_status VARCHAR2(30) := fnd_profile.value('AS_OPP_STATUS');
71 l_new_total_amount NUMBER;
72 l_TOT_REVENUE_OPP_FORECAST_AMT NUMBER := NULL; -- Added for ASNB
73
74 l_sales_credit_tbl AS_OPPORTUNITY_PUB.Sales_Credit_Tbl_type;
75 l_sales_credit_rec AS_OPPORTUNITY_PUB.Sales_Credit_Rec_type;
76 x_sales_credit_out_tbl AS_OPPORTUNITY_PUB.Sales_Credit_Out_Tbl_Type;
77 l_forecast_credit_type_id NUMBER := FND_PROFILE.Value('AS_FORECAST_CREDIT_TYPE_ID');
78 l_val NUMBER;
79 l_date DATE;
80 l_copy_opp_lines VARCHAR2(1) := p_copy_opp_lines;
81 l_cre_st_for_sc_flag VARCHAR2(1) := 'N';
82 l_insert BOOLEAN;
83
84 CURSOR c_customer(c_lead_id NUMBER) IS
85 SELECT customer_id
86 FROM AS_LEADS_ALL
87 WHERE lead_id = c_lead_id;
88
89 CURSOR c_header(c_lead_id NUMBER) IS
90 SELECT *
91 FROM AS_LEADS_ALL
92 WHERE lead_id = c_lead_id;
93
94 -- If create sales team for sales credit flag is set to 'N', return
95 -- all records from AS_ACCESSES_ALL for that lead_id but if the flag
96 -- is set to 'Y', return the records from AS_ACCESSES_ALL that also
97 -- exist in AS_SALES_CREDITS
98 -- Note that the result set is ordered by owner_flag because according
99 -- to the logic, the owner should be updated before any other sales
100 -- team member is inserted as otherwise, the insert might fail if the
101 -- record being inserted has same unique keys as the owner created by
102 -- the header
103 CURSOR c_salesteam(c_lead_id NUMBER, c_cre_st_for_sc_flag VARCHAR2) IS
104 SELECT *
105 FROM AS_ACCESSES_ALL A
106 WHERE lead_id = c_lead_id
107 AND salesforce_id in -- This condition was added for bug 5361442
108 (SELECT s.salesforce_id
109 FROM as_accesses_all s,
110 jtf_rs_resource_extns j
111 WHERE s.salesforce_id = j.resource_id
112 AND (j.end_date_active IS NULL
113 OR j.end_date_active > sysdate))
114 AND (c_cre_st_for_sc_flag = 'N' OR EXISTS
115 (SELECT 1
116 FROM AS_SALES_CREDITS C
117 WHERE C.lead_id = A.lead_id
118 AND C.salesforce_id = A.salesforce_id
119 AND C.salesgroup_id = A.sales_group_id))
120 ORDER BY nvl(owner_flag, 'N') desc;
121
122 CURSOR c_owner_in_salesTeam(c_lead_id NUMBER,c_salesforce_id NUMBER,c_sales_group_id NUMBER) IS
123 SELECT access_id,last_update_date
124 FROM AS_ACCESSES_ALL
125 WHERE lead_id = c_lead_id
126 AND salesforce_id = c_salesforce_id
127 AND nvl(sales_group_id, -99) = nvl(c_sales_group_id, -99)
128 AND nvl(owner_flag,'N')='Y';
129
130 -- Added for ASNB
131 CURSOR c_log_user_in_salesTeam(c_lead_id NUMBER,c_salesforce_id NUMBER,c_sales_group_id NUMBER) IS
132 SELECT access_id,last_update_date
133 FROM AS_ACCESSES_ALL
134 WHERE lead_id = c_lead_id
135 AND salesforce_id = c_salesforce_id
136 AND nvl(sales_group_id, -99) = nvl(c_sales_group_id, -99);
137
138 CURSOR c_lines(c_lead_id NUMBER) IS
139 SELECT *
140 FROM AS_LEAD_LINES_ALL
141 WHERE lead_id = c_lead_id;
142
143 CURSOR c_contacts(c_lead_id NUMBER) IS
144 SELECT *
145 FROM AS_LEAD_CONTACTS
146 WHERE lead_id = c_lead_id;
147
148 CURSOR c_competitors(c_lead_id NUMBER) IS
149 SELECT *
150 FROM AS_LEAD_COMPETITORS
151 WHERE lead_id = c_lead_id;
152
153 CURSOR c_sales_credits(c_lead_id NUMBER, c_lead_line_id NUMBER) IS
154 SELECT *
155 FROM AS_SALES_CREDITS
156 WHERE lead_id = c_lead_id
157 AND lead_line_id = c_lead_line_id;
158
159 CURSOR c_competitor_products (c_lead_line_id NUMBER) IS
160 SELECT *
161 FROM AS_LEAD_COMP_PRODUCTS
162 WHERE lead_line_id = c_lead_line_id;
163
164 CURSOR c_decision_factors(c_lead_line_id NUMBER) IS
165 SELECT *
166 FROM AS_LEAD_DECISION_FACTORS
167 WHERE lead_line_id = c_lead_line_id;
168
169 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
170 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lcppv.Copy_Opportunity';
171
172 BEGIN
173 -- Standard Start of API savepoint
174 SAVEPOINT COPY_OPPORTUNITY_PVT;
175
176 -- Standard call to check for call compatibility.
177 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
178 p_api_version_number,
179 l_api_name,
180 G_PKG_NAME)
181 THEN
182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
183 END IF;
184
185
186 -- Initialize message list if p_init_msg_list is set to TRUE.
187 IF FND_API.to_Boolean( p_init_msg_list )
188 THEN
189 FND_MSG_PUB.initialize;
190 END IF;
191
192
193 -- Debug Message
194 IF l_debug THEN
195 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
196 'Private API: ' || l_api_name || ' start');
197 END IF;
198
199
200 -- Initialize API return status to SUCCESS
201 x_return_status := FND_API.G_RET_STS_SUCCESS;
202
203 --
204 -- API body
205 --
206 IF (p_validation_level = fnd_api.g_valid_level_full) THEN --fix for bug#3756261
207 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
208 p_api_version_number => 2.0
209 ,p_init_msg_list => p_init_msg_list
210 ,p_salesforce_id => p_identity_salesforce_id
211 ,p_admin_group_id => p_admin_group_id
212 ,x_return_status => x_return_status
213 ,x_msg_count => x_msg_count
214 ,x_msg_data => x_msg_data
215 ,x_sales_member_rec => l_identity_sales_member_rec);
216
217 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
218 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
219 IF l_debug THEN
220 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
221 'Private API: Get_CurrentUser fail');
222 END IF;
223 END IF;
224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
225 END IF;
226 END IF;
227
228 IF(P_Check_Access_Flag = 'Y') THEN
229 -- Call Get_Access_Profiles to get access_profile_rec
230 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
231 p_profile_tbl => p_profile_tbl,
232 x_access_profile_rec => l_access_profile_rec);
233
234 OPEN c_customer(p_lead_id);
235 FETCH c_customer into l_customer_id;
236 CLOSE c_customer;
237
238 AS_ACCESS_PUB.has_viewCustomerAccess
239 ( p_api_version_number => 2.0
240 ,p_init_msg_list => p_init_msg_list
241 ,p_validation_level => p_validation_level
242 ,p_access_profile_rec => l_access_profile_rec
243 ,p_admin_flag => p_admin_flag
244 ,p_admin_group_id => p_admin_group_id
245 ,p_person_id => l_identity_sales_member_rec.employee_person_id
246 ,p_customer_id => nvl(p_new_customer_id, l_customer_id)
247 ,p_check_access_flag => p_check_access_flag
248 ,p_identity_salesforce_id => p_identity_salesforce_id
249 ,p_partner_cont_party_id => p_partner_cont_party_id
250 ,x_return_status => x_return_status
251 ,x_msg_count => x_msg_count
252 ,x_msg_data => x_msg_data
253 ,x_view_access_flag => l_view_access_flag );
254
255 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
256 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
257 IF l_debug THEN
258 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
259 'has_viewCustomerAccess fail');
260 END IF;
261
262 END IF;
263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
264 END IF;
265
266 IF (l_view_access_flag <> 'Y') THEN
267 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
268 FND_MESSAGE.Set_Name('AS', 'API_NO_VIEW_PRIVILEGE');
269 FND_MESSAGE.Set_Token('INFO', 'CUSTOMER_ID,OPPORTUNITY_ID,SALESFORCE_ID', FALSE);
270 FND_MSG_PUB.ADD;
271 END IF;
272 RAISE FND_API.G_EXC_ERROR;
273 END IF;
274 END IF;
275
276 --
277 -- Copy the opportunity header
278 --
279
280 IF p_lead_id is null or p_lead_id = fnd_api.g_miss_num
281 THEN
282 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
283 IF l_debug THEN
284 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
285 'Private API: p_lead_id is null');
286 END IF;
287
288 END IF;
289 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
290 END IF;
291
292 FOR hdr IN c_header(p_lead_id) LOOP
293
294 /* IF hdr.PRM_ASSIGNMENT_TYPE is NULL OR hdr.PRM_ASSIGNMENT_TYPE = 'UNASSIGNED' THEN
295 l_new_status := hdr.STATUS;
296 ELSE
297 l_new_status := l_default_status;
298 END IF; */ -- Fix for bug#3763097
299
300 l_new_status := hdr.STATUS; -- Fix for bug#3763097
301
302 -- Bug 3426788
303 -- If sales credits are to be copied, purchase lines should also be copied
304 IF p_copy_sales_credits = 'Y' THEN
305 l_copy_opp_lines := 'Y';
306 END IF;
307
308 IF p_copy_methodology = 'Y' THEN
309 l_new_sales_methodology_id := hdr.SALES_METHODOLOGY_ID;
310 ELSE
311 l_new_sales_methodology_id := NULL;
312 END IF;
313
314 IF l_copy_opp_lines = 'Y' THEN
315 l_new_total_amount := hdr.TOTAL_AMOUNT;
316 l_TOT_REVENUE_OPP_FORECAST_AMT := hdr.TOTAL_REVENUE_OPP_FORECAST_AMT; -- Added for ASNB
317 END IF;
318
319 l_header_rec.ORIGINAL_LEAD_ID := hdr.LEAD_ID;
320 l_header_rec.STATUS_CODE := l_new_status; --bug1580008
321 l_header_rec.CUSTOMER_ID := nvl(p_new_customer_id, hdr.CUSTOMER_ID);
322 l_header_rec.ADDRESS_ID := p_new_address_id;
323 l_header_rec.SALES_STAGE_ID := hdr.SALES_STAGE_ID;
324 l_header_rec.INITIATING_CONTACT_ID := hdr.INITIATING_CONTACT_ID;
325 l_header_rec.CHANNEL_CODE := hdr.CHANNEL_CODE;
326 l_header_rec.TOTAL_AMOUNT := l_new_total_amount; -- Bug 2040332
327 l_header_rec.TOTAL_REVENUE_OPP_FORECAST_AMT := l_TOT_REVENUE_OPP_FORECAST_AMT; -- Added for ASNB
328 l_header_rec.CURRENCY_CODE := hdr.CURRENCY_CODE;
329 l_header_rec.DECISION_DATE := hdr.DECISION_DATE;
330 l_header_rec.WIN_PROBABILITY := hdr.WIN_PROBABILITY;
331 l_header_rec.CLOSE_REASON := hdr.CLOSE_REASON;
332 l_header_rec.CLOSE_COMPETITOR_CODE := hdr.CLOSE_COMPETITOR_CODE;
333 l_header_rec.CLOSE_COMPETITOR := hdr.CLOSE_COMPETITOR;
334 l_header_rec.CLOSE_COMMENT := hdr.CLOSE_COMMENT;
335 l_header_rec.DESCRIPTION := p_description;
336 l_header_rec.RANK := hdr.RANK;
337 l_header_rec.SOURCE_PROMOTION_ID := hdr.SOURCE_PROMOTION_ID;
338 l_header_rec.END_USER_CUSTOMER_ID := hdr.END_USER_CUSTOMER_ID;
339 l_header_rec.END_USER_ADDRESS_ID := hdr.END_USER_ADDRESS_ID;
340 l_header_rec.OWNER_SALESFORCE_ID := hdr.OWNER_SALESFORCE_ID;
341 l_header_rec.OWNER_SALES_GROUP_ID := hdr.OWNER_SALES_GROUP_ID;
342 --l_header_rec.OWNER_ASSIGN_DATE := hdr.OWNER_ASSIGN_DATE;
343 --l_header_rec.ORG_ID := hdr.ORG_ID; commented for bug 5477698
344 -- l_header_rec.ORG_ID := FND_PROFILE.Value('DEFAULT_ORG_ID');
345 l_header_rec.ORG_ID := MO_UTILS.get_default_org_id; -- added for bug 5219495
346 l_header_rec.NO_OPP_ALLOWED_FLAG := hdr.NO_OPP_ALLOWED_FLAG;
347 l_header_rec.DELETE_ALLOWED_FLAG := hdr.DELETE_ALLOWED_FLAG;
348 l_header_rec.ATTRIBUTE_CATEGORY := hdr.ATTRIBUTE_CATEGORY;
349 l_header_rec.ATTRIBUTE1 := hdr.ATTRIBUTE1;
350 l_header_rec.ATTRIBUTE2 := hdr.ATTRIBUTE2;
351 l_header_rec.ATTRIBUTE3 := hdr.ATTRIBUTE3;
352 l_header_rec.ATTRIBUTE4 := hdr.ATTRIBUTE4;
353 l_header_rec.ATTRIBUTE5 := hdr.ATTRIBUTE5;
354 l_header_rec.ATTRIBUTE6 := hdr.ATTRIBUTE6;
355 l_header_rec.ATTRIBUTE7 := hdr.ATTRIBUTE7;
356 l_header_rec.ATTRIBUTE8 := hdr.ATTRIBUTE8;
357 l_header_rec.ATTRIBUTE9 := hdr.ATTRIBUTE9;
358 l_header_rec.ATTRIBUTE10 := hdr.ATTRIBUTE10;
359 l_header_rec.ATTRIBUTE11 := hdr.ATTRIBUTE11;
360 l_header_rec.ATTRIBUTE12 := hdr.ATTRIBUTE12;
361 l_header_rec.ATTRIBUTE13 := hdr.ATTRIBUTE13;
362 l_header_rec.ATTRIBUTE14 := hdr.ATTRIBUTE14;
363 l_header_rec.ATTRIBUTE15 := hdr.ATTRIBUTE15;
364 l_header_rec.PARENT_PROJECT := hdr.PARENT_PROJECT;
365 l_header_rec.LEAD_SOURCE_CODE := hdr.LEAD_SOURCE_CODE;
366 l_header_rec.ORIG_SYSTEM_REFERENCE := hdr.ORIG_SYSTEM_REFERENCE;
367 l_header_rec.CLOSE_COMPETITOR_ID := hdr.CLOSE_COMPETITOR_ID;
368 l_header_rec.END_USER_CUSTOMER_NAME := hdr.END_USER_CUSTOMER_NAME;
369 l_header_rec.PRICE_LIST_ID := hdr.PRICE_LIST_ID;
370 l_header_rec.DELETED_FLAG := hdr.DELETED_FLAG;
371 l_header_rec.AUTO_ASSIGNMENT_TYPE := NULL; --bug1580008
372 l_header_rec.PRM_ASSIGNMENT_TYPE := NULL; --bug1580008
373 l_header_rec.CUSTOMER_BUDGET := hdr.CUSTOMER_BUDGET;
374 l_header_rec.METHODOLOGY_CODE := hdr.METHODOLOGY_CODE;
375 l_header_rec.SALES_METHODOLOGY_ID := l_new_sales_methodology_id;
376 l_header_rec.DECISION_TIMEFRAME_CODE := hdr.DECISION_TIMEFRAME_CODE;
377 l_header_rec.INCUMBENT_PARTNER_RESOURCE_ID := hdr.INCUMBENT_PARTNER_RESOURCE_ID;
378 l_header_rec.INCUMBENT_PARTNER_PARTY_ID := hdr.INCUMBENT_PARTNER_PARTY_ID;
379 l_header_rec.OFFER_ID := hdr.OFFER_ID;
380 l_header_rec.VEHICLE_RESPONSE_CODE := hdr.VEHICLE_RESPONSE_CODE;
381 l_header_rec.BUDGET_STATUS_CODE := hdr.BUDGET_STATUS_CODE;
382 l_header_rec.FOLLOWUP_DATE := hdr.FOLLOWUP_DATE;
383 l_header_rec.PRM_EXEC_SPONSOR_FLAG := hdr.PRM_EXEC_SPONSOR_FLAG;
384 l_header_rec.PRM_PRJ_LEAD_IN_PLACE_FLAG := hdr.PRM_PRJ_LEAD_IN_PLACE_FLAG;
385 l_header_rec.PRM_IND_CLASSIFICATION_CODE := hdr.PRM_IND_CLASSIFICATION_CODE;
386 l_header_rec.PRM_LEAD_TYPE := hdr.PRM_LEAD_TYPE;
387 l_header_rec.FREEZE_FLAG := hdr.FREEZE_FLAG;
388 l_header_rec.PRM_REFERRAL_CODE := hdr.PRM_REFERRAL_CODE;
389
390 l_close_competitor_id := hdr.close_competitor_id;
391
392 -- if customer is changed, donot copy project
393 if nvl(p_new_customer_id, -1) <> nvl(hdr.CUSTOMER_ID, -1) then
394 l_header_rec.PARENT_PROJECT := null;
395 end if;
396
397 -- Donot copy owner if not copying salesteam
398 IF (p_copy_Salesteam <> 'Y') THEN
399 l_header_rec.OWNER_SALESFORCE_ID := null;
400 l_header_rec.OWNER_SALES_GROUP_ID := null;
401 END IF;
402
403 -- Calling Private package: Create_OPP_HEADER
404 -- Hint: Primary key needs to be returned
405 AS_OPPORTUNITY_PUB.Create_opp_header(
406 P_Api_Version_Number => 2.0,
407 P_Init_Msg_List => FND_API.G_FALSE,
408 P_Commit => FND_API.G_FALSE,
409 P_Validation_Level => P_Validation_Level,
410 P_Check_Access_Flag => 'N',
411 P_Admin_Flag => P_Admin_Flag ,
412 P_Admin_Group_Id => P_Admin_Group_Id,
413 P_Identity_Salesforce_Id => P_Identity_Salesforce_Id,
414 p_salesgroup_id => p_salesgroup_id,
415 P_Profile_Tbl => P_Profile_tbl,
416 P_Partner_Cont_Party_Id => p_partner_cont_party_id,
417 P_Header_Rec => l_Header_Rec ,
418 X_LEAD_ID => x_LEAD_ID,
419 X_Return_Status => x_return_status,
420 X_Msg_Count => x_msg_count,
421 X_Msg_Data => x_msg_data);
422
423 -- Check return status from the above procedure call
424 IF x_return_status = FND_API.G_RET_STS_ERROR then
425 raise FND_API.G_EXC_ERROR;
426 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
427 raise FND_API.G_EXC_UNEXPECTED_ERROR;
428 END IF;
429
430 IF l_debug THEN
431 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
432
433 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
434 'Private API: Create_opp_header fail');
435 ELSE
436 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
437 'Private API: Created lead '|| x_lead_id);
438 END IF;
439 END IF;
440
441 END LOOP;
442
443 -- Bug 3426788
444 -- Add the persons having sales credits to the sales team if profile is ON
445 IF ((p_copy_Salesteam <> 'Y') AND (p_copy_sales_credits = 'Y') AND ( FND_PROFILE.Value('AS_ENFORCE_SALES_TEAM') = 'Y')) THEN
446 l_cre_st_for_sc_flag := 'Y';
447 END IF;
448
449 --
450 -- Copy Sales team
451 --
452
453 IF (p_copy_salesteam = 'Y' OR l_cre_st_for_sc_flag = 'Y') THEN
454
455 -- Copy salesteam. At this point TAP/Owner logic has already run.
456 FOR str IN c_salesteam(p_lead_id,l_cre_st_for_sc_flag) LOOP
457
458 l_Sales_Team_Rec.Last_Update_Date := SYSDATE;
459 l_Sales_Team_Rec.Last_Updated_By := FND_GLOBAL.User_Id;
460 l_Sales_Team_Rec.Creation_Date := SYSDATE;
461 l_Sales_Team_Rec.Created_By := FND_GLOBAL.User_Id;
462 l_Sales_Team_Rec.Last_Update_Login := FND_GLOBAL.Conc_Login_Id;
463 --l_Sales_Team_Rec.owner_flag := str.owner_flag;
464 l_Sales_Team_Rec.Freeze_Flag := str.freeze_flag;
465 l_Sales_Team_Rec.Reassign_Flag := str.reassign_flag;
466 l_Sales_Team_Rec.Team_Leader_Flag := str.team_leader_flag;
467 l_Sales_Team_Rec.Person_Id := str.person_id;
468 l_Sales_Team_Rec.Customer_Id := nvl(p_new_customer_id, str.customer_id);
469
470 -- if customer is changed, change the address to the new address
471 if nvl(p_new_customer_id, -1) <> nvl(str.customer_id, -1) then
472 l_Sales_Team_Rec.Address_Id := p_new_address_id;
473 else
474 l_Sales_Team_Rec.Address_Id := str.address_id;
475 end if;
476
477 l_Sales_Team_Rec.Salesforce_id := str.salesforce_id;
478 l_Sales_Team_Rec.Created_Person_Id := str.created_person_id;
479 l_Sales_Team_Rec.Partner_Customer_id := str.partner_customer_id;
480 l_Sales_Team_Rec.Partner_Address_id := str.partner_address_id;
481 l_Sales_Team_Rec.Lead_Id := x_lead_id;
482 l_Sales_Team_Rec.Freeze_Date := str.freeze_date;
483 l_Sales_Team_Rec.Reassign_Reason := str.reassign_reason;
484 l_Sales_Team_Rec.Reassign_request_date := str.reassign_request_date;
485 l_Sales_Team_Rec.Reassign_requested_person_id := str.reassign_requested_person_id;
486 l_Sales_Team_Rec.Attribute_Category := str.attribute_category;
487 l_Sales_Team_Rec.Attribute1 := str.attribute1;
488 l_Sales_Team_Rec.Attribute2 := str.attribute2;
489 l_Sales_Team_Rec.Attribute3 := str.attribute3;
490 l_Sales_Team_Rec.Attribute4 := str.attribute4;
491 l_Sales_Team_Rec.Attribute5 := str.attribute5;
492 l_Sales_Team_Rec.Attribute6 := str.attribute6;
493 l_Sales_Team_Rec.Attribute7 := str.attribute7;
494 l_Sales_Team_Rec.Attribute8 := str.attribute8;
495 l_Sales_Team_Rec.Attribute9 := str.attribute9;
496 l_Sales_Team_Rec.Attribute10 := str.attribute10;
497 l_Sales_Team_Rec.Attribute11 := str.attribute11;
498 l_Sales_Team_Rec.Attribute12 := str.attribute12;
499 l_Sales_Team_Rec.Attribute13 := str.attribute13;
500 l_Sales_Team_Rec.Attribute14 := str.attribute14;
501 l_Sales_Team_Rec.Attribute15 := str.attribute15;
502 l_Sales_Team_Rec.Sales_group_id := str.sales_group_id;
503 l_Sales_Team_Rec.Sales_lead_id := str.sales_lead_id;
504 --l_Sales_Team_Rec.Internal_update_access := str.internal_update_access;
505 l_Sales_Team_Rec.Partner_Cont_Party_Id := str.partner_cont_party_id;
506 l_Sales_Team_Rec.Salesforce_Role_Code := str.salesforce_role_code;
507 l_Sales_Team_Rec.Salesforce_Relationship_Code := str.salesforce_relationship_code;
508 -- Added for ASNB
509 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y' THEN
510 l_Sales_Team_Rec.contributor_flag := str.contributor_flag;
511 END IF;
512
513 l_insert := true;
514 if (nvl(str.owner_flag,'N') = 'Y' AND
515 nvl(str.salesforce_id,-99) = nvl(l_header_rec.OWNER_SALESFORCE_ID,-99) AND
516 nvl(str.sales_group_id,-99) = nvl(l_header_rec.OWNER_SALES_GROUP_ID,-99)) then
517 open c_owner_in_salesTeam(x_lead_id,str.salesforce_id,str.sales_group_id);
518 fetch c_owner_in_salesTeam into l_val,l_date;
519 if (c_owner_in_salesTeam%FOUND) then
520 l_insert := false;
521 end if;
522 Close c_owner_in_salesTeam;
523 end if;
524
525 if (l_insert) then
526 IF l_debug THEN
527 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
528 'Calling Create_SalesTeam');
529 END IF;
530
531 AS_ACCESS_PUB.Create_SalesTeam (
532 p_api_version_number => 2.0
533 ,p_init_msg_list => FND_API.G_FALSE
534 ,p_commit => FND_API.G_FALSE
535 ,p_validation_level => p_Validation_Level
536 ,p_access_profile_rec => l_access_profile_rec
537 ,p_check_access_flag => 'N' -- P_Check_Access_flag
538 ,p_admin_flag => P_Admin_Flag
539 ,p_admin_group_id => P_Admin_Group_Id
540 ,p_identity_salesforce_id => P_Identity_Salesforce_Id
541 ,p_sales_team_rec => l_Sales_Team_Rec
542 ,X_Return_Status => x_Return_Status
543 ,X_Msg_Count => X_Msg_Count
544 ,X_Msg_Data => X_Msg_Data
545 ,x_access_id => l_Access_Id
546 );
547
548 -- Debug Message
549 IF l_debug THEN
550 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
551 'Create_SalesTeam:l_access_id > ' || l_access_id);
552
553 END IF;
554
555 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
556 RAISE FND_API.G_EXC_ERROR;
557 END IF;
558 -- Begin Added for ASNB
559 IF nvl(fnd_profile.value('AS_ACTIVATE_SALES_INTEROP'), 'N') = 'Y' AND
560 P_Identity_Salesforce_Id = l_Sales_Team_Rec.salesforce_id AND
561 nvl(l_Sales_Team_Rec.contributor_flag,'N') = 'Y'
562 THEN
563 open c_log_user_in_salesTeam(x_lead_id,l_Sales_Team_Rec.salesforce_id,l_Sales_Team_Rec.sales_group_id);
564 fetch c_log_user_in_salesTeam into l_val,l_date;
565 close c_log_user_in_salesTeam;
566 l_Sales_Team_Rec.last_update_date := l_date;
567 l_Sales_Team_Rec.access_id := l_val;
568 l_Sales_Team_Rec.Freeze_Flag := 'Y';
569 AS_ACCESS_PUB.Update_SalesTeam (
570 p_api_version_number => 2.0
571 ,p_init_msg_list => FND_API.G_FALSE
572 ,p_commit => FND_API.G_FALSE
573 ,p_validation_level => p_Validation_Level
574 ,p_access_profile_rec => l_access_profile_rec
575 ,p_check_access_flag => 'N' -- P_Check_Access_flag
576 ,p_admin_flag => P_Admin_Flag
577 ,p_admin_group_id => P_Admin_Group_Id
578 ,p_identity_salesforce_id => P_Identity_Salesforce_Id
579 ,p_sales_team_rec => l_Sales_Team_Rec
580 ,X_Return_Status => x_Return_Status
581 ,X_Msg_Count => X_Msg_Count
582 ,X_Msg_Data => X_Msg_Data
583 ,x_access_id => l_Access_Id
584 );
585
586 END IF;
587 -- End Added for ASNB
588 ELSE
589 IF l_debug THEN
590 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
591 'Calling Update_SalesTeam: access_id > ' || l_val);
592 END IF;
593 l_Sales_Team_Rec.last_update_date := l_date;
594 l_Sales_Team_Rec.access_id := l_val;
595 --dbms_output.put_line('access_id ' || l_Sales_Team_Rec.access_id);
596 --dbms_output.put_line('date from db ' || to_char(l_date,'MM:DD:YYYY HH24:MI:SS'));
597 AS_ACCESS_PUB.Update_SalesTeam (
598 p_api_version_number => 2.0
599 ,p_init_msg_list => FND_API.G_FALSE
600 ,p_commit => FND_API.G_FALSE
601 ,p_validation_level => p_Validation_Level
602 ,p_access_profile_rec => l_access_profile_rec
603 ,p_check_access_flag => 'N' -- P_Check_Access_flag
604 ,p_admin_flag => P_Admin_Flag
605 ,p_admin_group_id => P_Admin_Group_Id
606 ,p_identity_salesforce_id => P_Identity_Salesforce_Id
607 ,p_sales_team_rec => l_Sales_Team_Rec
608 ,X_Return_Status => x_Return_Status
609 ,X_Msg_Count => X_Msg_Count
610 ,X_Msg_Data => X_Msg_Data
611 ,x_access_id => l_Access_Id
612 );
613
614 -- Debug Message
615 IF l_debug THEN
616 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
617 'Update_SalesTeam: Done');
618 END IF;
619
620 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
621 RAISE FND_API.G_EXC_ERROR;
622 END IF;
623 END IF;
624 END LOOP;
625 END IF;
626 --
627 -- Copy Opportunity Lines and line details - Sales Credits,
628 -- Competitor Products and Decision Factors
629 --
630
631 IF(l_copy_opp_lines = 'Y') THEN
632 l_header_rec.lead_id := x_lead_id;
633
634 FOR lr IN c_lines(p_lead_id) LOOP
635 l_lead_line_id := null;
636
637 -- Copy lines
638 AS_LEAD_LINES_PKG.Insert_Row(
639 px_LEAD_LINE_ID => l_LEAD_LINE_ID,
640 p_LAST_UPDATE_DATE => SYSDATE,
641 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
642 p_CREATION_DATE => SYSDATE,
643 p_CREATED_BY => FND_GLOBAL.USER_ID,
644 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
645 p_REQUEST_ID => lr.REQUEST_ID,
646 p_PROGRAM_APPLICATION_ID => lr.PROGRAM_APPLICATION_ID,
647 p_PROGRAM_ID => lr.PROGRAM_ID,
648 p_PROGRAM_UPDATE_DATE => lr.PROGRAM_UPDATE_DATE,
649 p_LEAD_ID => x_LEAD_ID,
650 p_INTEREST_TYPE_ID => lr.INTEREST_TYPE_ID,
651 p_PRIMARY_INTEREST_CODE_ID => lr.PRIMARY_INTEREST_CODE_ID,
652 p_SECONDARY_INTEREST_CODE_ID => lr.SECONDARY_INTEREST_CODE_ID,
653 p_INTEREST_STATUS_CODE => lr.INTEREST_STATUS_CODE,
654 p_INVENTORY_ITEM_ID => lr.INVENTORY_ITEM_ID,
655 p_ORGANIZATION_ID => lr.ORGANIZATION_ID,
656 p_UOM_CODE => lr.UOM_CODE,
657 p_QUANTITY => lr.QUANTITY,
658 p_TOTAL_AMOUNT => lr.TOTAL_AMOUNT,
659 p_SALES_STAGE_ID => lr.SALES_STAGE_ID,
660 p_WIN_PROBABILITY => lr.WIN_PROBABILITY,
661 p_DECISION_DATE => lr.DECISION_DATE,
662 p_ORG_ID => lr.ORG_ID,
663 p_ATTRIBUTE_CATEGORY => lr.ATTRIBUTE_CATEGORY,
664 p_ATTRIBUTE1 => lr.ATTRIBUTE1,
665 p_ATTRIBUTE2 => lr.ATTRIBUTE2,
666 p_ATTRIBUTE3 => lr.ATTRIBUTE3,
667 p_ATTRIBUTE4 => lr.ATTRIBUTE4,
668 p_ATTRIBUTE5 => lr.ATTRIBUTE5,
669 p_ATTRIBUTE6 => lr.ATTRIBUTE6,
670 p_ATTRIBUTE7 => lr.ATTRIBUTE7,
671 p_ATTRIBUTE8 => lr.ATTRIBUTE8,
672 p_ATTRIBUTE9 => lr.ATTRIBUTE9,
673 p_ATTRIBUTE10 => lr.ATTRIBUTE10,
674 p_ATTRIBUTE11 => lr.ATTRIBUTE11,
675 p_ATTRIBUTE12 => lr.ATTRIBUTE12,
676 p_ATTRIBUTE13 => lr.ATTRIBUTE13,
677 p_ATTRIBUTE14 => lr.ATTRIBUTE14,
678 p_ATTRIBUTE15 => lr.ATTRIBUTE15,
679 p_STATUS_CODE => lr.STATUS_CODE,
680 p_CHANNEL_CODE => lr.CHANNEL_CODE,
681 p_QUOTED_LINE_FLAG => lr.QUOTED_LINE_FLAG,
682 p_PRICE => lr.PRICE,
683 p_PRICE_VOLUME_MARGIN => lr.PRICE_VOLUME_MARGIN,
684 p_SHIP_DATE => lr.SHIP_DATE,
685 p_FORECAST_DATE => lr.FORECAST_DATE,
686 p_ROLLING_FORECAST_FLAG => lr.ROLLING_FORECAST_FLAG,
687 p_SOURCE_PROMOTION_ID => lr.SOURCE_PROMOTION_ID,
688 p_OFFER_ID => lr.OFFER_ID,
689 p_PRODUCT_CATEGORY_ID => lr.PRODUCT_CATEGORY_ID,
690 p_PRODUCT_CAT_SET_ID => lr.PRODUCT_CAT_SET_ID);
691
692 IF l_lead_line_id is null THEN
693 IF l_debug THEN
694 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
695 'Private API: as_lead_lines_pkg.insert_row fail');
696 END IF;
697
698 RAISE FND_API.G_EXC_ERROR;
699 ELSE
700 IF l_debug THEN
701 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
702 'Private API: as_lead_lines_pkg.insert_row '|| l_lead_line_id);
703 END IF;
704
705 END IF;
706
707 -- Copy Sales Credits
708 IF (p_copy_sales_credits = 'Y') THEN
709 FOR scr IN c_sales_credits(p_lead_id, lr.lead_line_id) LOOP
710 l_sales_credit_id := null;
711 AS_SALES_CREDITS_PKG.Insert_Row(
712 px_SALES_CREDIT_ID => l_SALES_CREDIT_ID,
713 p_LAST_UPDATE_DATE => SYSDATE,
714 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
715 p_CREATION_DATE => SYSDATE,
716 p_CREATED_BY => FND_GLOBAL.USER_ID,
717 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
718 p_REQUEST_ID => scr.REQUEST_ID,
719 p_PROGRAM_APPLICATION_ID => scr.PROGRAM_APPLICATION_ID,
720 p_PROGRAM_ID => scr.PROGRAM_ID,
721 p_PROGRAM_UPDATE_DATE => scr.PROGRAM_UPDATE_DATE,
722 p_LEAD_ID => x_LEAD_ID,
723 p_LEAD_LINE_ID => l_LEAD_LINE_ID,
724 p_SALESFORCE_ID => scr.SALESFORCE_ID,
725 p_PERSON_ID => scr.PERSON_ID,
726 p_SALESGROUP_ID => scr.SALESGROUP_ID,
727 p_PARTNER_CUSTOMER_ID => scr.PARTNER_CUSTOMER_ID,
728 p_PARTNER_ADDRESS_ID => scr.PARTNER_ADDRESS_ID,
729 p_REVENUE_AMOUNT => scr.REVENUE_AMOUNT,
730 p_REVENUE_PERCENT => scr.REVENUE_PERCENT,
731 p_QUOTA_CREDIT_AMOUNT => scr.QUOTA_CREDIT_AMOUNT,
732 p_QUOTA_CREDIT_PERCENT => scr.QUOTA_CREDIT_PERCENT,
733 p_ATTRIBUTE_CATEGORY => scr.ATTRIBUTE_CATEGORY,
734 p_ATTRIBUTE1 => scr.ATTRIBUTE1,
735 p_ATTRIBUTE2 => scr.ATTRIBUTE2,
736 p_ATTRIBUTE3 => scr.ATTRIBUTE3,
737 p_ATTRIBUTE4 => scr.ATTRIBUTE4,
738 p_ATTRIBUTE5 => scr.ATTRIBUTE5,
739 p_ATTRIBUTE6 => scr.ATTRIBUTE6,
740 p_ATTRIBUTE7 => scr.ATTRIBUTE7,
741 p_ATTRIBUTE8 => scr.ATTRIBUTE8,
742 p_ATTRIBUTE9 => scr.ATTRIBUTE9,
743 p_ATTRIBUTE10 => scr.ATTRIBUTE10,
744 p_ATTRIBUTE11 => scr.ATTRIBUTE11,
745 p_ATTRIBUTE12 => scr.ATTRIBUTE12,
746 p_ATTRIBUTE13 => scr.ATTRIBUTE13,
747 p_ATTRIBUTE14 => scr.ATTRIBUTE14,
748 p_ATTRIBUTE15 => scr.ATTRIBUTE15,
749 p_MANAGER_REVIEW_FLAG => scr.MANAGER_REVIEW_FLAG,
750 p_MANAGER_REVIEW_DATE => scr.MANAGER_REVIEW_DATE,
751 p_ORIGINAL_SALES_CREDIT_ID => scr.ORIGINAL_SALES_CREDIT_ID,
752 p_CREDIT_PERCENT => scr.CREDIT_PERCENT,
753 p_CREDIT_AMOUNT => scr.CREDIT_AMOUNT,
754 p_CREDIT_TYPE_ID => scr.CREDIT_TYPE_ID,
755 -- The following fields are not passed before ASNB
756 p_OPP_WORST_FORECAST_AMOUNT => scr.OPP_WORST_FORECAST_AMOUNT,
757 p_OPP_FORECAST_AMOUNT => scr.OPP_FORECAST_AMOUNT,
758 p_OPP_BEST_FORECAST_AMOUNT => scr.OPP_BEST_FORECAST_AMOUNT,
759 P_DEFAULTED_FROM_OWNER_FLAG =>scr.DEFAULTED_FROM_OWNER_FLAG -- Added for ASNB
760 );
761
762 IF l_sales_credit_id is null THEN
763 IF l_debug THEN
764 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
765 'Private API: as_sales_credits_pkg.insert_row fail');
766 END IF;
767
768 RAISE FND_API.G_EXC_ERROR;
769 ELSE
770 IF l_debug THEN
771 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
772 'Private API: as_sales_credits_pkg.insert_row '|| l_sales_credit_id);
773 END IF;
774
775 END IF;
776
777 END LOOP; -- SC loop
778
779 ELSE -- default sales credits to the user
780 IF l_forecast_credit_type_id IS NULL THEN
781 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
782 IF l_debug THEN
783 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
784 'The profile AS_FORECAST_CREDIT_TYPE_ID is null');
785 END IF;
786
787 END IF;
788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
789 END IF;
790
791 l_sales_credit_rec.last_update_date := SYSDATE;
792 l_sales_credit_rec.last_updated_by := FND_GLOBAL.USER_ID;
793 l_sales_credit_rec.creation_Date := SYSDATE;
794 l_sales_credit_rec.created_by := FND_GLOBAL.USER_ID;
795 l_sales_credit_rec.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
796 l_sales_credit_rec.lead_id := x_lead_id;
797 l_sales_credit_rec.lead_line_id := l_LEAD_LINE_ID;
798 l_sales_credit_rec.salesforce_id := l_identity_sales_member_rec.salesforce_id;
799 l_sales_credit_rec.person_id := l_identity_sales_member_rec.employee_person_id;
800 l_sales_credit_rec.salesgroup_id := p_salesgroup_id;
801
802 IF (l_identity_sales_member_rec.partner_customer_id is NOT NULL) and
803 (l_identity_sales_member_rec.partner_customer_id <>FND_API.G_MISS_NUM)
804 THEN
805 l_sales_credit_rec.partner_customer_id := l_identity_sales_member_rec.partner_customer_id;
806 l_sales_credit_rec.partner_address_id := l_identity_sales_member_rec.partner_address_id;
807 ELSE
808 l_sales_credit_rec.partner_customer_id := l_identity_sales_member_rec.partner_contact_id;
809 END IF;
810
811 l_sales_credit_rec.credit_type_id := l_forecast_credit_type_id;
812 l_sales_credit_rec.credit_amount := lr.total_amount;
813 l_sales_credit_rec.credit_percent := 100;
814
815 l_sales_credit_tbl(1) := l_sales_credit_rec;
816
817 AS_OPP_sales_credit_PVT.Create_sales_credits(
818 P_Api_Version_Number => 2.0,
819 P_Init_Msg_List => FND_API.G_FALSE,
820 P_Commit => FND_API.G_FALSE,
821 P_Validation_Level => P_Validation_Level,
822 P_Check_Access_Flag => FND_API.G_FALSE,
823 P_Admin_Flag => FND_API.G_FALSE,
824 P_Admin_Group_Id => P_Admin_Group_Id,
825 P_Identity_Salesforce_Id => P_Identity_Salesforce_Id,
826 P_Partner_Cont_Party_Id => p_partner_cont_party_id,
827 P_Profile_Tbl => P_Profile_tbl,
828 P_Sales_Credit_Tbl => l_sales_credit_tbl,
829 X_Sales_Credit_Out_Tbl => x_sales_credit_out_tbl,
830 X_Return_Status => x_return_status,
831 X_Msg_Count => x_msg_count,
832 X_Msg_Data => x_msg_data);
833
834 -- Check return status from the above procedure call
835 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
836 THEN
837 IF l_debug THEN
838 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
839 'Copy Line: Create_Sales_credit fail' );
840 END IF;
841
842 raise FND_API.G_EXC_ERROR;
843 END IF;
844
845 SELECT sum(OPP_FORECAST_AMOUNT) INTO l_TOT_REVENUE_OPP_FORECAST_AMT
846 FROM AS_SALES_CREDITS
847 WHERE lead_id = l_Header_rec.lead_id AND
848 credit_type_id = l_forecast_credit_type_id;
849
850 END IF; -- p_copy_sales_credits
851
852 -- Update Header with forecast amount since create_opp_header
853 -- defaults it to null
854 UPDATE AS_LEADS_ALL
855 SET TOTAL_REVENUE_OPP_FORECAST_AMT = l_TOT_REVENUE_OPP_FORECAST_AMT
856 WHERE lead_id = l_Header_rec.lead_id;
857
858
859 IF ( p_copy_lead_competitors = 'Y') THEN
860
861 -- Copy Competitor Products
862 FOR cpdr IN c_competitor_products(lr.lead_line_id) LOOP
863 l_lead_competitor_prod_id := NULL;
864 -- Invoke table handler(AS_LEAD_COMP_PRODUCTS_PKG.Insert_Row)
865 AS_LEAD_COMP_PRODUCTS_PKG.Insert_Row(
866 p_ATTRIBUTE15 => cpdr.ATTRIBUTE15,
867 p_ATTRIBUTE14 => cpdr.ATTRIBUTE14,
868 p_ATTRIBUTE13 => cpdr.ATTRIBUTE13,
869 p_ATTRIBUTE12 => cpdr.ATTRIBUTE12,
870 p_ATTRIBUTE11 => cpdr.ATTRIBUTE11,
871 p_ATTRIBUTE10 => cpdr.ATTRIBUTE10,
872 p_ATTRIBUTE9 => cpdr.ATTRIBUTE9,
873 p_ATTRIBUTE8 => cpdr.ATTRIBUTE8,
874 p_ATTRIBUTE7 => cpdr.ATTRIBUTE7,
875 p_ATTRIBUTE6 => cpdr.ATTRIBUTE6,
876 p_ATTRIBUTE4 => cpdr.ATTRIBUTE4,
877 p_ATTRIBUTE5 => cpdr.ATTRIBUTE5,
878 p_ATTRIBUTE2 => cpdr.ATTRIBUTE2,
879 p_ATTRIBUTE3 => cpdr.ATTRIBUTE3,
880 p_ATTRIBUTE1 => cpdr.ATTRIBUTE1,
881 p_ATTRIBUTE_CATEGORY => cpdr.ATTRIBUTE_CATEGORY,
882 p_PROGRAM_ID => cpdr.PROGRAM_ID,
883 p_PROGRAM_UPDATE_DATE => cpdr.PROGRAM_UPDATE_DATE,
884 p_PROGRAM_APPLICATION_ID => cpdr.PROGRAM_APPLICATION_ID,
885 p_REQUEST_ID => cpdr.REQUEST_ID,
886 p_WIN_LOSS_STATUS => cpdr.WIN_LOSS_STATUS,
887 p_COMPETITOR_PRODUCT_ID => cpdr.COMPETITOR_PRODUCT_ID,
888 p_LEAD_LINE_ID => l_LEAD_LINE_ID,
889 p_LEAD_ID => x_LEAD_ID,
890 px_LEAD_COMPETITOR_PROD_ID => l_LEAD_COMPETITOR_PROD_ID,
891 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
892 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
893 p_LAST_UPDATE_DATE => SYSDATE,
894 p_CREATED_BY => FND_GLOBAL.USER_ID,
895 p_CREATION_DATE => SYSDATE);
896
897 IF l_lead_competitor_prod_id is null THEN
898 IF l_debug THEN
899 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
900 'Private API: as_lead_comp_products_pkg.insert_row fail');
901 END IF;
902
903 RAISE FND_API.G_EXC_ERROR;
904 ELSE
905 IF l_debug THEN
906 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
907 'Private API: as_lead_comp_products_pkg.insert_row '|| l_lead_competitor_prod_id);
908 END IF;
909
910 END IF;
911 END LOOP; -- CPD loop
912
913 -- Copy Decision Factors
914 FOR dfcr IN c_decision_factors(lr.lead_line_id) LOOP
915 l_lead_decision_factor_id := NULL;
916 AS_LEAD_DECISION_FACTORS_PKG.Insert_Row(
917 p_ATTRIBUTE15 => dfcr.ATTRIBUTE15,
918 p_ATTRIBUTE14 => dfcr.ATTRIBUTE14,
919 p_ATTRIBUTE13 => dfcr.ATTRIBUTE13,
920 p_ATTRIBUTE12 => dfcr.ATTRIBUTE12,
921 p_ATTRIBUTE11 => dfcr.ATTRIBUTE11,
922 p_ATTRIBUTE10 => dfcr.ATTRIBUTE10,
923 p_ATTRIBUTE9 => dfcr.ATTRIBUTE9,
924 p_ATTRIBUTE8 => dfcr.ATTRIBUTE8,
925 p_ATTRIBUTE7 => dfcr.ATTRIBUTE7,
926 p_ATTRIBUTE6 => dfcr.ATTRIBUTE6,
927 p_ATTRIBUTE5 => dfcr.ATTRIBUTE5,
928 p_ATTRIBUTE4 => dfcr.ATTRIBUTE4,
929 p_ATTRIBUTE3 => dfcr.ATTRIBUTE3,
930 p_ATTRIBUTE2 => dfcr.ATTRIBUTE2,
931 p_ATTRIBUTE1 => dfcr.ATTRIBUTE1,
932 p_ATTRIBUTE_CATEGORY => dfcr.ATTRIBUTE_CATEGORY,
933 p_PROGRAM_UPDATE_DATE => dfcr.PROGRAM_UPDATE_DATE,
934 p_PROGRAM_ID => dfcr.PROGRAM_ID,
935 p_PROGRAM_APPLICATION_ID => dfcr.PROGRAM_APPLICATION_ID,
936 p_REQUEST_ID => dfcr.REQUEST_ID,
937 p_DECISION_RANK => dfcr.DECISION_RANK,
938 p_DECISION_PRIORITY_CODE => dfcr.DECISION_PRIORITY_CODE,
939 p_DECISION_FACTOR_CODE => dfcr.DECISION_FACTOR_CODE,
940 px_LEAD_DECISION_FACTOR_ID => l_LEAD_DECISION_FACTOR_ID,
941 p_LEAD_LINE_ID => l_LEAD_LINE_ID,
942 p_CREATE_BY => FND_GLOBAL.USER_ID,
943 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
944 p_LAST_UPDATE_DATE => SYSDATE,
945 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
946 p_CREATION_DATE => SYSDATE);
947
948 IF l_lead_decision_factor_id is null THEN
949 IF l_debug THEN
950 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
951 'Private API: as_lead_decision_factors_pkg.insert_row fail');
952 END IF;
953
954 RAISE FND_API.G_EXC_ERROR;
955 ELSE
956 IF l_debug THEN
957 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
958 'Private API: as_lead_decision_factors_pkg.insert_row '|| l_lead_decision_factor_id );
959 END IF;
960 END IF;
961 END LOOP; -- DFC loop
962 END IF; -- copy competitors
963 END LOOP; -- line loop
964
965 END IF; -- If (p_copy_opp_line = 'Y')
966
967 --
968 -- Copy opportunity contacts
969 --
970 IF(p_copy_lead_contacts = 'Y') THEN
971 FOR cnr IN c_contacts(p_lead_id) LOOP
972 l_lead_contact_id := NULL;
973
974 AS_LEAD_CONTACTS_PKG.Insert_Row(
975 px_LEAD_CONTACT_ID => l_LEAD_CONTACT_ID,
976 p_LEAD_ID => x_LEAD_ID,
977 p_CONTACT_ID => cnr.CONTACT_ID,
978 p_LAST_UPDATE_DATE => SYSDATE,
979 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
980 p_CREATION_DATE => SYSDATE,
981 p_CREATED_BY => FND_GLOBAL.USER_ID,
982 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
983 p_REQUEST_ID => cnr.REQUEST_ID,
984 p_PROGRAM_APPLICATION_ID => cnr.PROGRAM_APPLICATION_ID,
985 p_PROGRAM_ID => cnr.PROGRAM_ID,
986 p_PROGRAM_UPDATE_DATE => cnr.PROGRAM_UPDATE_DATE,
987 p_ENABLED_FLAG => cnr.ENABLED_FLAG,
988 p_CUSTOMER_ID => nvl(p_new_customer_id, cnr.CUSTOMER_ID),
989 p_ADDRESS_ID => p_new_address_id,
990 p_RANK => cnr.RANK,
991 p_PHONE_ID => cnr.PHONE_ID,
992 p_ATTRIBUTE_CATEGORY => cnr.ATTRIBUTE_CATEGORY,
993 p_ATTRIBUTE1 => cnr.ATTRIBUTE1,
994 p_ATTRIBUTE2 => cnr.ATTRIBUTE2,
995 p_ATTRIBUTE3 => cnr.ATTRIBUTE3,
996 p_ATTRIBUTE4 => cnr.ATTRIBUTE4,
997 p_ATTRIBUTE5 => cnr.ATTRIBUTE5,
998 p_ATTRIBUTE6 => cnr.ATTRIBUTE6,
999 p_ATTRIBUTE7 => cnr.ATTRIBUTE7,
1000 p_ATTRIBUTE8 => cnr.ATTRIBUTE8,
1001 p_ATTRIBUTE9 => cnr.ATTRIBUTE9,
1002 p_ATTRIBUTE10 => cnr.ATTRIBUTE10,
1003 p_ATTRIBUTE11 => cnr.ATTRIBUTE11,
1004 p_ATTRIBUTE12 => cnr.ATTRIBUTE12,
1005 p_ATTRIBUTE13 => cnr.ATTRIBUTE13,
1006 p_ATTRIBUTE14 => cnr.ATTRIBUTE14,
1007 p_ATTRIBUTE15 => cnr.ATTRIBUTE15,
1008 p_ORG_ID => cnr.ORG_ID,
1009 p_PRIMARY_CONTACT_FLAG => cnr.PRIMARY_CONTACT_FLAG,
1010 p_ROLE => cnr.ROLE,
1011 p_CONTACT_PARTY_ID => cnr.CONTACT_PARTY_ID);
1012
1013 IF l_lead_contact_id is null THEN
1014 IF l_debug THEN
1015 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1016 'Private API: as_lead_lines_pkg.insert_row fail');
1017 END IF;
1018
1019 RAISE FND_API.G_EXC_ERROR;
1020 ELSE
1021 IF l_debug THEN
1022 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1023 'Private API: as_lead_contacts_pkg.insert_row '|| l_lead_contact_id);
1024 END IF;
1025
1026 END IF;
1027 END LOOP;
1028 END IF;
1029
1030 --
1031 -- Copy opportunity competitors
1032 --
1033 IF(p_copy_lead_competitors = 'Y') THEN
1034 FOR cmpr IN c_competitors(p_lead_id) LOOP
1035 l_lead_competitor_id := NULL;
1036
1037 IF (cmpr.COMPETITOR_ID <> nvl(l_close_competitor_id, -1)) THEN
1038 AS_LEAD_COMPETITORS_PKG.Insert_Row(
1039 px_LEAD_COMPETITOR_ID => l_LEAD_COMPETITOR_ID,
1040 p_LAST_UPDATE_DATE => SYSDATE,
1041 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1042 p_CREATION_DATE => SYSDATE,
1043 p_CREATED_BY => FND_GLOBAL.USER_ID,
1044 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
1045 p_REQUEST_ID => cmpr.REQUEST_ID,
1046 p_PROGRAM_APPLICATION_ID =>cmpr.PROGRAM_APPLICATION_ID,
1047 p_PROGRAM_ID => cmpr.PROGRAM_ID,
1048 p_PROGRAM_UPDATE_DATE => cmpr.PROGRAM_UPDATE_DATE,
1049 p_LEAD_ID => x_LEAD_ID,
1050 p_COMPETITOR_CODE => cmpr.COMPETITOR_CODE,
1051 p_COMPETITOR => cmpr.COMPETITOR,
1052 p_PRODUCTS => cmpr.PRODUCTS,
1053 p_COMMENTS => cmpr.COMMENTS,
1054 p_ATTRIBUTE_CATEGORY => cmpr.ATTRIBUTE_CATEGORY,
1055 p_ATTRIBUTE1 => cmpr.ATTRIBUTE1,
1056 p_ATTRIBUTE2 => cmpr.ATTRIBUTE2,
1057 p_ATTRIBUTE3 => cmpr.ATTRIBUTE3,
1058 p_ATTRIBUTE4 => cmpr.ATTRIBUTE4,
1059 p_ATTRIBUTE5 => cmpr.ATTRIBUTE5,
1060 p_ATTRIBUTE6 => cmpr.ATTRIBUTE6,
1061 p_ATTRIBUTE7 => cmpr.ATTRIBUTE7,
1062 p_ATTRIBUTE8 => cmpr.ATTRIBUTE8,
1063 p_ATTRIBUTE9 => cmpr.ATTRIBUTE9,
1064 p_ATTRIBUTE10 => cmpr.ATTRIBUTE10,
1065 p_ATTRIBUTE11 => cmpr.ATTRIBUTE11,
1066 p_ATTRIBUTE12 => cmpr.ATTRIBUTE12,
1067 p_ATTRIBUTE13 => cmpr.ATTRIBUTE13,
1068 p_ATTRIBUTE14 => cmpr.ATTRIBUTE14,
1069 p_ATTRIBUTE15 => cmpr.ATTRIBUTE15,
1070 p_WIN_LOSS_STATUS => cmpr.WIN_LOSS_STATUS,
1071 p_COMPETITOR_RANK => cmpr.COMPETITOR_RANK,
1072 p_RELATIONSHIP_PARTY_ID => cmpr.RELATIONSHIP_PARTY_ID,
1073 p_COMPETITOR_ID => cmpr.COMPETITOR_ID);
1074
1075 IF l_lead_competitor_id is null THEN
1076 IF l_debug THEN
1077 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1078 'Private API: as_lead_competitors_pkg.insert_row fail');
1079 END IF;
1080 RAISE FND_API.G_EXC_ERROR;
1081 ELSE
1082 IF l_debug THEN
1083 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1084 'Private API: as_lead_competitors_pkg.insert_row '|| l_lead_competitor_id);
1085 END IF;
1086 END IF;
1087 END IF;
1088
1089 END LOOP; -- cmpr loop
1090 END IF; -- p_copy_lead_competitors
1091
1092 -- Run TAP to reassign salesteam
1093 AS_RTTAP_OPPTY.RTTAP_WRAPPER(
1094 P_Api_Version_Number => 1.0,
1095 P_Init_Msg_List => FND_API.G_FALSE,
1096 P_Commit => FND_API.G_FALSE,
1097 p_lead_id => x_LEAD_ID,
1098 X_Return_Status => x_return_status,
1099 X_Msg_Count => x_msg_count,
1100 X_Msg_Data => x_msg_data
1101 );
1102
1103 -- Check return status from the above procedure call
1104 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1105 THEN
1106 IF l_debug THEN
1107 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1108 'Territory Assignment Call Failed' );
1109 END IF;
1110 raise FND_API.G_EXC_ERROR;
1111 END IF;
1112
1113 EXCEPTION
1114 WHEN FND_API.G_EXC_ERROR THEN
1115 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1116 P_MODULE => l_module
1117 ,P_API_NAME => L_API_NAME
1118 ,P_PKG_NAME => G_PKG_NAME
1119 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1120 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1121 ,X_MSG_COUNT => X_MSG_COUNT
1122 ,X_MSG_DATA => X_MSG_DATA
1123 ,X_RETURN_STATUS => X_RETURN_STATUS);
1124
1125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1126 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1127 P_MODULE => l_module
1128 ,P_API_NAME => L_API_NAME
1129 ,P_PKG_NAME => G_PKG_NAME
1130 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1131 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1132 ,X_MSG_COUNT => X_MSG_COUNT
1133 ,X_MSG_DATA => X_MSG_DATA
1134 ,X_RETURN_STATUS => X_RETURN_STATUS);
1135
1136 WHEN OTHERS THEN
1137 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1138 P_MODULE => l_module
1139 ,P_API_NAME => L_API_NAME
1140 ,P_PKG_NAME => G_PKG_NAME
1141 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1142 ,P_SQLCODE => SQLCODE
1143 ,P_SQLERRM => SQLERRM
1144 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1145 ,X_MSG_COUNT => X_MSG_COUNT
1146 ,X_MSG_DATA => X_MSG_DATA
1147 ,X_RETURN_STATUS => X_RETURN_STATUS);
1148
1149 END Copy_Opportunity;
1150
1151 End AS_OPP_COPY_PVT;