[Home] [Help]
PACKAGE: APPS.AHL_APPROVALS_PVT
Source
1 PACKAGE AHL_APPROVALS_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVAPRS.pls 120.0 2005/05/25 23:54:43 appldev noship $ */
3
4 -----------------------------------------------------------
5 -- PACKAGE
6 -- Ahl_Approvals_Pvt
7 --
8 -- PURPOSE
9 -- This package is a Private API for managing Approval Rules and Approvers information in
10 -- Advanced Services Online. It contains specification for pl/sql records and tables
11 --
12 -- Create_Approvals (see below for specification)
13 -- Update_Approvals (see below for specification)
14 -- Delete_Approvals (see below for specification)
15 -- Validate_Approvals (see below for specification)
16 --
17 -- Check_Approvals_Items (see below for specification)
18 -- Check_Approvals_Record (see below for specification)
19 -- Init_Approvals_Rec (see below for specification)
20 -- Complete_Approvals_Rec (see below for specification)
21 --
22 -- Create_Approvers (see below for specification)
23 -- Update_Approvers (see below for specification)
24 -- Delete_Approvers (see below for specification)
25 -- Validate_Approvers (see below for specification)
26 --
27 -- Check_Approvers_Items (see below for specification)
28 -- Check_Approvers_Record (see below for specification)
29 -- Init_Approvers_Rec (see below for specification)
30 -- Complete_Approvers_Rec (see below for specification)
31 --
32 --
33 -- NOTES
34 --
35 --
36 -- HISTORY
37 -- 21-JAN-2002 SHBHANDA Created.
38 -----------------------------------------------------------
39
40 -------------------------------------
41 -- Approval Rules Record Type -----
42 -------------------------------------
43 --
44 TYPE Approval_Rules_Rec_Type IS RECORD (
45 APPROVAL_RULE_ID NUMBER,
46 OBJECT_VERSION_NUMBER NUMBER,
47 APPROVAL_OBJECT_CODE VARCHAR2(30),
48 APPROVAL_PRIORITY_CODE VARCHAR2(30),
49 APPROVAL_TYPE_CODE VARCHAR2(30),
50 APPLICATION_USG_CODE VARCHAR2(30),
51 APPLICATION_USG VARCHAR2(80),
52 OPERATING_UNIT_ID NUMBER,
53 OPERATING_NAME VARCHAR2(240),
54 ACTIVE_START_DATE DATE,
55 ACTIVE_END_DATE DATE,
56 STATUS_CODE VARCHAR2(30),
57 SEEDED_FLAG VARCHAR2(1),
58 ATTRIBUTE_CATEGORY VARCHAR2(30),
59 ATTRIBUTE1 VARCHAR2(150),
60 ATTRIBUTE2 VARCHAR2(150),
61 ATTRIBUTE3 VARCHAR2(150),
62 ATTRIBUTE4 VARCHAR2(150),
63 ATTRIBUTE5 VARCHAR2(150),
64 ATTRIBUTE6 VARCHAR2(150),
65 ATTRIBUTE7 VARCHAR2(150),
66 ATTRIBUTE8 VARCHAR2(150),
67 ATTRIBUTE9 VARCHAR2(150),
68 ATTRIBUTE10 VARCHAR2(150),
69 ATTRIBUTE11 VARCHAR2(150),
70 ATTRIBUTE12 VARCHAR2(150),
71 ATTRIBUTE13 VARCHAR2(150),
72 ATTRIBUTE14 VARCHAR2(150),
73 ATTRIBUTE15 VARCHAR2(150),
74 APPROVAL_RULE_NAME VARCHAR2(360),
75 DESCRIPTION VARCHAR2(2000),
76 CREATION_DATE DATE,
77 CREATED_BY NUMBER,
78 LAST_UPDATE_DATE DATE,
79 LAST_UPDATED_BY NUMBER,
80 lAST_UPDATE_LOGIN NUMBER,
81 OPERATION_FLAG VARCHAR2(1)
82 );
83
84 -------------------------------------
85 ----- Approvers Record Type -------
86 -------------------------------------
87
88 TYPE Approvers_Rec_Type IS RECORD (
89 APPROVAL_APPROVER_ID NUMBER,
90 OBJECT_VERSION_NUMBER NUMBER,
91 APPROVAL_RULE_ID NUMBER,
92 APPROVER_TYPE_CODE VARCHAR2(30),
93 APPROVER_SEQUENCE NUMBER,
94 APPROVER_ID NUMBER,
95 APPROVER_NAME VARCHAR2(100),
96 LAST_UPDATE_DATE DATE,
97 LAST_UPDATED_BY NUMBER,
98 CREATION_DATE DATE,
99 CREATED_BY NUMBER,
100 LAST_UPDATE_LOGIN NUMBER,
101 ATTRIBUTE_CATEGORY VARCHAR2(30),
102 ATTRIBUTE1 VARCHAR2(150),
103 ATTRIBUTE2 VARCHAR2(150),
104 ATTRIBUTE3 VARCHAR2(150),
105 ATTRIBUTE4 VARCHAR2(150),
106 ATTRIBUTE5 VARCHAR2(150),
107 ATTRIBUTE6 VARCHAR2(150),
108 ATTRIBUTE7 VARCHAR2(150),
109 ATTRIBUTE8 VARCHAR2(150),
110 ATTRIBUTE9 VARCHAR2(150),
111 ATTRIBUTE10 VARCHAR2(150),
112 ATTRIBUTE11 VARCHAR2(150),
113 ATTRIBUTE12 VARCHAR2(150),
114 ATTRIBUTE13 VARCHAR2(150),
115 ATTRIBUTE14 VARCHAR2(150),
116 ATTRIBUTE15 VARCHAR2(150),
117 OPERATION_FLAG VARCHAR2(1)
118 );
119
120 ---------------------------------------------------
121 -- Table Type of Approval Rules Record Type -----
122 ---------------------------------------------------
123
124 Type Approvers_Tbl IS TABLE OF Approvers_Rec_Type
125 INDEX BY BINARY_INTEGER;
126
127 ---------------------------------------------------------------------
128 -- PROCEDURE
129 -- Process_Approvals
130 --
131 -- PURPOSE
132 -- Process Approvals entry.
133 --
134 -- PARAMETERS
135 -- p_x_Approval_Rules_Rec: the record representing AHL_Approval_Rules_B and AHL_Approval_Rules_TL tables
136 -- p_x_Approvers_Tbl : the table representing the records of AHL_Approvers tables.
137 --
138 -- NOTES
139 -- 1. Procedure helps out to link between JSP page and API package
140 -- 2. On the basis of operation flag as one field in each record type
141 -- the further procedure for create/update/delete for Approvals Rules and Approvers.
142 ---------------------------------------------------------------------
143
144 PROCEDURE Process_Approvals (
145 p_api_version IN NUMBER,
146 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
147 p_commit IN VARCHAR2,
148 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
149
150 p_x_Approval_Rules_Rec IN OUT NOCOPY Approval_Rules_Rec_Type,
151 p_x_Approvers_Tbl IN OUT NOCOPY Approvers_Tbl,
152
153 x_return_status OUT NOCOPY VARCHAR2,
154 x_msg_count OUT NOCOPY NUMBER,
155 x_msg_data OUT NOCOPY VARCHAR2
156 );
157
158 ---------------------------------------------------------------------
159 ------------ APPROVAL RULES -------------
160 ---------------------------------------------------------------------
161
162 --------------------------------------------------------------------
163 -- PROCEDURE
164 -- Create_Approval_Rules
165 --
166 -- PURPOSE
167 -- Create Approval Rules entry.
168 --
169 -- PARAMETERS
170 -- p_Approval_rec: the record representing AHL_Approval_Rules_VL view..
171 -- x_Approval_Rule_Id: the Approval_Rule_Id.
172 --
173 -- NOTES
174 -- 1. object_version_number will be set to 1.
175 -- 2. If Approval_Rules_Id is passed in, the uniqueness will be checked.
176 -- Raise exception in case of duplicates.
177 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
178 -- Raise exception for invalid flag.
179 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
180 -- 6. Please don't pass in any FND_API.g_miss_char/num/date.
181 -------------------------------------------------------------------
182 PROCEDURE Create_Approval_Rules (
183 p_api_version IN NUMBER,
184 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
185 p_commit IN VARCHAR2,
186 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
187
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2,
191
192 p_Approval_Rules_Rec IN Approval_Rules_Rec_Type,
193 x_Approval_Rules_Id OUT NOCOPY NUMBER
194 );
195
196 --------------------------------------------------------------------
197 -- PROCEDURE
198 -- Update_Approval_Rules
199 --
200 -- PURPOSE
201 -- Update an Approval_Rules entry.
202 --
203 -- PARAMETERS
204 -- p_Approval_Rules_rec: the record representing AHL_Approval_Rules_VL (without the ROW_ID column).
205 --
206 -- NOTES
207 -- 1. Raise exception if the object_version_number doesn't match.
208 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
209 -- that column won't be updated.
210 --------------------------------------------------------------------
211 PROCEDURE Update_Approval_Rules (
212 p_api_version IN NUMBER,
213 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
214 p_commit IN VARCHAR2,
215 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
216
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2,
220
221 p_Approval_Rules_rec IN Approval_Rules_Rec_Type
222 );
223
224 --------------------------------------------------------------------
225 -- PROCEDURE
226 -- Delete_Approval_Rules
227 --
228 -- PURPOSE
229 -- Delete a Approval Rules entry.
230 --
231 -- PARAMETERS
232 -- p_Approval_Rules_id: the Approval_Rules_id
233 -- p_object_version: the object_version_number
234 --
235 -- ISSUES
236 --
237 -- NOTES
238 -- 1. Raise exception if the object_version_number doesn't match.
239 --------------------------------------------------------------------
240 PROCEDURE Delete_Approval_Rules (
241 p_api_version IN NUMBER,
242 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
243 p_commit IN VARCHAR2,
244 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
245
246 x_return_status OUT NOCOPY VARCHAR2,
247 x_msg_count OUT NOCOPY NUMBER,
248 x_msg_data OUT NOCOPY VARCHAR2,
249
250 p_Approval_Rule_Id IN NUMBER,
251 p_object_version IN NUMBER
252 );
253
254 --------------------------------------------------------------------
255 -- PROCEDURE
256 -- Validate_Approval Rules
257 --
258 -- PURPOSE
259 -- Validate a Approval Rules entry.
260 --
261 -- PARAMETERS
262 -- p_Approval_rec: the record representing AHL_Approval_Rules_VL (without ROW_ID).
263 --
264 -- NOTES
265 -- 1. p_Approval_Rules_rec should be the complete Approval Rules record. There
266 -- should not be any FND_API.g_miss_char/num/date in it.
267 -- 2. If FND_API.g_miss_char/num/date is in the record, then raise
268 -- an exception, as those values are not handled.
269 --------------------------------------------------------------------
270 PROCEDURE Validate_Approval_Rules (
271 p_api_version IN NUMBER,
272 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
273 p_commit IN VARCHAR2,
274 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
275
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_count OUT NOCOPY NUMBER,
278 x_msg_data OUT NOCOPY VARCHAR2,
279
280 p_Approval_Rules_rec IN Approval_Rules_Rec_Type
281 );
282
283 ---------------------------------------------------------------------
284 -- PROCEDURE
285 -- Check_Approval_Rules_Items
286 --
287 -- PURPOSE
288 -- Perform the item level checking including unique keys,
289 -- required columns, foreign keys, domain constraints.
290 --
291 -- PARAMETERS
292 -- p_Approval_Rules_rec: the record to be validated
293 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
294 ---------------------------------------------------------------------
295 PROCEDURE Check_Approval_Rules_Items (
296 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
297 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
298 x_return_status OUT NOCOPY VARCHAR2
299 );
300
301 ---------------------------------------------------------------------
302 -- PROCEDURE
303 -- Check_Approval_Rules_Record
304 --
305 -- PURPOSE
306 -- Check the record level business rules.
307 --
308 -- PARAMETERS
309 -- p_Approval_Rules_rec: the record to be validated; may contain attributes
310 -- as FND_API.g_miss_char/num/date
311 -- p_complete_rec: the complete record after all "g_miss" items
312 -- have been replaced by current database values
313 ---------------------------------------------------------------------
314 PROCEDURE Check_Approval_Rules_Record (
315 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
316 p_complete_rec IN Approval_Rules_Rec_Type := NULL,
317 x_return_status OUT NOCOPY VARCHAR2
318 );
319
320 ---------------------------------------------------------------------
321 -- PROCEDURE
322 -- Init_Approval_Rules_Rec
323 --
324 -- PURPOSE
325 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
326 ---------------------------------------------------------------------
327 /*PROCEDURE Init_Approval_Rules_Rec (
328 x_Approval_Rules_rec OUT NOCOPY Approval_Rules_Rec_Type
329 );
330 */
331 ---------------------------------------------------------------------
332 -- PROCEDURE
333 -- Complete_Approval_Rules_Rec
334 --
335 -- PURPOSE
336 -- For Update_Approval_Rules, some attributes may be passed in as
337 -- FND_API.g_miss_char/num/date if the user doesn't want to
338 -- update those attributes. This procedure will replace the
339 -- "g_miss" attributes with current database values.
340 --
341 -- PARAMETERS
342 -- p_Approval_Rules_rec: the record which may contain attributes as
343 -- FND_API.g_miss_char/num/date
344 -- x_complete_rec: the complete record after all "g_miss" items
345 -- have been replaced by current database values
346 ---------------------------------------------------------------------
347 PROCEDURE Complete_Approval_Rules_Rec (
348 p_Approval_Rules_rec IN Approval_Rules_Rec_Type,
349 x_complete_rec OUT NOCOPY Approval_Rules_Rec_Type
350 );
351
352 ---------------------------------------------------------------------
353 ------------ APPROVERS -------------
354 ---------------------------------------------------------------------
355
356 ---------------------------------------------------------------------
357 -- PROCEDURE
358 -- Create_Approvers
359 --
360 -- PURPOSE
361 -- Create Approvers entry.
362 --
363 -- PARAMETERS
364 -- p_Approvers_rec: the record representing AHL_Approvers_V view..
365 -- x_Approval_Approver_Id: the Approval_Approver_Id.
366 --
367 -- NOTES
368 -- 1. object_version_number will be set to 1.
369 -- 2. If Approvers_Id is passed in, the uniqueness will be checked.
370 -- Raise exception in case of duplicates.
371 -- 3. Please don't pass in any FND_API.g_mess_char/num/date.
372 ---------------------------------------------------------------------
373
374 PROCEDURE Create_Approvers (
375 p_api_version IN NUMBER,
376 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
377 p_commit IN VARCHAR2,
378 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
379 x_return_status OUT NOCOPY VARCHAR2,
380 x_msg_count OUT NOCOPY NUMBER,
381 x_msg_data OUT NOCOPY VARCHAR2,
382
383 p_Approvers_Rec IN Approvers_Rec_Type,
384 x_Approval_Approver_Id OUT NOCOPY NUMBER
385 );
386
387 --------------------------------------------------------------------
388 -- PROCEDURE
389 -- Update_Approvers
390 --
391 -- PURPOSE
395 --
392 -- Update an Approvers entry.
393 --
394
396 -- PARAMETERS
397 -- p_Approvers_rec: the record representing AHL_Approvers_V (without the ROW_ID column).
398 --
399 -- NOTES
400 -- 1. Raise exception if the object_version_number doesn't match.
401 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
402 -- that column won't be updated.
403 --------------------------------------------------------------------
404 PROCEDURE Update_Approvers (
405 p_api_version IN NUMBER,
406 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
407 p_commit IN VARCHAR2,
408 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
409 p_Approvers_rec IN Approvers_Rec_Type,
410
411 x_return_status OUT NOCOPY VARCHAR2,
412 x_msg_count OUT NOCOPY NUMBER,
413 x_msg_data OUT NOCOPY VARCHAR2
414 );
415
416 --------------------------------------------------------------------
417 -- PROCEDURE
418 -- Delete_Approvers
419 --
420 -- PURPOSE
421 -- Delete a Approvers entry.
422 --
423 -- PARAMETERS
424 -- p_Approvers_id: the Approvers_id
425 -- p_object_version: the object_version_number
426 --
427 -- ISSUES
428 --
429 --
430 -- NOTES
431 -- 1. Raise exception if the object_version_number doesn't match.
432 --------------------------------------------------------------------
433
434 PROCEDURE Delete_Approvers (
435 p_api_version IN NUMBER,
436 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
437 p_commit IN VARCHAR2,
438 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
439 p_Approval_Approver_Id IN NUMBER,
440 p_object_version IN NUMBER,
441 x_return_status OUT NOCOPY VARCHAR2,
442 x_msg_count OUT NOCOPY NUMBER,
443 x_msg_data OUT NOCOPY VARCHAR2
444 );
445
446 --------------------------------------------------------------------
447 -- PROCEDURE
448 -- Validate_Approvers
449 --
450 -- PURPOSE
451 -- Validate a Approvers entry.
452 --
453 -- PARAMETERS
454 -- p_Approvers_rec: the record representing AHL_Approvers_V (without ROW_ID).
455 --
456 -- NOTES
457 -- 1. p_Approvers_rec should be the complete Approvers record. There
458 -- should not be any FND_API.g_miss_char/num/date in it.
459 -- 2. If FND_API.g_miss_char/num/date is in the record, the raise
460 -- an exception, as those values are not handled.
461 --------------------------------------------------------------------
462 PROCEDURE Validate_Approvers (
463 p_api_version IN NUMBER,
464 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
465 p_commit IN VARCHAR2,
466 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
467
468 x_return_status OUT NOCOPY VARCHAR2,
469 x_msg_count OUT NOCOPY NUMBER,
470 x_msg_data OUT NOCOPY VARCHAR2,
471 p_Approvers_rec IN Approvers_Rec_Type
472 );
473
474 --------------------------------------------------------------------
475 -- PROCEDURE
476 -- Check_Approvers_Items
477 --
478 -- PURPOSE
479 -- Perform the item level checking including unique keys,
480 -- required columns, foreign keys, domain constraints.
481 --
482 -- PARAMETERS
483 -- p_Approvers_rec: the record to be validated
484 -- p_validation_mode: JTF_PLSQL_API.g_create/g_update
485 ---------------------------------------------------------------------
486 PROCEDURE Check_Approvers_Items (
487 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
488 p_Approvers_rec IN Approvers_Rec_Type,
489
490 x_return_status OUT NOCOPY VARCHAR2
491 );
492
493 ---------------------------------------------------------------------
494
495 -- PROCEDURE
496 -- Check_Approvers_Record
497 --
498 -- PURPOSE
499 -- Check the record level business rules.
500 --
501 -- PARAMETERS
502 -- p_Approvers_rec: the record to be validated; may contain attributes
503 -- as FND_API.g_miss_char/num/date
504 -- p_complete_rec: the complete record after all "g_miss" items
505 -- have been replaced by current database values
506 ---------------------------------------------------------------------
507 --PROCEDURE Check_Approvers_Record (
508 -- p_Approvers_rec IN Approvers_Rec_Type,
509 --p_complete_rec IN Approvers_Rec_Type := NULL,
510
511 -- x_return_status OUT NOCOPY VARCHAR2
512 --);
513
514 ---------------------------------------------------------------------
515 -- PROCEDURE
516 -- Init_Approvers_Rec
517 --
518 -- PURPOSE
519 -- Initialize all attributes to be FND_API.g_miss_char/num/date.
520 ---------------------------------------------------------------------
521 /*PROCEDURE Init_Approvers_Rec (
522 x_Approvers_rec OUT NOCOPY Approvers_Rec_Type
523 );
524 */
525
526 ---------------------------------------------------------------------
527 -- PROCEDURE
528 -- Complete_Approvers_Rec
529 --
530 -- PURPOSE
531 -- For Update_Approvers, some attributes may be passed in as
532 -- FND_API.g_miss_char/num/date if the user doesn't want to
533 -- update those attributes. This procedure will replace the
534 -- "g_miss" attributes with current database values.
535 --
536 -- PARAMETERS
537 -- p_Approvers_rec: the record which may contain attributes as
538 -- FND_API.g_miss_char/num/date
539 -- x_complete_rec: the complete record after all "g_miss" items
540 -- have been replaced by current database values
541 ---------------------------------------------------------------------
542 PROCEDURE Complete_Approvers_Rec (
543 p_Approvers_rec IN Approvers_Rec_Type,
544 x_complete_rec OUT NOCOPY Approvers_Rec_Type
545 );
546
547 END Ahl_Approvals_Pvt;