[Home] [Help]
PACKAGE BODY: APPS.CSC_CHECK_RATINGS_PVT
Source
1 PACKAGE BODY CSC_CHECK_RATINGS_PVT as
2 /* $Header: cscvprab.pls 120.1 2006/05/31 11:46:09 adhanara noship $ */
3 -- Start of Comments
4 -- Package name : CSC_CHECK_RATINGS_PVT
5 -- Purpose :
6 -- History :
7 -- 18 Nov 02 jamose made changes for the NOCOPY and FND_API.G_MISS*
8 -- NOTE :
9 -- End of Comments
10
11
12 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSC_CHECK_RATINGS_PVT';
16 PROCEDURE Convert_Columns_to_Rec(
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cscvurab.pls';
14
15
17 p_CHECK_RATING_ID IN NUMBER DEFAULT NULL,
18 p_CHECK_ID IN NUMBER,
19 p_CHECK_RATING_GRADE IN VARCHAR2,
20 p_RATING_COLOR_ID IN NUMBER,
21 p_RATING_CODE IN VARCHAR2,
22 p_COLOR_CODE IN VARCHAR2,
23 p_RANGE_LOW_VALUE IN VARCHAR2,
24 p_RANGE_HIGH_VALUE IN VARCHAR2,
25 p_LAST_UPDATE_DATE IN DATE,
26 p_LAST_UPDATED_BY IN NUMBER,
27 p_CREATION_DATE IN DATE,
28 p_CREATED_BY IN NUMBER,
29 p_LAST_UPDATE_LOGIN IN NUMBER,
30 p_SEEDED_FLAG IN VARCHAR2,
31 X_Chk_rating_rec OUT NOCOPY CHK_RATING_Rec_Type
32 )
33 IS
34 BEGIN
35
36 X_Chk_rating_rec.CHECK_ID := P_CHECK_ID;
37 X_Chk_rating_rec.CHECK_RATING_ID := P_CHECK_RATING_ID;
38 X_Chk_rating_rec.CHECK_ID := P_CHECK_ID;
39 X_Chk_rating_rec.CHECK_RATING_GRADE := P_CHECK_RATING_GRADE;
40 X_Chk_rating_rec.RATING_COLOR_ID := P_RATING_COLOR_ID;
41 X_Chk_rating_rec.RATING_CODE := P_RATING_CODE;
42 X_Chk_rating_rec.COLOR_CODE := P_COLOR_CODE;
43 X_Chk_rating_rec.RANGE_LOW_VALUE := P_RANGE_LOW_VALUE;
44 X_Chk_rating_rec.RANGE_HIGH_VALUE := P_RANGE_HIGH_VALUE;
45 X_Chk_rating_rec.CREATED_BY := P_CREATED_BY;
46 X_Chk_rating_rec.CREATION_DATE := P_CREATION_DATE;
47 X_Chk_rating_rec.LAST_UPDATED_BY := P_LAST_UPDATED_BY;
48 X_Chk_rating_rec.LAST_UPDATE_DATE := P_LAST_UPDATE_DATE;
49 X_Chk_rating_rec.LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN;
50 X_Chk_rating_rec.SEEDED_FLAG := P_SEEDED_FLAG;
51
52 END Convert_Columns_to_Rec;
53
54
55
56 PROCEDURE Create_check_ratings(
57 P_Api_Version_Number IN NUMBER,
58 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
59 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
60 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
61 px_CHECK_RATING_ID IN OUT NOCOPY NUMBER,
62 p_CHECK_ID IN NUMBER ,
63 p_CHECK_RATING_GRADE IN VARCHAR2,
64 p_RATING_COLOR_ID IN NUMBER DEFAULT NULL,
65 p_RATING_CODE IN VARCHAR2,
66 p_COLOR_CODE IN VARCHAR2,
67 p_RANGE_LOW_VALUE IN VARCHAR2,
68 p_RANGE_HIGH_VALUE IN VARCHAR2,
69 p_LAST_UPDATE_DATE IN DATE ,
70 p_LAST_UPDATED_BY IN NUMBER,
71 p_CREATION_DATE IN DATE ,
72 p_CREATED_BY IN NUMBER,
73 p_LAST_UPDATE_LOGIN IN NUMBER,
74 p_SEEDED_FLAG IN VARCHAR2,
75 X_Return_Status OUT NOCOPY VARCHAR2,
76 X_Msg_Count OUT NOCOPY NUMBER,
77 X_Msg_Data OUT NOCOPY VARCHAR2
78 )
79 IS
80 l_chk_rating_rec CHK_RATING_Rec_Type := G_MISS_CHK_RATING_Rec;
81 BEGIN
82
83 Convert_Columns_to_Rec(
84 --p_CHECK_RATING_ID => p_CHECK_RATING_ID,
85 p_CHECK_ID => p_CHECK_ID,
86 p_CHECK_RATING_GRADE => p_CHECK_RATING_GRADE,
87 p_RATING_COLOR_ID => p_RATING_COLOR_ID,
88 p_RATING_CODE => p_RATING_CODE,
89 p_COLOR_CODE => p_COLOR_CODE,
90 p_RANGE_LOW_VALUE => p_RANGE_LOW_VALUE,
91 p_RANGE_HIGH_VALUE => p_RANGE_HIGH_VALUE,
92 P_CREATED_BY => P_CREATED_BY,
93 P_CREATION_DATE => P_CREATION_DATE,
94 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
95 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
96 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
97 P_SEEDED_FLAG => P_SEEDED_FLAG,
98 X_CHk_Rating_Rec => l_chk_rating_rec
99 );
100
101 Create_check_ratings(
102 P_Api_Version_Number => 1.0,
103 P_Init_Msg_List => CSC_CORE_UTILS_PVT.G_FALSE,
104 P_Commit => CSC_CORE_UTILS_PVT.G_FALSE,
105 P_Validation_Level => CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
106 px_Check_Rating_id => px_check_rating_id,
107 P_CHK_RATING_Rec => l_CHK_RATING_Rec ,
108 X_Return_Status => x_return_status,
109 X_Msg_Count => x_msg_count,
110 X_Msg_Data => x_msg_data
111 );
112
113
114 END Create_check_ratings;
115
116 PROCEDURE Create_check_ratings(
117 P_Api_Version_Number IN NUMBER,
118 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
119 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
120 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
121 px_Check_Rating_ID IN OUT NOCOPY NUMBER,
122 P_CHK_RATING_Rec IN CHK_RATING_Rec_Type := G_MISS_CHK_RATING_Rec,
123 X_Return_Status OUT NOCOPY VARCHAR2,
124 X_Msg_Count OUT NOCOPY NUMBER,
125 X_Msg_Data OUT NOCOPY VARCHAR2
126 )
127 IS
128 l_api_name CONSTANT VARCHAR2(30) := 'Create_check_ratings';
129 l_api_version_number CONSTANT NUMBER := 1.0;
130 l_return_status_full VARCHAR2(1);
131 l_chk_rating_rec CHK_RATING_Rec_Type;
132 BEGIN
133 -- Standard Start of API savepoint
134 SAVEPOINT CREATE_CHECK_RATINGS_PVT;
135
136 -- Standard call to check for call compatibility.
140 G_PKG_NAME)
137 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
138 p_api_version_number,
139 l_api_name,
141 THEN
142 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
143 END IF;
144
145
146 -- Initialize message list if p_init_msg_list is set to TRUE.
147 IF FND_API.to_Boolean( p_init_msg_list )
148 THEN
149 FND_MSG_PUB.initialize;
150 END IF;
151
152 -- Initialize API return status to SUCCESS
153 x_return_status := FND_API.G_RET_STS_SUCCESS;
154
155 --
156 -- API body
157 --
158
159 IF ( P_validation_level >= CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL)
160 THEN
161
162 -- Invoke validation procedures
163 Validate_check_ratings(
164 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
165 p_validation_level => p_validation_level,
166 p_validation_mode => CSC_CORE_UTILS_PVT.G_CREATE,
167 p_CHK_RATING_rec => p_chk_rating_Rec,
168 x_return_status => x_return_status,
169 x_msg_count => x_msg_count,
170 x_msg_data => x_msg_data);
171 END IF;
172
173 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
174 RAISE FND_API.G_EXC_ERROR;
175 END IF;
176
177 -- Invoke table handler(CSC_PROF_CHECK_RATINGS_PKG.Insert_Row)
178 CSC_PROF_CHECK_RATINGS_PKG.Insert_Row(
179 px_CHECK_RATING_ID => px_CHECK_RATING_ID,
180 p_CHECK_ID => P_CHK_RATING_rec.CHECK_ID,
181 p_CHECK_RATING_GRADE => P_CHK_RATING_rec.CHECK_RATING_GRADE,
182 --p_RATING_COLOR_ID => P_CHK_RATING_rec.RATING_COLOR_ID,
183 p_RATING_CODE => P_CHK_RATING_rec.RATING_CODE,
184 p_COLOR_CODE => P_CHK_RATING_rec.COLOR_CODE,
185 p_RANGE_LOW_VALUE => P_CHK_RATING_rec.RANGE_LOW_VALUE,
186 p_RANGE_HIGH_VALUE => P_CHK_RATING_rec.RANGE_HIGH_VALUE,
187 p_LAST_UPDATE_DATE => SYSDATE,
188 p_LAST_UPDATED_BY => P_CHK_RATING_rec.LAST_UPDATED_BY,
189 p_CREATION_DATE => SYSDATE,
190 p_CREATED_BY => P_CHK_RATING_rec.CREATED_BY,
191 p_LAST_UPDATE_LOGIN => P_CHK_RATING_rec.LAST_UPDATE_LOGIN ,
192 p_SEEDED_FLAG => P_CHK_RATING_rec.SEEDED_FLAG);
193
194
195 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
196 RAISE FND_API.G_EXC_ERROR;
197 END IF;
198
199 --
200 -- End of API body
201 --
202
203 -- Standard check for p_commit
204 IF FND_API.to_Boolean( p_commit )
205 THEN
206 COMMIT WORK;
207 END IF;
208
209
210 -- Standard call to get message count and if count is 1, get message info.
211 FND_MSG_PUB.Count_And_Get
212 ( p_count => x_msg_count,
213 p_data => x_msg_data
214 );
215
216 EXCEPTION
217 WHEN FND_API.G_EXC_ERROR THEN
218 ROLLBACK TO Create_check_ratings_PVT;
219 x_return_status := FND_API.G_RET_STS_ERROR;
220 FND_MSG_PUB.Count_And_Get
221 ( p_count => x_msg_count,
222 p_data => x_msg_data
223 );
224 APP_EXCEPTION.RAISE_EXCEPTION;
225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
226 ROLLBACK TO Create_check_ratings_PVT;
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228 FND_MSG_PUB.Count_And_Get
229 ( p_count => x_msg_count,
230 p_data => x_msg_data
231 );
232 APP_EXCEPTION.RAISE_EXCEPTION;
233 WHEN OTHERS THEN
234 ROLLBACK TO Create_check_ratings_PVT;
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236 FND_MSG_PUB.Build_Exc_Msg(G_PKG_NAME, l_api_name);
237 FND_MSG_PUB.Count_And_Get
238 ( p_count => x_msg_count,
239 p_data => x_msg_data
240 );
241 APP_EXCEPTION.RAISE_EXCEPTION;
242 End Create_check_ratings;
243
244
245 PROCEDURE Update_check_ratings(
246 P_Api_Version_Number IN NUMBER,
247 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
248 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
249 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
250 p_CHECK_RATING_ID IN NUMBER,
251 p_CHECK_ID IN NUMBER,
252 p_CHECK_RATING_GRADE IN VARCHAR2,
253 p_RATING_COLOR_ID IN NUMBER DEFAULT NULL,
254 p_RATING_CODE IN VARCHAR2,
255 p_COLOR_CODE IN VARCHAR2,
256 p_RANGE_LOW_VALUE IN VARCHAR2,
257 p_RANGE_HIGH_VALUE IN VARCHAR2,
258 p_LAST_UPDATE_DATE IN DATE ,
259 p_LAST_UPDATED_BY IN NUMBER,
260 p_CREATION_DATE IN DATE,
261 p_CREATED_BY IN NUMBER,
262 p_LAST_UPDATE_LOGIN IN NUMBER,
263 p_SEEDED_FLAG IN VARCHAR2,
264 X_Return_Status OUT NOCOPY VARCHAR2,
265 X_Msg_Count OUT NOCOPY NUMBER,
266 X_Msg_Data OUT NOCOPY VARCHAR2
267 )
268 IS
269 l_chk_rating_rec CHK_RATING_Rec_Type := G_MISS_CHK_RATING_Rec;
270 BEGIN
271
272 Convert_Columns_to_Rec(
273 p_CHECK_RATING_ID => p_CHECK_RATING_ID,
274 p_CHECK_ID => p_CHECK_ID,
275 p_CHECK_RATING_GRADE => p_CHECK_RATING_GRADE,
276 p_RATING_COLOR_ID => p_RATING_COLOR_ID,
277 p_RATING_CODE => p_RATING_CODE,
281 P_CREATED_BY => P_CREATED_BY,
278 p_COLOR_CODE => p_COLOR_CODE,
279 p_RANGE_LOW_VALUE => p_RANGE_LOW_VALUE,
280 p_RANGE_HIGH_VALUE => p_RANGE_HIGH_VALUE,
282 P_CREATION_DATE => P_CREATION_DATE,
283 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
284 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
285 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
286 P_SEEDED_FLAG => P_SEEDED_FLAG,
287 X_CHk_Rating_Rec => l_chk_rating_rec
288 );
289
290 Update_check_ratings(
291 P_Api_Version_Number => 1.0,
292 P_Init_Msg_List => CSC_CORE_UTILS_PVT.G_FALSE,
293 P_Commit => CSC_CORE_UTILS_PVT.G_FALSE,
294 P_Validation_Level => CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
295 P_CHK_RATING_Rec => l_CHK_RATING_Rec ,
296 X_Return_Status => x_return_status,
297 X_Msg_Count => x_msg_count,
298 X_Msg_Data => x_msg_data
299 );
300
301 END Update_check_ratings;
302
303 PROCEDURE Update_check_ratings(
304 P_Api_Version_Number IN NUMBER,
305 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
306 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
307 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
308 P_CHK_RATING_Rec IN CHK_RATING_Rec_Type := G_MISS_CHK_RATING_Rec,
309 X_Return_Status OUT NOCOPY VARCHAR2,
310 X_Msg_Count OUT NOCOPY NUMBER,
311 X_Msg_Data OUT NOCOPY VARCHAR2
312 )
313
314 IS
315
316 Cursor C_Get_check_ratings(c_CHECK_RATING_ID Number) IS
317 Select rowid,
318 CHECK_RATING_ID,
319 CHECK_ID,
320 CHECK_RATING_GRADE,
321 RATING_CODE,
322 COLOR_CODE,
323 RANGE_LOW_VALUE,
324 RANGE_HIGH_VALUE,
325 LAST_UPDATE_DATE,
326 LAST_UPDATED_BY,
327 CREATION_DATE,
328 CREATED_BY,
329 LAST_UPDATE_LOGIN,
330 SEEDED_FLAG
331 From CSC_PROF_CHECK_RATINGS
332 Where check_id = c_CHECK_RATING_ID
333 For Update NOWAIT;
334 l_api_name CONSTANT VARCHAR2(30) := 'Update_check_ratings';
335 l_api_version_number CONSTANT NUMBER := 1.0;
336 -- Local Variables
337 l_rowid ROWID;
338 l_chk_rating_rec CHK_RATING_Rec_Type;
339 l_old_chk_rating_rec CSC_PROF_CHECK_RATINGS%ROWTYPE;
340 BEGIN
341 -- Standard Start of API savepoint
342 SAVEPOINT UPDATE_CHECK_RATINGS_PVT;
343
344 -- Standard call to check for call compatibility.
345 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
346 p_api_version_number,
347 l_api_name,
348 G_PKG_NAME)
349 THEN
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
351 END IF;
352
353
354 -- Initialize message list if p_init_msg_list is set to TRUE.
355 IF FND_API.to_Boolean( p_init_msg_list )
356 THEN
357 FND_MSG_PUB.initialize;
358 END IF;
359
360
361 -- Initialize API return status to SUCCESS
362 x_return_status := FND_API.G_RET_STS_SUCCESS;
363
364 --
365 -- Api body
366 --
367
368 Open C_Get_check_ratings( p_chk_rating_rec.CHECK_ID);
369 Fetch C_Get_check_ratings into
370 l_rowid,
371 l_old_CHK_RATING_rec.CHECK_RATING_ID,
372 l_old_CHK_RATING_rec.CHECK_ID,
373 l_old_CHK_RATING_rec.CHECK_RATING_GRADE,
374 l_old_CHK_RATING_rec.RATING_CODE,
375 l_old_CHK_RATING_rec.COLOR_CODE,
376 l_old_CHK_RATING_rec.RANGE_LOW_VALUE,
377 l_old_CHK_RATING_rec.RANGE_HIGH_VALUE,
378 l_old_CHK_RATING_rec.LAST_UPDATE_DATE,
379 l_old_CHK_RATING_rec.LAST_UPDATED_BY,
380 l_old_CHK_RATING_rec.CREATION_DATE,
381 l_old_CHK_RATING_rec.CREATED_BY,
382 l_old_CHK_RATING_rec.LAST_UPDATE_LOGIN,
383 l_old_CHK_RATING_rec.SEEDED_FLAG;
384 If ( C_Get_check_ratings%NOTFOUND) Then
385 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
386 THEN
387 CSC_CORE_UTILS_PVT.Record_Is_Locked_msg(p_Api_Name=> l_api_name);
388 END IF;
389 raise FND_API.G_EXC_ERROR;
390 END IF;
391
392 IF ( P_validation_level >= CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL)
393 THEN
394
395 -- Invoke validation procedures
396 Validate_check_ratings(
397 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
398 p_validation_level => p_validation_level,
399 p_validation_mode => CSC_CORE_UTILS_PVT.G_UPDATE,
400 P_CHK_RATING_Rec => P_CHK_RATING_REC ,
401 x_return_status => x_return_status,
402 x_msg_count => x_msg_count,
403 x_msg_data => x_msg_data);
404 END IF;
405
406 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
407 RAISE FND_API.G_EXC_ERROR;
408 END IF;
409
410 -- Invoke table handler(CSC_PROF_CHECK_RATINGS_PKG.Update_Row)
411 CSC_PROF_CHECK_RATINGS_PKG.Update_Row(
412 p_CHECK_RATING_ID => csc_core_utils_pvt.get_g_miss_char(P_CHK_RATING_rec.CHECK_RATING_ID,l_old_CHK_RATING_rec.CHECK_RATING_ID),
416 p_COLOR_CODE => csc_core_utils_pvt.get_g_miss_char(P_CHK_RATING_rec.COLOR_CODE,l_old_CHK_RATING_rec.COLOR_CODE),
413 p_CHECK_ID => csc_core_utils_pvt.get_g_miss_char(p_CHK_RATING_REC.CHECK_ID,l_old_CHK_RATING_rec.CHECK_ID),
414 p_CHECK_RATING_GRADE => csc_core_utils_pvt.get_g_miss_char(P_CHK_RATING_rec.CHECK_RATING_GRADE,l_old_CHK_RATING_rec.CHECK_RATING_GRADE),
415 p_RATING_CODE => csc_core_utils_pvt.get_g_miss_char(P_CHK_RATING_rec.RATING_CODE,l_old_CHK_RATING_rec.RATING_CODE),
417 p_RANGE_LOW_VALUE => csc_core_utils_pvt.get_g_miss_char(P_CHK_RATING_rec.RANGE_LOW_VALUE,l_old_CHK_RATING_rec.RANGE_LOW_VALUE),
418 p_RANGE_HIGH_VALUE => csc_core_utils_pvt.get_g_miss_char(P_CHK_RATING_rec.RANGE_HIGH_VALUE,l_old_CHK_RATING_rec.RANGE_HIGH_VALUE),
419 p_LAST_UPDATE_DATE => SYSDATE,
420 p_LAST_UPDATED_BY => csc_core_utils_pvt.get_g_miss_num(P_CHK_RATING_rec.LAST_UPDATED_BY,l_old_CHK_RATING_rec.LAST_UPDATED_BY),
421 p_LAST_UPDATE_LOGIN => csc_core_utils_pvt.get_g_miss_num(P_CHK_RATING_rec.LAST_UPDATE_LOGIN,l_old_CHK_RATING_rec.LAST_UPDATE_LOGIN),
422 p_SEEDED_FLAG => csc_core_utils_pvt.get_g_miss_char(P_CHK_RATING_rec.SEEDED_FLAG,l_old_CHK_RATING_rec.SEEDED_FLAG) );
423
424
425 --
426 -- End of API body.
427 --
428
429 -- Standard check for p_commit
430 IF FND_API.to_Boolean( p_commit )
431 THEN
432 COMMIT WORK;
433 END IF;
434
435
436 -- Standard call to get message count and if count is 1, get message info.
437 FND_MSG_PUB.Count_And_Get
438 ( p_count => x_msg_count,
439 p_data => x_msg_data
440 );
441
442 EXCEPTION
443 WHEN FND_API.G_EXC_ERROR THEN
444 ROLLBACK TO Update_check_ratings_PVT;
445 x_return_status := FND_API.G_RET_STS_ERROR;
446 FND_MSG_PUB.Count_And_Get
447 ( p_count => x_msg_count,
448 p_data => x_msg_data
449 );
450 APP_EXCEPTION.RAISE_EXCEPTION;
451 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
452 ROLLBACK TO Update_check_ratings_PVT;
453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
454 FND_MSG_PUB.Count_And_Get
455 ( p_count => x_msg_count,
456 p_data => x_msg_data
457 );
458 APP_EXCEPTION.RAISE_EXCEPTION;
459 WHEN OTHERS THEN
460 ROLLBACK TO Update_check_ratings_PVT;
461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
462 FND_MSG_PUB.Build_Exc_Msg(G_PKG_NAME, l_api_name);
463 FND_MSG_PUB.Count_And_Get
464 ( p_count => x_msg_count,
465 p_data => x_msg_data
466 );
467 APP_EXCEPTION.RAISE_EXCEPTION;
468 End Update_check_ratings;
469
470
471 PROCEDURE Delete_check_ratings(
472 P_Api_Version_Number IN NUMBER,
473 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
474 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
475 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
476 P_CHECK_RATING_ID IN NUMBER,
477 X_Return_Status OUT NOCOPY VARCHAR2,
478 X_Msg_Count OUT NOCOPY NUMBER,
479 X_Msg_Data OUT NOCOPY VARCHAR2
480 )
481
482 IS
483 l_api_name CONSTANT VARCHAR2(30) := 'Delete_check_ratings';
484 l_api_version_number CONSTANT NUMBER := 1.0;
485 BEGIN
486 -- Standard Start of API savepoint
487 SAVEPOINT DELETE_CHECK_RATINGS_PVT;
488
489 -- Standard call to check for call compatibility.
490 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
491 p_api_version_number,
492 l_api_name,
493 G_PKG_NAME)
494 THEN
495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496 END IF;
497
498
499 -- Initialize message list if p_init_msg_list is set to TRUE.
500 IF FND_API.to_Boolean( p_init_msg_list )
501 THEN
502 FND_MSG_PUB.initialize;
503 END IF;
504
505 -- Initialize API return status to SUCCESS
506 x_return_status := FND_API.G_RET_STS_SUCCESS;
507
508 --
509 -- Api body
510 --
511
512 -- Invoke table handler(CSC_PROF_CHECK_RATINGS_PKG.Delete_Row)
513 CSC_PROF_CHECK_RATINGS_PKG.Delete_Row(
514 p_CHECK_RATING_ID => p_CHECK_RATING_ID);
515 --
516 -- End of API body
517 --
518
519 -- Standard check for p_commit
520 IF FND_API.to_Boolean( p_commit )
521 THEN
522 COMMIT WORK;
523 END IF;
524
525
526
527 -- Standard call to get message count and if count is 1, get message info.
528 FND_MSG_PUB.Count_And_Get
529 ( p_count => x_msg_count,
530 p_data => x_msg_data
531 );
532
533 EXCEPTION
534 WHEN FND_API.G_EXC_ERROR THEN
535 ROLLBACK TO Delete_check_ratings_PVT;
536 x_return_status := FND_API.G_RET_STS_ERROR;
537 FND_MSG_PUB.Count_And_Get
538 ( p_count => x_msg_count,
539 p_data => x_msg_data
540 );
541 APP_EXCEPTION.RAISE_EXCEPTION;
542 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
543 ROLLBACK TO Delete_check_ratings_PVT;
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
545 FND_MSG_PUB.Count_And_Get
546 ( p_count => x_msg_count,
547 p_data => x_msg_data
548 );
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 APP_EXCEPTION.RAISE_EXCEPTION;
550 WHEN OTHERS THEN
551 ROLLBACK TO Delete_check_ratings_PVT;
553 FND_MSG_PUB.Build_Exc_Msg(G_PKG_NAME, l_api_name);
554 FND_MSG_PUB.Count_And_Get
555 ( p_count => x_msg_count,
556 p_data => x_msg_data
557 );
558 APP_EXCEPTION.RAISE_EXCEPTION;
559 End Delete_check_ratings;
560
561 -- Item-level validation procedures
562 PROCEDURE Validate_CHECK_ID (
563 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
564 P_Validation_mode IN VARCHAR2,
565 P_CHECK_ID IN NUMBER,
566 X_Return_Status OUT NOCOPY VARCHAR2,
567 X_Msg_Count OUT NOCOPY NUMBER,
568 X_Msg_Data OUT NOCOPY VARCHAR2
569 )
570 IS
571 Cursor C1 is
572 Select check_id
573 From csc_prof_checks_b
574 Where check_id = p_Check_Id;
575 l_dummy number;
576 p_Api_Name VARCHAR2(100) := 'Validate_Check_Id';
577 BEGIN
578
579 -- Initialize message list if p_init_msg_list is set to TRUE.
580 IF FND_API.to_Boolean( p_init_msg_list )
581 THEN
582 FND_MSG_PUB.initialize;
583 END IF;
584
585 -- Initialize API return status to SUCCESS
586 x_return_status := FND_API.G_RET_STS_SUCCESS;
587
588
589 IF(p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE)
590 THEN
591 -- Hint: Validate data
592 -- IF p_CHECK_ID is not NULL and p_CHECK_ID <> G_MISS_NUM
593 -- verify if data is valid
594 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
595 IF p_CHECK_ID is not NULL and p_CHECK_ID <> CSC_CORE_UTILS_PVT.G_MISS_NUM
596 THEN
597 Open C1;
598 Fetch C1 into l_dummy;
599 IF C1%NOTFOUND THEN
600 x_return_status := FND_API.G_RET_STS_ERROR;
601 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
602 p_api_name=> p_api_name,
603 p_argument_value => p_check_id,
604 p_argument=>'P_CHECK_ID' );
605 END IF;
606 Close C1;
607 ELSE
608 x_return_status := FND_API.G_RET_STS_ERROR;
609 CSC_CORE_UTILS_PVT.mandatory_arg_error(
610 p_api_name => p_api_name,
611 p_argument => 'P_CHECK_ID',
612 p_argument_Value => p_check_id );
613 END IF;
614 ELSIF(p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE)
615 THEN
616 -- Hint: Validate data
617 -- IF p_CHECK_ID <> FND_API.G_MISS_NUM
618 -- verify if data is valid
619 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
620 IF p_CHECK_ID <> CSC_CORE_UTILS_PVT.G_MISS_NUM
621 THEN
622 Open C1;
623 Fetch C1 into l_dummy;
624 IF C1%NOTFOUND THEN
625 x_return_status := FND_API.G_RET_STS_ERROR;
626 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
627 p_api_name=> p_api_name,
628 p_argument_value => p_check_id,
629 p_argument=>'P_CHECK_ID' );
630 END IF;
631 Close C1;
632 ELSIF p_CHECK_ID IS NULL
633 THEN
634 x_return_status := FND_API.G_RET_STS_ERROR;
635 CSC_CORE_UTILS_PVT.mandatory_arg_error(
636 p_api_name => p_api_name,
637 p_argument => 'P_CHECK_ID',
638 p_argument_Value => p_check_id );
639 END IF;
640 END IF;
641
642 -- Standard call to get message count and if count is 1, get message info.
643 FND_MSG_PUB.Count_And_Get
644 ( p_count => x_msg_count,
645 p_data => x_msg_data
646 );
647
648 END Validate_CHECK_ID;
649
650
651 PROCEDURE Validate_CHECK_RATING_GRADE (
652 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
653 P_Validation_mode IN VARCHAR2,
654 P_CHECK_RATING_GRADE IN VARCHAR2,
655 X_Return_Status OUT NOCOPY VARCHAR2,
656 X_Msg_Count OUT NOCOPY NUMBER,
657 X_Msg_Data OUT NOCOPY VARCHAR2
658 )
659 IS
660 p_Api_Name VARCHAR2(100) := 'VALIDATE_CHECK_RATINGS_GRADE';
661 BEGIN
662 -- Initialize message list if p_init_msg_list is set to TRUE.
663 IF FND_API.to_Boolean( p_init_msg_list )
664 THEN
665 FND_MSG_PUB.initialize;
666 END IF;
667 -- Initialize API return status to SUCCESS
668 x_return_status := FND_API.G_RET_STS_SUCCESS;
669 -- validate NOT NULL column
670 IF(p_CHECK_RATING_GRADE is NULL)
671 THEN
672 x_return_status := FND_API.G_RET_STS_ERROR;
673 END IF;
674 IF(p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE)
675 THEN
676 -- Hint: Validate data
677 -- IF p_CHECK_RATING_GRADE is not NULL and p_CHECK_RATING_GRADE <> G_MISS_CHAR
678 -- verify if data is valid
679 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
680 NULL;
681 ELSIF(p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE)
682 THEN
683 -- Hint: Validate data
684 -- IF p_CHECK_RATING_GRADE <> G_MISS_CHAR
685 -- verify if data is valid
686 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
687 NULL;
688 END IF;
689 -- Standard call to get message count and if count is 1, get message info.
693 );
690 FND_MSG_PUB.Count_And_Get
691 ( p_count => x_msg_count,
692 p_data => x_msg_data
694 END Validate_CHECK_RATING_GRADE;
695
696
697
698 PROCEDURE Validate_RATING_CODE (
699 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
700 P_Validation_mode IN VARCHAR2,
701 P_RATING_CODE IN VARCHAR2,
702 X_Return_Status OUT NOCOPY VARCHAR2,
703 X_Msg_Count OUT NOCOPY NUMBER,
704 X_Msg_Data OUT NOCOPY VARCHAR2
705 )
706 IS
707 p_Api_Name VARCHAR2(100) := 'VALIDATE_RATING_CODE';
708 BEGIN
709
710 -- Initialize message list if p_init_msg_list is set to TRUE.
711 IF FND_API.to_Boolean( p_init_msg_list )
712 THEN
713 FND_MSG_PUB.initialize;
714 END IF;
715
716 -- Initialize API return status to SUCCESS
717 x_return_status := FND_API.G_RET_STS_SUCCESS;
718
719 -- validate NOT NULL column
720 IF(p_RATING_CODE is NULL)
721 THEN
722 x_return_status := FND_API.G_RET_STS_ERROR;
723 CSC_CORE_UTILS_PVT.mandatory_arg_error(
724 p_api_name => p_api_name,
725 p_argument => 'P_RATING_CODE',
726 p_argument_Value => p_rating_code );
727 END IF;
728
729 IF(p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE)
730 THEN
731 -- validate NOT NULL column
732 IF (p_RATING_CODE is NOT NULL) AND (p_RATING_CODE <> CSC_CORE_UTILS_PVT.G_MISS_CHAR)
733 THEN
734 IF CSC_CORE_UTILS_PVT.csc_lookup_code_not_exists(
735 p_effective_date => trunc(sysdate),
736 p_lookup_type => 'CSC_PROF_RATINGS',
737 p_lookup_Code => p_rating_code ) <> FND_API.G_RET_STS_SUCCESS
738 THEN
739
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
742 p_api_name=> p_api_name,
743 p_argument_value => p_rating_code,
744 p_argument=>'P_RATING_CODE' );
745 END IF;
746 ELSE
747 x_return_status := FND_API.G_RET_STS_ERROR;
748 CSC_CORE_UTILS_PVT.mandatory_arg_error(
749 p_api_name => p_api_name,
750 p_argument => 'P_RATING_CODE',
751 p_argument_Value => p_rating_code );
752 END IF;
753 ELSIF(p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE)
754 THEN
755
756 IF (p_RATING_CODE is NOT NULL) AND (p_RATING_CODE <> CSC_CORE_UTILS_PVT.G_MISS_CHAR)
757 THEN
758 IF CSC_CORE_UTILS_PVT.csc_lookup_code_not_exists(
759 p_effective_date => trunc(sysdate),
760 p_lookup_type => 'CSC_PROF_RATINGS',
761 p_lookup_Code => p_rating_code ) <> FND_API.G_RET_STS_SUCCESS
762 THEN
763
764 x_return_status := FND_API.G_RET_STS_ERROR;
765 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
766 p_api_name=> p_api_name,
767 p_argument_value => p_rating_code,
768 p_argument=>'P_RATING_CODE' );
769 END IF;
770 END IF;
771 END IF;
772
773 -- Standard call to get message count and if count is 1, get message info.
774 FND_MSG_PUB.Count_And_Get
775 ( p_count => x_msg_count,
776 p_data => x_msg_data
777 );
778 END Validate_RATING_CODE;
779
780 PROCEDURE Validate_check_ratings(
781 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
782 P_Validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
783 P_Validation_mode IN VARCHAR2,
784 p_CHK_RATING_Rec IN CHK_RATING_Rec_Type,
785 X_Return_Status OUT NOCOPY VARCHAR2,
786 X_Msg_Count OUT NOCOPY NUMBER,
787 X_Msg_Data OUT NOCOPY VARCHAR2
788 )
789
790 IS
791 l_api_name CONSTANT VARCHAR2(30) := 'Validate_check_ratings';
792
793 BEGIN
794
795 -- Initialize API return status to SUCCESS
796 x_return_status := FND_API.G_RET_STS_SUCCESS;
797 /*
798 Validate_CHECK_RATING_ID(
799 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
800 p_validation_mode => p_validation_mode,
801 p_CHECK_RATING_ID => l_CHECK_RATING_ID,
802 x_return_status => x_return_status,
803 x_msg_count => x_msg_count,
804 x_msg_data => x_msg_data);
805 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
806 raise FND_API.G_EXC_ERROR;
807 END IF;
808 */
809 Validate_CHECK_ID(
810 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
811 p_validation_mode => p_validation_mode,
812 p_CHECK_ID => P_CHK_RATING_REC.CHECK_ID,
813 x_return_status => x_return_status,
814 x_msg_count => x_msg_count,
815 x_msg_data => x_msg_data);
816 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
817 raise FND_API.G_EXC_ERROR;
818 END IF;
819
820 /*
821 Validate_CHECK_RATING_GRADE(
822 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
823 p_validation_mode => p_validation_mode,
824 p_CHECK_RATING_GRADE => p_CHK_RATING_REC.CHECK_RATING_GRADE,
825 x_return_status => x_return_status,
826 x_msg_count => x_msg_count,
827 x_msg_data => x_msg_data);
828 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
829 raise FND_API.G_EXC_ERROR;
830 END IF;
831 */
832
833
834
835 Validate_RATING_CODE(
836 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
837 p_validation_mode => p_validation_mode,
838 p_RATING_CODE => p_CHK_RATING_REC.RATING_CODE,
839 x_return_status => x_return_status,
840 x_msg_count => x_msg_count,
841 x_msg_data => x_msg_data);
842 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
843 raise FND_API.G_EXC_ERROR;
844 END IF;
845
846 CSC_CORE_UTILS_PVT.Validate_Seeded_Flag(
847 p_api_name =>'CSC_CHECK_RATINGS_PVT.VALIDATE_SEEDED_FLAG',
848 p_seeded_flag => p_CHK_RATING_rec.seeded_flag,
849 x_return_status => x_return_status );
850
851 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
852 RAISE FND_API.G_EXC_ERROR;
853 END IF;
854
855
856
857 END Validate_check_ratings;
858
859 End CSC_CHECK_RATINGS_PVT;