[Home] [Help]
PACKAGE BODY: APPS.CSC_PROFILE_GROUPS_PVT
Source
1 PACKAGE BODY CSC_PROFILE_GROUPS_PVT as
2 /* $Header: cscvpgrb.pls 115.13 2002/12/03 18:30:11 jamose ship $ */
3 -- Start of Comments
4 -- Package name : CSC_PROFILE_GROUPS_PVT
5 -- Purpose :
6 -- History :
7 -- 29 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_PROFILE_GROUPS_PVT';
13 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cscvpgrb.pls';
14
15 PROCEDURE Convert_Columns_to_Rec(
16 P_GROUP_ID IN NUMBER ,
17 P_CREATED_BY IN NUMBER ,
18 P_CREATION_DATE IN DATE ,
19 P_LAST_UPDATED_BY IN NUMBER ,
20 P_LAST_UPDATE_DATE IN DATE ,
21 P_LAST_UPDATE_LOGIN IN NUMBER ,
22 P_GROUP_NAME IN VARCHAR2,
23 P_GROUP_NAME_CODE IN VARCHAR2,
24 P_DESCRIPTION IN VARCHAR2,
25 P_PARTY_TYPE IN VARCHAR2 ,
26 P_START_DATE_ACTIVE IN DATE,
27 P_END_DATE_ACTIVE IN DATE,
28 P_USE_IN_CUSTOMER_DASHBOARD IN VARCHAR2,
29 P_SEEDED_FLAG IN VARCHAR2,
30 P_OBJECT_VERSION_NUMBER IN NUMBER DEFAULT NULL,
31 P_APPLICATION_ID IN NUMBER,
32 X_PROF_GROUP_Rec OUT NOCOPY PROF_GROUP_Rec_Type
33 )
34 IS
35 BEGIN
36
37 X_Prof_group_rec.GROUP_ID := P_GROUP_ID;
38 X_Prof_group_rec.CREATED_BY := P_CREATED_BY;
39 X_Prof_group_rec.CREATION_DATE := P_CREATION_DATE;
40 X_Prof_group_rec.LAST_UPDATED_BY := P_LAST_UPDATED_BY;
41 X_Prof_group_rec.LAST_UPDATE_DATE := P_LAST_UPDATE_DATE;
42 X_Prof_group_rec.LAST_UPDATE_LOGIN := P_LAST_UPDATE_LOGIN;
43 X_Prof_group_rec.GROUP_NAME := P_GROUP_NAME;
44 X_Prof_group_rec.GROUP_NAME_CODE := P_GROUP_NAME_CODE;
45 X_Prof_group_rec.DESCRIPTION := P_DESCRIPTION;
46 X_Prof_group_rec.PARTY_TYPE := P_PARTY_TYPE;
47 X_Prof_group_rec.START_DATE_ACTIVE := P_START_DATE_ACTIVE;
48 X_Prof_group_rec.END_DATE_ACTIVE := P_END_DATE_ACTIVE;
49 X_Prof_group_rec.USE_IN_CUSTOMER_DASHBOARD := P_USE_IN_CUSTOMER_DASHBOARD;
50 X_Prof_group_rec.SEEDED_FLAG := P_SEEDED_FLAG;
51 X_Prof_group_rec.OBJECT_VERSION_NUMBER := P_OBJECT_VERSION_NUMBER;
52 X_Prof_group_rec.APPLICATION_ID := P_APPLICATION_ID;
53
54
55 END;
56
57
58 PROCEDURE Create_profile_groups(
59 PX_Group_Id IN OUT NOCOPY NUMBER,
60 P_Api_Version_Number IN NUMBER,
61 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
62 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
63 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
64 P_GROUP_ID IN NUMBER DEFAULT NULL,
65 P_CREATED_BY IN NUMBER,
66 P_CREATION_DATE IN DATE,
67 P_LAST_UPDATED_BY IN NUMBER,
68 P_LAST_UPDATE_DATE IN DATE,
69 P_LAST_UPDATE_LOGIN IN NUMBER,
70 P_GROUP_NAME IN VARCHAR2,
71 P_GROUP_NAME_CODE IN VARCHAR2,
72 P_DESCRIPTION IN VARCHAR2,
73 P_PARTY_TYPE IN VARCHAR2,
74 P_START_DATE_ACTIVE IN DATE,
75 P_END_DATE_ACTIVE IN DATE,
76 P_USE_IN_CUSTOMER_DASHBOARD IN VARCHAR2,
77 P_SEEDED_FLAG IN VARCHAR2,
78 X_Object_Version_Number OUT NOCOPY NUMBER,
79 P_APPLICATION_ID IN NUMBER,
80 X_Return_Status OUT NOCOPY VARCHAR2,
81 X_Msg_Count OUT NOCOPY NUMBER,
82 X_Msg_Data OUT NOCOPY VARCHAR2
83 )
84 IS
85 l_prof_group_rec PROF_GROUP_REC_TYPE := G_MISS_PROF_GROUP_REC;
86 BEGIN
87
88 Convert_Columns_to_Rec(
89 P_GROUP_ID => P_GROUP_ID,
90 P_CREATED_BY => P_CREATED_BY,
91 P_CREATION_DATE => P_CREATION_DATE,
92 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
93 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
94 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
95 P_GROUP_NAME => P_GROUP_NAME,
96 P_GROUP_NAME_CODE => P_GROUP_NAME_CODE,
97 P_DESCRIPTION => P_DESCRIPTION,
98 P_PARTY_TYPE => P_PARTY_TYPE,
99 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
100 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
101 P_USE_IN_CUSTOMER_DASHBOARD => P_USE_IN_CUSTOMER_DASHBOARD,
102 P_SEEDED_FLAG => P_SEEDED_FLAG,
103 P_APPLICATION_ID => P_APPLICATION_ID,
104 X_PROF_GROUP_Rec => l_prof_group_rec
105 );
106 Create_profile_groups(
107 PX_Group_ID => PX_Group_Id,
108 P_Api_Version_Number => 1.0,
109 P_Init_Msg_List => CSC_CORE_UTILS_PVT.G_FALSE,
110 P_Commit => CSC_CORE_UTILS_PVT.G_FALSE,
111 P_Validation_Level => CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
112 P_PROF_GROUP_Rec => l_prof_group_rec,
113 X_Object_Version_Number => x_Object_Version_Number,
114 X_Return_Status => x_return_status,
115 X_Msg_Count => x_msg_count,
116 X_Msg_Data => x_msg_data);
117
118
119 END;
120
121
122 PROCEDURE Create_profile_groups(
123 PX_Group_Id IN OUT NOCOPY NUMBER,
124 P_Api_Version_Number IN NUMBER,
125 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
126 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
127 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
128 P_PROF_GROUP_Rec IN PROF_GROUP_Rec_Type := G_MISS_PROF_GROUP_REC,
129 X_Object_Version_Number OUT NOCOPY NUMBER,
130 X_Return_Status OUT NOCOPY VARCHAR2,
131 X_Msg_Count OUT NOCOPY NUMBER,
132 X_Msg_Data OUT NOCOPY VARCHAR2
133 )
134
135 IS
136 l_api_name CONSTANT VARCHAR2(30) := 'Create_profile_groups';
137 l_api_version_number CONSTANT NUMBER := 1.0;
138 l_return_status_full VARCHAR2(1);
139 BEGIN
140 -- Standard Start of API savepoint
141 SAVEPOINT CREATE_PROFILE_GROUPS_PVT;
142
143 -- Standard call to check for call compatibility.
144 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
145 p_api_version_number,
146 l_api_name,
147 G_PKG_NAME)
148 THEN
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 END IF;
151
152
153 -- Initialize message list if p_init_msg_list is set to TRUE.
154 IF FND_API.to_Boolean( p_init_msg_list )
155 THEN
156 FND_MSG_PUB.initialize;
157 END IF;
158
159
160 -- Initialize API return status to SUCCESS
161 x_return_status := FND_API.G_RET_STS_SUCCESS;
162
163 --
164 -- API body
165 --
166
167 IF ( P_validation_level >= CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL)
168 THEN
169
170 -- Invoke validation procedures
171 Validate_profile_groups(
172 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
173 p_validation_level => p_validation_level,
174 p_validation_mode => CSC_CORE_UTILS_PVT.G_CREATE,
175 P_PROF_GROUP_Rec => P_PROF_GROUP_Rec,
176 x_return_status => x_return_status,
177 x_msg_count => x_msg_count,
178 x_msg_data => x_msg_data);
179 END IF;
180
181 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
182 RAISE FND_API.G_EXC_ERROR;
183 END IF;
184
185
186 -- Invoke table handler(CSC_PROF_GROUPS_PKG.Insert_Row)
187 CSC_PROF_GROUPS_PKG.Insert_Row(
188 px_GROUP_ID => px_GROUP_ID,
189 p_CREATED_BY => FND_GLOBAL.USER_ID,
190 p_CREATION_DATE => SYSDATE,
191 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
192 p_LAST_UPDATE_DATE => SYSDATE,
193 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
194 p_GROUP_NAME => p_PROF_GROUP_rec.GROUP_NAME,
195 p_GROUP_NAME_CODE => p_PROF_GROUP_rec.GROUP_NAME_CODE,
196 p_DESCRIPTION => p_PROF_GROUP_rec.DESCRIPTION,
197 p_START_DATE_ACTIVE => p_PROF_GROUP_rec.START_DATE_ACTIVE,
198 p_END_DATE_ACTIVE => p_PROF_GROUP_rec.END_DATE_ACTIVE,
199 p_USE_IN_CUSTOMER_DASHBOARD => p_PROF_GROUP_rec.USE_IN_CUSTOMER_DASHBOARD,
200 p_PARTY_TYPE => p_PROF_GROUP_rec.PARTY_TYPE,
201 p_SEEDED_FLAG => p_PROF_GROUP_rec.SEEDED_FLAG,
202 x_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
203 p_APPLICATION_ID => p_PROF_GROUP_rec.APPLICATION_ID );
204
205
206 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
207 RAISE FND_API.G_EXC_ERROR;
208 END IF;
209
210 --
211 -- End of API body
212 --
213
214 -- Standard check for p_commit
215 IF FND_API.to_Boolean( p_commit )
216 THEN
217 COMMIT WORK;
218 END IF;
219
220
221
222 -- Standard call to get message count and if count is 1, get message info.
223 FND_MSG_PUB.Count_And_Get
224 ( p_count => x_msg_count,
225 p_data => x_msg_data
226 );
227
228 EXCEPTION
229 WHEN FND_API.G_EXC_ERROR THEN
230 ROLLBACK TO Create_profile_groups_PVT;
231 x_return_status := FND_API.G_RET_STS_ERROR;
232 FND_MSG_PUB.Count_And_Get
233 ( p_count => x_msg_count,
234 p_data => x_msg_data
235 );
236 APP_EXCEPTION.RAISE_EXCEPTION;
237 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
238 ROLLBACK TO Create_profile_groups_PVT;
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 FND_MSG_PUB.Count_And_Get
241 ( p_count => x_msg_count,
242 p_data => x_msg_data
243 );
244 APP_EXCEPTION.RAISE_EXCEPTION;
245 WHEN OTHERS THEN
246 ROLLBACK TO Create_profile_groups_PVT;
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
248 FND_MSG_PUB.Build_Exc_Msg(G_PKG_NAME, l_api_name);
249 FND_MSG_PUB.Count_And_Get
250 ( p_count => x_msg_count,
251 p_data => x_msg_data
252 );
253 APP_EXCEPTION.RAISE_EXCEPTION;
254 End Create_profile_groups;
255
256 PROCEDURE Update_profile_groups(
257 P_Api_Version_Number IN NUMBER,
258 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
259 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
260 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
261 P_GROUP_ID IN NUMBER,
262 P_CREATED_BY IN NUMBER,
263 P_CREATION_DATE IN DATE ,
264 P_LAST_UPDATED_BY IN NUMBER,
265 P_LAST_UPDATE_DATE IN DATE ,
266 P_LAST_UPDATE_LOGIN IN NUMBER,
267 P_GROUP_NAME IN VARCHAR2,
268 P_GROUP_NAME_CODE IN VARCHAR2,
269 P_DESCRIPTION IN VARCHAR2,
270 P_PARTY_TYPE IN VARCHAR2,
271 P_START_DATE_ACTIVE IN DATE ,
272 P_END_DATE_ACTIVE IN DATE ,
273 P_USE_IN_CUSTOMER_DASHBOARD IN VARCHAR2,
274 P_SEEDED_FLAG IN VARCHAR2,
275 PX_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
276 P_APPLICATION_ID IN NUMBER,
277 X_Return_Status OUT NOCOPY VARCHAR2,
278 X_Msg_Count OUT NOCOPY NUMBER,
279 X_Msg_Data OUT NOCOPY VARCHAR2
280 )
281 IS
282 l_prof_group_Rec PROF_GROUP_REC_TYPE;
283 BEGIN
284 Convert_Columns_to_Rec(
285 P_GROUP_ID => P_GROUP_ID,
286 P_CREATED_BY => P_CREATED_BY,
287 P_CREATION_DATE => P_CREATION_DATE,
288 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
289 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
290 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
291 P_GROUP_NAME => P_GROUP_NAME,
292 P_GROUP_NAME_CODE => P_GROUP_NAME_CODE,
293 P_DESCRIPTION => P_DESCRIPTION,
294 P_PARTY_TYPE => P_PARTY_TYPE,
295 P_START_DATE_ACTIVE => P_START_DATE_ACTIVE,
296 P_END_DATE_ACTIVE => P_END_DATE_ACTIVE,
297 P_USE_IN_CUSTOMER_DASHBOARD => P_USE_IN_CUSTOMER_DASHBOARD,
298 P_SEEDED_FLAG => P_SEEDED_FLAG,
299 P_OBJECT_VERSION_NUMBER => PX_OBJECT_VERSION_NUMBER,
300 P_APPLICATION_ID => P_APPLICATION_ID,
301 X_PROF_GROUP_Rec => l_prof_group_rec
302 );
303
304 Update_profile_groups(
305 P_Api_Version_Number => P_Api_Version_Number,
306 P_Init_Msg_List => P_Init_Msg_List,
307 P_Commit => P_Commit,
308 P_Validation_Level => P_Validation_Level,
309 P_PROF_GROUP_Rec => l_prof_group_rec,
310 PX_Object_Version_Number => px_Object_Version_Number,
311 X_Return_Status => x_return_status,
312 X_Msg_Count => x_msg_count,
313 X_Msg_Data => x_msg_data);
314 END;
315
316 PROCEDURE Update_profile_groups(
317 P_Api_Version_Number IN NUMBER,
318 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
319 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
320 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
321 P_PROF_GROUP_Rec IN PROF_GROUP_Rec_Type,
322 PX_Object_Version_Number IN OUT NOCOPY NUMBER,
323 X_Return_Status OUT NOCOPY VARCHAR2,
324 X_Msg_Count OUT NOCOPY NUMBER,
325 X_Msg_Data OUT NOCOPY VARCHAR2
326 )
327
328 IS
329
330 Cursor C_Get_profile_groups(c_GROUP_ID Number) IS
331 Select GROUP_ID,
332 CREATED_BY,
333 CREATION_DATE,
334 LAST_UPDATED_BY,
335 LAST_UPDATE_DATE,
336 LAST_UPDATE_LOGIN,
337 GROUP_NAME,
338 GROUP_NAME_CODE,
339 DESCRIPTION,
340 START_DATE_ACTIVE,
341 END_DATE_ACTIVE,
342 USE_IN_CUSTOMER_DASHBOARD,
343 SEEDED_FLAG,
344 OBJECT_VERSION_NUMBER
345
346 From CSC_PROF_GROUPS_VL
347 Where group_id = c_Group_id
348 And object_version_number = px_Object_Version_Number
349 For Update NOWAIT;
350
351 l_api_name CONSTANT VARCHAR2(30) := 'Update_profile_groups';
352 l_api_version_number CONSTANT NUMBER := 1.0;
353 -- Local Variables
354
355 l_old_PROF_GROUP_rec PROF_GROUP_Rec_Type;
356 l_rowid ROWID;
357 BEGIN
358 -- Standard Start of API savepoint
359 SAVEPOINT UPDATE_PROFILE_GROUPS_PVT;
360
361 -- Standard call to check for call compatibility.
362 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
363 p_api_version_number,
364 l_api_name,
365 G_PKG_NAME)
366 THEN
367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
368 END IF;
369
370
371 -- Initialize message list if p_init_msg_list is set to TRUE.
372 IF FND_API.to_Boolean( p_init_msg_list )
373 THEN
374 FND_MSG_PUB.initialize;
375 END IF;
376
377
378
379 -- Initialize API return status to SUCCESS
380 x_return_status := FND_API.G_RET_STS_SUCCESS;
381
382 --
383 -- Api body
384 --
385 Open C_Get_profile_groups( p_PROF_GROUP_rec.GROUP_ID);
386
387 Fetch C_Get_profile_groups into
388 l_old_PROF_GROUP_rec.GROUP_ID,
389 l_old_PROF_GROUP_rec.CREATED_BY,
390 l_old_PROF_GROUP_rec.CREATION_DATE,
391 l_old_PROF_GROUP_rec.LAST_UPDATED_BY,
392 l_old_PROF_GROUP_rec.LAST_UPDATE_DATE,
393 l_old_PROF_GROUP_rec.LAST_UPDATE_LOGIN,
394 l_old_PROF_GROUP_rec.GROUP_NAME,
395 l_old_PROF_GROUP_rec.GROUP_NAME_CODE,
396 l_old_PROF_GROUP_rec.DESCRIPTION,
397 l_old_PROF_GROUP_rec.START_DATE_ACTIVE,
398 l_old_PROF_GROUP_rec.END_DATE_ACTIVE,
399 l_old_PROF_GROUP_rec.USE_IN_CUSTOMER_DASHBOARD,
400 l_old_PROF_GROUP_rec.SEEDED_FLAG,
401 l_old_prof_GROUP_rec.OBJECT_VERSION_NUMBER;
402
403
404 If ( C_Get_profile_groups%NOTFOUND) Then
405 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
406 THEN
407 CLOSE C_Get_profile_Groups;
408 CSC_CORE_UTILS_PVT.RECORD_IS_LOCKED_MSG(p_Api_Name=>l_api_name);
409 --FND_MESSAGE.Set_Name('CSC', 'API_MISSING_UPDATE_TARGET');
410 --FND_MESSAGE.Set_Token ('INFO', 'CSC_PROF_GROUPS', FALSE);
411 --FND_MSG_PUB.Add;
412 END IF;
413 raise FND_API.G_EXC_ERROR;
414 END IF;
415 IF C_Get_Profile_Groups%ISOPEN THEN
416 Close C_Get_profile_groups;
417 END IF;
418
419
420 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
421 THEN
422
423 -- Invoke validation procedures
424 Validate_profile_groups(
425 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
426 p_validation_level => p_validation_level,
427 p_validation_mode => CSC_CORE_UTILS_PVT.G_UPDATE,
428 P_PROF_GROUP_Rec => P_PROF_GROUP_Rec,
429 x_return_status => x_return_status,
430 x_msg_count => x_msg_count,
431 x_msg_data => x_msg_data);
432 END IF;
433
434 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
435 RAISE FND_API.G_EXC_ERROR;
436 END IF;
437
438
439 -- Invoke table handler(CSC_PROF_GROUPS_PKG.Update_Row)
440 CSC_PROF_GROUPS_PKG.Update_Row(
441 p_GROUP_ID => csc_core_utils_pvt.Get_G_Miss_num(p_PROF_GROUP_rec.GROUP_ID,l_old_PROF_GROUP_rec.GROUP_ID),
442 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
443 p_LAST_UPDATE_DATE => SYSDATE,
444 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
445 p_GROUP_NAME =>csc_core_utils_pvt.Get_G_Miss_char(p_PROF_GROUP_rec.GROUP_NAME,l_old_PROF_GROUP_rec.GROUP_NAME),
446 p_GROUP_NAME_CODE =>csc_core_utils_pvt.Get_G_Miss_char( p_PROF_GROUP_rec.GROUP_NAME_CODE,l_old_PROF_GROUP_rec.GROUP_NAME_CODE),
447 p_DESCRIPTION =>csc_core_utils_pvt.Get_G_Miss_char( p_PROF_GROUP_rec.DESCRIPTION,l_old_PROF_GROUP_rec.DESCRIPTION),
448 p_START_DATE_ACTIVE => csc_core_utils_pvt.Get_G_Miss_Date(p_PROF_GROUP_rec.START_DATE_ACTIVE,l_old_PROF_GROUP_rec.START_DATE_ACTIVE),
449 p_END_DATE_ACTIVE => csc_core_utils_pvt.Get_G_Miss_Date(p_PROF_GROUP_rec.END_DATE_ACTIVE,l_old_PROF_GROUP_rec.END_DATE_ACTIVE),
450 p_USE_IN_CUSTOMER_DASHBOARD => csc_core_utils_pvt.Get_G_Miss_char(p_PROF_GROUP_rec.USE_IN_CUSTOMER_DASHBOARD,l_old_PROF_GROUP_rec.USE_IN_CUSTOMER_DASHBOARD),
451 p_PARTY_TYPE => csc_core_utils_pvt.Get_G_Miss_char(p_PROF_GROUP_rec.PARTY_TYPE,l_old_PROF_GROUP_rec.PARTY_TYPE),
452 p_SEEDED_FLAG => csc_core_utils_pvt.Get_G_Miss_char(p_PROF_GROUP_rec.SEEDED_FLAG,l_old_PROF_GROUP_rec.SEEDED_FLAG),
453 PX_OBJECT_VERSION_NUMBER => px_OBJECT_VERSION_NUMBER,
454 p_APPLICATION_ID => csc_core_utils_pvt.Get_G_Miss_num(p_PROF_GROUP_rec.APPLICATION_ID,l_old_PROF_GROUP_rec.APPLICATION_ID) );
455 --
456 -- End of API body.
457 --
458
459 -- Standard check for p_commit
460 IF FND_API.to_Boolean( p_commit )
461 THEN
462 COMMIT WORK;
463 END IF;
464
465
466
467 -- Standard call to get message count and if count is 1, get message info.
468 FND_MSG_PUB.Count_And_Get
469 ( p_count => x_msg_count,
470 p_data => x_msg_data
471 );
472
473 EXCEPTION
474 WHEN FND_API.G_EXC_ERROR THEN
475 ROLLBACK TO Update_profile_groups_PVT;
476 x_return_status := FND_API.G_RET_STS_ERROR;
477 FND_MSG_PUB.Count_And_Get
478 ( p_count => x_msg_count,
479 p_data => x_msg_data
480 );
481 APP_EXCEPTION.RAISE_EXCEPTION;
482 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
483 ROLLBACK TO Update_profile_groups_PVT;
484 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
485 FND_MSG_PUB.Count_And_Get
486 ( p_count => x_msg_count,
487 p_data => x_msg_data
488 );
489 APP_EXCEPTION.RAISE_EXCEPTION;
490 WHEN OTHERS THEN
491 ROLLBACK TO Update_profile_groups_PVT;
492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
493 FND_MSG_PUB.Build_Exc_Msg(G_PKG_NAME, l_api_name);
494 APP_EXCEPTION.RAISE_EXCEPTION;
495 End Update_profile_groups;
496
497
498 PROCEDURE Delete_profile_groups(
499 P_Api_Version_Number IN NUMBER,
500 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
501 P_Commit IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
502 p_validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
503 P_Group_ID IN NUMBER,
504 P_Object_Version_Number IN NUMBER,
505 X_Return_Status OUT NOCOPY VARCHAR2,
506 X_Msg_Count OUT NOCOPY NUMBER,
507 X_Msg_Data OUT NOCOPY VARCHAR2
508 )
509
510 IS
511 l_api_name CONSTANT VARCHAR2(30) := 'Delete_profile_groups';
512 l_api_version_number CONSTANT NUMBER := 1.0;
513 BEGIN
514 -- Standard Start of API savepoint
515 SAVEPOINT DELETE_PROFILE_GROUPS_PVT;
516
517 -- Standard call to check for call compatibility.
518 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
519 p_api_version_number,
520 l_api_name,
521 G_PKG_NAME)
522 THEN
523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
524 END IF;
525
526
527 -- Initialize message list if p_init_msg_list is set to TRUE.
528 IF FND_API.to_Boolean( p_init_msg_list )
529 THEN
530 FND_MSG_PUB.initialize;
531 END IF;
532
533
534
535 -- Initialize API return status to SUCCESS
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537
538 --
539 -- Api body
540 --
541
542 -- Invoke table handler(CSC_PROF_GROUPS_PKG.Delete_Row)
543 CSC_PROF_GROUPS_PKG.Delete_Row(
544 p_GROUP_ID => p_GROUP_ID,
545 p_OBJECT_VERSION_NUMBER => p_OBJECT_VERSION_NUMBER);
546 --
547 -- End of API body
548 --
549 -- Standard check for p_commit
550 IF FND_API.to_Boolean( p_commit )
551 THEN
552 COMMIT WORK;
553 END IF;
554
555
556 -- Standard call to get message count and if count is 1, get message info.
557 FND_MSG_PUB.Count_And_Get
558 ( p_count => x_msg_count,
559 p_data => x_msg_data
560 );
561
562 EXCEPTION
563 WHEN FND_API.G_EXC_ERROR THEN
564 ROLLBACK TO Delete_profile_groups_PVT;
565 x_return_status := FND_API.G_RET_STS_ERROR;
566 FND_MSG_PUB.Count_And_Get
567 ( p_count => x_msg_count,
568 p_data => x_msg_data
569 );
570 APP_EXCEPTION.RAISE_EXCEPTION;
571 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
572 ROLLBACK TO Delete_profile_groups_PVT;
573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
574 FND_MSG_PUB.Count_And_Get
575 ( p_count => x_msg_count,
576 p_data => x_msg_data
577 );
578 APP_EXCEPTION.RAISE_EXCEPTION;
579 WHEN OTHERS THEN
580 ROLLBACK TO Delete_profile_groups_PVT;
581 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
582 FND_MSG_PUB.build_Exc_Msg(G_PKG_NAME, l_api_name);
583 FND_MSG_PUB.Count_And_Get
584 ( p_count => x_msg_count,
585 p_data => x_msg_data
586 );
587 APP_EXCEPTION.RAISE_EXCEPTION;
588 End Delete_profile_groups;
589
590
591 PROCEDURE Validate_GROUP_NAME (
592 P_Api_Name IN VARCHAR2,
593 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
594 P_Validation_mode IN VARCHAR2,
595 P_GROUP_NAME IN VARCHAR2,
596 P_GROUP_ID IN NUMBER,
597 X_Return_Status OUT NOCOPY VARCHAR2,
598 X_Msg_Count OUT NOCOPY NUMBER,
599 X_Msg_Data OUT NOCOPY VARCHAR2
600 )
601 IS
602 Cursor C1 is
603 Select group_id
604 From csc_prof_groups_vl
605 Where group_name = p_GROUP_NAME;
606 l_dummy Number;
607 BEGIN
608
609 -- Initialize message list if p_init_msg_list is set to TRUE.
610 IF FND_API.to_Boolean( p_init_msg_list )
611 THEN
612 FND_MSG_PUB.initialize;
613 END IF;
614
615 -- Initialize API return status to SUCCESS
616 x_return_status := FND_API.G_RET_STS_SUCCESS;
617
618 -- validate NOT NULL column
619 IF(p_GROUP_NAME is NULL)
620 THEN
621 x_return_status := FND_API.G_RET_STS_ERROR;
622 CSC_CORE_UTILS_PVT.mandatory_arg_error(
623 p_api_name => p_api_name,
624 p_argument => 'p_GROUP_NAME',
625 p_argument_value => p_GROUP_NAME);
626 END IF;
627
628 IF(p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE)
629 THEN
630 -- Hint: Validate data
631 -- IF p_GROUP_NAME is not NULL and p_GROUP_NAME <> G_MISS_CHAR
632 -- verify if data is valid
633 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
634 IF p_GROUP_NAME is not NULL and p_GROUP_NAME <> CSC_CORE_UTILS_PVT.G_MISS_CHAR
635 THEN
636 Open C1;
637 Fetch C1 into l_dummy;
638 IF C1%FOUND THEN
639 x_return_status := FND_API.G_RET_STS_ERROR;
640 CSC_CORE_UTILS_PVT.Add_Duplicate_Value_Msg(
641 p_api_name => p_api_name,
642 p_argument => 'p_GROUP_NAME' ,
643 p_argument_value => p_GROUP_NAME);
644 END IF;
645 close C1;
646 END IF;
647 NULL;
648 ELSIF(p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE)
649 THEN
650 -- Hint: Validate data
651 -- IF p_GROUP_NAME <> G_MISS_CHAR
652 -- verify if data is valid
653 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
654 IF p_GROUP_NAME <> CSC_CORE_UTILS_PVT.G_MISS_CHAR
655 THEN
656 FOR C1_REC IN C1 LOOP
657 IF c1_rec.group_id <> p_GROUP_ID THEN
658 x_return_status := FND_API.G_RET_STS_ERROR;
659 CSC_CORE_UTILS_PVT.Add_Duplicate_Value_Msg(
660 p_api_name => p_api_name,
661 p_argument => 'p_GROUP_NAME' ,
662 p_argument_value => p_GROUP_NAME);
663 END IF;
664 END LOOP;
665 END IF;
666 END IF;
667
668 -- Standard call to get message count and if count is 1, get message info.
669 FND_MSG_PUB.Count_And_Get
670 ( p_count => x_msg_count,
671 p_data => x_msg_data
672 );
673
674 END Validate_GROUP_NAME;
675
676
677 PROCEDURE Validate_GROUP_NAME_CODE (
678 P_Api_Name IN VARCHAR2,
679 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
680 P_Validation_mode IN VARCHAR2,
681 P_GROUP_NAME_CODE IN VARCHAR2,
682 X_Return_Status OUT NOCOPY VARCHAR2,
683 X_Msg_Count OUT NOCOPY NUMBER,
684 X_Msg_Data OUT NOCOPY VARCHAR2
685 )
686 IS
687 Cursor C1 is
688 Select group_id
689 From csc_prof_groups_b
690 Where group_name_code = p_GROUP_NAME_CODE;
691 l_dummy Number;
692 BEGIN
693
694 -- Initialize message list if p_init_msg_list is set to TRUE.
695 IF FND_API.to_Boolean( p_init_msg_list )
696 THEN
697 FND_MSG_PUB.initialize;
698 END IF;
699
700
701 -- Initialize API return status to SUCCESS
702 x_return_status := FND_API.G_RET_STS_SUCCESS;
703
704 IF(p_validation_mode = CSC_CORE_UTILS_PVT.G_CREATE)
705 THEN
706 -- Hint: Validate data
707 -- IF p_GROUP_NAME_CODE is not NULL and p_GROUP_NAME_CODE <> G_MISS_CHAR
708 -- verify if data is valid
709 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
710 IF p_GROUP_NAME_CODE is not NULL and p_GROUP_NAME_CODE <> CSC_CORE_UTILS_PVT.G_MISS_CHAR
711 THEN
712 Open C1;
713 Fetch C1 into l_dummy;
714 IF C1%FOUND THEN
715 x_return_status := FND_API.G_RET_STS_ERROR;
716 CSC_CORE_UTILS_PVT.Add_Duplicate_Value_Msg(
717 p_api_name => p_api_name,
718 p_argument => 'p_GROUP_NAME_CODE' ,
719 p_argument_value => p_GROUP_NAME_CODE);
720 END IF;
721 Close C1;
722 END IF;
723 NULL;
724 ELSIF(p_validation_mode = CSC_CORE_UTILS_PVT.G_UPDATE)
725 THEN
726 -- Hint: Validate data
727 -- IF p_GROUP_NAME_CODE <> G_MISS_CHAR
728 -- verify if data is valid
729 -- if data is not valid : x_return_status := FND_API.G_RET_STS_ERROR;
730 --no validate of group name code
731 NULL;
732 END IF;
733
734 -- Standard call to get message count and if count is 1, get message info.
735 FND_MSG_PUB.Count_And_Get
736 ( p_count => x_msg_count,
737 p_data => x_msg_data
738 );
739
740 END Validate_GROUP_NAME_CODE;
741
742
743
744 PROCEDURE Validate_IN_CUST_DASHBOARD (
745 P_Api_Name IN VARCHAR2,
746 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
747 P_Validation_mode IN VARCHAR2,
748 P_USE_IN_CUSTOMER_DASHBOARD IN VARCHAR2,
749 X_Return_Status OUT NOCOPY VARCHAR2,
750 X_Msg_Count OUT NOCOPY NUMBER,
751 X_Msg_Data OUT NOCOPY VARCHAR2
752 )
753 IS
754 BEGIN
755
756 -- Initialize message list if p_init_msg_list is set to TRUE.
757 IF FND_API.to_Boolean( p_init_msg_list )
758 THEN
759 FND_MSG_PUB.initialize;
760 END IF;
761
762 -- Initialize API return status to SUCCESS
763 x_return_status := FND_API.G_RET_STS_SUCCESS;
764
765 IF ( P_USE_IN_CUSTOMER_DASHBOARD <> CSC_CORE_UTILS_PVT.G_MISS_CHAR ) AND
766 ( P_USE_IN_CUSTOMER_DASHBOARD IS NOT NULL )
767 THEN
768 IF CSC_CORE_UTILS_PVT.lookup_code_not_exists(
769 p_effective_date => trunc(sysdate),
770 p_lookup_type => 'YES_NO',
771 p_lookup_code => P_USE_IN_CUSTOMER_DASHBOARD ) <> FND_API.G_RET_STS_SUCCESS
772 THEN
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
775 p_api_name => p_api_name,
776 p_argument_value => P_USE_IN_CUSTOMER_DASHBOARD,
777 p_argument => 'P_USE_IN_CUSTOMER_DASHBOARD');
778 END IF;
779 END IF;
780
781 -- Standard call to get message count and if count is 1, get message info.
782 FND_MSG_PUB.Count_And_Get
783 ( p_count => x_msg_count,
784 p_data => x_msg_data
785 );
786
787 END Validate_IN_CUST_DASHBOARD;
788
789
790 PROCEDURE Validate_SEEDED_FLAG (
791 P_Api_Name IN VARCHAR2,
792 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
793 P_Validation_mode IN VARCHAR2,
794 P_SEEDED_FLAG IN VARCHAR2,
795 X_Return_Status OUT NOCOPY VARCHAR2,
796 X_Msg_Count OUT NOCOPY NUMBER,
797 X_Msg_Data OUT NOCOPY VARCHAR2
798 )
799 IS
800 BEGIN
801
802 -- Initialize message list if p_init_msg_list is set to TRUE.
803 IF FND_API.to_Boolean( p_init_msg_list )
804 THEN
805 FND_MSG_PUB.initialize;
806 END IF;
807
808
809 -- Initialize API return status to SUCCESS
810 x_return_status := FND_API.G_RET_STS_SUCCESS;
811
812 --Its a hardcode right now but should change i guess.***
813 IF ( P_SEEDED_FLAG IS NOT NULL AND
814 P_SEEDED_FLAG <> CSC_CORE_UTILS_PVT.G_MISS_CHAR ) THEN
815 IF P_SEEDED_FLAG NOT IN ('Y','N')
816 THEN
817 x_return_status := FND_API.G_RET_STS_ERROR;
818 CSC_CORE_UTILS_PVT.Add_Invalid_Argument_Msg(
819 p_api_name => p_api_name,
820 p_argument_value => P_SEEDED_FLAG,
821 p_argument => 'P_SEEDED_FLAG');
822 END IF;
823 END IF;
824
825 -- Standard call to get message count and if count is 1, get message info.
826 FND_MSG_PUB.Count_And_Get
827 ( p_count => x_msg_count,
828 p_data => x_msg_data
829 );
830
831 END Validate_SEEDED_FLAG;
832
833
834 PROCEDURE Validate_profile_groups(
835 P_Init_Msg_List IN VARCHAR2 := CSC_CORE_UTILS_PVT.G_FALSE,
836 P_Validation_level IN NUMBER := CSC_CORE_UTILS_PVT.G_VALID_LEVEL_FULL,
837 P_Validation_mode IN VARCHAR2,
838 P_PROF_GROUP_Rec IN PROF_GROUP_Rec_Type,
839 X_Return_Status OUT NOCOPY VARCHAR2,
840 X_Msg_Count OUT NOCOPY NUMBER,
841 X_Msg_Data OUT NOCOPY VARCHAR2
842 )
843 IS
844 l_api_name CONSTANT VARCHAR2(30) := 'Validate_profile_groups';
845 BEGIN
846
847
848 -- Initialize API return status to SUCCESS
849 x_return_status := FND_API.G_RET_STS_SUCCESS;
850
851 Validate_GROUP_NAME(
852 p_Api_Name => l_api_name,
853 p_init_msg_list => FND_API.G_FALSE,
854 p_validation_mode => p_validation_mode,
855 p_GROUP_NAME => P_PROF_GROUP_Rec.GROUP_NAME,
856 p_GROUP_ID => P_PROF_GROUP_Rec.GROUP_ID,
857 x_return_status => x_return_status,
858 x_msg_count => x_msg_count,
859 x_msg_data => x_msg_data);
860 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
861 raise FND_API.G_EXC_ERROR;
862 END IF;
863 Validate_GROUP_NAME_CODE(
864 p_Api_Name => l_api_name,
865 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
866 p_validation_mode => p_validation_mode,
867 p_GROUP_NAME_CODE => P_PROF_GROUP_Rec.GROUP_NAME_CODE,
868 x_return_status => x_return_status,
869 x_msg_count => x_msg_count,
870 x_msg_data => x_msg_data);
871 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
872 raise FND_API.G_EXC_ERROR;
873 END IF;
874
875 -- validate start and end date
876 CSC_CORE_UTILS_PVT.Validate_Start_End_Dt(
877 p_Api_name => l_Api_name,
878 p_START_DATE => P_PROF_GROUP_Rec.START_DATE_ACTIVE,
879 p_END_DATE => P_PROF_GROUP_Rec.END_DATE_ACTIVE,
880 x_return_status => x_return_status );
881 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
882 raise FND_API.G_EXC_ERROR;
883 END IF;
884
885 Validate_IN_CUST_DASHBOARD(
886 p_Api_Name => l_api_name,
887 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
888 p_validation_mode => p_validation_mode,
889 p_USE_IN_CUSTOMER_DASHBOARD => P_PROF_GROUP_Rec.USE_IN_CUSTOMER_DASHBOARD,
890 x_return_status => x_return_status,
891 x_msg_count => x_msg_count,
892 x_msg_data => x_msg_data);
893 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
894 raise FND_API.G_EXC_ERROR;
895 END IF;
896 Validate_SEEDED_FLAG(
897 p_Api_Name => l_api_name,
898 p_init_msg_list => CSC_CORE_UTILS_PVT.G_FALSE,
899 p_validation_mode => p_validation_mode,
900 p_SEEDED_FLAG => P_PROF_GROUP_Rec.SEEDED_FLAG,
901 x_return_status => x_return_status,
902 x_msg_count => x_msg_count,
903 x_msg_data => x_msg_data);
904 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
905 raise FND_API.G_EXC_ERROR;
906 END IF;
907
908 CSC_CORE_UTILS_PVT.Validate_APPLICATION_ID (
909 P_Init_Msg_List => CSC_CORE_UTILS_PVT.G_FALSE,
910 P_Application_ID => P_PROF_GROUP_Rec.application_id,
911 p_effective_date => SYSDATE,
912 X_Return_Status => x_return_status,
913 X_Msg_Count => x_msg_count,
914 X_Msg_Data => x_msg_data );
915
916 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
917 RAISE FND_API.G_EXC_ERROR;
918 END IF;
919
920
921
922 END Validate_profile_groups;
923
924 End CSC_PROFILE_GROUPS_PVT;