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