1 PACKAGE BODY Ams_Approvers_Pvt AS
2 /* $Header: amsvaprb.pls 120.0 2005/05/31 20:24:52 appldev noship $ */
3
4 -----------------------------------------------------------
5 -- PACKAGE
6 -- AMS_APPROVERS_PVT
7 --
8 --
9 -- PURPOSE
10 -- This package is a Private API for managing Approvers
11 -- in AMS. It contains specification for pl/sql records and tables
12 --
13 -- AMS_APPROVERS:
14 -- Create_approver (see below for specification)
15 -- Update_approver (see below for specification)
16 -- Delete_approver (see below for specification)
17 -- Lock_approver (see below for specification)
18 -- Validate_approver (see below for specification)
19 --
20 -- Check_Approvers_Items (see below for specification)
21 -- Check_Approvers_Record (see below for specification)
22 -- Init_Approvers_Rec
23 -- Complete_Approvers_Rec
24 --
25 -- NOTES
26 --
27 --
28 -- HISTORY
29 -- 24-OCT-2000 mukumar Created.
30 -- 09-APR-2002 vmodur Fix for Bug 2285556
31 -- Approver Dates were being validated even
32 -- when only the approver sequence was being changed
33 -- 12-SEP-2002 vmodur Changed l_meaning from Varchar2(30) to Varchar2(80)
34 -- for bug 2544992
35 -- 29-APR-2003 vmodur Bug 2898250 added check_func_use_valid
36 -- 14-NOV-2003 vmodur Bug 2677401 prevent addition or role with more than 1 user
37 -- 22-NOV-2004 vmodur Bug 3979814 Fix in 11.5.11
38 -- 24-MAR-2005 vmodur SQL Repository Fixes
39 -----------------------------------------------------------
40
41 -- Global CONSTANTS
42 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_APPROVERS_PVT';
43
44 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
45 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
46 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
47
48 FUNCTION compare_columns(
49 p_approvers_rec IN Approvers_Rec_Type
50 ) RETURN VARCHAR2; -- FND_API.g_true/g_false
51
52 FUNCTION seed_needs_update(
53 p_approvers_rec IN Approvers_Rec_Type
54 ) RETURN VARCHAR2; -- FND_API.g_true/g_false
55
56 -- Check_Approvers_Req_Items
57 PROCEDURE Check_Approvers_Req_Items (
58 p_approvers_rec IN Approvers_Rec_Type,
59 x_return_status OUT NOCOPY VARCHAR2
60 );
61 -- Check_Approvers_UK_Items
62 PROCEDURE Check_Approvers_UK_Items (
63 p_approvers_rec IN Approvers_Rec_Type,
64 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
65 x_return_status OUT NOCOPY VARCHAR2
66 );
67 -- Check_Approvers_FK_Items
68 PROCEDURE Check_Approvers_FK_Items (
69 p_approvers_rec IN Approvers_Rec_Type,
70 x_return_status OUT NOCOPY VARCHAR2
71 );
72 -- Check_Approvers_Lkup_Items
73 PROCEDURE Check_Approvers_Lkup_Items (
74 p_approvers_rec IN Approvers_Rec_Type,
75 x_return_status OUT NOCOPY VARCHAR2
76 );
77
78 -- Check_Approvers_Flag_Items
79 PROCEDURE Check_Approvers_Flag_Items (
80 p_approvers_rec IN Approvers_Rec_Type,
81 x_return_status OUT NOCOPY VARCHAR2
82 );
83
84 -- Check Aprrover Dates
85 PROCEDURE Check_Dates_Create_Range (
86 p_approvers_rec IN Approvers_Rec_Type,
87 x_return_status OUT NOCOPY VARCHAR2
88 );
89
90 PROCEDURE Check_Dates_Update_Range (
91 p_approvers_rec IN Approvers_Rec_Type,
92 x_return_status OUT NOCOPY VARCHAR2
93 );
94
95 PROCEDURE Check_Func_Use_Valid(
96 p_approvers_rec IN Approvers_Rec_Type,
97 x_return_status OUT NOCOPY VARCHAR2
98 );
99 --------------------------------------------------------------------
100 -- PROCEDURE
101 -- Create_Approvers
102 --
103 -- PURPOSE
104 -- Create Approvers entry.
105 --
106 -- PARAMETERS
107 -- p_approvers_rec: the record representing AMS_APPROVER .
108 -- x_approver_id: the approver_id.
109 --
110 -- NOTES
111 -- 1. object_version_number will be set to 1.
112 -- 2. If approver_id is passed in, the uniqueness will be checked.
113 -- Raise exception in case of duplicates.
114 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
115 -- Raise exception for invalid flag.
116 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
117 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
118 --------------------------------------------------------------------
119 PROCEDURE Create_approvers (
120 p_api_version IN NUMBER,
121 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
122 p_commit IN VARCHAR2 := Fnd_Api.g_false,
123 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
124
125 x_return_status OUT NOCOPY VARCHAR2,
126 x_msg_count OUT NOCOPY NUMBER,
127 x_msg_data OUT NOCOPY VARCHAR2,
128
129 p_approvers_rec IN Approvers_Rec_Type,
130 x_approver_id OUT NOCOPY NUMBER
131 )
132 IS
133
134 L_API_VERSION CONSTANT NUMBER := 1.0;
135 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Approvers';
136 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
137
138 l_approvers_rec Approvers_Rec_Type := p_approvers_rec;
139 l_dummy NUMBER;
140 l_return_status VARCHAR2(1);
141 l_row_id VARCHAR2(40);
142
143 CURSOR c_seq IS
144 SELECT ams_approvers_s.NEXTVAL
145 FROM dual;
146
147 CURSOR c_id_exists (x_id IN NUMBER) IS
148 SELECT 1 FROM dual
149 WHERE EXISTS (SELECT 1 FROM ams_approvers
150 WHERE approver_id = x_id);
151 BEGIN
152 --------------------- initialize -----------------------
153 SAVEPOINT Create_Approvers;
154 IF (AMS_DEBUG_HIGH_ON) THEN
155
156 Ams_Utility_Pvt.debug_message (l_full_name || ': Start');
157 END IF;
158 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
159 Fnd_Msg_Pub.initialize;
160 END IF;
161 IF NOT Fnd_Api.compatible_api_call (
162 L_API_VERSION,
163 p_api_version,
164 L_API_NAME,
165 G_PKG_NAME
166 ) THEN
167 RAISE Fnd_Api.g_exc_unexpected_error;
168 END IF;
169 x_return_status := Fnd_Api.g_ret_sts_success;
170 ----------------------- validate -----------------------
171 IF (AMS_DEBUG_HIGH_ON) THEN
172
173 Ams_Utility_Pvt.debug_message (l_full_name || ': Validate');
174 END IF;
175 Validate_approvers (
176 p_api_version => L_API_VERSION,
177 p_init_msg_list => p_init_msg_list,
181 x_msg_count => x_msg_count,
178 p_commit => p_commit,
179 p_validation_level => p_validation_level,
180 x_return_status => l_return_status,
182 x_msg_data => x_msg_data,
183 p_approvers_rec => l_approvers_rec
184 );
185 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
186 RAISE Fnd_Api.g_exc_error;
187 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
188 RAISE Fnd_Api.g_exc_unexpected_error;
189 END IF;
190 --
191 -- Check for the ID.
192 --
193 IF l_approvers_rec.approver_id IS NULL THEN
194 LOOP
195 --
196 -- If the ID is not passed into the API, then
197 -- grab a value from the sequence.
198 OPEN c_seq;
199 FETCH c_seq INTO l_approvers_rec.approver_id;
200 CLOSE c_seq;
201 --
202 -- Check to be sure that the sequence does not exist.
203 OPEN c_id_exists (l_approvers_rec.approver_id);
204 FETCH c_id_exists INTO l_dummy;
205 CLOSE c_id_exists;
206 --
207 -- If the value for the ID already exists, then
208 -- l_dummy would be populated with '1', otherwise,
209 -- it receives NULL.
210
211 EXIT WHEN l_dummy IS NULL;
212 END LOOP;
213 END IF;
214 -------------------------- insert --------------------------
215 IF (AMS_DEBUG_HIGH_ON) THEN
216
217 Ams_Utility_Pvt.debug_message (l_full_name || ': Insert');
218 END IF;
219 --
220 -- Insert into mutli-language supported table.
221 --
222 Ams_Approvers_Pkg.INSERT_ROW (
223 X_ROWID => l_row_id,
224 X_APPROVER_ID => l_approvers_rec.approver_id,
225 X_SEEDED_FLAG => NVL(l_approvers_rec.SEEDED_FLAG, 'N'),
226 X_ACTIVE_FLAG => NVL(l_approvers_rec.ACTIVE_FLAG, 'Y'),
227 X_START_DATE_ACTIVE => l_approvers_rec.START_DATE_ACTIVE,
228 X_END_DATE_ACTIVE => l_approvers_rec.END_DATE_ACTIVE,
229 X_OBJECT_VERSION_NUMBER => 1, --l_approvers_rec.l_obj_verno,
230 --X_SECURITY_GROUP_ID => l_approvers_rec.SECURITY_GROUP_ID,
231 X_AMS_APPROVAL_DETAIL_ID => l_approvers_rec.AMS_APPROVAL_DETAIL_ID,
232 X_APPROVER_SEQ => l_approvers_rec.APPROVER_SEQ,
233 X_APPROVER_TYPE => l_approvers_rec.APPROVER_TYPE,
234 X_OBJECT_APPROVER_ID => l_approvers_rec.OBJECT_APPROVER_ID,
235 X_NOTIFICATION_TYPE => l_approvers_rec.NOTIFICATION_TYPE,
236 X_NOTIFICATION_TIMEOUT => l_approvers_rec.NOTIFICATION_TYPE,
237 X_CREATION_DATE => SYSDATE,
238 X_CREATED_BY => Fnd_Global.User_Id,
239 X_LAST_UPDATE_DATE => SYSDATE,
240 X_LAST_UPDATED_BY => Fnd_Global.User_Id,
241 X_LAST_UPDATE_LOGIN => Fnd_Global.Conc_Login_Id
242 ) ;
243 -- set OUT value
244 x_approver_id := l_approvers_rec.approver_id;
245 --
246 -- END of API body.
247 --
248 -- Standard check of p_commit.
249 IF Fnd_Api.To_Boolean ( p_commit ) THEN
250 COMMIT WORK;
251 END IF;
252 Fnd_Msg_Pub.count_and_get(
253 p_encoded => Fnd_Api.g_false,
254 p_count => x_msg_count,
255 p_data => x_msg_data
256 );
257 IF (AMS_DEBUG_HIGH_ON) THEN
258
259 Ams_Utility_Pvt.debug_message (l_full_name || ': End');
260 END IF;
261
262
263 EXCEPTION
264 WHEN Fnd_Api.g_exc_error THEN
265 ROLLBACK TO Create_Approvers;
266 x_return_status := Fnd_Api.g_ret_sts_error;
267 Fnd_Msg_Pub.count_and_get(
268 p_encoded => Fnd_Api.g_false,
269 p_count => x_msg_count,
270 p_data => x_msg_data
271 );
272 WHEN Fnd_Api.g_exc_unexpected_error THEN
273 ROLLBACK TO Create_Approvers;
274 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
275 Fnd_Msg_Pub.count_and_get (
276 p_encoded => Fnd_Api.g_false,
277 p_count => x_msg_count,
278 p_data => x_msg_data
279 );
280 WHEN OTHERS THEN
281 ROLLBACK TO Create_Approvers;
282 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
283 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
284 THEN
285 Fnd_Msg_Pub.add_exc_msg (g_pkg_name, l_api_name);
286 END IF;
287 Fnd_Msg_Pub.count_and_get (
288 p_encoded => Fnd_Api.g_false,
289 p_count => x_msg_count,
290 p_data => x_msg_data
291 );
292 END Create_approvers;
293 --------------------------------------------------------------------
294 -- PROCEDURE
295 -- Update_approvers
296 --
297 -- PURPOSE
298 -- Update an approvers entry.
299 --
300 -- PARAMETERS
301 -- p_approvers_rec: the record representing AMS_APPROVERS (without the ROW_ID column).
302 --
303 -- NOTES
304 -- 1. Raise exception if the object_version_number doesn't match.
305 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
306 -- that column won't be updated.
307 --------------------------------------------------------------------
308 PROCEDURE Update_approvers (
309 p_api_version IN NUMBER,
310 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
311 p_commit IN VARCHAR2 := Fnd_Api.g_false,
312 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
313
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_count OUT NOCOPY NUMBER,
316 x_msg_data OUT NOCOPY VARCHAR2,
317
318 p_approvers_rec IN Approvers_Rec_Type
319 )
320 IS
321
322 L_API_VERSION CONSTANT NUMBER := 1.0;
323 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Approvers';
324 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
325
326 l_approvers_rec Approvers_Rec_Type := p_approvers_rec;
327 l_dummy NUMBER;
328 l_return_status VARCHAR2(1);
329
330 BEGIN
331 --------------------- initialize -----------------------
332 SAVEPOINT Update_approvers;
333 IF (AMS_DEBUG_HIGH_ON) THEN
334
335 Ams_Utility_Pvt.debug_message (l_full_name || ': Start');
336 END IF;
337 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
338 Fnd_Msg_Pub.initialize;
339 END IF;
340 IF NOT Fnd_Api.compatible_api_call(
341 l_api_version,
342 p_api_version,
343 l_api_name,
344 g_pkg_name
345 ) THEN
346 RAISE Fnd_Api.g_exc_unexpected_error;
347 END IF;
348 x_return_status := Fnd_Api.g_ret_sts_success;
349
350 ----------------------- validate ----------------------
351 IF (AMS_DEBUG_HIGH_ON) THEN
352
353 Ams_Utility_Pvt.debug_message (l_full_name || ': Validate');
354 END IF;
355 -- replace g_miss_char/num/date with current column values
356 Complete_approvers_Rec(p_approvers_rec,l_approvers_rec);
357 IF l_approvers_rec.seeded_flag = 'Y' THEN
358 IF compare_columns(l_approvers_rec) = Fnd_Api.g_false THEN
359 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
360 Fnd_Message.set_name ('AMS', 'AMS_STATUS_SEED_DATA');
361 Fnd_Msg_Pub.ADD;
362 END IF;
363 RAISE Fnd_Api.g_exc_error;
364 END IF;
365 ELSE
366 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
367 Check_approvers_Items (
368 p_approvers_rec => l_approvers_rec ,
369 p_validation_mode => Jtf_Plsql_Api.g_update,
370 x_return_status => l_return_status
371 );
372 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
373 RAISE Fnd_Api.g_exc_unexpected_error;
374 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
375 RAISE Fnd_Api.g_exc_error;
376 END IF;
377 END IF;
378
379
380 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_record THEN
381 Check_approvers_Record (
382 p_approvers_rec => l_approvers_rec,
383 p_complete_rec => l_approvers_rec,
384 x_return_status => l_return_status
385 );
386 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
387 RAISE Fnd_Api.g_exc_unexpected_error;
388 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
389 RAISE Fnd_Api.g_exc_error;
390 END IF;
391 END IF;
392 END IF; -- check for seeded flag
393 -- Check to see if the row is seeded if the row is seeded then can't update
394 -- modified.. enabled flag for seeded rows can be updated.. added seed_needs_ -- update function
395 IF l_approvers_rec.seeded_flag='N'
396 OR seed_needs_update(l_approvers_rec) = Fnd_Api.g_true
397 THEN
398 -------------------------- update --------------------
399 IF (AMS_DEBUG_HIGH_ON) THEN
400
401 Ams_Utility_Pvt.debug_message (l_full_name || ': Update');
402 END IF;
403 Ams_Approvers_Pkg.UPDATE_ROW(
404 X_APPROVER_ID => l_approvers_rec.approver_id,
405 X_SEEDED_FLAG => l_approvers_rec.SEEDED_FLAG,
406 X_ACTIVE_FLAG => l_approvers_rec.ACTIVE_FLAG,
407 X_START_DATE_ACTIVE => l_approvers_rec.START_DATE_ACTIVE,
408 X_END_DATE_ACTIVE => l_approvers_rec.END_DATE_ACTIVE,
409 X_OBJECT_VERSION_NUMBER => l_approvers_rec.OBJECT_VERSION_NUMBER+1,
410 --X_SECURITY_GROUP_ID => l_approvers_rec.SECURITY_GROUP_ID,
414 X_OBJECT_APPROVER_ID => l_approvers_rec.OBJECT_APPROVER_ID,
411 X_AMS_APPROVAL_DETAIL_ID => l_approvers_rec.AMS_APPROVAL_DETAIL_ID,
412 X_APPROVER_SEQ => l_approvers_rec.APPROVER_SEQ,
413 X_APPROVER_TYPE => l_approvers_rec.APPROVER_TYPE,
415 X_NOTIFICATION_TYPE => l_approvers_rec.NOTIFICATION_TYPE,
416 X_NOTIFICATION_TIMEOUT => l_approvers_rec.NOTIFICATION_TIMEOUT,
417 X_LAST_UPDATE_DATE => SYSDATE,
418 X_LAST_UPDATED_BY => Fnd_Global.User_Id,
419 X_LAST_UPDATE_LOGIN => Fnd_Global.Conc_Login_Id
420 );
421 END IF;-- ending if loop for second seeded_flag check
422 -------------------- finish --------------------------
423 IF Fnd_Api.to_boolean (p_commit) THEN
424 COMMIT;
425 END IF;
426 Fnd_Msg_Pub.count_and_get (
427 p_encoded => Fnd_Api.g_false,
428 p_count => x_msg_count,
429 p_data => x_msg_data
430 );
431 IF (AMS_DEBUG_HIGH_ON) THEN
432
433 Ams_Utility_Pvt.debug_message (l_full_name || ': End');
434 END IF;
435
436
437 EXCEPTION
438 WHEN Fnd_Api.g_exc_error THEN
439 ROLLBACK TO Update_approvers;
440 x_return_status := Fnd_Api.g_ret_sts_error;
441 Fnd_Msg_Pub.count_and_get (
442 p_encoded => Fnd_Api.g_false,
443 p_count => x_msg_count,
444 p_data => x_msg_data
445 );
446
447 WHEN Fnd_Api.g_exc_unexpected_error THEN
448 ROLLBACK TO Update_approvers;
449 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
450 Fnd_Msg_Pub.count_and_get (
451 p_encoded => Fnd_Api.g_false,
452 p_count => x_msg_count,
453 p_data => x_msg_data
454 );
455
456 WHEN OTHERS THEN
457 ROLLBACK TO Update_approvers;
458 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
459 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
460 THEN
461 Fnd_Msg_Pub.add_exc_msg (g_pkg_name, l_api_name);
462 END IF;
463 Fnd_Msg_Pub.count_and_get (
464 p_encoded => Fnd_Api.g_false,
465 p_count => x_msg_count,
466 p_data => x_msg_data
467 );
468 END Update_approvers;
469
470 --------------------------------------------------------------------
471 -- PROCEDURE
472 -- Delete_approvers
473 --
474 -- PURPOSE
475 -- Delete a approvers entry.
476 --
477 -- PARAMETERS
478 -- p_approver_id: the approver_id
479 -- p_object_version: the object_version_number
480 --
481 -- ISSUES
482 --
483 -- NOTES
484 -- 1. Raise exception if the object_version_number doesn't match.
485 --------------------------------------------------------------------
486 PROCEDURE Delete_approvers (
487 p_api_version IN NUMBER,
488 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
489 p_commit IN VARCHAR2 := Fnd_Api.g_false,
490 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
491
492 x_return_status OUT NOCOPY VARCHAR2,
493 x_msg_count OUT NOCOPY NUMBER,
494 x_msg_data OUT NOCOPY VARCHAR2,
495
496 p_approver_id IN NUMBER,
497 p_object_version IN NUMBER
498 ) IS
499
500 CURSOR c_approvers IS
501 SELECT *
502 FROM ams_approvers
503 WHERE approver_id = p_approver_id;
504 --
505 -- This is the only exception for using %ROWTYPE.
506 -- We are selecting from the VL view, which may
507 -- have some denormalized columns as compared to
508 -- the base tables.
509
510 l_approvers_rec c_approvers%ROWTYPE;
511 l_api_version CONSTANT NUMBER := 1.0;
512 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Approvers';
513 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
514
515 BEGIN
516
517 OPEN c_approvers;
518 FETCH c_approvers INTO l_approvers_rec;
519 IF c_approvers%NOTFOUND THEN
520 CLOSE c_approvers;
521 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
522 Fnd_Message.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
523 Fnd_Msg_Pub.ADD;
524 END IF;
525 RAISE Fnd_Api.g_exc_error;
526 END IF;
527 CLOSE c_approvers;
528 --------------------- initialize -----------------------
529 SAVEPOINT Delete_approvers;
530 IF (AMS_DEBUG_HIGH_ON) THEN
531
532 Ams_Utility_Pvt.debug_message (l_full_name || ': Start');
533 END IF;
534 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
535 Fnd_Msg_Pub.initialize;
536 END IF;
537 IF NOT Fnd_Api.compatible_api_call (
538 l_api_version,
539 p_api_version,
540 l_api_name,
541 g_pkg_name
542 ) THEN
543 RAISE Fnd_Api.g_exc_unexpected_error;
544 END IF;
545 x_return_status := Fnd_Api.g_ret_sts_success;
546
547 ------------------------ delete ------------------------
548 IF (AMS_DEBUG_HIGH_ON) THEN
549
550 Ams_Utility_Pvt.debug_message (l_full_name || ': Delete');
551 END IF;
552 -- Delete TL data
553 IF l_approvers_rec.seeded_flag='N'
554 THEN
555 Ams_Approvers_Pkg.DELETE_ROW (p_approver_id);
556 ELSE
557 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
558 Fnd_Message.set_name ('AMS', 'AMS_API_SEED_DATA');
559 Fnd_Msg_Pub.ADD;
560 RAISE Fnd_Api.g_exc_error;
561 END IF;
562 END IF;
563 -------------------- finish --------------------------
567 Fnd_Msg_Pub.count_and_get (
564 IF Fnd_Api.to_boolean (p_commit) THEN
565 COMMIT;
566 END IF;
568 p_encoded => Fnd_Api.g_false,
569 p_count => x_msg_count,
570 p_data => x_msg_data
571 );
572 IF (AMS_DEBUG_HIGH_ON) THEN
573
574 Ams_Utility_Pvt.debug_message (l_full_name || ': End');
575 END IF;
576
577
578 EXCEPTION
579 WHEN Fnd_Api.g_exc_error THEN
580 ROLLBACK TO Delete_approvers;
581 x_return_status := Fnd_Api.g_ret_sts_error;
582 Fnd_Msg_Pub.count_and_get (
583 p_encoded => Fnd_Api.g_false,
584 p_count => x_msg_count,
585 p_data => x_msg_data
586 );
587
588 WHEN Fnd_Api.g_exc_unexpected_error THEN
589 ROLLBACK TO Delete_approvers;
590 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
591 Fnd_Msg_Pub.count_and_get (
592 p_encoded => Fnd_Api.g_false,
593 p_count => x_msg_count,
594 p_data => x_msg_data
595 );
596
597 WHEN OTHERS THEN
598 ROLLBACK TO Delete_approvers;
599 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
600 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
601 THEN
602 Fnd_Msg_Pub.add_exc_msg (g_pkg_name, l_api_name);
603 END IF;
604 Fnd_Msg_Pub.count_and_get (
605 p_encoded => Fnd_Api.g_false,
606 p_count => x_msg_count,
607 p_data => x_msg_data
608 );
609 END Delete_approvers;
610
611 --------------------------------------------------------------------
612 -- PROCEDURE
613 -- Lock_approvers
614 --
615 -- PURPOSE
616 -- Lock a approval entry.
617 --
618 -- PARAMETERS
619 -- p_approver_id: the approvers
620 -- p_object_version: the object_version_number
621 --
622 -- ISSUES
623 --
624 -- NOTES
625 -- 1. Raise exception if the object_version_number doesn't match.
626 --------------------------------------------------------------------
627 PROCEDURE Lock_approvers (
628 p_api_version IN NUMBER,
629 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
630 p_commit IN VARCHAR2 := Fnd_Api.g_false,
631 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
632
633 x_return_status OUT NOCOPY VARCHAR2,
634 x_msg_count OUT NOCOPY NUMBER,
635 x_msg_data OUT NOCOPY VARCHAR2,
636
637 p_approver_id IN NUMBER,
638 p_object_version IN NUMBER
639 ) IS
640 BEGIN
641 NULL;
642 END;
643
644 --------------------------------------------------------------------
645 -- PROCEDURE
646 -- Validate_approvers
647 --
648 -- PURPOSE
649 -- Validate a approvers entry.
650 --
651 -- PARAMETERS
652 -- p_approvers_rec: the record representing AMS_APPROVERS (without ROW_ID).
653 --
654 -- NOTES
655 -- 1. p_approvers_rec should be the complete approvers record.
656 -- There should not be any FND_API.g_miss_char/num/date in it.
657 -- 2. If FND_API.g_miss_char/num/date is in the record, then raise
658 -- an exception, as those values are not handled.
659 --------------------------------------------------------------------
660 PROCEDURE Validate_approvers (
661 p_api_version IN NUMBER,
662 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
663 p_commit IN VARCHAR2 := Fnd_Api.g_false,
664 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
665
666 x_return_status OUT NOCOPY VARCHAR2,
667 x_msg_count OUT NOCOPY NUMBER,
668 x_msg_data OUT NOCOPY VARCHAR2,
669
670 p_approvers_rec IN Approvers_Rec_Type
671 ) IS
672 L_API_VERSION CONSTANT NUMBER := 1.0;
673 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_approvers';
674 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
675 l_return_status VARCHAR2(1);
676
677 BEGIN
678
679 --------------------- initialize -----------------------
680 IF (AMS_DEBUG_HIGH_ON) THEN
681
682 Ams_Utility_Pvt.debug_message (l_full_name || ': Start');
683 END IF;
684 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
685 Fnd_Msg_Pub.initialize;
686 END IF;
687 IF NOT Fnd_Api.compatible_api_call (
688 l_api_version,
689 p_api_version,
690 l_api_name,
691 g_pkg_name
692 ) THEN
693 RAISE Fnd_Api.g_exc_unexpected_error;
694 END IF;
695 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
696
697 ---------------------- validate ------------------------
698 IF (AMS_DEBUG_HIGH_ON) THEN
699
700 Ams_Utility_Pvt.debug_message (l_full_name || ': Check items');
701 END IF;
702 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
703 Check_Approvers_Items (
704 p_approvers_rec => p_approvers_rec,
705 p_validation_mode => Jtf_Plsql_Api.g_create,
706 x_return_status => l_return_status
707 );
708 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
709 RAISE Fnd_Api.g_exc_unexpected_error;
710 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
711 RAISE Fnd_Api.g_exc_error;
712 END IF;
713 END IF;
714 IF (AMS_DEBUG_HIGH_ON) THEN
715
716 Ams_Utility_Pvt.debug_message (l_full_name || ': Check record');
717 END IF;
718 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_record THEN
719 Check_Approvers_Record (
720 p_approvers_rec => p_approvers_rec,
721 p_complete_rec => NULL,
722 x_return_status => l_return_status
726 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
723 );
724 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
725 RAISE Fnd_Api.g_exc_unexpected_error;
727 RAISE Fnd_Api.g_exc_error;
728 END IF;
729 END IF;
730
731
732
733
734 -------------------- finish --------------------------
735 Fnd_Msg_Pub.count_and_get (
736 p_encoded => Fnd_Api.g_false,
737 p_count => x_msg_count,
738 p_data => x_msg_data
739 );
740 IF (AMS_DEBUG_HIGH_ON) THEN
741
742 Ams_Utility_Pvt.debug_message (l_full_name || ': End');
743 END IF;
744 EXCEPTION
745 WHEN Fnd_Api.g_exc_error THEN
746 x_return_status := Fnd_Api.g_ret_sts_error;
747 Fnd_Msg_Pub.count_and_get (
748 p_encoded => Fnd_Api.g_false,
749 p_count => x_msg_count,
750 p_data => x_msg_data
751 );
752 WHEN Fnd_Api.g_exc_unexpected_error THEN
753 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
754 Fnd_Msg_Pub.count_and_get (
755 p_encoded => Fnd_Api.g_false,
756 p_count => x_msg_count,
757 p_data => x_msg_data
758 );
759 WHEN OTHERS THEN
760 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
761 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
762 THEN
763 Fnd_Msg_Pub.add_exc_msg (g_pkg_name, l_api_name);
764 END IF;
765 Fnd_Msg_Pub.count_and_get (
766 p_encoded => Fnd_Api.g_false,
767 p_count => x_msg_count,
768 p_data => x_msg_data
769 );
770
771 END Validate_approvers;
772
773 ---------------------------------------------------------------------
774 -- PROCEDURE
775 -- Check_approvers_Items
776 --
777 -- PURPOSE
778 -- Perform the item level checking including unique keys,
779 -- required columns, foreign keys, domain constraints.
780 --
781 -- PARAMETERS
782 -- p_approvers_rec: the record to be validated
783 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
784 ---------------------------------------------------------------------
785 PROCEDURE Check_approvers_Items (
786 p_approvers_rec IN Approvers_Rec_Type,
787 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
788 x_return_status OUT NOCOPY VARCHAR2
789 ) IS
790 BEGIN
791 --
792 -- Validate required items.
793 Check_approvers_Req_Items (
794 p_approvers_rec => p_approvers_rec,
795 x_return_status => x_return_status
796 );
797 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
798 RETURN;
799 END IF;
800 --
801 -- Validate uniqueness.
802 Check_approvers_UK_Items (
803 p_approvers_rec => p_approvers_rec,
804 p_validation_mode => p_validation_mode,
805 x_return_status => x_return_status
806 );
807 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
808 RETURN;
809 END IF;
810
811 Check_approvers_FK_Items(
812 p_approvers_rec => p_approvers_rec,
813 x_return_status => x_return_status
814 );
815
816 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
817 RETURN;
818 END IF;
819 Check_approvers_Lkup_Items (
820 p_approvers_rec => p_approvers_rec,
821 x_return_status => x_return_status
822 );
823 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
824 RETURN;
825 END IF;
826 Check_approvers_Flag_Items(
827 p_approvers_rec => p_approvers_rec,
828 x_return_status => x_return_status
829 );
830 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
831 RETURN;
832 END IF;
833 IF (p_approvers_rec.start_date_active > p_approvers_rec.end_date_active) THEN
834 --dbms_output.put_line('st > ed ');
835 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
836 Fnd_Message.set_name('AMS', 'AMS_APPR_DTL_SD_BFR_ED');
837 Fnd_Msg_Pub.ADD;
838 x_return_status := Fnd_Api.g_ret_sts_error;
839 RETURN;
840 END IF;
841 END IF;
842 END Check_approvers_Items;
843
844 ---------------------------------------------------------------------
845 -- PROCEDURE
846 -- Check_approvers_Record
847 --
848 -- PURPOSE
849 -- Check the record level business rules.
850 --
851 -- PARAMETERS
852 -- p_approvers_rec: the record to be validated; may contain attributes
853 -- as FND_API.g_miss_char/num/date
854 -- p_complete_rec: the complete record after all "g_miss" items
855 -- have been replaced by current database values
856 ---------------------------------------------------------------------
857 PROCEDURE Check_approvers_Record (
858 p_approvers_rec IN Approvers_Rec_Type,
859 p_complete_rec IN Approvers_Rec_Type := NULL,
860 x_return_status OUT NOCOPY VARCHAR2
861 ) IS
862 l_start_date_active DATE;
863 l_end_date_active DATE;
864 BEGIN
865 --
866 -- Use local vars to reduce amount of typing.
867 IF p_complete_rec.start_date_active IS NOT NULL THEN
868 l_start_date_active := p_complete_rec.start_date_active;
869 ELSE
870 IF p_approvers_rec.start_date_active IS NOT NULL AND
871 p_approvers_rec.start_date_active <> Fnd_Api.g_miss_date THEN
872 l_start_date_active := p_approvers_rec.start_date_active;
873 END IF;
874 END IF;
878 IF p_approvers_rec.end_date_active IS NOT NULL AND
875 IF p_complete_rec.end_date_active IS NOT NULL THEN
876 l_end_date_active := p_complete_rec.end_date_active;
877 ELSE
879 p_approvers_rec.end_date_active <> Fnd_Api.g_miss_date THEN
880 l_end_date_active := p_approvers_rec.end_date_active;
881 END IF;
882 END IF;
883 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
884 IF l_start_date_active IS NOT NULL AND l_end_date_active IS NOT NULL THEN
885 IF l_start_date_active > l_end_date_active THEN
886 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
887 Fnd_Message.set_name ('AMS', 'AMS_APPR_APRVR_SD_BFR_ED');
888 Fnd_Msg_Pub.ADD;
889 END IF;
890 x_return_status := Fnd_Api.g_ret_sts_error;
891 RETURN;
892 END IF;
893 END IF;
894 END Check_approvers_Record;
895 ---------------------------------------------------------------------
896 -- PROCEDURE
897 -- Init_approvers_Rec
898 --
899 -- PURPOSE
900 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
901 ---------------------------------------------------------------------
902 PROCEDURE Init_approvers_Rec (
903 x_approvers_rec OUT NOCOPY Approvers_Rec_Type
904 ) IS
905 BEGIN
906 x_approvers_rec.approver_id := Fnd_Api.g_miss_num;
907 x_approvers_rec.start_date_active := Fnd_Api.g_miss_date;
908 x_approvers_rec.end_date_active := Fnd_Api.g_miss_date;
909 x_approvers_rec.object_version_number := Fnd_Api.g_miss_num;
910 --x_approvers_rec.security_group_id := Fnd_Api.g_miss_num;
911 x_approvers_rec.ams_approval_detail_id := Fnd_Api.g_miss_num;
912 x_approvers_rec.approver_seq := Fnd_Api.g_miss_num;
913 x_approvers_rec.approver_type := Fnd_Api.g_miss_char;
914 x_approvers_rec.object_approver_id := Fnd_Api.g_miss_num;
915 x_approvers_rec.notification_type := Fnd_Api.g_miss_char;
916 x_approvers_rec.notification_timeout := Fnd_Api.g_miss_num;
917 x_approvers_rec.seeded_flag := Fnd_Api.g_miss_char;
918 END;
919
920 ---------------------------------------------------------------------
921 -- PROCEDURE
922 -- Complete_approvers_Rec
923 --
924 -- PURPOSE
925 -- For Update_approvers, some attributes may be passed in as
926 -- FND_API.g_miss_char/num/date if the user doesn't want to
927 -- update those attributes. This procedure will replace the
928 -- "g_miss" attributes with current database values.
929 -- change g_miss to null VM 12-29-2002
930 -- PARAMETERS
931 -- p_approvers_rec: the record which may contain attributes as
932 -- null
933 -- x_complete_rec: the complete record after all null items
934 -- have been replaced by current database values
935 ---------------------------------------------------------------------
936 PROCEDURE Complete_approvers_Rec (
937 p_approvers_rec IN Approvers_Rec_Type,
938 x_complete_rec OUT NOCOPY Approvers_Rec_Type
939 ) IS
940 CURSOR c_approvers IS
941 SELECT *
942 FROM ams_approvers
943 WHERE approver_id = p_approvers_rec.approver_id;
944 --
945 -- This is the only exception for using %ROWTYPE.
946 -- We are selecting from the VL view, which may
947 -- have some denormalized columns as compared to
948 -- the base tables.
949 l_approvers_rec c_approvers%ROWTYPE;
950 BEGIN
951 x_complete_rec := p_approvers_rec;
952 OPEN c_approvers;
953 FETCH c_approvers INTO l_approvers_rec;
954 IF c_approvers%NOTFOUND THEN
955 CLOSE c_approvers;
956 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
957 Fnd_Message.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
958 Fnd_Msg_Pub.ADD;
959 END IF;
960 RAISE Fnd_Api.g_exc_error;
961 END IF;
962 CLOSE c_approvers;
963 --
964 IF p_approvers_rec.approver_id is null THEN
965 x_complete_rec.approver_id := l_approvers_rec.approver_id;
966 END IF;
967 /*
968 -- Don't replace null date with date from db
969 IF p_approvers_rec.start_date_active is null THEN
970 x_complete_rec.start_date_active := l_approvers_rec.start_date_active;
971 END IF;
972
973 IF p_approvers_rec.end_date_active is null THEN
974 x_complete_rec.end_date_active := l_approvers_rec.end_date_active;
975 END IF;
976 */
977 IF p_approvers_rec.object_version_number is null THEN
978 x_complete_rec.object_version_number := l_approvers_rec.object_version_number;
979 END IF;
980
981 /* IF p_approvers_rec.security_group_id is null THEN
982 x_complete_rec.security_group_id := l_approvers_rec.security_group_id;
983 END IF;
984 */
985
986 IF p_approvers_rec.ams_approval_detail_id is null THEN
987 x_complete_rec.ams_approval_detail_id := l_approvers_rec.ams_approval_detail_id;
988 END IF;
989
990 IF p_approvers_rec.approver_seq is null THEN
991 x_complete_rec.approver_seq := l_approvers_rec.approver_seq;
992 END IF;
993
994 IF p_approvers_rec.approver_type is null THEN
995 x_complete_rec.approver_type := l_approvers_rec.approver_type;
996 END IF;
997
998 IF p_approvers_rec.object_approver_id is null THEN
999 x_complete_rec.object_approver_id := l_approvers_rec.object_approver_id;
1000 END IF;
1001
1002 IF p_approvers_rec.notification_type is null THEN
1003 x_complete_rec.notification_type := l_approvers_rec.notification_type;
1004 END IF;
1005
1006 IF p_approvers_rec.notification_timeout is null THEN
1007 x_complete_rec.notification_timeout := l_approvers_rec.notification_timeout;
1008 END IF;
1009
1013
1010 IF p_approvers_rec.seeded_flag is null THEN
1011 x_complete_rec.seeded_flag := l_approvers_rec.seeded_flag;
1012 END IF;
1014 IF p_approvers_rec.active_flag is null THEN
1015 x_complete_rec.active_flag := l_approvers_rec.active_flag;
1016 END IF;
1017
1018 END Complete_approvers_Rec;
1019
1020 ---------------------------------------------------------
1021 -- Function Compare Columns
1022 -- added sugupta 05/22/2000
1023 -- this procedure will compare that no values have been modified for seeded statuses
1024 -----------------------------------------------------------------
1025 FUNCTION compare_columns(
1026 p_approvers_rec IN Approvers_Rec_Type
1027 )
1028 RETURN VARCHAR2
1029 IS
1030 l_count NUMBER := 0;
1031
1032 BEGIN
1033 IF (AMS_DEBUG_HIGH_ON) THEN
1034
1035 Ams_Utility_Pvt.DEBUG_MESSAGE('sTART DATE:'|| TO_CHAR( p_approvers_rec.start_date_active,'DD_MON_YYYY'));
1036 END IF;
1037 IF (AMS_DEBUG_HIGH_ON) THEN
1038
1039 Ams_Utility_Pvt.DEBUG_MESSAGE('end DATE:'|| TO_CHAR( p_approvers_rec.end_Date_active,'DD-MON-YYYY'));
1040 END IF;
1041
1042 IF p_approvers_rec.start_date_active IS NOT NULL THEN
1043 IF p_approvers_rec.end_Date_active IS NOT NULL THEN
1044 BEGIN
1045 SELECT 1 INTO l_count
1046 FROM AMS_APPROVERS
1047 WHERE approver_id =p_approvers_rec.approver_id
1048 AND start_date_active = p_approvers_rec.start_date_active
1049 AND end_date_active = p_approvers_rec.end_Date_active
1050 --AND security_group_id = p_approvers_rec.security_group_id
1051 AND ams_approval_detail_id = p_approvers_rec.ams_approval_detail_id
1052 AND approver_seq = p_approvers_rec.approver_seq
1053 AND approver_type = p_approvers_rec.approver_type
1054 AND object_approver_id = p_approvers_rec.object_approver_id
1055 AND notification_type = p_approvers_rec.notification_type
1056 AND notification_timeout = p_approvers_rec.notification_timeout
1057 AND seeded_flag = 'Y';
1058 EXCEPTION
1059 WHEN NO_DATA_FOUND THEN
1060 l_count := 0;
1061 END;
1062 ELSE -- for end date
1063 BEGIN
1064 SELECT 1 INTO l_count
1065 FROM AMS_APPROVERS
1066 WHERE approver_id =p_approvers_rec.approver_id
1067 AND start_date_active = p_approvers_rec.start_date_active
1068 AND end_date_active = p_approvers_rec.end_Date_active
1069 --AND security_group_id = p_approvers_rec.security_group_id
1070 AND ams_approval_detail_id = p_approvers_rec.ams_approval_detail_id
1071 AND approver_seq = p_approvers_rec.approver_seq
1072 AND approver_type = p_approvers_rec.approver_type
1073 AND object_approver_id = p_approvers_rec.object_approver_id
1074 AND notification_type = p_approvers_rec.notification_type
1075 AND notification_timeout = p_approvers_rec.notification_timeout
1076 AND seeded_flag = 'Y';
1077 EXCEPTION
1078 WHEN NO_DATA_FOUND THEN
1079 l_count := 0;
1080 END;
1081 END IF; -- for end date
1082 ELSE
1083 BEGIN
1084 SELECT 1 INTO l_count
1085 FROM AMS_APPROVERS
1086 WHERE approver_id =p_approvers_rec.approver_id
1087 AND start_date_active = p_approvers_rec.start_date_active
1088 AND end_date_active = p_approvers_rec.end_Date_active
1089 --AND security_group_id = p_approvers_rec.security_group_id
1090 AND ams_approval_detail_id = p_approvers_rec.ams_approval_detail_id
1091 AND approver_seq = p_approvers_rec.approver_seq
1092 AND approver_type = p_approvers_rec.approver_type
1093 AND object_approver_id = p_approvers_rec.object_approver_id
1094 AND notification_type = p_approvers_rec.notification_type
1095 AND notification_timeout = p_approvers_rec.notification_timeout
1096 AND seeded_flag = 'Y';
1097 EXCEPTION
1098 WHEN NO_DATA_FOUND THEN
1099 l_count := 0;
1100 END;
1101 END IF;
1102 IF l_count = 0 THEN
1103 RETURN Fnd_Api.g_false;
1104 ELSE
1105 RETURN Fnd_Api.g_true;
1106 END IF;
1107 END compare_columns;
1108
1109 ---------------------------------------------------------
1110 -- Function seed_needs_update
1111 -- added sugupta 05/22/2000
1112 -- this procedure will look at enabled flag and determine if update is needed
1113 -----------------------------------------------------------------
1114 FUNCTION seed_needs_update(
1115 p_approvers_rec IN Approvers_Rec_Type
1116 )
1117 RETURN VARCHAR2
1118 IS
1119 l_count NUMBER := 0;
1120
1121 BEGIN
1122 BEGIN
1123 SELECT 1 INTO l_count
1124 FROM AMS_APPROVERS
1125 WHERE approver_id = p_approvers_rec.approver_id
1126 AND seeded_flag = 'Y';
1127 EXCEPTION
1128 WHEN NO_DATA_FOUND THEN
1129 l_count := 0;
1130 END;
1131
1132 IF l_count = 0 THEN
1133 RETURN Fnd_Api.g_true; -- needs update
1134 ELSE
1135 RETURN Fnd_Api.g_false; -- doesnt need update
1136 END IF;
1137 END seed_needs_update;
1138
1139 -------------------------------------------------------------
1140 -- Check_Approvers_Req_Items
1141 -------------------------------------------------------------
1142 PROCEDURE Check_Approvers_Req_Items (
1143 p_approvers_rec IN Approvers_Rec_Type,
1144 x_return_status OUT NOCOPY VARCHAR2
1145 ) IS
1146 l_start_date DATE;
1147 l_end_date DATE;
1148 CURSOR get_parent_date (id_in IN NUMBER)IS
1149 SELECT START_DATE_ACTIVE, END_DATE_ACTIVE
1150 FROM AMS_APPROVAL_DETAILS
1151 WHERE APPROVAL_DETAIL_ID = id_in;
1152
1153 BEGIN
1157 THEN
1154 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1155 IF p_approvers_rec.ams_approval_detail_id = NULL
1156 OR p_approvers_rec.ams_approval_detail_id = Fnd_Api.g_miss_num
1158 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1159 Fnd_Message.set_name ('AMS', 'AMS_NO_APPROVAL_DETAIL_ID');
1160 Fnd_Msg_Pub.ADD;
1161 END IF;
1162 x_return_status := Fnd_Api.g_ret_sts_error;
1163 RETURN;
1164 END IF;
1165 --check for valid date range murali
1166 OPEN get_parent_date(p_approvers_rec.ams_approval_detail_id);
1167 FETCH get_parent_date INTO l_start_date, l_end_date;
1168 CLOSE get_parent_date;
1169 IF (p_approvers_rec.START_DATE_ACTIVE IS NOT NULL
1170 AND p_approvers_rec.START_DATE_ACTIVE <> Fnd_Api.g_miss_date)
1171 THEN
1172 IF p_approvers_rec.START_DATE_ACTIVE < l_start_date THEN
1173 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
1174 Fnd_Message.set_name('AMS', 'AMS_APPR_SD_LT_APD_SD');
1175 Fnd_Msg_Pub.ADD;
1176 END IF;
1177 x_return_status := Fnd_Api.g_ret_sts_error;
1178 RETURN;
1179 END IF;
1180 IF p_approvers_rec.START_DATE_ACTIVE > l_end_date THEN
1181 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
1182 Fnd_Message.set_name('AMS', 'AMS_APPR_SD_LT_APD_ED');
1183 Fnd_Msg_Pub.ADD;
1184 END IF;
1185 x_return_status := Fnd_Api.g_ret_sts_error;
1186 RETURN;
1187 END IF;
1188 END IF;
1189 IF (p_approvers_rec.END_DATE_ACTIVE IS NOT NULL
1190 OR p_approvers_rec.END_DATE_ACTIVE <> Fnd_Api.g_miss_date)
1191 THEN
1192 IF p_approvers_rec.END_DATE_ACTIVE < l_start_date THEN
1193 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
1194 Fnd_Message.set_name('AMS', 'AMS_APPR_ED_LT_APD_SD');
1195 Fnd_Msg_Pub.ADD;
1196 END IF;
1197 x_return_status := Fnd_Api.g_ret_sts_error;
1198 RETURN;
1199 END IF;
1200 IF p_approvers_rec.END_DATE_ACTIVE > l_end_date THEN
1201 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
1202 Fnd_Message.set_name('AMS', 'AMS_APPR_ED_LT_APD_ED');
1203 Fnd_Msg_Pub.ADD;
1204 END IF;
1205 x_return_status := Fnd_Api.g_ret_sts_error;
1206 RETURN;
1207 END IF;
1208 END IF;
1209 END;
1210 -------------------------------------------------------------
1211 -- Check_Approvers_UK_Items
1212 -------------------------------------------------------------
1213 PROCEDURE Check_Approvers_UK_Items (
1214 p_approvers_rec IN Approvers_Rec_Type,
1215 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
1216 x_return_status OUT NOCOPY VARCHAR2
1217 )IS
1218 l_dummy NUMBER;
1219 CURSOR c_appr_seq_exists(seq_num_in IN NUMBER, id_in IN NUMBER) IS
1220 SELECT 1 FROM dual
1221 WHERE EXISTS (SELECT 1 FROM ams_approvers
1222 WHERE approver_seq = seq_num_in
1223 AND ams_approval_detail_id = id_in
1224 AND active_flag = 'Y'
1225 );
1226 BEGIN
1227 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1228 IF p_validation_mode = Jtf_Plsql_Api.g_create
1229 THEN
1230 OPEN c_appr_seq_exists (p_approvers_rec.approver_seq,
1231 p_approvers_rec.ams_approval_detail_id
1232 );
1233 FETCH c_appr_seq_exists INTO l_dummy;
1234 CLOSE c_appr_seq_exists;
1235 IF l_dummy = 1 THEN
1236 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1237 Fnd_Message.set_name('AMS', 'AMS_APPR_DUP_SEQ');
1238 Fnd_Msg_Pub.ADD;
1239 x_return_status := Fnd_Api.g_ret_sts_error;
1240 RETURN;
1241 END IF;
1242 END IF;
1243 END IF;
1244
1245
1246 IF p_validation_mode = Jtf_Plsql_Api.g_create THEN
1247 Check_Dates_Create_Range (
1248 p_approvers_rec => p_approvers_rec,
1249 x_return_status => x_return_status
1250 );
1251
1252 IF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1253 RAISE Fnd_Api.g_exc_unexpected_error;
1254 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
1255 RAISE Fnd_Api.g_exc_error;
1256 END IF;
1257 ELSIF (p_validation_mode = Jtf_Plsql_Api.g_update
1258 AND (p_approvers_rec.active_flag = 'Y' OR
1259 p_approvers_rec.active_flag = NULL OR p_approvers_rec.active_flag = Fnd_Api.g_miss_char)) THEN
1260 Check_Dates_Update_Range (
1261 p_approvers_rec => p_approvers_rec,
1262 x_return_status => x_return_status
1263 );
1264
1265 IF x_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1266 RAISE Fnd_Api.g_exc_unexpected_error;
1267 ELSIF x_return_status = Fnd_Api.g_ret_sts_error THEN
1268 RAISE Fnd_Api.g_exc_error;
1269 END IF;
1270 END IF;
1271
1272 END Check_Approvers_UK_Items;
1273 -------------------------------------------------------------
1274 -- Check_Approvers_FK_Items
1275 -------------------------------------------------------------
1276 PROCEDURE Check_Approvers_FK_Items (
1277 p_approvers_rec IN Approvers_Rec_Type,
1278 x_return_status OUT NOCOPY VARCHAR2
1279 )IS
1280 l_dummy NUMBER;
1281 CURSOR c_uappr_id_exists(id_in IN NUMBER) IS
1282 SELECT 1 FROM dual
1283 WHERE EXISTS (SELECT 1 FROM ams_jtf_rs_emp_v
1284 WHERE RESOURCE_ID = id_in);
1285 CURSOR c_rappr_id_exists(id_in IN NUMBER) IS
1286 SELECT 1 FROM dual
1287 WHERE EXISTS ( SELECT 1
1288 FROM jtf_rs_role_relations rr, jtf_rs_roles_b rl
1292 AND SYSDATE BETWEEN rr.start_date_active and nvl(rr.end_date_active, SYSDATE)
1289 WHERE rr.role_id = rl.role_id
1290 AND rr.role_resource_type = 'RS_INDIVIDUAL'
1291 AND rr.delete_flag = 'N'
1293 AND rl.role_type_code in ( 'MKTGAPPR', 'AMSAPPR')
1294 AND rr.role_id = id_in);
1295 -- Replaced for SQL Repository Fix
1296 /*
1297 SELECT 1 FROM JTF_RS_DEFRESROLES_vl
1298 WHERE ROLE_ID = id_in);
1299 */
1300 CURSOR c_multi_appr_exists(id_in IN NUMBER) IS
1301 SELECT COUNT(1)
1302 FROM jtf_rs_role_relations rr, jtf_rs_roles_b rl
1303 WHERE rr.role_id = rl.role_id
1304 AND rr.role_resource_type = 'RS_INDIVIDUAL'
1305 AND rr.delete_flag = 'N'
1306 AND SYSDATE BETWEEN rr.start_date_active and nvl(rr.end_date_active, SYSDATE)
1307 AND rl.role_type_code in ( 'MKTGAPPR', 'AMSAPPR')
1308 AND rr.role_id = id_in;
1309
1310 -- Replaced for SQL Repository Fix
1311 /*
1312 FROM jtf_rs_defresroles_vl
1313 WHERE role_type_code IN ('MKTGAPPR','AMSAPPR')
1314 AND role_id = id_in
1315 AND role_resource_type = 'RS_INDIVIDUAL'
1316 AND delete_flag = 'N'
1317 AND TRUNC(SYSDATE) BETWEEN TRUNC(res_rl_start_date)
1318 AND TRUNC(NVL(res_rl_end_date,SYSDATE));
1319 */
1320 BEGIN
1321 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1322
1323 IF (AMS_DEBUG_HIGH_ON) THEN
1324
1325
1326
1327 Ams_Utility_Pvt.debug_message('The approver type is ' || p_approvers_rec.approver_type);
1328
1329 END IF;
1330 IF (AMS_DEBUG_HIGH_ON) THEN
1331
1332 Ams_Utility_Pvt.debug_message('The object_approver_id is ' || p_approvers_rec.object_approver_id);
1333 END IF;
1334
1335 IF p_approvers_rec.approver_type = 'USER' THEN
1336 OPEN c_uappr_id_exists (p_approvers_rec.object_approver_id);
1337 FETCH c_uappr_id_exists INTO l_dummy;
1338 CLOSE c_uappr_id_exists;
1339 IF (AMS_DEBUG_HIGH_ON) THEN
1340
1341 Ams_Utility_Pvt.debug_message('The l_dummy is ' || l_dummy);
1342 END IF;
1343
1344 IF l_dummy <> 1 THEN
1345 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1346 Fnd_Message.set_name('AMS', 'AMS_APPR_NO_RESORS');
1347 Fnd_Msg_Pub.ADD;
1348 x_return_status := Fnd_Api.g_ret_sts_error;
1349 RETURN;
1350 END IF;
1351 END IF;
1352 ELSIF p_approvers_rec.approver_type = 'ROLE' THEN
1353 IF (AMS_DEBUG_HIGH_ON) THEN
1354
1355 Ams_Utility_Pvt.debug_message('The approver type is ' || p_approvers_rec.approver_type);
1356 END IF;
1357 IF (AMS_DEBUG_HIGH_ON) THEN
1358
1359 Ams_Utility_Pvt.debug_message('The object_approver_id is ' || p_approvers_rec.object_approver_id);
1360 END IF;
1361
1362
1363 OPEN c_rappr_id_exists (p_approvers_rec.object_approver_id);
1364 FETCH c_rappr_id_exists INTO l_dummy;
1365 CLOSE c_rappr_id_exists;
1366 IF l_dummy <> 1 THEN
1367 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1368 Fnd_Message.set_name('AMS', 'AMS_APPR_NO_RESORS');
1369 Fnd_Msg_Pub.ADD;
1370 x_return_status := Fnd_Api.g_ret_sts_error;
1371 RETURN;
1372 END IF;
1373 END IF;
1374
1375 -- Added as part of Bug 2677401
1376 OPEN c_multi_appr_exists(p_approvers_rec.object_approver_id);
1377 FETCH c_multi_appr_exists INTO l_dummy;
1378 CLOSE c_multi_appr_exists;
1379 IF l_dummy > 1 THEN
1380 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1381 Fnd_Message.set_name('AMS', 'AMS_MANY_DEFAULT_ROLE');
1382 Fnd_Msg_Pub.ADD;
1383 x_return_status := Fnd_Api.g_ret_sts_error;
1384 RETURN;
1385 END IF;
1386 END IF;
1387
1388 ELSIF p_approvers_rec.approver_type = 'FUNCTION' THEN
1389 Check_Func_Use_Valid(p_approvers_rec => p_approvers_rec,
1390 x_return_status => x_return_status);
1391 END IF;
1392 END Check_Approvers_FK_Items;
1393 -------------------------------------------------------------
1394 -- Check_Approvers_Lkup_Items
1395 -------------------------------------------------------------
1396 PROCEDURE Check_Approvers_Lkup_Items (
1397 p_approvers_rec IN Approvers_Rec_Type,
1398 x_return_status OUT NOCOPY VARCHAR2
1399 )IS
1400 -- Changed from varchar2(30) for bug 2544992
1401 l_meaning VARCHAR2(80);
1402 BEGIN
1403 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1404 IF p_approvers_rec.approver_type <> Fnd_Api.g_miss_char THEN
1405 Ams_Utility_Pvt.get_lookup_meaning( 'AMS_APPROVER_TYPE',
1406 p_approvers_rec.approver_type,
1407 x_return_status,
1408 l_meaning
1409 );
1410 IF x_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1411 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1412 Fnd_Message.set_name('AMS', 'AMS_APPR_NO_APPR_TYPE');
1413 Fnd_Msg_Pub.ADD;
1414 x_return_status := Fnd_Api.g_ret_sts_error;
1415 RETURN;
1416 END IF;
1417 END IF;
1418 END IF;
1419 END Check_Approvers_Lkup_Items;
1420
1421 -------------------------------------------------------------
1422 -- Check_Approvers_Flag_Items
1423 -------------------------------------------------------------
1424 PROCEDURE Check_Approvers_Flag_Items (
1428 BEGIN
1425 p_approvers_rec IN Approvers_Rec_Type,
1426 x_return_status OUT NOCOPY VARCHAR2
1427 )IS
1429 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1430 END Check_Approvers_Flag_Items;
1431 --------------------------------------------------------------------
1432
1433
1434 -------------------------------------------------------------
1435 -- Check_Dates_Create_Range
1436 -------------------------------------------------------------
1437 PROCEDURE Check_Dates_Create_Range (
1438 p_approvers_rec IN Approvers_Rec_Type,
1439 x_return_status OUT NOCOPY VARCHAR2
1440 )IS
1441 p_start_date DATE := p_approvers_rec.start_date_active;
1442 p_end_date DATE := p_approvers_rec.end_date_active;
1443 l_start_date DATE;
1444 l_end_date DATE;
1445
1446
1447 CURSOR c_approval_rule IS
1448 SELECT start_date_active , end_date_active FROM ams_approval_details
1449 WHERE approval_detail_id = p_approvers_rec.ams_approval_detail_id;
1450
1451 BEGIN
1452
1453 OPEN c_approval_rule;
1454 FETCH c_approval_rule INTO l_start_date,l_end_date;
1455 CLOSE c_approval_rule;
1456
1457
1458 IF (p_start_date IS NULL AND p_end_date IS NOT NULL) THEN
1459 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1460 IF (AMS_DEBUG_HIGH_ON) THEN
1461
1462 Ams_Utility_Pvt.debug_message('Approver end date cannot be specified without start date');
1463 END IF;
1464
1465 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1466 Fnd_Message.set_name('AMS', 'AMS_APPR_NO_START_DATE');
1467 Fnd_Msg_Pub.ADD;
1468 x_return_status := Fnd_Api.g_ret_sts_error;
1469 END IF;
1470
1471 RETURN;
1472 END IF;
1473
1474 IF (p_start_date IS NOT NULL) THEN
1475 IF(p_start_date < trunc(SYSDATE)) THEN
1476 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1477 IF (AMS_DEBUG_HIGH_ON) THEN
1478
1479 Ams_Utility_Pvt.debug_message('Approver start date cannot be less than the system date');
1480 END IF;
1481
1482 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1483 Fnd_Message.set_name('AMS', 'AMS_APPR_START_DATE_LT_SYSDATE');
1484 Fnd_Msg_Pub.ADD;
1485 x_return_status := Fnd_Api.g_ret_sts_error;
1486 END IF;
1487
1488 RETURN;
1489 ELSIF ( p_start_date < l_start_date ) THEN
1490 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1491 IF (AMS_DEBUG_HIGH_ON) THEN
1492
1493 Ams_Utility_Pvt.debug_message('Approver Start Date cannot be less than the Approval Rule Start Date');
1494 END IF;
1495
1496 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1497 Fnd_Message.set_name('AMS', 'AMS_APPR_STDT_GT_APRD_STDT');
1498 Fnd_Msg_Pub.ADD;
1499 x_return_status := Fnd_Api.g_ret_sts_error;
1500 END IF;
1501
1502 RETURN;
1503 END IF;
1504 END IF;
1505
1506 IF (p_end_date IS NOT NULL ) THEN
1507 IF( p_end_date < trunc(SYSDATE)) THEN
1508 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1509 IF (AMS_DEBUG_HIGH_ON) THEN
1510
1511 Ams_Utility_Pvt.debug_message('Approver end date cannot be less than the system date');
1512 END IF;
1513
1514 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1515 Fnd_Message.set_name('AMS', 'AMS_APPR_END_DATE_LT_SYSDATE');
1519
1516 Fnd_Msg_Pub.ADD;
1517 x_return_status := Fnd_Api.g_ret_sts_error;
1518 END IF;
1520 RETURN;
1521 ELSIF (p_end_date > l_end_date) THEN
1522 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1523 IF (AMS_DEBUG_HIGH_ON) THEN
1524
1525 Ams_Utility_Pvt.debug_message('Approver end date cannot be greater than the Approval Rule end date');
1526 END IF;
1527
1528 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1529 Fnd_Message.set_name('AMS', 'AMS_APPR_EDDT_GT_APRD_EDDT');
1530 Fnd_Msg_Pub.ADD;
1531 x_return_status := Fnd_Api.g_ret_sts_error;
1532 END IF;
1533
1534 RETURN;
1535 END IF;
1536 END IF;
1537
1538
1539 END Check_Dates_Create_Range;
1540 --------------------------------------------------------------------
1541
1542 -------------------------------------------------------------
1543 -- Check_Dates_Update_Range
1544 -------------------------------------------------------------
1545 PROCEDURE Check_Dates_Update_Range (
1546 p_approvers_rec IN Approvers_Rec_Type,
1547 x_return_status OUT NOCOPY VARCHAR2
1548 )IS
1549 p_start_date DATE := p_approvers_rec.start_date_active;
1550 p_end_date DATE := p_approvers_rec.end_date_active;
1551 l_start_date DATE;
1552 l_end_date DATE;
1553 l_start_date_ar DATE;
1554 l_end_date_ar DATE;
1555
1556
1557 CURSOR c_approval_rule IS
1558 SELECT start_date_active , end_date_active FROM ams_approval_details
1559 WHERE approval_detail_id = p_approvers_rec.ams_approval_detail_id;
1560
1561 CURSOR c_approver IS
1562 SELECT start_date_active , end_date_active FROM ams_approvers
1563 WHERE approver_id = p_approvers_rec.approver_id;
1564
1565 BEGIN
1566
1567 OPEN c_approval_rule;
1568 FETCH c_approval_rule INTO l_start_date,l_end_date;
1569 CLOSE c_approval_rule;
1570
1571
1572 OPEN c_approver;
1573 FETCH c_approver INTO l_start_date_ar,l_end_date_ar;
1574 CLOSE c_approver;
1575
1576 -- Check whether Approver has a End Date without a Start Date
1577 IF (p_start_date IS NULL AND p_end_date IS NOT NULL) THEN
1578 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1579 IF (AMS_DEBUG_HIGH_ON) THEN
1580
1581 Ams_Utility_Pvt.debug_message('Approver End Date cannot be specified without Start Date');
1582 END IF;
1583
1584 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1585 Fnd_Message.set_name('AMS', 'AMS_APPR_NO_START_DATE');
1586 Fnd_Msg_Pub.ADD;
1587 x_return_status := Fnd_Api.g_ret_sts_error;
1588 END IF;
1589
1590 RETURN;
1591 END IF;
1592
1593 -- Check whether the approver start date has been changed and if it is less than current date
1594 IF (p_start_date IS NOT NULL) THEN
1595 IF(l_start_date_ar < trunc(SYSDATE) AND
1596 l_start_date_ar <> p_start_date) THEN -- Clause added for Bug 2285556
1597 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1598 IF (AMS_DEBUG_HIGH_ON) THEN
1599
1600 Ams_Utility_Pvt.debug_message('Approver start date cannot be changed as it is already active');
1601 END IF;
1602
1603 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1604 Fnd_Message.set_name('AMS', 'AMS_APPR_STDT_NO_CHANGE');
1605 Fnd_Msg_Pub.ADD;
1606 x_return_status := Fnd_Api.g_ret_sts_error;
1607 END IF;
1608
1609 RETURN;
1610
1611 -- Check whether approver start date is less than approval rule start date
1612 ELSIF ( p_start_date < l_start_date ) THEN
1613 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1614 IF (AMS_DEBUG_HIGH_ON) THEN
1615
1616 Ams_Utility_Pvt.debug_message('Approver start date cannot be less than the Approval Rule start date');
1617 END IF;
1618
1619 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1620 Fnd_Message.set_name('AMS', 'AMS_APPR_STDT_LT_APRD_STDT');
1621 Fnd_Msg_Pub.ADD;
1622 x_return_status := Fnd_Api.g_ret_sts_error;
1623 END IF;
1624
1625 RETURN;
1626 END IF;
1627 END IF;
1628
1629 -- Check whether the approver end date has been changed and if it is less than current date
1630 IF (p_end_date IS NOT NULL ) THEN
1631 IF( p_end_date < trunc(SYSDATE) AND
1632 p_end_date <> l_end_date_ar) THEN -- Clause Added for Bug 2285556
1633 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1634 IF (AMS_DEBUG_HIGH_ON) THEN
1635
1636 Ams_Utility_Pvt.debug_message('Approver end date cannot be less than the system date');
1637 END IF;
1638
1639 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1640 Fnd_Message.set_name('AMS', 'AMS_APPR_END_DATE_LT_SYSDATE');
1641 Fnd_Msg_Pub.ADD;
1642 x_return_status := Fnd_Api.g_ret_sts_error;
1643 END IF;
1644
1645 RETURN;
1646
1647 -- Check whether approver end date is greater than approval rule end date
1648 ELSIF (p_end_date > l_end_date) THEN
1649 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1650 IF (AMS_DEBUG_HIGH_ON) THEN
1651
1652 Ams_Utility_Pvt.debug_message('Approver end date cannot be greater than the Approval Rule end date');
1653 END IF;
1654
1655 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1656 Fnd_Message.set_name('AMS', 'AMS_APPR_EDDT_GT_APRD_EDDT');
1657 Fnd_Msg_Pub.ADD;
1658 x_return_status := Fnd_Api.g_ret_sts_error;
1659 END IF;
1660
1661 RETURN;
1662 END IF;
1663 /* Commented OUT NOCOPY the hanging elsif during fix for 2285556
1664 -- Approval Rule End date overrides the approver end date and hence this is not required
1665 ELSIF(l_end_date IS NOT NULL) THEN
1666 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1667 IF (AMS_DEBUG_HIGH_ON) THEN
1668
1669 Ams_Utility_Pvt.debug_message('Approver end date cannot be open as the end date for Approval Rule is Closed');
1670 END IF;
1671
1672 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1673 Fnd_Message.set_name('AMS', 'AMS_APPR_EDDT_OPEN');
1674 Fnd_Msg_Pub.ADD;
1675 x_return_status := Fnd_Api.g_ret_sts_error;
1676 END IF;
1677
1678 RETURN;
1679 */
1680 END IF;
1681
1682
1683 END Check_Dates_Update_Range;
1684 --------------------------------------------------------------------
1685 PROCEDURE Check_Func_Use_Valid(
1686 p_approvers_rec IN Approvers_Rec_Type,
1687 x_return_status OUT NOCOPY VARCHAR2
1688 ) IS
1689
1690 l_approval_object VARCHAR2(30);
1691 l_approval_type VARCHAR2(30);
1692 l_seeded_flag VARCHAR2(1);
1693 l_package_name VARCHAR2(80);
1694 l_proc_name VARCHAR2(80);
1695
1696 CURSOR c_approval_rule IS
1697 SELECT approval_object, approval_type
1698 FROM ams_approval_details
1699 WHERE approval_detail_id = p_approvers_rec.ams_approval_detail_id;
1700
1701 CURSOR c_obj_rule IS
1702 SELECT seeded_flag, package_name, procedure_name
1703 FROM ams_object_rules_vl
1704 WHERE object_rule_id = p_approvers_rec.object_approver_id;
1705
1706 BEGIN
1707 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1708
1709 OPEN c_approval_rule;
1710 FETCH c_approval_rule INTO l_approval_object, l_approval_type;
1711 CLOSE c_approval_rule;
1712
1713 OPEN c_obj_rule;
1714 FETCH c_obj_rule INTO l_seeded_flag, l_package_name, l_proc_name;
1715 CLOSE c_obj_rule;
1716
1717 IF l_seeded_flag = 'Y' THEN
1718 -- Check Validity of use only for Seeded Functions
1719
1720 IF l_approval_object NOT IN ('CAMP','EVEH','EVEO','CSCH','DELV','EONE')
1721 AND l_package_name = 'AMS_APPROVAL_UTIL_PVT'
1722 AND l_proc_name = 'GET_OBJECT_OWNER' THEN
1723
1724 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1725 IF (AMS_DEBUG_HIGH_ON) THEN
1726 Ams_Utility_Pvt.debug_message('Invalid Use of Function for this objects Approval');
1727 END IF;
1728
1729 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1730 Fnd_Message.set_name('AMS', 'AMS_APPR_FUNC_INVALID');
1731 Fnd_Msg_Pub.ADD;
1732 x_return_status := Fnd_Api.g_ret_sts_error;
1733 END IF;
1734
1735 RETURN;
1736 END IF;
1737
1738 IF l_approval_object NOT IN ('CSCH', 'EVEO', 'OFFR')
1739 AND l_package_name = 'AMS_APPROVAL_UTIL_PVT'
1740 AND l_proc_name = 'GET_PARENT_OBJECT_OWNER' THEN
1741
1742 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1743 IF (AMS_DEBUG_HIGH_ON) THEN
1744 Ams_Utility_Pvt.debug_message('Invalid Use of Function for this objects Approval');
1745 END IF;
1746
1747 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1748 Fnd_Message.set_name('AMS', 'AMS_APPR_FUNC_INVALID');
1749 Fnd_Msg_Pub.ADD;
1750 x_return_status := Fnd_Api.g_ret_sts_error;
1751 END IF;
1752
1753 RETURN;
1754 END IF;
1755
1756 IF l_approval_object IN ('CSCH', 'EVEO')
1757 AND l_package_name = 'AMS_APPROVAL_UTIL_PVT'
1758 AND l_proc_name = 'GET_PARENT_OBJECT_OWNER' THEN
1759
1760 IF NVL(Fnd_Profile.Value(name => 'AMS_SOURCE_FROM_PARENT'), 'N') = 'N' THEN
1761 Fnd_Message.Set_Name('AMS','AMS_APPR_FUNC_INVALID');
1762 Fnd_Msg_Pub.ADD;
1763 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1764 IF (AMS_DEBUG_HIGH_ON) THEN
1765 Ams_Utility_Pvt.debug_message('Invalid Use of Function for this objects Approval as SFP is NO');
1766 END IF;
1767 RETURN;
1768 END IF;
1769
1770 END IF;
1771
1772 IF l_approval_object NOT IN ('FUND', 'FREQ')
1773 AND l_package_name = 'AMS_APPROVAL_UTIL_PVT'
1774 AND l_proc_name = 'GET_BUDGET_OWNER' THEN
1775
1776 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1777 IF (AMS_DEBUG_HIGH_ON) THEN
1778 Ams_Utility_Pvt.debug_message('Invalid Use of Function for this objects Approval');
1779 END IF;
1780
1781 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1782 Fnd_Message.set_name('AMS', 'AMS_APPR_FUNC_INVALID');
1783 Fnd_Msg_Pub.ADD;
1784 x_return_status := Fnd_Api.g_ret_sts_error;
1785 END IF;
1786
1787 RETURN;
1788 END IF;
1789
1790 IF l_approval_object NOT IN ('FREQ') -- RFRQ not right Bug 3979814
1791 AND l_package_name = 'AMS_APPROVAL_UTIL_PVT'
1792 AND l_proc_name = 'GET_PARENT_BUDGET_OWNER' THEN
1793
1794 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1795 IF (AMS_DEBUG_HIGH_ON) THEN
1796 Ams_Utility_Pvt.debug_message('Invalid Use of Function for this objects Approval');
1797 END IF;
1798
1799 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
1800 Fnd_Message.set_name('AMS', 'AMS_APPR_FUNC_INVALID');
1801 Fnd_Msg_Pub.ADD;
1802 x_return_status := Fnd_Api.g_ret_sts_error;
1803 END IF;
1804
1805 RETURN;
1806 END IF;
1807
1808 END IF;
1809
1810 END Check_Func_Use_Valid;
1811
1812 END Ams_Approvers_Pvt;
[Home] [Help]