[Home] [Help]
PACKAGE BODY: APPS.OE_CREDIT_CHECK_ORDER_PVT
Source
1 PACKAGE BODY OE_credit_check_order_PVT AS
2 -- $Header: OEXVCRHB.pls 120.11.12010000.3 2008/11/12 07:53:12 cpati ship $
3 --+=======================================================================+
4 --| Copyright (c) 2001 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| |
9 --| FILENAME |
10 --| OEXVCRHB.pls |
11 --| |
12 --| DESCRIPTION |
13 --| Body of package OE_credit_check_order_PVT. It is used to determine |
14 --| if an order is subject to credit check. If it is, it will determine |
15 --| the available credit for a given credit rule that will check against|
16 --| the credit limits for the bill-to customer/site. The result returned|
17 --| will be |
18 --| 'PASS' if the customer/site passes credit check |
19 --| 'FAIL' if the customer/site fails credit check |
20 --| |
21 --| HISTORY |
22 --| Jun-01-2001 rajkrish created |
23 --| Jan-29-2002 multi org 3PM |
24 --| ontdev => 115.19 2001/11/07 23:24:54 |
25 --| Feb-14-2002 vto modified for external credit check API |
26 --| Feb-20-2002 vto Added function Address_Value_To_ID |
27 --| Feb-25-2002 tsimmond Added code for days_honor_manual_release |
28 --| to Check_Manual_Released_Holds function |
29 --| Mar-15-2002 vto Set G_result_out in |
30 --| check_other_credit_limits |
31 --| Mar-16-2002 tsimmond change condition in |
32 --| Check_Manual_Released_Holds |
33 --| Mar-25-2002 tsimmond changed '>' to '>=" for manual holds |
34 --| Apr-16-2002 vto fix bug 2325545. hold comment not set |
35 --| Apr-26-2002 rajkrish BUG 2338145 |
36 --| Jun-11-2002 rajkrish 2412678 |
37 --| rajkrish Bug 2787722 |
38 --| Aug-28-2002 rajkrish BUG 2505245 |
39 --| Sep-01-2002 tsimmond added code for FPI, submit AR |
40 --| Credit Management Review |
41 --| Nov-19-2002 |
42 --| 06-DEC-2002 vto Added NOCOPY to OUT variables |
43 --| 07-Jan-2003 tsimmond changed parameters values in Submit |
44 --| Credit Review |
45 --| 07-Feb-2003 2787722 |
46 --| Apr-01-2003 vto 2885044,2853800. Modify call to Check_Holds to|
47 --| pass in item_type and activity_name globals |
48 --| Apr-09-2003 tsimmond 2888032, changes in Submit Credit Review |
49 --| May-15-2003 vto 2894424, 2971689. New cc calling action: |
50 --| AUTO HOLD, AUTO RELEASE. |
51 --| Obsolete calling action: AUTO |
52 --| Bugbug2971644 June 12 rajkrish |
53 --| Bug 2948597 JUne 13 rajkrish |
54 --| Aug-24-2004 vto modified to support partial payments. |
55 --| also set created_by for release hold_source=1 |
56 --| Jan-15-2004 vto 3364726.G_crmgmt_installed instead of =TRUE |
57 --| Mar-10-2004 aksingh 3462295. Added api Update_Comments_And_Commit |
58 --| Jul-23-2004 vto 3788597. Modified get_order_exposure to not |
59 --| check hold if the calling action=EXTERNAL. |
60 --|=======================================================================+
61
62 --------------------
63 -- TYPE DECLARATIONS
64 --------------------
65
66
67 ------------
68 -- CONSTANTS
69 ------------
70 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_credit_check_order_PVT';
71
72 ---------------------------
73 -- PRIVATE GLOBAL VARIABLES
74 ---------------------------
75 G_debug_flag VARCHAR2(1) := NVL( OE_CREDIT_CHECK_UTIL.check_debug_flag ,'N') ;
76 G_result_out VARCHAR2(10) := 'PASS' ;
77 G_order NUMBER ;
78
79 g_hold_exist VARCHAR2(1) := NULL ;
80 ---------------------------
81 -- PROCEDURES AND FUNCTIONS
82 ---------------------------
83
84 --------------------------------------------------
85 -- Build the holds table to store the different
86 -- type of holds on the order lines for processing
87 -- during the credit check cycle.
88 --------------------------------------------------
89
90 -------------------------------------------------------
91 -- Check if credit hold was manually released.
92 -- N: No release records found
93 -- Y: Release records found
94 -------------------------------------------------------
95 FUNCTION Check_Manual_Released_Holds
96 ( p_calling_action IN VARCHAR2
97 , p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
98 , p_header_id IN NUMBER
99 , p_line_id IN NUMBER
100 , p_credit_check_rule_rec IN
101 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
102 )
103 RETURN VARCHAR2
104 IS
105 l_hold_release_id NUMBER;
106 l_dummy VARCHAR2(1);
107 l_manual_hold_exists VARCHAR2(1) := 'N';
108 l_released_rec_exists VARCHAR2(1) := 'Y';
109 l_release_date DATE;
110
111 BEGIN
112 IF G_debug_flag = 'Y'
113 THEN
114
115 OE_DEBUG_PUB.Add('OEXVCRHB: In Check_Manual_Released_Holds');
116 OE_DEBUG_PUB.Add('Check Header ID/Line ID: '||p_header_id||'/'||p_line_id);
117 OE_DEBUG_PUB.Add('p_calling action = '|| p_calling_action );
118 OE_DEBUG_PUB.Add('G_delayed_request = '||
119 OE_credit_engine_GRP.G_delayed_request );
120 END IF;
121
122 -- Will check only if delayed_request is FALSE
123 -- In other words, it will not check if the order is updated
124
125 -- Adding 'AUTO HOLD' for bug# 4207478
126 IF p_calling_action IN ('SHIPPING', 'PACKING' , 'PICKING', 'AUTO HOLD')
127 AND NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) =
128 FND_API.G_FALSE
129 THEN
130
131 BEGIN
132 SELECT /* MOAC_SQL_CHANGE */ NVL(MAX(H.HOLD_RELEASE_ID),0)
133 INTO l_hold_release_id
134 FROM OE_ORDER_HOLDS h,
135 OE_HOLD_SOURCES_ALL s
136 WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
137 AND H.HEADER_ID = p_header_id
138 AND H.LINE_ID IS NULL
139 AND H.HOLD_RELEASE_ID IS NOT NULL
140 AND S.HOLD_ID = p_hold_id
141 AND S.HOLD_ENTITY_CODE = 'O'
142 AND S.HOLD_ENTITY_ID = p_header_id
143 AND S.RELEASED_FLAG ='Y';
144 EXCEPTION
145 WHEN NO_DATA_FOUND THEN
146 OE_DEBUG_PUB.Add
147 ('No data found ');
148 l_released_rec_exists := 'N';
149 END;
150
151 IF l_released_rec_exists = 'Y' THEN
152 BEGIN
153 SELECT
154 'Y',CREATION_DATE
155 INTO
156 l_manual_hold_exists
157 , l_release_date
158 FROM OE_HOLD_RELEASES
159 WHERE HOLD_RELEASE_ID = l_hold_release_id
160 AND RELEASE_REASON_CODE <> 'PASS_CREDIT'
161 AND CREATED_BY <> 1;
162
163 -----check if days_honor_manual_release expired
164 IF p_credit_check_rule_rec.days_honor_manual_release IS NOT NULL
165 THEN
166 IF (l_release_date +
167 p_credit_check_rule_rec.days_honor_manual_release
168 >= SYSDATE )
169 THEN
170 l_manual_hold_exists := 'Y';
171 ELSE
172 l_manual_hold_exists := 'N';
173 END IF;
174 END IF;
175
176 EXCEPTION
177 WHEN NO_DATA_FOUND THEN
178 OE_DEBUG_PUB.Add
179 ('No manually released credit holds for Header ID/Line ID: '||
180 p_header_id||'/'||p_line_id,1);
181 l_manual_hold_exists := 'N';
182
183 END;
184 END IF;
185 END IF;
186
187 IF G_debug_flag = 'Y'
188 THEN
189 OE_DEBUG_PUB.Add('OEXVCRHB: Out Check_Manual_Released_Holds:'||l_manual_hold_exists);
190 END IF;
191
192 RETURN l_manual_hold_exists;
193
194 EXCEPTION
195 WHEN OTHERS THEN
196 OE_DEBUG_PUB.ADD(SUBSTR(SQLERRM,1,300) ) ;
197
198 OE_MSG_PUB.Add_Exc_Msg
199 ( G_PKG_NAME
200 , 'Check_Manual_Released_Holds'
201 );
202
203 RAISE;
204
205
206 END Check_Manual_Released_Holds;
207
208 -----------------------------------------------------
209 -- Check if credit hold exists already
210 ------------------------------------------------------
211
212 FUNCTION Hold_Exists
213 ( p_header_id IN NUMBER
214 , p_line_id IN NUMBER
215 )
216 RETURN BOOLEAN IS
217 l_hold_result VARCHAR2(30);
218 l_return_status VARCHAR2(30);
219 l_msg_count NUMBER;
220 l_msg_data VARCHAR2(2000);
221 BEGIN
222 IF G_debug_flag = 'Y'
223 THEN
224 OE_DEBUG_PUB.ADD('OEXVCRHB: In Hold_Exists');
225 OE_DEBUG_PUB.ADD('OEXVCRHB: Check holds for Header ID : '
226 || p_header_id,1);
227 OE_DEBUG_PUB.ADD('g_hold_exist => '|| g_hold_exist );
228
229
230 END IF;
231
232 IF g_hold_exist = 'Y'
233 THEN
234 l_hold_result := FND_API.G_TRUE ;
235 ELSIF g_hold_exist = 'N'
236 THEN
237 l_hold_result := 'F' ;
238 ELSE
239 l_hold_result := NULL ;
240
241 OE_DEBUG_PUB.ADD('Calling OE_HOLDS_PUB.Check_Holds ');
242
243 OE_HOLDS_PUB.Check_Holds
244 ( p_api_version => 1.0
245 , p_header_id => p_header_id
246 , p_hold_id => 1
247 , p_wf_item =>
248 OE_Credit_Engine_GRP.G_cc_hold_item_type
249 , p_wf_activity =>
250 OE_Credit_Engine_GRP.G_cc_hold_activity_name
251 , p_entity_code => 'O'
252 , p_entity_id => p_header_id
253 , x_result_out => l_hold_result
254 , x_msg_count => l_msg_count
255 , x_msg_data => l_msg_data
256 , x_return_status => l_return_status
257 );
258
259 IF G_debug_flag = 'Y'
260 THEN
261 OE_DEBUG_PUB.ADD('OEXVCRHB: Out Check_Holds ');
262 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
263 END IF;
264
265 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
266 RAISE FND_API.G_EXC_ERROR;
267 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
269 END IF;
270
271 END IF; -- g_hold_exist
272
273 IF G_debug_flag = 'Y'
274 THEN
275 OE_DEBUG_PUB.ADD('OEXVCRHB: about to Hold_Exists');
276 OE_DEBUG_PUB.ADD('l_hold_result => '|| l_hold_result );
277 END IF;
278
279 IF l_hold_result = FND_API.G_TRUE THEN
280 return TRUE;
281 ELSE
282 return FALSE;
283 END IF;
284
285 EXCEPTION
286 WHEN OTHERS THEN
287 OE_MSG_PUB.Add_Exc_Msg
288 ( G_PKG_NAME
289 , 'Hold_Exists'
290 );
291
292 RAISE;
293 END Hold_Exists;
294
295
296 ---------------------------------------------------
297 -- Write release message to the message table |
298 -- table and to the logfile if called from a |
299 -- concurrent program. |
300 ---------------------------------------------------
301
302 PROCEDURE Write_Release_Message (
303 p_calling_action IN VARCHAR2
304 , p_order_number IN NUMBER
305 , p_line_number IN NUMBER
306 )
307 IS
308 BEGIN
309
310 IF G_debug_flag = 'Y'
311 THEN
312 OE_DEBUG_PUB.Add('OEXVCRHB: In Write_Release_Message');
313 END IF;
314
315 IF p_calling_action = 'AUTO RELEASE' THEN
316 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Order Number: '||TO_CHAR(p_order_number)
317 ||' Line Number: '||TO_CHAR(p_line_number)
318 ||' released from credit check hold.');
319 END IF;
320
321 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_REMOVED');
322 FND_MESSAGE.Set_Token('ORDER_NUMBER',p_order_number);
323 FND_MESSAGE.Set_Token('LINE_NUMBER',p_line_number);
324 OE_MSG_PUB.Add;
325 ---OE_MSG_PUB.Save_Messages(1);
326
327 IF G_debug_flag = 'Y'
328 THEN
329 OE_DEBUG_PUB.Add('OEXVCRHB: Out Write_Release_Message');
330 END IF;
331 EXCEPTION
332 WHEN OTHERS THEN
333 OE_MSG_PUB.Add_Exc_Msg
334 ( G_PKG_NAME
335 , 'Write_Release_Message'
336 );
337
338 RAISE;
339
340 END Write_Release_Message;
341
342 ---------------------------------------------------
343 -- Write appropriate message to either the message|
344 -- table or to the logfile if called from a |
345 -- concurrent program for order level credit hold |
346 ---------------------------------------------------
347
348 PROCEDURE Write_Order_Hold_Msg
349 (
350 p_calling_action IN VARCHAR2
351 , p_cc_limit_used IN VARCHAR2 DEFAULT NULL
352 , p_cc_profile_used IN VARCHAR2 DEFAULT NULL
353 , p_order_number IN NUMBER
354 , p_item_category IN VARCHAR2 DEFAULT NULL
355 , x_comment OUT NOCOPY VARCHAR2
356 )
357 IS
358 l_comment VARCHAR2(2000);
359 l_cc_profile_used VARCHAR2(30); --6616741
360 l_calling_activity VARCHAR2(50); --ER#7479609
361 BEGIN
362 IF G_debug_flag = 'Y'
363 THEN
364 OE_DEBUG_PUB.Add('OEXVCRHB: In Write_Order_Hold_Msg');
365 OE_DEBUG_PUB.Add('p_calling_action => '||
366 p_calling_action );
367 OE_DEBUG_PUB.Add('p_cc_limit_used => '||
368 p_cc_limit_used );
369 OE_DEBUG_PUB.Add('p_cc_profile_used => '||
370 p_cc_profile_used );
371 OE_DEBUG_PUB.Add('p_order_number => '||
372 p_order_number );
373 END IF;
374
375 -- Write to message stack anyway regardless of the calling action
376 IF p_cc_limit_used <> 'ITEM' THEN
377 IF p_calling_action <> 'EXTERNAL'
378 THEN
379 -- bug 4002820
380 IF INSTR(p_cc_limit_used, ',') > 0 THEN
381 l_cc_profile_used := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_PROFILE', p_cc_profile_used); -- 6616741
382 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_MSG');
383 FND_MESSAGE.Set_Token('LIMIT_USED',p_cc_limit_used);
384 --6616741 FND_MESSAGE.Set_Token('CC_PROFILE',p_cc_profile_used);
385 FND_MESSAGE.Set_Token('CC_PROFILE',l_cc_profile_used); --6616741
386 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
387 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_MSG');
388 FND_MESSAGE.Set_Token('LIMIT_USED',p_cc_limit_used);
389 --6616741 FND_MESSAGE.Set_Token('CC_PROFILE',p_cc_profile_used);
390 FND_MESSAGE.Set_Token('CC_PROFILE',l_cc_profile_used); --6616741
391 OE_MSG_PUB.Add;
392 ELSE
393 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_'||p_cc_limit_used||'_'||
394 p_cc_profile_used);
395 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
396 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_'||p_cc_limit_used||'_'||
397 p_cc_profile_used);
398 OE_MSG_PUB.Add;
399 END IF;
400 ELSE
401 --bug4583872
402 IF INSTR(p_cc_limit_used, ',') > 0 THEN
403 FND_MESSAGE.Set_Name('ONT','OE_CC_EXT_MSG');
404 FND_MESSAGE.Set_Token('LIMIT_USED',p_cc_limit_used);
405 FND_MESSAGE.Set_Token('CC_PROFILE',p_cc_profile_used);
406 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
407 FND_MESSAGE.Set_Name('ONT','OE_CC_EXT_MSG');
408 FND_MESSAGE.Set_Token('LIMIT_USED',p_cc_limit_used);
409 FND_MESSAGE.Set_Token('CC_PROFILE',p_cc_profile_used);
410 OE_MSG_PUB.Add;
411 ELSE
412 FND_MESSAGE.Set_Name('ONT','OE_CC_EXT_'||p_cc_limit_used||'_'||
413 p_cc_profile_used);
414 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
415 FND_MESSAGE.Set_Name('ONT','OE_CC_EXT_'||p_cc_limit_used||'_'||
416 p_cc_profile_used);
417 OE_MSG_PUB.Add;
418 END IF;
419 END IF;
420 ELSE
421 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_'||p_cc_limit_used||'_CATEGORY');
422 FND_MESSAGE.Set_Token('CATEGORY',p_item_category);
423 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
424 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_'||p_cc_limit_used||'_'||
425 'CATEGORY');
426 FND_MESSAGE.Set_Token('CATEGORY',p_item_category);
427 OE_MSG_PUB.Add;
428 END IF;
429 --
430 -- Write to logfile if original call was from a concurrent program
431 --
432 IF p_calling_action = 'AUTO HOLD' THEN
433 FND_FILE.PUT_LINE(FND_FILE.LOG,'Order '||TO_CHAR(p_order_number)
434 ||': Credit check hold applied');
435 FND_FILE.PUT_LINE(FND_FILE.LOG,'Hold Comment: '||SUBSTR(l_comment,1,1000));
436 END IF;
437
438 --ER#7479609 start
439 IF OE_Verify_Payment_PUB.G_init_calling_action = 'AUTO HOLD' THEN
440 l_calling_activity := 'Credit Check Processor';
441 ELSE
442 l_calling_activity := InitCap(OE_Verify_Payment_PUB.G_init_calling_action);
443 END IF;
444
445 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_ACT_COM');
446 FND_MESSAGE.Set_Token('CALLING_ACTIVITY',l_calling_activity);
447 FND_MESSAGE.Set_Token('CREDIT_CHECK_RULE',OE_Verify_Payment_PUB.G_credit_check_rule);
448
449 l_comment := l_comment||SUBSTR(FND_MESSAGE.GET,1,2000);
450
451 --ER#7479609 end
452
453 x_comment := l_comment;
454
455 IF G_debug_flag = 'Y'
456 THEN
457 OE_DEBUG_PUB.Add('x_comment => '||
458 x_comment );
459 OE_DEBUG_PUB.Add('OEXVCRHB: Out Write_Order_Hold_Msg');
460 END IF;
461
462 EXCEPTION
463 WHEN OTHERS THEN
464 OE_MSG_PUB.Add_Exc_Msg
465 ( G_PKG_NAME
466 , 'Write_Order_Hold_Msg'
467 );
468
469 RAISE;
470
471 END Write_Order_Hold_Msg;
472
473 ---------------------------------------------------
474 -- Write release message to the screen or to the
475 -- log file if called from a concurrent program.
476 ---------------------------------------------------
477 PROCEDURE Write_Order_Release_Msg
478 ( p_calling_action IN VARCHAR2
479 , p_order_number IN NUMBER
480 )
481 IS
482 BEGIN
483 IF G_debug_flag = 'Y'
484 THEN
485 OE_DEBUG_PUB.Add('OEXVCRHB: In Write_Order_Release_Msg');
486 END IF;
487
488 IF p_calling_action = 'AUTO RELEASE' THEN
489 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Order '||TO_CHAR(p_order_number)
490 ||': Credit check hold released.');
491 ELSE
492 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_REMOVED');
493 OE_MSG_PUB.Add;
494 END IF;
495 --
496 IF G_debug_flag = 'Y'
497 THEN
498 OE_DEBUG_PUB.Add('OEXVCRHB: Out Write_Order_Release_Msg');
499 END IF;
500
501 EXCEPTION
502 WHEN OTHERS THEN
503 OE_MSG_PUB.Add_Exc_Msg
504 ( G_PKG_NAME
505 , 'Write_Order_Release_Msg'
506 );
507
508 RAISE;
509
510 END Write_Order_Release_Msg;
511
512 ------------------------------------------------
513
514 PROCEDURE Apply_hold_and_commit
515 ( p_hold_source_rec IN
516 OE_HOLDS_PVT.Hold_Source_Rec_Type
517 , x_msg_count OUT NOCOPY NUMBER
518 , x_msg_data OUT NOCOPY VARCHAR2
519 , x_return_status OUT NOCOPY VARCHAR2
520 )
521 IS
522
523 PRAGMA AUTONOMOUS_TRANSACTION;
524
525
526 BEGIN
527
528 OE_DEBUG_PUB.ADD(' OEXVCRHB: In Apply_hold_and_commit ');
529 OE_DEBUG_PUB.ADD(' Call OE_Holds_PUB.Apply_Holds ');
530
531
532 OE_Holds_PUB.Apply_Holds
533 ( p_api_version => 1.0
534 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
535 , p_hold_source_rec => p_hold_source_rec
536 , x_msg_count => x_msg_count
537 , x_msg_data => x_msg_data
538 , x_return_status => x_return_status
539 );
540
541 OE_DEBUG_PUB.ADD(' Out OE_Holds_PUB.Apply_Holds ');
542 OE_DEBUG_PUB.ADD(' x_return_status => '|| x_return_status );
543
544 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
545 RAISE FND_API.G_EXC_ERROR;
546 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
548 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
549
550 OE_DEBUG_PUB.ADD(' Holds success ');
551 OE_DEBUG_PUB.ADD(' About to Issue COMMIT');
552
553 COMMIT;
554
555 OE_DEBUG_PUB.ADD(' AFter Issue COMMIT');
556
557 END IF;
558
559 OE_DEBUG_PUB.ADD(' OEXVCRHB: OUT Apply_hold_and_commit ');
560
561 EXCEPTION
562 WHEN OTHERS THEN
563 rollback;
564 OE_DEBUG_PUB.ADD(' Error in Apply_hold_and_commit ' );
565 OE_DEBUG_PUB.ADD(' SQLERRM: '|| SQLERRM );
566 OE_MSG_PUB.Add_Exc_Msg
567 ( G_PKG_NAME
568 , 'Apply_hold_and_commit'
569 );
570
571 RAISE;
572
573 END Apply_hold_and_commit ;
574
575 ----------------------------------------------------
576 -- Procedure to Update Hold Comments And Commit --
577 ----------------------------------------------------
578 PROCEDURE Update_Comments_And_Commit
579 ( p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
580 , x_msg_count OUT NOCOPY NUMBER
581 , x_msg_data OUT NOCOPY VARCHAR2
582 , x_return_status OUT NOCOPY VARCHAR2
583 )
584 IS
585 PRAGMA AUTONOMOUS_TRANSACTION;
586 BEGIN
587
588 IF G_debug_flag = 'Y'
589 THEN
590 OE_DEBUG_PUB.ADD('OEXVCRHB: Entering Update_Comments_And_Commit');
591 OE_DEBUG_PUB.ADD('OEXVCRHB: Before OE_Holds_PUB.Update_Hold_Comments');
592 END IF;
593
594 OE_Holds_PUB.Update_Hold_comments
595 ( p_hold_source_rec => p_hold_source_rec
596 , x_msg_count => x_msg_count
597 , x_msg_data => x_msg_data
598 , x_return_status => x_return_status
599 );
600
601 IF G_debug_flag = 'Y'
602 THEN
603 OE_DEBUG_PUB.ADD('OEXVCRHB: After OE_Holds_PUB.Update_Hold_Comments Status '
604 || x_return_status);
605 END IF;
606
607 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
608 RAISE FND_API.G_EXC_ERROR;
609 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
611 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
612 IF G_debug_flag = 'Y'
613 THEN
614 OE_DEBUG_PUB.ADD('OEXVCRHB: Update Hold Comment Success, Issue COMMIT');
615 END IF;
616
617 COMMIT;
618
619 IF G_debug_flag = 'Y'
620 THEN
621 OE_DEBUG_PUB.ADD('OEXVCRHB: After Issuing COMMIT');
622 END IF;
623 END IF;
624
625 IF G_debug_flag = 'Y'
626 THEN
627 OE_DEBUG_PUB.ADD(' OEXVCRHB: Exiting Update_Comments_And_Commit');
628 END IF;
629
630 EXCEPTION
631 WHEN OTHERS THEN
632 rollback;
633 OE_DEBUG_PUB.ADD('OEXVCRHB: Error in Update_Comments_And_Commit' );
634 OE_DEBUG_PUB.ADD('SQLERRM: '|| SQLERRM );
635 OE_MSG_PUB.Add_Exc_Msg
636 ( G_PKG_NAME
637 , 'Update_Comments_And_Commit'
638 );
639
640 RAISE;
641
642 END Update_Comments_And_Commit ;
643
644 --------------------------------------------------
645
646 ---------------------------------------------------
647 -- Apply credit check hold on the specified order
648 ---------------------------------------------------
649 /*
650 ** Bug # 3415608 and 3430235
651 ** Reverted changes done under bug # 3386382.
652 ** Introduced new procedure Update_Comments_And_Commit to
653 ** Update and Commit Hold Comments. Apply_Holds_And_Commit
654 ** And Update_Comments_And_Commit are now called whenever
655 ** Calling Action is Picking, Packing or Shipping ELSE
656 ** Apply_Holds and Update_Hold_Comments are called.
657 */
658
659 PROCEDURE Apply_Order_CC_Hold
660 ( p_header_id IN NUMBER
661 , p_order_number IN NUMBER
662 , p_calling_action IN VARCHAR2 DEFAULT 'BOOKING'
663 , p_cc_limit_used IN VARCHAR2
664 , p_cc_profile_used IN VARCHAR2
665 , p_item_category_id IN NUMBER
666 , p_system_parameter_rec IN OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
667 , p_credit_check_rule_rec IN
668 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
669 , x_cc_hold_comment OUT NOCOPY VARCHAR2
670 , x_cc_result_out OUT NOCOPY VARCHAR2
671 )
672 IS
673
674 -- Cursor to select the category description
675 CURSOR item_category_csr IS
676 SELECT description
677 FROM mtl_categories
678 WHERE category_id = p_item_category_id;
679
680 l_item_category VARCHAR2(240):= NULL;
681 l_cc_result_out VARCHAR2(30) := 'FAIL_NONE';
682 l_hold_exists VARCHAR2(1) := NULL ;
683 l_msg_count NUMBER := 0;
684 l_msg_data VARCHAR2(2000);
685 l_return_status VARCHAR2(30);
686 l_hold_comment VARCHAR2(2000);
687 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type :=
688 OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
689 BEGIN
690 IF G_debug_flag = 'Y'
691 THEN
692 OE_DEBUG_PUB.Add('OEXVCRHB: In Apply_Order_CC_Hold');
693 END IF;
694
695 IF p_cc_limit_used = 'ITEM' THEN
696 OPEN item_category_csr;
697 FETCH item_category_csr INTO l_item_category;
698 CLOSE item_category_csr;
699 END IF;
700
701 --
702 -- Set hold source
703 --
704 l_hold_source_rec.hold_id := 1; -- credit hold
705 l_hold_source_rec.hold_entity_code := 'O'; -- order hold
706 l_hold_source_rec.hold_entity_id := p_header_id; -- order header
707 --
708 IF Hold_Exists( p_header_id => p_header_id
709 , p_line_id => NULL
710 ) THEN
711 G_result_out := 'FAIL' ;
712 Write_Order_Hold_Msg
713 (
714 p_calling_action => p_calling_action
715 , p_cc_limit_used => p_cc_limit_used
716 , p_cc_profile_used => p_cc_profile_used
717 , p_order_number => p_order_number
718 , p_item_category => l_item_category
719 , x_comment => l_hold_comment
720 );
721
722 l_hold_source_rec.hold_comment := l_hold_comment;
723
724 IF G_debug_flag = 'Y'
725 THEN
726 OE_DEBUG_PUB.Add('OEXVCRHB: Hold already applied on Header ID:' ||
727 p_header_id, 1);
728 END IF;
729
730 IF NVL(p_calling_action, 'BOOKING') IN ('SHIPPING','PACKING','PICKING')
731 THEN
732 IF G_debug_flag = 'Y'
733 THEN
734 OE_DEBUG_PUB.ADD('OEXVCRHB: Call Update_Comments_And_Commit');
735 END IF;
736
737 Update_Comments_And_Commit
738 ( p_hold_source_rec => l_hold_source_rec
739 , x_msg_count => l_msg_count
740 , x_msg_data => l_msg_data
741 , x_return_status => l_return_status
742 );
743
744 IF G_debug_flag = 'Y'
745 THEN
746 OE_DEBUG_PUB.ADD('OEXVCRHB: Out Update_Comments_And_Commit');
747 END IF;
748
749 ELSIF NVL( p_calling_action,'BOOKING') IN ('BOOKING','UPDATE','AUTO HOLD')
750 THEN
751 IF G_debug_flag = 'Y'
752 THEN
753 OE_DEBUG_PUB.ADD('OEXVCRHB: Call OE_Holds_PUB.Update_Hold_Comments directly');
754 END IF;
755
756 OE_Holds_PUB.Update_hold_comments
757 ( p_hold_source_rec => l_hold_source_rec
758 , x_msg_count => l_msg_count
759 , x_msg_data => l_msg_data
760 , x_return_status => l_return_status
761 );
762
763 IF G_debug_flag = 'Y'
764 THEN
765 OE_DEBUG_PUB.ADD('OEXVCRHB: Out OE_Holds_PUB.Update_Hold_Comments directly');
766 END IF;
767 END IF; -- Calling Action
768
769 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
770 RAISE FND_API.G_EXC_ERROR;
771 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
773 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
774 IF G_debug_flag = 'Y'
775 THEN
776 OE_DEBUG_PUB.ADD('OEXVCRHB: Updated Comments on Header ID:'
777 ||p_header_id, 1);
778 END IF;
779 END IF;
780 ELSE
781 IF Check_Manual_Released_Holds(
782 p_calling_action => p_calling_action
783 ,p_hold_id => 1
784 ,p_header_id => p_header_id
785 ,p_line_id => NULL
786 ,p_credit_check_rule_rec=>p_credit_check_rule_rec
787 ) = 'N'
788 THEN
789 IF G_debug_flag = 'Y'
790 THEN
791 OE_DEBUG_PUB.Add('No manual released holds ');
792 END IF;
793 G_result_out := 'FAIL' ;
794 Write_Order_Hold_Msg
795 (
796 p_calling_action => p_calling_action
797 , p_cc_limit_used => p_cc_limit_used
798 , p_cc_profile_used => p_cc_profile_used
799 , p_order_number => p_order_number
800 , p_item_category => l_item_category
801 , x_comment => l_hold_comment
802 );
803 l_hold_source_rec.hold_comment := l_hold_comment;
804
805 ------------------------------------------------------------
806 -- Call for all actions except for the
807 -- concurrent program credit check processor
808 IF NVL(p_calling_action, 'BOOKING') IN ('SHIPPING','PACKING','PICKING')
809 THEN
810
811 IF G_debug_flag = 'Y'
812 THEN
813 OE_DEBUG_PUB.ADD('OEXVCRHB: Call Apply_hold_and_commit ');
814 END IF;
815
816 Apply_hold_and_commit
817 ( p_hold_source_rec => l_hold_source_rec
818 , x_msg_count => l_msg_count
819 , x_msg_data => l_msg_data
820 , x_return_status => l_return_status
821 );
822
823 IF G_debug_flag = 'Y'
824 THEN
825 OE_DEBUG_PUB.ADD('OEXVCRHB: Out Apply_hold_and_commit ');
826 END IF;
827
828 ELSIF NVL( p_calling_action,'BOOKING') IN ('BOOKING','UPDATE','AUTO HOLD')
829 THEN
830 IF G_debug_flag = 'Y'
831 THEN
832 OE_DEBUG_PUB.ADD('OEXVCRHB: Call OE_Holds_PUB.Apply_Holds directly');
833 END IF;
834
835
836 OE_Holds_PUB.Apply_Holds
837 ( p_api_version => 1.0
838 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
839 , p_hold_source_rec => l_hold_source_rec
840 , x_msg_count => l_msg_count
841 , x_msg_data => l_msg_data
842 , x_return_status => l_return_status
843 );
844
845 IF G_debug_flag = 'Y' THEN
846 OE_DEBUG_PUB.ADD('OEXVCRHB: Out OE_Holds_PUB.Apply_Holds directly');
847 END IF;
848 END IF; --check calling action
849
850
851 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
852 RAISE FND_API.G_EXC_ERROR;
853 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
855 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
856 IF G_debug_flag = 'Y' THEN
857 OE_DEBUG_PUB.ADD('OEXVCRHB: Credit check hold applied header_ID: '
858 ||p_header_id, 1);
859 END IF;
860 END IF;
861 l_cc_result_out := 'FAIL_HOLD';
862 END IF; -- Check manual holds
863 END IF; -- Check hold exist
864 x_cc_hold_comment := l_hold_comment;
865 x_cc_result_out := l_cc_result_out;
866
867 IF G_debug_flag = 'Y'
868 THEN
869 OE_DEBUG_PUB.ADD('OEXVCRHB: Apply_Order_CC_Hold Result = '
870 ||x_cc_result_out);
871 OE_DEBUG_PUB.Add('OEXVCRHB: Out Apply_Order_CC_Hold');
872 END IF;
873
874 EXCEPTION
875 WHEN OTHERS THEN
876 OE_MSG_PUB.Add_Exc_Msg
877 ( G_PKG_NAME
878 , 'Apply_Order_CC_Hold'
879 );
880
881 RAISE;
882
883 END Apply_Order_CC_Hold;
884
885 -----------------------------------------
886 -- Release order level credit check hold
887 -- in the database.
888 -----------------------------------------
889
890 PROCEDURE Release_Order_CC_Hold
891 ( p_header_id IN NUMBER
892 , p_order_number IN NUMBER
893 , p_calling_action IN VARCHAR2 DEFAULT 'BOOKING'
894 , P_SYSTEM_PARAMETER_REC IN OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
895 , x_cc_result_out OUT NOCOPY VARCHAR2
896 )
897 IS
898
899 --ER#7479609 l_hold_entity_id NUMBER := p_header_id;
900 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE := p_header_id; --ER#7479609
901 l_hold_id NUMBER;
902 l_hold_exists VARCHAR2(1);
903 l_hold_result VARCHAR2(30);
904 l_msg_count NUMBER := 0;
905 l_msg_data VARCHAR2(2000);
906 l_return_status VARCHAR2(30);
907 l_release_reason VARCHAR2(30);
908 l_cc_result_out VARCHAR2(30) := 'PASS_NONE';
909 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type :=
910 OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
911 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type :=
912 OE_HOLDS_PVT.G_MISS_Hold_Release_REC;
913 l_calling_activity VARCHAR2(50); --ER#7479609
914 BEGIN
915 IF G_debug_flag = 'Y'
916 THEN
917 OE_DEBUG_PUB.Add('OEXVCRHB: In Release_Order_CC_Hold',1);
918 END IF;
919
920 l_return_status := FND_API.G_RET_STS_SUCCESS;
921
922 IF G_debug_flag = 'Y'
923 THEN
924 OE_DEBUG_PUB.Add('Check if Holds exist to release ');
925 END IF;
926
927 IF hold_exists( p_header_id => p_header_id
928 , p_line_id => NULL
929 )
930 THEN
931 l_hold_source_rec.hold_id := 1; -- Credit Checking hold
932 l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
933 l_hold_source_rec.HOLD_ENTITY_ID := p_header_id;
934
935 l_hold_release_rec.release_reason_code := 'PASS_CREDIT';
936 l_hold_release_rec.release_comment := 'Credit Check Engine' ;
937 l_hold_release_rec.created_by := 1; -- indicate non-manual release
938
939 --ER#7479609 start
940 IF OE_Verify_Payment_PUB.G_init_calling_action = 'AUTO RELEASE' THEN
941 l_calling_activity := 'Credit Check Processor';
942 ELSE
943 l_calling_activity := InitCap(OE_Verify_Payment_PUB.G_init_calling_action);
944 END IF;
945
946 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_ACT_COM');
947 FND_MESSAGE.Set_Token('CALLING_ACTIVITY',l_calling_activity);
948 FND_MESSAGE.Set_Token('CREDIT_CHECK_RULE',OE_Verify_Payment_PUB.G_credit_check_rule);
949
950 l_hold_release_rec.release_comment := l_hold_release_rec.release_comment||SUBSTR(FND_MESSAGE.GET,1,2000);
951
952 --ER#7479609 end
953
954
955 IF G_debug_flag = 'Y'
956 THEN
957 OE_DEBUG_PUB.ADD('Attempt to Release hold on ' || p_header_id, 1);
958 END IF;
959
960 IF NVL(p_calling_action, 'BOOKING') <> 'AUTO HOLD' THEN
961 OE_Holds_PUB.Release_Holds
962 ( p_api_version => 1.0
963 , p_hold_source_rec => l_hold_source_rec
964 , p_hold_release_rec => l_hold_release_rec
965 , x_msg_count => l_msg_count
966 , x_msg_data => l_msg_data
967 , x_return_status => l_return_status
968 );
969 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
970 RAISE FND_API.G_EXC_ERROR;
971 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
973 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
974 Write_Order_Release_Msg(
975 p_calling_action => p_calling_action
976 , p_order_number => p_order_number
977 );
978
979 IF G_debug_flag = 'Y'
980 THEN
981 OE_DEBUG_PUB.ADD('OEXVCRHB: Released credit check hold on Header ID:'
982 || p_header_id, 1);
983 END IF;
984 END IF;
985 l_cc_result_out := 'PASS_REL';
986 END IF; -- calling action check
987 END IF; -- hold exist
988 x_cc_result_out := l_cc_result_out;
989
990 IF G_debug_flag = 'Y'
991 THEN
992 OE_DEBUG_PUB.Add('OEXVCRHB: Out Release_Order_CC_Hold');
993 END IF;
994 EXCEPTION
995 WHEN OTHERS THEN
996 OE_MSG_PUB.Add_Exc_Msg
997 ( G_PKG_NAME
998 , 'Release_Order_CC_Hold'
999 );
1000
1001 RAISE;
1002
1003 END Release_Order_CC_Hold;
1004
1005
1006 -------------------------------------------------
1007 -- Chk_Past_Due_Invoice
1008 -- Check if any Invoice exist with past due date
1009
1010 --------------------------------------------------
1011 PROCEDURE Chk_Past_Due_Invoice
1012 ( p_customer_id IN NUMBER
1013 , p_site_use_id IN NUMBER
1014 , p_party_id IN NUMBER
1015 , p_credit_check_rule_rec IN
1016 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
1017 , p_system_parameter_rec IN
1018 OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
1019 , p_credit_level IN VARCHAR2
1020 , p_usage_curr IN oe_credit_check_util.curr_tbl_type
1021 , p_include_all_flag IN VARCHAR2
1022 , p_global_exposure_flag IN VARCHAR2 := 'N'
1023 , x_cc_result_out OUT NOCOPY VARCHAR2
1024 , x_return_status OUT NOCOPY VARCHAR2
1025 )
1026 IS
1027 l_exist_flag VARCHAR2(1);
1028
1029 BEGIN
1030 IF G_debug_flag = 'Y'
1031 THEN
1032 OE_DEBUG_PUB.ADD('OEXVCRHB: In Chk_Past_Due_Invoice');
1033 OE_DEBUG_PUB.ADD('p_global_exposure_flag = '|| p_global_exposure_flag );
1034 OE_DEBUG_PUB.ADD('Call Get_Past_Due_Invoice ');
1035 END IF;
1036
1037 -- Initialize return status to success
1038 x_return_status := FND_API.G_RET_STS_SUCCESS;
1039 -- Default to pass
1040 x_cc_result_out := 'PASS';
1041
1042
1043
1044
1045 OE_CREDIT_CHECK_UTIL.Get_Past_Due_Invoice
1046 ( p_customer_id => p_customer_id
1047 , p_site_use_id => p_site_use_id
1048 , p_party_id => p_party_id
1049 , p_credit_check_rule_rec => p_credit_check_rule_rec
1050 , p_system_parameter_rec => p_system_parameter_rec
1051 , p_credit_level => p_credit_level
1052 , p_usage_curr => p_usage_curr
1053 , p_include_all_flag => p_include_all_flag
1054 , p_global_exposure_flag => p_global_exposure_flag
1055 , x_exist_flag => l_exist_flag
1056 , x_return_status => x_return_status
1057 );
1058
1059 IF G_debug_flag = 'Y'
1060 THEN
1061 OE_DEBUG_PUB.ADD(' After Get_Past_Due_Invoice ');
1062 OE_DEBUG_PUB.ADD(' x_return_status = '|| x_return_status);
1063 OE_DEBUG_PUB.ADD(' l_exist_flag = '|| l_exist_flag );
1064 END IF;
1065
1066 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1067 RAISE FND_API.G_EXC_ERROR;
1068 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1070 END IF;
1071
1072
1073 IF l_exist_flag = 'Y'
1074 THEN
1075 x_cc_result_out := 'FAIL';
1076 END IF;
1077
1078 IF G_debug_flag = 'Y'
1079 THEN
1080 OE_DEBUG_PUB.ADD(' x_cc_result_out = '|| x_cc_result_out );
1081 OE_DEBUG_PUB.ADD('OEXVCRHB: Out Chk_Past_Due_Invoice');
1082 END IF;
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1086 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1087 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Chk_Past_Due_invoice');
1088 END IF;
1089 END Chk_Past_Due_Invoice;
1090
1091 --------------------------------------------------------------
1092 -- Check_Trx_Limit
1093 -- Check if the current order transaction amount exceeds the
1094 -- credit limit
1095 ---------------------------------------------------------------
1096 PROCEDURE Check_Trx_Limit
1097 ( p_header_rec IN OE_ORDER_PUB.header_rec_type
1098 , p_customer_id IN NUMBER
1099 , p_site_use_id IN NUMBER
1100 , p_credit_level IN VARCHAR2
1101 , p_credit_check_rule_rec IN
1102 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
1103 , p_system_parameter_rec IN
1104 OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
1105 , p_limit_curr_code IN VARCHAR2
1106 , p_trx_credit_limit IN NUMBER
1107 , p_calling_action IN VARCHAR2
1108 , p_transaction_amount IN NUMBER
1109 , x_cc_result_out OUT NOCOPY VARCHAR2
1110 , x_return_status OUT NOCOPY VARCHAR2
1111 , x_conversion_status OUT NOCOPY OE_CREDIT_CHECK_UTIL.curr_tbl_type
1112 )
1113 IS
1114
1115 l_order_value NUMBER := 0 ;
1116
1117 BEGIN
1118
1119 IF G_debug_flag = 'Y'
1120 THEN
1121 OE_DEBUG_PUB.ADD('OEXVCRHB: In Check_Trx_Limit');
1122 END IF;
1123
1124 -- Initialize return status to success
1125 x_return_status := FND_API.G_RET_STS_SUCCESS;
1126 -- Default to Pass
1127 x_cc_result_out := 'PASS';
1128
1129 ----------------------------------------------
1130 -- additional task - Read the value of |
1131 -- include_tax_flag from credit check rule |
1132 -- and calculate the value of l_order_values |
1133 -- accordingly. If the value of |
1134 -- include_tax_flag is NULL that means it is |
1135 -- 'No' |
1136 ----------------------------------------------
1137 --
1138 -- Call the get_transaction_amount procedure if the calling_action
1139 -- <> EXTERNAL, else call the GET_external_trx_amount procedure
1140 --
1141 IF NVL(p_calling_action,'BOOKING') <> 'EXTERNAL' THEN
1142 ----------------------------------------------
1143 -- Do not include lines with payment term |
1144 -- that have credit check flag = N. NULL |
1145 -- means Y. |
1146 ----------------------------------------------
1147 IF G_debug_flag = 'Y'
1148 THEN
1149 OE_DEBUG_PUB.ADD(' call GET_transaction_amount ');
1150 END IF;
1151
1152 OE_CREDIT_CHECK_UTIL.GET_transaction_amount
1153 ( p_header_id => p_header_rec.header_id
1154 , p_transaction_curr_code => p_header_rec.transactional_curr_code
1155 , p_credit_check_rule_rec => p_credit_check_rule_rec
1156 , p_system_parameter_rec => p_system_parameter_rec
1157 , p_customer_id => NULL
1158 , p_site_use_id => NULL
1159 , p_limit_curr_code => p_limit_curr_code
1160 , x_amount => l_order_value
1161 , x_conversion_status => x_conversion_status
1162 , x_return_status => x_return_status
1163 );
1164
1165 IF G_debug_flag = 'Y'
1166 THEN
1167 OE_DEBUG_PUB.ADD(' after GET_transaction_amount with status '
1168 || x_return_status );
1169 END IF;
1170
1171 ELSE
1172 IF G_debug_flag = 'Y'
1173 THEN
1174 OE_DEBUG_PUB.ADD(' call GET_external_trx_amount ');
1175 END IF;
1176
1177 OE_CREDIT_CHECK_UTIL.GET_external_trx_amount
1178 ( p_transaction_curr_code => p_header_rec.transactional_curr_code
1179 , p_transaction_amount => p_transaction_amount
1180 , p_credit_check_rule_rec => p_credit_check_rule_rec
1181 , p_system_parameter_rec => p_system_parameter_rec
1182 , p_limit_curr_code => p_limit_curr_code
1183 , x_amount => l_order_value
1184 , x_conversion_status => x_conversion_status
1185 , x_return_status => x_return_status
1186 );
1187
1188 IF G_debug_flag = 'Y'
1189 THEN
1190 OE_DEBUG_PUB.ADD(' after GET_transaction_amount with status '
1191 || x_return_status );
1192 END IF;
1193
1194 END IF;
1195
1196 IF G_debug_flag = 'Y'
1197 THEN
1198 OE_DEBUG_PUB.ADD(' err curr table count = '
1199 || x_conversion_status.COUNT );
1200 END IF;
1201
1202 IF NVL(x_conversion_status.COUNT,0) > 0
1203 THEN
1204 IF G_debug_flag = 'Y'
1205 THEN
1206 OE_DEBUG_PUB.ADD(' Currency conversion failure ');
1207 END IF;
1208
1209 x_cc_result_out := 'FAIL';
1210
1211 IF G_debug_flag = 'Y'
1212 THEN
1213 OE_DEBUG_PUB.Add('Fails trx credit limit');
1214 END IF;
1215
1216 END IF;
1217
1218 IF G_debug_flag = 'Y'
1219 THEN
1220 OE_DEBUG_PUB.ADD(' l_order_value = ' || l_order_value );
1221 OE_DEBUG_PUB.ADD(' p_trx_credit_limit = '|| p_trx_credit_limit );
1222 END IF;
1223
1224
1225 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1226 RAISE FND_API.G_EXC_ERROR;
1227 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1229 END IF;
1230 IF l_order_value > NVL(p_trx_credit_limit ,l_order_value )
1231 THEN
1232 x_cc_result_out := 'FAIL';
1233
1234 END IF;
1235
1236 IF NVL(x_conversion_status.COUNT,0) > 0
1237 THEN
1238 IF G_debug_flag = 'Y'
1239 THEN
1240 OE_DEBUG_PUB.ADD(' Currency conversion failure ');
1241 END IF;
1242
1243 x_cc_result_out := 'FAIL';
1244
1245 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
1246 FND_MESSAGE.Set_Token('FROM',p_header_rec.transactional_curr_code);
1247 FND_MESSAGE.Set_Token('TO',p_limit_curr_code );
1248 FND_MESSAGE.Set_Token('CONV',
1249 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'));
1250 OE_Credit_Engine_GRP.G_currency_error_msg :=
1251 SUBSTR(FND_MESSAGE.GET,1,1000) ;
1252
1253 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
1254 FND_MESSAGE.Set_Token('FROM',p_header_rec.transactional_curr_code);
1255 FND_MESSAGE.Set_Token('TO',p_limit_curr_code );
1256 FND_MESSAGE.Set_Token('CONV',
1257 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'));
1258 OE_MSG_PUB.ADD;
1259
1260 G_result_out := 'FAIL' ;
1261 x_cc_result_out := 'FAIL';
1262 x_return_status := FND_API.G_RET_STS_ERROR;
1263
1264 IF G_debug_flag = 'Y'
1265 THEN
1266 OE_DEBUG_PUB.ADD('Return status after assigned as Error = '
1267 || x_return_status );
1268 END IF;
1269
1270 END IF;
1271
1272 IF G_debug_flag = 'Y'
1273 THEN
1274 OE_DEBUG_PUB.ADD('x_cc_result_out = ' || x_cc_result_out );
1275 OE_DEBUG_PUB.ADD('OEXVCRHB: Out Check_Trx_Limit');
1276 END IF;
1277 EXCEPTION
1278 WHEN others THEN
1279 OE_DEBUG_PUB.Add('Check_Trx_Limit: Other exceptions');
1280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1281 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1282 OE_MSG_PUB.Add_Exc_Msg
1283 ( G_PKG_NAME
1284 , 'Check_Trx_Limit'
1285 );
1286 END IF;
1287 END Check_Trx_Limit;
1288
1289
1290 -------------------------------------------------------------------
1291 -- FUNCTION: check_credit_check_flags
1292 -- COMMENT:
1293 -- The function has been renamed from check_pay_term.
1294 -- It check if there is at least one line in the order
1295 -- that have both the associated payment term and payment
1296 -- type code credit check flag set to Yes, then
1297 -- the order requires credit check.
1298 -- IF at least one line exists, THEN
1299 -- RETURN 'Y'
1300 -- ELSE RETURN 'N'.
1301 -- It is used to determine if the order is subject to
1302 -- credit check.
1303 -- Default is Yes
1304 ---------------------------------------------------------------------
1305 FUNCTION check_credit_check_flags
1306 ( p_header_id IN NUMBER )
1307 RETURN VARCHAR2
1308 IS
1309
1310 l_exist VARCHAR2(1);
1311 l_count NUMBER ;
1312
1313 BEGIN
1314 IF G_debug_flag = 'Y'
1315 THEN
1316 OE_DEBUG_PUB.Add(' IN check_credit_check_flags ');
1317 END IF;
1318
1319 SELECT /* MOAC_SQL_NO_CHANGE */ COUNT(line_id)
1320 INTO l_count
1321 FROM OE_ORDER_LINES_ALL L
1322 , OE_ORDER_HEADERS_ALL H
1323 , RA_TERMS_B T
1324 WHERE h.HEADER_ID = p_header_id
1325 AND L.HEADER_ID = H.HEADER_ID
1326 AND T.TERM_ID = L.PAYMENT_TERM_ID
1327 AND NVL(T.CREDIT_CHECK_FLAG,'N') = 'Y' --bug4888346
1328 AND (EXISTS
1329 (SELECT NULL
1330 FROM oe_payment_types_all pt
1331 WHERE pt.payment_type_code = NVL(l.payment_type_code,
1332 NVL(h.payment_type_code, 'BME'))
1333 AND pt.credit_check_flag = 'Y'
1334 AND NVL(pt.org_id, -99) = NVL(l.org_id,-99))
1335 OR
1336 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
1337
1338 IF l_count > 0
1339 THEN
1340 l_exist := 'Y' ;
1341 ELSE
1342 l_exist := 'N' ;
1343 END IF;
1344
1345
1346 IF G_debug_flag = 'Y'
1347 THEN
1348 OE_DEBUG_PUB.Add(' l_exist = '|| l_exist );
1349 OE_DEBUG_PUB.Add(' l_count = '|| l_count );
1350 OE_DEBUG_PUB.Add(' OUT check_credit_check_flags ');
1351 END IF;
1352
1353 RETURN l_exist;
1354
1355 EXCEPTION
1356 WHEN others THEN
1357 OE_DEBUG_PUB.Add('check_credit_check_flags: Other exceptions');
1358 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1359 OE_MSG_PUB.Add_Exc_Msg
1360 ( G_PKG_NAME
1361 , 'check_credit_check_flags'
1362 );
1363 END IF;
1364 RAISE;
1365 END check_credit_check_flags;
1366
1367
1368
1369 -----------------------------------------------------+
1370 -- A trx is subject to credit check if all the |
1371 -- following four conditions are true: |
1372 -- 1. related credit rule available for the trx type |
1373 -- 2. credit check enabled for the payment term |
1374 -- 3. credit check enabled for site or cust |
1375 -- 4. credit limits available for site or cust |
1376 -- When true, the procedure returns limits/other info|
1377 ------------------------------------------------------
1378
1379 PROCEDURE Validate_other_credit_check
1380 ( p_header_rec IN OE_ORDER_PUB.header_rec_type
1381 , p_customer_id IN NUMBER
1382 , p_site_use_id IN NUMBER
1383 , p_calling_action IN VARCHAR2 := 'BOOKING'
1384 , p_credit_check_rule_rec IN
1385 OE_Credit_Check_Util.OE_credit_rules_rec_type
1386 , p_party_id IN NUMBER
1387 , x_check_order_flag OUT NOCOPY VARCHAR2
1388 , x_credit_check_lvl_out OUT NOCOPY VARCHAR2
1389 , x_default_limit_flag OUT NOCOPY VARCHAR2
1390 , x_limit_curr_code OUT NOCOPY VARCHAR2
1391 , x_overall_credit_limit OUT NOCOPY NUMBER
1392 , x_trx_credit_limit OUT NOCOPY NUMBER
1393 , x_usage_curr OUT NOCOPY OE_CREDIT_CHECK_UTIL.curr_tbl_type
1394 , x_include_all_flag OUT NOCOPY VARCHAR2
1395 , x_return_status OUT NOCOPY VARCHAR2
1396 , x_global_exposure_flag OUT NOCOPY VARCHAR2
1397 , x_credit_limit_entity_id OUT NOCOPY NUMBER
1398
1399 )
1400 IS
1401
1402 l_suppress_flag VARCHAR2(1) := 'N' ;
1403 l_credit_check_flag VARCHAR2(1) ;
1404
1405 BEGIN
1406 IF G_debug_flag = 'Y'
1407 THEN
1408 OE_DEBUG_PUB.ADD('OEXVCRHB: In Validate_other_credit_check');
1409 OE_DEBUG_PUB.ADD('HEADER id = '|| p_header_rec.header_id );
1410 OE_DEBUG_PUB.ADD('p_customer_id = '|| p_customer_id );
1411 OE_DEBUG_PUB.ADD('p_site_use_id = ' || p_site_use_id );
1412 END IF;
1413
1414 x_check_order_flag := 'Y';
1415 x_return_status := FND_API.G_RET_STS_SUCCESS;
1416 x_global_exposure_flag := 'N' ;
1417
1418 -- If precalculated, no need to figure out the
1419 -- transaction curr first during ALL currency check
1420
1421 IF NVL(p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG,'N') = 'N'
1422 THEN
1423 l_suppress_flag := 'Y' ;
1424 ELSE
1425 l_suppress_flag := 'N' ;
1426 END IF;
1427
1428 IF G_debug_flag = 'Y'
1429 THEN
1430 OE_DEBUG_PUB.ADD('QUICK_CR_CHECK_FLAG = '||
1431 p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG );
1432 OE_DEBUG_PUB.ADD('l_suppress_flag = '|| l_suppress_flag );
1433 OE_DEBUG_PUB.ADD('----------------------------------------- ');
1434 END IF;
1435
1436
1437
1438 ------------------------------------------------
1439 -- Program Logic: |
1440 -- 1. get site-level limit for the trx |
1441 -- 2. If (1) fails, get-customer-level limit |
1442 -----------------------------------------------|
1443 -- level | data | meaning |
1444 -----------------------------------------------|
1445 -- site | credit_check | stop. credit check |
1446 -- | flag = 'N' | not reqd for the trx |
1447 -----------------------------------------------|
1448 -- site | trx limit & | check customer |
1449 -- | overall limit| limits (and default |
1450 -- | are null | limit for the org) |
1451 -----------------------------------------------|
1452 -- cust/ | credit_check | stop. credit check |
1453 -- org | flag = 'N' | not reqd for the trx |
1454 -----------------------------------------------|
1455 -- cust/ | trx limit & | stop. credit check |
1456 -- org | overall limit| not reqd for the trx |
1457 -- | are null | |
1458 -----------------------------------------------|
1459 -- Note: |
1460 -- all rules of customer limits apply to the |
1461 -- default limits of the operating unit |
1462 -- [a 11.5.3 feature] |
1463 ------------------------------------------------
1464
1465
1466 OE_CREDIT_CHECK_UTIL.Get_Limit_Info
1467 ( p_header_id => p_header_rec.header_id
1468 , p_entity_type => 'SITE'
1469 , p_entity_id => p_site_use_id
1470 , p_cust_account_id => p_customer_id
1471 , p_party_id => p_party_id
1472 , p_trx_curr_code => p_header_rec.transactional_curr_code
1473 , p_suppress_unused_usages_flag => l_suppress_flag
1474 , p_navigate_to_next_level => 'Y'
1475 , p_precalc_exposure_used =>
1476 p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG
1477 , x_limit_curr_code => x_limit_curr_code
1478 , x_trx_limit => x_trx_credit_limit
1479 , x_overall_limit => x_overall_credit_limit
1480 , x_include_all_flag => x_include_all_flag
1481 , x_usage_curr_tbl => x_usage_curr
1482 , x_default_limit_flag => x_default_limit_flag
1483 , x_global_exposure_flag => x_global_exposure_flag
1484 , x_credit_limit_entity_id => x_credit_limit_entity_id
1485 , x_credit_check_level => x_credit_check_lvl_out
1486 );
1487
1488 IF G_debug_flag = 'Y'
1489 THEN
1490 OE_DEBUG_PUB.ADD(' after SITE Get_Limit_Info ');
1491 OE_DEBUG_PUB.ADD(' l_credit_check_flag ==> '|| l_credit_check_flag );
1492 OE_DEBUG_PUB.ADD(' x_limit_curr_code ==> ' || x_limit_curr_code );
1493 OE_DEBUG_PUB.ADD(' x_trx_credit_limit ==> '|| x_trx_credit_limit );
1494 OE_DEBUG_PUB.ADD(' x_overall_credit_limit
1495 ==> '|| x_overall_credit_limit );
1496 END IF;
1497
1498 IF (x_trx_credit_limit IS NULL AND
1499 x_overall_credit_limit IS NULL )
1500 THEN
1501 x_global_exposure_flag := 'N' ;
1502 x_check_order_flag := 'N' ;
1503 x_credit_limit_entity_id := NULL;
1504 x_credit_check_lvl_out := NULL ;
1505 END IF;
1506
1507 IF G_debug_flag = 'Y'
1508 THEN
1509 OE_DEBUG_PUB.ADD(' ');
1510 OE_DEBUG_PUB.ADD('Results from the credit profiles check');
1511 OE_DEBUG_PUB.ADD('--------------------------------------------');
1512 OE_DEBUG_PUB.Add('x_check_order_flag = '
1513 || x_check_order_flag,1 );
1514 OE_DEBUG_PUB.Add('x_credit_check_lvl_out = '
1515 || x_credit_check_lvl_out,1);
1516 OE_DEBUG_PUB.Add('x_default_limit_flag = '
1517 || x_default_limit_flag);
1518 OE_DEBUG_PUB.Add('x_limit_curr_code = '
1519 || x_limit_curr_code,1);
1520 OE_DEBUG_PUB.Add('x_overall_credit_limit = '
1521 ||x_overall_credit_limit,1);
1522 OE_DEBUG_PUB.Add('x_trx_credit_limit = '
1523 || x_trx_credit_limit,1);
1524 OE_DEBUG_PUB.Add('x_include_all_flag = '
1525 ||x_include_all_flag);
1526 OE_DEBUG_PUB.add ('x_global_exposure_flag = '||
1527 x_global_exposure_flag,1 );
1528 OE_DEBUG_PUB.add ('x_credit_limit_entity_id => '||
1529 x_credit_limit_entity_id );
1530
1531 OE_DEBUG_PUB.ADD(' ');
1532 OE_DEBUG_PUB.ADD('****** List of associated Usage currency rules **** ');
1533 OE_DEBUG_PUB.ADD(' ');
1534 END IF;
1535
1536 FOR K IN 1..x_usage_curr.COUNT
1537 LOOP
1538 IF G_debug_flag = 'Y'
1539 THEN
1540 OE_DEBUG_PUB.Add(' Usage currency ' || k || ' => ' ||
1541 x_usage_curr(K).usage_curr_code );
1542 END IF;
1543
1544 END LOOP ;
1545
1546
1547 IF G_debug_flag = 'Y'
1548 THEN
1549 OE_DEBUG_PUB.ADD(' ');
1550 OE_DEBUG_PUB.ADD('**************** End of List *********************** ');
1551 OE_DEBUG_PUB.Add('OEXVCRHB: Out Validate_other_credit_check');
1552 END IF;
1553
1554 EXCEPTION
1555 WHEN OTHERS THEN
1556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1558 OE_MSG_PUB.Add_Exc_Msg
1559 ( G_PKG_NAME
1560 , 'Validate_other_credit_check'
1561 );
1562 END IF;
1563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1564 END Validate_other_credit_check;
1565
1566 -----------------------------------------------------------
1567 -- PROCEDURE: Check_order_exposure PUBLIC
1568 -- DESCRIPTION: Calculate the exposure and compare against
1569 -- the overall credit limits to determine
1570 -- credit check status (PASS or FAIL).
1571 -- The calling_action can be the following:
1572 -- BOOKING - Called when booking an order
1573 -- UPDATE - Called when order is updated
1574 -- SHIPPING - Called from shipping
1575 -- PACKING
1576 -- PICKING
1577 -- AUTO - Obsolete. Was called from credit check processor
1578 -- AUTO RELEASE - Called from credit check processor to
1579 -- release credit hold only.
1580 -- AUTO HOLD - Called from credit check processor to
1581 -- apply credit hold only.
1582 -- EXTERNAL - Called from Check External Credit API
1583 -----------------------------------------------------------
1584 PROCEDURE Check_order_exposure
1585 ( p_customer_id IN NUMBER
1586 , p_site_use_id IN NUMBER
1587 , p_party_id IN NUMBER
1588 , p_header_id IN NUMBER
1589 , p_credit_level IN VARCHAR2
1590 , p_transaction_curr_code IN VARCHAR2
1591 , p_transaction_amount IN NUMBER DEFAULT 0
1592 , p_limit_curr_code IN VARCHAR2
1593 , p_overall_credit_limit IN NUMBER
1594 , p_calling_action IN VARCHAR2
1595 , p_usage_curr IN OE_CREDIT_CHECK_UTIL.curr_tbl_type
1596 , p_include_all_flag IN VARCHAR2 DEFAULT 'N'
1597 , p_holds_rel_flag IN VARCHAR2 DEFAULT 'N'
1598 , p_default_limit_flag IN VARCHAR2 DEFAULT 'N'
1599 , p_credit_check_rule_rec IN OE_Credit_Check_Util.OE_credit_rules_rec_type
1600 , p_system_parameter_rec IN OE_Credit_Check_Util.OE_systems_param_rec_type
1601 , p_global_exposure_flag IN VARCHAR2 := 'N'
1602 , p_credit_limit_entity_id IN VARCHAR2
1603 , x_total_exposure OUT NOCOPY NUMBER
1604 , x_cc_result_out OUT NOCOPY VARCHAR2
1605 , x_error_curr_tbl OUT NOCOPY OE_CREDIT_CHECK_UTIL.curr_tbl_type
1606 , x_return_status OUT NOCOPY VARCHAR2
1607 )
1608 IS
1609
1610
1611 l_conversion_status OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE ;
1612 l_customer_id NUMBER;
1613 l_site_id NUMBER;
1614 l_transaction_value NUMBER := 0;
1615 l_current_order_value NUMBER := 0 ;
1616 l_order_amount NUMBER ;
1617 l_order_hold_amount NUMBER ;
1618 l_ar_amount NUMBER ;
1619
1620 BEGIN
1621 x_return_status := FND_API.G_RET_STS_SUCCESS;
1622
1623 IF G_debug_flag = 'Y'
1624 THEN
1625
1626 OE_DEBUG_PUB.Add('OEXVCRHB: IN Check_order_exposure ');
1627 OE_DEBUG_PUB.Add(' ');
1628 OE_DEBUG_PUB.Add('-******-------------********---------------**********--');
1629 OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id );
1630 OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id );
1631 OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id );
1632 OE_DEBUG_PUB.Add('p_party_id = ' || p_party_id );
1633 OE_DEBUG_PUB.Add('p_credit_limit_entity_id = '||
1634 p_credit_limit_entity_id );
1635 OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level );
1636 OE_DEBUG_PUB.Add('p_transaction_curr_code = '|| p_transaction_curr_code );
1637 OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code );
1638 OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag );
1639 OE_DEBUG_PUB.Add('p_default_limit_flag = '|| p_default_limit_flag );
1640 OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag );
1641 OE_DEBUG_PUB.Add('p_transaction_amount = '|| p_transaction_amount );
1642 OE_DEBUG_PUB.Add('-******------------********---------------**********--');
1643 OE_DEBUG_PUB.Add(' ');
1644 OE_DEBUG_PUB.Add(' Precalculated flag = '||
1645 p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG );
1646 OE_DEBUG_PUB.Add(' ');
1647 END IF;
1648
1649
1650 x_total_exposure := 0 ;
1651 l_conversion_status.DELETE ;
1652
1653 IF p_credit_level = 'PARTY'
1654 THEN
1655 l_customer_id := NULL ;
1656 l_site_id := NULL;
1657 ELSIF p_credit_level = 'CUSTOMER'
1658 THEN
1659 l_customer_id := p_customer_id ;
1660 l_site_id := NULL;
1661 ELSE
1662 l_customer_id := p_customer_id ;
1663 l_site_id := p_site_use_id ;
1664 END IF;
1665
1666 IF G_debug_flag = 'Y'
1667 THEN
1668 OE_DEBUG_PUB.ADD('err curr table count = ' || l_conversion_status.COUNT );
1669 END IF;
1670
1671 IF NVL(l_conversion_status.COUNT,0) > 0 THEN
1672 x_cc_result_out := 'FAIL';
1673
1674 END IF;
1675
1676 IF p_overall_credit_limit IS NOT NULL
1677 THEN
1678 --
1679 -- If the calling action is 'EXTERNAL', then convert the transaction_amount
1680 -- to the limit currency.
1681 --
1682 l_current_order_value := 0 ;
1683
1684 IF NVL(p_calling_action, 'BOOKING') = 'EXTERNAL' THEN
1685
1686 IF G_debug_flag = 'Y'
1687 THEN
1688 OE_DEBUG_PUB.Add('OEXVCRHB GET_external_trx_amount ');
1689 END IF;
1690
1691 OE_CREDIT_CHECK_UTIL.GET_external_trx_amount
1692 ( p_transaction_curr_code => p_transaction_curr_code
1693 , p_transaction_amount => p_transaction_amount
1694 , p_credit_check_rule_rec => p_credit_check_rule_rec
1695 , p_system_parameter_rec => p_system_parameter_rec
1696 , p_limit_curr_code => p_limit_curr_code
1697 , x_amount => l_transaction_value
1698 , x_conversion_status => l_conversion_status
1699 , x_return_status => x_return_status
1700 );
1701
1702 IF G_debug_flag = 'Y'
1703 THEN
1704
1705 OE_DEBUG_PUB.Add(' after get_external_trx_amount ');
1706 OE_DEBUG_PUB.Add(' x_return_status ' || x_return_status );
1707 OE_DEBUG_PUB.Add(' l_trasaction_value = '|| l_transaction_value );
1708 OE_DEBUG_PUB.ADD('err curr table count = '
1709 || l_conversion_status.COUNT );
1710 END IF;
1711
1712 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1713 RAISE FND_API.G_EXC_ERROR;
1714 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1715 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1716 END IF;
1717 END IF;
1718
1719 IF NVL(l_conversion_status.COUNT,0) > 0 THEN
1720 IF G_debug_flag = 'Y' THEN
1721 OE_DEBUG_PUB.Add(' Currency conversion failure ');
1722 END IF;
1723 ELSE
1724 -- Get exposure only when there are no previous conversion failures.
1725 -- otherwise, just fail the exposure credit check with status Error.
1726
1727 g_hold_exist := NULL ;
1728
1729 IF NVL(p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG,'N') = 'N'
1730 THEN
1731
1732 OE_credit_check_util.Get_order_exposure
1733 ( p_header_id => p_header_id
1734 , p_transaction_curr_code => p_transaction_curr_code
1735 , p_customer_id => p_customer_id
1736 , p_site_use_id => p_site_use_id
1737 , p_credit_check_rule_rec => p_credit_check_rule_rec
1738 , p_system_parameter_rec => p_system_parameter_rec
1739 , p_credit_level => p_credit_level
1740 , p_limit_curr_code => p_limit_curr_code
1741 , p_usage_curr => p_usage_curr
1742 , p_include_all_flag => p_include_all_flag
1743 , p_global_exposure_flag => p_global_exposure_flag
1744 , p_need_exposure_details => 'N'
1745 , x_total_exposure => x_total_exposure
1746 , x_order_amount => l_order_amount
1747 , x_order_hold_amount => l_order_hold_amount
1748 , x_ar_amount => l_ar_amount
1749 , x_conversion_status => l_conversion_status
1750 , x_return_status => x_return_status
1751 );
1752
1753 IF G_debug_flag = 'Y'
1754 THEN
1755 OE_DEBUG_PUB.Add(' after get_order_exposure ');
1756 OE_DEBUG_PUB.Add(' x_return_status ' || x_return_status );
1757 OE_DEBUG_PUB.Add(' x_total_exposure = '|| x_total_exposure );
1758 OE_DEBUG_PUB.Add(' err cur tbl count = '||
1759 l_conversion_status.COUNT );
1760 END IF;
1761
1762 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1763 RAISE FND_API.G_EXC_ERROR;
1764 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1766 END IF;
1767
1768 ELSE
1769
1770
1771 OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1772 ( p_customer_id => l_customer_id
1773 , p_site_use_id => l_site_id
1774 , p_party_id => p_credit_limit_entity_id
1775 , p_header_id => p_header_id
1776 , p_credit_check_rule_rec => p_credit_check_rule_rec
1777 , p_system_parameters_rec => p_system_parameter_rec
1778 , p_limit_curr_code => p_limit_curr_code
1779 , p_usage_curr_tbl => p_usage_curr
1780 , p_include_all_flag => p_include_all_flag
1781 , p_global_exposure_flag => p_global_exposure_flag
1782 , p_need_exposure_details => 'N'
1783 , x_total_exposure => x_total_exposure
1784 , x_return_status => x_return_status
1785 , x_order_amount => l_order_amount
1786 , x_order_hold_amount => l_order_hold_amount
1787 , x_ar_amount => l_ar_amount
1788 , x_error_curr_tbl => l_conversion_status
1789 );
1790
1791 IF G_debug_flag = 'Y'
1792 THEN
1793 OE_DEBUG_PUB.Add('Out of Precalculated exposure ');
1794 OE_DEBUG_PUB.Add('x_return_status = '|| x_return_status );
1795 OE_DEBUG_PUB.Add('x_total_exposure = '|| x_total_exposure );
1796 OE_DEBUG_PUB.Add('Error table count = '||l_conversion_status.COUNT );
1797 END IF;
1798
1799 -- BUG Fix 2338145
1800 -- Get the current order amount to be included into the
1801 -- pre-calc exposure during booking action
1802
1803 -- Bug fix 2787722
1804 -- The current order amount should also be included
1805 -- for Non-Booking actions if the
1806 -- credit check rule does notInclude
1807 -- OM Uninvoiced Orders exposure
1808
1809 -- Add current Order amount if calling action <> BOOKING
1810 -- and credit check rule is EXCLUDE Order amount on Hold
1811 -- and current Order is already on Hold during Booking
1812
1813 -- bug fix 4184524 for calling action <> BOOKING:
1814 -- if both orders_on_hold_flag and uninvoiced_orders_flag are
1815 -- all 'Y', no need to consider the current order amount as they
1816 -- would have already been considered when running the Credit Summary
1817 -- concurrent program.
1818 -- if orders_on_hold_flag is 'N' and uninvoiced_orders_flag is 'Y',
1819 -- then only need to considers order amount for orders that are on hold.
1820 -- if orders_on_hold_flag is 'N' and uninvoiced_orders_flag is 'N',
1821 -- then need to consider the current order amount regardless if there is
1822 -- hold or not.
1823
1824 l_current_order_value := 0 ;
1825 g_hold_exist := NULL;
1826
1827 IF NVL(p_calling_action, 'BOOKING') = 'BOOKING' AND
1828 NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) =
1829 FND_API.G_FALSE
1830 THEN
1831 l_current_order_value :=
1832 NVL(OE_CREDIT_CHECK_UTIL.g_current_order_value,0) ;
1833 IF G_debug_flag = 'Y'
1834 THEN
1835 OE_DEBUG_PUB.Add('Calling action=BOOKING and G_delayed_request=FALSE');
1836 END IF;
1837 ELSIF NVL(p_calling_action, 'BOOKING') <> 'EXTERNAL'
1838 THEN
1839 IF p_calling_action <> 'BOOKING'
1840 AND NVL(p_credit_check_rule_rec.orders_on_hold_flag,'N') = 'N'
1841 AND p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG = 'Y'
1842 THEN
1843 -- add this IF condition for bug fix 4184524
1844 IF NVL(p_credit_check_rule_rec.uninvoiced_orders_flag,'N') = 'Y'
1845 THEN
1846 IF G_debug_flag = 'Y'
1847 THEN
1848 OE_DEBUG_PUB.Add(' Call Hold_Exists after get_exposure ');
1849 END IF;
1850 --
1851 IF Hold_Exists( p_header_id => p_header_id
1852 , p_line_id => NULL )
1853 THEN
1854 g_hold_exist := 'Y' ;
1855 ELSE
1856 g_hold_exist := 'N' ;
1857 END IF;
1858
1859 IF G_debug_flag = 'Y'
1860 THEN
1861 OE_DEBUG_PUB.Add(' after g_hold_exist => '|| g_hold_exist );
1862 END IF;
1863
1864 IF g_hold_exist = 'Y'
1865 THEN
1866 l_current_order_value :=
1867 NVL(OE_CREDIT_CHECK_UTIL.g_current_order_value,0) ;
1868 END IF;
1869 -- add this ELSIF for bug fix 4184524
1870 ELSIF NVL(p_credit_check_rule_rec.uninvoiced_orders_flag,'N') = 'N'
1871 THEN
1872 l_current_order_value :=
1873 NVL(OE_CREDIT_CHECK_UTIL.g_current_order_value,0) ;
1874 END IF; -- end of bug fix 4184524
1875 END IF;
1876 END IF;
1877
1878 IF G_debug_flag = 'Y'
1879 THEN
1880 OE_DEBUG_PUB.Add('l_current_order_value = '|| l_current_order_value );
1881 OE_DEBUG_PUB.Add('x_total_exposure before add = '|| x_total_exposure );
1882 END IF;
1883
1884 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1885 RAISE FND_API.G_EXC_ERROR;
1886 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1888 END IF;
1889 END IF; -- get exposure
1890 -- Add up the exposure and the current transaction amount
1891 x_total_exposure := NVL(l_transaction_value,0) +
1892 NVL(l_current_order_value,0) +
1893 NVL(x_total_exposure,0);
1894 END IF;
1895 ELSE
1896 x_total_exposure := 0 ;
1897 x_return_status := FND_API.G_RET_STS_SUCCESS;
1898 IF G_debug_flag = 'Y' THEN
1899 OE_DEBUG_PUB.Add(' UNLIMITED overall credi '
1900 || p_overall_credit_limit );
1901 END IF;
1902 END IF;
1903
1904 IF G_debug_flag = 'Y'
1905 THEN
1906 OE_DEBUG_PUB.Add('OEXVCRHB: x_total_exposure ' || x_total_exposure,1 );
1907 OE_DEBUG_PUB.Add('OEXVCRHB: p_overall_credit_limit '
1908 || p_overall_credit_limit,1 );
1909 OE_DEBUG_PUB.Add(' curr conv err count ' || l_conversion_status.COUNT );
1910 END IF;
1911
1912 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1913 RAISE FND_API.G_EXC_ERROR;
1914 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1916 END IF;
1917
1918 IF NVL(l_conversion_status.COUNT,0) > 0
1919 THEN
1920 IF G_debug_flag = 'Y' THEN
1921 OE_DEBUG_PUB.Add(' Currency conversion error ');
1922 END IF;
1923 x_cc_result_out := 'FAIL';
1924
1925
1926 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
1927 FND_MESSAGE.Set_Token('FROM',l_conversion_status(1).usage_curr_code );
1928 FND_MESSAGE.Set_Token('TO',p_limit_curr_code );
1929 FND_MESSAGE.Set_Token('CONV',
1930 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate')) ;
1931 OE_Credit_Engine_GRP.G_currency_error_msg :=
1932 SUBSTR(FND_MESSAGE.GET,1,1000) ;
1933
1934 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
1935 FND_MESSAGE.Set_Token('FROM',l_conversion_status(1).usage_curr_code );
1936 FND_MESSAGE.Set_Token('TO',p_limit_curr_code );
1937 FND_MESSAGE.Set_Token('CONV',
1938 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate')) ;
1939
1940 OE_MSG_PUB.ADD;
1941
1942 G_result_out := 'FAIL' ;
1943 x_cc_result_out := 'FAIL';
1944 x_return_status := FND_API.G_RET_STS_ERROR;
1945
1946 IF G_debug_flag = 'Y'
1947 THEN
1948 OE_DEBUG_PUB.Add(' Exposure CC Failed due to currency failure');
1949 OE_DEBUG_PUB.ADD('Return status after assigned as Error = '
1950 || x_return_status );
1951 END IF;
1952
1953 ELSE
1954 IF NVL(x_total_exposure,0) >
1955 NVL(p_overall_credit_limit,x_total_exposure)
1956 THEN
1957 x_cc_result_out := 'FAIL';
1958 ELSE
1959 x_cc_result_out := 'PASS';
1960 END IF;
1961 END IF;
1962
1963 IF G_debug_flag = 'Y'
1964 THEN
1965 OE_DEBUG_PUB.Add(' x_cc_result_out = ' || x_cc_result_out );
1966 OE_DEBUG_PUB.Add('OEXVCRHB: Out Check_order_Exposure ');
1967 END IF;
1968
1969 EXCEPTION
1970 WHEN others THEN
1971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1972 IF G_debug_flag = 'Y' THEN
1973 OE_DEBUG_PUB.Add('Check_order_exposure: Other exceptions');
1974 END IF;
1975 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1976 OE_MSG_PUB.Add_Exc_Msg
1977 ( G_PKG_NAME
1978 , 'Check_order_exposure'
1979 );
1980 END IF;
1981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1982 IF G_debug_flag = 'Y' THEN
1983 OE_DEBUG_PUB.Add( SUBSTR(SQLERRM,1,300) ) ;
1984 END IF;
1985 END Check_order_Exposure ;
1986
1987 -----------------------------------------------------------
1988 -- Check item category limits within the given site
1989 -- If credit check failed on any category, return failure
1990 -- and the category being checked.
1991 -----------------------------------------------------------
1992 PROCEDURE Check_Item_Limits
1993 ( p_header_rec IN OE_ORDER_PUB.header_rec_type
1994 , p_customer_id IN NUMBER
1995 , p_site_use_id IN NUMBER
1996 , p_calling_action IN VARCHAR2 DEFAULT 'BOOKING'
1997 , p_credit_check_rule_rec IN
1998 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
1999 , p_system_parameter_rec IN
2000 OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
2001 , x_cc_hold_comment OUT NOCOPY VARCHAR2
2002 , x_cc_result_out OUT NOCOPY VARCHAR2
2003 , x_return_status OUT NOCOPY VARCHAR2
2004 )
2005 IS
2006 l_category_sum NUMBER := 0 ;
2007 l_limit_category_sum NUMBER := 0 ; -- Sum converted to Limit currency
2008
2009 l_return_status VARCHAR2(30);
2010 l_include_tax_flag VARCHAR2(1) := 'Y';
2011 l_item_limits OE_CREDIT_CHECK_UTIL.item_limits_tbl_type;
2012 l_lines OE_CREDIT_CHECK_UTIL.lines_Rec_tbl_type;
2013 j BINARY_INTEGER := 1;
2014 i BINARY_INTEGER := 1;
2015 l_cc_result_out VARCHAR2(30);
2016 l_check_category_id NUMBER;
2017 l_limit_curr VARCHAR2(30);
2018 l_cc_hold_comment VARCHAR2(2000):= NULL;
2019
2020 BEGIN
2021 IF G_debug_flag = 'Y'
2022 THEN
2023 OE_DEBUG_PUB.Add('OEXVCRHB: In Check_Item_Limits');
2024 END IF;
2025 --
2026 -- Initialize return status to success
2027 x_return_status := FND_API.G_RET_STS_SUCCESS;
2028 -- Default to Pass
2029
2030 l_cc_result_out := 'PASS';
2031
2032 -- Need to use new get_item_limits api
2033 --
2034
2035 l_include_tax_flag := p_credit_check_rule_rec.include_tax_flag ;
2036
2037
2038 OE_CREDIT_CHECK_UTIL.Get_Item_Limit
2039 ( p_header_id => p_header_rec.header_id
2040 , p_include_tax_flag => p_credit_check_rule_rec.include_tax_flag
2041 , p_site_use_id => NULL
2042 , p_trx_curr_code => p_header_rec.transactional_curr_code
2043 , x_item_limits_tbl => l_item_limits
2044 , x_lines_tbl => l_lines
2045 );
2046
2047 IF l_item_limits.count = 0
2048 THEN
2049 x_cc_result_out := 'NOCHECK';
2050 IF G_debug_flag = 'Y'
2051 THEN
2052 OE_DEBUG_PUB.Add(' Count of category table = 0 ');
2053 END IF;
2054 ELSE
2055 IF G_debug_flag = 'Y'
2056 THEN
2057 OE_DEBUG_PUB.Add(' start category LOOP ');
2058 OE_DEBUG_PUB.Add(' ===================== ');
2059 END IF;
2060
2061 FOR i in 1..l_item_limits.count
2062 LOOP
2063 l_category_sum := 0;
2064 -- For each item category, sum the line values
2065
2066 IF G_debug_flag = 'Y'
2067 THEN
2068 OE_DEBUG_PUB.ADD(' ');
2069 OE_DEBUG_PUB.Add(' ------------------------------------ ');
2070 OE_DEBUG_PUB.Add(' Category id = '
2071 || l_item_limits(i).item_category_id );
2072 OE_DEBUG_PUB.Add(' ctg_line_amount = '
2073 || l_item_limits(i).ctg_line_amount );
2074 OE_DEBUG_PUB.Add(' limit_curr_code = '
2075 || l_item_limits(i).limit_curr_code );
2076 OE_DEBUG_PUB.Add(' item_limit = '
2077 || l_item_limits(i).item_limit );
2078 OE_DEBUG_PUB.Add(' grouping = '
2079 || l_item_limits(i).grouping_id );
2080 END IF;
2081
2082 l_category_sum := l_item_limits(i).ctg_line_amount ;
2083
2084 -- compare sum with category limit
2085 -- what to do when there is not limit
2086
2087 IF G_debug_flag = 'Y'
2088 THEN
2089 OE_DEBUG_PUB.Add(' l_category_sum = ' || l_category_sum );
2090 OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
2091 OE_Credit_Engine_GRP.GL_currency );
2092
2093 OE_DEBUG_PUB.Add(' ------------------------------------ ');
2094 OE_DEBUG_PUB.Add(' ');
2095 END IF;
2096
2097 l_check_category_id := l_item_limits(i).item_category_id ;
2098 l_limit_curr := l_item_limits(i).limit_curr_code ;
2099 l_limit_category_sum :=
2100 OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
2101 ( p_amount => l_category_sum
2102 , p_transactional_currency => p_header_rec.transactional_curr_code
2103 , p_limit_currency => l_item_limits(i).limit_curr_code
2104 , p_functional_currency => OE_Credit_Engine_GRP.GL_currency
2105 , p_conversion_date => SYSDATE
2106 , p_conversion_type => p_credit_check_rule_rec.conversion_type
2107 ) ;
2108
2109 IF G_debug_flag = 'Y'
2110 THEN
2111 OE_DEBUG_PUB.Add(' l_limit_category_sum = ' || l_limit_category_sum );
2112 OE_DEBUG_PUB.Add(' item_limit = ' || l_item_limits(i).item_limit );
2113 END IF;
2114
2115 IF l_limit_category_sum > l_item_limits(i).item_limit
2116 THEN
2117 IF G_debug_flag = 'Y'
2118 THEN
2119 OE_DEBUG_PUB.Add
2120 ('Fails item category ID: '|| l_item_limits(i).item_category_id);
2121 END IF;
2122 Apply_Order_CC_Hold
2123 ( p_header_id => p_header_rec.header_id
2124 , p_order_number => p_header_rec.order_number
2125 , p_calling_action => p_calling_action
2126 , p_cc_limit_used => 'ITEM'
2127 , p_cc_profile_used => 'CATEGORY'
2128 , p_item_category_id => l_item_limits(i).item_category_id
2129 , p_system_parameter_rec => p_system_parameter_rec
2130 , p_credit_check_rule_rec=> p_credit_check_rule_rec
2131 , x_cc_hold_comment => l_cc_hold_comment
2132 , x_cc_result_out => l_cc_result_out
2133 );
2134 EXIT; -- stop checking item limits
2135 END IF;
2136 l_limit_category_sum := 0 ;
2137 l_category_sum := 0 ;
2138 l_limit_curr := NULL ;
2139
2140 END LOOP; -- category loop
2141 x_cc_hold_comment := l_cc_hold_comment;
2142 x_cc_result_out := l_cc_result_out;
2143
2144 END IF;
2145
2146 IF G_debug_flag = 'Y'
2147 THEN
2148 OE_DEBUG_PUB.ADD('x_cc_result_out = '|| x_cc_result_out );
2149 OE_DEBUG_PUB.ADD('OEXVCRHB: Out Check_Item_Limit');
2150 END IF;
2151
2152 EXCEPTION
2153 WHEN GL_CURRENCY_API.NO_RATE
2154 THEN
2155 BEGIN
2156 IF G_debug_flag = 'Y' THEN
2157 OE_DEBUG_PUB.Add('EXCEPTION: GL_CURRENCY_API.NO_RATE ');
2158 OE_DEBUG_PUB.Add('Apply_Order_CC_Hold for Item category');
2159 OE_DEBUG_PUB.Add('currency = '|| p_header_rec.transactional_curr_code );
2160 OE_DEBUG_PUB.Add('checking category = '|| l_check_category_id );
2161 END IF;
2162
2163 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
2164 FND_MESSAGE.Set_Token('FROM',p_header_rec.transactional_curr_code);
2165 FND_MESSAGE.Set_Token('TO',l_limit_curr );
2166 FND_MESSAGE.Set_Token('CONV',
2167 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate')) ;
2168 OE_Credit_Engine_GRP.G_currency_error_msg :=
2169 SUBSTR(FND_MESSAGE.GET,1,1000) ;
2170
2171 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
2172 FND_MESSAGE.Set_Token('FROM',p_header_rec.transactional_curr_code);
2173 FND_MESSAGE.Set_Token('TO',l_limit_curr );
2174 FND_MESSAGE.Set_Token('CONV',
2175 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate')) ;
2176
2177 OE_MSG_PUB.ADD ;
2178
2179 G_result_out := 'FAIL' ;
2180 x_cc_result_out := 'FAIL';
2181 x_return_status := FND_API.G_RET_STS_ERROR;
2182
2183 IF G_debug_flag = 'Y' THEN
2184 OE_DEBUG_PUB.Add(' Item CTG CC Failed due to currency failure');
2185 OE_DEBUG_PUB.ADD('Return status after assigned as Error = '
2186 || x_return_status );
2187 END IF;
2188 END;
2189
2190 WHEN others THEN
2191 IF G_debug_flag = 'Y' THEN
2192 OE_DEBUG_PUB.Add('Check_Item_Limit: Other exceptions');
2193 END IF;
2194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2195 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2196 OE_MSG_PUB.Add_Exc_Msg
2197 ( G_PKG_NAME
2198 , 'Check_Item_Limits'
2199 );
2200 END IF;
2201 IF G_debug_flag = 'Y' THEN
2202 OE_DEBUG_PUB.Add( SUBSTR(SQLERRM,1,300),1 ) ;
2203 END IF;
2204 END Check_Item_Limits;
2205
2206 ------------------------------------------------------------
2207 -- PROCEDURE: Check_Other_Credit_Limits PRIVATE
2208 -- DESCRIPTION: Get additional credit limit information and
2209 -- perform credit check on customer/site/default
2210 -- credit limits specified in the customer/site
2211 -- or default credit profiles.
2212 ------------------------------------------------------------
2213 PROCEDURE Check_Other_Credit_Limits
2214 ( p_header_rec IN OE_ORDER_PUB.header_rec_type
2215 , p_customer_id IN NUMBER
2216 , p_site_use_id IN NUMBER
2217 , p_calling_action IN VARCHAR2 := 'BOOKING'
2218 , p_credit_check_rule_rec IN
2219 OE_Credit_Check_Util.OE_credit_rules_rec_type
2220 , p_system_parameter_rec IN
2221 OE_Credit_Check_Util.OE_systems_param_rec_type
2222 , p_transaction_amount IN NUMBER
2223 , p_party_id IN NUMBER ---------------------new (FPI)
2224 , x_credit_level OUT NOCOPY VARCHAR2
2225 , x_check_exposure_mode OUT NOCOPY VARCHAR2
2226 , x_cc_result_out OUT NOCOPY VARCHAR2
2227 , x_cc_hold_comment OUT NOCOPY VARCHAR2
2228 , x_return_status OUT NOCOPY VARCHAR2
2229 , x_global_exposure_flag OUT NOCOPY VARCHAR2
2230 )
2231 IS
2232
2233 l_check_order VARCHAR2(1);
2234 l_default_limit_flag VARCHAR2(1);
2235 l_limit_curr_code VARCHAR2(30);
2236 l_overall_credit_limit NUMBER;
2237 l_trx_credit_limit NUMBER;
2238 l_usage_curr OE_CREDIT_CHECK_UTIL.curr_tbl_type;
2239 l_include_all_flag VARCHAR2(1);
2240 l_prev_customer_id NUMBER;
2241 l_customer_result_out VARCHAR2(30) := NULL;
2242 l_total_exposure NUMBER;
2243 l_orders NUMBER;
2244 l_orders_on_hold NUMBER;
2245 l_payments_overdue NUMBER;
2246 l_payments_at_risk NUMBER;
2247 l_error_curr_tbl OE_CREDIT_CHECK_UTIL. curr_tbl_type ;
2248 l_cc_profile_used VARCHAR2(30);
2249 l_cc_limit_used VARCHAR2(80);
2250 l_cc_result_out VARCHAR2(30);
2251 l_request_id NUMBER; -----------------------new (FPI)
2252 l_msg_count NUMBER; -----------------------new (FPI)
2253 l_msg_data VARCHAR2(2000);-----------------------new (FPI)
2254 l_party_id NUMBER; -----------------------new (FPI)
2255 l_customer_id NUMBER; -----------------------new (FPI)
2256 l_site_use_id NUMBER; -----------------------new (FPI)
2257 l_source_org_id NUMBER; -----------------------new (FPI)
2258 l_source_user_id NUMBER; -----------------------new (FPI)
2259 l_source_resp_id NUMBER; -----------------------new (FPI)
2260 l_source_appln_id NUMBER; -----------------------new (FPI)
2261 l_source_security_group_id NUMBER; -----------------------new (FPI)
2262
2263 l_credit_limit_entity_id NUMBER ;
2264 l_support_party VARCHAR2(1) := 'N' ;
2265 l_cc_trx_result_out VARCHAR2(30);
2266 l_cc_duedate_result_out VARCHAR2(30);
2267 l_cc_overall_result_out VARCHAR2(30);
2268 ----Bug 4320650
2269 l_unrounded_exposure NUMBER;
2270 --bug 5907331
2271 l_review_party_id NUMBER;
2272
2273 BEGIN
2274
2275 IF G_debug_flag = 'Y'
2276 THEN
2277 OE_DEBUG_PUB.Add('OEXVCRHB: In Check_Other_Credit Limits',1);
2278 END IF;
2279
2280 --
2281 -- Set the default behavior to pass credit check
2282 --
2283 x_cc_result_out := 'PASS';
2284 x_return_status := FND_API.G_RET_STS_SUCCESS;
2285 x_global_exposure_flag := 'N' ;
2286
2287 l_cc_result_out := 'PASS';
2288 l_cc_trx_result_out := 'PASS';
2289 l_cc_duedate_result_out := 'PASS';
2290 l_cc_overall_result_out := 'PASS';
2291
2292 IF G_debug_flag = 'Y'
2293 THEN
2294 OE_DEBUG_PUB.Add(' ');
2295 OE_DEBUG_PUB.Add('Calling action = '|| p_calling_action);
2296 OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id);
2297 OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id );
2298 OE_DEBUG_PUB.Add('p_party_id => '|| p_party_id );
2299 OE_DEBUG_PUB.Add(' ');
2300 OE_DEBUG_PUB.Add('Calling Validate_other_credit_check');
2301 END IF;
2302 --
2303 -----------------------------------------------------------
2304 -- Check if order site use needs credit check. Also |
2305 -- determine if credit check should be at customer level |
2306 -- or the site level and the credit limits at that level. |
2307 -- The information returned will be used for credit check.|
2308 -----------------------------------------------------------
2309 --
2310 OE_credit_check_order_PVT.Validate_other_credit_check
2311 ( p_header_rec => p_header_rec
2312 , p_customer_id => p_customer_id
2313 , p_site_use_id => p_site_use_id
2314 , p_calling_action => p_calling_action
2315 , p_credit_check_rule_rec => p_credit_check_rule_rec
2316 , p_party_id => p_party_id
2317 , x_check_order_flag => l_check_order
2318 , x_credit_check_lvl_out => x_credit_level
2319 , x_default_limit_flag => l_default_limit_flag
2320 , x_limit_curr_code => l_limit_curr_code
2321 , x_overall_credit_limit => l_overall_credit_limit
2322 , x_trx_credit_limit => l_trx_credit_limit
2323 , x_usage_curr => l_usage_curr
2324 , x_include_all_flag => l_include_all_flag
2325 , x_return_status => x_return_status
2326 , x_global_exposure_flag => x_global_exposure_flag
2327 , x_credit_limit_entity_id => l_credit_limit_entity_id
2328 );
2329
2330 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2331 RAISE FND_API.G_EXC_ERROR;
2332 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2334 END IF;
2335 --
2336
2337 -----------------------------------------------------------
2338 -- Perform credit checks for due date, transaction limits,
2339 -- and overall limits.
2340 -----------------------------------------------------------
2341 IF l_check_order = 'Y' THEN
2342 --
2343 -- Determine the profile used
2344 --
2345 IF l_default_limit_flag = 'Y' THEN
2346 l_cc_profile_used := 'DEFAULT';
2347 ELSE
2348 l_cc_profile_used := x_credit_level ;
2349 END IF;
2350 --
2351 ----------------------------------------------------+
2352 -- order site use is subject to credit check: |
2353 ----------------------------------------------------|
2354 IF G_debug_flag = 'Y' THEN
2355 OE_DEBUG_PUB.Add('Calling Check Transaction Limit procedure');
2356 END IF;
2357
2358 OE_credit_check_order_PVT.Check_Trx_Limit
2359 ( p_header_rec => p_header_rec
2360 , p_customer_id => p_customer_id
2361 , p_site_use_id => p_site_use_id
2362 , p_credit_level => x_credit_level
2363 , p_credit_check_rule_rec => p_credit_check_rule_rec
2364 , p_system_parameter_rec => p_system_parameter_rec
2365 , p_limit_curr_code => l_limit_curr_code
2366 , p_trx_credit_limit => l_trx_credit_limit
2367 , p_calling_action => p_calling_action
2368 , p_transaction_amount => p_transaction_amount
2369 , x_cc_result_out => l_cc_trx_result_out
2370 , x_return_status => x_return_status
2371 , x_conversion_status => l_error_curr_tbl
2372 );
2373
2374 IF G_debug_flag = 'Y' THEN
2375 OE_DEBUG_PUB.Add('Check_Trx_Limit: Result Out ='||l_cc_trx_result_out);
2376 OE_DEBUG_PUB.Add('Check_Trx_Limit: Return Status ='|| x_return_status );
2377 END IF;
2378
2379 -- bug 4002820
2380 IF l_cc_trx_result_out = 'FAIL' THEN
2381 -- only overwrite the l_cc_result_out if the current checking fails
2382 -- to make sure the l_cc_result_out is FAIL if any of the checkings fails.
2383 l_cc_result_out := l_cc_trx_result_out;
2384 l_cc_limit_used := 'TRX';
2385 END IF;
2386
2387 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2388 RAISE FND_API.G_EXC_ERROR;
2389 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2390 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2391 END IF;
2392 g_order := OE_CREDIT_CHECK_UTIL.g_current_order_value ;
2393
2394
2395 -- IF l_cc_result_out = 'PASS' THEN
2396 -- Changed IF condition to fix bug 4002820, need to do overall
2397 -- limit checking even order limit checking failed when
2398 -- Credit Management is installed and used.
2399
2400 IF OE_CREDIT_CHECK_UTIL.G_crmgmt_installed is NULL
2401 THEN
2402 OE_CREDIT_CHECK_UTIL.G_crmgmt_installed :=
2403 AR_CMGT_CREDIT_REQUEST_API.is_Credit_Management_Installed ;
2404 END IF;
2405
2406 IF l_cc_trx_result_out = 'PASS'
2407 OR OE_CREDIT_CHECK_UTIL.G_crmgmt_installed = TRUE THEN
2408 IF G_debug_flag = 'Y' THEN
2409 OE_DEBUG_PUB.Add('Calling Check Past Due Invoice procedure');
2410 END IF;
2411
2412 OE_credit_check_order_PVT.Chk_Past_Due_Invoice
2413 ( p_customer_id => p_customer_id
2414 , p_site_use_id => p_site_use_id
2415 , p_party_id => l_credit_limit_entity_id
2416 , p_credit_check_rule_rec => p_credit_check_rule_rec
2417 , p_system_parameter_rec => p_system_parameter_rec
2418 , p_credit_level => x_credit_level
2419 , p_usage_curr => l_usage_curr
2420 , p_include_all_flag => l_include_all_flag
2421 , p_global_exposure_flag => x_global_exposure_flag
2422 , x_cc_result_out => l_cc_duedate_result_out
2423 , x_return_status => x_return_status
2424 );
2425
2426 IF G_debug_flag = 'Y' THEN
2427 OE_DEBUG_PUB.Add('Chk_Duedate_Limit: Result Out ='|| l_cc_duedate_result_out );
2428 OE_DEBUG_PUB.Add('Chk_Duedate_Limit: Return Status ='|| x_return_status );
2429 END IF;
2430
2431 -- bug 4002820
2432 IF l_cc_duedate_result_out = 'FAIL' THEN
2433 l_cc_result_out := l_cc_duedate_result_out;
2434 IF l_cc_limit_used IS NOT NULL THEN
2435 -- in order to disply useful message if two or more checkings fail.
2436 -- l_cc_limit_used := 'Order limit exceeded' || ', overdue invoices found';
2437 -- bug 4153299
2438 l_cc_limit_used := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'ORDER') || ', '
2439 || OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'OVERDUE');
2440
2441 ELSE
2442 l_cc_limit_used := 'DUEDATE';
2443 END IF;
2444 END IF;
2445
2446 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2447 RAISE FND_API.G_EXC_ERROR;
2448 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2450 END IF;
2451
2452 -- IF l_cc_result_out = 'PASS' THEN
2453 -- Changed IF condition to fix bug 4002813, need to do overall
2454 -- limit checking even order limit checking failed when
2455 -- Credit Management is installed and used.
2456 IF l_cc_duedate_result_out = 'PASS'
2457 OR OE_CREDIT_CHECK_UTIL.G_crmgmt_installed = TRUE THEN
2458 ----------------------------------------------------+
2459 -- order is subject to credit check: |
2460 ----------------------------------------------------|
2461 -- check 1: item limit <-- passed/failed|
2462 -- check 2: max-past-due-inv limit <-- passed |
2463 -- check 3: trx limit <-- passed |
2464 -- check 4: overall limit <-- in progress |
2465 ----------------------------------------------------+
2466
2467 x_check_exposure_mode := 'INLINE';
2468
2469 IF G_debug_flag = 'Y' THEN
2470 OE_DEBUG_PUB.Add('x_check_exposure_mode = '|| x_check_exposure_mode );
2471 OE_DEBUG_PUB.Add('Calling Check order Exposure procedure');
2472 END IF;
2473
2474 OE_credit_check_order_PVT.Check_order_exposure
2475 ( p_customer_id => p_customer_id
2476 , p_site_use_id => p_site_use_id
2477 , p_party_id => p_party_id
2478 , p_header_id => p_header_rec.header_id
2479 , p_credit_level => x_credit_level
2480 , p_transaction_curr_code => p_header_rec.transactional_curr_code
2481 , p_transaction_amount => p_transaction_amount
2482 , p_limit_curr_code => l_limit_curr_code
2483 , p_overall_credit_limit => l_overall_credit_limit
2484 , p_calling_action => p_calling_action
2485 , p_usage_curr => l_usage_curr
2486 , p_include_all_flag => l_include_all_flag
2487 , p_holds_rel_flag => 'N'
2488 , p_default_limit_flag => l_default_limit_flag
2489 , p_credit_check_rule_rec => p_credit_check_rule_rec
2490 , p_system_parameter_rec => p_system_parameter_rec
2491 , p_global_exposure_flag => x_global_exposure_flag
2492 , p_credit_limit_entity_id => l_credit_limit_entity_id
2493 , x_total_exposure => l_total_exposure
2494 , x_cc_result_out => l_cc_overall_result_out
2495 , x_error_curr_tbl => l_error_curr_tbl
2496 , x_return_status => x_return_status
2497 );
2498
2499 IF G_debug_flag = 'Y' THEN
2500 OE_DEBUG_PUB.Add('After call to Check_order_exposure ');
2501 OE_DEBUG_PUB.Add('l_cc_result_out = ' || l_cc_result_out );
2502 OE_DEBUG_PUB.Add('total exposure = ' || l_total_exposure );
2503 OE_DEBUG_PUB.Add('x_return_status = ' || x_return_status );
2504 OE_DEBUG_PUB.Add(' Credit Rule Id = '
2505 ||to_char(p_credit_check_rule_rec.credit_check_rule_id));
2506 END IF;
2507 --Bug 4320650
2508 l_unrounded_exposure := l_total_exposure;
2509
2510 OE_CREDIT_CHECK_UTIL.Rounded_Amount(l_limit_curr_code,
2511 l_unrounded_exposure,
2512 l_total_exposure);
2513
2514 -- bug 4002820
2515 IF l_cc_overall_result_out = 'FAIL' THEN
2516 l_cc_result_out := l_cc_overall_result_out;
2517 -- in order to disply useful message if two or more checkings fail.
2518 IF INSTR(l_cc_limit_used, ',') >0 THEN
2519 -- l_cc_limit_used := l_cc_limit_used || ', overall limit exceeded';
2520 --bug 4153299
2521 l_cc_limit_used := l_cc_limit_used || ', '
2522 || OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'OVERALL');
2523
2524 ELSIF l_cc_limit_used IS NOT NULL THEN
2525 IF l_cc_trx_result_out = 'FAIL' THEN
2526 -- l_cc_limit_used := 'Order limit, overall limit exceeded';
2527 -- bug 4153299
2528 l_cc_limit_used := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'ORDER') || ', '
2529 || OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'OVERALL');
2530
2531 ELSIF l_cc_duedate_result_out = 'FAIL' THEN
2532 -- l_cc_limit_used := 'Overdue invoices found'||', overall limit exceeded';
2533 -- bug 4153299
2534 l_cc_limit_used := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'OVERDUE') || ', '
2535 || OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'OVERALL');
2536
2537
2538 END IF;
2539 ELSE
2540 l_cc_limit_used := 'OVERALL';
2541 END IF;
2542 END IF;
2543
2544 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2545 RAISE FND_API.G_EXC_ERROR;
2546 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2548 END IF;
2549
2550 -- l_cc_limit_used := 'OVERALL';
2551
2552 ELSE
2553 l_cc_limit_used := 'DUEDATE';
2554 END IF;
2555 ELSE
2556 l_cc_limit_used := 'TRX';
2557 END IF;
2558 ELSE
2559 IF G_debug_flag = 'Y' THEN
2560 OE_DEBUG_PUB.Add('No credit check required');
2561 END IF;
2562 l_cc_result_out := 'NOCHECK';
2563 END IF;
2564 --
2565 -- Update database table with hold information
2566 --
2567
2568 --- BUG 2505245
2569 --- set the result to NO check if trx credit check amt = 0
2570 ---
2571
2572 IF G_debug_flag = 'Y'
2573 THEN
2574
2575 OE_DEBUG_PUB.Add(' OEXVCRHB: l_cc_limit_used ==> '|| l_cc_limit_used );
2576 OE_DEBUG_PUB.Add(' before l_cc_result_out => '|| l_cc_result_out );
2577
2578 OE_DEBUG_PUB.add(' g_current_order_value => '||
2579 OE_CREDIT_CHECK_UTIL.g_current_order_value );
2580
2581 OE_DEBUG_PUB.Add('before g_order ==> ' || g_order );
2582 END IF;
2583
2584 -- IF NVL(g_order,0) = 0
2585 IF (OE_OE_FORM_CANCEL_LINE.g_ord_lvl_can) --bug 3944617
2586 THEN
2587 l_cc_result_out := 'NOCHECK';
2588 END IF;
2589 -- g_order := NULL ;
2590 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110510' AND --bug#5887290
2591 OE_Sys_Parameters.Value('CREDIT_HOLD_ZERO_VALUE_ORDER') = 'N'
2592 THEN
2593 IF NVL(g_order,0) = 0 THEN
2594 l_cc_result_out := 'NOCHECK';
2595 END IF;
2596 END IF;
2597
2598
2599 IF G_debug_flag = 'Y'
2600 THEN
2601 OE_DEBUG_PUB.Add('after l_cc_result_out => '|| l_cc_result_out );
2602 OE_DEBUG_PUB.Add('after g_order ==> ' || g_order );
2603 END IF;
2604
2605 IF l_cc_result_out = 'FAIL' THEN
2606 IF NVL(p_calling_action,'BOOKING') <> 'EXTERNAL' THEN
2607 IF G_debug_flag = 'Y' THEN
2608 OE_DEBUG_PUB.Add('Calling Apply_Order_CC_Hold ');
2609 END IF;
2610
2611 -- bug 4153299
2612 -- 6616741 l_cc_profile_used := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_PROFILE', l_cc_profile_used);
2613
2614 Apply_Order_CC_Hold
2615 ( p_header_id => p_header_rec.header_id
2616 , p_order_number => p_header_rec.order_number
2617 , p_calling_action => p_calling_action
2618 , p_cc_limit_used => l_cc_limit_used
2619 , p_cc_profile_used => l_cc_profile_used
2620 , p_item_category_id => NULL
2621 , p_system_parameter_rec => p_system_parameter_rec
2622 , p_credit_check_rule_rec=> p_credit_check_rule_rec
2623 , x_cc_hold_comment => x_cc_hold_comment
2624 , x_cc_result_out => l_cc_result_out
2625 );
2626
2627 ---------------------- Start Credit Review --------------
2628
2629 IF l_cc_result_out in ('FAIL_HOLD','FAIL_NONE','FAIL')
2630 THEN
2631 -- changed IF condition for bug 4002820
2632 -- IF l_cc_limit_used = 'OVERALL'
2633 IF l_cc_overall_result_out = 'FAIL'
2634 THEN
2635 IF OE_CREDIT_CHECK_UTIL.G_crmgmt_installed is NULL
2636 THEN
2637 OE_CREDIT_CHECK_UTIL.G_crmgmt_installed :=
2638 AR_CMGT_CREDIT_REQUEST_API.is_Credit_Management_Installed ;
2639 END IF;
2640
2641 IF OE_CREDIT_CHECK_UTIL.G_crmgmt_installed = TRUE
2642 THEN
2643 --bug 5907331
2644 l_review_party_id := p_party_id;
2645 ------check if the credit check level is PARTY, CUSTOMER or SITE
2646 IF x_credit_level ='PARTY'
2647 THEN
2648 l_customer_id:=NULL;
2649 l_site_use_id:=NULL;
2650 --bug 5907331
2651 IF p_party_id <> nvl(l_credit_limit_entity_id,p_party_id) THEN
2652 l_review_party_id := l_credit_limit_entity_id;
2653 END IF;
2654
2655 ELSIF x_credit_level ='CUSTOMER'
2656 THEN
2657 l_customer_id:=p_customer_id;
2658 l_site_use_id:=NULL;
2659 ELSIF x_credit_level ='SITE'
2660 THEN
2661 l_customer_id:=p_customer_id;
2662 l_site_use_id:=p_site_use_id;
2663 END IF;
2664
2665 -------------get profile values:
2666 l_source_org_id := p_header_rec.org_id; /* MOAC ORG_ID CHANGE */
2667 -- l_source_org_id := FND_PROFILE.VALUE('ORG_ID');
2668 l_source_user_id := FND_PROFILE.VALUE ('USER_ID');
2669 l_source_resp_id := FND_PROFILE.VALUE ('RESP_ID');
2670 l_source_appln_id := FND_PROFILE.VALUE ('RESP_APPL_ID');
2671 l_source_security_group_id
2672 := FND_PROFILE.VALUE('SECURITY_GROUP_ID');
2673
2674 IF G_debug_flag = 'Y'
2675 THEN
2676 OE_DEBUG_PUB.Add('Calling Create_credit_request,
2677 credit check level= '||x_credit_level);
2678
2679 OE_DEBUG_PUB.Add('Parameters: ');
2680 OE_DEBUG_PUB.Add('-------------------------------------------');
2681 OE_DEBUG_PUB.Add('p_requestor_id= '||TO_CHAR(fnd_global.employee_id));
2682 OE_DEBUG_PUB.Add('p_review_type= ORDER_HOLD');
2683 OE_DEBUG_PUB.Add('p_credit_classification= NULL');
2684 OE_DEBUG_PUB.Add('p_requested_amount= '||TO_CHAR(l_total_exposure ));
2685 OE_DEBUG_PUB.Add('p_requested_currency= '||l_limit_curr_code);
2686 OE_DEBUG_PUB.Add('p_trx_amount= '||TO_CHAR(g_order));
2687 OE_DEBUG_PUB.Add('p_trx_currency= '||p_header_rec.transactional_curr_code );
2688 OE_DEBUG_PUB.Add('p_credit_type = TRADE' );
2689 OE_DEBUG_PUB.Add('p_term_length = NULL' );
2690 OE_DEBUG_PUB.Add('p_credit_check_rule_id= '||
2691 TO_CHAR(p_credit_check_rule_rec.credit_check_rule_id));
2692 OE_DEBUG_PUB.Add('p_credit_request_status = SUBMIT');
2693 OE_DEBUG_PUB.Add('p_party_id= '||TO_CHAR(p_party_id));
2694 OE_DEBUG_PUB.Add('p_cust_account_id= '||TO_CHAR(l_customer_id));
2695 OE_DEBUG_PUB.Add('p_cust_acct_site_id = NULL');
2696 OE_DEBUG_PUB.Add('p_site_use_id= '||TO_CHAR(l_site_use_id));
2697 OE_DEBUG_PUB.Add('p_contact_party_id = NULL');
2698 OE_DEBUG_PUB.Add('p_notes = NULL');
2699 OE_DEBUG_PUB.Add('p_source_org_id= '||TO_CHAR(l_source_org_id));
2700 OE_DEBUG_PUB.Add('p_source_user_id= '||TO_CHAR(l_source_user_id));
2701 OE_DEBUG_PUB.Add('p_source_resp_id= '||TO_CHAR(l_source_resp_id));
2702 OE_DEBUG_PUB.Add('p_source_appln_id= '||TO_CHAR(l_source_appln_id));
2703 OE_DEBUG_PUB.Add('p_source_security_group_id= '||TO_CHAR(l_source_security_group_id));
2704 OE_DEBUG_PUB.Add('p_source_name = OM');
2705 OE_DEBUG_PUB.Add('p_source_column1 = header_id= '||
2706 TO_CHAR(p_header_rec.header_id));
2707 OE_DEBUG_PUB.Add('p_source_column2 = order_number= '||
2708 TO_CHAR(p_header_rec.order_number));
2709 OE_DEBUG_PUB.Add('p_source_column3= ORDER');
2710
2711 END IF;
2712 ----------------Submit Credit Review--------------------
2713 AR_CMGT_CREDIT_REQUEST_API.Create_credit_request
2714 ( p_api_version => 1.0
2715 , p_init_msg_list => FND_API.G_FALSE
2716 , p_commit => FND_API.G_FALSE
2717 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2718 , x_return_status => x_return_status
2719 , x_msg_count => l_msg_count
2720 , x_msg_data => l_msg_data
2721 , p_application_number => NULL
2722 , p_application_date => SYSDATE
2723 , p_requestor_type => NULL
2724 , p_requestor_id => fnd_global.employee_id
2725 , p_review_type => 'ORDER_HOLD'
2726 , p_credit_classification => NULL
2727 , p_requested_amount => l_total_exposure
2728 , p_requested_currency => l_limit_curr_code
2729 , p_trx_amount => g_order
2730 , p_trx_currency => p_header_rec.transactional_curr_code
2731 , p_credit_type => 'TRADE'
2732 , p_term_length => NULL --the unit is no of months
2733 , p_credit_check_rule_id =>
2734 p_credit_check_rule_rec.credit_check_rule_id
2735 , p_credit_request_status => 'SUBMIT'
2736 , p_party_id => l_review_party_id --bug 5907331
2737 , p_cust_account_id => l_customer_id
2738 , p_cust_acct_site_id => NULL
2739 , p_site_use_id => l_site_use_id
2740 , p_contact_party_id => NULL --party_id of the pseudo party
2741 , p_notes => NULL --contact relationship.
2742 , p_source_org_id => l_source_org_id
2743 , p_source_user_id => l_source_user_id
2744 , p_source_resp_id => l_source_resp_id
2745 , p_source_appln_id => l_source_appln_id
2746 , p_source_security_group_id => l_source_security_group_id
2747 , p_source_name => 'OM'
2748 , p_source_column1 => p_header_rec.header_id
2749 , p_source_column2 => p_header_rec.order_number
2750 , p_source_column3 => 'ORDER'
2751 , p_credit_request_id => l_request_id
2752 );
2753
2754 IF x_return_status='S'
2755 THEN
2756
2757 FND_MESSAGE.Set_Name('ONT','OE_CC_CMGT_REVIEW');
2758 FND_MESSAGE.Set_Token('REQUEST_ID',l_request_id);
2759 OE_MSG_PUB.Add;
2760
2761 END IF;
2762
2763 IF G_debug_flag = 'Y'
2764 THEN
2765 IF x_return_status='S'
2766 THEN
2767
2768 OE_DEBUG_PUB.Add('Credit review submitted, request_id= '
2769 ||TO_CHAR(l_request_id));
2770 ELSE
2771 OE_DEBUG_PUB.Add('Credit review has not been submitted');
2772 END IF;
2773 END IF;
2774
2775 OE_DEBUG_PUB.Add('l_request_id= '||TO_CHAR(l_request_id));
2776 OE_DEBUG_PUB.Add('x_return_status= '||x_return_status);
2777 OE_DEBUG_PUB.Add('l_msg_count= '||TO_CHAR(l_msg_count));
2778 OE_DEBUG_PUB.Add('l_msg_data= '||l_msg_data);
2779
2780
2781 END IF;
2782
2783 END IF;
2784
2785 END IF;
2786 --------------------------------- End Credit review ---------
2787
2788 ELSE
2789 -- Create hold message for return to calling module.
2790 -- Also set the G_result_out variable to FAIL.
2791 G_result_out := 'FAIL';
2792 Write_Order_Hold_Msg
2793 (
2794 p_calling_action => p_calling_action
2795 , p_cc_limit_used => l_cc_limit_used
2796 , p_cc_profile_used => l_cc_profile_used
2797 , p_order_number => NULL
2798 , x_comment => x_cc_hold_comment
2799 );
2800 END IF;
2801 END IF;
2802 x_cc_result_out := l_cc_result_out;
2803 -- If no need to check order, then the non-item holds
2804 -- should be released.
2805 IF NVL(l_check_order,'N') = 'N' THEN
2806 x_check_exposure_mode := 'NOCHECK';
2807 END IF;
2808
2809 IF G_debug_flag = 'Y'
2810 THEN
2811 OE_DEBUG_PUB.Add('x_check_exposure_mode = ' || x_check_exposure_mode );
2812 OE_DEBUG_PUB.Add('x_cc_result_out = '|| x_cc_result_out );
2813 OE_DEBUG_PUB.Add('OEXVCRHB: Out Check_Other_Credit Limits');
2814 END IF;
2815
2816 EXCEPTION
2817 WHEN FND_API.G_EXC_ERROR THEN
2818 x_return_status := FND_API.G_RET_STS_ERROR;
2819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2820 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2821 WHEN OTHERS THEN
2822 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2823 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2824 OE_MSG_PUB.Add_Exc_Msg
2825 ( G_PKG_NAME, 'Check_Other_Credit_Limits');
2826 END IF;
2827 IF G_debug_flag = 'Y' THEN
2828 OE_DEBUG_PUB.Add( SUBSTR(SQLERRM,1,300),1 ) ;
2829 END IF;
2830 END Check_Other_Credit_Limits;
2831
2832 ------------------------------------------------+
2833 -- Mainline Function that will read an Order |
2834 -- Header and Determine if should be checked, |
2835 -- calculates total exposure, find credit |
2836 -- and determine result for calling function. |
2837 -------------------------------------------------
2838
2839 PROCEDURE Check_order_credit
2840 ( p_header_rec IN OE_ORDER_PUB.Header_Rec_Type
2841 , p_calling_action IN VARCHAR2 DEFAULT 'BOOKING'
2842 , p_credit_check_rule_rec IN OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
2843 , p_system_parameter_rec IN OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
2844 , p_transaction_amount IN NUMBER DEFAULT NULL
2845 , x_msg_count OUT NOCOPY NUMBER
2846 , x_msg_data OUT NOCOPY VARCHAR2
2847 , x_cc_result_out OUT NOCOPY VARCHAR2
2848 , x_cc_hold_comment OUT NOCOPY VARCHAR2
2849 , x_return_status OUT NOCOPY VARCHAR2
2850 ) IS
2851
2852 l_credit_level VARCHAR2(30); -- limits at cust or site level
2853 l_check_order VARCHAR2(1); -- if Order requires credit check
2854 l_check_exposure_mode VARCHAR2(20);
2855 l_cc_profile_used VARCHAR2(30) := NULL;
2856 l_cc_limit_used VARCHAR2(30) := NULL;
2857 l_msg_count NUMBER;
2858 l_msg_data VARCHAR2(2000);
2859 l_release_order_hold VARCHAR2(1) := 'Y';
2860 l_cc_result_out VARCHAR2(30);
2861 l_own_customer_id NUMBER;
2862 l_order_site_use_id NUMBER;
2863 l_check_order_eligible VARCHAR2(1);
2864 l_global_exposure_flag VARCHAR2(1);
2865 l_party_id NUMBER; ---------------------new (FPI)
2866 l_cc_hold_comment VARCHAR2(2000) := NULL;
2867
2868 BEGIN
2869 IF G_debug_flag = 'Y' THEN
2870 OE_DEBUG_PUB.Add('OEXVCRHB: In Check_order_credit API');
2871 END IF;
2872 --
2873 -- Set the default behavior to pass credit check
2874 --
2875 x_cc_result_out := 'NOCHECK';
2876 x_return_status := FND_API.G_RET_STS_SUCCESS;
2877 G_result_out := 'PASS' ;
2878 l_global_exposure_flag := 'N' ;
2879 g_hold_exist := NULL ;
2880 OE_Credit_Engine_GRP.G_currency_error_msg := NULL ;
2881 IF G_debug_flag = 'Y' THEN
2882 OE_DEBUG_PUB.Add( 'Initial start G_result_out = '|| G_result_out );
2883 OE_DEBUG_PUB.Add( 'Initial start G_currency_error_msg = '||
2884 substr(OE_Credit_Engine_GRP.G_currency_error_msg,1,10)) ;
2885 OE_DEBUG_PUB.Add(' ');
2886 OE_DEBUG_PUB.Add(' -------------------------------------------- ');
2887 OE_DEBUG_PUB.Add(' Calling action = '|| p_calling_action);
2888 OE_DEBUG_PUB.Add(' Transaction Amount = '|| p_transaction_amount);
2889 OE_DEBUG_PUB.Add(' Header ID = '|| p_header_rec.header_id );
2890 OE_DEBUG_PUB.Add(' Order Number = '|| p_header_rec.order_number );
2891 OE_DEBUG_PUB.Add(' Order currency = '
2892 || p_header_rec.transactional_curr_code );
2893 OE_DEBUG_PUB.Add(' sold_to_org = '
2894 || p_header_rec.sold_to_org_id );
2895 OE_DEBUG_PUB.Add(' Inv_to_org = '
2896 || p_header_rec.invoice_to_org_id );
2897 OE_DEBUG_PUB.Add(' Credit check level = '
2898 || p_credit_check_rule_rec.credit_check_level_code );
2899 OE_DEBUG_PUB.Add(' Conversion Type = '
2900 || p_credit_check_rule_rec.conversion_type );
2901 OE_DEBUG_PUB.Add(' User Conversion Type = '
2902 || p_credit_check_rule_rec.user_conversion_type );
2903 OE_DEBUG_PUB.Add(' Credit check rule id = '
2904 || p_credit_check_rule_rec.credit_check_rule_id );
2905 OE_DEBUG_PUB.Add(' CHECK_ITEM_CATEGORIES_FLAG = '
2906 || p_credit_check_rule_rec.CHECK_ITEM_CATEGORIES_FLAG );
2907 OE_DEBUG_PUB.Add(' SEND_HOLD_NOTIFICATIONS_FLAG = '
2908 || p_credit_check_rule_rec.SEND_HOLD_NOTIFICATIONS_FLAG );
2909 OE_DEBUG_PUB.Add('g_hold_exist => '|| g_hold_exist );
2910 OE_DEBUG_PUB.Add(' -------------------------------------------- ');
2911 OE_DEBUG_PUB.Add(' ');
2912 END IF;
2913 --
2914 -- Check payment term only if the calling action is not EXTERNAL
2915 --
2916 IF NVL(p_calling_action,'BOOKING') <> 'EXTERNAL' THEN
2917 IF G_debug_flag = 'Y' THEN
2918 OE_DEBUG_PUB.Add(' Call check_credit_check_flags ');
2919 END IF;
2920 l_check_order_eligible := check_credit_check_flags
2921 ( p_header_id => p_header_rec.header_id );
2922 ELSE
2923 l_check_order_eligible := 'Y';
2924 END IF;
2925
2926 IF G_debug_flag = 'Y' THEN
2927 OE_DEBUG_PUB.Add(' l_check_order_eligible = '|| l_check_order_eligible );
2928 END IF;
2929
2930 IF l_check_order_eligible = 'Y'
2931 THEN
2932 IF G_debug_flag = 'Y' THEN
2933 OE_DEBUG_PUB.Add('OEXVCRHB: Payment term YES CC checked , continue CC ');
2934 END IF;
2935
2936 SELECT /* MOAC_SQL_CHANGE */ cas.cust_account_id
2937 , su.site_use_id
2938 , ca.party_id --------------------new (FPI)
2939 INTO l_own_customer_id
2940 , l_order_site_use_id
2941 , l_party_id -------------------new (FPI)
2942 FROM HZ_CUST_SITE_USES_ALL su
2943 , HZ_CUST_ACCT_SITES_all cas
2944 , hz_cust_accounts_all ca --------------new (FPI)
2945 WHERE su.site_use_id = p_header_rec.invoice_to_org_id
2946 AND cas.CUST_ACCT_SITE_ID = su.CUST_ACCT_SITE_ID
2947 AND cas.cust_account_id=ca.cust_account_id; ---------new (FPI)
2948
2949 IF G_debug_flag = 'Y' THEN
2950 OE_DEBUG_PUB.Add('l_own_customer_id '|| l_own_customer_id );
2951 OE_DEBUG_PUB.Add('l_order_site_use_id '|| l_order_site_use_id);
2952 END IF;
2953
2954 ---------------------------------------------------+
2955 -- order is subject to credit check: |
2956 ---------------------------------------------------|
2957 -- check 1: item limit <-- in progress |
2958 -- check 2: max-past-due-inv limit |
2959 -- check 3: trx limit |
2960 -- check 4: overall limit |
2961 ---------------------------------------------------+
2962
2963 IF p_credit_check_rule_rec.CHECK_ITEM_CATEGORIES_FLAG = 'Y'
2964 THEN
2965 Check_Item_Limits
2966 ( p_header_rec => p_header_rec
2967 , p_customer_id => l_own_customer_id
2968 , p_site_use_id => l_order_site_use_id
2969 , p_calling_action => p_calling_action
2970 , p_credit_check_rule_rec => p_credit_check_rule_rec
2971 , p_system_parameter_rec => p_system_parameter_rec
2972 , x_cc_hold_comment => x_cc_hold_comment
2973 , x_cc_result_out => l_cc_result_out
2974 , x_return_status => x_return_status
2975 );
2976 IF G_debug_flag = 'Y' THEN
2977 OE_DEBUG_PUB.Add('Check_Item_Limit: Result Out = '
2978 || l_cc_result_out );
2979 OE_DEBUG_PUB.Add('Check_Item_Limit: Return Status = '
2980 || x_return_status );
2981 END IF;
2982 ELSE
2983 l_cc_result_out := 'PASS' ;
2984 x_return_status := FND_API.G_RET_STS_SUCCESS;
2985 IF G_debug_flag = 'Y' THEN
2986 OE_DEBUG_PUB.Add(' No Item category credit checking, flag OFF ');
2987 OE_DEBUG_PUB.Add('l_cc_result_out = '
2988 || l_cc_result_out );
2989 OE_DEBUG_PUB.Add('x_return_status = '
2990 || x_return_status );
2991 END IF;
2992 END IF;
2993
2994 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2995 RAISE FND_API.G_EXC_ERROR;
2996 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2998 END IF;
2999 --
3000 -- Apply the database hold and Exit the bill-to site loop if
3001 -- credit hold level is ORDER and the order failed credit check.
3002 --
3003 IF l_cc_result_out = 'FAIL' OR l_cc_result_out = 'FAIL_HOLD' OR
3004 l_cc_result_out = 'FAIL_NONE'
3005 THEN
3006 -- set the order hold release flag
3007 l_release_order_hold := 'N';
3008 IF G_debug_flag = 'Y' THEN
3009 OE_DEBUG_PUB.Add(' l_release_order_hold = '||l_release_order_hold );
3010 END IF;
3011 ELSE
3012 IF G_debug_flag = 'Y'
3013 THEN
3014 OE_DEBUG_PUB.Add('Item checking PASS, call Check_Other_Credit_Limits' );
3015 END IF;
3016
3017 ---------------------------------------------------+
3018 -- Check other credit limits for the bill-to site: |
3019 -- check 2: max-past-due-inv limit |
3020 -- check 3: trx limit |
3021 -- check 4: overall limit |
3022 ---------------------------------------------------+
3023 Check_Other_Credit_Limits
3024 ( p_header_rec => p_header_rec
3025 , p_party_id => l_party_id -----------new (FPI)
3026 , p_customer_id => l_own_customer_id
3027 , p_site_use_id => l_order_site_use_id
3028 , p_calling_action => p_calling_action
3029 , p_credit_check_rule_rec => p_credit_check_rule_rec
3030 , p_system_parameter_rec => p_system_parameter_rec
3031 , p_transaction_amount => p_transaction_amount
3032 , x_credit_level => l_credit_level
3033 , x_check_exposure_mode => l_check_exposure_mode
3034 , x_cc_result_out => l_cc_result_out
3035 , x_cc_hold_comment => x_cc_hold_comment
3036 , x_return_status => x_return_status
3037 , x_global_exposure_flag => l_global_exposure_flag
3038 );
3039
3040 IF G_debug_flag = 'Y'
3041 THEN
3042 OE_DEBUG_PUB.Add('Check_Other_Credit_Limits: Result Out = '
3043 || l_cc_result_out );
3044 OE_DEBUG_PUB.Add('l_check_exposure_mode = '||
3045 l_check_exposure_mode );
3046 OE_DEBUG_PUB.Add('l_credit_level = '||
3047 l_credit_level );
3048 OE_DEBUG_PUB.Add('x_cc_hold_comment = '||
3049 x_cc_hold_comment );
3050 OE_DEBUG_PUB.Add('Check_Other_Credit_Limits: Return Status = '
3051 || x_return_status );
3052 END IF;
3053
3054 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3055 RAISE FND_API.G_EXC_ERROR;
3056 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3058 END IF;
3059
3060 -- Apply order level credit hold to the database if it necessary
3061
3062 IF l_cc_result_out = 'FAIL'
3063 OR l_cc_result_out = 'FAIL_HOLD' OR
3064 l_cc_result_out = 'FAIL_NONE'
3065 THEN
3066 -- set the order hold release flag
3067 l_release_order_hold := 'N';
3068 IF G_debug_flag = 'Y' THEN
3069 OE_DEBUG_PUB.Add(' l_release_order_hold = '||l_release_order_hold );
3070 END IF;
3071 END IF;
3072
3073 END IF; -- else item checking
3074
3075 IF l_cc_result_out = 'NOCHECK' THEN
3076 x_cc_result_out := 'NOCHECK';
3077 ELSE
3078 x_cc_result_out := l_cc_result_out ;
3079 END IF;
3080
3081 IF G_debug_flag = 'Y'
3082 THEN
3083 OE_DEBUG_PUB.Add('OEXVCRHB: Check_order_credit Results');
3084 OE_DEBUG_PUB.Add('l_release_order_hold : '|| l_release_order_hold );
3085 OE_DEBUG_PUB.Add('l_cc_result_out : '|| l_cc_result_out );
3086 END IF;
3087
3088 ELSE
3089 x_cc_result_out := 'NOCHECK';
3090 l_release_order_hold := 'Y' ;
3091
3092 IF G_debug_flag = 'Y'
3093 THEN
3094 OE_DEBUG_PUB.Add('OEXVCRHB: Payment term NO CC checked ');
3095 END IF;
3096
3097 END IF; -- payment term check
3098
3099 IF G_debug_flag = 'Y'
3100 THEN
3101 OE_DEBUG_PUB.Add(' l_release_order_hold = '|| l_release_order_hold );
3102 OE_DEBUG_PUB.Add(' x_cc_result_out = ' || x_cc_result_out );
3103 END IF;
3104 --
3105 -- Also check calling action to release hold only when action not EXTERNAL
3106 --
3107 IF l_release_order_hold = 'Y' AND
3108 NVL(p_calling_action, 'BOOKING') <> 'EXTERNAL'
3109 -- AND l_cc_result_out <> 'NOCHECK'
3110 THEN
3111 IF G_debug_flag = 'Y'
3112 THEN
3113 OE_DEBUG_PUB.Add(' CALL Release_Order_CC_Hold ' );
3114 END IF;
3115
3116 Release_Order_CC_Hold
3117 ( p_header_id => p_header_rec.header_id
3118 , p_order_number => p_header_rec.order_number
3119 , p_calling_action => p_calling_action
3120 , p_system_parameter_rec => p_system_parameter_rec
3121 , x_cc_result_out => l_cc_result_out
3122 );
3123
3124 IF G_debug_flag = 'Y'
3125 THEN
3126 OE_DEBUG_PUB.Add(' Release_Order_CC_Hold : Result Out = '
3127 || l_cc_result_out );
3128 END IF;
3129
3130 END IF;
3131
3132 -- Bug 4506263 FP
3133 -- x_cc_result_out := G_result_out ;
3134 x_cc_result_out := l_cc_result_out;
3135
3136 g_hold_exist := NULL ;
3137
3138 IF G_debug_flag = 'Y'
3139 THEN
3140 OE_DEBUG_PUB.Add(' l_cc_result_out = '|| l_cc_result_out );
3141 OE_DEBUG_PUB.Add(' G_result_out = ' || G_result_out );
3142 OE_DEBUG_PUB.Add(' final x_cc_result_out = '|| x_cc_result_out );
3143 OE_DEBUG_PUB.Add('OEXVCRHB: Out Check_order_credit API');
3144 END IF;
3145
3146 EXCEPTION
3147 WHEN FND_API.G_EXC_ERROR THEN
3148 IF G_debug_flag = 'Y' THEN
3149 OE_DEBUG_PUB.Add('Check_order_credit: Error ');
3150 END IF;
3151 x_return_status := FND_API.G_RET_STS_ERROR;
3152 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3153 IF G_debug_flag = 'Y' THEN
3154 OE_DEBUG_PUB.Add('Check_order_credit: Unexpected Error ');
3155 END IF;
3156 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3157 WHEN OTHERS THEN
3158 IF G_debug_flag = 'Y' THEN
3159 OE_DEBUG_PUB.Add('Check_order_credit: Other Unexpected Error ');
3160 END IF;
3161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3162 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3163 OE_MSG_PUB.Add_Exc_Msg
3164 ( G_PKG_NAME
3165 , 'Check_order_credit'
3166 );
3167 END IF;
3168 IF G_debug_flag = 'Y' THEN
3169 OE_DEBUG_PUB.Add( SUBSTR(SQLERRM,1,300),1 ) ;
3170 END IF;
3171 OE_MSG_PUB.Count_And_Get
3172 ( p_count => x_msg_count
3173 , p_data => x_msg_data
3174 );
3175
3176 END Check_order_credit;
3177
3178
3179 END OE_credit_check_order_PVT ;