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