[Home] [Help]
PACKAGE BODY: APPS.OE_CREDIT_ENGINE_GRP
Source
1 PACKAGE BODY OE_Credit_Engine_GRP AS
2 -- $Header: OEXPCRGB.pls 120.15 2012/01/04 06:48:15 slagiset ship $
3 --+=======================================================================+
4 --| Copyright (c) 2001 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| |
9 --| FILENAME |
10 --| OEXPCRGB.pls |
11 --| |
12 --| DESCRIPTION |
13 --| Body of package OE_Credit_Engine_GRP |
14 --| |
15 --| NOTES |
16 --| This package body contains the procedures that will be |
17 --| used to call Order Specific Payment Verification process |
18 --| |
19 --| HISTORY |
20 --| 26-SEP-2001 INGERSOL BUG |
21 --| 05-FEB-2002 multi org |
22 --| ontdev=> OEXPCRGB.pls 115.17 2001/09/27 22:58:05 |
23 --| 05-FEB-2002 rajkrish 1PM |
24 --| 18-FEB-2002 External Credit Checking change |
25 --| 19-MAR-2002 Modified Check_Credit for external credit checking |
26 --| 12-JUN-2002 rajkrish 2412678 |
27 --| 12-NOV-2002 |
28 --| 31-MAR-2003 vto 2846473,2878410. Changed call to |
29 --| Send_Credit_Hold_NTF |
30 --| 01-APR-2003 vto 2853800. Use globals for activity cc holds |
31 --| G_cc_hold_activity_name |
32 --| G_cc_hold_item_type |
33 --| Added Set_G_CC_Hold_info procedure. |
34 --| 15-MAY-2003 vto 2894424, 2971689. New cc calling action: |
35 --| AUTO HOLD, AUTO RELEASE. |
36 --| Obsolete calling action: AUTO |
37 --+=======================================================================+
38
39 --=========================================================================
40 -- CONSTANTS
41 --=========================================================================
42 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Credit_Engine_GRP';
43
44 --=========================================================================
45 -- PRIVATE GLOBAL VARIABLES
46 --=========================================================================
47 G_debug_flag VARCHAR2(1) := NVL(OE_CREDIT_CHECK_UTIL.check_debug_flag ,'N');
48
49 --=========================================================================
50 -- PROCEDURES AND FUNCTIONS
51 --=========================================================================
52
53 ---------------------------------------------------------------------------
54 -- PROCEDURE: Set_G_CC_Hold_Info
55 -- COMMENT: Set the values for the G_cc_hold_item_type and
56 -- G_cc_hold_activity_name global variables.
57 ---------------------------------------------------------------------------
58 PROCEDURE Set_G_CC_Hold_Info
59 IS
60 BEGIN
61 IF G_debug_flag = 'Y' THEN
62 OE_DEBUG_PUB.ADD('OEXPCRGB: In Set_G_CC_Hold_Info', 1);
63 END IF;
64
65 SELECT item_type,
66 activity_name
67 INTO G_cc_hold_item_type,
68 G_cc_hold_activity_name
69 FROM oe_hold_definitions
70 WHERE hold_id = 1;
71
72 IF G_debug_flag = 'Y' THEN
73 OE_DEBUG_PUB.ADD('G_cc_hold_item_type = '||G_cc_hold_item_type, 2);
74 OE_DEBUG_PUB.ADD('G_cc_hold_activity_name = '||G_cc_hold_activity_name, 2);
75 OE_DEBUG_PUB.ADD('OEXPCRGB: Out Set_G_CC_Hold_Info', 1);
76 END IF;
77 EXCEPTION
78 WHEN NO_DATA_FOUND THEN
79 -- Hold definition should exist. This exception should not occur if setup is correct.
80 -- Ideally, an error should be raised indicating no cc hold definition found instead
81 -- of unexpected error.
82 IF G_debug_flag = 'Y' THEN
83 OE_DEBUG_PUB.ADD('EXCEPTION: No credit check hold definition found.', 1);
84 END IF;
85 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
86 OE_MSG_PUB.Add_Exc_Msg
87 ( G_PKG_NAME
88 , 'Set_G_CC_Hold_Info'
89 );
90 END IF;
91 RAISE;
92 WHEN OTHERS THEN
93 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
94 OE_MSG_PUB.Add_Exc_Msg
95 ( G_PKG_NAME
96 , 'Set_G_CC_Hold_Info'
97 );
98 END IF;
99 RAISE;
100 END Set_G_CC_Hold_Info;
101
102 ---------------------------------------------------------------------------
103 --PROCEDURE: Get_Credit_Check_Rule_ID
104 --COMMENT: Returns the credit check rule id attached with
105 -- the order trn type
106 ---------------------------------------------------------------------------
107 PROCEDURE Get_Credit_Check_Rule_ID
108 ( p_calling_action IN VARCHAR2
109 , p_order_type_id IN OE_ORDER_HEADERS.order_type_id%TYPE
110 , x_credit_rule_id OUT NOCOPY /* file.sql.39 change */ OE_Credit_check_rules.credit_check_rule_id%TYPE
111 )
112 IS
113 BEGIN
114 IF G_debug_flag = 'Y'
115 THEN
116 OE_DEBUG_PUB.ADD('OEXPCRGB: In Get_Credit_Check_Rule_ID ');
117 OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
118 OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
119 END IF;
120 x_credit_rule_id := NULL ;
121
122 IF p_calling_action in ('BOOKING','BOOKING_INLINE','UPDATE',
123 'AUTO HOLD', 'AUTO RELEASE')
124 THEN
125
126 /*7194250
127 SELECT ENTRY_CREDIT_CHECK_RULE_ID
128 INTO x_credit_rule_id
129 FROM OE_ORDER_TYPES_V
130 WHERE ORDER_TYPE_ID = p_order_type_id;
131 7194250*/
132 --7194250
133 SELECT ENTRY_CREDIT_CHECK_RULE_ID
134 INTO x_credit_rule_id
135 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
136 WHERE ORDER_TYPE_ID = p_order_type_id
137 AND ENTRY_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
138 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
139 --7194250
140
141 OE_Verify_Payment_PUB.G_credit_check_rule := 'Ordering'; --ER#7479609
142
143 ELSIF p_calling_action = 'SHIPPING'
144 THEN
145 /*7194250
146 SELECT SHIPPING_CREDIT_CHECK_RULE_ID
147 INTO x_credit_rule_id
148 FROM OE_ORDER_TYPES_V
149 WHERE ORDER_TYPE_ID = p_order_type_id;
150 7194250*/
151 --7194250
152 SELECT SHIPPING_CREDIT_CHECK_RULE_ID
153 INTO x_credit_rule_id
154 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
155 WHERE ORDER_TYPE_ID = p_order_type_id
156 AND SHIPPING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
157 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
158 --7194250
159
160 OE_Verify_Payment_PUB.G_credit_check_rule := 'Shipping'; --ER#7479609
161
162 ELSIF p_calling_action = 'PACKING'
163 THEN
164 /*7194250
165 SELECT PACKING_CREDIT_CHECK_RULE_ID
166 INTO x_credit_rule_id
167 FROM OE_ORDER_TYPES_V
168 WHERE ORDER_TYPE_ID = p_order_type_id;
169 7194250*/
170 --7194250
171 SELECT PACKING_CREDIT_CHECK_RULE_ID
172 INTO x_credit_rule_id
173 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
174 WHERE ORDER_TYPE_ID = p_order_type_id
175 AND PACKING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
176 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
177 --7194250
178
179 OE_Verify_Payment_PUB.G_credit_check_rule := 'Packing'; --ER#7479609
180
181 ELSIF p_calling_action = 'PICKING'
182 THEN
183 /*7194250
184 SELECT PICKING_CREDIT_CHECK_RULE_ID
185 INTO x_credit_rule_id
186 FROM OE_ORDER_TYPES_V
187 WHERE ORDER_TYPE_ID = p_order_type_id;
188 7194250*/
189 --7194250
190 SELECT PICKING_CREDIT_CHECK_RULE_ID
191 INTO x_credit_rule_id
192 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
193 WHERE ORDER_TYPE_ID = p_order_type_id
194 AND PICKING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
195 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
196 --7194250
197
198 OE_Verify_Payment_PUB.G_credit_check_rule := 'Picking/Purchase Release'; --ER#7479609
199
200 END IF;
201
202 IF G_debug_flag = 'Y'
203 THEN
204 OE_DEBUG_PUB.ADD('OEXPCRGB: Credit Check Rule ID: '
205 ||TO_CHAR(x_credit_rule_id) );
206
207 OE_DEBUG_PUB.ADD('OEXPCRGB: Out Get_Credit_Check_Rule_ID');
208 END IF;
209
210 EXCEPTION
211 WHEN NO_DATA_FOUND
212 THEN
213 x_credit_rule_id := NULL ;
214 OE_DEBUG_PUB.ADD('EXCEPTION:No credit check rule found');
215 WHEN OTHERS THEN
216 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
217 OE_MSG_PUB.Add_Exc_Msg
218 ( G_PKG_NAME
219 , 'Get_Credit_Check_Rule_ID'
220 );
221 END IF;
222 RAISE ;
223 END Get_Credit_Check_Rule_ID ;
224
225 --ER 12363706 start
226 FUNCTION Is_Tolerance_Enabled
227 ( p_header_id IN NUMBER,
228 p_credit_check_rule_rec OUT NOCOPY OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
229 )
230 RETURN BOOLEAN
231 IS
232 l_credit_check_rule_id NUMBER;
233 l_credit_check_rule_rec OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type ;
234 l_header_rec OE_Order_PUB.Header_Rec_Type;
235 l_released_order_amount NUMBER;
236 l_curr_order_amount NUMBER;
237 l_tolerance_percentage NUMBER;
238 l_tolerance_curr_code VARCHAR2(30);
239 l_tolerance_amount NUMBER;
240 l_tolerance_amount_conv NUMBER;
241 l_tolerance_amount_per NUMBER;
242 l_tolerance_amount_fin NUMBER;
243 l_calling_action VARCHAR2(30);
244 BEGIN
245 IF G_debug_flag = 'Y'
246 THEN
247 OE_DEBUG_PUB.Add('OEXPCRGB: In Is_Tolerance_Enabled ');
248 END IF;
249
250 l_calling_action := OE_Verify_Payment_PUB.Which_Rule(p_header_id => p_header_id);
251
252 OE_HEADER_UTIL.QUERY_ROW ( p_header_id => p_header_id,
253 x_header_rec => l_header_rec);
254
255 Get_Credit_Check_Rule_ID
256 ( p_calling_action => l_calling_action
257 , p_order_type_id => l_header_rec.order_type_id
258 , x_credit_rule_id => l_credit_check_rule_id
259 );
260
261
262 OE_CREDIT_CHECK_UTIL.GET_credit_check_rule
263 ( p_header_id => p_header_id
264 , p_credit_check_rule_id => l_credit_check_rule_id
265 , x_credit_check_rules_rec => l_credit_check_rule_rec
266 );
267
268 p_credit_check_rule_rec := l_credit_check_rule_rec;
269
270
271 IF l_credit_check_rule_rec.tolerance_percentage IS NULL and l_credit_check_rule_rec.tolerance_amount IS NULL
272 THEN
273 RETURN FALSE;
274 ELSE
275 RETURN TRUE;
276 END IF;
277
278
279 IF G_debug_flag = 'Y'
280 THEN
281 OE_DEBUG_PUB.Add('OEXPCRGB: Out Is_Tolerance_Enabled ');
282 END IF;
283
284 EXCEPTION
285
286 WHEN OTHERS THEN
287 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
288 OE_MSG_PUB.Add_Exc_Msg
289 ( G_PKG_NAME
290 , 'Credit_Tolerance_Check'
291 );
292 END IF;
293 OE_DEBUG_PUB.ADD( SUBSTR( SQLERRM,1,300) ,1 ) ;
294
295
296 END Is_Tolerance_Enabled ;
297
298 FUNCTION Credit_Tolerance_Check
299 ( p_header_id IN NUMBER
300 )
301 RETURN BOOLEAN
302 IS
303 l_credit_check_rule_id NUMBER;
304 l_credit_check_rule_rec OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type ;
305 l_header_rec OE_Order_PUB.Header_Rec_Type;
306 l_released_order_amount NUMBER;
307 l_released_curr_code VARCHAR2(15);
308 l_released_order_amount_conv NUMBER;
309 l_curr_order_amount NUMBER;
310 l_tolerance_amount_conv NUMBER;
311 l_tolerance_amount_per NUMBER;
312 l_tolerance_amount_fin NUMBER;
313 l_return_status VARCHAR2(30);
314 l_conversion_status OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
315
316 CURSOR res_det IS
317 SELECT OHR.Released_Order_Amount,OHR.Released_Curr_code
318 FROM OE_ORDER_HOLDS OOH,
319 OE_HOLD_SOURCES_ALL OHS,
320 OE_HOLD_RELEASES OHR
321 WHERE OOH.HOLD_SOURCE_ID = OHS.HOLD_SOURCE_ID
322 AND OOH.HEADER_ID = p_header_id
323 AND OOH.HOLD_RELEASE_ID IS NOT NULL
324 AND OHS.HOLD_ID = 1
325 AND OHS.HOLD_ENTITY_CODE = 'O'
326 AND OHS.HOLD_ENTITY_ID = p_header_id
327 AND OHS.RELEASED_FLAG ='Y'
328 AND OHR.HOLD_RELEASE_ID = OOH.HOLD_RELEASE_ID
329 ORDER BY OHR.creation_date DESC;
330
331 BEGIN
332 IF G_debug_flag = 'Y'
333 THEN
334 OE_DEBUG_PUB.Add('OEXPCRGB: In Credit_Tolerance_Check ');
335 END IF;
336
337 IF Is_Tolerance_Enabled(p_header_id,l_credit_check_rule_rec) THEN
338
339 OE_HEADER_UTIL.QUERY_ROW ( p_header_id => p_header_id,
340 x_header_rec => l_header_rec);
341
342 l_credit_check_rule_rec.credit_check_level_code := 'ORDER';
343
344 OE_CREDIT_CHECK_UTIL.GET_transaction_amount
345 ( p_header_id => p_header_id
346 , p_transaction_curr_code => l_header_rec.transactional_curr_code
347 , p_credit_check_rule_rec => l_credit_check_rule_rec
348 , p_system_parameter_rec => NULL
349 , p_customer_id => NULL
350 , p_site_use_id => NULL
351 , p_limit_curr_code => l_header_rec.transactional_curr_code
352 , p_all_lines => 'Y'
353 , x_amount => l_curr_order_amount
354 , x_conversion_status => l_conversion_status
355 , x_return_status => l_return_status
356 );
357
358 OPEN res_det;
359 FETCH res_det INTO l_released_order_amount,l_released_curr_code;
360 CLOSE res_det;
361
362 IF l_released_curr_code <> l_header_rec.transactional_curr_code
363 THEN
364 l_released_order_amount_conv :=
365 OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
366 ( p_amount => l_released_order_amount
367 , p_transactional_currency => l_released_curr_code
368 , p_limit_currency => l_header_rec.transactional_curr_code
369 , p_functional_currency => OE_Credit_Engine_GRP.GL_currency
370 , p_conversion_date => SYSDATE
371 , p_conversion_type => l_credit_check_rule_rec.conversion_type
372 );
373 ELSE
374 OE_DEBUG_PUB.Add('OEXPCRGB: Currency conversion not required. Released and Transactional currency is same');
375 l_released_order_amount_conv := l_released_order_amount;
376 END IF;
377
378 /***If No Tolerance Defined , the Credit Checking will always be triggered**/
379 IF l_credit_check_rule_rec.tolerance_percentage IS NULL and l_credit_check_rule_rec.tolerance_amount IS NULL
380 THEN
381 OE_DEBUG_PUB.Add('OEXPCRGB: Tolerance Check Not Required');
382 RETURN FALSE;
383 END IF;
384
385 IF l_credit_check_rule_rec.tolerance_amount IS NOT NULL
386 THEN
387
388 IF l_credit_check_rule_rec.tolerance_curr_code <> l_header_rec.transactional_curr_code
389 THEN
390 l_tolerance_amount_conv :=
391 OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
392 ( p_amount => l_credit_check_rule_rec.tolerance_amount
393 , p_transactional_currency => l_credit_check_rule_rec.tolerance_curr_code
394 , p_limit_currency => l_header_rec.transactional_curr_code
395 , p_functional_currency => OE_Credit_Engine_GRP.GL_currency
396 , p_conversion_date => SYSDATE
397 , p_conversion_type => l_credit_check_rule_rec.conversion_type
398 );
399 ELSE
400 OE_DEBUG_PUB.Add('OEXPCRGB: Currency conversion not required. Tolerance and Transactional currency is same');
401 l_tolerance_amount_conv := l_credit_check_rule_rec.tolerance_amount;
402 END IF;
403 END IF;
404
405 IF l_credit_check_rule_rec.tolerance_percentage IS NOT NULL
406 THEN
407 l_tolerance_amount_per := l_released_order_amount_conv * (l_credit_check_rule_rec.tolerance_percentage/100);
408 END IF;
409
410 IF l_credit_check_rule_rec.tolerance_amount IS NOT NULL AND l_credit_check_rule_rec.tolerance_percentage IS NULL
411 THEN
412
413 l_tolerance_amount_fin := l_tolerance_amount_conv;
414
415 ELSIF l_credit_check_rule_rec.tolerance_amount IS NULL AND l_credit_check_rule_rec.tolerance_percentage IS NOT NULL
416 THEN
417
418 l_tolerance_amount_fin := l_tolerance_amount_per;
419
420 ELSE
421 IF l_tolerance_amount_per < l_tolerance_amount_conv
422 THEN
423 l_tolerance_amount_fin := l_tolerance_amount_per;
424 ELSE
425 l_tolerance_amount_fin := l_tolerance_amount_conv;
426 END IF;
427 END IF;
428
429 IF G_debug_flag = 'Y'
430 THEN
431 OE_DEBUG_PUB.Add('OEXPCRGB: l_released_order_amount_conv: ' ||l_released_order_amount_conv);
432 OE_DEBUG_PUB.Add('OEXPCRGB: l_tolerance_amount_fin: ' ||l_tolerance_amount_fin);
433 OE_DEBUG_PUB.Add('OEXPCRGB: l_curr_order_amount: ' ||l_curr_order_amount);
434 END IF;
435
436 IF l_curr_order_amount <= (l_released_order_amount + l_tolerance_amount_fin)
437 THEN
438 OE_DEBUG_PUB.Add('OEXPCRGB: Tolerance Check Passed');
439 RETURN TRUE;
440 ELSE
441 OE_DEBUG_PUB.Add('OEXPCRGB: Tolerance Check Failed');
442 RETURN FALSE;
443 END IF;
444 END IF;
445
446
447
448 RETURN FALSE;
449 IF G_debug_flag = 'Y'
450 THEN
451 OE_DEBUG_PUB.Add('OEXPCRGB: Out Credit_Tolerance_Check ');
452 END IF;
453
454 EXCEPTION
455
456 WHEN OTHERS THEN
457 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
458 OE_MSG_PUB.Add_Exc_Msg
459 ( G_PKG_NAME
460 , 'Credit_Tolerance_Check'
461 );
462 END IF;
463 OE_DEBUG_PUB.ADD( SUBSTR( SQLERRM,1,300) ,1 ) ;
464
465
466 END Credit_Tolerance_Check ;
467 --ER 12363706 end
468
469
470 ---------------------------------------------------------------------
471 -- PROCEDURE: Apply_exception_hold
472 -- COMMENT: Apply credit check hold on the specified order
473 -- during process exceptions
474 -- when the return status is not SUCCESS
475 -- The return status will be assigned as Error
476 -- If apply hold returns error , the process will
477 -- abort and return Unexpected error
478 ----------------------------------------------------------------------
479 PROCEDURE Apply_exception_hold
480 ( p_header_id IN NUMBER
481 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
482 )
483 IS
484
485 l_hold_exists VARCHAR2(1) := NULL ;
486 l_hold_result VARCHAR2(30);
487 l_msg_count NUMBER := 0;
488 l_msg_data VARCHAR2(2000);
489 l_return_status VARCHAR2(30);
490 l_hold_comment VARCHAR2(2000);
491 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type :=
492 OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
493 BEGIN
494 IF G_debug_flag = 'Y'
495 THEN
496 OE_DEBUG_PUB.Add('OEXPCRGB: In Apply_exception_hold ');
497 END IF;
498
499 l_hold_source_rec.hold_id := 1; -- credit hold
500 l_hold_source_rec.hold_entity_code := 'O'; -- order hold
501 l_hold_source_rec.hold_entity_id := p_header_id; -- order header
502
503 IF G_debug_flag = 'Y'
504 THEN
505 OE_DEBUG_PUB.Add('Call OE_HOLDS_PUB.Check_Holds');
506 END IF;
507
508 OE_HOLDS_PUB.Check_Holds
509 ( p_api_version => 1.0
510 , p_header_id => p_header_id
511 , p_hold_id => 1
512 , p_wf_item => OE_Credit_Engine_GRP.G_cc_hold_item_type
513 , p_wf_activity => OE_Credit_Engine_GRP.G_cc_hold_activity_name
514 , p_entity_code => 'O'
515 , p_entity_id => p_header_id
516 , x_result_out => l_hold_result
517 , x_msg_count => l_msg_count
518 , x_msg_data => l_msg_data
519 , x_return_status => x_return_status
520 );
521
522 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
523 RAISE FND_API.G_EXC_ERROR;
524 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
526 END IF;
527
528 IF l_hold_result = FND_API.G_TRUE
529 THEN
530 OE_DEBUG_PUB.Add('OEXVCRHB: Hold already applied on Header ID:' ||
531 p_header_id );
532 ELSE
533 l_hold_comment := 'Credit checking package API Error' ;
534 l_hold_source_rec.hold_comment :=
535 NVL(OE_Credit_Engine_GRP.G_currency_error_msg, l_hold_comment) ;
536
537 OE_DEBUG_PUB.Add('Call OE_Holds_PUB.Apply_Holds' );
538
539 OE_Holds_PUB.Apply_Holds
540 ( p_api_version => 1.0
541 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
542 , p_hold_source_rec => l_hold_source_rec
543 , x_msg_count => l_msg_count
544 , x_msg_data => l_msg_data
545 , x_return_status => x_return_status
546 );
547
548 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
549 RAISE FND_API.G_EXC_ERROR;
550 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
551 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
552 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
553 OE_DEBUG_PUB.ADD('OEXPCRGB: Credit check hold applied on header_ID: '
554 ||p_header_id, 1);
555 END IF;
556 END IF; -- Check hold exist
557
558 IF G_debug_flag = 'Y'
559 THEN
560 OE_DEBUG_PUB.Add('OEXPCRGB: Out Apply_exception_hold ');
561 END IF;
562
563 EXCEPTION
564
565 WHEN OTHERS THEN
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
567 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
568 OE_MSG_PUB.Add_Exc_Msg
569 ( G_PKG_NAME
570 , 'Apply_exception_hold'
571 );
572 END IF;
573 OE_DEBUG_PUB.ADD( SUBSTR( SQLERRM,1,300) ,1 ) ;
574
575
576 END Apply_Exception_hold ;
577
578
579 ---------------------------------------------------------------------------
580 --FUNCTION GET_GL_currency
581 --COMMENT: Returns the SOB currency
582
583 ---------------------------------------------------------------------------
584 FUNCTION GET_GL_currency
585 RETURN VARCHAR2
586 IS
587
588 l_gl_currency VARCHAR2(10);
589 l_sob_id NUMBER;
590
591 BEGIN
592 IF G_debug_flag = 'Y'
593 THEN
594 OE_DEBUG_PUB.ADD('OEXPCRGB: In Get_GL_currency ');
595 END IF;
596
597 BEGIN
598 l_sob_id := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID') ;
599
600 IF G_debug_flag = 'Y'
601 THEN
602 OE_DEBUG_PUB.ADD('l_sob_id = '|| l_sob_id );
603 OE_DEBUG_PUB.ADD('GET SOB currency ');
604 END IF;
605
606 SELECT
607 currency_code
608 INTO
609 l_gl_currency
610 FROM
611 GL_sets_of_books
612 WHERE set_of_books_id = l_sob_id ;
613
614 IF G_debug_flag = 'Y'
615 THEN
616 OE_DEBUG_PUB.ADD('l_gl_currency = '|| l_gl_currency );
617 END IF;
618
619 EXCEPTION
620 WHEN NO_DATA_FOUND
621 THEN
622 l_gl_currency := NULL ;
623 OE_DEBUG_PUB.ADD('EXCEPTION: NO_DATA_FOUND ');
624 l_gl_currency := NULL ;
625 WHEN TOO_MANY_ROWS
626 THEN
627 l_gl_currency := NULL ;
628 OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS');
629 l_gl_currency := NULL ;
630 END ;
631
632 IF G_debug_flag = 'Y'
633 THEN
634 OE_DEBUG_PUB.ADD('OEXPCRGB: Out Get_GL_currency ');
635 END IF;
636
637 RETURN(l_GL_currency);
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
642 OE_MSG_PUB.Add_Exc_Msg
643 ( G_PKG_NAME
644 , 'Get_GL_currency'
645 );
646 END IF;
647 OE_DEBUG_PUB.ADD( SUBSTR(SQLERRM,1,300) ) ;
648 END Get_GL_currency ;
649
650 ---------------------------------------------------------------------------
651 --PROCEDURE: Credit_check_with_payment_typ
652 --COMMENT: Main API called from verify_payment switch
653 -- 2412678 add a new input parameter
654 ---------------------------------------------------------------------------
655 PROCEDURE Credit_check_with_payment_typ
656 ( p_header_id IN NUMBER
657 , p_calling_action IN VARCHAR2
658 , p_delayed_request IN VARCHAR2
659 , p_credit_check_rule_id IN NUMBER := NULL
660 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
661 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
662 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
663 )
664 IS
665
666 l_epayment VARCHAR2(1) ;
667
668 l_msg_count NUMBER := 0 ;
669 l_msg_data VARCHAR2(2000):= NULL ;
670 l_result_out VARCHAR2(100);
671 l_cc_hold_comment VARCHAR2(2000):= NULL;
672
673 BEGIN
674 x_return_status := FND_API.G_RET_STS_SUCCESS;
675
676 IF G_debug_flag = 'Y'
677 THEN
678 OE_DEBUG_PUB.ADD('OEXPCRGB: IN Credit_check_with_payment_typ ',1);
679 OE_DEBUG_PUB.ADD('p_header_id = '|| p_header_id,1 );
680 OE_DEBUG_PUB.ADD('p_calling_action = ' || p_calling_action,1 );
681 OE_DEBUG_PUB.ADD('p_delayed_request = '|| p_delayed_request,1 );
682 OE_DEBUG_PUB.ADD('p_credit_check_rule_id => '|| p_credit_check_rule_id,1 );
683 END IF;
684
685 OE_Credit_Engine_GRP.G_delayed_request := NULL ;
686
687 IF G_debug_flag = 'Y'
688 THEN
689 OE_DEBUG_PUB.ADD('call Check_Credit for credit checking',1);
690 END IF;
691
692 Check_Credit
693 ( p_header_id => p_header_id
694 , p_calling_action => p_calling_action
695 , p_delayed_request => p_delayed_request
696 , p_credit_check_rule_id => p_credit_check_rule_id
697 , x_msg_count => x_msg_count
698 , x_msg_data => x_msg_data
699 , x_cc_hold_comment => l_cc_hold_comment
700 , x_result_out => l_result_out
701 , x_return_status => x_return_status
702 );
703
704 IF G_debug_flag = 'Y'
705 THEN
706 OE_DEBUG_PUB.ADD('x_return_status '|| x_return_status );
707 END IF;
708
709 IF x_return_status = FND_API.G_RET_STS_ERROR
710 THEN
711 -- Assign the status to SUCCESS as the order will
712 -- put on hold when the status is error
713 -- GL currency conversion
714
715 x_return_status := FND_API.G_RET_STS_SUCCESS ;
716
717 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
719 END IF;
720
721 IF G_debug_flag = 'Y'
722 THEN
723 OE_DEBUG_PUB.ADD('OEXPCRGB:Final x_return_status = '|| x_return_status,1 );
724 OE_DEBUG_PUB.ADD('OEXPCRGB:l_result_out = '|| l_result_out,1 );
725 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT Credit_check_with_payment_typ ',1);
726 END IF;
727
728 Oe_Globals.G_calling_source:= 'WSH'; --8478151
729
730 EXCEPTION
731
732 WHEN FND_API.G_EXC_ERROR THEN
733 Oe_Globals.G_calling_source:= 'WSH'; --8478151
734 x_return_status := FND_API.G_RET_STS_ERROR;
735 oe_debug_pub.add(' SQLERRM: '|| SQLERRM );
736 OE_MSG_PUB.Count_And_Get
737 ( p_count => l_msg_count,
738 p_data => l_msg_data
739 );
740
741 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
742 Oe_Globals.G_calling_source:= 'WSH'; --8478151
743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
744 oe_debug_pub.add(' SQLERRM: '|| SQLERRM );
745 OE_MSG_PUB.Count_And_Get
746 ( p_count => l_msg_count,
747 p_data => l_msg_data
748 );
749
750 WHEN OTHERS THEN
751 Oe_Globals.G_calling_source:= 'WSH'; --8478151
752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753 oe_debug_pub.add(' SQLERRM: '|| SQLERRM );
754 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
755 THEN
756 FND_MSG_PUB.Add_Exc_Msg
757 ( G_PKG_NAME
758 , 'Credit_check_with_payment_typ'
759 );
760 END IF;
761
762 OE_MSG_PUB.Count_And_Get
763 ( p_count => l_msg_count,
764 p_data => l_msg_data
765 );
766
767 END Credit_check_with_payment_typ ;
768
769 --=========================================================================
770 -- PROCEDURE : Get_Customer_Exposure PUBLIC
771 -- PARAMETERS : p_customer_id Customer ID
772 -- : p_site_id Bill-to Site ID
773 -- : p_limit_curr_code Credit limit currency code
774 -- : p_credit_check_rule_id Credit Check Rule Id
775 -- : x_total_exposure Credit exposure
776 -- : x_return_status Status
777 -- COMMENT : This procedure calculates credit exposure for given customer
778 -- or given bill-to site.
779 -- This procedure will superseed the original get_customer_exposure
780 -- API for party level changes from ONT-I
781 -- For backward compatible reasons, the original procedure
782 -- will call this procedure
783 --=========================================================================
784 PROCEDURE Get_customer_exposure
785 ( p_party_id IN NUMBER
786 , p_customer_id IN NUMBER
787 , p_site_id IN NUMBER
788 , p_limit_curr_code IN VARCHAR2
789 , p_credit_check_rule_id IN NUMBER
790 , p_need_exposure_details IN VARCHAR2 := 'N'
791 , x_total_exposure OUT NOCOPY /* file.sql.39 change */ NUMBER
792 , x_order_hold_amount OUT NOCOPY /* file.sql.39 change */ NUMBER
793 , x_order_amount OUT NOCOPY /* file.sql.39 change */ NUMBER
794 , x_ar_amount OUT NOCOPY /* file.sql.39 change */ NUMBER
795 , x_external_amount OUT NOCOPY /* file.sql.39 change */ NUMBER
796 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
797 )
798 IS
799
800
801 l_msg_count NUMBER := 0 ;
802 l_msg_data VARCHAR2(2000):= NULL ;
803 l_credit_check_rule_rec OE_CREDIT_CHECK_UTIL.OE_CREDIT_RULES_REC_TYPE ;
804 l_system_parameters_rec OE_CREDIT_CHECK_UTIL.OE_SYSTEMS_PARAM_REC_TYPE ;
805 l_usage_curr_tbl OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
806 l_total_exposure NUMBER;
807 l_error_curr_tbl OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
808 l_conversion_status OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
809 l_return_status VARCHAR2(50);
810 l_include_all_flag VARCHAR2(15);
811 i INTEGER:=0;
812 j INTEGER:=0;
813 k INTEGER:=0;
814 f INTEGER:=0;
815
816 l_start NUMBER:=0;
817 l_end NUMBER:=0;
818
819 l_global_exposure_flag VARCHAR2(1);
820 l_org_id NUMBER:=0; /* MOAC CREDIT CHECK CHANGE */
821
822 BEGIN
823 IF G_debug_flag = 'Y'
824 THEN
825 OE_DEBUG_PUB.ADD('OEXPCRGB: IN Get_Customer_Exposure ',1);
826 OE_DEBUG_PUB.ADD('p_party_id => '|| p_party_id );
827 OE_DEBUG_PUB.ADD('p_customer_id => '|| p_customer_id );
828 OE_DEBUG_PUB.ADD('p_site_id => '|| p_site_id );
829 OE_DEBUG_PUB.ADD('p_limit_curr_code => '|| p_limit_curr_code );
830 OE_DEBUG_PUB.ADD('p_credit_check_rule_id => '|| p_credit_check_rule_id );
831 OE_DEBUG_PUB.ADD('p_need_exposure_details => '|| p_need_exposure_details );
832 END IF;
833
834 x_return_status := FND_API.G_RET_STS_SUCCESS;
835 l_global_exposure_flag := 'N' ;
836
837
838 ---get credit check rule record
839 OE_CREDIT_CHECK_UTIL.GET_credit_check_rule
840 ( p_credit_check_rule_id => p_credit_check_rule_id
841 , x_credit_check_rules_rec => l_credit_check_rule_rec
842 );
843
844 -----Get system parameters record
845 OE_CREDIT_CHECK_UTIL.GET_System_parameters
846 ( x_system_parameter_rec=>l_system_parameters_rec
847 );
848
849 OE_DEBUG_PUB.ADD('Check entity type ');
850
851 -------------------- EXPOSURE -----------------------
852
853 /* Modified the following condition to fix the bug 5650329 */
854 IF p_party_id IS NOT NULL and ( p_customer_id IS NULL ) and ( p_site_id IS NULL )
855 THEN
856 l_global_exposure_flag := 'Y' ;
857
858 IF G_debug_flag = 'Y'
859 THEN
860 OE_DEBUG_PUB.ADD('Party level exposure ');
861 OE_DEBUG_PUB.ADD('Calling Get_global_exposure_flag ');
862 OE_DEBUG_PUB.ADD('l_global_exposure_flag = '||
863 l_global_exposure_flag );
864 OE_DEBUG_PUB.ADD('OEXPCRGB: Get usages for PARTY level ');
865 END IF;
866
867 ----get table of usages
868 OE_CREDIT_CHECK_UTIL.Get_Usages
869 ( p_entity_type => 'PARTY'
870 , p_entity_id => p_party_id
871 , p_limit_curr_code => p_limit_curr_code
872 , p_suppress_unused_usages_flag => 'Y'
873 , p_default_limit_flag => NULL
874 , p_global_exposure_flag => l_global_exposure_flag
875 , x_include_all_flag => l_include_all_flag
876 , x_usage_curr_tbl => l_usage_curr_tbl
877 );
878
879 IF G_debug_flag = 'Y'
880 THEN
881 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of OE_CREDIT_CHECK_UTIL. Get_Usages ');
882 OE_DEBUG_PUB.ADD('x_include_all_flag: '||l_include_all_flag);
883 OE_DEBUG_PUB.ADD('---------------------------------------');
884 END IF;
885
886 ----IF l_include_all_flag is 'Y', assign it to the global variable
887 --- used later for unchecked exposure
888
889 IF l_include_all_flag='Y'
890 THEN
891 OE_Credit_Engine_GRP.G_cust_incl_all_flag :='Y';
892 END IF;
893
894 FOR a IN 1..l_usage_curr_tbl.COUNT
895 LOOP
896 IF G_debug_flag = 'Y'
897 THEN
898 OE_DEBUG_PUB.ADD('currency_code=: '
899 ||l_usage_curr_tbl(a).usage_curr_code);
900 END IF;
901 END LOOP;
902 --------------------------------------------------------------------------------
903 ----assign table of usages to the Global variable
904
905 l_start:=OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT+1;
906 l_end:=OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT+l_usage_curr_tbl.COUNT;
907
908 IF G_debug_flag = 'Y'
909 THEN
910 OE_DEBUG_PUB.ADD('l_start= '||TO_CHAR(l_start));
911 OE_DEBUG_PUB.ADD('l_end= '||TO_CHAR(l_end));
912 OE_DEBUG_PUB.ADD('IN loop for assign table of usge to Glb variable ');
913 END IF;
914
915 FOR i IN l_start..l_end
916 LOOP
917 j:=j+1;
918 OE_Credit_Engine_GRP.G_cust_curr_tbl(i).usage_curr_code
919 := l_usage_curr_tbl(j).usage_curr_code;
920
921 END LOOP;
922
923 OE_CREDIT_EXPOSURE_PVT.Get_Exposure
924 ( p_party_id => p_party_id
925 , p_customer_id => NULL
926 , p_site_use_id => NULL
927 , p_header_id => NULL
928 , p_credit_check_rule_rec => l_credit_check_rule_rec
929 , p_system_parameters_rec => l_system_parameters_rec
930 , p_limit_curr_code => p_limit_curr_code
931 , p_usage_curr_tbl => l_usage_curr_tbl
932 , p_include_all_flag => l_include_all_flag
933 , p_global_exposure_flag => l_global_exposure_flag
934 , p_need_exposure_details => p_need_exposure_details
935 , x_total_exposure => x_total_exposure
936 , x_order_amount => x_order_amount
937 , x_order_hold_amount => x_order_hold_amount
938 , x_ar_amount => x_ar_amount
939 , x_return_status => l_return_status
940 , x_error_curr_tbl => l_error_curr_tbl
941 );
942
943
944 IF l_error_curr_tbl.COUNT<>0
945 THEN
946
947 FOR k IN 1..l_error_curr_tbl.COUNT
948 LOOP
949 IF G_debug_flag = 'Y'
950 THEN
951 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
952 !!!!!!!');
953 OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(k)
954 .usage_curr_code
955 ||' and credit limit currency '
956 ||p_limit_curr_code
957 ||' is missing for conversion type '
958 || NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1
959 );
960 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
961 !!!!!!!');
962
963 END IF;
964 END LOOP;
965
966 x_return_status:='C';
967
968 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
969 THEN
970 RAISE FND_API.G_EXC_ERROR;
971 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
972 THEN
973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
974 END IF;
975
976 -----------End party ----
977 ---customer level exposure
978 ELSIF p_site_id IS NULL
979 THEN
980 IF G_debug_flag = 'Y'
981 THEN
982 OE_DEBUG_PUB.ADD('Customer level exposure ');
983 OE_DEBUG_PUB.ADD('Calling Get_global_exposure_flag ');
984 END IF;
985
986 l_global_exposure_flag :=
987 OE_CREDIT_CHECK_UTIL.Get_global_exposure_flag
988 ( p_entity_type => 'CUSTOMER'
989 , p_entity_id => p_customer_id
990 , p_limit_curr_code => p_limit_curr_code
991 );
992
993 /* Start MOAC CREDIT CHECK CHANGE */
994 IF l_global_exposure_flag = 'N'
995 THEN
996 OE_Credit_Engine_GRP.Set_context;
997 END IF;
998 /* End MOAC CREDIT CHECK CHANGE */
999
1000 IF G_debug_flag = 'Y'
1001 THEN
1002 OE_DEBUG_PUB.ADD('l_global_exposure_flag = '||
1003 l_global_exposure_flag );
1004 OE_DEBUG_PUB.ADD('OEXPCRGB: Get usages for CUSTOMER level ');
1005 END IF;
1006
1007 ----get table of usages
1008 OE_CREDIT_CHECK_UTIL.Get_Usages
1009 ( p_entity_type => 'CUSTOMER'
1010 , p_entity_id => p_customer_id
1011 , p_limit_curr_code => p_limit_curr_code
1012 , p_suppress_unused_usages_flag => 'Y'
1013 , p_default_limit_flag => NULL
1014 , p_global_exposure_flag => l_global_exposure_flag
1015 , x_include_all_flag => l_include_all_flag
1016 , x_usage_curr_tbl => l_usage_curr_tbl
1017 );
1018
1019 IF G_debug_flag = 'Y'
1020 THEN
1021 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of OE_CREDIT_CHECK_UTIL. Get_Usages ');
1022 OE_DEBUG_PUB.ADD('x_include_all_flag: '||l_include_all_flag);
1023 OE_DEBUG_PUB.ADD('---------------------------------------');
1024 END IF;
1025
1026 ----IF l_include_all_flag is 'Y', assign it to the global variable
1027 --- used later for unchecked exposure
1028
1029 IF l_include_all_flag='Y'
1030 THEN
1031 OE_Credit_Engine_GRP.G_cust_incl_all_flag :='Y';
1032 END IF;
1033
1034
1035 FOR a IN 1..l_usage_curr_tbl.COUNT
1036 LOOP
1037 IF G_debug_flag = 'Y'
1038 THEN
1039 OE_DEBUG_PUB.ADD('currency_code=: '
1040 ||l_usage_curr_tbl(a).usage_curr_code);
1041 END IF;
1042 END LOOP;
1043 --------------------------------------------------------------------------------
1044 ----assign table of usages to the Global variable
1045
1046 l_start:=OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT+1;
1047 l_end:=OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT+l_usage_curr_tbl.COUNT;
1048
1049 IF G_debug_flag = 'Y'
1050 THEN
1051 OE_DEBUG_PUB.ADD('l_start= '||TO_CHAR(l_start));
1052 OE_DEBUG_PUB.ADD('l_end= '||TO_CHAR(l_end));
1053 OE_DEBUG_PUB.ADD('IN loop for assign table of usages to Global variable ');
1054 END IF;
1055
1056 FOR i IN l_start..l_end
1057 LOOP
1058 j:=j+1;
1059 OE_Credit_Engine_GRP.G_cust_curr_tbl(i).usage_curr_code
1060 := l_usage_curr_tbl(j).usage_curr_code;
1061
1062 END LOOP;
1063
1064 ----pre-calculate exposure
1065 IF l_credit_check_rule_rec.quick_cr_check_flag = 'Y'
1066 THEN
1067
1068 OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1069 ( p_customer_id => p_customer_id
1070 , p_site_use_id => NULL
1071 , p_header_id => NULL
1072 , p_credit_check_rule_rec => l_credit_check_rule_rec
1073 , p_system_parameters_rec => l_system_parameters_rec
1074 , p_limit_curr_code => p_limit_curr_code
1075 , p_usage_curr_tbl => l_usage_curr_tbl
1076 , p_include_all_flag => l_include_all_flag
1077 , p_global_exposure_flag => l_global_exposure_flag
1078 , p_need_exposure_details => p_need_exposure_details
1079 , x_total_exposure => x_total_exposure
1080 , x_order_amount => x_order_amount
1081 , x_order_hold_amount => x_order_hold_amount
1082 , x_ar_amount => x_ar_amount
1083 , x_return_status => l_return_status
1084 , x_error_curr_tbl => l_error_curr_tbl
1085 );
1086
1087 IF l_error_curr_tbl.COUNT<>0
1088 THEN
1089
1090 FOR k IN 1..l_error_curr_tbl.COUNT
1091 LOOP
1092 IF G_debug_flag = 'Y'
1093 THEN
1094 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1095 OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(k).usage_curr_code
1096 ||' and credit limit currency '
1097 ||p_limit_curr_code
1098 ||' is missing for conversion type '
1099 || NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1100 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1101 END IF;
1102 END LOOP;
1103
1104 ---bug fix 2439029
1105
1106 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1107
1108 x_return_status:='C';
1109
1110 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1111 THEN
1112 RAISE FND_API.G_EXC_ERROR;
1113 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1114 THEN
1115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1116 END IF;
1117
1118 ----non pre-calculate exposure
1119 ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1120 THEN
1121
1122 OE_CREDIT_CHECK_UTIL.Get_order_exposure
1123 ( p_header_id => NULL
1124 , p_transaction_curr_code => NULL
1125 , p_customer_id => p_customer_id
1126 , p_site_use_id => NULL
1127 , p_credit_check_rule_rec => l_credit_check_rule_rec
1128 , p_system_parameter_rec => l_system_parameters_rec
1129 , p_credit_level => 'CUSTOMER'
1130 , p_limit_curr_code => p_limit_curr_code
1131 , p_usage_curr => l_usage_curr_tbl
1132 , p_include_all_flag => l_include_all_flag
1133 , p_global_exposure_flag => l_global_exposure_flag
1134 , p_need_exposure_details => p_need_exposure_details
1135 , x_total_exposure => x_total_exposure
1136 , x_order_amount => x_order_amount
1137 , x_order_hold_amount => x_order_hold_amount
1138 , x_ar_amount => x_ar_amount
1139 , x_return_status => l_return_status
1140 , x_conversion_status => l_conversion_status
1141 );
1142
1143
1144 IF l_conversion_status.COUNT<>0
1145 THEN
1146
1147 FOR k IN 1..l_conversion_status.COUNT
1148 LOOP
1149 IF G_debug_flag = 'Y'
1150 THEN
1151 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1152 OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(k).usage_curr_code
1153 ||' and credit limit currency '
1154 ||p_limit_curr_code
1155 ||' is missing for conversion type '
1156 || NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1157 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1158 END IF;
1159 END LOOP;
1160
1161 ---bug fix 2439029
1162
1163 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1164
1165 x_return_status:='C';
1166
1167 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1168 THEN
1169 RAISE FND_API.G_EXC_ERROR;
1170 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1171 THEN
1172 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1173 END IF;
1174
1175 END IF;
1176
1177 ----------------- SITE level exposure ----------------
1178
1179 -----bill-to site level exposure
1180 ELSIF p_site_id IS NOT NULL
1181 THEN
1182 l_global_exposure_flag := 'N' ;
1183
1184 /* Start MOAC CREDIT CHECK CHANGE */
1185
1186 BEGIN
1187 SELECT org_id
1188 INTO l_org_id
1189 FROM hz_cust_site_uses_all
1190 WHERE site_use_id = p_site_id;
1191
1192 EXCEPTION
1193 WHEN NO_DATA_FOUND THEN
1194 IF G_debug_flag = 'Y' THEN
1195 OE_DEBUG_PUB.ADD ('Exception : No org id via site_id ', 1);
1196 END IF;
1197
1198 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1199 THEN
1200 FND_MSG_PUB.Add_Exc_Msg
1201 ( G_PKG_NAME
1202 , 'Get_Customer_Exposure'
1203 );
1204 END IF;
1205
1206 WHEN OTHERS THEN
1207 IF G_debug_flag = 'Y' THEN
1208 OE_DEBUG_PUB.ADD ('Exception : Unexpected error in finding org id via site_id ', 1);
1209 END IF;
1210
1211 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1212 THEN
1213 FND_MSG_PUB.Add_Exc_Msg
1214 ( G_PKG_NAME
1215 , 'Get_Customer_Exposure'
1216 );
1217 END IF;
1218
1219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1220 END ;
1221
1222 MO_GLOBAL.Set_Policy_Context('S', l_org_id) ;
1223
1224 OE_CREDIT_CHECK_UTIL.G_org_id := l_org_id ;
1225
1226 /* End MOAC CREDIT CHECK CHANGE */
1227
1228 IF G_debug_flag = 'Y'
1229 THEN
1230 OE_DEBUG_PUB.ADD('Site level exposure ');
1231 END IF;
1232
1233 ----get table of usages
1234 OE_CREDIT_CHECK_UTIL.Get_Usages
1235 ( p_entity_type => 'SITE'
1236 , p_entity_id => p_site_id
1237 , p_limit_curr_code => p_limit_curr_code
1238 , p_suppress_unused_usages_flag => 'Y'
1239 , p_default_limit_flag => NULL
1240 , p_global_exposure_flag => 'N'
1241 , x_include_all_flag => l_include_all_flag
1242 , x_usage_curr_tbl => l_usage_curr_tbl
1243 );
1244
1245
1246 ----IF l_include_all_flag is 'Y', assign it to the global variable
1247 IF l_include_all_flag='Y'
1248 THEN
1249 OE_Credit_Engine_GRP.G_site_incl_all_flag:='Y';
1250 END IF;
1251
1252
1253
1254 FOR a IN 1..l_usage_curr_tbl.COUNT
1255 LOOP
1256 IF G_debug_flag = 'Y'
1257 THEN
1258 OE_DEBUG_PUB.ADD('currency_code=: '||l_usage_curr_tbl(a).usage_curr_code);
1259 END IF;
1260 END LOOP;
1261 --------------------------------------------------------------------------------
1262
1263 ----assign table of usages to the Global variable
1264 l_start := OE_Credit_Engine_GRP.G_site_curr_tbl.COUNT+1;
1265 l_end := OE_Credit_Engine_GRP.G_site_curr_tbl.COUNT+l_usage_curr_tbl.COUNT;
1266
1267 IF G_debug_flag = 'Y'
1268 THEN
1269 OE_DEBUG_PUB.ADD('l_start= '||TO_CHAR(l_start));
1270 OE_DEBUG_PUB.ADD('l_end= '||TO_CHAR(l_end));
1271 OE_DEBUG_PUB.ADD('IN loop for assign table of usages to Global variable ');
1272 OE_DEBUG_PUB.ADD('table of currencies:');
1273 END IF;
1274
1275 FOR i IN l_start..l_end
1276 LOOP
1277
1278 j:=j+1;
1279 OE_Credit_Engine_GRP.G_site_curr_tbl(i).usage_curr_code
1280 := l_usage_curr_tbl(j).usage_curr_code;
1281
1282 IF G_debug_flag = 'Y'
1283 THEN
1284 OE_DEBUG_PUB.ADD('currency= '||l_usage_curr_tbl(j).usage_curr_code);
1285 END IF;
1286 END LOOP;
1287
1288
1289 ----pre-calculate exposure
1290 IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1291 THEN
1292
1293 OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1294 ( p_customer_id => p_customer_id
1295 , p_site_use_id => p_site_id
1296 , p_header_id => NULL
1297 , p_credit_check_rule_rec => l_credit_check_rule_rec
1298 , p_system_parameters_rec => l_system_parameters_rec
1299 , p_limit_curr_code => p_limit_curr_code
1300 , p_usage_curr_tbl => l_usage_curr_tbl
1301 , p_include_all_flag => l_include_all_flag
1302 , p_global_exposure_flag => 'N'
1303 , p_need_exposure_details => p_need_exposure_details
1304 , x_total_exposure => x_total_exposure
1305 , x_order_amount => x_order_amount
1306 , x_order_hold_amount => x_order_hold_amount
1307 , x_ar_amount => x_ar_amount
1308 , x_return_status => l_return_status
1309 , x_error_curr_tbl => l_error_curr_tbl
1310 );
1311
1312 IF l_error_curr_tbl.COUNT<>0
1313 THEN
1314 FOR f IN 1..l_error_curr_tbl.COUNT
1315 LOOP
1316 IF G_debug_flag = 'Y'
1317 THEN
1318 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1319 OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code
1320 ||' and credit limit currency '
1321 ||p_limit_curr_code
1322 ||' is missing for conversion type '
1323 || NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1324 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1325 END IF;
1326 END LOOP;
1327
1328 ---bug fix 2439029
1329
1330 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1331
1332 x_return_status:='C';
1333
1334 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1335 THEN
1336 RAISE FND_API.G_EXC_ERROR;
1337 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1338 THEN
1339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1340 END IF;
1341
1342 ----non pre-calculate exposure
1343 ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1344 THEN
1345
1346 OE_CREDIT_CHECK_UTIL.Get_order_exposure
1347 ( p_header_id => NULL
1348 , p_transaction_curr_code => NULL
1349 , p_customer_id => p_customer_id
1350 , p_site_use_id => p_site_id
1351 , p_credit_check_rule_rec => l_credit_check_rule_rec
1352 , p_system_parameter_rec => l_system_parameters_rec
1353 , p_credit_level => 'SITE'
1354 , p_limit_curr_code => p_limit_curr_code
1355 , p_usage_curr => l_usage_curr_tbl
1356 , p_include_all_flag => l_include_all_flag
1357 , p_global_exposure_flag => 'N'
1358 , p_need_exposure_details => p_need_exposure_details
1359 , x_total_exposure => x_total_exposure
1360 , x_order_amount => x_order_amount
1361 , x_order_hold_amount => x_order_hold_amount
1362 , x_ar_amount => x_ar_amount
1363 , x_return_status => l_return_status
1364 , x_conversion_status => l_conversion_status
1365 );
1366
1367
1368 IF l_conversion_status.COUNT<>0
1369 THEN
1370
1371 FOR f IN 1..l_conversion_status.COUNT
1372 LOOP
1373 IF G_debug_flag = 'Y'
1374 THEN
1375 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1376 OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code
1377 ||' and credit limit currency '
1378 ||p_limit_curr_code
1379 ||' is missing for conversion type '
1380 || NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1381 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1382 END IF;
1383 END LOOP;
1384
1385 ---bug fix 2439029
1386
1387 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1388
1389 x_return_status:='C';
1390
1391
1392 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1393 THEN
1394 RAISE FND_API.G_EXC_ERROR;
1395 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1396 THEN
1397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1398 END IF;
1399 END IF;
1400
1401 END IF;
1402
1403 IF G_debug_flag = 'Y'
1404 THEN
1405 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT Get_Customer_Exposure with the status='||x_return_status);
1406 END IF;
1407
1408 EXCEPTION
1409
1410 WHEN FND_API.G_EXC_ERROR THEN
1411 x_return_status := FND_API.G_RET_STS_ERROR;
1412 OE_MSG_PUB.Count_And_Get
1413 ( p_count => l_msg_count,
1414 p_data => l_msg_data
1415 );
1416
1417 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1419 OE_MSG_PUB.Count_And_Get
1420 ( p_count => l_msg_count,
1421 p_data => l_msg_data
1422 );
1423
1424 WHEN OTHERS THEN
1425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1426 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1427 THEN
1428 FND_MSG_PUB.Add_Exc_Msg
1429 ( G_PKG_NAME
1430 , 'Get_Customer_Exposure'
1431 );
1432 END IF;
1433
1434 OE_MSG_PUB.Count_And_Get
1435 ( p_count => l_msg_count,
1436 p_data => l_msg_data
1437 );
1438
1439 END Get_Customer_Exposure;
1440
1441 --------------------------------------------------------
1442 -- The following is the Original procedure released with ONT-G
1443 -- From the ONT-I, this procedure is overloaded with the
1444 -- party level
1445 -- for backward compatible this procedure is retained
1446 -------------------------------------------------------------
1447 PROCEDURE Get_customer_exposure
1448 ( p_customer_id IN NUMBER
1449 , p_site_id IN NUMBER
1450 , p_limit_curr_code IN VARCHAR2
1451 , p_credit_check_rule_id IN NUMBER
1452 , x_total_exposure OUT NOCOPY /* file.sql.39 change */ NUMBER
1453 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1454 )
1455 IS
1456
1457 l_msg_count NUMBER := 0 ;
1458 l_msg_data VARCHAR2(2000):= NULL ;
1459
1460 l_order_hold_amount NUMBER;
1461 l_ar_amount NUMBER;
1462 l_order_amount NUMBER;
1463 l_EXTERNAL_AMOUNT NUMBER ;
1464
1465 BEGIN
1466
1467 IF G_debug_flag = 'Y'
1468 THEN
1469 OE_DEBUG_PUB.ADD(' Into Get_customer_exposure ');
1470 OE_DEBUG_PUB.ADD(' Calling Get_customer_exposure with details');
1471 OE_DEBUG_PUB.ADD('p_customer_id => '|| p_customer_id );
1472 OE_DEBUG_PUB.ADD('p_site_id => '|| p_site_id );
1473 OE_DEBUG_PUB.ADD('p_limit_curr_code => '|| p_limit_curr_code );
1474 OE_DEBUG_PUB.ADD('p_credit_check_rule_id => '|| p_credit_check_rule_id );
1475
1476 END IF;
1477
1478 OE_CREDIT_ENGINE_GRP.Get_customer_exposure
1479 ( p_party_id => NULL
1480 , p_customer_id => p_customer_id
1481 , p_site_id => p_site_id
1482 , p_limit_curr_code => p_limit_curr_code
1483 , p_credit_check_rule_id => p_credit_check_rule_id
1484 , p_need_exposure_details => 'N'
1485 , x_total_exposure => x_total_exposure
1486 , x_order_hold_amount => l_order_hold_amount
1487 , x_order_amount => l_order_amount
1488 , x_ar_amount => l_ar_amount
1489 , x_external_amount => l_external_amount
1490 , x_return_status => x_return_status
1491 );
1492
1493 IF G_debug_flag = 'Y'
1494 THEN
1495 OE_DEBUG_PUB.ADD(' Out x_total_exposure => '|| x_total_exposure );
1496 OE_DEBUG_PUB.ADD(' Out x_return_status => '|| x_return_status );
1497 OE_DEBUG_PUB.ADD(' Out Get_customer_exposure');
1498 END IF;
1499
1500 EXCEPTION
1501 WHEN OTHERS THEN
1502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1503 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1504 THEN
1505 FND_MSG_PUB.Add_Exc_Msg
1506 ( G_PKG_NAME
1507 , 'Get_Customer_Exposure'
1508 );
1509 END IF;
1510
1511 OE_MSG_PUB.Count_And_Get
1512 ( p_count => l_msg_count,
1513 p_data => l_msg_data
1514 );
1515
1516 END Get_Customer_Exposure;
1517
1518
1519 --------------------------------------------------------------------------
1520 --PROCEDURE: Check_Credit
1521 --COMMENT: main procedure for checking non electronic payments
1522 --MODIFICATION:
1523 -- 02/15/2002 Removed Default NULL for p_calling_action
1524 ---------------------------------------------------------------------------
1525 PROCEDURE Check_Credit
1526 ( p_header_id IN NUMBER
1527 , p_calling_action IN VARCHAR2
1528 , p_delayed_request IN VARCHAR2 := NULL
1529 , p_bill_to_site_use_id IN NUMBER := NULL
1530 , p_credit_check_rule_id IN NUMBER := NULL
1531 , p_functional_currency_code IN VARCHAR2 := NULL
1532 , p_transaction_currency_code IN VARCHAR2 := NULL
1533 , p_transaction_amount IN NUMBER := NULL
1534 , p_org_id IN NUMBER := NULL
1535 , x_cc_hold_comment OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1536 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1537 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1538 , x_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1539 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1540 )
1541 IS
1542
1543 l_msg_count NUMBER := 0 ;
1544 l_msg_data VARCHAR2(2000):= NULL ;
1545 l_credit_check_rule_id NUMBER;
1546 l_credit_check_rule_rec OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type ;
1547 l_system_parameter_rec OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type ;
1548 l_cc_limit_used NUMBER;
1549 l_cc_profile_used VARCHAR2(30);
1550 l_header_rec OE_Order_PUB.Header_Rec_Type;
1551
1552 BEGIN
1553
1554 x_return_status := FND_API.G_RET_STS_SUCCESS;
1555
1556 IF G_debug_flag = 'Y'
1557 THEN
1558 OE_DEBUG_PUB.ADD('OEXPCRGB: IN Check_Credit ',1);
1559 OE_DEBUG_PUB.ADD('Input parameters list ');
1560 OE_DEBUG_PUB.ADD('p_header_id = '|| p_header_id,1 );
1561 OE_DEBUG_PUB.ADD('p_delayed_request = '|| p_delayed_request,1 );
1562 OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action,1 );
1563 OE_DEBUG_PUB.ADD('p_credit_check_rule_id => '|| p_credit_check_rule_id );
1564 END IF;
1565
1566 OE_Credit_Engine_GRP.G_delayed_request := NULL ;
1567 OE_Credit_Engine_GRP.G_currency_error_msg := NULL ;
1568 OE_Credit_Engine_GRP.G_delayed_request := p_delayed_request ;
1569
1570 --ER#7479609 start
1571 IF p_calling_action in ('AUTO HOLD','AUTO RELEASE') THEN
1572 OE_Verify_Payment_PUB.G_init_calling_action := p_calling_action;
1573 END IF;
1574 --ER#7479609 end
1575
1576 --bug# 4967828
1577 IF p_org_id IS NOT NULL THEN
1578 OE_CREDIT_CHECK_UTIL.G_org_id := p_org_id ;
1579 END IF;
1580
1581 -- Get Gl currency if calling action is not EXTERNAL
1582 --
1583 IF NVL(p_calling_action, 'BOOKING') <> 'EXTERNAL' THEN
1584 OE_Credit_Engine_GRP.GL_currency := OE_Credit_Engine_GRP.GET_GL_currency;
1585
1586 OE_Header_UTIL.Query_Row
1587 (p_header_id => p_header_id
1588 ,x_header_rec => l_header_rec
1589 );
1590
1591 --bug# 4967828
1592 IF p_org_id IS NULL THEN
1593 OE_CREDIT_CHECK_UTIL.G_org_id := l_header_rec.org_id ;
1594 END IF;
1595
1596 IF G_debug_flag = 'Y'
1597 THEN
1598 OE_DEBUG_PUB.ADD('after query header ');
1599 OE_DEBUG_PUB.ADD(' ');
1600 OE_DEBUG_PUB.ADD('======================================');
1601 OE_DEBUG_PUB.ADD('Header ID = '|| l_header_rec.header_id );
1602 OE_DEBUG_PUB.ADD('order_category_code = '||
1603 l_header_rec.order_category_code );
1604 OE_DEBUG_PUB.ADD('Booked flag = '||
1605 l_header_rec.booked_flag );
1606 OE_DEBUG_PUB.ADD('Order number = '||
1607 l_header_rec.order_number );
1608 --OE_DEBUG_PUB.ADD('Credit crad approval date = '||
1609 -- l_header_rec.credit_card_approval_date );
1610 OE_DEBUG_PUB.ADD('payment_term_id = ' ||
1611 l_header_rec.payment_term_id );
1612 OE_DEBUG_PUB.ADD('order_type_id = '||
1613 l_header_rec.order_type_id );
1614 OE_DEBUG_PUB.ADD(' ');
1615 OE_DEBUG_PUB.ADD('======================================');
1616 END IF;
1617
1618 IF p_credit_check_rule_id is NULL or
1619 NVL(p_credit_check_rule_id,0) = 0
1620 THEN
1621
1622 Get_Credit_Check_Rule_ID
1623 ( p_calling_action => p_calling_action
1624 , p_order_type_id => l_header_rec.order_type_id
1625 , x_credit_rule_id => l_credit_check_rule_id
1626 );
1627 ELSE
1628 l_credit_check_rule_id := p_credit_check_rule_id;
1629 END IF;
1630
1631 ELSE
1632 -- External credit checking call
1633 l_credit_check_rule_id := p_credit_check_rule_id;
1634 OE_CREDIT_ENGINE_GRP.GL_Currency := p_functional_currency_code;
1635 l_header_rec.header_id := NULL;
1636 l_header_rec.transactional_curr_code := p_transaction_currency_code;
1637 l_header_rec.invoice_to_org_id := p_bill_to_site_use_id;
1638 END IF;
1639
1640 IF G_debug_flag = 'Y'
1641 THEN
1642 OE_DEBUG_PUB.ADD(' GL_CURRENCY after = '|| OE_Credit_Engine_GRP.GL_currency );
1643 OE_DEBUG_PUB.ADD('l_credit_check_rule_id = '|| l_credit_check_rule_id );
1644 END IF;
1645
1646 ----------------------- start Processing --------------------
1647
1648 IF l_credit_check_rule_id is NULL
1649 THEN
1650 IF G_debug_flag = 'Y'
1651 THEN
1652 OE_DEBUG_PUB.ADD(' No credit check attached, return ');
1653 END IF;
1654 RETURN ;
1655 END IF;
1656
1657 -- Initialize the G_cc_hold_item_type and G_cc_hold_activity_name
1658 -- global variables
1659 Set_G_CC_Hold_Info;
1660
1661 OE_CREDIT_CHECK_UTIL.GET_credit_check_rule
1662 ( p_credit_check_rule_id => l_credit_check_rule_id
1663 , x_credit_check_rules_rec => l_credit_check_rule_rec
1664 );
1665
1666 OE_CREDIT_CHECK_UTIL.GET_System_parameters
1667 ( x_system_parameter_rec => l_system_parameter_rec
1668 );
1669
1670 IF G_debug_flag = 'Y'
1671 THEN
1672 OE_DEBUG_PUB.ADD('credit_check_level_code = '||
1673 l_credit_check_rule_rec.credit_check_level_code );
1674 END IF;
1675
1676 IF NVL(l_credit_check_rule_rec.credit_check_level_code,'ORDER')
1677 = 'ORDER'
1678 THEN
1679 OE_Credit_check_order_PVT.Check_order_credit
1680 ( p_header_rec => l_header_rec
1681 , p_calling_action => p_calling_action
1682 , p_credit_check_rule_rec => l_credit_check_rule_rec
1683 , p_system_parameter_rec => l_system_parameter_rec
1684 , p_transaction_amount => p_transaction_amount
1685 , x_msg_count => x_msg_count
1686 , x_msg_data => x_msg_data
1687 , x_cc_result_out => x_result_out
1688 , x_cc_hold_comment => x_cc_hold_comment
1689 , x_return_status => x_return_status
1690 );
1691
1692 IF G_debug_flag = 'Y'
1693 THEN
1694 OE_DEBUG_PUB.ADD('OEXPCRGB: Out of Check_order_credit ');
1695 OE_DEBUG_PUB.ADD('x_return_status = '|| x_return_status );
1696 OE_DEBUG_PUB.ADD('x_result_out =>' || x_result_out );
1697 END IF;
1698
1699 ELSIF NVL(l_credit_check_rule_rec.credit_check_level_code,'ORDER')
1700 = 'LINE'
1701 THEN
1702
1703 --8478151
1704 IF NVL(p_delayed_request, FND_API.G_FALSE) = FND_API.G_TRUE and p_calling_action = 'SHIPPING' THEN
1705 Oe_Globals.G_calling_source:= 'ONT';
1706 END IF;
1707 --8478151
1708
1709 OE_Credit_Check_lines_PVT.G_line_hold_count := 0;
1710 OE_Credit_check_lines_PVT.Check_order_lines_credit
1711 ( p_header_rec => l_header_rec
1712 , p_calling_action => p_calling_action
1713 , p_credit_check_rule_rec => l_credit_check_rule_rec
1714 , p_system_parameter_rec => l_system_parameter_rec
1715 , x_msg_count => x_msg_count
1716 , x_msg_data => x_msg_data
1717 , x_cc_result_out => x_result_out
1718 , x_cc_limit_used => l_cc_limit_used
1719 , x_cc_profile_used => l_cc_profile_used
1720 , x_return_status => x_return_status
1721 ) ;
1722
1723 IF G_debug_flag = 'Y'
1724 THEN
1725 OE_DEBUG_PUB.ADD('OEXPCRGB: Out of Check_order_lines_credit ');
1726 OE_DEBUG_PUB.ADD('x_return_status = '|| x_return_status );
1727 OE_DEBUG_PUB.ADD('x_result_out => '|| x_result_out );
1728 OE_DEBUG_PUB.ADD('line hold count =>'||OE_Credit_Check_lines_PVT.G_line_hold_count);
1729 END IF;
1730 END IF;
1731
1732 -- Moved calling_action check to outside of status check
1733 IF p_calling_action <> 'EXTERNAL' THEN
1734 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1735
1736
1737 Apply_exception_hold
1738 ( p_header_id => p_header_id
1739 , x_return_status => x_return_status
1740 );
1741
1742 IF G_debug_flag = 'Y'
1743 THEN
1744 OE_DEBUG_PUB.ADD('Apply_exception_hold x_return_status => '
1745 || x_return_status );
1746 END IF;
1747
1748 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1749 THEN
1750 x_return_status := FND_API.G_RET_STS_ERROR;
1751 ELSE
1752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1753 END IF;
1754 END IF; -- apply exception hold
1755 -- set result out to FAIL when credit check failed or
1756 -- when an exception occurred and a hold was successfully placed
1757
1758 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1759 x_result_out := 'FAIL' ;
1760 END IF;
1761 ELSE -- for the case of EXTERNAL credit check
1762 -- x_return_status is either SUCCESS, ERROR or UNEXP_ERROR
1763 -- for external. If it is is and expected error, it might be
1764 -- due to credit check failure or failure from currency conversion.
1765 -- In this case, don't attempt to place credit hold
1766 -- set return status to SUCCESS with result_out=FAIL to indicate that
1767 -- credit checking completed successfully, even though
1768 -- the result_out is failure.
1769 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1770
1771 IF G_debug_flag = 'Y'
1772 THEN
1773 OE_DEBUG_PUB.ADD( ' x_return_status = '|| x_return_status );
1774 OE_DEBUG_PUB.ADD( ' G_currency_error_msg = '||
1775 OE_Credit_Engine_GRP.G_currency_error_msg );
1776 END IF;
1777 END IF;
1778
1779 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1780 x_result_out := 'FAIL';
1781 x_return_status := FND_API.G_RET_STS_SUCCESS;
1782 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1783 x_result_out := 'FAIL';
1784 END IF;
1785 -- for success, default is PASS
1786 END IF;
1787
1788 IF G_debug_flag = 'Y'
1789 THEN
1790 OE_DEBUG_PUB.ADD('after excp holds x_return_status => '
1791 || x_return_status );
1792 OE_DEBUG_PUB.ADD('after excp holds x_result_out => '
1793 || x_result_out );
1794 OE_DEBUG_PUB.ADD('Check for notification send ' ) ;
1795 END IF;
1796
1797 IF x_result_out = 'FAIL_HOLD' AND -- Bug 4506263 FP
1798 NVL(p_calling_action, 'BOOKING') NOT IN ('EXTERNAL', 'AUTO RELEASE')
1799 THEN
1800 IF l_credit_check_rule_rec.send_hold_notifications_flag = 'Y'
1801 THEN
1802 OE_credit_check_util.send_credit_hold_ntf
1803 ( p_header_rec => l_header_rec
1804 , p_credit_hold_level => l_credit_check_rule_rec.credit_hold_level_code
1805 , p_cc_hold_comment => x_cc_hold_comment
1806 , x_return_status => x_return_status
1807 );
1808
1809 IF G_debug_flag = 'Y'
1810 THEN
1811
1812 OE_DEBUG_PUB.ADD('after call send_credit_hold_ntf ');
1813 OE_DEBUG_PUB.ADD('x_return_status = '|| x_return_status );
1814 OE_DEBUG_PUB.ADD('line hold count =>'||OE_Credit_Check_lines_PVT.G_line_hold_count);
1815 END IF;
1816
1817 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1818 THEN
1819
1820 OE_DEBUG_PUB.ADD('send failed ');
1821
1822 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1823
1824 OE_DEBUG_PUB.ADD(' after assign x_return_status = '
1825 || x_return_status );
1826 END IF;
1827
1828
1829 ELSE
1830 IF G_debug_flag = 'Y'
1831 THEN
1832 OE_DEBUG_PUB.ADD('No need for sending notifications flag OFF or ignored',1);
1833 END IF;
1834 END IF;
1835
1836 END IF;
1837
1838
1839
1840 OE_Credit_Engine_GRP.G_delayed_request := NULL ;
1841
1842 IF p_calling_action = 'EXTERNAL'
1843 THEN
1844 OE_MSG_PUB.Count_And_Get
1845 ( p_count => x_msg_count,
1846 p_data => x_msg_data
1847 );
1848 END IF;
1849
1850 IF G_debug_flag = 'Y'
1851 THEN
1852 OE_DEBUG_PUB.ADD('Final x_return_status => '|| x_return_status );
1853 OE_DEBUG_PUB.ADD('Final x_result_out => '|| x_result_out );
1854 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT Check_Credit ',1);
1855 END IF;
1856
1857 Oe_Globals.G_calling_source:= 'WSH'; --8478151
1858
1859 EXCEPTION
1860
1861 WHEN FND_API.G_EXC_ERROR THEN
1862 Oe_Globals.G_calling_source:= 'WSH'; --8478151
1863 x_return_status := FND_API.G_RET_STS_ERROR;
1864 x_result_out := 'FAIL' ;
1865 OE_MSG_PUB.Count_And_Get
1866 ( p_count => x_msg_count,
1867 p_data => x_msg_data
1868 );
1869
1870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1871 Oe_Globals.G_calling_source:= 'WSH'; --8478151
1872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1873 x_result_out := 'FAIL' ;
1874 OE_MSG_PUB.Count_And_Get
1875 ( p_count => x_msg_count,
1876 p_data => x_msg_data
1877 );
1878
1879 WHEN OTHERS THEN
1880 Oe_Globals.G_calling_source:= 'WSH'; --8478151
1881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1882 x_result_out := 'FAIL' ;
1883 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1884 THEN
1885 FND_MSG_PUB.Add_Exc_Msg
1886 ( G_PKG_NAME
1887 , 'Check_Credit'
1888 );
1889 END IF;
1890
1891 OE_MSG_PUB.Count_And_Get
1892 ( p_count => x_msg_count,
1893 p_data => x_msg_data
1894 );
1895
1896 END Check_Credit ;
1897
1898 /* Start MOAC CREDIT CHECK CHANGE */
1899 ------------------------------------------------------------------------------
1900 -- PROCEDURE : Set_context PRIVATE
1901 -- COMMENT : This procedure set the context
1902 --
1903 --------------------------------------------------------------------------------
1904 PROCEDURE Set_context
1905 IS
1906 --
1907 l_org_id NUMBER;
1908 --
1909 BEGIN
1910
1911 OE_DEBUG_PUB.ADD('OEXPCRGB: IN Set_context ' );
1912 BEGIN
1913
1914 l_org_id := mo_global.get_current_org_id;
1915
1916 IF l_org_id IS NOT NULL THEN
1917 IF G_debug_flag = 'Y' THEN
1918 oe_debug_pub.add( 'OEXPCRGB: setting single org context to '|| l_org_id , 1 );
1919 END IF;
1920
1921 MO_GLOBAL.Set_Policy_Context('S', l_org_id);
1922 ELSE
1923 IF G_debug_flag = 'Y' THEN
1924 oe_debug_pub.add('OEXPCRGB: Unexpected error in setting org context where Customer Exposure flag is ''''N', 1 );
1925 END IF;
1926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1927 END IF;
1928
1929 OE_CREDIT_CHECK_UTIL.G_org_id := l_org_id ;
1930
1931 IF l_org_id IS NULL THEN
1932 Fnd_Message.set_name('FND','MO_ORG_REQUIRED');
1933 Oe_Msg_Pub.Add;
1934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1935 END IF;
1936
1937 EXCEPTION
1938 WHEN NO_DATA_FOUND
1939 THEN
1940 l_org_id := NULL ;
1941
1942 OE_CREDIT_CHECK_UTIL.G_org_id := NULL ;
1943
1944 OE_DEBUG_PUB.ADD(' Exception Set context');
1945 END ;
1946
1947 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT Set_context ' );
1948
1949 EXCEPTION
1950 WHEN OTHERS THEN
1951 IF G_debug_flag = 'Y' THEN
1952 OE_DEBUG_PUB.ADD ('Exception : Unexpected error from Set_Context ', 1);
1953 END IF;
1954
1955 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1956 THEN
1957 FND_MSG_PUB.Add_Exc_Msg
1958 ( G_PKG_NAME
1959 , 'Set_Context'
1960 );
1961 END IF;
1962
1963 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1964
1965 END Set_context ;
1966
1967 /* End MOAC CREDIT CHECK CHANGE */
1968
1969 END OE_Credit_Engine_GRP ;