[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
501: ||v_order_date_clause
502: ||v_order_by_clause;
503:
504: END IF;
505: OE_DEBUG_PUB.ADD('Hold select : '||v_hold_final_select,2); --bug# 5187621
506: OE_DEBUG_PUB.ADD('Release select : '||v_release_final_select,2);
507: --
508: -- Open the dynamic sql cursor
509: --
502: ||v_order_by_clause;
503:
504: END IF;
505: OE_DEBUG_PUB.ADD('Hold select : '||v_hold_final_select,2); --bug# 5187621
506: OE_DEBUG_PUB.ADD('Release select : '||v_release_final_select,2);
507: --
508: -- Open the dynamic sql cursor
509: --
510: v_hold_cursorID := DBMS_SQL.OPEN_CURSOR;
513: -- Parse the query
514: --
515: DBMS_SQL.PARSE(v_hold_cursorID, v_hold_final_select, DBMS_SQL.NATIVE);
516: DBMS_SQL.PARSE(v_release_cursorID, v_release_final_select, DBMS_SQL.NATIVE);
517: OE_DEBUG_PUB.ADD('Parsed the cursor',2);
518: --
519: -- Define input variables. Only bind the variables if it is part of the
520: -- select statement
521: --
518: --
519: -- Define input variables. Only bind the variables if it is part of the
520: -- select statement
521: --
522: OE_DEBUG_PUB.ADD('Start binding input variables',2);
523:
524: IF p_order_by = 'GREATEST_ORDER_VALUE' THEN
525: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':gl_currency_code',p_gl_currency_code);
526: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':gl_currency_code',p_gl_currency_code);
523:
524: IF p_order_by = 'GREATEST_ORDER_VALUE' THEN
525: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':gl_currency_code',p_gl_currency_code);
526: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':gl_currency_code',p_gl_currency_code);
527: OE_DEBUG_PUB.ADD('Binded gl_currency_code',2);
528: END IF;
529:
530: IF INSTRB(v_cust_prof_class_clause,':cust_prof_class_from') <> 0 THEN
531: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_prof_class_from',p_cust_prof_class_name_from);
529:
530: IF INSTRB(v_cust_prof_class_clause,':cust_prof_class_from') <> 0 THEN
531: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_prof_class_from',p_cust_prof_class_name_from);
532: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_prof_class_from',p_cust_prof_class_name_from);
533: OE_DEBUG_PUB.ADD('Binded cust_prof_class_from',2);
534: END IF;
535:
536: IF INSTRB(v_cust_prof_class_clause,':cust_prof_class_to') <> 0 THEN
537: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_prof_class_to',p_cust_prof_class_name_to);
535:
536: IF INSTRB(v_cust_prof_class_clause,':cust_prof_class_to') <> 0 THEN
537: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_prof_class_to',p_cust_prof_class_name_to);
538: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_prof_class_to' ,p_cust_prof_class_name_to);
539: OE_DEBUG_PUB.ADD('Binded cust_prof_class_to',2);
540: END IF;
541:
542: IF INSTRB(v_party_name_clause, ':party_name_from') <> 0 THEN
543: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':party_name_from',p_party_name_from);
541:
542: IF INSTRB(v_party_name_clause, ':party_name_from') <> 0 THEN
543: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':party_name_from',p_party_name_from);
544: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':party_name_from',p_party_name_from);
545: OE_DEBUG_PUB.ADD('Binded party_name_from',2);
546: END IF;
547:
548: IF INSTRB(v_party_name_clause, ':party_name_to') <> 0 THEN
549: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':party_name_to',p_party_name_to);
547:
548: IF INSTRB(v_party_name_clause, ':party_name_to') <> 0 THEN
549: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':party_name_to',p_party_name_to);
550: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':party_name_to' ,p_party_name_to);
551: OE_DEBUG_PUB.ADD('Binded party_name_to',2);
552: END IF;
553:
554: IF INSTRB(v_cust_acct_number_clause, ':cust_acct_number_from') <> 0 THEN
555: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_acct_number_from', p_cust_acct_number_from);
553:
554: IF INSTRB(v_cust_acct_number_clause, ':cust_acct_number_from') <> 0 THEN
555: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_acct_number_from', p_cust_acct_number_from);
556: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_acct_number_from', p_cust_acct_number_from);
557: OE_DEBUG_PUB.ADD('Binded cust_acct_number_from',2);
558: END IF;
559:
560: IF INSTRB(v_cust_acct_number_clause, ':cust_acct_number_to') <> 0 THEN
561: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_acct_number_to', p_cust_acct_number_to);
559:
560: IF INSTRB(v_cust_acct_number_clause, ':cust_acct_number_to') <> 0 THEN
561: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':cust_acct_number_to', p_cust_acct_number_to);
562: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':cust_acct_number_to', p_cust_acct_number_to);
563: OE_DEBUG_PUB.ADD('Binded cust_acct_number_to',2);
564: END IF;
565:
566: IF INSTRB(v_order_date_clause, ':order_date_from') <> 0 THEN
567: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_from', p_order_date_from);
565:
566: IF INSTRB(v_order_date_clause, ':order_date_from') <> 0 THEN
567: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_from', p_order_date_from);
568: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':order_date_from', p_order_date_from);
569: OE_DEBUG_PUB.ADD('Binded order_date_from',2);
570: END IF;
571:
572: IF INSTRB(v_order_date_clause, ':order_date_to') <> 0 THEN
573: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_to', p_order_date_to);
571:
572: IF INSTRB(v_order_date_clause, ':order_date_to') <> 0 THEN
573: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_to', p_order_date_to);
574: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':order_date_to', p_order_date_to);
575: OE_DEBUG_PUB.ADD('Binded order_date_to',2);
576: END IF;
577: OE_DEBUG_PUB.ADD('Finished binding input variables',2);
578: --
579: --Define the output variables
573: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':order_date_to', p_order_date_to);
574: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':order_date_to', p_order_date_to);
575: OE_DEBUG_PUB.ADD('Binded order_date_to',2);
576: END IF;
577: OE_DEBUG_PUB.ADD('Finished binding input variables',2);
578: --
579: --Define the output variables
580: --
581: DBMS_SQL.DEFINE_COLUMN(v_hold_cursorID, 1, f_hold_header_id);
580: --
581: DBMS_SQL.DEFINE_COLUMN(v_hold_cursorID, 1, f_hold_header_id);
582: DBMS_SQL.DEFINE_COLUMN(v_release_cursorID, 1, f_release_header_id);
583:
584: OE_DEBUG_PUB.ADD('Hold ID : '||v_hold_cursorID,2); --bug# 5187621
585: OE_DEBUG_PUB.ADD('Release ID : '||v_release_cursorID,2);
586: --
587: -- Set the l_profile_org_id value to use instead of checking for NULL
588: --
581: DBMS_SQL.DEFINE_COLUMN(v_hold_cursorID, 1, f_hold_header_id);
582: DBMS_SQL.DEFINE_COLUMN(v_release_cursorID, 1, f_release_header_id);
583:
584: OE_DEBUG_PUB.ADD('Hold ID : '||v_hold_cursorID,2); --bug# 5187621
585: OE_DEBUG_PUB.ADD('Release ID : '||v_release_cursorID,2);
586: --
587: -- Set the l_profile_org_id value to use instead of checking for NULL
588: --
589: -- MOAC Start
592: IF l_profile_org_id IS NOT NULL THEN
593: MO_GLOBAL.set_policy_context('S', l_profile_org_id);
594:
595: IF l_debug_level > 0 THEN
596: OE_DEBUG_PUB.Add('org_id : ' || l_profile_org_id);
597: END IF;
598:
599: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':profile_org_id', l_profile_org_id);
600: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':profile_org_id', l_profile_org_id);
597: END IF;
598:
599: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':profile_org_id', l_profile_org_id);
600: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':profile_org_id', l_profile_org_id);
601: OE_DEBUG_PUB.ADD('Binded profile_org_id',2);
602:
603: --------------------------------------------------------------------
604: --
605: -- RELEASE: Execute the RELEASE select statement
616: --
617: -- Retreive the header ID into output variable
618: --
619: DBMS_SQL.COLUMN_VALUE(v_release_cursorID, 1, f_release_header_id);
620: OE_DEBUG_PUB.ADD('f_release_header_id = '|| f_release_header_id,4);
621: OE_Credit_Engine_GRP.Check_Credit
622: ( p_header_id => f_release_header_id
623: , p_calling_action => 'AUTO RELEASE'
624: , x_msg_count => l_msg_count
626: , x_result_out => l_result_out
627: , x_cc_hold_comment => l_cc_hold_comment
628: , x_return_status => l_return_status
629: );
630: OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
631: --
632: -- Insert the result into the temporary table
633: --
634: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
661: --
662: -- Retreive the header ID into output variable
663: --
664: DBMS_SQL.COLUMN_VALUE(v_hold_cursorID, 1, f_hold_header_id);
665: OE_DEBUG_PUB.ADD('f_hold_header_id = '|| f_hold_header_id,4);
666: OE_Credit_Engine_GRP.Check_Credit
667: ( p_header_id => f_hold_header_id
668: , p_calling_action => 'AUTO HOLD'
669: , x_msg_count => l_msg_count
671: , x_result_out => l_result_out
672: , x_cc_hold_comment => l_cc_hold_comment
673: , x_return_status => l_return_status
674: );
675: OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
676: --
677: -- Insert the result into the temporary table
678: --
679: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
690: END IF;
691: END LOOP;
692: --
693: DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
694: OE_DEBUG_PUB.ADD('Orders Processed = '||l_count,2);
695: ELSE
696: OPEN l_secured_ou_cur;
697:
698: LOOP
706:
707: MO_GLOBAL.set_policy_context('S', l_profile_org_id);
708:
709: IF l_debug_level > 0 THEN
710: OE_DEBUG_PUB.Add('org_id : ' || l_profile_org_id);
711: END IF;
712:
713: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':profile_org_id', l_profile_org_id);
714: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':profile_org_id', l_profile_org_id);
711: END IF;
712:
713: DBMS_SQL.BIND_VARIABLE(v_hold_cursorID,':profile_org_id', l_profile_org_id);
714: DBMS_SQL.BIND_VARIABLE(v_release_cursorID,':profile_org_id', l_profile_org_id);
715: OE_DEBUG_PUB.ADD('Binded profile_org_id',2);
716:
717: --------------------------------------------------------------------
718: --
719: -- RELEASE: Execute the RELEASE select statement
730: --
731: -- Retreive the header ID into output variable
732: --
733: DBMS_SQL.COLUMN_VALUE(v_release_cursorID, 1, f_release_header_id);
734: OE_DEBUG_PUB.ADD('f_release_header_id = '|| f_release_header_id,4);
735: OE_Credit_Engine_GRP.Check_Credit
736: ( p_header_id => f_release_header_id
737: , p_calling_action => 'AUTO RELEASE'
738: , x_msg_count => l_msg_count
740: , x_result_out => l_result_out
741: , x_cc_hold_comment => l_cc_hold_comment
742: , x_return_status => l_return_status
743: );
744: OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
745: --
746: -- Insert the result into the temporary table
747: --
748: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
773: --
774: -- Retreive the header ID into output variable
775: --
776: DBMS_SQL.COLUMN_VALUE(v_hold_cursorID, 1, f_hold_header_id);
777: OE_DEBUG_PUB.ADD('f_hold_header_id = '|| f_hold_header_id,4);
778: OE_Credit_Engine_GRP.Check_Credit
779: ( p_header_id => f_hold_header_id
780: , p_calling_action => 'AUTO HOLD'
781: , x_msg_count => l_msg_count
783: , x_result_out => l_result_out
784: , x_cc_hold_comment => l_cc_hold_comment
785: , x_return_status => l_return_status
786: );
787: OE_DEBUG_PUB.ADD('Check_Credit return status = '||l_return_status);
788: --
789: -- Insert the result into the temporary table
790: --
791: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
805:
806: DBMS_SQL.CLOSE_CURSOR(v_release_cursorID);
807: DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
808:
809: OE_DEBUG_PUB.ADD('Orders Processed = '||l_count,2);
810:
811: CLOSE l_secured_ou_cur;
812: END IF;
813: -- MOAC End
827: , x_result_out => l_result_out
828: , x_cc_hold_comment => l_cc_hold_comment
829: , x_return_status => l_return_status
830: );
831: OE_DEBUG_PUB.ADD('Release: Check_Credit return status = '||l_return_status);
832: --
833: -- Insert the results into the temporary table
834: --
835: OE_DEBUG_PUB.ADD('Right before inserting into temp table');
831: OE_DEBUG_PUB.ADD('Release: Check_Credit return status = '||l_return_status);
832: --
833: -- Insert the results into the temporary table
834: --
835: OE_DEBUG_PUB.ADD('Right before inserting into temp table');
836: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
837: (l_result_out = 'PASS' OR l_result_out = 'PASS_REL')) --bug# 5187621
838: THEN
839: Insert_In_Temp_Table(p_header_id, 'PASS');
844: Insert_In_Temp_Table(p_header_id, 'FAIL');
845: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
846: Insert_In_Temp_Table(p_header_id, 'ERROR');
847: END IF;
848: OE_DEBUG_PUB.ADD('Release: Right after inserting into temp table');
849:
850: OE_Credit_Engine_GRP.Check_Credit
851: ( p_header_id => p_header_id
852: , p_calling_action => 'AUTO HOLD'
855: , x_result_out => l_result_out
856: , x_cc_hold_comment => l_cc_hold_comment
857: , x_return_status => l_return_status
858: );
859: OE_DEBUG_PUB.ADD('Hold: Check_Credit return status = '||l_return_status);
860: --
861: -- Insert the results into the temporary table
862: --
863: OE_DEBUG_PUB.ADD('Right before inserting into temp table');
859: OE_DEBUG_PUB.ADD('Hold: Check_Credit return status = '||l_return_status);
860: --
861: -- Insert the results into the temporary table
862: --
863: OE_DEBUG_PUB.ADD('Right before inserting into temp table');
864: IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
865: (l_result_out = 'PASS' OR l_result_out = 'PASS_REL')) --bug# 5187621
866: THEN
867: Insert_In_Temp_Table(p_header_id, 'PASS');
872: Insert_In_Temp_Table(p_header_id, 'FAIL');
873: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
874: Insert_In_Temp_Table(p_header_id, 'ERROR');
875: END IF;
876: OE_DEBUG_PUB.ADD('Hold: Right after inserting into temp table');
877: END IF;
878:
879: OE_DEBUG_PUB.ADD('OUT OEXRCRCB: Credit_Check_Processor',1);
880: EXCEPTION
875: END IF;
876: OE_DEBUG_PUB.ADD('Hold: Right after inserting into temp table');
877: END IF;
878:
879: OE_DEBUG_PUB.ADD('OUT OEXRCRCB: Credit_Check_Processor',1);
880: EXCEPTION
881: WHEN GL_CURRENCY_API.NO_RATE THEN
882: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Error',1);
883: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE',1);
878:
879: OE_DEBUG_PUB.ADD('OUT OEXRCRCB: Credit_Check_Processor',1);
880: EXCEPTION
881: WHEN GL_CURRENCY_API.NO_RATE THEN
882: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Error',1);
883: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE',1);
884: IF DBMS_SQL.IS_OPEN(v_hold_cursorID) THEN
885: DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
886: END IF;
879: OE_DEBUG_PUB.ADD('OUT OEXRCRCB: Credit_Check_Processor',1);
880: EXCEPTION
881: WHEN GL_CURRENCY_API.NO_RATE THEN
882: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Error',1);
883: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE',1);
884: IF DBMS_SQL.IS_OPEN(v_hold_cursorID) THEN
885: DBMS_SQL.CLOSE_CURSOR(v_hold_cursorID);
886: END IF;
887: IF DBMS_SQL.IS_OPEN(v_release_cursorID) THEN
888: DBMS_SQL.CLOSE_CURSOR(v_release_cursorID);
889: END IF;
890: RAISE;
891: WHEN OTHERS THEN
892: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Unexpected Error',1);
893: OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
894: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
895: OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Credit_Check_Processor');
896: END IF;
889: END IF;
890: RAISE;
891: WHEN OTHERS THEN
892: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_Check_Processor - Unexpected Error',1);
893: OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
894: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
895: OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Credit_Check_Processor');
896: END IF;
897: IF DBMS_SQL.IS_OPEN(v_hold_cursorID) THEN
961: l_ar_amount NUMBER;
962:
963:
964: BEGIN
965: OE_DEBUG_PUB.ADD('IN Get_Unchecked_Exposure ');
966: x_return_status := FND_API.G_RET_STS_SUCCESS;
967:
968:
969: ---get credit check rule record
979:
980: -----Party level-------
981: IF p_party_id IS NOT NULL AND p_customer_id IS NULL
982: THEN
983: OE_DEBUG_PUB.ADD('global table has rows= '||
984: TO_CHAR(OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT));
985:
986: ----get unchecked usages
987: ---put checked currencies in one string with # as separator
998: ---if there are no usage currencies
999: IF OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT=0
1000: THEN
1001: ---build the table for all currencies
1002: OE_DEBUG_PUB.ADD('Build table for all currencies as unchecked');
1003:
1004: FOR curr_csr_rec IN curr_csr
1005: LOOP
1006:
1012: ELSE
1013:
1014: FOR curr_csr_rec IN curr_csr
1015: LOOP
1016: -- OE_DEBUG_PUB.ADD('Start loop for currency '||
1017: -- curr_csr_rec.currency_code);
1018:
1019: IF INSTRB (l_checked_curr_rec,curr_csr_rec.currency_code,1,1)=0
1020: THEN
1028:
1029: END IF;
1030:
1031: -----just for debuging----------------------------------------------------------
1032: OE_DEBUG_PUB.ADD('table for unchecked currencies for the party: ');
1033:
1034: FOR k IN 1..l_cust_unchk_curr_tbl.COUNT
1035: LOOP
1036: OE_DEBUG_PUB.ADD('currency_code=: '||l_cust_unchk_curr_tbl(k).usage_curr_code);
1032: OE_DEBUG_PUB.ADD('table for unchecked currencies for the party: ');
1033:
1034: FOR k IN 1..l_cust_unchk_curr_tbl.COUNT
1035: LOOP
1036: OE_DEBUG_PUB.ADD('currency_code=: '||l_cust_unchk_curr_tbl(k).usage_curr_code);
1037: END LOOP;
1038: --------------------------------------------------------------------------------
1039:
1040: -----calculate exposure
1041:
1042: ----pre-calculate exposure
1043: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1044: THEN
1045: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1046: OE_DEBUG_PUB.ADD('Parameters:');
1047: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1048: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1049: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1042: ----pre-calculate exposure
1043: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1044: THEN
1045: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1046: OE_DEBUG_PUB.ADD('Parameters:');
1047: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1048: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1049: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1050: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1043: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1044: THEN
1045: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1046: OE_DEBUG_PUB.ADD('Parameters:');
1047: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1048: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1049: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1050: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1051: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1044: THEN
1045: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1046: OE_DEBUG_PUB.ADD('Parameters:');
1047: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1048: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1049: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1050: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1051: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1052: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1045: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1046: OE_DEBUG_PUB.ADD('Parameters:');
1047: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1048: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1049: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1050: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1051: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1052: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1053:
1046: OE_DEBUG_PUB.ADD('Parameters:');
1047: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1048: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1049: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1050: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1051: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1052: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1053:
1054: /*
1047: OE_DEBUG_PUB.ADD('p_party_id: '||p_party_id);
1048: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1049: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1050: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1051: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1052: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1053:
1054: /*
1055:
1048: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1049: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1050: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1051: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1052: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1053:
1054: /*
1055:
1056: OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1089: , x_error_curr_tbl => l_error_curr_tbl
1090: );
1091:
1092:
1093: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1094: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1095: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1096:
1097: IF l_error_curr_tbl.COUNT<>0
1090: );
1091:
1092:
1093: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1094: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1095: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1096:
1097: IF l_error_curr_tbl.COUNT<>0
1098: THEN
1091:
1092:
1093: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1094: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1095: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1096:
1097: IF l_error_curr_tbl.COUNT<>0
1098: THEN
1099: FOR f IN 1..l_error_curr_tbl.COUNT
1097: IF l_error_curr_tbl.COUNT<>0
1098: THEN
1099: FOR f IN 1..l_error_curr_tbl.COUNT
1100: LOOP
1101: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1102:
1103: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1104: base currency '||p_base_currency||' is missing for conversion type '||
1105: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1099: FOR f IN 1..l_error_curr_tbl.COUNT
1100: LOOP
1101: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1102:
1103: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1104: base currency '||p_base_currency||' is missing for conversion type '||
1105: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1106:
1107: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1103: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1104: base currency '||p_base_currency||' is missing for conversion type '||
1105: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1106:
1107: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1108: END LOOP;
1109:
1110: ---bug fix 2439029
1111:
1123:
1124: ----non pre-calculate exposure
1125: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1126: THEN
1127: OE_DEBUG_PUB.ADD('Party level exposure is supported only for
1128: credit check rule with pre-calculated exposure');
1129:
1130: x_unchecked_expousre:=0;
1131:
1133:
1134: ----Customer level
1135: ELSIF p_site_id IS NULL AND p_party_id IS NULL
1136: THEN
1137: OE_DEBUG_PUB.ADD('global table has rows= '||
1138: TO_CHAR(OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT));
1139:
1140: ----get unchecked usages
1141: ---put checked currencies in one string with # as separator
1152: ---if there are no usage currencies
1153: IF OE_Credit_Engine_GRP.G_cust_curr_tbl.COUNT=0
1154: THEN
1155: ---build the table for all currencies
1156: OE_DEBUG_PUB.ADD('Build table for all currencies as unchecked');
1157:
1158: FOR curr_csr_rec IN curr_csr
1159: LOOP
1160:
1166: ELSE
1167:
1168: FOR curr_csr_rec IN curr_csr
1169: LOOP
1170: -- OE_DEBUG_PUB.ADD('Start loop for currency '||
1171: -- curr_csr_rec.currency_code);
1172:
1173: IF INSTRB (l_checked_curr_rec,curr_csr_rec.currency_code,1,1)=0
1174: THEN
1182:
1183: END IF;
1184:
1185: -----just for debuging----------------------------------------------------------
1186: OE_DEBUG_PUB.ADD('table for unchecked currencies for the customer: ');
1187:
1188: FOR k IN 1..l_cust_unchk_curr_tbl.COUNT
1189: LOOP
1190: OE_DEBUG_PUB.ADD('currency_code=: '||l_cust_unchk_curr_tbl(k).usage_curr_code);
1186: OE_DEBUG_PUB.ADD('table for unchecked currencies for the customer: ');
1187:
1188: FOR k IN 1..l_cust_unchk_curr_tbl.COUNT
1189: LOOP
1190: OE_DEBUG_PUB.ADD('currency_code=: '||l_cust_unchk_curr_tbl(k).usage_curr_code);
1191: END LOOP;
1192: --------------------------------------------------------------------------------
1193:
1194: -----calculate exposure
1195:
1196: ----pre-calculate exposure
1197: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1198: THEN
1199: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1200: OE_DEBUG_PUB.ADD('Parameters:');
1201: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1202: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1203: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1196: ----pre-calculate exposure
1197: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1198: THEN
1199: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1200: OE_DEBUG_PUB.ADD('Parameters:');
1201: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1202: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1203: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1204: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1197: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1198: THEN
1199: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1200: OE_DEBUG_PUB.ADD('Parameters:');
1201: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1202: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1203: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1204: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1205: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1198: THEN
1199: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1200: OE_DEBUG_PUB.ADD('Parameters:');
1201: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1202: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1203: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1204: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1205: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1206:
1199: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1200: OE_DEBUG_PUB.ADD('Parameters:');
1201: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1202: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1203: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1204: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1205: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1206:
1207: /*
1200: OE_DEBUG_PUB.ADD('Parameters:');
1201: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1202: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1203: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1204: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1205: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1206:
1207: /*
1208:
1201: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1202: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1203: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1204: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1205: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1206:
1207: /*
1208:
1209: OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1242: , x_error_curr_tbl => l_error_curr_tbl
1243: );
1244:
1245:
1246: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1247: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1248: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1249:
1250: IF l_error_curr_tbl.COUNT<>0
1243: );
1244:
1245:
1246: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1247: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1248: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1249:
1250: IF l_error_curr_tbl.COUNT<>0
1251: THEN
1244:
1245:
1246: OE_DEBUG_PUB.ADD('OUT of Get_Exposure ');
1247: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1248: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1249:
1250: IF l_error_curr_tbl.COUNT<>0
1251: THEN
1252: FOR f IN 1..l_error_curr_tbl.COUNT
1250: IF l_error_curr_tbl.COUNT<>0
1251: THEN
1252: FOR f IN 1..l_error_curr_tbl.COUNT
1253: LOOP
1254: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1255:
1256: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1257: base currency '||p_base_currency||' is missing for conversion type '||
1258: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1252: FOR f IN 1..l_error_curr_tbl.COUNT
1253: LOOP
1254: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1255:
1256: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1257: base currency '||p_base_currency||' is missing for conversion type '||
1258: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1259:
1260: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1256: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1257: base currency '||p_base_currency||' is missing for conversion type '||
1258: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1259:
1260: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1261: END LOOP;
1262:
1263: ---bug fix 2439029
1264:
1276:
1277: ----non pre-calculate exposure
1278: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1279: THEN
1280: OE_DEBUG_PUB.ADD('OEXPCRGB: IN OE_CREDIT_ENGIN_GRB.Get_order_Exposure ');
1281: OE_DEBUG_PUB.ADD('Parameters:');
1282: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1283: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1284: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1277: ----non pre-calculate exposure
1278: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1279: THEN
1280: OE_DEBUG_PUB.ADD('OEXPCRGB: IN OE_CREDIT_ENGIN_GRB.Get_order_Exposure ');
1281: OE_DEBUG_PUB.ADD('Parameters:');
1282: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1283: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1284: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1285: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1278: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1279: THEN
1280: OE_DEBUG_PUB.ADD('OEXPCRGB: IN OE_CREDIT_ENGIN_GRB.Get_order_Exposure ');
1281: OE_DEBUG_PUB.ADD('Parameters:');
1282: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1283: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1284: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1285: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1286: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1279: THEN
1280: OE_DEBUG_PUB.ADD('OEXPCRGB: IN OE_CREDIT_ENGIN_GRB.Get_order_Exposure ');
1281: OE_DEBUG_PUB.ADD('Parameters:');
1282: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1283: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1284: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1285: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1286: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1287: /*
1280: OE_DEBUG_PUB.ADD('OEXPCRGB: IN OE_CREDIT_ENGIN_GRB.Get_order_Exposure ');
1281: OE_DEBUG_PUB.ADD('Parameters:');
1282: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1283: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1284: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1285: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1286: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1287: /*
1288: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1281: OE_DEBUG_PUB.ADD('Parameters:');
1282: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1283: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1284: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1285: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1286: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1287: /*
1288: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1289: ( p_header_id => NULL
1282: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1283: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1284: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1285: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1286: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1287: /*
1288: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1289: ( p_header_id => NULL
1290: , p_transaction_curr_code => NULL
1323: , x_return_status => l_return_status
1324: );
1325:
1326:
1327: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1328: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1329: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1330:
1331: IF l_conversion_status.COUNT<>0
1324: );
1325:
1326:
1327: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1328: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1329: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1330:
1331: IF l_conversion_status.COUNT<>0
1332: THEN
1325:
1326:
1327: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1328: OE_DEBUG_PUB.ADD('x_unchecked_expousre = '|| x_unchecked_expousre );
1329: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1330:
1331: IF l_conversion_status.COUNT<>0
1332: THEN
1333: FOR f IN 1..l_conversion_status.COUNT
1331: IF l_conversion_status.COUNT<>0
1332: THEN
1333: FOR f IN 1..l_conversion_status.COUNT
1334: LOOP
1335: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1336:
1337: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1338: base currency '||p_base_currency||' is missing for conversion type '||
1339: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1333: FOR f IN 1..l_conversion_status.COUNT
1334: LOOP
1335: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1336:
1337: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1338: base currency '||p_base_currency||' is missing for conversion type '||
1339: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1340:
1341: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1337: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1338: base currency '||p_base_currency||' is missing for conversion type '||
1339: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1340:
1341: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1342:
1343: END LOOP;
1344:
1345: ---bug fix 2439029
1361: -----Bill-to Site Level
1362: ELSIF p_site_id IS NOT NULL
1363: THEN
1364:
1365: OE_DEBUG_PUB.ADD('global table has rows= '||
1366: TO_CHAR(OE_Credit_Engine_GRP.G_site_curr_tbl.COUNT));
1367: ----get unchecked usages
1368: ---put checked currencies in one string with # as separator
1369: FOR i in 1..OE_CREDIT_ENGINE_GRP.G_site_curr_tbl.COUNT
1376: ---if there are no usage currencies
1377: IF OE_Credit_Engine_GRP.G_site_curr_tbl.COUNT=0
1378: THEN
1379: ---build the table for all currencies
1380: OE_DEBUG_PUB.ADD('Build table for all currencies as unchecked');
1381:
1382: FOR curr_csr_rec IN curr_csr
1383: LOOP
1384:
1402:
1403: END IF;
1404:
1405: -----just for debuging----------------------------------------------------------
1406: OE_DEBUG_PUB.ADD('table for unchecked currencies for the site: ');
1407:
1408: FOR k IN 1..l_site_unchk_curr_tbl.COUNT
1409: LOOP
1410: OE_DEBUG_PUB.ADD('currency_code=: '||l_site_unchk_curr_tbl(k).usage_curr_code);
1406: OE_DEBUG_PUB.ADD('table for unchecked currencies for the site: ');
1407:
1408: FOR k IN 1..l_site_unchk_curr_tbl.COUNT
1409: LOOP
1410: OE_DEBUG_PUB.ADD('currency_code=: '||l_site_unchk_curr_tbl(k).usage_curr_code);
1411: END LOOP;
1412: --------------------------------------------------------------------------------
1413:
1414: -----calculate exposure
1415:
1416: ----pre-calculate exposure
1417: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1418: THEN
1419: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1420: OE_DEBUG_PUB.ADD('Parameters:');
1421: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1422: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1423: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1416: ----pre-calculate exposure
1417: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1418: THEN
1419: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1420: OE_DEBUG_PUB.ADD('Parameters:');
1421: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1422: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1423: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1424: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1417: IF l_credit_check_rule_rec.quick_cr_check_flag ='Y'
1418: THEN
1419: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1420: OE_DEBUG_PUB.ADD('Parameters:');
1421: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1422: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1423: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1424: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1425: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1418: THEN
1419: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1420: OE_DEBUG_PUB.ADD('Parameters:');
1421: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1422: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1423: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1424: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1425: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1426: /*
1419: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1420: OE_DEBUG_PUB.ADD('Parameters:');
1421: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1422: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1423: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1424: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1425: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1426: /*
1427: OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1420: OE_DEBUG_PUB.ADD('Parameters:');
1421: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1422: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1423: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1424: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1425: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1426: /*
1427: OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1428: ( p_customer_id => p_customer_id
1421: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1422: OE_DEBUG_PUB.ADD('p_site_id: '||p_site_id);
1423: OE_DEBUG_PUB.ADD('p_header_id:'||'NULL');
1424: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1425: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1426: /*
1427: OE_CREDIT_EXPOSURE_PVT.Get_Exposure
1428: ( p_customer_id => p_customer_id
1429: , p_site_use_id => p_site_id
1460: , x_error_curr_tbl => l_error_curr_tbl
1461: );
1462:
1463:
1464: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1465: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1466: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1467:
1468: IF l_error_curr_tbl.COUNT<>0
1461: );
1462:
1463:
1464: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1465: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1466: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1467:
1468: IF l_error_curr_tbl.COUNT<>0
1469: THEN
1462:
1463:
1464: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of OE_CREDIT_EXPOSURE_PVT.Get_Exposure ');
1465: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1466: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1467:
1468: IF l_error_curr_tbl.COUNT<>0
1469: THEN
1470: FOR f IN 1..l_error_curr_tbl.COUNT
1468: IF l_error_curr_tbl.COUNT<>0
1469: THEN
1470: FOR f IN 1..l_error_curr_tbl.COUNT
1471: LOOP
1472: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1473:
1474: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1475: base currency '||p_base_currency||' is missing for conversion type '||
1476: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1470: FOR f IN 1..l_error_curr_tbl.COUNT
1471: LOOP
1472: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1473:
1474: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1475: base currency '||p_base_currency||' is missing for conversion type '||
1476: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1477:
1478: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1474: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_error_curr_tbl(f).usage_curr_code||' and
1475: base currency '||p_base_currency||' is missing for conversion type '||
1476: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1477:
1478: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1479:
1480: END LOOP;
1481:
1482: ---bug fix 2439029
1495:
1496: ----non pre-calculate exposure
1497: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1498: THEN
1499: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_CHECK_UTIL.Get_order_Exposure ');
1500: OE_DEBUG_PUB.ADD('Parameters:');
1501: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1502: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1503: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1496: ----non pre-calculate exposure
1497: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1498: THEN
1499: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_CHECK_UTIL.Get_order_Exposure ');
1500: OE_DEBUG_PUB.ADD('Parameters:');
1501: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1502: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1503: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1504: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1497: ELSIF l_credit_check_rule_rec.quick_cr_check_flag ='N'
1498: THEN
1499: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_CHECK_UTIL.Get_order_Exposure ');
1500: OE_DEBUG_PUB.ADD('Parameters:');
1501: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1502: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1503: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1504: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1505: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1498: THEN
1499: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_CHECK_UTIL.Get_order_Exposure ');
1500: OE_DEBUG_PUB.ADD('Parameters:');
1501: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1502: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1503: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1504: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1505: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1506: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1499: OE_DEBUG_PUB.ADD('OEXPCRGB: IN of OE_CREDIT_CHECK_UTIL.Get_order_Exposure ');
1500: OE_DEBUG_PUB.ADD('Parameters:');
1501: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1502: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1503: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1504: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1505: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1506: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1507: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1500: OE_DEBUG_PUB.ADD('Parameters:');
1501: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1502: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1503: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1504: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1505: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1506: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1507: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1508: /*
1501: OE_DEBUG_PUB.ADD('p_header_id: '||'NULL');
1502: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1503: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1504: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1505: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1506: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1507: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1508: /*
1509: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1502: OE_DEBUG_PUB.ADD('p_transaction_curr_code: '||'NULL');
1503: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1504: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1505: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1506: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1507: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1508: /*
1509: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1510: ( p_header_id => NULL
1503: OE_DEBUG_PUB.ADD('p_customer_id: '||p_customer_id);
1504: OE_DEBUG_PUB.ADD('p_site_use_id: '||p_site_id);
1505: OE_DEBUG_PUB.ADD('p_credit_level: '||'SITE');
1506: OE_DEBUG_PUB.ADD('p_limit_curr_code: '||p_base_currency);
1507: OE_DEBUG_PUB.ADD('p_include_all_flag: '||'N');
1508: /*
1509: OE_CREDIT_CHECK_UTIL.Get_order_exposure
1510: ( p_header_id => NULL
1511: , p_transaction_curr_code => NULL
1544: , x_return_status => l_return_status
1545: );
1546:
1547:
1548: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1549: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1550: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1551:
1552: IF l_conversion_status.COUNT<>0
1545: );
1546:
1547:
1548: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1549: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1550: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1551:
1552: IF l_conversion_status.COUNT<>0
1553: THEN
1546:
1547:
1548: OE_DEBUG_PUB.ADD('OUT of Get_order_exposure ');
1549: OE_DEBUG_PUB.ADD('x_unchecked_expousre: '||x_unchecked_expousre);
1550: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
1551:
1552: IF l_conversion_status.COUNT<>0
1553: THEN
1554:
1553: THEN
1554:
1555: FOR f IN 1..l_conversion_status.COUNT
1556: LOOP
1557: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1558:
1559: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1560: base currency '||p_base_currency||' is missing for conversion type '||
1561: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1555: FOR f IN 1..l_conversion_status.COUNT
1556: LOOP
1557: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1558:
1559: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1560: base currency '||p_base_currency||' is missing for conversion type '||
1561: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1562:
1563: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1559: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||l_conversion_status(f).usage_curr_code||' and
1560: base currency '||p_base_currency||' is missing for conversion type '||
1561: NVL(l_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
1562:
1563: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
1564:
1565: END LOOP;
1566:
1567: ---bug fix 2439029
1581: END IF;
1582:
1583: END IF;
1584:
1585: OE_DEBUG_PUB.ADD('Out Get_unchecked_exposure with status='||x_return_status);
1586: OE_DEBUG_PUB.ADD('unchecked_exposure='||x_unchecked_expousre);
1587:
1588:
1589: EXCEPTION
1582:
1583: END IF;
1584:
1585: OE_DEBUG_PUB.ADD('Out Get_unchecked_exposure with status='||x_return_status);
1586: OE_DEBUG_PUB.ADD('unchecked_exposure='||x_unchecked_expousre);
1587:
1588:
1589: EXCEPTION
1590: WHEN OTHERS THEN
1932:
1933: BEGIN
1934:
1935:
1936: OE_DEBUG_PUB.ADD('IN OEXRCRCB:Credit_exposure_report_utils ');
1937: l_return_status := FND_API.G_RET_STS_SUCCESS;
1938: l_global_exposure_flag := 'N' ;
1939:
1940: ----get base currency=currency of the current operating unit
1939:
1940: ----get base currency=currency of the current operating unit
1941: l_base_currency:=OE_CREDIT_CHECK_UTIL.Get_GL_currency;
1942:
1943: OE_DEBUG_PUB.ADD('l_base_currency= '||l_base_currency);
1944:
1945: ---get conversion type
1946: SELECT
1947: conversion_type
1952:
1953: ----get the hierarchy_type
1954: l_profile_value:=OE_CREDIT_CHECK_UTIL.G_hierarchy_type;
1955:
1956: OE_DEBUG_PUB.ADD('AR_CMGT_HIERARCHY_TYPE= '||l_profile_value);
1957:
1958: --------Party Range Summary Report-----------
1959:
1960: IF p_report_by_option='PARTY_SUMMARY'
1959:
1960: IF p_report_by_option='PARTY_SUMMARY'
1961: THEN
1962:
1963: OE_DEBUG_PUB.ADD('IN Party Summary Report');
1964:
1965: ------start loop for parties
1966: IF (p_party_name_low IS NOT NULL) THEN
1967: l_input := 1;
1971: l_input := 3;
1972: ELSIF (p_party_number_high IS NOT NULL) THEN
1973: l_input := 4;
1974: ELSE
1975: OE_DEBUG_PUB.ADD(' no party input');
1976: l_input := 0;
1977: END IF;
1978:
1979: OE_DEBUG_PUB.ADD(' party input:'||l_input);
1975: OE_DEBUG_PUB.ADD(' no party input');
1976: l_input := 0;
1977: END IF;
1978:
1979: OE_DEBUG_PUB.ADD(' party input:'||l_input);
1980: IF (l_input > 0) THEN
1981: -- 5212830 FOR party_csr_rec IN party_csr(l_input)
1982:
1983: LOOP
2052: , x_return_status => l_return_status
2053: );
2054:
2055:
2056: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2057: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2058:
2059: IF l_return_status = 'C'
2060: THEN
2053: );
2054:
2055:
2056: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2057: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2058:
2059: IF l_return_status = 'C'
2060: THEN
2061: l_return_status1:='C';
2074: l_party_available:=party_prof_csr_rec.party_overall_limit - l_party_total_exposure;
2075:
2076:
2077: -----convert overall_credit_limit, exposure and available credit into base currency
2078: OE_DEBUG_PUB.ADD('IN Convert amounts ');
2079:
2080: l_base_cur_overall_limit :=
2081: GL_CURRENCY_API.Convert_closest_amount_sql
2082: ( x_from_currency => party_prof_csr_rec.party_currency_code
2087: , x_amount => party_prof_csr_rec.party_overall_limit
2088: , x_max_roll_days => -1
2089: );
2090:
2091: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2092: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2093:
2094: l_base_cur_exposure :=
2095: GL_CURRENCY_API.Convert_closest_amount_sql
2088: , x_max_roll_days => -1
2089: );
2090:
2091: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2092: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2093:
2094: l_base_cur_exposure :=
2095: GL_CURRENCY_API.Convert_closest_amount_sql
2096: ( x_from_currency => party_prof_csr_rec.party_currency_code
2101: , x_amount => l_party_total_exposure
2102: , x_max_roll_days => -1
2103: );
2104:
2105: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2106: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2107:
2108: l_base_cur_available :=
2109: GL_CURRENCY_API.Convert_closest_amount_sql
2102: , x_max_roll_days => -1
2103: );
2104:
2105: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2106: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2107:
2108: l_base_cur_available :=
2109: GL_CURRENCY_API.Convert_closest_amount_sql
2110: ( x_from_currency => party_prof_csr_rec.party_currency_code
2115: , x_amount => l_party_available
2116: , x_max_roll_days => -1
2117: );
2118:
2119: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2120: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2121: || TO_CHAR(l_base_cur_available ));
2122:
2123: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2116: , x_max_roll_days => -1
2117: );
2118:
2119: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2120: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2121: || TO_CHAR(l_base_cur_available ));
2122:
2123: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2124:
2119: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2120: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2121: || TO_CHAR(l_base_cur_available ));
2122:
2123: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2124:
2125: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2126:
2127: l_global_exposure_flag := 'Y' ;
2121: || TO_CHAR(l_base_cur_available ));
2122:
2123: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2124:
2125: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2126:
2127: l_global_exposure_flag := 'Y' ;
2128:
2129:
2126:
2127: l_global_exposure_flag := 'Y' ;
2128:
2129:
2130: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2131:
2132: -----insert data into temp table
2133: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2134: ( party_id
2176: , NULL
2177: , l_global_exposure_flag
2178: );
2179:
2180: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
2181:
2182: ----end loop for party credit profiles
2183: END LOOP;
2184:
2193: ----if global variable G_cust_incl_all_flag is 'Y'
2194: ----then unchecked exposure will be 0
2195: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
2196: THEN
2197: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2198: so l_party_unchk_exposure=0');
2199: l_party_unchk_exposure:=0;
2200: ELSE
2201: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
2197: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2198: so l_party_unchk_exposure=0');
2199: l_party_unchk_exposure:=0;
2200: ELSE
2201: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
2202: ||TO_CHAR(party_csr_rec.party_id));
2203: Get_unchecked_exposure
2204: ( p_party_id => party_csr_rec.party_id
2205: , p_customer_id => NULL
2210: , x_return_status => l_return_status
2211: );
2212: END IF;
2213:
2214: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for party: '
2215: ||TO_CHAR(party_csr_rec.party_id));
2216: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2217: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2218:
2212: END IF;
2213:
2214: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for party: '
2215: ||TO_CHAR(party_csr_rec.party_id));
2216: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2217: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2218:
2219: IF l_return_status = 'C'
2220: THEN
2213:
2214: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for party: '
2215: ||TO_CHAR(party_csr_rec.party_id));
2216: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2217: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2218:
2219: IF l_return_status = 'C'
2220: THEN
2221: l_return_status1:='C';
2229: THEN
2230: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2231: END IF;
2232:
2233: OE_DEBUG_PUB.ADD('Check if the party has any credit profiles ');
2234: -----check if the party has any credit profiles
2235: SELECT COUNT(*)
2236: INTO l_prof_count
2237: FROM
2245:
2246: IF l_prof_count>0
2247: THEN
2248:
2249: OE_DEBUG_PUB.ADD('Party has credit profiles ');
2250: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2251:
2252: ----update temp table with unchecked exposure
2253: UPDATE oe_credit_exposure_temp
2246: IF l_prof_count>0
2247: THEN
2248:
2249: OE_DEBUG_PUB.ADD('Party has credit profiles ');
2250: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2251:
2252: ----update temp table with unchecked exposure
2253: UPDATE oe_credit_exposure_temp
2254: SET unchecked_exposure=l_party_unchk_exposure
2258: -----there are no credit profiles for this party
2259: ----insert unchecked exposure
2260: ELSE
2261:
2262: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
2263: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2264:
2265: -----insert data into temp table
2266: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2259: ----insert unchecked exposure
2260: ELSE
2261:
2262: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
2263: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2264:
2265: -----insert data into temp table
2266: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2267: ( party_id
2309: , l_party_unchk_exposure
2310: , NULL
2311: );
2312:
2313: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
2314:
2315: END IF;
2316:
2317: END LOOP; ----end loop for parties
2350: ELSE
2351: l_specific_party_id:= p_spec_party_num_id;
2352: END IF;
2353:
2354: OE_DEBUG_PUB.ADD('IN Party Detail Report for party_id='||TO_CHAR(l_specific_party_id));
2355:
2356: ---Check if this party is part of Hierarchy AR_CMGT_HIERARCHY_TYPE
2357: SELECT COUNT(*)
2358: INTO l_count_hierarchy
2362: OR child_id=l_specific_party_id);
2363:
2364: IF l_count_hierarchy>0
2365: THEN
2366: OE_DEBUG_PUB.ADD('This party is part of the hierarchy '||l_profile_value);
2367: ------use cursor for hierarchical parties
2368:
2369: ------start loop for parties
2370: FOR party_hier_csr_rec IN party_hier_csr
2393: , x_return_status => l_return_status
2394: );
2395:
2396:
2397: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2398: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2399:
2400: IF l_return_status = 'C'
2401: THEN
2394: );
2395:
2396:
2397: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2398: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2399:
2400: IF l_return_status = 'C'
2401: THEN
2402: l_return_status1:='C';
2415: l_party_available:=party_prof_csr_rec.party_overall_limit - l_party_total_exposure;
2416:
2417:
2418: -----convert overall_credit_limit, exposure and available credit into base currency
2419: OE_DEBUG_PUB.ADD('IN Convert amounts ');
2420:
2421: l_base_cur_overall_limit :=
2422: GL_CURRENCY_API.Convert_closest_amount_sql
2423: ( x_from_currency => party_prof_csr_rec.party_currency_code
2428: , x_amount => party_prof_csr_rec.party_overall_limit
2429: , x_max_roll_days => -1
2430: );
2431:
2432: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2433: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2434:
2435: l_base_cur_exposure :=
2436: GL_CURRENCY_API.Convert_closest_amount_sql
2429: , x_max_roll_days => -1
2430: );
2431:
2432: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2433: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2434:
2435: l_base_cur_exposure :=
2436: GL_CURRENCY_API.Convert_closest_amount_sql
2437: ( x_from_currency => party_prof_csr_rec.party_currency_code
2442: , x_amount => l_party_total_exposure
2443: , x_max_roll_days => -1
2444: );
2445:
2446: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2447: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2448:
2449: l_base_cur_available :=
2450: GL_CURRENCY_API.Convert_closest_amount_sql
2443: , x_max_roll_days => -1
2444: );
2445:
2446: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2447: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2448:
2449: l_base_cur_available :=
2450: GL_CURRENCY_API.Convert_closest_amount_sql
2451: ( x_from_currency => party_prof_csr_rec.party_currency_code
2456: , x_amount => l_party_available
2457: , x_max_roll_days => -1
2458: );
2459:
2460: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2461: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2462: || TO_CHAR(l_base_cur_available ));
2463:
2464: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2457: , x_max_roll_days => -1
2458: );
2459:
2460: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2461: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2462: || TO_CHAR(l_base_cur_available ));
2463:
2464: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2465:
2460: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2461: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2462: || TO_CHAR(l_base_cur_available ));
2463:
2464: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2465:
2466: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2467:
2468: l_global_exposure_flag := 'Y' ;
2462: || TO_CHAR(l_base_cur_available ));
2463:
2464: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2465:
2466: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2467:
2468: l_global_exposure_flag := 'Y' ;
2469:
2470: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2466: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
2467:
2468: l_global_exposure_flag := 'Y' ;
2469:
2470: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2471:
2472: -----insert data into temp table
2473: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2474: ( party_id
2516: , NULL
2517: , l_global_exposure_flag
2518: );
2519:
2520: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
2521:
2522: ----end loop for party credit profiles
2523: END LOOP;
2524:
2533: ----if global variable G_cust_incl_all_flag is 'Y'
2534: ----then unchecked exposure will be 0
2535: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
2536: THEN
2537: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2538: so l_party_unchk_exposure=0');
2539: l_party_unchk_exposure:=0;
2540: ELSE
2541: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
2537: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2538: so l_party_unchk_exposure=0');
2539: l_party_unchk_exposure:=0;
2540: ELSE
2541: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
2542: ||TO_CHAR(party_hier_csr_rec.party_id));
2543: Get_unchecked_exposure
2544: ( p_party_id => party_hier_csr_rec.party_id
2545: , p_customer_id => NULL
2550: , x_return_status => l_return_status
2551: );
2552: END IF;
2553:
2554: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure, for the party:'
2555: ||TO_CHAR(party_hier_csr_rec.party_id));
2556: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2557: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2558:
2552: END IF;
2553:
2554: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure, for the party:'
2555: ||TO_CHAR(party_hier_csr_rec.party_id));
2556: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2557: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2558:
2559: IF l_return_status = 'C'
2560: THEN
2553:
2554: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure, for the party:'
2555: ||TO_CHAR(party_hier_csr_rec.party_id));
2556: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure));
2557: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2558:
2559: IF l_return_status = 'C'
2560: THEN
2561: l_return_status1:='C';
2569: THEN
2570: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2571: END IF;
2572:
2573: OE_DEBUG_PUB.ADD('Check if the party has any credit profiles ');
2574: -----check if the party has any credit profiles
2575: SELECT COUNT(*)
2576: INTO l_prof_count
2577: FROM
2585:
2586: IF l_prof_count>0
2587: THEN
2588:
2589: OE_DEBUG_PUB.ADD('Party has credit profiles ');
2590: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2591:
2592: ----update temp table with unchecked exposure
2593: UPDATE oe_credit_exposure_temp
2586: IF l_prof_count>0
2587: THEN
2588:
2589: OE_DEBUG_PUB.ADD('Party has credit profiles ');
2590: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2591:
2592: ----update temp table with unchecked exposure
2593: UPDATE oe_credit_exposure_temp
2594: SET unchecked_exposure=l_party_unchk_exposure
2598: -----there are no credit profiles for this party
2599: ----insert unchecked exposure
2600: ELSE
2601:
2602: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
2603: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2604:
2605: -----insert data into temp table
2606: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2599: ----insert unchecked exposure
2600: ELSE
2601:
2602: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
2603: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2604:
2605: -----insert data into temp table
2606: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2607: ( party_id
2649: , l_party_unchk_exposure
2650: , 'Y'
2651: );
2652:
2653: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
2654:
2655: END IF; ---end of checking if the party has credit profiles
2656:
2657: ------start report for cust accounts for the specific party
2655: END IF; ---end of checking if the party has credit profiles
2656:
2657: ------start report for cust accounts for the specific party
2658:
2659: OE_DEBUG_PUB.ADD('IN Cust accounts section of the Party Deatils Report');
2660:
2661: ------start loop for customers
2662: FOR party_cust_csr_rec IN party_cust_csr (p_party_id => party_hier_csr_rec.party_id)
2663: LOOP
2679: , x_return_status => l_return_status
2680: );
2681:
2682:
2683: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2684: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2685:
2686: IF l_return_status = 'C'
2687: THEN
2680: );
2681:
2682:
2683: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2684: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2685:
2686: IF l_return_status = 'C'
2687: THEN
2688: l_return_status1:='C';
2701: l_cust_available:=cust_prof_csr_rec.cust_overall_limit - l_cust_total_exposure;
2702:
2703:
2704: -----convert overall_credit_limit, exposure and available credit into base currency
2705: OE_DEBUG_PUB.ADD('IN Convert amounts ');
2706:
2707: l_base_cur_overall_limit :=
2708: GL_CURRENCY_API.Convert_closest_amount_sql
2709: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
2714: , x_amount => cust_prof_csr_rec.cust_overall_limit
2715: , x_max_roll_days => -1
2716: );
2717:
2718: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2719: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2720:
2721: l_base_cur_exposure :=
2722: GL_CURRENCY_API.Convert_closest_amount_sql
2715: , x_max_roll_days => -1
2716: );
2717:
2718: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2719: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
2720:
2721: l_base_cur_exposure :=
2722: GL_CURRENCY_API.Convert_closest_amount_sql
2723: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
2728: , x_amount => l_cust_total_exposure
2729: , x_max_roll_days => -1
2730: );
2731:
2732: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2733: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2734:
2735: l_base_cur_available :=
2736: GL_CURRENCY_API.Convert_closest_amount_sql
2729: , x_max_roll_days => -1
2730: );
2731:
2732: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2733: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
2734:
2735: l_base_cur_available :=
2736: GL_CURRENCY_API.Convert_closest_amount_sql
2737: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
2742: , x_amount => l_cust_available
2743: , x_max_roll_days => -1
2744: );
2745:
2746: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2747: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2748: || TO_CHAR(l_base_cur_available ));
2749:
2750: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2743: , x_max_roll_days => -1
2744: );
2745:
2746: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2747: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2748: || TO_CHAR(l_base_cur_available ));
2749:
2750: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2751:
2746: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
2747: OE_DEBUG_PUB.ADD('l_base_cur_available = '
2748: || TO_CHAR(l_base_cur_available ));
2749:
2750: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2751:
2752: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
2753:
2754: l_global_exposure_flag :=
2748: || TO_CHAR(l_base_cur_available ));
2749:
2750: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
2751:
2752: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
2753:
2754: l_global_exposure_flag :=
2755: OE_CREDIT_CHECK_UTIL.Get_global_exposure_flag
2756: ( p_entity_type => 'CUSTOMER'
2757: , p_entity_id => party_cust_csr_rec.customer_id
2758: , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
2759: ) ;
2760:
2761: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
2762: l_global_exposure_flag );
2763:
2764: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2765:
2760:
2761: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
2762: l_global_exposure_flag );
2763:
2764: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
2765:
2766: -----insert data into temp table
2767: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2768: ( party_id
2810: , NULL
2811: , l_global_exposure_flag
2812: );
2813:
2814: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
2815:
2816: ----end loop for customer credit profiles
2817: END LOOP;
2818:
2829: ----then unchecked exposure will be 0
2830:
2831: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
2832: THEN
2833: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
2834: so l_cust_unchk_exposure=0');
2835: l_cust_unchk_exposure:=0;
2836: ELSE
2837:
2834: so l_cust_unchk_exposure=0');
2835: l_cust_unchk_exposure:=0;
2836: ELSE
2837:
2838: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the customer '
2839: ||TO_CHAR(party_cust_csr_rec.customer_id));
2840:
2841: Get_unchecked_exposure
2842: ( p_party_id => NULL
2848: , x_return_status => l_return_status
2849: );
2850: END IF;
2851:
2852: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer: '
2853: ||TO_CHAR(party_cust_csr_rec.customer_id));
2854: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure) );
2855: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2856:
2850: END IF;
2851:
2852: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer: '
2853: ||TO_CHAR(party_cust_csr_rec.customer_id));
2854: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure) );
2855: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2856:
2857: IF l_return_status = 'C'
2858: THEN
2851:
2852: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer: '
2853: ||TO_CHAR(party_cust_csr_rec.customer_id));
2854: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure) );
2855: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
2856:
2857: IF l_return_status = 'C'
2858: THEN
2859: l_return_status1:='C';
2867: THEN
2868: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2869: END IF;
2870:
2871: OE_DEBUG_PUB.ADD('Check if the customer has any credit profiles ');
2872: -----check if the customer has any credit profiles
2873: SELECT COUNT(*)
2874: INTO l_prof_count
2875: FROM
2882:
2883: IF l_prof_count>0
2884: THEN
2885:
2886: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
2887: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2888:
2889: ----update temp table with unchecked exposure
2890: UPDATE oe_credit_exposure_temp
2883: IF l_prof_count>0
2884: THEN
2885:
2886: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
2887: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
2888:
2889: ----update temp table with unchecked exposure
2890: UPDATE oe_credit_exposure_temp
2891: SET unchecked_exposure=l_cust_unchk_exposure
2897: -----there are no credit profiles for this customer
2898: ----insert unchecked exposure
2899: ELSE
2900:
2901: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
2902: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2903:
2904: -----insert data into temp table
2905: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2898: ----insert unchecked exposure
2899: ELSE
2900:
2901: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
2902: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
2903:
2904: -----insert data into temp table
2905: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
2906: ( party_id
2948: , l_cust_unchk_exposure
2949: , 'Y'
2950: );
2951:
2952: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
2953:
2954: END IF; --end of checking if the customer has credit profile
2955:
2956: ----end loop for customers
2963:
2964:
2965: -----party is not part of the hierarchy
2966: ELSE
2967: OE_DEBUG_PUB.ADD('This party is not part of the hierarchy '||l_profile_value);
2968: ------use cursor for non hierarchical parties
2969:
2970: ------start loop for parties
2971: FOR party_hier_csr1_rec IN party_hier_csr1
2994: , x_return_status => l_return_status
2995: );
2996:
2997:
2998: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2999: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3000:
3001: IF l_return_status = 'C'
3002: THEN
2995: );
2996:
2997:
2998: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
2999: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3000:
3001: IF l_return_status = 'C'
3002: THEN
3003: l_return_status1:='C';
3016: l_party_available:=party_prof_csr_rec.party_overall_limit - l_party_total_exposure;
3017:
3018:
3019: -----convert overall_credit_limit, exposure and available credit into base currency
3020: OE_DEBUG_PUB.ADD('IN Convert amounts ');
3021:
3022: l_base_cur_overall_limit :=
3023: GL_CURRENCY_API.Convert_closest_amount_sql
3024: ( x_from_currency => party_prof_csr_rec.party_currency_code
3029: , x_amount => party_prof_csr_rec.party_overall_limit
3030: , x_max_roll_days => -1
3031: );
3032:
3033: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3034: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3035:
3036: l_base_cur_exposure :=
3037: GL_CURRENCY_API.Convert_closest_amount_sql
3030: , x_max_roll_days => -1
3031: );
3032:
3033: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3034: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3035:
3036: l_base_cur_exposure :=
3037: GL_CURRENCY_API.Convert_closest_amount_sql
3038: ( x_from_currency => party_prof_csr_rec.party_currency_code
3043: , x_amount => l_party_total_exposure
3044: , x_max_roll_days => -1
3045: );
3046:
3047: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3048: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3049:
3050: l_base_cur_available :=
3051: GL_CURRENCY_API.Convert_closest_amount_sql
3044: , x_max_roll_days => -1
3045: );
3046:
3047: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3048: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3049:
3050: l_base_cur_available :=
3051: GL_CURRENCY_API.Convert_closest_amount_sql
3052: ( x_from_currency => party_prof_csr_rec.party_currency_code
3057: , x_amount => l_party_available
3058: , x_max_roll_days => -1
3059: );
3060:
3061: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3062: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3063: || TO_CHAR(l_base_cur_available ));
3064:
3065: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3058: , x_max_roll_days => -1
3059: );
3060:
3061: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3062: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3063: || TO_CHAR(l_base_cur_available ));
3064:
3065: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3066:
3061: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3062: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3063: || TO_CHAR(l_base_cur_available ));
3064:
3065: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3066:
3067: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
3068:
3069: l_global_exposure_flag := 'Y' ;
3063: || TO_CHAR(l_base_cur_available ));
3064:
3065: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3066:
3067: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
3068:
3069: l_global_exposure_flag := 'Y' ;
3070:
3071: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3067: OE_DEBUG_PUB.ADD('Global_exposure_flag for party always = Y ');
3068:
3069: l_global_exposure_flag := 'Y' ;
3070:
3071: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3072:
3073: -----insert data into temp table
3074: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3075: ( party_id
3117: , NULL
3118: , l_global_exposure_flag
3119: );
3120:
3121: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
3122:
3123: ----end loop for party credit profiles
3124: END LOOP;
3125:
3134: ----if global variable G_cust_incl_all_flag is 'Y'
3135: ----then unchecked exposure will be 0
3136: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
3137: THEN
3138: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
3139: so l_party_unchk_exposure=0');
3140:
3141: l_party_unchk_exposure:=0;
3142: ELSE
3139: so l_party_unchk_exposure=0');
3140:
3141: l_party_unchk_exposure:=0;
3142: ELSE
3143: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the party: '
3144: ||TO_CHAR(party_hier_csr1_rec.party_id));
3145:
3146: Get_unchecked_exposure
3147: ( p_party_id => party_hier_csr1_rec.party_id
3153: , x_return_status => l_return_status
3154: );
3155: END IF;
3156:
3157: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for the party: '
3158: ||TO_CHAR(party_hier_csr1_rec.party_id));
3159: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure) );
3160: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3161:
3155: END IF;
3156:
3157: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for the party: '
3158: ||TO_CHAR(party_hier_csr1_rec.party_id));
3159: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure) );
3160: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3161:
3162: IF l_return_status = 'C'
3163: THEN
3156:
3157: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure for the party: '
3158: ||TO_CHAR(party_hier_csr1_rec.party_id));
3159: OE_DEBUG_PUB.ADD('l_party_unchk_exposure = '|| TO_CHAR(l_party_unchk_exposure) );
3160: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3161:
3162: IF l_return_status = 'C'
3163: THEN
3164: l_return_status1:='C';
3172: THEN
3173: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3174: END IF;
3175:
3176: OE_DEBUG_PUB.ADD('Check if the party has any credit profiles ');
3177: -----check if the party has any credit profiles
3178: SELECT COUNT(*)
3179: INTO l_prof_count
3180: FROM
3188:
3189: IF l_prof_count>0
3190: THEN
3191:
3192: OE_DEBUG_PUB.ADD('Party has credit profiles ');
3193: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3194:
3195: ----update temp table with unchecked exposure
3196: UPDATE oe_credit_exposure_temp
3189: IF l_prof_count>0
3190: THEN
3191:
3192: OE_DEBUG_PUB.ADD('Party has credit profiles ');
3193: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3194:
3195: ----update temp table with unchecked exposure
3196: UPDATE oe_credit_exposure_temp
3197: SET unchecked_exposure=l_party_unchk_exposure
3201: -----there are no credit profiles for this party
3202: ----insert unchecked exposure
3203: ELSE
3204:
3205: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
3206: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3207:
3208: -----insert data into temp table
3209: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3202: ----insert unchecked exposure
3203: ELSE
3204:
3205: OE_DEBUG_PUB.ADD('Party does not have any credit profiles ');
3206: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3207:
3208: -----insert data into temp table
3209: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3210: ( party_id
3252: , l_party_unchk_exposure
3253: , 'Y'
3254: );
3255:
3256: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
3257:
3258: END IF; ---end of checking if the party has credit profiles
3259:
3260: ------start report for cust accounts for the specific party
3258: END IF; ---end of checking if the party has credit profiles
3259:
3260: ------start report for cust accounts for the specific party
3261:
3262: OE_DEBUG_PUB.ADD('IN Cust accounts section of the Party Details Report');
3263: OE_DEBUG_PUB.ADD('party_hier_csr1_rec.party_id ==> '||
3264: party_hier_csr1_rec.party_id );
3265:
3266: ------start loop for customers
3259:
3260: ------start report for cust accounts for the specific party
3261:
3262: OE_DEBUG_PUB.ADD('IN Cust accounts section of the Party Details Report');
3263: OE_DEBUG_PUB.ADD('party_hier_csr1_rec.party_id ==> '||
3264: party_hier_csr1_rec.party_id );
3265:
3266: ------start loop for customers
3267: FOR party_cust_csr_rec IN party_cust_csr (p_party_id => party_hier_csr1_rec.party_id)
3284: , x_return_status => l_return_status
3285: );
3286:
3287:
3288: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
3289: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3290:
3291: IF l_return_status = 'C'
3292: THEN
3285: );
3286:
3287:
3288: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
3289: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3290:
3291: IF l_return_status = 'C'
3292: THEN
3293: l_return_status1:='C';
3306: l_cust_available:=cust_prof_csr_rec.cust_overall_limit - l_cust_total_exposure;
3307:
3308:
3309: -----convert overall_credit_limit, exposure and available credit into base currency
3310: OE_DEBUG_PUB.ADD('IN Convert amounts ');
3311:
3312: l_base_cur_overall_limit :=
3313: GL_CURRENCY_API.Convert_closest_amount_sql
3314: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3319: , x_amount => cust_prof_csr_rec.cust_overall_limit
3320: , x_max_roll_days => -1
3321: );
3322:
3323: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3324: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3325:
3326: l_base_cur_exposure :=
3327: GL_CURRENCY_API.Convert_closest_amount_sql
3320: , x_max_roll_days => -1
3321: );
3322:
3323: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3324: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3325:
3326: l_base_cur_exposure :=
3327: GL_CURRENCY_API.Convert_closest_amount_sql
3328: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3333: , x_amount => l_cust_total_exposure
3334: , x_max_roll_days => -1
3335: );
3336:
3337: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3338: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3339:
3340: l_base_cur_available :=
3341: GL_CURRENCY_API.Convert_closest_amount_sql
3334: , x_max_roll_days => -1
3335: );
3336:
3337: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3338: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3339:
3340: l_base_cur_available :=
3341: GL_CURRENCY_API.Convert_closest_amount_sql
3342: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3347: , x_amount => l_cust_available
3348: , x_max_roll_days => -1
3349: );
3350:
3351: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3352: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3353: || TO_CHAR(l_base_cur_available ));
3354:
3355: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3348: , x_max_roll_days => -1
3349: );
3350:
3351: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3352: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3353: || TO_CHAR(l_base_cur_available ));
3354:
3355: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3356:
3351: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3352: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3353: || TO_CHAR(l_base_cur_available ));
3354:
3355: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3356:
3357: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
3358:
3359: l_global_exposure_flag :=
3353: || TO_CHAR(l_base_cur_available ));
3354:
3355: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3356:
3357: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
3358:
3359: l_global_exposure_flag :=
3360: OE_CREDIT_CHECK_UTIL.Get_global_exposure_flag
3361: ( p_entity_type => 'CUSTOMER'
3362: , p_entity_id => party_cust_csr_rec.customer_id
3363: , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
3364: ) ;
3365:
3366: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
3367: l_global_exposure_flag );
3368:
3369: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3370:
3365:
3366: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
3367: l_global_exposure_flag );
3368:
3369: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3370:
3371: -----insert data into temp table
3372: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3373: ( party_id
3415: , NULL
3416: , 'Y'
3417: );
3418:
3419: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
3420:
3421: ----end loop for customer credit profiles
3422: END LOOP;
3423:
3432: ----if global variable G_cust_incl_all_flag is 'Y'
3433: ----then unchecked exposure will be 0
3434: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
3435: THEN
3436: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
3437: so l_cust_unchk_exposure=0');
3438:
3439: l_cust_unchk_exposure:=0;
3440: ELSE
3437: so l_cust_unchk_exposure=0');
3438:
3439: l_cust_unchk_exposure:=0;
3440: ELSE
3441: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the customer='
3442: ||TO_CHAR(party_cust_csr_rec.customer_id));
3443:
3444: Get_unchecked_exposure
3445: ( p_party_id => NULL
3451: , x_return_status => l_return_status
3452: );
3453: END IF;
3454:
3455: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer='
3456: ||TO_CHAR(party_cust_csr_rec.customer_id) );
3457: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3458: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3459:
3453: END IF;
3454:
3455: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer='
3456: ||TO_CHAR(party_cust_csr_rec.customer_id) );
3457: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3458: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3459:
3460: IF l_return_status = 'C'
3461: THEN
3454:
3455: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer='
3456: ||TO_CHAR(party_cust_csr_rec.customer_id) );
3457: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3458: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3459:
3460: IF l_return_status = 'C'
3461: THEN
3462: l_return_status1:='C';
3470: THEN
3471: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3472: END IF;
3473:
3474: OE_DEBUG_PUB.ADD('Check if the customer has any credit profiles ');
3475: -----check if the customer has any credit profiles
3476: SELECT COUNT(*)
3477: INTO l_prof_count
3478: FROM
3485:
3486: IF l_prof_count>0
3487: THEN
3488:
3489: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
3490: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3491:
3492: ----update temp table with unchecked exposure
3493: UPDATE oe_credit_exposure_temp
3486: IF l_prof_count>0
3487: THEN
3488:
3489: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
3490: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
3491:
3492: ----update temp table with unchecked exposure
3493: UPDATE oe_credit_exposure_temp
3494: SET unchecked_exposure=l_cust_unchk_exposure
3500: -----there are no credit profiles for this customer
3501: ----insert unchecked exposure
3502: ELSE
3503:
3504: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
3505: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3506:
3507: -----insert data into temp table
3508: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3501: ----insert unchecked exposure
3502: ELSE
3503:
3504: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
3505: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
3506:
3507: -----insert data into temp table
3508: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3509: ( party_id
3551: , l_cust_unchk_exposure
3552: , 'Y'
3553: );
3554:
3555: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
3556:
3557: END IF; --end of checking if the customer has credit profile
3558:
3559: ----end loop for customers
3560: END LOOP;
3561:
3562: END LOOP; ----end loop for parties
3563:
3564: OE_DEBUG_PUB.ADD('Out Parties Loop 1 ');
3565:
3566: END IF; ---end of cheking if the party is part of the hierarchy
3567:
3568: -------- Customer Summary Report-----------
3570: ELSIF p_report_by_option='CUST_SUMMARY'
3571: OR p_report_by_option='CUST_DETAILS'
3572: THEN
3573:
3574: OE_DEBUG_PUB.ADD('IN Cust Summary Report');
3575:
3576: --Performance issue: start (SQL ID-16485806 FTS on HZ_CUST_ACCOUNTS and HZ_CUST_PROFILE_CLASSES)
3577: IF (p_customer_name_low IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3578: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3574: OE_DEBUG_PUB.ADD('IN Cust Summary Report');
3575:
3576: --Performance issue: start (SQL ID-16485806 FTS on HZ_CUST_ACCOUNTS and HZ_CUST_PROFILE_CLASSES)
3577: IF (p_customer_name_low IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3578: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3579: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3580:
3581: OPEN cust_cur FOR
3582: SELECT
3575:
3576: --Performance issue: start (SQL ID-16485806 FTS on HZ_CUST_ACCOUNTS and HZ_CUST_PROFILE_CLASSES)
3577: IF (p_customer_name_low IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3578: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3579: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3580:
3581: OPEN cust_cur FOR
3582: SELECT
3583: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3598: AND cp.STATUS='A' --14699527
3599: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3600: AND NVL(p_prof_class_high, cpc.name)) ;
3601: ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3602: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3603: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3604:
3605: OPEN cust_cur FOR
3606: SELECT
3599: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3600: AND NVL(p_prof_class_high, cpc.name)) ;
3601: ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_low IS NOT NULL) THEN
3602: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3603: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3604:
3605: OPEN cust_cur FOR
3606: SELECT
3607: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3621: AND cp.STATUS='A' --14699527
3622: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3623: AND NVL(p_prof_class_high, cpc.name)) ;
3624: ELSIF (p_customer_name_low IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3625: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3626: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3627:
3628: OPEN cust_cur FOR
3629: SELECT
3622: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3623: AND NVL(p_prof_class_high, cpc.name)) ;
3624: ELSIF (p_customer_name_low IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3625: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3626: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3627:
3628: OPEN cust_cur FOR
3629: SELECT
3630: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3644: AND cp.STATUS='A' --14699527
3645: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3646: AND NVL(p_prof_class_high, cpc.name)) ;
3647: ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3648: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3649: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3650:
3651: OPEN cust_cur FOR
3652: SELECT
3645: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3646: AND NVL(p_prof_class_high, cpc.name)) ;
3647: ELSIF (p_customer_name_high IS NOT NULL) AND (p_cust_number_high IS NOT NULL) THEN
3648: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3649: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3650:
3651: OPEN cust_cur FOR
3652: SELECT
3653: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3666: AND cp.STATUS='A' --14699527
3667: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3668: AND NVL(p_prof_class_high, cpc.name)) ;
3669: ELSIF (p_customer_name_low IS NOT NULL) THEN
3670: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3671:
3672: OPEN cust_cur FOR
3673: SELECT
3674: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3687: AND cp.STATUS='A' --14699527
3688: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3689: AND NVL(p_prof_class_high, cpc.name)) ;
3690: ELSIF (p_customer_name_high IS NOT NULL) THEN
3691: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3692:
3693: OPEN cust_cur FOR
3694: SELECT
3695: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3707: AND cp.STATUS='A' --14699527
3708: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3709: AND NVL(p_prof_class_high, cpc.name)) ;
3710: ELSIF (p_cust_number_low IS NOT NULL) THEN
3711: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3712:
3713: OPEN cust_cur FOR
3714: SELECT
3715: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3728: AND cp.STATUS='A' --14699527
3729: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3730: AND NVL(p_prof_class_high, cpc.name)) ;
3731: ELSIF (p_cust_number_high IS NOT NULL) THEN
3732: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3733:
3734: OPEN cust_cur FOR
3735: SELECT
3736: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3749: AND cpc.name BETWEEN NVL(p_prof_class_low, cpc.name)
3750: AND NVL(p_prof_class_high, cpc.name)) ;
3751: ELSIF (p_customer_name_low IS NULL) AND (p_customer_name_high IS NULL) AND
3752: (p_cust_number_high IS NULL) AND (p_cust_number_low IS NULL) THEN
3753: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3754: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3755: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3756: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3757:
3750: AND NVL(p_prof_class_high, cpc.name)) ;
3751: ELSIF (p_customer_name_low IS NULL) AND (p_customer_name_high IS NULL) AND
3752: (p_cust_number_high IS NULL) AND (p_cust_number_low IS NULL) THEN
3753: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3754: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3755: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3756: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3757:
3758: OPEN cust_cur FOR
3751: ELSIF (p_customer_name_low IS NULL) AND (p_customer_name_high IS NULL) AND
3752: (p_cust_number_high IS NULL) AND (p_cust_number_low IS NULL) THEN
3753: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3754: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3755: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3756: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3757:
3758: OPEN cust_cur FOR
3759: SELECT
3752: (p_cust_number_high IS NULL) AND (p_cust_number_low IS NULL) THEN
3753: OE_DEBUG_PUB.ADD('Customer name-LOW :' || p_customer_name_low);
3754: OE_DEBUG_PUB.ADD('Customer name-HIGH :' || p_customer_name_high);
3755: OE_DEBUG_PUB.ADD('Customer number-LOW :' || p_cust_number_low);
3756: OE_DEBUG_PUB.ADD('Customer number-HIGH:' || p_cust_number_high);
3757:
3758: OPEN cust_cur FOR
3759: SELECT
3760: SUBSTRB(party.party_name,1,50) ||'('||c.account_number||')' Customer
3795: , x_total_exposure => l_cust_total_exposure
3796: , x_return_status => l_return_status
3797: );
3798:
3799: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
3800: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3801:
3802: IF l_return_status = 'C'
3803: THEN
3796: , x_return_status => l_return_status
3797: );
3798:
3799: OE_DEBUG_PUB.ADD('OEXPCRGB: OUT of Get_Customer_Exposure ');
3800: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3801:
3802: IF l_return_status = 'C'
3803: THEN
3804: l_return_status1:='C';
3816: -----calculate available credit
3817: l_cust_available:=cust_prof_csr_rec.cust_overall_limit - l_cust_total_exposure;
3818:
3819: -----convert overall_credit_limit, exposure and available credit into base currency
3820: OE_DEBUG_PUB.ADD('IN Convert amounts ');
3821:
3822: l_base_cur_overall_limit :=
3823: GL_CURRENCY_API.Convert_closest_amount_sql
3824: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3829: , x_amount => cust_prof_csr_rec.cust_overall_limit
3830: , x_max_roll_days => -1
3831: );
3832:
3833: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3834: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3835:
3836: l_base_cur_exposure :=
3837: GL_CURRENCY_API.Convert_closest_amount_sql
3830: , x_max_roll_days => -1
3831: );
3832:
3833: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3834: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
3835:
3836: l_base_cur_exposure :=
3837: GL_CURRENCY_API.Convert_closest_amount_sql
3838: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3843: , x_amount => l_cust_total_exposure
3844: , x_max_roll_days => -1
3845: );
3846:
3847: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3848: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3849:
3850: l_base_cur_available :=
3851: GL_CURRENCY_API.Convert_closest_amount_sql
3844: , x_max_roll_days => -1
3845: );
3846:
3847: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3848: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
3849:
3850: l_base_cur_available :=
3851: GL_CURRENCY_API.Convert_closest_amount_sql
3852: ( x_from_currency => cust_prof_csr_rec.cust_currency_code
3857: , x_amount => l_cust_available
3858: , x_max_roll_days => -1
3859: );
3860:
3861: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3862: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3863: || TO_CHAR(l_base_cur_available ));
3864:
3865: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3858: , x_max_roll_days => -1
3859: );
3860:
3861: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3862: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3863: || TO_CHAR(l_base_cur_available ));
3864:
3865: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3866:
3861: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
3862: OE_DEBUG_PUB.ADD('l_base_cur_available = '
3863: || TO_CHAR(l_base_cur_available ));
3864:
3865: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3866:
3867: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
3868:
3869: l_global_exposure_flag :=
3863: || TO_CHAR(l_base_cur_available ));
3864:
3865: OE_DEBUG_PUB.ADD('OUT Convert amounts ');
3866:
3867: OE_DEBUG_PUB.ADD('call Get_global_exposure_flag ');
3868:
3869: l_global_exposure_flag :=
3870: OE_CREDIT_CHECK_UTIL.Get_global_exposure_flag
3871: ( p_entity_type => 'CUSTOMER'
3873: , p_limit_curr_code => cust_prof_csr_rec.cust_currency_code
3874: ) ;
3875:
3876:
3877: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
3878: l_global_exposure_flag );
3879:
3880: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3881:
3876:
3877: OE_DEBUG_PUB.ADD('l_global_exposure_flag => '||
3878: l_global_exposure_flag );
3879:
3880: OE_DEBUG_PUB.ADD('IN Insert data into temp table ');
3881:
3882: -----insert data into temp table
3883: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
3884: ( party_id
3926: , NULL
3927: , l_global_exposure_flag
3928: );
3929:
3930: OE_DEBUG_PUB.ADD('OUT Insert data into temp table ');
3931:
3932: ----end loop for customer credit profiles
3933: END LOOP;
3934:
3943: ----if global variable G_cust_incl_all_flag is 'Y'
3944: ----then unchecked exposure will be 0
3945: IF OE_Credit_Engine_GRP.G_cust_incl_all_flag='Y'
3946: THEN
3947: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_cust_incl_all_flag=Y,
3948: so l_cust_unchk_exposure=0');
3949:
3950: l_cust_unchk_exposure:=0;
3951: ELSE
3948: so l_cust_unchk_exposure=0');
3949:
3950: l_cust_unchk_exposure:=0;
3951: ELSE
3952: OE_DEBUG_PUB.ADD('IN Get_unchecked_exposure for the customer= '
3953: ||TO_CHAR(cust_csr_rec.customer_id));
3954:
3955: Get_unchecked_exposure
3956: ( p_party_id => NULL
3962: , x_return_status => l_return_status
3963: );
3964: END IF;
3965:
3966: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer= '
3967: ||TO_CHAR(cust_csr_rec.customer_id));
3968: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3969: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3970:
3964: END IF;
3965:
3966: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer= '
3967: ||TO_CHAR(cust_csr_rec.customer_id));
3968: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3969: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3970:
3971: IF l_return_status = 'C'
3972: THEN
3965:
3966: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for the customer= '
3967: ||TO_CHAR(cust_csr_rec.customer_id));
3968: OE_DEBUG_PUB.ADD('l_cust_unchk_exposure = '|| TO_CHAR(l_cust_unchk_exposure));
3969: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
3970:
3971: IF l_return_status = 'C'
3972: THEN
3973: l_return_status1:='C';
3981: THEN
3982: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3983: END IF;
3984:
3985: OE_DEBUG_PUB.ADD('Check if the customer has any credit profiles ');
3986: -----check if the customer has any credit profiles
3987: SELECT COUNT(*)
3988: INTO l_prof_count
3989: FROM
3996:
3997: IF l_prof_count>0
3998: THEN
3999:
4000: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
4001: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
4002:
4003: ----update temp table with unchecked exposure
4004: UPDATE oe_credit_exposure_temp
3997: IF l_prof_count>0
3998: THEN
3999:
4000: OE_DEBUG_PUB.ADD('Customer has credit profiles ');
4001: OE_DEBUG_PUB.ADD('Update temp table with unchecked exposure ');
4002:
4003: ----update temp table with unchecked exposure
4004: UPDATE oe_credit_exposure_temp
4005: SET unchecked_exposure=l_cust_unchk_exposure
4010: -----there are no credit profiles for this customer
4011: ----insert unchecked exposure
4012: ELSE
4013:
4014: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
4015: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
4016:
4017: -----insert data into temp table
4018: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4011: ----insert unchecked exposure
4012: ELSE
4013:
4014: OE_DEBUG_PUB.ADD('Customer does not have any credit profiles ');
4015: OE_DEBUG_PUB.ADD('IN Insert data into temp table for unchecked exposure ');
4016:
4017: -----insert data into temp table
4018: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4019: ( party_id
4061: , l_cust_unchk_exposure
4062: , NULL
4063: );
4064:
4065: OE_DEBUG_PUB.ADD('Out Insert data into temp table ');
4066:
4067: END IF;
4068:
4069: IF p_report_by_option='CUST_DETAILS'
4068:
4069: IF p_report_by_option='CUST_DETAILS'
4070: THEN
4071:
4072: OE_DEBUG_PUB.ADD('IN Customern Detail Report');
4073: --------Detail Report------------
4074:
4075: -------------------Bill-to Site Info-------------
4076: -----start loop for bill-to sites
4085: ----start loop for bill-to site credit profiles
4086: FOR site_prof_csr_rec IN site_prof_csr( p_customer_id => cust_csr_rec.customer_id
4087: , p_site_id => site_csr_rec.site_id)
4088: LOOP
4089: OE_DEBUG_PUB.ADD('IN loop for bill-to sites ');
4090: OE_DEBUG_PUB.ADD('Processing site '||site_csr_rec.Customer_site);
4091:
4092: ------calculate bill-to site credit exposure
4093: OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
4086: FOR site_prof_csr_rec IN site_prof_csr( p_customer_id => cust_csr_rec.customer_id
4087: , p_site_id => site_csr_rec.site_id)
4088: LOOP
4089: OE_DEBUG_PUB.ADD('IN loop for bill-to sites ');
4090: OE_DEBUG_PUB.ADD('Processing site '||site_csr_rec.Customer_site);
4091:
4092: ------calculate bill-to site credit exposure
4093: OE_CREDIT_ENGINE_GRP.Get_Customer_Exposure
4094: ( p_customer_id => cust_csr_rec.customer_id
4099: , x_return_status => l_return_status
4100: );
4101:
4102:
4103: OE_DEBUG_PUB.ADD('OUT of Get_Customer_Exposure ');
4104: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4105: OE_DEBUG_PUB.ADD('l_site_total_exposure = '||TO_CHAR(l_site_total_exposure));
4106:
4107: IF l_return_status = 'C'
4100: );
4101:
4102:
4103: OE_DEBUG_PUB.ADD('OUT of Get_Customer_Exposure ');
4104: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4105: OE_DEBUG_PUB.ADD('l_site_total_exposure = '||TO_CHAR(l_site_total_exposure));
4106:
4107: IF l_return_status = 'C'
4108: THEN
4101:
4102:
4103: OE_DEBUG_PUB.ADD('OUT of Get_Customer_Exposure ');
4104: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4105: OE_DEBUG_PUB.ADD('l_site_total_exposure = '||TO_CHAR(l_site_total_exposure));
4106:
4107: IF l_return_status = 'C'
4108: THEN
4109: l_return_status1:='C';
4132: , x_amount => site_prof_csr_rec.site_overall_limit
4133: , x_max_roll_days => -1
4134: );
4135:
4136: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4137: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
4138:
4139:
4140: l_base_cur_exposure :=
4133: , x_max_roll_days => -1
4134: );
4135:
4136: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4137: OE_DEBUG_PUB.ADD('l_base_cur_overall_limit = '|| TO_CHAR(l_base_cur_overall_limit));
4138:
4139:
4140: l_base_cur_exposure :=
4141: GL_CURRENCY_API.Convert_closest_amount_sql
4147: , x_amount => l_site_total_exposure
4148: , x_max_roll_days => -1
4149: );
4150:
4151: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4152: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
4153:
4154:
4155: l_base_cur_available :=
4148: , x_max_roll_days => -1
4149: );
4150:
4151: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4152: OE_DEBUG_PUB.ADD('l_base_cur_exposure = '|| TO_CHAR(l_base_cur_exposure));
4153:
4154:
4155: l_base_cur_available :=
4156: GL_CURRENCY_API.Convert_closest_amount_sql
4162: , x_amount => l_site_available
4163: , x_max_roll_days => -1
4164: );
4165:
4166: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4167: OE_DEBUG_PUB.ADD('l_base_cur_available = '|| TO_CHAR(l_base_cur_available));
4168:
4169: -----insert data into temp table
4170: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4163: , x_max_roll_days => -1
4164: );
4165:
4166: OE_DEBUG_PUB.ADD('GL_currency_api.Convert_closest_amount_sql ');
4167: OE_DEBUG_PUB.ADD('l_base_cur_available = '|| TO_CHAR(l_base_cur_available));
4168:
4169: -----insert data into temp table
4170: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4171: ( party_id
4222: ----then unchecked_exposure will be 0
4223:
4224: IF OE_Credit_Engine_GRP.G_site_incl_all_flag='Y'
4225: THEN
4226: OE_DEBUG_PUB.ADD('OE_Credit_Engine_GRP.G_site_incl_all_flag='
4227: ||OE_Credit_Engine_GRP.G_site_incl_all_flag
4228: ||'so l_site_unchk_exposure=0');
4229:
4230: l_site_unchk_exposure:=0;
4228: ||'so l_site_unchk_exposure=0');
4229:
4230: l_site_unchk_exposure:=0;
4231: ELSE
4232: OE_DEBUG_PUB.ADD('Start Get_unchecked_exposure for bill-to site ='
4233: ||TO_CHAR(site_csr_rec.site_id));
4234:
4235: Get_unchecked_exposure
4236: ( p_party_id => NULL
4242: , x_return_status => l_return_status
4243: );
4244: END IF;
4245:
4246: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for bill-to site '||
4247: TO_CHAR(site_csr_rec.site_id));
4248: OE_DEBUG_PUB.ADD('l_site_unchk_exposure = '|| TO_CHAR(l_site_unchk_exposure) );
4249: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4250:
4244: END IF;
4245:
4246: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for bill-to site '||
4247: TO_CHAR(site_csr_rec.site_id));
4248: OE_DEBUG_PUB.ADD('l_site_unchk_exposure = '|| TO_CHAR(l_site_unchk_exposure) );
4249: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4250:
4251: IF l_return_status = 'C'
4252: THEN
4245:
4246: OE_DEBUG_PUB.ADD('OUT of Get_unchecked_exposure,for bill-to site '||
4247: TO_CHAR(site_csr_rec.site_id));
4248: OE_DEBUG_PUB.ADD('l_site_unchk_exposure = '|| TO_CHAR(l_site_unchk_exposure) );
4249: OE_DEBUG_PUB.ADD('l_return_status = '|| l_return_status );
4250:
4251: IF l_return_status = 'C'
4252: THEN
4253: l_return_status1:='C';
4261: THEN
4262: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4263: END IF;
4264:
4265: OE_DEBUG_PUB.ADD('Check if the customer site has any credit profiles ');
4266:
4267: -----check if the customer site has any credit profiles
4268: SELECT COUNT(*)
4269: INTO l_prof_count1
4277:
4278: IF l_prof_count1>0
4279: THEN
4280:
4281: OE_DEBUG_PUB.ADD('Customer site has credit profiles ');
4282: OE_DEBUG_PUB.ADD('Update temp table with unchecked_exposure ');
4283:
4284: ----update temp table with unchecked exposure for bill-to site
4285: UPDATE oe_credit_exposure_temp
4278: IF l_prof_count1>0
4279: THEN
4280:
4281: OE_DEBUG_PUB.ADD('Customer site has credit profiles ');
4282: OE_DEBUG_PUB.ADD('Update temp table with unchecked_exposure ');
4283:
4284: ----update temp table with unchecked exposure for bill-to site
4285: UPDATE oe_credit_exposure_temp
4286: SET unchecked_exposure=l_site_unchk_exposure
4290: -----there are no credit profiles for this customer site
4291: -----insert unchecked exposure into temp table
4292: ELSE
4293:
4294: OE_DEBUG_PUB.ADD('Customer site does not have credit profiles ');
4295: OE_DEBUG_PUB.ADD('Insert into temp table unchecked_exposure ');
4296:
4297: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4298: ( party_id
4291: -----insert unchecked exposure into temp table
4292: ELSE
4293:
4294: OE_DEBUG_PUB.ADD('Customer site does not have credit profiles ');
4295: OE_DEBUG_PUB.ADD('Insert into temp table unchecked_exposure ');
4296:
4297: INSERT INTO OE_CREDIT_EXPOSURE_TEMP
4298: ( party_id
4299: , party_name
4340: , l_site_unchk_exposure
4341: , 'N'
4342: );
4343:
4344: OE_DEBUG_PUB.ADD('Out of the insert into temp table ');
4345:
4346: END IF;
4347:
4348: -----end loop for bill-to sites
4362: FROM OE_CREDIT_EXPOSURE_TEMP;
4363: END IF;
4364:
4365: COMMIT;
4366: OE_DEBUG_PUB.ADD('Out of OEXRCRCB:Credit_exposure_report_utils with the status='||x_return_status);
4367:
4368: EXCEPTION
4369:
4370: WHEN GL_CURRENCY_API.NO_RATE THEN
4367:
4368: EXCEPTION
4369:
4370: WHEN GL_CURRENCY_API.NO_RATE THEN
4371: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - GL_CURRENCY_API.NO_RATE');
4372: RAISE;
4373: WHEN OTHERS THEN
4374: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - Unexpected Error',1);
4375: OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
4370: WHEN GL_CURRENCY_API.NO_RATE THEN
4371: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - GL_CURRENCY_API.NO_RATE');
4372: RAISE;
4373: WHEN OTHERS THEN
4374: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - Unexpected Error',1);
4375: OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
4376:
4377: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4378: OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Credit_exposure_report_utils');
4371: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - GL_CURRENCY_API.NO_RATE');
4372: RAISE;
4373: WHEN OTHERS THEN
4374: OE_DEBUG_PUB.ADD('OEXRCRCB: Credit_exposure_report_utils - Unexpected Error',1);
4375: OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
4376:
4377: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4378: OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Credit_exposure_report_utils');
4379: END IF;