[Home] [Help]
PACKAGE BODY: APPS.AS_COMPETITOR_PROD_PVT
Source
1 PACKAGE BODY AS_COMPETITOR_PROD_PVT as
2 /* $Header: asxvcpdb.pls 120.1 2005/06/14 01:34:01 appldev $ */
3 -- Start of Comments
4 -- Package name : AS_COMPETITOR_PROD_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_COMPETITOR_PROD_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxvcpdb.pls';
13
14 -- Local procedure for competitor products
15
16 FUNCTION Opp_Won( p_LEAD_ID IN NUMBER) RETURN BOOLEAN
17 IS
18
19 CURSOR c_WIN_LOSS_INDICATOR(c_LEAD_ID NUMBER) IS
20 select st.WIN_LOSS_INDICATOR
21 from as_statuses_b st,
22 as_leads_all ld
23 where st.STATUS_CODE = ld.STATUS
24 and ld.LEAD_ID = c_LEAD_ID;
25
26 l_indicator varchar2(1);
27
28 BEGIN
29 open c_WIN_LOSS_INDICATOR( p_LEAD_ID);
30 fetch c_WIN_LOSS_INDICATOR into l_indicator;
31 close c_WIN_LOSS_INDICATOR;
32
33 IF ( nvl(l_indicator, 'L') = 'W') THEN
34 return TRUE;
35 ELSE
36 return FALSE;
37 END IF;
38
39 EXCEPTION
40 WHEN OTHERS THEN
41 return FALSE;
42
43 END Opp_Won;
44
45
46 FUNCTION check_dup(p_Competitor_Prod_rec IN AS_OPPORTUNITY_PUB.Competitor_Prod_Rec_Type
47 := AS_OPPORTUNITY_PUB.G_MISS_Competitor_Prod_Rec)
48 RETURN BOOLEAN IS
49
50 CURSOR dup_exist IS
51 select 'Y'
52 from as_lead_comp_products
53 where lead_line_id = p_Competitor_Prod_rec.lead_line_id
54 and competitor_product_id = p_Competitor_Prod_rec.competitor_product_id;
55
56 l_dup_exist varchar2(1);
57 BEGIN
58 open dup_exist;
59 fetch dup_exist into l_dup_exist;
60 close dup_exist;
61
62 IF ( nvl(l_dup_exist, 'N') = 'Y' ) THEN
63 return TRUE;
64 ELSE
65 return FALSE;
66 END IF;
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 return FALSE;
71
72 END check_dup;
73
74
75
76
77
78
79 -- Hint: Primary key needs to be returned.
80 PROCEDURE Create_competitor_prods(
81 P_Api_Version_Number IN NUMBER,
82 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
83 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
84 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
85 P_Admin_Group_Id IN NUMBER,
86 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
87 P_Competitor_Prod_Tbl IN AS_OPPORTUNITY_PUB.Competitor_Prod_Tbl_Type :=
88 AS_OPPORTUNITY_PUB.G_MISS_Competitor_Prod_Tbl,
89 X_competitor_prod_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.competitor_prod_out_tbl_type,
90 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
91 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
92 P_Identity_Salesforce_Id IN NUMBER := NULL,
93 P_Partner_Cont_Party_id IN NUMBER := FND_API.G_MISS_NUM,
94 X_Return_Status OUT NOCOPY VARCHAR2,
95 X_Msg_Count OUT NOCOPY NUMBER,
96 X_Msg_Data OUT NOCOPY VARCHAR2
97 )
98
99 IS
100 L_Api_Name CONSTANT VARCHAR2(30) := 'Create_Competitor_Prods';
101 L_Api_Version_Number CONSTANT NUMBER := 2.0;
102 L_Return_Status_Full VARCHAR2(1);
103 L_Identity_Sales_Member_Rec AS_SALES_MEMBER_PUB.Sales_Member_Rec_Type;
104 L_Competitor_Prod_Rec AS_OPPORTUNITY_PUB.Competitor_Prod_Rec_Type;
105 L_LEAD_COMPETITOR_PROD_ID NUMBER;
106 L_LEAD_COMPETITOR_PROD VARCHAR2(225);
107 L_Line_Count CONSTANT NUMBER := P_Competitor_Prod_Tbl.count;
108 L_Access_Profile_Rec AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
109 L_Item_Property_Rec AS_UTILITY_PUB.ITEM_PROPERTY_REC_TYPE;
110 L_Access_Flag VARCHAR2(1);
111
112 l_opp_won BOOLEAN := Opp_Won(P_Competitor_Prod_Tbl(1).LEAD_ID);
113 l_loop_count NUMBER := 1;
114 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
115 l_module CONSTANT VARCHAR2(255) := 'as.plsql.cpdpv.Create_competitor_prods';
116
117
118 BEGIN
119 -- Standard Start of API savepoint
120 SAVEPOINT CREATE_COMPETITOR_PRODS_PVT;
121
122 -- Standard call to check for call compatibility.
123 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
124 p_api_version_number,
125 l_api_name,
126 G_PKG_NAME)
127 THEN
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129 END IF;
130
131
132 -- Initialize message list if p_init_msg_list is set to TRUE.
133 IF FND_API.to_Boolean( p_init_msg_list )
134 THEN
135 FND_MSG_PUB.initialize;
136 END IF;
137
138
139 -- Debug Message
140 IF l_debug THEN
141 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
142 'Private API: ' || l_api_name || ' start');
143 END IF;
144
145 -- Initialize API return status to SUCCESS
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147
148 --
149 -- API body
150 --
151
152 /*
153 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
154 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is
155 -- invoked for customization purpose
156 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
157 THEN
158 AS_CALLOUT_PKG.Create_competitor_prods_BC(
159 p_api_version_number => 2.0,
160 p_init_msg_list => FND_API.G_FALSE,
161 p_commit => FND_API.G_FALSE,
162 p_validation_level => p_validation_level,
163 P_Competitor_Prod_Rec => P_Competitor_Prod_Rec,
164 -- Hint: Add detail tables as parameter lists if it's master-detail
165 -- relationship.
166 x_return_status => x_return_status,
167 x_msg_count => x_msg_count,
168 x_msg_data => x_msg_data);
169 END IF;
170 */
171
172
173 -- ******************************************************************
174 -- Validate Environment
175 -- ******************************************************************
176 IF FND_GLOBAL.User_Id IS NULL
177 THEN
178 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
179 THEN
180 FND_MESSAGE.Set_Name(' + appShortName +',
181 'UT_CANNOT_GET_PROFILE_VALUE');
182 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
183 FND_MSG_PUB.ADD;
184 END IF;
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187
188 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
189 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
190 p_api_version_number => 2.0
191 ,p_init_msg_list => p_init_msg_list
192 ,p_salesforce_id => p_identity_salesforce_id
193 ,p_admin_group_id => p_admin_group_id
194 ,x_return_status => x_return_status
195 ,x_msg_count => x_msg_count
196 ,x_msg_data => x_msg_data
197 ,x_sales_member_rec => l_identity_sales_member_rec);
198 END IF;
199
200 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
201 RAISE FND_API.G_EXC_ERROR;
202 END IF;
203
204 -- Call Get_Access_Profiles to get access_profile_rec
205 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
206 p_profile_tbl => p_profile_tbl,
207 x_access_profile_rec => l_access_profile_rec);
208
209 -- Access checking
210 IF ( p_check_access_flag = 'Y' )
211 THEN
212 AS_ACCESS_PUB.Has_updateOpportunityAccess(
213 p_api_version_number => 2.0,
214 p_init_msg_list => p_init_msg_list,
215 p_validation_level => p_validation_level,
216 p_access_profile_rec => l_access_profile_rec,
217 p_admin_flag => p_admin_flag,
218 p_admin_group_id => p_admin_group_id,
219 p_person_id =>
220 l_identity_sales_member_rec.employee_person_id,
221 p_opportunity_id => P_Competitor_Prod_Tbl(1).LEAD_ID,
222 p_check_access_flag => 'Y',
223 p_identity_salesforce_id => p_identity_salesforce_id,
224 p_partner_cont_party_id => NULL,
225 x_return_status => x_return_status,
226 x_msg_count => x_msg_count,
227 x_msg_data => x_msg_data,
228 x_update_access_flag => l_access_flag);
229
230 IF l_access_flag <> 'Y' THEN
231 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
232 'API_NO_UPDATE_PRIVILEGE');
233 END IF;
234
235 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
236 RAISE FND_API.G_EXC_ERROR;
237 END IF;
238 END IF;
239
240 FOR l_curr_row IN 1..l_line_count LOOP
241 X_competitor_prod_out_tbl(l_curr_row).return_status :=
242 FND_API.G_RET_STS_SUCCESS ;
243
244 -- Progress Message
245 --
246 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
247 THEN
248 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
249 FND_MESSAGE.Set_Token ('ROW', 'AS_LEAD_COMP_PRODUCTS', TRUE);
250 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
251 FND_MSG_PUB.Add;
252 END IF;
253
254 l_competitor_prod_rec := P_Competitor_Prod_Tbl(l_curr_row);
255
256 -- Default win/loss status
257 IF (l_competitor_prod_rec.WIN_LOSS_STATUS IS NULL ) THEN
258 l_competitor_prod_rec.WIN_LOSS_STATUS :=
259 fnd_profile.value('AS_DEFAULT_WIN_LOSS_STATUS');
260 END IF;
261
262 -- Reset the win/loss status
263 IF (l_opp_won) THEN
264 l_competitor_prod_rec.WIN_LOSS_STATUS := 'LOST';
265 END IF;
266
267 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL )
268 THEN
269 -- Debug message
270 IF l_debug THEN
271 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
272 'Private API: Validate_competitor_prod');
273
274 END IF;
275
276 -- Invoke validation procedures
277 Validate_competitor_prod(
278 P_Init_Msg_List => FND_API.G_FALSE,
279 P_Validation_Level => p_validation_level,
280 P_Validation_Mode => AS_UTILITY_PVT.G_CREATE,
281 P_Competitor_Prod_Rec => l_Competitor_Prod_Rec,
282 x_return_status => x_return_status,
283 x_msg_count => x_msg_count,
284 x_msg_data => x_msg_data
285 );
286 END IF;
287
288 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
289 RAISE FND_API.G_EXC_ERROR;
290 END IF;
291 /*
292 IF check_dup( p_Competitor_Prod_rec => l_Competitor_Prod_rec ) THEN
293 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
294 THEN
295 FND_MESSAGE.Set_Name('AS', 'API_DUP_COMPETITOR_PRODUCTS');
296 FND_MSG_PUB.ADD;
297 END IF;
298 RAISE FND_API.G_EXC_ERROR;
299 END IF;
300 */
301 -- Debug Message
302 IF l_debug THEN
303 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
304 'Private API: Calling create table handler');
305
306 END IF;
307
308 l_LEAD_COMPETITOR_PROD_ID := l_Competitor_Prod_rec.LEAD_COMPETITOR_PROD_ID;
309
310 -- Invoke table handler(AS_LEAD_COMP_PRODUCTS_PKG.Insert_Row)
311 AS_LEAD_COMP_PRODUCTS_PKG.Insert_Row(
312 p_ATTRIBUTE15 => l_competitor_prod_rec.ATTRIBUTE15,
313 p_ATTRIBUTE14 => l_competitor_prod_rec.ATTRIBUTE14,
314 p_ATTRIBUTE13 => l_competitor_prod_rec.ATTRIBUTE13,
315 p_ATTRIBUTE12 => l_competitor_prod_rec.ATTRIBUTE12,
316 p_ATTRIBUTE11 => l_competitor_prod_rec.ATTRIBUTE11,
317 p_ATTRIBUTE10 => l_competitor_prod_rec.ATTRIBUTE10,
318 p_ATTRIBUTE9 => l_competitor_prod_rec.ATTRIBUTE9,
319 p_ATTRIBUTE8 => l_competitor_prod_rec.ATTRIBUTE8,
320 p_ATTRIBUTE7 => l_competitor_prod_rec.ATTRIBUTE7,
321 p_ATTRIBUTE6 => l_competitor_prod_rec.ATTRIBUTE6,
322 p_ATTRIBUTE4 => l_competitor_prod_rec.ATTRIBUTE4,
323 p_ATTRIBUTE5 => l_competitor_prod_rec.ATTRIBUTE5,
324 p_ATTRIBUTE2 => l_competitor_prod_rec.ATTRIBUTE2,
325 p_ATTRIBUTE3 => l_competitor_prod_rec.ATTRIBUTE3,
326 p_ATTRIBUTE1 => l_competitor_prod_rec.ATTRIBUTE1,
327 p_ATTRIBUTE_CATEGORY => l_competitor_prod_rec.ATTRIBUTE_CATEGORY,
328 p_PROGRAM_ID => l_competitor_prod_rec.PROGRAM_ID,
329 p_PROGRAM_UPDATE_DATE => l_competitor_prod_rec.PROGRAM_UPDATE_DATE,
330 p_PROGRAM_APPLICATION_ID => l_competitor_prod_rec.PROGRAM_APPLICATION_ID,
331 p_REQUEST_ID => l_competitor_prod_rec.REQUEST_ID,
332 p_WIN_LOSS_STATUS => l_competitor_prod_rec.WIN_LOSS_STATUS,
333 p_COMPETITOR_PRODUCT_ID => l_competitor_prod_rec.COMPETITOR_PRODUCT_ID,
334 p_LEAD_LINE_ID => l_competitor_prod_rec.LEAD_LINE_ID,
335 p_LEAD_ID => l_competitor_prod_rec.LEAD_ID,
336 px_LEAD_COMPETITOR_PROD_ID => l_LEAD_COMPETITOR_PROD_ID,
337 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
338 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
339 p_LAST_UPDATE_DATE => SYSDATE,
340 p_CREATED_BY => FND_GLOBAL.USER_ID,
341 p_CREATION_DATE => SYSDATE);
342
343
344 X_competitor_prod_out_tbl(l_curr_row).LEAD_COMPETITOR_PROD_ID :=
345 l_LEAD_COMPETITOR_PROD_ID;
346 X_competitor_prod_out_tbl(l_curr_row).return_status := x_return_status;
347
348 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
349 RAISE FND_API.G_EXC_ERROR;
350 END IF;
351
352 -- rolloup the competitor to the opp header
353 IF l_loop_count = 1 THEN
354 UPDATE AS_LEADS_ALL
355 SET object_version_number = nvl(object_version_number,0) + 1, CLOSE_COMPETITOR_ID =
356 ( select competitor_party_id
357 from ams_competitor_products_b
358 where competitor_product_id = l_competitor_prod_rec.COMPETITOR_PRODUCT_ID )
359 WHERE lead_id = l_competitor_prod_rec.LEAD_ID
360 AND CLOSE_COMPETITOR_ID is null;
361 END IF;
362 l_loop_count := l_loop_count + 1;
363
364 END LOOP;
365
366 --
367 -- End of API body
368 --
369
370 -- Standard check for p_commit
371 IF FND_API.to_Boolean( p_commit )
372 THEN
373 COMMIT WORK;
374 END IF;
375
376
377 -- Debug Message
378 IF l_debug THEN
379 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
380 'Private API: ' || l_api_name || ' end');
381 END IF;
382
383
384 -- Standard call to get message count and if count is 1, get message info.
385 FND_MSG_PUB.Count_And_Get
386 ( p_count => x_msg_count,
387 p_data => x_msg_data
388 );
389
390 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
391 /*
392 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is
393 -- invoked for customization purpose
394 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
395 THEN
396 AS_CALLOUT_PKG.Create_competitor_prods_AC(
397 p_api_version_number => 2.0,
398 p_init_msg_list => FND_API.G_FALSE,
399 p_commit => FND_API.G_FALSE,
400 p_validation_level => p_validation_level,
401 P_Competitor_Prod_Prod_Rec => P_Competitor_Prod_Prod_Rec,
402 -- Hint: Add detail tables as parameter lists if it's master-detail
403 -- relationship.
404 x_return_status => x_return_status,
405 x_msg_count => x_msg_count,
406 x_msg_data => x_msg_data);
407 END IF;
408 */
409 EXCEPTION
410
411 WHEN DUP_VAL_ON_INDEX THEN
412 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
413 THEN
414 FND_MESSAGE.Set_Name('AS', 'API_DUP_COMPETITOR_PRODUCTS');
415 FND_MSG_PUB.ADD;
416 END IF;
417
418 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
419 P_MODULE => l_module
420 ,P_API_NAME => L_API_NAME
421 ,P_PKG_NAME => G_PKG_NAME
422 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
423 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
424 ,X_MSG_COUNT => X_MSG_COUNT
425 ,X_MSG_DATA => X_MSG_DATA
426 ,X_RETURN_STATUS => X_RETURN_STATUS);
427
428 WHEN FND_API.G_EXC_ERROR THEN
429 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
430 P_MODULE => l_module
431 ,P_API_NAME => L_API_NAME
432 ,P_PKG_NAME => G_PKG_NAME
433 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
434 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
435 ,X_MSG_COUNT => X_MSG_COUNT
436 ,X_MSG_DATA => X_MSG_DATA
437 ,X_RETURN_STATUS => X_RETURN_STATUS);
438
439 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
440 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
441 P_MODULE => l_module
442 ,P_API_NAME => L_API_NAME
443 ,P_PKG_NAME => G_PKG_NAME
444 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
445 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
446 ,X_MSG_COUNT => X_MSG_COUNT
447 ,X_MSG_DATA => X_MSG_DATA
448 ,X_RETURN_STATUS => X_RETURN_STATUS);
449
450 WHEN OTHERS THEN
451 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
452 P_MODULE => l_module
453 ,P_API_NAME => L_API_NAME
454 ,P_PKG_NAME => G_PKG_NAME
455 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
456 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
457 ,X_MSG_COUNT => X_MSG_COUNT
458 ,X_MSG_DATA => X_MSG_DATA
459 ,X_RETURN_STATUS => X_RETURN_STATUS);
460 End Create_competitor_prods;
461
462
463 PROCEDURE Update_competitor_prods(
464 P_Api_Version_Number IN NUMBER,
465 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
466 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
467 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
468 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
469 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
470 P_Admin_Group_Id IN NUMBER,
471 P_Identity_Salesforce_Id IN NUMBER,
472 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
473 P_Partner_Cont_Party_id IN NUMBER := FND_API.G_MISS_NUM,
474 P_Competitor_Prod_Tbl IN AS_OPPORTUNITY_PUB.Competitor_Prod_Tbl_Type,
475 X_competitor_prod_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.competitor_prod_out_tbl_type,
476 X_Return_Status OUT NOCOPY VARCHAR2,
477 X_Msg_Count OUT NOCOPY NUMBER,
478 X_Msg_Data OUT NOCOPY VARCHAR2
479 )
480
481 IS
482 Cursor C_Get_competitor_prod(c_LEAD_COMPETITOR_PROD_ID Number) IS
483 Select LAST_UPDATE_DATE
484 From AS_LEAD_COMP_PRODUCTS
485 WHERE LEAD_COMPETITOR_PROD_ID = c_LEAD_COMPETITOR_PROD_ID
486 For Update NOWAIT;
487
488 L_Api_Name CONSTANT VARCHAR2(30) := 'Update_competitor_prods';
489 L_Api_Version_Number CONSTANT NUMBER := 2.0;
490 L_Identity_Sales_Member_Rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
491 L_Ref_Competitor_Prod_Rec AS_OPPORTUNITY_PUB.Competitor_Prod_Rec_Type;
492 L_Rowid ROWID;
493 L_Competitor_Prod_Rec AS_OPPORTUNITY_PUB.Competitor_Prod_Rec_Type;
494 L_Line_Count CONSTANT NUMBER := P_Competitor_Prod_Tbl.count;
495 L_Access_Profile_Rec AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
496 L_Last_Update_Date DATE;
497 L_Access_Flag VARCHAR2(1);
498
499 l_opp_won BOOLEAN := Opp_Won(P_Competitor_Prod_Tbl(1).LEAD_ID);
500 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
501 l_module CONSTANT VARCHAR2(255) := 'as.plsql.cpdpv.Update_competitor_prods';
502
503 BEGIN
504 -- Standard Start of API savepoint
505 SAVEPOINT UPDATE_COMPETITOR_PRODS_PVT;
506
507 -- Standard call to check for call compatibility.
508 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
509 p_api_version_number,
510 l_api_name,
511 G_PKG_NAME)
512 THEN
513 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
514 END IF;
515
516
517 -- Initialize message list if p_init_msg_list is set to TRUE.
518 IF FND_API.to_Boolean( p_init_msg_list )
519 THEN
520 FND_MSG_PUB.initialize;
521 END IF;
522
523
524 -- Debug Message
525 IF l_debug THEN
526 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
527 'Private API: ' || l_api_name || ' start');
528 END IF;
529
530
531 -- Initialize API return status to SUCCESS
532 x_return_status := FND_API.G_RET_STS_SUCCESS;
533
534 --
535 -- Api body
536 --
537 /*
538 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
539 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is
540 -- invoked for customization purpose
541 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
542 THEN
543 AS_CALLOUT_PKG.Update_competitor_prods_BU(
544 p_api_version_number => 2.0,
545 p_init_msg_list => FND_API.G_FALSE,
546 p_commit => FND_API.G_FALSE,
547 p_validation_level => p_validation_level,
548 p_identity_salesforce_id => p_identity_salesforce_id,
549 P_Competitor_Prod_Rec => P_Competitor_Prod_Rec,
550 -- Hint: Add detail tables as parameter lists if it's master-detail
551 -- relationship.
552 x_return_status => x_return_status,
553 x_msg_count => x_msg_count,
554 x_msg_data => x_msg_data);
555 END IF;
556 */
557
558
559
560 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL )
561 THEN
562 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
563 p_api_version_number => 2.0
564 ,p_init_msg_list => p_init_msg_list
565 ,p_salesforce_id => p_identity_salesforce_id
566 ,p_admin_group_id => p_admin_group_id
567 ,x_return_status => x_return_status
568 ,x_msg_count => x_msg_count
569 ,x_msg_data => x_msg_data
570 ,x_sales_member_rec => l_identity_sales_member_rec);
571 END IF;
572
573 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
574 RAISE FND_API.G_EXC_ERROR;
575 END IF;
576
577 -- Call Get_Access_Profiles to get access_profile_rec
578 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
579 p_profile_tbl => p_profile_tbl,
580 x_access_profile_rec => l_access_profile_rec);
581
582 -- Access checking
583 IF ( p_check_access_flag = 'Y' )
584 THEN
585 AS_ACCESS_PUB.Has_updateOpportunityAccess(
586 p_api_version_number => 2.0,
587 p_init_msg_list => p_init_msg_list,
588 p_validation_level => p_validation_level,
589 p_access_profile_rec => l_access_profile_rec,
590 p_admin_flag => p_admin_flag,
591 p_admin_group_id => p_admin_group_id,
592 p_person_id =>
593 l_identity_sales_member_rec.employee_person_id,
594 p_opportunity_id => P_Competitor_Prod_Tbl(1).LEAD_ID,
595 p_check_access_flag => 'Y',
596 p_identity_salesforce_id => p_identity_salesforce_id,
597 p_partner_cont_party_id => NULL,
598 x_return_status => x_return_status,
599 x_msg_count => x_msg_count,
600 x_msg_data => x_msg_data,
601 x_update_access_flag => l_access_flag);
602
603 IF l_access_flag <> 'Y' THEN
604 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
605 'API_NO_UPDATE_PRIVILEGE');
606 END IF;
607
608 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
609 RAISE FND_API.G_EXC_ERROR;
610 END IF;
611 END IF;
612
613 FOR l_curr_row IN 1..l_line_count LOOP
614 X_competitor_prod_out_tbl(l_curr_row).return_status :=
615 FND_API.G_RET_STS_SUCCESS ;
616 -- Progress Message
617 --
618 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
619 THEN
620 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
621 FND_MESSAGE.Set_Token ('ROW', 'AS_LEAD_COMP_PRODUCTS', TRUE);
622 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
623 FND_MSG_PUB.Add;
624 END IF;
625
626 l_competitor_prod_rec := P_Competitor_Prod_Tbl(l_curr_row);
627
628 -- Debug Message
629 IF l_debug THEN
630 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
631 'Private API: - Open Cursor to Select');
632
633 END IF;
634
635 Open C_Get_competitor_prod( l_Competitor_Prod_rec.LEAD_COMPETITOR_PROD_ID);
636
637 Fetch C_Get_competitor_prod into l_last_update_date;
638
639 If ( C_Get_competitor_prod%NOTFOUND) Then
640 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
641 THEN
642 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
643 FND_MESSAGE.Set_Token ('INFO', 'competitor_prod', FALSE);
644 FND_MSG_PUB.Add;
645 END IF;
646 raise FND_API.G_EXC_ERROR;
647 END IF;
648 -- Debug Message
649 IF l_debug THEN
650 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
651 'Private API: - Close Cursor');
652 END IF;
653
654 Close C_Get_competitor_prod;
655
656 If (l_Competitor_Prod_rec.last_update_date is NULL or
657 l_Competitor_Prod_rec.last_update_date = FND_API.G_MISS_Date ) Then
658 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
659 THEN
660 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
661 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
662 FND_MSG_PUB.ADD;
663 END IF;
664 raise FND_API.G_EXC_ERROR;
665 End if;
666 -- Check Whether record has been changed by someone else
667 If (l_Competitor_Prod_rec.last_update_date <> l_last_update_date) Then
668 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
669 THEN
670 FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
671 FND_MESSAGE.Set_Token('INFO', 'competitor_prod', FALSE);
672 FND_MSG_PUB.ADD;
673 END IF;
674 raise FND_API.G_EXC_ERROR;
675 End if;
676
677 -- Reset the win/loss status
678 IF (l_opp_won) THEN
679 l_competitor_prod_rec.WIN_LOSS_STATUS := 'LOST';
680 END IF;
681
682 IF ( p_validation_level >= FND_API.G_VALID_LEVEL_FULL )
683 THEN
684 -- Debug message
685 IF l_debug THEN
686 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
687 'Private API: Validate_competitor_prod');
688 END IF;
689
690 -- Invoke validation procedures
691 Validate_competitor_prod(
692 p_init_msg_list => FND_API.G_FALSE,
693 p_validation_level => p_validation_level,
694 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
695 P_Competitor_Prod_Rec => l_Competitor_Prod_Rec,
696 x_return_status => x_return_status,
697 x_msg_count => x_msg_count,
698 x_msg_data => x_msg_data);
699 END IF;
700
701 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
702 RAISE FND_API.G_EXC_ERROR;
703 END IF;
704 /*
705 IF check_dup( p_Competitor_Prod_rec => l_Competitor_Prod_rec ) THEN
706 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
707 THEN
708 FND_MESSAGE.Set_Name('AS', 'API_DUP_COMPETITOR_PRODUCTS');
709 FND_MSG_PUB.ADD;
710 END IF;
711 RAISE FND_API.G_EXC_ERROR;
712 END IF;
713 */
714 -- Debug Message
715 IF l_debug THEN
716 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
717 'Private API: Calling update table handler');
718 END IF;
719
720
721 -- Invoke table handler(AS_LEAD_COMP_PRODUCTS_PKG.Update_Row)
722 AS_LEAD_COMP_PRODUCTS_PKG.Update_Row(
723 p_ATTRIBUTE15 => l_competitor_prod_rec.ATTRIBUTE15,
724 p_ATTRIBUTE14 => l_competitor_prod_rec.ATTRIBUTE14,
725 p_ATTRIBUTE13 => l_competitor_prod_rec.ATTRIBUTE13,
726 p_ATTRIBUTE12 => l_competitor_prod_rec.ATTRIBUTE12,
727 p_ATTRIBUTE11 => l_competitor_prod_rec.ATTRIBUTE11,
728 p_ATTRIBUTE10 => l_competitor_prod_rec.ATTRIBUTE10,
729 p_ATTRIBUTE9 => l_competitor_prod_rec.ATTRIBUTE9,
730 p_ATTRIBUTE8 => l_competitor_prod_rec.ATTRIBUTE8,
731 p_ATTRIBUTE7 => l_competitor_prod_rec.ATTRIBUTE7,
732 p_ATTRIBUTE6 => l_competitor_prod_rec.ATTRIBUTE6,
733 p_ATTRIBUTE4 => l_competitor_prod_rec.ATTRIBUTE4,
734 p_ATTRIBUTE5 => l_competitor_prod_rec.ATTRIBUTE5,
735 p_ATTRIBUTE2 => l_competitor_prod_rec.ATTRIBUTE2,
736 p_ATTRIBUTE3 => l_competitor_prod_rec.ATTRIBUTE3,
737 p_ATTRIBUTE1 => l_competitor_prod_rec.ATTRIBUTE1,
738 p_ATTRIBUTE_CATEGORY => l_competitor_prod_rec.ATTRIBUTE_CATEGORY,
739 p_PROGRAM_ID => l_competitor_prod_rec.PROGRAM_ID,
740 p_PROGRAM_UPDATE_DATE => l_competitor_prod_rec.PROGRAM_UPDATE_DATE,
741 p_PROGRAM_APPLICATION_ID => l_competitor_prod_rec.PROGRAM_APPLICATION_ID,
742 p_REQUEST_ID => l_competitor_prod_rec.REQUEST_ID,
743 p_WIN_LOSS_STATUS => l_competitor_prod_rec.WIN_LOSS_STATUS,
744 p_COMPETITOR_PRODUCT_ID => l_competitor_prod_rec.COMPETITOR_PRODUCT_ID,
745 p_LEAD_LINE_ID => l_competitor_prod_rec.LEAD_LINE_ID,
746 p_LEAD_ID => l_competitor_prod_rec.LEAD_ID,
747 p_LEAD_COMPETITOR_PROD_ID => l_competitor_prod_rec.LEAD_COMPETITOR_PROD_ID,
748 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
749 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
750 p_LAST_UPDATE_DATE => SYSDATE,
751 p_CREATED_BY => FND_API.G_MISS_NUM,
752 p_CREATION_DATE => l_competitor_prod_rec.CREATION_DATE);
753
754 X_competitor_prod_out_tbl(l_curr_row).LEAD_COMPETITOR_PROD_ID :=
755 l_Competitor_Prod_rec.LEAD_COMPETITOR_PROD_ID;
756 X_competitor_prod_out_tbl(l_curr_row).return_status := x_return_status;
757
758 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
759 RAISE FND_API.G_EXC_ERROR;
760 END IF;
761
762 END LOOP;
763
764
765 --
766 -- End of API body.
767 --
768
769 -- Standard check for p_commit
770 IF FND_API.to_Boolean( p_commit )
771 THEN
772 COMMIT WORK;
773 END IF;
774
775
776 -- Debug Message
777 IF l_debug THEN
778 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
779 'Private API: ' || l_api_name || ' end');
780 END IF;
781
782
783
784 -- Standard call to get message count and if count is 1, get message info.
785 FND_MSG_PUB.Count_And_Get
786 ( p_count => x_msg_count,
787 p_data => x_msg_data
788 );
789
790 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
791 /*
792 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is
793 -- invoked for customization purpose
794 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
795 THEN
796 AS_CALLOUT_PKG.Update_competitor_prods_AU(
797 p_api_version_number => 2.0,
798 p_init_msg_list => FND_API.G_FALSE,
799 p_commit => FND_API.G_FALSE,
800 p_validation_level => p_validation_level,
801 p_identity_salesforce_id => p_identity_salesforce_id,
802 P_Competitor_Prod_Prod_Rec => P_Competitor_Prod_Rec,
803 -- Hint: Add detail tables as parameter lists if it's master-detail
804 -- relationship.
805 x_return_status => x_return_status,
806 x_msg_count => x_msg_count,
807 x_msg_data => x_msg_data);
808 END IF;
809 */
810 EXCEPTION
811
812 WHEN DUP_VAL_ON_INDEX THEN
813 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
814 THEN
815 FND_MESSAGE.Set_Name('AS', 'API_DUP_COMPETITOR_PRODUCTS');
816 FND_MSG_PUB.ADD;
817 END IF;
818
819 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
820 P_MODULE => l_module
821 ,P_API_NAME => L_API_NAME
822 ,P_PKG_NAME => G_PKG_NAME
823 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
824 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
825 ,X_MSG_COUNT => X_MSG_COUNT
826 ,X_MSG_DATA => X_MSG_DATA
827 ,X_RETURN_STATUS => X_RETURN_STATUS);
828
829 WHEN FND_API.G_EXC_ERROR THEN
830 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
831 P_MODULE => l_module
832 ,P_API_NAME => L_API_NAME
833 ,P_PKG_NAME => G_PKG_NAME
834 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
835 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
836 ,X_MSG_COUNT => X_MSG_COUNT
837 ,X_MSG_DATA => X_MSG_DATA
838 ,X_RETURN_STATUS => X_RETURN_STATUS);
839
840 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
841 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
842 P_MODULE => l_module
843 ,P_API_NAME => L_API_NAME
844 ,P_PKG_NAME => G_PKG_NAME
845 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
846 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
847 ,X_MSG_COUNT => X_MSG_COUNT
848 ,X_MSG_DATA => X_MSG_DATA
849 ,X_RETURN_STATUS => X_RETURN_STATUS);
850
851 WHEN OTHERS THEN
852 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
853 P_MODULE => l_module
854 ,P_API_NAME => L_API_NAME
855 ,P_PKG_NAME => G_PKG_NAME
856 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
857 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
858 ,X_MSG_COUNT => X_MSG_COUNT
859 ,X_MSG_DATA => X_MSG_DATA
860 ,X_RETURN_STATUS => X_RETURN_STATUS);
861 End Update_competitor_prods;
862
863
864 -- Hint: Add corresponding delete detail table procedures if it's master-detail
865 -- relationship.
866 -- The Master delete procedure may not be needed depends on different
867 -- business requirements.
868 PROCEDURE Delete_competitor_prods(
869 P_Api_Version_Number IN NUMBER,
870 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
871 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
872 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
873 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
874 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
875 P_Admin_Group_Id IN NUMBER,
876 P_Identity_Salesforce_Id IN NUMBER,
877 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
878 P_Partner_Cont_Party_id IN NUMBER := FND_API.G_MISS_NUM,
879 P_Competitor_Prod_Tbl IN AS_OPPORTUNITY_PUB.Competitor_Prod_Tbl_Type,
880 X_competitor_prod_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.competitor_prod_out_tbl_type,
881 X_Return_Status OUT NOCOPY VARCHAR2,
882 X_Msg_Count OUT NOCOPY NUMBER,
883 X_Msg_Data OUT NOCOPY VARCHAR2
884 )
885
886 IS
887 L_Api_Name CONSTANT VARCHAR2(30) := 'Delete_competitor_prods';
888 L_Api_Version_Number CONSTANT NUMBER := 2.0;
889 L_Identity_Sales_Member_Rec AS_SALES_MEMBER_PUB.Sales_Member_Rec_Type;
890 L_Competitor_Prod_Rec AS_OPPORTUNITY_PUB.Competitor_Prod_Rec_Type;
891 L_Lead_Competitor_Prod_Id NUMBER;
892 L_Line_Count CONSTANT NUMBER := P_Competitor_Prod_Tbl.count;
893 L_Access_Profile_Rec AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
894 L_Access_Flag VARCHAR2(1);
895 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
896 l_module CONSTANT VARCHAR2(255) := 'as.plsql.cpdpv.Delete_competitor_prods';
897
898 BEGIN
899 -- Standard Start of API savepoint
900 SAVEPOINT DELETE_COMPETITOR_PRODS_PVT;
901
902 -- Standard call to check for call compatibility.
903 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
904 p_api_version_number,
905 l_api_name,
906 G_PKG_NAME)
907 THEN
908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
909 END IF;
910
911
912 -- Initialize message list if p_init_msg_list is set to TRUE.
913 IF FND_API.to_Boolean( p_init_msg_list )
914 THEN
915 FND_MSG_PUB.initialize;
916 END IF;
917
918
919 -- Debug Message
920 IF l_debug THEN
921 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
922 'Private API: ' || l_api_name || ' start');
923 END IF;
924
925
926 -- Initialize API return status to SUCCESS
927 x_return_status := FND_API.G_RET_STS_SUCCESS;
928
929 --
930 -- Api body
931 --
932 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
933 /*
934 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
935 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
936 THEN
937 AS_CALLOUT_PKG.Delete_competitor_prods_BD(
938 p_api_version_number => 2.0,
939 p_init_msg_list => FND_API.G_FALSE,
940 p_commit => FND_API.G_FALSE,
941 p_validation_level => p_validation_level,
942 p_identity_salesforce_id => p_identity_salesforce_id,
943 P_Competitor_Prod_Rec => P_Competitor_Prod_Rec,
944 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
945 x_return_status => x_return_status,
946 x_msg_count => x_msg_count,
947 x_msg_data => x_msg_data);
948 END IF;
949 */
950
951 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL )
952 THEN
953 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
954 p_api_version_number => 2.0
955 ,p_init_msg_list => p_init_msg_list
956 ,p_salesforce_id => p_identity_salesforce_id
957 ,p_admin_group_id => p_admin_group_id
958 ,x_return_status => x_return_status
959 ,x_msg_count => x_msg_count
960 ,x_msg_data => x_msg_data
961 ,x_sales_member_rec => l_identity_sales_member_rec);
962 END IF;
963
964 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
965 RAISE FND_API.G_EXC_ERROR;
966 END IF;
967
968 -- Call Get_Access_Profiles to get access_profile_rec
969 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
970 p_profile_tbl => p_profile_tbl,
971 x_access_profile_rec => l_access_profile_rec);
972
973 -- Access checking
974 IF ( p_check_access_flag = 'Y' )
975 THEN
976 AS_ACCESS_PUB.Has_updateOpportunityAccess(
977 p_api_version_number => 2.0,
978 p_init_msg_list => p_init_msg_list,
979 p_validation_level => p_validation_level,
980 p_access_profile_rec => l_access_profile_rec,
981 p_admin_flag => p_admin_flag,
982 p_admin_group_id => p_admin_group_id,
983 p_person_id =>
984 l_identity_sales_member_rec.employee_person_id,
985 p_opportunity_id => l_Competitor_Prod_rec.LEAD_ID,
986 p_check_access_flag => 'Y',
987 p_identity_salesforce_id => p_identity_salesforce_id,
988 p_partner_cont_party_id => NULL,
989 x_return_status => x_return_status,
990 x_msg_count => x_msg_count,
991 x_msg_data => x_msg_data,
992 x_update_access_flag => l_access_flag);
993 END IF;
994
995 IF l_access_flag <> 'Y' THEN
996 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
997 'API_NO_UPDATE_PRIVILEGE');
998 END IF;
999
1000 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1001 RAISE FND_API.G_EXC_ERROR;
1002 END IF;
1003
1004 FOR l_curr_row IN 1..l_line_count LOOP
1005 X_competitor_prod_out_tbl(l_curr_row).return_status :=
1006 FND_API.G_RET_STS_SUCCESS ;
1007
1008 -- Progress Message
1009 --
1010 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1011 THEN
1012 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
1013 FND_MESSAGE.Set_Token ('ROW', 'AS_LEAD_COMP_PRODUCTS', TRUE);
1014 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(l_curr_row), FALSE);
1015 FND_MSG_PUB.Add;
1016 END IF;
1017
1018 l_competitor_prod_rec := P_Competitor_Prod_Tbl(l_curr_row);
1019
1020 -- Debug Message
1021 IF l_debug THEN
1022 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1023 'Private API: Calling delete table handler');
1024 END IF;
1025
1026
1027 -- Invoke table handler(AS_LEAD_COMP_PRODUCTS_PKG.Delete_Row)
1028 AS_LEAD_COMP_PRODUCTS_PKG.Delete_Row(
1029 p_LEAD_COMPETITOR_PROD_ID => l_Competitor_Prod_rec.LEAD_COMPETITOR_PROD_ID);
1030
1031 X_competitor_prod_out_tbl(l_curr_row).LEAD_COMPETITOR_PROD_ID :=
1032 l_LEAD_COMPETITOR_PROD_ID;
1033 X_competitor_prod_out_tbl(l_curr_row).return_status := x_return_status;
1034
1035 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1036 RAISE FND_API.G_EXC_ERROR;
1037 END IF;
1038
1039 END LOOP;
1040
1041 --
1042 -- End of API body
1043 --
1044
1045 -- Standard check for p_commit
1046 IF FND_API.to_Boolean( p_commit )
1047 THEN
1048 COMMIT WORK;
1049 END IF;
1050
1051
1052 -- Debug Message
1053 IF l_debug THEN
1054 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1055 'Private API: ' || l_api_name || ' end');
1056 END IF;
1057
1058
1059 -- Standard call to get message count and if count is 1, get message info.
1060 FND_MSG_PUB.Count_And_Get
1061 ( p_count => x_msg_count,
1062 p_data => x_msg_data
1063 );
1064
1065 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
1066 /*
1067 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is
1068 -- invoked for customization purpose
1069 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
1070 THEN
1071 AS_CALLOUT_PKG.Delete_competitor_prods_AD(
1072 p_api_version_number => 2.0,
1073 p_init_msg_list => FND_API.G_FALSE,
1074 p_commit => FND_API.G_FALSE,
1075 p_validation_level => p_validation_level,
1076 p_identity_salesforce_id => p_identity_salesforce_id,
1077 P_Competitor_Prod_Prod_Rec => P_Competitor_Prod_Rec,
1078 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
1079 x_return_status => x_return_status,
1080 x_msg_count => x_msg_count,
1081 x_msg_data => x_msg_data);
1082 END IF;
1083 */
1084 EXCEPTION
1085 WHEN FND_API.G_EXC_ERROR THEN
1086 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1087 P_MODULE => l_module
1088 ,P_API_NAME => L_API_NAME
1089 ,P_PKG_NAME => G_PKG_NAME
1090 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1091 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1092 ,X_MSG_COUNT => X_MSG_COUNT
1093 ,X_MSG_DATA => X_MSG_DATA
1094 ,X_RETURN_STATUS => X_RETURN_STATUS);
1095
1096 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1097 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1098 P_MODULE => l_module
1099 ,P_API_NAME => L_API_NAME
1100 ,P_PKG_NAME => G_PKG_NAME
1101 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1102 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1103 ,X_MSG_COUNT => X_MSG_COUNT
1104 ,X_MSG_DATA => X_MSG_DATA
1105 ,X_RETURN_STATUS => X_RETURN_STATUS);
1106
1107 WHEN OTHERS THEN
1108 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1109 P_MODULE => l_module
1110 ,P_API_NAME => L_API_NAME
1111 ,P_PKG_NAME => G_PKG_NAME
1112 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1113 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1114 ,X_MSG_COUNT => X_MSG_COUNT
1115 ,X_MSG_DATA => X_MSG_DATA
1116 ,X_RETURN_STATUS => X_RETURN_STATUS);
1117 End Delete_competitor_prods;
1118
1119
1120 -- Item-level validation procedures
1121 PROCEDURE Validate_REQUEST_ID (
1122 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1123 P_Validation_mode IN VARCHAR2,
1124 P_REQUEST_ID IN NUMBER,
1125 X_Return_Status OUT NOCOPY VARCHAR2,
1126 X_Msg_Count OUT NOCOPY NUMBER,
1127 X_Msg_Data OUT NOCOPY VARCHAR2
1128 )
1129 IS
1130 BEGIN
1131
1132 -- Initialize message list if p_init_msg_list is set to TRUE.
1133 IF FND_API.to_Boolean( p_init_msg_list )
1134 THEN
1135 FND_MSG_PUB.initialize;
1136 END IF;
1137
1138
1139 -- Initialize API return status to SUCCESS
1140 x_return_status := FND_API.G_RET_STS_SUCCESS;
1141
1142 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
1143 THEN
1144 -- Hint: Validate data
1145 -- IF p_REQUEST_ID is not NULL and p_REQUEST_ID <> G_MISS_CHAR
1146 -- verify if data is valid
1147 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1148 NULL;
1149 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
1150 THEN
1151 -- Hint: Validate data
1152 -- IF p_REQUEST_ID <> G_MISS_CHAR
1153 -- verify if data is valid
1154 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
1155 NULL;
1156 END IF;
1157
1158 -- Standard call to get message count and if count is 1, get message info.
1159 FND_MSG_PUB.Count_And_Get
1160 ( p_count => x_msg_count,
1161 p_data => x_msg_data
1162 );
1163
1164 END Validate_REQUEST_ID;
1165
1166
1167 PROCEDURE Validate_WIN_LOSS_STATUS (
1168 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1169 P_Validation_mode IN VARCHAR2,
1170 P_WIN_LOSS_STATUS IN VARCHAR2,
1171 X_Return_Status OUT NOCOPY VARCHAR2,
1172 X_Msg_Count OUT NOCOPY NUMBER,
1173 X_Msg_Data OUT NOCOPY VARCHAR2
1174 )
1175 IS
1176
1177 CURSOR C_WIN_LOSS_STATUS_Exists (c_lookup_type VARCHAR2,
1178 c_Lookup_Code VARCHAR2) IS
1179 SELECT 'X'
1180 FROM as_lookups
1181 WHERE lookup_type = c_lookup_type
1182 and lookup_code = c_Lookup_Code;
1183 l_val VARCHAR2(1);
1184 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1185 l_module CONSTANT VARCHAR2(255) := 'as.plsql.cpdpv.Validate_WIN_LOSS_STATUS';
1186
1187 BEGIN
1188
1189 -- Initialize message list if p_init_msg_list is set to TRUE.
1190 IF FND_API.to_Boolean( p_init_msg_list )
1191 THEN
1192 FND_MSG_PUB.initialize;
1193 END IF;
1194
1195
1196 -- Initialize API return status to SUCCESS
1197 x_return_status := FND_API.G_RET_STS_SUCCESS;
1198
1199 IF (p_WIN_LOSS_STATUS is NOT NULL) and
1200 (p_WIN_LOSS_STATUS <> FND_API.G_MISS_CHAR)
1201 THEN
1202 -- WIN_LOSS_STATUS should exist in as_lookups
1203 OPEN C_WIN_LOSS_STATUS_Exists ('WIN_LOSS_STATUS',
1204 p_WIN_LOSS_STATUS);
1205 FETCH C_WIN_LOSS_STATUS_Exists into l_val;
1206
1207 IF C_WIN_LOSS_STATUS_Exists%NOTFOUND THEN
1208 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
1209 -- 'Private API: WIN_LOSS_STATUS is invalid');
1210
1211 AS_UTILITY_PVT.Set_Message(
1212 p_module => l_module,
1213 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1214 p_msg_name => 'API_INVALID_WIN_LOSS_STATUS',
1215 p_token1 => 'VALUE',
1216 p_token1_value => p_WIN_LOSS_STATUS );
1217
1218 x_return_status := FND_API.G_RET_STS_ERROR;
1219 END IF;
1220
1221 CLOSE C_WIN_LOSS_STATUS_Exists;
1222 END IF;
1223
1224 -- Standard call to get message count and if count is 1, get message info.
1225 FND_MSG_PUB.Count_And_Get
1226 ( p_count => x_msg_count,
1227 p_data => x_msg_data
1228 );
1229
1230 END Validate_WIN_LOSS_STATUS;
1231
1232
1233 PROCEDURE Validate_COMPETITOR_PRODUCT_ID (
1234 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1235 P_Validation_mode IN VARCHAR2,
1236 P_COMPETITOR_PRODUCT_ID IN NUMBER,
1237 X_Return_Status OUT NOCOPY VARCHAR2,
1238 X_Msg_Count OUT NOCOPY NUMBER,
1239 X_Msg_Data OUT NOCOPY VARCHAR2
1240 )
1241 IS
1242
1243 /*
1244 CURSOR c_competitor_product_exists(c_COMPETITOR_PRODUCT_ID number) IS
1245 select 'X'
1246 from ams_competitor_products_b
1247 where competitor_product_id = c_COMPETITOR_PRODUCT_ID;
1248 */
1249 l_val VARCHAR2(1);
1250 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1251 l_module CONSTANT VARCHAR2(255) := 'as.plsql.cpdpv.Validate_COMPETITOR_PRODUCT_ID';
1252
1253 BEGIN
1254 -- Initialize message list if p_init_msg_list is set to TRUE.
1255 IF FND_API.to_Boolean( p_init_msg_list )
1256 THEN
1257 FND_MSG_PUB.initialize;
1258 END IF;
1259
1260
1261 -- Initialize API return status to SUCCESS
1262 x_return_status := FND_API.G_RET_STS_SUCCESS;
1263
1264 IF (p_COMPETITOR_PRODUCT_ID is NULL) or
1265 (p_COMPETITOR_PRODUCT_ID = FND_API.G_MISS_NUM)
1266 THEN
1267 AS_UTILITY_PVT.Set_Message(
1268 p_module => l_module,
1269 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1270 p_msg_name => 'API_MISSING_COMPETITOR_PRODUCT_ID');
1271
1272 x_return_status := FND_API.G_RET_STS_ERROR;
1273 ELSE
1274 NULL;
1275 /*
1276 OPEN c_competitor_product_exists(p_COMPETITOR_PRODUCT_ID);
1277 FETCH c_competitor_product_exists into l_val;
1278 IF c_competitor_product_exists%NOTFOUND THEN
1279 IF l_debug THEN
1280 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
1281 'Private API: COMPETITOR_PRODUCT_ID is invalid');
1282 END IF;
1283
1284 x_return_status := FND_API.G_RET_STS_ERROR;
1285 END IF;
1286 CLOSE c_competitor_product_exists;
1287 */
1288 END IF;
1289
1290 -- Standard call to get message count and if count is 1, get message info.
1291 FND_MSG_PUB.Count_And_Get
1292 ( p_count => x_msg_count,
1293 p_data => x_msg_data
1294 );
1295
1296 END Validate_COMPETITOR_PRODUCT_ID;
1297
1298
1299 PROCEDURE Validate_LEAD_LINE_ID (
1300 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1301 P_Validation_mode IN VARCHAR2,
1302 P_LEAD_LINE_ID IN NUMBER,
1303 X_Return_Status OUT NOCOPY VARCHAR2,
1304 X_Msg_Count OUT NOCOPY NUMBER,
1305 X_Msg_Data OUT NOCOPY VARCHAR2
1306 )
1307 IS
1308
1309 CURSOR C_Lead_Line_Id_Exists (c_Lead_Line_Id NUMBER) IS
1310 SELECT 'X'
1311 FROM as_lead_lines
1312 WHERE lead_line_id = c_Lead_Line_Id;
1313
1314
1315
1316 l_val VARCHAR2(1);
1317 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1318 l_module CONSTANT VARCHAR2(255) := 'as.plsql.cpdpv.Validate_LEAD_LINE_ID';
1319
1320 BEGIN
1321
1322 -- Initialize message list if p_init_msg_list is set to TRUE.
1323 IF FND_API.to_Boolean( p_init_msg_list )
1324 THEN
1325 FND_MSG_PUB.initialize;
1326 END IF;
1327
1328
1329 -- Initialize API return status to SUCCESS
1330 x_return_status := FND_API.G_RET_STS_SUCCESS;
1331
1332
1333 IF (p_LEAD_LINE_ID is NULL) or (p_LEAD_LINE_ID = FND_API.G_MISS_NUM)
1334 THEN
1335 IF l_debug THEN
1336 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
1337 'Private API 38: Violate NOT NULL constraint(LEAD_LINE_ID)');
1338 END IF;
1339
1340 x_return_status := FND_API.G_RET_STS_ERROR;
1341 ELSE
1342 OPEN C_Lead_Line_Id_Exists (p_Lead_Line_Id);
1343 FETCH C_Lead_Line_Id_Exists into l_val;
1344 IF C_Lead_Line_Id_Exists%NOTFOUND
1345 THEN
1346 IF l_debug THEN
1347 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
1348 'Private API 39: LEAD_LINE_ID is not valid');
1349 END IF;
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351 END IF;
1352 CLOSE C_Lead_Line_Id_Exists;
1353 END IF;
1354
1355 -- Standard call to get message count and if count is 1, get message info.
1356 FND_MSG_PUB.Count_And_Get
1357 ( p_count => x_msg_count,
1358 p_data => x_msg_data
1359 );
1360
1361 END Validate_LEAD_LINE_ID;
1362
1363
1364 PROCEDURE Validate_LEAD_ID (
1365 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1366 P_Validation_mode IN VARCHAR2,
1367 P_LEAD_ID IN NUMBER,
1368 X_Return_Status OUT NOCOPY VARCHAR2,
1369 X_Msg_Count OUT NOCOPY NUMBER,
1370 X_Msg_Data OUT NOCOPY VARCHAR2
1371 )
1372 IS
1373
1374
1375 CURSOR C_Lead_Id_Exists (c_Lead_Id NUMBER) IS
1376 SELECT 'X'
1377 FROM as_leads
1378 WHERE lead_id = c_Lead_Id;
1379
1380 l_val VARCHAR2(1);
1381 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1382 l_module CONSTANT VARCHAR2(255) := 'as.plsql.cpdpv.Validate_LEAD_ID';
1383
1384 BEGIN
1385
1386 -- Initialize message list if p_init_msg_list is set to TRUE.
1387 IF FND_API.to_Boolean( p_init_msg_list )
1388 THEN
1389 FND_MSG_PUB.initialize;
1390 END IF;
1391
1392
1393 -- Initialize API return status to SUCCESS
1394 x_return_status := FND_API.G_RET_STS_SUCCESS;
1395
1396
1397 IF (p_LEAD_ID is NULL) or (p_LEAD_ID = FND_API.G_MISS_NUM)
1398 THEN
1399 IF l_debug THEN
1400 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
1401 'Private API 36: Violate NOT NULL constraint(LEAD_ID)');
1402 END IF;
1403
1404 x_return_status := FND_API.G_RET_STS_ERROR;
1405 ELSE
1406 OPEN C_Lead_Id_Exists (p_Lead_Id);
1407 FETCH C_Lead_Id_Exists into l_val;
1408 IF C_Lead_Id_Exists%NOTFOUND
1409 THEN
1410 IF l_debug THEN
1411 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
1412 'Private API 37: LEAD_ID is not valid');
1413 END IF;
1414
1415 x_return_status := FND_API.G_RET_STS_ERROR;
1416 END IF;
1417 CLOSE C_Lead_Id_Exists;
1418 END IF;
1419
1420 -- Standard call to get message count and if count is 1, get message info.
1421 FND_MSG_PUB.Count_And_Get
1422 ( p_count => x_msg_count,
1423 p_data => x_msg_data
1424 );
1425
1426 END Validate_LEAD_ID;
1427
1428
1429 PROCEDURE Validate_L_COMPETITOR_PROD_ID (
1430 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1431 P_Validation_mode IN VARCHAR2,
1432 P_LEAD_COMPETITOR_PROD_ID IN NUMBER,
1433 X_Return_Status OUT NOCOPY VARCHAR2,
1434 X_Msg_Count OUT NOCOPY NUMBER,
1435 X_Msg_Data OUT NOCOPY VARCHAR2
1436 )
1437 IS
1438 CURSOR C_Lead_CProd_Id_Exists (c_Lead_Competitor_Prod_Id NUMBER) IS
1439 SELECT 'X'
1440 FROM as_lead_comp_products
1441 WHERE lead_competitor_prod_id = c_Lead_Competitor_Prod_Id;
1442
1443 l_val VARCHAR2(1);
1444 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1445 l_module CONSTANT VARCHAR2(255) := 'as.plsql.cpdpv.Validate_L_COMPETITOR_PROD_ID';
1446
1447 BEGIN
1448 -- Initialize message list if p_init_msg_list is set to TRUE.
1449 IF FND_API.to_Boolean( p_init_msg_list )
1450 THEN
1451 FND_MSG_PUB.initialize;
1452 END IF;
1453
1454 -- Initialize API return status to SUCCESS
1455 x_return_status := FND_API.G_RET_STS_SUCCESS;
1456
1457 -- Calling from Create API
1458 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
1459 THEN
1460 IF (p_LEAD_COMPETITOR_PROD_ID is NOT NULL) and (p_LEAD_COMPETITOR_PROD_ID <> FND_API.G_MISS_NUM)
1461 THEN
1462 OPEN C_Lead_CProd_Id_Exists (p_Lead_Competitor_Prod_Id);
1463 FETCH C_Lead_CProd_Id_Exists into l_val;
1464
1465 IF C_Lead_CProd_Id_Exists%FOUND THEN
1466 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
1467 -- 'Private API: LEAD_COMPETITOR_PROD_ID exist');
1468
1469 AS_UTILITY_PVT.Set_Message(
1470 p_module => l_module,
1471 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1472 p_msg_name => 'API_DUPLICATE_LEAD_CPROD_ID',
1473 p_token1 => 'VALUE',
1474 p_token1_value => p_LEAD_COMPETITOR_PROD_ID );
1475
1476 x_return_status := FND_API.G_RET_STS_ERROR;
1477 END IF;
1478
1479 CLOSE C_Lead_CProd_Id_Exists;
1480 END IF;
1481
1482 -- Calling from Update API
1483 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
1484 THEN
1485 -- validate NOT NULL column
1486 IF (p_LEAD_COMPETITOR_PROD_ID is NULL) or (p_LEAD_COMPETITOR_PROD_ID = FND_API.G_MISS_NUM)
1487 THEN
1488 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
1489 -- 'Private API: Violate NOT NULL constraint(LEAD_COMPETITOR_PROD_ID)');
1490
1491 AS_UTILITY_PVT.Set_Message(
1492 p_module => l_module,
1493 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1494 p_msg_name => 'API_MISSING_LEAD_CPROD_ID');
1495
1496 x_return_status := FND_API.G_RET_STS_ERROR;
1497 ELSE
1498 OPEN C_Lead_CProd_Id_Exists (p_Lead_Competitor_Prod_Id);
1499 FETCH C_Lead_CProd_Id_Exists into l_val;
1500
1501 IF C_Lead_CProd_Id_Exists%NOTFOUND
1502 THEN
1503 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
1504 -- 'Private API: LEAD_COMPETITOR_PROD_ID is not valid');
1505
1506 AS_UTILITY_PVT.Set_Message(
1507 p_module => l_module,
1508 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1509 p_msg_name => 'API_INVALID_LEAD_CPROD_ID',
1510 p_token1 => 'VALUE',
1511 p_token1_value => p_LEAD_COMPETITOR_PROD_ID );
1512
1513 x_return_status := FND_API.G_RET_STS_ERROR;
1514 END IF;
1515
1516 CLOSE C_Lead_CProd_Id_Exists;
1517 END IF;
1518 END IF;
1519
1520 -- Standard call to get message count and if count is 1, get message info.
1521 FND_MSG_PUB.Count_And_Get
1522 ( p_count => x_msg_count,
1523 p_data => x_msg_data
1524 );
1525
1526 END Validate_L_COMPETITOR_PROD_ID;
1527
1528
1529 -- Hint: inter-field level validation can be added here.
1530 -- Hint: If p_validation_mode = AS_UTILITY_PVT.G_VALIDATE_UPDATE, we should use cursor
1531 -- to get old values for all fields used in inter-field validation and set all G_MISS_XXX fields to original value
1532 -- stored in database table.
1533
1534
1535
1536 PROCEDURE Validate_Competitor_Prod_rec(
1537 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1538 P_Validation_mode IN VARCHAR2,
1539 P_Competitor_Prod_Rec IN as_opportunity_pub.Competitor_Prod_Rec_Type,
1540 X_Return_Status OUT NOCOPY VARCHAR2,
1541 X_Msg_Count OUT NOCOPY NUMBER,
1542 X_Msg_Data OUT NOCOPY VARCHAR2
1543 )
1544 IS
1545 BEGIN
1546
1547 -- Initialize message list if p_init_msg_list is set to TRUE.
1548 IF FND_API.to_Boolean( p_init_msg_list )
1549 THEN
1550 FND_MSG_PUB.initialize;
1551 END IF;
1552
1553
1554 -- Initialize API return status to SUCCESS
1555 x_return_status := FND_API.G_RET_STS_SUCCESS;
1556
1557 -- Hint: Validate data
1558 -- If data not valid
1559 -- THEN
1560 -- x_return_status := FND_API.G_RET_STS_ERROR;
1561
1562 -- Debug Message
1563
1564 -- Standard call to get message count and if count is 1, get message info.
1565 FND_MSG_PUB.Count_And_Get
1566 ( p_count => x_msg_count,
1567 p_data => x_msg_data
1568 );
1569
1570 END Validate_Competitor_Prod_Rec;
1571
1572 PROCEDURE Validate_competitor_prod(
1573 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1574 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1575 P_Validation_mode IN VARCHAR2,
1576 P_Competitor_Prod_Rec IN as_opportunity_pub.Competitor_Prod_Rec_Type,
1577 X_Return_Status OUT NOCOPY VARCHAR2,
1578 X_Msg_Count OUT NOCOPY NUMBER,
1579 X_Msg_Data OUT NOCOPY VARCHAR2
1580 )
1581 IS
1582 l_api_name CONSTANT VARCHAR2(30) := 'Validate_competitor_prod';
1583 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1584 l_module CONSTANT VARCHAR2(255) := 'as.plsql.cpdpv.Validate_competitor_prod';
1585
1586 BEGIN
1587
1588 -- Debug Message
1589 IF l_debug THEN
1590 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
1591 END IF;
1592
1593
1594
1595 -- Initialize API return status to SUCCESS
1596 x_return_status := FND_API.G_RET_STS_SUCCESS;
1597
1598 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM) THEN
1599
1600 IF l_debug THEN
1601 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1602 'Private API validate: REQUEST_ID');
1603 END IF;
1604
1605 Validate_REQUEST_ID(
1606 p_init_msg_list => FND_API.G_FALSE,
1607 p_validation_mode => p_validation_mode,
1608 p_REQUEST_ID => P_Competitor_Prod_Rec.REQUEST_ID,
1609 x_return_status => x_return_status,
1610 x_msg_count => x_msg_count,
1611 x_msg_data => x_msg_data);
1612
1613 IF l_debug THEN
1614 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1615 'Private API validate: WIN_LOSS_STATUS ');
1616 END IF;
1617
1618 Validate_WIN_LOSS_STATUS(
1619 p_init_msg_list => FND_API.G_FALSE,
1620 p_validation_mode => p_validation_mode,
1621 p_WIN_LOSS_STATUS => P_Competitor_Prod_Rec.WIN_LOSS_STATUS,
1622 x_return_status => x_return_status,
1623 x_msg_count => x_msg_count,
1624 x_msg_data => x_msg_data);
1625
1626
1627 Validate_COMPETITOR_PRODUCT_ID(
1628 p_init_msg_list => FND_API.G_FALSE,
1629 p_validation_mode => p_validation_mode,
1630 p_COMPETITOR_PRODUCT_ID => P_Competitor_Prod_Rec.COMPETITOR_PRODUCT_ID,
1631 x_return_status => x_return_status,
1632 x_msg_count => x_msg_count,
1633 x_msg_data => x_msg_data);
1634
1635 IF l_debug THEN
1636 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1637 'Private API validate: LEAD_LINE_ID');
1638 END IF;
1639
1640 Validate_LEAD_LINE_ID(
1641 p_init_msg_list => FND_API.G_FALSE,
1642 p_validation_mode => p_validation_mode,
1643 p_LEAD_LINE_ID => P_Competitor_Prod_Rec.LEAD_LINE_ID,
1644 x_return_status => x_return_status,
1645 x_msg_count => x_msg_count,
1646 x_msg_data => x_msg_data);
1647
1648 IF l_debug THEN
1649 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1650 'Private API validate: LEAD_ID');
1651 END IF;
1652
1653 Validate_LEAD_ID(
1654 p_init_msg_list => FND_API.G_FALSE,
1655 p_validation_mode => p_validation_mode,
1656 p_LEAD_ID => P_Competitor_Prod_Rec.LEAD_ID,
1657 x_return_status => x_return_status,
1658 x_msg_count => x_msg_count,
1659 x_msg_data => x_msg_data);
1660
1661 IF l_debug THEN
1662 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1663 'Private API validate: L_COMPETITOR_PROD_ID');
1664 END IF;
1665
1666
1667 Validate_L_COMPETITOR_PROD_ID(
1668 p_init_msg_list => FND_API.G_FALSE,
1669 p_validation_mode => p_validation_mode,
1670 p_LEAD_COMPETITOR_PROD_ID => P_Competitor_Prod_Rec.LEAD_COMPETITOR_PROD_ID,
1671 x_return_status => x_return_status,
1672 x_msg_count => x_msg_count,
1673 x_msg_data => x_msg_data);
1674
1675 END IF;
1676
1677 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_RECORD) THEN
1678 -- Hint: Inter-field level validation can be added here
1679 -- invoke record level validation procedures
1680
1681 IF l_debug THEN
1682 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1683 'Private API validate: Competitor_Prod_Rec');
1684 END IF;
1685
1686
1687 Validate_Competitor_Prod_Rec(
1688 p_init_msg_list => FND_API.G_FALSE,
1689 p_validation_mode => p_validation_mode,
1690 P_Competitor_Prod_Rec => P_Competitor_Prod_Rec,
1691 x_return_status => x_return_status,
1692 x_msg_count => x_msg_count,
1693 x_msg_data => x_msg_data);
1694
1695 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1696 raise FND_API.G_EXC_ERROR;
1697 END IF;
1698 END IF;
1699
1700 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_RECORD) THEN
1701 -- invoke inter-record level validation procedures
1702 NULL;
1703 END IF;
1704
1705 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_ENTITY) THEN
1706 -- invoke inter-entity level validation procedures
1707 NULL;
1708 END IF;
1709
1710
1711 -- Debug Message
1712 IF l_debug THEN
1713 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
1714 'Private API: Validation end');
1715 END IF;
1716
1717
1718 END Validate_competitor_prod;
1719
1720
1721
1722 End AS_COMPETITOR_PROD_PVT;