[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
454:
455: -- If not Use the Shipping Rule for all other calling Actions
456: ELSE
457:
458: oe_debug_pub.ADD('Selecting the shipping cchk rule');
459: /*7194250
460: SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
461: INTO l_credit_check_rule_id
462: FROM OE_ORDER_TYPES_V
477:
478: -- If no credit rule was found for the calling action the Order
479: -- is not subject to credit check
480:
481: oe_debug_pub.ADD('check order after selecting credit rule : '|| l_check_order);
482: IF l_credit_check_rule_id <= 0 THEN
483: oe_debug_pub.ADD('no cchk rule found');
484: l_check_order := 'N';
485: END IF;
479: -- is not subject to credit check
480:
481: oe_debug_pub.ADD('check order after selecting credit rule : '|| l_check_order);
482: IF l_credit_check_rule_id <= 0 THEN
483: oe_debug_pub.ADD('no cchk rule found');
484: l_check_order := 'N';
485: END IF;
486:
487: -- If the Order type is subject to credit check we should check the
500: INTO l_invoice_to_cust_id
501: FROM OE_INVOICE_TO_ORGS_V
502: WHERE ORGANIZATION_ID = p_header_rec.invoice_to_org_id;
503:
504: oe_debug_pub.ADD('Invoice To Customer Id: '||to_char(l_invoice_to_cust_id));
505:
506: BEGIN
507:
508: SELECT NVL(CP.CREDIT_CHECKING, 'N')
533: WHEN NO_DATA_FOUND THEN
534: -- If we do not find a Credit Profile we will assume that the credit limits are set
535: -- at the customer level.
536:
537: oe_debug_pub.ADD('Customer level credit check');
538: l_credit_check_lvl_out := 'CUSTOMER';
539:
540: WHEN OTHERS THEN
541:
557: END IF;
558:
559: END IF; -- Check if the Order type is subject to credit check for site or customer
560:
561: oe_debug_pub.ADD('check order after selecting site/customer level : ' || l_check_order);
562: oe_debug_pub.ADD('level for credit check: '|| l_credit_check_lvl_out);
563: IF l_check_order = 'Y' AND l_credit_check_lvl_out = 'CUSTOMER' THEN
564:
565: -- If both the Order type and the Bill to are subject to credit check
558:
559: END IF; -- Check if the Order type is subject to credit check for site or customer
560:
561: oe_debug_pub.ADD('check order after selecting site/customer level : ' || l_check_order);
562: oe_debug_pub.ADD('level for credit check: '|| l_credit_check_lvl_out);
563: IF l_check_order = 'Y' AND l_credit_check_lvl_out = 'CUSTOMER' THEN
564:
565: -- If both the Order type and the Bill to are subject to credit check
566: -- and no credit profile was found at the bill to site go on to
583: AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
584: AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code
585: AND CP.SITE_USE_ID IS NULL;
586:
587: oe_debug_pub.ADD('limit test:'|| l_credit_limit_test );
588:
589: EXCEPTION
590:
591: WHEN NO_DATA_FOUND THEN
609: IF l_credit_limit_test < 0 THEN
610: l_check_order := 'N';
611: END IF;
612:
613: oe_debug_pub.ADD('check order after determining credit limits :'
614: || l_check_order);
615: END IF; -- The Order type Subject to Credit Check No credit profile at bill to.
616:
617: IF l_check_order = 'Y' THEN
659: p_credit_check_lvl_out := l_credit_check_lvl_out;
660: p_credit_rule_out := l_credit_check_rule_id;
661: p_check_order_out := l_check_order;
662:
663: oe_debug_pub.ADD('check order after checking payment term :'|| l_check_order);
664: EXCEPTION
665:
666: WHEN OTHERS THEN
667: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1212: -- As in R10SC/R11, if the schedule_date (or when this field is null, request_date)
1213: -- is within the no. of shipping_interval days of the current date, include only
1214: -- those amounts for calculating uninvoiced orders total.
1215:
1216: oe_debug_pub.ADD('In Check Exposure');
1217: OPEN credit_check_rule;
1218: /* additional task */
1219: FETCH credit_check_rule INTO l_open_ar_balance_flag
1220: , l_open_ar_days
1224: , l_shipping_horizon
1225: , l_include_risk_flag;
1226: CLOSE credit_check_rule;
1227:
1228: oe_debug_pub.ADD('Credit Check Rule is as follows :');
1229: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1230: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1231: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1232: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1225: , l_include_risk_flag;
1226: CLOSE credit_check_rule;
1227:
1228: oe_debug_pub.ADD('Credit Check Rule is as follows :');
1229: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1230: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1231: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1232: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1233: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1226: CLOSE credit_check_rule;
1227:
1228: oe_debug_pub.ADD('Credit Check Rule is as follows :');
1229: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1230: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1231: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1232: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1233: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1234: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1227:
1228: oe_debug_pub.ADD('Credit Check Rule is as follows :');
1229: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1230: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1231: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1232: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1233: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1234: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1235: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1228: oe_debug_pub.ADD('Credit Check Rule is as follows :');
1229: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1230: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1231: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1232: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1233: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1234: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1235: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1236:
1229: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1230: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1231: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1232: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1233: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1234: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1235: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1236:
1237: /* additional task */
1230: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1231: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1232: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1233: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1234: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1235: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1236:
1237: /* additional task */
1238: l_est_valid_days := to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0') ) ;
1231: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1232: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1233: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1234: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1235: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1236:
1237: /* additional task */
1238: l_est_valid_days := to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0') ) ;
1239: oe_debug_pub.ADD('Estimated Valid Days ='||to_char(l_est_valid_days));
1235: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1236:
1237: /* additional task */
1238: l_est_valid_days := to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0') ) ;
1239: oe_debug_pub.ADD('Estimated Valid Days ='||to_char(l_est_valid_days));
1240:
1241: -- Move the following SQL out of if clause for customer credit level
1242: -- because even for site credit level, we need to pass pay_from_customer
1243: /* Find Customer Id of the Header Invoice To Org */
1245: INTO l_invoice_to_cust_id
1246: FROM OE_INVOICE_TO_ORGS_V
1247: WHERE ORGANIZATION_ID = p_header_rec.invoice_to_org_id;
1248:
1249: oe_debug_pub.ADD('Invoice To Customer Id: '||to_char(l_invoice_to_cust_id));
1250:
1251: IF p_credit_level = 'CUSTOMER' THEN -- Retrieving exposure at CUSTOMER level
1252:
1253:
1274: CLOSE ar_balance_in_ar_days;
1275:
1276: END IF; -- If open_ar_days is null
1277:
1278: oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
1279:
1280: /* If the include payments at risk flag is set to yes
1281: Update the exposure by payments that are not thought to be collectable
1282: These payments are in the cash receipts history
1304: CLOSE pay_risk_in_ar_days;
1305:
1306: END IF; -- If open_ar_days is null
1307:
1308: oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
1309:
1310: END IF; -- Include Payments at Risk
1311:
1312:
1312:
1313: -- Now update the total exposure value.
1314:
1315: l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
1316: oe_debug_pub.ADD(' Accounts Receivables Exposure ='||to_char(l_total_exposure));
1317:
1318: END IF; -- checking accounts receivables exposure
1319:
1320:
1329: BEGIN
1330: OPEN uninvoiced_orders(l_include_tax_flag);
1331: FETCH uninvoiced_orders INTO l_total_on_order;
1332: IF uninvoiced_orders%notfound THEN
1333: oe_debug_pub.ADD('not found any uninvoiced orders');
1334: l_total_on_order := 0;
1335: END IF;
1336: CLOSE uninvoiced_orders;
1337:
1335: END IF;
1336: CLOSE uninvoiced_orders;
1337:
1338: /* WHEN others THEN
1339: oe_debug_pub.ADD('not found');*/
1340: END;
1341:
1342:
1343: -- Now update the total exposure value to include the Order Backlog Value
1341:
1342:
1343: -- Now update the total exposure value to include the Order Backlog Value
1344:
1345: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
1346: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
1347:
1348: oe_debug_pub.ADD('Exposure after taking care of uninvoiced orders only ='||to_char(l_total_exposure));
1349:
1344:
1345: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
1346: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
1347:
1348: oe_debug_pub.ADD('Exposure after taking care of uninvoiced orders only ='||to_char(l_total_exposure));
1349:
1350: /* Next check if we should be excluding orders that are already on hold
1351: from the calculation of Total exposure
1352: NOTE: If l_orders_on_hold_flag = 'Y', that means INCLUDE the value of orders on
1368: CLOSE orders_on_hold;
1369:
1370:
1371: -- Now update the total exposure value to EXCLUDE the value of orders on hold
1372: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
1373:
1374: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
1375:
1376: oe_debug_pub.ADD('Total exposure after taking care of Hold ='||to_char(l_total_exposure));
1372: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
1373:
1374: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
1375:
1376: oe_debug_pub.ADD('Total exposure after taking care of Hold ='||to_char(l_total_exposure));
1377:
1378: END IF; -- orders on hold flag
1379:
1380: -- Check Commitment Total if Commitment Sequencing "On"
1386: l_total_commitment := 0;
1387: END IF;
1388: CLOSE commitment_total;
1389:
1390: oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
1391:
1392: -- If orders on hold are to be excluded then find out
1393: -- the commitment amount associated to orders on hold
1394: IF l_orders_on_hold_flag = 'N' THEN
1399: END IF;
1400:
1401: CLOSE on_hold_commitment_total;
1402:
1403: oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
1404: END IF;
1405:
1406: OPEN current_commitment_total;
1407: FETCH current_commitment_total INTO l_current_commitment;
1409: l_current_commitment := 0;
1410: END IF;
1411: CLOSE current_commitment_total;
1412:
1413: oe_debug_pub.ADD('Current Order Commitment Amount: ' || l_current_commitment);
1414:
1415: l_total_commitment := l_total_commitment + l_current_commitment - nvl(l_on_hold_commitment, 0);
1416:
1417: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
1413: oe_debug_pub.ADD('Current Order Commitment Amount: ' || l_current_commitment);
1414:
1415: l_total_commitment := l_total_commitment + l_current_commitment - nvl(l_on_hold_commitment, 0);
1416:
1417: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
1418:
1419: -- Now update the total exposure value to EXCLUDE already applied Commitments.
1420: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
1421:
1418:
1419: -- Now update the total exposure value to EXCLUDE already applied Commitments.
1420: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
1421:
1422: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
1423:
1424: END IF; -- Commitment Sequencing
1425:
1426: /* additional task - ADD Current Order value to the calculated exposure */
1433: END IF;
1434: CLOSE current_order;
1435:
1436: l_total_exposure := l_total_exposure + NVL(l_current_order,0);
1437: oe_debug_pub.ADD('Total exposure after taking care of Current Order (CUSTOMER level)='||to_char(l_total_exposure));
1438:
1439:
1440: END IF; -- uninvoiced order flag
1441:
1465: CLOSE site_ar_balance_in_ar_days;
1466:
1467: END IF; -- If open_ar_days is null
1468:
1469: oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
1470:
1471: /* If the include payments at risk flag is set to yes
1472: Update the exposure by payments that are not thought to be collectable
1473: These payments are in the cash receipts history
1495: CLOSE site_pay_risk_in_ar_days;
1496:
1497: END IF; -- If open_ar_days is null
1498:
1499: oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
1500:
1501: END IF; -- Include Payments at Risk
1502:
1503: -- Now update the total exposure value.
1502:
1503: -- Now update the total exposure value.
1504: l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
1505:
1506: oe_debug_pub.ADD('Exposure from a/c receivables ='||to_char(l_total_exposure));
1507:
1508: END IF; -- checking accounts receivables exposure
1509:
1510:
1527:
1528:
1529: -- Now update the total exposure value to include the Order Backlog Value
1530:
1531: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
1532: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
1533:
1534: oe_debug_pub.ADD('Exposure after uninvoiced orders ='||to_char(l_total_exposure));
1535:
1530:
1531: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
1532: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
1533:
1534: oe_debug_pub.ADD('Exposure after uninvoiced orders ='||to_char(l_total_exposure));
1535:
1536: /* Next check if we should be excluding orders that are already on hold
1537: from the calculation of Total exposure
1538: NOTE: If l_orders_on_hold_flag = 'Y', that means INCLUDE the value of orders
1554: END IF;
1555: CLOSE site_orders_on_hold;
1556:
1557: -- Now update the total exposure value to EXCLUDE the value of orders on hold
1558: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
1559: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
1560:
1561: oe_debug_pub.ADD('Total Exposure after taking care of Hold ='||to_char(l_total_exposure));
1562:
1557: -- Now update the total exposure value to EXCLUDE the value of orders on hold
1558: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
1559: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
1560:
1561: oe_debug_pub.ADD('Total Exposure after taking care of Hold ='||to_char(l_total_exposure));
1562:
1563: END IF; -- check orders on hold
1564:
1565: -- Check Commitment Total if Commitment Sequencing "On"
1571: l_total_commitment := 0;
1572: END IF;
1573: CLOSE site_commitment_total;
1574:
1575: oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
1576:
1577: -- If orders on hold are to be excluded then find out
1578: -- the commitment amount associated to orders on hold
1579: IF l_orders_on_hold_flag = 'N' THEN
1584: END IF;
1585:
1586: CLOSE site_on_hold_commitment_total;
1587:
1588: oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
1589: END IF;
1590:
1591: l_total_commitment := l_total_commitment - nvl(l_on_hold_commitment, 0);
1592:
1590:
1591: l_total_commitment := l_total_commitment - nvl(l_on_hold_commitment, 0);
1592:
1593: -- Now update the total exposure value to EXCLUDE already applied Commitments.
1594: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
1595:
1596: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
1597:
1598: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
1594: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
1595:
1596: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
1597:
1598: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
1599:
1600: END IF; -- Commitment Sequencing
1601:
1602: /* additional task - ADD Current Order */
1609: END IF;
1610: CLOSE current_order;
1611:
1612: l_total_exposure := l_total_exposure + NVL(l_current_order,0);
1613: oe_debug_pub.ADD('Total exposure after taking care of Current Order (SITE level) ='||to_char(l_total_exposure));
1614:
1615: END IF; -- uninvoiced order flag
1616:
1617: END IF; -- credit exposure at site level or customer level
1616:
1617: END IF; -- credit exposure at site level or customer level
1618:
1619: -- Load the Out Variable to be returned.
1620: oe_debug_pub.ADD('OUTPUT total exposure: '|| l_total_exposure);
1621: p_total_exposure := l_total_exposure;
1622:
1623:
1624: EXCEPTION
1660:
1661: if l_maximum_days_past_due > 0 then
1662: -- Check to see if there is any unpaid invoice that is past the
1663: -- due date.
1664: oe_debug_pub.ADD('OEXPCRCB.pls: line level maximum_days_past_due:' ||to_char(l_maximum_days_past_due) );
1665: BEGIN
1666: -- Default to Y, in case there is one or more invoices due.
1667: p_check_past_due := 'Y';
1668: select 'Any Past due invoice'
1676: EXCEPTION
1677:
1678: WHEN NO_DATA_FOUND THEN
1679: p_check_past_due := 'N';
1680: oe_debug_pub.ADD('OEXPCRCB.pls: No Invoices Past due -- line level' );
1681: WHEN TOO_MANY_ROWS THEN
1682: null;
1683: END;
1684:
1683: END;
1684:
1685:
1686: end if;
1687: oe_debug_pub.ADD('OEXPCRCB.pls: Line level Past due Invoice Check:' || p_check_past_due);
1688: EXCEPTION
1689:
1690: WHEN others THEN
1691: p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1784: WHERE P.HEADER_ID = p_header_rec.header_id
1785: AND L.LINE_ID = P.LINE_ID
1786: AND L.INVOICE_TO_ORG_ID = p_invoice_to_org_id;
1787:
1788: oe_debug_pub.ADD('OEXPCRCB.pls: line level trx commitment total:' || l_order_commitment);
1789:
1790: -- get the actual order value subject to credit check.
1791: l_order_value := l_order_value - l_order_commitment;
1792:
1795: -- If credit available is less than the total exposure or
1796: -- if the order amount is greater than the transaction limit
1797: -- Return Failure
1798:
1799: oe_debug_pub.ADD('OEXPCRCB.pls: line level total exposure is:' ||p_total_exposure );
1800: oe_debug_pub.ADD('OEXPCRCB.pls: line level total credit limit:' || p_overall_credit_limit);
1801: oe_debug_pub.ADD('OEXPCRCB.pls: line level total order value:' || l_order_value);
1802: oe_debug_pub.ADD('OEXPCRCB.pls: line level order credit limit:' || p_trx_credit_limit);
1803: -- Replaced this code
1796: -- if the order amount is greater than the transaction limit
1797: -- Return Failure
1798:
1799: oe_debug_pub.ADD('OEXPCRCB.pls: line level total exposure is:' ||p_total_exposure );
1800: oe_debug_pub.ADD('OEXPCRCB.pls: line level total credit limit:' || p_overall_credit_limit);
1801: oe_debug_pub.ADD('OEXPCRCB.pls: line level total order value:' || l_order_value);
1802: oe_debug_pub.ADD('OEXPCRCB.pls: line level order credit limit:' || p_trx_credit_limit);
1803: -- Replaced this code
1804: -- IF l_order_value > p_trx_credit_limit OR
1797: -- Return Failure
1798:
1799: oe_debug_pub.ADD('OEXPCRCB.pls: line level total exposure is:' ||p_total_exposure );
1800: oe_debug_pub.ADD('OEXPCRCB.pls: line level total credit limit:' || p_overall_credit_limit);
1801: oe_debug_pub.ADD('OEXPCRCB.pls: line level total order value:' || l_order_value);
1802: oe_debug_pub.ADD('OEXPCRCB.pls: line level order credit limit:' || p_trx_credit_limit);
1803: -- Replaced this code
1804: -- IF l_order_value > p_trx_credit_limit OR
1805: -- p_total_exposure > p_overall_credit_limit THEN
1798:
1799: oe_debug_pub.ADD('OEXPCRCB.pls: line level total exposure is:' ||p_total_exposure );
1800: oe_debug_pub.ADD('OEXPCRCB.pls: line level total credit limit:' || p_overall_credit_limit);
1801: oe_debug_pub.ADD('OEXPCRCB.pls: line level total order value:' || l_order_value);
1802: oe_debug_pub.ADD('OEXPCRCB.pls: line level order credit limit:' || p_trx_credit_limit);
1803: -- Replaced this code
1804: -- IF l_order_value > p_trx_credit_limit OR
1805: -- p_total_exposure > p_overall_credit_limit THEN
1806: -- p_result_out := 'FAIL';
1803: -- Replaced this code
1804: -- IF l_order_value > p_trx_credit_limit OR
1805: -- p_total_exposure > p_overall_credit_limit THEN
1806: -- p_result_out := 'FAIL';
1807: -- oe_debug_pub.ADD('Over credit limit');
1808: -- END IF;
1809:
1810: -- With this
1811: if (p_trx_credit_limit <> -1) then
1810: -- With this
1811: if (p_trx_credit_limit <> -1) then
1812: if (l_order_value > p_trx_credit_limit) then
1813: p_result_out := 'FAIL';
1814: oe_debug_pub.ADD('Line Level: Order Value greater then Transaction Limit.');
1815: end if;
1816: end if;
1817: if (p_overall_credit_limit <> -1) then
1818: if (p_total_exposure > p_overall_credit_limit) then
1816: end if;
1817: if (p_overall_credit_limit <> -1) then
1818: if (p_total_exposure > p_overall_credit_limit) then
1819: p_result_out := 'FAIL';
1820: oe_debug_pub.ADD('Line Level: Total Exposure is greater then Overall Credit Limit');
1821: end if;
1822: end if;
1823:
1824:
1870: l_customer_id NUMBER;
1871: BEGIN
1872:
1873: -- Set the default behaviour to pass credit check
1874: -- oe_debug_pub.debug_on;
1875:
1876: p_result_out := 'PASS';
1877: p_return_status := FND_API.G_RET_STS_SUCCESS;
1878:
1875:
1876: p_result_out := 'PASS';
1877: p_return_status := FND_API.G_RET_STS_SUCCESS;
1878:
1879: oe_debug_pub.ADD('Line Level: Calling action is '|| p_calling_action);
1880:
1881: -- The first thing to do is to load the record structure for the order header
1882: -- This is done in the OE_HEADER_UTIL package by the Query Row function.
1883: -- The caller must pass a Header id and the function returns the record
1882: -- This is done in the OE_HEADER_UTIL package by the Query Row function.
1883: -- The caller must pass a Header id and the function returns the record
1884: -- Structure l_header_rec
1885:
1886: oe_debug_pub.ADD('Line Level: Before querying');
1887: OE_HEADER_UTIL.QUERY_ROW(p_header_id => p_header_id
1888: ,x_header_rec => l_header_rec);
1889:
1890: -- OE_LINE_UTIL.Query_Row(p_line_id => p_line_id
1902: --FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SITE_USE_ID');
1903: --OE_MSG_PUB.ADD;
1904: --fnd_message.set_token('SITE_USE_ID',
1905: -- to_char(p_hold_entity_id));
1906: OE_Debug_PUB.Add('OEXPCRCB: Line Level: No Customer ID for Bill to:' ||
1907: to_char(p_invoice_to_org_id), 1);
1908: RAISE FND_API.G_EXC_ERROR;
1909: END;
1910: OE_Debug_PUB.Add('OEXPCRCB: Line Level: Customer ID:' ||
1906: OE_Debug_PUB.Add('OEXPCRCB: Line Level: No Customer ID for Bill to:' ||
1907: to_char(p_invoice_to_org_id), 1);
1908: RAISE FND_API.G_EXC_ERROR;
1909: END;
1910: OE_Debug_PUB.Add('OEXPCRCB: Line Level: Customer ID:' ||
1911: to_char(l_customer_id), 1);
1912: -- Now we have the Record Structure loaded we can call the other
1913: -- functions without having to go to the database.
1914: -- Checking whether the order should undergo a credit check. Also
1914: -- Checking whether the order should undergo a credit check. Also
1915: -- returns whether the check should be at the customer level or the
1916: -- bill-to site level and the credit limits at that level.
1917:
1918: oe_debug_pub.ADD('just before the check Line procedure');
1919: OE_Credit_PUB.Check_Order_Line
1920: ( l_header_rec
1921: , p_invoice_to_org_id
1922: , l_customer_id
1939:
1940: -- If the Order is subject to Credit Check i.e. l_check_order = 'Y'
1941: -- First check if there are any unpaid invoices that are passed the
1942: -- maximum due dates.
1943: oe_debug_pub.ADD('Line Level: Calling Check Past Due Invoice procedure');
1944:
1945: oe_credit_pub.chk_past_due_invoice_line (
1946: l_header_rec
1947: ,p_invoice_to_org_id
1959:
1960: IF l_check_past_due = 'N' THEN
1961: -- Determine total exposure.
1962:
1963: oe_debug_pub.ADD('Line Level: Calling the check exposure procedure');
1964: OE_Credit_PUB.Check_Exposure_Line
1965: ( l_header_rec
1966: , p_invoice_to_org_id
1967: , l_customer_id
1976: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1977: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1978: END IF;
1979:
1980: oe_debug_pub.ADD('Line Level: Credit Rule Id ='||to_char(l_credit_rule_id));
1981:
1982: -- Next, compare the order amount and the exposure to the
1983: -- order credit limit and total credit limit.
1984:
1997: , l_result_out
1998: , l_return_status
1999: );
2000:
2001: oe_debug_pub.add('Line Level: After the call for check_Trx_Limit_Line');
2002: oe_debug_pub.add('Line level: Result out ='||l_result_out);
2003: oe_debug_pub.add('Line Level: Return Status ='||l_return_status);
2004:
2005: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1998: , l_return_status
1999: );
2000:
2001: oe_debug_pub.add('Line Level: After the call for check_Trx_Limit_Line');
2002: oe_debug_pub.add('Line level: Result out ='||l_result_out);
2003: oe_debug_pub.add('Line Level: Return Status ='||l_return_status);
2004:
2005: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2006: RAISE FND_API.G_EXC_ERROR;
1999: );
2000:
2001: oe_debug_pub.add('Line Level: After the call for check_Trx_Limit_Line');
2002: oe_debug_pub.add('Line level: Result out ='||l_result_out);
2003: oe_debug_pub.add('Line Level: Return Status ='||l_return_status);
2004:
2005: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2006: RAISE FND_API.G_EXC_ERROR;
2007: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2010:
2011: p_result_out := l_result_out;
2012:
2013: ELSE -- l_check_past_due = 'N'
2014: oe_debug_pub.ADD('Line Level: Past due Invoices: Credit Check Failed');
2015: p_result_out := 'FAIL';
2016: END IF; -- l_check_past_due = 'N'
2017: ELSE -- if credit check order = N
2018:
2015: p_result_out := 'FAIL';
2016: END IF; -- l_check_past_due = 'N'
2017: ELSE -- if credit check order = N
2018:
2019: oe_debug_pub.ADD('Line Level: No credit check required');
2020: -- FND_MESSAGE.SET_NAME('OE', 'OE_NO_CREDIT_CHECK_REQUIRED');
2021: -- FND_MSG_PUB.ADD;
2022: -- null;
2023:
2022: -- null;
2023:
2024: END IF;
2025:
2026: -- oe_debug_pub.dumpdebug;
2027: -- oe_debug_pub.debug_off;
2028:
2029: -- Count the Messages on the Message Stack and if only 1 return it in
2030: -- message data.
2023:
2024: END IF;
2025:
2026: -- oe_debug_pub.dumpdebug;
2027: -- oe_debug_pub.debug_off;
2028:
2029: -- Count the Messages on the Message Stack and if only 1 return it in
2030: -- message data.
2031: -- If more than 1 just return the count. The Calling routine has to get
2105: -- Order Being Credit Checked.
2106: -- If Called from Validating the Order at Order Entry use the
2107: -- Entry Credit Check Rule.
2108:
2109: oe_debug_pub.ADD('Line Level: Which cchk rule');
2110: IF p_calling_action = 'BOOKING' THEN
2111: oe_debug_pub.ADD('Line Level: Selecting the order entry cchk rule');
2112: /*7194250
2113: SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
2107: -- Entry Credit Check Rule.
2108:
2109: oe_debug_pub.ADD('Line Level: Which cchk rule');
2110: IF p_calling_action = 'BOOKING' THEN
2111: oe_debug_pub.ADD('Line Level: Selecting the order entry cchk rule');
2112: /*7194250
2113: SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
2114: INTO l_credit_check_rule_id
2115: FROM OE_ORDER_TYPES_V
2125: --7194250
2126: -- If not Use the Shipping Rule for all other calling Actions
2127: OE_Verify_Payment_PUB.G_credit_check_rule := 'Ordering'; --ER#7479609
2128: ELSE
2129: oe_debug_pub.ADD('Line Level: Selecting the shipping cchk rule');
2130: /*7194250
2131: SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
2132: INTO l_credit_check_rule_id
2133: FROM OE_ORDER_TYPES_V
2146:
2147: -- If no credit rule was found for the calling action the Order
2148: -- is not subject to credit check
2149:
2150: oe_debug_pub.ADD('line level: check order after selecting credit rule : '|| l_check_order);
2151: IF l_credit_check_rule_id <= 0 THEN
2152: oe_debug_pub.ADD('Line Level: No Credit Check rule defined for ' || p_calling_action);
2153: l_check_order := 'N';
2154: END IF;
2148: -- is not subject to credit check
2149:
2150: oe_debug_pub.ADD('line level: check order after selecting credit rule : '|| l_check_order);
2151: IF l_credit_check_rule_id <= 0 THEN
2152: oe_debug_pub.ADD('Line Level: No Credit Check rule defined for ' || p_calling_action);
2153: l_check_order := 'N';
2154: END IF;
2155:
2156: -- If the Order type is subject to credit check we should check the
2185:
2186: -- If we find a Credit Profile at this level the customer has credit limits
2187: -- at the site level.
2188: l_credit_check_lvl_out := 'SITE';
2189: oe_debug_pub.ADD('Line Level: Site/CheckFlag/Limit Lest/overall_credit_limit/trx_credit_limit:'
2190: || to_char(p_invoice_to_org_id) || '/'
2191: || l_check_order || '/'
2192: || to_char(l_credit_limit_test) || '/'
2193: || to_char(p_overall_credit_limit) || '/'
2197:
2198: WHEN NO_DATA_FOUND THEN
2199: -- If we do not find a Credit Profile we will assume that the credit
2200: -- limits are set at the customer level.
2201: oe_debug_pub.ADD('Line Level: Customer level credit check');
2202: l_credit_check_lvl_out := 'CUSTOMER';
2203: WHEN OTHERS THEN
2204: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2205: OE_MSG_PUB.Add_Exc_Msg
2218: END IF;
2219:
2220: END IF; -- Check if the Order type is subject to credit check for site or customer
2221:
2222: oe_debug_pub.ADD('line level: check order after selecting site/customer level : ' || l_check_order);
2223: oe_debug_pub.ADD('level for credit check: '|| l_credit_check_lvl_out);
2224: IF l_check_order = 'Y' AND l_credit_check_lvl_out = 'CUSTOMER' THEN
2225:
2226: -- If both the Order type and the Bill to are subject to credit check
2219:
2220: END IF; -- Check if the Order type is subject to credit check for site or customer
2221:
2222: oe_debug_pub.ADD('line level: check order after selecting site/customer level : ' || l_check_order);
2223: oe_debug_pub.ADD('level for credit check: '|| l_credit_check_lvl_out);
2224: IF l_check_order = 'Y' AND l_credit_check_lvl_out = 'CUSTOMER' THEN
2225:
2226: -- If both the Order type and the Bill to are subject to credit check
2227: -- and no credit profile was found at the bill to site go on to
2244: AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
2245: AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code
2246: AND CP.SITE_USE_ID IS NULL;
2247:
2248: oe_debug_pub.ADD('Customer/CheckFlag/LimitTest/overall_credit_limit/trx_credit_limit:'
2249: || to_char(p_customer_id) || '/'
2250: || l_check_order || '/'
2251: || to_char(l_credit_limit_test) || '/'
2252: || to_char(p_overall_credit_limit) || '/'
2273: -- or the Total Exposure, do not credit check
2274: IF l_credit_limit_test < 0 THEN
2275: l_check_order := 'N';
2276: END IF;
2277: oe_debug_pub.ADD('check order after determining credit limits :'|| l_check_order);
2278: END IF; -- The Order type Subject to Credit Check No credit profile at bill to.
2279:
2280: IF l_check_order = 'Y' THEN
2281:
2299: -- If all the lines does not have a payment term that is subject to credit check
2300: -- we should exempt the bill to from credit check.
2301: IF l_credit_check_term = 0 THEN
2302: l_check_order := 'N';
2303: oe_debug_pub.ADD('Line does not have Payment Term subject to credit check');
2304: END IF;
2305:
2306: EXCEPTION
2307: WHEN NO_DATA_FOUND THEN
2305:
2306: EXCEPTION
2307: WHEN NO_DATA_FOUND THEN
2308: l_check_order := 'N';
2309: oe_debug_pub.ADD('Line does not have Payment Term subject to credit check');
2310: WHEN OTHERS THEN
2311: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2312: OE_MSG_PUB.Add_Exc_Msg
2313: ( G_PKG_NAME
2323: p_credit_check_lvl_out := l_credit_check_lvl_out;
2324: p_credit_rule_out := l_credit_check_rule_id;
2325: p_check_Order_out := l_check_order;
2326:
2327: oe_debug_pub.ADD('check order after checking payment term :'|| l_check_order);
2328: EXCEPTION
2329:
2330: WHEN OTHERS THEN
2331: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2898: -- As in R10SC/R11, if the schedule_date (or when this field is null,
2899: -- request_date) is within the no. of shipping_interval days of the current
2900: -- date, include only those amounts for calculating uninvoiced orders total.
2901:
2902: oe_debug_pub.ADD('In Line Level Check Exposure');
2903: OPEN credit_check_rule;
2904: FETCH credit_check_rule INTO l_open_ar_balance_flag
2905: , l_open_ar_days
2906: , l_uninvoiced_orders_flag
2909: , l_shipping_horizon
2910: , l_include_risk_flag;
2911: CLOSE credit_check_rule;
2912:
2913: oe_debug_pub.ADD('Credit Check Rule is as follows :');
2914: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2915: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2916: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2917: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2910: , l_include_risk_flag;
2911: CLOSE credit_check_rule;
2912:
2913: oe_debug_pub.ADD('Credit Check Rule is as follows :');
2914: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2915: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2916: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2917: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2918: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2911: CLOSE credit_check_rule;
2912:
2913: oe_debug_pub.ADD('Credit Check Rule is as follows :');
2914: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2915: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2916: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2917: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2918: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2919: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2912:
2913: oe_debug_pub.ADD('Credit Check Rule is as follows :');
2914: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2915: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2916: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2917: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2918: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2919: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2920: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2913: oe_debug_pub.ADD('Credit Check Rule is as follows :');
2914: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2915: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2916: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2917: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2918: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2919: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2920: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2921:
2914: oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2915: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2916: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2917: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2918: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2919: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2920: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2921:
2922: l_est_valid_days :=
2915: oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2916: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2917: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2918: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2919: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2920: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2921:
2922: l_est_valid_days :=
2923: to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0'));
2916: oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2917: oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2918: oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2919: oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2920: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2921:
2922: l_est_valid_days :=
2923: to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0'));
2924: oe_debug_pub.ADD('Estimated Valid Days ='||to_char(l_est_valid_days));
2920: oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2921:
2922: l_est_valid_days :=
2923: to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0'));
2924: oe_debug_pub.ADD('Estimated Valid Days ='||to_char(l_est_valid_days));
2925:
2926:
2927: IF p_credit_level = 'CUSTOMER' THEN
2928: -- Retrieving exposure at CUSTOMER level
2944: END IF;
2945: CLOSE ar_balance_in_ar_days;
2946: END IF; -- If l_open_ar_days is null
2947:
2948: oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
2949:
2950: /*********************************************************************
2951: * If the include payments at risk flag is set to yes, Update the *
2952: * exposure by payments that are not thought to be collectable *
2970: END IF;
2971: CLOSE pay_risk_in_ar_days;
2972: END IF; -- If l_open_ar_days is null
2973:
2974: oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
2975:
2976: END IF; -- l_include_risk_flag (Include Payments at Risk)
2977:
2978: -- Update the total exposure value.
2976: END IF; -- l_include_risk_flag (Include Payments at Risk)
2977:
2978: -- Update the total exposure value.
2979: l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
2980: oe_debug_pub.ADD('Accounts Receivables Exposure ='||
2981: to_char(l_total_exposure));
2982:
2983: END IF; -- l_open_ar_balance_flag (checking accounts receivables exposure)
2984:
2990: BEGIN
2991: OPEN uninvoiced_orders(l_include_tax_flag);
2992: FETCH uninvoiced_orders INTO l_total_on_order;
2993: IF uninvoiced_orders%notfound THEN
2994: oe_debug_pub.ADD('not found any uninvoiced orders');
2995: l_total_on_order := 0;
2996: END IF;
2997: CLOSE uninvoiced_orders;
2998: /* WHEN others THEN
2995: l_total_on_order := 0;
2996: END IF;
2997: CLOSE uninvoiced_orders;
2998: /* WHEN others THEN
2999: oe_debug_pub.ADD('not found');*/
3000: END;
3001:
3002: -- Now update the total exposure value to include the Order Backlog Value
3003:
3000: END;
3001:
3002: -- Now update the total exposure value to include the Order Backlog Value
3003:
3004: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
3005: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
3006:
3007: oe_debug_pub.ADD('Exposure after taking care of uninvoiced orders='
3008: || to_char(l_total_exposure));
3003:
3004: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
3005: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
3006:
3007: oe_debug_pub.ADD('Exposure after taking care of uninvoiced orders='
3008: || to_char(l_total_exposure));
3009:
3010: -- Next check if we should be excluding orders that are already on hold
3011: -- from the calculation of Total exposure
3021: END IF;
3022: CLOSE orders_on_hold;
3023:
3024: -- Update the total exposure value to EXCLUDE the value of orders on hold
3025: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
3026:
3027: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
3028:
3029: oe_debug_pub.ADD('Total exposure after taking care of Hold ='||
3025: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
3026:
3027: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
3028:
3029: oe_debug_pub.ADD('Total exposure after taking care of Hold ='||
3030: to_char(l_total_exposure));
3031: END IF; -- l_orders_on_hold_flag
3032:
3033: -- Check Commitment Total if Commitment Sequencing "On"
3039: l_total_commitment := 0;
3040: END IF;
3041: CLOSE commitment_total;
3042:
3043: oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
3044:
3045: -- If orders on hold are to be excluded then find out
3046: -- the commitment amount associated to orders on hold
3047: IF l_orders_on_hold_flag = 'N' THEN
3052: END IF;
3053:
3054: CLOSE on_hold_commitment_total;
3055:
3056: oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
3057: END IF;
3058:
3059: OPEN current_commitment_total;
3060: FETCH current_commitment_total INTO l_current_commitment;
3062: l_current_commitment := 0;
3063: END IF;
3064: CLOSE current_commitment_total;
3065:
3066: oe_debug_pub.ADD('Current Order Commitment Amount: ' || l_current_commitment);
3067:
3068: l_total_commitment := l_total_commitment + l_current_commitment - nvl(l_on_hold_commitment, 0);
3069:
3070: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
3066: oe_debug_pub.ADD('Current Order Commitment Amount: ' || l_current_commitment);
3067:
3068: l_total_commitment := l_total_commitment + l_current_commitment - nvl(l_on_hold_commitment, 0);
3069:
3070: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
3071:
3072: -- Now update the total exposure value to EXCLUDE already applied Commitments.
3073: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
3074:
3071:
3072: -- Now update the total exposure value to EXCLUDE already applied Commitments.
3073: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
3074:
3075: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
3076:
3077: END IF; -- Commitment Sequencing
3078:
3079: -- ADD Current Order value to the calculated exposure */
3085: END IF;
3086: CLOSE current_order;
3087:
3088: l_total_exposure := l_total_exposure + NVL(l_current_order,0);
3089: oe_debug_pub.ADD('Total exposure after Current Order (CUSTOMER)='
3090: ||to_char(l_total_exposure));
3091:
3092: END IF; -- uninvoiced order flag
3093:
3114: END IF;
3115: CLOSE site_ar_balance_in_ar_days;
3116: END IF; -- If open_ar_days is null
3117:
3118: oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
3119:
3120: -- If the include payments at risk flag is set to yes
3121: -- Update the exposure by payments that are not thought to be collectable
3122: -- These payments are in the cash receipts history
3138: END IF;
3139: CLOSE site_pay_risk_in_ar_days;
3140: END IF; -- If open_ar_days is null
3141:
3142: oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
3143:
3144: END IF; -- l_include_risk_flag (Include Payments at Risk)
3145:
3146: -- Now update the total exposure value.
3144: END IF; -- l_include_risk_flag (Include Payments at Risk)
3145:
3146: -- Now update the total exposure value.
3147: l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
3148: oe_debug_pub.ADD('Exposure from a/c receivables ='||
3149: to_char(l_total_exposure));
3150:
3151: END IF; -- l_open_ar_balance_flag(checking accounts receivables exposure)
3152:
3165: CLOSE site_uninvoiced_orders;
3166:
3167: -- Update the total exposure value to include the Order Backlog Value
3168:
3169: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
3170: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
3171:
3172: oe_debug_pub.ADD('Exposure after uninvoiced orders ='||
3173: to_char(l_total_exposure));
3168:
3169: oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
3170: l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
3171:
3172: oe_debug_pub.ADD('Exposure after uninvoiced orders ='||
3173: to_char(l_total_exposure));
3174:
3175: -- Next check if we should be excluding orders that are already on hold
3176: -- from the calculation of Total exposure
3187: CLOSE site_orders_on_hold;
3188:
3189: -- Update the total exposure value to EXCLUDE the value of
3190: -- orders on hold
3191: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
3192: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
3193:
3194: oe_debug_pub.ADD('Total Exposure after taking care of Hold ='||
3195: to_char(l_total_exposure));
3190: -- orders on hold
3191: oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
3192: l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
3193:
3194: oe_debug_pub.ADD('Total Exposure after taking care of Hold ='||
3195: to_char(l_total_exposure));
3196:
3197: END IF; -- check orders on hold
3198:
3205: l_total_commitment := 0;
3206: END IF;
3207: CLOSE site_commitment_total;
3208:
3209: oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
3210:
3211: -- If orders on hold are to be excluded then find out
3212: -- the commitment amount associated to orders on hold
3213: IF l_orders_on_hold_flag = 'N' THEN
3218: END IF;
3219:
3220: CLOSE site_on_hold_commitment_total;
3221:
3222: oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
3223: END IF;
3224:
3225: l_total_commitment := l_total_commitment - nvl(l_on_hold_commitment, 0);
3226:
3224:
3225: l_total_commitment := l_total_commitment - nvl(l_on_hold_commitment, 0);
3226:
3227: -- Now update the total exposure value to EXCLUDE already applied Commitments.
3228: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
3229:
3230: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
3231:
3232: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
3228: oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
3229:
3230: l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
3231:
3232: oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
3233:
3234: END IF; -- Commitment Sequencing
3235:
3236: OPEN site_current_order(l_include_tax_flag);
3240: END IF;
3241: CLOSE site_current_order;
3242:
3243: l_total_exposure := l_total_exposure + NVL(l_current_order,0);
3244: oe_debug_pub.ADD('Total exposure after including Current Order(SITE)='
3245: ||to_char(l_total_exposure));
3246:
3247: END IF; -- l_uninvoiced_orders_flag
3248:
3247: END IF; -- l_uninvoiced_orders_flag
3248:
3249: END IF; -- credit exposure at SITE level or CUSTOMER level
3250:
3251: oe_debug_pub.ADD('Line Level: OUTPUT total exposure: '|| l_total_exposure);
3252: p_total_exposure := l_total_exposure;
3253:
3254:
3255: EXCEPTION