1 PACKAGE BODY OE_APPROVALS_WF AS
2 /* $Header: OEXWAPRB.pls 120.4.12010000.2 2008/10/21 06:01:08 smanian 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',
179 l_attachment_location);
180 END IF;
181
182
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 l_avaluetext(3) := l_expiration_date;
212
213 wf_engine.SetItemAttrTextArray( itemtype
214 , itemkey
215 , l_aname
216 , l_avaluetext
217 );
218 -- End setting Header Attributes
219 END IF;
220 -------itemtype = OE_GLOBALS.G_WFI_NGO-------
221
222 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
223 (p_item_type => itemtype,
224 p_header_id => l_transaction_id,
225 p_flow_status_code => 'PENDING_INTERNAL_APPROVAL',
226 p_sales_document_type_code => l_sales_document_type_code,
227 x_return_status => l_return_status );
228
229 IF l_debug_level > 0 THEN
230 oe_debug_pub.add('Initiate_Approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
231 END IF;
232
233 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
234 -- start data fix project
235 -- OE_STANDARD_WF.Save_Messages;
236 -- OE_STANDARD_WF.Clear_Msg_Context;
237 -- end data fix project
238 app_exception.raise_exception;
239 END IF;
240
241
242 resultout := 'COMPLETE:COMPLETE';
243 OE_STANDARD_WF.Clear_Msg_Context;
244 return;
245 end if;
246 end if; -- End for 'RUN' mode
247
248 --
249 -- CANCEL mode - activity 'compensation'
250 --
251 -- This is an event point is called with the effect of the activity must
252 -- be undone, for example when a process is reset to an earlier point
253 -- due to a loop back.
254 --
255 if (funcmode = 'CANCEL') then
256
257 -- your cancel code goes here
258 null;
259
260 -- no result needed
261 resultout := 'COMPLETE';
262 return;
263 end if;
264
265
266 --
267 -- Other execution modes may be created in the future. Your
268 -- activity will indicate that it does not implement a mode
269 -- by returning null
270 --
271 -- resultout := '';
272 -- return;
273
274 exception
275 when others then
276 -- The line below records this function call in the error system
277 -- in the case of an exception.
278 wf_core.context('OE_APPROVALS_WF', 'Initiate_Approval',
279 itemtype, itemkey, to_char(actid), funcmode);
280 -- start data fix project
281 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
282 p_itemtype => itemtype,
283 p_itemkey => itemkey);
284 OE_STANDARD_WF.Save_Messages;
285 OE_STANDARD_WF.Clear_Msg_Context;
286 -- end data fix project
287 raise;
288
289
290 END Initiate_Approval;
291
292
293 /**********************************
294 * Get_Next_Approver *
295 **********************************/
296 /*
297 This procedure sets the NOTIFICATION_APPROVER item attribute based on
298 the definition/setup in the OM Approver List form, insert/update
299 the proper record in the OM Approval transaction table
300 OE_APPROVAL_TRANSACTIONS. Checks the max(approver_sequence) from
301 OE_APPROVAL_TRANSACTIONS given a transaction_id, and then fetches
302 the role from the OE_APPROVER_LISTS with approver_sequence = max+1
303 and insert the record in OE_APPROVER_TRANSACTIONS.
304 Returns Y if it finds the next approver and returns N if there is no
305 approver left. In the case there is no more approvers, it will update
306 the status to APPROVED
307 */
308 Procedure Get_Next_Approver
309 (itemtype in varchar2,
310 itemkey in varchar2,
311 actid in number,
312 funcmode in varchar2,
313 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
314 IS
315
316 l_transaction_id NUMBER;
317 l_role VARCHAR2(240);
318 l_sales_document_type_code VARCHAR2(30);
319
320 l_return_status VARCHAR2(30);
321 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
322
323 l_sold_to_org_id NUMBER;
324 l_salesrep_id NUMBER;
325 l_salesrep VARCHAR2(240);
326 l_sold_to VARCHAR2(240);
327 --l_customer_number NUMBER;
328 l_customer_number varchar2(30); --bug4575846
329 l_expiration_date DATE;
330
331 l_aname wf_engine.nametabtyp;
332 l_avaluetext wf_engine.texttabtyp;
333
334 BEGIN
335
336 --
337 -- RUN mode - normal process execution
338 --
339 if (funcmode = 'RUN') then
340
341 l_transaction_id := to_number(itemkey);
342
343 OE_STANDARD_WF.Set_Msg_Context(actid);
344 OE_MSG_PUB.set_msg_context(
345 p_entity_code => 'HEADER'
346 ,p_entity_id => to_number(itemkey)
347 ,p_header_id => to_number(itemkey));
348
349 IF l_debug_level > 0 THEN
350 oe_debug_pub.add( 'Get_Next_Approver ', 1 ) ;
351 END IF;
352
353 -- We need to set the status of the last approver to APPROVED here
354 -- in the OE_APPROVER_TRANSACTIONS
355 UPDATE OE_APPROVER_TRANSACTIONS
356 SET APPROVAL_STATUS = 'APPROVED'
357 WHERE TRANSACTION_ID = l_transaction_id
358 --- ?? phase code = not really needed
359 AND APPROVER_SEQUENCE = (select max(APPROVER_SEQUENCE)
360 from OE_APPROVER_TRANSACTIONS
361 WHERE TRANSACTION_ID = l_transaction_id);
362
363
364 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
365 l_sales_document_type_code := wf_engine.GetItemAttrText(
366 OE_GLOBALS.G_WFI_NGO,
367 l_transaction_id,
368 'SALES_DOCUMENT_TYPE_CODE');
369 END IF;
370
371 -- CALL THE Get_Next_Approver_internal
372 l_role := Get_Next_Approver_internal(l_transaction_id,
373 itemtype,
374 l_sales_document_type_code);
375 IF l_debug_level > 0 THEN
376 oe_debug_pub.add('Role-> ' || l_role, 1) ;
377 END IF;
378
379
380
381 if l_role is NULL then
382
383 /*
384 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
385 (p_item_type => itemtype,
386 p_header_id => l_transaction_id,
387 p_flow_status_code => 'APPROVED',
391 IF l_debug_level > 0 THEN
388 p_sales_document_type_code => l_sales_document_type_code,
389 x_return_status => l_return_status );
390
392 oe_debug_pub.add('Get_next_approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
393 END IF;
394
395 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
396 OE_STANDARD_WF.Save_Messages;
397 OE_STANDARD_WF.Clear_Msg_Context;
398 app_exception.raise_exception;
399 END IF;
400 */
401
402 resultout := 'COMPLETE:N';
403 OE_STANDARD_WF.Save_Messages;
404 OE_STANDARD_WF.Clear_Msg_Context;
405 return;
406
407 else
408 wf_engine.SetItemAttrText(itemtype,
409 itemkey,
410 'NOTIFICATION_APPROVER',
411 l_role);
412
413
414
415
416 -----------------------------------
417 -- Set Header Attributes Values --
418 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
419 ----???? Join to the value table
420 IF l_sales_document_type_code = 'O' THEN
421 select sold_to_org_id, expiration_date, salesrep_id
422 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
423 from oe_order_headers_all
424 where header_id = l_transaction_id;
425 ELSE
426
427 select obha.sold_to_org_id, obhe.end_date_active, obha.salesrep_id
428 into l_sold_to_org_id, l_expiration_date, l_salesrep_id
429 from oe_blanket_headers_all obha,
430 oe_blanket_headers_ext obhe
431 where obha.header_id = l_transaction_id
432 and obha.order_number = obhe.order_number;
433
434 END IF;
435
436 l_salesrep := OE_Id_To_Value.Salesrep(p_salesrep_id=>l_salesrep_id);
437 OE_Id_To_Value.Sold_To_Org(p_sold_to_org_id => l_sold_to_org_id,
438 x_org => l_sold_to,
439 x_customer_number => l_customer_number);
440
441
442 l_aname(1) := 'SALESPERSON';
443 l_avaluetext(1) := l_salesrep;
444 l_aname(2) := 'SOLD_TO';
445 l_avaluetext(2) := l_sold_to;
446 l_aname(3) := 'EXPIRATION_DATE';
447 l_avaluetext(3) := l_expiration_date;
448
449 wf_engine.SetItemAttrTextArray( itemtype
450 , itemkey
451 , l_aname
452 , l_avaluetext
453 );
454 END IF;
455 -- End setting Header Attributes
456
457
458
459 resultout := 'COMPLETE:Y';
460 OE_STANDARD_WF.Save_Messages;
461 OE_STANDARD_WF.Clear_Msg_Context;
462 return;
463 end if;
464
465 end if; -- End for 'RUN' mode
466
467 --
468 -- CANCEL mode - activity 'compensation'
469 --
470 -- This is an event point is called with the effect of the activity must
471 -- be undone, for example when a process is reset to an earlier point
472 -- due to a loop back.
473 --
474 if (funcmode = 'CANCEL') then
475
476 -- your cancel code goes here
477 null;
478
479 -- no result needed
480 resultout := 'COMPLETE';
481 return;
482 end if;
483
484
485 --
486 -- Other execution modes may be created in the future. Your
487 -- activity will indicate that it does not implement a mode
488 -- by returning null
489 --
490 -- resultout := '';
491 -- return;
492
493 exception
494 when others then
495 -- The line below records this function call in the error system
496 -- in the case of an exception.
497 wf_core.context('OE_APPROVALS_WF', 'Get_Next_Approver',
498 itemtype, itemkey, to_char(actid), funcmode);
499 -- start data fix project
500 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
501 p_itemtype => itemtype,
502 p_itemkey => itemkey);
503 OE_STANDARD_WF.Save_Messages;
504 OE_STANDARD_WF.Clear_Msg_Context;
505 -- end data fix project
506 raise;
507
508
509 END Get_Next_Approver;
510
511
512
513 /**********************************
514 * Get_Next_Approver_InternaL *
515 **********************************/
516 /*
517 Gets called from Initiate_approval and Get_next_approval
518 */
519 function Get_Next_Approver_internal (
520 p_transaction_id in NUMBER,
521 p_itemtype in VARCHAR2,
522 p_sales_document_type_code in VARCHAR2,
523 p_query_mode in VARCHAR2 default 'N'
524 )
525 RETURN VARCHAR2
526 IS
527
528 l_role varchar2(320);
529 l_approver_sequence number;
530 l_curr_approver_sequence number;
531 l_list_id NUMBER;
535
532 l_user_id NUMBER;
533 l_transaction_type_id NUMBER;
534 l_transaction_phase_code VARCHAR2(30);
536 l_check_default_list VARCHAR2(1) := 'N';
537
538 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
539
540
541 -- Get the next approver_sequence and its role
542 -- cursor c_get_next_approver (m_cur_seq number, m_list_id number) is
543 -- select ROLE, APPROVER_SEQUENCE
544 -- from OE_APPROVER_LIST_MEMBERS
545 -- where list_id = m_list_id
546 -- and APPROVER_SEQUENCE > m_cur_seq
547 -- and ACTIVE_FLAG = 'Y'
548 -- order by APPROVER_SEQUENCE;
549
550 cursor c_get_next_approver is
551 select ROLE, APPROVER_SEQUENCE
552 from OE_APPROVER_LIST_MEMBERS
553 where list_id = l_list_id
554 and APPROVER_SEQUENCE > l_curr_approver_sequence
555 and ACTIVE_FLAG = 'Y'
556 order by APPROVER_SEQUENCE;
557
558
559 BEGIN
560 IF l_debug_level > 0 THEN
561 oe_debug_pub.add('Get_Next_Approver_internal', 1 ) ;
562 END IF;
563
564 -- Get the User ID
565 l_user_id := OE_APPROVALS_WF.get_user_id;
566
567 -- First get the transaction_type_id and the transaction_phase
568 -- We need to hit different table to find that out
569
570 IF l_debug_level > 0 THEN
571 oe_debug_pub.add('SaleDocumentTypeCode-> ' || p_sales_document_type_code, 1) ;
572 END IF;
573
574 -- If p_sales_document_type_code is not B, it is either a quote or order or line
575 IF nvl(p_sales_document_type_code, 'XXXX') = 'B' THEN
576 select ORDER_TYPE_ID, nvl(TRANSACTION_PHASE_CODE, 'F')
577 into l_transaction_type_id, l_transaction_phase_code
578 from oe_blanket_headers_all
579 where header_id = p_transaction_id;
580 IF l_debug_level > 0 THEN
581 oe_debug_pub.add('Transaction_type_id->' || l_transaction_type_id, 1) ;
582 oe_debug_pub.add('TransactionPhase->' || l_transaction_phase_code, 1);
583 END IF;
584
585 ELSE
586 select ORDER_TYPE_ID, nvl(TRANSACTION_PHASE_CODE, 'F')
587 into l_transaction_type_id, l_transaction_phase_code
588 from oe_order_headers_all
589 where header_id = p_transaction_id;
590 IF l_debug_level > 0 THEN
591 oe_debug_pub.add('Transaction_type_id->' || l_transaction_type_id, 1) ;
592 oe_debug_pub.add('TransactionPhase->' || l_transaction_phase_code, 1);
593 END IF;
594
595 END IF;
596
597 -- Get the approver list_id
598 BEGIN
599 select list_id
600 into l_list_id
601 from OE_APPROVER_LISTS
602 where TRANSACTION_TYPE_ID = l_transaction_type_id
603 and TRANSACTION_PHASE_CODE is not NULL
604 and TRANSACTION_PHASE_CODE = l_transaction_phase_code
605 and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE )
606 AND NVL(END_DATE_ACTIVE, SYSDATE );
607 IF l_debug_level > 0 THEN
608 oe_debug_pub.add('ListID-> ' || l_list_id, 1) ;
609 END IF;
610
611 EXCEPTION
612 when NO_DATA_FOUND then
613 IF l_debug_level > 0 THEN
614 oe_debug_pub.add('No ListID Found ', 1) ;
615 END IF;
616 l_check_default_list := 'Y';
617 end;
618
619
620 if l_check_default_list = 'Y' then
621
622 BEGIN
623 select list_id
624 into l_list_id
625 from OE_APPROVER_LISTS
626 where TRANSACTION_TYPE_ID = l_transaction_type_id
627 and TRANSACTION_PHASE_CODE is NULL
628 and SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE )
629 AND NVL(END_DATE_ACTIVE, SYSDATE );
630 IF l_debug_level > 0 THEN
631 oe_debug_pub.add('Default ListID-> ' || l_list_id, 1) ;
632 END IF;
633
634 EXCEPTION
635 when NO_DATA_FOUND then
636 IF l_debug_level > 0 THEN
637 oe_debug_pub.add('No Default ListID Found ', 1) ;
638 END IF;
639 l_role := NULL;
640 return l_role;
641 end;
642
643 end if;
644
645
646 -------------------------------------------
647 -- Get the Max Current APPROVER_SEQUENCE --
648 -------------------------------------------
649 BEGIN
650 select max(APPROVER_SEQUENCE)
651 into l_curr_approver_sequence
652 from OE_APPROVER_TRANSACTIONS
653 where TRANSACTION_ID = p_transaction_id
654 and TRANSACTION_TYPE_ID = l_transaction_type_id
655 and TRANSACTION_PHASE_CODE = l_transaction_phase_code;
656
657 IF l_debug_level > 0 THEN
658 oe_debug_pub.add('Max Curr APPROVER_SEQUENCE-> ' || l_curr_approver_sequence, 1);
659 END IF;
660 IF l_curr_approver_sequence is null Then
661 l_curr_approver_sequence := 0;
662 END IF;
663
664 EXCEPTION
665 when NO_DATA_FOUND then
666 IF l_debug_level > 0 THEN
667 oe_debug_pub.add('Max Curr APPROVER_SEQUENCE-> ' || l_curr_approver_sequence, 1);
668 END IF;
669
670 l_curr_approver_sequence := 0;
671 END;
672
676 open c_get_next_approver;
673 -- open c_get_next_approver (m_cur_seq => l_curr_approver_sequence,
674 -- m_list_id => l_list_id);
675
677
678 FETCH c_get_next_approver
679 INTO l_role, l_approver_sequence;
680
681 if c_get_next_approver%notfound then
682 IF l_debug_level > 0 THEN
683 oe_debug_pub.add('No Role ', 1) ;
684 END IF;
685
686 l_role := NULL;
687 CLOSE c_get_next_approver;
688 return l_role;
689
690 end if;
691
692 CLOSE c_get_next_approver;
693 oe_debug_pub.add('Role-> ' || l_role, 1) ;
694
695
696 IF p_query_mode = 'N' THEN
697 IF l_debug_level > 0 THEN
698 oe_debug_pub.add('Inserting into OE_APPROVER_TRANSACTIONS ', 1) ;
699 END IF;
700 -- insert this next approver in the OE_APPROVER_TRANSACTIONS
701 INSERT INTO OE_APPROVER_TRANSACTIONS
702 (
703 TRANSACTION_ID
704 ,TRANSACTION_TYPE_ID --?? Do we need this. evalute
705 ,TRANSACTION_PHASE_CODE
706 ,ROLE
707 ,APPROVER_SEQUENCE
708 ,APPROVAL_STATUS
709 ,CREATION_DATE
710 ,CREATED_BY
711 ,LAST_UPDATE_DATE
712 ,LAST_UPDATED_BY
713 ,LAST_UPDATE_LOGIN
714
715 )
716 VALUES
717 (
718 p_transaction_id
719 ,l_transaction_type_id
720 ,l_transaction_phase_code
721 ,l_role
722 ,l_approver_sequence
723 ,NULL --APPROVAL_STATUS
724 ,SYSDATE
725 ,l_user_id
726 ,SYSDATE
727 ,l_user_id
728 ,l_user_id
729 );
730 END IF;
731
732 RETURN l_role;
733
734 END Get_Next_Approver_internal;
735
736 /**************************
737 * Approve_Approval *
738 **************************/
739 /*
740 This procedure will update the OM Approval transaction table
741 OE_APPROVER_TRANSACTIONS with proper results. Perform a status
742 update to INTERNAL_APPROVED.
743 */
744 Procedure Approve_Approval
745 (itemtype in varchar2,
746 itemkey in varchar2,
747 actid in number,
748 funcmode in varchar2,
749 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
750 IS
751
752 l_transaction_id NUMBER;
753 l_sales_document_type_code VARCHAR2(30);
754 l_return_status VARCHAR2(30);
755 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
756
757 BEGIN
758
759 --
760 -- RUN mode - normal process execution
761 --
762 if (funcmode = 'RUN') then
763
764 l_transaction_id := to_number(itemkey);
765
766 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
767 l_sales_document_type_code := wf_engine.GetItemAttrText(
768 OE_GLOBALS.G_WFI_NGO,
769 l_transaction_id,
770 'SALES_DOCUMENT_TYPE_CODE');
771 END IF;
772
773 OE_STANDARD_WF.Set_Msg_Context(actid);
774 OE_MSG_PUB.set_msg_context(
775 p_entity_code => 'HEADER'
776 ,p_entity_id => to_number(itemkey)
777 ,p_header_id => to_number(itemkey));
778
779 IF l_debug_level > 0 THEN
780 oe_debug_pub.add('Approve_Approval.. ', 1) ;
781 END IF;
782
783 OE_ORDER_WF_UTIL.Update_Flow_Status_Code
784 (p_item_type => itemtype,
785 p_header_id => l_transaction_id,
786 p_flow_status_code => 'INTERNAL_APPROVED',
787 p_sales_document_type_code => l_sales_document_type_code,
788 x_return_status => l_return_status );
789
790 IF l_debug_level > 0 THEN
791 oe_debug_pub.add('Approve_Approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
792 END IF;
793
794 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
795 -- start data fix project
796 -- OE_STANDARD_WF.Save_Messages;
797 -- OE_STANDARD_WF.Clear_Msg_Context;
798 -- end data fix project
799 --app_exception.raise_exception;
800 --bug7386039
801
802 resultout := 'DEFERRED:'||to_char(sysdate+(TO_NUMBER(g_defer_min)/1440),wf_engine.date_format);
803 return;
804
805 END IF;
806
807 resultout := 'COMPLETE';
808 OE_STANDARD_WF.Save_Messages;
809 OE_STANDARD_WF.Clear_Msg_Context;
810 return;
811
812 end if;
813
814
815 --
816 -- CANCEL mode - activity 'compensation'
817 --
818 -- This is an event point is called with the effect of the activity must
819 -- be undone, for example when a process is reset to an earlier point
820 -- due to a loop back.
821 --
822 if (funcmode = 'CANCEL') then
823
824 -- your cancel code goes here
825 null;
826
830 end if;
827 -- no result needed
828 resultout := 'COMPLETE';
829 return;
831
832
833 --
834 -- Other execution modes may be created in the future. Your
835 -- activity will indicate that it does not implement a mode
836 -- by returning null
837 --
838 -- resultout := '';
839 -- return;
840
841 exception
842 when others then
843 -- The line below records this function call in the error system
844 -- in the case of an exception.
845 IF l_debug_level > 0 THEN
846 oe_debug_pub.add('Exception in Reject_Approval.. ' ,1) ;
847 END IF;
848 wf_core.context('OE_APPROVALS_WF', 'Approve_Approval',
849 itemtype, itemkey, to_char(actid), funcmode);
850 -- start data fix project
851 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
852 p_itemtype => itemtype,
853 p_itemkey => itemkey);
854 OE_STANDARD_WF.Save_Messages;
855 OE_STANDARD_WF.Clear_Msg_Context;
856 -- end data fix project
857 raise;
858
859
860 END Approve_Approval;
861
862
863 /*************************
864 * Approval_Timeout *
865 *************************/
866 /*
867 This procedure will determine if to Cotinue or Reject the transaction
868 in case the approver has timed out and not responded. It will read the
869 system parameter value and determne if to continue or Reject
870
871 */
872 Procedure Approval_Timeout
873 (itemtype in varchar2,
874 itemkey in varchar2,
875 actid in number,
876 funcmode in varchar2,
877 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
878 IS
879 l_next_role varchar2(320);
880 l_current_approver varchar2(320);
881 l_sales_document_type_code VARCHAR2(30);
882 l_transaction_id NUMBER;
883 l_return_status VARCHAR2(30);
884 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
885
886 BEGIN
887
888 --
889 -- RUN mode - normal process execution
890 --
891 if (funcmode = 'RUN') then
892
893 l_transaction_id := to_number(itemkey);
894
895 OE_STANDARD_WF.Set_Msg_Context(actid);
896 OE_MSG_PUB.set_msg_context(
897 p_entity_code => 'HEADER'
898 ,p_entity_id => to_number(itemkey)
899 ,p_header_id => to_number(itemkey));
900
901 IF l_debug_level > 0 THEN
902 oe_debug_pub.add('Approval_Timeout.. ', 1) ;
903 END IF;
904
905
906 IF nvl(OE_SYS_PARAMETERS.value('NO_RESPONSE_FROM_APPROVER'), 'CONTINUE') ='CONTINUE' THEN
907
908 -- If the current approver is the last in the list, then still reject it.
909 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
910 l_sales_document_type_code := wf_engine.GetItemAttrText(
911 OE_GLOBALS.G_WFI_NGO,
912 l_transaction_id,
913 'SALES_DOCUMENT_TYPE_CODE');
914 END IF;
915
916 -- check_if_last_approver(l_transaction_id,l_sales_document_type_code);
917 l_next_role := Get_Next_Approver_internal(
918 l_transaction_id,
919 itemtype,
920 l_sales_document_type_code,
921 'Y');
922
923 if l_next_role is NULL THEN
924 resultout := 'COMPLETE:REJECTED';
925 OE_STANDARD_WF.Clear_Msg_Context;
926 return;
927 else
928 resultout := 'COMPLETE:CONTINUE';
929 OE_STANDARD_WF.Clear_Msg_Context;
930 return;
931 end if;
932
933 ELSE
934 resultout := 'COMPLETE:REJECTED';
935 OE_STANDARD_WF.Clear_Msg_Context;
936 return;
937
938 END IF;
939
940
941 resultout := 'COMPLETE';
942 OE_STANDARD_WF.Clear_Msg_Context;
943 return;
944 end if; -- End for 'RUN' mode
945
946 --
947 -- CANCEL mode - activity 'compensation'
948 --
949 -- This is an event point is called with the effect of the activity must
950 -- be undone, for example when a process is reset to an earlier point
951 -- due to a loop back.
952 --
953 if (funcmode = 'CANCEL') then
954
955 -- your cancel code goes here
956 null;
957
958 -- no result needed
959 resultout := 'COMPLETE';
960 return;
961 end if;
962
963
964 --
965 -- Other execution modes may be created in the future. Your
966 -- activity will indicate that it does not implement a mode
967 -- by returning null
968 --
969 -- resultout := '';
970 -- return;
971
972
973 exception
974 when others then
975 -- The line below records this function call in the error system
976 -- in the case of an exception.
977 IF l_debug_level > 0 THEN
981 itemtype, itemkey, to_char(actid), funcmode);
978 oe_debug_pub.add('Exception in Approval_Timeout.. ' ,1) ;
979 END IF;
980 wf_core.context('OE_APPROVALS_WF', 'Approval_Timeout',
982 -- start data fix project
983 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
984 p_itemtype => itemtype,
985 p_itemkey => itemkey);
986 OE_STANDARD_WF.Save_Messages;
987 OE_STANDARD_WF.Clear_Msg_Context;
988 -- end data fix project
989 raise;
990
991
992 END Approval_Timeout;
993
994
995
996
997 /*************************
998 * Reject_Approval *
999 *************************/
1000 /*
1001 This procedure will update the OM Approval transaction table
1002 OE_APPROVER_TRANSACTIONS with proper results. Perform a status
1003 update to DRAFT_INTERNAL_REJECTED. And update the column
1004 DRAFT_SUBMITTED_FLAG to 'N' to the base table.
1005 */
1006 Procedure Reject_Approval
1007 (itemtype in varchar2,
1008 itemkey in varchar2,
1009 actid in number,
1010 funcmode in varchar2,
1011 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
1012 IS
1013
1014 l_transaction_id NUMBER;
1015 l_return_status VARCHAR2(30);
1016 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1017
1018 BEGIN
1019
1020 --
1021 -- RUN mode - normal process execution
1022 --
1023 if (funcmode = 'RUN') then
1024
1025 l_transaction_id := to_number(itemkey);
1026
1027 OE_STANDARD_WF.Set_Msg_Context(actid);
1028 OE_MSG_PUB.set_msg_context(
1029 p_entity_code => 'HEADER'
1030 ,p_entity_id => to_number(itemkey)
1031 ,p_header_id => to_number(itemkey));
1032
1033 IF l_debug_level > 0 THEN
1034 oe_debug_pub.add('Reject_Approval.. ', 1) ;
1035 END IF;
1036
1037 BEGIN
1038 UPDATE OE_APPROVER_TRANSACTIONS
1039 SET APPROVAL_STATUS = 'REJECTED'
1040 WHERE TRANSACTION_ID = to_number(itemkey)
1041 AND APPROVER_SEQUENCE = ( select max(APPROVER_SEQUENCE)
1042 from OE_APPROVER_TRANSACTIONS
1043 where TRANSACTION_ID = to_number(itemkey));
1044 EXCEPTION
1045 WHEN NO_DATA_FOUND THEN
1046 -- In case someone adds an additional approval notification after its approved
1047 -- and then that notifcations get rejected and transitions to Reject_apprroval
1048 null;
1049 END;
1050
1051 IF itemtype = OE_GLOBALS.G_WFI_NGO THEN
1052 OE_ORDER_WF_UTIL.Update_Quote_Blanket(
1053 p_item_type => OE_GLOBALS.G_WFI_NGO,
1054 p_item_key => to_number(itemkey),
1055 p_flow_status_code => 'DRAFT_INTERNAL_REJECTED',
1056 p_draft_submitted_flag => 'N',
1057 x_return_status => l_return_status);
1058 ELSE
1059 OE_ORDER_WF_UTIL.Update_flow_status_code(
1060 p_item_type => itemtype,
1061 p_header_id => to_number(itemkey),
1062 p_flow_status_code => 'INTERNAL_REJECTED',
1063 x_return_status => l_return_status);
1064 END IF;
1065
1066 IF l_debug_level > 0 THEN
1067 oe_debug_pub.add('Reject_Approval STATUS FROM Update_Flow_Status_Code: '|| l_return_status );
1068 END IF;
1069
1070 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1071 -- start data fix project
1072 -- OE_STANDARD_WF.Save_Messages;
1073 -- OE_STANDARD_WF.Clear_Msg_Context;
1074 -- end data fix project
1075 --app_exception.raise_exception;
1076 --bug7386039
1077 resultout := 'DEFERRED:'||to_char(sysdate+(TO_NUMBER(g_defer_min)/1440),wf_engine.date_format);
1078 return;
1079
1080 END IF;
1081
1082
1083
1084 resultout := 'COMPLETE';
1085 OE_STANDARD_WF.Clear_Msg_Context;
1086 return;
1087 end if; -- End for 'RUN' mode
1088
1089 --
1090 -- CANCEL mode - activity 'compensation'
1091 --
1092 -- This is an event point is called with the effect of the activity must
1093 -- be undone, for example when a process is reset to an earlier point
1094 -- due to a loop back.
1095 --
1096 if (funcmode = 'CANCEL') then
1097
1098 -- your cancel code goes here
1099 null;
1100
1101 -- no result needed
1102 resultout := 'COMPLETE';
1103 return;
1104 end if;
1105
1106
1107 --
1108 -- Other execution modes may be created in the future. Your
1109 -- activity will indicate that it does not implement a mode
1110 -- by returning null
1111 --
1112 -- resultout := '';
1113 -- return;
1114
1115 exception
1116 when others then
1117 -- The line below records this function call in the error system
1118 -- in the case of an exception.
1119 IF l_debug_level > 0 THEN
1120 oe_debug_pub.add('Exception in Reject_Approval.. ' ,1) ;
1121 END IF;
1122 wf_core.context('OE_APPROVALS_WF', 'Reject_Approval',
1126 p_itemtype => itemtype,
1123 itemtype, itemkey, to_char(actid), funcmode);
1124 -- start data fix project
1125 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
1127 p_itemkey => itemkey);
1128 OE_STANDARD_WF.Save_Messages;
1129 OE_STANDARD_WF.Clear_Msg_Context;
1130 -- end data fix project
1131 raise;
1132
1133
1134 END Reject_Approval;
1135
1136
1137
1138 /*************************
1139 * Get_Current_Approver *
1140 *************************/
1141 /*
1142 This API will hit the OE_APPROVER_TRANSACTIONS table to find the
1143 max(approver_sequence) approver for the given transaction_id and
1144 retrieve the proper name of the approver. NID will be passed in.
1145 */
1146 Procedure Get_Current_Approver
1147 (document_id in varchar2,
1148 display_type in varchar2,
1149 document in out NOCOPY /* file.sql.39 change */ varchar2,
1150 document_type in out NOCOPY /* file.sql.39 change */ varchar2)
1151 IS
1152
1153 l_role varchar2(320);
1154 l_approver_sequence NUMBER;
1155 l_transaction_id NUMBER;
1156
1157 l_list_id NUMBER;
1158 l_user_id NUMBER;
1159 l_transaction_type_id NUMBER;
1160 l_transaction_phase_code VARCHAR2(30);
1161
1162 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1163
1164 Begin
1165 IF l_debug_level > 0 THEN
1166 oe_debug_pub.add('Get_Current_Approver.. ' ,1) ;
1167 END IF;
1168
1169
1170 -- select to_number(ITEM_KEY)
1171 -- into l_transaction_id
1172 -- from wf_item_activity_statuses_v
1173 -- where NOTIFICATION_ID = to_number(document_id);
1174
1175 --Replaced with
1176 select to_number(ITEM_KEY)
1177 into l_transaction_id
1178 from WF_ITEM_ACTIVITY_STATUSES
1179 where NOTIFICATION_ID = to_number(document_id);
1180
1181 l_role := Get_Current_Approver_internal (l_transaction_id);
1182
1183 IF l_debug_level > 0 THEN
1184 oe_debug_pub.add('ItemKey/Current Role' || l_transaction_id || '/'
1185 || l_role,1);
1186 END IF;
1187
1188
1189 document := l_role;
1190
1191 end Get_Current_Approver;
1192
1193
1194 /**********************************
1195 * Get_Current_Approver_internal *
1196 **********************************/
1197 /*
1198 */
1199 function Get_Current_Approver_internal(p_transaction_id in NUMBER)
1200 return varchar2
1201 IS
1202 l_role varchar2(320);
1203
1204 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1205
1206 BEGIN
1207
1208 IF l_debug_level > 0 THEN
1209 oe_debug_pub.add('In function Get_Current_Approver_internal....', 1);
1210 END IF;
1211
1212 BEGIN
1213 select role
1214 into l_role
1215 from OE_APPROVER_TRANSACTIONS
1216 where TRANSACTION_ID = p_transaction_id
1217 and APPROVER_SEQUENCE = ( select max(APPROVER_SEQUENCE)
1218 from OE_APPROVER_TRANSACTIONS
1219 where TRANSACTION_ID = p_transaction_id);
1220
1221 EXCEPTION
1222 WHEN NO_DATA_FOUND THEN
1223 IF l_debug_level > 0 THEN
1224 oe_debug_pub.add('No OE_APPROVER_TRANSACTIONS for TransactionID:'
1225 || p_transaction_id ,1);
1226 l_role := null;-- 6615403
1227 END IF;
1228
1229
1230 END;
1231 IF l_debug_level > 0 THEN
1232 oe_debug_pub.add('ItemKey/Current Role' || p_transaction_id || '/'
1233 || l_role,1);
1234 END IF;
1235 RETURN l_role;-- 6615403
1236
1237 END Get_Current_Approver_internal;
1238
1239
1240
1241 /****************************
1242 * Get_Sales_Document_Type *
1243 ****************************/
1244 Procedure Get_Sales_Document_Type (document_id in varchar2,
1245 display_type in varchar2,
1246 document in out NOCOPY /* file.sql.39 change */ varchar2,
1247 document_type in out NOCOPY /* file.sql.39 change */ varchar2)
1248 IS
1249 l_sales_document_type_code VARCHAR2(30);
1250 l_sales_document_type_desc VARCHAR2(80);
1251 l_transaction_id NUMBER;
1252
1253 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1254
1255 BEGIN
1256 IF l_debug_level > 0 THEN
1257 oe_debug_pub.add('Get_Sales_Document_Type...',1);
1258 END IF;
1259
1260 -- select to_number(ITEM_KEY)
1261 -- into l_transaction_id
1262 -- from wf_item_activity_statuses_v
1263 -- where NOTIFICATION_ID = to_number(document_id);
1264
1265 --Replaced with
1266 select to_number(ITEM_KEY)
1267 into l_transaction_id
1268 from WF_ITEM_ACTIVITY_STATUSES
1269 where NOTIFICATION_ID = to_number(document_id);
1270
1271
1272 l_sales_document_type_code := wf_engine.GetItemAttrText(
1273 OE_GLOBALS.G_WFI_NGO,
1274 to_char(l_transaction_id),
1275 'SALES_DOCUMENT_TYPE_CODE');
1276
1277 select meaning
1278 into l_sales_document_type_desc
1279 from oe_lookups
1280 where LOOKUP_TYPE = 'SALES_DOCUMENT_TYPE'
1281 and LOOKUP_CODE = l_sales_document_type_code;
1282
1283 IF l_debug_level > 0 THEN
1284 oe_debug_pub.add('Sales_Document_Type_Code:' || l_sales_document_type_code,1);
1285 END IF;
1286
1287 document := l_sales_document_type_desc;
1288 EXCEPTION
1289 when no_data_found then
1290 raise; -- fill in the details
1291 when others then
1292 raise; -- fill in the details
1293 END Get_Sales_Document_Type;
1294
1295
1296
1297
1298
1299 END OE_APPROVALS_WF;
[Home] [Help]