1 PACKAGE BODY AMS_APPROVAL_DETAILS_PVT AS
2 /* $Header: amsvapdb.pls 120.2 2005/12/28 00:14:47 vmodur noship $ */
3
4 -----------------------------------------------------------
5 -- PACKAGE
6 -- AMS_APPROVAL_DETAILS_PVT
7 --
8 -- PURPOSE
9 -- This package is a Private API for managing Approval details
10 -- in AMS. It contains specification for pl/sql records and tables
11 --
12 -- AMS_APPROVAL_DETAILS_VL:
13 -- Create_Approval_Details (see below for specification)
14 -- Update_Approval_Details (see below for specification)
15 -- Delete_Approval_Details (see below for specification)
16 -- Lock_Approval_Details (see below for specification)
17 -- Validate_Approval_Details (see below for specification)
18 --
19 -- Check_Approval_Details_Items (see below for specification)
20 -- Check_Approval_Details_Record (see below for specification)
21 -- Init_Approval_Details_Rec
22 -- Complete_Approval_Details_Rec
23 --
24 -- NOTES
25 --
26 --
27 -- HISTORY
28 -- 19-OCT-2000 mukumar Created.
29 -- 08-JAN-2001 MUKUMAR In validation for lookup type
30 -- AMS_FUND_SOURCE replaced with
31 -- AMS_APPEOVAL_RULE_FOR.
32 -- 26-DEC-2001 SVEERAVE Replaced logic in Check_Approval_Dtls_UK_Items
33 -- to fix bug# 2155701
34 -- 05-FEB-2002 VMODUR Change Check_Unique_Rule from a function to a
35 -- procedure to also return the name of overlapping
36 -- rule for bug# 2195020. Token is used to display
37 -- this rule name in the jsp
38 -- 30-APR-2002 VMODUR Country Code is now an integral part of Approval Rule
39 -- Validation Enh 1578624
40 -- 09-MAY-2002 VMODUR Fix for Bug 2340052 - Overlapping Rules
41 -- Currency Code is also used to determine uniqueness
42 -- and overlapping
43 -- 20-JUN-2002 VMODUR Bug 2195020 - Fix for Budget and Concept Approval Rule Overlap
44 -- to return the name of the overlapping rule. Function
45 -- Check_Approval_Amounts_Overlap was changed to a procedure and
46 -- new proc get_Approval_Rule_Name added
47 -- 24-Jul-2002 VMODUR Fix for Bug 2474782 - Allow Negative Amounts For Claims
48 -- and call check_approval_details_items during update also
49 -- 12-Sep-2002 VMODUR l_meaning changed from Varchar2(30) to Varchar2(80) for MLS Bug
50 -- 31-Jan-2003 VMODUR Bug 2776795 Fix
51 -- 29-Jul-2003 VMODUR Bug 3068835 fix from 11.5.9 Cert
52 -- 13-Sep-2003 VMODUR 11.5.10 Changes for LITE CSCH - No Budget Min Amt null Validation
53 -- 30-Oct-2003 VMODUR 11.5.10 Amount Overlap Changes
54 -- 01-DEC-2003 VMODUR Bug 3275739 Fix
55 -- 12-MAY-2004 VMODUR Perf Repository Fix. Use _VL instead of _V
56 -- 12-JUL-2004 VMODUR Bug 3737174 Fix
57 -- 04-OCT-2004 VMODUR Bug 3871802 Fix in 11.5.11
58 -- 14-SEP-2005 VMODUR R12 Changes - No LITE/PHAT distinction for CSCH
59 -----------------------------------------------------------
60
61 -- Global CONSTANTS
62 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_Approval_Details_PVT';
63
64 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
65 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
66 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
67
68 FUNCTION compare_columns(
69 p_approval_details_rec IN Approval_Details_Rec_Type
70 ) RETURN VARCHAR2; -- FND_API.g_true/g_false
71
72 FUNCTION seed_needs_update(
73 p_approval_details_rec IN Approval_Details_Rec_Type
74 ) RETURN VARCHAR2; -- FND_API.g_true/g_false
75
76 -- Check_Approval_Dtls_Req_Items
77 PROCEDURE Check_Approval_Dtls_Req_Items (
78 p_approval_details_rec IN Approval_Details_Rec_Type,
79 x_return_status OUT NOCOPY VARCHAR2
80 );
81 -- Check_Approval_Dtls_UK_Items
82 PROCEDURE Check_Approval_Dtls_UK_Items (
83 p_approval_details_rec IN Approval_Details_Rec_Type,
84 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
85 x_return_status OUT NOCOPY VARCHAR2
86 );
87 -- Check_Approval_Dtls_FK_Items
88 PROCEDURE Check_Approval_Dtls_FK_Items (
89 p_approval_details_rec IN Approval_Details_Rec_Type,
90 x_return_status OUT NOCOPY VARCHAR2
91 );
92 -- Check_Approval_Dtls_Lkup_Items
93 PROCEDURE Check_Approval_Dtls_Lkup_Items (
94 p_approval_details_rec IN Approval_Details_Rec_Type,
95 x_return_status OUT NOCOPY VARCHAR2
96 );
97
98 -- Check_Approval_Dtls_Flag_Items
99 PROCEDURE Check_Approval_Dtls_Flag_Items (
100 p_approval_details_rec IN Approval_Details_Rec_Type,
101 x_return_status OUT NOCOPY VARCHAR2
102 );
103
104 PROCEDURE Check_Approval_Amounts_Overlap (
105 p_approval_details_rec IN Approval_Details_Rec_Type,
106 p_appoval_ids IN t_approval_id_table,
107 x_exist_rule_name OUT NOCOPY VARCHAR2,
108 x_return_status OUT NOCOPY VARCHAR2
109 );
110
111 FUNCTION Check_Dates_Overlap (
112 p_approval_details_rec IN Approval_Details_Rec_Type,
113 p_validation_mode IN VARCHAR2
114 ) RETURN VARCHAR2; -- FND_API.g_true/g_false
115
116
117 PROCEDURE Check_Unique_Rule (
118 p_approval_details_rec IN Approval_Details_Rec_Type,
119 x_exist_rule_name OUT NOCOPY VARCHAR2,
120 x_return_status OUT NOCOPY VARCHAR2 -- FND_API.g_true/g_false
121 );
122
123 PROCEDURE Get_Approval_Rule_Name(
124 p_approval_detail_id IN NUMBER,
125 x_rule_name OUT NOCOPY VARCHAR2)
126 IS
127 l_rule_name VARCHAR2(240);
128 BEGIN
129 SELECT name
130 INTO l_rule_name
131 FROM ams_approval_details_v
132 where approval_detail_id = p_approval_detail_id;
133 x_rule_name := l_rule_name;
134 END;
135
136 FUNCTION Is_Usage_Lite(
137 p_custom_setup_id IN NUMBER
138 ) RETURN VARCHAR2 -- FND_API.g_true/g_false
139 IS
140 l_usage VARCHAR2(30);
141 BEGIN
142 SELECT usage
143 INTO l_usage
144 FROM ams_custom_setups_b
145 WHERE custom_setup_id = p_custom_setup_id;
146
147 IF l_usage = 'LITE' THEN
148 return FND_API.g_true;
149 ELSE
150 return FND_API.g_false;
151 END IF;
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 return FND_API.g_false;
155 END;
156
157
158 --------------------------------------------------------------------
159 -- PROCEDURE
160 -- Create_Approval_Details
161 --
162 -- PURPOSE
163 -- Create Approval Details entry.
164 --
165 -- PARAMETERS
166 -- p_approval_detail_rec: the record representing AMS_APPROVAL_DETAILS_VL view..
167 -- x_approval_detail_id: the approval_detail_id.
168 --
169 -- NOTES
170 -- 1. object_version_number will be set to 1.
171 -- 2. If approval_detail_id is passed in, the uniqueness will be checked.
172 -- Raise exception in case of duplicates.
173 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
174 -- Raise exception for invalid flag.
175 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
176 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
177 --------------------------------------------------------------------
178 PROCEDURE Create_approval_details (
179 p_api_version IN NUMBER,
180 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
181 p_commit IN VARCHAR2 := FND_API.g_false,
182 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
183 x_return_status OUT NOCOPY VARCHAR2,
184 x_msg_count OUT NOCOPY NUMBER,
185 x_msg_data OUT NOCOPY VARCHAR2,
186 p_approval_details_rec IN Approval_Details_Rec_Type,
187 x_approval_detail_id OUT NOCOPY NUMBER
188 )
189 IS
190
191 L_API_VERSION CONSTANT NUMBER := 1.0;
192 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Approval_Details';
193 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
194 l_Approval_Details_rec Approval_Details_Rec_Type := p_approval_details_rec;
195 l_exist_rule_name AMS_APPROVAL_DETAILS_V.Name%TYPE;
196 l_dummy NUMBER;
197 l_return_status VARCHAR2(1);
198 l_row_id VARCHAR2(40);
199
200 CURSOR c_seq IS
201 SELECT ams_approval_details_s.NEXTVAL
202 FROM dual;
203
204 CURSOR c_id_exists (x_id IN NUMBER) IS
205 SELECT 1 FROM dual
206 WHERE EXISTS (SELECT 1 FROM ams_approval_details
207 WHERE approval_detail_id = x_id);
208 BEGIN
209 --------------------- initialize -----------------------
210 SAVEPOINT Create_Approval_Details;
211 IF (AMS_DEBUG_HIGH_ON) THEN
212
213 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
214 END IF;
215 IF FND_API.to_boolean (p_init_msg_list) THEN
216 FND_MSG_PUB.initialize;
217 END IF;
218 IF NOT FND_API.compatible_api_call (
219 L_API_VERSION,
220 p_api_version,
221 L_API_NAME,
222 G_PKG_NAME
223 ) THEN
224 RAISE FND_API.g_exc_unexpected_error;
225 END IF;
226 x_return_status := FND_API.g_ret_sts_success;
227
228
229 ----------------Similar Rule Already Exists Or Not ---------------------------
230
231 Check_Unique_Rule(p_approval_details_rec => l_Approval_Details_rec,
232 x_exist_rule_name => l_exist_rule_name,
233 x_return_status => l_return_status);
234 IF l_return_status = FND_API.g_true THEN
235 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
236 FND_MESSAGE.set_name ('AMS', 'AMS_APPR_RULE_EXISTS');
237 FND_MESSAGE.set_token('EXIST_RULE_NAME', l_exist_rule_name);
238 FND_MSG_PUB.add;
239 END IF;
240 RAISE FND_API.g_exc_error;
241 END IF;
242
243 ----------------------- validate -----------------------
244 IF (AMS_DEBUG_HIGH_ON) THEN
245
246 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
247 END IF;
248 Validate_approval_details (
249 p_api_version => L_API_VERSION,
250 p_init_msg_list => p_init_msg_list,
251 p_commit => p_commit,
252 p_validation_level => p_validation_level,
253 x_return_status => l_return_status,
254 x_msg_count => x_msg_count,
255 x_msg_data => x_msg_data,
256 p_approval_details_rec => l_Approval_Details_rec
257 );
258 IF l_return_status = FND_API.g_ret_sts_error THEN
259 RAISE FND_API.g_exc_error;
260 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
261 RAISE FND_API.g_exc_unexpected_error;
262 END IF;
263 --
264 -- Check for the ID.
265 --
266 IF l_Approval_Details_rec.approval_detail_id IS NULL THEN
267 LOOP
268 --
269 -- If the ID is not passed into the API, then
270 -- grab a value from the sequence.
271 OPEN c_seq;
272 FETCH c_seq INTO l_Approval_Details_rec.approval_detail_id;
273 CLOSE c_seq;
274 --
275 -- Check to be sure that the sequence does not exist.
276 OPEN c_id_exists (l_Approval_Details_rec.approval_detail_id);
277 FETCH c_id_exists INTO l_dummy;
278 CLOSE c_id_exists;
279 --
280 -- If the value for the ID already exists, then
281 -- l_dummy would be populated with '1', otherwise,
282 -- it receives NULL.
283
284 EXIT WHEN l_dummy IS NULL;
285 END LOOP;
286 END IF;
287 -------------------------- insert --------------------------
288 IF (AMS_DEBUG_HIGH_ON) THEN
289
290 AMS_Utility_PVT.debug_message (l_full_name || ': Insert');
291 END IF;
292 --
293 -- Insert into mutli-language supported table.
294 --
295 AMS_APPROVAL_DETAILS_PKG.INSERT_ROW (
296 X_ROWID => l_row_id,
297 X_APPROVAL_DETAIL_ID => l_Approval_Details_rec.APPROVAL_DETAIL_ID,
298 X_START_DATE_ACTIVE => l_Approval_Details_rec.START_DATE_ACTIVE,
299 X_END_DATE_ACTIVE => l_Approval_Details_rec.END_DATE_ACTIVE,
300 X_OBJECT_VERSION_NUMBER => 1, --l_Approval_Details_rec.OBJECT_VERSION_NUMBER,
301 --X_SECURITY_GROUP_ID => l_Approval_Details_rec.SECURITY_GROUP_ID,
302 X_BUSINESS_GROUP_ID => l_Approval_Details_rec.BUSINESS_GROUP_ID,
303 X_BUSINESS_UNIT_ID => l_Approval_Details_rec.BUSINESS_UNIT_ID,
304 X_ORGANIZATION_ID => l_Approval_Details_rec.ORGANIZATION_ID,
305 X_CUSTOM_SETUP_ID => l_Approval_Details_rec.CUSTOM_SETUP_ID,
306 X_APPROVAL_OBJECT => l_Approval_Details_rec.APPROVAL_OBJECT,
307 X_APPROVAL_OBJECT_TYPE => l_Approval_Details_rec.APPROVAL_OBJECT_TYPE,
308 X_APPROVAL_TYPE => l_Approval_Details_rec.APPROVAL_TYPE,
309 X_APPROVAL_PRIORITY => l_Approval_Details_rec.APPROVAL_PRIORITY,
310 X_APPROVAL_LIMIT_TO => l_Approval_Details_rec.APPROVAL_LIMIT_TO,
311 X_APPROVAL_LIMIT_FROM => l_Approval_Details_rec.APPROVAL_LIMIT_FROM,
312 X_SEEDED_FLAG => nvl(l_Approval_Details_rec.SEEDED_FLAG, 'N'),
313 X_ACTIVE_FLAG => nvl(l_Approval_Details_rec.ACTIVE_FLAG, 'Y'),
314 X_CURRENCY_CODE => l_Approval_Details_rec.CURRENCY_CODE,
315 X_USER_COUNTRY_CODE => l_Approval_Details_rec.USER_COUNTRY_CODE,
316 X_NAME => l_Approval_Details_rec.NAME,
317 X_DESCRIPTION => l_Approval_Details_rec.DESCRIPTION,
318 X_CREATION_DATE => sysdate,
319 X_CREATED_BY => FND_GLOBAL.User_Id,
320 X_LAST_UPDATE_DATE => sysdate,
321 X_LAST_UPDATED_BY => FND_GLOBAL.User_Id,
322 X_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
323 ) ;
324 -- set OUT value
325 x_approval_detail_id := l_Approval_Details_rec.APPROVAL_DETAIL_ID;
326 --
327 -- END of API body.
328 --
329 -- Standard check of p_commit.
330 IF FND_API.To_Boolean ( p_commit ) THEN
331 COMMIT WORK;
332 END IF;
333 FND_MSG_PUB.count_and_get(
334 p_encoded => FND_API.g_false,
335 p_count => x_msg_count,
336 p_data => x_msg_data
337 );
338 IF (AMS_DEBUG_HIGH_ON) THEN
339
340 AMS_Utility_PVT.debug_message (l_full_name || ': End');
341 END IF;
342
343 EXCEPTION
344 WHEN FND_API.g_exc_error THEN
345 ROLLBACK TO Create_Approval_Details;
346 x_return_status := FND_API.g_ret_sts_error;
347 FND_MSG_PUB.count_and_get(
348 p_encoded => FND_API.g_false,
349 p_count => x_msg_count,
350 p_data => x_msg_data
351 );
352 WHEN FND_API.g_exc_unexpected_error THEN
353 ROLLBACK TO Create_Approval_Details;
354 x_return_status := FND_API.g_ret_sts_unexp_error ;
355 FND_MSG_PUB.count_and_get (
356 p_encoded => FND_API.g_false,
357 p_count => x_msg_count,
358 p_data => x_msg_data
359 );
360 WHEN OTHERS THEN
361 ROLLBACK TO Create_Approval_Details;
362 x_return_status := FND_API.g_ret_sts_unexp_error ;
363 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
364 THEN
365 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
366 END IF;
367 FND_MSG_PUB.count_and_get (
368 p_encoded => FND_API.g_false,
369 p_count => x_msg_count,
370 p_data => x_msg_data
371 );
372 END Create_approval_details;
373 --------------------------------------------------------------------
374 -- PROCEDURE
375 -- Update_approval_details
376 --
377 -- PURPOSE
378 -- Update an approval details entry.
379 --
380 -- PARAMETERS
381 -- p_approval_details_rec: the record representing AMS_APPROVAL_DETAILS_VL (without the ROW_ID column).
382 --
383 -- NOTES
384 -- 1. Raise exception if the object_version_number doesn't match.
385 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
386 -- that column won't be updated.
387 --------------------------------------------------------------------
388 PROCEDURE Update_approval_details (
389 p_api_version IN NUMBER,
390 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
391 p_commit IN VARCHAR2 := FND_API.g_false,
392 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
393
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_count OUT NOCOPY NUMBER,
396 x_msg_data OUT NOCOPY VARCHAR2,
397
398 p_approval_details_rec IN Approval_Details_Rec_Type
399 )
400 IS
401
402 L_API_VERSION CONSTANT NUMBER := 1.0;
403 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Approval_Details';
404 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
405
406 l_Approval_Details_rec Approval_Details_Rec_Type := p_approval_details_rec;
407 l_exist_rule_name AMS_APPROVAL_DETAILS_V.Name%TYPE;
408 l_dummy NUMBER;
409 l_return_status VARCHAR2(1);
410
411 CURSOR c_rec_exists (x_id IN NUMBER, ver IN NUMBER) IS
412 SELECT 1 FROM ams_approval_details
413 WHERE approval_detail_id = x_id
414 AND object_version_number = ver;
415
416 BEGIN
417 --------------------- initialize -----------------------
418 SAVEPOINT Update_approval_details;
419 IF (AMS_DEBUG_HIGH_ON) THEN
420
421 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
422 END IF;
423 IF FND_API.to_boolean (p_init_msg_list) THEN
424 FND_MSG_PUB.initialize;
425 END IF;
426 IF NOT FND_API.compatible_api_call(
427 l_api_version,
428 p_api_version,
429 l_api_name,
430 g_pkg_name
431 ) THEN
432 RAISE FND_API.g_exc_unexpected_error;
433 END IF;
434 x_return_status := FND_API.g_ret_sts_success;
435
436
437 ----------------Similar Rule Already Exists Or Not ---------------------------
438 Check_Unique_Rule(p_approval_details_rec => l_Approval_Details_rec,
439 x_exist_rule_name => l_exist_rule_name,
440 x_return_status => l_return_status);
441 IF l_return_status = FND_API.g_true THEN
442 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
443 FND_MESSAGE.set_name ('AMS', 'AMS_APPR_RULE_EXISTS');
444 FND_MESSAGE.set_token('EXIST_RULE_NAME',l_exist_rule_name);
445 FND_MSG_PUB.add;
446 END IF;
447 RAISE FND_API.g_exc_error;
448 END IF;
449
450 ----------------------- validate ----------------------
451 IF (AMS_DEBUG_HIGH_ON) THEN
452
453 AMS_Utility_PVT.debug_message (l_full_name || ': Validate');
454 END IF;
455 -- replace g_miss_char/num/date with current column values
456 Complete_approval_details_Rec(p_approval_details_rec,l_approval_details_Rec);
457 IF l_approval_details_Rec.seeded_flag = 'Y' THEN
458 IF compare_columns(l_approval_details_Rec) = FND_API.g_false THEN
459 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
460 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_SEED_DATA');
461 FND_MSG_PUB.add;
462 END IF;
463 RAISE FND_API.g_exc_error;
464 END IF;
465 ELSE
466 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
467 Check_approval_details_Items (
468 p_approval_details_rec => l_approval_details_Rec ,
469 p_validation_mode => JTF_PLSQL_API.g_update,
470 x_return_status => l_return_status
471 );
472 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
473 RAISE FND_API.g_exc_unexpected_error;
474 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
475 RAISE FND_API.g_exc_error;
476 END IF;
477 END IF;
478 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
479 Check_approval_details_Record (
480 p_approval_details_rec => p_approval_details_rec,
481 p_complete_rec => l_approval_details_rec,
482 x_return_status => l_return_status
483 );
484 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
485 RAISE FND_API.g_exc_unexpected_error;
486 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
487 RAISE FND_API.g_exc_error;
488 END IF;
489 END IF;
490 END IF; -- check for seeded flag
491 -- Check to see if the row is seeded if the row is seeded then can't update
492 -- modified.. enabled flag for seeded rows can be updated.. added seed_needs_ -- update function
493 IF l_approval_details_rec.seeded_flag='N'
494 OR seed_needs_update(l_approval_details_rec) = FND_API.g_true
495 THEN
496 -------------------------- update --------------------
497 IF (AMS_DEBUG_HIGH_ON) THEN
498
499 AMS_Utility_PVT.debug_message (l_full_name || ': Update');
500 END IF;
501 OPEN c_rec_exists (l_Approval_Details_rec.approval_detail_id, p_Approval_Details_rec.OBJECT_VERSION_NUMBER);
502 FETCH c_rec_exists INTO l_dummy;
503 If c_rec_exists%NOTFOUND THEN
504 CLOSE c_rec_exists;
505 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
506 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
507 FND_MSG_PUB.add;
508 END IF;
509 RAISE FND_API.g_exc_error;
510 else
511 CLOSE c_rec_exists;
512 end IF;
513
514 AMS_APPROVAL_DETAILS_PKG.UPDATE_ROW(
515 X_APPROVAL_DETAIL_ID => l_Approval_Details_rec.approval_detail_id,
516 X_START_DATE_ACTIVE => l_approval_details_rec.START_DATE_ACTIVE,
517 X_END_DATE_ACTIVE => l_approval_details_rec.END_DATE_ACTIVE,
518 X_OBJECT_VERSION_NUMBER => l_approval_details_rec.OBJECT_VERSION_NUMBER+1,
519 --X_SECURITY_GROUP_ID => l_approval_details_rec.SECURITY_GROUP_ID,
520 X_BUSINESS_GROUP_ID => l_approval_details_rec.BUSINESS_GROUP_ID,
521 X_BUSINESS_UNIT_ID => l_approval_details_rec.BUSINESS_UNIT_ID,
522 X_ORGANIZATION_ID => l_approval_details_rec.ORGANIZATION_ID,
523 X_CUSTOM_SETUP_ID => l_approval_details_rec.CUSTOM_SETUP_ID,
524 X_APPROVAL_OBJECT => l_approval_details_rec.APPROVAL_OBJECT,
525 X_APPROVAL_OBJECT_TYPE => l_approval_details_rec.APPROVAL_OBJECT_TYPE,
526 X_APPROVAL_TYPE => l_approval_details_rec.APPROVAL_TYPE,
527 X_APPROVAL_PRIORITY => l_approval_details_rec.APPROVAL_PRIORITY,
528 X_APPROVAL_LIMIT_TO => l_approval_details_rec.APPROVAL_LIMIT_TO,
529 X_APPROVAL_LIMIT_FROM => l_approval_details_rec.APPROVAL_LIMIT_FROM,
530 X_SEEDED_FLAG => l_approval_details_rec.SEEDED_FLAG,
531 X_ACTIVE_FLAG => l_Approval_Details_rec.ACTIVE_FLAG,
532 X_CURRENCY_CODE => l_Approval_Details_rec.CURRENCY_CODE,
533 X_USER_COUNTRY_CODE => l_Approval_Details_rec.USER_COUNTRY_CODE,
534 X_NAME => l_approval_details_rec.NAME,
535 X_DESCRIPTION => l_approval_details_rec.DESCRIPTION,
536 X_LAST_UPDATE_DATE => SYSDATE,
537 X_LAST_UPDATED_BY => FND_GLOBAL.User_Id,
538 X_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
539 );
540 END IF;-- ending if loop for second seeded_flag check
541 -------------------- finish --------------------------
542 IF FND_API.to_boolean (p_commit) THEN
543 COMMIT;
544 END IF;
545 FND_MSG_PUB.count_and_get (
546 p_encoded => FND_API.g_false,
547 p_count => x_msg_count,
548 p_data => x_msg_data
549 );
550 IF (AMS_DEBUG_HIGH_ON) THEN
551
552 AMS_Utility_PVT.debug_message (l_full_name || ': End');
553 END IF;
554
555 EXCEPTION
556 WHEN FND_API.g_exc_error THEN
557 ROLLBACK TO Update_approval_details;
558 x_return_status := FND_API.g_ret_sts_error;
559 FND_MSG_PUB.count_and_get (
560 p_encoded => FND_API.g_false,
561 p_count => x_msg_count,
562 p_data => x_msg_data
563 );
564
565 WHEN FND_API.g_exc_unexpected_error THEN
566 ROLLBACK TO Update_approval_details;
567 x_return_status := FND_API.g_ret_sts_unexp_error ;
568 FND_MSG_PUB.count_and_get (
569 p_encoded => FND_API.g_false,
570 p_count => x_msg_count,
571 p_data => x_msg_data
572 );
573
574 WHEN OTHERS THEN
575 ROLLBACK TO Update_approval_details;
576 x_return_status := FND_API.g_ret_sts_unexp_error ;
577 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
578 THEN
579 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
580 END IF;
581 FND_MSG_PUB.count_and_get (
582 p_encoded => FND_API.g_false,
583 p_count => x_msg_count,
584 p_data => x_msg_data
585 );
586 END Update_approval_details;
587
588 --------------------------------------------------------------------
589 -- PROCEDURE
590 -- Delete_approval_details
591 --
592 -- PURPOSE
593 -- Delete a approval details entry.
594 --
595 -- PARAMETERS
596 -- p_approval_detail_id: the approval_detail_id
597 -- p_object_version: the object_version_number
598 --
599 -- ISSUES
600 --
601 -- NOTES
602 -- 1. Raise exception if the object_version_number doesn't match.
603 --------------------------------------------------------------------
604 PROCEDURE Delete_approval_details (
605 p_api_version IN NUMBER,
606 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
607 p_commit IN VARCHAR2 := FND_API.g_false,
608 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
609
610 x_return_status OUT NOCOPY VARCHAR2,
611 x_msg_count OUT NOCOPY NUMBER,
612 x_msg_data OUT NOCOPY VARCHAR2,
613
614 p_approval_detail_id IN NUMBER,
615 p_object_version IN NUMBER
616 ) IS
617
618 CURSOR c_approval_details IS
619 SELECT *
620 FROM ams_approval_details_vl
621 WHERE approval_detail_id = p_approval_detail_id;
622 --
623 -- This is the only exception for using %ROWTYPE.
624 -- We are selecting from the VL view, which may
625 -- have some denormalized columns as compared to
626 -- the base tables.
627
628 l_approval_details_rec c_approval_details%ROWTYPE;
629 l_api_version CONSTANT NUMBER := 1.0;
630 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Approval_Details';
631 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
632
633 Begin
634
635 OPEN c_approval_details;
636 FETCH c_approval_details INTO l_approval_details_rec;
637 IF c_approval_details%NOTFOUND THEN
638 CLOSE c_approval_details;
639 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
640 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
641 FND_MSG_PUB.add;
642 END IF;
643 RAISE FND_API.g_exc_error;
644 END IF;
645 CLOSE c_approval_details;
646 --------------------- initialize -----------------------
647 SAVEPOINT Delete_approval_details;
648 IF (AMS_DEBUG_HIGH_ON) THEN
649
650 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
651 END IF;
652 IF FND_API.to_boolean (p_init_msg_list) THEN
653 FND_MSG_PUB.initialize;
654 END IF;
655 IF NOT FND_API.compatible_api_call (
656 l_api_version,
657 p_api_version,
658 l_api_name,
659 g_pkg_name
660 ) THEN
661 RAISE FND_API.g_exc_unexpected_error;
662 END IF;
663 x_return_status := FND_API.g_ret_sts_success;
664
665 ------------------------ delete ------------------------
666 IF (AMS_DEBUG_HIGH_ON) THEN
667
668 AMS_Utility_PVT.debug_message (l_full_name || ': Delete');
669 END IF;
670 -- Delete TL data
671 IF l_approval_details_rec.seeded_flag='N'
672 THEN
673 AMS_APPROVAL_DETAILS_PKG.DELETE_ROW (p_approval_detail_id);
674 ELSE
675 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
676 FND_MESSAGE.set_name ('AMS', 'AMS_API_SEED_DATA');
677 FND_MSG_PUB.add;
678 RAISE FND_API.g_exc_error;
679 END IF;
680 END IF;
681 -------------------- finish --------------------------
682 IF FND_API.to_boolean (p_commit) THEN
683 COMMIT;
684 END IF;
685 FND_MSG_PUB.count_and_get (
686 p_encoded => FND_API.g_false,
687 p_count => x_msg_count,
688 p_data => x_msg_data
689 );
690 IF (AMS_DEBUG_HIGH_ON) THEN
691
692 AMS_Utility_PVT.debug_message (l_full_name || ': End');
693 END IF;
694
695 EXCEPTION
696 WHEN FND_API.g_exc_error THEN
697 ROLLBACK TO Delete_approval_details;
698 x_return_status := FND_API.g_ret_sts_error;
699 FND_MSG_PUB.count_and_get (
700 p_encoded => FND_API.g_false,
701 p_count => x_msg_count,
702 p_data => x_msg_data
703 );
704
705 WHEN FND_API.g_exc_unexpected_error THEN
706 ROLLBACK TO Delete_approval_details;
707 x_return_status := FND_API.g_ret_sts_unexp_error ;
708 FND_MSG_PUB.count_and_get (
709 p_encoded => FND_API.g_false,
710 p_count => x_msg_count,
711 p_data => x_msg_data
712 );
713
714 WHEN OTHERS THEN
715 ROLLBACK TO Delete_approval_details;
716 x_return_status := FND_API.g_ret_sts_unexp_error ;
717 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
718 THEN
719 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
720 END IF;
721 FND_MSG_PUB.count_and_get (
722 p_encoded => FND_API.g_false,
723 p_count => x_msg_count,
724 p_data => x_msg_data
725 );
726 END Delete_approval_details;
727
728 --------------------------------------------------------------------
729 -- PROCEDURE
730 -- Lock_approval_details
731 --
732 -- PURPOSE
733 -- Lock a approval details entry.
734 --
735 -- PARAMETERS
736 -- p_approval_detail_id: the approval_detail
737 -- p_object_version: the object_version_number
738 --
739 -- ISSUES
740 --
741 -- NOTES
742 -- 1. Raise exception if the object_version_number doesn't match.
743 --------------------------------------------------------------------
744 PROCEDURE Lock_approval_details (
745 p_api_version IN NUMBER,
746 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
747 p_commit IN VARCHAR2 := FND_API.g_false,
748 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
749
750 x_return_status OUT NOCOPY VARCHAR2,
751 x_msg_count OUT NOCOPY NUMBER,
752 x_msg_data OUT NOCOPY VARCHAR2,
753
754 p_approval_detail_id IN NUMBER,
755 p_object_version IN NUMBER
756 ) IS
757 BEGIN
758 NULL;
759 END;
760
761 --------------------------------------------------------------------
762 -- PROCEDURE
763 -- Validate_approval_details
764 --
765 -- PURPOSE
766 -- Validate a approval_details entry.
767 --
768 -- PARAMETERS
769 -- p_approval_details_rec: the record representing AMS_APPROVAL_DETAILS_VL (without ROW_ID).
770 --
771 -- NOTES
772 -- 1. p_approval_details_rec should be the complete approval_details record.
773 -- There should not be any FND_API.g_miss_char/num/date in it.
774 -- 2. If FND_API.g_miss_char/num/date is in the record, then raise
775 -- an exception, as those values are not handled.
776 --------------------------------------------------------------------
777 PROCEDURE Validate_approval_details (
778 p_api_version IN NUMBER,
779 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
780 p_commit IN VARCHAR2 := FND_API.g_false,
781 p_validation_level IN NUMBER := FND_API.g_valid_level_full,
782
783 x_return_status OUT NOCOPY VARCHAR2,
784 x_msg_count OUT NOCOPY NUMBER,
785 x_msg_data OUT NOCOPY VARCHAR2,
786
787 p_approval_details_rec IN approval_details_rec_type
788 ) IS
789 L_API_VERSION CONSTANT NUMBER := 1.0;
790 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_approval_details';
791 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
792 l_return_status VARCHAR2(1);
793
794 BEGIN
795
796 --------------------- initialize -----------------------
797 IF (AMS_DEBUG_HIGH_ON) THEN
798
799 AMS_Utility_PVT.debug_message (l_full_name || ': Start');
800 END IF;
801 IF FND_API.to_boolean (p_init_msg_list) THEN
802 FND_MSG_PUB.initialize;
803 END IF;
804 IF NOT FND_API.compatible_api_call (
805 l_api_version,
806 p_api_version,
807 l_api_name,
808 g_pkg_name
809 ) THEN
810 RAISE FND_API.g_exc_unexpected_error;
811 END IF;
812 x_return_status := FND_API.g_ret_sts_success;
813
814 ---------------------- validate ------------------------
815 IF (AMS_DEBUG_HIGH_ON) THEN
816
817 AMS_Utility_PVT.debug_message (l_full_name || ': Check items');
818 END IF;
819 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
820 Check_Approval_details_Items (
821 p_approval_details_rec => p_approval_details_rec,
822 p_validation_mode => JTF_PLSQL_API.g_create,
823 x_return_status => l_return_status
824 );
825 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
826 RAISE FND_API.g_exc_unexpected_error;
827 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
828 RAISE FND_API.g_exc_error;
829 END IF;
830 END IF;
831 IF (AMS_DEBUG_HIGH_ON) THEN
832
833 AMS_Utility_PVT.debug_message (l_full_name || ': Check record');
834 END IF;
835 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
836 Check_Approval_details_Record (
837 p_approval_details_rec => p_approval_details_rec,
838 p_complete_rec => NULL,
839 x_return_status => l_return_status
840 );
841 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
842 RAISE FND_API.g_exc_unexpected_error;
843 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
844 RAISE FND_API.g_exc_error;
845 END IF;
846 END IF;
847 -----------------check the start date <= end date ------
848
849 -------------------- finish --------------------------
850 FND_MSG_PUB.count_and_get (
851 p_encoded => FND_API.g_false,
852 p_count => x_msg_count,
853 p_data => x_msg_data
854 );
855 IF (AMS_DEBUG_HIGH_ON) THEN
856
857 AMS_Utility_PVT.debug_message (l_full_name || ': End');
858 END IF;
859 EXCEPTION
860 WHEN FND_API.g_exc_error THEN
861 x_return_status := FND_API.g_ret_sts_error;
862 FND_MSG_PUB.count_and_get (
863 p_encoded => FND_API.g_false,
864 p_count => x_msg_count,
865 p_data => x_msg_data
866 );
867 WHEN FND_API.g_exc_unexpected_error THEN
868 x_return_status := FND_API.g_ret_sts_unexp_error ;
869 FND_MSG_PUB.count_and_get (
870 p_encoded => FND_API.g_false,
871 p_count => x_msg_count,
872 p_data => x_msg_data
873 );
874 WHEN OTHERS THEN
875 x_return_status := FND_API.g_ret_sts_unexp_error;
876 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_unexp_error)
877 THEN
878 FND_MSG_PUB.add_exc_msg (g_pkg_name, l_api_name);
879 END IF;
880 FND_MSG_PUB.count_and_get (
881 p_encoded => FND_API.g_false,
882 p_count => x_msg_count,
883 p_data => x_msg_data
884 );
885
886 END Validate_approval_details;
887
888 ---------------------------------------------------------------------
889 -- PROCEDURE
890 -- Check_approval_details_Items
891 --
892 -- PURPOSE
893 -- Perform the item level checking including unique keys,
894 -- required columns, foreign keys, domain constraints.
895 --
896 -- PARAMETERS
897 -- p_approval_details_rec: the record to be validated
898 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
899 ---------------------------------------------------------------------
900 PROCEDURE Check_approval_details_Items (
901 p_approval_details_rec IN approval_details_Rec_Type,
902 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
903 x_return_status OUT NOCOPY VARCHAR2
904 ) IS
905 BEGIN
906 --
907 IF (AMS_DEBUG_HIGH_ON) THEN
908
909 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items0');
910 END IF;
911
912 --IF p_validation_mode = JTF_PLSQL_API.g_create THEN --VMODUR 24-Jul-2002
913 --- some logic
914 If (p_approval_details_rec.start_date_active > p_approval_details_rec.end_date_active) THEN
915 --dbms_output.put_line('st > ed ');
916 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
917 FND_MESSAGE.set_name('AMS', 'AMS_APPR_DTL_SD_BFR_ED');
918 FND_MSG_PUB.add;
919 x_return_status := FND_API.g_ret_sts_error;
920 RETURN;
921 END IF;
922 END IF;
923
924 If (p_approval_details_rec.approval_type = 'BUDGET' )THEN
925 -- if (p_approval_details_rec.approval_object = 'CSCH') THEN -- R12
926 -- if Is_Usage_Lite(p_approval_details_rec.custom_setup_id) = FND_API.g_false THEN -- R12
927 if((p_approval_details_rec.approval_limit_from is NULL
928 or p_approval_details_rec.approval_limit_from = FND_API.g_miss_num)
929 AND(p_approval_details_rec.approval_limit_to is NULL
930 OR p_approval_details_rec.approval_limit_to = FND_API.g_miss_num))THEN
931 -- dbms_output.put_line('AMS_APPR_BGT_NO_MIN_AMT');
932 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
933 FND_MESSAGE.set_name('AMS', 'AMS_APPR_BGT_NO_MIN_AMT');
934 FND_MSG_PUB.add;
935 x_return_status := FND_API.g_ret_sts_error;
936 RETURN;
937 END IF;
938 END IF;
939 -- END IF;
940 -- END IF;
941 END IF;
942
943 IF (AMS_DEBUG_HIGH_ON) THEN
944
945
946
947 AMS_Utility_PVT.debug_message ('Checking for the -ve max amounts');
948
949 END IF;
950 -- For Claims, negative max amount is OK
951 If (p_approval_details_rec.approval_limit_to IS NOT NULL
952 AND p_approval_details_rec.approval_limit_to <> FND_API.g_miss_num
953 AND p_approval_details_rec.approval_limit_to < 0
954 AND p_approval_details_rec.approval_object <> 'CLAM')
955 THEN
956 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
957 -- FND_MESSAGE.set_name('AMS', 'AMS_APPR_DTL_NO_CURR');
958 FND_MESSAGE.set_name('AMS', 'AMS_APPR_DTL_MAX_AMT_LS_ZERO');
959 FND_MSG_PUB.add;
960 x_return_status := FND_API.g_ret_sts_error;
961 RETURN;
962 END IF;
963 END IF;
964
965 IF (AMS_DEBUG_HIGH_ON) THEN
966
967
968
969 AMS_Utility_PVT.debug_message ('Checking for the -ve min amounts');
970
971 END IF;
972 -- For Claims, negative min amount is OK
973 If (p_approval_details_rec.approval_limit_from IS NOT NULL
974 AND p_approval_details_rec.approval_limit_from <> FND_API.g_miss_num
975 AND p_approval_details_rec.approval_limit_from < 0
976 AND p_approval_details_rec.approval_object <> 'CLAM')
977 THEN
978 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
979 FND_MESSAGE.set_name('AMS', 'AMS_APPR_DTL_MIN_AMT_LS_ZERO');
980 --dbms_output.put_line('AMS_APPR_DTL_MIN_AMT_LS_ZERO');
981 FND_MSG_PUB.add;
982 x_return_status := FND_API.g_ret_sts_error;
983 RETURN;
984 END IF;
985 END IF;
986
987 IF (AMS_DEBUG_HIGH_ON) THEN
988
989
990
991 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items1');
992
993 END IF;
994 If (p_approval_details_rec.approval_limit_from >= p_approval_details_rec.approval_limit_to) THEN
995 --dbms_output.put_line('AMS_APPR_MIN_MAX_AMT_ERR');
996 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
997 FND_MESSAGE.set_name('AMS', 'AMS_APPR_MIN_MAX_AMT_ERR');
998 FND_MSG_PUB.add;
999 x_return_status := FND_API.g_ret_sts_error;
1000 RETURN;
1001 END IF;
1002 END IF;
1003
1004 IF (AMS_DEBUG_HIGH_ON) THEN
1005
1006
1007
1008 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items2');
1009
1010 END IF;
1011 If (((p_approval_details_rec.approval_limit_to IS NOT NULL
1012 AND p_approval_details_rec.approval_limit_to <> FND_API.g_miss_num)
1013 OR (p_approval_details_rec.approval_limit_from IS NOT NULL
1014 AND p_approval_details_rec.approval_limit_from <> FND_API.g_miss_num))
1015 AND (p_approval_details_rec.currency_code is NULL
1016 OR p_approval_details_rec.currency_code = FND_API.g_miss_char)) THEN
1017 --dbms_output.put_line('AMS_APPR_DTL_NO_CURR');
1018 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1019 FND_MESSAGE.set_name('AMS', 'AMS_APPR_DTL_NO_CURR');
1020 FND_MSG_PUB.add;
1021 x_return_status := FND_API.g_ret_sts_error;
1022 RETURN;
1023 END IF;
1024 END IF;
1025 --END IF; -- VMODUR 24-Jul-2002
1026
1027
1028 --- some logic
1029 IF (AMS_DEBUG_HIGH_ON) THEN
1030
1031 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items3');
1032 END IF;
1033 -- Validate required items.
1034 Check_approval_dtls_Req_Items (
1035 p_approval_details_rec => p_approval_details_rec,
1036 x_return_status => x_return_status
1037 );
1038 IF x_return_status <> FND_API.g_ret_sts_success THEN
1039 RETURN;
1040 END IF;
1041 --
1042 IF (AMS_DEBUG_HIGH_ON) THEN
1043
1044 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items4');
1045 END IF;
1046 -- Validate uniqueness.
1047 Check_approval_dtls_UK_Items (
1048 p_approval_details_rec => p_approval_details_rec,
1049 p_validation_mode => p_validation_mode,
1050 x_return_status => x_return_status
1051 );
1052 IF x_return_status <> FND_API.g_ret_sts_success THEN
1053
1054 RETURN;
1055 END IF;
1056 IF (AMS_DEBUG_HIGH_ON) THEN
1057
1058 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items6');
1059 END IF;
1060 /* Check_approval_dtls_FK_Items(
1061 p_approval_details_rec => p_approval_details_rec,
1062 x_return_status => x_return_status
1063 );
1064
1065 IF x_return_status <> FND_API.g_ret_sts_success THEN
1066 RETURN;
1067 END IF;
1068 */
1069 IF (AMS_DEBUG_HIGH_ON) THEN
1070
1071 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items7');
1072 END IF;
1073 Check_approval_dtls_Lkup_Items (
1074 p_approval_details_rec => p_approval_details_rec,
1075 x_return_status => x_return_status
1076 );
1077 IF x_return_status <> FND_API.g_ret_sts_success THEN
1078 RETURN;
1079 END IF;
1080 IF (AMS_DEBUG_HIGH_ON) THEN
1081
1082 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items8');
1083 END IF;
1084 Check_approval_dtls_Flag_Items(
1085 p_approval_details_rec => p_approval_details_rec,
1086 x_return_status => x_return_status
1087 );
1088 IF x_return_status <> FND_API.g_ret_sts_success THEN
1089 RETURN;
1090 END IF;
1091 /*
1092 --- some logic
1093 IF (AMS_DEBUG_HIGH_ON) THEN
1094
1095 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items9');
1096 END IF;
1097 If (p_approval_details_rec.start_date_active > p_approval_details_rec.end_date_active) THEN
1098 --dbms_output.put_line('st > ed ');
1099 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1100 FND_MESSAGE.set_name('AMS', 'AMS_APPR_DTL_SD_BFR_ED');
1101 FND_MSG_PUB.add;
1102 x_return_status := FND_API.g_ret_sts_error;
1103 RETURN;
1104 END IF;
1105 END IF;
1106 IF (AMS_DEBUG_HIGH_ON) THEN
1107
1108 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items10');
1109 END IF;
1110 If (p_approval_details_rec.approval_type = 'BUDGET' )THEN
1111 if((p_approval_details_rec.approval_limit_from is NULL
1112 or p_approval_details_rec.approval_limit_from = FND_API.g_miss_num)
1113 AND(p_approval_details_rec.approval_limit_to is NULL
1114 OR p_approval_details_rec.approval_limit_to = FND_API.g_miss_num))THEN
1115 -- dbms_output.put_line('AMS_APPR_BGT_NO_MIN_AMT');
1116 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1117 FND_MESSAGE.set_name('AMS', 'AMS_APPR_BGT_NO_MIN_AMT');
1118 FND_MSG_PUB.add;
1119 x_return_status := FND_API.g_ret_sts_error;
1120 RETURN;
1121 END IF;
1122 END IF;
1123 END IF;
1124 IF (AMS_DEBUG_HIGH_ON) THEN
1125
1126 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items11');
1127 END IF;
1128 If (p_approval_details_rec.approval_limit_from >= p_approval_details_rec.approval_limit_to) THEN
1129 --dbms_output.put_line('AMS_APPR_MIN_MAX_AMT_ERR');
1130 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1131 FND_MESSAGE.set_name('AMS', 'AMS_APPR_MIN_MAX_AMT_ERR');
1132 FND_MSG_PUB.add;
1133 x_return_status := FND_API.g_ret_sts_error;
1134 RETURN;
1135 END IF;
1136 END IF;
1137 IF (AMS_DEBUG_HIGH_ON) THEN
1138
1139 AMS_Utility_PVT.debug_message ('Inside Check_approval_details_Items12');
1140 END IF;
1141 If (((p_approval_details_rec.approval_limit_to IS NOT NULL
1142 AND p_approval_details_rec.approval_limit_to <> FND_API.g_miss_num)
1143 OR (p_approval_details_rec.approval_limit_from IS NOT NULL
1144 AND p_approval_details_rec.approval_limit_from <> FND_API.g_miss_num))
1145 AND (p_approval_details_rec.currency_code is NULL
1146 OR p_approval_details_rec.currency_code = FND_API.g_miss_char)) THEN
1147 --dbms_output.put_line('AMS_APPR_DTL_NO_CURR');
1148 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1149 FND_MESSAGE.set_name('AMS', 'AMS_APPR_DTL_NO_CURR');
1150 FND_MSG_PUB.add;
1151 x_return_status := FND_API.g_ret_sts_error;
1152 RETURN;
1153 END IF;
1154 END IF;
1155 */
1156 END Check_approval_details_Items;
1157
1158 ---------------------------------------------------------------------
1159 -- PROCEDURE
1160 -- Check_approval_details_Record
1161 --
1162 -- PURPOSE
1163 -- Check the record level business rules.
1164 --
1165 -- PARAMETERS
1166 -- p_approval_details_rec: the record to be validated; may contain attributes
1167 -- as FND_API.g_miss_char/num/date
1168 -- p_complete_rec: the complete record after all "g_miss" items
1169 -- have been replaced by current database values
1170 ---------------------------------------------------------------------
1171 PROCEDURE Check_approval_details_Record (
1172 p_approval_details_rec IN approval_details_Rec_Type,
1173 p_complete_rec IN approval_details_Rec_Type := NULL,
1174 x_return_status OUT NOCOPY VARCHAR2
1175 ) IS
1176 l_start_date_active DATE;
1177 l_end_date_active DATE;
1178 BEGIN
1179 --
1180 -- Use local vars to reduce amount of typing.
1181 if p_complete_rec.start_date_active IS NOT NULL then
1182 l_start_date_active := p_complete_rec.start_date_active;
1183 else
1184 if p_approval_details_rec.start_date_active is NOT NULL AND
1185 p_approval_details_rec.start_date_active <> FND_API.g_miss_date then
1186 l_start_date_active := p_approval_details_rec.start_date_active;
1187 end if;
1188 end if;
1189 if p_complete_rec.end_date_active IS NOT NULL then
1190 l_end_date_active := p_complete_rec.end_date_active;
1191 else
1192 if p_approval_details_rec.end_date_active is NOT NULL AND
1193 p_approval_details_rec.end_date_active <> FND_API.g_miss_date then
1194 l_end_date_active := p_approval_details_rec.end_date_active;
1195 end if;
1196 end if;
1197 x_return_status := FND_API.G_RET_STS_SUCCESS;
1198 IF l_start_date_active IS NOT NULL AND l_end_date_active IS NOT NULL THEN
1199 IF l_start_date_active > l_end_date_active THEN
1200 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1201 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_FROMDT_GTR_TODT');
1202 FND_MSG_PUB.add;
1203 END IF;
1204 x_return_status := FND_API.g_ret_sts_error;
1205 RETURN;
1206 END IF;
1207 END IF;
1208 END Check_approval_details_Record;
1209 ---------------------------------------------------------------------
1210 -- PROCEDURE
1211 -- Init_approval_details_Rec
1212 --
1213 -- PURPOSE
1214 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
1215 ---------------------------------------------------------------------
1216 PROCEDURE Init_approval_details_Rec (
1217 x_approval_details_rec OUT NOCOPY approval_details_Rec_Type
1218 ) IS
1219 BEGIN
1220 x_approval_details_rec.approval_detail_id := FND_API.g_miss_num;
1221 x_approval_details_rec.start_date_active := FND_API.g_miss_date;
1222 x_approval_details_rec.end_date_active := FND_API.g_miss_date;
1223 x_approval_details_rec.object_version_number := FND_API.g_miss_num;
1224 --x_approval_details_rec.security_group_id := FND_API.g_miss_num;
1225 x_approval_details_rec.business_group_id := FND_API.g_miss_num;
1226 x_approval_details_rec.business_unit_id := FND_API.g_miss_num;
1227 x_approval_details_rec.organization_id := FND_API.g_miss_num;
1228 x_approval_details_rec.custom_setup_id := FND_API.g_miss_num;
1229 x_approval_details_rec.approval_object := FND_API.g_miss_char;
1230 x_approval_details_rec.approval_object_type := FND_API.g_miss_char;
1231 x_approval_details_rec.approval_type := FND_API.g_miss_char;
1232 x_approval_details_rec.approval_priority := FND_API.g_miss_char;
1233 x_approval_details_rec.approval_limit_to := FND_API.g_miss_num;
1234 x_approval_details_rec.approval_limit_from := FND_API.g_miss_num;
1235 x_approval_details_rec.seeded_flag := FND_API.g_miss_char;
1236 x_approval_details_rec.active_flag := FND_API.g_miss_char;
1237 x_approval_details_rec.currency_code := FND_API.g_miss_char;
1238 x_approval_details_rec.user_country_code := FND_API.g_miss_char;
1239 x_approval_details_rec.name := FND_API.g_miss_char;
1240 x_approval_details_rec.description := FND_API.g_miss_char;
1241 END;
1242
1243 ---------------------------------------------------------------------
1244 -- PROCEDURE
1245 -- Complete_approval_details_Rec
1246 --
1247 -- PURPOSE
1248 -- For Update_approval_details, some attributes may be passed in as
1249 -- FND_API.g_miss_char/num/date if the user doesn't want to
1250 -- update those attributes. This procedure will replace the
1251 -- "g_miss" attributes with current database values.
1252 --
1253 -- PARAMETERS
1254 -- p_approval_details_rec: the record which may contain attributes as
1255 -- FND_API.g_miss_char/num/date
1256 -- x_complete_rec: the complete record after all "g_miss" items
1257 -- have been replaced by current database values
1258 -- Changes have been made as g_miss_xxxx is no longer used
1259 ---------------------------------------------------------------------
1260 PROCEDURE Complete_approval_details_Rec (
1261 p_approval_details_rec IN approval_details_Rec_Type,
1262 x_complete_rec OUT NOCOPY approval_details_Rec_Type
1263 ) IS
1264 CURSOR c_approval_details IS
1265 SELECT *
1266 FROM ams_approval_details_vl
1267 WHERE approval_detail_id = p_approval_details_rec.approval_detail_id;
1268 --
1269 -- This is the only exception for using %ROWTYPE.
1270 -- We are selecting from the VL view, which may
1271 -- have some denormalized columns as compared to
1272 -- the base tables.
1273 l_approval_details_rec c_approval_details%ROWTYPE;
1274 BEGIN
1275 x_complete_rec := p_approval_details_rec;
1276 OPEN c_approval_details;
1277 FETCH c_approval_details INTO l_approval_details_rec;
1278 IF c_approval_details%NOTFOUND THEN
1279 CLOSE c_approval_details;
1280 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1281 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1282 FND_MSG_PUB.add;
1283 END IF;
1284 RAISE FND_API.g_exc_error;
1285 END IF;
1286 CLOSE c_approval_details;
1287 --
1288 If p_approval_details_rec.approval_detail_id is null THEN
1289 x_complete_rec.approval_detail_id := l_approval_details_rec.approval_detail_id;
1290 END IF;
1291 -- Don't fill in dates as they can be null
1292 /*
1293 IF p_approval_details_rec.start_date_active is null THEN
1294 x_complete_rec.start_date_active := l_approval_details_rec.start_date_active;
1295 END IF;
1296
1297 IF p_approval_details_rec.end_date_active is null THEN
1298 x_complete_rec.end_date_active := l_approval_details_rec.end_date_active;
1299 END IF;
1300 */
1301 IF p_approval_details_rec.object_version_number is null THEN
1302 x_complete_rec.object_version_number := l_approval_details_rec.object_version_number;
1303 END IF;
1304
1305 /* IF p_approval_details_rec.security_group_id is null THEN
1306 x_complete_rec.security_group_id := l_approval_details_rec.security_group_id;
1307 END IF;
1308 */
1309
1310 IF p_approval_details_rec.business_group_id is null THEN
1311 x_complete_rec.business_group_id := l_approval_details_rec.business_group_id;
1312 END IF;
1313
1314 IF p_approval_details_rec.business_unit_id is null THEN
1315 x_complete_rec.business_unit_id := l_approval_details_rec.business_unit_id;
1316 END IF;
1317
1318 IF p_approval_details_rec.organization_id is null THEN
1319 x_complete_rec.organization_id := l_approval_details_rec.organization_id;
1320 END IF;
1321
1322 IF p_approval_details_rec.custom_setup_id is null THEN
1323 x_complete_rec.custom_setup_id := l_approval_details_rec.custom_setup_id;
1324 END IF;
1325
1326 IF p_approval_details_rec.approval_object is null THEN
1327 x_complete_rec.approval_object := l_approval_details_rec.approval_object;
1328 END IF;
1329
1330 IF p_approval_details_rec.approval_object_type is null THEN
1331 x_complete_rec.approval_object_type := l_approval_details_rec.approval_object_type;
1332 END IF;
1333
1334 IF p_approval_details_rec.approval_type is null THEN
1335 x_complete_rec.approval_type := l_approval_details_rec.approval_type;
1336 END IF;
1337
1338 IF p_approval_details_rec.approval_priority is null THEN
1339 x_complete_rec.approval_priority := l_approval_details_rec.approval_priority;
1340 END IF;
1341 -- Limits can be null
1342 /*
1343 IF p_approval_details_rec.approval_limit_to is null THEN
1344 x_complete_rec.approval_limit_to := l_approval_details_rec.approval_limit_to;
1345 END IF;
1346
1347 IF p_approval_details_rec.approval_limit_from is null THEN
1348 x_complete_rec.approval_limit_from := l_approval_details_rec.approval_limit_from;
1349 END IF;
1350 */
1351 IF p_approval_details_rec.seeded_flag is null THEN
1352 x_complete_rec.seeded_flag := l_approval_details_rec.seeded_flag;
1353 END IF;
1354
1355 IF p_approval_details_rec.active_flag is null THEN
1356 x_complete_rec.active_flag := l_approval_details_rec.active_flag;
1357 END IF;
1358
1359 IF p_approval_details_rec.currency_code is null THEN
1360 x_complete_rec.currency_code := l_approval_details_rec.currency_code;
1361 END IF;
1362 /*
1363 IF p_approval_details_rec.user_country_code is null THEN
1364 x_complete_rec.user_country_code := l_approval_details_rec.user_country_code;
1365 END IF;
1366 */
1367
1368 IF p_approval_details_rec.name is null THEN
1369 x_complete_rec.name := l_approval_details_rec.name;
1370 END IF;
1371 -- Description can be null
1372 -- Bug 3737174
1373 /*
1374 IF p_approval_details_rec.description is null THEN
1375 x_complete_rec.description := l_approval_details_rec.description;
1376 END IF;
1377 */
1378 END Complete_approval_details_Rec;
1379
1380 ---------------------------------------------------------
1381 -- Function Compare Columns
1382 -- added sugupta 05/22/2000
1383 -- this procedure will compare that no values have been modified for seeded statuses
1384 -----------------------------------------------------------------
1385 FUNCTION compare_columns(
1386 p_approval_details_rec in approval_details_Rec_Type
1387 )
1388 RETURN VARCHAR2
1389 IS
1390 l_count NUMBER := 0;
1391
1392 BEGIN
1393 IF (AMS_DEBUG_HIGH_ON) THEN
1394
1395 AMS_UTILITY_PVT.DEBUG_MESSAGE('sTART DATE:'|| to_char( p_approval_details_rec.start_date_active,'DD_MON_YYYY'));
1396 END IF;
1397 IF (AMS_DEBUG_HIGH_ON) THEN
1398
1399 AMS_UTILITY_PVT.DEBUG_MESSAGE('end DATE:'|| to_char( p_approval_details_rec.end_Date_active,'DD-MON-YYYY'));
1400 END IF;
1401
1402 if p_approval_details_rec.start_date_active is NOT NULL then
1403 if p_approval_details_rec.end_Date_active is NOT NULL then
1404 BEGIN
1405 select 1 into l_count
1406 from AMS_APPROVAL_DETAILS_VL
1407 where approval_detail_id =p_approval_details_rec.approval_detail_id
1408 and name = p_approval_details_rec.name
1409 and description = p_approval_details_rec.description
1410 and start_date_active = p_approval_details_rec.start_date_active
1411 and end_date_active = p_approval_details_rec.end_Date_active
1412 -- and security_group_id = p_approval_details_rec.security_group_id
1413 and business_group_id = p_approval_details_rec.business_group_id
1414 and user_country_code = p_approval_details_rec.user_country_code
1415 and organization_id = p_approval_details_rec.organization_id
1416 and custom_setup_id = p_approval_details_rec.custom_setup_id
1417 and approval_object = p_approval_details_rec.approval_object
1418 and approval_object_type = p_approval_details_rec.approval_object_type
1419 and approval_type = p_approval_details_rec.approval_type
1420 and approval_priority = p_approval_details_rec.approval_priority
1421 and approval_limit_to = p_approval_details_rec.approval_limit_to
1422 and approval_limit_from = p_approval_details_rec.approval_limit_from
1423 and seeded_flag = 'Y'
1424 and active_flag = 'Y'
1425 and currency_code = p_approval_details_rec.currency_code;
1426 EXCEPTION
1427 WHEN NO_DATA_FOUND THEN
1428 l_count := 0;
1429 END;
1430 else -- for end date
1431 BEGIN
1432 select 1 into l_count
1433 from AMS_APPROVAL_DETAILS_VL
1434 where approval_detail_id =p_approval_details_rec.approval_detail_id
1435 and name = p_approval_details_rec.name
1436 and description = p_approval_details_rec.description
1437 and start_date_active = p_approval_details_rec.start_date_active
1438 and end_date_active = p_approval_details_rec.end_Date_active
1439 -- and security_group_id = p_approval_details_rec.security_group_id
1440 and business_group_id = p_approval_details_rec.business_group_id
1441 and user_country_code = p_approval_details_rec.user_country_code
1442 and organization_id = p_approval_details_rec.organization_id
1443 and custom_setup_id = p_approval_details_rec.custom_setup_id
1444 and approval_object = p_approval_details_rec.approval_object
1445 and approval_object_type = p_approval_details_rec.approval_object_type
1446 and approval_type = p_approval_details_rec.approval_type
1447 and approval_priority = p_approval_details_rec.approval_priority
1448 and approval_limit_to = p_approval_details_rec.approval_limit_to
1449 and approval_limit_from = p_approval_details_rec.approval_limit_from
1450 and seeded_flag = 'Y'
1451 and active_flag = 'Y'
1452 and currency_code = p_approval_details_rec.currency_code;
1453 EXCEPTION
1454 WHEN NO_DATA_FOUND THEN
1455 l_count := 0;
1456 END;
1457 end if; -- for end date
1458 else
1459 BEGIN
1460 select 1 into l_count
1461 from AMS_APPROVAL_DETAILS_VL
1462 where approval_detail_id =p_approval_details_rec.approval_detail_id
1463 and name = p_approval_details_rec.name
1464 and description = p_approval_details_rec.description
1465 and start_date_active = p_approval_details_rec.start_date_active
1466 and end_date_active = p_approval_details_rec.end_Date_active
1467 -- and security_group_id = p_approval_details_rec.security_group_id
1468 and business_group_id = p_approval_details_rec.business_group_id
1469 and user_country_code = p_approval_details_rec.user_country_code
1470 and organization_id = p_approval_details_rec.organization_id
1471 and custom_setup_id = p_approval_details_rec.custom_setup_id
1472 and approval_object = p_approval_details_rec.approval_object
1473 and approval_object_type =p_approval_details_rec.approval_object_type
1474 and approval_type = p_approval_details_rec.approval_type
1475 and approval_priority = p_approval_details_rec.approval_priority
1476 and approval_limit_to = p_approval_details_rec.approval_limit_to
1477 and approval_limit_from = p_approval_details_rec.approval_limit_from
1478 and seeded_flag = 'Y'
1479 and active_flag = 'Y'
1480 and currency_code = p_approval_details_rec.currency_code;
1481 EXCEPTION
1482 WHEN NO_DATA_FOUND THEN
1483 l_count := 0;
1484 END;
1485 end if;
1486 IF l_count = 0 THEN
1487 RETURN FND_API.g_false;
1488 ELSE
1489 RETURN FND_API.g_true;
1490 END IF;
1491 END compare_columns;
1492
1493 ---------------------------------------------------------
1494 -- Function seed_needs_update
1495 -- added sugupta 05/22/2000
1496 -- this procedure will look at enabled flag and determine if update is needed
1497 -----------------------------------------------------------------
1498 FUNCTION seed_needs_update(
1499 p_approval_details_rec in approval_details_Rec_Type
1500 )
1501 RETURN VARCHAR2
1502 IS
1503 l_count NUMBER := 0;
1504
1505 BEGIN
1506 BEGIN
1507 select 1 into l_count
1508 from AMS_APPROVAL_DETAILS
1509 where approval_detail_id = p_approval_details_rec.approval_detail_id
1510 and seeded_flag = 'Y';
1511 EXCEPTION
1512 WHEN NO_DATA_FOUND THEN
1513 l_count := 0;
1514 END;
1515
1516 IF l_count = 0 THEN
1517 RETURN FND_API.g_true; -- needs update
1518 ELSE
1519 RETURN FND_API.g_false; -- doesnt need update
1520 END IF;
1521 END seed_needs_update;
1522
1523 -------------------------------------------------------------
1524 -- Check_Approval_Dtls_Req_Items
1525 -------------------------------------------------------------
1526 PROCEDURE Check_Approval_Dtls_Req_Items (
1527 p_approval_details_rec IN Approval_Details_Rec_Type,
1528 x_return_status OUT NOCOPY VARCHAR2
1529 ) IS
1530 BEGIN
1531
1532 IF p_approval_details_rec.name IS NULL THEN
1533 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1534 FND_MESSAGE.set_name ('AMS', 'AMS_STATUS_NO_NAME');
1535 FND_MSG_PUB.add;
1536 END IF;
1537 x_return_status := FND_API.g_ret_sts_error;
1538 RETURN;
1539 END IF;
1540
1541 END;
1542 -------------------------------------------------------------
1543 -- Check_Approval_Dtls_UK_Items
1544 -------------------------------------------------------------
1545 PROCEDURE Check_Approval_Dtls_UK_Items (
1546 p_approval_details_rec IN Approval_Details_Rec_Type,
1547 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1548 x_return_status OUT NOCOPY VARCHAR2
1549 )IS
1550
1551 l_valid_flag VARCHAR2(1);
1552 /*
1553 l_dummy NUMBER;
1554 l_rec approval_details_Rec_Type;
1555 l_exists1 NUMBER := 1;
1556 l_exists2 NUMBER := 1;
1557
1558
1559
1560 CURSOR c_approval_details(id_in IN NUMBER) IS
1561 SELECT *
1562 FROM ams_approval_details_vl
1563 WHERE approval_detail_id = id_in;
1564
1565 cursor c_appr_name1(name_in IN VARCHAR2) IS
1566 SELECT 1 FROM DUAL WHERE EXISTS (select 1 from AMS_APPROVAL_DETAILS_VL
1567 where name = name_in);
1568
1569 cursor c_appr_name2(name_in IN VARCHAR2, id_in IN NUMBER) IS
1570 SELECT 1 FROM DUAL WHERE EXISTS (select 1 from AMS_APPROVAL_DETAILS_VL
1571 where name = name_in
1572 and approval_detail_id = id_in);
1573
1574 l_approval_details_rec c_approval_details%ROWTYPE;
1575 */
1576 cursor c_rule_name(name_in IN VARCHAR2, id_in IN NUMBER) IS
1577 SELECT '1' FROM DUAL WHERE EXISTS (select 1 from AMS_APPROVAL_DETAILS_VL
1578 where name = name_in
1579 and approval_detail_id <> id_in);
1580
1581 BEGIN
1582 IF (AMS_DEBUG_HIGH_ON) THEN
1583
1584 AMS_UTILITY_PVT.debug_message('Private API:Check_Approval_Dtls_UK_Items');
1585 END IF;
1586 x_return_status := FND_API.G_RET_STS_SUCCESS;
1587
1588 --Validate unique approval_id
1589 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1590 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1591 'ams_approval_details',
1592 'approval_detail_id = ''' || p_approval_details_rec.approval_detail_id ||'''');
1593 ELSE
1594 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1595 'ams_approval_details',
1596 'approval_detail_id = ''' || p_approval_details_rec.approval_detail_id ||
1597 ''' AND approval_detail_id <> ' || p_approval_details_rec.approval_detail_id);
1598 END IF;
1599
1600 IF l_valid_flag = FND_API.g_false THEN
1601 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1602 THEN
1603 FND_MESSAGE.set_name('AMS', 'AMS_APPR_DUPLICATE_ID');
1604 FND_MSG_PUB.add;
1605 END IF;
1606 x_return_status := FND_API.g_ret_sts_error;
1607 RETURN;
1608 END IF;
1609
1610 --Validate unique apporval rule name
1611 -- Commented due to Bug 2776795 ams_utility_pvt check_uniqueness cannot handle names containing
1612 -- operators like 'AND'
1613 /*
1614
1615 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1616 'ams_approval_details_vl', 'NAME = ''' || p_approval_details_rec.name ||
1617 ''' AND approval_detail_id <> ' || NVL(p_approval_details_rec.approval_detail_id,FND_API.G_MISS_NUM));
1618 */
1619
1620 OPEN c_rule_name(p_approval_details_rec.name,NVL(p_approval_details_rec.approval_detail_id,FND_API.G_MISS_NUM));
1621 FETCH c_rule_name INTO l_valid_flag;
1622 IF c_rule_name%NOTFOUND THEN
1623 NULL;
1624 END IF;
1625 CLOSE c_rule_name;
1626
1627
1628 IF l_valid_flag = '1' THEN
1629
1630 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1631 IF (AMS_DEBUG_HIGH_ON) THEN
1632
1633 AMS_UTILITY_PVT.debug_message('Check_Approval_Dtls_UK_Items: Inside error');
1634 END IF;
1635 FND_MESSAGE.set_name('AMS', 'AMS_DUP_NAME');
1636 FND_MSG_PUB.add;
1637 END IF;
1638 x_return_status := FND_API.g_ret_sts_error;
1639 RETURN;
1640 END IF;
1641
1642
1643 /*
1644 IF p_validation_mode = JTF_PLSQL_API.g_create
1645 AND p_approval_details_rec.approval_detail_id IS NOT NULL THEN
1646 OPEN c_appr_name2(P_approval_details_rec.name,P_approval_details_rec.approval_detail_id);
1647 fetch c_appr_name2 into l_dummy;
1648 close c_appr_name2;
1649
1650 IF l_dummy = 1 THEN
1651 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1652 FND_MESSAGE.set_name ('AMS', 'AMS_DUP_NAME');
1653 FND_MSG_PUB.add;
1654 END IF;
1655 x_return_status := FND_API.g_ret_sts_error;
1656 RETURN;
1657 END IF;
1658
1659 END IF;
1660
1661
1662 -- check if NAME is UNIQUE
1663 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1664 OPEN c_appr_name1(P_approval_details_rec.name);
1665 fetch c_appr_name1 into l_dummy;
1666 close c_appr_name1;
1667 IF l_dummy <> 1 THEN
1668 l_valid_flag := FND_API.g_false;
1669 END IF;
1670 ELSE
1671 OPEN c_appr_name2(P_approval_details_rec.name, P_approval_details_rec.approval_detail_id);
1672 fetch c_appr_name2 into l_dummy;
1673 close c_appr_name2;
1674 IF l_dummy <> 1 THEN
1675 l_valid_flag := FND_API.g_false;
1676 END IF;
1677 END IF;
1678
1679
1680 IF l_valid_flag = FND_API.g_false THEN
1681 IF FND_MSG_PUB.check_msg_level (FND_MSG_PUB.g_msg_lvl_error) THEN
1682 FND_MESSAGE.set_name ('AMS', 'AMS_DUP_NAME');
1683 FND_MSG_PUB.add;
1684 END IF;
1685 x_return_status := FND_API.g_ret_sts_error;
1686 RETURN;
1687 END IF;
1688
1689 */
1690 /* Check dates over lap. If Approval Type is Concept, we will check only for Dates overlap. If it is
1691 Budget, we call Amounts Over Lap function with in Dates Overlap function which checks for Budget
1692 Overlap.
1693 */
1694
1695 IF( Check_Dates_Overlap ( p_approval_details_rec , p_validation_mode)) = FND_API.g_true THEN
1696 RAISE FND_API.g_exc_error;
1697 END IF;
1698
1699 END Check_Approval_Dtls_UK_Items;
1700 -------------------------------------------------------------
1701 -- Check_Approval_Dtls_FK_Items
1702 -------------------------------------------------------------
1703 PROCEDURE Check_Approval_Dtls_FK_Items (
1704 p_approval_details_rec IN Approval_Details_Rec_Type,
1705 x_return_status OUT NOCOPY VARCHAR2
1706 )IS
1707 l_lookup_type varchar2(30);
1708 l_count number;
1709 l_meaning varchar2(80);
1710 l_dummy number;
1711
1712 CURSOR c_get_org(cur_organization_id number,
1713 cur_business_group_id number,
1714 cur_org_type varchar2) is
1715 SELECT count(1)
1716 FROM hr_organization_units hou
1717 WHERE hou.organization_id = cur_organization_id
1718 --AND hou.business_group_id = cur_business_group_id
1719 AND hou.type = nvl(cur_org_type,hou.type)
1720 AND sysdate between hou.date_from and nvl(hou.date_to,sysdate +1 );
1721
1722 Cursor c_appr_objt_type_exists(id_in IN NUMBER) IS
1723 SELECT 1 FROM dual
1724 WHERE EXISTS (SELECT 1 FROM ams_categories_b
1725 WHERE enabled_flag = 'Y'
1726 AND arc_category_created_for = 'FUND'
1727 AND category_id = id_in);
1728
1729 cursor c_cust_setup_id_c_exists(id_in IN NUMBER) IS
1730 SELECT 1 from dual
1731 where EXISTS (select 1 FROM ams_custom_setups_b
1732 WHERE enabled_flag = 'Y'
1733 AND object_type IN ('RCAM','ECAM'));
1734
1735 cursor c_cust_setup_id_exists(id_in IN NUMBER, p_lookup_code IN VARCHAR2) IS
1736 SELECT 1 from dual
1737 where EXISTS (select 1 FROM ams_custom_setups_b
1738 WHERE enabled_flag = 'Y'
1739 AND object_type = p_lookup_code);
1740 BEGIN
1741 x_return_status := FND_API.G_RET_STS_SUCCESS;
1742 -- check approval_object
1743 IF (AMS_DEBUG_HIGH_ON) THEN
1744
1745 AMS_Utility_PVT.debug_message ('Inside Check_FK__Items0');
1746 END IF;
1747 If p_approval_details_rec.approval_object <> FND_API.g_miss_char THEN
1748 ams_utility_pvt.get_lookup_meaning( 'AMS_APPROVAL_RULE_FOR',
1749 p_approval_details_rec.approval_object,
1750 x_return_status,
1751 l_meaning
1752 );
1753 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1754 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1755 FND_MESSAGE.set_name('AMS', 'AMS_BAD_APPROVAL_OBJECT_TYPE');
1756 FND_MSG_PUB.add;
1757 RETURN;
1758 END IF;
1759 END IF;
1760 END IF;
1761 IF (AMS_DEBUG_HIGH_ON) THEN
1762
1763 AMS_Utility_PVT.debug_message ('Inside Check_FK__Items1');
1764 END IF;
1765 If p_approval_details_rec.approval_type <> FND_API.g_miss_char THEN
1766 ams_utility_pvt.get_lookup_meaning( 'AMS_APPROVAL_TYPE',
1767 p_approval_details_rec.approval_type,
1768 x_return_status,
1769 l_meaning
1770 );
1771 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1772 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1773 FND_MESSAGE.set_name('AMS', 'AMS_INVALID_APPROVAL_TYPE');
1774 FND_MSG_PUB.add;
1775 RETURN;
1776 END IF;
1777 END IF;
1778 END IF;
1779 IF (AMS_DEBUG_HIGH_ON) THEN
1780
1781 AMS_Utility_PVT.debug_message ('Inside Check_FK__Items2');
1782 END IF;
1783 If (p_approval_details_rec.approval_object_type <> FND_API.g_miss_char
1784 AND p_approval_details_rec.approval_object_type IS NOT NULL) THEN
1785 IF (AMS_DEBUG_HIGH_ON) THEN
1786
1787 AMS_Utility_PVT.debug_message ('Inside Check_FK__Items21');
1788 END IF;
1789 IF p_approval_details_rec.approval_object = 'FUND' then
1790 IF (AMS_DEBUG_HIGH_ON) THEN
1791
1792 AMS_Utility_PVT.debug_message ('Inside Check_FK__Items22');
1793 END IF;
1794 open c_appr_objt_type_exists(to_number(p_approval_details_rec.approval_object_type));
1795 fetch c_appr_objt_type_exists INTO l_dummy;
1796 close c_appr_objt_type_exists;
1797 IF l_dummy <> 1 THEN
1798 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1799 FND_MESSAGE.set_name('AMS', 'AMS_BAD_APPROVAL_OBJECT_TYPE');
1800 FND_MSG_PUB.add;
1801 x_return_status := FND_API.g_ret_sts_error;
1802 RETURN;
1803 END IF;
1804 END IF;
1805 ELSIF p_approval_details_rec.approval_object = 'CAMP' OR
1806 p_approval_details_rec.approval_object = 'EVEH' OR
1807 p_approval_details_rec.approval_object = 'EONE' OR
1808 p_approval_details_rec.approval_object = 'EVEO' then
1809 IF (AMS_DEBUG_HIGH_ON) THEN
1810
1811 AMS_Utility_PVT.debug_message ('Inside Check_FK__Items3');
1812 END IF;
1813 IF p_approval_details_rec.approval_object = 'CAMP' then
1814 l_lookup_type := 'AMS_MEDIA_TYPE';
1815 ELSE
1816 l_lookup_type := 'AMS_EVENT_TYPE';
1817 END IF;
1818 ams_utility_pvt.get_lookup_meaning( l_lookup_type,
1819 p_approval_details_rec.approval_object_type,
1820 x_return_status,
1821 l_meaning
1822 );
1823 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1824 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1825 FND_MESSAGE.set_name('AMS', 'AMS_BAD_APPROVAL_OBJECT_TYPE');
1826 FND_MSG_PUB.add;
1827 RETURN;
1828 END IF;
1829 END IF;
1830 END IF;
1831 END iF;
1832 IF (AMS_DEBUG_HIGH_ON) THEN
1833
1834 AMS_Utility_PVT.debug_message ('Inside Check_FK__Items4');
1835 END IF;
1836 IF (p_approval_details_rec.approval_priority IS NOT NULL AND
1837 p_approval_details_rec.approval_priority <> FND_API.g_miss_char) THEN
1838 IF p_approval_details_rec.approval_object = 'CAMP' OR
1839 p_approval_details_rec.approval_object = 'EVEH' OR
1840 p_approval_details_rec.approval_object = 'EVEO' then
1841 ams_utility_pvt.get_lookup_meaning( 'AMS_PRIORITY',
1842 p_approval_details_rec.approval_priority,
1843 x_return_status,
1844 l_meaning
1845 );
1846 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1847 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1848 FND_MESSAGE.set_name('AMS', 'AMS_APPR_NO_PRIORITY_LKUP');
1849 FND_MSG_PUB.add;
1850 RETURN;
1851 END IF;
1852 END IF;
1853 END IF;
1854 END iF;
1855 IF (AMS_DEBUG_HIGH_ON) THEN
1856
1857 AMS_Utility_PVT.debug_message ('Inside Check_FK__Items5');
1858 END IF;
1859
1860 If p_approval_details_rec.custom_setup_id <> FND_API.g_miss_num THEN
1861 IF p_approval_details_rec.approval_object <> FND_API.g_miss_char THEN
1862 IF p_approval_details_rec.approval_object = 'CAMP' THEN
1863 open c_cust_setup_id_c_exists(p_approval_details_rec.custom_setup_id);
1864 fetch c_cust_setup_id_c_exists INTO l_dummy;
1865 close c_cust_setup_id_c_exists;
1866 IF l_dummy <> 1 THEN
1867 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1868 FND_MESSAGE.set_name('AMS', 'AMS_BAD_CUSTOM_SETUP_ID');
1869 FND_MSG_PUB.add;
1870 x_return_status := FND_API.g_ret_sts_error;
1871 RETURN;
1872 END IF;
1873 END IF;
1874 ELSIF p_approval_details_rec.approval_object = 'EVEH'
1875 OR p_approval_details_rec.approval_object = 'EVEO'
1876 OR p_approval_details_rec.approval_object = 'FUND' THEN
1877 open c_cust_setup_id_exists(p_approval_details_rec.custom_setup_id, p_approval_details_rec.approval_object);
1878 fetch c_cust_setup_id_exists INTO l_dummy;
1879 close c_cust_setup_id_exists;
1880 IF l_dummy <> 1 THEN
1881 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1882 FND_MESSAGE.set_name('AMS', 'AMS_BAD_CUSTOM_SETUP_ID');
1883 FND_MSG_PUB.add;
1884 x_return_status := FND_API.g_ret_sts_error;
1885 RETURN;
1886 END IF;
1887 END IF;
1888 END IF;
1889 END IF;
1890 END IF;
1891 IF (AMS_DEBUG_HIGH_ON) THEN
1892
1893 AMS_Utility_PVT.debug_message ('Inside Check_FK__Items6');
1894 END IF;
1895 IF p_approval_details_rec.business_unit_id is not null then
1896 OPEN c_get_org(p_approval_details_rec.business_unit_id ,
1897 p_approval_details_rec.business_group_id, 'BU') ;
1898 fetch c_get_org into l_count;
1899 close c_get_org;
1900 IF l_count = 0 then
1901 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1902 FND_MESSAGE.set_name('AMS', 'AMS_BAD_BU_ID');
1903 FND_MSG_PUB.add;
1904 x_return_status := FND_API.g_ret_sts_error;
1905 RETURN;
1906 END IF;
1907 END IF;
1908 END IF;
1909 IF (AMS_DEBUG_HIGH_ON) THEN
1910
1911 AMS_Utility_PVT.debug_message ('Inside Check_FK__Items7');
1912 END IF;
1913 IF p_approval_details_rec.organization_id is not null then
1914 OPEN c_get_org(p_approval_details_rec.organization_id ,
1915 p_approval_details_rec.business_group_id, '') ;
1916 fetch c_get_org into l_count;
1917 close c_get_org;
1918 IF l_count = 0 then
1919 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1920 FND_MESSAGE.set_name('AMS', 'AMS_BAD_ORG_ID');
1921 FND_MSG_PUB.add;
1922 x_return_status := FND_API.g_ret_sts_error;
1923 RETURN;
1924 END IF;
1925 END IF;
1926 END IF;
1927 END Check_Approval_Dtls_FK_Items;
1928 -------------------------------------------------------------
1929 -- Check_Approval_Dtls_Lkup_Items
1930 -------------------------------------------------------------
1931 PROCEDURE Check_Approval_Dtls_Lkup_Items (
1932 p_approval_details_rec IN Approval_Details_Rec_Type,
1933 x_return_status OUT NOCOPY VARCHAR2
1934 )IS
1935 BEGIN
1936 x_return_status := FND_API.G_RET_STS_SUCCESS;
1937 END Check_Approval_Dtls_Lkup_Items;
1938
1939 -------------------------------------------------------------
1940 -- Check_Approval_Dtls_Flag_Items
1941 -------------------------------------------------------------
1942 PROCEDURE Check_Approval_Dtls_Flag_Items (
1943 p_approval_details_rec IN Approval_Details_Rec_Type,
1944 x_return_status OUT NOCOPY VARCHAR2
1945 ) IS
1946 BEGIN
1947 x_return_status := FND_API.G_RET_STS_SUCCESS;
1948 END Check_Approval_Dtls_Flag_Items;
1949 --------------------------------------------------------------------
1950
1951 -------------------------------------------------------------
1952 -- Check_Approval_Amounts_Overlap
1953 -------------------------------------------------------------
1954 -- Bug 2195020 This was changed from a function to a procedure to return
1955 -- the name of the overlapping rule
1956 PROCEDURE Check_Approval_Amounts_Overlap (
1957 p_approval_details_rec IN Approval_Details_Rec_Type,
1958 p_appoval_ids IN t_approval_id_table,
1959 x_exist_rule_name OUT NOCOPY VARCHAR2,
1960 x_return_status OUT NOCOPY VARCHAR2)
1961 IS
1962
1963 l_Approval_Details_rec Approval_Details_Rec_Type;
1964 l_max_amount NUMBER;
1965 l_min_amount NUMBER;
1966 l_approval_id NUMBER;
1967 p_max_amount NUMBER := p_approval_details_rec.approval_limit_to;
1968 p_min_amount NUMBER := p_approval_details_rec.approval_limit_from;
1969 l_id NUMBER := 1;
1970 amount_overlap BOOLEAN := FND_API.to_boolean(FND_API.g_false);
1971 l_rule_name VARCHAR2(240);
1972
1973 CURSOR c_approval_rule (l_approval_id IN NUMBER)IS
1974 SELECT approval_limit_from, approval_limit_to
1975 FROM ams_approval_details
1976 WHERE approval_detail_id = l_approval_id;
1977
1978 BEGIN
1979
1980 FOR l_id IN 1..p_appoval_ids.COUNT LOOP
1981
1982 l_approval_id := p_appoval_ids(l_id);
1983
1984 OPEN c_approval_rule(l_approval_id);
1985 FETCH c_approval_rule INTO l_min_amount, l_max_amount;
1986 CLOSE c_approval_rule;
1987
1988 IF ( l_max_amount IS NULL AND l_min_amount IS NOT NULL AND p_min_amount IS NOT NULL AND p_max_amount IS NOT NULL) THEN
1989 IF ( p_max_amount > l_min_amount) THEN
1990 amount_overlap := FND_API.to_boolean(FND_API.g_true);
1991 END IF;
1992 END IF;
1993
1994 IF NOT amount_overlap AND
1995 (l_max_amount IS NOT NULL AND l_min_amount IS NOT NULL AND p_max_amount IS NULL AND p_min_amount IS NOT NULL) THEN
1996 IF ( p_min_amount < l_max_amount) THEN
1997 amount_overlap := FND_API.to_boolean(FND_API.g_true);
1998 END IF;
1999 END IF;
2000
2001 -- Condition added by VMODUR for bug 2340052
2002 -- Case when max amounts are null and a rule is setup for which min amountis greater than or equal existing amount
2003 IF NOT amount_overlap AND
2004 (l_min_amount IS NOT NULL and l_max_amount IS NULL and p_min_amount IS NOT NULL and p_max_amount IS NULL) THEN
2005
2006 IF (p_min_amount >= l_min_amount) THEN
2007 amount_overlap := FND_API.to_boolean(FND_API.g_true);
2008 END IF;
2009 END IF;
2010
2011 IF NOT amount_overlap AND
2012 ( l_max_amount IS NOT NULL AND l_min_amount IS NOT NULL AND p_max_amount IS NOT NULL AND p_min_amount IS NOT NULL) THEN
2013
2014
2015 IF ( p_min_amount >= l_min_amount AND p_min_amount < l_max_amount ) THEN
2016 IF (AMS_DEBUG_HIGH_ON) THEN
2017
2018 Ams_Utility_Pvt.debug_message('The budget range is overlapping with that of existing rule ');
2019 END IF;
2020 amount_overlap := FND_API.to_boolean(FND_API.g_true);
2021
2022 ELSIF ( p_max_amount > l_min_amount AND p_max_amount <= l_max_amount ) THEN
2023 IF (AMS_DEBUG_HIGH_ON) THEN
2024
2025 Ams_Utility_Pvt.debug_message('The budget range is overlapping with that of existing rule ');
2026 END IF;
2027 amount_overlap := FND_API.to_boolean(FND_API.g_true);
2028
2029 ELSIF ( p_min_amount < l_min_amount AND p_max_amount > l_max_amount ) THEN
2030 IF (AMS_DEBUG_HIGH_ON) THEN
2031
2032 Ams_Utility_Pvt.debug_message('The budget range is overlapping with that of existing rule ');
2033 END IF;
2034 amount_overlap := FND_API.to_boolean(FND_API.g_true);
2035
2036 END IF;
2037
2038 END IF;
2039
2040 IF amount_overlap THEN
2041 Get_Approval_Rule_Name(p_approval_detail_id => l_approval_id,
2042 x_rule_name => l_rule_name);
2043
2044 x_exist_rule_name := l_rule_name;
2045 x_return_status := FND_API.g_true;
2046 EXIT;
2047 ELSE
2048 x_return_status := FND_API.g_false;
2049 END IF;
2050
2051 END LOOP;
2052
2053 END Check_Approval_Amounts_Overlap;
2054
2055 ------------------------------------------------------------------------
2056 -- Bug# 2195020 VMODUR
2057 PROCEDURE Check_Unique_Rule (
2058 p_approval_details_rec IN Approval_Details_Rec_Type,
2059 x_exist_rule_name OUT NOCOPY VARCHAR2,
2060 x_return_status OUT NOCOPY VARCHAR2
2061 )
2062 IS
2063 l_count NUMBER := 0;
2064 l_miss_num NUMBER := FND_API.g_miss_num;
2065 l_miss_char VARCHAR2(30) := FND_API.g_miss_char;
2066 l_miss_date DATE := FND_API.g_miss_date;
2067 l_approval_detail_id NUMBER;
2068 l_approval_rule_name AMS_APPROVAL_DETAILS_V.Name%TYPE;
2069
2070 CURSOR c_approval_unique IS
2071 select approval_detail_id, name
2072 from AMS_APPROVAL_DETAILS_VL -- Perf Bug Fix. Was previously using _V
2073 where nvl(start_date_active,l_miss_date) = nvl(p_approval_details_rec.start_date_active, l_miss_date)
2074 and nvl(end_date_active,l_miss_date) = nvl(p_approval_details_rec.end_date_active, l_miss_date)
2075 and nvl(business_unit_id,l_miss_num) = nvl(p_approval_details_rec.business_unit_id, l_miss_num)
2076 and nvl(user_country_code,l_miss_char) = nvl(p_approval_details_rec.user_country_code, l_miss_char)
2077 and nvl(currency_code,l_miss_char) = nvl(p_approval_details_rec.currency_code, l_miss_char)
2078 and nvl(organization_id,l_miss_num) = nvl(p_approval_details_rec.organization_id, l_miss_num)
2079 and nvl(custom_setup_id,l_miss_num) = nvl(p_approval_details_rec.custom_setup_id, l_miss_num)
2080 and approval_object = p_approval_details_rec.approval_object
2081 and nvl(approval_object_type,l_miss_char) = nvl(p_approval_details_rec.approval_object_type, l_miss_char)
2082 and nvl(approval_type,l_miss_char) = nvl(p_approval_details_rec.approval_type, l_miss_char)
2083 and nvl(approval_priority,l_miss_char) = nvl(p_approval_details_rec.approval_priority, l_miss_char)
2084 -- Bug 3068835 both lines were using limit_to
2085 and nvl(approval_limit_from,l_miss_num) = nvl(p_approval_details_rec.approval_limit_from, l_miss_num)
2086 and nvl(approval_limit_to,l_miss_num) = nvl(p_approval_details_rec.approval_limit_to, l_miss_num);
2087
2088
2089
2090 BEGIN
2091
2092 OPEN c_approval_unique;
2093 FETCH c_approval_unique INTO l_approval_detail_id, l_approval_rule_name;
2094 CLOSE c_approval_unique ;
2095
2096 IF l_approval_detail_id IS NOT NULL THEN
2097 IF p_approval_details_rec.approval_detail_id IS NOT NULL THEN
2098 IF l_approval_detail_id <> p_approval_details_rec.approval_detail_id THEN
2099 x_return_status := Fnd_Api.g_true;
2100 ELSE
2101 x_return_status := Fnd_Api.g_false;
2102 END IF;
2103 ELSE
2104 x_return_status := Fnd_Api.g_true;
2105 END IF;
2106 ELSE
2107 x_return_status := Fnd_Api.g_false;
2108 END IF;
2109
2110 x_exist_rule_name := l_approval_rule_name;
2111 END Check_Unique_Rule;
2112
2113 ----------------------------------------------------------------------------------
2114
2115 -------------------------------------------------------------
2116 -- Check_Dates_Overlap
2117 -------------------------------------------------------------
2118 FUNCTION Check_Dates_Overlap (
2119 p_approval_details_rec IN Approval_Details_Rec_Type,
2120 p_validation_mode IN VARCHAR2
2121 ) RETURN VARCHAR2
2122 IS
2123
2124 l_Approval_Details_rec Approval_Details_Rec_Type;
2125 l_start_date DATE;
2126 l_end_date DATE;
2127 p_start_date DATE := TRUNC(p_approval_details_rec.start_date_active);
2128 p_end_date DATE := TRUNC(p_approval_details_rec.end_date_active);
2129 l_approval_detail_id NUMBER ;
2130
2131 l_temp NUMBER := 0;
2132
2133 l_miss_num NUMBER := FND_API.g_miss_num;
2134 l_miss_char VARCHAR2(30) := FND_API.g_miss_char;
2135 l_miss_date DATE := FND_API.g_miss_date;
2136 l_rule_name VARCHAR2(240);
2137 l_return_status VARCHAR2(1);
2138
2139
2140 v_approvalIds t_approval_id_table;
2141
2142 CURSOR c_approval_dates IS
2143 SELECT TRUNC(start_date_active) , TRUNC(end_date_active) FROM ams_approval_details
2144 WHERE approval_detail_id = p_approval_details_rec.approval_detail_id;
2145
2146 CURSOR c_approval_rule IS
2147 SELECT approval_detail_id, TRUNC(start_date_active), TRUNC(end_date_active) FROM ams_approval_details
2148 where nvl(business_unit_id,l_miss_num) = nvl(p_approval_details_rec.business_unit_id, l_miss_num)
2149 and nvl(user_country_code,l_miss_char) = nvl(p_approval_details_rec.user_country_code, l_miss_char)
2150 and nvl(currency_code,l_miss_char) = nvl(p_approval_details_rec.currency_code, l_miss_char)
2151 and nvl(organization_id,l_miss_num) = nvl(p_approval_details_rec.organization_id, l_miss_num)
2152 and nvl(custom_setup_id,l_miss_num) = nvl(p_approval_details_rec.custom_setup_id, l_miss_num)
2153 and approval_object = p_approval_details_rec.approval_object
2154 and nvl(approval_object_type,l_miss_char) = nvl(p_approval_details_rec.approval_object_type, l_miss_char)
2155 and approval_type = p_approval_details_rec.approval_type
2156 and nvl(approval_priority,l_miss_char) = nvl(p_approval_details_rec.approval_priority, l_miss_char)
2157 AND approval_detail_id NOT IN(nvl(p_approval_details_rec.approval_detail_id,0));
2158
2159
2160 BEGIN
2161
2162 /* If the Rule is already active i.e. start_date is < SYSDATE, you cannot update the start_date */
2163 IF p_validation_mode = JTF_PLSQL_API.g_update THEN
2164
2165 OPEN c_approval_dates;
2166 FETCH c_approval_dates INTO l_start_date, l_end_date;
2167 CLOSE c_approval_dates;
2168
2169 IF (AMS_DEBUG_HIGH_ON) THEN
2170 Ams_Utility_Pvt.debug_message('l_start_date '||l_start_date);
2171 Ams_Utility_Pvt.debug_message('l_end_date '||l_end_date);
2172 Ams_Utility_Pvt.debug_message('p_rec_start_date '||p_start_date);
2173 Ams_Utility_Pvt.debug_message('p_rec_end_date '||p_end_date);
2174 END IF;
2175
2176 IF (p_start_date IS NOT NULL ) THEN
2177
2178 IF (l_start_date <= trunc(SYSDATE) AND p_start_date <> l_start_date ) THEN
2179 IF (AMS_DEBUG_HIGH_ON) THEN
2180 Ams_Utility_Pvt.debug_message('You cannot update the Approval Rule start date as it is already active');
2181 END IF;
2182
2183 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2184 Fnd_Message.set_name('AMS', 'AMS_APRD_START_DATE_NO_UPDATE');
2185 Fnd_Msg_Pub.ADD;
2186 RETURN FND_API.g_true;
2187 END IF;
2188
2189 END IF;
2190
2191 IF ( l_start_date IS NOT NULL AND l_start_date > trunc(SYSDATE)
2192 AND p_start_date < trunc(SYSDATE)
2193 ) THEN
2194 IF (AMS_DEBUG_HIGH_ON) THEN
2195
2196 Ams_Utility_Pvt.debug_message('Approval Rule Start Date cannot be less than the system date');
2197 END IF;
2198
2199 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2200 Fnd_Message.set_name('AMS', 'AMS_APRD_START_DATE_LT_SYSDATE');
2201 Fnd_Msg_Pub.ADD;
2202 RETURN FND_API.g_true;
2203 END IF;
2204 END IF;
2205
2206
2207 END IF;
2208
2209 END IF;
2210
2211
2212 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2213
2214 /* Checking the Dates are Valid Or Not */
2215 IF (p_start_date IS NOT NULL AND p_start_date < trunc(SYSDATE)) THEN
2216 IF (AMS_DEBUG_HIGH_ON) THEN
2217
2218 Ams_Utility_Pvt.debug_message('Approval Rule Start Date cannot be less than the system date');
2219 END IF;
2220
2221 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2222 Fnd_Message.set_name('AMS', 'AMS_APRD_START_DATE_LT_SYSDATE');
2223 Fnd_Msg_Pub.ADD;
2224 RETURN FND_API.g_true;
2225 END IF;
2226 END IF;
2227
2228 END IF;
2229
2230 IF (p_start_date IS NOT NULL AND p_start_date < trunc(SYSDATE)
2231 AND l_start_date IS NOT NULL AND l_start_date > trunc(SYSDATE)) THEN
2232 IF (AMS_DEBUG_HIGH_ON) THEN
2233
2234 Ams_Utility_Pvt.debug_message('Approval Rule Start Date cannot be less than the system date');
2235 END IF;
2236
2237 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2238 Fnd_Message.set_name('AMS', 'AMS_APRD_START_DATE_LT_SYSDATE');
2239 Fnd_Msg_Pub.ADD;
2240 RETURN FND_API.g_true;
2241 END IF;
2242 END IF;
2243
2244 IF (p_end_date IS NOT NULL AND p_end_date < trunc(SYSDATE)) THEN
2245 IF (AMS_DEBUG_HIGH_ON) THEN
2246
2247 Ams_Utility_Pvt.debug_message('Approval Rule End Date cannot be less than the system date');
2248 END IF;
2249
2250 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2251 Fnd_Message.set_name('AMS', 'AMS_APRD_END_DATE_LT_SYSDATE');
2252 Fnd_Msg_Pub.ADD;
2253 RETURN FND_API.g_true;
2254 END IF;
2255 END IF;
2256
2257 IF (p_start_date IS NOT NULL AND p_end_date IS NOT NULL) THEN
2258 IF ( p_start_date > p_end_date ) THEN
2259 IF (AMS_DEBUG_HIGH_ON) THEN
2260
2261 Ams_Utility_Pvt.debug_message('Approval Rule END Date cannot be less than Start Date');
2262 END IF;
2263
2264 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2265 Fnd_Message.set_name('AMS', 'AMS_APRD_ED_DATE_LT_ST_DATE');
2266 Fnd_Msg_Pub.ADD;
2267 RETURN FND_API.g_true;
2268 END IF;
2269 END IF;
2270 END IF;
2271
2272
2273
2274
2275 /* Checking for Date Overlap with any other record if exists with the same rule parameters */
2276
2277 OPEN c_approval_rule;
2278 FETCH c_approval_rule INTO l_approval_detail_id, l_start_date, l_end_date;
2279
2280 WHILE c_approval_rule%FOUND LOOP
2281
2282 IF ( l_end_date IS NULL AND l_start_date IS NOT NULL AND p_start_date IS NOT NULL AND p_end_date IS NOT NULL) THEN
2283 IF( p_end_date >= l_start_date) THEN
2284
2285 IF (AMS_DEBUG_HIGH_ON) THEN
2286
2287
2288
2289 Ams_Utility_Pvt.debug_message('Entered Condition 1 ');
2290
2291 END IF;
2292 IF (AMS_DEBUG_HIGH_ON) THEN
2293
2294 Ams_Utility_Pvt.debug_message('The date range is overlapping with that of existing rule ');
2295 END IF;
2296
2297 l_temp := l_temp + 1;
2298 v_approvalIds(l_temp) := l_approval_detail_id;
2299
2300 END IF;
2301 END IF;
2302
2303 IF ( l_end_date IS NULL AND l_start_date IS NOT NULL AND p_start_date IS NOT NULL AND p_end_date IS NULL) THEN
2304 IF( p_start_date >= l_start_date) THEN
2305
2306 IF (AMS_DEBUG_HIGH_ON) THEN
2307
2308
2309
2310 Ams_Utility_Pvt.debug_message('Entered Condition 1 ');
2311
2312 END IF;
2313 IF (AMS_DEBUG_HIGH_ON) THEN
2314
2315 Ams_Utility_Pvt.debug_message('The date range is overlapping with that of existing rule ');
2316 END IF;
2317
2318 l_temp := l_temp + 1;
2319 v_approvalIds(l_temp) := l_approval_detail_id;
2320
2321 END IF;
2322 END IF;
2323
2324
2325
2326 IF (l_end_date IS NOT NULL AND l_start_date IS NOT NULL AND p_end_date IS NULL AND p_start_date IS NOT NULL) THEN
2327 IF( p_start_date <= l_end_date) THEN
2328
2329 IF (AMS_DEBUG_HIGH_ON) THEN
2330
2331
2332
2333 Ams_Utility_Pvt.debug_message('Entered Condition 2 ');
2334
2335 END IF;
2336 IF (AMS_DEBUG_HIGH_ON) THEN
2337
2338 Ams_Utility_Pvt.debug_message('The date range is overlapping with that of existing rule ');
2339 END IF;
2340
2341 l_temp := l_temp + 1;
2342 v_approvalIds(l_temp) := l_approval_detail_id;
2343
2344
2345 END IF;
2346 END IF;
2347
2348
2349 IF( l_end_date IS NOT NULL AND l_start_date IS NOT NULL AND p_end_date IS NOT NULL AND p_start_date IS NOT NULL) THEN
2350
2351 IF (AMS_DEBUG_HIGH_ON) THEN
2352 Ams_Utility_Pvt.debug_message('Entered Condition 3 ');
2353 Ams_Utility_Pvt.debug_message('p_start_date is ' || to_char(p_start_date));
2354 Ams_Utility_Pvt.debug_message('p_end_date is ' || to_char(p_end_date));
2355 Ams_Utility_Pvt.debug_message('l_start_date is ' || to_char(l_start_date));
2356 Ams_Utility_Pvt.debug_message('l_end_date is ' || to_char(l_end_date));
2357 END IF;
2358
2359 IF ( p_start_date >= l_start_date AND p_start_date <= l_end_date
2360 -- AND Condition added due to Bug 3275739
2361 -- We don't need to validate overlap with expired approval rules
2362 AND l_end_date >= trunc(SYSDATE)) THEN
2363 IF (AMS_DEBUG_HIGH_ON) THEN
2364
2365 Ams_Utility_Pvt.debug_message('The date range is overlapping with that of existing rule ');
2366 END IF;
2367
2368 l_temp := l_temp + 1;
2369 v_approvalIds(l_temp) := l_approval_detail_id;
2370
2371 ELSIF ( p_end_date >= l_start_date AND p_end_date <= l_end_date
2372 -- AND Condition added due to Bug 3275739
2373 -- We don't need to validate overlap with expired approval rules
2374 AND l_end_date >= trunc(SYSDATE)) THEN
2375 IF (AMS_DEBUG_HIGH_ON) THEN
2376
2377 Ams_Utility_Pvt.debug_message('The date range is overlapping with that of existing rule ');
2378 END IF;
2379
2380 l_temp := l_temp + 1;
2381 v_approvalIds(l_temp) := l_approval_detail_id;
2382
2383 ELSIF ( p_start_date <= l_start_date AND p_end_date >= l_end_date
2384 -- AND Condition added due to Bug 3275739
2385 -- We don't need to validate overlap with expired approval rules
2386 AND l_end_date >= trunc(SYSDATE)) THEN
2387 IF (AMS_DEBUG_HIGH_ON) THEN
2388
2389 Ams_Utility_Pvt.debug_message('The date range is overlapping with that of existing rule ');
2390 END IF;
2391
2392 l_temp := l_temp + 1;
2393 v_approvalIds(l_temp) := l_approval_detail_id;
2394
2395 END IF;
2396
2397 END IF;
2398
2399 FETCH c_approval_rule INTO l_approval_detail_id, l_start_date, l_end_date;
2400
2401 END LOOP;
2402
2403 CLOSE c_approval_rule;
2404
2405 IF(p_approval_details_rec.approval_type = 'CONCEPT') THEN
2406 IF(l_temp <> 0) THEN -- date overlap is there
2407
2408 Get_Approval_Rule_Name(p_approval_detail_id => v_approvalIds(1),
2409 x_rule_name => l_rule_name);
2410
2411 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2412 Fnd_Message.set_name('AMS', 'AMS_APRD_DATE_OVERLAP');
2413 Fnd_Message.set_token('EXIST_RULE_NAME', l_rule_name);
2414 Fnd_Msg_Pub.ADD;
2415 RETURN FND_API.g_true;
2416 END IF;
2417 END IF;
2418 --ELSIF (p_approval_details_rec.approval_type = 'BUDGET') THEN --- VMODUR 30-Oct-2003
2419 -- Change because claims etc. are not being checked for Amt Overlap
2420 ELSE
2421 IF(l_temp <> 0) THEN -- date overlap is there
2422 Check_Approval_Amounts_Overlap (p_approval_details_rec => p_approval_details_rec,
2423 p_appoval_ids => v_approvalIds,
2424 x_exist_rule_name => l_rule_name,
2425 x_return_status => l_return_status);
2426 IF l_return_status = FND_API.g_true THEN
2427 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_ERROR) THEN
2428 Fnd_Message.set_name('AMS', 'AMS_APRD_MIN_MAX_AMT_OVERLAP');
2429 Fnd_Message.set_token('EXIST_RULE_NAME', l_rule_name);
2430 Fnd_Msg_Pub.ADD;
2431 RETURN FND_API.g_true;
2432 END IF;
2433 END IF;
2434 END IF;
2435 END IF;
2436
2437 RETURN FND_API.g_false;
2438
2439 END Check_Dates_Overlap;
2440 --------------------------------------------------------------------
2441
2442 END AMS_approval_details_PVT;
[Home] [Help]