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