eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OE_CREDIT_ENGINE_GRP

Source


1 PACKAGE BODY OE_Credit_Engine_GRP AS
2 -- $Header: OEXPCRGB.pls 120.6.12010000.3 2008/11/14 11:52:18 msundara 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 
226 
227 ---------------------------------------------------------------------
228 -- PROCEDURE: Apply_exception_hold
229 -- COMMENT:   Apply credit check hold on the specified order
230 --            during process exceptions
231 --            when the return status is not SUCCESS
232 --            The return status will be assigned as Error
233 --            If apply hold returns error , the process will
234 --            abort and return Unexpected error
235 ----------------------------------------------------------------------
236 PROCEDURE Apply_exception_hold
237  ( p_header_id             IN  NUMBER
238  , x_return_status         OUT NOCOPY /* file.sql.39 change */ VARCHAR2
239   )
240 IS
241 
242   l_hold_exists        VARCHAR2(1)  := NULL ;
243   l_hold_result        VARCHAR2(30);
244   l_msg_count          NUMBER := 0;
245   l_msg_data           VARCHAR2(2000);
246   l_return_status      VARCHAR2(30);
247   l_hold_comment       VARCHAR2(2000);
248   l_hold_source_rec    OE_HOLDS_PVT.Hold_Source_Rec_Type :=
249           OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
250 BEGIN
251   IF G_debug_flag = 'Y'
252   THEN
253      OE_DEBUG_PUB.Add('OEXPCRGB: In Apply_exception_hold ');
254   END IF;
255 
256   l_hold_source_rec.hold_id          := 1;           -- credit hold
257   l_hold_source_rec.hold_entity_code := 'O';         -- order hold
258   l_hold_source_rec.hold_entity_id   := p_header_id; -- order header
259 
260   IF G_debug_flag = 'Y'
261   THEN
262     OE_DEBUG_PUB.Add('Call OE_HOLDS_PUB.Check_Holds');
263   END IF;
264 
265   OE_HOLDS_PUB.Check_Holds
266                       ( p_api_version    => 1.0
267                       , p_header_id      => p_header_id
268                       , p_hold_id        => 1
269                       , p_wf_item        => OE_Credit_Engine_GRP.G_cc_hold_item_type
270                       , p_wf_activity    => OE_Credit_Engine_GRP.G_cc_hold_activity_name
271                       , p_entity_code    => 'O'
272                       , p_entity_id      => p_header_id
273                       , x_result_out     => l_hold_result
274                       , x_msg_count      => l_msg_count
275                       , x_msg_data       => l_msg_data
276                       , x_return_status  => x_return_status
277                       );
278 
279   IF x_return_status = FND_API.G_RET_STS_ERROR THEN
280       RAISE FND_API.G_EXC_ERROR;
281   ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
282       RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
283   END IF;
284 
285   IF l_hold_result = FND_API.G_TRUE
286   THEN
287       OE_DEBUG_PUB.Add('OEXVCRHB: Hold already applied on Header ID:' ||
288       p_header_id );
289   ELSE
290       l_hold_comment := 'Credit checking package API Error' ;
291       l_hold_source_rec.hold_comment :=
292             NVL(OE_Credit_Engine_GRP.G_currency_error_msg, l_hold_comment) ;
293 
294       OE_DEBUG_PUB.Add('Call OE_Holds_PUB.Apply_Holds' );
295 
296       OE_Holds_PUB.Apply_Holds
297         (   p_api_version       => 1.0
298         ,   p_validation_level  => FND_API.G_VALID_LEVEL_NONE
299         ,   p_hold_source_rec   => l_hold_source_rec
300         ,   x_msg_count         => l_msg_count
301         ,   x_msg_data          => l_msg_data
302         ,   x_return_status     => x_return_status
303         );
304 
305         IF x_return_status = FND_API.G_RET_STS_ERROR THEN
306           RAISE FND_API.G_EXC_ERROR;
307         ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
308           RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
309         ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
310         OE_DEBUG_PUB.ADD('OEXPCRGB: Credit check hold applied on header_ID: '
311                        ||p_header_id, 1);
312       END IF;
313     END IF; -- Check hold exist
314 
315    IF G_debug_flag = 'Y'
316    THEN
317      OE_DEBUG_PUB.Add('OEXPCRGB: Out Apply_exception_hold ');
318    END IF;
319 
320 EXCEPTION
321 
322   WHEN OTHERS THEN
323   x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
324     IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
325       OE_MSG_PUB.Add_Exc_Msg
326       (   G_PKG_NAME
327       ,   'Apply_exception_hold'
328       );
329     END IF;
330     OE_DEBUG_PUB.ADD( SUBSTR( SQLERRM,1,300) ,1 ) ;
331 
332 
333 END Apply_Exception_hold ;
334 
335 
336 ---------------------------------------------------------------------------
337 --FUNCTION GET_GL_currency
338 --COMMENT:   Returns the SOB currency
339 
340 ---------------------------------------------------------------------------
341 FUNCTION GET_GL_currency
342 RETURN VARCHAR2
343 IS
344 
345 l_gl_currency VARCHAR2(10);
346 l_sob_id      NUMBER;
347 
348 BEGIN
349   IF G_debug_flag = 'Y'
350   THEN
351     OE_DEBUG_PUB.ADD('OEXPCRGB: In Get_GL_currency ');
352   END IF;
353 
354   BEGIN
355     l_sob_id := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID') ;
356 
357     IF G_debug_flag = 'Y'
358     THEN
359       OE_DEBUG_PUB.ADD('l_sob_id = '|| l_sob_id );
360       OE_DEBUG_PUB.ADD('GET SOB currency ');
361     END IF;
362 
363     SELECT
364       currency_code
365     INTO
366       l_gl_currency
367     FROM
368       GL_sets_of_books
369     WHERE set_of_books_id = l_sob_id ;
370 
371     IF G_debug_flag = 'Y'
372     THEN
373       OE_DEBUG_PUB.ADD('l_gl_currency = '|| l_gl_currency );
374     END IF;
375 
376    EXCEPTION
377    WHEN NO_DATA_FOUND
378    THEN
379      l_gl_currency := NULL ;
380     OE_DEBUG_PUB.ADD('EXCEPTION: NO_DATA_FOUND ');
381      l_gl_currency := NULL ;
382    WHEN TOO_MANY_ROWS
383    THEN
384      l_gl_currency := NULL ;
385     OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS');
386      l_gl_currency := NULL ;
387   END ;
388 
389   IF G_debug_flag = 'Y'
390   THEN
391     OE_DEBUG_PUB.ADD('OEXPCRGB: Out Get_GL_currency ');
392   END IF;
393 
394   RETURN(l_GL_currency);
395 
396 EXCEPTION
397   WHEN OTHERS THEN
398     IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
399       OE_MSG_PUB.Add_Exc_Msg
400       (   G_PKG_NAME
401       ,   'Get_GL_currency'
402       );
403     END IF;
404     OE_DEBUG_PUB.ADD( SUBSTR(SQLERRM,1,300) ) ;
405 END Get_GL_currency ;
406 
407 ---------------------------------------------------------------------------
408 --PROCEDURE: Credit_check_with_payment_typ
409 --COMMENT:   Main API called from verify_payment switch
410 -- 2412678 add a new input parameter
411 ---------------------------------------------------------------------------
412 PROCEDURE Credit_check_with_payment_typ
413 (  p_header_id            IN   NUMBER
414 ,  p_calling_action       IN   VARCHAR2
415 ,  p_delayed_request      IN   VARCHAR2
416 ,  p_credit_check_rule_id IN   NUMBER := NULL
417 ,  x_msg_count            OUT NOCOPY /* file.sql.39 change */  NUMBER
418 ,  x_msg_data             OUT NOCOPY /* file.sql.39 change */  VARCHAR2
419 ,  x_return_status        OUT NOCOPY /* file.sql.39 change */  VARCHAR2
420 )
421 IS
422 
423 l_epayment VARCHAR2(1) ;
424 
425 l_msg_count         NUMBER        := 0 ;
426 l_msg_data          VARCHAR2(2000):= NULL ;
427 l_result_out        VARCHAR2(100);
428 l_cc_hold_comment   VARCHAR2(2000):= NULL;
429 
430 BEGIN
431   x_return_status := FND_API.G_RET_STS_SUCCESS;
432 
433   IF G_debug_flag = 'Y'
434   THEN
435     OE_DEBUG_PUB.ADD('OEXPCRGB: IN Credit_check_with_payment_typ ',1);
436     OE_DEBUG_PUB.ADD('p_header_id = '|| p_header_id,1 );
437     OE_DEBUG_PUB.ADD('p_calling_action = ' || p_calling_action,1 );
438     OE_DEBUG_PUB.ADD('p_delayed_request = '|| p_delayed_request,1 );
439     OE_DEBUG_PUB.ADD('p_credit_check_rule_id => '|| p_credit_check_rule_id,1 );
440   END IF;
441 
442   OE_Credit_Engine_GRP.G_delayed_request    := NULL ;
443 
444   IF G_debug_flag = 'Y'
445   THEN
446     OE_DEBUG_PUB.ADD('call Check_Credit for credit checking',1);
447   END IF;
448 
449    Check_Credit
450    (      p_header_id       => p_header_id
451       ,   p_calling_action  => p_calling_action
452       ,   p_delayed_request => p_delayed_request
453       ,   p_credit_check_rule_id => p_credit_check_rule_id
454       ,   x_msg_count       => x_msg_count
455       ,   x_msg_data        => x_msg_data
456       ,   x_cc_hold_comment => l_cc_hold_comment
457       ,   x_result_out      => l_result_out
458       ,   x_return_status   => x_return_status
459    );
460 
461    IF G_debug_flag = 'Y'
462    THEN
463       OE_DEBUG_PUB.ADD('x_return_status '|| x_return_status );
464    END IF;
465 
466    IF x_return_status = FND_API.G_RET_STS_ERROR
467    THEN
468          -- Assign the status to SUCCESS as the order will
469          -- put on hold when the status  is error
470          -- GL currency conversion
471 
472           x_return_status := FND_API.G_RET_STS_SUCCESS ;
473 
474    ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
475         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
476    END IF;
477 
478   IF G_debug_flag = 'Y'
479   THEN
480    OE_DEBUG_PUB.ADD('OEXPCRGB:Final x_return_status = '|| x_return_status,1 );
481    OE_DEBUG_PUB.ADD('OEXPCRGB:l_result_out = '|| l_result_out,1 );
482    OE_DEBUG_PUB.ADD('OEXPCRGB: OUT Credit_check_with_payment_typ ',1);
483   END IF;
484 
485 EXCEPTION
486 
487     WHEN FND_API.G_EXC_ERROR THEN
488       x_return_status := FND_API.G_RET_STS_ERROR;
489     oe_debug_pub.add(' SQLERRM: '|| SQLERRM );
490       OE_MSG_PUB.Count_And_Get
491             ( p_count => l_msg_count,
492               p_data  => l_msg_data
493             );
494 
495     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
496       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
497     oe_debug_pub.add(' SQLERRM: '|| SQLERRM );
498       OE_MSG_PUB.Count_And_Get
499             ( p_count => l_msg_count,
500               p_data  => l_msg_data
501             );
502 
503     WHEN OTHERS THEN
504       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
505     oe_debug_pub.add(' SQLERRM: '|| SQLERRM );
506       IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
507       THEN
508         FND_MSG_PUB.Add_Exc_Msg
509             (   G_PKG_NAME
510             ,   'Credit_check_with_payment_typ'
511             );
512       END IF;
513 
514       OE_MSG_PUB.Count_And_Get
515             ( p_count => l_msg_count,
516               p_data  => l_msg_data
517             );
518 
519 END Credit_check_with_payment_typ ;
520 
521 --=========================================================================
522 -- PROCEDURE  : Get_Customer_Exposure   PUBLIC
523 -- PARAMETERS : p_customer_id           Customer ID
524 --            : p_site_id               Bill-to Site ID
525 --            : p_limit_curr_code       Credit limit currency code
526 --            : p_credit_check_rule_id  Credit Check Rule Id
527 --            : x_total_exposure        Credit exposure
528 --            : x_return_status         Status
529 -- COMMENT    : This procedure calculates credit exposure for given customer
530 --              or given bill-to site.
531 --             This procedure will superseed the original get_customer_exposure
532 --             API for party level changes from ONT-I
533 --             For backward compatible reasons, the original procedure
534 --             will call this procedure
535 --=========================================================================
536 PROCEDURE Get_customer_exposure
537 ( p_party_id             IN NUMBER
538 , p_customer_id           IN NUMBER
539 , p_site_id               IN NUMBER
540 , p_limit_curr_code       IN VARCHAR2
541 , p_credit_check_rule_id  IN NUMBER
542 , p_need_exposure_details IN VARCHAR2 := 'N'
543 , x_total_exposure        OUT NOCOPY /* file.sql.39 change */ NUMBER
544 , x_order_hold_amount     OUT NOCOPY /* file.sql.39 change */ NUMBER
545 , x_order_amount          OUT NOCOPY /* file.sql.39 change */ NUMBER
546 , x_ar_amount             OUT NOCOPY /* file.sql.39 change */ NUMBER
547 , x_external_amount       OUT NOCOPY /* file.sql.39 change */ NUMBER
548 , x_return_status         OUT NOCOPY /* file.sql.39 change */ VARCHAR2
549 )
550 IS
551 
552 
553 l_msg_count             NUMBER        := 0 ;
554 l_msg_data              VARCHAR2(2000):= NULL ;
555 l_credit_check_rule_rec OE_CREDIT_CHECK_UTIL.OE_CREDIT_RULES_REC_TYPE ;
556 l_system_parameters_rec OE_CREDIT_CHECK_UTIL.OE_SYSTEMS_PARAM_REC_TYPE ;
557 l_usage_curr_tbl        OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
558 l_total_exposure        NUMBER;
559 l_error_curr_tbl        OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
560 l_conversion_status     OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
561 l_return_status         VARCHAR2(50);
562 l_include_all_flag      VARCHAR2(15);
563 i                       INTEGER:=0;
564 j                       INTEGER:=0;
565 k                       INTEGER:=0;
566 f                       INTEGER:=0;
567 
568 l_start                 NUMBER:=0;
569 l_end                   NUMBER:=0;
570 
571 l_global_exposure_flag  VARCHAR2(1);
572 l_org_id                NUMBER:=0; /* MOAC CREDIT CHECK CHANGE */
573 
574 BEGIN
575  IF G_debug_flag = 'Y'
576  THEN
577   OE_DEBUG_PUB.ADD('OEXPCRGB: IN Get_Customer_Exposure ',1);
578   OE_DEBUG_PUB.ADD('p_party_id              => '|| p_party_id );
579   OE_DEBUG_PUB.ADD('p_customer_id           => '|| p_customer_id );
580   OE_DEBUG_PUB.ADD('p_site_id               => '|| p_site_id );
581   OE_DEBUG_PUB.ADD('p_limit_curr_code       => '|| p_limit_curr_code );
582   OE_DEBUG_PUB.ADD('p_credit_check_rule_id  => '|| p_credit_check_rule_id );
583   OE_DEBUG_PUB.ADD('p_need_exposure_details => '|| p_need_exposure_details );
584  END IF;
585 
586   x_return_status := FND_API.G_RET_STS_SUCCESS;
587   l_global_exposure_flag := 'N' ;
588 
589 
590   ---get credit check rule record
591   OE_CREDIT_CHECK_UTIL.GET_credit_check_rule
592   ( p_credit_check_rule_id   => p_credit_check_rule_id
593   , x_credit_check_rules_rec => l_credit_check_rule_rec
594   );
595 
596   -----Get system parameters record
597   OE_CREDIT_CHECK_UTIL.GET_System_parameters
598   ( x_system_parameter_rec=>l_system_parameters_rec
599   );
600 
601    OE_DEBUG_PUB.ADD('Check entity type ');
602 
603 -------------------- EXPOSURE -----------------------
604 
605 /* Modified the following condition to fix the bug 5650329 */
606   IF p_party_id IS NOT NULL and ( p_customer_id IS NULL ) and ( p_site_id IS NULL )
607   THEN
608     l_global_exposure_flag := 'Y' ;
609 
610     IF G_debug_flag = 'Y'
611     THEN
612       OE_DEBUG_PUB.ADD('Party level exposure ');
613       OE_DEBUG_PUB.ADD('Calling Get_global_exposure_flag ');
614       OE_DEBUG_PUB.ADD('l_global_exposure_flag = '||
615            l_global_exposure_flag );
616       OE_DEBUG_PUB.ADD('OEXPCRGB: Get usages for PARTY level ');
617      END IF;
618 
619     ----get table of usages
620     OE_CREDIT_CHECK_UTIL.Get_Usages
621     ( p_entity_type                 => 'PARTY'
622     , p_entity_id                   => p_party_id
623     , p_limit_curr_code             => p_limit_curr_code
624     , p_suppress_unused_usages_flag => 'Y'
625     , p_default_limit_flag          => NULL
626     , p_global_exposure_flag        => l_global_exposure_flag
627     , x_include_all_flag            => l_include_all_flag
628     , x_usage_curr_tbl              => l_usage_curr_tbl
629     );
630 
631    IF G_debug_flag = 'Y'
632     THEN
633        OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of OE_CREDIT_CHECK_UTIL. Get_Usages ');
634        OE_DEBUG_PUB.ADD('x_include_all_flag: '||l_include_all_flag);
635        OE_DEBUG_PUB.ADD('---------------------------------------');
636     END IF;
637 
638     ----IF l_include_all_flag is 'Y', assign it to the global variable
639     --- used later for unchecked exposure
640 
641     IF l_include_all_flag='Y'
642     THEN
643       OE_Credit_Engine_GRP.G_cust_incl_all_flag :='Y';
644     END IF;
645 
646     FOR a IN 1..l_usage_curr_tbl.COUNT
647     LOOP
648      IF G_debug_flag = 'Y'
649      THEN
650        OE_DEBUG_PUB.ADD('currency_code=: '
651               ||l_usage_curr_tbl(a).usage_curr_code);
652      END IF;
653     END LOOP;
654 --------------------------------------------------------------------------------
655     ----assign table of usages to the Global variable
656 
657     l_start:=OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT+1;
658     l_end:=OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT+l_usage_curr_tbl.COUNT;
659 
660      IF G_debug_flag = 'Y'
661      THEN
662         OE_DEBUG_PUB.ADD('l_start= '||TO_CHAR(l_start));
663         OE_DEBUG_PUB.ADD('l_end= '||TO_CHAR(l_end));
664         OE_DEBUG_PUB.ADD('IN loop for assign table of usge to Glb variable ');
665      END IF;
666 
667     FOR i IN l_start..l_end
668     LOOP
669       j:=j+1;
670       OE_Credit_Engine_GRP.G_cust_curr_tbl(i).usage_curr_code
671                  := l_usage_curr_tbl(j).usage_curr_code;
672 
673     END LOOP;
674 
675      OE_CREDIT_EXPOSURE_PVT.Get_Exposure
676       ( p_party_id                => p_party_id
677       , p_customer_id             => NULL
678       , p_site_use_id             => NULL
679       , p_header_id               => NULL
680       , p_credit_check_rule_rec   => l_credit_check_rule_rec
681       , p_system_parameters_rec   => l_system_parameters_rec
682       , p_limit_curr_code         => p_limit_curr_code
683       , p_usage_curr_tbl          => l_usage_curr_tbl
684       , p_include_all_flag        => l_include_all_flag
685       , p_global_exposure_flag    => l_global_exposure_flag
686       , p_need_exposure_details  => p_need_exposure_details
687       , x_total_exposure         => x_total_exposure
688       , x_order_amount           => x_order_amount
689       , x_order_hold_amount      => x_order_hold_amount
690       , x_ar_amount              => x_ar_amount
691       , x_return_status           => l_return_status
692       , x_error_curr_tbl          => l_error_curr_tbl
693       );
694 
695 
696       IF l_error_curr_tbl.COUNT<>0
697       THEN
698 
699         FOR k IN 1..l_error_curr_tbl.COUNT
700         LOOP
701           IF G_debug_flag = 'Y'
702           THEN
703             OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
704 !!!!!!!');
705             OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(k)
706 .usage_curr_code
707                ||' and credit limit currency '
708                ||p_limit_curr_code
709                ||' is missing for conversion type '
710               || NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1
711 );
712             OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
713 !!!!!!!');
714 
715           END IF;
716         END LOOP;
717 
718        x_return_status:='C';
719 
720       ELSIF l_return_status = FND_API.G_RET_STS_ERROR
721       THEN
722         RAISE FND_API.G_EXC_ERROR;
723       ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
724       THEN
725         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
726       END IF;
727 
728    -----------End party ----
729   ---customer level exposure
730   ELSIF p_site_id IS NULL
731   THEN
732     IF G_debug_flag = 'Y'
733     THEN
734       OE_DEBUG_PUB.ADD('Customer level exposure ');
735       OE_DEBUG_PUB.ADD('Calling Get_global_exposure_flag ');
736     END IF;
737 
738        l_global_exposure_flag :=
739        OE_CREDIT_CHECK_UTIL.Get_global_exposure_flag
740        (  p_entity_type      => 'CUSTOMER'
741         , p_entity_id        => p_customer_id
742         , p_limit_curr_code  => p_limit_curr_code
743        );
744 
745      /* Start MOAC CREDIT CHECK CHANGE */
746      IF l_global_exposure_flag = 'N'
747      THEN
748          OE_Credit_Engine_GRP.Set_context;
749      END IF;
750      /* End MOAC CREDIT CHECK CHANGE */
751 
752      IF G_debug_flag = 'Y'
753      THEN
754          OE_DEBUG_PUB.ADD('l_global_exposure_flag = '||
755            l_global_exposure_flag );
756          OE_DEBUG_PUB.ADD('OEXPCRGB: Get usages for CUSTOMER level ');
757      END IF;
758 
759     ----get table of usages
760     OE_CREDIT_CHECK_UTIL.Get_Usages
761     ( p_entity_type                 => 'CUSTOMER'
762     , p_entity_id                   => p_customer_id
763     , p_limit_curr_code             => p_limit_curr_code
764     , p_suppress_unused_usages_flag => 'Y'
765     , p_default_limit_flag          => NULL
766     , p_global_exposure_flag        => l_global_exposure_flag
767     , x_include_all_flag            => l_include_all_flag
768     , x_usage_curr_tbl              => l_usage_curr_tbl
769     );
770 
771     IF G_debug_flag = 'Y'
772     THEN
773        OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of OE_CREDIT_CHECK_UTIL. Get_Usages ');
774        OE_DEBUG_PUB.ADD('x_include_all_flag: '||l_include_all_flag);
775        OE_DEBUG_PUB.ADD('---------------------------------------');
776     END IF;
777 
778     ----IF l_include_all_flag is 'Y', assign it to the global variable
779     --- used later for unchecked exposure
780 
781     IF l_include_all_flag='Y'
782     THEN
783       OE_Credit_Engine_GRP.G_cust_incl_all_flag :='Y';
784     END IF;
785 
786 
787     FOR a IN 1..l_usage_curr_tbl.COUNT
788     LOOP
789      IF G_debug_flag = 'Y'
790      THEN
791        OE_DEBUG_PUB.ADD('currency_code=: '
792               ||l_usage_curr_tbl(a).usage_curr_code);
793      END IF;
794     END LOOP;
795 --------------------------------------------------------------------------------
796     ----assign table of usages to the Global variable
797 
798     l_start:=OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT+1;
799     l_end:=OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT+l_usage_curr_tbl.COUNT;
800 
801      IF G_debug_flag = 'Y'
802      THEN
803         OE_DEBUG_PUB.ADD('l_start= '||TO_CHAR(l_start));
804         OE_DEBUG_PUB.ADD('l_end= '||TO_CHAR(l_end));
805         OE_DEBUG_PUB.ADD('IN loop for assign table of usages to Global variable ');
806      END IF;
807 
808     FOR i IN l_start..l_end
809     LOOP
810       j:=j+1;
811       OE_Credit_Engine_GRP.G_cust_curr_tbl(i).usage_curr_code
812                  := l_usage_curr_tbl(j).usage_curr_code;
813 
814     END LOOP;
815 
816     ----pre-calculate exposure
817     IF l_credit_check_rule_rec.quick_cr_check_flag = 'Y'
818     THEN
819 
820       OE_CREDIT_EXPOSURE_PVT.Get_Exposure
821       ( p_customer_id             => p_customer_id
822       , p_site_use_id             => NULL
823       , p_header_id               => NULL
824       , p_credit_check_rule_rec   => l_credit_check_rule_rec
825       , p_system_parameters_rec   => l_system_parameters_rec
826       , p_limit_curr_code         => p_limit_curr_code
827       , p_usage_curr_tbl          => l_usage_curr_tbl
828       , p_include_all_flag        => l_include_all_flag
829       , p_global_exposure_flag    => l_global_exposure_flag
830       , p_need_exposure_details  => p_need_exposure_details
831       , x_total_exposure         => x_total_exposure
832       , x_order_amount           => x_order_amount
833       , x_order_hold_amount      => x_order_hold_amount
834       , x_ar_amount              => x_ar_amount
835       , x_return_status           => l_return_status
836       , x_error_curr_tbl          => l_error_curr_tbl
837       );
838 
839       IF l_error_curr_tbl.COUNT<>0
840       THEN
841 
842         FOR k IN 1..l_error_curr_tbl.COUNT
843         LOOP
844           IF G_debug_flag = 'Y'
845           THEN
846             OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
847             OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(k).usage_curr_code
848                ||' and credit limit currency '
849                ||p_limit_curr_code
850                ||' is missing for conversion type '
851               || NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
852             OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
853           END IF;
854         END LOOP;
855 
856        ---bug fix 2439029
857 
858        -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
859 
860        x_return_status:='C';
861 
862       ELSIF l_return_status = FND_API.G_RET_STS_ERROR
863       THEN
864         RAISE FND_API.G_EXC_ERROR;
865       ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
866       THEN
867         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
868       END IF;
869 
870     ----non pre-calculate exposure
871     ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
872     THEN
873 
874       OE_CREDIT_CHECK_UTIL.Get_order_exposure
875       ( p_header_id              => NULL
876       , p_transaction_curr_code  => NULL
877       , p_customer_id            => p_customer_id
878       , p_site_use_id            => NULL
879       , p_credit_check_rule_rec  => l_credit_check_rule_rec
880       , p_system_parameter_rec   => l_system_parameters_rec
881       , p_credit_level           => 'CUSTOMER'
882       , p_limit_curr_code        => p_limit_curr_code
883       , p_usage_curr             => l_usage_curr_tbl
884       , p_include_all_flag       => l_include_all_flag
885       ,  p_global_exposure_flag  => l_global_exposure_flag
886       , p_need_exposure_details  => p_need_exposure_details
887       , x_total_exposure         => x_total_exposure
888       , x_order_amount           => x_order_amount
889       , x_order_hold_amount      => x_order_hold_amount
890       , x_ar_amount              => x_ar_amount
891       , x_return_status          => l_return_status
892       , x_conversion_status      => l_conversion_status
893       );
894 
895 
896       IF l_conversion_status.COUNT<>0
897       THEN
898 
899         FOR k IN 1..l_conversion_status.COUNT
900         LOOP
901          IF G_debug_flag = 'Y'
902          THEN
903           OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
904           OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(k).usage_curr_code
905                 ||' and credit limit currency '
906                 ||p_limit_curr_code
907                 ||' is missing for conversion type '
908             || NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
909           OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
910          END IF;
911         END LOOP;
912 
913        ---bug fix 2439029
914 
915        -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
916 
917        x_return_status:='C';
918 
919       ELSIF l_return_status = FND_API.G_RET_STS_ERROR
920       THEN
921         RAISE FND_API.G_EXC_ERROR;
922       ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
923       THEN
924         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
925       END IF;
926 
927     END IF;
928 
929  ----------------- SITE level exposure ----------------
930 
931   -----bill-to site level exposure
932   ELSIF p_site_id IS NOT NULL
933   THEN
934       l_global_exposure_flag := 'N' ;
935 
936 /* Start MOAC CREDIT CHECK CHANGE */
937 
938     BEGIN
939       SELECT org_id
940       INTO   l_org_id
941       FROM   hz_cust_site_uses_all
942       WHERE  site_use_id = p_site_id;
943 
944     EXCEPTION
945       WHEN NO_DATA_FOUND THEN
946          IF G_debug_flag = 'Y' THEN
947             OE_DEBUG_PUB.ADD ('Exception : No org id via site_id ', 1);
948          END IF;
949 
950          IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
951          THEN
952            FND_MSG_PUB.Add_Exc_Msg
953             (   G_PKG_NAME
954             ,   'Get_Customer_Exposure'
955             );
956          END IF;
957 
958       WHEN OTHERS THEN
959          IF G_debug_flag = 'Y' THEN
960             OE_DEBUG_PUB.ADD ('Exception : Unexpected error in finding org id via site_id ', 1);
961          END IF;
962 
963          IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
964          THEN
965            FND_MSG_PUB.Add_Exc_Msg
966             (   G_PKG_NAME
967             ,   'Get_Customer_Exposure'
968             );
969          END IF;
970 
971          RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972     END ;
973 
974     MO_GLOBAL.Set_Policy_Context('S', l_org_id) ;
975 
976     OE_CREDIT_CHECK_UTIL.G_org_id := l_org_id ;
977 
978 /* End MOAC CREDIT CHECK CHANGE */
979 
980     IF G_debug_flag = 'Y'
981     THEN
982       OE_DEBUG_PUB.ADD('Site level exposure ');
983     END IF;
984 
985     ----get table of usages
986     OE_CREDIT_CHECK_UTIL.Get_Usages
987     ( p_entity_type                 => 'SITE'
988     , p_entity_id                   => p_site_id
989     , p_limit_curr_code             => p_limit_curr_code
990     , p_suppress_unused_usages_flag => 'Y'
991     , p_default_limit_flag          => NULL
992     , p_global_exposure_flag        => 'N'
993     , x_include_all_flag            => l_include_all_flag
994     , x_usage_curr_tbl              => l_usage_curr_tbl
995     );
996 
997 
998     ----IF l_include_all_flag is 'Y', assign it to the global variable
999     IF l_include_all_flag='Y'
1000     THEN
1001       OE_Credit_Engine_GRP.G_site_incl_all_flag:='Y';
1002     END IF;
1003 
1004 
1005 
1006     FOR a IN 1..l_usage_curr_tbl.COUNT
1007     LOOP
1008       IF G_debug_flag = 'Y'
1009       THEN
1010         OE_DEBUG_PUB.ADD('currency_code=: '||l_usage_curr_tbl(a).usage_curr_code);
1011       END IF;
1012     END LOOP;
1013 --------------------------------------------------------------------------------
1014 
1015     ----assign table of usages to the Global variable
1016     l_start := OE_Credit_Engine_GRP.G_site_curr_tbl.COUNT+1;
1017     l_end   := OE_Credit_Engine_GRP.G_site_curr_tbl.COUNT+l_usage_curr_tbl.COUNT;
1018 
1019     IF G_debug_flag = 'Y'
1020     THEN
1021       OE_DEBUG_PUB.ADD('l_start= '||TO_CHAR(l_start));
1022       OE_DEBUG_PUB.ADD('l_end= '||TO_CHAR(l_end));
1023       OE_DEBUG_PUB.ADD('IN loop for assign table of usages to Global variable ');
1024       OE_DEBUG_PUB.ADD('table of currencies:');
1025     END IF;
1026 
1027     FOR i IN l_start..l_end
1028     LOOP
1029 
1030       j:=j+1;
1031       OE_Credit_Engine_GRP.G_site_curr_tbl(i).usage_curr_code
1032              := l_usage_curr_tbl(j).usage_curr_code;
1033 
1034       IF G_debug_flag = 'Y'
1035       THEN
1036         OE_DEBUG_PUB.ADD('currency= '||l_usage_curr_tbl(j).usage_curr_code);
1037       END IF;
1038     END LOOP;
1039 
1040 
1041     ----pre-calculate exposure
1042     IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1043     THEN
1044 
1045       OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1046       ( p_customer_id             => p_customer_id
1047       , p_site_use_id             => p_site_id
1048       , p_header_id               => NULL
1049       , p_credit_check_rule_rec   => l_credit_check_rule_rec
1050       , p_system_parameters_rec   => l_system_parameters_rec
1051       , p_limit_curr_code         => p_limit_curr_code
1052       , p_usage_curr_tbl          => l_usage_curr_tbl
1053       , p_include_all_flag        => l_include_all_flag
1054       , p_global_exposure_flag    => 'N'
1055       , p_need_exposure_details  => p_need_exposure_details
1056       , x_total_exposure         => x_total_exposure
1057       , x_order_amount           => x_order_amount
1058       , x_order_hold_amount      => x_order_hold_amount
1059       , x_ar_amount              => x_ar_amount
1060       , x_return_status           => l_return_status
1061       , x_error_curr_tbl          => l_error_curr_tbl
1062       );
1063 
1064       IF l_error_curr_tbl.COUNT<>0
1065       THEN
1066         FOR f IN 1..l_error_curr_tbl.COUNT
1067         LOOP
1068          IF G_debug_flag = 'Y'
1069          THEN
1070            OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1071            OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code
1072                ||' and credit limit currency '
1073                ||p_limit_curr_code
1074                ||' is missing for conversion type '
1075             || NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1076            OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1077          END IF;
1078         END LOOP;
1079 
1080        ---bug fix 2439029
1081 
1082        -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1083 
1084        x_return_status:='C';
1085 
1086       ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1087       THEN
1088         RAISE FND_API.G_EXC_ERROR;
1089       ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1090       THEN
1091         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1092       END IF;
1093 
1094     ----non pre-calculate exposure
1095     ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1096     THEN
1097 
1098       OE_CREDIT_CHECK_UTIL.Get_order_exposure
1099       ( p_header_id              => NULL
1100       , p_transaction_curr_code  => NULL
1101       , p_customer_id            => p_customer_id
1102       , p_site_use_id            => p_site_id
1103       , p_credit_check_rule_rec  => l_credit_check_rule_rec
1104       , p_system_parameter_rec   => l_system_parameters_rec
1105       , p_credit_level           => 'SITE'
1106       , p_limit_curr_code        => p_limit_curr_code
1107       , p_usage_curr             => l_usage_curr_tbl
1108       , p_include_all_flag       => l_include_all_flag
1109       , p_global_exposure_flag  => 'N'
1110       , p_need_exposure_details  => p_need_exposure_details
1111       , x_total_exposure         => x_total_exposure
1112       , x_order_amount           => x_order_amount
1113       , x_order_hold_amount      => x_order_hold_amount
1114       , x_ar_amount              => x_ar_amount
1115       , x_return_status          => l_return_status
1116       , x_conversion_status      => l_conversion_status
1117       );
1118 
1119 
1120       IF l_conversion_status.COUNT<>0
1121       THEN
1122 
1123         FOR f IN 1..l_conversion_status.COUNT
1124         LOOP
1125           IF G_debug_flag = 'Y'
1126           THEN
1127             OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1128             OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code
1129                    ||' and credit limit currency '
1130                    ||p_limit_curr_code
1131                    ||' is missing for conversion type '
1132              || NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1133             OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1134          END IF;
1135         END LOOP;
1136 
1137        ---bug fix 2439029
1138 
1139        -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1140 
1141        x_return_status:='C';
1142 
1143 
1144       ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1145       THEN
1146         RAISE FND_API.G_EXC_ERROR;
1147       ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1148       THEN
1149         RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1150       END IF;
1151     END IF;
1152 
1153   END IF;
1154 
1155     IF G_debug_flag = 'Y'
1156     THEN
1157       OE_DEBUG_PUB.ADD('OEXPCRGB: OUT Get_Customer_Exposure with the status='||x_return_status);
1158     END IF;
1159 
1160 EXCEPTION
1161 
1162     WHEN FND_API.G_EXC_ERROR THEN
1163       x_return_status := FND_API.G_RET_STS_ERROR;
1164       OE_MSG_PUB.Count_And_Get
1165             ( p_count => l_msg_count,
1166               p_data  => l_msg_data
1167             );
1168 
1169     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1170       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1171       OE_MSG_PUB.Count_And_Get
1172             ( p_count => l_msg_count,
1173               p_data  => l_msg_data
1174             );
1175 
1176     WHEN OTHERS THEN
1177       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1178       IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1179       THEN
1180         FND_MSG_PUB.Add_Exc_Msg
1181             (   G_PKG_NAME
1182             ,   'Get_Customer_Exposure'
1183             );
1184       END IF;
1185 
1186       OE_MSG_PUB.Count_And_Get
1187             ( p_count => l_msg_count,
1188               p_data  => l_msg_data
1189             );
1190 
1191 END Get_Customer_Exposure;
1192 
1193 --------------------------------------------------------
1194 -- The following is the Original procedure released with ONT-G
1195 -- From the ONT-I, this procedure is overloaded with the
1196 -- party level
1197 -- for backward compatible this procedure is retained
1198 -------------------------------------------------------------
1199 PROCEDURE Get_customer_exposure
1200 ( p_customer_id           IN NUMBER
1201 , p_site_id               IN NUMBER
1202 , p_limit_curr_code       IN VARCHAR2
1203 , p_credit_check_rule_id  IN NUMBER
1204 , x_total_exposure    OUT NOCOPY /* file.sql.39 change */ NUMBER
1205 , x_return_status     OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1206 )
1207 IS
1208 
1209 l_msg_count             NUMBER        := 0 ;
1210 l_msg_data              VARCHAR2(2000):= NULL ;
1211 
1212 l_order_hold_amount NUMBER;
1213 l_ar_amount         NUMBER;
1214 l_order_amount      NUMBER;
1215 l_EXTERNAL_AMOUNT   NUMBER ;
1216 
1217 BEGIN
1218 
1219  IF G_debug_flag = 'Y'
1220  THEN
1221     OE_DEBUG_PUB.ADD(' Into Get_customer_exposure ');
1222     OE_DEBUG_PUB.ADD(' Calling Get_customer_exposure with details');
1223     OE_DEBUG_PUB.ADD('p_customer_id           => '|| p_customer_id );
1224     OE_DEBUG_PUB.ADD('p_site_id               => '|| p_site_id );
1225     OE_DEBUG_PUB.ADD('p_limit_curr_code       => '|| p_limit_curr_code );
1226     OE_DEBUG_PUB.ADD('p_credit_check_rule_id  => '|| p_credit_check_rule_id );
1227 
1228  END IF;
1229 
1230 OE_CREDIT_ENGINE_GRP.Get_customer_exposure
1231 ( p_party_id              => NULL
1232 , p_customer_id           => p_customer_id
1233 , p_site_id               => p_site_id
1234 , p_limit_curr_code       => p_limit_curr_code
1235 , p_credit_check_rule_id  => p_credit_check_rule_id
1236 , p_need_exposure_details => 'N'
1237 , x_total_exposure        => x_total_exposure
1238 , x_order_hold_amount     => l_order_hold_amount
1239 , x_order_amount          => l_order_amount
1240 , x_ar_amount             => l_ar_amount
1241 , x_external_amount       => l_external_amount
1242 , x_return_status         => x_return_status
1243 );
1244 
1245   IF G_debug_flag = 'Y'
1246   THEN
1247     OE_DEBUG_PUB.ADD(' Out x_total_exposure => '|| x_total_exposure );
1248     OE_DEBUG_PUB.ADD(' Out x_return_status => '|| x_return_status );
1249     OE_DEBUG_PUB.ADD(' Out Get_customer_exposure');
1250   END IF;
1251 
1252 EXCEPTION
1253     WHEN OTHERS THEN
1254       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1255       IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1256       THEN
1257         FND_MSG_PUB.Add_Exc_Msg
1258             (   G_PKG_NAME
1259             ,   'Get_Customer_Exposure'
1260             );
1261       END IF;
1262 
1263       OE_MSG_PUB.Count_And_Get
1264             ( p_count => l_msg_count,
1265               p_data  => l_msg_data
1266             );
1267 
1268 END Get_Customer_Exposure;
1269 
1270 
1271 --------------------------------------------------------------------------
1272 --PROCEDURE: Check_Credit
1273 --COMMENT:  main procedure for checking non electronic payments
1274 --MODIFICATION:
1275 -- 02/15/2002 Removed Default NULL for p_calling_action
1276 ---------------------------------------------------------------------------
1277 PROCEDURE Check_Credit
1278 (   p_header_id                 IN      NUMBER
1279 ,   p_calling_action            IN      VARCHAR2
1280 ,   p_delayed_request           IN      VARCHAR2 := NULL
1281 ,   p_bill_to_site_use_id       IN      NUMBER   := NULL
1282 ,   p_credit_check_rule_id      IN      NUMBER   := NULL
1283 ,   p_functional_currency_code  IN      VARCHAR2 := NULL
1284 ,   p_transaction_currency_code IN      VARCHAR2 := NULL
1285 ,   p_transaction_amount        IN      NUMBER   := NULL
1286 ,   p_org_id                    IN      NUMBER   := NULL
1287 ,   x_cc_hold_comment           OUT NOCOPY /* file.sql.39 change */     VARCHAR2
1288 ,   x_msg_count                 OUT NOCOPY /* file.sql.39 change */     NUMBER
1289 ,   x_msg_data                  OUT NOCOPY /* file.sql.39 change */     VARCHAR2
1290 ,   x_result_out                OUT NOCOPY /* file.sql.39 change */     VARCHAR2
1291 ,   x_return_status             OUT NOCOPY /* file.sql.39 change */     VARCHAR2
1292 )
1293 IS
1294 
1295 l_msg_count              NUMBER        := 0 ;
1296 l_msg_data               VARCHAR2(2000):= NULL ;
1297 l_credit_check_rule_id   NUMBER;
1298 l_credit_check_rule_rec  OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type ;
1299 l_system_parameter_rec   OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type ;
1300 l_cc_limit_used          NUMBER;
1301 l_cc_profile_used        VARCHAR2(30);
1302 l_header_rec             OE_Order_PUB.Header_Rec_Type;
1303 
1304 BEGIN
1305 
1306   x_return_status := FND_API.G_RET_STS_SUCCESS;
1307 
1308   IF G_debug_flag = 'Y'
1309   THEN
1310     OE_DEBUG_PUB.ADD('OEXPCRGB: IN Check_Credit ',1);
1311     OE_DEBUG_PUB.ADD('Input parameters list ');
1312     OE_DEBUG_PUB.ADD('p_header_id = '|| p_header_id,1 );
1313     OE_DEBUG_PUB.ADD('p_delayed_request = '|| p_delayed_request,1 );
1314     OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action,1 );
1315     OE_DEBUG_PUB.ADD('p_credit_check_rule_id => '|| p_credit_check_rule_id );
1316   END IF;
1317 
1318   OE_Credit_Engine_GRP.G_delayed_request    := NULL ;
1319   OE_Credit_Engine_GRP.G_currency_error_msg := NULL ;
1320   OE_Credit_Engine_GRP.G_delayed_request    := p_delayed_request ;
1321 
1322 --ER#7479609 start
1323   IF p_calling_action in ('AUTO HOLD','AUTO RELEASE')  THEN
1324    OE_Verify_Payment_PUB.G_init_calling_action := p_calling_action;
1325   END IF;
1326 --ER#7479609 end
1327 
1328 --bug# 4967828
1329   IF p_org_id IS NOT NULL THEN
1330      OE_CREDIT_CHECK_UTIL.G_org_id := p_org_id ;
1331   END IF;
1332 
1333   -- Get Gl currency if calling action is not EXTERNAL
1334   --
1335   IF NVL(p_calling_action, 'BOOKING') <> 'EXTERNAL' THEN
1336     OE_Credit_Engine_GRP.GL_currency := OE_Credit_Engine_GRP.GET_GL_currency;
1337 
1338     OE_Header_UTIL.Query_Row
1339         (p_header_id            => p_header_id
1340         ,x_header_rec           => l_header_rec
1341         );
1342 
1343     --bug# 4967828
1344     IF p_org_id IS NULL THEN
1345        OE_CREDIT_CHECK_UTIL.G_org_id := l_header_rec.org_id ;
1346     END IF;
1347 
1348     IF G_debug_flag = 'Y'
1349     THEN
1350       OE_DEBUG_PUB.ADD('after query header ');
1351       OE_DEBUG_PUB.ADD(' ');
1352       OE_DEBUG_PUB.ADD('======================================');
1353       OE_DEBUG_PUB.ADD('Header ID = '|| l_header_rec.header_id );
1354       OE_DEBUG_PUB.ADD('order_category_code = '||
1355          l_header_rec.order_category_code );
1356       OE_DEBUG_PUB.ADD('Booked flag = '||
1357          l_header_rec.booked_flag );
1358       OE_DEBUG_PUB.ADD('Order number = '||
1359          l_header_rec.order_number );
1360       OE_DEBUG_PUB.ADD('Credit crad approval date = '||
1361          l_header_rec.credit_card_approval_date );
1362       OE_DEBUG_PUB.ADD('payment_term_id = ' ||
1363       l_header_rec.payment_term_id );
1364       OE_DEBUG_PUB.ADD('order_type_id = '||
1365       l_header_rec.order_type_id );
1366       OE_DEBUG_PUB.ADD(' ');
1367       OE_DEBUG_PUB.ADD('======================================');
1368    END IF;
1369 
1370     IF p_credit_check_rule_id is NULL or
1371          NVL(p_credit_check_rule_id,0) = 0
1372     THEN
1373 
1374        Get_Credit_Check_Rule_ID
1375        ( p_calling_action        => p_calling_action
1376        , p_order_type_id         => l_header_rec.order_type_id
1377        , x_credit_rule_id        => l_credit_check_rule_id
1378       );
1379     ELSE
1380      l_credit_check_rule_id := p_credit_check_rule_id;
1381     END IF;
1382 
1383   ELSE
1384     -- External credit checking call
1385     l_credit_check_rule_id := p_credit_check_rule_id;
1386     OE_CREDIT_ENGINE_GRP.GL_Currency := p_functional_currency_code;
1387     l_header_rec.header_id := NULL;
1388     l_header_rec.transactional_curr_code := p_transaction_currency_code;
1389     l_header_rec.invoice_to_org_id := p_bill_to_site_use_id;
1390   END IF;
1391 
1392     IF G_debug_flag = 'Y'
1393     THEN
1394      OE_DEBUG_PUB.ADD(' GL_CURRENCY after = '|| OE_Credit_Engine_GRP.GL_currency );
1395      OE_DEBUG_PUB.ADD('l_credit_check_rule_id = '|| l_credit_check_rule_id );
1396     END IF;
1397 
1398   ----------------------- start Processing --------------------
1399 
1400   IF l_credit_check_rule_id is NULL
1401   THEN
1402     IF G_debug_flag = 'Y'
1403     THEN
1404       OE_DEBUG_PUB.ADD(' No credit check attached, return ');
1405     END IF;
1406     RETURN ;
1407   END IF;
1408 
1409   -- Initialize the G_cc_hold_item_type and G_cc_hold_activity_name
1410   -- global variables
1411   Set_G_CC_Hold_Info;
1412 
1413   OE_CREDIT_CHECK_UTIL.GET_credit_check_rule
1414   ( p_credit_check_rule_id   => l_credit_check_rule_id
1415   , x_credit_check_rules_rec => l_credit_check_rule_rec
1416   );
1417 
1418   OE_CREDIT_CHECK_UTIL.GET_System_parameters
1419   ( x_system_parameter_rec => l_system_parameter_rec
1420   );
1421 
1422   IF G_debug_flag = 'Y'
1423   THEN
1424      OE_DEBUG_PUB.ADD('credit_check_level_code = '||
1425        l_credit_check_rule_rec.credit_check_level_code );
1426   END IF;
1427 
1428   IF NVL(l_credit_check_rule_rec.credit_check_level_code,'ORDER')
1429           = 'ORDER'
1430   THEN
1431     OE_Credit_check_order_PVT.Check_order_credit
1432     ( p_header_rec            => l_header_rec
1433     , p_calling_action        => p_calling_action
1434     , p_credit_check_rule_rec => l_credit_check_rule_rec
1435     , p_system_parameter_rec  => l_system_parameter_rec
1436     , p_transaction_amount    => p_transaction_amount
1437     , x_msg_count             => x_msg_count
1438     , x_msg_data              => x_msg_data
1439     , x_cc_result_out         => x_result_out
1440     , x_cc_hold_comment       => x_cc_hold_comment
1441     , x_return_status         => x_return_status
1442     );
1443 
1444     IF G_debug_flag = 'Y'
1445     THEN
1446       OE_DEBUG_PUB.ADD('OEXPCRGB: Out of Check_order_credit ');
1447       OE_DEBUG_PUB.ADD('x_return_status = '|| x_return_status );
1448       OE_DEBUG_PUB.ADD('x_result_out =>' || x_result_out );
1449     END IF;
1450 
1451   ELSIF NVL(l_credit_check_rule_rec.credit_check_level_code,'ORDER')
1452           = 'LINE'
1453   THEN
1454     OE_Credit_Check_lines_PVT.G_line_hold_count := 0;
1455     OE_Credit_check_lines_PVT.Check_order_lines_credit
1456     ( p_header_rec            => l_header_rec
1457     , p_calling_action        => p_calling_action
1458     , p_credit_check_rule_rec => l_credit_check_rule_rec
1459     , p_system_parameter_rec  => l_system_parameter_rec
1460     , x_msg_count             => x_msg_count
1461     , x_msg_data              => x_msg_data
1462     , x_cc_result_out         => x_result_out
1463     , x_cc_limit_used         => l_cc_limit_used
1464     , x_cc_profile_used       => l_cc_profile_used
1465     , x_return_status         => x_return_status
1466     ) ;
1467 
1468     IF G_debug_flag = 'Y'
1469     THEN
1470       OE_DEBUG_PUB.ADD('OEXPCRGB: Out of Check_order_lines_credit ');
1471       OE_DEBUG_PUB.ADD('x_return_status = '|| x_return_status );
1472       OE_DEBUG_PUB.ADD('x_result_out => '|| x_result_out );
1473       OE_DEBUG_PUB.ADD('line hold count =>'||OE_Credit_Check_lines_PVT.G_line_hold_count);
1474     END IF;
1475   END IF;
1476 
1477   -- Moved calling_action check to outside of status check
1478   IF p_calling_action <> 'EXTERNAL' THEN
1479     IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1480 
1481 
1482       Apply_exception_hold
1483         ( p_header_id            => p_header_id
1484         , x_return_status        => x_return_status
1485         );
1486 
1487        IF G_debug_flag = 'Y'
1488        THEN
1489          OE_DEBUG_PUB.ADD('Apply_exception_hold  x_return_status => '
1490               || x_return_status );
1491        END IF;
1492 
1493       IF x_return_status = FND_API.G_RET_STS_SUCCESS
1494       THEN
1495         x_return_status := FND_API.G_RET_STS_ERROR;
1496       ELSE
1497         x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1498       END IF;
1499     END IF;  -- apply exception hold
1500     -- set result out to FAIL when credit check failed or
1501     -- when an exception occurred and a hold was successfully placed
1502 
1503     IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1504       x_result_out := 'FAIL' ;
1505     END IF;
1506   ELSE  -- for the case of EXTERNAL credit check
1507     -- x_return_status is either SUCCESS, ERROR or UNEXP_ERROR
1508     -- for external. If it is is and expected error, it might be
1509     -- due to credit check failure or failure from currency conversion.
1510     -- In this case, don't attempt to place credit hold
1511     -- set return status to SUCCESS with result_out=FAIL to indicate that
1512     -- credit checking completed successfully, even though
1513     -- the result_out is failure.
1514     IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1515 
1516       IF G_debug_flag = 'Y'
1517       THEN
1518          OE_DEBUG_PUB.ADD( ' x_return_status = '|| x_return_status );
1519          OE_DEBUG_PUB.ADD( ' G_currency_error_msg = '||
1520               OE_Credit_Engine_GRP.G_currency_error_msg );
1521        END IF;
1522     END IF;
1523 
1524     IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1525       x_result_out := 'FAIL';
1526       x_return_status := FND_API.G_RET_STS_SUCCESS;
1527     ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1528       x_result_out := 'FAIL';
1529     END IF;
1530     -- for success, default is PASS
1531   END IF;
1532 
1533   IF G_debug_flag = 'Y'
1534   THEN
1535      OE_DEBUG_PUB.ADD('after excp holds  x_return_status => '
1536             || x_return_status );
1537      OE_DEBUG_PUB.ADD('after excp holds x_result_out => '
1538             || x_result_out );
1539      OE_DEBUG_PUB.ADD('Check for notification send ' ) ;
1540    END IF;
1541 
1542   IF x_result_out = 'FAIL_HOLD' AND -- Bug 4506263 FP
1543       NVL(p_calling_action, 'BOOKING') NOT IN ('EXTERNAL', 'AUTO RELEASE')
1544   THEN
1545     IF  l_credit_check_rule_rec.send_hold_notifications_flag = 'Y'
1546     THEN
1547       OE_credit_check_util.send_credit_hold_ntf
1548       ( p_header_rec        => l_header_rec
1549       , p_credit_hold_level => l_credit_check_rule_rec.credit_hold_level_code
1550       , p_cc_hold_comment   => x_cc_hold_comment
1551       , x_return_status     => x_return_status
1552       );
1553 
1554       IF G_debug_flag = 'Y'
1555       THEN
1556 
1557         OE_DEBUG_PUB.ADD('after call send_credit_hold_ntf ');
1558         OE_DEBUG_PUB.ADD('x_return_status = '|| x_return_status );
1559         OE_DEBUG_PUB.ADD('line hold count =>'||OE_Credit_Check_lines_PVT.G_line_hold_count);
1560       END IF;
1561 
1562        IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1563        THEN
1564 
1565          OE_DEBUG_PUB.ADD('send failed ');
1566 
1567          x_return_status := FND_API.G_RET_STS_SUCCESS ;
1568 
1569         OE_DEBUG_PUB.ADD(' after assign x_return_status = '
1570                 || x_return_status );
1571        END IF;
1572 
1573 
1574      ELSE
1575        IF G_debug_flag = 'Y'
1576        THEN
1577           OE_DEBUG_PUB.ADD('No need for sending notifications flag OFF or ignored',1);
1578        END IF;
1579      END IF;
1580 
1581   END IF;
1582 
1583 
1584 
1585   OE_Credit_Engine_GRP.G_delayed_request    := NULL ;
1586 
1587   IF p_calling_action = 'EXTERNAL'
1588   THEN
1589     OE_MSG_PUB.Count_And_Get
1590     ( p_count => x_msg_count,
1591       p_data  => x_msg_data
1592     );
1593   END IF;
1594 
1595   IF G_debug_flag = 'Y'
1596   THEN
1597     OE_DEBUG_PUB.ADD('Final x_return_status => '|| x_return_status );
1598     OE_DEBUG_PUB.ADD('Final x_result_out => '|| x_result_out );
1599     OE_DEBUG_PUB.ADD('OEXPCRGB: OUT Check_Credit ',1);
1600   END IF;
1601 
1602 EXCEPTION
1603 
1604     WHEN FND_API.G_EXC_ERROR THEN
1605       x_return_status := FND_API.G_RET_STS_ERROR;
1606        x_result_out := 'FAIL' ;
1607       OE_MSG_PUB.Count_And_Get
1608             ( p_count => x_msg_count,
1609               p_data  => x_msg_data
1610             );
1611 
1612     WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1613       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1614        x_result_out := 'FAIL' ;
1615       OE_MSG_PUB.Count_And_Get
1616             ( p_count => x_msg_count,
1617               p_data  => x_msg_data
1618             );
1619 
1620     WHEN OTHERS THEN
1621       x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1622        x_result_out := 'FAIL' ;
1623       IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1624       THEN
1625         FND_MSG_PUB.Add_Exc_Msg
1626             (   G_PKG_NAME
1627             ,   'Check_Credit'
1628             );
1629       END IF;
1630 
1631       OE_MSG_PUB.Count_And_Get
1632             ( p_count => x_msg_count,
1633               p_data  => x_msg_data
1634             );
1635 
1636 END Check_Credit ;
1637 
1638 /* Start MOAC CREDIT CHECK CHANGE */
1639 ------------------------------------------------------------------------------
1640 --  PROCEDURE  : Set_context                 PRIVATE
1641 --  COMMENT    : This procedure set the context
1642 --
1643 --------------------------------------------------------------------------------
1644 PROCEDURE Set_context
1645 IS
1646 --
1647 l_org_id        NUMBER;
1648 --
1649 BEGIN
1650 
1651   OE_DEBUG_PUB.ADD('OEXPCRGB: IN Set_context ' );
1652   BEGIN
1653 
1654     l_org_id :=  mo_global.get_current_org_id;
1655 
1656     IF l_org_id IS NOT NULL THEN
1657        IF G_debug_flag = 'Y' THEN
1658           oe_debug_pub.add(  'OEXPCRGB: setting single org context to '|| l_org_id , 1 );
1659        END IF;
1660 
1661        MO_GLOBAL.Set_Policy_Context('S', l_org_id);
1662     ELSE
1663        IF G_debug_flag = 'Y' THEN
1664           oe_debug_pub.add('OEXPCRGB: Unexpected error in setting org context where Customer Exposure flag is ''''N', 1 );
1665        END IF;
1666        RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1667     END IF;
1668 
1669     OE_CREDIT_CHECK_UTIL.G_org_id := l_org_id ;
1670 
1671     IF l_org_id IS NULL THEN
1672        Fnd_Message.set_name('FND','MO_ORG_REQUIRED');
1673        Oe_Msg_Pub.Add;
1674        RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1675     END IF;
1676 
1677   EXCEPTION
1678   WHEN NO_DATA_FOUND
1679   THEN
1680     l_org_id := NULL ;
1681 
1682     OE_CREDIT_CHECK_UTIL.G_org_id := NULL ;
1683 
1684     OE_DEBUG_PUB.ADD(' Exception Set context');
1685   END ;
1686 
1687   OE_DEBUG_PUB.ADD('OEXPCRGB: OUT Set_context ' );
1688 
1689 EXCEPTION
1690   WHEN OTHERS THEN
1691     IF G_debug_flag = 'Y' THEN
1692        OE_DEBUG_PUB.ADD ('Exception : Unexpected error from Set_Context ', 1);
1693     END IF;
1694 
1695     IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1696     THEN
1697        FND_MSG_PUB.Add_Exc_Msg
1698        (   G_PKG_NAME
1699        ,   'Set_Context'
1700        );
1701     END IF;
1702 
1703     RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1704 
1705 END Set_context ;
1706 
1707 /* End MOAC CREDIT CHECK CHANGE */
1708 
1709 END OE_Credit_Engine_GRP ;