1 package body OE_CREDIT_WF as
2 /* $Header: OEXWCRCB.pls 120.4 2010/06/16 07:25:42 amimukhe ship $ */
3
4
5 /*--------------------------------------------------------------------------
6 Called by the workflow activity CREDIT_CHECK, this procedure:
7 1) Doesn't do a credit check if a previous hold was manually released and
8 the order is not being updated.
9 2) Decides on the credit check rule to use.
10 3) Calls the mainline procedure in the credit check utility.
11 ----------------------------------------------------------------------------*/
12
13 procedure OE_CHECK_AVAILABLE_CREDIT(
14 itemtype in varchar2,
15 itemkey in varchar2,
16 actid in number,
17 funcmode in varchar2,
18 resultout in out nocopy varchar2)
19 is
20 l_return_status VARCHAR2(30); -- checks the return status of the called procedures
21 l_msg_count NUMBER := 0; -- checks the no. of msgs. from the called procedures
22 l_msg_data VARCHAR2(240); -- stores the msg. data from the called procedures
23 l_result_out VARCHAR2(240); -- PASS/FAIL: result from credit check API
24 l_header_id NUMBER; -- Header Id for the order being processed
25 l_calling_action VARCHAR2(30); -- is the credit check rule Booking/Shipping
26 --
27 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
28 --
29 BEGIN
30 -- start data fix project
31 OE_STANDARD_WF.Set_Msg_Context(actid);
32 -- end data fix project
33 --
34 -- RUN mode - normal process execution
35 --
36
37 if (funcmode = 'RUN') then
38
39 IF l_debug_level > 0 THEN
40 oe_debug_pub.add( 'OEXCRWFB: CHECK_AVAILABLE_CREDIT: ITEM TYPE='||ITEMTYPE ) ;
41 END IF;
42
43 l_header_id := GetHeaderID(itemtype, itemkey);
44
45 /*-------- Check for manual release of holds ------------------------------*/
46
47 -- No credit check if a previous credit hold was manually released and if
48 -- this credit check is NOT due to the order being updated
49
50 IF itemtype in ('OEOH', 'OEOL') THEN
51 IF (CheckManualRelease(l_header_id) = 'Y') THEN
52 resultout := 'COMPLETE:PASS';
53 return;
54 END IF;
55 END IF;
56
57 IF l_debug_level > 0 THEN
58 oe_debug_pub.add( 'OEXCRWFB: AFTER CHECK FOR MANUAL RELEASE' ) ;
59 END IF;
60
61 /*-------- Deciding which credit check rule to use -------------------------*/
62
63 /* commented the following code to fix the bug 9663460
64
65 l_calling_action := WhichCreditRule(itemtype,
66 itemkey,
67 actid);
68 */
69
70 l_calling_action := OE_Verify_Payment_PUB.Which_Rule(p_header_id => l_header_id);
71
72
73 /*------- Calling the credit check API -----------------------------------*/
74 OE_Credit_PUB.Check_Available_Credit
75 ( p_header_id => l_header_id
76 , p_calling_action => l_calling_action
77 , p_msg_count => l_msg_count
78 , p_msg_data => l_msg_data
79 , p_result_out => l_result_out
80 , p_return_status => l_return_status
81 );
82
83 -- If the check available credit procedure returns with success we should
84 -- set the result out for workflow.
85
86 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
87
88 IF l_result_out = 'FAIL' then
89 resultout := 'COMPLETE:FAIL';
90 ELSE
91 resultout := 'COMPLETE:PASS';
92 END IF;
93
94 IF l_debug_level > 0 THEN
95 oe_debug_pub.add( 'OEXCRWFB CHECK AVAILABLE CREDIT , RESULTOUT:' || RESULTOUT ) ;
96 END IF;
97
98 ELSE RAISE PROGRAM_ERROR;
99
100 END IF;
101
102 return;
103
104 end if;
105
106 --
107 -- CANCEL mode - activity 'compensation'
108 --
109 -- This is an event point is called with the effect of the activity must
110 -- be undone, for example when a process is reset to an earlier point
111 -- due to a loop back.
112 --
113 if (funcmode = 'CANCEL') then
114
115 -- your cancel code goes here
116 null;
117
118 -- no result needed
119 resultout := 'COMPLETE';
120 return;
121 end if;
122
123
124 --
125 -- Other execution modes may be created in the future. Your
126 -- activity will indicate that it does not implement a mode
127 -- by returning null
128 --
129 resultout := '';
130 return;
131
132 exception
133 when others then
134 -- The line below records this function call in the error system
135 -- in the case of an exception.
136 wf_core.context('OE_CREDIT_WF', 'OE_CHECK_AVAILABLE_CREDIT',
137 itemtype, itemkey, to_char(actid), funcmode);
138 -- start data fix project
139 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
140 p_itemtype => itemtype,
141 p_itemkey => itemkey);
142 OE_STANDARD_WF.Save_Messages;
143 OE_STANDARD_WF.Clear_Msg_Context;
144 -- end data fix project
145 raise;
146
147 end OE_CHECK_AVAILABLE_CREDIT;
148
149 /*----------------------------------------------------------------------------------
150 This Procedure is provided for the scenario of a credit hold already existing
151 for a given order. At deployment time the workflow can be set up to
152 not reapply a credit hold if one has already been placed based on the outcome
153 of this procedure.
154 -----------------------------------------------------------------------------------*/
155 procedure OE_CHECK_FOR_HOLDS(
156 itemtype in varchar2,
157 itemkey in varchar2,
158 actid in number,
159 funcmode in varchar2,
160 resultout in out nocopy varchar2)
161 is
162 l_hold_exists VARCHAR2(1); -- Y/N depending on whether the hold exists or not
163 l_hold_count NUMBER; -- Number of credit holds on this order...should be 1 at any time
164 l_released VARCHAR2(1); -- Y/N depending on whether the hold exists or not
165 l_header_id NUMBER;
166 l_return_status VARCHAR2(30); -- checks the return status of the called procedures
167 l_msg_count NUMBER := 0; -- checks the no. of msgs. from the called procedures
168 l_msg_data VARCHAR2(240); -- stores the msg. data from the called procedures
169 --
170 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
171 --
172 begin
173 -- start data fix project
174 OE_STANDARD_WF.Set_Msg_Context(actid);
175 -- end data fix project
176 --
177 -- RUN mode - normal process execution
178 --
179 if (funcmode = 'RUN') then
180
181 IF l_debug_level > 0 THEN
182 oe_debug_pub.add( 'IN CHECK FOR HOLDS , OEXCRWFB WITH ITEMTYPE:'||ITEMTYPE ) ;
183 END IF;
184
185 l_header_id := GetHeaderID(itemtype, itemkey);
186
187 BEGIN
188
189 -- Getting the number of credit failure holds on this order
190 SELECT count(*)
191 INTO l_hold_count
192 FROM OE_ORDER_HOLDS H, OE_HOLD_SOURCES S
193 WHERE H.HEADER_ID = l_header_id
194 -- AND H.LINE_ID IS NULL
195 AND H.HOLD_RELEASE_ID IS NULL
196 AND H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
197 AND S.HOLD_ID = 1
198 AND S.HOLD_ENTITY_CODE = 'O'
199 AND S.HOLD_ENTITY_ID = l_header_id
200 AND S.RELEASED_FLAG = 'N';
201
202 -- if number of rows retrieved > 0, then hold exists
203 if l_hold_count > 0 then
204 l_hold_exists := 'Y';
205 else
206 l_hold_exists := 'N';
207 end if;
208
209 EXCEPTION
210 WHEN NO_DATA_FOUND THEN
211 l_hold_exists := 'N';
212 IF l_debug_level > 0 THEN
213 oe_debug_pub.add( 'NO DATA FOUND IN CHECK FOR HOLDS' ) ;
214 END IF;
215 END;
216
217 -- Setting the result for the workflow
218 IF l_hold_exists = 'Y' then
219 resultout := 'COMPLETE:Y';
220 ELSE
221 resultout := 'COMPLETE:N';
222 END IF;
223
224
225 return;
226
227 end if;
228
229 --
230 -- CANCEL mode - activity 'compensation'
231 --
232 -- This is an event point is called with the effect of the activity must
233 -- be undone, for example when a process is reset to an earlier point
234 -- due to a loop back.
235 --
236 if (funcmode = 'CANCEL') then
237
238 -- your cancel code goes here
239 null;
240
241 -- no result needed
242 resultout := 'COMPLETE';
243 return;
244 end if;
245
246
247 --
248 -- Other execution modes may be created in the future. Your
249 -- activity will indicate that it does not implement a mode
250 -- by returning null
251 --
252 resultout := '';
253 return;
254
255 exception
256 when others then
257 -- The line below records this function call in the error system
258 -- in the case of an exception.
259 wf_core.context('OE_CREDIT_WF', 'OE_CHECK_FOR_HOLDS',
260 itemtype, itemkey, to_char(actid), funcmode);
261 -- start data fix project
262 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
263 p_itemtype => itemtype,
264 p_itemkey => itemkey);
265 OE_STANDARD_WF.Save_Messages;
266 OE_STANDARD_WF.Clear_Msg_Context;
267 -- end data fix project
268 raise;
269
270 end OE_CHECK_FOR_HOLDS;
271
272 /*---------------------------------------------------------------------
273 Based on the results of the OE_CHECK_AVAILABLE_CREDIT procedure the
274 workflow may be set to apply a credit hold.
275 ----------------------------------------------------------------------*/
276 procedure OE_APPLY_CREDIT_HOLD(
277 itemtype in varchar2,
278 itemkey in varchar2,
279 actid in number,
280 funcmode in varchar2,
281 resultout in out nocopy varchar2)
282 is
283 l_order_number NUMBER;
284 l_return_status VARCHAR2(30);
285 l_msg_count NUMBER := 0;
286 l_msg_data VARCHAR2(240);
287 l_header_id NUMBER;
288 l_hold_source_rec OE_Hold_Sources_Pvt.Hold_Source_REC :=
289 OE_Hold_Sources_Pvt.G_MISS_Hold_Source_REC;
290 --
291 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
292 --
293 begin
294 -- start data fix project
295 OE_STANDARD_WF.Set_Msg_Context(actid);
296 -- end data fix project
297 --
298 -- RUN mode - normal process execution
299 --
300
301 -- Apply Credit hold using public hold API.
302
303
304 if (funcmode = 'RUN') then
305
306 l_header_id := GetHeaderID( itemtype, itemkey);
307
308 IF l_debug_level > 0 THEN
309 oe_debug_pub.add( 'APPLYING CREDIT HOLD TO HEADER:' || L_HEADER_ID ) ;
310 END IF;
311
312 /*--------------------------- Applying credit hold ----------------------------------*/
313 l_hold_source_rec.hold_id := 1; -- Credit Failure Hold
314 l_hold_source_rec.hold_entity_code := 'O'; -- Order Hold
315 l_hold_source_rec.hold_entity_id := l_header_id; -- Order Header
316
317
318 OE_Holds_PUB.Apply_Holds
319 ( p_api_version => 1.0
320 , p_header_id => l_header_id
321 , p_hold_source_rec => l_hold_source_rec
322 , x_return_status => l_return_status
323 , x_msg_count => l_msg_count
324 , x_msg_data => l_msg_data
325 );
326
327 /*
328 OE_Holds_PUB.Apply_Holds
329 ( p_header_id => l_header_id
330 , p_hold_id => 1 -- Credit Failure Hold
331 , p_entity_code => 'O' -- Order Hold
332 , p_entity_id => l_header_id -- Order Header id
333 , x_return_status => l_return_status
334 , x_msg_count => l_msg_count
335 , x_msg_data => l_msg_data
336 , p_api_version => 1.0
337 );
338 */
339
340 IF l_debug_level > 0 THEN
341 oe_debug_pub.add( 'APPLIED CREDIT HOLD TO HEADER:' || L_HEADER_ID ) ;
342 END IF;
343
344 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
345 raise PROGRAM_ERROR;
346 end if;
347
348 -- Next set the Workflow Item Attributes to put meaningful information
349 -- in the message when notifying the credit manager.
350 SELECT ORDER_NUMBER
351 INTO l_order_number
352 FROM OE_ORDER_HEADERS
353 WHERE header_id = l_header_id;
354
355 wf_engine.SetItemAttrNumber(itemtype, itemkey, 'ORDER_NUMBER', l_order_number);
356
357 -- Set workflow activity completion result
358 resultout := 'COMPLETE:Null';
359
360 return;
361
362 end if;
363
364
365 --
366 -- CANCEL mode - activity 'compensation'
367 --
368 -- This is an event point is called with the effect of the activity must
369 -- be undone, for example when a process is reset to an earlier point
370 -- due to a loop back.
371 --
372 if (funcmode = 'CANCEL') then
373
374 -- your cancel code goes here
375 null;
376
377 -- no result needed
378 resultout := 'COMPLETE';
379 return;
380 end if;
381
382
383 --
384 -- Other execution modes may be created in the future. Your
385 -- activity will indicate that it does not implement a mode
386 -- by returning null
387 --
388 resultout := '';
389 return;
390
391 exception
392 when others then
393 -- The line below records this function call in the error system
394 -- in the case of an exception.
395 wf_core.context('OE_CREDIT_WF', 'OE_APPLY_CREDIT_HOLD',
396 itemtype, itemkey, to_char(actid), funcmode);
397 -- start data fix project
398 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
399 p_itemtype => itemtype,
400 p_itemkey => itemkey);
401 OE_STANDARD_WF.Save_Messages;
402 OE_STANDARD_WF.Clear_Msg_Context;
403 -- end data fix project
404 raise;
405
406 end OE_APPLY_CREDIT_HOLD;
407
408 /*-----------------------------------------------------
409 This procedure is a workflow wrapper to the Public
410 release hold API in OE_HOLDS_PUB. It is provided
411 here so that when the credit manager responds to
412 his notification that a credit review is required
413 for a customer whose order has gone on credit hold,
414 we can automate the removal of the hold if the
415 customer passes credit review.
416 -------------------------------------------------------*/
417 procedure OE_RELEASE_CREDIT_HOLD(
418 itemtype in varchar2,
419 itemkey in varchar2,
420 actid in number,
421 funcmode in varchar2,
422 resultout in out nocopy varchar2)
423 is
424 --ER#7479609 l_hold_entity_id NUMBER;
425 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE; --ER#7479609
426 l_hold_count NUMBER;
427 l_return_status VARCHAR2(30);
428 l_msg_count NUMBER := 0;
429 l_msg_data VARCHAR2(240);
430 l_release_reason VARCHAR2(30);
431 l_hold_release_rec OE_Hold_Sources_Pvt.Hold_Release_REC :=
432 OE_Hold_Sources_Pvt.G_MISS_Hold_Release_REC;
433 Cursor blocked_process IS
434 SELECT wfas.item_type, wfas.item_key, wpa.activity_name, wfas.activity_status
435 FROM wf_item_activity_statuses wfas, wf_process_activities wpa
436 WHERE wpa.activity_name IN ('OE_CREDIT_HOLD_NTF','OE_HOLD_BLOCK','WAIT_FOR_NTF_RESULT')
437 AND wfas.process_activity = wpa.instance_id
438 AND wfas.activity_status = 'NOTIFIED'
439 AND wfas.item_type = 'OEOH'
440 AND wpa.activity_item_type = 'OEOH'
441 and wfas.item_key = to_char(l_hold_entity_id)
442 UNION
443 SELECT wfas.item_type, wfas.item_key, wpa.activity_name, wfas.activity_status
444 FROM wf_item_activity_statuses wfas, wf_process_activities wpa
445 WHERE wpa.activity_name IN ('OE_CREDIT_HOLD_NTF','OE_HOLD_BLOCK','WAIT_FOR_NTF_RESULT')
446 AND wfas.process_activity = wpa.instance_id
447 AND wfas.activity_status = 'NOTIFIED'
448 AND wpa.activity_item_type = 'OEOL'
449 AND wfas.item_type = 'OEOL' and wfas.item_key
450 in (select line_id
451 from oe_order_lines_all L
452 where L.header_id = l_hold_entity_id);
453 --
454 --OR (item_type = 'OECHGORD' and item_key
455 -- in (select to_char(P.wf_key_id)
456 -- from oe_line_pending_actions P
457 -- where P.header_id = l_hold_entity_id)));
458 --
459 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
460 --
461 begin
462 -- start data fix project
463 OE_STANDARD_WF.Set_Msg_Context(actid);
464 -- end data fix project
465 --
466 -- RUN mode - normal process execution
467 --
468
469 if (funcmode = 'RUN') then
470
471 l_return_status := FND_API.G_RET_STS_SUCCESS;
472
473 IF l_debug_level > 0 THEN
474 oe_debug_pub.add( 'IN RELEASE HOLDS , OEXCRWFB WITH ITEMTYPE:'||ITEMTYPE ) ;
475 END IF;
476
477 l_hold_entity_id := GetHeaderID(itemtype, itemkey);
478
479 IF l_debug_level > 0 THEN
480 oe_debug_pub.add( 'CHECKING HOLDS FOR HEADER ID:' || L_HOLD_ENTITY_ID ) ;
481 END IF;
482
483 /*-------------------- Checking if the hold still exists -------------------*/
484
485 SELECT count(*)
486 INTO l_hold_count
487 FROM OE_ORDER_HOLDS H, OE_HOLD_SOURCES S
488 WHERE H.HEADER_ID = l_hold_entity_id
489 -- AND H.LINE_ID IS NULL
490 AND H.HOLD_RELEASE_ID IS NULL
491 AND H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
492 AND S.HOLD_ID = 1
493 AND S.HOLD_ENTITY_CODE = 'O'
494 AND S.HOLD_ENTITY_ID = l_hold_entity_id
495 AND S.RELEASED_FLAG = 'N';
496
497
498 IF l_hold_count > 0 THEN
499
500 IF l_debug_level > 0 THEN
501 oe_debug_pub.add( 'RELEASING ORDER WITH HEADER ID:' || L_HOLD_ENTITY_ID ) ;
502 END IF;
503
504 /*-------------------- Releasing credit hold -------------------------------*/
505 l_hold_release_rec.release_reason_code := 'PASS_CREDIT';
506 OE_Holds_PUB.Release_Holds
507 ( p_api_version => 1.0
508 , p_hold_id => 1
509 , p_entity_code => 'O'
510 , p_entity_id => l_hold_entity_id
511 , p_header_id => l_hold_entity_id
512 , p_hold_release_rec => l_hold_release_rec
513 , x_return_status => l_return_status
514 , x_msg_count => l_msg_count
515 , x_msg_data => l_msg_data
516 );
517
518 /*
519 OE_Holds_PUB.RELEASE_HOLDS
520 ( p_entity_id => l_hold_entity_id
521 , p_hold_id => 1 -- Credit hold ID
522 , p_reason_code => 'PASS_CREDIT'
523 , p_entity_code => 'O' -- Order Entity
524 , x_result_out => resultout
525 , x_return_status => l_return_status
526 , x_msg_count => l_msg_count
527 , x_msg_data => l_msg_data
528 , p_api_version => 1.0
529 );
530 */
531
532 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
533 raise PROGRAM_ERROR;
534 end if;
535
536 -- Check for all the workflows (header/line/change order) that may be held up due
537 -- to this hold and complete them to loop back to the check for holds function
538
539 FOR curr_block_process IN blocked_process LOOP
540
541 IF l_debug_level > 0 THEN
542 oe_debug_pub.add( 'REL. HOLDS COMPLETE_ACT WITH ITEMTYPE:'||ITEMTYPE||' AND ITEMKEY:'||ITEMKEY ) ;
543 END IF;
544
545 -- Completing all blocked check for hold processes for this order
546
547 IF curr_block_process.activity_name = 'OE_HOLD_BLOCK' THEN
548
549 WF_ENGINE.CompleteActivity( curr_block_process.item_type
550 , curr_block_process.item_key
551 , curr_block_process.activity_name
552 , NULL
553 );
554
555 -- Completing all waiting credit check processes
556
557 ELSE
558
559 WF_ENGINE.CompleteActivity( curr_block_process.item_type
560 , curr_block_process.item_key
561 , curr_block_process.activity_name
562 , 'APPROVED'
563 );
564
565 END IF;
566
567 END LOOP;
568
569 END IF; -- Do nothing if the hold has already been released.
570
571 -- setting the resultout for workflow
572 resultout := 'COMPLETE:Null';
573
574 return;
575
576 end if;
577
578
579 --
580 -- CANCEL mode - activity 'compensation'
581 --
582 -- This is an event point is called with the effect of the activity must
583 -- be undone, for example when a process is reset to an earlier point
584 -- due to a loop back.
585 --
586 if (funcmode = 'CANCEL') then
587
588 -- your cancel code goes here
589 null;
590
591 -- no result needed
592 resultout := 'COMPLETE';
593 return;
594 end if;
595
596
597 --
598 -- Other execution modes may be created in the future. Your
599 -- activity will indicate that it does not implement a mode
600 -- by returning null
601 --
602 resultout := '';
603 return;
604
605 exception
606 when others then
607 -- The line below records this function call in the error system
608 -- in the case of an exception.
609 wf_core.context('OE_CREDIT_WF', 'OE_RELEASE_CREDIT_HOLD',
610 itemtype, itemkey, to_char(actid), funcmode);
611 -- start data fix project
612 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
613 p_itemtype => itemtype,
614 p_itemkey => itemkey);
615 OE_STANDARD_WF.Save_Messages;
616 OE_STANDARD_WF.Clear_Msg_Context;
617 -- end data fix project
618 raise;
619
620 end OE_RELEASE_CREDIT_HOLD;
621
622
623 /*-------------------------------------------------------------------
624 When the order is being updated and the credit has to be
625 re-evaluated, this process suspends the previous credit hold
626 notifications (if any).
627 ---------------------------------------------------------------------*/
628 procedure OE_WAIT_HOLD_NTF(
629 itemtype in varchar2,
630 itemkey in varchar2,
631 actid in number,
632 funcmode in varchar2,
633 resultout in out nocopy varchar2)
634 is
635 l_header_id NUMBER;
636 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
637 l_order_number NUMBER;
638 Cursor ntf_process_data IS
639 SELECT wfas.item_type, wfas.item_key
640 FROM wf_item_activity_statuses wfas, wf_process_activities wpa
641 WHERE wfas.item_type='OEOH'
642 AND wfas.process_activity = wpa.instance_id
643 AND wpa.activity_item_type='OEOH'
644 AND wpa.activity_name = 'OE_CREDIT_HOLD_NTF'
645 AND wfas.activity_status = 'NOTIFIED'
646 AND wfas.item_key = to_char(l_header_id)
647 UNION
648 SELECT wfas.item_type, wfas.item_key
649 FROM wf_item_activity_statuses wfas
650 , wf_process_activities wpa
651 , oe_order_lines_all ol
652 WHERE wfas.item_type='OEOL'
653 AND wfas.process_activity = wpa.instance_id
654 AND wpa.activity_item_type='OEOL'
655 AND wpa.activity_name = 'OE_CREDIT_HOLD_NTF'
656 AND wfas.activity_status = 'NOTIFIED'
657 AND wfas.item_key = ol.line_id
658 AND ol.header_id = l_header_id;
659
660 -- Not need anymore
661 --OR (item_type = 'OECHGORD' and item_key
662 -- IN (select to_char(P.wf_key_id)
663 -- from oe_line_pending_actions P
664 -- where P.header_id = l_header_id)));
665 --
666 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
667 --
668 begin
669 -- start data fix project
670 OE_STANDARD_WF.Set_Msg_Context(actid);
671 -- end data fix project
672 --
673 -- RUN mode - normal process execution
674 --
675
676 if (funcmode = 'RUN') then
677
678 l_header_id := GetHeaderId( itemtype, itemkey);
679
680 IF l_debug_level > 0 THEN
681 oe_debug_pub.add( 'IN THE WAIT_HOLD_NTF PROCEDURE' ) ;
682 END IF;
683
684 FOR current_ntf_data IN ntf_process_data LOOP
685
686 WF_ENGINE.CompleteActivity(current_ntf_data.item_type
687 , current_ntf_data.item_key
688 , 'OE_CREDIT_HOLD_NTF'
689 , 'WAIT'
690 );
691
692 END LOOP;
693
694 -- The workflow calling this procedure doesn't go through the apply
695 -- holds procedure as there is a credit hold already existing. However,
696 -- new notifications are sent and hence, the need to populate the
697 -- item attributes.
698
699 SELECT ORDER_NUMBER
700 INTO l_order_number
701 FROM OE_ORDER_HEADERS
702 WHERE header_id = l_header_id;
703
704 wf_engine.SetItemAttrNumber(itemtype, itemkey, 'ORDER_NUMBER', l_order_number);
705
706 resultout := 'COMPLETE:Null';
707 return;
708
709 end if;
710
711 --
712 -- CANCEL mode - activity 'compensation'
713 --
714 -- This is an event point is called with the effect of the activity must
715 -- be undone, for example when a process is reset to an earlier point
716 -- due to a loop back.
717 --
718 if (funcmode = 'CANCEL') then
719
720 -- your cancel code goes here
721 null;
722
723 -- no result needed
724 resultout := 'COMPLETE';
725 return;
726 end if;
727
728
729 --
730 -- Other execution modes may be created in the future. Your
731 -- activity will indicate that it does not implement a mode
732 -- by returning null
733 --
734 resultout := '';
735 return;
736
737 exception
738 when others then
739 -- The line below records this function call in the error system
740 -- in the case of an exception.
741 wf_core.context('OE_CREDIT_WF', 'OE_WAIT_HOLD_NTF',
742 itemtype, itemkey, to_char(actid), funcmode);
743 -- start data fix project
744 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
745 p_itemtype => itemtype,
746 p_itemkey => itemkey);
747 OE_STANDARD_WF.Save_Messages;
748 OE_STANDARD_WF.Clear_Msg_Context;
749 -- end data fix project
750 raise;
751
752 end OE_WAIT_HOLD_NTF;
753
754 /*--------------------------------------------------------------------
755 New BLOCK activity defined so that this activity cannot be
756 completed by the end-user through 'Progress Order'.
757 To be re-visited.
758 ----------------------------------------------------------------------*/
759 procedure CREDIT_BLOCK(itemtype in varchar2,
760 itemkey in varchar2,
761 actid in number,
762 funcmode in varchar2,
763 resultout in out nocopy varchar2)
764 is
765 --
766 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
767 --
768 begin
769 -- start data fix project
770 OE_STANDARD_WF.Set_Msg_Context(actid);
771 -- end data fix project
772 -- Do nothing in cancel or timeout mode
773 if (funcmode <> wf_engine.eng_run) then
774 resultout := wf_engine.eng_null;
775 return;
776 end if;
777
778 resultout := wf_engine.eng_notified||':'||wf_engine.eng_null||
779 ':'||wf_engine.eng_null;
780 exception
781 when others then
782 Wf_Core.Context('OE_CREDIT_WF', 'CREDIT_BLOCK', itemtype,
783 itemkey, to_char(actid), funcmode);
784 -- start data fix project
785 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
786 p_itemtype => itemtype,
787 p_itemkey => itemkey);
788 OE_STANDARD_WF.Save_Messages;
789 OE_STANDARD_WF.Clear_Msg_Context;
790 -- end data fix project
791 raise;
792
793 end CREDIT_BLOCK;
794
795 /*-----------------------------------------------------------------------
796 GetHeaderId retrieves the order header id based on the
797 current workflow(itemtype)
798 ------------------------------------------------------------------------*/
799 function GetHeaderID(itemtype in varchar2,
800 itemkey in varchar2
801 )
802 return NUMBER
803 is
804 l_header_id NUMBER;
805 -- Retrieves header id if it's a Line workflow
806 CURSOR line_header IS
807 SELECT header_id
808 FROM oe_order_lines
809 WHERE line_id = to_number(itemkey);
810 -- Retrieves header id if it's a Change Order workflow
811 -- Not used anymore
812 --CURSOR pending_rec_header IS
813 -- SELECT header_id
814 -- FROM oe_line_pending_actions
815 -- WHERE wf_key_id = to_number(itemkey);
816 --
817 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
818 --
819 begin
820
821 -- Retrieving the order header id based on the current workflow(itemtype)
822 IF itemtype = 'OEOH' THEN
823
824 l_header_id := to_number(itemkey);
825
826 ELSIF itemtype = 'OEOL' THEN
827
828 OPEN line_header;
829 FETCH line_header INTO l_header_id;
830 CLOSE line_header;
831
832 -- Not used anymore.
833 --ELSIF itemtype='OECHGORD' THEN
834 --
835 -- OPEN pending_rec_header;
836 -- FETCH pending_rec_header INTO l_header_id;
837 -- CLOSE pending_rec_header;
838
839 END IF;
840
841 return(l_header_id);
842
843 end GetHeaderID;
844
845 /*-------------------------------------------------------------------
846 Returns 'Y' if the last credit hold was manually released
847 else returns 'N'.
848 ---------------------------------------------------------------------*/
849 function CheckManualRelease(header_id in number)
850 return VARCHAR2
851 is
852 l_manual_release NUMBER := 0; -- no. of times credit hold was manually released
853 l_hold_release_id NUMBER := 0; -- release ID for the last credit hold
854 CURSOR released_hold IS
855 --added for BUG#9728597 Start
856 SELECT ohr.HOLD_RELEASE_ID
857 FROM OE_ORDER_HOLDS H, OE_HOLD_SOURCES S
858 ,oe_hold_releases ohr
859 WHERE H.HEADER_ID = header_id
860 AND H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
861 AND H.HOLD_RELEASE_ID IS NOT NULL
862 AND S.HOLD_ID = 1
863 AND S.HOLD_ENTITY_CODE = 'O'
864 AND S.HOLD_ENTITY_ID = header_id
865 AND S.RELEASED_FLAG ='Y'
866 AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
867 ORDER BY ohr.creation_date DESC;
868 --added for BUG#9728597 End
869
870 --commented for BUG#9728597
871 /* SELECT NVL(MAX(H.HOLD_RELEASE_ID),0)
872 FROM OE_ORDER_HOLDS H, OE_HOLD_SOURCES S
873 WHERE H.HEADER_ID = header_id
874 AND H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
875 AND H.HOLD_RELEASE_ID IS NOT NULL
876 AND S.HOLD_ID = 1
877 AND S.HOLD_ENTITY_CODE = 'O'
878 AND S.HOLD_ENTITY_ID = header_id
879 AND S.RELEASED_FLAG ='Y';*/
880 --commented for BUG#9728597
881
882 --
883 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
884 --
885 begin
886
887
888 -- Retrieve the hold release ID of the last released credit hold
889 -- for this order
890 OPEN released_hold;
891 --IF (released_hold%found) THEN --commented for BUG#9728597
892 FETCH released_hold INTO l_hold_release_id;
893 --added for BUG#9728597 Start
894 IF (released_hold%notfound) THEN
895 oe_debug_pub.add('No Released record for Margin Holds');
896 --added for BUG#9728597 End
897 END IF;
898 CLOSE released_hold;
899
900 IF l_hold_release_id > 0 THEN
901
902 SELECT count(*)
903 INTO l_manual_release
904 FROM OE_HOLD_RELEASES
905 WHERE HOLD_RELEASE_ID = l_hold_release_id
906 AND RELEASE_REASON_CODE <> 'PASS_CREDIT'
907 AND CREATED_BY <> 1;
908
909 IF l_manual_release > 0 THEN
910 return('Y');
911 ELSE
912 return('N');
913 END IF;
914
915 ELSE
916
917 return('N');
918
919 END IF;
920
921 end CheckManualRelease;
922
923 /*-------------------------------------------------------------------
924 Returns 'BOOKING' if the credit rule to be used is the
925 booking rule and returns 'SHIPPING' for the shipping rule.
926 ---------------------------------------------------------------------*/
927 function WhichCreditRule(itemtype in varchar2,
928 itemkey in varchar2,
929 actid in number)
930 return VARCHAR2
931 is
932 l_header_id NUMBER;
933 l_calling_action VARCHAR2(30); -- is the credit check rule Booking/Shipping
934 l_pick_rel_count NUMBER; -- has pick release been done for at least one order line?
935 l_next_pick_rel NUMBER; -- is the next activity = pick release?
936 l_fromact_id NUMBER; -- activity instance id of the parent process
937 -- Gets the number of order lines that have gone through pick release
938 CURSOR pick_rel IS
939 SELECT count(*)
940 FROM wf_item_activity_statuses wfas, wf_process_activities wpa
941 WHERE wfas.item_type = 'OEOL'
942 AND wfas.process_activity = wpa.instance_id
943 AND wpa.activity_item_type = 'OEOL'
944 AND wfas.item_key IN ( SELECT to_char(line_id)
945 FROM oe_order_lines_all
946 WHERE header_id = l_header_id)
947 AND wpa.activity_name = 'PICK_RELEASE';
948 -- Checks if the next activity is pick release
949 CURSOR next_pick_rel IS
950 SELECT count(*)
951 FROM wf_activity_transitions atr
952 WHERE atr.from_process_activity = l_fromact_id
953 AND result_code = 'APPROVED'
954 AND to_process_activity IN
955 (SELECT pa.instance_id
956 FROM wf_process_activities pa
957 WHERE pa.activity_name = 'PICK_RELEASE'
958 AND pa.activity_item_type = itemtype);
959 --
960 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
961 --
962 begin
963
964 l_header_id := GetHeaderID(itemtype, itemkey);
965
966 -- Setting the credit check rule to the 'booking rule' initially
967 l_calling_action := 'BOOKING';
968
969 -- Checking if there exists a line WF corresponding to this header that has
970 -- gone till the activity of PICK RELEASE
971
972 OPEN pick_rel;
973 FETCH pick_rel INTO l_pick_rel_count;
974 CLOSE pick_rel;
975
976 -- If any order line has gone till pick_release, use the 'shipping' rule
977 -- for credit check
978
979 IF l_pick_rel_count > 0 then
980 l_calling_action := 'SHIPPING';
981 END IF;
982
983
984 IF itemtype = 'OEOL' and l_calling_action = 'BOOKING' THEN
985
986 -- Retrieving the instance id for the credit checking process (NOT activity)
987 -- This assumes that this activity will always be called from within
988 -- the credit check process.
989
990 l_fromact_id := wf_engine_util.activity_parent_process(itemtype
991 ,itemkey
992 ,actid);
993
994 -- check if the next activity after credit check PROCESS is
995 -- pick release
996
997 OPEN next_pick_rel;
998 FETCH next_pick_rel INTO l_next_pick_rel;
999 CLOSE next_pick_rel;
1000
1001 -- If the next activity is pick release, use the 'shipping' rule
1002
1003 IF l_next_pick_rel > 0 then
1004 l_calling_action := 'SHIPPING';
1005 END IF;
1006
1007 END IF;
1008
1009 return(l_calling_action);
1010
1011 end WhichCreditRule;
1012
1013 end OE_CREDIT_WF;
[Home] [Help]