[Home] [Help]
PACKAGE BODY: APPS.PV_SELATTVAL_PVT
Source
1 PACKAGE BODY PV_SELATTVAL_PVT as
2 /* $Header: pvrvsavb.pls 120.1 2005/12/06 14:17:00 amaram noship $ */
3 -- Start of Comments
4 -- Package name : PV_SELATTVAL_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_SELATTVAL_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvrvsavb.pls';
13
14
15 -- Hint: Primary key needs to be returned.
16 AS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
17 AS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
18 AS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
19
20 PROCEDURE Create_selattval(
21 P_Api_Version_Number IN NUMBER,
22 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
23 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
24 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
25 P_Identity_Resource_Id IN NUMBER,
26 P_SELATTVAL_Rec IN PV_RULE_RECTYPE_PUB.SELATTVAL_Rec_Type
27 := PV_RULE_RECTYPE_PUB.G_MISS_SELATTVAL_REC,
28 X_ATTR_VALUE_ID OUT NOCOPY NUMBER,
29 X_Return_Status OUT NOCOPY VARCHAR2,
30 X_Msg_Count OUT NOCOPY NUMBER,
31 X_Msg_Data OUT NOCOPY VARCHAR2
32 )
33
34 IS
35 l_api_name CONSTANT VARCHAR2(30) := 'Create_selattval';
36 l_api_version_number CONSTANT NUMBER := 2.0;
37 l_return_status_full VARCHAR2(1);
38 l_access_flag VARCHAR2(1);
39 BEGIN
40 -- Standard Start of API savepoint
41 SAVEPOINT CREATE_SELATTVAL_PVT;
42
43 -- Standard call to check for call compatibility.
44 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
45 p_api_version_number,
46 l_api_name,
47 G_PKG_NAME)
48 THEN
49 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
50 END IF;
51
52
53 -- Initialize message list if p_init_msg_list is set to TRUE.
54 IF FND_API.to_Boolean( p_init_msg_list )
55 THEN
56 FND_MSG_PUB.initialize;
57 END IF;
58
59
60 -- Debug Message
61 IF (AS_DEBUG_HIGH_ON) THEN
62
63 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
64 END IF;
65
66
67 -- Initialize API return status to SUCCESS
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 --
71 -- API body
72 --
73 -- ******************************************************************
74 -- Validate Environment
75 -- ******************************************************************
76 IF FND_GLOBAL.User_Id IS NULL
77 THEN
78 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
79 THEN
80 FND_MESSAGE.Set_Name('PV', 'UT_CANNOT_GET_PROFILE_VALUE');
81 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
82 FND_MSG_PUB.ADD;
83 END IF;
84 RAISE FND_API.G_EXC_ERROR;
85 END IF;
86
87 -- Debug message
88 IF (AS_DEBUG_HIGH_ON) THEN
89
90 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_selattval');
91 END IF;
92
93 -- Invoke validation procedures
94 Validate_selattval(
95 p_init_msg_list => FND_API.G_FALSE,
96 p_validation_level => p_validation_level,
97 p_validation_mode => AS_UTILITY_PVT.G_CREATE,
98 P_SELATTVAL_Rec => P_SELATTVAL_Rec,
99 x_return_status => x_return_status,
100 x_msg_count => x_msg_count,
101 x_msg_data => x_msg_data);
102
103 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
104 RAISE FND_API.G_EXC_ERROR;
105 END IF;
106
107 -- Hint: Add corresponding Master-Detail business logic here if necessary.
108
109 -- Debug Message
110 IF (AS_DEBUG_HIGH_ON) THEN
111
112 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling create table handler');
113 END IF;
114
115 -- Invoke table handler(PV_SELECTED_ATTR_VALUES_PKG.Insert_Row)
116 PV_SELECTED_ATTR_VALUES_PKG.Insert_Row(
117 px_ATTR_VALUE_ID => x_ATTR_VALUE_ID
118 ,p_LAST_UPDATE_DATE => SYSDATE
119 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
120 ,p_CREATION_DATE => SYSDATE
121 ,p_CREATED_BY => FND_GLOBAL.USER_ID
122 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
123 ,p_OBJECT_VERSION_NUMBER => p_SELATTVAL_rec.OBJECT_VERSION_NUMBER
124 ,p_REQUEST_ID => p_SELATTVAL_rec.REQUEST_ID
125 ,p_PROGRAM_APPLICATION_ID => p_SELATTVAL_rec.PROGRAM_APPLICATION_ID
126 ,p_PROGRAM_ID => p_SELATTVAL_rec.PROGRAM_ID
127 ,p_PROGRAM_UPDATE_DATE => p_SELATTVAL_rec.PROGRAM_UPDATE_DATE
128 ,p_SELECTION_CRITERIA_ID => p_SELATTVAL_rec.SELECTION_CRITERIA_ID
129 ,p_ATTRIBUTE_VALUE => p_SELATTVAL_rec.ATTRIBUTE_VALUE
130 ,p_ATTRIBUTE_TO_VALUE => p_SELATTVAL_rec.ATTRIBUTE_TO_VALUE
131 ,p_ATTRIBUTE_CATEGORY => p_SELATTVAL_rec.ATTRIBUTE_CATEGORY
132 ,p_ATTRIBUTE1 => p_SELATTVAL_rec.ATTRIBUTE1
133 ,p_ATTRIBUTE2 => p_SELATTVAL_rec.ATTRIBUTE2
134 ,p_ATTRIBUTE3 => p_SELATTVAL_rec.ATTRIBUTE3
135 ,p_ATTRIBUTE4 => p_SELATTVAL_rec.ATTRIBUTE4
136 ,p_ATTRIBUTE5 => p_SELATTVAL_rec.ATTRIBUTE5
137 ,p_ATTRIBUTE6 => p_SELATTVAL_rec.ATTRIBUTE6
138 ,p_ATTRIBUTE7 => p_SELATTVAL_rec.ATTRIBUTE7
139 ,p_ATTRIBUTE8 => p_SELATTVAL_rec.ATTRIBUTE8
140 ,p_ATTRIBUTE9 => p_SELATTVAL_rec.ATTRIBUTE9
141 ,p_ATTRIBUTE10 => p_SELATTVAL_rec.ATTRIBUTE10
142 ,p_ATTRIBUTE11 => p_SELATTVAL_rec.ATTRIBUTE11
143 ,p_ATTRIBUTE12 => p_SELATTVAL_rec.ATTRIBUTE12
144 ,p_ATTRIBUTE13 => p_SELATTVAL_rec.ATTRIBUTE13
145 ,p_ATTRIBUTE14 => p_SELATTVAL_rec.ATTRIBUTE14
146 ,p_ATTRIBUTE15 => p_SELATTVAL_rec.ATTRIBUTE15
147 ,p_SCORE => p_SELATTVAL_rec.SCORE);
148 -- Hint: Primary key should be returned.
149 -- x_ATTR_VALUE_ID := px_ATTR_VALUE_ID;
150
151 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
152 RAISE FND_API.G_EXC_ERROR;
153 END IF;
154
155 --
156 -- End of API body
157 --
158
159 -- Standard check for p_commit
160 IF FND_API.to_Boolean( p_commit )
161 THEN
162 COMMIT WORK;
163 END IF;
164
165
166 -- Debug Message
167 IF (AS_DEBUG_HIGH_ON) THEN
168
169 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
170 END IF;
171
172
173 -- Standard call to get message count and if count is 1, get message info.
174 FND_MSG_PUB.Count_And_Get
175 ( p_count => x_msg_count,
176 p_data => x_msg_data
177 );
178
179 EXCEPTION
180 WHEN FND_API.G_EXC_ERROR THEN
181 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
182 P_API_NAME => L_API_NAME
183 ,P_PKG_NAME => G_PKG_NAME
184 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
185 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
186 ,X_MSG_COUNT => X_MSG_COUNT
187 ,X_MSG_DATA => X_MSG_DATA
188 ,X_RETURN_STATUS => X_RETURN_STATUS);
189
190 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
191 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
192 P_API_NAME => L_API_NAME
193 ,P_PKG_NAME => G_PKG_NAME
194 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
195 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
196 ,X_MSG_COUNT => X_MSG_COUNT
197 ,X_MSG_DATA => X_MSG_DATA
198 ,X_RETURN_STATUS => X_RETURN_STATUS);
199
200 WHEN OTHERS THEN
201 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
202 P_API_NAME => L_API_NAME
203 ,P_PKG_NAME => G_PKG_NAME
204 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
205 ,P_SQLCODE => SQLCODE
206 ,P_SQLERRM => SQLERRM
207 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
208 ,X_MSG_COUNT => X_MSG_COUNT
209 ,X_MSG_DATA => X_MSG_DATA
210 ,X_RETURN_STATUS => X_RETURN_STATUS);
211 End Create_selattval;
212
213
214 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
215 PROCEDURE Update_selattval(
216 P_Api_Version_Number IN NUMBER,
217 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
218 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
219 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
220 P_Identity_Resource_Id IN NUMBER,
221 P_SELATTVAL_Rec IN PV_RULE_RECTYPE_PUB.SELATTVAL_Rec_Type,
222 X_Return_Status OUT NOCOPY VARCHAR2,
223 X_Msg_Count OUT NOCOPY NUMBER,
224 X_Msg_Data OUT NOCOPY VARCHAR2
225 )
226
227 IS
228
229 Cursor C_Get_selattval(pc_ATTR_VALUE_ID Number) IS
230 Select object_version_number
231 From PV_SELECTED_ATTR_VALUES
232 where attr_value_id = pc_attr_value_id
233 For Update NOWAIT;
234
235 l_api_name CONSTANT VARCHAR2(30) := 'Update_selattval';
236 l_api_version_number CONSTANT NUMBER := 2.0;
237 -- Local Variables
238 l_ref_SELATTVAL_rec PV_RULE_RECTYPE_PUB.SELATTVAL_Rec_Type;
239 l_tar_SELATTVAL_rec PV_RULE_RECTYPE_PUB.SELATTVAL_Rec_Type := P_SELATTVAL_Rec;
240 l_rowid ROWID;
241 BEGIN
242 -- Standard Start of API savepoint
243 SAVEPOINT UPDATE_SELATTVAL_PVT;
244
245 -- Standard call to check for call compatibility.
246 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
247 p_api_version_number,
248 l_api_name,
249 G_PKG_NAME)
250 THEN
251 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
252 END IF;
253
254
255 -- Initialize message list if p_init_msg_list is set to TRUE.
256 IF FND_API.to_Boolean( p_init_msg_list )
257 THEN
258 FND_MSG_PUB.initialize;
259 END IF;
260
261
262 -- Debug Message
263 IF (AS_DEBUG_HIGH_ON) THEN
264
265 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
266 END IF;
267
268
269 -- Initialize API return status to SUCCESS
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271
272 --
273 -- Api body
274 --
275
276 -- Debug Message
277 IF (AS_DEBUG_HIGH_ON) THEN
278
279 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Open Cursor to Select');
280 END IF;
281
282 Open C_Get_selattval( l_tar_SELATTVAL_rec.ATTR_VALUE_ID);
283 Fetch C_Get_selattval into
284 l_ref_SELATTVAL_rec.OBJECT_VERSION_NUMBER;
285
286 If ( C_Get_selattval%NOTFOUND) Then
287 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
288 THEN
289 FND_MESSAGE.Set_Name('PV', 'API_MISSING_UPDATE_TARGET');
290 FND_MESSAGE.Set_Token ('INFO', 'selattval', FALSE);
291 FND_MSG_PUB.Add;
292 END IF;
293 Close C_Get_selattval;
294 raise FND_API.G_EXC_ERROR;
295 END IF;
296 -- Debug Message
297 IF (AS_DEBUG_HIGH_ON) THEN
298
299 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Close Cursor');
300 END IF;
301 Close C_Get_selattval;
302
303
304 If (l_tar_SELATTVAL_rec.object_version_number is NULL or
305 l_tar_SELATTVAL_rec.object_version_number = FND_API.G_MISS_NUM ) Then
306 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
307 THEN
308 FND_MESSAGE.Set_Name('PV', 'API_MISSING_ID');
309 FND_MESSAGE.Set_Token('COLUMN', 'object_version_number', FALSE);
310 FND_MSG_PUB.ADD;
311 END IF;
312 raise FND_API.G_EXC_ERROR;
313 End if;
314 -- Check Whether record has been changed by someone else
315 If (l_tar_SELATTVAL_rec.object_version_number <> l_ref_SELATTVAL_rec.object_version_number) Then
316 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
317 THEN
318 FND_MESSAGE.Set_Name('PV', 'API_RECORD_CHANGED');
319 FND_MESSAGE.Set_Token('INFO', 'selattval', FALSE);
320 FND_MSG_PUB.ADD;
321 END IF;
322 raise FND_API.G_EXC_ERROR;
323 End if;
324
325 -- Debug message
326 IF (AS_DEBUG_HIGH_ON) THEN
327
328 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_selattval');
329 END IF;
330
331 -- Invoke validation procedures
332 Validate_selattval(
333 p_init_msg_list => FND_API.G_FALSE,
334 p_validation_level => p_validation_level,
335 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
336 P_SELATTVAL_Rec => P_SELATTVAL_Rec,
337 x_return_status => x_return_status,
338 x_msg_count => x_msg_count,
339 x_msg_data => x_msg_data);
340
341 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
342 RAISE FND_API.G_EXC_ERROR;
343 END IF;
344
345 -- Hint: Add corresponding Master-Detail business logic here if necessary.
346
347 -- Debug Message
348 IF (AS_DEBUG_HIGH_ON) THEN
349
350 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
351 END IF;
352
353 -- Invoke table handler(PV_SELECTED_ATTR_VALUES_PKG.Update_Row)
354 PV_SELECTED_ATTR_VALUES_PKG.Update_Row(
355 p_ATTR_VALUE_ID => p_SELATTVAL_rec.ATTR_VALUE_ID
356 ,p_LAST_UPDATE_DATE => SYSDATE
357 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
358 ,p_CREATION_DATE => FND_API.G_MISS_DATE
359 ,p_CREATED_BY => FND_API.G_MISS_NUM
360 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
361 ,p_OBJECT_VERSION_NUMBER => p_SELATTVAL_rec.OBJECT_VERSION_NUMBER
362 ,p_REQUEST_ID => p_SELATTVAL_rec.REQUEST_ID
363 ,p_PROGRAM_APPLICATION_ID => p_SELATTVAL_rec.PROGRAM_APPLICATION_ID
364 ,p_PROGRAM_ID => p_SELATTVAL_rec.PROGRAM_ID
365 ,p_PROGRAM_UPDATE_DATE => p_SELATTVAL_rec.PROGRAM_UPDATE_DATE
366 ,p_SELECTION_CRITERIA_ID => p_SELATTVAL_rec.SELECTION_CRITERIA_ID
367 ,p_ATTRIBUTE_VALUE => p_SELATTVAL_rec.ATTRIBUTE_VALUE
368 ,p_ATTRIBUTE_TO_VALUE => p_SELATTVAL_rec.ATTRIBUTE_TO_VALUE
369 ,p_ATTRIBUTE_CATEGORY => p_SELATTVAL_rec.ATTRIBUTE_CATEGORY
370 ,p_ATTRIBUTE1 => p_SELATTVAL_rec.ATTRIBUTE1
371 ,p_ATTRIBUTE2 => p_SELATTVAL_rec.ATTRIBUTE2
372 ,p_ATTRIBUTE3 => p_SELATTVAL_rec.ATTRIBUTE3
373 ,p_ATTRIBUTE4 => p_SELATTVAL_rec.ATTRIBUTE4
374 ,p_ATTRIBUTE5 => p_SELATTVAL_rec.ATTRIBUTE5
375 ,p_ATTRIBUTE6 => p_SELATTVAL_rec.ATTRIBUTE6
376 ,p_ATTRIBUTE7 => p_SELATTVAL_rec.ATTRIBUTE7
377 ,p_ATTRIBUTE8 => p_SELATTVAL_rec.ATTRIBUTE8
378 ,p_ATTRIBUTE9 => p_SELATTVAL_rec.ATTRIBUTE9
379 ,p_ATTRIBUTE10 => p_SELATTVAL_rec.ATTRIBUTE10
380 ,p_ATTRIBUTE11 => p_SELATTVAL_rec.ATTRIBUTE11
381 ,p_ATTRIBUTE12 => p_SELATTVAL_rec.ATTRIBUTE12
382 ,p_ATTRIBUTE13 => p_SELATTVAL_rec.ATTRIBUTE13
383 ,p_ATTRIBUTE14 => p_SELATTVAL_rec.ATTRIBUTE14
384 ,p_ATTRIBUTE15 => p_SELATTVAL_rec.ATTRIBUTE15
385 ,p_SCORE => p_SELATTVAL_rec.SCORE);
386 --
387 -- End of API body.
388 --
389
390 -- Standard check for p_commit
391 IF FND_API.to_Boolean( p_commit )
392 THEN
393 COMMIT WORK;
394 END IF;
395
396
397 -- Debug Message
398 IF (AS_DEBUG_HIGH_ON) THEN
399
400 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
401 END IF;
402
403
404 -- Standard call to get message count and if count is 1, get message info.
405 FND_MSG_PUB.Count_And_Get
406 ( p_count => x_msg_count,
407 p_data => x_msg_data
408 );
409
410 EXCEPTION
411 WHEN FND_API.G_EXC_ERROR THEN
412 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
413 P_API_NAME => L_API_NAME
414 ,P_PKG_NAME => G_PKG_NAME
415 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
416 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
417 ,X_MSG_COUNT => X_MSG_COUNT
418 ,X_MSG_DATA => X_MSG_DATA
419 ,X_RETURN_STATUS => X_RETURN_STATUS);
420
421 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
422 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
423 P_API_NAME => L_API_NAME
424 ,P_PKG_NAME => G_PKG_NAME
425 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
426 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
427 ,X_MSG_COUNT => X_MSG_COUNT
428 ,X_MSG_DATA => X_MSG_DATA
429 ,X_RETURN_STATUS => X_RETURN_STATUS);
430
431 WHEN OTHERS THEN
432 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
433 P_API_NAME => L_API_NAME
434 ,P_PKG_NAME => G_PKG_NAME
435 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
436 ,P_SQLCODE => SQLCODE
437 ,P_SQLERRM => SQLERRM
438 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
439 ,X_MSG_COUNT => X_MSG_COUNT
440 ,X_MSG_DATA => X_MSG_DATA
441 ,X_RETURN_STATUS => X_RETURN_STATUS);
442 End Update_selattval;
443
444
445 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
446 -- The Master delete procedure may not be needed depends on different business requirements.
447 PROCEDURE Delete_selattval(
448 P_Api_Version_Number IN NUMBER,
449 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
450 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
451 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
452 P_Identity_Resource_Id IN NUMBER,
453 P_SELATTVAL_Rec IN PV_RULE_RECTYPE_PUB.SELATTVAL_Rec_Type,
454 X_Return_Status OUT NOCOPY VARCHAR2,
455 X_Msg_Count OUT NOCOPY NUMBER,
456 X_Msg_Data OUT NOCOPY VARCHAR2
457 )
458
459 IS
460 l_api_name CONSTANT VARCHAR2(30) := 'Delete_selattval';
461 l_api_version_number CONSTANT NUMBER := 2.0;
462 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
463 BEGIN
464 -- Standard Start of API savepoint
465 SAVEPOINT DELETE_SELATTVAL_PVT;
466
467 -- Standard call to check for call compatibility.
468 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
469 p_api_version_number,
470 l_api_name,
471 G_PKG_NAME)
472 THEN
473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
474 END IF;
475
476
477 -- Initialize message list if p_init_msg_list is set to TRUE.
478 IF FND_API.to_Boolean( p_init_msg_list )
479 THEN
480 FND_MSG_PUB.initialize;
481 END IF;
482
483
484 -- Debug Message
485 IF (AS_DEBUG_HIGH_ON) THEN
486
487 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
488 END IF;
489
490
491 -- Initialize API return status to SUCCESS
492 x_return_status := FND_API.G_RET_STS_SUCCESS;
493
494 --
495 -- Api body
496 --
497
498 -- Debug Message
499 IF (AS_DEBUG_HIGH_ON) THEN
500
501 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling delete table handler');
502 END IF;
503
504 -- Invoke table handler(PV_SELECTED_ATTR_VALUES_PKG.Delete_Row)
505 PV_SELECTED_ATTR_VALUES_PKG.Delete_Row(
506 p_ATTR_VALUE_ID => p_SELATTVAL_rec.ATTR_VALUE_ID);
507 --
508 -- End of API body
509 --
510
511 -- Standard check for p_commit
512 IF FND_API.to_Boolean( p_commit )
513 THEN
514 COMMIT WORK;
515 END IF;
516
517
518 -- Debug Message
519 IF (AS_DEBUG_HIGH_ON) THEN
520
521 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
522 END IF;
523
524
525 -- Standard call to get message count and if count is 1, get message info.
526 FND_MSG_PUB.Count_And_Get
527 ( p_count => x_msg_count,
528 p_data => x_msg_data
529 );
530
531 EXCEPTION
532 WHEN FND_API.G_EXC_ERROR THEN
533 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
534 P_API_NAME => L_API_NAME
535 ,P_PKG_NAME => G_PKG_NAME
536 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
537 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
538 ,X_MSG_COUNT => X_MSG_COUNT
539 ,X_MSG_DATA => X_MSG_DATA
540 ,X_RETURN_STATUS => X_RETURN_STATUS);
541
542 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
543 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
544 P_API_NAME => L_API_NAME
545 ,P_PKG_NAME => G_PKG_NAME
546 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
547 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
548 ,X_MSG_COUNT => X_MSG_COUNT
549 ,X_MSG_DATA => X_MSG_DATA
550 ,X_RETURN_STATUS => X_RETURN_STATUS);
551
552 WHEN OTHERS THEN
553 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
554 P_API_NAME => L_API_NAME
555 ,P_PKG_NAME => G_PKG_NAME
556 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
557 ,P_SQLCODE => SQLCODE
558 ,P_SQLERRM => SQLERRM
559 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
560 ,X_MSG_COUNT => X_MSG_COUNT
561 ,X_MSG_DATA => X_MSG_DATA
562 ,X_RETURN_STATUS => X_RETURN_STATUS);
563 End Delete_selattval;
564
565
566 -- Item-level validation procedures
567 PROCEDURE Validate_ATTR_VALUE_ID (
568 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
569 P_Validation_mode IN VARCHAR2,
570 P_ATTR_VALUE_ID IN NUMBER,
571 X_Return_Status OUT NOCOPY VARCHAR2,
572 X_Msg_Count OUT NOCOPY NUMBER,
573 X_Msg_Data OUT NOCOPY VARCHAR2
574 )
575 IS
576 CURSOR C_attr_value_Id_Exists (pc_attr_value_id NUMBER) IS
577 SELECT 'X'
578 FROM pv_selected_attr_values
579 WHERE attr_value_id = pc_attr_value_id;
580
581 l_val VARCHAR2(1);
582 BEGIN
583
584 -- Initialize message list if p_init_msg_list is set to TRUE.
585 IF FND_API.to_Boolean( p_init_msg_list )
586 THEN
587 FND_MSG_PUB.initialize;
588 END IF;
589
590
591 -- Initialize API return status to SUCCESS
592 x_return_status := FND_API.G_RET_STS_SUCCESS;
593
594 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
595 THEN
596 IF (p_attr_value_id IS NOT NULL) AND
597 (p_attr_value_id <> FND_API.G_MISS_NUM)
598 THEN
599 AS_UTILITY_PVT.Set_Message(
600 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
601 p_msg_name => 'API_INVALID_ID',
602 p_token1 => 'attr_value_id',
603 p_token1_value => p_attr_value_id);
604
605 x_return_status := FND_API.G_RET_STS_ERROR;
606 END IF;
607
608 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
609 THEN
610 -- validate NOT NULL column
611 IF (p_attr_value_id IS NULL) OR
612 (p_attr_value_id = FND_API.G_MISS_NUM)
613 THEN
614 AS_UTILITY_PVT.Set_Message(
615 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
616 p_msg_name => 'API_MISSING_LEAD_ID');
617
618 x_return_status := FND_API.G_RET_STS_ERROR;
619 ELSE
620 OPEN C_attr_value_id_exists (p_attr_value_id);
621 FETCH C_attr_value_id_exists into l_val;
622
623 IF C_attr_value_id_exists%NOTFOUND
624 THEN
625 AS_UTILITY_PVT.Set_Message(
626 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
627 p_msg_name => 'API_INVALID_ID',
628 p_token1 => 'attr_value_id',
629 p_token1_value => p_attr_value_id );
630
631 x_return_status := FND_API.G_RET_STS_ERROR;
632 END IF;
633
634 CLOSE C_attr_value_id_exists;
635 END IF;
636 END IF;
637
638
639 -- Standard call to get message count and if count is 1, get message info.
640 FND_MSG_PUB.Count_And_Get
641 ( p_count => x_msg_count,
642 p_data => x_msg_data
643 );
644
645 END Validate_ATTR_VALUE_ID;
646
647
648 PROCEDURE Validate_SELECTION_CRITERIA_ID (
649 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
650 P_Validation_mode IN VARCHAR2,
651 P_SELECTION_CRITERIA_ID IN NUMBER,
652 X_Return_Status OUT NOCOPY VARCHAR2,
653 X_Msg_Count OUT NOCOPY NUMBER,
654 X_Msg_Data OUT NOCOPY VARCHAR2
655 )
656 IS
657 CURSOR C_select_criteria_id_Exists (c_select_criteria_id NUMBER) IS
658 SELECT 'X'
659 FROM pv_enty_select_criteria
660 WHERE selection_criteria_id = c_select_criteria_id;
661
662 l_val VARCHAR2(1);
663
664 BEGIN
665
666 -- Initialize message list if p_init_msg_list is set to TRUE.
667 IF FND_API.to_Boolean( p_init_msg_list )
668 THEN
669 FND_MSG_PUB.initialize;
670 END IF;
671
672
673 -- Initialize API return status to SUCCESS
674 x_return_status := FND_API.G_RET_STS_SUCCESS;
675
676 -- validate NOT NULL column
677 IF(p_selection_criteria_id is NULL)
678 THEN
679 IF (AS_DEBUG_HIGH_ON) THEN
680 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_ERROR, 'Private API: -Violate NOT NULL constraint(selection_criteria_id)');
681 END IF;
682
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 END IF;
685
686 OPEN C_select_criteria_id_Exists (p_selection_criteria_id);
687 FETCH C_select_criteria_id_Exists into l_val;
688
689 IF C_select_criteria_id_Exists%NOTFOUND
690 THEN
691 AS_UTILITY_PVT.Set_Message(
692 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
693 p_msg_name => 'API_INVALID_ID',
694 p_token1 => 'COLUMN',
695 p_token1_value => 'selection_criteria_id',
696 p_token2 => 'VALUE',
697 p_token2_value => p_selection_criteria_id );
698
699 x_return_status := FND_API.G_RET_STS_ERROR;
700 END IF;
701 CLOSE C_select_criteria_id_Exists;
702
703 -- Standard call to get message count and if count is 1, get message info.
704 FND_MSG_PUB.Count_And_Get
705 ( p_count => x_msg_count,
706 p_data => x_msg_data
707 );
708
709 END Validate_SELECTION_CRITERIA_ID;
710
711
712 PROCEDURE Validate_selattval(
713 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
714 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
715 P_Validation_mode IN VARCHAR2,
716 P_SELATTVAL_Rec IN PV_RULE_RECTYPE_PUB.SELATTVAL_Rec_Type,
717 X_Return_Status OUT NOCOPY VARCHAR2,
718 X_Msg_Count OUT NOCOPY NUMBER,
719 X_Msg_Data OUT NOCOPY VARCHAR2
720 )
721 IS
722 --ryellapu duplicated check for criterian
723 cursor lc_criterion_dup_values_check (pc_rule_id number,pc_attribute_id number,pc_attribute_value varchar2,pc_selection_type_code varchar2) is
724 select attribute_value
725 from
726 pv_selected_attr_values sav, pv_enty_select_criteria esc
727 where
728 sav.selection_criteria_id = esc.selection_criteria_id and
729 esc.process_rule_id = pc_rule_id and
730 esc.attribute_id = pc_attribute_id and
731 esc.selection_type_code = pc_selection_type_code and
732 sav.attribute_value = pc_attribute_value;
733
734 l_api_name CONSTANT VARCHAR2(30) := 'Validate_selattval';
735 l_rule_id number;
736 l_attribute_id number;
737 l_attribute_value varchar2(2000);
738 l_selection_type_code varchar2(30);
739 BEGIN
740
741 -- Debug Message
742 IF (AS_DEBUG_HIGH_ON) THEN
743
744 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
745 END IF;
746
747
748 -- Initialize API return status to SUCCESS
749 x_return_status := FND_API.G_RET_STS_SUCCESS;
750
751 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM) THEN
752
753 Validate_ATTR_VALUE_ID(
754 p_init_msg_list => FND_API.G_FALSE,
755 p_validation_mode => p_validation_mode,
756 p_ATTR_VALUE_ID => P_SELATTVAL_Rec.ATTR_VALUE_ID,
757 x_return_status => x_return_status,
758 x_msg_count => x_msg_count,
759 x_msg_data => x_msg_data);
760
761 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
762 raise FND_API.G_EXC_ERROR;
763 END IF;
764
765 pv_common_checks_pvt.Validate_OBJECT_VERSION_NUMBER(
766 p_init_msg_list => FND_API.G_FALSE,
767 p_validation_mode => p_validation_mode,
768 p_OBJECT_VERSION_NUMBER => P_SELATTVAL_Rec.OBJECT_VERSION_NUMBER,
769 x_return_status => x_return_status,
770 x_msg_count => x_msg_count,
771 x_msg_data => x_msg_data);
772
773 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
774 raise FND_API.G_EXC_ERROR;
775 END IF;
776
777 Validate_SELECTION_CRITERIA_ID(
778 p_init_msg_list => FND_API.G_FALSE,
779 p_validation_mode => p_validation_mode,
780 p_SELECTION_CRITERIA_ID => P_SELATTVAL_Rec.SELECTION_CRITERIA_ID,
781 x_return_status => x_return_status,
782 x_msg_count => x_msg_count,
783 x_msg_data => x_msg_data);
784
785 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
786 raise FND_API.G_EXC_ERROR;
787 END IF;
788
789 END IF;
790
791 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_RECORD) THEN
792 -- Hint: Inter-field level validation can be added here
793 -- invoke record level validation procedures
794 -- if p_validation_mode = AS_UTILITY_PVT.G_CREATE then
795 select process_rule_id,attribute_id,selection_type_code into l_rule_id,l_attribute_id,l_selection_type_code from pv_enty_select_criteria
796 where selection_criteria_id = P_SELATTVAL_Rec.selection_criteria_id;
797
798 open lc_criterion_dup_values_check (pc_rule_id => l_rule_id,
799 pc_attribute_id => l_attribute_id,
800 pc_attribute_value => P_SELATTVAL_Rec.attribute_value,
801 pc_selection_type_code => l_selection_type_code);
802
803 fetch lc_criterion_dup_values_check into l_attribute_value;
804 close lc_criterion_dup_values_check;
805
806 if l_attribute_value is not null then
807 FND_MESSAGE.Set_Name('PV', 'PV_DUPLICATE_CRITERIA');
808 FND_MSG_PUB.Add;
809 x_return_status := FND_API.G_RET_STS_ERROR;
810 raise FND_API.G_EXC_ERROR;
811 end if;
812 -- end if;
813 END IF;
814
815 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_RECORD) THEN
816 -- invoke inter-record level validation procedures
817 NULL;
818 END IF;
819
820 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_ENTITY) THEN
821 -- invoke inter-entity level validation procedures
822 NULL;
823 END IF;
824
825
826 -- Debug Message
827 IF (AS_DEBUG_HIGH_ON) THEN
828
829 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
830 END IF;
831
832 END Validate_selattval;
833
834 End PV_SELATTVAL_PVT;