[Home] [Help]
PACKAGE BODY: APPS.OE_APPROVALS_WF
Source
1 PACKAGE BODY OE_APPROVALS_WF AS
2 /* $Header: OEXWAPRB.pls 120.6.12020000.8 2013/03/25 08:54:53 spothula ship $ */
3
4 -- Start of Comments
5 -- API name OE_APPROVALS_WF
6 -- Type Public
7 -- Version Current version = 1.0
8 -- Initial version = 1.0
9
10 /*=======================*/
11 /* Private procedures */
12 /*=======================*/
13
14 g_defer_min CONSTANT NUMBER := 5; --bug7386039
15
16 /**********************
17 * get_user_id *
18 **********************/
19 function get_user_id
20 return number
21 IS
22 BEGIN
23 return NVL(FND_GLOBAL.USER_ID, -1);
24 END get_user_id;
25
26
27
28
29 /****************************
30 * Initiate_Approval *
31 ****************************/
32 Procedure Initiate_Approval
33 (itemtype in varchar2,
34 itemkey in varchar2,
35 actid in number,
36 funcmode in varchar2,
37 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
38 IS
39
40 l_transaction_id NUMBER;
41 l_role VARCHAR2(240);
42 l_sales_document_type_code VARCHAR2(30);
43
44 l_attachment_location VARCHAR2(240); --??
45 l_msg_count NUMBER;
46 l_msg_data VARCHAR2(240);
47 l_return_status VARCHAR2(30);
48
49 l_sold_to_org_id NUMBER;
50 l_salesrep_id NUMBER;
51 l_salesrep VARCHAR2(240);
52 l_sold_to VARCHAR2(240);
53 --l_customer_number NUMBER;
54 l_customer_number varchar2(30) ;-- bug4575846
55 l_expiration_date DATE;
56
57 l_aname wf_engine.nametabtyp;
58 l_avaluetext wf_engine.texttabtyp;
59
60 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
61 BEGIN
62
63 --
64 -- RUN mode - normal process execution
65 --
66 if (funcmode = 'RUN') then
67
68 l_transaction_id := to_number(itemkey);
69
70 OE_STANDARD_WF.Set_Msg_Context(actid);
71 OE_MSG_PUB.set_msg_context(
72 p_entity_code => 'HEADER'
73 ,p_entity_id => to_number(itemkey)
74 ,p_header_id => to_number(itemkey));
75
76 IF l_debug_level > 0 THEN
77 oe_debug_pub.add( 'Initiate_Approval ', 1) ;
78 END IF;
79
80 -- Delete any previous approval transaction data.
81 DELETE
82 FROM OE_APPROVER_TRANSACTIONS
83 WHERE TRANSACTION_ID = l_transaction_id;
84
85 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
86 l_sales_document_type_code := wf_engine.GetItemAttrText(
87 OE_GLOBALS.G_WFI_NGO,
88 l_transaction_id,
89 'SALES_DOCUMENT_TYPE_CODE');
90 END IF;
91
92 -- CALL THE GET_NEXT_APPROVER to get the first approver
93 l_role := Get_Next_Approver_internal(l_transaction_id,
94 itemtype,
95 l_sales_document_type_code);
96 IF l_debug_level > 0 THEN
97 oe_debug_pub.add( 'Role->' || l_role, 1) ;
98 END IF;
99
100
101 if l_role is NULL then
102 IF l_debug_level > 0 THEN
103 oe_debug_pub.add('Role is null. Set transaction to Not Eligible', 1 ) ;
104 END IF;
105
106
107 /* OE_ORDER_WF_UTIL.Update_Flow_Status_Code
108 (p_item_type => itemtype,
109 p_header_id => l_transaction_id,
110 p_flow_status_code => 'INTERNAL_APPROVED',
111 p_sales_document_type_code => l_sales_document_type_code,
112 x_return_status => l_return_status );
113
114 IF l_debug_level > 0 THEN
115 oe_debug_pub.add('Initiate_approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
116 END IF;
117
118 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
119 OE_STANDARD_WF.Save_Messages;
120 OE_STANDARD_WF.Clear_Msg_Context;
121 app_exception.raise_exception;
122 END IF;
123
124 */
125
126 resultout := 'COMPLETE:NOT_ELIGIBLE';
127 OE_STANDARD_WF.Save_Messages;
128 OE_STANDARD_WF.Clear_Msg_Context;
129 return;
130
131 else
132 IF l_debug_level > 0 THEN
133 oe_debug_pub.add('Setting the role to->' || l_role, 1 ) ;
134 END IF;
135
136 wf_engine.SetItemAttrText(itemtype,
137 itemkey,
138 'NOTIFICATION_APPROVER',
139 l_role);
140
141 IF l_debug_level > 0 THEN
142 oe_debug_pub.add('Before calling OE_CONTRACTS_UTIL.attachment_location.. ', 1);
143 END IF;
144
145 OE_CONTRACTS_UTIL.attachment_location
146 (p_api_version => 1.0,
147 p_doc_type => l_sales_document_type_code,
148 p_doc_id => l_transaction_id,
149 x_workflow_string => l_attachment_location,
150 x_return_status => l_return_status,
151 x_msg_count => l_msg_count,
152 x_msg_data => l_msg_data );
153
154
155 IF l_debug_level > 0 THEN
156 oe_debug_pub.add('l_attachment_location->' || l_attachment_location, 1);
157 oe_debug_pub.add('l_return_status->' || l_return_status, 1);
158 oe_debug_pub.add('l_msg_data->' || l_msg_data, 1);
159 END IF;
160
161 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
162 -- start data fix project
163 -- OE_STANDARD_WF.Save_Messages;
164 -- OE_STANDARD_WF.Clear_Msg_Context;
165 -- end data fix project
166 app_exception.raise_exception;
167 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
168 -- start data fix project
169 -- OE_STANDARD_WF.Save_Messages;
170 -- OE_STANDARD_WF.Clear_Msg_Context;
171 -- end data fix project
172 app_exception.raise_exception;
173 END IF;
174
175 IF l_attachment_location is NOT NULL THEN
176 wf_engine.SetItemAttrText(itemtype,
177 itemkey,
178 'CONTRACT_ATTACHMENT',
182
179 l_attachment_location);
180 END IF;
181
183 --------------------------------------------------
184 -- Set Header Attributes Values for Negotiation --
185
186 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
187 IF l_sales_document_type_code = 'O' THEN
188 select sold_to_org_id, expiration_date, salesrep_id
189 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
190 from oe_order_headers_all
191 where header_id = to_number(itemkey);
192 ELSE
193 select obha.sold_to_org_id, obhe.end_date_active, obha.salesrep_id
194 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
195 from oe_blanket_headers_all obha, oe_blanket_headers_ext obhe
196 where obha.header_id = to_number(itemkey)
197 and obha.order_number = obhe.order_number;
198 END IF;
199
200 l_salesrep := OE_Id_To_Value.Salesrep(p_salesrep_id=>l_salesrep_id);
201 OE_Id_To_Value.Sold_To_Org(p_sold_to_org_id => l_sold_to_org_id,
202 x_org => l_sold_to,
203 x_customer_number => l_customer_number);
204
205
206 l_aname(1) := 'SALESPERSON';
207 l_avaluetext(1) := l_salesrep;
208 l_aname(2) := 'SOLD_TO';
209 l_avaluetext(2) := l_sold_to;
210 l_aname(3) := 'EXPIRATION_DATE';
211 --Bug 12884612: Use date mask while converting to text
212 l_avaluetext(3) := to_char(l_expiration_date,'DD-MON-RRRR');
213
214 wf_engine.SetItemAttrTextArray( itemtype
215 , itemkey
216 , l_aname
217 , l_avaluetext
218 );
219 -- End setting Header Attributes
220 END IF;
221 -------itemtype = OE_GLOBALS.G_WFI_NGO-------
222
223 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
224 (p_item_type => itemtype,
225 p_header_id => l_transaction_id,
226 p_flow_status_code => 'PENDING_INTERNAL_APPROVAL',
227 p_sales_document_type_code => l_sales_document_type_code,
228 x_return_status => l_return_status );
229
230 IF l_debug_level > 0 THEN
231 oe_debug_pub.add('Initiate_Approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
232 END IF;
233
234 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
235 -- start data fix project
236 -- OE_STANDARD_WF.Save_Messages;
237 -- OE_STANDARD_WF.Clear_Msg_Context;
238 -- end data fix project
239 app_exception.raise_exception;
240 END IF;
241
242
243 resultout := 'COMPLETE:COMPLETE';
244 OE_STANDARD_WF.Clear_Msg_Context;
245 return;
246 end if;
247 end if; -- End for 'RUN' mode
248
249 --
250 -- CANCEL mode - activity 'compensation'
251 --
252 -- This is an event point is called with the effect of the activity must
253 -- be undone, for example when a process is reset to an earlier point
254 -- due to a loop back.
255 --
256 if (funcmode = 'CANCEL') then
257
258 -- your cancel code goes here
259 null;
260
261 -- no result needed
262 resultout := 'COMPLETE';
263 return;
264 end if;
265
266
267 --
268 -- Other execution modes may be created in the future. Your
269 -- activity will indicate that it does not implement a mode
270 -- by returning null
271 --
272 -- resultout := '';
273 -- return;
274
275 exception
276 when others then
277 -- The line below records this function call in the error system
278 -- in the case of an exception.
279 wf_core.context('OE_APPROVALS_WF', 'Initiate_Approval',
280 itemtype, itemkey, to_char(actid), funcmode);
281 -- start data fix project
282 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
283 p_itemtype => itemtype,
284 p_itemkey => itemkey);
285 OE_STANDARD_WF.Save_Messages;
286 OE_STANDARD_WF.Clear_Msg_Context;
287 -- end data fix project
288 raise;
289
290
291 END Initiate_Approval;
292
293
294 /**********************************
295 * Get_Next_Approver *
296 **********************************/
297 /*
298 This procedure sets the NOTIFICATION_APPROVER item attribute based on
299 the definition/setup in the OM Approver List form, insert/update
300 the proper record in the OM Approval transaction table
301 OE_APPROVAL_TRANSACTIONS. Checks the max(approver_sequence) from
302 OE_APPROVAL_TRANSACTIONS given a transaction_id, and then fetches
303 the role from the OE_APPROVER_LISTS with approver_sequence = max+1
304 and insert the record in OE_APPROVER_TRANSACTIONS.
305 Returns Y if it finds the next approver and returns N if there is no
306 approver left. In the case there is no more approvers, it will update
307 the status to APPROVED
308 */
309 Procedure Get_Next_Approver
310 (itemtype in varchar2,
311 itemkey in varchar2,
312 actid in number,
313 funcmode in varchar2,
314 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
315 IS
316
317 l_transaction_id NUMBER;
318 l_role VARCHAR2(240);
319 l_sales_document_type_code VARCHAR2(30);
320
321 l_return_status VARCHAR2(30);
322 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
323
324 l_sold_to_org_id NUMBER;
325 l_salesrep_id NUMBER;
326 l_salesrep VARCHAR2(240);
327 l_sold_to VARCHAR2(240);
328 --l_customer_number NUMBER;
329 l_customer_number varchar2(30); --bug4575846
330 l_expiration_date DATE;
331
332 l_aname wf_engine.nametabtyp;
333 l_avaluetext wf_engine.texttabtyp;
334
335 BEGIN
336
337 --
338 -- RUN mode - normal process execution
339 --
340 if (funcmode = 'RUN') then
341
342 l_transaction_id := to_number(itemkey);
343
344 OE_STANDARD_WF.Set_Msg_Context(actid);
345 OE_MSG_PUB.set_msg_context(
346 p_entity_code => 'HEADER'
347 ,p_entity_id => to_number(itemkey)
348 ,p_header_id => to_number(itemkey));
349
350 IF l_debug_level > 0 THEN
351 oe_debug_pub.add( 'Get_Next_Approver ', 1 ) ;
352 END IF;
353
354 -- We need to set the status of the last approver to APPROVED here
355 -- in the OE_APPROVER_TRANSACTIONS
356 UPDATE OE_APPROVER_TRANSACTIONS
357 SET APPROVAL_STATUS = 'APPROVED'
358 WHERE TRANSACTION_ID = l_transaction_id
359 --- ?? phase code = not really needed
360 AND APPROVER_SEQUENCE = (select max(APPROVER_SEQUENCE)
361 from OE_APPROVER_TRANSACTIONS
362 WHERE TRANSACTION_ID = l_transaction_id);
363
364
365 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
366 l_sales_document_type_code := wf_engine.GetItemAttrText(
367 OE_GLOBALS.G_WFI_NGO,
368 l_transaction_id,
369 'SALES_DOCUMENT_TYPE_CODE');
370 END IF;
371
372 -- CALL THE Get_Next_Approver_internal
373 l_role := Get_Next_Approver_internal(l_transaction_id,
374 itemtype,
375 l_sales_document_type_code);
376 IF l_debug_level > 0 THEN
377 oe_debug_pub.add('Role-> ' || l_role, 1) ;
378 END IF;
379
380
381
382 if l_role is NULL then
383
384 /*
385 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
386 (p_item_type => itemtype,
387 p_header_id => l_transaction_id,
388 p_flow_status_code => 'APPROVED',
389 p_sales_document_type_code => l_sales_document_type_code,
390 x_return_status => l_return_status );
391
392 IF l_debug_level > 0 THEN
393 oe_debug_pub.add('Get_next_approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
394 END IF;
395
396 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
397 OE_STANDARD_WF.Save_Messages;
398 OE_STANDARD_WF.Clear_Msg_Context;
399 app_exception.raise_exception;
400 END IF;
401 */
402
403 resultout := 'COMPLETE:N';
404 OE_STANDARD_WF.Save_Messages;
405 OE_STANDARD_WF.Clear_Msg_Context;
406 return;
407
408 else
409 wf_engine.SetItemAttrText(itemtype,
410 itemkey,
411 'NOTIFICATION_APPROVER',
412 l_role);
413
414
415
416
417 -----------------------------------
418 -- Set Header Attributes Values --
419 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
420 ----???? Join to the value table
421 IF l_sales_document_type_code = 'O' THEN
422 select sold_to_org_id, expiration_date, salesrep_id
423 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
424 from oe_order_headers_all
425 where header_id = l_transaction_id;
426 ELSE
427
428 select obha.sold_to_org_id, obhe.end_date_active, obha.salesrep_id
429 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
430 from oe_blanket_headers_all obha,
431 oe_blanket_headers_ext obhe
432 where obha.header_id = l_transaction_id
433 and obha.order_number = obhe.order_number;
434
435 END IF;
436
437 l_salesrep := OE_Id_To_Value.Salesrep(p_salesrep_id=>l_salesrep_id);
438 OE_Id_To_Value.Sold_To_Org(p_sold_to_org_id => l_sold_to_org_id,
439 x_org => l_sold_to,
440 x_customer_number => l_customer_number);
441
442
443 l_aname(1) := 'SALESPERSON';
444 l_avaluetext(1) := l_salesrep;
445 l_aname(2) := 'SOLD_TO';
446 l_avaluetext(2) := l_sold_to;
447 l_aname(3) := 'EXPIRATION_DATE';
448 --Bug 12884612: Use date mask while converting to text
449 l_avaluetext(3) := to_char(l_expiration_date,'DD-MON-RRRR');
450
451 wf_engine.SetItemAttrTextArray( itemtype
452 , itemkey
453 , l_aname
454 , l_avaluetext
455 );
456 END IF;
457 -- End setting Header Attributes
458
459
460
461 resultout := 'COMPLETE:Y';
462 OE_STANDARD_WF.Save_Messages;
463 OE_STANDARD_WF.Clear_Msg_Context;
464 return;
465 end if;
466
467 end if; -- End for 'RUN' mode
468
469 --
470 -- CANCEL mode - activity 'compensation'
471 --
472 -- This is an event point is called with the effect of the activity must
473 -- be undone, for example when a process is reset to an earlier point
474 -- due to a loop back.
475 --
476 if (funcmode = 'CANCEL') then
477
478 -- your cancel code goes here
479 null;
480
481 -- no result needed
482 resultout := 'COMPLETE';
483 return;
484 end if;
485
486
487 --
488 -- Other execution modes may be created in the future. Your
489 -- activity will indicate that it does not implement a mode
490 -- by returning null
491 --
492 -- resultout := '';
493 -- return;
494
495 exception
496 when others then
497 -- The line below records this function call in the error system
498 -- in the case of an exception.
499 wf_core.context('OE_APPROVALS_WF', 'Get_Next_Approver',
500 itemtype, itemkey, to_char(actid), funcmode);
501 -- start data fix project
502 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
503 p_itemtype => itemtype,
504 p_itemkey => itemkey);
505 OE_STANDARD_WF.Save_Messages;
506 OE_STANDARD_WF.Clear_Msg_Context;
507 -- end data fix project
508 raise;
509
510
511 END Get_Next_Approver;
512
513
514
515 /**********************************
516 * Get_Next_Approver_InternaL *
517 **********************************/
518 /*
519 Gets called from Initiate_approval and Get_next_approval
520 */
521 function Get_Next_Approver_internal (
522 p_transaction_id in NUMBER,
523 p_itemtype in VARCHAR2,
524 p_sales_document_type_code in VARCHAR2,
525 p_query_mode in VARCHAR2 default 'N'
526 )
527 RETURN VARCHAR2
528 IS
529
530 l_role varchar2(320);
531 l_approver_sequence number;
532 l_curr_approver_sequence number;
533 l_list_id NUMBER;
534 l_user_id NUMBER;
535 l_transaction_type_id NUMBER;
536 l_transaction_phase_code VARCHAR2(30);
537
538 l_check_default_list VARCHAR2(1) := 'N';
539
540 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
541
542
543 -- Get the next approver_sequence and its role
544 -- cursor c_get_next_approver (m_cur_seq number, m_list_id number) is
545 -- select ROLE, APPROVER_SEQUENCE
546 -- from OE_APPROVER_LIST_MEMBERS
547 -- where list_id = m_list_id
548 -- and APPROVER_SEQUENCE > m_cur_seq
549 -- and ACTIVE_FLAG = 'Y'
550 -- order by APPROVER_SEQUENCE;
551
552 cursor c_get_next_approver is
553 select ROLE, APPROVER_SEQUENCE
554 from OE_APPROVER_LIST_MEMBERS
555 where list_id = l_list_id
556 and APPROVER_SEQUENCE > l_curr_approver_sequence
557 and ACTIVE_FLAG = 'Y'
558 order by APPROVER_SEQUENCE;
559
560
561 BEGIN
562 IF l_debug_level > 0 THEN
563 oe_debug_pub.add('Get_Next_Approver_internal', 1 ) ;
564 END IF;
565
566 -- Get the User ID
567 l_user_id := OE_APPROVALS_WF.get_user_id;
568
569 -- First get the transaction_type_id and the transaction_phase
570 -- We need to hit different table to find that out
571
572 IF l_debug_level > 0 THEN
573 oe_debug_pub.add('SaleDocumentTypeCode-> ' || p_sales_document_type_code, 1) ;
574 END IF;
575
576 -- If p_sales_document_type_code is not B, it is either a quote or order or line
577 IF nvl(p_sales_document_type_code, 'XXXX') = 'B' THEN
578 select ORDER_TYPE_ID, nvl(TRANSACTION_PHASE_CODE, 'F')
579 into l_transaction_type_id, l_transaction_phase_code
580 from oe_blanket_headers_all
581 where header_id = p_transaction_id;
582 IF l_debug_level > 0 THEN
583 oe_debug_pub.add('Transaction_type_id->' || l_transaction_type_id, 1) ;
584 oe_debug_pub.add('TransactionPhase->' || l_transaction_phase_code, 1);
585 END IF;
586
587 ELSE
588 select ORDER_TYPE_ID, nvl(TRANSACTION_PHASE_CODE, 'F')
589 into l_transaction_type_id, l_transaction_phase_code
590 from oe_order_headers_all
591 where header_id = p_transaction_id;
592 IF l_debug_level > 0 THEN
593 oe_debug_pub.add('Transaction_type_id->' || l_transaction_type_id, 1) ;
594 oe_debug_pub.add('TransactionPhase->' || l_transaction_phase_code, 1);
595 END IF;
596
597 END IF;
598
599 -- Get the approver list_id
600 BEGIN
601 select list_id
602 into l_list_id
603 from OE_APPROVER_LISTS
604 where TRANSACTION_TYPE_ID = l_transaction_type_id
605 and TRANSACTION_PHASE_CODE is not NULL
606 and TRANSACTION_PHASE_CODE = l_transaction_phase_code
607 and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE )
608 AND NVL(END_DATE_ACTIVE, SYSDATE );
609 IF l_debug_level > 0 THEN
610 oe_debug_pub.add('ListID-> ' || l_list_id, 1) ;
611 END IF;
612
613 EXCEPTION
614 when NO_DATA_FOUND then
615 IF l_debug_level > 0 THEN
616 oe_debug_pub.add('No ListID Found ', 1) ;
617 END IF;
618 l_check_default_list := 'Y';
619 end;
620
621
622 if l_check_default_list = 'Y' then
623
624 BEGIN
625 select list_id
626 into l_list_id
627 from OE_APPROVER_LISTS
628 where TRANSACTION_TYPE_ID = l_transaction_type_id
629 and TRANSACTION_PHASE_CODE is NULL
630 and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE )
631 AND NVL(END_DATE_ACTIVE, SYSDATE );
632 IF l_debug_level > 0 THEN
633 oe_debug_pub.add('Default ListID-> ' || l_list_id, 1) ;
634 END IF;
635
636 EXCEPTION
637 when NO_DATA_FOUND then
638 IF l_debug_level > 0 THEN
639 oe_debug_pub.add('No Default ListID Found ', 1) ;
640 END IF;
641 l_role := NULL;
642 return l_role;
643 end;
644
645 end if;
646
647
648 -------------------------------------------
649 -- Get the Max Current APPROVER_SEQUENCE --
650 -------------------------------------------
651 BEGIN
652 select max(APPROVER_SEQUENCE)
653 into l_curr_approver_sequence
654 from OE_APPROVER_TRANSACTIONS
655 where TRANSACTION_ID = p_transaction_id
656 and TRANSACTION_TYPE_ID = l_transaction_type_id
657 and TRANSACTION_PHASE_CODE = l_transaction_phase_code;
658
659 IF l_debug_level > 0 THEN
660 oe_debug_pub.add('Max Curr APPROVER_SEQUENCE-> ' || l_curr_approver_sequence, 1);
661 END IF;
662 IF l_curr_approver_sequence is null Then
663 l_curr_approver_sequence := 0;
664 END IF;
665
666 EXCEPTION
667 when NO_DATA_FOUND then
668 IF l_debug_level > 0 THEN
669 oe_debug_pub.add('Max Curr APPROVER_SEQUENCE-> ' || l_curr_approver_sequence, 1);
670 END IF;
671
672 l_curr_approver_sequence := 0;
673 END;
674
675 -- open c_get_next_approver (m_cur_seq => l_curr_approver_sequence,
679
676 -- m_list_id => l_list_id);
677
678 open c_get_next_approver;
680 FETCH c_get_next_approver
681 INTO l_role, l_approver_sequence;
682
683 if c_get_next_approver%notfound then
684 IF l_debug_level > 0 THEN
685 oe_debug_pub.add('No Role ', 1) ;
686 END IF;
687
688 l_role := NULL;
689 CLOSE c_get_next_approver;
690 return l_role;
691
692 end if;
693
694 CLOSE c_get_next_approver;
695 oe_debug_pub.add('Role-> ' || l_role, 1) ;
696
697
698 IF p_query_mode = 'N' THEN
699 IF l_debug_level > 0 THEN
700 oe_debug_pub.add('Inserting into OE_APPROVER_TRANSACTIONS ', 1) ;
701 END IF;
702 -- insert this next approver in the OE_APPROVER_TRANSACTIONS
703 INSERT INTO OE_APPROVER_TRANSACTIONS
704 (
705 TRANSACTION_ID
706 ,TRANSACTION_TYPE_ID --?? Do we need this. evalute
707 ,TRANSACTION_PHASE_CODE
708 ,ROLE
709 ,APPROVER_SEQUENCE
710 ,APPROVAL_STATUS
711 ,CREATION_DATE
712 ,CREATED_BY
713 ,LAST_UPDATE_DATE
714 ,LAST_UPDATED_BY
715 ,LAST_UPDATE_LOGIN
716
717 )
718 VALUES
719 (
720 p_transaction_id
721 ,l_transaction_type_id
722 ,l_transaction_phase_code
723 ,l_role
724 ,l_approver_sequence
725 ,NULL --APPROVAL_STATUS
726 ,SYSDATE
727 ,l_user_id
728 ,SYSDATE
729 ,l_user_id
730 ,l_user_id
731 );
732 END IF;
733
734 RETURN l_role;
735
736 END Get_Next_Approver_internal;
737
738 /**************************
739 * Approve_Approval *
740 **************************/
741 /*
742 This procedure will update the OM Approval transaction table
743 OE_APPROVER_TRANSACTIONS with proper results. Perform a status
744 update to INTERNAL_APPROVED.
745 */
746 Procedure Approve_Approval
747 (itemtype in varchar2,
748 itemkey in varchar2,
749 actid in number,
750 funcmode in varchar2,
751 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
752 IS
753
754 l_transaction_id NUMBER;
755 l_sales_document_type_code VARCHAR2(30);
756 l_return_status VARCHAR2(30);
757 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
758
759 BEGIN
760
761 --
762 -- RUN mode - normal process execution
763 --
764 if (funcmode = 'RUN') then
765
766 l_transaction_id := to_number(itemkey);
767
768 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
769 l_sales_document_type_code := wf_engine.GetItemAttrText(
770 OE_GLOBALS.G_WFI_NGO,
771 l_transaction_id,
772 'SALES_DOCUMENT_TYPE_CODE');
773 END IF;
774
775 OE_STANDARD_WF.Set_Msg_Context(actid);
776 OE_MSG_PUB.set_msg_context(
777 p_entity_code => 'HEADER'
778 ,p_entity_id => to_number(itemkey)
779 ,p_header_id => to_number(itemkey));
780
781 IF l_debug_level > 0 THEN
782 oe_debug_pub.add('Approve_Approval.. ', 1) ;
783 END IF;
784
785 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
786 (p_item_type => itemtype,
787 p_header_id => l_transaction_id,
788 p_flow_status_code => 'INTERNAL_APPROVED',
789 p_sales_document_type_code => l_sales_document_type_code,
790 x_return_status => l_return_status );
791
792 IF l_debug_level > 0 THEN
793 oe_debug_pub.add('Approve_Approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
794 END IF;
795
796 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
797 -- start data fix project
798 -- OE_STANDARD_WF.Save_Messages;
799 -- OE_STANDARD_WF.Clear_Msg_Context;
800 -- end data fix project
801 --app_exception.raise_exception;
802 --bug7386039
803
804 resultout := 'DEFERRED:'||to_char(sysdate+(TO_NUMBER(g_defer_min)/1440),wf_engine.date_format);
805 return;
806
807 END IF;
808
809 resultout := 'COMPLETE';
810 OE_STANDARD_WF.Save_Messages;
811 OE_STANDARD_WF.Clear_Msg_Context;
812 return;
813
814 end if;
815
816
817 --
818 -- CANCEL mode - activity 'compensation'
819 --
820 -- This is an event point is called with the effect of the activity must
821 -- be undone, for example when a process is reset to an earlier point
822 -- due to a loop back.
823 --
824 if (funcmode = 'CANCEL') then
825
826 -- your cancel code goes here
827 null;
828
829 -- no result needed
830 resultout := 'COMPLETE';
831 return;
832 end if;
833
834
835 --
836 -- Other execution modes may be created in the future. Your
837 -- activity will indicate that it does not implement a mode
838 -- by returning null
839 --
840 -- resultout := '';
841 -- return;
842
843 exception
844 when others then
845 -- The line below records this function call in the error system
849 END IF;
846 -- in the case of an exception.
847 IF l_debug_level > 0 THEN
848 oe_debug_pub.add('Exception in Reject_Approval.. ' ,1) ;
850 wf_core.context('OE_APPROVALS_WF', 'Approve_Approval',
851 itemtype, itemkey, to_char(actid), funcmode);
852 -- start data fix project
853 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
854 p_itemtype => itemtype,
855 p_itemkey => itemkey);
856 OE_STANDARD_WF.Save_Messages;
857 OE_STANDARD_WF.Clear_Msg_Context;
858 -- end data fix project
859 raise;
860
861
862 END Approve_Approval;
863
864
865 /*************************
866 * Approval_Timeout *
867 *************************/
868 /*
869 This procedure will determine if to Cotinue or Reject the transaction
870 in case the approver has timed out and not responded. It will read the
871 system parameter value and determne if to continue or Reject
872
873 */
874 Procedure Approval_Timeout
875 (itemtype in varchar2,
876 itemkey in varchar2,
877 actid in number,
878 funcmode in varchar2,
879 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
880 IS
881 l_next_role varchar2(320);
882 l_current_approver varchar2(320);
883 l_sales_document_type_code VARCHAR2(30);
884 l_transaction_id NUMBER;
885 l_return_status VARCHAR2(30);
886 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
887
888 BEGIN
889
890 --
891 -- RUN mode - normal process execution
892 --
893 if (funcmode = 'RUN') then
894
895 l_transaction_id := to_number(itemkey);
896
897 OE_STANDARD_WF.Set_Msg_Context(actid);
898 OE_MSG_PUB.set_msg_context(
899 p_entity_code => 'HEADER'
900 ,p_entity_id => to_number(itemkey)
901 ,p_header_id => to_number(itemkey));
902
903 IF l_debug_level > 0 THEN
904 oe_debug_pub.add('Approval_Timeout.. ', 1) ;
905 END IF;
906
907
908 IF nvl(OE_SYS_PARAMETERS.value('NO_RESPONSE_FROM_APPROVER'), 'CONTINUE') ='CONTINUE' THEN
909
910 -- If the current approver is the last in the list, then still reject it.
911 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
912 l_sales_document_type_code := wf_engine.GetItemAttrText(
913 OE_GLOBALS.G_WFI_NGO,
914 l_transaction_id,
915 'SALES_DOCUMENT_TYPE_CODE');
916 END IF;
917
918 -- check_if_last_approver(l_transaction_id,l_sales_document_type_code);
919 l_next_role := Get_Next_Approver_internal(
920 l_transaction_id,
921 itemtype,
922 l_sales_document_type_code,
923 'Y');
924
925 if l_next_role is NULL THEN
926 resultout := 'COMPLETE:REJECTED';
927 OE_STANDARD_WF.Clear_Msg_Context;
928 return;
929 else
930 resultout := 'COMPLETE:CONTINUE';
931 OE_STANDARD_WF.Clear_Msg_Context;
932 return;
933 end if;
934
935 ELSE
936 resultout := 'COMPLETE:REJECTED';
937 OE_STANDARD_WF.Clear_Msg_Context;
938 return;
939
940 END IF;
941
942
943 resultout := 'COMPLETE';
944 OE_STANDARD_WF.Clear_Msg_Context;
945 return;
946 end if; -- End for 'RUN' mode
947
948 --
949 -- CANCEL mode - activity 'compensation'
950 --
951 -- This is an event point is called with the effect of the activity must
952 -- be undone, for example when a process is reset to an earlier point
953 -- due to a loop back.
954 --
955 if (funcmode = 'CANCEL') then
956
957 -- your cancel code goes here
958 null;
959
960 -- no result needed
961 resultout := 'COMPLETE';
962 return;
963 end if;
964
965
966 --
967 -- Other execution modes may be created in the future. Your
968 -- activity will indicate that it does not implement a mode
969 -- by returning null
970 --
971 -- resultout := '';
972 -- return;
973
974
975 exception
976 when others then
977 -- The line below records this function call in the error system
978 -- in the case of an exception.
979 IF l_debug_level > 0 THEN
980 oe_debug_pub.add('Exception in Approval_Timeout.. ' ,1) ;
981 END IF;
982 wf_core.context('OE_APPROVALS_WF', 'Approval_Timeout',
983 itemtype, itemkey, to_char(actid), funcmode);
984 -- start data fix project
985 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
986 p_itemtype => itemtype,
987 p_itemkey => itemkey);
988 OE_STANDARD_WF.Save_Messages;
989 OE_STANDARD_WF.Clear_Msg_Context;
990 -- end data fix project
991 raise;
992
993
994 END Approval_Timeout;
995
996
997
998
999 /*************************
1000 * Reject_Approval *
1001 *************************/
1002 /*
1003 This procedure will update the OM Approval transaction table
1004 OE_APPROVER_TRANSACTIONS with proper results. Perform a status
1005 update to DRAFT_INTERNAL_REJECTED. And update the column
1009 (itemtype in varchar2,
1006 DRAFT_SUBMITTED_FLAG to 'N' to the base table.
1007 */
1008 Procedure Reject_Approval
1010 itemkey in varchar2,
1011 actid in number,
1012 funcmode in varchar2,
1013 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
1014 IS
1015
1016 l_transaction_id NUMBER;
1017 l_return_status VARCHAR2(30);
1018 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1019
1020 BEGIN
1021
1022 --
1023 -- RUN mode - normal process execution
1024 --
1025 if (funcmode = 'RUN') then
1026
1027 l_transaction_id := to_number(itemkey);
1028
1029 OE_STANDARD_WF.Set_Msg_Context(actid);
1030 OE_MSG_PUB.set_msg_context(
1031 p_entity_code => 'HEADER'
1032 ,p_entity_id => to_number(itemkey)
1033 ,p_header_id => to_number(itemkey));
1034
1035 IF l_debug_level > 0 THEN
1036 oe_debug_pub.add('Reject_Approval.. ', 1) ;
1037 END IF;
1038
1039 BEGIN
1040 UPDATE OE_APPROVER_TRANSACTIONS
1041 SET APPROVAL_STATUS = 'REJECTED'
1042 WHERE TRANSACTION_ID = to_number(itemkey)
1043 AND APPROVER_SEQUENCE = ( select max(APPROVER_SEQUENCE)
1044 from OE_APPROVER_TRANSACTIONS
1045 where TRANSACTION_ID = to_number(itemkey));
1046 EXCEPTION
1047 WHEN NO_DATA_FOUND THEN
1048 -- In case someone adds an additional approval notification after its approved
1049 -- and then that notifcations get rejected and transitions to Reject_apprroval
1050 null;
1051 END;
1052
1053 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
1054 OE_ORDER_WF_UTIL.Update_Quote_Blanket(
1055 p_item_type => OE_GLOBALS.G_WFI_NGO,
1056 p_item_key => to_number(itemkey),
1057 p_flow_status_code => 'DRAFT_INTERNAL_REJECTED',
1058 p_draft_submitted_flag => 'N',
1059 x_return_status => l_return_status);
1060 ELSE
1061 OE_ORDER_WF_UTIL.Update_flow_status_code(
1062 p_item_type => itemtype,
1063 p_header_id => to_number(itemkey),
1064 p_flow_status_code => 'INTERNAL_REJECTED',
1065 x_return_status => l_return_status);
1066 END IF;
1067
1068 IF l_debug_level > 0 THEN
1069 oe_debug_pub.add('Reject_Approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
1070 END IF;
1071
1072 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1073 -- start data fix project
1074 -- OE_STANDARD_WF.Save_Messages;
1075 -- OE_STANDARD_WF.Clear_Msg_Context;
1076 -- end data fix project
1077 --app_exception.raise_exception;
1078 --bug7386039
1079 resultout := 'DEFERRED:'||to_char(sysdate+(TO_NUMBER(g_defer_min)/1440),wf_engine.date_format);
1080 return;
1081
1082 END IF;
1083
1084
1085
1086 resultout := 'COMPLETE';
1087 OE_STANDARD_WF.Clear_Msg_Context;
1088 return;
1089 end if; -- End for 'RUN' mode
1090
1091 --
1092 -- CANCEL mode - activity 'compensation'
1093 --
1094 -- This is an event point is called with the effect of the activity must
1095 -- be undone, for example when a process is reset to an earlier point
1096 -- due to a loop back.
1097 --
1098 if (funcmode = 'CANCEL') then
1099
1100 -- your cancel code goes here
1101 null;
1102
1103 -- no result needed
1104 resultout := 'COMPLETE';
1105 return;
1106 end if;
1107
1108
1109 --
1110 -- Other execution modes may be created in the future. Your
1111 -- activity will indicate that it does not implement a mode
1112 -- by returning null
1113 --
1114 -- resultout := '';
1115 -- return;
1116
1117 exception
1118 when others then
1119 -- The line below records this function call in the error system
1120 -- in the case of an exception.
1121 IF l_debug_level > 0 THEN
1122 oe_debug_pub.add('Exception in Reject_Approval.. ' ,1) ;
1123 END IF;
1124 wf_core.context('OE_APPROVALS_WF', 'Reject_Approval',
1125 itemtype, itemkey, to_char(actid), funcmode);
1126 -- start data fix project
1127 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1128 p_itemtype => itemtype,
1129 p_itemkey => itemkey);
1130 OE_STANDARD_WF.Save_Messages;
1131 OE_STANDARD_WF.Clear_Msg_Context;
1132 -- end data fix project
1133 raise;
1134
1135
1136 END Reject_Approval;
1137
1138
1139
1140 /*************************
1141 * Get_Current_Approver *
1142 *************************/
1143 /*
1144 This API will hit the OE_APPROVER_TRANSACTIONS table to find the
1145 max(approver_sequence) approver for the given transaction_id and
1146 retrieve the proper name of the approver. NID will be passed in.
1147 */
1148 Procedure Get_Current_Approver
1149 (document_id in varchar2,
1150 display_type in varchar2,
1151 document in out NOCOPY /* file.sql.39 change */ varchar2,
1152 document_type in out NOCOPY /* file.sql.39 change */ varchar2)
1153 IS
1154
1155 l_role varchar2(320);
1156 l_approver_sequence NUMBER;
1157 l_transaction_id NUMBER;
1158
1159 l_list_id NUMBER;
1160 l_user_id NUMBER;
1161 l_transaction_type_id NUMBER;
1162 l_transaction_phase_code VARCHAR2(30);
1163
1164 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1165
1166 Begin
1167 IF l_debug_level > 0 THEN
1168 oe_debug_pub.add('Get_Current_Approver.. ' ,1) ;
1169 END IF;
1170
1171
1172 -- select to_number(ITEM_KEY)
1173 -- into l_transaction_id
1174 -- from wf_item_activity_statuses_v
1175 -- where NOTIFICATION_ID = to_number(document_id);
1176
1177 --Replaced with
1178 select to_number(ITEM_KEY)
1179 into l_transaction_id
1180 from WF_ITEM_ACTIVITY_STATUSES
1181 where NOTIFICATION_ID = to_number(document_id);
1182
1183 l_role := Get_Current_Approver_internal (l_transaction_id);
1184
1185 IF l_debug_level > 0 THEN
1186 oe_debug_pub.add('ItemKey/Current Role' || l_transaction_id || '/'
1187 || l_role,1);
1188 END IF;
1189
1190
1191 document := l_role;
1192
1193 end Get_Current_Approver;
1194
1195
1196 /**********************************
1197 * Get_Current_Approver_internal *
1198 **********************************/
1199 /*
1200 */
1201 function Get_Current_Approver_internal(p_transaction_id in NUMBER)
1202 return varchar2
1203 IS
1204 l_role varchar2(320);
1205
1206 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1207
1208 BEGIN
1209
1210 IF l_debug_level > 0 THEN
1211 oe_debug_pub.add('In function Get_Current_Approver_internal....', 1);
1212 END IF;
1213
1214 BEGIN
1215 select role
1216 into l_role
1217 from OE_APPROVER_TRANSACTIONS
1218 where TRANSACTION_ID = p_transaction_id
1219 and APPROVER_SEQUENCE = ( select max(APPROVER_SEQUENCE)
1220 from OE_APPROVER_TRANSACTIONS
1221 where TRANSACTION_ID = p_transaction_id);
1222
1223 EXCEPTION
1224 WHEN NO_DATA_FOUND THEN
1225 IF l_debug_level > 0 THEN
1226 oe_debug_pub.add('No OE_APPROVER_TRANSACTIONS for TransactionID:'
1227 || p_transaction_id ,1);
1228 l_role := null;-- 6615403
1229 END IF;
1230
1231
1232 END;
1233 IF l_debug_level > 0 THEN
1234 oe_debug_pub.add('ItemKey/Current Role' || p_transaction_id || '/'
1235 || l_role,1);
1236 END IF;
1237 RETURN l_role;-- 6615403
1238
1239 END Get_Current_Approver_internal;
1240
1241
1242
1243 /****************************
1244 * Get_Sales_Document_Type *
1245 ****************************/
1246 Procedure Get_Sales_Document_Type (document_id in varchar2,
1247 display_type in varchar2,
1248 document in out NOCOPY /* file.sql.39 change */ varchar2,
1249 document_type in out NOCOPY /* file.sql.39 change */ varchar2)
1250 IS
1251 l_sales_document_type_code VARCHAR2(30);
1252 l_sales_document_type_desc VARCHAR2(80);
1253 l_transaction_id NUMBER;
1254
1255 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1256
1257 BEGIN
1258 IF l_debug_level > 0 THEN
1259 oe_debug_pub.add('Get_Sales_Document_Type...',1);
1260 END IF;
1261
1262 -- select to_number(ITEM_KEY)
1263 -- into l_transaction_id
1264 -- from wf_item_activity_statuses_v
1265 -- where NOTIFICATION_ID = to_number(document_id);
1266
1267 --Replaced with
1268 select to_number(ITEM_KEY)
1269 into l_transaction_id
1270 from WF_ITEM_ACTIVITY_STATUSES
1271 where NOTIFICATION_ID = to_number(document_id);
1272
1273
1274 l_sales_document_type_code := wf_engine.GetItemAttrText(
1275 OE_GLOBALS.G_WFI_NGO,
1276 to_char(l_transaction_id),
1277 'SALES_DOCUMENT_TYPE_CODE');
1278
1279 select meaning
1280 into l_sales_document_type_desc
1281 from oe_lookups
1282 where LOOKUP_TYPE = 'SALES_DOCUMENT_TYPE'
1283 and LOOKUP_CODE = l_sales_document_type_code;
1284
1285 IF l_debug_level > 0 THEN
1286 oe_debug_pub.add('Sales_Document_Type_Code:' || l_sales_document_type_code,1);
1287 END IF;
1288
1289 document := l_sales_document_type_desc;
1290 EXCEPTION
1291 when no_data_found then
1292 raise; -- fill in the details
1293 when others then
1297 --Added for AME ER 16084377
1294 raise; -- fill in the details
1295 END Get_Sales_Document_Type;
1296
1298 /*This procedure is used to get the list of approvers and launch parallel approval workflows*/
1299 /* Formatted on 2013/01/11 10:11 (Formatter Plus v4.8.8) */
1300 PROCEDURE get_ame_approvers_launch (
1301 itemtype IN VARCHAR2,
1302 itemkey IN VARCHAR2,
1303 actid IN NUMBER,
1304 funcmode IN VARCHAR2,
1305 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1306 )
1307 IS
1308 l_transaction_id NUMBER;
1309 l_role VARCHAR2 (240);
1310 l_sales_document_type_code VARCHAR2 (30);
1311 l_attachment_location VARCHAR2 (240); --??
1312 l_msg_count NUMBER;
1313 l_msg_data VARCHAR2 (240);
1314 l_return_status VARCHAR2 (30);
1315 l_sold_to_org_id NUMBER;
1316 l_salesrep_id NUMBER;
1317 l_salesrep VARCHAR2 (240);
1318 l_sold_to VARCHAR2 (240);
1319 --l_customer_number NUMBER;
1320 l_customer_number VARCHAR2 (30); -- bug4575846
1321 l_expiration_date DATE;
1322 l_aname wf_engine.nametabtyp;
1323 l_avaluetext wf_engine.texttabtyp;
1324 ----++++++++++++++++++++++++++++++++++++++++++++
1325 l_admin_approver ame_util.approverrecord;
1326 l_ret_approver VARCHAR2 (50);
1327 l_name wf_users.NAME%TYPE; --bug 8620671
1328 l_display_name VARCHAR2 (150);
1329 l_debug_info VARCHAR2 (50);
1330 --l_role VARCHAR2(50);
1331 l_role_display VARCHAR2 (150);
1332 l_org_id NUMBER (15);
1333 l_error_message VARCHAR2 (2000);
1334 l_invoice_id NUMBER (15);
1335 l_iteration NUMBER (9);
1336 l_count NUMBER (9);
1337 l_hist_rec ap_inv_aprvl_hist%ROWTYPE;
1338 l_notf_iteration NUMBER;
1339 l_complete VARCHAR2 (1);
1340 l_next_approvers ame_util.approverstable2;
1341 l_next_approver ame_util.approverrecord2;
1342 l_index ame_util.idlist;
1343 l_ids ame_util.stringlist;
1344 l_class ame_util.stringlist;
1345 l_source ame_util.longstringlist;
1346 l_ampersand VARCHAR2 (1);
1347 l_next_approver_s ame_util.approverrecord;
1348 --itemkey VARCHAR2(100):='';
1349 l_application_id NUMBER := 660;
1350 --l_transaction_id VARCHAR2(100); --'232349';
1351 l_transaction_type_id VARCHAR2 (100); --'OENH';
1352 l_user_id VARCHAR2 (100);
1353 l_orig_system wf_roles.orig_system%TYPE;
1354 l_orig_system_id wf_roles.orig_system_id%TYPE;
1355 l_person_id NUMBER;
1356 l_item_key VARCHAR2 (100);
1357 -- used to get child item keys.
1358 l_approver_sequence NUMBER := 0;
1359 --for time being
1360 l_transaction_phase_code VARCHAR2 (10);
1361 l_parent_item_key VARCHAR2 (100);
1362 l_notif_from_role VARCHAR2 (100);
1363 l_transaction_number NUMBER;
1364 l_sales_document_type VARCHAR2 (100);
1365 l_nego_details VARCHAR2 (1000);
1366 l_nego_short_desc VARCHAR2 (1000);
1367 l_order_number NUMBER;
1368 l_rma_descriptor VARCHAR2 (1000);
1369 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1370 l_ame_transaction VARCHAR2 (250);
1371 l_item_type VARCHAR2 (10) := 'OEAME';
1372 l_nego_url VARCHAR2 (1000);
1373 l_order_url VARCHAR2 (1000);
1374 l_aname2 wf_engine.nametabtyp;
1375 l_avalue wf_engine.numtabtyp;
1376 i NUMBER := 0;
1377 -- for attribute text
1378 j NUMBER := 0;
1379 l_process_out VARCHAR2 (10);
1380 approverlist ame_util.approverstable2;
1381 -- for attribute number
1382 BEGIN
1383 SAVEPOINT get_ame_approvers_launch; --establishing save point here.
1384
1385 IF l_debug_level > 0
1386 THEN
1387 oe_debug_pub.ADD ('Inside get_ame_approvers_launch:funcmode '
1388 || funcmode
1389 );
1390 END IF;
1391
1392 --
1393 -- RUN mode - normal process execution
1394 --
1395 IF (funcmode = 'RUN')
1396 THEN
1397 IF l_debug_level > 0
1398 THEN
1399 oe_debug_pub.ADD ('funcmode is run');
1400 END IF;
1401
1402 l_transaction_type_id := itemtype; --'OENH';
1403 l_transaction_id := TO_NUMBER (itemkey);
1404
1405 SELECT parent_item_key
1406 INTO l_parent_item_key
1407 FROM wf_items wi
1408 WHERE wi.item_type = itemtype AND wi.item_key = itemkey;
1409
1410 oe_standard_wf.set_msg_context (actid);
1411 oe_msg_pub.set_msg_context (p_entity_code => 'HEADER',
1412 p_entity_id => TO_NUMBER (itemkey),
1413 p_header_id => TO_NUMBER (itemkey)
1414 );
1415
1416 IF l_debug_level > 0
1417 THEN
1418 oe_debug_pub.ADD ('Initiate_Approval');
1419 END IF;
1420
1421 -- Delete any previous approval transaction data.
1422 DELETE FROM oe_approver_transactions
1423 WHERE transaction_id = l_transaction_id;
1424
1425 IF itemtype = oe_globals.g_wfi_ngo
1426 THEN
1427 l_sales_document_type_code :=
1428 wf_engine.getitemattrtext (oe_globals.g_wfi_ngo,
1429 l_transaction_id,
1430 'SALES_DOCUMENT_TYPE_CODE'
1431 );
1432
1433 IF l_debug_level > 0
1434 THEN
1435 oe_debug_pub.ADD ( 'l_sales_document_type_code= '
1436 || l_sales_document_type_code
1437 );
1438 END IF;
1439
1440 IF l_sales_document_type_code = 'O' THEN
1441 SELECT quote_number
1442 INTO l_transaction_number
1443 FROM oe_order_headers_all -- 16084377 AME BSA
1444 WHERE header_id = TO_NUMBER (itemkey);
1445 ELSE
1446 SELECT ORDER_number -- it is order number. Not quote number.
1447 INTO l_transaction_number
1448 FROM oe_blanket_headers_all
1449 WHERE header_id = TO_NUMBER (itemkey);
1450 END IF ;
1451
1452 END IF;
1453
1454 ------------
1455 --Plug in New API Code Here
1456 ------------
1457 IF l_debug_level > 0
1458 THEN
1459 oe_debug_pub.ADD
1460 ('Getting the AME transaction type associated with this header');
1461 END IF;
1462
1463 IF itemtype = oe_globals.g_wfi_ngo
1464 THEN
1465 IF l_debug_level > 0
1466 THEN
1467 oe_debug_pub.ADD ('item type is negotiation so AME TTY is OENEG');
1468 END IF;
1469
1470 IF l_sales_document_type_code = 'O' THEN
1471
1472 l_ame_transaction := 'OENEG';
1473
1474 ELSE -- 16084377 AME BSA
1475 l_ame_transaction := 'OEBSA';
1476 END IF ;
1477 ELSIF itemtype = oe_globals.g_wfi_hdr
1478 THEN
1479 IF l_debug_level > 0
1480 THEN
1481 oe_debug_pub.ADD ('item type is header so AME TTY is OERMA');
1482 END IF;
1483
1484 l_ame_transaction := 'OERMA';
1485 END IF;
1486
1487 IF l_debug_level > 0
1488 THEN
1489 oe_debug_pub.ADD
1490 ( 'AME transaction type associated with this header is:'
1491 || l_ame_transaction
1492 );
1493 oe_debug_pub.ADD ('Before calling AME API :' || TO_NUMBER (itemkey));
1497
1494 END IF;
1495
1496 -- 16084377 unnecessary notification bug
1498 BEGIN
1499 ame_api2.getallapprovers7
1500 (applicationidin => 660,
1501 transactionidin => TO_NUMBER(itemkey),
1502 transactiontypein => l_ame_transaction,
1503 approvalprocesscompleteynout => l_process_out,
1504 approversout => approverlist
1505 );
1506
1507 IF approverlist.COUNT = 0 THEN
1508 oe_debug_pub.ADD ('no approvers.May be quote/bsa/rma does not qualify AME rules');
1509 resultout := 'COMPLETE:NOT_APPLICABLE';
1510 RETURN;
1511 END IF ;
1512 EXCEPTION
1513 WHEN OTHERS
1514 THEN
1515 IF NVL (oe_sys_parameters.VALUE ('TREAT_AME_EXCEPTION'),
1516 'REJECTION'
1517 ) = 'APPROVAL'
1518 THEN
1519 resultout := 'COMPLETE:NO_NEXT_APPROVER';
1520 RETURN;
1521 ELSIF NVL (oe_sys_parameters.VALUE ('TREAT_AME_EXCEPTION'),
1522 'REJECTION'
1523 ) = 'REJECTION'
1524 THEN
1525 resultout := 'COMPLETE:INVALID_APPROVER';
1526 RETURN;
1527 END IF;
1528 END;
1529
1530 BEGIN
1531 ame_api2.getnextapprovers1
1532 (applicationidin => '660',
1533 transactiontypein => l_ame_transaction,
1534 --itemtype,
1535 transactionidin => TO_NUMBER
1536 (itemkey),
1537 flagapproversasnotifiedin => ame_util.booleantrue,
1538 --ame_util.booleanFalse --16084377 change
1539 approvalprocesscompleteynout => l_complete,
1540 nextapproversout => l_next_approvers,
1541 itemindexesout => l_index,
1542 itemidsout => l_ids,
1543 itemclassesout => l_class,
1544 itemsourcesout => l_source
1545 );
1546 EXCEPTION
1547 WHEN OTHERS
1548 THEN
1549 IF NVL (oe_sys_parameters.VALUE ('TREAT_AME_EXCEPTION'),
1550 'REJECTION'
1551 ) = 'APPROVAL'
1552 THEN
1553 resultout := 'COMPLETE:NO_NEXT_APPROVER';
1554 RETURN;
1555 ELSIF NVL (oe_sys_parameters.VALUE ('TREAT_AME_EXCEPTION'),
1556 'REJECTION'
1557 ) = 'REJECTION'
1558 THEN
1559 resultout := 'COMPLETE:INVALID_APPROVER';
1560 RETURN;
1561 END IF;
1562 END;
1563
1564 IF l_next_approvers.COUNT > 0
1565 THEN
1566 IF l_debug_level > 0
1567 THEN
1568 oe_debug_pub.ADD ( 'AFTER calling AME API :'
1569 || l_next_approvers.COUNT
1570 );
1571 oe_debug_pub.ADD ( 'nextApproversOut = '
1572 || l_next_approvers (1).NAME
1573 || '----'
1574 || l_complete
1575 );
1576 END IF;
1577 end if;
1578
1579 -----------
1580 IF l_complete = ame_util2.completefullyapproved
1581 THEN
1582 IF l_debug_level > 0
1583 THEN
1584 oe_debug_pub.ADD ('Fully Approved');
1585 END IF;
1586 ELSIF l_complete = ame_util2.completefullyrejected
1587 THEN
1588 IF l_debug_level > 0
1589 THEN
1590 oe_debug_pub.ADD ('Fully Rejected');
1591 END IF;
1592 ELSIF l_complete = ame_util2.completepartiallyapproved
1593 THEN
1594 IF l_debug_level > 0
1595 THEN
1596 oe_debug_pub.ADD ('Partially Approved');
1597 END IF;
1598 ELSIF l_complete = ame_util2.completenoapprovers
1599 THEN
1600 IF l_debug_level > 0
1601 THEN
1602 oe_debug_pub.ADD ('No Approvers');
1603 END IF;
1604 ELSIF l_complete = ame_util2.notcompleted
1605 THEN
1606 IF l_debug_level > 0
1607 THEN
1608 oe_debug_pub.ADD ('Not Completed');
1609 END IF;
1610 END IF;
1611
1612 -- Loop through approvers' table returned by AME
1613 IF l_complete = ame_util.booleanfalse
1614 THEN
1615 IF l_debug_level > 0
1616 THEN
1617 oe_debug_pub.ADD ( 'l_next_approvers.count= '
1618 || l_next_approvers.COUNT
1619 || '---'
1620 || l_index.COUNT
1621 || '---'
1622 || l_source.COUNT
1623 );
1624 END IF;
1625
1626 FOR l_table IN 1 .. l_next_approvers.COUNT
1627 LOOP
1628 l_next_approver := l_next_approvers (l_table);
1629
1633 ( '***nextApproversOut = '
1630 IF l_debug_level > 0
1631 THEN
1632 oe_debug_pub.ADD
1634 || l_next_approvers (l_table).NAME
1635 || '-'
1636 || l_next_approvers (l_table).group_or_chain_order_number
1637 || '-'
1638 || l_next_approvers (l_table).member_order_number
1639 );
1640 oe_debug_pub.ADD
1641 ( '***nextApproversOut = '
1642 || l_next_approvers (l_table).action_type_id
1643 || '-'
1644 || l_next_approvers (l_table).occurrence
1645 || '-'
1646 || l_next_approvers (l_table).SOURCE
1647 );
1648 oe_debug_pub.ADD
1649 ( '***nextApproversOut = '
1650 || l_next_approvers (l_table).item_class
1651 || '-'
1652 || l_next_approvers (l_table).item_id
1653 || '-'
1654 || l_next_approvers (l_table).item_order_number
1655 );
1656 oe_debug_pub.ADD
1657 ( '***nextApproversOut = '
1658 || l_next_approvers (l_table).action_type_order_number
1659 || '-'
1660 || l_next_approvers (l_table).approver_order_number
1661 || '-'
1662 || l_next_approvers (l_table).approval_status
1663 );
1664 oe_debug_pub.ADD ( 'Inside Approvals LOOP'
1665 || '-'
1666 || l_next_approvers (l_table).NAME
1667 || '-'
1668 || l_next_approvers (l_table).item_class
1669 || '-'
1670 || l_next_approvers (l_table).item_id
1671 || '-'
1672 || l_table
1673 );
1674 END IF;
1675
1676 l_role := l_next_approvers (l_table).NAME;
1677 l_orig_system := l_next_approvers (l_table).orig_system;
1678 l_orig_system_id := l_next_approvers (l_table).orig_system_id;
1679
1680 IF l_debug_level > 0
1681 THEN
1682 oe_debug_pub.ADD ('Role->' || l_role, 1);
1683 END IF;
1684
1685 IF l_role IS NULL
1686 THEN
1687 IF l_debug_level > 0
1688 THEN
1689 oe_debug_pub.ADD
1690 ('Role is null. Set transaction to Not Eligible',
1691 1
1692 );
1693 END IF;
1694
1695 resultout := 'COMPLETE:INVALID_APPROVER';
1696 --oe_standard_wf.save_messages;
1697 oe_standard_wf.clear_msg_context;
1698 RETURN;
1699 ELSE
1700 IF l_debug_level > 0
1701 THEN
1702 oe_debug_pub.ADD ('role is not null.Launch workflows');
1703 END IF;
1704
1705 SELECT TO_CHAR (oe_ame_parallel_s.NEXTVAL)
1706 INTO l_item_key
1707 FROM SYS.DUAL;
1708
1709 IF l_debug_level > 0
1710 THEN
1711 oe_debug_pub.ADD ('item key derived is:' || l_item_key);
1712 END IF;
1713
1714 -- Create a child process for the retrieved approver.
1715 IF itemtype = 'OENH'
1716 THEN
1717 wf_engine.createprocess
1718 (itemtype => l_item_type,
1719 itemkey => l_item_key,
1720 process => 'AME_PARALLEL_APPROVAL'
1721 );
1722 ELSIF itemtype = 'OEOH'
1723 THEN
1724 wf_engine.createprocess
1725 (itemtype => l_item_type,
1726 itemkey => l_item_key,
1727 process => 'AME_PARALLEL_APPR_RMA'
1728 );
1729 END IF;
1730
1731 /* Need to set the parent child relationship between processes */
1732 wf_engine.setitemparent (itemtype => l_item_type,
1733 itemkey => l_item_key,
1734 parent_itemtype => itemtype,
1735 parent_itemkey => itemkey,
1736 parent_context => NULL
1737 );
1738 IF itemtype = 'OENH' THEN
1739 wf_engine.setitemattrnumber (itemtype,
1740 itemkey,
1741 'HEADER_ID',
1742 TO_NUMBER (itemkey)
1743 );
1747 wf_engine.getitemattrtext (itemtype,
1744
1745 END IF ;
1746 l_notif_from_role :=
1748 itemkey,
1749 'NOTIFICATION_FROM_ROLE'
1750 );
1751 i := i + 1;
1752 l_aname (i) := 'NOTIFICATION_APPROVER';
1753 l_avaluetext (i) := l_role;
1754 i := i + 1;
1755 l_aname (i) := 'NOTIFICATION_FROM_ROLE';
1756 l_avaluetext (i) := l_notif_from_role;
1757 i := i + 1;
1758 l_aname (i) := 'ORIG_SYSTEM';
1759 l_avaluetext (i) := l_orig_system;
1760 j := j + 1;
1761 l_aname2 (j) := 'ORIG_SYSTEM_ID';
1762 l_avalue (j) := l_orig_system_id;
1763 j := j + 1;
1764 l_aname2 (j) := 'HEADER_ID';
1765 l_avalue (j) := TO_NUMBER (itemkey);
1766
1767 IF itemtype = oe_globals.g_wfi_ngo
1768 THEN
1769 i := i + 1;
1770 l_aname (i) := 'SALES_DOCUMENT_TYPE_CODE';
1771 l_avaluetext (i) := l_sales_document_type_code;
1772 l_sales_document_type :=
1773 wf_engine.getitemattrtext (itemtype,
1774 itemkey,
1775 'SALES_DOCUMENT_TYPE'
1776 );
1777 i := i + 1;
1778 l_aname (i) := 'SALES_DOCUMENT_TYPE';
1779 l_avaluetext (i) := l_sales_document_type;
1780 l_nego_details :=
1781 wf_engine.getitemattrtext (itemtype,
1782 itemkey,
1783 'NEGOTIATION_DETAILS'
1784 );
1785 i := i + 1;
1786 l_aname (i) := 'NEGOTIATION_DETAILS';
1787 l_avaluetext (i) := l_nego_details;
1788 l_nego_url :=
1789 wf_engine.getitemattrtext (itemtype,
1790 itemkey,
1791 'NEGOTIATION_DETAILS_URL'
1792 );
1793 i := i + 1;
1794 l_aname (i) := 'NEGOTIATION_DETAILS_URL';
1795 l_avaluetext (i) := l_nego_url;
1796 l_nego_short_desc :=
1797 wf_engine.getitemattrtext
1798 (itemtype,
1799 itemkey,
1800 'NEGOTIATION_SHORT_DESCRIPTOR'
1801 );
1802 i := i + 1;
1803 l_aname (i) := 'NEGOTIATION_SHORT_DESCRIPTOR';
1804 l_avaluetext (i) := l_nego_short_desc;
1805 j := j + 1;
1806 l_aname2 (j) := 'TRANSACTION_NUMBER';
1807 l_avalue (j) := l_transaction_number;
1808 wf_engine.setitemattrnumber (itemtype,
1809 itemkey,
1810 'TRANSACTION_NUMBER',
1811 l_transaction_number
1812 );
1813
1814 IF l_debug_level > 0
1815 THEN
1816 oe_debug_pub.ADD
1817 ('Before calling OE_CONTRACTS_UTIL.attachment_location.. ',
1818 1
1819 );
1820 END IF;
1821
1822 oe_contracts_util.attachment_location
1823 (p_api_version => 1.0,
1824 p_doc_type => l_sales_document_type_code,
1825 p_doc_id => l_transaction_id,
1826 x_workflow_string => l_attachment_location,
1827 x_return_status => l_return_status,
1828 x_msg_count => l_msg_count,
1829 x_msg_data => l_msg_data
1830 );
1831
1832 IF l_debug_level > 0
1833 THEN
1834 oe_debug_pub.ADD ( 'l_attachment_location->'
1835 || l_attachment_location,
1836 1
1837 );
1838 oe_debug_pub.ADD ( 'l_return_status->'
1839 || l_return_status,
1840 1
1841 );
1842 oe_debug_pub.ADD ('l_msg_data->' || l_msg_data, 1);
1843 END IF;
1844
1845 IF l_return_status = fnd_api.g_ret_sts_error
1846 THEN
1847 app_exception.raise_exception;
1848 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
1849 THEN
1850 app_exception.raise_exception;
1851 END IF;
1852
1853 IF l_attachment_location IS NOT NULL
1854 THEN
1855
1856 wf_engine.setitemattrtext (itemtype,
1860 );
1857 itemkey,
1858 'CONTRACT_ATTACHMENT',
1859 l_attachment_location
1861 i := i + 1;
1862 l_aname (i) := 'CONTRACT_ATTACHMENT';
1863 l_avaluetext (i) := l_attachment_location;
1864 END IF;
1865 ELSE -- type is rma and not quote.
1866 l_order_number :=
1867 wf_engine.getitemattrnumber (itemtype,
1868 itemkey,
1869 'ORDER_NUMBER'
1870 );
1871 j := j + 1;
1872 l_aname2 (j) := 'ORDER_NUMBER';
1873 l_avalue (j) := l_order_number;
1874 l_order_url :=
1875 wf_engine.getitemattrtext (itemtype,
1876 itemkey,
1877 'ORDER_DETAILS_URL'
1878 );
1879 i := i + 1;
1880 l_aname (i) := 'ORDER_DETAILS_URL';
1881 l_avaluetext (i) := l_order_url;
1882 l_rma_descriptor :=
1883 wf_engine.getitemattrtext (itemtype,
1884 itemkey,
1885 'HDR_SHORT_DESCRIPTOR'
1886 );
1887 i := i + 1;
1888 l_aname (i) := 'HDR_SHORT_DESCRIPTOR';
1889 l_avaluetext (i) := l_rma_descriptor;
1890 END IF;
1891
1892 --------------------------------------------------
1893 -- Set Header Attributes Values for Negotiation --
1894 IF itemtype = oe_globals.g_wfi_ngo
1895 THEN
1896 IF l_sales_document_type_code = 'O'
1897 THEN
1898 SELECT sold_to_org_id, expiration_date,
1899 salesrep_id, transaction_phase_code,
1900 order_type_id
1901 INTO l_sold_to_org_id, l_expiration_date,
1902 l_salesrep_id, l_transaction_phase_code,
1903 l_transaction_type_id
1904 FROM oe_order_headers_all
1905 WHERE header_id = TO_NUMBER (itemkey);
1906 ELSE
1907 SELECT obha.sold_to_org_id, obhe.end_date_active,
1908 obha.salesrep_id,
1909 obha.transaction_phase_code,
1910 obha.order_type_id
1911 INTO l_sold_to_org_id, l_expiration_date,
1912 l_salesrep_id,
1913 l_transaction_phase_code,
1914 l_transaction_type_id
1915 FROM oe_blanket_headers_all obha,
1916 oe_blanket_headers_ext obhe
1917 WHERE obha.header_id = TO_NUMBER (itemkey)
1918 AND obha.order_number = obhe.order_number;
1919 END IF;
1920
1921 l_salesrep :=
1922 oe_id_to_value.salesrep
1923 (p_salesrep_id => l_salesrep_id);
1924 oe_id_to_value.sold_to_org
1925 (p_sold_to_org_id => l_sold_to_org_id,
1926 x_org => l_sold_to,
1927 x_customer_number => l_customer_number
1928 );
1929 i := i + 1;
1930 l_aname (i) := 'SALESPERSON';
1931 l_avaluetext (i) := l_salesrep;
1932 wf_engine.setitemattrtext (itemtype,
1933 itemkey,
1934 'SALESPERSON',
1935 l_salesrep
1936 );
1937 i := i + 1;
1938 l_aname (i) := 'SOLD_TO';
1939 l_avaluetext (i) := l_sold_to;
1940 wf_engine.setitemattrtext (itemtype,
1941 itemkey,
1942 'SOLD_TO',
1943 l_sold_to
1944 );
1945 i := i + 1;
1946 l_aname (i) := 'EXPIRATION_DATE';
1947 l_avaluetext (i) := l_expiration_date;
1948
1949 -- End setting Header Attributes
1950 IF l_debug_level > 0
1951 THEN
1952 oe_debug_pub.ADD ('before calling start process');
1953 END IF;
1954 END IF;
1955
1956 wf_engine.setitemattrtextarray (l_item_type,
1957 l_item_key,
1958 l_aname,
1959 l_avaluetext
1960 );
1961 wf_engine.setitemattrnumberarray (l_item_type,
1962 l_item_key,
1963 l_aname2,
1964 l_avalue
1968 itemkey => l_item_key
1965 );
1966 -------itemtype = OE_GLOBALS.G_WFI_NGO-------
1967 wf_engine.startprocess (itemtype => l_item_type,
1969 );
1970 END IF;
1971 END LOOP;
1972
1973 IF l_next_approvers.COUNT > 0
1974 THEN
1975 IF itemtype = 'OENH'
1976 THEN
1977 oe_order_wf_util.update_flow_status_code
1978 (p_item_type => itemtype,
1979 p_header_id => l_transaction_id,
1980 p_flow_status_code => 'PENDING_INTERNAL_APPROVAL',
1981 p_sales_document_type_code => l_sales_document_type_code,
1982 x_return_status => l_return_status
1983 );
1984
1985 IF l_debug_level > 0
1986 THEN
1987 oe_debug_pub.ADD
1988 ( 'Initiate_Approval STATUS FROM Update_Flow_Status_Code: '
1989 || l_return_status
1990 );
1991 END IF;
1992
1993 IF l_return_status <> fnd_api.g_ret_sts_success
1994 THEN
1995 app_exception.raise_exception;
1996 END IF;
1997 END IF;
1998
1999 resultout := 'COMPLETE:VALID_APPROVER';
2000 oe_standard_wf.clear_msg_context;
2001 RETURN;
2002 END IF; --approvers count
2003 ELSIF l_complete = ame_util.booleantrue
2004 THEN
2005 resultout := 'COMPLETE:NO_NEXT_APPROVER';
2006 END IF;
2007 END IF; -- End for 'RUN' mode
2008
2009 --
2010 -- CANCEL mode - activity 'compensation'
2011 --
2012 -- This is an event point is called with the effect of the activity must
2013 -- be undone, for example when a process is reset to an earlier point
2014 -- due to a loop back.
2015 --
2016 IF (funcmode = 'CANCEL')
2017 THEN
2018 -- your cancel code goes here
2019 NULL;
2020 -- no result needed
2021 resultout := 'COMPLETE';
2022 RETURN;
2023 END IF;
2024
2025 --
2026 -- Other execution modes may be created in the future. Your
2027 -- activity will indicate that it does not implement a mode
2028 -- by returning null
2029 --
2030 -- resultout := '';
2031 -- return;
2032 EXCEPTION
2033 WHEN OTHERS
2034 THEN
2035 -- The line below records this function call in the error system
2036 -- in the case of an exception.
2037 wf_core.CONTEXT ('OE_APPROVALS_WF',
2038 'get_ame_approvers_launch',
2039 itemtype,
2040 itemkey,
2041 TO_CHAR (actid),
2042 funcmode
2043 );
2044 -- start data fix project
2045 oe_standard_wf.add_error_activity_msg (p_actid => actid,
2046 p_itemtype => itemtype,
2047 p_itemkey => itemkey
2048 );
2049 oe_standard_wf.save_messages;
2050 oe_standard_wf.clear_msg_context;
2051 -- end data fix project
2052 --resultout := 'COMPLETE:INVALID_APPROVER';
2053 --wf_engine.setitemattrtext (itemtype, itemkey, 'AME_EXCEPTION', 'Y');
2054 ROLLBACK TO get_ame_approvers_launch;
2055 RAISE;
2056 END get_ame_approvers_launch;
2057
2058 PROCEDURE is_ame_exception (
2059 itemtype IN VARCHAR2,
2060 itemkey IN VARCHAR2,
2061 actid IN NUMBER,
2062 funcmode IN VARCHAR2,
2063 resultout OUT NOCOPY VARCHAR2
2064 )
2065 IS
2066 l_exception VARCHAR2 (100);
2067 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2068 BEGIN
2069 IF l_debug_level >0 THEN
2070 oe_debug_pub.ADD ('Checking if there is AME exception');
2071 END IF ;
2072 l_exception :=
2073 wf_engine.getitemattrtext (itemtype => itemtype,
2074 itemkey => itemkey,
2075 aname => 'AME_EXCEPTION'
2076 );
2077 IF l_debug_level >0 THEN
2078 oe_debug_pub.ADD ('ame exception is:' || l_exception);
2079 END IF ;
2080
2081 IF l_exception IS NULL
2082 THEN
2083 resultout := 'COMPLETE:N';
2084 IF l_debug_level >0 THEN
2085 oe_debug_pub.ADD ('ame exception is:' || l_exception);
2086 END IF ;
2087 ELSE
2088 resultout := 'COMPLETE:Y';
2089 END IF;
2090 IF l_debug_level >0 THEN
2091 oe_debug_pub.ADD ('Exiting is AME exception');
2092 END IF ;
2093 EXCEPTION
2094 WHEN OTHERS
2095 THEN
2096 wf_core.CONTEXT ('OE_Approvals_WF',
2097 'is_ame_exception',
2098 itemtype,
2099 itemkey,
2100 TO_CHAR (actid),
2101 funcmode,
2102 SQLERRM
2103 );
2104 oe_standard_wf.add_error_activity_msg (p_actid => actid,
2108 oe_standard_wf.save_messages;
2105 p_itemtype => itemtype,
2106 p_itemkey => itemkey
2107 );
2109 oe_standard_wf.clear_msg_context;
2110 END is_ame_exception;
2111
2112 PROCEDURE check_parallel_wf_status (
2113 itemtype IN VARCHAR2,
2114 itemkey IN VARCHAR2,
2115 actid IN NUMBER,
2116 funcmode IN VARCHAR2,
2117 resultout OUT NOCOPY VARCHAR2
2118 )
2119 IS
2120 l_open_children NUMBER := 0;
2121 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2122 BEGIN
2123 IF l_debug_level > 0 THEN
2124 oe_debug_pub.ADD ('entering check_parallel_wf_status');
2125 END IF ;
2126
2127 SELECT COUNT (1)
2128 INTO l_open_children
2129 FROM wf_items wi
2130 WHERE wi.item_type = 'OEAME'
2131 AND wi.parent_item_type = itemtype
2132 AND wi.parent_item_key = itemkey
2133 AND wi.item_key <> NVL (g_current_item_key, 'XXXX')
2134 AND wi.end_date IS NULL;
2135
2136 IF l_open_children > 0
2137 THEN
2138 resultout := 'COMPLETE:EXISTS';
2139 ELSE
2140 resultout := 'COMPLETE:NOT_EXISTS';
2141 END IF;
2142 IF l_debug_level > 0 THEN
2143 oe_debug_pub.ADD ('exiting check_parallel_wf_status');
2144 END IF ;
2145 EXCEPTION
2146 WHEN OTHERS
2147 THEN
2148 IF l_debug_level > 0 THEN
2149 oe_debug_pub.ADD ('error in check parallel wf status:' || SQLERRM);
2150 END IF ;
2151 wf_core.CONTEXT ('OE_Approvals_WF',
2152 'check_parallel_wf_status',
2153 itemtype,
2154 itemkey,
2155 TO_CHAR (actid),
2156 funcmode,
2157 SQLERRM
2158 );
2159 oe_standard_wf.add_error_activity_msg (p_actid => actid,
2160 p_itemtype => itemtype,
2161 p_itemkey => itemkey
2162 );
2163 oe_standard_wf.save_messages;
2164 oe_standard_wf.clear_msg_context;
2165 END check_parallel_wf_status;
2166
2167 PROCEDURE treat_ame_exception_as (
2168 itemtype IN VARCHAR2,
2169 itemkey IN VARCHAR2,
2170 actid IN NUMBER,
2171 funcmode IN VARCHAR2,
2172 resultout OUT NOCOPY VARCHAR2
2173 )
2174 IS
2175 CURSOR get_all_children
2176 IS
2177 SELECT item_type, item_key
2178 FROM wf_items
2179 WHERE parent_item_type = itemtype AND parent_item_key = itemkey;
2180 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2181 BEGIN
2182 IF l_debug_level >0 THEN
2183 oe_debug_pub.ADD ('Inside treat_ame_exception_as');
2184 END IF ;
2185
2186 IF NVL (oe_sys_parameters.VALUE ('TREAT_AME_EXCEPTION'), 'REJECTION') =
2187 'APPROVAL'
2188 THEN
2189 resultout := 'COMPLETE:APPROVAL';
2190 IF l_debug_level > 0 THEN
2191 oe_debug_pub.ADD ('Treating as approval');
2192 END IF ;
2193 oe_standard_wf.clear_msg_context;
2194 RETURN;
2195 ELSIF NVL (oe_sys_parameters.VALUE ('TREAT_AME_EXCEPTION'), 'REJECTION') =
2196 'REJECTION'
2197 THEN
2198 FOR i IN get_all_children
2199 LOOP
2200 -- if exception is encounted before children are aborted, they would be left orphans in case of rejection.
2201 wf_engine.abortprocess (i.item_type, i.item_key);
2202 END LOOP;
2203
2204 resultout := 'COMPLETE:REJECTION';
2205 IF l_debug_level > 0 THEN
2206 oe_debug_pub.ADD ('Treating as rejection');
2207 END IF ;
2208 oe_standard_wf.clear_msg_context;
2209 RETURN;
2210 END IF;
2211 EXCEPTION
2212 WHEN OTHERS
2213 THEN
2214 IF l_debug_level > 0 THEN
2215 oe_debug_pub.ADD ('error in treat ame exceptions as:' || SQLERRM);
2216 END IF ;
2217 wf_core.CONTEXT ('OE_Approvals_WF',
2218 'treat_ame_exception_as',
2219 itemtype,
2220 itemkey,
2221 TO_CHAR (actid),
2222 funcmode,
2223 SQLERRM
2224 );
2225 oe_standard_wf.add_error_activity_msg (p_actid => actid,
2226 p_itemtype => itemtype,
2227 p_itemkey => itemkey
2228 );
2229 oe_standard_wf.save_messages;
2230 oe_standard_wf.clear_msg_context;
2231 END treat_ame_exception_as;
2232
2233 PROCEDURE get_notification_attributes (
2234 itemtype IN VARCHAR2,
2235 itemkey IN VARCHAR2,
2236 actid IN NUMBER,
2237 funcmode IN VARCHAR2,
2238 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2239 )
2240 IS
2241 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2245 oe_debug_pub.ADD ('Entering get notification attributes');
2242 BEGIN
2243 IF l_debug_level > 0
2244 THEN
2246 END IF;
2247
2248 oe_approvals_wf.g_ame_used := 'Y';
2249
2250 -- can add code in future if required. Leaving blank for now as we are already setting the attributes
2251 --from parent itself
2252 IF l_debug_level > 0
2253 THEN
2254 oe_debug_pub.ADD ('Exiting get notification attributes');
2255 END IF;
2256
2257 resultout := 'COMPLETE:NULL';
2258 EXCEPTION
2259 WHEN OTHERS
2260 THEN
2261 wf_core.CONTEXT ('OE_Approvals_WF',
2262 'get_notification_attributes',
2263 itemtype,
2264 itemkey,
2265 TO_CHAR (actid),
2266 funcmode,
2267 SQLERRM
2268 );
2269 oe_standard_wf.add_error_activity_msg (p_actid => actid,
2270 p_itemtype => itemtype,
2271 p_itemkey => itemkey
2272 );
2273 oe_standard_wf.save_messages;
2274 oe_standard_wf.clear_msg_context;
2275 END get_notification_attributes;
2276
2277 PROCEDURE process_response_approve (
2278 itemtype IN VARCHAR2,
2279 itemkey IN VARCHAR2,
2280 actid IN NUMBER,
2281 funcmode IN VARCHAR2,
2282 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2283 )
2284 IS
2285 l_transaction_id NUMBER;
2286 l_role VARCHAR2 (240);
2287 l_sales_document_type_code VARCHAR2 (30);
2288 l_return_status VARCHAR2 (30);
2289 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2290 l_sold_to_org_id NUMBER;
2291 l_salesrep_id NUMBER;
2292 l_salesrep VARCHAR2 (240);
2293 l_sold_to VARCHAR2 (240);
2294 --l_customer_number NUMBER;
2295 l_customer_number VARCHAR2 (30); --bug4575846
2296 l_expiration_date DATE;
2297 l_aname wf_engine.nametabtyp;
2298 l_avaluetext wf_engine.texttabtyp;
2299 ----++++++++++++++++++++++++++++++++++++++++++++
2300 l_admin_approver ame_util.approverrecord;
2301 l_ret_approver VARCHAR2 (50);
2302 l_name wf_users.NAME%TYPE; --bug 8620671
2303 l_display_name VARCHAR2 (150);
2304 l_debug_info VARCHAR2 (50);
2305 --l_role VARCHAR2(50);
2306 l_role_display VARCHAR2 (150);
2307 l_org_id NUMBER (15);
2308 l_error_message VARCHAR2 (2000);
2309 l_invoice_id NUMBER (15);
2310 l_iteration NUMBER (9);
2311 l_count NUMBER (9);
2312 l_hist_rec ap_inv_aprvl_hist%ROWTYPE;
2313 l_notf_iteration NUMBER;
2314 l_complete VARCHAR2 (1);
2315 l_next_approvers ame_util.approverstable2;
2316 l_next_approver ame_util.approverrecord2;
2317 l_index ame_util.idlist;
2318 l_ids ame_util.stringlist;
2319 l_class ame_util.stringlist;
2320 l_source ame_util.longstringlist;
2321 l_ampersand VARCHAR2 (1);
2322 l_next_approver_s ame_util.approverrecord;
2323 --itemkey VARCHAR2(100):='';
2324 l_application_id NUMBER := 660;
2325 --l_transaction_id VARCHAR2(100); --'232349';
2326 l_transaction_type_id VARCHAR2 (100); --'OENH';
2327 l_user_id VARCHAR2 (100);
2328 l_orig_system wf_roles.orig_system%TYPE;
2329 l_orig_system_id wf_roles.orig_system_id%TYPE;
2330 l_person_id NUMBER;
2331 l_s_count NUMBER := 0;
2332 l_step VARCHAR2 (200);
2333 xx_ame EXCEPTION;
2334 l_approver ame_util.approverrecord2;
2335 l_parent_itemkey VARCHAR2 (100);
2336 l_parent_itemtype VARCHAR2 (20);
2337 l_ame_transaction_type VARCHAR2 (250);
2338 BEGIN
2339 --
2340 -- RUN mode - normal process execution
2341 --
2342 IF (funcmode = 'RUN')
2343 THEN
2344 BEGIN
2345 SELECT parent_item_key, parent_item_type
2346 INTO l_parent_itemkey, l_parent_itemtype
2347 FROM wf_items
2348 WHERE item_type = itemtype AND item_key = itemkey;
2349 EXCEPTION
2350 WHEN OTHERS
2351 THEN
2352 IF l_debug_level > 0 THEN
2353 oe_debug_pub.ADD ('Error in getting parent item key:'
2354 || SQLERRM
2355 );
2356 END IF ;
2357 END;
2358
2359 l_transaction_id := TO_NUMBER (itemkey);
2360 oe_standard_wf.set_msg_context (actid);
2361 oe_msg_pub.set_msg_context
2362 (p_entity_code => 'HEADER',
2363 p_entity_id => TO_NUMBER
2364 (l_parent_itemkey),
2365 p_header_id => TO_NUMBER
2369 IF l_debug_level > 0
2366 (l_parent_itemkey)
2367 );
2368
2370 THEN
2371 oe_debug_pub.ADD ('Entering PROCESS_RESPONSE_APPROVE ', 1);
2372 END IF;
2373
2374 -- We need to set the status of the last approver to APPROVED here
2375 -- in the OE_APPROVER_TRANSACTIONS
2376 l_role :=
2377 wf_engine.getitemattrtext (itemtype,
2378 l_transaction_id,
2379 'NOTIFICATION_APPROVER'
2380 );
2381
2382 IF l_parent_itemtype = oe_globals.g_wfi_ngo
2383 THEN
2384 BEGIN
2385 l_sales_document_type_code :=
2386 wf_engine.getitemattrtext (itemtype,
2387 l_transaction_id,
2388 'SALES_DOCUMENT_TYPE_CODE'
2389 );
2390 EXCEPTION
2391 WHEN OTHERS
2392 THEN
2393 IF l_debug_level > 0 THEN
2394 oe_debug_pub.ADD ('l_sales_document_type_code - Exception');
2395 oe_debug_pub.ADD
2396 ( 'l_sales_document_type_code - Exception'
2397 || SQLERRM
2398 );
2399 END IF ;
2400 RAISE;
2401 END;
2402 END IF;
2403
2404 ------------
2405 BEGIN
2406 l_step := 'get Attr for N APP';
2407 IF l_debug_level > 0 THEN
2408 oe_debug_pub.ADD ('l_setp - ' || l_step);
2409 oe_debug_pub.ADD ('l_setp - ' || l_step);
2410 END IF ;
2411 l_name :=
2412 wf_engine.getitemattrtext (itemtype,
2413 l_transaction_id,
2414 'NOTIFICATION_APPROVER'
2415 );
2416 l_orig_system :=
2417 wf_engine.getitemattrtext (itemtype,
2418 l_transaction_id,
2419 'ORIG_SYSTEM'
2420 );
2421 l_orig_system_id :=
2422 wf_engine.getitemattrtext (itemtype,
2423 l_transaction_id,
2424 'ORIG_SYSTEM_ID'
2425 );
2426 l_user_id :=
2427 wf_engine.getitemattrtext (itemtype,
2428 l_transaction_id,
2429 'USER_ID'
2430 );
2431 IF l_debug_level > 0 THEN
2432 oe_debug_pub.ADD ( 'l_name - '
2433 || l_name
2434 || ' Orig System - '
2435 || l_orig_system
2436 || 'Orig Sys ID - '
2437 || l_orig_system_id
2438 || '-'
2439 || l_user_id
2440 );
2441 END IF ;
2442 l_step := 'Get User ID';
2443
2444 IF l_parent_itemtype = 'OENH'
2445 THEN
2446 IF l_sales_document_type_code='O' THEN -- 16084377 AME BSA
2447 l_ame_transaction_type := 'OENEG';
2448 ELSE
2449 l_ame_transaction_type := 'OEBSA';
2450 END IF ;
2451 ELSIF l_parent_itemtype = 'OEOH'
2452 THEN
2453 l_ame_transaction_type := 'OERMA';
2454 END IF;
2455
2456 IF l_orig_system = 'FND_RESP'
2457 THEN
2458 --L_User_Id := L_Orig_System_Id;
2459 l_step := 'updateApprovalStatus2';
2460 IF l_debug_level > 0 THEN
2461 oe_debug_pub.ADD ('l_setp - ' || l_step);
2462 oe_debug_pub.ADD ('l_setp - ' || l_step);
2463 END IF ;
2464 ame_api2.updateapprovalstatus2
2465 (applicationidin => 660,
2466 transactiontypein => l_ame_transaction_type,
2467 --itemtype,
2468 transactionidin => TO_NUMBER
2469 (l_parent_itemkey),
2470 approvalstatusin => ame_util.approvedstatus,
2471 approvernamein => l_name,
2472 itemclassin => 'header',
2473 itemidin => TO_NUMBER
2474 (l_parent_itemkey)
2475 );
2476 ELSIF l_orig_system = 'FND_USR'
2477 THEN
2478 l_user_id := l_orig_system_id;
2479 l_step := 'updateApprovalStatus2';
2480 IF l_debug_level > 0 THEN
2481 oe_debug_pub.ADD ('l_setp - ' || l_step);
2482 oe_debug_pub.ADD ('l_setp - ' || l_step);
2483 END IF ;
2484 ame_api2.updateapprovalstatus2
2485 (applicationidin => 660,
2486 transactiontypein => l_ame_transaction_type,
2487 --itemtype,
2491 approvernamein => l_name,
2488 transactionidin => TO_NUMBER
2489 (l_parent_itemkey),
2490 approvalstatusin => ame_util.approvedstatus,
2492 itemclassin => 'header',
2493 itemidin => TO_NUMBER
2494 (l_parent_itemkey)
2495 );
2496 ELSIF l_orig_system = 'PER'
2497 THEN
2498 l_person_id := l_orig_system_id;
2499 l_step := 'updateApprovalStatus2';
2500 IF l_debug_level > 0 THEN
2501 oe_debug_pub.ADD ('l_setp - ' || l_step);
2502 oe_debug_pub.ADD ('l_setp - ' || l_step);
2503 END IF ;
2504 ame_api2.updateapprovalstatus2
2505 (applicationidin => 660,
2506 transactiontypein => l_ame_transaction_type,
2507 --itemtype,
2508 transactionidin => TO_NUMBER
2509 (l_parent_itemkey),
2510 approvalstatusin => ame_util.approvedstatus,
2511 approvernamein => l_name,
2512 itemclassin => 'header',
2513 itemidin => TO_NUMBER
2514 (l_parent_itemkey)
2515 );
2516 END IF;
2517
2518 resultout := 'COMPLETE';
2519 IF l_debug_level > 0 THEN
2520 oe_debug_pub.ADD ( 'USER ID - '
2521 || l_user_id
2522 || ' Person ID - '
2523 || l_person_id
2524 );
2525 oe_debug_pub.ADD ( 'USER ID - '
2526 || l_user_id
2527 || ' Person ID - '
2528 || l_person_id
2529 );
2530 END IF ;
2531 l_aname (1) := 'APPROVER_RESPONSE';
2532 l_avaluetext (1) := 'APPROVED';
2533 l_aname (2) := 'AME_SUB_APPROVAL_RESPONSE';
2534 l_avaluetext (2) := 'APPROVED';
2535 wf_engine.setitemattrtextarray (itemtype,
2536 itemkey,
2537 l_aname,
2538 l_avaluetext
2539 );
2540 wf_engine.setitemattrtextarray (l_parent_itemtype,
2541 l_parent_itemkey,
2542 l_aname,
2543 l_avaluetext
2544 );
2545 IF l_debug_level > 0 THEN
2546 oe_debug_pub.ADD
2547 ('Set the approver response attribute to approved');
2548 END IF ;
2549 EXCEPTION
2550 WHEN OTHERS
2551 THEN
2552 IF l_debug_level > 0 THEN
2553 oe_debug_pub.ADD ( 'Call AME to Update Status-Approved - '
2554 || itemtype
2555 || '-'
2556 || itemkey,
2557 NULL || '-' || l_application_id
2558 );
2559 oe_debug_pub.ADD ( 'Call AME to Update Status-Approved - '
2560 || itemtype
2561 || '-'
2562 || itemkey
2563 || '-'
2564 || l_application_id
2565 || SQLERRM
2566 );
2567 END IF ;
2568 RAISE xx_ame;
2569 END;
2570 END IF;
2571 EXCEPTION
2572 WHEN xx_ame
2573 THEN
2574 wf_engine.setitemattrtext (l_parent_itemtype,
2575 l_parent_itemkey,
2576 'AME_EXCEPTION',
2577 'Y'
2578 );
2579 resultout := 'COMPLETE';
2580 WHEN OTHERS
2581 THEN
2582 IF l_debug_level > 0 THEN
2583 oe_debug_pub.ADD ('error in PROCESS_RESPONSE_APPROVE:' || SQLERRM);
2584 oe_debug_pub.ADD ('error in PROCESS_RESPONSE_APPROVE:' || SQLERRM);
2585 END IF ;
2586 wf_core.CONTEXT ('OE_Approvals_WF',
2587 'process_response_approve',
2588 itemtype,
2589 itemkey,
2590 TO_CHAR (actid),
2591 funcmode,
2592 SQLERRM
2593 );
2594 oe_standard_wf.add_error_activity_msg (p_actid => actid,
2595 p_itemtype => itemtype,
2596 p_itemkey => itemkey
2597 );
2598 oe_standard_wf.save_messages;
2599 oe_standard_wf.clear_msg_context;
2600 RAISE;
2601 END process_response_approve;
2602
2603 PROCEDURE process_response_reject (
2604 itemtype IN VARCHAR2,
2605 itemkey IN VARCHAR2,
2609 )
2606 actid IN NUMBER,
2607 funcmode IN VARCHAR2,
2608 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2610 IS
2611 l_transaction_id NUMBER;
2612 l_role VARCHAR2 (240);
2613 l_sales_document_type_code VARCHAR2 (30);
2614 l_return_status VARCHAR2 (30);
2615 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2616 l_sold_to_org_id NUMBER;
2617 l_salesrep_id NUMBER;
2618 l_salesrep VARCHAR2 (240);
2619 l_sold_to VARCHAR2 (240);
2620 --l_customer_number NUMBER;
2621 l_customer_number VARCHAR2 (30); --bug4575846
2622 l_expiration_date DATE;
2623 l_aname wf_engine.nametabtyp;
2624 l_avaluetext wf_engine.texttabtyp;
2625 ----++++++++++++++++++++++++++++++++++++++++++++
2626 l_admin_approver ame_util.approverrecord;
2627 l_ret_approver VARCHAR2 (50);
2628 l_name wf_users.NAME%TYPE; --bug 8620671
2629 l_display_name VARCHAR2 (150);
2630 l_debug_info VARCHAR2 (50);
2631 --l_role VARCHAR2(50);
2632 l_role_display VARCHAR2 (150);
2633 l_org_id NUMBER (15);
2634 l_error_message VARCHAR2 (2000);
2635 l_invoice_id NUMBER (15);
2636 l_iteration NUMBER (9);
2637 l_count NUMBER (9);
2638 l_hist_rec ap_inv_aprvl_hist%ROWTYPE;
2639 l_notf_iteration NUMBER;
2640 l_complete VARCHAR2 (1);
2641 l_next_approvers ame_util.approverstable2;
2642 l_next_approver ame_util.approverrecord2;
2643 l_index ame_util.idlist;
2644 l_ids ame_util.stringlist;
2645 l_class ame_util.stringlist;
2646 l_source ame_util.longstringlist;
2647 l_ampersand VARCHAR2 (1);
2648 l_next_approver_s ame_util.approverrecord;
2649 --itemkey VARCHAR2(100):='';
2650 l_application_id NUMBER := 660;
2651 --l_transaction_id VARCHAR2(100); --'232349';
2652 l_transaction_type_id VARCHAR2 (100); --'OENH';
2653 l_user_id VARCHAR2 (100);
2654 l_orig_system wf_roles.orig_system%TYPE;
2655 l_orig_system_id wf_roles.orig_system_id%TYPE;
2656 l_person_id NUMBER;
2657 l_s_count NUMBER := 0;
2658 l_step VARCHAR2 (200);
2659 xx_ame EXCEPTION;
2660 l_approver ame_util.approverrecord2;
2661 l_parent_itemkey VARCHAR2 (100);
2662 l_parent_item_type VARCHAR2 (20);
2663 l_ame_transaction_type VARCHAR2 (250);
2664 BEGIN
2665 --
2666 -- RUN mode - normal process execution
2667 --
2668 IF (funcmode = 'RUN')
2669 THEN
2670 BEGIN
2671 SELECT parent_item_key, parent_item_type
2672 INTO l_parent_itemkey, l_parent_item_type
2673 FROM wf_items
2674 WHERE item_type = itemtype AND item_key = itemkey;
2675 EXCEPTION
2676 WHEN OTHERS
2677 THEN
2678 IF l_debug_level > 0 THEN
2679 oe_debug_pub.ADD ('Error in getting parent item key:'
2680 || SQLERRM
2681 );
2682 END IF ;
2683 END;
2684
2685 l_transaction_id := TO_NUMBER (itemkey);
2686 oe_standard_wf.set_msg_context (actid);
2687 oe_msg_pub.set_msg_context
2688 (p_entity_code => 'HEADER',
2689 p_entity_id => TO_NUMBER
2690 (l_parent_itemkey),
2691 p_header_id => TO_NUMBER
2692 (l_parent_itemkey)
2693 );
2694
2695 IF l_debug_level > 0
2696 THEN
2697 oe_debug_pub.ADD ('Entering PROCESS_RESPONSE_REJECT ', 1);
2698 END IF;
2699
2700 -- We need to set the status of the last approver to APPROVED here
2701 -- in the OE_APPROVER_TRANSACTIONS
2702 l_role :=
2703 wf_engine.getitemattrtext (itemtype,
2704 l_transaction_id,
2705 'NOTIFICATION_APPROVER'
2706 );
2707
2708 IF l_parent_item_type = oe_globals.g_wfi_ngo
2709 THEN
2710 BEGIN
2711 l_sales_document_type_code :=
2712 wf_engine.getitemattrtext (itemtype,
2713 l_transaction_id,
2714 'SALES_DOCUMENT_TYPE_CODE'
2715 );
2716 EXCEPTION
2717 WHEN OTHERS
2718 THEN
2719 IF l_debug_level > 0 THEN
2720 oe_debug_pub.ADD ('l_sales_document_type_code - Exception');
2721 oe_debug_pub.ADD
2722 ( 'l_sales_document_type_code - Exception'
2723 || SQLERRM
2724 );
2725 END IF ;
2726 RAISE;
2727 END;
2731 BEGIN
2728 END IF;
2729
2730 ------------
2732 l_step := 'get Attr for N APP';
2733 IF l_debug_level > 0 THEN
2734 oe_debug_pub.ADD ('l_setp - ' || l_step);
2735 oe_debug_pub.ADD ('l_setp - ' || l_step);
2736 END IF ;
2737 l_name :=
2738 wf_engine.getitemattrtext (itemtype,
2739 l_transaction_id,
2740 'NOTIFICATION_APPROVER'
2741 );
2742 l_orig_system :=
2743 wf_engine.getitemattrtext (itemtype,
2744 l_transaction_id,
2745 'ORIG_SYSTEM'
2746 );
2747 l_orig_system_id :=
2748 wf_engine.getitemattrtext (itemtype,
2749 l_transaction_id,
2750 'ORIG_SYSTEM_ID'
2751 );
2752 l_user_id :=
2753 wf_engine.getitemattrtext (itemtype,
2754 l_transaction_id,
2755 'USER_ID'
2756 );
2757 IF l_debug_level > 0 THEN
2758 oe_debug_pub.ADD ( 'l_name - '
2759 || l_name
2760 || ' Orig System - '
2761 || l_orig_system
2762 || 'Orig Sys ID - '
2763 || l_orig_system_id
2764 || '-'
2765 || l_user_id
2766 );
2767 END IF ;
2768 l_step := 'Get User ID';
2769
2770 IF l_parent_item_type = 'OENH'
2771 THEN
2772 IF l_sales_document_type_code='O' THEN -- 16084377 AME BSA
2773 l_ame_transaction_type := 'OENEG';
2774 ELSE
2775 l_ame_transaction_type := 'OEBSA';
2776 END IF ;
2777 ELSIF l_parent_item_type = 'OEOH'
2778 THEN
2779 l_ame_transaction_type := 'OERMA';
2780 END IF;
2781
2782 IF l_orig_system = 'FND_RESP'
2783 THEN
2784 --L_User_Id := L_Orig_System_Id;
2785 l_step := 'updateApprovalStatus2';
2786 IF l_debug_level > 0 THEN
2787 oe_debug_pub.ADD ('l_setp - ' || l_step);
2788 oe_debug_pub.ADD ('l_setp - ' || l_step);
2789 END IF ;
2790 ame_api2.updateapprovalstatus2
2791 (applicationidin => 660,
2792 transactiontypein => l_ame_transaction_type,
2793 --itemtype,
2794 transactionidin => TO_NUMBER
2795 (l_parent_itemkey),
2796 approvalstatusin => ame_util.rejectstatus,
2797 approvernamein => l_name,
2798 itemclassin => 'header',
2799 itemidin => TO_NUMBER
2800 (l_parent_itemkey)
2801 );
2802 ELSIF l_orig_system = 'FND_USR'
2803 THEN
2804 l_user_id := l_orig_system_id;
2805 l_step := 'updateApprovalStatus2';
2806 IF l_debug_level > 0 THEN
2807 oe_debug_pub.ADD ('l_setp - ' || l_step);
2808 oe_debug_pub.ADD ('l_setp - ' || l_step);
2809 END IF ;
2810 ame_api2.updateapprovalstatus2
2811 (applicationidin => 660,
2812 transactiontypein => l_ame_transaction_type,
2813 --itemtype,
2814 transactionidin => TO_NUMBER
2815 (l_parent_itemkey),
2816 approvalstatusin => ame_util.rejectstatus,
2817 approvernamein => l_name,
2818 itemclassin => 'header',
2819 itemidin => TO_NUMBER
2820 (l_parent_itemkey)
2821 );
2822 ELSIF l_orig_system = 'PER'
2823 THEN
2824 l_person_id := l_orig_system_id;
2825 l_step := 'updateApprovalStatus2';
2826 IF l_debug_level > 0 THEN
2827 oe_debug_pub.ADD ('l_setp - ' || l_step);
2828 oe_debug_pub.ADD ('l_setp - ' || l_step);
2829 END IF ;
2830 ame_api2.updateapprovalstatus2
2831 (applicationidin => 660,
2832 transactiontypein => l_ame_transaction_type,
2833 --itemtype,
2834 transactionidin => TO_NUMBER
2835 (l_parent_itemkey),
2836 approvalstatusin => ame_util.rejectstatus,
2837 approvernamein => l_name,
2838 itemclassin => 'header',
2839 itemidin => TO_NUMBER
2840 (l_parent_itemkey)
2841 );
2842 END IF;
2843
2844 resultout := 'COMPLETE';
2845 IF l_debug_level > 0 THEN
2849 || l_person_id
2846 oe_debug_pub.ADD ( 'USER ID - '
2847 || l_user_id
2848 || ' Person ID - '
2850 );
2851 oe_debug_pub.ADD ( 'USER ID - '
2852 || l_user_id
2853 || ' Person ID - '
2854 || l_person_id
2855 );
2856 END IF ;
2857 l_aname (1) := 'APPROVER_RESPONSE';
2858 l_avaluetext (1) := 'REJECTED';
2859 l_aname (2) := 'AME_SUB_APPROVAL_RESPONSE';
2860 l_avaluetext (2) := 'REJECT';
2861 wf_engine.setitemattrtextarray (itemtype,
2862 itemkey,
2863 l_aname,
2864 l_avaluetext
2865 );
2866 wf_engine.setitemattrtextarray (l_parent_item_type,
2867 l_parent_itemkey,
2868 l_aname,
2869 l_avaluetext
2870 );
2871 IF l_debug_level > 0 THEN
2872 oe_debug_pub.ADD
2873 ('Set the approver response attribute to rejected');
2874 END IF ;
2875 EXCEPTION
2876 WHEN OTHERS
2877 THEN
2878 IF l_debug_level > 0 THEN
2879 oe_debug_pub.ADD ( 'Call AME to Update Status-Approved - '
2880 || itemtype
2881 || '-'
2882 || itemkey,
2883 NULL || '-' || l_application_id
2884 );
2885 oe_debug_pub.ADD ( 'Call AME to Update Status-Approved - '
2886 || itemtype
2887 || '-'
2888 || itemkey
2889 || '-'
2890 || l_application_id
2891 || SQLERRM
2892 );
2893 END IF ;
2894 RAISE xx_ame;
2895 END;
2896 END IF;
2897 EXCEPTION
2898 WHEN xx_ame
2899 THEN
2900 wf_engine.setitemattrtext (l_parent_item_type,
2901 l_parent_itemkey,
2902 'AME_EXCEPTION',
2903 'Y'
2904 );
2905 resultout := 'COMPLETE';
2906 WHEN OTHERS
2907 THEN
2908 IF l_debug_level > 0 THEN
2909 oe_debug_pub.ADD ('error in PROCESS_RESPONSE_APPROVE:' || SQLERRM);
2910 oe_debug_pub.ADD ('error in PROCESS_RESPONSE_APPROVE:' || SQLERRM);
2911 END IF ;
2912 wf_core.CONTEXT ('OE_Approvals_WF',
2913 'process_response_reject',
2914 itemtype,
2915 itemkey,
2916 TO_CHAR (actid),
2917 funcmode,
2918 SQLERRM
2919 );
2920 oe_standard_wf.add_error_activity_msg (p_actid => actid,
2921 p_itemtype => itemtype,
2922 p_itemkey => itemkey
2923 );
2924 oe_standard_wf.save_messages;
2925 oe_standard_wf.clear_msg_context;
2926 RAISE;
2927 END process_response_reject;
2928
2929 PROCEDURE process_beat_by_first (
2930 itemtype IN VARCHAR2,
2931 itemkey IN VARCHAR2,
2932 actid IN NUMBER,
2933 funcmode IN VARCHAR2,
2934 resultout OUT NOCOPY VARCHAR2
2935 )
2936 IS
2937 l_progress VARCHAR2 (500) := '000';
2938 l_parent_item_type wf_items.parent_item_type%TYPE;
2939 l_parent_item_key wf_items.parent_item_key%TYPE;
2940 l_child_approver_empid NUMBER;
2941 l_child_approver_groupid NUMBER;
2942 --l_approver_group_id NUMBER;
2943 l_header_id NUMBER;
2944 l_process_out VARCHAR2 (10);
2945 approverlist ame_util.approverstable2;
2946 ametransactiontype po_document_types.ame_transaction_type%TYPE;
2947 l_response_action VARCHAR2 (20);
2948 l_note VARCHAR2 (4000);
2949 l_person_id NUMBER;
2950 l_orig_system VARCHAR2 (3);
2951 l_orig_system_id NUMBER;
2952 l_first_approver_id NUMBER;
2953 l_first_position_id NUMBER;
2954 l_preparer_user_name fnd_user.user_name%TYPE;
2955 l_doc_string VARCHAR2 (200);
2956 l_ame_exception ame_util.longeststringtype;
2957 l_approver_response VARCHAR2 (20);
2958 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2959 xx_ame EXCEPTION;
2960
2961 CURSOR l_child_wf (
2962 p_itemtype IN wf_items.parent_item_type%TYPE,
2963 p_itemkey IN wf_items.parent_item_key%TYPE
2964 )
2965 IS
2966 SELECT wfi.item_type, wfi.item_key
2967 FROM wf_items wfi, wf_item_activity_statuses wfias
2968 WHERE wfi.parent_item_key = p_itemkey
2969 AND wfi.item_type = 'OEAME'
2970 AND wfi.parent_item_type = p_itemtype
2974 AND wfias.notification_id IS NOT NULL
2971 AND wfias.item_type = wfi.item_type
2972 AND wfias.item_key = wfi.item_key
2973 AND wfias.activity_status = 'NOTIFIED'
2975 AND wfi.item_key <> itemkey;
2976
2977 l_child_wf_cur l_child_wf%ROWTYPE;
2978 l_current_person_id NUMBER;
2979 l_current_appr_group_id NUMBER;
2980 l_abort NUMBER;
2981 l_sales_document_type_code VARCHAR2(240); -- 16084377 ame bsa
2982 BEGIN
2983 IF l_debug_level > 0
2984 THEN
2985 oe_debug_pub.ADD ('Entering Process beat by first');
2986 END IF;
2987
2988 SELECT parent_item_type, parent_item_key
2989 INTO l_parent_item_type, l_parent_item_key
2990 FROM wf_items
2991 WHERE item_type = itemtype AND item_key = itemkey;
2992 IF l_debug_level > 0 THEN
2993 oe_debug_pub.ADD ('After getting parent details');
2994 END IF ;
2995 /* Check if there we have encountered any ame exception.
2996 If the value of ame_exception is not null, then we have faced some exception.
2997 So just comlete the block activity and return */
2998 l_ame_exception :=
2999 wf_engine.getitemattrtext (itemtype => l_parent_item_type,
3000 itemkey => l_parent_item_key,
3001 aname => 'AME_EXCEPTION'
3002 );
3003 IF l_debug_level > 0 THEN
3004 oe_debug_pub.ADD ( 'After gettingAME exception details:'
3005 || l_ame_exception
3006 );
3007 END IF ;
3008 l_current_person_id :=
3009 wf_engine.getitemattrtext (itemtype => itemtype,
3010 itemkey => itemkey,
3011 aname => 'ORIG_SYSTEM_ID'
3012 );
3013
3014 IF l_ame_exception IS NOT NULL
3015 THEN
3016 resultout := 'COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
3017 RETURN;
3018 END IF;
3019
3020 /* l_approver_group_id := wf_engine.GetItemAttrText( itemtype => itemtype,
3021 itemkey => itemkey,
3022 aname => 'APPROVAL_GROUP_ID');*/
3023 /*l_header_id :=
3024 wf_engine.getitemattrnumber (itemtype => l_parent_item_type,
3025 itemkey => l_parent_item_key,
3026 aname => 'HEADER_ID'
3027 ); not required. Since this procedure is always called
3028 only from child parallel flow, parent item key is the header id always*/
3029
3030
3031 l_header_id := TO_NUMBER (l_parent_item_key);
3032
3033 IF l_debug_level > 0 THEN
3034 oe_debug_pub.ADD ('Header id derived is:' || l_header_id);
3035 END IF ;
3036 l_approver_response :=
3037 wf_engine.getitemattrtext (itemtype => itemtype,
3038 itemkey => itemkey,
3039 aname => 'APPROVER_RESPONSE'
3040 );
3041 IF l_debug_level > 0 THEN
3042 oe_debug_pub.ADD ('Approver response is:' || l_approver_response);
3043 END IF ;
3044 IF l_approver_response = 'APPROVED'
3045 THEN
3046 IF l_parent_item_type = 'OENH'
3047 THEN
3048 BEGIN
3049 l_sales_document_type_code :=
3050 wf_engine.getitemattrtext (itemtype,
3051 itemkey,
3052 'SALES_DOCUMENT_TYPE_CODE'
3053 );
3054 EXCEPTION
3055 WHEN OTHERS THEN NULL ;
3056 END ; -- 16084377 AME BSA
3057 IF l_sales_document_type_code = 'O' THEN
3058 ametransactiontype := 'OENEG';
3059 ELSE
3060 ametransactiontype := 'OEBSA';
3061 END IF ;
3062 ELSIF l_parent_item_type = 'OEOH'
3063 THEN
3064 ametransactiontype := 'OERMA';
3065 END IF;
3066 IF l_debug_level > 0 THEN
3067 oe_debug_pub.ADD ( 'After getting AME transaction type:'
3068 || ametransactiontype
3069 );
3070 END IF ;
3071 BEGIN
3072 ame_api2.getallapprovers7
3073 (applicationidin => 660,
3074 transactionidin => l_header_id,
3075 transactiontypein => ametransactiontype,
3076 approvalprocesscompleteynout => l_process_out,
3077 approversout => approverlist
3078 );
3079 EXCEPTION
3080 WHEN OTHERS
3081 THEN
3082 RAISE xx_ame;
3083 END;
3084
3085 -- Once we get the approvers list from AME, we iterate through the approvers list,
3086 -- to find out the current first authority approver.
3087 FOR i IN 1 .. approverlist.COUNT
3088 LOOP
3089 IF approverlist (i).orig_system_id = l_current_person_id
3090 THEN
3091 l_current_appr_group_id := approverlist (i).group_or_chain_id;
3092 END IF;
3093 END LOOP;
3094
3095 FOR i IN 1 .. approverlist.COUNT
3096 LOOP
3097 IF l_debug_level > 0
3098 THEN
3102 );
3099 oe_debug_pub.ADD ( 'authority'
3100 || ' '
3101 || approverlist (i).authority
3103 oe_debug_pub.ADD ( 'approval_status'
3104 || ' '
3105 || approverlist (i).approval_status
3106 );
3107 oe_debug_pub.ADD ( 'api_insertion'
3108 || ' '
3109 || approverlist (i).api_insertion
3110 );
3111 oe_debug_pub.ADD ( 'group_or_chain_id'
3112 || ' '
3113 || approverlist (i).group_or_chain_id
3114 );
3115 END IF;
3116
3117 IF ( approverlist (i).approval_status =
3118 ame_util.beatbyfirstresponderstatus
3119 AND approverlist (i).api_insertion = ame_util.oamgenerated
3120 AND approverlist (i).group_or_chain_id =
3121 l_current_appr_group_id
3122 )
3123 THEN
3124 l_orig_system := approverlist (i).orig_system;
3125 l_orig_system_id := approverlist (i).orig_system_id;
3126
3127 IF (l_orig_system = ame_util.perorigsystem)
3128 THEN
3129 -- Employee Supervisor Record.
3130 l_person_id := l_orig_system_id;
3131 ELSIF (l_orig_system = ame_util.posorigsystem)
3132 THEN
3133 -- Position Hierarchy Record.
3134 BEGIN
3135 SELECT person_id
3136 INTO l_person_id
3137 FROM (SELECT person.person_id
3138 FROM per_all_people_f person,
3139 per_all_assignments_f asg
3140 WHERE asg.position_id = l_orig_system_id
3141 AND TRUNC (SYSDATE)
3142 BETWEEN person.effective_start_date
3143 AND NVL
3144 (person.effective_end_date,
3145 TRUNC (SYSDATE)
3146 )
3147 AND person.person_id = asg.person_id
3148 AND asg.primary_flag = 'Y'
3149 AND asg.assignment_type IN ('E', 'C')
3150 AND ( person.current_employee_flag = 'Y'
3151 OR person.current_npw_flag = 'Y'
3152 )
3153 AND asg.assignment_status_type_id NOT IN (
3154 SELECT assignment_status_type_id
3155 FROM per_assignment_status_types
3156 WHERE per_system_status =
3157 'TERM_ASSIGN')
3158 AND TRUNC (SYSDATE)
3159 BETWEEN asg.effective_start_date
3160 AND asg.effective_end_date
3161 ORDER BY person.last_name)
3162 WHERE ROWNUM = 1;
3163 EXCEPTION
3164 WHEN NO_DATA_FOUND
3165 THEN
3166 l_person_id := -1;
3167 END;
3168 ELSIF (l_orig_system = ame_util.fnduserorigsystem)
3169 THEN
3170 -- FND User Record.
3171 SELECT employee_id
3172 INTO l_person_id
3173 FROM fnd_user
3174 WHERE user_id = l_orig_system_id
3175 AND TRUNC (SYSDATE) BETWEEN start_date
3176 AND NVL (end_date, SYSDATE + 1);
3177 END IF;
3178
3179 -- stop the workflow
3180 IF l_debug_level > 0 THEN
3181 oe_debug_pub.ADD ('before getting child workflows');
3182 END IF ;
3183
3184 OPEN l_child_wf (l_parent_item_type, l_parent_item_key);
3185
3186 LOOP
3187 FETCH l_child_wf
3188 INTO l_child_wf_cur;
3189
3190 EXIT WHEN l_child_wf%NOTFOUND;
3191 IF l_debug_level > 0 THEN
3192 oe_debug_pub.ADD ('Inside getting child workflows');
3193 END IF ;
3194
3195 /* l_child_approver_empid := wf_engine.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
3196 itemkey => l_child_wf_cur.item_key,
3197 aname => 'APPROVER_EMPID');
3198
3199 l_child_approver_groupid :=wf_engine.GetItemAttrNumber( itemtype => l_child_wf_cur.item_type,
3200 itemkey => l_child_wf_cur.item_key,
3201 aname => 'APPROVAL_GROUP_ID');
3202
3203
3204 IF (l_child_approver_empid = l_person_id )--and
3205 --l_child_approver_groupid = l_approver_group_id)
3206 THEN
3207
3208
3209 EXIT;
3210
3211 END IF;*/
3215 FROM wf_item_attribute_values
3212 BEGIN
3213 SELECT 1
3214 INTO l_abort
3216 WHERE item_type = l_child_wf_cur.item_type
3217 AND NAME = 'ORIG_SYSTEM_ID'
3218 AND number_value = l_orig_system_id
3219 AND item_key = l_child_wf_cur.item_key;
3220 IF l_debug_level > 0 THEN
3221 oe_debug_pub.ADD ( 'before aborting the flow for:'
3222 || l_child_wf_cur.item_key
3223 );
3224 END IF ;
3225 wf_engine.abortprocess (l_child_wf_cur.item_type,
3226 l_child_wf_cur.item_key
3227 );
3228 EXCEPTION
3229 WHEN NO_DATA_FOUND
3230 THEN
3231 IF l_debug_level > 0 THEN
3232 oe_debug_pub.ADD
3233 ('this might be a parallel flow from dual chain of authority.do no abort'
3234 );
3235 END IF ;
3236 END;
3237 END LOOP;
3238
3239 CLOSE l_child_wf;
3240 END IF;
3241 END LOOP;
3242 ELSIF (l_approver_response = 'REJECTED')
3243 THEN
3244 OPEN l_child_wf (l_parent_item_type, l_parent_item_key);
3245
3246 LOOP
3247 FETCH l_child_wf
3248 INTO l_child_wf_cur;
3249
3250 EXIT WHEN l_child_wf%NOTFOUND;
3251 wf_engine.abortprocess (l_child_wf_cur.item_type,
3252 l_child_wf_cur.item_key
3253 );
3254 END LOOP;
3255
3256 CLOSE l_child_wf;
3257 END IF;
3258
3259 resultout := 'COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
3260 RETURN;
3261 EXCEPTION
3262 WHEN xx_ame
3263 THEN
3264 wf_engine.setitemattrtext (l_parent_item_type,
3265 l_parent_item_key,
3266 'AME_EXCEPTION',
3267 'Y'
3268 );
3269 resultout := 'COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
3270 WHEN OTHERS
3271 THEN
3272 IF l_debug_level > 0 THEN
3273 oe_debug_pub.ADD ('error in Process_Beat_By_First:' || SQLERRM);
3274 END IF ;
3275 wf_core.CONTEXT ('OE_Approvals_WF',
3276 'process_beat_by_first',
3277 itemtype,
3278 itemkey,
3279 TO_CHAR (actid),
3280 funcmode,
3281 SQLERRM
3282 );
3283 oe_standard_wf.add_error_activity_msg (p_actid => actid,
3284 p_itemtype => itemtype,
3285 p_itemkey => itemkey
3286 );
3287 oe_standard_wf.save_messages;
3288 oe_standard_wf.clear_msg_context;
3289 RAISE;
3290 END process_beat_by_first;
3291
3292 PROCEDURE use_ame_approval (
3293 itemtype IN VARCHAR2,
3294 itemkey IN VARCHAR2,
3295 actid IN NUMBER,
3296 funcmode IN VARCHAR2,
3297 resultout OUT NOCOPY VARCHAR2
3298 )
3299 IS
3300 l_ame_used VARCHAR2 (10);
3301 l_rejected_once VARCHAR2 (1);
3302 l_ame_transaction VARCHAR2 (250);
3303 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3304 l_sales_document_type_code varchar2(10);
3305 BEGIN
3306 IF l_debug_level > 0 THEN
3307 oe_debug_pub.ADD ('Entering IS_AME_APPROVAL');
3308 END IF ;
3309 g_ame_used := 'N';
3310
3311 IF itemtype = oe_globals.g_wfi_ngo
3312 THEN
3313 l_sales_document_type_code := wf_engine.GetItemAttrText(
3314 itemtype,
3315 itemkey,
3316 'SALES_DOCUMENT_TYPE_CODE');
3317 IF l_debug_level > 0 THEN
3318 oe_debug_pub.ADD ('item type is negotiation so AME TTY is OENEG');
3319 END IF ;
3320 IF l_sales_document_type_code = 'O' THEN
3321 l_ame_transaction := 'OENEG'; -- 16084377 AME BSA
3322 ELSE
3323 l_ame_transaction := 'OEBSA';
3324 END IF ;
3325 ELSIF itemtype = oe_globals.g_wfi_hdr
3326 THEN
3327 IF l_debug_level > 0 THEN
3328 oe_debug_pub.ADD ('item type is header so AME TTY is OERMA');
3329 END IF ;
3330 l_ame_transaction := 'OERMA';
3331 END IF;
3332
3333 BEGIN
3334 IF l_ame_transaction = 'OEBSA' THEN -- 16084377 AME BSA
3335
3336
3337 SELECT use_ame_approval
3338 INTO l_ame_used
3339 FROM oe_transaction_types_all
3340 WHERE transaction_type_id = (SELECT order_type_id
3341 FROM oe_blanket_headers_all -- 16084377 AME BSA
3342 WHERE header_id = TO_NUMBER (itemkey));
3343
3344 ELSE
3345
3346 SELECT use_ame_approval
3347 INTO l_ame_used
3348 FROM oe_transaction_types_all
3349 WHERE transaction_type_id = (SELECT order_type_id
3353 END IF ;
3350 FROM oe_order_headers_all
3351 WHERE header_id = TO_NUMBER (itemkey));
3352
3354 EXCEPTION
3355 WHEN NO_DATA_FOUND
3356 THEN
3357 l_ame_used := NULL;
3358 END;
3359
3360 IF NVL(l_ame_used,'N') = 'N'
3361 THEN
3362 resultout := 'COMPLETE:N';
3363 RETURN;
3364 ELSE
3365 resultout := 'COMPLETE:Y';
3366 oe_approvals_wf.g_ame_used := 'Y';
3367 IF l_debug_level > 0 THEN
3368 oe_debug_pub.ADD
3369 ('AME approval is used.check if the transaction is rejected in the past'
3370 );
3371 END IF ;
3372 BEGIN
3373 SELECT 'Y'
3374 INTO l_rejected_once
3375 FROM wf_item_activity_statuses_h wiash,
3376 wf_process_activities wpa
3377 WHERE wiash.process_activity = wpa.instance_id
3378 AND wiash.item_type = itemtype
3379 AND wiash.item_key = itemkey
3380 AND wpa.activity_name = 'INTERNAL_APPROVAL_PROCESS_AME'
3381 AND wiash.activity_status = 'COMPLETE'
3382 AND wiash.activity_result_code = 'REJECTED'
3383 AND ROWNUM = 1;
3384 IF l_debug_level > 0 THEN
3385 oe_debug_pub.ADD
3386 ('Yes it was rejected in the past. So reset the approvals');
3387 END IF ;
3388 EXCEPTION
3389 WHEN NO_DATA_FOUND
3390 THEN
3391 IF l_debug_level > 0 THEN
3392 oe_debug_pub.ADD ('No. This is the first time');
3393 END IF ;
3394 l_rejected_once := 'N';
3395 END;
3396
3397 IF l_rejected_once = 'Y'
3398 THEN
3399 IF l_debug_level > 0 THEN
3400 oe_debug_pub.ADD ('Calling AME API to reset the approvals');
3401 END IF ;
3402 BEGIN
3403 ame_api2.clearallapprovals
3404 (applicationidin => '660',
3405 transactiontypein => l_ame_transaction,
3406 transactionidin => TO_NUMBER
3407 (itemkey)
3408 );
3409 EXCEPTION
3410 WHEN OTHERS
3411 THEN
3412 IF l_debug_level > 0 THEN
3413 oe_debug_pub.ADD
3414 ('error is clearing approvals.correct the error and retry'
3415 );
3416 END IF ;
3417 END;
3418 END IF;
3419
3420 RETURN;
3421 END IF;
3422 EXCEPTION
3423 WHEN OTHERS
3424 THEN
3425 wf_core.CONTEXT ('OE_Approvals_WF',
3426 'use_ame_approval',
3427 itemtype,
3428 itemkey,
3429 TO_CHAR (actid),
3430 funcmode,
3431 SQLERRM
3432 );
3433 oe_standard_wf.add_error_activity_msg (p_actid => actid,
3434 p_itemtype => itemtype,
3435 p_itemkey => itemkey
3436 );
3437 oe_standard_wf.save_messages;
3438 oe_standard_wf.clear_msg_context;
3439 END use_ame_approval;
3440
3441 PROCEDURE push_block_activity (
3442 itemtype IN VARCHAR2,
3443 itemkey IN VARCHAR2,
3444 actid IN NUMBER,
3445 funcmode IN VARCHAR2,
3446 resultout OUT NOCOPY VARCHAR2
3447 )
3448 IS
3449 l_parent_item_key VARCHAR2 (100);
3450 l_parent_item_type VARCHAR2 (20);
3451 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3452 BEGIN
3453 IF l_debug_level > 0 THEN
3454 oe_debug_pub.ADD ('Entering Push_block_activity');
3455 END IF ;
3456 g_current_item_key := NULL;
3457
3458 SELECT parent_item_key, parent_item_type
3459 INTO l_parent_item_key, l_parent_item_type
3460 FROM wf_items
3461 WHERE item_type = itemtype AND item_key = itemkey;
3462
3463 g_current_item_key := itemkey;
3464
3465 BEGIN
3466 wf_engine.completeactivity (itemtype => l_parent_item_type,
3467 itemkey => l_parent_item_key,
3468 activity => 'WAIT_FOR_AME',
3469 RESULT => NULL
3470 );
3471 EXCEPTION
3472 WHEN OTHERS
3473 THEN
3474 NULL;
3475 END;
3476
3477 resultout := 'COMPLETE' || ':' || 'ACTIVITY_PERFORMED';
3478 RETURN;
3479 IF l_debug_level > 0 THEN
3480 oe_debug_pub.ADD ('Exiting Push_block_activity');
3481 END IF ;
3482 EXCEPTION
3483 WHEN OTHERS
3484 THEN
3485 IF l_debug_level > 0 THEN
3486 oe_debug_pub.ADD (SQLERRM);
3487 END IF ;
3488 wf_core.CONTEXT ('OE_Approvals_WF',
3489 'push_block_activity',
3490 itemtype,
3491 itemkey,
3492 TO_CHAR (actid),
3493 funcmode,
3494 SQLERRM
3495 );
3499 );
3496 oe_standard_wf.add_error_activity_msg (p_actid => actid,
3497 p_itemtype => itemtype,
3498 p_itemkey => itemkey
3500 oe_standard_wf.save_messages;
3501 oe_standard_wf.clear_msg_context;
3502 END push_block_activity;
3503
3504 PROCEDURE approval_timeout_ame (
3505 itemtype IN VARCHAR2,
3506 itemkey IN VARCHAR2,
3507 actid IN NUMBER,
3508 funcmode IN VARCHAR2,
3509 resultout IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
3510 )
3511 IS
3512 l_next_role VARCHAR2 (320);
3513 l_current_approver VARCHAR2 (320);
3514 l_sales_document_type_code VARCHAR2 (30);
3515 l_transaction_id NUMBER;
3516 l_return_status VARCHAR2 (30);
3517 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3518 BEGIN
3519 --
3520 -- RUN mode - normal process execution
3521 --
3522 IF (funcmode = 'RUN')
3523 THEN
3524 l_transaction_id := TO_NUMBER (itemkey);
3525 oe_standard_wf.set_msg_context (actid);
3526 oe_msg_pub.set_msg_context (p_entity_code => 'HEADER',
3527 p_entity_id => TO_NUMBER (itemkey),
3528 p_header_id => TO_NUMBER (itemkey)
3529 );
3530
3531 IF l_debug_level > 0
3532 THEN
3533 oe_debug_pub.ADD ('Approval_Timeout ame.. ', 1);
3534 END IF;
3535
3536 IF NVL (oe_sys_parameters.VALUE ('NO_RESPONSE_FROM_APPROVER'),
3537 'CONTINUE'
3538 ) = 'CONTINUE'
3539 THEN
3540 --just check the sys param value for AME. Last approver concept is not applicable here
3541 resultout := 'COMPLETE:CONTINUE';
3542 oe_standard_wf.clear_msg_context;
3543 RETURN;
3544 ELSE
3545 resultout := 'COMPLETE:REJECTED';
3546 oe_standard_wf.clear_msg_context;
3547 RETURN;
3548 END IF;
3549
3550 resultout := 'COMPLETE';
3551 oe_standard_wf.clear_msg_context;
3552 RETURN;
3553 END IF;
3554
3555 IF (funcmode = 'CANCEL')
3556 THEN
3557 -- your cancel code goes here
3558 NULL;
3559 -- no result needed
3560 resultout := 'COMPLETE';
3561 RETURN;
3562 END IF;
3563 EXCEPTION
3564 WHEN OTHERS
3565 THEN
3566 IF l_debug_level > 0
3567 THEN
3568 oe_debug_pub.ADD ('Exception in Approval_Timeout.. ', 1);
3569 END IF;
3570
3571 wf_core.CONTEXT ('OE_APPROVALS_WF',
3572 'Approval_Timeout_AME',
3573 itemtype,
3574 itemkey,
3575 TO_CHAR (actid),
3576 funcmode
3577 );
3578 -- start data fix project
3579 oe_standard_wf.add_error_activity_msg (p_actid => actid,
3580 p_itemtype => itemtype,
3581 p_itemkey => itemkey
3582 );
3583 oe_standard_wf.save_messages;
3584 oe_standard_wf.clear_msg_context;
3585 -- end data fix project
3586 RAISE;
3587 END approval_timeout_ame;
3588
3589 FUNCTION get_order_total (p_header_id IN NUMBER)
3590 RETURN NUMBER
3591 IS
3592 l_subtotal NUMBER;
3593 l_discount NUMBER;
3594 l_tax NUMBER;
3595 l_charges NUMBER;
3596 l_order_total NUMBER;
3597 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3598 BEGIN
3599 IF l_debug_level > 0 THEN
3600 oe_debug_pub.ADD ('Entering oe_approvals_Wf.get_order_total');
3601 END IF ;
3602 oe_oe_totals_summary.order_totals (p_header_id => p_header_id,
3603 p_subtotal => l_subtotal,
3604 p_discount => l_discount,
3605 p_charges => l_charges,
3606 p_tax => l_tax
3607 );
3608 IF l_debug_level > 0 THEN
3609 oe_debug_pub.ADD ('After getting all the totals');
3610 END IF ;
3611 l_order_total := l_subtotal + l_charges + l_tax;
3612 IF l_debug_level > 0 THEN
3613 oe_debug_pub.ADD ('returning:' || l_order_total);
3614 END IF ;
3615 RETURN l_order_total;
3616 EXCEPTION
3617 WHEN OTHERS
3618 THEN
3619 IF l_debug_level > 0 THEN
3620 oe_debug_pub.ADD ('Error in getting order total:' || SQLERRM);
3621 END IF ;
3622 RETURN 0;
3623 END get_order_total;
3624
3625 FUNCTION get_customer_category (p_header_id IN NUMBER, p_ame_type IN VARCHAR2)
3626 RETURN VARCHAR2
3627 IS
3628 l_cust_category VARCHAR2 (240);
3629 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3630 BEGIN
3631 IF l_debug_level > 0
3632 THEN
3633 oe_debug_pub.ADD ('Entering oe_approvals_Wf.get_customer_category');
3634 END IF;
3635
3636 if p_ame_type='OEBSA' THEN
3637 BEGIN
3638
3639 SELECT category_code
3640 INTO l_cust_category
3641 FROM hz_parties hp, fnd_lookup_values fl, fnd_languages fndl -- 16084377 AME BSA
3642 WHERE fl.lookup_type = 'CUSTOMER_CATEGORY'
3643 AND fl.LANGUAGE = fndl.language_code
3644 AND fndl.installed_flag = 'B'
3645 AND fl.lookup_code = hp.category_code
3646 AND party_id = (SELECT sold_to_org_id
3647 FROM oe_blanket_headers_all
3648 WHERE header_id = p_header_id);
3649 EXCEPTION
3650 WHEN OTHERS THEN
3651 l_cust_category:= NULL ;
3652 END ;
3653
3654 ELSE
3655
3656 BEGIN
3657 SELECT category_code
3658 INTO l_cust_category
3659 FROM hz_parties hp, fnd_lookup_values fl, fnd_languages fndl
3660 WHERE fl.lookup_type = 'CUSTOMER_CATEGORY'
3661 AND fl.LANGUAGE = fndl.language_code
3662 AND fndl.installed_flag = 'B'
3663 AND fl.lookup_code = hp.category_code
3664 AND party_id = (SELECT sold_to_org_id
3665 FROM oe_order_headers_all
3666 WHERE header_id = p_header_id);
3667 EXCEPTION
3668 WHEN OTHERS THEN
3669 l_cust_category:= NULL ;
3670 END ;
3671
3672 IF l_debug_level > 0
3673 THEN
3674 oe_debug_pub.ADD ('After getting Category');
3675 END IF;
3676
3677 end if ;
3678
3679 RETURN l_cust_category;
3680 EXCEPTION
3681 WHEN NO_DATA_FOUND
3682 THEN
3683 l_cust_category := NULL;
3684 RETURN l_cust_category;
3685 WHEN OTHERS
3686 THEN
3687 IF l_debug_level > 0
3688 THEN
3689 oe_debug_pub.ADD ('Error in getting order total:' || SQLERRM);
3690 END IF;
3691
3692 l_cust_category := NULL;
3693 RETURN l_cust_category;
3694 END get_customer_category;
3695
3696
3697
3698 END OE_APPROVALS_WF;