[Home] [Help]
PACKAGE BODY: APPS.HZ_CREDIT_REQUEST_PVT
Source
4 --------------------
1 PACKAGE BODY HZ_CREDIT_REQUEST_PVT AS
2 -- $Header: OEXCRRQB.pls 120.6.12020000.3 2012/11/27 20:48:12 cpati ship $
3
5 -- TYPE DECLARATIONS
6 --------------------
7
8 ------------
9 -- CONSTANTS
10 ------------
11
12 -------------------------
13 -- PUBLIC VARIABLES
14 -------------------------
15 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_CREDIT_REQUEST_PVT';
16 ----------------------
17 -- PRIVATE VARIABLES
18 ----------------------
19 ---added for bug#8617023 start
20 G_debug_flag VARCHAR2(1) := NVL( OE_CREDIT_CHECK_UTIL.check_debug_flag ,'N') ;
21 G_hdr_hold_released VARCHAR2(1) := 'N' ;
22 ---added for bug#8617023 end
23
24 ---------------------------
25 -- PROCEDURES AND FUNCTIONS
26 ---------------------------
27
28 --------------------------------------------------------------------------------------------
29 -- This procedure is added for bug 8617023.
30 -- After this change when Credit Manager will submitt the recomendation
31 -- to release Credit Check Faliure Hold Release_Order_CC_Hold procedure will be used
32 -- instead of OE_CREDIT_CHECK_ORDER_PVT.Release_Order_CC_Hold.
33 -- This procedure has an extra parameter of p_user_id to denote the User who actually
34 -- released the hold. The hold release will be treated as Manual release instead of
35 -- automatic release.
36 --------------------------------------------------------------------------------------------
37
38 PROCEDURE Release_Order_CC_Hold ( p_header_id IN NUMBER ,
39 p_order_number IN NUMBER ,
40 p_calling_action IN VARCHAR2 DEFAULT 'BOOKING',
41 P_SYSTEM_PARAMETER_REC IN OE_CREDIT_CHECK_UTIL.OE_systems_param_rec_type ,
42 p_user_id IN NUMBER, --parameter added
43 x_cc_result_out OUT NOCOPY VARCHAR2 )
44 IS
45 l_hold_entity_id NUMBER := p_header_id;
46 l_hold_id NUMBER;
47 l_hold_exists VARCHAR2(1);
48 l_hold_result VARCHAR2(30) := NULL;
49 l_msg_count NUMBER := 0;
50 l_msg_data VARCHAR2(2000);
51 l_return_status VARCHAR2(30);
52 l_release_reason VARCHAR2(30);
53 l_cc_result_out VARCHAR2(30) := 'PASS_NONE';
54 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type := OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
55 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type := OE_HOLDS_PVT.G_MISS_Hold_Release_REC;
56
57 BEGIN
58 IF G_debug_flag = 'Y' THEN
59 oe_debug_pub.add('In Release_Order_CC_Hold');
60 END IF;
61 l_return_status := FND_API.G_RET_STS_SUCCESS;
62 IF G_debug_flag = 'Y' THEN
63 oe_debug_pub.add('Check if Holds exist to release ');
64 END IF;
65
66 -- check whether holds exists
67 oe_debug_pub.add('Calling OE_HOLDS_PUB.Check_Holds ');
68 oe_debug_pub.add('p_wf_item :: '||OE_Credit_Engine_GRP.G_cc_hold_item_type);
69 oe_debug_pub.add('p_wf_activity :: '||OE_Credit_Engine_GRP.G_cc_hold_activity_name);
70
71 OE_HOLDS_PUB.Check_Holds ( p_api_version => 1.0 ,
72 p_header_id => p_header_id ,
73 p_hold_id => 1 ,
74 p_wf_item => OE_Credit_Engine_GRP.G_cc_hold_item_type ,
75 p_wf_activity => OE_Credit_Engine_GRP.G_cc_hold_activity_name ,
76 p_entity_code => 'O' ,
77 p_entity_id => p_header_id ,
78 x_result_out => l_hold_result ,
79 x_msg_count => l_msg_count ,
80 x_msg_data => l_msg_data ,
81 x_return_status => l_return_status
82 );
83 IF G_debug_flag = 'Y' THEN
84 oe_debug_pub.add('Out Check_Holds ');
85 oe_debug_pub.add('l_return_status = '|| l_return_status );
86 oe_debug_pub.add('l_hold_result = '|| l_hold_result );
87 END IF;
88 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
89 RAISE FND_API.G_EXC_ERROR;
90 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93 -- releasing the Hold
94 IF l_hold_result = FND_API.G_TRUE THEN
95 l_hold_source_rec.hold_id := 1; -- Credit Checking hold
96 l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
97 l_hold_source_rec.HOLD_ENTITY_ID := p_header_id;
98 --9865613 l_hold_release_rec.release_reason_code := 'AR_APPROVE';
99 l_hold_release_rec.release_reason_code := 'OCM_AUTOMATIC'; --9865613
100 l_hold_release_rec.release_comment := 'Approved by Credit Manager' ;
101 l_hold_release_rec.created_by := p_user_id; -- Manually Released By Credit Manager
102 IF G_debug_flag = 'Y' THEN
103 oe_debug_pub.add('Attempt to Release hold on '|| p_header_id);
104 END IF;
105 IF NVL(p_calling_action, 'BOOKING') <> 'AUTO HOLD' THEN
106 OE_Holds_PUB.Release_Holds ( p_api_version => 1.0 ,
110 x_msg_data => l_msg_data ,
107 p_hold_source_rec => l_hold_source_rec ,
108 p_hold_release_rec => l_hold_release_rec ,
109 x_msg_count => l_msg_count ,
111 x_return_status => l_return_status
112 );
113 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
114 RAISE FND_API.G_EXC_ERROR;
115 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
118 IF G_debug_flag = 'Y' THEN
119 oe_debug_pub.add('Released credit check hold on Header ID:'|| p_header_id);
120 END IF;
121 END IF;
122 l_cc_result_out := 'PASS_REL';
123 END IF; -- calling action check
124 END IF; -- hold exist
125 x_cc_result_out := l_cc_result_out;
126 IF G_debug_flag = 'Y' THEN
127 oe_debug_pub.add('Out Release_Order_CC_Hold');
128 END IF;
129 EXCEPTION
130 WHEN OTHERS THEN
131 oe_debug_pub.add('EXCEPTION :: Release_Order_CC_Hold' );
132 RAISE;
133 END Release_Order_CC_Hold;
134
135 --------------------------------------------------------------------------------------------
136 -- This procedure is added for bug 8617023.
137 -- After this change when Credit Manager will submitt the recomendation
138 -- to release Credit Check Faliure Hold Release_Line_CC_Hold procedure will be used
139 -- instead of OE_CREDIT_CHECK_ORDER_PVT.Release_Line_CC_Hold.
140 -- This procedure has an extra parameter of p_user_id to denote the User who actually
141 -- released the hold. The hold release will be treated as Manual release instead of
142 -- automatic release.
143 --------------------------------------------------------------------------------------------
144
145 PROCEDURE Release_Line_CC_Hold
146 ( p_header_id IN NUMBER
147 , p_order_number IN NUMBER
148 , p_line_id IN NUMBER
149 , p_line_number IN NUMBER
150 , p_calling_action IN VARCHAR2 DEFAULT NULL
151 , p_credit_hold_level IN VARCHAR2
152 , p_user_id IN NUMBER
153 , x_cc_result_out OUT NOCOPY VARCHAR2
154 )
155 IS
156 l_hold_entity_id NUMBER := p_header_id;
157 l_hold_id NUMBER;
158 l_hold_exists VARCHAR2(1);
159 l_hold_result VARCHAR2(30) := NULL;
160 l_msg_count NUMBER := 0;
161 l_msg_data VARCHAR2(2000);
162 l_return_status VARCHAR2(30);
163 l_release_reason VARCHAR2(30);
164 l_cc_result_out VARCHAR2(30) := 'PASS_NONE';
165
166 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type :=
167 OE_HOLDS_PVT.G_MISS_Hold_Source_REC;
168 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type :=
169 OE_HOLDS_PVT.G_MISS_Hold_Release_REC;
170 BEGIN
171 IF G_debug_flag = 'Y'
172 THEN
173 oe_debug_pub.add(' In Release_Line_CC_Hold');
174 oe_debug_pub.add('Processing line ID = '|| p_line_id );
175 END IF;
176
177 l_return_status := FND_API.G_RET_STS_SUCCESS;
178
179
180 --checking whether HOLD exists
181 IF G_debug_flag = 'Y'
182 THEN
183 OE_DEBUG_PUB.ADD('Check for holds for Header/Line ID : '|| p_header_id || '/' || p_line_id);
184 END IF;
185
186 OE_HOLDS_PUB.Check_Holds
187 ( p_api_version => 1.0
188 , p_header_id => p_header_id
189 , p_line_id => p_line_id
190 , p_hold_id => 1
191 , p_wf_item => OE_Credit_Engine_GRP.G_cc_hold_item_type
192 , p_wf_activity => OE_Credit_Engine_GRP.G_cc_hold_activity_name
193 , p_entity_code => 'O'
194 , p_entity_id => p_header_id
195 , x_result_out => l_hold_result
196 , x_msg_count => l_msg_count
197 , x_msg_data => l_msg_data
198 , x_return_status => l_return_status
199 );
200
201 IF G_debug_flag = 'Y'
202 THEN
203 OE_DEBUG_PUB.ADD('Out Check_Holds');
204 OE_DEBUG_PUB.ADD('l_return_status :'||l_return_status);
205 OE_DEBUG_PUB.ADD('l_hold_result :'||l_hold_result);
206 END IF;
207
208 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
209 RAISE FND_API.G_EXC_ERROR;
210 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 END IF;
213 --end check hold exits
214
215 IF l_hold_result = FND_API.G_TRUE THEN
216 IF NVL(p_calling_action,'BOOKING') <> 'AUTO HOLD' THEN
217 l_hold_source_rec.hold_id := 1; -- Credit Checking hold
218 l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
219 l_hold_source_rec.HOLD_ENTITY_ID := p_header_id;
220 l_hold_source_rec.line_id := p_line_id;
221
222 --9865613 l_hold_release_rec.release_reason_code := 'AR_APPROVE';
223 l_hold_release_rec.release_reason_code := 'OCM_AUTOMATIC'; --9865613
224 l_hold_release_rec.release_comment := 'Approved by Credit Manager' ;
225 l_hold_release_rec.created_by := p_user_id; -- Manually Released By Credit Manager
226
227 OE_Holds_PUB.Release_Holds
228 ( p_api_version => 1.0
232 , x_msg_data => l_msg_data
229 , p_hold_source_rec => l_hold_source_rec
230 , p_hold_release_rec => l_hold_release_rec
231 , x_msg_count => l_msg_count
233 , x_return_status => l_return_status
234 );
235
236 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
237 IF NVL(G_hdr_hold_released,'N') = 'N'
238 THEN
239 l_cc_result_out := 'HDR_HOLD' ;
240 ELSE
241 RAISE FND_API.G_EXC_ERROR;
242 END IF;
243 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
244 IF NVL(G_hdr_hold_released,'N') = 'N'
245 THEN
246 l_cc_result_out := 'HDR_HOLD' ;
247 ELSE
248 RAISE FND_API.G_EXC_ERROR;
249 END IF;
250 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
251 l_cc_result_out := 'PASS_REL';
252 END IF;
253 END IF; -- check calling action
254 ELSE
255 IF G_debug_flag = 'Y'
256 THEN
257 OE_DEBUG_PUB.ADD(' No Hold exist to be Released ');
258 END IF;
259 END IF; -- Holds Exist IF
260
261 x_cc_result_out := l_cc_result_out;
262
263 IF G_debug_flag = 'Y'
264 THEN
265 OE_DEBUG_PUB.ADD('x_cc_result_out = '|| x_cc_result_out );
266 OE_DEBUG_PUB.ADD('Out Release_Line_CC_Hold');
267 END IF;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 OE_DEBUG_PUB.ADD('EXCEPTION :: Release_LINE_CC_Hold' );
272 RAISE;
273 RAISE;
274
275 END Release_Line_CC_Hold;
276 ----------------------------------------------------------------
277
278 ----------------------------------------------------------------
279 --This is rule function, that is subscribed to the Oracle Workflow
280 -- Business Event CreditRequest.Recommendation.implement
281 --to implement recomendations of the AR CRedit Management Review
282 ----------------------------------------------------------------
283 FUNCTION Rule_Credit_Recco_Impl
284 ( p_subscription_guid In RAW
285 , p_event IN OUT NOCOPY WF_EVENT_T
286 )
287 RETURN VARCHAR2
288 IS
289 l_key VARCHAR2(240);
290 l_credit_check_rule_rec OE_CREDIT_CHECK_UTIL.OE_credit_rules_rec_type;
291 l_header_rec OE_ORDER_PUB.Header_Rec_Type;
292 l_credit_check_rule_id NUMBER;
293 l_limit_currency AR_CMGT_CREDIT_REQUESTS.limit_currency%TYPE;
294 l_trx_amount NUMBER;
295 l_trx_currency AR_CMGT_CREDIT_REQUESTS.trx_currency%TYPE;
296 l_source_name VARCHAR2(50); --9509804
297 l_source_column1 NUMBER;
298 l_source_column2 NUMBER;
299 l_source_column3 VARCHAR2(30);
300 l_CASE_FOLDER_ID NUMBER; --bug#8617023
301 l_released_by NUMBER; --bug#8617023
302 l_party_id NUMBER;
303 l_cust_account_id NUMBER;
304 l_site_use_id NUMBER;
305
306 l_new_amount NUMBER;
307
308 l_cc_result_out VARCHAR2(30);
309 l_conversion_status OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE ;
310 l_msg_count NUMBER;
311 l_msg_data VARCHAR2(2000);
312 l_return_status VARCHAR2(30);
313 l_credit_request_id NUMBER;
314 l_user_id NUMBER;
315 l_resp_id NUMBER;
316 l_resp_appl_id NUMBER;
317 l_security_group_id NUMBER;
318 l_count NUMBER;
319 l_source_org_id NUMBER; -- bug 7120635
320
321 -- note: This cursor does not take payment type into account for 11.5.10
322 CURSOR billto_lines_csr(p_site_use_id NUMBER,p_header_id NUMBER)
323 IS
324 SELECT
325 l.line_id
326 , l.line_number
327 FROM
328 oe_order_lines_all l
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 l.open_flag = 'Y'
333 AND l.booked_flag = 'Y'
334 AND NVL(l.invoiced_quantity,0) = 0
335 AND NVL(l.shipped_quantity,0) = 0
336 AND l.line_category_code = 'ORDER'
337 AND l.payment_term_id = t.term_id
338 AND t.credit_check_flag = 'Y'
339 ORDER BY l.line_id;
340
341 -- 4299254
342 CURSOR lines_csr(p_header_id NUMBER)
343 IS
344 SELECT
345 l.line_id
346 , l.line_number
347 FROM
348 oe_order_lines_all l
349 , ra_terms_b t
350 WHERE l.header_id = p_header_id
351 AND l.open_flag = 'Y'
352 AND l.booked_flag = 'Y'
353 AND NVL(l.invoiced_quantity,0) = 0
354 AND NVL(l.shipped_quantity,0) = 0
355 AND l.line_category_code = 'ORDER'
356 AND l.payment_term_id = t.term_id
357 AND t.credit_check_flag = 'Y'
358 ORDER BY l.line_id;
359
360 BEGIN
361
362 --9509804 start
363 l_source_name := p_event.GetValueForParameter('SOURCE_NAME');
364
365 IF NVL(l_source_name, 'XX') <> 'OM'
366 THEN
367 /* - This entire routine is designed to handle OM credit
368 requests. It makes the assumption that some source columns are
369 numeric because OM passes them that way. This block just exits
370 if the source is not OM */
371 RETURN 'SUCCESS';
372 END IF;
373 --9509804 end
374
375 l_key := p_event.GetEventKey();
376 l_credit_request_id := p_event.GetValueForParameter('CREDIT_REQUEST_ID');
377 l_source_column1 := p_event.GetValueForParameter('SOURCE_COLUMN1');
378 l_source_column2 := p_event.GetValueForParameter('SOURCE_COLUMN2');
379 l_source_column3 := p_event.GetValueForParameter('SOURCE_COLUMN3');
380 l_CASE_FOLDER_ID := p_event.GetValueForParameter('CASE_FOLDER_ID'); --bug#8617023
384 --bug# 8617023 start
381
382 --check that the recomendation exist
383
385 --changed the logic of the select statement
386 --instead of count we will now fetch the
387 --id of the user who actually released the
388 --hold
389 l_count := 1;
390 BEGIN
391 SELECT CREATED_BY
392 INTO l_released_by
393 FROM ar_cmgt_cf_recommends
394 WHERE credit_request_id = l_credit_request_id
395 AND credit_recommendation = 'REMOVE_ORDER_HOLD'
396 AND status = 'I'
397 AND CASE_FOLDER_ID = l_CASE_FOLDER_ID;
398 EXCEPTION
399 WHEN OTHERS THEN
400 l_count := 0;
401 END;
402 /*
403 SELECT COUNT(1)
404 INTO l_count
405 FROM ar_cmgt_cf_recommends
406 WHERE credit_request_id = l_credit_request_id
407 AND credit_recommendation = 'REMOVE_ORDER_HOLD'
408 AND status = 'I';*/
409 --bug# 8617023 end
410
411 -- proceed if recommendation is to release order
412 IF l_count > 0 THEN
413 -- Get the credit management information required to release the hold
414 BEGIN
415 SELECT
416 limit_currency
417 , credit_check_rule_id
418 , trx_currency
419 , trx_amount
420 , party_id
421 , cust_account_id
422 , site_use_id --4299254
423 , source_user_id
424 , source_resp_id
425 , source_resp_appln_id
426 , source_security_group_id
427 , source_org_id -- Bug 7120635
428 INTO l_limit_currency
429 , l_credit_check_rule_id
430 , l_trx_currency
431 , l_trx_amount
432 , l_party_id
433 , l_cust_account_id
434 , l_site_use_id
435 , l_user_id
436 , l_resp_id
437 , l_resp_appl_id
438 , l_security_group_id
439 , l_source_org_id -- Bug 7120635
440 FROM ar_cmgt_credit_requests
441 WHERE credit_request_id = l_credit_request_id;
442 EXCEPTION
443 WHEN NO_DATA_FOUND THEN
444 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
445 END;
446
447 -- set security context
448 FND_GLOBAL.apps_initialize
449 ( l_user_id
450 , l_resp_id
451 , l_resp_appl_id
452 , l_security_group_id
453 );
454
455 -- for 7120635
456 MO_GLOBAL.set_policy_context('S', l_source_org_id);
457 --MO_GLOBAL.INIT('ONT');
458
459 --15884319 start
460 SELECT item_type,
461 activity_name
462 INTO OE_Credit_Engine_GRP.G_cc_hold_item_type,
463 OE_Credit_Engine_GRP.G_cc_hold_activity_name
464 FROM oe_hold_definitions
465 WHERE hold_id = 1;
466 --15884319 end
467
468 --Populate credit check rule record
469 OE_CREDIT_CHECK_UTIL.GET_credit_check_rule
470 ( p_header_id => l_source_column1
471 , p_credit_check_rule_id => l_credit_check_rule_id
472 , x_credit_check_rules_rec => l_credit_check_rule_rec
473 );
474
475 -- for order level hold we will call
476 IF l_source_column3 = 'ORDER' THEN
477 -- Get new transactional amount
478 OE_CREDIT_CHECK_UTIL.GET_transaction_amount
479 ( p_header_id => l_source_column1
480 , p_transaction_curr_code => l_trx_currency
481 , p_credit_check_rule_rec => l_credit_check_rule_rec
482 , p_system_parameter_rec => NULL
483 , p_customer_id => l_cust_account_id
484 , p_site_use_id => l_site_use_id
485 , p_limit_curr_code => l_limit_currency
486 , x_amount => l_new_amount
487 , x_conversion_status => l_conversion_status
488 , x_return_status => l_return_status
489 );
490
491 IF l_return_status = FND_API.G_RET_STS_ERROR
492 THEN
493 RAISE FND_API.G_EXC_ERROR;
494 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
495 THEN
496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
497 END IF;
498
499 --check if the order_amount has been changed
500 --if amount has not been changed, release the hold
501 --should raise message indicating that amount has changed and hold is not release.
502
503 IF l_new_amount = l_trx_amount THEN
504 --commented for bug#8617023
505 /*OE_CREDIT_CHECK_ORDER_PVT.Release_Order_CC_Hold
506 ( p_header_id => l_source_column1
507 , p_order_number => l_source_column2
508 , p_calling_action => NULL
509 , p_system_parameter_rec=> NULL
510 , x_cc_result_out => l_cc_result_out
511 ); */
512
513 --added for bug#8617023
514 Release_Order_CC_Hold
515 ( p_header_id => l_source_column1
516 , p_order_number => l_source_column2
517 , p_calling_action => NULL
518 , p_system_parameter_rec=> NULL
519 , p_user_id => l_released_by
520 , x_cc_result_out => l_cc_result_out
521 );
522
523 END IF;
524 ELSE --for line level hold
525 --4299254: If the order goes on hold using site level limits, release hold
526 --for the lines corresponding to that bill to site, otherwise release the hold
527 --for all the lines.
528 -- Credit Management inserts site_use_id as -99,if OM pass it as NULL.
529 IF nvl(l_site_use_id,-99) > 0 THEN
530 --Get new transactional amount for this bill to site..
531 --Bug 4377933: If customer_id is passed as NULL,then this API returns the transaction
532 --amount for site..
533 OE_CREDIT_CHECK_UTIL.GET_transaction_amount
537 , p_system_parameter_rec => NULL
534 ( p_header_id => l_source_column1
535 , p_transaction_curr_code => l_trx_currency
536 , p_credit_check_rule_rec => l_credit_check_rule_rec
538 , p_customer_id => NULL
539 , p_site_use_id => l_site_use_id
540 , p_limit_curr_code => l_limit_currency
541 , x_amount => l_new_amount
542 , x_conversion_status => l_conversion_status
543 , x_return_status => l_return_status
544 );
545 IF l_return_status = FND_API.G_RET_STS_ERROR
546 THEN
547 RAISE FND_API.G_EXC_ERROR;
548 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
549 THEN
550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
551 END IF;
552 --Check if amount per bill-to site has been changed
553 --if amount has not been changed, release holds for bill_to site
554 IF l_new_amount = l_trx_amount THEN
555 --OPEN the cursor for sites
556 FOR c_line IN billto_lines_csr(p_site_use_id =>l_site_use_id
557 ,p_header_id =>l_source_column1)
558 LOOP
559 --commented for bug#8617023
560 /* OE_CREDIT_CHECK_LINES_PVT.Release_Line_CC_Hold
561 ( p_header_id => l_source_column1
562 , p_order_number => l_source_column2
563 , p_line_id => c_line.line_id
564 , p_line_number => c_line.line_number
565 , p_calling_action => NULL
566 , p_credit_hold_level => 'LINE'
567 , x_cc_result_out => l_cc_result_out
568 ); */
569 --added for bug#8617023
570 Release_Line_CC_Hold
571 ( p_header_id => l_source_column1
572 , p_order_number => l_source_column2
573 , p_line_id => c_line.line_id
574 , p_line_number => c_line.line_number
575 , p_calling_action => NULL
576 , p_credit_hold_level => 'LINE'
577 , p_user_id => l_released_by
578 , x_cc_result_out => l_cc_result_out
579 );
580
581 END LOOP;
582 END IF;
583 --ELSE --If the order goes on hold using customer credit limits --13987649
584 ELSIF nvl(l_cust_account_id,-99) > 0 THEN --13987649
585 OE_CREDIT_CHECK_UTIL.GET_transaction_amount
586 ( p_header_id => l_source_column1
587 , p_transaction_curr_code => l_trx_currency
588 , p_credit_check_rule_rec => l_credit_check_rule_rec
589 , p_system_parameter_rec => NULL
590 , p_customer_id => l_cust_account_id
591 , p_site_use_id => l_site_use_id
592 , p_limit_curr_code => l_limit_currency
593 , x_amount => l_new_amount
594 , x_conversion_status => l_conversion_status
595 , x_return_status => l_return_status
596 );
597 IF l_return_status = FND_API.G_RET_STS_ERROR
598 THEN
599 RAISE FND_API.G_EXC_ERROR;
600 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
601 THEN
602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
603 END IF;
604 IF l_new_amount = l_trx_amount THEN
605 --OPEN the cursor for all lines
606 FOR c_line IN lines_csr(p_header_id =>l_source_column1)
607 LOOP
608 --commented for bug#8617023
609 /*OE_CREDIT_CHECK_LINES_PVT.Release_Line_CC_Hold
610 ( p_header_id => l_source_column1
611 , p_order_number => l_source_column2
612 , p_line_id => c_line.line_id
613 , p_line_number => c_line.line_number
614 , p_calling_action => NULL
615 , p_credit_hold_level => 'LINE'
616 , x_cc_result_out => l_cc_result_out
617 );*/
618
619 --added for bug#8617023
620 Release_Line_CC_Hold
621 ( p_header_id => l_source_column1
622 , p_order_number => l_source_column2
623 , p_line_id => c_line.line_id
624 , p_line_number => c_line.line_number
625 , p_calling_action => NULL
626 , p_credit_hold_level => 'LINE'
627 , p_user_id => l_released_by
628 , x_cc_result_out => l_cc_result_out
629 );
630
631 END LOOP;
632 END IF;
633 --13987649 Start
634 ELSIF nvl(l_party_id,-99) > 0 THEN
635 oe_debug_pub.add('OEXCRRQB Line Level, Party ID= '||l_party_id||' -Orig Chk Level- '||l_credit_check_rule_rec.credit_check_level_code);
636 l_credit_check_rule_rec.credit_check_level_code := 'ORDER';
637 OE_CREDIT_CHECK_UTIL.GET_transaction_amount
638 ( p_header_id => l_source_column1
639 , p_transaction_curr_code => l_trx_currency
640 , p_credit_check_rule_rec => l_credit_check_rule_rec
641 , p_system_parameter_rec => NULL
642 , p_customer_id => l_cust_account_id
643 , p_site_use_id => l_site_use_id
644 , p_limit_curr_code => l_limit_currency
645 , x_amount => l_new_amount
646 , x_conversion_status => l_conversion_status
647 , x_return_status => l_return_status
648 );
649 IF l_return_status = FND_API.G_RET_STS_ERROR
650 THEN
651 RAISE FND_API.G_EXC_ERROR;
652 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
653 THEN
654 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
655 END IF;
656 IF l_new_amount = l_trx_amount THEN
657 --OPEN the cursor for all lines
658 FOR c_line IN lines_csr(p_header_id =>l_source_column1)
659 LOOP
660
661
662 --added for bug#8617023
663 Release_Line_CC_Hold
664 ( p_header_id => l_source_column1
665 , p_order_number => l_source_column2
666 , p_line_id => c_line.line_id
667 , p_line_number => c_line.line_number
668 , p_calling_action => NULL
669 , p_credit_hold_level => 'LINE'
670 , p_user_id => l_released_by
671 , x_cc_result_out => l_cc_result_out
672 );
673
674 END LOOP;
675 END IF;
676 --13987649 End
677 END IF; -- end of check if site_use_id is passed.
678 END IF; --end of check if hold is line level or order level
679 END IF; --end of check if recommendation is to release hold
680
681 RETURN 'SUCCESS';
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
686 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
687 FND_MSG_PUB.ADD;
688 WF_CORE.CONTEXT('HZ_CREDIT_REQUEST_PVT',
689 'Rule_Credit_Recco_Impl',
690 p_event.getEventName(),
691 p_subscription_guid);
692 WF_EVENT.setErrorInfo(p_event, 'ERROR');
693
694 RETURN 'ERROR';
695 END Rule_Credit_Recco_Impl;
696
697
698
699 END HZ_CREDIT_REQUEST_PVT ;