1 PACKAGE BODY por_ame_approval_list AS
2 /* $Header: POXAPL2B.pls 120.75.12020000.5 2013/03/19 07:20:45 aacai ship $ */
3
4
5 -- Read the profile option that enables/disables the debug log
6 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
7 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
8 g_fnd_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
9
10 approvalListStr VARCHAR2(32767) := NULL;
11
12 /* private routines */
13
14 --------------------------------------------------------------------------------
15 --Start of Comments
16 --Name: getAllApprovers
17 --Function:
18 -- Get the latest approval list from AME
19 --Parameters:
20 --IN:
21 --pReqHeaderId
22 -- Requisition Header ID
23 --pAmeTransactionType
24 -- AME transaction type
25 --OUT:
26 --pApprovalListStr
27 -- Approval List concatenated in a string
28 --pApprovalListCount
29 -- Number of Approvers.
30 -- It has a value of 0, if the document does not require approval.
31 --End of Comments
32 --------------------------------------------------------------------------------
33 procedure getAllApprovers(pReqHeaderId IN NUMBER,
34 pAmeTransactionType IN VARCHAR2,
35 pApprovalListStr OUT NOCOPY VARCHAR2,
36 pApprovalListCount OUT NOCOPY NUMBER);
37
38
39 --------------------------------------------------------------------------------
40 --Start of Comments
41 --Name: getAbsolutePosition
42 --Function:
43 -- Return the absolute position given an input position.
44 -- The absolute position is added with an offset by the number of past approvers.
45 --Parameters:
46 --IN:
47 --pReqHeaderId
48 -- Requisition Header ID
49 --pAmeTransactionType
50 -- AME transaction type
51 --pPosition
52 -- This position is a relative value after the last of the past approvers.
53 --OUT:
54 -- None
55 --End of Comments
56 --------------------------------------------------------------------------------
57 function getAbsolutePosition(pReqHeaderId IN NUMBER,
58 pAmeTransactionType IN VARCHAR2,
59 pPosition IN NUMBER)
60 return number;
61
62 --------------------------------------------------------------------------------
63 --Start of Comments
64 --Name: marshalField
65 --Function:
66 -- Append the input string into approval list string
67 -- Replace the input string if it contains either a quote or delimiter char.
68 -- Another quote char is added in front of a quote or delimiter char.
69
70 --Parameters:
71 --IN:
72 --p_string
73 -- Input string
74
75 --OUT:
76 --None
77 --End of Comments
78 --------------------------------------------------------------------------------
79 PROCEDURE marshalField(p_string IN VARCHAR2,
80 p_quote_char IN VARCHAR2,
81 p_delimiter IN VARCHAR2);
82
83 --------------------------------------------------------------------------------
84 --Start of Comments
85 --Name: serializeApproversTable
86 --Function:
87 -- Serialize approver list table into a string representation
88 --Parameters:
89 --IN:
90 --approversTableIn
91 -- Approver list table
92 --pReqHeaderId
93 -- Requisition Header Id
94 --OUT:
95 --approverCount
96 -- Number of approvers
97 --hasApprovalAction
98 -- 'Y' if there is approver taken action
99 --End of Comments
100 --------------------------------------------------------------------------------
101
102 function serializeApproversTable(approversTableIn in ame_util.approversTable2,
103 reqHeaderId in NUMBER,
104 approverCount out nocopy number,
105 hasApprovalAction out nocopy varchar2)
106 return varchar2;
107
108 --------------------------------------------------------------------------------
109 --Start of Comments
110 --Name: getAmeTxnType
111 --Function:
112 -- Get the ame txn type given the document type and subtype
113 --Parameters:
114 --IN:
115 -- p_doc_type
116 -- Document Type
117 -- p_doc_subtype
118 -- Document subtype
119 -- p_org_id
120 -- Corresponding org id
121 --OUT:
122 -- ameTxnType
123 -- the corresponding ame txn type
124 --End of Comments
125 --------------------------------------------------------------------------------
126 function getAmeTxnType (p_doc_type in VARCHAR2,
127 p_doc_subtype in VARCHAR2,
128 p_org_id in NUMBER)
129 return varchar2;
130
131
132 /* public API */
133 --------------------------------------------------------------------------------
134 --Start of Comments
135 --Name: change_first_approver
136 --Function:
137 -- Call AME API to get the new approval list for a requisition.
138 -- The new approval list is based on the person ID of a new first approver.
139 --Parameters:
140 --IN:
141 -- pReqHeaderId Requisition Header ID
142 -- pPersonId Person ID of a new first approver
143 --OUT:
144 -- pApprovalListStr Approval List concatenated in a string
145 -- pApprovalListCount Number of Approvers.
146 -- It has a value of 0, if the document does not require approval.
147 -- pQuoteChar Quote Character, used for escaping purpose in tokenization
148 -- pFieldDelimiter Field Delimiter, used for delimiting list string into elements.
149 --End of Comments
150 --------------------------------------------------------------------------------
151 procedure change_first_approver( pReqHeaderId IN NUMBER,
152 pPersonId IN NUMBER,
153 pApprovalListStr OUT NOCOPY VARCHAR2,
154 pApprovalListCount OUT NOCOPY NUMBER,
155 pQuoteChar OUT NOCOPY VARCHAR2,
156 pFieldDelimiter OUT NOCOPY VARCHAR2
157 ) IS
158
159 l_api_name varchar2(50):= 'change_first_approver';
160 tmpApprover ame_util.approverRecord2;
161 ameTransactionType po_document_types.ame_transaction_type%TYPE;
162
163 approverList ame_util.approversTable2;
164 l_process_out VARCHAR2(10);
165 currentFirstApprover ame_util.approverRecord2;
166
167 begin
168
169 if g_fnd_debug = 'Y' then
170 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
171 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering change_first_approver...');
172 END IF;
173 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
174 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pReqHeaderId :' || pReqHeaderId );
175 END IF;
176 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
177 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pPersonId :' || pPersonId );
178 END IF;
179 end if;
180
181 getAmeTransactionType(pReqHeaderId => pReqHeaderId,
182 pAmeTransactionType => ameTransactionType);
183
184 pQuoteChar :=quoteChar;
185 pFieldDelimiter :=fieldDelimiter;
186
187 if g_fnd_debug = 'Y' then
188 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
189 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame api ame_api2.getAllApprovers7() to get the list of approvers from AME.');
190 END IF;
191 end if;
192
193 -- get the current approvers list from AME.
194 ame_api2.getAllApprovers7( applicationIdIn=>applicationId,
195 transactionIdIn=>pReqHeaderId,
196 transactionTypeIn=>ameTransactionType,
197 approvalProcessCompleteYNOut => l_process_out,
198 approversOut=>approverList
199 );
200
201 if g_fnd_debug = 'Y' then
202 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
203 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Retrieved the list of approvers from AME using ame_api2.getAllApprovers7()');
204 END IF;
205 end if;
206
207 -- Once we get the approvers list from AME, we iterate through the approvers list,
208 -- to find out the current first authority approver.
209 for i in 1 .. approverList.count loop
210 if( approverList(i).authority = ame_util.authorityApprover
211 and approverList(i).approval_status is null
212 and approverList(i).api_insertion = 'N'
213 and approverList(i).group_or_chain_id < 3 ) then
214 currentFirstApprover := approverList(i) ;
215 if g_fnd_debug = 'Y' then
216 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
217 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Found the first authority approver...' || currentFirstApprover.name );
218 END IF;
219 end if;
220 exit;
221 end if;
222 end loop;
223
224 -- Once we get the current first authority approver, we check for the current first authority approver's action type(POS/PER).
225 -- If the first approver record is of position hierarchy action type,
226 -- then we need to find out the position id of the given approver and frame the new approver record.
227 -- We also set the columns first_position_id and first_approver_id in po_requisition_headers_all
228 -- If the first approver record is of emp supervisor action type,
229 -- then we simply frame the new approver record from the input parameters.
230 -- FND users cannot be set as first authority approver. So no need to check for the value 'FND'
231
232 if currentFirstApprover.orig_system = ame_util.posOrigSystem then
233
234 if g_fnd_debug = 'Y' then
235 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
236 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'First record is of type Position Hierarchy...' );
237 END IF;
238 end if;
239 tmpApprover.orig_system := ame_util.posOrigSystem;
240 SELECT position_id into tmpApprover.orig_system_id FROM PER_ALL_ASSIGNMENTS_F pa
241 WHERE pa.person_id = pPersonId and pa.primary_flag = 'Y' and pa.assignment_type in ('E','C')
242 and pa.position_id is not null and pa.assignment_status_type_id not in (
243 select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
244 and TRUNC ( pa.effective_start_date )
245 <= TRUNC(SYSDATE) AND NVL(pa.effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE);
246
247 if g_fnd_debug = 'Y' then
248 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
249 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Got users position_id :' || tmpApprover.orig_system_id );
250 END IF;
251 end if;
252
253 UPDATE po_requisition_headers_all
254 SET first_position_id = tmpApprover.orig_system_id, first_approver_id = pPersonId
255 WHERE requisition_header_id = pReqHeaderId;
256
257 if g_fnd_debug = 'Y' then
258 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
259 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Inserted the first_position_id and first_approver_id columns.' );
260 END IF;
261 end if;
262
263 IF tmpApprover.orig_system_id IS NULL THEN
264 raise_application_error(-20001, 'User is not associated to any position. ');
265 END IF;
266 else
267 if g_fnd_debug = 'Y' then
268 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
269 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' First record is of type Emp-Sup Hierarchy...' );
270 END IF;
271 end if;
272 tmpApprover.orig_system := ame_util.perOrigSystem;
273 tmpApprover.orig_system_id := pPersonId;
274 end if;
275
276 -- set the mandatory default attributes for the first authority approver.
277 tmpApprover.authority := ame_util.authorityApprover;
278 tmpApprover.api_insertion := ame_util.apiAuthorityInsertion;
279 tmpApprover.approval_status := ame_util.nullStatus;
280 tmpApprover.approver_category := ame_util.approvalApproverCategory ;
281 tmpApprover.item_class := currentFirstApprover.item_class ;
282 tmpApprover.item_id := currentFirstApprover.item_id ;
283 tmpApprover.action_type_id := currentFirstApprover.action_type_id ;
284 tmpApprover.group_or_chain_id := currentFirstApprover.group_or_chain_id ;
285
286 -- retrieve the name from wf_roles table for the given orig_system and orig_system_id values.
287 -- this name field does not refer to neither employee name nor position name
288 -- this name filed is the mandatory key field for the approverrecord2. We should pass this to ame. Otherwise ame will throw exception.
289
290 -- bug 9395808
291 -- fix The problem of just picking up the first approver randomly when an approver is manually
292 -- inserted in the chain in iProc.
293 -- it should pick up the approver with earliest start date.
294 -- also validating the approver status
295 SELECT name into tmpApprover.name FROM (
296 SELECT name
297 FROM wf_roles
298 WHERE orig_system = tmpApprover.orig_system and orig_system_id = tmpApprover.orig_system_id
299 and status = 'ACTIVE' and trunc (nvl( expiration_date, sysdate)) >= trunc(sysdate)
300 ORDER BY start_date
301 )
302 WHERE rownum = 1;
303
304 IF tmpApprover.name IS NULL THEN
305 raise_application_error(-20001, 'Record Not Found in WF_ROLES for the orig_system_id :' ||
306 tmpApprover.orig_system_id || ' -- orig_system :' || tmpApprover.orig_system );
307 END IF;
308
309 -- call the ame api to set the first authority approver.
310 -- tmpApprover is the new first authority approver record.
311
312 if g_fnd_debug = 'Y' then
313 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
314 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api2.setFirstAuthorityApprover()...' );
315 END IF;
316 end if;
317
318 -- set a save point for failure rollback
319 SAVEPOINT CHANGE_FIRST_APPROVER;
320
321 ame_api2.setFirstAuthorityApprover( applicationIdIn => applicationId,
322 transactionIdIn => pReqHeaderId,
323 approverIn => tmpApprover,
324 transactionTypeIn => ameTransactionType,
325 clearChainStatusYNIn => ame_util.booleanTrue
326 );
327 if g_fnd_debug = 'Y' then
328 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
329 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api2.setFirstAuthorityApprover()...' );
330 END IF;
331 end if;
332
333 -- Once we change the first authority approver, then get the updated approvers list from ame.
334 getAllApprovers(pReqHeaderId, ameTransactionType, pApprovalListStr, pApprovalListCount);
335
336 if g_fnd_debug = 'Y' then
337 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
338 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving change_first_approver...');
339 END IF;
340 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
341 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListStr :' || pApprovalListStr);
342 END IF;
343 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
344 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListCount :' || pApprovalListCount);
345 END IF;
346 end if;
347
348 exception
349 when NO_DATA_FOUND then
350 pApprovalListCount := 0;
351 pApprovalListStr := 'NO_DATA_FOUND';
352 if g_fnd_debug = 'Y' then
353 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
354 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
355 l_api_name || '.NO_DATA_FOUND', 'NO_DATA_FOUND');
356 END IF;
357 end if;
358 when others then
359 pApprovalListCount := 0;
360 pApprovalListStr := 'EXCEPTION:' || sqlerrm;
361 if g_fnd_debug = 'Y' then
362 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
363 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
364 l_api_name || '.others_exception', sqlerrm);
365 END IF;
366 end if;
367 ROLLBACK TO CHANGE_FIRST_APPROVER;
368 end;
369
370 --------------------------------------------------------------------------------
371 --Start of Comments
372 --Name: insert_approver
373 --Function:
374 -- Call AME API to insert an approver into a specific position.
375 -- The new approval list is retrieved after the insertion.
376
377 --Parameters:
378 --IN:
379 -- pReqHeaderId Requisition Header ID
380 -- pPersonId Person ID of a new approver
381 -- pAuthority AME Authority type of the new approver
382 -- pInsertionType AME Insertion type of the new approver
383 -- pPosition Position to be inserted
384 -- pApproverNumber Exact insertion psition
385 -- pInsertionType Where to insert, after or before
386 -- pApproverName Username of the approver
387 --OUT:
388 -- pApprovalListStr Approval List concatenated in a string
389 -- pApprovalListCount Number of Approvers.
390 -- It has a value of 0, if the document does not require approval.
391 -- pQuoteChar Quote Character, used for escaping purpose in tokenization
392 -- pFieldDelimiter Field Delimiter, used for delimiting list string into elements.
393 --End of Comments
394 --------------------------------------------------------------------------------
395 procedure insert_approver( pReqHeaderId IN NUMBER,
396 pPersonId IN NUMBER,
397 pAuthority IN VARCHAR2,
398 pApproverCategory IN VARCHAR2,
399 pPosition IN NUMBER,
400 pApproverNumber IN NUMBER,
401 pInsertionType IN VARCHAR2,
402 pApproverName IN VARCHAR2,
403 pApprovalListStr OUT NOCOPY VARCHAR2,
404 pApprovalListCount OUT NOCOPY NUMBER,
405 pQuoteChar OUT NOCOPY VARCHAR2,
406 pFieldDelimiter OUT NOCOPY VARCHAR2
407 ) IS
408 l_api_name varchar2(50):= 'insert_approver';
409
410 tmpApprover ame_util.approverRecord2;
411 insertOrder ame_util.insertionRecord2;
412 upperLimit number;
413 approverList ame_util.approversTable2;
414 hasAvailableOrder boolean := false;
415 E_NO_AVAILABLE_INSERTION EXCEPTION;
416 ameTransactionType po_document_types.ame_transaction_type%TYPE;
417 absolutePosition number;
418 availableInsertionList ame_util.insertionsTable2;
419 l_process_out VARCHAR2(10);
420 l_group_or_chain_id NUMBER := 0;
421 l_action_type_id NUMBER := 0;
422 l_counter NUMBER := 0;
423 l_approver_position NUMBER := 0;
424 hasHiddenApprovers boolean := false; -- Flag to check hidden approvers
425 l_insertion_type varchar2(1); -- To store insertion type
426
427 begin
428
429 if g_fnd_debug = 'Y' then
430 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
431 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering insert_approver...');
432 END IF;
433 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
434 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pReqHeaderId :' || pReqHeaderId );
435 END IF;
436 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
437 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pPersonId :' || pPersonId );
438 END IF;
439 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
440 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pAuthority :' || pAuthority );
441 END IF;
442 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
443 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pPosition :' || pPosition );
444 END IF;
445 end if;
446
447 getAmeTransactionType(pReqHeaderId => pReqHeaderId,
448 pAmeTransactionType => ameTransactionType);
449
450 pQuoteChar :=quoteChar;
451 pFieldDelimiter :=fieldDelimiter;
452
453 /*
454 past approvers are not in middle tier
455 so need to deduce the real position
456 */
457
458
459 -- Frame the approverRecord2 from the input parameters.
460 -- Set the default mandatory attributes also.
461 tmpApprover.orig_system := ame_util.perOrigSystem;
462 tmpApprover.orig_system_id := pPersonId;
463 tmpApprover.authority := pAuthority;
464 tmpApprover.api_insertion := ame_util.apiInsertion;
465 tmpApprover.approver_category := pApproverCategory;
466 tmpApprover.approval_status := ame_util.nullStatus;
467 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
468 transactionIdIn => pReqHeaderId,
469 transactionTypeIn => ameTransactionType,
470 approvalProcessCompleteYNOut => l_process_out,
471 approversOut => approverList
472 );
473
474 /* Check if there is any supressed or repeated approver. As soon as we met any, set hasHiddenApprovers true and exit
475 Added extra statuses to check if there is any hidden approver in approver checkout flow */
476 For i In 1 .. approverList.count LOOP
477 IF (approverList(i).approval_status IN (ame_util.repeatedStatus,ame_util.suppressedStatus,
478 ame_util.notifiedByRepeatedStatus, ame_util.approvedByRepeatedStatus,
479 ame_util.rejectedByRepeatedStatus, ame_util.approvedStatus,
480 ame_util.rejectStatus )) THEN
481 hasHiddenApprovers := true;
482 EXIT;
483 END IF;
484 END LOOP;
485
486 /* If we have any superessed approver then execute this code ,otherwise pass the pApproverNumber to absoluteposition
487 Approvers with Approval_status 'REPEATED' OR 'SUPPRESSED'is not considered in getAllApprovers procedure
488 Second condition will be used to avoid this code when we are inserting first approver */
489 IF ( hasHiddenApprovers = true AND pApproverName IS NOT NULL ) THEN
490 if g_fnd_debug = 'Y' then
491 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
492 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Found Repeated/Skipped Approvers!' );
493 END IF;
494 end if;
495
496 for i in 1 .. approverList.count loop
497 /* Find approver with given name and status NULL or not REPEATED or not SUPPRESSED
498 Status XXXByRepeatedStatus is used because 'repeated' status gets modified to XXXByRepeatedStatus
499 status when other approvers in group or PCOA gets notified or approve or reject .Also group can have pending as well
500 as approved approvers */
501 IF ( pApproverName = approverList(i).name AND ( approverList(i).approval_status IS NULL
502 OR approverList(i).approval_status NOT IN (ame_util.repeatedStatus,ame_util.suppressedStatus,
503 ame_util.notifiedByRepeatedStatus, ame_util.approvedByRepeatedStatus,
504 ame_util.rejectedByRepeatedStatus, ame_util.approvedStatus,
505 ame_util.rejectStatus))) THEN
506 l_approver_position := i;
507 /* Once we locate the approver terminate the loop */
508 EXIT;
509 END IF;
510 end loop;
511 l_group_or_chain_id := approverList(l_approver_position).group_or_chain_id;
512 l_action_type_id := approverList(l_approver_position).action_type_id;
513 l_insertion_type := approverList(l_approver_position).api_insertion; -- Set insertion type which will be used to identify adhoc approver
514
515 /* Condition 1.1: If we are inserting after an approver who belongs to a group
516 Condition 1.2: If we are inserting after an approver who is either adhoc or belongs to COA
517 Condition 2.1: If we are inserting approver before an approver who belongs to a group
518 Condition 2.2: If we are inserting before an approver who is either adhoc or belongs to COA
519 In each case we have to take care of approvers who are repeated or deleted (suppressed)
520 And if there are approvers like this then either increment or decrement positionId depending upon whether
521 we are inserting after or before */
522 IF pInsertionType = 'A' THEN
523 l_counter := l_approver_position +1;
524 /* Check if the approver is adhoc or not. If adhoc then we need not to look for any repeated or supressed approver.
525 Checking it for 'after' case and not 'before' because insertion before adhoc is only possible if adhoc is inserted at
526 the left end of chain. And in that case action_type_id is enough to locate exact position of insertion
527 Requirement of these extra statuses have beed explained earlier. Not included condition check of 'approvedStatus'
528 for 'before' case, because we dont provide option to insert before approver who have approved */
529 IF l_insertion_type <> ame_util.apiInsertion THEN
530 if l_group_or_chain_id > 1 then
531 while( l_counter <= approverList.count AND approverList(l_counter).group_or_chain_id = l_group_or_chain_id
532 AND approverList(l_counter).approval_status IN (ame_util.repeatedStatus,ame_util.suppressedStatus,
533 ame_util.notifiedByRepeatedStatus, ame_util.approvedByRepeatedStatus, ame_util.rejectedByRepeatedStatus,
534 ame_util.approvedStatus) ) LOOP
535 l_counter := l_counter + 1;
536 END LOOP;
537 else
538 while( l_counter <= approverList.count AND approverList(l_counter).action_type_id = l_action_type_id
539 AND approverList(l_counter).approval_status IN (ame_util.repeatedStatus,ame_util.suppressedStatus,
540 ame_util.notifiedByRepeatedStatus, ame_util.approvedByRepeatedStatus, ame_util.rejectedByRepeatedStatus,
541 ame_util.approvedStatus) ) LOOP
542 l_counter := l_counter + 1;
543 END LOOP;
544 end if;
545 END IF;
546 absolutePosition := l_counter;
547 ELSE
548 l_counter := l_approver_position - 1;
549 if l_group_or_chain_id > 1 then
550 while( l_counter > 0 AND approverList(l_counter).group_or_chain_id = l_group_or_chain_id
551 AND approverList(l_counter).approval_status IN (ame_util.repeatedStatus,ame_util.suppressedStatus,
552 ame_util.notifiedByRepeatedStatus, ame_util.approvedByRepeatedStatus, ame_util.rejectedByRepeatedStatus)) LOOP
553 l_counter := l_counter - 1;
554 END LOOP;
555 else
556 while( l_counter > 0 AND approverList(l_counter).action_type_id = l_action_type_id
557 AND approverList(l_counter).approval_status IN (ame_util.repeatedStatus,ame_util.suppressedStatus,
558 ame_util.notifiedByRepeatedStatus, ame_util.approvedByRepeatedStatus, ame_util.rejectedByRepeatedStatus )) LOOP
559 l_counter := l_counter - 1;
560 END LOOP;
561 end if;
562 absolutePosition := l_counter + 1;
563 END IF;
564
565 /* If we dont have any repeated approver */
566 ELSE
567 absolutePosition := pApproverNumber;
568 END IF;
569
570 -- Verify the available insertions list from ame by giving the position number
571 -- Ame will give the output available list if the insertion is possible.
572 if g_fnd_debug = 'Y' then
573 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
574 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' final absolutePosition :' || absolutePosition );
575 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api3.getAvailableInsertions()..');
576 END IF;
577 end if;
578
579 ame_api3.getAvailableInsertions( applicationIdIn => applicationId,
580 transactionIdIn => pReqHeaderId,
581 positionIn => absolutePosition,
582 transactionTypeIn => ameTransactionType,
583 availableInsertionsOut => availableInsertionList
584 );
585
586 if g_fnd_debug = 'Y' then
587 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
588 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api3.getAvailableInsertions()..');
589 END IF;
590 end if;
591
592 -- Iterate through the available list and find out the authority approver insertion location.
593 -- Once we get the exact available location, then simply populate the approver record's mandatory fields.
594
595 IF(approverList.count = 0) THEN
596 FOR i IN 1 .. availableInsertionList.COUNT LOOP
597 IF availableInsertionList(i).order_type IN
598 (ame_util.absoluteOrder,ame_util.afterApprover, ame_util.beforeApprover) AND
599 availableInsertionList(i).api_insertion = tmpApprover.api_insertion AND
600 availableInsertionList(i).authority = tmpApprover.authority THEN
601
602 insertOrder := availableInsertionList(i);
603
604 tmpApprover.item_class := insertOrder.item_class;
605 tmpApprover.item_id := insertOrder.item_id;
606 tmpApprover.action_type_id := insertOrder.action_type_id;
607 tmpApprover.group_or_chain_id := insertOrder.group_or_chain_id;
608 tmpApprover.api_insertion := insertOrder.api_insertion;
609 tmpApprover.authority := insertOrder.authority;
610
611 -- retrieve the name from wf_roles table for the given orig_system and orig_system_id values.
612 -- this name field does not refer to neither employee name nor position name
613 -- this name filed is the mandatory key field for the approverrecord2. We should pass this to ame. Otherwise ame will throw exception.
614 -- bug 9395808
615 -- fix The problem of just picking up the first approver randomly when an approver is manually
616 -- inserted in the chain in iProc.
617 -- it should pick up the approver with earliest start date.
618 -- also validating the approver status
619 SELECT name into tmpApprover.name FROM (
620 SELECT name
621 FROM wf_roles
622 WHERE orig_system = tmpApprover.orig_system and orig_system_id = tmpApprover.orig_system_id
623 and status = 'ACTIVE' and trunc (nvl( expiration_date, sysdate)) >= trunc(sysdate)
624 ORDER BY start_date
625 )
626 WHERE rownum = 1;
627
628 IF tmpApprover.name IS NULL THEN
629 raise_application_error(-20001, 'Record Not Found in WF_ROLES for the orig_system_id :' ||
630 tmpApprover.orig_system_id || ' -- orig_system :' || tmpApprover.orig_system );
631 END IF;
632
633 if g_fnd_debug = 'Y' then
634 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
635 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Found the available position also to insert..');
636 END IF;
637 end if;
638
639 hasAvailableOrder := true;
640 EXIT;
641
642 END IF;
643 END LOOP;
644 ELSE
645 FOR i IN 1 .. availableInsertionList.COUNT LOOP
646 /* We can insert after an approver if we have order_type in absoluteOrder,afterApprover or beforeApprover
647 And for insertion before an approver we should have order_type as beforeApprover */
648 IF ((pInsertionType = 'A' and availableInsertionList(i).order_type IN ( ame_util.absoluteOrder,ame_util.afterApprover, ame_util.beforeApprover))
649 OR (pInsertionType = 'B' and availableInsertionList(i).order_type = ame_util.beforeApprover)) AND
650 availableInsertionList(i).api_insertion = tmpApprover.api_insertion AND
651 availableInsertionList(i).authority = tmpApprover.authority THEN
652
653 insertOrder := availableInsertionList(i);
654
655 tmpApprover.item_class := insertOrder.item_class;
656 tmpApprover.item_id := insertOrder.item_id;
657 tmpApprover.action_type_id := insertOrder.action_type_id;
658 tmpApprover.group_or_chain_id := insertOrder.group_or_chain_id;
659 tmpApprover.api_insertion := insertOrder.api_insertion;
660 tmpApprover.authority := insertOrder.authority;
661
662 -- retrieve the name from wf_roles table for the given orig_system and orig_system_id values.
663 -- this name field does not refer to neither employee name nor position name
664 -- this name filed is the mandatory key field for the approverrecord2. We should pass this to ame. Otherwise ame will throw exception.
665 -- bug 9395808
666 -- fix The problem of just picking up the first approver randomly when an approver is manually
667 -- inserted in the chain in iProc.
668 -- it should pick up the approver with earliest start date.
669 -- also validating the approver status
670 SELECT name into tmpApprover.name FROM (
671 SELECT name
672 FROM wf_roles
673 WHERE orig_system = tmpApprover.orig_system and orig_system_id = tmpApprover.orig_system_id
674 and status = 'ACTIVE' and trunc (nvl( expiration_date, sysdate)) >= trunc(sysdate)
675 ORDER BY start_date
676 )
677 WHERE rownum = 1;
678
679 IF tmpApprover.name IS NULL THEN
680 raise_application_error(-20001, 'Record Not Found in WF_ROLES for the orig_system_id :' ||
681 tmpApprover.orig_system_id || ' -- orig_system :' || tmpApprover.orig_system );
682 END IF;
683
684 if g_fnd_debug = 'Y' then
685 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
686 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Found the available position also to insert..');
687 END IF;
688 end if;
689
690 hasAvailableOrder := true;
691 EXIT;
692
693 END IF;
694 END LOOP;
695 END IF;
696 -- Call ame api to insert an approver if the hasAvailableOrder = true
697 -- tmpApprover will be the new approver record and will be inserted in the absolutePosition.
698 if (hasAvailableOrder) then
699
700 if g_fnd_debug = 'Y' then
701 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
702 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api3.insertApprover()..');
703 END IF;
704 end if;
705
706 ame_api3.insertApprover( applicationIdIn => applicationId,
707 transactionIdIn => pReqHeaderId,
708 approverIn => tmpApprover,
709 positionIn => absolutePosition,
710 insertionIn => insertOrder,
711 transactionTypeIn => ameTransactionType
712 );
713
714 if g_fnd_debug = 'Y' then
715 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
716 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api3.insertApprover()..');
717 END IF;
718 end if;
719 -- Once we insert an approver to ame, get the updated list of approvers from ame.
720 getAllApprovers(pReqHeaderId, ameTransactionType, pApprovalListStr, pApprovalListCount);
721
722 if g_fnd_debug = 'Y' then
723 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
724 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving insert_approver...');
725 END IF;
726 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
727 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListStr :' || pApprovalListStr);
728 END IF;
729 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
730 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListCount :' || pApprovalListCount);
731 END IF;
732 end if;
733
734 return;
735 end if;
736
737 raise E_NO_AVAILABLE_INSERTION;
738
739 exception
740 when E_NO_AVAILABLE_INSERTION then
741 pApprovalListCount := 0;
742 pApprovalListStr := 'EXCEPTION-E_NO_AVAILABLE_INSERTION';
743 if g_fnd_debug = 'Y' then
744 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
745 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
746 l_api_name || '.E_NO_AVAILABLE_INSERTION', 'No available insertion order');
747 END IF;
748 end if;
749 when NO_DATA_FOUND then
750 pApprovalListCount := 0;
751 pApprovalListStr := 'NO_DATA_FOUND';
752 if g_fnd_debug = 'Y' then
753 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
754 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
755 l_api_name || '.NO_DATA_FOUND', 'NO_DATA_FOUND');
756 END IF;
757 end if;
758 when others then
759 pApprovalListCount := 0;
760 pApprovalListStr := 'EXCEPTION';
761 if g_fnd_debug = 'Y' then
762 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
763 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
764 l_api_name || '.others_exception', sqlerrm);
765 END IF;
766 end if;
767 end;
768
769 --------------------------------------------------------------------------------
770 --Start of Comments
771 --Name: delete_approver
772 --Function:
773 -- Call AME API to delete an approver from the current approver list.
774 -- The new approval list is retrieved after the deletion.
775
776 --Parameters:
777 --IN:
778 -- pReqHeaderId Requisition Header ID
779 -- pPersonId Person ID of the approver to be deleted
780 --OUT:
781 -- pApprovalListStr Approval List concatenated in a string
782 -- pApprovalListCount Number of Approvers.
783 -- It has a value of 0, if the document does not require approval.
784 -- pQuoteChar Quote Character, used for escaping purpose in tokenization
785 -- pFieldDelimiter Field Delimiter, used for delimiting list string into elements.
786 --End of Comments
787 --------------------------------------------------------------------------------
788 procedure delete_approver( pReqHeaderId IN NUMBER,
789 pPersonId IN NUMBER,
790 pOrigSystem IN VARCHAR2,
791 pOrigSystemId IN NUMBER,
792 pRecordName IN VARCHAR2,
793 pAuthority IN VARCHAR2,
794 pApprovalListStr OUT NOCOPY VARCHAR2,
795 pApprovalListCount OUT NOCOPY NUMBER,
796 pQuoteChar OUT NOCOPY VARCHAR2,
797 pFieldDelimiter OUT NOCOPY VARCHAR2
798 ) IS
799
800 l_api_name varchar2(50):= 'delete_approver';
801 tmpApprover ame_util.approverRecord2;
802 ameTransactionType po_document_types.ame_transaction_type%TYPE;
803
804 approverList ame_util.approversTable2;
805 l_process_out VARCHAR2(10);
806 l_first_position_id NUMBER;
807
808 begin
809
810 if g_fnd_debug = 'Y' then
811 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
812 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering delete_approver...');
813 END IF;
814 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
815 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pReqHeaderId :' || pReqHeaderId );
816 END IF;
817 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
818 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pPersonId :' || pPersonId );
819 END IF;
820 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
821 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pOrigSystem :' || pOrigSystem );
822 END IF;
823 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
824 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pOrigSystemId :' || pOrigSystemId );
825 END IF;
826 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
827 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pRecordName :' || pRecordName );
828 END IF;
829 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
830 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pAuthority :' || pAuthority );
831 END IF;
832 end if;
833
834 getAmeTransactionType(pReqHeaderId => pReqHeaderId,
835 pAmeTransactionType => ameTransactionType);
836
837 pQuoteChar :=quoteChar;
838 pFieldDelimiter :=fieldDelimiter;
839
840 -- Frame the approverRecord2 from the input parameters.
841 tmpApprover.orig_system_id := pOrigSystemId;
842 tmpApprover.orig_system := pOrigSystem;
843 tmpApprover.name := pRecordName;
844 tmpApprover.authority := pAuthority;
845
846 if g_fnd_debug = 'Y' then
847 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
848 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api2.getAllApprovers7()..');
849 END IF;
850 end if;
851
852 -- get the AME approvers list
853 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
854 transactionIdIn => pReqHeaderId,
855 transactionTypeIn => ameTransactionType,
856 approvalProcessCompleteYNOut => l_process_out,
857 approversOut => approverList
858 );
859
860 if g_fnd_debug = 'Y' then
861 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
862 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api2.getAllApprovers7()..');
863 END IF;
864 end if;
865
866 -- check for the given approver details in the approval list
867 -- the approver should be there in the approval list
868 -- once we get the approver details in the list, simply copy the record to tmpApprover approverRecord2.
869 for i in 1 .. approverList.count loop
870
871 if g_fnd_debug = 'Y' then
872 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
873 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Searching through list of Approvers for match');
874 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' name: ' || tmpApprover.name || ' ? ' || approverList(i).name);
875 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' orig_system: ' || tmpApprover.orig_system || ' ? ' || approverList(i).orig_system);
876 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' orig_system_id: ' || tmpApprover.orig_system_id || ' ? ' || approverList(i).orig_system_id);
877 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' authority: ' || tmpApprover.authority || ' ? ' || approverList(i).authority);
878 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' status is NULL or NOT SUPPRESSED: ' || approverList(i).approval_status);
879 END IF;
880 end if;
881
882 if( approverList(i).name = tmpApprover.name and
883 approverList(i).orig_system = tmpApprover.orig_system and
884 approverList(i).orig_system_id = tmpApprover.orig_system_id and
885 approverList(i).authority = tmpApprover.authority and
886 ( approverList(i).approval_status is null or
887 approverList(i).approval_status = ame_util.nullStatus or
888 approverList(i).approval_status <> ame_util.suppressedStatus) ) then
889
890 if g_fnd_debug = 'Y' then
891 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
892 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Found the approver to be deleted also in the list...');
893 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Approver Name = ' || tmpApprover.name);
894 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Approver Action Type Id = ' || approverList(i).action_type_id);
895 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Approver Status = ' || approverList(i).approval_status);
896 END IF;
897 end if;
898 tmpApprover := approverList(i) ;
899 exit;
900
901 end if;
902 end loop;
903
904 -- Call the ame api to delete the approver.
905 -- tmpApprover will be deleted from the approver list
906 if g_fnd_debug = 'Y' then
907 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
908 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api3.suppressApprover()..');
909 END IF;
910 end if;
911 ame_api3.suppressApprover( applicationIdIn => applicationId,
912 transactionIdIn => pReqHeaderId,
913 approverIn => tmpApprover,
914 transactionTypeIn => ameTransactionType
915 );
916
917 if g_fnd_debug = 'Y' then
918 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
919 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api3.suppressApprover()..');
920 END IF;
921 end if;
922
923 if (tmpApprover.orig_system=ame_util.posOrigSystem) THEN
924
925 SELECT FIRST_POSITION_ID
926 INTO l_first_position_id
927 FROM po_requisition_headers_all
928 WHERE requisition_header_id = pReqHeaderId;
929
930 if g_fnd_debug = 'Y' then
931 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
932 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'POS Record is being suppressed.');
933 END IF;
934 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
935 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Stored FIRST_POSITION_ID = ' || l_first_position_id);
936 END IF;
937 end if;
938
939 if (tmpApprover.orig_system_id=l_first_position_id) then
940 -- we are suppressing the First Position Approver;
941 -- thus, we need to clear the values from the req headers table.
942
943 UPDATE po_requisition_headers_all
944 SET first_position_id = NULL, first_approver_id = NULL
945 WHERE requisition_header_id = pReqHeaderId;
946
947 if g_fnd_debug = 'Y' then
948 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
949 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name,
950 'Cleared first_position_id and first_approver_id from req_header table.');
951 END IF;
952 end if;
953
954 END IF;
955 END IF;
956
957 -- Once we delete an approver from the approval list, get the updated approval list from ame.
958 getAllApprovers(pReqHeaderId, ameTransactionType, pApprovalListStr, pApprovalListCount);
959
960 if g_fnd_debug = 'Y' then
961 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
962 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving delete_approver...');
963 END IF;
964 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
965 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListStr :' || pApprovalListStr);
966 END IF;
967 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
968 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListCount :' || pApprovalListCount);
969 END IF;
970 end if;
971
972 exception
973 when NO_DATA_FOUND then
974 pApprovalListCount := 0;
975 pApprovalListStr := 'NO_DATA_FOUND';
976 if g_fnd_debug = 'Y' then
977 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
978 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
979 l_api_name || '.NO_DATA_FOUND', 'NO_DATA_FOUND');
980 END IF;
981 end if;
982 when others then
983 pApprovalListCount := 0;
984 pApprovalListStr := 'EXCEPTION:' || sqlerrm;
985 if g_fnd_debug = 'Y' then
986 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
987 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
988 l_api_name || '.others_exception', sqlerrm);
989 END IF;
990 end if;
991 end;
992
993 --------------------------------------------------------------------------------
994 --Start of Comments
995 --Name: get_ame_approval_list
996 --Function:
997 -- Call AME API to build the latest approver list.
998 -- If the approver list should be defaulted,
999 -- Then the history of the list will be cleared before building a list, except the following case:
1000 -- If the defaulting is for opening saved cart:
1001 -- (an incompleted requisition which did not have past approval action)
1002
1003 --Parameters:
1004 --IN:
1005 -- pReqHeaderId Requisition Header ID
1006 -- pDefaultFlag Value 1 if approver list should be defaulted.
1007 -- Value -1 if approver list is to be rebuilt for iP approver checkout
1008 -- null value if the latest approver list is to be
1009 -- retrieved in all other cases.
1010
1011 --OUT:
1012 -- pApprovalListStr Approval List concatenated in a string
1013 -- pApprovalListCount Number of Approvers.
1014 -- It has a value of 0, if the document does not require approval.
1015 -- pQuoteChar Quote Character, used for escaping purpose in tokenization
1016 -- pFieldDelimiter Field Delimiter, used for delimiting list string into elements.
1017 -- pApprovalAction 'Y' if there were approvers taken action on the document.
1018 -- Those approvers are not included in the list,
1019 -- Only the future approvers are returned.
1020 --End of Comments
1021 --------------------------------------------------------------------------------
1022 procedure get_ame_approval_list(pReqHeaderId IN NUMBER,
1023 pDefaultFlag IN NUMBER,
1024 pApprovalListStr OUT NOCOPY VARCHAR2,
1025 pApprovalListCount OUT NOCOPY NUMBER,
1026 pQuoteChar OUT NOCOPY VARCHAR2,
1027 pFieldDelimiter OUT NOCOPY VARCHAR2,
1028 pApprovalAction OUT NOCOPY VARCHAR2
1029 ) IS
1030
1031 l_api_name varchar2(50):= 'get_ame_approval_list';
1032 --approverList ame_util.approversTable;
1033 clearListForSavedCart varchar2(1) := 'N';
1034 authorizationStatus po_requisition_headers.authorization_status%TYPE;
1035 preparerId po_requisition_headers.preparer_id%TYPE;
1036 ameTransactionType po_document_types.ame_transaction_type%TYPE;
1037
1038 l_itemtype po_requisition_headers.wf_item_type%TYPE;
1039 l_itemkey po_requisition_headers.wf_item_key%TYPE;
1040
1041 approverList ame_util.approversTable2;
1042 l_process_out VARCHAR2(10);
1043 l_progress VARCHAR2(10);
1044 l_appr NUMBER;
1045
1046 BEGIN
1047
1048 if g_fnd_debug = 'Y' then
1049 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1050 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering get_ame_approval_list...');
1051 END IF;
1052 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1053 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pReqHeaderId :' || pReqHeaderId );
1054 END IF;
1055 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1056 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pDefaultFlag :' || pDefaultFlag );
1057 END IF;
1058 end if;
1059
1060 -- pDefaultFlag:
1061 -- -1: Approver Checkout
1062 -- 1: Possible Reset
1063 -- 2: Force Reset
1064 -- null: Load Current List
1065
1066 if (pDefaultFlag = -1) then
1067 SELECT wf_item_type, wf_item_key
1068 INTO l_itemtype, l_itemkey
1069 FROM po_requisition_headers_all
1070 WHERE requisition_header_id = pReqHeaderId;
1071 ameTransactionType := PO_WF_UTIL_PKG.GetItemAttrText( itemtype => l_itemtype,
1072 itemkey => l_itemkey,
1073 aname => 'AME_TRANSACTION_TYPE');
1074 else
1075 getAmeTransactionType(pReqHeaderId => pReqHeaderId,
1076 pAmeTransactionType => ameTransactionType);
1077 end if;
1078
1079 pQuoteChar :=quoteChar;
1080 pFieldDelimiter :=fieldDelimiter;
1081
1082 if (pDefaultFlag=1 OR pDefaultFlag=2) then
1083 /* check to see it is not open saved cart */
1084 select authorization_status, preparer_id
1085 into authorizationStatus, preparerId
1086 from po_requisition_headers
1087 where requisition_header_id = pReqHeaderId;
1088
1089 -- Get the approval list from ame.
1090 -- Based on our function design, we clear the approval list before calling the getAllApprovers7 call.
1091 -- If we call clearAllApprovals all the inserted/deleted approvers by the users will be ignored.
1092 -- AME will return the rule generated approvers if we call getAllApprovers after the clearAllApprovals call.
1093
1094 if g_fnd_debug = 'Y' then
1095 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1096 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'authorizationStatus :' || authorizationStatus);
1097 END IF;
1098 end if;
1099
1100 -- if INCOMPLETE and pDefaultFlag is 1 (non-force), then check to see if we should reset or not
1101 if (authorizationStatus = 'INCOMPLETE' AND pDefaultFlag=1) then
1102
1103 if g_fnd_debug = 'Y' then
1104 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1105 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api2.getAllApprovers7()a...');
1106 END IF;
1107 end if;
1108
1109 BEGIN
1110 l_progress := '000';
1111
1112 /*Bug 6314864 and 10107113: commented in case of final approver times out*/
1113
1114 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
1115 transactionIdIn => pReqHeaderId,
1116 transactionTypeIn => ameTransactionType,
1117 approvalProcessCompleteYNOut => l_process_out,
1118 approversOut => approverList
1119 );
1120 l_progress := '001';
1121
1122 if g_fnd_debug = 'Y' then
1123 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1124 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Done with ame_api6.getApprovers...progress='|| l_progress );
1125 END IF;
1126 end if;
1127
1128 for i in 1 .. approverList.count loop
1129 if(approverList(i).approval_status is not null
1130 and approverList(i).orig_system_id <> preparerId) then
1131 /* this is not a saved cart, need to clear the list */
1132 clearListForSavedCart := 'Y';
1133 l_progress := '005';
1134 exit;
1135 end if;
1136 end loop;
1137
1138 EXCEPTION when others then
1139 SELECT Count(*) INTO l_appr
1140 FROM po_action_history WHERE
1141 object_id =pReqHeaderId
1142 AND OBJECT_TYPE_CODE = 'REQUISITION'
1143 AND ACTION_CODE <> 'SUBMIT';
1144
1145 if g_fnd_debug = 'Y' then
1146 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1147 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Exception in ame_api2.getAllApprovers7,l_appr= ' || l_appr||' progress='|| l_progress );
1148 END IF;
1149 end if;
1150 IF l_appr <> 0 THEN
1151 clearListForSavedCart := 'Y';
1152 END IF;
1153
1154 END;
1155
1156 if g_fnd_debug = 'Y' then
1157 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1158 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'clearListForSavedCart :' || clearListForSavedCart);
1159 END IF;
1160 end if;
1161
1162 if (clearListForSavedCart = 'Y') then
1163
1164 -- clear columns since we are rebuilding the approval list
1165 UPDATE po_requisition_headers_all
1166 SET first_position_id = NULL, first_approver_id = NULL
1167 WHERE requisition_header_id = pReqHeaderId;
1168
1169 if g_fnd_debug = 'Y' then
1170 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1171 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name,
1172 'Cleared first_position_id and first_approver_id from req_header table.');
1173 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api2.clearAllApprovals()a...');
1174 END IF;
1175 end if;
1176
1177 ame_api2.clearAllApprovals( applicationIdIn => applicationId,
1178 transactionIdIn => pReqHeaderId,
1179 transactionTypeIn => ameTransactionType
1180 );
1181
1182 if g_fnd_debug = 'Y' then
1183 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1184 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api2.clearAllApprovals()a...');
1185 END IF;
1186 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1187 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api2.getAllApprovers7()b...');
1188 END IF;
1189 end if;
1190
1191 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
1192 transactionIdIn => pReqHeaderId,
1193 transactionTypeIn => ameTransactionType,
1194 approvalProcessCompleteYNOut => l_process_out,
1195 approversOut => approverList
1196 );
1197 if g_fnd_debug = 'Y' then
1198 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1199 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api2.getAllApprovers7()b...');
1200 END IF;
1201 end if;
1202
1203 end if; -- if(clearListForSavedCart = 'Y')
1204
1205 else -- if (pDefaultFlag=2) or (pDefaultFlag=1 and not INCOMPLETE), this will force the reset
1206
1207 -- clear columns since we are rebuilding the approval list
1208 UPDATE po_requisition_headers_all
1209 SET first_position_id = NULL, first_approver_id = NULL
1210 WHERE requisition_header_id = pReqHeaderId;
1211
1212 if g_fnd_debug = 'Y' then
1213 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1214 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name,
1215 'Cleared first_position_id and first_approver_id from req_header table.');
1216 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api2.clearAllApprovals()b...');
1217 END IF;
1218 end if;
1219
1220 ame_api2.clearAllApprovals( applicationIdIn => applicationId,
1221 transactionIdIn => pReqHeaderId,
1222 transactionTypeIn => ameTransactionType
1223 );
1224
1225 if g_fnd_debug = 'Y' then
1226 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1227 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api2.clearAllApprovals()b...');
1228 END IF;
1229 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1230 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api2.getAllApprovers7()c...');
1231 END IF;
1232 end if;
1233
1234 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
1235 transactionIdIn => pReqHeaderId,
1236 transactionTypeIn => ameTransactionType,
1237 approvalProcessCompleteYNOut => l_process_out,
1238 approversOut => approverList
1239 );
1240 if g_fnd_debug = 'Y' then
1241 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1242 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api2.getAllApprovers7()c...');
1243 END IF;
1244 end if;
1245
1246 end if; -- if ( authorizationStatus = 'INCOMPLETE')
1247
1248 else -- if pDefaultFlag is null or -1
1249
1250 if g_fnd_debug = 'Y' then
1251 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1252 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api2.getAllApprovers7()d...');
1253 END IF;
1254 end if;
1255
1256 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
1257 transactionIdIn => pReqHeaderId,
1258 transactionTypeIn => ameTransactionType,
1259 approvalProcessCompleteYNOut => l_process_out,
1260 approversOut => approverList
1261 );
1262
1263 if g_fnd_debug = 'Y' then
1264 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1265 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api2.getAllApprovers7()d...');
1266 END IF;
1267 end if;
1268
1269 end if; -- if(pDefaultFlag = 1)
1270
1271 if(approverList.count > 0) then
1272 pApprovalListStr := serializeApproversTable( approverList, pReqHeaderId, pApprovalListCount, pApprovalAction );
1273 else
1274 pApprovalListCount:=0;
1275 end if;
1276 if g_fnd_debug = 'Y' then
1277 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1278 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving get_ame_approval_list...');
1279 END IF;
1280 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1281 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListStr :' || pApprovalListStr);
1282 END IF;
1283 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1284 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListCount :' || pApprovalListCount);
1285 END IF;
1286 end if;
1287
1288 exception
1289 when NO_DATA_FOUND then
1290 pApprovalListCount := 0;
1291 pApprovalListStr := 'NO_DATA_FOUND';
1292 if g_fnd_debug = 'Y' then
1293 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1294 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1295 l_api_name || '.NO_DATA_FOUND', 'NO_DATA_FOUND');
1296 END IF;
1297 end if;
1298 when others then
1299 pApprovalListCount := 0;
1300 pApprovalListStr := 'EXCEPTION:' || sqlerrm;
1301 if g_fnd_debug = 'Y' then
1302 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1303 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1304 l_api_name || '.others_exception', sqlerrm);
1305 END IF;
1306 end if;
1307 END get_ame_approval_list;
1308
1309 --------------------------------------------------------------------------------
1310 --Start of Comments
1311 --Name: is_ame_reqapprv_workflow
1312 --Function:
1313 -- Returns 'Y' if the requisition workflow uses AME for approval routing
1314 -- Returns 'N' if the requisition workflow does not AME for approval routing
1315
1316 --Parameters:
1317 --IN:
1318 -- pReqHeaderId Requisition Header ID
1319 --OUT:
1320 -- None
1321 --End of Comments
1322 --------------------------------------------------------------------------------
1323 function is_ame_reqapprv_workflow (pReqHeaderId IN NUMBER,
1324 pIsRcoApproval IN BOOLEAN,
1325 xAmeTransactionType OUT NOCOPY VARCHAR2)
1326 return varchar2 IS
1327 isAmeApproval varchar2 (1);
1328 l_itemtype po_requisition_headers.wf_item_type%TYPE;
1329 l_itemkey po_requisition_headers.wf_item_key%TYPE;
1330
1331 BEGIN
1332
1333 if (pIsRcoApproval) then
1334 SELECT DISTINCT wf_item_type, wf_item_key
1335 INTO l_itemtype, l_itemkey
1336 FROM po_change_requests
1337 WHERE document_header_id= pReqHeaderId AND
1338 document_type = 'REQ' AND
1339 action_type IN ('MODIFICATION', 'CANCELLATION') AND
1340 creation_date = (select max(creation_date)
1341 from PO_CHANGE_REQUESTS
1342 where DOCUMENT_HEADER_ID = pReqHeaderId) AND
1343 request_status NOT IN ('ACCEPTED', 'REJECTED');
1344 else
1345 SELECT wf_item_type, wf_item_key
1346 INTO l_itemtype, l_itemkey
1347 FROM po_requisition_headers_all
1348 WHERE requisition_header_id = pReqHeaderId;
1349 end if;
1350
1351 isAmeApproval := PO_WF_UTIL_PKG.GetItemAttrText( itemtype => l_itemtype,
1352 itemkey => l_itemkey,
1353 aname => 'IS_AME_APPROVAL');
1354
1355 if (isAmeApproval = 'Y') then
1356 xAmeTransactionType := PO_WF_UTIL_PKG.GetItemAttrText( itemtype => l_itemtype,
1357 itemkey => l_itemkey,
1358 aname => 'AME_TRANSACTION_TYPE');
1359 return 'Y';
1360 else
1361 return 'N';
1362 end if;
1363 END;
1364
1365 --------------------------------------------------------------------------------
1366 --Start of Comments
1367 --Name: get_old_approval_list
1368 --Function:
1369 -- Call AME API to get the existing approval list for a requisition.
1370 -- Only the future approval list is returned.
1371
1372 --Parameters:
1373 --IN:
1374 -- pReqHeaderId Requisition Header ID
1375 --OUT:
1376 -- pApprovalListStr Furture Approval List concatenated in a string
1377 -- pApprovalListCount Number of Approvers. It has a value of 0,
1378 -- if AME did not build approval list for this transaction.
1379 -- pQuoteChar Quote Character, used for escaping purpose in tokenization
1380 -- pFieldDelimiter Field Delimiter, used for delimiting list string into elements.
1381 --End of Comments
1382 --------------------------------------------------------------------------------
1383 procedure get_old_approval_list(pReqHeaderId IN NUMBER,
1384 pApprovalListStr OUT NOCOPY VARCHAR2,
1385 pApprovalListCount OUT NOCOPY NUMBER,
1386 pQuoteChar OUT NOCOPY VARCHAR2,
1387 pFieldDelimiter OUT NOCOPY VARCHAR2) IS
1388
1389 l_api_name varchar2(50):= 'get_old_approval_list';
1390 hasApprovalAction varchar2(1);
1391 approverCount number;
1392 approverList ame_util.approversTable2;
1393 ameTransactionType po_document_types.ame_transaction_type%TYPE;
1394 authorizationStatus po_requisition_headers_all.authorization_status%TYPE;
1395 isRcoApproval boolean := false;
1396 changeRequestExist number := 0;
1397 l_process_out VARCHAR2(10);
1398
1399 BEGIN
1400
1401 if g_fnd_debug = 'Y' then
1402 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1403 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering get_old_approval_list...');
1404 END IF;
1405 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1406 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pReqHeaderId :' || pReqHeaderId );
1407 END IF;
1408 end if;
1409
1410 begin
1411 select authorization_status
1412 into authorizationStatus
1413 from po_requisition_headers_all
1414 where requisition_header_id = pReqHeaderId;
1415 exception
1416 when others then
1417 if g_fnd_debug = 'Y' then
1418 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1419 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1420 l_api_name || '.others_exception', sqlerrm);
1421 END IF;
1422 end if;
1423 return;
1424 end;
1425
1426 if g_fnd_debug = 'Y' then
1427 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1428 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'authorizationStatus :' || authorizationStatus);
1429 END IF;
1430 end if;
1431
1432 if (authorizationStatus <> 'IN PROCESS' and authorizationStatus <> 'PRE-APPROVED') then
1433 -- if the requisition is approved, check if it is rco transaction
1434 if (authorizationStatus = 'APPROVED') then
1435 begin
1436 SELECT COUNT(1)
1437 INTO changeRequestExist
1438 FROM
1439 PO_CHANGE_REQUESTS pcr
1440 WHERE
1441 pcr.document_header_id = pReqHeaderId AND
1442 pcr.document_type = 'REQ' AND
1443 pcr.action_type IN ('MODIFICATION', 'CANCELLATION') AND
1444 pcr.approval_required_flag = 'Y' AND
1445 pcr.request_status NOT IN ('ACCEPTED', 'REJECTED');
1446 exception
1447 when others then
1448 null; -- assume not rco or the rco is approved/rejected.
1449 end;
1450 if (changeRequestExist>0) then
1451 isRcoApproval :=true;
1452 else
1453 return;
1454 end if;
1455 else
1456 return;
1457 end if;
1458 end if;
1459
1460 if (is_ame_reqapprv_workflow (pReqHeaderId, isRcoApproval, ameTransactionType) <> 'Y') then
1461
1462 pApprovalListCount := 0;
1463 if g_fnd_debug = 'Y' then
1464 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1465 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving get_old_approval_list due to the call is_ame_reqapprv_workflow()...');
1466 END IF;
1467 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1468 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListCount :' || pApprovalListCount);
1469 END IF;
1470 end if;
1471 return;
1472 end if;
1473
1474 pQuoteChar :=quoteChar;
1475 pFieldDelimiter :=fieldDelimiter;
1476
1477 if g_fnd_debug = 'Y' then
1478 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1479 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api3.getOldApprovers()...');
1480 END IF;
1481 end if;
1482
1483 /*
1484 ame_api3.getOldApprovers(applicationIdIn=>applicationId,
1485 transactionIdIn=>pReqHeaderId,
1486 transactionTypeIn=>ameTransactionType,
1487 oldApproversOut=>approverList);
1488 */
1489
1490 /* ame_api2.getAllApprovers7( applicationIdIn => applicationId,
1491 transactionIdIn => pReqHeaderId,
1492 transactionTypeIn => ameTransactionType,
1493 approvalProcessCompleteYNOut => l_process_out,
1494 approversOut => approverList
1495 );*/
1496
1497 ame_api6.getApprovers2( applicationIdIn => applicationId,
1498 transactionTypeIn => ameTransactionType,
1499 transactionIdIn => pReqHeaderId,
1500 approversOut => approverList
1501 );
1502
1503 if g_fnd_debug = 'Y' then
1504 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1505 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api3.getOldApprovers()...');
1506 END IF;
1507 end if;
1508
1509 pApprovalListCount := approverList.count;
1510
1511 if(approverList.count>0) then
1512 pApprovalListStr := serializeApproversTable(approverList, pReqHeaderId, approverCount, hasApprovalAction);
1513 pApprovalListCount := approverCount;
1514 end if;
1515
1516 if g_fnd_debug = 'Y' then
1517 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1518 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving get_old_approval_list...');
1519 END IF;
1520 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1521 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListStr :' || pApprovalListStr);
1522 END IF;
1523 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1524 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListCount :' || pApprovalListCount);
1525 END IF;
1526 end if;
1527
1528 exception
1529 when NO_DATA_FOUND then
1530 pApprovalListCount := 0;
1531 pApprovalListStr := 'NO_DATA_FOUND';
1532 if g_fnd_debug = 'Y' then
1533 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1534 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1535 l_api_name || '.NO_DATA_FOUND', 'NO_DATA_FOUND');
1536 END IF;
1537 end if;
1538 when others then
1539 pApprovalListCount := 0;
1540 pApprovalListStr := 'EXCEPTION';
1541 if g_fnd_debug = 'Y' then
1542 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1543 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1544 l_api_name || '.others_exception', sqlerrm);
1545 END IF;
1546 end if;
1547 END;
1548
1549 --------------------------------------------------------------------------------
1550 --Start of Comments
1551 --Name: is_req_pre_approved
1552 --Function:
1553 -- Approval workflow PL/SQL handler
1554 -- Check AME approval list to determine if the document requires approval
1555 -- from a chain of authority approver
1556 -- Return 'Y' if yes
1557 -- Return 'N' if no
1558
1559 --Parameters:
1560 -- Standard workflow in/out parameters
1561
1562 --End of Comments
1563 --------------------------------------------------------------------------------
1564 procedure is_req_pre_approved(itemtype in varchar2,
1565 itemkey in varchar2,
1566 actid in number,
1567 funcmode in varchar2,
1568 resultout out NOCOPY varchar2) is
1569
1570 l_api_name varchar2(50):= 'is_req_pre_approved';
1571 x_progress varchar2(100);
1572 x_resultout varchar2(30);
1573 l_document_id number;
1574 l_return_val varchar2(1);
1575
1576 l_doc_string varchar2(200);
1577 l_preparer_user_name varchar2(100);
1578
1579 approverCount integer;
1580 approvers ame_util.approversTable2;
1581 ameTransactionType po_document_types.ame_transaction_type%TYPE;
1582 l_process_out VARCHAR2(10);
1583
1584 BEGIN
1585
1586 if g_fnd_debug = 'Y' then
1587 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1588 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering is_req_pre_approved...');
1589 END IF;
1590 end if;
1591
1592 x_progress := 'POR_AME_APPROVAL_LIST.is_req_pre_approved: 01';
1593 IF (g_po_wf_debug = 'Y') THEN
1594 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1595 END IF;
1596
1597 l_return_val := 'Y';
1598
1599
1600 -- Do nothing in cancel or timeout mode
1601 --
1602 if (funcmode <> wf_engine.eng_run) then
1603
1604 resultout := wf_engine.eng_null;
1605 return;
1606
1607 end if;
1608
1609 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1610 itemkey => itemkey,
1611 aname => 'DOCUMENT_ID');
1612
1613 getAmeTransactionType(pReqHeaderId => to_number(l_document_id),
1614 pAmeTransactionType => ameTransactionType);
1615
1616 x_progress := 'POR_AME_APPROVAL_LIST.is_req_pre_approved: 02' || ameTransactionType;
1617 IF (g_po_wf_debug = 'Y') THEN
1618 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1619 END IF;
1620
1621 -- Get the list of approvers from ame.
1622 if g_fnd_debug = 'Y' then
1623 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1624 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api2.getAllApprovers7()...');
1625 END IF;
1626 end if;
1627
1628 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
1629 transactionIdIn => l_document_id,
1630 transactionTypeIn => ameTransactionType,
1631 approvalProcessCompleteYNOut => l_process_out,
1632 approversOut => approvers
1633 );
1634 if g_fnd_debug = 'Y' then
1635 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1636 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api2.getAllApprovers7()...');
1637 END IF;
1638 end if;
1639 approverCount := approvers.count;
1640
1641 -- Once we get the list of approvers from AME, check for the approval_status in all the record.
1642 for i in 1 .. approverCount loop
1643 if(approvers(i).authority = ame_util.authorityApprover and
1644 (approvers(i).api_insertion = ame_util.oamGenerated or
1645 approvers(i).api_insertion = ame_util.apiAuthorityInsertion) and
1646 (approvers(i).approval_status is null or
1647 approvers(i).approval_status = ame_util.nullStatus)) then
1648 l_return_val := 'N';
1649 exit;
1650 end if;
1651 end loop;
1652
1653 resultout := wf_engine.eng_completed || ':' || l_return_val;
1654 x_resultout := l_return_val;
1655
1656 x_progress := 'POR_AME_APPROVAL_LIST.is_req_pre_approved: 02. RESULT= ' || x_resultout;
1657 IF (g_po_wf_debug = 'Y') THEN
1658 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,x_progress);
1659 END IF;
1660
1661 if g_fnd_debug = 'Y' then
1662 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1663 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving is_req_pre_approved...');
1664 END IF;
1665 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1666 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- x_resultout :' || x_resultout);
1667 END IF;
1668 end if;
1669
1670 EXCEPTION
1671
1672 WHEN OTHERS THEN
1673 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1674 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1675 WF_CORE.context('POR_AME_APPROVAL_LIST' , 'is_req_pre_approved', itemtype, itemkey, x_progress);
1676 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, 'POR_AME_APPROVAL_LIST.is_req_pre_approved');
1677 RAISE;
1678
1679 END is_req_pre_approved;
1680
1681
1682 --------------------------------------------------------------------------------
1683 --Start of Comments
1684 --Name: get_first_authority_approver
1685 --Function:
1686 -- Call AME API to fetch the latest approval list
1687 -- Then walk through the list to find a first chain of authority approver
1688 -- for a requisition.
1689 -- The procedure raises any exception thrown by AME engine.
1690
1691 --Parameters:
1692 --IN:
1693 -- pReqHeaderId Requisition Header ID
1694 --OUT:
1695 -- xPersonId The person ID of the chain of authority approver
1696 -- This variable will have a null value,
1697 -- if such an approver is not found.
1698
1699 --End of Comments
1700 --------------------------------------------------------------------------------
1701 procedure get_first_authority_approver(pReqHeaderId IN NUMBER,
1702 xPersonId OUT NOCOPY VARCHAR2) IS
1703
1704 l_api_name varchar2(50):= 'get_first_authority_approver';
1705 approverCount number;
1706 approvers ame_util.approversTable2;
1707 ameTransactionType po_document_types.ame_transaction_type%TYPE;
1708 l_process_out VARCHAR2(10);
1709
1710 BEGIN
1711
1712 if g_fnd_debug = 'Y' then
1713 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1714 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering get_first_authority_approver...');
1715 END IF;
1716 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1717 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param -- pReqHeaderId ' || pReqHeaderId );
1718 END IF;
1719 end if;
1720
1721 xPersonId := null;
1722 getAmeTransactionType(pReqHeaderId => pReqHeaderId,
1723 pAmeTransactionType => ameTransactionType);
1724
1725 -- Get the approvers list from ame.
1726 if g_fnd_debug = 'Y' then
1727 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1728 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api2.getAllApprovers7()...');
1729 END IF;
1730 end if;
1731
1732 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
1733 transactionIdIn => pReqHeaderId,
1734 transactionTypeIn => ameTransactionType,
1735 approvalProcessCompleteYNOut => l_process_out,
1736 approversOut => approvers
1737 );
1738
1739 if g_fnd_debug = 'Y' then
1740 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1741 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api2.getAllApprovers7()...');
1742 END IF;
1743 end if;
1744
1745 approverCount := approvers.count;
1746
1747 -- Once we get the list of approvers from ame, check for the first authority approver record in the list.
1748 -- Check for the action type(POS/PER) in the first authority approver record.
1749 -- If the first record is of position hierarchy type, then find out the person_id from the position id and return that.
1750 -- Otherwise simply return the person_id from the orig_system_id value.
1751
1752 for i in 1 .. approverCount loop
1753 if(approvers(i).authority = ame_util.authorityApprover and
1754 approvers(i).api_insertion = ame_util.oamGenerated and
1755 (approvers(i).approval_status is null or
1756 approvers(i).approval_status = ame_util.nullStatus)) then
1757
1758 if approvers(i).orig_system = ame_util.posOrigSystem then
1759
1760 if g_fnd_debug = 'Y' then
1761 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1762 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'First record is Position Hierarchy action type...');
1763 END IF;
1764 end if;
1765
1766 begin
1767 SELECT person_id into xPersonId FROM (
1768 SELECT person.person_id FROM per_all_people_f person, per_all_assignments_f asg
1769 WHERE asg.position_id = approvers(i).orig_system_id and trunc(sysdate) between person.effective_start_date
1770 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
1771 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
1772 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
1773 and asg.assignment_status_type_id not in (
1774 SELECT assignment_status_type_id FROM per_assignment_status_types
1775 WHERE per_system_status = 'TERM_ASSIGN'
1776 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
1777 ) where rownum = 1;
1778 exception
1779 WHEN NO_DATA_FOUND THEN
1780 RAISE;
1781 end;
1782 else
1783 xPersonId := approvers(i).orig_system_id;
1784 end if;
1785 exit;
1786 end if;
1787 end loop;
1788
1789 if g_fnd_debug = 'Y' then
1790 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1791 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving get_first_authority_approver...');
1792 END IF;
1793 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1794 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- xPersonId :' || xPersonId);
1795 END IF;
1796 end if;
1797
1798 exception
1799 when others then
1800 if g_fnd_debug = 'Y' then
1801 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1802 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1803 l_api_name || '.others_exception', sqlerrm);
1804 END IF;
1805 end if;
1806 raise;
1807
1808 END;
1809
1810 --------------------------------------------------------------------------------
1811 --Start of Comments
1812 --Name: can_delete_oam_approvers
1813 --Function:
1814 -- Call AME API to fetch the value of the
1815 -- ALLOW_DELETING_RULE_GENERATED_APPROVERS OAM attribute.
1816 -- This attribute specifies whether approvers generated by
1817 -- approval rules can be deleted from the approver list.
1818
1819 --Parameters:
1820 --IN:
1821 -- pReqHeaderId Requisition Header ID
1822 --OUT:
1823 -- xResult 'Y' or 'N'
1824 --End of Comments
1825 --------------------------------------------------------------------------------
1826 PROCEDURE can_delete_oam_approvers( pReqHeaderId IN NUMBER,
1827 xResult OUT NOCOPY VARCHAR2) IS
1828 l_api_name varchar2(50):= 'can_delete_oam_approvers';
1829 attributeValue1 VARCHAR2(10);
1830 attributeValue2 VARCHAR2(10);
1831 attributeValue3 VARCHAR2(10);
1832 ameTransactionType po_document_types.ame_transaction_type%TYPE;
1833
1834 BEGIN
1835
1836 if g_fnd_debug = 'Y' then
1837 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1838 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering can_delete_oam_approvers...');
1839 END IF;
1840 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1841 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param -- pReqHeaderId ' || pReqHeaderId );
1842 END IF;
1843 end if;
1844
1845 getAmeTransactionType(pReqHeaderId => pReqHeaderId,
1846 pAmeTransactionType => ameTransactionType);
1847
1848 if g_fnd_debug = 'Y' then
1849 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1850 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api3.getAttributeValue()...');
1851 END IF;
1852 end if;
1853 ame_api3.getAttributeValue(applicationIdIn => applicationId,
1854 transactionTypeIn => ameTransactionType,
1855 transactionIdIn => pReqHeaderId,
1856 attributeNameIn => ame_util.allowDeletingOamApprovers,
1857 itemIdIn => NULL,
1858 attributeValue1Out => attributeValue1,
1859 attributeValue2Out => attributeValue2,
1860 attributeValue3Out => attributeValue3);
1861
1862 if g_fnd_debug = 'Y' then
1863 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1864 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api3.getAttributeValue()...');
1865 END IF;
1866 end if;
1867
1868 IF attributeValue1 = 'true' THEN
1869 xResult := 'Y';
1870 ELSE
1871 xResult := 'N';
1872 END IF;
1873
1874 if g_fnd_debug = 'Y' then
1875 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1876 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving can_delete_oam_approvers...');
1877 END IF;
1878 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1879 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- xResult :' || xResult);
1880 END IF;
1881 end if;
1882
1883 EXCEPTION
1884 WHEN OTHERS THEN
1885 if g_fnd_debug = 'Y' then
1886 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1887 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1888 l_api_name || '.others_exception', sqlerrm);
1889 END IF;
1890 end if;
1891 --raise;
1892 xResult := 'N';
1893 END can_delete_oam_approvers;
1894
1895
1896 /* private procedures */
1897 procedure getAmeTransactionType(pReqHeaderId IN NUMBER,
1898 pAmeTransactionType OUT NOCOPY VARCHAR2
1899 ) IS
1900
1901 l_api_name varchar2(50):= 'getAmeTransactionType';
1902 changeRequestExist number := 0;
1903 docType po_document_types.document_type_code%TYPE;
1904 docSubType po_document_types.document_subtype%TYPE;
1905 lookupCode po_requisition_headers.type_lookup_code%TYPE;
1906 orgId NUMBER;
1907
1908 begin
1909
1910 if g_fnd_debug = 'Y' then
1911 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1912 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'pReqHeaderId: ' || pReqHeaderId);
1913 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'current org id: ' || PO_MOAC_UTILS_PVT.get_current_org_id);
1914 END IF;
1915 end if;
1916
1917 -- check if is rco, alternative is to check authorization status
1918 begin
1919 SELECT COUNT(1)
1920 INTO changeRequestExist
1921 FROM
1922 PO_CHANGE_REQUESTS pcr
1923 WHERE
1924 pcr.document_header_id = pReqHeaderId AND
1925 pcr.document_type = 'REQ' AND
1926 pcr.action_type IN ('MODIFICATION', 'CANCELLATION') AND
1927 pcr.request_status NOT IN ('ACCEPTED', 'REJECTED');
1928 exception
1929 when others then
1930 if g_fnd_debug = 'Y' then
1931 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1932 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Not RCO');
1933 END IF;
1934 end if;
1935 end;
1936
1937 if (changeRequestExist > 0) then
1938 docType := 'CHANGE_REQUEST';
1939 docSubType :='REQUISITION';
1940
1941 SELECT org_id
1942 INTO orgId
1943 FROM po_requisition_headers_all
1944 WHERE requisition_header_id = pReqHeaderId;
1945
1946 else
1947 docType := 'REQUISITION';
1948 begin
1949 SELECT type_lookup_code, org_id
1950 INTO lookupCode, orgId
1951 FROM po_requisition_headers_all
1952 WHERE requisition_header_id = pReqHeaderId;
1953
1954 exception
1955 when others then
1956 if g_fnd_debug = 'Y' then
1957 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1958 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Req not found');
1959 END IF;
1960 end if;
1961 lookupCode := 'PURCHASE'; -- assume not internal
1962 end;
1963 docSubType := lookupCode;
1964 end if;
1965
1966 -- Use the private function to fetch the ame txn type given the doc type and subtype
1967 pAmeTransactionType := getAmeTxnType(docType, docSubType, orgId);
1968
1969 if g_fnd_debug = 'Y' then
1970 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1971 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'pAmeTransactionType: ' || pAmeTransactionType);
1972 END IF;
1973 end if;
1974
1975 end;
1976
1977 function getAmeTxnType (p_doc_type IN VARCHAR2,
1978 p_doc_subtype IN VARCHAR2,
1979 p_org_id IN NUMBER)
1980 return VARCHAR2 is
1981 x_ame_txn_type po_document_types_all_b.ame_transaction_type%TYPE;
1982 BEGIN
1983
1984 SELECT ame_transaction_type
1985 INTO x_ame_txn_type
1986 FROM po_document_types_all_b
1987 WHERE document_type_code = p_doc_type
1988 and document_subtype = p_doc_subtype
1989 and org_id = p_org_id;
1990
1991 return x_ame_txn_type;
1992
1993 EXCEPTION
1994 when others then
1995 return 'PURCHASE_REQ';
1996 END getAmeTxnType;
1997
1998 PROCEDURE marshalField(p_string IN VARCHAR2,
1999 p_quote_char IN VARCHAR2,
2000 p_delimiter IN VARCHAR2) IS
2001 l_string VARCHAR2(32767) := NULL;
2002 BEGIN
2003 l_string := p_string;
2004 l_string := REPLACE(l_string, p_quote_char, p_quote_char || p_quote_char);
2005 l_string := REPLACE(l_string, p_delimiter, p_quote_char || p_delimiter);
2006 approvalListStr := approvalListStr ||l_string || p_delimiter;
2007 END marshalField;
2008
2009
2010 --------------------------------------------------------------------------------
2011 --Start of Comments
2012 --Name: serializeApproversTable
2013 --Function:
2014 -- This function will simply iterate thorugh the list
2015 -- of approvers and frame an approver string from each approver record
2016 -- and give the output.
2017
2018 -- If the approver record is of position hierarchy action type, then
2019 -- the list of users associated to the position will be retrieved
2020 -- and will be sort by last_name. Then the first user will be selected from the sorted list
2021 -- and that user's person_id will be considered.
2022
2023 -- approverRecord.orig_system can have 3 values like following.
2024 -- PER = Employee Supervisor action type --> approverRecord.orig_system_id will be person_id
2025 -- POS = Position Hierarchy --> approverRecord.orig_system_id will be position_id
2026 -- FND = FND Users --> approverRecord.orig_system_id will be user_id
2027
2028 --Parameters:
2029 --IN:
2030 -- approversTableIn Approvers List
2031 -- reqHeaderId ID of the requisition
2032 --OUT:
2033 -- approverCount Total Number of approvers in the list.
2034 -- hasApprovalAction 'Y' or 'N'
2035 --End of Comments
2036 --------------------------------------------------------------------------------
2037 function serializeApproversTable( approversTableIn in ame_util.approversTable2,
2038 reqHeaderId in NUMBER,
2039 approverCount out nocopy number,
2040 hasApprovalAction out nocopy varchar2
2041 )
2042 return varchar2 as
2043
2044 l_api_name varchar2(50):= 'serializeApproversTable';
2045 upperLimit integer;
2046
2047 l_full_name per_all_people_f.full_name%TYPE;
2048 l_person_id per_all_people_f.person_id%TYPE;
2049 l_job_or_position VARCHAR2(2000);
2050 l_orig_system VARCHAR2(30);
2051 l_orig_system_id NUMBER;
2052
2053 l_position_id NUMBER;
2054 l_job_id NUMBER;
2055 l_valid_approver VARCHAR2(1);
2056 l_preparer_id NUMBER; --bug 14591082
2057 l_preparer_pos_id NUMBER; --bug 14591082
2058
2059 begin
2060
2061 if g_fnd_debug = 'Y' then
2062 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2063 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering serializeApproversTable...');
2064 END IF;
2065 end if;
2066
2067 -- bug 9559404: Commented the following code as l_preparer_id is no longer needed
2068 -- bug 14591082:uncommented this piece of code to get preparer id
2069 select PREPARER_ID
2070 into l_preparer_id
2071 from po_requisition_headers_all
2072 where reqHeaderId = requisition_header_id;
2073
2074 if g_fnd_debug = 'Y' then
2075 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2076 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_preparer_id: ' || l_preparer_id);
2077 END IF;
2078 end if;
2079
2080 approvalListStr := NULL;
2081 upperLimit := approversTableIn.count;
2082
2083 approverCount := 0;
2084 hasApprovalAction := 'N';
2085
2086 if g_fnd_debug = 'Y' then
2087 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2088 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' upperLimit :' || upperLimit );
2089 END IF;
2090 end if;
2091
2092 -- Iterate through the list of approvers.
2093 for i in 1 .. upperLimit loop
2094
2095 if g_fnd_debug = 'Y' then
2096 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2097 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Processing the approver :' || i );
2098 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Orig_System :' || approversTableIn(i).orig_system);
2099 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Orig System Id :' || approversTableIn(i).orig_system_id);
2100 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Approval Status :' || approversTableIn(i).approval_status);
2101 END IF;
2102 end if;
2103
2104 -- assume valid approver
2105 l_valid_approver := 'Y';
2106
2107 -- bug 9559404 : Commented the following code so that if preparer is an approver, he is also shown in the approvals list
2108 -- bug 14591082: Uncommented this piece of code to handle preparer
2109 -- if we have a Emp-Sup approver, make sure the approverId
2110 -- is NOT the same as preparer's Id
2111 if (approversTableIn(i).orig_system = ame_util.perOrigSystem) then
2112 -- bug 14591082: added condition 'approversTableIn(i).approval_status = ame_util.approvedStatus'
2113 if (approversTableIn(i).orig_system_id = l_preparer_id AND approversTableIn(i).approval_status = ame_util.approvedStatus ) THEN
2114 l_valid_approver := 'N';
2115 if g_fnd_debug = 'Y' then
2116 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2117 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' ApproverID matches l_preparer_id... skipping!');
2118 END IF;
2119 end if;
2120 end if;
2121
2122 -- else if we have a position approver, make sure the position
2123 -- is NOT the same as preparer's position
2124 elsif (approversTableIn(i).orig_system = ame_util.posOrigSystem) then
2125
2126 SELECT POSITION_ID
2127 INTO l_preparer_pos_id
2128 FROM PER_ALL_ASSIGNMENTS_F
2129 WHERE PERSON_ID = l_preparer_id
2130 and primary_flag = 'Y'
2131 and assignment_type in ('E','C')
2132 and assignment_status_type_id not in ( select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
2133 and TRUNC ( effective_start_date ) <= TRUNC(SYSDATE)
2134 and NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
2135 and rownum = 1;
2136 -- bug 14591082: added condition 'approversTableIn(i).approval_status = ame_util.approvedStatus'
2137 if (approversTableIn(i).orig_system_id = l_preparer_pos_id AND approversTableIn(i).approval_status = ame_util.approvedStatus ) then
2138 l_valid_approver := 'N';
2139 if g_fnd_debug = 'Y' then
2140 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2141 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Approvers Position matches preparers... skipping!');
2142 END IF;
2143 end if;
2144 end if;
2145
2146 end if; -- Checking PER and POS-based approver
2147
2148 -- bug 9559404 : end
2149
2150 -- If the approval_status is not null, then we can assume the following.
2151 -- 1. The approver is deleted.
2152 -- 2. The approver is notified or approved/rejected.
2153 -- So if the approval_status is not null,
2154 -- we need to additionally check if the status is not notified and not suppressed and not approved,
2155 -- then do not consider the record.
2156 -- (we want to consider notified, suppressed and approved records)
2157
2158 if( approversTableIn(i).approval_status is not null AND
2159 approversTableIn(i).approval_status<>ame_util.notifiedStatus AND
2160 approversTableIn(i).approval_status<>ame_util.suppressedStatus AND
2161 approversTableIn(i).approval_status<>ame_util.approvedStatus) then
2162
2163 hasApprovalAction := 'Y';
2164 if g_fnd_debug = 'Y' then
2165 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2166 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Approval_status is ' || approversTableIn(i).approval_status);
2167 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' ...so we do not consider this approver.');
2168 END IF;
2169 end if;
2170
2171 elsif (l_valid_approver = 'Y') then
2172
2173 l_orig_system := approversTableIn(i).orig_system;
2174 l_orig_system_id := approversTableIn(i).orig_system_id;
2175 l_job_or_position := NULL;
2176
2177 -- orig_system and orig_system_id should not be null.
2178 -- There is a bug 4403014 for the same. So this is a work-around to achieve the same.
2179 if l_orig_system is null and l_orig_system_id is null then
2180 SELECT orig_system, orig_system_id into l_orig_system, l_orig_system_id FROM wf_roles where name = approversTableIn(i).name and rownum = 1;
2181 end if;
2182
2183 if l_orig_system is null and l_orig_system_id is null then
2184 raise NO_DATA_FOUND;
2185 end if;
2186
2187 if g_fnd_debug = 'Y' then
2188 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2189 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_orig_system :' || l_orig_system );
2190 END IF;
2191 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2192 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_orig_system_id :' || l_orig_system_id );
2193 END IF;
2194 end if;
2195
2196 begin
2197 get_person_info( l_orig_system,
2198 l_orig_system_id,
2199 approversTableIn(i).display_name,
2200 reqHeaderId,
2201 g_fnd_debug,
2202 l_person_id,
2203 l_full_name);
2204
2205 exception
2206 WHEN NO_DATA_FOUND THEN
2207 -- No approvers found for this position. So we will skip this position.
2208 l_valid_approver := 'N';
2209
2210 -- We raise the exception only for the last approver.
2211 if i = upperLimit then
2212 raise NO_DATA_FOUND;
2213 end if;
2214 end;
2215
2216 -- Find position | job name
2217 if ( l_orig_system = ame_util.posOrigSystem ) then
2218 l_job_or_position := approversTableIn(i).display_name;
2219 else
2220 l_job_or_position := null;
2221 end if;
2222
2223 -- Verify the person_id is not null.
2224 if( l_valid_approver = 'Y' AND ( l_person_id is null or l_full_name is null ))then
2225
2226 SELECT orig_system_id, display_name, description into l_person_id, l_full_name, l_job_or_position
2227 FROM wf_roles where name = approversTableIn(i).name and rownum = 1;
2228
2229 -- We raise the exception only for the last approver.
2230 --if l_person_id is null then
2231 if i = upperLimit then
2232 raise NO_DATA_FOUND;
2233 end if;
2234
2235 end if;
2236
2237 if g_fnd_debug = 'Y' then
2238 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2239 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_full_name :' || l_full_name );
2240 END IF;
2241 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2242 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_person_id :' || l_person_id );
2243 END IF;
2244 end if;
2245
2246 -- Make sure position/job name is populated.
2247 if( l_job_or_position is null ) then
2248
2249 -- retrieve the position name. if the position name is null check for the job name.
2250
2251 SELECT position_id, job_id INTO l_position_id, l_job_id
2252 FROM per_all_assignments_f
2253 WHERE person_id = l_person_id
2254 and primary_flag = 'Y' and assignment_type in ('E','C')
2255 and assignment_status_type_id not in ( select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
2256 and TRUNC ( effective_start_date ) <= TRUNC(SYSDATE) AND NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
2257 and rownum = 1;
2258
2259 if l_position_id is not null then
2260 SELECT name INTO l_job_or_position FROM per_all_positions WHERE position_id = l_position_id;
2261 end if;
2262
2263 if l_job_or_position is null and l_job_id is not null then
2264 SELECT name INTO l_job_or_position FROM per_jobs WHERE job_id = l_job_id;
2265 end if;
2266
2267 if g_fnd_debug = 'Y' then
2268 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2269 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_job_or_position :' || l_job_or_position );
2270 END IF;
2271 end if;
2272
2273 end if;
2274
2275 -- If the approver is valid, then only frame the approver string.
2276 if( l_valid_approver = 'Y' ) then
2277 marshalField(l_full_name, quoteChar, fieldDelimiter);
2278 marshalField( to_char(l_person_id), quoteChar, fieldDelimiter);
2279 marshalField(l_job_or_position, quoteChar, fieldDelimiter);
2280 marshalField(approversTableIn(i).name, quoteChar, fieldDelimiter);
2281
2282 --marshalField(approversTableIn(i).orig_system, quoteChar, fieldDelimiter);
2283 --marshalField(to_char(approversTableIn(i).orig_system_id), quoteChar, fieldDelimiter);
2284
2285 marshalField(l_orig_system, quoteChar, fieldDelimiter);
2286 marshalField(to_char(l_orig_system_id), quoteChar, fieldDelimiter);
2287
2288 marshalField(approversTableIn(i).api_insertion, quoteChar, fieldDelimiter);
2289 marshalField(approversTableIn(i).authority, quoteChar, fieldDelimiter);
2290 marshalField(approversTableIn(i).approval_status, quoteChar, fieldDelimiter);
2291 marshalField(approversTableIn(i).approver_category, quoteChar, fieldDelimiter);
2292 marshalField(approversTableIn(i).approver_order_number, quoteChar, fieldDelimiter);
2293 marshalField(approversTableIn(i).action_type_id, quoteChar, fieldDelimiter);
2294 marshalField(approversTableIn(i).group_or_chain_id, quoteChar, fieldDelimiter);
2295 marshalField(approversTableIn(i).member_order_number, quoteChar, fieldDelimiter);
2296 --marshalField(to_char(i), quoteChar, fieldDelimiter);
2297 approverCount := approverCount +1;
2298 marshalField(to_char(approverCount), quoteChar, fieldDelimiter);
2299 end if;
2300
2301 end if; -- if approval_status is not null.
2302 end loop;
2303
2304 if g_fnd_debug = 'Y' then
2305 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2306 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving serializeApproversTable...');
2307 END IF;
2308 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2309 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- approvalListStr :' || approvalListStr );
2310 END IF;
2311 end if;
2312 return approvalListStr;
2313
2314 exception
2315 when others then
2316 if g_fnd_debug = 'Y' then
2317 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2318 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2319 l_api_name || '.others_exception', sqlerrm);
2320 END IF;
2321 end if;
2322 raise;
2323 return(null);
2324 end serializeApproversTable;
2325
2326 procedure getAllApprovers( pReqHeaderId IN NUMBER,
2327 pAmeTransactionType IN VARCHAR2,
2328 pApprovalListStr OUT NOCOPY VARCHAR2,
2329 pApprovalListCount OUT NOCOPY NUMBER) IS
2330
2331 l_api_name varchar2(50):= 'getAllApprovers';
2332 hasApprovalAction varchar2(1);
2333 approverList ame_util.approversTable2;
2334 l_process_out VARCHAR2(10);
2335
2336 begin
2337
2338
2339 if g_fnd_debug = 'Y' then
2340 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2341 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering getAllApprovers...');
2342 END IF;
2343 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2344 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param -- pReqHeaderId :' || pReqHeaderId);
2345 END IF;
2346 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2347 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame_api2.getAllApprovers7()...');
2348 END IF;
2349 end if;
2350
2351 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
2352 transactionIdIn => pReqHeaderId,
2353 transactionTypeIn => pAmeTransactionType,
2354 approvalProcessCompleteYNOut => l_process_out,
2355 approversOut => approverList
2356 );
2357
2358 if g_fnd_debug = 'Y' then
2359 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2360 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Done with ame_api2.getAllApprovers7()...');
2361 END IF;
2362 end if;
2363
2364 if(approverList.count > 0) then
2365 /* no approver required */
2366 pApprovalListStr := serializeApproversTable(approverList, pReqHeaderId, pApprovalListCount, hasApprovalAction);
2367 else
2368 pApprovalListCount:=0;
2369 end if;
2370
2371 if g_fnd_debug = 'Y' then
2372 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2373 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving getAllApprovers...');
2374 END IF;
2375 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2376 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- approvalListStr :' || approvalListStr );
2377 END IF;
2378 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2379 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListCount :' || pApprovalListCount );
2380 END IF;
2381 end if;
2382
2383 exception
2384 when others then
2385 if g_fnd_debug = 'Y' then
2386 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2387 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2388 l_api_name || '.others_exception', sqlerrm);
2389 END IF;
2390 end if;
2391 raise;
2392 end getAllApprovers;
2393
2394 function getAbsolutePosition(pReqHeaderId IN NUMBER,
2395 pAmeTransactionType IN VARCHAR2,
2396 pPosition IN NUMBER)
2397 return number IS
2398
2399 l_api_name varchar2(50):= 'getAbsolutePosition';
2400 approverList ame_util.approversTable2;
2401 absolutePosition number := pPosition;
2402 numOfNullStatus number := 0;
2403 l_process_out VARCHAR2(10);
2404
2405 begin
2406
2407 ame_api2.getAllApprovers7( applicationIdIn=>applicationId,
2408 transactionIdIn=>pReqHeaderId,
2409 transactionTypeIn=>pAmeTransactionType,
2410 approvalProcessCompleteYNOut => l_process_out,
2411 approversOut=>approverList
2412 );
2413
2414 for i in 1 .. approverList.count loop
2415 if(approverList(i).approval_status is not null) then
2416 absolutePosition := absolutePosition + 1;
2417 else
2418 numOfNullStatus := numOfNullStatus + 1;
2419 end if;
2420 if(numOfNullStatus >= pPosition) then
2421 exit;
2422 end if;
2423 end loop;
2424 return absolutePosition;
2425
2426 exception
2427 when others then
2428 if g_fnd_debug = 'Y' then
2429 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2430 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2431 l_api_name || '.others_exception', sqlerrm);
2432 END IF;
2433 end if;
2434 raise;
2435
2436 end getAbsolutePosition;
2437
2438 --------------------------------------------------------------------------------
2439 --Start of Comments
2440 --Name: retrieve_approval_info
2441 --Function:
2442 -- This procedure checks which approval is used.
2443 -- -- whether AME is used for approval or PO Hierarchy approval.
2444 --Parameters:
2445 --IN:
2446 -- pReqHeaderId Requisition Header ID
2447 -- pIsApprovalHistoryFlow Flag indicating whether function is being
2448 -- called from approval history flow
2449 --OUT:
2450 -- x_is_ame_approval 'Y' if AME is used for approval.
2451 -- x_approval_status Status of the req.
2452 -- pQuoteChar Quote Character, used for escaping purpose in tokenization
2453 -- x_is_rco_approval 'Y' if RCO approval.
2454 --End of Comments
2455 --------------------------------------------------------------------------------
2456 procedure retrieve_approval_info( p_req_header_id in number,
2457 p_is_approval_history_flow in varchar2,
2458 x_is_ame_approval out NOCOPY varchar2,
2459 x_approval_status out NOCOPY varchar2,
2460 x_is_rco_approval out NOCOPY varchar2
2461 ) IS
2462
2463 l_itemtype po_requisition_headers.wf_item_type%TYPE;
2464 l_itemkey po_requisition_headers.wf_item_key%TYPE;
2465 l_api_name varchar2(50):= 'retrieve_approval_info';
2466
2467 l_change_request_group_id NUMBER;
2468
2469 begin
2470
2471 if g_fnd_debug = 'Y' then
2472 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2473 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering retrieve_approval_info...');
2474 END IF;
2475 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2476 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - p_req_header_id :' || p_req_header_id );
2477 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - p_is_approval_history_flow :' || p_is_approval_history_flow );
2478
2479 END IF;
2480 end if;
2481
2482 -- Initialize all the output params.
2483 x_is_rco_approval := 'N';
2484 x_is_ame_approval := 'N';
2485 x_approval_status := null;
2486
2487
2488 select authorization_status, wf_item_type, wf_item_key
2489 into x_approval_status, l_itemtype, l_itemkey
2490 from po_requisition_headers_all
2491 where requisition_header_id = p_req_header_id;
2492
2493 if g_fnd_debug = 'Y' then
2494 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2495 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' x_approval_status :' || x_approval_status );
2496 END IF;
2497 end if;
2498
2499 -- Check whether it is ame approval or not.
2500 x_is_ame_approval := PO_WF_UTIL_PKG.GetItemAttrText( itemtype => l_itemtype,
2501 itemkey => l_itemkey,
2502 aname => 'IS_AME_APPROVAL'
2503 );
2504
2505 -- If the code is not being called from the approval history flow, check for pending change requests
2506 if (p_is_approval_history_flow <> 'Y') then
2507
2508 -- First check whether change request is there or not.
2509 BEGIN
2510 SELECT max(change_request_group_id) INTO l_change_request_group_id
2511 FROM po_change_requests
2512 WHERE document_header_id = p_req_header_id
2513 AND document_type = 'REQ';
2514 EXCEPTION
2515 WHEN OTHERS THEN
2516 l_change_request_group_id := null;
2517 END;
2518
2519 -- l_change_request_group_id is not null, then change request is there for the req.
2520 -- retrieve the status , wf_item_type and wf_item_key.
2521 IF l_change_request_group_id IS NOT NULL THEN
2522
2523 x_is_rco_approval := 'Y';
2524
2525 SELECT wf_item_type, wf_item_key,
2526 decode( request_status, 'ACCEPTED', x_approval_status,
2527 'MGR_APP', 'APPROVED',
2528 'REJECTED','REJECTED',
2529 'IN PROCESS' )
2530 INTO l_itemtype, l_itemkey, x_approval_status
2531 FROM po_change_requests
2532 WHERE document_header_id = p_req_header_id
2533 AND change_request_group_id = l_change_request_group_id
2534 AND document_type = 'REQ'
2535 AND action_type <> 'DERIVED'
2536 AND rownum = 1;
2537
2538 END IF;
2539
2540 end if; -- end of RCO check...
2541
2542 -- get the authorization status meaning from fnd_loopup_values_vl
2543 SELECT distinct meaning into x_approval_status
2544 FROM fnd_lookup_values_vl
2545 WHERE lookup_code = x_approval_status and lookup_type = 'AUTHORIZATION STATUS';
2546
2547 -- Check RCO approval type is ame approval or not.
2548 if( 'Y' = x_is_rco_approval ) then
2549 if( l_itemtype is not null AND l_itemkey is not null ) then
2550 x_is_ame_approval := PO_WF_UTIL_PKG.GetItemAttrText( itemtype => l_itemtype,
2551 itemkey => l_itemkey,
2552 aname => 'IS_AME_APPROVAL'
2553 );
2554 end if;
2555 end if;
2556
2557 if g_fnd_debug = 'Y' then
2558 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2559 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' x_is_ame_approval :' || x_is_ame_approval );
2560 END IF;
2561 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2562 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' x_is_rco_approval :' || x_is_rco_approval );
2563 END IF;
2564 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2565 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' x_approval_status :' || x_approval_status );
2566 END IF;
2567 end if;
2568
2569 exception
2570 when others then
2571 if g_fnd_debug = 'Y' then
2572 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2573 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2574 l_api_name || '.others_exception', sqlerrm);
2575 END IF;
2576 end if;
2577 raise;
2578 end RETRIEVE_APPROVAL_INFO;
2579
2580 --------------------------------------------------------------------------------
2581 --Start of Comments
2582 --Name: retrieve_approver_info
2583 --Function:
2584 -- This procedure retrieves the approver's title and email.
2585 --Parameters:
2586 --IN:
2587 -- pReqHeaderId Requisition Header ID
2588 --OUT:
2589 -- x_title - title of the approver.
2590 -- x_email - email of the approver.
2591 --End of Comments
2592 --------------------------------------------------------------------------------
2593 procedure retrieve_approver_info( p_approver_id in number,
2594 x_title out NOCOPY varchar2,
2595 x_email out NOCOPY varchar2
2596 ) IS
2597
2598 l_api_name varchar2(50):= 'retrieve_approver_info';
2599 l_position_id number;
2600 l_job_id number;
2601
2602 begin
2603
2604 if g_fnd_debug = 'Y' then
2605 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2606 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering retrieve_approver_info...');
2607 END IF;
2608 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2609 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - p_approver_id :' || p_approver_id );
2610 END IF;
2611 end if;
2612
2613 x_title := null;
2614 x_email := null;
2615
2616 -- first get the email id.
2617 SELECT email_address INTO x_email FROM per_all_people_f
2618 WHERE person_id = p_approver_id
2619 AND TRUNC ( effective_start_date ) <= TRUNC(SYSDATE) AND NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
2620 AND rownum = 1;
2621
2622 if g_fnd_debug = 'Y' then
2623 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2624 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' x_email :' || x_email );
2625 END IF;
2626 end if;
2627
2628 -- retrieve the position name. if the position name is null check for the job name.
2629 SELECT position_id, job_id INTO l_position_id, l_job_id
2630 FROM per_all_assignments_f
2631 WHERE person_id = p_approver_id
2632 and primary_flag = 'Y' and assignment_type in ('E','C')
2633 and assignment_status_type_id not in ( select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
2634 and TRUNC ( effective_start_date ) <= TRUNC(SYSDATE) AND NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
2635 and rownum = 1;
2636
2637 if l_position_id is not null then
2638 SELECT name INTO x_title FROM per_all_positions WHERE position_id = l_position_id;
2639 end if;
2640
2641 if x_title is null and l_job_id is not null then
2642 SELECT name INTO x_title FROM per_jobs WHERE job_id = l_job_id;
2643 end if;
2644
2645 if g_fnd_debug = 'Y' then
2646 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2647 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' x_title :' || x_title );
2648 END IF;
2649 end if;
2650
2651 exception
2652 when others then
2653 if g_fnd_debug = 'Y' then
2654 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2655 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2656 l_api_name || '.others_exception', sqlerrm);
2657 END IF;
2658 end if;
2659 end retrieve_approver_info;
2660 --------------------------------------------------------------------------------
2661 --Start of Comments
2662 --Name: get_approval_group_name
2663 --Function:
2664 -- This procedure retrieves the approval group name for the given group id.
2665 --Parameters:
2666 --IN:
2667 -- p_group_id Group Id
2668 --OUT:
2669 -- x_group_name - Group name for the given group id.
2670 --End of Comments
2671 --------------------------------------------------------------------------------
2672 function get_approval_group_name( p_group_id in number ) return varchar2 IS
2673
2674 l_api_name varchar2(50):= 'get_approval_group_name';
2675 l_group_name varchar2(1000);
2676
2677 begin
2678
2679 if p_group_id is null then
2680 return '';
2681 end if;
2682
2683 if g_fnd_debug = 'Y' then
2684 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2685 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering get_approval_group_name...');
2686 END IF;
2687 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2688 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - p_group_id :' || p_group_id );
2689 END IF;
2690 end if;
2691
2692 -- If the group id is less than 3, then it is not approval group.
2693 if p_group_id < 3 then
2694 return '';
2695 end if;
2696
2697 l_group_name := 'Group:' || p_group_id;
2698
2699 ame_api5.getApprovalGroupName( groupIdIn => p_group_id,
2700 groupNameOut => l_group_name
2701 );
2702
2703 if g_fnd_debug = 'Y' then
2704 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2705 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_group_name :' || l_group_name );
2706 END IF;
2707 end if;
2708
2709 return l_group_name;
2710
2711 exception
2712 when others then
2713 l_group_name := 'Group:' || p_group_id;
2714 if g_fnd_debug = 'Y' then
2715 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2716 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2717 l_api_name || '.others_exception', sqlerrm);
2718 END IF;
2719 end if;
2720 return l_group_name;
2721 end get_approval_group_name;
2722
2723 --------------------------------------------------------------------------------
2724 --Start of Comments
2725 --Name: get_ame_approval_list_history
2726 --Function:
2727 -- Call AME API to build the approver list history.
2728 --Parameters:
2729 --IN:
2730 -- pReqHeaderId Requisition Header ID
2731 -- pCallingPage The page from which the function is being called
2732 --OUT:
2733 -- pApprovalListStr Approval List concatenated in a string
2734 -- pApprovalListCount Number of Approvers.
2735 -- It has a value of 0, if the document does not require approval.
2736 -- pQuoteChar Quote Character, used for escaping purpose in tokenization
2737 -- pFieldDelimiter Field Delimiter, used for delimiting list string into elements.
2738 --End of Comments
2739 --------------------------------------------------------------------------------
2740 procedure get_ame_approval_list_history( pReqHeaderId IN NUMBER,
2741 pCallingPage IN VARCHAR2,
2742 pApprovalListStr OUT NOCOPY VARCHAR2,
2743 pApprovalListCount OUT NOCOPY NUMBER,
2744 pQuoteChar OUT NOCOPY VARCHAR2,
2745 pFieldDelimiter OUT NOCOPY VARCHAR2
2746 ) IS
2747
2748 l_api_name varchar2(50):= 'get_ame_approval_list_history';
2749 ameTransactionType po_document_types.ame_transaction_type%TYPE;
2750
2751 l_itemtype po_requisition_headers.wf_item_type%TYPE;
2752 l_itemkey po_requisition_headers.wf_item_key%TYPE;
2753
2754 approverList ame_util.approversTable2;
2755 l_process_out VARCHAR2(10);
2756
2757 l_full_name per_all_people_f.full_name%TYPE;
2758 l_person_id per_all_people_f.person_id%TYPE;
2759 l_job_or_position VARCHAR2(2000);
2760 l_orig_system VARCHAR2(30);
2761 l_orig_system_id NUMBER;
2762
2763 l_job_id number;
2764 l_position_id number;
2765 l_valid_approver VARCHAR2(1);
2766
2767 l_preparer_id NUMBER;
2768 l_first_approver_id NUMBER;
2769 l_first_position_id NUMBER;
2770 l_org_id NUMBER;
2771 p_doc_type po_document_types_all_b.document_type_code%type;
2772 p_doc_subtype po_document_types_all_b.document_subtype%type;
2773 l_authorizationStatus po_requisition_headers_all.AUTHORIZATION_STATUS%type;
2774 l_change_pending_flag po_requisition_headers_all.CHANGE_PENDING_FLAG%type;
2775 l_approval_reqd_flag PO_CHANGE_REQUESTS.APPROVAL_REQUIRED_FLAG%type;
2776
2777 BEGIN
2778
2779 if g_fnd_debug = 'Y' then
2780 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2781 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering get_ame_approval_list_history...');
2782 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pReqHeaderId :' || pReqHeaderId );
2783 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' current org Id: ' || PO_MOAC_UTILS_PVT.get_current_org_id);
2784 END IF;
2785 end if;
2786
2787 select PREPARER_ID,
2788 first_position_id,
2789 first_approver_id,
2790 org_id,
2791 AUTHORIZATION_STATUS,
2792 CHANGE_PENDING_FLAG
2793 into l_preparer_id,
2794 l_first_position_id,
2795 l_first_approver_id,
2796 l_org_id,
2797 l_authorizationStatus,
2798 l_change_pending_flag
2799 from po_requisition_headers_all
2800 where pReqHeaderId = requisition_header_id;
2801
2802 --PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2803
2804 if g_fnd_debug = 'Y' then
2805 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2806 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' PreparerID for this req :' || l_preparer_id );
2807 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_first_position_id: ' || l_first_position_id);
2808 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_first_approver_id: ' || l_first_approver_id);
2809 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_authorizationStatus: ' || l_authorizationStatus);
2810 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_change_pending_flag: ' || l_change_pending_flag);
2811 END IF;
2812 end if;
2813
2814 pQuoteChar := quoteChar;
2815 pFieldDelimiter := fieldDelimiter;
2816
2817 approvalListStr := NULL;
2818 pApprovalListCount := 0;
2819
2820 /*
2821 If the function is being called from approval history page or the change history page
2822 Set the appropriate document type and subtype and then get the corresponding
2823 ame transaction type
2824 */
2825
2826 if (pCallingPage = 'fromChangeHistoryPage' OR pCallingPage = 'fromRCONotificationPage') then
2827 -- We are in RCO Modes...
2828 -- need to check if approval is even required.
2829 -- If not, then no approval needed for this RCO
2830 -- Simply return and do NOT build AME list
2831 begin
2832 select DISTINCT nvl(APPROVAL_REQUIRED_FLAG, 'N')
2833 into l_approval_reqd_flag
2834 from PO_CHANGE_REQUESTS
2835 where DOCUMENT_HEADER_ID = pReqHeaderId
2836 and action_type IN ('MODIFICATION', 'CANCELLATION')
2837 and creation_date = (select max(creation_date)
2838 from PO_CHANGE_REQUESTS
2839 where DOCUMENT_HEADER_ID = pReqHeaderId);
2840 exception
2841 when others then
2842 l_approval_reqd_flag := 'Y';
2843 end;
2844
2845 if (l_approval_reqd_flag = 'N') then
2846
2847 if g_fnd_debug = 'Y' then
2848 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2849 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' No Approval Required! RETURNING....');
2850 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving get_ame_approval_list...');
2851 END IF;
2852 end if;
2853
2854 pApprovalListCount := 0;
2855 pApprovalListStr := '';
2856
2857 RETURN;
2858 END IF;
2859
2860 -- otherwise, AME required... continue with existing logic.
2861 p_doc_type := 'CHANGE_REQUEST';
2862 p_doc_subtype := 'REQUISITION';
2863 else
2864 p_doc_type := 'REQUISITION';
2865
2866 begin
2867 SELECT type_lookup_code
2868 INTO p_doc_subtype
2869 FROM po_requisition_headers_all
2870 WHERE requisition_header_id = pReqHeaderId;
2871
2872 exception
2873 when others then
2874 p_doc_subtype := 'PURCHASE';
2875 end;
2876
2877 end if;
2878
2879 ameTransactionType := getAmeTxnType(p_doc_type, p_doc_subtype, l_org_id);
2880
2881 if g_fnd_debug = 'Y' then
2882 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2883 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'ameTransactionType: ' || ameTransactionType);
2884 END IF;
2885 end if;
2886
2887 -- if called from RCO page or RCO notification, we use change_pending_flag to determine if txn is in process
2888 -- otherwise, we look at authorizationStatus
2889
2890 if ( (pCallingPage <> 'fromRCONotificationPage' AND pCallingPage <> 'fromChangeHistoryPage' AND l_authorizationStatus = 'IN PROCESS') OR
2891 (pCallingPage = 'fromRCONotificationPage' AND l_change_pending_flag = 'Y') OR
2892 (pCallingPage = 'fromChangeHistoryPage' AND l_change_pending_flag = 'Y') ) then
2893
2894 -- if req is in process, then use ame_api2 which will rebuild the approval list
2895 if g_fnd_debug = 'Y' then
2896 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2897 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Req is IN PROCESS: Using ame_api2 to REBUILD');
2898 END IF;
2899 end if;
2900
2901 -- If we are going to use the flag approvalProcessCompleteYNOut,
2902 -- then we have to set the following flag.
2903 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanTrue;
2904
2905 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
2906 transactionIdIn => pReqHeaderId,
2907 transactionTypeIn => ameTransactionType,
2908 approvalProcessCompleteYNOut => l_process_out,
2909 approversOut => approverList
2910 );
2911
2912 if g_fnd_debug = 'Y' then
2913 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2914 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'approvalProcessCompleteYNOut = ' || l_process_out);
2915 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'getAllApprovers7 Done');
2916 END IF;
2917 end if;
2918
2919 else
2920 -- otherwise, req is completed... so use ame_api6 to get the stored history w/o rebuilding
2921
2922 if g_fnd_debug = 'Y' then
2923 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2924 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Req is COMPLETED: Using ame_api6 - NO REBUILD');
2925 END IF;
2926 end if;
2927
2928 ame_api6.getApprovers(applicationIdIn => applicationId,
2929 transactionTypeIn => ameTransactionType,
2930 transactionIdIn => pReqHeaderId,
2931 approversOut => approverList);
2932
2933 -- process is completed. Set flag.
2934 l_process_out := 'Y';
2935
2936 if g_fnd_debug = 'Y' then
2937 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2938 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'getApprovers Done');
2939 END IF;
2940 end if;
2941
2942 END IF;
2943
2944 -- Iterate through the list of approvers.
2945 for i in 1 .. approverList.count loop
2946
2947 l_valid_approver := 'Y';
2948 if g_fnd_debug = 'Y' then
2949 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2950 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Processing the approver :' || i );
2951 END IF;
2952 end if;
2953
2954 if (l_preparer_id=approverList(i).orig_system_id) THEN
2955 l_valid_approver := 'N';
2956 if g_fnd_debug = 'Y' then
2957 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2958 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' ApproverID matches PreparerID... invalid approver!');
2959 END IF;
2960 end if;
2961 end if;
2962
2963 if( ( ( l_process_out = 'Y' OR l_process_out = 'N' ) AND
2964 ( approverList(i).approval_status is not null AND
2965 ( approverList(i).approval_status not in
2966 ( ame_util.notifiedByRepeatedStatus,
2967 ame_util.approvedByRepeatedStatus,
2968 ame_util.rejectedByRepeatedStatus,
2969 ame_util.repeatedStatus,
2970 ame_util.noResponseStatus
2971 )
2972 )
2973 )
2974 ) OR
2975 ( ( l_process_out = 'W' OR l_process_out = 'P' )AND
2976 ( approverList(i).approval_status is null OR
2977 ( approverList(i).approval_status not in
2978 ( ame_util.notifiedByRepeatedStatus,
2979 ame_util.approvedByRepeatedStatus,
2980 ame_util.rejectedByRepeatedStatus,
2981 ame_util.repeatedStatus,
2982 ame_util.noResponseStatus
2983 )
2984 )
2985 )
2986 )
2987 ) then
2988
2989 l_orig_system := approverList(i).orig_system;
2990 l_orig_system_id := approverList(i).orig_system_id;
2991 l_job_or_position := NULL;
2992
2993 if ( l_orig_system = ame_util.perOrigSystem) then
2994
2995 -- Employee Supervisor Record.
2996 if g_fnd_debug = 'Y' then
2997 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2998 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Emp - Sup Record ...');
2999 END IF;
3000 end if;
3001 l_full_name := approverList(i).display_name;
3002 l_person_id := l_orig_system_id;
3003
3004 elsif ( l_orig_system = ame_util.posOrigSystem) then
3005
3006 -- Position Hierarchy Record. The logic is mentioned in the comments section.
3007 if g_fnd_debug = 'Y' then
3008 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3009 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Position Hierarchy Record ...');
3010 END IF;
3011 end if;
3012
3013 begin
3014
3015 if (l_first_position_id is not NULL AND l_first_position_id=l_orig_system_id) then
3016
3017 if g_fnd_debug = 'Y' then
3018 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3019 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Approver position matches l_first_position_id.');
3020 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Using stored l_first_approver_id as person_id.');
3021 END IF;
3022 end if;
3023
3024 l_person_id := l_first_approver_id;
3025
3026 SELECT full_name
3027 INTO l_full_name
3028 FROM per_all_people_f
3029 WHERE person_id = l_first_approver_id
3030 AND TRUNC(sysdate) between effective_start_date and effective_end_date;
3031
3032 else
3033 SELECT person_id, full_name into l_person_id,l_full_name FROM (
3034 SELECT person.person_id, person.full_name FROM per_all_people_f person, per_all_assignments_f asg
3035 WHERE asg.position_id = l_orig_system_id and trunc(sysdate) between person.effective_start_date
3036 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
3037 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
3038 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
3039 and asg.assignment_status_type_id not in (
3040 SELECT assignment_status_type_id FROM per_assignment_status_types
3041 WHERE per_system_status = 'TERM_ASSIGN'
3042 ) and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date order by person.last_name
3043 ) where rownum = 1;
3044
3045 end if;
3046
3047 exception
3048 WHEN NO_DATA_FOUND THEN
3049 --RAISE;
3050 l_valid_approver := 'N';
3051 END;
3052
3053 elsif (l_orig_system = ame_util.fndUserOrigSystem) then
3054
3055 -- FND User Record.
3056 if g_fnd_debug = 'Y' then
3057 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3058 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'FND User Record ...');
3059 END IF;
3060 end if;
3061 SELECT employee_id into l_person_id
3062 FROM fnd_user
3063 WHERE user_id = l_orig_system_id
3064 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
3065
3066 l_full_name := approverList(i).display_name;
3067
3068 end if;
3069
3070 if g_fnd_debug = 'Y' then
3071 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3072 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_full_name :' || l_full_name );
3073 END IF;
3074 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3075 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_person_id :' || l_person_id );
3076 END IF;
3077 end if;
3078
3079 -- Find position | job name
3080 if ( l_orig_system = ame_util.posOrigSystem ) then
3081 l_job_or_position := approverList(i).display_name;
3082 else
3083 l_job_or_position := null;
3084 end if;
3085
3086 -- Make sure position/job name is populated.
3087 if( l_job_or_position is null ) then
3088
3089 -- retrieve the position name. if the position name is null check for the job name.
3090
3091 -- bug #15883770 relax the condition on employee assignment, that is
3092 -- to allow user to view the approval list regardless the current
3093 -- employee status (Employee, Ex-employee , etc..) for approved requisition
3094 SELECT position_id, job_id INTO l_position_id, l_job_id
3095 FROM per_all_assignments_f
3096 WHERE person_id = l_person_id
3097 and (l_authorizationStatus = 'APPROVED'
3098 or (primary_flag = 'Y' and assignment_type in ('E','C')
3099 and assignment_status_type_id not in ( select assignment_status_type_id from per_assignment_status_types where per_system_status = 'TERM_ASSIGN')
3100 and TRUNC ( effective_start_date ) <= TRUNC(SYSDATE) AND NVL(effective_end_date, TRUNC( SYSDATE)) >= TRUNC(SYSDATE)
3101 )
3102 )
3103 and rownum = 1;
3104
3105 if l_position_id is not null then
3106 SELECT name INTO l_job_or_position FROM per_all_positions WHERE position_id = l_position_id;
3107 end if;
3108
3109 if l_job_or_position is null and l_job_id is not null then
3110 SELECT name INTO l_job_or_position FROM per_jobs WHERE job_id = l_job_id;
3111 end if;
3112
3113 if g_fnd_debug = 'Y' then
3114 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3115 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_job_or_position :' || l_job_or_position );
3116 END IF;
3117 end if;
3118
3119 end if;
3120
3121 if( l_valid_approver = 'Y' ) then
3122 marshalField(l_full_name, quoteChar, fieldDelimiter);
3123 marshalField( to_char(l_person_id), quoteChar, fieldDelimiter);
3124 marshalField(l_job_or_position, quoteChar, fieldDelimiter);
3125 marshalField(approverList(i).name, quoteChar, fieldDelimiter);
3126
3127 --marshalField(approversTableIn(i).orig_system, quoteChar, fieldDelimiter);
3128 --marshalField(to_char(approversTableIn(i).orig_system_id), quoteChar, fieldDelimiter);
3129
3130 marshalField(l_orig_system, quoteChar, fieldDelimiter);
3131 marshalField(to_char(l_orig_system_id), quoteChar, fieldDelimiter);
3132
3133 marshalField(approverList(i).api_insertion, quoteChar, fieldDelimiter);
3134 marshalField(approverList(i).authority, quoteChar, fieldDelimiter);
3135 marshalField(approverList(i).approval_status, quoteChar, fieldDelimiter);
3136 marshalField(approverList(i).approver_category, quoteChar, fieldDelimiter);
3137 marshalField(approverList(i).approver_order_number, quoteChar, fieldDelimiter);
3138 marshalField(approverList(i).action_type_id, quoteChar, fieldDelimiter);
3139 marshalField(approverList(i).group_or_chain_id, quoteChar, fieldDelimiter);
3140 marshalField(approverList(i).member_order_number, quoteChar, fieldDelimiter);
3141 --marshalField(to_char(i), quoteChar, fieldDelimiter);
3142 pApprovalListCount := pApprovalListCount +1;
3143 marshalField(to_char(pApprovalListCount), quoteChar, fieldDelimiter);
3144 end if;
3145
3146 end if;
3147 end loop;
3148
3149 pApprovalListStr := approvalListStr;
3150
3151 if g_fnd_debug = 'Y' then
3152 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3153 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListStr :' || pApprovalListStr);
3154 END IF;
3155 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3156 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Output param -- pApprovalListCount :' || pApprovalListCount);
3157 END IF;
3158 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3159 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving get_ame_approval_list...');
3160 END IF;
3161 end if;
3162
3163 exception
3164 when NO_DATA_FOUND then
3165 pApprovalListCount := 0;
3166 pApprovalListStr := 'NO_DATA_FOUND';
3167 if g_fnd_debug = 'Y' then
3168 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3169 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3170 l_api_name || '.NO_DATA_FOUND', 'NO_DATA_FOUND');
3171 END IF;
3172 end if;
3173 when others then
3174 pApprovalListCount := 0;
3175 pApprovalListStr := 'EXCEPTION';
3176 if g_fnd_debug = 'Y' then
3177 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3178 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3179 l_api_name || '.others_exception', sqlerrm);
3180 END IF;
3181 end if;
3182 END get_ame_approval_list_history;
3183
3184
3185 --------------------------------------------------------------------------------
3186 --Start of Comments
3187 --Name: get_next_approvers_info
3188 --Function:
3189 -- Call AME API to get approverId and approverName
3190
3191 --Parameters:
3192 --IN:
3193 -- pReqHeaderId Requisition Header ID
3194
3195 --OUT:
3196 -- x_approverId Approver's ID
3197 -- x_approverName Full name of the approver
3198 --End of Comments
3199 --------------------------------------------------------------------------------
3200 procedure get_next_approvers_info(pReqHeaderId IN NUMBER,
3201 x_approverId OUT NOCOPY NUMBER,
3202 x_approverName OUT NOCOPY VARCHAR2
3203 ) IS
3204
3205 l_itemtype po_requisition_headers.wf_item_type%TYPE;
3206 l_itemkey po_requisition_headers.wf_item_key%TYPE;
3207 l_ameTransactionType po_document_types.ame_transaction_type%TYPE;
3208 l_completeYNO varchar2(100);
3209
3210 l_approversList ame_util.approversTable2;
3211 l_approver_index NUMBER;
3212 l_preparer_id number;
3213 l_approverId number;
3214 l_approverName VARCHAR2(240);
3215 l_next_app_count number :=0;
3216
3217 l_api_name varchar2(50):= 'get_next_approvers_info';
3218 x_progress varchar2(100);
3219
3220 BEGIN
3221 if g_fnd_debug = 'Y' then
3222 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3223 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering get_next_approvers_info...');
3224 END IF;
3225 end if;
3226
3227 x_progress := '001';
3228 getAmeTransactionType(pReqHeaderId => pReqHeaderId,
3229 pAmeTransactionType => l_ameTransactionType);
3230
3231 x_progress := '002';
3232
3233 ame_api2.getAllApprovers7( applicationIdIn => applicationId,
3234 transactionIdIn => pReqHeaderId,
3235 transactionTypeIn => l_ameTransactionType,
3236 approvalProcessCompleteYNOut => l_completeYNO,
3237 approversOut => l_approversList
3238 );
3239
3240 x_progress := '003';
3241
3242 select PREPARER_ID
3243 into l_preparer_id
3244 from po_requisition_headers_all
3245 where pReqHeaderId = requisition_header_id;
3246
3247 x_progress := '004';
3248
3249 for i in 1 .. l_approversList.count loop
3250 if ( l_approversList(i).approval_status= 'NOTIFIED'
3251 and l_approversList(i).approver_category = ame_util.approvalApproverCategory ) then
3252
3253 get_person_info( l_approversList(i).orig_system,
3254 l_approversList(i).orig_system_id,
3255 l_approversList(i).display_name,
3256 pReqHeaderId,
3257 g_fnd_debug,
3258 l_approverId,
3259 l_approverName);
3260
3261 if (l_approverId <> l_preparer_id ) then
3262 l_next_app_count := l_next_app_count + 1;
3263 x_approverId := l_approverId;
3264 x_approverName := l_approverName;
3265 end if;
3266
3267 end if;
3268 end loop;
3269
3270 x_progress := '006';
3271
3272 if (l_next_app_count > 1 ) then
3273
3274 -- if more than 1 approvers, approver_name is shown as 'MULTIPLE'
3275 x_approverName := 'MULTIPLE';
3276 x_approverId := NULL;
3277
3278 end if;
3279
3280 x_progress := '007';
3281
3282 if g_fnd_debug = 'Y' then
3283 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3284 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'x_approverId :' || x_approverId );
3285 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' x_approverName:' || x_approverName);
3286 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Leaving get_next_approvers_info...');
3287 END IF;
3288 end if;
3289
3290 EXCEPTION
3291 when others then
3292 x_approverId := -999;
3293 x_approverName := null;
3294 if g_fnd_debug = 'Y' then
3295 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3296 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3297 l_api_name || '.others_exception', sqlerrm);
3298 END IF;
3299 end if;
3300 raise;
3301
3302 END get_next_approvers_info;
3303
3304
3305 procedure get_person_info( p_origSystem IN VARCHAR2,
3306 p_origSystemId IN NUMBER,
3307 p_displayName IN VARCHAR2,
3308 p_reqHeaderId IN NUMBER,
3309 p_logFlag IN VARCHAR2,
3310 x_personId OUT NOCOPY NUMBER,
3311 x_fullName OUT NOCOPY VARCHAR2
3312 ) IS
3313
3314 l_first_approver_id NUMBER;
3315 l_first_position_id NUMBER;
3316
3317 l_api_name varchar2(50):= 'get_person_info';
3318
3319 BEGIN
3320
3321 if p_logFlag = 'Y' then
3322 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3323 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'get_person_info(+)');
3324 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' p_origSystem = ' || p_origSystem);
3325 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' p_origSystemId = ' || p_origSystemId);
3326 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' p_displayName = ' || p_displayName);
3327 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' p_reqHeaderId = ' || p_reqHeaderId);
3328 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' p_logFlag = ' || p_logFlag);
3329 END IF;
3330 end if;
3331
3332 if ( p_origSystem = ame_util.perOrigSystem) then
3333
3334 -- Employee Supervisor Record.
3335 if p_logFlag = 'Y' then
3336 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3337 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Employee Supervisor Record ...');
3338 END IF;
3339 end if;
3340
3341 x_fullName := p_displayName;
3342 x_personId := p_origSystemId;
3343
3344 elsif ( p_origSystem = ame_util.posOrigSystem) then
3345
3346 -- Position Hierarchy Record.
3347 if p_logFlag = 'Y' then
3348 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3349 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Position Hierarchy Record ...');
3350 END IF;
3351 end if;
3352
3353 select first_position_id, first_approver_id
3354 into l_first_position_id, l_first_approver_id
3355 from po_requisition_headers_all
3356 where p_reqHeaderId = requisition_header_id;
3357
3358 if p_logFlag = 'Y' then
3359 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3360 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_first_position_id: ' || l_first_position_id);
3361 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' l_first_approver_id: ' || l_first_approver_id);
3362 END IF;
3363 end if;
3364
3365 if (l_first_position_id is not NULL AND l_first_position_id = p_origSystemId) then
3366 -- use stored approver_id since position_id matches stored id
3367
3368 if p_logFlag = 'Y' then
3369 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3370 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Approver position matches l_first_position_id.');
3371 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' Using stored l_first_approver_id as person_id.');
3372 END IF;
3373 end if;
3374
3375 x_personId := l_first_approver_id;
3376
3377 SELECT full_name
3378 INTO x_fullName
3379 FROM per_all_people_f
3380 WHERE person_id = l_first_approver_id
3381 AND trunc(sysdate) between effective_start_date and effective_end_date;
3382
3383 else
3384 SELECT person_id, full_name
3385 into x_personId, x_fullName
3386 FROM (
3387 SELECT person.person_id, person.full_name
3388 FROM per_all_people_f person, per_all_assignments_f asg
3389 WHERE asg.position_id = p_origSystemId
3390 and trunc(sysdate) between person.effective_start_date
3391 and nvl(person.effective_end_date, trunc(sysdate)) and person.person_id = asg.person_id
3392 and asg.primary_flag = 'Y' and asg.assignment_type in ('E','C')
3393 and ( person.current_employee_flag = 'Y' or person.current_npw_flag = 'Y' )
3394 and asg.assignment_status_type_id not in (
3395 SELECT assignment_status_type_id
3396 FROM per_assignment_status_types
3397 WHERE per_system_status = 'TERM_ASSIGN'
3398 )
3399 and trunc(sysdate) between asg.effective_start_date and asg.effective_end_date
3400 order by person.last_name
3401 )
3402 WHERE rownum = 1;
3403
3404 end if;
3405
3406 elsif (p_origSystem = ame_util.fndUserOrigSystem) then
3407
3408 -- FND User Record.
3409 if p_logFlag = 'Y' then
3410 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3411 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' FND User Record ...');
3412 END IF;
3413 end if;
3414
3415 SELECT employee_id
3416 into x_personId
3417 FROM fnd_user
3418 WHERE user_id = p_origSystemId
3419 and trunc(sysdate) between start_date and nvl(end_date, sysdate+1);
3420
3421 x_fullName := p_displayName;
3422
3423 end if;
3424
3425 if p_logFlag = 'Y' then
3426 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3427 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' x_fullName :' || x_fullName );
3428 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, ' x_personId :' || x_personId );
3429 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'get_person_info(-)');
3430 END IF;
3431 end if;
3432
3433 end get_person_info;
3434
3435 /* public API */
3436 --------------------------------------------------------------------------------
3437 --Start of Comments
3438 --Name: is_req_forward_valid
3439 --Function:
3440 -- Call AME API to check whether the requisition can be forwareded in case of AME approvals
3441 --Parameters:
3442 --IN:
3443 -- pReqHeaderId Requisition Header ID
3444 -- pPersonId Person ID of a new first approver
3445 --OUT:
3446 -- pApprovalListStr Approval List concatenated in a string
3447 -- pApprovalListCount Number of Approvers.
3448 -- It has a value of 0, if the document does not require approval.
3449 -- pQuoteChar Quote Character, used for escaping purpose in tokenization
3450 -- pFieldDelimiter Field Delimiter, used for delimiting list string into elements.
3451 --End of Comments
3452 --------------------------------------------------------------------------------
3453 FUNCTION is_req_forward_valid( pReqHeaderId IN NUMBER) RETURN VARCHAR2 IS
3454
3455 l_api_name varchar2(50):= 'is_req_forward_valid';
3456 tmpApprover ame_util.approverRecord2;
3457 ameTransactionType po_document_types.ame_transaction_type%TYPE;
3458
3459 approverList ame_util.approversTable2;
3460 l_process_out VARCHAR2(10);
3461 currentFirstApprover ame_util.approverRecord2;
3462 approvalList VARCHAR2(3000);
3463 apprCt NUMBER;
3464 appChar VARCHAR2(3000);
3465 appDelim VARCHAR2(3000);
3466 flag VARCHAR2(1) := 'Y';
3467 begin
3468 if g_fnd_debug = 'Y' then
3469 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3470 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Entering is_req_forward_valid...');
3471 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Input param - pReqHeaderId :' || pReqHeaderId );
3472 END IF;
3473 end if;
3474
3475 getAmeTransactionType(pReqHeaderId => pReqHeaderId,
3476 pAmeTransactionType => ameTransactionType);
3477
3478
3479 if g_fnd_debug = 'Y' then
3480 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3481 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Invoking ame api ame_api2.getAllApprovers7() to get the list of approvers from AME.' ||ameTransactionType );
3482 END IF;
3483 end if;
3484
3485 IF ameTransactionType IS NOT NULL THEN
3486 -- get the current approvers list from AME.
3487 ame_api2.getAllApprovers7( applicationIdIn=>applicationId,
3488 transactionIdIn=>pReqHeaderId,
3489 transactionTypeIn=>ameTransactionType,
3490 approvalProcessCompleteYNOut => l_process_out,
3491 approversOut=>approverList
3492 );
3493
3494 if g_fnd_debug = 'Y' then
3495 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3496 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Retrieved the list of approvers from AME using ame_api2.getAllApprovers7()');
3497 END IF;
3498 end if;
3499
3500 -- Once we get the approvers list from AME, we iterate through the approvers list,
3501 -- to find out the current first authority approver.
3502 for i in 1 .. approverList.count loop
3503 if( approverList(i).authority = ame_util.authorityApprover
3504 and approverList(i).approval_status is null
3505 and approverList(i).api_insertion = 'N'
3506 and approverList(i).group_or_chain_id < 3 ) then
3507
3508 if g_fnd_debug = 'Y' then
3509 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3510 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Found the first authority approver...' || currentFirstApprover.name );
3511 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Returing can forward Y' );
3512
3513 END IF;
3514 end if;
3515
3516 RETURN 'Y';
3517 end if;
3518 end loop;
3519 ELSE
3520 RETURN 'Y';
3521
3522
3523 END IF;
3524 if g_fnd_debug = 'Y' then
3525 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
3526 FND_LOG.string(FND_LOG.level_statement, g_module_prefix || l_api_name, 'Returing cannot forward N' );
3527
3528 END IF;
3529 end if;
3530
3531 RETURN 'N';
3532
3533 exception
3534 when NO_DATA_FOUND then
3535
3536
3537 if g_fnd_debug = 'Y' then
3538 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3539 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3540 l_api_name || '.NO_DATA_FOUND', 'NO_DATA_FOUND');
3541 END IF;
3542 end if;
3543 RETURN 'N';
3544
3545 when others then
3546 if g_fnd_debug = 'Y' then
3547 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3548 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3549 l_api_name || '.others_exception', sqlerrm);
3550 END IF;
3551 end if;
3552 ROLLBACK TO is_req_forward_valid;
3553 RETURN 'N';
3554
3555 end;
3556
3557 /* For bug 16064617 :: adding following proc which will be used in a New WF EVENT
3558 created for clearing AME approval list when approver rejects the requisition
3559 and Reject action gets successful just before sending FYI notification to preparer
3560 about rejection of document. */
3561
3562 procedure Clear_ame_apprv_list_reject(itemtype in varchar2,
3563 itemkey in varchar2,
3564 actid in number,
3565 funcmode in varchar2,
3566 resultout out NOCOPY varchar2) IS
3567
3568 l_document_type PO_DOCUMENT_TYPES_ALL.document_type_code%TYPE;
3569 l_document_subtype PO_DOCUMENT_TYPES_ALL.document_subtype%TYPE;
3570 l_document_id NUMBER;
3571 l_ame_Transaction_Type PO_DOCUMENT_TYPES_ALL.ame_transaction_type%TYPE;
3572 l_progress VARCHAR2(300) := '000';
3573 l_doc_string varchar2(200);
3574 l_preparer_user_name varchar2(100);
3575 l_orgid number;
3576
3577 BEGIN
3578
3579 -- Do nothing in cancel or timeout mode
3580 --
3581 if (funcmode <> wf_engine.eng_run) then
3582
3583 resultout := wf_engine.eng_null;
3584 return;
3585
3586 end if;
3587
3588 l_progress := 'Get doc attributes: 001';
3589 IF (g_po_wf_debug = 'Y') THEN
3590 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3591 END IF;
3592
3593 l_document_type := wf_engine.GetItemAttrText (itemtype => itemtype,
3594 itemkey => itemkey,
3595 aname => 'DOCUMENT_TYPE');
3596
3597 l_document_subtype := wf_engine.GetItemAttrText (itemtype => itemtype,
3598 itemkey => itemkey,
3599 aname => 'DOCUMENT_SUBTYPE');
3600
3601 l_document_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3602 itemkey => itemkey,
3603 aname => 'DOCUMENT_ID');
3604
3605 l_progress := 'Get doc attributes: 002 l_document_type :: '||l_document_type||
3606 'l_document_subtype :: '||l_document_subtype||'l_document_id :: '||l_document_id;
3607
3608 IF (g_po_wf_debug = 'Y') THEN
3609 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3610 END IF;
3611
3612 /*
3613 ** Desc Need to set the org context
3614 */
3615
3616 l_progress := 'Get org_id: 003';
3617 IF (g_po_wf_debug = 'Y') THEN
3618 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3619 END IF;
3620
3621 l_orgid := wf_engine.GetItemAttrNumber (itemtype => itemtype,
3622 itemkey => itemkey,
3623 aname => 'ORG_ID');
3624
3625 l_progress := 'Get doc attributes: 004 l_orgid :: '||l_orgid;
3626
3627 IF (g_po_wf_debug = 'Y') THEN
3628 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3629 END IF;
3630
3631 IF l_orgid is NOT NULL THEN
3632 PO_MOAC_UTILS_PVT.set_org_context(l_orgid) ; -- <R12.MOAC>
3633 END IF;
3634
3635 l_progress := 'Get ame transaction type: 005';
3636 IF (g_po_wf_debug = 'Y') THEN
3637 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3638 END IF;
3639
3640 IF l_document_type = 'REQUISITION' THEN
3641
3642 BEGIN
3643
3644 SELECT ame_transaction_type
3645 INTO l_ame_Transaction_Type
3646 FROM po_document_types
3647 WHERE document_type_code = l_document_type
3648 and document_subtype = l_document_subtype;
3649
3650 EXCEPTION
3651
3652 WHEN OTHERS THEN
3653
3654 l_ame_Transaction_Type := null;
3655
3656 END;
3657
3658 END IF;
3659
3660 l_progress := 'Get ame transaction type: 006 l_ame_Transaction_Type :: '||l_ame_Transaction_Type;
3661 IF (g_po_wf_debug = 'Y') THEN
3662 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3663 END IF;
3664
3665
3666 IF l_ame_Transaction_Type IS NOT NULL THEN
3667
3668 l_progress := 'Ame Clear all approvals call: 007 ';
3669 IF (g_po_wf_debug = 'Y') THEN
3670 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3671 END IF;
3672
3673 ame_api2.clearAllApprovals(applicationIdIn=>201,
3674 transactionIdIn=>l_document_id,
3675 transactionTypeIn=>l_ame_Transaction_Type);
3676
3677 END IF;
3678
3679 l_progress := 'Get_req_preparer_msg_attribute End : 008 ';
3680 IF (g_po_wf_debug = 'Y') THEN
3681 /* DEBUG */ PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_progress);
3682 END IF;
3683
3684 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
3685
3686
3687 EXCEPTION
3688 WHEN OTHERS THEN
3689 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
3690 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
3691 WF_CORE.context('POR_AME_APPROVAL_LIST' , 'Clear_ame_apprv_list_reject', itemtype, itemkey,l_progress);
3692 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm,
3693 'POR_AME_APPROVAL_LIST.CLEAR_AME_APPRV_LIST_REJECT');
3694 raise;
3695
3696 END Clear_ame_apprv_list_reject;
3697
3698 END por_ame_approval_list;
[Home] [Help]