[Home] [Help]
PACKAGE BODY: APPS.PV_SELCRIT_PVT
Source
1 PACKAGE BODY PV_SELCRIT_PVT as
2 /* $Header: pvrvescb.pls 115.9 2004/06/07 22:45:20 solin ship $ */
3 -- Start of Comments
4 -- Package name : PV_SELCRIT_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_SELCRIT_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvrvescb.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_selcrit(
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_SELCRIT_Rec IN PV_RULE_RECTYPE_PUB.SELCRIT_Rec_Type
27 := PV_RULE_RECTYPE_PUB.G_MISS_SELCRIT_REC,
28 X_SELECTION_CRITERIA_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 IS
34 l_api_name CONSTANT VARCHAR2(30) := 'Create_selcrit';
35 l_api_version_number CONSTANT NUMBER := 2.0;
36 l_return_status_full VARCHAR2(1);
37 l_access_flag VARCHAR2(1);
38 l_previous_status VARCHAR2(30);
39 BEGIN
40 -- Standard Start of API savepoint
41 SAVEPOINT CREATE_SELCRIT_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_selcrit');
91 END IF;
92
93 -- Invoke validation procedures
94 Validate_selcrit(
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_SELCRIT_Rec => P_SELCRIT_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 -- ---------------------------------------------------------------------
110 -- Status check for LEAD_MONITOR rules. This is checked only in the case
111 -- of an Create.
112 -- The requirements of LEAD_MONITOR rules are such that when the status
113 -- of the rule is 'ACTIVE', nothing on the html page is updatable.
114 -- ---------------------------------------------------------------------
115 IF (p_SELCRIT_REC.SELECTION_TYPE_CODE = 'MONITOR_SCOPE') THEN
116 -- --------------------------------------------------------------
117 -- Check the database for the "before" image. We need to compare
118 -- the before image to the after image.
119 -- --------------------------------------------------------------
120 FOR x IN (SELECT status_code FROM pv_process_rules_b
121 WHERE process_rule_id = p_SELCRIT_rec.PROCESS_RULE_ID)
122 LOOP
123 l_previous_status := x.status_code;
124 END LOOP;
125
126 IF (l_previous_status = 'ACTIVE') THEN
127 FND_MESSAGE.Set_Name('PV', 'PV_MONITOR_RULE_ACTIVE');
128 FND_MSG_PUB.Add;
129 RAISE FND_API.G_EXC_ERROR;
130 END IF;
131 END IF;
132
133 -- Debug Message
134 IF (AS_DEBUG_HIGH_ON) THEN
135
136 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling create table handler');
137 END IF;
138
139 -- Invoke table handler(PV_ENTY_SELECT_CRITERIA_PKG.Insert_Row)
140 PV_ENTY_SELECT_CRITERIA_PKG.Insert_Row(
141 px_SELECTION_CRITERIA_ID => x_SELECTION_CRITERIA_ID
142 ,p_LAST_UPDATE_DATE => SYSDATE
143 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
144 ,p_CREATION_DATE => SYSDATE
145 ,p_CREATED_BY => FND_GLOBAL.USER_ID
146 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
147 ,p_OBJECT_VERSION_NUMBER => p_SELCRIT_rec.OBJECT_VERSION_NUMBER
148 ,p_REQUEST_ID => p_SELCRIT_rec.REQUEST_ID
149 ,p_PROGRAM_APPLICATION_ID => p_SELCRIT_rec.PROGRAM_APPLICATION_ID
150 ,p_PROGRAM_ID => p_SELCRIT_rec.PROGRAM_ID
151 ,p_PROGRAM_UPDATE_DATE => p_SELCRIT_rec.PROGRAM_UPDATE_DATE
152 ,p_PROCESS_RULE_ID => p_SELCRIT_rec.PROCESS_RULE_ID
153 ,p_ATTRIBUTE_ID => p_SELCRIT_rec.ATTRIBUTE_ID
154 ,p_SELECTION_TYPE_CODE => p_SELCRIT_rec.SELECTION_TYPE_CODE
155 ,p_OPERATOR => p_SELCRIT_rec.OPERATOR
156 ,p_RANK => p_SELCRIT_rec.RANK
157 ,p_ATTRIBUTE_CATEGORY => p_SELCRIT_rec.ATTRIBUTE_CATEGORY
158 ,p_ATTRIBUTE1 => p_SELCRIT_rec.ATTRIBUTE1
159 ,p_ATTRIBUTE2 => p_SELCRIT_rec.ATTRIBUTE2
160 ,p_ATTRIBUTE3 => p_SELCRIT_rec.ATTRIBUTE3
161 ,p_ATTRIBUTE4 => p_SELCRIT_rec.ATTRIBUTE4
162 ,p_ATTRIBUTE5 => p_SELCRIT_rec.ATTRIBUTE5
163 ,p_ATTRIBUTE6 => p_SELCRIT_rec.ATTRIBUTE6
164 ,p_ATTRIBUTE7 => p_SELCRIT_rec.ATTRIBUTE7
165 ,p_ATTRIBUTE8 => p_SELCRIT_rec.ATTRIBUTE8
166 ,p_ATTRIBUTE9 => p_SELCRIT_rec.ATTRIBUTE9
167 ,p_ATTRIBUTE10 => p_SELCRIT_rec.ATTRIBUTE10
168 ,p_ATTRIBUTE11 => p_SELCRIT_rec.ATTRIBUTE11
169 ,p_ATTRIBUTE12 => p_SELCRIT_rec.ATTRIBUTE12
170 ,p_ATTRIBUTE13 => p_SELCRIT_rec.ATTRIBUTE13
171 ,p_ATTRIBUTE14 => p_SELCRIT_rec.ATTRIBUTE14
172 ,p_ATTRIBUTE15 => p_SELCRIT_rec.ATTRIBUTE15
173 ); -- Hint: Primary key should be returned.
174 -- x_SELECTION_CRITERIA_ID := px_SELECTION_CRITERIA_ID;
175
176 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
177 RAISE FND_API.G_EXC_ERROR;
178 END IF;
179
180 --
181 -- End of API body
182 --
183
184 -- Standard check for p_commit
185 IF FND_API.to_Boolean( p_commit )
186 THEN
187 COMMIT WORK;
188 END IF;
189
190
191 -- Debug Message
192 IF (AS_DEBUG_HIGH_ON) THEN
193
194 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
195 END IF;
196
197
198 -- Standard call to get message count and if count is 1, get message info.
199 FND_MSG_PUB.Count_And_Get
200 ( p_count => x_msg_count,
201 p_data => x_msg_data
202 );
203
204 EXCEPTION
205 WHEN FND_API.G_EXC_ERROR THEN
206 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
207 P_API_NAME => L_API_NAME
208 ,P_PKG_NAME => G_PKG_NAME
209 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
210 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
211 ,X_MSG_COUNT => X_MSG_COUNT
212 ,X_MSG_DATA => X_MSG_DATA
213 ,X_RETURN_STATUS => X_RETURN_STATUS);
214
215 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
216 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
217 P_API_NAME => L_API_NAME
218 ,P_PKG_NAME => G_PKG_NAME
219 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
220 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
221 ,X_MSG_COUNT => X_MSG_COUNT
222 ,X_MSG_DATA => X_MSG_DATA
223 ,X_RETURN_STATUS => X_RETURN_STATUS);
224
225 WHEN OTHERS THEN
226 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
227 P_API_NAME => L_API_NAME
228 ,P_PKG_NAME => G_PKG_NAME
229 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
230 ,P_SQLCODE => SQLCODE
231 ,P_SQLERRM => SQLERRM
232 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
233 ,X_MSG_COUNT => X_MSG_COUNT
234 ,X_MSG_DATA => X_MSG_DATA
235 ,X_RETURN_STATUS => X_RETURN_STATUS);
236 End Create_selcrit;
237
238
239 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
240 PROCEDURE Update_selcrit(
241 P_Api_Version_Number IN NUMBER,
242 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
243 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
244 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
245 P_Identity_Resource_Id IN NUMBER,
246 P_SELCRIT_Rec IN PV_RULE_RECTYPE_PUB.SELCRIT_Rec_Type,
247 X_Return_Status OUT NOCOPY VARCHAR2,
248 X_Msg_Count OUT NOCOPY NUMBER,
249 X_Msg_Data OUT NOCOPY VARCHAR2
250 )
251
252 IS
253
254 Cursor C_Get_selcrit(pc_SELECTION_CRITERIA_ID Number) IS
255 Select object_version_number
256 From PV_ENTY_SELECT_CRITERIA
257 where selection_criteria_id = pc_selection_criteria_id
258 For Update NOWAIT;
259
260 l_api_name CONSTANT VARCHAR2(30) := 'Update_selcrit';
261 l_api_version_number CONSTANT NUMBER := 2.0;
262 l_previous_status VARCHAR2(30);
263 -- Local Variables
264 l_ref_SELCRIT_rec PV_RULE_RECTYPE_PUB.SELCRIT_Rec_Type;
265 l_tar_SELCRIT_rec PV_RULE_RECTYPE_PUB.SELCRIT_Rec_Type := P_SELCRIT_Rec;
266 l_rowid ROWID;
267 BEGIN
268 -- Standard Start of API savepoint
269 SAVEPOINT UPDATE_SELCRIT_PVT;
270
271 -- Standard call to check for call compatibility.
272 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
273 p_api_version_number,
274 l_api_name,
275 G_PKG_NAME)
276 THEN
277 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
278 END IF;
279
280
281 -- Initialize message list if p_init_msg_list is set to TRUE.
282 IF FND_API.to_Boolean( p_init_msg_list )
283 THEN
284 FND_MSG_PUB.initialize;
285 END IF;
286
287
288 -- Debug Message
289 IF (AS_DEBUG_HIGH_ON) THEN
290
291 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
292 END IF;
293
294
295 -- Initialize API return status to SUCCESS
296 x_return_status := FND_API.G_RET_STS_SUCCESS;
297
298 --
299 -- Api body
300 --
301
302 -- Debug Message
303 IF (AS_DEBUG_HIGH_ON) THEN
304
305 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Open Cursor to Select');
306 END IF;
307
308 Open C_Get_selcrit( l_tar_SELCRIT_rec.SELECTION_CRITERIA_ID);
309 Fetch C_Get_selcrit into
310 l_ref_SELCRIT_rec.object_version_number;
311
312 If ( C_Get_selcrit%NOTFOUND) Then
313 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
314 THEN
315 FND_MESSAGE.Set_Name('PV', 'API_MISSING_UPDATE_TARGET');
316 FND_MESSAGE.Set_Token ('INFO', 'selcrit', FALSE);
317 FND_MSG_PUB.Add;
318 END IF;
319 Close C_Get_selcrit;
320 raise FND_API.G_EXC_ERROR;
321 END IF;
322 -- Debug Message
323 IF (AS_DEBUG_HIGH_ON) THEN
324
325 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: - Close Cursor');
326 END IF;
327 Close C_Get_selcrit;
328
329 If (l_tar_SELCRIT_rec.object_version_number is NULL or
330 l_tar_SELCRIT_rec.object_version_number = FND_API.G_MISS_NUM ) Then
331 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
332 THEN
333 FND_MESSAGE.Set_Name('PV', 'API_MISSING_ID');
334 FND_MESSAGE.Set_Token('COLUMN', 'object_version_number', FALSE);
335 FND_MSG_PUB.ADD;
336 END IF;
337 raise FND_API.G_EXC_ERROR;
338 End if;
339 -- Check Whether record has been changed by someone else
340 If (l_tar_SELCRIT_rec.object_version_number <> l_ref_SELCRIT_rec.object_version_number) Then
341 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
342 THEN
343 FND_MESSAGE.Set_Name('PV', 'API_RECORD_CHANGED');
344 FND_MESSAGE.Set_Token('INFO', 'selcrit', FALSE);
345 FND_MSG_PUB.ADD;
346 END IF;
347 raise FND_API.G_EXC_ERROR;
348 End if;
349
350 -- Debug message
351 IF (AS_DEBUG_HIGH_ON) THEN
352
353 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,'Private API: Validate_selcrit');
354 END IF;
355
356 -- Invoke validation procedures
357 Validate_selcrit(
358 p_init_msg_list => FND_API.G_FALSE,
359 p_validation_level => p_validation_level,
360 p_validation_mode => AS_UTILITY_PVT.G_UPDATE,
361 P_SELCRIT_Rec => P_SELCRIT_Rec,
362 x_return_status => x_return_status,
363 x_msg_count => x_msg_count,
364 x_msg_data => x_msg_data);
365
366 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369
370 -- Hint: Add corresponding Master-Detail business logic here if necessary.
371
372 -- ---------------------------------------------------------------------
373 -- Status check for LEAD_MONITOR rules. This is checked only in the case
374 -- of an update.
375 -- The requirements of LEAD_MONITOR rules are such that when the status
376 -- of the rule is 'ACTIVE', nothing on the html page is updatable.
377 -- ---------------------------------------------------------------------
378 IF (p_SELCRIT_REC.SELECTION_TYPE_CODE = 'MONITOR_SCOPE') THEN
379 -- --------------------------------------------------------------
380 -- Check the database for the "before" image. We need to compare
381 -- the before image to the after image.
382 -- --------------------------------------------------------------
383 FOR x IN (SELECT status_code FROM pv_process_rules_b
384 WHERE process_rule_id = p_SELCRIT_rec.PROCESS_RULE_ID)
385 LOOP
386 l_previous_status := x.status_code;
387 END LOOP;
388
389 IF (l_previous_status = 'ACTIVE') THEN
390 FND_MESSAGE.Set_Name('PV', 'PV_MONITOR_RULE_ACTIVE');
391 FND_MSG_PUB.Add;
392 RAISE FND_API.G_EXC_ERROR;
393 END IF;
394 END IF;
395
396
397
398 -- Debug Message
399 IF (AS_DEBUG_HIGH_ON) THEN
400
401 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
402 END IF;
403
404 -- Invoke table handler(PV_ENTY_SELECT_CRITERIA_PKG.Update_Row)
405 PV_ENTY_SELECT_CRITERIA_PKG.Update_Row(
406 p_SELECTION_CRITERIA_ID => p_SELCRIT_rec.SELECTION_CRITERIA_ID
407 ,p_LAST_UPDATE_DATE => SYSDATE
408 ,p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
409 ,p_CREATION_DATE => FND_API.G_MISS_DATE
410 ,p_CREATED_BY => FND_API.G_MISS_NUM
411 ,p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
412 ,p_OBJECT_VERSION_NUMBER => p_SELCRIT_rec.OBJECT_VERSION_NUMBER
413 ,p_REQUEST_ID => p_SELCRIT_rec.REQUEST_ID
414 ,p_PROGRAM_APPLICATION_ID => p_SELCRIT_rec.PROGRAM_APPLICATION_ID
415 ,p_PROGRAM_ID => p_SELCRIT_rec.PROGRAM_ID
416 ,p_PROGRAM_UPDATE_DATE => p_SELCRIT_rec.PROGRAM_UPDATE_DATE
417 ,p_PROCESS_RULE_ID => p_SELCRIT_rec.PROCESS_RULE_ID
418 ,p_ATTRIBUTE_ID => p_SELCRIT_rec.ATTRIBUTE_ID
419 ,p_SELECTION_TYPE_CODE => p_SELCRIT_rec.SELECTION_TYPE_CODE
420 ,p_OPERATOR => p_SELCRIT_rec.OPERATOR
421 ,p_RANK => p_SELCRIT_rec.RANK
422 ,p_ATTRIBUTE_CATEGORY => p_SELCRIT_rec.ATTRIBUTE_CATEGORY
423 ,p_ATTRIBUTE1 => p_SELCRIT_rec.ATTRIBUTE1
424 ,p_ATTRIBUTE2 => p_SELCRIT_rec.ATTRIBUTE2
425 ,p_ATTRIBUTE3 => p_SELCRIT_rec.ATTRIBUTE3
426 ,p_ATTRIBUTE4 => p_SELCRIT_rec.ATTRIBUTE4
427 ,p_ATTRIBUTE5 => p_SELCRIT_rec.ATTRIBUTE5
428 ,p_ATTRIBUTE6 => p_SELCRIT_rec.ATTRIBUTE6
429 ,p_ATTRIBUTE7 => p_SELCRIT_rec.ATTRIBUTE7
430 ,p_ATTRIBUTE8 => p_SELCRIT_rec.ATTRIBUTE8
431 ,p_ATTRIBUTE9 => p_SELCRIT_rec.ATTRIBUTE9
432 ,p_ATTRIBUTE10 => p_SELCRIT_rec.ATTRIBUTE10
433 ,p_ATTRIBUTE11 => p_SELCRIT_rec.ATTRIBUTE11
434 ,p_ATTRIBUTE12 => p_SELCRIT_rec.ATTRIBUTE12
435 ,p_ATTRIBUTE13 => p_SELCRIT_rec.ATTRIBUTE13
436 ,p_ATTRIBUTE14 => p_SELCRIT_rec.ATTRIBUTE14
437 ,p_ATTRIBUTE15 => p_SELCRIT_rec.ATTRIBUTE15
438 ); --
439 -- End of API body.
440 --
441
442 -- Standard check for p_commit
443 IF FND_API.to_Boolean( p_commit )
444 THEN
445 COMMIT WORK;
446 END IF;
447
448
449 -- Debug Message
450 IF (AS_DEBUG_HIGH_ON) THEN
451
452 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
453 END IF;
454
455
456 -- Standard call to get message count and if count is 1, get message info.
457 FND_MSG_PUB.Count_And_Get
458 ( p_count => x_msg_count,
459 p_data => x_msg_data
460 );
461
462 EXCEPTION
463 WHEN FND_API.G_EXC_ERROR THEN
464 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
465 P_API_NAME => L_API_NAME
466 ,P_PKG_NAME => G_PKG_NAME
467 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
468 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
469 ,X_MSG_COUNT => X_MSG_COUNT
470 ,X_MSG_DATA => X_MSG_DATA
471 ,X_RETURN_STATUS => X_RETURN_STATUS);
472
473 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
474 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
475 P_API_NAME => L_API_NAME
476 ,P_PKG_NAME => G_PKG_NAME
477 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
478 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
479 ,X_MSG_COUNT => X_MSG_COUNT
480 ,X_MSG_DATA => X_MSG_DATA
481 ,X_RETURN_STATUS => X_RETURN_STATUS);
482
483 WHEN OTHERS THEN
484 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
485 P_API_NAME => L_API_NAME
486 ,P_PKG_NAME => G_PKG_NAME
487 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
488 ,P_SQLCODE => SQLCODE
489 ,P_SQLERRM => SQLERRM
490 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
491 ,X_MSG_COUNT => X_MSG_COUNT
492 ,X_MSG_DATA => X_MSG_DATA
493 ,X_RETURN_STATUS => X_RETURN_STATUS);
494 End Update_selcrit;
495
496
497 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
498 -- The Master delete procedure may not be needed depends on different business requirements.
499 PROCEDURE Delete_selcrit(
500 P_Api_Version_Number IN NUMBER,
501 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
502 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
503 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
504 P_Identity_Resource_Id IN NUMBER,
505 P_SELCRIT_Rec IN PV_RULE_RECTYPE_PUB.SELCRIT_Rec_Type,
506 X_Return_Status OUT NOCOPY VARCHAR2,
507 X_Msg_Count OUT NOCOPY NUMBER,
508 X_Msg_Data OUT NOCOPY VARCHAR2
509 )
510
511 IS
512 l_api_name CONSTANT VARCHAR2(30) := 'Delete_selcrit';
513 l_api_version_number CONSTANT NUMBER := 2.0;
514
515 cursor lc_value_rows(pc_criteria_id number) is
516 select attr_value_id from pv_selected_attr_values
517 where selection_criteria_id = pc_criteria_id;
518
519 l_SELATTVAL_rec PV_RULE_RECTYPE_PUB.SELATTVAL_Rec_Type;
520
521 l_attr_value_id number;
522
523 BEGIN
524 -- Standard Start of API savepoint
525 SAVEPOINT DELETE_SELCRIT_PVT;
526
527 -- Standard call to check for call compatibility.
528 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
529 p_api_version_number,
530 l_api_name,
531 G_PKG_NAME)
532 THEN
533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
534 END IF;
535
536
537 -- Initialize message list if p_init_msg_list is set to TRUE.
538 IF FND_API.to_Boolean( p_init_msg_list )
539 THEN
540 FND_MSG_PUB.initialize;
541 END IF;
542
543
544 -- Debug Message
545 IF (AS_DEBUG_HIGH_ON) THEN
546
547 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
548 END IF;
549
550
551 -- Initialize API return status to SUCCESS
552 x_return_status := FND_API.G_RET_STS_SUCCESS;
553
554 --
555 -- Api body
556 --
557
558 begin
559
560 open lc_value_rows (pc_criteria_id => p_SELCRIT_rec.SELECTION_CRITERIA_ID);
561 loop
562
563 fetch lc_value_rows into l_attr_value_id;
564 exit when lc_value_rows%notfound;
565
566 l_selattval_rec.attr_value_id := l_attr_value_id;
567
568 PV_selattval_PVT.Delete_selattval(
569 P_Api_Version_Number => 2.0,
570 P_Init_Msg_List => FND_API.G_FALSE,
571 P_Commit => p_commit,
572 P_Validation_Level => p_Validation_Level,
573 P_Identity_Resource_Id => P_Identity_Resource_Id,
574 P_SELATTVAL_Rec => l_SELATTVAL_Rec,
575 X_Return_Status => x_return_status,
576 X_Msg_Count => x_msg_count,
577 X_Msg_Data => x_msg_data);
578
579
580 -- Check return status from the above procedure call
581 IF x_return_status = FND_API.G_RET_STS_ERROR then
582 raise FND_API.G_EXC_ERROR;
583 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
584 raise FND_API.G_EXC_UNEXPECTED_ERROR;
585 END IF;
586
587 end loop;
588 close lc_value_rows;
589
590 exception
591 when others then
592 close lc_value_rows;
593 raise;
594 end;
595
596 -- Debug Message
597 IF (AS_DEBUG_HIGH_ON) THEN
598
599 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
600 'Private API: Calling PV_ENTY_SELECT_CRITERIA_PKG.Delete_Row');
601 END IF;
602
603 PV_ENTY_SELECT_CRITERIA_PKG.Delete_Row(
604 p_SELECTION_CRITERIA_ID => p_SELCRIT_rec.SELECTION_CRITERIA_ID);
605 --
606 -- End of API body
607 --
608
609 -- Standard check for p_commit
610 IF FND_API.to_Boolean( p_commit )
611 THEN
612 COMMIT WORK;
613 END IF;
614
615
616 -- Debug Message
617 IF (AS_DEBUG_HIGH_ON) THEN
618
619 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
620 END IF;
621
622
623 -- Standard call to get message count and if count is 1, get message info.
624 FND_MSG_PUB.Count_And_Get
625 ( p_count => x_msg_count,
626 p_data => x_msg_data
627 );
628
629 EXCEPTION
630 WHEN FND_API.G_EXC_ERROR THEN
631 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
632 P_API_NAME => L_API_NAME
633 ,P_PKG_NAME => G_PKG_NAME
634 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
635 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
636 ,X_MSG_COUNT => X_MSG_COUNT
637 ,X_MSG_DATA => X_MSG_DATA
638 ,X_RETURN_STATUS => X_RETURN_STATUS);
639
640 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
641 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
642 P_API_NAME => L_API_NAME
643 ,P_PKG_NAME => G_PKG_NAME
644 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
645 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
646 ,X_MSG_COUNT => X_MSG_COUNT
647 ,X_MSG_DATA => X_MSG_DATA
648 ,X_RETURN_STATUS => X_RETURN_STATUS);
649
650 WHEN OTHERS THEN
651 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
652 P_API_NAME => L_API_NAME
653 ,P_PKG_NAME => G_PKG_NAME
654 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
655 ,P_SQLCODE => SQLCODE
656 ,P_SQLERRM => SQLERRM
657 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
658 ,X_MSG_COUNT => X_MSG_COUNT
659 ,X_MSG_DATA => X_MSG_DATA
660 ,X_RETURN_STATUS => X_RETURN_STATUS);
661 End Delete_selcrit;
662
663
664 -- Item-level validation procedures
665 PROCEDURE Validate_SELECTION_CRITERIA_ID (
666 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
667 P_Validation_mode IN VARCHAR2,
668 P_SELECTION_CRITERIA_ID IN NUMBER,
669 X_Return_Status OUT NOCOPY VARCHAR2,
670 X_Msg_Count OUT NOCOPY NUMBER,
671 X_Msg_Data OUT NOCOPY VARCHAR2
672 )
673 IS
674 CURSOR C_selection_criteria_id_exists (pc_selection_criteria_id NUMBER) IS
675 SELECT 'X'
676 FROM pv_enty_select_criteria
677 WHERE selection_criteria_id = pc_selection_criteria_id;
678
679 l_val VARCHAR2(1);
680 BEGIN
681
682 -- Initialize message list if p_init_msg_list is set to TRUE.
683 IF FND_API.to_Boolean( p_init_msg_list )
684 THEN
685 FND_MSG_PUB.initialize;
686 END IF;
687
688
689 -- Initialize API return status to SUCCESS
690 x_return_status := FND_API.G_RET_STS_SUCCESS;
691
692 IF(p_validation_mode = AS_UTILITY_PVT.G_CREATE)
693 THEN
694 IF (p_selection_criteria_id IS NOT NULL) AND
695 (p_selection_criteria_id <> FND_API.G_MISS_NUM)
696 THEN
697 AS_UTILITY_PVT.Set_Message(
698 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
699 p_msg_name => 'API_INVALID_ID',
700 p_token1 => 'selection_criteria_id',
701 p_token1_value => p_selection_criteria_id);
702
703 x_return_status := FND_API.G_RET_STS_ERROR;
704 END IF;
705
706 ELSIF(p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
707 THEN
708 -- validate NOT NULL column
709 IF (p_selection_criteria_id IS NULL) OR
710 (p_selection_criteria_id = FND_API.G_MISS_NUM)
711 THEN
712 AS_UTILITY_PVT.Set_Message(
713 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
714 p_msg_name => 'API_MISSING_LEAD_ID');
715
716 x_return_status := FND_API.G_RET_STS_ERROR;
717 ELSE
718 OPEN C_selection_criteria_id_exists (p_selection_criteria_id);
719 FETCH C_selection_criteria_id_exists into l_val;
720
721 IF C_selection_criteria_id_exists%NOTFOUND
722 THEN
723 AS_UTILITY_PVT.Set_Message(
724 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
725 p_msg_name => 'API_INVALID_ID',
726 p_token1 => 'selection_criteria_id',
727 p_token1_value => p_selection_criteria_id );
728
729 x_return_status := FND_API.G_RET_STS_ERROR;
730 END IF;
731
732 CLOSE C_selection_criteria_id_exists;
733 END IF;
734 END IF;
735
736
737 -- Standard call to get message count and if count is 1, get message info.
738 FND_MSG_PUB.Count_And_Get
739 ( p_count => x_msg_count,
740 p_data => x_msg_data
741 );
742
743 END Validate_SELECTION_CRITERIA_ID;
744
745
746 PROCEDURE Validate_selcrit(
747 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
748 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
749 P_Validation_mode IN VARCHAR2,
750 P_SELCRIT_Rec IN PV_RULE_RECTYPE_PUB.SELCRIT_Rec_Type,
751 X_Return_Status OUT NOCOPY VARCHAR2,
752 X_Msg_Count OUT NOCOPY NUMBER,
753 X_Msg_Data OUT NOCOPY VARCHAR2
754 )
755 IS
756
757 -- ---------------------------------------------------------------------------
758 -- Chandra added ('INPUT_FILTER', 'MONITOR_SCOPE')
759 -- ---------------------------------------------------------------------------
760 cursor lc_chk_input_filter_dups (pc_rule_id number, pc_attribute_id number) is
761 select rule.process_rule_name, attr.name
762 from
763 pv_process_rules_vl rule, pv_attributes_vl attr, pv_enty_select_criteria crit
764 where
765 crit.process_rule_id = pc_rule_id
766 and crit.attribute_id = pc_attribute_id
767 and crit.selection_type_code IN ('INPUT_FILTER', 'MONITOR_SCOPE')
768 and crit.process_rule_id = rule.process_rule_id
769 and crit.attribute_id = attr.attribute_id;
770
771 cursor lc_tie_break_operator (pc_lookup_code varchar2) is
772 select lookup_code
773 from pv_lookups where lookup_type = 'PV_TIE_BREAKING_OPERATOR'
774 and lookup_code = pc_lookup_code;
775
776 l_rule_name varchar2(300);
777 l_operator varchar2(300);
778 l_attribute_name varchar2(300);
779 l_api_name CONSTANT VARCHAR2(30) := 'Validate_selcrit';
780 l_rank number;
781 l_attribute_id number;
782
783 BEGIN
784
785 -- Debug Message
786 IF (AS_DEBUG_HIGH_ON) THEN
787
788 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'start');
789 END IF;
790
791
792 -- Initialize API return status to SUCCESS
793 x_return_status := FND_API.G_RET_STS_SUCCESS;
794
795 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_ITEM) THEN
796
797 Validate_SELECTION_CRITERIA_ID(
798 p_init_msg_list => FND_API.G_FALSE,
799 p_validation_mode => p_validation_mode,
800 p_SELECTION_CRITERIA_ID => P_SELCRIT_Rec.SELECTION_CRITERIA_ID,
801 x_return_status => x_return_status,
802 x_msg_count => x_msg_count,
803 x_msg_data => x_msg_data);
804
805 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
806 raise FND_API.G_EXC_ERROR;
807 END IF;
808
809 pv_common_checks_pvt.Validate_OBJECT_VERSION_NUMBER(
810 p_init_msg_list => FND_API.G_FALSE,
811 p_validation_mode => p_validation_mode,
812 p_OBJECT_VERSION_NUMBER => P_SELCRIT_Rec.OBJECT_VERSION_NUMBER,
813 x_return_status => x_return_status,
814 x_msg_count => x_msg_count,
815 x_msg_data => x_msg_data);
816
817 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
818 raise FND_API.G_EXC_ERROR;
819 END IF;
820
821 pv_common_checks_pvt.Validate_PROCESS_RULE_ID(
822 p_init_msg_list => FND_API.G_FALSE,
823 p_validation_mode => p_validation_mode,
824 p_PROCESS_RULE_ID => P_SELCRIT_Rec.PROCESS_RULE_ID,
825 x_return_status => x_return_status,
826 x_msg_count => x_msg_count,
827 x_msg_data => x_msg_data);
828
829 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
830 raise FND_API.G_EXC_ERROR;
831 END IF;
832
833 pv_common_checks_pvt.Validate_Lookup(
834 p_init_msg_list => FND_API.G_FALSE,
835 p_validation_mode => p_validation_mode,
836 p_TABLE_NAME => 'PV_ENTY_SELECT_CRITERIA',
837 p_COLUMN_NAME => 'SELECTION_TYPE_CODE',
838 p_lookup_type => 'PV_SELECTION_TYPE_CODE',
839 p_lookup_code => P_SELCRIT_Rec.SELECTION_TYPE_CODE,
840 x_return_status => x_return_status,
841 x_msg_count => x_msg_count,
842 x_msg_data => x_msg_data);
843
844 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
845 raise FND_API.G_EXC_ERROR;
846 END IF;
847
848
849 --ryellapu - Additional checks to Rank and Attribute id to avoid any duplicate values
850
851 if p_SELCRIT_REC.SELECTION_TYPE_CODE = 'TIE_BREAKING' then
852 begin
853
854 if p_SELCRIT_REC.rank is null then
855 FND_MESSAGE.Set_Name('PV', 'PV_NULLCHECK_TIEBR_RANK');
856 FND_MSG_PUB.Add;
857 x_return_status := FND_API.G_RET_STS_ERROR;
858 raise FND_API.G_EXC_ERROR;
859 end if;
860
861 select rank into l_rank from pv_enty_select_criteria
862 where process_rule_id = p_SELCRIT_REC.process_rule_id and
863 rank = p_SELCRIT_REC.rank;
864
865 if l_rank is not null then
866 FND_MESSAGE.Set_Name('PV', 'PV_DUPLICATE_TIEBR_RANK');
867 FND_MSG_PUB.Add;
868 x_return_status := FND_API.G_RET_STS_ERROR;
869 raise FND_API.G_EXC_ERROR;
870 end if;
871 exception
872 when no_data_found then
873 null;
874 when others then
875 x_return_status := FND_API.G_RET_STS_ERROR;
876 raise FND_API.G_EXC_ERROR;
877 end;
878 end if;
879
880 if p_SELCRIT_REC.SELECTION_TYPE_CODE = 'TIE_BREAKING' then
881 begin
882 select attribute_id into l_attribute_id from pv_enty_select_criteria
883 where process_rule_id = p_SELCRIT_REC.process_rule_id and
884 attribute_id = p_SELCRIT_REC.attribute_id;
885
886 if (l_attribute_id is not null and p_validation_mode = AS_UTILITY_PVT.G_CREATE) then
887 FND_MESSAGE.Set_Name('PV', 'PV_DUPLICATE_TIEBR_ATTRIBUTE');
888 FND_MSG_PUB.Add;
889 x_return_status := FND_API.G_RET_STS_ERROR;
890 raise FND_API.G_EXC_ERROR;
891 end if;
892 exception
893 when no_data_found then
894 null;
895 end;
896 end if;
897
898
899 if p_SELCRIT_REC.SELECTION_TYPE_CODE = 'TIE_BREAKING' then
900
901 open lc_tie_break_operator( pc_lookup_code => p_SELCRIT_REC.OPERATOR);
902 fetch lc_tie_break_operator into l_operator;
903 close lc_tie_break_operator;
904
905 if l_operator is NULL then
906
907 pv_common_checks_pvt.Set_Message(
908 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
909 p_msg_name => 'API_INVALID_CODE',
910 p_token1 => 'TABLE_NAME',
911 p_token1_value => 'PV_ENTY_SELECT_CRITERIA',
912 p_token2 => 'COLUMN_NAME',
913 p_token2_value => 'OPERATOR',
914 p_token3 => 'LOOKUP_TYPE',
915 p_token3_value => 'PV_TIE_BREAKING_OPERATOR',
916 p_token4 => 'LOOKUP_CODE',
917 p_token4_value => p_SELCRIT_REC.OPERATOR);
918
919 x_return_status := FND_API.G_RET_STS_ERROR;
920 raise FND_API.G_EXC_ERROR;
921
922 end if;
923
924 else
925
926 pv_common_checks_pvt.Validate_OPERATOR(
927 p_init_msg_list => FND_API.G_FALSE,
928 p_validation_mode => p_validation_mode,
929 p_TABLE_NAME => 'PV_ENTY_SELECT_CRITERIA',
930 p_COLUMN_NAME => 'OPERATOR',
931 p_attribute_id => P_SELCRIT_REC.ATTRIBUTE_ID,
932 p_OPERATOR_CODE => P_SELCRIT_Rec.OPERATOR,
933 x_return_status => x_return_status,
934 x_msg_count => x_msg_count,
935 x_msg_data => x_msg_data);
936
937 end if;
938
939 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
940 raise FND_API.G_EXC_ERROR;
941 END IF;
942
943 END IF;
944
945 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_RECORD) THEN
946 -- Hint: Inter-field level validation can be added here
947 -- invoke record level validation procedures
948 NULL;
949 END IF;
950
951 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_RECORD) THEN
952
953 -- ---------------------------------------------------------------------------
954 -- Chandra added ('INPUT_FILTER', 'MONITOR_SCOPE')
955 -- ---------------------------------------------------------------------------
956 if p_validation_mode = AS_UTILITY_PVT.G_CREATE and
957 p_SELCRIT_REC.SELECTION_TYPE_CODE IN ('INPUT_FILTER', 'MONITOR_SCOPE') then
958
959 open lc_chk_input_filter_dups (pc_rule_id => p_SELCRIT_REC.process_rule_id,
960 pc_attribute_id => p_SELCRIT_REC.attribute_id);
961
962 fetch lc_chk_input_filter_dups into l_rule_name, l_attribute_name;
963 close lc_chk_input_filter_dups;
964
965 if l_attribute_name is not null then
966
967 pv_common_checks_pvt.Set_Message(
968 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
969 p_msg_name => 'PV_DUPLICATE_INPUT_FILTER',
970 p_token1 => 'RULE_NAME',
971 p_token1_value => l_rule_name,
972 p_token2 => 'ATTRIBUTE_NAME',
973 p_token2_value => l_attribute_name);
974
975 x_return_status := FND_API.G_RET_STS_ERROR;
976
977 end if;
978
979 end if;
980
981 END IF;
982
983 IF (p_validation_level >= AS_UTILITY_PUB.G_VALID_LEVEL_INTER_ENTITY) THEN
984 -- invoke inter-entity level validation procedures
985 NULL;
986 END IF;
987
988
989 -- Debug Message
990 IF (AS_DEBUG_HIGH_ON) THEN
991
992 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: ' || l_api_name || 'end');
993 END IF;
994
995 END Validate_selcrit;
996
997 End PV_SELCRIT_PVT;