[Home] [Help]
PACKAGE BODY: APPS.OE_CREDIT_INTERFACE_UTIL
Source
1 PACKAGE BODY OE_CREDIT_INTERFACE_UTIL AS
2 -- $Header: OEXUCERB.pls 120.2.12010000.2 2008/08/04 15:02:04 amallik ship $
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| OEXUCERB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Package Spec of OE_CREDIT_INTERFACE_UTIL |
13 --| This package body contains some utility procedures for handling |
14 --| exposure amount from receivable interface table. |
15 --| |
16 --| PROCEDURE LIST |
17 --| Get_Exposure_Amount |
18 --| |
19 --| HISTORY |
20 --| Aug-01-2006 Initial creation |
21 --+=======================================================================+
22
23 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_CREDIT_INTERFACE_UTIL';
24 G_DEBUG_FLAG VARCHAR2(1) := NVL(oe_credit_check_util.check_debug_flag , 'N' ) ;
25
26 --===============================================================================
27 -- PROCEDURE : Get_exposure_amount
28 -- Comments : Return the overall exposure amount in RA_INTERFACE_LINES_ALL table
29 --===============================================================================
30 PROCEDURE Get_exposure_amount
31 ( p_header_id IN NUMBER
32 , p_customer_id IN NUMBER
33 , p_site_use_id IN NUMBER
34 , p_credit_check_rule_rec IN OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
35 , p_system_parameter_rec IN OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
36 , p_credit_level IN VARCHAR2
37 , p_limit_curr_code IN VARCHAR2
38 , p_usage_curr IN OE_CREDIT_CHECK_UTIL.curr_tbl_type
39 , p_global_exposure_flag IN VARCHAR2 := 'N'
40 , x_exposure_amount OUT NOCOPY NUMBER
41 , x_conversion_status OUT NOCOPY OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE
42 , x_return_status OUT NOCOPY VARCHAR2
43 )
44 IS
45
46 l_header_id NUMBER := NVL(p_header_id, 0) ;
47 l_customer_id NUMBER := NVL(p_customer_id, 0) ;
48 l_site_use_id NUMBER := NVL(p_site_use_id, 0) ;
49
50 l_include_tax_flag VARCHAR2(1) := NVL(p_credit_check_rule_rec.include_tax_flag,'N') ;
51 l_freight_charges_flag VARCHAR2(1) := NVL(p_credit_check_rule_rec.incl_freight_charges_flag,'N') ;
52 l_include_returns_flag VARCHAR2(1) := NVL(p_credit_check_rule_rec.include_returns_flag,'N');
53 l_include_uninvoiced_flag VARCHAR2(1) := NVL(p_credit_check_rule_rec.uninvoiced_orders_flag,'N');
54
55 l_org_id NUMBER := OE_CREDIT_CHECK_UTIL.G_ORG_ID ;
56
57 l_total_exposure NUMBER := 0 ;
58
59 l_total_on_order NUMBER := 0 ;
60 l_total_on_return NUMBER := 0 ;
61 l_total_on_freight1 NUMBER := 0 ;
62 l_total_on_freight2 NUMBER := 0 ;
63
64 l_cum_total_on_order NUMBER := 0 ;
65 l_cum_total_on_return NUMBER := 0 ;
66 l_cum_total_on_freight1 NUMBER := 0 ;
67 l_cum_total_on_freight2 NUMBER := 0 ;
68
69 l_usage_total_exposure NUMBER := 0 ;
70 l_limit_total_exposure NUMBER := 0 ;
71
72 l_current_usage_cur VARCHAR2(100) ;
73
74 ---------------------------------------------------------------
75 -- Cursor definitions
76 -- a) In order related cursors, use l_header_id instead of
77 -- p_header_id. This is done to remove the NVL on p_header_id
78 -- as p_header_id will be coming in as NULL for the
79 -- exposure reports.
80 ---------------------------------------------------------------
81 --------------------- START exposure amount cursors -----------
82 ---------------------------------------------------------------
83 -- CUSTOMER/SITE LEVEL CURSORS
84 ---------------------------------------------------------------
85
86 ---CUSTOMER/SITE REGULAR ORDERS
87 CURSOR cust_reg_orders (p_curr_code IN VARCHAR2 default NULL) IS
88 SELECT SUM (
89 ( NVL( rl.amount, 0 ) )
90 + DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
91 NVL(l.tax_value,0), 0 ), 0 )
92 )
93 FROM oe_order_lines l
94 , oe_order_headers_all h
95 , ra_interface_lines_all rl
96 WHERE rl.orig_system_bill_customer_id = l_customer_id
97 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462
98 AND h.header_id = l.header_id
99 AND h.org_id = l.org_id
100 AND NVL( l.invoiced_quantity, 0 ) <> 0
101 AND l.line_category_code = 'ORDER'
102 AND h.booked_flag = 'Y'
103 AND h.header_id <> l_header_id
104 AND h.transactional_curr_code = p_curr_code
105 AND nvl(rl.interface_status, '~') <> 'P'
106 AND rl.interface_line_context = 'ORDER ENTRY'
107 AND rl.interface_line_attribute1 = h.order_number
108 AND rl.interface_line_attribute6 = l.line_id
109 AND (EXISTS
110 (SELECT NULL
111 FROM oe_payment_types_all pt
112 WHERE pt.payment_type_code = NVL(l.payment_type_code,
113 NVL(h.payment_type_code, 'BME'))
114 AND pt.credit_check_flag = 'Y'
115 AND NVL(pt.org_id, -99) = l_org_id)
116 OR
117 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
118
119 ---CUSTOMER/SITE RETURN ORDERS
120 CURSOR cust_reg_orders_return(p_curr_code IN VARCHAR2 default NULL) IS
121 SELECT SUM (
122 ( DECODE( SIGN (NVL( rl.quantity_ordered, 0 )), -1, (+1), (-1) ) * NVL( rl.amount, 0 ) )
123 + DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
124 NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0), 0 )
125 )
126 FROM oe_order_lines l
127 , oe_order_headers_all h
128 , ra_interface_lines_all rl
129 WHERE rl.orig_system_bill_customer_id = l_customer_id
130 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462
131 AND h.header_id = l.header_id
132 AND h.org_id = l.org_id
133 AND NVL( l.invoiced_quantity, 0 ) <> 0
134 AND l.line_category_code = 'RETURN'
135 AND h.booked_flag = 'Y'
136 AND h.header_id <> l_header_id
137 AND h.transactional_curr_code = p_curr_code
138 AND nvl(rl.interface_status, '~') <> 'P'
139 AND rl.interface_line_context = 'ORDER ENTRY'
140 AND rl.interface_line_attribute1 = h.order_number
141 AND rl.interface_line_attribute6 = l.line_id
142 AND (EXISTS
143 (SELECT NULL
144 FROM oe_payment_types_all pt
145 WHERE pt.payment_type_code = NVL(l.payment_type_code,
146 NVL(h.payment_type_code, 'BME'))
147 AND pt.credit_check_flag = 'Y'
148 AND NVL(pt.org_id, -99) = l_org_id)
149 OR
150 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
151
152 ---CUSTOMER/SITE FREIGHT ORDERS
153 CURSOR cust_reg_orders_freight1 (p_curr_code IN VARCHAR2 default null) IS
154 SELECT SUM( NVL( rl.amount, 0 ))
155 FROM oe_price_adjustments p
156 , oe_order_lines l
157 , oe_order_headers_all h
158 , ra_interface_lines_all rl
159 WHERE rl.orig_system_bill_customer_id = l_customer_id
160 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462
161 AND p.line_id = l.line_id
162 AND NVL( l.invoiced_quantity, 0 ) <> 0
163 AND p.header_id = l.header_id
164 AND p.header_id = h.header_id
165 AND h.header_id = l.header_id
166 AND h.org_id = l.org_id
167 AND h.booked_flag = 'Y'
168 AND p.applied_flag = 'Y'
169 AND p.list_line_type_code = 'FREIGHT_CHARGE'
170 AND h.order_category_code IN ('ORDER','MIXED','RETURN')
171 AND NVL(p.invoiced_flag, 'N') = 'Y'
172 AND h.transactional_curr_code = p_curr_code
173 AND h.header_id <> l_header_id
174 AND nvl(rl.interface_status, '~') <> 'P'
175 AND rl.interface_line_context = 'ORDER ENTRY'
176 AND rl.interface_line_attribute1 = h.order_number
177 AND rl.interface_line_attribute6 = p.price_adjustment_id
178 AND (EXISTS
179 (SELECT NULL
180 FROM oe_payment_types_all pt
181 WHERE pt.payment_type_code = NVL(l.payment_type_code,
182 NVL(h.payment_type_code, 'BME'))
183 AND pt.credit_check_flag = 'Y'
184 AND NVL(pt.org_id, -99) = l_org_id)
185 OR
186 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
187
188 CURSOR cust_reg_orders_freight2 (p_curr_code IN VARCHAR2 default null) IS
189 SELECT SUM( NVL( rl.amount, 0 ))
190 FROM oe_price_adjustments p
191 , oe_order_headers_all h
192 , ra_interface_lines_all rl
193 WHERE rl.orig_system_bill_customer_id = l_customer_id
194 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462
195 AND p.line_id IS NULL
196 AND p.header_id = h.header_id
197 AND h.booked_flag = 'Y'
198 AND p.applied_flag = 'Y'
199 AND p.list_line_type_code = 'FREIGHT_CHARGE'
200 AND h.order_category_code IN ('ORDER','MIXED','RETURN')
201 AND NVL(p.invoiced_flag, 'N') = 'Y'
202 AND h.transactional_curr_code = p_curr_code
203 AND h.header_id <> l_header_id
204 AND nvl(rl.interface_status, '~') <> 'P'
205 AND rl.interface_line_context = 'ORDER ENTRY'
206 AND rl.interface_line_attribute1 = h.order_number
207 AND rl.interface_line_attribute6 = p.price_adjustment_id
208 AND EXISTS
209 (SELECT NULL
210 FROM oe_payment_types_all pt,
211 oe_order_lines l
212 WHERE pt.credit_check_flag = 'Y'
213 AND l.header_id = h.header_id
214 AND l.org_id = pt.org_id
215 AND NVL(pt.org_id, -99) = l_org_id
216 AND pt.payment_type_code =
217 DECODE(l.payment_type_code, NULL,
218 DECODE(h.payment_type_code, NULL, pt.payment_type_code,
219 h.payment_type_code),
220 l.payment_type_code));
221
222 ---------------------------------------------------------------
223 -- GLOBAL EXPOSURE CURSORS
224 -------------------------------------------------------------
225
226 ---GLOBAL REGULAR ORDERS
227 CURSOR cust_glb_orders (p_curr_code IN VARCHAR2 default NULL) IS
228 SELECT SUM (
229 ( NVL( rl.amount, 0 ) )
230 + DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
231 NVL(l.tax_value,0), 0 ), 0 )
232 )
233 FROM oe_order_lines_all l
234 , oe_order_headers_all h
235 , ra_interface_lines_all rl
236 WHERE rl.orig_system_bill_customer_id = l_customer_id
237 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462
238 AND h.header_id = l.header_id
239 AND NVL( l.invoiced_quantity, 0 ) <> 0
240 AND l.line_category_code = 'ORDER'
241 AND h.booked_flag = 'Y'
242 AND h.header_id <> l_header_id
243 AND h.transactional_curr_code = p_curr_code
244 AND nvl(rl.interface_status, '~') <> 'P'
245 AND rl.interface_line_context = 'ORDER ENTRY'
246 AND rl.interface_line_attribute1 = h.order_number
247 AND rl.interface_line_attribute6 = l.line_id
248 AND (EXISTS
249 (SELECT NULL
250 FROM oe_payment_types_all pt
251 WHERE pt.payment_type_code = NVL(l.payment_type_code,
252 NVL(h.payment_type_code, 'BME'))
253 AND pt.credit_check_flag = 'Y'
254 AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
255 OR
256 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
257
258 ---GLOBAL RETURN ORDERS
259 CURSOR cust_glb_orders_return (p_curr_code IN VARCHAR2 default NULL) IS
260 SELECT SUM (
261 ( DECODE( SIGN (NVL( rl.quantity_ordered, 0 )), -1, (+1), (-1) ) * NVL( rl.amount, 0 ) )
262 + DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
263 NVL(DECODE(l.line_category_code,'RETURN',(-1)*l.tax_value,l.tax_value),0),0), 0 )
264 )
265 FROM oe_order_lines_all l
266 , oe_order_headers_all h
267 , ra_interface_lines_all rl
268 WHERE rl.orig_system_bill_customer_id = l_customer_id
269 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462
270 AND h.header_id = l.header_id
271 AND NVL( l.invoiced_quantity, 0 ) <> 0
272 AND l.line_category_code = 'RETURN'
273 AND h.booked_flag = 'Y'
274 AND h.header_id <> l_header_id
275 AND h.transactional_curr_code = p_curr_code
276 AND nvl(rl.interface_status, '~') <> 'P'
277 AND rl.interface_line_context = 'ORDER ENTRY'
278 AND rl.interface_line_attribute1 = h.order_number
279 AND rl.interface_line_attribute6 = l.line_id
280 AND (EXISTS
281 (SELECT NULL
282 FROM oe_payment_types_all pt
283 WHERE pt.payment_type_code = NVL(l.payment_type_code,
284 NVL(h.payment_type_code, 'BME'))
285 AND pt.credit_check_flag = 'Y'
286 AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
287 OR
288 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
289
290 ---GLOBAL FREIGHT ORDERS
291 CURSOR cust_glb_orders_freight1 (p_curr_code IN VARCHAR2 default null) IS
292 SELECT SUM( NVL( rl.amount, 0 ))
293 FROM oe_price_adjustments p
294 , oe_order_lines_all l
295 , oe_order_headers_all h
296 , ra_interface_lines_all rl
297 WHERE rl.orig_system_bill_customer_id = l_customer_id
298 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462
299 AND p.line_id = l.line_id
300 AND NVL( l.invoiced_quantity, 0 ) <> 0
301 AND p.header_id = l.header_id
302 AND p.header_id = h.header_id
303 AND h.header_id = l.header_id
304 AND h.booked_flag = 'Y'
305 AND p.applied_flag = 'Y'
306 AND p.list_line_type_code = 'FREIGHT_CHARGE'
307 AND NVL(p.invoiced_flag, 'N') = 'Y'
308 AND h.transactional_curr_code = p_curr_code
309 AND h.header_id <> l_header_id
310 AND nvl(rl.interface_status, '~') <> 'P'
311 AND rl.interface_line_context = 'ORDER ENTRY'
312 AND rl.interface_line_attribute1 = h.order_number
313 AND rl.interface_line_attribute6 = p.price_adjustment_id
314 AND (EXISTS
315 (SELECT NULL
316 FROM oe_payment_types_all pt
317 WHERE pt.payment_type_code = NVL(l.payment_type_code,
318 NVL(h.payment_type_code, 'BME'))
319 AND pt.credit_check_flag = 'Y'
320 AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
321 OR
322 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
323
324 CURSOR cust_glb_orders_freight2 (p_curr_code IN VARCHAR2 default null) IS
325 SELECT SUM( NVL( rl.amount, 0 ))
326 FROM oe_price_adjustments p
327 , oe_order_headers_all h
328 , ra_interface_lines_all rl
329 WHERE rl.orig_system_bill_customer_id = l_customer_id
330 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(p_site_use_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462
331 AND p.line_id IS NULL
332 AND p.header_id = h.header_id
333 AND h.booked_flag = 'Y'
334 AND p.applied_flag = 'Y'
335 AND p.list_line_type_code = 'FREIGHT_CHARGE'
336 AND h.order_category_code IN ('ORDER','MIXED','RETURN')
337 AND NVL(p.invoiced_flag, 'N') = 'Y'
338 AND h.transactional_curr_code = p_curr_code
339 AND h.header_id <> l_header_id
340 AND nvl(rl.interface_status, '~') <> 'P'
341 AND rl.interface_line_context = 'ORDER ENTRY'
342 AND rl.interface_line_attribute1 = h.order_number
343 AND rl.interface_line_attribute6 = p.price_adjustment_id
344 AND EXISTS
345 (SELECT NULL
346 FROM oe_payment_types_all pt,
347 oe_order_lines l
348 WHERE pt.credit_check_flag = 'Y'
349 AND l.header_id = h.header_id
350 AND l.org_id = pt.org_id
351 AND NVL(pt.org_id, -99) = NVL(h.org_id, -99)
352 AND pt.payment_type_code =
353 DECODE(l.payment_type_code, NULL,
354 DECODE(h.payment_type_code, NULL, pt.payment_type_code,
355 h.payment_type_code),
356 l.payment_type_code));
357
358 ----------------------- END exposure amount cursors -----------
359
360 BEGIN
361 IF G_debug_flag = 'Y'
362 THEN
363 OE_DEBUG_PUB.Add(' ');
364 OE_DEBUG_PUB.ADD('OEXUCERB: IN Get_exposure_amount',1);
365 END IF;
366
367 x_return_status := FND_API.G_RET_STS_SUCCESS;
368
369 IF G_debug_flag = 'Y'
370 THEN
371 OE_DEBUG_PUB.Add('Exposure Input parameters ');
372 OE_DEBUG_PUB.Add('--------------------------------------');
373 OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
374 OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
375 OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
376 OE_DEBUG_PUB.Add('p_credit_check_rule_id = '|| p_credit_check_rule_rec.credit_check_rule_id);
377 OE_DEBUG_PUB.Add('Conversion type = '|| p_credit_check_rule_rec.conversion_type);
378 OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
379 OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
380 OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
381 OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
382 OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
383 OE_DEBUG_PUB.Add(' l_include_returns_flag = '|| l_include_returns_flag );
384 OE_DEBUG_PUB.Add(' l_include_uninvoiced_flag= '|| l_include_uninvoiced_flag );
385
386 OE_DEBUG_PUB.Add('--------------End Parameters---------- ');
387 END IF;
388
389 IF p_credit_level = 'SITE'
390 THEN
391 BEGIN
392 SELECT cas.cust_account_id
393 INTO l_customer_id
394 FROM hz_cust_site_uses su
395 , hz_cust_acct_sites_all cas
396 WHERE su.site_use_id = l_site_use_id
397 AND su.cust_acct_site_id = cas.cust_acct_site_id ;
398
399 IF G_debug_flag = 'Y'
400 THEN
401 OE_DEBUG_PUB.ADD(' Customer ID : ' || l_customer_id ,1);
402 END IF;
403 EXCEPTION
404 WHEN NO_DATA_FOUND
405 THEN
406 IF G_debug_flag = 'Y'
407 THEN
408 OE_DEBUG_PUB.ADD(' No Data found for Customer ID using Site Use ID',1);
409 END IF;
410 END;
411 END IF;
412
413 IF p_credit_level IN ('CUSTOMER', 'SITE')
414 THEN
415 -- Select total amount exposure using CUSTOMER/SITE CURSORs
416 IF G_debug_flag = 'Y'
417 THEN
418 OE_DEBUG_PUB.Add('Begin AR Exposure calculation ');
419 END IF;
420
421 -- The exposure calculation must be done for all the usage currencies
422 -- as part of the multi currency set up.
423
424
425 l_current_usage_cur := NULL ;
426
427 FOR i IN 1..p_usage_curr.count
428 LOOP
429
430 l_current_usage_cur := NULL ;
431
432 IF G_debug_flag = 'Y'
433 THEN
434 OE_DEBUG_PUB.ADD(' ');
435 OE_DEBUG_PUB.ADD('############################### ');
436 OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
437 OE_DEBUG_PUB.ADD('############################### ');
438 OE_DEBUG_PUB.ADD(' ');
439 END IF;
440
441 l_current_usage_cur := p_usage_curr(i).usage_curr_code ;
442
443 ------------------- AR Exposure logic ------------------------
444 IF p_global_exposure_flag = 'Y'
445 THEN
446 IF l_include_uninvoiced_flag = 'Y'
447 THEN
448 IF G_debug_flag = 'Y'
449 THEN
450 OE_DEBUG_PUB.Add('Select cust_glb_orders ');
451 END IF;
452
453 OPEN cust_glb_orders(p_usage_curr(i).usage_curr_code);
454 FETCH cust_glb_orders INTO l_total_on_order;
455
456 IF G_debug_flag = 'Y'
457 THEN
458 OE_DEBUG_PUB.ADD(' l_total_on_order = ' || nvl(l_total_on_order,0));
459 END IF;
460
461 IF cust_glb_orders%NOTFOUND
462 THEN
463 l_total_on_order := 0 ;
464 OE_DEBUG_PUB.Add('No Uninvoiced order amount found ');
465 END IF;
466 CLOSE cust_glb_orders;
467 END IF;
468
469 IF l_include_returns_flag = 'Y'
470 THEN
471 IF G_debug_flag = 'Y'
472 THEN
473 OE_DEBUG_PUB.Add('Select cust_glb_orders_return ');
474 END IF;
475
476 OPEN cust_glb_orders_return(p_usage_curr(i).usage_curr_code);
477 FETCH cust_glb_orders_return INTO l_total_on_return;
478
479 IF G_debug_flag = 'Y'
480 THEN
481 OE_DEBUG_PUB.ADD(' l_total_on_return = ' || nvl(l_total_on_return,0));
482 END IF;
483
484 IF cust_glb_orders_return%NOTFOUND
485 THEN
486 l_total_on_return := 0 ;
487 OE_DEBUG_PUB.Add('No Return order amount found ');
488 END IF;
489
490 CLOSE cust_glb_orders_return;
491 END IF;
492
493 IF l_freight_charges_flag ='Y'
494 THEN
495 IF G_debug_flag = 'Y'
496 THEN
497 OE_DEBUG_PUB.Add('Select cust_glb_orders_freight1 ');
498 END IF;
499
500 OPEN cust_glb_orders_freight1 (p_usage_curr(i).usage_curr_code);
501 FETCH cust_glb_orders_freight1 INTO l_total_on_freight1;
502
503 IF G_debug_flag = 'Y'
504 THEN
505 OE_DEBUG_PUB.ADD(' l_total_on_freight1 = ' || nvl(l_total_on_freight1,0));
506 END IF;
507
508 IF cust_glb_orders_freight1%NOTFOUND
509 THEN
510 l_total_on_freight1 := 0 ;
511 OE_DEBUG_PUB.Add('No Freight order amount found ');
512 END IF;
513
514 CLOSE cust_glb_orders_freight1;
515
516 IF G_debug_flag = 'Y'
517 THEN
518 OE_DEBUG_PUB.Add('Select cust_glb_orders_freight2 ');
519 END IF;
520
521 OPEN cust_glb_orders_freight2 (p_usage_curr(i).usage_curr_code);
522 FETCH cust_glb_orders_freight2 INTO l_total_on_freight2;
523
524 IF G_debug_flag = 'Y'
525 THEN
526 OE_DEBUG_PUB.ADD(' l_total_on_freight2 = ' || nvl(l_total_on_freight2,0));
527 END IF;
528
529 IF cust_glb_orders_freight2%NOTFOUND
530 THEN
531 l_total_on_freight2 := 0 ;
532 OE_DEBUG_PUB.Add('No Freight order amount found ');
533 END IF;
534
535 CLOSE cust_glb_orders_freight2;
536 END IF;
537 ELSE
538 IF l_include_uninvoiced_flag = 'Y'
539 THEN
540 IF G_debug_flag = 'Y'
541 THEN
542 OE_DEBUG_PUB.Add('Select cust_reg_orders ');
543 END IF;
544
545 OPEN cust_reg_orders(p_usage_curr(i).usage_curr_code);
546 FETCH cust_reg_orders INTO l_total_on_order;
547
548 IF G_debug_flag = 'Y'
549 THEN
550 OE_DEBUG_PUB.ADD(' l_total_on_order = ' || nvl(l_total_on_order,0));
551 END IF;
552
553 IF cust_reg_orders%NOTFOUND
554 THEN
555 l_total_on_order := 0 ;
556 OE_DEBUG_PUB.Add('No Uninvoiced order amount found ');
557 END IF;
558
559 CLOSE cust_reg_orders;
560 END IF;
561
562 IF l_include_returns_flag = 'Y'
563 THEN
564 IF G_debug_flag = 'Y'
565 THEN
566 OE_DEBUG_PUB.Add('Select cust_reg_orders_return ');
567 END IF;
568
569 OPEN cust_reg_orders_return(p_usage_curr(i).usage_curr_code);
570 FETCH cust_reg_orders_return INTO l_total_on_return;
571
572 IF G_debug_flag = 'Y'
573 THEN
574 OE_DEBUG_PUB.ADD(' l_total_on_return = ' || nvl(l_total_on_return,0));
575 END IF;
576
577 IF cust_reg_orders_return%NOTFOUND
578 THEN
579 l_total_on_return := 0 ;
580 OE_DEBUG_PUB.Add('No Return order amount found ');
581 END IF;
582
583 CLOSE cust_reg_orders_return;
584 END IF;
585
586 IF l_freight_charges_flag ='Y'
587 THEN
588 IF G_debug_flag = 'Y'
589 THEN
590 OE_DEBUG_PUB.Add('Select open cust_reg_orders_freight1 ');
591 END IF;
592
593 OPEN cust_reg_orders_freight1 (p_usage_curr(i).usage_curr_code);
594 FETCH cust_reg_orders_freight1 INTO l_total_on_freight1;
595
596 IF G_debug_flag = 'Y'
597 THEN
598 OE_DEBUG_PUB.ADD(' l_total_on_freight1 = ' || nvl(l_total_on_freight1,0));
599 END IF;
600
601 IF cust_reg_orders_freight1%NOTFOUND
602 THEN
603 l_total_on_freight1 := 0 ;
604 OE_DEBUG_PUB.Add('No Freight order amount found ');
605 END IF;
606
607 CLOSE cust_reg_orders_freight1;
608
609 IF G_debug_flag = 'Y'
610 THEN
611 OE_DEBUG_PUB.Add('Select open cust_reg_orders_freight2 ');
612 END IF;
613
614 OPEN cust_reg_orders_freight2 (p_usage_curr(i).usage_curr_code);
615 FETCH cust_reg_orders_freight2 INTO l_total_on_freight2;
616
617 IF G_debug_flag = 'Y'
618 THEN
619 OE_DEBUG_PUB.ADD(' l_total_on_freight2 = ' || nvl(l_total_on_freight2,0));
620 END IF;
621
622 IF cust_reg_orders_freight2%NOTFOUND
623 THEN
624 l_total_on_freight2 := 0 ;
625 OE_DEBUG_PUB.Add('No Freight order amount found ');
626 END IF;
627
628 CLOSE cust_reg_orders_freight2;
629 END IF;
630 END IF ; --- Global
631
632 ----------------------- End AR Exposure ------------------
633
634 l_cum_total_on_order := l_cum_total_on_order + NVL(l_total_on_order,0);
635 l_cum_total_on_return := l_cum_total_on_return + NVL(l_total_on_return,0);
636 l_cum_total_on_freight1:= l_cum_total_on_freight1 + NVL(l_total_on_freight1,0);
637 l_cum_total_on_freight2:= l_cum_total_on_freight2 + NVL(l_total_on_freight2,0);
638
639 l_usage_total_exposure := NVL(l_cum_total_on_order,0) + NVL(l_cum_total_on_return,0)
640 + NVL(l_cum_total_on_freight1,0) + NVL(l_cum_total_on_freight2,0);
641
642 IF G_debug_flag = 'Y'
643 THEN
644 OE_DEBUG_PUB.ADD(' ');
645 OE_DEBUG_PUB.ADD(' l_cum_total_on_order = ' || nvl(l_cum_total_on_order,0));
646 OE_DEBUG_PUB.ADD(' l_cum_total_on_return = ' || nvl(l_cum_total_on_return,0));
647 OE_DEBUG_PUB.ADD(' l_cum_total_on_freight1 = ' || nvl(l_total_on_freight1,0));
648 OE_DEBUG_PUB.ADD(' l_cum_total_on_freight2 = ' || nvl(l_total_on_freight2,0));
649 OE_DEBUG_PUB.ADD(' l_usage_total_exposure = ' || nvl(l_usage_total_exposure,0));
650
651 OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
652 OE_DEBUG_PUB.Add(' GL_CURRENCY = '|| OE_Credit_Engine_GRP.GL_currency );
653 END IF;
654
655 IF OE_Credit_Engine_GRP.GL_currency IS NULL
656 THEN
657 OE_DEBUG_PUB.ADD(' Call GET_GL_currency ');
658
659 OE_Credit_Engine_GRP.GL_currency := OE_CREDIT_CHECK_UTIL.GET_GL_currency ;
660
661 OE_DEBUG_PUB.ADD(' GL_CURRENCY after = ' || OE_Credit_Engine_GRP.GL_currency );
662 END IF;
663
664 l_limit_total_exposure :=
665 OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
666 ( p_amount => l_usage_total_exposure
667 , p_transactional_currency => p_usage_curr(i).usage_curr_code
668 , p_limit_currency => p_limit_curr_code
669 , p_functional_currency => OE_Credit_Engine_GRP.GL_currency
670 , p_conversion_date => SYSDATE
671 , p_conversion_type => p_credit_check_rule_rec.conversion_type
672 );
673
674 l_total_exposure := NVL(l_total_exposure,0) + NVL(l_limit_total_exposure,0) ;
675
676 IF G_debug_flag = 'Y'
677 THEN
678 OE_DEBUG_PUB.ADD('l_total_exposure = ' || nvl(l_total_exposure,0));
679 END IF;
680
681 l_cum_total_on_order := 0;
682 l_cum_total_on_return := 0;
683 l_cum_total_on_freight1 := 0;
684 l_cum_total_on_freight2 := 0;
685
686 l_usage_total_exposure := 0;
687 l_limit_total_exposure := 0;
688
689 l_total_on_order := 0;
690 l_total_on_return := 0;
691 l_total_on_freight1 := 0;
692 l_total_on_freight2 := 0;
693
694 END LOOP ; -- CURRENCY LOOP
695 END IF;
696
697 x_exposure_amount := NVL(l_total_exposure,0) ;
698
699 IF G_debug_flag = 'Y'
700 THEN
701 OE_DEBUG_PUB.Add(' ');
702 OE_DEBUG_PUB.Add('---------------##########----------------' );
703 OE_DEBUG_PUB.Add(' ');
704 OE_DEBUG_PUB.Add('Total exposure amount in Interface table = '|| x_exposure_amount,1);
705 OE_DEBUG_PUB.Add(' ');
706 OE_DEBUG_PUB.Add('-------------- ##########----------------' );
707 OE_DEBUG_PUB.Add(' ');
708 OE_DEBUG_PUB.ADD('OEXUCERB: OUT NOCOPY Get_exposure_amount ',1);
709 END IF;
710
711 EXCEPTION
712 WHEN GL_CURRENCY_API.NO_RATE THEN
713 OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE in get_exposure_amount',1);
714 OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
715 x_conversion_status(1).usage_curr_code := l_current_usage_cur ;
716
717 fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
718 OE_DEBUG_PUB.ADD('Exception table added ');
719 WHEN OTHERS THEN
720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
721 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
722 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, '
723 Get_exposure_amount');
724 END IF;
725 RAISE;
726
727 END Get_exposure_amount ;
728
729 END OE_CREDIT_INTERFACE_UTIL ;