[Home] [Help]
PACKAGE BODY: APPS.OE_CREDIT_CHECK_LINES_PVT
Source
1 PACKAGE BODY OE_credit_check_lines_PVT AS
2 -- $Header: OEXVCRLB.pls 120.35.12020000.5 2012/09/13 10:29:21 sujithku ship $
3 --+=======================================================================+
4 --| Copyright (c) 2001 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| |
9 --| FILENAME |
10 --| OEXVCRLB.pls |
11 --| DESCRIPTION |
12 --| Body of package OE_credit_check_lines_PVT. It contains the |
13 --| and functions used to perform line level credit checking according |
14 --| to a given credit rule. It will check the amount against the credit|
15 --| limits set at the customer/site. The result returned will be |
16 --| 'PASS' if it is within the customer/site limit and |
17 --| 'FAIL' if it exceeds the limits. |
18 --| |
19 --| HISTORY |
20 --| May-21-2001 rajkrish created |
21 --| Nov-07-2001 Update Comments |
22 --| Jan-29-2002 Multi org changes |
23 --| ontdev => 115.20 2001/11/07 22:55:37 |
24 --| Mar-16-2002 tsimmond added changes into |
25 --| Check_manual_released_holds |
26 --| Mar-25-2002 tsimmond changed '>' to '>=" for manual holds |
27 --| Apr-26-2002 rajkrish BUG 2338145 |
28 --| Jun-11-2002 rajkrish 2412678 |
29 --| Sep-01-2002 tsimmond added code for FPI, submit AR |
30 --| Credit Management Review |
31 --| Nov-17-2002 rajkrish FPI party level |
32 --| Dec-06-2002 vto Added NOCOPY to OUT variables |
33 --| Jan-07-2003 tsimmond changed parameters values in Submit |
34 --| Credit Review |
35 --| Feb-07-2003 Bug 2787722 |
36 --| Mar-31-2003 vto 2846473,2878410. Handle new global for line count|
37 --| Apr-01-2003 vto 2885044,2853800. Modify call to Check_Holds to |
38 --| pass in item_type and activity_name globals |
39 --| Apr-09-2003 tsimmond 2888032, changes in Submit Credit Review |
40 --| May-15-2003 vto 2894424, 2971689. New cc calling action: |
41 --| AUTO HOLD, AUTO RELEASE. |
42 --| Obsolete calling action: AUTO |
43 --| bug2948597 rajkrish |
44 --| Aug-22-2003 vto Modified to support partial payments and |
45 --| added create_by=1 for release hold source to ID|
46 --| system created/release holds (bug 3042838) |
47 --| Jan-15-2004 vto 3364726. G_crmgmt_installed instead of = TRUE |
48 --| Mar-10-2004 aksingh 3462295. Added api Update_Comments_And_Commit |
49 --| Jul-12-2001 sujithku 12651163. Check_Other_Credit_Limits modified |
50 --| 20-Jan-2012 Kadiraju Bug#13768161 |
51 --| 19-Mar-2012 Kadiraju Bug#13939240 -->FP of 13822905
52 --| 07-Jul-2012 slagiset Bug#14305856 |
53 --|=======================================================================+
54
55 --------------------
56 -- TYPE DECLARATIONS
57 --------------------
58
59 ------------
60 -- CONSTANTS
61 ------------
62 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_credit_check_lines_PVT';
63
64 ---------------------------
65 -- PRIVATE GLOBAL VARIABLES
66 ---------------------------
67 G_debug_flag VARCHAR2(1) := NVL(OE_CREDIT_CHECK_UTIL.check_debug_flag,'N');
68 G_result_out VARCHAR2(10) := 'PASS' ;
69 G_release_status VARCHAR2(30) := 'NO' ;
70 G_hdr_hold_released VARCHAR2(1) := 'N' ;
71 G_order NUMBER;
72
73 ------Global variables for Submiting AR Credit Review -------new (FPI)
74
75 G_total_site_exposure NUMBER;
76 G_limit_currency VARCHAR2(15);
77 G_cc_limit_used VARCHAR2(80);
78
79 -- bug 5907331
80 G_credit_limit_entity_id NUMBER;
81
82 g_hold_reason_rec AR_CMGT_CREDIT_REQUEST_API.hold_reason_rec_type
83 := AR_CMGT_CREDIT_REQUEST_API.hold_reason_rec_type(NULL); --ER8880886
84
85 ---------------------------
86 -- PROCEDURES AND FUNCTIONS
87 ---------------------------
88 ----------------------------------------------------
89 /* Bug 7673312 Function added to find the top_model_line_id incase of SMC ,
90 if that is non smc it will return the same line id. */
91 Function top_model_line_id_smc(p_line_id IN NUMBER)
92 RETURN NUMBER
93 IS
94 l_top_model_line_id NUMBER;
95
96 BEGIN
97 Select top_model_line_id
98 into l_top_model_line_id
99 from oe_order_lines_all
100 where line_id=p_line_id
101 and top_model_line_id IS NOT NULL
102 and ship_model_complete_flag='Y';
103
104 oe_debug_pub.add('Line Id : '||p_line_id||' passed is a part of SMC PTO line id: '||l_top_model_line_id);
105
106 RETURN(l_top_model_line_id);
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 oe_debug_pub.add('OTHERS:Line Id : '||p_line_id);
111 RETURN(p_line_id);
112 END top_model_line_id_smc;
113 -- Bug 7673312
114
115 ----------------------------------------------------
116 PROCEDURE Apply_hold_and_commit
117 ( p_hold_source_rec IN
118 OE_HOLDS_PVT.Hold_Source_Rec_Type
119 , x_msg_count OUT NOCOPY NUMBER
120 , x_msg_data OUT NOCOPY VARCHAR2
121 , x_return_status OUT NOCOPY VARCHAR2
122 )
123 IS
124
125 PRAGMA AUTONOMOUS_TRANSACTION;
126
127
128 BEGIN
129
130 OE_DEBUG_PUB.ADD(' OEXVCRLB: In Apply_hold_and_commit ');
131 OE_DEBUG_PUB.ADD(' Call OE_Holds_PUB.Apply_Holds ');
132
133
134 OE_Holds_PUB.Apply_Holds
135 ( p_api_version => 1.0
136 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
137 , p_hold_source_rec => p_hold_source_rec
138 , x_msg_count => x_msg_count
139 , x_msg_data => x_msg_data
140 , x_return_status => x_return_status
141 );
142
143 OE_DEBUG_PUB.ADD(' Out OE_Holds_PUB.Apply_Holds ');
144 OE_DEBUG_PUB.ADD(' x_return_status => '|| x_return_status );
145 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
146 RAISE FND_API.G_EXC_ERROR;
147 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
148 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
149 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
150
151 OE_DEBUG_PUB.ADD(' Holds success ');
152 OE_DEBUG_PUB.ADD(' About to Issue COMMIT');
153
154 COMMIT;
155
156 OE_DEBUG_PUB.ADD(' AFter Issue COMMIT');
157
158 END IF;
159
160 OE_DEBUG_PUB.ADD(' OEXVCRLB: OUT Apply_hold_and_commit ');
161
162 EXCEPTION
163 WHEN OTHERS THEN
164 rollback;
165 OE_DEBUG_PUB.ADD(' Error in Apply_hold_and_commit ' );
166 OE_DEBUG_PUB.ADD(' SQLERRM: '|| SQLERRM );
167 OE_MSG_PUB.Add_Exc_Msg
168 ( G_PKG_NAME
169 , 'Apply_hold_and_commit'
170 );
171
172 RAISE;
173
174 END Apply_hold_and_commit ;
175
176
177 ----------------------------------------------------
178 -- Procedure to Update Hold Comments And Commit --
179 ----------------------------------------------------
180 PROCEDURE Update_Comments_And_Commit
181 ( p_hold_source_rec IN OE_HOLDS_PVT.Hold_Source_Rec_Type
182 , x_msg_count OUT NOCOPY NUMBER
183 , x_msg_data OUT NOCOPY VARCHAR2
184 , x_return_status OUT NOCOPY VARCHAR2
185 )
186 IS
187 PRAGMA AUTONOMOUS_TRANSACTION;
188 BEGIN
189
190 IF G_debug_flag = 'Y'
191 THEN
192 OE_DEBUG_PUB.ADD('OEXVCRLB: Entering Update_Comments_And_Commit');
193 OE_DEBUG_PUB.ADD('OEXVCRLB: Before OE_Holds_PUB.Update_Hold_Comments');
194 END IF;
195
196 OE_Holds_PUB.Update_Hold_comments
197 ( p_hold_source_rec => p_hold_source_rec
198 , x_msg_count => x_msg_count
199 , x_msg_data => x_msg_data
200 , x_return_status => x_return_status
201 );
202
203 IF G_debug_flag = 'Y'
204 THEN
205 OE_DEBUG_PUB.ADD('OEXVCRLB: After OE_Holds_PUB.Update_Hold_Comments Status '
206 || x_return_status);
207 END IF;
208
209 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
210 RAISE FND_API.G_EXC_ERROR;
211 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
214 IF G_debug_flag = 'Y'
215 THEN
216 OE_DEBUG_PUB.ADD('OEXVCRLB: Update Hold Comment Success, Issue COMMIT');
217 END IF;
218
219 COMMIT;
220
221 IF G_debug_flag = 'Y'
222 THEN
223 OE_DEBUG_PUB.ADD('OEXVCRLB: After Issuing COMMIT');
224 END IF;
225 END IF;
226
227 IF G_debug_flag = 'Y'
228 THEN
229 OE_DEBUG_PUB.ADD(' OEXVCRLB: Exiting Update_Comments_And_Commit');
230 END IF;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 rollback;
235 OE_DEBUG_PUB.ADD('OEXVCRLB: Error in Update_Comments_And_Commit' );
236 OE_DEBUG_PUB.ADD('SQLERRM: '|| SQLERRM );
237 OE_MSG_PUB.Add_Exc_Msg
238 ( G_PKG_NAME
239 , 'Update_Comments_And_Commit'
240 );
241
242 RAISE;
243
244 END Update_Comments_And_Commit ;
245
246 --------------------------------------------------
247 -- Build the holds table to store the different
248 -- type of holds on the order lines for processing
249 -- during the credit check cycle.
250 --------------------------------------------------
251
252 PROCEDURE Create_Holds_Table
253 ( p_header_id IN NUMBER
254 , p_site_use_id IN NUMBER
255 , x_holds_table OUT NOCOPY Line_Holds_Tbl_Rectype
256 )
257 IS
258
259 l_hold_line_seq VARCHAR2(1) := NVL(OE_SYS_PARAMETERS.VALUE('OE_HOLD_LINE_SEQUENCE'),1); --ER 6135714
260
261 --ER 6135714 CURSOR billto_lines_csr IS
262 CURSOR billto_lines_csr_1 IS --ER 6135714
263 --ER 6135714 SELECT /* MOAC_SQL_NO_CHANGE */ l.line_id, l.line_number
264 SELECT /* MOAC_SQL_NO_CHANGE */ l.line_id, l.line_number,0 line_total --ER 6135714
265 FROM oe_order_lines_all l,
266 oe_order_headers_all h,
267 ra_terms_b t
268 WHERE l.invoice_to_org_id = p_site_use_id
269 AND l.header_id = p_header_id
270 AND h.header_id = l.header_id
271 AND l.open_flag = 'Y'
272 AND l.booked_flag = 'Y'
273 AND NVL(l.invoiced_quantity,0) = 0
274 AND NVL(l.shipped_quantity,0) = 0
275 AND l.line_category_code = 'ORDER'
276 AND l.payment_term_id = t.term_id
277 AND t.credit_check_flag = 'Y'
278 AND (l.ato_line_id IS NULL OR l.ato_line_id = l.line_id)
279 AND (EXISTS
280 (SELECT NULL
281 FROM oe_payment_types_all pt
282 WHERE pt.payment_type_code = NVL(l.payment_type_code,
283 NVL(h.payment_type_code, 'BME'))
284 AND pt.credit_check_flag = 'Y'
285 AND NVL(pt.org_id, -99) = NVL(h.org_id,-99))
286 OR
287 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL)
288 )
289 ORDER BY l.line_id;
290
291
292 --ER 6135714
293 CURSOR billto_lines_csr_2 IS
294 SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
295 + decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from oe_order_lines_all l2 where
296 l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
297 )line_total
298 FROM oe_order_lines_all l,
299 oe_order_headers_all h,
300 ra_terms_b t
301 WHERE l.invoice_to_org_id = p_site_use_id
302 AND l.header_id = p_header_id
303 AND h.header_id = l.header_id
304 AND l.open_flag = 'Y'
305 AND l.booked_flag = 'Y'
306 AND NVL(l.invoiced_quantity,0) = 0
307 AND NVL(l.shipped_quantity,0) = 0
308 AND l.line_category_code = 'ORDER'
309 AND l.payment_term_id = t.term_id
310 AND t.credit_check_flag = 'Y'
311 AND (l.ato_line_id IS NULL OR l.ato_line_id = l.line_id)
312 AND (EXISTS
313 (SELECT NULL
314 FROM oe_payment_types_all pt
315 WHERE pt.payment_type_code = NVL(l.payment_type_code,
316 NVL(h.payment_type_code, 'BME'))
317 AND pt.credit_check_flag = 'Y'
318 AND NVL(pt.org_id, -99) = NVL(h.org_id,-99))
319 OR
320 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL)
321 )
322 ORDER BY nvl(l.SCHEDULE_SHIP_DATE, l.REQUEST_DATE) , l.SHIPMENT_PRIORITY_CODE, l.line_id;
323
324
325 CURSOR billto_lines_csr_3 IS
326 SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
327 + decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from oe_order_lines_all l2 where
328 l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
329 ) line_total
330 FROM oe_order_lines_all l,
331 oe_order_headers_all h,
332 ra_terms_b t
333 WHERE l.invoice_to_org_id = p_site_use_id
334 AND l.header_id = p_header_id
335 AND h.header_id = l.header_id
336 AND l.open_flag = 'Y'
337 AND l.booked_flag = 'Y'
338 AND NVL(l.invoiced_quantity,0) = 0
339 AND NVL(l.shipped_quantity,0) = 0
340 AND l.line_category_code = 'ORDER'
341 AND l.payment_term_id = t.term_id
342 AND t.credit_check_flag = 'Y'
343 AND (l.ato_line_id IS NULL OR l.ato_line_id = l.line_id)
344 AND (EXISTS
345 (SELECT NULL
346 FROM oe_payment_types_all pt
347 WHERE pt.payment_type_code = NVL(l.payment_type_code,
348 NVL(h.payment_type_code, 'BME'))
349 AND pt.credit_check_flag = 'Y'
350 AND NVL(pt.org_id, -99) = NVL(h.org_id,-99))
351 OR
352 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL)
353 )
354 ORDER BY l.SHIPMENT_PRIORITY_CODE,nvl(l.SCHEDULE_SHIP_DATE, l.REQUEST_DATE) , l.line_id;
355
356 CURSOR billto_lines_csr_4 IS
357 SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
358 + decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from oe_order_lines_all l2 where
359 l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
360 ) line_total
361 FROM oe_order_lines_all l,
362 oe_order_headers_all h,
363 ra_terms_b t
364 WHERE l.invoice_to_org_id = p_site_use_id
365 AND l.header_id = p_header_id
366 AND h.header_id = l.header_id
367 AND l.open_flag = 'Y'
368 AND l.booked_flag = 'Y'
369 AND NVL(l.invoiced_quantity,0) = 0
370 AND NVL(l.shipped_quantity,0) = 0
371 AND l.line_category_code = 'ORDER'
372 AND l.payment_term_id = t.term_id
373 AND t.credit_check_flag = 'Y'
374 AND (l.ato_line_id IS NULL OR l.ato_line_id = l.line_id)
375 AND (EXISTS
376 (SELECT NULL
377 FROM oe_payment_types_all pt
378 WHERE pt.payment_type_code = NVL(l.payment_type_code,
379 NVL(h.payment_type_code, 'BME'))
380 AND pt.credit_check_flag = 'Y'
381 AND NVL(pt.org_id, -99) = NVL(h.org_id,-99))
382 OR
383 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL)
384 )
385 ORDER BY line_total asc;
386
387 CURSOR billto_lines_csr_5 IS
388 SELECT l.line_id, l.line_number , NVL(unit_selling_price,0) * NVL(ordered_quantity,0)
389 + decode( l.ato_line_id, null , 0, (select sum (NVL(l2.unit_selling_price,0) * NVL(l2.ordered_quantity,0)) from oe_order_lines_all l2 where
390 l2.ato_line_id = l.line_id and l2.ato_line_id <> l2.line_id)
391 ) line_total
392 FROM oe_order_lines_all l,
393 oe_order_headers_all h,
394 ra_terms_b t
395 WHERE l.invoice_to_org_id = p_site_use_id
396 AND l.header_id = p_header_id
397 AND h.header_id = l.header_id
398 AND l.open_flag = 'Y'
399 AND l.booked_flag = 'Y'
400 AND NVL(l.invoiced_quantity,0) = 0
401 AND NVL(l.shipped_quantity,0) = 0
402 AND l.line_category_code = 'ORDER'
403 AND l.payment_term_id = t.term_id
404 AND t.credit_check_flag = 'Y'
405 AND (l.ato_line_id IS NULL OR l.ato_line_id = l.line_id)
406 AND (EXISTS
407 (SELECT NULL
408 FROM oe_payment_types_all pt
409 WHERE pt.payment_type_code = NVL(l.payment_type_code,
410 NVL(h.payment_type_code, 'BME'))
411 AND pt.credit_check_flag = 'Y'
412 AND NVL(pt.org_id, -99) = NVL(h.org_id,-99))
413 OR
414 (l.payment_type_code IS NULL AND h.payment_type_code IS NULL)
415 )
416 ORDER BY line_total desc;
417 --ER 6135714
418
419 l_site_holds_tbl Line_Holds_Tbl_Rectype;
420 row_cntr BINARY_INTEGER := 1;
421 BEGIN
422 IF G_debug_flag = 'Y'
423 THEN
424 OE_DEBUG_PUB.Add('OEXVCRLB: In Create_Holds_Table');
425 OE_DEBUG_PUB.Add('p_site_use_id '|| p_site_use_id );
426 END IF;
427
428 /*ER 6135714
429 FOR c_line IN billto_lines_csr LOOP
430 l_site_holds_tbl(row_cntr).line_id := c_line.line_id;
431 l_site_holds_tbl(row_cntr).line_number := c_line.line_number;
432 row_cntr := row_cntr + 1;
433 END LOOP;
434 ER 6135714*/
435
436 --ER 6135714
437 IF l_hold_line_seq = '1' THEN
438 FOR c_line IN billto_lines_csr_1 LOOP
439 l_site_holds_tbl(row_cntr).line_id := c_line.line_id;
440 l_site_holds_tbl(row_cntr).line_number := c_line.line_number;
441 l_site_holds_tbl(row_cntr).line_total := c_line.line_total;
442 row_cntr := row_cntr + 1;
443 END LOOP;
444 ELSIF l_hold_line_seq = '2' THEN
445 FOR c_line IN billto_lines_csr_2 LOOP
446 l_site_holds_tbl(row_cntr).line_id := c_line.line_id;
447 l_site_holds_tbl(row_cntr).line_number := c_line.line_number;
448 l_site_holds_tbl(row_cntr).line_total := c_line.line_total;
449 row_cntr := row_cntr + 1;
450 END LOOP;
451 ELSIF l_hold_line_seq = '3' THEN
452 FOR c_line IN billto_lines_csr_3 LOOP
453 l_site_holds_tbl(row_cntr).line_id := c_line.line_id;
454 l_site_holds_tbl(row_cntr).line_number := c_line.line_number;
455 l_site_holds_tbl(row_cntr).line_total := c_line.line_total;
456 row_cntr := row_cntr + 1;
457 END LOOP;
458 ELSIF l_hold_line_seq = '4' THEN
459 FOR c_line IN billto_lines_csr_4 LOOP
460 l_site_holds_tbl(row_cntr).line_id := c_line.line_id;
461 l_site_holds_tbl(row_cntr).line_number := c_line.line_number;
462 l_site_holds_tbl(row_cntr).line_total := c_line.line_total;
463 row_cntr := row_cntr + 1;
464 END LOOP;
465 ELSIF l_hold_line_seq = '5' THEN
466 FOR c_line IN billto_lines_csr_5 LOOP
467 l_site_holds_tbl(row_cntr).line_id := c_line.line_id;
468 l_site_holds_tbl(row_cntr).line_number := c_line.line_number;
469 l_site_holds_tbl(row_cntr).line_total := c_line.line_total;
470 row_cntr := row_cntr + 1;
471 END LOOP;
472 END IF;
473 --ER 6135714
474
475 x_holds_table := l_site_holds_tbl;
476
477 IF G_debug_flag = 'Y'
478 THEN
479 OE_DEBUG_PUB.Add('Holds table count = '|| x_holds_table.COUNT );
480 OE_DEBUG_PUB.Add('OEXVCRLB: Out Create_Holds_Table');
481 END IF;
482
483 EXCEPTION
484 WHEN OTHERS THEN
485 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
486 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Create_Holds_Table');
487 END IF;
488 RAISE;
489
490 END Create_Holds_Table ;
491
492 ------------------------------------------------------
493 -- Update the values of a line in the holds table.
494 ------------------------------------------------------
495
496 PROCEDURE Update_Holds_Table
497 ( p_holds_table IN OUT NOCOPY Line_Holds_Tbl_Rectype
498 , p_line_id IN NUMBER DEFAULT NULL
499 , p_hold IN VARCHAR2 DEFAULT NULL
500 , p_cc_limit_used IN VARCHAR2 DEFAULT NULL
501 , p_cc_profile_used IN VARCHAR2 DEFAULT NULL
502 , p_customer_id IN NUMBER DEFAULT NULL
503 , p_site_use_id IN NUMBER DEFAULT NULL
504 , p_party_id IN NUMBER DEFAULT NULL
505 , p_item_category_id IN NUMBER DEFAULT NULL
506 , x_return_status OUT NOCOPY VARCHAR2
507 )
508 IS
509 BEGIN
510 IF G_debug_flag = 'Y'
511 THEN
512 OE_DEBUG_PUB.Add('OEXVCRLB: In Update_Holds_Table');
513 OE_DEBUG_PUB.Add(' p_customer_id = '|| p_customer_id );
514 OE_DEBUG_PUB.Add(' p_site_use_id = '|| p_site_use_id );
515 OE_DEBUG_PUB.Add(' p_party_id = '|| p_party_id );
516 END IF;
517
518
519 -- Initialize return status to success
520 x_return_status := FND_API.G_RET_STS_SUCCESS;
521
522 IF p_line_id IS NULL THEN
523 -- Update the whole table.
524 FOR i IN 1..p_holds_table.COUNT
525 LOOP
526 IF p_hold = 'ITEM'
527 THEN
528 p_holds_table(i).hold := p_hold;
529 p_holds_table(i).limit_used := p_cc_limit_used;
530 p_holds_table(i).profile_used := p_cc_profile_used;
531 p_holds_table(i).customer_id := p_customer_id;
532 p_holds_table(i).site_use_id := p_site_use_id;
533 p_holds_table(i).item_category_id := p_item_category_id;
534 p_holds_table(i).party_id := p_party_id ;
535
536 ELSIF NVL(p_holds_table(i).hold,'NONE') <> 'ITEM'
537 THEN
538
539 p_holds_table(i).hold := p_hold;
540 p_holds_table(i).limit_used := p_cc_limit_used;
541 p_holds_table(i).profile_used := p_cc_profile_used;
542 p_holds_table(i).customer_id := p_customer_id;
543 p_holds_table(i).site_use_id := p_site_use_id;
544 p_holds_table(i).item_category_id := p_item_category_id;
545 p_holds_table(i).party_id := p_party_id ;
546 END IF;
547 END LOOP;
548
549 ELSE -- Line ID not null
550 -- Update the specific line.
551
552 FOR i IN 1..p_holds_table.COUNT
553 LOOP
554 IF p_holds_table(i).line_id = p_line_id
555 THEN
556 IF p_hold = 'ITEM'
557 THEN
558 p_holds_table(i).hold := p_hold;
559 p_holds_table(i).limit_used := p_cc_limit_used;
560 p_holds_table(i).profile_used := p_cc_profile_used;
561 p_holds_table(i).customer_id := p_customer_id;
562 p_holds_table(i).site_use_id := p_site_use_id;
563 p_holds_table(i).item_category_id := p_item_category_id;
564 p_holds_table(i).party_id := p_party_id ;
565
566 ELSIF NVL(p_holds_table(i).hold,'NONE') <> 'ITEM'
567 THEN
568 p_holds_table(i).hold := p_hold;
569 p_holds_table(i).limit_used := p_cc_limit_used;
570 p_holds_table(i).profile_used := p_cc_profile_used;
571 p_holds_table(i).customer_id := p_customer_id;
572 p_holds_table(i).site_use_id := p_site_use_id;
573 p_holds_table(i).item_category_id := p_item_category_id;
574 p_holds_table(i).party_id := p_party_id ;
575
576 END IF;
577 END IF;
578 END LOOP;
579 END IF;
580
581 IF G_debug_flag = 'Y'
582 THEN
583 OE_DEBUG_PUB.Add('OEXVCRLB: Out Update_Holds_Table');
584 END IF;
585
586 EXCEPTION
587 WHEN OTHERS THEN
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
589 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
590 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Update_Holds_Table');
591 END IF;
592 RAISE;
593 END Update_Holds_Table;
594
595 --ER 6135714------------------------------------------------------------------------------------
596 --------------------------------------------------------------------------
597 -- This overloaded API is created for credit check hold on order line --
598 -- based on the system parameter 'Credit Hold Sequence for Order Lines' --
599 -- This Overloaded Method is created as a part of ER 6135714 --
600 --------------------------------------------------------------------------
601
602 PROCEDURE Update_Holds_Table
603 ( p_holds_table IN OUT NOCOPY Line_Holds_Tbl_Rectype
604 , p_hold IN VARCHAR2 DEFAULT NULL
605 , p_cc_limit_used IN VARCHAR2 DEFAULT NULL
606 , p_cc_profile_used IN VARCHAR2 DEFAULT NULL
607 , p_customer_id IN NUMBER DEFAULT NULL
608 , p_site_use_id IN NUMBER DEFAULT NULL
609 , p_party_id IN NUMBER DEFAULT NULL
610 , p_exposure IN NUMBER
611 , p_overall_credit_limit IN NUMBER
612 --13939240 Start
613 , p_limit_currency IN VARCHAR2
614 , p_transactional_currency IN VARCHAR2
615 , p_conversion_type IN VARCHAR2
616 --13939240 End
617 )
618 IS
619 l_amt_on_hold number := 0;
620 l_amt_hold_applied number := 0;
621 l_converted_amt number :=0;--Added for Bug#13939240
622
623 BEGIN
624 IF G_debug_flag = 'Y'
625 THEN
626 OE_DEBUG_PUB.Add('OEXVCRLB: In Update_Holds_Table Overloaded');
627 OE_DEBUG_PUB.Add(' p_customer_id = '|| p_customer_id );
628 OE_DEBUG_PUB.Add(' p_site_use_id = '|| p_site_use_id );
629 OE_DEBUG_PUB.Add(' p_party_id = '|| p_party_id );
630 OE_DEBUG_PUB.Add(' p_exposure = '|| p_exposure );
631 OE_DEBUG_PUB.Add(' p_overall_credit_limit = '|| p_overall_credit_limit);
632 END IF;
633
634 -- exposure is always > credit limit for hold to be applied
635 l_amt_on_hold := p_exposure - p_overall_credit_limit;
636
637 FOR i IN reverse p_holds_table.FIRST..p_holds_table.LAST LOOP
638 IF G_debug_flag = 'Y' THEN
639 OE_DEBUG_PUB.Add('line_total='|| p_holds_table(i).line_total || ' Line-id-' ||p_holds_table(i).line_id );
640 END IF;
641 --13939240 start
642 --line_total stored in p_holds_table is of order header currency and p_exposure,
643 --p_overall_credit_limit,l_amt_on_hold are in credit profile currency.
644 --Needs amount conversion while checking amounts.
645 IF p_limit_currency <> p_transactional_currency THEN
646 l_converted_amt:=0;
647 l_converted_amt :=
648 OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
649 ( p_amount => p_holds_table(i).line_total
650 , p_transactional_currency => p_transactional_currency
651 , p_limit_currency => p_limit_currency
652 , p_functional_currency => OE_Credit_Engine_GRP.GL_currency
653 , p_conversion_date => SYSDATE
654 , p_conversion_type => p_conversion_type
655 ) ;
656 p_holds_table(i).line_total :=l_converted_amt;
657 IF G_debug_flag = 'Y' THEN
658 OE_DEBUG_PUB.Add('After conversion line_total='|| p_holds_table(i).line_total || ' Line-id-' ||p_holds_table(i).line_id );
659 END IF;
660 END IF;
661 --13939240 End
662 -- Dont apply hold if the line value is 0, uncomment below line for 0 value line Toshiba ER
663 -- currently Option 4 will ensure that 0 value is NOT on hold
664 -- if (l_amt_hold_applied < l_amt_on_hold AND p_holds_table(i).line_total <> 0) then
665
666 IF (l_amt_hold_applied < l_amt_on_hold ) then --apply hold
667 l_amt_hold_applied := l_amt_hold_applied + p_holds_table(i).line_total;
668 p_holds_table(i).hold := p_hold;
669 p_holds_table(i).limit_used := p_cc_limit_used;
670 p_holds_table(i).profile_used := p_cc_profile_used;
671 p_holds_table(i).customer_id := p_customer_id;
672 p_holds_table(i).site_use_id := p_site_use_id;
673 p_holds_table(i).item_category_id := NULL;
674 p_holds_table(i).party_id := p_party_id ;
675 IF G_debug_flag = 'Y' THEN
676 OE_DEBUG_PUB.Add('Applying Hold on Line_id = '|| p_holds_table(i).line_id );
677 END IF;
678 END IF;
679 END LOOP;
680
681 IF G_debug_flag = 'Y'
682 THEN
683 OE_DEBUG_PUB.Add('OEXVCRLB: Out Update_Holds_Table');
684 END IF;
685 EXCEPTION
686 WHEN OTHERS THEN
687 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
688 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Update_Holds_Table overloaded');
689 END IF;
690 RAISE;
691 END Update_Holds_Table;
692 --ER 6135714------------------------------------------------------------------------------------
693
694 -------------------------------------------------------
695 -- Check if credit hold was manually released.
696 -- N: No release records found
697 -- Y: Release records found
698 -------------------------------------------------------
699 FUNCTION Check_Manual_Released_Holds
700 ( p_calling_action IN VARCHAR2
701 , p_credit_hold_level IN VARCHAR2
702 , p_hold_id IN OE_HOLD_DEFINITIONS.HOLD_ID%TYPE
703 , p_header_id IN NUMBER
704 , p_line_id IN NUMBER
705 , p_credit_check_rule_rec IN
706 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
707 )
708 RETURN VARCHAR2
709 IS
710 l_hold_release_id NUMBER;
711 l_dummy VARCHAR2(1);
712 l_manual_hold_exists VARCHAR2(1) := 'N';
713 l_released_rec_exists VARCHAR2(1) := 'Y';
714 l_release_date DATE;
715
716 --added for BUG#9728597 Start
717 cursor released_hold is
718 SELECT ohr.HOLD_RELEASE_ID
719 FROM OE_ORDER_HOLDS h,
720 OE_HOLD_SOURCES_ALL s,
721 oe_hold_releases ohr
722 WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
723 AND H.HEADER_ID = p_header_id
724 AND H.LINE_ID IS NULL
725 AND H.HOLD_RELEASE_ID IS NOT NULL
726 AND S.HOLD_ID = p_hold_id
727 AND S.HOLD_ENTITY_CODE = 'O'
728 AND S.HOLD_ENTITY_ID = p_header_id
729 AND S.RELEASED_FLAG ='Y'
730 AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
731 ORDER BY ohr.creation_date DESC;
732
733 cursor released_hold_line is
734 SELECT ohr.HOLD_RELEASE_ID
735 FROM OE_ORDER_HOLDS h,
736 OE_HOLD_SOURCES_ALL s,
737 oe_hold_releases ohr
738 WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
739 AND H.HEADER_ID = p_header_id
740 AND H.LINE_ID = p_line_id
741 AND H.HOLD_RELEASE_ID IS NOT NULL
742 AND S.HOLD_ID = p_hold_id
743 AND S.HOLD_ENTITY_CODE = 'O'
744 AND S.HOLD_ENTITY_ID = p_header_id
745 AND S.RELEASED_FLAG ='Y'
746 AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
747 ORDER BY ohr.creation_date DESC;
748 --added for BUG#9728597 End
749
750 BEGIN
751 IF G_debug_flag = 'Y'
752 THEN
753 OE_DEBUG_PUB.Add('OEXVCRLB: In Check_Manual_Released_Holds');
754 OE_DEBUG_PUB.Add('p_calling_action = '|| p_calling_action );
755 OE_DEBUG_PUB.Add('Check for Header ID/Line ID: '||p_header_id||'/'
756 ||p_line_id,1);
757 OE_DEBUG_PUB.Add('G_delayed_request = '||
758 OE_credit_engine_grp.G_delayed_request );
759 END IF;
760
761 -- Will not check if the event is UPDATE
762
763 -- Adding 'AUTO HOLD' for bug# 4207478
764 IF p_calling_action IN ( 'SHIPPING' , 'PACKING' , 'PICKING', 'AUTO HOLD')
765 AND NVL(OE_credit_engine_grp.G_delayed_request, FND_API.G_FALSE )
766 = FND_API.G_FALSE
767 --ER 12363706 start
768 OR (NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) = FND_API.G_TRUE)
769 --ER 12363706 end
770 THEN
771 BEGIN
772 IF p_credit_hold_level = 'ORDER'
773 THEN
774
775 --commented for BUG#9728597
776 /*SELECT /* MOAC_SQL_CHANGE */ /*NVL(MAX(H.HOLD_RELEASE_ID),0)
777 INTO l_hold_release_id
778 FROM OE_ORDER_HOLDS h,
779 OE_HOLD_SOURCES_ALL s
780 WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
781 AND H.HEADER_ID = p_header_id
782 AND H.LINE_ID IS NULL
783 AND H.HOLD_RELEASE_ID IS NOT NULL
784 AND S.HOLD_ID = p_hold_id
785 AND S.HOLD_ENTITY_CODE = 'O'
786 AND S.HOLD_ENTITY_ID = p_header_id
787 AND S.RELEASED_FLAG ='Y';*/
788 --commented for BUG#9728597
789
790 --added for BUG#9728597 Start
791 OPEN released_hold;
792 FETCH released_hold INTO l_hold_release_id;
793 IF (released_hold%notfound) THEN
794 oe_debug_pub.add('No Released record found');
795 l_released_rec_exists := 'N';
796 END IF;
797 CLOSE released_hold;
798 --added for BUG#9728597 End
799
800
801 ELSE
802
803 --commented for BUG#9728597
804 /*SELECT /* MOAC_SQL_CHANGE */ /*NVL(MAX(H.HOLD_RELEASE_ID),0)
805 INTO l_hold_release_id
806 FROM OE_ORDER_HOLDS h,
807 OE_HOLD_SOURCES_ALL s
808 WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
809 AND H.HEADER_ID = p_header_id
810 AND H.LINE_ID = p_line_id
811 AND H.HOLD_RELEASE_ID IS NOT NULL
812 AND S.HOLD_ID = p_hold_id
813 AND S.HOLD_ENTITY_CODE = 'O'
814 AND S.HOLD_ENTITY_ID = p_header_id
815 AND S.RELEASED_FLAG ='Y';*/
816 --commented for BUG#9728597
817
818 --added for BUG#9728597 Start
819 OPEN released_hold_line;
820 FETCH released_hold_line INTO l_hold_release_id;
821 IF (released_hold_line%notfound) THEN
822 oe_debug_pub.add('No Released record found');
823 l_released_rec_exists := 'N';
824 END IF;
825 CLOSE released_hold_line;
826 --added for BUG#9728597 End
827
828 END IF;
829
830 EXCEPTION
831 WHEN NO_DATA_FOUND THEN
832 OE_DEBUG_PUB.Add
833 ('No released record exist forHeader ID/Line ID: '||p_header_id||'/'||p_line_id,1);
834 l_released_rec_exists := 'N';
835 WHEN OTHERS THEN
836 NULL;
837 END;
838
839 IF l_released_rec_exists = 'Y' THEN
840 BEGIN
841 SELECT
842 'Y'
843 , CREATION_DATE -----added
844 INTO
845 l_manual_hold_exists
846 , l_release_date
847 FROM OE_HOLD_RELEASES
848 WHERE HOLD_RELEASE_ID = l_hold_release_id
849 AND RELEASE_REASON_CODE <> 'PASS_CREDIT'
850 AND CREATED_BY <> 1;
851 IF NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) = FND_API.G_FALSE THEN --ER 12363706
852 -----check if days_honor_manual_release expired
853 IF p_credit_check_rule_rec.days_honor_manual_release IS NOT NULL
854 THEN
855 IF (l_release_date + p_credit_check_rule_rec.days_honor_manual_release >= SYSDATE )
856 THEN
857 l_manual_hold_exists := 'Y';
858 ELSE
859 l_manual_hold_exists := 'N';
860 END IF;
861 END IF;
862 END IF; --ER 12363706
863 EXCEPTION
864 WHEN NO_DATA_FOUND THEN
865 OE_DEBUG_PUB.Add
866 ('No manually released credit holds for Header ID/Line ID: '||
867 p_header_id||'/'||p_line_id,1);
868 l_manual_hold_exists := 'N';
869 WHEN OTHERS THEN
870 NULL;
871 END;
872 END IF;
873 END IF;
874
875 IF G_debug_flag = 'Y'
876 THEN
877 OE_DEBUG_PUB.Add('OEXVCRLB: Out Check_Manual_Released_Holds: '||l_manual_hold_exists );
878 END IF;
879
880 RETURN l_manual_hold_exists ;
881
882 EXCEPTION
883 WHEN OTHERS THEN
884 OE_MSG_PUB.Add_Exc_Msg
885 (G_PKG_NAME, 'Check_Manual_Released_Holds' );
886 RAISE;
887
888 END Check_Manual_Released_Holds;
889
890 ----------------------------------------------------
891 --- Check for max past due invoices for
892 -- line level bill to
893 ----------------------------------------------------
894 PROCEDURE Chk_Past_Due_Invoice
895 ( p_customer_id IN NUMBER
896 , p_site_use_id IN NUMBER
897 , p_party_id IN NUMBER
898 , p_credit_check_rule_rec IN
899 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
900 , p_system_parameter_rec IN
901 OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
902 , p_credit_level IN VARCHAR2
903 , p_usage_curr IN oe_credit_check_util.curr_tbl_type
904 , p_include_all_flag IN VARCHAR2
905 , p_global_exposure_flag IN VARCHAR2 := 'N'
906 , x_cc_result_out OUT NOCOPY VARCHAR2
907 , x_return_status OUT NOCOPY VARCHAR2
908 )
909 IS
910 l_exist_flag VARCHAR2(1);
911
912 BEGIN
913 IF G_debug_flag = 'Y'
914 THEN
915 OE_DEBUG_PUB.ADD('OEXVCRLB: In Chk_Past_Due_Invoice');
916 END IF;
917
918 -- Initialize return status to success
919 x_return_status := FND_API.G_RET_STS_SUCCESS;
920 -- Default to pass
921 x_cc_result_out := 'PASS';
922
923
924
925 OE_CREDIT_CHECK_UTIL.Get_Past_Due_Invoice
926 ( p_customer_id => p_customer_id
927 , p_site_use_id => p_site_use_id
928 , p_party_id => p_party_id
929 , p_credit_check_rule_rec => p_credit_check_rule_rec
930 , p_system_parameter_rec => p_system_parameter_rec
931 , p_credit_level => p_credit_level
932 , p_usage_curr => p_usage_curr
933 , p_include_all_flag => p_include_all_flag
934 , p_global_exposure_flag => p_global_exposure_flag
935 , x_exist_flag => l_exist_flag
936 , x_return_status => x_return_status
937 );
938
939
940 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
941 RAISE FND_API.G_EXC_ERROR;
942 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
943 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
944 END IF;
945
946 IF l_exist_flag = 'Y'
947 THEN
948 x_cc_result_out := 'FAIL';
949 END IF;
950
951 IF G_debug_flag = 'Y'
952 THEN
953 OE_DEBUG_PUB.ADD('l_exist_flag ' || l_exist_flag );
954 OE_DEBUG_PUB.ADD('x_cc_result_out ' || x_cc_result_out);
955 OE_DEBUG_PUB.ADD('OEXVCRLB: Out Chk_Past_Due_Invoice');
956 END IF;
957
958 EXCEPTION
959 WHEN OTHERS THEN
960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
961 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
962 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'Chk_Past_Due_invoice');
963 END IF;
964 END Chk_Past_Due_Invoice;
965
966
967 -----------------------------------------------------
968 -- Check if line level credit holds exits for a given
969 -- order/line
970 --------------------------------------------------
971 FUNCTION Hold_Exists
972 ( p_header_id IN NUMBER
973 , p_line_id IN NUMBER
974 , p_credit_hold_level IN VARCHAR2
975 )
976 RETURN BOOLEAN IS
977 l_hold_result VARCHAR2(30);
978 l_return_status VARCHAR2(30);
979 l_msg_count NUMBER;
980 l_msg_data VARCHAR2(2000);
981 BEGIN
982 IF G_debug_flag = 'Y'
983 THEN
984 OE_DEBUG_PUB.ADD('OEXVCRLB: In Hold_Exists');
985 END IF;
986
987 IF p_credit_hold_level = 'ORDER'
988 THEN
989 IF G_debug_flag = 'Y'
990 THEN
991 OE_DEBUG_PUB.ADD('OEXVCRLB: Check for credit check holds for Header ID : '
992 || p_header_id,1);
993 END IF;
994
995 OE_HOLDS_PUB.Check_Holds
996 ( p_api_version => 1.0
997 , p_header_id => p_header_id
998 , p_hold_id => 1
999 , p_wf_item => OE_Credit_Engine_GRP.G_cc_hold_item_type
1000 , p_wf_activity => OE_Credit_Engine_GRP.G_cc_hold_activity_name
1001 , p_entity_code => 'O'
1002 , p_entity_id => p_header_id
1003 , x_result_out => l_hold_result
1004 , x_msg_count => l_msg_count
1005 , x_msg_data => l_msg_data
1006 , x_return_status => l_return_status
1007 );
1008 ELSE
1009 IF G_debug_flag = 'Y'
1010 THEN
1011 OE_DEBUG_PUB.ADD('OEXVCRLB: Check for holds for Header/Line ID : '
1012 || p_header_id || '/' || p_line_id,1);
1013 END IF;
1014
1015 OE_HOLDS_PUB.Check_Holds
1016 ( p_api_version => 1.0
1017 , p_header_id => p_header_id
1018 , p_line_id => p_line_id
1019 , p_hold_id => 1
1020 , p_wf_item => OE_Credit_Engine_GRP.G_cc_hold_item_type
1021 , p_wf_activity => OE_Credit_Engine_GRP.G_cc_hold_activity_name
1022 , p_entity_code => 'O'
1023 , p_entity_id => p_header_id
1024 , x_result_out => l_hold_result
1025 , x_msg_count => l_msg_count
1026 , x_msg_data => l_msg_data
1027 , x_return_status => l_return_status
1028 );
1029 END IF;
1030
1031 IF G_debug_flag = 'Y'
1032 THEN
1033 OE_DEBUG_PUB.ADD('OEXVCRLB: Out Check_Holds');
1034 END IF;
1035
1036 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1037 RAISE FND_API.G_EXC_ERROR;
1038 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1039 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1040 END IF;
1041
1042 IF l_hold_result = FND_API.G_TRUE THEN
1043 return TRUE;
1044 ELSE
1045 return FALSE;
1046 END IF;
1047
1048 EXCEPTION
1049 WHEN OTHERS THEN
1050 OE_MSG_PUB.Add_Exc_Msg
1051 (G_PKG_NAME, 'Hold_Exists');
1052 RAISE;
1053
1054 END Hold_Exists;
1055
1056 ---------------------------------------------------
1057 -- Write appropriate message to either the message|
1058 -- table or to the logfile if called from a |
1059 -- concurrent program. |
1060 ---------------------------------------------------
1061
1062 PROCEDURE Write_Hold_Message
1063 ( p_calling_action IN VARCHAR2
1064 , p_cc_limit_used IN VARCHAR2 DEFAULT NULL
1065 , p_cc_profile_used IN VARCHAR2 DEFAULT NULL
1066 , p_order_number IN NUMBER
1067 , p_line_number IN NUMBER
1068 , p_customer_name IN VARCHAR2 DEFAULT NULL
1069 , p_site_name IN VARCHAR2 DEFAULT NULL
1070 , p_party_name IN VARCHAR2 DEFAULT NULL
1071 , p_item_category IN VARCHAR2 DEFAULT NULL
1072 , x_comment OUT NOCOPY VARCHAR2
1073 )
1074 IS
1075 l_comment VARCHAR2(2000);
1076 l_cc_profile_used VARCHAR2(30);
1077 l_calling_activity VARCHAR2(50); --ER#7479609
1078
1079 BEGIN
1080 IF G_debug_flag = 'Y'
1081 THEN
1082 OE_DEBUG_PUB.Add('OEXVCRLB: In Write_Hold_Message');
1083 END IF;
1084
1085 -- Write to message stack anyway regardless of the calling action
1086 -- added IF for bug 5467793
1087 --bug 7207292, reverting fix 5467793 as multiple messages are now shown in processing messages window
1088 --IF NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) =
1089 -- FND_API.G_FALSE THEN
1090 IF p_cc_limit_used <> 'ITEM' THEN
1091 -- bug 4002820
1092 IF INSTR(p_cc_limit_used, ',') > 0 THEN
1093 l_cc_profile_used := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_PROFILE', p_cc_profile_used);
1094 FND_MESSAGE.Set_Name('ONT','OE_CC_LINE_HOLD_MSG');
1095 FND_MESSAGE.Set_Token('ORDER_NUMBER' ,p_order_number);
1096 FND_MESSAGE.Set_Token('LINE_NUMBER' ,p_line_number);
1097 FND_MESSAGE.Set_Token('LIMIT_USED',p_cc_limit_used);
1098 FND_MESSAGE.Set_Token('CC_PROFILE',l_cc_profile_used);
1099 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
1100 FND_MESSAGE.Set_Name('ONT','OE_CC_LINE_HOLD_MSG');
1101 FND_MESSAGE.Set_Token('ORDER_NUMBER' ,p_order_number);
1102 FND_MESSAGE.Set_Token('LINE_NUMBER' ,p_line_number);
1103 FND_MESSAGE.Set_Token('LIMIT_USED',p_cc_limit_used);
1104 FND_MESSAGE.Set_Token('CC_PROFILE',l_cc_profile_used);
1105 OE_MSG_PUB.Add;
1106
1107 ELSE
1108 IF p_cc_profile_used = 'SITE' THEN
1109 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_'||p_cc_limit_used||'_'||
1110 p_cc_profile_used);
1111 FND_MESSAGE.Set_Token('ORDER_NUMBER' ,p_order_number);
1112 FND_MESSAGE.Set_Token('LINE_NUMBER' ,p_line_number);
1113 FND_MESSAGE.Set_Token('CUSTOMER_NAME',p_customer_name);
1114 FND_MESSAGE.Set_Token('SITE_NAME' ,p_site_name);
1115 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
1116
1117 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_'||p_cc_limit_used||'_'||
1118 p_cc_profile_used);
1119 FND_MESSAGE.Set_Token('ORDER_NUMBER',p_order_number);
1120 FND_MESSAGE.Set_Token('LINE_NUMBER',p_line_number);
1121 FND_MESSAGE.Set_Token('CUSTOMER_NAME',p_customer_name);
1122 FND_MESSAGE.Set_Token('SITE_NAME' ,p_site_name);
1123 OE_MSG_PUB.Add;
1124 ELSIF p_cc_profile_used = 'CUSTOMER'
1125 THEN
1126 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_'||p_cc_limit_used||'_'||
1127 p_cc_profile_used);
1128 FND_MESSAGE.Set_Token('ORDER_NUMBER' ,p_order_number);
1129 FND_MESSAGE.Set_Token('LINE_NUMBER' ,p_line_number);
1130 FND_MESSAGE.Set_Token('CUSTOMER_NAME',p_customer_name);
1131 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
1132
1133 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_'||p_cc_limit_used||'_'||
1134 p_cc_profile_used);
1135 FND_MESSAGE.Set_Token('ORDER_NUMBER',p_order_number);
1136 FND_MESSAGE.Set_Token('LINE_NUMBER',p_line_number);
1137 FND_MESSAGE.Set_Token('CUSTOMER_NAME',p_customer_name);
1138 OE_MSG_PUB.Add;
1139
1140 ELSIF p_cc_profile_used = 'PARTY'
1141 THEN
1142 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_'||p_cc_limit_used||'_'||
1143 p_cc_profile_used);
1144 FND_MESSAGE.Set_Token('ORDER_NUMBER' ,p_order_number);
1145 FND_MESSAGE.Set_Token('LINE_NUMBER' ,p_line_number);
1146 FND_MESSAGE.Set_Token('PARTY_NAME',p_party_name );
1147 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
1148
1149 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_'||p_cc_limit_used||'_'||
1150 p_cc_profile_used);
1151 FND_MESSAGE.Set_Token('ORDER_NUMBER',p_order_number);
1152 FND_MESSAGE.Set_Token('LINE_NUMBER',p_line_number);
1153 FND_MESSAGE.Set_Token('PARTY_NAME',p_party_name) ;
1154 OE_MSG_PUB.Add;
1155
1156 ELSIF p_cc_profile_used = 'DEFAULT' THEN
1157 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_'||p_cc_limit_used||'_'||
1158 p_cc_profile_used);
1159 FND_MESSAGE.Set_Token('ORDER_NUMBER',p_order_number);
1160 FND_MESSAGE.Set_Token('LINE_NUMBER',p_line_number);
1161 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
1162
1163 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_'||p_cc_limit_used||'_'||
1164 p_cc_profile_used);
1165 FND_MESSAGE.Set_Token('ORDER_NUMBER',p_order_number);
1166 FND_MESSAGE.Set_Token('LINE_NUMBER',p_line_number);
1167 OE_MSG_PUB.Add;
1168 END IF;
1169 END IF;
1170 ELSE
1171 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_'||p_cc_limit_used||'_'||
1172 'CATEGORY');
1173 FND_MESSAGE.Set_Token('ORDER_NUMBER',p_order_number);
1174 FND_MESSAGE.Set_Token('LINE_NUMBER',p_line_number);
1175 FND_MESSAGE.Set_Token('CATEGORY',p_item_category);
1176 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
1177
1178 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_'||p_cc_limit_used||'_'||
1179 'CATEGORY');
1180 FND_MESSAGE.Set_Token('ORDER_NUMBER',p_order_number);
1181 FND_MESSAGE.Set_Token('LINE_NUMBER',p_line_number);
1182 FND_MESSAGE.Set_Token('CATEGORY',p_item_category);
1183 OE_MSG_PUB.Add;
1184 END IF;
1185 --END IF; -- bug 5467793
1186 --
1187 -- Save messages on message stack to message table
1188 -- Give a dummy request id
1189 -- rajesh
1190 --OE_MSG_PUB.Save_Messages(1);
1191 --OE_MSG_PUB.Delete_Msg(OE_MSG_PUB.G_msg_count);
1192 --
1193 -- Write to logfile if original call was from a concurrent program
1194 --
1195 IF p_calling_action = 'AUTO HOLD' THEN
1196 FND_FILE.PUT_LINE(FND_FILE.LOG,'Order Number: '||TO_CHAR(p_order_number)
1197 ||' Line Number: '||TO_CHAR(p_line_number)
1198 ||' placed on credit check hold.');
1199 FND_FILE.PUT_LINE(FND_FILE.LOG,'Hold Comment: '||SUBSTR(l_comment,1,1000));
1200 END IF;
1201
1202 --ER#7479609 start
1203 IF OE_Verify_Payment_PUB.G_init_calling_action = 'AUTO HOLD' THEN
1204 l_calling_activity := 'Credit Check Processor';
1205 ELSE
1206 l_calling_activity := InitCap(OE_Verify_Payment_PUB.G_init_calling_action);
1207 END IF;
1208
1209 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_ACT_COM');
1210 FND_MESSAGE.Set_Token('CALLING_ACTIVITY',l_calling_activity);
1211 FND_MESSAGE.Set_Token('CREDIT_CHECK_RULE',OE_Verify_Payment_PUB.G_credit_check_rule);
1212
1213 l_comment := l_comment||SUBSTR(FND_MESSAGE.GET,1,2000);
1214 --ER#7479609 end
1215
1216 x_comment := NVL(OE_Credit_Engine_GRP.G_currency_error_msg,l_comment);
1217
1218 IF G_debug_flag = 'Y'
1219 THEN
1220 OE_DEBUG_PUB.ADD(' x_comment '|| x_comment );
1221 OE_DEBUG_PUB.Add('OEXVCRLB: Out Write_Hold_Message');
1222 END IF;
1223
1224 EXCEPTION
1225 WHEN OTHERS THEN
1226 OE_MSG_PUB.Add_Exc_Msg
1227 (G_PKG_NAME, 'Write_Hold_Message');
1228 RAISE;
1229
1230 END Write_Hold_Message;
1231
1232 ---------------------------------------------------
1233 -- Write release message to the message table |
1234 -- table and to the logfile if called from a |
1235 -- concurrent program. |
1236 ---------------------------------------------------
1237
1238 PROCEDURE Write_Release_Message (
1239 p_calling_action IN VARCHAR2
1240 , p_order_number IN NUMBER
1241 , p_line_number IN NUMBER
1242 )
1243 IS
1244 BEGIN
1245 IF G_debug_flag = 'Y'
1246 THEN
1247 OE_DEBUG_PUB.Add('OEXVCRLB: In Write_Release_Message');
1248 END IF;
1249
1250 IF p_calling_action = 'AUTO RELEASE' THEN
1251 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Order Number: '||TO_CHAR(p_order_number)
1252 ||' Line Number: '||TO_CHAR(p_line_number)
1253 ||' released from credit check hold.');
1254 END IF;
1255
1256 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_REMOVED');
1257 FND_MESSAGE.Set_Token('ORDER_NUMBER',p_order_number);
1258 FND_MESSAGE.Set_Token('LINE_NUMBER',p_line_number);
1259 OE_MSG_PUB.Add;
1260 --- rajesh
1261 --OE_MSG_PUB.Save_Messages(1);
1262 --
1263 IF G_debug_flag = 'Y'
1264 THEN
1265 OE_DEBUG_PUB.Add('OEXVCRLB: Out Write_Release_Message');
1266 END IF;
1267
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 OE_MSG_PUB.Add_Exc_Msg
1271 (G_PKG_NAME, 'Write_Release_Message');
1272 RAISE;
1273
1274 END Write_Release_Message;
1275
1276 ---------------------------------------------------
1277 -- Write appropriate message to either the message|
1278 -- table or to the logfile if called from a |
1279 -- concurrent program for order level credit hold |
1280 ---------------------------------------------------
1281
1282 PROCEDURE Write_Order_Hold_Msg
1283 (
1284 p_calling_action IN VARCHAR2
1285 , p_cc_limit_used IN VARCHAR2 DEFAULT NULL
1286 , p_cc_profile_used IN VARCHAR2 DEFAULT NULL
1287 , p_order_number IN NUMBER
1288 , p_item_category IN VARCHAR2 DEFAULT NULL
1289 , x_comment OUT NOCOPY VARCHAR2
1290 )
1291 IS
1292 l_comment VARCHAR2(2000);
1293 l_cc_profile_used VARCHAR2(30);
1294 l_calling_activity VARCHAR2(50); --ER#7479609
1295
1296 BEGIN
1297 IF G_debug_flag = 'Y'
1298 THEN
1299 OE_DEBUG_PUB.Add('OEXVCRLB: In Write_Order_Hold_Msg');
1300 END IF;
1301
1302 -- Write to message stack anyway regardless of the calling action
1303 IF p_cc_limit_used <> 'ITEM' THEN
1304 -- bug 4002820
1305 IF INSTR(p_cc_limit_used, ',') > 0 THEN
1306
1307 --bug 4153299
1308 l_cc_profile_used := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_PROFILE', p_cc_profile_used);
1309
1310 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_MSG');
1311 FND_MESSAGE.Set_Token('LIMIT_USED',p_cc_limit_used);
1312 FND_MESSAGE.Set_Token('CC_PROFILE',l_cc_profile_used);
1313 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
1314 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_MSG');
1315 FND_MESSAGE.Set_Token('LIMIT_USED',p_cc_limit_used);
1316 FND_MESSAGE.Set_Token('CC_PROFILE',l_cc_profile_used);
1317 OE_MSG_PUB.Add;
1318 ELSE
1319 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_'||p_cc_limit_used||'_'||
1320 p_cc_profile_used);
1321 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
1322 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_'||p_cc_limit_used||'_'||
1323 p_cc_profile_used);
1324 OE_MSG_PUB.Add;
1325 END IF;
1326 ELSE
1327 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_'||p_cc_limit_used||'_CATEGORY');
1328 FND_MESSAGE.Set_Token('CATEGORY',p_item_category);
1329 l_comment := SUBSTR(FND_MESSAGE.GET,1,2000);
1330 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_'||p_cc_limit_used||'_'||
1331 'CATEGORY');
1332 FND_MESSAGE.Set_Token('CATEGORY',p_item_category);
1333 OE_MSG_PUB.Add;
1334 END IF;
1335 --
1336 -- Write to logfile if original call was from a concurrent program
1337 --
1338 IF p_calling_action = 'AUTO HOLD' THEN
1339 FND_FILE.PUT_LINE(FND_FILE.LOG,'Order '||TO_CHAR(p_order_number)
1340 ||': Credit check hold applied');
1341 FND_FILE.PUT_LINE(FND_FILE.LOG,'Hold Comment: '||SUBSTR(l_comment,1,1000));
1342 END IF;
1343
1344 --ER#7479609 start
1345 IF OE_Verify_Payment_PUB.G_init_calling_action = 'AUTO HOLD' THEN
1346 l_calling_activity := 'Credit Check Processor';
1347 ELSE
1348 l_calling_activity := InitCap(OE_Verify_Payment_PUB.G_init_calling_action);
1349 END IF;
1350
1351 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_ACT_COM');
1352 FND_MESSAGE.Set_Token('CALLING_ACTIVITY',l_calling_activity);
1353 FND_MESSAGE.Set_Token('CREDIT_CHECK_RULE',OE_Verify_Payment_PUB.G_credit_check_rule);
1354
1355 l_comment := l_comment||SUBSTR(FND_MESSAGE.GET,1,2000);
1356 --ER#7479609 end
1357
1358 x_comment := l_comment;
1359
1360 IF G_debug_flag = 'Y'
1361 THEN
1362 OE_DEBUG_PUB.Add(' x_comment '|| x_comment );
1363 OE_DEBUG_PUB.Add('OEXVCRLB: Out Write_Order_Hold_Msg');
1364 END IF;
1365
1366 EXCEPTION
1367 WHEN OTHERS THEN
1368 OE_MSG_PUB.Add_Exc_Msg
1369 (G_PKG_NAME, 'Write_Order_Hold_Msg');
1370 RAISE;
1371
1372 END Write_Order_Hold_Msg;
1373
1374 ---------------------------------------------------
1375 -- Write release message to the screen or to the
1376 -- log file if called from a concurrent program.
1377 ---------------------------------------------------
1378 PROCEDURE Write_Order_Release_Msg
1379 ( p_calling_action IN VARCHAR2
1380 , p_order_number IN NUMBER
1381 )
1382 IS
1383 BEGIN
1384 IF G_debug_flag = 'Y'
1385 THEN
1386 OE_DEBUG_PUB.Add('OEXVCRLB: In Write_Order_Release_Msg');
1387 END IF;
1388
1389 IF p_calling_action = 'AUTO RELEASE' THEN
1390 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Order '||TO_CHAR(p_order_number)
1391 ||': Credit check hold released.');
1392 ELSE
1393 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_REMOVED');
1394 OE_MSG_PUB.Add;
1395 END IF;
1396 --
1397 IF G_debug_flag = 'Y'
1398 THEN
1399 OE_DEBUG_PUB.Add('OEXVCRLB: Out Write_Order_Release_Msg');
1400 END IF;
1401
1402 EXCEPTION
1403 WHEN OTHERS THEN
1404 OE_MSG_PUB.Add_Exc_Msg
1405 (G_PKG_NAME, 'Write_Order_Release_Msg');
1406 RAISE;
1407
1408 END Write_Order_Release_Msg;
1409
1410 ---------------------------------------------------
1411 -- Apply credit check hold on the specified order
1412 -- line.
1413 ---------------------------------------------------
1414 /*
1415 ** Bug # 3416932
1416 ** Replaced call to Apply_Hold_And_Commit with Apply_Holds
1417 ** and made this complete procedure part of Autonomous Trxn
1418 */
1419 /*
1420 ** Bug # 3415608 and 3430235
1421 ** Reverted changes done under bug # 3386382.
1422 ** Introduced new procedure Update_Comments_And_Commit to
1423 ** Update and Commit Hold Comments. Apply_Holds_And_Commit
1424 ** And Update_Comments_And_Commit are now called whenever
1425 ** Calling Action is Picking, Packing or Shipping ELSE
1426 ** Apply_Holds and Update_Hold_Comments are called.
1427 */
1428
1429 PROCEDURE Apply_Line_CC_Hold
1430 ( p_header_id IN NUMBER
1431 , p_order_number IN NUMBER
1432 , p_line_id IN NUMBER
1433 , p_line_number IN NUMBER
1434 , p_calling_action IN VARCHAR2 DEFAULT 'BOOKING'
1435 , p_cc_limit_used IN VARCHAR2
1436 , p_cc_profile_used IN VARCHAR2
1437 , p_party_id IN NUMBER DEFAULT NULL
1438 , p_customer_id IN NUMBER DEFAULT NULL
1439 , p_site_use_id IN NUMBER DEFAULT NULL
1440 , p_item_category_id IN NUMBER DEFAULT NULL
1441 , p_credit_hold_level IN VARCHAR2
1442 , p_credit_check_rule_rec IN
1443 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
1444 , x_cc_result_out OUT NOCOPY VARCHAR2
1445 )
1446 IS
1447
1448 -- Cursor to select the customer name
1449 CURSOR customer_name_csr IS
1450 SELECT name
1451 FROM oe_sold_to_orgs_v
1452 WHERE customer_id = p_customer_id;
1453
1454 CURSOR party_name_csr IS
1455 SELECT party_name
1456 FROM hz_parties
1457 WHERE party_id = p_party_id ;
1458
1459 -- Cursor to select site use code
1460 CURSOR site_name_csr IS
1461 SELECT location
1462 FROM hz_cust_site_uses
1463 WHERE site_use_id = p_site_use_id;
1464 -- Cursor to select item category
1465 CURSOR item_category_csr IS
1466 SELECT description
1467 FROM mtl_categories
1468 WHERE category_id = p_item_category_id;
1469 --
1470 l_customer_name VARCHAR2(360);
1471 l_party_name VARCHAR2(360);
1472 l_item_category VARCHAR2(240):= NULL;
1473 l_site_name VARCHAR2(40);
1474 l_cc_result_out VARCHAR2(30) := 'FAIL_NONE';
1475 l_hold_exists VARCHAR2(1) := NULL ;
1476 l_msg_count NUMBER := 0;
1477 l_msg_data VARCHAR2(2000);
1478 l_return_status VARCHAR2(30);
1479 l_hold_comment VARCHAR2(2000);
1480 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type :=
1481 OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
1482 BEGIN
1483 IF G_debug_flag = 'Y'
1484 THEN
1485 OE_DEBUG_PUB.Add('OEXVCRLB: In Apply_Line_CC_Hold');
1486 OE_DEBUG_PUB.Add('p_calling_action => '|| p_calling_action );
1487 OE_DEBUG_PUB.Add('p_header_id => '|| p_header_id);
1488 OE_DEBUG_PUB.Add('p_line_id => '|| p_line_id );
1489 OE_DEBUG_PUB.Add('p_cc_limit_used => '|| p_cc_limit_used );
1490 OE_DEBUG_PUB.Add('---------------------------------');
1491 END IF;
1492 --
1493 --
1494 IF p_cc_limit_used <> 'ITEM' THEN
1495 --
1496 -- Get the customer name
1497 --
1498 OPEN customer_name_csr;
1499 FETCH customer_name_csr INTO l_customer_name;
1500 CLOSE customer_name_csr;
1501 --
1502 -- Get the site use location
1503 --
1504 OPEN site_name_csr;
1505 FETCH site_name_csr INTO l_site_name;
1506 CLOSE site_name_csr;
1507
1508 OPEN party_name_csr;
1509 FETCH party_name_csr INTO l_party_name;
1510 CLOSE party_name_csr;
1511 ELSE
1512 --
1513 -- Get item category if it is passed in
1514 --
1515 IF p_cc_limit_used = 'ITEM' THEN
1516 OPEN item_category_csr;
1517 FETCH item_category_csr INTO l_item_category;
1518 CLOSE item_category_csr;
1519 END IF;
1520 END IF;
1521 --
1522 -- Set hold source
1523 --
1524 l_hold_source_rec.hold_id := 1; -- credit hold
1525 l_hold_source_rec.hold_entity_code := 'O'; -- order hold
1526 l_hold_source_rec.hold_entity_id := p_header_id; -- order header
1527 --
1528 IF Hold_Exists( p_header_id => p_header_id
1529 , p_line_id => p_line_id
1530 , p_credit_hold_level => p_credit_hold_level
1531 ) THEN
1532 G_line_hold_count := G_line_hold_count + 1;
1533 Write_Hold_Message
1534 (
1535 p_calling_action => p_calling_action
1536 , p_cc_limit_used => p_cc_limit_used
1537 , p_cc_profile_used => p_cc_profile_used
1538 , p_order_number => p_order_number
1539 , p_line_number => p_line_number
1540 , p_customer_name => l_customer_name
1541 , p_site_name => l_site_name
1542 , p_party_name => l_party_name
1543 , p_item_category => l_item_category
1544 , x_comment => l_hold_comment
1545 );
1546
1547 G_result_out := 'FAIL' ;
1548 l_hold_source_rec.hold_comment := l_hold_comment;
1549 l_hold_source_rec.line_id := p_line_id;
1550
1551 IF G_debug_flag = 'Y'
1552 THEN
1553 OE_DEBUG_PUB.Add('OEXVCRLB: Hold already applied on Header/Line ID:' ||
1554 p_header_id || '/' || p_line_id, 1);
1555 END IF;
1556
1557 IF NVL(p_calling_action, 'BOOKING') IN ('SHIPPING','PACKING','PICKING')
1558 THEN
1559 IF G_debug_flag = 'Y'
1560 THEN
1561 OE_DEBUG_PUB.ADD('OEXVCRLB: Call Update_Comments_And_Commit');
1562 END IF;
1563 --IF NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) =
1564 --FND_API.G_FALSE THEN --bug6120327
1565 l_cc_result_out := 'FAIL_HOLD';--kadiraju added for Bug# 13768161
1566 Update_Comments_And_Commit
1567 ( p_hold_source_rec => l_hold_source_rec
1568 , x_msg_count => l_msg_count
1569 , x_msg_data => l_msg_data
1570 , x_return_status => l_return_status
1571 );
1572
1573 -- END IF;
1574 IF G_debug_flag = 'Y'
1575 THEN
1576 OE_DEBUG_PUB.ADD('OEXVCRLB: Out Update_Comments_And_Commit');
1577 END IF;
1578
1579 ELSIF NVL( p_calling_action,'BOOKING') IN ('BOOKING','UPDATE','AUTO HOLD')
1580 THEN
1581 IF G_debug_flag = 'Y'
1582 THEN
1583 OE_DEBUG_PUB.ADD('OEXVCRLB: Call OE_Holds_PUB.Update_Hold_Comments directly');
1584 END IF;
1585 --IF NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) =
1586 --FND_API.G_FALSE THEN --bug6120327
1587 l_cc_result_out := 'FAIL_HOLD';--kadiraju added for Bug# 13768161
1588 OE_Holds_PUB.Update_Hold_comments
1589 ( p_hold_source_rec => l_hold_source_rec
1590 , x_msg_count => l_msg_count
1591 , x_msg_data => l_msg_data
1592 , x_return_status => l_return_status
1593 );
1594
1595 --END IF;
1596
1597 IF G_debug_flag = 'Y'
1598 THEN
1599 OE_DEBUG_PUB.ADD('OEXVCRLB: Out OE_Holds_PUB.Update_Hold_Comments directly');
1600 END IF;
1601 END IF;
1602
1603 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1604 RAISE FND_API.G_EXC_ERROR;
1605 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1607 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1608 IF G_debug_flag = 'Y' THEN
1609 OE_DEBUG_PUB.ADD
1610 ('OEXVCRLB: Updated Comments on Header/Line ID:' ||
1611 p_header_id || '/' ||
1612 p_line_id, 1);
1613 END IF;
1614 END IF;
1615 ELSE
1616 IF (Check_Manual_Released_Holds
1617 ( p_calling_action => p_calling_action
1618 , p_credit_hold_level => p_credit_hold_level
1619 ,p_hold_id => 1
1620 ,p_header_id => p_header_id
1621 --,p_line_id => p_line_id
1622 ,p_line_id => top_model_line_id_smc(p_line_id) -- Bug 7673312
1623 ,p_credit_check_rule_rec=>p_credit_check_rule_rec
1624 ) = 'N'
1625 --ER 12363706 start
1626 AND NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) = FND_API.G_FALSE) OR (NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) = FND_API.G_TRUE)
1627 --ER 12363706 end
1628 THEN
1629 G_result_out := 'FAIL' ;
1630
1631 Write_Hold_Message
1632 (
1633 p_calling_action => p_calling_action
1634 , p_cc_limit_used => p_cc_limit_used
1635 , p_cc_profile_used => p_cc_profile_used
1636 , p_order_number => p_order_number
1637 , p_line_number => p_line_number
1638 , p_customer_name => l_customer_name
1639 , p_site_name => l_site_name
1640 , p_item_category => l_item_category
1641 , x_comment => l_hold_comment
1642 );
1643 l_hold_source_rec.hold_comment := l_hold_comment;
1644 l_hold_source_rec.line_id := p_line_id;
1645 --
1646 ------------------------------------------------------------
1647 -- Call for all actions except for the
1648 -- concurrent program credit check processor
1649 IF NVL(p_calling_action, 'BOOKING') IN ('SHIPPING','PACKING','PICKING')
1650 THEN
1651
1652 IF G_debug_flag = 'Y'
1653 THEN
1654 OE_DEBUG_PUB.ADD('OEXVCRLB: Call Apply_hold_and_commit ');
1655 END IF;
1656
1657 --8478151
1658 IF Oe_Globals.G_calling_source = 'ONT' and p_calling_action = 'SHIPPING'
1659 THEN
1660 OE_Credit_Engine_GRP.G_Credit_Profile_Level := p_cc_profile_used; --ER 12363706
1661
1662 OE_Holds_PUB.Apply_Holds
1663 ( p_api_version => 1.0
1664 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1665 , p_hold_source_rec => l_hold_source_rec
1666 , x_msg_count => l_msg_count
1667 , x_msg_data => l_msg_data
1668 , x_return_status => l_return_status
1669 );
1670
1671 OE_Credit_Engine_GRP.G_Credit_Profile_Level := NULL; --ER 12363706
1672 ELSE --8478151
1673 OE_Credit_Engine_GRP.G_Credit_Profile_Level := p_cc_profile_used; --ER 12363706
1674
1675 Apply_hold_and_commit
1676 ( p_hold_source_rec => l_hold_source_rec
1677 , x_msg_count => l_msg_count
1678 , x_msg_data => l_msg_data
1679 , x_return_status => l_return_status
1680 );
1681
1682 OE_Credit_Engine_GRP.G_Credit_Profile_Level := NULL; --ER 12363706
1683 END IF; --8478151
1684
1685
1686 IF G_debug_flag = 'Y'
1687 THEN
1688 OE_DEBUG_PUB.ADD('OEXVCRLB: Out Apply_hold_and_commit ');
1689 END IF;
1690
1691
1692 ELSIF NVL( p_calling_action,'BOOKING') IN ('BOOKING','UPDATE','AUTO HOLD')
1693 THEN
1694 IF G_debug_flag = 'Y'
1695 THEN
1696 OE_DEBUG_PUB.ADD('OEXVCRLB: Call OE_Holds_PUB.Apply_Holds directly');
1697 END IF;
1698 OE_Credit_Engine_GRP.G_Credit_Profile_Level := p_cc_profile_used; --ER 12363706
1699 OE_Holds_PUB.Apply_Holds
1700 ( p_api_version => 1.0
1701 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1702 , p_hold_source_rec => l_hold_source_rec
1703 , x_msg_count => l_msg_count
1704 , x_msg_data => l_msg_data
1705 , x_return_status => l_return_status
1706 );
1707 OE_Credit_Engine_GRP.G_Credit_Profile_Level := NULL; --ER 12363706
1708 IF G_debug_flag = 'Y'
1709 THEN
1710 OE_DEBUG_PUB.ADD('OEXVCRLB: Out OE_Holds_PUB.Apply_Holds directly');
1711 END IF;
1712 END IF;
1713 -------------------------------------------------------
1714 IF G_debug_flag = 'Y' THEN
1715 OE_DEBUG_PUB.ADD('OEXVCRLB: Apply Holds status '|| l_return_status );
1716 END IF;
1717
1718 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1719 RAISE FND_API.G_EXC_ERROR;
1720 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1722 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1723 IF G_debug_flag = 'Y' THEN
1724 OE_DEBUG_PUB.ADD
1725 ('OEXVCRLB: Applied credit check hold on Header/Line ID:' ||
1726 p_header_id || '/' ||
1727 p_line_id, 1);
1728 END IF;
1729 END IF;
1730 l_cc_result_out := 'FAIL_HOLD';
1731 G_line_hold_count := G_line_hold_count + 1;
1732 END IF; -- Check manual holds
1733 END IF; -- Check hold exist
1734 -- The result out is FAIL_NONE for AUTO RELEASE calling action
1735 x_cc_result_out := l_cc_result_out;
1736
1737 IF G_debug_flag = 'Y'
1738 THEN
1739 OE_DEBUG_PUB.Add('OEXVCRLB: Apply_Line_CC_Hold Result = '|| x_cc_result_out );
1740 OE_DEBUG_PUB.Add('OEXVCRLB: Out Apply_Line_CC_Hold');
1741 END IF;
1742
1743
1744 EXCEPTION
1745 WHEN OTHERS THEN
1746 OE_MSG_PUB.Add_Exc_Msg
1747 (G_PKG_NAME, 'Apply_Line_CC_Hold');
1748 RAISE;
1749
1750 END Apply_Line_CC_Hold;
1751
1752 ---------------------------------------------------
1753 -- Apply credit check hold on the specified order
1754 ---------------------------------------------------
1755 /*
1756 ** Bug # 3416932: Made this procedure an Autonomous Trxn
1757 ** Bug # 3462295: Reverted back the autonomous change
1758 */
1759 PROCEDURE Apply_Order_CC_Hold
1760 ( p_header_id IN NUMBER
1761 , p_order_number IN NUMBER
1762 , p_calling_action IN VARCHAR2 DEFAULT 'BOOKING'
1763 , p_cc_limit_used IN VARCHAR2
1764 , p_cc_profile_used IN VARCHAR2
1765 , p_item_category_id IN NUMBER DEFAULT NULL
1766 , p_credit_hold_level IN VARCHAR2
1767 , p_credit_check_rule_rec IN
1768 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
1769 , x_cc_result_out OUT NOCOPY VARCHAR2
1770 )
1771 IS
1772
1773 -- Cursor to select item category
1774 CURSOR item_category_csr IS
1775 SELECT description
1776 FROM mtl_categories
1777 WHERE category_id = p_item_category_id;
1778
1779 l_notification_id NUMBER;
1780 l_item_category VARCHAR2(240):= NULL;
1781 l_cc_result_out VARCHAR2(30) := 'FAIL_NONE';
1782 l_hold_exists VARCHAR2(1) := NULL ;
1783 l_msg_count NUMBER := 0;
1784 l_msg_data VARCHAR2(2000);
1785 l_return_status VARCHAR2(30);
1786 l_hold_comment VARCHAR2(2000);
1787 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type :=
1788 OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
1789 BEGIN
1790 IF G_debug_flag = 'Y'
1791 THEN
1792 OE_DEBUG_PUB.Add('OEXVCRLB: In Apply_Order_CC_Hold');
1793 END IF;
1794 --
1795 -- Get the order number for notification
1796 --
1797 IF p_cc_limit_used = 'ITEM' THEN
1798 OPEN item_category_csr;
1799 FETCH item_category_csr INTO l_item_category;
1800 CLOSE item_category_csr;
1801 END IF;
1802
1803 --
1804 -- Set hold source
1805 --
1806 l_hold_source_rec.hold_id := 1; -- credit hold
1807 l_hold_source_rec.hold_entity_code := 'O'; -- order hold
1808 l_hold_source_rec.hold_entity_id := p_header_id; -- order header
1809 --
1810 IF Hold_Exists( p_header_id => p_header_id
1811 , p_line_id => NULL
1812 , p_credit_hold_level =>
1813 p_credit_hold_level
1814 ) THEN
1815 Write_Order_Hold_Msg
1816 (
1817 p_calling_action => p_calling_action
1818 , p_cc_limit_used => p_cc_limit_used
1819 , p_cc_profile_used => p_cc_profile_used
1820 , p_order_number => p_order_number
1821 , p_item_category => l_item_category
1822 , x_comment => l_hold_comment
1823 );
1824
1825 G_result_out := 'FAIL' ;
1826 l_cc_result_out := 'FAIL_HOLD';--kadiraju added for Bug# 13768161
1827 l_hold_source_rec.hold_comment := l_hold_comment;
1828
1829 IF G_debug_flag = 'Y'
1830 THEN
1831 OE_DEBUG_PUB.Add('OEXVCRLB: Hold already applied on Header ID:' ||
1832 p_header_id, 1);
1833 END IF;
1834 ELSE
1835 IF (Check_Manual_Released_Holds(
1836 p_calling_action => p_calling_action
1837 , p_credit_hold_level =>
1838 p_credit_hold_level
1839 ,p_hold_id => 1
1840 ,p_header_id => p_header_id
1841 ,p_line_id => NULL
1842 ,p_credit_check_rule_rec=>p_credit_check_rule_rec
1843 ) = 'N'
1844 --ER 12363706 start
1845 AND NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) = FND_API.G_FALSE) OR (NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) = FND_API.G_TRUE)
1846 --ER 12363706 end
1847 THEN
1848 G_result_out := 'FAIL' ;
1849
1850 IF G_debug_flag = 'Y'
1851 THEN
1852 OE_DEBUG_PUB.Add( ' No manual release, call Write_order_hold_msg ');
1853 END IF;
1854
1855 G_result_out := 'FAIL' ;
1856
1857 Write_Order_Hold_Msg
1858 (
1859 p_calling_action => p_calling_action
1860 , p_cc_limit_used => p_cc_limit_used
1861 , p_cc_profile_used => p_cc_profile_used
1862 , p_order_number => p_order_number
1863 , p_item_category => l_item_category
1864 , x_comment => l_hold_comment
1865 );
1866 l_hold_source_rec.hold_comment := l_hold_comment;
1867
1868 IF NVL(p_calling_action, 'BOOKING') <> 'AUTO RELEASE' THEN
1869 OE_Credit_Engine_GRP.G_Credit_Profile_Level := p_cc_profile_used; --ER 12363706
1870 OE_Holds_PUB.Apply_Holds
1871 ( p_api_version => 1.0
1872 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1873 , p_hold_source_rec => l_hold_source_rec
1874 , x_msg_count => l_msg_count
1875 , x_msg_data => l_msg_data
1876 , x_return_status => l_return_status
1877 );
1878 OE_Credit_Engine_GRP.G_Credit_Profile_Level := NULL; --ER 12363706
1879 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1880 RAISE FND_API.G_EXC_ERROR;
1881 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1883 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1884 IF G_debug_flag = 'Y' THEN
1885 OE_DEBUG_PUB.ADD
1886 ('OEXVCRLB: Credit check hold applied on header_ID: '||p_header_id, 1);
1887 END IF;
1888 END IF;
1889 l_cc_result_out := 'FAIL_HOLD';
1890 END IF; -- check calling action
1891 END IF; -- Check manual holds
1892 END IF; -- Check hold exist
1893 x_cc_result_out := l_cc_result_out;
1894
1895 IF G_debug_flag = 'Y'
1896 THEN
1897 OE_DEBUG_PUB.ADD('OEXVCRLB: Apply_Order_CC_Hold Result = '
1898 ||l_cc_result_out);
1899 OE_DEBUG_PUB.Add('OEXVCRLB: Out Apply_Order_CC_Hold');
1900 END IF;
1901
1902 EXCEPTION
1903 WHEN OTHERS THEN
1904 OE_MSG_PUB.Add_Exc_Msg
1905 (G_PKG_NAME, 'Apply_Order_CC_Hold' );
1906 RAISE;
1907
1908 END Apply_Order_CC_Hold;
1909
1910 -----------------------------------------
1911 -- Release order level credit check hold
1912 -- in the database.
1913 -----------------------------------------
1914
1915 PROCEDURE Release_Order_CC_Hold
1916 ( p_header_id IN NUMBER
1917 , p_order_number IN NUMBER
1918 , p_calling_action IN VARCHAR2 DEFAULT 'BOOKING'
1919 , p_credit_hold_level IN VARCHAR2
1920 , x_cc_result_out OUT NOCOPY VARCHAR2
1921 )
1922 IS
1923
1924 --ER#7479609 l_hold_entity_id NUMBER := p_header_id;
1925 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE := p_header_id; --ER#7479609
1926 l_hold_id NUMBER;
1927 l_hold_exists VARCHAR2(1);
1928 l_hold_result VARCHAR2(30);
1929 l_msg_count NUMBER := 0;
1930 l_msg_data VARCHAR2(2000);
1931 l_return_status VARCHAR2(30);
1932 l_release_reason VARCHAR2(30);
1933 l_cc_result_out VARCHAR2(30) := 'PASS_NONE';
1934 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type :=
1935 OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
1936 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type :=
1937 OE_HOLDS_PVT.G_MISS_Hold_Release_REC;
1938 l_calling_activity VARCHAR2(50); --ER#7479609
1939 BEGIN
1940 IF G_debug_flag = 'Y'
1941 THEN
1942 OE_DEBUG_PUB.Add('OEXVCRLB: In Release_Order_CC_Hold');
1943 END IF;
1944
1945 l_return_status := FND_API.G_RET_STS_SUCCESS;
1946 --
1947 -- Release credit hold if the calling action is not BOOKING and not
1948 -- UPDATE with background credit check set.
1949 --
1950 IF hold_exists( p_header_id => p_header_id
1951 , p_line_id => NULL
1952 , p_credit_hold_level =>
1953 p_credit_hold_level
1954 )
1955 THEN
1956 IF NVL(p_calling_action, 'BOOKING') <> 'AUTO HOLD' THEN
1957 l_hold_source_rec.hold_id := 1; -- Credit Checking hold
1958 l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
1959 l_hold_source_rec.HOLD_ENTITY_ID := p_header_id;
1960
1961 l_hold_release_rec.release_reason_code := 'PASS_CREDIT';
1962 l_hold_release_rec.release_comment := 'Credit Check Engine';
1963 l_hold_release_rec.created_by := 1; -- indicate non-manual release
1964
1965 --ER#7479609 start
1966 IF OE_Verify_Payment_PUB.G_init_calling_action = 'AUTO RELEASE' THEN
1967 l_calling_activity := 'Credit Check Processor';
1968 ELSE
1969 l_calling_activity := InitCap(OE_Verify_Payment_PUB.G_init_calling_action);
1970 END IF;
1971
1972 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_ACT_COM');
1973 FND_MESSAGE.Set_Token('CALLING_ACTIVITY',l_calling_activity);
1974 FND_MESSAGE.Set_Token('CREDIT_CHECK_RULE',OE_Verify_Payment_PUB.G_credit_check_rule);
1975
1976 l_hold_release_rec.release_comment := l_hold_release_rec.release_comment||SUBSTR(FND_MESSAGE.GET,1,2000);
1977
1978 --ER#7479609 end
1979
1980
1981 OE_Holds_PUB.Release_Holds
1982 ( p_api_version => 1.0
1983 , p_hold_source_rec => l_hold_source_rec
1984 , p_hold_release_rec => l_hold_release_rec
1985 , x_msg_count => l_msg_count
1986 , x_msg_data => l_msg_data
1987 , x_return_status => l_return_status
1988 );
1989 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1990 RAISE FND_API.G_EXC_ERROR;
1991 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1993 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1994 Write_Order_Release_Msg(
1995 p_calling_action => p_calling_action
1996 , p_order_number => p_order_number
1997 );
1998 END IF;
1999 l_cc_result_out := 'PASS_REL';
2000
2001 IF G_debug_flag = 'Y'
2002 THEN
2003 OE_DEBUG_PUB.ADD('OEXVCRLB: Released credit check hold on Header ID:'
2004 || p_header_id, 1);
2005 END IF;
2006 END IF; -- check calling action
2007 END IF; -- hold exist
2008 x_cc_result_out := l_cc_result_out;
2009
2010 IF G_debug_flag = 'Y'
2011 THEN
2012 OE_DEBUG_PUB.Add('OEXVCRLB: Out Release_Order_CC_Hold');
2013 END IF;
2014
2015 EXCEPTION
2016 WHEN OTHERS THEN
2017 OE_MSG_PUB.Add_Exc_Msg
2018 (G_PKG_NAME, 'Release_Order_CC_Hold');
2019 RAISE;
2020
2021 END Release_Order_CC_Hold;
2022
2023 -----------------------------------------------------
2024 -- Apply item catagory hold on lines within the given
2025 -- bill-to site that have items belonging to the
2026 -- specified item category.
2027 -----------------------------------------------------
2028
2029 PROCEDURE Apply_Item_Category_Holds
2030 ( p_header_id IN NUMBER
2031 , p_item_category_id IN NUMBER
2032 , p_lines IN OE_CREDIT_CHECK_UTIL.lines_Rec_tbl_type
2033 , p_holds_table IN OUT NOCOPY Line_Holds_Tbl_Rectype
2034 )
2035 IS
2036 i BINARY_INTEGER := 1;
2037 l_return_status VARCHAR2(30);
2038 BEGIN
2039
2040 IF G_debug_flag = 'Y'
2041 THEN
2042 OE_DEBUG_PUB.Add('OEXVCRLB: In Apply_Item_Category_Holds');
2043 END IF;
2044
2045 l_return_status := FND_API.G_RET_STS_SUCCESS;
2046 FOR i IN 1..p_lines.count LOOP
2047 IF p_item_category_id = p_lines(i).item_category_id THEN
2048 OE_DEBUG_PUB.Add('Line ID '||p_lines(i).line_id
2049 ||' fails ITEM limit ck',1);
2050 Update_Holds_Table
2051 ( p_holds_table => p_holds_table
2052 , p_line_id => p_lines(i).line_id
2053 , p_hold => 'ITEM'
2054 , p_cc_limit_used => 'ITEM'
2055 , p_cc_profile_used => 'CATEGORY'
2056 , p_item_category_id => p_item_category_id
2057 , x_return_status => l_return_status
2058 );
2059 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2060 RAISE FND_API.G_EXC_ERROR;
2061 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2062 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2063 END IF;
2064 END IF;
2065 END LOOP;
2066
2067 OE_DEBUG_PUB.Add('OEXVCRLB: Out Apply_Item_Category_Holds');
2068
2069 EXCEPTION
2070 WHEN OTHERS THEN
2071 OE_MSG_PUB.Add_Exc_Msg
2072 (G_PKG_NAME, 'Apply_Item_Category_Holds');
2073 RAISE;
2074
2075 END Apply_Item_Category_Holds;
2076
2077 ---------------------------------------------------
2078 -- Update the plsql holds table to add hold info |
2079 -- for each line that do not already have ITEM |
2080 -- hold information. |
2081 ---------------------------------------------------
2082
2083 PROCEDURE Apply_Other_Holds
2084 ( p_header_id IN NUMBER
2085 , p_customer_id IN NUMBER
2086 , p_site_use_id IN NUMBER
2087 , p_party_id IN NUMBER
2088 , p_cc_limit_used IN VARCHAR2
2089 , p_cc_profile_used IN VARCHAR2
2090 , p_holds_table IN OUT NOCOPY Line_Holds_Tbl_Rectype
2091 )
2092 IS
2093 l_return_status VARCHAR2(30);
2094 BEGIN
2095 IF G_debug_flag = 'Y'
2096 THEN
2097 OE_DEBUG_PUB.Add('OEXVCRLB: In Apply_Other_Holds');
2098 END IF;
2099
2100 Update_Holds_Table
2101 ( p_holds_table => p_holds_table
2102 , p_hold => 'OTHER'
2103 , p_cc_limit_used => p_cc_limit_used
2104 , p_cc_profile_used => p_cc_profile_used
2105 , p_customer_id => p_customer_id
2106 , p_site_use_id => p_site_use_id
2107 , p_party_id => p_party_id
2108 , x_return_status => l_return_status
2109 );
2110
2111 IF G_debug_flag = 'Y'
2112 THEN
2113 OE_DEBUG_PUB.Add('OEXVCRLB: Out Apply_Other_Holds');
2114 END IF;
2115
2116 EXCEPTION
2117 WHEN OTHERS THEN
2118 OE_MSG_PUB.Add_Exc_Msg
2119 (G_PKG_NAME, 'Apply_Other_Holds');
2120 RAISE;
2121
2122 END Apply_Other_Holds;
2123
2124 ---------------------------------------------------------
2125 -- Release credit check holds on order lines belonging to
2126 -- a bill-to site
2127 ---------------------------------------------------------
2128
2129 PROCEDURE Release_Line_CC_Hold
2130 ( p_header_id IN NUMBER
2131 , p_order_number IN NUMBER
2132 , p_line_id IN NUMBER
2133 , p_line_number IN NUMBER
2134 , p_calling_action IN VARCHAR2 DEFAULT NULL
2135 , p_credit_hold_level IN VARCHAR2
2136 , x_cc_result_out OUT NOCOPY VARCHAR2
2137 )
2138 IS
2139 --ER#7479609 l_hold_entity_id NUMBER := p_header_id;
2140 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE := p_header_id; --ER#7479609
2141 l_hold_id NUMBER;
2142 l_hold_exists VARCHAR2(1);
2143 l_hold_result VARCHAR2(30);
2144 l_msg_count NUMBER := 0;
2145 l_msg_data VARCHAR2(2000);
2146 l_return_status VARCHAR2(30);
2147 l_release_reason VARCHAR2(30);
2148 l_cc_result_out VARCHAR2(30) := 'PASS_NONE';
2149
2150 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type :=
2151 OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
2152 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type :=
2153 OE_HOLDS_PVT.G_MISS_Hold_Release_REC;
2154 l_calling_activity VARCHAR2(50); --ER#7479609
2155
2156 BEGIN
2157
2158 IF G_debug_flag = 'Y'
2159 THEN
2160 OE_DEBUG_PUB.ADD('OEXVCRLB: In Release_Line_CC_Hold');
2161 OE_DEBUG_PUB.ADD('Processing line ID = '||
2162 p_line_id );
2163 END IF;
2164
2165 l_return_status := FND_API.G_RET_STS_SUCCESS;
2166
2167 -- Holds Issue 1979918
2168 --------------------------------------------------------------------
2169 -- During the credit checking is at Line level ( PASS scenario ),
2170 -- IF there exist a credit checking Hold already exists at Header
2171 -- that hold must be released to begin with.
2172 -- If not the Check_holds API will ALWAYS return YES while
2173 -- checking for existing holds at line level
2174 -- REsult out will be HDR_HOLD for the first time
2175 ---------------------------------------------------------------------
2176
2177 IF hold_exists( p_header_id => p_header_id
2178 , p_line_id => p_line_id
2179 , p_credit_hold_level => p_credit_hold_level
2180 )
2181 THEN
2182 IF NVL(p_calling_action,'BOOKING') <> 'AUTO HOLD' THEN
2183 l_hold_source_rec.hold_id := 1; -- Credit Checking hold
2184 l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
2185 l_hold_source_rec.HOLD_ENTITY_ID := p_header_id;
2186 l_hold_source_rec.line_id := p_line_id;
2187
2188 l_hold_release_rec.release_reason_code := 'PASS_CREDIT';
2189 l_hold_release_rec.release_comment := 'Credit Check Engine' ;
2190 l_hold_release_rec.created_by := 1; -- hold release by system
2191
2192 --ER#7479609 start
2193 IF OE_Verify_Payment_PUB.G_init_calling_action = 'AUTO RELEASE' THEN
2194 l_calling_activity := 'Credit Check Processor';
2195 ELSE
2196 l_calling_activity := InitCap(OE_Verify_Payment_PUB.G_init_calling_action);
2197 END IF;
2198
2199 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_ACT_COM');
2200 FND_MESSAGE.Set_Token('CALLING_ACTIVITY',l_calling_activity);
2201 FND_MESSAGE.Set_Token('CREDIT_CHECK_RULE',OE_Verify_Payment_PUB.G_credit_check_rule);
2202
2203 l_hold_release_rec.release_comment := l_hold_release_rec.release_comment||SUBSTR(FND_MESSAGE.GET,1,2000);
2204 --ER#7479609 end
2205
2206 OE_Holds_PUB.Release_Holds
2207 ( p_api_version => 1.0
2208 , p_hold_source_rec => l_hold_source_rec
2209 , p_hold_release_rec => l_hold_release_rec
2210 , x_msg_count => l_msg_count
2211 , x_msg_data => l_msg_data
2212 , x_return_status => l_return_status
2213 );
2214
2215 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2216 IF NVL(G_hdr_hold_released,'N') = 'N'
2217 THEN
2218 l_cc_result_out := 'HDR_HOLD' ;
2219 ELSE
2220 RAISE FND_API.G_EXC_ERROR;
2221 END IF;
2222 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2223 IF NVL(G_hdr_hold_released,'N') = 'N'
2224 THEN
2225 l_cc_result_out := 'HDR_HOLD' ;
2226 ELSE
2227 RAISE FND_API.G_EXC_ERROR;
2228 END IF;
2229 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
2230 Write_Release_Message(
2231 p_calling_action => p_calling_action
2232 , p_order_number => p_order_number
2233 , p_line_number => p_line_number
2234 );
2235 l_cc_result_out := 'PASS_REL';
2236 G_release_status := 'RELEASED' ;
2237 END IF;
2238 END IF; -- check calling action
2239 ELSE
2240 IF G_debug_flag = 'Y'
2241 THEN
2242 OE_DEBUG_PUB.ADD(' No Hold exist to be Released ');
2243 END IF;
2244 END IF; -- Holds Exist IF
2245
2246 x_cc_result_out := l_cc_result_out;
2247
2248 IF G_debug_flag = 'Y'
2249 THEN
2250 OE_DEBUG_PUB.ADD('x_cc_result_out = '|| x_cc_result_out );
2251 OE_DEBUG_PUB.ADD('OEXVCRLB: Out Release_Line_CC_Hold');
2252 END IF;
2253
2254 EXCEPTION
2255 WHEN OTHERS THEN
2256 OE_MSG_PUB.Add_Exc_Msg
2257 (G_PKG_NAME, 'Release_Line_CC_Hold');
2258 RAISE;
2259
2260 END Release_Line_CC_Hold;
2261
2262 ----------------------------------------------
2263 -- additional task - made the procedure |
2264 -- Check_trx_Limit local to this package and |
2265 -- added p_credit_rule_id as an additional |
2266 -- input parameter |
2267 ----------------------------------------------
2268
2269 PROCEDURE Check_Trx_Limit
2270 ( p_header_rec IN OE_ORDER_PUB.header_rec_type
2271 , p_customer_id IN NUMBER
2272 , p_site_use_id IN NUMBER
2273 , p_credit_level IN VARCHAR2
2274 , p_credit_check_rule_rec IN
2275 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
2276 , p_system_parameter_rec IN
2277 OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
2278 , p_limit_curr_code IN VARCHAR2
2279 , p_trx_credit_limit IN NUMBER
2280 , x_cc_result_out OUT NOCOPY VARCHAR2
2281 , x_return_status OUT NOCOPY VARCHAR2
2282 , x_conversion_status OUT NOCOPY OE_CREDIT_CHECK_UTIL.curr_tbl_type
2283 )
2284 IS
2285
2286 l_order_value NUMBER;
2287 l_customer_id NUMBER;
2288 -- 13987649
2289 l_credit_check_rule_rec OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type;
2290
2291 BEGIN
2292 IF G_debug_flag = 'Y'
2293 THEN
2294 OE_DEBUG_PUB.ADD('OEXVCRLB: In Check_Trx_Limit');
2295 OE_DEBUG_PUB.ADD(' ', 2);
2296 OE_DEBUG_PUB.ADD(' ---------------------------------------- ', 2);
2297 OE_DEBUG_PUB.ADD(' Header ID = '|| p_header_rec.header_id, 2);
2298 OE_DEBUG_PUB.ADD(' p_customer_id = '|| p_customer_id, 2);
2299 OE_DEBUG_PUB.ADD(' p_site_use_id = '|| p_site_use_id, 2);
2300 OE_DEBUG_PUB.ADD(' p_credit_level = '|| p_credit_level, 2);
2301 OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code, 2);
2302 OE_DEBUG_PUB.ADD(' p_trx_credit_limit = '|| p_trx_credit_limit,2);
2303 END IF;
2304
2305 -- Initialize return status to success
2306 x_return_status := FND_API.G_RET_STS_SUCCESS;
2307 -- Default to Pass
2308 x_cc_result_out := 'PASS';
2309
2310 -- Start 13987649
2311 l_credit_check_rule_rec := p_credit_check_rule_rec;
2312 -- End 13987649
2313
2314 ----------------------------------------------
2315 -- additional task - Read the value of |
2316 -- include_tax_flag from credit check rule |
2317 -- and calculate the value of l_order_values |
2318 -- accordingly. If the value of |
2319 -- include_tax_flag is NULL that means it is |
2320 -- 'No' |
2321 ----------------------------------------------
2322
2323 ----------------------------------------------
2324 -- Do not include lines with payment term |
2325 -- that have credit check flag = N. NULL |
2326 -- means Y. |
2327 ----------------------------------------------
2328 IF p_credit_level = 'CUSTOMER'
2329 THEN
2330 l_customer_id := p_customer_id ;
2331 -- Start 13987649, For Party level
2332 -- Party Line Level Check also to consider entire Order Amount
2333 ELSIF p_credit_level = 'PARTY'
2334 THEN
2335 l_credit_check_rule_rec.credit_check_level_code := 'ORDER';
2336 -- End 13987649, For Party level
2337 ELSE
2338 l_customer_id := NULL;
2339 END IF;
2340
2341 IF G_debug_flag = 'Y'
2342 THEN
2343 OE_DEBUG_PUB.ADD(' l_customer_id = '|| l_customer_id );
2344 OE_DEBUG_PUB.ADD(' Call GET_transaction_amount ' );
2345 END IF;
2346
2347 OE_CREDIT_CHECK_UTIL.GET_transaction_amount
2348 ( p_header_id => p_header_rec.header_id
2349 , p_transaction_curr_code => p_header_rec.transactional_curr_code
2350 -- , p_credit_check_rule_rec => p_credit_check_rule_rec -- 13987649
2351 , p_credit_check_rule_rec => l_credit_check_rule_rec -- 13987649
2352 , p_system_parameter_rec => p_system_parameter_rec
2353 , p_customer_id => l_customer_id
2354 , p_site_use_id => p_site_use_id
2355 , p_limit_curr_code => p_limit_curr_code
2356 , x_amount => l_order_value
2357 , x_conversion_status => x_conversion_status
2358 , x_return_status => x_return_status
2359 );
2360
2361 IF G_debug_flag = 'Y'
2362 THEN
2363 OE_DEBUG_PUB.ADD(' Out of GET with status '
2364 || x_return_status );
2365 OE_DEBUG_PUB.ADD(' ERR curr tbl count = '
2366 || x_conversion_status.COUNT );
2367 END IF;
2368
2369 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2370 RAISE FND_API.G_EXC_ERROR;
2371 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2373 END IF;
2374
2375 IF G_debug_flag = 'Y'
2376 THEN
2377 OE_DEBUG_PUB.ADD(' l_order_value = '|| l_order_value );
2378 END IF;
2379
2380
2381 IF l_order_value > NVL(p_trx_credit_limit, l_order_value) THEN
2382 x_cc_result_out := 'FAIL';
2383 END IF;
2384
2385 IF x_conversion_status.COUNT > 0
2386 THEN
2387 x_cc_result_out := 'FAIL';
2388
2389 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
2390 FND_MESSAGE.Set_Token('FROM',p_header_rec.transactional_curr_code);
2391 FND_MESSAGE.Set_Token('TO',p_limit_curr_code );
2392 FND_MESSAGE.Set_Token('CONV',
2393 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'));
2394 OE_Credit_Engine_GRP.G_currency_error_msg :=
2395 SUBSTR(FND_MESSAGE.GET,1,1000) ;
2396
2397 G_result_out := 'FAIL' ;
2398 x_cc_result_out := 'FAIL' ;
2399
2400 IF p_credit_check_rule_rec.credit_hold_level_code = 'ORDER'
2401 THEN
2402 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
2403 FND_MESSAGE.Set_Token('FROM',p_header_rec.transactional_curr_code);
2404 FND_MESSAGE.Set_Token('TO',p_limit_curr_code );
2405 FND_MESSAGE.Set_Token('CONV',
2406 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'));
2407
2408 OE_MSG_PUB.ADD;
2409 x_return_status := FND_API.G_RET_STS_ERROR;
2410
2411 END IF;
2412
2413
2414 END IF;
2415
2416 IF G_debug_flag = 'Y'
2417 THEN
2418 OE_DEBUG_PUB.ADD(' x_cc_result_out = '|| x_cc_result_out );
2419 OE_DEBUG_PUB.ADD(' x_return_status = '|| x_return_status);
2420 OE_DEBUG_PUB.ADD(' ---------------------------------------- ' );
2421 OE_DEBUG_PUB.ADD(' ' );
2422 OE_DEBUG_PUB.ADD('OEXVCRLB: Out Check_Trx_Limit');
2423 END IF;
2424 EXCEPTION
2425 WHEN others THEN
2426 OE_DEBUG_PUB.Add('Check_Trx_Limit: Other exceptions');
2427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2428 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2429 OE_MSG_PUB.Add_Exc_Msg
2430 ( G_PKG_NAME
2431 , 'Check_Trx_Limit'
2432 );
2433 END IF;
2434 OE_DEBUG_PUB.ADD( SUBSTR(SQLERRM,1,300) ,1 );
2435 END Check_Trx_Limit;
2436
2437 -----------------------------------------------------+
2438 -- A trx is subject to credit check if all the |
2439 -- following four conditions are true: |
2440 -- 1. related credit rule available for the trx type |
2441 -- 2. credit check enabled for the payment term |
2442 -- 3. credit check enabled for site or cust |
2443 -- 4. credit limits available for site or cust |
2444 -- When true, the procedure returns limits/other info|
2445 ------------------------------------------------------
2446
2447 PROCEDURE Validate_other_credit_check
2448 ( p_header_rec IN OE_ORDER_PUB.header_rec_type
2449 , p_customer_id IN NUMBER
2450 , p_site_use_id IN NUMBER
2451 , p_calling_action IN VARCHAR2 := 'BOOKING'
2452 , p_party_id IN NUMBER
2453 , p_credit_check_rule_rec IN
2454 OE_Credit_Check_Util.OE_credit_rules_rec_type
2455 , x_check_order_flag OUT NOCOPY VARCHAR2
2456 , x_credit_check_lvl_out OUT NOCOPY VARCHAR2
2457 , x_default_limit_flag OUT NOCOPY VARCHAR2
2458 , x_limit_curr_code OUT NOCOPY VARCHAR2
2459 , x_overall_credit_limit OUT NOCOPY NUMBER
2460 , x_trx_credit_limit OUT NOCOPY NUMBER
2461 , x_usage_curr OUT NOCOPY OE_CREDIT_CHECK_UTIL.curr_tbl_type
2462 , x_include_all_flag OUT NOCOPY VARCHAR2
2463 , x_return_status OUT NOCOPY VARCHAR2
2464 , x_global_exposure_flag OUT NOCOPY VARCHAR2
2465 , x_credit_limit_entity_id OUT NOCOPY NUMBER
2466 )
2467 IS
2468
2469 l_site_use_id NUMBER;
2470 l_customer_id NUMBER;
2471
2472 BEGIN
2473
2474 x_check_order_flag := 'Y';
2475 x_return_status := FND_API.G_RET_STS_SUCCESS;
2476 x_global_exposure_flag := 'N' ;
2477
2478 IF G_debug_flag = 'Y'
2479 THEN
2480 OE_DEBUG_PUB.ADD('OEXVCRLB: In Validate_other_credit_check');
2481 OE_DEBUG_PUB.ADD(' ' );
2482 OE_DEBUG_PUB.ADD(' ---------------------------------------- ' );
2483 OE_DEBUG_PUB.ADD(' Header ID = '|| p_header_rec.header_id );
2484 OE_DEBUG_PUB.ADD(' p_customer_id = '|| p_customer_id );
2485 OE_DEBUG_PUB.ADD(' p_site_use_id = '|| p_site_use_id );
2486 OE_DEBUG_PUB.ADD(' p_party_id = '|| p_party_id );
2487 OE_DEBUG_PUB.ADD(' p_calling_action = '|| p_calling_action);
2488 END IF;
2489
2490 ------------------------------------------------
2491 -- Program Logic: |
2492 -- 1. get site-level limit for the trx |
2493 -- 2. If (1) fails, get-customer-level limit |
2494 -----------------------------------------------|
2495 -- level | data | meaning |
2496 -----------------------------------------------|
2497 -- site | credit_check | stop. credit check |
2498 -- | flag = 'N' | not reqd for the trx |
2499 -----------------------------------------------|
2500 -- site | trx limit & | check customer |
2501 -- | overall limit| limits (and default |
2502 -- | are null | limit for the org) |
2503 -----------------------------------------------|
2504 -- cust/ | credit_check | stop. credit check |
2505 -- org | flag = 'N' | not reqd for the trx |
2506 -----------------------------------------------|
2507 -- cust/ | trx limit & | stop. credit check |
2508 -- org | overall limit| not reqd for the trx |
2509 -- | are null | |
2510 -----------------------------------------------|
2511 -- Note: |
2512 -- all rules of customer limits apply to the |
2513 -- default limits of the operating unit |
2514 -- [a 11.5.3 feature] |
2515 ------------------------------------------------
2516
2517 OE_CREDIT_CHECK_UTIL.Get_Limit_Info
2518 ( p_header_id => p_header_rec.header_id
2519 , p_entity_type => 'SITE'
2520 , p_entity_id => p_site_use_id
2521 , p_cust_account_id => p_customer_id
2522 , p_party_id => p_party_id
2523 , p_trx_curr_code => p_header_rec.transactional_curr_code
2524 , p_suppress_unused_usages_flag => 'N'
2525 , p_navigate_to_next_level => 'Y'
2526 , p_precalc_exposure_used =>
2527 p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG
2528 , x_limit_curr_code => x_limit_curr_code
2529 , x_trx_limit => x_trx_credit_limit
2530 , x_overall_limit => x_overall_credit_limit
2531 , x_include_all_flag => x_include_all_flag
2532 , x_usage_curr_tbl => x_usage_curr
2533 , x_default_limit_flag => x_default_limit_flag
2534 , x_global_exposure_flag => x_global_exposure_flag
2535 , x_credit_limit_entity_id => x_credit_limit_entity_id
2536 , x_credit_check_level => x_credit_check_lvl_out
2537 );
2538
2539
2540 IF G_debug_flag = 'Y'
2541 THEN
2542 OE_DEBUG_PUB.ADD(' after Get_Limit_Info ');
2543 END IF;
2544
2545 IF (x_trx_credit_limit IS NULL AND
2546 x_overall_credit_limit IS NULL )
2547 THEN
2548 x_global_exposure_flag := 'N' ;
2549 x_check_order_flag := 'N' ;
2550 x_credit_limit_entity_id := NULL;
2551 x_credit_check_lvl_out := NULL ;
2552 END IF;
2553
2554 IF G_debug_flag = 'Y'
2555 THEN
2556 OE_DEBUG_PUB.ADD(' ');
2557 OE_DEBUG_PUB.Add(' ');
2558 OE_DEBUG_PUB.Add(' Result from credit profile check ');
2559 OE_DEBUG_PUB.Add(' -------------------------------------------');
2560 OE_DEBUG_PUB.Add(' ');
2561 OE_DEBUG_PUB.Add('x_credit_check_lvl_out = '|| x_credit_check_lvl_out);
2562 OE_DEBUG_PUB.Add('x_default_limit_flag = '|| x_default_limit_flag);
2563 OE_DEBUG_PUB.Add('x_limit_curr_code = '|| x_limit_curr_code);
2564 OE_DEBUG_PUB.Add('x_overall_credit_limit = '|| x_overall_credit_limit);
2565 OE_DEBUG_PUB.Add('x_trx_credit_limit = '|| x_trx_credit_limit);
2566 OE_DEBUG_PUB.Add('x_include_all_flag = '|| x_include_all_flag);
2567 OE_DEBUG_PUB.Add('x_global_exposure_flag = '|| x_global_exposure_flag );
2568 OE_DEBUG_PUB.Add('x_credit_limit_entity_id =' ||
2569 x_credit_limit_entity_id );
2570 OE_DEBUG_PUB.ADD(' ');
2571 OE_DEBUG_PUB.ADD('****** List of associated Usage currency rules **** ');
2572 OE_DEBUG_PUB.ADD(' ');
2573 END IF;
2574
2575 FOR K IN 1..x_usage_curr.COUNT
2576 LOOP
2577 IF G_debug_flag = 'Y'
2578 THEN
2579 OE_DEBUG_PUB.Add(' Usage currency ' || k || ' => ' ||
2580 x_usage_curr(K).usage_curr_code );
2581 END IF;
2582 END LOOP ;
2583
2584 IF G_debug_flag = 'Y'
2585 THEN
2586 OE_DEBUG_PUB.ADD(' ');
2587 OE_DEBUG_PUB.ADD('**************** End of List *********************** ');
2588 OE_DEBUG_PUB.Add('OEXVCRLB: Out Validate_other_credit_check');
2589 END IF;
2590 EXCEPTION
2591 WHEN OTHERS THEN
2592 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2593 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2594 OE_MSG_PUB.Add_Exc_Msg
2595 ( G_PKG_NAME
2596 , 'Validate_other_credit_check'
2597 );
2598 END IF;
2599 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2600 OE_DEBUG_PUB.ADD( SUBSTR(SQLERRM,1,300) ,1 );
2601 END Validate_other_credit_check;
2602
2603 -----------------------------------------------------------
2604 -- PROCEDURE: Check_Order_lines_exposure PUBLIC
2605 -- DESCRIPTION: Calculate the exposure and compare against
2606 -- the overall credit limits to determine
2607 -- credit check status (PASS or FAIL).
2608 -- The calling_action can be the following:
2609 -- BOOKING - Called when booking an order
2610 -- UPDATE - Called when order is updated
2611 -- SHIPPING - Called from shipping
2612 -- PACKING
2613 -- PICKING
2614 -- AUTO - obsoleted. Was called by credit check processor
2615 -- AUTO HOLD - Called by credit check processor for holds
2616 -- AUTO RELEASE - Called by credit check processor for release
2617 -----------------------------------------------------------
2618 PROCEDURE Check_Order_lines_exposure
2619 ( p_customer_id IN NUMBER
2620 , p_site_use_id IN NUMBER
2621 , p_header_id IN NUMBER
2622 , p_credit_level IN VARCHAR2
2623 , p_limit_curr_code IN VARCHAR2
2624 , p_overall_credit_limit IN NUMBER
2625 , p_calling_action IN VARCHAR2
2626 , p_usage_curr IN
2627 OE_CREDIT_CHECK_UTIL.curr_tbl_type
2628 , p_include_all_flag IN VARCHAR2 DEFAULT 'N'
2629 , p_holds_rel_flag IN VARCHAR2 DEFAULT 'N'
2630 , p_default_limit_flag IN VARCHAR2 DEFAULT 'N'
2631 , p_credit_check_rule_rec IN
2632 OE_Credit_Check_Util.OE_credit_rules_rec_type
2633 , p_system_parameter_rec IN
2634 OE_Credit_Check_Util.OE_systems_param_rec_type
2635 , p_global_exposure_flag IN VARCHAR2 := 'N'
2636 , p_party_id IN NUMBER
2637 , p_credit_limit_entity_id IN NUMBER
2638 , x_total_exposure OUT NOCOPY NUMBER
2639 , x_cc_result_out OUT NOCOPY VARCHAR2
2640 , x_error_curr_tbl OUT NOCOPY
2641 OE_CREDIT_CHECK_UTIL.curr_tbl_type
2642 , x_return_status OUT NOCOPY VARCHAR2
2643 )
2644 IS
2645 l_customer_id NUMBER;
2646 l_site_id NUMBER;
2647 l_current_order_value NUMBER := 0 ;
2648
2649 l_order_amount NUMBER ;
2650 l_order_hold_amount NUMBER ;
2651 l_ar_amount NUMBER ;
2652
2653 BEGIN
2654
2655 IF G_debug_flag = 'Y'
2656 THEN
2657 OE_DEBUG_PUB.Add('OEXVCRLB: IN Check_Order_lines_exposure ');
2658 OE_DEBUG_PUB.Add(' ');
2659 OE_DEBUG_PUB.Add('-******---------------********---------------**********--');
2660 OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id );
2661 OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id );
2662 OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id );
2663 OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level );
2664 OE_DEBUG_PUB.Add('p_limit_curr_code = '||
2665 p_limit_curr_code );
2666 OE_DEBUG_PUB.Add('p_include_all_flag = '||
2667 p_include_all_flag );
2668 OE_DEBUG_PUB.Add('p_default_limit_flag = '||
2669 p_default_limit_flag );
2670 OE_DEBUG_PUB.Add('p_overall_credit_limit = '||
2671 p_overall_credit_limit );
2672 OE_DEBUG_PUB.Add('p_global_exposure_flag = '||
2673 p_global_exposure_flag );
2674 OE_DEBUG_PUB.Add('p_credit_limit_entity_id => '||
2675 p_credit_limit_entity_id);
2676 OE_DEBUG_PUB.Add('-******---------------********---------------**********--');
2677 OE_DEBUG_PUB.Add(' ');
2678 END IF;
2679
2680 l_current_order_value := 0 ;
2681
2682 IF p_credit_level = 'PARTY'
2683 THEN
2684 l_customer_id := NULL ;
2685 l_site_id := NULL;
2686
2687 ELSIF p_credit_level = 'CUSTOMER'
2688 THEN
2689 l_customer_id := p_customer_id ;
2690 l_site_id := NULL;
2691 ELSE
2692 l_customer_id := p_customer_id ;
2693 l_site_id := p_site_use_id ;
2694
2695 END IF;
2696
2697 IF p_overall_credit_limit IS NOT NULL -- bug 4351533
2698 THEN
2699
2700 ----------------------------------------------------------
2701 -- Set the default behaviour to pass credit check |
2702 -- exposure |
2703 ----------------------------------------------------------
2704
2705 x_return_status := FND_API.G_RET_STS_SUCCESS;
2706 x_total_exposure := 0 ;
2707 l_current_order_value := 0 ;
2708
2709 OE_DEBUG_PUB.ADD( ' Call Get_Exposure ');
2710
2711 OE_CREDIT_EXPOSURE_PVT.Get_Exposure
2712 ( p_customer_id => l_customer_id
2713 , p_site_use_id => l_site_id
2714 , p_header_id => p_header_id
2715 , p_party_id => p_credit_limit_entity_id
2716 , p_credit_check_rule_rec => p_credit_check_rule_rec
2717 , p_system_parameters_rec => p_system_parameter_rec
2718 , p_limit_curr_code => p_limit_curr_code
2719 , p_usage_curr_tbl => p_usage_curr
2720 , p_include_all_flag => p_include_all_flag
2721 , p_global_exposure_flag => p_global_exposure_flag
2722 , p_need_exposure_details => 'N'
2723 , x_total_exposure => x_total_exposure
2724 , x_order_amount => l_order_amount
2725 , x_order_hold_amount => l_order_hold_amount
2726 , x_ar_amount => l_ar_amount
2727 , x_return_status => x_return_status
2728 , x_error_curr_tbl => x_error_curr_tbl
2729 );
2730
2731 IF G_debug_flag = 'Y'
2732 THEN
2733 OE_DEBUG_PUB.Add('Out of Get_Exposure- Precalculated exposure ');
2734 OE_DEBUG_PUB.Add('x_return_status = '|| x_return_status );
2735 OE_DEBUG_PUB.Add('x_total_exposure = '|| x_total_exposure );
2736 OE_DEBUG_PUB.Add('Error table count = '|| x_error_curr_tbl.COUNT );
2737 END IF;
2738
2739 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2740 RAISE FND_API.G_EXC_ERROR;
2741 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2743 END IF;
2744
2745
2746 -- BUG Fix 2338145
2747 -- Get the current order amount to be included into the
2748 -- pre-calc exposure during booking action
2749
2750 -- Bug fix 2787722
2751 -- The current bill-tosite amount should also be included
2752 -- for Non-Booking actions if the
2753 -- credit check rule does notInclude
2754 -- OM Uninvoiced Orders exposure
2755
2756
2757 l_current_order_value := 0 ;
2758
2759 IF NVL(p_calling_action, 'BOOKING') = 'BOOKING'
2760 and NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) =
2761 FND_API.G_FALSE
2762 THEN
2763 l_current_order_value :=
2764 NVL(OE_CREDIT_CHECK_UTIL.g_current_order_value,0) ;
2765 ELSE
2766 IF NVL(p_credit_check_rule_rec.uninvoiced_orders_flag,'N') = 'N'
2767 THEN
2768 l_current_order_value :=
2769 NVL(OE_CREDIT_CHECK_UTIL.g_current_order_value,0) ;
2770 ELSE
2771 l_current_order_value := 0 ;
2772 END IF;
2773 END IF;
2774
2775 IF G_debug_flag = 'Y'
2776 THEN
2777 OE_DEBUG_PUB.Add('l_current_order_value => '
2778 || l_current_order_value );
2779 END IF;
2780
2781
2782
2783 x_total_exposure := NVL(l_current_order_value,0) + NVL(x_total_exposure,0);
2784
2785 ---------------------------------------------------
2786 -- compare limit and exposure |
2787 ---------------------------------------------------
2788
2789 IF G_debug_flag = 'Y'
2790 THEN
2791 OE_DEBUG_PUB.Add('x_total_exposure = '|| x_total_exposure );
2792 OE_DEBUG_PUB.Add('p_overall_credit_limit = '|| p_overall_credit_limit );
2793 END IF;
2794
2795 IF NVL(x_total_exposure,0) > p_overall_credit_limit
2796 THEN
2797 x_cc_result_out := 'FAIL';
2798 ELSE
2799 x_cc_result_out := 'PASS';
2800 END IF;
2801
2802 IF NVL(x_error_curr_tbl.COUNT,0) > 0
2803 THEN
2804 x_cc_result_out := 'FAIL' ;
2805
2806 OE_DEBUG_PUB.Add(' Currency conversion failed ');
2807
2808 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
2809 FND_MESSAGE.Set_Token('FROM',x_error_curr_tbl(1).usage_curr_code);
2810 FND_MESSAGE.Set_Token('TO',p_limit_curr_code );
2811 FND_MESSAGE.Set_Token('CONV',
2812 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'));
2813 OE_Credit_Engine_GRP.G_currency_error_msg :=
2814 SUBSTR(FND_MESSAGE.GET,1,1000) ;
2815
2816 G_result_out := 'FAIL' ;
2817 x_cc_result_out := 'FAIL' ;
2818
2819
2820 IF p_credit_check_rule_rec.credit_hold_level_code = 'ORDER'
2821 THEN
2822 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
2823 FND_MESSAGE.Set_Token('FROM',x_error_curr_tbl(1).usage_curr_code);
2824 FND_MESSAGE.Set_Token('TO',p_limit_curr_code );
2825 FND_MESSAGE.Set_Token('CONV',
2826 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'));
2827
2828
2829 OE_MSG_PUB.ADD;
2830 x_return_status := FND_API.G_RET_STS_ERROR;
2831 OE_DEBUG_PUB.ADD('Return status after assigned as Error = '
2832 || x_return_status );
2833 END IF;
2834
2835
2836 END IF;
2837
2838
2839 ELSE
2840 x_cc_result_out := 'PASS';
2841
2842 IF G_debug_flag = 'Y'
2843 THEN
2844 OE_DEBUG_PUB.Add(' No need to check exposure, UNLIMITED ');
2845 END IF;
2846 END IF;
2847
2848 IF G_debug_flag = 'Y'
2849 THEN
2850 OE_DEBUG_PUB.Add(' x_cc_result_out = ' || x_cc_result_out );
2851 OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status);
2852 OE_DEBUG_PUB.Add('OEXVCRLB: Out CHECK_ORDER_LINES_EXPOSURE');
2853 END IF;
2854 EXCEPTION
2855 WHEN others THEN
2856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2857 OE_DEBUG_PUB.Add('CHECK_ORDER_LINES_EXPOSURE: Other exceptions');
2858 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2859 OE_MSG_PUB.Add_Exc_Msg
2860 ( G_PKG_NAME
2861 , 'CHECK_ORDER_LINES_EXPOSURE'
2862 );
2863 END IF;
2864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2865 OE_DEBUG_PUB.ADD( SUBSTR(SQLERRM,1,300) ,1 );
2866 END CHECK_ORDER_LINES_EXPOSURE;
2867
2868 -----------------------------------------------------------
2869 -- Check item category limits within the given site
2870 -- If credit check failed on any category, return failure
2871 -- and the category being checked.
2872 -----------------------------------------------------------
2873 PROCEDURE Check_Item_Limits
2874 ( p_header_rec IN OE_ORDER_PUB.header_rec_type
2875 , p_customer_id IN NUMBER
2876 , p_site_use_id IN NUMBER
2877 , p_calling_action IN VARCHAR2 DEFAULT 'BOOKING'
2878 , p_credit_check_rule_rec IN
2879 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
2880 , p_system_parameter_rec IN
2881 OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
2882 , p_holds_table IN OUT NOCOPY Line_Holds_Tbl_Rectype
2883 , x_cc_result_out OUT NOCOPY VARCHAR2
2884 , x_return_status OUT NOCOPY VARCHAR2
2885 )
2886 IS
2887 l_category_sum NUMBER := 0 ;
2888 l_limit_category_sum NUMBER := 0 ; -- Sum converted to Limit currency
2889
2890 l_return_status VARCHAR2(30);
2891 l_credit_hold_level VARCHAR2(30);
2892 l_include_tax_flag VARCHAR2(1) := 'Y';
2893 l_item_limits OE_CREDIT_CHECK_UTIL.item_limits_tbl_type;
2894 l_lines OE_CREDIT_CHECK_UTIL.lines_Rec_tbl_type;
2895 j BINARY_INTEGER := 1;
2896 i BINARY_INTEGER := 1;
2897 l_cc_result_out VARCHAR2(30);
2898 l_check_category_id NUMBER;
2899 l_limit_currency VARCHAR2(30);
2900
2901 BEGIN
2902 OE_DEBUG_PUB.Add('OEXVCRLB: In Check_Item_Limits');
2903 --
2904 -- Initialize return status to success
2905 x_return_status := FND_API.G_RET_STS_SUCCESS;
2906 -- Default to Pass
2907 l_cc_result_out := 'PASS';
2908 l_credit_hold_level := p_credit_check_rule_rec.CREDIT_HOLD_LEVEL_CODE ;
2909 -- Need to use new get_item_limits api
2910 --
2911
2912 l_include_tax_flag := p_credit_check_rule_rec.include_tax_flag ;
2913
2914 OE_DEBUG_PUB.Add(' Call Get_Item_Limit ');
2915
2916 OE_CREDIT_CHECK_UTIL.Get_Item_Limit
2917 ( p_header_id => p_header_rec.header_id
2918 , p_include_tax_flag => p_credit_check_rule_rec.include_tax_flag
2919 , p_site_use_id => p_site_use_id
2920 , p_trx_curr_code => p_header_rec.transactional_curr_code
2921 , x_item_limits_tbl => l_item_limits
2922 , x_lines_tbl => l_lines
2923 );
2924
2925
2926 OE_DEBUG_PUB.Add(' After Get_Item_Limit with item tbl count '
2927 || l_item_limits.COUNT );
2928
2929 IF l_item_limits.count = 0
2930 THEN
2931 x_cc_result_out := 'NOCHECK';
2932 OE_DEBUG_PUB.Add(' No need to check as count 0 ');
2933
2934 ELSE
2935 OE_DEBUG_PUB.Add(' start category loop ');
2936 OE_DEBUG_PUB.Add(' ======================== ');
2937
2938 FOR i in 1..l_item_limits.count
2939 LOOP
2940 l_category_sum := 0;
2941 -- For each item category, sum the line values
2942 ----------------------------------------------
2943 OE_DEBUG_PUB.ADD(' ');
2944 OE_DEBUG_PUB.Add(' ------------------------------------ ');
2945 OE_DEBUG_PUB.Add(' Category id = '
2946 || l_item_limits(i).item_category_id );
2947 OE_DEBUG_PUB.Add(' ctg_line_amount = '
2948 || l_item_limits(i).ctg_line_amount );
2949
2950 OE_DEBUG_PUB.Add(' limit_curr_code = '
2951 || l_item_limits(i).limit_curr_code );
2952 OE_DEBUG_PUB.Add(' item_limit = '
2953 || l_item_limits(i).item_limit );
2954 OE_DEBUG_PUB.Add(' grouping = '
2955 || l_item_limits(i).grouping_id );
2956
2957 l_category_sum := l_item_limits(i).ctg_line_amount ;
2958
2959 OE_DEBUG_PUB.Add(' l_category_sum = ' || l_category_sum );
2960 OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
2961 OE_Credit_Engine_GRP.GL_currency );
2962
2963
2964 OE_DEBUG_PUB.ADD(' ');
2965 OE_DEBUG_PUB.Add(' ------------------------------------ ');
2966
2967
2968 l_check_category_id := l_item_limits(i).item_category_id ;
2969 l_limit_currency := l_item_limits(i).limit_curr_code ;
2970
2971 l_limit_category_sum :=
2972 OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
2973 ( p_amount => l_category_sum
2974 , p_transactional_currency => p_header_rec.transactional_curr_code
2975 , p_limit_currency => l_item_limits(i).limit_curr_code
2976 , p_functional_currency => OE_Credit_Engine_GRP.GL_currency
2977 , p_conversion_date => SYSDATE
2978 , p_conversion_type => p_credit_check_rule_rec.conversion_type
2979 ) ;
2980
2981 OE_DEBUG_PUB.Add
2982 (' l_limit_category_sum = '|| l_limit_category_sum );
2983
2984 OE_DEBUG_PUB.Add
2985 (' Credit limit = '|| l_item_limits(i).item_limit );
2986
2987 IF l_limit_category_sum > l_item_limits(i).item_limit
2988 THEN
2989 OE_DEBUG_PUB.Add
2990 ('Fails item category ID: '|| l_item_limits(i).item_category_id);
2991
2992 IF l_credit_hold_level = 'ORDER' THEN
2993
2994 OE_DEBUG_PUB.Add
2995 (' Call Apply_Order_CC_Hold ');
2996
2997 Apply_Order_CC_Hold
2998 ( p_header_id => p_header_rec.header_id
2999 , p_order_number => p_header_rec.order_number
3000 , p_calling_action => p_calling_action
3001 , p_cc_limit_used => 'ITEM'
3002 , p_cc_profile_used => 'CATEGORY'
3003 , p_item_category_id => l_item_limits(i).item_category_id
3004 , p_credit_hold_level =>
3005 p_credit_check_rule_rec.credit_hold_level_code
3006 , p_credit_check_rule_rec=>p_credit_check_rule_rec
3007 , x_cc_result_out => l_cc_result_out
3008 );
3009 EXIT; -- stop checking item limits
3010 ELSE
3011
3012 OE_DEBUG_PUB.Add
3013 (' Apply_Item_Category_Holds ');
3014
3015 Apply_Item_Category_Holds
3016 ( p_header_id => p_header_rec.header_id
3017 ,p_item_category_id => l_item_limits(i).item_category_id
3018 ,p_lines => l_lines
3019 ,p_holds_table => p_holds_table
3020 );
3021 END IF;
3022 -- If any category failed credit check then the result of
3023 -- check item limits is FAIL.
3024 l_cc_result_out := 'FAIL';
3025 --Don't exit until all item categories are checked.
3026 END IF;
3027
3028 l_limit_category_sum := 0 ;
3029 l_category_sum := 0;
3030 l_limit_currency := NULL;
3031
3032 END LOOP; -- category loop
3033
3034 OE_DEBUG_PUB.ADD(' out of category loop ');
3035
3036 x_cc_result_out := l_cc_result_out;
3037 END IF;
3038
3039 OE_DEBUG_PUB.ADD(' x_cc_result_out = ' || x_cc_result_out );
3040
3041 OE_DEBUG_PUB.ADD('OEXVCRLB: Out Check_Item_Limit');
3042
3043 EXCEPTION
3044 WHEN GL_CURRENCY_API.NO_RATE
3045 THEN
3046 BEGIN
3047 OE_DEBUG_PUB.Add('EXCEPTION: GL_CURRENCY_API.NO_RATE ');
3048 OE_DEBUG_PUB.Add('Apply_Order_CC_Hold for Item category');
3049 OE_DEBUG_PUB.Add('currency = '|| p_header_rec.transactional_curr_code );
3050 OE_DEBUG_PUB.Add('checking category = '|| l_check_category_id );
3051
3052 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
3053 FND_MESSAGE.Set_Token('FROM',p_header_rec.transactional_curr_code );
3054 FND_MESSAGE.Set_Token('TO',l_limit_currency );
3055 FND_MESSAGE.Set_Token('CONV',
3056 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'));
3057 OE_Credit_Engine_GRP.G_currency_error_msg :=
3058 SUBSTR(FND_MESSAGE.GET,1,1000) ;
3059 G_result_out := 'FAIL' ;
3060 x_cc_result_out := 'FAIL' ;
3061
3062
3063 IF p_credit_check_rule_rec.credit_hold_level_code = 'ORDER'
3064 THEN
3065 fnd_message.set_name('ONT', 'OE_CC_CONVERSION_ERORR');
3066 FND_MESSAGE.Set_Token('FROM',p_header_rec.transactional_curr_code );
3067 FND_MESSAGE.Set_Token('TO',l_limit_currency );
3068 FND_MESSAGE.Set_Token('CONV',
3069 NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'));
3070
3071 OE_MSG_PUB.ADD ;
3072 x_return_status := FND_API.G_RET_STS_ERROR;
3073 OE_DEBUG_PUB.ADD('Return status after assigned as Error = '
3074 || x_return_status );
3075 END IF;
3076
3077 OE_DEBUG_PUB.ADD(' Item CTG cc fails due to conversion error ');
3078 END;
3079
3080 WHEN others THEN
3081 OE_DEBUG_PUB.Add('Check_Item_Limit: Other exceptions');
3082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3083 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3084 OE_MSG_PUB.Add_Exc_Msg
3085 ( G_PKG_NAME
3086 , 'Check_Item_Limits'
3087 );
3088 END IF;
3089 OE_DEBUG_PUB.ADD( SUBSTR(SQLERRM,1,300) ,1 );
3090 END Check_Item_Limits;
3091
3092 ------------------------------------------------------------
3093 -- PROCEDURE: Check_Other_Credit_Limits PRIVATE
3094 -- DESCRIPTION: Get additional credit limit information and
3095 -- perform credit check on customer/site/default
3096 -- credit limits specified in the customer/site
3097 -- or default credit profiles.
3098 ------------------------------------------------------------
3099 PROCEDURE Check_Other_Credit_Limits
3100 ( p_header_rec IN OE_ORDER_PUB.header_rec_type
3101 , p_customer_id IN NUMBER
3102 , p_site_use_id IN NUMBER
3103 , p_calling_action IN VARCHAR2 := 'BOOKING'
3104 , p_credit_check_rule_rec IN
3105 OE_Credit_Check_Util.OE_credit_rules_rec_type
3106 , p_system_parameter_rec IN
3107 OE_Credit_Check_Util.OE_systems_param_rec_type
3108 , p_holds_table IN OUT NOCOPY Line_Holds_Tbl_Rectype
3109 , p_party_id IN NUMBER
3110 , x_credit_level OUT NOCOPY VARCHAR2
3111 , x_check_exposure_mode OUT NOCOPY VARCHAR2
3112 , x_cc_result_out OUT NOCOPY VARCHAR2
3113 , x_return_status OUT NOCOPY VARCHAR2
3114 , x_global_exposure_flag OUT NOCOPY VARCHAR2
3115 )
3116 IS
3117
3118 l_check_order VARCHAR2(1);
3119 l_default_limit_flag VARCHAR2(1);
3120 l_limit_curr_code VARCHAR2(30);
3121 l_overall_credit_limit NUMBER;
3122 l_trx_credit_limit NUMBER;
3123 l_usage_curr OE_CREDIT_CHECK_UTIL.curr_tbl_type;
3124 l_include_all_flag VARCHAR2(1);
3125 l_prev_customer_id NUMBER;
3126 l_customer_result_out VARCHAR2(30) := NULL;
3127 l_total_exposure NUMBER;
3128 l_orders NUMBER;
3129 l_orders_on_hold NUMBER;
3130 l_payments_overdue NUMBER;
3131 l_payments_at_risk NUMBER;
3132 l_error_curr_tbl OE_CREDIT_CHECK_UTIL. curr_tbl_type ;
3133 l_cc_profile_used VARCHAR2(30);
3134 l_cc_limit_used VARCHAR2(80);
3135 l_cc_result_out VARCHAR2(30);
3136 l_credit_hold_level VARCHAR2(30);
3137 l_credit_limit_entity_id NUMBER;
3138
3139 --bug 4293874 start
3140 l_request_id NUMBER;
3141 l_msg_count NUMBER;
3142 l_msg_data VARCHAR2(2000);
3143 l_customer_id NUMBER;
3144 l_site_use_id NUMBER;
3145 l_source_org_id NUMBER;
3146 l_source_user_id NUMBER;
3147 l_source_resp_id NUMBER;
3148 l_source_appln_id NUMBER;
3149 l_source_security_group_id NUMBER;
3150 --bug 4293874 ends
3151
3152 l_cc_trx_result_out VARCHAR2(30);
3153 l_cc_duedate_result_out VARCHAR2(30);
3154 l_cc_overall_result_out VARCHAR2(30);
3155 ----Bug 4320650
3156 l_unrounded_exposure NUMBER;
3157 -- bug 5907331
3158 l_review_party_id NUMBER;
3159 l_hold_line_seq VARCHAR2(1) := NVL(OE_SYS_PARAMETERS.VALUE('OE_HOLD_LINE_SEQUENCE'),1); -- ER 6135714
3160
3161 i_hld_rec NUMBER := 0; --ER8880886
3162 --ER 12363706 start
3163 l_new_cust_account_id NUMBER;
3164 l_old_cust_account_id NUMBER;
3165 l_tolerance_check VARCHAR2(1) := 'N';
3166 l_credit_profile_level VARCHAR2(30);
3167 l_line_exists VARCHAR2(1) := 'N';
3168
3169
3170 -- To retrieve the credit profile level of the latest manually released hold.
3171 CURSOR released_hold
3172 IS
3173 SELECT Credit_Profile_Level
3174 FROM OE_ORDER_HOLDS_all h,
3175 OE_HOLD_SOURCES_ALL s,
3176 oe_hold_releases ohr
3177 WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
3178 AND H.HEADER_ID = p_header_rec.header_id
3179 AND H.LINE_ID IS NULL
3180 AND H.HOLD_RELEASE_ID IS NOT NULL
3181 AND S.HOLD_ID = 1
3182 AND S.HOLD_ENTITY_CODE = 'O'
3183 AND S.HOLD_ENTITY_ID = p_header_rec.header_id
3184 AND S.RELEASED_FLAG ='Y'
3185 AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
3186 ORDER BY ohr.creation_date DESC;
3187 --ER 12363706 end
3188
3189 --13706069 start
3190 CURSOR released_hold_line(p_line_id IN NUMBER)
3191 IS
3192 SELECT Credit_Profile_Level
3193 FROM OE_ORDER_HOLDS_all h,
3194 OE_HOLD_SOURCES_ALL s,
3195 oe_hold_releases ohr
3196 WHERE H.HOLD_SOURCE_ID = S.HOLD_SOURCE_ID
3197 AND H.HEADER_ID = p_header_rec.header_id
3198 AND H.LINE_ID = p_line_id
3199 AND H.HOLD_RELEASE_ID IS NOT NULL
3200 AND S.HOLD_ID = 1
3201 AND S.HOLD_ENTITY_CODE = 'O'
3202 AND S.HOLD_ENTITY_ID = H.header_id
3203 AND S.RELEASED_FLAG ='Y'
3204 AND ohr.HOLD_RELEASE_ID = h.HOLD_RELEASE_ID
3205
3206 ORDER BY ohr.creation_date DESC;
3207 --13706069 end
3208 BEGIN
3209 --
3210 -- Set the default behavior to pass credit check
3211 --
3212 x_cc_result_out := 'PASS';
3213 x_return_status := FND_API.G_RET_STS_SUCCESS;
3214 l_credit_hold_level := p_credit_check_rule_rec.CREDIT_HOLD_LEVEL_CODE ;
3215 x_global_exposure_flag := 'N' ;
3216
3217 l_cc_result_out := 'PASS';
3218 l_cc_trx_result_out := 'PASS';
3219 l_cc_duedate_result_out := 'PASS';
3220 l_cc_overall_result_out := 'PASS';
3221 g_hold_reason_rec.delete; -- 14305856 - Initializing the g_hold_reason_rec table
3222 l_cc_limit_used := NULL; -- 14305856 - Initializing the l_cc_limit_used to null
3223
3224
3225 IF G_debug_flag = 'Y'
3226 THEN
3227 OE_DEBUG_PUB.Add('OEXVCRLB: In Check_Other_Credit Limits');
3228 OE_DEBUG_PUB.ADD(' ' );
3229 OE_DEBUG_PUB.ADD(' ---------------------------------------- ' );
3230 OE_DEBUG_PUB.ADD(' Header ID = '|| p_header_rec.header_id );
3231 OE_DEBUG_PUB.ADD(' p_customer_id = '|| p_customer_id );
3232 OE_DEBUG_PUB.ADD(' p_site_use_id = '|| p_site_use_id );
3233 OE_DEBUG_PUB.Add(' p_calling_action = '|| p_calling_action );
3234 OE_DEBUG_PUB.Add('Calling Validate_other_credit_check');
3235 END IF;
3236 --
3237 -----------------------------------------------------------
3238 -- Check if order site use needs credit check. Also |
3239 -- determine if credit check should be at customer level |
3240 -- or the site level and the credit limits at that level. |
3241 -- The information returned will be used for credit check.|
3242 -----------------------------------------------------------
3243 --
3244 OE_credit_check_lines_PVT.Validate_other_credit_check
3245 ( p_header_rec => p_header_rec
3246 , p_customer_id => p_customer_id
3247 , p_site_use_id => p_site_use_id
3248 , p_calling_action => p_calling_action
3249 , p_credit_check_rule_rec => p_credit_check_rule_rec
3250 , p_party_id => p_party_id
3251 , x_check_order_flag => l_check_order
3252 , x_credit_check_lvl_out => x_credit_level
3253 , x_default_limit_flag => l_default_limit_flag
3254 , x_limit_curr_code => l_limit_curr_code
3255 , x_overall_credit_limit => l_overall_credit_limit
3256 , x_trx_credit_limit => l_trx_credit_limit
3257 , x_usage_curr => l_usage_curr
3258 , x_include_all_flag => l_include_all_flag
3259 , x_return_status => x_return_status
3260 , x_global_exposure_flag => x_global_exposure_flag
3261 , x_credit_limit_entity_id => l_credit_limit_entity_id
3262 );
3263
3264 --ER 12363706 start
3265 IF p_credit_check_rule_rec.credit_hold_level_code = 'ORDER' THEN
3266 BEGIN
3267 IF G_debug_flag = 'Y'
3268 THEN
3269 OE_DEBUG_PUB.Add('OEXVCRLB: Retrieve the latest released Credit Profile ' || p_header_rec.header_id);
3270 END IF;
3271
3272 OPEN released_hold;
3273 FETCH released_hold INTO l_credit_profile_level;
3274
3275 IF (released_hold%notfound) THEN
3276 oe_debug_pub.add('No Released record found');
3277 l_credit_profile_level := NULL;
3278 END IF;
3279
3280 CLOSE released_hold;
3281
3282 EXCEPTION
3283 WHEN OTHERS THEN
3284 oe_debug_pub.ADD('OEXVCRLB: In Exception block');
3285 l_credit_profile_level := NULL;
3286 END;
3287 END IF;
3288
3289 FOR i IN 1 .. OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab.count
3290 LOOP
3291 l_line_exists := 'N';
3292
3293 IF G_debug_flag = 'Y' THEN
3294 oe_debug_pub.add('OEXVCRLB: BillTo site has changed. Checking if the new site belongs to the same CUSTOMER');
3295 oe_debug_pub.add('OEXVCRLB: Also checking if the earlier credit checking also happened due to CUSTOMER level limits only');
3296 END IF;
3297
3298 FOR k IN 1 ..p_holds_table.count
3299 LOOP
3300 IF OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab(i).line_id = p_holds_table(k).line_id THEN
3301 l_line_exists := 'Y';
3302 --13706069 start
3303 ELSE
3304 IF G_debug_flag = 'Y' THEN
3305 oe_debug_pub.add('OEXVCRLB: Bill To site is not changed for the line.' || OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab(i).line_id);
3306 END IF;
3307
3308 l_tolerance_check := 'Y';
3309 --13706069 end
3310 END IF;
3311 END LOOP;
3312
3313 IF l_line_exists = 'Y' THEN
3314 IF OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab(i).line_id IS NOT NULL AND p_credit_check_rule_rec.credit_hold_level_code = 'LINE' THEN
3315
3316 --13706069 start
3317 BEGIN
3318 IF G_debug_flag = 'Y' THEN
3319 OE_DEBUG_PUB.Add('OEXVCRLB: Retrieve the latest released Credit Profile ' || OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab(i).line_id);
3320 END IF;
3321
3322 OPEN released_hold_line(OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab(i).line_id);
3323 FETCH released_hold_line INTO l_credit_profile_level;
3324
3325 IF (released_hold_line%notfound) THEN
3326 oe_debug_pub.add('No Released record found');
3327 l_credit_profile_level := NULL;
3328 END IF;
3329
3330 CLOSE released_hold_line;
3331
3332 EXCEPTION
3333 WHEN OTHERS THEN
3334 oe_debug_pub.ADD('OEXVCRLB: In Exception block');
3335 l_credit_profile_level := NULL;
3336 END;
3337
3338 --13706069 end
3339 END IF;
3340
3341 IF x_credit_level = 'CUSTOMER' AND l_credit_profile_level ='CUSTOMER' THEN
3342
3343 SELECT acct_site.cust_account_id
3344 INTO l_new_cust_account_id
3345 FROM HZ_CUST_SITE_USES_ALL SITE,
3346 HZ_PARTY_SITES PARTY_SITE,
3347 HZ_CUST_ACCT_SITES ACCT_SITE
3348 WHERE SITE.SITE_USE_ID = OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab(i).new_invoice_to_org_id
3349 AND SITE.SITE_USE_CODE = 'BILL_TO'
3350 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
3351 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
3352 AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
3353
3354 SELECT acct_site.cust_account_id
3355 INTO l_old_cust_account_id
3356 FROM HZ_CUST_SITE_USES_ALL SITE,
3357 HZ_PARTY_SITES PARTY_SITE,
3358 HZ_CUST_ACCT_SITES ACCT_SITE
3359 WHERE SITE.SITE_USE_ID = OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab(i).old_invoice_to_org_id
3360 AND SITE.SITE_USE_CODE = 'BILL_TO'
3361 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
3362 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
3363 AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
3364
3365 IF l_new_cust_account_id <> l_old_cust_account_id THEN
3366 IF G_debug_flag = 'Y'
3367 THEN
3368 OE_DEBUG_PUB.Add('OEXVCRLB: Sites belong to the different customer and credit checking at Customer level. Tolerance check N ');
3369 END IF;
3370 l_tolerance_check := 'N';
3371 EXIT;
3372 ELSE
3373 IF G_debug_flag = 'Y'
3374 THEN
3375 OE_DEBUG_PUB.Add('slagiset: Sites belong to the same customer and credit checking at Customer level. Tolerance check Y ');
3376 END IF;
3377 l_tolerance_check := 'Y';
3378 END IF;
3379 ELSE
3380 l_tolerance_check := 'N';
3381 END IF;
3382 END IF;
3383 END LOOP;
3384
3385 IF l_tolerance_check = 'Y' THEN
3386 IF OE_CREDIT_ENGINE_GRP.CREDIT_TOLERANCE_CHECK( p_header_id => p_header_rec.header_id) THEN
3387 RETURN;
3388 END IF;
3389 END IF;
3390
3391 -- OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab.delete; Commented for bug# 13706069
3392 --ER 12363706 end
3393 IF G_debug_flag = 'Y'
3394 THEN
3395 OE_DEBUG_PUB.Add(' After Validate_other_credit_check status '
3396 || x_return_status );
3397 OE_DEBUG_PUB.Add(' x_global_exposure_flag => '||
3398 x_global_exposure_flag );
3399 OE_DEBUG_PUB.Add(' l_credit_limit_entity_id ==> '||
3400 l_credit_limit_entity_id );
3401 OE_DEBUG_PUB.Add(' l_check_order = '|| l_check_order );
3402 END IF;
3403
3404 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3405 RAISE FND_API.G_EXC_ERROR;
3406 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3408 END IF;
3409
3410
3411 --
3412 -----------------------------------------------------------
3413 -- Perform credit checks for due date, transaction limits,
3414 -- and overall limits.
3415 -----------------------------------------------------------
3416 IF l_check_order = 'Y' THEN
3417 --
3418 -- Determine the profile used
3419 --
3420 IF l_default_limit_flag = 'Y' THEN
3421 l_cc_profile_used := 'DEFAULT';
3422 ELSE
3423 l_cc_profile_used := x_credit_level ;
3424 END IF;
3425 --
3426 ----------------------------------------------------+
3427 -- order site use is subject to credit check: |
3428 ----------------------------------------------------|
3429 -- check 1: item limit <-- passed/failed|
3430 -- check 2: max-past-due-inv limit <-- in progress |
3431 -- check 3: trx limit |
3432 -- check 4: overall limit |
3433 ----------------------------------------------------+
3434 --
3435
3436
3437 OE_credit_check_lines_PVT.Chk_Past_Due_Invoice
3438 ( p_customer_id => p_customer_id
3439 , p_site_use_id => p_site_use_id
3440 , p_party_id => l_credit_limit_entity_id
3441 , p_credit_check_rule_rec => p_credit_check_rule_rec
3442 , p_system_parameter_rec => p_system_parameter_rec
3443 , p_credit_level => x_credit_level
3444 , p_usage_curr => l_usage_curr
3445 , p_include_all_flag => l_include_all_flag
3446 , p_global_exposure_flag => x_global_exposure_flag
3447 , x_cc_result_out => l_cc_duedate_result_out
3448 , x_return_status => x_return_status
3449 );
3450
3451 IF G_debug_flag = 'Y'
3452 THEN
3453 OE_DEBUG_PUB.Add('Chk_Past_Due_Invoice: Result Out ='
3454 ||l_cc_duedate_result_out);
3455 OE_DEBUG_PUB.Add('Chk_Past_Due_Invoice: Return Status ='
3456 || x_return_status );
3457
3458 END IF;
3459
3460 -- bug 4002820
3461 IF l_cc_duedate_result_out = 'FAIL' THEN
3462 -- only overwrite the l_cc_result_out if the current checking fails
3463 -- to make sure the l_cc_result_out is FAIL if any of the checkings fails.
3464 l_cc_result_out := l_cc_duedate_result_out;
3465 l_cc_limit_used := 'DUEDATE';
3466
3467 --ER8880886
3468 i_hld_rec := i_hld_rec +1;
3469 g_hold_reason_rec.extend; --14305856
3470 g_hold_reason_rec(i_hld_rec) := 'OE_CC_HOLD_OVERDUE';
3471 --ER8880886
3472 END IF;
3473
3474
3475 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3476 RAISE FND_API.G_EXC_ERROR;
3477 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3479 END IF;
3480
3481 -- IF l_cc_result_out = 'PASS' THEN
3482 -- Changed IF condition to fix bug 4002820, need to do overall
3483 -- limit checking even order limit checking failed when
3484 -- Credit Management is installed and used.
3485
3486 IF OE_CREDIT_CHECK_UTIL.G_crmgmt_installed is NULL
3487 THEN
3488 OE_CREDIT_CHECK_UTIL.G_crmgmt_installed :=
3489 AR_CMGT_CREDIT_REQUEST_API.is_Credit_Management_Installed ;
3490 END IF;
3491
3492 IF l_cc_duedate_result_out = 'PASS'
3493 OR OE_CREDIT_CHECK_UTIL.G_crmgmt_installed = TRUE THEN
3494 ----------------------------------------------------+
3495 -- order site use is subject to credit check: |
3496 ----------------------------------------------------|
3497 -- check 1: item limit <-- passed/failed|
3498 -- check 2: max-past-due-inv limit <-- passed |
3499 -- check 3: trx limit <-- in progress |
3500 -- check 4: overall limit |
3501 ----------------------------------------------------+
3502 --
3503
3504 OE_credit_check_lines_PVT.Check_Trx_Limit
3505 ( p_header_rec => p_header_rec
3506 , p_customer_id => p_customer_id
3507 , p_site_use_id => p_site_use_id
3508 , p_credit_level => x_credit_level
3509 , p_credit_check_rule_rec => p_credit_check_rule_rec
3510 , p_system_parameter_rec => p_system_parameter_rec
3511 , p_limit_curr_code => l_limit_curr_code
3512 , p_trx_credit_limit => l_trx_credit_limit
3513 , x_cc_result_out => l_cc_trx_result_out
3514 , x_return_status => x_return_status
3515 , x_conversion_status => l_error_curr_tbl
3516 );
3517
3518 IF G_debug_flag = 'Y'
3519 THEN
3520 OE_DEBUG_PUB.Add('Check_Trx_Limit: Result Out ='
3521 ||l_cc_trx_result_out);
3522 OE_DEBUG_PUB.Add('Check_Trx_Limit: Return Status ='
3523 || x_return_status );
3524 OE_DEBUG_PUB.Add('err curr tbl count = '|| l_error_curr_tbl.COUNT );
3525
3526 END IF;
3527
3528 IF l_cc_trx_result_out = 'FAIL' THEN
3529 l_cc_result_out := l_cc_trx_result_out;
3530 IF l_cc_limit_used IS NOT NULL THEN
3531 -- in order to disply useful message if two or more checkings fail.
3532 -- l_cc_limit_used := 'Overdue invoices found' || ', order limit exceeded';
3533 -- bug 4153299
3534 /*l_cc_limit_used
3535 := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'OVERDUE')
3536 || OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'ORDER');*/ --commented ER8880886
3537
3538 l_cc_limit_used := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'OVERDUE') || ', '
3539 || OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'ORDER'); --added ER8880886
3540
3541 --ER8880886
3542 i_hld_rec := i_hld_rec +1;
3543 g_hold_reason_rec.extend;
3544 g_hold_reason_rec(i_hld_rec) := 'OE_CC_HOLD_ORDER';
3545 --ER8880886
3546 ELSE
3547 l_cc_limit_used := 'TRX';
3548
3549 --ER8880886
3550 i_hld_rec := i_hld_rec +1;
3551 g_hold_reason_rec.extend; --14305856
3552 g_hold_reason_rec(i_hld_rec) := 'OE_CC_HOLD_ORDER';
3553 --ER8880886
3554
3555 END IF;
3556 END IF;
3557
3558 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3559 RAISE FND_API.G_EXC_ERROR;
3560 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3562 END IF;
3563
3564 -- IF l_cc_result_out = 'PASS' THEN
3565 -- Changed IF condition to fix bug 4002820, need to do overall
3566 -- limit checking even order limit checking failed when
3567 -- Credit Management is installed and used.
3568 IF l_cc_trx_result_out = 'PASS'
3569 OR OE_CREDIT_CHECK_UTIL.G_crmgmt_installed = TRUE THEN
3570 ----------------------------------------------------+
3571 -- order is subject to credit check: |
3572 ----------------------------------------------------|
3573 -- check 1: item limit <-- passed/failed|
3574 -- check 2: max-past-due-inv limit <-- passed |
3575 -- check 3: trx limit <-- passed |
3576 -- check 4: overall limit <-- in progress |
3577 ----------------------------------------------------+
3578 --
3579 --
3580 x_check_exposure_mode := 'INLINE';
3581
3582 IF G_debug_flag = 'Y'
3583 THEN
3584 OE_DEBUG_PUB.Add(' x_check_exposure_mode = '
3585 || x_check_exposure_mode );
3586 END IF;
3587 -------------------------------------------------
3588 -- l_prev_customer_id is used to keep track of the
3589 -- customer level exposure calc
3590 -- If a bill to site has no credit profile defined,
3591 -- the customer profile is used.
3592 -- This l_prev_customer_id variable will enable to prevent
3593 -- multiple credit exposure calculation for customer level
3594 -- if more than one bill to
3595 -- site has no credit profile and needs to use the customer
3596 -- level
3597 ---------------------------------------------------
3598 IF G_debug_flag = 'Y'
3599 THEN
3600 OE_DEBUG_PUB.Add(' l_prev_customer_id = '|| l_prev_customer_id );
3601 END IF;
3602
3603 IF ( ( x_credit_level NOT IN ( 'CUSTOMER','PARTY') )
3604 OR
3605 NVL(l_prev_customer_id,p_customer_id * -1) <> p_customer_id
3606 )
3607 THEN
3608
3609 OE_credit_check_lines_PVT.Check_Order_lines_exposure
3610 ( p_customer_id => p_customer_id
3611 , p_site_use_id => p_site_use_id
3612 , p_header_id => p_header_rec.header_id
3613 , p_party_id => p_party_id
3614 , p_credit_level => x_credit_level
3615 , p_limit_curr_code => l_limit_curr_code
3616 , p_overall_credit_limit => l_overall_credit_limit
3617 , p_calling_action => p_calling_action
3618 , p_usage_curr => l_usage_curr
3619 , p_include_all_flag => l_include_all_flag
3620 , p_holds_rel_flag => 'N'
3621 , p_default_limit_flag => l_default_limit_flag
3622 , p_credit_check_rule_rec => p_credit_check_rule_rec
3623 , p_system_parameter_rec => p_system_parameter_rec
3624 , p_global_exposure_flag => x_global_exposure_flag
3625 , p_credit_limit_entity_id => l_credit_limit_entity_id
3626 , x_total_exposure => l_total_exposure
3627 , x_cc_result_out => l_cc_overall_result_out
3628 , x_error_curr_tbl => l_error_curr_tbl
3629 , x_return_status => x_return_status
3630 );
3631
3632
3633 IF G_debug_flag = 'Y'
3634 THEN
3635 OE_DEBUG_PUB.Add('After call to Check_order_lines_Exposure ');
3636 OE_DEBUG_PUB.Add('l_cc_result_out = ' || l_cc_overall_result_out );
3637 OE_DEBUG_PUB.Add('total exposure = ' || l_total_exposure );
3638 OE_DEBUG_PUB.Add('x_return_status = ' || x_return_status );
3639 OE_DEBUG_PUB.Add('Err curr table count = '||
3640 l_error_curr_tbl.COUNT );
3641 END IF;
3642 --Bug 4320650
3643 l_unrounded_exposure := l_total_exposure;
3644
3645 OE_CREDIT_CHECK_UTIL.Rounded_Amount(l_limit_curr_code,
3646 l_unrounded_exposure,
3647 l_total_exposure);
3648
3649
3650 G_total_site_exposure:=l_total_exposure ; -------new (FPI)
3651
3652
3653 G_limit_currency :=l_limit_curr_code ; -------new (FPI)
3654
3655
3656 IF x_credit_level = 'CUSTOMER' OR x_credit_level = 'PARTY'
3657 THEN
3658 l_prev_customer_id := p_customer_id;
3659 l_customer_result_out := l_cc_result_out;
3660 END IF;
3661
3662 ELSE
3663 IF G_debug_flag = 'Y'
3664 THEN
3665 OE_DEBUG_PUB.Add('customer exposure already checked');
3666 END IF;
3667
3668 -- customer exposure already checked, retrieve the result
3669 l_cc_result_out := l_customer_result_out;
3670 END IF;
3671
3672 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3673 RAISE FND_API.G_EXC_ERROR;
3674 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3676 END IF;
3677
3678 IF l_cc_overall_result_out = 'FAIL' THEN
3679 l_cc_result_out := l_cc_overall_result_out;
3680 -- in order to disply useful message if two or more checkings fail.
3681
3682 IF INSTR(l_cc_limit_used, ',') >0 THEN
3683 -- l_cc_limit_used := l_cc_limit_used || ', overall limit exceeded';
3684 -- bug 4153299
3685 l_cc_limit_used := l_cc_limit_used || ', '
3686 || OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'OVERALL');
3687
3688 --ER8880886
3689 i_hld_rec := i_hld_rec +1;
3690 g_hold_reason_rec.extend;
3691 g_hold_reason_rec(i_hld_rec) := 'OE_CC_HOLD_OVERALL';
3692 --ER8880886
3693
3694 ELSIF l_cc_limit_used IS NOT NULL THEN
3695
3696 --ER8880886
3697 i_hld_rec := i_hld_rec +1;
3698 g_hold_reason_rec.extend;
3699 g_hold_reason_rec(i_hld_rec) := 'OE_CC_HOLD_OVERALL';
3700 --ER8880886
3701
3702 IF l_cc_trx_result_out = 'FAIL' THEN
3703 -- l_cc_limit_used := 'Order limit, overall limit exceeded';
3704 -- bug 4153299
3705 l_cc_limit_used
3706 := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'ORDER')
3707 ||', '
3708 || OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'OVERALL');
3709 ELSIF l_cc_duedate_result_out = 'FAIL' THEN
3710 -- l_cc_limit_used := 'Overdue invoices found'||', overall limit exceeded'; --commented ER8880886
3711 l_cc_limit_used := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'OVERDUE') || ', '
3712 || OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_LIMIT', 'OVERALL'); --ER8880886
3713
3714 END IF;
3715 ELSE
3716 l_cc_limit_used := 'OVERALL';
3717
3718 --ER8880886
3719 i_hld_rec := i_hld_rec +1;
3720 g_hold_reason_rec.extend; --14305856
3721 g_hold_reason_rec(i_hld_rec) := 'OE_CC_HOLD_OVERALL';
3722 --ER8880886
3723
3724 END IF;
3725
3726 -- set g_cc_limit_used here in order to indicate Overall Limit was
3727 -- used in the subsequent call to submit Credit Management Request.
3728 -- l_cc_limit_used will be passed to display messages.
3729 G_cc_limit_used := 'OVERALL';
3730 END IF;
3731
3732 -- l_cc_limit_used := 'OVERALL';
3733
3734 ELSE
3735 l_cc_limit_used := 'TRX';
3736 END IF;
3737 ELSE
3738 l_cc_limit_used := 'DUEDATE';
3739 END IF;
3740 ELSE
3741 IF G_debug_flag = 'Y'
3742 THEN
3743 OE_DEBUG_PUB.Add('No credit check required');
3744 END IF;
3745
3746 l_cc_result_out := 'NOCHECK';
3747 END IF;
3748 -- bug 5907331
3749 G_credit_limit_entity_id := l_credit_limit_entity_id;
3750
3751 --
3752 -- Update database table with hold information
3753 --
3754 IF l_cc_result_out = 'FAIL' THEN
3755 IF l_credit_hold_level = 'ORDER' THEN
3756
3757 -- bug 4153299
3758 --6616741 l_cc_profile_used := OE_CREDIT_CHECK_UTIL.Get_CC_Lookup_Meaning('OE_CC_PROFILE', l_cc_profile_used);
3759
3760 Apply_Order_CC_Hold
3761 ( p_header_id => p_header_rec.header_id
3762 , p_order_number => p_header_rec.order_number
3763 , p_calling_action => p_calling_action
3764 , p_cc_limit_used => l_cc_limit_used
3765 , p_cc_profile_used => l_cc_profile_used
3766 , p_item_category_id => NULL
3767 , p_credit_hold_level => l_credit_hold_level
3768 , p_credit_check_rule_rec=> p_credit_check_rule_rec
3769 , x_cc_result_out => l_cc_result_out
3770 );
3771
3772 ----Bug 4293874 starts----------
3773 ---------------------- Start Credit Review --------------
3774
3775 --kadiraju changes begin for Bug#13768161
3776 --IF l_cc_result_out in ('FAIL_HOLD','FAIL_NONE','FAIL')
3777 IF l_cc_result_out in ('FAIL_HOLD','FAIL')
3778 --kadiraju changes End for Bug#13768161
3779 THEN
3780 --IF l_cc_overall_result_out = 'FAIL' --ER8880886
3781 -- THEN --ER8880886
3782 IF OE_CREDIT_CHECK_UTIL.G_crmgmt_installed is NULL
3783 THEN
3784 OE_CREDIT_CHECK_UTIL.G_crmgmt_installed :=
3785 AR_CMGT_CREDIT_REQUEST_API.is_Credit_Management_Installed ;
3786 END IF;
3787
3788 IF OE_CREDIT_CHECK_UTIL.G_crmgmt_installed
3789 THEN
3790 -- bug 5907331
3791 l_review_party_id := p_party_id;
3792 ------check if the credit check level is PARTY, CUSTOMER or SITE
3793 IF x_credit_level ='PARTY'
3794 THEN
3795 l_customer_id:=NULL;
3796 l_site_use_id:=NULL;
3797 -- bug 5907331
3798 IF p_party_id <> nvl(l_credit_limit_entity_id ,p_party_id) THEN
3799 l_review_party_id := l_credit_limit_entity_id;
3800 END IF;
3801 ELSIF x_credit_level ='CUSTOMER'
3802 THEN
3803 l_customer_id:=p_customer_id;
3804 l_site_use_id:=NULL;
3805 ELSIF x_credit_level ='SITE'
3806 THEN
3807 l_customer_id:=p_customer_id;
3808 l_site_use_id:=p_site_use_id;
3809 END IF;
3810
3811 -------------get profile values:
3812 -- l_source_org_id := FND_PROFILE.VALUE('ORG_ID'); -- Bug 12651163
3813 l_source_org_id := p_header_rec.org_id; --Bug 12651163
3814 l_source_user_id := FND_PROFILE.VALUE ('USER_ID');
3815 l_source_resp_id := FND_PROFILE.VALUE ('RESP_ID');
3816 l_source_appln_id := FND_PROFILE.VALUE ('RESP_APPL_ID');
3817 l_source_security_group_id := FND_PROFILE.VALUE('SECURITY_GROUP_ID');
3818
3819 IF G_debug_flag = 'Y'
3820 THEN
3821 OE_DEBUG_PUB.Add('Calling Create_credit_request,
3822 credit check level= '||x_credit_level);
3823
3824 OE_DEBUG_PUB.Add('Parameters: ');
3825 OE_DEBUG_PUB.Add('-------------------------------------------');
3826 OE_DEBUG_PUB.Add('p_requestor_id= '||TO_CHAR(fnd_global.employee_id));
3827 OE_DEBUG_PUB.Add('p_review_type= ORDER_HOLD');
3828 OE_DEBUG_PUB.Add('p_credit_classification= NULL');
3829 --OE_DEBUG_PUB.Add('p_requested_amount= '||TO_CHAR(l_total_exposure ));-- Bug 12651163
3830 OE_DEBUG_PUB.Add('p_requested_amount= '||
3831 TO_CHAR(OE_CREDIT_CHECK_LINES_PVT.G_total_site_exposure ));-- Bug 12651163
3832 --OE_DEBUG_PUB.Add('p_requested_currency= '||l_limit_curr_code);-- Bug 12651163
3833 OE_DEBUG_PUB.Add('p_requested_currency= '||
3834 OE_CREDIT_CHECK_LINES_PVT.G_limit_currency);-- Bug 12651163
3835 --OE_DEBUG_PUB.Add('p_trx_amount= '||TO_CHAR(g_order));-- Bug 12651163
3836 OE_DEBUG_PUB.Add('p_trx_amount= '||
3837 TO_CHAR(OE_CREDIT_CHECK_UTIL.g_current_order_value));-- Bug 12651163
3838 OE_DEBUG_PUB.Add('p_trx_currency= '||p_header_rec.transactional_curr_code );
3839 OE_DEBUG_PUB.Add('p_credit_type = TRADE' );
3840 OE_DEBUG_PUB.Add('p_term_length = NULL' );
3841 OE_DEBUG_PUB.Add('p_credit_check_rule_id= '||
3842 TO_CHAR(p_credit_check_rule_rec.credit_check_rule_id));
3843 OE_DEBUG_PUB.Add('p_credit_request_status = SUBMIT');
3844 OE_DEBUG_PUB.Add('p_party_id= '||TO_CHAR(p_party_id));
3845 OE_DEBUG_PUB.Add('p_cust_account_id= '||TO_CHAR(l_customer_id));
3846 OE_DEBUG_PUB.Add('p_cust_acct_site_id = NULL');
3847 OE_DEBUG_PUB.Add('p_site_use_id= '||TO_CHAR(l_site_use_id));
3848 OE_DEBUG_PUB.Add('p_contact_party_id = NULL');
3849 OE_DEBUG_PUB.Add('p_notes = NULL');
3850 OE_DEBUG_PUB.Add('p_source_org_id= '||TO_CHAR(l_source_org_id));
3851 OE_DEBUG_PUB.Add('p_source_user_id= '||TO_CHAR(l_source_user_id));
3852 OE_DEBUG_PUB.Add('p_source_resp_id= '||TO_CHAR(l_source_resp_id));
3853 OE_DEBUG_PUB.Add('p_source_appln_id= '||TO_CHAR(l_source_appln_id));
3854 OE_DEBUG_PUB.Add('p_source_security_group_id= '||TO_CHAR(l_source_security_group_id));
3855 OE_DEBUG_PUB.Add('p_source_name = OM');
3856 OE_DEBUG_PUB.Add('p_source_column1 = header_id= '||
3857 TO_CHAR(p_header_rec.header_id));
3858 OE_DEBUG_PUB.Add('p_source_column2 = order_number= '||
3859 TO_CHAR(p_header_rec.order_number));
3860 OE_DEBUG_PUB.Add('p_source_column3= ORDER');
3861
3862 END IF;
3863 ----------------Submit Credit Review--------------------
3864 AR_CMGT_CREDIT_REQUEST_API.Create_credit_request
3865 ( p_api_version => 1.0
3866 , p_init_msg_list => FND_API.G_FALSE
3867 , p_commit => FND_API.G_FALSE
3868 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3869 , x_return_status => x_return_status
3870 , x_msg_count => l_msg_count
3871 , x_msg_data => l_msg_data
3872 , p_application_number => NULL
3873 , p_application_date => SYSDATE
3874 , p_requestor_type => NULL
3875 , p_requestor_id => fnd_global.employee_id
3876 , p_review_type => 'ORDER_HOLD'
3877 , p_credit_classification => NULL
3878 --, p_requested_amount => l_total_exposure -- Bug 12651163
3879 , p_requested_amount => OE_CREDIT_CHECK_LINES_PVT.G_total_site_exposure -- Bug 12651163
3880 --, p_requested_currency => l_limit_curr_code -- Bug 12651163
3881 , p_requested_currency => OE_CREDIT_CHECK_LINES_PVT.G_limit_currency -- Bug 12651163
3882 --, p_trx_amount => g_order -- Bug 12651163
3883 , p_trx_amount => OE_CREDIT_CHECK_UTIL.g_current_order_value-- Bug 12651163
3884 , p_trx_currency => p_header_rec.transactional_curr_code
3885 , p_credit_type => 'TRADE'
3886 , p_term_length => NULL --the unit is no of months
3887 , p_credit_check_rule_id => p_credit_check_rule_rec.credit_check_rule_id
3888 , p_credit_request_status => 'SUBMIT'
3889 , p_party_id => l_review_party_id -- bug 5907331
3890 , p_cust_account_id => l_customer_id
3891 , p_cust_acct_site_id => NULL
3892 , p_site_use_id => l_site_use_id
3893 , p_contact_party_id => NULL --party_id of the pseudo party
3894 , p_notes => NULL --contact relationship.
3895 , p_source_org_id => l_source_org_id
3896 , p_source_user_id => l_source_user_id
3897 , p_source_resp_id => l_source_resp_id
3898 , p_source_appln_id => l_source_appln_id
3899 , p_source_security_group_id => l_source_security_group_id
3900 , p_source_name => 'OM'
3901 , p_source_column1 => p_header_rec.header_id
3902 , p_source_column2 => p_header_rec.order_number
3903 , p_source_column3 => 'ORDER'
3904 , p_credit_request_id => l_request_id
3905 , p_hold_reason_rec => g_hold_reason_rec --ER8880886
3906 );
3907
3908 IF x_return_status='S'
3909 THEN
3910 FND_MESSAGE.Set_Name('ONT','OE_CC_CMGT_REVIEW');
3911 FND_MESSAGE.Set_Token('REQUEST_ID',l_request_id);
3912 OE_MSG_PUB.Add;
3913 END IF;
3914
3915 IF G_debug_flag = 'Y'
3916 THEN
3917 IF x_return_status='S'
3918 THEN
3919 OE_DEBUG_PUB.Add('Credit review submitted, request_id= '
3920 ||TO_CHAR(l_request_id));
3921 ELSE
3922 OE_DEBUG_PUB.Add('Credit review has not been submitted');
3923 END IF;
3924
3925 OE_DEBUG_PUB.Add('l_request_id= '||TO_CHAR(l_request_id));
3926 OE_DEBUG_PUB.Add('x_return_status= '||x_return_status);
3927 OE_DEBUG_PUB.Add('l_msg_count= '||TO_CHAR(l_msg_count));
3928 OE_DEBUG_PUB.Add('l_msg_data= '||l_msg_data);
3929
3930 END IF;
3931
3932 END IF;
3933
3934 --END IF; --ER8880886
3935
3936 END IF;
3937 --------------------------------- End Credit review -----
3938 ---Bug 4293874 ends---------
3939 ELSE
3940
3941
3942 -- ER 6135714
3943 IF G_debug_flag = 'Y' THEN
3944 OE_DEBUG_PUB.Add('Applying Hold for hold line sequence ');
3945 oe_debug_pub.add(' l_cc_limit_used= '||l_cc_limit_used||' l_total_exposure= '||l_total_exposure
3946 || ' l_hold_line_seq= '||l_hold_line_seq); --14365827
3947 END IF;
3948
3949 IF (l_hold_line_seq = '1' OR p_calling_action = 'BOOKING'
3950 OR ( l_cc_limit_used <> 'OVERALL' and l_total_exposure is NULL) --14365827
3951 OR nvl(p_credit_check_rule_rec.uninvoiced_orders_flag,'N') = 'Y') THEN
3952 IF G_debug_flag = 'Y' THEN
3953 OE_DEBUG_PUB.Add('Applying Hold for all hold line sequence option');
3954 END IF;
3955 -- ER 6135714
3956 Apply_Other_Holds
3957 ( p_header_id => p_header_rec.header_id
3958 , p_customer_id => p_customer_id
3959 , p_site_use_id => p_site_use_id
3960 , p_party_id => l_credit_limit_entity_id
3961 , p_cc_limit_used => l_cc_limit_used
3962 , p_cc_profile_used => l_cc_profile_used
3963 ,p_holds_table => p_holds_table
3964 );
3965 ELSE -- ER 6135714
3966 Update_Holds_Table
3967 ( p_holds_table => p_holds_table
3968 , p_hold => 'OTHER'
3969 , p_cc_limit_used => l_cc_limit_used
3970 , p_cc_profile_used => l_cc_profile_used
3971 , p_customer_id => p_customer_id
3972 , p_site_use_id => p_site_use_id
3973 , p_party_id => l_credit_limit_entity_id
3974 , p_exposure => l_total_exposure
3975 , p_overall_credit_limit => l_overall_credit_limit
3976 --13939240 Start
3977 , p_limit_currency => OE_CREDIT_CHECK_LINES_PVT.G_limit_currency
3978 , p_transactional_currency =>p_header_rec.transactional_curr_code
3979 , p_conversion_type =>p_credit_check_rule_rec.conversion_type
3980 --13939240 End
3981 );
3982 END IF; -- ER 6135714
3983
3984 END IF;
3985 END IF;
3986 x_cc_result_out := l_cc_result_out;
3987 -- If no need to check order, then the non-item holds should be released.
3988 IF NVL(l_check_order,'N') = 'N' THEN
3989 x_check_exposure_mode := 'NOCHECK';
3990 END IF;
3991
3992 -----assign l_cc_limit_used to the Global
3993 -- G_cc_limit_used := l_cc_limit_used;
3994 -- bug 4002820
3995 IF nvl(g_cc_limit_used,'NULL') <> 'OVERALL' THEN
3996 G_cc_limit_used := l_cc_limit_used;
3997 END IF;
3998
3999 IF G_debug_flag = 'Y'
4000 THEN
4001 OE_DEBUG_PUB.Add('OEXVCRLB: Out Check_Other_Credit Limits');
4002 END IF;
4003
4004
4005 EXCEPTION
4006 WHEN FND_API.G_EXC_ERROR THEN
4007 x_return_status := FND_API.G_RET_STS_ERROR;
4008 RAISE FND_API.G_EXC_ERROR;
4009 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4010 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4012 WHEN OTHERS THEN
4013 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4014 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4015 OE_MSG_PUB.Add_Exc_Msg
4016 ( G_PKG_NAME, 'Check_Other_Credit_Limits');
4017 END IF;
4018 OE_DEBUG_PUB.ADD( SUBSTR(SQLERRM,1,300),1 ) ;
4019
4020 END Check_Other_Credit_Limits;
4021
4022 -------------------------------------------------
4023 -- Read from the plsql holds table and update the
4024 -- database holds table.
4025 -------------------------------------------------
4026 PROCEDURE Apply_And_Release_Holds
4027 ( p_header_id IN NUMBER
4028 , p_order_number IN NUMBER
4029 , p_holds_table IN Line_Holds_Tbl_Rectype
4030 , p_calling_action IN VARCHAR2
4031 , p_check_exposure_mode IN VARCHAR2
4032 , p_credit_hold_level IN VARCHAR2
4033 , p_credit_check_rule_rec IN
4034 OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
4035 , x_cc_result_out OUT NOCOPY VARCHAR2
4036 , x_return_status OUT NOCOPY VARCHAR2
4037 )
4038 IS
4039 l_notification_id NUMBER;
4040 l_wfn_to VARCHAR2(100);
4041 l_result_out VARCHAR2(30);
4042 l_cc_result_out VARCHAR2(30) ;
4043 l_comment VARCHAR2(2000);
4044 l_cc_hdr_result_out VARCHAR2(30) ;
4045
4046 BEGIN
4047 IF G_debug_flag = 'Y'
4048 THEN
4049 OE_DEBUG_PUB.Add('OEXVCRLB: In Apply_And_Release_Holds');
4050 OE_DEBUG_PUB.Add('p_header_id: '||p_header_id);
4051 OE_DEBUG_PUB.Add('start Loop for holds table');
4052 OE_DEBUG_PUB.Add('p_check_exposure_mode => '|| p_check_exposure_mode );
4053 END IF;
4054
4055 x_return_status := FND_API.G_RET_STS_SUCCESS;
4056 --
4057 -- Get the order number for notification
4058 --
4059
4060 FOR i IN 1..p_holds_table.COUNT LOOP
4061 IF p_holds_table(i).hold IS NOT NULL
4062 THEN
4063
4064 --bug 4503551
4065 OE_MSG_PUB.Set_Msg_Context(
4066 p_entity_code => 'LINE'
4067 ,p_entity_id => p_holds_table(i).line_id
4068 ,p_header_id => p_header_id
4069 ,p_line_id => p_holds_table(i).line_id );
4070
4071 l_cc_result_out := 'FAIL_NONE' ;
4072
4073 Apply_Line_CC_Hold
4074 ( p_header_id => p_header_id
4075 , p_order_number => p_order_number
4076 , p_line_id => p_holds_table(i).line_id
4077 , p_line_number => p_holds_table(i).line_number
4078 , p_calling_action => p_calling_action
4079 , p_cc_limit_used => p_holds_table(i).limit_used
4080 , p_cc_profile_used => p_holds_table(i).profile_used
4081 , p_customer_id => p_holds_table(i).customer_id
4082 , p_site_use_id => p_holds_table(i).site_use_id
4083 , p_party_id => p_holds_table(i).party_id
4084 , p_item_category_id => p_holds_table(i).item_category_id
4085 , p_credit_hold_level => p_credit_hold_level
4086 , p_credit_check_rule_rec => p_credit_check_rule_rec
4087 , x_cc_result_out => l_result_out
4088 );
4089
4090 IF G_debug_flag = 'Y'
4091 THEN
4092 OE_DEBUG_PUB.Add('Apply Hold: l_result_out = '|| l_result_out);
4093 END IF;
4094
4095 IF l_result_out = 'FAIL_HOLD' THEN
4096 l_cc_result_out := l_result_out;
4097 END IF;
4098
4099 OE_MSG_PUB.Reset_Msg_Context('LINE'); --bug 4503551
4100
4101 ELSIF p_check_exposure_mode = 'INLINE' OR
4102 p_check_exposure_mode = 'NOCHECK'
4103 THEN
4104
4105
4106 Release_Line_CC_Hold
4107 ( p_header_id => p_header_id
4108 , p_order_number => p_order_number
4109 , p_line_id => p_holds_table(i).line_id
4110 , p_line_number => p_holds_table(i).line_number
4111 , p_calling_action => p_calling_action
4112 , p_credit_hold_level => p_credit_hold_level
4113 , x_cc_result_out => l_result_out
4114 );
4115
4116
4117 ----------------------------------------------------------
4118 -- IF l_result_out = HDR_HOLD, thst means that there is a credit
4119 -- hold already at header level. This hold must be released
4120 -- first and then continue the lines processed again
4121 -------------------------------------------------------------
4122
4123 IF l_result_out = 'HDR_HOLD'
4124 THEN
4125 IF NVL(G_hdr_hold_released,'N') = 'N'
4126 THEN
4127 BEGIN
4128 IF G_debug_flag = 'Y'
4129 THEN
4130 OE_DEBUG_PUB.ADD('Call Releases_Order_Cc_Hold ');
4131 OE_DEBUG_PUB.ADD('Before G_hdr_hold_released = '||
4132 G_hdr_hold_released );
4133 END IF;
4134
4135 Release_Order_CC_Hold
4136 ( p_header_id => p_header_id
4137 , p_order_number => p_order_number
4138 , p_calling_action => p_calling_action
4139 , p_credit_hold_level => p_credit_hold_level
4140 , x_cc_result_out => l_cc_hdr_result_out
4141 );
4142
4143
4144 G_hdr_hold_released := 'Y' ;
4145
4146
4147 l_result_out := NULL ;
4148
4149 Release_Line_CC_Hold
4150 ( p_header_id => p_header_id
4151 , p_order_number => p_order_number
4152 , p_line_id => p_holds_table(i).line_id
4153 , p_line_number => p_holds_table(i).line_number
4154 , p_calling_action => p_calling_action
4155 , p_credit_hold_level => p_credit_hold_level
4156 , x_cc_result_out => l_result_out
4157 );
4158
4159 END ;
4160
4161 ELSE
4162 IF G_debug_flag = 'Y'
4163 THEN
4164 OE_DEBUG_PUB.ADD('Header holds released already');
4165 END IF;
4166 END IF;
4167 l_cc_hdr_result_out := NULL ;
4168 END IF; -- End HDR_HOLD
4169
4170 END IF; -- Holds table IF
4171 END LOOP;
4172
4173
4174 x_cc_result_out := l_cc_result_out;
4175
4176 IF G_debug_flag = 'Y'
4177 THEN
4178 OE_DEBUG_PUB.Add('x_cc_result_out => '|| x_cc_result_out );
4179 OE_DEBUG_PUB.Add('OEXVCRLB: Out Apply_And_Release_Holds');
4180 END IF;
4181 EXCEPTION
4182 WHEN others THEN
4183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4184 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4185 OE_MSG_PUB.Add_Exc_Msg
4186 ( G_PKG_NAME
4187 , 'Apply_And_Release_Holds'
4188 );
4189 END IF;
4190 OE_DEBUG_PUB.ADD( SUBSTR(SQLERRM,1,300),1 ) ;
4191 END Apply_And_Release_Holds;
4192
4193 ------------------------------------------------+
4194 -- Mainline Function that will read an Order |
4195 -- Header and Determine if should be checked, |
4196 -- calculates total exposure, find credit |
4197 -- and determine result for calling function. |
4198 -------------------------------------------------
4199
4200 PROCEDURE Check_order_lines_credit
4201 ( p_header_rec IN OE_ORDER_PUB.Header_Rec_Type
4202 , p_calling_action IN VARCHAR2 DEFAULT 'BOOKING'
4203 , p_credit_check_rule_rec IN OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type
4204 , p_system_parameter_rec IN OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type
4205 , x_msg_count OUT NOCOPY NUMBER
4206 , x_msg_data OUT NOCOPY VARCHAR2
4207 , x_cc_result_out OUT NOCOPY VARCHAR2
4208 , x_cc_limit_used OUT NOCOPY VARCHAR2
4209 , x_cc_profile_used OUT NOCOPY VARCHAR2
4210 , x_return_status OUT NOCOPY VARCHAR2
4211 ) IS
4212
4213 l_credit_level VARCHAR2(30); -- limits at cust or site level
4214 l_check_order VARCHAR2(1); -- if Order requires credit check
4215 l_check_exposure_mode VARCHAR2(20);
4216 l_cc_profile_used VARCHAR2(30) := NULL;
4217 l_cc_limit_used VARCHAR2(30) := NULL;
4218 l_msg_count NUMBER;
4219 l_msg_data VARCHAR2(2000);
4220 l_holds_table Line_Holds_Tbl_Rectype;
4221 l_release_order_hold VARCHAR2(1) := 'Y';
4222 l_credit_hold_level VARCHAR2(30);
4223 l_cc_result_out VARCHAR2(30);
4224 l_own_customer_id NUMBER;
4225 l_global_exposure_flag VARCHAR2(1);
4226 l_party_id NUMBER; -----------------new (FPI)
4227 l_request_id NUMBER; -----------------new (FPI)
4228 l_customer_id NUMBER; -----------------------new (FPI)
4229 l_site_use_id NUMBER; -----------------------new (FPI)
4230 l_source_org_id NUMBER; -----------------------new (FPI)
4231 l_source_user_id NUMBER; -----------------------new (FPI)
4232 l_source_resp_id NUMBER; -----------------------new (FPI)
4233 l_source_appln_id NUMBER; -----------------------new (FPI)
4234 l_source_security_group_id NUMBER; -----------------------new (FPI)
4235
4236 -- bug 5907331
4237 l_review_party_id NUMBER;
4238
4239 --ER 12363706 start
4240 l_manual_hold_exists VARCHAR2(1) := 'N';
4241 l_hold_source_id NUMBER;
4242 l_release_reason_code OE_HOLD_RELEASES.RELEASE_REASON_CODE%TYPE;
4243 l_created_by OE_HOLD_RELEASES.CREATED_BY%TYPE;
4244 --ER 12363706 end
4245
4246
4247 CURSOR cust_and_site_csr IS
4248 SELECT DISTINCT
4249 ool.invoice_to_org_id site_use_id
4250 FROM oe_order_lines_all ool
4251 WHERE ool.header_id = p_header_rec.header_id
4252 AND ool.open_flag = 'Y'
4253 AND NVL(ool.invoiced_quantity,0) = 0
4254 AND NVL(ool.shipped_quantity,0) = 0
4255 ORDER BY 1 ;
4256
4257
4258 -- bug 4767772
4259 -- to select lines on credit checking hold but having payment term with
4260 -- credit check flag unchecked, this might be resulted by user changing
4261 -- the payment term after hold got applied.
4262 CURSOR lines_on_hold IS
4263 SELECT l.line_id, l.line_number
4264 FROM oe_order_headers_all h,
4265 oe_order_lines_all l,
4266 ra_terms t
4267 WHERE h.header_id = p_header_rec.header_id
4268 AND h.header_id = l.header_id
4269 AND l.payment_term_id = t.term_id
4270 AND nvl(t.credit_check_flag, 'N') = 'N'
4271 AND (EXISTS
4272 (SELECT 'Y'
4273 FROM oe_payment_types_all pt
4274 WHERE NVL(l.payment_type_code, 'N') = pt.payment_type_code
4275 AND pt.credit_check_flag = 'N'
4276 )
4277 OR l.payment_type_code IS NULL
4278 )
4279 AND (EXISTS
4280 (SELECT 'Y'
4281 FROM oe_order_holds_all oh,
4282 oe_hold_sources_all hs
4283 WHERE oh.header_id = p_header_rec.header_id
4284 AND oh.line_id = l.line_id
4285 AND oh.hold_release_id IS NULL
4286 AND oh.hold_source_id = hs.hold_source_id
4287 AND hs.hold_id = 1
4288 ));
4289
4290 --ER 12363706 start
4291
4292 CURSOR latest_hold_rec(p_header_id IN NUMBER) IS
4293 SELECT OHS.HOLD_SOURCE_ID
4294 FROM OE_ORDER_HOLDS_ALL OOH,
4295 OE_HOLD_SOURCES_ALL OHS
4296 WHERE OOH.HOLD_SOURCE_ID = OHS.HOLD_SOURCE_ID
4297 AND OOH.HEADER_ID = p_header_id
4298 AND OHS.HOLD_ID = 1
4299 ORDER BY OOH.last_update_date DESC;
4300
4301 --ER 12363706 end
4302
4303 BEGIN
4304 IF G_debug_flag = 'Y'
4305 THEN
4306 OE_DEBUG_PUB.Add('OEXVCRLB: In Check_order_lines_credit API',1);
4307 END IF;
4308
4309 --
4310 -- Set the default behavior to pass credit check
4311 --
4312 x_cc_result_out := 'NOCHECK';
4313 x_return_status := FND_API.G_RET_STS_SUCCESS;
4314 l_global_exposure_flag := 'N' ;
4315
4316 G_result_out := 'PASS' ;
4317 G_release_status := 'NO' ;
4318 G_hdr_hold_released := 'N' ;
4319
4320
4321 OE_Credit_Engine_GRP.G_currency_error_msg := NULL;
4322
4323 l_credit_hold_level := p_credit_check_rule_rec.CREDIT_HOLD_LEVEL_CODE ;
4324
4325 IF G_debug_flag = 'Y'
4326 THEN
4327 OE_DEBUG_PUB.Add('Inital starting G_result_out = '|| G_result_out);
4328 OE_DEBUG_PUB.Add('l_credit_hold_level => '||
4329 l_credit_hold_level );
4330 OE_DEBUG_PUB.Add('Inital G_hdr_hold_released = '||
4331 G_hdr_hold_released);
4332 OE_DEBUG_PUB.Add('Inital G_release_status = '||
4333 G_release_status );
4334 OE_DEBUG_PUB.Add('G_currency_error_msg = '||
4335 OE_Credit_Engine_GRP.G_currency_error_msg );
4336 OE_DEBUG_PUB.Add(' ');
4337 OE_DEBUG_PUB.Add(' ---------------------------------------');
4338 OE_DEBUG_PUB.Add(' ');
4339 OE_DEBUG_PUB.Add(' p_calling_action = ' || p_calling_action );
4340 OE_DEBUG_PUB.Add(' OEXVCRLB:Header ID = ' || p_header_rec.header_id );
4341 OE_DEBUG_PUB.Add(' ORDER NUMBER = ' || p_header_rec.order_number );
4342 OE_DEBUG_PUB.Add(' Credit check rule ID = '
4343 || p_credit_check_rule_rec.credit_check_rule_id );
4344 OE_DEBUG_PUB.Add(' conversion type = '
4345 || p_credit_check_rule_rec.conversion_type );
4346 OE_DEBUG_PUB.Add(' Credit check level = '
4347 || p_credit_check_rule_rec.credit_check_level_code );
4348
4349 OE_DEBUG_PUB.Add(' CHECK_ITEM_CATEGORIES_FLAG = '
4350 || p_credit_check_rule_rec.CHECK_ITEM_CATEGORIES_FLAG );
4351 OE_DEBUG_PUB.Add(' SEND_HOLD_NOTIFICATIONS_FLAG = '
4352 || p_credit_check_rule_rec.SEND_HOLD_NOTIFICATIONS_FLAG );
4353 OE_DEBUG_PUB.Add(' ');
4354 OE_DEBUG_PUB.Add(' ---------------------------------------');
4355 OE_DEBUG_PUB.Add(' ');
4356 OE_DEBUG_PUB.Add('start SITE loop ');
4357 END IF;
4358
4359 -- bug 4767772
4360 -- release the credit hold for lines with credit check flag not enabled.
4361 FOR c_lines IN lines_on_hold
4362 LOOP
4363
4364 Release_Line_CC_Hold
4365 ( p_header_id => p_header_rec.header_id
4366 , p_order_number => p_header_rec.order_number
4367 , p_line_id => c_lines.line_id
4368 , p_line_number => c_lines.line_number
4369 , p_calling_action => p_calling_action
4370 , p_credit_hold_level => 'LINE'
4371 , x_cc_result_out => l_cc_result_out
4372 );
4373
4374 IF G_debug_flag = 'Y'
4375 THEN
4376 oe_debug_pub.add ('line level credit checking hold is released for line: '||c_lines.line_id,3);
4377 oe_debug_pub.add ('l_cc_result_out is : '||l_cc_result_out ,3);
4378 END IF;
4379
4380 END LOOP;
4381
4382 --ER 12363706 start
4383
4384 -- Call Tolerance check for LINE level credit checking and ORDER level hold
4385 IF NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) = FND_API.G_TRUE
4386 THEN
4387 IF l_credit_hold_level = 'ORDER'
4388 THEN
4389 oe_debug_pub.ADD('OEXVCRLB: Credit check at Order Level');
4390 IF OE_CREDIT_ENGINE_GRP.TOLERANCE_CHECK_REQUIRED
4391 THEN
4392 oe_debug_pub.ADD('OEXVCRLB: Tolerance Check is required');
4393 IF NOT OE_HOLDS_PUB.Hold_exists( p_hold_entity_code => 'O'
4394 , p_hold_entity_id => p_header_rec.header_id
4395 , p_hold_id => 1
4396 , p_org_id => mo_global.get_current_org_id
4397 )
4398 THEN
4399 oe_debug_pub.ADD('OEXVCRLB: Active hold does not exists.');
4400 IF Check_Manual_Released_Holds( p_calling_action => p_calling_action ,
4401 p_credit_hold_level => l_credit_hold_level ,
4402 p_hold_id => 1 ,
4403 p_header_id => p_header_rec.header_id ,
4404 p_line_id => NULL ,
4405 p_credit_check_rule_rec=>p_credit_check_rule_rec) = 'Y'
4406 THEN
4407 oe_debug_pub.ADD('OEXVCRLB: Manually released hold exists.');
4408 IF OE_CREDIT_ENGINE_GRP.CREDIT_TOLERANCE_CHECK( p_header_id => p_header_rec.header_id) THEN
4409 OE_CREDIT_ENGINE_GRP.TOLERANCE_CHECK_REQUIRED := TRUE;
4410
4411 IF G_debug_flag = 'Y'
4412 THEN
4413 oe_debug_pub.ADD('OEXVCRLB: Tolerance Check Passed');
4414 END IF;
4415
4416 RETURN;
4417 END IF;
4418 END IF;
4419 END IF;
4420 END IF;
4421 END IF;
4422 END IF;
4423
4424 -- Call Tolerance check if LINE level credit checking and LINE level hold
4425 IF NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) = FND_API.G_TRUE
4426 THEN
4427 IF l_credit_hold_level = 'LINE'
4428 THEN
4429 oe_debug_pub.ADD('OEXVCRLB: Credit check at Line Level');
4430 IF OE_CREDIT_ENGINE_GRP.TOLERANCE_CHECK_REQUIRED
4431 THEN
4432 oe_debug_pub.ADD('OEXVCRLB: Tolerance Check is required');
4433 OPEN latest_hold_rec(p_header_rec.header_id);
4434
4435 FETCH latest_hold_rec INTO l_hold_source_id;
4436
4437 CLOSE latest_hold_rec;
4438
4439 IF G_debug_flag = 'Y'
4440 THEN
4441 oe_debug_pub.ADD('OEXVCRLB: Latest Hold Source id: '||l_hold_source_id);
4442 END IF;
4443
4444 BEGIN
4445 SELECT RELEASE_REASON_CODE,CREATED_BY
4446 INTO l_release_reason_code,l_created_by
4447 FROM OE_HOLD_RELEASES
4448 WHERE HOLD_SOURCE_ID = l_hold_source_id;
4449
4450 IF G_debug_flag = 'Y'
4451 THEN
4452 oe_debug_pub.ADD('OEXVCRLB: Latest Hold Source Reason Code:'|| l_release_reason_code);
4453 oe_debug_pub.ADD('OEXVCRLB: Latest Hold Source Created By:' || l_created_by);
4454 END IF;
4455
4456 IF l_release_reason_code <> 'PASS_CREDIT' AND l_created_by <> 1
4457 THEN
4458 l_manual_hold_exists := 'Y';
4459 END IF;
4460 EXCEPTION
4461 WHEN OTHERS THEN
4462 oe_debug_pub.ADD('OEXVCRLB: Error Others');
4463 l_manual_hold_exists := 'N';
4464 END;
4465
4466 IF G_debug_flag = 'Y'
4467 THEN
4468 oe_debug_pub.ADD('OEXVCRLB: l_manual_hold_exists:'||l_manual_hold_exists);
4469 END IF;
4470
4471 IF l_manual_hold_exists = 'Y'
4472 THEN
4473 IF OE_CREDIT_ENGINE_GRP.CREDIT_TOLERANCE_CHECK( p_header_id => p_header_rec.header_id) THEN
4474 OE_CREDIT_ENGINE_GRP.TOLERANCE_CHECK_REQUIRED := TRUE;
4475 IF G_debug_flag = 'Y'
4476 THEN
4477 oe_debug_pub.ADD('OEXVCRLB: Tolerance Check Passed');
4478 END IF;
4479 RETURN;
4480 END IF;
4481 END IF;
4482 END IF;
4483 END IF;
4484 END IF;
4485
4486 OE_CREDIT_ENGINE_GRP.TOLERANCE_CHECK_REQUIRED := TRUE;
4487 --ER 12363706 end
4488
4489
4490
4491 FOR c_site IN cust_and_site_csr
4492 LOOP
4493 IF G_debug_flag = 'Y'
4494 THEN
4495 OE_DEBUG_PUB.ADD('OEXVCRLB: HeaderID/SiteUseID:' ||
4496 p_header_rec.header_id || '/' || c_site.site_use_id, 1);
4497 END IF;
4498
4499 l_own_customer_id := NULL ;
4500
4501 BEGIN
4502 SELECT /* MOAC_SQL_CHANGE */ cas.cust_account_id
4503 , ca.party_id --------------new (FPI)
4504 INTO l_own_customer_id
4505 , l_party_id --------------new (FPI)
4506 FROM HZ_cust_acct_sites_all cas
4507 , HZ_cust_site_uses su
4508 , hz_cust_accounts_all ca --------------new (FPI)
4509 WHERE su.site_use_id = c_site.site_use_id
4510 AND cas.cust_acct_site_id = su.cust_acct_site_id
4511 AND cas.cust_account_id=ca.cust_account_id; ---------new (FPI)
4512
4513
4514 IF G_debug_flag = 'Y'
4515 THEN
4516 OE_DEBUG_PUB.ADD(' l_own_customer_id = '|| l_own_customer_id );
4517 END IF;
4518
4519 EXCEPTION
4520 WHEN NO_DATA_FOUND
4521 THEN
4522 OE_DEBUG_PUB.ADD(' Exception - No data found ');
4523 RAISE;
4524 WHEN TOO_MANY_ROWS
4525 THEN
4526 OE_DEBUG_PUB.ADD(' Exception - TOO_MANY_ROWS');
4527 RAISE;
4528 END ;
4529
4530
4531
4532 --
4533 -------------------------------------------------------
4534 -- Initialize site level variables.
4535 -------------------------------------------------------
4536 --
4537 -- Recreate the plsql holds table for each site
4538 --
4539
4540 Create_Holds_Table
4541 ( p_header_id => p_header_rec.header_id
4542 , p_site_use_id => c_site.site_use_id
4543 , x_holds_table => l_holds_table
4544 );
4545
4546 IF G_debug_flag = 'Y'
4547 THEN
4548 OE_DEBUG_PUB.Add('PLSQL Holds table created');
4549 END IF;
4550
4551 ----------------------------------------------------------
4552 -- Perform item category credit check for the order site
4553 -- IF the check failed,
4554 -- set the result to FAIL.
4555 -- IF no credit check is performed, THEN return NOCHECK in
4556 -- x_cc_result_out
4557 ----------------------------------------------------------
4558 --
4559 ---------------------------------------------------+
4560 -- order site use is subject to credit check: |
4561 ---------------------------------------------------|
4562 -- check 1: item limit <-- in progress |
4563 -- check 2: max-past-due-inv limit |
4564 -- check 3: trx limit |
4565 -- check 4: overall limit |
4566 ---------------------------------------------------+
4567
4568 IF G_debug_flag = 'Y'
4569 THEN
4570 OE_DEBUG_PUB.Add(' table count = '|| l_holds_table.COUNT );
4571 END IF;
4572
4573 IF l_holds_table.COUNT > 0
4574 THEN
4575
4576 IF p_credit_check_rule_rec.CHECK_ITEM_CATEGORIES_FLAG = 'Y'
4577 THEN
4578
4579 Check_Item_Limits
4580 ( p_header_rec => p_header_rec
4581 , p_customer_id => l_own_customer_id
4582 , p_site_use_id => c_site.site_use_id
4583 , p_calling_action => p_calling_action
4584 , p_credit_check_rule_rec => p_credit_check_rule_rec
4585 , p_system_parameter_rec => p_system_parameter_rec
4586 , p_holds_table => l_holds_table
4587 , x_cc_result_out => l_cc_result_out
4588 , x_return_status => x_return_status
4589 );
4590
4591 ELSE
4592 l_cc_result_out := 'PASS' ;
4593 x_return_status := FND_API.G_RET_STS_SUCCESS;
4594
4595 IF G_debug_flag = 'Y'
4596 THEN
4597 OE_DEBUG_PUB.Add(' No check item categories, Flag OFF ');
4598 END IF;
4599
4600 END IF;
4601
4602
4603 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4604 RAISE FND_API.G_EXC_ERROR;
4605 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4607 END IF;
4608 --
4609 -- Apply the database hold and Exit the bill-to site loop if
4610 -- credit hold level is ORDER and the order failed credit check.
4611 --
4612 IF l_credit_hold_level = 'ORDER' AND
4613 ( l_cc_result_out = 'FAIL' OR l_cc_result_out = 'FAIL_HOLD' OR
4614 l_cc_result_out = 'FAIL_NONE')
4615 THEN
4616 -- set the order hold release flag
4617 l_release_order_hold := 'N';
4618
4619 IF G_debug_flag = 'Y'
4620 THEN
4621 OE_DEBUG_PUB.Add(' Exit SITE loop as order failed ');
4622 END IF;
4623
4624 EXIT;
4625
4626 END IF;
4627 --
4628 -- Check other credit limits regardless if the lines of the
4629 -- site have item category failure. Since only lines with
4630 -- items belonging to the failed item category are placed on
4631 -- hold, other lines will need to be checked for other holds.
4632 -- Otherwise, if no further checking is done, then those lines
4633 -- can be booked even though they might fail other credit limits.
4634 --
4635
4636 ---------------------------------------------------+
4637 -- Check other credit limits for the bill-to site: |
4638 -- check 2: max-past-due-inv limit |
4639 -- check 3: trx limit |
4640 -- check 4: overall limit |
4641 ---------------------------------------------------+
4642
4643
4644 Check_Other_Credit_Limits
4645 ( p_header_rec => p_header_rec
4646 , p_customer_id => l_own_customer_id
4647 , p_site_use_id => c_site.site_use_id
4648 , p_calling_action => p_calling_action
4649 , p_credit_check_rule_rec => p_credit_check_rule_rec
4650 , p_system_parameter_rec => p_system_parameter_rec
4651 , p_holds_table => l_holds_table
4652 , p_party_id => l_party_id
4653 , x_credit_level => l_credit_level
4654 , x_check_exposure_mode => l_check_exposure_mode
4655 , x_cc_result_out => l_cc_result_out
4656 , x_return_status => x_return_status
4657 , x_global_exposure_flag => l_global_exposure_flag
4658 );
4659
4660 IF G_debug_flag = 'Y'
4661 THEN
4662 OE_DEBUG_PUB.Add('Check_Other_Credit_Limits : Result Out = '
4663 || l_cc_result_out );
4664 OE_DEBUG_PUB.Add('Check_Other_Credit_Limits: Return Status = '
4665 || x_return_status );
4666 END IF;
4667
4668
4669 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4670 RAISE FND_API.G_EXC_ERROR;
4671 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4673 END IF;
4674
4675 -- Apply order level credit hold to the database if it necessary
4676
4677 IF l_credit_hold_level = 'ORDER' AND
4678 ( l_cc_result_out = 'FAIL' OR l_cc_result_out = 'FAIL_HOLD' OR
4679 l_cc_result_out = 'FAIL_NONE') THEN
4680 -- set the order hold release flag
4681 l_release_order_hold := 'N';
4682
4683 IF G_debug_flag = 'Y'
4684 THEN
4685 OE_DEBUG_PUB.Add(' Exit site loop as FAILED ');
4686 END IF;
4687
4688 EXIT; --exit out of bill-to site loop
4689
4690 END IF;
4691
4692 --
4693 -- Actually apply and release holds in the database table.
4694 --
4695
4696 IF l_cc_result_out <> 'NOCHECK'
4697 AND l_credit_hold_level = 'LINE'
4698 THEN
4699
4700
4701
4702 Apply_And_Release_Holds
4703 ( p_header_id => p_header_rec.header_id
4704 , p_order_number => p_header_rec.order_number
4705 , p_holds_table => l_holds_table
4706 , p_calling_action => p_calling_action
4707 , p_check_exposure_mode => l_check_exposure_mode
4708 , p_credit_hold_level => l_credit_hold_level
4709 , p_credit_check_rule_rec => p_credit_check_rule_rec
4710 , x_cc_result_out => l_cc_result_out
4711 , x_return_status => x_return_status
4712 );
4713
4714
4715 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
4716 RAISE FND_API.G_EXC_ERROR;
4717 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4719 END IF;
4720 END IF;
4721
4722 ------------------------- Credit review ------------
4723
4724 --kadiraju changes begin for Bug#13768161
4725 --IF l_cc_result_out in ('FAIL_HOLD','FAIL_NONE','FAIL')
4726 IF l_cc_result_out in ('FAIL_HOLD','FAIL')
4727 --kadiraju changes End for Bug#13768161
4728 THEN
4729 ---------submit AR Credit Review---------
4730 --IF OE_CREDIT_CHECK_LINES_PVT.G_cc_limit_used = 'OVERALL' --ER8880886
4731 --THEN --ER8880886
4732
4733 IF OE_CREDIT_CHECK_UTIL.G_crmgmt_installed is NULL
4734 THEN
4735 OE_CREDIT_CHECK_UTIL.G_crmgmt_installed :=
4736 AR_CMGT_CREDIT_REQUEST_API.is_Credit_Management_Installed ;
4737 END IF;
4738
4739 IF OE_CREDIT_CHECK_UTIL.G_crmgmt_installed
4740 THEN
4741
4742 IF G_debug_flag = 'Y'
4743 THEN
4744 OE_DEBUG_PUB.Add('Calling Create_credit_request ');
4745 END IF;
4746 -- bug 5907331
4747 l_review_party_id := l_party_id;
4748 ------check if the credit check level is PARTY, CUSTOMER or SITE
4749 IF l_credit_level ='PARTY'
4750 THEN
4751 l_customer_id := NULL;
4752 l_site_use_id := NULL;
4753 -- bug 5907331
4754 IF l_party_id <> nvl(G_credit_limit_entity_id ,l_party_id) THEN
4755 l_review_party_id := G_credit_limit_entity_id;
4756 END IF;
4757
4758 ELSIF l_credit_level ='CUSTOMER'
4759 THEN
4760 l_customer_id := l_own_customer_id ;
4761 l_site_use_id := NULL;
4762 ELSIF l_credit_level ='SITE'
4763 THEN
4764 l_customer_id := l_own_customer_id;
4765 l_site_use_id := c_site.site_use_id;
4766 END IF;
4767
4768 -------------get profile values:
4769 l_source_org_id := p_header_rec.org_id; /* MOAC ORG_ID CHANGE */ --FND_PROFILE.VALUE('ORG_ID');
4770 l_source_user_id := FND_PROFILE.VALUE ('USER_ID');
4771 l_source_resp_id := FND_PROFILE.VALUE ('RESP_ID');
4772 l_source_appln_id := FND_PROFILE.VALUE ('RESP_APPL_ID');
4773 l_source_security_group_id :=
4774 FND_PROFILE.VALUE('SECURITY_GROUP_ID');
4775
4776 IF G_debug_flag = 'Y'
4777 THEN
4778 OE_DEBUG_PUB.Add('Calling Create_credit_request,
4779 credit check level= '||l_credit_level);
4780 OE_DEBUG_PUB.Add('Parameters: ');
4781 OE_DEBUG_PUB.Add('-------------------------------------------');
4782 OE_DEBUG_PUB.Add('p_requestor_id= '||TO_CHAR(fnd_global.employee_id));
4783 OE_DEBUG_PUB.Add('p_review_type= ORDER_HOLD');
4784 OE_DEBUG_PUB.Add('p_credit_classification= NULL');
4785 OE_DEBUG_PUB.Add('p_requested_amount= '||
4786 TO_CHAR(OE_CREDIT_CHECK_LINES_PVT.G_total_site_exposure ));
4787 OE_DEBUG_PUB.Add('p_requested_currency= '||
4788 OE_CREDIT_CHECK_LINES_PVT.G_limit_currency);
4789 OE_DEBUG_PUB.Add('p_trx_amount= '||
4790 TO_CHAR(OE_CREDIT_CHECK_UTIL.g_current_order_value));
4791 OE_DEBUG_PUB.Add('p_trx_currency= '||
4792 p_header_rec.transactional_curr_code );
4793 OE_DEBUG_PUB.Add('p_credit_type = TRADE' );
4794 OE_DEBUG_PUB.Add('p_term_length = NULL' );
4795 OE_DEBUG_PUB.Add('p_credit_check_rule_id= '||
4796 TO_CHAR(p_credit_check_rule_rec.credit_check_rule_id));
4797 OE_DEBUG_PUB.Add('p_credit_request_status = SUBMIT');
4798 OE_DEBUG_PUB.Add('p_party_id= '||TO_CHAR(l_party_id));
4799 OE_DEBUG_PUB.Add('p_cust_account_id= '||TO_CHAR(l_customer_id));
4800 OE_DEBUG_PUB.Add('p_cust_acct_site_id = NULL');
4801 OE_DEBUG_PUB.Add('p_site_use_id= '||TO_CHAR(l_site_use_id));
4802 OE_DEBUG_PUB.Add('p_contact_party_id = NULL');
4803 OE_DEBUG_PUB.Add('p_notes = NULL');
4804 OE_DEBUG_PUB.Add('p_source_org_id= '||TO_CHAR(l_source_org_id));
4805 OE_DEBUG_PUB.Add('p_source_user_id= '||TO_CHAR(l_source_user_id));
4806 OE_DEBUG_PUB.Add('p_source_resp_id= '||TO_CHAR(l_source_resp_id));
4807 OE_DEBUG_PUB.Add('p_source_appln_id= '||TO_CHAR(l_source_appln_id));
4808 OE_DEBUG_PUB.Add('p_source_security_group_id= '||TO_CHAR(l_source_security_group_id));
4809 OE_DEBUG_PUB.Add('p_source_name = OM');
4810 OE_DEBUG_PUB.Add('p_source_column1 = header_id= '||
4811 TO_CHAR(p_header_rec.header_id));
4812 OE_DEBUG_PUB.Add('p_source_column2 = order_number= '||
4813 TO_CHAR(p_header_rec.order_number));
4814 OE_DEBUG_PUB.Add('p_source_column3= LINE');
4815
4816 END IF;
4817 ----------------Submit Credit Review--------------------
4818 AR_CMGT_CREDIT_REQUEST_API.Create_credit_request
4819 ( p_api_version => 1.0
4820 , p_init_msg_list => FND_API.G_FALSE
4821 , p_commit => FND_API.G_FALSE
4822 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
4823 , x_return_status => x_return_status
4824 , x_msg_count => l_msg_count
4825 , x_msg_data => l_msg_data
4826 , p_application_number => NULL
4827 , p_application_date => SYSDATE
4828 , p_requestor_type => NULL
4829 , p_requestor_id => fnd_global.employee_id
4830 , p_review_type => 'ORDER_HOLD'
4831 , p_credit_classification => NULL
4832 , p_requested_amount =>
4833 OE_CREDIT_CHECK_LINES_PVT.G_total_site_exposure
4834 , p_requested_currency =>
4835 OE_CREDIT_CHECK_LINES_PVT.G_limit_currency
4836 , p_trx_amount =>
4837 OE_CREDIT_CHECK_UTIL.g_current_order_value
4838 , p_trx_currency => p_header_rec.transactional_curr_code
4839 , p_credit_type => 'TRADE'
4840 , p_term_length => NULL --the unit is no of months
4841 , p_credit_check_rule_id =>
4842 p_credit_check_rule_rec.credit_check_rule_id
4843 , p_credit_request_status => 'SUBMIT'
4844 , p_party_id => l_review_party_id -- bug 5907331
4845 , p_cust_account_id => l_customer_id
4846 , p_cust_acct_site_id => NULL
4847 , p_site_use_id => l_site_use_id
4848 , p_contact_party_id => NULL --party_id of the pseudo party
4849 , p_notes => NULL --contact relationship.
4850 , p_source_org_id => l_source_org_id
4851 , p_source_user_id => l_source_user_id
4852 , p_source_resp_id => l_source_resp_id
4853 , p_source_appln_id => l_source_appln_id
4854 , p_source_security_group_id => l_source_security_group_id
4855 , p_source_name => 'OM'
4856 , p_source_column1 => p_header_rec.header_id
4857 , p_source_column2 => p_header_rec.order_number
4858 , p_source_column3 => 'LINE'
4859 , p_credit_request_id => l_request_id
4860 , p_hold_reason_rec => g_hold_reason_rec --ER8880886
4861 );
4862
4863 IF x_return_status='S'
4864 THEN
4865
4866 --bug 4503551
4867 OE_MSG_PUB.Set_Msg_Context(
4868 p_entity_code => 'HEADER'
4869 ,p_entity_id => p_header_rec.header_id
4870 ,p_header_id => p_header_rec.header_id );
4871
4872 FND_MESSAGE.Set_Name('ONT','OE_CC_CMGT_REVIEW');
4873 FND_MESSAGE.Set_Token('REQUEST_ID',l_request_id);
4874 OE_MSG_PUB.Add;
4875 OE_MSG_PUB.Reset_Msg_Context('HEADER'); --bug 4503551
4876 END IF;
4877
4878 IF G_debug_flag = 'Y'
4879 THEN
4880 IF x_return_status='S'
4881 THEN
4882
4883 OE_DEBUG_PUB.Add('Credit review submitted, request_id= '
4884 ||TO_CHAR(l_request_id));
4885 ELSE
4886 OE_DEBUG_PUB.Add('Credit review has not been submitted');
4887 END IF;
4888 END IF;
4889
4890 OE_DEBUG_PUB.Add('l_request_id= '||TO_CHAR(l_request_id));
4891 OE_DEBUG_PUB.Add('x_return_status= '||x_return_status);
4892 OE_DEBUG_PUB.Add('l_msg_count= '||TO_CHAR(l_msg_count));
4893 OE_DEBUG_PUB.Add('l_msg_data= '||l_msg_data);
4894
4895
4896 END IF;
4897
4898 --END IF; --ER8880886
4899 END IF; -- credit rev
4900 ---------------------------------End Credit review --------------
4901
4902
4903 G_total_site_exposure := 0; ----------new (FPI)
4904 G_limit_currency := NULL ;
4905 G_cc_limit_used := NULL ;
4906 --
4907 -- Return null for output since it is meaningless at the order level
4908 --
4909 IF l_cc_result_out = 'NOCHECK'
4910 AND l_credit_hold_level = 'LINE'
4911 THEN
4912 IF G_debug_flag = 'Y'
4913 THEN
4914 OE_DEBUG_PUB.Add('No credit check required');
4915 END IF;
4916 --x_cc_result_out := 'NOCHECK';
4917
4918
4919 Apply_And_Release_Holds
4920 ( p_header_id => p_header_rec.header_id
4921 , p_order_number => p_header_rec.order_number
4922 , p_holds_table => l_holds_table
4923 , p_calling_action => p_calling_action
4924 , p_check_exposure_mode => l_check_exposure_mode
4925 , p_credit_hold_level => l_credit_hold_level
4926 , p_credit_check_rule_rec => p_credit_check_rule_rec
4927 , x_cc_result_out => x_cc_result_out
4928 , x_return_status => x_return_status
4929 );
4930
4931 ELSE
4932 x_cc_result_out := l_cc_result_out ;
4933 x_cc_limit_used := l_cc_limit_used;
4934 x_cc_profile_used := l_cc_profile_used;
4935 END IF;
4936
4937 ELSE
4938 OE_DEBUG_PUB.Add('No credit check as table count = 0 ');
4939 END IF ; -- count IF
4940
4941 END LOOP; -- End of Loop
4942
4943 -- Release order level credit hold if it exist and if the
4944 IF G_debug_flag = 'Y'
4945 THEN
4946 OE_DEBUG_PUB.Add(' x_cc_result_out = '|| x_cc_result_out );
4947 OE_DEBUG_PUB.Add(' x_cc_limit_used = '|| x_cc_limit_used );
4948 OE_DEBUG_PUB.Add(' x_cc_profile_used = '|| x_cc_profile_used );
4949 OE_DEBUG_PUB.Add(' l_release_order_hold = '|| l_release_order_hold );
4950 END IF;
4951
4952
4953 IF l_credit_hold_level = 'ORDER'
4954 AND l_release_order_hold = 'Y'
4955 -- AND l_cc_result_out <> 'NOCHECK'
4956 THEN
4957
4958 Release_Order_CC_Hold
4959 ( p_header_id => p_header_rec.header_id
4960 , p_order_number => p_header_rec.order_number
4961 , p_calling_action => p_calling_action
4962 , p_credit_hold_level =>
4963 p_credit_check_rule_rec.credit_hold_level_code
4964
4965 , x_cc_result_out => l_cc_result_out
4966 );
4967 END IF;
4968 -- Bug 4506263 FP
4969 -- x_cc_result_out := G_result_out ;
4970 x_cc_result_out := l_cc_result_out ;
4971
4972 IF G_debug_flag = 'Y'
4973 THEN
4974 OE_DEBUG_PUB.Add(' l_cc_result_out = '|| l_cc_result_out);
4975 OE_DEBUG_PUB.Add(' G_result_out = '|| G_result_out );
4976 OE_DEBUG_PUB.Add(' G_release_status = '|| G_release_status );
4977 OE_DEBUG_PUB.Add(' final x_cc_result_out = '|| x_cc_result_out,1 );
4978 END IF;
4979 --
4980 IF l_credit_hold_level = 'LINE'
4981 THEN
4982 -- fix bug 4558056
4983 -- OE_MSG_PUB.Save_Messages(1);
4984 -- OE_MSG_PUB.Delete_Msg(OE_MSG_PUB.G_msg_count);
4985
4986 -- added OR condition for bug 5467793
4987 IF x_cc_result_out = 'FAIL'
4988 --OR( NVL(OE_credit_engine_GRP.G_delayed_request, FND_API.G_FALSE ) =
4989 --FND_API.G_TRUE AND x_cc_result_out IN ('FAIL_HOLD', 'FAIL_NONE'))
4990 THEN
4991 -- Display the general message for the user on the screen
4992
4993 FND_MESSAGE.Set_Name('ONT','OE_CC_HLD_GENERAL_MSG');
4994
4995 OE_MSG_PUB.Add;
4996 END IF;
4997
4998 IF G_release_status = 'RELEASED'
4999 THEN
5000
5001 FND_MESSAGE.Set_Name('ONT','OE_CC_HOLD_REMOVED');
5002 OE_MSG_PUB.Add;
5003
5004 END IF;
5005
5006 END IF;
5007
5008 OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab.delete; --13706069
5009 IF G_debug_flag = 'Y'
5010 THEN
5011 OE_DEBUG_PUB.Add('OEXVCRLB: Out Check_order_lines_credit API',1);
5012 END IF;
5013
5014 EXCEPTION
5015 WHEN FND_API.G_EXC_ERROR THEN
5016 x_return_status := FND_API.G_RET_STS_ERROR;
5017 OE_CREDIT_ENGINE_GRP.TOLERANCE_CHECK_REQUIRED := TRUE; --ER 12363706
5018 OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab.delete; --13706069
5019 OE_DEBUG_PUB.Add('Check_order_lines_credit: Error ',1);
5020
5021 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5022 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5023 OE_CREDIT_ENGINE_GRP.TOLERANCE_CHECK_REQUIRED := TRUE; --ER 12363706
5024 OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab.delete; --13706069
5025 OE_DEBUG_PUB.Add('Check_order_lines_credit: Unexpected Error ',1);
5026 WHEN OTHERS THEN
5027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5028 OE_CREDIT_ENGINE_GRP.TOLERANCE_CHECK_REQUIRED := TRUE; --ER 12363706
5029 OE_CREDIT_CHECK_UTIL.G_CC_Invoice_tab.delete; --13706069
5030 OE_DEBUG_PUB.Add('Check_order_lines_credit: Other Unexpected Error ',1);
5031 OE_DEBUG_PUB.ADD( SUBSTR(SQLERRM,1,300),1 ) ;
5032 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5033 OE_MSG_PUB.Add_Exc_Msg
5034 ( G_PKG_NAME
5035 , 'Check_order_lines_credit'
5036 );
5037 END IF;
5038 END Check_order_lines_credit;
5039
5040
5041 END OE_credit_check_lines_PVT;