[Home] [Help]
PACKAGE BODY: APPS.AMS_USER_STATUSES_PVT
Source
1 PACKAGE BODY AMS_USER_STATUSES_PVT AS
2 /* $Header: amsvustb.pls 115.25 2003/12/10 08:04:13 vmodur ship $ */
3 -----------------------------------------------------------
4 -- PACKAGE
5 -- AMS_USER_STATUSES_PVT
6 --
7 -- PROCEDURES
8 -- AMS_USER_STATUSES_VL:
9 -- Check_User_Status_Req_Items
10 -- Check_User_Status_UK_Items
11 -- Check_User_Status_FK_Items
12 -- Check_User_Status_Lookup_Items
13 -- Check_User_Status_Flag_Items
14 --
15 -- NOTES
16 --
17 --
18 -- HISTORY
19 -- 10-Nov-1999 rvaka Created.
20 -- 17-Jun-2002 sveerave included application_id while inserting the row,
21 -- and corrected check_lookup_exists for lookup_type
22 -- 25-Jun-2003 vmodur Added can_disable_status for Bug 3021076
23 -- 03-Dec-2003 vmodur Bug 3265043 and OZF Migration
24 -----------------------------------------------------------
25 --
26 -- Global CONSTANTS
27 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_User_Statuses_PVT';
28 -- Check_User_Status_Req_Items
29 AMS_DEBUG_HIGH_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
30 AMS_DEBUG_LOW_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
31 AMS_DEBUG_MEDIUM_ON CONSTANT boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
32
33 PROCEDURE Check_User_Status_Req_Items (
34 p_user_status_rec IN User_Status_Rec_Type,
35 x_return_status OUT NOCOPY VARCHAR2
36 );
37 -- Check_User_Status_UK_Items
38 PROCEDURE Check_User_Status_UK_Items (
42 );
39 p_user_status_rec IN User_Status_Rec_Type,
40 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
41 x_return_status OUT NOCOPY VARCHAR2
43 -- Check_User_Status_FK_Items
44 PROCEDURE Check_User_Status_FK_Items (
45 p_user_status_rec IN User_Status_Rec_Type,
46 x_return_status OUT NOCOPY VARCHAR2
47 );
48 -- Check_User_Status_Lookup_Items
49 PROCEDURE Check_User_Status_Lookup_Items (
50 p_user_status_rec IN User_Status_Rec_Type,
51 x_return_status OUT NOCOPY VARCHAR2
52 );
53 -- Check_User_Status_Flag_Items
54 PROCEDURE Check_User_Status_Flag_Items (
55 p_user_status_rec IN User_Status_Rec_Type,
56 x_return_status OUT NOCOPY VARCHAR2
57 );
58 FUNCTION compare_columns(
59 l_user_status_rec IN User_Status_Rec_Type
60 ) RETURN VARCHAR2; -- FND_API.g_true/g_false
61
62 FUNCTION seed_needs_update(
63 l_user_status_rec IN User_Status_Rec_Type
64 ) RETURN VARCHAR2; -- FND_API.g_true/g_false
65
66 FUNCTION can_disable_status(
67 l_user_status_rec IN User_Status_Rec_Type
68 ) RETURN VARCHAR2; -- FND_API.g_true/g_false
69 -------------------------------------
70 ----- USER_STATUS -----
71 -------------------------------------
72 --------------------------------------------------------------------
73 -- PROCEDURE
74 -- Create_User_Status
75 --
76 --------------------------------------------------------------------
77 PROCEDURE Create_User_Status (
78 p_api_version IN NUMBER,
79 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
80 p_commit IN VARCHAR2 := FND_API.g_false,
81 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_count OUT NOCOPY NUMBER,
84 x_msg_data OUT NOCOPY VARCHAR2,
85 p_user_status_rec IN User_Status_Rec_Type,
86 x_user_status_id OUT NOCOPY NUMBER
87 )
88 IS
89 L_API_VERSION CONSTANT NUMBER := 1.0;
90 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_User_Status';
91 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
92 l_User_Status_rec User_Status_Rec_Type := p_user_status_rec;
93 l_dummy NUMBER;
94 l_return_status VARCHAR2(1);
95 CURSOR c_seq IS
96 SELECT ams_user_statuses_b_s.NEXTVAL
97 FROM dual;
98 CURSOR c_id_exists (x_id IN NUMBER) IS
99 SELECT 1
100 FROM dual
101 WHERE EXISTS (SELECT 1
102 FROM ams_user_statuses_vl
103 WHERE user_status_id = x_id);
104 BEGIN
105 --------------------- initialize -----------------------
106 SAVEPOINT Create_User_Status;
107 IF (AMS_DEBUG_HIGH_ON) THEN
108
109 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
110 END IF;
111 IF FND_API.to_boolean (p_init_msg_list) THEN
112 FND_MSG_PUB.initialize;
113 END IF;
114 IF NOT FND_API.compatible_api_call (
115 L_API_VERSION,
116 p_api_version,
117 L_API_NAME,
118 G_PKG_NAME
119 ) THEN
120 RAISE FND_API.g_exc_unexpected_error;
121 END IF;
122 x_return_status := FND_API.g_ret_sts_success;
123 ----------------------- validate -----------------------
124 IF (AMS_DEBUG_HIGH_ON) THEN
125
126 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
127 END IF;
128
129
130
131 Validate_User_Status (
132 p_api_version => l_api_version,
133 p_init_msg_list => p_init_msg_list,
134 p_validation_level => p_validation_level,
135 x_return_status => l_return_status,
136 x_msg_count => x_msg_count,
137 x_msg_data => x_msg_data,
138 p_user_status_rec => l_user_status_rec
139 );
140 IF l_return_status = FND_API.g_ret_sts_error THEN
141 RAISE FND_API.g_exc_error;
142 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
143 RAISE FND_API.g_exc_unexpected_error;
144 END IF;
145 --
146 -- Check for the ID.
147 --
148 IF l_user_status_rec.user_status_id IS NULL THEN
149 LOOP
150 --
151 -- If the ID is not passed into the API, then
152 -- grab a value from the sequence.
153 OPEN c_seq;
154 FETCH c_seq INTO l_user_status_rec.user_status_id;
155 CLOSE c_seq;
156 --
157 -- Check to be sure that the sequence does not exist.
158 OPEN c_id_exists (l_user_status_rec.user_status_id);
159 FETCH c_id_exists INTO l_dummy;
160 CLOSE c_id_exists;
161 --
162 -- If the value for the ID already exists, then
163 -- l_dummy would be populated with '1', otherwise,
164 -- it receives NULL.
165 EXIT WHEN l_dummy IS NULL;
166 END LOOP;
167 END IF;
168 -------------------------- insert --------------------------
169 IF (AMS_DEBUG_HIGH_ON) THEN
170
171 AMS_Utility_PVT.debug_message (l_full_name || ': Insert');
172 END IF;
173 --
174 -- Insert into mutli-language supported table.
175 --
176 INSERT INTO ams_user_statuses_b (
177 user_status_id,
178 -- standard who columns
179 last_update_date,
180 last_updated_by,
181 creation_date,
182 created_by,
183 last_update_login,
184 object_version_number,
185 system_status_type,
189 seeded_flag,
186 system_status_code,
187 enabled_flag,
188 default_flag,
190 start_date_active,
191 end_date_active,
192 application_id
193 )
194 VALUES (
195 l_user_status_rec.user_status_id,
196 -- standard who columns
197 SYSDATE,
198 FND_GLOBAL.User_Id,
199 SYSDATE,
200 FND_GLOBAL.User_Id,
201 FND_GLOBAL.Conc_Login_Id,
202 1, -- object_version_number
203 l_user_status_rec.system_status_type,
204 l_user_status_rec.system_status_code,
205 NVL (l_user_status_rec.enabled_flag, 'Y'), -- Default is 'Y'
206 NVL (l_user_status_rec.default_flag, 'N'), -- Default is 'N'
207 NVL (l_user_status_rec.seeded_flag, 'N'), -- Default is 'N'
208 l_user_status_rec.start_date_active,
209 l_user_status_rec.end_date_active,
210 fnd_global.resp_appl_id -- added to capture application_id
211 );
212 INSERT INTO ams_user_statuses_tl (
213 user_status_id,
214 language,
215 last_update_date,
216 last_updated_by,
217 creation_date,
218 created_by,
219 last_update_login,
220 source_lang,
221 name,
222 description
223 )
224 SELECT l_user_status_rec.user_status_id,
225 l.language_code,
226 -- standard who columns
227 SYSDATE,
228 FND_GLOBAL.User_Id,
229 SYSDATE,
230 FND_GLOBAL.User_Id,
231 FND_GLOBAL.Conc_Login_Id,
232 USERENV('LANG'),
233 l_user_status_rec.name,
234 l_user_status_rec.description
235 FROM fnd_languages l
236 WHERE l.installed_flag IN ('I', 'B')
237 AND NOT EXISTS (SELECT NULL
238 FROM ams_user_statuses_tl t
239 WHERE t.user_status_id = l_user_status_rec.user_status_id
240 AND t.language = l.language_code);
241 ------------------------- finish -------------------------------
242 -- set OUT value
243 x_user_status_id := l_user_status_rec.user_status_id;
244 --
245 -- END of API body.
246 --
247 -- Standard check of p_commit.
248 IF FND_API.To_Boolean ( p_commit ) THEN
249 COMMIT WORK;
250 END IF;
251 FND_MSG_PUB.count_and_get(
252 p_encoded => FND_API.g_false,
253 p_count => x_msg_count,
254 p_data => x_msg_data
255 );
256 IF (AMS_DEBUG_HIGH_ON) THEN
257
258 AMS_Utility_PVT.debug_message (l_full_name || ': End');
259 END IF;
260 EXCEPTION
261 WHEN FND_API.g_exc_error THEN
262 ROLLBACK TO Create_User_Status;
263 x_return_status := FND_API.g_ret_sts_error;
264 FND_MSG_PUB.count_and_get(
265 p_encoded => FND_API.g_false,
266 p_count => x_msg_count,
267 p_data => x_msg_data
268 );
269 WHEN FND_API.g_exc_unexpected_error THEN
270 ROLLBACK TO Create_User_Status;
271 x_return_status := FND_API.g_ret_sts_unexp_error ;
272 FND_MSG_PUB.count_and_get (
273 p_encoded => FND_API.g_false,
274 p_count => x_msg_count,
275 p_data => x_msg_data
276 );
277 WHEN OTHERS THEN
278 ROLLBACK TO Create_User_Status;
279 x_return_status := FND_API.g_ret_sts_unexp_error ;
280 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
281 THEN
282 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
283 END IF;
284 FND_MSG_PUB.count_and_get (
285 p_encoded => FND_API.g_false,
286 p_count => x_msg_count,
287 p_data => x_msg_data
288 );
289 END Create_user_status;
290 --------------------------------------------------------------------
291 -- PROCEDURE
292 -- Update_User_Status
293 --
294 --------------------------------------------------------------------
295 PROCEDURE Update_User_Status (
296 p_api_version IN NUMBER,
297 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
298 p_commit IN VARCHAR2 := FND_API.g_false,
299 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
300 x_return_status OUT NOCOPY VARCHAR2,
301 x_msg_count OUT NOCOPY NUMBER,
302 x_msg_data OUT NOCOPY VARCHAR2,
303 p_user_status_rec IN User_Status_Rec_Type
304 )
305 IS
306 L_API_VERSION CONSTANT NUMBER := 1.0;
307 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_User_Status';
308 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
309 l_user_status_rec User_Status_Rec_Type := p_user_status_rec;
310 l_dummy NUMBER;
311 l_return_status VARCHAR2(1);
312 BEGIN
313 --------------------- initialize -----------------------
314 SAVEPOINT Update_User_Status;
315 IF (AMS_DEBUG_HIGH_ON) THEN
316
317 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
318 END IF;
319 IF FND_API.to_boolean (p_init_msg_list) THEN
320 FND_MSG_PUB.initialize;
321 END IF;
322 IF NOT FND_API.compatible_api_call(
323 l_api_version,
324 p_api_version,
325 l_api_name,
326 g_pkg_name
327 ) THEN
328 RAISE FND_API.g_exc_unexpected_error;
329 END IF;
330 x_return_status := FND_API.g_ret_sts_success;
331 ----------------------- validate ----------------------
332 IF (AMS_DEBUG_HIGH_ON) THEN
333
337 Complete_User_Status_Rec (p_user_status_rec, l_user_status_rec);
334 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
335 END IF;
336 -- replace g_miss_char/num/date with current column values
338
339 IF l_user_status_rec.seeded_flag = 'Y' THEN
340 IF compare_columns(l_user_status_rec) = FND_API.g_false THEN
341 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
342 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_SEED_DATA');
343 FND_MSG_PUB.add;
344 END IF;
345 RAISE FND_API.g_exc_error;
346 END IF;
347 ELSE
348
349 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
350 Check_User_Status_Items (
351 p_user_status_rec => p_user_status_rec,
352 p_validation_mode => JTF_PLSQL_API.g_update,
353 x_return_status => l_return_status
354 );
355 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
356 RAISE FND_API.g_exc_unexpected_error;
357 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
358 RAISE FND_API.g_exc_error;
359 END IF;
360 END IF;
361 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
362 Check_User_Status_Record (
363 p_user_status_rec => p_user_status_rec,
364 p_complete_rec => l_user_status_rec,
365 x_return_status => l_return_status
366 );
367 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
368 RAISE FND_API.g_exc_unexpected_error;
369 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
370 RAISE FND_API.g_exc_error;
371 END IF;
372 END IF;
373
374 END IF; -- check for seeded flag
375
376 IF l_user_status_rec.default_flag = 'Y' THEN
377 UPDATE ams_user_statuses_b
378 SET
379 default_flag = 'N'
380 WHERE system_status_type = l_user_status_rec.system_status_type
381 AND system_status_code = l_user_status_rec.system_status_code;
382
383 -- always enable the default status.
384 l_user_status_rec.enabled_flag := 'Y';
385 END IF;
386
387 -- Check to see if the row is seeded if the row is seeded then can't update
388 -- modified.. enabled flag for seeded rows can be updated.. added seed_needs_update function
389 IF l_user_status_rec.seeded_flag='N' OR
390 seed_needs_update(l_user_status_rec) = FND_API.g_true THEN
391 -------------------------- update --------------------
392 IF (AMS_DEBUG_HIGH_ON) THEN
393
394 AMS_Utility_PVT.debug_message (l_full_name || ': Update');
395 END IF;
396
397 -- Check to see if the user status can be disabled
398 IF NVL(l_user_status_rec.enabled_flag,'Y') = 'N' THEN
399 IF can_disable_status(l_user_status_rec) = FND_API.g_false THEN
400 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
401 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_CANNOT_DISABLE');
402 FND_MESSAGE.set_token('USER_STATUS', l_user_status_rec.name);
403 FND_MSG_PUB.add;
404 END IF;
405 RAISE FND_API.G_EXC_ERROR;
406 END IF;
407 END IF;
408
409 UPDATE ams_user_statuses_b
410 SET
414 object_version_number = object_version_number + 1,
411 last_update_date = SYSDATE,
412 last_updated_by = FND_GLOBAL.User_Id,
413 last_update_login = FND_GLOBAL.Conc_Login_Id,
415 system_status_type = l_user_status_rec.system_status_type,
416 system_status_code = l_user_status_rec.system_status_code,
417 enabled_flag = NVL (l_user_status_rec.enabled_flag, 'Y'),
418 default_flag = NVL (l_user_status_rec.default_flag, 'N'),
419 seeded_flag = NVL (l_user_status_rec.seeded_flag, 'N'),
420 start_date_active = l_user_status_rec.start_date_active,
421 end_date_active = l_user_status_rec.end_date_active
422 WHERE user_status_id = l_user_status_rec.user_status_id
423 AND object_version_number = l_user_status_rec.object_version_number;
424 IF (SQL%NOTFOUND) THEN
425 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
426 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
427 FND_MSG_PUB.add;
428 END IF;
429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
430 END IF;
431 UPDATE ams_user_statuses_tl
432 SET
433 last_update_date = SYSDATE,
434 last_updated_by = FND_GLOBAL.User_Id,
435 last_update_login = FND_GLOBAL.Conc_Login_Id,
436 source_lang = USERENV('LANG'),
437 name = l_user_status_rec.name,
438 description = l_user_status_rec.description
439 WHERE user_status_id = l_user_status_rec.user_status_id
440 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
441 IF (SQL%NOTFOUND) THEN
442 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
443 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
444 FND_MSG_PUB.add;
445 END IF;
446 RAISE FND_API.g_exc_error;
447 END IF;
448 END IF; -- ending if loop for second seeded_flag check
449
450 -------------------- finish --------------------------
451 IF FND_API.to_boolean (p_commit) THEN
452 COMMIT;
453 END IF;
454 FND_MSG_PUB.count_and_get (
455 p_encoded => FND_API.g_false,
456 p_count => x_msg_count,
457 p_data => x_msg_data
458 );
459 IF (AMS_DEBUG_HIGH_ON) THEN
460
461 AMS_Utility_PVT.debug_message (l_full_name || ': End');
462 END IF;
463 EXCEPTION
464 WHEN FND_API.g_exc_error THEN
465 ROLLBACK TO Update_User_Status;
466 x_return_status := FND_API.g_ret_sts_error;
467 FND_MSG_PUB.count_and_get (
468 p_encoded => FND_API.g_false,
469 p_count => x_msg_count,
470 p_data => x_msg_data
471 );
472 WHEN FND_API.g_exc_unexpected_error THEN
473 ROLLBACK TO Update_User_Status;
474 x_return_status := FND_API.g_ret_sts_unexp_error ;
475 FND_MSG_PUB.count_and_get (
476 p_encoded => FND_API.g_false,
477 p_count => x_msg_count,
478 p_data => x_msg_data
479 );
480 WHEN OTHERS THEN
481 ROLLBACK TO Update_User_Status;
482 x_return_status := FND_API.g_ret_sts_unexp_error ;
483 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
484 THEN
485 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
486 END IF;
487 FND_MSG_PUB.count_and_get (
488 p_encoded => FND_API.g_false,
489 p_count => x_msg_count,
490 p_data => x_msg_data
491 );
492 END Update_User_Status;
493 --------------------------------------------------------------------
494 -- PROCEDURE
495 -- Delete_User_Status
496 --
497 --------------------------------------------------------------------
498 PROCEDURE Delete_User_Status (
499 p_api_version IN NUMBER,
500 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
501 p_commit IN VARCHAR2 := FND_API.g_false,
502 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
503 x_return_status OUT NOCOPY VARCHAR2,
504 x_msg_count OUT NOCOPY NUMBER,
505 x_msg_data OUT NOCOPY VARCHAR2,
506 p_user_status_id IN NUMBER,
507 p_object_version IN NUMBER
508 )
509 IS
510 CURSOR c_user_status IS
511 SELECT *
512 FROM ams_user_statuses_vl
513 WHERE user_status_id = p_user_status_id;
514 --
515 -- This is the only exception for using %ROWTYPE.
516 -- We are selecting from the VL view, which may
517 -- have some denormalized columns as compared to
518 -- the base tables.
519 l_user_status_rec c_user_status%ROWTYPE;
520
521 l_api_version CONSTANT NUMBER := 1.0;
522 l_api_name CONSTANT VARCHAR2(30) := 'Delete_User_Status';
523 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
524 BEGIN
525 OPEN c_user_status;
526 FETCH c_user_status INTO l_user_status_rec;
527 IF c_user_status%NOTFOUND THEN
528 CLOSE c_user_status;
529 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
530 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
531 FND_MSG_PUB.add;
532 END IF;
533 RAISE FND_API.g_exc_error;
534 END IF;
535 CLOSE c_user_status;
536 --------------------- initialize -----------------------
537 SAVEPOINT Delete_User_Status;
538 IF (AMS_DEBUG_HIGH_ON) THEN
539
540 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
541 END IF;
542 IF FND_API.to_boolean (p_init_msg_list) THEN
543 FND_MSG_PUB.initialize;
544 END IF;
545 IF NOT FND_API.compatible_api_call (
546 l_api_version,
547 p_api_version,
548 l_api_name,
549 g_pkg_name
550 ) THEN
551 RAISE FND_API.g_exc_unexpected_error;
552 END IF;
553 x_return_status := FND_API.g_ret_sts_success;
554 ------------------------ delete ------------------------
555 IF (AMS_DEBUG_HIGH_ON) THEN
556
557 AMS_Utility_PVT.debug_message (l_full_name || ': Delete');
558 END IF;
559 -- Delete TL data
560
561 IF l_user_status_rec.seeded_flag='N'
562 THEN
563
564 DELETE FROM ams_user_statuses_tl
565 WHERE user_status_id = p_user_status_id;
566 IF (SQL%NOTFOUND) THEN
567 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error)
568 THEN
569 FND_MESSAGE.set_name ('AMS', 'AMS_API_RECORD_NOT_FOUND');
570 FND_MSG_PUB.add;
571 END IF;
572 RAISE FND_API.g_exc_error;
573 END IF;
574 DELETE FROM ams_user_statuses_b
575 WHERE user_status_id = p_user_status_id
576 AND object_version_number = p_object_version;
577
578 ELSE
579 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
580 FND_MESSAGE.set_name ('AMS', 'AMS_API_SEED_DATA');
581 FND_MSG_PUB.add;
582 RAISE FND_API.g_exc_error;
583 END IF;
584
585 END IF; -- ending if loop for seeded_flag check
586
587
588 -------------------- finish --------------------------
589 IF FND_API.to_boolean (p_commit) THEN
590 COMMIT;
591 END IF;
592 FND_MSG_PUB.count_and_get (
593 p_encoded => FND_API.g_false,
594 p_count => x_msg_count,
595 p_data => x_msg_data
596 );
597 IF (AMS_DEBUG_HIGH_ON) THEN
598
599 AMS_Utility_PVT.debug_message (l_full_name || ': End');
600 END IF;
601 EXCEPTION
602 WHEN FND_API.g_exc_error THEN
603 ROLLBACK TO Delete_User_Status;
604 x_return_status := FND_API.g_ret_sts_error;
605 FND_MSG_PUB.count_and_get (
606 p_encoded => FND_API.g_false,
607 p_count => x_msg_count,
608 p_data => x_msg_data
609 );
610 WHEN FND_API.g_exc_unexpected_error THEN
611 ROLLBACK TO Delete_User_Status;
612 x_return_status := FND_API.g_ret_sts_unexp_error ;
613 FND_MSG_PUB.count_and_get (
614 p_encoded => FND_API.g_false,
615 p_count => x_msg_count,
616 p_data => x_msg_data
617 );
618 WHEN OTHERS THEN
619 ROLLBACK TO Delete_User_Status;
620 x_return_status := FND_API.g_ret_sts_unexp_error ;
621 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
622 THEN
623 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
624 END IF;
625 FND_MSG_PUB.count_and_get (
626 p_encoded => FND_API.g_false,
627 p_count => x_msg_count,
628 p_data => x_msg_data
629 );
630 END Delete_User_Status;
631 --------------------------------------------------------------------
632 -- PROCEDURE
633 -- Lock_User_Status
634 --
635 --------------------------------------------------------------------
636 PROCEDURE Lock_User_Status (
637 p_api_version IN NUMBER,
638 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
639 p_commit IN VARCHAR2 := FND_API.g_false,
640 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
641 x_return_status OUT NOCOPY VARCHAR2,
642 x_msg_count OUT NOCOPY NUMBER,
643 x_msg_data OUT NOCOPY VARCHAR2,
644 p_user_status_id IN NUMBER,
645 p_object_version IN NUMBER
646 )
647 IS
648 l_api_version CONSTANT NUMBER := 1.0;
649 l_api_name CONSTANT VARCHAR2(30) := 'Lock_User_Status';
650 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
651 l_dummy NUMBER; -- Used by the lock cursor.
652 --
653 -- NOTE: Not necessary to distinguish between a record
654 -- which does not exist and one which has been updated
655 -- by another user. To get that distinction, remove
656 -- the object_version condition from the SQL statement
657 -- and perform comparison in the body and raise the
658 -- exception there.
659 CURSOR c_lock IS
660 SELECT object_version_number
661 FROM ams_user_statuses_vl
662 WHERE user_status_id = p_user_status_id
663 AND object_version_number = p_object_version
664 FOR UPDATE NOWAIT;
665 BEGIN
666 --------------------- initialize -----------------------
667 IF (AMS_DEBUG_HIGH_ON) THEN
668
669 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
670 END IF;
671 IF FND_API.to_boolean (p_init_msg_list) THEN
672 FND_MSG_PUB.initialize;
673 END IF;
674 IF NOT FND_API.compatible_api_call (
675 l_api_version,
676 p_api_version,
677 l_api_name,
678 g_pkg_name
679 ) THEN
680 RAISE FND_API.g_exc_unexpected_error;
681 END IF;
682 x_return_status := FND_API.g_ret_sts_success;
683 ------------------------ lock -------------------------
684 IF (AMS_DEBUG_HIGH_ON) THEN
685
686 AMS_Utility_PVT.debug_message (l_full_name || ': Lock');
687 END IF;
688 OPEN c_lock;
689 FETCH c_lock INTO l_dummy;
690 IF (c_lock%NOTFOUND) THEN
691 CLOSE c_lock;
692 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
693 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
694 FND_MSG_PUB.add;
695 END IF;
696 RAISE FND_API.g_exc_error;
697 END IF;
698 CLOSE c_lock;
699 -------------------- finish --------------------------
700 FND_MSG_PUB.count_and_get (
701 p_encoded => FND_API.g_false,
702 p_count => x_msg_count,
706
703 p_data => x_msg_data
704 );
705 IF (AMS_DEBUG_HIGH_ON) THEN
707 AMS_Utility_PVT.debug_message (l_full_name || ': End');
708 END IF;
709 EXCEPTION
710 WHEN AMS_Utility_PVT.resource_locked THEN
711 x_return_status := FND_API.g_ret_sts_error;
712 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
713 FND_MESSAGE.set_name ('AMS', 'AMS_API_RESOURCE_LOCKED');
714 FND_MSG_PUB.add;
715 END IF;
716 FND_MSG_PUB.count_and_get (
717 p_encoded => FND_API.g_false,
718 p_count => x_msg_count,
719 p_data => x_msg_data
720 );
721 WHEN FND_API.g_exc_error THEN
722 x_return_status := FND_API.g_ret_sts_error;
723 FND_MSG_PUB.count_and_get (
724 p_encoded => FND_API.g_false,
725 p_count => x_msg_count,
726 p_data => x_msg_data
727 );
728 WHEN FND_API.g_exc_unexpected_error THEN
729 x_return_status := FND_API.g_ret_sts_unexp_error ;
730 FND_MSG_PUB.count_and_get (
731 p_encoded => FND_API.g_false,
732 p_count => x_msg_count,
733 p_data => x_msg_data
734 );
735 WHEN OTHERS THEN
736 x_return_status := FND_API.g_ret_sts_unexp_error ;
737 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
738 THEN
739 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
740 END IF;
741 FND_MSG_PUB.count_and_get (
742 p_encoded => FND_API.g_false,
743 p_count => x_msg_count,
744 p_data => x_msg_data
745 );
746 END Lock_User_Status;
747 --------------------------------------------------------------------
748 -- PROCEDURE
749 -- Validate_User_Status
750 --
751 --------------------------------------------------------------------
752 PROCEDURE Validate_User_Status (
753 p_api_version IN NUMBER,
754 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
755 p_commit IN VARCHAR2 := FND_API.g_false,
756 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
757 x_return_status OUT NOCOPY VARCHAR2,
758 x_msg_count OUT NOCOPY NUMBER,
759 x_msg_data OUT NOCOPY VARCHAR2,
760 p_user_status_rec IN User_Status_Rec_Type
761 )
762 IS
763 L_API_VERSION CONSTANT NUMBER := 1.0;
764 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_User_Status';
765 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
766 l_return_status VARCHAR2(1);
767 BEGIN
768 --------------------- initialize -----------------------
769 IF (AMS_DEBUG_HIGH_ON) THEN
770
771 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
772 END IF;
773 IF FND_API.to_boolean (p_init_msg_list) THEN
774 FND_MSG_PUB.initialize;
775 END IF;
776 IF NOT FND_API.compatible_api_call (
777 l_api_version,
778 p_api_version,
779 l_api_name,
780 g_pkg_name
781 ) THEN
782 RAISE FND_API.g_exc_unexpected_error;
783 END IF;
784 x_return_status := FND_API.g_ret_sts_success;
785 ---------------------- validate ------------------------
786 IF (AMS_DEBUG_HIGH_ON) THEN
787
788 AMS_Utility_PVT.debug_message (l_full_name || ': Check items');
789 END IF;
790 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
791 Check_User_Status_Items (
792 p_user_status_rec => p_user_status_rec,
793 p_validation_mode => JTF_PLSQL_API.g_create,
794 x_return_status => l_return_status
795 );
796 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
797 RAISE FND_API.g_exc_unexpected_error;
798 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
799 RAISE FND_API.g_exc_error;
800 END IF;
801 END IF;
802 IF (AMS_DEBUG_HIGH_ON) THEN
803
804 AMS_Utility_PVT.debug_message (l_full_name || ': Check record');
805 END IF;
806 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
807 Check_User_Status_Record (
808 p_user_status_rec => p_user_status_rec,
809 p_complete_rec => NULL,
810 x_return_status => l_return_status
811 );
812 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
813 RAISE FND_API.g_exc_unexpected_error;
814 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
815 RAISE FND_API.g_exc_error;
816 END IF;
817 END IF;
818 -------------------- finish --------------------------
819 FND_MSG_PUB.count_and_get (
820 p_encoded => FND_API.g_false,
821 p_count => x_msg_count,
822 p_data => x_msg_data
823 );
824 IF (AMS_DEBUG_HIGH_ON) THEN
825
826 AMS_Utility_PVT.debug_message (l_full_name || ': End');
827 END IF;
828 EXCEPTION
829 WHEN FND_API.g_exc_error THEN
830 x_return_status := FND_API.g_ret_sts_error;
831 FND_MSG_PUB.count_and_get (
832 p_encoded => FND_API.g_false,
833 p_count => x_msg_count,
834 p_data => x_msg_data
835 );
836 WHEN FND_API.g_exc_unexpected_error THEN
837 x_return_status := FND_API.g_ret_sts_unexp_error ;
838 FND_MSG_PUB.count_and_get (
839 p_encoded => FND_API.g_false,
840 p_count => x_msg_count,
841 p_data => x_msg_data
842 );
843 WHEN OTHERS THEN
844 x_return_status := FND_API.g_ret_sts_unexp_error;
845 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
846 THEN
847 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
848 END IF;
849 FND_MSG_PUB.count_and_get (
850 p_encoded => FND_API.g_false,
851 p_count => x_msg_count,
852 p_data => x_msg_data
853 );
854 END Validate_User_Status;
855
856 ---------------------------------------------------------------------
857 -- PROCEDURE
858 -- Check_User_Status_Items
859 --
860 ---------------------------------------------------------------------
861 PROCEDURE Check_User_Status_Items (
862 p_user_status_rec IN User_Status_Rec_Type,
863 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
864 x_return_status OUT NOCOPY VARCHAR2
865 )
866 IS
867 BEGIN
868 --
869 -- Validate required items.
870 Check_User_Status_Req_Items (
871 p_user_status_rec => p_user_status_rec,
872 x_return_status => x_return_status
873 );
874 IF x_return_status <> FND_API.g_ret_sts_success THEN
875 RETURN;
876 END IF;
877 --
878 -- Validate uniqueness.
879 Check_User_Status_UK_Items (
880 p_user_status_rec => p_user_status_rec,
881 p_validation_mode => p_validation_mode,
882 x_return_status => x_return_status
883 );
884 IF x_return_status <> FND_API.g_ret_sts_success THEN
885 RETURN;
886 END IF;
887 Check_User_Status_FK_Items(
888 p_user_status_rec => p_user_status_rec,
889 x_return_status => x_return_status
890 );
891 IF x_return_status <> FND_API.g_ret_sts_success THEN
892 RETURN;
893 END IF;
894 Check_User_Status_Lookup_Items (
895 p_user_status_rec => p_user_status_rec,
896 x_return_status => x_return_status
897 );
898 IF x_return_status <> FND_API.g_ret_sts_success THEN
899 RETURN;
900 END IF;
901 Check_User_Status_Flag_Items(
902 p_user_status_rec => p_user_status_rec,
903 x_return_status => x_return_status
904 );
905 IF x_return_status <> FND_API.g_ret_sts_success THEN
906 RETURN;
907 END IF;
908 END Check_User_Status_Items;
909 ---------------------------------------------------------------------
910 -- PROCEDURE
911 -- Check_User_Status_Record
912 --
913 -- PURPOSE
914 -- Check the record level business rules.
915 --
916 -- PARAMETERS
917 -- p_user_status_rec: the record to be validated; may contain attributes
918 -- as FND_API.g_miss_char/num/date
919 -- p_complete_rec: the complete record after all "g_miss" items
920 -- have been replaced by current database values
921 ---------------------------------------------------------------------
925 x_return_status OUT NOCOPY VARCHAR2
922 PROCEDURE Check_User_Status_Record (
923 p_user_status_rec IN User_Status_Rec_Type,
924 p_complete_rec IN User_Status_Rec_Type := NULL,
926 )
927 IS
928 l_start_date_active DATE;
929 l_end_date_active DATE;
930 BEGIN
931 --
932 -- Use local vars to reduce amount of typing.
933 if p_complete_rec.start_date_active IS NOT NULL then
934 l_start_date_active := p_complete_rec.start_date_active;
935 else
936 if p_user_status_rec.start_date_active is NOT NULL AND
937 p_user_status_rec.start_date_active <> FND_API.g_miss_date then
938 l_start_date_active := p_user_status_rec.start_date_active;
939 end if;
940 end if;
941
942 if p_complete_rec.end_date_active IS NOT NULL then
943 l_end_date_active := p_complete_rec.end_date_active;
944 else
945 if p_user_status_rec.end_date_active is NOT NULL AND
946 p_user_status_rec.end_date_active <> FND_API.g_miss_date then
947 l_end_date_active := p_user_status_rec.end_date_active;
948 end if;
949 end if;
950
951 x_return_status := FND_API.G_RET_STS_SUCCESS;
952 --
953 /*
954 -- Validate the active dates.
955 IF l_start_date_active <> FND_API.g_miss_date OR l_end_date_active <> FND_API.g_miss_date THEN
956 IF (l_start_date_active = FND_API.g_miss_date) THEN
957 l_start_date_active := p_complete_rec.start_date_active;
958 END IF;
959
960 IF (l_end_date_active IS NULL OR l_end_date_active = FND_API.g_miss_date) THEN
961 l_end_date_active := p_complete_rec.end_date_active;
962 END IF;
963 */
964 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.DEBUG_MESSAGE('sTART DATE:'|| to_char(l_start_date_active,'DD_MON_YYYY'));END IF;
965 --IF (AMS_DEBUG_HIGH_ON) THENAMS_UTILITY_PVT.DEBUG_MESSAGE('end DATE:'|| to_char(l_end_date_active,'DD-MON-YYYY'));END IF;
966
967 IF l_start_date_active IS NOT NULL AND l_end_date_active IS NOT NULL THEN
968 IF l_start_date_active > l_end_date_active THEN
969 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
970 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_FROMDT_GTR_TODT');
971 FND_MSG_PUB.add;
972 END IF;
973 x_return_status := FND_API.g_ret_sts_error;
974 RETURN;
975 END IF;
976 END IF;
977 -- END IF;
978
979 END Check_User_Status_Record;
980
981
982 ---------------------------------------------------------------------
983 -- PROCEDURE
984 -- Init_User_Status_Rec
985 --
986 ---------------------------------------------------------------------
987 PROCEDURE Init_User_Status_Rec (
988 x_user_status_rec OUT NOCOPY User_Status_Rec_Type
989 )
990 IS
991 BEGIN
992 x_user_status_rec.user_status_id := FND_API.g_miss_num;
993 x_user_status_rec.last_update_date := FND_API.g_miss_date;
994 x_user_status_rec.last_updated_by := FND_API.g_miss_num;
995 x_user_status_rec.creation_date := FND_API.g_miss_date;
996 x_user_status_rec.created_by := FND_API.g_miss_num;
997 x_user_status_rec.last_update_login := FND_API.g_miss_num;
998 x_user_status_rec.object_version_number := FND_API.g_miss_num;
999 x_user_status_rec.system_status_type := FND_API.g_miss_char;
1000 x_user_status_rec.system_status_code := FND_API.g_miss_char;
1001 x_user_status_rec.enabled_flag := FND_API.g_miss_char;
1002 x_user_status_rec.default_flag := FND_API.g_miss_char;
1003 x_user_status_rec.seeded_flag := FND_API.g_miss_char;
1004 x_user_status_rec.start_date_active := FND_API.g_miss_date;
1005 x_user_status_rec.end_date_active := FND_API.g_miss_date;
1006 x_user_status_rec.name := FND_API.g_miss_char;
1007 x_user_status_rec.description := FND_API.g_miss_char;
1008 END Init_User_Status_Rec;
1009 ---------------------------------------------------------------------
1010 -- PROCEDURE
1011 -- Complete_User_Status_Rec
1012 --
1013 ---------------------------------------------------------------------
1014 PROCEDURE Complete_User_Status_Rec (
1015 p_user_status_rec IN User_Status_Rec_Type,
1016 x_complete_rec OUT NOCOPY User_Status_Rec_Type
1017 )
1018 IS
1019 CURSOR c_user_status IS
1020 SELECT *
1021 FROM ams_user_statuses_vl
1022 WHERE user_status_id = p_user_status_rec.user_status_id;
1023 --
1024 -- This is the only exception for using %ROWTYPE.
1025 -- We are selecting from the VL view, which may
1026 -- have some denormalized columns as compared to
1027 -- the base tables.
1028 l_user_status_rec c_user_status%ROWTYPE;
1029 BEGIN
1030 x_complete_rec := p_user_status_rec;
1031 OPEN c_user_status;
1032 FETCH c_user_status INTO l_user_status_rec;
1033 IF c_user_status%NOTFOUND THEN
1034 CLOSE c_user_status;
1035 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1036 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1037 FND_MSG_PUB.add;
1038 END IF;
1039 RAISE FND_API.g_exc_error;
1040 END IF;
1041 CLOSE c_user_status;
1042 --
1043 -- SYSTEM_STATUS_TYPE
1044 IF p_user_status_rec.system_status_type = FND_API.g_miss_char THEN
1045 x_complete_rec.system_status_type := l_user_status_rec.system_status_type;
1046 END IF;
1047 -- SYSTEM_STATUS_CODE
1048 IF p_user_status_rec.system_status_code = FND_API.g_miss_char THEN
1049 x_complete_rec.system_status_code := l_user_status_rec.system_status_code;
1050 END IF;
1051 --
1052 -- DEFAULT_FLAG
1053 IF p_user_status_rec.default_flag = FND_API.g_miss_char THEN
1054 x_complete_rec.default_flag := l_user_status_rec.default_flag;
1055 END IF;
1056 --
1060 END IF;
1057 -- ENABLED_FLAG
1058 IF p_user_status_rec.enabled_flag = FND_API.g_miss_char THEN
1059 x_complete_rec.enabled_flag := l_user_status_rec.enabled_flag;
1061
1062 -- SEEDED_FLAG
1063 IF p_user_status_rec.seeded_flag = FND_API.g_miss_char THEN
1064 x_complete_rec.seeded_flag := l_user_status_rec.seeded_flag;
1065 END IF;
1066 --
1067 -- START_DATE_ACTIVE
1068 IF p_user_status_rec.start_date_active = FND_API.g_miss_date THEN
1069 x_complete_rec.start_date_active := l_user_status_rec.start_date_active;
1070 END IF;
1071 --
1072 -- END_DATE_ACTIVE
1073 IF p_user_status_rec.end_date_active = FND_API.g_miss_date THEN
1074 x_complete_rec.end_date_active := l_user_status_rec.end_date_active;
1075 END IF;
1076 --
1077 -- NAME
1078 IF p_user_status_rec.name = FND_API.g_miss_char THEN
1079 x_complete_rec.name := l_user_status_rec.name;
1080 END IF;
1081 --
1082 -- DESCRIPTION
1083 IF p_user_status_rec.description = FND_API.g_miss_char THEN
1084 x_complete_rec.description := l_user_status_rec.description;
1085 END IF;
1086 END Complete_User_Status_Rec;
1087 ---------------------------------------------------------
1088 -- Function Compare Columns
1089 -- added sugupta 05/22/2000
1090 -- this procedure will compare that no values have been modified for seeded statuses
1091 -----------------------------------------------------------------
1092 FUNCTION compare_columns(
1093 l_user_status_rec IN User_Status_Rec_Type
1094 )
1095 RETURN VARCHAR2
1096 IS
1097 l_count NUMBER := 0;
1098
1099 BEGIN
1100 IF (AMS_DEBUG_HIGH_ON) THEN
1101
1102 AMS_UTILITY_PVT.DEBUG_MESSAGE('sTART DATE:'|| to_char(l_user_status_rec.start_date_active,'DD_MON_YYYY'));
1103 END IF;
1104 IF (AMS_DEBUG_HIGH_ON) THEN
1105
1106 AMS_UTILITY_PVT.DEBUG_MESSAGE('end DATE:'|| to_char(l_user_status_rec.end_Date_active,'DD-MON-YYYY'));
1107 END IF;
1108
1109 if l_user_status_rec.start_date_active is NOT NULL then
1110 if l_user_status_rec.end_Date_active is NOT NULL then
1111
1112 BEGIN
1113 select 1 into l_count
1114 from ams_user_statuses_vl
1115 where user_status_id = l_user_status_rec.user_status_id
1116 and name = l_user_status_rec.name
1117 and start_date_active = l_user_status_rec.start_date_active
1118 and end_date_active = l_user_status_rec.end_Date_active
1119 and system_status_type = l_user_status_rec.system_status_type
1120 and system_status_code = l_user_status_rec.system_status_code
1121 and seeded_flag = 'Y';
1122 EXCEPTION
1123 WHEN NO_DATA_FOUND THEN
1124 l_count := 0;
1125 END;
1126 else -- for end date
1127 BEGIN
1128 select 1 into l_count
1129 from ams_user_statuses_vl
1130 where user_status_id = l_user_status_rec.user_status_id
1131 and name = l_user_status_rec.name
1132 and start_date_active = l_user_status_rec.start_date_active
1133 and system_status_type = l_user_status_rec.system_status_type
1134 and system_status_code = l_user_status_rec.system_status_code
1135 and seeded_flag = 'Y';
1136 EXCEPTION
1137 WHEN NO_DATA_FOUND THEN
1138 l_count := 0;
1139 END;
1140 end if; -- for end date
1141 else
1142 BEGIN
1143 select 1 into l_count
1144 from ams_user_statuses_vl
1145 where user_status_id = l_user_status_rec.user_status_id
1146 and name = l_user_status_rec.name
1147 and system_status_type = l_user_status_rec.system_status_type
1148 and system_status_code = l_user_status_rec.system_status_code
1149 and seeded_flag = 'Y';
1150 EXCEPTION
1151 WHEN NO_DATA_FOUND THEN
1152 l_count := 0;
1153 END;
1154 end if;
1155
1156 IF l_count = 0 THEN
1157 RETURN FND_API.g_false;
1158 ELSE
1159 RETURN FND_API.g_true;
1160 END IF;
1161 END compare_columns;
1162
1163 ---------------------------------------------------------
1164 -- Function seed_needs_update
1165 -- added sugupta 05/22/2000
1166 -- this procedure will look at enabled flag and determine if update is needed
1167 -- updated dcastlem 09/17/2001
1168 -- also looks at defualt flag
1169 -----------------------------------------------------------------
1170 FUNCTION seed_needs_update(
1171 l_user_status_rec IN User_Status_Rec_Type
1172 )
1173 RETURN VARCHAR2
1174 IS
1175 l_count NUMBER := 0;
1176
1177 BEGIN
1178 BEGIN
1179 select 1 into l_count
1180 from ams_user_statuses_vl
1181 where user_status_id = l_user_status_rec.user_status_id
1182 and enabled_flag = l_user_status_rec.enabled_flag
1183 and default_flag = l_user_status_rec.default_flag
1184 and seeded_flag = 'Y';
1185 EXCEPTION
1186 WHEN NO_DATA_FOUND THEN
1187 l_count := 0;
1188 END;
1189
1190 IF l_count = 0 THEN
1191 RETURN FND_API.g_true; -- needs update
1192 ELSE
1193 RETURN FND_API.g_false; -- doesnt need update
1194 END IF;
1195 END seed_needs_update;
1196
1197 -- Check_User_Status_Req_Items
1198 PROCEDURE Check_User_Status_Req_Items (
1199 p_user_status_rec IN User_Status_Rec_Type,
1200 x_return_status OUT NOCOPY VARCHAR2
1201 )
1202 IS
1203 BEGIN
1204 -- SYSTEM_STATUS_TYPE
1205 IF p_user_status_rec.system_status_type IS NULL THEN
1206 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1207 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_NO_STATUS_TYPE');
1208 FND_MSG_PUB.add;
1209 END IF;
1213 -- SYSTEM_STATUS_CODE
1210 x_return_status := FND_API.g_ret_sts_error;
1211 RETURN;
1212 END IF;
1214 IF p_user_status_rec.system_status_code IS NULL THEN
1215 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1216 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_NO_SYSTEM_STATUS');
1217 FND_MSG_PUB.add;
1218 END IF;
1219 x_return_status := FND_API.g_ret_sts_error;
1220 RETURN;
1221 END IF;
1222 -- NAME
1223 IF p_user_status_rec.name IS NULL THEN
1224 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1225 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_NO_NAME');
1226 FND_MSG_PUB.add;
1227 END IF;
1228 x_return_status := FND_API.g_ret_sts_error;
1229 RETURN;
1230 END IF;
1231
1232 --
1233 -- START_DATE_ACTIVE
1234 --IF p_user_status_rec.start_date_active IS NULL THEN
1235 -- IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1236 -- THEN
1237 -- FND_MESSAGE.set_name('AMS', 'AMS_STATUS_NO_ACTIVE_FROM');
1238 -- FND_MSG_PUB.add;
1239 -- END IF;
1240 --
1241 -- x_return_status := FND_API.g_ret_sts_error;
1242 -- RETURN;
1243 --END IF;
1244
1245
1246 END Check_User_Status_Req_Items;
1247 -- Check_User_Status_UK_Items
1248 PROCEDURE Check_User_Status_UK_Items (
1249 p_user_status_rec IN User_Status_Rec_Type,
1250 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1251 x_return_status OUT NOCOPY VARCHAR2
1252 )
1253 IS
1254 l_valid_flag VARCHAR2(1);
1255 BEGIN
1256 x_return_status := FND_API.g_ret_sts_success;
1257 -- MEDIA_ID
1258 -- For Create_User_Status, when ID is passed in, we need to
1259 -- check if this ID is unique.
1260 IF p_validation_mode = JTF_PLSQL_API.g_create
1261 AND p_user_status_rec.user_status_id IS NOT NULL
1262 THEN
1263 IF AMS_Utility_PVT.check_uniqueness(
1264 'ams_user_statuses_vl',
1265 'user_status_id = ' || p_user_status_rec.user_status_id
1266 ) = FND_API.g_false
1267 THEN
1268 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1269 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_DUP_USR_ST_ID');
1270 FND_MSG_PUB.add;
1271 END IF;
1272 x_return_status := FND_API.g_ret_sts_error;
1273 RETURN;
1274 END IF;
1275 END IF;
1276 -- check if NAME is UNIQUE
1277 -- modified sugupta 02/22/2000 UNIQUENESS OF NAME LIMITED TO THAT ACTIVITY TYPE
1278 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1279 l_valid_flag := AMS_Utility_PVT.check_uniqueness (
1280 'ams_user_statuses_vl',
1281 'name = ''' || p_user_status_rec.name ||
1282 ''' AND system_status_type = '''|| p_user_status_rec.system_status_type || ''''
1283 );
1284 ELSE
1285 l_valid_flag := AMS_Utility_PVT.check_uniqueness (
1286 'ams_user_statuses_vl',
1287 'name = ''' || p_user_status_rec.name ||
1288 ''' AND system_status_type = '''|| p_user_status_rec.system_status_type ||
1289 ''' AND user_status_id <> ' || p_user_status_rec.user_status_id
1290 );
1291 END IF;
1292 IF l_valid_flag = FND_API.g_false THEN
1293 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1294 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_DUP_NAME');
1295 FND_MSG_PUB.add;
1296 END IF;
1297 x_return_status := FND_API.g_ret_sts_error;
1298 RETURN;
1299 END IF;
1300 END Check_User_Status_UK_Items;
1301 -- Check_User_Status_FK_Items
1302 PROCEDURE Check_User_Status_FK_Items (
1303 p_user_status_rec IN User_Status_Rec_Type,
1304 x_return_status OUT NOCOPY VARCHAR2
1305 )
1306 IS
1307 BEGIN
1308 --
1309 -- What do we need to do about FKs between the
1310 -- B and TL tables?
1311 x_return_status := FND_API.g_ret_sts_success;
1312 END Check_User_Status_FK_Items;
1313 -- Check_User_Status_Lookup_Items
1314 PROCEDURE Check_User_Status_Lookup_Items (
1315 p_user_status_rec IN User_Status_Rec_Type,
1316 x_return_status OUT NOCOPY VARCHAR2
1317 )
1318 IS
1319 -- Shouldnt l_system_status_type be equal to p_user_status_rec.system_status_type
1320 l_system_status_type VARCHAR2(30);
1321 BEGIN
1322 x_return_status := FND_API.g_ret_sts_success;
1323 --
1324 -- SYSTEM_STATUS_TYPE
1325 l_system_status_type := p_user_status_rec.system_status_type;
1326 IF p_user_status_rec.system_status_code <> FND_API.g_miss_char THEN
1327 /* changed by BGEORGE 6/17/2002
1328 IF AMS_Utility_PVT.check_lookup_exists (
1329 p_lookup_type => l_system_status_type,
1330 p_lookup_code => p_user_status_rec.system_status_code
1331 ) = FND_API.g_false
1332 */
1333 IF AMS_Utility_PVT.check_lookup_exists(
1334 p_lookup_type => l_system_status_type,
1335 p_lookup_code => p_user_status_rec.system_status_code,
1336 p_view_application_id => fnd_global.resp_appl_id
1337 ) = FND_API.g_false
1338 THEN
1339 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1340 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_BAD_SYSTEM_STATUS');
1341 FND_MSG_PUB.add;
1342 END IF;
1343 x_return_status := FND_API.g_ret_sts_error;
1344 RETURN;
1345 END IF;
1346 END IF;
1347 END Check_User_Status_Lookup_Items;
1348 -- Check_User_Status_Flag_Items
1349 PROCEDURE Check_User_Status_Flag_Items (
1350 p_user_status_rec IN User_Status_Rec_Type,
1351 x_return_status OUT NOCOPY VARCHAR2
1352 )
1353 IS
1354 BEGIN
1355 x_return_status := FND_API.g_ret_sts_success;
1356 -- DEFAULT_FLAG
1357 IF p_user_status_rec.default_flag <> FND_API.g_miss_char AND p_user_status_rec.default_flag IS NOT NULL THEN
1358 IF AMS_Utility_PVT.is_Y_or_N (p_user_status_rec.default_flag) = FND_API.g_false THEN
1359 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1360 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_BAD_DEFAULT_FLAG');
1361 FND_MSG_PUB.add;
1362 END IF;
1363 x_return_status := FND_API.g_ret_sts_error;
1364 RETURN;
1365 END IF;
1366 END IF;
1367 -- ENABLED_FLAG
1368 IF p_user_status_rec.enabled_flag <> FND_API.g_miss_char AND p_user_status_rec.enabled_flag IS NOT NULL THEN
1369 IF AMS_Utility_PVT.is_Y_or_N (p_user_status_rec.enabled_flag) = FND_API.g_false THEN
1370 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1371 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_BAD_ENABLED_FLAG');
1372 FND_MSG_PUB.add;
1373 END IF;
1374 x_return_status := FND_API.g_ret_sts_error;
1375 RETURN;
1376 END IF;
1377 END IF;
1378 END Check_User_Status_Flag_Items;
1379
1380 -----------------------------------------------------------------
1381 FUNCTION can_disable_status(
1382 l_user_status_rec IN User_Status_Rec_Type
1383 )
1384 RETURN VARCHAR2
1385 IS
1386 l_count NUMBER := 0;
1387 l_system_status_type VARCHAR2(30);
1388 l_obj_type VARCHAR2(30);
1389 l_stmt VARCHAR2(1000);
1390 l_user_status_id NUMBER;
1391 l_table VARCHAR2(30);
1392
1393 BEGIN
1394 l_system_status_type := l_user_status_rec.system_status_type;
1395 l_user_status_id := l_user_status_rec.user_status_id;
1396 l_obj_type := substr(l_system_status_type, instr(l_system_status_type, '_', 1,1)+1, instr(l_system_status_type,'_',-1,1)-instr(l_system_status_type, '_', 1,1)-1);
1397
1398
1399 -- Not all these tables have an index on user_status_id
1400 -- In such cases, there may be performance hit.
1401
1402 IF l_obj_type IN ('CAMPAIGN','PROGRAM') THEN
1403 l_table := 'AMS_CAMPAIGNS_ALL_B';
1404
1405 ELSIF l_obj_type = 'CAMPAIGN_SCHEDULE' THEN
1406 l_table := 'AMS_CAMPAIGN_SCHEDULES_B';
1407
1408 ELSIF l_obj_type = 'EVENT' THEN
1409 l_table := 'AMS_EVENT_HEADERS_ALL_B'; -- Also AMS_EVENT_OFFERS_ALL_B
1410
1411 ELSIF l_obj_type = 'EVENT_REG' THEN
1412 l_table := 'AMS_EVENT_REGISTRATIONS';
1413
1414 ELSIF l_obj_type = 'EVENT_AGENDA' THEN
1415 l_table := 'AMS_ACT_RESOURCES';
1416
1417 ELSIF l_obj_type = 'DELIV' THEN
1418 l_table := 'AMS_DELIVERABLES_ALL_B';
1419
1420 ELSIF l_obj_type = 'PRICELIST' THEN
1421 l_table := 'OZF_PRICE_LIST_ATTRIBUTES';
1422
1423 ELSIF l_obj_type = 'BUDGETSOURCE' THEN
1424 l_table := 'OZF_ACT_BUDGETS';
1425
1426 ELSIF l_obj_type = 'FUND' THEN
1427 l_table := 'OZF_FUNDS_ALL_B';
1428
1429 ELSIF l_obj_type = 'DM_SCORE' THEN
1430 l_table := 'AMS_DM_SCORES_ALL_B';
1431
1432 ELSIF l_obj_type = 'DM_MODEL' THEN
1433 l_table := 'AMS_DM_MODELS_ALL_B';
1434
1435 ELSIF l_obj_type = 'CLAIM' THEN
1436 l_table := 'OZF_CLAIMS_ALL';
1437
1438 ELSIF l_obj_type = 'LIST' THEN
1439 l_table := 'AMS_LIST_HEADERS_ALL';
1440
1441 ELSIF l_obj_type = 'LIST_SEGMENT' THEN
1442 l_table := 'AMS_CELLS_ALL_B';
1443
1444 ELSIF l_obj_type = 'IMPORT' THEN
1445 l_table := 'AMS_IMP_LIST_HEADERS_ALL';
1446
1447 ELSIF l_obj_type = 'OFFER' THEN
1448 l_table := 'OZF_OFFERS';
1449
1450 END IF;
1451
1452 IF l_table IS NOT NULL THEN
1453 l_stmt := 'SELECT count(1) from '||l_table||' where user_status_id = :b1';
1454 EXECUTE IMMEDIATE l_stmt INTO l_count USING l_user_status_id;
1455
1456 IF l_obj_type = 'EVENT' and l_count = 0 THEN
1457 l_table := 'AMS_EVENT_OFFERS_ALL_B'; -- Already checked ams_event_headers_all_b
1458 l_stmt := 'SELECT count(1) from '||l_table||' where user_status_id = :b1';
1459 EXECUTE IMMEDIATE l_stmt INTO l_count USING l_user_status_id;
1460 END IF;
1461
1462 END IF;
1463
1464
1465 IF l_count = 0 THEN
1466 RETURN FND_API.g_true; -- can be disabled
1467 ELSE
1468 RETURN FND_API.g_false; -- cannot be disables
1469 END IF;
1470
1471 END can_disable_status;
1472
1473 END AMS_User_Statuses_PVT;