[Home] [Help]
PACKAGE BODY: APPS.OE_CREDIT_INTERFACE_UTIL
Source
4 --| Copyright (c) 2000 Oracle Corporation |
1 PACKAGE BODY OE_CREDIT_INTERFACE_UTIL AS
2 -- $Header: OEXUCERB.pls 120.10 2011/08/25 21:04:13 cpati ship $
3 --+=======================================================================+
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 ;
66 l_cum_total_on_order NUMBER := 0 ;
63 l_total_on_freight1_tmp NUMBER := 0 ; --TaxER
64 l_total_on_freight2_tmp NUMBER := 0 ; --TaxER
65
67 l_cum_total_on_return NUMBER := 0 ;
68 l_cum_total_on_freight1 NUMBER := 0 ;
69 l_cum_total_on_freight2 NUMBER := 0 ;
70
71 l_usage_total_exposure NUMBER := 0 ;
72 l_limit_total_exposure NUMBER := 0 ;
73
74 l_current_usage_cur VARCHAR2(100) ;
75
76 l_cust_acct_site_id number; -- bug 8744491
77
78 ---------------------------------------------------------------
79 -- Cursor definitions
80 -- a) In order related cursors, use l_header_id instead of
81 -- p_header_id. This is done to remove the NVL on p_header_id
82 -- as p_header_id will be coming in as NULL for the
83 -- exposure reports.
84 ---------------------------------------------------------------
85 --------------------- START exposure amount cursors -----------
86 ---------------------------------------------------------------
87 -- CUSTOMER/SITE LEVEL CURSORS
88 ---------------------------------------------------------------
89
90 ---CUSTOMER/SITE REGULAR ORDERS
91 CURSOR cust_reg_orders (p_curr_code IN VARCHAR2 default NULL,l_cust_acct_site_id IN NUMBER DEFAULT NULL) IS -- bug 8744491
92 SELECT SUM (
93 ( NVL( rl.amount, 0 ) )
94 + DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
95 NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ), 0 ) --TaxER
96 )
97 FROM oe_order_lines l
98 , oe_order_headers_all h
99 , ra_interface_lines_all rl
100 WHERE rl.orig_system_bill_customer_id = l_customer_id
101 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
102 AND h.header_id = l.header_id
103 AND h.org_id = l.org_id
104 AND NVL( l.invoiced_quantity, 0 ) <> 0
105 AND l.line_category_code = 'ORDER'
106 AND h.booked_flag = 'Y'
107 --AND h.header_id <> l_header_id --commented for bug#8879693
108 AND h.transactional_curr_code = p_curr_code
109 AND nvl(rl.interface_status, '~') <> 'P'
110 AND rl.interface_line_context = 'ORDER ENTRY'
111 --bug 11662722 AND rl.interface_line_attribute1 = To_Char(h.order_number) -- To_Char added for Bug 10008003
112 --bug 11662722 AND rl.interface_line_attribute6 = To_Char(l.line_id) -- To_Char added for Bug 10008003
113 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
114
115 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = l.line_id --bug 11662722
116 AND (EXISTS
117 (SELECT NULL
118 FROM oe_payment_types_all pt
119 WHERE pt.payment_type_code = NVL(l.payment_type_code,
120 NVL(h.payment_type_code, 'BME'))
121 AND pt.credit_check_flag = 'Y'
122 AND NVL(pt.org_id, -99) = l_org_id)
123 OR
124 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
125
126 ---CUSTOMER/SITE RETURN ORDERS
127 CURSOR cust_reg_orders_return(p_curr_code IN VARCHAR2 default NULL,l_cust_acct_site_id IN NUMBER DEFAULT NULL) IS -- bug 8744491
128 SELECT SUM (
129 ( DECODE( SIGN (NVL( rl.quantity_ordered, 0 )), -1, (+1), (-1) ) * NVL( rl.amount, 0 ) )
130 + DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
134 , oe_order_headers_all h
131 NVL(DECODE(l.line_category_code,'RETURN',(-1)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0), 0 ) --TaxER
132 )
133 FROM oe_order_lines l
135 , ra_interface_lines_all rl
136 WHERE rl.orig_system_bill_customer_id = l_customer_id
137 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
138 AND h.header_id = l.header_id
139 AND h.org_id = l.org_id
140 AND NVL( l.invoiced_quantity, 0 ) <> 0
141 AND l.line_category_code = 'RETURN'
142 AND h.booked_flag = 'Y'
143 --AND h.header_id <> l_header_id --commented for bug#8879693
144 AND h.transactional_curr_code = p_curr_code
145 AND nvl(rl.interface_status, '~') <> 'P'
146 AND rl.interface_line_context = 'ORDER ENTRY'
147 --bug 11662722 AND rl.interface_line_attribute1 = To_Char(h.order_number) -- To_Char added for Bug 10008003
148 --bug 11662722 AND rl.interface_line_attribute6 = To_Char(l.line_id) -- To_Char added for Bug 10008003
149 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
150
151 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = l.line_id --bug 11662722
152 AND (EXISTS
153 (SELECT NULL
154 FROM oe_payment_types_all pt
155 WHERE pt.payment_type_code = NVL(l.payment_type_code,
156 NVL(h.payment_type_code, 'BME'))
157 AND pt.credit_check_flag = 'Y'
158 AND NVL(pt.org_id, -99) = l_org_id)
159 OR
160 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
161
162 ---CUSTOMER/SITE FREIGHT ORDERS
163 CURSOR cust_reg_orders_freight1 (p_curr_code IN VARCHAR2 default null,l_cust_acct_site_id IN NUMBER DEFAULT NULL) IS -- bug 8744491
164 SELECT SUM( NVL( rl.amount, 0 ))
165 FROM oe_price_adjustments p
166 , oe_order_lines l
167 , oe_order_headers_all h
168 , ra_interface_lines_all rl
169 WHERE rl.orig_system_bill_customer_id = l_customer_id
170 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
171 AND p.line_id = l.line_id
172 AND NVL( l.invoiced_quantity, 0 ) <> 0
173 AND p.header_id = l.header_id
174 AND p.header_id = h.header_id
175 AND h.header_id = l.header_id
176 AND h.org_id = l.org_id
177 AND h.booked_flag = 'Y'
178 AND p.applied_flag = 'Y'
179 AND p.list_line_type_code = 'FREIGHT_CHARGE'
180 AND h.order_category_code IN ('ORDER','MIXED','RETURN')
181 AND NVL(p.invoiced_flag, 'N') = 'Y'
182 AND h.transactional_curr_code = p_curr_code
183 --AND h.header_id <> l_header_id --commented for bug#8879693
184 AND nvl(rl.interface_status, '~') <> 'P'
185 AND rl.interface_line_context = 'ORDER ENTRY'
186 --bug 11662722 AND rl.interface_line_attribute1 = To_Char(h.order_number) -- To_Char added for Bug 10008003
187 --bug 11662722 AND rl.interface_line_attribute6 = To_Char(p.price_adjustment_id) -- To_Char added for Bug 10008003
188 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
189 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id --bug 11662722
190 AND (EXISTS
191 (SELECT NULL
192 FROM oe_payment_types_all pt
193 WHERE pt.payment_type_code = NVL(l.payment_type_code,
194 NVL(h.payment_type_code, 'BME'))
195 AND pt.credit_check_flag = 'Y'
196 AND NVL(pt.org_id, -99) = l_org_id)
197 OR
198 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL))
199 --TaxER Start
200 UNION ALL
201 SELECT SUM(NVL(DECODE(p_credit_check_rule_rec.include_tax_flag,'Y',DECODE(p1.list_line_type_code,'TAX',p1.adjusted_amount,0),0),0))
202 FROM oe_price_adjustments p,oe_price_adjustments p1
203 , oe_order_lines l
204 , oe_order_headers_all h
205 , ra_interface_lines_all rl
206 WHERE rl.orig_system_bill_customer_id = l_customer_id
207 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
208 AND p.line_id = l.line_id
209 AND NVL( l.invoiced_quantity, 0 ) <> 0
210 AND p.header_id = l.header_id
211 AND p.header_id = h.header_id
212 AND h.header_id = l.header_id
213 AND h.org_id = l.org_id
214 AND h.booked_flag = 'Y'
215 AND p.applied_flag = 'Y'
216 AND p.list_line_type_code = 'FREIGHT_CHARGE'
217 AND p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
218 AND p1.applied_flag = 'N' --12895421
219 AND p.price_adjustment_id = p1.parent_adjustment_id
220 AND p.line_id = p1.line_id
221 AND p.header_id = p1.header_id
222 AND h.order_category_code IN ('ORDER','MIXED','RETURN')
223 AND NVL(p.invoiced_flag, 'N') = 'Y'
224 AND h.transactional_curr_code = p_curr_code
225 AND nvl(rl.interface_status, '~') <> 'P'
226 AND rl.interface_line_context = 'ORDER ENTRY'
227 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
228 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id --bug 11662722
229 AND (EXISTS
230 (SELECT NULL
231 FROM oe_payment_types_all pt
232 WHERE pt.payment_type_code = NVL(l.payment_type_code,
233 NVL(h.payment_type_code, 'BME'))
234 AND pt.credit_check_flag = 'Y'
235 AND NVL(pt.org_id, -99) = l_org_id)
236 OR
237 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
238 --TaxER End
239
240
241 CURSOR cust_reg_orders_freight2 (p_curr_code IN VARCHAR2 default null,l_cust_acct_site_id IN NUMBER DEFAULT NULL) IS -- bug 8744491
242 SELECT SUM( NVL( rl.amount, 0 ))
243 FROM oe_price_adjustments p
244 , oe_order_headers_all h
245 , ra_interface_lines_all rl
246 WHERE rl.orig_system_bill_customer_id = l_customer_id
247 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
248 AND p.line_id IS NULL
249 AND p.header_id = h.header_id
250 AND h.booked_flag = 'Y'
251 AND p.applied_flag = 'Y'
252 AND p.list_line_type_code = 'FREIGHT_CHARGE'
253 AND h.order_category_code IN ('ORDER','MIXED','RETURN')
254 AND NVL(p.invoiced_flag, 'N') = 'Y'
255 AND h.transactional_curr_code = p_curr_code
256 --AND h.header_id <> l_header_id --commented for bug#8879693
257 AND nvl(rl.interface_status, '~') <> 'P'
258 AND rl.interface_line_context = 'ORDER ENTRY'
259 --bug 11662722 AND rl.interface_line_attribute1 = To_Char(h.order_number) -- To_Char added for Bug 10008003
260 --bug 11662722 AND rl.interface_line_attribute6 = To_Char(p.price_adjustment_id) -- To_Char added for Bug 10008003
261 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
262 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id --bug 11662722
263 AND EXISTS
264 (SELECT NULL
265 FROM oe_payment_types_all pt,
266 oe_order_lines l
267 WHERE pt.credit_check_flag = 'Y'
268 AND l.header_id = h.header_id
269 AND l.org_id = pt.org_id
270 AND NVL(pt.org_id, -99) = l_org_id
271 AND pt.payment_type_code =
272 DECODE(l.payment_type_code, NULL,
273 DECODE(h.payment_type_code, NULL, pt.payment_type_code,
274 h.payment_type_code),
275 l.payment_type_code))
276 --TaxER Start
277 UNION ALL
278 SELECT SUM(NVL(DECODE(p_credit_check_rule_rec.include_tax_flag,'Y',DECODE(p1.list_line_type_code,'TAX',p1.adjusted_amount,0),0),0))
279 FROM oe_price_adjustments p,oe_price_adjustments p1
280 , oe_order_headers_all h
281 , ra_interface_lines_all rl
282 WHERE rl.orig_system_bill_customer_id = l_customer_id
283 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
284 AND p.line_id IS NULL
285 AND p.header_id = h.header_id
286 AND h.booked_flag = 'Y'
287 AND p.applied_flag = 'Y'
288 AND p.list_line_type_code = 'FREIGHT_CHARGE'
289 AND p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
290 AND p1.applied_flag = 'N' --12895421
291 AND p.price_adjustment_id = p1.parent_adjustment_id
292 AND p.line_id = p1.line_id
296 AND h.transactional_curr_code = p_curr_code
293 AND p.header_id = p1.header_id
294 AND h.order_category_code IN ('ORDER','MIXED','RETURN')
295 AND NVL(p.invoiced_flag, 'N') = 'Y'
297 AND nvl(rl.interface_status, '~') <> 'P'
298 AND rl.interface_line_context = 'ORDER ENTRY'
299 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
300 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id --bug 11662722
301 AND EXISTS
302 (SELECT NULL
303 FROM oe_payment_types_all pt,
304 oe_order_lines l
305 WHERE pt.credit_check_flag = 'Y'
306 AND l.header_id = h.header_id
307 AND l.org_id = pt.org_id
308 AND NVL(pt.org_id, -99) = l_org_id
309 AND pt.payment_type_code =
310 DECODE(l.payment_type_code, NULL,
311 DECODE(h.payment_type_code, NULL, pt.payment_type_code,
312 h.payment_type_code),
313 l.payment_type_code));
314 --TaxER End
315
316
317 ---------------------------------------------------------------
318 -- GLOBAL EXPOSURE CURSORS
319 -------------------------------------------------------------
320
321 ---GLOBAL REGULAR ORDERS
322 CURSOR cust_glb_orders (p_curr_code IN VARCHAR2 default NULL,l_cust_acct_site_id IN NUMBER DEFAULT NULL) IS -- bug 8744491
323 SELECT SUM (
324 ( NVL( rl.amount, 0 ) )
325 + DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
326 NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ), 0 ) --TaxER
327 )
328 FROM oe_order_lines_all l
329 , oe_order_headers_all h
330 , ra_interface_lines_all rl
331 WHERE rl.orig_system_bill_customer_id = l_customer_id
332 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
333 AND h.header_id = l.header_id
334 AND NVL( l.invoiced_quantity, 0 ) <> 0
335 AND l.line_category_code = 'ORDER'
336 AND h.booked_flag = 'Y'
337 --AND h.header_id <> l_header_id --commented for bug#8879693
338 AND h.transactional_curr_code = p_curr_code
339 AND nvl(rl.interface_status, '~') <> 'P'
340 AND rl.interface_line_context = 'ORDER ENTRY'
341 --bug 11662722 AND rl.interface_line_attribute1 = To_Char(h.order_number) -- To_Char added for Bug 10008003
342 --bug 11662722 AND rl.interface_line_attribute6 = To_Char(l.line_id) -- To_Char added for Bug 10008003)
343 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
344
345 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = l.line_id --bug 11662722
346 AND (EXISTS
347 (SELECT NULL
348 FROM oe_payment_types_all pt
349 WHERE pt.payment_type_code = NVL(l.payment_type_code,
350 NVL(h.payment_type_code, 'BME'))
351 AND pt.credit_check_flag = 'Y'
352 AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
353 OR
354 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
355
356 ---GLOBAL RETURN ORDERS
357 CURSOR cust_glb_orders_return (p_curr_code IN VARCHAR2 default NULL,l_cust_acct_site_id IN NUMBER DEFAULT NULL) IS -- bug 8744491
358 SELECT SUM (
359 ( DECODE( SIGN (NVL( rl.quantity_ordered, 0 )), -1, (+1), (-1) ) * NVL( rl.amount, 0 ) )
360 + DECODE( rl.interface_line_attribute11, '0', DECODE(l_include_tax_flag, 'Y',
361 NVL(DECODE(l.line_category_code,'RETURN',(-1)*NVL(l.tax_line_value,l.tax_value),NVL(l.tax_line_value,l.tax_value)),0),0), 0 ) --TaxER
362 )
363 FROM oe_order_lines_all l
364 , oe_order_headers_all h
365 , ra_interface_lines_all rl
366 WHERE rl.orig_system_bill_customer_id = l_customer_id
367 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
368 AND h.header_id = l.header_id
369 AND NVL( l.invoiced_quantity, 0 ) <> 0
370 AND l.line_category_code = 'RETURN'
371 AND h.booked_flag = 'Y'
372 --AND h.header_id <> l_header_id --commented for bug#8879693
373 AND h.transactional_curr_code = p_curr_code
374 AND nvl(rl.interface_status, '~') <> 'P'
375 AND rl.interface_line_context = 'ORDER ENTRY'
376 --bug 11662722 AND rl.interface_line_attribute1 = To_Char(h.order_number) -- To_Char added for Bug 10008003
377 --bug 11662722 AND rl.interface_line_attribute6 = To_Char(l.line_id) -- To_Char added for Bug 10008003
378 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
379
380 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = l.line_id --bug 11662722
381
382 AND (EXISTS
383 (SELECT NULL
384 FROM oe_payment_types_all pt
385 WHERE pt.payment_type_code = NVL(l.payment_type_code,
386 NVL(h.payment_type_code, 'BME'))
387 AND pt.credit_check_flag = 'Y'
388 AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
389 OR
393 CURSOR cust_glb_orders_freight1 (p_curr_code IN VARCHAR2 default null,l_cust_acct_site_id IN NUMBER DEFAULT NULL) IS -- bug 8744491
390 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
391
392 ---GLOBAL FREIGHT ORDERS
394 SELECT SUM( NVL( rl.amount, 0 ))
395 FROM oe_price_adjustments p
396 , oe_order_lines_all l
397 , oe_order_headers_all h
398 , ra_interface_lines_all rl
399 WHERE rl.orig_system_bill_customer_id = l_customer_id
400 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
401 AND p.line_id = l.line_id
402 AND NVL( l.invoiced_quantity, 0 ) <> 0
403 AND p.header_id = l.header_id
404 AND p.header_id = h.header_id
405 AND h.header_id = l.header_id
406 AND h.booked_flag = 'Y'
407 AND p.applied_flag = 'Y'
408 AND p.list_line_type_code = 'FREIGHT_CHARGE'
409 AND NVL(p.invoiced_flag, 'N') = 'Y'
410 AND h.transactional_curr_code = p_curr_code
411 --AND h.header_id <> l_header_id --commented for bug#8879693
412 AND nvl(rl.interface_status, '~') <> 'P'
413 AND rl.interface_line_context = 'ORDER ENTRY'
414 ---bug 11662722 AND rl.interface_line_attribute1 = To_Char(h.order_number) -- To_Char added for Bug 10008003
415 ---bug 11662722 AND rl.interface_line_attribute6 = To_Char(p.price_adjustment_id) -- To_Char added for Bug 10008003
416 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
417 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id --bug 11662722
418 AND (EXISTS
419 (SELECT NULL
420 FROM oe_payment_types_all pt
421 WHERE pt.payment_type_code = NVL(l.payment_type_code,
422 NVL(h.payment_type_code, 'BME'))
423 AND pt.credit_check_flag = 'Y'
424 AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
425 OR
426 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL))
427 --TaxER Start
428 UNION ALL
429 SELECT SUM(NVL(DECODE(p_credit_check_rule_rec.include_tax_flag,'Y',DECODE(p1.list_line_type_code,'TAX',p1.adjusted_amount,0),0),0))
430 FROM oe_price_adjustments p,oe_price_adjustments p1
431 , oe_order_lines_all l
432 , oe_order_headers_all h
433 , ra_interface_lines_all rl
434 WHERE rl.orig_system_bill_customer_id = l_customer_id
435 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
436 AND p.line_id = l.line_id
437 AND NVL( l.invoiced_quantity, 0 ) <> 0
438 AND p.header_id = l.header_id
439 AND p.header_id = h.header_id
440 AND h.header_id = l.header_id
441 AND h.booked_flag = 'Y'
442 AND p.applied_flag = 'Y'
443 AND p.list_line_type_code = 'FREIGHT_CHARGE'
444 AND p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
445 AND p1.applied_flag = 'N' --12895421
446 AND p.price_adjustment_id = p1.parent_adjustment_id
447 AND p.line_id = p1.line_id
448 AND p.header_id = p1.header_id
449 AND NVL(p.invoiced_flag, 'N') = 'Y'
450 AND h.transactional_curr_code = p_curr_code
451 AND nvl(rl.interface_status, '~') <> 'P'
452 AND rl.interface_line_context = 'ORDER ENTRY'
453 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
454 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id --bug 11662722
455 AND (EXISTS
456 (SELECT NULL
457 FROM oe_payment_types_all pt
458 WHERE pt.payment_type_code = NVL(l.payment_type_code,
459 NVL(h.payment_type_code, 'BME'))
460 AND pt.credit_check_flag = 'Y'
461 AND NVL(pt.org_id, -99) = NVL(h.org_id, -99))
462 OR
463 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
464 --TaxER End
465
466
467 CURSOR cust_glb_orders_freight2 (p_curr_code IN VARCHAR2 default null,l_cust_acct_site_id IN NUMBER DEFAULT NULL) IS -- bug 8744491
468 SELECT SUM( NVL( rl.amount, 0 ))
469 FROM oe_price_adjustments p
470 , oe_order_headers_all h
471 , ra_interface_lines_all rl
472 WHERE rl.orig_system_bill_customer_id = l_customer_id
473 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
474 AND p.line_id IS NULL
475 AND p.header_id = h.header_id
476 AND h.booked_flag = 'Y'
477 AND p.applied_flag = 'Y'
478 AND p.list_line_type_code = 'FREIGHT_CHARGE'
479 AND h.order_category_code IN ('ORDER','MIXED','RETURN')
480 AND NVL(p.invoiced_flag, 'N') = 'Y'
481 AND h.transactional_curr_code = p_curr_code
482 --AND h.header_id <> l_header_id --commented for bug#8879693
483 AND nvl(rl.interface_status, '~') <> 'P'
484 AND rl.interface_line_context = 'ORDER ENTRY'
485 --bug 11662722 AND rl.interface_line_attribute1 = To_Char(h.order_number) -- To_Char added for Bug 10008003
486 --bug 11662722 AND rl.interface_line_attribute6 = To_Char(p.price_adjustment_id) -- To_Char added for Bug 10008003
490 (SELECT NULL
487 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
488 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id --bug 11662722
489 AND EXISTS
491 FROM oe_payment_types_all pt,
492 oe_order_lines l
493 WHERE pt.credit_check_flag = 'Y'
494 AND l.header_id = h.header_id
495 AND l.org_id = pt.org_id
496 AND NVL(pt.org_id, -99) = NVL(h.org_id, -99)
497 AND pt.payment_type_code =
498 DECODE(l.payment_type_code, NULL,
499 DECODE(h.payment_type_code, NULL, pt.payment_type_code,
500 h.payment_type_code),
501 l.payment_type_code))
502 --TaxER Start
503 UNION ALL
504 SELECT SUM(NVL(DECODE(p_credit_check_rule_rec.include_tax_flag,'Y',DECODE(p1.list_line_type_code,'TAX',p1.adjusted_amount,0),0),0))
505 FROM oe_price_adjustments p
506 ,oe_price_adjustments p1
507 , oe_order_headers_all h
508 , ra_interface_lines_all rl
509 WHERE rl.orig_system_bill_customer_id = l_customer_id
510 AND rl.ORIG_SYSTEM_BILL_ADDRESS_ID = nvl(l_cust_acct_site_id,ORIG_SYSTEM_BILL_ADDRESS_ID) --6896462 bug 8744491
511 AND p.line_id IS NULL
512 AND p.header_id = h.header_id
513 AND h.booked_flag = 'Y'
514 AND p.applied_flag = 'Y'
515 AND p.list_line_type_code = 'FREIGHT_CHARGE'
516 AND p1.list_line_type_code = 'TAX' and p1.parent_adjustment_id IS NOT NULL
517 AND p1.applied_flag = 'N' --12895421
518 AND p.price_adjustment_id = p1.parent_adjustment_id
519 AND p.line_id = p1.line_id
520 AND p.header_id = p1.header_id
521 AND h.order_category_code IN ('ORDER','MIXED','RETURN')
522 AND NVL(p.invoiced_flag, 'N') = 'Y'
523 AND h.transactional_curr_code = p_curr_code
524 AND nvl(rl.interface_status, '~') <> 'P'
525 AND rl.interface_line_context = 'ORDER ENTRY'
526 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute1,'0123456789','0'),'0'), NULL, rl.interface_line_attribute1,-99999)) = h.order_number -- bug 11662722
527 AND TO_NUMBER(DECODE(RTRIM(TRANSLATE(rl.interface_line_attribute6,'0123456789','0'),'0'), NULL, rl.interface_line_attribute6,-99999)) = p.price_adjustment_id --bug 11662722
528 AND EXISTS
529 (SELECT NULL
530 FROM oe_payment_types_all pt,
531 oe_order_lines l
532 WHERE pt.credit_check_flag = 'Y'
533 AND l.header_id = h.header_id
534 AND l.org_id = pt.org_id
535 AND NVL(pt.org_id, -99) = NVL(h.org_id, -99)
536 AND pt.payment_type_code =
537 DECODE(l.payment_type_code, NULL,
538 DECODE(h.payment_type_code, NULL, pt.payment_type_code,
539 h.payment_type_code),
540 l.payment_type_code));
541 --TaxER End
542
543 ----------------------- END exposure amount cursors -----------
544
545 BEGIN
546 IF G_debug_flag = 'Y'
547 THEN
548 OE_DEBUG_PUB.Add(' ');
549 OE_DEBUG_PUB.ADD('OEXUCERB: IN Get_exposure_amount',1);
550 END IF;
551
552 x_return_status := FND_API.G_RET_STS_SUCCESS;
553
554 IF G_debug_flag = 'Y'
555 THEN
556 OE_DEBUG_PUB.Add('Exposure Input parameters ');
557 OE_DEBUG_PUB.Add('--------------------------------------');
558 OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
559 OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
560 OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
561 OE_DEBUG_PUB.Add('p_credit_check_rule_id = '|| p_credit_check_rule_rec.credit_check_rule_id);
562 OE_DEBUG_PUB.Add('Conversion type = '|| p_credit_check_rule_rec.conversion_type);
563 OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
564 OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
565 OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
566 OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
567 OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
568 OE_DEBUG_PUB.Add(' l_include_returns_flag = '|| l_include_returns_flag );
569 OE_DEBUG_PUB.Add(' l_include_uninvoiced_flag= '|| l_include_uninvoiced_flag );
570
571 OE_DEBUG_PUB.Add('--------------End Parameters---------- ');
572 END IF;
573
574 IF p_credit_level = 'SITE'
575 THEN
576 BEGIN
577 -- bug 8744491
578 SELECT cas.cust_account_id,su.cust_acct_site_id
579 INTO l_customer_id,l_cust_acct_site_id
580 FROM hz_cust_site_uses su
581 , hz_cust_acct_sites_all cas
582 WHERE su.site_use_id = l_site_use_id
586 THEN
583 AND su.cust_acct_site_id = cas.cust_acct_site_id ;
584
585 IF G_debug_flag = 'Y'
587 OE_DEBUG_PUB.ADD(' Customer ID : ' || l_customer_id ,1);
588 OE_DEBUG_PUB.ADD(' Customer site ID : ' || l_cust_acct_site_id ,1); -- bug 8744491
589 END IF;
590 EXCEPTION
591 WHEN NO_DATA_FOUND
592 THEN
593 IF G_debug_flag = 'Y'
594 THEN
595 OE_DEBUG_PUB.ADD(' No Data found for Customer ID using Site Use ID',1);
596 END IF;
597 END;
598 END IF;
599
600 IF p_credit_level IN ('CUSTOMER', 'SITE')
601 THEN
602 -- Select total amount exposure using CUSTOMER/SITE CURSORs
603 IF G_debug_flag = 'Y'
604 THEN
605 OE_DEBUG_PUB.Add('Begin AR Exposure calculation ');
606 END IF;
607
608 -- The exposure calculation must be done for all the usage currencies
609 -- as part of the multi currency set up.
610
611
612 l_current_usage_cur := NULL ;
613
614 FOR i IN 1..p_usage_curr.count
615 LOOP
616
617 l_current_usage_cur := NULL ;
618
619 IF G_debug_flag = 'Y'
620 THEN
621 OE_DEBUG_PUB.ADD(' ');
622 OE_DEBUG_PUB.ADD('############################### ');
623 OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
624 OE_DEBUG_PUB.ADD('############################### ');
625 OE_DEBUG_PUB.ADD(' ');
626 END IF;
627
628 l_current_usage_cur := p_usage_curr(i).usage_curr_code ;
629
630 ------------------- AR Exposure logic ------------------------
631 IF p_global_exposure_flag = 'Y'
632 THEN
633 IF l_include_uninvoiced_flag = 'Y'
634 THEN
635 IF G_debug_flag = 'Y'
636 THEN
637 OE_DEBUG_PUB.Add('Select cust_glb_orders ');
638 END IF;
639
640 OPEN cust_glb_orders(p_usage_curr(i).usage_curr_code,l_cust_acct_site_id); --bug 8744491
641 FETCH cust_glb_orders INTO l_total_on_order;
642
643 IF G_debug_flag = 'Y'
644 THEN
645 OE_DEBUG_PUB.ADD(' l_total_on_order = ' || nvl(l_total_on_order,0));
646 END IF;
647
648 IF cust_glb_orders%NOTFOUND
649 THEN
650 l_total_on_order := 0 ;
651 OE_DEBUG_PUB.Add('No Uninvoiced order amount found ');
652 END IF;
653 CLOSE cust_glb_orders;
654 END IF;
655
656 IF l_include_returns_flag = 'Y'
657 THEN
658 IF G_debug_flag = 'Y'
659 THEN
660 OE_DEBUG_PUB.Add('Select cust_glb_orders_return ');
661 END IF;
662
663 OPEN cust_glb_orders_return(p_usage_curr(i).usage_curr_code,l_cust_acct_site_id); --bug 8744491
664 FETCH cust_glb_orders_return INTO l_total_on_return;
665
666 IF G_debug_flag = 'Y'
667 THEN
668 OE_DEBUG_PUB.ADD(' l_total_on_return = ' || nvl(l_total_on_return,0));
669 END IF;
670
671 IF cust_glb_orders_return%NOTFOUND
672 THEN
673 l_total_on_return := 0 ;
674 OE_DEBUG_PUB.Add('No Return order amount found ');
675 END IF;
676
677 CLOSE cust_glb_orders_return;
678 END IF;
679
680 IF l_freight_charges_flag ='Y'
681 THEN
682 IF G_debug_flag = 'Y'
683 THEN
684 OE_DEBUG_PUB.Add('Select cust_glb_orders_freight1 ');
685 END IF;
686
687 OPEN cust_glb_orders_freight1 (p_usage_curr(i).usage_curr_code,l_cust_acct_site_id); --bug 8744491
688 --TaxER FETCH cust_glb_orders_freight1 INTO l_total_on_freight1;
689 --TaxER Start
690 LOOP
691 FETCH cust_glb_orders_freight1 INTO l_total_on_freight1_tmp;
692 EXIT WHEN cust_glb_orders_freight1%NOTFOUND;
693 l_total_on_freight1 := l_total_on_freight1 + nvl(l_total_on_freight1_tmp,0);
694 END LOOP;
695 --TaxER end
696
697 IF G_debug_flag = 'Y'
698 THEN
699 OE_DEBUG_PUB.ADD(' l_total_on_freight1 = ' || nvl(l_total_on_freight1,0));
700 END IF;
701
702 --Tax ER IF cust_glb_orders_freight1%NOTFOUND
703 IF l_total_on_freight1 = 0 --TaxER
704 THEN
705 l_total_on_freight1 := 0 ;
706 OE_DEBUG_PUB.Add('No Freight order amount found ');
707 END IF;
708
709 CLOSE cust_glb_orders_freight1;
710
711 IF G_debug_flag = 'Y'
712 THEN
713 OE_DEBUG_PUB.Add('Select cust_glb_orders_freight2 ');
714 END IF;
715
716 OPEN cust_glb_orders_freight2 (p_usage_curr(i).usage_curr_code,l_cust_acct_site_id); --bug 8744491
717 --TaxER FETCH cust_glb_orders_freight2 INTO l_total_on_freight2;
718 --TaxER Start
719 LOOP
720 FETCH cust_glb_orders_freight2 INTO l_total_on_freight2_tmp;
721 EXIT WHEN cust_glb_orders_freight2%NOTFOUND;
722 l_total_on_freight2 := l_total_on_freight2 + nvl(l_total_on_freight2_tmp,0);
723 END LOOP;
724 --TaxER end
725
726 IF G_debug_flag = 'Y'
727 THEN
728 OE_DEBUG_PUB.ADD(' l_total_on_freight2 = ' || nvl(l_total_on_freight2,0));
729 END IF;
730
731 --TaxER IF cust_glb_orders_freight2%NOTFOUND
732 IF l_total_on_freight2 = 0 --TaxER
733 THEN
734 l_total_on_freight2 := 0 ;
735 OE_DEBUG_PUB.Add('No Freight order amount found ');
736 END IF;
737
738 CLOSE cust_glb_orders_freight2;
739 END IF;
740 ELSE
741 IF l_include_uninvoiced_flag = 'Y'
742 THEN
743 IF G_debug_flag = 'Y'
744 THEN
745 OE_DEBUG_PUB.Add('Select cust_reg_orders ');
746 END IF;
747
748 OPEN cust_reg_orders(p_usage_curr(i).usage_curr_code,l_cust_acct_site_id); --bug 8744491
749 FETCH cust_reg_orders INTO l_total_on_order;
750
751 IF G_debug_flag = 'Y'
752 THEN
753 OE_DEBUG_PUB.ADD(' l_total_on_order = ' || nvl(l_total_on_order,0));
754 END IF;
755
756 IF cust_reg_orders%NOTFOUND
757 THEN
758 l_total_on_order := 0 ;
759 OE_DEBUG_PUB.Add('No Uninvoiced order amount found ');
760 END IF;
761
762 CLOSE cust_reg_orders;
763 END IF;
764
765 IF l_include_returns_flag = 'Y'
766 THEN
767 IF G_debug_flag = 'Y'
768 THEN
769 OE_DEBUG_PUB.Add('Select cust_reg_orders_return ');
770 END IF;
771
772 OPEN cust_reg_orders_return(p_usage_curr(i).usage_curr_code,l_cust_acct_site_id); --bug 8744491
773 FETCH cust_reg_orders_return INTO l_total_on_return;
774
775 IF G_debug_flag = 'Y'
776 THEN
777 OE_DEBUG_PUB.ADD(' l_total_on_return = ' || nvl(l_total_on_return,0));
778 END IF;
779
780 IF cust_reg_orders_return%NOTFOUND
781 THEN
782 l_total_on_return := 0 ;
783 OE_DEBUG_PUB.Add('No Return order amount found ');
784 END IF;
785
786 CLOSE cust_reg_orders_return;
787 END IF;
788
789 IF l_freight_charges_flag ='Y'
790 THEN
791 IF G_debug_flag = 'Y'
792 THEN
793 OE_DEBUG_PUB.Add('Select open cust_reg_orders_freight1 ');
794 END IF;
795
796 OPEN cust_reg_orders_freight1 (p_usage_curr(i).usage_curr_code,l_cust_acct_site_id); --bug 8744491
797 --TaxER FETCH cust_reg_orders_freight1 INTO l_total_on_freight1;
798 --TaxER Start
799 LOOP
800 FETCH cust_reg_orders_freight1 INTO l_total_on_freight1_tmp;
801 EXIT WHEN cust_reg_orders_freight1%NOTFOUND;
802 l_total_on_freight1 := l_total_on_freight1 + nvl(l_total_on_freight1_tmp,0);
803 END LOOP;
804 --TaxER end
805
806 IF G_debug_flag = 'Y'
807 THEN
808 OE_DEBUG_PUB.ADD(' l_total_on_freight1 = ' || nvl(l_total_on_freight1,0));
809 END IF;
810
811 --TaxER IF cust_reg_orders_freight1%NOTFOUND
812 IF l_total_on_freight1 = 0 --TaxER
813 THEN
814 l_total_on_freight1 := 0 ;
815 OE_DEBUG_PUB.Add('No Freight order amount found ');
816 END IF;
817
818 CLOSE cust_reg_orders_freight1;
819
820 IF G_debug_flag = 'Y'
821 THEN
822 OE_DEBUG_PUB.Add('Select open cust_reg_orders_freight2 ');
823 END IF;
824
825 OPEN cust_reg_orders_freight2 (p_usage_curr(i).usage_curr_code,l_cust_acct_site_id); --bug 8744491
829 FETCH cust_reg_orders_freight2 INTO l_total_on_freight2_tmp;
826 --TaxER FETCH cust_reg_orders_freight2 INTO l_total_on_freight2;
827 --TaxER Start
828 LOOP
830 EXIT WHEN cust_reg_orders_freight2%NOTFOUND;
831 l_total_on_freight2 := l_total_on_freight2 + nvl(l_total_on_freight2_tmp,0);
832 END LOOP;
833 --TaxER end
834
835 IF G_debug_flag = 'Y'
836 THEN
837 OE_DEBUG_PUB.ADD(' l_total_on_freight2 = ' || nvl(l_total_on_freight2,0));
838 END IF;
839
840 --TaxER IF cust_reg_orders_freight2%NOTFOUND
841 IF l_total_on_freight2 = 0 --TaxER
842 THEN
843 l_total_on_freight2 := 0 ;
844 OE_DEBUG_PUB.Add('No Freight order amount found ');
845 END IF;
846
847 CLOSE cust_reg_orders_freight2;
848 END IF;
849 END IF ; --- Global
850
851 ----------------------- End AR Exposure ------------------
852
853 l_cum_total_on_order := l_cum_total_on_order + NVL(l_total_on_order,0);
854 l_cum_total_on_return := l_cum_total_on_return + NVL(l_total_on_return,0);
855 l_cum_total_on_freight1:= l_cum_total_on_freight1 + NVL(l_total_on_freight1,0);
856 l_cum_total_on_freight2:= l_cum_total_on_freight2 + NVL(l_total_on_freight2,0);
857
858 l_usage_total_exposure := NVL(l_cum_total_on_order,0) + NVL(l_cum_total_on_return,0)
859 + NVL(l_cum_total_on_freight1,0) + NVL(l_cum_total_on_freight2,0);
860
861 IF G_debug_flag = 'Y'
862 THEN
863 OE_DEBUG_PUB.ADD(' ');
864 OE_DEBUG_PUB.ADD(' l_cum_total_on_order = ' || nvl(l_cum_total_on_order,0));
865 OE_DEBUG_PUB.ADD(' l_cum_total_on_return = ' || nvl(l_cum_total_on_return,0));
866 OE_DEBUG_PUB.ADD(' l_cum_total_on_freight1 = ' || nvl(l_total_on_freight1,0));
867 OE_DEBUG_PUB.ADD(' l_cum_total_on_freight2 = ' || nvl(l_total_on_freight2,0));
868 OE_DEBUG_PUB.ADD(' l_usage_total_exposure = ' || nvl(l_usage_total_exposure,0));
869
870 OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
871 OE_DEBUG_PUB.Add(' GL_CURRENCY = '|| OE_Credit_Engine_GRP.GL_currency );
872 END IF;
873
874 IF OE_Credit_Engine_GRP.GL_currency IS NULL
875 THEN
876 OE_DEBUG_PUB.ADD(' Call GET_GL_currency ');
877
878 OE_Credit_Engine_GRP.GL_currency := OE_CREDIT_CHECK_UTIL.GET_GL_currency ;
879
880 OE_DEBUG_PUB.ADD(' GL_CURRENCY after = ' || OE_Credit_Engine_GRP.GL_currency );
881 END IF;
882
883 l_limit_total_exposure :=
884 OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
885 ( p_amount => l_usage_total_exposure
886 , p_transactional_currency => p_usage_curr(i).usage_curr_code
887 , p_limit_currency => p_limit_curr_code
888 , p_functional_currency => OE_Credit_Engine_GRP.GL_currency
889 , p_conversion_date => SYSDATE
890 , p_conversion_type => p_credit_check_rule_rec.conversion_type
891 );
892
893 l_total_exposure := NVL(l_total_exposure,0) + NVL(l_limit_total_exposure,0) ;
894
895 IF G_debug_flag = 'Y'
896 THEN
897 OE_DEBUG_PUB.ADD('l_total_exposure = ' || nvl(l_total_exposure,0));
898 END IF;
899
900 l_cum_total_on_order := 0;
901 l_cum_total_on_return := 0;
902 l_cum_total_on_freight1 := 0;
903 l_cum_total_on_freight2 := 0;
904
905 l_usage_total_exposure := 0;
906 l_limit_total_exposure := 0;
907
908 l_total_on_order := 0;
909 l_total_on_return := 0;
910 l_total_on_freight1 := 0;
911 l_total_on_freight2 := 0;
912 l_total_on_freight1_tmp := 0; --TaxER
913 l_total_on_freight2_tmp := 0; --TaxER
914
915 END LOOP ; -- CURRENCY LOOP
916 END IF;
917
918 x_exposure_amount := NVL(l_total_exposure,0) ;
919
920 IF G_debug_flag = 'Y'
921 THEN
922 OE_DEBUG_PUB.Add(' ');
923 OE_DEBUG_PUB.Add('---------------##########----------------' );
924 OE_DEBUG_PUB.Add(' ');
925 OE_DEBUG_PUB.Add('Total exposure amount in Interface table = '|| x_exposure_amount,1);
926 OE_DEBUG_PUB.Add(' ');
930 END IF;
927 OE_DEBUG_PUB.Add('-------------- ##########----------------' );
928 OE_DEBUG_PUB.Add(' ');
929 OE_DEBUG_PUB.ADD('OEXUCERB: OUT NOCOPY Get_exposure_amount ',1);
931
932 EXCEPTION
933 WHEN GL_CURRENCY_API.NO_RATE THEN
934 OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE in get_exposure_amount',1);
935 OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
936 x_conversion_status(1).usage_curr_code := l_current_usage_cur ;
937
938 fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
939 OE_DEBUG_PUB.ADD('Exception table added ');
940 WHEN OTHERS THEN
941 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
942 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
943 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, '
944 Get_exposure_amount');
945 END IF;
946 RAISE;
947
948 END Get_exposure_amount ;
949
950 END OE_CREDIT_INTERFACE_UTIL ;