1 PACKAGE BODY OE_Credit_PUB AS
2 /* $Header: OEXPCRCB.pls 120.4.12010000.3 2008/11/14 11:47:57 msundara 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 SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
446 INTO l_credit_check_rule_id
447 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
448 WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
449 AND ENTRY_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
450 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
451 --7194250
452
453 OE_Verify_Payment_PUB.G_credit_check_rule := 'Ordering'; --ER#7479609
454
455 -- If not Use the Shipping Rule for all other calling Actions
456 ELSE
457
458 oe_debug_pub.ADD('Selecting the shipping cchk rule');
459 /*7194250
460 SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
461 INTO l_credit_check_rule_id
462 FROM OE_ORDER_TYPES_V
463 WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
464 7194250*/
465 --7194250
466 SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
467 INTO l_credit_check_rule_id
468 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
469 WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
470 AND SHIPPING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
471 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
472 --7194250
473
474 OE_Verify_Payment_PUB.G_credit_check_rule := 'Shipping'; --ER#7479609
475
476 END IF;
477
478 -- If no credit rule was found for the calling action the Order
479 -- is not subject to credit check
480
481 oe_debug_pub.ADD('check order after selecting credit rule : '|| l_check_order);
482 IF l_credit_check_rule_id <= 0 THEN
483 oe_debug_pub.ADD('no cchk rule found');
484 l_check_order := 'N';
485 END IF;
486
487 -- If the Order type is subject to credit check we should check the
488 -- bill to site and customer to see if they are subject to credit check.
489
490 IF l_check_order = 'Y' THEN
491
492 -- The Order type is Subject to Credit Check.
493 -- Procedure to determine if the Order Bill to is subject to Credit Check
494 -- Get the flags that control if credit check should be performed
495 -- from the Credit Profile. Get the Credit Limit Amounts from the
496 -- Credit Profile Amounts for the Customers Bill to Address.
497
498 /* Find Customer Id of the Header Invoice To Org */
499 SELECT CUSTOMER_ID
500 INTO l_invoice_to_cust_id
501 FROM OE_INVOICE_TO_ORGS_V
502 WHERE ORGANIZATION_ID = p_header_rec.invoice_to_org_id;
503
504 oe_debug_pub.ADD('Invoice To Customer Id: '||to_char(l_invoice_to_cust_id));
505
506 BEGIN
507
508 SELECT NVL(CP.CREDIT_CHECKING, 'N')
509 , (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
510 , NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
511 , NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
512 INTO l_check_order
513 , l_credit_limit_test
514 , p_overall_credit_limit
515 , p_trx_credit_limit
516 FROM HZ_CUSTOMER_PROFILES CP
517 , HZ_CUST_PROFILE_AMTS CPA
518 WHERE CP.CUST_ACCOUNT_ID = l_invoice_to_cust_id
519 AND CP.SITE_USE_ID = p_header_rec.invoice_to_org_id
520 AND CPA.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
521 AND CPA.SITE_USE_ID = CP.SITE_USE_ID
522 AND CPA.CUST_ACCOUNT_PROFILE_ID = CP.CUST_ACCOUNT_PROFILE_ID
523 AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code;
524
525 -- If we find a Credit Profile at this level the customer has credit limits
526 -- at the site level.
527
528
529 l_credit_check_lvl_out := 'SITE';
530
531 EXCEPTION
532
533 WHEN NO_DATA_FOUND THEN
534 -- If we do not find a Credit Profile we will assume that the credit limits are set
535 -- at the customer level.
536
537 oe_debug_pub.ADD('Customer level credit check');
538 l_credit_check_lvl_out := 'CUSTOMER';
539
540 WHEN OTHERS THEN
541
542 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
543 OE_MSG_PUB.Add_Exc_Msg
544 ( G_PKG_NAME
545 , 'Check Order:Check Customer'
546 );
547 END IF;
548 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
550
551 END;
552
553 -- If no credit limit has been set for either the total Order Amount
554 -- or the Total Exposure, get the credit profiles from the customer level
555 IF l_credit_limit_test < 0 and l_credit_check_lvl_out ='SITE' THEN
556 l_credit_check_lvl_out := 'CUSTOMER';
557 END IF;
558
559 END IF; -- Check if the Order type is subject to credit check for site or customer
560
561 oe_debug_pub.ADD('check order after selecting site/customer level : ' || l_check_order);
562 oe_debug_pub.ADD('level for credit check: '|| l_credit_check_lvl_out);
563 IF l_check_order = 'Y' AND l_credit_check_lvl_out = 'CUSTOMER' THEN
564
565 -- If both the Order type and the Bill to are subject to credit check
566 -- and no credit profile was found at the bill to site go on to
567 -- check if the customer is subject to credit check.
568 -- Procedure to Determine if the Customer is subject to Credit Check
569
570 BEGIN
571
572 SELECT NVL(CP.CREDIT_CHECKING,'N')
573 , (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
574 , NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
575 , NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
576 INTO l_check_order
577 , l_credit_limit_test
578 , p_overall_credit_limit
579 , p_trx_credit_limit
580 FROM HZ_CUSTOMER_PROFILES CP
581 , HZ_CUST_PROFILE_AMTS CPA
582 WHERE CP.CUST_ACCOUNT_ID = l_invoice_to_cust_id
583 AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
584 AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code
585 AND CP.SITE_USE_ID IS NULL;
586
587 oe_debug_pub.ADD('limit test:'|| l_credit_limit_test );
588
589 EXCEPTION
590
591 WHEN NO_DATA_FOUND THEN
592 -- If we don't find a credit profile at the customer level, no
593 -- credit checking needed.
594 l_check_order := 'N';
595
596 WHEN OTHERS THEN
597 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
598 OE_MSG_PUB.Add_Exc_Msg
599 ( G_PKG_NAME
600 , 'Check Order:Check Customer'
601 );
602 END IF;
603 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
604 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
605 END;
606
607 -- If no credit limit has been set for either the total Order Amount
608 -- or the Total Exposure, do not credit check.
609 IF l_credit_limit_test < 0 THEN
610 l_check_order := 'N';
611 END IF;
612
613 oe_debug_pub.ADD('check order after determining credit limits :'
614 || l_check_order);
615 END IF; -- The Order type Subject to Credit Check No credit profile at bill to.
616
617 IF l_check_order = 'Y' THEN
618
619 -- Order Type, Bill to or Customer are subject to Credit Check
620 -- If the Order Type, Bill to Site and Customer are subject to credit check,
621 -- go on to check if the payment term is subject to credit check.
622 -- Procedure to Determine if Payment Term is subject to Credit Check
623 -- Check if any line on the order has a payment term that requires credit checking.
624 -- If at least one order line has a payment term that requires credit checking the
625 -- order is subject to credit check.
626
627 BEGIN
628
629 SELECT COUNT(*)
630 INTO l_credit_check_term
631 FROM OE_ORDER_LINES L, OE_RA_TERMS_V T
632 WHERE L.HEADER_ID = p_header_rec.header_id
633 AND T.TERM_ID = L.PAYMENT_TERM_ID
634 AND NVL(T.CREDIT_CHECK_FLAG, 'Y') = 'Y';
635
636 -- If no line exists with a payment term that is subject to credit check
637 -- we should exempt the order from credit check.
638
639 IF l_credit_check_term = 0 THEN
640 l_check_order := 'N';
641 END IF;
642
643 EXCEPTION
644
645 WHEN OTHERS THEN
646 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
647 OE_MSG_PUB.Add_Exc_Msg
648 ( G_PKG_NAME
649 , 'Check_Order: Check Terms'
650 );
651 END IF;
652 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
654
655 END;
656
657 END IF; -- Order Type, Bill to or Customer are subject to Credit Check
658
659 p_credit_check_lvl_out := l_credit_check_lvl_out;
660 p_credit_rule_out := l_credit_check_rule_id;
661 p_check_order_out := l_check_order;
662
663 oe_debug_pub.ADD('check order after checking payment term :'|| l_check_order);
664 EXCEPTION
665
666 WHEN OTHERS THEN
667 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
668 THEN
669 OE_MSG_PUB.Add_Exc_Msg
670 ( G_PKG_NAME
671 , 'Check_Order'
672 );
673 END IF;
674 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
676
677 END Check_Order;
678
679
680
681 PROCEDURE Check_Exposure
682 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
683 , p_credit_check_rule_id IN NUMBER
684 , p_credit_level IN VARCHAR2
685 , p_total_exposure OUT NOCOPY /* file.sql.39 change */ NUMBER
686 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
687 )
688 IS
689 l_open_ar_balance_flag Varchar2(1) := 'Y';
690 l_open_ar_days NUMBER;
691 l_uninvoiced_orders_flag Varchar2(1) := 'Y';
692 l_orders_on_hold_flag Varchar2(1) := 'Y';
693
694 /* additional task -added the following variable */
695 l_include_tax_flag Varchar2(1) := 'Y';
696
697 l_shipping_horizon Date := TO_DATE('31/12/4712', 'DD/MM/YYYY');
698 l_include_risk_flag Varchar2(1) := 'Y';
699 l_total_from_ar NUMBER := 0;
700 l_total_on_order NUMBER := 0;
701 l_total_on_hold NUMBER := 0;
702 l_total_exposure NUMBER := 0;
703 l_payments_at_risk NUMBER := 0;
704 l_current_order NUMBER := 0;
705
706 -- ZB
707 l_maximum_days_past_due NUMBER := 0;
708
709 --additional task : added the following variable
710 l_est_valid_days NUMBER := 0 ;
711
712 l_total_commitment NUMBER := 0;
713 l_current_commitment NUMBER := 0;
714 l_on_hold_commitment NUMBER := 0;
715
716 l_invoice_to_cust_id NUMBER;
717
718 /* additional task - for adding the Include Tax option, the cursor needs to be modified */
719
720 Cursor credit_check_rule IS
721 SELECT OPEN_AR_BALANCE_FLAG
722 , OPEN_AR_DAYS
723 , UNINVOICED_ORDERS_FLAG
724 , ORDERS_ON_HOLD_FLAG
725 , INCLUDE_TAX_FLAG
726 , DECODE(SHIPPING_INTERVAL, NULL,
727 TO_DATE('31/12/4712', 'DD/MM/YYYY'),
728 SHIPPING_INTERVAL+SYSDATE)
729 , INCLUDE_PAYMENTS_AT_RISK_FLAG
730 FROM OE_CREDIT_CHECK_RULES
731 WHERE CREDIT_CHECK_RULE_ID = p_credit_check_rule_id;
732
733 Cursor ar_balance IS
734 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
735 FROM AR_PAYMENT_SCHEDULES
736 WHERE CUSTOMER_ID = l_invoice_to_cust_id
737 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
738 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
739
740 Cursor ar_balance_in_ar_days IS
741 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
742 FROM AR_PAYMENT_SCHEDULES
743 WHERE CUSTOMER_ID = l_invoice_to_cust_id
744 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
745 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
746 AND SYSDATE - TRX_DATE > l_open_ar_days;
747
748 Cursor pay_risk IS
749 SELECT NVL(SUM(CRH.AMOUNT), 0)
750 FROM AR_CASH_RECEIPT_HISTORY CRH
751 , AR_CASH_RECEIPTS CR
752 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
753 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
754 AND CRH.CURRENT_RECORD_FLAG = 'Y'
755 AND CRH.STATUS <>
756 DECODE(CRH.FACTOR_FLAG,'Y',
757 'RISK_ELIMINATED','CLEARED')
758 AND CRH.STATUS <> 'REVERSED'
759 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
760 AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id;
761
762 Cursor pay_risk_in_ar_days IS
763 SELECT NVL(SUM(CRH.AMOUNT), 0)
764 FROM AR_CASH_RECEIPT_HISTORY CRH
765 , AR_CASH_RECEIPTS CR
766 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
767 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
768 AND CRH.CURRENT_RECORD_FLAG = 'Y'
769 AND CRH.STATUS <>
770 DECODE(CRH.FACTOR_FLAG,'Y',
771 'RISK_ELIMINATED','CLEARED')
772 AND CRH.STATUS <> 'REVERSED'
773 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
774 AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id
775 AND SYSDATE - CR.RECEIPT_DATE > l_open_ar_days;
776
777
778 /* additional task : modified the following 2 cursors uninvoiced_orders and
779 orders_on_hold for the following purpose
780 1. excluded unbooked orders
781 2. excluded orders authorized by credit card
782 3. Exclude the Current Order
783
784 So far there was a bug in the application - if the current order was
785 on hold and booked then current order value was not getting included
786 into the credit exposure. With present logic, we exclude the current order
787 initially while calculating the uninvoiced order total and orders_on_hold
788 total and then add it up later. Because irrespective of the fact that
789 current order is booked/entered/on hold etc, we have to take care of that
790 amount in Credit exposure.
791
792 Two additional conditions has been added for all cursors related
793 to Include Uninvoiced Orders. We will consider only those orders
794 which are not invoiced yet, i.e. invoiced qty is zero.
795 Also we should consider Open Orders Only
796 The decode statement has been added to improve performance*/
797
798 Cursor uninvoiced_orders(p_include_tax VARCHAR2) IS
799 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
800 *NVL(unit_selling_price,0)) + decode(p_include_tax,'Y',NVL(tax_value,0), 0))
801 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H --ksurendr SQL# 16485169 Removed OE_PAYMENTS
802 WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id --table from outer query as it is not used.
803 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
804 AND H.OPEN_FLAG = 'Y'
805 AND H.HEADER_ID = L.HEADER_ID
806 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
807 <= l_shipping_horizon
808 AND NVL(L.INVOICED_QUANTITY,0) = 0
809 AND L.OPEN_FLAG ='Y'
810 AND L.LINE_CATEGORY_CODE ='ORDER'
811 AND H.BOOKED_FLAG = 'Y'
812 AND H.HEADER_ID <> p_header_rec.header_id
813 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
814 OR (h.payment_type_code = 'CREDIT_CARD'
815 AND NOT EXISTS
816 (Select 'valid auth code'
817 From oe_payments op,
818 iby_trxn_ext_auths_v ite
819 Where op.header_id = h.header_id
820 And op.trxn_extension_id = ite.trxn_extension_id
821 And authorization_status = 0
822 And effective_auth_amount > 0)
823 )
824 );
825
826 /* changed for R12 cc encryption
827 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
828 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
829 */
830
831 /* old code, logic remaining same:
832 AND (H.CREDIT_CARD_APPROVAL_CODE IS NULL
833 OR
834 (H.CREDIT_CARD_APPROVAL_CODE IS NOT NULL
835 AND H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days < SYSDATE))*/
836
837 Cursor orders_on_hold(p_include_tax VARCHAR2) IS
838 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
839 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y',NVL(tax_value,0),0))
840 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
841 WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id
842 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
843 AND H.OPEN_FLAG = 'Y'
844 AND H.HEADER_ID = L.HEADER_ID
845 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
846 <= l_shipping_horizon
847 AND NVL(L.INVOICED_QUANTITY,0) = 0
848 AND L.OPEN_FLAG ='Y'
849 AND L.LINE_CATEGORY_CODE ='ORDER'
850 AND H.BOOKED_FLAG = 'Y'
851 AND H.HEADER_ID <> p_header_rec.header_id
852 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
853 OR (h.payment_type_code = 'CREDIT_CARD'
854 AND NOT EXISTS
855 (Select 'valid auth code'
856 From oe_payments op,
857 iby_trxn_ext_auths_v ite
858 Where op.header_id = h.header_id
859 And op.trxn_extension_id = ite.trxn_extension_id
860 And authorization_status = 0
861 And effective_auth_amount > 0)
862 )
863 )
864 /*
865 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
866 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
867 */
868 AND EXISTS (SELECT 1
869 FROM OE_ORDER_HOLDS OH
870 WHERE H.HEADER_ID = OH.HEADER_ID
871 AND OH.HOLD_RELEASE_ID IS NULL
872 );
873
874 Cursor commitment_total IS
875 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
876 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
877 WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id
878 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
879 AND H.OPEN_FLAG = 'Y'
880 AND H.BOOKED_FLAG = 'Y'
881 AND H.HEADER_ID = P.HEADER_ID
882 AND H.HEADER_ID <> p_header_rec.header_id
883 /*
884 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
885 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
886 */
887 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
888 OR (h.payment_type_code = 'CREDIT_CARD'
889 AND NOT EXISTS
890 (Select 'valid auth code'
891 From oe_payments op,
892 iby_trxn_ext_auths_v ite
893 Where op.header_id = h.header_id
894 And op.trxn_extension_id = ite.trxn_extension_id
895 And authorization_status = 0
896 And effective_auth_amount > 0)
897 )
898 )
899 AND L.HEADER_ID = H.HEADER_ID
900 AND L.LINE_ID = P.LINE_ID
901 AND NVL(L.INVOICED_QUANTITY,0) = 0
902 AND L.OPEN_FLAG = 'Y'
903 AND L.LINE_CATEGORY_CODE = 'ORDER'
904 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
905 <= l_shipping_horizon;
906
907 Cursor on_hold_commitment_total IS
908 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
909 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
910 WHERE H.SOLD_TO_ORG_ID = l_invoice_to_cust_id
911 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
912 AND H.OPEN_FLAG = 'Y'
913 AND H.BOOKED_FLAG = 'Y'
914 AND H.HEADER_ID = P.HEADER_ID
915 AND H.HEADER_ID <> p_header_rec.header_id
916 /*
917 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
918 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
919 */
920 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
921 OR (h.payment_type_code = 'CREDIT_CARD'
922 AND NOT EXISTS
923 (Select 'valid auth code'
924 From oe_payments op,
925 iby_trxn_ext_auths_v ite
926 Where op.header_id = h.header_id
927 And op.trxn_extension_id = ite.trxn_extension_id
928 And authorization_status = 0
929 And effective_auth_amount > 0)
930 )
931 )
932 AND L.HEADER_ID = H.HEADER_ID
933 AND L.LINE_ID = P.LINE_ID
934 AND NVL(L.INVOICED_QUANTITY,0) = 0
935 AND L.OPEN_FLAG = 'Y'
936 AND L.LINE_CATEGORY_CODE = 'ORDER'
937 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
938 <= l_shipping_horizon
939 AND EXISTS (SELECT 'Hold Exists'
940 FROM OE_ORDER_HOLDS OH
941 WHERE H.HEADER_ID = OH.HEADER_ID
942 AND OH.HOLD_RELEASE_ID IS NULL );
943
944 Cursor current_commitment_total IS
945 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
946 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
947 WHERE H.HEADER_ID = p_header_rec.header_id
948 AND H.HEADER_ID = P.HEADER_ID
949 /*
950 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
951 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
952 */
953 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
954 OR (h.payment_type_code = 'CREDIT_CARD'
955 AND NOT EXISTS
956 (Select 'valid auth code'
957 From oe_payments op,
958 iby_trxn_ext_auths_v ite
959 Where op.header_id = h.header_id
960 And op.trxn_extension_id = ite.trxn_extension_id
961 And authorization_status = 0
962 And effective_auth_amount > 0)
963 )
964 )
965 AND L.HEADER_ID = H.HEADER_ID
966 AND L.LINE_ID = P.LINE_ID
967 AND NVL(L.INVOICED_QUANTITY,0) = 0
968 AND L.OPEN_FLAG = 'Y'
969 AND L.LINE_CATEGORY_CODE = 'ORDER';
970
971 -- CURSORS FOR SITE - LEVEL EXPOSURE CALCULATIONS
972 Cursor site_ar_balance IS
973 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
974 FROM AR_PAYMENT_SCHEDULES
975 WHERE CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id
976 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
977 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
978
979 Cursor site_ar_balance_in_ar_days IS
980 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
981 FROM AR_PAYMENT_SCHEDULES
982 WHERE CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id
983 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
984 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
985 AND SYSDATE - TRX_DATE > l_open_ar_days;
986
987 Cursor site_pay_risk IS
988 SELECT NVL(SUM(CRH.AMOUNT),0)
989 FROM AR_CASH_RECEIPT_HISTORY CRH
990 , AR_CASH_RECEIPTS CR
991 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
992 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
993 AND CRH.CURRENT_RECORD_FLAG = 'Y'
994 AND CRH.STATUS <>
995 DECODE(CRH.FACTOR_FLAG,'Y',
996 'RISK_ELIMINATED','CLEARED')
997 AND CRH.STATUS <> 'REVERSED'
998 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
999 AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id
1000 AND CR.CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id;
1001
1002 Cursor site_pay_risk_in_ar_days IS
1003 SELECT NVL(SUM(CRH.AMOUNT),0)
1004 FROM AR_CASH_RECEIPT_HISTORY CRH
1005 , AR_CASH_RECEIPTS CR
1006 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
1007 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
1008 AND CRH.CURRENT_RECORD_FLAG = 'Y'
1009 AND CRH.STATUS <>
1010 DECODE(CRH.FACTOR_FLAG,'Y',
1011 'RISK_ELIMINATED','CLEARED')
1012 AND CRH.STATUS <> 'REVERSED'
1013 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
1014 AND CR.PAY_FROM_CUSTOMER = l_invoice_to_cust_id
1015 AND CR.CUSTOMER_SITE_USE_ID = p_header_rec.invoice_to_org_id
1016 AND SYSDATE - TRX_DATE > l_open_ar_days;
1017
1018 /* additional task : modified the following 2 SITE cursors
1019 1. excluded unbooked orders
1020 2. excluded orders authorized by credit card
1021 3. Excludes the current order
1022 */
1023
1024 Cursor site_uninvoiced_orders(p_include_tax VARCHAR2) IS
1025 SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0) )
1026 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', nvl(tax_value,0), 0))
1027 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
1028 WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
1029 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
1030 AND H.OPEN_FLAG = 'Y'
1031 AND H.HEADER_ID = L.HEADER_ID
1032 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
1033 <= l_shipping_horizon
1034 AND NVL(L.INVOICED_QUANTITY,0) = 0
1035 AND L.OPEN_FLAG ='Y'
1036 AND L.LINE_CATEGORY_CODE ='ORDER'
1037 AND H.BOOKED_FLAG = 'Y'
1038 AND H.HEADER_ID <> p_header_rec.header_id
1039 /*
1040 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
1041 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
1042 */
1043 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
1044 OR (h.payment_type_code = 'CREDIT_CARD'
1045 AND NOT EXISTS
1046 (Select 'valid auth code'
1047 From oe_payments op,
1048 iby_trxn_ext_auths_v ite
1049 Where op.header_id = h.header_id
1050 And op.trxn_extension_id = ite.trxn_extension_id
1051 And authorization_status = 0
1052 And effective_auth_amount > 0)
1053 )
1054 );
1055
1056 Cursor site_orders_on_hold(p_include_tax VARCHAR2) IS
1057
1058 SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0))
1059 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', NVL(tax_value,0),0))
1060 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
1061 WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
1062 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
1063 AND H.OPEN_FLAG = 'Y'
1064 AND H.HEADER_ID = L.HEADER_ID
1065 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
1066 <= l_shipping_horizon
1067 AND NVL(L.INVOICED_QUANTITY,0) = 0
1068 AND L.OPEN_FLAG ='Y'
1069 AND L.LINE_CATEGORY_CODE ='ORDER'
1070 AND H.BOOKED_FLAG = 'Y'
1071 AND H.HEADER_ID <> p_header_rec.header_id
1072 /*
1073 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
1074 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
1075 */
1076 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
1077 OR (h.payment_type_code = 'CREDIT_CARD'
1078 AND NOT EXISTS
1079 (Select 'valid auth code'
1080 From oe_payments op,
1081 iby_trxn_ext_auths_v ite
1082 Where op.header_id = h.header_id
1083 And op.trxn_extension_id = ite.trxn_extension_id
1084 And authorization_status = 0
1085 And effective_auth_amount > 0)
1086 )
1087 )
1088 AND EXISTS (SELECT 1
1089 FROM OE_ORDER_HOLDS OH
1090 WHERE H.HEADER_ID = OH.HEADER_ID
1091 AND OH.HOLD_RELEASE_ID IS NULL
1092 );
1093
1094 Cursor site_commitment_total IS
1095 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
1096 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
1097 WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
1098 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
1099 AND H.OPEN_FLAG = 'Y'
1100 AND H.BOOKED_FLAG = 'Y'
1101 AND H.HEADER_ID = P.HEADER_ID
1102 /*
1103 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
1104 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
1105 */
1106 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
1107 OR (h.payment_type_code = 'CREDIT_CARD'
1108 AND NOT EXISTS
1109 (Select 'valid auth code'
1110 From oe_payments op,
1111 iby_trxn_ext_auths_v ite
1112 Where op.header_id = h.header_id
1113 And op.trxn_extension_id = ite.trxn_extension_id
1114 And authorization_status = 0
1115 And effective_auth_amount > 0)
1116 )
1117 )
1118 AND L.HEADER_ID = H.HEADER_ID
1119 AND L.LINE_ID = P.LINE_ID
1120 AND NVL(L.INVOICED_QUANTITY,0) = 0
1121 AND L.OPEN_FLAG = 'Y'
1122 AND L.LINE_CATEGORY_CODE = 'ORDER'
1123 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
1124 <= l_shipping_horizon;
1125
1126 Cursor site_on_hold_commitment_total IS
1127 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
1128 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
1129 WHERE H.INVOICE_TO_ORG_ID = p_header_rec.invoice_to_org_id
1130 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
1131 AND H.OPEN_FLAG = 'Y'
1132 AND H.BOOKED_FLAG = 'Y'
1133 AND H.HEADER_ID = P.HEADER_ID
1134 /*
1135 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
1136 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
1137 */
1138 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
1139 OR (h.payment_type_code = 'CREDIT_CARD'
1140 AND NOT EXISTS
1141 (Select 'valid auth code'
1142 From oe_payments op,
1143 iby_trxn_ext_auths_v ite
1144 Where op.header_id = h.header_id
1145 And op.trxn_extension_id = ite.trxn_extension_id
1146 And authorization_status = 0
1147 And effective_auth_amount > 0)
1148 )
1149 )
1150 AND L.HEADER_ID = H.HEADER_ID
1151 AND L.LINE_ID = P.LINE_ID
1152 AND NVL(L.INVOICED_QUANTITY,0) = 0
1153 AND L.OPEN_FLAG = 'Y'
1154 AND L.LINE_CATEGORY_CODE = 'ORDER'
1155 AND H.HEADER_ID <> p_header_rec.header_id
1156 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
1157 <= l_shipping_horizon
1158 AND EXISTS (SELECT 'Hold Exists'
1159 FROM OE_ORDER_HOLDS OH
1160 WHERE H.HEADER_ID = OH.HEADER_ID
1161 AND OH.HOLD_RELEASE_ID IS NULL );
1162
1163 /* additional task- Current Order - */
1164
1165 -- modified cursor for bug 1655720, to include lines falling outside of
1166 -- shipping horizon for credit checking.
1167 Cursor current_order(p_include_tax VARCHAR2) IS
1168 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
1169 *NVL(unit_selling_price,0)) + decode(p_include_tax,'Y', NVL(tax_value,0), 0))
1170 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
1171 WHERE H.HEADER_ID = p_header_rec.header_id
1172 AND H.HEADER_ID = L.HEADER_ID
1173 -- AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
1174 -- <= to_date(l_shipping_horizon, 'DD-MON-YY')
1175 AND NVL(L.INVOICED_QUANTITY,0) = 0
1176 AND L.OPEN_FLAG ='Y'
1177 AND L.LINE_CATEGORY_CODE ='ORDER'
1178 /*
1179 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
1180 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
1181 */
1182 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
1183 OR (h.payment_type_code = 'CREDIT_CARD'
1184 AND NOT EXISTS
1185 (Select 'valid auth code'
1186 From oe_payments op,
1187 iby_trxn_ext_auths_v ite
1188 Where op.header_id = h.header_id
1189 And op.trxn_extension_id = ite.trxn_extension_id
1190 And authorization_status = 0
1191 And effective_auth_amount > 0)
1192 )
1193 );
1194
1195
1196 BEGIN
1197
1198 -- Set the default behaviour to pass credit check exposure
1199 p_return_status := FND_API.G_RET_STS_SUCCESS;
1200 -- Read the Credit rule and determine the inclusions and exclusions for exposure
1201
1202 -- Include Amounts on Receivables if within the AR horizon. Add the OPEN_AR_DAYS
1203 -- to todays date to determine the AR Horizon. Use this to compare the Due date on
1204 -- the Invoice. If the due_date is greater than system date by less than open_ar_days
1205 -- In the R10SC/R11, if the trx_date/invoice_date is earlier than system date
1206 -- by open_ar_days, those
1207 -- orders only are considered. So the check was WHERE SYSDATE - TRX_DATE > OPEN_AR_DAYS
1208
1209 -- Include amounts on Order Backlog if within the shipping horizon. Add the Shipping Horizon
1210 -- to todays date to determin the shipping horizon. Use this to compare the scheduled ship date on
1211 -- the order line.
1212 -- As in R10SC/R11, if the schedule_date (or when this field is null, request_date)
1213 -- is within the no. of shipping_interval days of the current date, include only
1214 -- those amounts for calculating uninvoiced orders total.
1215
1216 oe_debug_pub.ADD('In Check Exposure');
1217 OPEN credit_check_rule;
1218 /* additional task */
1219 FETCH credit_check_rule INTO l_open_ar_balance_flag
1220 , l_open_ar_days
1221 , l_uninvoiced_orders_flag
1222 , l_orders_on_hold_flag
1223 , l_include_tax_flag
1224 , l_shipping_horizon
1225 , l_include_risk_flag;
1226 CLOSE credit_check_rule;
1227
1228 oe_debug_pub.ADD('Credit Check Rule is as follows :');
1229 oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
1230 oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
1231 oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
1232 oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
1233 oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
1234 oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
1235 oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
1236
1237 /* additional task */
1238 l_est_valid_days := to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0') ) ;
1239 oe_debug_pub.ADD('Estimated Valid Days ='||to_char(l_est_valid_days));
1240
1241 -- Move the following SQL out of if clause for customer credit level
1242 -- because even for site credit level, we need to pass pay_from_customer
1243 /* Find Customer Id of the Header Invoice To Org */
1244 SELECT CUSTOMER_ID
1245 INTO l_invoice_to_cust_id
1246 FROM OE_INVOICE_TO_ORGS_V
1247 WHERE ORGANIZATION_ID = p_header_rec.invoice_to_org_id;
1248
1249 oe_debug_pub.ADD('Invoice To Customer Id: '||to_char(l_invoice_to_cust_id));
1250
1251 IF p_credit_level = 'CUSTOMER' THEN -- Retrieving exposure at CUSTOMER level
1252
1253
1254 IF l_open_ar_balance_flag = 'Y' THEN
1255
1256 -- Find Accounts Receivable Exposure
1257
1258 IF l_open_ar_days IS NULL THEN
1259
1260 OPEN ar_balance;
1261 FETCH ar_balance INTO l_total_from_ar;
1262 IF ar_balance%notfound THEN
1263 l_total_from_ar := 0;
1264 END IF;
1265 CLOSE ar_balance;
1266
1267 ELSE
1268
1269 OPEN ar_balance_in_ar_days;
1270 FETCH ar_balance_in_ar_days INTO l_total_from_ar;
1271 IF ar_balance_in_ar_days%notfound THEN
1272 l_total_from_ar := 0;
1273 END IF;
1274 CLOSE ar_balance_in_ar_days;
1275
1276 END IF; -- If open_ar_days is null
1277
1278 oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
1279
1280 /* If the include payments at risk flag is set to yes
1281 Update the exposure by payments that are not thought to be collectable
1282 These payments are in the cash receipts history
1283 */
1284
1285
1286 IF l_include_risk_flag = 'Y' THEN
1287
1288 IF l_open_ar_days IS NULL THEN
1289
1290 OPEN pay_risk;
1291 FETCH pay_risk INTO l_payments_at_risk;
1292 IF pay_risk%notfound THEN
1293 l_payments_at_risk := 0;
1294 END IF;
1295 CLOSE pay_risk;
1296
1297 ELSE
1298
1299 OPEN pay_risk_in_ar_days;
1300 FETCH pay_risk_in_ar_days INTO l_payments_at_risk;
1301 IF pay_risk_in_ar_days%notfound THEN
1302 l_payments_at_risk := 0;
1303 END IF;
1304 CLOSE pay_risk_in_ar_days;
1305
1306 END IF; -- If open_ar_days is null
1307
1308 oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
1309
1310 END IF; -- Include Payments at Risk
1311
1312
1313 -- Now update the total exposure value.
1314
1315 l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
1316 oe_debug_pub.ADD(' Accounts Receivables Exposure ='||to_char(l_total_exposure));
1317
1318 END IF; -- checking accounts receivables exposure
1319
1320
1321
1322 /* additional task - depending on the include_tax_flag value tax will be included or
1323 excluded from credit exposure calculation */
1324
1325 IF l_uninvoiced_orders_flag = 'Y' THEN
1326
1327 /* additional task */
1328
1329 BEGIN
1330 OPEN uninvoiced_orders(l_include_tax_flag);
1331 FETCH uninvoiced_orders INTO l_total_on_order;
1332 IF uninvoiced_orders%notfound THEN
1333 oe_debug_pub.ADD('not found any uninvoiced orders');
1334 l_total_on_order := 0;
1335 END IF;
1336 CLOSE uninvoiced_orders;
1337
1338 /* WHEN others THEN
1339 oe_debug_pub.ADD('not found');*/
1340 END;
1341
1342
1343 -- Now update the total exposure value to include the Order Backlog Value
1344
1345 oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
1346 l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
1347
1348 oe_debug_pub.ADD('Exposure after taking care of uninvoiced orders only ='||to_char(l_total_exposure));
1349
1350 /* Next check if we should be excluding orders that are already on hold
1351 from the calculation of Total exposure
1352 NOTE: If l_orders_on_hold_flag = 'Y', that means INCLUDE the value of orders on
1353 hold and hence, the following calculation doesn't need to be done.
1354 */
1355
1356 IF l_orders_on_hold_flag = 'N' THEN
1357
1358 -- Find the value of all orders that are the subject of an order hold
1359 -- at either the header or line level.
1360
1361 /* additional task - Added the logic for include_tax_flag */
1362
1363 OPEN orders_on_hold(l_include_tax_flag);
1364 FETCH orders_on_hold INTO l_total_on_hold;
1365 IF orders_on_hold%notfound THEN
1366 l_total_on_hold := 0;
1367 END IF;
1368 CLOSE orders_on_hold;
1369
1370
1371 -- Now update the total exposure value to EXCLUDE the value of orders on hold
1372 oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
1373
1374 l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
1375
1376 oe_debug_pub.ADD('Total exposure after taking care of Hold ='||to_char(l_total_exposure));
1377
1378 END IF; -- orders on hold flag
1379
1380 -- Check Commitment Total if Commitment Sequencing "On"
1381 IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
1382
1383 OPEN commitment_total;
1384 FETCH commitment_total INTO l_total_commitment;
1385 IF commitment_total%notfound THEN
1386 l_total_commitment := 0;
1387 END IF;
1388 CLOSE commitment_total;
1389
1390 oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
1391
1392 -- If orders on hold are to be excluded then find out
1393 -- the commitment amount associated to orders on hold
1394 IF l_orders_on_hold_flag = 'N' THEN
1395 OPEN on_hold_commitment_total;
1396 FETCH on_hold_commitment_total INTO l_on_hold_commitment;
1397 IF on_hold_commitment_total%notfound THEN
1398 l_on_hold_commitment := 0;
1399 END IF;
1400
1401 CLOSE on_hold_commitment_total;
1402
1403 oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
1404 END IF;
1405
1406 OPEN current_commitment_total;
1407 FETCH current_commitment_total INTO l_current_commitment;
1408 IF current_commitment_total%notfound THEN
1409 l_current_commitment := 0;
1410 END IF;
1411 CLOSE current_commitment_total;
1412
1413 oe_debug_pub.ADD('Current Order Commitment Amount: ' || l_current_commitment);
1414
1415 l_total_commitment := l_total_commitment + l_current_commitment - nvl(l_on_hold_commitment, 0);
1416
1417 oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
1418
1419 -- Now update the total exposure value to EXCLUDE already applied Commitments.
1420 l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
1421
1422 oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
1423
1424 END IF; -- Commitment Sequencing
1425
1426 /* additional task - ADD Current Order value to the calculated exposure */
1427
1428
1429 OPEN current_order(l_include_tax_flag);
1430 FETCH current_order INTO l_current_order;
1431 IF current_order%notfound THEN
1432 l_current_order := 0;
1433 END IF;
1434 CLOSE current_order;
1435
1436 l_total_exposure := l_total_exposure + NVL(l_current_order,0);
1437 oe_debug_pub.ADD('Total exposure after taking care of Current Order (CUSTOMER level)='||to_char(l_total_exposure));
1438
1439
1440 END IF; -- uninvoiced order flag
1441
1442 ELSE -- Retrieving exposure at SITE level
1443
1444
1445 IF l_open_ar_balance_flag = 'Y' THEN
1446
1447 -- Find Accounts Receivable Exposure
1448
1449 IF l_open_ar_days IS NULL THEN
1450
1451 OPEN site_ar_balance;
1452 FETCH site_ar_balance INTO l_total_from_ar;
1453 IF site_ar_balance%notfound THEN
1454 l_total_from_ar := 0;
1455 END IF;
1456 CLOSE site_ar_balance;
1457
1458 ELSE
1459
1460 OPEN site_ar_balance_in_ar_days;
1461 FETCH site_ar_balance_in_ar_days INTO l_total_from_ar;
1462 IF site_ar_balance_in_ar_days%notfound THEN
1463 l_total_from_ar := 0;
1464 END IF;
1465 CLOSE site_ar_balance_in_ar_days;
1466
1467 END IF; -- If open_ar_days is null
1468
1469 oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
1470
1471 /* If the include payments at risk flag is set to yes
1472 Update the exposure by payments that are not thought to be collectable
1473 These payments are in the cash receipts history
1474 */
1475
1476
1477 IF l_include_risk_flag = 'Y' THEN
1478
1479 IF l_open_ar_days IS NULL THEN
1480
1481 OPEN site_pay_risk;
1482 FETCH site_pay_risk INTO l_payments_at_risk;
1483 IF site_pay_risk%notfound THEN
1484 l_payments_at_risk := 0;
1485 END IF;
1486 CLOSE site_pay_risk;
1487
1488 ELSE
1489
1490 OPEN site_pay_risk_in_ar_days;
1491 FETCH site_pay_risk_in_ar_days INTO l_payments_at_risk;
1492 IF site_pay_risk_in_ar_days%notfound THEN
1493 l_payments_at_risk := 0;
1494 END IF;
1495 CLOSE site_pay_risk_in_ar_days;
1496
1497 END IF; -- If open_ar_days is null
1498
1499 oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
1500
1501 END IF; -- Include Payments at Risk
1502
1503 -- Now update the total exposure value.
1504 l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
1505
1506 oe_debug_pub.ADD('Exposure from a/c receivables ='||to_char(l_total_exposure));
1507
1508 END IF; -- checking accounts receivables exposure
1509
1510
1511 /* If the include_uninvoiced_orders is set to yes,depending on the
1512 value of include_tax_flag, tax valuw will be included in the
1513 calculation of total_exposure.
1514 */
1515
1516
1517 IF l_uninvoiced_orders_flag = 'Y' THEN
1518
1519 /* additional task - added logic for include_tax_flag */
1520
1521 OPEN site_uninvoiced_orders(l_include_tax_flag);
1522 FETCH site_uninvoiced_orders INTO l_total_on_order;
1523 IF site_uninvoiced_orders%notfound THEN
1524 l_total_on_order := 0;
1525 END IF;
1526 CLOSE site_uninvoiced_orders;
1527
1528
1529 -- Now update the total exposure value to include the Order Backlog Value
1530
1531 oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
1532 l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
1533
1534 oe_debug_pub.ADD('Exposure after uninvoiced orders ='||to_char(l_total_exposure));
1535
1536 /* Next check if we should be excluding orders that are already on hold
1537 from the calculation of Total exposure
1538 NOTE: If l_orders_on_hold_flag = 'Y', that means INCLUDE the value of orders
1539 on hold and hence, the following calculation doesn't need to be done.
1540 */
1541
1542
1543 IF l_orders_on_hold_flag = 'N' THEN
1544
1545 -- Find the value of all orders that are the subject of an order hold
1546 -- at either the header or line level.
1547
1548 /* additional task - added logic for include_tax_flag */
1549
1550 OPEN site_orders_on_hold(l_include_tax_flag);
1551 FETCH site_orders_on_hold INTO l_total_on_hold;
1552 IF site_orders_on_hold%notfound THEN
1553 l_total_on_hold := 0;
1554 END IF;
1555 CLOSE site_orders_on_hold;
1556
1557 -- Now update the total exposure value to EXCLUDE the value of orders on hold
1558 oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
1559 l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
1560
1561 oe_debug_pub.ADD('Total Exposure after taking care of Hold ='||to_char(l_total_exposure));
1562
1563 END IF; -- check orders on hold
1564
1565 -- Check Commitment Total if Commitment Sequencing "On"
1566 IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
1567
1568 OPEN site_commitment_total;
1569 FETCH site_commitment_total INTO l_total_commitment;
1570 IF site_commitment_total%notfound THEN
1571 l_total_commitment := 0;
1572 END IF;
1573 CLOSE site_commitment_total;
1574
1575 oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
1576
1577 -- If orders on hold are to be excluded then find out
1578 -- the commitment amount associated to orders on hold
1579 IF l_orders_on_hold_flag = 'N' THEN
1580 OPEN site_on_hold_commitment_total;
1581 FETCH site_on_hold_commitment_total INTO l_on_hold_commitment;
1582 IF site_on_hold_commitment_total%notfound THEN
1583 l_on_hold_commitment := 0;
1584 END IF;
1585
1586 CLOSE site_on_hold_commitment_total;
1587
1588 oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
1589 END IF;
1590
1591 l_total_commitment := l_total_commitment - nvl(l_on_hold_commitment, 0);
1592
1593 -- Now update the total exposure value to EXCLUDE already applied Commitments.
1594 oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
1595
1596 l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
1597
1598 oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
1599
1600 END IF; -- Commitment Sequencing
1601
1602 /* additional task - ADD Current Order */
1603
1604
1605 OPEN current_order(l_include_tax_flag);
1606 FETCH current_order INTO l_current_order;
1607 IF current_order%notfound THEN
1608 l_current_order := 0;
1609 END IF;
1610 CLOSE current_order;
1611
1612 l_total_exposure := l_total_exposure + NVL(l_current_order,0);
1613 oe_debug_pub.ADD('Total exposure after taking care of Current Order (SITE level) ='||to_char(l_total_exposure));
1614
1615 END IF; -- uninvoiced order flag
1616
1617 END IF; -- credit exposure at site level or customer level
1618
1619 -- Load the Out Variable to be returned.
1620 oe_debug_pub.ADD('OUTPUT total exposure: '|| l_total_exposure);
1621 p_total_exposure := l_total_exposure;
1622
1623
1624 EXCEPTION
1625
1626 WHEN others THEN
1627 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1628 OE_MSG_PUB.Add_Exc_Msg
1629 ( G_PKG_NAME
1630 , 'Check_Exposure'
1631 );
1632 END IF;
1633 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1634
1635 RAISE ;
1636
1637 END Check_Exposure;
1638
1639 -- bug 1830389, new procedures introduced for line level credit checking.
1640 procedure chk_past_due_invoice_line (
1641 p_header_rec IN OE_Order_PUB.Header_Rec_Type
1642 ,p_invoice_to_org_id IN NUMBER
1643 ,p_customer_id IN NUMBER
1644 ,p_credit_rule_id IN NUMBER
1645 ,p_credit_level IN VARCHAR2
1646 ,p_check_past_due OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1647 ,p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1648 )
1649 IS
1650 l_maximum_days_past_due NUMBER;
1651 l_dummy VARCHAR2(30);
1652 BEGIN
1653 -- Default to pass back
1654 p_check_past_due := 'N';
1655
1656 select NVL(maximum_days_past_due, 0)
1657 into l_maximum_days_past_due
1658 from OE_CREDIT_CHECK_RULES
1659 where CREDIT_CHECK_RULE_ID = p_credit_rule_id;
1660
1661 if l_maximum_days_past_due > 0 then
1662 -- Check to see if there is any unpaid invoice that is past the
1663 -- due date.
1664 oe_debug_pub.ADD('OEXPCRCB.pls: line level maximum_days_past_due:' ||to_char(l_maximum_days_past_due) );
1665 BEGIN
1666 -- Default to Y, in case there is one or more invoices due.
1667 p_check_past_due := 'Y';
1668 select 'Any Past due invoice'
1669 into l_dummy
1670 from AR_PAYMENT_SCHEDULES
1671 WHERE CUSTOMER_ID = p_customer_id
1672 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
1673 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
1674 AND AMOUNT_DUE_REMAINING > 0
1675 AND DUE_DATE < sysdate - l_maximum_days_past_due;
1676 EXCEPTION
1677
1678 WHEN NO_DATA_FOUND THEN
1679 p_check_past_due := 'N';
1680 oe_debug_pub.ADD('OEXPCRCB.pls: No Invoices Past due -- line level' );
1681 WHEN TOO_MANY_ROWS THEN
1682 null;
1683 END;
1684
1685
1686 end if;
1687 oe_debug_pub.ADD('OEXPCRCB.pls: Line level Past due Invoice Check:' || p_check_past_due);
1688 EXCEPTION
1689
1690 WHEN others THEN
1691 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1692 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1693
1694 OE_MSG_PUB.Add_Exc_Msg
1695 ( G_PKG_NAME
1696 ,'chk_past_due_invoice'
1697 );
1698 END IF;
1699 RAISE;
1700
1701 END chk_past_due_invoice_line;
1702
1703
1704 -- calculates total exposure, find credit limits and determin result for calling function.
1705 /* additional task - made the procedure Check_trx_Limit
1706 local to this package and added p_credit_rule_id as
1707 an additional input parameter */
1708
1709
1710 PROCEDURE Check_Trx_Limit_Line
1711 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
1712 , p_invoice_to_org_id IN NUMBER
1713 , p_customer_id IN NUMBER
1714 , p_credit_rule_id IN NUMBER
1715 , p_credit_level IN VARCHAR2
1716 , p_trx_credit_limit IN NUMBER := FND_API.G_MISS_NUM
1717 , p_total_exposure IN NUMBER
1718 , p_overall_credit_limit IN NUMBER
1719 , p_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1720 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1721 )
1722 IS
1723
1724 l_order_value NUMBER;
1725 l_include_tax_flag VARCHAR2(1) := 'Y';
1726
1727 l_order_commitment NUMBER;
1728
1729 BEGIN
1730
1731 -- Initialize return status to success
1732 p_return_status := FND_API.G_RET_STS_SUCCESS;
1733
1734 -- Default to Pass
1735 p_result_out := 'PASS';
1736
1737 /* additional task- Read the value of include_tax_flag from credit check rule
1738 and calculate the value of l_order_values accordingly */
1739
1740 /* If the value of include_tax_flag is NULL that means it is 'No' */
1741
1742 select NVL(include_tax_flag, 'N')
1743 into l_include_tax_flag
1744 from OE_CREDIT_CHECK_RULES
1745 where CREDIT_CHECK_RULE_ID = p_credit_rule_id;
1746
1747 -- Total order limit of the site should be compared against
1748 -- the sum of all the lines that has this site
1749 -- Depending on the value of tax_flag add the tax_value
1750 /*
1751 ** Commenting based on the discussion with zbutt on 07/16/01
1752 ** Only one select to find out Trxn level order total needed
1753 IF p_credit_level = 'SITE' THEN
1754 */
1755 SELECT SUM(decode(l_include_tax_flag , 'Y', NVL(tax_value,0),0)
1756 + (NVL(unit_selling_price,0)
1757 * (NVL(ordered_quantity,0) )))
1758 INTO l_order_value
1759 FROM OE_ORDER_LINES
1760 WHERE HEADER_ID = p_header_rec.header_id
1761 AND invoice_to_org_id = p_invoice_to_org_id;
1762 /*
1763 ELSE
1764 SELECT SUM(decode(l_include_tax_flag , 'Y', NVL(tax_value,0),0)
1765 + (NVL(unit_selling_price,0)
1766 * (NVL(ordered_quantity,0) )))
1767 INTO l_order_value
1768 FROM OE_ORDER_LINES
1769 WHERE HEADER_ID = p_header_rec.header_id
1770 AND sold_to_org_id = (select organization_id
1771 from oe_sold_to_orgs_v
1772 where customer_id = p_customer_id);
1773
1774
1775 END IF;
1776 */
1777
1778 -- Get Total Commitments applied to the current order if Commitment Sequencing is "On"
1779 IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
1780
1781 SELECT NVL(SUM(P.commitment_applied_amount), 0)
1782 INTO l_order_commitment
1783 FROM OE_PAYMENTS P, OE_ORDER_LINES L
1784 WHERE P.HEADER_ID = p_header_rec.header_id
1785 AND L.LINE_ID = P.LINE_ID
1786 AND L.INVOICE_TO_ORG_ID = p_invoice_to_org_id;
1787
1788 oe_debug_pub.ADD('OEXPCRCB.pls: line level trx commitment total:' || l_order_commitment);
1789
1790 -- get the actual order value subject to credit check.
1791 l_order_value := l_order_value - l_order_commitment;
1792
1793 END IF;
1794
1795 -- If credit available is less than the total exposure or
1796 -- if the order amount is greater than the transaction limit
1797 -- Return Failure
1798
1799 oe_debug_pub.ADD('OEXPCRCB.pls: line level total exposure is:' ||p_total_exposure );
1800 oe_debug_pub.ADD('OEXPCRCB.pls: line level total credit limit:' || p_overall_credit_limit);
1801 oe_debug_pub.ADD('OEXPCRCB.pls: line level total order value:' || l_order_value);
1802 oe_debug_pub.ADD('OEXPCRCB.pls: line level order credit limit:' || p_trx_credit_limit);
1803 -- Replaced this code
1804 -- IF l_order_value > p_trx_credit_limit OR
1805 -- p_total_exposure > p_overall_credit_limit THEN
1806 -- p_result_out := 'FAIL';
1807 -- oe_debug_pub.ADD('Over credit limit');
1808 -- END IF;
1809
1810 -- With this
1811 if (p_trx_credit_limit <> -1) then
1812 if (l_order_value > p_trx_credit_limit) then
1813 p_result_out := 'FAIL';
1814 oe_debug_pub.ADD('Line Level: Order Value greater then Transaction Limit.');
1815 end if;
1816 end if;
1817 if (p_overall_credit_limit <> -1) then
1818 if (p_total_exposure > p_overall_credit_limit) then
1819 p_result_out := 'FAIL';
1820 oe_debug_pub.ADD('Line Level: Total Exposure is greater then Overall Credit Limit');
1821 end if;
1822 end if;
1823
1824
1825 EXCEPTION
1826
1827 WHEN others THEN
1828 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1829 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1830 OE_MSG_PUB.Add_Exc_Msg
1831 ( G_PKG_NAME
1832 , 'Check_Trx_Limit_Line'
1833 );
1834 END IF;
1835 RAISE ;
1836
1837 END Check_Trx_Limit_Line;
1838
1839
1840
1841 -- Mainline Function that will read an Order Header and Determine if
1842 -- should be checked, calculates total exposure, find credit limits
1843 -- and determine result for calling function.
1844
1845 ----------------------------------------------------------------------------
1846 PROCEDURE Check_Available_Credit_Line
1847 ( p_header_id IN NUMBER := FND_API.G_MISS_NUM
1848 , p_invoice_to_org_id IN NUMBER := FND_API.G_MISS_NUM
1849 , p_calling_action IN VARCHAR2 := 'BOOKING'
1850 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
1851 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1852 , p_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Pass or Fail Credit Check
1853 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1854 )
1855 IS
1856 l_header_rec OE_Order_PUB.Header_Rec_Type;
1857 --l_line_rec OE_Order_PUB.Line_Rec_Type;
1858 l_credit_rule_id NUMBER;
1859 l_credit_level VARCHAR2(30); -- are the limits at the customer
1860 -- or site level
1861 --l_order_value NUMBER;
1862 l_check_order VARCHAR2(1); -- Indicates if this Order is
1863 -- subject to credit check
1864 l_total_exposure NUMBER;
1865 l_trx_credit_limit NUMBER;
1866 l_overall_credit_limit NUMBER;
1867 l_result_out VARCHAR2(30);
1868 l_return_status VARCHAR2(30);
1869 l_check_past_due VARCHAR2(1); -- if any invoice is past due
1870 l_customer_id NUMBER;
1871 BEGIN
1872
1873 -- Set the default behaviour to pass credit check
1874 -- oe_debug_pub.debug_on;
1875
1876 p_result_out := 'PASS';
1877 p_return_status := FND_API.G_RET_STS_SUCCESS;
1878
1879 oe_debug_pub.ADD('Line Level: Calling action is '|| p_calling_action);
1880
1881 -- The first thing to do is to load the record structure for the order header
1882 -- This is done in the OE_HEADER_UTIL package by the Query Row function.
1883 -- The caller must pass a Header id and the function returns the record
1884 -- Structure l_header_rec
1885
1886 oe_debug_pub.ADD('Line Level: Before querying');
1887 OE_HEADER_UTIL.QUERY_ROW(p_header_id => p_header_id
1888 ,x_header_rec => l_header_rec);
1889
1890 -- OE_LINE_UTIL.Query_Row(p_line_id => p_line_id
1891 -- ,x_line_rec => l_line_rec );
1892
1893
1894 BEGIN
1895 SELECT customer_id
1896 INTO l_customer_id
1897 FROM oe_invoice_to_orgs_v
1898 WHERE ORGANIZATION_ID = p_invoice_to_org_id;
1899 EXCEPTION
1900 WHEN no_data_found then
1901 --x_return_status := FND_API.G_RET_STS_ERROR;
1902 --FND_MESSAGE.SET_NAME('ONT', 'OE_INVALID_SITE_USE_ID');
1903 --OE_MSG_PUB.ADD;
1904 --fnd_message.set_token('SITE_USE_ID',
1905 -- to_char(p_hold_entity_id));
1906 OE_Debug_PUB.Add('OEXPCRCB: Line Level: No Customer ID for Bill to:' ||
1907 to_char(p_invoice_to_org_id), 1);
1908 RAISE FND_API.G_EXC_ERROR;
1909 END;
1910 OE_Debug_PUB.Add('OEXPCRCB: Line Level: Customer ID:' ||
1911 to_char(l_customer_id), 1);
1912 -- Now we have the Record Structure loaded we can call the other
1913 -- functions without having to go to the database.
1914 -- Checking whether the order should undergo a credit check. Also
1915 -- returns whether the check should be at the customer level or the
1916 -- bill-to site level and the credit limits at that level.
1917
1918 oe_debug_pub.ADD('just before the check Line procedure');
1919 OE_Credit_PUB.Check_Order_Line
1920 ( l_header_rec
1921 , p_invoice_to_org_id
1922 , l_customer_id
1923 , p_calling_action
1924 , l_check_order
1925 , l_credit_rule_id
1926 , l_credit_level
1927 , l_overall_credit_limit
1928 , l_trx_credit_limit
1929 , l_return_status
1930 );
1931
1932 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1933 RAISE FND_API.G_EXC_ERROR;
1934 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1935 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1936 END IF;
1937
1938 IF l_check_order = 'Y' THEN
1939
1940 -- If the Order is subject to Credit Check i.e. l_check_order = 'Y'
1941 -- First check if there are any unpaid invoices that are passed the
1942 -- maximum due dates.
1943 oe_debug_pub.ADD('Line Level: Calling Check Past Due Invoice procedure');
1944
1945 oe_credit_pub.chk_past_due_invoice_line (
1946 l_header_rec
1947 ,p_invoice_to_org_id
1948 ,l_customer_id
1949 ,l_credit_rule_id
1950 ,l_credit_level
1951 ,l_check_past_due
1952 ,l_return_status
1953 );
1954 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1955 RAISE FND_API.G_EXC_ERROR;
1956 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1957 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1958 END IF;
1959
1960 IF l_check_past_due = 'N' THEN
1961 -- Determine total exposure.
1962
1963 oe_debug_pub.ADD('Line Level: Calling the check exposure procedure');
1964 OE_Credit_PUB.Check_Exposure_Line
1965 ( l_header_rec
1966 , p_invoice_to_org_id
1967 , l_customer_id
1968 , l_credit_rule_id
1969 , l_credit_level
1970 , l_total_exposure
1971 , l_return_status
1972 );
1973
1974 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1975 RAISE FND_API.G_EXC_ERROR;
1976 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1977 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1978 END IF;
1979
1980 oe_debug_pub.ADD('Line Level: Credit Rule Id ='||to_char(l_credit_rule_id));
1981
1982 -- Next, compare the order amount and the exposure to the
1983 -- order credit limit and total credit limit.
1984
1985 /* additional task - now credit_rule_id is passed to Check_trx_Limit_line*/
1986
1987
1988 OE_Credit_PUB.Check_Trx_Limit_Line
1989 ( l_header_rec
1990 , p_invoice_to_org_id
1991 , l_customer_id
1992 , l_credit_rule_id
1993 , l_credit_level -- New
1994 , l_trx_credit_limit
1995 , l_total_exposure
1996 , l_overall_credit_limit
1997 , l_result_out
1998 , l_return_status
1999 );
2000
2001 oe_debug_pub.add('Line Level: After the call for check_Trx_Limit_Line');
2002 oe_debug_pub.add('Line level: Result out ='||l_result_out);
2003 oe_debug_pub.add('Line Level: Return Status ='||l_return_status);
2004
2005 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2006 RAISE FND_API.G_EXC_ERROR;
2007 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2008 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2009 END IF;
2010
2011 p_result_out := l_result_out;
2012
2013 ELSE -- l_check_past_due = 'N'
2014 oe_debug_pub.ADD('Line Level: Past due Invoices: Credit Check Failed');
2015 p_result_out := 'FAIL';
2016 END IF; -- l_check_past_due = 'N'
2017 ELSE -- if credit check order = N
2018
2019 oe_debug_pub.ADD('Line Level: No credit check required');
2020 -- FND_MESSAGE.SET_NAME('OE', 'OE_NO_CREDIT_CHECK_REQUIRED');
2021 -- FND_MSG_PUB.ADD;
2022 -- null;
2023
2024 END IF;
2025
2026 -- oe_debug_pub.dumpdebug;
2027 -- oe_debug_pub.debug_off;
2028
2029 -- Count the Messages on the Message Stack and if only 1 return it in
2030 -- message data.
2031 -- If more than 1 just return the count. The Calling routine has to get
2032 -- the messages from the message stack.
2033
2034 OE_MSG_PUB.Count_And_Get
2035 ( p_count => p_msg_count
2036 , p_data => p_msg_data
2037 );
2038
2039 EXCEPTION
2040
2041 WHEN FND_API.G_EXC_ERROR THEN
2042
2043 p_return_status := FND_API.G_RET_STS_ERROR;
2044
2045 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2046
2047 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2048
2049 WHEN OTHERS THEN
2050
2051 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2052
2053 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2054 THEN
2055 OE_MSG_PUB.Add_Exc_Msg
2056 ( G_PKG_NAME
2057 , 'Check_Available_Credit'
2058 );
2059 END IF;
2060
2061 END Check_Available_Credit_Line;
2062
2063 /* ----------------------------------------------------------------------
2064 Procedure to determine if the Line is subject to credit check and
2065 if it is, return whether the check is at the site level or at the
2066 customer level. Also, returns the credit limits at that level.
2067 -------------------------------------------------------------------------
2068 */
2069
2070
2071 PROCEDURE Check_Order_Line
2072 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
2073 , p_invoice_to_org_id IN NUMBER
2074 , p_customer_id IN NUMBER
2075 , p_calling_action IN VARCHAR2 := 'BOOKING'
2076 , p_check_Order_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2077 , p_credit_rule_out OUT NOCOPY /* file.sql.39 change */ NUMBER
2078 , p_credit_check_lvl_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2079 , p_overall_credit_limit OUT NOCOPY /* file.sql.39 change */ NUMBER
2080 , p_trx_credit_limit OUT NOCOPY /* file.sql.39 change */ NUMBER
2081 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2082 )
2083 IS
2084 -- Set up working Variables for Check Order
2085 l_credit_limit_test NUMBER := FND_API.G_MISS_NUM;
2086 l_credit_check_term NUMBER := FND_API.G_MISS_NUM;
2087 l_credit_check_rule_id NUMBER := FND_API.G_MISS_NUM;
2088 l_credit_check_lvl_out VARCHAR2(30) := 'SITE';
2089 l_check_order VARCHAR2(1);
2090
2091 BEGIN
2092 -- Function to Determine if the Order Type is Subject to Credit Check
2093 -- Assume that the Order is subject to credit Check by setting the
2094 -- l_check_order variable to Yes.
2095
2096 p_check_Order_out := 'Y';
2097 l_check_Order := 'Y';
2098
2099 -- Set up a variable to capture the situation of having no credit
2100 -- profile set up.
2101
2102 p_return_status := FND_API.G_RET_STS_SUCCESS;
2103
2104 -- Read the Credit Rules on the Order Type Definition for the
2105 -- Order Being Credit Checked.
2106 -- If Called from Validating the Order at Order Entry use the
2107 -- Entry Credit Check Rule.
2108
2109 oe_debug_pub.ADD('Line Level: Which cchk rule');
2110 IF p_calling_action = 'BOOKING' THEN
2111 oe_debug_pub.ADD('Line Level: Selecting the order entry cchk rule');
2112 /*7194250
2113 SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
2114 INTO l_credit_check_rule_id
2115 FROM OE_ORDER_TYPES_V
2116 WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
2117 7194250*/
2118 --7194250
2119 SELECT NVL(ENTRY_CREDIT_CHECK_RULE_ID, -1)
2120 INTO l_credit_check_rule_id
2121 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
2122 WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
2123 AND ENTRY_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
2124 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
2125 --7194250
2126 -- If not Use the Shipping Rule for all other calling Actions
2127 OE_Verify_Payment_PUB.G_credit_check_rule := 'Ordering'; --ER#7479609
2128 ELSE
2129 oe_debug_pub.ADD('Line Level: Selecting the shipping cchk rule');
2130 /*7194250
2131 SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
2132 INTO l_credit_check_rule_id
2133 FROM OE_ORDER_TYPES_V
2134 WHERE ORDER_TYPE_ID = p_header_rec.order_type_id;
2135 7194250*/
2136 --7194250
2137 SELECT NVL(SHIPPING_CREDIT_CHECK_RULE_ID, -1)
2138 INTO l_credit_check_rule_id
2139 FROM OE_ORDER_TYPES_V OT,OE_CREDIT_CHECK_RULES CCR
2140 WHERE OT.ORDER_TYPE_ID = p_header_rec.order_type_id
2141 AND SHIPPING_CREDIT_CHECK_RULE_ID=CCR.CREDIT_CHECK_RULE_ID
2142 AND Trunc(SYSDATE) BETWEEN NVL(CCR.START_DATE_ACTIVE, Trunc(SYSDATE)) AND NVL(CCR.END_DATE_ACTIVE, Trunc(SYSDATE));
2143 --7194250
2144 OE_Verify_Payment_PUB.G_credit_check_rule := 'Shipping'; --ER#7479609
2145 END IF;
2146
2147 -- If no credit rule was found for the calling action the Order
2148 -- is not subject to credit check
2149
2150 oe_debug_pub.ADD('line level: check order after selecting credit rule : '|| l_check_order);
2151 IF l_credit_check_rule_id <= 0 THEN
2152 oe_debug_pub.ADD('Line Level: No Credit Check rule defined for ' || p_calling_action);
2153 l_check_order := 'N';
2154 END IF;
2155
2156 -- If the Order type is subject to credit check we should check the
2157 -- bill to site and customer to see if they are subject to credit check.
2158
2159 IF l_check_order = 'Y' THEN
2160
2161 -- The Order type is Subject to Credit Check.
2162 -- Procedure to determine if the Order Bill to is subject to Credit Check
2163 -- Get the flags that control if credit check should be performed
2164 -- from the Credit Profile. Get the Credit Limit Amounts from the
2165 -- Credit Profile Amounts for the Customers Bill to Address.
2166
2167 BEGIN
2168
2169 SELECT NVL(CP.CREDIT_CHECKING, 'N')
2170 , (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
2171 , NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
2172 , NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
2173 INTO l_check_order
2174 , l_credit_limit_test
2175 , p_overall_credit_limit
2176 , p_trx_credit_limit
2177 FROM HZ_CUSTOMER_PROFILES CP
2178 , HZ_CUST_PROFILE_AMTS CPA
2179 WHERE CP.CUST_ACCOUNT_ID = p_customer_id
2180 AND CP.SITE_USE_ID = p_invoice_to_org_id
2181 AND CPA.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
2182 AND CPA.SITE_USE_ID = CP.SITE_USE_ID
2183 AND CPA.CUST_ACCOUNT_PROFILE_ID = CP.CUST_ACCOUNT_PROFILE_ID
2184 AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code;
2185
2186 -- If we find a Credit Profile at this level the customer has credit limits
2187 -- at the site level.
2188 l_credit_check_lvl_out := 'SITE';
2189 oe_debug_pub.ADD('Line Level: Site/CheckFlag/Limit Lest/overall_credit_limit/trx_credit_limit:'
2190 || to_char(p_invoice_to_org_id) || '/'
2191 || l_check_order || '/'
2192 || to_char(l_credit_limit_test) || '/'
2193 || to_char(p_overall_credit_limit) || '/'
2194 || to_char(p_trx_credit_limit) );
2195
2196 EXCEPTION
2197
2198 WHEN NO_DATA_FOUND THEN
2199 -- If we do not find a Credit Profile we will assume that the credit
2200 -- limits are set at the customer level.
2201 oe_debug_pub.ADD('Line Level: Customer level credit check');
2202 l_credit_check_lvl_out := 'CUSTOMER';
2203 WHEN OTHERS THEN
2204 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2205 OE_MSG_PUB.Add_Exc_Msg
2206 ( G_PKG_NAME
2207 , 'Check Order:Check Customer'
2208 );
2209 END IF;
2210 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2212 END;
2213
2214 -- If no credit limit has been set for either the total Order Amount
2215 -- or the Total Exposure, get the credit profiles from the customer level
2216 IF l_credit_limit_test < 0 and l_credit_check_lvl_out ='SITE' THEN
2217 l_credit_check_lvl_out := 'CUSTOMER';
2218 END IF;
2219
2220 END IF; -- Check if the Order type is subject to credit check for site or customer
2221
2222 oe_debug_pub.ADD('line level: check order after selecting site/customer level : ' || l_check_order);
2223 oe_debug_pub.ADD('level for credit check: '|| l_credit_check_lvl_out);
2224 IF l_check_order = 'Y' AND l_credit_check_lvl_out = 'CUSTOMER' THEN
2225
2226 -- If both the Order type and the Bill to are subject to credit check
2227 -- and no credit profile was found at the bill to site go on to
2228 -- check if the customer is subject to credit check.
2229 -- Procedure to Determine if the Customer is subject to Credit Check
2230
2231 BEGIN
2232
2233 SELECT NVL(CP.CREDIT_CHECKING,'N')
2234 , (NVL(CPA.OVERALL_CREDIT_LIMIT,-1) + NVL(CPA.TRX_CREDIT_LIMIT, -1))
2235 , NVL(CPA.OVERALL_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
2236 , NVL(CPA.TRX_CREDIT_LIMIT * ((100 + CP.TOLERANCE)/100), -1)
2237 INTO l_check_order
2238 , l_credit_limit_test
2239 , p_overall_credit_limit
2240 , p_trx_credit_limit
2241 FROM HZ_CUSTOMER_PROFILES CP
2242 , HZ_CUST_PROFILE_AMTS CPA
2243 WHERE CP.CUST_ACCOUNT_ID = p_customer_id
2244 AND CP.CUST_ACCOUNT_PROFILE_ID = CPA.CUST_ACCOUNT_PROFILE_ID
2245 AND CPA.CURRENCY_CODE = p_header_rec.transactional_curr_code
2246 AND CP.SITE_USE_ID IS NULL;
2247
2248 oe_debug_pub.ADD('Customer/CheckFlag/LimitTest/overall_credit_limit/trx_credit_limit:'
2249 || to_char(p_customer_id) || '/'
2250 || l_check_order || '/'
2251 || to_char(l_credit_limit_test) || '/'
2252 || to_char(p_overall_credit_limit) || '/'
2253 || to_char(p_trx_credit_limit) );
2254
2255 EXCEPTION
2256 WHEN NO_DATA_FOUND THEN
2257 -- If we don't find a credit profile at the customer level, no
2258 -- credit checking needed.
2259 l_check_order := 'N';
2260
2261 WHEN OTHERS THEN
2262 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2263 OE_MSG_PUB.Add_Exc_Msg
2264 ( G_PKG_NAME
2265 , 'Check Order:Check Customer'
2266 );
2267 END IF;
2268 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2269 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2270 END;
2271
2272 -- If no credit limit has been set for either the total Order Amount
2273 -- or the Total Exposure, do not credit check
2274 IF l_credit_limit_test < 0 THEN
2275 l_check_order := 'N';
2276 END IF;
2277 oe_debug_pub.ADD('check order after determining credit limits :'|| l_check_order);
2278 END IF; -- The Order type Subject to Credit Check No credit profile at bill to.
2279
2280 IF l_check_order = 'Y' THEN
2281
2282 -- Order Type, Bill to or Customer are subject to Credit Check
2283 -- If the Order Type, Bill to Site and Customer are subject to credit check,
2284 -- go on to check if the payment term is subject to credit check.
2285 -- Procedure to Determine if Payment Term is subject to Credit Check
2286 -- Check if line on the order has a payment term that requires credit checking.
2287
2288 -- Check to see if all the lines for this bill to have payment term set to 'Y'
2289 BEGIN
2290
2291 SELECT COUNT(*)
2292 INTO l_credit_check_term
2293 FROM OE_ORDER_LINES L, OE_RA_TERMS_V T
2294 WHERE L.header_id = p_header_rec.header_id
2295 AND L.invoice_to_org_id = p_invoice_to_org_id
2296 AND L.PAYMENT_TERM_ID = T.TERM_ID
2297 AND NVL(T.CREDIT_CHECK_FLAG, 'Y') = 'Y';
2298
2299 -- If all the lines does not have a payment term that is subject to credit check
2300 -- we should exempt the bill to from credit check.
2301 IF l_credit_check_term = 0 THEN
2302 l_check_order := 'N';
2303 oe_debug_pub.ADD('Line does not have Payment Term subject to credit check');
2304 END IF;
2305
2306 EXCEPTION
2307 WHEN NO_DATA_FOUND THEN
2308 l_check_order := 'N';
2309 oe_debug_pub.ADD('Line does not have Payment Term subject to credit check');
2310 WHEN OTHERS THEN
2311 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2312 OE_MSG_PUB.Add_Exc_Msg
2313 ( G_PKG_NAME
2314 , 'Check_Order: Check Terms'
2315 );
2316 END IF;
2317 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2319 END;
2320
2321 END IF; -- Order Type, Bill to or Customer are subject to Credit Check
2322
2323 p_credit_check_lvl_out := l_credit_check_lvl_out;
2324 p_credit_rule_out := l_credit_check_rule_id;
2325 p_check_Order_out := l_check_order;
2326
2327 oe_debug_pub.ADD('check order after checking payment term :'|| l_check_order);
2328 EXCEPTION
2329
2330 WHEN OTHERS THEN
2331 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2332 THEN
2333 OE_MSG_PUB.Add_Exc_Msg
2334 ( G_PKG_NAME
2335 , 'Check_Order'
2336 );
2337 END IF;
2338 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2340
2341 END Check_Order_line;
2342
2343
2344 PROCEDURE Check_Exposure_Line
2345 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
2346 , p_invoice_to_org_id IN NUMBER
2347 , p_customer_id IN NUMBER
2348 , p_credit_check_rule_id IN NUMBER
2349 , p_credit_level IN VARCHAR2
2350 , p_total_exposure OUT NOCOPY /* file.sql.39 change */ NUMBER
2351 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2352 )
2353 IS
2354 l_open_ar_balance_flag Varchar2(1) := 'Y';
2355 l_open_ar_days NUMBER;
2356 l_uninvoiced_orders_flag Varchar2(1) := 'Y';
2357 l_orders_on_hold_flag Varchar2(1) := 'Y';
2358
2359 /* additional task -added the following variable */
2360 l_include_tax_flag Varchar2(1) := 'Y';
2361
2362 l_shipping_horizon Date := TO_DATE('31/12/4712', 'DD/MM/YYYY');
2363 l_include_risk_flag Varchar2(1) := 'Y';
2364 l_total_from_ar NUMBER := 0;
2365 l_total_on_order NUMBER := 0;
2366 l_total_on_hold NUMBER := 0;
2367 l_total_exposure NUMBER := 0;
2368 l_payments_at_risk NUMBER := 0;
2369 l_current_order NUMBER := 0;
2370
2371 -- ZB
2372 l_maximum_days_past_due NUMBER := 0;
2373
2374 --additional task : added the following variable
2375 l_est_valid_days NUMBER := 0 ;
2376
2377 l_total_commitment NUMBER := 0;
2378 l_current_commitment NUMBER := 0;
2379 l_on_hold_commitment NUMBER := 0;
2380
2381 /* additional task - for adding the Include Tax option, the cursor needs to be modified */
2382
2383 Cursor credit_check_rule IS
2384 SELECT OPEN_AR_BALANCE_FLAG
2385 , OPEN_AR_DAYS
2386 , UNINVOICED_ORDERS_FLAG
2387 , ORDERS_ON_HOLD_FLAG
2388 , INCLUDE_TAX_FLAG
2389 , DECODE(SHIPPING_INTERVAL, NULL,
2390 TO_DATE('31/12/4712', 'DD/MM/YYYY'),
2391 SHIPPING_INTERVAL+SYSDATE)
2392 , INCLUDE_PAYMENTS_AT_RISK_FLAG
2393 FROM OE_CREDIT_CHECK_RULES
2394 WHERE CREDIT_CHECK_RULE_ID = p_credit_check_rule_id;
2395
2396 Cursor ar_balance IS
2397 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
2398 FROM AR_PAYMENT_SCHEDULES
2399 WHERE CUSTOMER_ID = p_customer_id
2400 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
2401 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
2402
2403 Cursor ar_balance_in_ar_days IS
2404 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
2405 FROM AR_PAYMENT_SCHEDULES
2406 WHERE CUSTOMER_ID = p_customer_id
2407 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
2408 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
2409 AND SYSDATE - TRX_DATE > l_open_ar_days;
2410
2411 Cursor pay_risk IS
2412 SELECT NVL(SUM(CRH.AMOUNT), 0)
2413 FROM AR_CASH_RECEIPT_HISTORY CRH,
2414 AR_CASH_RECEIPTS CR
2415 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
2416 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
2417 AND CRH.CURRENT_RECORD_FLAG = 'Y'
2418 AND CRH.STATUS <> DECODE(CRH.FACTOR_FLAG,'Y',
2419 'RISK_ELIMINATED','CLEARED')
2420 AND CRH.STATUS <> 'REVERSED'
2421 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
2422 AND CR.PAY_FROM_CUSTOMER = p_customer_id;
2423
2424 Cursor pay_risk_in_ar_days IS
2425 SELECT NVL(SUM(CRH.AMOUNT), 0)
2426 FROM AR_CASH_RECEIPT_HISTORY CRH
2427 , AR_CASH_RECEIPTS CR
2428 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
2429 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
2430 AND CRH.CURRENT_RECORD_FLAG = 'Y'
2431 AND CRH.STATUS <>
2432 DECODE(CRH.FACTOR_FLAG,'Y',
2433 'RISK_ELIMINATED','CLEARED')
2434 AND CRH.STATUS <> 'REVERSED'
2435 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
2436 AND CR.PAY_FROM_CUSTOMER = p_customer_id
2437 AND SYSDATE - CR.RECEIPT_DATE > l_open_ar_days;
2438
2439
2440 /* additional task : modified the following 2 cursors uninvoiced_orders and
2441 orders_on_hold for the following purpose
2442 1. excluded unbooked orders
2443 2. excluded orders authorized by credit card
2444 3. Exclude the Current Order
2445
2446 So far there was a bug in the application - if the current order was
2447 on hold and booked then current order value was not getting included
2448 into the credit exposure. With present logic, we exclude the current order
2449 initially while calculating the uninvoiced order total and orders_on_hold
2450 total and then add it up later. Because irrespective of the fact that
2451 current order is booked/entered/on hold etc, we have to take care of that
2452 amount in Credit exposure.
2453
2454 Two additional conditions has been added for all cursors related
2455 to Include Uninvoiced Orders. We will consider only those orders
2456 which are not invoiced yet, i.e. invoiced qty is zero.
2457 Also we should consider Open Orders Only
2458 The decode statement has been added to improve performance*/
2459
2460 Cursor uninvoiced_orders(p_include_tax VARCHAR2) IS
2461 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
2462 *NVL(unit_selling_price,0)) + decode(p_include_tax,'Y',NVL(tax_value,0), 0))
2463 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2464 WHERE H.SOLD_TO_ORG_ID = p_customer_id
2465 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2466 AND H.HEADER_ID = L.HEADER_ID
2467 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2468 <= l_shipping_horizon
2469 AND NVL(L.INVOICED_QUANTITY,0) = 0
2470 AND L.OPEN_FLAG ='Y'
2471 AND L.LINE_CATEGORY_CODE ='ORDER'
2472 AND H.BOOKED_FLAG = 'Y'
2473 AND H.HEADER_ID <> p_header_rec.header_id
2474 /*
2475 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2476 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
2477 */
2478 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2479 OR (h.payment_type_code = 'CREDIT_CARD'
2480 AND NOT EXISTS
2481 (Select 'valid auth code'
2482 From oe_payments op,
2483 iby_trxn_ext_auths_v ite
2484 Where op.header_id = h.header_id
2485 And op.trxn_extension_id = ite.trxn_extension_id
2486 And authorization_status = 0
2487 And effective_auth_amount > 0)
2488 )
2489 );
2490
2491 /* old code, logic remaining same:
2492 AND (H.CREDIT_CARD_APPROVAL_CODE IS NULL
2493 OR
2494 (H.CREDIT_CARD_APPROVAL_CODE IS NOT NULL
2495 AND H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days < SYSDATE))*/
2496
2497 Cursor /* MOAC_SQL_CHANGE */ orders_on_hold(p_include_tax VARCHAR2) IS
2498 SELECT SUM((NVL(ordered_quantity,0)
2499 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y',NVL(tax_value,0),0))
2500 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2501 WHERE H.SOLD_TO_ORG_ID = p_customer_id
2502 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2503 AND H.HEADER_ID = L.HEADER_ID
2504 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2505 <= l_shipping_horizon
2506 AND NVL(L.INVOICED_QUANTITY,0) = 0
2507 AND L.OPEN_FLAG ='Y'
2508 AND L.LINE_CATEGORY_CODE ='ORDER'
2509 AND H.BOOKED_FLAG = 'Y'
2510 AND H.HEADER_ID <> p_header_rec.header_id
2511 /*
2512 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2513 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
2514 */
2515 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2516 OR (h.payment_type_code = 'CREDIT_CARD'
2517 AND NOT EXISTS
2518 (Select 'valid auth code'
2519 From oe_payments op,
2520 iby_trxn_ext_auths_v ite
2521 Where op.header_id = h.header_id
2522 And op.trxn_extension_id = ite.trxn_extension_id
2523 And authorization_status = 0
2524 And effective_auth_amount > 0)
2525 )
2526 )
2527 AND EXISTS (SELECT 1
2528 FROM OE_ORDER_HOLDS OH
2529 WHERE H.HEADER_ID = OH.HEADER_ID
2530 AND OH.HOLD_RELEASE_ID IS NULL
2531 );
2532 Cursor commitment_total IS
2533 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
2534 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
2535 WHERE H.SOLD_TO_ORG_ID = p_customer_id
2536 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2537 AND H.BOOKED_FLAG = 'Y'
2538 AND H.HEADER_ID = P.HEADER_ID
2539 AND H.HEADER_ID <> p_header_rec.header_id
2540 /*
2541 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2542 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
2543 */
2544 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2545 OR (h.payment_type_code = 'CREDIT_CARD'
2546 AND NOT EXISTS
2547 (Select 'valid auth code'
2548 From oe_payments op,
2549 iby_trxn_ext_auths_v ite
2550 Where op.header_id = h.header_id
2551 And op.trxn_extension_id = ite.trxn_extension_id
2552 And authorization_status = 0
2553 And effective_auth_amount > 0)
2554 )
2555 )
2556 AND L.HEADER_ID = H.HEADER_ID
2557 AND L.LINE_ID = P.LINE_ID
2558 AND NVL(L.INVOICED_QUANTITY,0) = 0
2559 AND L.OPEN_FLAG = 'Y'
2560 AND L.LINE_CATEGORY_CODE = 'ORDER'
2561 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2562 <= l_shipping_horizon;
2563
2564 Cursor on_hold_commitment_total IS
2565 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
2566 FROM OE_PAYMENTS P, OE_ORDER_HEADERS H, OE_ORDER_LINES_ALL L
2567 WHERE H.SOLD_TO_ORG_ID = p_customer_id
2568 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2569 AND H.BOOKED_FLAG = 'Y'
2570 AND H.HEADER_ID = P.HEADER_ID
2571 AND H.HEADER_ID <> p_header_rec.header_id
2572 /*
2573 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2574 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
2575 */
2576 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2577 OR (h.payment_type_code = 'CREDIT_CARD'
2578 AND NOT EXISTS
2579 (Select 'valid auth code'
2580 From oe_payments op,
2581 iby_trxn_ext_auths_v ite
2582 Where op.header_id = h.header_id
2583 And op.trxn_extension_id = ite.trxn_extension_id
2584 And authorization_status = 0
2585 And effective_auth_amount > 0)
2586 )
2587 )
2588 AND L.HEADER_ID = H.HEADER_ID
2589 AND L.LINE_ID = P.LINE_ID
2590 AND NVL(L.INVOICED_QUANTITY,0) = 0
2591 AND L.OPEN_FLAG = 'Y'
2592 AND L.LINE_CATEGORY_CODE = 'ORDER'
2593 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2594 <= l_shipping_horizon
2595 AND EXISTS (SELECT 1
2596 FROM OE_ORDER_HOLDS OH
2597 WHERE H.HEADER_ID = OH.HEADER_ID
2598 AND OH.HOLD_RELEASE_ID IS NULL);
2599
2600 Cursor current_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.HEADER_ID = p_header_rec.header_id
2604 AND H.HEADER_ID = P.HEADER_ID
2605 AND L.HEADER_ID = H.HEADER_ID
2606 AND L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2607 AND L.LINE_ID = P.LINE_ID
2608 AND NVL(L.INVOICED_QUANTITY,0) = 0
2609 AND L.OPEN_FLAG ='Y'
2610 AND L.LINE_CATEGORY_CODE ='ORDER'
2611 /*
2612 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2613 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
2614 */
2615 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2616 OR (h.payment_type_code = 'CREDIT_CARD'
2617 AND NOT EXISTS
2618 (Select 'valid auth code'
2619 From oe_payments op,
2620 iby_trxn_ext_auths_v ite
2621 Where op.header_id = h.header_id
2622 And op.trxn_extension_id = ite.trxn_extension_id
2623 And authorization_status = 0
2624 And effective_auth_amount > 0)
2625 )
2626 );
2627
2628 Cursor current_order(p_include_tax VARCHAR2) IS
2629 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
2630 *NVL(unit_selling_price,0)) + decode(p_include_tax,'Y', NVL(tax_value,0), 0))
2631 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2632 WHERE H.HEADER_ID = p_header_rec.header_id
2633 AND H.HEADER_ID = L.HEADER_ID
2634 AND L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2635 -- AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2636 -- <= to_date(l_shipping_horizon, 'DD-MON-YY')
2637 AND NVL(L.INVOICED_QUANTITY,0) = 0
2638 AND L.OPEN_FLAG ='Y'
2639 AND L.LINE_CATEGORY_CODE ='ORDER'
2640 /*
2641 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2642 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
2643 */
2644 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2645 OR (h.payment_type_code = 'CREDIT_CARD'
2646 AND NOT EXISTS
2647 (Select 'valid auth code'
2648 From oe_payments op,
2649 iby_trxn_ext_auths_v ite
2650 Where op.header_id = h.header_id
2651 And op.trxn_extension_id = ite.trxn_extension_id
2652 And authorization_status = 0
2653 And effective_auth_amount > 0)
2654 )
2655 );
2656
2657 -- CURSORS FOR SITE - LEVEL EXPOSURE CALCULATIONS
2658 Cursor site_ar_balance IS
2659 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
2660 FROM AR_PAYMENT_SCHEDULES
2661 WHERE CUSTOMER_SITE_USE_ID = p_invoice_to_org_id
2662 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
2663 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y';
2664
2665 Cursor site_ar_balance_in_ar_days IS
2666 SELECT NVL(SUM(AMOUNT_DUE_REMAINING), 0)
2667 FROM AR_PAYMENT_SCHEDULES
2668 WHERE CUSTOMER_SITE_USE_ID = p_invoice_to_org_id
2669 AND INVOICE_CURRENCY_CODE = p_header_rec.transactional_curr_code
2670 AND NVL(RECEIPT_CONFIRMED_FLAG, 'Y') = 'Y'
2671 AND SYSDATE - TRX_DATE > l_open_ar_days;
2672
2673 Cursor site_pay_risk IS
2674 SELECT NVL(SUM(CRH.AMOUNT),0)
2675 FROM AR_CASH_RECEIPT_HISTORY CRH
2676 , AR_CASH_RECEIPTS CR
2677 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
2678 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
2679 AND CRH.CURRENT_RECORD_FLAG = 'Y'
2680 AND CRH.STATUS <>
2681 DECODE(CRH.FACTOR_FLAG,'Y',
2682 'RISK_ELIMINATED','CLEARED')
2683 AND CRH.STATUS <> 'REVERSED'
2684 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
2685 AND CR.PAY_FROM_CUSTOMER = p_customer_id
2686 AND CR.CUSTOMER_SITE_USE_ID = p_invoice_to_org_id;
2687
2688 Cursor site_pay_risk_in_ar_days IS
2689 SELECT NVL(SUM(CRH.AMOUNT),0)
2690 FROM AR_CASH_RECEIPT_HISTORY CRH
2691 , AR_CASH_RECEIPTS CR
2692 WHERE CRH.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
2693 AND NVL(CR.CONFIRMED_FLAG,'Y') = 'Y'
2694 AND CRH.CURRENT_RECORD_FLAG = 'Y'
2695 AND CRH.STATUS <>
2696 DECODE(CRH.FACTOR_FLAG,'Y',
2697 'RISK_ELIMINATED','CLEARED')
2698 AND CRH.STATUS <> 'REVERSED'
2699 AND CR.CURRENCY_CODE = p_header_rec.transactional_curr_code
2700 AND CR.PAY_FROM_CUSTOMER = p_customer_id
2701 AND CR.CUSTOMER_SITE_USE_ID = p_invoice_to_org_id
2702 AND SYSDATE - TRX_DATE > l_open_ar_days;
2703
2704 /* additional task : modified the following 2 SITE cursors
2705 1. excluded unbooked orders
2706 2. excluded orders authorized by credit card
2707 3. Excludes the current order
2708 */
2709
2710 Cursor site_uninvoiced_orders(p_include_tax VARCHAR2) IS
2711 SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0) )
2712 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', nvl(tax_value,0), 0))
2713 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2714 WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2715 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2716 AND H.HEADER_ID = L.HEADER_ID
2717 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2718 <= l_shipping_horizon
2719 AND NVL(L.INVOICED_QUANTITY,0) = 0
2720 AND L.OPEN_FLAG ='Y'
2721 AND L.LINE_CATEGORY_CODE ='ORDER'
2722 AND H.BOOKED_FLAG = 'Y'
2723 AND H.HEADER_ID <> p_header_rec.header_id
2724 /*
2725 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2726 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
2727 */
2728 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2729 OR (h.payment_type_code = 'CREDIT_CARD'
2730 AND NOT EXISTS
2731 (Select 'valid auth code'
2732 From oe_payments op,
2733 iby_trxn_ext_auths_v ite
2734 Where op.header_id = h.header_id
2735 And op.trxn_extension_id = ite.trxn_extension_id
2736 And authorization_status = 0
2737 And effective_auth_amount > 0)
2738 )
2739 );
2740
2741 Cursor site_orders_on_hold(p_include_tax VARCHAR2) IS
2742
2743 SELECT /* MOAC_SQL_CHANGE */ SUM(((NVL(ordered_quantity,0))
2744 *NVL(unit_selling_price,0)) + decode(p_include_tax, 'Y', NVL(tax_value,0),0))
2745 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2746 WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2747 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2748 AND H.HEADER_ID = L.HEADER_ID
2749 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2750 <= l_shipping_horizon
2751 AND NVL(L.INVOICED_QUANTITY,0) = 0
2752 AND L.OPEN_FLAG ='Y'
2753 AND L.LINE_CATEGORY_CODE ='ORDER'
2754 AND H.BOOKED_FLAG = 'Y'
2755 AND H.HEADER_ID <> p_header_rec.header_id
2756 /*
2757 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2758 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
2759 */
2760 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2761 OR (h.payment_type_code = 'CREDIT_CARD'
2762 AND NOT EXISTS
2763 (Select 'valid auth code'
2764 From oe_payments op,
2765 iby_trxn_ext_auths_v ite
2766 Where op.header_id = h.header_id
2767 And op.trxn_extension_id = ite.trxn_extension_id
2768 And authorization_status = 0
2769 And effective_auth_amount > 0)
2770 )
2771 )
2772 AND EXISTS (SELECT 1
2773 FROM OE_ORDER_HOLDS OH
2774 WHERE H.HEADER_ID = OH.HEADER_ID
2775 AND OH.HOLD_RELEASE_ID IS NULL
2776 );
2777
2778 Cursor site_commitment_total IS
2779 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
2780 FROM OE_PAYMENTS P, OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2781 WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2782 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2783 AND H.HEADER_ID = P.HEADER_ID
2784 AND L.HEADER_ID = H.HEADER_ID
2785 AND L.LINE_ID = P.LINE_ID
2786 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2787 <= l_shipping_horizon
2788 AND NVL(L.INVOICED_QUANTITY,0) = 0
2789 AND L.OPEN_FLAG = 'Y'
2790 AND L.LINE_CATEGORY_CODE = 'ORDER'
2791 AND H.BOOKED_FLAG = 'Y'
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
2809 Cursor site_on_hold_commitment_total IS
2810 SELECT /* MOAC_SQL_CHANGE */ NVL(SUM(P.commitment_applied_amount), 0)
2811 FROM OE_PAYMENTS P, OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2812 WHERE L.INVOICE_TO_ORG_ID = p_invoice_to_org_id
2813 AND H.TRANSACTIONAL_CURR_CODE = p_header_rec.transactional_curr_code
2814 AND H.HEADER_ID = P.HEADER_ID
2815 AND L.HEADER_ID = H.HEADER_ID
2816 AND L.LINE_ID = P.LINE_ID
2817 AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2818 <= l_shipping_horizon
2819 AND NVL(L.INVOICED_QUANTITY,0) = 0
2820 AND L.OPEN_FLAG = 'Y'
2821 AND L.LINE_CATEGORY_CODE = 'ORDER'
2822 AND H.BOOKED_FLAG = 'Y'
2823 AND H.HEADER_ID <> p_header_rec.header_id
2824 /*
2825 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2826 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate
2827 */
2828 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2829 OR (h.payment_type_code = 'CREDIT_CARD'
2830 AND NOT EXISTS
2831 (Select 'valid auth code'
2832 From oe_payments op,
2833 iby_trxn_ext_auths_v ite
2834 Where op.header_id = h.header_id
2835 And op.trxn_extension_id = ite.trxn_extension_id
2836 And authorization_status = 0
2837 And effective_auth_amount > 0)
2838 )
2839 )
2840 AND EXISTS (SELECT 1
2841 FROM OE_ORDER_HOLDS OH
2842 WHERE H.HEADER_ID = OH.HEADER_ID
2843 AND OH.HOLD_RELEASE_ID IS NULL);
2844
2845 /* additional task- Current Order - */
2846
2847 Cursor site_current_order(p_include_tax VARCHAR2) IS
2848 SELECT /* MOAC_SQL_CHANGE */ SUM((NVL(ordered_quantity,0)
2849 *NVL(unit_selling_price,0)) + decode(p_include_tax,'Y', NVL(tax_value,0), 0))
2850 FROM OE_ORDER_LINES_ALL L, OE_ORDER_HEADERS H
2851 WHERE H.HEADER_ID = p_header_rec.header_id
2852 AND H.HEADER_ID = L.HEADER_ID
2853 AND L.invoice_to_org_id = p_invoice_to_org_id
2854 -- AND NVL(L.SCHEDULE_SHIP_DATE,NVL(L.REQUEST_DATE,H.REQUEST_DATE))
2855 -- <= to_date(l_shipping_horizon, 'DD-MON-YY')
2856 AND NVL(L.INVOICED_QUANTITY,0) = 0
2857 AND L.OPEN_FLAG ='Y'
2858 AND L.LINE_CATEGORY_CODE ='ORDER'
2859 /*
2860 AND decode(H.CREDIT_CARD_APPROVAL_CODE, NULL, (sysdate -1),
2861 (H.CREDIT_CARD_APPROVAL_DATE + l_est_valid_days)) < sysdate;
2862 */
2863 AND (nvl(h.payment_type_code, 'NULL') <> 'CREDIT_CARD'
2864 OR (h.payment_type_code = 'CREDIT_CARD'
2865 AND NOT EXISTS
2866 (Select 'valid auth code'
2867 From oe_payments op,
2868 iby_trxn_ext_auths_v ite
2869 Where op.header_id = h.header_id
2870 And op.trxn_extension_id = ite.trxn_extension_id
2871 And authorization_status = 0
2872 And effective_auth_amount > 0)
2873 )
2874 );
2875
2876
2877 BEGIN
2878
2879 -- Set the default behaviour to pass credit check exposure
2880 p_return_status := FND_API.G_RET_STS_SUCCESS;
2881 -- Read the Credit rule and determine the inclusions and exclusions
2882 -- for exposure
2883
2884 -- Include Amounts on Receivables if within the AR horizon.
2885 -- Add the OPEN_AR_DAYS
2886 -- to todays date to determine the AR Horizon. Use this to compare the
2887 -- Due date on
2888 -- the Invoice. If the due_date is greater than system date by less than
2889 -- open_ar_days
2890 -- In the R10SC/R11, if the trx_date/invoice_date is earlier than system date
2891 -- by open_ar_days, those
2892 -- orders only are considered. So the check was WHERE SYSDATE - TRX_DATE >
2893 -- OPEN_AR_DAYS
2894 -- Include amounts on Order Backlog if within the shipping horizon.
2895 -- Add the Shipping Horizon
2896 -- to todays date to determin the shipping horizon. Use this to compare
2897 -- the scheduled ship date on the order line.
2898 -- As in R10SC/R11, if the schedule_date (or when this field is null,
2899 -- request_date) is within the no. of shipping_interval days of the current
2900 -- date, include only those amounts for calculating uninvoiced orders total.
2901
2902 oe_debug_pub.ADD('In Line Level Check Exposure');
2903 OPEN credit_check_rule;
2904 FETCH credit_check_rule INTO l_open_ar_balance_flag
2905 , l_open_ar_days
2906 , l_uninvoiced_orders_flag
2907 , l_orders_on_hold_flag
2908 , l_include_tax_flag
2909 , l_shipping_horizon
2910 , l_include_risk_flag;
2911 CLOSE credit_check_rule;
2912
2913 oe_debug_pub.ADD('Credit Check Rule is as follows :');
2914 oe_debug_pub.ADD('Open AR Balance Flag ='||l_open_ar_balance_flag);
2915 oe_debug_pub.ADD('Open AR days ='||to_char(l_open_ar_days));
2916 oe_debug_pub.ADD('Uninvoiced Orders Flag ='||l_uninvoiced_orders_flag);
2917 oe_debug_pub.ADD('Orders On Hold flag ='||l_orders_on_hold_flag);
2918 oe_debug_pub.ADD('Include Tax Flag ='||l_include_tax_flag);
2919 oe_debug_pub.ADD('Shipping Horizon days ='||to_char(l_shipping_horizon, 'DD-MON-YYYY'));
2920 oe_debug_pub.ADD('Include Risk flag ='||l_include_risk_flag);
2921
2922 l_est_valid_days :=
2923 to_number( nvl(fnd_profile.value('ONT_EST_AUTH_VALID_DAYS'), '0'));
2924 oe_debug_pub.ADD('Estimated Valid Days ='||to_char(l_est_valid_days));
2925
2926
2927 IF p_credit_level = 'CUSTOMER' THEN
2928 -- Retrieving exposure at CUSTOMER level
2929
2930 IF l_open_ar_balance_flag = 'Y' THEN
2931 -- Find Accounts Receivable Exposure
2932 IF l_open_ar_days IS NULL THEN
2933 OPEN ar_balance;
2934 FETCH ar_balance INTO l_total_from_ar;
2935 IF ar_balance%notfound THEN
2936 l_total_from_ar := 0;
2937 END IF;
2938 CLOSE ar_balance;
2939 ELSE
2940 OPEN ar_balance_in_ar_days;
2941 FETCH ar_balance_in_ar_days INTO l_total_from_ar;
2942 IF ar_balance_in_ar_days%notfound THEN
2943 l_total_from_ar := 0;
2944 END IF;
2945 CLOSE ar_balance_in_ar_days;
2946 END IF; -- If l_open_ar_days is null
2947
2948 oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
2949
2950 /*********************************************************************
2951 * If the include payments at risk flag is set to yes, Update the *
2952 * exposure by payments that are not thought to be collectable *
2953 * These payments are in the cash receipts history *
2954 *********************************************************************/
2955
2956 IF l_include_risk_flag = 'Y' THEN
2957
2958 IF l_open_ar_days IS NULL THEN
2959 OPEN pay_risk;
2960 FETCH pay_risk INTO l_payments_at_risk;
2961 IF pay_risk%notfound THEN
2962 l_payments_at_risk := 0;
2963 END IF;
2964 CLOSE pay_risk;
2965 ELSE
2966 OPEN pay_risk_in_ar_days;
2967 FETCH pay_risk_in_ar_days INTO l_payments_at_risk;
2968 IF pay_risk_in_ar_days%notfound THEN
2969 l_payments_at_risk := 0;
2970 END IF;
2971 CLOSE pay_risk_in_ar_days;
2972 END IF; -- If l_open_ar_days is null
2973
2974 oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
2975
2976 END IF; -- l_include_risk_flag (Include Payments at Risk)
2977
2978 -- Update the total exposure value.
2979 l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
2980 oe_debug_pub.ADD('Accounts Receivables Exposure ='||
2981 to_char(l_total_exposure));
2982
2983 END IF; -- l_open_ar_balance_flag (checking accounts receivables exposure)
2984
2985 -- Depending on the include_tax_flag value tax will be included or
2986 -- excluded from credit exposure calculation
2987
2988 IF l_uninvoiced_orders_flag = 'Y' THEN
2989
2990 BEGIN
2991 OPEN uninvoiced_orders(l_include_tax_flag);
2992 FETCH uninvoiced_orders INTO l_total_on_order;
2993 IF uninvoiced_orders%notfound THEN
2994 oe_debug_pub.ADD('not found any uninvoiced orders');
2995 l_total_on_order := 0;
2996 END IF;
2997 CLOSE uninvoiced_orders;
2998 /* WHEN others THEN
2999 oe_debug_pub.ADD('not found');*/
3000 END;
3001
3002 -- Now update the total exposure value to include the Order Backlog Value
3003
3004 oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
3005 l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
3006
3007 oe_debug_pub.ADD('Exposure after taking care of uninvoiced orders='
3008 || to_char(l_total_exposure));
3009
3010 -- Next check if we should be excluding orders that are already on hold
3011 -- from the calculation of Total exposure
3012
3013 IF l_orders_on_hold_flag = 'N' THEN
3014 -- Find the value of all orders that are the subject of an order hold
3015 -- at either the header or line level.
3016
3017 OPEN orders_on_hold(l_include_tax_flag);
3018 FETCH orders_on_hold INTO l_total_on_hold;
3019 IF orders_on_hold%notfound THEN
3020 l_total_on_hold := 0;
3021 END IF;
3022 CLOSE orders_on_hold;
3023
3024 -- Update the total exposure value to EXCLUDE the value of orders on hold
3025 oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
3026
3027 l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
3028
3029 oe_debug_pub.ADD('Total exposure after taking care of Hold ='||
3030 to_char(l_total_exposure));
3031 END IF; -- l_orders_on_hold_flag
3032
3033 -- Check Commitment Total if Commitment Sequencing "On"
3034 IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
3035
3036 OPEN commitment_total;
3037 FETCH commitment_total INTO l_total_commitment;
3038 IF commitment_total%notfound THEN
3039 l_total_commitment := 0;
3040 END IF;
3041 CLOSE commitment_total;
3042
3043 oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
3044
3045 -- If orders on hold are to be excluded then find out
3046 -- the commitment amount associated to orders on hold
3047 IF l_orders_on_hold_flag = 'N' THEN
3048 OPEN on_hold_commitment_total;
3049 FETCH on_hold_commitment_total INTO l_on_hold_commitment;
3050 IF on_hold_commitment_total%notfound THEN
3051 l_on_hold_commitment := 0;
3052 END IF;
3053
3054 CLOSE on_hold_commitment_total;
3055
3056 oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
3057 END IF;
3058
3059 OPEN current_commitment_total;
3060 FETCH current_commitment_total INTO l_current_commitment;
3061 IF current_commitment_total%notfound THEN
3062 l_current_commitment := 0;
3063 END IF;
3064 CLOSE current_commitment_total;
3065
3066 oe_debug_pub.ADD('Current Order Commitment Amount: ' || l_current_commitment);
3067
3068 l_total_commitment := l_total_commitment + l_current_commitment - nvl(l_on_hold_commitment, 0);
3069
3070 oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
3071
3072 -- Now update the total exposure value to EXCLUDE already applied Commitments.
3073 l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
3074
3075 oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
3076
3077 END IF; -- Commitment Sequencing
3078
3079 -- ADD Current Order value to the calculated exposure */
3080
3081 OPEN current_order(l_include_tax_flag);
3082 FETCH current_order INTO l_current_order;
3083 IF current_order%notfound THEN
3084 l_current_order := 0;
3085 END IF;
3086 CLOSE current_order;
3087
3088 l_total_exposure := l_total_exposure + NVL(l_current_order,0);
3089 oe_debug_pub.ADD('Total exposure after Current Order (CUSTOMER)='
3090 ||to_char(l_total_exposure));
3091
3092 END IF; -- uninvoiced order flag
3093
3094 ----------------
3095 -- SITE LEVEL --
3096 ----------------
3097 ELSE -- Retrieving exposure at SITE level
3098
3099 IF l_open_ar_balance_flag = 'Y' THEN
3100
3101 -- Find Accounts Receivable Exposure
3102 IF l_open_ar_days IS NULL THEN
3103 OPEN site_ar_balance;
3104 FETCH site_ar_balance INTO l_total_from_ar;
3105 IF site_ar_balance%notfound THEN
3106 l_total_from_ar := 0;
3107 END IF;
3108 CLOSE site_ar_balance;
3109 ELSE
3110 OPEN site_ar_balance_in_ar_days;
3111 FETCH site_ar_balance_in_ar_days INTO l_total_from_ar;
3112 IF site_ar_balance_in_ar_days%notfound THEN
3113 l_total_from_ar := 0;
3114 END IF;
3115 CLOSE site_ar_balance_in_ar_days;
3116 END IF; -- If open_ar_days is null
3117
3118 oe_debug_pub.ADD('Open Receivables Balance: '||l_total_from_ar);
3119
3120 -- If the include payments at risk flag is set to yes
3121 -- Update the exposure by payments that are not thought to be collectable
3122 -- These payments are in the cash receipts history
3123
3124 IF l_include_risk_flag = 'Y' THEN
3125
3126 IF l_open_ar_days IS NULL THEN
3127 OPEN site_pay_risk;
3128 FETCH site_pay_risk INTO l_payments_at_risk;
3129 IF site_pay_risk%notfound THEN
3130 l_payments_at_risk := 0;
3131 END IF;
3132 CLOSE site_pay_risk;
3133 ELSE
3134 OPEN site_pay_risk_in_ar_days;
3135 FETCH site_pay_risk_in_ar_days INTO l_payments_at_risk;
3136 IF site_pay_risk_in_ar_days%notfound THEN
3137 l_payments_at_risk := 0;
3138 END IF;
3139 CLOSE site_pay_risk_in_ar_days;
3140 END IF; -- If open_ar_days is null
3141
3142 oe_debug_pub.ADD('Payments At Risk: '||l_payments_at_risk);
3143
3144 END IF; -- l_include_risk_flag (Include Payments at Risk)
3145
3146 -- Now update the total exposure value.
3147 l_total_exposure := nvl(l_total_from_ar,0) + nvl(l_payments_at_risk,0);
3148 oe_debug_pub.ADD('Exposure from a/c receivables ='||
3149 to_char(l_total_exposure));
3150
3151 END IF; -- l_open_ar_balance_flag(checking accounts receivables exposure)
3152
3153
3154 -- If the include_uninvoiced_orders is set to yes,depending on the
3155 -- value of include_tax_flag, tax valuw will be included in the
3156 -- calculation of total_exposure.
3157
3158 IF l_uninvoiced_orders_flag = 'Y' THEN
3159
3160 OPEN site_uninvoiced_orders(l_include_tax_flag);
3161 FETCH site_uninvoiced_orders INTO l_total_on_order;
3162 IF site_uninvoiced_orders%notfound THEN
3163 l_total_on_order := 0;
3164 END IF;
3165 CLOSE site_uninvoiced_orders;
3166
3167 -- Update the total exposure value to include the Order Backlog Value
3168
3169 oe_debug_pub.ADD('Total amt. of uninvoiced orders: '|| l_total_on_order);
3170 l_total_exposure := l_total_exposure + nvl(l_total_on_order,0);
3171
3172 oe_debug_pub.ADD('Exposure after uninvoiced orders ='||
3173 to_char(l_total_exposure));
3174
3175 -- Next check if we should be excluding orders that are already on hold
3176 -- from the calculation of Total exposure
3177
3178 IF l_orders_on_hold_flag = 'N' THEN
3179 -- Find the value of all orders that are the subject of an order hold
3180 -- at either the header or line level.
3181
3182 OPEN site_orders_on_hold(l_include_tax_flag);
3183 FETCH site_orders_on_hold INTO l_total_on_hold;
3184 IF site_orders_on_hold%notfound THEN
3185 l_total_on_hold := 0;
3186 END IF;
3187 CLOSE site_orders_on_hold;
3188
3189 -- Update the total exposure value to EXCLUDE the value of
3190 -- orders on hold
3191 oe_debug_pub.ADD('Total amount on hold:' || l_total_on_hold);
3192 l_total_exposure := l_total_exposure - nvl(l_total_on_hold,0);
3193
3194 oe_debug_pub.ADD('Total Exposure after taking care of Hold ='||
3195 to_char(l_total_exposure));
3196
3197 END IF; -- check orders on hold
3198
3199 -- Check Commitment Total if Commitment Sequencing "On"
3200 IF OE_Commitment_PVT.do_Commitment_Sequencing THEN
3201
3202 OPEN site_commitment_total;
3203 FETCH site_commitment_total INTO l_total_commitment;
3204 IF site_commitment_total%notfound THEN
3205 l_total_commitment := 0;
3206 END IF;
3207 CLOSE site_commitment_total;
3208
3209 oe_debug_pub.ADD('Commitment Amount: ' || l_total_commitment);
3210
3211 -- If orders on hold are to be excluded then find out
3212 -- the commitment amount associated to orders on hold
3213 IF l_orders_on_hold_flag = 'N' THEN
3214 OPEN site_on_hold_commitment_total;
3215 FETCH site_on_hold_commitment_total INTO l_on_hold_commitment;
3216 IF site_on_hold_commitment_total%notfound THEN
3217 l_on_hold_commitment := 0;
3218 END IF;
3219
3220 CLOSE site_on_hold_commitment_total;
3221
3222 oe_debug_pub.ADD('On Hold Commitment Amount: ' || l_on_hold_commitment);
3223 END IF;
3224
3225 l_total_commitment := l_total_commitment - nvl(l_on_hold_commitment, 0);
3226
3227 -- Now update the total exposure value to EXCLUDE already applied Commitments.
3228 oe_debug_pub.ADD('Total Commitment Amount: ' || l_total_commitment);
3229
3230 l_total_exposure := l_total_exposure - nvl(l_total_commitment,0);
3231
3232 oe_debug_pub.ADD('Total exposure after taking care of Commitments = '||to_char(l_total_exposure));
3233
3234 END IF; -- Commitment Sequencing
3235
3236 OPEN site_current_order(l_include_tax_flag);
3237 FETCH site_current_order INTO l_current_order;
3238 IF site_current_order%notfound THEN
3239 l_current_order := 0;
3240 END IF;
3241 CLOSE site_current_order;
3242
3243 l_total_exposure := l_total_exposure + NVL(l_current_order,0);
3244 oe_debug_pub.ADD('Total exposure after including Current Order(SITE)='
3245 ||to_char(l_total_exposure));
3246
3247 END IF; -- l_uninvoiced_orders_flag
3248
3249 END IF; -- credit exposure at SITE level or CUSTOMER level
3250
3251 oe_debug_pub.ADD('Line Level: OUTPUT total exposure: '|| l_total_exposure);
3252 p_total_exposure := l_total_exposure;
3253
3254
3255 EXCEPTION
3256 WHEN others THEN
3257 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3258 OE_MSG_PUB.Add_Exc_Msg
3259 ( G_PKG_NAME
3260 , 'Check_Exposure'
3261 );
3262 END IF;
3263 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3264 RAISE ;
3265
3266 END Check_Exposure_Line;
3267
3268 END OE_Credit_PUB;
[Home] [Help]