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