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