[Home] [Help]
PACKAGE BODY: APPS.OE_CREDIT_CHECK_RPT
Source
1 PACKAGE BODY OE_CREDIT_CHECK_RPT AS
2 -- $Header: OEXRCRCB.pls 120.8 2006/06/30 21:52:34 spooruli noship $
3 --==============================================================
4 -- TYPE DECLARATIONS
5 --==============================================================
6
7 --==============================================================
8 -- CONSTANTS
9 --==============================================================
10 --Global constant holding the package name
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_CREDIT_CREDIT_RPT';
12
13 --==============================================================
14 -- PUBLIC VARIABLES
15 --==============================================================
16 p_gl_currency_code VARCHAR2(30) := OE_CREDIT_CHECK_UTIL.GET_GL_CURRENCY;
17
18 --==============================================================
19 -- PROCEDURES AND FUNCTIONS
20 --==============================================================
21
22 --=====================================================================
23 -- PROCEDURE: Insert_In_Temp_Table
24 -- DESCRIPTION: Insert a row into the temporary table
25 --=====================================================================
26 PROCEDURE Insert_In_Temp_Table
27 ( p_header_id IN NUMBER
28 ,p_credit_status IN VARCHAR2
29 )
30 IS
31 l_party_name VARCHAR2(50);
32 l_account_number VARCHAR2(30);
33 l_transaction_type_name VARCHAR2(30);
34 l_credit_status VARCHAR2(80);
35 l_credit_status_sort NUMBER;
36 l_header_rec OE_Order_PUB.Header_Rec_Type;
37
38 BEGIN
39 OE_DEBUG_PUB.ADD('IN OEXRCRCB: Insert_In_Temp_Table');
40 --
41 -- Get order header record
42 --
43 OE_Header_UTIL.Query_Row
44 (p_header_id => p_header_id
45 ,x_header_rec => l_header_rec
46 );
47 --
48 BEGIN
49 SELECT SUBSTRB(HP.PARTY_NAME,1,50) NAME,
50 HCA.ACCOUNT_NUMBER
51 INTO l_party_name,
52 l_account_number
53 FROM HZ_CUST_ACCOUNTS HCA,
54 HZ_PARTIES HP
55 WHERE HCA.PARTY_ID = HP.PARTY_ID
56 AND HCA.CUST_ACCOUNT_ID = l_header_rec.sold_to_org_id;
57 OE_DEBUG_PUB.ADD('Customer Name/Number = '||l_party_name||'/'||l_account_number);
58 EXCEPTION
59 WHEN NO_DATA_FOUND THEN
60 OE_DEBUG_PUB.ADD('Custoner Name/Number not found for header_id '||p_header_id);
61 END;
62
63 BEGIN
64 SELECT otv.name
65 INTO l_transaction_type_name
66 FROM oe_transaction_types_vl otv
67 WHERE l_header_rec.order_type_id = otv.transaction_type_id;
68 OE_DEBUG_PUB.ADD('Order Type = '||l_transaction_type_name);
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71 OE_DEBUG_PUB.ADD('Order Type not found for header_id '||p_header_id);
72 END;
73
74 IF p_credit_status = 'PASS' THEN
75 l_credit_status_sort := 30;
76 ELSIF p_credit_status = 'FAIL' THEN
77 l_credit_status_sort := 20;
78 ELSE
79 l_credit_status_sort := 10;
80 END IF;
81
82 BEGIN
83 SELECT meaning
84 INTO l_credit_status
85 FROM oe_lookups
86 WHERE lookup_code = p_credit_status
87 AND lookup_type = 'CREDIT_CHECK_STATUS';
88 OE_DEBUG_PUB.ADD('Order Credit Status = '||l_credit_status);
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 OE_DEBUG_PUB.ADD('Credit status not found for lookup_code '||p_credit_status);
92 END;
93
94 INSERT INTO OE_CC_PROCESSOR_TEMP (
95 PARTY_NAME
96 ,ACCOUNT_NUMBER
97 ,TRANSACTION_TYPE_NAME
98 ,ORDER_NUMBER
99 ,CREDIT_STATUS
100 ,CREDIT_STATUS_SORT
101 )
102 VALUES (
103 l_party_name
104 ,l_account_number
105 ,l_transaction_type_name
106 ,l_header_rec.order_number
107 ,l_credit_status
108 ,l_credit_status_sort
109 );
110 OE_DEBUG_PUB.ADD('Inserted row into temporary table ');
111 OE_DEBUG_PUB.ADD('OUT OEXRCRCB: Insert_In_Temp_Table');
112 EXCEPTION
113 WHEN OTHERS THEN
114 OE_DEBUG_PUB.ADD('OEXRCRCB: Insert_In_Temp_Table - Unexpected Error');
115 RAISE;
116 END Insert_In_Temp_Table;
117
118 --=====================================================================
119 --PROCEDURE: Credit_Check_Processor PUBLIC
120 --
121 -- COMMENT : This is the pl/sql procedure for Credit Check Processor concurrent
122 -- program that perform credit check for a batch of sales orders.
123 -- It checks an order based on header id.
124 -- If header_id is NULL, it processes all the orders for a range of
125 -- profile classes, customer names, customer numbers, order dates
126 -- If all the parameters are NULL it picks up all order headers
127 -- of the profile organization [MO:Operating Unit]
128 --=====================================================================
129
130 PROCEDURE Credit_Check_Processor
131 ( p_profile_org_id IN NUMBER DEFAULT NULL
132 , p_cust_prof_class_name_from IN VARCHAR2 DEFAULT NULL
133 , p_cust_prof_class_name_to IN VARCHAR2 DEFAULT NULL
134 , p_party_name_from IN VARCHAR2 DEFAULT NULL
135 , p_party_name_to IN VARCHAR2 DEFAULT NULL
136 , p_cust_acct_number_from IN VARCHAR2 DEFAULT NULL
137 , p_cust_acct_number_to IN VARCHAR2 DEFAULT NULL
138 , p_order_date_from IN DATE DEFAULT NULL
139 , p_order_date_to IN DATE DEFAULT NULL
140 , p_header_id IN NUMBER DEFAULT NULL
141 , p_order_by IN VARCHAR2
142 )
143 IS
144 --================================
145 --API Name And Version
146 --================================
147 l_api_name CONSTANT VARCHAR2(30) := 'Credit_Check_Processor';
148 l_api_version CONSTANT NUMBER := 1.0;
149
150 --================================
151 --Variable and Cursor Declarations
152 --================================
153
154 l_msg_count NUMBER := 0 ;
155 l_msg_data VARCHAR2(2000):= NULL ;
156 l_result_out VARCHAR2(30) := NULL ;
157 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS ;
158 l_cc_limit_used VARCHAR2(30) := NULL ;
159 l_cc_profile_used VARCHAR2(30) := NULL ;
160 l_count NUMBER := 0;
161 l_cc_hold_comment VARCHAR2(2000):= NULL;
162 l_profile_org_id NUMBER;
163
164 --variables for dynamic sql query
165 v_hold_cursorID NUMBER;
166 v_hold_final_select VARCHAR2(7000) := NULL;
167
168 v_release_cursorID NUMBER;
169 v_release_final_select VARCHAR2(7000) := NULL;
170
171 v_dummy NUMBER;
172
173 v_order_by_clause VARCHAR2(1000) := NULL;
174 v_cust_prof_class_clause VARCHAR2(1000);
175 v_party_name_clause VARCHAR2(1000);
176 v_cust_acct_number_clause VARCHAR2(1000);
177 v_order_date_clause VARCHAR2(1000);
178
179 --Variables for input of dynamic sql
180 profile_org_id NUMBER;
181 cust_prof_class_from HZ_CUST_PROFILE_CLASSES.name%TYPE;
182 cust_prof_class_to HZ_CUST_PROFILE_CLASSES.name%TYPE;
183 party_name_from HZ_PARTIES.party_name%TYPE;
184 party_name_to HZ_PARTIES.party_name%TYPE;
185 cust_acct_number_from HZ_CUST_ACCOUNTS_ALL.account_number%TYPE;
186 cust_acct_number_to HZ_CUST_ACCOUNTS_ALL.account_number%TYPE;
187 order_date_from DATE;
188 order_date_to DATE;
189 gl_currency_code FND_CURRENCIES.currency_code%TYPE;
190
191 --Variables for output of dynamic sql
192 f_hold_header_id NUMBER;
193 f_release_header_id NUMBER;
194
195 -- MOAC start
196 CURSOR l_secured_ou_cur IS
197 SELECT ou.organization_id
198 FROM hr_operating_units ou
199 WHERE mo_global.check_access(ou.organization_id) = 'Y';
200
201 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
202 -- MOAC end
203
204 BEGIN
205 OE_DEBUG_PUB.ADD('IN OEXRCRCB: Credit_Check_Processor',1);
206 OE_DEBUG_PUB.ADD ('Input Parameters:',1);
207 OE_DEBUG_PUB.ADD ('p_profile_org_id = '||to_char(p_profile_org_id),1);
208 OE_DEBUG_PUB.ADD ('p_cust_prof_class_name_from = '||p_cust_prof_class_name_from,1);
209 OE_DEBUG_PUB.ADD ('p_cust_prof_class_name_to = '||p_cust_prof_class_name_to,1);
210 OE_DEBUG_PUB.ADD ('p_party_name_from = '||p_party_name_from,1);
211 OE_DEBUG_PUB.ADD ('p_party_name_to = '||p_party_name_to,1);
212 OE_DEBUG_PUB.ADD ('p_cust_acct_number_from = '||p_cust_acct_number_from,1);
213 OE_DEBUG_PUB.ADD ('p_cust_acct_number_to = '||p_cust_acct_number_to,1);
214 OE_DEBUG_PUB.ADD ('p_order_date_from = '||fnd_date.date_to_chardate(p_order_date_from),1);
215 OE_DEBUG_PUB.ADD ('p_order_date_To = '||fnd_date.date_to_chardate(p_order_date_to),1);
216 OE_DEBUG_PUB.ADD ('p_header_id = '||p_header_id,1);
217 OE_DEBUG_PUB.ADD ('p_order_by = '||p_order_by,1);
218
219 -- If a header_id is not provided, there is a possibility that more than one orders need
220 -- to be credit check, so the cursor needs to be built with the appropriate sorting.
221 -- This is done using dynamic sql.
222 -- If a header_id is provided, check credit for the combination of parameters provided.
223 -- For example: If an order number is provided and a customer range is provided, the
224 -- program will ensure that the order has the sold to org at the header is
225 -- within the customer range provided.
226
227 IF p_header_id is NULL OR p_header_id = 0 THEN
228 OE_DEBUG_PUB.ADD('Building the SELECT statement',2);
229 --
230 -- Build the sorting clause of the select statement
231 --
232 IF p_order_by = 'EARLIEST_ORDER_DATE' THEN
233 v_order_by_clause := 'ORDER BY OH.ordered_date';
234 ELSIF p_order_by = 'GREATEST_ORDER_VALUE' THEN
235 v_order_by_clause :=
236 'ORDER BY DECODE(OH.transactional_curr_code, :gl_currency_code,
237 oe_oe_totals_summary.prt_order_total(OH.header_id),
238 gl_currency_api.convert_closest_amount_sql(
239 OH.transactional_curr_code,
240 :gl_currency_code,
241 DECODE(OH.conversion_rate_date, NULL,SYSDATE,OH.conversion_rate_date),
242 DECODE(OH.conversion_type_code, NULL,''Corporate'',OH.conversion_type_code),
243 OH.conversion_rate,
244 oe_oe_totals_summary.prt_order_total(OH.header_id),
245 -1)) DESC, OH.ordered_date';
246 ELSIF p_order_by = 'EARLIEST_SHIP_DATE' THEN
247 v_order_by_clause := 'GROUP BY OH.header_id,OH.ordered_date
248 ORDER BY MIN(OL.schedule_ship_date), OH.ordered_date';
249 END IF;
250
251 --
252 -- Build the v_cust_prof_class_clause
253 --
254 IF p_cust_prof_class_name_from IS NOT NULL OR p_cust_prof_class_name_to IS NOT NULL THEN
255 IF p_cust_prof_class_name_from = p_cust_prof_class_name_to THEN
256 v_cust_prof_class_clause := 'AND EXISTS
257 (SELECT HCP.cust_account_id
258 FROM HZ_CUSTOMER_PROFILES HCP,
259 HZ_CUST_PROFILE_CLASSES HCPC
260 WHERE HCP.profile_class_id = HCPC.profile_class_id
261 AND HCP.cust_account_id = OH.sold_to_org_id
262 AND HCPC.name = :cust_prof_class_from) ';
263 ELSIF p_cust_prof_class_name_to IS NULL THEN
264 v_cust_prof_class_clause := 'AND EXISTS
265 (SELECT HCP.cust_account_id
266 FROM HZ_CUSTOMER_PROFILES HCP,
267 HZ_CUST_PROFILE_CLASSES HCPC
268 WHERE HCP.profile_class_id = HCPC.profile_class_id
269 AND HCP.cust_account_id = OH.sold_to_org_id
270 AND HCPC.name >= :cust_prof_class_from) ';
271 ELSIF p_cust_prof_class_name_from IS NULL THEN
272 v_cust_prof_class_clause := 'AND EXISTS
273 (SELECT HCP.cust_account_id
274 FROM HZ_CUSTOMER_PROFILES HCP,
275 HZ_CUST_PROFILE_CLASSES HCPC
276 WHERE HCP.profile_class_id = HCPC.profile_class_id
277 AND HCP.cust_account_id = OH.sold_to_org_id
278 AND HCPC.name <= :cust_prof_class_to) ';
279 ELSE
280 v_cust_prof_class_clause := 'AND EXISTS
281 (SELECT HCP.cust_account_id
282 FROM HZ_CUSTOMER_PROFILES HCP,
283 HZ_CUST_PROFILE_CLASSES HCPC
284 WHERE HCP.profile_class_id = HCPC.profile_class_id
285 AND HCP.cust_account_id = OH.sold_to_org_id
286 AND HCPC.name BETWEEN :cust_prof_class_from AND
287 :cust_prof_class_to) ';
288 END IF;
289 END IF;
290
291 --
292 -- Build the v_party_name_clause
293 --
294 IF p_party_name_from IS NOT NULL OR p_party_name_to IS NOT NULL THEN
295 IF p_party_name_from = p_party_name_to THEN
296 v_party_name_clause :=
297 'AND EXISTS (
298 SELECT HCA.cust_account_id
302 AND HCA.cust_account_id = OH.sold_to_org_id
299 FROM HZ_CUST_ACCOUNTS HCA,
300 HZ_PARTIES HP
301 WHERE HCA.party_id = HP.party_id
303 AND HP.party_name = :party_name_from
304 ) ';
305 ELSIF p_party_name_to IS NULL THEN
306 v_party_name_clause :=
307 'AND EXISTS (
308 SELECT HCA.cust_account_id
309 FROM HZ_CUST_ACCOUNTS HCA,
310 HZ_PARTIES HP
311 WHERE HCA.party_id = HP.party_id
312 AND HCA.cust_account_id = OH.sold_to_org_id
313 AND HP.party_name >= :party_name_from
314 ) ';
315 ELSIF p_party_name_from IS NULL THEN
316 v_party_name_clause :=
317 'AND EXISTS (
318 SELECT HCA.cust_account_id
319 FROM HZ_CUST_ACCOUNTS HCA,
320 HZ_PARTIES HP
321 WHERE HCA.party_id = HP.party_id
322 AND HCA.cust_account_id = OH.sold_to_org_id
323 AND HP.party_name <= :party_name_to
324 ) ';
325 ELSE
326 v_party_name_clause :=
327 'AND EXISTS (
328 SELECT HCA.cust_account_id
329 FROM HZ_CUST_ACCOUNTS HCA,
330 HZ_PARTIES HP
331 WHERE HCA.party_id = HP.party_id
332 AND HCA.cust_account_id = OH.sold_to_org_id
333 AND HP.party_name BETWEEN :party_name_from AND :party_name_to
334 ) ';
335 END IF;
336 END IF;
337 --
338 -- Build the v_cust_acct_number_clause
339 --
340 IF p_cust_acct_number_from IS NOT NULL OR p_cust_acct_number_to IS NOT NULL THEN
341 IF p_cust_acct_number_from = p_cust_acct_number_to THEN
342 v_cust_acct_number_clause :=
343 'AND EXISTS (
344 SELECT HCA.cust_account_id
345 FROM HZ_CUST_ACCOUNTS HCA
346 WHERE HCA.cust_account_id = OH.sold_to_org_id
347 AND HCA.account_number = :cust_acct_number_from
348 ) ';
349 ELSIF p_cust_acct_number_to IS NULL THEN
350 v_cust_acct_number_clause :=
351 'AND EXISTS (
352 SELECT HCA.cust_account_id
353 FROM HZ_CUST_ACCOUNTS HCA
354 WHERE HCA.cust_account_id = OH.sold_to_org_id
355 AND HCA.account_number >= :cust_acct_number_from
356 ) ';
357 ELSIF p_cust_acct_number_from IS NULL THEN
358 v_cust_acct_number_clause :=
359 'AND EXISTS (
360 SELECT HCA.cust_account_id
361 FROM HZ_CUST_ACCOUNTS HCA
362 WHERE HCA.cust_account_id = OH.sold_to_org_id
363 AND HCA.account_number <= :cust_acct_number_to
364 ) ';
365 ELSE
366 v_cust_acct_number_clause :=
367 'AND EXISTS (
368 SELECT HCA.cust_account_id
369 FROM HZ_CUST_ACCOUNTS HCA
370 WHERE HCA.cust_account_id = OH.sold_to_org_id
371 AND HCA.account_number BETWEEN :cust_acct_number_from AND :cust_acct_number_to
372 ) ';
373 END IF;
374 END IF;
375
376 --
377 -- Build the v_order_date_clause. No need to trunc the parameters since the
378 -- input accepted from the concurrent program is only the date format with
379 -- no time components.
380 --
381 IF p_order_date_from IS NOT NULL OR p_order_date_to IS NOT NULL THEN
382 IF p_order_date_from = p_order_date_to THEN
383 v_order_date_clause :=
384 'AND TRUNC(OH.ordered_date) = :order_date_from ';
385 ELSIF p_order_date_to IS NULL THEN
386 v_order_date_clause :=
387 'AND TRUNC(OH.ordered_date) >= :order_date_from ';
388 ELSIF p_order_date_from IS NULL THEN
389 v_order_date_clause :=
390 'AND TRUNC(OH.ordered_date) <= :order_date_to ';
391 ELSE
392 v_order_date_clause :=
393 'AND TRUNC(OH.ordered_date) BETWEEN :order_date_from AND :order_date_to ';
394 END IF;
395 END IF;
396
397 --
398 -- Build the complete select statement for dynamic sql processing
399 --
400 IF p_order_by = 'EARLIEST_SHIP_DATE' THEN
401 v_hold_final_select := 'SELECT OH.header_id
402 FROM OE_ORDER_HEADERS_ALL OH, OE_ORDER_LINES_ALL OL
403 WHERE OH.BOOKED_FLAG = ''Y''
407 AND OH.header_id = OL.header_id
404 AND OH.OPEN_FLAG = ''Y''
405 AND OH.order_category_code <> ''RETURN''
406 AND NVL(OH.org_id,-99) = :profile_org_id
408 AND OL.flow_status_code = ''BOOKED''
409 AND OL.open_flag = ''Y''
410 AND OL.booked_flag = ''Y''
411 AND OL.line_category_code = ''ORDER''
412 AND NVL(OL.invoiced_quantity, 0) = 0
413 AND NVL(OL.shipped_quantity, 0) = 0 '
414 ||v_cust_prof_class_clause
415 ||v_party_name_clause
416 ||v_cust_acct_number_clause
417 ||v_order_date_clause
418 ||v_order_by_clause;
419
420 v_release_final_select := 'SELECT OH.header_id
421 FROM OE_ORDER_HEADERS_ALL OH, OE_ORDER_LINES_ALL OL,
422 OE_HOLD_SOURCES_ALL OHS
423 WHERE OH.BOOKED_FLAG = ''Y''
424 AND OH.OPEN_FLAG = ''Y''
425 AND OH.order_category_code <> ''RETURN''
426 AND NVL(OH.org_id,-99) = :profile_org_id
427 AND OH.header_id = OL.header_id
428 AND OL.open_flag = ''Y''
429 AND OL.booked_flag = ''Y''
430 AND OL.line_category_code = ''ORDER''
431 AND NVL(OL.invoiced_quantity, 0) = 0
432 AND NVL(OL.shipped_quantity, 0) = 0
433 AND OH.header_id = OHS.hold_entity_id
434 AND OHS.hold_entity_code = ''O''
435 AND OHS.released_flag = ''N''
436 AND OHS.hold_id = 1
437 AND NVL(OHS.hold_until_date, SYSDATE+1) > SYSDATE '
438 ||v_cust_prof_class_clause
439 ||v_party_name_clause
440 ||v_cust_acct_number_clause
441 ||v_order_date_clause
442 ||v_order_by_clause;
443
444 ELSE
445 v_hold_final_select := 'SELECT OH.header_id
446 FROM OE_ORDER_HEADERS_ALL OH
447 WHERE OH.BOOKED_FLAG = ''Y''
448 AND OH.OPEN_FLAG = ''Y''
449 AND OH.order_category_code <> ''RETURN''
450 AND NVL(OH.org_id,-99) = :profile_org_id
451 AND EXISTS (SELECT 1
452 FROM OE_ORDER_LINES_ALL OL
453 WHERE NVL(OL.org_id,-99) = :profile_org_id
454 AND OL.header_id = OH.header_id
455 AND OL.line_category_code = ''ORDER''
456 AND OL.open_flag = ''Y''
457 AND OL.booked_flag = ''Y''
458 AND OL.flow_status_code = ''BOOKED''
459 AND NVL(OL.invoiced_quantity, 0) = 0
460 AND NVL(OL.shipped_quantity, 0) = 0) '
461 ||v_cust_prof_class_clause
462 ||v_party_name_clause
463 ||v_cust_acct_number_clause
464 ||v_order_date_clause
465 ||v_order_by_clause;
466
467 v_release_final_select := 'SELECT OH.header_id
468 FROM OE_ORDER_HEADERS_ALL OH
469 WHERE OH.BOOKED_FLAG = ''Y''
470 AND OH.OPEN_FLAG = ''Y''
471 AND OH.order_category_code <> ''RETURN''
472 AND NVL(OH.org_id,-99) = :profile_org_id
473 AND EXISTS (SELECT 1
474 FROM OE_HOLD_SOURCES_ALL OHS
475 WHERE OHS.hold_entity_code = ''O''
476 AND OHS.hold_id = 1
477 AND OHS.released_flag = ''N''
478 AND NVL(OHS.hold_until_date, SYSDATE+1) > SYSDATE
479 AND OHS.hold_entity_id = OH.header_id
480 )
481 AND EXISTS (SELECT 1
482 FROM OE_ORDER_LINES_ALL OL
483 WHERE NVL(OL.org_id,-99) = :profile_org_id
484 AND OL.header_id = OH.header_id
485 AND OL.open_flag = ''Y''
486 AND OL.booked_flag = ''Y''
487 AND OL.line_category_code = ''ORDER''
488 AND NVL(OL.invoiced_quantity, 0) = 0
489 AND NVL(OL.shipped_quantity, 0) = 0 ) '
490 ||v_cust_prof_class_clause
491 ||v_party_name_clause
492 ||v_cust_acct_number_clause
496 END IF;
493 ||v_order_date_clause
494 ||v_order_by_clause;
495
497 OE_DEBUG_PUB.ADD('Hold select : '||v_hold_final_select,2); --bug# 5187621
498 OE_DEBUG_PUB.ADD('Release select : '||v_release_final_select,2);
499 --
500 -- Open the dynamic sql cursor
501 --
502 v_hold_cursorID := DBMS_SQL.OPEN_CURSOR;
503 v_release_cursorID := DBMS_SQL.OPEN_CURSOR;
504 --
505 -- Parse the query
506 --
507 DBMS_SQL.PARSE(v_hold_cursorID, v_hold_final_select, DBMS_SQL.NATIVE);
508 DBMS_SQL.PARSE(v_release_cursorID, v_release_final_select, DBMS_SQL.NATIVE);
509 OE_DEBUG_PUB.ADD('Parsed the cursor',2);
510 --
511 -- Define input variables. Only bind the variables if it is part of the
512 -- select statement
513 --
514 OE_DEBUG_PUB.ADD('Start binding input variables',2);
515
516 IF p_order_by = 'GREATEST_ORDER_VALUE' THEN
517 DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':gl_currency_code',p_gl_currency_code);
518 DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':gl_currency_code',p_gl_currency_code);
519 OE_DEBUG_PUB.ADD('Binded gl_currency_code',2);
520 END IF;
521
522 IF INSTRB(v_cust_prof_class_clause,':cust_prof_class_from') <> 0 THEN
523 DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_prof_class_from',p_cust_prof_class_name_from);
524 DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_prof_class_from',p_cust_prof_class_name_from);
525 OE_DEBUG_PUB.ADD('Binded cust_prof_class_from',2);
526 END IF;
527
528 IF INSTRB(v_cust_prof_class_clause,':cust_prof_class_to') <> 0 THEN
529 DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_prof_class_to',p_cust_prof_class_name_to);
530 DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_prof_class_to' ,p_cust_prof_class_name_to);
531 OE_DEBUG_PUB.ADD('Binded cust_prof_class_to',2);
532 END IF;
533
534 IF INSTRB(v_party_name_clause, ':party_name_from') <> 0 THEN
535 DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':party_name_from',p_party_name_from);
536 DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':party_name_from',p_party_name_from);
537 OE_DEBUG_PUB.ADD('Binded party_name_from',2);
538 END IF;
539
540 IF INSTRB(v_party_name_clause, ':party_name_to') <> 0 THEN
541 DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':party_name_to',p_party_name_to);
542 DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':party_name_to' ,p_party_name_to);
543 OE_DEBUG_PUB.ADD('Binded party_name_to',2);
544 END IF;
545
546 IF INSTRB(v_cust_acct_number_clause, ':cust_acct_number_from') <> 0 THEN
547 DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_acct_number_from', p_cust_acct_number_from);
548 DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_acct_number_from', p_cust_acct_number_from);
549 OE_DEBUG_PUB.ADD('Binded cust_acct_number_from',2);
550 END IF;
551
552 IF INSTRB(v_cust_acct_number_clause, ':cust_acct_number_to') <> 0 THEN
553 DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_acct_number_to', p_cust_acct_number_to);
554 DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_acct_number_to', p_cust_acct_number_to);
555 OE_DEBUG_PUB.ADD('Binded cust_acct_number_to',2);
556 END IF;
557
558 IF INSTRB(v_order_date_clause, ':order_date_from') <> 0 THEN
559 DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_from', p_order_date_from);
560 DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':order_date_from', p_order_date_from);
561 OE_DEBUG_PUB.ADD('Binded order_date_from',2);
562 END IF;
563
564 IF INSTRB(v_order_date_clause, ':order_date_to') <> 0 THEN
565 DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_to', p_order_date_to);
566 DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':order_date_to', p_order_date_to);
567 OE_DEBUG_PUB.ADD('Binded order_date_to',2);
568 END IF;
569 OE_DEBUG_PUB.ADD('Finished binding input variables',2);
570 --
571 --Define the output variables
572 --
573 DBMS_SQL.DEFINE_COLUMN(v_hold_cursorID, 1, f_hold_header_id);
574 DBMS_SQL.DEFINE_COLUMN(v_release_cursorID, 1, f_release_header_id);
575
576 OE_DEBUG_PUB.ADD('Hold ID : '||v_hold_cursorID,2); --bug# 5187621
577 OE_DEBUG_PUB.ADD('Release ID : '||v_release_cursorID,2);
578 --
579 -- Set the l_profile_org_id value to use instead of checking for NULL
580 --
581 -- MOAC Start
582 l_profile_org_id := p_profile_org_id;
583
584 IF l_profile_org_id IS NOT NULL THEN
585 MO_GLOBAL.set_policy_context('S', l_profile_org_id);
586
587 IF l_debug_level > 0 THEN
588 OE_DEBUG_PUB.Add('org_id : ' || l_profile_org_id);
589 END IF;
590
591 DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':profile_org_id', l_profile_org_id);
592 DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':profile_org_id', l_profile_org_id);
593 OE_DEBUG_PUB.ADD('Binded profile_org_id',2);
594
595 --------------------------------------------------------------------
596 --
597 -- RELEASE: Execute the RELEASE select statement
598 --
599 v_dummy := DBMS_SQL.EXECUTE(v_release_cursorID);
600 --
601 -- RELEASE: Fetch the orders and credit check each one with calling action AUTO RELEASE
602 --
603 LOOP
604 IF DBMS_SQL.FETCH_ROWS(v_release_cursorID) = 0 THEN
608 --
605 EXIT;
606 END IF;
607 l_count := l_count+1;
609 -- Retreive the header ID into output variable
610 --
611 DBMS_SQL.COLUMN_VALUE(v_release_cursorID, 1, f_release_header_id);
612 OE_DEBUG_PUB.ADD('f_release_header_id = '|| f_release_header_id,4);
613 OE_Credit_Engine_GRP.Check_Credit
614 ( p_header_id => f_release_header_id
615 , p_calling_action => 'AUTO RELEASE'
616 , x_msg_count => l_msg_count
617 , x_msg_data => l_msg_data
618 , x_result_out => l_result_out
619 , x_cc_hold_comment => l_cc_hold_comment
620 , x_return_status => l_return_status
621 );
622 OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
623 --
624 -- Insert the result into the temporary table
625 --
626 IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
627 (l_result_out = 'PASS' OR l_result_out = 'PASS_REL')) --bug# 5187621
628 THEN
629 Insert_In_Temp_Table(f_release_header_id, 'PASS');
630 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) OR
631 (l_return_status = FND_API.G_RET_STS_SUCCESS AND
632 (l_result_out = 'FAIL' OR l_result_out = 'FAIL_HOLD' OR l_result_out = 'FAIL_NONE')) --bug# 5187621
633 THEN
634 Insert_In_Temp_Table(f_release_header_id, 'FAIL');
635 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
636 Insert_In_Temp_Table(f_release_header_id, 'ERROR');
637 END IF;
638 END LOOP;
639
640 DBMS_SQL.CLOSE_CURSOR(v_release_cursorID);
641
642 -------------------------------------------------------------------------
643 --
644 -- HOLD: Execute the HOLD select statement
645 --
646 v_dummy := DBMS_SQL.EXECUTE(v_hold_cursorID);
647
648 LOOP
649 IF DBMS_SQL.FETCH_ROWS(v_hold_cursorID) = 0 THEN
650 EXIT;
651 END IF;
652 l_count := l_count+1;
653 --
654 -- Retreive the header ID into output variable
655 --
656 DBMS_SQL.COLUMN_VALUE(v_hold_cursorID, 1, f_hold_header_id);
657 OE_DEBUG_PUB.ADD('f_hold_header_id = '|| f_hold_header_id,4);
658 OE_Credit_Engine_GRP.Check_Credit
659 ( p_header_id => f_hold_header_id
660 , p_calling_action => 'AUTO HOLD'
661 , x_msg_count => l_msg_count
662 , x_msg_data => l_msg_data
663 , x_result_out => l_result_out
664 , x_cc_hold_comment => l_cc_hold_comment
665 , x_return_status => l_return_status
666 );
667 OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
668 --
669 -- Insert the result into the temporary table
670 --
671 IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
672 (l_result_out = 'PASS' OR l_result_out = 'PASS_REL')) --bug# 5187621
673 THEN
674 Insert_In_Temp_Table(f_hold_header_id, 'PASS');
675 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) OR
676 (l_return_status = FND_API.G_RET_STS_SUCCESS AND
677 (l_result_out = 'FAIL' OR l_result_out = 'FAIL_HOLD' OR l_result_out = 'FAIL_NONE')) --bug# 5187621
678 THEN
679 Insert_In_Temp_Table(f_hold_header_id, 'FAIL');
680 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
681 Insert_In_Temp_Table(f_hold_header_id, 'ERROR');
682 END IF;
683 END LOOP;
684 --
685 DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
686 OE_DEBUG_PUB.ADD('Orders Processed = '||l_count,2);
687 ELSE
688 OPEN l_secured_ou_cur;
689
690 LOOP
691 FETCH l_secured_ou_cur
692 into l_profile_org_id;
693 EXIT WHEN l_secured_ou_cur%NOTFOUND;
694
695 IF l_profile_org_id IS NULL THEN
696 l_profile_org_id := mo_global.get_current_org_id;
697 END IF;
698
699 MO_GLOBAL.set_policy_context('S', l_profile_org_id);
700
701 IF l_debug_level > 0 THEN
702 OE_DEBUG_PUB.Add('org_id : ' || l_profile_org_id);
703 END IF;
704
705 DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':profile_org_id', l_profile_org_id);
706 DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':profile_org_id', l_profile_org_id);
707 OE_DEBUG_PUB.ADD('Binded profile_org_id',2);
708
709 --------------------------------------------------------------------
710 --
711 -- RELEASE: Execute the RELEASE select statement
712 --
713 v_dummy := DBMS_SQL.EXECUTE(v_release_cursorID);
714 --
715 -- RELEASE: Fetch the orders and credit check each one with calling action AUTO RELEASE
716 --
717 LOOP
718 IF DBMS_SQL.FETCH_ROWS(v_release_cursorID) = 0 THEN
719 EXIT;
720 END IF;
721 l_count := l_count+1;
722 --
723 -- Retreive the header ID into output variable
724 --
725 DBMS_SQL.COLUMN_VALUE(v_release_cursorID, 1, f_release_header_id);
729 , p_calling_action => 'AUTO RELEASE'
726 OE_DEBUG_PUB.ADD('f_release_header_id = '|| f_release_header_id,4);
727 OE_Credit_Engine_GRP.Check_Credit
728 ( p_header_id => f_release_header_id
730 , x_msg_count => l_msg_count
731 , x_msg_data => l_msg_data
732 , x_result_out => l_result_out
733 , x_cc_hold_comment => l_cc_hold_comment
734 , x_return_status => l_return_status
735 );
736 OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
737 --
738 -- Insert the result into the temporary table
739 --
740 IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
741 (l_result_out = 'PASS' OR l_result_out = 'PASS_REL')) --bug# 5187621
742 THEN
743 Insert_In_Temp_Table(f_release_header_id, 'PASS');
744 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) OR
745 (l_return_status = FND_API.G_RET_STS_SUCCESS AND
746 (l_result_out = 'FAIL' OR l_result_out = 'FAIL_HOLD' OR l_result_out = 'FAIL_NONE')) --bug# 5187621
747 THEN
748 Insert_In_Temp_Table(f_release_header_id, 'FAIL');
749 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
750 Insert_In_Temp_Table(f_release_header_id, 'ERROR');
751 END IF;
752 END LOOP;
753
754 -------------------------------------------------------------------------
755 --
756 -- HOLD: Execute the HOLD select statement
757 --
758 v_dummy := DBMS_SQL.EXECUTE(v_hold_cursorID);
759
760 LOOP
761 IF DBMS_SQL.FETCH_ROWS(v_hold_cursorID) = 0 THEN
762 EXIT;
763 END IF;
764 l_count := l_count+1;
765 --
766 -- Retreive the header ID into output variable
767 --
768 DBMS_SQL.COLUMN_VALUE(v_hold_cursorID, 1, f_hold_header_id);
769 OE_DEBUG_PUB.ADD('f_hold_header_id = '|| f_hold_header_id,4);
770 OE_Credit_Engine_GRP.Check_Credit
771 ( p_header_id => f_hold_header_id
772 , p_calling_action => 'AUTO HOLD'
773 , x_msg_count => l_msg_count
774 , x_msg_data => l_msg_data
775 , x_result_out => l_result_out
776 , x_cc_hold_comment => l_cc_hold_comment
777 , x_return_status => l_return_status
778 );
779 OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
780 --
781 -- Insert the result into the temporary table
782 --
783 IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
784 (l_result_out = 'PASS' OR l_result_out = 'PASS_REL')) --bug# 5187621
785 THEN
786 Insert_In_Temp_Table(f_hold_header_id, 'PASS');
787 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) OR
788 (l_return_status = FND_API.G_RET_STS_SUCCESS AND
789 (l_result_out = 'FAIL' OR l_result_out = 'FAIL_HOLD' OR l_result_out = 'FAIL_NONE')) --bug# 5187621
790 THEN
791 Insert_In_Temp_Table(f_hold_header_id, 'FAIL');
792 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
793 Insert_In_Temp_Table(f_hold_header_id, 'ERROR');
794 END IF;
795 END LOOP;
796 END LOOP;
797
798 DBMS_SQL.CLOSE_CURSOR(v_release_cursorID);
799 DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
800
801 OE_DEBUG_PUB.ADD('Orders Processed = '||l_count,2);
802
803 CLOSE l_secured_ou_cur;
804 END IF;
805 -- MOAC End
806 ELSE
807 --
808 -- Credit check the specific order if the header ID is provided for the Credit Check
809 -- Processor PL/SQL procedure. the Order Sequence parameter.
810 -- Call twice: once to release holds and once to place holds. This is not ideal, but we will
811 -- separate out the release and hold now.
812 -- Future: should give option to user to determine if program should place or release holds,
813 -- not both.
814 OE_Credit_Engine_GRP.Check_Credit
815 ( p_header_id => p_header_id
816 , p_calling_action => 'AUTO RELEASE'
817 , x_msg_count => l_msg_count
818 , x_msg_data => l_msg_data
819 , x_result_out => l_result_out
820 , x_cc_hold_comment => l_cc_hold_comment
821 , x_return_status => l_return_status
822 );
823 OE_DEBUG_PUB.ADD('Release: Check_Credit return status = '||l_return_status);
824 --
825 -- Insert the results into the temporary table
826 --
827 OE_DEBUG_PUB.ADD('Right before inserting into temp table');
828 IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
829 (l_result_out = 'PASS' OR l_result_out = 'PASS_REL')) --bug# 5187621
830 THEN
831 Insert_In_Temp_Table(p_header_id, 'PASS');
832 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) OR
833 (l_return_status = FND_API.G_RET_STS_SUCCESS AND
834 (l_result_out = 'FAIL' OR l_result_out = 'FAIL_HOLD' OR l_result_out = 'FAIL_NONE')) --bug# 5187621
835 THEN
836 Insert_In_Temp_Table(p_header_id, 'FAIL');
837 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
841
838 Insert_In_Temp_Table(p_header_id, 'ERROR');
839 END IF;
840 OE_DEBUG_PUB.ADD('Release: Right after inserting into temp table');
842 OE_Credit_Engine_GRP.Check_Credit
843 ( p_header_id => p_header_id
844 , p_calling_action => 'AUTO HOLD'
845 , x_msg_count => l_msg_count
846 , x_msg_data => l_msg_data
847 , x_result_out => l_result_out
848 , x_cc_hold_comment => l_cc_hold_comment
849 , x_return_status => l_return_status
850 );
851 OE_DEBUG_PUB.ADD('Hold: Check_Credit return status = '||l_return_status);
852 --
853 -- Insert the results into the temporary table
854 --
855 OE_DEBUG_PUB.ADD('Right before inserting into temp table');
856 IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
857 (l_result_out = 'PASS' OR l_result_out = 'PASS_REL')) --bug# 5187621
858 THEN
859 Insert_In_Temp_Table(p_header_id, 'PASS');
860 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) OR
861 (l_return_status = FND_API.G_RET_STS_SUCCESS AND
862 (l_result_out = 'FAIL' OR l_result_out = 'FAIL_HOLD' OR l_result_out = 'FAIL_NONE')) --bug# 5187621
863 THEN
864 Insert_In_Temp_Table(p_header_id, 'FAIL');
865 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
866 Insert_In_Temp_Table(p_header_id, 'ERROR');
867 END IF;
868 OE_DEBUG_PUB.ADD('Hold: Right after inserting into temp table');
869 END IF;
870
871 OE_DEBUG_PUB.ADD('OUT OEXRCRCB: Credit_Check_Processor',1);
872 EXCEPTION
873 WHEN GL_CURRENCY_API.NO_RATE THEN
874 OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Error',1);
875 OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE',1);
876 IF DBMS_SQL.IS_OPEN(v_hold_cursorID) THEN
877 DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
878 END IF;
879 IF DBMS_SQL.IS_OPEN(v_release_cursorID) THEN
880 DBMS_SQL.CLOSE_CURSOR(v_release_cursorID);
881 END IF;
882 RAISE;
883 WHEN OTHERS THEN
884 OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Unexpected Error',1);
885 OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
886 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
887 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Credit_Check_Processor');
888 END IF;
889 IF DBMS_SQL.IS_OPEN(v_hold_cursorID) THEN
890 DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
891 END IF;
892 IF DBMS_SQL.IS_OPEN(v_release_cursorID) THEN
893 DBMS_SQL.CLOSE_CURSOR(v_release_cursorID);
894 END IF;
895 RAISE;
896 END Credit_Check_Processor;
897
898
899 --========================================================================
900 -- PROCEDURE : Get_unchecked_exposure PUBLIC
901 -- PARAMETERS: p_party_id Party ID
902 -- p_customer_id customer ID
903 -- p_site_id bill-to site id
904 -- p_base_currency currency of the current operating unit
905 -- p_usage_curr_tbl table of all unchecked currencies
906 -- x_unchecked_expousre unchecked exposure
907 --
908 -- COMMENT : This procedure calculates unchecked exposure in the
909 -- base currency.
910 --
911 --=====================================================================
912 PROCEDURE Get_unchecked_exposure
913 ( p_party_id IN NUMBER DEFAULT NULL
914 , p_customer_id IN NUMBER
915 , p_site_id IN NUMBER
916 , p_base_currency IN VARCHAR2
917 , p_credit_check_rule_id IN NUMBER
918 , x_unchecked_expousre OUT NOCOPY NUMBER
919 , x_return_status OUT NOCOPY VARCHAR2
920 )
921 IS
922 ---cursor to select all currencies
923 CURSOR curr_csr
924 IS
925 SELECT
926 currency_code
927 FROM fnd_currencies
928 WHERE enabled_flag='Y'
929 AND currency_flag='Y';
930
931 l_seperator VARCHAR2(1) := '#';
932 l_start NUMBER := 1;
933 l_end NUMBER := 1;
934 l_return_status VARCHAR2(50);
935 l_currency VARCHAR2(10);
936 l_checked_curr_rec VARCHAR2(2000);
937 i INTEGER:=0;
938 j INTEGER:=0;
939 f INTEGER:=0;
940
941
942 l_credit_check_rule_rec OE_CREDIT_CHECK_UTIL.OE_CREDIT_RULES_REC_TYPE ;
943 l_system_parameters_rec OE_CREDIT_CHECK_UTIL.OE_SYSTEMS_PARAM_REC_TYPE ;
944 l_cust_unchk_curr_tbl OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
945 l_site_unchk_curr_tbl OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
946 l_total_exposure NUMBER;
947 l_error_curr_tbl OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
948 l_conversion_status OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
949 l_include_all_flag VARCHAR2(15);
950
951 l_order_amount NUMBER;
952 l_order_hold_amount NUMBER;
953 l_ar_amount NUMBER;
954
955
956 BEGIN
957 OE_DEBUG_PUB.ADD('IN Get_Unchecked_Exposure ');
958 x_return_status := FND_API.G_RET_STS_SUCCESS;
959
960
961 ---get credit check rule record
962 OE_CREDIT_CHECK_UTIL.GET_credit_check_rule
963 ( p_credit_check_rule_id => p_credit_check_rule_id
964 , x_credit_check_rules_rec => l_credit_check_rule_rec
965 );
966
967 -----Get system parameters record
971
968 OE_CREDIT_CHECK_UTIL.GET_System_parameters
969 ( x_system_parameter_rec=>l_system_parameters_rec
970 );
972 -----Party level-------
973 IF p_party_id IS NOT NULL AND p_customer_id IS NULL
974 THEN
975 OE_DEBUG_PUB.ADD('global table has rows= '||
976 TO_CHAR(OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT));
977
978 ----get unchecked usages
979 ---put checked currencies in one string with # as separator
980 FOR i in 1..OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT
981 LOOP
982
983 l_checked_curr_rec:=l_checked_curr_rec || l_seperator
984 || OE_Credit_Engine_GRP.G_cust_curr_tbl(i).usage_curr_code;
985
986 END LOOP;
987
988 ----build table for unchecked currencies
989
990 ---if there are no usage currencies
991 IF OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT=0
992 THEN
993 ---build the table for all currencies
994 OE_DEBUG_PUB.ADD('Build table for all currencies as unchecked');
995
996 FOR curr_csr_rec IN curr_csr
997 LOOP
998
999 j := j + 1;
1000 l_cust_unchk_curr_tbl(j).usage_curr_code
1001 := curr_csr_rec.currency_code;
1002 END LOOP;
1003
1004 ELSE
1005
1006 FOR curr_csr_rec IN curr_csr
1007 LOOP
1008 -- OE_DEBUG_PUB.ADD('Start loop for currency '||
1009 -- curr_csr_rec.currency_code);
1010
1011 IF INSTRB (l_checked_curr_rec,curr_csr_rec.currency_code,1,1)=0
1012 THEN
1013 j := j + 1;
1014 l_cust_unchk_curr_tbl(j).usage_curr_code
1015 := curr_csr_rec.currency_code;
1016
1017
1018 END IF;
1019 END LOOP;
1020
1021 END IF;
1022
1023 -----just for debuging----------------------------------------------------------
1024 OE_DEBUG_PUB.ADD('table for unchecked currencies for the party: ');
1025
1026 FOR k IN 1..l_cust_unchk_curr_tbl.COUNT
1027 LOOP
1028 OE_DEBUG_PUB.ADD('currency_code=: '||l_cust_unchk_curr_tbl(k).usage_curr_code);
1029 END LOOP;
1030 --------------------------------------------------------------------------------
1031
1032 -----calculate exposure
1033
1034 ----pre-calculate exposure
1035 IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1036 THEN
1037 OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1038 OE_DEBUG_PUB.ADD('Parameters:');
1039 OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1040 OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1041 OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1042 OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1043 OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1044 OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1045
1046 /*
1047
1048 OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1049 ( p_customer_id => p_customer_id
1050 , p_site_use_id => NULL
1051 , p_header_id => NULL
1052 , p_credit_check_rule_rec => l_credit_check_rule_rec
1053 , p_system_parameters_rec => l_system_parameters_rec
1054 , p_limit_curr_code => p_base_currency
1055 , p_usage_curr_tbl => l_cust_unchk_curr_tbl
1056 , p_include_all_flag => 'N'
1057 , x_total_exposure => x_unchecked_expousre
1058 , x_return_status => l_return_status
1059 , x_error_curr_tbl => l_error_curr_tbl
1060 );
1061
1062 */
1063
1064 OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1065 ( p_party_id => p_party_id
1066 , p_customer_id => NULL
1067 , p_site_use_id => NULL
1068 , p_header_id => NULL
1069 , p_credit_check_rule_rec => l_credit_check_rule_rec
1070 , p_system_parameters_rec => l_system_parameters_rec
1071 , p_limit_curr_code => p_base_currency
1072 , p_usage_curr_tbl => l_cust_unchk_curr_tbl
1073 , p_include_all_flag => 'N'
1074 , p_global_exposure_flag => 'N'
1075 , p_need_exposure_details => 'N'
1076 , x_total_exposure => x_unchecked_expousre
1077 , x_order_amount => l_order_amount
1078 , x_order_hold_amount => l_order_hold_amount
1079 , x_ar_amount => l_ar_amount
1080 , x_return_status => l_return_status
1081 , x_error_curr_tbl => l_error_curr_tbl
1082 );
1083
1084
1085 OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1086 OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1087 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1088
1089 IF l_error_curr_tbl.COUNT<>0
1090 THEN
1091 FOR f IN 1..l_error_curr_tbl.COUNT
1092 LOOP
1093 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1094
1095 OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1096 base currency '||p_base_currency||' is missing for conversion type '||
1097 NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1098
1099 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1103
1100 END LOOP;
1101
1102 ---bug fix 2439029
1104 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1105
1106 x_return_status:='C';
1107
1108 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1109 THEN
1110 RAISE FND_API.G_EXC_ERROR;
1111 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1112 THEN
1113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1114 END IF;
1115
1116 ----non pre-calculate exposure
1117 ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1118 THEN
1119 OE_DEBUG_PUB.ADD('Party level exposure is supported only for
1120 credit check rule with pre-calculated exposure');
1121
1122 x_unchecked_expousre:=0;
1123
1124 END IF;
1125
1126 ----Customer level
1127 ELSIF p_site_id IS NULL AND p_party_id IS NULL
1128 THEN
1129 OE_DEBUG_PUB.ADD('global table has rows= '||
1130 TO_CHAR(OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT));
1131
1132 ----get unchecked usages
1133 ---put checked currencies in one string with # as separator
1134 FOR i in 1..OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT
1135 LOOP
1136
1137 l_checked_curr_rec:=l_checked_curr_rec || l_seperator
1138 || OE_Credit_Engine_GRP.G_cust_curr_tbl(i).usage_curr_code;
1139
1140 END LOOP;
1141
1142 ----build table for unchecked currencies
1143
1144 ---if there are no usage currencies
1145 IF OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT=0
1146 THEN
1147 ---build the table for all currencies
1148 OE_DEBUG_PUB.ADD('Build table for all currencies as unchecked');
1149
1150 FOR curr_csr_rec IN curr_csr
1151 LOOP
1152
1153 j := j + 1;
1154 l_cust_unchk_curr_tbl(j).usage_curr_code
1155 := curr_csr_rec.currency_code;
1156 END LOOP;
1157
1158 ELSE
1159
1160 FOR curr_csr_rec IN curr_csr
1161 LOOP
1162 -- OE_DEBUG_PUB.ADD('Start loop for currency '||
1163 -- curr_csr_rec.currency_code);
1164
1165 IF INSTRB (l_checked_curr_rec,curr_csr_rec.currency_code,1,1)=0
1166 THEN
1167 j := j + 1;
1168 l_cust_unchk_curr_tbl(j).usage_curr_code
1169 := curr_csr_rec.currency_code;
1170
1171
1172 END IF;
1173 END LOOP;
1174
1175 END IF;
1176
1177 -----just for debuging----------------------------------------------------------
1178 OE_DEBUG_PUB.ADD('table for unchecked currencies for the customer: ');
1179
1180 FOR k IN 1..l_cust_unchk_curr_tbl.COUNT
1181 LOOP
1182 OE_DEBUG_PUB.ADD('currency_code=: '||l_cust_unchk_curr_tbl(k).usage_curr_code);
1183 END LOOP;
1184 --------------------------------------------------------------------------------
1185
1186 -----calculate exposure
1187
1188 ----pre-calculate exposure
1189 IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1190 THEN
1191 OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1192 OE_DEBUG_PUB.ADD('Parameters:');
1193 OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1194 OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1195 OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1196 OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1197 OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1198
1199 /*
1200
1201 OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1202 ( p_customer_id => p_customer_id
1203 , p_site_use_id => NULL
1204 , p_header_id => NULL
1205 , p_credit_check_rule_rec => l_credit_check_rule_rec
1206 , p_system_parameters_rec => l_system_parameters_rec
1207 , p_limit_curr_code => p_base_currency
1208 , p_usage_curr_tbl => l_cust_unchk_curr_tbl
1209 , p_include_all_flag => 'N'
1210 , x_total_exposure => x_unchecked_expousre
1211 , x_return_status => l_return_status
1212 , x_error_curr_tbl => l_error_curr_tbl
1213 );
1214
1215 */
1216
1217 OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1218 ( p_customer_id => p_customer_id
1219 , p_site_use_id => NULL
1220 , p_party_id => NULL
1221 , p_header_id => NULL
1222 , p_credit_check_rule_rec => l_credit_check_rule_rec
1223 , p_system_parameters_rec => l_system_parameters_rec
1224 , p_limit_curr_code => p_base_currency
1225 , p_usage_curr_tbl => l_cust_unchk_curr_tbl
1226 , p_include_all_flag => 'N'
1227 , p_global_exposure_flag => 'N'
1228 , p_need_exposure_details => 'N'
1229 , x_total_exposure => x_unchecked_expousre
1230 , x_order_amount => l_order_amount
1231 , x_order_hold_amount => l_order_hold_amount
1232 , x_ar_amount => l_ar_amount
1233 , x_return_status => l_return_status
1234 , x_error_curr_tbl => l_error_curr_tbl
1235 );
1236
1237
1238 OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1239 OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1240 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1241
1242 IF l_error_curr_tbl.COUNT<>0
1243 THEN
1244 FOR f IN 1..l_error_curr_tbl.COUNT
1248 OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1245 LOOP
1246 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1247
1249 base currency '||p_base_currency||' is missing for conversion type '||
1250 NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1251
1252 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1253 END LOOP;
1254
1255 ---bug fix 2439029
1256
1257 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1258
1259 x_return_status:='C';
1260
1261 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1262 THEN
1263 RAISE FND_API.G_EXC_ERROR;
1264 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1265 THEN
1266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1267 END IF;
1268
1269 ----non pre-calculate exposure
1270 ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1271 THEN
1272 OE_DEBUG_PUB.ADD('OEXPCRGB: IN OE_CREDIT_ENGIN_GRB.Get_order_Exposure ');
1273 OE_DEBUG_PUB.ADD('Parameters:');
1274 OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1275 OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1276 OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1277 OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1278 OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1279 /*
1280 OE_CREDIT_CHECK_UTIL.Get_order_exposure
1281 ( p_header_id => NULL
1282 , p_transaction_curr_code => NULL
1283 , p_customer_id => p_customer_id
1284 , p_site_use_id => NULL
1285 , p_credit_check_rule_rec => l_credit_check_rule_rec
1286 , p_system_parameter_rec => l_system_parameters_rec
1287 , p_credit_level => 'CUSTOMER'
1288 , p_limit_curr_code => p_base_currency
1289 , p_usage_curr => l_cust_unchk_curr_tbl
1290 , p_include_all_flag => 'N'
1291 , x_total_exposure => x_unchecked_expousre
1292 , x_return_status => l_return_status
1293 , x_conversion_status => l_conversion_status
1294 );
1295 */
1296
1297 OE_CREDIT_CHECK_UTIL.Get_order_exposure
1298 ( p_header_id => NULL
1299 , p_transaction_curr_code => NULL
1300 , p_customer_id => p_customer_id
1301 , p_site_use_id => NULL
1302 , p_credit_check_rule_rec => l_credit_check_rule_rec
1303 , p_system_parameter_rec => l_system_parameters_rec
1304 , p_credit_level => 'CUSTOMER'
1305 , p_limit_curr_code => p_base_currency
1306 , p_usage_curr => l_cust_unchk_curr_tbl
1307 , p_include_all_flag => 'N'
1308 , p_global_exposure_flag => 'N'
1309 , p_need_exposure_details => 'N'
1310 , x_total_exposure => x_unchecked_expousre
1311 , x_ar_amount => l_ar_amount
1312 , x_order_amount => l_order_amount
1313 , x_order_hold_amount => l_order_hold_amount
1314 , x_conversion_status => l_conversion_status
1315 , x_return_status => l_return_status
1316 );
1317
1318
1319 OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1320 OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1321 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1322
1323 IF l_conversion_status.COUNT<>0
1324 THEN
1325 FOR f IN 1..l_conversion_status.COUNT
1326 LOOP
1327 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1328
1329 OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1330 base currency '||p_base_currency||' is missing for conversion type '||
1331 NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1332
1333 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1334
1335 END LOOP;
1336
1337 ---bug fix 2439029
1338
1339 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1340
1341 x_return_status:='C';
1342
1343 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1344 THEN
1345 RAISE FND_API.G_EXC_ERROR;
1346 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1347 THEN
1348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1349 END IF;
1350
1351 END IF;
1352
1353 -----Bill-to Site Level
1354 ELSIF p_site_id IS NOT NULL
1355 THEN
1356
1357 OE_DEBUG_PUB.ADD('global table has rows= '||
1358 TO_CHAR(OE_Credit_Engine_GRP.G_site_curr_tbl.COUNT));
1359 ----get unchecked usages
1360 ---put checked currencies in one string with # as separator
1361 FOR i in 1..OE_CREDIT_ENGINE_GRP.G_site_curr_tbl.COUNT
1362 LOOP
1363 l_checked_curr_rec:=l_checked_curr_rec || l_seperator || OE_CREDIT_ENGINE_GRP.G_site_curr_tbl(i).usage_curr_code;
1364 END LOOP;
1365
1366 ----build table for unchecked currencies
1367
1368 ---if there are no usage currencies
1369 IF OE_Credit_Engine_GRP.G_site_curr_tbl.COUNT=0
1370 THEN
1371 ---build the table for all currencies
1372 OE_DEBUG_PUB.ADD('Build table for all currencies as unchecked');
1373
1374 FOR curr_csr_rec IN curr_csr
1375 LOOP
1376
1377 j := j + 1;
1381 ELSE
1378 l_site_unchk_curr_tbl(j).usage_curr_code :=
1379 curr_csr_rec.currency_code;
1380 END LOOP;
1382
1383 FOR curr_csr_rec IN curr_csr
1384 LOOP
1385
1386 IF INSTRB (l_checked_curr_rec,curr_csr_rec.currency_code,1,1)=0
1387 THEN
1388 j := j + 1;
1389 l_site_unchk_curr_tbl(j).usage_curr_code :=
1390 curr_csr_rec.currency_code;
1391
1392 END IF;
1393 END LOOP;
1394
1395 END IF;
1396
1397 -----just for debuging----------------------------------------------------------
1398 OE_DEBUG_PUB.ADD('table for unchecked currencies for the site: ');
1399
1400 FOR k IN 1..l_site_unchk_curr_tbl.COUNT
1401 LOOP
1402 OE_DEBUG_PUB.ADD('currency_code=: '||l_site_unchk_curr_tbl(k).usage_curr_code);
1403 END LOOP;
1404 --------------------------------------------------------------------------------
1405
1406 -----calculate exposure
1407
1408 ----pre-calculate exposure
1409 IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1410 THEN
1411 OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1412 OE_DEBUG_PUB.ADD('Parameters:');
1413 OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1414 OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1415 OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1416 OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1417 OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1418 /*
1419 OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1420 ( p_customer_id => p_customer_id
1421 , p_site_use_id => p_site_id
1422 , p_header_id => NULL
1423 , p_credit_check_rule_rec => l_credit_check_rule_rec
1424 , p_system_parameters_rec => l_system_parameters_rec
1425 , p_limit_curr_code => p_base_currency
1426 , p_usage_curr_tbl => l_site_unchk_curr_tbl
1427 , p_include_all_flag => 'N'
1428 , x_total_exposure => x_unchecked_expousre
1429 , x_return_status => l_return_status
1430 , x_error_curr_tbl => l_error_curr_tbl
1431 );
1432 */
1433
1434
1435 OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1436 ( p_customer_id => p_customer_id
1437 , p_site_use_id => p_site_id
1438 , p_party_id => NULL
1439 , p_header_id => NULL
1440 , p_credit_check_rule_rec => l_credit_check_rule_rec
1441 , p_system_parameters_rec => l_system_parameters_rec
1442 , p_limit_curr_code => p_base_currency
1443 , p_usage_curr_tbl => l_site_unchk_curr_tbl
1444 , p_include_all_flag => 'N'
1445 , p_global_exposure_flag => 'N'
1446 , p_need_exposure_details => 'N'
1447 , x_total_exposure => x_unchecked_expousre
1448 , x_order_amount => l_order_amount
1449 , x_order_hold_amount => l_order_hold_amount
1450 , x_ar_amount => l_ar_amount
1451 , x_return_status => l_return_status
1452 , x_error_curr_tbl => l_error_curr_tbl
1453 );
1454
1455
1456 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1457 OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1458 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1459
1460 IF l_error_curr_tbl.COUNT<>0
1461 THEN
1462 FOR f IN 1..l_error_curr_tbl.COUNT
1463 LOOP
1464 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1465
1466 OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1467 base currency '||p_base_currency||' is missing for conversion type '||
1468 NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1469
1470 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1471
1472 END LOOP;
1473
1474 ---bug fix 2439029
1475
1476 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1477
1478 x_return_status:='C';
1479
1480 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1481 THEN
1482 RAISE FND_API.G_EXC_ERROR;
1483 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1484 THEN
1485 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1486 END IF;
1487
1488 ----non pre-calculate exposure
1489 ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1490 THEN
1491 OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_CHECK_UTIL.Get_order_Exposure ');
1492 OE_DEBUG_PUB.ADD('Parameters:');
1493 OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1494 OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1495 OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1496 OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1497 OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1498 OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1499 OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1500 /*
1501 OE_CREDIT_CHECK_UTIL.Get_order_exposure
1502 ( p_header_id => NULL
1503 , p_transaction_curr_code => NULL
1504 , p_customer_id => p_customer_id
1505 , p_site_use_id => p_site_id
1506 , p_credit_check_rule_rec => l_credit_check_rule_rec
1507 , p_system_parameter_rec => l_system_parameters_rec
1508 , p_credit_level => 'SITE'
1512 , x_total_exposure => x_unchecked_expousre
1509 , p_limit_curr_code => p_base_currency
1510 , p_usage_curr => l_site_unchk_curr_tbl
1511 , p_include_all_flag => 'N'
1513 , x_return_status => l_return_status
1514 , x_conversion_status => l_conversion_status
1515 );
1516 */
1517
1518 OE_CREDIT_CHECK_UTIL.Get_order_exposure
1519 ( p_header_id => NULL
1520 , p_transaction_curr_code => NULL
1521 , p_customer_id => p_customer_id
1522 , p_site_use_id => p_site_id
1523 , p_credit_check_rule_rec => l_credit_check_rule_rec
1524 , p_system_parameter_rec => l_system_parameters_rec
1525 , p_credit_level => 'SITE'
1526 , p_limit_curr_code => p_base_currency
1527 , p_usage_curr => l_site_unchk_curr_tbl
1528 , p_include_all_flag => 'N'
1529 , p_global_exposure_flag => 'N'
1530 , p_need_exposure_details => 'N'
1531 , x_total_exposure => x_unchecked_expousre
1532 , x_ar_amount => l_ar_amount
1533 , x_order_amount => l_order_amount
1534 , x_order_hold_amount => l_order_hold_amount
1535 , x_conversion_status => l_conversion_status
1536 , x_return_status => l_return_status
1537 );
1538
1539
1540 OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1541 OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1542 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1543
1544 IF l_conversion_status.COUNT<>0
1545 THEN
1546
1547 FOR f IN 1..l_conversion_status.COUNT
1548 LOOP
1549 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1550
1551 OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1552 base currency '||p_base_currency||' is missing for conversion type '||
1553 NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1554
1555 OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1556
1557 END LOOP;
1558
1559 ---bug fix 2439029
1560
1561 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1562
1563 x_return_status:='C';
1564
1565 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
1566 THEN
1567 RAISE FND_API.G_EXC_ERROR;
1568 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
1569 THEN
1570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1571 END IF;
1572
1573 END IF;
1574
1575 END IF;
1576
1577 OE_DEBUG_PUB.ADD('Out Get_unchecked_exposure with status='||x_return_status);
1578 OE_DEBUG_PUB.ADD('unchecked_exposure='||x_unchecked_expousre);
1579
1580
1581 EXCEPTION
1582 WHEN OTHERS THEN
1583 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1584 OE_MSG_PUB.Add_Exc_Msg
1585 ( G_PKG_NAME
1586 , 'Get_unchecked_exposure'
1587 );
1588 END IF;
1589 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
1590
1591
1592
1593 END Get_unchecked_exposure;
1594
1595 --========================================================================
1596 -- PROCEDURE : Credit_exposure_report_utils PUBLIC
1597 -- PARAMETERS: p_report_by_option
1598 -- p_specific_party_id
1599 -- p_specific_party_num
1600 -- p_party_name_low
1601 -- p_party_name_high
1602 -- p_party_number_low
1603 -- p_party_number_high
1604 -- p_prof_class_low customer profile class name from
1605 -- p_prof_class_high customer profile class name to
1606 -- p_customer_name_low customer name from
1607 -- p_customer_name_high customer name to
1608 -- p_cust_number_low customer number from
1609 -- p_cust_number_high customer number to
1610 -- p_cr_check_rule_id credit check rule
1611 -- p_org_id
1612 -- x_return_status
1613 --
1614 -- COMMENT : This is the main procedure for Credit Exposure Report. It calculates
1615 -- exposure and populates temp table OE_CREDIT_EXPOSURE_TMP
1616 --
1617 --=======================================================================--
1618 PROCEDURE Credit_exposure_report_utils
1619 ( p_report_by_option IN VARCHAR2 DEFAULT NULL
1620 , p_specific_party_id IN NUMBER DEFAULT NULL
1621 , p_spec_party_num_id IN NUMBER DEFAULT NULL
1622 , p_party_name_low IN VARCHAR2 DEFAULT NULL
1623 , p_party_name_high IN VARCHAR2 DEFAULT NULL
1624 , p_party_number_low IN VARCHAR2 DEFAULT NULL
1625 , p_party_number_high IN VARCHAR2 DEFAULT NULL
1626 , p_prof_class_low IN VARCHAR2 DEFAULT NULL
1627 , p_prof_class_high IN VARCHAR2 DEFAULT NULL
1628 , p_customer_name_low IN VARCHAR2 DEFAULT NULL
1629 , p_customer_name_high IN VARCHAR2 DEFAULT NULL
1630 , p_cust_number_low IN VARCHAR2 DEFAULT NULL
1631 , p_cust_number_high IN VARCHAR2 DEFAULT NULL
1632 , p_cr_check_rule_id IN NUMBER
1633 , p_org_id IN NUMBER
1634 , x_return_status OUT NOCOPY VARCHAR2
1635 )
1636
1637 IS
1638
1639 Type cust_cur_type IS REF CURSOR;
1640 cust_cur cust_cur_type;
1641
1642 TYPE cust_type is RECORD (
1643 customer VARCHAR2(150),
1647 cust_csr_rec cust_type;
1644 customer_id hz_cust_accounts.cust_account_id%TYPE,
1645 customer_number hz_cust_accounts.account_number%TYPE);
1646
1648
1649 l_input NUMBER := 0;
1650
1651 ---cursor to select all eligible parties for Party Range Summary
1652 /*
1653 CURSOR party_csr(l_input NUMBER)
1654 IS
1655 SELECT
1656 p.party_name||'('||p.party_number||')' Party
1657 , p.party_id
1658 , p.party_number
1659 FROM
1660 hz_parties p
1661 WHERE party_type IN ('ORGANIZATION','PERSON')
1662 AND party_name BETWEEN p_party_name_low
1663 AND NVL(p_party_name_high, party_name)
1664 AND party_number BETWEEN NVL(p_party_number_low, party_number )
1665 AND NVL(p_party_number_high, party_number )
1666 AND l_input = 1
1667 UNION
1668 SELECT
1669 p.party_name||'('||p.party_number||')' Party
1670 , p.party_id
1671 , p.party_number
1672 FROM
1673 hz_parties p
1674 WHERE party_type IN ('ORGANIZATION','PERSON')
1675 AND party_name <= p_party_name_high
1676 AND party_number BETWEEN NVL(p_party_number_low, party_number )
1677 AND NVL(p_party_number_high, party_number )
1678 AND l_input = 2
1679 UNION
1680 SELECT
1681 p.party_name||'('||p.party_number||')' Party
1682 , p.party_id
1683 , p.party_number
1684 FROM
1685 hz_parties p
1686 WHERE party_type IN ('ORGANIZATION','PERSON')
1687 AND party_number BETWEEN p_party_number_low
1688 AND NVL(p_party_number_high, party_number )
1689 AND l_input = 3
1690 UNION
1691 SELECT
1692 p.party_name||'('||p.party_number||')' Party
1693 , p.party_id
1694 , p.party_number
1695 FROM
1696 hz_parties p
1697 WHERE party_type IN ('ORGANIZATION','PERSON')
1698 AND party_number <= p_party_number_high
1699 AND l_input = 4
1700 ; */
1701
1702
1703 -- 5212830
1704
1705 CURSOR party_csr1(l_input NUMBER)IS
1706 SELECT
1707 p.party_name||'('||p.party_number||')' Party
1708 , p.party_id
1709 , p.party_number
1710 FROM
1711 hz_parties p
1712 WHERE party_type IN ('ORGANIZATION','PERSON')
1713 AND party_name BETWEEN p_party_name_low
1714 AND NVL(p_party_name_high, party_name)
1715 AND party_number BETWEEN NVL(p_party_number_low, party_number )
1716 AND NVL(p_party_number_high, party_number )
1717 AND l_input = 1 ;
1718
1719 CURSOR party_csr2(l_input NUMBER)IS
1720 SELECT
1721 p.party_name||'('||p.party_number||')' Party
1722 , p.party_id
1723 , p.party_number
1724 FROM
1725 hz_parties p
1726 WHERE party_type IN ('ORGANIZATION','PERSON')
1727 AND party_name <= p_party_name_high
1728 AND party_number BETWEEN NVL(p_party_number_low, party_number )
1729 AND NVL(p_party_number_high, party_number )
1730 AND l_input = 2;
1731
1732 CURSOR party_csr3(l_input NUMBER)IS
1733 SELECT
1734 p.party_name||'('||p.party_number||')' Party
1735 , p.party_id
1736 , p.party_number
1737 FROM
1738 hz_parties p
1739 WHERE party_type IN ('ORGANIZATION','PERSON')
1740 AND party_number BETWEEN p_party_number_low
1741 AND NVL(p_party_number_high, party_number )
1742 AND l_input = 3;
1743
1744 CURSOR party_csr4(l_input NUMBER)IS
1745 SELECT
1746 p.party_name||'('||p.party_number||')' Party
1747 , p.party_id
1748 , p.party_number
1749 FROM
1750 hz_parties p
1751 WHERE party_type IN ('ORGANIZATION','PERSON')
1752 AND party_number <= p_party_number_high
1753 AND l_input = 4;
1754
1755 party_csr_rec party_csr1%ROWTYPE;
1756
1757 -- 5212830
1758
1759
1760 ---cursor to select parties in hierarchical order
1761 ---for Party Detail
1762 CURSOR party_hier_csr
1763 IS
1764 SELECT
1765 p.party_name||'('||p.party_number||')' Party
1766 , n.child_id party_id
1767 , p.party_number party_number
1768 , NVL(n.level_number,0) level_number
1769 FROM
1770 hz_parties p, hz_hierarchy_nodes n
1771 WHERE p.party_id=n.child_id
1772 AND n.parent_object_type = 'ORGANIZATION'
1773 AND n.parent_table_name = 'HZ_PARTIES'
1774 AND n.child_object_type = 'ORGANIZATION'
1775 AND n.effective_start_date <= SYSDATE
1776 AND n.effective_end_date >= SYSDATE
1777 AND n.hierarchy_type
1778 = OE_CREDIT_CHECK_UTIL.G_hierarchy_type
1779 AND n.parent_id=NVL(p_specific_party_id,p_spec_party_num_id)
1780 ORDER BY level_number;
1781
1782 ---cursor to select parties if they are not part of the
1783 -- hierarchical order,for Party Detail
1784 CURSOR party_hier_csr1
1785 IS
1786 SELECT
1787 p.party_name||'('||p.party_number||')' Party
1788 , p.party_id
1789 , p.party_number party_number
1790 FROM
1791 hz_parties p
1792 WHERE p.party_id=NVL(p_specific_party_id,p_spec_party_num_id);
1793
1794
1795 ----cursor to select all credit profiles for the given party
1796 CURSOR party_prof_csr(p_party_id NUMBER)
1797 IS
1798 SELECT
1799 cpa.currency_code party_currency_code
1800 , cpa.overall_credit_limit party_overall_limit
1801 FROM
1802 hz_customer_profiles cp
1803 , hz_cust_profile_amts cpa
1807 AND cp.party_id=p_party_id;
1804 WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
1805 AND cp.site_use_id IS NULL
1806 AND cp.cust_account_id=-1
1808
1809
1810 ---cursor to select all eligible customers for a given party
1811 CURSOR party_cust_csr(p_party_id IN NUMBER)
1812 IS
1813 SELECT
1814 SUBSTRB(p.party_name,1,50) ||'('||c.account_number||')' Customer
1815 , c.cust_account_id customer_id
1816 , c.account_number customer_number
1817 FROM
1818 hz_cust_accounts c
1819 , hz_parties p
1820 WHERE c.status='A'
1821 AND c.party_id = p.party_id
1822 AND p.party_id= p_party_id;
1823
1824 ---cursor to select all eligible customers
1825 /*
1826 --Performance issue (SQL ID-16485806 FTS on HZ_CUST_ACCOUNTS and HZ_CUST_PROFILE_CLASSES)
1827 CURSOR cust_csr
1828 IS
1829 SELECT
1830 SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
1831 , c.cust_account_id customer_id
1832 , c.account_number customer_number
1833 FROM
1834 hz_cust_accounts c
1835 , hz_parties party
1836 WHERE c.status='A'
1837 AND c.party_id = party.party_id
1838 AND party.party_name BETWEEN NVL(p_customer_name_low, party.party_name )
1839 AND NVL(p_customer_name_high, party.party_name)
1840 AND c.account_number BETWEEN NVL(p_cust_number_low, c.account_number )
1841 AND NVL(p_cust_number_high, c.account_number )
1842 AND c.cust_account_id IN (SELECT cp.cust_account_id
1843 FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
1844 WHERE cp.profile_class_id=cpc.profile_class_id
1845 AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
1846 AND NVL(p_prof_class_high, cpc.name));
1847 */
1848
1849 ----cursor to select all credit profiles for the given customer
1850 CURSOR cust_prof_csr(p_customer_id NUMBER)
1851 IS
1852 SELECT
1853 cpa.currency_code cust_currency_code
1854 , cpa.overall_credit_limit cust_overall_limit
1855 FROM
1856 hz_customer_profiles cp
1857 , hz_cust_profile_amts cpa
1858 WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
1859 AND cp.site_use_id IS NULL
1860 AND cp.cust_account_id=p_customer_id;
1861
1862 ----cursor to select all bill-to sites for the given customer
1863 CURSOR site_csr(p_customer_id NUMBER)
1864 IS
1865 SELECT
1866 csu.location Customer_site
1867 , csu.site_use_id site_id
1868 , csu.cust_acct_site_id
1869 FROM
1870 hz_cust_site_uses_all csu
1871 , hz_cust_acct_sites_all casa
1872 WHERE csu.site_use_code='BILL_TO'
1873 AND csu.cust_acct_site_id=casa.cust_acct_site_id
1874 AND casa.cust_account_id=p_customer_id
1875 AND csu.org_id=p_org_id
1876 AND casa.org_id=p_org_id;
1877
1878
1879 ----cursor to select all credit profiles for the given bill-to site
1880 CURSOR site_prof_csr(p_customer_id NUMBER, p_site_id NUMBER)
1881 IS
1882 SELECT
1883 cpa.currency_code site_currency_code
1884 , cpa.overall_credit_limit site_overall_limit
1885 FROM
1886 hz_customer_profiles cp
1887 , hz_cust_profile_amts cpa
1888 WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
1889 AND cp.site_use_id =p_site_id
1890 AND cp.cust_account_id=p_customer_id;
1891
1892 l_msg_count NUMBER := 0 ;
1893 l_msg_data VARCHAR2(2000):= NULL ;
1894 l_cust_total_exposure NUMBER;
1895 l_party_total_exposure NUMBER;
1896 l_site_total_exposure NUMBER;
1897 l_return_status VARCHAR2(30);
1898 l_return_status1 VARCHAR2(30); ---for exchnage rate missing status
1899 l_base_currency VARCHAR2(15);
1900 l_cust_available NUMBER;
1901 l_party_available NUMBER;
1902 l_site_available NUMBER;
1903 l_base_cur_overall_limit NUMBER;
1904 l_base_cur_exposure NUMBER;
1905 l_base_cur_available NUMBER;
1906 l_cust_unchk_exposure NUMBER;
1907 l_party_unchk_exposure NUMBER;
1908 l_site_unchk_exposure NUMBER;
1909 l_conversion_type VARCHAR2(30);
1910 l_empty_curr_tbl OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
1911 l_global_exposure_flag VARCHAR2(1);
1912 l_prof_count NUMBER:=0;
1913 l_prof_count1 NUMBER:=0;
1914 l_order_hold_amount NUMBER;
1915 l_order_amount NUMBER;
1916 l_ar_amount NUMBER;
1917 l_external_amount NUMBER;
1918 l_specific_party_id NUMBER;
1919 l_profile_value VARCHAR2(100);
1920 l_count_hierarchy NUMBER;
1921
1922 BEGIN
1923
1924
1925 OE_DEBUG_PUB.ADD('IN OEXRCRCB:Credit_exposure_report_utils ');
1926 l_return_status := FND_API.G_RET_STS_SUCCESS;
1927 l_global_exposure_flag := 'N' ;
1928
1929 ----get base currency=currency of the current operating unit
1930 l_base_currency:=OE_CREDIT_CHECK_UTIL.Get_GL_currency;
1931
1932 OE_DEBUG_PUB.ADD('l_base_currency= '||l_base_currency);
1933
1934 ---get conversion type
1935 SELECT
1936 conversion_type
1937 INTO
1938 l_conversion_type
1939 FROM oe_credit_check_rules
1940 WHERE credit_check_rule_id=p_cr_check_rule_id;
1941
1942 ----get the hierarchy_type
1943 l_profile_value:=OE_CREDIT_CHECK_UTIL.G_hierarchy_type;
1944
1948
1945 OE_DEBUG_PUB.ADD('AR_CMGT_HIERARCHY_TYPE= '||l_profile_value);
1946
1947 --------Party Range Summary Report-----------
1949 IF p_report_by_option='PARTY_SUMMARY'
1950 THEN
1951
1952 OE_DEBUG_PUB.ADD('IN Party Summary Report');
1953
1954 ------start loop for parties
1955 IF (p_party_name_low IS NOT NULL) THEN
1956 l_input := 1;
1957 ELSIF (p_party_name_high IS NOT NULL) THEN
1958 l_input := 2;
1959 ELSIF (p_party_number_low IS NOT NULL) THEN
1960 l_input := 3;
1961 ELSIF (p_party_number_high IS NOT NULL) THEN
1962 l_input := 4;
1963 ELSE
1964 OE_DEBUG_PUB.ADD(' no party input');
1965 l_input := 0;
1966 END IF;
1967
1968 OE_DEBUG_PUB.ADD(' party input:'||l_input);
1969 IF (l_input > 0) THEN
1970 -- 5212830 FOR party_csr_rec IN party_csr(l_input)
1971
1972 LOOP
1973
1974 -- 5212830
1975
1976 IF (l_input = 1) THEN
1977
1978 IF NOT (party_csr1%ISOPEN) THEN
1979 OPEN party_csr1(l_input);
1980 END IF;
1981
1982 FETCH party_csr1 INTO party_csr_rec;
1983 EXIT WHEN party_csr1%NOTFOUND;
1984
1985 ELSIF (l_input = 2) THEN
1986
1987
1988 IF NOT (party_csr2%ISOPEN) THEN
1989 OPEN party_csr2(l_input);
1990 END IF;
1991
1992 FETCH party_csr2 INTO party_csr_rec;
1993 EXIT WHEN party_csr2%NOTFOUND ;
1994
1995
1996 ELSIF (l_input = 3) THEN
1997
1998 IF NOT (party_csr3%ISOPEN) THEN
1999 OPEN party_csr3(l_input);
2000 END IF;
2001
2002 FETCH party_csr3 INTO party_csr_rec;
2003 EXIT WHEN party_csr3%NOTFOUND ;
2004
2005 ELSIF (l_input = 4) THEN
2006
2007 IF NOT (party_csr4%ISOPEN) THEN
2008 OPEN party_csr4(l_input);
2009 END IF;
2010
2011 FETCH party_csr4 INTO party_csr_rec;
2012 EXIT WHEN party_csr4%NOTFOUND;
2013
2014 END IF;
2015
2016 -- 5212830
2017
2018 ----Empty global variables
2019 OE_Credit_Engine_GRP.G_cust_curr_tbl:=l_empty_curr_tbl;
2020 OE_Credit_Engine_GRP.G_cust_incl_all_flag:='N';
2021
2022 ----start loop for party credit profiles
2023
2024
2025
2026 FOR party_prof_csr_rec IN party_prof_csr(p_party_id=>party_csr_rec.party_id)
2027 LOOP
2028 ------calculate party credit exposure
2029 OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
2030 ( p_party_id => party_csr_rec.party_id
2031 , p_customer_id => NULL
2032 , p_site_id => NULL
2033 , p_limit_curr_code => party_prof_csr_rec.party_currency_code
2034 , p_credit_check_rule_id => p_cr_check_rule_id
2035 , p_need_exposure_details => 'N'
2036 , x_total_exposure => l_party_total_exposure
2037 , x_order_hold_amount => l_order_hold_amount
2038 , x_order_amount => l_order_amount
2039 , x_ar_amount => l_ar_amount
2040 , x_external_amount => l_external_amount
2041 , x_return_status => l_return_status
2042 );
2043
2044
2045 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2046 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2047
2048 IF l_return_status = 'C'
2049 THEN
2050 l_return_status1:='C';
2051
2052 EXIT;
2053
2054 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2055 THEN
2056 RAISE FND_API.G_EXC_ERROR;
2057 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2058 THEN
2059 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2060 END IF;
2061
2062 -----calculate available credit
2063 l_party_available:=party_prof_csr_rec.party_overall_limit - l_party_total_exposure;
2064
2065
2066 -----convert overall_credit_limit, exposure and available credit into base currency
2067 OE_DEBUG_PUB.ADD('IN Convert amounts ');
2068
2069 l_base_cur_overall_limit :=
2070 GL_CURRENCY_API.Convert_closest_amount_sql
2071 ( x_from_currency => party_prof_csr_rec.party_currency_code
2072 , x_to_currency => l_base_currency
2073 , x_conversion_date => sysdate
2074 , x_conversion_type => l_conversion_type
2075 , x_user_rate => NULL
2076 , x_amount => party_prof_csr_rec.party_overall_limit
2077 , x_max_roll_days => -1
2078 );
2079
2080 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2081 OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2082
2083 l_base_cur_exposure :=
2084 GL_CURRENCY_API.Convert_closest_amount_sql
2085 ( x_from_currency => party_prof_csr_rec.party_currency_code
2086 , x_to_currency => l_base_currency
2087 , x_conversion_date => sysdate
2088 , x_conversion_type => l_conversion_type
2089 , x_user_rate => NULL
2090 , x_amount => l_party_total_exposure
2091 , x_max_roll_days => -1
2092 );
2093
2094 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2098 GL_CURRENCY_API.Convert_closest_amount_sql
2095 OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2096
2097 l_base_cur_available :=
2099 ( x_from_currency => party_prof_csr_rec.party_currency_code
2100 , x_to_currency => l_base_currency
2101 , x_conversion_date => sysdate
2102 , x_conversion_type => l_conversion_type
2103 , x_user_rate => NULL
2104 , x_amount => l_party_available
2105 , x_max_roll_days => -1
2106 );
2107
2108 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2109 OE_DEBUG_PUB.ADD('l_base_cur_available = '
2110 || TO_CHAR(l_base_cur_available ));
2111
2112 OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2113
2114 OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2115
2116 l_global_exposure_flag := 'Y' ;
2117
2118
2119 OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2120
2121 -----insert data into temp table
2122 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2123 ( party_id
2124 , party_name
2125 , party_number
2126 , party_level
2127 , party_parent_id
2128 , report_by_option
2129 , customer_id
2130 , customer_name
2131 , customer_number
2132 , bill_to_site_id
2133 , bill_to_site_name
2134 , credit_limit_currency
2135 , cr_cur_overall_limit
2136 , cr_cur_exposure
2137 , cr_cur_available
2138 , base_currency
2139 , base_cur_overall_limit
2140 , base_cur_exposure
2141 , base_cur_available
2142 , unchecked_exposure
2143 , global_exposure_flag
2144 )
2145 VALUES
2146 ( party_csr_rec.party_id
2147 , party_csr_rec.Party
2148 , party_csr_rec.party_number
2149 , NULL
2150 , NULL
2151 , 'PARTY_SUMMARY'
2152 , NULL
2153 , NULL
2154 , NULL
2155 , NULL
2156 , NULL
2157 , party_prof_csr_rec.party_currency_code
2158 , party_prof_csr_rec.party_overall_limit
2159 , l_party_total_exposure
2160 , l_party_available
2161 , l_base_currency
2162 , l_base_cur_overall_limit
2163 , l_base_cur_exposure
2164 , l_base_cur_available
2165 , NULL
2166 , l_global_exposure_flag
2167 );
2168
2169 OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
2170
2171 ----end loop for party credit profiles
2172 END LOOP;
2173
2174 IF l_return_status = 'C'
2175 THEN
2176 l_return_status1:='C';
2177
2178 EXIT;
2179 END IF;
2180
2181 ----calculate unchecked exposure
2182 ----if global variable G_cust_incl_all_flag is 'Y'
2183 ----then unchecked exposure will be 0
2184 IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
2185 THEN
2186 OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2187 so l_party_unchk_exposure=0');
2188 l_party_unchk_exposure:=0;
2189 ELSE
2190 OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
2191 ||TO_CHAR(party_csr_rec.party_id));
2192 Get_unchecked_exposure
2193 ( p_party_id => party_csr_rec.party_id
2194 , p_customer_id => NULL
2195 , p_site_id => NULL
2196 , p_base_currency => l_base_currency
2197 , p_credit_check_rule_id => p_cr_check_rule_id
2198 , x_unchecked_expousre => l_party_unchk_exposure
2199 , x_return_status => l_return_status
2200 );
2201 END IF;
2202
2203 OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for party: '
2204 ||TO_CHAR(party_csr_rec.party_id));
2205 OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2206 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2207
2208 IF l_return_status = 'C'
2209 THEN
2210 l_return_status1:='C';
2211
2212 EXIT;
2213
2214 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2215 THEN
2216 RAISE FND_API.G_EXC_ERROR;
2217 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2218 THEN
2219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2220 END IF;
2221
2222 OE_DEBUG_PUB.ADD('Check if the party has any credit profiles ');
2223 -----check if the party has any credit profiles
2224 SELECT COUNT(*)
2225 INTO l_prof_count
2226 FROM
2227 hz_customer_profiles cp
2228 , hz_cust_profile_amts cpa
2229 WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
2230 AND cp.site_use_id IS NULL
2231 AND cp.cust_account_id=-1
2232 AND cp.party_id=party_csr_rec.party_id;
2233
2234 IF l_prof_count>0
2235 THEN
2236
2237 OE_DEBUG_PUB.ADD('Party has credit profiles ');
2238 OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2239
2240 ----update temp table with unchecked exposure
2244 AND customer_id IS NULL;
2241 UPDATE oe_credit_exposure_temp
2242 SET unchecked_exposure=l_party_unchk_exposure
2243 WHERE party_id=party_csr_rec.party_id
2245
2246 -----there are no credit profiles for this party
2247 ----insert unchecked exposure
2248 ELSE
2249
2250 OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
2251 OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2252
2253 -----insert data into temp table
2254 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2255 ( party_id
2256 , party_name
2257 , party_number
2258 , party_level
2259 , party_parent_id
2260 , report_by_option
2261 , customer_id
2262 , customer_name
2263 , customer_number
2264 , bill_to_site_id
2265 , bill_to_site_name
2266 , credit_limit_currency
2267 , cr_cur_overall_limit
2268 , cr_cur_exposure
2269 , cr_cur_available
2270 , base_currency
2271 , base_cur_overall_limit
2272 , base_cur_exposure
2273 , base_cur_available
2274 , unchecked_exposure
2275 , global_exposure_flag
2276 )
2277 VALUES
2278 ( party_csr_rec.party_id
2279 , party_csr_rec.Party
2280 , party_csr_rec.party_number
2281 , NULL
2282 , NULL
2283 , 'PARTY_SUMMARY'
2284 , NULL
2285 , NULL
2286 , NULL
2287 , NULL
2288 , NULL
2289 , NULL
2290 , NULL
2291 , NULL
2292 , NULL
2293 , l_base_currency
2294 , NULL
2295 , NULL
2296 , NULL
2297 , l_party_unchk_exposure
2298 , NULL
2299 );
2300
2301 OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
2302
2303 END IF;
2304
2305 END LOOP; ----end loop for parties
2306
2307 -- 5212830
2308
2309 IF (party_csr1%ISOPEN) THEN
2310 CLOSE party_csr1;
2311 END IF;
2312
2313 IF (party_csr2%ISOPEN) THEN
2314 CLOSE party_csr2;
2315 END IF;
2316
2317 IF (party_csr3%ISOPEN) THEN
2318 CLOSE party_csr3;
2319 END IF;
2320
2321 IF (party_csr4%ISOPEN) THEN
2322 CLOSE party_csr4;
2323 END IF;
2324
2325 -- 5212830
2326
2327
2328 END IF; -- l_type
2329
2330 --------Party Details Report-----------
2331
2332 ELSIF p_report_by_option='PARTY_DETAILS'
2333 THEN
2334 -----get party_id---
2335 IF p_specific_party_id IS NOT NULL
2336 THEN
2337 l_specific_party_id:= p_specific_party_id;
2338 ELSE
2339 l_specific_party_id:= p_spec_party_num_id;
2340 END IF;
2341
2342 OE_DEBUG_PUB.ADD('IN Party Detail Report for party_id='||TO_CHAR(l_specific_party_id));
2343
2344 ---Check if this party is part of Hierarchy AR_CMGT_HIERARCHY_TYPE
2345 SELECT COUNT(*)
2346 INTO l_count_hierarchy
2347 FROM hz_hierarchy_nodes
2348 WHERE hierarchy_type=l_profile_value
2349 AND (parent_id = l_specific_party_id
2350 OR child_id=l_specific_party_id);
2351
2352 IF l_count_hierarchy>0
2353 THEN
2354 OE_DEBUG_PUB.ADD('This party is part of the hierarchy '||l_profile_value);
2355 ------use cursor for hierarchical parties
2356
2357 ------start loop for parties
2358 FOR party_hier_csr_rec IN party_hier_csr
2359 LOOP
2360
2361 ----Empty global variables
2362 OE_Credit_Engine_GRP.G_cust_curr_tbl:=l_empty_curr_tbl;
2363 OE_Credit_Engine_GRP.G_cust_incl_all_flag:='N';
2364
2365 ----start loop for party credit profiles
2366 FOR party_prof_csr_rec IN party_prof_csr(p_party_id=>party_hier_csr_rec.party_id)
2367 LOOP
2368 ------calculate party credit exposure
2369 OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
2370 ( p_party_id => party_hier_csr_rec.party_id
2371 , p_customer_id => NULL
2372 , p_site_id => NULL
2373 , p_limit_curr_code => party_prof_csr_rec.party_currency_code
2374 , p_credit_check_rule_id => p_cr_check_rule_id
2375 , p_need_exposure_details => 'N'
2376 , x_total_exposure => l_party_total_exposure
2377 , x_order_hold_amount => l_order_hold_amount
2378 , x_order_amount => l_order_amount
2379 , x_ar_amount => l_ar_amount
2380 , x_external_amount => l_external_amount
2381 , x_return_status => l_return_status
2382 );
2383
2384
2385 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2386 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2387
2388 IF l_return_status = 'C'
2389 THEN
2390 l_return_status1:='C';
2391
2392 EXIT;
2393
2394 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2395 THEN
2396 RAISE FND_API.G_EXC_ERROR;
2397 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2398 THEN
2402 -----calculate available credit
2399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2400 END IF;
2401
2403 l_party_available:=party_prof_csr_rec.party_overall_limit - l_party_total_exposure;
2404
2405
2406 -----convert overall_credit_limit, exposure and available credit into base currency
2407 OE_DEBUG_PUB.ADD('IN Convert amounts ');
2408
2409 l_base_cur_overall_limit :=
2410 GL_CURRENCY_API.Convert_closest_amount_sql
2411 ( x_from_currency => party_prof_csr_rec.party_currency_code
2412 , x_to_currency => l_base_currency
2413 , x_conversion_date => sysdate
2414 , x_conversion_type => l_conversion_type
2415 , x_user_rate => NULL
2416 , x_amount => party_prof_csr_rec.party_overall_limit
2417 , x_max_roll_days => -1
2418 );
2419
2420 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2421 OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2422
2423 l_base_cur_exposure :=
2424 GL_CURRENCY_API.Convert_closest_amount_sql
2425 ( x_from_currency => party_prof_csr_rec.party_currency_code
2426 , x_to_currency => l_base_currency
2427 , x_conversion_date => sysdate
2428 , x_conversion_type => l_conversion_type
2429 , x_user_rate => NULL
2430 , x_amount => l_party_total_exposure
2431 , x_max_roll_days => -1
2432 );
2433
2434 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2435 OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2436
2437 l_base_cur_available :=
2438 GL_CURRENCY_API.Convert_closest_amount_sql
2439 ( x_from_currency => party_prof_csr_rec.party_currency_code
2440 , x_to_currency => l_base_currency
2441 , x_conversion_date => sysdate
2442 , x_conversion_type => l_conversion_type
2443 , x_user_rate => NULL
2444 , x_amount => l_party_available
2445 , x_max_roll_days => -1
2446 );
2447
2448 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2449 OE_DEBUG_PUB.ADD('l_base_cur_available = '
2450 || TO_CHAR(l_base_cur_available ));
2451
2452 OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2453
2454 OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2455
2456 l_global_exposure_flag := 'Y' ;
2457
2458 OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2459
2460 -----insert data into temp table
2461 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2462 ( party_id
2463 , party_name
2464 , party_number
2465 , party_level
2466 , party_parent_id
2467 , report_by_option
2468 , customer_id
2469 , customer_name
2470 , customer_number
2471 , bill_to_site_id
2472 , bill_to_site_name
2473 , credit_limit_currency
2474 , cr_cur_overall_limit
2475 , cr_cur_exposure
2476 , cr_cur_available
2477 , base_currency
2478 , base_cur_overall_limit
2479 , base_cur_exposure
2480 , base_cur_available
2481 , unchecked_exposure
2482 , global_exposure_flag
2483 )
2484 VALUES
2485 ( party_hier_csr_rec.party_id
2486 , party_hier_csr_rec.Party
2487 , party_hier_csr_rec.party_number
2488 , party_hier_csr_rec.level_number
2489 , l_specific_party_id
2490 , 'PARTY_DETAILS'
2491 , NULL
2492 , NULL
2493 , NULL
2494 , NULL
2495 , NULL
2496 , party_prof_csr_rec.party_currency_code
2497 , party_prof_csr_rec.party_overall_limit
2498 , l_party_total_exposure
2499 , l_party_available
2500 , l_base_currency
2501 , l_base_cur_overall_limit
2502 , l_base_cur_exposure
2503 , l_base_cur_available
2504 , NULL
2505 , l_global_exposure_flag
2506 );
2507
2508 OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
2509
2510 ----end loop for party credit profiles
2511 END LOOP;
2512
2513 IF l_return_status = 'C'
2514 THEN
2515 l_return_status1:='C';
2516
2517 EXIT;
2518 END IF;
2519
2520 ----calculate unchecked exposure
2521 ----if global variable G_cust_incl_all_flag is 'Y'
2522 ----then unchecked exposure will be 0
2523 IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
2524 THEN
2525 OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2526 so l_party_unchk_exposure=0');
2527 l_party_unchk_exposure:=0;
2528 ELSE
2529 OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
2530 ||TO_CHAR(party_hier_csr_rec.party_id));
2534 , p_site_id => NULL
2531 Get_unchecked_exposure
2532 ( p_party_id => party_hier_csr_rec.party_id
2533 , p_customer_id => NULL
2535 , p_base_currency => l_base_currency
2536 , p_credit_check_rule_id => p_cr_check_rule_id
2537 , x_unchecked_expousre => l_party_unchk_exposure
2538 , x_return_status => l_return_status
2539 );
2540 END IF;
2541
2542 OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure, for the party:'
2543 ||TO_CHAR(party_hier_csr_rec.party_id));
2544 OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2545 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2546
2547 IF l_return_status = 'C'
2548 THEN
2549 l_return_status1:='C';
2550
2551 EXIT;
2552
2553 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2554 THEN
2555 RAISE FND_API.G_EXC_ERROR;
2556 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2557 THEN
2558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2559 END IF;
2560
2561 OE_DEBUG_PUB.ADD('Check if the party has any credit profiles ');
2562 -----check if the party has any credit profiles
2563 SELECT COUNT(*)
2564 INTO l_prof_count
2565 FROM
2566 hz_customer_profiles cp
2567 , hz_cust_profile_amts cpa
2568 WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
2569 AND cp.site_use_id IS NULL
2570 AND cp.cust_account_id=-1
2571 AND cp.party_id=party_hier_csr_rec.party_id;
2572
2573 IF l_prof_count>0
2574 THEN
2575
2576 OE_DEBUG_PUB.ADD('Party has credit profiles ');
2577 OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2578
2579 ----update temp table with unchecked exposure
2580 UPDATE oe_credit_exposure_temp
2581 SET unchecked_exposure=l_party_unchk_exposure
2582 WHERE party_id=party_hier_csr_rec.party_id
2583 AND customer_id IS NULL;
2584
2585 -----there are no credit profiles for this party
2586 ----insert unchecked exposure
2587 ELSE
2588
2589 OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
2590 OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2591
2592 -----insert data into temp table
2593 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2594 ( party_id
2595 , party_name
2596 , party_number
2597 , party_level
2598 , party_parent_id
2599 , report_by_option
2600 , customer_id
2601 , customer_name
2602 , customer_number
2603 , bill_to_site_id
2604 , bill_to_site_name
2605 , credit_limit_currency
2606 , cr_cur_overall_limit
2607 , cr_cur_exposure
2608 , cr_cur_available
2609 , base_currency
2610 , base_cur_overall_limit
2611 , base_cur_exposure
2612 , base_cur_available
2613 , unchecked_exposure
2614 , global_exposure_flag
2615 )
2616 VALUES
2617 ( party_hier_csr_rec.party_id
2618 , party_hier_csr_rec.Party
2619 , party_hier_csr_rec.party_number
2620 , party_hier_csr_rec.level_number
2621 , l_specific_party_id
2622 , 'PARTY_DETAILS'
2623 , NULL
2624 , NULL
2625 , NULL
2626 , NULL
2627 , NULL
2628 , NULL
2629 , NULL
2630 , NULL
2631 , NULL
2632 , l_base_currency
2633 , NULL
2634 , NULL
2635 , NULL
2636 , l_party_unchk_exposure
2637 , 'Y'
2638 );
2639
2640 OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
2641
2642 END IF; ---end of checking if the party has credit profiles
2643
2644 ------start report for cust accounts for the specific party
2645
2646 OE_DEBUG_PUB.ADD('IN Cust accounts section of the Party Deatils Report');
2647
2648 ------start loop for customers
2649 FOR party_cust_csr_rec IN party_cust_csr (p_party_id => party_hier_csr_rec.party_id)
2650 LOOP
2651
2652 ----Empty global variables
2653 OE_Credit_Engine_GRP.G_cust_curr_tbl:=l_empty_curr_tbl;
2654 OE_Credit_Engine_GRP.G_cust_incl_all_flag:='N';
2655
2656 ----start loop for customer credit profiles
2657 FOR cust_prof_csr_rec IN cust_prof_csr(p_customer_id=>party_cust_csr_rec.customer_id)
2658 LOOP
2659 ------calculate customer credit exposure
2660 OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
2661 ( p_customer_id => party_cust_csr_rec.customer_id
2662 , p_site_id => NULL
2663 , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
2664 , p_credit_check_rule_id => p_cr_check_rule_id
2665 , x_total_exposure => l_cust_total_exposure
2666 , x_return_status => l_return_status
2667 );
2668
2669
2673 IF l_return_status = 'C'
2670 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2671 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2672
2674 THEN
2675 l_return_status1:='C';
2676
2677 EXIT;
2678
2679 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2680 THEN
2681 RAISE FND_API.G_EXC_ERROR;
2682 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2683 THEN
2684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2685 END IF;
2686
2687 -----calculate available credit
2688 l_cust_available:=cust_prof_csr_rec.cust_overall_limit - l_cust_total_exposure;
2689
2690
2691 -----convert overall_credit_limit, exposure and available credit into base currency
2692 OE_DEBUG_PUB.ADD('IN Convert amounts ');
2693
2694 l_base_cur_overall_limit :=
2695 GL_CURRENCY_API.Convert_closest_amount_sql
2696 ( x_from_currency => cust_prof_csr_rec.cust_currency_code
2697 , x_to_currency => l_base_currency
2698 , x_conversion_date => sysdate
2699 , x_conversion_type => l_conversion_type
2700 , x_user_rate => NULL
2701 , x_amount => cust_prof_csr_rec.cust_overall_limit
2702 , x_max_roll_days => -1
2703 );
2704
2705 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2706 OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2707
2708 l_base_cur_exposure :=
2709 GL_CURRENCY_API.Convert_closest_amount_sql
2710 ( x_from_currency => cust_prof_csr_rec.cust_currency_code
2711 , x_to_currency => l_base_currency
2712 , x_conversion_date => sysdate
2713 , x_conversion_type => l_conversion_type
2714 , x_user_rate => NULL
2715 , x_amount => l_cust_total_exposure
2716 , x_max_roll_days => -1
2717 );
2718
2719 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2720 OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2721
2722 l_base_cur_available :=
2723 GL_CURRENCY_API.Convert_closest_amount_sql
2724 ( x_from_currency => cust_prof_csr_rec.cust_currency_code
2725 , x_to_currency => l_base_currency
2726 , x_conversion_date => sysdate
2727 , x_conversion_type => l_conversion_type
2728 , x_user_rate => NULL
2729 , x_amount => l_cust_available
2730 , x_max_roll_days => -1
2731 );
2732
2733 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2734 OE_DEBUG_PUB.ADD('l_base_cur_available = '
2735 || TO_CHAR(l_base_cur_available ));
2736
2737 OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2738
2739 OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
2740
2741 l_global_exposure_flag :=
2742 OE_CREDIT_CHECK_UTIL.Get_global_exposure_flag
2743 ( p_entity_type => 'CUSTOMER'
2744 , p_entity_id => party_cust_csr_rec.customer_id
2745 , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
2746 ) ;
2747
2748 OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
2749 l_global_exposure_flag );
2750
2751 OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2752
2753 -----insert data into temp table
2754 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2755 ( party_id
2756 , party_name
2757 , party_number
2758 , party_level
2759 , party_parent_id
2760 , report_by_option
2761 , customer_id
2762 , customer_name
2763 , customer_number
2764 , bill_to_site_id
2765 , bill_to_site_name
2766 , credit_limit_currency
2767 , cr_cur_overall_limit
2768 , cr_cur_exposure
2769 , cr_cur_available
2770 , base_currency
2771 , base_cur_overall_limit
2772 , base_cur_exposure
2773 , base_cur_available
2774 , unchecked_exposure
2775 , global_exposure_flag
2776 )
2777 VALUES
2778 ( party_hier_csr_rec.party_id
2779 , party_hier_csr_rec.Party
2780 , party_hier_csr_rec.party_number
2781 , party_hier_csr_rec.level_number
2782 , l_specific_party_id
2783 , 'PARTY_DETAILS'
2784 , party_cust_csr_rec.customer_id
2785 , party_cust_csr_rec.Customer
2786 , party_cust_csr_rec.customer_number
2787 , NULL
2788 , NULL
2789 , cust_prof_csr_rec.cust_currency_code
2790 , cust_prof_csr_rec.cust_overall_limit
2791 , l_cust_total_exposure
2792 , l_cust_available
2793 , l_base_currency
2794 , l_base_cur_overall_limit
2795 , l_base_cur_exposure
2796 , l_base_cur_available
2797 , NULL
2798 , l_global_exposure_flag
2799 );
2800
2804 END LOOP;
2801 OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
2802
2803 ----end loop for customer credit profiles
2805
2806 IF l_return_status = 'C'
2807 THEN
2808 l_return_status1:='C';
2809
2810 EXIT;
2811 END IF;
2812
2813
2814 ----calculate unchecked exposure
2815 ----if global variable G_cust_incl_all_flag is 'Y'
2816 ----then unchecked exposure will be 0
2817
2818 IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
2819 THEN
2820 OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2821 so l_cust_unchk_exposure=0');
2822 l_cust_unchk_exposure:=0;
2823 ELSE
2824
2825 OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the customer '
2826 ||TO_CHAR(party_cust_csr_rec.customer_id));
2827
2828 Get_unchecked_exposure
2829 ( p_party_id => NULL
2830 , p_customer_id => party_cust_csr_rec.customer_id
2831 , p_site_id => NULL
2832 , p_base_currency => l_base_currency
2833 , p_credit_check_rule_id => p_cr_check_rule_id
2834 , x_unchecked_expousre => l_cust_unchk_exposure
2835 , x_return_status => l_return_status
2836 );
2837 END IF;
2838
2839 OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer: '
2840 ||TO_CHAR(party_cust_csr_rec.customer_id));
2841 OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure) );
2842 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2843
2844 IF l_return_status = 'C'
2845 THEN
2846 l_return_status1:='C';
2847
2848 EXIT;
2849
2850 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2851 THEN
2852 RAISE FND_API.G_EXC_ERROR;
2853 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2854 THEN
2855 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2856 END IF;
2857
2858 OE_DEBUG_PUB.ADD('Check if the customer has any credit profiles ');
2859 -----check if the customer has any credit profiles
2860 SELECT COUNT(*)
2861 INTO l_prof_count
2862 FROM
2863 hz_customer_profiles cp
2864 , hz_cust_profile_amts cpa
2865 WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
2866 AND cp.site_use_id IS NULL
2867 AND cp.cust_account_id=party_cust_csr_rec.customer_id;
2868
2869 IF l_prof_count>0
2870 THEN
2871
2872 OE_DEBUG_PUB.ADD('Customer has credit profiles ');
2873 OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2874
2875 ----update temp table with unchecked exposure
2876 UPDATE oe_credit_exposure_temp
2877 SET unchecked_exposure=l_cust_unchk_exposure
2878 WHERE customer_id=party_cust_csr_rec.customer_id
2879 AND party_id=party_hier_csr_rec.party_id
2880 AND bill_to_site_id IS NULL
2881 AND report_by_option='PARTY_DETAILS';
2882
2883 -----there are no credit profiles for this customer
2884 ----insert unchecked exposure
2885 ELSE
2886
2887 OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
2888 OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2889
2890 -----insert data into temp table
2891 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2892 ( party_id
2893 , party_name
2894 , party_number
2895 , party_level
2896 , party_parent_id
2897 , report_by_option
2898 , customer_id
2899 , customer_name
2900 , customer_number
2901 , bill_to_site_id
2902 , bill_to_site_name
2903 , credit_limit_currency
2904 , cr_cur_overall_limit
2905 , cr_cur_exposure
2906 , cr_cur_available
2907 , base_currency
2908 , base_cur_overall_limit
2909 , base_cur_exposure
2910 , base_cur_available
2911 , unchecked_exposure
2912 , global_exposure_flag
2913 )
2914 VALUES
2915 ( party_hier_csr_rec.party_id
2916 , party_hier_csr_rec.Party
2917 , party_hier_csr_rec.party_number
2918 , party_hier_csr_rec.level_number
2919 , l_specific_party_id
2920 , 'PARTY_DETAILS'
2921 , party_cust_csr_rec.customer_id
2922 , party_cust_csr_rec.Customer
2923 , party_cust_csr_rec.customer_number
2924 , NULL
2925 , NULL
2926 , NULL
2927 , NULL
2928 , NULL
2929 , NULL
2930 , l_base_currency
2931 , NULL
2932 , NULL
2933 , NULL
2934 , l_cust_unchk_exposure
2935 , 'Y'
2936 );
2937
2938 OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
2939
2940 END IF; --end of checking if the customer has credit profile
2941
2942 ----end loop for customers
2943 END LOOP;
2944
2945 END LOOP; ----end loop for parties
2946
2950
2947
2948
2949
2951 -----party is not part of the hierarchy
2952 ELSE
2953 OE_DEBUG_PUB.ADD('This party is not part of the hierarchy '||l_profile_value);
2954 ------use cursor for non hierarchical parties
2955
2956 ------start loop for parties
2957 FOR party_hier_csr1_rec IN party_hier_csr1
2958 LOOP
2959
2960 ----Empty global variables
2961 OE_Credit_Engine_GRP.G_cust_curr_tbl:=l_empty_curr_tbl;
2962 OE_Credit_Engine_GRP.G_cust_incl_all_flag:='N';
2963
2964 ----start loop for party credit profiles
2965 FOR party_prof_csr_rec IN party_prof_csr(p_party_id=>party_hier_csr1_rec.party_id)
2966 LOOP
2967 ------calculate party credit exposure
2968 OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
2969 ( p_party_id => party_hier_csr1_rec.party_id
2970 , p_customer_id => NULL
2971 , p_site_id => NULL
2972 , p_limit_curr_code => party_prof_csr_rec.party_currency_code
2973 , p_credit_check_rule_id => p_cr_check_rule_id
2974 , p_need_exposure_details => 'N'
2975 , x_total_exposure => l_party_total_exposure
2976 , x_order_hold_amount => l_order_hold_amount
2977 , x_order_amount => l_order_amount
2978 , x_ar_amount => l_ar_amount
2979 , x_external_amount => l_external_amount
2980 , x_return_status => l_return_status
2981 );
2982
2983
2984 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2985 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2986
2987 IF l_return_status = 'C'
2988 THEN
2989 l_return_status1:='C';
2990
2991 EXIT;
2992
2993 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
2994 THEN
2995 RAISE FND_API.G_EXC_ERROR;
2996 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
2997 THEN
2998 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2999 END IF;
3000
3001 -----calculate available credit
3002 l_party_available:=party_prof_csr_rec.party_overall_limit - l_party_total_exposure;
3003
3004
3005 -----convert overall_credit_limit, exposure and available credit into base currency
3006 OE_DEBUG_PUB.ADD('IN Convert amounts ');
3007
3008 l_base_cur_overall_limit :=
3009 GL_CURRENCY_API.Convert_closest_amount_sql
3010 ( x_from_currency => party_prof_csr_rec.party_currency_code
3011 , x_to_currency => l_base_currency
3012 , x_conversion_date => sysdate
3013 , x_conversion_type => l_conversion_type
3014 , x_user_rate => NULL
3015 , x_amount => party_prof_csr_rec.party_overall_limit
3016 , x_max_roll_days => -1
3017 );
3018
3019 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3020 OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3021
3022 l_base_cur_exposure :=
3023 GL_CURRENCY_API.Convert_closest_amount_sql
3024 ( x_from_currency => party_prof_csr_rec.party_currency_code
3025 , x_to_currency => l_base_currency
3026 , x_conversion_date => sysdate
3027 , x_conversion_type => l_conversion_type
3028 , x_user_rate => NULL
3029 , x_amount => l_party_total_exposure
3030 , x_max_roll_days => -1
3031 );
3032
3033 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3034 OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3035
3036 l_base_cur_available :=
3037 GL_CURRENCY_API.Convert_closest_amount_sql
3038 ( x_from_currency => party_prof_csr_rec.party_currency_code
3039 , x_to_currency => l_base_currency
3040 , x_conversion_date => sysdate
3041 , x_conversion_type => l_conversion_type
3042 , x_user_rate => NULL
3043 , x_amount => l_party_available
3044 , x_max_roll_days => -1
3045 );
3046
3047 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3048 OE_DEBUG_PUB.ADD('l_base_cur_available = '
3049 || TO_CHAR(l_base_cur_available ));
3050
3051 OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3052
3053 OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
3054
3055 l_global_exposure_flag := 'Y' ;
3056
3057 OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3058
3059 -----insert data into temp table
3060 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3061 ( party_id
3062 , party_name
3063 , party_number
3064 , party_level
3065 , party_parent_id
3066 , report_by_option
3067 , customer_id
3068 , customer_name
3069 , customer_number
3070 , bill_to_site_id
3071 , bill_to_site_name
3072 , credit_limit_currency
3073 , cr_cur_overall_limit
3074 , cr_cur_exposure
3075 , cr_cur_available
3079 , base_cur_available
3076 , base_currency
3077 , base_cur_overall_limit
3078 , base_cur_exposure
3080 , unchecked_exposure
3081 , global_exposure_flag
3082 )
3083 VALUES
3084 ( party_hier_csr1_rec.party_id
3085 , party_hier_csr1_rec.Party
3086 , party_hier_csr1_rec.party_number
3087 , 0
3088 , l_specific_party_id
3089 , 'PARTY_DETAILS'
3090 , NULL
3091 , NULL
3092 , NULL
3093 , NULL
3094 , NULL
3095 , party_prof_csr_rec.party_currency_code
3096 , party_prof_csr_rec.party_overall_limit
3097 , l_party_total_exposure
3098 , l_party_available
3099 , l_base_currency
3100 , l_base_cur_overall_limit
3101 , l_base_cur_exposure
3102 , l_base_cur_available
3103 , NULL
3104 , l_global_exposure_flag
3105 );
3106
3107 OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
3108
3109 ----end loop for party credit profiles
3110 END LOOP;
3111
3112 IF l_return_status = 'C'
3113 THEN
3114 l_return_status1:='C';
3115
3116 EXIT;
3117 END IF;
3118
3119 ----calculate unchecked exposure
3120 ----if global variable G_cust_incl_all_flag is 'Y'
3121 ----then unchecked exposure will be 0
3122 IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
3123 THEN
3124 OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
3125 so l_party_unchk_exposure=0');
3126
3127 l_party_unchk_exposure:=0;
3128 ELSE
3129 OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
3130 ||TO_CHAR(party_hier_csr1_rec.party_id));
3131
3132 Get_unchecked_exposure
3133 ( p_party_id => party_hier_csr1_rec.party_id
3134 , p_customer_id => NULL
3135 , p_site_id => NULL
3136 , p_base_currency => l_base_currency
3137 , p_credit_check_rule_id => p_cr_check_rule_id
3138 , x_unchecked_expousre => l_party_unchk_exposure
3139 , x_return_status => l_return_status
3140 );
3141 END IF;
3142
3143 OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for the party: '
3144 ||TO_CHAR(party_hier_csr1_rec.party_id));
3145 OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure) );
3146 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3147
3148 IF l_return_status = 'C'
3149 THEN
3150 l_return_status1:='C';
3151
3152 EXIT;
3153
3154 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3155 THEN
3156 RAISE FND_API.G_EXC_ERROR;
3157 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3158 THEN
3159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3160 END IF;
3161
3162 OE_DEBUG_PUB.ADD('Check if the party has any credit profiles ');
3163 -----check if the party has any credit profiles
3164 SELECT COUNT(*)
3165 INTO l_prof_count
3166 FROM
3167 hz_customer_profiles cp
3168 , hz_cust_profile_amts cpa
3169 WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
3170 AND cp.site_use_id IS NULL
3171 AND cp.cust_account_id=-1
3172 AND cp.party_id=party_hier_csr1_rec.party_id;
3173
3174 IF l_prof_count>0
3175 THEN
3176
3177 OE_DEBUG_PUB.ADD('Party has credit profiles ');
3178 OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3179
3180 ----update temp table with unchecked exposure
3181 UPDATE oe_credit_exposure_temp
3182 SET unchecked_exposure=l_party_unchk_exposure
3183 WHERE party_id=party_hier_csr1_rec.party_id
3184 AND customer_id IS NULL;
3185
3186 -----there are no credit profiles for this party
3187 ----insert unchecked exposure
3188 ELSE
3189
3190 OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
3191 OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3192
3193 -----insert data into temp table
3194 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3195 ( party_id
3196 , party_name
3197 , party_number
3198 , party_level
3199 , party_parent_id
3200 , report_by_option
3201 , customer_id
3202 , customer_name
3203 , customer_number
3204 , bill_to_site_id
3205 , bill_to_site_name
3206 , credit_limit_currency
3207 , cr_cur_overall_limit
3208 , cr_cur_exposure
3209 , cr_cur_available
3210 , base_currency
3211 , base_cur_overall_limit
3212 , base_cur_exposure
3213 , base_cur_available
3214 , unchecked_exposure
3215 , global_exposure_flag
3216 )
3217 VALUES
3218 ( party_hier_csr1_rec.party_id
3219 , party_hier_csr1_rec.Party
3220 , party_hier_csr1_rec.party_number
3221 , 0
3222 , l_specific_party_id
3226 , NULL
3223 , 'PARTY_DETAILS'
3224 , NULL
3225 , NULL
3227 , NULL
3228 , NULL
3229 , NULL
3230 , NULL
3231 , NULL
3232 , NULL
3233 , l_base_currency
3234 , NULL
3235 , NULL
3236 , NULL
3237 , l_party_unchk_exposure
3238 , 'Y'
3239 );
3240
3241 OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
3242
3243 END IF; ---end of checking if the party has credit profiles
3244
3245 ------start report for cust accounts for the specific party
3246
3247 OE_DEBUG_PUB.ADD('IN Cust accounts section of the Party Details Report');
3248 OE_DEBUG_PUB.ADD('party_hier_csr1_rec.party_id ==> '||
3249 party_hier_csr1_rec.party_id );
3250
3251 ------start loop for customers
3252 FOR party_cust_csr_rec IN party_cust_csr (p_party_id => party_hier_csr1_rec.party_id)
3253 LOOP
3254
3255 ----Empty global variables
3256 OE_Credit_Engine_GRP.G_cust_curr_tbl:=l_empty_curr_tbl;
3257 OE_Credit_Engine_GRP.G_cust_incl_all_flag:='N';
3258
3259 ----start loop for customer credit profiles
3260 FOR cust_prof_csr_rec IN cust_prof_csr(p_customer_id=>party_cust_csr_rec.customer_id)
3261 LOOP
3262 ------calculate customer credit exposure
3263 OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
3264 ( p_customer_id => party_cust_csr_rec.customer_id
3265 , p_site_id => NULL
3266 , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
3267 , p_credit_check_rule_id => p_cr_check_rule_id
3268 , x_total_exposure => l_cust_total_exposure
3269 , x_return_status => l_return_status
3270 );
3271
3272
3273 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
3274 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3275
3276 IF l_return_status = 'C'
3277 THEN
3278 l_return_status1:='C';
3279
3280 EXIT;
3281
3282 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3283 THEN
3284 RAISE FND_API.G_EXC_ERROR;
3285 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3286 THEN
3287 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3288 END IF;
3289
3290 -----calculate available credit
3291 l_cust_available:=cust_prof_csr_rec.cust_overall_limit - l_cust_total_exposure;
3292
3293
3294 -----convert overall_credit_limit, exposure and available credit into base currency
3295 OE_DEBUG_PUB.ADD('IN Convert amounts ');
3296
3297 l_base_cur_overall_limit :=
3298 GL_CURRENCY_API.Convert_closest_amount_sql
3299 ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3300 , x_to_currency => l_base_currency
3301 , x_conversion_date => sysdate
3302 , x_conversion_type => l_conversion_type
3303 , x_user_rate => NULL
3304 , x_amount => cust_prof_csr_rec.cust_overall_limit
3305 , x_max_roll_days => -1
3306 );
3307
3308 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3309 OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3310
3311 l_base_cur_exposure :=
3312 GL_CURRENCY_API.Convert_closest_amount_sql
3313 ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3314 , x_to_currency => l_base_currency
3315 , x_conversion_date => sysdate
3316 , x_conversion_type => l_conversion_type
3317 , x_user_rate => NULL
3318 , x_amount => l_cust_total_exposure
3319 , x_max_roll_days => -1
3320 );
3321
3322 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3323 OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3324
3325 l_base_cur_available :=
3326 GL_CURRENCY_API.Convert_closest_amount_sql
3327 ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3328 , x_to_currency => l_base_currency
3329 , x_conversion_date => sysdate
3330 , x_conversion_type => l_conversion_type
3331 , x_user_rate => NULL
3332 , x_amount => l_cust_available
3333 , x_max_roll_days => -1
3334 );
3335
3336 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3337 OE_DEBUG_PUB.ADD('l_base_cur_available = '
3338 || TO_CHAR(l_base_cur_available ));
3339
3340 OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3341
3342 OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
3343
3344 l_global_exposure_flag :=
3345 OE_CREDIT_CHECK_UTIL.Get_global_exposure_flag
3346 ( p_entity_type => 'CUSTOMER'
3347 , p_entity_id => party_cust_csr_rec.customer_id
3348 , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
3349 ) ;
3350
3351 OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
3352 l_global_exposure_flag );
3353
3357 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3354 OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3355
3356 -----insert data into temp table
3358 ( party_id
3359 , party_name
3360 , party_number
3361 , party_level
3362 , party_parent_id
3363 , report_by_option
3364 , customer_id
3365 , customer_name
3366 , customer_number
3367 , bill_to_site_id
3368 , bill_to_site_name
3369 , credit_limit_currency
3370 , cr_cur_overall_limit
3371 , cr_cur_exposure
3372 , cr_cur_available
3373 , base_currency
3374 , base_cur_overall_limit
3375 , base_cur_exposure
3376 , base_cur_available
3377 , unchecked_exposure
3378 , global_exposure_flag
3379 )
3380 VALUES
3381 ( party_hier_csr1_rec.party_id
3382 , party_hier_csr1_rec.Party
3383 , party_hier_csr1_rec.party_number
3384 , 0
3385 , l_specific_party_id
3386 , 'PARTY_DETAILS'
3387 , party_cust_csr_rec.customer_id
3388 , party_cust_csr_rec.Customer
3389 , party_cust_csr_rec.customer_number
3390 , NULL
3391 , NULL
3392 , cust_prof_csr_rec.cust_currency_code
3393 , cust_prof_csr_rec.cust_overall_limit
3394 , l_cust_total_exposure
3395 , l_cust_available
3396 , l_base_currency
3397 , l_base_cur_overall_limit
3398 , l_base_cur_exposure
3399 , l_base_cur_available
3400 , NULL
3401 , 'Y'
3402 );
3403
3404 OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
3405
3406 ----end loop for customer credit profiles
3407 END LOOP;
3408
3409 IF l_return_status = 'C'
3410 THEN
3411 l_return_status1:='C';
3412
3413 EXIT;
3414 END IF;
3415
3416 ----calculate unchecked exposure
3417 ----if global variable G_cust_incl_all_flag is 'Y'
3418 ----then unchecked exposure will be 0
3419 IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
3420 THEN
3421 OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
3422 so l_cust_unchk_exposure=0');
3423
3424 l_cust_unchk_exposure:=0;
3425 ELSE
3426 OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the customer='
3427 ||TO_CHAR(party_cust_csr_rec.customer_id));
3428
3429 Get_unchecked_exposure
3430 ( p_party_id => NULL
3431 , p_customer_id => party_cust_csr_rec.customer_id
3432 , p_site_id => NULL
3433 , p_base_currency => l_base_currency
3434 , p_credit_check_rule_id => p_cr_check_rule_id
3435 , x_unchecked_expousre => l_cust_unchk_exposure
3436 , x_return_status => l_return_status
3437 );
3438 END IF;
3439
3440 OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer='
3441 ||TO_CHAR(party_cust_csr_rec.customer_id) );
3442 OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3443 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3444
3445 IF l_return_status = 'C'
3446 THEN
3447 l_return_status1:='C';
3448
3449 EXIT;
3450
3451 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3452 THEN
3453 RAISE FND_API.G_EXC_ERROR;
3454 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3455 THEN
3456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3457 END IF;
3458
3459 OE_DEBUG_PUB.ADD('Check if the customer has any credit profiles ');
3460 -----check if the customer has any credit profiles
3461 SELECT COUNT(*)
3462 INTO l_prof_count
3463 FROM
3464 hz_customer_profiles cp
3465 , hz_cust_profile_amts cpa
3466 WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
3467 AND cp.site_use_id IS NULL
3468 AND cp.cust_account_id=party_cust_csr_rec.customer_id;
3469
3470 IF l_prof_count>0
3471 THEN
3472
3473 OE_DEBUG_PUB.ADD('Customer has credit profiles ');
3474 OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3475
3476 ----update temp table with unchecked exposure
3477 UPDATE oe_credit_exposure_temp
3478 SET unchecked_exposure=l_cust_unchk_exposure
3479 WHERE customer_id=party_cust_csr_rec.customer_id
3480 AND party_id=party_hier_csr1_rec.party_id
3481 AND bill_to_site_id IS NULL
3482 AND report_by_option='PARTY_DETAILS';
3483
3484 -----there are no credit profiles for this customer
3485 ----insert unchecked exposure
3486 ELSE
3487
3488 OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
3489 OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3490
3491 -----insert data into temp table
3492 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3493 ( party_id
3494 , party_name
3495 , party_number
3496 , party_level
3497 , party_parent_id
3498 , report_by_option
3499 , customer_id
3500 , customer_name
3504 , credit_limit_currency
3501 , customer_number
3502 , bill_to_site_id
3503 , bill_to_site_name
3505 , cr_cur_overall_limit
3506 , cr_cur_exposure
3507 , cr_cur_available
3508 , base_currency
3509 , base_cur_overall_limit
3510 , base_cur_exposure
3511 , base_cur_available
3512 , unchecked_exposure
3513 , global_exposure_flag
3514 )
3515 VALUES
3516 ( party_hier_csr1_rec.party_id
3517 , party_hier_csr1_rec.Party
3518 , party_hier_csr1_rec.party_number
3519 , 0
3520 , l_specific_party_id
3521 , 'PARTY_DETAILS'
3522 , party_cust_csr_rec.customer_id
3523 , party_cust_csr_rec.Customer
3524 , party_cust_csr_rec.customer_number
3525 , NULL
3526 , NULL
3527 , NULL
3528 , NULL
3529 , NULL
3530 , NULL
3531 , l_base_currency
3532 , NULL
3533 , NULL
3534 , NULL
3535 , l_cust_unchk_exposure
3536 , 'Y'
3537 );
3538
3539 OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
3540
3541 END IF; --end of checking if the customer has credit profile
3542
3543 ----end loop for customers
3544 END LOOP;
3545
3546 END LOOP; ----end loop for parties
3547
3548 OE_DEBUG_PUB.ADD('Out Parties Loop 1 ');
3549
3550 END IF; ---end of cheking if the party is part of the hierarchy
3551
3552 -------- Customer Summary Report-----------
3553
3554 ELSIF p_report_by_option='CUST_SUMMARY'
3555 OR p_report_by_option='CUST_DETAILS'
3556 THEN
3557
3558 OE_DEBUG_PUB.ADD('IN Cust Summary Report');
3559
3560 --Performance issue: start (SQL ID-16485806 FTS on HZ_CUST_ACCOUNTS and HZ_CUST_PROFILE_CLASSES)
3561 IF (p_customer_name_low IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3562 OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3563 OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3564
3565 OPEN cust_cur FOR
3566 SELECT
3567 SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3568 , c.cust_account_id customer_id
3569 , c.account_number customer_number
3570 FROM
3571 hz_cust_accounts c
3572 , hz_parties party
3573 WHERE c.status='A'
3574 AND c.party_id = party.party_id
3575 AND party.party_name BETWEEN p_customer_name_low
3576 AND NVL(p_customer_name_high, party.party_name)
3577 AND c.account_number BETWEEN p_cust_number_low
3578 AND NVL(p_cust_number_high, c.account_number )
3579 AND c.cust_account_id IN (SELECT cp.cust_account_id
3580 FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3581 WHERE cp.profile_class_id=cpc.profile_class_id
3582 AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3583 AND NVL(p_prof_class_high, cpc.name)) ;
3584 ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3585 OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3586 OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3587
3588 OPEN cust_cur FOR
3589 SELECT
3590 SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3591 , c.cust_account_id customer_id
3592 , c.account_number customer_number
3593 FROM
3594 hz_cust_accounts c
3595 , hz_parties party
3596 WHERE c.status='A'
3597 AND c.party_id = party.party_id
3598 AND party.party_name <= p_customer_name_high
3599 AND c.account_number BETWEEN p_cust_number_low
3600 AND NVL(p_cust_number_high, c.account_number )
3601 AND c.cust_account_id IN (SELECT cp.cust_account_id
3602 FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3603 WHERE cp.profile_class_id=cpc.profile_class_id
3604 AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3605 AND NVL(p_prof_class_high, cpc.name)) ;
3606 ELSIF (p_customer_name_low IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3607 OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3608 OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3609
3610 OPEN cust_cur FOR
3611 SELECT
3612 SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3613 , c.cust_account_id customer_id
3614 , c.account_number customer_number
3615 FROM
3616 hz_cust_accounts c
3617 , hz_parties party
3618 WHERE c.status='A'
3619 AND c.party_id = party.party_id
3620 AND party.party_name BETWEEN p_customer_name_low
3621 AND NVL(p_customer_name_high, party.party_name)
3622 AND c.account_number <=p_cust_number_high
3623 AND c.cust_account_id IN (SELECT cp.cust_account_id
3627 AND NVL(p_prof_class_high, cpc.name)) ;
3624 FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3625 WHERE cp.profile_class_id=cpc.profile_class_id
3626 AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3628 ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3629 OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3630 OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3631
3632 OPEN cust_cur FOR
3633 SELECT
3634 SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3635 , c.cust_account_id customer_id
3636 , c.account_number customer_number
3637 FROM
3638 hz_cust_accounts c
3639 , hz_parties party
3640 WHERE c.status='A'
3641 AND c.party_id = party.party_id
3642 AND party.party_name <= p_customer_name_high
3643 AND c.account_number <= p_cust_number_high
3644 AND c.cust_account_id IN (SELECT cp.cust_account_id
3645 FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3646 WHERE cp.profile_class_id=cpc.profile_class_id
3647 AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3648 AND NVL(p_prof_class_high, cpc.name)) ;
3649 ELSIF (p_customer_name_low IS NOT NULL) THEN
3650 OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3651
3652 OPEN cust_cur FOR
3653 SELECT
3654 SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3655 , c.cust_account_id customer_id
3656 , c.account_number customer_number
3657 FROM
3658 hz_cust_accounts c
3659 , hz_parties party
3660 WHERE c.status='A'
3661 AND c.party_id = party.party_id
3662 AND party.party_name BETWEEN p_customer_name_low
3663 AND NVL(p_customer_name_high, party.party_name)
3664 AND c.cust_account_id IN (SELECT cp.cust_account_id
3665 FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3666 WHERE cp.profile_class_id=cpc.profile_class_id
3667 AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3668 AND NVL(p_prof_class_high, cpc.name)) ;
3669 ELSIF (p_customer_name_high IS NOT NULL) THEN
3670 OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3671
3672 OPEN cust_cur FOR
3673 SELECT
3674 SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3675 , c.cust_account_id customer_id
3676 , c.account_number customer_number
3677 FROM
3678 hz_cust_accounts c
3679 , hz_parties party
3680 WHERE c.status='A'
3681 AND c.party_id = party.party_id
3682 AND party.party_name <= p_customer_name_high
3683 AND c.cust_account_id IN (SELECT cp.cust_account_id
3684 FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3685 WHERE cp.profile_class_id=cpc.profile_class_id
3686 AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3687 AND NVL(p_prof_class_high, cpc.name)) ;
3688 ELSIF (p_cust_number_low IS NOT NULL) THEN
3689 OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3690
3691 OPEN cust_cur FOR
3692 SELECT
3693 SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3694 , c.cust_account_id customer_id
3695 , c.account_number customer_number
3696 FROM
3697 hz_cust_accounts c
3698 , hz_parties party
3699 WHERE c.status='A'
3700 AND c.party_id = party.party_id
3701 AND c.account_number BETWEEN p_cust_number_low
3702 AND NVL(p_cust_number_high, c.account_number )
3703 AND c.cust_account_id IN (SELECT cp.cust_account_id
3704 FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3705 WHERE cp.profile_class_id=cpc.profile_class_id
3706 AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3707 AND NVL(p_prof_class_high, cpc.name)) ;
3708 ELSIF (p_cust_number_high IS NOT NULL) THEN
3709 OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3710
3711 OPEN cust_cur FOR
3712 SELECT
3713 SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3714 , c.cust_account_id customer_id
3715 , c.account_number customer_number
3716 FROM
3717 hz_cust_accounts c
3718 , hz_parties party
3719 WHERE c.status='A'
3720 AND c.party_id = party.party_id
3721 AND c.account_number <= p_cust_number_high
3722 AND c.cust_account_id IN (SELECT cp.cust_account_id
3723 FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3724 WHERE cp.profile_class_id=cpc.profile_class_id
3725 AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3726 AND NVL(p_prof_class_high, cpc.name)) ;
3730 OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3727 ELSIF (p_customer_name_low IS NULL) AND (p_customer_name_high IS NULL) AND
3728 (p_cust_number_high IS NULL) AND (p_cust_number_low IS NULL) THEN
3729 OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3731 OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3732 OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3733
3734 OPEN cust_cur FOR
3735 SELECT
3736 SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3737 , c.cust_account_id customer_id
3738 , c.account_number customer_number
3739 FROM
3740 hz_cust_accounts c
3741 , hz_parties party
3742 WHERE c.status='A'
3743 AND c.party_id = party.party_id
3744 AND c.cust_account_id IN (SELECT cp.cust_account_id
3745 FROM hz_cust_profile_classes cpc,hz_customer_profiles cp
3746 WHERE cp.profile_class_id=cpc.profile_class_id
3747 AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3748 AND NVL(p_prof_class_high, cpc.name)) ;
3749 END IF ;
3750 --Performance issue: end (SQL ID-16485806 FTS on HZ_CUST_ACCOUNTS and HZ_CUST_PROFILE_CLASSES)
3751
3752 ------start loop for customers
3753 LOOP
3754 FETCH cust_cur INTO cust_csr_rec;
3755 EXIT WHEN cust_cur%NOTFOUND;
3756
3757 ----Empty global variables
3758 OE_Credit_Engine_GRP.G_cust_curr_tbl:=l_empty_curr_tbl;
3759 OE_Credit_Engine_GRP.G_cust_incl_all_flag:='N';
3760
3761 ----start loop for customer credit profiles
3762 FOR cust_prof_csr_rec IN cust_prof_csr(p_customer_id=>cust_csr_rec.customer_id)
3763 LOOP
3764 ------calculate customer credit exposure
3765 OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
3766 ( p_customer_id => cust_csr_rec.customer_id
3767 , p_site_id => NULL
3768 , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
3769 , p_credit_check_rule_id => p_cr_check_rule_id
3770 , x_total_exposure => l_cust_total_exposure
3771 , x_return_status => l_return_status
3772 );
3773
3774 OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
3775 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3776
3777 IF l_return_status = 'C'
3778 THEN
3779 l_return_status1:='C';
3780
3781 EXIT;
3782
3783 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3784 THEN
3785 RAISE FND_API.G_EXC_ERROR;
3786 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3787 THEN
3788 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3789 END IF;
3790
3791 -----calculate available credit
3792 l_cust_available:=cust_prof_csr_rec.cust_overall_limit - l_cust_total_exposure;
3793
3794 -----convert overall_credit_limit, exposure and available credit into base currency
3795 OE_DEBUG_PUB.ADD('IN Convert amounts ');
3796
3797 l_base_cur_overall_limit :=
3798 GL_CURRENCY_API.Convert_closest_amount_sql
3799 ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3800 , x_to_currency => l_base_currency
3801 , x_conversion_date => sysdate
3802 , x_conversion_type => l_conversion_type
3803 , x_user_rate => NULL
3804 , x_amount => cust_prof_csr_rec.cust_overall_limit
3805 , x_max_roll_days => -1
3806 );
3807
3808 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3809 OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3810
3811 l_base_cur_exposure :=
3812 GL_CURRENCY_API.Convert_closest_amount_sql
3813 ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3814 , x_to_currency => l_base_currency
3815 , x_conversion_date => sysdate
3816 , x_conversion_type => l_conversion_type
3817 , x_user_rate => NULL
3818 , x_amount => l_cust_total_exposure
3819 , x_max_roll_days => -1
3820 );
3821
3822 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3826 GL_CURRENCY_API.Convert_closest_amount_sql
3823 OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3824
3825 l_base_cur_available :=
3827 ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3828 , x_to_currency => l_base_currency
3829 , x_conversion_date => sysdate
3830 , x_conversion_type => l_conversion_type
3831 , x_user_rate => NULL
3832 , x_amount => l_cust_available
3833 , x_max_roll_days => -1
3834 );
3835
3836 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3837 OE_DEBUG_PUB.ADD('l_base_cur_available = '
3838 || TO_CHAR(l_base_cur_available ));
3839
3840 OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3841
3842 OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
3843
3844 l_global_exposure_flag :=
3845 OE_CREDIT_CHECK_UTIL.Get_global_exposure_flag
3846 ( p_entity_type => 'CUSTOMER'
3847 , p_entity_id => cust_csr_rec.customer_id
3848 , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
3849 ) ;
3850
3851
3852 OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
3853 l_global_exposure_flag );
3854
3855 OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3856
3857 -----insert data into temp table
3858 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3859 ( party_id
3860 , party_name
3861 , party_number
3862 , party_level
3863 , party_parent_id
3864 , report_by_option
3865 , customer_id
3866 , customer_name
3867 , customer_number
3868 , bill_to_site_id
3869 , bill_to_site_name
3870 , credit_limit_currency
3871 , cr_cur_overall_limit
3872 , cr_cur_exposure
3873 , cr_cur_available
3874 , base_currency
3875 , base_cur_overall_limit
3876 , base_cur_exposure
3877 , base_cur_available
3878 , unchecked_exposure
3879 , global_exposure_flag
3880 )
3881 VALUES
3882 ( NULL
3883 , NULL
3884 , NULL
3885 , NULL
3886 , NULL
3887 , 'CUST_SUMMARY'
3888 , cust_csr_rec.customer_id
3889 , cust_csr_rec.Customer
3890 , cust_csr_rec.customer_number
3891 , NULL
3892 , NULL
3893 , cust_prof_csr_rec.cust_currency_code
3894 , cust_prof_csr_rec.cust_overall_limit
3895 , l_cust_total_exposure
3896 , l_cust_available
3897 , l_base_currency
3898 , l_base_cur_overall_limit
3899 , l_base_cur_exposure
3900 , l_base_cur_available
3901 , NULL
3902 , l_global_exposure_flag
3903 );
3904
3905 OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
3906
3907 ----end loop for customer credit profiles
3908 END LOOP;
3909
3910 IF l_return_status = 'C'
3911 THEN
3912 l_return_status1:='C';
3913
3914 EXIT;
3915 END IF;
3916
3917 ----calculate unchecked exposure
3918 ----if global variable G_cust_incl_all_flag is 'Y'
3919 ----then unchecked exposure will be 0
3920 IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
3921 THEN
3922 OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
3923 so l_cust_unchk_exposure=0');
3924
3925 l_cust_unchk_exposure:=0;
3926 ELSE
3927 OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the customer= '
3928 ||TO_CHAR(cust_csr_rec.customer_id));
3929
3930 Get_unchecked_exposure
3931 ( p_party_id => NULL
3932 , p_customer_id => cust_csr_rec.customer_id
3933 , p_site_id => NULL
3934 , p_base_currency => l_base_currency
3935 , p_credit_check_rule_id => p_cr_check_rule_id
3936 , x_unchecked_expousre => l_cust_unchk_exposure
3937 , x_return_status => l_return_status
3938 );
3939 END IF;
3940
3941 OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer= '
3942 ||TO_CHAR(cust_csr_rec.customer_id));
3943 OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3944 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3945
3946 IF l_return_status = 'C'
3947 THEN
3948 l_return_status1:='C';
3949
3950 EXIT;
3951
3952 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
3953 THEN
3954 RAISE FND_API.G_EXC_ERROR;
3955 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
3956 THEN
3957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3958 END IF;
3959
3960 OE_DEBUG_PUB.ADD('Check if the customer has any credit profiles ');
3961 -----check if the customer has any credit profiles
3962 SELECT COUNT(*)
3963 INTO l_prof_count
3964 FROM
3965 hz_customer_profiles cp
3966 , hz_cust_profile_amts cpa
3967 WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
3968 AND cp.site_use_id IS NULL
3969 AND cp.cust_account_id=cust_csr_rec.customer_id;
3970
3971 IF l_prof_count>0
3972 THEN
3973
3974 OE_DEBUG_PUB.ADD('Customer has credit profiles ');
3975 OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3976
3977 ----update temp table with unchecked exposure
3978 UPDATE oe_credit_exposure_temp
3982 AND party_id IS NULL;
3979 SET unchecked_exposure=l_cust_unchk_exposure
3980 WHERE customer_id=cust_csr_rec.customer_id
3981 AND bill_to_site_id IS NULL
3983
3984 -----there are no credit profiles for this customer
3985 ----insert unchecked exposure
3986 ELSE
3987
3988 OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
3989 OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3990
3991 -----insert data into temp table
3992 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3993 ( party_id
3994 , party_name
3995 , party_number
3996 , party_level
3997 , party_parent_id
3998 , report_by_option
3999 , customer_id
4000 , customer_name
4001 , customer_number
4002 , bill_to_site_id
4003 , bill_to_site_name
4004 , credit_limit_currency
4005 , cr_cur_overall_limit
4006 , cr_cur_exposure
4007 , cr_cur_available
4008 , base_currency
4009 , base_cur_overall_limit
4010 , base_cur_exposure
4011 , base_cur_available
4012 , unchecked_exposure
4013 , global_exposure_flag
4014 )
4015 VALUES
4016 ( NULL
4017 , NULL
4018 , NULL
4019 , NULL
4020 , NULL
4021 , 'CUST_SUMMARY'
4022 , cust_csr_rec.customer_id
4023 , cust_csr_rec.Customer
4024 , cust_csr_rec.customer_number
4025 , NULL
4026 , NULL
4027 , NULL
4028 , NULL
4029 , NULL
4030 , NULL
4031 , l_base_currency
4032 , NULL
4033 , NULL
4034 , NULL
4035 , l_cust_unchk_exposure
4036 , NULL
4037 );
4038
4039 OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
4040
4041 END IF;
4042
4043 IF p_report_by_option='CUST_DETAILS'
4044 THEN
4045
4046 OE_DEBUG_PUB.ADD('IN Customern Detail Report');
4047 --------Detail Report------------
4048
4049 -------------------Bill-to Site Info-------------
4050 -----start loop for bill-to sites
4051 FOR site_csr_rec IN site_csr(cust_csr_rec.customer_id)
4052 LOOP
4053
4054 ----Empty global variables
4055 OE_Credit_Engine_GRP.G_site_curr_tbl:=l_empty_curr_tbl;
4056 OE_Credit_Engine_GRP.G_site_incl_all_flag:='N';
4057
4058
4059 ----start loop for bill-to site credit profiles
4060 FOR site_prof_csr_rec IN site_prof_csr( p_customer_id => cust_csr_rec.customer_id
4061 , p_site_id => site_csr_rec.site_id)
4062 LOOP
4063 OE_DEBUG_PUB.ADD('IN loop for bill-to sites ');
4064 OE_DEBUG_PUB.ADD('Processing site '||site_csr_rec.Customer_site);
4065
4066 ------calculate bill-to site credit exposure
4067 OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
4068 ( p_customer_id => cust_csr_rec.customer_id
4069 , p_site_id => site_csr_rec.site_id
4070 , p_limit_curr_code => site_prof_csr_rec.site_currency_code
4071 , p_credit_check_rule_id => p_cr_check_rule_id
4072 , x_total_exposure => l_site_total_exposure
4073 , x_return_status => l_return_status
4074 );
4075
4076
4077 OE_DEBUG_PUB.ADD('OUT of Get_Customer_Exposure ');
4078 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4079 OE_DEBUG_PUB.ADD('l_site_total_exposure = '||TO_CHAR(l_site_total_exposure));
4080
4081 IF l_return_status = 'C'
4082 THEN
4083 l_return_status1:='C';
4084
4085 EXIT;
4086
4087 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4088 THEN
4089 RAISE FND_API.G_EXC_ERROR;
4090 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4091 THEN
4092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4093 END IF;
4094
4095 -----calculate available credit
4096 l_site_available:=site_prof_csr_rec.site_overall_limit - l_site_total_exposure;
4097
4098 -----convert overall_credit_limit, exposure and available credit into base currency
4099 l_base_cur_overall_limit :=
4100 GL_CURRENCY_API.Convert_closest_amount_sql
4101 ( x_from_currency => site_prof_csr_rec.site_currency_code
4102 , x_to_currency => l_base_currency
4103 , x_conversion_date => SYSDATE
4104 , x_conversion_type => l_conversion_type
4105 , x_user_rate => NULL
4106 , x_amount => site_prof_csr_rec.site_overall_limit
4107 , x_max_roll_days => -1
4108 );
4109
4110 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4111 OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
4112
4113
4114 l_base_cur_exposure :=
4115 GL_CURRENCY_API.Convert_closest_amount_sql
4116 ( x_from_currency => site_prof_csr_rec.site_currency_code
4117 , x_to_currency => l_base_currency
4118 , x_conversion_date => SYSDATE
4119 , x_conversion_type => l_conversion_type
4120 , x_user_rate => NULL
4121 , x_amount => l_site_total_exposure
4122 , x_max_roll_days => -1
4123 );
4124
4128
4125 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4126 OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
4127
4129 l_base_cur_available :=
4130 GL_CURRENCY_API.Convert_closest_amount_sql
4131 ( x_from_currency => site_prof_csr_rec.site_currency_code
4132 , x_to_currency => l_base_currency
4133 , x_conversion_date => SYSDATE
4134 , x_conversion_type => l_conversion_type
4135 , x_user_rate => NULL
4136 , x_amount => l_site_available
4137 , x_max_roll_days => -1
4138 );
4139
4140 OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4141 OE_DEBUG_PUB.ADD('l_base_cur_available = '|| TO_CHAR(l_base_cur_available));
4142
4143 -----insert data into temp table
4144 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4145 ( party_id
4146 , party_name
4147 , party_number
4148 , party_level
4149 , party_parent_id
4150 , report_by_option
4151 , customer_id
4152 , customer_name
4153 , customer_number
4154 , bill_to_site_id
4155 , bill_to_site_name
4156 , credit_limit_currency
4157 , cr_cur_overall_limit
4158 , cr_cur_exposure
4159 , cr_cur_available
4160 , base_currency
4161 , base_cur_overall_limit
4162 , base_cur_exposure
4163 , base_cur_available
4164 , unchecked_exposure
4165 , global_exposure_flag
4166 )
4167 VALUES
4168 ( NULL
4169 , NULL
4170 , NULL
4171 , NULL
4172 , NULL
4173 , 'CUST_DETAILS'
4174 , cust_csr_rec.customer_id
4175 , cust_csr_rec.Customer
4176 , cust_csr_rec.customer_number
4177 , site_csr_rec.site_id
4178 , site_csr_rec.Customer_site
4179 , site_prof_csr_rec.site_currency_code
4180 , site_prof_csr_rec.site_overall_limit
4181 , l_site_total_exposure
4182 , l_site_available
4183 , l_base_currency
4184 , l_base_cur_overall_limit
4185 , l_base_cur_exposure
4186 , l_base_cur_available
4187 , NULL
4188 , 'N'
4189 );
4190
4191 ----end loop for bill-to site credit profiles
4192 END LOOP;
4193
4194 ----calculate unchecked exposure for bill-to site
4195 ----if global variable for include_all_flag is 'Y'
4196 ----then unchecked_exposure will be 0
4197
4198 IF OE_Credit_Engine_GRP.G_site_incl_all_flag='Y'
4199 THEN
4200 OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_site_incl_all_flag='
4201 ||OE_Credit_Engine_GRP.G_site_incl_all_flag
4202 ||'so l_site_unchk_exposure=0');
4203
4204 l_site_unchk_exposure:=0;
4205 ELSE
4206 OE_DEBUG_PUB.ADD('Start Get_unchecked_exposure for bill-to site ='
4207 ||TO_CHAR(site_csr_rec.site_id));
4208
4209 Get_unchecked_exposure
4210 ( p_party_id => NULL
4211 , p_customer_id => cust_csr_rec.customer_id
4212 , p_site_id => site_csr_rec.site_id
4213 , p_base_currency => l_base_currency
4214 , p_credit_check_rule_id => p_cr_check_rule_id
4215 , x_unchecked_expousre => l_site_unchk_exposure
4216 , x_return_status => l_return_status
4217 );
4218 END IF;
4219
4220 OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for bill-to site '||
4221 TO_CHAR(site_csr_rec.site_id));
4222 OE_DEBUG_PUB.ADD('l_site_unchk_exposure = '|| TO_CHAR(l_site_unchk_exposure) );
4223 OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4224
4225 IF l_return_status = 'C'
4226 THEN
4227 l_return_status1:='C';
4228
4229 EXIT;
4230
4231 ELSIF l_return_status = FND_API.G_RET_STS_ERROR
4232 THEN
4233 RAISE FND_API.G_EXC_ERROR;
4234 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
4235 THEN
4236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4237 END IF;
4238
4239 OE_DEBUG_PUB.ADD('Check if the customer site has any credit profiles ');
4240
4241 -----check if the customer site has any credit profiles
4242 SELECT COUNT(*)
4243 INTO l_prof_count1
4244 FROM
4245 hz_customer_profiles cp
4246 , hz_cust_profile_amts cpa
4247 WHERE cp.cust_account_profile_id=cpa.cust_account_profile_id
4248 AND cp.site_use_id =site_csr_rec.site_id
4249 AND cp.cust_account_id=cust_csr_rec.customer_id;
4250
4251 IF l_prof_count1>0
4252 THEN
4253
4254 OE_DEBUG_PUB.ADD('Customer site has credit profiles ');
4255 OE_DEBUG_PUB.ADD('Update temp table with unchecked_exposure ');
4256
4257 ----update temp table with unchecked exposure for bill-to site
4258 UPDATE oe_credit_exposure_temp
4259 SET unchecked_exposure=l_site_unchk_exposure
4260 WHERE customer_id=cust_csr_rec.customer_id
4261 AND bill_to_site_id=site_csr_rec.site_id;
4262
4263 -----there are no credit profiles for this customer site
4264 -----insert unchecked exposure into temp table
4265 ELSE
4266
4267 OE_DEBUG_PUB.ADD('Customer site does not have credit profiles ');
4268 OE_DEBUG_PUB.ADD('Insert into temp table unchecked_exposure ');
4269
4270 INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4271 ( party_id
4272 , party_name
4273 , party_number
4274 , party_level
4275 , party_parent_id
4276 , report_by_option
4277 , customer_id
4278 , customer_name
4279 , customer_number
4280 , bill_to_site_id
4281 , bill_to_site_name
4282 , credit_limit_currency
4283 , cr_cur_overall_limit
4284 , cr_cur_exposure
4285 , cr_cur_available
4286 , base_currency
4287 , base_cur_overall_limit
4288 , base_cur_exposure
4289 , base_cur_available
4290 , unchecked_exposure
4291 , global_exposure_flag
4292 )
4293 VALUES
4294 ( NULL
4295 , NULL
4296 , NULL
4297 , NULL
4298 , NULL
4299 , 'CUST_DETAILS'
4300 , cust_csr_rec.customer_id
4301 , cust_csr_rec.Customer
4302 , cust_csr_rec.customer_number
4303 , site_csr_rec.site_id
4304 , site_csr_rec.Customer_site
4305 , NULL
4306 , NULL
4307 , NULL
4308 , NULL
4309 , l_base_currency
4310 , NULL
4311 , NULL
4312 , NULL
4313 , l_site_unchk_exposure
4314 , 'N'
4315 );
4316
4317 OE_DEBUG_PUB.ADD('Out of the insert into temp table ');
4318
4319 END IF;
4320
4321 -----end loop for bill-to sites
4322 END LOOP;
4323 END IF;
4324 ----end loop for customers
4325 END LOOP;
4326 END IF; -----end for p_report_by_option
4327
4328 x_return_status :=l_return_status1;
4329
4330 -----if some exchange rates are missing, delete everything
4331 ---- from the temp table, so that report will print no data found
4332 IF l_return_status1='C'
4333 THEN
4334 DELETE
4335 FROM OE_CREDIT_EXPOSURE_TEMP;
4336 END IF;
4337
4338 COMMIT;
4339 OE_DEBUG_PUB.ADD('Out of OEXRCRCB:Credit_exposure_report_utils with the status='||x_return_status);
4340
4341 EXCEPTION
4342
4343 WHEN GL_CURRENCY_API.NO_RATE THEN
4344 OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - GL_CURRENCY_API.NO_RATE');
4345 RAISE;
4346 WHEN OTHERS THEN
4347 OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - Unexpected Error',1);
4348 OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
4349
4350 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4351 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Credit_exposure_report_utils');
4352 END IF;
4353 RAISE;
4354 END Credit_exposure_report_utils;
4355
4356
4357 END OE_CREDIT_CHECK_RPT;