[Home] [Help]
PACKAGE BODY: APPS.ASO_PA_APR_INT
Source
1 PACKAGE BODY ASO_PA_APR_INT AS
2 /* $Header: asoipapb.pls 120.0.12020000.12 2013/04/23 15:48:49 rassharm noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_PA_APR_INT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoipapb.pls';
6 g_user_id NUMBER;
7
8
9 PROCEDURE get_all_approvers (
10 p_api_version_number IN NUMBER,
11 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
12 p_commit IN VARCHAR2 := fnd_api.g_false,
13 p_object_id IN NUMBER,
14 p_object_type IN VARCHAR2,
15 p_application_id IN NUMBER,
16 p_object_approval_id IN NUMBER,
17 p_clear_transaction_flag IN VARCHAR2 := fnd_api.g_false,
18 p_data_flag IN VARCHAR2 :='Y',
19 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
20 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
21 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
22 x_approvers_list OUT NOCOPY /* file.sql.39 change */ ASO_PA_APR_PUB.approvers_list_tbl_type,
23 x_rules_list OUT NOCOPY /* file.sql.39 change */ ASO_PA_APR_PUB.rules_list_tbl_type
24 ) IS
25 l_api_name CONSTANT VARCHAR2 (30) := 'GET_ALL_APPROVERS';
26 l_api_version CONSTANT NUMBER := 1.0;
27
28 ruleids ame_util.idlist;
29 l_ruletypeout VARCHAR2 (240);
30 l_conditionidsout ame_util.idlist;
31 m integer:=0;
32 approvers2 ame_util.approversTable2;
33 approverrecord2 ame_util.approverrecord2;
34 tempOrigSystem ame_util.stringType;
35 tempOrigSystemId integer;
36 approvalProcessCompleteYN varchar2(1);
37 itemIndexes ame_util.idList;
38 itemClasses ame_util.stringList;
39 itemIds ame_util.stringList;
40 itemSources ame_util.longStringList;
41 ruleIndexes ame_util.idList;
42 sourceTypes ame_util.stringList;
43 lRuleDescription varchar2(240);
44 l_actionTypeNamesOut ame_util.stringList;
45 l_actionTypeDescriptionsOut ame_util.stringList;
46 l_actionDescriptionsOut ame_util.stringList;
47 l_conditionDescriptionsOut ame_util.longestStringList;
48 l_conditionHasLOVsOut ame_util.charList;
49 l_item_class_id number(15);
50 l_approver_category varchar2(1);
51 l_appr_status_code varchar2(30);
52 l_appr_instance_status varchar2(30);
53
54 /* Cursor to fetch approver category description*/
55 CURSOR c_appr_cat (l_value VARCHAR2 ) is
56 select meaning
57 from fnd_lookups
58 where lookup_type = 'AME_APPROVER_CATEGORY'
59 and lookup_code = l_value
60 and enabled_flag = 'Y';
61
62 /* Cursor to fetch approver type description*/
63 CURSOR c_appr_type (l_value VARCHAR2 ) is
64 select meaning
65 from fnd_lookups
66 where lookup_type = 'FND_WF_ORIG_SYSTEMS'
67 and lookup_code = l_value
68 and enabled_flag = 'Y';
69
70 /* Cursor to fetch Sublist Desc */
71 CURSOR c_appr_sublist (l_value VARCHAR2 ) is
72 select meaning
73 from fnd_lookups
74 where lookup_type = 'AME_SUBLIST_TYPES'
75 and lookup_code = l_value
76 and enabled_flag = 'Y';
77
78 /* Cursor to fetch Action type Desc */
79 cursor c_appr_action (l_value INTEGER) is
80 select USER_ACTION_TYPE_NAME
81 from ame_action_types_vl
82 where ACTION_TYPE_ID = l_value
83 and nvl(end_date,sysdate)>=trunc(sysdate);
84
85 /* Cursor to fetch rule class id and rule category id */
86 cursor c_rules_det (l_application_id number, l_value NUMBER) is
87 select item_class_id,nvl(approver_category,'A') approver_category
88 from ame_rules_v
89 where fnd_application_id=l_application_id
90 and rule_id=l_value;
91
92 /* Cursor to fetch rule class desc */
93 cursor c_itemclass_desc(l_value number) is
94 select USER_ITEM_CLASS_NAME
95 from ame_item_classes_vl
96 where item_class_id=l_value
97 and nvl(end_date,sysdate)>=trunc(sysdate);
98
99 -- PA rassharm
100 /* cursor to fetch approver details */
101 cursor get_approver_details(p_object_appr_id number) is
102 select APPROVAL_DET_ID, APPROVER_PERSON_ID, approver_user_id,APPROVER_SEQUENCE, APPROVER_STATUS, APPROVER_COMMENTS,
103 name,item_class, item_id,action_type_id, group_or_chain_id, occurrence, authority,approver_category
104 from ASO_APR_APPROVAL_DETAILS
105 where object_approval_id = p_object_appr_id
106 order by APPROVAL_DET_ID;
107
108 l_name varchar2(320);
109 l_displayname varchar2(320);
110 j integer:=0;
111
112 BEGIN
113 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
114 aso_debug_pub.ADD (
115 'Begin get_all_approvers',
116 1,
117 'N'
118 );
119 aso_debug_pub.ADD (
120 'Application ID : ' || p_application_id,
121 1,
122 'N'
123 );
124 aso_debug_pub.ADD (
125 'Object ID : ' || p_object_id,
126 1,
127 'N'
128 );
129 aso_debug_pub.ADD (
130 'Object Type : ' || p_object_type,
131 1,
132 'N'
133 );
134 aso_debug_pub.ADD (
135 'Commit Flag : ' || p_commit,
136 1,
137 'N'
138 );
139 aso_debug_pub.ADD (
140 'Data Flag : ' || p_data_flag,
141 1,
142 'N'
143 );
144 END IF;
145 -- Standard call to establisg savepoint .
146
147 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
148 aso_debug_pub.ADD (
149 'Establishing save point GET_ALL_APPROVERS_INT',
150 1,
151 'N'
152 );
153 END IF;
154 SAVEPOINT get_all_approvers_int;
155
156 -- Standard call to check for call compatibility.
157
158 IF NOT fnd_api.compatible_api_call (
159 l_api_version,
160 p_api_version_number,
161 l_api_name,
162 g_pkg_name
163 )
164 THEN
165 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
166 aso_debug_pub.ADD (
167 'GET_ALL_APROVERS api call was not compatible pls check version ',
168 1,
169 'N'
170 );
171 END IF;
172 RAISE fnd_api.g_exc_unexpected_error;
173 END IF;
174
175 -- Initialize message list if p_init_msg_list is set to TRUE
176
177 IF fnd_api.to_boolean (
178 p_init_msg_list
179 )
180 THEN
181 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
182 aso_debug_pub.ADD (
183 'Initializing the message list ',
184 1,
185 'N'
186 );
187 END IF;
188 fnd_msg_pub.initialize;
189 END IF;
190
191 -- Initialize API return status to success
192
193 x_return_status := fnd_api.g_ret_sts_success;
194
195 --pp_debug('p_data_flag'||p_data_flag);
196 --pp_debug('p_object_approval_id'||p_object_approval_id);
197
198 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
199 aso_debug_pub.ADD ('Inside get_all_approvers p_data_flag'||p_data_flag,1,'N');
200 aso_debug_pub.ADD ('Inside get_all_approvers p_object_approval_id'||p_object_approval_id,1,'N');
201 END IF;
202
203 if (p_data_flag='W') or (p_data_flag='N') then
204 if p_object_approval_id is not null then
205 select approval_Status into l_appr_instance_status
206 from aso_apr_obj_approvals
207 where object_approval_id= p_object_approval_id;
208 end if;
209 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
210 aso_debug_pub.ADD ('Inside get_all_approvers approval_Status'||l_appr_instance_status,1,'N');
211 END IF;
212
213
214 end if;
215
216 -- Clear all transactions if the flag is set to true
217
218 IF fnd_api.to_boolean (
219 p_clear_transaction_flag
220 )
221 THEN
222 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
223 aso_debug_pub.ADD (
224 'clearing all previous transactions in AME ',
225 1,
226 'N'
227 );
228 aso_debug_pub.ADD (
229 'Calling AME clearAllApprovals',
230 1,
231 'N'
232 );
233 END IF;
234 ame_api2.clearallapprovals (
235 applicationidin => p_application_id,
236 transactionidin => p_object_id,
237 transactiontypein => p_object_type
238 );
239 commit; -- bug 16699795
240 END IF;
241
242
243
244 if (p_Data_flag='Y') or (p_data_flag='W' and l_appr_instance_status ='PEND') or (p_Data_flag='N' and l_appr_instance_status='PEND') then
245 --pp_debug('Begore call to AME');
246 -- Calling the AME API to get all the approvers -----
247
248 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
249 aso_debug_pub.ADD (
250 'Calling AME get All Approvers Procedure',
251 1,
252 'N'
253 );
254 END IF;
255
256 ame_api2.getAllApprovers4 (
257 applicationIdIn => p_application_id,
258 transactionTypeIn => p_object_type,
259 transactionIdIn => p_object_id,
260 approvalProcessCompleteYNOut => approvalProcessCompleteYN,
261 approversOut => approvers2,
262 itemIndexesOut => itemIndexes,
263 itemClassesOut => itemClasses,
264 itemIdsOut => itemIds,
265 itemSourcesOut => itemSources,
266 ruleIndexesOut => ruleIndexes,
267 sourceTypesOut => sourceTypes,
268 ruleIdsOut => ruleids
269 );
270 else
271 -- fetching the approvers data from ASO tables
272
273
274 j:=0;
275 approvers2:=ame_util.emptyApproversTable2;
276 for c1 in get_approver_details(p_object_approval_id) loop
277
278 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
279 aso_debug_pub.ADD ('get_approver_details: c1.name'||c1.name,1,'N');
280 end if;
281
282
283 If c1.name is null then -- for backward compatibility
284 if c1.approver_user_id is not null then
285 WF_DIRECTORY.GetUserName(ame_util.fndUserOrigSystem, c1.approver_user_id, l_name, l_displayname);
286 else
287 WF_DIRECTORY.GetUserName(ame_util.perOrigSystem, c1.approver_person_id, l_name, l_displayname);
288 if l_name is null then -- POS
289 WF_DIRECTORY.GetUserName(ame_util.posOrigSystem, c1.approver_person_id, l_name, l_displayname);
290 end if;
291 end if;
292 else
293 l_name:=c1.name;
294 end if;
295
296 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
297 aso_debug_pub.ADD ('l_name'||l_name,1,'N');
298 end if;
299 approverrecord2:=ame_util.emptyApproverRecord2;
300 j:=j+1;
301 approverrecord2.name:=c1.name;
302 ame_approver_type_pkg.getApproverOrigSystemAndId(nameIn =>l_name,
303 origSystemOut => approverrecord2.Orig_System,
304 origSystemIdOut => approverrecord2.Orig_System_Id);
305 approverrecord2.display_name:=ame_approver_type_pkg.getApproverDisplayName(l_name);
306 approverrecord2.approver_order_number:=c1.approver_sequence;
307 approverrecord2.approver_category:=c1.approver_category;
308 approverrecord2.authority:=c1.authority;
309 approverrecord2.action_type_id:=c1.action_type_id;
310 approverrecord2.item_class:=c1.item_class;
311 approverrecord2.item_id:=c1.item_id;
312 approverrecord2.approval_status:=c1.approver_status;
313 approverrecord2.group_or_chain_id:=c1.group_or_chain_id;
314 approverrecord2.occurrence:=c1.occurrence;
315 approvers2(j):=approverrecord2;
316 end loop;
317
318
319
320
321
322
323 end if;
324
325 --pp_debug( 'Approvers2 Count is ' || approvers2.COUNT);
326 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
327 aso_debug_pub.ADD ( 'Approvers2 Count is ' || approvers2.COUNT, 1, 'N');
328 aso_debug_pub.ADD ( 'approvalProcessCompleteYN =' || approvalProcessCompleteYN, 1, 'N');
329 END IF;
330
331
332 -- Added code to check if it is a self-approval case
333 IF (approvers2.count = 0) THEN
334
335 -- the requester is approver in this case
336 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
337 aso_debug_pub.add('Self approval case -- requester is only approver', 1, 'N');
338 END IF;
339 NULL;
340
341 ELSE
342
343 -- Looping through the PL/SQL Table and assigning values to be passed OUT NOCOPY /* file.sql.39 change */ as
344 -- list of approvers
345
346 FOR i IN 1 .. approvers2.COUNT
347 LOOP
348 m := x_approvers_list.count + 1;
349 if approvers2(i).orig_system is null or
350 approvers2(i).orig_system_id is null
351 then
352 ame_approver_type_pkg.getApproverOrigSystemAndId(nameIn => approvers2(i).name,
353 origSystemOut => tempOrigSystem,
354 origSystemIdOut => tempOrigSystemId);
355 else
356 tempOrigSystem := approvers2(i).orig_system;
357 tempOrigSystemId := approvers2(i).orig_system_id;
358 end if;
359
360 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
361 aso_debug_pub.ADD ('approvers2(i).orig_system'||approvers2(i).orig_system,1,'N' );
362 aso_debug_pub.ADD ('approvers2(i).orig_system_id'||approvers2(i).orig_system_id,1,'N' );
363 aso_debug_pub.ADD ('tempOrigSystem '||tempOrigSystem,1,'N' );
364 aso_debug_pub.ADD ('tempOrigSystemId '||tempOrigSystemId,1,'N' );
365 end if;
366
367 if tempOrigSystem = ame_util.perOrigSystem then
368 x_approvers_list(m).approver_user_id := null;
369 x_approvers_list(m).approver_person_id := tempOrigSystemId;
370
371 elsif tempOrigSystem = ame_util.fndUserOrigSystem then
372 x_approvers_list(m).approver_user_id := tempOrigSystemId;
373 x_approvers_list(m).approver_person_id := null;
374 end if;
375 x_approvers_list (m).approver_sequence:=approvers2(i).approver_order_number;
376
377 x_approvers_list (m).approver_category:= approvers2(i).approver_category;
378
379 x_approvers_list (m).authority:= approvers2(i).authority;
380
381 if (approvers2(i).approver_category IS NOT NULL) then
382 OPEN c_appr_cat (approvers2(i).approver_category);
383 FETCH c_appr_cat INTO x_approvers_list ( m).approver_category_desc;
384 CLOSE c_appr_cat;
385 end if;
386
387 if (tempOrigSystem is not null) then
388 x_approvers_list ( m).approver_type_desc:=ame_approver_type_pkg.getOrigSystemDisplayName(tempOrigSystem);
389 /*OPEN c_appr_type (tempOrigSystem);
390 FETCH c_appr_type INTO x_approvers_list ( m).approver_type_desc;
391 CLOSE c_appr_type;*/
392 end if;
393
394 if (approvers2(i).authority is not null) then
395 OPEN c_appr_sublist (approvers2(i).authority);
396 FETCH c_appr_sublist INTO x_approvers_list ( m).sub_list_desc;
397 CLOSE c_appr_sublist;
398 end if;
399
400 if (approvers2(i).action_type_id is not null) then
401 OPEN c_appr_action (approvers2(i).action_type_id);
402 FETCH c_appr_action INTO x_approvers_list ( m).action_type;
403 CLOSE c_appr_action;
404 end if;
405
406
407 x_approvers_list ( m).approver_name := approvers2(i).display_name;
408 x_approvers_list ( m).name := approvers2(i).name;
409 x_approvers_list ( m).item_class := approvers2(i).item_class;
410 x_approvers_list ( m).item_id := approvers2(i).item_id;
411 x_approvers_list ( m).action_type := AME_ACTION_PKG.getActionTypeName(approvers2(i).action_type_id);
412 x_approvers_list ( m).action_type_id := approvers2(i).action_type_id;
413
414 if (p_Data_flag='Y') or (p_data_flag='W' and l_appr_instance_status ='PEND') or (p_Data_flag='N' and l_appr_instance_status='PEND') then
415 if approvers2(i).approval_status is null then
416 l_appr_status_code:='NOSUBMIT';
417 elsif approvers2(i).approval_status = 'NOTIFIED' then
418 if approvers2(i).approver_category='F' then
419 l_appr_status_code:='NR';
420 else
421 l_appr_status_code:='PEND';
422 end if;
423 elsif approvers2(i).approval_status = 'APPROVE' then
424 l_appr_status_code:='APPR';
425 elsif approvers2(i).approval_status = 'REJECT' then
426 l_appr_status_code:='REJ';
427 elsif approvers2(i).approval_status = 'NO RESPONSE' then
428 l_appr_status_code:='TOUT';
429 elsif approvers2(i).approval_status = 'BEAT BY FIRST RESPONDER' then
430 l_appr_status_code:='NR';
431 elsif approvers2(i).approval_status = 'SUPPRESSED' then
432 l_appr_status_code:='SKIP';
433 else
434 l_appr_status_code:='X';
435 end if;
436 else
437 l_appr_status_code:=approvers2(i).approval_status;
438 end if;
439
440 if l_appr_status_code='X' then
441 x_approvers_list ( m).approver_Status := approvers2(i).approval_status;
442 else
443 Select meaning
444 into x_approvers_list( m).approver_Status
445 from aso_lookups
446 where lookup_type = 'ASO_APPROVER_STATUS'
447 and lookup_code = l_appr_status_code
448 and enabled_flag='Y'
449 And trunc(nvl(start_date_active, sysdate-1)) <= trunc(sysdate)
450 And trunc(nvl(end_date_active, sysdate+1)) >= trunc(sysdate);
451
452 --and sysdate between start_date_active and nvl(end_date_active,sysdate);
453 end if;
454
455
456 x_approvers_list ( m).chain_number := approvers2(i).group_or_chain_id;
457 x_approvers_list ( m).occurrence := approvers2(i).occurrence;
458
459
460 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
461 aso_debug_pub.ADD ( 'Approver Sequence : ' || approvers2(i).approver_order_number, 1, 'N');
462 aso_debug_pub.ADD ( 'Approver category : ' || x_approvers_list (m).approver_category, 1, 'N');
463 aso_debug_pub.ADD ( 'Approver name = ' || approvers2(i).name, 1, 'N');
464 aso_debug_pub.ADD ( 'Approver display name = ' || approvers2(i).display_name, 1, 'N');
465 aso_debug_pub.ADD ( 'item_class = ' || approvers2(i).item_class, 1, 'N');
466 aso_debug_pub.ADD ( 'item_id = ' || approvers2(i).item_id, 1, 'N');
467 aso_debug_pub.ADD ( 'action type = ' || x_approvers_list(m).action_type, 1, 'N');
468 aso_debug_pub.ADD ( 'The person_id is ' || x_approvers_list ( m).approver_person_id, 1, 'N');
469 aso_debug_pub.ADD ( 'The user_id is ' || x_approvers_list ( m).approver_user_id, 1, 'N');
470 END IF;
471
472 END LOOP;
473
474 END IF; -- end if for self approver case
475
476
477 if (x_approvers_list.count>0) and ( p_data_flag='N') then
478 -- populate global temp tables and return rules table
479
480 --Insert into test_pa_approval_debug_log values ('Before call to insert_approvers_gtt');
481
482 insert_approvers_gtt(approvertblin=>x_approvers_list,
483 p_object_appr_id => p_object_approval_id,
484 x_return_status => x_return_status,
485 x_msg_count => x_msg_count,
486 x_msg_data => x_msg_data);
487
488
489 end if;
490
491 -- Calling the OAM API to get the rules
492
493 -- Looping through the PL/SQL Table to assign values to be passed OUT NOCOPY /* file.sql.39 change */ as list
494 -- of rules and their descriptions
495
496 -- Checking if rule needs to be fetched from ASO table or AME.
497 if p_object_approval_id is not null then
498 select approval_Status into l_appr_instance_status
499 from aso_apr_obj_approvals
500 where p_object_approval_id=object_approval_id;
501
502 if l_appr_instance_status<>'PEND' then
503 --pp_debug('Fetching rules from table');
504 m:=0;
505 ruleids:=ame_util.emptyIdList;
506 for c1 in (select OAM_RULE_ID
507 from aso_apr_rules
508 where p_object_approval_id=object_approval_id)
509 loop
510 m:=m+1;
511 ruleids(m):=c1.OAM_RULE_ID;
512
513 end loop;
514 end if;
515 end if;
516
517
518 m:=0;
519
520 FOR i IN 1 .. ruleids.COUNT
521 LOOP
522
523 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
524 aso_debug_pub.ADD ('Calling AME get applicable rules procedure'|| ruleids.COUNT,1,'N');
525 END IF;
526
527 IF i > 1 THEN
528
529 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
530 aso_debug_pub.ADD (' Checking for duplicate ruleids.. ',1,'N');
531 END IF;
532
533
534 FOR j IN 1..x_rules_list.COUNT LOOP
535
536 IF ( ruleids(i) = x_rules_list(j).rule_id ) THEN
537
538 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
539 aso_debug_pub.ADD ('Found a duplicate rule ',1,'N');
540 aso_debug_pub.ADD ('Duplicate rule_id is '|| ruleids(i),1,'N');
541
542 END IF;
543
544 GOTO end_of_loop;
545
546 END IF;
547 END LOOP;
548
549 END IF;
550
551 aso_debug_pub.add('Rule id'||ruleids(I), 1, 'N');
552 ame_api3.getruledetails3 (
553 ruleIdIn => ruleids(i),
554 ruleTypeOut => l_ruletypeout,
555 ruleDescriptionOut => lRuleDescription,
556 conditionIdsOut => l_conditionidsout,
557 conditionDescriptionsOut => l_conditionDescriptionsOut,
558 conditionHasLOVsOut => l_ConditionHasLOVsOut,
559 actionTypeNamesOut => l_actionTypeNamesOut,
560 actionTypeDescriptionsOut => l_actionTypeDescriptionsOut,
561 actionDescriptionsOut => l_actionDescriptionsOut
562 );
563
564 if (m = 0) then
565 m := x_rules_list.count + 1;
566 end if;
567
568
569 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
570 aso_debug_pub.ADD ( 'm= '||m, 1, 'N');
571 aso_debug_pub.ADD ( 'l_actionDescriptionsOut.count = '||l_actionDescriptionsOut.count, 1, 'N');
572 end if;
573
574 aso_debug_pub.ADD ('m '||m, 1, 'N');
575 aso_debug_pub.ADD ('l_actionDescriptionsOut'||l_actionDescriptionsOut.count, 1, 'N');
576
577
578 for r in 1..l_actionDescriptionsOut.COUNT
579 loop
580 x_rules_list(m).rule_id := ruleids(i);
581 x_rules_list(m).rule_description := lRuleDescription;
582 x_rules_list(m).rule_type := AME_RULE_PKG.getRuleTypeLabel2 (to_number(l_ruletypeout));
583 x_rules_list(m).approval_level := l_actionDescriptionsOut(r);
584
585
586
587 if (x_rules_list(m).rule_id is not null) then
588 OPEN c_rules_det(880,x_rules_list(m).rule_id);
589 FETCH c_rules_det INTO l_item_class_id, l_approver_category;
590 CLOSE c_rules_det;
591 end if;
592
593 --pp_debug('l_item_class_id'||l_item_class_id);
594 --pp_debug('l_approver_category'||l_approver_category);
595
596 if l_item_class_id is not null then
597
598 OPEN c_itemclass_desc (l_item_class_id);
599 FETCH c_itemclass_desc INTO x_rules_list(m).rule_item_class;
600 CLOSE c_itemclass_desc;
601 end if;
602
603 if l_approver_category is not null then
604
605 OPEN c_appr_cat (l_approver_category);
606 FETCH c_appr_cat INTO x_rules_list(m).rule_category_desc;
607 CLOSE c_appr_cat;
608 end if;
609
610
611 m := m + 1;
612
613 END LOOP;
614 <<end_of_loop>>
615
616 NULL; -- if duplicate rules do nothing
617 END LOOP;
618
619 /*pp_debug('rulelist===============================');
620 for i in 1..x_rules_list.count loop
621 pp_debug('rule_id'||x_rules_list(i).rule_id);
622 pp_debug('rule_description'||x_rules_list(i).rule_description);
623 pp_debug('rule_type'||x_rules_list(i).rule_type);
624 pp_debug('approval_level'||x_rules_list(i).approval_level);
625 pp_debug('rule_item_class'||x_rules_list(i).rule_item_class);
626 pp_debug('rule_category_desc'||x_rules_list(i).rule_category_desc);
627 end loop;
628 */
629 -- commit the work
630
631 IF fnd_api.to_boolean (
632 p_commit
633 )
634 THEN
635 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
636 aso_debug_pub.ADD (
637 'Commiting the work in get_all_approvers procedure ',
638 1,
639 'N'
640 );
641 END IF;
642 COMMIT WORK;
643 END IF;
644
645 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
646 aso_debug_pub.ADD (
647 'End get all pa approvers procedure ',
648 1,
649 'N'
650 );
651 END IF;
652 EXCEPTION
653 WHEN fnd_api.g_exc_error
654 THEN
655 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
656 aso_debug_pub.ADD (
657 'Exception FND_API.G_EXC_ERROR in get_all_approvers ',
658 1,
659 'N'
660 );
661 END IF;
662 aso_utility_pvt.handle_exceptions (
663 p_api_name => l_api_name,
664 p_pkg_name => g_pkg_name,
665 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
666 p_package_type => aso_utility_pvt.g_int,
667 p_sqlcode => SQLCODE,
668 p_sqlerrm => SQLERRM,
669 x_msg_count => x_msg_count,
670 x_msg_data => x_msg_data,
671 x_return_status => x_return_status
672 );
673 WHEN fnd_api.g_exc_unexpected_error
674 THEN
675 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
676 aso_debug_pub.ADD (
677 'Exception FND_API.G_EXC_UNEXPECTED_ERROR in get_all_approvers ',
678 1,
679 'N'
680 );
681 END IF;
682 aso_utility_pvt.handle_exceptions (
683 p_api_name => l_api_name,
684 p_pkg_name => g_pkg_name,
685 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
686 p_package_type => aso_utility_pvt.g_int,
687 p_sqlcode => SQLCODE,
688 p_sqlerrm => SQLERRM,
689 x_msg_count => x_msg_count,
690 x_msg_data => x_msg_data,
691 x_return_status => x_return_status
692 );
693 WHEN OTHERS
694 THEN
695 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
696 aso_debug_pub.ADD (
697 'When Others Exception in get_all_approvers ',
698 1,
699 'N'
700 );
701 END IF;
702 aso_utility_pvt.handle_exceptions (
703 p_api_name => l_api_name,
704 p_pkg_name => g_pkg_name,
705 p_exception_level => aso_utility_pvt.g_exc_others,
706 p_package_type => aso_utility_pvt.g_int,
707 p_sqlcode => SQLCODE,
708 p_sqlerrm => SQLERRM,
709 x_msg_count => x_msg_count,
710 x_msg_data => x_msg_data,
711 x_return_status => x_return_status
712 );
713 END get_all_approvers;
714
715
716 PROCEDURE start_papproval_process (
717 p_api_version_number IN NUMBER,
718 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
719 p_commit IN VARCHAR2 := fnd_api.g_false,
720 p_object_id IN NUMBER,
721 p_object_type IN VARCHAR2,
722 p_application_id IN NUMBER,
723 p_requester_comments IN VARCHAR2,
724 p_del_appr_table IN ASO_PA_APR_PUB.approvers_list_tbl_type,
725 x_object_approval_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
726 x_approval_instance_id OUT NOCOPY /* file.sql.39 change */ NUMBER,
727 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
728 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
729 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
730 ) IS
731 l_api_name CONSTANT VARCHAR2 (30) := 'START_PAPPROVAL_PROCESS';
732 l_api_version CONSTANT NUMBER := 1.0;
733 p_approval_instance_id NUMBER;
734 p_object_approval_id NUMBER;
735
736 l_return_status VARCHAR2 (10);
737 l_msg_count NUMBER;
738 l_msg_data VARCHAR2 (240);
739 l_requester_group_id NUMBER;
740 l_sales_group_role VARCHAR2(250);
741 l_obsolete_status varchar2(1);
742 l_employee_id NUMBER;
743 l_dup_approval NUMBER;
744 l_approver_index number;
745 l_del_approver ame_util.approverRecord2;
746 -- l_process_out VARCHAR2 (10);
747 --approverlist ame_util.approverstable2;
748 CURSOR c2 (
749 c_object_id NUMBER
750 ) IS
751 SELECT NVL (
752 (MAX (
753 approval_instance_id
754 ) + 1
755 ),
756 1
757 )
758 FROM aso_apr_obj_approvals
759 WHERE object_id = c_object_id;
760
761 cursor get_employee_id(l_user_id NUMBER) IS
762 select employee_id
763 from fnd_user
764 where user_id = l_user_id;
765
766 CURSOR C_get_duplicate_approval IS
767 SELECT count(*)
768 FROM aso_apr_obj_approvals
769 WHERE object_id = p_object_id
770 AND approval_status = 'PEND';
771
772 BEGIN
773 g_user_id := fnd_global.user_id;
774 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
775 aso_debug_pub.ADD (
776 'Begin start_papproval_process procedure ',
777 1,
778 'N'
779 );
780 aso_debug_pub.ADD (
781 'Application ID : ' || p_application_id,
782 1,
783 'N'
784 );
785 aso_debug_pub.ADD (
786 'Object ID : ' || p_object_id,
787 1,
788 'N'
789 );
790 aso_debug_pub.ADD (
791 'Object Type : ' || p_object_type,
792 1,
793 'N'
794 );
795 aso_debug_pub.ADD (
796 'Commit Flag : ' || p_commit,
797 1,
798 'N'
799 );
800 END IF;
801 -- Standard call to establisg savepoint .
802
803 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
804 aso_debug_pub.ADD (
805 'Establishing save point START_PAPPROVAL_PROCESS_INT',
806 1,
807 'N'
808 );
809 END IF;
810 SAVEPOINT start_papproval_process_int;
811
812 -- Standard call to check for call compatibility.
813 IF NOT fnd_api.compatible_api_call (
814 l_api_version,
815 p_api_version_number,
816 l_api_name,
817 g_pkg_name
818 )
819 THEN
820 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
821 aso_debug_pub.ADD (
822 'START_PAPPROVAL_PROCESS api call was not compatible pls check version ',
823 1,
824 'N'
825 );
826 END IF;
827 RAISE fnd_api.g_exc_unexpected_error;
828 END IF;
829
830 -- Initialize message list if p_init_msg_list is set to TRUE
831
832 IF fnd_api.to_boolean (
833 p_init_msg_list
834 )
835 THEN
836 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
837 aso_debug_pub.ADD (
838 'Initializing the message list ',
839 1,
840 'N'
841 );
842 END IF;
843 fnd_msg_pub.initialize;
844 END IF;
845
846 -- Initialize API return status to success
847 x_return_status := fnd_api.g_ret_sts_success;
848
849
850 OPEN C_get_duplicate_approval;
851 FETCH C_get_duplicate_approval INTO l_dup_approval ;
852 CLOSE C_get_duplicate_approval;
853
854 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
855 aso_debug_pub.ADD('l_dup_approval: '|| l_dup_approval,1,'N');
856 END IF;
857
858 IF l_dup_approval = 0 THEN
859
860 -- checking if there is any approvers that needs to be suppresses
861 -- Just assign the values in p_del_appr_table to ame_util.approversTable2 structure and call ame_api3.suppressApprovers
862
863 if p_del_appr_table.count> 0 then
864 for l_approver_index in p_del_appr_table.first..p_del_appr_table.last loop
865 BEGIN
866 l_del_approver.name:=p_del_appr_table(l_approver_index).name;
867 l_del_approver.item_class:=p_del_appr_table(l_approver_index).item_class;
868 l_del_approver.item_id:=p_del_appr_table(l_approver_index).item_id;
869 l_del_approver.action_type_id:=p_del_appr_table(l_approver_index).action_type_id;
870 l_del_approver.group_or_chain_id:=p_del_appr_table(l_approver_index).chain_number;
871 l_del_approver.occurrence:=p_del_appr_table(l_approver_index).occurrence;
872
873
874 ame_api3.suppressApprover(applicationIdIn => p_application_id,
875 transactionTypeIn => p_object_type,
876 transactionIdIn => p_object_id,
877 approverIn => l_del_approver);
878
879
880
881
882 exception
883 when others then
884 raise;
885 END;
886 end loop;
887 end if;
888
889
890
891
892 -- Get the Requester Group id
893 begin
894
895 IF aso_debug_pub.g_debug_flag = 'Y' THEN
896 aso_debug_pub.add('Before calling aso_utility_pvt.Get_Profile_Obsolete_Status', 1, 'N');
897 END IF;
898
899 l_obsolete_status := aso_utility_pvt.Get_Profile_Obsolete_Status(p_profile_name => 'AST_DEFAULT_ROLE_AND_GROUP',
900 p_application_id => 521);
901
902 IF aso_debug_pub.g_debug_flag = 'Y' THEN
903 aso_debug_pub.add('After calling Get_Profile_Obsolete_Status: l_obsolete_status: ' || l_obsolete_status, 1, 'N');
904 END IF;
905
906 if l_obsolete_status = 'T' then
907
908 l_sales_group_role := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
909
910 IF aso_debug_pub.g_debug_flag = 'Y' THEN
911 aso_debug_pub.add('l_sales_group_role: ' || l_sales_group_role, 1, 'N');
912 END IF;
913
914 l_requester_group_id := SUBSTR(l_sales_group_role, 1, INSTR(l_sales_group_role,'(')-1);
915
916 IF aso_debug_pub.g_debug_flag = 'Y' THEN
917 aso_debug_pub.add('l_requester_group_id: ' || l_requester_group_id, 1, 'N');
918 END IF;
919
920 if l_requester_group_id is null then
921
922 l_sales_group_role := FND_PROFILE.Value_Specific( 'AST_DEFAULT_GROUP', G_USER_ID, NULL, 521);
923
924 IF aso_debug_pub.g_debug_flag = 'Y' THEN
925 aso_debug_pub.add('l_sales_group_role: ' || l_sales_group_role, 1, 'N');
926 END IF;
927
928 l_requester_group_id := to_number(l_sales_group_role);
929
930 IF aso_debug_pub.g_debug_flag = 'Y' THEN
931 aso_debug_pub.add('l_requester_group_id: ' || l_requester_group_id, 1, 'N');
932 END IF;
933
934 end if;
935
936 else
937
938 l_sales_group_role := FND_PROFILE.Value_Specific( 'ASF_DEFAULT_GROUP_ROLE', G_USER_ID, NULL, 522);
939
940 IF aso_debug_pub.g_debug_flag = 'Y' THEN
941 aso_debug_pub.add('l_sales_group_role: ' || l_sales_group_role, 1, 'N');
942 END IF;
943
944 l_requester_group_id := SUBSTR(l_sales_group_role, 1, INSTR(l_sales_group_role,'(')-1);
945
946 IF aso_debug_pub.g_debug_flag = 'Y' THEN
947 aso_debug_pub.add('l_requester_group_id: ' || l_requester_group_id, 1, 'N');
948 END IF;
949
950 if l_requester_group_id is null then
951
952 l_sales_group_role := FND_PROFILE.Value_Specific( 'AST_DEFAULT_ROLE_AND_GROUP', G_USER_ID, NULL, 521);
953
954 IF aso_debug_pub.g_debug_flag = 'Y' THEN
955 aso_debug_pub.add('l_sales_group_role: ' || l_sales_group_role, 1, 'N');
956 END IF;
957
958 l_requester_group_id := substr(l_sales_group_role, instr(l_sales_group_role,':', -1) + 1, length(l_sales_group_role));
959
960 IF aso_debug_pub.g_debug_flag = 'Y' THEN
961 aso_debug_pub.add('l_requester_group_id: ' || l_requester_group_id, 1, 'N');
962 END IF;
963
964 end if;
965
966 end if;
967
968
969 exception
970 when others then
971 l_requester_group_id := 0;
972 end;
973
974 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
975 aso_debug_pub.ADD (
976 'Requester Group ID is ' || l_requester_group_id,
977 1,
978 'N'
979 );
980 END IF;
981 -- Generate a new value for the approval Instace ID
982 OPEN c2 (
983 p_object_id
984 );
985 FETCH c2 INTO p_approval_instance_id;
986 CLOSE c2;
987 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
988 aso_debug_pub.ADD (
989 'Approval Instance ID is ' || p_approval_instance_id,
990 1,
991 'N'
992 );
993 aso_debug_pub.ADD (
994 'Inserting a row into the header table ',
995 1,
996 'N'
997 );
998 END IF;
999 -- Inserting a Row into the Header table by calling the Table Handler
1000
1001 aso_apr_approvals_pkg.header_insert_row (
1002 p_object_approval_id,
1003 p_object_id,
1004 p_object_type,
1005 p_approval_instance_id,
1006 'PEND',
1007 p_application_id,
1008 SYSDATE -- p_START_DATE
1009 ,
1010 NULL -- p_END_DATE
1011 ,
1012 SYSDATE -- p_CREATION_DATE
1013 ,
1014 g_user_id -- p_CREATED_BY
1015 ,
1016 SYSDATE -- p_LAST_UPDATE_DATE
1017 ,
1018 g_user_id -- P_UPDATED_BY
1019 ,
1020 fnd_global.conc_login_id -- p_LAST_UPDATE_LOGIN
1021 ,
1022 NULL,
1023 NULL,
1024 NULL,
1025 NULL,
1026 NULL,
1027 NULL,
1028 NULL,
1029 NULL,
1030 NULL,
1031 NULL,
1032 NULL,
1033 NULL,
1034 NULL,
1035 NULL,
1036 NULL,
1037 NULL,
1038 NULL,
1039 NULL,
1040 NULL,
1041 NULL,
1042 NULL -- p_CONTEXT
1043 ,
1044 NULL -- p_SECURITY_GROUP_ID
1045 ,
1046 1 -- p_OBJECT_VERSION_NUMBER
1047 ,
1048 g_user_id -- p_REQUESTER_USERID
1049 ,
1050 p_requester_comments -- p_REQUESTER_COMMENTS
1051 ,
1052 l_requester_group_id -- p_REQUESTER_GROUP_ID
1053 );
1054
1055 /* BEGIN
1056 ame_api2.getallapprovers7
1057 (applicationidin => p_application_id,
1058 transactionidin => p_object_id,
1059 transactiontypein =>p_object_type,
1060 approvalprocesscompleteynout => l_process_out,
1061 approversout => approverlist
1062 );
1063
1064 --pp_debug('l_process_out'||l_process_out);
1065 --pp_debug('approverlist: : '||approverlist.count);
1066
1067 if approverlist.count=0 then -- No approvers
1068 update aso_apr_obj_approvals
1069 set approval_status = 'APPR',
1070 end_date = sysdate
1071 where object_approval_id = p_object_approval_id;
1072 END IF;
1073 EXCEPTION
1074 when others then
1075 --pp_debug('exception getallapprovers');
1076 null;
1077
1078
1079 END;
1080 */
1081
1082
1083 -- Pass back the new approval id
1084 x_object_approval_id := p_object_approval_id;
1085 x_approval_instance_id := p_approval_instance_id;
1086
1087 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1088 aso_debug_pub.ADD (
1089 'Object Approval ID :' || x_approval_instance_id,
1090 1,
1091 'N'
1092 );
1093 aso_debug_pub.ADD (
1094 'Approval Instance ID :' || x_approval_instance_id,
1095 1,
1096 'N'
1097 );
1098 END IF;
1099
1100 ELSE -- l_dup_approval is not 0
1101
1102 -- return a dummy instance id
1103 x_approval_instance_id := -1;
1104 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1105 aso_debug_pub.ADD(' Duplicate approval process FOUND , hence another process NOT Started',1,'N');
1106 END IF;
1107 END IF;
1108
1109 -- commit the work
1110
1111 IF fnd_api.to_boolean (
1112 p_commit
1113 )
1114 THEN
1115 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1116 aso_debug_pub.ADD (
1117 'Commiting the work in START_PAPPROVAL_PROCESS procedure ',
1118 1,
1119 'N'
1120 );
1121 END IF;
1122 COMMIT WORK;
1123 END IF;
1124
1125 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1126 aso_debug_pub.ADD (
1127 'End Start_papproval_process PROCEDURE ',
1128 1,
1129 'N'
1130 );
1131 END IF;
1132 EXCEPTION
1133 WHEN fnd_api.g_exc_error
1134 THEN
1135 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1136 aso_debug_pub.ADD (
1137 'Exception FND_API.G_EXC_ERROR in start_papproval_process',
1138 1,
1139 'N'
1140 );
1141 END IF;
1142 aso_utility_pvt.handle_exceptions (
1143 p_api_name => l_api_name,
1144 p_pkg_name => g_pkg_name,
1145 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
1146 p_package_type => aso_utility_pvt.g_int,
1147 p_sqlcode => SQLCODE,
1148 p_sqlerrm => SQLERRM,
1149 x_msg_count => x_msg_count,
1150 x_msg_data => x_msg_data,
1151 x_return_status => x_return_status
1152 );
1153 WHEN fnd_api.g_exc_unexpected_error
1154 THEN
1155 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1156 aso_debug_pub.ADD (
1157 'Exception FND_API.G_EXC_UNEXPECTED_ERROR in start_papproval_process',
1158 1,
1159 'N'
1160 );
1161 END IF;
1162 aso_utility_pvt.handle_exceptions (
1163 p_api_name => l_api_name,
1164 p_pkg_name => g_pkg_name,
1165 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
1166 p_package_type => aso_utility_pvt.g_int,
1167 p_sqlcode => SQLCODE,
1168 p_sqlerrm => SQLERRM,
1169 x_msg_count => x_msg_count,
1170 x_msg_data => x_msg_data,
1171 x_return_status => x_return_status
1172 );
1173
1174 WHEN OTHERS
1175 THEN
1176 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1177 aso_debug_pub.ADD (
1178 'When Others Exception in start_papproval_process',
1179 1,
1180 'N'
1181 );
1182 END IF;
1183 aso_utility_pvt.handle_exceptions (
1184 p_api_name => l_api_name,
1185 p_pkg_name => g_pkg_name,
1186 p_exception_level => aso_utility_pvt.g_exc_others,
1187 p_package_type => aso_utility_pvt.g_int,
1188 p_sqlcode => SQLCODE,
1189 p_sqlerrm => SQLERRM,
1190 x_msg_count => x_msg_count,
1191 x_msg_data => x_msg_data,
1192 x_return_status => x_return_status
1193 );
1194 END start_papproval_process;
1195
1196 PROCEDURE cancel_approval_process (
1197 p_api_version_number IN NUMBER,
1198 p_init_msg_list IN VARCHAR2,
1199 p_commit IN VARCHAR2,
1200 p_object_id IN NUMBER,
1201 p_object_type IN VARCHAR2,
1202 p_application_id IN NUMBER,
1203 p_itemtype IN VARCHAR2,
1204 p_object_approval_id IN NUMBER,
1205 p_user_id IN NUMBER,
1206 x_return_status OUT NOCOPY VARCHAR2,
1207 x_msg_count OUT NOCOPY NUMBER,
1208 x_msg_data OUT NOCOPY VARCHAR2
1209 ) IS
1210 l_api_name CONSTANT VARCHAR2 (3000) := 'cancel_approval_process';
1211 l_api_version CONSTANT NUMBER := 1.0;
1212 l_cancellor_username VARCHAR2 (240);
1213
1214 CURSOR get_approval_id (
1215 c_object_id NUMBER,
1216 c_object_type VARCHAR2
1217 ) IS
1218 SELECT object_approval_id
1219 FROM aso_apr_obj_approvals
1220 WHERE object_id = c_object_id
1221 AND object_type = c_object_type
1222 AND approval_instance_id = (SELECT MAX (
1223 approval_instance_id
1224 )
1225 FROM aso_apr_obj_approvals
1226 WHERE object_id = c_object_id
1227 AND object_type = c_object_type);
1228
1229
1230 BEGIN
1231 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1232 aso_debug_pub.ADD (
1233 'Begin cancel_approval_process PROCEDURE ',
1234 1,
1235 'N'
1236 );
1237 aso_debug_pub.ADD (
1238 'Application ID : ' || p_application_id,
1239 1,
1240 'N'
1241 );
1242 aso_debug_pub.ADD (
1243 'Object ID : ' || p_object_id,
1244 1,
1245 'N'
1246 );
1247 aso_debug_pub.ADD (
1248 'Object Type : ' || p_object_type,
1249 1,
1250 'N'
1251 );
1252 aso_debug_pub.ADD (
1253 'Commit Flag : ' || p_commit,
1254 1,
1255 'N'
1256 );
1257 aso_debug_pub.ADD (
1258 'Object Approval Id : ' || p_object_approval_id,
1259 1,
1260 'N'
1261 );
1262
1263
1264
1265 END IF;
1266 -- Set a save point
1267 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1268 aso_debug_pub.ADD (
1269 'Establishing save point CANCEL_APPROVAL_PROCESS_INT',
1270 1,
1271 'N'
1272 );
1273 END IF;
1274 SAVEPOINT cancel_approval_process_int;
1275
1276 -- Standard call to check for call compatibility.
1277 IF NOT fnd_api.compatible_api_call (
1278 l_api_version,
1279 p_api_version_number,
1280 l_api_name,
1281 g_pkg_name
1282 )
1283 THEN
1284 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1285 aso_debug_pub.ADD (
1286 'cancel_approval_process api call was not compatible pls check version ',
1287 1,
1288 'N'
1289 );
1290 END IF;
1291 RAISE fnd_api.g_exc_unexpected_error;
1292 END IF;
1293
1294 -- Initialize message list if p_init_msg_list is set to TRUE
1295
1296 IF fnd_api.to_boolean (
1297 p_init_msg_list
1298 )
1299 THEN
1300 fnd_msg_pub.initialize;
1301 END IF;
1302
1303 -- Initialize API return status to success
1304 x_return_status := fnd_api.g_ret_sts_success;
1305
1306 -- Check to see if required parameters are passed
1307 IF ( ((P_Object_approval_id IS NULL) OR (P_Object_approval_id = FND_API.G_MISS_NUM)) AND
1308 ((p_object_id IS NULL) OR (p_object_id = FND_API.G_MISS_NUM)) AND
1309 ((p_object_type IS NULL) OR (p_object_type = FND_API.G_MISS_CHAR)) ) THEN
1310
1311 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_INVALID_ID' );
1312 FND_MESSAGE.Set_Token ('COLUMN' , 'P_Object_approval_id', FALSE );
1313 FND_MESSAGE.Set_Token ( 'VALUE' , TO_CHAR ( P_Object_approval_id ) , FALSE );
1314 FND_MSG_PUB.ADD;
1315 RAISE FND_API.G_EXC_ERROR;
1316 END IF;
1317
1318 IF (p_object_approval_id IS NULL)
1319 OR (p_object_approval_id = fnd_api.g_miss_num)
1320 THEN
1321 FOR i IN get_approval_id (
1322 p_object_id,
1323 p_object_type
1324 )
1325 LOOP
1326 -- calling the prccedure to cancel the workflow process
1327
1328 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1329 aso_debug_pub.ADD (
1330 'Object approval ID :' || i.object_approval_id,
1331 1,
1332 'N'
1333 );
1334 aso_debug_pub.ADD (
1335 'Calling the wokflow procedure to start the cancellation process ',
1336 1,
1337 'N'
1338 );
1339 END IF;
1340 aso_pa_apr_wf_pvt.cancelapproval (
1341 i.object_approval_id,
1342 p_itemtype,
1343 p_user_id
1344 );
1345 END LOOP;
1346 ELSE
1347 -- calling the prccedure to cancel the workflow process
1348 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1349 aso_debug_pub.ADD (
1350 'Object approval ID :' || p_object_approval_id,
1351 1,
1352 'N'
1353 );
1354 aso_debug_pub.ADD (
1355 'Calling the wokflow procedure to start the cancellation process ',
1356 1,
1357 'N'
1358 );
1359 END IF;
1360 aso_pa_apr_wf_pvt.cancelapproval (
1361 p_object_approval_id,
1362 p_itemtype,
1363 p_user_id
1364 );
1365 END IF;
1366
1367 -- Commit the work
1368
1369 IF fnd_api.to_boolean (
1370 p_commit
1371 )
1372 THEN
1373 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1374 aso_debug_pub.ADD (
1375 'Committing the work in cancel approval procedure ',
1376 1,
1377 'N'
1378 );
1379 END IF;
1380 COMMIT WORK;
1381 END IF;
1382
1383 fnd_msg_pub.count_and_get(p_encoded => 'F',
1384 p_count => x_msg_count,
1385 p_data => x_msg_data);
1386 for k in 1..x_msg_count loop
1387 x_msg_data := fnd_msg_pub.get(p_msg_index => k,
1388 p_encoded => 'F');
1389 end loop;
1390
1391 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1392 aso_debug_pub.ADD (
1393 'End CancelApproval procedure ',
1394 1,
1395 'N'
1396 );
1397 END IF;
1398 EXCEPTION
1399 WHEN fnd_api.g_exc_error
1400 THEN
1401 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1402 aso_debug_pub.ADD (
1403 'Exception FND_API.G_EXC_ERROR in CancelApproval',
1404 1,
1405 'N'
1406 );
1407 END IF;
1408 aso_utility_pvt.handle_exceptions (
1409 p_api_name => l_api_name,
1410 p_pkg_name => g_pkg_name,
1411 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
1412 p_package_type => aso_utility_pvt.g_int,
1413 p_sqlcode => SQLCODE,
1414 p_sqlerrm => SQLERRM,
1415 x_msg_count => x_msg_count,
1416 x_msg_data => x_msg_data,
1417 x_return_status => x_return_status
1418 );
1419 WHEN fnd_api.g_exc_unexpected_error
1420 THEN
1421 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1422 aso_debug_pub.ADD (
1423 'Exception FND_API.G_EXC_UNEXPECTED_ERROR in CancelApproval',
1424 1,
1425 'N'
1426 );
1427 END IF;
1428 aso_utility_pvt.handle_exceptions (
1429 p_api_name => l_api_name,
1430 p_pkg_name => g_pkg_name,
1431 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
1432 p_package_type => aso_utility_pvt.g_int,
1433 p_sqlcode => SQLCODE,
1434 p_sqlerrm => SQLERRM,
1435 x_msg_count => x_msg_count,
1436 x_msg_data => x_msg_data,
1437 x_return_status => x_return_status
1438 );
1439 WHEN OTHERS
1440 THEN
1441 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1442 aso_debug_pub.ADD (
1443 'When Others Exception in CancelApproval',
1444 1,
1445 'N'
1446 );
1447 END IF;
1448 aso_utility_pvt.handle_exceptions (
1449 p_api_name => l_api_name,
1450 p_pkg_name => g_pkg_name,
1451 p_exception_level => aso_utility_pvt.g_exc_others,
1452 p_package_type => aso_utility_pvt.g_int,
1453 p_sqlcode => SQLCODE,
1454 p_sqlerrm => SQLERRM,
1455 x_msg_count => x_msg_count,
1456 x_msg_data => x_msg_data,
1457 x_return_status => x_return_status
1458 );
1459 END cancel_approval_process;
1460
1461 PROCEDURE start_pa_approval_workflow (
1462 p_api_version_number IN NUMBER,
1463 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1464 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1465 P_Object_approval_id IN NUMBER,
1466 P_itemtype IN VARCHAR2,
1467 P_sender_name IN VARCHAR2,
1468 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1469 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1470 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1471 )
1472 IS
1473
1474 l_api_name CONSTANT VARCHAR2 (30) := 'start_pa_approval_workflow';
1475 l_api_version CONSTANT NUMBER := 1.0;
1476
1477
1478 BEGIN
1479 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1480 aso_debug_pub.ADD (
1481 'Start of start_pa_approval_workflow',
1482 1,
1483 'N'
1484 );
1485
1486 --pp_debug('In Workflow');
1487 aso_debug_pub.ADD (
1488 'Object Approval ID : ' || P_Object_approval_id,
1489 1,
1490 'N'
1491 );
1492 aso_debug_pub.ADD (
1493 'ItemType : ' || P_itemtype,
1494 1,
1495 'N'
1496 );
1497 aso_debug_pub.ADD (
1498 'Sender name : ' || P_sender_name,
1499 1,
1500 'N'
1501 );
1502 aso_debug_pub.ADD (
1503 'Commit Flag : ' || p_commit,
1504 1,
1505 'N'
1506 );
1507 END IF;
1508
1509 -- Standard call to check for call compatibility.
1510 IF NOT fnd_api.compatible_api_call (
1511 l_api_version,
1512 p_api_version_number,
1513 l_api_name,
1514 g_pkg_name
1515 )
1516 THEN
1517 RAISE fnd_api.g_exc_unexpected_error;
1518 END IF;
1519
1520 -- Initialize message list if p_init_msg_list is set to TRUE
1521 IF fnd_api.to_boolean (
1522 p_init_msg_list
1523 )
1524 THEN
1525 fnd_msg_pub.initialize;
1526 END IF;
1527
1528 -- Initialize API return status to success
1529 x_return_status := fnd_api.g_ret_sts_success;
1530
1531 -- Check to see if required parameters are passed
1532 IF ((P_Object_approval_id IS NULL) OR (P_Object_approval_id = FND_API.G_MISS_NUM)) THEN
1533 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_INVALID_ID' );
1534 FND_MESSAGE.Set_Token ('COLUMN' , 'P_Object_approval_id', FALSE );
1535 FND_MESSAGE.Set_Token ( 'VALUE' , TO_CHAR ( P_Object_approval_id ) , FALSE );
1536 FND_MSG_PUB.ADD;
1537 RAISE FND_API.G_EXC_ERROR;
1538 ELSIF ((P_itemtype IS NULL) OR (P_itemtype = FND_API.G_MISS_CHAR)) THEN
1539 FND_MESSAGE.Set_Name ('ASO' , 'ASO_API_INVALID_ID' );
1540 FND_MESSAGE.Set_Token ('COLUMN' , 'P_Object_approval_id', FALSE );
1541 FND_MESSAGE.Set_Token ( 'VALUE' , TO_CHAR ( P_Object_approval_id ) , FALSE );
1542 FND_MSG_PUB.ADD;
1543 RAISE FND_API.G_EXC_ERROR;
1544 END IF;
1545
1546 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1547 aso_debug_pub.ADD (
1548 'Calling procedure aso_pa_apr_wf_pvt.start_aso_approvals',
1549 1,
1550 'N'
1551 );
1552 END IF;
1553
1554 aso_pa_apr_wf_pvt.start_aso_approvals (
1555 P_Object_approval_id,
1556 P_itemtype,
1557 P_sender_name);
1558
1559
1560 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1561 aso_debug_pub.ADD (
1562 'After Calling procedure aso_pa_apr_wf_pvt.start_aso_approvals',
1563 1,
1564 'N'
1565 );
1566 END IF;
1567
1568
1569 -- Commit the work
1570
1571 IF fnd_api.to_boolean (
1572 p_commit
1573 )
1574 THEN
1575 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1576 aso_debug_pub.ADD (
1577 'Committing the work in start_pa_approval_workflow ',
1578 1,
1579 'N'
1580 );
1581 END IF;
1582 COMMIT WORK;
1583 END IF;
1584
1585 fnd_msg_pub.count_and_get(p_encoded => 'F',
1586 p_count => x_msg_count,
1587 p_data => x_msg_data);
1588 for k in 1..x_msg_count loop
1589 x_msg_data := fnd_msg_pub.get(p_msg_index => k,
1590 p_encoded => 'F');
1591 end loop;
1592
1593
1594
1595 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1596 aso_debug_pub.ADD (
1597 'End start_pa_approval_workflow procedure ',
1598 1,
1599 'N'
1600 );
1601 END IF;
1602
1603
1604 EXCEPTION
1605 WHEN fnd_api.g_exc_error
1606 THEN
1607 aso_utility_pvt.handle_exceptions (
1608 p_api_name => l_api_name,
1609 p_pkg_name => g_pkg_name,
1610 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
1611 p_package_type => aso_utility_pvt.g_int,
1612 p_sqlcode => SQLCODE,
1613 p_sqlerrm => SQLERRM,
1614 x_msg_count => x_msg_count,
1615 x_msg_data => x_msg_data,
1616 x_return_status => x_return_status
1617 );
1618 WHEN fnd_api.g_exc_unexpected_error
1619 THEN
1620 aso_utility_pvt.handle_exceptions (
1621 p_api_name => l_api_name,
1622 p_pkg_name => g_pkg_name,
1623 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
1624 p_package_type => aso_utility_pvt.g_int,
1625 p_sqlcode => SQLCODE,
1626 p_sqlerrm => SQLERRM,
1627 x_msg_count => x_msg_count,
1628 x_msg_data => x_msg_data,
1629 x_return_status => x_return_status
1630 );
1631 WHEN OTHERS
1632 THEN
1633 aso_utility_pvt.handle_exceptions (
1634 p_api_name => l_api_name,
1635 p_pkg_name => g_pkg_name,
1636 p_exception_level => aso_utility_pvt.g_exc_others,
1637 p_package_type => aso_utility_pvt.g_int,
1638 p_sqlcode => SQLCODE,
1639 p_sqlerrm => SQLERRM,
1640 x_msg_count => x_msg_count,
1641 x_msg_data => x_msg_data,
1642 x_return_status => x_return_status
1643 );
1644 END start_pa_approval_workflow;
1645
1646 PROCEDURE upd_status_self_appr
1647 ( p_qte_hdr_id IN NUMBER,
1648 p_obj_ver_num IN NUMBER,
1649 p_last_update_date IN DATE,
1650 x_obj_ver_num OUT NOCOPY NUMBER,
1651 x_last_update_date OUT NOCOPY DATE,
1652 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1653 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1654 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1655 )
1656 IS
1657
1658 l_api_name CONSTANT VARCHAR2 (240) := 'upd_status_self_appr';
1659 l_last_update_date DATE;
1660 l_object_version_number NUMBER;
1661 l_status_id NUMBER;
1662
1663 l_process_out VARCHAR2 (10);
1664 approverlist ame_util.approverstable2;
1665 l_appr_cat_count number;
1666 l_object_approval_id number;
1667 l_object_type varchar2(30);
1668 l_application_id number;
1669 l_apr_object_version number;
1670
1671 cursor c_get_qte_info ( l_qte_hdr_id number) is
1672 select last_update_date,object_version_number
1673 from aso_quote_headers_all
1674 where quote_header_id = l_qte_hdr_id;
1675
1676 CURSOR C_Get_Status IS
1677 SELECT Quote_Status_Id
1678 FROM ASO_QUOTE_STATUSES_B
1679 WHERE Status_Code = 'APPROVAL PENDING';
1680
1681 cursor c_get_object(l_qte_header_id number) is
1682 select object_approval_id,object_type,application_id,object_version_number
1683 from aso_apr_obj_approvals
1684 where OBJECT_ID=l_qte_header_id
1685 and approval_Status='PEND';
1686
1687 BEGIN
1688
1689 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1690 aso_debug_pub.add('upd_status_self_appr: BEGIN ', 1, 'Y');
1691 END IF;
1692
1693 -- Initialize API return status to success
1694 x_return_status := fnd_api.g_ret_sts_success;
1695
1696 SAVEPOINT upd_status_self_appr_int;
1697
1698 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1699 aso_debug_pub.add('upd_status_self_appr: p_qte_hdr_id: '|| p_qte_hdr_id , 1, 'Y');
1700 aso_debug_pub.add('upd_status_self_appr: p_obj_ver_num: || p_obj_ver_num ', 1, 'Y');
1701 aso_debug_pub.add('upd_status_self_appr: p_last_update_date: || p_last_update_date ', 1, 'Y');
1702 END IF;
1703
1704 Open c_get_qte_info(p_qte_hdr_id);
1705 Fetch c_get_qte_info into l_LAST_UPDATE_DATE,l_object_version_number;
1706 If ( c_get_qte_info%NOTFOUND) Then
1707 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1708 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_UPDATE_TARGET');
1709 FND_MESSAGE.Set_Token ('INFO', 'quote', FALSE);
1710 FND_MSG_PUB.Add;
1711 END IF;
1712 raise FND_API.G_EXC_ERROR;
1713 END IF;
1714 Close c_get_qte_info;
1715
1716 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1717 aso_debug_pub.add('upd_status_self_appr: l_LAST_UPDATE_DATE: '|| l_LAST_UPDATE_DATE, 1, 'Y');
1718 aso_debug_pub.add('upd_status_self_appr: l_object_version_number: '|| l_object_version_number,1,'Y');
1719 END IF;
1720
1721 If (l_last_update_date is NULL or l_last_update_date = FND_API.G_MISS_Date ) Then
1722 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1723 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_COLUMN');
1724 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
1725 FND_MSG_PUB.ADD;
1726 END IF;
1727 raise FND_API.G_EXC_ERROR;
1728 End if;
1729
1730 -- Check Whether record has been changed by someone else
1731 If ( (l_last_update_date <> p_last_update_date ) OR (l_object_version_number <> p_obj_ver_num ) ) Then
1732 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1733 FND_MESSAGE.Set_Name('ASO', 'ASO_API_RECORD_CHANGED');
1734 FND_MESSAGE.Set_Token('INFO', 'quote', FALSE);
1735 FND_MSG_PUB.ADD;
1736 END IF;
1737 raise FND_API.G_EXC_ERROR;
1738 End if;
1739
1740 OPEN C_Get_Status;
1741 FETCH C_Get_Status into l_status_id;
1742 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1743 aso_debug_pub.add('upd_status_self_appr: l_status_id: '|| l_status_id, 1, 'Y');
1744 END IF;
1745
1746 CLOSE C_Get_Status;
1747 -- set the out variables
1748 x_last_update_date := sysdate;
1749 x_obj_ver_num := l_object_version_number + 1;
1750
1751 -- update the quote status to approval pending
1752 update aso_quote_headers_all
1753 set quote_status_id = l_status_id,
1754 object_version_number = x_obj_ver_num,
1755 last_update_date = x_last_update_date
1756 where quote_header_id = p_qte_hdr_id;
1757
1758
1759 -- For Self approval case i.e. no approvers or only FYI approvers
1760 BEGIN
1761 open c_get_object(p_qte_hdr_id);
1762 fetch c_get_object into l_object_approval_id,l_object_type,l_application_id,l_apr_object_version;
1763 close c_get_object;
1764
1765 --pp_debug('l_object_approval_id'||l_object_approval_id);
1766 --pp_debug('l_object_type'||l_object_type);
1767 --pp_debug('l_application_id'||l_application_id);
1768 --pp_debug('l_apr_object_version'||l_apr_object_version);
1769
1770 ame_api2.getallapprovers7
1771 (applicationidin => l_application_id,
1772 transactionidin => p_qte_hdr_id,
1773 transactiontypein =>l_object_type,
1774 approvalprocesscompleteynout => l_process_out,
1775 approversout => approverlist
1776 );
1777
1778 --pp_debug('l_process_out'||l_process_out);
1779 --pp_debug('approverlist: : '||approverlist.count);
1780
1781 l_appr_cat_count:=0;
1782 for i in 1..approverlist.count loop
1783 if (approverlist(i).approver_category='A') and (approverlist(i).approval_status<>'SUPPRESSED') then
1784 l_appr_cat_count:=l_appr_cat_count+1;
1785 end if;
1786 end loop;
1787
1788
1789 x_last_update_date := sysdate;
1790 l_apr_object_version := nvl(l_apr_object_version,0) + 1;
1791 --pp_debug('l_appr_cat_count'||l_appr_cat_count);
1792 if (approverlist.count=0) or (l_appr_cat_count=0) then --No approvers
1793
1794 --pp_debug('Self approval scenario');
1795
1796 update aso_apr_obj_approvals
1797 set approval_status = 'APPR',
1798 object_version_number = l_apr_object_version,
1799 end_date = x_last_update_date,
1800 last_update_date = x_last_update_date
1801 where object_approval_id = l_object_approval_id;
1802
1803 END IF;
1804
1805 EXCEPTION
1806 when others then
1807 --pp_debug('exception getallapprovers');
1808 NULL;
1809 END;
1810
1811
1812 fnd_msg_pub.count_and_get(p_encoded => 'F',
1813 p_count => x_msg_count,
1814 p_data => x_msg_data);
1815 for k in 1..x_msg_count loop
1816 x_msg_data := fnd_msg_pub.get(p_msg_index => k,
1817 p_encoded => 'F');
1818 end loop;
1819
1820 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1821 aso_debug_pub.add('upd_status_self_appr: END ', 1, 'Y');
1822 END IF;
1823
1824 EXCEPTION
1825 WHEN fnd_api.g_exc_error
1826 THEN
1827 aso_utility_pvt.handle_exceptions (
1828 p_api_name => l_api_name,
1829 p_pkg_name => g_pkg_name,
1830 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
1831 p_package_type => aso_utility_pvt.g_int,
1832 p_sqlcode => SQLCODE,
1833 p_sqlerrm => SQLERRM,
1834 x_msg_count => x_msg_count,
1835 x_msg_data => x_msg_data,
1836 x_return_status => x_return_status
1837 );
1838 WHEN fnd_api.g_exc_unexpected_error
1839 THEN
1840 aso_utility_pvt.handle_exceptions (
1841 p_api_name => l_api_name,
1842 p_pkg_name => g_pkg_name,
1843 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
1844 p_package_type => aso_utility_pvt.g_int,
1845 p_sqlcode => SQLCODE,
1846 p_sqlerrm => SQLERRM,
1847 x_msg_count => x_msg_count,
1848 x_msg_data => x_msg_data,
1849 x_return_status => x_return_status
1850 );
1851 WHEN OTHERS
1852 THEN
1853 aso_utility_pvt.handle_exceptions (
1854 p_api_name => l_api_name,
1855 p_pkg_name => g_pkg_name,
1856 p_exception_level => aso_utility_pvt.g_exc_others,
1857 p_package_type => aso_utility_pvt.g_int,
1858 p_sqlcode => SQLCODE,
1859 p_sqlerrm => SQLERRM,
1860 x_msg_count => x_msg_count,
1861 x_msg_data => x_msg_data,
1862 x_return_status => x_return_status
1863 );
1864 END upd_status_self_appr;
1865
1866 procedure insert_approvers_gtt
1867 (approvertblin IN aso_pa_apr_pub.approvers_list_tbl_type,
1868 p_object_appr_id IN number,
1869 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
1870 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
1871 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
1872 as
1873 cursor get_approver_details(p_object_appr_id number) is
1874 select APPROVAL_DET_ID, APPROVER_PERSON_ID, approver_user_id,APPROVER_SEQUENCE, APPROVER_STATUS, APPROVER_COMMENTS,
1875 name,item_class, item_id,action_type_id, group_or_chain_id, occurrence, authority,approver_category
1876 from ASO_APR_APPROVAL_DETAILS
1877 where object_approval_id = p_object_appr_id
1878 order by APPROVAL_DET_ID;
1879
1880 /* Cursor to fetch approver category description*/
1881 CURSOR c_appr_cat (l_value VARCHAR2 ) is
1882 select meaning
1883 from fnd_lookups
1884 where lookup_type = 'AME_APPROVER_CATEGORY'
1885 and lookup_code = l_value
1886 and enabled_flag = 'Y';
1887
1888
1889 /* Cursor to fetch Sublist Desc */
1890 CURSOR c_appr_sublist (l_value VARCHAR2 ) is
1891 select meaning
1892 from fnd_lookups
1893 where lookup_type = 'AME_SUBLIST_TYPES'
1894 and lookup_code = l_value
1895 and enabled_flag = 'Y';
1896
1897 /* Cursor to fetch Action type Desc */
1898 cursor c_appr_action (l_value INTEGER) is
1899 select USER_ACTION_TYPE_NAME
1900 from ame_action_types_vl
1901 where ACTION_TYPE_ID = l_value
1902 and nvl(end_date,sysdate)>=trunc(sysdate);
1903
1904 l_approval_status varchar2(30);
1905 l_approval_Detail_id number;
1906 l_approver_comments VARCHAR2 (240);
1907 l_displayname varchar2(360);
1908 l_name varchar2(320);
1909 l_approver_type VARCHAR2 (50);
1910 tempOrigSystem ame_util.stringType;
1911 tempOrigSystemId integer;
1912 l_approver_category_desc varchar2(50);
1913 l_sub_list_desc varchar2(50);
1914 l_action_type varchar2(150);
1915 l_count number:=0;
1916 l_api_name varchar2(240):='insert_approvers_gtt';
1917 l_approval_status_desc varchar2(30);
1918 begin
1919
1920 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1921 aso_debug_pub.add('insert_approvers_gtt: BEGIN ', 1, 'Y');
1922 END IF;
1923
1924 --pp_debug('insert_approvers_gtt: BEGIN');
1925 -- Initialize API return status to success
1926 x_return_status := fnd_api.g_ret_sts_success;
1927
1928 SAVEPOINT insert_approvers_gtt;
1929
1930 select approval_status
1931 into l_approval_status
1932 from ASO_APR_OBJ_APPROVALS
1933 where OBJECT_APPROVAL_ID=p_object_appr_id;
1934
1935 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1936 aso_debug_pub.ADD ('insert_approvers_gtt:',1,'N');
1937 --pp_debug('insert_approvers_gtt: BEGIN'||l_approval_status);
1938 end if;
1939
1940
1941
1942 select count(*) into l_count
1943 from ASO_APR_APPROVAL_DETAILS_TEMP
1944 where object_approval_id = p_object_appr_id;
1945
1946 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1947 aso_debug_pub.ADD ('insert_approvers_gtt: temp count'||l_count,1,'N');
1948 --pp_debug('insert_approvers_gtt: temp count'||l_count);
1949 end if;
1950 if l_count>0 then
1951 Delete from ASO_APR_APPROVAL_DETAILS_TEMP
1952 where object_approval_id = p_object_appr_id;
1953 end if;
1954
1955 if (l_approval_status='PEND') then
1956 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1957 aso_debug_pub.ADD ('insert_approvers_gtt: approvertblin count'||approvertblin.count,1,'N');
1958 --pp_debug('insert_approvers_gtt: approvertblin'||approvertblin.count);
1959 end if;
1960
1961 if approvertblin.count>0 then
1962 for i in 1..approvertblin.count loop
1963 begin
1964 select APPROVAL_DET_ID , approver_comments
1965 into l_approval_Detail_id, l_approver_comments
1966 from ASO_APR_APPROVAL_DETAILS
1967 where object_approval_id = p_object_appr_id
1968 and nvl(approver_person_id,approver_user_id)=nvl(approvertblin(i).approver_person_id,approvertblin(i).approver_user_id)
1969 and name=approvertblin(i).name
1970 and APPROVER_SEQUENCE=approvertblin(i).approver_sequence
1971 and ((item_id is null) or (item_id=approvertblin(i).item_id))
1972 and ((item_class is null) or (item_class=approvertblin(i).item_class))
1973 and (nvl(action_type_id,0)=nvl(approvertblin(i).action_type_id,0))
1974 and (nvl(group_or_chain_id,0)=nvl(approvertblin(i).chain_number,0))
1975 and (nvl(occurrence,0) = nvl(approvertblin(i).occurrence,0));
1976 exception
1977 when no_data_found then
1978 l_approval_Detail_id:=null;
1979 l_approver_comments:=null;
1980 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1981 aso_debug_pub.ADD ('insert_approvers_gtt: approvertblin count'||approvertblin.count,1,'N');
1982 --pp_debug('Not in approvers table');
1983 end if;
1984 null;
1985
1986 end;
1987
1988
1989 -- Inserting into the temp table for display
1990 insert into ASO_APR_APPROVAL_DETAILS_TEMP
1991 (APPROVAL_DET_ID,OBJECT_APPROVAL_ID,APPROVER_PERSON_ID,APPROVER_USER_ID,APPROVER_SEQUENCE,
1992 APPROVER_STATUS,NAME,APPROVER_NAME,APPROVAL_COMMENTS,APPROVER_CATEGORY,APPROVER_CATEGORY_DESC,
1993 APPROVER_TYPE_DESC,SUB_LIST_DESC,ITEM_ID,ITEM_CLASS, ACTION_TYPE, ACTION_TYPE_ID,
1994 CHAIN_NUMBER,AUTHORITY)
1995 values
1996 (l_approval_Detail_id,p_object_appr_id,approvertblin(i).approver_person_id,approvertblin(i).approver_user_id,approvertblin(i).APPROVER_SEQUENCE,
1997 approvertblin(i).approver_status,approvertblin(i).name,approvertblin(i).approver_name,l_approver_comments,approvertblin(i).approver_category,approvertblin(i).approver_category_desc,
1998 approvertblin(i).approver_type_desc,approvertblin(i).SUB_LIST_DESC,approvertblin(i).ITEM_ID,approvertblin(i).ITEM_CLASS,
1999 approvertblin(i).ACTION_TYPE,approvertblin(i).ACTION_TYPE_ID,approvertblin(i).CHAIN_NUMBER,approvertblin(i).AUTHORITY
2000 );
2001
2002 end loop;
2003 select count(*) into l_count
2004 from ASO_APR_APPROVAL_DETAILS_TEMP
2005 where object_approval_id = p_object_appr_id;
2006
2007 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2008 aso_debug_pub.ADD ('insert_approvers_gtt: After inserting into temp table count'||l_count,1,'N');
2009 --pp_debug('insert_approvers_gtt: temp table count:'||l_count);
2010 end if;
2011
2012
2013 end if;
2014
2015 elsif (l_approval_status='CAN') or (l_approval_status='REJ') or (l_approval_status='APPR') then
2016
2017 for c1 in get_approver_details(p_object_appr_id) loop
2018
2019 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2020 aso_debug_pub.ADD ('insert_approvers_gtt: c1.name'||c1.name,1,'N');
2021 end if;
2022
2023
2024 If c1.name is null then -- for backward compatibility
2025 if c1.approver_user_id is not null then
2026 WF_DIRECTORY.GetUserName(ame_util.fndUserOrigSystem, c1.approver_user_id, l_name, l_displayname);
2027 else
2028 WF_DIRECTORY.GetUserName(ame_util.perOrigSystem, c1.approver_person_id, l_name, l_displayname);
2029 if l_name is null then -- POS
2030 WF_DIRECTORY.GetUserName(ame_util.posOrigSystem, c1.approver_person_id, l_name, l_displayname);
2031 end if;
2032 end if;
2033 else
2034 l_name:=c1.name;
2035 end if;
2036
2037 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2038 aso_debug_pub.ADD ('l_name'||l_name,1,'N');
2039 end if;
2040
2041
2042
2043 ame_approver_type_pkg.getApproverOrigSystemAndId(nameIn =>l_name,
2044 origSystemOut => tempOrigSystem,
2045 origSystemIdOut => tempOrigSystemId);
2046 l_displayname:=ame_approver_type_pkg.getApproverDisplayName(l_name);
2047 l_approver_type:=ame_approver_type_pkg.getOrigSystemDisplayName(tempOrigSystem);
2048 if (c1.approver_category IS NOT NULL) then
2049 OPEN c_appr_cat (c1.approver_category);
2050 FETCH c_appr_cat INTO l_approver_category_desc;
2051 CLOSE c_appr_cat;
2052 end if;
2053
2054 if (c1.authority is not null) then
2055 OPEN c_appr_sublist (c1.authority);
2056 FETCH c_appr_sublist INTO l_sub_list_desc;
2057 CLOSE c_appr_sublist;
2058 end if;
2059
2060 if (c1.action_type_id is not null) then
2061 OPEN c_appr_action (c1.action_type_id);
2062 FETCH c_appr_action INTO l_ACTION_TYPE;
2063 CLOSE c_appr_action;
2064 end if;
2065
2066 if (c1.approver_status is not null) then
2067 Select meaning
2068 into l_approval_status_desc
2069 from aso_lookups
2070 where lookup_type = 'ASO_APPROVER_STATUS'
2071 and lookup_code = c1.approver_status
2072 and enabled_flag='Y'
2073 And trunc(nvl(start_date_active, sysdate-1)) <= trunc(sysdate)
2074 And trunc(nvl(end_date_active, sysdate+1)) >= trunc(sysdate);
2075 --and sysdate between start_date_active and nvl(end_date_active,sysdate);
2076 end if;
2077
2078
2079
2080
2081
2082 insert into ASO_APR_APPROVAL_DETAILS_TEMP
2083 (APPROVAL_DET_ID,OBJECT_APPROVAL_ID,APPROVER_PERSON_ID,APPROVER_USER_ID,APPROVER_SEQUENCE,
2084 APPROVER_STATUS,NAME,APPROVER_NAME,APPROVAL_COMMENTS,APPROVER_CATEGORY,APPROVER_CATEGORY_DESC,
2085 APPROVER_TYPE_DESC,SUB_LIST_DESC,ITEM_ID,ITEM_CLASS, ACTION_TYPE, ACTION_TYPE_ID,
2086 CHAIN_NUMBER,AUTHORITY)
2087 values
2088 (c1.APPROVAL_DET_ID,p_object_appr_id,c1.approver_person_id,c1.approver_user_id,c1.APPROVER_SEQUENCE,
2089 l_approval_status_desc,l_name,l_displayname,c1.approver_comments,c1.approver_category,l_approver_category_desc,
2090 l_approver_type,l_sub_list_desc,c1.ITEM_ID,c1.ITEM_CLASS,l_ACTION_TYPE,c1.ACTION_TYPE_ID,c1.group_or_chain_id,c1.AUTHORITY
2091 );
2092
2093 end loop;
2094
2095 end if; -- l_approval_status
2096
2097 /*
2098 Select count(*) into l_count
2099 from ASO_APR_APPROVAL_DETAILS_TEMP
2100 where object_approval_id = p_object_appr_id;
2101
2102 Insert into test_pa_approval_debug_log values ('ASO_APR_APPROVAL_DETAILS_TEMP Record Count :'||l_count);
2103
2104 COMMIT;
2105 */
2106
2107 exception
2108
2109 WHEN fnd_api.g_exc_error
2110 THEN
2111 aso_utility_pvt.handle_exceptions (
2112 p_api_name => l_api_name,
2113 p_pkg_name => g_pkg_name,
2114 p_exception_level => fnd_msg_pub.g_msg_lvl_error,
2115 p_package_type => aso_utility_pvt.g_int,
2116 p_sqlcode => SQLCODE,
2117 p_sqlerrm => SQLERRM,
2118 x_msg_count => x_msg_count,
2119 x_msg_data => x_msg_data,
2120 x_return_status => x_return_status
2121 );
2122 WHEN fnd_api.g_exc_unexpected_error
2123 THEN
2124 aso_utility_pvt.handle_exceptions (
2125 p_api_name => l_api_name,
2126 p_pkg_name => g_pkg_name,
2127 p_exception_level => fnd_msg_pub.g_msg_lvl_unexp_error,
2128 p_package_type => aso_utility_pvt.g_int,
2129 p_sqlcode => SQLCODE,
2130 p_sqlerrm => SQLERRM,
2131 x_msg_count => x_msg_count,
2132 x_msg_data => x_msg_data,
2133 x_return_status => x_return_status
2134 );
2135 WHEN OTHERS
2136 THEN
2137 aso_utility_pvt.handle_exceptions (
2138 p_api_name => l_api_name,
2139 p_pkg_name => g_pkg_name,
2140 p_exception_level => aso_utility_pvt.g_exc_others,
2141 p_package_type => aso_utility_pvt.g_int,
2142 p_sqlcode => SQLCODE,
2143 p_sqlerrm => SQLERRM,
2144 x_msg_count => x_msg_count,
2145 x_msg_data => x_msg_data,
2146 x_return_status => x_return_status
2147 );
2148 end insert_approvers_gtt;
2149
2150 END aso_pa_apr_int;