1 PACKAGE BODY OE_Credit_PUB AS
2 /* $Header: OEXPCRCB.pls 120.7.12020000.2 2013/04/08 06:21:21 kadiraju ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Credit_PUB';
5
6
7
8 procedure chk_past_due_invoice (
9 p_header_rec IN OE_Order_PUB.Header_Rec_Type
10 ,p_credit_rule_id IN NUMBER
11 ,p_credit_level IN VARCHAR2
12 ,p_check_past_due OUT NOCOPY /* file.sql.39 change */ VARCHAR2
13 ,p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
14 )
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
24 p_check_past_due := 'N';
25
26 select NVL(maximum_days_past_due, 0)
27 into l_maximum_days_past_due
28 from OE_CREDIT_CHECK_RULES
29 where CREDIT_CHECK_RULE_ID = p_credit_rule_id;
30
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';
40 select 'Any Past due invoice'
41 into l_dummy
42 from AR_PAYMENT_SCHEDULES
43 WHERE CUSTOMER_ID = ( SELECT CUSTOMER_ID
44 FROM OE_INVOICE_TO_ORGS_V
45 WHERE ORGANIZATION_ID = p_header_rec.invoice_to_org_id)
46 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
47 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
48 AND AMOUNT_DUE_REMAINING > 0
49 AND DUE_DATE < sysdate - l_maximum_days_past_due;
50 EXCEPTION
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;
60
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
70 WHEN others THEN
71 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
72 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
73
74 OE_MSG_PUB.Add_Exc_Msg
75 ( G_PKG_NAME
76 ,'chk_past_due_invoice'
77 );
78 END IF;
79 RAISE;
80
81 END chk_past_due_invoice;
82
83
84 -- Mainline Function that will read an Order Header and Determine if should be checked,
85 -- calculates total exposure, find credit limits and determin result for calling function.
86 /* additional task - made the procedure Check_trx_Limit
87 local to this package and added p_credit_rule_id as
88 an additional input parameter */
89
90
91 PROCEDURE Check_Trx_Limit
92 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
93 , p_credit_rule_id IN NUMBER
94 , p_trx_credit_limit IN NUMBER := FND_API.G_MISS_NUM
95 , p_total_exposure IN NUMBER
96 , p_overall_credit_limit IN NUMBER
97 , p_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
98 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
99 )
100 IS
101
102 l_order_value NUMBER;
103 l_include_tax_flag VARCHAR2(1) := 'Y';
104
105 l_order_commitment NUMBER;
106
107 BEGIN
108
109 -- Initialize return status to success
110 p_return_status := FND_API.G_RET_STS_SUCCESS;
111
112 -- Default to Pass
113 p_result_out := 'PASS';
114
115 /* additional task- Read the value of include_tax_flag from credit check rule
116 and calculate the value of l_order_values accordingly */
117
118 /* If the value of include_tax_flag is NULL that means it is 'No' */
119
120 select NVL(include_tax_flag, 'N')
121 into l_include_tax_flag
122 from OE_CREDIT_CHECK_RULES
123 where CREDIT_CHECK_RULE_ID = p_credit_rule_id;
124
125 -- Depending on the value of tax_flag add the tax_value
126
127 SELECT SUM(decode(l_include_tax_flag , 'Y', NVL(tax_value,0),0)
128 + (NVL(unit_selling_price,0)
129 * (NVL(ordered_quantity,0) )))
130 INTO l_order_value
131 FROM OE_ORDER_LINES
132 WHERE HEADER_ID = p_header_rec.header_id;
133
134 -- Get Total Commitments applied to the current order if Commitment Sequencing is "On"
135 IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
136
137 SELECT NVL(SUM(commitment_applied_amount), 0)
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
147 END IF;
148
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
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
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
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
179 EXCEPTION
180
181 WHEN others THEN
182 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
183 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
184 OE_MSG_PUB.Add_Exc_Msg
185 ( G_PKG_NAME
186 , 'Check_Trx_Limit'
187 );
188 END IF;
189 RAISE ;
190
191 END Check_Trx_Limit;
192
193
194
195 -- Mainline Function that will read an Order Header and Determine if
196 -- should be checked, calculates total exposure, find credit limits
197 -- and determine result for calling function.
198
199 PROCEDURE Check_Available_Credit
200 ( p_header_id IN NUMBER := FND_API.G_MISS_NUM
201 , p_calling_action IN VARCHAR2 := 'BOOKING'
202 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
203 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
204 , p_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Pass or Fail Credit Check
205 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
206 )
207 IS
208 l_header_rec OE_Order_PUB.Header_Rec_Type;
209 l_credit_rule_id NUMBER;
210 l_credit_level VARCHAR2(30); -- are the limits at the customer
211 -- or site level
212 l_order_value NUMBER;
213 l_check_order VARCHAR2(1); -- Indicates if this Order is
214 -- subject to credit check
215 l_total_exposure NUMBER;
216 l_trx_credit_limit NUMBER;
217 l_overall_credit_limit NUMBER;
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
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
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
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
254 , l_credit_rule_id
255 , l_credit_level
256 , l_overall_credit_limit
257 , l_trx_credit_limit
258 , l_return_status
259 );
260
261 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
262 RAISE FND_API.G_EXC_ERROR;
263 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END IF;
266
267 IF l_check_order = 'Y' THEN
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
277 ,l_credit_level
278 ,l_check_past_due
279 ,l_return_status
280 );
281 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
282 RAISE FND_API.G_EXC_ERROR;
283 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
285 END IF;
286
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
296 , l_credit_rule_id
297 , l_credit_level
298 , l_total_exposure
299 , l_return_status
300 );
301
302 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
303 RAISE FND_API.G_EXC_ERROR;
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
313 /* additional task - now credit_rule_id is passed to Check_trx_Limit*/
314
315
316 OE_Credit_PUB.Check_Trx_Limit
317 ( l_header_rec
318 , l_credit_rule_id
319 , l_trx_credit_limit
320 , l_total_exposure
321 , l_overall_credit_limit
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
331 RAISE FND_API.G_EXC_ERROR;
332 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
334 END IF;
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
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
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
357 -- the messages from the message stack.
358
359 OE_MSG_PUB.Count_And_Get
360 ( p_count => p_msg_count
361 , p_data => p_msg_data
362 );
363
364 EXCEPTION
365
366 WHEN FND_API.G_EXC_ERROR THEN
367
368 p_return_status := FND_API.G_RET_STS_ERROR;
369
370 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
371
372 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
373
374 WHEN OTHERS THEN
375
376 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377
378 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
379 THEN
380 OE_MSG_PUB.Add_Exc_Msg
381 ( G_PKG_NAME
382 , 'Check_Available_Credit'
383 );
384 END IF;
385
386 END Check_Available_Credit;
387
388 /* ----------------------------------------------------------------------
389 Procedure to determine if the order is subject to credit check and
390 if it is, return whether the check is at the site level or at the
391 customer level. Also, returns the credit limits at that level.
392 -------------------------------------------------------------------------
393 */
394
395
396 PROCEDURE Check_Order
397 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
398 , p_calling_action IN VARCHAR2 := 'BOOKING'
399 , p_check_order_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
400 , p_credit_rule_out OUT NOCOPY /* file.sql.39 change */ NUMBER
401 , p_credit_check_lvl_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
402 , p_overall_credit_limit OUT NOCOPY /* file.sql.39 change */ NUMBER
403 , p_trx_credit_limit OUT NOCOPY /* file.sql.39 change */ NUMBER
404 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
405 )
406 IS
407 -- Set up working Variables for Check Order
408 l_credit_limit_test NUMBER := FND_API.G_MISS_NUM;
409 l_credit_check_term NUMBER := FND_API.G_MISS_NUM;
410 l_credit_check_rule_id NUMBER := FND_API.G_MISS_NUM;
411 l_credit_check_lvl_out VARCHAR2(30) := 'SITE';
412 l_check_order VARCHAR2(1);
413 l_invoice_to_cust_id NUMBER;
414
415 BEGIN
416
417 -- Function to Determine if the Order Type is Subject to Credit Check
418 -- Assume that the Order is subject to credit Check by setting the
419 -- l_check_order variable to Yes.
420
421 p_check_order_out := 'Y';
422 l_check_order := 'Y';
423
424 -- Set up a variable to capture the situation of having no credit
425 -- profile set up.
426
427 p_return_status := FND_API.G_RET_STS_SUCCESS;
428
429 -- Read the Credit Rules on the Order Type Definition for the
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
439 SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
440 INTO l_credit_check_rule_id
441 FROM OE_ORDER_TYPES_V
442 WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
443 7194250*/
444 --7194250
445 BEGIN --7695423
446 SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
447 INTO l_credit_check_rule_id
448 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
449 WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
450 AND ENTRY_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
451 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
452 --7695423 start
453 EXCEPTION
454 WHEN OTHERS THEN
455 l_credit_check_rule_id := -1;
456 END;
457 --7695423 end
458
459 --7194250
460
461 OE_Verify_Payment_PUB.G_credit_check_rule := 'Ordering'; --ER#7479609
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
471 WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
472 7194250*/
473 --7194250
474 BEGIN --7695423
475 SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
476 INTO l_credit_check_rule_id
477 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
478 WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
479 AND SHIPPING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
480 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
481 --7695423 start
482 EXCEPTION
483 WHEN OTHERS THEN
484 l_credit_check_rule_id := -1;
485 END;
486 --7695423 end
487
488 --7194250
489
490 OE_Verify_Payment_PUB.G_credit_check_rule := 'Shipping'; --ER#7479609
491
492 END IF;
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;
502
503 -- If the Order type is subject to credit check we should check the
504 -- bill to site and customer to see if they are subject to credit check.
505
506 IF l_check_order = 'Y' THEN
507
508 -- The Order type is Subject to Credit Check.
509 -- Procedure to determine if the Order Bill to is subject to Credit Check
510 -- Get the flags that control if credit check should be performed
511 -- from the Credit Profile. Get the Credit Limit Amounts from the
512 -- Credit Profile Amounts for the Customers Bill to Address.
513
514 /* Find Customer Id of the Header Invoice To Org */
515 SELECT CUSTOMER_ID
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')
525 , (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
526 , NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
527 , NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
528 INTO l_check_order
529 , l_credit_limit_test
530 , p_overall_credit_limit
531 , p_trx_credit_limit
532 FROM HZ_CUSTOMER_PROFILES CP
533 , HZ_CUST_PROFILE_AMTS CPA
534 WHERE CP.CUST_ACCOUNT_ID = l_invoice_to_cust_id
535 AND CP.SITE_USE_ID = p_header_rec.invoice_to_org_id
536 AND CPA.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
537 AND CPA.SITE_USE_ID = CP.SITE_USE_ID
538 AND CPA.CUST_ACCOUNT_PROFILE_ID = CP.CUST_ACCOUNT_PROFILE_ID
539 AND CP.STATUS='A' --14699527
540 AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code;
541
542 -- If we find a Credit Profile at this level the customer has credit limits
543 -- at the site level.
544
545
546 l_credit_check_lvl_out := 'SITE';
547
548 EXCEPTION
549
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
559 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
560 OE_MSG_PUB.Add_Exc_Msg
561 ( G_PKG_NAME
562 , 'Check Order:Check Customer'
563 );
564 END IF;
565 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567
568 END;
569
570 -- If no credit limit has been set for either the total Order Amount
571 -- or the Total Exposure, get the credit profiles from the customer level
572 IF l_credit_limit_test < 0 and l_credit_check_lvl_out ='SITE' THEN
573 l_credit_check_lvl_out := 'CUSTOMER';
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
583 -- and no credit profile was found at the bill to site go on to
584 -- check if the customer is subject to credit check.
585 -- Procedure to Determine if the Customer is subject to Credit Check
586
587 BEGIN
588
589 SELECT NVL(CP.CREDIT_CHECKING,'N')
590 , (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
591 , NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
592 , NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
593 INTO l_check_order
594 , l_credit_limit_test
595 , p_overall_credit_limit
596 , p_trx_credit_limit
597 FROM HZ_CUSTOMER_PROFILES CP
598 , HZ_CUST_PROFILE_AMTS CPA
599 WHERE CP.CUST_ACCOUNT_ID = l_invoice_to_cust_id
600 AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
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
610 -- If we don't find a credit profile at the customer level, no
611 -- credit checking needed.
612 l_check_order := 'N';
613
614 WHEN OTHERS THEN
615 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
616 OE_MSG_PUB.Add_Exc_Msg
617 ( G_PKG_NAME
618 , 'Check Order:Check Customer'
619 );
620 END IF;
621 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
623 END;
624
625 -- If no credit limit has been set for either the total Order Amount
626 -- or the Total Exposure, do not credit check.
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
636
637 -- Order Type, Bill to or Customer are subject to Credit Check
638 -- If the Order Type, Bill to Site and Customer are subject to credit check,
639 -- go on to check if the payment term is subject to credit check.
640 -- Procedure to Determine if Payment Term is subject to Credit Check
641 -- Check if any line on the order has a payment term that requires credit checking.
642 -- If at least one order line has a payment term that requires credit checking the
643 -- order is subject to credit check.
644
645 BEGIN
646
647 SELECT COUNT(*)
648 INTO l_credit_check_term
649 FROM OE_ORDER_LINES L, OE_RA_TERMS_V T
650 WHERE L.HEADER_ID = p_header_rec.header_id
651 AND T.TERM_ID = L.PAYMENT_TERM_ID
652 AND NVL(T.CREDIT_CHECK_FLAG, 'Y') = 'Y';
653
654 -- If no line exists with a payment term that is subject to credit check
655 -- we should exempt the order from credit check.
656
657 IF l_credit_check_term = 0 THEN
658 l_check_order := 'N';
659 END IF;
660
661 EXCEPTION
662
663 WHEN OTHERS THEN
664 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
665 OE_MSG_PUB.Add_Exc_Msg
666 ( G_PKG_NAME
667 , 'Check_Order: Check Terms'
668 );
669 END IF;
670 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
671 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
672
673 END;
674
675 END IF; -- Order Type, Bill to or Customer are subject to Credit Check
676
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)
686 THEN
687 OE_MSG_PUB.Add_Exc_Msg
688 ( G_PKG_NAME
689 , 'Check_Order'
690 );
691 END IF;
692 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
694
695 END Check_Order;
696
697
698
699 PROCEDURE Check_Exposure
700 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
701 , p_credit_check_rule_id IN NUMBER
702 , p_credit_level IN VARCHAR2
703 , p_total_exposure OUT NOCOPY /* file.sql.39 change */ NUMBER
704 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
705 )
706 IS
707 l_open_ar_balance_flag Varchar2(1) := 'Y';
708 l_open_ar_days NUMBER;
709 l_uninvoiced_orders_flag Varchar2(1) := 'Y';
710 l_orders_on_hold_flag Varchar2(1) := 'Y';
711
712 /* additional task -added the following variable */
713 l_include_tax_flag Varchar2(1) := 'Y';
714
715 l_shipping_horizon Date := TO_DATE('31/12/4712', 'DD/MM/YYYY');
716 l_include_risk_flag Varchar2(1) := 'Y';
717 l_total_from_ar NUMBER := 0;
718 l_total_on_order NUMBER := 0;
719 l_total_on_hold NUMBER := 0;
720 l_total_exposure NUMBER := 0;
721 l_payments_at_risk NUMBER := 0;
722 l_current_order NUMBER := 0;
723
724 -- ZB
725 l_maximum_days_past_due NUMBER := 0;
726
727 --additional task : added the following variable
728 l_est_valid_days NUMBER := 0 ;
729
730 l_total_commitment NUMBER := 0;
731 l_current_commitment NUMBER := 0;
732 l_on_hold_commitment NUMBER := 0;
733
734 l_invoice_to_cust_id NUMBER;
735
736 /* additional task - for adding the Include Tax option, the cursor needs to be modified */
737
738 Cursor credit_check_rule IS
739 SELECT OPEN_AR_BALANCE_FLAG
740 , OPEN_AR_DAYS
741 , UNINVOICED_ORDERS_FLAG
742 , ORDERS_ON_HOLD_FLAG
743 , INCLUDE_TAX_FLAG
744 , DECODE(SHIPPING_INTERVAL, NULL,
745 TO_DATE('31/12/4712', 'DD/MM/YYYY'),
746 SHIPPING_INTERVAL+SYSDATE)
747 , INCLUDE_PAYMENTS_AT_RISK_FLAG
748 FROM OE_CREDIT_CHECK_RULES
749 WHERE CREDIT_CHECK_RULE_ID = p_credit_check_rule_id;
750
751 Cursor ar_balance IS
752 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
753 FROM AR_PAYMENT_SCHEDULES
754 WHERE CUSTOMER_ID = l_invoice_to_cust_id
755 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
756 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
757
758 Cursor ar_balance_in_ar_days IS
759 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
760 FROM AR_PAYMENT_SCHEDULES
761 WHERE CUSTOMER_ID = l_invoice_to_cust_id
762 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
763 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
764 AND SYSDATE - TRX_DATE > l_open_ar_days;
765
766 Cursor pay_risk IS
767 SELECT NVL(SUM(CRH.AMOUNT), 0)
768 FROM AR_CASH_RECEIPT_HISTORY CRH
769 , AR_CASH_RECEIPTS CR
770 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
771 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
772 AND CRH.CURRENT_RECORD_FLAG = 'Y'
773 AND CRH.STATUS <>
774 DECODE(CRH.FACTOR_FLAG,'Y',
775 'RISK_ELIMINATED','CLEARED')
776 AND CRH.STATUS <> 'REVERSED'
777 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
778 AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id;
779
780 Cursor pay_risk_in_ar_days IS
781 SELECT NVL(SUM(CRH.AMOUNT), 0)
782 FROM AR_CASH_RECEIPT_HISTORY CRH
783 , AR_CASH_RECEIPTS CR
784 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
785 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
786 AND CRH.CURRENT_RECORD_FLAG = 'Y'
787 AND CRH.STATUS <>
788 DECODE(CRH.FACTOR_FLAG,'Y',
789 'RISK_ELIMINATED','CLEARED')
790 AND CRH.STATUS <> 'REVERSED'
791 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
792 AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id
793 AND SYSDATE - CR.RECEIPT_DATE > l_open_ar_days;
794
795
796 /* additional task : modified the following 2 cursors uninvoiced_orders and
797 orders_on_hold for the following purpose
798 1. excluded unbooked orders
799 2. excluded orders authorized by credit card
800 3. Exclude the Current Order
801
802 So far there was a bug in the application - if the current order was
803 on hold and booked then current order value was not getting included
804 into the credit exposure. With present logic, we exclude the current order
805 initially while calculating the uninvoiced order total and orders_on_hold
806 total and then add it up later. Because irrespective of the fact that
807 current order is booked/entered/on hold etc, we have to take care of that
808 amount in Credit exposure.
809
810 Two additional conditions has been added for all cursors related
811 to Include Uninvoiced Orders. We will consider only those orders
812 which are not invoiced yet, i.e. invoiced qty is zero.
813 Also we should consider Open Orders Only
814 The decode statement has been added to improve performance*/
815
816 Cursor uninvoiced_orders(p_include_tax VARCHAR2) IS
817 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
818 *NVL(unit_selling_price,0)) + decode(p_include_tax,'Y',NVL(tax_value,0), 0))
819 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H --ksurendr SQL# 16485169 Removed OE_PAYMENTS
820 WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id --table from outer query as it is not used.
821 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
822 AND H.OPEN_FLAG = 'Y'
823 AND H.HEADER_ID = L.HEADER_ID
824 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
825 <= l_shipping_horizon
826 AND NVL(L.INVOICED_QUANTITY,0) = 0
827 AND L.OPEN_FLAG ='Y'
828 AND L.LINE_CATEGORY_CODE ='ORDER'
829 AND H.BOOKED_FLAG = 'Y'
830 AND H.HEADER_ID <> p_header_rec.header_id
831 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
832 OR (h.payment_type_code = 'CREDIT_CARD'
833 AND NOT EXISTS
834 (Select 'valid auth code'
835 From oe_payments op,
836 iby_trxn_ext_auths_v ite
837 Where op.header_id = h.header_id
838 And op.trxn_extension_id = ite.trxn_extension_id
839 And authorization_status = 0
840 And effective_auth_amount > 0)
841 )
842 );
843
844 /* changed for R12 cc encryption
845 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
846 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
847 */
848
849 /* old code, logic remaining same:
850 AND (H.CREDIT_CARD_APPROVAL_CODE IS NULL
851 OR
852 (H.CREDIT_CARD_APPROVAL_CODE IS NOT NULL
853 AND H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days < SYSDATE))*/
854
855 Cursor orders_on_hold(p_include_tax VARCHAR2) IS
856 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
857 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y',NVL(tax_value,0),0))
858 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
859 WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id
860 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
861 AND H.OPEN_FLAG = 'Y'
862 AND H.HEADER_ID = L.HEADER_ID
863 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
864 <= l_shipping_horizon
865 AND NVL(L.INVOICED_QUANTITY,0) = 0
866 AND L.OPEN_FLAG ='Y'
867 AND L.LINE_CATEGORY_CODE ='ORDER'
868 AND H.BOOKED_FLAG = 'Y'
869 AND H.HEADER_ID <> p_header_rec.header_id
870 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
871 OR (h.payment_type_code = 'CREDIT_CARD'
872 AND NOT EXISTS
873 (Select 'valid auth code'
874 From oe_payments op,
875 iby_trxn_ext_auths_v ite
876 Where op.header_id = h.header_id
877 And op.trxn_extension_id = ite.trxn_extension_id
878 And authorization_status = 0
879 And effective_auth_amount > 0)
880 )
881 )
882 /*
883 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
884 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
885 */
886 AND EXISTS (SELECT 1
887 FROM OE_ORDER_HOLDS OH
888 WHERE H.HEADER_ID = OH.HEADER_ID
889 AND OH.HOLD_RELEASE_ID IS NULL
890 );
891
892 Cursor commitment_total IS
893 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
894 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
895 WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id
896 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
897 AND H.OPEN_FLAG = 'Y'
898 AND H.BOOKED_FLAG = 'Y'
899 AND H.HEADER_ID = P.HEADER_ID
900 AND H.HEADER_ID <> p_header_rec.header_id
901 /*
902 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
903 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
904 */
905 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
906 OR (h.payment_type_code = 'CREDIT_CARD'
907 AND NOT EXISTS
908 (Select 'valid auth code'
909 From oe_payments op,
910 iby_trxn_ext_auths_v ite
911 Where op.header_id = h.header_id
912 And op.trxn_extension_id = ite.trxn_extension_id
913 And authorization_status = 0
914 And effective_auth_amount > 0)
915 )
916 )
917 AND L.HEADER_ID = H.HEADER_ID
918 AND L.LINE_ID = P.LINE_ID
919 AND NVL(L.INVOICED_QUANTITY,0) = 0
920 AND L.OPEN_FLAG = 'Y'
921 AND L.LINE_CATEGORY_CODE = 'ORDER'
922 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
923 <= l_shipping_horizon;
924
925 Cursor on_hold_commitment_total IS
926 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
927 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
928 WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id
929 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
930 AND H.OPEN_FLAG = 'Y'
931 AND H.BOOKED_FLAG = 'Y'
932 AND H.HEADER_ID = P.HEADER_ID
933 AND H.HEADER_ID <> p_header_rec.header_id
934 /*
935 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
936 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
937 */
938 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
939 OR (h.payment_type_code = 'CREDIT_CARD'
940 AND NOT EXISTS
941 (Select 'valid auth code'
942 From oe_payments op,
943 iby_trxn_ext_auths_v ite
944 Where op.header_id = h.header_id
945 And op.trxn_extension_id = ite.trxn_extension_id
946 And authorization_status = 0
947 And effective_auth_amount > 0)
948 )
949 )
950 AND L.HEADER_ID = H.HEADER_ID
951 AND L.LINE_ID = P.LINE_ID
952 AND NVL(L.INVOICED_QUANTITY,0) = 0
953 AND L.OPEN_FLAG = 'Y'
954 AND L.LINE_CATEGORY_CODE = 'ORDER'
955 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
956 <= l_shipping_horizon
957 AND EXISTS (SELECT 'Hold Exists'
958 FROM OE_ORDER_HOLDS OH
959 WHERE H.HEADER_ID = OH.HEADER_ID
960 AND OH.HOLD_RELEASE_ID IS NULL );
961
962 Cursor current_commitment_total IS
963 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
964 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
965 WHERE H.HEADER_ID = p_header_rec.header_id
966 AND H.HEADER_ID = P.HEADER_ID
967 /*
968 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
969 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
970 */
971 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
972 OR (h.payment_type_code = 'CREDIT_CARD'
973 AND NOT EXISTS
974 (Select 'valid auth code'
975 From oe_payments op,
976 iby_trxn_ext_auths_v ite
977 Where op.header_id = h.header_id
978 And op.trxn_extension_id = ite.trxn_extension_id
979 And authorization_status = 0
980 And effective_auth_amount > 0)
981 )
982 )
983 AND L.HEADER_ID = H.HEADER_ID
984 AND L.LINE_ID = P.LINE_ID
985 AND NVL(L.INVOICED_QUANTITY,0) = 0
986 AND L.OPEN_FLAG = 'Y'
987 AND L.LINE_CATEGORY_CODE = 'ORDER';
988
989 -- CURSORS FOR SITE - LEVEL EXPOSURE CALCULATIONS
990 Cursor site_ar_balance IS
991 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
992 FROM AR_PAYMENT_SCHEDULES
993 WHERE CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id
994 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
995 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
996
997 Cursor site_ar_balance_in_ar_days IS
998 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
999 FROM AR_PAYMENT_SCHEDULES
1000 WHERE CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id
1001 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
1002 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
1003 AND SYSDATE - TRX_DATE > l_open_ar_days;
1004
1005 Cursor site_pay_risk IS
1006 SELECT NVL(SUM(CRH.AMOUNT),0)
1007 FROM AR_CASH_RECEIPT_HISTORY CRH
1008 , AR_CASH_RECEIPTS CR
1009 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1010 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
1011 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1012 AND CRH.STATUS <>
1013 DECODE(CRH.FACTOR_FLAG,'Y',
1014 'RISK_ELIMINATED','CLEARED')
1015 AND CRH.STATUS <> 'REVERSED'
1016 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
1017 AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id
1018 AND CR.CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id;
1019
1020 Cursor site_pay_risk_in_ar_days IS
1021 SELECT NVL(SUM(CRH.AMOUNT),0)
1022 FROM AR_CASH_RECEIPT_HISTORY CRH
1023 , AR_CASH_RECEIPTS CR
1024 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1025 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
1026 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1027 AND CRH.STATUS <>
1028 DECODE(CRH.FACTOR_FLAG,'Y',
1029 'RISK_ELIMINATED','CLEARED')
1030 AND CRH.STATUS <> 'REVERSED'
1031 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
1032 AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id
1033 AND CR.CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id
1034 AND SYSDATE - TRX_DATE > l_open_ar_days;
1035
1036 /* additional task : modified the following 2 SITE cursors
1037 1. excluded unbooked orders
1038 2. excluded orders authorized by credit card
1039 3. Excludes the current order
1040 */
1041
1042 Cursor site_uninvoiced_orders(p_include_tax VARCHAR2) IS
1043 SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0) )
1044 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', nvl(tax_value,0), 0))
1045 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
1046 WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
1047 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
1048 AND H.OPEN_FLAG = 'Y'
1049 AND H.HEADER_ID = L.HEADER_ID
1050 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
1051 <= l_shipping_horizon
1052 AND NVL(L.INVOICED_QUANTITY,0) = 0
1053 AND L.OPEN_FLAG ='Y'
1054 AND L.LINE_CATEGORY_CODE ='ORDER'
1055 AND H.BOOKED_FLAG = 'Y'
1056 AND H.HEADER_ID <> p_header_rec.header_id
1057 /*
1058 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
1059 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
1060 */
1061 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
1062 OR (h.payment_type_code = 'CREDIT_CARD'
1063 AND NOT EXISTS
1064 (Select 'valid auth code'
1065 From oe_payments op,
1066 iby_trxn_ext_auths_v ite
1067 Where op.header_id = h.header_id
1068 And op.trxn_extension_id = ite.trxn_extension_id
1069 And authorization_status = 0
1070 And effective_auth_amount > 0)
1071 )
1072 );
1073
1074 Cursor site_orders_on_hold(p_include_tax VARCHAR2) IS
1075
1076 SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0))
1077 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', NVL(tax_value,0),0))
1078 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
1079 WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
1080 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
1081 AND H.OPEN_FLAG = 'Y'
1082 AND H.HEADER_ID = L.HEADER_ID
1083 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
1084 <= l_shipping_horizon
1085 AND NVL(L.INVOICED_QUANTITY,0) = 0
1086 AND L.OPEN_FLAG ='Y'
1087 AND L.LINE_CATEGORY_CODE ='ORDER'
1088 AND H.BOOKED_FLAG = 'Y'
1089 AND H.HEADER_ID <> p_header_rec.header_id
1090 /*
1091 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
1092 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
1093 */
1094 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
1095 OR (h.payment_type_code = 'CREDIT_CARD'
1096 AND NOT EXISTS
1097 (Select 'valid auth code'
1098 From oe_payments op,
1099 iby_trxn_ext_auths_v ite
1100 Where op.header_id = h.header_id
1101 And op.trxn_extension_id = ite.trxn_extension_id
1102 And authorization_status = 0
1103 And effective_auth_amount > 0)
1104 )
1105 )
1106 AND EXISTS (SELECT 1
1107 FROM OE_ORDER_HOLDS OH
1108 WHERE H.HEADER_ID = OH.HEADER_ID
1109 AND OH.HOLD_RELEASE_ID IS NULL
1110 );
1111
1112 Cursor site_commitment_total IS
1113 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
1114 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
1115 WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
1116 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
1117 AND H.OPEN_FLAG = 'Y'
1118 AND H.BOOKED_FLAG = 'Y'
1119 AND H.HEADER_ID = P.HEADER_ID
1120 /*
1121 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
1122 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
1123 */
1124 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
1125 OR (h.payment_type_code = 'CREDIT_CARD'
1126 AND NOT EXISTS
1127 (Select 'valid auth code'
1128 From oe_payments op,
1129 iby_trxn_ext_auths_v ite
1130 Where op.header_id = h.header_id
1131 And op.trxn_extension_id = ite.trxn_extension_id
1132 And authorization_status = 0
1133 And effective_auth_amount > 0)
1134 )
1135 )
1136 AND L.HEADER_ID = H.HEADER_ID
1137 AND L.LINE_ID = P.LINE_ID
1138 AND NVL(L.INVOICED_QUANTITY,0) = 0
1139 AND L.OPEN_FLAG = 'Y'
1140 AND L.LINE_CATEGORY_CODE = 'ORDER'
1141 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
1142 <= l_shipping_horizon;
1143
1144 Cursor site_on_hold_commitment_total IS
1145 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
1146 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
1147 WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
1148 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
1149 AND H.OPEN_FLAG = 'Y'
1150 AND H.BOOKED_FLAG = 'Y'
1151 AND H.HEADER_ID = P.HEADER_ID
1152 /*
1153 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
1154 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
1155 */
1156 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
1157 OR (h.payment_type_code = 'CREDIT_CARD'
1158 AND NOT EXISTS
1159 (Select 'valid auth code'
1160 From oe_payments op,
1161 iby_trxn_ext_auths_v ite
1162 Where op.header_id = h.header_id
1163 And op.trxn_extension_id = ite.trxn_extension_id
1164 And authorization_status = 0
1165 And effective_auth_amount > 0)
1166 )
1167 )
1168 AND L.HEADER_ID = H.HEADER_ID
1169 AND L.LINE_ID = P.LINE_ID
1170 AND NVL(L.INVOICED_QUANTITY,0) = 0
1171 AND L.OPEN_FLAG = 'Y'
1172 AND L.LINE_CATEGORY_CODE = 'ORDER'
1173 AND H.HEADER_ID <> p_header_rec.header_id
1174 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
1175 <= l_shipping_horizon
1176 AND EXISTS (SELECT 'Hold Exists'
1177 FROM OE_ORDER_HOLDS OH
1178 WHERE H.HEADER_ID = OH.HEADER_ID
1179 AND OH.HOLD_RELEASE_ID IS NULL );
1180
1181 /* additional task- Current Order - */
1182
1183 -- modified cursor for bug 1655720, to include lines falling outside of
1184 -- shipping horizon for credit checking.
1185 Cursor current_order(p_include_tax VARCHAR2) IS
1186 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
1187 *NVL(unit_selling_price,0)) + decode(p_include_tax,'Y', NVL(tax_value,0), 0))
1188 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
1189 WHERE H.HEADER_ID = p_header_rec.header_id
1190 AND H.HEADER_ID = L.HEADER_ID
1191 -- AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
1192 -- <= to_date(l_shipping_horizon, 'DD-MON-YY')
1193 AND NVL(L.INVOICED_QUANTITY,0) = 0
1194 AND L.OPEN_FLAG ='Y'
1195 AND L.LINE_CATEGORY_CODE ='ORDER'
1196 /*
1197 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
1198 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
1199 */
1200 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
1201 OR (h.payment_type_code = 'CREDIT_CARD'
1202 AND NOT EXISTS
1203 (Select 'valid auth code'
1204 From oe_payments op,
1205 iby_trxn_ext_auths_v ite
1206 Where op.header_id = h.header_id
1207 And op.trxn_extension_id = ite.trxn_extension_id
1208 And authorization_status = 0
1209 And effective_auth_amount > 0)
1210 )
1211 );
1212
1213
1214 BEGIN
1215
1216 -- Set the default behaviour to pass credit check exposure
1217 p_return_status := FND_API.G_RET_STS_SUCCESS;
1218 -- Read the Credit rule and determine the inclusions and exclusions for exposure
1219
1220 -- Include Amounts on Receivables if within the AR horizon. Add the OPEN_AR_DAYS
1221 -- to todays date to determine the AR Horizon. Use this to compare the Due date on
1222 -- the Invoice. If the due_date is greater than system date by less than open_ar_days
1223 -- In the R10SC/R11, if the trx_date/invoice_date is earlier than system date
1224 -- by open_ar_days, those
1225 -- orders only are considered. So the check was WHERE SYSDATE - TRX_DATE > OPEN_AR_DAYS
1226
1227 -- Include amounts on Order Backlog if within the shipping horizon. Add the Shipping Horizon
1228 -- to todays date to determin the shipping horizon. Use this to compare the scheduled ship date on
1229 -- the order line.
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
1239 , l_uninvoiced_orders_flag
1240 , l_orders_on_hold_flag
1241 , l_include_tax_flag
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);
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));
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 */
1262 SELECT CUSTOMER_ID
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
1272 IF l_open_ar_balance_flag = 'Y' THEN
1273
1274 -- Find Accounts Receivable Exposure
1275
1276 IF l_open_ar_days IS NULL THEN
1277
1278 OPEN ar_balance;
1279 FETCH ar_balance INTO l_total_from_ar;
1280 IF ar_balance%notfound THEN
1281 l_total_from_ar := 0;
1282 END IF;
1283 CLOSE ar_balance;
1284
1285 ELSE
1286
1287 OPEN ar_balance_in_ar_days;
1288 FETCH ar_balance_in_ar_days INTO l_total_from_ar;
1289 IF ar_balance_in_ar_days%notfound THEN
1290 l_total_from_ar := 0;
1291 END IF;
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
1301 */
1302
1303
1304 IF l_include_risk_flag = 'Y' THEN
1305
1306 IF l_open_ar_days IS NULL THEN
1307
1308 OPEN pay_risk;
1309 FETCH pay_risk INTO l_payments_at_risk;
1310 IF pay_risk%notfound THEN
1311 l_payments_at_risk := 0;
1312 END IF;
1313 CLOSE pay_risk;
1314
1315 ELSE
1316
1317 OPEN pay_risk_in_ar_days;
1318 FETCH pay_risk_in_ar_days INTO l_payments_at_risk;
1319 IF pay_risk_in_ar_days%notfound THEN
1320 l_payments_at_risk := 0;
1321 END IF;
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
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
1339
1340 /* additional task - depending on the include_tax_flag value tax will be included or
1341 excluded from credit exposure calculation */
1342
1343 IF l_uninvoiced_orders_flag = 'Y' THEN
1344
1345 /* additional task */
1346
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
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
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
1371 hold and hence, the following calculation doesn't need to be done.
1372 */
1373
1374 IF l_orders_on_hold_flag = 'N' THEN
1375
1376 -- Find the value of all orders that are the subject of an order hold
1377 -- at either the header or line level.
1378
1379 /* additional task - Added the logic for include_tax_flag */
1380
1381 OPEN orders_on_hold(l_include_tax_flag);
1382 FETCH orders_on_hold INTO l_total_on_hold;
1383 IF orders_on_hold%notfound THEN
1384 l_total_on_hold := 0;
1385 END IF;
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));
1395
1396 END IF; -- orders on hold flag
1397
1398 -- Check Commitment Total if Commitment Sequencing "On"
1399 IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
1400
1401 OPEN commitment_total;
1402 FETCH commitment_total INTO l_total_commitment;
1403 IF commitment_total%notfound THEN
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
1413 OPEN on_hold_commitment_total;
1414 FETCH on_hold_commitment_total INTO l_on_hold_commitment;
1415 IF on_hold_commitment_total%notfound THEN
1416 l_on_hold_commitment := 0;
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;
1426 IF current_commitment_total%notfound THEN
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);
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 */
1445
1446
1447 OPEN current_order(l_include_tax_flag);
1448 FETCH current_order INTO l_current_order;
1449 IF current_order%notfound THEN
1450 l_current_order := 0;
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
1460 ELSE -- Retrieving exposure at SITE level
1461
1462
1463 IF l_open_ar_balance_flag = 'Y' THEN
1464
1465 -- Find Accounts Receivable Exposure
1466
1467 IF l_open_ar_days IS NULL THEN
1468
1469 OPEN site_ar_balance;
1470 FETCH site_ar_balance INTO l_total_from_ar;
1471 IF site_ar_balance%notfound THEN
1472 l_total_from_ar := 0;
1473 END IF;
1474 CLOSE site_ar_balance;
1475
1476 ELSE
1477
1478 OPEN site_ar_balance_in_ar_days;
1479 FETCH site_ar_balance_in_ar_days INTO l_total_from_ar;
1480 IF site_ar_balance_in_ar_days%notfound THEN
1481 l_total_from_ar := 0;
1482 END IF;
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
1492 */
1493
1494
1495 IF l_include_risk_flag = 'Y' THEN
1496
1497 IF l_open_ar_days IS NULL THEN
1498
1499 OPEN site_pay_risk;
1500 FETCH site_pay_risk INTO l_payments_at_risk;
1501 IF site_pay_risk%notfound THEN
1502 l_payments_at_risk := 0;
1503 END IF;
1504 CLOSE site_pay_risk;
1505
1506 ELSE
1507
1508 OPEN site_pay_risk_in_ar_days;
1509 FETCH site_pay_risk_in_ar_days INTO l_payments_at_risk;
1510 IF site_pay_risk_in_ar_days%notfound THEN
1511 l_payments_at_risk := 0;
1512 END IF;
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.
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
1529 /* If the include_uninvoiced_orders is set to yes,depending on the
1530 value of include_tax_flag, tax valuw will be included in the
1531 calculation of total_exposure.
1532 */
1533
1534
1535 IF l_uninvoiced_orders_flag = 'Y' THEN
1536
1537 /* additional task - added logic for include_tax_flag */
1538
1539 OPEN site_uninvoiced_orders(l_include_tax_flag);
1540 FETCH site_uninvoiced_orders INTO l_total_on_order;
1541 IF site_uninvoiced_orders%notfound THEN
1542 l_total_on_order := 0;
1543 END IF;
1544 CLOSE site_uninvoiced_orders;
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
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
1557 on hold and hence, the following calculation doesn't need to be done.
1558 */
1559
1560
1561 IF l_orders_on_hold_flag = 'N' THEN
1562
1563 -- Find the value of all orders that are the subject of an order hold
1564 -- at either the header or line level.
1565
1566 /* additional task - added logic for include_tax_flag */
1567
1568 OPEN site_orders_on_hold(l_include_tax_flag);
1569 FETCH site_orders_on_hold INTO l_total_on_hold;
1570 IF site_orders_on_hold%notfound THEN
1571 l_total_on_hold := 0;
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
1581 END IF; -- check orders on hold
1582
1583 -- Check Commitment Total if Commitment Sequencing "On"
1584 IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
1585
1586 OPEN site_commitment_total;
1587 FETCH site_commitment_total INTO l_total_commitment;
1588 IF site_commitment_total%notfound THEN
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
1598 OPEN site_on_hold_commitment_total;
1599 FETCH site_on_hold_commitment_total INTO l_on_hold_commitment;
1600 IF site_on_hold_commitment_total%notfound THEN
1601 l_on_hold_commitment := 0;
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
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));
1617
1618 END IF; -- Commitment Sequencing
1619
1620 /* additional task - ADD Current Order */
1621
1622
1623 OPEN current_order(l_include_tax_flag);
1624 FETCH current_order INTO l_current_order;
1625 IF current_order%notfound THEN
1626 l_current_order := 0;
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
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
1643
1644 WHEN others THEN
1645 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1646 OE_MSG_PUB.Add_Exc_Msg
1647 ( G_PKG_NAME
1648 , 'Check_Exposure'
1649 );
1650 END IF;
1651 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1652
1653 RAISE ;
1654
1655 END Check_Exposure;
1656
1657 -- bug 1830389, new procedures introduced for line level credit checking.
1658 procedure chk_past_due_invoice_line (
1659 p_header_rec IN OE_Order_PUB.Header_Rec_Type
1660 ,p_invoice_to_org_id IN NUMBER
1661 ,p_customer_id IN NUMBER
1662 ,p_credit_rule_id IN NUMBER
1663 ,p_credit_level IN VARCHAR2
1664 ,p_check_past_due OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1665 ,p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1666 )
1667 IS
1668 l_maximum_days_past_due NUMBER;
1669 l_dummy VARCHAR2(30);
1670 BEGIN
1671 -- Default to pass back
1672 p_check_past_due := 'N';
1673
1674 select NVL(maximum_days_past_due, 0)
1675 into l_maximum_days_past_due
1676 from OE_CREDIT_CHECK_RULES
1677 where CREDIT_CHECK_RULE_ID = p_credit_rule_id;
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'
1687 into l_dummy
1688 from AR_PAYMENT_SCHEDULES
1689 WHERE CUSTOMER_ID = p_customer_id
1690 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
1691 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
1692 AND AMOUNT_DUE_REMAINING > 0
1693 AND DUE_DATE < sysdate - l_maximum_days_past_due;
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
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;
1710 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1711
1712 OE_MSG_PUB.Add_Exc_Msg
1713 ( G_PKG_NAME
1714 ,'chk_past_due_invoice'
1715 );
1716 END IF;
1717 RAISE;
1718
1719 END chk_past_due_invoice_line;
1720
1721
1722 -- calculates total exposure, find credit limits and determin result for calling function.
1723 /* additional task - made the procedure Check_trx_Limit
1724 local to this package and added p_credit_rule_id as
1725 an additional input parameter */
1726
1727
1728 PROCEDURE Check_Trx_Limit_Line
1729 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
1730 , p_invoice_to_org_id IN NUMBER
1731 , p_customer_id IN NUMBER
1732 , p_credit_rule_id IN NUMBER
1733 , p_credit_level IN VARCHAR2
1734 , p_trx_credit_limit IN NUMBER := FND_API.G_MISS_NUM
1735 , p_total_exposure IN NUMBER
1736 , p_overall_credit_limit IN NUMBER
1737 , p_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1738 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1739 )
1740 IS
1741
1742 l_order_value NUMBER;
1743 l_include_tax_flag VARCHAR2(1) := 'Y';
1744
1745 l_order_commitment NUMBER;
1746
1747 BEGIN
1748
1749 -- Initialize return status to success
1750 p_return_status := FND_API.G_RET_STS_SUCCESS;
1751
1752 -- Default to Pass
1753 p_result_out := 'PASS';
1754
1755 /* additional task- Read the value of include_tax_flag from credit check rule
1756 and calculate the value of l_order_values accordingly */
1757
1758 /* If the value of include_tax_flag is NULL that means it is 'No' */
1759
1760 select NVL(include_tax_flag, 'N')
1761 into l_include_tax_flag
1762 from OE_CREDIT_CHECK_RULES
1763 where CREDIT_CHECK_RULE_ID = p_credit_rule_id;
1764
1765 -- Total order limit of the site should be compared against
1766 -- the sum of all the lines that has this site
1767 -- Depending on the value of tax_flag add the tax_value
1768 /*
1769 ** Commenting based on the discussion with zbutt on 07/16/01
1770 ** Only one select to find out Trxn level order total needed
1771 IF p_credit_level = 'SITE' THEN
1772 */
1773 SELECT SUM(decode(l_include_tax_flag , 'Y', NVL(tax_value,0),0)
1774 + (NVL(unit_selling_price,0)
1775 * (NVL(ordered_quantity,0) )))
1776 INTO l_order_value
1777 FROM OE_ORDER_LINES
1778 WHERE HEADER_ID = p_header_rec.header_id
1779 AND invoice_to_org_id = p_invoice_to_org_id;
1780 /*
1781 ELSE
1782 SELECT SUM(decode(l_include_tax_flag , 'Y', NVL(tax_value,0),0)
1783 + (NVL(unit_selling_price,0)
1784 * (NVL(ordered_quantity,0) )))
1785 INTO l_order_value
1786 FROM OE_ORDER_LINES
1787 WHERE HEADER_ID = p_header_rec.header_id
1788 AND sold_to_org_id = (select organization_id
1789 from oe_sold_to_orgs_v
1790 where customer_id = p_customer_id);
1791
1792
1793 END IF;
1794 */
1795
1796 -- Get Total Commitments applied to the current order if Commitment Sequencing is "On"
1797 IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
1798
1799 SELECT NVL(SUM(P.commitment_applied_amount), 0)
1800 INTO l_order_commitment
1801 FROM OE_PAYMENTS P, OE_ORDER_LINES L
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
1811 END IF;
1812
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
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
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
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
1843 EXCEPTION
1844
1845 WHEN others THEN
1846 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1847 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1848 OE_MSG_PUB.Add_Exc_Msg
1849 ( G_PKG_NAME
1850 , 'Check_Trx_Limit_Line'
1851 );
1852 END IF;
1853 RAISE ;
1854
1855 END Check_Trx_Limit_Line;
1856
1857
1858
1859 -- Mainline Function that will read an Order Header and Determine if
1860 -- should be checked, calculates total exposure, find credit limits
1861 -- and determine result for calling function.
1862
1863 ----------------------------------------------------------------------------
1864 PROCEDURE Check_Available_Credit_Line
1865 ( p_header_id IN NUMBER := FND_API.G_MISS_NUM
1866 , p_invoice_to_org_id IN NUMBER := FND_API.G_MISS_NUM
1867 , p_calling_action IN VARCHAR2 := 'BOOKING'
1868 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1869 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1870 , p_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Pass or Fail Credit Check
1871 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1872 )
1873 IS
1874 l_header_rec OE_Order_PUB.Header_Rec_Type;
1875 --l_line_rec OE_Order_PUB.Line_Rec_Type;
1876 l_credit_rule_id NUMBER;
1877 l_credit_level VARCHAR2(30); -- are the limits at the customer
1878 -- or site level
1879 --l_order_value NUMBER;
1880 l_check_order VARCHAR2(1); -- Indicates if this Order is
1881 -- subject to credit check
1882 l_total_exposure NUMBER;
1883 l_trx_credit_limit NUMBER;
1884 l_overall_credit_limit NUMBER;
1885 l_result_out VARCHAR2(30);
1886 l_return_status VARCHAR2(30);
1887 l_check_past_due VARCHAR2(1); -- if any invoice is past due
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
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
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
1909 -- ,x_line_rec => l_line_rec );
1910
1911
1912 BEGIN
1913 SELECT customer_id
1914 INTO l_customer_id
1915 FROM oe_invoice_to_orgs_v
1916 WHERE ORGANIZATION_ID = p_invoice_to_org_id;
1917 EXCEPTION
1918 WHEN no_data_found then
1919 --x_return_status := FND_API.G_RET_STS_ERROR;
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:' ||
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
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
1941 , p_calling_action
1942 , l_check_order
1943 , l_credit_rule_id
1944 , l_credit_level
1945 , l_overall_credit_limit
1946 , l_trx_credit_limit
1947 , l_return_status
1948 );
1949
1950 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1951 RAISE FND_API.G_EXC_ERROR;
1952 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1954 END IF;
1955
1956 IF l_check_order = 'Y' THEN
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
1966 ,l_customer_id
1967 ,l_credit_rule_id
1968 ,l_credit_level
1969 ,l_check_past_due
1970 ,l_return_status
1971 );
1972 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1973 RAISE FND_API.G_EXC_ERROR;
1974 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1976 END IF;
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
1986 , l_credit_rule_id
1987 , l_credit_level
1988 , l_total_exposure
1989 , l_return_status
1990 );
1991
1992 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1993 RAISE FND_API.G_EXC_ERROR;
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
2003 /* additional task - now credit_rule_id is passed to Check_trx_Limit_line*/
2004
2005
2006 OE_Credit_PUB.Check_Trx_Limit_Line
2007 ( l_header_rec
2008 , p_invoice_to_org_id
2009 , l_customer_id
2010 , l_credit_rule_id
2011 , l_credit_level -- New
2012 , l_trx_credit_limit
2013 , l_total_exposure
2014 , l_overall_credit_limit
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
2024 RAISE FND_API.G_EXC_ERROR;
2025 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2027 END IF;
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
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
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
2050 -- the messages from the message stack.
2051
2052 OE_MSG_PUB.Count_And_Get
2053 ( p_count => p_msg_count
2054 , p_data => p_msg_data
2055 );
2056
2057 EXCEPTION
2058
2059 WHEN FND_API.G_EXC_ERROR THEN
2060
2061 p_return_status := FND_API.G_RET_STS_ERROR;
2062
2063 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2064
2065 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2066
2067 WHEN OTHERS THEN
2068
2069 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2070
2071 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2072 THEN
2073 OE_MSG_PUB.Add_Exc_Msg
2074 ( G_PKG_NAME
2075 , 'Check_Available_Credit'
2076 );
2077 END IF;
2078
2079 END Check_Available_Credit_Line;
2080
2081 /* ----------------------------------------------------------------------
2082 Procedure to determine if the Line is subject to credit check and
2083 if it is, return whether the check is at the site level or at the
2084 customer level. Also, returns the credit limits at that level.
2085 -------------------------------------------------------------------------
2086 */
2087
2088
2089 PROCEDURE Check_Order_Line
2090 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
2091 , p_invoice_to_org_id IN NUMBER
2092 , p_customer_id IN NUMBER
2093 , p_calling_action IN VARCHAR2 := 'BOOKING'
2094 , p_check_Order_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2095 , p_credit_rule_out OUT NOCOPY /* file.sql.39 change */ NUMBER
2096 , p_credit_check_lvl_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2097 , p_overall_credit_limit OUT NOCOPY /* file.sql.39 change */ NUMBER
2098 , p_trx_credit_limit OUT NOCOPY /* file.sql.39 change */ NUMBER
2099 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2100 )
2101 IS
2102 -- Set up working Variables for Check Order
2103 l_credit_limit_test NUMBER := FND_API.G_MISS_NUM;
2104 l_credit_check_term NUMBER := FND_API.G_MISS_NUM;
2105 l_credit_check_rule_id NUMBER := FND_API.G_MISS_NUM;
2106 l_credit_check_lvl_out VARCHAR2(30) := 'SITE';
2107 l_check_order VARCHAR2(1);
2108
2109 BEGIN
2110 -- Function to Determine if the Order Type is Subject to Credit Check
2111 -- Assume that the Order is subject to credit Check by setting the
2112 -- l_check_order variable to Yes.
2113
2114 p_check_Order_out := 'Y';
2115 l_check_Order := 'Y';
2116
2117 -- Set up a variable to capture the situation of having no credit
2118 -- profile set up.
2119
2120 p_return_status := FND_API.G_RET_STS_SUCCESS;
2121
2122 -- Read the Credit Rules on the Order Type Definition for the
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)
2132 INTO l_credit_check_rule_id
2133 FROM OE_ORDER_TYPES_V
2134 WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
2135 7194250*/
2136 --7194250
2137 BEGIN --7695423
2138 SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
2139 INTO l_credit_check_rule_id
2140 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
2141 WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
2142 AND ENTRY_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
2143 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
2144 --7695423 start
2145 EXCEPTION
2146 WHEN OTHERS THEN
2147 l_credit_check_rule_id := -1;
2148 END;
2149 --7695423 end
2150
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
2160 WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
2161 7194250*/
2162 --7194250
2163 BEGIN --7695423
2164 SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
2165 INTO l_credit_check_rule_id
2166 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
2167 WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
2168 AND SHIPPING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
2169 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
2170 --7695423 start
2171 EXCEPTION
2172 WHEN OTHERS THEN
2173 l_credit_check_rule_id := -1;
2174 END;
2175 --7695423 end
2176
2177 --7194250
2178 OE_Verify_Payment_PUB.G_credit_check_rule := 'Shipping'; --ER#7479609
2179 END IF;
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;
2189
2190 -- If the Order type is subject to credit check we should check the
2191 -- bill to site and customer to see if they are subject to credit check.
2192
2193 IF l_check_order = 'Y' THEN
2194
2195 -- The Order type is Subject to Credit Check.
2196 -- Procedure to determine if the Order Bill to is subject to Credit Check
2197 -- Get the flags that control if credit check should be performed
2198 -- from the Credit Profile. Get the Credit Limit Amounts from the
2199 -- Credit Profile Amounts for the Customers Bill to Address.
2200
2201 BEGIN
2202
2203 SELECT NVL(CP.CREDIT_CHECKING, 'N')
2204 , (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
2205 , NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
2206 , NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
2207 INTO l_check_order
2208 , l_credit_limit_test
2209 , p_overall_credit_limit
2210 , p_trx_credit_limit
2211 FROM HZ_CUSTOMER_PROFILES CP
2212 , HZ_CUST_PROFILE_AMTS CPA
2213 WHERE CP.CUST_ACCOUNT_ID = p_customer_id
2214 AND CP.SITE_USE_ID = p_invoice_to_org_id
2215 AND CPA.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
2216 AND CPA.SITE_USE_ID = CP.SITE_USE_ID
2217 AND CPA.CUST_ACCOUNT_PROFILE_ID = CP.CUST_ACCOUNT_PROFILE_ID
2218 AND CP.STATUS='A' --14699527
2219 AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code;
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) || '/'
2229 || to_char(p_trx_credit_limit) );
2230
2231 EXCEPTION
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
2241 ( G_PKG_NAME
2242 , 'Check Order:Check Customer'
2243 );
2244 END IF;
2245 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2247 END;
2248
2249 -- If no credit limit has been set for either the total Order Amount
2250 -- or the Total Exposure, get the credit profiles from the customer level
2251 IF l_credit_limit_test < 0 and l_credit_check_lvl_out ='SITE' THEN
2252 l_credit_check_lvl_out := 'CUSTOMER';
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
2262 -- and no credit profile was found at the bill to site go on to
2263 -- check if the customer is subject to credit check.
2264 -- Procedure to Determine if the Customer is subject to Credit Check
2265
2266 BEGIN
2267
2268 SELECT NVL(CP.CREDIT_CHECKING,'N')
2269 , (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
2270 , NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
2271 , NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
2272 INTO l_check_order
2273 , l_credit_limit_test
2274 , p_overall_credit_limit
2275 , p_trx_credit_limit
2276 FROM HZ_CUSTOMER_PROFILES CP
2277 , HZ_CUST_PROFILE_AMTS CPA
2278 WHERE CP.CUST_ACCOUNT_ID = p_customer_id
2279 AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
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) || '/'
2289 || to_char(p_trx_credit_limit) );
2290
2291 EXCEPTION
2292 WHEN NO_DATA_FOUND THEN
2293 -- If we don't find a credit profile at the customer level, no
2294 -- credit checking needed.
2295 l_check_order := 'N';
2296
2297 WHEN OTHERS THEN
2298 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2299 OE_MSG_PUB.Add_Exc_Msg
2300 ( G_PKG_NAME
2301 , 'Check Order:Check Customer'
2302 );
2303 END IF;
2304 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2306 END;
2307
2308 -- If no credit limit has been set for either the total Order Amount
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
2318 -- Order Type, Bill to or Customer are subject to Credit Check
2319 -- If the Order Type, Bill to Site and Customer are subject to credit check,
2320 -- go on to check if the payment term is subject to credit check.
2321 -- Procedure to Determine if Payment Term is subject to Credit Check
2322 -- Check if line on the order has a payment term that requires credit checking.
2323
2324 -- Check to see if all the lines for this bill to have payment term set to 'Y'
2325 BEGIN
2326
2327 SELECT COUNT(*)
2328 INTO l_credit_check_term
2329 FROM OE_ORDER_LINES L, OE_RA_TERMS_V T
2330 WHERE L.header_id = p_header_rec.header_id
2331 AND L.invoice_to_org_id = p_invoice_to_org_id
2332 AND L.PAYMENT_TERM_ID = T.TERM_ID
2333 AND NVL(T.CREDIT_CHECK_FLAG, 'Y') = 'Y';
2334
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
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
2350 , 'Check_Order: Check Terms'
2351 );
2352 END IF;
2353 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2354 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2355 END;
2356
2357 END IF; -- Order Type, Bill to or Customer are subject to Credit Check
2358
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)
2368 THEN
2369 OE_MSG_PUB.Add_Exc_Msg
2370 ( G_PKG_NAME
2371 , 'Check_Order'
2372 );
2373 END IF;
2374 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2376
2377 END Check_Order_line;
2378
2379
2380 PROCEDURE Check_Exposure_Line
2381 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
2382 , p_invoice_to_org_id IN NUMBER
2383 , p_customer_id IN NUMBER
2384 , p_credit_check_rule_id IN NUMBER
2385 , p_credit_level IN VARCHAR2
2386 , p_total_exposure OUT NOCOPY /* file.sql.39 change */ NUMBER
2387 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2388 )
2389 IS
2390 l_open_ar_balance_flag Varchar2(1) := 'Y';
2391 l_open_ar_days NUMBER;
2392 l_uninvoiced_orders_flag Varchar2(1) := 'Y';
2393 l_orders_on_hold_flag Varchar2(1) := 'Y';
2394
2395 /* additional task -added the following variable */
2396 l_include_tax_flag Varchar2(1) := 'Y';
2397
2398 l_shipping_horizon Date := TO_DATE('31/12/4712', 'DD/MM/YYYY');
2399 l_include_risk_flag Varchar2(1) := 'Y';
2400 l_total_from_ar NUMBER := 0;
2401 l_total_on_order NUMBER := 0;
2402 l_total_on_hold NUMBER := 0;
2403 l_total_exposure NUMBER := 0;
2404 l_payments_at_risk NUMBER := 0;
2405 l_current_order NUMBER := 0;
2406
2407 -- ZB
2408 l_maximum_days_past_due NUMBER := 0;
2409
2410 --additional task : added the following variable
2411 l_est_valid_days NUMBER := 0 ;
2412
2413 l_total_commitment NUMBER := 0;
2414 l_current_commitment NUMBER := 0;
2415 l_on_hold_commitment NUMBER := 0;
2416
2417 /* additional task - for adding the Include Tax option, the cursor needs to be modified */
2418
2419 Cursor credit_check_rule IS
2420 SELECT OPEN_AR_BALANCE_FLAG
2421 , OPEN_AR_DAYS
2422 , UNINVOICED_ORDERS_FLAG
2423 , ORDERS_ON_HOLD_FLAG
2424 , INCLUDE_TAX_FLAG
2425 , DECODE(SHIPPING_INTERVAL, NULL,
2426 TO_DATE('31/12/4712', 'DD/MM/YYYY'),
2427 SHIPPING_INTERVAL+SYSDATE)
2428 , INCLUDE_PAYMENTS_AT_RISK_FLAG
2429 FROM OE_CREDIT_CHECK_RULES
2430 WHERE CREDIT_CHECK_RULE_ID = p_credit_check_rule_id;
2431
2432 Cursor ar_balance IS
2433 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
2434 FROM AR_PAYMENT_SCHEDULES
2435 WHERE CUSTOMER_ID = p_customer_id
2436 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
2437 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
2438
2439 Cursor ar_balance_in_ar_days IS
2440 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
2441 FROM AR_PAYMENT_SCHEDULES
2442 WHERE CUSTOMER_ID = p_customer_id
2443 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
2444 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
2445 AND SYSDATE - TRX_DATE > l_open_ar_days;
2446
2447 Cursor pay_risk IS
2448 SELECT NVL(SUM(CRH.AMOUNT), 0)
2449 FROM AR_CASH_RECEIPT_HISTORY CRH,
2450 AR_CASH_RECEIPTS CR
2451 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
2452 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
2453 AND CRH.CURRENT_RECORD_FLAG = 'Y'
2454 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG,'Y',
2455 'RISK_ELIMINATED','CLEARED')
2456 AND CRH.STATUS <> 'REVERSED'
2457 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
2458 AND CR.PAY_FROM_CUSTOMER = p_customer_id;
2459
2460 Cursor pay_risk_in_ar_days IS
2461 SELECT NVL(SUM(CRH.AMOUNT), 0)
2462 FROM AR_CASH_RECEIPT_HISTORY CRH
2463 , AR_CASH_RECEIPTS CR
2464 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
2465 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
2466 AND CRH.CURRENT_RECORD_FLAG = 'Y'
2467 AND CRH.STATUS <>
2468 DECODE(CRH.FACTOR_FLAG,'Y',
2469 'RISK_ELIMINATED','CLEARED')
2470 AND CRH.STATUS <> 'REVERSED'
2471 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
2472 AND CR.PAY_FROM_CUSTOMER = p_customer_id
2473 AND SYSDATE - CR.RECEIPT_DATE > l_open_ar_days;
2474
2475
2476 /* additional task : modified the following 2 cursors uninvoiced_orders and
2477 orders_on_hold for the following purpose
2478 1. excluded unbooked orders
2479 2. excluded orders authorized by credit card
2480 3. Exclude the Current Order
2481
2482 So far there was a bug in the application - if the current order was
2483 on hold and booked then current order value was not getting included
2484 into the credit exposure. With present logic, we exclude the current order
2485 initially while calculating the uninvoiced order total and orders_on_hold
2486 total and then add it up later. Because irrespective of the fact that
2487 current order is booked/entered/on hold etc, we have to take care of that
2488 amount in Credit exposure.
2489
2490 Two additional conditions has been added for all cursors related
2491 to Include Uninvoiced Orders. We will consider only those orders
2492 which are not invoiced yet, i.e. invoiced qty is zero.
2493 Also we should consider Open Orders Only
2494 The decode statement has been added to improve performance*/
2495
2496 Cursor uninvoiced_orders(p_include_tax VARCHAR2) IS
2497 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
2498 *NVL(unit_selling_price,0)) + decode(p_include_tax,'Y',NVL(tax_value,0), 0))
2499 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2500 WHERE H.SOLD_TO_ORG_ID = p_customer_id
2501 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2502 AND H.HEADER_ID = L.HEADER_ID
2503 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2504 <= l_shipping_horizon
2505 AND NVL(L.INVOICED_QUANTITY,0) = 0
2506 AND L.OPEN_FLAG ='Y'
2507 AND L.LINE_CATEGORY_CODE ='ORDER'
2508 AND H.BOOKED_FLAG = 'Y'
2509 AND H.HEADER_ID <> p_header_rec.header_id
2510 /*
2511 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2512 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
2513 */
2514 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2515 OR (h.payment_type_code = 'CREDIT_CARD'
2516 AND NOT EXISTS
2517 (Select 'valid auth code'
2518 From oe_payments op,
2519 iby_trxn_ext_auths_v ite
2520 Where op.header_id = h.header_id
2521 And op.trxn_extension_id = ite.trxn_extension_id
2522 And authorization_status = 0
2523 And effective_auth_amount > 0)
2524 )
2525 );
2526
2527 /* old code, logic remaining same:
2528 AND (H.CREDIT_CARD_APPROVAL_CODE IS NULL
2529 OR
2530 (H.CREDIT_CARD_APPROVAL_CODE IS NOT NULL
2531 AND H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days < SYSDATE))*/
2532
2533 Cursor /* MOAC_SQL_CHANGE */ orders_on_hold(p_include_tax VARCHAR2) IS
2534 SELECT SUM((NVL(ordered_quantity,0)
2535 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y',NVL(tax_value,0),0))
2536 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2537 WHERE H.SOLD_TO_ORG_ID = p_customer_id
2538 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2539 AND H.HEADER_ID = L.HEADER_ID
2540 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2541 <= l_shipping_horizon
2542 AND NVL(L.INVOICED_QUANTITY,0) = 0
2543 AND L.OPEN_FLAG ='Y'
2544 AND L.LINE_CATEGORY_CODE ='ORDER'
2545 AND H.BOOKED_FLAG = 'Y'
2546 AND H.HEADER_ID <> p_header_rec.header_id
2547 /*
2548 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2549 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
2550 */
2551 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2552 OR (h.payment_type_code = 'CREDIT_CARD'
2553 AND NOT EXISTS
2554 (Select 'valid auth code'
2555 From oe_payments op,
2556 iby_trxn_ext_auths_v ite
2557 Where op.header_id = h.header_id
2558 And op.trxn_extension_id = ite.trxn_extension_id
2559 And authorization_status = 0
2560 And effective_auth_amount > 0)
2561 )
2562 )
2563 AND EXISTS (SELECT 1
2564 FROM OE_ORDER_HOLDS OH
2565 WHERE H.HEADER_ID = OH.HEADER_ID
2566 AND OH.HOLD_RELEASE_ID IS NULL
2567 );
2568 Cursor commitment_total IS
2569 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
2570 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
2571 WHERE H.SOLD_TO_ORG_ID = p_customer_id
2572 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2573 AND H.BOOKED_FLAG = 'Y'
2574 AND H.HEADER_ID = P.HEADER_ID
2575 AND H.HEADER_ID <> p_header_rec.header_id
2576 /*
2577 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2578 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
2579 */
2580 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2581 OR (h.payment_type_code = 'CREDIT_CARD'
2582 AND NOT EXISTS
2583 (Select 'valid auth code'
2584 From oe_payments op,
2585 iby_trxn_ext_auths_v ite
2586 Where op.header_id = h.header_id
2587 And op.trxn_extension_id = ite.trxn_extension_id
2588 And authorization_status = 0
2589 And effective_auth_amount > 0)
2590 )
2591 )
2592 AND L.HEADER_ID = H.HEADER_ID
2593 AND L.LINE_ID = P.LINE_ID
2594 AND NVL(L.INVOICED_QUANTITY,0) = 0
2595 AND L.OPEN_FLAG = 'Y'
2596 AND L.LINE_CATEGORY_CODE = 'ORDER'
2597 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2598 <= l_shipping_horizon;
2599
2600 Cursor on_hold_commitment_total IS
2601 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
2602 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
2603 WHERE H.SOLD_TO_ORG_ID = p_customer_id
2604 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2605 AND H.BOOKED_FLAG = 'Y'
2606 AND H.HEADER_ID = P.HEADER_ID
2607 AND H.HEADER_ID <> p_header_rec.header_id
2608 /*
2609 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2610 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
2611 */
2612 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2613 OR (h.payment_type_code = 'CREDIT_CARD'
2614 AND NOT EXISTS
2615 (Select 'valid auth code'
2616 From oe_payments op,
2617 iby_trxn_ext_auths_v ite
2618 Where op.header_id = h.header_id
2619 And op.trxn_extension_id = ite.trxn_extension_id
2620 And authorization_status = 0
2621 And effective_auth_amount > 0)
2622 )
2623 )
2624 AND L.HEADER_ID = H.HEADER_ID
2625 AND L.LINE_ID = P.LINE_ID
2626 AND NVL(L.INVOICED_QUANTITY,0) = 0
2627 AND L.OPEN_FLAG = 'Y'
2628 AND L.LINE_CATEGORY_CODE = 'ORDER'
2629 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2630 <= l_shipping_horizon
2631 AND EXISTS (SELECT 1
2632 FROM OE_ORDER_HOLDS OH
2633 WHERE H.HEADER_ID = OH.HEADER_ID
2634 AND OH.HOLD_RELEASE_ID IS NULL);
2635
2636 Cursor current_commitment_total IS
2637 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
2638 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
2639 WHERE H.HEADER_ID = p_header_rec.header_id
2640 AND H.HEADER_ID = P.HEADER_ID
2641 AND L.HEADER_ID = H.HEADER_ID
2642 AND L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2643 AND L.LINE_ID = P.LINE_ID
2644 AND NVL(L.INVOICED_QUANTITY,0) = 0
2645 AND L.OPEN_FLAG ='Y'
2646 AND L.LINE_CATEGORY_CODE ='ORDER'
2647 /*
2648 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2649 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
2650 */
2651 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2652 OR (h.payment_type_code = 'CREDIT_CARD'
2653 AND NOT EXISTS
2654 (Select 'valid auth code'
2655 From oe_payments op,
2656 iby_trxn_ext_auths_v ite
2657 Where op.header_id = h.header_id
2658 And op.trxn_extension_id = ite.trxn_extension_id
2659 And authorization_status = 0
2660 And effective_auth_amount > 0)
2661 )
2662 );
2663
2664 Cursor current_order(p_include_tax VARCHAR2) IS
2665 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
2666 *NVL(unit_selling_price,0)) + decode(p_include_tax,'Y', NVL(tax_value,0), 0))
2667 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2668 WHERE H.HEADER_ID = p_header_rec.header_id
2669 AND H.HEADER_ID = L.HEADER_ID
2670 AND L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2671 -- AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2672 -- <= to_date(l_shipping_horizon, 'DD-MON-YY')
2673 AND NVL(L.INVOICED_QUANTITY,0) = 0
2674 AND L.OPEN_FLAG ='Y'
2675 AND L.LINE_CATEGORY_CODE ='ORDER'
2676 /*
2677 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2678 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
2679 */
2680 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2681 OR (h.payment_type_code = 'CREDIT_CARD'
2682 AND NOT EXISTS
2683 (Select 'valid auth code'
2684 From oe_payments op,
2685 iby_trxn_ext_auths_v ite
2686 Where op.header_id = h.header_id
2687 And op.trxn_extension_id = ite.trxn_extension_id
2688 And authorization_status = 0
2689 And effective_auth_amount > 0)
2690 )
2691 );
2692
2693 -- CURSORS FOR SITE - LEVEL EXPOSURE CALCULATIONS
2694 Cursor site_ar_balance IS
2695 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
2696 FROM AR_PAYMENT_SCHEDULES
2697 WHERE CUSTOMER_SITE_USE_ID = p_invoice_to_org_id
2698 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
2699 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
2700
2701 Cursor site_ar_balance_in_ar_days IS
2702 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
2703 FROM AR_PAYMENT_SCHEDULES
2704 WHERE CUSTOMER_SITE_USE_ID = p_invoice_to_org_id
2705 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
2706 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
2707 AND SYSDATE - TRX_DATE > l_open_ar_days;
2708
2709 Cursor site_pay_risk IS
2710 SELECT NVL(SUM(CRH.AMOUNT),0)
2711 FROM AR_CASH_RECEIPT_HISTORY CRH
2712 , AR_CASH_RECEIPTS CR
2713 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
2714 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
2715 AND CRH.CURRENT_RECORD_FLAG = 'Y'
2716 AND CRH.STATUS <>
2717 DECODE(CRH.FACTOR_FLAG,'Y',
2718 'RISK_ELIMINATED','CLEARED')
2719 AND CRH.STATUS <> 'REVERSED'
2720 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
2721 AND CR.PAY_FROM_CUSTOMER = p_customer_id
2722 AND CR.CUSTOMER_SITE_USE_ID = p_invoice_to_org_id;
2723
2724 Cursor site_pay_risk_in_ar_days IS
2725 SELECT NVL(SUM(CRH.AMOUNT),0)
2726 FROM AR_CASH_RECEIPT_HISTORY CRH
2727 , AR_CASH_RECEIPTS CR
2728 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
2729 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
2730 AND CRH.CURRENT_RECORD_FLAG = 'Y'
2731 AND CRH.STATUS <>
2732 DECODE(CRH.FACTOR_FLAG,'Y',
2733 'RISK_ELIMINATED','CLEARED')
2734 AND CRH.STATUS <> 'REVERSED'
2735 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
2736 AND CR.PAY_FROM_CUSTOMER = p_customer_id
2737 AND CR.CUSTOMER_SITE_USE_ID = p_invoice_to_org_id
2738 AND SYSDATE - TRX_DATE > l_open_ar_days;
2739
2740 /* additional task : modified the following 2 SITE cursors
2741 1. excluded unbooked orders
2742 2. excluded orders authorized by credit card
2743 3. Excludes the current order
2744 */
2745
2746 Cursor site_uninvoiced_orders(p_include_tax VARCHAR2) IS
2747 SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0) )
2748 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', nvl(tax_value,0), 0))
2749 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2750 WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2751 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2752 AND H.HEADER_ID = L.HEADER_ID
2753 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2754 <= l_shipping_horizon
2755 AND NVL(L.INVOICED_QUANTITY,0) = 0
2756 AND L.OPEN_FLAG ='Y'
2757 AND L.LINE_CATEGORY_CODE ='ORDER'
2758 AND H.BOOKED_FLAG = 'Y'
2759 AND H.HEADER_ID <> p_header_rec.header_id
2760 /*
2761 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2762 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
2763 */
2764 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2765 OR (h.payment_type_code = 'CREDIT_CARD'
2766 AND NOT EXISTS
2767 (Select 'valid auth code'
2768 From oe_payments op,
2769 iby_trxn_ext_auths_v ite
2770 Where op.header_id = h.header_id
2771 And op.trxn_extension_id = ite.trxn_extension_id
2772 And authorization_status = 0
2773 And effective_auth_amount > 0)
2774 )
2775 );
2776
2777 Cursor site_orders_on_hold(p_include_tax VARCHAR2) IS
2778
2779 SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0))
2780 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', NVL(tax_value,0),0))
2781 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2782 WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2783 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2784 AND H.HEADER_ID = L.HEADER_ID
2785 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2786 <= l_shipping_horizon
2787 AND NVL(L.INVOICED_QUANTITY,0) = 0
2788 AND L.OPEN_FLAG ='Y'
2789 AND L.LINE_CATEGORY_CODE ='ORDER'
2790 AND H.BOOKED_FLAG = 'Y'
2791 AND H.HEADER_ID <> p_header_rec.header_id
2792 /*
2793 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2794 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
2795 */
2796 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2797 OR (h.payment_type_code = 'CREDIT_CARD'
2798 AND NOT EXISTS
2799 (Select 'valid auth code'
2800 From oe_payments op,
2801 iby_trxn_ext_auths_v ite
2802 Where op.header_id = h.header_id
2803 And op.trxn_extension_id = ite.trxn_extension_id
2804 And authorization_status = 0
2805 And effective_auth_amount > 0)
2806 )
2807 )
2808 AND EXISTS (SELECT 1
2809 FROM OE_ORDER_HOLDS OH
2810 WHERE H.HEADER_ID = OH.HEADER_ID
2811 AND OH.HOLD_RELEASE_ID IS NULL
2812 );
2813
2814 Cursor site_commitment_total IS
2815 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
2816 FROM OE_PAYMENTS P, OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2817 WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2818 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2819 AND H.HEADER_ID = P.HEADER_ID
2820 AND L.HEADER_ID = H.HEADER_ID
2821 AND L.LINE_ID = P.LINE_ID
2822 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2823 <= l_shipping_horizon
2824 AND NVL(L.INVOICED_QUANTITY,0) = 0
2825 AND L.OPEN_FLAG = 'Y'
2826 AND L.LINE_CATEGORY_CODE = 'ORDER'
2827 AND H.BOOKED_FLAG = 'Y'
2828 /*
2829 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2830 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
2831 */
2832 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2833 OR (h.payment_type_code = 'CREDIT_CARD'
2834 AND NOT EXISTS
2835 (Select 'valid auth code'
2836 From oe_payments op,
2837 iby_trxn_ext_auths_v ite
2838 Where op.header_id = h.header_id
2839 And op.trxn_extension_id = ite.trxn_extension_id
2840 And authorization_status = 0
2841 And effective_auth_amount > 0)
2842 )
2843 );
2844
2845 Cursor site_on_hold_commitment_total IS
2846 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
2847 FROM OE_PAYMENTS P, OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2848 WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2849 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2850 AND H.HEADER_ID = P.HEADER_ID
2851 AND L.HEADER_ID = H.HEADER_ID
2852 AND L.LINE_ID = P.LINE_ID
2853 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2854 <= l_shipping_horizon
2855 AND NVL(L.INVOICED_QUANTITY,0) = 0
2856 AND L.OPEN_FLAG = 'Y'
2857 AND L.LINE_CATEGORY_CODE = 'ORDER'
2858 AND H.BOOKED_FLAG = 'Y'
2859 AND H.HEADER_ID <> p_header_rec.header_id
2860 /*
2861 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2862 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
2863 */
2864 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2865 OR (h.payment_type_code = 'CREDIT_CARD'
2866 AND NOT EXISTS
2867 (Select 'valid auth code'
2868 From oe_payments op,
2869 iby_trxn_ext_auths_v ite
2870 Where op.header_id = h.header_id
2871 And op.trxn_extension_id = ite.trxn_extension_id
2872 And authorization_status = 0
2873 And effective_auth_amount > 0)
2874 )
2875 )
2876 AND EXISTS (SELECT 1
2877 FROM OE_ORDER_HOLDS OH
2878 WHERE H.HEADER_ID = OH.HEADER_ID
2879 AND OH.HOLD_RELEASE_ID IS NULL);
2880
2881 /* additional task- Current Order - */
2882
2883 Cursor site_current_order(p_include_tax VARCHAR2) IS
2884 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
2885 *NVL(unit_selling_price,0)) + decode(p_include_tax,'Y', NVL(tax_value,0), 0))
2886 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2887 WHERE H.HEADER_ID = p_header_rec.header_id
2888 AND H.HEADER_ID = L.HEADER_ID
2889 AND L.invoice_to_org_id = p_invoice_to_org_id
2890 -- AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2891 -- <= to_date(l_shipping_horizon, 'DD-MON-YY')
2892 AND NVL(L.INVOICED_QUANTITY,0) = 0
2893 AND L.OPEN_FLAG ='Y'
2894 AND L.LINE_CATEGORY_CODE ='ORDER'
2895 /*
2896 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2897 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
2898 */
2899 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2900 OR (h.payment_type_code = 'CREDIT_CARD'
2901 AND NOT EXISTS
2902 (Select 'valid auth code'
2903 From oe_payments op,
2904 iby_trxn_ext_auths_v ite
2905 Where op.header_id = h.header_id
2906 And op.trxn_extension_id = ite.trxn_extension_id
2907 And authorization_status = 0
2908 And effective_auth_amount > 0)
2909 )
2910 );
2911
2912
2913 BEGIN
2914
2915 -- Set the default behaviour to pass credit check exposure
2916 p_return_status := FND_API.G_RET_STS_SUCCESS;
2917 -- Read the Credit rule and determine the inclusions and exclusions
2918 -- for exposure
2919
2920 -- Include Amounts on Receivables if within the AR horizon.
2921 -- Add the OPEN_AR_DAYS
2922 -- to todays date to determine the AR Horizon. Use this to compare the
2923 -- Due date on
2924 -- the Invoice. If the due_date is greater than system date by less than
2925 -- open_ar_days
2926 -- In the R10SC/R11, if the trx_date/invoice_date is earlier than system date
2927 -- by open_ar_days, those
2928 -- orders only are considered. So the check was WHERE SYSDATE - TRX_DATE >
2929 -- OPEN_AR_DAYS
2930 -- Include amounts on Order Backlog if within the shipping horizon.
2931 -- Add the Shipping Horizon
2932 -- to todays date to determin the shipping horizon. Use this to compare
2933 -- the scheduled ship date on the order line.
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
2943 , l_orders_on_hold_flag
2944 , l_include_tax_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);
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));
2961
2962
2963 IF p_credit_level = 'CUSTOMER' THEN
2964 -- Retrieving exposure at CUSTOMER level
2965
2966 IF l_open_ar_balance_flag = 'Y' THEN
2967 -- Find Accounts Receivable Exposure
2968 IF l_open_ar_days IS NULL THEN
2969 OPEN ar_balance;
2970 FETCH ar_balance INTO l_total_from_ar;
2971 IF ar_balance%notfound THEN
2972 l_total_from_ar := 0;
2973 END IF;
2974 CLOSE ar_balance;
2975 ELSE
2976 OPEN ar_balance_in_ar_days;
2977 FETCH ar_balance_in_ar_days INTO l_total_from_ar;
2978 IF ar_balance_in_ar_days%notfound THEN
2979 l_total_from_ar := 0;
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 *
2989 * These payments are in the cash receipts history *
2990 *********************************************************************/
2991
2992 IF l_include_risk_flag = 'Y' THEN
2993
2994 IF l_open_ar_days IS NULL THEN
2995 OPEN pay_risk;
2996 FETCH pay_risk INTO l_payments_at_risk;
2997 IF pay_risk%notfound THEN
2998 l_payments_at_risk := 0;
2999 END IF;
3000 CLOSE pay_risk;
3001 ELSE
3002 OPEN pay_risk_in_ar_days;
3003 FETCH pay_risk_in_ar_days INTO l_payments_at_risk;
3004 IF pay_risk_in_ar_days%notfound THEN
3005 l_payments_at_risk := 0;
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.
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
3021 -- Depending on the include_tax_flag value tax will be included or
3022 -- excluded from credit exposure calculation
3023
3024 IF l_uninvoiced_orders_flag = 'Y' THEN
3025
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
3035 oe_debug_pub.ADD('not found');*/
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));
3045
3046 -- Next check if we should be excluding orders that are already on hold
3047 -- from the calculation of Total exposure
3048
3049 IF l_orders_on_hold_flag = 'N' THEN
3050 -- Find the value of all orders that are the subject of an order hold
3051 -- at either the header or line level.
3052
3053 OPEN orders_on_hold(l_include_tax_flag);
3054 FETCH orders_on_hold INTO l_total_on_hold;
3055 IF orders_on_hold%notfound THEN
3056 l_total_on_hold := 0;
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 ='||
3066 to_char(l_total_exposure));
3067 END IF; -- l_orders_on_hold_flag
3068
3069 -- Check Commitment Total if Commitment Sequencing "On"
3070 IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
3071
3072 OPEN commitment_total;
3073 FETCH commitment_total INTO l_total_commitment;
3074 IF commitment_total%notfound THEN
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
3084 OPEN on_hold_commitment_total;
3085 FETCH on_hold_commitment_total INTO l_on_hold_commitment;
3086 IF on_hold_commitment_total%notfound THEN
3087 l_on_hold_commitment := 0;
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;
3097 IF current_commitment_total%notfound THEN
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);
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 */
3116
3117 OPEN current_order(l_include_tax_flag);
3118 FETCH current_order INTO l_current_order;
3119 IF current_order%notfound THEN
3120 l_current_order := 0;
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
3130 ----------------
3131 -- SITE LEVEL --
3132 ----------------
3133 ELSE -- Retrieving exposure at SITE level
3134
3135 IF l_open_ar_balance_flag = 'Y' THEN
3136
3137 -- Find Accounts Receivable Exposure
3138 IF l_open_ar_days IS NULL THEN
3139 OPEN site_ar_balance;
3140 FETCH site_ar_balance INTO l_total_from_ar;
3141 IF site_ar_balance%notfound THEN
3142 l_total_from_ar := 0;
3143 END IF;
3144 CLOSE site_ar_balance;
3145 ELSE
3146 OPEN site_ar_balance_in_ar_days;
3147 FETCH site_ar_balance_in_ar_days INTO l_total_from_ar;
3148 IF site_ar_balance_in_ar_days%notfound THEN
3149 l_total_from_ar := 0;
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
3159
3160 IF l_include_risk_flag = 'Y' THEN
3161
3162 IF l_open_ar_days IS NULL THEN
3163 OPEN site_pay_risk;
3164 FETCH site_pay_risk INTO l_payments_at_risk;
3165 IF site_pay_risk%notfound THEN
3166 l_payments_at_risk := 0;
3167 END IF;
3168 CLOSE site_pay_risk;
3169 ELSE
3170 OPEN site_pay_risk_in_ar_days;
3171 FETCH site_pay_risk_in_ar_days INTO l_payments_at_risk;
3172 IF site_pay_risk_in_ar_days%notfound THEN
3173 l_payments_at_risk := 0;
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.
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
3189
3190 -- If the include_uninvoiced_orders is set to yes,depending on the
3191 -- value of include_tax_flag, tax valuw will be included in the
3192 -- calculation of total_exposure.
3193
3194 IF l_uninvoiced_orders_flag = 'Y' THEN
3195
3196 OPEN site_uninvoiced_orders(l_include_tax_flag);
3197 FETCH site_uninvoiced_orders INTO l_total_on_order;
3198 IF site_uninvoiced_orders%notfound THEN
3199 l_total_on_order := 0;
3200 END IF;
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));
3210
3211 -- Next check if we should be excluding orders that are already on hold
3212 -- from the calculation of Total exposure
3213
3214 IF l_orders_on_hold_flag = 'N' THEN
3215 -- Find the value of all orders that are the subject of an order hold
3216 -- at either the header or line level.
3217
3218 OPEN site_orders_on_hold(l_include_tax_flag);
3219 FETCH site_orders_on_hold INTO l_total_on_hold;
3220 IF site_orders_on_hold%notfound THEN
3221 l_total_on_hold := 0;
3222 END IF;
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));
3232
3233 END IF; -- check orders on hold
3234
3235 -- Check Commitment Total if Commitment Sequencing "On"
3236 IF OE_Commitment_PVT.do_Commitment_Sequencing THEN
3237
3238 OPEN site_commitment_total;
3239 FETCH site_commitment_total INTO l_total_commitment;
3240 IF site_commitment_total%notfound THEN
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
3250 OPEN site_on_hold_commitment_total;
3251 FETCH site_on_hold_commitment_total INTO l_on_hold_commitment;
3252 IF site_on_hold_commitment_total%notfound THEN
3253 l_on_hold_commitment := 0;
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
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));
3269
3270 END IF; -- Commitment Sequencing
3271
3272 OPEN site_current_order(l_include_tax_flag);
3273 FETCH site_current_order INTO l_current_order;
3274 IF site_current_order%notfound THEN
3275 l_current_order := 0;
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
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
3292 WHEN others THEN
3293 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3294 OE_MSG_PUB.Add_Exc_Msg
3295 ( G_PKG_NAME
3296 , 'Check_Exposure'
3297 );
3298 END IF;
3299 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3300 RAISE ;
3301
3302 END Check_Exposure_Line;
3303
3304 END OE_Credit_PUB;
[Home] [Help]