[Home] [Help]
PACKAGE BODY: APPS.AME_DYNAMIC_APPROVAL_PKG
Source
1 package body ame_dynamic_approval_pkg as
2 /* $Header: amedapkg.pkb 120.2 2005/11/16 00:19:35 ubhat noship $ */
3
4 /* procedures */
5
6 function getApproverGroup(p_group_id in number)
7 return varchar2 as
8 -- Cursor approver group name
9 CURSOR c_approver_group_name (
10 p_group_id IN number
11 )
12 IS
13 select name from ame_approval_groups where
14 approval_group_id = p_group_id;
15
16 ln_group_name varchar2(240) := null;
17 begin
18 OPEN c_approver_group_name ( p_group_id=>p_group_id);
19 FETCH c_approver_group_name INTO ln_group_name;
20 IF c_approver_group_name%NOTFOUND THEN
21 return null;
22 END IF ;
23 CLOSE c_approver_group_name;
24 return ln_group_name;
25 end;
26
27
28 PROCEDURE insert_ame_approver(
29 p_application_id in number,
30 p_transaction_type in varchar2,
31 p_transaction_id in varchar2,
32 p_approverIn in ame_approver_record2_table_ss,
33 p_positionIn in number,
34 p_insertionIn in ame_insertion_record2_table_ss,
35 p_warning_msg_name OUT NOCOPY varchar2,
36 p_error_msg_text OUT NOCOPY varchar2
37 ) as
38
39 PRAGMA AUTONOMOUS_TRANSACTION;
40 --local variables
41 l_proc constant varchar2(100) := 'insert_ame_approver';
42
43 c_approver_rec2 ame_util.approverRecord2;
44 c_insertion_record2 ame_util.insertionRecord2;
45 lv_parameter varchar2(650);
46 BEGIN
47
48 hr_utility.set_location('Entering: insert_ame_approver', 1);
49 hr_utility.trace('p_application_id=' || p_application_id);
50 hr_utility.trace('p_transaction_type=' || p_transaction_type);
51 hr_utility.trace('p_transaction_id=' || p_transaction_id);
52 hr_utility.trace('inserting approver ' || p_approverIn(1).name);
53 hr_utility.trace('inserting approver id= ' || p_approverIn(1).orig_system_id);
54
55 --Copy details from p_approverIn/p_insertionIn to c_approver_rec2
56 c_approver_rec2.name := p_approverIn(1).name;
57 c_approver_rec2.orig_system := p_approverIn(1).orig_system ;
58 c_approver_rec2.orig_system_id := p_approverIn(1).orig_system_id ;
59 c_approver_rec2.approver_category := p_approverIn(1).approver_category;
60 c_approver_rec2.approval_status := null;
61 c_approver_rec2.item_class := p_insertionIn(1).item_class;
62 c_approver_rec2.item_id := p_insertionIn(1).item_id;
63 c_approver_rec2.action_type_id := p_insertionIn(1).action_type_id;
64 c_approver_rec2.group_or_chain_id := p_insertionIn(1).group_or_chain_id;
65 c_approver_rec2.api_insertion := p_insertionIn(1).api_insertion;
66 c_approver_rec2.authority := p_insertionIn(1).authority;
67
68 -- Copy details from p_insertionIn to c_insertion_record2
69 c_insertion_record2.item_class := p_insertionIn(1).item_class;
70 c_insertion_record2.item_id := p_insertionIn(1).item_id;
71 c_insertion_record2.action_type_id := p_insertionIn(1).action_type_id;
72 c_insertion_record2.group_or_chain_id := p_insertionIn(1).group_or_chain_id;
73 c_insertion_record2.api_insertion := p_insertionIn(1).api_insertion;
74 c_insertion_record2.authority := p_insertionIn(1).authority;
75 c_insertion_record2.order_type := p_insertionIn(1).order_type;
76 -- to take care of '\v' special character not passivated,
77 -- so replacing with another special charater which will be
78 -- passivated.
79 lv_parameter := p_insertionIn(1).parameter;
80 lv_parameter := replace(lv_parameter,'^',ame_util.fieldDelimiter);
81 c_insertion_record2.parameter := lv_parameter;
82
83 c_insertion_record2.description := p_insertionIn(1).description;
84
85 if (ame_util.firstAuthority = c_insertion_record2.order_type) then
86 begin
87 -- for now, we are not inserting COA first Authoriry approvers
88 -- setFirstAuthority is accepting only Authority Insertion, but
89 -- getAvailableOption returning 'Y'
90 c_approver_rec2.api_insertion := 'A';
91 ame_api2.setFirstAuthorityApprover(applicationIdIn =>p_application_id,
92 transactionIdIn =>p_transaction_id,
93 approverIn =>c_approver_rec2,
94 clearChainStatusYNIn => 'N',
95 transactionTypeIn=>p_transaction_type );
96 end;
97 else
98 begin
99 -- These parameters need to be null for adhoc approvers
100 c_insertion_record2.action_type_id := ame_util.nullInsertionActionTypeId;
101 c_insertion_record2.group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
102
103 c_approver_rec2.action_type_id := ame_util.nullInsertionActionTypeId;
104 c_approver_rec2.group_or_chain_id := ame_util.nullInsertionGroupOrChainId;
105
106 ame_api3.insertApprover(applicationIdIn =>p_application_id,
107 transactionIdIn =>p_transaction_id,
108 approverIn =>c_approver_rec2,
109 positionIn => p_positionIn,
110 insertionIn =>c_insertion_record2,
111 transactionTypeIn=>p_transaction_type );
112 end;
113 end if;
114
115 hr_utility.set_location('Leaving: insert_ame_approver', 2);
116 commit;
117 EXCEPTION
118 WHEN OTHERS THEN
119 rollback;
120 raise;
121 END insert_ame_approver;
122
123
124
125 PROCEDURE delete_ame_approver(
126 p_application_id in number,
127 p_transaction_type in varchar2,
128 p_transaction_id in varchar2,
129 p_approverIn in ame_approver_record2_table_ss,
130 p_warning_msg_name OUT NOCOPY varchar2,
131 p_error_msg_text OUT NOCOPY varchar2
132
133 )
134
135 as
136 PRAGMA AUTONOMOUS_TRANSACTION;
137 --local variables
138 l_proc constant varchar2(100) := 'delete_ame_approver';
139 c_approver_rec2 ame_util.approverRecord2;
140 BEGIN
141
142 hr_utility.set_location('Entering: delete_ame_approver', 1);
143 hr_utility.trace('p_application_id=' || p_application_id);
144 hr_utility.trace('p_transaction_type=' || p_transaction_type);
145 hr_utility.trace('p_transaction_id=' || p_transaction_id);
146 hr_utility.trace('deleting approver ' || p_approverIn(1).name);
147
148 -- copy details from p_approverIn to c_approver_rec2
149 c_approver_rec2.name := p_approverIn(1).name;
150 c_approver_rec2.item_class := p_approverIn(1).item_class;
151 c_approver_rec2.item_id := p_approverIn(1).item_id;
152 c_approver_rec2.action_type_id := p_approverIn(1).action_type_id;
153 c_approver_rec2.group_or_chain_id := p_approverIn(1).group_or_chain_id;
154 c_approver_rec2.api_insertion := p_approverIn(1).api_insertion;
155 c_approver_rec2.occurrence := p_approverIn(1).occurrence;
156
157
158 if(p_approverIn(1).api_Insertion = ame_util.apiInsertion) then
159 begin
160 -- API Inserrted Adhoc approver, so call clearInsertion
161 ame_api3.clearInsertion(applicationIdIn =>p_application_id,
162 transactionTypeIn => p_transaction_type,
163 transactionIdIn =>p_transaction_id,
164 approverIn =>c_approver_rec2
165 );
166
167 end;
168 else
169 begin
170 -- AME genereated approver, so call supressApprover
171 ame_api3.suppressApprover(applicationIdIn =>p_application_id,
172 transactionTypeIn => p_transaction_type,
173 transactionIdIn =>p_transaction_id,
174 approverIn =>c_approver_rec2
175 );
176
177 end;
178 end if;
179 hr_utility.set_location('Leaving: delete_ame_approver', 2);
180 commit;
181 EXCEPTION
182 WHEN OTHERS THEN
183 rollback;
184 raise;
185 END delete_ame_approver;
186
187
188
189 PROCEDURE get_ame_apprs_and_ins_list(
190 p_application_id in integer,
191 p_transaction_type in varchar2,
192 p_transaction_id in varchar2,
193 p_apprs_view_type in varchar2 default 'Active',
194 p_coa_insertions_flag in varchar2 default 'N',
195 p_ame_approvers_list OUT NOCOPY ame_approver_record2_table_ss,
196 p_ame_order_type_list OUT NOCOPY ame_insertion_record2_table_ss,
197 -- We need this value to add "Append to list" option
198 p_all_approvers_count out NOCOPY varchar2,
199 p_warning_msg_name OUT NOCOPY varchar2,
200 p_error_msg_text OUT NOCOPY varchar2
201
202 )
203 is
204
205 --local variables
206
207 l_proc constant varchar2(100) := 'get_ame_apprs_and_ins_list';
208 lv_parameter varchar2(650);
209 errString ame_util.longestStringType;
210 CURSOR c_active_order_type_name (
211 p_order_type IN varchar2
212 )
213 IS
214 select description
215 from fnd_lookups
216 where lookup_type like 'AME_DA_ACTIVE_ORDER_TYPE' and lookup_code = p_order_type;
217
218
219 CURSOR c_order_type_name (
220 p_order_type IN varchar2
221 )
222 IS
223 select meaning
224 from fnd_lookups
225 where lookup_type like 'AME_APPR_INSERTION_ORDER_TYPE' and lookup_code = p_order_type;
226
227 CURSOR c_apr_status (
228 p_status IN varchar2
229 )
230 IS
231 select meaning
232 from fnd_lookups
233 where lookup_type like 'AME_APPROVAL_STATUS' and lookup_code = p_status;
234
235 CURSOR c_apr_category (
236 p_category IN varchar2
237 )
238 IS
239 select meaning
240 from fnd_lookups
241 where lookup_type like 'AME_APPROVER_CATEGORY' and lookup_code = p_category;
242
243
244 l_default_approvers_list ame_approver_record2_table_ss := ame_approver_record2_table_ss();
245 l_default_approver ame_approver_record2_ss;
246 l_default_insertions_list ame_insertion_record2_table_ss := ame_insertion_record2_table_ss();
247 l_default_insertion ame_insertion_record2_ss;
248 ln_approver_index NUMBER;
249 c_all_approvers ame_util.approversTable2;
250 c_all_insertions ame_util2.insertionsTable3;
251 ln_approver_list_cnt NUMBER ;
252 ln_insertions_index NUMBER;
253 ln_insertion_record2_num number;
254 lv_approval_status varchar2(10);
255 ln_approver_group_name varchar2(240);
256 bactiveApproversYNIn varchar2(1);
257 allowDeletingOamApprovers ame_util.attributeValueType;
258 ruleIdList ame_util.idList;
259 sourceDescription ame_util.stringType;
260 allow_delete varchar2(30);
261 ln_order_type_name varchar2(100);
262 active_apr_index number;
263 last_active_apr_name varchar2(100);
264 ln_apr_status varchar2(80);
265 ln_apr_category varchar2(80);
266 tempIndex integer;
267
268
269 BEGIN
270
271 hr_utility.set_location('Entering: get_ame_apprs_and_ins_list', 1);
272 hr_utility.trace('p_application_id=' || p_application_id);
273 hr_utility.trace('p_transaction_type=' || p_transaction_type);
274 hr_utility.trace('p_transaction_id=' || p_transaction_id);
275
276 -- set flag based on user selection for Active or All Approvers
277 -- zero means active approver
278 if(p_apprs_view_type = '0' or p_apprs_view_type is null) then
279 bactiveApproversYNIn := ame_util.booleanTrue;
280 else
281 bactiveApproversYNIn := ame_util.booleanFalse;
282 end if;
283
284 -- get Active/All AME Approvers
285 ame_api5.getAllApproversAndInsertions(applicationIdIn =>p_application_id,
286 transactionIdIn=>p_transaction_id,
287 transactionTypeIn =>p_transaction_type,
288 activeApproversYNIn => bactiveApproversYNIn,
289 -- currently not getting COA approvers
290 coaInsertionsYNIN => ame_util.booleanFalse,
291 approvalProcessCompleteYNOut =>lv_approval_status,
292 approversOut=>c_all_approvers,
293 availableInsertionsOut => c_all_insertions);
294
295
296 -- populate the p_ame_approvers_list and p_aprs_aval_insr_list
297 ln_insertion_record2_num := 0;
298 ln_approver_list_cnt :=0;
299
300 -- iterate through approvers list
301 tempIndex := c_all_approvers.first;
302 while(tempIndex is not null) loop
303 begin
304 -- count for approvers
305 ln_approver_list_cnt:= ln_approver_list_cnt + 1;
306 -- parse the source to see if the approver was inserted.
307 ame_util.parseSourceValue(sourceValueIn => c_all_approvers(tempIndex).source,
308 sourceDescriptionOut => sourceDescription,
309 ruleIdListOut => ruleIdList);
310
311 -- If the approver was OAM generated, check whether deleting OAM-generated approvers
312 -- is allowed or not. If so, record the deletion.
313 allow_delete := 'AMEDeleteEnabled';
314 if(c_all_approvers(tempIndex).api_insertion = ame_util.oamGenerated or
315 sourceDescription = ame_util.ruleGeneratedSource ) then
316 begin
317 allowDeletingOamApprovers :=
318 ame_engine.getHeaderAttValue2(attributeNameIn => ame_util.allowDeletingOamApprovers);
319 if(allowDeletingOamApprovers <> ame_util.booleanAttributeTrue) then
320 begin
321 allow_delete := 'AMEDeleteDisabled';
322 end;
323 end if;
324 end;
325 end if;
326
327 -- if approver already responded then disable delete button
328 if (c_all_approvers(tempIndex).approval_status is not null and
329 c_all_approvers(tempIndex).approval_status in
330 (ame_util.approveAndForwardStatus
331 ,ame_util.approvedStatus
332 ,ame_util.notifiedStatus
333 ,ame_util.notifiedByRepeatedStatus
334 ,ame_util.approvedByRepeatedStatus
335 ,ame_util.rejectedByRepeatedStatus
336 ,ame_util.suppressedStatus)) then
337 begin
338 allow_delete := 'AMEDeleteDisabled';
339 end;
340 end if;
341
342 -- get Approver Group
343 ln_approver_group_name := getApproverGroup(c_all_approvers(tempIndex).group_or_chain_id);
344
345 -- get approver status
346 ln_apr_status := null;
347 if (c_all_approvers(tempIndex).approval_status is not null) then
348 begin
349 OPEN c_apr_status ( p_status=>trim(c_all_approvers(tempIndex).approval_status));
350 FETCH c_apr_status INTO ln_apr_status;
351 CLOSE c_apr_status;
352 end;
353 end if;
354
355 -- get approver category lookup value
356 OPEN c_apr_category ( p_category=>trim(c_all_approvers(tempIndex).approver_category));
357 FETCH c_apr_category INTO ln_apr_category;
358 CLOSE c_apr_category;
359
360
361 -- create the out ame_approver_record2_ss
362 l_default_approver := ame_approver_record2_ss(
363 tempIndex, -- approver line no
364 c_all_approvers(tempIndex).name, -- name
365 c_all_approvers(tempIndex).orig_system,
366 c_all_approvers(tempIndex).orig_system_id,
367 c_all_approvers(tempIndex).display_name, -- display name
368 ln_apr_category, --c_all_approvers(i).approver_category,
369 c_all_approvers(tempIndex).api_insertion,
370 c_all_approvers(tempIndex).authority, -- authority
371 ln_apr_status, --c_all_approvers(i).approval_status,
372 c_all_approvers(tempIndex).action_type_id,
373 c_all_approvers(tempIndex).group_or_chain_id, -- group_or_chain_id
374 c_all_approvers(tempIndex).occurrence, -- occurrence
375 null, -- source
376 c_all_approvers(tempIndex).item_class, -- item_class
377 c_all_approvers(tempIndex).item_id, -- item_id
378 c_all_approvers(tempIndex).approver_order_number,
379 allow_delete, -- allow detele
380 ln_approver_group_name -- approver_group_name
381 );
382
383
384 -- add new row to the approvers list
385 l_default_approvers_list.EXTEND;
386 -- add to list
387 l_default_approvers_list(ln_approver_list_cnt) := l_default_approver;
388
389 -- get next approver record from sparse array
390 tempIndex := c_all_approvers.next(tempIndex);
391 end;
392 END LOOP; -- approvers loop
393
394
395
396
397 ln_insertions_index := c_all_insertions.count;
398
399 -- get all approvers count by subtracting one from last insertion record position value
400 ln_approver_index := c_all_insertions(ln_insertions_index).position;
401 ln_approver_index := ln_approver_index - 1;
402 p_all_approvers_count := ln_approver_index || '';
403
404
405 -- iterate through all insertion records to pass only active insertions and
406 -- and substitute "Order" , "After Approver Name" and "Before Approver Name"
407 active_apr_index := 1;
408 FOR J IN 1..ln_insertions_index LOOP
409
410 -- increment the active_approver_index once we are done with current position
411 if( active_apr_index = 0 or ((active_apr_index < ln_approver_list_cnt+1)
412 and c_all_insertions(j).position > l_default_approvers_list(active_apr_index).line_no))
413 then
414 begin
415 active_apr_index := active_apr_index + 1;
416 end;
417 end if;
418
419 -- Copy the records for last approver and active approver index position inserion records
420 if( (ln_approver_index+1 = c_all_insertions(j).position)
421 or ((active_apr_index < ln_approver_list_cnt+1)
422 and
423 c_all_insertions(j).position =
424 l_default_approvers_list(active_apr_index).line_no))
425 then
426 begin
427
428
429 -- getting lookup values based on active or all approvers mode
430 if( bactiveApproversYNIn = 'Y' and c_all_insertions(j).order_type
431 in ('absolute order','before approver','after approver')
432 ) then
433 begin
434 OPEN c_active_order_type_name ( p_order_type=>upper(c_all_insertions(j).order_type));
435 FETCH c_active_order_type_name INTO ln_order_type_name;
436 CLOSE c_active_order_type_name;
437 end;
438 else
439 begin
440 OPEN c_order_type_name ( p_order_type=>upper(c_all_insertions(j).order_type));
441 FETCH c_order_type_name INTO ln_order_type_name;
442 CLOSE c_order_type_name;
443 end;
444 end if;
445
446 -- append approver names or order number to based on order types to display in
447 -- in the poplist
448 -- append order number
449 if( c_all_insertions(j).order_type = 'absolute order') then
450 ln_order_type_name := ln_order_type_name || ' : ' || active_apr_index;
451 end if;
452 -- append before approver name
453 if ( c_all_insertions(j).order_type = 'after approver' ) then
454 begin
455 -- in case of first approver and order_type='after approver'
456 -- skip this insertion order type for end user
457 if(active_apr_index <= 1)then goto End_of_Insertions_Loop; end if;
458
459 ln_order_type_name := ln_order_type_name || ' : '
460 || l_default_approvers_list(active_apr_index-1).display_name;
461 end;
462 end if;
463 -- append before approver name
464 if ( c_all_insertions(j).order_type = 'before approver') then
465 ln_order_type_name := ln_order_type_name || ' : '
466 || l_default_approvers_list(active_apr_index).display_name;
467 end if;
468
469 -- to take care of '\v' special character not passivated,
470 -- so replacing with another special charater which will be
471 -- passivated.
472 lv_parameter := c_all_insertions(j).parameter;
473 lv_parameter := replace(lv_parameter,ame_util.fieldDelimiter,'^');
474
475 l_default_insertion := ame_insertion_record2_ss(
476 c_all_insertions(j).position, -- position index
477 c_all_insertions(j).item_class, -- item_class
478 c_all_insertions(j).item_id, -- item_id
479 c_all_insertions(j).action_type_id, -- null for adhoc
480 c_all_insertions(j).group_or_chain_id, -- null for adhoc
481 c_all_insertions(j).order_type, -- order_type
482 lv_parameter, -- parameter
483 c_all_insertions(j).api_Insertion,
484 c_all_insertions(j).authority, -- authority
485 ln_order_type_name -- appended order type
486 );
487 -- add new row to insertion list
488 l_default_insertions_list.EXTEND;
489 ln_insertion_record2_num := ln_insertion_record2_num + 1;
490 -- add to list
491 l_default_insertions_list(ln_insertion_record2_num) := l_default_insertion ;
492 end;
493 end if;
494
495 <<End_of_Insertions_Loop>>
496 -- this statement included just for above End_of_Insertions_Loop label
497 hr_utility.trace('end of insertion records for loop');
498 END LOOP; -- insertions loop
499 -- end of the reading insertion records
500
501 -- set out parameters for approvers and insertion records
502 p_ame_order_type_list := l_default_insertions_list;
503 p_ame_approvers_list := l_default_approvers_list;
504
505 hr_utility.set_location('Leaving: get_ame_apprs_and_ins_list', 2);
506
507 EXCEPTION
508 WHEN OTHERS THEN
509 ame_util.runtimeException(packageNameIn => 'ame_dynamic_approval_pkg',
510 routineNameIn => 'get_ame_apprs_and_ins_list',
511 exceptionNumberIn => sqlcode,
512 exceptionStringIn => sqlerrm);
513 if sqlcode = -20001 then
514 errString := sqlerrm;
515 errString:= substr(errString,11);
516 else
517 fnd_message.set_name('PER','AME_400692_ENGINE_ERROR');
518 errString := fnd_message.get;
519 end if;
520 p_warning_msg_name := errString;
521 END get_ame_apprs_and_ins_list;
522
523
524 end ame_dynamic_approval_pkg;