[Home] [Help]
15: IS
16: l_maximum_days_past_due NUMBER;
17: l_dummy VARCHAR2(30);
18: --
19: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
20: --
21:
22: BEGIN
23: -- Default to pass back
31: if l_maximum_days_past_due > 0 then
32: -- Check to see if there is any unpaid invoice that is past the
33: -- due date.
34: if l_debug_level > 0 then
35: oe_debug_pub.ADD('OEXPCRCB.pls: maximum_days_past_due:' ||to_char(l_maximum_days_past_due) );
36: end if;
37: BEGIN
38: -- Default to Y, in case there is one or more invoices due.
39: p_check_past_due := 'Y';
51:
52: WHEN NO_DATA_FOUND THEN
53: p_check_past_due := 'N';
54: if l_debug_level > 0 then
55: oe_debug_pub.ADD('OEXPCRCB.pls: No Invoices Past due' );
56: end if;
57: WHEN TOO_MANY_ROWS THEN
58: null;
59: END;
61:
62: end if;
63:
64: if l_debug_level > 0 then
65: oe_debug_pub.ADD('OEXPCRCB.pls: Past due Invoice Check:' || p_check_past_due);
66: end if;
67:
68: EXCEPTION
69:
138: INTO l_order_commitment
139: FROM OE_PAYMENTS
140: WHERE HEADER_ID = p_header_rec.header_id;
141:
142: oe_debug_pub.ADD('OEXPCRCB.pls: order commitment total:' || l_order_commitment);
143:
144: -- get the actual order value subject to credit check.
145: l_order_value := l_order_value - l_order_commitment;
146:
149: -- If credit available is less than the total exposure or
150: -- if the order amount is greater than the transaction limit
151: -- Return Failure
152:
153: oe_debug_pub.ADD('OEXPCRCB.pls: total exposure is:' ||p_total_exposure );
154: oe_debug_pub.ADD('OEXPCRCB.pls: total credit limit:' || p_overall_credit_limit);
155: oe_debug_pub.ADD('OEXPCRCB.pls: total order value:' || l_order_value);
156: oe_debug_pub.ADD('OEXPCRCB.pls: order credit limit:' || p_trx_credit_limit);
157: -- Replaced this code
150: -- if the order amount is greater than the transaction limit
151: -- Return Failure
152:
153: oe_debug_pub.ADD('OEXPCRCB.pls: total exposure is:' ||p_total_exposure );
154: oe_debug_pub.ADD('OEXPCRCB.pls: total credit limit:' || p_overall_credit_limit);
155: oe_debug_pub.ADD('OEXPCRCB.pls: total order value:' || l_order_value);
156: oe_debug_pub.ADD('OEXPCRCB.pls: order credit limit:' || p_trx_credit_limit);
157: -- Replaced this code
158: -- IF l_order_value > p_trx_credit_limit OR
151: -- Return Failure
152:
153: oe_debug_pub.ADD('OEXPCRCB.pls: total exposure is:' ||p_total_exposure );
154: oe_debug_pub.ADD('OEXPCRCB.pls: total credit limit:' || p_overall_credit_limit);
155: oe_debug_pub.ADD('OEXPCRCB.pls: total order value:' || l_order_value);
156: oe_debug_pub.ADD('OEXPCRCB.pls: order credit limit:' || p_trx_credit_limit);
157: -- Replaced this code
158: -- IF l_order_value > p_trx_credit_limit OR
159: -- p_total_exposure > p_overall_credit_limit THEN
152:
153: oe_debug_pub.ADD('OEXPCRCB.pls: total exposure is:' ||p_total_exposure );
154: oe_debug_pub.ADD('OEXPCRCB.pls: total credit limit:' || p_overall_credit_limit);
155: oe_debug_pub.ADD('OEXPCRCB.pls: total order value:' || l_order_value);
156: oe_debug_pub.ADD('OEXPCRCB.pls: order credit limit:' || p_trx_credit_limit);
157: -- Replaced this code
158: -- IF l_order_value > p_trx_credit_limit OR
159: -- p_total_exposure > p_overall_credit_limit THEN
160: -- p_result_out := 'FAIL';
157: -- Replaced this code
158: -- IF l_order_value > p_trx_credit_limit OR
159: -- p_total_exposure > p_overall_credit_limit THEN
160: -- p_result_out := 'FAIL';
161: -- oe_debug_pub.ADD('Over credit limit');
162: -- END IF;
163:
164: -- With this
165: if (p_trx_credit_limit <> -1) then
164: -- With this
165: if (p_trx_credit_limit <> -1) then
166: if (l_order_value > p_trx_credit_limit) then
167: p_result_out := 'FAIL';
168: oe_debug_pub.ADD('Order Value greater then Transaction Limit');
169: end if;
170: end if;
171: if (p_overall_credit_limit <> -1) then
172: if (p_total_exposure > p_overall_credit_limit) then
170: end if;
171: if (p_overall_credit_limit <> -1) then
172: if (p_total_exposure > p_overall_credit_limit) then
173: p_result_out := 'FAIL';
174: oe_debug_pub.ADD('Total Exposure is greater then Overall Credit Limit');
175: end if;
176: end if;
177:
178:
218: l_result_out VARCHAR2(30);
219: l_return_status VARCHAR2(30);
220: l_check_past_due VARCHAR2(1); -- if any invoice is past due
221: --
222: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
223: --
224:
225: BEGIN
226:
224:
225: BEGIN
226:
227: -- Set the default behaviour to pass credit check
228: -- oe_debug_pub.debug_on;
229:
230: p_result_out := 'PASS';
231: p_return_status := FND_API.G_RET_STS_SUCCESS;
232:
229:
230: p_result_out := 'PASS';
231: p_return_status := FND_API.G_RET_STS_SUCCESS;
232:
233: oe_debug_pub.ADD('Calling action is '|| p_calling_action);
234:
235: -- The first thing to do is to load the record structure for the order header
236: -- This is done in the OE_HEADER_UTIL package by the Query Row function.
237: -- The caller must pass a Header id and the function returns the record
236: -- This is done in the OE_HEADER_UTIL package by the Query Row function.
237: -- The caller must pass a Header id and the function returns the record
238: -- Structure l_header_rec
239:
240: oe_debug_pub.ADD('Before querying');
241: OE_HEADER_UTIL.QUERY_ROW(p_header_id=>p_header_id,x_header_rec=>l_header_rec);
242:
243: -- Now we have the Record Structure loaded we can call the other
244: -- functions without having to go to the database.
245: -- Checking whether the order should undergo a credit check. Also
246: -- returns whether the check should be at the customer level or the
247: -- bill-to site level and the credit limits at that level.
248:
249: oe_debug_pub.ADD('just before the check order procedure');
250: OE_Credit_PUB.Check_Order
251: ( l_header_rec
252: , p_calling_action
253: , l_check_order
268:
269: -- If the Order is subject to Credit Check i.e. l_check_order = 'Y'
270: -- First check if there are any unpaid invoices that are passed the
271: -- maximum due dates.
272: oe_debug_pub.ADD('Calling Check Past Due Invoice procedure');
273:
274: oe_credit_pub.chk_past_due_invoice (
275: l_header_rec
276: ,l_credit_rule_id
287: IF l_check_past_due = 'N' THEN
288: -- Determine total exposure.
289:
290: if l_debug_level > 0 then
291: oe_debug_pub.ADD('Calling the check exposure procedure');
292: end if;
293:
294: OE_Credit_PUB.Check_Exposure
295: ( l_header_rec
304: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
305: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
306: END IF;
307:
308: oe_debug_pub.ADD(' Credit Rule Id ='||to_char(l_credit_rule_id));
309:
310: -- Next, compare the order amount and the exposure to the
311: -- order credit limit and total credit limit.
312:
322: , l_result_out
323: , l_return_status
324: );
325:
326: oe_debug_pub.add('After the call for check_Trx_Limit');
327: oe_debug_pub.add('Result out ='||l_result_out);
328: oe_debug_pub.add('Return Status ='||l_return_status);
329:
330: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
323: , l_return_status
324: );
325:
326: oe_debug_pub.add('After the call for check_Trx_Limit');
327: oe_debug_pub.add('Result out ='||l_result_out);
328: oe_debug_pub.add('Return Status ='||l_return_status);
329:
330: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
331: RAISE FND_API.G_EXC_ERROR;
324: );
325:
326: oe_debug_pub.add('After the call for check_Trx_Limit');
327: oe_debug_pub.add('Result out ='||l_result_out);
328: oe_debug_pub.add('Return Status ='||l_return_status);
329:
330: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
331: RAISE FND_API.G_EXC_ERROR;
332: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
335:
336: p_result_out := l_result_out;
337:
338: ELSE -- l_check_past_due = 'N'
339: oe_debug_pub.ADD('Past due Invoices: Credit Check Failed');
340: p_result_out := 'FAIL';
341: END IF; -- l_check_past_due = 'N'
342: ELSE -- if credit check order = N
343:
340: p_result_out := 'FAIL';
341: END IF; -- l_check_past_due = 'N'
342: ELSE -- if credit check order = N
343:
344: oe_debug_pub.ADD('No credit check required');
345: -- FND_MESSAGE.SET_NAME('OE', 'OE_NO_CREDIT_CHECK_REQUIRED');
346: -- FND_MSG_PUB.ADD;
347: -- null;
348:
347: -- null;
348:
349: END IF;
350:
351: -- oe_debug_pub.dumpdebug;
352: -- oe_debug_pub.debug_off;
353:
354: -- Count the Messages on the Message Stack and if only 1 return it in
355: -- message data.
348:
349: END IF;
350:
351: -- oe_debug_pub.dumpdebug;
352: -- oe_debug_pub.debug_off;
353:
354: -- Count the Messages on the Message Stack and if only 1 return it in
355: -- message data.
356: -- If more than 1 just return the count. The Calling routine has to get
430: -- Order Being Credit Checked.
431: -- If Called from Validating the Order at Order Entry use the
432: -- Entry Credit Check Rule.
433:
434: oe_debug_pub.ADD('Which cchk rule');
435: IF p_calling_action = 'BOOKING' THEN
436:
437: oe_debug_pub.ADD('Selecting the order entry cchk rule');
438: /*7194250
433:
434: oe_debug_pub.ADD('Which cchk rule');
435: IF p_calling_action = 'BOOKING' THEN
436:
437: oe_debug_pub.ADD('Selecting the order entry cchk rule');
438: /*7194250
439: SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
440: INTO l_credit_check_rule_id
441: FROM OE_ORDER_TYPES_V
462:
463: -- If not Use the Shipping Rule for all other calling Actions
464: ELSE
465:
466: oe_debug_pub.ADD('Selecting the shipping cchk rule');
467: /*7194250
468: SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
469: INTO l_credit_check_rule_id
470: FROM OE_ORDER_TYPES_V
493:
494: -- If no credit rule was found for the calling action the Order
495: -- is not subject to credit check
496:
497: oe_debug_pub.ADD('check order after selecting credit rule : '|| l_check_order);
498: IF l_credit_check_rule_id <= 0 THEN
499: oe_debug_pub.ADD('no cchk rule found');
500: l_check_order := 'N';
501: END IF;
495: -- is not subject to credit check
496:
497: oe_debug_pub.ADD('check order after selecting credit rule : '|| l_check_order);
498: IF l_credit_check_rule_id <= 0 THEN
499: oe_debug_pub.ADD('no cchk rule found');
500: l_check_order := 'N';
501: END IF;
502:
503: -- If the Order type is subject to credit check we should check the
516: INTO l_invoice_to_cust_id
517: FROM OE_INVOICE_TO_ORGS_V
518: WHERE ORGANIZATION_ID = p_header_rec.invoice_to_org_id;
519:
520: oe_debug_pub.ADD('Invoice To Customer Id: '||to_char(l_invoice_to_cust_id));
521:
522: BEGIN
523:
524: SELECT NVL(CP.CREDIT_CHECKING, 'N')
550: WHEN NO_DATA_FOUND THEN
551: -- If we do not find a Credit Profile we will assume that the credit limits are set
552: -- at the customer level.
553:
554: oe_debug_pub.ADD('Customer level credit check');
555: l_credit_check_lvl_out := 'CUSTOMER';
556:
557: WHEN OTHERS THEN
558:
574: END IF;
575:
576: END IF; -- Check if the Order type is subject to credit check for site or customer
577:
578: oe_debug_pub.ADD('check order after selecting site/customer level : ' || l_check_order);
579: oe_debug_pub.ADD('level for credit check: '|| l_credit_check_lvl_out);
580: IF l_check_order = 'Y' AND l_credit_check_lvl_out = 'CUSTOMER' THEN
581:
582: -- If both the Order type and the Bill to are subject to credit check
575:
576: END IF; -- Check if the Order type is subject to credit check for site or customer
577:
578: oe_debug_pub.ADD('check order after selecting site/customer level : ' || l_check_order);
579: oe_debug_pub.ADD('level for credit check: '|| l_credit_check_lvl_out);
580: IF l_check_order = 'Y' AND l_credit_check_lvl_out = 'CUSTOMER' THEN
581:
582: -- If both the Order type and the Bill to are subject to credit check
583: -- and no credit profile was found at the bill to site go on to
601: AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code
602: AND CP.STATUS='A' --14699527
603: AND CP.SITE_USE_ID IS NULL;
604:
605: oe_debug_pub.ADD('limit test:'|| l_credit_limit_test );
606:
607: EXCEPTION
608:
609: WHEN NO_DATA_FOUND THEN
627: IF l_credit_limit_test < 0 THEN
628: l_check_order := 'N';
629: END IF;
630:
631: oe_debug_pub.ADD('check order after determining credit limits :'
632: || l_check_order);
633: END IF; -- The Order type Subject to Credit Check No credit profile at bill to.
634:
635: IF l_check_order = 'Y' THEN
677: p_credit_check_lvl_out := l_credit_check_lvl_out;
678: p_credit_rule_out := l_credit_check_rule_id;
679: p_check_order_out := l_check_order;
680:
681: oe_debug_pub.ADD('check order after checking payment term :'|| l_check_order);
682: EXCEPTION
683:
684: WHEN OTHERS THEN
685: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1230: -- As in R10SC/R11, if the schedule_date (or when this field is null, request_date)
1231: -- is within the no. of shipping_interval days of the current date, include only
1232: -- those amounts for calculating uninvoiced orders total.
1233:
1234: oe_debug_pub.ADD('In Check Exposure');
1235: OPEN credit_check_rule;
1236: /* additional task */
1237: FETCH credit_check_rule INTO l_open_ar_balance_flag
1238: , l_open_ar_days
1242: , l_shipping_horizon
1243: , l_include_risk_flag;
1244: CLOSE credit_check_rule;
1245:
1246: oe_debug_pub.ADD('Credit Check Rule is as follows :');
1247: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1248: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1249: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1250: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1243: , l_include_risk_flag;
1244: CLOSE credit_check_rule;
1245:
1246: oe_debug_pub.ADD('Credit Check Rule is as follows :');
1247: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1248: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1249: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1250: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1251: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1244: CLOSE credit_check_rule;
1245:
1246: oe_debug_pub.ADD('Credit Check Rule is as follows :');
1247: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1248: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1249: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1250: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1251: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1252: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1245:
1246: oe_debug_pub.ADD('Credit Check Rule is as follows :');
1247: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1248: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1249: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1250: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1251: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1252: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1253: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1246: oe_debug_pub.ADD('Credit Check Rule is as follows :');
1247: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1248: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1249: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1250: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1251: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1252: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1253: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1254:
1247: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1248: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1249: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1250: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1251: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1252: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1253: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1254:
1255: /* additional task */
1248: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1249: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1250: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1251: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1252: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1253: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1254:
1255: /* additional task */
1256: l_est_valid_days := to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0') ) ;
1249: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1250: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1251: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1252: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1253: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1254:
1255: /* additional task */
1256: l_est_valid_days := to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0') ) ;
1257: oe_debug_pub.ADD('Estimated Valid Days ='||to_char(l_est_valid_days));
1253: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1254:
1255: /* additional task */
1256: l_est_valid_days := to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0') ) ;
1257: oe_debug_pub.ADD('Estimated Valid Days ='||to_char(l_est_valid_days));
1258:
1259: -- Move the following SQL out of if clause for customer credit level
1260: -- because even for site credit level, we need to pass pay_from_customer
1261: /* Find Customer Id of the Header Invoice To Org */
1263: INTO l_invoice_to_cust_id
1264: FROM OE_INVOICE_TO_ORGS_V
1265: WHERE ORGANIZATION_ID = p_header_rec.invoice_to_org_id;
1266:
1267: oe_debug_pub.ADD('Invoice To Customer Id: '||to_char(l_invoice_to_cust_id));
1268:
1269: IF p_credit_level = 'CUSTOMER' THEN -- Retrieving exposure at CUSTOMER level
1270:
1271:
1292: CLOSE ar_balance_in_ar_days;
1293:
1294: END IF; -- If open_ar_days is null
1295:
1296: oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
1297:
1298: /* If the include payments at risk flag is set to yes
1299: Update the exposure by payments that are not thought to be collectable
1300: These payments are in the cash receipts history
1322: CLOSE pay_risk_in_ar_days;
1323:
1324: END IF; -- If open_ar_days is null
1325:
1326: oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
1327:
1328: END IF; -- Include Payments at Risk
1329:
1330:
1330:
1331: -- Now update the total exposure value.
1332:
1333: l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
1334: oe_debug_pub.ADD(' Accounts Receivables Exposure ='||to_char(l_total_exposure));
1335:
1336: END IF; -- checking accounts receivables exposure
1337:
1338:
1347: BEGIN
1348: OPEN uninvoiced_orders(l_include_tax_flag);
1349: FETCH uninvoiced_orders INTO l_total_on_order;
1350: IF uninvoiced_orders%notfound THEN
1351: oe_debug_pub.ADD('not found any uninvoiced orders');
1352: l_total_on_order := 0;
1353: END IF;
1354: CLOSE uninvoiced_orders;
1355:
1353: END IF;
1354: CLOSE uninvoiced_orders;
1355:
1356: /* WHEN others THEN
1357: oe_debug_pub.ADD('not found');*/
1358: END;
1359:
1360:
1361: -- Now update the total exposure value to include the Order Backlog Value
1359:
1360:
1361: -- Now update the total exposure value to include the Order Backlog Value
1362:
1363: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
1364: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
1365:
1366: oe_debug_pub.ADD('Exposure after taking care of uninvoiced orders only ='||to_char(l_total_exposure));
1367:
1362:
1363: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
1364: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
1365:
1366: oe_debug_pub.ADD('Exposure after taking care of uninvoiced orders only ='||to_char(l_total_exposure));
1367:
1368: /* Next check if we should be excluding orders that are already on hold
1369: from the calculation of Total exposure
1370: NOTE: If l_orders_on_hold_flag = 'Y', that means INCLUDE the value of orders on
1386: CLOSE orders_on_hold;
1387:
1388:
1389: -- Now update the total exposure value to EXCLUDE the value of orders on hold
1390: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
1391:
1392: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
1393:
1394: oe_debug_pub.ADD('Total exposure after taking care of Hold ='||to_char(l_total_exposure));
1390: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
1391:
1392: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
1393:
1394: oe_debug_pub.ADD('Total exposure after taking care of Hold ='||to_char(l_total_exposure));
1395:
1396: END IF; -- orders on hold flag
1397:
1398: -- Check Commitment Total if Commitment Sequencing "On"
1404: l_total_commitment := 0;
1405: END IF;
1406: CLOSE commitment_total;
1407:
1408: oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
1409:
1410: -- If orders on hold are to be excluded then find out
1411: -- the commitment amount associated to orders on hold
1412: IF l_orders_on_hold_flag = 'N' THEN
1417: END IF;
1418:
1419: CLOSE on_hold_commitment_total;
1420:
1421: oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
1422: END IF;
1423:
1424: OPEN current_commitment_total;
1425: FETCH current_commitment_total INTO l_current_commitment;
1427: l_current_commitment := 0;
1428: END IF;
1429: CLOSE current_commitment_total;
1430:
1431: oe_debug_pub.ADD('Current Order Commitment Amount: ' || l_current_commitment);
1432:
1433: l_total_commitment := l_total_commitment + l_current_commitment - nvl(l_on_hold_commitment, 0);
1434:
1435: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
1431: oe_debug_pub.ADD('Current Order Commitment Amount: ' || l_current_commitment);
1432:
1433: l_total_commitment := l_total_commitment + l_current_commitment - nvl(l_on_hold_commitment, 0);
1434:
1435: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
1436:
1437: -- Now update the total exposure value to EXCLUDE already applied Commitments.
1438: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
1439:
1436:
1437: -- Now update the total exposure value to EXCLUDE already applied Commitments.
1438: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
1439:
1440: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
1441:
1442: END IF; -- Commitment Sequencing
1443:
1444: /* additional task - ADD Current Order value to the calculated exposure */
1451: END IF;
1452: CLOSE current_order;
1453:
1454: l_total_exposure := l_total_exposure + NVL(l_current_order,0);
1455: oe_debug_pub.ADD('Total exposure after taking care of Current Order (CUSTOMER level)='||to_char(l_total_exposure));
1456:
1457:
1458: END IF; -- uninvoiced order flag
1459:
1483: CLOSE site_ar_balance_in_ar_days;
1484:
1485: END IF; -- If open_ar_days is null
1486:
1487: oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
1488:
1489: /* If the include payments at risk flag is set to yes
1490: Update the exposure by payments that are not thought to be collectable
1491: These payments are in the cash receipts history
1513: CLOSE site_pay_risk_in_ar_days;
1514:
1515: END IF; -- If open_ar_days is null
1516:
1517: oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
1518:
1519: END IF; -- Include Payments at Risk
1520:
1521: -- Now update the total exposure value.
1520:
1521: -- Now update the total exposure value.
1522: l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
1523:
1524: oe_debug_pub.ADD('Exposure from a/c receivables ='||to_char(l_total_exposure));
1525:
1526: END IF; -- checking accounts receivables exposure
1527:
1528:
1545:
1546:
1547: -- Now update the total exposure value to include the Order Backlog Value
1548:
1549: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
1550: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
1551:
1552: oe_debug_pub.ADD('Exposure after uninvoiced orders ='||to_char(l_total_exposure));
1553:
1548:
1549: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
1550: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
1551:
1552: oe_debug_pub.ADD('Exposure after uninvoiced orders ='||to_char(l_total_exposure));
1553:
1554: /* Next check if we should be excluding orders that are already on hold
1555: from the calculation of Total exposure
1556: NOTE: If l_orders_on_hold_flag = 'Y', that means INCLUDE the value of orders
1572: END IF;
1573: CLOSE site_orders_on_hold;
1574:
1575: -- Now update the total exposure value to EXCLUDE the value of orders on hold
1576: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
1577: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
1578:
1579: oe_debug_pub.ADD('Total Exposure after taking care of Hold ='||to_char(l_total_exposure));
1580:
1575: -- Now update the total exposure value to EXCLUDE the value of orders on hold
1576: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
1577: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
1578:
1579: oe_debug_pub.ADD('Total Exposure after taking care of Hold ='||to_char(l_total_exposure));
1580:
1581: END IF; -- check orders on hold
1582:
1583: -- Check Commitment Total if Commitment Sequencing "On"
1589: l_total_commitment := 0;
1590: END IF;
1591: CLOSE site_commitment_total;
1592:
1593: oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
1594:
1595: -- If orders on hold are to be excluded then find out
1596: -- the commitment amount associated to orders on hold
1597: IF l_orders_on_hold_flag = 'N' THEN
1602: END IF;
1603:
1604: CLOSE site_on_hold_commitment_total;
1605:
1606: oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
1607: END IF;
1608:
1609: l_total_commitment := l_total_commitment - nvl(l_on_hold_commitment, 0);
1610:
1608:
1609: l_total_commitment := l_total_commitment - nvl(l_on_hold_commitment, 0);
1610:
1611: -- Now update the total exposure value to EXCLUDE already applied Commitments.
1612: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
1613:
1614: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
1615:
1616: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
1612: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
1613:
1614: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
1615:
1616: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
1617:
1618: END IF; -- Commitment Sequencing
1619:
1620: /* additional task - ADD Current Order */
1627: END IF;
1628: CLOSE current_order;
1629:
1630: l_total_exposure := l_total_exposure + NVL(l_current_order,0);
1631: oe_debug_pub.ADD('Total exposure after taking care of Current Order (SITE level) ='||to_char(l_total_exposure));
1632:
1633: END IF; -- uninvoiced order flag
1634:
1635: END IF; -- credit exposure at site level or customer level
1634:
1635: END IF; -- credit exposure at site level or customer level
1636:
1637: -- Load the Out Variable to be returned.
1638: oe_debug_pub.ADD('OUTPUT total exposure: '|| l_total_exposure);
1639: p_total_exposure := l_total_exposure;
1640:
1641:
1642: EXCEPTION
1678:
1679: if l_maximum_days_past_due > 0 then
1680: -- Check to see if there is any unpaid invoice that is past the
1681: -- due date.
1682: oe_debug_pub.ADD('OEXPCRCB.pls: line level maximum_days_past_due:' ||to_char(l_maximum_days_past_due) );
1683: BEGIN
1684: -- Default to Y, in case there is one or more invoices due.
1685: p_check_past_due := 'Y';
1686: select 'Any Past due invoice'
1694: EXCEPTION
1695:
1696: WHEN NO_DATA_FOUND THEN
1697: p_check_past_due := 'N';
1698: oe_debug_pub.ADD('OEXPCRCB.pls: No Invoices Past due -- line level' );
1699: WHEN TOO_MANY_ROWS THEN
1700: null;
1701: END;
1702:
1701: END;
1702:
1703:
1704: end if;
1705: oe_debug_pub.ADD('OEXPCRCB.pls: Line level Past due Invoice Check:' || p_check_past_due);
1706: EXCEPTION
1707:
1708: WHEN others THEN
1709: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1802: WHERE P.HEADER_ID = p_header_rec.header_id
1803: AND L.LINE_ID = P.LINE_ID
1804: AND L.INVOICE_TO_ORG_ID = p_invoice_to_org_id;
1805:
1806: oe_debug_pub.ADD('OEXPCRCB.pls: line level trx commitment total:' || l_order_commitment);
1807:
1808: -- get the actual order value subject to credit check.
1809: l_order_value := l_order_value - l_order_commitment;
1810:
1813: -- If credit available is less than the total exposure or
1814: -- if the order amount is greater than the transaction limit
1815: -- Return Failure
1816:
1817: oe_debug_pub.ADD('OEXPCRCB.pls: line level total exposure is:' ||p_total_exposure );
1818: oe_debug_pub.ADD('OEXPCRCB.pls: line level total credit limit:' || p_overall_credit_limit);
1819: oe_debug_pub.ADD('OEXPCRCB.pls: line level total order value:' || l_order_value);
1820: oe_debug_pub.ADD('OEXPCRCB.pls: line level order credit limit:' || p_trx_credit_limit);
1821: -- Replaced this code
1814: -- if the order amount is greater than the transaction limit
1815: -- Return Failure
1816:
1817: oe_debug_pub.ADD('OEXPCRCB.pls: line level total exposure is:' ||p_total_exposure );
1818: oe_debug_pub.ADD('OEXPCRCB.pls: line level total credit limit:' || p_overall_credit_limit);
1819: oe_debug_pub.ADD('OEXPCRCB.pls: line level total order value:' || l_order_value);
1820: oe_debug_pub.ADD('OEXPCRCB.pls: line level order credit limit:' || p_trx_credit_limit);
1821: -- Replaced this code
1822: -- IF l_order_value > p_trx_credit_limit OR
1815: -- Return Failure
1816:
1817: oe_debug_pub.ADD('OEXPCRCB.pls: line level total exposure is:' ||p_total_exposure );
1818: oe_debug_pub.ADD('OEXPCRCB.pls: line level total credit limit:' || p_overall_credit_limit);
1819: oe_debug_pub.ADD('OEXPCRCB.pls: line level total order value:' || l_order_value);
1820: oe_debug_pub.ADD('OEXPCRCB.pls: line level order credit limit:' || p_trx_credit_limit);
1821: -- Replaced this code
1822: -- IF l_order_value > p_trx_credit_limit OR
1823: -- p_total_exposure > p_overall_credit_limit THEN
1816:
1817: oe_debug_pub.ADD('OEXPCRCB.pls: line level total exposure is:' ||p_total_exposure );
1818: oe_debug_pub.ADD('OEXPCRCB.pls: line level total credit limit:' || p_overall_credit_limit);
1819: oe_debug_pub.ADD('OEXPCRCB.pls: line level total order value:' || l_order_value);
1820: oe_debug_pub.ADD('OEXPCRCB.pls: line level order credit limit:' || p_trx_credit_limit);
1821: -- Replaced this code
1822: -- IF l_order_value > p_trx_credit_limit OR
1823: -- p_total_exposure > p_overall_credit_limit THEN
1824: -- p_result_out := 'FAIL';
1821: -- Replaced this code
1822: -- IF l_order_value > p_trx_credit_limit OR
1823: -- p_total_exposure > p_overall_credit_limit THEN
1824: -- p_result_out := 'FAIL';
1825: -- oe_debug_pub.ADD('Over credit limit');
1826: -- END IF;
1827:
1828: -- With this
1829: if (p_trx_credit_limit <> -1) then
1828: -- With this
1829: if (p_trx_credit_limit <> -1) then
1830: if (l_order_value > p_trx_credit_limit) then
1831: p_result_out := 'FAIL';
1832: oe_debug_pub.ADD('Line Level: Order Value greater then Transaction Limit.');
1833: end if;
1834: end if;
1835: if (p_overall_credit_limit <> -1) then
1836: if (p_total_exposure > p_overall_credit_limit) then
1834: end if;
1835: if (p_overall_credit_limit <> -1) then
1836: if (p_total_exposure > p_overall_credit_limit) then
1837: p_result_out := 'FAIL';
1838: oe_debug_pub.ADD('Line Level: Total Exposure is greater then Overall Credit Limit');
1839: end if;
1840: end if;
1841:
1842:
1888: l_customer_id NUMBER;
1889: BEGIN
1890:
1891: -- Set the default behaviour to pass credit check
1892: -- oe_debug_pub.debug_on;
1893:
1894: p_result_out := 'PASS';
1895: p_return_status := FND_API.G_RET_STS_SUCCESS;
1896:
1893:
1894: p_result_out := 'PASS';
1895: p_return_status := FND_API.G_RET_STS_SUCCESS;
1896:
1897: oe_debug_pub.ADD('Line Level: Calling action is '|| p_calling_action);
1898:
1899: -- The first thing to do is to load the record structure for the order header
1900: -- This is done in the OE_HEADER_UTIL package by the Query Row function.
1901: -- The caller must pass a Header id and the function returns the record
1900: -- This is done in the OE_HEADER_UTIL package by the Query Row function.
1901: -- The caller must pass a Header id and the function returns the record
1902: -- Structure l_header_rec
1903:
1904: oe_debug_pub.ADD('Line Level: Before querying');
1905: OE_HEADER_UTIL.QUERY_ROW(p_header_id => p_header_id
1906: ,x_header_rec => l_header_rec);
1907:
1908: -- OE_LINE_UTIL.Query_Row(p_line_id => p_line_id
1920: --FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SITE_USE_ID');
1921: --OE_MSG_PUB.ADD;
1922: --fnd_message.set_token('SITE_USE_ID',
1923: -- to_char(p_hold_entity_id));
1924: OE_Debug_PUB.Add('OEXPCRCB: Line Level: No Customer ID for Bill to:' ||
1925: to_char(p_invoice_to_org_id), 1);
1926: RAISE FND_API.G_EXC_ERROR;
1927: END;
1928: OE_Debug_PUB.Add('OEXPCRCB: Line Level: Customer ID:' ||
1924: OE_Debug_PUB.Add('OEXPCRCB: Line Level: No Customer ID for Bill to:' ||
1925: to_char(p_invoice_to_org_id), 1);
1926: RAISE FND_API.G_EXC_ERROR;
1927: END;
1928: OE_Debug_PUB.Add('OEXPCRCB: Line Level: Customer ID:' ||
1929: to_char(l_customer_id), 1);
1930: -- Now we have the Record Structure loaded we can call the other
1931: -- functions without having to go to the database.
1932: -- Checking whether the order should undergo a credit check. Also
1932: -- Checking whether the order should undergo a credit check. Also
1933: -- returns whether the check should be at the customer level or the
1934: -- bill-to site level and the credit limits at that level.
1935:
1936: oe_debug_pub.ADD('just before the check Line procedure');
1937: OE_Credit_PUB.Check_Order_Line
1938: ( l_header_rec
1939: , p_invoice_to_org_id
1940: , l_customer_id
1957:
1958: -- If the Order is subject to Credit Check i.e. l_check_order = 'Y'
1959: -- First check if there are any unpaid invoices that are passed the
1960: -- maximum due dates.
1961: oe_debug_pub.ADD('Line Level: Calling Check Past Due Invoice procedure');
1962:
1963: oe_credit_pub.chk_past_due_invoice_line (
1964: l_header_rec
1965: ,p_invoice_to_org_id
1977:
1978: IF l_check_past_due = 'N' THEN
1979: -- Determine total exposure.
1980:
1981: oe_debug_pub.ADD('Line Level: Calling the check exposure procedure');
1982: OE_Credit_PUB.Check_Exposure_Line
1983: ( l_header_rec
1984: , p_invoice_to_org_id
1985: , l_customer_id
1994: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1995: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1996: END IF;
1997:
1998: oe_debug_pub.ADD('Line Level: Credit Rule Id ='||to_char(l_credit_rule_id));
1999:
2000: -- Next, compare the order amount and the exposure to the
2001: -- order credit limit and total credit limit.
2002:
2015: , l_result_out
2016: , l_return_status
2017: );
2018:
2019: oe_debug_pub.add('Line Level: After the call for check_Trx_Limit_Line');
2020: oe_debug_pub.add('Line level: Result out ='||l_result_out);
2021: oe_debug_pub.add('Line Level: Return Status ='||l_return_status);
2022:
2023: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2016: , l_return_status
2017: );
2018:
2019: oe_debug_pub.add('Line Level: After the call for check_Trx_Limit_Line');
2020: oe_debug_pub.add('Line level: Result out ='||l_result_out);
2021: oe_debug_pub.add('Line Level: Return Status ='||l_return_status);
2022:
2023: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2024: RAISE FND_API.G_EXC_ERROR;
2017: );
2018:
2019: oe_debug_pub.add('Line Level: After the call for check_Trx_Limit_Line');
2020: oe_debug_pub.add('Line level: Result out ='||l_result_out);
2021: oe_debug_pub.add('Line Level: Return Status ='||l_return_status);
2022:
2023: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2024: RAISE FND_API.G_EXC_ERROR;
2025: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2028:
2029: p_result_out := l_result_out;
2030:
2031: ELSE -- l_check_past_due = 'N'
2032: oe_debug_pub.ADD('Line Level: Past due Invoices: Credit Check Failed');
2033: p_result_out := 'FAIL';
2034: END IF; -- l_check_past_due = 'N'
2035: ELSE -- if credit check order = N
2036:
2033: p_result_out := 'FAIL';
2034: END IF; -- l_check_past_due = 'N'
2035: ELSE -- if credit check order = N
2036:
2037: oe_debug_pub.ADD('Line Level: No credit check required');
2038: -- FND_MESSAGE.SET_NAME('OE', 'OE_NO_CREDIT_CHECK_REQUIRED');
2039: -- FND_MSG_PUB.ADD;
2040: -- null;
2041:
2040: -- null;
2041:
2042: END IF;
2043:
2044: -- oe_debug_pub.dumpdebug;
2045: -- oe_debug_pub.debug_off;
2046:
2047: -- Count the Messages on the Message Stack and if only 1 return it in
2048: -- message data.
2041:
2042: END IF;
2043:
2044: -- oe_debug_pub.dumpdebug;
2045: -- oe_debug_pub.debug_off;
2046:
2047: -- Count the Messages on the Message Stack and if only 1 return it in
2048: -- message data.
2049: -- If more than 1 just return the count. The Calling routine has to get
2123: -- Order Being Credit Checked.
2124: -- If Called from Validating the Order at Order Entry use the
2125: -- Entry Credit Check Rule.
2126:
2127: oe_debug_pub.ADD('Line Level: Which cchk rule');
2128: IF p_calling_action = 'BOOKING' THEN
2129: oe_debug_pub.ADD('Line Level: Selecting the order entry cchk rule');
2130: /*7194250
2131: SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
2125: -- Entry Credit Check Rule.
2126:
2127: oe_debug_pub.ADD('Line Level: Which cchk rule');
2128: IF p_calling_action = 'BOOKING' THEN
2129: oe_debug_pub.ADD('Line Level: Selecting the order entry cchk rule');
2130: /*7194250
2131: SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
2132: INTO l_credit_check_rule_id
2133: FROM OE_ORDER_TYPES_V
2151: --7194250
2152: -- If not Use the Shipping Rule for all other calling Actions
2153: OE_Verify_Payment_PUB.G_credit_check_rule := 'Ordering'; --ER#7479609
2154: ELSE
2155: oe_debug_pub.ADD('Line Level: Selecting the shipping cchk rule');
2156: /*7194250
2157: SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
2158: INTO l_credit_check_rule_id
2159: FROM OE_ORDER_TYPES_V
2180:
2181: -- If no credit rule was found for the calling action the Order
2182: -- is not subject to credit check
2183:
2184: oe_debug_pub.ADD('line level: check order after selecting credit rule : '|| l_check_order);
2185: IF l_credit_check_rule_id <= 0 THEN
2186: oe_debug_pub.ADD('Line Level: No Credit Check rule defined for ' || p_calling_action);
2187: l_check_order := 'N';
2188: END IF;
2182: -- is not subject to credit check
2183:
2184: oe_debug_pub.ADD('line level: check order after selecting credit rule : '|| l_check_order);
2185: IF l_credit_check_rule_id <= 0 THEN
2186: oe_debug_pub.ADD('Line Level: No Credit Check rule defined for ' || p_calling_action);
2187: l_check_order := 'N';
2188: END IF;
2189:
2190: -- If the Order type is subject to credit check we should check the
2220:
2221: -- If we find a Credit Profile at this level the customer has credit limits
2222: -- at the site level.
2223: l_credit_check_lvl_out := 'SITE';
2224: oe_debug_pub.ADD('Line Level: Site/CheckFlag/Limit Lest/overall_credit_limit/trx_credit_limit:'
2225: || to_char(p_invoice_to_org_id) || '/'
2226: || l_check_order || '/'
2227: || to_char(l_credit_limit_test) || '/'
2228: || to_char(p_overall_credit_limit) || '/'
2232:
2233: WHEN NO_DATA_FOUND THEN
2234: -- If we do not find a Credit Profile we will assume that the credit
2235: -- limits are set at the customer level.
2236: oe_debug_pub.ADD('Line Level: Customer level credit check');
2237: l_credit_check_lvl_out := 'CUSTOMER';
2238: WHEN OTHERS THEN
2239: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2240: OE_MSG_PUB.Add_Exc_Msg
2253: END IF;
2254:
2255: END IF; -- Check if the Order type is subject to credit check for site or customer
2256:
2257: oe_debug_pub.ADD('line level: check order after selecting site/customer level : ' || l_check_order);
2258: oe_debug_pub.ADD('level for credit check: '|| l_credit_check_lvl_out);
2259: IF l_check_order = 'Y' AND l_credit_check_lvl_out = 'CUSTOMER' THEN
2260:
2261: -- If both the Order type and the Bill to are subject to credit check
2254:
2255: END IF; -- Check if the Order type is subject to credit check for site or customer
2256:
2257: oe_debug_pub.ADD('line level: check order after selecting site/customer level : ' || l_check_order);
2258: oe_debug_pub.ADD('level for credit check: '|| l_credit_check_lvl_out);
2259: IF l_check_order = 'Y' AND l_credit_check_lvl_out = 'CUSTOMER' THEN
2260:
2261: -- If both the Order type and the Bill to are subject to credit check
2262: -- and no credit profile was found at the bill to site go on to
2280: AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code
2281: AND CP.STATUS='A' --14699527
2282: AND CP.SITE_USE_ID IS NULL;
2283:
2284: oe_debug_pub.ADD('Customer/CheckFlag/LimitTest/overall_credit_limit/trx_credit_limit:'
2285: || to_char(p_customer_id) || '/'
2286: || l_check_order || '/'
2287: || to_char(l_credit_limit_test) || '/'
2288: || to_char(p_overall_credit_limit) || '/'
2309: -- or the Total Exposure, do not credit check
2310: IF l_credit_limit_test < 0 THEN
2311: l_check_order := 'N';
2312: END IF;
2313: oe_debug_pub.ADD('check order after determining credit limits :'|| l_check_order);
2314: END IF; -- The Order type Subject to Credit Check No credit profile at bill to.
2315:
2316: IF l_check_order = 'Y' THEN
2317:
2335: -- If all the lines does not have a payment term that is subject to credit check
2336: -- we should exempt the bill to from credit check.
2337: IF l_credit_check_term = 0 THEN
2338: l_check_order := 'N';
2339: oe_debug_pub.ADD('Line does not have Payment Term subject to credit check');
2340: END IF;
2341:
2342: EXCEPTION
2343: WHEN NO_DATA_FOUND THEN
2341:
2342: EXCEPTION
2343: WHEN NO_DATA_FOUND THEN
2344: l_check_order := 'N';
2345: oe_debug_pub.ADD('Line does not have Payment Term subject to credit check');
2346: WHEN OTHERS THEN
2347: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2348: OE_MSG_PUB.Add_Exc_Msg
2349: ( G_PKG_NAME
2359: p_credit_check_lvl_out := l_credit_check_lvl_out;
2360: p_credit_rule_out := l_credit_check_rule_id;
2361: p_check_Order_out := l_check_order;
2362:
2363: oe_debug_pub.ADD('check order after checking payment term :'|| l_check_order);
2364: EXCEPTION
2365:
2366: WHEN OTHERS THEN
2367: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2934: -- As in R10SC/R11, if the schedule_date (or when this field is null,
2935: -- request_date) is within the no. of shipping_interval days of the current
2936: -- date, include only those amounts for calculating uninvoiced orders total.
2937:
2938: oe_debug_pub.ADD('In Line Level Check Exposure');
2939: OPEN credit_check_rule;
2940: FETCH credit_check_rule INTO l_open_ar_balance_flag
2941: , l_open_ar_days
2942: , l_uninvoiced_orders_flag
2945: , l_shipping_horizon
2946: , l_include_risk_flag;
2947: CLOSE credit_check_rule;
2948:
2949: oe_debug_pub.ADD('Credit Check Rule is as follows :');
2950: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2951: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2952: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2953: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2946: , l_include_risk_flag;
2947: CLOSE credit_check_rule;
2948:
2949: oe_debug_pub.ADD('Credit Check Rule is as follows :');
2950: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2951: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2952: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2953: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2954: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2947: CLOSE credit_check_rule;
2948:
2949: oe_debug_pub.ADD('Credit Check Rule is as follows :');
2950: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2951: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2952: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2953: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2954: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2955: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2948:
2949: oe_debug_pub.ADD('Credit Check Rule is as follows :');
2950: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2951: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2952: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2953: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2954: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2955: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2956: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2949: oe_debug_pub.ADD('Credit Check Rule is as follows :');
2950: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2951: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2952: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2953: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2954: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2955: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2956: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2957:
2950: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2951: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2952: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2953: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2954: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2955: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2956: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2957:
2958: l_est_valid_days :=
2951: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2952: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2953: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2954: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2955: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2956: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2957:
2958: l_est_valid_days :=
2959: to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0'));
2952: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2953: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2954: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2955: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2956: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2957:
2958: l_est_valid_days :=
2959: to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0'));
2960: oe_debug_pub.ADD('Estimated Valid Days ='||to_char(l_est_valid_days));
2956: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2957:
2958: l_est_valid_days :=
2959: to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0'));
2960: oe_debug_pub.ADD('Estimated Valid Days ='||to_char(l_est_valid_days));
2961:
2962:
2963: IF p_credit_level = 'CUSTOMER' THEN
2964: -- Retrieving exposure at CUSTOMER level
2980: END IF;
2981: CLOSE ar_balance_in_ar_days;
2982: END IF; -- If l_open_ar_days is null
2983:
2984: oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
2985:
2986: /*********************************************************************
2987: * If the include payments at risk flag is set to yes, Update the *
2988: * exposure by payments that are not thought to be collectable *
3006: END IF;
3007: CLOSE pay_risk_in_ar_days;
3008: END IF; -- If l_open_ar_days is null
3009:
3010: oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
3011:
3012: END IF; -- l_include_risk_flag (Include Payments at Risk)
3013:
3014: -- Update the total exposure value.
3012: END IF; -- l_include_risk_flag (Include Payments at Risk)
3013:
3014: -- Update the total exposure value.
3015: l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
3016: oe_debug_pub.ADD('Accounts Receivables Exposure ='||
3017: to_char(l_total_exposure));
3018:
3019: END IF; -- l_open_ar_balance_flag (checking accounts receivables exposure)
3020:
3026: BEGIN
3027: OPEN uninvoiced_orders(l_include_tax_flag);
3028: FETCH uninvoiced_orders INTO l_total_on_order;
3029: IF uninvoiced_orders%notfound THEN
3030: oe_debug_pub.ADD('not found any uninvoiced orders');
3031: l_total_on_order := 0;
3032: END IF;
3033: CLOSE uninvoiced_orders;
3034: /* WHEN others THEN
3031: l_total_on_order := 0;
3032: END IF;
3033: CLOSE uninvoiced_orders;
3034: /* WHEN others THEN
3035: oe_debug_pub.ADD('not found');*/
3036: END;
3037:
3038: -- Now update the total exposure value to include the Order Backlog Value
3039:
3036: END;
3037:
3038: -- Now update the total exposure value to include the Order Backlog Value
3039:
3040: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
3041: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
3042:
3043: oe_debug_pub.ADD('Exposure after taking care of uninvoiced orders='
3044: || to_char(l_total_exposure));
3039:
3040: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
3041: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
3042:
3043: oe_debug_pub.ADD('Exposure after taking care of uninvoiced orders='
3044: || to_char(l_total_exposure));
3045:
3046: -- Next check if we should be excluding orders that are already on hold
3047: -- from the calculation of Total exposure
3057: END IF;
3058: CLOSE orders_on_hold;
3059:
3060: -- Update the total exposure value to EXCLUDE the value of orders on hold
3061: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
3062:
3063: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
3064:
3065: oe_debug_pub.ADD('Total exposure after taking care of Hold ='||
3061: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
3062:
3063: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
3064:
3065: oe_debug_pub.ADD('Total exposure after taking care of Hold ='||
3066: to_char(l_total_exposure));
3067: END IF; -- l_orders_on_hold_flag
3068:
3069: -- Check Commitment Total if Commitment Sequencing "On"
3075: l_total_commitment := 0;
3076: END IF;
3077: CLOSE commitment_total;
3078:
3079: oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
3080:
3081: -- If orders on hold are to be excluded then find out
3082: -- the commitment amount associated to orders on hold
3083: IF l_orders_on_hold_flag = 'N' THEN
3088: END IF;
3089:
3090: CLOSE on_hold_commitment_total;
3091:
3092: oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
3093: END IF;
3094:
3095: OPEN current_commitment_total;
3096: FETCH current_commitment_total INTO l_current_commitment;
3098: l_current_commitment := 0;
3099: END IF;
3100: CLOSE current_commitment_total;
3101:
3102: oe_debug_pub.ADD('Current Order Commitment Amount: ' || l_current_commitment);
3103:
3104: l_total_commitment := l_total_commitment + l_current_commitment - nvl(l_on_hold_commitment, 0);
3105:
3106: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
3102: oe_debug_pub.ADD('Current Order Commitment Amount: ' || l_current_commitment);
3103:
3104: l_total_commitment := l_total_commitment + l_current_commitment - nvl(l_on_hold_commitment, 0);
3105:
3106: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
3107:
3108: -- Now update the total exposure value to EXCLUDE already applied Commitments.
3109: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
3110:
3107:
3108: -- Now update the total exposure value to EXCLUDE already applied Commitments.
3109: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
3110:
3111: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
3112:
3113: END IF; -- Commitment Sequencing
3114:
3115: -- ADD Current Order value to the calculated exposure */
3121: END IF;
3122: CLOSE current_order;
3123:
3124: l_total_exposure := l_total_exposure + NVL(l_current_order,0);
3125: oe_debug_pub.ADD('Total exposure after Current Order (CUSTOMER)='
3126: ||to_char(l_total_exposure));
3127:
3128: END IF; -- uninvoiced order flag
3129:
3150: END IF;
3151: CLOSE site_ar_balance_in_ar_days;
3152: END IF; -- If open_ar_days is null
3153:
3154: oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
3155:
3156: -- If the include payments at risk flag is set to yes
3157: -- Update the exposure by payments that are not thought to be collectable
3158: -- These payments are in the cash receipts history
3174: END IF;
3175: CLOSE site_pay_risk_in_ar_days;
3176: END IF; -- If open_ar_days is null
3177:
3178: oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
3179:
3180: END IF; -- l_include_risk_flag (Include Payments at Risk)
3181:
3182: -- Now update the total exposure value.
3180: END IF; -- l_include_risk_flag (Include Payments at Risk)
3181:
3182: -- Now update the total exposure value.
3183: l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
3184: oe_debug_pub.ADD('Exposure from a/c receivables ='||
3185: to_char(l_total_exposure));
3186:
3187: END IF; -- l_open_ar_balance_flag(checking accounts receivables exposure)
3188:
3201: CLOSE site_uninvoiced_orders;
3202:
3203: -- Update the total exposure value to include the Order Backlog Value
3204:
3205: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
3206: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
3207:
3208: oe_debug_pub.ADD('Exposure after uninvoiced orders ='||
3209: to_char(l_total_exposure));
3204:
3205: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
3206: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
3207:
3208: oe_debug_pub.ADD('Exposure after uninvoiced orders ='||
3209: to_char(l_total_exposure));
3210:
3211: -- Next check if we should be excluding orders that are already on hold
3212: -- from the calculation of Total exposure
3223: CLOSE site_orders_on_hold;
3224:
3225: -- Update the total exposure value to EXCLUDE the value of
3226: -- orders on hold
3227: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
3228: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
3229:
3230: oe_debug_pub.ADD('Total Exposure after taking care of Hold ='||
3231: to_char(l_total_exposure));
3226: -- orders on hold
3227: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
3228: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
3229:
3230: oe_debug_pub.ADD('Total Exposure after taking care of Hold ='||
3231: to_char(l_total_exposure));
3232:
3233: END IF; -- check orders on hold
3234:
3241: l_total_commitment := 0;
3242: END IF;
3243: CLOSE site_commitment_total;
3244:
3245: oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
3246:
3247: -- If orders on hold are to be excluded then find out
3248: -- the commitment amount associated to orders on hold
3249: IF l_orders_on_hold_flag = 'N' THEN
3254: END IF;
3255:
3256: CLOSE site_on_hold_commitment_total;
3257:
3258: oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
3259: END IF;
3260:
3261: l_total_commitment := l_total_commitment - nvl(l_on_hold_commitment, 0);
3262:
3260:
3261: l_total_commitment := l_total_commitment - nvl(l_on_hold_commitment, 0);
3262:
3263: -- Now update the total exposure value to EXCLUDE already applied Commitments.
3264: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
3265:
3266: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
3267:
3268: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
3264: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
3265:
3266: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
3267:
3268: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
3269:
3270: END IF; -- Commitment Sequencing
3271:
3272: OPEN site_current_order(l_include_tax_flag);
3276: END IF;
3277: CLOSE site_current_order;
3278:
3279: l_total_exposure := l_total_exposure + NVL(l_current_order,0);
3280: oe_debug_pub.ADD('Total exposure after including Current Order(SITE)='
3281: ||to_char(l_total_exposure));
3282:
3283: END IF; -- l_uninvoiced_orders_flag
3284:
3283: END IF; -- l_uninvoiced_orders_flag
3284:
3285: END IF; -- credit exposure at SITE level or CUSTOMER level
3286:
3287: oe_debug_pub.ADD('Line Level: OUTPUT total exposure: '|| l_total_exposure);
3288: p_total_exposure := l_total_exposure;
3289:
3290:
3291: EXCEPTION