[Home] [Help]
PACKAGE BODY: APPS.AS_OPP_OBSTACLE_PVT
Source
1 PACKAGE BODY AS_OPP_OBSTACLE_PVT as
2 /* $Header: asxvobsb.pls 120.1 2005/06/14 01:36:54 appldev $ */
3 -- Start of Comments
4 -- Package name : AS_OPP_OBSTACLE_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AS_OPP_OBSTACLE_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asxvobsb.pls';
13
14 -- Hint: Primary key needs to be returned.
15 PROCEDURE Create_obstacles(
16 P_Api_Version_Number IN NUMBER,
17 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
18 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
19 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
20 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
21 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
22 P_Admin_Group_Id IN NUMBER,
23 P_Identity_Salesforce_Id IN VARCHAR2 := FND_API.G_FALSE,
24 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
25 p_partner_cont_party_id IN NUMBER := FND_API.G_MISS_NUM,
26 P_Obstacle_tbl IN AS_OPPORTUNITY_PUB.Obstacle_tbl_Type := AS_OPPORTUNITY_PUB.G_MISS_Obstacle_tbl,
27 --Hint: Add detail tables as parameter lists if it's master-detail relationship.
28 X_LEAD_OBSTACLE_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.obstacle_out_tbl_type,
29 X_Return_Status OUT NOCOPY VARCHAR2,
30 X_Msg_Count OUT NOCOPY NUMBER,
31 X_Msg_Data OUT NOCOPY VARCHAR2
32 )
33
34
35 IS
36 l_api_name CONSTANT VARCHAR2(30) := 'Create_obstacles';
37 l_api_version_number CONSTANT NUMBER := 2.0;
38 l_return_status_full VARCHAR2(1);
39 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
40 l_OBSTACLE_rec AS_OPPORTUNITY_PUB.OBSTACLE_Rec_Type;
41 l_LEAD_OBSTACLE_ID NUMBER;
42 l_access_profile_rec AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
43 l_access_flag VARCHAR2(1);
44 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
45 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lobpv.Create_obstacles';
46 BEGIN
47 -- Standard Start of API savepoint
48 SAVEPOINT CREATE_OBSTACLES_PVT;
49
50 -- Standard call to check for call compatibility.
51 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
52 p_api_version_number,
53 l_api_name,
54 G_PKG_NAME)
55 THEN
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57 END IF;
58
59
60 -- Initialize message list if p_init_msg_list is set to TRUE.
61 IF FND_API.to_Boolean( p_init_msg_list )
62 THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65
66
67 -- Debug Message
68 IF l_debug THEN
69 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
70 END IF;
71
72
73
74 -- Initialize API return status to SUCCESS
75 x_return_status := FND_API.G_RET_STS_SUCCESS;
76
77 --
78 -- API body
79 --
80
81 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
82 /*
83 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
84 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
85 THEN
86 AS_CALLOUT_PKG.Create_obstacle_BC(
87 p_api_version_number => 2.0,
88 p_init_msg_list => FND_API.G_FALSE,
89 p_commit => FND_API.G_FALSE,
90 p_validation_level => p_validation_level,
91 P_Obstacle_Rec => P_Obstacle_Rec,
92 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
93 x_return_status => x_return_status,
94 x_msg_count => x_msg_count,
95 x_msg_data => x_msg_data);
96 END IF;
97 */
98 -- ******************************************************************
99 -- Validate Environment
100 -- ******************************************************************
101 IF FND_GLOBAL.User_Id IS NULL
102 THEN
103 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
104 THEN
105 FND_MESSAGE.Set_Name(' + appShortName +', 'UT_CANNOT_GET_PROFILE_VALUE');
106 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
107 FND_MSG_PUB.ADD;
108 END IF;
109 RAISE FND_API.G_EXC_ERROR;
110 END IF;
111
112
113 IF ( P_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN
114 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
115 p_api_version_number => 2.0
116 ,p_init_msg_list => p_init_msg_list
117 ,p_salesforce_id => p_identity_salesforce_id
118 ,p_admin_group_id => p_admin_group_id
119 ,x_return_status => x_return_status
120 ,x_msg_count => x_msg_count
121 ,x_msg_data => x_msg_data
122 ,x_sales_member_rec => l_identity_sales_member_rec);
123 END IF;
124
125 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
126 RAISE FND_API.G_EXC_ERROR;
127 END IF;
128
129 -- Call Get_Access_Profiles to get access_profile_rec
130 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
131 p_profile_tbl => p_profile_tbl,
132 x_access_profile_rec => l_access_profile_rec);
133
134 IF( p_check_access_flag = 'Y' )
135 THEN
136 AS_ACCESS_PUB.Has_updateOpportunityAccess(
137 p_api_version_number => 2.0
138 ,p_init_msg_list => p_init_msg_list
139 ,p_validation_level => p_validation_level
140 ,p_access_profile_rec => l_access_profile_rec
141 ,p_admin_flag => p_admin_flag
142 ,p_admin_group_id => p_admin_group_id
143 ,p_person_id => l_identity_sales_member_rec.employee_person_id
144 ,p_opportunity_id => P_Obstacle_tbl(1).LEAD_ID
145 ,p_check_access_flag => 'Y'
146 ,p_identity_salesforce_id => p_identity_salesforce_id
147 ,p_partner_cont_party_id => NULL
148 ,x_return_status => x_return_status
149 ,x_msg_count => x_msg_count
150 ,x_msg_data => x_msg_data
151 ,x_update_access_flag => l_access_flag);
152 END IF;
153
154 IF l_access_flag <> 'Y' THEN
155 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
156 'API_NO_UPDATE_PRIVILEGE');
157 END IF;
158
159 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
160 RAISE FND_API.G_EXC_ERROR;
161 END IF;
162
163
164 -- Invoke table handler(AS_LEAD_OBSTACLES_PKG.Insert_Row)
165 FOR I in 1 .. P_OBSTACLE_tbl.count LOOP
166
167 X_LEAD_OBSTACLE_out_tbl(I).return_status := FND_API.G_RET_STS_SUCCESS;
168 l_LEAD_OBSTACLE_ID := P_OBSTACLE_Tbl(I).LEAD_OBSTACLE_ID;
169
170 -- Progress Message
171 --
172 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
173 THEN
174 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
175 FND_MESSAGE.Set_Token ('ROW', 'AS_OPP_OBSTACLE', TRUE);
176 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(I), FALSE);
177 FND_MSG_PUB.Add;
178 END IF;
179
180 l_OBSTACLE_rec := P_OBSTACLE_Tbl(I);
181
182 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
183 THEN
184 -- Debug message
185 IF l_debug THEN
186 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_obstacle');
187 END IF;
188
189
190 -- Invoke validation procedures
191 Validate_obstacle(
192 p_init_msg_list => FND_API.G_FALSE,
193 p_validation_level => p_validation_level,
194 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
195 P_Obstacle_Rec => l_Obstacle_Rec,
196 x_return_status => x_return_status,
197 x_msg_count => x_msg_count,
198 x_msg_data => x_msg_data);
199 END IF;
200
201 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
202 RAISE FND_API.G_EXC_ERROR;
203 END IF;
204
205 -- Hint: Add corresponding Master-Detail business logic here if necessary.
206
207 -- Debug Message
208 IF l_debug THEN
209 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling create table handler');
210 END IF;
211
212
213 AS_LEAD_OBSTACLES_PKG.Insert_Row(
214 px_LEAD_OBSTACLE_ID => l_LEAD_OBSTACLE_ID,
215 p_LAST_UPDATE_DATE => SYSDATE,
216 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
217 p_CREATION_DATE => SYSDATE,
218 p_CREATED_BY => FND_GLOBAL.USER_ID,
219 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
220 p_REQUEST_ID => p_Obstacle_tbl(I).REQUEST_ID,
221 p_PROGRAM_APPLICATION_ID => p_Obstacle_tbl(I).PROGRAM_APPLICATION_ID,
222 p_PROGRAM_ID => p_Obstacle_tbl(I).PROGRAM_ID,
223 p_PROGRAM_UPDATE_DATE => p_Obstacle_tbl(I).PROGRAM_UPDATE_DATE,
224 p_LEAD_ID => p_Obstacle_tbl(I).LEAD_ID,
225 p_OBSTACLE_CODE => p_Obstacle_tbl(I).OBSTACLE_CODE,
226 p_OBSTACLE => p_Obstacle_tbl(I).OBSTACLE,
227 p_OBSTACLE_STATUS => p_Obstacle_tbl(I).OBSTACLE_STATUS,
228 p_COMMENTS => p_Obstacle_tbl(I).COMMENTS,
229 p_ATTRIBUTE_CATEGORY => p_Obstacle_tbl(I).ATTRIBUTE_CATEGORY,
230 p_ATTRIBUTE1 => p_Obstacle_tbl(I).ATTRIBUTE1,
231 p_ATTRIBUTE2 => p_Obstacle_tbl(I).ATTRIBUTE2,
232 p_ATTRIBUTE3 => p_Obstacle_tbl(I).ATTRIBUTE3,
233 p_ATTRIBUTE4 => p_Obstacle_tbl(I).ATTRIBUTE4,
234 p_ATTRIBUTE5 => p_Obstacle_tbl(I).ATTRIBUTE5,
235 p_ATTRIBUTE6 => p_Obstacle_tbl(I).ATTRIBUTE6,
236 p_ATTRIBUTE7 => p_Obstacle_tbl(I).ATTRIBUTE7,
237 p_ATTRIBUTE8 => p_Obstacle_tbl(I).ATTRIBUTE8,
238 p_ATTRIBUTE9 => p_Obstacle_tbl(I).ATTRIBUTE9,
239 p_ATTRIBUTE10 => p_Obstacle_tbl(I).ATTRIBUTE10,
240 p_ATTRIBUTE11 => p_Obstacle_tbl(I).ATTRIBUTE11,
241 p_ATTRIBUTE12 => p_Obstacle_tbl(I).ATTRIBUTE12,
242 p_ATTRIBUTE13 => p_Obstacle_tbl(I).ATTRIBUTE13,
243 p_ATTRIBUTE14 => p_Obstacle_tbl(I).ATTRIBUTE14,
244 p_ATTRIBUTE15 => p_Obstacle_tbl(I).ATTRIBUTE15);
245 -- ? p_SECURITY_GROUP_ID => p_Obstacle_tbl(I).SECURITY_GROUP_ID);
246 -- Hint: Primary key should be returned.
247 -- x_LEAD_OBSTACLE_ID := px_LEAD_OBSTACLE_ID;
248
249 X_LEAD_OBSTACLE_out_tbl(I).LEAD_OBSTACLE_ID := l_LEAD_OBSTACLE_ID;
250 X_LEAD_OBSTACLE_out_tbl(I).return_status := x_return_status;
251
252 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
253 RAISE FND_API.G_EXC_ERROR;
254 END IF;
255
256
257 END LOOP;
258 --
259 -- End of API body
260 --
261
262 -- Standard check for p_commit
263 IF FND_API.to_Boolean( p_commit )
264 THEN
265 COMMIT WORK;
266 END IF;
267
268
269 -- Debug Message
270 IF l_debug THEN
271 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
272 END IF;
273
274
275
276 -- Standard call to get message count and if count is 1, get message info.
277 FND_MSG_PUB.Count_And_Get
278 ( p_count => x_msg_count,
279 p_data => x_msg_data
280 );
281
282 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
283 /*
284 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
285 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
286 THEN
287 AS_CALLOUT_PKG.Create_obstacle_AC(
288 p_api_version_number => 2.0,
289 p_init_msg_list => FND_API.G_FALSE,
290 p_commit => FND_API.G_FALSE,
291 p_validation_level => p_validation_level,
292 P_Obstacle_Rec => P_Obstacle_Rec,
293 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
294 x_return_status => x_return_status,
295 x_msg_count => x_msg_count,
296 x_msg_data => x_msg_data);
297 END IF;
298 */
299 EXCEPTION
300
301 WHEN DUP_VAL_ON_INDEX THEN
302 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
303 THEN
304 FND_MESSAGE.Set_Name('AS', 'API_DUP_ISSUES');
305 FND_MSG_PUB.ADD;
306 END IF;
307
308 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
309 P_MODULE => l_module
310 ,P_API_NAME => L_API_NAME
311 ,P_PKG_NAME => G_PKG_NAME
312 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
313 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
314 ,X_MSG_COUNT => X_MSG_COUNT
315 ,X_MSG_DATA => X_MSG_DATA
316 ,X_RETURN_STATUS => X_RETURN_STATUS);
317
318
319 WHEN FND_API.G_EXC_ERROR THEN
320 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
321 P_MODULE => l_module
322 ,P_API_NAME => L_API_NAME
323 ,P_PKG_NAME => G_PKG_NAME
324 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
325 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
326 ,X_MSG_COUNT => X_MSG_COUNT
327 ,X_MSG_DATA => X_MSG_DATA
328 ,X_RETURN_STATUS => X_RETURN_STATUS);
329
330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
331 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
332 P_MODULE => l_module
333 ,P_API_NAME => L_API_NAME
334 ,P_PKG_NAME => G_PKG_NAME
335 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
336 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
337 ,X_MSG_COUNT => X_MSG_COUNT
338 ,X_MSG_DATA => X_MSG_DATA
339 ,X_RETURN_STATUS => X_RETURN_STATUS);
340
341 WHEN OTHERS THEN
342 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
343 P_MODULE => l_module
344 ,P_API_NAME => L_API_NAME
345 ,P_PKG_NAME => G_PKG_NAME
346 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
347 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
348 ,X_MSG_COUNT => X_MSG_COUNT
349 ,X_MSG_DATA => X_MSG_DATA
350 ,X_RETURN_STATUS => X_RETURN_STATUS);
351 End Create_obstacles;
352
353
354 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
355 PROCEDURE Update_obstacles(
356 P_Api_Version_Number IN NUMBER,
357 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
358 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
359 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
360 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
361 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
362 P_Admin_Group_Id IN NUMBER,
363 P_Identity_Salesforce_Id IN NUMBER,
364 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
365 p_partner_cont_party_id IN NUMBER := FND_API.G_MISS_NUM,
366 P_Obstacle_tbl IN AS_OPPORTUNITY_PUB.Obstacle_tbl_type,
367 X_LEAD_OBSTACLE_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.obstacle_out_tbl_type,
368 X_Return_Status OUT NOCOPY VARCHAR2,
369 X_Msg_Count OUT NOCOPY NUMBER,
370 X_Msg_Data OUT NOCOPY VARCHAR2
371 )
372
373 IS
374
375 Cursor C_Get_obstacle(c_LEAD_OBSTACLE_ID Number) IS
376 Select rowid,
377 LEAD_OBSTACLE_ID,
378 LAST_UPDATE_DATE,
379 LAST_UPDATED_BY,
380 CREATION_DATE,
381 CREATED_BY,
382 LAST_UPDATE_LOGIN,
383 REQUEST_ID,
384 PROGRAM_APPLICATION_ID,
385 PROGRAM_ID,
386 PROGRAM_UPDATE_DATE,
387 LEAD_ID,
388 OBSTACLE_CODE,
389 OBSTACLE,
390 OBSTACLE_STATUS,
391 COMMENTS,
392 ATTRIBUTE_CATEGORY,
393 ATTRIBUTE1,
394 ATTRIBUTE2,
395 ATTRIBUTE3,
396 ATTRIBUTE4,
397 ATTRIBUTE5,
398 ATTRIBUTE6,
399 ATTRIBUTE7,
400 ATTRIBUTE8,
401 ATTRIBUTE9,
402 ATTRIBUTE10,
403 ATTRIBUTE11,
404 ATTRIBUTE12,
405 ATTRIBUTE13,
406 ATTRIBUTE14,
407 ATTRIBUTE15
408 -- ? SECURITY_GROUP_ID
409 From AS_LEAD_OBSTACLES
410 WHERE LEAD_OBSTACLE_ID = c_LEAD_OBSTACLE_ID
411 -- Hint: Developer need to provide Where clause
412 For Update NOWAIT;
413
414 l_api_name CONSTANT VARCHAR2(30) := 'Update_obstacles';
415 l_api_version_number CONSTANT NUMBER := 2.0;
416 -- Local Variables
417 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
418 l_ref_Obstacle_rec AS_OPPORTUNITY_PUB.Obstacle_Rec_Type;
419 l_tar_Obstacle_rec AS_OPPORTUNITY_PUB.Obstacle_Rec_Type;
420 l_Obstacle_rec AS_OPPORTUNITY_PUB.Obstacle_Rec_Type;
421 l_rowid ROWID;
422 l_access_profile_rec AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
423 l_access_flag VARCHAR2(1);
424 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
425 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lobpv.Update_obstacles';
426 BEGIN
427 -- Standard Start of API savepoint
428 SAVEPOINT UPDATE_OBSTACLES_PVT;
429
430 -- Standard call to check for call compatibility.
431 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
432 p_api_version_number,
433 l_api_name,
434 G_PKG_NAME)
435 THEN
436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
437 END IF;
438
439
440 -- Initialize message list if p_init_msg_list is set to TRUE.
441 IF FND_API.to_Boolean( p_init_msg_list )
442 THEN
443 FND_MSG_PUB.initialize;
444 END IF;
445
446
447 -- Debug Message
448 IF l_debug THEN
449 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
450 END IF;
451
452
453
454 -- Initialize API return status to SUCCESS
455 x_return_status := FND_API.G_RET_STS_SUCCESS;
456
457 --
458 -- Api body
459 --
460 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
461 /*
462 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
463 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
464 THEN
465 AS_CALLOUT_PKG.Update_obstacle_BU(
466 p_api_version_number => 2.0,
467 p_init_msg_list => FND_API.G_FALSE,
468 p_commit => FND_API.G_FALSE,
469 p_validation_level => p_validation_level,
470 p_identity_salesforce_id => p_identity_salesforce_id,
471 P_Obstacle_Rec => P_Obstacle_Rec,
472 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
473 x_return_status => x_return_status,
474 x_msg_count => x_msg_count,
475 x_msg_data => x_msg_data);
476 END IF;
477 */
478
479 IF ( P_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN
480 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
481 p_api_version_number => 2.0
482 ,p_init_msg_list => p_init_msg_list
483 ,p_salesforce_id => p_identity_salesforce_id
484 ,p_admin_group_id => p_admin_group_id
485 ,x_return_status => x_return_status
486 ,x_msg_count => x_msg_count
487 ,x_msg_data => x_msg_data
488 ,x_sales_member_rec => l_identity_sales_member_rec);
489 END IF;
490
491 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
492 RAISE FND_API.G_EXC_ERROR;
493 END IF;
494
495 -- Call Get_Access_Profiles to get access_profile_rec
496 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
497 p_profile_tbl => p_profile_tbl,
498 x_access_profile_rec => l_access_profile_rec);
499
500 IF( p_check_access_flag = 'Y' )
501 THEN
502 AS_ACCESS_PUB.Has_updateOpportunityAccess(
503 p_api_version_number => 2.0
504 ,p_init_msg_list => p_init_msg_list
505 ,p_validation_level => p_validation_level
506 ,p_access_profile_rec => l_access_profile_rec
507 ,p_admin_flag => p_admin_flag
508 ,p_admin_group_id => p_admin_group_id
509 ,p_person_id => l_identity_sales_member_rec.employee_person_id
510 ,p_opportunity_id => P_Obstacle_tbl(1).LEAD_ID
511 ,p_check_access_flag => 'Y'
512 ,p_identity_salesforce_id => p_identity_salesforce_id
513 ,p_partner_cont_party_id => NULL
514 ,x_return_status => x_return_status
515 ,x_msg_count => x_msg_count
516 ,x_msg_data => x_msg_data
517 ,x_update_access_flag => l_access_flag);
518 END IF;
519
520 IF l_access_flag <> 'Y' THEN
521 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
522 'API_NO_UPDATE_PRIVILEGE');
523 END IF;
524
525 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
526 RAISE FND_API.G_EXC_ERROR;
527 END IF;
528
529 FOR I in 1 .. P_Obstacle_tbl.count LOOP
530
531 l_tar_Obstacle_rec := P_Obstacle_tbl(I);
532 X_LEAD_OBSTACLE_out_tbl(I).return_status := FND_API.G_RET_STS_SUCCESS;
533
534 -- Progress Message
535 --
536 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
537 THEN
538 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
539 FND_MESSAGE.Set_Token ('ROW', 'AS_OPP_OBSTACLE', TRUE);
540 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(I), FALSE);
541 FND_MSG_PUB.Add;
542 END IF;
543
544 l_OBSTACLE_rec := P_OBSTACLE_Tbl(I);
545
546 -- Debug Message
547 IF l_debug THEN
548 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Open Cursor to Select');
549 END IF;
550
551
552 Open C_Get_obstacle( l_tar_Obstacle_rec.LEAD_OBSTACLE_ID);
553
554 Fetch C_Get_obstacle into
555 l_rowid,
556 l_ref_Obstacle_rec.LEAD_OBSTACLE_ID,
557 l_ref_Obstacle_rec.LAST_UPDATE_DATE,
558 l_ref_Obstacle_rec.LAST_UPDATED_BY,
559 l_ref_Obstacle_rec.CREATION_DATE,
560 l_ref_Obstacle_rec.CREATED_BY,
561 l_ref_Obstacle_rec.LAST_UPDATE_LOGIN,
562 l_ref_Obstacle_rec.REQUEST_ID,
563 l_ref_Obstacle_rec.PROGRAM_APPLICATION_ID,
564 l_ref_Obstacle_rec.PROGRAM_ID,
565 l_ref_Obstacle_rec.PROGRAM_UPDATE_DATE,
566 l_ref_Obstacle_rec.LEAD_ID,
567 l_ref_Obstacle_rec.OBSTACLE_CODE,
568 l_ref_Obstacle_rec.OBSTACLE,
569 l_ref_Obstacle_rec.OBSTACLE_STATUS,
570 l_ref_Obstacle_rec.COMMENTS,
571 l_ref_Obstacle_rec.ATTRIBUTE_CATEGORY,
572 l_ref_Obstacle_rec.ATTRIBUTE1,
573 l_ref_Obstacle_rec.ATTRIBUTE2,
574 l_ref_Obstacle_rec.ATTRIBUTE3,
575 l_ref_Obstacle_rec.ATTRIBUTE4,
576 l_ref_Obstacle_rec.ATTRIBUTE5,
577 l_ref_Obstacle_rec.ATTRIBUTE6,
578 l_ref_Obstacle_rec.ATTRIBUTE7,
579 l_ref_Obstacle_rec.ATTRIBUTE8,
580 l_ref_Obstacle_rec.ATTRIBUTE9,
581 l_ref_Obstacle_rec.ATTRIBUTE10,
582 l_ref_Obstacle_rec.ATTRIBUTE11,
583 l_ref_Obstacle_rec.ATTRIBUTE12,
584 l_ref_Obstacle_rec.ATTRIBUTE13,
585 l_ref_Obstacle_rec.ATTRIBUTE14,
586 l_ref_Obstacle_rec.ATTRIBUTE15;
587 -- ? l_ref_Obstacle_rec.SECURITY_GROUP_ID;
588
589 If ( C_Get_obstacle%NOTFOUND) Then
590 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
591 THEN
592 FND_MESSAGE.Set_Name('AS', 'API_MISSING_UPDATE_TARGET');
593 FND_MESSAGE.Set_Token ('INFO', 'obstacle', FALSE);
594 FND_MSG_PUB.Add;
595 END IF;
596 raise FND_API.G_EXC_ERROR;
597 END IF;
598 -- Debug Message
599 IF l_debug THEN
600 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Close Cursor');
601 END IF;
602
603 Close C_Get_obstacle;
604
605 If (l_tar_Obstacle_rec.last_update_date is NULL or
606 l_tar_Obstacle_rec.last_update_date = FND_API.G_MISS_Date ) Then
607 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
608 THEN
609 FND_MESSAGE.Set_Name('AS', 'API_MISSING_ID');
610 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
611 FND_MSG_PUB.ADD;
612 END IF;
613 raise FND_API.G_EXC_ERROR;
614 End if;
615 -- Check Whether record has been changed by someone else
616 If (l_tar_Obstacle_rec.last_update_date <> l_ref_Obstacle_rec.last_update_date) Then
617 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
618 THEN
619 FND_MESSAGE.Set_Name('AS', 'API_RECORD_CHANGED');
620 FND_MESSAGE.Set_Token('INFO', 'obstacle', FALSE);
621 FND_MSG_PUB.ADD;
622 END IF;
623 raise FND_API.G_EXC_ERROR;
624 End if;
625
626 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
627 THEN
628 -- Debug message
629 IF l_debug THEN
630 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_obstacle');
631 END IF;
632
633
634 -- Invoke validation procedures
635 Validate_obstacle(
636 p_init_msg_list => FND_API.G_FALSE,
637 p_validation_level => p_validation_level,
638 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
639 P_Obstacle_Rec => l_Obstacle_Rec,
640 x_return_status => x_return_status,
641 x_msg_count => x_msg_count,
642 x_msg_data => x_msg_data);
643 END IF;
644
645 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
646 RAISE FND_API.G_EXC_ERROR;
647 END IF;
648
649 -- Hint: Add corresponding Master-Detail business logic here if necessary.
650
651 -- Debug Message
652 IF l_debug THEN
653 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
654 END IF;
655
656
657 -- Invoke table handler(AS_LEAD_OBSTACLES_PKG.Update_Row)
658 AS_LEAD_OBSTACLES_PKG.Update_Row(
659 p_LEAD_OBSTACLE_ID => p_Obstacle_tbl(I).LEAD_OBSTACLE_ID,
660 p_LAST_UPDATE_DATE => SYSDATE,
661 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
662 p_CREATION_DATE => SYSDATE,
663 p_CREATED_BY => FND_GLOBAL.USER_ID,
664 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
665 p_REQUEST_ID => p_Obstacle_tbl(I).REQUEST_ID,
666 p_PROGRAM_APPLICATION_ID => p_Obstacle_tbl(I).PROGRAM_APPLICATION_ID,
667 p_PROGRAM_ID => p_Obstacle_tbl(I).PROGRAM_ID,
668 p_PROGRAM_UPDATE_DATE => p_Obstacle_tbl(I).PROGRAM_UPDATE_DATE,
669 p_LEAD_ID => p_Obstacle_tbl(I).LEAD_ID,
670 p_OBSTACLE_CODE => p_Obstacle_tbl(I).OBSTACLE_CODE,
671 p_OBSTACLE => p_Obstacle_tbl(I).OBSTACLE,
672 p_OBSTACLE_STATUS => p_Obstacle_tbl(I).OBSTACLE_STATUS,
673 p_COMMENTS => p_Obstacle_tbl(I).COMMENTS,
674 p_ATTRIBUTE_CATEGORY => p_Obstacle_tbl(I).ATTRIBUTE_CATEGORY,
675 p_ATTRIBUTE1 => p_Obstacle_tbl(I).ATTRIBUTE1,
676 p_ATTRIBUTE2 => p_Obstacle_tbl(I).ATTRIBUTE2,
677 p_ATTRIBUTE3 => p_Obstacle_tbl(I).ATTRIBUTE3,
678 p_ATTRIBUTE4 => p_Obstacle_tbl(I).ATTRIBUTE4,
679 p_ATTRIBUTE5 => p_Obstacle_tbl(I).ATTRIBUTE5,
680 p_ATTRIBUTE6 => p_Obstacle_tbl(I).ATTRIBUTE6,
681 p_ATTRIBUTE7 => p_Obstacle_tbl(I).ATTRIBUTE7,
682 p_ATTRIBUTE8 => p_Obstacle_tbl(I).ATTRIBUTE8,
683 p_ATTRIBUTE9 => p_Obstacle_tbl(I).ATTRIBUTE9,
684 p_ATTRIBUTE10 => p_Obstacle_tbl(I).ATTRIBUTE10,
685 p_ATTRIBUTE11 => p_Obstacle_tbl(I).ATTRIBUTE11,
686 p_ATTRIBUTE12 => p_Obstacle_tbl(I).ATTRIBUTE12,
687 p_ATTRIBUTE13 => p_Obstacle_tbl(I).ATTRIBUTE13,
688 p_ATTRIBUTE14 => p_Obstacle_tbl(I).ATTRIBUTE14,
689 p_ATTRIBUTE15 => p_Obstacle_tbl(I).ATTRIBUTE15);
690 -- ? p_SECURITY_GROUP_ID => p_Obstacle_tbl(I).SECURITY_GROUP_ID);
691
692 X_LEAD_OBSTACLE_out_tbl(I).LEAD_OBSTACLE_ID := p_Obstacle_tbl(I).LEAD_OBSTACLE_ID;
693 X_LEAD_OBSTACLE_out_tbl(I).return_status := x_return_status;
694
695 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
696 RAISE FND_API.G_EXC_ERROR;
697 END IF;
698
699 END LOOP;
700 --
701 -- End of API body.
702 --
703
704 -- Standard check for p_commit
705 IF FND_API.to_Boolean( p_commit )
706 THEN
707 COMMIT WORK;
708 END IF;
709
710
711 -- Debug Message
712 IF l_debug THEN
713 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
714 END IF;
715
716
717
718 -- Standard call to get message count and if count is 1, get message info.
719 FND_MSG_PUB.Count_And_Get
720 ( p_count => x_msg_count,
721 p_data => x_msg_data
722 );
723
724 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
725 /*
726 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
727 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
728 THEN
729 AS_CALLOUT_PKG.Update_obstacle_AU(
730 p_api_version_number => 2.0,
731 p_init_msg_list => FND_API.G_FALSE,
732 p_commit => FND_API.G_FALSE,
733 p_validation_level => p_validation_level,
734 p_identity_salesforce_id => p_identity_salesforce_id,
735 P_Obstacle_Rec => P_Obstacle_Rec,
736 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
737 x_return_status => x_return_status,
738 x_msg_count => x_msg_count,
739 x_msg_data => x_msg_data);
740 END IF;
741 */
742 EXCEPTION
743 WHEN DUP_VAL_ON_INDEX THEN
744 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
745 THEN
746 FND_MESSAGE.Set_Name('AS', 'API_DUP_ISSUES');
747 FND_MSG_PUB.ADD;
748 END IF;
749
750 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
751 P_MODULE => l_module
752 ,P_API_NAME => L_API_NAME
753 ,P_PKG_NAME => G_PKG_NAME
754 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
755 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
756 ,X_MSG_COUNT => X_MSG_COUNT
757 ,X_MSG_DATA => X_MSG_DATA
758 ,X_RETURN_STATUS => X_RETURN_STATUS);
759
760
761 WHEN FND_API.G_EXC_ERROR THEN
762 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
763 P_MODULE => l_module
764 ,P_API_NAME => L_API_NAME
765 ,P_PKG_NAME => G_PKG_NAME
766 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
767 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
768 ,X_MSG_COUNT => X_MSG_COUNT
769 ,X_MSG_DATA => X_MSG_DATA
770 ,X_RETURN_STATUS => X_RETURN_STATUS);
771
772 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
773 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
774 P_MODULE => l_module
775 ,P_API_NAME => L_API_NAME
776 ,P_PKG_NAME => G_PKG_NAME
777 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
778 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
779 ,X_MSG_COUNT => X_MSG_COUNT
780 ,X_MSG_DATA => X_MSG_DATA
781 ,X_RETURN_STATUS => X_RETURN_STATUS);
782
783 WHEN OTHERS THEN
784 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
785 P_MODULE => l_module
786 ,P_API_NAME => L_API_NAME
787 ,P_PKG_NAME => G_PKG_NAME
788 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
789 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
790 ,X_MSG_COUNT => X_MSG_COUNT
791 ,X_MSG_DATA => X_MSG_DATA
792 ,X_RETURN_STATUS => X_RETURN_STATUS);
793 End Update_obstacles;
794
795
796 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
797 -- The Master delete procedure may not be needed depends on different business requirements.
798 PROCEDURE Delete_obstacles(
799 P_Api_Version_Number IN NUMBER,
800 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
801 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
802 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
803 P_Check_Access_Flag IN VARCHAR2 := FND_API.G_FALSE,
804 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
805 P_Admin_Group_Id IN NUMBER,
806 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
807 P_identity_salesforce_id IN NUMBER := NULL,
808 p_partner_cont_party_id IN NUMBER := FND_API.G_MISS_NUM,
809 P_Obstacle_tbl IN AS_OPPORTUNITY_PUB.Obstacle_tbl_type,
810 X_LEAD_OBSTACLE_out_tbl OUT NOCOPY AS_OPPORTUNITY_PUB.obstacle_out_tbl_type,
811 X_Return_Status OUT NOCOPY VARCHAR2,
812 X_Msg_Count OUT NOCOPY NUMBER,
813 X_Msg_Data OUT NOCOPY VARCHAR2
814 )
815
816 IS
817 l_api_name CONSTANT VARCHAR2(30) := 'Delete_obstacles';
818 l_api_version_number CONSTANT NUMBER := 2.0;
819 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
820 l_Obstacle_rec AS_OPPORTUNITY_PUB.Obstacle_Rec_Type;
821 l_access_profile_rec AS_ACCESS_PUB.ACCESS_PROFILE_REC_TYPE;
822 l_access_flag VARCHAR2(1);
823 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
824 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lobpv.Delete_obstacles';
825 BEGIN
826 -- Standard Start of API savepoint
827 SAVEPOINT DELETE_OBSTACLES_PVT;
828
829 -- Standard call to check for call compatibility.
830 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
831 p_api_version_number,
832 l_api_name,
833 G_PKG_NAME)
834 THEN
835 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
836 END IF;
837
838
839 -- Initialize message list if p_init_msg_list is set to TRUE.
840 IF FND_API.to_Boolean( p_init_msg_list )
841 THEN
842 FND_MSG_PUB.initialize;
843 END IF;
844
845
846 -- Debug Message
847 IF l_debug THEN
848 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
849 END IF;
850
851
852 -- Initialize API return status to SUCCESS
853 x_return_status := FND_API.G_RET_STS_SUCCESS;
854
855 --
856 -- Api body
857 --
858 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
859 /*
860 -- if profile AS_PRE_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
861 IF(FND_PROFILE.VALUE('AS_PRE_CUSTOM_ENABLED')='Y')
862 THEN
863 AS_CALLOUT_PKG.Delete_obstacle_BD(
864 p_api_version_number => 2.0,
865 p_init_msg_list => FND_API.G_FALSE,
866 p_commit => FND_API.G_FALSE,
867 p_validation_level => p_validation_level,
868 p_identity_salesforce_id => p_identity_salesforce_id,
869 P_Obstacle_Rec => P_Obstacle_Rec,
870 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
871 x_return_status => x_return_status,
872 x_msg_count => x_msg_count,
873 x_msg_data => x_msg_data);
874 END IF;
875 */
876
877 IF ( P_validation_level = FND_API.G_VALID_LEVEL_FULL) THEN
878 AS_SALES_ORG_MANAGER_PVT.Get_CurrentUser(
879 p_api_version_number => 2.0
880 ,p_init_msg_list => p_init_msg_list
881 ,p_salesforce_id => p_identity_salesforce_id
882 ,p_admin_group_id => p_admin_group_id
883 ,x_return_status => x_return_status
884 ,x_msg_count => x_msg_count
885 ,x_msg_data => x_msg_data
886 ,x_sales_member_rec => l_identity_sales_member_rec);
887 END IF;
888
889 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
890 RAISE FND_API.G_EXC_ERROR;
891 END IF;
892
893 -- Call Get_Access_Profiles to get access_profile_rec
894 AS_OPPORTUNITY_PUB.Get_Access_Profiles(
895 p_profile_tbl => p_profile_tbl,
896 x_access_profile_rec => l_access_profile_rec);
897
898 IF( p_check_access_flag = 'Y' )
899 THEN
900 AS_ACCESS_PUB.Has_updateOpportunityAccess(
901 p_api_version_number => 2.0
902 ,p_init_msg_list => p_init_msg_list
903 ,p_validation_level => p_validation_level
904 ,p_access_profile_rec => l_access_profile_rec
905 ,p_admin_flag => p_admin_flag
906 ,p_admin_group_id => p_admin_group_id
907 ,p_person_id => l_identity_sales_member_rec.employee_person_id
908 ,p_opportunity_id => P_Obstacle_tbl(1).LEAD_ID
909 ,p_check_access_flag => 'Y'
910 ,p_identity_salesforce_id => p_identity_salesforce_id
911 ,p_partner_cont_party_id => NULL
912 ,x_return_status => x_return_status
913 ,x_msg_count => x_msg_count
914 ,x_msg_data => x_msg_data
915 ,x_update_access_flag => l_access_flag);
916 END IF;
917
918 IF l_access_flag <> 'Y' THEN
919 AS_UTILITY_PVT.Set_Message(l_module, FND_MSG_PUB.G_MSG_LVL_ERROR,
920 'API_NO_UPDATE_PRIVILEGE');
921 END IF;
922
923 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
924 RAISE FND_API.G_EXC_ERROR;
925 END IF;
926
927
928 -- Invoke table handler(AS_LEAD_OBSTACLES_PKG.Delete_Row)
929 FOR I in 1 .. P_Obstacle_tbl.count LOOP
930
931 X_LEAD_OBSTACLE_out_tbl(I).return_status := FND_API.G_RET_STS_SUCCESS;
932
933 -- Progress Message
934 --
935 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
936 THEN
937 FND_MESSAGE.Set_Name ('AS', 'API_PROCESSING_ROW');
938 FND_MESSAGE.Set_Token ('ROW', 'AS_OPP_OBSTACLE', TRUE);
939 FND_MESSAGE.Set_Token ('RECORD_NUM', to_char(I), FALSE);
940 FND_MSG_PUB.Add;
941 END IF;
942
943 -- Debug Message
944 IF l_debug THEN
945 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling delete table handler');
946 END IF;
947
948
949 AS_LEAD_OBSTACLES_PKG.Delete_Row(
950 p_LEAD_OBSTACLE_ID => p_Obstacle_tbl(I).LEAD_OBSTACLE_ID);
951
952 X_LEAD_OBSTACLE_out_tbl(I).LEAD_OBSTACLE_ID := p_Obstacle_tbl(I).LEAD_OBSTACLE_ID;
953 X_LEAD_OBSTACLE_out_tbl(I).return_status := x_return_status;
954
955 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
956 RAISE FND_API.G_EXC_ERROR;
957 END IF;
958
959 END LOOP;
960 --
961 -- End of API body
962 --
963
964 -- Standard check for p_commit
965 IF FND_API.to_Boolean( p_commit )
966 THEN
967 COMMIT WORK;
968 END IF;
969
970
971 -- Debug Message
972 IF l_debug THEN
973 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
974 END IF;
975
976
977
978 -- Standard call to get message count and if count is 1, get message info.
979 FND_MSG_PUB.Count_And_Get
980 ( p_count => x_msg_count,
981 p_data => x_msg_data
982 );
983
984 -- Un-comment the following statements when AS_CALLOUT_PKG is ready.
985 /*
986 -- if profile AS_POST_CUSTOM_ENABLED is set to 'Y', callout procedure is invoked for customization purpose
987 IF(FND_PROFILE.VALUE('AS_POST_CUSTOM_ENABLED')='Y')
988 THEN
989 AS_CALLOUT_PKG.Delete_obstacle_AD(
990 p_api_version_number => 2.0,
991 p_init_msg_list => FND_API.G_FALSE,
992 p_commit => FND_API.G_FALSE,
993 p_validation_level => p_validation_level,
994 p_identity_salesforce_id => p_identity_salesforce_id,
995 P_Obstacle_Rec => P_Obstacle_Rec,
996 -- Hint: Add detail tables as parameter lists if it's master-detail relationship.
997 x_return_status => x_return_status,
998 x_msg_count => x_msg_count,
999 x_msg_data => x_msg_data);
1000 END IF;
1001 */
1002 EXCEPTION
1003 WHEN FND_API.G_EXC_ERROR THEN
1004 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1005 P_MODULE => l_module
1006 ,P_API_NAME => L_API_NAME
1007 ,P_PKG_NAME => G_PKG_NAME
1008 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1009 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1010 ,X_MSG_COUNT => X_MSG_COUNT
1011 ,X_MSG_DATA => X_MSG_DATA
1012 ,X_RETURN_STATUS => X_RETURN_STATUS);
1013
1014 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1015 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1016 P_MODULE => l_module
1017 ,P_API_NAME => L_API_NAME
1018 ,P_PKG_NAME => G_PKG_NAME
1019 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1020 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1021 ,X_MSG_COUNT => X_MSG_COUNT
1022 ,X_MSG_DATA => X_MSG_DATA
1023 ,X_RETURN_STATUS => X_RETURN_STATUS);
1024
1025 WHEN OTHERS THEN
1026 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
1027 P_MODULE => l_module
1028 ,P_API_NAME => L_API_NAME
1029 ,P_PKG_NAME => G_PKG_NAME
1030 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
1031 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
1032 ,X_MSG_COUNT => X_MSG_COUNT
1033 ,X_MSG_DATA => X_MSG_DATA
1034 ,X_RETURN_STATUS => X_RETURN_STATUS);
1035 End Delete_obstacles;
1036
1037
1038
1039
1040 -- Item-level validation procedures
1041 PROCEDURE Validate_LEAD_OBSTACLE_ID (
1042 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1043 P_Validation_mode IN VARCHAR2,
1044 P_LEAD_OBSTACLE_ID IN NUMBER,
1045 -- Hint: You may add 'X_Item_Property_Rec OUT AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1046 X_Return_Status OUT NOCOPY VARCHAR2,
1047 X_Msg_Count OUT NOCOPY NUMBER,
1048 X_Msg_Data OUT NOCOPY VARCHAR2
1049 )
1050 IS
1051 CURSOR C_Lead_Obstacle_Id_Exists (c_Lead_Obstacle_Id NUMBER) IS
1052 SELECT 'X'
1053 FROM as_lead_obstacles
1054 WHERE lead_Obstacle_id = c_Lead_Obstacle_Id;
1055
1056 l_val VARCHAR2(1);
1057 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lobpv.Validate_LEAD_OBSTACLE_ID';
1058
1059 BEGIN
1060
1061 -- Initialize message list if p_init_msg_list is set to TRUE.
1062 IF FND_API.to_Boolean( p_init_msg_list )
1063 THEN
1064 FND_MSG_PUB.initialize;
1065 END IF;
1066
1067
1068 -- Initialize API return status to SUCCESS
1069 x_return_status := FND_API.G_RET_STS_SUCCESS;
1070
1071 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
1072 THEN
1073
1074 IF (p_LEAD_OBSTACLE_ID is not NULL) and (p_LEAD_OBSTACLE_ID <> FND_API.G_MISS_NUM)
1075 THEN
1076 OPEN C_Lead_Obstacle_Id_Exists (p_Lead_Obstacle_Id);
1077 FETCH C_Lead_Obstacle_Id_Exists into l_val;
1078
1079 IF C_Lead_Obstacle_Id_Exists%FOUND THEN
1080 AS_UTILITY_PVT.Set_Message(
1081 p_module => l_module,
1082 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1083 p_msg_name => 'API_DUPLICATE_LEAD_OBSTACLE_ID');
1084
1085 x_return_status := FND_API.G_RET_STS_ERROR;
1086 END IF;
1087
1088 CLOSE C_Lead_Obstacle_Id_Exists;
1089 END IF;
1090
1091 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
1092 THEN
1093
1094 IF (p_LEAD_OBSTACLE_ID is NULL) or (p_LEAD_OBSTACLE_ID = FND_API.G_MISS_NUM)
1095 THEN
1096 AS_UTILITY_PVT.Set_Message(
1097 p_module => l_module,
1098 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1099 p_msg_name => 'API_MISSING_LEAD_OBSTACLE_ID');
1100
1101 x_return_status := FND_API.G_RET_STS_ERROR;
1102 ELSE
1103 OPEN C_Lead_Obstacle_Id_Exists (p_Lead_Obstacle_Id);
1104 FETCH C_Lead_Obstacle_Id_Exists into l_val;
1105
1106 IF C_Lead_Obstacle_Id_Exists%NOTFOUND
1107 THEN
1108 AS_UTILITY_PVT.Set_Message(
1109 p_module => l_module,
1110 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1111 p_msg_name => 'API_INVALID_LEAD_OBSTACLE_ID',
1112 p_token1 => 'VALUE',
1113 p_token1_value => p_LEAD_OBSTACLE_ID );
1114
1115 x_return_status := FND_API.G_RET_STS_ERROR;
1116 END IF;
1117
1118 CLOSE C_Lead_Obstacle_Id_Exists;
1119 END IF;
1120
1121 END IF;
1122
1123 -- Standard call to get message count and if count is 1, get message info.
1124 FND_MSG_PUB.Count_And_Get
1125 ( p_count => x_msg_count,
1126 p_data => x_msg_data
1127 );
1128
1129 END Validate_LEAD_OBSTACLE_ID;
1130
1131
1132 PROCEDURE Validate_LEAD_ID (
1133 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1134 P_Validation_mode IN VARCHAR2,
1135 P_LEAD_ID IN NUMBER,
1136 -- Hint: You may add 'X_Item_Property_Rec OUT AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1137 X_Return_Status OUT NOCOPY VARCHAR2,
1138 X_Msg_Count OUT NOCOPY NUMBER,
1139 X_Msg_Data OUT NOCOPY VARCHAR2
1140 )
1141 IS
1142 CURSOR C_Lead_Id_Exists (c_Lead_Id NUMBER) IS
1143 SELECT 'X'
1144 FROM as_leads_all
1145 WHERE lead_id = c_Lead_Id;
1146
1147 l_val VARCHAR2(1);
1148 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1149 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lobpv.Validate_LEAD_ID';
1150
1151 BEGIN
1152
1153 -- Initialize message list if p_init_msg_list is set to TRUE.
1154 IF FND_API.to_Boolean( p_init_msg_list )
1155 THEN
1156 FND_MSG_PUB.initialize;
1157 END IF;
1158
1159
1160 -- Initialize API return status to SUCCESS
1161 x_return_status := FND_API.G_RET_STS_SUCCESS;
1162
1163 -- validate NOT NULL column
1164 IF(p_LEAD_ID is NULL)
1165 THEN
1166 IF l_debug THEN
1167 AS_UTILITY_PVT.Debug_Message(l_module,
1168 'ERROR',
1169 'Private obstacle API: -Violate NOT NULL constraint(LEAD_ID)');
1170 END IF;
1171
1172 x_return_status := FND_API.G_RET_STS_ERROR;
1173 END IF;
1174
1175 IF (p_LEAD_ID is NULL) or (p_LEAD_ID = FND_API.G_MISS_NUM)
1176 THEN
1177 AS_UTILITY_PVT.Set_Message(
1178 p_module => l_module,
1179 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1180 p_msg_name => 'API_MISSING_LEAD_ID');
1181
1182 x_return_status := FND_API.G_RET_STS_ERROR;
1183 ELSE
1184 OPEN C_Lead_Id_Exists (p_Lead_Id);
1185 FETCH C_Lead_Id_Exists into l_val;
1186
1187 IF C_Lead_Id_Exists%NOTFOUND
1188 THEN
1189 AS_UTILITY_PVT.Set_Message(
1190 p_module => l_module,
1191 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1192 p_msg_name => 'API_INVALID_LEAD_ID',
1193 p_token1 => 'VALUE',
1194 p_token1_value => p_LEAD_ID );
1195
1196 x_return_status := FND_API.G_RET_STS_ERROR;
1197 END IF;
1198
1199 CLOSE C_Lead_Id_Exists;
1200 END IF;
1201
1202 -- Standard call to get message count and if count is 1, get message info.
1203 FND_MSG_PUB.Count_And_Get
1204 ( p_count => x_msg_count,
1205 p_data => x_msg_data
1206 );
1207
1208 END Validate_LEAD_ID;
1209
1210
1211 PROCEDURE Validate_OBSTACLE_CODE (
1212 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1213 P_Validation_mode IN VARCHAR2,
1214 P_OBSTACLE_CODE IN VARCHAR2,
1215 -- Hint: You may add 'X_Item_Property_Rec OUT AS_UTILITY_PVT.ITEM_PROPERTY_REC_TYPE' here if you'd like to pass back item property.
1216 X_Return_Status OUT NOCOPY VARCHAR2,
1217 X_Msg_Count OUT NOCOPY NUMBER,
1218 X_Msg_Data OUT NOCOPY VARCHAR2
1219 )
1220 IS
1221 CURSOR C_OBSTACLE_CODE_Exists (c_lookup_type VARCHAR2,
1222 c_OBSTACLE_CODE VARCHAR2) IS
1223 SELECT 'X'
1224 FROM as_lookups
1225 WHERE lookup_type = c_lookup_type
1226 and lookup_code = c_OBSTACLE_CODE
1227 and enabled_flag = 'Y';
1228
1229 l_val VARCHAR2(1);
1230 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1231 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lobpv.Validate_OBSTACLE_CODE';
1232 BEGIN
1233
1234 -- Initialize message list if p_init_msg_list is set to TRUE.
1235 IF FND_API.to_Boolean( p_init_msg_list )
1236 THEN
1237 FND_MSG_PUB.initialize;
1238 END IF;
1239
1240
1241 -- Initialize API return status to SUCCESS
1242 x_return_status := FND_API.G_RET_STS_SUCCESS;
1243
1244 IF(p_OBSTACLE_CODE is NOT NULL) and (p_OBSTACLE_CODE <> FND_API.G_MISS_CHAR)
1245 THEN
1246 -- OBSTACLE_CODE should exist in as_lookups
1247 OPEN C_OBSTACLE_CODE_Exists ('ISSUE', p_OBSTACLE_CODE);
1248 FETCH C_OBSTACLE_CODE_Exists into l_val;
1249
1250 IF C_OBSTACLE_CODE_Exists%NOTFOUND THEN
1251 -- AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR,
1252 -- 'Private API: OBSTACLE_CODE is invalid');
1253
1254 AS_UTILITY_PVT.Set_Message(
1255 p_module => l_module,
1256 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
1257 p_msg_name => 'API_INVALID_OBSTACLE_CODE',
1258 p_token1 => 'VALUE',
1259 p_token1_value => p_OBSTACLE_CODE );
1260
1261 x_return_status := FND_API.G_RET_STS_ERROR;
1262 END IF;
1263
1264 CLOSE C_OBSTACLE_CODE_Exists;
1265 END IF;
1266
1267 -- Standard call to get message count and if count is 1, get message info.
1268 FND_MSG_PUB.Count_And_Get
1269 ( p_count => x_msg_count,
1270 p_data => x_msg_data
1271 );
1272
1273 END Validate_OBSTACLE_CODE;
1274
1275
1276 -- Hint: inter-field level validation can be added here.
1277 -- Hint: If p_validation_mode = AS_UTILITY_PVT.G_VALIDATE_UPDATE, we should use cursor
1278 -- to get old values for all fields used in inter-field validation and set all G_MISS_XXX fields to original value
1279 -- stored in database table.
1280 PROCEDURE Validate_Obstacle_rec(
1281 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1282 P_Validation_mode IN VARCHAR2,
1283 P_Obstacle_Rec IN AS_OPPORTUNITY_PUB.Obstacle_Rec_Type,
1284 X_Return_Status OUT NOCOPY VARCHAR2,
1285 X_Msg_Count OUT NOCOPY NUMBER,
1286 X_Msg_Data OUT NOCOPY VARCHAR2
1287 )
1288 IS
1289 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1290 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lobpv.Validate_Obstacle_rec';
1291 BEGIN
1292
1293 -- Initialize message list if p_init_msg_list is set to TRUE.
1294 IF FND_API.to_Boolean( p_init_msg_list )
1295 THEN
1296 FND_MSG_PUB.initialize;
1297 END IF;
1298
1299
1300 -- Initialize API return status to SUCCESS
1301 x_return_status := FND_API.G_RET_STS_SUCCESS;
1302
1303 -- Hint: Validate data
1304 -- If data not valid
1305 -- THEN
1306 -- x_return_status := FND_API.G_RET_STS_ERROR;
1307
1308 -- Debug Message
1309 IF l_debug THEN
1310 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'API_INVALID_RECORD');
1311 END IF;
1312
1313
1314 -- Standard call to get message count and if count is 1, get message info.
1315 FND_MSG_PUB.Count_And_Get
1316 ( p_count => x_msg_count,
1317 p_data => x_msg_data
1318 );
1319
1320 END Validate_Obstacle_Rec;
1321
1322 PROCEDURE Validate_obstacle(
1323 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
1324 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1325 P_Validation_mode IN VARCHAR2,
1326 P_Obstacle_Rec IN AS_OPPORTUNITY_PUB.Obstacle_Rec_Type,
1327 X_Return_Status OUT NOCOPY VARCHAR2,
1328 X_Msg_Count OUT NOCOPY NUMBER,
1329 X_Msg_Data OUT NOCOPY VARCHAR2
1330 )
1331 IS
1332 l_api_name CONSTANT VARCHAR2(30) := 'Validate_obstacle';
1333 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
1334 l_module CONSTANT VARCHAR2(255) := 'as.plsql.lobpv.Validate_obstacle';
1335 BEGIN
1336
1337 -- Debug Message
1338 IF l_debug THEN
1339 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
1340 END IF;
1341
1342
1343
1344 -- Initialize API return status to SUCCESS
1345 x_return_status := FND_API.G_RET_STS_SUCCESS;
1346
1347 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM) THEN
1348 -- Hint: We provide validation procedure for every column. Developer should delete
1349 -- unnecessary validation procedures.
1350 Validate_LEAD_OBSTACLE_ID(
1351 p_init_msg_list => FND_API.G_FALSE,
1352 p_validation_mode => p_validation_mode,
1353 p_LEAD_OBSTACLE_ID => P_Obstacle_Rec.LEAD_OBSTACLE_ID,
1354 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1355 x_return_status => x_return_status,
1356 x_msg_count => x_msg_count,
1357 x_msg_data => x_msg_data);
1358 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1359 raise FND_API.G_EXC_ERROR;
1360 END IF;
1361
1362 Validate_LEAD_ID(
1363 p_init_msg_list => FND_API.G_FALSE,
1364 p_validation_mode => p_validation_mode,
1365 p_LEAD_ID => P_Obstacle_Rec.LEAD_ID,
1366 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1367 x_return_status => x_return_status,
1368 x_msg_count => x_msg_count,
1369 x_msg_data => x_msg_data);
1370 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1371 raise FND_API.G_EXC_ERROR;
1372 END IF;
1373
1374 Validate_OBSTACLE_CODE(
1375 p_init_msg_list => FND_API.G_FALSE,
1376 p_validation_mode => p_validation_mode,
1377 p_OBSTACLE_CODE => P_Obstacle_Rec.OBSTACLE_CODE,
1378 -- Hint: You may add x_item_property_rec as one of your OUT parameter if you'd like to pass back item property.
1379 x_return_status => x_return_status,
1380 x_msg_count => x_msg_count,
1381 x_msg_data => x_msg_data);
1382 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1383 raise FND_API.G_EXC_ERROR;
1384 END IF;
1385
1386 END IF;
1387
1388 /*
1389 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_RECORD) THEN
1390 -- Hint: Inter-field level validation can be added here
1391 -- invoke record level validation procedures
1392 Validate_Obstacle_Rec(
1393 p_init_msg_list => FND_API.G_FALSE,
1394 p_validation_mode => p_validation_mode,
1395 P_Obstacle_Rec => P_Obstacle_Rec,
1396 x_return_status => x_return_status,
1397 x_msg_count => x_msg_count,
1398 x_msg_data => x_msg_data);
1399
1400 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1401 raise FND_API.G_EXC_ERROR;
1402 END IF;
1403 END IF;
1404 */
1405
1406 /*
1407 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_RECORD) THEN
1408 -- invoke inter-record level validation procedures
1409 NULL;
1410 END IF;
1411 */
1412
1413 /*
1414 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_ENTITY) THEN
1415 -- invoke inter-entity level validation procedures
1416 NULL;
1417 END IF;
1418 */
1419
1420
1421 -- Debug Message
1422 IF l_debug THEN
1423 AS_UTILITY_PVT.Debug_Message(l_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
1424 END IF;
1425
1426
1427 END Validate_obstacle;
1428
1429 End AS_OPP_OBSTACLE_PVT;