[Home] [Help]
PACKAGE BODY: APPS.PO_AME_WF_PVT
Source
1 PACKAGE BODY PO_AME_WF_PVT AS
2 -- $Header: PO_AME_WF_PVT.plb 120.4.12020000.1 2013/02/10 12:21:20 vegajula noship $
3
4 -- Read the profile option that enables/disables the debug log
5 g_po_wf_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('PO_SET_DEBUG_WORKFLOW_ON'),'N');
6 g_next_approvers ame_util.approversTable2;
7 g_production_Indexes ame_util.idList;
8 g_variable_Names ame_util.stringList;
9 g_variable_Values ame_util.stringList;
10 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
11 g_pkg_name CONSTANT VARCHAR2(20) := 'PO_AME_WF_PVT';
12 g_module_prefix CONSTANT VARCHAR2(30) := 'po.plsql.' || g_pkg_name||'.';
13
14 FUNCTION position_has_valid_approvers(
15 documentId NUMBER,
16 documentType VARCHAR2)
17 RETURN VARCHAR2;
18
19 FUNCTION is_last_approver_record(
20 documentId NUMBER,
21 documentType VARCHAR2,
22 approverRecord IN ame_util.approverRecord2 )
23 RETURN VARCHAR2;
24
25 FUNCTION check_set_esigners(
26 itemtype IN VARCHAR2,
27 itemkey IN VARCHAR2 )
28 RETURN VARCHAR2;
29
30 PROCEDURE update_pending_signature (
31 itemtype IN VARCHAR2,
32 itemkey IN VARCHAR2,
33 p_po_header_id IN NUMBER);
34
35 PROCEDURE supress_existing_approvers(
36 itemtype IN VARCHAR2,
37 itemkey IN VARCHAR2);
38
39 PROCEDURE update_auth_status_approve(
40 p_document_id IN NUMBER,
41 p_item_type IN VARCHAR2,
42 p_item_key IN VARCHAR2);
43
44 --------------------------------------------------------------------------------
45 --Start of Comments
46 --Name: InsertActionHistoryPoAme
47 --Pre-reqs:
48 -- None.
49 --Modifies:
50 -- None.
51 --Locks:
52 -- None.
53 --Function:
54 -- This AUTONOMOUS procedure is used to create a new blank action history record
55 --Parameters:
56 --IN:
57 -- p_document_id
58 -- p_draft_id
59 -- p_document_type
60 -- p_document_subtype
61 -- p_revision_num
62 -- p_employee_id
63 -- p_approval_group_id
64 -- p_action
65 --OUT:
66 -- Standard workflow OUT parameters
67 --Testing:
68 --
69 --End of Comments
70 -------------------------------------------------------------------------------
71 PROCEDURE InsertActionHistoryPoAme(
72 p_document_id IN NUMBER,
73 p_draft_id IN VARCHAR2,
74 p_document_type IN VARCHAR2,
75 p_document_subtype IN VARCHAR2,
76 p_revision_num IN NUMBER,
77 p_employee_id IN NUMBER,
78 p_approval_group_id IN NUMBER,
79 p_action IN VARCHAR2,
80 p_note IN VARCHAR2 default null)
81 IS
82 PRAGMA AUTONOMOUS_TRANSACTION;
83 l_sequence_num PO_ACTION_HISTORY.SEQUENCE_NUM%TYPE;
84 BEGIN
85
86 -----------------------------------------------------------------------
87 -- SQL What: Get the document information from the latest action history record.
88 -- SQL Why : Using this, we will fetch the other informaiton required for
89 -- inserting the action history record.
90 -----------------------------------------------------------------------
91 SELECT MAX(sequence_num)
92 INTO l_sequence_num
93 FROM PO_ACTION_HISTORY
94 WHERE object_type_code = p_document_type --'PO'
95 AND object_sub_type_code = p_document_subtype --'STANDARD'
96 AND object_id = p_document_id;
97
98 po_forward_sv1.insert_action_history ( p_document_id,
99 p_document_type,
100 p_document_subtype,
101 l_sequence_num + 1,
102 p_action,
103 sysdate,
104 p_employee_id,
105 NULL,
106 p_note,
107 p_revision_num,
108 NULL,
109 fnd_global.conc_request_id,
110 fnd_global.prog_appl_id,
111 fnd_global.conc_program_id,
112 SYSDATE,
113 fnd_global.user_id,
114 fnd_global.login_id,
115 p_approval_group_id);
116 COMMIT;
117
118 EXCEPTION
119 WHEN OTHERS THEN
120 ROLLBACK;
121 RAISE;
122 END InsertActionHistoryPoAme;
123
124 --------------------------------------------------------------------------------
125 --Start of Comments
126 --Name: UpdateActionHistoryPoAme
127 --Pre-reqs:
128 -- None.
129 --Modifies:
130 -- None.
131 --Locks:
132 -- None.
133 --Function:
134 -- This procedure updates the po_action_history table based on the approvers response.
135 --Parameters:
136 --IN:
137 -- p_document_id
138 -- p_draft_id
139 -- p_document_type
140 -- p_document_subtype
141 -- p_action
142 -- p_note
143 -- p_current_approver
144 --OUT:
145 -- None.
146 --End of Comments
147 -------------------------------------------------------------------------------
148 PROCEDURE UpdateActionHistoryPoAme(
149 p_document_id NUMBER,
150 p_draft_id NUMBER,
151 p_document_type IN VARCHAR2,
152 p_document_subtype IN VARCHAR2,
153 p_action VARCHAR2,
154 p_note VARCHAR2,
155 p_current_approver NUMBER)
156 IS
157 PRAGMA AUTONOMOUS_TRANSACTION;
158
159 BEGIN
160 -----------------------------------------------------------------------
161 -- Update the action history record with NULL action code with the
162 -- appropriate action code.
163 -- Compare the approver id if it is passed in. Else, update the record
164 -- without the validation.
165 -----------------------------------------------------------------------
166 IF (p_current_approver IS NOT NULL) THEN
167
168 UPDATE po_action_history
169 SET action_code = p_action,
170 note = p_note,
171 action_date = sysdate
172 WHERE object_id = p_document_id
173 AND employee_id = p_current_approver
174 AND action_code IS NULL
175 AND object_type_code = p_document_type
176 AND object_sub_type_code = p_document_subtype
177 AND rownum =1;
178
179 ELSE
180
181 UPDATE po_action_history
182 SET action_code = p_action, note = p_note, action_date = sysdate
183 WHERE object_id = p_document_id
184 AND action_code IS NULL
185 AND object_type_code = p_document_type
186 AND object_sub_type_code = p_document_subtype;
187
188 END IF;
189
190 COMMIT;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 ROLLBACK;
195 RAISE;
196 END UpdateActionHistoryPoAme;
197
198 ----------------------------------------------------------------------------------
199 --Start of Comments
200 --Name: get_next_approvers
201 --Pre-reqs:
202 -- None.
203 --Modifies:
204 -- None.
205 --Locks:
206 -- None.
207 --Function:
208 -- Workflow activity PL/SQL handler
209 -- Get the next approver name from the AME approval list
210 -- And update workflow attributes.
211 -- If no next approver is found, approval routing will terminate.
212 --Parameters:
213 --IN:
214 -- Standard workflow IN parameters
215 --OUT:
216 -- Standard workflow OUT parameters
217 --Testing:
218 --
219 --End of Comments
220 -------------------------------------------------------------------------------
221 PROCEDURE get_next_approvers(
222 itemtype IN VARCHAR2,
223 itemkey IN VARCHAR2,
224 actid IN NUMBER,
225 funcmode IN VARCHAR2,
226 resultout OUT NOCOPY VARCHAR2)
227 IS
228 l_document_id NUMBER;
229 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
230 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
231 l_next_approver_id NUMBER;
232 l_next_approver_user_name FND_USER.USER_NAME%TYPE;
233 l_next_approver_disp_name WF_USERS.DISPLAY_NAME%TYPE;
234 l_orig_system WF_USERS.ORIG_SYSTEM%TYPE := ame_util.perOrigSystem;
235 l_sequence_num NUMBER;
236 l_approver_type VARCHAR2(30);
237 l_doc_string VARCHAR2(200);
238 l_preparer_user_name FND_USER.USER_NAME%TYPE;
239 l_org_id NUMBER;
240 l_insertion_type VARCHAR2(30);
241 l_authority_type VARCHAR2(30);
242 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
243 l_completeYNO VARCHAR2(1);
244 l_position_has_valid_approvers VARCHAR2(10);
245 l_need_to_get_next_approver BOOLEAN;
246 l_ame_exception ame_util.longestStringType;
247 l_transaction_id NUMBER;
248 l_next_approver ame_util.approverRecord;
249 xitemIndexesOut ame_util.idList;
250 xitemClassesOut ame_util.stringList;
251 xitemIdsOut ame_util.stringList;
252 xitemSourcesOut ame_util.longStringList;
253 xtransVariableNamesOut ame_util.stringList;
254 xtransVariableValuesOut ame_util.stringList;
255 AME_GET_NEXT_APPRVR_EXCEPTION EXCEPTION;
256 l_progress VARCHAR2(3) := '000';
257 l_api_name VARCHAR2(500) := 'get_next_approvers';
258 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
259 l_esigner_flag VARCHAR2(1);
260 l_esigner_exists VARCHAR2(1);
261
262 BEGIN
263
264 IF (funcmode <> wf_engine.eng_run) THEN
265 resultout := wf_engine.eng_null;
266 RETURN;
267 END IF;
268
269 --Set the global attributes in the po wrapper function
270 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
271 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
272
273 l_progress := '010';
274 IF (g_po_wf_debug = 'Y') THEN
275 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
276 END IF;
277
278 -- Logic :
279 -- + Fetch all workflow related attributes.
280 -- + In loop fetch next set of approvers. Using AME_API2.getNextApprovers3(..) will provide
281 -- set of production rules (name/value pairs) being applied to currents set of approvers.
282 -- + Check whether returned set of approvers have valid position or not by giving call to
283 -- function position_has_valid_approvers or not.
284 -- + When count of approvers reach to zero, check whether worklfow routing process is completed or
285 -- not through OUT varaible l_completeYNO in AME_API2.getNextApprovers3(..) */
286
287 -- Check if there is any AME exception. If yes, then return 'invalid approver'
288 l_ame_exception := po_wf_util_pkg.GetItemAttrText( aname => 'AME_EXCEPTION');
289
290 IF l_ame_exception IS NOT NULL THEN
291 resultout := wf_engine.eng_completed||':'||'INVALID_APPROVER';
292 RETURN;
293 END IF;
294
295 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
296 l_document_subtype := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_SUBTYPE');
297 l_transaction_type := po_wf_util_pkg.GetItemAttrText( aname => 'AME_TRANSACTION_TYPE');
298 l_transaction_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'AME_TRANSACTION_ID');
299 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
300 l_esigner_exists := po_wf_util_pkg.GetItemAttrText( aname => 'E_SIGNER_EXISTS');
301
302 l_progress := '020';
303
304 -- Get the next approver from AME.
305 LOOP
306 l_need_to_get_next_approver := FALSE;
307 BEGIN
308 l_progress := '030';
309 IF (g_po_wf_debug = 'Y') THEN
310 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Calling AME API with transaction id ' || l_transaction_id);
311 END IF;
312
313 ame_util2.detailedApprovalStatusFlagYN := ame_util.booleanTrue;
314 AME_API2.getNextApprovers3 (
315 applicationIdIn => applicationId,
316 transactionTypeIn => l_transaction_type,
317 transactionIdIn => l_transaction_id,
318 flagApproversAsNotifiedIn => ame_util.booleanTrue,
319 approvalProcessCompleteYNOut => l_completeYNO,
320 nextApproversOut => g_next_approvers,
321 itemIndexesOut => xitemIndexesOut,
322 itemClassesOut => xitemClassesOut,
323 itemIdsOut => xitemIdsOut,
324 itemSourcesOut => xitemSourcesOut,
325 productionIndexesOut => g_production_Indexes,
326 variableNamesOut => g_variable_Names,
327 variableValuesOut => g_variable_Values,
328 transVariableNamesOut => xtransVariableNamesOut,
329 transVariableValuesOut => xtransVariableValuesOut);
330
331 EXCEPTION
332 WHEN OTHERS THEN
333 RAISE;
334 END;
335 l_progress := '040';
336 IF (g_po_wf_debug = 'Y') THEN
337 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||'.'||l_progress||':'||
338 ' g_next_approvers.count:'||g_next_approvers.count||
339 ' l_completeYNO:'||l_completeYNO);
340 END IF;
341
342 IF ( g_next_approvers.count > 0 ) THEN
343
344 IF (g_po_wf_debug = 'Y') THEN
345 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' BEFORE ESIGNER EXISTS');
346 END IF;
347
348 -- Check whether approver set is of signers or not. If yes, set the attribute and exit
349 IF(l_esigner_exists = 'N') THEN
350 l_esigner_flag := check_set_esigners(itemtype, itemkey);
351 IF l_esigner_flag = 'Y' then
352 IF (g_po_wf_debug = 'Y') THEN
353 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' BEFORE update_pending_signature');
354 END IF;
355 update_pending_signature(itemtype, itemkey, l_document_id);
356 resultout:= wf_engine.eng_completed||':'||'VALID_ESIGNER';
357 RETURN;
358 END IF; -- l_esigner_flag = 'Y'
359 END IF; -- l_esigner_exists = 'N'
360
361 l_position_has_valid_approvers := position_has_valid_approvers(l_transaction_id, l_transaction_type);
362 IF (g_po_wf_debug = 'Y') THEN
363 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||'.'||l_progress||':'||
364 ' l_position_has_valid_approvers:'||l_position_has_valid_approvers||
365 ' g_next_approvers.count:'||g_next_approvers.count);
366 END IF;
367
368 IF( g_next_approvers.count = 0 AND 'NO_USERS' = l_position_has_valid_approvers ) THEN
369 l_need_to_get_next_approver := TRUE;
370 END IF;
371 END IF; --g_next_approvers.count IF
372 EXIT WHEN l_need_to_get_next_approver = FALSE;
373 END LOOP; -- Get the next approver from AME
374
375 -- Check the number of next approvers.
376 -- If the count is greater than zero then verify whether position
377 -- has valid approvers or not. Return INVALID_APPROVER and
378 -- VALID_APPROVER depeding upon same.
379 -- If the count is zero then verify the approval process is completed or not.
380
381 IF ( g_next_approvers.count > 0 ) THEN
382 IF( 'N' = l_position_has_valid_approvers ) THEN
383 resultout := wf_engine.eng_completed||':'||'INVALID_APPROVER';
384 ELSE
385 resultout:= wf_engine.eng_completed||':'||'VALID_APPROVER';
386 END IF; -- l_position_has_valid_approvers IF
387 ELSE
388 IF (l_completeYNO IN ('X','Y')) THEN
389 -- Check whether if signer existed. If yes, we need to end with Signer Complete process.
390 -- Else on normal approval process.
391 IF (l_esigner_exists = 'N') THEN
392 resultout := wf_engine.eng_completed||':'||'NO_NEXT_APPROVER';
393 ELSE
394 resultout := wf_engine.eng_completed||':'||'NO_NEXT_APPROVER_ESIGNER';
395 END IF;
396 ELSE
397 resultout:= wf_engine.eng_completed||':'||'';
398 END IF; -- l_completeYNO IF
399 END IF; --g_next_approvers.count > 0 IF
400
401 RETURN;
402 EXCEPTION
403 WHEN OTHERS THEN
404 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
405 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
406 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
407 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, l_progress, l_document_id);
408 IF (g_po_wf_debug = 'Y') THEN
409 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
410 END IF;
411 resultout:= wf_engine.eng_completed||':'||'INVALID_APPROVER';
412 RETURN;
413 END get_next_approvers;
414
415 --------------------------------------------------------------------------------
416 --Start of Comments
417 --Name: is_ame_exception
418 --Pre-reqs:
419 -- None.
420 --Modifies:
421 -- None.
422 --Locks:
423 -- None.
424 --Function:
425 -- Workflow activity PL/SQL handler.
426 -- It checks if the AME_EXCEPTION attribute is NULL or not.
427 -- If not NULL, it means there have been some AME exception encountered,
428 -- and it returns 'Y'.
429 -- Else it will return 'N'
430 --Parameters:
431 --IN:
432 -- Standard workflow IN parameters
433 --OUT:
434 -- Standard workflow OUT parameters
435 --Testing:
436 --
437 --End of Comments
438 -------------------------------------------------------------------------------
439 PROCEDURE is_ame_exception(
440 itemtype IN VARCHAR2,
441 itemkey IN VARCHAR2,
442 actid IN NUMBER,
443 funcmode IN VARCHAR2,
444 resultout OUT NOCOPY VARCHAR2)
445 IS
446 l_ame_exception ame_util.longestStringType;
447 l_progress VARCHAR2(3) := '000';
448 l_doc_string VARCHAR2(200);
449 l_api_name VARCHAR2(500) := 'is_ame_exception';
450 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
451 BEGIN
452
453 IF (funcmode <> wf_engine.eng_run) THEN
454 resultout := wf_engine.eng_null;
455 RETURN;
456 END IF;
457
458 --Set the global attributes in the po wrapper function
459 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
460 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
461
462 l_progress := '010';
463 l_ame_exception :=PO_WF_UTIL_PKG.GetItemAttrText (aname => 'AME_EXCEPTION');
464
465 IF l_ame_exception IS NOT NULL THEN
466 resultout := wf_engine.eng_completed || ':' ||'Y';
467 ELSE
468 resultout := wf_engine.eng_completed || ':' ||'N';
469 END IF;
470
471 EXCEPTION
472 WHEN OTHERS THEN
473 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
474 wf_core.context(g_pkg_name, l_api_name, 'Unexpected Exception:', l_progress, SQLERRM);
475 IF (g_po_wf_debug = 'Y') THEN
476 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_log_head||':'||l_progress||':'||SQLERRM);
477 END IF;
478 RAISE;
479 END is_ame_exception;
480
481 --------------------------------------------------------------------------------
482 --Start of Comments
483 --Name: position_has_valid_approvers
484 --Pre-reqs:
485 -- None.
486 --Modifies:
487 -- None.
488 --Locks:
489 -- None.
490 --Function:
491 -- This function is used to check whether to launch the parallel approval process or not.
492 -- If a position does not have any users, then this function will return 'N', otherwise return 'Y'
493 --Parameters:
494 --IN:
495 -- documentId : AME transaction id
496 -- documentType : AME Transaction Type
497 --OUT:
498 -- 'Y' We can launch the parallel approval process.
499 -- 'N' Invalid approver. We can not launch the parallel approval process.
500 -- 'NO_USERS' No users for position. This AME record will be deleted. Go to the next approver record.
501 --Testing:
502 --
503 --End of Comments
504 -------------------------------------------------------------------------------
505 FUNCTION position_has_valid_approvers(
506 documentId NUMBER,
507 documentType VARCHAR2)
508 RETURN VARCHAR2
509 IS
510 l_next_approver_id NUMBER;
511 l_next_approver_name per_employees_current_x.full_name%TYPE;
512 l_position_has_valid_approvers VARCHAR2(10);
513 l_approver_index NUMBER;
514 l_first_approver_id NUMBER := NULL;
515 l_first_position_id NUMBER := NULL;
516 BEGIN
517 l_position_has_valid_approvers := 'Y';
518 l_approver_index := g_next_approvers.first();
519
520 WHILE ( l_approver_index IS NOT NULL ) LOOP
521
522 l_position_has_valid_approvers := 'Y';
523 IF (g_next_approvers(l_approver_index).orig_system = ame_util.posOrigSystem) THEN
524
525 BEGIN
526 -----------------------------------------------------------------------
527 -- SQL What: Get the person assigned to position returned by AME.
528 -- SQL Why : When AME returns position id, then using this sql we find
529 -- one person assigned to this position and use this person
530 -- as approver.
531 -----------------------------------------------------------------------
532 SELECT person_id, full_name
533 INTO l_next_approver_id, l_next_approver_name
534 FROM ( SELECT person.person_id, person.full_name
535 FROM per_all_people_f person,
536 per_all_assignments_f asg,
537 wf_users wu
538 WHERE asg.position_id = g_next_approvers(l_approver_index).orig_system_id
539 AND wu.orig_system = ame_util.perorigsystem
540 AND wu.orig_system_id = person.person_id
541 AND TRUNC(SYSDATE) BETWEEN person.effective_start_date AND NVL(person.effective_end_date, TRUNC( SYSDATE))
542 AND person.person_id = asg.person_id
543 AND asg.primary_flag = 'Y'
544 AND asg.assignment_type IN ( 'E', 'C' )
545 AND ( person.current_employee_flag = 'Y' OR person.current_npw_flag = 'Y' )
546 AND asg.assignment_status_type_id NOT IN
547 ( SELECT assignment_status_type_id
548 FROM per_assignment_status_types
549 WHERE per_system_status = 'TERM_ASSIGN' )
550 AND TRUNC(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
551 ORDER BY person.last_name)
552 WHERE ROWNUM = 1;
553
554 EXCEPTION
555 WHEN NO_DATA_FOUND THEN
556 -- No users for this position. Check whether this is last position or not.
557 -- If this is last position then return 'N'.
558 IF (is_last_approver_record(documentId, documentType, g_next_approvers(l_approver_index)) = 'Y') THEN
559 RETURN 'N';
560 ELSE
561 -- As this is a blank record, remove it in AME and the global variable.
562 -- Return 'NO_USERS'. We use PO_SYS_GENERATED_APPROVERS_SUPPRESS dynamic profile to
563 -- override AME mandatory attribute ALLOW_DELETING_RULE_GENERATED_APPROVERS.
564 fnd_profile.put('PO_SYS_GENERATED_APPROVERS_SUPPRESS', 'Y');
565 ame_api3.suppressApprover( applicationIdIn => applicationId,
566 transactionIdIn => documentId,
567 approverIn => g_next_approvers(l_approver_index),
568 transactionTypeIn => documentType );
569 fnd_profile.put('PO_SYS_GENERATED_APPROVERS_SUPPRESS', 'Y');
570 g_next_approvers.delete(l_approver_index);
571 l_position_has_valid_approvers := 'NO_USERS';
572 END IF; -- is_last_approver_record IF
573 END;
574 END IF; --g_next_approvers(l_approver_index).orig_system = ame_util.posOrigSystem
575
576 l_approver_index := g_next_approvers.next(l_approver_index);
577 END LOOP; -- l_approver_index NOT NULL LOOP
578
579 RETURN l_position_has_valid_approvers;
580
581 EXCEPTION
582 WHEN OTHERS THEN
583 RETURN 'N';
584 END position_has_valid_approvers;
585
586 --------------------------------------------------------------------------------
587 --Start of Comments
588 --Name: is_last_approver_record
589 --Pre-reqs:
590 -- None.
591 --Modifies:
592 -- None.
593 --Locks:
594 -- None.
595 --Function:
596 -- This function is used to check whether the approver/position is last in the approval chain or not
597 -- This function will be invoked only if a particular position does not have any associated users.
598 -- If this function returns 'Y', then position_has_valid_approvers reruns 'N'
599 --Parameters:
600 --IN:
601 -- documentId : ReqHeaderId
602 -- documentType : AME Transaction Type
603 -- approverRecord : Current approver record
604 --OUT:
605 -- 'Y' The approver/position is last in the approval chain.
606 -- 'N' The approver/position is not last in the approval chain
607 --Testing:
608 -- None.
609 --End of Comments
610 -------------------------------------------------------------------------------
611 FUNCTION is_last_approver_record(
612 documentId NUMBER,
613 documentType VARCHAR2,
614 approverRecord IN ame_util.approverRecord2 )
615 RETURN VARCHAR2
616 IS
617 l_is_last_approver_record VARCHAR2(1);
618 l_total_approver_count NUMBER;
619 l_current_approver_index NUMBER;
620 tmpApproverList ame_util.approversTable2;
621 l_process_out VARCHAR2(10);
622
623 BEGIN
624 ame_api2.getAllApprovers7 ( applicationIdIn => applicationId,
625 transactionIdIn => documentId,
626 transactionTypeIn => documentType,
627 approvalProcessCompleteYNOut => l_process_out,
628 approversOut => tmpApproverList );
629
630 l_total_approver_count := tmpApproverList.count;
631 l_current_approver_index := 0;
632
633 FOR i IN 1..tmpApproverList.count LOOP
634 l_current_approver_index := i;
635 IF ( tmpApproverList(i).name = approverRecord.name
636 AND tmpApproverList(i).orig_system = approverRecord.orig_system
637 AND tmpApproverList(i).orig_system_id = approverRecord.orig_system_id
638 AND tmpApproverList(i).authority = approverRecord.authority
639 AND tmpApproverList(i).group_or_chain_id = approverRecord.group_or_chain_id
640 AND tmpApproverList(i).action_type_id = approverRecord.action_type_id
641 AND tmpApproverList(i).item_id = approverRecord.item_id
642 AND tmpApproverList(i).item_class = approverRecord.item_class
643 AND tmpApproverList(i).approver_category = approverRecord.approver_category ) THEN
644 EXIT;
645 END IF;
646 END LOOP;
647
648 IF( l_current_approver_index = l_total_approver_count ) THEN
649 RETURN 'Y';
650 ELSE
651 RETURN 'N';
652 END IF;
653
654 EXCEPTION
655 WHEN OTHERS THEN
656 RETURN 'Y';
657 END is_last_approver_record;
658
659 --------------------------------------------------------------------------------
660 --Start of Comments
661 --Name: launch_parallel_approval
662 --Pre-reqs:
663 -- None.
664 --Modifies:
665 -- None.
666 --Locks:
667 -- None.
668 --Function:
669 -- Workflow activity PL/SQL handler.
670 -- + This procedure is used to send the notification for the approvers.
671 -- + Iterate through the list of approvers got from the API call
672 -- ame_api2.getNextApprovers3.
673 -- + Get the next approver name from the global variable g_next_approvers
674 -- and for each retrieved approver separate workflow process is kicked.
675 -- + They are marked as child of the current approval process (workflow
676 -- master detail co-ordination).
677 -- + For example, if there are 3 approvers, then 3 child process will be
678 -- created and each of them will be notified at the same time.
679 -- + If the next approver record is of Position Hierarchy type, then the
680 -- users associated to the position_id will be retrieved, will be
681 -- alphabetically sorted using last_name and to the first user
682 -- notification will be sent.
683 -- + To separate out APPROVER, REVIEWERS and SIGINERS added code to check
684 -- production rules (name/value pairs)and set approver_category
685 -- workflow attribute accordingly.
686 --
687 --Parameters:
688 --IN:
689 -- Standard workflow IN parameters
690 --OUT:
691 -- Standard workflow OUT parameters
692 --Testing:
693 --
694 --End of Comments
695 -------------------------------------------------------------------------------
696
697 PROCEDURE launch_parallel_approval(
698 itemtype IN VARCHAR2,
699 itemkey IN VARCHAR2,
700 actid IN NUMBER,
701 funcmode IN VARCHAR2,
702 resultout OUT NOCOPY VARCHAR2)
703 IS
704 l_progress VARCHAR2(3) DEFAULT '000';
705 l_document_id NUMBER;
706 l_item_key wf_items.item_key%TYPE;
707 l_next_approver_id NUMBER;
708 l_next_approver_name per_employees_current_x.full_name%TYPE;
709 l_next_approver_user_name VARCHAR2(100);
710 l_next_approver_disp_name VARCHAR2(240);
711 l_orig_system VARCHAR2(48);
712 l_org_id NUMBER;
713 l_functional_currency VARCHAR2(30);
714 l_transaction_type po_document_types.ame_transaction_type%TYPE;
715 n_varname wf_engine.nametabtyp;
716 n_varval wf_engine.numtabtyp;
717 t_po_varname wf_engine.nametabtyp;
718 t_po_varval wf_engine.texttabtyp;
719 l_no_positionholder EXCEPTION;
720 l_preparer_user_name fnd_user.user_name%TYPE;
721 l_doc_string VARCHAR2(200);
722 l_start_block_activity VARCHAR2(1);
723 l_has_fyi_app VARCHAR2(1);
724 l_approver_index NUMBER;
725 l_first_position_id NUMBER DEFAULT NULL;
726 l_first_approver_id NUMBER DEFAULT NULL;
727 l_ame_transaction_id NUMBER;
728 l_document_type po_document_types.document_type_code%TYPE;
729 l_esigner_exists VARCHAR2(1);
730 l_api_name VARCHAR2(500) := 'launch_parallel_approval';
731 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
732 l_owner_user_name fnd_user.user_name%TYPE;
733 l_userkey PO_HEADERS_ALL.SEGMENT1%TYPE;
734
735 BEGIN
736
737 IF (funcmode <> wf_engine.eng_run) THEN
738 resultout := wf_engine.eng_null;
739 RETURN;
740 END IF;
741
742 --Set the global attributes in the po wrapper function
743 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
744 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
745
746 l_progress := '010';
747 IF (g_po_wf_debug = 'Y') THEN
748 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
749 END IF;
750
751 --setting the bypass flag to N if document is getting forwarded
752 po_wf_util_pkg.SetItemAttrText(aname => 'BYPASS_CHECKS_FLAG', avalue => 'N');
753
754 --Fetch workflow attributes
755 l_org_id := po_wf_util_pkg.GetItemAttrNumber(aname => 'ORG_ID');
756 l_document_type := po_wf_util_pkg.GetItemAttrText (aname => 'DOCUMENT_TYPE');
757 l_document_id := po_wf_util_pkg.GetItemAttrNumber (aname => 'DOCUMENT_ID');
758 l_transaction_type := po_wf_util_pkg.GetItemAttrText (aname => 'AME_TRANSACTION_TYPE');
759 l_ame_transaction_id := po_wf_util_pkg.GetItemAttrNumber (aname => 'AME_TRANSACTION_ID');
760 l_esigner_exists := po_wf_util_pkg.GetItemAttrText (aname => 'E_SIGNER_EXISTS');
761
762 l_start_block_activity := 'N';
763 l_has_fyi_app := 'N';
764 l_approver_index := g_next_approvers.first;
765
766 --Loop through current set of approvers until l_approver_index is not null
767 WHILE (l_approver_index IS NOT NULL) LOOP
768 l_progress := '020';
769 IF (g_po_wf_debug = 'Y') THEN
770 PO_WF_DEBUG_PKG.insert_debug (itemtype, itemkey, l_log_head||':'||l_progress
771 ||': g_next_approvers.name'
772 ||g_next_approvers(l_approver_index).name);
773 END IF;
774
775 --Fetch new item key
776 SELECT TO_CHAR (l_document_id)||'-'||TO_CHAR (po_wf_itemkey_s.nextval)
777 INTO l_item_key
778 FROM sys.dual;
779
780 --Create the parallel process
781 wf_engine.CreateProcess ( itemtype => itemtype,
782 itemkey => l_item_key,
783 process => 'PARALLEL_APPROVAL_PROCESS');
784 --Set parent attributes
785 wf_engine.SetItemParent ( itemtype => itemtype,
786 itemkey => l_item_key,
787 parent_itemtype => itemtype,
788 parent_itemkey => itemkey,
789 parent_context => NULL );
790
791 --In array t_po_varname and t_po_varval, set all required workflow attributes
792 t_po_varname (1) := 'DOCUMENT_TYPE';
793 t_po_varval (1) := po_wf_util_pkg.GetItemAttrText (aname => 'DOCUMENT_TYPE');
794 t_po_varname (2) := 'DOCUMENT_SUBTYPE';
795 t_po_varval (2) := po_wf_util_pkg.GetItemAttrText (aname => 'DOCUMENT_SUBTYPE');
796 t_po_varname (3) := 'PREPARER_USER_NAME';
797 t_po_varval (3) := po_wf_util_pkg.GetItemAttrText (aname => 'PREPARER_USER_NAME');
798 t_po_varname (4) := 'PREPARER_DISPLAY_NAME';
799 t_po_varval (4) := po_wf_util_pkg.GetItemAttrText (aname => 'PREPARER_DISPLAY_NAME');
800 t_po_varname (5) := 'FUNCTIONAL_CURRENCY';
801 t_po_varval (5) := po_wf_util_pkg.GetItemAttrText (aname => 'FUNCTIONAL_CURRENCY');
802 t_po_varname (6) := 'TOTAL_AMOUNT_DSP';
803 t_po_varval (6) := po_wf_util_pkg.GetItemAttrText (aname => 'TOTAL_AMOUNT_DSP');
804 t_po_varname (7) := 'FORWARD_FROM_DISP_NAME';
805 t_po_varval (7) := po_wf_util_pkg.GetItemAttrText (aname => 'FORWARD_FROM_DISP_NAME');
806 t_po_varname (8) := 'FORWARD_FROM_USER_NAME';
807 t_po_varval (8) := po_wf_util_pkg.GetItemAttrText (aname => 'FORWARD_FROM_USER_NAME');
808 t_po_varname (9) := 'DOCUMENT_NUMBER';
809 t_po_varval (9) := po_wf_util_pkg.GetItemAttrText (aname => 'DOCUMENT_NUMBER');
810 l_userkey := t_po_varval (9);
811 t_po_varname (10) := 'AME_TRANSACTION_TYPE';
812 t_po_varval (10) := po_wf_util_pkg.GetItemAttrText (aname => 'AME_TRANSACTION_TYPE');
813 t_po_varname (11) := 'OPEN_FORM_COMMAND';
814 t_po_varval (11) := po_wf_util_pkg.GetItemAttrText (aname => 'OPEN_FORM_COMMAND');
815 t_po_varname (12) := 'PO_DESCRIPTION';
816 t_po_varval (12) := po_wf_util_pkg.GetItemAttrText (aname => 'PO_DESCRIPTION');
817 t_po_varname (13) := 'PO_AMOUNT_DSP';
818 t_po_varval (13) := po_wf_util_pkg.GetItemAttrText (aname => 'PO_AMOUNT_DSP');
819
820 t_po_varname (14) := 'VIEW_DOC_URL';
821 t_po_varval (14) := po_wf_util_pkg.GetItemAttrText (aname => 'VIEW_DOC_URL');
822 IF (t_po_varval (14) IS NOT NULL) THEN
823 t_po_varval (14) := t_po_varval (14) || '&' || 'item_key=' || l_item_key;
824 END IF;
825
826 t_po_varname (15) := 'EDIT_DOC_URL';
827 t_po_varval (15) := po_wf_util_pkg.GetItemAttrText (aname => 'EDIT_DOC_URL');
828
829 IF (t_po_varval (15) IS NOT NULL) THEN
830 t_po_varval (15) := t_po_varval (15) || '&' || 'item_key=' || l_item_key;
831 END IF;
832
833 l_progress := '030';
834 IF (g_po_wf_debug = 'Y') THEN
835 PO_WF_DEBUG_PKG.insert_debug (itemtype, itemkey, l_log_head||':'||l_progress
836 ||' g_next_approvers.orig_system:'
837 ||g_next_approvers (l_approver_index).orig_system);
838 END IF;
839
840 --Fetch next approver_id from the global list.
841 -- If the approver is a PER role then use the same person id.
842 -- If the approver is POS role, then find out the first user corresponding to that person.
843 -- If it is an FND USER pick the employee_id corresponding to that FND USER
844 IF (g_next_approvers (l_approver_index).orig_system = ame_util.perorigsystem) THEN
845
846 l_next_approver_id := g_next_approvers (l_approver_index).orig_system_id;
847
848 ELSIF (g_next_approvers (l_approver_index).orig_system = ame_util.posorigsystem) THEN
849
850 BEGIN
851 -----------------------------------------------------------------------
852 -- SQL What: Get the person assigned to position returned by AME.
853 -- SQL Why : When AME returns position id, then using this sql we find
854 -- one person assigned to this position and use this person
855 -- as approver.
856 -----------------------------------------------------------------------
857 SELECT person_id , full_name
858 INTO l_next_approver_id, l_next_approver_name
859 FROM ( SELECT person.person_id , person.full_name
860 FROM per_all_people_f person ,
861 per_all_assignments_f asg,
862 wf_users wu
863 WHERE asg.position_id = g_next_approvers (l_approver_index).orig_system_id
864 AND wu.orig_system = ame_util.perorigsystem
865 AND wu.orig_system_id = person.person_id
866 AND TRUNC (sysdate) BETWEEN person.effective_start_date AND NVL (person.effective_end_date ,TRUNC (sysdate))
867 AND person.person_id = asg.person_id
868 AND asg.primary_flag = 'Y'
869 AND asg.assignment_type IN ('E','C')
870 AND ( person.current_employee_flag = 'Y' OR person.current_npw_flag = 'Y' )
871 AND asg.assignment_status_type_id NOT IN
872 ( SELECT assignment_status_type_id
873 FROM per_assignment_status_types
874 WHERE per_system_status = 'TERM_ASSIGN'
875 )
876 AND TRUNC (sysdate) BETWEEN asg.effective_start_date AND asg.effective_end_date
877 ORDER BY person.last_name )
878 WHERE ROWNUM = 1;
879 EXCEPTION
880 WHEN no_data_found THEN
881 RAISE;
882 END;
883
884 ELSIF (g_next_approvers (l_approver_index).orig_system = ame_util.fnduserorigsystem) THEN
885
886 SELECT employee_id
887 INTO l_next_approver_id
888 FROM fnd_user
889 WHERE user_id = g_next_approvers (l_approver_index).orig_system_id
890 AND TRUNC (sysdate) BETWEEN start_date AND NVL (end_date ,sysdate + 1);
891
892 END IF;
893
894 l_progress := '040';
895
896 t_po_varname (16) := 'AME_APPROVER_TYPE';
897 t_po_varval (16) := g_next_approvers (l_approver_index).orig_system;
898
899 wf_directory.getusername (ame_util.perorigsystem, l_next_approver_id, l_next_approver_user_name, l_next_approver_disp_name);
900
901 l_progress := '050';
902
903 IF (g_po_wf_debug = 'Y') THEN
904 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_next_approver_user_name:'||l_next_approver_user_name);
905 END IF;
906
907 IF (g_next_approvers (l_approver_index).orig_system = ame_util.perorigsystem) THEN
908 t_po_varname (17) := 'APPROVER_USER_NAME';
909 t_po_varval (17) := g_next_approvers (l_approver_index).name;
910 t_po_varname (18) := 'APPROVER_DISPLAY_NAME';
911 t_po_varval (18) := g_next_approvers (l_approver_index).display_name;
912 ELSE
913 t_po_varname (17) := 'APPROVER_USER_NAME';
914 t_po_varval (17) := l_next_approver_user_name;
915 t_po_varname (18) := 'APPROVER_DISPLAY_NAME';
916 t_po_varval (18) := l_next_approver_disp_name;
917 END IF;
918
919 -- set owner username
920 l_owner_user_name := t_po_varval (17);
921
922 t_po_varname (19) := 'IS_FYI_APPROVER';
923 IF (g_next_approvers (l_approver_index).approver_category = ame_util.fyiapprovercategory) THEN
924 t_po_varval (19) := 'Y';
925 l_has_fyi_app := 'Y';
926 l_start_block_activity := 'N';
927 ELSE
928 t_po_varval (19) := 'N';
929 IF (l_has_fyi_app = 'N') THEN
930 l_start_block_activity := 'Y';
931 END IF;
932 END IF;
933
934 t_po_varname (20) := 'DOCUMENT_TYPE_DISP';
935 t_po_varval (20) := po_wf_util_pkg.GetItemAttrText (aname => 'DOCUMENT_TYPE_DISP');
936 t_po_varname (21) := 'REQUIRES_APPROVAL_MSG';
937 t_po_varval (21) := po_wf_util_pkg.GetItemAttrText (aname => 'REQUIRES_APPROVAL_MSG');
938 t_po_varname (22) := 'WRONG_FORWARD_TO_MSG';
939 t_po_varval (22) := po_wf_util_pkg.GetItemAttrText (aname => 'WRONG_FORWARD_TO_MSG');
940 t_po_varname (23) := 'OPERATING_UNIT_NAME';
941 t_po_varval (23) := po_wf_util_pkg.GetItemAttrText (aname => 'OPERATING_UNIT_NAME');
942 t_po_varname (24) := 'NOTE';
943 t_po_varval (24) := po_wf_util_pkg.GetItemAttrText (aname => 'NOTE');
944 t_po_varname (25) := 'PO_LINES_DETAILS';
945 t_po_varval (25) := po_wf_util_pkg.GetItemAttrText (aname => 'PO_LINES_DETAILS');
946 t_po_varname (26) := 'DOCUMENT_SUBTYPE_DISP';
947 t_po_varval (26) := po_wf_util_pkg.GetItemAttrText (aname => 'DOCUMENT_SUBTYPE_DISP');
948 t_po_varname (27) := 'ACTION_HISTORY';
949 t_po_varval (27) := po_wf_util_pkg.GetItemAttrText (aname => 'ACTION_HISTORY');
950 t_po_varname (28) := 'PO_APPROVE_MSG';
951 t_po_varval (28) := po_wf_util_pkg.GetItemAttrText (aname => 'PO_APPROVE_MSG');
952 t_po_varname (29) := 'SUPPLIER';
953 t_po_varval (29) := po_wf_util_pkg.GetItemAttrText (aname => 'SUPPLIER');
954 t_po_varname (30) := 'SUPPLIER_SITE';
955 t_po_varval (30) := po_wf_util_pkg.GetItemAttrText (aname => 'SUPPLIER_SITE');
956 t_po_varname (31) := 'AUTHORIZATION_STATUS';
957 t_po_varval (31) := po_wf_util_pkg.GetItemAttrText (aname => 'AUTHORIZATION_STATUS');
958 t_po_varname (32) := 'WITH_TERMS';
959 t_po_varval (32) := po_wf_util_pkg.GetItemAttrText (aname => 'WITH_TERMS');
960 t_po_varname (33) := 'LANGUAGE_CODE';
961 t_po_varval (33) := po_wf_util_pkg.GetItemAttrText (aname => 'LANGUAGE_CODE');
962
963 l_progress := '060';
964 --Adding code for setting attribute approver_category for on the basis of production rule.
965 IF (g_po_wf_debug = 'Y') THEN
966 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' g_production_Indexes.Count:'||g_production_Indexes.Count);
967 END IF;
968
969 --Determine the approver category
970 t_po_varname (34) := 'APPROVER_CATEGORY';
971 IF (g_production_Indexes.Count > 0) THEN
972 FOR j IN 1..g_production_Indexes.Count LOOP
973
974 IF (g_po_wf_debug = 'Y') THEN
975 PO_WF_DEBUG_PKG.insert_debug (itemtype, itemkey, l_log_head||':'||l_progress||' j:' || j);
976 PO_WF_DEBUG_PKG.insert_debug (itemtype, itemkey, l_log_head||':'||l_progress||' g_production_Indexes(j):' || g_production_Indexes(j));
977 PO_WF_DEBUG_PKG.insert_debug (itemtype, itemkey, l_log_head||':'||l_progress||' l_approver_index:' || l_approver_index);
978 PO_WF_DEBUG_PKG.insert_debug (itemtype, itemkey, l_log_head||':'||l_progress||' g_variable_Names(j):' || g_variable_Names(j));
979 PO_WF_DEBUG_PKG.insert_debug (itemtype, itemkey, l_log_head||':'||l_progress||' g_variable_Values(j):' || g_variable_Values(j));
980 END IF;
981
982 IF g_production_Indexes(j) = l_approver_index THEN
983 IF g_variable_Names(j) = 'REVIEWER' AND g_variable_Values(j)= 'YES' THEN
984 t_po_varval (34) := 'REVIEWER';
985 END IF;
986 END IF;
987 END LOOP; -- end of for loop for production rules
988 ELSIF l_esigner_exists = 'Y' THEN
989 PO_WF_DEBUG_PKG.insert_debug (itemtype, itemkey, l_log_head||':'||l_progress||' Esigner exists (into post approval grp)');
990 t_po_varval (34) := 'ESIGNER';
991 ELSE --g_production_Indexes.Count < 0
992 PO_WF_DEBUG_PKG.insert_debug (itemtype, itemkey, l_log_head||':'||l_progress||' No Production Rules');
993 t_po_varval (34) := 'APPROVER';
994 END IF;
995
996 l_progress := '070';
997 t_po_varname (35) := 'BUYER_USER_NAME';
998 t_po_varval (35) := po_wf_util_pkg.GetItemAttrText (aname => 'BUYER_USER_NAME');
999 t_po_varname (36) := 'NOTIFICATION_REGION';
1000 t_po_varval (36) := po_wf_util_pkg.GetItemAttrText (aname => 'NOTIFICATION_REGION');
1001 t_po_varname (37) := 'REQUIRES_REVIEW_MSG';
1002 t_po_varval (37) := po_wf_util_pkg.GetItemAttrText (aname => 'REQUIRES_REVIEW_MSG');
1003 t_po_varname (38) := 'PDF_ATTACHMENT_BUYER';
1004 t_po_varval (38) := po_wf_util_pkg.GetItemAttrText (aname => 'PDF_ATTACHMENT_BUYER');
1005 t_po_varname (39) := 'PO_PDF_ERROR';
1006 t_po_varval (39) := po_wf_util_pkg.GetItemAttrText (aname => 'PO_PDF_ERROR');
1007 t_po_varname (40) := '#HISTORY';
1008 t_po_varval (40) := po_wf_util_pkg.GetItemAttrText (aname => '#HISTORY');
1009 t_po_varname (41) := 'REQUIRES_ESIGN_MSG';
1010 t_po_varval (41) := po_wf_util_pkg.GetItemAttrText (aname => 'REQUIRES_ESIGN_MSG');
1011
1012 --Set the item attributes from the array
1013 l_progress := '080';
1014 wf_engine.SetItemAttrTextarray (itemtype, l_item_key, t_po_varname, t_po_varval);
1015
1016 l_progress := '090';
1017 n_varname (1) := 'DOCUMENT_ID';
1018 n_varval (1) := l_document_id;
1019 n_varname (2) := 'ORG_ID';
1020 n_varval (2) := l_org_id;
1021 n_varname (3) := 'AME_APPROVER_ID';
1022 n_varval (3) := g_next_approvers (l_approver_index).orig_system_id;
1023 n_varname (4) := 'APPROVER_EMPID';
1024 n_varval (4) := l_next_approver_id;
1025 n_varname (5) := 'APPROVAL_GROUP_ID';
1026
1027 IF (g_po_wf_debug = 'Y') THEN
1028 PO_WF_DEBUG_PKG.insert_debug ( itemtype, itemkey, l_log_head||':'||l_progress
1029 ||' g_next_approvers.api_insertion:'
1030 ||g_next_approvers(l_approver_index).api_insertion);
1031 END IF;
1032
1033 IF (g_next_approvers (l_approver_index).api_insertion = 'Y') THEN
1034 n_varval (5) := 1;
1035 ELSE
1036 n_varval (5) := g_next_approvers (l_approver_index).group_or_chain_id;
1037 END IF;
1038
1039 n_varname (6) := 'RESPONSIBILITY_ID';
1040 n_varval (6) := po_wf_util_pkg.GetItemAttrNumber (aname => 'RESPONSIBILITY_ID');
1041 n_varname (7) := 'APPLICATION_ID';
1042 n_varval (7) := po_wf_util_pkg.GetItemAttrNumber (aname => 'APPLICATION_ID');
1043
1044 IF (g_po_wf_debug = 'Y') THEN
1045 PO_WF_DEBUG_PKG.insert_debug (itemtype, itemkey, l_log_head||':'||l_progress
1046 ||' RESP:'||n_varval(6)
1047 ||' APPL_ID:'||n_varval(7));
1048 END IF;
1049 n_varname (8) := 'AME_TRANSACTION_ID';
1050 n_varval (8) := l_ame_transaction_id;
1051 n_varname (9) := 'DRAFT_ID';
1052 n_varval (9) := po_wf_util_pkg.GetItemAttrNumber (aname => 'DRAFT_ID');
1053 n_varname (10) := 'REVISION_NUMBER';
1054 n_varval (10) := po_wf_util_pkg.GetItemAttrNumber (aname => 'REVISION_NUMBER');
1055
1056 wf_engine.SetItemAttrNumberArray (itemtype, l_item_key, n_varname, n_varval);
1057
1058 l_progress := '100';
1059
1060 wf_engine.SetItemOwner(
1061 itemtype => itemtype,
1062 itemkey => l_item_key,
1063 owner => l_owner_user_name);
1064
1065 wf_engine.SetItemUserKey(
1066 itemtype => itemtype,
1067 itemkey => l_item_key,
1068 userkey => l_userkey);
1069
1070 --Kick off the process
1071 wf_engine.StartProcess (itemtype => itemtype ,itemkey => l_item_key);
1072
1073 --Move to the next index
1074 l_approver_index := g_next_approvers.next (l_approver_index);
1075
1076 END LOOP; --WHILE (l_approver_index IS NOT NULL)
1077
1078 l_progress := '110';
1079
1080 IF l_start_block_activity = 'Y' THEN
1081 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1082 ELSE
1083 resultout := wf_engine.eng_completed || ':' || '';
1084 END IF;
1085
1086 -- After routing is done, delete approver list
1087 g_next_approvers.delete;
1088
1089 RETURN;
1090
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1094 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1095 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
1096 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, l_progress, l_document_id);
1097 IF (g_po_wf_debug = 'Y') THEN
1098 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
1099 END IF;
1100 RAISE;
1101 END launch_parallel_approval;
1102
1103 --------------------------------------------------------------------------------
1104 --Start of Comments
1105 --Name: determine_approver_category
1106 --Pre-reqs:
1107 -- None.
1108 --Modifies:
1109 -- None.
1110 --Locks:
1111 -- None.
1112 --Function:
1113 -- Workflow activity PL/SQL handler.
1114 -- This is uesd to determine approver_category.
1115 -- Values can be 'APPROVER', 'ESIGNER' and 'REVIEWER'.
1116 --Parameters:
1117 --IN:
1118 -- Standard workflow IN parameters
1119 --OUT:
1120 -- Standard workflow OUT parameters
1121 --Testing:
1122 -- None
1123 --End of Comments
1124 ---------------------------------------------------------------------------------
1125 PROCEDURE determine_approver_category(
1126 itemtype IN VARCHAR2,
1127 itemkey IN VARCHAR2,
1128 actid IN NUMBER,
1129 funcmode IN VARCHAR2,
1130 resultout OUT NOCOPY VARCHAR2)
1131 IS
1132 l_approver_category VARCHAR2(100);
1133 BEGIN
1134
1135 IF (funcmode <> wf_engine.eng_run) THEN
1136 resultout := wf_engine.eng_null;
1137 RETURN;
1138 END IF;
1139
1140 --Set the global attributes in the po wrapper function
1141 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
1142 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
1143
1144 --Logic is check for workflow attribute 'APPROVER_CATEGORY' and pass it ahead
1145 l_approver_category := po_wf_util_pkg.GetItemAttrText (aname => 'APPROVER_CATEGORY');
1146 resultout := wf_engine.eng_completed || ':' || l_approver_category;
1147
1148 END determine_approver_category;
1149 --------------------------------------------------------------------------------
1150 --Start of Comments
1151 --Name: process_response_internal
1152 --Pre-reqs:
1153 -- None.
1154 --Modifies:
1155 -- None.
1156 --Locks:
1157 -- None.
1158 --Function:
1159 -- Workflow activity PL/SQL handler.
1160 -- This procedure is used to inform AME about the approvers response.
1161 --Parameters:
1162 --IN:
1163 -- Standard workflow IN parameters
1164 --OUT:
1165 -- Standard workflow OUT parameters
1166 --Testing:
1167 --
1168 --End of Comments
1169 -------------------------------------------------------------------------------
1170
1171 PROCEDURE process_response_internal(
1172 itemtype IN VARCHAR2,
1173 itemkey IN VARCHAR2,
1174 p_response IN VARCHAR2 )
1175 IS
1176 l_progress VARCHAR2(3) := '000';
1177 l_document_id NUMBER;
1178 l_transaction_type po_document_types.ame_transaction_type%TYPE;
1179 l_current_approver ame_util.approverRecord2;
1180 l_forwardee ame_util.approverRecord2;
1181 l_approver_posoition_id NUMBER;
1182 l_approver_type VARCHAR2(10);
1183 l_parent_item_type wf_items.parent_item_type%TYPE;
1184 l_parent_item_key wf_items.parent_item_key%TYPE;
1185 l_document_type po_document_types.document_type_code%TYPE;
1186 l_ame_transaction_id NUMBER;
1187 l_error_message ame_util.longestStringType;
1188 wf_role_not_found EXCEPTION;
1189 l_api_name VARCHAR2(500) := 'process_response_internal';
1190 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
1191 BEGIN
1192
1193 --Set the global attributes in the po wrapper function
1194 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
1195 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
1196
1197 -- Logic:
1198 -- Fetch all required workflow attributes
1199 -- Need l_forwardee approverRecord to be populated in case of FORWRAD and
1200 -- APPROVE AND FORWARD on the basis of current_approver_type.
1201 -- Also populate l_forwardee.name value or else AME throws exception
1202 -- Update current approval record attribute approval_status with proper
1203 -- status, so that same can be communicated to AME.
1204 -- Update AME about current status of approver through API ame_api2.updateApprovalStatus.
1205
1206 l_progress := '010';
1207 IF (g_po_wf_debug = 'Y') THEN
1208 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
1209 END IF;
1210
1211 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
1212 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
1213 l_ame_transaction_id := po_wf_util_pkg.GetItemAttrNumber(aname => 'AME_TRANSACTION_ID');
1214 l_transaction_type := po_wf_util_pkg.GetItemAttrText( aname => 'AME_TRANSACTION_TYPE');
1215 l_approver_type := po_wf_util_pkg.GetItemAttrText( aname => 'AME_APPROVER_TYPE');
1216
1217 --Populate l_forwardee approverRecord on the basis of current approver_type
1218 IF (l_approver_type = ame_util.posOrigSystem) THEN
1219 l_current_approver.orig_system := ame_util.posOrigSystem;
1220 ELSIF (l_approver_type = ame_util.fndUserOrigSystem) THEN
1221 l_current_approver.orig_system := ame_util.fndUserOrigSystem;
1222 ELSE
1223 l_current_approver.orig_system := ame_util.perOrigSystem;
1224 l_current_approver.name := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_USER_NAME');
1225 END IF;
1226
1227 l_current_approver.orig_system_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'AME_APPROVER_ID');
1228 l_forwardee.orig_system := ame_util.perOrigSystem;
1229 l_forwardee.name := po_wf_util_pkg.GetItemAttrText( aname => 'FORWARD_TO_USERNAME_RESPONSE');
1230
1231 BEGIN
1232
1233 SELECT employee_id
1234 INTO l_forwardee.orig_system_id
1235 FROM fnd_user
1236 WHERE user_name = l_forwardee.name;
1237
1238 EXCEPTION
1239
1240 WHEN OTHERS THEN
1241 l_forwardee.orig_system_id := NULL;
1242 END;
1243
1244 l_progress := '020';
1245
1246 --Update current approval record attribute approval_status with proper status
1247 --so that same can be communicated to AME.
1248 IF( p_response = 'APPROVE') THEN
1249 l_current_approver.approval_status := ame_util.approvedStatus;
1250 ELSIF( p_response = 'REJECT') THEN
1251 l_current_approver.approval_status := ame_util.rejectStatus;
1252 ELSIF( p_response = 'TIMEOUT') THEN
1253 l_current_approver.approval_status := ame_util.noResponseStatus;
1254 ELSIF( p_response = 'FORWARD') THEN
1255 l_current_approver.approval_status := ame_util.forwardStatus;
1256 ELSIF( p_response = 'APPROVE AND FORWARD') THEN
1257 l_current_approver.approval_status := ame_util.approveAndForwardStatus;
1258 ELSIF( p_response = 'EXCEPTION') THEN
1259 l_current_approver.approval_status := ame_util.exceptionStatus;
1260 END IF;
1261
1262 l_progress := '030';
1263 IF (g_po_wf_debug = 'Y') THEN
1264 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
1265 ||' p_response'||p_response
1266 ||' l_forwardee.name:'||l_forwardee.name);
1267 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
1268 ||' p_response'||p_response
1269 ||' l_forwardee.orig_system:'||l_forwardee.orig_system);
1270 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
1271 ||' l_forwardee.orig_system_id:'||l_forwardee.orig_system_id);
1272 END IF;
1273
1274 -- Get the name value for the approverRecord2.
1275 -- This is a mandatory field. If we do not pass this value to AME, we will get invalid parameter exception.
1276 IF p_response IN ('FORWARD', 'APPROVE AND FORWARD') THEN
1277
1278 IF l_forwardee.name IS NULL THEN
1279 SELECT name
1280 INTO l_forwardee.name
1281 FROM ( SELECT name
1282 FROM wf_roles
1283 WHERE orig_system = l_forwardee.orig_system
1284 AND orig_system_id = l_forwardee.orig_system_id
1285 ORDER BY start_date )
1286 WHERE ROWNUM = 1;
1287 END IF;
1288
1289 END IF;
1290
1291 IF l_current_approver.name IS NULL THEN
1292 SELECT name
1293 INTO l_current_approver.name
1294 FROM ( SELECT name
1295 FROM wf_roles
1296 WHERE orig_system = l_current_approver.orig_system
1297 AND orig_system_id = l_current_approver.orig_system_id
1298 ORDER BY start_date )
1299 WHERE ROWNUM = 1;
1300 END IF;
1301
1302
1303 l_progress := '040';
1304 IF l_current_approver.name IS NULL THEN
1305 RAISE wf_role_not_found;
1306 END IF;
1307
1308 l_progress := '050';
1309 IF (g_po_wf_debug = 'Y') THEN
1310 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
1311 ||' l_current_approver.name:'||l_current_approver.name);
1312 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
1313 ||' l_current_approver.orig_system:'||l_current_approver.orig_system);
1314 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
1315 ||' l_current_approver.orig_system_id:'||l_current_approver.orig_system_id);
1316 END IF;
1317
1318
1319
1320 l_progress := '060';
1321 -- Update the Approval status with the response from the approver.
1322 IF p_response IN ('FORWARD', 'APPROVE AND FORWARD') THEN
1323 ame_api2.updateApprovalStatus(
1324 applicationIdIn => applicationId,
1325 transactionIdIn => l_ame_transaction_id,
1326 transactionTypeIn => l_transaction_type,
1327 approverIn => l_current_approver,
1328 forwardeeIn => l_forwardee);
1329 ELSE
1330 ame_api2.updateApprovalStatus(
1331 applicationIdIn => applicationId,
1332 transactionIdIn => l_ame_transaction_id,
1333 transactionTypeIn => l_transaction_type,
1334 approverIn => l_current_approver);
1335 END IF;
1336
1337 EXCEPTION
1338 WHEN wf_role_not_found THEN
1339 -- Exception is not passed on. This is expected to complete normally.
1340 l_error_message := SQLERRM;
1341
1342 SELECT parent_item_type, parent_item_key
1343 INTO l_parent_item_type, l_parent_item_key
1344 FROM wf_items
1345 WHERE item_type = itemtype
1346 AND item_key = itemkey;
1347
1348 po_wf_util_pkg.SetItemAttrText(
1349 itemtype => l_parent_item_type,
1350 itemkey => l_parent_item_key,
1351 aname => 'AME_EXCEPTION',
1352 avalue => l_error_message );
1353
1354 WHEN OTHERS THEN
1355 RAISE;
1356 END process_response_internal;
1357
1358 --------------------------------------------------------------------------------
1359 --Start of Comments
1360 --Name: process_response_exception
1361 --Pre-reqs:
1362 -- None.
1363 --Modifies:
1364 -- None.
1365 --Locks:
1366 -- None.
1367 --Function:
1368 -- Workflow activity PL/SQL handler.
1369 -- This procedure is the wrapper procedure of process_response_internal()
1370 -- This procedure stmaps current approver workflow
1371 --Parameters:
1372 --IN:
1373 -- Standard workflow IN parameters
1374 --OUT:
1375 -- Standard workflow OUT parameters
1376 --Testing:
1377 --
1378 --End of Comments
1379 -------------------------------------------------------------------------------
1380 PROCEDURE process_response_exception(
1381 itemtype IN VARCHAR2,
1382 itemkey IN VARCHAR2,
1383 actid IN NUMBER,
1384 funcmode IN VARCHAR2,
1385 resultout OUT NOCOPY VARCHAR2 )
1386 IS
1387 l_progress VARCHAR2(3) := '000';
1388 l_parent_item_type wf_items.parent_item_type%TYPE;
1389 l_parent_item_key wf_items.parent_item_key%TYPE;
1390 l_child_approver_empid NUMBER;
1391 l_child_approver_user_name wf_users.name%TYPE;
1392 l_child_approver_display_name wf_users.display_name%TYPE;
1393 l_api_name VARCHAR2(500) := 'process_response_exception';
1394 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
1395 BEGIN
1396
1397 IF (funcmode <> wf_engine.eng_run) THEN
1398 resultout := wf_engine.eng_null;
1399 RETURN;
1400 END IF;
1401
1402 --Set the global attributes in the po wrapper function
1403 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
1404 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
1405
1406 l_progress := '010';
1407 IF (g_po_wf_debug = 'Y') THEN
1408 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
1409 END IF;
1410
1411 SELECT parent_item_type, parent_item_key
1412 INTO l_parent_item_type, l_parent_item_key
1413 FROM wf_items
1414 WHERE item_type = itemtype
1415 AND item_key = itemkey;
1416
1417 -- Call process_response_internal with 'EXCEPTION'
1418 process_response_internal(itemtype, itemkey, 'EXCEPTION');
1419 po_wf_util_pkg.SetItemAttrText(aname => 'AME_SUB_APPROVAL_RESPONSE', avalue => 'EXCEPTION');
1420 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1421 RETURN;
1422 END Process_Response_Exception;
1423
1424 --------------------------------------------------------------------------------
1425 --Start of Comments
1426 --Name: insert_action_history
1427 --Pre-reqs:
1428 -- None.
1429 --Modifies:
1430 -- None.
1431 --Locks:
1432 -- None.
1433 --Function:
1434 -- Workflow activity PL/SQL handler.
1435 -- This procedure is the wrapper procedure of insertActionHistory()
1436 --Parameters:
1437 --IN:
1438 -- Standard workflow IN parameters
1439 --OUT:
1440 -- Standard workflow OUT parameters
1441 --Testing:
1442 --
1443 --End of Comments
1444 -------------------------------------------------------------------------------
1445 PROCEDURE insert_action_history(
1446 itemtype IN VARCHAR2,
1447 itemkey IN VARCHAR2,
1448 actid IN NUMBER,
1449 funcmode IN VARCHAR2,
1450 resultout OUT NOCOPY VARCHAR2)
1451 IS
1452
1453 l_progress VARCHAR2(3) := '000';
1454 l_document_id NUMBER;
1455 l_draft_id NUMBER;
1456 l_revision_num NUMBER;
1457 l_action VARCHAR2(30) := NULL;
1458 l_next_approver_id NUMBER :='';
1459 l_document_type po_document_types.document_type_code%TYPE;
1460 l_document_subtype po_document_types_all_b.document_subtype%TYPE;
1461 l_approval_group_id NUMBER:='';
1462 l_doc_string VARCHAR2(200);
1463 l_preparer_user_name VARCHAR2(100);
1464 l_org_id NUMBER;
1465 l_api_name VARCHAR2(500) := 'insert_action_history';
1466 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
1467 BEGIN
1468
1469 IF (funcmode <> wf_engine.eng_run) THEN
1470 resultout := wf_engine.eng_null;
1471 RETURN;
1472 END IF;
1473
1474 --Set the global attributes in the po wrapper function
1475 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
1476 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
1477
1478 -- Logic:
1479 -- + Fetch worklfow attributes required for inserting NULL record into action history.
1480 -- against current approver.
1481 -- + Call autonomous transaction InsertActionHistoryPoAme to insert record into action hsitory.
1482 -- + Reset attributes 'FORWARD_TO_USERNAME_RESPONSE' and 'NOTE'
1483 l_progress := '010';
1484 IF (g_po_wf_debug = 'Y') THEN
1485 PO_WF_DEBUG_PKG.insert_debug(itemtype, itemkey, l_log_head||':'||l_progress);
1486 END IF;
1487
1488 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
1489 l_draft_id := po_wf_util_pkg.GetItemAttrText( aname => 'DRAFT_ID');
1490 l_next_approver_id := po_wf_util_pkg.GetItemAttrNumber( aname=>'APPROVER_EMPID');
1491 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
1492 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText ( aname => 'DOCUMENT_SUBTYPE');
1493 l_approval_group_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'APPROVAL_GROUP_ID');
1494 l_org_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'ORG_ID');
1495 l_revision_num := po_wf_util_pkg.GetItemAttrNumber( aname => 'REVISION_NUMBER');
1496
1497 IF l_org_id IS NOT NULL THEN
1498 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1499 END IF;
1500
1501 l_progress := '020';
1502 InsertActionHistoryPoAme(
1503 p_document_id => l_document_id,
1504 p_draft_id => l_draft_id,
1505 p_document_type => l_document_type,
1506 p_document_subtype => l_document_subtype,
1507 p_revision_num => l_revision_num,
1508 p_employee_id => l_next_approver_id,
1509 p_approval_group_id => l_approval_group_id,
1510 p_action => l_action );
1511
1512 l_progress := '030';
1513
1514 --Reset the FORWARD_TO_USERNAME_RESPONSE and NOTE attributes
1515 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_TO_USERNAME_RESPONSE', avalue => NULL);
1516 po_wf_util_pkg.SetItemAttrText( aname => 'NOTE', avalue => NULL);
1517 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
1518
1519 RETURN;
1520
1521 EXCEPTION
1522 WHEN OTHERS THEN
1523 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1524 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1525 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
1526 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, l_progress, l_document_id);
1527 IF (g_po_wf_debug = 'Y') THEN
1528 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
1529 END IF;
1530 RAISE;
1531 END insert_action_history;
1532
1533 --------------------------------------------------------------------------------
1534 --Start of Comments
1535 --Name: update_action_history_forward
1536 --Pre-reqs:
1537 -- None.
1538 --Modifies:
1539 -- None.
1540 --Locks:
1541 -- None.
1542 --Function:
1543 -- Workflow activity PL/SQL handler.
1544 -- This procedure updates the po_action_history table based on the approvers response.
1545 --Parameters:
1546 --IN:
1547 -- Standard workflow IN parameters
1548 --OUT:
1549 -- Standard workflow OUT parameters
1550 --Testing:
1551 --
1552 --End of Comments
1553 -------------------------------------------------------------------------------
1554
1555 PROCEDURE update_action_history_forward(
1556 itemtype IN VARCHAR2,
1557 itemkey IN VARCHAR2,
1558 actid IN NUMBER,
1559 funcmode IN VARCHAR2,
1560 resultout OUT NOCOPY VARCHAR2)
1561 IS
1562 l_progress VARCHAR2(3) := '000';
1563 l_action VARCHAR2(30) := 'FORWARD';
1564 l_forward_to_id NUMBER :='';
1565 l_document_id NUMBER;
1566 l_document_type VARCHAR2(25):='';
1567 l_document_subtype VARCHAR2(25):='';
1568 l_return_code NUMBER;
1569 l_result BOOLEAN:=FALSE;
1570 l_note VARCHAR2(4000);
1571 l_doc_string VARCHAR2(200);
1572 l_preparer_user_name VARCHAR2(100);
1573 l_org_id NUMBER;
1574 l_current_approver NUMBER;
1575 l_draft_id NUMBER;
1576 l_api_name VARCHAR2(500) := 'Update_Action_History_Forward';
1577 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
1578
1579 BEGIN
1580
1581 IF (funcmode <> wf_engine.eng_run) THEN
1582 resultout := wf_engine.eng_null;
1583 RETURN;
1584 END IF;
1585
1586 --Set the global attributes in the po wrapper function
1587 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
1588 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
1589
1590 -- Logic :
1591 -- + Fetch worklfow attributes required for updating NULL action to FORWARD action in
1592 -- action history against current approver.
1593 -- + Call autonomous transaction UpdateActionHistoryPoAme to update po_action_history.
1594 -- + Also set 'APPROVER_RESPONSE' workflow attribute with value 'FORWARD'
1595
1596 l_progress := '010';
1597 IF (g_po_wf_debug = 'Y') THEN
1598 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
1599 END IF;
1600
1601 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( aname=>'APPROVER_EMPID');
1602 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
1603 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
1604 l_document_subtype := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_SUBTYPE');
1605 l_note := po_wf_util_pkg.GetItemAttrText( aname => 'NOTE');
1606 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DRAFT_ID');
1607 l_org_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'ORG_ID');
1608
1609 IF l_org_id IS NOT NULL THEN
1610 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1611 END IF;
1612
1613 l_progress := '020';
1614 UpdateActionHistoryPoAme (
1615 p_document_id => l_document_id,
1616 p_draft_id => l_draft_id,
1617 p_document_type => l_document_type,
1618 p_document_subtype => l_document_subtype,
1619 p_action => l_action,
1620 p_note =>l_note,
1621 p_current_approver => l_current_approver);
1622
1623 l_progress := '030';
1624 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_RESPONSE', avalue => 'FORWARD' );
1625 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
1626
1627 EXCEPTION
1628 WHEN OTHERS THEN
1629 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1630 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1631 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
1632 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, l_progress, l_document_id);
1633 IF (g_po_wf_debug = 'Y') THEN
1634 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Exception:'||sqlerrm);
1635 END IF;
1636 RAISE;
1637 END Update_Action_History_Forward;
1638
1639 --------------------------------------------------------------------------------
1640 --Start of Comments
1641 --Name: update_action_history_approve
1642 --Pre-reqs:
1643 -- None.
1644 --Modifies:
1645 -- None.
1646 --Locks:
1647 -- None.
1648 --Function:
1649 -- Workflow activity PL/SQL handler.
1650 -- This procedure updates the po_action_history table based on the approvers response.
1651 --Parameters:
1652 --IN:
1653 -- Standard workflow IN parameters
1654 --OUT:
1655 -- Standard workflow OUT parameters
1656 --Testing:
1657 --
1658 --End of Comments
1659 -------------------------------------------------------------------------------
1660 PROCEDURE update_action_history_approve(
1661 itemtype IN VARCHAR2,
1662 itemkey IN VARCHAR2,
1663 actid IN NUMBER,
1664 funcmode IN VARCHAR2,
1665 resultout OUT NOCOPY VARCHAR2)
1666 IS
1667 l_progress VARCHAR2(3) := '000';
1668 l_action VARCHAR2(30) := 'APPROVE';
1669 l_forward_to_id NUMBER :='';
1670 l_document_id NUMBER;
1671 l_document_type VARCHAR2(25):='';
1672 l_document_subtype VARCHAR2(25):='';
1673 l_return_code NUMBER;
1674 l_result BOOLEAN:=FALSE;
1675 l_note VARCHAR2(4000);
1676 l_doc_string VARCHAR2(200);
1677 l_preparer_user_name VARCHAR2(100);
1678 l_org_id NUMBER;
1679 l_current_approver NUMBER;
1680 l_draft_id NUMBER;
1681 l_approver_category VARCHAR2(100);
1682 l_api_name VARCHAR2(500) := 'Update_Action_History_Approve';
1683 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
1684
1685 BEGIN
1686
1687 IF (funcmode <> wf_engine.eng_run) THEN
1688 resultout := wf_engine.eng_null;
1689 RETURN;
1690 END IF;
1691
1692 --Set the global attributes in the po wrapper function
1693 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
1694 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
1695
1696 -- Logic :
1697 -- + Fetch worklfow attributes required for updating NULL action to APPROVE for approver,
1698 -- REVIEW ACCEPTED for Reviewer into action history, SIGNED for ESIGNER
1699 -- against current approver/reviewer/esigner.
1700 -- + Call autonomous transaction UpdateActionHistoryPoAme to update po_action_history.
1701 -- + Also set 'APPROVER_RESPONSE' workflow attribute with value 'APPROVE'
1702
1703 l_progress := '010';
1704 IF (g_po_wf_debug = 'Y') THEN
1705 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
1706 END IF;
1707
1708 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( aname=>'APPROVER_EMPID');
1709 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
1710 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
1711 l_document_subtype := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_SUBTYPE');
1712 l_note := po_wf_util_pkg.GetItemAttrText( aname => 'NOTE');
1713 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DRAFT_ID');
1714 l_org_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'ORG_ID');
1715
1716 IF l_org_id IS NOT NULL THEN
1717 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1718 END IF;
1719
1720 --Change the l_action for REVIEWER
1721 l_approver_category := po_wf_util_pkg.GetItemAttrText ( aname => 'APPROVER_CATEGORY');
1722 IF l_approver_category = 'REVIEWER' THEN
1723 l_action := 'REVIEW ACCEPTED';
1724 ELSIF l_approver_category = 'ESIGNER' THEN
1725 l_action := 'SIGNED';
1726 ELSE
1727 l_action := 'APPROVE';
1728 END IF;
1729
1730 l_progress := '020';
1731 IF (g_po_wf_debug = 'Y') THEN
1732 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
1733 ||' Doc Info:'||TO_CHAR(l_document_id)||'-'
1734 ||TO_CHAR(l_draft_id)||'-'||l_document_type
1735 ||'-'||l_document_subtype);
1736 END IF;
1737
1738 UpdateActionHistoryPoAme (
1739 p_document_id => l_document_id,
1740 p_draft_id => l_draft_id,
1741 p_document_type => l_document_type,
1742 p_document_subtype => l_document_subtype,
1743 p_action => l_action,
1744 p_note =>l_note,
1745 p_current_approver => l_current_approver);
1746
1747 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_RESPONSE', avalue => 'APPROVED' );
1748 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
1749 RETURN;
1750
1751 EXCEPTION
1752 WHEN OTHERS THEN
1753 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1754 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1755 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
1756 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, l_progress, l_document_id);
1757 IF (g_po_wf_debug = 'Y') THEN
1758 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
1759 END IF;
1760 RAISE;
1761 END Update_Action_History_Approve;
1762
1763 --------------------------------------------------------------------------------
1764 --Start of Comments
1765 --Name: update_action_history_reject
1766 --Pre-reqs:
1767 -- None.
1768 --Modifies:
1769 -- None.
1770 --Locks:
1771 -- None.
1772 --Function:
1773 -- Workflow activity PL/SQL handler.
1774 -- This procedure updates the po_action_history table based on the approvers response.
1775 --Parameters:
1776 --IN:
1777 -- Standard workflow IN parameters
1778 --OUT:
1779 -- Standard workflow OUT parameters
1780 --Testing:
1781 --
1782 --End of Comments
1783 -------------------------------------------------------------------------------
1784 PROCEDURE update_action_history_reject(
1785 itemtype IN VARCHAR2,
1786 itemkey IN VARCHAR2,
1787 actid IN NUMBER,
1788 funcmode IN VARCHAR2,
1789 resultout OUT NOCOPY VARCHAR2)
1790 IS
1791 l_progress VARCHAR2(3) := '000';
1792 l_action VARCHAR2(30) := 'REJECT';
1793 l_document_id NUMBER;
1794 l_document_type VARCHAR2(25):='';
1795 l_document_subtype VARCHAR2(25):='';
1796 l_return_code NUMBER;
1797 l_result BOOLEAN:=FALSE;
1798 l_note VARCHAR2(4000);
1799 l_doc_string VARCHAR2(200);
1800 l_preparer_user_name VARCHAR2(100);
1801 l_org_id NUMBER;
1802 l_current_approver NUMBER;
1803 l_draft_id NUMBER;
1804 l_approver_category VARCHAR2(100);
1805 l_api_name VARCHAR2(500) := 'Update_Action_History_Reject';
1806 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
1807
1808 BEGIN
1809
1810 IF (funcmode <> wf_engine.eng_run) THEN
1811 resultout := wf_engine.eng_null;
1812 RETURN;
1813 END IF;
1814
1815 --Set the global attributes in the po wrapper function
1816 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
1817 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
1818
1819 l_progress := '010';
1820 IF (g_po_wf_debug = 'Y') THEN
1821 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
1822 END IF;
1823
1824 -- Logic :
1825 -- + Fetch worklfow attributes required for updating NULL action to REJECT for approver,
1826 -- REVIEW REJECTED for Reviewer into action history, SIGNER REJECTED for ESIGNER
1827 -- against current approver/reviewer/esigner.
1828 -- + Call autonomous transaction UpdateActionHistoryPoAme to update po_action_history.
1829 -- + Also set 'APPROVER_RESPONSE' workflow attribute with value 'REJECTED'
1830
1831 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( aname=>'APPROVER_EMPID');
1832 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
1833 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
1834 l_document_subtype := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_SUBTYPE');
1835 l_note := po_wf_util_pkg.GetItemAttrText( aname => 'NOTE');
1836 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DRAFT_ID');
1837 l_org_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'ORG_ID');
1838
1839 IF l_org_id IS NOT NULL THEN
1840 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1841 END IF;
1842
1843 --Change the l_action for REVIEWER
1844 l_approver_category := po_wf_util_pkg.GetItemAttrText ( aname => 'APPROVER_CATEGORY');
1845 IF l_approver_category = 'REVIEWER' THEN
1846 l_action := 'REVIEW REJECTED';
1847 ELSIF l_approver_category = 'ESIGNER' THEN
1848 l_action := 'SIGNER REJECTED';
1849 ELSE
1850 l_action := 'REJECT';
1851 END IF;
1852
1853 l_progress := '020';
1854 IF (g_po_wf_debug = 'Y') THEN
1855 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
1856 ||' Doc Info:'||TO_CHAR(l_document_id)||'-'
1857 ||TO_CHAR(l_draft_id)||'-'||l_document_type
1858 ||'-'||l_document_subtype);
1859 END IF;
1860
1861 UpdateActionHistoryPoAme (
1862 p_document_id => l_document_id,
1863 p_draft_id => l_draft_id,
1864 p_document_type => l_document_type,
1865 p_document_subtype => l_document_subtype,
1866 p_action => l_action,
1867 p_note =>l_note,
1868 p_current_approver => l_current_approver);
1869
1870 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_RESPONSE', avalue => 'REJECTED' );
1871 resultout := wf_engine.eng_completed||':'|| 'ACTIVITY_PERFORMED';
1872
1873 EXCEPTION
1874 WHEN OTHERS THEN
1875 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1876 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1877 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
1878 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, l_progress, l_document_id);
1879 IF (g_po_wf_debug = 'Y') THEN
1880 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
1881 END IF;
1882 RAISE;
1883 END Update_Action_History_Reject;
1884
1885 --------------------------------------------------------------------------------
1886 --Start of Comments
1887 --Name: update_action_history_timeout
1888 --Pre-reqs:
1889 -- None.
1890 --Modifies:
1891 -- None.
1892 --Locks:
1893 -- None.
1894 --Function:
1895 -- Workflow activity PL/SQL handler.
1896 -- This procedure updates the po_action_history table based on the approvers response.
1897 --Parameters:
1898 --IN:
1899 -- Standard workflow IN parameters
1900 --OUT:
1901 -- Standard workflow OUT parameters
1902 --Testing:
1903 --
1904 --End of Comments
1905 -------------------------------------------------------------------------------
1906 PROCEDURE update_action_history_timeout(
1907 itemtype IN VARCHAR2,
1908 itemkey IN VARCHAR2,
1909 actid IN NUMBER,
1910 funcmode IN VARCHAR2,
1911 resultout OUT NOCOPY VARCHAR2)
1912 IS
1913 l_progress VARCHAR2(3) := '000';
1914 l_action VARCHAR2(30) := 'TIMED OUT';
1915 l_document_id NUMBER;
1916 l_document_type VARCHAR2(25):='';
1917 l_document_subtype VARCHAR2(25):='';
1918 l_return_code NUMBER;
1919 l_result BOOLEAN:=FALSE;
1920 l_note VARCHAR2(4000);
1921 l_doc_string VARCHAR2(200);
1922 l_preparer_user_name VARCHAR2(100);
1923 l_org_id NUMBER;
1924 l_current_approver NUMBER;
1925 l_draft_id NUMBER;
1926 l_api_name VARCHAR2(500) := 'Update_Action_History_Timeout';
1927 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
1928
1929 BEGIN
1930
1931 IF (funcmode <> wf_engine.eng_run) THEN
1932 resultout := wf_engine.eng_null;
1933 RETURN;
1934 END IF;
1935
1936 --Set the global attributes in the po wrapper function
1937 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
1938 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
1939
1940 l_progress := '010';
1941 IF (g_po_wf_debug = 'Y') THEN
1942 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
1943 END IF;
1944
1945 -- Logic:
1946 -- + Fetch worklfow attributes required for updating NULL action to NO ACTION action into action history
1947 -- against current approver.
1948 -- + Call autonomous transaction UpdateActionHistoryPoAme to update po_action_history.
1949
1950 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( aname=>'APPROVER_EMPID');
1951 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
1952 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DRAFT_ID');
1953 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
1954 l_document_subtype := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_SUBTYPE');
1955 l_note := fnd_message.get_string('ICX', 'ICX_POR_NOTIF_TIMEOUT');
1956 l_org_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'ORG_ID');
1957 IF l_org_id IS NOT NULL THEN
1958 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
1959 END IF;
1960
1961 l_progress := '020';
1962 IF (g_po_wf_debug = 'Y') THEN
1963 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
1964 ||' Doc Info:'||TO_CHAR(l_document_id)
1965 ||'-'||TO_CHAR(l_draft_id)||'-'
1966 ||l_document_type||'-'||l_document_subtype);
1967 END IF;
1968
1969 UpdateActionHistoryPoAme (
1970 p_document_id => l_document_id,
1971 p_draft_id => l_draft_id,
1972 p_document_type => l_document_type,
1973 p_document_subtype => l_document_subtype,
1974 p_action => l_action,
1975 p_note =>l_note,
1976 p_current_approver => l_current_approver);
1977
1978 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
1979 RETURN;
1980
1981 EXCEPTION
1982 WHEN OTHERS THEN
1983 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
1984 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
1985 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
1986 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, l_progress, l_document_id);
1987 IF (g_po_wf_debug = 'Y') THEN
1988 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
1989 END IF;
1990 RAISE;
1991 END Update_Action_History_Timeout;
1992
1993 --------------------------------------------------------------------------------
1994 --Start of Comments
1995 --Name: update_action_history_timeout
1996 --Pre-reqs:
1997 -- None.
1998 --Modifies:
1999 -- None.
2000 --Locks:
2001 -- None.
2002 --Function:
2003 -- Workflow activity PL/SQL handler.
2004 -- This procedure updates the po_action_history table based on the approvers response.
2005 --Parameters:
2006 --IN:
2007 -- Standard workflow IN parameters
2008 --OUT:
2009 -- Standard workflow OUT parameters
2010 --Testing:
2011 --
2012 --End of Comments
2013 -------------------------------------------------------------------------------
2014 PROCEDURE update_action_history_app_fwd(
2015 itemtype IN VARCHAR2,
2016 itemkey IN VARCHAR2,
2017 actid IN NUMBER,
2018 funcmode IN VARCHAR2,
2019 resultout OUT NOCOPY VARCHAR2)
2020 IS
2021 l_progress VARCHAR2(3) := '000';
2022 l_action VARCHAR2(30) := 'APPROVE AND FORWARD';
2023 l_document_id NUMBER;
2024 l_document_type VARCHAR2(25):='';
2025 l_document_subtype VARCHAR2(25):='';
2026 l_return_code NUMBER;
2027 l_result BOOLEAN:=FALSE;
2028 l_note VARCHAR2(4000);
2029 l_doc_string VARCHAR2(200);
2030 l_preparer_user_name VARCHAR2(100);
2031 l_org_id NUMBER;
2032 l_current_approver NUMBER;
2033 l_draft_id NUMBER;
2034 l_api_name VARCHAR2(500) := 'update_action_history_app_fwd';
2035 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
2036
2037 BEGIN
2038
2039 IF (funcmode <> wf_engine.eng_run) THEN
2040 resultout := wf_engine.eng_null;
2041 RETURN;
2042 END IF;
2043
2044 --Set the global attributes in the po wrapper function
2045 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
2046 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
2047
2048 l_progress := '010';
2049 IF (g_po_wf_debug = 'Y') THEN
2050 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
2051 END IF;
2052
2053 -- Logic:
2054 -- + Fetch worklfow attributes required for updating NULL action to NO ACTION action into action history
2055 -- against current approver.
2056 -- + Call autonomous transaction UpdateActionHistoryPoAme to update po_action_history.
2057
2058 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( aname=>'APPROVER_EMPID');
2059 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
2060 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DRAFT_ID');
2061 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
2062 l_document_subtype := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_SUBTYPE');
2063 l_note := fnd_message.get_string('ICX', 'ICX_POR_NOTIF_TIMEOUT');
2064 l_org_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'ORG_ID');
2065 IF l_org_id IS NOT NULL THEN
2066 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
2067 END IF;
2068
2069 l_progress := '020';
2070 IF (g_po_wf_debug = 'Y') THEN
2071 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
2072 ||' Doc Info:'||TO_CHAR(l_document_id)
2073 ||'-'||TO_CHAR(l_draft_id)||'-'
2074 ||l_document_type||'-'||l_document_subtype);
2075 END IF;
2076
2077 UpdateActionHistoryPoAme (
2078 p_document_id => l_document_id,
2079 p_draft_id => l_draft_id,
2080 p_document_type => l_document_type,
2081 p_document_subtype => l_document_subtype,
2082 p_action => l_action,
2083 p_note => l_note,
2084 p_current_approver => l_current_approver);
2085
2086 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
2087 RETURN;
2088
2089 EXCEPTION
2090 WHEN OTHERS THEN
2091 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
2092 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
2093 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
2094 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, l_progress, l_document_id);
2095 IF (g_po_wf_debug = 'Y') THEN
2096 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
2097 END IF;
2098 RAISE;
2099 END update_action_history_app_fwd;
2100
2101 --------------------------------------------------------------------------------
2102 --Start of Comments
2103 --Name: Process_Response_App_Forward
2104 --Pre-reqs:
2105 -- None.
2106 --Modifies:
2107 -- None.
2108 --Locks:
2109 -- None.
2110 --Function:
2111 -- Workflow activity PL/SQL handler.
2112 -- This procedure is the wrapper procedure of process_response_internal()
2113 --Parameters:
2114 --IN:
2115 -- Standard workflow IN parameters
2116 --OUT:
2117 -- Standard workflow OUT parameters
2118 --Testing:
2119 --
2120 --End of Comments
2121 -------------------------------------------------------------------------------
2122
2123 PROCEDURE process_response_app_forward(
2124 itemtype IN VARCHAR2,
2125 itemkey IN VARCHAR2,
2126 actid IN NUMBER,
2127 funcmode IN VARCHAR2,
2128 resultout OUT NOCOPY VARCHAR2)
2129 IS
2130 l_progress VARCHAR2(3) := '000';
2131 l_parent_item_type wf_items.parent_item_type%TYPE;
2132 l_parent_item_key wf_items.parent_item_key%TYPE;
2133 l_child_approver_empid NUMBER;
2134 l_child_approver_user_name wf_users.name%TYPE;
2135 l_child_approver_display_name wf_users.display_name%TYPE;
2136 l_api_name VARCHAR2(500) := 'process_response_app_forward';
2137 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
2138
2139 BEGIN
2140
2141 IF (funcmode <> wf_engine.eng_run) THEN
2142 resultout := wf_engine.eng_null;
2143 RETURN;
2144 END IF;
2145
2146 --Set the global attributes in the po wrapper function
2147 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
2148 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
2149
2150 l_progress := '010';
2151 IF (g_po_wf_debug = 'Y') THEN
2152 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
2153 END IF;
2154
2155 -- Logic :
2156 -- + Get parent_itemp_type and parent_item_key.
2157 -- + Call process_response_internal with action 'APPROVE and FORWARD'. It will update ame with status and forwadee record.
2158 -- + Set workflow attribute AME_SUB_APPROVAL_RESPONSE to APPROVE AND FORWARD' of parent.
2159 -- + Fetch required current approver related workflow attributes as child attributes.
2160 -- + Populate parent workflow attributes FORWARD_FROM_ID, FORWARD_FROM_USER_NAME, FORWARD_FROM_DISP_NAME
2161 -- APPROVER_EMPID, APPROVER_USER_NAME, APPROVER_DISPLAY_NAME with child approver attributes.
2162
2163 SELECT parent_item_type, parent_item_key
2164 INTO l_parent_item_type, l_parent_item_key
2165 FROM wf_items
2166 WHERE item_type = itemtype
2167 AND item_key = itemkey;
2168
2169 l_progress := '020';
2170 process_response_internal(itemtype, itemkey, 'APPROVE AND FORWARD');
2171 l_progress := '030';
2172
2173 IF (g_po_wf_debug = 'Y') THEN
2174 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Returned from process_response_internal');
2175 END IF;
2176
2177 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( aname => 'APPROVER_EMPID');
2178 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_USER_NAME');
2179 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_DISPLAY_NAME');
2180
2181 PO_WF_UTIL_PKG.G_ITEM_TYPE := l_parent_item_type;
2182 PO_WF_UTIL_PKG.G_ITEM_KEY := l_parent_item_key;
2183 po_wf_util_pkg.SetItemAttrText( aname => 'AME_SUB_APPROVAL_RESPONSE', avalue => 'APPROVE_AND_FORWARD');
2184 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_ID', avalue => l_child_approver_empid );
2185 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_USER_NAME', avalue => l_child_approver_user_name);
2186 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_DISP_NAME', avalue => l_child_approver_display_name);
2187 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_EMPID', avalue => l_child_approver_empid );
2188 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_USER_NAME', avalue => l_child_approver_user_name );
2189 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_DISPLAY_NAME', avalue => l_child_approver_display_name );
2190
2191 IF (g_po_wf_debug = 'Y') THEN
2192 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Process_Response_App_Forward completed');
2193 END IF;
2194
2195 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
2196 RETURN;
2197
2198 END Process_Response_App_Forward;
2199
2200 --------------------------------------------------------------------------------
2201 --Start of Comments
2202 --Name: process_response_approve
2203 --Pre-reqs:
2204 -- None.
2205 --Modifies:
2206 -- None.
2207 --Locks:
2208 -- None.
2209 --Function:
2210 -- Workflow activity PL/SQL handler.
2211 -- This procedure is the wrapper procedure of process_response_internal()
2212 --Parameters:
2213 --IN:
2214 -- Standard workflow IN parameters
2215 --OUT:
2216 -- Standard workflow OUT parameters
2217 --Testing:
2218 --
2219 --End of Comments
2220 -------------------------------------------------------------------------------
2221 PROCEDURE process_response_approve(
2222 itemtype IN VARCHAR2,
2223 itemkey IN VARCHAR2,
2224 actid IN NUMBER,
2225 funcmode IN VARCHAR2,
2226 resultout OUT NOCOPY VARCHAR2)
2227 IS
2228 l_progress VARCHAR2(3) := '000';
2229 l_parent_item_type wf_items.parent_item_type%TYPE;
2230 l_parent_item_key wf_items.parent_item_key%TYPE;
2231 l_child_approver_empid NUMBER;
2232 l_child_approver_user_name wf_users.name%TYPE;
2233 l_child_approver_display_name wf_users.display_name%TYPE;
2234 l_api_name VARCHAR2(500) := 'process_response_approve';
2235 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
2236
2237 BEGIN
2238
2239 IF (funcmode <> wf_engine.eng_run) THEN
2240 resultout := wf_engine.eng_null;
2241 RETURN;
2242 END IF;
2243
2244 --Set the global attributes in the po wrapper function
2245 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
2246 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
2247
2248 l_progress := '010';
2249 IF (g_po_wf_debug = 'Y') THEN
2250 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
2251 END IF;
2252
2253 -- Logic :
2254 -- + Get parent_itemp_type and parent_item_key.
2255 -- + Call process_response_internal with action 'APPROVE'. It will update ame with status and forwadee record.
2256 -- + Set workflow attribute AME_SUB_APPROVAL_RESPONSE to APPROVE' of parent.
2257 -- + Fetch required current approver related workflow attributes as child attributes.
2258 -- + Populate parent workflow attributes FORWARD_FROM_ID, FORWARD_FROM_USER_NAME, FORWARD_FROM_DISP_NAME
2259 -- APPROVER_EMPID, APPROVER_USER_NAME, APPROVER_DISPLAY_NAME with child approver attributes.
2260
2261 SELECT parent_item_type, parent_item_key
2262 INTO l_parent_item_type, l_parent_item_key
2263 FROM wf_items
2264 WHERE item_type = itemtype
2265 AND item_key = itemkey;
2266
2267 l_progress := '020';
2268 process_response_internal(itemtype, itemkey, 'APPROVE');
2269 l_progress := '030';
2270
2271 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( aname => 'APPROVER_EMPID');
2272 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_USER_NAME');
2273 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_DISPLAY_NAME');
2274
2275 PO_WF_UTIL_PKG.G_ITEM_TYPE := l_parent_item_type;
2276 PO_WF_UTIL_PKG.G_ITEM_KEY := l_parent_item_key;
2277 po_wf_util_pkg.SetItemAttrText( aname => 'AME_SUB_APPROVAL_RESPONSE', avalue => 'APPROVE');
2278 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_ID', avalue => l_child_approver_empid );
2279 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_USER_NAME', avalue => l_child_approver_user_name);
2280 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_DISP_NAME', avalue => l_child_approver_display_name);
2281 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_EMPID', avalue => l_child_approver_empid );
2282 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_USER_NAME', avalue => l_child_approver_user_name );
2283 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_DISPLAY_NAME', avalue => l_child_approver_display_name );
2284
2285 l_progress := '040';
2286 IF (g_po_wf_debug = 'Y') THEN
2287 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Completed');
2288 END IF;
2289
2290 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
2291 RETURN;
2292
2293 END Process_Response_Approve;
2294
2295 --------------------------------------------------------------------------------
2296 --Start of Comments
2297 --Name: process_response_reject
2298 --Pre-reqs:
2299 -- None.
2300 --Modifies:
2301 -- None.
2302 --Locks:
2303 -- None.
2304 --Function:
2305 -- Workflow activity PL/SQL handler.
2306 -- This procedure is the wrapper procedure of process_response_internal()
2307 --Parameters:
2308 --IN:
2309 -- Standard workflow IN parameters
2310 --OUT:
2311 -- Standard workflow OUT parameters
2312 --Testing:
2313 --
2314 --End of Comments
2315 -------------------------------------------------------------------------------
2316 PROCEDURE process_response_reject(
2317 itemtype IN VARCHAR2,
2318 itemkey IN VARCHAR2,
2319 actid IN NUMBER,
2320 funcmode IN VARCHAR2,
2321 resultout OUT NOCOPY VARCHAR2)
2322 IS
2323 l_progress VARCHAR2(3) := '000';
2324 l_parent_item_type wf_items.parent_item_type%TYPE;
2325 l_parent_item_key wf_items.parent_item_key%TYPE;
2326 l_child_approver_empid NUMBER;
2327 l_child_approver_user_name wf_users.name%TYPE;
2328 l_child_approver_display_name wf_users.display_name%TYPE;
2329 l_api_name VARCHAR2(500) := 'process_response_reject';
2330 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
2331
2332 BEGIN
2333
2334 IF (funcmode <> wf_engine.eng_run) THEN
2335 resultout := wf_engine.eng_null;
2336 RETURN;
2337 END IF;
2338
2339 --Set the global attributes in the po wrapper function
2340 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
2341 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
2342
2343 l_progress := '010';
2344 IF (g_po_wf_debug = 'Y') THEN
2345 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
2346 END IF;
2347
2348 -- Logic:
2349 -- + Get parent_itemp_type and parent_item_key.
2350 -- + Call process_response_internal with action 'REJECT'. It will update ame with status and forwadee record.
2351 -- + Set workflow attribute AME_SUB_APPROVAL_RESPONSE to REJECT' of parent.
2352 -- + Fetch required current approver related workflow attributes as child attributes.
2353 -- + Populate parent workflow attributes FORWARD_FROM_ID, FORWARD_FROM_USER_NAME, FORWARD_FROM_DISP_NAME
2354 -- APPROVER_EMPID, APPROVER_USER_NAME, APPROVER_DISPLAY_NAME with child approver attributes.
2355
2356 l_progress := '020';
2357 process_response_internal(itemtype, itemkey, 'REJECT');
2358 l_progress := '030';
2359
2360 SELECT parent_item_type, parent_item_key
2361 INTO l_parent_item_type, l_parent_item_key
2362 FROM wf_items
2363 WHERE item_type = itemtype
2364 AND item_key = itemkey;
2365
2366 l_progress := '040';
2367
2368 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( aname => 'APPROVER_EMPID');
2369 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_USER_NAME');
2370 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_DISPLAY_NAME');
2371
2372 PO_WF_UTIL_PKG.G_ITEM_TYPE := l_parent_item_type;
2373 PO_WF_UTIL_PKG.G_ITEM_KEY := l_parent_item_key;
2374 po_wf_util_pkg.SetItemAttrText( aname => 'AME_SUB_APPROVAL_RESPONSE', avalue => 'REJECT');
2375 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_ID', avalue => l_child_approver_empid );
2376 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_USER_NAME', avalue => l_child_approver_user_name);
2377 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_DISP_NAME', avalue => l_child_approver_display_name);
2378 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_EMPID', avalue => l_child_approver_empid );
2379 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_USER_NAME', avalue => l_child_approver_user_name );
2380 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_DISPLAY_NAME', avalue => l_child_approver_display_name );
2381
2382 l_progress := '050';
2383 IF (g_po_wf_debug = 'Y') THEN
2384 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Completed');
2385 END IF;
2386
2387 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
2388 RETURN;
2389
2390 END Process_Response_Reject;
2391
2392 --------------------------------------------------------------------------------
2393 --Start of Comments
2394 --Name: process_response_forward
2395 --Pre-reqs:
2396 -- None.
2397 --Modifies:
2398 -- None.
2399 --Locks:
2400 -- None.
2401 --Function:
2402 -- Workflow activity PL/SQL handler.
2403 -- This procedure is the wrapper procedure of process_response_internal()
2404 --Parameters:
2405 --IN:
2406 -- Standard workflow IN parameters
2407 --OUT:
2408 -- Standard workflow OUT parameters
2409 --Testing:
2410 --
2411 --End of Comments
2412 -------------------------------------------------------------------------------
2413 PROCEDURE process_response_forward(
2414 itemtype IN VARCHAR2,
2415 itemkey IN VARCHAR2,
2416 actid IN NUMBER,
2417 funcmode IN VARCHAR2,
2418 resultout OUT NOCOPY VARCHAR2)
2419 IS
2420 l_progress VARCHAR2(3) := '000';
2421 l_parent_item_type wf_items.parent_item_type%TYPE;
2422 l_parent_item_key wf_items.parent_item_key%TYPE;
2423 l_child_approver_empid NUMBER;
2424 l_child_approver_user_name wf_users.name%TYPE;
2425 l_child_approver_display_name wf_users.display_name%TYPE;
2426 l_api_name VARCHAR2(500) := 'process_response_forward';
2427 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
2428 BEGIN
2429
2430 IF (funcmode <> wf_engine.eng_run) THEN
2431 resultout := wf_engine.eng_null;
2432 RETURN;
2433 END IF;
2434
2435 --Set the global attributes in the po wrapper function
2436 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
2437 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
2438
2439 l_progress := '010';
2440 IF (g_po_wf_debug = 'Y') THEN
2441 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
2442 END IF;
2443
2444 -- Logic:
2445 -- + Get parent_itemp_type and parent_item_key.
2446 -- + Call process_response_internal with action 'FORWARD'. It will update ame with status and forwadee record.
2447 -- + Set workflow attribute AME_SUB_APPROVAL_RESPONSE to 'FORWARD' of parent.
2448 -- + Fetch required current approver related workflow attributes as child attributes.
2449 -- + Populate parent workflow attributes FORWARD_FROM_ID, FORWARD_FROM_USER_NAME, FORWARD_FROM_DISP_NAME
2450 -- APPROVER_EMPID, APPROVER_USER_NAME, APPROVER_DISPLAY_NAME with child approver attributes.
2451
2452 SELECT parent_item_type, parent_item_key
2453 INTO l_parent_item_type, l_parent_item_key
2454 FROM wf_items
2455 WHERE item_type = itemtype
2456 AND item_key = itemkey;
2457
2458 l_progress := '020';
2459 process_response_internal(itemtype, itemkey, 'FORWARD');
2460 l_progress := '030';
2461
2462 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( aname => 'APPROVER_EMPID');
2463 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_USER_NAME');
2464 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_DISPLAY_NAME');
2465
2466 PO_WF_UTIL_PKG.G_ITEM_TYPE := l_parent_item_type;
2467 PO_WF_UTIL_PKG.G_ITEM_KEY := l_parent_item_key;
2468 po_wf_util_pkg.SetItemAttrText( aname => 'AME_SUB_APPROVAL_RESPONSE', avalue => 'FORWARD');
2469 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_ID', avalue => l_child_approver_empid );
2470 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_USER_NAME', avalue => l_child_approver_user_name);
2471 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_DISP_NAME', avalue => l_child_approver_display_name);
2472 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_EMPID', avalue => l_child_approver_empid );
2473 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_USER_NAME', avalue => l_child_approver_user_name );
2474 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_DISPLAY_NAME', avalue => l_child_approver_display_name );
2475
2476 l_progress := '040';
2477 IF (g_po_wf_debug = 'Y') THEN
2478 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Completed');
2479 END IF;
2480
2481 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
2482 RETURN;
2483
2484 END Process_Response_Forward;
2485
2486 --------------------------------------------------------------------------------
2487 --Start of Comments
2488 --Name: process_response_timeout
2489 --Pre-reqs:
2490 -- None.
2491 --Modifies:
2492 -- None.
2493 --Locks:
2494 -- None.
2495 --Function:
2496 -- Workflow activity PL/SQL handler.
2497 -- This procedure is the wrapper procedure of process_response_internal()
2498 --Parameters:
2499 --IN:
2500 -- Standard workflow IN parameters
2501 --OUT:
2502 -- Standard workflow OUT parameters
2503 --Testing:
2504 --
2505 --End of Comments
2506 -------------------------------------------------------------------------------
2507 PROCEDURE process_response_timeout(
2508 itemtype IN VARCHAR2,
2509 itemkey IN VARCHAR2,
2510 actid IN NUMBER,
2511 funcmode IN VARCHAR2,
2512 resultout OUT NOCOPY VARCHAR2)
2513 IS
2514 l_progress VARCHAR2(3) := '000';
2515 l_parent_item_type wf_items.parent_item_type%TYPE;
2516 l_parent_item_key wf_items.parent_item_key%TYPE;
2517 l_child_approver_empid NUMBER;
2518 l_child_approver_user_name wf_users.name%TYPE;
2519 l_child_approver_display_name wf_users.display_name%TYPE;
2520 l_api_name VARCHAR2(500) := 'process_response_timeout';
2521 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
2522
2523 BEGIN
2524
2525 IF (funcmode <> wf_engine.eng_run) THEN
2526 resultout := wf_engine.eng_null;
2527 RETURN;
2528 END IF;
2529
2530 --Set the global attributes in the po wrapper function
2531 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
2532 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
2533
2534 l_progress := '010';
2535 IF (g_po_wf_debug = 'Y') THEN
2536 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
2537 END IF;
2538
2539 -- Logic:
2540 -- + Get parent_itemp_type and parent_item_key.
2541 -- + Call process_response_internal with action 'TIMEOUT'. It will update ame with status and forwadee record.
2542 -- + Set workflow attribute AME_SUB_APPROVAL_RESPONSE to 'TIMEOUT' of parent.
2543 -- + Fetch required current approver related workflow attributes as child attributes.
2544 -- + Populate parent workflow attributes FORWARD_FROM_ID, FORWARD_FROM_USER_NAME, FORWARD_FROM_DISP_NAME
2545 -- APPROVER_EMPID, APPROVER_USER_NAME, APPROVER_DISPLAY_NAME with child approver attributes.
2546
2547 process_response_internal(itemtype, itemkey, 'TIMEOUT');
2548 l_progress := '020';
2549
2550 SELECT parent_item_type, parent_item_key
2551 INTO l_parent_item_type, l_parent_item_key
2552 FROM wf_items
2553 WHERE item_type = itemtype
2554 AND item_key = itemkey;
2555
2556 l_progress := '030';
2557
2558 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( aname => 'APPROVER_EMPID');
2559 l_child_approver_user_name := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_USER_NAME');
2560 l_child_approver_display_name := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_DISPLAY_NAME');
2561
2562 PO_WF_UTIL_PKG.G_ITEM_TYPE := l_parent_item_type;
2563 PO_WF_UTIL_PKG.G_ITEM_KEY := l_parent_item_key;
2564 po_wf_util_pkg.SetItemAttrText( aname => 'AME_SUB_APPROVAL_RESPONSE', avalue => 'TIMEOUT');
2565 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_ID', avalue => l_child_approver_empid );
2566 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_USER_NAME', avalue => l_child_approver_user_name);
2567 po_wf_util_pkg.SetItemAttrText( aname => 'FORWARD_FROM_DISP_NAME', avalue => l_child_approver_display_name);
2568 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_EMPID', avalue => l_child_approver_empid );
2569 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_USER_NAME', avalue => l_child_approver_user_name );
2570 po_wf_util_pkg.SetItemAttrText( aname => 'APPROVER_DISPLAY_NAME', avalue => l_child_approver_display_name );
2571
2572 l_progress := '040';
2573
2574 wf_engine.CompleteActivity (itemtype => l_parent_item_type,
2575 itemkey => l_parent_item_key,
2576 activity => 'BLOCK',
2577 result => NULL);
2578
2579 IF (g_po_wf_debug = 'Y') THEN
2580 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Completed');
2581 END IF;
2582
2583 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
2584 RETURN;
2585
2586 END Process_Response_Timeout;
2587
2588 --------------------------------------------------------------------------------
2589 --Start of Comments
2590 --Name: increment_no_reminder_attr
2591 --Pre-reqs:
2592 -- None.
2593 --Modifies:
2594 -- None.
2595 --Locks:
2596 -- None.
2597 --Function:
2598 -- Workflow activity PL/SQL handler.
2599 -- This procedure is use to increment workflow attibute no_reminder and track
2600 -- only two reminders are received by an approver.
2601 --Parameters:
2602 --IN:
2603 -- Standard workflow IN parameters
2604 --OUT:
2605 -- Standard workflow OUT parameters
2606 --Testing:
2607 --
2608 --End of Comments
2609 -------------------------------------------------------------------------------
2610 PROCEDURE increment_no_reminder_attr(
2611 itemtype IN VARCHAR2,
2612 itemkey IN VARCHAR2,
2613 actid IN NUMBER,
2614 funcmode IN VARCHAR2,
2615 resultout OUT NOCOPY VARCHAR2)
2616 IS
2617 l_no_reminder NUMBER;
2618 l_progress VARCHAR2(3) := '000';
2619 l_api_name VARCHAR2(500) := 'increment_no_reminder_attr';
2620 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
2621
2622 BEGIN
2623
2624 IF (funcmode <> wf_engine.eng_run) THEN
2625 resultout := wf_engine.eng_null;
2626 RETURN;
2627 END IF;
2628
2629 --Set the global attributes in the po wrapper function
2630 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
2631 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
2632
2633 l_progress := '010';
2634 IF (g_po_wf_debug = 'Y') THEN
2635 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
2636 END IF;
2637
2638 l_no_reminder := po_wf_util_pkg.GetItemAttrNumber( aname => 'NO_REMINDER');
2639 IF (l_no_reminder < 2) THEN
2640 l_no_reminder := l_no_reminder + 1;
2641 END IF;
2642 po_wf_util_pkg.SetItemAttrNumber( aname => 'NO_REMINDER', avalue => l_no_reminder );
2643
2644 l_progress := '020';
2645
2646 IF l_no_reminder = 1 THEN
2647 po_wf_util_pkg.SetItemAttrText( aname => 'REMINDER_TEXT', avalue => 'First Reminder' );
2648 ELSIF l_no_reminder = 2 THEN
2649 po_wf_util_pkg.SetItemAttrText( aname => 'REMINDER_TEXT', avalue => 'Second Reminder' );
2650 END IF;
2651
2652 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
2653 RETURN;
2654
2655 END Increment_No_Reminder_Attr;
2656
2657 --------------------------------------------------------------------------------
2658 --Start of Comments
2659 --Name: post_approval_notif
2660 --Pre-reqs:
2661 -- None.
2662 --Modifies:
2663 -- None.
2664 --Locks:
2665 -- None.
2666 --Function:
2667 -- Workflow activity PL/SQL handler.
2668 -- This procedure is use to increment workflow attibute no_reminder and track
2669 -- only two reminders are received by an approver.
2670 --Parameters:
2671 --IN:
2672 -- Standard workflow IN parameters
2673 --OUT:
2674 -- Standard workflow OUT parameters
2675 --Testing:
2676 --
2677 --End of Comments
2678 -------------------------------------------------------------------------------
2679 PROCEDURE post_approval_notif(
2680 itemtype IN VARCHAR2,
2681 itemkey IN VARCHAR2,
2682 actid IN NUMBER,
2683 funcmode IN VARCHAR2,
2684 resultout OUT NOCOPY VARCHAR2)
2685 IS
2686 l_responder_id fnd_user.user_id%TYPE;
2687 l_session_user_id NUMBER;
2688 l_session_resp_id NUMBER;
2689 l_session_appl_id NUMBER;
2690 l_preparer_resp_id NUMBER;
2691 l_preparer_appl_id NUMBER;
2692 l_preserved_ctx VARCHAR2(5);
2693 l_nid NUMBER;
2694 l_progress VARCHAR2(3);
2695 l_action po_action_history.action_code%TYPE := NULL;
2696 l_new_recipient_id wf_roles.orig_system_id%TYPE;
2697 l_current_recipient_id wf_roles.orig_system_id%TYPE;
2698 l_origsys wf_roles.orig_system%TYPE;
2699 l_document_id NUMBER;
2700 l_revision_num NUMBER;
2701 l_draft_id NUMBER;
2702 l_org_id NUMBER;
2703 l_document_type po_document_types.document_type_code%TYPE;
2704 l_document_subtype po_document_types_all_b.document_subtype%TYPE;
2705 l_approval_group_id NUMBER:='';
2706 l_original_recipient wf_notifications.original_recipient%TYPE;
2707 l_current_recipient_role wf_notifications.recipient_role%TYPE;
2708 l_api_name VARCHAR2(500) := 'post_approval_notif';
2709 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
2710
2711 BEGIN
2712
2713 --Set the global attributes in the po wrapper function
2714 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
2715 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
2716
2717 l_nid := wf_engine.context_nid;
2718 po_wf_util_pkg.SetItemAttrNumber( aname => 'NOTIFICATION_ID', avalue => l_nid);
2719
2720 l_progress := '010';
2721 IF (g_po_wf_debug = 'Y') THEN
2722 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
2723 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||' funcmode:'||funcmode);
2724 END IF;
2725
2726 IF (funcmode IN ('FORWARD','QUESTION','ANSWER', 'TIMEOUT') )THEN
2727
2728 --Determine the action
2729 IF (funcmode = 'FORWARD') THEN
2730 l_action := 'DELEGATE';
2731 ELSIF (funcmode = 'QUESTION') THEN
2732 l_action := 'QUESTION';
2733 ELSIF (funcmode = 'ANSWER') THEN
2734 l_action := 'ANSWER';
2735 ELSIF (funcmode = 'TIMEOUT') THEN
2736 l_action := 'TIMED OUT';
2737 END IF;
2738
2739 l_progress := '020';
2740 IF (g_po_wf_debug = 'Y') THEN
2741 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_action:'||l_action);
2742 END IF;
2743
2744 IF(l_action IS NOT NULL) THEN
2745 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
2746 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
2747 l_org_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'ORG_ID');
2748 l_draft_id := po_wf_util_pkg.GetItemAttrText( aname => 'DRAFT_ID');
2749 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText ( aname => 'DOCUMENT_SUBTYPE');
2750 l_approval_group_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'APPROVAL_GROUP_ID');
2751 l_revision_num := po_wf_util_pkg.GetItemAttrNumber( aname => 'REVISION_NUMBER');
2752
2753 --If function mode in 'FORWARD','QUESTION','ANSWER', then fetch l_new_recipient_id from wf_engine.context_new_role.
2754 --Else fetch it from original_recipient.
2755 IF (funcmode <> 'TIMEOUT') THEN
2756
2757 l_progress := '030';
2758 wf_directory.GetRoleOrigSysInfo (wf_engine.context_new_role, l_origsys, l_new_recipient_id);
2759
2760 ELSE
2761
2762 l_progress := '040';
2763 BEGIN
2764 SELECT original_recipient,
2765 DECODE(more_info_role, NULL, recipient_role, more_info_role)
2766 INTO l_original_recipient, l_current_recipient_role
2767 FROM wf_notifications
2768 WHERE notification_id = WF_ENGINE.context_nid
2769 AND (more_info_role IS NOT NULL OR recipient_role <> original_recipient );
2770 EXCEPTION
2771 WHEN OTHERS THEN
2772 l_original_recipient := NULL;
2773 END;
2774
2775 IF l_original_recipient IS NOT NULL THEN
2776 Wf_Directory.GetRoleOrigSysInfo(l_original_recipient, l_origsys, l_new_recipient_id);
2777 END IF;
2778
2779 END IF;
2780
2781 l_progress := '050';
2782 IF (g_po_wf_debug = 'Y') THEN
2783 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||
2784 l_progress||' l_new_recipient_id:'||l_new_recipient_id);
2785 END IF;
2786
2787 --We should not be allowing the delegation of a notication to a user who is not an employee.
2788 IF ((funcmode = 'FORWARD') AND (l_origsys <> 'PER')) THEN
2789 fnd_message.set_name ('PO' ,'PO_INVALID_USER_FOR_REASSIGN');
2790 app_exception.raise_exception;
2791 END IF;
2792
2793 l_progress := '060';
2794
2795 --Fetch the current recepient id
2796 IF (funcmode = 'ANSWER') THEN
2797 wf_directory.getroleorigsysinfo (wf_engine.context_more_info_role, l_origsys, l_current_recipient_id);
2798 ELSIF (funcmode = 'TIMEOUT') THEN
2799 Wf_Directory.GetRoleOrigSysInfo(l_current_recipient_role, l_origsys, l_current_recipient_id);
2800 ELSE
2801 wf_directory.getroleorigsysinfo (wf_engine.context_recipient_role, l_origsys, l_current_recipient_id);
2802 END IF;
2803
2804 l_progress := '070';
2805 IF (g_po_wf_debug = 'Y') THEN
2806 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
2807 ||' l_current_recipient_id:'||l_current_recipient_id);
2808 END IF;
2809
2810 IF (funcmode = 'FORWARD' ) THEN
2811 po_wf_util_pkg.SetItemAttrNumber( aname => 'APPROVER_EMPID', avalue => l_new_recipient_id);
2812 END IF;
2813
2814 l_progress := '080';
2815
2816 IF l_new_recipient_id IS NOT NULL THEN
2817 --Update po_action_history NULL record against current approver/l_current_recipient_id with l_action.
2818 UpdateActionHistoryPoAme(
2819 p_document_id => l_document_id,
2820 p_draft_id => l_draft_id,
2821 p_document_type => l_document_type,
2822 p_document_subtype => l_document_subtype,
2823 p_action => l_action,
2824 p_note => wf_engine.context_user_comment,
2825 p_current_approver => l_current_recipient_id );
2826 --Insert null action record into action_history for l_new_recipient_id
2827 InsertActionHistoryPoAme(
2828 p_document_id => l_document_id,
2829 p_draft_id => l_draft_id,
2830 p_document_type => l_document_type,
2831 p_document_subtype => l_document_subtype,
2832 p_revision_num => l_revision_num,
2833 p_employee_id => l_new_recipient_id,
2834 p_approval_group_id => l_approval_group_id,
2835 p_action => NULL );
2836 END IF;
2837
2838 l_progress := '090';
2839
2840 IF (funcmode <> 'TIMEOUT') THEN
2841 resultout := wf_engine.eng_completed || ':' || wf_engine.eng_null;
2842 END IF;
2843 RETURN;
2844
2845 END IF; --IF(l_action IS NOT NULL) THEN
2846 END IF; --IF (funcmode IN ('FORWARD','QUESTION','ANSWER', 'TIMEOUT') )THEN
2847
2848 -- Preserve the response context
2849 IF (funcmode = 'RESPOND') THEN
2850
2851 l_progress := '100';
2852 l_nid := wf_engine.context_nid;
2853 po_wf_util_pkg.SetItemAttrNumber( aname => 'NOTIFICATION_ID', avalue => l_nid);
2854
2855 SELECT fu.user_id
2856 INTO l_responder_id
2857 FROM fnd_user fu,
2858 wf_notifications wfn
2859 WHERE wfn.notification_id = l_nid
2860 AND wfn.original_recipient = fu.user_name;
2861
2862 IF (wf_engine.preserved_context = TRUE) THEN
2863 l_preserved_ctx := 'TRUE';
2864 ELSE
2865 l_preserved_ctx := 'FALSE';
2866 END IF;
2867
2868 l_progress := '110';
2869 IF (g_po_wf_debug = 'Y') THEN
2870 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_preserved_ctx:'||l_preserved_ctx);
2871 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_responder_id:'||l_responder_id);
2872 END IF;
2873
2874 l_session_user_id := fnd_global.user_id;
2875 l_session_resp_id := fnd_global.resp_id;
2876 l_session_appl_id := fnd_global.resp_appl_id;
2877
2878 IF (l_session_user_id = - 1) THEN
2879 l_session_user_id := NULL;
2880 END IF;
2881
2882 IF (l_session_resp_id = - 1) THEN
2883 l_session_resp_id := NULL;
2884 END IF;
2885
2886 IF (l_session_appl_id = - 1) THEN
2887 l_session_appl_id := NULL;
2888 END IF;
2889
2890 l_progress := '120';
2891 IF (g_po_wf_debug = 'Y') THEN
2892 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_session_user_id:'||l_session_user_id);
2893 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_session_resp_id:'||l_session_resp_id);
2894 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_session_appl_id:'||l_session_appl_id);
2895 END IF;
2896
2897 l_preparer_resp_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'RESPONSIBILITY_ID');
2898 l_preparer_appl_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'APPLICATION_ID');
2899
2900 l_progress := '130';
2901 IF (g_po_wf_debug = 'Y') THEN
2902 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_preparer_resp_id:'||l_preparer_resp_id);
2903 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_preparer_appl_id:'||l_preparer_appl_id);
2904 END IF;
2905
2906 l_progress := '140';
2907 IF (l_responder_id IS NOT NULL) THEN
2908
2909 IF (l_responder_id <> l_session_user_id) THEN
2910 --Possible in 2 scenarios:
2911 -- 1. when the response is made from email using guest user feature
2912 -- 2. When the response is made from sysadmin login
2913 -- In this case capture the session user with preparer resp and appl id
2914 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_USER_ID' ,avalue => l_responder_id);
2915 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_RESP_ID' ,avalue => l_preparer_resp_id);
2916 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_APPL_ID' ,avalue => l_preparer_appl_id);
2917 ELSE
2918 -- Possible when the response is made from the default worklist without choosing a valid responsibility
2919 -- In this case also capture the session user with preparer resp and appl id
2920 IF (l_session_resp_id IS NULL) THEN
2921 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_USER_ID' ,avalue => l_responder_id);
2922 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_RESP_ID' ,avalue => l_preparer_resp_id);
2923 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_APPL_ID' ,avalue => l_preparer_appl_id);
2924 ELSE
2925 -- All values available - Possible when the response is made after choosing a correct responsibility
2926 IF (l_preserved_ctx = 'TRUE') THEN
2927 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_USER_ID' ,avalue => l_responder_id);
2928 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_RESP_ID' ,avalue => l_session_resp_id);
2929 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_APPL_ID' ,avalue => l_session_appl_id);
2930 ELSE
2931 -- The current session is a background session. So cannot depend on the session resp and appl ids.
2932 -- Need to depend on the preparer resp and appl ids
2933 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_USER_ID' ,avalue => l_responder_id);
2934 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_RESP_ID' ,avalue => l_preparer_resp_id);
2935 po_wf_util_pkg.SetItemAttrNumber( aname => 'RESPONDER_APPL_ID' ,avalue => l_preparer_appl_id);
2936 END IF; --IF (l_preserved_ctx = 'TRUE') THEN
2937
2938 END IF; --IF (l_session_resp_id IS NULL) THEN
2939 END IF; --IF (l_responder_id <> l_session_user_id) THEN
2940 END IF; --IF (l_responder_id IS NOT NULL) THEN
2941
2942 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
2943
2944 l_progress := '150';
2945 IF (g_po_wf_debug = 'Y') THEN
2946 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Completed');
2947 END IF;
2948
2949 RETURN;
2950
2951 END IF; --IF (funcmode = 'RESPOND') THEN
2952
2953 IF (funcmode = 'TRANSFER') THEN
2954 fnd_message.set_name ('PO','PO_WF_NOTIF_NO_TRANSFER');
2955 app_exception.raise_exception;
2956 resultout := wf_engine.eng_completed;
2957 RETURN;
2958 END IF;
2959
2960 END post_approval_notif;
2961
2962 --------------------------------------------------------------------------------
2963 --Start of Comments
2964 --Name: generate_pdf_ame_buyer
2965 --Pre-reqs:
2966 -- None.
2967 --Modifies:
2968 -- None.
2969 --Locks:
2970 -- None.
2971 --Function:
2972 -- Workflow activity PL/SQL handler.
2973 -- This procedure is used to generate pdf for buyer with or without terms and conditions.
2974 --Parameters:
2975 --IN:
2976 -- Standard workflow IN parameters
2977 --OUT:
2978 -- Standard workflow OUT parameters
2979 --Testing:
2980 --
2981 --End of Comments
2982 -------------------------------------------------------------------------------
2983 PROCEDURE generate_pdf_ame_buyer(
2984 itemtype IN VARCHAR2,
2985 itemkey IN VARCHAR2,
2986 actid IN NUMBER,
2987 funcmode IN VARCHAR2,
2988 resultout OUT NOCOPY VARCHAR2)
2989 IS
2990 l_document_id NUMBER;
2991 l_document_subtype po_headers.type_lookup_code%TYPE;
2992 l_header_id NUMBER;
2993 l_document_type po_headers.type_lookup_code%TYPE;
2994 l_revision_num NUMBER;
2995 l_request_id NUMBER;
2996 l_conterm_exists po_headers_all.conterms_exist_flag%TYPE;
2997 l_authorization_status VARCHAR2(25);
2998 l_progress VARCHAR2(200);
2999 l_withterms VARCHAR2(1);
3000 l_set_lang BOOLEAN;
3001 l_language_code fnd_languages.language_code%TYPE;
3002 l_language fnd_languages.nls_language%TYPE;
3003 l_territory fnd_languages.nls_territory%TYPE;
3004 submission_error EXCEPTION;
3005 l_msg VARCHAR2(500);
3006 l_terms_param VARCHAR2(1);
3007 l_api_name VARCHAR2(500) := 'generate_pdf_ame_buyer';
3008 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
3009
3010 BEGIN
3011
3012 IF (funcmode <> wf_engine.eng_run) THEN
3013 resultout := wf_engine.eng_null;
3014 RETURN;
3015 END IF;
3016
3017 --Set the global attributes in the po wrapper function
3018 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
3019 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
3020
3021 l_progress := '010';
3022 IF (g_po_wf_debug = 'Y') THEN
3023 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
3024 END IF;
3025
3026 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText (aname => 'DOCUMENT_TYPE');
3027 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText (aname => 'DOCUMENT_SUBTYPE');
3028 l_document_id := po_wf_util_pkg.GetItemAttrNumber (aname => 'DOCUMENT_ID');
3029 l_revision_num := po_wf_util_pkg.GetItemAttrNumber (aname => 'REVISION_NUMBER');
3030 l_authorization_status := PO_WF_UTIL_PKG.GetItemAttrText(aname => 'AUTHORIZATION_STATUS');
3031 l_withterms := PO_WF_UTIL_PKG.GetItemAttrText (aname => 'WITH_TERMS');
3032 l_language_code := PO_WF_UTIL_PKG.GetItemAttrText(aname=>'LANGUAGE_CODE');
3033
3034 IF l_document_type IN ('PO', 'PA') AND l_document_subtype IN ('STANDARD', 'BLANKET', 'CONTRACT') THEN
3035 l_header_id := l_document_id;
3036 END IF;
3037
3038 --Get the language code
3039 l_progress := '020';
3040 BEGIN
3041 SELECT nls_language, nls_territory
3042 INTO l_language, l_territory
3043 FROM fnd_languages
3044 WHERE language_code = l_language_code;
3045 EXCEPTION
3046 WHEN NO_DATA_FOUND THEN
3047 IF (g_po_wf_debug = 'Y') THEN
3048 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Error when fetching language:'||sqlerrm);
3049 END IF;
3050 END;
3051
3052 IF l_withterms IN ('Y','T') THEN
3053 l_withterms := 'Y';
3054 ELSE
3055 l_withterms := 'N';
3056 END IF;
3057
3058 --Set the language preference
3059 l_set_lang := fnd_request.set_options('NO', 'NO', l_language, l_territory, NULL, FND_PROFILE.VALUE('ICX_NUMERIC_CHARACTERS'));
3060 po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
3061 l_request_id := fnd_request.submit_request( 'PO',
3062 'POXPOPDF',
3063 NULL,
3064 NULL,
3065 FALSE,
3066 'R', --P_report_type
3067 NULL , --P_agend_id
3068 NULL, --P_po_num_from
3069 NULL , --P_po_num_to
3070 NULL , --P_relaese_num_from
3071 NULL , --P_release_num_to
3072 NULL , --P_date_from
3073 NULL , --P_date_to
3074 NULL , --P_approved_flag
3075 'N', --P_test_flag
3076 NULL , --P_print_releases
3077 NULL , --P_sortby
3078 NULL , --P_user_id
3079 NULL , --P_fax_enable
3080 NULL , --P_fax_number
3081 NULL , --P_BLANKET_LINES
3082 'View', --View_or_Communicate,
3083 l_withterms, --P_WITHTERMS
3084 'Y', --P_storeFlag
3085 'N', --P_PRINT_FLAG
3086 l_document_id, --P_DOCUMENT_ID
3087 l_revision_num, --P_REVISION_NUM
3088 l_authorization_status, --P_AUTHORIZATION_STATUS
3089 l_document_subtype, --P_DOCUMENT_TYPE
3090 NULL, -- P_PO_TEMPLATE_CODE
3091 NULL, -- P_CONTRACT_TEMPLATE_CODE
3092 fnd_global.local_chr(0),
3093 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3094 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3095 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3096 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3097 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3098 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3099 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3100 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3101 NULL);
3102
3103 IF (g_po_wf_debug = 'Y') THEN
3104 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_request_id:'||l_request_id);
3105 END IF;
3106
3107 --If the request id is NULL or 0, then there is a submission check error.
3108 IF (l_request_id <= 0 OR l_request_id IS NULL) THEN
3109 RAISE SUBMISSION_ERROR;
3110 END IF;
3111
3112 po_wf_util_pkg.SetItemAttrNumber (aname => 'REQUEST_ID', avalue => l_request_id);
3113
3114 EXCEPTION
3115 WHEN submission_error THEN
3116 l_msg := fnd_message.get;
3117 IF (g_po_wf_debug = 'Y') THEN
3118 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Exception when submitting the request');
3119 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' fnd_message:'||l_msg);
3120 END IF;
3121 WF_CORE.context(g_pkg_name, l_api_name, l_progress, l_msg);
3122 RAISE;
3123 WHEN OTHERS THEN
3124 IF (g_po_wf_debug = 'Y') THEN
3125 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Exception:'||sqlerrm);
3126 END IF;
3127 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
3128 RAISE;
3129 END generate_pdf_ame_buyer;
3130
3131 --------------------------------------------------------------------------------
3132 --Start of Comments
3133 --Name: generate_pdf_ame_supp
3134 --Pre-reqs:
3135 -- None.
3136 --Modifies:
3137 -- None.
3138 --Locks:
3139 -- None.
3140 --Function:
3141 -- Workflow activity PL/SQL handler.
3142 -- This procedure is used to generate pdf for supplier in supplier's launguage along with Attachments.zip
3143 -- with or without terms and conditions.
3144 --Parameters:
3145 --IN:
3146 -- Standard workflow IN parameters
3147 --OUT:
3148 -- Standard workflow OUT parameters
3149 --Testing:
3150 --
3151 --End of Comments
3152 -------------------------------------------------------------------------------
3153 PROCEDURE generate_pdf_ame_supp(
3154 itemtype IN VARCHAR2,
3155 itemkey IN VARCHAR2,
3156 actid IN NUMBER,
3157 funcmode IN VARCHAR2,
3158 resultout OUT NOCOPY VARCHAR2)
3159 IS
3160 l_document_id po_headers.po_header_id%TYPE;
3161 l_revision_num po_headers.revision_num%TYPE;
3162 l_document_subtype po_headers.type_lookup_code%TYPE;
3163 l_document_type po_headers.type_lookup_code%TYPE;
3164 l_territory fnd_languages.nls_territory%type;
3165 l_language_code fnd_languages.language_code%type;
3166 l_supp_lang po_vendor_sites_all.language%TYPE;
3167 l_language fnd_languages.nls_language%type;
3168 l_authorization_status po_headers.authorization_status%TYPE;
3169 l_header_id po_headers.po_header_id%TYPE;
3170 l_with_terms VARCHAR2(1);
3171 l_request_id NUMBER;
3172 l_set_lang BOOLEAN;
3173 l_progress VARCHAR2(100);
3174 submission_error EXCEPTION;
3175 l_msg VARCHAR2(500);
3176 l_terms_param VARCHAR2(1);
3177 l_attachments_exist VARCHAR2(1); -- Holds 'Y' if there are any supplier file attachments
3178 l_duplicate_filenames VARCHAR2(1); -- Holds 'Y' if there are any supplier file attachments with same filename
3179 l_error_flag NUMBER; -- Determines if the error condition (same file namebut different file lengths has been met or not)
3180 l_max_attachment_size po_system_parameters_all.max_attachment_size%type;
3181 l_filename_new fnd_lobs.file_name%type;
3182 l_length NUMBER;
3183 l_length_new NUMBER;
3184 l_filename fnd_lobs.file_name%type;
3185 l_api_name VARCHAR2(500) := 'generate_pdf_ame_supp';
3186 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
3187
3188 CURSOR l_get_po_attachments_csr(l_po_header_id NUMBER) IS
3189 SELECT fl.file_name, dbms_lob.getlength(fl.file_data)
3190 FROM fnd_documents d,
3191 fnd_attached_documents ad,
3192 fnd_doc_category_usages dcu,
3193 fnd_attachment_functions af,
3194 fnd_lobs fl
3195 WHERE ( (ad.pk1_value = TO_CHAR(l_po_header_id) AND ad.entity_name = 'PO_HEADERS') OR
3196 (ad.pk1_value = (SELECT To_Char(vendor_id)
3197 FROM po_headers_all
3198 WHERE po_header_id = l_po_header_id) AND ad.entity_name = 'PO_VENDORS') OR
3199 (ad.pk1_value IN (SELECT To_Char(po_line_id)
3200 FROM po_lines_all
3201 WHERE po_header_id = l_po_header_id ) AND ad.entity_name = 'PO_LINES') OR
3202 (ad.pk1_value IN (SELECT To_Char(from_header_id)
3203 FROM po_lines_all
3204 WHERE po_header_id = l_po_header_id
3205 AND from_header_id IS NOT NULL ) AND ad.entity_name = 'PO_HEADERS') OR
3206 (ad.pk1_value IN (SELECT To_Char(from_line_id)
3207 FROM po_lines_all
3208 WHERE po_header_id = l_po_header_id
3209 AND from_line_id IS NOT NULL ) AND ad.entity_name = 'PO_LINES') OR
3210 (ad.pk1_value IN (SELECT To_Char(line_location_id)
3211 FROM po_line_locations_all
3212 WHERE po_header_id = l_po_header_id
3213 AND shipment_type IN ('PRICE BREAK', 'STANDARD', 'PREPAYMENT')) AND ad.entity_name = 'PO_SHIPMENTS') OR
3214 (ad.pk2_value IN (SELECT To_Char(item_id)
3215 FROM po_lines_all
3216 WHERE po_header_id = l_po_header_id
3217 AND TO_CHAR(PO_COMMUNICATION_PVT.getInventoryOrgId()) = ad.pk1_value
3218 AND item_id IS NOT NULL ) AND ad.entity_name = 'MTL_SYSTEM_ITEMS') )
3219 AND d.document_id = ad.document_id
3220 AND dcu.category_id = d.category_id
3221 AND dcu.attachment_function_id = af.attachment_function_id
3222 AND d.datatype_id = 6
3223 AND af.function_name = 'PO_PRINTPO'
3224 AND d.media_id = fl.file_id
3225 AND dcu.enabled_flag = 'Y'
3226 GROUP BY fl.file_name, dbms_lob.getlength(fl.file_data)
3227 ORDER BY fl.file_name;
3228
3229 BEGIN
3230 IF (funcmode <> wf_engine.eng_run) THEN
3231 resultout := wf_engine.eng_null;
3232 RETURN;
3233 END IF;
3234
3235 --Set the global attributes in the po wrapper function
3236 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
3237 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
3238
3239 l_progress := '010';
3240 IF (g_po_wf_debug = 'Y') THEN
3241 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
3242 END IF;
3243
3244 l_document_type := PO_WF_UTIL_PKG.GetItemAttrText (aname => 'DOCUMENT_TYPE');
3245 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText (aname => 'DOCUMENT_SUBTYPE');
3246 l_document_id := po_wf_util_pkg.GetItemAttrNumber (aname => 'DOCUMENT_ID');
3247 l_revision_num := po_wf_util_pkg.GetItemAttrNumber (aname => 'REVISION_NUMBER');
3248 l_language_code := PO_WF_UTIL_PKG.GetItemAttrText (aname => 'LANGUAGE_CODE');
3249 l_with_terms := PO_WF_UTIL_PKG.GetItemAttrText (aname => 'WITH_TERMS');
3250 l_authorization_status := PO_WF_UTIL_PKG.GetItemAttrText(aname => 'AUTHORIZATION_STATUS');
3251
3252 IF l_document_type IN ('PO', 'PA') AND l_document_subtype IN ('STANDARD', 'BLANKET', 'CONTRACT') THEN
3253 l_header_id := l_document_id;
3254 END IF;
3255
3256 l_progress := '020';
3257
3258 BEGIN
3259 SELECT pv.language
3260 INTO l_supp_lang
3261 FROM po_vendor_sites_all pv, po_headers_all ph
3262 WHERE ph.po_header_id = l_header_id
3263 AND ph.vendor_site_id = pv.vendor_site_id;
3264 EXCEPTION
3265 WHEN NO_DATA_FOUND THEN
3266 IF (g_po_wf_debug = 'Y') THEN
3267 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3268 ||' Error while fetching vendor site language:'||sqlerrm);
3269 END IF;
3270 END;
3271
3272 l_progress := '030';
3273 BEGIN
3274 SELECT nls_language
3275 INTO l_language
3276 FROM fnd_languages
3277 WHERE language_code = l_language_code;
3278 EXCEPTION
3279 WHEN NO_DATA_FOUND THEN
3280 IF (g_po_wf_debug = 'Y') THEN
3281 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3282 ||' Error while fetching the language code:'||sqlerrm);
3283 END IF;
3284 END;
3285
3286 /*-------------------------------------------------------------------------
3287 In the whole of Zip generation process, all unexpected exceptions must
3288 be handled and none should be raised to the workflow because that will
3289 stop the workflow process would prevent sending the error notification.
3290
3291 In case of any unexpected exceptions, the exception should be handled
3292 and workflow attribute ZIP_ERROR_CODE should be set to 'UNEXPECTED' so
3293 that corresponding error notification can be sent to buyer and supplier.
3294
3295 Also in case of exception, l_max_attachment_size should be set to 0 so
3296 that Zip file is not generated.
3297 -------------------------------------------------------------------------*/
3298 BEGIN
3299 -- Get the 'Maximum Attachment Size' value from Purchasing Options
3300 -- A value of 0 means Zip Attachments are not supported
3301 l_progress := '040';
3302 l_max_attachment_size := PO_COMMUNICATION_PVT.get_max_zip_size(itemtype, itemkey);
3303
3304 IF l_max_attachment_size > 0 THEN
3305 -- If PO has no 'To Supplier' file attachments then 'Zip Attachment' link
3306 -- should not show up in the notifications and Zip file should not be generated
3307 l_attachments_exist := 'N';
3308
3309 IF (g_po_wf_debug = 'Y') THEN
3310 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Checking for supplier file attachments');
3311 END IF;
3312
3313 BEGIN
3314 l_attachments_exist := PO_COMMUNICATION_PVT.check_for_attachments(p_document_type => l_document_type, p_document_id => l_document_id);
3315 EXCEPTION
3316 WHEN no_data_found THEN
3317 IF (g_po_wf_debug = 'Y') THEN
3318 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' No Supplier file attachments');
3319 END IF;
3320 l_max_attachment_size := 0; --No need to generate the pdf
3321 END;
3322
3323 IF (g_po_wf_debug = 'Y') THEN
3324 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_attachments_exist:'||l_attachments_exist);
3325 END IF;
3326
3327 IF l_attachments_exist = 'Y' THEN
3328 l_progress := '050';
3329 po_wf_util_pkg.SetItemAttrText (aname => 'ZIP_ATTACHMENT',
3330 avalue => 'PLSQLBLOB:PO_COMMUNICATION_PVT.ZIP_ATTACH/' || itemtype || ':' || itemkey);
3331
3332 /*---------------------------------------------------------------------------------
3333 An error condition is when two or more file attachments have the same file name
3334 but different file sizes. In this case a zip error notification should be sent
3335 and zip file should not be generated.
3336 Following two cases are ok:
3337 1. There are no duplicate file names in the PO Attachments
3338 2. Files with same name also have the same sizes
3339 Case 1 would be most common and is given highest priority in terms of performance.
3340 So a separate query for finding duplicate file names is written. If no duplicate
3341 file names then cursors for checking the error condition are not opened.
3342 ---------------------------------------------------------------------------------*/
3343 IF (g_po_wf_debug = 'Y') THEN
3344 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Check for duplicate filenames');
3345 END IF;
3346
3347 l_duplicate_filenames := 'N';
3348
3349 BEGIN
3350 SELECT 'Y'
3351 INTO l_duplicate_filenames
3352 FROM dual
3353 WHERE EXISTS ( SELECT fl.file_name
3354 FROM fnd_documents d,
3355 fnd_attached_documents ad,
3356 fnd_doc_category_usages dcu,
3357 fnd_attachment_functions af,
3358 fnd_lobs fl
3359 WHERE ( (ad.pk1_value = TO_CHAR(l_document_id) AND ad.entity_name = 'PO_HEADERS') OR
3360 --
3361 (ad.pk1_value = TO_CHAR((SELECT vendor_id
3362 FROM po_headers_all
3363 WHERE po_header_id = l_document_id))
3364 AND ad.entity_name = 'PO_VENDORS') OR
3365 --
3366 (ad.pk1_value IN (SELECT po_line_id
3367 FROM po_lines_all
3368 WHERE po_header_id = l_document_id)
3369 AND ad.entity_name = 'PO_LINES') OR
3370 --
3371 (ad.pk1_value IN (SELECT from_header_id
3372 FROM po_lines_all
3373 WHERE po_header_id = l_document_id
3374 AND from_header_id IS NOT NULL) AND ad.entity_name = 'PO_HEADERS') OR
3375 --
3376 (ad.pk1_value IN (SELECT from_line_id
3377 FROM po_lines_all
3378 WHERE po_header_id = l_document_id
3379 AND from_line_id IS NOT NULL) AND ad.entity_name = 'PO_LINES') OR
3380 --
3381 (ad.pk1_value IN (SELECT line_location_id
3382 FROM po_line_locations_all
3383 WHERE po_header_id = l_document_id
3384 AND shipment_type IN ('PRICE BREAK', 'STANDARD', 'PREPAYMENT'))
3385 AND ad.entity_name = 'PO_SHIPMENTS') OR
3386 --
3387 (ad.pk2_value IN (SELECT item_id
3388 FROM po_lines_all
3389 WHERE po_header_id = l_document_id
3390 AND TO_CHAR(PO_COMMUNICATION_PVT.getInventoryOrgId()) = ad.pk1_value
3391 AND item_id IS NOT NULL) AND ad.entity_name = 'MTL_SYSTEM_ITEMS'))
3392 AND d.document_id = ad.document_id
3393 AND dcu.category_id = d.category_id
3394 AND dcu.attachment_function_id = af.attachment_function_id
3395 AND d.datatype_id = 6
3396 AND af.function_name = 'PO_PRINTPO'
3397 AND d.media_id = fl.file_id
3398 AND dcu.enabled_flag = 'Y'
3399 GROUP BY fl.file_name
3400 HAVING COUNT(*)>1);
3401
3402 --If no_data_found then let l_duplicate_filename remain 'N'
3403 --so that cursor is not opened. All other exceptions raised
3404 --until caught by outer exception handler
3405 EXCEPTION
3406 WHEN NO_DATA_FOUND THEN
3407 IF (g_po_wf_debug = 'Y') THEN
3408 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' No duplicate attachments');
3409 END IF;
3410 END;
3411
3412 l_progress := '060';
3413
3414 IF l_duplicate_filenames = 'Y' THEN
3415 IF (g_po_wf_debug = 'Y') THEN
3416 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Duplicate filenames found');
3417 END IF;
3418
3419 --Loop through the ordered records to verify if the same attachment has same different content
3420 OPEN l_get_po_attachments_csr(l_document_id);
3421 l_error_flag := 0;
3422
3423 LOOP
3424 FETCH l_get_po_attachments_csr INTO l_filename_new, l_length_new;
3425
3426 EXIT WHEN (l_get_po_attachments_csr%notfound);
3427
3428 IF (l_filename_new = l_filename AND l_length_new <> l_length) THEN
3429 l_error_flag := 1;
3430 EXIT;
3431 END IF;
3432
3433 l_filename := l_filename_new;
3434 l_length := l_length_new;
3435 END LOOP;
3436
3437 CLOSE l_get_po_attachments_csr;
3438
3439 IF (g_po_wf_debug = 'Y') THEN
3440 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_error_flag:'||l_error_flag);
3441 END IF;
3442
3443 IF l_error_flag = 1 THEN
3444 PO_COMMUNICATION_PVT.set_zip_error_code(itemtype, itemkey, 'DUPLICATE_FILENAME');
3445 l_max_attachment_size := 0; --No need to generate the pdf
3446 END IF;
3447 END IF; --IF l_duplicate_filenames = 'Y'
3448 END IF; --IF l_attachments_exist = 'Y'
3449 END IF; --IF l_max_attachment_size > 0
3450
3451 EXCEPTION
3452 WHEN OTHERS THEN
3453 IF (g_po_wf_debug = 'Y') THEN
3454 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||'Exception when detecting duplicates');
3455 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' sqlerrm:'||sqlerrm);
3456 END IF;
3457 PO_COMMUNICATION_PVT.set_zip_error_code(itemtype, itemkey, 'UNEXPECTED');
3458 l_max_attachment_size := 0; --No need to generate the pdf
3459 END;
3460
3461 l_progress := '070';
3462 -- Submit the request to generate the zip file
3463 IF l_with_terms IN ('Y','T') THEN
3464 l_terms_param := 'Y';
3465 ELSE
3466 l_terms_param := 'N';
3467 END IF;
3468
3469 l_progress := '080';
3470 IF l_language <> l_supp_lang THEN
3471 SELECT nls_territory
3472 INTO l_territory
3473 FROM fnd_languages
3474 WHERE nls_language = l_supp_lang;
3475
3476 l_set_lang := fnd_request.set_options('NO', 'NO', l_supp_lang, l_territory, NULL, FND_PROFILE.VALUE('ICX_NUMERIC_CHARACTERS'));
3477 po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
3478
3479 l_progress := '090';
3480
3481 l_request_id := fnd_request.submit_request( 'PO',
3482 'POXPOPDF',
3483 NULL,
3484 NULL,
3485 FALSE,
3486 'R', --P_report_type
3487 NULL , --P_agend_id
3488 NULL, --P_po_num_from
3489 NULL , --P_po_num_to
3490 NULL , --P_relaese_num_from
3491 NULL , --P_release_num_to
3492 NULL , --P_date_from
3493 NULL , --P_date_to
3494 NULL , --P_approved_flag
3495 'N', --P_test_flag
3496 NULL , --P_print_releases
3497 NULL , --P_sortby
3498 NULL , --P_user_id
3499 NULL , --P_fax_enable
3500 NULL , --P_fax_number
3501 NULL , --P_BLANKET_LINES
3502 'View', --View_or_Communicate,
3503 l_terms_param, --P_WITHTERMS
3504 'Y', --P_storeFlag
3505 'N', --P_PRINT_FLAG
3506 l_document_id, --P_DOCUMENT_ID
3507 l_revision_num, --P_REVISION_NUM
3508 l_authorization_status, --P_AUTHORIZATION_STATUS
3509 l_document_subtype, --P_DOCUMENT_TYPE
3510 l_max_attachment_size, --P_max_zip_size
3511 NULL, -- P_PO_TEMPLATE_CODE
3512 NULL, -- P_CONTRACT_TEMPLATE_CODE
3513 fnd_global.local_chr(0),
3514 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3515 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3516 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3517 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3518 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3519 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3520 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3521 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3522 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3523 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
3524 NULL, NULL);
3525
3526 l_progress := '100';
3527 IF (g_po_wf_debug = 'Y') THEN
3528 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_request_id:'||l_request_id);
3529 END IF;
3530
3531 IF (l_request_id <= 0 OR l_request_id IS NULL) THEN
3532 RAISE SUBMISSION_ERROR;
3533 END IF;
3534
3535 po_wf_util_pkg.SetItemAttrNumber( aname => 'REQUEST_ID', avalue => l_request_id);
3536
3537 END IF;
3538
3539 EXCEPTION
3540 WHEN SUBMISSION_ERROR THEN
3541 l_msg := fnd_message.get;
3542 IF (g_po_wf_debug = 'Y') THEN
3543 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Exception when submitting the request');
3544 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' fnd_message:'||l_msg);
3545 END IF;
3546 WF_CORE.context(g_pkg_name, l_api_name, l_progress, l_msg);
3547 RAISE;
3548
3549 WHEN OTHERS THEN
3550 IF (g_po_wf_debug = 'Y') THEN
3551 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Exception:'||sqlerrm);
3552 END IF;
3553 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
3554 RAISE;
3555
3556 END generate_pdf_ame_supp;
3557
3558 --------------------------------------------------------------------------------
3559 --Start of Comments
3560 --Name: forward_unable_to_reserve
3561 --Pre-reqs:
3562 -- None.
3563 --Modifies:
3564 -- None.
3565 --Locks:
3566 -- None.
3567 --Function:
3568 -- Workflow activity PL/SQL handler.
3569 -- This procedure is used to set workflow attributes in case approver uses forward
3570 -- for Unable to Reserve Notification
3571 --Parameters:
3572 --IN:
3573 -- Standard workflow IN parameters
3574 --OUT:
3575 -- Standard workflow OUT parameters
3576 --Testing:
3577 --
3578 --End of Comments
3579 -------------------------------------------------------------------------------
3580
3581 PROCEDURE forward_unable_to_reserve(
3582 itemtype IN VARCHAR2,
3583 itemkey IN VARCHAR2,
3584 actid IN NUMBER,
3585 funcmode IN VARCHAR2,
3586 resultout OUT NOCOPY VARCHAR2)
3587 IS
3588 l_forward_to_username_response VARCHAR2(60) := NULL;
3589 l_document_id NUMBER;
3590 l_document_type VARCHAR2(25):='';
3591 l_document_subtype VARCHAR2(25):='';
3592 l_revision_num NUMBER;
3593 l_note VARCHAR2(4000);
3594 l_org_id NUMBER;
3595 l_current_approver NUMBER;
3596 l_draft_id NUMBER;
3597 l_progress VARCHAR2(3);
3598 l_approval_group_id NUMBER;
3599 l_api_name VARCHAR2(500) := 'forward_unable_to_reserve';
3600 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
3601
3602 BEGIN
3603 IF (funcmode <> wf_engine.eng_run) THEN
3604 resultout := wf_engine.eng_null;
3605 RETURN;
3606 END IF;
3607
3608 --Set the global attributes in the po wrapper function
3609 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
3610 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
3611
3612 l_progress := '010';
3613 IF (g_po_wf_debug = 'Y') THEN
3614 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
3615 END IF;
3616
3617 l_forward_to_username_response := po_wf_util_pkg.GetItemAttrText( aname => 'FORWARD_TO_USERNAME_RESPONSE');
3618 po_wf_util_pkg.SetItemAttrText ( aname => 'APPROVER_USER_NAME', avalue => l_forward_to_username_response);
3619
3620 IF (g_po_wf_debug = 'Y') THEN
3621 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3622 ||' l_forward_to_username_response:'||l_forward_to_username_response);
3623 END IF;
3624
3625 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( aname=>'APPROVER_EMPID');
3626 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
3627 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DRAFT_ID');
3628 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
3629 l_document_subtype := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_SUBTYPE');
3630 l_note := po_wf_util_pkg.GetItemAttrText( aname => 'NOTE');
3631 l_approval_group_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'APPROVAL_GROUP_ID');
3632 l_revision_num := po_wf_util_pkg.GetItemAttrNumber( aname => 'REVISION_NUMBER');
3633
3634 IF l_org_id IS NOT NULL THEN
3635 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
3636 END IF;
3637
3638 IF (g_po_wf_debug = 'Y') THEN
3639 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_current_approver:'||l_current_approver);
3640 END IF;
3641
3642 l_progress := '020';
3643
3644 InsertActionHistoryPoAme(
3645 p_document_id => l_document_id,
3646 p_draft_id => l_draft_id,
3647 p_document_type => l_document_type,
3648 p_document_subtype => l_document_subtype,
3649 p_revision_num => l_revision_num,
3650 p_employee_id => l_current_approver,
3651 p_approval_group_id => l_approval_group_id,
3652 p_action => 'FORWARD');
3653
3654 l_progress := '030';
3655
3656 InsertActionHistoryPoAme(
3657 p_document_id => l_document_id,
3658 p_draft_id => l_draft_id,
3659 p_document_type => l_document_type,
3660 p_document_subtype => l_document_subtype,
3661 p_revision_num => l_revision_num,
3662 p_employee_id => l_current_approver,
3663 p_approval_group_id => l_approval_group_id,
3664 p_action => NULL);
3665
3666 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
3667
3668 END forward_unable_to_reserve;
3669
3670 --------------------------------------------------------------------------------
3671 --Name: process_beat_by_first
3672 --Pre-reqs:
3673 -- None.
3674 --Modifies:
3675 -- None.
3676 --Locks:
3677 -- None.
3678 --Function:
3679 -- Workflow activity PL/SQL handler.
3680 -- This procedure handles the stopping of workflow and the updating of the
3681 -- action history table in the case of approvers being beat by first
3682 -- responder.
3683 --Parameters:
3684 --IN:
3685 -- Standard workflow IN parameters
3686 --OUT:
3687 -- Standard workflow OUT parameters
3688 --Testing:
3689 --
3690 --End of Comments
3691 -------------------------------------------------------------------------------
3692 PROCEDURE process_beat_by_first(
3693 itemtype IN VARCHAR2,
3694 itemkey IN VARCHAR2,
3695 actid IN NUMBER,
3696 funcmode IN VARCHAR2,
3697 resultout OUT NOCOPY VARCHAR2)
3698 IS
3699 l_progress VARCHAR2(3) := '000';
3700 l_parent_item_type wf_items.parent_item_type%TYPE;
3701 l_parent_item_key wf_items.parent_item_key%TYPE;
3702 l_child_approver_empid NUMBER;
3703 l_child_approver_groupid NUMBER;
3704 l_approver_group_id NUMBER;
3705 l_po_header_id NUMBER;
3706 l_process_out VARCHAR2(10);
3707 l_approver_list ame_util.approversTable2;
3708 ameTransactionType po_document_types.ame_transaction_type%TYPE;
3709 l_response_action VARCHAR2(20);
3710 l_note VARCHAR2(4000);
3711 l_person_id NUMBER;
3712 l_orig_system VARCHAR2(3);
3713 l_orig_system_id NUMBER;
3714 l_preparer_user_name fnd_user.user_name%TYPE;
3715 l_doc_string VARCHAR2(200);
3716 l_ame_exception ame_util.longestStringType;
3717 l_approver_response VARCHAR2(20);
3718 l_transaction_type po_document_types.ame_transaction_type%TYPE;
3719 l_ame_transaction_id NUMBER;
3720 l_document_type po_document_types.document_type_code%TYPE;
3721 l_document_subtype po_document_types.document_subtype%TYPE;
3722 l_approver_category VARCHAR2(20);
3723 l_api_name VARCHAR2(500) := 'process_beat_by_first';
3724 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
3725 l_approver_disp_name VARCHAR2(200);
3726
3727 CURSOR l_child_wf ( itemtype IN wf_items.parent_item_type%TYPE,
3728 itemkey IN wf_items.parent_item_key%TYPE ) IS
3729 SELECT wfi.item_type,
3730 wfi.item_key,
3731 wfn.recipient_role,
3732 wfn.original_recipient
3733 FROM wf_items wfi,
3734 wf_item_activity_statuses wfias,
3735 wf_notifications wfn
3736 WHERE wfi.parent_item_key = itemkey
3737 AND wfi.item_type = itemtype
3738 AND wfias.item_type = wfi.item_type
3739 AND wfias.item_key = wfi.item_key
3740 AND wfias.activity_status = 'NOTIFIED'
3741 AND wfias.notification_id IS NOT NULL
3742 AND wfias.notification_id = wfn.notification_id;
3743
3744 l_child_wf_cur l_child_wf%ROWTYPE;
3745 l_draft_id NUMBER;
3746
3747 BEGIN
3748
3749 IF (funcmode <> wf_engine.eng_run) THEN
3750 resultout := wf_engine.eng_null;
3751 RETURN;
3752 END IF;
3753
3754 --Set the global attributes in the po wrapper function
3755 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
3756 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
3757
3758 l_progress := '010';
3759 IF (g_po_wf_debug = 'Y') THEN
3760 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
3761 END IF;
3762
3763 SELECT parent_item_type, parent_item_key
3764 INTO l_parent_item_type, l_parent_item_key
3765 FROM wf_items
3766 WHERE item_type = itemtype
3767 AND item_key = itemkey;
3768
3769 --Check if there we have encountered any ame exception.
3770 --If the value of ame_exception is not null, then we have faced some exception.
3771 --So just comlete the block activity and return
3772
3773 l_approver_group_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'APPROVAL_GROUP_ID');
3774 l_po_header_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
3775 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DRAFT_ID');
3776 l_approver_response := po_wf_util_pkg.GetItemAttrText( aname => 'APPROVER_RESPONSE');
3777 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
3778 l_document_subtype := PO_WF_UTIL_PKG.GetItemAttrText( aname => 'DOCUMENT_SUBTYPE');
3779 l_ame_transaction_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'AME_TRANSACTION_ID');
3780 l_transaction_type := po_wf_util_pkg.GetItemAttrText( aname => 'AME_TRANSACTION_TYPE');
3781 l_approver_category := po_wf_util_pkg.GetItemAttrText ( aname => 'APPROVER_CATEGORY');
3782 l_approver_disp_name := po_wf_util_pkg.GetItemAttrText ( aname => 'APPROVER_DISPLAY_NAME');
3783
3784 l_progress := '020';
3785
3786 IF l_approver_response = 'APPROVED' THEN
3787 IF (g_po_wf_debug = 'Y') THEN
3788 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_ame_transaction_id:'||l_ame_transaction_id);
3789 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_transaction_type:'||l_transaction_type);
3790 END IF;
3791
3792 IF l_approver_category = 'REVIEWER' THEN
3793 fnd_message.set_name('PO', 'PO_ALREADY_REVIEW_ACCEPTED');
3794 ELSIF l_approver_category = 'ESIGNER' THEN
3795 fnd_message.set_name('PO', 'PO_ALREADY_SIGNED');
3796 ELSE
3797 fnd_message.set_name('PO', 'PO_ALREADY_APPROVED');
3798 END IF;
3799
3800 fnd_message.set_token('PERSON_NAME', l_approver_disp_name);
3801 l_note := fnd_message.get;
3802
3803 ame_api2.getAllApprovers7 ( applicationIdIn => applicationId,
3804 transactionIdIn => l_ame_transaction_id,
3805 transactionTypeIn => l_transaction_type,
3806 approvalProcessCompleteYNOut => l_process_out,
3807 approversOut => l_approver_list );
3808
3809 l_progress := '030';
3810 -- Once we get the approvers list from AME, we iterate through the approvers list,
3811 -- to find out the current first authority approver.
3812
3813 FOR i IN 1.. l_approver_list.count LOOP
3814 IF (g_po_wf_debug = 'Y') THEN
3815 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3816 ||' Index:'||TO_CHAR(i));
3817 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3818 ||' orig_system:'|| l_approver_list(i).orig_system);
3819 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3820 ||' orig_system_id:'|| l_approver_list(i).orig_system_id);
3821 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3822 ||' authority:'|| l_approver_list(i).authority);
3823 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3824 ||' approval_status:'|| l_approver_list(i).approval_status);
3825 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3826 ||' api_insertion:'|| l_approver_list(i).api_insertion);
3827 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3828 ||' group_or_chain_id:'|| l_approver_list(i).group_or_chain_id);
3829 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3830 ||' beatByFirstResponderStatus:'||ame_util.beatByFirstResponderStatus);
3831 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3832 ||' oamGenerated:'||ame_util.oamGenerated);
3833 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3834 ||' l_approver_group_id:'||l_approver_group_id);
3835 END IF;
3836
3837 l_progress := '040';
3838 IF ( l_approver_list(i).approval_status = ame_util.beatByFirstResponderStatus
3839 AND l_approver_list(i).api_insertion = ame_util.oamGenerated
3840 AND l_approver_list(i).group_or_chain_id = l_approver_group_id) THEN
3841
3842 l_orig_system := l_approver_list(i).orig_system;
3843 l_orig_system_id := l_approver_list(i).orig_system_id;
3844
3845 IF ( l_orig_system = ame_util.perOrigSystem) THEN
3846 -- Employee Supervisor Record.
3847 l_person_id := l_orig_system_id;
3848
3849 ELSIF ( l_orig_system = ame_util.posOrigSystem) THEN
3850 -- Position Hierarchy Record.
3851 BEGIN
3852 -----------------------------------------------------------------------
3853 -- SQL What: Get the person assigned to position returned by AME.
3854 -- SQL Why : When AME returns position id, then using this sql we find
3855 -- one person assigned to this position and use this person
3856 -- as approver.
3857 -----------------------------------------------------------------------
3858 SELECT person_id
3859 INTO l_person_id
3860 FROM ( SELECT person.person_id
3861 FROM per_all_people_f person,
3862 per_all_assignments_f asg,
3863 wf_users wu
3864 WHERE asg.position_id = l_orig_system_id
3865 AND wu.orig_system = ame_util.perorigsystem
3866 AND wu.orig_system_id = person.person_id
3867 AND TRUNC(SYSDATE) BETWEEN person.effective_start_date AND NVL(person.effective_end_date, TRUNC( SYSDATE) )
3868 AND person.person_id = asg.person_id
3869 AND asg.primary_flag = 'Y'
3870 AND asg.assignment_type IN ( 'E', 'C' )
3871 AND ( person.current_employee_flag = 'Y' OR person.current_npw_flag = 'Y' )
3872 AND asg.assignment_status_type_id NOT IN
3873 (SELECT assignment_status_type_id
3874 FROM per_assignment_status_types
3875 WHERE per_system_status = 'TERM_ASSIGN' )
3876 AND TRUNC(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date
3877 ORDER BY person.last_name )
3878 WHERE ROWNUM = 1;
3879 EXCEPTION
3880 WHEN NO_DATA_FOUND THEN
3881 l_person_id := -1;
3882 END;
3883
3884 ELSIF (l_orig_system = ame_util.fndUserOrigSystem) THEN
3885 --FND User Record.
3886 SELECT employee_id
3887 INTO l_person_id
3888 FROM fnd_user
3889 WHERE user_id = l_orig_system_id
3890 AND TRUNC(SYSDATE) BETWEEN start_date AND NVL(end_date, SYSDATE + 1);
3891
3892 END IF; --l_orig_system =
3893
3894 l_progress := '050';
3895 IF (g_po_wf_debug = 'Y') THEN
3896 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' l_person_id:'||l_person_id);
3897 END IF;
3898
3899 OPEN l_child_wf(l_parent_item_type, l_parent_item_key);
3900 LOOP
3901
3902 FETCH l_child_wf INTO l_child_wf_cur;
3903 EXIT WHEN l_child_wf%NOTFOUND;
3904
3905 l_child_approver_empid := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
3906 itemkey => l_child_wf_cur.item_key,
3907 aname => 'APPROVER_EMPID');
3908 l_child_approver_groupid := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
3909 itemkey => l_child_wf_cur.item_key,
3910 aname => 'APPROVAL_GROUP_ID');
3911 IF (g_po_wf_debug = 'Y') THEN
3912 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3913 ||' l_child_approver_empid:'||l_child_approver_empid);
3914 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3915 ||' l_child_approver_groupid:'||l_child_approver_groupid);
3916 END IF;
3917
3918 IF ( ( l_child_approver_empid = l_person_id OR
3919 ( l_child_wf_cur.recipient_role <> l_child_wf_cur.original_recipient) ) AND
3920 l_child_approver_groupid = l_approver_group_id ) THEN
3921
3922 IF (g_po_wf_debug = 'Y') THEN
3923 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress
3924 ||' l_child_wf_cur.item_key:'||l_child_wf_cur.item_key);
3925 END IF;
3926
3927 l_progress := '060';
3928 wf_engine.AbortProcess(l_child_wf_cur.item_type ,l_child_wf_cur.item_key);
3929
3930 END IF;
3931
3932 END LOOP; --l_child_wf
3933 CLOSE l_child_wf;
3934
3935 l_progress := '070';
3936 UpdateActionHistoryPoAme (
3937 p_document_id => l_po_header_id,
3938 p_draft_id => l_draft_id,
3939 p_document_type => l_document_type,
3940 p_document_subtype => l_document_subtype,
3941 p_action => 'NO ACTION',
3942 p_note => l_note,
3943 p_current_approver => l_person_id);
3944
3945 END IF; -- l_approver_list(i).approval_status = ame_util.beatByFirstResponderStatus
3946 END LOOP; -- l_approver_list.count
3947
3948 ELSIF (l_approver_response = 'REJECTED') THEN
3949
3950 l_progress := '080';
3951
3952 OPEN l_child_wf(l_parent_item_type, l_parent_item_key);
3953 LOOP
3954
3955 FETCH l_child_wf INTO l_child_wf_cur;
3956 EXIT WHEN l_child_wf%NOTFOUND;
3957
3958 -- Get the approver id as the person id to update the action history
3959 l_person_id := po_wf_util_pkg.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
3960 itemkey => l_child_wf_cur.item_key,
3961 aname => 'APPROVER_EMPID');
3962 IF (g_po_wf_debug = 'Y') THEN
3963 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' child item_key:'||l_child_wf_cur.item_key);
3964 END IF;
3965
3966 wf_engine.AbortProcess(l_child_wf_cur.item_type ,l_child_wf_cur.item_key);
3967
3968 -- update the action history table
3969 IF l_approver_category = 'REVIEWER' THEN
3970 fnd_message.set_name('PO', 'PO_ALREADY_REVIEW_REJECTED');
3971 ELSIF l_approver_category = 'ESIGNER' THEN
3972 fnd_message.set_name('PO', 'PO_ALREADY_SIGNER_REJECTED');
3973 ELSE
3974 fnd_message.set_name('PO', 'PO_ALREADY_REJECTED');
3975 END IF;
3976
3977 fnd_message.set_token('PERSON_NAME', l_approver_disp_name);
3978 l_note := fnd_message.get;
3979
3980 l_progress := '090';
3981
3982 UpdateActionHistoryPoAme (
3983 p_document_id => l_po_header_id,
3984 p_draft_id => l_draft_id,
3985 p_document_type => l_document_type,
3986 p_document_subtype => l_document_subtype,
3987 p_action => 'NO ACTION',
3988 p_note => l_note,
3989 p_current_approver => l_person_id);
3990
3991 l_progress := '100';
3992 END LOOP;
3993 CLOSE l_child_wf;
3994
3995 END IF; --IF l_approver_response = 'APPROVED'
3996
3997 l_progress := '110';
3998
3999 wf_engine.CompleteActivity(
4000 itemtype => l_parent_item_type,
4001 itemkey => l_parent_item_key,
4002 activity => 'BLOCK',
4003 result => NULL);
4004
4005 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
4006 RETURN;
4007
4008 EXCEPTION
4009 WHEN OTHERS THEN
4010 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
4011 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
4012 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
4013 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, l_progress, l_po_header_id);
4014 IF (g_po_wf_debug = 'Y') THEN
4015 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
4016 END IF;
4017 RAISE;
4018 END process_beat_by_first;
4019
4020 --------------------------------------------------------------------------------
4021 --Name: update_resp_verf_failed
4022 --Pre-reqs:
4023 -- None.
4024 --Modifies:
4025 -- None.
4026 --Locks:
4027 -- None.
4028 --Function: This procedure sets AME_SUB_APPROVAL_RESPONSE with FAILED_VERIFICATION. Document in this
4029 -- case is not supposed to returned.
4030 -- Workflow activity PL/SQL handler.
4031 --Parameters:
4032 --IN:
4033 -- Standard workflow IN parameters
4034 --OUT:
4035 -- Standard workflow OUT parameters
4036 --Testing:
4037 --
4038 --End of Comments
4039 -------------------------------------------------------------------------------
4040 PROCEDURE update_resp_verf_failed(
4041 itemtype IN VARCHAR2,
4042 itemkey IN VARCHAR2,
4043 actid IN NUMBER,
4044 funcmode IN VARCHAR2,
4045 resultout OUT NOCOPY VARCHAR2)
4046 IS
4047 l_progress VARCHAR2(3) := '000';
4048 l_parent_item_type wf_items.parent_item_type%TYPE;
4049 l_parent_item_key wf_items.parent_item_key%TYPE;
4050 l_api_name VARCHAR2(500) := 'update_resp_verf_failed';
4051 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
4052
4053 BEGIN
4054
4055 IF (funcmode <> wf_engine.eng_run) THEN
4056 resultout := wf_engine.eng_null;
4057 RETURN;
4058 END IF;
4059
4060 --Set the global attributes in the po wrapper function
4061 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
4062 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
4063
4064 l_progress := '010';
4065 IF (g_po_wf_debug = 'Y') THEN
4066 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
4067 END IF;
4068
4069 SELECT parent_item_type, parent_item_key
4070 INTO l_parent_item_type, l_parent_item_key
4071 FROM wf_items
4072 WHERE item_type = itemtype
4073 AND item_key = itemkey;
4074
4075 l_progress := '020';
4076
4077 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
4078 itemkey => l_parent_item_key,
4079 aname => 'AME_SUB_APPROVAL_RESPONSE',
4080 avalue => 'FAILED_VERIFICATION');
4081 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
4082
4083 END update_resp_verf_failed;
4084
4085 --------------------------------------------------------------------------------
4086 --Name: update_resp_verf_failed_reject
4087 --Pre-reqs:
4088 -- None.
4089 --Modifies:
4090 -- None.
4091 --Locks:
4092 -- None.
4093 --Function: This procedure sets AME_SUB_APPROVAL_RESPONSE with FALIED_VERIFICATION_REJECT.
4094 -- Document in this case is not supposed to rejected.
4095 -- Workflow activity PL/SQL handler.
4096 --Parameters:
4097 --IN:
4098 -- Standard workflow IN parameters
4099 --OUT:
4100 -- Standard workflow OUT parameters
4101 --Testing:
4102 --
4103 --End of Comments
4104 -------------------------------------------------------------------------------
4105 PROCEDURE update_resp_verf_failed_reject(
4106 itemtype IN VARCHAR2,
4107 itemkey IN VARCHAR2,
4108 actid IN NUMBER,
4109 funcmode IN VARCHAR2,
4110 resultout OUT NOCOPY VARCHAR2)
4111 IS
4112 l_progress VARCHAR2(3) := '000';
4113 l_parent_item_type wf_items.parent_item_type%TYPE;
4114 l_parent_item_key wf_items.parent_item_key%TYPE;
4115 l_api_name VARCHAR2(500) := 'update_resp_verf_failed_reject';
4116 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
4117
4118 BEGIN
4119
4120 IF (funcmode <> wf_engine.eng_run) THEN
4121 resultout := wf_engine.eng_null;
4122 RETURN;
4123 END IF;
4124
4125 --Set the global attributes in the po wrapper function
4126 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
4127 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
4128
4129 l_progress := '010';
4130 IF (g_po_wf_debug = 'Y') THEN
4131 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
4132 END IF;
4133
4134 SELECT parent_item_type, parent_item_key
4135 INTO l_parent_item_type, l_parent_item_key
4136 FROM wf_items
4137 WHERE item_type = itemtype
4138 AND item_key = itemkey;
4139
4140 l_progress := '020';
4141
4142 po_wf_util_pkg.SetItemAttrText( itemtype => l_parent_item_type,
4143 itemkey => l_parent_item_key,
4144 aname => 'AME_SUB_APPROVAL_RESPONSE',
4145 avalue => 'FALIED_VERIFICATION_REJECT');
4146 resultout := wf_engine.eng_completed||':'||'ACTIVITY_PERFORMED';
4147
4148 END update_resp_verf_failed_reject;
4149
4150 --------------------------------------------------------------------------------
4151 --Start of Comments
4152 --Name: get_ame_sub_approval_response
4153 --Pre-reqs:
4154 -- None.
4155 --Modifies:
4156 -- None.
4157 --Locks:
4158 -- None.
4159 --Function:
4160 -- Workflow activity PL/SQL handler.
4161 -- This procedure is used to fetch workflow attribute AME_SUB_APPROVAL_RESPONSE.
4162 --Parameters:
4163 --IN:
4164 -- Standard workflow IN parameters
4165 --OUT:
4166 -- Standard workflow OUT parameters
4167 --Testing:
4168 --
4169 --End of Comments
4170 -------------------------------------------------------------------------------
4171 PROCEDURE get_ame_sub_approval_response(
4172 itemtype IN VARCHAR2,
4173 itemkey IN VARCHAR2,
4174 actid IN NUMBER,
4175 funcmode IN VARCHAR2,
4176 resultout OUT NOCOPY VARCHAR2)
4177 IS
4178 l_ame_sub_approval_response VARCHAR2(50);
4179 l_progress VARCHAR2(3) := '000';
4180 l_api_name VARCHAR2(500) := 'get_ame_sub_approval_response';
4181 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
4182
4183 BEGIN
4184
4185 IF (funcmode <> wf_engine.eng_run) THEN
4186 resultout := wf_engine.eng_null;
4187 RETURN;
4188 END IF;
4189
4190 --Set the global attributes in the po wrapper function
4191 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
4192 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
4193
4194 l_progress := '010';
4195 IF (g_po_wf_debug = 'Y') THEN
4196 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
4197 END IF;
4198
4199 l_ame_sub_approval_response := po_wf_util_pkg.GetItemAttrText( aname => 'AME_SUB_APPROVAL_RESPONSE');
4200
4201 resultout := wf_engine.eng_completed || ':' || l_ame_sub_approval_response;
4202
4203 END get_ame_sub_approval_response;
4204
4205 --------------------------------------------------------------------------------
4206 --Start of Comments
4207 --Name: update_action_history_reminder
4208 --Pre-reqs:
4209 -- None.
4210 --Modifies:
4211 -- None.
4212 --Locks:
4213 -- None.
4214 --Function:
4215 -- Workflow activity PL/SQL handler.
4216 -- This procedure updates the po_action_history with REMINDER based on no of reminder.
4217 --Parameters:
4218 --IN:
4219 -- Standard workflow IN parameters
4220 --OUT:
4221 -- Standard workflow OUT parameters
4222 --Testing:
4223 --
4224 --End of Comments
4225 -------------------------------------------------------------------------------
4226 PROCEDURE update_action_history_reminder(
4227 itemtype IN VARCHAR2,
4228 itemkey IN VARCHAR2,
4229 actid IN NUMBER,
4230 funcmode IN VARCHAR2,
4231 resultout OUT NOCOPY VARCHAR2)
4232 IS
4233 l_no_of_reminder NUMBER;
4234 l_progress VARCHAR2(3) := '000';
4235 l_action po_action_history.action_code%TYPE := NULL;
4236 l_current_approver NUMBER;
4237 l_document_id NUMBER;
4238 l_document_type VARCHAR2(25);
4239 l_document_subtype VARCHAR2(25);
4240 l_approval_group_id NUMBER;
4241 l_org_id NUMBER;
4242 l_draft_id NUMBER;
4243 l_doc_string VARCHAR2(200);
4244 l_preparer_user_name fnd_user.user_name%TYPE;
4245 l_api_name VARCHAR2(500) := 'update_action_history_reminder';
4246 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
4247
4248 BEGIN
4249
4250 IF (funcmode <> wf_engine.eng_run) THEN
4251 resultout := wf_engine.eng_null;
4252 RETURN;
4253 END IF;
4254
4255 --Set the global attributes in the po wrapper function
4256 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
4257 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
4258
4259 l_progress := '010';
4260 IF (g_po_wf_debug = 'Y') THEN
4261 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
4262 END IF;
4263
4264 l_no_of_reminder := po_wf_util_pkg.GetItemAttrNumber( aname => 'NO_REMINDER');
4265
4266 IF(l_no_of_reminder = 1) THEN
4267 l_action := 'FIRST REMINDER';
4268 ELSIF (l_no_of_reminder = 2) THEN
4269 l_action := 'SECOND REMINDER';
4270 END IF;
4271
4272 l_current_approver := po_wf_util_pkg.GetItemAttrNumber( aname=>'APPROVER_EMPID');
4273 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
4274 l_draft_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DRAFT_ID');
4275 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
4276 l_document_subtype := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_SUBTYPE');
4277 l_approval_group_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'APPROVAL_GROUP_ID');
4278 l_org_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'ORG_ID');
4279
4280 l_progress := '020';
4281 IF l_org_id IS NOT NULL THEN
4282 PO_MOAC_UTILS_PVT.set_org_context(l_org_id);
4283 END IF;
4284
4285 l_progress := '030';
4286 UpdateActionHistoryPoAme(
4287 p_document_id => l_document_id,
4288 p_draft_id => l_draft_id,
4289 p_document_type => l_document_type,
4290 p_document_subtype => l_document_subtype,
4291 p_action => l_action,
4292 p_note => NULL,
4293 p_current_approver => l_current_approver);
4294
4295 EXCEPTION
4296 WHEN OTHERS THEN
4297 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemType, itemkey);
4298 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemType, itemkey);
4299 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
4300 PO_REQAPPROVAL_INIT1.send_error_notif(itemType, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, l_progress, l_document_id);
4301 IF (g_po_wf_debug = 'Y') THEN
4302 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
4303 END IF;
4304 RAISE;
4305 END update_action_history_reminder;
4306
4307 --------------------------------------------------------------------------------
4308 --Start of Comments
4309 --Name: ame_is_forward_to_valid
4310 --Pre-reqs:
4311 -- None.
4312 --Modifies:
4313 -- None.
4314 --Locks:
4315 -- None.
4316 --Function:
4317 -- Workflow activity PL/SQL handler.
4318 -- This procedure checks userame entered in the Forward-To field in response to the
4319 -- the approval notification, a valid username. If not resend the
4320 -- notification back to the user.
4321 --Parameters:
4322 --IN:
4323 -- Standard workflow IN parameters
4324 --OUT:
4325 -- Standard workflow OUT parameters
4326 --Testing:
4327 --
4328 --End of Comments
4329 -------------------------------------------------------------------------------
4330 PROCEDURE ame_is_forward_to_valid(
4331 itemtype IN VARCHAR2,
4332 itemkey IN VARCHAR2,
4333 actid IN NUMBER,
4334 funcmode IN VARCHAR2,
4335 resultout OUT NOCOPY VARCHAR2)
4336 IS
4337 l_forward_to_username_response VARCHAR2(100);
4338 l_error_msg VARCHAR2(500);
4339 l_progress VARCHAR2(3) := '000';
4340 l_orgid NUMBER;
4341 x_user_id NUMBER;
4342 l_api_name VARCHAR2(500) := 'ame_is_forward_to_valid';
4343 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
4344 BEGIN
4345
4346 IF (funcmode <> wf_engine.eng_run) THEN
4347 resultout := wf_engine.eng_null;
4348 RETURN;
4349 END IF;
4350
4351 --Set the global attributes in the po wrapper function
4352 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
4353 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
4354
4355 l_progress := '010';
4356 IF (g_po_wf_debug = 'Y') THEN
4357 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress||' Start');
4358 END IF;
4359
4360 l_orgid := po_wf_util_pkg.GetItemAttrNumber ( aname => 'ORG_ID');
4361
4362 IF l_orgid IS NOT NULL THEN
4363 po_moac_utils_pvt.set_org_context(l_orgid);
4364 END IF;
4365
4366 --Check that the value entered by responder as the FORWARD-TO user, is actually
4367 --a valid employee (has an employee id).
4368
4369 l_forward_to_username_response := po_wf_util_pkg.GetItemAttrText ( aname => 'FORWARD_TO_USERNAME_RESPONSE');
4370 l_forward_to_username_response := UPPER(l_forward_to_username_response);
4371
4372 BEGIN
4373 SELECT HR.PERSON_ID
4374 INTO x_user_id
4375 FROM FND_USER FND, PO_WORKFORCE_CURRENT_X HR
4376 WHERE FND.USER_NAME = l_forward_to_username_response
4377 AND FND.EMPLOYEE_ID = HR.PERSON_ID
4378 AND ROWNUM = 1;
4379
4380 fnd_message.set_name ('PO','PO_WF_NOTIF_REQUIRES_APPROVAL');
4381 l_error_msg := fnd_message.get;
4382 po_wf_util_pkg.SetItemAttrText ( aname => 'REQUIRES_APPROVAL_MSG' , avalue => l_error_msg);
4383 po_wf_util_pkg.SetItemAttrText ( aname => 'WRONG_FORWARD_TO_MSG' , avalue => '');
4384 resultout := wf_engine.eng_completed||':'||'Y';
4385 EXCEPTION
4386 WHEN NO_DATA_FOUND THEN
4387 --+ Set the error message in WRONG_FORWARD_TO_MSG so that it will be shown to the user
4388 --+ Set the Subject of the Approval notification to "Invalid forward-to"
4389 -- since the user entered an invalid forward-to, then set the
4390 -- "requires your approval" message to NULL.
4391 fnd_message.set_name ('PO','PO_WF_NOTIF_INVALID_FORWARD');
4392 l_error_msg := fnd_message.get;
4393 po_wf_util_pkg.SetItemAttrText ( aname => 'REQUIRES_APPROVAL_MSG' , avalue => '');
4394 po_wf_util_pkg.SetItemAttrText ( aname => 'WRONG_FORWARD_TO_MSG' , avalue => l_error_msg);
4395 resultout := wf_engine.eng_completed || ':' || 'N';
4396 END;
4397 END ame_is_forward_to_valid;
4398
4399 --------------------------------------------------------------------------------
4400 --Start of Comments
4401 --Name: abort_workflow
4402 --Pre-reqs:
4403 -- None.
4404 --Modifies:
4405 -- None.
4406 --Locks:
4407 -- None.
4408 --Function:
4409 -- This API aborts the workflow along with all the child workflows.
4410 --Parameters:
4411 --IN:
4412 --itemtype
4413 --itemkey
4414 --OUT:
4415 --x_return_message
4416 --End of Comments
4417 -------------------------------------------------------------------------------
4418 PROCEDURE abort_workflow(
4419 itemtype IN VARCHAR2,
4420 itemkey IN VARCHAR2,
4421 x_return_message OUT NOCOPY VARCHAR2)
4422 IS
4423 pragma AUTONOMOUS_TRANSACTION;
4424
4425 l_log_head VARCHAR2(50) := g_module_prefix || 'abort_workflow';
4426
4427 l_progress VARCHAR2(10);
4428
4429 CURSOR wfstoabort(t_item_type VARCHAR2,t_item_key VARCHAR2) IS
4430 SELECT LEVEL,
4431 item_type,
4432 item_key,
4433 end_date
4434 FROM wf_items
4435 START WITH item_type = t_item_type
4436 AND item_key = t_item_key
4437 CONNECT BY PRIOR item_type = parent_item_type
4438 AND PRIOR item_key = parent_item_key
4439 ORDER BY LEVEL DESC;
4440
4441 wf_rec wfstoabort%ROWTYPE;
4442
4443 BEGIN
4444
4445 open wfstoabort(itemtype,itemkey);
4446 loop
4447 fetch wfstoabort into wf_rec;
4448 if wfstoabort%NOTFOUND then
4449 close wfstoabort;
4450 exit;
4451 end if;
4452
4453 if (wf_rec.end_date is null) then
4454
4455 IF g_debug_stmt THEN
4456 PO_DEBUG.debug_stmt
4457 (p_log_head => l_log_head,
4458 p_token => l_progress,
4459 p_message => 'Aborting workflow : ' || wf_rec.item_type ||
4460 ' - ' || wf_rec.item_key);
4461 END IF;
4462
4463 WF_Engine.AbortProcess(itemtype => wf_rec.item_type,
4464 itemkey =>wf_rec.item_key,
4465 verify_lock => TRUE );
4466
4467 end if;
4468 end loop;
4469 COMMIT;
4470
4471 EXCEPTION
4472
4473 WHEN OTHERS THEN
4474 ROLLBACK;
4475 x_return_message := 'Exception ' || SQLERRM || ' in abort_workflow';
4476 IF g_debug_stmt THEN
4477 PO_DEBUG.debug_stmt
4478 (p_log_head => l_log_head,
4479 p_token => l_progress,
4480 p_message => x_return_message);
4481 END IF;
4482 RAISE;
4483 END abort_workflow;
4484
4485 --------------------------------------------------------------------------------
4486 --Start of Comments
4487 --Name: reset_authorization_status
4488 --Pre-reqs:
4489 -- None.
4490 --Modifies:
4491 -- None.
4492 --Locks:
4493 -- None.
4494 --Function:
4495 -- This API resets the authorization status to INCOMPLETE/REQUIRES REAPPROVAL.
4496 -- Also resets all the required flags.
4497 --Parameters:
4498 --IN:
4499 --p_document_id
4500 --OUT:
4501 --x_return_message
4502 --End of Comments
4503 -------------------------------------------------------------------------------
4504 PROCEDURE reset_authorization_status(
4505 p_document_id IN NUMBER,
4506 p_item_type IN VARCHAR2,
4507 p_item_key IN VARCHAR2,
4508 x_return_message OUT NOCOPY VARCHAR2)
4509 IS
4510 pragma AUTONOMOUS_TRANSACTION;
4511
4512 l_log_head VARCHAR2(50) := g_module_prefix || 'reset_authorization_status';
4513 l_progress VARCHAR2(10);
4514
4515 BEGIN
4516 l_progress := '000';
4517 IF g_debug_stmt THEN
4518 PO_DEBUG.debug_stmt
4519 (p_log_head => l_log_head,
4520 p_token => l_progress,
4521 p_message => 'Updating authorization status of ' || p_document_id);
4522 END IF;
4523
4524 UPDATE po_headers_all
4525 SET authorization_status = decode(approved_date, NULL, 'INCOMPLETE',
4526 'REQUIRES REAPPROVAL'),
4527 wf_item_type = p_item_type,
4528 wf_item_key = p_item_key,
4529 approved_flag = decode(approved_date, NULL, 'N', 'R'),
4530 pending_signature_flag = 'N',
4531 acceptance_required_flag = 'N',
4532 acceptance_due_date = Null,
4533 last_updated_by = FND_GLOBAL.user_id,
4534 last_update_login = FND_GLOBAL.login_id,
4535 last_update_date = sysdate,
4536 ame_approval_id = DECODE(ame_transaction_type,
4537 NULL,NULL,
4538 po_ame_approvals_s.NEXTVAL)
4539 WHERE po_header_id = p_document_id;
4540
4541 COMMIT;
4542
4543 EXCEPTION
4544
4545 WHEN OTHERS THEN
4546 ROLLBACK;
4547 x_return_message := 'Exception ' || SQLERRM || ' in reset_authorization_status';
4548 IF g_debug_stmt THEN
4549 PO_DEBUG.debug_stmt
4550 (p_log_head => l_log_head,
4551 p_token => l_progress,
4552 p_message => x_return_message);
4553 END IF;
4554 RAISE;
4555 END reset_authorization_status;
4556
4557 --------------------------------------------------------------------------------
4558 --Start of Comments
4559 --Name: send_withdraw_notification
4560 --Pre-reqs:
4561 -- None.
4562 --Modifies:
4563 -- None.
4564 --Locks:
4565 -- None.
4566 --Function:
4567 -- This API sends withdrawal notification.
4568 --Parameters:
4569 --IN:
4570 --p_document_id
4571 --p_document_num
4572 --p_doc_type_disp
4573 --p_from_user_name
4574 --p_role - Role to which notififcation has to be sent.
4575 --p_withdrawal_reason
4576 --OUT:
4577 --x_return_message
4578 --End of Comments
4579 -------------------------------------------------------------------------------
4580 PROCEDURE send_withdraw_notification(
4581 p_document_id IN NUMBER,
4582 p_document_num IN VARCHAR2,
4583 p_doc_type_disp IN VARCHAR,
4584 p_from_user_name IN VARCHAR,
4585 p_role IN VARCHAR,
4586 p_withdrawal_reason IN VARCHAR2,
4587 p_view_po_url IN VARCHAR2,
4588 p_edit_po_url IN VARCHAR2)
4589 IS
4590 pragma AUTONOMOUS_TRANSACTION;
4591
4592 l_log_head VARCHAR2(50) := g_module_prefix || 'send_withdraw_notification';
4593 l_progress VARCHAR2(10);
4594 l_notification_id NUMBER;
4595
4596 BEGIN
4597
4598 l_notification_id := wf_notification.send(role =>p_role,
4599 msg_type => 'POAPPRV',
4600 msg_name => 'PO_WITHDRAWN');
4601
4602 wf_notification.SetAttrText(nid =>l_notification_id,
4603 aname=> 'DOCUMENT_NUMBER',
4604 avalue =>p_document_num);
4605
4606 wf_notification.SetAttrText(nid =>l_notification_id,
4607 aname=> 'DOCUMENT_TYPE_DISP',
4608 avalue =>p_doc_type_disp);
4609
4610 wf_notification.SetAttrText(nid =>l_notification_id,
4611 aname=> '#FROM_ROLE',
4612 avalue =>p_from_user_name);
4613
4614 wf_notification.SetAttrText(nid =>l_notification_id,
4615 aname=> 'NOTIFICATION_REGION',
4616 avalue =>'JSP:/OA_HTML/OA.jsp?OAFunc=PO_APPRV_NOTIF&poHeaderId=' || p_document_id);
4617
4618 wf_notification.SetAttrText(nid =>l_notification_id,
4619 aname=> '#HISTORY',
4620 avalue =>'JSP:/OA_HTML/OA.jsp?OAFunc=PO_APPRV_NTF_ACTION_DETAILS&poHeaderId='
4621 || p_document_id || '&showActions=Y');
4622
4623 wf_notification.SetAttrText(nid =>l_notification_id,
4624 aname=> 'VIEW_DOC_URL',
4625 avalue =>p_view_po_url);
4626
4627 wf_notification.SetAttrText(nid =>l_notification_id,
4628 aname=> 'EDIT_DOC_URL',
4629 avalue =>p_edit_po_url);
4630
4631 IF g_debug_stmt THEN
4632 PO_DEBUG.debug_stmt (p_log_head => l_log_head,
4633 p_token => l_progress,
4634 p_message => 'Sending notification ' || l_notification_id ||
4635 ' to ' || p_role);
4636 END IF;
4637 COMMIT;
4638
4639 EXCEPTION
4640
4641 WHEN OTHERS THEN
4642 ROLLBACK;
4643 IF g_debug_stmt THEN
4644 PO_DEBUG.debug_stmt
4645 (p_log_head => l_log_head,
4646 p_token => l_progress,
4647 p_message => 'Exception ' || SQLERRM || ' while sending notification to ' || p_role);
4648 END IF;
4649 END send_withdraw_notification;
4650
4651 --------------------------------------------------------------------------------
4652 --Start of Comments
4653 --Name: notify_abt_withdrawal
4654 --Pre-reqs:
4655 -- None.
4656 --Modifies:
4657 -- None.
4658 --Locks:
4659 -- None.
4660 --Function:
4661 -- This API identifies all the approvers, buyer and supplier if acceptances exist
4662 -- and calls send_withdraw_notification
4663 --Parameters:
4664 --IN:
4665 --p_document_id
4666 --p_from_user_name
4667 --p_withdrawal_reason
4668 --OUT:
4669 --x_return_message
4670 --End of Comments
4671 -------------------------------------------------------------------------------
4672 PROCEDURE notify_abt_withdrawal(
4673 p_document_id IN NUMBER,
4674 p_from_user_name IN VARCHAR,
4675 p_withdrawal_reason IN VARCHAR2,
4676 p_view_po_url IN VARCHAR2,
4677 p_edit_po_url IN VARCHAR2)
4678 IS
4679
4680 l_log_head VARCHAR2(50) := g_module_prefix || 'notify_abt_withdrawal';
4681 l_progress VARCHAR2(10);
4682 l_emp_user_name VARCHAR2(100);
4683 l_emp_disp_name VARCHAR2(240);
4684 l_supplier_contact_id NUMBER;
4685 l_acceptance_id NUMBER;
4686 l_document_number po_headers_all.segment1%TYPE;
4687 l_doc_type_disp VARCHAR2(240);
4688 l_notification_id NUMBER;
4689 l_supp_contact_email VARCHAR2(200);
4690 l_supp_contact_user_name VARCHAR2(100);
4691
4692 CURSOR employee_to_send_notif(p_po_header_id NUMBER) IS
4693 SELECT DISTINCT poh.employee_id
4694 FROM po_action_history poh
4695 WHERE poh.object_id = p_po_header_id
4696 AND poh.employee_id IS NOT NULL
4697 AND poh.sequence_num >= (SELECT MAX(sequence_num)
4698 FROM po_action_history poh1
4699 WHERE poh1.object_id = p_po_header_id
4700 AND poh1.action_code = 'SUBMIT')
4701 UNION
4702 SELECT agent_id
4703 FROM po_headers_all
4704 WHERE po_header_id = p_po_header_id;
4705
4706 emp_rc employee_to_send_notif%ROWTYPE;
4707
4708 BEGIN
4709
4710 l_progress := '000';
4711 IF g_debug_stmt THEN
4712 PO_DEBUG.debug_begin(p_log_head => l_log_head );
4713 PO_DEBUG.debug_stmt
4714 (p_log_head => l_log_head,
4715 p_token =>l_progress,
4716 p_message => 'In notify_abt_withdrawal');
4717
4718 END IF;
4719
4720 SELECT segment1
4721 INTO l_document_number
4722 FROM po_headers_all
4723 WHERE po_header_id = p_document_id;
4724
4725 l_progress := '001';
4726
4727 l_doc_type_disp:= PO_DOC_STYLE_PVT.get_style_display_name(p_document_id);
4728
4729 l_progress := '002';
4730
4731 OPEN employee_to_send_notif(p_document_id);
4732 LOOP
4733 FETCH employee_to_send_notif INTO emp_rc;
4734
4735 IF employee_to_send_notif%NOTFOUND THEN
4736 CLOSE employee_to_send_notif;
4737 EXIT;
4738 END IF;
4739
4740 PO_REQAPPROVAL_INIT1.get_user_name(emp_rc.employee_id , l_emp_user_name, l_emp_disp_name);
4741
4742 l_progress := '003';
4743 IF g_debug_stmt THEN
4744 PO_DEBUG.debug_begin(p_log_head => l_log_head );
4745 PO_DEBUG.debug_stmt
4746 (p_log_head => l_log_head,
4747 p_token => l_progress,
4748 p_message => 'Calling send withdraw notifictaion to ' || l_emp_user_name );
4749 END IF;
4750
4751 send_withdraw_notification(p_document_id => p_document_id,
4752 p_document_num => l_document_number,
4753 p_doc_type_disp => l_doc_type_disp,
4754 p_from_user_name => p_from_user_name,
4755 p_role => l_emp_user_name,
4756 p_withdrawal_reason => p_withdrawal_reason,
4757 p_view_po_url => p_view_po_url,
4758 p_edit_po_url => p_edit_po_url);
4759 END LOOP;
4760
4761 -- Check whether any acceptance is recorded by supplier, if yes send supplier contact id notification
4762 l_progress := '004';
4763
4764 BEGIN
4765 SELECT acceptance_id
4766 INTO l_acceptance_id
4767 FROM po_acceptances
4768 WHERE po_header_id=p_document_id;
4769
4770 IF g_debug_stmt THEN
4771 PO_DEBUG.debug_begin(p_log_head => l_log_head );
4772 PO_DEBUG.debug_stmt
4773 (p_log_head => l_log_head,
4774 p_token => l_progress,
4775 p_message => 'l_acceptance_id ' || l_acceptance_id );
4776 END IF;
4777
4778 SELECT psc.user_name
4779 INTO l_supp_contact_user_name
4780 FROM po_supplier_contacts_val_v psc,
4781 po_headers_all poh
4782 WHERE psc.vendor_contact_id= poh.vendor_contact_id
4783 AND psc.vendor_site_id= poh.vendor_site_id
4784 AND po_header_id=p_document_id;
4785
4786 l_progress := '005';
4787
4788 IF g_debug_stmt THEN
4789 PO_DEBUG.debug_begin(p_log_head => l_log_head );
4790 PO_DEBUG.debug_stmt
4791 (p_log_head => l_log_head,
4792 p_token => l_progress,
4793 p_message => 'l_supp_contact_user_name ' || l_supp_contact_user_name );
4794 END IF;
4795
4796 IF l_supp_contact_user_name IS NOT NULL THEN
4797 send_withdraw_notification( p_document_id => p_document_id,
4798 p_document_num => l_document_number,
4799 p_doc_type_disp => l_doc_type_disp,
4800 p_from_user_name => p_from_user_name,
4801 p_role => l_supp_contact_user_name,
4802 p_withdrawal_reason => p_withdrawal_reason,
4803 p_view_po_url => p_view_po_url,
4804 p_edit_po_url => p_edit_po_url);
4805 END IF;
4806
4807 EXCEPTION
4808 WHEN NO_DATA_FOUND THEN
4809 IF g_debug_stmt THEN
4810 PO_DEBUG.debug_stmt (p_log_head => l_log_head,
4811 p_token => l_progress,
4812 p_message => 'Need not send notifictaion to Supplier');
4813 END IF;
4814 END;
4815
4816 EXCEPTION
4817
4818 WHEN OTHERS THEN
4819 IF g_debug_stmt THEN
4820 PO_DEBUG.debug_stmt
4821 (p_log_head => l_log_head,
4822 p_token => l_progress,
4823 p_message => 'Exception ' || SQLERRM || ' in notify_abt_withdrawal');
4824 END IF;
4825 END notify_abt_withdrawal;
4826
4827 --------------------------------------------------------------------------------
4828 --Start of Comments
4829 --Name: withdraw_document
4830 --Pre-reqs:
4831 -- None.
4832 --Modifies:
4833 -- None.
4834 --Locks:
4835 -- None.
4836 --Function:
4837 -- This API withdraws document.
4838 --Parameters:
4839 --IN:
4840 --p_document_id
4841 --p_draft_id
4842 --p_document_type
4843 --p_document_sub_type
4844 --p_revision_num
4845 --p_current_employee_id
4846 --p_note
4847 --OUT:
4848 --x_return_status
4849 --x_return_message
4850 --End of Comments
4851 -------------------------------------------------------------------------------
4852 PROCEDURE withdraw_document(
4853 p_document_id IN NUMBER,
4854 p_draft_id IN NUMBER,
4855 p_document_type IN VARCHAR2,
4856 p_document_sub_type IN VARCHAR2,
4857 p_revision_num IN NUMBER,
4858 p_current_employee_id IN NUMBER,
4859 p_note IN VARCHAR2,
4860 x_return_status OUT NOCOPY VARCHAR2,
4861 x_return_message OUT NOCOPY VARCHAR2) IS
4862
4863 l_log_head VARCHAR2(50) := g_module_prefix || 'withdraw_document';
4864 l_progress VARCHAR2(10);
4865 l_item_type wf_items.item_type%TYPE;
4866 l_item_key wf_items.item_key%TYPE;
4867 l_note po_action_history.note%TYPE;
4868 l_current_user_name VARCHAR2(100);
4869 l_disp_name VARCHAR2(240);
4870 l_send_notf_flag VARCHAR2(1);
4871 l_view_po_url VARCHAR2(1000);
4872 l_edit_po_url VARCHAR2(1000);
4873
4874 BEGIN
4875
4876 l_progress := '000';
4877 IF g_debug_stmt THEN
4878 PO_DEBUG.debug_begin(p_log_head => l_log_head );
4879 PO_DEBUG.debug_stmt
4880 (p_log_head => l_log_head,
4881 p_token => l_progress,
4882 p_message => 'document_type: ' || p_document_type ||
4883 ' document_sub_type ' || p_document_sub_type
4884 ||' document_id : ' || p_document_id
4885 || ' current_employee_id ' || p_current_employee_id);
4886
4887 END IF;
4888
4889 -- Logic :
4890 -- + Get all the required values
4891 -- + Supress existing approvers in AME.
4892 -- + Abort the approval workflow
4893 -- + Update the authorization status
4894 -- + Update the action history
4895 -- + Notify all approvers if Send notification
4896 -- to all approvers is selected in the style
4897
4898 SELECT wf_item_type, wf_item_key
4899 INTO l_item_type,l_item_key
4900 FROM po_headers_all
4901 WHERE po_header_id = p_document_id;
4902
4903 l_progress := '001';
4904
4905 IF g_debug_stmt THEN
4906 PO_DEBUG.debug_begin(p_log_head => l_log_head );
4907 PO_DEBUG.debug_stmt
4908 (p_log_head => l_log_head,
4909 p_token => l_progress,
4910 p_message => 'itemkey is ' || l_item_key);
4911 END IF;
4912
4913 PO_REQAPPROVAL_INIT1.get_user_name(p_current_employee_id, l_current_user_name, l_disp_name);
4914
4915 l_progress := '002';
4916
4917 IF g_debug_stmt THEN
4918 PO_DEBUG.debug_begin(p_log_head => l_log_head );
4919 PO_DEBUG.debug_stmt
4920 (p_log_head => l_log_head,
4921 p_token => l_progress,
4922 p_message => 'Current user is ' || l_current_user_name);
4923 END IF;
4924
4925 l_view_po_url := PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_item_type,
4926 itemkey => l_item_key,
4927 aname => 'VIEW_DOC_URL');
4928
4929 l_edit_po_url:= PO_WF_UTIL_PKG.GetItemAttrText ( itemtype => l_item_type,
4930 itemkey => l_item_key,
4931 aname => 'EDIT_DOC_URL' );
4932 BEGIN
4933 supress_existing_approvers(
4934 itemtype => l_item_type,
4935 itemkey => l_item_key);
4936 EXCEPTION
4937 WHEN OTHERS THEN
4938 NULL;
4939 END;
4940
4941 abort_workflow(itemtype => l_item_type,
4942 itemkey => l_item_key,
4943 x_return_message => x_return_message);
4944
4945 l_progress := '003';
4946 IF g_debug_stmt THEN
4947 PO_DEBUG.debug_begin(p_log_head => l_log_head );
4948 PO_DEBUG.debug_stmt
4949 (p_log_head => l_log_head,
4950 p_token => l_progress,
4951 p_message => 'Successfully aborted workflow' );
4952 END IF;
4953
4954 reset_authorization_status(
4955 p_document_id => p_document_id,
4956 p_item_type => NULL,
4957 p_item_key => NULL,
4958 x_return_message => x_return_message);
4959
4960 l_progress := '004';
4961 IF g_debug_stmt THEN
4962 PO_DEBUG.debug_begin(p_log_head => l_log_head );
4963 PO_DEBUG.debug_stmt
4964 (p_log_head => l_log_head,
4965 p_token => l_progress,
4966 p_message => 'Authorization status update complete' );
4967 END IF;
4968
4969 l_note := fnd_message.get_string('PO','PO_ACTION_HIST_WITHDRAW_NOTE');
4970 UpdateActionHistoryPoAme(
4971 p_document_id => p_document_id,
4972 p_draft_id => p_draft_id,
4973 p_document_type => p_document_type,
4974 p_document_subtype => p_document_sub_type,
4975 p_action => 'NO ACTION',
4976 p_note => l_note,
4977 p_current_approver => NULL);
4978
4979 l_progress := '005';
4980 IF g_debug_stmt THEN
4981 PO_DEBUG.debug_begin(p_log_head => l_log_head );
4982 PO_DEBUG.debug_stmt
4983 (p_log_head => l_log_head,
4984 p_token => l_progress,
4985 p_message => 'Updated the existing NULL actions with NO ACTION');
4986 END IF;
4987
4988 InsertActionHistoryPoAme (
4989 p_document_id => p_document_id,
4990 p_draft_id => p_draft_id,
4991 p_document_type => p_document_type,
4992 p_document_subtype => p_document_sub_type,
4993 p_revision_num => p_revision_num,
4994 p_employee_id => p_current_employee_id,
4995 p_approval_group_id => NULL,
4996 p_action => 'WITHDRAW',
4997 p_note => p_note);
4998
4999 l_progress := '006';
5000 IF g_debug_stmt THEN
5001 PO_DEBUG.debug_begin(p_log_head => l_log_head );
5002 PO_DEBUG.debug_stmt
5003 (p_log_head => l_log_head,
5004 p_token => l_progress,
5005 p_message => 'Inserted WITHDRAW action in action history');
5006 END IF;
5007
5008 BEGIN
5009 SELECT nvl(SEND_WITHDRW_NOTF_FLAG,'N')
5010 INTO l_send_notf_flag
5011 FROM po_doc_style_headers ds,
5012 po_headers_all poh
5013 WHERE poh.po_header_id = p_document_id
5014 AND poh.style_id = ds.style_id;
5015 EXCEPTION
5016 when others then
5017 l_send_notf_flag := 'N';
5018 END;
5019
5020 l_progress := '007';
5021 IF g_debug_stmt THEN
5022 PO_DEBUG.debug_begin(p_log_head => l_log_head );
5023 PO_DEBUG.debug_stmt
5024 (p_log_head => l_log_head,
5025 p_token => l_progress,
5026 p_message => 'l_send_notf_flag : ' || l_send_notf_flag);
5027 END IF;
5028
5029 IF(l_send_notf_flag = 'Y') THEN
5030
5031 notify_abt_withdrawal(
5032 p_document_id => p_document_id,
5033 p_from_user_name => l_current_user_name,
5034 p_withdrawal_reason => p_note,
5035 p_view_po_url => l_view_po_url,
5036 p_edit_po_url => l_edit_po_url);
5037
5038 l_progress := '008';
5039 IF g_debug_stmt THEN
5040 PO_DEBUG.debug_begin(p_log_head => l_log_head );
5041 PO_DEBUG.debug_stmt
5042 (p_log_head => l_log_head,
5043 p_token => l_progress,
5044 p_message => 'Notified all approvers');
5045 END IF;
5046
5047 END IF;
5048
5049 x_return_status := FND_API.G_RET_STS_SUCCESS;
5050
5051 EXCEPTION
5052
5053 WHEN OTHERS THEN
5054 x_return_status := FND_API.G_RET_STS_ERROR;
5055 FND_MESSAGE.set_name('PO', 'PO_UNABLE_TO_WITHDRAW');
5056 FND_MESSAGE.set_token('ERR_MESSAGE', x_return_message);
5057 x_return_message := FND_MESSAGE.get;
5058 IF g_debug_stmt THEN
5059 PO_DEBUG.debug_begin(p_log_head => l_log_head );
5060 PO_DEBUG.debug_stmt
5061 (p_log_head => l_log_head,
5062 p_token => l_progress,
5063 p_message => x_return_message);
5064 END IF;
5065 END withdraw_document;
5066
5067 --------------------------------------------------------------------------------
5068 --Start of Comments
5069 --Name: check_set_esigners
5070 --Pre-reqs:
5071 -- None.
5072 --Modifies:
5073 -- None.
5074 --Locks:
5075 -- None.
5076 --Function:
5077 -- This is uesd to determine whether approver is post-approver or not.
5078 -- And then correspondingly set values for attribute E_SIGNER_EXISTS as Y or N.
5079 --Parameters:
5080 --IN:
5081 -- Standard workflow IN parameters
5082 --OUT:
5083 -- Standard workflow OUT parameters
5084 --Testing:
5085 -- None
5086 --End of Comments
5087 ---------------------------------------------------------------------------------
5088
5089 FUNCTION check_set_esigners(
5090 itemtype IN VARCHAR2,
5091 itemkey IN VARCHAR2)
5092 RETURN VARCHAR2
5093 IS
5094 l_approver_index NUMBER;
5095 e_signer_flag VARCHAR2(1);
5096 l_transaction_type po_document_types.ame_transaction_type%TYPE;
5097 l_ame_transaction_id NUMBER;
5098 l_api_name VARCHAR2(500) := 'check_set_esigners';
5099 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
5100
5101 BEGIN
5102
5103 -- Logic :
5104 -- + Check whether approver is post approver or not. Post approvers are signers.
5105 -- approverRecord.authority = 'X' for Pre-approvers
5106 -- approverRecord.authority = 'Y' for Approvers
5107 -- approverRecord.authority = 'Z' for Post-Approvers(signers)
5108 -- + If e-signer exists, then set workflow attribute 'E_SIGNER_EXISTS' as Y.
5109 -- Also update ame for current set approver with approval status as NULL, so that
5110 -- they would be fetched next time in getNextApprovers(..) for E-Signer looping.
5111
5112 --Set the global attributes in the po wrapper function
5113 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
5114 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
5115
5116 IF (g_po_wf_debug = 'Y') THEN
5117 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head || ' 001 ');
5118 END IF;
5119
5120 l_ame_transaction_id := po_wf_util_pkg.GetItemAttrNumber(aname => 'AME_TRANSACTION_ID');
5121 l_transaction_type := po_wf_util_pkg.GetItemAttrText( aname => 'AME_TRANSACTION_TYPE');
5122
5123 e_signer_flag := 'N';
5124 l_approver_index := g_next_approvers.first();
5125
5126 WHILE ( l_approver_index IS NOT NULL ) LOOP
5127 IF (g_next_approvers(l_approver_index).authority = 'Z' AND e_signer_flag = 'N') THEN
5128 po_wf_util_pkg.SetItemAttrText (aname => 'E_SIGNER_EXISTS', avalue => 'Y');
5129 e_signer_flag :='Y';
5130 END IF;
5131
5132 IF(e_signer_flag = 'Y') THEN
5133 g_next_approvers(l_approver_index).approval_status := ame_util.nullStatus;
5134 ame_api2.updateApprovalStatus ( applicationIdIn => applicationId,
5135 transactionIdIn => l_ame_transaction_id,
5136 transactionTypeIn => l_transaction_type,
5137 approverIn => g_next_approvers(l_approver_index),
5138 updateItemIn => TRUE);
5139 IF (g_po_wf_debug = 'Y') THEN
5140 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||' 002 :'||' updated ame for '||
5141 g_next_approvers(l_approver_index).name || ' with null status');
5142 END IF;
5143 END IF;
5144 l_approver_index := g_next_approvers.next(l_approver_index);
5145 END LOOP;
5146
5147 IF (g_po_wf_debug = 'Y') THEN
5148 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head || ' return e_signer_flag=' || e_signer_flag);
5149 END IF;
5150
5151 RETURN e_signer_flag;
5152 END check_set_esigners;
5153
5154 --------------------------------------------------------------------------------
5155 --Start of Comments
5156 --Name: is_fyi_approver
5157 --Pre-reqs:
5158 -- None.
5159 --Modifies:
5160 -- None.
5161 --Locks:
5162 -- None.
5163 --Function:
5164 -- Workflow activity PL/SQL handler.
5165 -- This is uesd to determine whether approver is FYI approver or not.
5166 -- Values can be Y or N.
5167 --Parameters:
5168 --IN:
5169 -- Standard workflow IN parameters
5170 --OUT:
5171 -- Standard workflow OUT parameters
5172 --Testing:
5173 -- None
5174 --End of Comments
5175 ---------------------------------------------------------------------------------
5176 PROCEDURE is_fyi_approver(
5177 itemtype IN VARCHAR2,
5178 itemkey IN VARCHAR2,
5179 actid IN NUMBER,
5180 funcmode IN VARCHAR2,
5181 resultout OUT NOCOPY VARCHAR2)
5182 IS
5183 l_is_fyi_approver VARCHAR2(1);
5184 BEGIN
5185
5186 IF (funcmode <> wf_engine.eng_run) THEN
5187 resultout := wf_engine.eng_null;
5188 RETURN;
5189 END IF;
5190
5191 --Set the global attributes in the po wrapper function
5192 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
5193 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
5194
5195 --Logic is check first for workflow attribute 'IS_FYI_APPROVER', then attribute 'APPROVER_CATEGORY'
5196 --Fetch 'IS_FYI_APPROVER' value which is set in launch_parralel_approval
5197 l_is_fyi_approver := po_wf_util_pkg.GetItemAttrText (aname => 'IS_FYI_APPROVER');
5198
5199 IF l_is_fyi_approver = 'Y' THEN
5200 resultout := wf_engine.eng_completed || ':' || 'Y';
5201 ELSE
5202 resultout := wf_engine.eng_completed || ':' || 'N';
5203 END IF;
5204
5205 END is_fyi_approver;
5206
5207 --------------------------------------------------------------------------------
5208 --Start of Comments
5209 --Name: get_current_future_approvers
5210 --Pre-reqs:
5211 -- None.
5212 --Modifies:
5213 -- None.
5214 --Locks:
5215 -- None.
5216 --Function:
5217 -- This function returns current/future approvers for a particular
5218 -- AME transaction id/type.
5219 --Parameters:
5220 --IN:
5221 -- transactionType
5222 -- transactionId
5223 --RETURNS:
5224 -- po_ame_approver_tab
5225 --End of Comments
5226 ---------------------------------------------------------------------------------
5227 FUNCTION get_current_future_approvers(
5228 transactionType IN VARCHAR2,
5229 transactionId IN NUMBER)
5230 RETURN po_ame_approver_tab
5231 IS
5232
5233 l_is_last_approver_record VARCHAR2(1);
5234 l_total_approver_count NUMBER;
5235 l_current_approver_index NUMBER;
5236 ApproverList ame_util.approversTable2;
5237 xprocess_out VARCHAR2(10);
5238 xitemIndexesOut ame_util.idList;
5239 xitemClassesOut ame_util.stringList;
5240 xitemIdsOut ame_util.stringList;
5241 xitemSourcesOut ame_util.longStringList;
5242 xtransVariableNamesOut ame_util.stringList;
5243 xtransVariableValuesOut ame_util.stringList;
5244 xproduction_Indexes ame_util.idList;
5245 xvariable_Names ame_util.stringList;
5246 xvariable_Values ame_util.stringList;
5247 l_next_approver_id NUMBER;
5248 l_next_approver_name per_employees_current_x.full_name%TYPE;
5249 l_next_approver_user_name VARCHAR2(100);
5250 l_next_approver_disp_name VARCHAR2(240);
5251 l_approver_category VARCHAR2(100);
5252 l_approver_order_number NUMBER;
5253 l_approver_index NUMBER;
5254 l_is_current_approver varchar2(1);
5255 l_log_head VARCHAR2(500);
5256 l_progress VARCHAR2(10);
5257 x_approver_tab PO_AME_APPROVER_TAB;
5258
5259 BEGIN
5260
5261 l_log_head := g_module_prefix || 'get_current_future_approvers';
5262
5263 l_progress := '000';
5264 IF g_debug_stmt THEN
5265 PO_DEBUG.debug_begin(p_log_head => l_log_head );
5266 PO_DEBUG.debug_stmt
5267 (p_log_head => l_log_head,
5268 p_token => l_progress,
5269 p_message => 'transactionType: ' || transactionType ||
5270 ' transactionId ' || transactionId);
5271
5272 END IF;
5273
5274 x_approver_tab := PO_AME_APPROVER_TAB();
5275
5276 l_progress := '001';
5277
5278 -- Logic - Loop through all the approvers returned by AME API - getAllApprovers3
5279 -- If the approval_status of a particualr approver is NULL then it is a future approver.
5280 -- If the approval_status is NOTIFIED then it is a current approver.
5281 -- Get the person name based on orig_system -> PER/POS/FND USER
5282 -- Get the approver category by checking production indexes - Reviewer,
5283 -- Post approval group - Signer else Approver.
5284 -- Populate the plsql table po_ame_approver_tab.
5285
5286 ame_api2.getAllApprovers3(applicationIdIn => applicationId,
5287 transactionTypeIn => transactionType,
5288 transactionIdIn => transactionId,
5289 approvalProcessCompleteYNOut => xprocess_out,
5290 approversOut => ApproverList,
5291 itemIndexesOut => xitemIndexesOut,
5292 itemClassesOut => xitemClassesOut,
5293 itemIdsOut => xitemIdsOut,
5294 itemSourcesOut => xitemSourcesOut,
5295 productionIndexesOut =>xproduction_Indexes,
5296 variableNamesOut => xvariable_Names,
5297 variableValuesOut => xvariable_Values,
5298 transVariableNamesOut => xtransVariableNamesOut,
5299 transVariableValuesOut => xtransVariableValuesOut);
5300
5301 l_progress := '002';
5302
5303 l_approver_index := ApproverList.first;
5304
5305 WHILE (l_approver_index IS NOT NULL) LOOP
5306
5307 BEGIN
5308
5309 l_progress := '003';
5310
5311 IF ( ApproverList(l_approver_index).approval_status IS NULL
5312 OR ApproverList(l_approver_index).approval_status IN (ame_util.notifiedStatus,
5313 ame_util.notifiedByRepeatedStatus))
5314 THEN
5315
5316 l_progress := '004';
5317
5318 --Get the Order No.
5319 l_approver_order_number := ApproverList(l_approver_index).approver_order_number;
5320
5321 l_progress := '005';
5322 -- Get the Approver Name
5323 IF (ApproverList(l_approver_index).orig_system = ame_util.perorigsystem) THEN
5324
5325 l_next_approver_id := ApproverList(l_approver_index).orig_system_id;
5326
5327 ELSIF (ApproverList(l_approver_index).orig_system = ame_util.posorigsystem) THEN
5328
5329 -----------------------------------------------------------------------
5330 -- SQL What: Get the person assigned to position returned by AME.
5331 -- SQL Why : When AME returns position id, then using this sql we find
5332 -- one person assigned to this position and use this person
5333 -- as approver.
5334 -----------------------------------------------------------------------
5335 SELECT person_id , full_name
5336 INTO l_next_approver_id, l_next_approver_name
5337 FROM ( SELECT person.person_id , person.full_name
5338 FROM per_all_people_f person ,
5339 per_all_assignments_f asg ,
5340 wf_users wu
5341 WHERE asg.position_id = ApproverList(l_approver_index).orig_system_id
5342 AND wu.orig_system = ame_util.perorigsystem
5343 AND wu.orig_system_id = person.person_id
5344 AND TRUNC (sysdate) BETWEEN person.effective_start_date AND NVL (person.effective_end_date ,TRUNC (sysdate))
5345 AND person.person_id = asg.person_id
5346 AND asg.primary_flag = 'Y'
5347 AND asg.assignment_type IN ('E','C')
5348 AND ( person.current_employee_flag = 'Y' OR person.current_npw_flag = 'Y' )
5349 AND asg.assignment_status_type_id NOT IN
5350 ( SELECT assignment_status_type_id
5351 FROM per_assignment_status_types
5352 WHERE per_system_status = 'TERM_ASSIGN'
5353 )
5354 AND TRUNC (sysdate) BETWEEN asg.effective_start_date AND asg.effective_end_date
5355 ORDER BY person.last_name )
5356 WHERE ROWNUM = 1;
5357
5358 ELSIF (ApproverList(l_approver_index).orig_system = ame_util.fnduserorigsystem) THEN
5359 SELECT employee_id
5360 INTO l_next_approver_id
5361 FROM fnd_user
5362 WHERE user_id = ApproverList(l_approver_index).orig_system_id
5363 AND TRUNC (sysdate) BETWEEN start_date AND NVL (end_date ,sysdate + 1);
5364 END IF;
5365
5366 wf_directory.getusername (ame_util.perorigsystem, l_next_approver_id, l_next_approver_user_name, l_next_approver_disp_name);
5367
5368 IF (ApproverList(l_approver_index).orig_system = ame_util.perorigsystem) THEN
5369 l_next_approver_disp_name := ApproverList(l_approver_index).display_name;
5370 END IF;
5371
5372 l_progress := '006';
5373 -- Get the Approver category
5374 l_approver_category := null;
5375 IF ApproverList(l_approver_index).approver_category = ame_util.fyiapprovercategory then
5376 l_approver_category := 'FYI_APPROVER';
5377
5378 ELSE
5379
5380 IF (xproduction_Indexes.Count > 0) THEN
5381 FOR j IN 1..xproduction_Indexes.Count LOOP
5382 IF xproduction_Indexes(j) = l_approver_index THEN
5383 IF xvariable_Names(j) = 'REVIEWER' AND xvariable_Values(j)= 'YES' THEN
5384 l_approver_category := 'REVIEWER';
5385 END IF;
5386 END IF;
5387 END LOOP;
5388 END IF;
5389
5390 IF (l_approver_category is null) then
5391 IF (ApproverList(l_approver_index).authority = 'Z') then
5392 l_approver_category := 'ESIGNER';
5393 ELSE
5394 l_approver_category := 'APPROVER';
5395 END IF;
5396 END IF;
5397
5398 END IF;
5399
5400 BEGIN
5401 SELECT displayed_field
5402 INTO l_approver_category
5403 FROM po_lookup_codes
5404 WHERE lookup_type = 'PO_APPROVER_TYPE'
5405 AND lookup_code = l_approver_category;
5406 EXCEPTION
5407 WHEN no_data_found THEN
5408 l_approver_category := NULL;
5409 END;
5410
5411 l_progress := '007';
5412 -- Identify if it is current approver
5413 IF(ApproverList(l_approver_index).approval_status = ame_util.notifiedStatus
5414 AND ApproverList(l_approver_index).approver_category <> ame_util.fyiapprovercategory)THEN
5415 l_is_current_approver := 'Y';
5416 ELSE
5417 l_is_current_approver := 'N';
5418 END IF;
5419
5420 l_progress := '008';
5421
5422 IF NOT(ApproverList(l_approver_index).approver_category = ame_util.fyiapprovercategory
5423 AND Nvl(ApproverList(l_approver_index).approval_status,-1) IN
5424 (ame_util.notifiedStatus,ame_util.notifiedByRepeatedStatus)) THEN
5425
5426 -- Insert record in x_approver_tab
5427 x_approver_tab.extend;
5428 x_approver_tab (x_approver_tab.last) := PO_AME_APPROVER_REC(l_approver_order_number,
5429 l_next_approver_disp_name,
5430 l_approver_category,
5431 l_is_current_approver);
5432
5433 END IF;
5434
5435 END IF;
5436
5437 l_progress := '009';
5438 l_approver_index := ApproverList.next (l_approver_index);
5439
5440 EXCEPTION
5441 WHEN OTHERS THEN
5442 IF g_debug_stmt THEN
5443 PO_DEBUG.debug_begin(p_log_head => l_log_head );
5444 PO_DEBUG.debug_stmt
5445 (p_log_head => l_log_head,
5446 p_token => l_progress,
5447 p_message => 'Exception ' || sqlerrm);
5448 END IF;
5449
5450 l_approver_index := ApproverList.next (l_approver_index);
5451
5452 --bug 16168369
5453 --Remove 'CONTINUE' as for Oracle 10g it's not compatible
5454 --CONTINUE ;
5455 END;
5456
5457 END LOOP;
5458
5459 l_progress := '010';
5460
5461 RETURN x_approver_tab;
5462
5463 EXCEPTION
5464
5465 WHEN OTHERS THEN
5466
5467 IF g_debug_stmt THEN
5468 PO_DEBUG.debug_begin(p_log_head => l_log_head );
5469 PO_DEBUG.debug_stmt
5470 (p_log_head => l_log_head,
5471 p_token => l_progress,
5472 p_message => 'Exception ' || sqlerrm);
5473 END IF;
5474
5475 RETURN x_approver_tab;
5476
5477 END get_current_future_approvers;
5478 --------------------------------------------------------------------------------
5479 --Start of Comments
5480 --Name: set_esigner_response_accepted
5481 --Pre-reqs:
5482 -- None.
5483 --Modifies:
5484 -- None.
5485 --Locks:
5486 -- None.
5487 --Function:
5488 -- Workflow activity PL/SQL handler.
5489 -- This is uesd to set ESIGNER_RESPONSE to accepted
5490 --Parameters:
5491 --IN:
5492 -- Standard workflow IN parameters
5493 --OUT:
5494 -- Standard workflow OUT parameters
5495 --Testing:
5496 -- None
5497 --End of Comments
5498 ---------------------------------------------------------------------------------
5499 PROCEDURE set_esigner_response_accepted(
5500 itemtype IN VARCHAR2,
5501 itemkey IN VARCHAR2,
5502 actid IN NUMBER,
5503 funcmode IN VARCHAR2,
5504 resultout OUT NOCOPY VARCHAR2)
5505 IS
5506 BEGIN
5507 -- Logic :
5508 -- + Set worflow attribute for 'ESIGNER_RESPONSE' as ACCEPTED
5509
5510 IF (funcmode <> wf_engine.eng_run) THEN
5511 resultout := wf_engine.eng_null;
5512 RETURN;
5513 END IF;
5514
5515 --Set the global attributes in the po wrapper function
5516 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
5517 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
5518
5519 po_wf_util_pkg.SetItemAttrText (aname => 'ESIGNER_RESPONSE', avalue => 'ACCEPTED');
5520 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
5521
5522 END set_esigner_response_accepted;
5523
5524 --------------------------------------------------------------------------------
5525 --Start of Comments
5526 --Name: set_esigner_response_rejected
5527 --Pre-reqs:
5528 -- None.
5529 --Modifies:
5530 -- None.
5531 --Locks:
5532 -- None.
5533 --Function:
5534 -- Workflow activity PL/SQL handler.
5535 -- This is uesd to set ESIGNER_RESPONSE to rejected
5536 --Parameters:
5537 --IN:
5538 -- Standard workflow IN parameters
5539 --OUT:
5540 -- Standard workflow OUT parameters
5541 --Testing:
5542 -- None
5543 --End of Comments
5544 ---------------------------------------------------------------------------------
5545 PROCEDURE set_esigner_response_rejected(
5546 itemtype IN VARCHAR2,
5547 itemkey IN VARCHAR2,
5548 actid IN NUMBER,
5549 funcmode IN VARCHAR2,
5550 resultout OUT NOCOPY VARCHAR2)
5551 IS
5552 BEGIN
5553 -- Logic :
5554 -- + Set worflow attribute for 'ESIGNER_RESPONSE' as REJECTED
5555
5556 IF (funcmode <> wf_engine.eng_run) THEN
5557 resultout := wf_engine.eng_null;
5558 RETURN;
5559 END IF;
5560
5561 --Set the global attributes in the po wrapper function
5562 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
5563 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
5564
5565 po_wf_util_pkg.SetItemAttrText (aname => 'ESIGNER_RESPONSE', avalue => 'REJECTED');
5566 resultout := wf_engine.eng_completed || ':' || 'ACTIVITY_PERFORMED';
5567
5568 END set_esigner_response_rejected;
5569
5570 -------------------------------------------------------------------------------
5571 --Start of Comments
5572 --Name: create_erecord
5573 --Pre-reqs:
5574 -- None.
5575 --Modifies:
5576 -- None.
5577 --Locks:
5578 -- None.
5579 --Function:
5580 -- Calls the APIs given by eRecords product team to store the signature
5581 -- notification as an eRecord
5582 --Parameters:
5583 --IN:
5584 --itemtype
5585 -- Standard parameter to be used in a workflow procedure
5586 --itemkey
5587 -- Standard parameter to be used in a workflow procedure
5588 --actid
5589 -- Standard parameter to be used in a workflow procedure
5590 --funcmode
5591 -- Standard parameter to be used in a workflow procedure
5592 --OUT:
5593 --resultout
5594 -- Standard parameter to be used in a workflow procedure
5595 --Testing:
5596 -- Testing to be done based on the test cases in Document Binding DLD
5597 --End of Comments
5598 -------------------------------------------------------------------------------
5599 PROCEDURE create_erecord(
5600 itemtype IN VARCHAR2,
5601 itemkey IN VARCHAR2,
5602 actid IN NUMBER,
5603 funcmode IN VARCHAR2,
5604 resultout OUT NOCOPY VARCHAR2)
5605 IS
5606 l_signature_id NUMBER;
5607 l_evidence_store_id NUMBER;
5608 l_notif_id NUMBER;
5609 l_erecord_id NUMBER;
5610 l_doc_parameters PO_ERECORDS_PVT.Params_tbl_type;
5611 l_sig_parameters PO_ERECORDS_PVT.Params_tbl_type;
5612 l_document_id PO_HEADERS.po_header_id%TYPE;
5613 l_user_name FND_USER.user_name%TYPE;
5614 l_requester FND_USER.user_name%TYPE;
5615 l_esigner_response VARCHAR2(20);
5616 l_response VARCHAR2(20);
5617 l_event_name VARCHAR2(50);
5618 l_acceptance_note PO_ACCEPTANCES.note%TYPE;
5619 l_document_number PO_HEADERS_ALL.segment1%TYPE;
5620 l_orgid PO_HEADERS_ALL.org_id%TYPE;
5621 l_revision PO_HEADERS_ALL.revision_num%TYPE;
5622 l_return_status VARCHAR2(1);
5623 l_msg_count NUMBER;
5624 l_msg_data VARCHAR2(2000);
5625 l_doc_string VARCHAR2(200);
5626 l_preparer_user_name WF_USERS.name%TYPE;
5627 l_trans_status VARCHAR2(10);
5628 l_response_code FND_LOOKUP_VALUES.meaning%TYPE;
5629 l_reason_code FND_LOOKUP_VALUES.meaning%TYPE;
5630 l_signer_type FND_LOOKUP_VALUES.meaning%TYPE;
5631 l_signer VARCHAR2(10);
5632 l_erecords_exception EXCEPTION;
5633 l_api_name VARCHAR2(500) := 'CREATE_ERECORD';
5634 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
5635 BEGIN
5636 -- Logic :
5637 -- + Call PO_ERECORDS_PVT.capture_signature to capture signature.
5638 -- + Call PO_ERECORDS_PVT.send_ackn to send acknowledgement.
5639 -- + Set ERECORD_ID and SIG_ID workflow attributes.
5640
5641 --Set the global attributes in the po wrapper function
5642 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
5643 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
5644
5645 IF (g_po_wf_debug = 'Y') THEN
5646 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head || ' 001 ');
5647 END IF;
5648
5649 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
5650 l_acceptance_note := PO_WF_UTIL_PKG.GetItemAttrText ( aname => 'SIGNATURE_COMMENTS');
5651 l_esigner_response := PO_WF_UTIL_PKG.GetItemAttrText ( aname => 'ESIGNER_RESPONSE');
5652 l_orgid := po_wf_util_pkg.GetItemAttrNumber (aname => 'ORG_ID');
5653 l_document_number := PO_WF_UTIL_PKG.GetItemAttrText( aname => 'DOCUMENT_NUMBER');
5654 l_revision := PO_WF_UTIL_PKG.GetItemAttrText( aname => 'REVISION_NUMBER');
5655
5656 l_signer := 'BUYER';
5657 l_event_name := 'oracle.apps.po.buyersignature';
5658
5659 l_user_name := FND_GLOBAL.user_name;
5660 l_requester := PO_WF_UTIL_PKG.GetItemAttrText (aname => 'BUYER_USER_NAME');
5661 --Get the Notification Id of the recent Signature Notification into l_notif_id.
5662 l_notif_id := po_wf_util_pkg.GetItemAttrNumber (aname => 'NOTIFICATION_ID');
5663
5664
5665 BEGIN
5666 SELECT displayed_field
5667 INTO l_response_code
5668 FROM Po_Lookup_Codes
5669 WHERE Lookup_Type = 'ERECORD_RESPONSE'
5670 AND Lookup_Code = l_esigner_response;
5671 EXCEPTION
5672 WHEN NO_DATA_FOUND THEN
5673 l_response_code := NULL;
5674 END;
5675
5676 BEGIN
5677 SELECT displayed_field
5678 INTO l_reason_code
5679 FROM Po_Lookup_Codes
5680 WHERE Lookup_Type = 'ERECORD_REASON'
5681 AND Lookup_Code = 'ERES_REASON';
5682 EXCEPTION
5683 WHEN NO_DATA_FOUND THEN
5684 l_reason_code := NULL;
5685 END;
5686
5687 BEGIN
5688 SELECT displayed_field
5689 INTO l_signer_type
5690 FROM Po_Lookup_Codes
5691 WHERE Lookup_Type = 'ERECORD_SIGNER_TYPE'
5692 AND Lookup_Code = Decode(l_signer,'BUYER','CUSTOMER');
5693 EXCEPTION
5694 WHEN NO_DATA_FOUND THEN
5695 l_signer_type := NULL;
5696 END;
5697
5698
5699 l_evidence_store_id := wf_notification.GetAttrText(l_notif_id, '#WF_SIG_ID');
5700
5701 l_doc_parameters(1).Param_Name := 'PSIG_USER_KEY_LABEL';
5702 l_doc_parameters(1).Param_Value := fnd_message.get_string('PO', 'PO_EREC_PARAM_KEYVALUE');
5703 l_doc_parameters(1).Param_displayname := 'PSIG_USER_KEY_LABEL';
5704 l_doc_parameters(2).Param_Name := 'PSIG_USER_KEY_VALUE';
5705 l_doc_parameters(2).Param_Value := l_document_number;
5706 l_doc_parameters(2).Param_displayname := 'PSIG_USER_KEY_VALUE';
5707
5708 l_sig_parameters(1).Param_Name := 'SIGNERS_COMMENT';
5709 l_sig_parameters(1).Param_Value := l_acceptance_note;
5710 l_sig_parameters(1).Param_displayname := 'Signer Comment';
5711 l_sig_parameters(2).Param_Name := 'REASON_CODE';
5712 l_sig_parameters(2).Param_Value := l_reason_code;
5713 l_sig_parameters(2).Param_displayname := '';
5714 l_sig_parameters(3).Param_Name := 'WF_SIGNER_TYPE';
5715 l_sig_parameters(3).Param_Value := l_signer_type;
5716 l_sig_parameters(3).Param_displayname := '';
5717
5718 IF (g_po_wf_debug = 'Y') THEN
5719 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head || ' 002 ');
5720 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head || ' l_response_code ' || l_response_code
5721 || ' l_reason_code' || l_reason_code || ' l_signer_type' || l_signer_type || ' l_event_name' || l_event_name
5722 || ' l_requester' ||l_requester || ' l_user_name' || l_user_name || ' l_notif_id ' || l_notif_id) ;
5723 END IF;
5724
5725 -- Calling capture_signature API to store the eRecord
5726 PO_ERECORDS_PVT.capture_signature (
5727 p_api_version => 1.0,
5728 p_init_msg_list => FND_API.G_FALSE,
5729 p_commit => FND_API.G_FALSE,
5730 x_return_status => l_return_status,
5731 x_msg_count => l_msg_count,
5732 x_msg_data => l_msg_data,
5733 p_psig_xml => NULL,
5734 p_psig_document => NULL,
5735 p_psig_docFormat => 'HTML',
5736 p_psig_requester => l_requester,
5737 p_psig_source => 'SSWA',
5738 p_event_name => l_event_name,
5739 p_event_key => (l_document_number||'-'||l_revision),
5740 p_wf_notif_id => l_notif_id,
5741 x_document_id => l_erecord_id,
5742 p_doc_parameters_tbl => l_doc_parameters,
5743 p_user_name => l_user_name,
5744 p_original_recipient => NULL,
5745 p_overriding_comment => NULL,
5746 x_signature_id => l_signature_id,
5747 p_evidenceStore_id => l_evidence_store_id,
5748 p_user_response => l_response_code,
5749 p_sig_parameters_tbl => l_sig_parameters);
5750
5751
5752 IF l_return_status <> FND_API.G_RET_STS_SUCCESS then
5753 RAISE l_erecords_exception;
5754 END IF;
5755
5756 IF l_erecord_id IS NULL THEN
5757 l_trans_status := 'ERROR';
5758 ELSE
5759 l_trans_status := 'SUCCESS';
5760 END IF;
5761
5762 PO_ERECORDS_PVT.send_ackn
5763 ( p_api_version => 1.0,
5764 p_init_msg_list => FND_API.G_FALSE,
5765 x_return_status => l_return_status,
5766 x_msg_count => l_msg_count,
5767 x_msg_data => l_msg_data,
5768 p_event_name => l_event_name,
5769 p_event_key => (l_document_number||'-'||l_revision),
5770 p_erecord_id => l_erecord_id,
5771 p_trans_status => l_trans_status,
5772 p_ackn_by => l_user_name,
5773 p_ackn_note => l_acceptance_note,
5774 p_autonomous_commit => FND_API.G_FALSE);
5775
5776 IF (g_po_wf_debug = 'Y') THEN
5777 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head || ' 003');
5778 END IF;
5779
5780 IF l_return_status <> 'S' THEN
5781 RAISE l_erecords_exception;
5782 END IF;
5783
5784 po_wf_util_pkg.SetItemAttrNumber (aname => 'ERECORD_ID', avalue => l_erecord_id);
5785 po_wf_util_pkg.SetItemAttrNumber(aname => 'SIG_ID', avalue => l_signature_id);
5786
5787 EXCEPTION
5788 WHEN l_erecords_exception then
5789 IF (g_po_wf_debug = 'Y') THEN
5790 PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey, 'End erecords_exception:PO_AME_WF_PVT.CREATE_ERECORD ');
5791 PO_WF_DEBUG_PKG.INSERT_DEBUG(itemtype, itemkey, 'ERROR RETURNED '||l_msg_data || 'error is ' || SQLERRM || ' code is ' || SQLCODE);
5792 END IF;
5793 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemtype, itemkey);
5794 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemtype, itemkey);
5795 wf_core.context('PO_AME_WF_PVT', 'Create_Erecord', 'l_erecords_exception');
5796
5797 PO_REQAPPROVAL_INIT1.send_error_notif(itemtype, itemkey, l_preparer_user_name, l_doc_string, l_msg_data,'PO_AME_WF_PVT.Create_Erecord', l_document_id);
5798 RAISE;
5799 END CREATE_ERECORD;
5800
5801 --------------------------------------------------------------------------------
5802 --Start of Comments
5803 --Name: check_for_esigner_exists
5804 --Pre-reqs:
5805 -- None.
5806 --Modifies:
5807 -- None.
5808 --Locks:
5809 -- None.
5810 --Function:
5811 -- Workflow activity PL/SQL handler.
5812 -- This is uesd to determine e-signer/post approvers exists or not.
5813 -- Fetch value for attribute E_SIGNER_EXISTS.
5814 --Parameters:
5815 --IN:
5816 -- Standard workflow IN parameters
5817 --OUT:
5818 -- Standard workflow OUT parameters
5819 --Testing:
5820 -- None
5821 --End of Comments
5822 ---------------------------------------------------------------------------------
5823
5824 PROCEDURE check_for_esigner_exists(
5825 itemtype IN VARCHAR2,
5826 itemkey IN VARCHAR2,
5827 actid IN NUMBER,
5828 funcmode IN VARCHAR2,
5829 resultout OUT NOCOPY VARCHAR2)
5830 IS
5831 l_esigner_exists VARCHAR2(1);
5832 BEGIN
5833 -- Logic :
5834 -- + Check worflow attribute for 'E_SIGNER_EXISTS' and return
5835
5836 IF (funcmode <> wf_engine.eng_run) THEN
5837 resultout := wf_engine.eng_null;
5838 RETURN;
5839 END IF;
5840
5841 --Set the global attributes in the po wrapper function
5842 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
5843 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
5844
5845 --Logic is to check for workflow attribute 'E_SIGNER_EXISTS'
5846 l_esigner_exists := po_wf_util_pkg.GetItemAttrText (aname => 'E_SIGNER_EXISTS');
5847 IF (g_po_wf_debug = 'Y') THEN
5848 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, 'PO_AME_WF_PVT.check_for_esigner_exists: ' || l_esigner_exists);
5849 END IF;
5850 resultout := wf_engine.eng_completed || ':' || l_esigner_exists;
5851
5852 END check_for_esigner_exists;
5853
5854 --------------------------------------------------------------------------------
5855 --Start of Comments
5856 --Name: update_pending_signature
5857 --Pre-reqs:
5858 -- None.
5859 --Modifies:
5860 -- None.
5861 --Locks:
5862 -- None.
5863 --Function:
5864 -- Workflow activity PL/SQL handler.
5865 -- This procedures update pending_signature_flag in po_headers_all
5866 -- to 'E' if there are esigners and supplier signature is not required.
5867 --Parameters:
5868 --IN:
5869 -- Standard workflow IN parameters
5870 --OUT:
5871 -- Standard workflow OUT parameters
5872 --Testing:
5873 -- None
5874 --End of Comments
5875 ---------------------------------------------------------------------------------
5876
5877 PROCEDURE update_pending_signature(
5878 itemtype IN VARCHAR2,
5879 itemkey IN VARCHAR2,
5880 p_po_header_id IN NUMBER)
5881 IS
5882 l_api_name VARCHAR2(100) := 'update_pending_signature';
5883 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
5884 PRAGMA AUTONOMOUS_TRANSACTION;
5885 BEGIN
5886 -- Logic :
5887 -- + Update po_headers_all with pending_signature_flag = E which signifies there are
5888 -- post approvers/ e-sigenrs for this PO.
5889 IF (g_po_wf_debug = 'Y') THEN
5890 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' 001');
5891 END IF;
5892
5893 UPDATE po_headers_all
5894 SET pending_signature_flag='E'
5895 WHERE po_header_id = p_po_header_id
5896 AND NVL(acceptance_required_flag,'N') <>'S';
5897
5898 IF (g_po_wf_debug = 'Y') THEN
5899 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey,l_log_head || ' no os rows updated' || SQL%ROWCOUNT);
5900 END IF;
5901
5902 COMMIT;
5903
5904 EXCEPTION
5905 WHEN OTHERS THEN
5906 ROLLBACK;
5907 RAISE;
5908 END update_pending_signature;
5909
5910 --------------------------------------------------------------------------------
5911 --Start of Comments
5912 --Name: update_auth_status_approve
5913 --Pre-reqs:
5914 -- None.
5915 --Modifies:
5916 -- None.
5917 --Locks:
5918 -- None.
5919 --Function:
5920 -- Workflow activity PL/SQL handler.
5921 -- This procedures update authorization_satus to APPROVED after esigners process is complete
5922 --Parameters:
5923 --IN:
5924 -- Standard workflow IN parameters
5925 --OUT:
5926 -- Standard workflow OUT parameters
5927 --Testing:
5928 -- None
5929 --End of Comments
5930 ---------------------------------------------------------------------------------
5931
5932 PROCEDURE update_auth_status_approve(
5933 p_document_id IN NUMBER,
5934 p_item_type IN VARCHAR2,
5935 p_item_key IN VARCHAR2)
5936 IS
5937 PRAGMA AUTONOMOUS_TRANSACTION;
5938 l_user_id NUMBER;
5939 l_login_id NUMBER;
5940 l_api_name VARCHAR2(500) := 'update_auth_status_approve';
5941 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
5942
5943 BEGIN
5944 -- Logic :
5945 -- + Update po_headers_all status to APPROVED and corresponding fields.
5946 -- + Update po_line_locations_all for approved flag
5947
5948 IF (g_po_wf_debug = 'Y') THEN
5949 PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key, l_log_head || ' 001');
5950 END IF;
5951
5952 PO_WF_UTIL_PKG.G_ITEM_TYPE := p_item_type;
5953 PO_WF_UTIL_PKG.G_ITEM_KEY := p_item_key;
5954
5955 l_user_id := fnd_global.user_id;
5956 l_login_id := fnd_global.login_id;
5957
5958 IF (g_po_wf_debug = 'Y') THEN
5959 PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key, l_log_head || ' 002 Updating status for Userid-' || l_user_id
5960 || ' LoginId-' || l_login_id || ' and DocumentId' || p_document_id);
5961 END IF;
5962
5963 UPDATE po_headers poh
5964 SET poh.authorization_status = po_document_action_pvt.g_doc_status_approved
5965 ,poh.approved_flag = 'Y'
5966 ,poh.approved_date = sysdate
5967 ,poh.last_update_date = sysdate
5968 ,poh.last_updated_by = l_user_id
5969 ,poh.last_update_login = l_login_id
5970 ,poh.pending_signature_flag = 'N'
5971 ,poh.acceptance_required_flag = 'N'
5972 ,acceptance_due_date = Null
5973 WHERE poh.po_header_id = p_document_id;
5974
5975 IF (g_po_wf_debug = 'Y') THEN
5976 PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key, l_log_head || ' 003 : After updating po_headers_all ');
5977 END IF;
5978
5979 UPDATE po_line_locations_all poll
5980 SET poll.approved_flag = 'Y'
5981 ,poll.approved_date = sysdate
5982 ,poll.last_update_date = sysdate
5983 ,poll.last_updated_by = l_user_id
5984 ,poll.last_update_login = l_login_id
5985 WHERE poll.po_header_id = p_document_id
5986 AND poll.po_release_id IS NULL
5987 AND nvl (poll.approved_flag,'N') <> 'Y';
5988
5989 IF (g_po_wf_debug = 'Y') THEN
5990 PO_WF_DEBUG_PKG.insert_debug(p_item_type,p_item_key, l_log_head || ' After updating po_line_locations_all' );
5991 END IF;
5992
5993 COMMIT;
5994
5995 END update_auth_status_approve;
5996
5997 --------------------------------------------------------------------------------
5998 --Start of Comments
5999 --Name: update_auth_status_esign
6000 --Pre-reqs:
6001 -- None.
6002 --Modifies:
6003 -- None.
6004 --Locks:
6005 -- None.
6006 --Function:
6007 -- Workflow activity PL/SQL handler.
6008 -- This procedures update authorization_satus to APPROVED after esigners process is complete
6009 --Parameters:
6010 --IN:
6011 -- Standard workflow IN parameters
6012 --OUT:
6013 -- Standard workflow OUT parameters
6014 --Testing:
6015 -- None
6016 --End of Comments
6017 ---------------------------------------------------------------------------------
6018
6019 PROCEDURE update_auth_status_esign(
6020 itemtype IN VARCHAR2,
6021 itemkey IN VARCHAR2,
6022 actid IN NUMBER,
6023 funcmode IN VARCHAR2,
6024 resultout OUT NOCOPY VARCHAR2)
6025 IS
6026 l_document_id NUMBER;
6027 l_document_type PO_DOCUMENT_TYPES.DOCUMENT_TYPE_CODE%TYPE;
6028 l_document_subtype PO_DOCUMENT_TYPES.DOCUMENT_SUBTYPE%TYPE;
6029 l_doc_string VARCHAR2(200);
6030 l_preparer_user_name FND_USER.USER_NAME%TYPE;
6031 l_revision_num NUMBER;
6032 l_api_name VARCHAR2(500) := 'update_pending_signature';
6033 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
6034 l_return_status VARCHAR2(1);
6035 l_msg_count NUMBER;
6036 l_msg_data VARCHAR2(2000);
6037 l_acceptance_date DATE;
6038 x_error_msg VARCHAR2(2000);
6039 l_itemkey PO_HEADERS_ALL.wf_item_key%TYPE;
6040 l_binding_exception EXCEPTION;
6041 l_progress VARCHAR2(3);
6042 BEGIN
6043 -- Logic :
6044 -- + call update_auth_status_approve to update base tables.
6045 -- + if there were any acceptances like supplier siganture, need to update
6046 -- contract termss
6047
6048 IF (g_po_wf_debug = 'Y') THEN
6049 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' 001');
6050 END IF;
6051
6052 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
6053 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
6054
6055 l_document_id := po_wf_util_pkg.GetItemAttrNumber(aname => 'DOCUMENT_ID');
6056 l_document_type := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_TYPE');
6057 l_document_subtype := po_wf_util_pkg.GetItemAttrText( aname => 'DOCUMENT_SUBTYPE');
6058 l_revision_num := po_wf_util_pkg.GetItemAttrNumber( aname => 'REVISION_NUMBER');
6059
6060 l_progress := '001';
6061 IF (g_po_wf_debug = 'Y') THEN
6062 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' 002 Updating status DocumentId' || l_document_id);
6063 END IF;
6064
6065 update_auth_status_approve(
6066 p_document_id => l_document_id,
6067 p_item_type => itemtype,
6068 p_item_key => itemkey);
6069
6070
6071 l_progress := '002';
6072 PO_DELREC_PVT.create_update_delrec (
6073 p_api_version => 1.0,
6074 x_return_status => l_return_status,
6075 x_msg_count => l_msg_count,
6076 x_msg_data => l_msg_data,
6077 p_action => 'APPROVE',
6078 p_doc_type => l_document_type,
6079 p_doc_subtype => l_document_subtype,
6080 p_doc_id => l_document_id,
6081 p_line_id => NULL,
6082 p_line_location_id => NULL);
6083
6084 l_progress := '003';
6085 -- Check whether supplier accpetance was recorded in case document was signed by supplier.
6086 -- If yes process document in same way as it is doen PO_SIGNATURE_PVT.POST_SIGNATURE, UPDATE_PO_DETAILS
6087 BEGIN
6088 SELECT max(action_date)
6089 INTO l_acceptance_date
6090 FROM PO_ACCEPTANCES
6091 WHERE Po_Header_Id = l_document_id
6092 AND Revision_Num = l_revision_num
6093 AND Signature_Flag = 'Y'
6094 AND ACCEPTING_PARTY IN ('B','S')
6095 AND ACCEPTED_FLAG= 'Y';
6096
6097 -- Inform Contracts to activate deliverable, now that PO is successfully
6098 -- Changed status to approved
6099
6100 l_progress := '004';
6101 PO_CONTERMS_WF_PVT.UPDATE_CONTRACT_TERMS(
6102 p_po_header_id => l_document_id,
6103 p_signed_date => l_acceptance_date,
6104 x_return_status => l_return_status,
6105 x_msg_data => l_msg_data,
6106 x_msg_count => l_msg_count);
6107
6108 IF l_return_status <> 'S' then
6109 x_error_msg := l_msg_data;
6110 RAISE l_binding_exception;
6111 END IF;
6112
6113 l_progress := '005';
6114 PO_SIGNATURE_PVT.find_item_key(
6115 p_po_header_id => l_document_id,
6116 p_revision_num => l_revision_num,
6117 p_document_type => l_document_type,
6118 x_itemkey => l_itemkey,
6119 x_result => l_return_status);
6120
6121 IF l_return_status = 'S' AND l_itemkey IS NOT NULL THEN
6122 PO_SIGNATURE_PVT.abort_doc_sign_process(
6123 p_itemkey => l_itemkey,
6124 x_result => l_return_status);
6125 ELSIF l_return_status = 'E' THEN
6126 x_error_msg := 'PO_MANY_SIGN_PROCESSES';
6127 RAISE l_binding_exception;
6128 END IF;
6129
6130 EXCEPTION
6131 WHEN NO_DATA_FOUND THEN
6132 NULL;
6133 END;
6134
6135 EXCEPTION
6136 WHEN l_binding_exception THEN
6137 IF (g_po_wf_debug = 'Y') THEN
6138 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' in binding excpetion with error messsage ' || x_error_msg);
6139 END IF;
6140
6141 WHEN OTHERS THEN
6142 l_doc_string := PO_REQAPPROVAL_INIT1.get_error_doc(itemtype, itemkey);
6143 l_preparer_user_name := PO_REQAPPROVAL_INIT1.get_preparer_user_name(itemtype, itemkey);
6144 WF_CORE.context(g_pkg_name, l_api_name, l_progress, sqlerrm);
6145 PO_REQAPPROVAL_INIT1.send_error_notif(itemtype, itemkey, l_preparer_user_name, l_doc_string, sqlerrm, l_progress, l_document_id);
6146 IF (g_po_wf_debug = 'Y') THEN
6147 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head||':'||l_progress);
6148 END IF;
6149 RAISE;
6150 END update_auth_status_esign;
6151
6152 -------------------------------------------------------------------------------
6153 --Start of Comments
6154 --Name: trigger_approval_workflow
6155 --Pre-reqs:
6156 -- None.
6157 --Modifies:
6158 -- None.
6159 --Locks:
6160 -- None.
6161 --Function:
6162 -- This function triggers PO approval workflow block activity
6163 -- from Document Signature Process
6164 --Parameters:
6165 --IN:
6166 --itemtype
6167 -- Standard parameter to be used in a workflow procedure
6168 --itemkey
6169 -- Standard parameter to be used in a workflow procedure
6170 --actid
6171 -- Standard parameter to be used in a workflow procedure
6172 --funcmode
6173 -- Standard parameter to be used in a workflow procedure
6174 --OUT:
6175 --resultout
6176 -- Standard parameter to be used in a workflow procedure
6177 --Testing:
6178 -- Testing to be done based on the test cases in Document Binding DLD
6179 --End of Comments
6180 -------------------------------------------------------------------------------
6181
6182 PROCEDURE trigger_approval_workflow(
6183 itemtype IN VARCHAR2,
6184 itemkey IN VARCHAR2,
6185 actid IN NUMBER,
6186 funcmode IN VARCHAR2,
6187 resultout OUT NOCOPY VARCHAR2)
6188 IS
6189 l_document_id NUMBER;
6190 l_api_name VARCHAR2(500) := 'trigger_approval_workflow';
6191 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
6192 l_result VARCHAR2(1);
6193 l_po_itemkey PO_HEADERS_ALL.wf_item_key%TYPE;
6194
6195 BEGIN
6196
6197 -- Logic :
6198 -- + Call PO_SIGNATURE_PVT.Complete_Block_Activities to complete block activity in PO Approval workflow
6199 -- after supplier siganture part is done.
6200
6201 IF (g_po_wf_debug = 'Y') THEN
6202 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' 001');
6203 END IF;
6204
6205 po_wf_util_pkg.g_item_type := itemtype;
6206 po_wf_util_pkg.g_item_key := itemkey;
6207
6208 l_document_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'DOCUMENT_ID');
6209
6210 -- Fetch PO approval workflow item key from pbase table.
6211 SELECT wf_item_key
6212 INTO l_po_itemkey
6213 FROM po_headers_all poh
6214 WHERE poh.po_header_id = l_document_id;
6215
6216 -- Completes the Blocked Activities in the PO Approval process
6217 PO_SIGNATURE_PVT.Complete_Block_Activities(p_itemkey => l_po_itemkey,
6218 p_status => 'Y',
6219 x_result => l_result);
6220
6221 IF (g_po_wf_debug = 'Y') THEN
6222 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' 002 result : ' || l_result);
6223 END IF;
6224
6225 resultout := wf_engine.eng_completed;
6226 END trigger_approval_workflow;
6227
6228 -------------------------------------------------------------------------------
6229 --Start of Comments
6230 --Name: suppress_existing_esigners
6231 --Pre-reqs:
6232 -- None.
6233 --Modifies:
6234 -- None.
6235 --Locks:
6236 -- None.
6237 --Function:
6238 -- This function is called only if supplier rejected signature.
6239 -- It supresses if they were any post-approvers/ e-signers.
6240 --Parameters:
6241 --IN:
6242 --itemtype
6243 -- Standard parameter to be used in a workflow procedure
6244 --itemkey
6245 -- Standard parameter to be used in a workflow procedure
6246 --actid
6247 -- Standard parameter to be used in a workflow procedure
6248 --funcmode
6249 -- Standard parameter to be used in a workflow procedure
6250 --OUT:
6251 --resultout
6252 -- Standard parameter to be used in a workflow procedure
6253 --Testing:
6254 -- Testing to be done based on the test cases in Document Binding DLD
6255 --End of Comments
6256 -------------------------------------------------------------------------------
6257
6258 PROCEDURE suppress_existing_esigners(
6259 itemtype IN VARCHAR2,
6260 itemkey IN VARCHAR2,
6261 actid IN NUMBER,
6262 funcmode IN VARCHAR2,
6263 resultout OUT NOCOPY VARCHAR2)
6264 IS
6265 l_api_name VARCHAR2(500) := 'suppress_existing_esigners';
6266 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
6267 l_esigner_exists VARCHAR2(1);
6268
6269 BEGIN
6270 -- Logic :
6271 -- 1. For post-approvers/e-signers where ameStatus is still NULL, we need to supress them.
6272 -- This function is called only if supplier signature was rejected.
6273
6274 IF (g_po_wf_debug = 'Y') THEN
6275 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' 001 ');
6276 END IF;
6277 --Set the global attributes in the po wrapper function
6278 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
6279 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
6280
6281 l_esigner_exists := po_wf_util_pkg.GetItemAttrText(aname => 'E_SIGNER_EXISTS');
6282
6283 IF (g_po_wf_debug = 'Y') THEN
6284 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' l_esigners_exists ' || l_esigner_exists);
6285 END IF;
6286
6287 IF l_esigner_exists = 'Y' THEN
6288 BEGIN
6289 supress_existing_approvers(
6290 itemtype => itemtype,
6291 itemkey => itemkey);
6292 EXCEPTION
6293 WHEN OTHERS THEN
6294 NULL;
6295 END;
6296
6297 IF (g_po_wf_debug = 'Y') THEN
6298 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' l_esigners_exists ' || l_esigner_exists);
6299 END IF;
6300
6301 END IF;
6302
6303 resultout := wf_engine.eng_completed;
6304
6305 END suppress_existing_esigners;
6306
6307 -------------------------------------------------------------------------------
6308 --Start of Comments
6309 --Name: supress_existing_approvers
6310 --Pre-reqs:
6311 -- None.
6312 --Modifies:
6313 -- None.
6314 --Locks:
6315 -- None.
6316 --Function:
6317 -- This function is called if documnet is returned to supress existing approvers
6318 --IN:
6319 --itemtype
6320 -- Standard parameter to be used in a workflow procedure
6321 --itemkey
6322 -- Standard parameter to be used in a workflow procedure
6323 --actid
6324 -- Standard parameter to be used in a workflow procedure
6325 --funcmode
6326 -- Standard parameter to be used in a workflow procedure
6327 --OUT:
6328 --resultout
6329 -- Standard parameter to be used in a workflow procedure
6330 --Testing:
6331 -- Testing to be done based on the test cases in Document Binding DLD
6332 --End of Comments
6333 -------------------------------------------------------------------------------
6334 PROCEDURE supress_existing_approvers(
6335 itemtype IN VARCHAR2,
6336 itemkey IN VARCHAR2)
6337 IS
6338 l_api_name VARCHAR2(500) := 'supress_existing_approvers';
6339 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
6340 l_approver_list ame_util.approversTable2;
6341 xprocess_out VARCHAR2(1);
6342 l_transaction_type PO_DOCUMENT_TYPES.AME_TRANSACTION_TYPE%TYPE;
6343 l_transaction_id NUMBER;
6344 l_approver_index NUMBER;
6345
6346 BEGIN
6347 -- Logic :
6348 -- 1. For post-approvers/reviewers/approvers where ameStatus is still NULL or notified, we need to supress them.
6349 -- 2. Here we fetch all approvers through ame_api2.getAllApprovers7. Then check for approvers
6350 -- whose status is NULL, means for them AME routing havent been strated yet.
6351 -- 3. Call ame_api3.suppressApprover to suppress such approvers.
6352 -- We set-reset dynamic profile 'PO_SYS_GENERATED_APPROVERS_SUPPRESS', to override AME mandatory
6353 -- attribute ALLOW_DELETING_RULE_GENERATED_APPROVERS.
6354
6355 IF (g_po_wf_debug = 'Y') THEN
6356 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' 001 ');
6357 END IF;
6358 --Set the global attributes in the po wrapper function
6359 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
6360 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
6361
6362 l_transaction_type := po_wf_util_pkg.GetItemAttrText( aname => 'AME_TRANSACTION_TYPE');
6363 l_transaction_id := po_wf_util_pkg.GetItemAttrNumber( aname => 'AME_TRANSACTION_ID');
6364
6365 IF l_transaction_id IS NOT NULL AND l_transaction_type IS NOT NULL THEN
6366 ame_api2.getAllApprovers7(applicationIdIn => applicationId,
6367 transactionTypeIn => l_transaction_type,
6368 transactionIdIn => l_transaction_id,
6369 approvalProcessCompleteYNOut => xprocess_out,
6370 approversOut => l_approver_list);
6371
6372 l_approver_index := l_approver_list.first();
6373
6374 WHILE ( l_approver_index IS NOT NULL ) LOOP
6375 IF (g_po_wf_debug = 'Y') THEN
6376 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' supressing approver name ' || l_approver_list(l_approver_index).name);
6377 END IF;
6378
6379 IF l_approver_list(l_approver_index).approval_status IS NULL OR
6380 l_approver_list(l_approver_index).approval_status IN (ame_util.notifiedStatus, ame_util.notifiedByRepeatedStatus) THEN
6381 fnd_profile.put('PO_SYS_GENERATED_APPROVERS_SUPPRESS', 'Y');
6382 ame_api3.suppressApprover( applicationIdIn => applicationId,
6383 transactionIdIn => l_transaction_id,
6384 transactionTypeIn => l_transaction_type,
6385 approverIn => l_approver_list(l_approver_index));
6386 fnd_profile.put('PO_SYS_GENERATED_APPROVERS_SUPPRESS', 'N');
6387 END IF; -- l_approver_index loop
6388 l_approver_index := l_approver_list.next(l_approver_index);
6389 END LOOP;
6390 END IF; -- l_transaction_id IS NOT NULL AND l_transaction_type IS NOT NULL
6391 END supress_existing_approvers;
6392
6393 -------------------------------------------------------------------------------
6394 --Start of Comments
6395 --Name: complete_ame_transaction
6396 --Pre-reqs:
6397 -- None.
6398 --Modifies:
6399 -- None.
6400 --Locks:
6401 -- None.
6402 --Function:
6403 -- This function is called if documnet is returned to supress exixting approvers and
6404 -- set new ame_approval_id.
6405 --IN:
6406 --itemtype
6407 -- Standard parameter to be used in a workflow procedure
6408 --itemkey
6409 -- Standard parameter to be used in a workflow procedure
6410 --actid
6411 -- Standard parameter to be used in a workflow procedure
6412 --funcmode
6413 -- Standard parameter to be used in a workflow procedure
6414 --OUT:
6415 --resultout
6416 -- Standard parameter to be used in a workflow procedure
6417 --Testing:
6418 -- Testing to be done based on the test cases in Document Binding DLD
6419 --End of Comments
6420 -------------------------------------------------------------------------------
6421
6422 PROCEDURE complete_ame_transaction(
6423 itemtype IN VARCHAR2,
6424 itemkey IN VARCHAR2,
6425 actid IN NUMBER,
6426 funcmode IN VARCHAR2,
6427 resultout OUT NOCOPY VARCHAR2)
6428 IS
6429 l_api_name VARCHAR2(500) := 'complete_ame_transaction';
6430 l_log_head VARCHAR2(500) := g_module_prefix||l_api_name;
6431 l_document_id NUMBER;
6432 l_return_message VARCHAR2(1000);
6433
6434 BEGIN
6435 IF (g_po_wf_debug = 'Y') THEN
6436 PO_WF_DEBUG_PKG.insert_debug(itemtype,itemkey, l_log_head || ' 001 ');
6437 END IF;
6438 --Set the global attributes in the po wrapper function
6439 PO_WF_UTIL_PKG.G_ITEM_TYPE := itemtype;
6440 PO_WF_UTIL_PKG.G_ITEM_KEY := itemkey;
6441
6442 l_document_id := po_wf_util_pkg.GetItemAttrNumber(aname => 'DOCUMENT_ID');
6443
6444 BEGIN
6445 supress_existing_approvers(
6446 itemtype => itemtype,
6447 itemkey => itemkey);
6448 EXCEPTION
6449 WHEN OTHERS THEN
6450 NULL;
6451 END;
6452
6453 reset_authorization_status(
6454 p_document_id => l_document_id,
6455 p_item_type => itemtype,
6456 p_item_key => itemkey,
6457 x_return_message => l_return_message);
6458
6459 resultout := wf_engine.eng_completed;
6460
6461 END complete_ame_transaction;
6462
6463 END PO_AME_WF_PVT;