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