[Home] [Help]
PACKAGE BODY: APPS.QPR_DEAL_APPROVALS_PVT
Source
1 PACKAGE BODY QPR_DEAL_APPROVALS_PVT AS
2 /* $Header: QPRPNAPB.pls 120.22 2008/05/28 12:02:28 amjha ship $ */
3
4 type approval_record_type is record
5 ( approver_id number,
6 approver_name varchar2(320),
7 approval_sequence number,
11 notification_date date,
8 approval_status varchar2(50),
9 rule_id number,
10 rule_description varchar2(100),
12 response_date date,
13 comments varchar2(2000),
14 archive_flag varchar2(1),
15 item_class varchar2(100),
16 item_id varchar2(100));
17
18 type approval_table_type is table of approval_record_type index by binary_integer;
19
20 type id_list is table of number index by binary_integer;
21
22 g_appl_id number := null;
23
24 g_transaction_type_id varchar2(50) := 'oracle.apps.qpr';
25 --g_transaction_type_id varchar2(50) := 'Deal-Approval';
26
27
28 procedure clear_existing_approvals(p_response_header_id in number,
29 p_user_id in number);
30
31 procedure synch_with_db(p_response_header_id in number,
32 x_approvals_tab in out nocopy approval_table_type);
33
34 procedure get_ame_approvals(p_response_header_id in number,
35 x_approvals_tab out nocopy approval_table_type,
36 x_approvals_complete out nocopy varchar2);
37
38 procedure mark_ame_notified(p_response_header_id in number,
39 x_notified_approvals_tab out nocopy approval_table_type);
40
41 procedure update_ame_status(p_response_header_id in number,
42 p_user_id in number,
43 p_status in varchar2,
44 x_prior_approvals_tab out nocopy approval_table_type,
45 x_approvals_complete out nocopy varchar2);
46
47 procedure insert_approval_records(p_response_header_id in number,
48 p_approvals_tab in approval_table_type);
49
50 procedure insert_archive_record(p_response_header_id in number,
51 p_user_id in number,
52 p_action in varchar2,
53 p_comments in varchar2);
54
55 procedure update_with_ame(p_response_header_id in number,
56 p_user_id in number,
57 p_action in varchar2);
58
59 function get_approval_record(p_ame_record ame_util.approverRecord2)
60 return approval_record_type;
61
62 function is_approvals_changed(p_response_header_id in number,
63 p_ame_approvals_tab in approval_table_type)
64 return boolean;
65
66 function get_approvals_records(p_response_header_id in number,
67 p_user_id in number)
68 return approval_table_type;
69
70 function get_approvals_records(p_response_header_id in number)
71 return approval_table_type;
72
73 function is_action_permitted(p_response_header_id in number,
74 p_user_id in number,
75 p_action_code in varchar2)
76 return boolean;
77
78 function get_application_id
79 return number
80 is
81 begin
82 if g_appl_id is null
83 then
84 select application_id into g_appl_id
85 from fnd_application
86 where application_short_name = 'QPR';
87 end if;
88 return g_appl_id;
89 end get_application_id;
90
91
92 procedure clear_existing_approvals(p_response_header_id in number,
93 p_user_id in number)
94 is
95 begin
96 delete from qpr_pn_response_approvals
97 where response_header_id = p_response_header_id
98 and archive_flag = 'N'
99 and approver_id <> p_user_id;
100 end clear_existing_approvals;
101
102 function get_approval_record(p_ame_record ame_util.approverRecord2)
103 return approval_record_type
104 is
105 l_approver_rec approval_record_type;
106 l_fnd_user_id number;
107
108 cursor fnd_user(c_emp_id number) is
109 select user_id
110 from fnd_user
111 where employee_id = c_emp_id;
112 begin
113
114 --alosh: finding the fnd_user_id
115 if(p_ame_record.orig_system = 'FND_USR')
116 then
117 l_fnd_user_id := p_ame_record.orig_system_id;
118 elsif(p_ame_record.orig_system = 'PER')
119 then
120 open fnd_user(p_ame_record.orig_system_id);
121 fetch fnd_user into l_fnd_user_id;
122 close fnd_user;
123 end if;
124
125 l_approver_rec.approver_id := l_fnd_user_id;
126 l_approver_rec.approver_name := p_ame_record.name;
127 l_approver_rec.approval_sequence := p_ame_record.approver_order_number;
128 l_approver_rec.approval_status := p_ame_record.approval_status;
129 l_approver_rec.archive_flag := 'N';
130 l_approver_rec.item_class := nvl(p_ame_record.item_class, 'NULL');
131 l_approver_rec.item_id := nvl(p_ame_record.item_id, 'NULL');
132
133 return l_approver_rec;
134 end get_approval_record;
135
136 procedure synch_with_db(p_response_header_id in number,
137 x_approvals_tab in out nocopy approval_table_type)
138 is
139
140 cursor get_db_records(c_response_header_id number)
141 is
142 select approver_id,
143 approval_status,
144 rule_id,
145 notification_date,
146 response_date,
147 comments
148 from qpr_pn_response_approvals
149 where response_header_id = c_response_header_id;
150 begin
151
152 if x_approvals_tab.count = 0
153 then
154 return;
155 end if;
156
157 for approval_rec in get_db_records(p_response_header_id)
158 loop
159 for i in x_approvals_tab.first .. x_approvals_tab.last
160 loop
164 x_approvals_tab(i).response_date := approval_rec.response_date;
161 if(approval_rec.approver_id = x_approvals_tab(i).approver_id and approval_rec.rule_id = x_approvals_tab(i).rule_id)
162 then
163 x_approvals_tab(i).notification_date := approval_rec.notification_date;
165 x_approvals_tab(i).comments := approval_rec.comments;
166 exit;
167 end if;
168 end loop;
169 end loop;
170
171 end synch_with_db;
172
173 procedure rebuild_ame_status(p_response_header_id in number)
174 is
175 l_count number;
176 l_notify_flag varchar2(1);
177 l_approvals_complete varchar2(1);
178 l_ame_next_approver_tab ame_util.approversTable2;
179 l_approver_record ame_util.approverRecord2;
180
181 cursor get_active_records(c_resp_id number)
182 is
183 select approver_name, approval_status
184 from qpr_pn_response_approvals
185 where response_header_id = c_resp_id
186 and archive_flag = 'N';
187
188 begin
189
190 select count(*) into l_count
191 from qpr_pn_response_approvals
192 where response_header_id = p_response_header_id
193 and archive_flag = 'N'
194 and approval_status in ('NOTIFIED', 'APPROVE');
195
196 if l_count > 0
197 then
198 l_notify_flag := ame_util.booleanTrue;
199 ame_api2.getNextApprovers4(
200 applicationIdIn => get_application_id(),
201 transactionTypeIn => g_transaction_type_id,
202 transactionIdIn => p_response_header_id,
203 flagApproversAsNotifiedIn => l_notify_flag,
204 approvalProcessCompleteYNOut => l_approvals_complete,
205 nextApproversOut => l_ame_next_approver_tab
206 );
207
208 for rec in get_active_records(p_response_header_id)
209 loop
210
211 l_approver_record.name := rec.approver_name;
212 l_approver_record.approval_status := rec.approval_status;
213 ame_api2.updateApprovalStatus(
214 applicationIdIn => get_application_id(),
215 transactionTypeIn => g_transaction_type_id,
216 transactionIdIn => p_response_header_id,
217 approverIn => l_approver_record);
218
219 end loop;
220 end if;
221 end rebuild_ame_status;
222
223
224 function is_action_permitted(p_response_header_id in number,
225 p_user_id in number,
226 p_action_code in varchar2)
227 return boolean
228
229 is
230 l_status varchar2(50);
231
232 begin
233
234 if p_action_code = 'APPROVE' or p_action_code= 'REJECT'
235 then
236 l_status := null;
237 select approval_status into l_status
238 from qpr_pn_response_approvals
239 where response_header_id = p_response_header_id
240 and approver_id = p_user_id
241 and archive_flag = 'N'
242 and approval_status = 'NOTIFIED';
243
244 if l_status = 'NOTIFIED'
245 then
246 return true;
247 else
248 return false;
249 end if;
250 end if;
251
252 return true;
253 exception
254 when others
255 then
256 return false;
257 end is_action_permitted;
258
259
260
261 procedure get_ame_approvals(p_response_header_id in number,
262 x_approvals_tab out nocopy approval_table_type,
263 x_approvals_complete out nocopy varchar2)
264 is
265 l_transaction_id varchar2(50);
266 l_notify_flag varchar2(1);
267 l_ame_approver_table ame_util.approversTable2;
268 l_ame_next_approver_tab ame_util.approversTable2;
269 l_ame_item_indexes ame_util.idlist;
270 l_ame_item_classes ame_util.stringlist;
271 l_ame_item_ids ame_util.stringlist;
272 l_ame_item_sources ame_util.longstringlist;
273 l_ame_rule_indexes ame_util.idlist;
274 l_ame_source_types ame_util.stringlist;
275 l_ame_rule_ids ame_util.idlist;
276 l_ame_rule_descs ame_util.stringlist;
277 l_approval_rec approval_record_type;
278
279 begin
280
281 l_transaction_id := p_response_header_id;
282 l_notify_flag := ame_util.booleanFalse;
283
284
285
286 ame_api2.getAllApprovers6(
287 applicationIdIn => get_application_id(),
288 transactionTypeIn => g_transaction_type_id,
289 transactionIdIn => l_transaction_id,
290 approvalProcessCompleteYNOut => x_approvals_complete,
291 approversOut => l_ame_approver_table,
292 itemIndexesOut => l_ame_item_indexes,
293 itemClassesOut => l_ame_item_classes,
294 itemIdsOut => l_ame_item_ids,
295 itemSourcesOut => l_ame_item_sources,
296 ruleIndexesOut => l_ame_rule_indexes,
297 sourceTypesOut => l_ame_source_types,
298 ruleIdsOut => l_ame_rule_ids,
299 ruleDescriptionsOut => l_ame_rule_descs
300 );
301
302 if(l_ame_approver_table.count > 0)
303 then
304
305 for i in l_ame_approver_table.first .. l_ame_approver_table.last
306 loop
307
308 l_approval_rec := get_approval_record(l_ame_approver_table(i));
309 l_approval_rec.rule_id := l_ame_rule_ids(i);
310 l_approval_rec.rule_description := l_ame_rule_descs(i);
311 x_approvals_tab(i) := l_approval_rec;
312
313 end loop;
314 end if;
315
316 ame_api2.getNextApprovers4(
317 applicationIdIn => get_application_id(),
318 transactionTypeIn => g_transaction_type_id,
322 nextApproversOut => l_ame_next_approver_tab
319 transactionIdIn => l_transaction_id,
320 flagApproversAsNotifiedIn => l_notify_flag,
321 approvalProcessCompleteYNOut => x_approvals_complete,
323 );
324
325 if(l_ame_next_approver_tab.count > 0)
326 then
327
328 for i in l_ame_next_approver_tab.first .. l_ame_next_approver_tab.last
329 loop
330
331 for j in x_approvals_tab.first .. x_approvals_tab.last
332 loop
333
334 if l_ame_next_approver_tab(i).name = x_approvals_tab(j).approver_name
335 then
336 if x_approvals_tab(j).approval_status is null
337 then
338 x_approvals_tab(j).approval_status := 'PENDING_APPROVAL';
339 end if;
340 exit;
341 end if;
342
343 end loop;
344
345 end loop;
346
347 end if;
348
349 --alosh: update ame records with current status and date
350 synch_with_db(p_response_header_id => p_response_header_id,
351 x_approvals_tab => x_approvals_tab);
352
353
354 end get_ame_approvals;
355
356
357 procedure mark_ame_notified(p_response_header_id in number,
358 x_notified_approvals_tab out nocopy approval_table_type)
359 is
360 l_transaction_id varchar2(50);
361 l_notify_flag varchar2(1);
362 l_approvals_complete varchar2(1);
363 l_ame_approver_table ame_util.approversTable2;
364 l_count number;
365
366 begin
367
368 l_transaction_id := p_response_header_id;
369 l_notify_flag := ame_util.booleanFalse;
370
371 ame_api2.getNextApprovers4(
372 applicationIdIn => get_application_id(),
373 transactionTypeIn => g_transaction_type_id,
374 transactionIdIn => l_transaction_id,
375 flagApproversAsNotifiedIn => l_notify_flag,
376 approvalProcessCompleteYNOut => l_approvals_complete,
377 nextApproversOut => l_ame_approver_table
378 );
379
380 l_count := 0;
381
382 if(l_ame_approver_table.count > 0)
383 then
384
385 for i in l_ame_approver_table.first .. l_ame_approver_table.last
386 loop
387
388 if l_ame_approver_table(i).approval_status is null
389 then
390 x_notified_approvals_tab(l_count) := get_approval_record(l_ame_approver_table(i));
391 l_count := l_count + 1;
392 end if;
393
394 end loop;
395 end if;
396
397 l_notify_flag := ame_util.booleanTrue;
398 ame_api2.getNextApprovers4(
399 applicationIdIn => get_application_id(),
400 transactionTypeIn => g_transaction_type_id,
401 transactionIdIn => l_transaction_id,
402 flagApproversAsNotifiedIn => l_notify_flag,
403 approvalProcessCompleteYNOut => l_approvals_complete,
404 nextApproversOut => l_ame_approver_table
405 );
406
407 end mark_ame_notified;
408
409 procedure update_ame_status(p_response_header_id in number,
410 p_user_id in number,
411 p_status in varchar2,
412 x_prior_approvals_tab out nocopy approval_table_type,
413 x_approvals_complete out nocopy varchar2)
414 is
415 l_ame_approver_table ame_util.approversTable2;
416 l_ame_item_indexes ame_util.idlist;
417 l_ame_item_classes ame_util.stringlist;
418 l_ame_item_ids ame_util.stringlist;
419 l_ame_item_sources ame_util.longstringlist;
420 l_ame_rule_indexes ame_util.idlist;
421 l_ame_source_types ame_util.stringlist;
422 l_ame_rule_ids ame_util.idlist;
423 l_ame_rule_descs ame_util.stringlist;
424 l_approval_rec approval_record_type;
425 l_approvals_tab approval_table_type;
426 l_user_name varchar2(320);
427 l_approver_record ame_util.approverRecord2;
428
429 l_index number;
430 l_rule_ids id_list;
431 l_rule_count number;
432 l_prior_approvals_index number;
433
434 begin
435
436 select distinct approver_name
437 into l_user_name
438 from qpr_pn_response_approvals
439 where response_header_id = p_response_header_id
440 and approver_id = p_user_id;
441
442 l_approver_record.name := l_user_name;
443
444 if p_status is not null
445 then
446 l_approver_record.approval_status := 'NOTIFIED';
447 ame_api2.updateApprovalStatus(
448 applicationIdIn => get_application_id(),
449 transactionTypeIn => g_transaction_type_id,
450 transactionIdIn => p_response_header_id,
451 approverIn => l_approver_record);
452 end if;
453 l_approver_record.approval_status := p_status;
454
455 ame_api2.updateApprovalStatus(
456 applicationIdIn => get_application_id(),
457 transactionTypeIn => g_transaction_type_id,
458 transactionIdIn => p_response_header_id,
459 approverIn => l_approver_record);
460
461
462 ame_api2.getAllApprovers6(
463 applicationIdIn => get_application_id(),
464 transactionTypeIn => g_transaction_type_id,
465 transactionIdIn => p_response_header_id,
466 approvalProcessCompleteYNOut => x_approvals_complete,
467 approversOut => l_ame_approver_table,
468 itemIndexesOut => l_ame_item_indexes,
469 itemClassesOut => l_ame_item_classes,
470 itemIdsOut => l_ame_item_ids,
471 itemSourcesOut => l_ame_item_sources,
472 ruleIndexesOut => l_ame_rule_indexes,
473 sourceTypesOut => l_ame_source_types,
477
474 ruleIdsOut => l_ame_rule_ids,
475 ruleDescriptionsOut => l_ame_rule_descs
476 );
478 if(l_ame_approver_table.count > 0)
479 then
480
481 for i in l_ame_approver_table.first .. l_ame_approver_table.last
482 loop
483
484 l_approval_rec := get_approval_record(l_ame_approver_table(i));
485 l_approval_rec.rule_id := l_ame_rule_ids(i);
486 l_approval_rec.rule_description := l_ame_rule_descs(i);
487 l_approvals_tab(i) := l_approval_rec;
488
489 end loop;
490 end if;
491
492
493 l_index := l_approvals_tab.last;
494 l_rule_count := 0;
495 l_prior_approvals_index := 0;
496 loop
497 if(not l_approvals_tab.exists(l_index))
498 then
499 exit;
500 end if;
501
502 if l_approvals_tab(l_index).approver_name = l_user_name
503 then
504 l_rule_ids(l_rule_count) := l_approvals_tab(l_index).rule_id;
505 l_rule_count := l_rule_count + 1;
506 elsif l_approvals_tab(l_index).approval_status = 'APPROVE'
507 then
508 if l_rule_ids is not null and l_rule_ids.count > 0
509 then
510 for i in l_rule_ids.first .. l_rule_ids.last
511 loop
512 if l_approvals_tab(l_index).rule_id = l_rule_ids(i)
513 then
514 x_prior_approvals_tab(l_prior_approvals_index) := l_approvals_tab(l_index);
515 l_prior_approvals_index := l_prior_approvals_index +1;
516 exit;
517 end if;
518 end loop;
519 end if;
520 end if;
521 l_index := l_index - 1;
522 end loop;
523
524 end update_ame_status;
525
526
527
528 procedure clear_ame_status(p_response_header_id in number,
529 p_user_id in number,
530 x_cleared_approvals_tab out nocopy approval_table_type)
531 is
532 l_ame_approver_table ame_util.approversTable2;
533 l_ame_rec ame_util.approverRecord2;
534 l_ame_item_indexes ame_util.idlist;
535 l_ame_item_classes ame_util.stringlist;
536 l_ame_item_ids ame_util.stringlist;
537 l_ame_item_sources ame_util.longstringlist;
538 l_ame_rule_indexes ame_util.idlist;
539 l_ame_source_types ame_util.stringlist;
540 l_ame_rule_ids ame_util.idlist;
541 l_ame_rule_descs ame_util.stringlist;
542 l_rule_id number;
543 l_item_class varchar2(100);
544 l_item_id varchar2(100);
545 l_approvals_complete varchar2(1);
546 l_user_name varchar2(320);
547 l_count number;
548
549 begin
550
551 ame_api2.getAllApprovers6(
552 applicationIdIn => get_application_id(),
553 transactionTypeIn => g_transaction_type_id,
554 transactionIdIn => p_response_header_id,
555 approvalProcessCompleteYNOut => l_approvals_complete,
556 approversOut => l_ame_approver_table,
557 itemIndexesOut => l_ame_item_indexes,
558 itemClassesOut => l_ame_item_classes,
559 itemIdsOut => l_ame_item_ids,
560 itemSourcesOut => l_ame_item_sources,
561 ruleIndexesOut => l_ame_rule_indexes,
562 sourceTypesOut => l_ame_source_types,
563 ruleIdsOut => l_ame_rule_ids,
564 ruleDescriptionsOut => l_ame_rule_descs
565 );
566
567 if(l_ame_approver_table.count > 0)
568 then
569
570 l_count := 0;
571
572 for i in l_ame_approver_table.first .. l_ame_approver_table.last
573 loop
574
575 l_ame_rec := l_ame_approver_table(i);
576 l_ame_rec.approval_status := null;
577 x_cleared_approvals_tab(l_count) := get_approval_record(l_ame_rec);
578 l_count := l_count +1;
579
580 ame_api2.updateApprovalStatus(
581 applicationIdIn => get_application_id(),
582 transactionTypeIn => g_transaction_type_id,
583 transactionIdIn => p_response_header_id,
584 approverIn => l_ame_rec);
585 end loop;
586
587 end if;
588 ame_api2.clearAllApprovals(
589 applicationIdIn => get_application_id(),
590 transactionTypeIn => g_transaction_type_id,
591 transactionIdIn => p_response_header_id);
592
593
594 end clear_ame_status;
595
596 function is_approvals_changed(p_response_header_id in number,
597 p_ame_approvals_tab in approval_table_type)
598 return boolean
599 is
600 l_approvals_changed boolean;
601 l_db_records approval_table_type;
602 l_count number;
603
604 begin
605
606 l_approvals_changed := false;
607 l_db_records := get_approvals_records(p_response_header_id => p_response_header_id);
608
609 if p_ame_approvals_tab.count <> l_db_records.count
610 then
611 l_approvals_changed := true;
612 return l_approvals_changed;
613 end if;
614
615
616 if p_ame_approvals_tab.count > 0
617 then
618
619 l_count := l_db_records.first;
620
621 for i in p_ame_approvals_tab.first .. p_ame_approvals_tab.last
622 loop
623
624 if p_ame_approvals_tab(i).approver_id <> l_db_records(l_count).approver_id or
625 p_ame_approvals_tab(i).approval_sequence <> l_db_records(l_count).approval_sequence or
626 p_ame_approvals_tab(i).approval_status <> l_db_records(l_count).approval_status or
627 p_ame_approvals_tab(i).rule_id <> l_db_records(l_count).rule_id or
628 p_ame_approvals_tab(i).item_class <> l_db_records(l_count).item_class or
629 p_ame_approvals_tab(i).item_id <> l_db_records(l_count).item_id
630 then
634 end if;
631
632 l_approvals_changed := true;
633 return l_approvals_changed;
635
636 l_count := l_count +1;
637 end loop;
638
639 end if;
640
641 return l_approvals_changed;
642
643 exception
644 when others
645 then
646 l_approvals_changed := true;
647 return l_approvals_changed;
648
649 end is_approvals_changed;
650
651
652
653
654
655 procedure insert_approval_records(p_response_header_id in number,
656 p_approvals_tab in approval_table_type)
657 is
658 l_current_date date;
659 l_user_id number;
660 l_login_id number;
661 l_approver_record approval_record_type;
662 l_approval_transaction_id number;
663
664 begin
665
666 l_current_date := sysdate;
667 l_user_id := fnd_global.user_id;
668 l_login_id := fnd_global.conc_login_id;
669
670 if(p_approvals_tab.count >0)
671 then
672 for i in p_approvals_tab.first .. p_approvals_tab.last
673 loop
674
675 l_approver_record := p_approvals_tab(i);
676 select qpr_pn_response_approvals_s.nextval into l_approval_transaction_id from dual;
677 insert into qpr_pn_response_approvals (
678 "APPROVAL_TRANSACTION_ID",
679 "RESPONSE_HEADER_ID",
680 "APPROVAL_SEQUENCE",
681 "APPROVER_ID",
682 "APPROVER_NAME",
683 "APPROVAL_STATUS",
684 "RULE_ID",
685 "RULE_DESCRIPTION",
686 "NOTIFICATION_DATE",
687 "RESPONSE_DATE",
688 "COMMENTS",
689 "ARCHIVE_FLAG",
690 "ITEM_CLASS",
691 "ITEM_ID",
692 "CREATION_DATE",
693 "CREATED_BY",
694 "LAST_UPDATE_DATE",
695 "LAST_UPDATED_BY",
696 "LAST_UPDATE_LOGIN")
697 values (
698 l_approval_transaction_id,
699 p_response_header_id,
700 l_approver_record.approval_sequence,
701 l_approver_record.approver_id,
702 l_approver_record.approver_name,
703 l_approver_record.approval_status,
704 l_approver_record.rule_id,
705 l_approver_record.rule_description,
706 l_approver_record.notification_date,
707 l_approver_record.response_date,
708 l_approver_record.comments,
709 l_approver_record.archive_flag,
710 l_approver_record.item_class,
711 l_approver_record.item_id,
712 l_current_date,
713 l_user_id,
714 l_current_date,
715 l_user_id,
716 l_login_id);
717 end loop;
718 end if;
719
720 end insert_approval_records;
721
722 function get_approvals_records(p_response_header_id in number,
723 p_user_id in number)
724 return approval_table_type
725 is
726
727 l_approval_tab approval_table_type;
728 l_count number;
729
730 cursor get_approvals_cursor(c_response_header_id number,
731 c_user_id number)
732 is
733 select APPROVAL_SEQUENCE,
734 APPROVER_ID,
735 APPROVER_NAME,
736 APPROVAL_STATUS,
737 RULE_ID,
738 RULE_DESCRIPTION,
739 NOTIFICATION_DATE,
740 RESPONSE_DATE,
741 COMMENTS,
742 ARCHIVE_FLAG,
743 ITEM_CLASS,
744 ITEM_ID
745 from qpr_pn_response_approvals
746 where response_header_id = c_Response_header_id
747 and approver_id = c_user_id
748 and archive_flag = 'N';
749
750 begin
751
752 l_count := 0;
753 for approval_rec in get_approvals_cursor(c_response_header_id => p_response_header_id,
754 c_user_id => p_user_id)
755 loop
756 l_approval_tab(l_count).approval_sequence := approval_rec.approval_sequence;
757 l_approval_tab(l_count).approver_id := approval_rec.approver_id;
758 l_approval_tab(l_count).approver_name := approval_rec.approver_name;
759 l_approval_tab(l_count).approval_status := approval_rec.approval_status;
760 l_approval_tab(l_count).rule_id := approval_rec.rule_id;
761 l_approval_tab(l_count).rule_description := approval_rec.rule_description;
762 l_approval_tab(l_count).notification_date := approval_rec.notification_date;
763 l_approval_tab(l_count).response_date := approval_rec.response_date;
764 l_approval_tab(l_count).comments := approval_rec.comments;
765 l_approval_tab(l_count).archive_flag := approval_rec.archive_flag;
766 l_approval_tab(l_count).item_class := approval_rec.item_class;
767 l_approval_tab(l_count).item_id := approval_rec.item_id;
768
769 l_count := l_count+1;
770 end loop;
771
772 return l_approval_tab;
773 exception
774 when others
775 then
776 return l_approval_tab;
777
778 end get_approvals_records;
779
780
781 function get_approvals_records(p_response_header_id in number)
782 return approval_table_type
783 is
784
785 l_approval_tab approval_table_type;
786 l_count number;
787
788 cursor get_approvals_cursor(c_response_header_id number)
789 is
790 select APPROVAL_SEQUENCE,
791 APPROVER_ID,
792 APPROVER_NAME,
793 APPROVAL_STATUS,
794 RULE_ID,
795 RULE_DESCRIPTION,
796 NOTIFICATION_DATE,
797 RESPONSE_DATE,
798 COMMENTS,
799 ARCHIVE_FLAG,
800 ITEM_CLASS,
804 and archive_flag = 'N'
801 ITEM_ID
802 from qpr_pn_response_approvals
803 where response_header_id = c_Response_header_id
805 order by approval_transaction_id;
806
807 begin
808
809 l_count := 0;
810 for approval_rec in get_approvals_cursor(c_response_header_id => p_response_header_id)
811 loop
812 l_approval_tab(l_count).approval_sequence := approval_rec.approval_sequence;
813 l_approval_tab(l_count).approver_id := approval_rec.approver_id;
814 l_approval_tab(l_count).approver_name := approval_rec.approver_name;
815 l_approval_tab(l_count).approval_status := approval_rec.approval_status;
816 l_approval_tab(l_count).rule_id := approval_rec.rule_id;
817 l_approval_tab(l_count).rule_description := approval_rec.rule_description;
818 l_approval_tab(l_count).notification_date := approval_rec.notification_date;
819 l_approval_tab(l_count).response_date := approval_rec.response_date;
820 l_approval_tab(l_count).comments := approval_rec.comments;
821 l_approval_tab(l_count).archive_flag := approval_rec.archive_flag;
822 l_approval_tab(l_count).item_class := approval_rec.item_class;
823 l_approval_tab(l_count).item_id := approval_rec.item_id;
824
825 l_count := l_count+1;
826 end loop;
827
828 return l_approval_tab;
829
830 end get_approvals_records;
831
832
833 procedure insert_archive_record(p_response_header_id in number,
834 p_user_id in number,
835 p_action in varchar2,
836 p_comments in varchar2)
837 is
838 l_approval_tab approval_table_type;
839 l_approval_rec approval_record_type;
840 l_archive_tab approval_table_type;
841
842 begin
843
844 l_approval_tab := get_approvals_records(p_response_header_id, p_user_id);
845 if l_approval_tab.count > 0
846 then
847 l_approval_rec := l_approval_tab(0);
848 else
849 l_approval_rec.approver_id := p_user_id;
850 select user_name into l_approval_rec.approver_name
851 from fnd_user
852 where user_id = p_user_id;
853 end if;
854 l_approval_rec.approval_sequence := -1;
855 l_approval_rec.approval_status := p_action;
856 l_approval_rec.response_date := sysdate;
857 l_approval_rec.archive_flag := 'Y';
858 l_approval_rec.comments := p_comments;
859
860 l_archive_tab(0) := l_approval_rec;
861
862 insert_approval_records(p_response_header_id => p_response_header_id,
863 p_approvals_tab => l_archive_tab);
864
865 end insert_archive_record;
866
867 procedure update_with_ame(p_response_header_id in number,
868 p_user_id in number,
869 p_action in varchar2)
870 is
871 l_ame_approver_table ame_util.approversTable2;
872 l_ame_item_indexes ame_util.idlist;
873 l_ame_item_classes ame_util.stringlist;
874 l_ame_item_ids ame_util.stringlist;
875 l_ame_item_sources ame_util.longstringlist;
876 l_ame_rule_indexes ame_util.idlist;
877 l_ame_source_types ame_util.stringlist;
878 l_ame_rule_ids ame_util.idlist;
879 l_ame_rule_descs ame_util.stringlist;
880 l_approval_rec approval_record_type;
881 l_approvals_tab approval_table_type;
882 l_approvals_complete varchar2(1);
883
884 begin
885
886 ame_api2.getAllApprovers6(
887 applicationIdIn => get_application_id(),
888 transactionTypeIn => g_transaction_type_id,
889 transactionIdIn => p_response_header_id,
890 approvalProcessCompleteYNOut => l_approvals_complete,
891 approversOut => l_ame_approver_table,
892 itemIndexesOut => l_ame_item_indexes,
893 itemClassesOut => l_ame_item_classes,
894 itemIdsOut => l_ame_item_ids,
895 itemSourcesOut => l_ame_item_sources,
896 ruleIndexesOut => l_ame_rule_indexes,
897 sourceTypesOut => l_ame_source_types,
898 ruleIdsOut => l_ame_rule_ids,
899 ruleDescriptionsOut => l_ame_rule_descs
900 );
901 if(l_ame_approver_table.count > 0)
902 then
903
904 for i in l_ame_approver_table.first .. l_ame_approver_table.last
905 loop
906
907 l_approval_rec := get_approval_record(l_ame_approver_table(i));
908 l_approval_rec.rule_id := l_ame_rule_ids(i);
909 l_approval_rec.rule_description := l_ame_rule_descs(i);
910 l_approvals_tab(i) := l_approval_rec;
911
912 end loop;
913 end if;
914
915
916 if l_approvals_tab.count > 0
917 then
918
919 for i in l_approvals_tab.first .. l_approvals_tab.last
920 loop
921
922 update qpr_pn_response_approvals
923 set approval_status = l_approvals_tab(i).approval_status
924 where response_header_id = p_response_header_id
925 and approver_id = l_approvals_tab(i).approver_id
926 and rule_id = l_approvals_tab(i).rule_id
927 and item_class = l_approvals_tab(i).item_class
928 and item_id = l_approvals_tab(i).item_id
929 and archive_flag = 'N';
930 end loop;
931
932 update qpr_pn_response_approvals
933 set notification_date = sysdate
934 where response_header_id = p_response_header_id
935 and approval_status like 'NOTIFIED%'
936 and notification_date is null
937 and archive_flag = 'N';
938
939 end if;
940
941 if p_action = 'APPROVE' or p_action = 'REJECT'
942 then
943 update qpr_pn_response_approvals
944 set approval_status = p_action
945 where response_header_id = p_response_header_id
946 and approver_id = p_user_id
947 and nvl(approval_status, 'NULL') in ('NOTIFIED', 'NULL')
951
948 and archive_flag = 'N';
949 end if;
950
952 end update_with_ame;
953
954
955
956
957 PROCEDURE INIT_APPROVALS(
958 p_response_header_id IN NUMBER,
959 p_user_id in number,
960 x_approvals_complete OUT NOCOPY VARCHAR2,
961 x_return_status OUT NOCOPY VARCHAR2
962 )
963 IS
964 l_approvals_tab approval_table_type;
965 l_user_present_flag boolean;
966 BEGIN
967
968 x_return_status := FND_API.G_RET_STS_SUCCESS;
969 --alosh: rebuilding ame to counter cases where transactions get reset
970 rebuild_ame_status(p_response_header_id => p_response_header_id);
971
972 --alosh: invoking ame to get the approver list
973 get_ame_approvals(p_response_header_id => p_response_header_id,
974 x_approvals_tab => l_approvals_tab,
975 x_approvals_complete => x_approvals_complete);
976
977 if not is_approvals_changed(p_response_header_id => p_response_header_id,
978 p_ame_approvals_tab => l_approvals_tab)
979 then
980 return;
981 end if;
982
983 l_user_present_flag := false;
984 if l_approvals_tab is not null and l_approvals_tab.count > 0
985 then
986 for i in l_approvals_tab.first .. l_approvals_tab.last
987 loop
988 if l_approvals_tab(i).approver_id = p_user_id
989 then
990 l_user_present_flag := true;
991 end if;
992 end loop;
993 end if;
994
995
996 --alosh: deleting existing active records
997 if l_user_present_flag
998 then
999 clear_existing_approvals(p_response_header_id => p_response_header_id,
1000 p_user_id => -999);
1001 else
1002 clear_existing_approvals(p_response_header_id => p_response_header_id,
1003 p_user_id => p_user_id);
1004 end if;
1005
1006 --alosh: inserting new set of active records
1007 insert_approval_records(p_response_header_id => p_response_header_id,
1008 p_approvals_tab => l_approvals_tab);
1009
1010 --alosh: commit thru UI
1011 --commit;
1012
1013 EXCEPTION
1014 WHEN OTHERS THEN
1015
1016 --alosh: rollback thru UI
1017 --rollback;
1018 x_return_status := FND_API.G_RET_STS_ERROR;
1019 raise;
1020 END INIT_APPROVALS;
1021
1022 procedure send_notifications(
1023 p_response_header_id in number,
1024 p_approvals_tab in approval_table_type,
1025 p_notification_type in varchar2,
1026 p_comments in varchar2)
1027 is
1028 l_retcode number;
1029 l_errbuf varchar2(2000);
1030 l_user_list qpr_wkfl_util.char_type;
1031 l_user_list_cancel qpr_wkfl_util.char_type;
1032 l_requestor varchar2(50);
1033 l_requestor_present_flag boolean;
1034 l_increment number;
1035 cursor get_requestor_cancel(p_respone_header_id number)
1036 is
1037 select approver_name
1038 from qpr_pn_response_approvals
1039 where approval_status = 'NOTIFIED'
1040 and archive_flag = 'N'
1041 and response_header_id = p_response_header_id;
1042
1043 cursor get_requestor(p_respone_header_id number)
1044 is
1045 select approver_name
1046 from qpr_pn_response_approvals
1047 where approval_status = 'SUBMIT'
1048 and archive_flag = 'Y'
1049 and response_header_id = p_response_header_id
1050 order by last_update_date desc;
1051 begin
1052
1053 open get_requestor(p_response_header_id);
1054 fetch get_requestor into l_requestor;
1055 close get_requestor;
1056
1057 l_requestor_present_flag := false;
1058
1059 if p_approvals_tab is not null and p_approvals_tab.count > 0
1060 then
1061
1062 for i in p_approvals_tab.first .. p_approvals_tab.last
1063 loop
1064 l_user_list(i) := p_approvals_tab(i).approver_name;
1065 if l_user_list(i) = l_requestor
1066 then
1067 l_requestor_present_flag := true;
1068 end if;
1069 end loop;
1070 end if;
1071
1072
1073 if l_requestor_present_flag = false
1074 then
1075 if p_notification_type = 'APPROVE' or p_notification_type = 'REJECT'
1076 then
1077 if l_user_list is not null and l_user_list.count > 0
1078 then
1079 l_user_list(l_user_list.last+1) := l_requestor;
1080 else
1081 l_user_list(0) := l_requestor;
1082 end if;
1083 end if;
1084 end if;
1085
1086 if l_user_list is not null and l_user_list.count > 0
1087 then
1088
1089 if p_notification_type = 'SUBMIT'
1090 then
1091
1092 for i in l_user_list.first .. l_user_list.last
1093 loop
1094 qpr_wkfl_util.invoke_toapp_nfn_process(
1095 p_response_id => p_response_header_id,
1096 p_fwd_to_user => l_user_list(i),
1097 retcode => l_retcode,
1098 errbuf => l_errbuf);
1099 end loop;
1100
1101 elsif p_notification_type = 'CANCEL'
1102 then
1103
1104 l_increment := 0;
1105 for requestor_record in get_requestor_cancel(p_response_header_id)
1106 loop
1107 l_user_list_cancel(l_increment) := requestor_record.approver_name;
1108 l_increment := l_increment + 1;
1109 end loop;
1113
1110
1111 --pp_debug('amitl_increment = '||l_increment||'response_header_id = '||p_response_header_id);
1112
1114 qpr_wkfl_util.cancel_toapp_nfn_process(
1115 p_response_id => p_response_header_id,
1116 p_usr_list => l_user_list_cancel,
1117 retcode => l_retcode,
1118 errbuf => l_errbuf);
1119
1120
1121 qpr_wkfl_util.invoke_cb_nfn_process(
1122 p_response_id => p_response_header_id,
1123 p_usr_list => l_user_list,
1124 p_comments => p_comments,
1125 retcode => l_retcode,
1126 errbuf => l_errbuf);
1127 else
1128
1129 qpr_wkfl_util.invoke_appstat_nfn_process(
1130 p_response_id => p_response_header_id,
1131 p_usr_list => l_user_list,
1132 p_comments => p_comments,
1133 p_status => p_notification_type,
1134 retcode => l_retcode,
1135 errbuf => l_errbuf);
1136 end if;
1137 end if;
1138
1139 end send_notifications;
1140
1141 procedure process_user_action(p_response_header_id in number,
1142 p_user_id in number,
1143 p_action_code in varchar2,
1144 p_comments in varchar2,
1145 p_standalone_call in boolean default false,
1146 x_approvals_complete out nocopy varchar2,
1147 x_return_status out nocopy varchar2)
1148 is
1149 l_approval_tab approval_table_type;
1150 l_temp_approval_tab approval_table_type;
1151
1152 begin
1153
1154 x_return_status := fnd_api.g_ret_sts_success;
1155
1156 if not is_action_permitted(p_response_header_id => p_response_header_id,
1157 p_user_id => p_user_id,
1158 p_action_code => p_action_code)
1159 then
1160 return;
1161 end if;
1162
1163 --alosh: update ame status
1164 if p_action_code = 'APPROVE' or p_action_code = 'REJECT'
1165 then
1166 update_ame_status(p_response_header_id => p_response_header_id,
1167 p_user_id => p_user_id,
1168 p_status => p_action_code,
1169 x_prior_approvals_tab => l_approval_tab,
1170 x_approvals_complete => x_approvals_complete);
1171
1172 elsif p_action_code = 'CANCEL'
1173 then
1174 clear_ame_status(p_response_header_id => p_response_header_id,
1175 p_user_id => p_user_id,
1176 x_cleared_approvals_tab => l_approval_tab);
1177 end if;
1178
1179
1180 --alosh: process next approvers
1181 if p_action_code = 'SUBMIT' or p_action_code = 'APPROVE'
1182 then
1183 --alosh: update ame as notified
1184 mark_ame_notified(
1185 p_response_header_id => p_response_header_id,
1186 x_notified_approvals_tab => l_temp_approval_tab);
1187
1188 --alosh: send approval notifications
1189 send_notifications(
1190 p_response_header_id => p_response_header_id,
1191 p_approvals_tab => l_temp_approval_tab,
1192 p_notification_type => 'SUBMIT',
1193 p_comments => null);
1194 elsif p_action_code = 'CANCEL'
1195 then
1196 send_notifications(
1197 p_response_header_id => p_response_header_id,
1198 p_approvals_tab => l_temp_approval_tab,
1199 p_notification_type => 'CANCEL',
1200 p_comments => null);
1201
1202 end if;
1203
1204 --alosh: send fyi notifications
1205 if p_action_code = 'APPROVE' or p_action_code = 'REJECT' or p_action_code = 'CANCEL'
1206 then
1207 send_notifications(
1208 p_response_header_id => p_response_header_id,
1209 p_approvals_tab => l_approval_tab,
1210 p_notification_type => p_action_code,
1211 p_comments => p_comments);
1212 end if;
1213
1214 --alosh: insert archive record
1215 insert_archive_record(p_response_header_id => p_response_header_id,
1216 p_user_id => p_user_id,
1217 p_action => p_action_code,
1218 p_comments => p_comments);
1219
1220 --alosh: update tables
1221 update_with_ame(p_response_header_id => p_response_header_id,
1222 p_user_id => p_user_id,
1223 p_action => p_action_code);
1224
1225 if p_standalone_call
1226 then
1227 if p_action_code = 'REJECT'
1228 then
1229 update qpr_pn_response_hdrs
1230 set response_status = 'REJECT'
1231 where response_header_id = p_response_header_id;
1232 elsif p_action_code = 'APPROVE' and x_approvals_complete = 'Y'
1233 then
1234 update qpr_pn_response_hdrs
1235 set response_status = 'PEND_ACCEPT_APPROVE'
1236 where response_header_id = p_response_header_id;
1237 end if;
1238
1239 commit;
1240 end if;
1241 exception
1242 when others
1243 then
1244 if p_standalone_call
1245 then
1246 rollback;
1247 end if;
1248 x_return_status := fnd_api.g_ret_sts_error;
1249 raise;
1250 end process_user_action;
1251
1252
1253
1254 procedure process_user_action(p_response_header_id in number,
1255 p_user_name in varchar2,
1256 p_action_code in varchar2,
1257 p_comments in varchar2,
1258 p_standalone_call in boolean default false,
1259 x_approvals_complete out nocopy varchar2,
1260 x_return_status out nocopy varchar2)
1261 is
1262 l_user_id number;
1263 begin
1264
1265 --alosh: get user id
1269 where response_header_id = p_response_header_id
1266 select distinct approver_id
1267 into l_user_id
1268 from qpr_pn_response_approvals
1270 and approver_name = p_user_name
1271 and archive_flag = 'N';
1272
1273 --alosh: call process_user_action
1274 process_user_action(p_response_header_id => p_response_header_id,
1275 p_user_id => l_user_id,
1276 p_action_code=> p_action_code,
1277 p_comments => p_comments,
1278 p_standalone_call => p_standalone_call,
1279 x_approvals_complete => x_approvals_complete,
1280 x_return_status => x_return_status);
1281 exception
1282 when others then
1283 x_return_status := fnd_api.g_ret_sts_error;
1284 raise;
1285 end process_user_action;
1286
1287
1288
1289 procedure synch_approvals(
1290 p_original_response_id in number,
1291 p_new_response_id in number,
1292 x_return_status out nocopy varchar2
1293 )
1294 is
1295
1296 cursor get_all_approvals_cursor(c_response_header_id number)
1297 is
1298 select APPROVAL_SEQUENCE,
1299 APPROVER_ID,
1300 APPROVER_NAME,
1301 APPROVAL_STATUS,
1302 RULE_ID,
1303 RULE_DESCRIPTION,
1304 NOTIFICATION_DATE,
1305 RESPONSE_DATE,
1306 COMMENTS,
1307 ARCHIVE_FLAG,
1308 ITEM_CLASS,
1309 ITEM_ID
1310 from qpr_pn_response_approvals
1311 where response_header_id = c_Response_header_id
1312 order by archive_flag,approval_transaction_id;
1313
1314 l_approvals_complete varchar2(1);
1315 l_ame_rec ame_util.approverRecord2;
1316 l_approvals_tab approval_table_type;
1317 l_count number;
1318
1319 begin
1320
1321 x_return_status := FND_API.G_RET_STS_SUCCESS;
1322
1323 get_ame_approvals(p_response_header_id => p_new_response_id,
1324 x_approvals_tab => l_approvals_tab,
1325 x_approvals_complete => l_approvals_complete);
1326
1327 synch_with_db(p_response_header_id => p_original_response_id,
1328 x_approvals_tab => l_approvals_tab);
1329
1330 l_count := 0;
1331 if l_approvals_tab.count > 0
1332 then
1333 l_count := l_approvals_tab.first;
1334 end if;
1335
1336 for l_approver_record in get_all_approvals_cursor(p_original_response_id)
1337 loop
1338
1339 if(l_approver_record.archive_flag = 'N')
1340 then
1341
1342 l_ame_rec.name := l_approver_record.approver_name;
1343 l_ame_rec.approval_status := l_approver_record.approval_status;
1344
1345 ame_api2.updateApprovalStatus(
1346 applicationIdIn => get_application_id(),
1347 transactionTypeIn => g_transaction_type_id,
1348 transactionIdIn => p_new_response_id,
1349 approverIn => l_ame_rec);
1350 l_approvals_tab(l_count).approval_status := l_approver_record.approval_status;
1351
1352 else
1353
1354 l_approvals_tab(l_count).approver_id := l_approver_record.approver_id;
1355 l_approvals_tab(l_count).approver_name := l_approver_record.approver_name;
1356 l_approvals_tab(l_count).approval_sequence := l_approver_record.approval_sequence;
1357 l_approvals_tab(l_count).approval_status := l_approver_record.approval_status;
1358 l_approvals_tab(l_count).rule_id := l_approver_record.rule_id;
1359 l_approvals_tab(l_count).rule_description := l_approver_record.rule_description;
1360 l_approvals_tab(l_count).notification_date := l_approver_record.notification_date;
1361 l_approvals_tab(l_count).response_date := l_approver_record.response_date;
1362 l_approvals_tab(l_count).comments := l_approver_record.comments;
1363 l_approvals_tab(l_count).archive_flag := l_approver_record.archive_flag;
1364 l_approvals_tab(l_count).item_class := l_approver_record.item_class;
1365 --l_approvals_tab(l_count).item_id := l_approver_record.item_id;
1366
1367 end if;
1368 l_count:=l_count+1;
1369
1370 end loop;
1371
1372 insert_approval_records(p_response_header_id => p_new_response_id,
1373 p_approvals_tab => l_approvals_tab);
1374
1375 exception
1376 when others
1377 then
1378 x_return_status := FND_API.G_RET_STS_ERROR;
1379 raise;
1380
1381 end synch_approvals;
1382
1383
1384 procedure process_stuck_notifications(
1385 p_response_header_id in number,
1386 p_user_id in number,
1387 p_action_code in varchar2,
1388 x_return_status out nocopy varchar2)
1389 is
1390 l_user_name varchar2(100);
1391 l_retcode number;
1392 l_errbuf varchar2(2000);
1393 l_user_list qpr_wkfl_util.char_type;
1394
1395 begin
1396
1397 x_return_status := FND_API.G_RET_STS_SUCCESS;
1398
1399 if p_action_code = 'APPROVE' or p_action_code = 'REJECT'
1400 then
1401
1402 select distinct approver_name into l_user_name
1403 from qpr_pn_response_approvals
1404 where response_header_id = p_response_header_id
1405 and approver_id = p_user_id;
1406
1407 qpr_wkfl_util.complete_toapp_nfn_process(
1408 p_response_id => p_response_header_id,
1409 p_current_user => l_user_name,
1410 p_status => p_action_code,
1411 retcode => l_retcode,
1412 errbuf => l_errbuf);
1413
1414 end if;
1415 EXCEPTION
1416 WHEN OTHERS THEN
1417 x_return_status := FND_API.G_RET_STS_ERROR;
1418 END process_stuck_notifications;
1419
1420
1421 procedure clear_action_history(
1422 p_response_header_id in number,
1423 p_user_id in number,
1424 p_action_code in varchar2,
1425 x_return_status out nocopy varchar2)
1426 is
1427
1428 begin
1429
1430 delete from qpr_pn_response_approvals
1431 where approval_transaction_id = (
1432 select max(approval_transaction_id)
1433 from qpr_pn_response_approvals
1434 where response_header_id = p_response_header_id
1435 and approver_id = p_user_id
1436 and approval_status = p_action_code
1437 and archive_flag = 'Y');
1438 end clear_action_history;
1439
1440
1441
1442 PROCEDURE CHECK_COMPLIANCE(
1443 p_response_header_id IN NUMBER,
1444 o_comply out nocopy varchar2,
1445 o_rules_desc out nocopy varchar2,
1446 x_return_status OUT NOCOPY VARCHAR2
1447 )
1448 IS
1449 l_application_id number;
1450 l_transaction_id varchar2(50);
1451 l_approval_complete_flag varchar2(10);
1452 l_ame_approver_table ame_util.approversTable2;
1453 l_itemIndexesOut ame_util.idList;
1454 l_itemClassesOut ame_util.stringList;
1455 l_itemIdsOut ame_util.stringList;
1456 l_itemSourcesOut ame_util.longStringList;
1457 l_ruleIndexesOut ame_util.idList;
1458 l_sourceTypesOut ame_util.stringList;
1459 l_ruleDescriptionsOut ame_util.stringList;
1460 l_rules_desc varchar2(1000);
1461 l_count number;
1462 BEGIN
1463 x_return_status := FND_API.G_RET_STS_SUCCESS;
1464 l_application_id := 667;
1465 l_transaction_id := p_response_header_id;
1466 o_comply := 'N';
1467
1468 ame_api2.getAllApprovers5(
1469 l_application_id,
1470 g_transaction_type_id,
1471 l_transaction_id,
1472 l_approval_complete_flag,
1473 l_ame_approver_table,
1474 l_itemIndexesOut,
1475 l_itemClassesOut,
1476 l_itemIdsOut,
1477 l_itemSourcesOut,
1478 l_ruleIndexesOut,
1479 l_sourceTypesOut,
1480 l_ruleDescriptionsOut);
1481 /* applicationIdIn in number,
1482 transactionTypeIn in varchar2,
1483 transactionIdIn in varchar2,
1484 approvalProcessCompleteYNOut out nocopy varchar2,
1485 approversOut out nocopy ame_util.approversTable2,
1486 itemIndexesOut out nocopy ame_util.idList,
1487 itemClassesOut out nocopy ame_util.stringList,
1488 itemIdsOut out nocopy ame_util.stringList,
1489 itemSourcesOut out nocopy ame_util.longStringList,
1490 ruleIndexesOut out nocopy ame_util.idList,
1491 sourceTypesOut out nocopy ame_util.stringList,
1492 ruleDescriptionsOut out nocopy ame_util.stringList)*/
1493 if(l_ame_approver_table.count > 0) then
1494 o_comply := 'N';
1495 l_count := 0;
1496 for i in l_ruleDescriptionsOut.first .. l_ruleDescriptionsOut.last loop
1497 l_rules_desc := l_rules_desc || l_ruleDescriptionsOut(i);
1498 l_count := l_count + 1;
1499 if l_count>9 then
1500 exit;
1501 else
1502 l_rules_desc := l_rules_desc|| ' , ';
1503 end if;
1504 end loop;
1505 o_rules_desc := l_rules_desc;
1506 else
1507 o_comply := 'Y';
1508 end if;
1509 EXCEPTION
1510 WHEN OTHERS THEN
1511 x_return_status := FND_API.G_RET_STS_ERROR;
1512 END;
1513
1514 END QPR_DEAL_APPROVALS_PVT;