[Home] [Help]
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
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;
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
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;
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;
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;
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
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');
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;
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: --=====================================================================
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);
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);
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);
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);
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);
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);
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);
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);
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);
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);
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:
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
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.
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.
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
493: ||v_order_date_clause
494: ||v_order_by_clause;
495:
496: END IF;
497: OE_DEBUG_PUB.ADD('Hold select : '||v_hold_final_select,2); --bug# 5187621
498: OE_DEBUG_PUB.ADD('Release select : '||v_release_final_select,2);
499: --
500: -- Open the dynamic sql cursor
501: --
494: ||v_order_by_clause;
495:
496: END IF;
497: OE_DEBUG_PUB.ADD('Hold select : '||v_hold_final_select,2); --bug# 5187621
498: OE_DEBUG_PUB.ADD('Release select : '||v_release_final_select,2);
499: --
500: -- Open the dynamic sql cursor
501: --
502: v_hold_cursorID := DBMS_SQL.OPEN_CURSOR;
505: -- Parse the query
506: --
507: DBMS_SQL.PARSE(v_hold_cursorID, v_hold_final_select, DBMS_SQL.NATIVE);
508: DBMS_SQL.PARSE(v_release_cursorID, v_release_final_select, DBMS_SQL.NATIVE);
509: OE_DEBUG_PUB.ADD('Parsed the cursor',2);
510: --
511: -- Define input variables. Only bind the variables if it is part of the
512: -- select statement
513: --
510: --
511: -- Define input variables. Only bind the variables if it is part of the
512: -- select statement
513: --
514: OE_DEBUG_PUB.ADD('Start binding input variables',2);
515:
516: IF p_order_by = 'GREATEST_ORDER_VALUE' THEN
517: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':gl_currency_code',p_gl_currency_code);
518: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':gl_currency_code',p_gl_currency_code);
515:
516: IF p_order_by = 'GREATEST_ORDER_VALUE' THEN
517: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':gl_currency_code',p_gl_currency_code);
518: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':gl_currency_code',p_gl_currency_code);
519: OE_DEBUG_PUB.ADD('Binded gl_currency_code',2);
520: END IF;
521:
522: IF INSTRB(v_cust_prof_class_clause,':cust_prof_class_from') <> 0 THEN
523: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_prof_class_from',p_cust_prof_class_name_from);
521:
522: IF INSTRB(v_cust_prof_class_clause,':cust_prof_class_from') <> 0 THEN
523: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_prof_class_from',p_cust_prof_class_name_from);
524: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_prof_class_from',p_cust_prof_class_name_from);
525: OE_DEBUG_PUB.ADD('Binded cust_prof_class_from',2);
526: END IF;
527:
528: IF INSTRB(v_cust_prof_class_clause,':cust_prof_class_to') <> 0 THEN
529: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_prof_class_to',p_cust_prof_class_name_to);
527:
528: IF INSTRB(v_cust_prof_class_clause,':cust_prof_class_to') <> 0 THEN
529: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_prof_class_to',p_cust_prof_class_name_to);
530: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_prof_class_to' ,p_cust_prof_class_name_to);
531: OE_DEBUG_PUB.ADD('Binded cust_prof_class_to',2);
532: END IF;
533:
534: IF INSTRB(v_party_name_clause, ':party_name_from') <> 0 THEN
535: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':party_name_from',p_party_name_from);
533:
534: IF INSTRB(v_party_name_clause, ':party_name_from') <> 0 THEN
535: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':party_name_from',p_party_name_from);
536: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':party_name_from',p_party_name_from);
537: OE_DEBUG_PUB.ADD('Binded party_name_from',2);
538: END IF;
539:
540: IF INSTRB(v_party_name_clause, ':party_name_to') <> 0 THEN
541: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':party_name_to',p_party_name_to);
539:
540: IF INSTRB(v_party_name_clause, ':party_name_to') <> 0 THEN
541: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':party_name_to',p_party_name_to);
542: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':party_name_to' ,p_party_name_to);
543: OE_DEBUG_PUB.ADD('Binded party_name_to',2);
544: END IF;
545:
546: IF INSTRB(v_cust_acct_number_clause, ':cust_acct_number_from') <> 0 THEN
547: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_acct_number_from', p_cust_acct_number_from);
545:
546: IF INSTRB(v_cust_acct_number_clause, ':cust_acct_number_from') <> 0 THEN
547: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_acct_number_from', p_cust_acct_number_from);
548: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_acct_number_from', p_cust_acct_number_from);
549: OE_DEBUG_PUB.ADD('Binded cust_acct_number_from',2);
550: END IF;
551:
552: IF INSTRB(v_cust_acct_number_clause, ':cust_acct_number_to') <> 0 THEN
553: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_acct_number_to', p_cust_acct_number_to);
551:
552: IF INSTRB(v_cust_acct_number_clause, ':cust_acct_number_to') <> 0 THEN
553: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_acct_number_to', p_cust_acct_number_to);
554: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_acct_number_to', p_cust_acct_number_to);
555: OE_DEBUG_PUB.ADD('Binded cust_acct_number_to',2);
556: END IF;
557:
558: IF INSTRB(v_order_date_clause, ':order_date_from') <> 0 THEN
559: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_from', p_order_date_from);
557:
558: IF INSTRB(v_order_date_clause, ':order_date_from') <> 0 THEN
559: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_from', p_order_date_from);
560: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':order_date_from', p_order_date_from);
561: OE_DEBUG_PUB.ADD('Binded order_date_from',2);
562: END IF;
563:
564: IF INSTRB(v_order_date_clause, ':order_date_to') <> 0 THEN
565: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_to', p_order_date_to);
563:
564: IF INSTRB(v_order_date_clause, ':order_date_to') <> 0 THEN
565: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_to', p_order_date_to);
566: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':order_date_to', p_order_date_to);
567: OE_DEBUG_PUB.ADD('Binded order_date_to',2);
568: END IF;
569: OE_DEBUG_PUB.ADD('Finished binding input variables',2);
570: --
571: --Define the output variables
565: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_to', p_order_date_to);
566: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':order_date_to', p_order_date_to);
567: OE_DEBUG_PUB.ADD('Binded order_date_to',2);
568: END IF;
569: OE_DEBUG_PUB.ADD('Finished binding input variables',2);
570: --
571: --Define the output variables
572: --
573: DBMS_SQL.DEFINE_COLUMN(v_hold_cursorID, 1, f_hold_header_id);
572: --
573: DBMS_SQL.DEFINE_COLUMN(v_hold_cursorID, 1, f_hold_header_id);
574: DBMS_SQL.DEFINE_COLUMN(v_release_cursorID, 1, f_release_header_id);
575:
576: OE_DEBUG_PUB.ADD('Hold ID : '||v_hold_cursorID,2); --bug# 5187621
577: OE_DEBUG_PUB.ADD('Release ID : '||v_release_cursorID,2);
578: --
579: -- Set the l_profile_org_id value to use instead of checking for NULL
580: --
573: DBMS_SQL.DEFINE_COLUMN(v_hold_cursorID, 1, f_hold_header_id);
574: DBMS_SQL.DEFINE_COLUMN(v_release_cursorID, 1, f_release_header_id);
575:
576: OE_DEBUG_PUB.ADD('Hold ID : '||v_hold_cursorID,2); --bug# 5187621
577: OE_DEBUG_PUB.ADD('Release ID : '||v_release_cursorID,2);
578: --
579: -- Set the l_profile_org_id value to use instead of checking for NULL
580: --
581: -- MOAC Start
584: IF l_profile_org_id IS NOT NULL THEN
585: MO_GLOBAL.set_policy_context('S', l_profile_org_id);
586:
587: IF l_debug_level > 0 THEN
588: OE_DEBUG_PUB.Add('org_id : ' || l_profile_org_id);
589: END IF;
590:
591: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':profile_org_id', l_profile_org_id);
592: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':profile_org_id', l_profile_org_id);
589: END IF;
590:
591: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':profile_org_id', l_profile_org_id);
592: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':profile_org_id', l_profile_org_id);
593: OE_DEBUG_PUB.ADD('Binded profile_org_id',2);
594:
595: --------------------------------------------------------------------
596: --
597: -- RELEASE: Execute the RELEASE select statement
608: --
609: -- Retreive the header ID into output variable
610: --
611: DBMS_SQL.COLUMN_VALUE(v_release_cursorID, 1, f_release_header_id);
612: OE_DEBUG_PUB.ADD('f_release_header_id = '|| f_release_header_id,4);
613: OE_Credit_Engine_GRP.Check_Credit
614: ( p_header_id => f_release_header_id
615: , p_calling_action => 'AUTO RELEASE'
616: , x_msg_count => l_msg_count
618: , x_result_out => l_result_out
619: , x_cc_hold_comment => l_cc_hold_comment
620: , x_return_status => l_return_status
621: );
622: OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
623: --
624: -- Insert the result into the temporary table
625: --
626: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
653: --
654: -- Retreive the header ID into output variable
655: --
656: DBMS_SQL.COLUMN_VALUE(v_hold_cursorID, 1, f_hold_header_id);
657: OE_DEBUG_PUB.ADD('f_hold_header_id = '|| f_hold_header_id,4);
658: OE_Credit_Engine_GRP.Check_Credit
659: ( p_header_id => f_hold_header_id
660: , p_calling_action => 'AUTO HOLD'
661: , x_msg_count => l_msg_count
663: , x_result_out => l_result_out
664: , x_cc_hold_comment => l_cc_hold_comment
665: , x_return_status => l_return_status
666: );
667: OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
668: --
669: -- Insert the result into the temporary table
670: --
671: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
682: END IF;
683: END LOOP;
684: --
685: DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
686: OE_DEBUG_PUB.ADD('Orders Processed = '||l_count,2);
687: ELSE
688: OPEN l_secured_ou_cur;
689:
690: LOOP
698:
699: MO_GLOBAL.set_policy_context('S', l_profile_org_id);
700:
701: IF l_debug_level > 0 THEN
702: OE_DEBUG_PUB.Add('org_id : ' || l_profile_org_id);
703: END IF;
704:
705: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':profile_org_id', l_profile_org_id);
706: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':profile_org_id', l_profile_org_id);
703: END IF;
704:
705: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':profile_org_id', l_profile_org_id);
706: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':profile_org_id', l_profile_org_id);
707: OE_DEBUG_PUB.ADD('Binded profile_org_id',2);
708:
709: --------------------------------------------------------------------
710: --
711: -- RELEASE: Execute the RELEASE select statement
722: --
723: -- Retreive the header ID into output variable
724: --
725: DBMS_SQL.COLUMN_VALUE(v_release_cursorID, 1, f_release_header_id);
726: OE_DEBUG_PUB.ADD('f_release_header_id = '|| f_release_header_id,4);
727: OE_Credit_Engine_GRP.Check_Credit
728: ( p_header_id => f_release_header_id
729: , p_calling_action => 'AUTO RELEASE'
730: , x_msg_count => l_msg_count
732: , x_result_out => l_result_out
733: , x_cc_hold_comment => l_cc_hold_comment
734: , x_return_status => l_return_status
735: );
736: OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
737: --
738: -- Insert the result into the temporary table
739: --
740: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
765: --
766: -- Retreive the header ID into output variable
767: --
768: DBMS_SQL.COLUMN_VALUE(v_hold_cursorID, 1, f_hold_header_id);
769: OE_DEBUG_PUB.ADD('f_hold_header_id = '|| f_hold_header_id,4);
770: OE_Credit_Engine_GRP.Check_Credit
771: ( p_header_id => f_hold_header_id
772: , p_calling_action => 'AUTO HOLD'
773: , x_msg_count => l_msg_count
775: , x_result_out => l_result_out
776: , x_cc_hold_comment => l_cc_hold_comment
777: , x_return_status => l_return_status
778: );
779: OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
780: --
781: -- Insert the result into the temporary table
782: --
783: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
797:
798: DBMS_SQL.CLOSE_CURSOR(v_release_cursorID);
799: DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
800:
801: OE_DEBUG_PUB.ADD('Orders Processed = '||l_count,2);
802:
803: CLOSE l_secured_ou_cur;
804: END IF;
805: -- MOAC End
819: , x_result_out => l_result_out
820: , x_cc_hold_comment => l_cc_hold_comment
821: , x_return_status => l_return_status
822: );
823: OE_DEBUG_PUB.ADD('Release: Check_Credit return status = '||l_return_status);
824: --
825: -- Insert the results into the temporary table
826: --
827: OE_DEBUG_PUB.ADD('Right before inserting into temp table');
823: OE_DEBUG_PUB.ADD('Release: Check_Credit return status = '||l_return_status);
824: --
825: -- Insert the results into the temporary table
826: --
827: OE_DEBUG_PUB.ADD('Right before inserting into temp table');
828: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
829: (l_result_out = 'PASS' OR l_result_out = 'PASS_REL')) --bug# 5187621
830: THEN
831: Insert_In_Temp_Table(p_header_id, 'PASS');
836: Insert_In_Temp_Table(p_header_id, 'FAIL');
837: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
838: Insert_In_Temp_Table(p_header_id, 'ERROR');
839: END IF;
840: OE_DEBUG_PUB.ADD('Release: Right after inserting into temp table');
841:
842: OE_Credit_Engine_GRP.Check_Credit
843: ( p_header_id => p_header_id
844: , p_calling_action => 'AUTO HOLD'
847: , x_result_out => l_result_out
848: , x_cc_hold_comment => l_cc_hold_comment
849: , x_return_status => l_return_status
850: );
851: OE_DEBUG_PUB.ADD('Hold: Check_Credit return status = '||l_return_status);
852: --
853: -- Insert the results into the temporary table
854: --
855: OE_DEBUG_PUB.ADD('Right before inserting into temp table');
851: OE_DEBUG_PUB.ADD('Hold: Check_Credit return status = '||l_return_status);
852: --
853: -- Insert the results into the temporary table
854: --
855: OE_DEBUG_PUB.ADD('Right before inserting into temp table');
856: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
857: (l_result_out = 'PASS' OR l_result_out = 'PASS_REL')) --bug# 5187621
858: THEN
859: Insert_In_Temp_Table(p_header_id, 'PASS');
864: Insert_In_Temp_Table(p_header_id, 'FAIL');
865: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
866: Insert_In_Temp_Table(p_header_id, 'ERROR');
867: END IF;
868: OE_DEBUG_PUB.ADD('Hold: Right after inserting into temp table');
869: END IF;
870:
871: OE_DEBUG_PUB.ADD('OUT OEXRCRCB: Credit_Check_Processor',1);
872: EXCEPTION
867: END IF;
868: OE_DEBUG_PUB.ADD('Hold: Right after inserting into temp table');
869: END IF;
870:
871: OE_DEBUG_PUB.ADD('OUT OEXRCRCB: Credit_Check_Processor',1);
872: EXCEPTION
873: WHEN GL_CURRENCY_API.NO_RATE THEN
874: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Error',1);
875: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE',1);
870:
871: OE_DEBUG_PUB.ADD('OUT OEXRCRCB: Credit_Check_Processor',1);
872: EXCEPTION
873: WHEN GL_CURRENCY_API.NO_RATE THEN
874: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Error',1);
875: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE',1);
876: IF DBMS_SQL.IS_OPEN(v_hold_cursorID) THEN
877: DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
878: END IF;
871: OE_DEBUG_PUB.ADD('OUT OEXRCRCB: Credit_Check_Processor',1);
872: EXCEPTION
873: WHEN GL_CURRENCY_API.NO_RATE THEN
874: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Error',1);
875: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE',1);
876: IF DBMS_SQL.IS_OPEN(v_hold_cursorID) THEN
877: DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
878: END IF;
879: IF DBMS_SQL.IS_OPEN(v_release_cursorID) THEN
880: DBMS_SQL.CLOSE_CURSOR(v_release_cursorID);
881: END IF;
882: RAISE;
883: WHEN OTHERS THEN
884: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Unexpected Error',1);
885: OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
886: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
887: OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Credit_Check_Processor');
888: END IF;
881: END IF;
882: RAISE;
883: WHEN OTHERS THEN
884: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Unexpected Error',1);
885: OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
886: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
887: OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Credit_Check_Processor');
888: END IF;
889: IF DBMS_SQL.IS_OPEN(v_hold_cursorID) THEN
953: l_ar_amount NUMBER;
954:
955:
956: BEGIN
957: OE_DEBUG_PUB.ADD('IN Get_Unchecked_Exposure ');
958: x_return_status := FND_API.G_RET_STS_SUCCESS;
959:
960:
961: ---get credit check rule record
971:
972: -----Party level-------
973: IF p_party_id IS NOT NULL AND p_customer_id IS NULL
974: THEN
975: OE_DEBUG_PUB.ADD('global table has rows= '||
976: TO_CHAR(OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT));
977:
978: ----get unchecked usages
979: ---put checked currencies in one string with # as separator
990: ---if there are no usage currencies
991: IF OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT=0
992: THEN
993: ---build the table for all currencies
994: OE_DEBUG_PUB.ADD('Build table for all currencies as unchecked');
995:
996: FOR curr_csr_rec IN curr_csr
997: LOOP
998:
1004: ELSE
1005:
1006: FOR curr_csr_rec IN curr_csr
1007: LOOP
1008: -- OE_DEBUG_PUB.ADD('Start loop for currency '||
1009: -- curr_csr_rec.currency_code);
1010:
1011: IF INSTRB (l_checked_curr_rec,curr_csr_rec.currency_code,1,1)=0
1012: THEN
1020:
1021: END IF;
1022:
1023: -----just for debuging----------------------------------------------------------
1024: OE_DEBUG_PUB.ADD('table for unchecked currencies for the party: ');
1025:
1026: FOR k IN 1..l_cust_unchk_curr_tbl.COUNT
1027: LOOP
1028: OE_DEBUG_PUB.ADD('currency_code=: '||l_cust_unchk_curr_tbl(k).usage_curr_code);
1024: OE_DEBUG_PUB.ADD('table for unchecked currencies for the party: ');
1025:
1026: FOR k IN 1..l_cust_unchk_curr_tbl.COUNT
1027: LOOP
1028: OE_DEBUG_PUB.ADD('currency_code=: '||l_cust_unchk_curr_tbl(k).usage_curr_code);
1029: END LOOP;
1030: --------------------------------------------------------------------------------
1031:
1032: -----calculate exposure
1033:
1034: ----pre-calculate exposure
1035: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1036: THEN
1037: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1038: OE_DEBUG_PUB.ADD('Parameters:');
1039: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1040: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1041: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1034: ----pre-calculate exposure
1035: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1036: THEN
1037: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1038: OE_DEBUG_PUB.ADD('Parameters:');
1039: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1040: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1041: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1042: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1035: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1036: THEN
1037: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1038: OE_DEBUG_PUB.ADD('Parameters:');
1039: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1040: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1041: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1042: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1043: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1036: THEN
1037: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1038: OE_DEBUG_PUB.ADD('Parameters:');
1039: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1040: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1041: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1042: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1043: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1044: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1037: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1038: OE_DEBUG_PUB.ADD('Parameters:');
1039: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1040: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1041: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1042: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1043: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1044: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1045:
1038: OE_DEBUG_PUB.ADD('Parameters:');
1039: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1040: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1041: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1042: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1043: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1044: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1045:
1046: /*
1039: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1040: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1041: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1042: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1043: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1044: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1045:
1046: /*
1047:
1040: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1041: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1042: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1043: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1044: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1045:
1046: /*
1047:
1048: OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1081: , x_error_curr_tbl => l_error_curr_tbl
1082: );
1083:
1084:
1085: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1086: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1087: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1088:
1089: IF l_error_curr_tbl.COUNT<>0
1082: );
1083:
1084:
1085: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1086: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1087: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1088:
1089: IF l_error_curr_tbl.COUNT<>0
1090: THEN
1083:
1084:
1085: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1086: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1087: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1088:
1089: IF l_error_curr_tbl.COUNT<>0
1090: THEN
1091: FOR f IN 1..l_error_curr_tbl.COUNT
1089: IF l_error_curr_tbl.COUNT<>0
1090: THEN
1091: FOR f IN 1..l_error_curr_tbl.COUNT
1092: LOOP
1093: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1094:
1095: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1096: base currency '||p_base_currency||' is missing for conversion type '||
1097: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1091: FOR f IN 1..l_error_curr_tbl.COUNT
1092: LOOP
1093: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1094:
1095: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1096: base currency '||p_base_currency||' is missing for conversion type '||
1097: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1098:
1099: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1095: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1096: base currency '||p_base_currency||' is missing for conversion type '||
1097: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1098:
1099: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1100: END LOOP;
1101:
1102: ---bug fix 2439029
1103:
1115:
1116: ----non pre-calculate exposure
1117: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1118: THEN
1119: OE_DEBUG_PUB.ADD('Party level exposure is supported only for
1120: credit check rule with pre-calculated exposure');
1121:
1122: x_unchecked_expousre:=0;
1123:
1125:
1126: ----Customer level
1127: ELSIF p_site_id IS NULL AND p_party_id IS NULL
1128: THEN
1129: OE_DEBUG_PUB.ADD('global table has rows= '||
1130: TO_CHAR(OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT));
1131:
1132: ----get unchecked usages
1133: ---put checked currencies in one string with # as separator
1144: ---if there are no usage currencies
1145: IF OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT=0
1146: THEN
1147: ---build the table for all currencies
1148: OE_DEBUG_PUB.ADD('Build table for all currencies as unchecked');
1149:
1150: FOR curr_csr_rec IN curr_csr
1151: LOOP
1152:
1158: ELSE
1159:
1160: FOR curr_csr_rec IN curr_csr
1161: LOOP
1162: -- OE_DEBUG_PUB.ADD('Start loop for currency '||
1163: -- curr_csr_rec.currency_code);
1164:
1165: IF INSTRB (l_checked_curr_rec,curr_csr_rec.currency_code,1,1)=0
1166: THEN
1174:
1175: END IF;
1176:
1177: -----just for debuging----------------------------------------------------------
1178: OE_DEBUG_PUB.ADD('table for unchecked currencies for the customer: ');
1179:
1180: FOR k IN 1..l_cust_unchk_curr_tbl.COUNT
1181: LOOP
1182: OE_DEBUG_PUB.ADD('currency_code=: '||l_cust_unchk_curr_tbl(k).usage_curr_code);
1178: OE_DEBUG_PUB.ADD('table for unchecked currencies for the customer: ');
1179:
1180: FOR k IN 1..l_cust_unchk_curr_tbl.COUNT
1181: LOOP
1182: OE_DEBUG_PUB.ADD('currency_code=: '||l_cust_unchk_curr_tbl(k).usage_curr_code);
1183: END LOOP;
1184: --------------------------------------------------------------------------------
1185:
1186: -----calculate exposure
1187:
1188: ----pre-calculate exposure
1189: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1190: THEN
1191: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1192: OE_DEBUG_PUB.ADD('Parameters:');
1193: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1194: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1195: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1188: ----pre-calculate exposure
1189: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1190: THEN
1191: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1192: OE_DEBUG_PUB.ADD('Parameters:');
1193: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1194: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1195: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1196: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1189: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1190: THEN
1191: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1192: OE_DEBUG_PUB.ADD('Parameters:');
1193: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1194: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1195: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1196: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1197: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1190: THEN
1191: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1192: OE_DEBUG_PUB.ADD('Parameters:');
1193: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1194: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1195: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1196: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1197: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1198:
1191: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1192: OE_DEBUG_PUB.ADD('Parameters:');
1193: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1194: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1195: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1196: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1197: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1198:
1199: /*
1192: OE_DEBUG_PUB.ADD('Parameters:');
1193: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1194: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1195: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1196: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1197: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1198:
1199: /*
1200:
1193: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1194: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1195: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1196: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1197: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1198:
1199: /*
1200:
1201: OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1234: , x_error_curr_tbl => l_error_curr_tbl
1235: );
1236:
1237:
1238: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1239: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1240: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1241:
1242: IF l_error_curr_tbl.COUNT<>0
1235: );
1236:
1237:
1238: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1239: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1240: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1241:
1242: IF l_error_curr_tbl.COUNT<>0
1243: THEN
1236:
1237:
1238: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1239: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1240: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1241:
1242: IF l_error_curr_tbl.COUNT<>0
1243: THEN
1244: FOR f IN 1..l_error_curr_tbl.COUNT
1242: IF l_error_curr_tbl.COUNT<>0
1243: THEN
1244: FOR f IN 1..l_error_curr_tbl.COUNT
1245: LOOP
1246: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1247:
1248: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1249: base currency '||p_base_currency||' is missing for conversion type '||
1250: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1244: FOR f IN 1..l_error_curr_tbl.COUNT
1245: LOOP
1246: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1247:
1248: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1249: base currency '||p_base_currency||' is missing for conversion type '||
1250: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1251:
1252: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1248: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1249: base currency '||p_base_currency||' is missing for conversion type '||
1250: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1251:
1252: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1253: END LOOP;
1254:
1255: ---bug fix 2439029
1256:
1268:
1269: ----non pre-calculate exposure
1270: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1271: THEN
1272: OE_DEBUG_PUB.ADD('OEXPCRGB: IN OE_CREDIT_ENGIN_GRB.Get_order_Exposure ');
1273: OE_DEBUG_PUB.ADD('Parameters:');
1274: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1275: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1276: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1269: ----non pre-calculate exposure
1270: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1271: THEN
1272: OE_DEBUG_PUB.ADD('OEXPCRGB: IN OE_CREDIT_ENGIN_GRB.Get_order_Exposure ');
1273: OE_DEBUG_PUB.ADD('Parameters:');
1274: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1275: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1276: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1277: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1270: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1271: THEN
1272: OE_DEBUG_PUB.ADD('OEXPCRGB: IN OE_CREDIT_ENGIN_GRB.Get_order_Exposure ');
1273: OE_DEBUG_PUB.ADD('Parameters:');
1274: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1275: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1276: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1277: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1278: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1271: THEN
1272: OE_DEBUG_PUB.ADD('OEXPCRGB: IN OE_CREDIT_ENGIN_GRB.Get_order_Exposure ');
1273: OE_DEBUG_PUB.ADD('Parameters:');
1274: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1275: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1276: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1277: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1278: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1279: /*
1272: OE_DEBUG_PUB.ADD('OEXPCRGB: IN OE_CREDIT_ENGIN_GRB.Get_order_Exposure ');
1273: OE_DEBUG_PUB.ADD('Parameters:');
1274: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1275: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1276: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1277: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1278: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1279: /*
1280: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1273: OE_DEBUG_PUB.ADD('Parameters:');
1274: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1275: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1276: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1277: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1278: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1279: /*
1280: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1281: ( p_header_id => NULL
1274: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1275: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1276: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1277: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1278: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1279: /*
1280: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1281: ( p_header_id => NULL
1282: , p_transaction_curr_code => NULL
1315: , x_return_status => l_return_status
1316: );
1317:
1318:
1319: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1320: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1321: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1322:
1323: IF l_conversion_status.COUNT<>0
1316: );
1317:
1318:
1319: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1320: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1321: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1322:
1323: IF l_conversion_status.COUNT<>0
1324: THEN
1317:
1318:
1319: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1320: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1321: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1322:
1323: IF l_conversion_status.COUNT<>0
1324: THEN
1325: FOR f IN 1..l_conversion_status.COUNT
1323: IF l_conversion_status.COUNT<>0
1324: THEN
1325: FOR f IN 1..l_conversion_status.COUNT
1326: LOOP
1327: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1328:
1329: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1330: base currency '||p_base_currency||' is missing for conversion type '||
1331: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1325: FOR f IN 1..l_conversion_status.COUNT
1326: LOOP
1327: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1328:
1329: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1330: base currency '||p_base_currency||' is missing for conversion type '||
1331: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1332:
1333: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1329: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1330: base currency '||p_base_currency||' is missing for conversion type '||
1331: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1332:
1333: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1334:
1335: END LOOP;
1336:
1337: ---bug fix 2439029
1353: -----Bill-to Site Level
1354: ELSIF p_site_id IS NOT NULL
1355: THEN
1356:
1357: OE_DEBUG_PUB.ADD('global table has rows= '||
1358: TO_CHAR(OE_Credit_Engine_GRP.G_site_curr_tbl.COUNT));
1359: ----get unchecked usages
1360: ---put checked currencies in one string with # as separator
1361: FOR i in 1..OE_CREDIT_ENGINE_GRP.G_site_curr_tbl.COUNT
1368: ---if there are no usage currencies
1369: IF OE_Credit_Engine_GRP.G_site_curr_tbl.COUNT=0
1370: THEN
1371: ---build the table for all currencies
1372: OE_DEBUG_PUB.ADD('Build table for all currencies as unchecked');
1373:
1374: FOR curr_csr_rec IN curr_csr
1375: LOOP
1376:
1394:
1395: END IF;
1396:
1397: -----just for debuging----------------------------------------------------------
1398: OE_DEBUG_PUB.ADD('table for unchecked currencies for the site: ');
1399:
1400: FOR k IN 1..l_site_unchk_curr_tbl.COUNT
1401: LOOP
1402: OE_DEBUG_PUB.ADD('currency_code=: '||l_site_unchk_curr_tbl(k).usage_curr_code);
1398: OE_DEBUG_PUB.ADD('table for unchecked currencies for the site: ');
1399:
1400: FOR k IN 1..l_site_unchk_curr_tbl.COUNT
1401: LOOP
1402: OE_DEBUG_PUB.ADD('currency_code=: '||l_site_unchk_curr_tbl(k).usage_curr_code);
1403: END LOOP;
1404: --------------------------------------------------------------------------------
1405:
1406: -----calculate exposure
1407:
1408: ----pre-calculate exposure
1409: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1410: THEN
1411: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1412: OE_DEBUG_PUB.ADD('Parameters:');
1413: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1414: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1415: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1408: ----pre-calculate exposure
1409: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1410: THEN
1411: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1412: OE_DEBUG_PUB.ADD('Parameters:');
1413: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1414: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1415: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1416: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1409: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1410: THEN
1411: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1412: OE_DEBUG_PUB.ADD('Parameters:');
1413: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1414: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1415: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1416: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1417: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1410: THEN
1411: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1412: OE_DEBUG_PUB.ADD('Parameters:');
1413: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1414: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1415: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1416: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1417: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1418: /*
1411: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1412: OE_DEBUG_PUB.ADD('Parameters:');
1413: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1414: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1415: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1416: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1417: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1418: /*
1419: OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1412: OE_DEBUG_PUB.ADD('Parameters:');
1413: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1414: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1415: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1416: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1417: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1418: /*
1419: OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1420: ( p_customer_id => p_customer_id
1413: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1414: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1415: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1416: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1417: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1418: /*
1419: OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1420: ( p_customer_id => p_customer_id
1421: , p_site_use_id => p_site_id
1452: , x_error_curr_tbl => l_error_curr_tbl
1453: );
1454:
1455:
1456: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1457: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1458: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1459:
1460: IF l_error_curr_tbl.COUNT<>0
1453: );
1454:
1455:
1456: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1457: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1458: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1459:
1460: IF l_error_curr_tbl.COUNT<>0
1461: THEN
1454:
1455:
1456: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1457: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1458: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1459:
1460: IF l_error_curr_tbl.COUNT<>0
1461: THEN
1462: FOR f IN 1..l_error_curr_tbl.COUNT
1460: IF l_error_curr_tbl.COUNT<>0
1461: THEN
1462: FOR f IN 1..l_error_curr_tbl.COUNT
1463: LOOP
1464: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1465:
1466: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1467: base currency '||p_base_currency||' is missing for conversion type '||
1468: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1462: FOR f IN 1..l_error_curr_tbl.COUNT
1463: LOOP
1464: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1465:
1466: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1467: base currency '||p_base_currency||' is missing for conversion type '||
1468: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1469:
1470: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1466: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1467: base currency '||p_base_currency||' is missing for conversion type '||
1468: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1469:
1470: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1471:
1472: END LOOP;
1473:
1474: ---bug fix 2439029
1487:
1488: ----non pre-calculate exposure
1489: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1490: THEN
1491: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_CHECK_UTIL.Get_order_Exposure ');
1492: OE_DEBUG_PUB.ADD('Parameters:');
1493: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1494: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1495: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1488: ----non pre-calculate exposure
1489: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1490: THEN
1491: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_CHECK_UTIL.Get_order_Exposure ');
1492: OE_DEBUG_PUB.ADD('Parameters:');
1493: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1494: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1495: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1496: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1489: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1490: THEN
1491: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_CHECK_UTIL.Get_order_Exposure ');
1492: OE_DEBUG_PUB.ADD('Parameters:');
1493: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1494: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1495: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1496: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1497: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1490: THEN
1491: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_CHECK_UTIL.Get_order_Exposure ');
1492: OE_DEBUG_PUB.ADD('Parameters:');
1493: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1494: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1495: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1496: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1497: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1498: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1491: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_CHECK_UTIL.Get_order_Exposure ');
1492: OE_DEBUG_PUB.ADD('Parameters:');
1493: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1494: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1495: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1496: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1497: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1498: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1499: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1492: OE_DEBUG_PUB.ADD('Parameters:');
1493: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1494: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1495: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1496: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1497: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1498: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1499: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1500: /*
1493: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1494: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1495: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1496: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1497: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1498: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1499: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1500: /*
1501: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1494: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1495: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1496: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1497: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1498: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1499: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1500: /*
1501: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1502: ( p_header_id => NULL
1495: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1496: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1497: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1498: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1499: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1500: /*
1501: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1502: ( p_header_id => NULL
1503: , p_transaction_curr_code => NULL
1536: , x_return_status => l_return_status
1537: );
1538:
1539:
1540: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1541: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1542: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1543:
1544: IF l_conversion_status.COUNT<>0
1537: );
1538:
1539:
1540: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1541: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1542: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1543:
1544: IF l_conversion_status.COUNT<>0
1545: THEN
1538:
1539:
1540: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1541: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1542: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1543:
1544: IF l_conversion_status.COUNT<>0
1545: THEN
1546:
1545: THEN
1546:
1547: FOR f IN 1..l_conversion_status.COUNT
1548: LOOP
1549: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1550:
1551: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1552: base currency '||p_base_currency||' is missing for conversion type '||
1553: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1547: FOR f IN 1..l_conversion_status.COUNT
1548: LOOP
1549: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1550:
1551: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1552: base currency '||p_base_currency||' is missing for conversion type '||
1553: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1554:
1555: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1551: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1552: base currency '||p_base_currency||' is missing for conversion type '||
1553: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1554:
1555: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1556:
1557: END LOOP;
1558:
1559: ---bug fix 2439029
1573: END IF;
1574:
1575: END IF;
1576:
1577: OE_DEBUG_PUB.ADD('Out Get_unchecked_exposure with status='||x_return_status);
1578: OE_DEBUG_PUB.ADD('unchecked_exposure='||x_unchecked_expousre);
1579:
1580:
1581: EXCEPTION
1574:
1575: END IF;
1576:
1577: OE_DEBUG_PUB.ADD('Out Get_unchecked_exposure with status='||x_return_status);
1578: OE_DEBUG_PUB.ADD('unchecked_exposure='||x_unchecked_expousre);
1579:
1580:
1581: EXCEPTION
1582: WHEN OTHERS THEN
1921:
1922: BEGIN
1923:
1924:
1925: OE_DEBUG_PUB.ADD('IN OEXRCRCB:Credit_exposure_report_utils ');
1926: l_return_status := FND_API.G_RET_STS_SUCCESS;
1927: l_global_exposure_flag := 'N' ;
1928:
1929: ----get base currency=currency of the current operating unit
1928:
1929: ----get base currency=currency of the current operating unit
1930: l_base_currency:=OE_CREDIT_CHECK_UTIL.Get_GL_currency;
1931:
1932: OE_DEBUG_PUB.ADD('l_base_currency= '||l_base_currency);
1933:
1934: ---get conversion type
1935: SELECT
1936: conversion_type
1941:
1942: ----get the hierarchy_type
1943: l_profile_value:=OE_CREDIT_CHECK_UTIL.G_hierarchy_type;
1944:
1945: OE_DEBUG_PUB.ADD('AR_CMGT_HIERARCHY_TYPE= '||l_profile_value);
1946:
1947: --------Party Range Summary Report-----------
1948:
1949: IF p_report_by_option='PARTY_SUMMARY'
1948:
1949: IF p_report_by_option='PARTY_SUMMARY'
1950: THEN
1951:
1952: OE_DEBUG_PUB.ADD('IN Party Summary Report');
1953:
1954: ------start loop for parties
1955: IF (p_party_name_low IS NOT NULL) THEN
1956: l_input := 1;
1960: l_input := 3;
1961: ELSIF (p_party_number_high IS NOT NULL) THEN
1962: l_input := 4;
1963: ELSE
1964: OE_DEBUG_PUB.ADD(' no party input');
1965: l_input := 0;
1966: END IF;
1967:
1968: OE_DEBUG_PUB.ADD(' party input:'||l_input);
1964: OE_DEBUG_PUB.ADD(' no party input');
1965: l_input := 0;
1966: END IF;
1967:
1968: OE_DEBUG_PUB.ADD(' party input:'||l_input);
1969: IF (l_input > 0) THEN
1970: -- 5212830 FOR party_csr_rec IN party_csr(l_input)
1971:
1972: LOOP
2041: , x_return_status => l_return_status
2042: );
2043:
2044:
2045: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2046: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2047:
2048: IF l_return_status = 'C'
2049: THEN
2042: );
2043:
2044:
2045: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2046: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2047:
2048: IF l_return_status = 'C'
2049: THEN
2050: l_return_status1:='C';
2063: l_party_available:=party_prof_csr_rec.party_overall_limit - l_party_total_exposure;
2064:
2065:
2066: -----convert overall_credit_limit, exposure and available credit into base currency
2067: OE_DEBUG_PUB.ADD('IN Convert amounts ');
2068:
2069: l_base_cur_overall_limit :=
2070: GL_CURRENCY_API.Convert_closest_amount_sql
2071: ( x_from_currency => party_prof_csr_rec.party_currency_code
2076: , x_amount => party_prof_csr_rec.party_overall_limit
2077: , x_max_roll_days => -1
2078: );
2079:
2080: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2081: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2082:
2083: l_base_cur_exposure :=
2084: GL_CURRENCY_API.Convert_closest_amount_sql
2077: , x_max_roll_days => -1
2078: );
2079:
2080: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2081: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2082:
2083: l_base_cur_exposure :=
2084: GL_CURRENCY_API.Convert_closest_amount_sql
2085: ( x_from_currency => party_prof_csr_rec.party_currency_code
2090: , x_amount => l_party_total_exposure
2091: , x_max_roll_days => -1
2092: );
2093:
2094: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2095: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2096:
2097: l_base_cur_available :=
2098: GL_CURRENCY_API.Convert_closest_amount_sql
2091: , x_max_roll_days => -1
2092: );
2093:
2094: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2095: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2096:
2097: l_base_cur_available :=
2098: GL_CURRENCY_API.Convert_closest_amount_sql
2099: ( x_from_currency => party_prof_csr_rec.party_currency_code
2104: , x_amount => l_party_available
2105: , x_max_roll_days => -1
2106: );
2107:
2108: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2109: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2110: || TO_CHAR(l_base_cur_available ));
2111:
2112: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2105: , x_max_roll_days => -1
2106: );
2107:
2108: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2109: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2110: || TO_CHAR(l_base_cur_available ));
2111:
2112: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2113:
2108: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2109: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2110: || TO_CHAR(l_base_cur_available ));
2111:
2112: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2113:
2114: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2115:
2116: l_global_exposure_flag := 'Y' ;
2110: || TO_CHAR(l_base_cur_available ));
2111:
2112: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2113:
2114: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2115:
2116: l_global_exposure_flag := 'Y' ;
2117:
2118:
2115:
2116: l_global_exposure_flag := 'Y' ;
2117:
2118:
2119: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2120:
2121: -----insert data into temp table
2122: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2123: ( party_id
2165: , NULL
2166: , l_global_exposure_flag
2167: );
2168:
2169: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
2170:
2171: ----end loop for party credit profiles
2172: END LOOP;
2173:
2182: ----if global variable G_cust_incl_all_flag is 'Y'
2183: ----then unchecked exposure will be 0
2184: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
2185: THEN
2186: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2187: so l_party_unchk_exposure=0');
2188: l_party_unchk_exposure:=0;
2189: ELSE
2190: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
2186: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2187: so l_party_unchk_exposure=0');
2188: l_party_unchk_exposure:=0;
2189: ELSE
2190: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
2191: ||TO_CHAR(party_csr_rec.party_id));
2192: Get_unchecked_exposure
2193: ( p_party_id => party_csr_rec.party_id
2194: , p_customer_id => NULL
2199: , x_return_status => l_return_status
2200: );
2201: END IF;
2202:
2203: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for party: '
2204: ||TO_CHAR(party_csr_rec.party_id));
2205: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2206: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2207:
2201: END IF;
2202:
2203: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for party: '
2204: ||TO_CHAR(party_csr_rec.party_id));
2205: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2206: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2207:
2208: IF l_return_status = 'C'
2209: THEN
2202:
2203: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for party: '
2204: ||TO_CHAR(party_csr_rec.party_id));
2205: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2206: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2207:
2208: IF l_return_status = 'C'
2209: THEN
2210: l_return_status1:='C';
2218: THEN
2219: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2220: END IF;
2221:
2222: OE_DEBUG_PUB.ADD('Check if the party has any credit profiles ');
2223: -----check if the party has any credit profiles
2224: SELECT COUNT(*)
2225: INTO l_prof_count
2226: FROM
2233:
2234: IF l_prof_count>0
2235: THEN
2236:
2237: OE_DEBUG_PUB.ADD('Party has credit profiles ');
2238: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2239:
2240: ----update temp table with unchecked exposure
2241: UPDATE oe_credit_exposure_temp
2234: IF l_prof_count>0
2235: THEN
2236:
2237: OE_DEBUG_PUB.ADD('Party has credit profiles ');
2238: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2239:
2240: ----update temp table with unchecked exposure
2241: UPDATE oe_credit_exposure_temp
2242: SET unchecked_exposure=l_party_unchk_exposure
2246: -----there are no credit profiles for this party
2247: ----insert unchecked exposure
2248: ELSE
2249:
2250: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
2251: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2252:
2253: -----insert data into temp table
2254: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2247: ----insert unchecked exposure
2248: ELSE
2249:
2250: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
2251: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2252:
2253: -----insert data into temp table
2254: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2255: ( party_id
2297: , l_party_unchk_exposure
2298: , NULL
2299: );
2300:
2301: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
2302:
2303: END IF;
2304:
2305: END LOOP; ----end loop for parties
2338: ELSE
2339: l_specific_party_id:= p_spec_party_num_id;
2340: END IF;
2341:
2342: OE_DEBUG_PUB.ADD('IN Party Detail Report for party_id='||TO_CHAR(l_specific_party_id));
2343:
2344: ---Check if this party is part of Hierarchy AR_CMGT_HIERARCHY_TYPE
2345: SELECT COUNT(*)
2346: INTO l_count_hierarchy
2350: OR child_id=l_specific_party_id);
2351:
2352: IF l_count_hierarchy>0
2353: THEN
2354: OE_DEBUG_PUB.ADD('This party is part of the hierarchy '||l_profile_value);
2355: ------use cursor for hierarchical parties
2356:
2357: ------start loop for parties
2358: FOR party_hier_csr_rec IN party_hier_csr
2381: , x_return_status => l_return_status
2382: );
2383:
2384:
2385: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2386: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2387:
2388: IF l_return_status = 'C'
2389: THEN
2382: );
2383:
2384:
2385: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2386: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2387:
2388: IF l_return_status = 'C'
2389: THEN
2390: l_return_status1:='C';
2403: l_party_available:=party_prof_csr_rec.party_overall_limit - l_party_total_exposure;
2404:
2405:
2406: -----convert overall_credit_limit, exposure and available credit into base currency
2407: OE_DEBUG_PUB.ADD('IN Convert amounts ');
2408:
2409: l_base_cur_overall_limit :=
2410: GL_CURRENCY_API.Convert_closest_amount_sql
2411: ( x_from_currency => party_prof_csr_rec.party_currency_code
2416: , x_amount => party_prof_csr_rec.party_overall_limit
2417: , x_max_roll_days => -1
2418: );
2419:
2420: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2421: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2422:
2423: l_base_cur_exposure :=
2424: GL_CURRENCY_API.Convert_closest_amount_sql
2417: , x_max_roll_days => -1
2418: );
2419:
2420: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2421: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2422:
2423: l_base_cur_exposure :=
2424: GL_CURRENCY_API.Convert_closest_amount_sql
2425: ( x_from_currency => party_prof_csr_rec.party_currency_code
2430: , x_amount => l_party_total_exposure
2431: , x_max_roll_days => -1
2432: );
2433:
2434: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2435: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2436:
2437: l_base_cur_available :=
2438: GL_CURRENCY_API.Convert_closest_amount_sql
2431: , x_max_roll_days => -1
2432: );
2433:
2434: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2435: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2436:
2437: l_base_cur_available :=
2438: GL_CURRENCY_API.Convert_closest_amount_sql
2439: ( x_from_currency => party_prof_csr_rec.party_currency_code
2444: , x_amount => l_party_available
2445: , x_max_roll_days => -1
2446: );
2447:
2448: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2449: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2450: || TO_CHAR(l_base_cur_available ));
2451:
2452: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2445: , x_max_roll_days => -1
2446: );
2447:
2448: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2449: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2450: || TO_CHAR(l_base_cur_available ));
2451:
2452: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2453:
2448: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2449: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2450: || TO_CHAR(l_base_cur_available ));
2451:
2452: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2453:
2454: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2455:
2456: l_global_exposure_flag := 'Y' ;
2450: || TO_CHAR(l_base_cur_available ));
2451:
2452: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2453:
2454: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2455:
2456: l_global_exposure_flag := 'Y' ;
2457:
2458: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2454: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2455:
2456: l_global_exposure_flag := 'Y' ;
2457:
2458: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2459:
2460: -----insert data into temp table
2461: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2462: ( party_id
2504: , NULL
2505: , l_global_exposure_flag
2506: );
2507:
2508: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
2509:
2510: ----end loop for party credit profiles
2511: END LOOP;
2512:
2521: ----if global variable G_cust_incl_all_flag is 'Y'
2522: ----then unchecked exposure will be 0
2523: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
2524: THEN
2525: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2526: so l_party_unchk_exposure=0');
2527: l_party_unchk_exposure:=0;
2528: ELSE
2529: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
2525: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2526: so l_party_unchk_exposure=0');
2527: l_party_unchk_exposure:=0;
2528: ELSE
2529: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
2530: ||TO_CHAR(party_hier_csr_rec.party_id));
2531: Get_unchecked_exposure
2532: ( p_party_id => party_hier_csr_rec.party_id
2533: , p_customer_id => NULL
2538: , x_return_status => l_return_status
2539: );
2540: END IF;
2541:
2542: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure, for the party:'
2543: ||TO_CHAR(party_hier_csr_rec.party_id));
2544: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2545: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2546:
2540: END IF;
2541:
2542: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure, for the party:'
2543: ||TO_CHAR(party_hier_csr_rec.party_id));
2544: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2545: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2546:
2547: IF l_return_status = 'C'
2548: THEN
2541:
2542: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure, for the party:'
2543: ||TO_CHAR(party_hier_csr_rec.party_id));
2544: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2545: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2546:
2547: IF l_return_status = 'C'
2548: THEN
2549: l_return_status1:='C';
2557: THEN
2558: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2559: END IF;
2560:
2561: OE_DEBUG_PUB.ADD('Check if the party has any credit profiles ');
2562: -----check if the party has any credit profiles
2563: SELECT COUNT(*)
2564: INTO l_prof_count
2565: FROM
2572:
2573: IF l_prof_count>0
2574: THEN
2575:
2576: OE_DEBUG_PUB.ADD('Party has credit profiles ');
2577: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2578:
2579: ----update temp table with unchecked exposure
2580: UPDATE oe_credit_exposure_temp
2573: IF l_prof_count>0
2574: THEN
2575:
2576: OE_DEBUG_PUB.ADD('Party has credit profiles ');
2577: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2578:
2579: ----update temp table with unchecked exposure
2580: UPDATE oe_credit_exposure_temp
2581: SET unchecked_exposure=l_party_unchk_exposure
2585: -----there are no credit profiles for this party
2586: ----insert unchecked exposure
2587: ELSE
2588:
2589: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
2590: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2591:
2592: -----insert data into temp table
2593: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2586: ----insert unchecked exposure
2587: ELSE
2588:
2589: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
2590: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2591:
2592: -----insert data into temp table
2593: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2594: ( party_id
2636: , l_party_unchk_exposure
2637: , 'Y'
2638: );
2639:
2640: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
2641:
2642: END IF; ---end of checking if the party has credit profiles
2643:
2644: ------start report for cust accounts for the specific party
2642: END IF; ---end of checking if the party has credit profiles
2643:
2644: ------start report for cust accounts for the specific party
2645:
2646: OE_DEBUG_PUB.ADD('IN Cust accounts section of the Party Deatils Report');
2647:
2648: ------start loop for customers
2649: FOR party_cust_csr_rec IN party_cust_csr (p_party_id => party_hier_csr_rec.party_id)
2650: LOOP
2666: , x_return_status => l_return_status
2667: );
2668:
2669:
2670: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2671: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2672:
2673: IF l_return_status = 'C'
2674: THEN
2667: );
2668:
2669:
2670: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2671: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2672:
2673: IF l_return_status = 'C'
2674: THEN
2675: l_return_status1:='C';
2688: l_cust_available:=cust_prof_csr_rec.cust_overall_limit - l_cust_total_exposure;
2689:
2690:
2691: -----convert overall_credit_limit, exposure and available credit into base currency
2692: OE_DEBUG_PUB.ADD('IN Convert amounts ');
2693:
2694: l_base_cur_overall_limit :=
2695: GL_CURRENCY_API.Convert_closest_amount_sql
2696: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
2701: , x_amount => cust_prof_csr_rec.cust_overall_limit
2702: , x_max_roll_days => -1
2703: );
2704:
2705: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2706: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2707:
2708: l_base_cur_exposure :=
2709: GL_CURRENCY_API.Convert_closest_amount_sql
2702: , x_max_roll_days => -1
2703: );
2704:
2705: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2706: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2707:
2708: l_base_cur_exposure :=
2709: GL_CURRENCY_API.Convert_closest_amount_sql
2710: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
2715: , x_amount => l_cust_total_exposure
2716: , x_max_roll_days => -1
2717: );
2718:
2719: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2720: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2721:
2722: l_base_cur_available :=
2723: GL_CURRENCY_API.Convert_closest_amount_sql
2716: , x_max_roll_days => -1
2717: );
2718:
2719: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2720: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2721:
2722: l_base_cur_available :=
2723: GL_CURRENCY_API.Convert_closest_amount_sql
2724: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
2729: , x_amount => l_cust_available
2730: , x_max_roll_days => -1
2731: );
2732:
2733: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2734: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2735: || TO_CHAR(l_base_cur_available ));
2736:
2737: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2730: , x_max_roll_days => -1
2731: );
2732:
2733: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2734: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2735: || TO_CHAR(l_base_cur_available ));
2736:
2737: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2738:
2733: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2734: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2735: || TO_CHAR(l_base_cur_available ));
2736:
2737: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2738:
2739: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
2740:
2741: l_global_exposure_flag :=
2735: || TO_CHAR(l_base_cur_available ));
2736:
2737: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2738:
2739: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
2740:
2741: l_global_exposure_flag :=
2742: OE_CREDIT_CHECK_UTIL.Get_global_exposure_flag
2743: ( p_entity_type => 'CUSTOMER'
2744: , p_entity_id => party_cust_csr_rec.customer_id
2745: , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
2746: ) ;
2747:
2748: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
2749: l_global_exposure_flag );
2750:
2751: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2752:
2747:
2748: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
2749: l_global_exposure_flag );
2750:
2751: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2752:
2753: -----insert data into temp table
2754: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2755: ( party_id
2797: , NULL
2798: , l_global_exposure_flag
2799: );
2800:
2801: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
2802:
2803: ----end loop for customer credit profiles
2804: END LOOP;
2805:
2816: ----then unchecked exposure will be 0
2817:
2818: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
2819: THEN
2820: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2821: so l_cust_unchk_exposure=0');
2822: l_cust_unchk_exposure:=0;
2823: ELSE
2824:
2821: so l_cust_unchk_exposure=0');
2822: l_cust_unchk_exposure:=0;
2823: ELSE
2824:
2825: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the customer '
2826: ||TO_CHAR(party_cust_csr_rec.customer_id));
2827:
2828: Get_unchecked_exposure
2829: ( p_party_id => NULL
2835: , x_return_status => l_return_status
2836: );
2837: END IF;
2838:
2839: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer: '
2840: ||TO_CHAR(party_cust_csr_rec.customer_id));
2841: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure) );
2842: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2843:
2837: END IF;
2838:
2839: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer: '
2840: ||TO_CHAR(party_cust_csr_rec.customer_id));
2841: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure) );
2842: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2843:
2844: IF l_return_status = 'C'
2845: THEN
2838:
2839: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer: '
2840: ||TO_CHAR(party_cust_csr_rec.customer_id));
2841: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure) );
2842: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2843:
2844: IF l_return_status = 'C'
2845: THEN
2846: l_return_status1:='C';
2854: THEN
2855: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2856: END IF;
2857:
2858: OE_DEBUG_PUB.ADD('Check if the customer has any credit profiles ');
2859: -----check if the customer has any credit profiles
2860: SELECT COUNT(*)
2861: INTO l_prof_count
2862: FROM
2868:
2869: IF l_prof_count>0
2870: THEN
2871:
2872: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
2873: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2874:
2875: ----update temp table with unchecked exposure
2876: UPDATE oe_credit_exposure_temp
2869: IF l_prof_count>0
2870: THEN
2871:
2872: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
2873: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2874:
2875: ----update temp table with unchecked exposure
2876: UPDATE oe_credit_exposure_temp
2877: SET unchecked_exposure=l_cust_unchk_exposure
2883: -----there are no credit profiles for this customer
2884: ----insert unchecked exposure
2885: ELSE
2886:
2887: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
2888: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2889:
2890: -----insert data into temp table
2891: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2884: ----insert unchecked exposure
2885: ELSE
2886:
2887: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
2888: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2889:
2890: -----insert data into temp table
2891: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2892: ( party_id
2934: , l_cust_unchk_exposure
2935: , 'Y'
2936: );
2937:
2938: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
2939:
2940: END IF; --end of checking if the customer has credit profile
2941:
2942: ----end loop for customers
2949:
2950:
2951: -----party is not part of the hierarchy
2952: ELSE
2953: OE_DEBUG_PUB.ADD('This party is not part of the hierarchy '||l_profile_value);
2954: ------use cursor for non hierarchical parties
2955:
2956: ------start loop for parties
2957: FOR party_hier_csr1_rec IN party_hier_csr1
2980: , x_return_status => l_return_status
2981: );
2982:
2983:
2984: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2985: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2986:
2987: IF l_return_status = 'C'
2988: THEN
2981: );
2982:
2983:
2984: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2985: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2986:
2987: IF l_return_status = 'C'
2988: THEN
2989: l_return_status1:='C';
3002: l_party_available:=party_prof_csr_rec.party_overall_limit - l_party_total_exposure;
3003:
3004:
3005: -----convert overall_credit_limit, exposure and available credit into base currency
3006: OE_DEBUG_PUB.ADD('IN Convert amounts ');
3007:
3008: l_base_cur_overall_limit :=
3009: GL_CURRENCY_API.Convert_closest_amount_sql
3010: ( x_from_currency => party_prof_csr_rec.party_currency_code
3015: , x_amount => party_prof_csr_rec.party_overall_limit
3016: , x_max_roll_days => -1
3017: );
3018:
3019: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3020: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3021:
3022: l_base_cur_exposure :=
3023: GL_CURRENCY_API.Convert_closest_amount_sql
3016: , x_max_roll_days => -1
3017: );
3018:
3019: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3020: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3021:
3022: l_base_cur_exposure :=
3023: GL_CURRENCY_API.Convert_closest_amount_sql
3024: ( x_from_currency => party_prof_csr_rec.party_currency_code
3029: , x_amount => l_party_total_exposure
3030: , x_max_roll_days => -1
3031: );
3032:
3033: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3034: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3035:
3036: l_base_cur_available :=
3037: GL_CURRENCY_API.Convert_closest_amount_sql
3030: , x_max_roll_days => -1
3031: );
3032:
3033: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3034: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3035:
3036: l_base_cur_available :=
3037: GL_CURRENCY_API.Convert_closest_amount_sql
3038: ( x_from_currency => party_prof_csr_rec.party_currency_code
3043: , x_amount => l_party_available
3044: , x_max_roll_days => -1
3045: );
3046:
3047: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3048: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3049: || TO_CHAR(l_base_cur_available ));
3050:
3051: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3044: , x_max_roll_days => -1
3045: );
3046:
3047: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3048: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3049: || TO_CHAR(l_base_cur_available ));
3050:
3051: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3052:
3047: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3048: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3049: || TO_CHAR(l_base_cur_available ));
3050:
3051: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3052:
3053: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
3054:
3055: l_global_exposure_flag := 'Y' ;
3049: || TO_CHAR(l_base_cur_available ));
3050:
3051: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3052:
3053: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
3054:
3055: l_global_exposure_flag := 'Y' ;
3056:
3057: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3053: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
3054:
3055: l_global_exposure_flag := 'Y' ;
3056:
3057: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3058:
3059: -----insert data into temp table
3060: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3061: ( party_id
3103: , NULL
3104: , l_global_exposure_flag
3105: );
3106:
3107: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
3108:
3109: ----end loop for party credit profiles
3110: END LOOP;
3111:
3120: ----if global variable G_cust_incl_all_flag is 'Y'
3121: ----then unchecked exposure will be 0
3122: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
3123: THEN
3124: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
3125: so l_party_unchk_exposure=0');
3126:
3127: l_party_unchk_exposure:=0;
3128: ELSE
3125: so l_party_unchk_exposure=0');
3126:
3127: l_party_unchk_exposure:=0;
3128: ELSE
3129: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
3130: ||TO_CHAR(party_hier_csr1_rec.party_id));
3131:
3132: Get_unchecked_exposure
3133: ( p_party_id => party_hier_csr1_rec.party_id
3139: , x_return_status => l_return_status
3140: );
3141: END IF;
3142:
3143: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for the party: '
3144: ||TO_CHAR(party_hier_csr1_rec.party_id));
3145: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure) );
3146: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3147:
3141: END IF;
3142:
3143: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for the party: '
3144: ||TO_CHAR(party_hier_csr1_rec.party_id));
3145: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure) );
3146: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3147:
3148: IF l_return_status = 'C'
3149: THEN
3142:
3143: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for the party: '
3144: ||TO_CHAR(party_hier_csr1_rec.party_id));
3145: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure) );
3146: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3147:
3148: IF l_return_status = 'C'
3149: THEN
3150: l_return_status1:='C';
3158: THEN
3159: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3160: END IF;
3161:
3162: OE_DEBUG_PUB.ADD('Check if the party has any credit profiles ');
3163: -----check if the party has any credit profiles
3164: SELECT COUNT(*)
3165: INTO l_prof_count
3166: FROM
3173:
3174: IF l_prof_count>0
3175: THEN
3176:
3177: OE_DEBUG_PUB.ADD('Party has credit profiles ');
3178: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3179:
3180: ----update temp table with unchecked exposure
3181: UPDATE oe_credit_exposure_temp
3174: IF l_prof_count>0
3175: THEN
3176:
3177: OE_DEBUG_PUB.ADD('Party has credit profiles ');
3178: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3179:
3180: ----update temp table with unchecked exposure
3181: UPDATE oe_credit_exposure_temp
3182: SET unchecked_exposure=l_party_unchk_exposure
3186: -----there are no credit profiles for this party
3187: ----insert unchecked exposure
3188: ELSE
3189:
3190: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
3191: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3192:
3193: -----insert data into temp table
3194: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3187: ----insert unchecked exposure
3188: ELSE
3189:
3190: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
3191: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3192:
3193: -----insert data into temp table
3194: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3195: ( party_id
3237: , l_party_unchk_exposure
3238: , 'Y'
3239: );
3240:
3241: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
3242:
3243: END IF; ---end of checking if the party has credit profiles
3244:
3245: ------start report for cust accounts for the specific party
3243: END IF; ---end of checking if the party has credit profiles
3244:
3245: ------start report for cust accounts for the specific party
3246:
3247: OE_DEBUG_PUB.ADD('IN Cust accounts section of the Party Details Report');
3248: OE_DEBUG_PUB.ADD('party_hier_csr1_rec.party_id ==> '||
3249: party_hier_csr1_rec.party_id );
3250:
3251: ------start loop for customers
3244:
3245: ------start report for cust accounts for the specific party
3246:
3247: OE_DEBUG_PUB.ADD('IN Cust accounts section of the Party Details Report');
3248: OE_DEBUG_PUB.ADD('party_hier_csr1_rec.party_id ==> '||
3249: party_hier_csr1_rec.party_id );
3250:
3251: ------start loop for customers
3252: FOR party_cust_csr_rec IN party_cust_csr (p_party_id => party_hier_csr1_rec.party_id)
3269: , x_return_status => l_return_status
3270: );
3271:
3272:
3273: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
3274: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3275:
3276: IF l_return_status = 'C'
3277: THEN
3270: );
3271:
3272:
3273: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
3274: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3275:
3276: IF l_return_status = 'C'
3277: THEN
3278: l_return_status1:='C';
3291: l_cust_available:=cust_prof_csr_rec.cust_overall_limit - l_cust_total_exposure;
3292:
3293:
3294: -----convert overall_credit_limit, exposure and available credit into base currency
3295: OE_DEBUG_PUB.ADD('IN Convert amounts ');
3296:
3297: l_base_cur_overall_limit :=
3298: GL_CURRENCY_API.Convert_closest_amount_sql
3299: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3304: , x_amount => cust_prof_csr_rec.cust_overall_limit
3305: , x_max_roll_days => -1
3306: );
3307:
3308: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3309: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3310:
3311: l_base_cur_exposure :=
3312: GL_CURRENCY_API.Convert_closest_amount_sql
3305: , x_max_roll_days => -1
3306: );
3307:
3308: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3309: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3310:
3311: l_base_cur_exposure :=
3312: GL_CURRENCY_API.Convert_closest_amount_sql
3313: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3318: , x_amount => l_cust_total_exposure
3319: , x_max_roll_days => -1
3320: );
3321:
3322: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3323: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3324:
3325: l_base_cur_available :=
3326: GL_CURRENCY_API.Convert_closest_amount_sql
3319: , x_max_roll_days => -1
3320: );
3321:
3322: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3323: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3324:
3325: l_base_cur_available :=
3326: GL_CURRENCY_API.Convert_closest_amount_sql
3327: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3332: , x_amount => l_cust_available
3333: , x_max_roll_days => -1
3334: );
3335:
3336: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3337: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3338: || TO_CHAR(l_base_cur_available ));
3339:
3340: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3333: , x_max_roll_days => -1
3334: );
3335:
3336: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3337: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3338: || TO_CHAR(l_base_cur_available ));
3339:
3340: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3341:
3336: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3337: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3338: || TO_CHAR(l_base_cur_available ));
3339:
3340: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3341:
3342: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
3343:
3344: l_global_exposure_flag :=
3338: || TO_CHAR(l_base_cur_available ));
3339:
3340: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3341:
3342: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
3343:
3344: l_global_exposure_flag :=
3345: OE_CREDIT_CHECK_UTIL.Get_global_exposure_flag
3346: ( p_entity_type => 'CUSTOMER'
3347: , p_entity_id => party_cust_csr_rec.customer_id
3348: , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
3349: ) ;
3350:
3351: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
3352: l_global_exposure_flag );
3353:
3354: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3355:
3350:
3351: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
3352: l_global_exposure_flag );
3353:
3354: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3355:
3356: -----insert data into temp table
3357: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3358: ( party_id
3400: , NULL
3401: , 'Y'
3402: );
3403:
3404: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
3405:
3406: ----end loop for customer credit profiles
3407: END LOOP;
3408:
3417: ----if global variable G_cust_incl_all_flag is 'Y'
3418: ----then unchecked exposure will be 0
3419: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
3420: THEN
3421: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
3422: so l_cust_unchk_exposure=0');
3423:
3424: l_cust_unchk_exposure:=0;
3425: ELSE
3422: so l_cust_unchk_exposure=0');
3423:
3424: l_cust_unchk_exposure:=0;
3425: ELSE
3426: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the customer='
3427: ||TO_CHAR(party_cust_csr_rec.customer_id));
3428:
3429: Get_unchecked_exposure
3430: ( p_party_id => NULL
3436: , x_return_status => l_return_status
3437: );
3438: END IF;
3439:
3440: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer='
3441: ||TO_CHAR(party_cust_csr_rec.customer_id) );
3442: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3443: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3444:
3438: END IF;
3439:
3440: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer='
3441: ||TO_CHAR(party_cust_csr_rec.customer_id) );
3442: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3443: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3444:
3445: IF l_return_status = 'C'
3446: THEN
3439:
3440: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer='
3441: ||TO_CHAR(party_cust_csr_rec.customer_id) );
3442: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3443: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3444:
3445: IF l_return_status = 'C'
3446: THEN
3447: l_return_status1:='C';
3455: THEN
3456: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3457: END IF;
3458:
3459: OE_DEBUG_PUB.ADD('Check if the customer has any credit profiles ');
3460: -----check if the customer has any credit profiles
3461: SELECT COUNT(*)
3462: INTO l_prof_count
3463: FROM
3469:
3470: IF l_prof_count>0
3471: THEN
3472:
3473: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
3474: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3475:
3476: ----update temp table with unchecked exposure
3477: UPDATE oe_credit_exposure_temp
3470: IF l_prof_count>0
3471: THEN
3472:
3473: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
3474: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3475:
3476: ----update temp table with unchecked exposure
3477: UPDATE oe_credit_exposure_temp
3478: SET unchecked_exposure=l_cust_unchk_exposure
3484: -----there are no credit profiles for this customer
3485: ----insert unchecked exposure
3486: ELSE
3487:
3488: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
3489: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3490:
3491: -----insert data into temp table
3492: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3485: ----insert unchecked exposure
3486: ELSE
3487:
3488: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
3489: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3490:
3491: -----insert data into temp table
3492: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3493: ( party_id
3535: , l_cust_unchk_exposure
3536: , 'Y'
3537: );
3538:
3539: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
3540:
3541: END IF; --end of checking if the customer has credit profile
3542:
3543: ----end loop for customers
3544: END LOOP;
3545:
3546: END LOOP; ----end loop for parties
3547:
3548: OE_DEBUG_PUB.ADD('Out Parties Loop 1 ');
3549:
3550: END IF; ---end of cheking if the party is part of the hierarchy
3551:
3552: -------- Customer Summary Report-----------
3554: ELSIF p_report_by_option='CUST_SUMMARY'
3555: OR p_report_by_option='CUST_DETAILS'
3556: THEN
3557:
3558: OE_DEBUG_PUB.ADD('IN Cust Summary Report');
3559:
3560: --Performance issue: start (SQL ID-16485806 FTS on HZ_CUST_ACCOUNTS and HZ_CUST_PROFILE_CLASSES)
3561: IF (p_customer_name_low IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3562: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3558: OE_DEBUG_PUB.ADD('IN Cust Summary Report');
3559:
3560: --Performance issue: start (SQL ID-16485806 FTS on HZ_CUST_ACCOUNTS and HZ_CUST_PROFILE_CLASSES)
3561: IF (p_customer_name_low IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3562: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3563: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3564:
3565: OPEN cust_cur FOR
3566: SELECT
3559:
3560: --Performance issue: start (SQL ID-16485806 FTS on HZ_CUST_ACCOUNTS and HZ_CUST_PROFILE_CLASSES)
3561: IF (p_customer_name_low IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3562: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3563: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3564:
3565: OPEN cust_cur FOR
3566: SELECT
3567: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3581: WHERE cp.profile_class_id=cpc.profile_class_id
3582: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3583: AND NVL(p_prof_class_high, cpc.name)) ;
3584: ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3585: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3586: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3587:
3588: OPEN cust_cur FOR
3589: SELECT
3582: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3583: AND NVL(p_prof_class_high, cpc.name)) ;
3584: ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3585: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3586: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3587:
3588: OPEN cust_cur FOR
3589: SELECT
3590: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3603: WHERE cp.profile_class_id=cpc.profile_class_id
3604: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3605: AND NVL(p_prof_class_high, cpc.name)) ;
3606: ELSIF (p_customer_name_low IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3607: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3608: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3609:
3610: OPEN cust_cur FOR
3611: SELECT
3604: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3605: AND NVL(p_prof_class_high, cpc.name)) ;
3606: ELSIF (p_customer_name_low IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3607: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3608: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3609:
3610: OPEN cust_cur FOR
3611: SELECT
3612: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3625: WHERE cp.profile_class_id=cpc.profile_class_id
3626: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3627: AND NVL(p_prof_class_high, cpc.name)) ;
3628: ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3629: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3630: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3631:
3632: OPEN cust_cur FOR
3633: SELECT
3626: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3627: AND NVL(p_prof_class_high, cpc.name)) ;
3628: ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3629: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3630: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3631:
3632: OPEN cust_cur FOR
3633: SELECT
3634: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3646: WHERE cp.profile_class_id=cpc.profile_class_id
3647: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3648: AND NVL(p_prof_class_high, cpc.name)) ;
3649: ELSIF (p_customer_name_low IS NOT NULL) THEN
3650: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3651:
3652: OPEN cust_cur FOR
3653: SELECT
3654: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3666: WHERE cp.profile_class_id=cpc.profile_class_id
3667: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3668: AND NVL(p_prof_class_high, cpc.name)) ;
3669: ELSIF (p_customer_name_high IS NOT NULL) THEN
3670: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3671:
3672: OPEN cust_cur FOR
3673: SELECT
3674: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3685: WHERE cp.profile_class_id=cpc.profile_class_id
3686: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3687: AND NVL(p_prof_class_high, cpc.name)) ;
3688: ELSIF (p_cust_number_low IS NOT NULL) THEN
3689: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3690:
3691: OPEN cust_cur FOR
3692: SELECT
3693: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3705: WHERE cp.profile_class_id=cpc.profile_class_id
3706: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3707: AND NVL(p_prof_class_high, cpc.name)) ;
3708: ELSIF (p_cust_number_high IS NOT NULL) THEN
3709: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3710:
3711: OPEN cust_cur FOR
3712: SELECT
3713: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3725: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3726: AND NVL(p_prof_class_high, cpc.name)) ;
3727: ELSIF (p_customer_name_low IS NULL) AND (p_customer_name_high IS NULL) AND
3728: (p_cust_number_high IS NULL) AND (p_cust_number_low IS NULL) THEN
3729: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3730: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3731: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3732: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3733:
3726: AND NVL(p_prof_class_high, cpc.name)) ;
3727: ELSIF (p_customer_name_low IS NULL) AND (p_customer_name_high IS NULL) AND
3728: (p_cust_number_high IS NULL) AND (p_cust_number_low IS NULL) THEN
3729: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3730: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3731: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3732: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3733:
3734: OPEN cust_cur FOR
3727: ELSIF (p_customer_name_low IS NULL) AND (p_customer_name_high IS NULL) AND
3728: (p_cust_number_high IS NULL) AND (p_cust_number_low IS NULL) THEN
3729: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3730: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3731: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3732: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3733:
3734: OPEN cust_cur FOR
3735: SELECT
3728: (p_cust_number_high IS NULL) AND (p_cust_number_low IS NULL) THEN
3729: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3730: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3731: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3732: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3733:
3734: OPEN cust_cur FOR
3735: SELECT
3736: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3770: , x_total_exposure => l_cust_total_exposure
3771: , x_return_status => l_return_status
3772: );
3773:
3774: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
3775: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3776:
3777: IF l_return_status = 'C'
3778: THEN
3771: , x_return_status => l_return_status
3772: );
3773:
3774: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
3775: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3776:
3777: IF l_return_status = 'C'
3778: THEN
3779: l_return_status1:='C';
3791: -----calculate available credit
3792: l_cust_available:=cust_prof_csr_rec.cust_overall_limit - l_cust_total_exposure;
3793:
3794: -----convert overall_credit_limit, exposure and available credit into base currency
3795: OE_DEBUG_PUB.ADD('IN Convert amounts ');
3796:
3797: l_base_cur_overall_limit :=
3798: GL_CURRENCY_API.Convert_closest_amount_sql
3799: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3804: , x_amount => cust_prof_csr_rec.cust_overall_limit
3805: , x_max_roll_days => -1
3806: );
3807:
3808: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3809: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3810:
3811: l_base_cur_exposure :=
3812: GL_CURRENCY_API.Convert_closest_amount_sql
3805: , x_max_roll_days => -1
3806: );
3807:
3808: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3809: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3810:
3811: l_base_cur_exposure :=
3812: GL_CURRENCY_API.Convert_closest_amount_sql
3813: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3818: , x_amount => l_cust_total_exposure
3819: , x_max_roll_days => -1
3820: );
3821:
3822: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3823: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3824:
3825: l_base_cur_available :=
3826: GL_CURRENCY_API.Convert_closest_amount_sql
3819: , x_max_roll_days => -1
3820: );
3821:
3822: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3823: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3824:
3825: l_base_cur_available :=
3826: GL_CURRENCY_API.Convert_closest_amount_sql
3827: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3832: , x_amount => l_cust_available
3833: , x_max_roll_days => -1
3834: );
3835:
3836: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3837: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3838: || TO_CHAR(l_base_cur_available ));
3839:
3840: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3833: , x_max_roll_days => -1
3834: );
3835:
3836: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3837: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3838: || TO_CHAR(l_base_cur_available ));
3839:
3840: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3841:
3836: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3837: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3838: || TO_CHAR(l_base_cur_available ));
3839:
3840: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3841:
3842: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
3843:
3844: l_global_exposure_flag :=
3838: || TO_CHAR(l_base_cur_available ));
3839:
3840: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3841:
3842: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
3843:
3844: l_global_exposure_flag :=
3845: OE_CREDIT_CHECK_UTIL.Get_global_exposure_flag
3846: ( p_entity_type => 'CUSTOMER'
3848: , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
3849: ) ;
3850:
3851:
3852: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
3853: l_global_exposure_flag );
3854:
3855: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3856:
3851:
3852: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
3853: l_global_exposure_flag );
3854:
3855: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3856:
3857: -----insert data into temp table
3858: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3859: ( party_id
3901: , NULL
3902: , l_global_exposure_flag
3903: );
3904:
3905: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
3906:
3907: ----end loop for customer credit profiles
3908: END LOOP;
3909:
3918: ----if global variable G_cust_incl_all_flag is 'Y'
3919: ----then unchecked exposure will be 0
3920: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
3921: THEN
3922: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
3923: so l_cust_unchk_exposure=0');
3924:
3925: l_cust_unchk_exposure:=0;
3926: ELSE
3923: so l_cust_unchk_exposure=0');
3924:
3925: l_cust_unchk_exposure:=0;
3926: ELSE
3927: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the customer= '
3928: ||TO_CHAR(cust_csr_rec.customer_id));
3929:
3930: Get_unchecked_exposure
3931: ( p_party_id => NULL
3937: , x_return_status => l_return_status
3938: );
3939: END IF;
3940:
3941: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer= '
3942: ||TO_CHAR(cust_csr_rec.customer_id));
3943: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3944: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3945:
3939: END IF;
3940:
3941: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer= '
3942: ||TO_CHAR(cust_csr_rec.customer_id));
3943: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3944: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3945:
3946: IF l_return_status = 'C'
3947: THEN
3940:
3941: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer= '
3942: ||TO_CHAR(cust_csr_rec.customer_id));
3943: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3944: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3945:
3946: IF l_return_status = 'C'
3947: THEN
3948: l_return_status1:='C';
3956: THEN
3957: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3958: END IF;
3959:
3960: OE_DEBUG_PUB.ADD('Check if the customer has any credit profiles ');
3961: -----check if the customer has any credit profiles
3962: SELECT COUNT(*)
3963: INTO l_prof_count
3964: FROM
3970:
3971: IF l_prof_count>0
3972: THEN
3973:
3974: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
3975: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3976:
3977: ----update temp table with unchecked exposure
3978: UPDATE oe_credit_exposure_temp
3971: IF l_prof_count>0
3972: THEN
3973:
3974: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
3975: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3976:
3977: ----update temp table with unchecked exposure
3978: UPDATE oe_credit_exposure_temp
3979: SET unchecked_exposure=l_cust_unchk_exposure
3984: -----there are no credit profiles for this customer
3985: ----insert unchecked exposure
3986: ELSE
3987:
3988: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
3989: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3990:
3991: -----insert data into temp table
3992: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3985: ----insert unchecked exposure
3986: ELSE
3987:
3988: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
3989: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3990:
3991: -----insert data into temp table
3992: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3993: ( party_id
4035: , l_cust_unchk_exposure
4036: , NULL
4037: );
4038:
4039: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
4040:
4041: END IF;
4042:
4043: IF p_report_by_option='CUST_DETAILS'
4042:
4043: IF p_report_by_option='CUST_DETAILS'
4044: THEN
4045:
4046: OE_DEBUG_PUB.ADD('IN Customern Detail Report');
4047: --------Detail Report------------
4048:
4049: -------------------Bill-to Site Info-------------
4050: -----start loop for bill-to sites
4059: ----start loop for bill-to site credit profiles
4060: FOR site_prof_csr_rec IN site_prof_csr( p_customer_id => cust_csr_rec.customer_id
4061: , p_site_id => site_csr_rec.site_id)
4062: LOOP
4063: OE_DEBUG_PUB.ADD('IN loop for bill-to sites ');
4064: OE_DEBUG_PUB.ADD('Processing site '||site_csr_rec.Customer_site);
4065:
4066: ------calculate bill-to site credit exposure
4067: OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
4060: FOR site_prof_csr_rec IN site_prof_csr( p_customer_id => cust_csr_rec.customer_id
4061: , p_site_id => site_csr_rec.site_id)
4062: LOOP
4063: OE_DEBUG_PUB.ADD('IN loop for bill-to sites ');
4064: OE_DEBUG_PUB.ADD('Processing site '||site_csr_rec.Customer_site);
4065:
4066: ------calculate bill-to site credit exposure
4067: OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
4068: ( p_customer_id => cust_csr_rec.customer_id
4073: , x_return_status => l_return_status
4074: );
4075:
4076:
4077: OE_DEBUG_PUB.ADD('OUT of Get_Customer_Exposure ');
4078: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4079: OE_DEBUG_PUB.ADD('l_site_total_exposure = '||TO_CHAR(l_site_total_exposure));
4080:
4081: IF l_return_status = 'C'
4074: );
4075:
4076:
4077: OE_DEBUG_PUB.ADD('OUT of Get_Customer_Exposure ');
4078: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4079: OE_DEBUG_PUB.ADD('l_site_total_exposure = '||TO_CHAR(l_site_total_exposure));
4080:
4081: IF l_return_status = 'C'
4082: THEN
4075:
4076:
4077: OE_DEBUG_PUB.ADD('OUT of Get_Customer_Exposure ');
4078: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4079: OE_DEBUG_PUB.ADD('l_site_total_exposure = '||TO_CHAR(l_site_total_exposure));
4080:
4081: IF l_return_status = 'C'
4082: THEN
4083: l_return_status1:='C';
4106: , x_amount => site_prof_csr_rec.site_overall_limit
4107: , x_max_roll_days => -1
4108: );
4109:
4110: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4111: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
4112:
4113:
4114: l_base_cur_exposure :=
4107: , x_max_roll_days => -1
4108: );
4109:
4110: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4111: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
4112:
4113:
4114: l_base_cur_exposure :=
4115: GL_CURRENCY_API.Convert_closest_amount_sql
4121: , x_amount => l_site_total_exposure
4122: , x_max_roll_days => -1
4123: );
4124:
4125: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4126: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
4127:
4128:
4129: l_base_cur_available :=
4122: , x_max_roll_days => -1
4123: );
4124:
4125: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4126: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
4127:
4128:
4129: l_base_cur_available :=
4130: GL_CURRENCY_API.Convert_closest_amount_sql
4136: , x_amount => l_site_available
4137: , x_max_roll_days => -1
4138: );
4139:
4140: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4141: OE_DEBUG_PUB.ADD('l_base_cur_available = '|| TO_CHAR(l_base_cur_available));
4142:
4143: -----insert data into temp table
4144: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4137: , x_max_roll_days => -1
4138: );
4139:
4140: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4141: OE_DEBUG_PUB.ADD('l_base_cur_available = '|| TO_CHAR(l_base_cur_available));
4142:
4143: -----insert data into temp table
4144: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4145: ( party_id
4196: ----then unchecked_exposure will be 0
4197:
4198: IF OE_Credit_Engine_GRP.G_site_incl_all_flag='Y'
4199: THEN
4200: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_site_incl_all_flag='
4201: ||OE_Credit_Engine_GRP.G_site_incl_all_flag
4202: ||'so l_site_unchk_exposure=0');
4203:
4204: l_site_unchk_exposure:=0;
4202: ||'so l_site_unchk_exposure=0');
4203:
4204: l_site_unchk_exposure:=0;
4205: ELSE
4206: OE_DEBUG_PUB.ADD('Start Get_unchecked_exposure for bill-to site ='
4207: ||TO_CHAR(site_csr_rec.site_id));
4208:
4209: Get_unchecked_exposure
4210: ( p_party_id => NULL
4216: , x_return_status => l_return_status
4217: );
4218: END IF;
4219:
4220: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for bill-to site '||
4221: TO_CHAR(site_csr_rec.site_id));
4222: OE_DEBUG_PUB.ADD('l_site_unchk_exposure = '|| TO_CHAR(l_site_unchk_exposure) );
4223: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4224:
4218: END IF;
4219:
4220: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for bill-to site '||
4221: TO_CHAR(site_csr_rec.site_id));
4222: OE_DEBUG_PUB.ADD('l_site_unchk_exposure = '|| TO_CHAR(l_site_unchk_exposure) );
4223: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4224:
4225: IF l_return_status = 'C'
4226: THEN
4219:
4220: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for bill-to site '||
4221: TO_CHAR(site_csr_rec.site_id));
4222: OE_DEBUG_PUB.ADD('l_site_unchk_exposure = '|| TO_CHAR(l_site_unchk_exposure) );
4223: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4224:
4225: IF l_return_status = 'C'
4226: THEN
4227: l_return_status1:='C';
4235: THEN
4236: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4237: END IF;
4238:
4239: OE_DEBUG_PUB.ADD('Check if the customer site has any credit profiles ');
4240:
4241: -----check if the customer site has any credit profiles
4242: SELECT COUNT(*)
4243: INTO l_prof_count1
4250:
4251: IF l_prof_count1>0
4252: THEN
4253:
4254: OE_DEBUG_PUB.ADD('Customer site has credit profiles ');
4255: OE_DEBUG_PUB.ADD('Update temp table with unchecked_exposure ');
4256:
4257: ----update temp table with unchecked exposure for bill-to site
4258: UPDATE oe_credit_exposure_temp
4251: IF l_prof_count1>0
4252: THEN
4253:
4254: OE_DEBUG_PUB.ADD('Customer site has credit profiles ');
4255: OE_DEBUG_PUB.ADD('Update temp table with unchecked_exposure ');
4256:
4257: ----update temp table with unchecked exposure for bill-to site
4258: UPDATE oe_credit_exposure_temp
4259: SET unchecked_exposure=l_site_unchk_exposure
4263: -----there are no credit profiles for this customer site
4264: -----insert unchecked exposure into temp table
4265: ELSE
4266:
4267: OE_DEBUG_PUB.ADD('Customer site does not have credit profiles ');
4268: OE_DEBUG_PUB.ADD('Insert into temp table unchecked_exposure ');
4269:
4270: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4271: ( party_id
4264: -----insert unchecked exposure into temp table
4265: ELSE
4266:
4267: OE_DEBUG_PUB.ADD('Customer site does not have credit profiles ');
4268: OE_DEBUG_PUB.ADD('Insert into temp table unchecked_exposure ');
4269:
4270: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4271: ( party_id
4272: , party_name
4313: , l_site_unchk_exposure
4314: , 'N'
4315: );
4316:
4317: OE_DEBUG_PUB.ADD('Out of the insert into temp table ');
4318:
4319: END IF;
4320:
4321: -----end loop for bill-to sites
4335: FROM OE_CREDIT_EXPOSURE_TEMP;
4336: END IF;
4337:
4338: COMMIT;
4339: OE_DEBUG_PUB.ADD('Out of OEXRCRCB:Credit_exposure_report_utils with the status='||x_return_status);
4340:
4341: EXCEPTION
4342:
4343: WHEN GL_CURRENCY_API.NO_RATE THEN
4340:
4341: EXCEPTION
4342:
4343: WHEN GL_CURRENCY_API.NO_RATE THEN
4344: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - GL_CURRENCY_API.NO_RATE');
4345: RAISE;
4346: WHEN OTHERS THEN
4347: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - Unexpected Error',1);
4348: OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
4343: WHEN GL_CURRENCY_API.NO_RATE THEN
4344: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - GL_CURRENCY_API.NO_RATE');
4345: RAISE;
4346: WHEN OTHERS THEN
4347: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - Unexpected Error',1);
4348: OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
4349:
4350: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4351: OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Credit_exposure_report_utils');
4344: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - GL_CURRENCY_API.NO_RATE');
4345: RAISE;
4346: WHEN OTHERS THEN
4347: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - Unexpected Error',1);
4348: OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
4349:
4350: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4351: OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Credit_exposure_report_utils');
4352: END IF;