[Home] [Help]
PACKAGE BODY: APPS.AHL_APPROVALS_PVT
Source
1 Package BODY Ahl_Approvals_Pvt AS
2 /* $Header: AHLVAPRB.pls 120.2 2006/03/28 01:28:14 sagarwal noship $ */
3 -----------------------------------------------------------
4 -- PACKAGE
5 -- AHL_Approval_Rules_PVT
6 --
7 -- PROCEDURES
8 -- AHL_Approval_Rules_B AND AHL_Approval_Rules_TL:
9 -- Check_Approval_Rules_Req_Items
10 -- Check_Approval_Rules_UK_Items
11 -- AHL_Approvers:
12 -- Check_Approvers_Req_Items
13 -- Check_Approvers_UK_Items
14 --
15 -- NOTES
16 --
17 --
18 -- HISTORY
19 -- 20-Jan-2002 shbhanda Created.
20 -----------------------------------------------------------------
21 -- Global CONSTANTS
22 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_Approvals_PVT';
23 -- Reema : FND Logging
24 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
25
26 -- Perform record level(inter-field) validation only
27 G_VALID_APPROVER CONSTANT NUMBER:= 50;
28 G_VALID_QUALIFIER CONSTANT NUMBER:= 40;
29 G_MATCH_STATUS CONSTANT NUMBER:= 30;
30 G_VALID_NAME CONSTANT NUMBER:= 20;
31
32 /* Start code by shbhanda on 10-MAR-02 */
33 -- Added for use by bind_parse.
34 TYPE col_val_rec IS RECORD (
35 col_name VARCHAR2(2000),
36 col_op VARCHAR2(10),
37 col_value VARCHAR2(2000) );
38
39 TYPE col_val_tbl IS TABLE OF col_val_rec INDEX BY BINARY_INTEGER;
40 /* End code by shbhanda on 10-MAR-02 */
41
42 -- Check_Approval_Rules_Req_Items
43 PROCEDURE Check_Approval_Rules_Req_Items (
44 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
45 x_return_status OUT NOCOPY VARCHAR2
46 );
47
48 -- Check_Approval_Rules_UK_Items
49 PROCEDURE Check_Approval_Rules_UK_Items (
50 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
51 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
52 x_return_status OUT NOCOPY VARCHAR2
53 );
54
55 -- Compare Columns
56 FUNCTION compare_columns(
57 l_Approval_Rules_rec IN Approval_Rules_Rec_Type
58 ) RETURN VARCHAR2;
59 -- FND_API.g_true/g_false
60
61 -- Check_Approver_for_Rule : to check at least one approver present when status updating to active for approval
62 PROCEDURE Check_Approver_for_Rule (
63 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
64 p_complete_rec IN Approval_Rules_Rec_Type := NULL,
65 x_return_status OUT NOCOPY VARCHAR2
66 );
67
68 -- Check_Default_Approver : whether ApproverType is Role and Name as Empty
69 PROCEDURE Check_Approver_Role (
70 p_Approvers_rec IN Approvers_Rec_Type,
71 p_complete_rec IN Approvers_Rec_Type := NULL,
72 x_return_status OUT NOCOPY VARCHAR2
73 );
74
75 -- Check_Active_for_Qualifier : to check whether for a particular approval qualifier
76 -- among all approvals only one of them is to be active
77 -- Qualifier comprises of 'Approval Object Code', 'Approval Priority Code', 'Approval Type Code' and 'Operating Unit Id'
78 PROCEDURE Check_Active_for_Qualifier (
79 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
80 p_complete_rec IN Approval_Rules_Rec_Type := NULL,
81 x_return_status OUT NOCOPY VARCHAR2
82 );
83
84 -- Check_Approver_Sequence : it should not be Zero OR Negative sequence
85 PROCEDURE Check_Approver_Sequence (
86 p_Approvers_rec IN Approvers_Rec_Type,
87 p_complete_rec IN Approvers_Rec_Type := NULL,
88 x_return_status OUT NOCOPY VARCHAR2
89 );
90
91 -- Check_Approver_User/Role Name : if it is entered by user .[A selected from LOV then
92 -- retrieve the id value check whether the entered name is valid
93 /*PROCEDURE Check_Approver_Name (
94 p_Approvers_rec IN Approvers_Rec_Type,
95 p_complete_rec IN Approvers_Rec_Type := NULL,
96 x_return_status OUT NOCOPY VARCHAR2
97 );*/
98
99
100 -- Check_Operating_Name : if it is entered by user selected from LOV then
101 -- retrieve the operating unit id value check whether the entered name is valid
102 PROCEDURE Check_Operating_Name (
103 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
104 p_complete_rec IN Approval_Rules_Rec_Type := NULL,
105 x_return_status OUT NOCOPY VARCHAR2
106 );
107
108 PROCEDURE Check_Appl_Usg_Code(
109 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
110 x_return_status OUT NOCOPY VARCHAR2
111 );
112
113
114 -- Check_Match_for_Status : to validate the various combination of current status code
115 PROCEDURE Check_Match_for_Status(
116 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
117 p_complete_rec IN Approval_Rules_Rec_Type := NULL,
118 x_return_status OUT NOCOPY VARCHAR2
119 );
120
121 -- Check_Approvers_Req_Items
122 PROCEDURE Check_Approvers_Req_Items (
123 p_Approvers_rec IN Approvers_Rec_Type,
124 x_return_status OUT NOCOPY VARCHAR2
125 );
126
127 -- Check_Approvers_UK_Items
128 PROCEDURE Check_Approvers_UK_Items (
129 p_Approvers_rec IN Approvers_Rec_Type,
130 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
131 x_return_status OUT NOCOPY VARCHAR2
132 );
133
134 PROCEDURE Check_Approver_Name_Or_Id(
135 p_approvers_rec IN Approvers_rec_type,
136 x_approver_id OUT NOCOPY NUMBER,
137 x_return_status OUT NOCOPY VARCHAR2
138 );
139
140 --------------------------------------------------------------------
141 -- PROCEDURE
142 -- Process_Wf_Mapping for both Approval Rules
143 --
144 --------------------------------------------------------------------
145 PROCEDURE Process_Approvals (
146 p_api_version IN NUMBER,
147 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
148 p_commit IN VARCHAR2,
149 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
150
151 p_x_Approval_Rules_Rec IN OUT NOCOPY Approval_Rules_Rec_Type,
152 p_x_Approvers_Tbl IN OUT NOCOPY Approvers_Tbl,
153
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2
157 )
158 IS
159 L_API_VERSION CONSTANT NUMBER := 1.0;
160 L_API_NAME CONSTANT VARCHAR2(30) := 'Creation';
161 L_FULL_NAME CONSTANT VARCHAR2(60) := 'Approvals' || '.' || L_API_NAME;
162
163 l_x_Approval_Rules_Rec Approval_Rules_Rec_Type := p_x_Approval_Rules_Rec;
164 l_x_Approvers_Tbl Approvers_Tbl := p_x_Approvers_Tbl;
165
166 l_dummy NUMBER;
167 l_return_status VARCHAR2(1);
168 p_object_version VARCHAR2(1) := 1;
169 x_Approval_Rules_Id NUMBER;
170 x_Approval_Approver_Id NUMBER;
171
172 BEGIN
173 --------------------- initialize -----------------------
174 SAVEPOINT Process_Approvals;
175 -- Check if API is called in debug mode. If yes, enable debug.
176 IF G_DEBUG='Y' THEN
177 Ahl_Debug_Pub.enable_debug;
178 END IF;
179 -- Debug info.
180 IF G_DEBUG='Y' THEN
181 Ahl_Debug_Pub.debug( l_full_name ||':Start');
182 END IF;
183
184 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
185 Fnd_Msg_Pub.initialize;
186 END IF;
187
188 IF NOT Fnd_Api.compatible_api_call (
189 L_API_VERSION,
190 p_api_version,
191 L_API_NAME,
192 G_PKG_NAME
193 ) THEN
194 RAISE Fnd_Api.g_exc_unexpected_error;
195 END IF;
196
197 x_return_status := Fnd_Api.g_ret_sts_success;
198
199 ----------------------- validate -----------------------
200 IF G_DEBUG='Y' THEN
201 Ahl_Debug_Pub.debug( l_full_name ||':Validate');
202 END IF;
203
204 ---------------Manipulations in Approval Rules---------------
205 IF (l_x_Approval_Rules_Rec.operation_flag = 'C' OR l_x_Approval_Rules_Rec.operation_flag = 'c') THEN
206 -- For creation of Approvers
207 Create_Approval_Rules (
208 p_api_version,
209 p_init_msg_list,
210 p_commit,
211 p_validation_level,
212 x_return_status ,
213 x_msg_count,
214 x_msg_data,
215 l_x_Approval_Rules_Rec,
216 X_APPROVAL_RULES_ID);
217
218 p_x_Approval_Rules_Rec.APPROVAL_RULE_ID:=X_APPROVAL_RULES_ID;
219
220 END IF;
221 IF (l_x_Approval_Rules_Rec.operation_flag = 'U' OR l_x_Approval_Rules_Rec.operation_flag = 'u') THEN
222 -- For updation of Approvers
223 Update_Approval_Rules (
224 p_api_version ,
225 p_init_msg_list,
226 p_commit,
227 p_validation_level,
228 x_return_status,
229 x_msg_count,
230 x_msg_data,
231 l_x_Approval_Rules_Rec);
232 END IF;
233 ---------------Manipulations in Approvers---------------
234 IF (l_x_Approvers_Tbl.COUNT > 0) THEN
235 FOR i IN l_x_Approvers_Tbl.FIRST..l_x_Approvers_Tbl.LAST LOOP
236 IF (l_x_Approvers_Tbl(i).operation_flag = 'C' OR l_x_Approvers_Tbl(i).operation_flag = 'c') THEN
237 -- For creation of Approvers
238 Create_Approvers (
239 p_api_version,
240 p_init_msg_list,
241 p_commit,
242 p_validation_level,
243 x_return_status,
244 x_msg_count,
245 x_msg_data,
246 l_x_Approvers_Tbl(i),
247 x_Approval_Approver_Id);
248
249 p_x_Approvers_Tbl(i).APPROVAL_APPROVER_ID := x_Approval_Approver_Id;
250
251 END IF;
252 IF (l_x_Approvers_Tbl(i).operation_flag = 'U' OR l_x_Approvers_Tbl(i).operation_flag = 'u') THEN
253 -- For updation of Approvers
254 Update_Approvers (
255 p_api_version ,
256 p_init_msg_list,
257 p_commit,
258 p_validation_level,
259 l_x_Approvers_Tbl(i),
260 x_return_status,
261 x_msg_count,
262 x_msg_data);
263 END IF;
264 IF (l_x_Approvers_Tbl(i).operation_flag = 'D' OR l_x_Approvers_Tbl(i).operation_flag = 'd') THEN
265 -- For deletion of Approvers
266 Delete_Approvers (
267 p_api_version,
268 p_init_msg_list,
269 p_commit,
270 p_validation_level,
271 l_x_Approvers_Tbl(i).Approval_Approver_Id,
272 p_object_version,
273 x_return_status,
274 x_msg_count,
275 x_msg_data
276 );
277 END IF;
278 END LOOP;
279 END IF;
280
281 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
282 RAISE Fnd_Api.g_exc_error;
283 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
284 RAISE Fnd_Api.g_exc_unexpected_error;
285 END IF;
286
287 --
288 -- END of API body.
289 --
290 -- Standard check of p_commit.
291 IF Fnd_Api.To_Boolean (p_commit) THEN
292 COMMIT WORK;
293 END IF;
294 Fnd_Msg_Pub.count_and_get(
295 p_encoded => Fnd_Api.g_false,
296 p_count => x_msg_count,
297 p_data => x_msg_data
298 );
299 IF G_DEBUG='Y' THEN
300 Ahl_Debug_Pub.debug( l_full_name ||':End');
301 END IF;
302 -- Check if API is called in debug mode. If yes, disable debug.
303 IF G_DEBUG='Y' THEN
304 Ahl_Debug_Pub.disable_debug;
305 END IF;
306
307 EXCEPTION
308 WHEN Fnd_Api.g_exc_error THEN
309 ROLLBACK TO Process_Approvals;
310 x_return_status := Fnd_Api.g_ret_sts_error;
311 Fnd_Msg_Pub.count_and_get(
312 p_encoded => Fnd_Api.g_false,
313 p_count => x_msg_count,
314 p_data => x_msg_data
315 );
316 WHEN Fnd_Api.g_exc_unexpected_error THEN
317 ROLLBACK TO Process_Approvals;
318 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
319 Fnd_Msg_Pub.count_and_get (
320 p_encoded => Fnd_Api.g_false,
321 p_count => x_msg_count,
322 p_data => x_msg_data
323 );
324 WHEN OTHERS THEN
325 ROLLBACK TO Process_Approvals;
326 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
327 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error) THEN
328 Fnd_Msg_Pub.add_exc_msg (g_pkg_name, l_api_name);
329 END IF;
330 Fnd_Msg_Pub.count_and_get (
331 p_encoded => Fnd_Api.g_false,
332 p_count => x_msg_count,
333 p_data => x_msg_data
334 );
335
336 END Process_Approvals;
337
338
339 --------------------------------------------------------------------
340 ----- Approval_Rules -----
341 --------------------------------------------------------------------
342 /* Start code on 11-MAR-02 by shbhanda */
343
344 ---------------------------------------------------------------------
345 -- PROCEDURE
346 -- bind_parse
347 -- USAGE
348 -- bind_parse (varchar2, col_val_tbl);
349 -- The input string must have a space between the AND and operator clause
350 -- and it must exclude the initial WHERE/AND statement.
351 -- Example: source_code = 'xyz' and campaign_id <> 1
352 ---------------------------------------------------------------------
353 PROCEDURE bind_parse (
354 p_string IN VARCHAR2,
355 x_col_val_tbl OUT NOCOPY col_val_tbl)
356 IS
357 l_new_str VARCHAR2(4000);
358 l_str VARCHAR2(4000) := p_string;
359 l_curr_pos NUMBER; -- the position index of the operator string
360 l_eq_pos NUMBER;
361 l_not_pos NUMBER;
362 l_and_pos NUMBER;
363 l_like_pos NUMBER;
364 i NUMBER := 1;
365 BEGIN
366 LOOP
367 l_and_pos := INSTR (UPPER (l_str), ' AND ');
368 -- handle condition where no more AND's are
369 -- left -- usually if only one condition or
370 -- the last condition in the WHERE clause.
371 IF l_and_pos = 0 THEN
372 l_new_str := l_str;
373 ELSE
374 l_new_str := SUBSTR (l_str, 1, l_and_pos - 1);
375 END IF;
376
377 --
378 -- The operator should also be passed
379 -- back to the calling program.
380 l_eq_pos := INSTR (l_new_str, '=');
381 l_not_pos := INSTR (l_new_str, '<>');
382 l_like_pos := INSTR (l_new_str, 'LIKE');
383
384 --
385 -----------------------------------
386 -- operator equal not equal
387 -- error 0 0
388 -- = 1 0
389 -- <> 0 1
390 -- = 1 2
391 -- <> 2 1
392 -----------------------------------
393
394 IF l_eq_pos = 0 AND l_not_pos = 0 AND l_like_pos = 0 THEN
395 -- Could not find either an = or an <>
396 -- operator.
397 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_unexp_error) THEN
398 Fnd_Message.set_name('AHL', 'AHL_UTIL_NO_WHERE_OPERATOR');
399 Fnd_Msg_Pub.ADD;
400 RAISE Fnd_Api.g_exc_unexpected_error;
401 END IF;
402 ELSIF l_eq_pos > 0 AND l_not_pos = 0 THEN
403 l_curr_pos := l_eq_pos;
404 x_col_val_tbl(i).col_op := '=';
405 ELSIF l_not_pos > 0 AND l_eq_pos = 0 THEN
406 l_curr_pos := l_not_pos;
407 x_col_val_tbl(i).col_op := '<>';
408 ELSIF l_eq_pos < l_not_pos THEN
409 l_curr_pos := l_eq_pos;
410 x_col_val_tbl(i).col_op := '=';
411 ELSIF l_eq_pos = 0 AND l_not_pos = 0 AND l_like_pos <> 0 THEN
412 l_curr_pos := l_like_pos;
413 x_col_val_tbl(i).col_op := 'LIKE';
414 ELSE
415 l_curr_pos := l_not_pos;
416 x_col_val_tbl(i).col_op := '<>';
417 END IF;
418
419 x_col_val_tbl(i).col_name := UPPER (LTRIM (RTRIM (SUBSTR (l_new_str, 1, l_curr_pos - 1))));
420
421 IF x_col_val_tbl(i).col_op = 'LIKE' THEN
422 -- Add 4 to the current position for 'LIKE'
423 x_col_val_tbl(i).col_value := LTRIM (RTRIM (SUBSTR (l_new_str, l_curr_pos + 4)));
424 ELSE
425 -- Add 2 to the current position for '<>'.
426 x_col_val_tbl(i).col_value := LTRIM (RTRIM (SUBSTR (l_new_str, l_curr_pos + 2)));
427 --
428 END IF;
429
430 -- Remove the single quotes from the begin and end of the string value;
431 -- no action if a numeric value.
432 IF INSTR (x_col_val_tbl(i).col_value, '''', 1) = 1 THEN
433 x_col_val_tbl(i).col_value := SUBSTR (x_col_val_tbl(i).col_value,2);
434 x_col_val_tbl(i).col_value := SUBSTR (x_col_val_tbl(i).col_value, 1, LENGTH(x_col_val_tbl(i).col_value) - 1);
435 END IF;
436
437 IF l_and_pos = 0 THEN
438 EXIT; -- no more to parse
439 END IF;
440
441 l_str := SUBSTR (l_str, l_and_pos + 4);
442 i := i + 1;
443 END LOOP;
444 END bind_parse;
445
446 ---------------------------------------------------------------------
447 -- FUNCTION
448 -- Check_Rules_Uniqueness
449 --
450 -- HISTORY
451 -- Use bind_parse to enable use of bind variables.
452 -- p_null_value_flag (values : 'C' = approval_prority_code; 'I' = operating_unit_id;
453 -- 'B' Both 'C' and 'I'; 'N' = None of them)
454 ---------------------------------------------------------------------
455 FUNCTION Check_Rules_Uniqueness(
456 p_table_name IN VARCHAR2,
457 p_where_clause IN VARCHAR2,
458 p_null_value_flag IN VARCHAR2
459 )
460 RETURN VARCHAR2
461 IS
462
463 l_sql VARCHAR2(4000);
464 l_count NUMBER;
465 l_bind_tbl col_val_tbl;
466 l_and_proirity_clause VARCHAR2(100) := ' AND approval_priority_code IS NULL';
467 l_and_operating_clause VARCHAR2(100) := ' AND operating_unit_id IS NULL';
468
469 BEGIN
470
471 l_sql := 'SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM ' || UPPER(p_table_name);
472 bind_parse (p_where_clause, l_bind_tbl);
473 --
474 -- Support up to 6 WHERE conditions for uniqueness. If
475 -- the number of conditions changes, then must also revise
476 -- the execute portion of the code.
477 IF l_bind_tbl.COUNT <= 6 THEN
478 l_sql := l_sql || ' WHERE ' || l_bind_tbl(1).col_name || ' ' || l_bind_tbl(1).col_op || ' :b1';
479 FOR i IN 2..l_bind_tbl.COUNT LOOP
480 l_sql := l_sql || ' AND ' || l_bind_tbl(i).col_name || ' ' || l_bind_tbl(i).col_op || ' :b' || i;
481 END LOOP;
482 ELSE
483 -- Exceeded the number of conditions supported
484 -- for bind variables.
485 l_sql := l_sql || ' WHERE ' || p_where_clause;
486 END IF;
487
488 -- IF l_bind_tbl.COUNT < 2 THEN
489 IF p_null_value_flag = 'N' THEN
490 l_sql := l_sql || ')';
491 ELSE
492 l_sql := l_sql ;
493 END IF;
494
495 Ahl_Utility_Pvt.debug_message('SQL statement: '||l_sql);
496 --
497 -- Modify here if number of WHERE conditions
498 -- supported changes.
499 BEGIN
500 IF l_bind_tbl.COUNT = 1 OR l_bind_tbl.COUNT = 2 THEN
501 EXECUTE IMMEDIATE l_sql INTO l_count
502 USING l_bind_tbl(1).col_value;
503 ELSIF l_bind_tbl.COUNT = 3 THEN
504 EXECUTE IMMEDIATE l_sql || l_and_operating_clause || l_and_proirity_clause || ')' INTO l_count
505 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value;
506 ELSIF l_bind_tbl.COUNT = 4 THEN
507 IF p_null_value_flag = 'C' THEN
508 EXECUTE IMMEDIATE l_sql || l_and_proirity_clause || ')' INTO l_count
509 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value, l_bind_tbl(4).col_value;
510 ELSIF p_null_value_flag = 'I' THEN
511 EXECUTE IMMEDIATE l_sql || l_and_operating_clause || ')' INTO l_count
512 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value, l_bind_tbl(4).col_value;
513 ELSIF p_null_value_flag = 'B' THEN
514 EXECUTE IMMEDIATE l_sql || l_and_operating_clause || l_and_proirity_clause || ')' INTO l_count
515 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value, l_bind_tbl(4).col_value;
516 ELSE
517 EXECUTE IMMEDIATE l_sql INTO l_count
518 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value, l_bind_tbl(4).col_value;
519 END IF;
520 ELSIF l_bind_tbl.COUNT = 5 THEN
521 IF p_null_value_flag = 'C' THEN
522 EXECUTE IMMEDIATE l_sql || l_and_proirity_clause || ')' INTO l_count
523 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value , l_bind_tbl(3).col_value, l_bind_tbl(4).col_value, l_bind_tbl(5).col_value;
524 ELSIF p_null_value_flag = 'I' THEN
525 EXECUTE IMMEDIATE l_sql || l_and_operating_clause || ')' INTO l_count
526 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value , l_bind_tbl(3).col_value, l_bind_tbl(4).col_value, l_bind_tbl(5).col_value;
527 ELSIF p_null_value_flag = 'B' THEN
528 EXECUTE IMMEDIATE l_sql || l_and_operating_clause || l_and_proirity_clause || ')' INTO l_count
529 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value , l_bind_tbl(3).col_value, l_bind_tbl(4).col_value, l_bind_tbl(5).col_value;
530 ELSE
531 EXECUTE IMMEDIATE l_sql INTO l_count
532 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value , l_bind_tbl(3).col_value, l_bind_tbl(4).col_value, l_bind_tbl(5).col_value;
533 END IF;
534 ELSIF l_bind_tbl.COUNT = 6 THEN
535 EXECUTE IMMEDIATE l_sql INTO l_count
536 USING l_bind_tbl(1).col_value, l_bind_tbl(2).col_value, l_bind_tbl(3).col_value, l_bind_tbl(4).col_value, l_bind_tbl(5).col_value, l_bind_tbl(6).col_value;
537 ELSE
538 EXECUTE IMMEDIATE l_sql INTO l_count;
539 END IF;
540
541 EXCEPTION
542 WHEN NO_DATA_FOUND THEN
543 l_count := 0;
544 END;
545
546 IF l_count = 0 THEN
547 RETURN Fnd_Api.g_true;
548 ELSE
549 RETURN Fnd_Api.g_false;
550 END IF;
551
552 END Check_Rules_Uniqueness;
553
554 ---------------------------------------------------------------------
555
556 /* End code on 11-MAR-02 by shbhanda */
557 --------------------------------------------------------------------
558 -- PROCEDURE
559 -- Create_Approval_Rules
560 --
561 --------------------------------------------------------------------
562
563 PROCEDURE Create_Approval_Rules (
564 p_api_version IN NUMBER,
565 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
566 p_commit IN VARCHAR2,
567 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
568 x_return_status OUT NOCOPY VARCHAR2,
569 x_msg_count OUT NOCOPY NUMBER,
570 x_msg_data OUT NOCOPY VARCHAR2,
571 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
572 x_Approval_Rules_id OUT NOCOPY NUMBER
573 )
574 IS
575 L_API_VERSION CONSTANT NUMBER := 1.0;
576 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Approval_Rules';
577 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
578 l_Approval_Rules_rec Approval_Rules_Rec_Type := p_Approval_Rules_rec;
579 l_dummy NUMBER;
580 l_return_status VARCHAR2(1);
581 l_rowid VARCHAR2(30);
582 l_object_version_number NUMBER := 1;
583 l_status VARCHAR2(30) := 'DRAFT';
584 l_seed VARCHAR2(1) := 'N';
585
586 CURSOR c_seq IS
587 SELECT Ahl_Approval_Rules_B_S.NEXTVAL
588 FROM dual;
589
590 CURSOR c_id_exists (x_id IN NUMBER) IS
591 SELECT 1
592 FROM dual
593 WHERE EXISTS (SELECT 1
594 FROM Ahl_Approval_Rules_vl
595 WHERE Approval_Rule_id = x_id);
596 CURSOR c_operating IS
597 SELECT ORGANIZATION_ID
598 FROM HR_OPERATING_UNITS
599 WHERE NAME = l_Approval_Rules_rec.OPERATING_NAME;
600
601 -- Reema :
602 -- Added cursor for Application Usage Code
603 CURSOR c_appl_usg IS
604 SELECT LOOKUP_CODE
605 FROM FND_LOOKUPS
606 WHERE MEANING = l_Approval_Rules_rec.APPLICATION_USG
607 AND LOOKUP_TYPE = 'AHL_APPLICATION_USAGE_CODE';
608
609 BEGIN
610 --------------------- initialize -----------------------
611 SAVEPOINT Create_Approval_Rules;
612 -- Check if API is called in debug mode. If yes, enable debug.
613 IF G_DEBUG='Y' THEN
614 Ahl_Debug_Pub.enable_debug;
615 END IF;
616 -- Debug info.
617 IF G_DEBUG='Y' THEN
618 Ahl_Debug_Pub.debug( l_full_name ||':Start');
619 END IF;
620
621 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
622 Fnd_Msg_Pub.initialize;
623 END IF;
624 IF NOT Fnd_Api.compatible_api_call (
625 L_API_VERSION,
626 p_api_version,
627 L_API_NAME,
628 G_PKG_NAME
629 ) THEN
630 RAISE Fnd_Api.g_exc_unexpected_error;
631 END IF;
632 x_return_status := Fnd_Api.g_ret_sts_success;
633 ----------------------- validate -----------------------
634 IF G_DEBUG='Y' THEN
635 Ahl_Debug_Pub.debug( l_full_name ||':Validate');
636 END IF;
637
638 Validate_Approval_Rules (
639 p_api_version => l_api_version,
640 p_init_msg_list => p_init_msg_list,
641 p_commit => p_commit,
642 p_validation_level => p_validation_level,
643 x_return_status => l_return_status,
644 x_msg_count => x_msg_count,
645 x_msg_data => x_msg_data,
646 p_Approval_Rules_rec => l_Approval_Rules_rec
647 );
648 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
649 RAISE Fnd_Api.g_exc_error;
650 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
651 RAISE Fnd_Api.g_exc_unexpected_error;
652 END IF;
653 --
654 -- Check for the ID.
655 --
656 IF l_Approval_Rules_rec.APPROVAL_RULE_ID IS Null OR l_Approval_Rules_rec.APPROVAL_RULE_ID = Fnd_Api.g_miss_num THEN
657
658 LOOP
659 --
660 -- If the ID is not passed into the API, then
661 -- grab a value from the sequence.
662 OPEN c_seq;
663 FETCH c_seq INTO l_Approval_Rules_rec.APPROVAL_RULE_ID;
664 CLOSE c_seq;
665
666 --
667 -- Check to be sure that the sequence does not exist.
668 OPEN c_id_exists (l_Approval_Rules_rec.APPROVAL_RULE_ID);
669 FETCH c_id_exists INTO l_dummy;
670 CLOSE c_id_exists;
671 --
672 -- If the value for the ID already exists, then
673 -- l_dummy would be populated with '1', otherwise,
674 -- it receives NULL.
675 EXIT WHEN l_dummy IS NULL;
676 END LOOP;
677 END IF;
678
679 --
680 -- To check whether the Operating Name is valid.
681
682 IF l_Approval_Rules_rec.Operating_Unit_Id IS NULL AND l_Approval_Rules_rec.Operating_Name IS NOT NULL THEN
683 IF p_validation_level >= G_VALID_NAME THEN
684 Check_Operating_Name (
685 p_Approval_Rules_rec => p_Approval_Rules_rec,
686 p_complete_rec => l_Approval_Rules_rec,
687 x_return_status => l_return_status
688 );
689 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
690 RAISE Fnd_Api.g_exc_unexpected_error;
691 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
692 RAISE Fnd_Api.g_exc_error;
693 ELSE
694 OPEN c_operating;
695 FETCH c_operating INTO l_Approval_Rules_rec.Operating_Unit_Id;
696 CLOSE c_operating;
697 END IF;
698 END IF;
699 END IF;
700
701 -- Reema:
702 -- Check whether the Application Usage Code is valid
703 /*IF p_validation_level >= G_VALID_NAME THEN
704 Check_Appl_Usg_Code (
705 p_Approval_Rules_rec => p_Approval_Rules_rec,
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 ELSE
713 OPEN c_appl_usg;
714 FETCH c_appl_usg INTO l_Approval_Rules_rec.Application_Usg_Code;
715 IF c_appl_usg%NOTFOUND THEN
716 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
717 Fnd_Message.set_name ('AHL', 'AHL_APPR_APPUSG_INVALID');
718 Fnd_Msg_Pub.ADD;
719 END IF;
720 RAISE Fnd_Api.g_exc_unexpected_error;
721 END IF;
722 END IF;
723 END IF;
724 */
725 -------------------------- insert --------------------------
726 IF G_DEBUG='Y' THEN
727 Ahl_Debug_Pub.debug( l_full_name ||':Insert');
728 END IF;
729
730 -- Invoke the table handler to create a record
731 --
732
733 Ahl_Approval_Rules_Pkg.Insert_Row (
734 X_ROWID => l_rowid,
735 X_APPROVAL_RULE_ID => l_Approval_Rules_rec.APPROVAL_RULE_ID,
736 X_OBJECT_VERSION_NUMBER => 1,
737 X_APPROVAL_OBJECT_CODE => l_Approval_Rules_rec.APPROVAL_OBJECT_CODE,
738 X_APPROVAL_PRIORITY_CODE=> l_Approval_Rules_rec.APPROVAL_PRIORITY_CODE,
739 X_APPROVAL_TYPE_CODE => l_Approval_Rules_rec.APPROVAL_TYPE_CODE,
740 X_APPLICATION_USG_CODE => l_Approval_Rules_rec.APPLICATION_USG_CODE,
741 X_OPERATING_UNIT_ID => l_Approval_Rules_rec.OPERATING_UNIT_ID,
742 X_ACTIVE_START_DATE => l_Approval_Rules_rec.ACTIVE_START_DATE,
743 X_ACTIVE_END_DATE => l_Approval_Rules_rec.ACTIVE_END_DATE,
744 X_STATUS_CODE => l_status,
745 X_SEEDED_FLAG => l_seed,
746 X_ATTRIBUTE_CATEGORY => l_Approval_Rules_rec.ATTRIBUTE_CATEGORY,
747 X_ATTRIBUTE1 => l_Approval_Rules_rec.ATTRIBUTE1,
748 X_ATTRIBUTE2 => l_Approval_Rules_rec.ATTRIBUTE2,
749 X_ATTRIBUTE3 => l_Approval_Rules_rec.ATTRIBUTE3,
750 X_ATTRIBUTE4 => l_Approval_Rules_rec.ATTRIBUTE4,
751 X_ATTRIBUTE5 => l_Approval_Rules_rec.ATTRIBUTE5,
752 X_ATTRIBUTE6 => l_Approval_Rules_rec.ATTRIBUTE6,
753 X_ATTRIBUTE7 => l_Approval_Rules_rec.ATTRIBUTE7,
754 X_ATTRIBUTE8 => l_Approval_Rules_rec.ATTRIBUTE8,
755 X_ATTRIBUTE9 => l_Approval_Rules_rec.ATTRIBUTE9,
756 X_ATTRIBUTE10 => l_Approval_Rules_rec.ATTRIBUTE10,
757 X_ATTRIBUTE11 => l_Approval_Rules_rec.ATTRIBUTE11,
758 X_ATTRIBUTE12 => l_Approval_Rules_rec.ATTRIBUTE12,
759 X_ATTRIBUTE13 => l_Approval_Rules_rec.ATTRIBUTE13,
760 X_ATTRIBUTE14 => l_Approval_Rules_rec.ATTRIBUTE14,
761 X_ATTRIBUTE15 => l_Approval_Rules_rec.ATTRIBUTE15,
762 X_APPROVAL_RULE_NAME => l_Approval_Rules_rec.APPROVAL_RULE_NAME,
763 X_DESCRIPTION => l_Approval_Rules_rec.DESCRIPTION,
764 X_CREATION_DATE => SYSDATE,
765 X_CREATED_BY => Fnd_Global.USER_ID,
766 X_LAST_UPDATE_DATE => SYSDATE,
767 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
768 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
769
770 ------------------------- finish -------------------------------
771 -- set OUT value
772 x_Approval_Rules_id := l_Approval_Rules_rec.APPROVAL_RULE_ID;
773
774 --
775 -- END of API body.
776 --
777 -- Standard check of p_commit.
778 IF Fnd_Api.To_Boolean ( p_commit ) THEN
779 COMMIT WORK;
780 END IF;
781 Fnd_Msg_Pub.count_and_get(
782 p_encoded => Fnd_Api.g_false,
783 p_count => x_msg_count,
784 p_data => x_msg_data
785 );
786 IF G_DEBUG='Y' THEN
787 Ahl_Debug_Pub.debug( l_full_name ||':End');
788 END IF;
789 -- Check if API is called in debug mode. If yes, disable debug.
790 IF G_DEBUG='Y' THEN
791 Ahl_Debug_Pub.disable_debug;
792 END IF;
793
794 EXCEPTION
795 WHEN Fnd_Api.g_exc_error THEN
796 ROLLBACK TO Create_Approval_Rules;
797 x_return_status := Fnd_Api.g_ret_sts_error;
798 Fnd_Msg_Pub.count_and_get(
799 p_encoded => Fnd_Api.g_false,
800 p_count => x_msg_count,
801 p_data => x_msg_data
802 );
803 WHEN Fnd_Api.g_exc_unexpected_error THEN
804 ROLLBACK TO Create_Approval_Rules;
805 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
806 Fnd_Msg_Pub.count_and_get (
807 p_encoded => Fnd_Api.g_false,
808 p_count => x_msg_count,
809 p_data => x_msg_data
810 );
811 WHEN OTHERS THEN
812 ROLLBACK TO Create_Approval_Rules;
813 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
814 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
815 THEN
816 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
817 END IF;
818 Fnd_Msg_Pub.count_and_get (
819 p_encoded => Fnd_Api.g_false,
820 p_count => x_msg_count,
821 p_data => x_msg_data
822 );
823 END Create_Approval_Rules;
824
825 --------------------------------------------------------------------
826 -- PROCEDURE
827 -- Update_Approval_Rules
828 --
829 --------------------------------------------------------------------
830 PROCEDURE Update_Approval_Rules (
831 p_api_version IN NUMBER,
832 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
833 p_commit IN VARCHAR2,
834 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
835 x_return_status OUT NOCOPY VARCHAR2,
836 x_msg_count OUT NOCOPY NUMBER,
837 x_msg_data OUT NOCOPY VARCHAR2,
838 p_Approval_Rules_rec IN Approval_Rules_Rec_Type
839 )
840 IS
841 L_API_VERSION CONSTANT NUMBER := 1.0;
842 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Approval_Rules';
843 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
844 l_Approval_Rules_rec Approval_Rules_Rec_Type := p_Approval_Rules_rec;
845 l_dummy NUMBER;
846 l_return_status VARCHAR2(1);
847 l_seed VARCHAR2(1);
848 l_status VARCHAR2(30);
849 l_operating_unit NUMBER;
850 CURSOR c_operating IS
851 SELECT ORGANIZATION_ID
852 FROM HR_OPERATING_UNITS
853 WHERE NAME = l_Approval_Rules_rec.OPERATING_NAME;
854
855 CURSOR CUR_STATUS IS
856 SELECT Status_code
857 FROM Ahl_approval_rules_b
858 WHERE Approval_rule_id = p_Approval_Rules_rec.approval_rule_id;
859
860 CURSOR CUR_SEED IS
861 SELECT Seeded_Flag
862 FROM Ahl_approval_rules_b
863 WHERE Approval_rule_id = p_Approval_Rules_rec.approval_rule_id;
864
865 CURSOR c_appl_usg IS
866 SELECT LOOKUP_CODE
867 FROM FND_LOOKUPS
868 WHERE MEANING = l_Approval_Rules_rec.APPLICATION_USG
869 AND LOOKUP_TYPE = 'AHL_APPLICATION_USAGE_CODE';
870
871 BEGIN
872 --------------------- initialize -----------------------
873 SAVEPOINT Update_Approval_Rules;
874
875 -- Check if API is called in debug mode. If yes, enable debug.
876 IF G_DEBUG='Y' THEN
877 Ahl_Debug_Pub.enable_debug;
878 END IF;
879 -- Debug info.
880 IF G_DEBUG='Y' THEN
881 Ahl_Debug_Pub.debug( l_full_name ||':Start');
882 END IF;
883 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
884 Fnd_Msg_Pub.initialize;
885 END IF;
886 IF NOT Fnd_Api.compatible_api_call(
887 l_api_version,
888 p_api_version,
889 l_api_name,
890 G_PKG_NAME
891 ) THEN
892 RAISE Fnd_Api.g_exc_unexpected_error;
893 END IF;
894 x_return_status := Fnd_Api.g_ret_sts_success;
895 ----------------------- validate ----------------------
896 IF G_DEBUG='Y' THEN
897 Ahl_Debug_Pub.debug( l_full_name ||':Validate');
898 END IF;
899
900 --
901 -- To check whether the Operating Name is valid.
902 IF l_Approval_Rules_rec.Operating_Unit_Id IS NULL AND l_Approval_Rules_rec.Operating_Name IS NOT NULL THEN
903 IF p_validation_level >= G_VALID_NAME THEN
904 Check_Operating_Name (
905 p_Approval_Rules_rec => p_Approval_Rules_rec,
906 p_complete_rec => l_Approval_Rules_rec,
907 x_return_status => l_return_status
908 );
909 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
910 RAISE Fnd_Api.g_exc_unexpected_error;
911 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
912 RAISE Fnd_Api.g_exc_error;
913 ELSE
914 OPEN c_operating;
915 FETCH c_operating INTO l_operating_unit;
916 CLOSE c_operating;
917 END IF;
918 END IF;
919 ELSIF l_Approval_Rules_rec.Operating_Unit_Id IS NOT NULL AND l_Approval_Rules_rec.Operating_Name IS NULL THEN
920 l_operating_unit := NULL;
921 ELSIF l_Approval_Rules_rec.Operating_Unit_Id IS NOT NULL AND l_Approval_Rules_rec.Operating_Name IS NOT NULL THEN
922 OPEN c_operating;
923 FETCH c_operating INTO l_operating_unit;
924 CLOSE c_operating;
925 ELSE
926 l_operating_unit := NULL;
927 END IF;
928
929
930
931 -- replace g_miss_char/num/date with current column values
932 Complete_Approval_Rules_Rec (p_Approval_Rules_rec, l_Approval_Rules_rec);
933 -- Reema:
934 -- Check whether the Application Usage Code is valid
935 /*
936 IF p_Approval_Rules_rec.application_usg IS NOT NULL AND p_validation_level >= G_VALID_NAME THEN
937 Check_Appl_Usg_Code (
938 p_Approval_Rules_rec => p_Approval_Rules_rec,
939 x_return_status => l_return_status
940 );
941 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
942 RAISE Fnd_Api.g_exc_unexpected_error;
943 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
944 RAISE Fnd_Api.g_exc_error;
945 ELSE
946 OPEN c_appl_usg;
947 FETCH c_appl_usg INTO l_Approval_Rules_rec.Application_Usg_Code;
948 IF c_appl_usg%NOTFOUND THEN
949 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
950 Fnd_Message.set_name ('AHL', 'AHL_APPR_APPUSG_INVALID');
951 Fnd_Msg_Pub.ADD;
952 END IF;
953 RAISE Fnd_Api.g_exc_unexpected_error;
954 END IF;
955 END IF;
956 END IF;*/
957 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
958 Check_Approval_Rules_Items (
959 p_Approval_Rules_rec => p_Approval_Rules_rec,
960 p_validation_mode => Jtf_Plsql_Api.g_update,
961 x_return_status => l_return_status
962 );
963 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
964 RAISE Fnd_Api.g_exc_unexpected_error;
965 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
966 RAISE Fnd_Api.g_exc_error;
967 END IF;
968 END IF;
969
970 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_record THEN
971 Check_Approval_Rules_Record (
972 p_Approval_Rules_rec => p_Approval_Rules_rec,
973 p_complete_rec => l_Approval_Rules_rec,
974 x_return_status => l_return_status
975 );
976 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
977 RAISE Fnd_Api.g_exc_unexpected_error;
978 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
979 RAISE Fnd_Api.g_exc_error;
980 END IF;
981 END IF;
982
983 IF p_validation_level >= G_VALID_QUALIFIER THEN
984 Check_Active_for_Qualifier (
985 p_Approval_Rules_rec => p_Approval_Rules_rec,
986 p_complete_rec => l_Approval_Rules_rec,
987 x_return_status => l_return_status
988 );
989 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
990 RAISE Fnd_Api.g_exc_unexpected_error;
991 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
992 RAISE Fnd_Api.g_exc_error;
993 END IF;
994 END IF;
995
996 --
997 -- Status updated to 'ACTIVE' only if at least one Approver defined for the Approval Rule
998 IF p_validation_level >= G_VALID_APPROVER THEN
999 Check_Approver_for_Rule (
1000 p_Approval_Rules_rec => p_Approval_Rules_rec,
1001 p_complete_rec => l_Approval_Rules_rec,
1002 x_return_status => l_return_status
1003 );
1004 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1005 RAISE Fnd_Api.g_exc_unexpected_error;
1006 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
1007 RAISE Fnd_Api.g_exc_error;
1008 END IF;
1009 END IF;
1010
1011 --
1012 -- Checking the combinations of status updated if matches.
1013 OPEN CUR_STATUS;
1014 FETCH CUR_STATUS INTO l_status;
1015 CLOSE CUR_STATUS;
1016
1017 IF G_DEBUG='Y' THEN
1018 Ahl_Debug_Pub.debug( l_full_name ||':before status order Update');
1019 END IF;
1020
1021 IF UPPER(l_status) <> UPPER(p_Approval_Rules_rec.status_code) THEN
1022
1023 Ahl_Utility_Pvt.check_status_order_change (
1024 'AHL_APPR_STATUS_TYPE',
1025 l_status,
1026 p_Approval_Rules_rec.status_code,
1027 l_return_status
1028 );
1029 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1030 RAISE Fnd_Api.g_exc_unexpected_error;
1031 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
1032 RAISE Fnd_Api.g_exc_error;
1033 END IF;
1034 END IF;
1035
1036 --
1037 --
1038 /* IF p_validation_level >= G_MATCH_STATUS THEN
1039
1040 Check_Match_for_Status (
1041 p_Approval_Rules_rec => p_Approval_Rules_rec,
1042 p_complete_rec => l_Approval_Rules_rec,
1043 x_return_status => l_return_status
1044 );
1045 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1046 RAISE Fnd_Api.g_exc_unexpected_error;
1047 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
1048 RAISE Fnd_Api.g_exc_error;
1049 END IF;
1050 END IF; */
1051
1052 --
1053 -- retrieve value of seeded flag from cursor.
1054 OPEN CUR_SEED;
1055 FETCH CUR_SEED INTO l_seed;
1056 CLOSE CUR_SEED;
1057
1058 -------------------------- update --------------------
1059 IF G_DEBUG='Y' THEN
1060 Ahl_Debug_Pub.debug( l_full_name ||':Update');
1061 END IF;
1062
1063 Ahl_Approval_Rules_Pkg.UPDATE_ROW (
1064 X_APPROVAL_RULE_ID => l_Approval_Rules_rec.APPROVAL_RULE_ID,
1065 X_OBJECT_VERSION_NUMBER => l_Approval_Rules_rec.OBJECT_VERSION_NUMBER + 1,
1066 X_APPROVAL_OBJECT_CODE => l_Approval_Rules_rec.APPROVAL_OBJECT_CODE,
1067 X_APPROVAL_PRIORITY_CODE=> l_Approval_Rules_rec.APPROVAL_PRIORITY_CODE,
1068 X_APPROVAL_TYPE_CODE => l_Approval_Rules_rec.APPROVAL_TYPE_CODE,
1069 X_APPLICATION_USG_CODE => l_Approval_Rules_Rec.APPLICATION_USG_CODE,
1070 X_OPERATING_UNIT_ID => l_operating_unit,
1071 X_ACTIVE_START_DATE => l_Approval_Rules_rec.ACTIVE_START_DATE,
1072 X_ACTIVE_END_DATE => l_Approval_Rules_rec.ACTIVE_END_DATE,
1073 X_STATUS_CODE => l_Approval_Rules_rec.STATUS_CODE,
1074 X_SEEDED_FLAG => l_seed,
1075 X_ATTRIBUTE_CATEGORY => l_Approval_Rules_rec.ATTRIBUTE_CATEGORY,
1076 X_ATTRIBUTE1 => l_Approval_Rules_rec.ATTRIBUTE1,
1077 X_ATTRIBUTE2 => l_Approval_Rules_rec.ATTRIBUTE2,
1078 X_ATTRIBUTE3 => l_Approval_Rules_rec.ATTRIBUTE3,
1079 X_ATTRIBUTE4 => l_Approval_Rules_rec.ATTRIBUTE4,
1080 X_ATTRIBUTE5 => l_Approval_Rules_rec.ATTRIBUTE5,
1081 X_ATTRIBUTE6 => l_Approval_Rules_rec.ATTRIBUTE6,
1082 X_ATTRIBUTE7 => l_Approval_Rules_rec.ATTRIBUTE7,
1083 X_ATTRIBUTE8 => l_Approval_Rules_rec.ATTRIBUTE8,
1084 X_ATTRIBUTE9 => l_Approval_Rules_rec.ATTRIBUTE9,
1085 X_ATTRIBUTE10 => l_Approval_Rules_rec.ATTRIBUTE10,
1086 X_ATTRIBUTE11 => l_Approval_Rules_rec.ATTRIBUTE11,
1087 X_ATTRIBUTE12 => l_Approval_Rules_rec.ATTRIBUTE12,
1088 X_ATTRIBUTE13 => l_Approval_Rules_rec.ATTRIBUTE13,
1089 X_ATTRIBUTE14 => l_Approval_Rules_rec.ATTRIBUTE14,
1090 X_ATTRIBUTE15 => l_Approval_Rules_rec.ATTRIBUTE15,
1091 X_APPROVAL_RULE_NAME => l_Approval_Rules_rec.APPROVAL_RULE_NAME,
1092 X_DESCRIPTION => l_Approval_Rules_rec.DESCRIPTION,
1093 X_LAST_UPDATE_DATE => SYSDATE,
1094 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
1095 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID );
1096
1097 -------------------- finish --------------------------
1098 IF Fnd_Api.to_boolean (p_commit) THEN
1099 COMMIT;
1100 END IF;
1101 Fnd_Msg_Pub.count_and_get (
1102 p_encoded => Fnd_Api.g_false,
1103 p_count => x_msg_count,
1104 p_data => x_msg_data
1105 );
1106
1107 IF G_DEBUG='Y' THEN
1108 Ahl_Debug_Pub.debug( l_full_name ||':End');
1109 END IF;
1110
1111 -- Check if API is called in debug mode. If yes, disable debug.
1112 IF G_DEBUG='Y' THEN
1113 Ahl_Debug_Pub.disable_debug;
1114 END IF;
1115 EXCEPTION
1116 WHEN Fnd_Api.g_exc_error THEN
1117 ROLLBACK TO Update_Approval_Rules;
1118 x_return_status := Fnd_Api.g_ret_sts_error;
1119 Fnd_Msg_Pub.count_and_get (
1120 p_encoded => Fnd_Api.g_false,
1121 p_count => x_msg_count,
1122 p_data => x_msg_data
1123 );
1124 WHEN Fnd_Api.g_exc_unexpected_error THEN
1125 ROLLBACK TO Update_Approval_Rules;
1126 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1127 Fnd_Msg_Pub.count_and_get (
1128 p_encoded => Fnd_Api.g_false,
1129 p_count => x_msg_count,
1130 p_data => x_msg_data
1131 );
1132 WHEN OTHERS THEN
1133 ROLLBACK TO update_Approval_Rules;
1134 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1135 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1136 THEN
1137 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1138 END IF;
1139 Fnd_Msg_Pub.count_and_get (
1140 p_encoded => Fnd_Api.g_false,
1141 p_count => x_msg_count,
1142 p_data => x_msg_data
1143 );
1144 END Update_Approval_Rules;
1145
1146 --------------------------------------------------------------------
1147 -- PROCEDURE
1148 -- Check_Approver_for_Rule
1149 -- Status updated to 'ACTIVE' only if at least one Approver defined for the Approval Rule
1150 --------------------------------------------------------------------
1151
1152 PROCEDURE Check_Approver_for_Rule (
1153 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
1154 p_complete_rec IN Approval_Rules_Rec_Type := NULL,
1155 x_return_status OUT NOCOPY VARCHAR2
1156 )
1157 IS
1158 l_count NUMBER;
1159 l_status VARCHAR2(30);
1160
1161 CURSOR check_approver1 IS
1162 SELECT 1 FROM ahl_approvers
1163 WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id;
1164
1165 CURSOR chk_STATUS IS
1166 SELECT status_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id;
1167 BEGIN
1168 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1169 OPEN chk_STATUS;
1170 FETCH chk_STATUS INTO l_status;
1171 CLOSE chk_STATUS;
1172 IF UPPER(l_status) <> UPPER(p_Approval_Rules_rec.status_code) THEN
1173 IF UPPER(p_Approval_Rules_rec.STATUS_CODE) = 'ACTIVE' THEN
1174 /*IF p_Approval_Rules_rec.approval_priority_code IS NOT NULL AND p_Approval_Rules_rec.operating_unit_id IS NOT NULL THEN*/
1175 OPEN check_approver1;
1176 FETCH check_approver1 INTO l_count;
1177
1178 IF check_approver1%NOTFOUND THEN
1179 CLOSE check_approver1;
1180 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1181 Fnd_Message.set_name ('AHL', 'AHL_APPR_STATUS_NOT_TO_ACTIVE');
1182 Fnd_Msg_Pub.ADD;
1183 END IF;
1184 x_return_status := Fnd_Api.g_ret_sts_error;
1185 RETURN;
1186 ELSE
1187 CLOSE check_approver1;
1188 END IF;
1189 /*ELSIF p_Approval_Rules_rec.approval_priority_code IS NULL AND p_Approval_Rules_rec.operating_unit_id IS NOT NULL THEN
1190 Open check_approver2;
1191 Fetch check_approver2 into l_count;
1192 IF check_approver2%notfound THEN
1193 Close check_approver1;
1194 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1195 Fnd_Message.set_name ('AHL', 'AHL_APPR_STATUS_NOT_TO_ACTIVE');
1196 Fnd_Msg_Pub.ADD;
1197 END IF;
1198 x_return_status := Fnd_Api.g_ret_sts_error;
1199 RETURN;
1200 ELSE
1201 Close check_approver2;
1202 END IF;
1203 ELSIF p_Approval_Rules_rec.approval_priority_code IS NOT NULL AND p_Approval_Rules_rec.operating_unit_id IS NULL THEN
1204 Open check_approver3;
1205 Fetch check_approver3 into l_count;
1206 IF check_approver3%notfound THEN
1207 Close check_approver3;
1208 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1209 Fnd_Message.set_name ('AHL', 'AHL_APPR_STATUS_NOT_TO_ACTIVE');
1210 Fnd_Msg_Pub.ADD;
1211 END IF;
1212 x_return_status := Fnd_Api.g_ret_sts_error;
1213 RETURN;
1214 ELSE
1215 Close check_approver3;
1216 END IF;
1217 ELSE
1218 Open check_approver4;
1219 Fetch check_approver4 into l_count;
1220 IF check_approver4%notfound THEN
1221 Close check_approver4;
1222 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1223 Fnd_Message.set_name ('AHL', 'AHL_APPR_STATUS_NOT_TO_ACTIVE');
1224 Fnd_Msg_Pub.ADD;
1225 END IF;
1226 x_return_status := Fnd_Api.g_ret_sts_error;
1227 RETURN;
1228 ELSE
1229 Close check_approver4;
1230 END IF;
1231 END IF;*/
1232
1233 END IF;
1234 END IF;
1235 END Check_Approver_for_Rule;
1236
1237 --------------------------------------------------------------------
1238 -- PROCEDURE
1239 -- Check_Active_for_Qualifier
1240 -- to check whether for a particular approval qualifier
1241 -- among all approvals only one of them is to be active
1242 -- Qualifier comprises of 'Approval Object Code', 'Approval Priority Code', 'Approval Type Code' and 'Operating Unit Id'
1243 --------------------------------------------------------------------
1244
1245 PROCEDURE Check_Active_for_Qualifier (
1246 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
1247 p_complete_rec IN Approval_Rules_Rec_Type := NULL,
1248 x_return_status OUT NOCOPY VARCHAR2
1249 )
1250 IS
1251 l_count1 NUMBER;
1252 l_count2 NUMBER;
1253 l_count3 NUMBER;
1254 l_count4 NUMBER;
1255 l_count5 NUMBER;
1256
1257 CURSOR check_active1 IS
1258 SELECT 1 FROM ahl_approval_rules_b
1259 WHERE UPPER(status_code) = 'ACTIVE'
1260 AND operating_unit_id = (SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS WHERE NAME = p_Approval_Rules_rec.OPERATING_NAME)
1261 AND approval_priority_code = p_Approval_Rules_rec.approval_priority_code
1262 AND application_usg_code = (SELECT application_usg_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1263 AND approval_object_code = (SELECT approval_object_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1264 AND approval_type_code = (SELECT approval_type_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1265 AND approval_rule_id <> p_Approval_Rules_rec.approval_rule_id;
1266
1267 CURSOR check_active2 IS
1268 SELECT 1 FROM ahl_approval_rules_b
1269 WHERE UPPER(status_code) = 'ACTIVE'
1270 AND operating_unit_id = (SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS WHERE NAME = p_Approval_Rules_rec.OPERATING_NAME)
1271 AND approval_priority_code IS NULL
1272 AND application_usg_code = (SELECT application_usg_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1273 AND approval_object_code = (SELECT approval_object_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1274 AND approval_type_code IS NULL
1275 AND approval_rule_id <> p_Approval_Rules_rec.approval_rule_id;
1276
1277 CURSOR check_active3 IS
1278 SELECT 1 FROM ahl_approval_rules_b
1279 WHERE UPPER(status_code) = 'ACTIVE'
1280 AND operating_unit_id IS NULL
1281 AND approval_priority_code = p_Approval_Rules_rec.approval_priority_code
1282 AND application_usg_code = (SELECT application_usg_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1283 AND approval_object_code = (SELECT approval_object_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1284 AND approval_type_code = (SELECT approval_type_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1285 AND approval_rule_id <> p_Approval_Rules_rec.approval_rule_id;
1286
1287 CURSOR check_active4 IS
1288 SELECT 1 FROM ahl_approval_rules_b
1289 WHERE UPPER(status_code) = 'ACTIVE'
1290 AND operating_unit_id IS NULL
1291 AND approval_priority_code IS NULL
1292 AND application_usg_code =(SELECT application_usg_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1293 AND approval_object_code = (SELECT approval_object_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1294 AND approval_type_code = (SELECT approval_type_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1295 AND approval_rule_id <> p_Approval_Rules_rec.approval_rule_id;
1296 CURSOR check_active5 IS
1297 SELECT 1 FROM ahl_approval_rules_b
1298 WHERE UPPER(status_code) = 'ACTIVE'
1299 AND operating_unit_id IS NULL
1300 AND application_usg_code = (SELECT application_usg_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1301 AND approval_object_code = (SELECT approval_object_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id)
1302 AND approval_rule_id <> p_Approval_Rules_rec.approval_rule_id;
1303 BEGIN
1304 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1305
1306 IF p_Approval_Rules_rec.STATUS_CODE='ACTIVE' THEN
1307
1308 IF p_Approval_Rules_rec.approval_priority_code IS NOT NULL AND p_Approval_Rules_rec.OPERATING_NAME IS NOT NULL THEN
1309 OPEN check_active1;
1310 FETCH check_active1 INTO l_count1;
1311 IF check_active1%FOUND THEN
1312 CLOSE check_active1;
1313 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1314 Fnd_Message.set_name ('AHL', 'AHL_APPR_QUALIFIER_ACTIVE');
1315 Fnd_Msg_Pub.ADD;
1316 END IF;
1317 x_return_status := Fnd_Api.g_ret_sts_error;
1318 RETURN;
1319 ELSE
1320 CLOSE check_active1;
1321 END IF;
1322 ELSIF p_Approval_Rules_rec.approval_priority_code IS NULL AND p_Approval_Rules_rec.OPERATING_NAME IS NOT NULL THEN
1323 OPEN check_active2;
1324 FETCH check_active2 INTO l_count2;
1325 IF check_active2%FOUND THEN
1326 CLOSE check_active2;
1327 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1328 Fnd_Message.set_name ('AHL', 'AHL_APPR_QUALIFIER_ACTIVE');
1329 Fnd_Msg_Pub.ADD;
1330 END IF;
1331 x_return_status := Fnd_Api.g_ret_sts_error;
1332 RETURN;
1333 ELSE
1334 CLOSE check_active2;
1335 END IF;
1336 ELSIF p_Approval_Rules_rec.approval_priority_code IS NOT NULL AND p_Approval_Rules_rec.OPERATING_NAME IS NULL THEN
1337 OPEN check_active3;
1338 FETCH check_active3 INTO l_count3;
1339 IF check_active3%FOUND THEN
1340 CLOSE check_active3;
1341 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1342 Fnd_Message.set_name ('AHL', 'AHL_APPR_QUALIFIER_ACTIVE');
1343 Fnd_Msg_Pub.ADD;
1344 END IF;
1345 x_return_status := Fnd_Api.g_ret_sts_error;
1346 RETURN;
1347 ELSE
1348 CLOSE check_active3;
1349 END IF;
1350 ELSIF p_Approval_Rules_rec.approval_priority_code IS NULL AND p_Approval_Rules_rec.OPERATING_NAME IS NULL AND p_Approval_Rules_rec.APPROVAL_TYPE_CODE IS NOT NULL THEN
1351 OPEN check_active4;
1352 FETCH check_active4 INTO l_count4;
1353 IF check_active4%FOUND THEN
1354 CLOSE check_active4;
1355 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1356 Fnd_Message.set_name ('AHL', 'AHL_APPR_QUALIFIER_ACTIVE');
1357 Fnd_Msg_Pub.ADD;
1358 END IF;
1359 x_return_status := Fnd_Api.g_ret_sts_error;
1360 RETURN;
1361 ELSE
1362 CLOSE check_active4;
1363 END IF;
1364 ELSIF p_Approval_Rules_rec.approval_priority_code IS NULL AND p_Approval_Rules_rec.OPERATING_NAME IS NULL AND p_Approval_Rules_rec.APPROVAL_TYPE_CODE IS NULL THEN
1365 OPEN check_active5;
1366 FETCH check_active5 INTO l_count5;
1367 IF check_active5%FOUND THEN
1368 CLOSE check_active5;
1369 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1370 Fnd_Message.set_name ('AHL', 'AHL_APPR_QUALIFIER_ACTIVE');
1371 Fnd_Msg_Pub.ADD;
1372 END IF;
1373 x_return_status := Fnd_Api.g_ret_sts_error;
1374 RETURN;
1375 ELSE
1376 CLOSE check_active5;
1377 END IF;
1378 END IF;
1379 END IF;
1380 END Check_Active_for_Qualifier;
1381
1382 --------------------------------------------------------------------
1383 -- PROCEDURE
1384 -- Check_Operating_Name
1385 -- To check whether the Operating Name is valid.
1386 --------------------------------------------------------------------
1387
1388 PROCEDURE Check_Operating_Name(
1389 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
1390 p_complete_rec IN Approval_Rules_Rec_Type := NULL,
1391 x_return_status OUT NOCOPY VARCHAR2
1392 )
1393 IS
1394 l_count NUMBER;
1395 CURSOR chk_op_name IS
1396 SELECT 1 FROM HR_OPERATING_UNITS
1397 WHERE name = p_Approval_Rules_rec.operating_name;
1398 BEGIN
1399
1400 OPEN chk_op_name;
1401 FETCH chk_op_name INTO l_count;
1402 IF chk_op_name%NOTFOUND THEN
1403 CLOSE chk_op_name;
1404 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1405 Fnd_Message.set_name ('AHL', 'AHL_APPR_NO_OPERATING_NAME');
1406 Fnd_Msg_Pub.ADD;
1407 END IF;
1408 x_return_status := Fnd_Api.g_ret_sts_error;
1409 RETURN;
1410 ELSE
1411 CLOSE chk_op_name ;
1412 END IF;
1413 END Check_Operating_Name;
1414
1415 --------------------------------------------------------------------
1416 -- PROCEDURE
1417 -- Check_Appl_Usg_Code
1418 -- To check whether the Application Usage Code is valid.
1419 --------------------------------------------------------------------
1420
1421 PROCEDURE Check_Appl_Usg_Code(
1422 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
1423 x_return_status OUT NOCOPY VARCHAR2
1424 )
1425 IS
1426 l_count NUMBER;
1427
1428 CURSOR chk_appl_usg_code IS
1429 SELECT 1 FROM FND_LOOKUPS
1430 WHERE meaning = p_Approval_Rules_rec.Application_Usg
1431 AND LOOKUP_TYPE = 'AHL_APPLICATION_USAGE_CODE';
1432 BEGIN
1433 OPEN chk_appl_usg_code;
1434 FETCH chk_appl_usg_code INTO l_count;
1435 IF chk_appl_usg_code%NOTFOUND THEN
1436 CLOSE chk_appl_usg_code;
1437 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1438 Fnd_Message.set_name ('AHL', 'AHL_APPR_APPUSG_INVALID');
1439 Fnd_Msg_Pub.ADD;
1440 END IF;
1441 x_return_status := Fnd_Api.g_ret_sts_error;
1442 RETURN;
1443 ELSE
1444 CLOSE chk_appl_usg_code;
1445 END IF;
1446 END Check_Appl_Usg_Code;
1447
1448 --------------------------------------------------------------------
1449 -- PROCEDURE
1450 -- Check_Match_for_Status
1451 -- Status match from Ahl_Status_Order_Rules for the Approval Rule
1452 --------------------------------------------------------------------
1453
1454 PROCEDURE Check_Match_for_Status (
1455 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
1456 p_complete_rec IN Approval_Rules_Rec_Type,
1457 x_return_status OUT NOCOPY VARCHAR2
1458 )
1459 IS
1460 l_count NUMBER;
1461 l_status VARCHAR2(30);
1462
1463 CURSOR check_status IS
1464 SELECT 1 FROM ahl_approval_rules_b t1, ahl_status_order_rules t2
1465 WHERE t1.approval_rule_id = p_Approval_Rules_rec.approval_rule_id
1466 AND t2.next_status_code = p_Approval_Rules_rec.status_code
1467 AND t2.current_status_code = (SELECT status_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id);
1468
1469 CURSOR CUR_STATUS IS
1470 SELECT status_code FROM ahl_approval_rules_b WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id;
1471
1472 BEGIN
1473 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1474 OPEN CUR_STATUS;
1475 FETCH CUR_STATUS INTO l_status;
1476 CLOSE CUR_STATUS;
1477 IF l_status <> p_Approval_Rules_rec.status_code THEN
1478 OPEN check_status;
1479 FETCH check_status INTO l_count;
1480 IF check_status%NOTFOUND THEN
1481 CLOSE check_status;
1482 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1483 Fnd_Message.set_name ('AHL', 'AHL_APPR_INVALID_STATUS');
1484 Fnd_Msg_Pub.ADD;
1485 END IF;
1486 x_return_status := Fnd_Api.g_ret_sts_error;
1487 RETURN;
1488 ELSE
1489 CLOSE check_status;
1490 END IF;
1491 END IF;
1492 END Check_Match_for_Status;
1493
1494 --------------------------------------------------------------------
1495 -- PROCEDURE
1496 -- Delete_Approval_Rules
1497 --
1498 --------------------------------------------------------------------
1499
1500 PROCEDURE Delete_Approval_Rules (
1501 p_api_version IN NUMBER,
1502 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
1503 p_commit IN VARCHAR2,
1504 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
1505 x_return_status OUT NOCOPY VARCHAR2,
1506 x_msg_count OUT NOCOPY NUMBER,
1507 x_msg_data OUT NOCOPY VARCHAR2,
1508 p_Approval_Rule_id IN NUMBER,
1509 p_object_version IN NUMBER
1510 )
1511
1512
1513 IS
1514 CURSOR c_Approval_Rules IS
1515 SELECT *
1516 FROM Ahl_Approval_Rules_VL
1517 WHERE Approval_Rule_id = p_Approval_Rule_id;
1518 --
1519 -- This is the only exception for using %ROWTYPE.
1520 -- We are selecting from the VL view, which may
1521 -- have some denormalized columns as compared to
1522 -- the base tables.
1523 l_Approval_Rules_rec c_Approval_Rules%ROWTYPE;
1524 l_api_version CONSTANT NUMBER := 1.0;
1525 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Approval_Rules';
1526 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_Api_name;
1527 BEGIN
1528 --------------------- initialize -----------------------
1529 SAVEPOINT Delete_Approval_Rules;
1530 -- Check if API is called in debug mode. If yes, enable debug.
1531 IF G_DEBUG='Y' THEN
1532 Ahl_Debug_Pub.enable_debug;
1533 END IF;
1534 -- Debug info.
1535 IF G_DEBUG='Y' THEN
1536 Ahl_Debug_Pub.debug( l_full_name ||':Start');
1537 END IF;
1538 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
1539 Fnd_Msg_Pub.initialize;
1540 END IF;
1541 IF NOT Fnd_Api.compatible_api_call (
1542 l_api_version,
1543 p_api_version,
1544 l_api_name,
1545 G_PKG_NAME
1546 ) THEN
1547 RAISE Fnd_Api.g_exc_unexpected_error;
1548 END IF;
1549 x_return_status := Fnd_Api.g_ret_sts_success;
1550
1551 ------------------------ delete ------------------------
1552 IF G_DEBUG='Y' THEN
1553 Ahl_Debug_Pub.debug( l_full_name ||':Delete');
1554 END IF;
1555
1556 OPEN c_Approval_Rules;
1557 FETCH c_Approval_Rules INTO l_Approval_Rules_rec;
1558 IF c_Approval_Rules%NOTFOUND THEN
1559 CLOSE c_Approval_Rules;
1560 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
1561 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
1562 Fnd_Msg_Pub.ADD;
1563 END IF;
1564 RAISE Fnd_Api.g_exc_error;
1565 END IF;
1566 CLOSE c_Approval_Rules;
1567 -- Delete TL data
1568
1569 /*DELETE FROM Ahl_Approval_Rules_tl
1570 WHERE Approval_Rule_id = p_Approval_Rule_id;
1571 IF (SQL%NOTFOUND) THEN
1572 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error)
1573 THEN
1574 Fnd_Message.set_name ('AHL', 'AHL_API_RECORD_NOT_FOUND');
1575 Fnd_Msg_Pub.ADD;
1576 END IF;
1577 RAISE Fnd_Api.g_exc_error;
1578 END IF;
1579
1580 DELETE FROM Ahl_Approval_Rules_b
1581 WHERE Approval_Rule_id = p_Approval_Rule_id;*/
1582
1583 UPDATE Ahl_Approval_Rules_B
1584 SET Status_Code = 'OBSOLETE'
1585 WHERE Approval_Rule_id = p_Approval_Rule_id;
1586 IF (SQL%NOTFOUND) THEN
1587 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error)
1588 THEN
1589 Fnd_Message.set_name ('AHL', 'AHL_API_RECORD_NOT_FOUND');
1590 Fnd_Msg_Pub.ADD;
1591 END IF;
1592 RAISE Fnd_Api.g_exc_error;
1593 END IF;
1594
1595 -------------------- finish --------------------------
1596 IF Fnd_Api.to_boolean (p_commit) THEN
1597 COMMIT;
1598 END IF;
1599 Fnd_Msg_Pub.count_and_get (
1600 p_encoded => Fnd_Api.g_false,
1601 p_count => x_msg_count,
1602 p_data => x_msg_data
1603 );
1604 IF G_DEBUG='Y' THEN
1605 Ahl_Debug_Pub.debug( l_full_name ||':End');
1606 END IF;
1607 -- Check if API is called in debug mode. If yes, disable debug.
1608 IF G_DEBUG='Y' THEN
1609 Ahl_Debug_Pub.disable_debug;
1610 END IF;
1611 EXCEPTION
1612 WHEN Fnd_Api.g_exc_error THEN
1613 ROLLBACK TO Delete_Approval_Rules;
1614 x_return_status := Fnd_Api.g_ret_sts_error;
1615 Fnd_Msg_Pub.count_and_get (
1616 p_encoded => Fnd_Api.g_false,
1617 p_count => x_msg_count,
1618 p_data => x_msg_data
1619 );
1620 WHEN Fnd_Api.g_exc_unexpected_error THEN
1621 ROLLBACK TO Delete_Approval_Rules;
1622 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1623 Fnd_Msg_Pub.count_and_get (
1624 p_encoded => Fnd_Api.g_false,
1625 p_count => x_msg_count,
1626 p_data => x_msg_data
1627 );
1628 WHEN OTHERS THEN
1629 ROLLBACK TO Delete_Approval_Rules;
1630 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1631 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1632 THEN
1633 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1634 END IF;
1635 Fnd_Msg_Pub.count_and_get (
1636 p_encoded => Fnd_Api.g_false,
1637 p_count => x_msg_count,
1638 p_data => x_msg_data
1639 );
1640 END Delete_Approval_Rules;
1641
1642 --------------------------------------------------------------------
1643 -- PROCEDURE
1644 -- Validate_Approval_Rules
1645 --
1646 --------------------------------------------------------------------
1647 PROCEDURE Validate_Approval_Rules (
1648 p_api_version IN NUMBER,
1649 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
1650 p_commit IN VARCHAR2,
1651 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
1652 x_return_status OUT NOCOPY VARCHAR2,
1653 x_msg_count OUT NOCOPY NUMBER,
1654 x_msg_data OUT NOCOPY VARCHAR2,
1655 p_Approval_Rules_rec IN Approval_Rules_Rec_Type
1656 )
1657 IS
1658 L_API_VERSION CONSTANT NUMBER := 1.0;
1659 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Approval_Rules';
1660 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1661 l_return_status VARCHAR2(1);
1662 BEGIN
1663 --------------------- initialize -----------------------
1664 -- Check if API is called in debug mode. If yes, enable debug.
1665 IF G_DEBUG='Y' THEN
1666 Ahl_Debug_Pub.enable_debug;
1667 END IF;
1668 -- Debug info.
1669 IF G_DEBUG='Y' THEN
1670 Ahl_Debug_Pub.debug( l_full_name ||':Start');
1671 END IF;
1672 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
1673 Fnd_Msg_Pub.initialize;
1674 END IF;
1675 IF NOT Fnd_Api.compatible_api_call (
1676 l_api_version,
1677 p_api_version,
1678 l_api_name,
1679 G_PKG_NAME
1680 ) THEN
1681 RAISE Fnd_Api.g_exc_unexpected_error;
1682 END IF;
1683 x_return_status := Fnd_Api.g_ret_sts_success;
1684 ---------------------- validate ------------------------
1685 IF G_DEBUG='Y' THEN
1686 Ahl_Debug_Pub.debug( l_full_name ||':Check items');
1687 END IF;
1688 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
1689 Check_Approval_Rules_Items (
1690 p_Approval_Rules_rec => p_Approval_Rules_rec,
1691 p_validation_mode => Jtf_Plsql_Api.g_create,
1692 x_return_status => l_return_status
1693 );
1694 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1695 RAISE Fnd_Api.g_exc_unexpected_error;
1696 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
1697 RAISE Fnd_Api.g_exc_error;
1698 END IF;
1699 END IF;
1700 IF G_DEBUG='Y' THEN
1701 Ahl_Debug_Pub.debug( l_full_name ||':Check record');
1702 END IF;
1703
1704 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_record THEN
1705 Check_Approval_Rules_Record (
1706 p_Approval_Rules_rec => p_Approval_Rules_rec,
1707 p_complete_rec => NULL,
1708 x_return_status => l_return_status
1709 );
1710 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1711 RAISE Fnd_Api.g_exc_unexpected_error;
1712 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
1713 RAISE Fnd_Api.g_exc_error;
1714 END IF;
1715 END IF;
1716 -------------------- finish --------------------------
1717 Fnd_Msg_Pub.count_and_get (
1718 p_encoded => Fnd_Api.g_false,
1719 p_count => x_msg_count,
1720 p_data => x_msg_data
1721 );
1722 IF G_DEBUG='Y' THEN
1723 Ahl_Debug_Pub.debug( l_full_name ||':End');
1724 END IF;
1725 -- Check if API is called in debug mode. If yes, disable debug.
1726 IF G_DEBUG='Y' THEN
1727 Ahl_Debug_Pub.disable_debug;
1728 END IF;
1729 EXCEPTION
1730 WHEN Fnd_Api.g_exc_error THEN
1731 x_return_status := Fnd_Api.g_ret_sts_error;
1732 Fnd_Msg_Pub.count_and_get (
1733 p_encoded => Fnd_Api.g_false,
1734 p_count => x_msg_count,
1735 p_data => x_msg_data
1736 );
1737 WHEN Fnd_Api.g_exc_unexpected_error THEN
1738 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
1739 Fnd_Msg_Pub.count_and_get (
1740 p_encoded => Fnd_Api.g_false,
1741 p_count => x_msg_count,
1742 p_data => x_msg_data
1743 );
1744 WHEN OTHERS THEN
1745 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
1746 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
1747 THEN
1748 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
1749 END IF;
1750 Fnd_Msg_Pub.count_and_get (
1751 p_encoded => Fnd_Api.g_false,
1752 p_count => x_msg_count,
1753 p_data => x_msg_data
1754 );
1755 END Validate_Approval_Rules;
1756
1757 ---------------------------------------------------------------------
1758 -- PROCEDURE
1759 -- Check_Approval_Rules_Items
1760 --
1761 ---------------------------------------------------------------------
1762 PROCEDURE Check_Approval_Rules_Items (
1763 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
1764 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
1765 x_return_status OUT NOCOPY VARCHAR2
1766 )
1767 IS
1768 BEGIN
1769 --
1770 -- Validate required items.
1771 Check_Approval_Rules_Req_Items (
1772 p_Approval_Rules_rec => p_Approval_Rules_rec,
1773 x_return_status => x_return_status
1774 );
1775 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1776 RETURN;
1777 END IF;
1778
1779 --
1780 -- Validate uniqueness.
1781 Check_Approval_Rules_UK_Items (
1782 p_Approval_Rules_rec => p_Approval_Rules_rec,
1783 p_validation_mode => p_validation_mode,
1784 x_return_status => x_return_status
1785 );
1786 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
1787 RETURN;
1788 END IF;
1789
1790 END Check_Approval_Rules_Items;
1791 ---------------------------------------------------------------------
1792 -- PROCEDURE
1793 -- Check_Approval_Rules_Record
1794 --
1795 -- PURPOSE
1796 -- Check the record level business rules.
1797 --
1798 -- PARAMETERS
1799 -- p_Approval_Rules_rec: the record to be validated; may contain attributes
1800 -- as FND_API.g_miss_char/num/date
1801 -- p_complete_rec: the complete record after all "g_miss" items
1802 -- have been replaced by current database values
1803 ---------------------------------------------------------------------
1804 PROCEDURE Check_Approval_Rules_Record (
1805 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
1806 p_complete_rec IN Approval_Rules_Rec_Type := NULL,
1807 x_return_status OUT NOCOPY VARCHAR2
1808 )
1809 IS
1810 l_active_start_date DATE;
1811 l_active_end_date DATE;
1812 BEGIN
1813 --
1814 -- Use local vars to reduce amount of typing.
1815 IF p_complete_rec.active_start_date IS NOT NULL THEN
1816 l_active_start_date := p_complete_rec.active_start_date;
1817 ELSE
1818 IF p_Approval_Rules_rec.active_start_date IS NOT NULL AND
1819 p_Approval_Rules_rec.active_start_date <> Fnd_Api.g_miss_date THEN
1820 l_active_start_date := p_Approval_Rules_rec.active_start_date;
1821 END IF;
1822 END IF;
1823
1824 IF p_complete_rec.active_end_date IS NOT NULL THEN
1825 l_active_end_date := p_complete_rec.active_end_date;
1826 ELSE
1827 IF p_Approval_Rules_rec.active_end_date IS NOT NULL AND
1828 p_Approval_Rules_rec.active_end_date <> Fnd_Api.g_miss_date THEN
1829 l_active_end_date := p_Approval_Rules_rec.active_end_date;
1830 END IF;
1831 END IF;
1832
1833 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1834 --
1835 -- Validate the active dates.
1836 IF l_active_start_date IS NOT NULL AND l_active_end_date IS NOT NULL THEN
1837 IF l_active_start_date > l_active_end_date THEN
1838 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1839 Fnd_Message.set_name ('AHL', 'AHL_APPR_FROMDT_GTR_TODT');
1840 Fnd_Msg_Pub.ADD;
1841 END IF;
1842 x_return_status := Fnd_Api.g_ret_sts_error;
1843 RETURN;
1844 END IF;
1845
1846 IF l_active_start_date = l_active_end_date THEN
1847 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
1848 Fnd_Message.set_name ('AHL', 'AHL_APPR_FROMDT_EQU_TODT');
1849 Fnd_Msg_Pub.ADD;
1850 END IF;
1851 x_return_status := Fnd_Api.g_ret_sts_error;
1852 RETURN;
1853 END IF;
1854 END IF;
1855
1856 END Check_Approval_Rules_Record;
1857
1858
1859 ---------------------------------------------------------------------
1860 -- PROCEDURE
1861 -- Init_Approval_Rules_Rec
1862 --
1863 ---------------------------------------------------------------------
1864 /*PROCEDURE Init_Approval_Rules_Rec (
1865 x_Approval_Rules_rec OUT NOCOPY Approval_Rules_Rec_Type
1866 )
1867 IS
1868 BEGIN
1869 x_Approval_Rules_rec.approval_rule_id := Fnd_Api.g_miss_num;
1870 x_Approval_Rules_rec.object_version_number := Fnd_Api.g_miss_num;
1871 x_Approval_Rules_rec.approval_object_code := Fnd_Api.g_miss_char;
1872 x_Approval_Rules_rec.approval_priority_code := Fnd_Api.g_miss_char;
1873 x_Approval_Rules_rec.approval_type_code := Fnd_Api.g_miss_char;
1874 x_Approval_Rules_rec.OPERATING_UNIT_ID := Fnd_Api.g_miss_num;
1875 x_Approval_Rules_rec.active_start_date := Fnd_Api.g_miss_date;
1876 x_Approval_Rules_rec.active_end_date := Fnd_Api.g_miss_date;
1877 x_Approval_Rules_rec.status_code := Fnd_Api.g_miss_char;
1878 x_Approval_Rules_rec.approval_rule_name := Fnd_Api.g_miss_char;
1879 x_Approval_Rules_rec.description := Fnd_Api.g_miss_char;
1880 x_Approval_Rules_rec.last_update_date := Fnd_Api.g_miss_date;
1881 x_Approval_Rules_rec.last_updated_by := Fnd_Api.g_miss_num;
1882 x_Approval_Rules_rec.creation_date := Fnd_Api.g_miss_date;
1883 x_Approval_Rules_rec.created_by := Fnd_Api.g_miss_num;
1884 x_Approval_Rules_rec.last_update_login := Fnd_Api.g_miss_num;
1885 x_Approval_Rules_rec.attribute_category := Fnd_Api.g_miss_char;
1886 x_Approval_Rules_rec.attribute1 := Fnd_Api.g_miss_char;
1887 x_Approval_Rules_rec.attribute2 := Fnd_Api.g_miss_char;
1888 x_Approval_Rules_rec.attribute3 := Fnd_Api.g_miss_char;
1889 x_Approval_Rules_rec.attribute4 := Fnd_Api.g_miss_char;
1890 x_Approval_Rules_rec.attribute5 := Fnd_Api.g_miss_char;
1891 x_Approval_Rules_rec.attribute6 := Fnd_Api.g_miss_char;
1892 x_Approval_Rules_rec.attribute7 := Fnd_Api.g_miss_char;
1893 x_Approval_Rules_rec.attribute8 := Fnd_Api.g_miss_char;
1894 x_Approval_Rules_rec.attribute9 := Fnd_Api.g_miss_char;
1895 x_Approval_Rules_rec.attribute10 := Fnd_Api.g_miss_char;
1896 x_Approval_Rules_rec.attribute11 := Fnd_Api.g_miss_char;
1897 x_Approval_Rules_rec.attribute12 := Fnd_Api.g_miss_char;
1898 x_Approval_Rules_rec.attribute13 := Fnd_Api.g_miss_char;
1899 x_Approval_Rules_rec.attribute14 := Fnd_Api.g_miss_char;
1900 x_Approval_Rules_rec.attribute15 := Fnd_Api.g_miss_char;
1901
1902 END Init_Approval_Rules_Rec;
1903 */
1904 ---------------------------------------------------------------------
1905 -- PROCEDURE
1906 -- Complete_Approval_Rules_Rec
1907 --
1908 ---------------------------------------------------------------------
1909 PROCEDURE Complete_Approval_Rules_Rec (
1910 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
1911 x_complete_rec OUT NOCOPY Approval_Rules_Rec_Type
1912 )
1913 IS
1914 CURSOR c_Approval_Rules IS
1915 SELECT *
1916 FROM Ahl_Approval_Rules_v
1917 WHERE Approval_Rule_id = p_Approval_Rules_rec.Approval_Rule_id;
1918 --
1919 -- This is the only exception for using %ROWTYPE.
1920 -- We are selecting from the VL view, which may
1921 -- have some denormalized columns as compared to
1922 -- the base tables.
1923 l_Approval_Rules_rec c_Approval_Rules%ROWTYPE;
1924 BEGIN
1925 x_complete_rec := p_Approval_Rules_rec;
1926 OPEN c_Approval_Rules;
1927 FETCH c_Approval_Rules INTO l_Approval_Rules_rec;
1928 IF c_Approval_Rules%NOTFOUND THEN
1929 CLOSE c_Approval_Rules;
1930 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
1931 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
1932 Fnd_Msg_Pub.ADD;
1933 END IF;
1934 RAISE Fnd_Api.g_exc_error;
1935 END IF;
1936 CLOSE c_Approval_Rules;
1937 --
1938 -- APPROVAL_OBJECT_CODE
1939 IF p_Approval_Rules_rec.approval_object_code is null or p_Approval_Rules_rec.approval_object_code = Fnd_Api.g_miss_char THEN
1940 x_complete_rec.approval_object_code := l_Approval_Rules_rec.approval_object_code;
1941 END IF;
1942
1943 -- APPLICATION_USG_CODE
1944 IF p_Approval_Rules_rec.application_usg_code is null or p_Approval_Rules_rec.application_usg_code = Fnd_Api.g_miss_char THEN
1945 x_complete_rec.application_usg_code := l_Approval_Rules_rec.application_usg_code;
1946 END IF;
1947 --
1948 -- APPROVAL_TYPE_CODE
1949 IF p_Approval_Rules_rec.approval_type_code is null or p_Approval_Rules_rec.approval_type_code = Fnd_Api.g_miss_char THEN
1950 x_complete_rec.approval_type_code := l_Approval_Rules_rec.approval_type_code;
1951 END IF;
1952
1953 --
1954 -- STATUS_CODE
1955 IF p_Approval_Rules_rec.status_code is null or p_Approval_Rules_rec.status_code = Fnd_Api.g_miss_char THEN
1956 x_complete_rec.status_code := l_Approval_Rules_rec.status_code;
1957 END IF;
1958
1959 --
1960 -- ACTIVE_START_DATE
1961 IF p_Approval_Rules_rec.active_start_date is null or p_Approval_Rules_rec.active_start_date = Fnd_Api.g_miss_date THEN
1962 x_complete_rec.active_start_date := l_Approval_Rules_rec.active_start_date;
1963 END IF;
1964
1965 --
1966 -- APPROVAL_NAME
1967 IF p_Approval_Rules_rec.approval_rule_name is null or p_Approval_Rules_rec.approval_rule_name = Fnd_Api.g_miss_char THEN
1968 x_complete_rec.approval_rule_name := l_Approval_Rules_rec.approval_rule_name;
1969 END IF;
1970
1971 END Complete_Approval_Rules_Rec;
1972
1973 ---------------------------------------------------------
1974 -- Function Compare Columns
1975 -- this procedure will compare that no values have been modified for seeded statuses
1976 -----------------------------------------------------------------
1977 FUNCTION compare_columns(
1978 l_Approval_Rules_rec IN Approval_Rules_Rec_Type
1979 )
1980 RETURN VARCHAR2
1981 IS
1982 l_count NUMBER := 0;
1983
1984 BEGIN
1985 IF G_DEBUG='Y' THEN
1986 Ahl_Debug_Pub.debug( 'START DATE:'||TO_CHAR(l_Approval_Rules_rec.active_start_date,'DD_MON_YYYY'));
1987 Ahl_Debug_Pub.debug( 'END DATE:'||TO_CHAR(l_Approval_Rules_rec.active_end_date,'DD_MON_YYYY'));
1988 END IF;
1989
1990 IF l_Approval_Rules_rec.active_start_date IS NOT NULL THEN
1991 IF l_Approval_Rules_rec.active_end_Date IS NOT NULL THEN
1992
1993 BEGIN
1994 SELECT 1 INTO l_count
1995 FROM Ahl_Approval_Rules_vl
1996 WHERE Approval_Rule_id = l_Approval_Rules_rec.Approval_Rule_id
1997 AND approval_rule_name = l_Approval_Rules_rec.approval_rule_name
1998 AND active_start_date = l_Approval_Rules_rec.active_start_date
1999 AND active_end_date = l_Approval_Rules_rec.active_end_Date
2000 AND status_code = l_Approval_Rules_rec.status_code;
2001 EXCEPTION
2002 WHEN NO_DATA_FOUND THEN
2003 l_count := 0;
2004 END;
2005 ELSE -- for end date
2006 BEGIN
2007 SELECT 1 INTO l_count
2008 FROM Ahl_Approval_Rules_vl
2009 WHERE Approval_Rule_id = l_Approval_Rules_rec.Approval_Rule_id
2010 AND approval_rule_name = l_Approval_Rules_rec.approval_rule_name
2011 AND active_start_date = l_Approval_Rules_rec.active_start_date
2012 AND status_code = l_Approval_Rules_rec.status_code;
2013 EXCEPTION
2014 WHEN NO_DATA_FOUND THEN
2015 l_count := 0;
2016 END;
2017 END IF; -- for end date
2018 ELSE
2019 BEGIN
2020 SELECT 1 INTO l_count
2021 FROM Ahl_Approval_Rules_vl
2022 WHERE Approval_Rule_id = l_Approval_Rules_rec.Approval_Rule_id
2023 AND approval_rule_name = l_Approval_Rules_rec.approval_rule_name
2024 AND status_code = l_Approval_Rules_rec.status_code;
2025 EXCEPTION
2026 WHEN NO_DATA_FOUND THEN
2027 l_count := 0;
2028 END;
2029 END IF;
2030
2031 IF l_count = 0 THEN
2032 RETURN Fnd_Api.g_false;
2033 ELSE
2034 RETURN Fnd_Api.g_true;
2035 END IF;
2036 END compare_columns;
2037
2038 -- Check_Approval_Rules_Req_Items
2039 PROCEDURE Check_Approval_Rules_Req_Items (
2040 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
2041 x_return_status OUT NOCOPY VARCHAR2
2042 )
2043 IS
2044 BEGIN
2045 -- APPROVAL RULE NAME
2046 IF p_Approval_Rules_rec.Approval_Rule_Name IS NULL THEN
2047 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2048 Fnd_Message.set_name ('AHL', 'AHL_APPR_RULE_NAME_MISSING');
2049 Fnd_Msg_Pub.ADD;
2050 END IF;
2051 x_return_status := Fnd_Api.g_ret_sts_error;
2052 RETURN;
2053 END IF;
2054
2055 IF p_Approval_Rules_rec.Approval_Rule_Id is null OR p_Approval_Rules_rec.Approval_Rule_Id = Fnd_Api.g_miss_num THEN
2056
2057 -- APPROVAL OBJECT CODE
2058 IF p_Approval_Rules_rec.Approval_object_code IS NULL THEN
2059 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2060 Fnd_Message.set_name ('AHL', 'AHL_APPR_OBJECT_CODE_MISSING');
2061 Fnd_Msg_Pub.ADD;
2062 END IF;
2063 x_return_status := Fnd_Api.g_ret_sts_error;
2064 RETURN;
2065 END IF;
2066
2067 -- APPLICATION USAGE
2068 IF p_Approval_Rules_rec.application_usg_code IS NULL THEN
2069 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2070 Fnd_Message.set_name ('AHL', 'AHL_APPR_APPUSG_ISNULL');
2071 Fnd_Msg_Pub.ADD;
2072 END IF;
2073 x_return_status := Fnd_Api.g_ret_sts_error;
2074 RETURN;
2075 END IF;
2076
2077 -- TYPE CODE
2078 /*IF p_Approval_Rules_rec.approval_type_code IS NULL THEN
2079 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2080 Fnd_Message.set_name ('AHL', 'AHL_APPR_TYPE_CODE_MISSING');
2081 Fnd_Msg_Pub.ADD;
2082 END IF;
2083 x_return_status := Fnd_Api.g_ret_sts_error;
2084 RETURN;
2085 END IF;
2086 */
2087
2088 END IF;
2089
2090 -- START DATE
2091 IF p_Approval_Rules_rec.active_start_date IS NULL THEN
2092 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2093 Fnd_Message.set_name ('AHL', 'AHL_APPR_ST_DATE_MISSING');
2094 Fnd_Msg_Pub.ADD;
2095 END IF;
2096 x_return_status := Fnd_Api.g_ret_sts_error;
2097 RETURN;
2098 END IF;
2099
2100 /*-- PROIRITY CODE
2101 IF p_Approval_Rules_rec.approval_priority_code IS NULL THEN
2102 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2103 Fnd_Message.set_name ('AHL', 'AHL_APPR_PROIRITY_CODE_MISSING');
2104 Fnd_Msg_Pub.ADD;
2105 END IF;
2106 x_return_status := Fnd_Api.g_ret_sts_error;
2107 RETURN;
2108 END IF;*/
2109
2110 /* -- OPERATING UNIT CODE
2111 IF p_Approval_Rules_rec.OPERATING_UNIT_ID IS NULL THEN
2112 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2113 Fnd_Message.set_name ('AHL', 'AHL_APPR_OP_UNIT_CODE_MISSING');
2114 Fnd_Msg_Pub.ADD;
2115 END IF;
2116 x_return_status := Fnd_Api.g_ret_sts_error;
2117 RETURN;
2118 END IF; */
2119
2120 END Check_Approval_Rules_Req_Items;
2121
2122
2123 -- Check_Approval_Rules_UK_Items
2124 PROCEDURE Check_Approval_Rules_UK_Items (
2125 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
2126 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
2127 x_return_status OUT NOCOPY VARCHAR2
2128 )
2129 IS
2130 l_valid_flag VARCHAR2(1);
2131 l_approval_priority_code VARCHAR2(30);
2132 l_operating_unit_id NUMBER;
2133 l_application_usg_code VARCHAR2(30);
2134 l_operating NUMBER;
2135 l_object VARCHAR2(30);
2136 l_type VARCHAR2(30);
2137
2138 CURSOR c_operating IS
2139 SELECT ORGANIZATION_ID FROM HR_OPERATING_UNITS
2140 WHERE NAME = p_Approval_Rules_rec.OPERATING_NAME;
2141
2142 CURSOR c_object IS
2143 SELECT approval_object_code FROM ahl_approval_rules_b
2144 WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id;
2145
2146 CURSOR c_type IS
2147 SELECT approval_type_code FROM ahl_approval_rules_b
2148 WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id;
2149
2150 CURSOR c_appl_usg IS
2151 SELECT application_usg_code
2152 FROM ahl_approval_rules_b
2153 WHERE approval_rule_id = p_Approval_Rules_rec.approval_rule_id;
2154
2155 BEGIN
2156 x_return_status := Fnd_Api.g_ret_sts_success;
2157 --
2158 -- For Create_Approval_Rules, when ID is passed in, we need to
2159 -- check if this ID is unique.
2160 IF p_validation_mode = Jtf_Plsql_Api.g_create
2161 AND p_Approval_Rules_rec.Approval_Rule_id IS NOT NULL
2162 THEN
2163 IF Ahl_Utility_Pvt.check_uniqueness(
2164 'Ahl_Approval_Rules_v',
2165 'Approval_Rule_Id = ' || p_Approval_Rules_rec.Approval_Rule_id
2166 ) = Fnd_Api.g_false
2167 THEN
2168 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2169 Fnd_Message.set_name ('AHL', 'AHL_APPR_DUPLICATE_ID');
2170 Fnd_Msg_Pub.ADD;
2171 END IF;
2172 x_return_status := Fnd_Api.g_ret_sts_error;
2173 RETURN;
2174 END IF;
2175 END IF;
2176
2177 -- check if NAME is UNIQUE
2178 IF p_validation_mode = Jtf_Plsql_Api.g_create THEN
2179 l_valid_flag := Ahl_Utility_Pvt.Check_Uniqueness (
2180 'Ahl_Approval_Rules_v',
2181 'approval_rule_name = ''' || p_Approval_Rules_rec.approval_rule_name || ''''
2182 -- ''' AND language = ''' || p_Approval_Rules_rec.language || ''''
2183 );
2184 ELSE
2185 l_valid_flag := Ahl_Utility_Pvt.Check_Uniqueness (
2186 'Ahl_Approval_Rules_v',
2187 'approval_rule_name = ''' || p_Approval_Rules_rec.approval_rule_name ||
2188 -- ''' AND language = ''' || p_Approval_Rules_rec.language ||
2189 ''' AND approval_rule_id <> ' || p_Approval_Rules_rec.approval_rule_id
2190 );
2191 END IF;
2192
2193 IF l_valid_flag = Fnd_Api.g_false THEN
2194 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2195 Fnd_Message.set_name ('AHL', 'AHL_APPR_DUPLICATE_NAME');
2196 Fnd_Msg_Pub.ADD;
2197 END IF;
2198 x_return_status := Fnd_Api.g_ret_sts_error;
2199 RETURN;
2200 END IF;
2201
2202 -- Check if Approval_object_code, Approval_proirity_code, Operating_unit_id, Approval_type_code make the UNIQUE KEY combination
2203
2204 l_approval_priority_code := p_Approval_Rules_rec.approval_priority_code;
2205
2206 /* To retrieve approvals operating unit id from operating unit name ---*/
2207 OPEN c_operating;
2208 FETCH c_operating INTO l_operating;
2209 IF c_operating%FOUND THEN
2210 CLOSE c_operating;
2211 l_operating_unit_id := l_operating;
2212 ELSE
2213 CLOSE c_operating;
2214 l_operating_unit_id := NULL;
2215 END IF;
2216
2217 -- Reema:
2218 -- Retrieve the application usage code
2219 -- from fnd_lookups
2220 /* OPEN c_appl_usg;
2221 FETCH c_appl_usg INTO l_application_usg_code;
2222 IF c_appl_usg%NOTFOUND THEN
2223 l_application_usg_code := NULL;
2224 END IF;
2225 CLOSE c_appl_usg;
2226 */
2227 IF p_validation_mode = Jtf_Plsql_Api.g_create THEN
2228 -- For add approval uniqueness validations
2229 IF l_approval_priority_code IS NOT NULL AND l_OPERATING_UNIT_ID IS NOT NULL THEN
2230 l_valid_flag := Check_Rules_Uniqueness (
2231 'Ahl_Approval_Rules_v',
2232 'approval_object_code = ''' || p_Approval_Rules_rec.approval_object_code ||
2233 ''' AND approval_type_code = '''|| p_Approval_Rules_rec.approval_type_code ||
2234 ''' AND approval_priority_code = '''|| p_Approval_Rules_rec.approval_priority_code ||
2235 ''' AND operating_unit_id = ' || l_operating_unit_id ||
2236 ' AND application_usg_code = ''' || p_Approval_Rules_rec.application_usg_code ||
2237 ''' AND active_start_date LIKE ''%' || p_Approval_Rules_rec.active_start_date || '%''', 'N' );
2238 ELSIF l_approval_priority_code IS NOT NULL AND l_OPERATING_UNIT_ID IS NULL THEN
2239 l_valid_flag := Check_Rules_Uniqueness (
2240 'Ahl_Approval_Rules_vl',
2241 'approval_object_code = ''' || p_Approval_Rules_rec.approval_object_code ||
2242 ''' AND approval_type_code = '''|| p_Approval_Rules_rec.approval_type_code ||
2243 ''' AND approval_priority_code = ''' || p_Approval_Rules_rec.approval_priority_code ||
2244 ''' AND application_usg_code = ''' || p_Approval_Rules_rec.application_usg_code ||
2245 ''' AND active_start_date LIKE ''%' || p_Approval_Rules_rec.active_start_date || '%''', 'I' );
2246 ELSIF l_approval_priority_code IS NULL AND l_OPERATING_UNIT_ID IS NOT NULL THEN
2247 l_valid_flag := Check_Rules_Uniqueness (
2248 'Ahl_Approval_Rules_v',
2249 'approval_object_code = ''' || p_Approval_Rules_rec.approval_object_code ||
2250 ''' AND approval_type_code = '''|| p_Approval_Rules_rec.approval_type_code ||
2251 ''' AND operating_unit_id = ' || l_operating_unit_id ||
2252 ' AND application_usg_code = ''' || p_Approval_Rules_rec.application_usg_code ||
2253 ''' AND active_start_date LIKE ''%' || p_Approval_Rules_rec.active_start_date || '%''', 'C' );
2254 ELSE
2255 l_valid_flag := Check_Rules_Uniqueness (
2256 'Ahl_Approval_Rules_v',
2257 'approval_object_code = ''' || p_Approval_Rules_rec.approval_object_code ||
2258 ''' AND approval_type_code = '''|| p_Approval_Rules_rec.approval_type_code ||
2259 ''' AND application_usg_code = ''' || p_Approval_Rules_rec.application_usg_code ||
2260 ''' AND active_start_date LIKE ''%' || p_Approval_Rules_rec.active_start_date || '%''', 'B' );
2261 END IF;
2262
2263 ELSE
2264 -- For edit approval uniqueness validations
2265 OPEN c_object;
2266 FETCH c_object INTO l_object;
2267 CLOSE c_object;
2268
2269 OPEN c_type;
2270 FETCH c_type INTO l_type;
2271 CLOSE c_type;
2272
2273 OPEN c_appl_usg;
2274 FETCH c_appl_usg INTO l_application_usg_code;
2275 CLOSE c_appl_usg;
2276
2277 IF l_approval_priority_code IS NOT NULL AND l_OPERATING_UNIT_ID IS NOT NULL THEN
2278 l_valid_flag := Check_Rules_Uniqueness (
2279 'Ahl_Approval_Rules_v',
2280 'approval_object_code = ''' || l_object ||
2281 ''' AND approval_type_code = '''|| l_type ||
2282 ''' AND approval_priority_code = '''|| p_Approval_Rules_rec.approval_priority_code ||
2283 ''' AND operating_unit_id = ' || l_operating_unit_id ||
2284 ' AND application_usg_code = ''' || l_application_usg_code ||
2285 ''' AND active_start_date LIKE ''%' || p_Approval_Rules_rec.active_start_date ||
2286 '%'' AND approval_rule_id <> ' || p_Approval_Rules_rec.approval_rule_id, 'N'
2287 );
2288 ELSIF l_approval_priority_code IS NOT NULL AND l_OPERATING_UNIT_ID IS NULL THEN
2289 l_valid_flag := Check_Rules_Uniqueness (
2290 'Ahl_Approval_Rules_v',
2291 'approval_object_code = ''' || l_object ||
2292 ''' AND approval_type_code = '''|| l_type ||
2293 ''' AND approval_priority_code = '''|| p_Approval_Rules_rec.approval_priority_code ||
2294 ''' AND application_usg_code = ''' || l_application_usg_code ||
2295 ''' AND active_start_date LIKE ''%' || p_Approval_Rules_rec.active_start_date ||
2296 '%'' AND approval_rule_id <> ' || p_Approval_Rules_rec.approval_rule_id, 'I'
2297 );
2298 ELSIF l_approval_priority_code IS NULL AND l_OPERATING_UNIT_ID IS NOT NULL THEN
2299 l_valid_flag := Check_Rules_Uniqueness (
2300 'Ahl_Approval_Rules_v',
2301 'approval_object_code = ''' || l_object ||
2302 ''' AND approval_type_code = '''|| l_type ||
2303 ''' AND OPERATING_UNIT_ID = ' || l_operating_unit_id ||
2304 ' AND application_usg_code = ''' || l_application_usg_code ||
2305 ''' AND active_start_date LIKE ''%' || p_Approval_Rules_rec.active_start_date ||
2306 '%'' AND approval_rule_id <> ' || p_Approval_Rules_rec.approval_rule_id, 'N'
2307 );
2308 ELSE
2309 l_valid_flag := Check_Rules_Uniqueness (
2310 'Ahl_Approval_Rules_v',
2311 'approval_object_code = ''' || l_object ||
2312 ''' AND approval_type_code = '''|| l_type ||
2313 ''' AND application_usg_code = ''' || l_application_usg_code ||
2314 ''' AND active_start_date LIKE ''%' || p_Approval_Rules_rec.active_start_date ||
2315 '%'' AND approval_rule_id <> ' || p_Approval_Rules_rec.approval_rule_id, 'B'
2316 );
2317 END IF;
2318 END IF;
2319 IF l_valid_flag = Fnd_Api.g_false THEN
2320 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2321 Fnd_Message.set_name ('AHL', 'AHL_APPR_RULE_NOT_UNIQUE');
2322 Fnd_Msg_Pub.ADD;
2323 END IF;
2324 x_return_status := Fnd_Api.g_ret_sts_error;
2325 RETURN;
2326 END IF;
2327
2328 END Check_Approval_Rules_UK_Items;
2329
2330 --------------------------------------------------------------------
2331 ----- Approvers -----
2332 --------------------------------------------------------------------
2333
2334 --------------------------------------------------------------------
2335 -- PROCEDURE
2336 -- Create_Approvers
2337 --
2338 --------------------------------------------------------------------
2339
2340 PROCEDURE Create_Approvers (
2341 p_api_version IN NUMBER,
2342 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
2343 p_commit IN VARCHAR2,
2344 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
2345 x_return_status OUT NOCOPY VARCHAR2,
2346 x_msg_count OUT NOCOPY NUMBER,
2347 x_msg_data OUT NOCOPY VARCHAR2,
2348
2349 p_Approvers_rec IN Approvers_Rec_Type,
2350 x_Approval_Approver_Id OUT NOCOPY NUMBER
2351 )
2352 IS
2353 L_API_VERSION CONSTANT NUMBER := 1.0;
2354 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Approvers';
2355 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2356 l_Approvers_rec Approvers_Rec_Type := p_Approvers_rec;
2357 l_dummy NUMBER;
2358 l_approver_id NUMBER;
2359 l_return_status VARCHAR2(1);
2360 l_rowid VARCHAR2(30);
2361 l_object_version_number NUMBER := 1;
2362
2363 CURSOR c_seq IS
2364 SELECT Ahl_Approvers_S.NEXTVAL
2365 FROM dual;
2366
2367 CURSOR c_id_exists (x_id IN NUMBER) IS
2368 SELECT 1
2369 FROM dual
2370 WHERE EXISTS (SELECT 1
2371 FROM AHL_Approvers
2372 WHERE Approval_Approver_Id = x_id);
2373 BEGIN
2374 --------------------- initialize -----------------------
2375 SAVEPOINT Create_Approvers;
2376 -- Check if API is called in debug mode. If yes, enable debug.
2377 IF G_DEBUG='Y' THEN
2378 Ahl_Debug_Pub.enable_debug;
2379 END IF;
2380 -- Debug info.
2381 IF G_DEBUG='Y' THEN
2382 Ahl_Debug_Pub.debug( l_full_name ||':Start');
2383 END IF;
2384
2385 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
2386 Fnd_Msg_Pub.initialize;
2387 END IF;
2388 IF NOT Fnd_Api.compatible_api_call (
2389 L_API_VERSION,
2390 p_api_version,
2391 L_API_NAME,
2392 G_PKG_NAME
2393 ) THEN
2394 RAISE Fnd_Api.g_exc_unexpected_error;
2395 END IF;
2396 x_return_status := Fnd_Api.g_ret_sts_success;
2397 ----------------------- validate -----------------------
2398 IF G_DEBUG='Y' THEN
2399 Ahl_Debug_Pub.debug( l_full_name ||':Validate');
2400 END IF;
2401
2402 Validate_Approvers (
2403 p_api_version => l_api_version,
2404 p_init_msg_list => p_init_msg_list,
2405 p_commit => p_commit,
2406 p_validation_level => p_validation_level,
2407 x_return_status => l_return_status,
2408 x_msg_count => x_msg_count,
2409 x_msg_data => x_msg_data,
2410 p_Approvers_rec => l_Approvers_rec
2411 );
2412 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
2413 RAISE Fnd_Api.g_exc_error;
2414 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2415 RAISE Fnd_Api.g_exc_unexpected_error;
2416 END IF;
2417
2418 IF l_Approvers_rec.APPROVER_ID IS NULL THEN
2419 Check_Approver_Role (
2420 p_Approvers_rec => p_Approvers_rec,
2421 p_complete_rec => l_Approvers_rec,
2422 x_return_status => l_return_status
2423 );
2424 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2425 RAISE Fnd_Api.g_exc_unexpected_error;
2426 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
2427 RAISE Fnd_Api.g_exc_error;
2428 END IF;
2429 END IF;
2430
2431 Check_Approver_Name_Or_Id(p_approvers_rec => p_approvers_rec,
2432 x_approver_id => l_approver_id,
2433 x_return_status => l_return_status
2434 );
2435 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2436 RAISE Fnd_Api.g_exc_unexpected_error;
2437 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
2438 RAISE Fnd_Api.g_exc_error;
2439 END IF;
2440
2441 l_approvers_rec.approver_id := l_approver_id;
2442
2443
2444 IF l_Approvers_rec.APPROVER_SEQUENCE IS NOT NULL THEN
2445 Check_Approver_Sequence (
2446 p_Approvers_rec => p_Approvers_rec,
2447 p_complete_rec => l_Approvers_rec,
2448 x_return_status => l_return_status
2449 );
2450 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2451 RAISE Fnd_Api.g_exc_unexpected_error;
2452 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
2453 RAISE Fnd_Api.g_exc_error;
2454 END IF;
2455 END IF;
2456
2457 --
2458 -- Check for the ID.
2459 --
2460 IF l_Approvers_rec.Approval_Approver_Id IS NULL or l_Approvers_rec.Approval_Approver_Id = Fnd_Api.g_miss_num THEN
2461 LOOP
2462 --
2463 -- If the ID is not passed into the API, then
2464 -- grab a value from the sequence.
2465 OPEN c_seq;
2466 FETCH c_seq INTO l_Approvers_rec.Approval_Approver_Id;
2467 CLOSE c_seq;
2468 --
2469 -- Check to be sure that the sequence does not exist.
2470 OPEN c_id_exists (l_Approvers_rec.Approval_Approver_Id);
2471 FETCH c_id_exists INTO l_dummy;
2472 CLOSE c_id_exists;
2473 --
2474 -- If the value for the ID already exists, then
2475 -- l_dummy would be populated with '1', otherwise,
2476 -- it receives NULL.
2477 EXIT WHEN l_dummy IS NULL;
2478 END LOOP;
2479 END IF;
2480
2481 -------------------------- insert --------------------------
2482 IF G_DEBUG='Y' THEN
2483 Ahl_Debug_Pub.debug( l_full_name ||':Insert');
2484 END IF;
2485 -- Invoke the table handler to create a record
2486 --
2487 Ahl_Approvers_Pkg.Insert_Row (
2488 X_ROWID => l_rowid,
2489 X_APPROVAL_APPROVER_ID => l_Approvers_rec.APPROVAL_APPROVER_ID,
2490 X_OBJECT_VERSION_NUMBER => 1,
2491 X_APPROVAL_RULE_ID => l_Approvers_rec.APPROVAL_RULE_ID,
2492 X_APPROVER_TYPE_CODE => l_Approvers_rec.APPROVER_TYPE_CODE,
2493 X_APPROVER_SEQUENCE => l_Approvers_rec.APPROVER_SEQUENCE,
2494 X_APPROVER_ID => l_Approvers_rec.APPROVER_ID,
2495 X_LAST_UPDATE_DATE => SYSDATE,
2496 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
2497 X_CREATION_DATE => SYSDATE,
2498 X_CREATED_BY => Fnd_Global.USER_ID,
2499 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
2500 X_ATTRIBUTE_CATEGORY => l_Approvers_rec.ATTRIBUTE_CATEGORY,
2501 X_ATTRIBUTE1 => l_Approvers_rec.ATTRIBUTE1,
2502 X_ATTRIBUTE2 => l_Approvers_rec.ATTRIBUTE2,
2503 X_ATTRIBUTE3 => l_Approvers_rec.ATTRIBUTE3,
2504 X_ATTRIBUTE4 => l_Approvers_rec.ATTRIBUTE4,
2505 X_ATTRIBUTE5 => l_Approvers_rec.ATTRIBUTE5,
2506 X_ATTRIBUTE6 => l_Approvers_rec.ATTRIBUTE6,
2507 X_ATTRIBUTE7 => l_Approvers_rec.ATTRIBUTE7,
2508 X_ATTRIBUTE8 => l_Approvers_rec.ATTRIBUTE8,
2509 X_ATTRIBUTE9 => l_Approvers_rec.ATTRIBUTE9,
2510 X_ATTRIBUTE10 => l_Approvers_rec.ATTRIBUTE10,
2511 X_ATTRIBUTE11 => l_Approvers_rec.ATTRIBUTE11,
2512 X_ATTRIBUTE12 => l_Approvers_rec.ATTRIBUTE12,
2513 X_ATTRIBUTE13 => l_Approvers_rec.ATTRIBUTE13,
2514 X_ATTRIBUTE14 => l_Approvers_rec.ATTRIBUTE14,
2515 X_ATTRIBUTE15 => l_Approvers_rec.ATTRIBUTE15 );
2516
2517 ------------------------- finish -------------------------------
2518
2519 -- set OUT value
2520 x_Approval_Approver_Id := l_Approvers_rec.APPROVAL_APPROVER_ID;
2521 --
2522 -- END of API body.
2523 --
2524 -- Standard check of p_commit.
2525 IF Fnd_Api.To_Boolean ( p_commit ) THEN
2526 COMMIT WORK;
2527 END IF;
2528 Fnd_Msg_Pub.count_and_get(
2529 p_encoded => Fnd_Api.g_false,
2530 p_count => x_msg_count,
2531 p_data => x_msg_data
2532 );
2533 IF G_DEBUG='Y' THEN
2534 Ahl_Debug_Pub.debug( l_full_name ||':End');
2535 END IF;
2536 -- Check if API is called in debug mode. If yes, disable debug.
2537 IF G_DEBUG='Y' THEN
2538 Ahl_Debug_Pub.disable_debug;
2539 END IF;
2540
2541 EXCEPTION
2542 WHEN Fnd_Api.g_exc_error THEN
2543 ROLLBACK TO Create_Approvers;
2544 x_return_status := Fnd_Api.g_ret_sts_error;
2545 Fnd_Msg_Pub.count_and_get(
2546 p_encoded => Fnd_Api.g_false,
2547 p_count => x_msg_count,
2548 p_data => x_msg_data
2549 );
2550 WHEN Fnd_Api.g_exc_unexpected_error THEN
2551 ROLLBACK TO Create_Approvers;
2552 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2553 Fnd_Msg_Pub.count_and_get (
2554 p_encoded => Fnd_Api.g_false,
2555 p_count => x_msg_count,
2556 p_data => x_msg_data
2557 );
2558 WHEN OTHERS THEN
2559 ROLLBACK TO Create_Approvers;
2560 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2561 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error) THEN
2562 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
2563 END IF;
2564 Fnd_Msg_Pub.count_and_get (
2565 p_encoded => Fnd_Api.g_false,
2566 p_count => x_msg_count,
2567 p_data => x_msg_data
2568 );
2569 END Create_Approvers;
2570
2571 --------------------------------------------------------------------
2572 -- PROCEDURE
2573 -- Update_Approvers
2574 --
2575 --------------------------------------------------------------------
2576 PROCEDURE Update_Approvers (
2577 p_api_version IN NUMBER,
2578 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
2579 p_commit IN VARCHAR2,
2580 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
2581 p_Approvers_rec IN Approvers_Rec_Type,
2582
2583 x_return_status OUT NOCOPY VARCHAR2,
2584 x_msg_count OUT NOCOPY NUMBER,
2585 x_msg_data OUT NOCOPY VARCHAR2
2586
2587 )
2588 IS
2589 L_API_VERSION CONSTANT NUMBER := 1.0;
2590 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Approvers';
2591 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2592
2593 l_Approvers_rec Approvers_Rec_Type := p_Approvers_rec;
2594 l_dummy NUMBER;
2595 l_return_status VARCHAR2(1);
2596 BEGIN
2597 --------------------- initialize -----------------------
2598 SAVEPOINT Update_Approvers;
2599
2600 -- Check if API is called in debug mode. If yes, enable debug.
2601 IF G_DEBUG='Y' THEN
2602 Ahl_Debug_Pub.enable_debug;
2603 END IF;
2604 -- Debug info.
2605 IF G_DEBUG='Y' THEN
2606 Ahl_Debug_Pub.debug( l_full_name ||':Start');
2607 END IF;
2608 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
2609 Fnd_Msg_Pub.initialize;
2610 END IF;
2611 IF NOT Fnd_Api.compatible_api_call(
2612 l_api_version,
2613 p_api_version,
2614 l_api_name,
2615 G_PKG_NAME
2616 ) THEN
2617 RAISE Fnd_Api.g_exc_unexpected_error;
2618 END IF;
2619 x_return_status := Fnd_Api.g_ret_sts_success;
2620 ----------------------- validate ----------------------
2621 IF G_DEBUG='Y' THEN
2622 Ahl_Debug_Pub.debug( l_full_name ||':Validate');
2623 END IF;
2624 -- replace g_miss_char/num/date with current column values
2625 Complete_Approvers_Rec (p_Approvers_rec, l_Approvers_rec);
2626 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
2627 Check_Approvers_Items (
2628 p_validation_mode => Jtf_Plsql_Api.g_update,
2629 p_Approvers_rec => p_Approvers_rec,
2630 x_return_status => l_return_status
2631 );
2632 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2633 RAISE Fnd_Api.g_exc_unexpected_error;
2634 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
2635 RAISE Fnd_Api.g_exc_error;
2636 END IF;
2637 END IF;
2638 IF l_Approvers_rec.APPROVER_ID IS NULL THEN
2639 Check_Approver_Role (
2640 p_Approvers_rec => p_Approvers_rec,
2641 p_complete_rec => l_Approvers_rec,
2642 x_return_status => l_return_status
2643 );
2644 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2645 RAISE Fnd_Api.g_exc_unexpected_error;
2646 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
2647 RAISE Fnd_Api.g_exc_error;
2648 END IF;
2649 END IF;
2650
2651 IF l_Approvers_rec.APPROVER_SEQUENCE IS NOT NULL THEN
2652 Check_Approver_Sequence (
2653 p_Approvers_rec => p_Approvers_rec,
2654 p_complete_rec => l_Approvers_rec,
2655 x_return_status => l_return_status
2656 );
2657 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2658 RAISE Fnd_Api.g_exc_unexpected_error;
2659 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
2660 RAISE Fnd_Api.g_exc_error;
2661 END IF;
2662 END IF;
2663
2664 ------------------------- update --------------------
2665 IF G_DEBUG='Y' THEN
2666 Ahl_Debug_Pub.debug( l_full_name ||':Update');
2667 END IF;
2668
2669 Ahl_Approvers_Pkg. UPDATE_ROW (
2670 X_APPROVAL_APPROVER_ID => l_Approvers_rec.APPROVAL_APPROVER_ID,
2671 X_OBJECT_VERSION_NUMBER => l_Approvers_rec.OBJECT_VERSION_NUMBER + 1,
2672 X_APPROVAL_RULE_ID => l_Approvers_rec.APPROVAL_RULE_ID,
2673 X_APPROVER_TYPE_CODE => l_Approvers_rec.APPROVER_TYPE_CODE,
2674 X_APPROVER_SEQUENCE => l_Approvers_rec.APPROVER_SEQUENCE,
2675 X_APPROVER_ID => l_Approvers_rec.APPROVER_ID,
2676 X_LAST_UPDATE_DATE => SYSDATE,
2677 X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
2678 X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID,
2679 X_ATTRIBUTE_CATEGORY => l_Approvers_rec.ATTRIBUTE_CATEGORY,
2680 X_ATTRIBUTE1 => l_Approvers_rec.ATTRIBUTE1,
2681 X_ATTRIBUTE2 => l_Approvers_rec.ATTRIBUTE2,
2682 X_ATTRIBUTE3 => l_Approvers_rec.ATTRIBUTE3,
2683 X_ATTRIBUTE4 => l_Approvers_rec.ATTRIBUTE4,
2684 X_ATTRIBUTE5 => l_Approvers_rec.ATTRIBUTE5,
2685 X_ATTRIBUTE6 => l_Approvers_rec.ATTRIBUTE6,
2686 X_ATTRIBUTE7 => l_Approvers_rec.ATTRIBUTE7,
2687 X_ATTRIBUTE8 => l_Approvers_rec.ATTRIBUTE8,
2688 X_ATTRIBUTE9 => l_Approvers_rec.ATTRIBUTE9,
2689 X_ATTRIBUTE10 => l_Approvers_rec.ATTRIBUTE10,
2690 X_ATTRIBUTE11 => l_Approvers_rec.ATTRIBUTE11,
2691 X_ATTRIBUTE12 => l_Approvers_rec.ATTRIBUTE12,
2692 X_ATTRIBUTE13 => l_Approvers_rec.ATTRIBUTE13,
2693 X_ATTRIBUTE14 => l_Approvers_rec.ATTRIBUTE14,
2694 X_ATTRIBUTE15 => l_Approvers_rec.ATTRIBUTE15 );
2695
2696 -------------------- finish --------------------------
2697 -- dbms_output.put_line('test7');
2698 IF Fnd_Api.to_boolean (p_commit) THEN
2699 COMMIT;
2700 END IF;
2701 Fnd_Msg_Pub.count_and_get (
2702 p_encoded => Fnd_Api.g_false,
2703 p_count => x_msg_count,
2704 p_data => x_msg_data
2705 );
2706 IF G_DEBUG='Y' THEN
2707 Ahl_Debug_Pub.debug( l_full_name ||':End');
2708 END IF;
2709 -- Check if API is called in debug mode. If yes, disable debug.
2710 IF G_DEBUG='Y' THEN
2711 Ahl_Debug_Pub.disable_debug;
2712 END IF;
2713 EXCEPTION
2714 WHEN Fnd_Api.g_exc_error THEN
2715 ROLLBACK TO Update_Approvers;
2716 x_return_status := Fnd_Api.g_ret_sts_error;
2717 Fnd_Msg_Pub.count_and_get (
2718 p_encoded => Fnd_Api.g_false,
2719 p_count => x_msg_count,
2720 p_data => x_msg_data
2721 );
2722 WHEN Fnd_Api.g_exc_unexpected_error THEN
2723 ROLLBACK TO Update_Approvers;
2724 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2725 Fnd_Msg_Pub.count_and_get (
2726 p_encoded => Fnd_Api.g_false,
2727 p_count => x_msg_count,
2728 p_data => x_msg_data
2729 );
2730 WHEN OTHERS THEN
2731 ROLLBACK TO Update_Approvers;
2732 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2733 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
2734 THEN
2735 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
2736 END IF;
2737 Fnd_Msg_Pub.count_and_get (
2738 p_encoded => Fnd_Api.g_false,
2739 p_count => x_msg_count,
2740 p_data => x_msg_data
2741 );
2742 END Update_Approvers;
2743
2744 --------------------------------------------------------------------
2745 -- PROCEDURE
2746 -- Check_Approver_Sequence
2747 --
2748 --------------------------------------------------------------------
2749
2750 PROCEDURE Check_Approver_Sequence (
2751 p_Approvers_rec IN Approvers_Rec_Type,
2752 p_complete_rec IN Approvers_Rec_Type := NULL,
2753 x_return_status OUT NOCOPY VARCHAR2
2754 )
2755 IS
2756
2757 BEGIN
2758 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2759 IF p_Approvers_rec.approver_sequence IS NOT NULL THEN
2760 IF p_Approvers_rec.approver_sequence = 0 OR p_Approvers_rec.approver_sequence < 0 THEN
2761 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2762 Fnd_Message.set_name ('AHL', 'AHL_APPR_ORDER_INVALID_JSP');
2763 Fnd_Msg_Pub.ADD;
2764 END IF;
2765 x_return_status := Fnd_Api.g_ret_sts_error;
2766 RETURN;
2767 END IF;
2768 END IF;
2769 END Check_Approver_Sequence;
2770
2771 --------------------------------------------------------------------
2772 -- PROCEDURE
2773 -- Check_Approver_Role
2774 --
2775 --------------------------------------------------------------------
2776
2777 PROCEDURE Check_Approver_Role (
2778 p_Approvers_rec IN Approvers_Rec_Type,
2779 p_complete_rec IN Approvers_Rec_Type := NULL,
2780 x_return_status OUT NOCOPY VARCHAR2
2781 )
2782 IS
2783 BEGIN
2784 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2785 IF p_Approvers_rec.APPROVER_NAME IS NULL THEN
2786 IF UPPER(p_Approvers_rec.approver_type_code) = 'USER' THEN
2787 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2788 Fnd_Message.set_name ('AHL', 'AHL_APPR_TYPE_NOT_USER');
2789 Fnd_Msg_Pub.ADD;
2790 END IF;
2791 x_return_status := Fnd_Api.g_ret_sts_error;
2792 RETURN;
2793 END IF;
2794 END IF;
2795 END Check_Approver_Role;
2796
2797 --------------------------------------------------------------------
2798 -- PROCEDURE
2799 -- Delete_Approvers
2800 --
2801 --------------------------------------------------------------------
2802 PROCEDURE Delete_Approvers (
2803 p_api_version IN NUMBER,
2804 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
2805 p_commit IN VARCHAR2,
2806 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
2807 p_Approval_Approver_Id IN NUMBER,
2808 p_object_version IN NUMBER,
2809 x_return_status OUT NOCOPY VARCHAR2,
2810 x_msg_count OUT NOCOPY NUMBER,
2811 x_msg_data OUT NOCOPY VARCHAR2
2812 )
2813 IS
2814 CURSOR c_Approvers IS
2815 SELECT *
2816 FROM AHL_Approvers
2817 WHERE APPROVAL_APPROVER_ID = p_Approval_Approver_Id;
2818
2819 /* Start Code for checking approvals by shbhanda Mar'08---*/
2820 CURSOR check_approval_rules IS
2821 SELECT COUNT(*) FROM Ahl_Approvers
2822 WHERE Approval_Rule_Id IN
2823 (SELECT Approval_Rule_Id FROM Ahl_Approvers WHERE APPROVAL_APPROVER_ID = p_Approval_Approver_Id);
2824
2825 CURSOR check_appr_status IS
2826 SELECT status_code FROM Ahl_Approval_rules_b
2827 WHERE Approval_Rule_Id IN
2828 (SELECT Approval_Rule_Id FROM Ahl_Approvers WHERE APPROVAL_APPROVER_ID = p_Approval_Approver_Id);
2829
2830 /* End Code for checking approvals by shbhanda Mar'08---*/
2831
2832 --
2833 -- This is the only exception for using %ROWTYPE.
2834 -- We are selecting from the VL view, which may
2835 -- have some denormalized columns as compared to
2836 -- the base tables.
2837 l_Approvers_rec c_Approvers%ROWTYPE;
2838 l_api_version CONSTANT NUMBER := 1.0;
2839 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Approvers';
2840 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2841 l_count NUMBER;
2842 l_status VARCHAR2(30);
2843 BEGIN
2844 --------------------- initialize -----------------------
2845 SAVEPOINT Delete_Approvers;
2846 -- Check if API is called in debug mode. If yes, enable debug.
2847 IF G_DEBUG='Y' THEN
2848 Ahl_Debug_Pub.enable_debug;
2849 END IF;
2850 -- Debug info.
2851 IF G_DEBUG='Y' THEN
2852 Ahl_Debug_Pub.debug( l_full_name ||':Start');
2853 END IF;
2854 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
2855 Fnd_Msg_Pub.initialize;
2856 END IF;
2857 IF NOT Fnd_Api.compatible_api_call (
2858 l_api_version,
2859 p_api_version,
2860 l_api_name,
2861 G_PKG_NAME
2862 ) THEN
2863 RAISE Fnd_Api.g_exc_unexpected_error;
2864 END IF;
2865 x_return_status := Fnd_Api.g_ret_sts_success;
2866
2867 OPEN c_Approvers;
2868 FETCH c_Approvers INTO l_Approvers_rec;
2869 IF c_Approvers%NOTFOUND THEN
2870 CLOSE c_Approvers;
2871 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
2872 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
2873 Fnd_Msg_Pub.ADD;
2874 END IF;
2875 RAISE Fnd_Api.g_exc_error;
2876 END IF;
2877 CLOSE c_Approvers;
2878
2879 /* Start Code for checking approvals by shbhanda Mar'08---*/
2880 OPEN check_appr_status;
2881 FETCH check_appr_status INTO l_status;
2882 IF check_appr_status%FOUND THEN
2883 CLOSE check_appr_status;
2884 OPEN check_approval_rules;
2885 FETCH check_approval_rules INTO l_count;
2886 IF check_approval_rules%FOUND THEN
2887 IF l_count > 1 THEN
2888 CLOSE check_approval_rules;
2889 ELSE
2890 CLOSE check_approval_rules;
2891 IF UPPER(l_status) = 'ACTIVE' THEN
2892 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
2893 Fnd_Message.set_name('AHL', 'AHL_APPR_NOT_DELETE');
2894 Fnd_Msg_Pub.ADD;
2895 END IF;
2896 RAISE Fnd_Api.g_exc_error;
2897 END IF;
2898 END IF;
2899 END IF;
2900 ELSE
2901 CLOSE check_appr_status;
2902 END IF;
2903 /* End Code for checking approvals by shbhanda Mar'08---*/
2904
2905 ------------------------ delete ------------------------
2906 IF G_DEBUG='Y' THEN
2907 Ahl_Debug_Pub.debug( l_full_name ||':Delete');
2908 END IF;
2909 -- Delete data
2910
2911 DELETE FROM Ahl_Approvers
2912 WHERE APPROVAL_APPROVER_ID = p_Approval_Approver_Id;
2913 IF (SQL%NOTFOUND) THEN
2914 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2915 Fnd_Message.set_name ('AHL', 'AHL_API_RECORD_NOT_FOUND');
2916 Fnd_Msg_Pub.ADD;
2917 END IF;
2918 RAISE Fnd_Api.g_exc_error;
2919 END IF;
2920
2921
2922 -------------------- finish --------------------------
2923 IF Fnd_Api.to_boolean (p_commit) THEN
2924 COMMIT;
2925 END IF;
2926 Fnd_Msg_Pub.count_and_get (
2927 p_encoded => Fnd_Api.g_false,
2928 p_count => x_msg_count,
2929 p_data => x_msg_data
2930 );
2931 IF G_DEBUG='Y' THEN
2932 Ahl_Debug_Pub.debug( l_full_name ||':End');
2933 END IF;
2934 -- Check if API is called in debug mode. If yes, disable debug.
2935 IF G_DEBUG='Y' THEN
2936 Ahl_Debug_Pub.disable_debug;
2937 END IF;
2938 EXCEPTION
2939 WHEN Fnd_Api.g_exc_error THEN
2940 ROLLBACK TO Delete_Approvers;
2941 x_return_status := Fnd_Api.g_ret_sts_error;
2942 Fnd_Msg_Pub.count_and_get (
2943 p_encoded => Fnd_Api.g_false,
2944 p_count => x_msg_count,
2945 p_data => x_msg_data
2946 );
2947 WHEN Fnd_Api.g_exc_unexpected_error THEN
2948 ROLLBACK TO Delete_Approvers;
2949 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2950 Fnd_Msg_Pub.count_and_get (
2951 p_encoded => Fnd_Api.g_false,
2952 p_count => x_msg_count,
2953 p_data => x_msg_data
2954 );
2955 WHEN OTHERS THEN
2956 ROLLBACK TO Delete_Approvers;
2957 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2958 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
2959 THEN
2960 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
2961 END IF;
2962 Fnd_Msg_Pub.count_and_get (
2963 p_encoded => Fnd_Api.g_false,
2964 p_count => x_msg_count,
2965 p_data => x_msg_data
2966 );
2967
2968 END Delete_Approvers;
2969 --------------------------------------------------------------------
2970 -- PROCEDURE
2971 -- Validate_Approvers
2972 --
2973 --------------------------------------------------------------------
2974 PROCEDURE Validate_Approvers (
2975
2976 p_api_version IN NUMBER,
2977 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
2978 p_commit IN VARCHAR2,
2979 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
2980
2981 x_return_status OUT NOCOPY VARCHAR2,
2982 x_msg_count OUT NOCOPY NUMBER,
2983 x_msg_data OUT NOCOPY VARCHAR2,
2984 p_Approvers_rec IN Approvers_Rec_Type
2985 )
2986 IS
2987 L_API_VERSION CONSTANT NUMBER := 1.0;
2988 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Approvers';
2989 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2990 l_return_status VARCHAR2(1);
2991 BEGIN
2992 --------------------- initialize -----------------------
2993 -- Check if API is called in debug mode. If yes, enable debug.
2994 IF G_DEBUG='Y' THEN
2995 Ahl_Debug_Pub.enable_debug;
2996 END IF;
2997 -- Debug info.
2998 IF G_DEBUG='Y' THEN
2999 Ahl_Debug_Pub.debug( l_full_name ||':Start');
3000 END IF;
3001 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
3002 Fnd_Msg_Pub.initialize;
3003 END IF;
3004 IF NOT Fnd_Api.compatible_api_call (
3005 l_api_version,
3006 p_api_version,
3007 l_api_name,
3008 G_PKG_NAME
3009 ) THEN
3010 RAISE Fnd_Api.g_exc_unexpected_error;
3011 END IF;
3012 x_return_status := Fnd_Api.g_ret_sts_success;
3013 ---------------------- validate ------------------------
3014 IF G_DEBUG='Y' THEN
3015 Ahl_Debug_Pub.debug( l_full_name ||':Check items');
3016 END IF;
3017
3018 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
3019 Check_Approvers_Items (
3020 p_validation_mode => Jtf_Plsql_Api.g_create,
3021 p_Approvers_rec => p_Approvers_rec,
3022 x_return_status => l_return_status
3023 );
3024 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3025 RAISE Fnd_Api.g_exc_unexpected_error;
3026 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
3027 RAISE Fnd_Api.g_exc_error;
3028 END IF;
3029 END IF;
3030 IF G_DEBUG='Y' THEN
3031 Ahl_Debug_Pub.debug( l_full_name ||':Check record');
3032 END IF;
3033
3034 -------------------- finish --------------------------
3035 Fnd_Msg_Pub.count_and_get (
3036 p_encoded => Fnd_Api.g_false,
3037 p_count => x_msg_count,
3038 p_data => x_msg_data
3039 );
3040 IF G_DEBUG='Y' THEN
3041 Ahl_Debug_Pub.debug( l_full_name ||':End');
3042 END IF;
3043 -- Check if API is called in debug mode. If yes, disable debug.
3044 IF G_DEBUG='Y' THEN
3045 Ahl_Debug_Pub.disable_debug;
3046 END IF;
3047 EXCEPTION
3048 WHEN Fnd_Api.g_exc_error THEN
3049 x_return_status := Fnd_Api.g_ret_sts_error;
3050 Fnd_Msg_Pub.count_and_get (
3051 p_encoded => Fnd_Api.g_false,
3052 p_count => x_msg_count,
3053 p_data => x_msg_data
3054 );
3055 WHEN Fnd_Api.g_exc_unexpected_error THEN
3056 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
3057 Fnd_Msg_Pub.count_and_get (
3058 p_encoded => Fnd_Api.g_false,
3059 p_count => x_msg_count,
3060 p_data => x_msg_data
3061 );
3062 WHEN OTHERS THEN
3063 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3064 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
3065 THEN
3066 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
3067 END IF;
3068 Fnd_Msg_Pub.count_and_get (
3069 p_encoded => Fnd_Api.g_false,
3070 p_count => x_msg_count,
3071 p_data => x_msg_data
3072 );
3073 END Validate_Approvers;
3074
3075 ---------------------------------------------------------------------
3076 -- PROCEDURE
3077 -- Check_Approvers_Items
3078 --
3079 ---------------------------------------------------------------------
3080 PROCEDURE Check_Approvers_Items (
3081 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
3082 p_Approvers_rec IN Approvers_Rec_Type,
3083 x_return_status OUT NOCOPY VARCHAR2
3084 )
3085 IS
3086 BEGIN
3087 --
3088 -- Validate required items.
3089 Check_Approvers_Req_Items (
3090 p_Approvers_rec => p_Approvers_rec,
3091 x_return_status => x_return_status
3092 );
3093 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
3094 RETURN;
3095 END IF;
3096 --
3097 -- Validate uniqueness.
3098 Check_Approvers_UK_Items (
3099 p_Approvers_rec => p_Approvers_rec,
3100 p_validation_mode => p_validation_mode,
3101 x_return_status => x_return_status
3102 );
3103 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
3104 RETURN;
3105 END IF;
3106
3107 END Check_Approvers_Items;
3108
3109 ---------------------------------------------------------------------
3110 -- PROCEDURE
3111 -- Init_Approvers_Rec
3112 --
3113 ---------------------------------------------------------------------
3114 /*PROCEDURE Init_Approvers_Rec (
3115 x_Approvers_rec OUT NOCOPY Approvers_Rec_Type
3116 )
3117 IS
3118 BEGIN
3119 x_Approvers_rec.APPROVAL_APPROVER_ID := Fnd_Api.g_miss_num;
3120 x_Approvers_rec.OBJECT_VERSION_NUMBER := Fnd_Api.g_miss_num;
3121 x_Approvers_rec.APPROVAL_RULE_ID := Fnd_Api.g_miss_num;
3122 x_Approvers_rec.APPROVER_TYPE_CODE := Fnd_Api.g_miss_num;
3123 x_Approvers_rec.APPROVER_SEQUENCE := Fnd_Api.g_miss_char;
3124 x_Approvers_rec.APPROVER_ID := Fnd_Api.g_miss_num;
3125 END Init_Approvers_Rec;
3126 */
3127 ---------------------------------------------------------------------
3128 -- PROCEDURE
3129 -- Complete_Approvers_Rec
3130 --
3131 ---------------------------------------------------------------------
3132 PROCEDURE Complete_Approvers_Rec (
3133 p_Approvers_rec IN Approvers_Rec_Type,
3134 x_complete_rec OUT NOCOPY Approvers_Rec_Type
3135 )
3136 IS
3137 CURSOR c_Approvers IS
3138 SELECT *
3139 FROM AHL_Approvers
3140 WHERE APPROVAL_APPROVER_ID = p_Approvers_rec.Approval_Approver_Id;
3141 --
3142 -- This is the only exception for using %ROWTYPE.
3143 -- We are selecting from the V view, which may
3144 -- have some denormalized columns as compared to
3145 -- the base tables.
3146 l_Approvers_rec c_Approvers%ROWTYPE;
3147 BEGIN
3148 x_complete_rec := p_Approvers_rec;
3149 OPEN c_Approvers;
3150 FETCH c_Approvers INTO l_Approvers_rec;
3151 IF c_Approvers%NOTFOUND THEN
3152 CLOSE c_Approvers;
3153 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.g_msg_lvl_error) THEN
3154 Fnd_Message.set_name('AHL', 'AHL_API_RECORD_NOT_FOUND');
3155 Fnd_Msg_Pub.ADD;
3156 END IF;
3157 RAISE Fnd_Api.g_exc_error;
3158 END IF;
3159 CLOSE c_Approvers;
3160
3161 --
3162 -- APPROVAL_SEQUENCE
3163 IF p_Approvers_rec.Approver_sequence is null or p_Approvers_rec.Approver_sequence = Fnd_Api.g_miss_num THEN
3164 x_complete_rec.Approver_sequence := l_Approvers_rec.Approver_sequence;
3165 END IF;
3166
3167 --
3168 -- APPROVER_ID
3169 IF p_Approvers_rec.Approver_Id is null or p_Approvers_rec.Approver_Id = Fnd_Api.g_miss_num THEN
3170 x_complete_rec.Approver_Id := l_Approvers_rec.Approver_Id;
3171 END IF;
3172
3173 --
3174 -- APPROVAL_RULE_ID
3175 IF p_Approvers_rec.APPROVAL_RULE_ID is null or p_Approvers_rec.APPROVAL_RULE_ID = Fnd_Api.g_miss_num THEN
3176 x_complete_rec.APPROVAL_RULE_ID := l_Approvers_rec.APPROVAL_RULE_ID;
3177 END IF;
3178
3179 --
3180 -- APPROVAL_TYPE_CODE
3181 IF p_Approvers_rec.APPROVER_TYPE_CODE is null or p_Approvers_rec.APPROVER_TYPE_CODE = Fnd_Api.g_miss_char THEN
3182 x_complete_rec.APPROVER_TYPE_CODE := l_Approvers_rec.APPROVER_TYPE_CODE;
3183 END IF;
3184
3185 END Complete_Approvers_Rec;
3186
3187 -- Check_Approvers_Req_Items
3188 PROCEDURE Check_Approvers_Req_Items (
3189 p_Approvers_rec IN Approvers_Rec_Type,
3190 x_return_status OUT NOCOPY VARCHAR2
3191 )
3192 IS
3193 BEGIN
3194 -- APPROVAL SEQUENCE
3195 IF p_Approvers_rec.Approver_sequence IS NULL THEN
3196 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
3197 Fnd_Message.set_name ('AHL', 'AHL_APPR_SEQUENCE_MISSING');
3198 Fnd_Msg_Pub.ADD;
3199 END IF;
3200 x_return_status := Fnd_Api.g_ret_sts_error;
3201 RETURN;
3202 END IF;
3203
3204 -- APPROVER_NAME - Approver Name is mandatory only if the approver type is user
3205 -- if approver name is null and the approval type is role, then the default approver is picked up
3206 IF UPPER(p_Approvers_rec.APPROVER_TYPE_CODE) = 'USER' AND p_Approvers_rec.APPROVER_NAME IS NULL THEN
3207 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
3208 Fnd_Message.set_name ('AHL', 'AHL_APPR_TYPE_MISSING');
3209 Fnd_Msg_Pub.ADD;
3210 END IF;
3211 x_return_status := Fnd_Api.g_ret_sts_error;
3212 RETURN;
3213 END IF;
3214
3215 END Check_Approvers_Req_Items;
3216
3217
3218 PROCEDURE Check_Approver_Name_Or_Id(
3219 p_approvers_rec IN Approvers_rec_type,
3220 x_approver_id OUT NOCOPY NUMBER,
3221 x_return_status OUT NOCOPY VARCHAR2
3222 )
3223 IS
3224
3225 -- Bug 4919031 (Perf Fix)
3226 -- Spliting cursor c_approver_id into c_approver_id_user and c_approver_id_role
3227 -- The usage is based on p_approvers_rec.approver_type_code to be user or role
3228 /*
3229 CURSOR c_approver_id (approver_name IN VARCHAR2)
3230 IS
3231 SELECT ROLE_ID
3232 FROM AHL_APPROVERS_TYPE_V
3233 WHERE UPPER(ROLE_NAME) LIKE UPPER(approver_name)
3234 AND UPPER(LOOKUP_CODE) LIKE UPPER(p_approvers_rec.approver_type_code);
3235 */
3236
3237 CURSOR c_approver_id_user (approver_name IN VARCHAR2)
3238 IS
3239 SELECT AJREV.RESOURCE_ID
3240 FROM JTF_RS_RESOURCE_EXTNS AJREV,
3241 FND_USER USR
3242 WHERE AJREV.CATEGORY IN ('EMPLOYEE','PARTNER', 'PARTY')
3243 AND AJREV.RESOURCE_ID > 0
3244 AND USR.USER_NAME LIKE UPPER(approver_name)
3245 AND AJREV.USER_ID = USR.USER_ID;
3246
3247
3248 CURSOR c_approver_id_role (approver_name IN VARCHAR2)
3249 IS
3250 SELECT DISTINCT JRRV.role_id
3251 FROM jtf_rs_role_relations_vl JRRV
3252 WHERE JRRV.role_type_code in ('AHLAPPR', 'AHLGAPPR')
3253 AND UPPER(JRRV.role_name) LIKE UPPER(approver_name);
3254
3255 -- Bug 4919031 (Perf Fix)
3256 -- Spliting cursor c_approver_name_id into c_approver_name_id_user and c_approver_name_id_role
3257 -- The usage is based on p_approvers_rec.approver_type_code to be user or role
3258 /*
3259 CURSOR c_approver_name_id (approver_name IN VARCHAR2,
3260 approver_id IN NUMBER)
3261 IS
3262 SELECT ROLE_ID
3263 FROM AHL_APPROVERS_TYPE_V
3264 WHERE UPPER(ROLE_NAME) LIKE UPPER(approver_name)
3265 AND ROLE_ID = approver_id
3266 AND UPPER(LOOKUP_CODE) LIKE UPPER(p_approvers_rec.approver_type_code);
3267 */
3268
3269 CURSOR c_approver_name_id_user (approver_name IN VARCHAR2,
3270 approver_id IN NUMBER)
3271 IS
3272 SELECT AJREV.RESOURCE_ID
3273 FROM JTF_RS_RESOURCE_EXTNS AJREV
3274 WHERE AJREV.CATEGORY IN ('EMPLOYEE','PARTNER', 'PARTY')
3275 AND AJREV.RESOURCE_ID > 0
3276 AND UPPER(AJREV.USER_NAME) LIKE UPPER(approver_name)
3277 AND AJREV.RESOURCE_ID = approver_id;
3278
3279 CURSOR c_approver_name_id_role (approver_name IN VARCHAR2,
3280 approver_id IN NUMBER)
3281 IS
3282 SELECT DISTINCT JRRV.role_id
3283 FROM jtf_rs_role_relations_vl JRRV
3284 WHERE JRRV.role_type_code in ('AHLAPPR', 'AHLGAPPR')
3285 AND UPPER(JRRV.role_name) LIKE UPPER(approver_name)
3286 AND JRRV.role_id = approver_id;
3287
3288 l_approver_id NUMBER;
3289
3290 BEGIN
3291 x_return_status := Fnd_Api.g_ret_sts_success;
3292
3293 IF(p_approvers_rec.approver_name IS NOT NULL and p_approvers_rec.approver_name <> FND_API.G_MISS_CHAR) THEN
3294
3295
3296 -- Bug 4919031 (Perf Fix)
3297 -- Spliting cursor c_approver_id into c_approver_id_user and c_approver_id_role
3298 -- The usage is based on p_approvers_rec.approver_type_code to be user or role
3299
3300 IF UPPER(p_approvers_rec.approver_type_code) = 'ROLE' THEN
3301
3302 -- CHeck if the approver name and approver id match
3303 OPEN c_approver_name_id_role(p_approvers_rec.approver_name, p_approvers_rec.approver_id);
3304 FETCH c_approver_name_id_role INTO l_approver_id;
3305 IF c_approver_name_id_role%NOTFOUND THEN
3306 -- The approver_name has changed
3307 OPEN c_approver_id_role(p_approvers_rec.approver_name);
3308 LOOP
3309 EXIT WHEN c_approver_id_role%NOTFOUND;
3310 FETCH c_approver_id_role INTO l_approver_id;
3311 END LOOP;
3312
3313 IF c_approver_id_role%ROWCOUNT = 0 THEN
3314 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
3315 Fnd_Message.set_name ('AHL', 'AHL_APPR_TYPE_MISSING');
3316 Fnd_Msg_Pub.ADD;
3317 END IF;
3318 x_return_status := Fnd_Api.g_ret_sts_error;
3319 RETURN;
3320 ELSIF c_approver_id_role%ROWCOUNT > 1 THEN
3321 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
3322 Fnd_Message.set_name ('AHL', 'AHL_APPR_TYPE_MISSING');
3323 Fnd_Msg_Pub.ADD;
3324 END IF;
3325 x_return_status := Fnd_Api.g_ret_sts_error;
3326 RETURN;
3327 END IF;
3328 CLOSE c_approver_id_role;
3329 END IF;
3330 x_approver_id := l_approver_id;
3331 CLOSE c_approver_name_id_role;
3332
3333 ELSIF UPPER(p_approvers_rec.approver_type_code) = 'USER' THEN
3334
3335 -- CHeck if the approver name and approver id match
3336 OPEN c_approver_name_id_user(p_approvers_rec.approver_name, p_approvers_rec.approver_id);
3337 FETCH c_approver_name_id_user INTO l_approver_id;
3338 IF c_approver_name_id_user%NOTFOUND THEN
3339 -- The approver_name has changed
3340 OPEN c_approver_id_user(p_approvers_rec.approver_name);
3341 LOOP
3342 EXIT WHEN c_approver_id_user%NOTFOUND;
3343 FETCH c_approver_id_user INTO l_approver_id;
3344 END LOOP;
3345
3346 IF c_approver_id_user%ROWCOUNT = 0 THEN
3347 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
3348 Fnd_Message.set_name ('AHL', 'AHL_APPR_TYPE_MISSING');
3349 Fnd_Msg_Pub.ADD;
3350 END IF;
3351 x_return_status := Fnd_Api.g_ret_sts_error;
3352 RETURN;
3353 ELSIF c_approver_id_user%ROWCOUNT > 1 THEN
3354 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
3355 Fnd_Message.set_name ('AHL', 'AHL_APPR_TYPE_MISSING');
3356 Fnd_Msg_Pub.ADD;
3357 END IF;
3358 x_return_status := Fnd_Api.g_ret_sts_error;
3359 RETURN;
3360 END IF;
3361 CLOSE c_approver_id_user;
3362 END IF;
3363 x_approver_id := l_approver_id;
3364 CLOSE c_approver_name_id_user;
3365
3366 END IF;
3367
3368 END IF;
3369 END Check_Approver_Name_Or_Id;
3370
3371 -- Check_Approvers_UK_Items
3372 PROCEDURE Check_Approvers_UK_Items (
3373 p_Approvers_rec IN Approvers_Rec_Type,
3374 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
3375 x_return_status OUT NOCOPY VARCHAR2
3376 )
3377 IS
3378 l_valid_flag VARCHAR2(1);
3379 BEGIN
3380 x_return_status := Fnd_Api.g_ret_sts_success;
3381 -- APPROVAL_APPROVER_ID
3382 -- For Create_Approvers, when ID is passed in, we need to
3383 -- check if this ID is unique.
3384 IF p_validation_mode = Jtf_Plsql_Api.g_create
3385 AND p_Approvers_rec.Approval_Approver_Id IS NOT NULL
3386 THEN
3387 IF Ahl_Utility_Pvt.check_uniqueness(
3388 'AHL_Approvers',
3389 'APPROVAL_APPROVER_ID = ' || p_Approvers_rec.Approval_Approver_Id
3390 ) = Fnd_Api.g_false
3391 THEN
3392 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
3393 Fnd_Message.set_name ('AHL', 'AHL_APPR_DUPLICATE_ID');
3394 Fnd_Msg_Pub.ADD;
3395 END IF;
3396 x_return_status := Fnd_Api.g_ret_sts_error;
3397 RETURN;
3398 END IF;
3399 END IF;
3400 -- check if Approval_Rule_Id,Approver_Sequence and Approver_Id is UNIQUE
3401
3402 IF p_validation_mode = Jtf_Plsql_Api.g_create THEN
3403 l_valid_flag := Ahl_Utility_Pvt.check_uniqueness (
3404 'AHL_Approvers',
3405 'Approval_Rule_Id = ' || p_Approvers_rec.Approval_Rule_Id ||
3406 ' AND Approver_Sequence = ' || p_Approvers_rec.Approver_Sequence
3407 );
3408 ELSE
3409 l_valid_flag := Ahl_Utility_Pvt.check_uniqueness (
3410 'AHL_Approvers',
3411 'Approval_Rule_Id = ' || p_Approvers_rec.Approval_Rule_Id ||
3412 ' AND Approver_Sequence = ' || p_Approvers_rec.Approver_Sequence ||
3413 ' AND Approval_Approver_id <> ' || p_Approvers_rec.Approval_Approver_id
3414 );
3415 END IF;
3416 IF l_valid_flag = Fnd_Api.g_false THEN
3417 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
3418 Fnd_Message.set_name ('AHL', 'AHL_APPR_SEQ_NOT_UNIQUE');
3419 Fnd_Msg_Pub.ADD;
3420 END IF;
3421 x_return_status := Fnd_Api.g_ret_sts_error;
3422 RETURN;
3423 END IF;
3424 END Check_Approvers_UK_Items;
3425
3426 END Ahl_Approvals_Pvt;