[Home] [Help]
PACKAGE BODY: APPS.OE_PREPAYMENT_PVT
Source
1 PACKAGE BODY OE_PrePayment_PVT AS
2 /* $Header: OEXVPPYB.pls 120.44.12020000.5 2013/01/30 14:04:26 kadiraju ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_PrePayment_PVT';
5
6 /*--------------------------------------------------------------------
7 Function Total_Invoiced_Amount
8 Returns the invoiced amount for the order. Added for Bug 4938105
9 ---------------------------------------------------------------------*/
10 FUNCTION Total_Invoiced_Amount
11 (
12 p_header_id IN NUMBER
13 ) RETURN NUMBER
14 IS
15 l_order_total NUMBER;
16 l_tax_total NUMBER;
17 l_charges NUMBER;
18 l_invoice_total NUMBER;
19 l_commitment_total NUMBER;
20 l_chgs_w_line_id NUMBER := 0;
21 l_chgs_wo_line_id NUMBER := 0;
22 --
23 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
24 --
25 BEGIN
26
27 IF OE_ORDER_UTIL.G_Precision IS NULL THEN
28 OE_ORDER_UTIL.G_Precision:=2;
29 END IF;
30
31 -- Select the Tax Total and Outbound Extended Price
32 SELECT
33 SUM(ROUND(nvl(ool.tax_value,0), OE_ORDER_UTIL.G_Precision))
34 , SUM(ROUND(nvl(ool.Ordered_Quantity,0)
35 *(ool.unit_selling_price), OE_ORDER_UTIL.G_Precision))
36 INTO
37 l_tax_total
38 , l_order_total
39 FROM oe_order_lines_all ool
40 WHERE ool.header_id = p_header_id
41 AND ool.open_flag = 'N'
42 AND ool.cancelled_flag = 'N'
43 AND ool.line_category_code <> 'RETURN'
44 AND NOT EXISTS
45 (SELECT 'Non Invoiceable Item Line'
46 FROM mtl_system_items mti
47 WHERE mti.inventory_item_id = ool.inventory_item_id
48 AND mti.organization_id = nvl(ool.ship_from_org_id,
49 oe_sys_parameters.value('MASTER_ORGANIZATION_ID'))
50 AND (mti.invoiceable_item_flag = 'N'
51 OR mti.invoice_enabled_flag = 'N'));
52
53 IF OE_Commitment_Pvt.Do_Commitment_Sequencing THEN
54 -- Select the committment applied amount if Commitment Sequencing "On"
55 SELECT SUM(ROUND(nvl(op.commitment_applied_amount,0), OE_ORDER_UTIL.G_Precision))
56 INTO l_commitment_total
57 FROM oe_payments op
58 WHERE op.header_id = p_header_id
59 AND NOT EXISTS
60 (SELECT 'Non Invoiceable Item Line'
61 FROM mtl_system_items mti, oe_order_lines_all ool
62 WHERE ool.line_id = op.line_id
63 AND mti.inventory_item_id = ool.inventory_item_id
64 AND mti.organization_id = nvl(ool.ship_from_org_id,
65 oe_sys_parameters.value('MASTER_ORGANIZATION_ID'))
66 AND (mti.invoiceable_item_flag = 'N'
67 OR mti.invoice_enabled_flag = 'N'));
68 ELSE
69 -- Select the Outbound Extended Price for lines that have committment
70 SELECT SUM(ROUND(nvl(ool.Ordered_Quantity,0) *(ool.unit_selling_price), OE_ORDER_UTIL.G_Precision))
71 INTO l_commitment_total
72 FROM oe_order_lines_all ool
73 WHERE ool.header_id = p_header_id
74 AND ool.commitment_id is not null
75 AND ool.open_flag = 'N'
76 AND ool.cancelled_flag = 'N'
77 AND ool.line_category_code <> 'RETURN'
78 AND NOT EXISTS
79 (SELECT 'Non Invoiceable Item Line'
80 FROM mtl_system_items mti
81 WHERE mti.inventory_item_id = ool.inventory_item_id
82 AND mti.organization_id = nvl(ool.ship_from_org_id,
83 oe_sys_parameters.value('MASTER_ORGANIZATION_ID'))
84 AND (mti.invoiceable_item_flag = 'N'
85 OR mti.invoice_enabled_flag = 'N'));
86 END IF;
87
88 -- Select the Outbound Charges Total
89 SELECT SUM(
90 ROUND(
91 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',-P.OPERAND,P.OPERAND), OE_ORDER_UTIL.G_Precision
92 )
93 )
94 INTO l_chgs_wo_line_id
95 FROM OE_PRICE_ADJUSTMENTS P
96 WHERE P.HEADER_ID = p_header_id
97 AND P.LINE_ID IS NULL
98 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
99 AND P.APPLIED_FLAG = 'Y'
100 AND NVL(P.INVOICED_FLAG, 'N') = 'N';
101
102 SELECT SUM(
103 ROUND(
104 DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
105 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
106 -P.OPERAND,
107 (-L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT)),
108 DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
109 P.OPERAND,
110 (L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT))
111 )
112 ,OE_ORDER_UTIL.G_Precision
113 )
114 )
115 INTO l_chgs_w_line_id
116 FROM OE_PRICE_ADJUSTMENTS P,
117 OE_ORDER_LINES_ALL L
118 WHERE P.HEADER_ID = p_header_id
119 AND P.LINE_ID = L.LINE_ID
120 AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
121 AND P.APPLIED_FLAG = 'Y'
122 AND L.header_id = p_header_id
123 AND L.open_flag = 'N'
124 AND L.cancelled_flag = 'N'
125 AND L.line_category_code <> 'RETURN'
126 AND NOT EXISTS
127 (SELECT 'Non Invoiceable Item Line'
128 FROM MTL_SYSTEM_ITEMS MTI
129 WHERE MTI.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
130 AND MTI.ORGANIZATION_ID = NVL(L.SHIP_FROM_ORG_ID,
131 oe_sys_parameters.value('MASTER_ORGANIZATION_ID'))
132 AND (MTI.INVOICEABLE_ITEM_FLAG = 'N'
133 OR MTI.INVOICE_ENABLED_FLAG = 'N'));
134
135 l_charges := nvl(l_chgs_wo_line_id,0) + nvl(l_chgs_w_line_id,0);
136
137 l_invoice_total := nvl(l_order_total, 0) + nvl(l_tax_total, 0)
138 + nvl(l_charges, 0) - nvl(l_commitment_total,0);
139
140 IF l_debug_level > 0 THEN
141 oe_debug_pub.add( 'OEXVPPYB: CALCULATING THE TOTAL INVOICED AMOUNT FOR THIS ORDER ' , 1 ) ;
142 oe_debug_pub.add( 'OEXVPPYB: TOTAL INVOICED AMOUNT ORDER : '||TO_CHAR ( L_ORDER_TOTAL ) , 1 ) ;
143 oe_debug_pub.add( 'OEXVPPYB: TOTAL INVOICED AMOUNT TAX : '||TO_CHAR ( L_TAX_TOTAL ) , 1 ) ;
144 oe_debug_pub.add( 'OEXVPPYB: TOTAL INVOICED AMOUNT COMMITMENTS : '||TO_CHAR ( L_COMMITMENT_TOTAL ) , 1 ) ;
145 oe_debug_pub.add( 'OEXVPPYB: TOTAL INVOICED AMOUNT OTHER CHARGES : '||TO_CHAR ( L_CHARGES ) , 1 ) ;
146 oe_debug_pub.add( 'OEXVPPYB: TOTAL_INVOICED_AMOUNT : '||TO_CHAR ( l_invoice_total ) , 1 ) ;
147 END IF;
148 RETURN (l_invoice_total);
149
150 EXCEPTION
151 WHEN OTHERS THEN
152 IF l_debug_level > 0 THEN
153 oe_debug_pub.add( 'FROM Total_Invoiced_Amount OTHERS' ) ;
154 END IF;
155 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
156
157 END Total_Invoiced_Amount;
158
159 /*--------------------------------------------------------------------------
160 Function Calculate_Pending_Amount
161 Returns the pending amount to be processed
162 ---------------------------------------------------------------------*/
163
164 FUNCTION Calculate_Pending_Amount
165 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type)
166 RETURN NUMBER
167 IS
168 l_prepaid_amount NUMBER := 0;
169 l_pending_amount NUMBER := 0;
170 l_threshold NUMBER := 0;
171 l_outbound_total NUMBER := 0;
172 l_balance_on_prepaid_amount NUMBER := 0; --Bug 4938105
173
174 --
175 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
176 --
177 BEGIN
178
179 IF l_debug_level > 0 THEN
180 oe_debug_pub.add( 'OEXVPPYB: IN CALCULATE PENDING AMOUNT' , 1 ) ;
181 END IF;
182
183 -- Fetch the Order Total Amount
184 IF l_debug_level > 0 THEN
185 oe_debug_pub.add( 'OEXVPPYB: FETCH OUTBOUND LINES TOTAL' , 3 ) ;
186 END IF;
187
188 l_outbound_total := OE_OE_TOTALS_SUMMARY.Outbound_Order_Total(p_header_rec.header_id);
189
190 IF l_debug_level > 0 THEN
191 oe_debug_pub.add( 'OEXVPPYB: TOTAL VALUE OF OUTBOUND LINES : '|| L_OUTBOUND_TOTAL , 3 ) ;
192 END IF;
193
194 BEGIN
195 SELECT nvl(prepaid_amount, 0)
196 INTO l_prepaid_amount
197 FROM oe_payments
198 WHERE header_id= p_header_rec.header_id
199 AND payment_type_code = 'CREDIT_CARD';
200
201 EXCEPTION WHEN NO_DATA_FOUND THEN
202 null;
203 END;
204
205 IF l_debug_level > 0 THEN
206 oe_debug_pub.add( 'OEXVPPYB: PREPAID_AMOUNT FOR THE ORDER IS: '||L_PREPAID_AMOUNT , 1 ) ;
207 END IF;
208
209 --Start of Bug 4938105
210 l_balance_on_prepaid_amount := l_prepaid_amount - Total_Invoiced_Amount(p_header_rec.header_id);
211
212 IF l_balance_on_prepaid_amount < 0 THEN
213 l_balance_on_prepaid_amount := 0;
214 END IF;
215
216 IF l_debug_level > 0 THEN
217 oe_debug_pub.add( 'OEXVPPYB: BALANCE ON PREPAID_AMOUNT FOR THE ORDER IS: '||l_balance_on_prepaid_amount , 1 ) ;
218 END IF;
219
220 l_pending_amount := l_outbound_total - l_balance_on_prepaid_amount ;
221 --End of Bug 4938105
222
223 RETURN l_pending_amount;
224
225 END Calculate_Pending_Amount;
226
227 /*--------------------------------------------------------------------------
228 Procedure Create_Receipt
229 This procedure calls AR API to create a new receipt for the amount specified.
230 ----------------------------------------------------------------------------*/
231 PROCEDURE Create_Receipt
232 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
233 , p_amount IN NUMBER
234 , p_receipt_method_id IN NUMBER
235 , p_bank_acct_id IN NUMBER
236 , p_bank_acct_uses_id IN NUMBER
237 , p_trxn_extension_id IN NUMBER --R12 CC Encryption
238 , p_payment_set_id IN OUT NOCOPY NUMBER
239 , p_receipt_number IN OUT NOCOPY VARCHAR2 -- bug 4724845
240 , p_payment_number IN OE_PAYMENTS.PAYMENT_NUMBER%TYPE DEFAULT NULL --7559372
241 , x_payment_response_error_code OUT NOCOPY VARCHAR2
242 , p_approval_code IN OUT NOCOPY VARCHAR2
243 , x_msg_count OUT NOCOPY NUMBER
244 , x_msg_data OUT NOCOPY VARCHAR2
245 , x_return_status OUT NOCOPY VARCHAR2
246 , x_result_out OUT NOCOPY VARCHAR2
247 )
248 IS
249 l_bank_acct_id NUMBER ;
250 l_bank_acct_uses_id NUMBER ;
251 l_application_ref_id NUMBER := p_header_rec.header_id;
252 l_application_ref_num NUMBER := p_header_rec.order_number;
253 l_msg_count NUMBER := 0 ;
254 l_msg_data VARCHAR2(2000) := NULL ;
255 l_return_status VARCHAR2(30) := NULL ;
256 l_result_out VARCHAR2(30) := NULL ;
257 l_hold_exists VARCHAR2(1);
258 l_receipt_method_id NUMBER;
259 p_customer_id NUMBER;
260 l_site_use_id NUMBER;
261 l_payment_set_id NUMBER;
262 l_application_ref_type VARCHAR2(30);
263 l_cr_id NUMBER;
264 l_receivable_application_id NUMBER;
265 l_call_payment_processor VARCHAR2(30);
266 l_remittance_bank_account_id NUMBER;
267 l_called_from VARCHAR2(30);
268 l_secondary_application_ref_id NUMBER;
269 l_receipt_number VARCHAR2(30);
270 p_bank_account_id NUMBER;
271 l_payment_server_order_num VARCHAR2(80);
272 l_trxn_id NUMBER;
273 l_exchange_rate_type VARCHAR2(30);
274 l_exchange_rate NUMBER;
275 l_exchange_rate_date DATE;
276 l_set_of_books_rec OE_Order_Cache.Set_Of_Books_Rec_Type;
277 l_msg_text VARCHAR2(2000);
278
279 --
280 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
281 --
282
283 l_hdr_inv_to_cust_id NUMBER; --bug#8854662
284
285 BEGIN
286
287 IF l_debug_level > 0 THEN
288 oe_debug_pub.add( 'ENTERING OE_PREPAYMENT_PV.CREATE_RECEIPT.' , 1 ) ;
289 END IF;
290 x_result_out := 'PASS' ;
291 x_return_status := FND_API.G_RET_STS_SUCCESS;
292
293 IF l_debug_level > 0 THEN
294 oe_debug_pub.add( 'OEXVPPYB: CALLING AR CREATE RECEIPT API ' , 3 ) ;
295 END IF;
296 l_payment_set_id := p_payment_set_id;
297
298 -- To get the remittance bank account id.
299 BEGIN
300 SELECT ba.bank_account_id
301 INTO l_remittance_bank_account_id
302 FROM ar_receipt_methods rm,
303 ap_bank_accounts ba,
304 ar_receipt_method_accounts rma ,
305 ar_receipt_classes rc
306 WHERE rm.receipt_method_id = p_receipt_method_id
307 and rm.receipt_method_id = rma.receipt_method_id
308 and rc.receipt_class_id = rm.receipt_class_id
309 and rc.creation_method_code = 'AUTOMATIC'
310 and rma.remit_bank_acct_use_id = ba.bank_account_id
311 and ba.account_type = 'INTERNAL'
312 and ba.currency_code = decode(ba.receipt_multi_currency_flag, 'Y'
313 ,ba.currency_code
314 ,p_header_rec.transactional_curr_code)
315 and rma.primary_flag = 'Y';
316
317 EXCEPTION WHEN NO_DATA_FOUND THEN
318 null;
319 END;
320
321 /* commented out the following code for R12 CC encryption project
322 -- to get the p_payment_server_order_num (i.e. tangible_id) for
323 -- the credit_card_approval_code
324 IF p_approval_code IS NOT NULL THEN
325
326 IF l_debug_level > 0 THEN
327 oe_debug_pub.add( 'OEXVPPYB: FETCHING THE TANGIBLE ID.' , 3 ) ;
328 END IF;
329 OE_Verify_Payment_PUB.Fetch_Current_Auth
330 ( p_header_rec => p_header_rec
331 , p_trxn_id => l_trxn_id
332 , p_tangible_id => l_payment_server_order_num
333 );
334 END IF;
335 */
336
337 l_set_of_books_rec := OE_Order_Cache.Load_Set_Of_Books;
338 IF p_header_rec.transactional_curr_code
339 = l_set_of_books_rec.currency_code THEN
340 l_exchange_rate_type := null;
341 l_exchange_rate := null;
342 l_exchange_rate_date := null;
343 ELSE
344 l_exchange_rate_type := p_header_rec.conversion_type_code;
345 l_exchange_rate := p_header_rec.conversion_rate;
346 l_exchange_rate_date := p_header_rec.conversion_rate_date;
347
348 END IF;
349
350
351 -- seeded lookup_code for AR lookup_type 'AR_PREPAYMENT_TYPE' is 'OM'.
352 l_application_ref_type := 'OM';
353 l_application_ref_num := p_header_rec.order_number;
354 l_application_ref_id := p_header_rec.header_id;
355
356 p_bank_account_id := null;
357
358 IF p_trxn_extension_id IS NOT NULL THEN
359 l_call_payment_processor := FND_API.G_TRUE;
360 ELSE
361 l_call_payment_processor := FND_API.G_FALSE;
362 END IF;
363
364 IF l_debug_level > 0 THEN
365 oe_debug_pub.add( 'OEXVPPYB: before calling AR Create_Prepayment: '||p_header_rec.header_id , 3 ) ;
366 oe_debug_pub.add( 'OEXVPPYB: receipt_method_id is: '||p_receipt_method_id, 3 ) ;
367 oe_debug_pub.add( 'OEXVPPYB: p_approval_code is: '||p_approval_code , 3 ) ;
368 oe_debug_pub.add( 'OEXVPPYB: p_trxn_extension_id is: '||p_trxn_extension_id , 3 ) ;
369 oe_debug_pub.add( 'OEXVPPYB: org_id is: '||p_header_rec.org_id , 3 ) ;
370 oe_debug_pub.add( 'OEXVPPYB: payment_set_id is: '||p_payment_set_id , 3 ) ;
371 END IF;
372
373 --changes for bug#8854662 start
374 BEGIN
375 SELECT acct_site.cust_account_id
376 INTO l_hdr_inv_to_cust_id
377 FROM hz_cust_acct_sites_all acct_site, hz_cust_site_uses_all site
378 WHERE SITE.SITE_USE_CODE = 'BILL_TO'
379 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
380 AND SITE.SITE_USE_ID = p_header_rec.invoice_to_org_id;
381 EXCEPTION
382 WHEN OTHERS THEN
383 l_hdr_inv_to_cust_id := p_header_rec.sold_to_org_id;
384 END;
385 --changes for bug#8854662 end
386
387 AR_PREPAYMENTS_PUB.create_prepayment(
388 p_api_version => 1.0,
389 p_commit => FND_API.G_FALSE,
390 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
391 x_return_status => x_return_status,
392 x_msg_count => x_msg_count,
393 x_msg_data => x_msg_data,
394 p_init_msg_list => FND_API.G_TRUE,
395 p_receipt_number => p_receipt_number, -- bug 4724845
396 p_amount => p_amount, -- pending_amount,
397 p_receipt_method_id => p_receipt_method_id,
398 --p_customer_id => p_header_rec.sold_to_org_id, --bug#8854662
399 p_customer_id => l_hdr_inv_to_cust_id, --bug#8854662
400 p_customer_site_use_id => p_header_rec.invoice_to_org_id,
401 p_customer_bank_account_id => p_bank_acct_id,
402 p_currency_code => p_header_rec.transactional_curr_code,
403 p_exchange_rate => l_exchange_rate,
404 p_exchange_rate_type => l_exchange_rate_type,
405 p_exchange_rate_date => l_exchange_rate_date,
406 p_applied_payment_schedule_id => -7, -- hard coded.
407 p_application_ref_type => l_application_ref_type ,
408 p_application_ref_num => l_application_ref_num, --Order Number
409 p_application_ref_id => l_application_ref_id, --Order Id
410 p_cr_id => l_cr_id, --OUT
411 p_receivable_application_id => l_receivable_application_id, --OUT
412 p_call_payment_processor => l_call_payment_processor,
413 p_remittance_bank_account_id => l_remittance_bank_account_id,
414 p_called_from => 'OM',
415 p_payment_server_order_num => l_payment_server_order_num,
416 p_approval_code => p_approval_code,
417 p_secondary_application_ref_id => l_secondary_application_ref_id,
418 p_payment_response_error_code => x_payment_response_error_code,
419 p_payment_set_id => p_payment_set_id,
420 p_org_id => p_header_rec.org_id,
421 p_payment_trxn_extension_id => p_trxn_extension_id
422 );
423
424
425 IF l_debug_level > 0 THEN
426 oe_debug_pub.add( 'OEXVPPYB: AFTER AR CREATE_PREPAYMENT CASH_RECEIPT_ID IS: '||L_CR_ID , 1 ) ;
427 oe_debug_pub.add( 'OEXVPPYB: AFTER AR CREATE_PREPAYMENT PAYMENT_SET_ID IS: '||P_PAYMENT_SET_ID , 1 ) ;
428 oe_debug_pub.add( 'OEXVPPYB: AFTER AR CREATE_PREPAYMENT CHECK NUMBER IS: '||P_RECEIPT_NUMBER , 1 ) ; -- bug 4724845
429 oe_debug_pub.add( 'OEXVPPYB: AFTER AR CREATE_PREPAYMENT x_payment_response_error_code IS: '||x_payment_response_error_code , 1 ) ;
430 oe_debug_pub.add( 'OEXVPPYB: AFTER AR CREATE_PREPAYMENT approval_code IS: '||p_approval_code , 1 ) ;
431 oe_debug_pub.add( 'OEXVPPYB: AFTER AR CREATE_PREPAYMENT x_msg_count IS: '||x_msg_count , 1 ) ;
432 oe_debug_pub.add( 'OEXVPPYB: AFTER AR CREATE_PREPAYMENT RETURN STATUS IS: '||X_RETURN_STATUS , 1 ) ;
433 END IF;
434
435 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
436 IF x_msg_count = 1 THEN
437 IF l_debug_level > 0 THEN
438 oe_debug_pub.add('Error message after calling Create_Prepayment API: '||x_msg_data , 3 ) ;
439 END IF;
440 --7559372 start
441 FND_MESSAGE.Set_Name('ONT','OE_PPCC_AUTH_FAIL');
442 FND_MESSAGE.Set_Token('PAYMENT_NUMBER',p_payment_number);
443 FND_MESSAGE.Set_Token('ERROR',x_msg_data);
444 x_msg_data := SUBSTR(FND_MESSAGE.GET,1,2000);
445 --7559372 end
446 oe_msg_pub.add_text(p_message_text => x_msg_data);
447 ELSIF ( FND_MSG_PUB.Count_Msg > 0 ) THEN
448 arp_util.enable_debug;
449 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
450 -- l_msg_text := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
451 l_msg_text := FND_MSG_PUB.Get(i,'F');
452 IF l_debug_level > 0 THEN
453 oe_debug_pub.Add( 'Error message from AR API: '|| L_MSG_TEXT , 3 );
454 END IF;
455 --7559372 start
456 FND_MESSAGE.Set_Name('ONT','OE_PPCC_AUTH_FAIL');
457 FND_MESSAGE.Set_Token('PAYMENT_NUMBER',p_payment_number);
458 FND_MESSAGE.Set_Token('ERROR',l_msg_text);
459 l_msg_text := SUBSTR(FND_MESSAGE.GET,1,2000);
460 --7559372 end
461 oe_msg_pub.add_text(p_message_text => l_msg_text);
462 END LOOP;
463 END IF;
464
465 x_result_out := 'FAIL';
466
467 -- RETURN;
468 END IF;
469
470 IF l_debug_level > 0 THEN
471 oe_debug_pub.add( 'EXITING OE_PREPAYMENT_PV.CREATE_RECEIPT.' , 1 ) ;
472 END IF;
473
474 EXCEPTION
475 WHEN OTHERS THEN
476 x_result_out := 'FAIL';
477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
478 IF l_debug_level > 0 THEN
479 oe_debug_pub.add('Unexpected error in Create_Prepayment API: '||sqlerrm , 3 ) ;
480 END IF;
481
482 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
483 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , 'Create_Receipt');
484 END IF;
485
486 OE_MSG_PUB.Count_And_Get
487 ( p_count => l_msg_count,
488 p_data => l_msg_data
489 );
490
491 END Create_Receipt;
492
493 /*--------------------------------------------------------------------------
494 Procedure Refund_Request
495 This procedure calls AR API to submit refund request for the amount specified.
496 ----------------------------------------------------------------------------*/
497 PROCEDURE Refund_Request
498 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
499 , p_amount IN NUMBER
500 , p_payment_set_id IN NUMBER
501 , x_msg_count OUT NOCOPY NUMBER
502 , x_msg_data OUT NOCOPY VARCHAR2
503 , x_return_status OUT NOCOPY VARCHAR2
504 , x_result_out OUT NOCOPY VARCHAR2
505 )
506 IS
507
508 l_return_status VARCHAR2(30);
509 l_prepay_application_id NUMBER;
510 l_number_of_refund_receipts NUMBER;
511 l_receipt_number VARCHAR2(30);
512 l_payment_set_id NUMBER;
513 l_refund_amount NUMBER;
514 l_format_mask VARCHAR2(500);
515 l_msg_count NUMBER := 0 ;
516 l_msg_data VARCHAR2(2000) := NULL ;
517
518 --
519 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
520 --
521 BEGIN
522
523 IF l_debug_level > 0 THEN
524 oe_debug_pub.add( 'ENTERING OE_PREPAYMENT_PV.REFUND_REQUEST.' , 1 ) ;
525 oe_debug_pub.add( 'BEFORE CALLING REFUND_PREPAYMENTS PAYMENT_SET_ID IS: '||P_PAYMENT_SET_ID , 1 ) ;
526 END IF;
527
528 AR_PREPAYMENTS.refund_prepayments(
529 p_api_version => 1.0,
530 p_commit => FND_API.G_FALSE,
531 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
532 x_return_status => l_return_status,
533 x_msg_count => x_msg_count,
534 x_msg_data => x_msg_data,
535 p_init_msg_list => FND_API.G_TRUE,
536 p_prepay_application_id => l_prepay_application_id, -- OUT NOCOPY /* file.sql.39 change */
537 p_number_of_refund_receipts => l_number_of_refund_receipts,
538 -- p_receipt_number => l_receipt_number,
539 p_receivables_trx_id => null,
540 p_refund_amount => p_amount * (-1),
541 p_payment_set_id => p_payment_set_id
542 );
543
544 x_return_status := l_return_status;
545 IF l_debug_level > 0 THEN
546 oe_debug_pub.add( 'OEXVPPYB: RECEIPT_NUMBER AFTER CALLING AR REFUND_PREPAYMENTS IS: '||L_RECEIPT_NUMBER , 1 ) ;
547 oe_debug_pub.add( 'OEXVPPYB: NUMBER_OF_REFUND AFTER CALLING AR REFUND_PREPAYMENT IS: '||L_NUMBER_OF_REFUND_RECEIPTS , 1 ) ;
548 END IF;
549
550 l_format_mask := get_format_mask(p_header_rec.transactional_curr_code);
551
552 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
553 fnd_message.Set_Name('ONT', 'ONT_REFUND_PROCESS_SUCCESS');
554 FND_MESSAGE.SET_TOKEN('AMOUNT' , TO_CHAR(p_amount * -1, l_format_mask));
555 FND_MESSAGE.SET_TOKEN('NUMBER' , l_number_of_refund_receipts);
556 oe_msg_pub.add;
557 IF l_debug_level > 0 THEN
558 oe_debug_pub.add( 'OEXVPPYB: REFUND REQUEST OF ' ||P_AMOUNT||' HAS BEEN PROCESSED SUCCESSFULLY.' , 3 ) ;
559 END IF;
560 ELSE
561 fnd_message.Set_Name('ONT', 'ONT_REFUND_PROCESS_FAILED');
562 FND_MESSAGE.SET_TOKEN('AMOUNT', TO_CHAR(p_amount, l_format_mask));
563 oe_msg_pub.add;
564 IF l_debug_level > 0 THEN
565 oe_debug_pub.add( 'OEXVPPYB: REFUND PROCESSING FOR ' ||P_AMOUNT||' FAILED.' , 3 ) ;
566 END IF;
567 END IF;
568
569 IF l_debug_level > 0 THEN
570 oe_debug_pub.add( 'EXITING OE_PREPAYMENT_PV.REFUND_REQUEST.' , 1 ) ;
571 END IF;
572
573 EXCEPTION WHEN OTHERS THEN
574 x_result_out := 'FAIL';
575 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
576
577 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
578 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , 'Refund_Request');
579 END IF;
580
581 OE_MSG_PUB.Count_And_Get
582 ( p_count => l_msg_count,
583 p_data => l_msg_data
584 );
585
586 END Refund_Request;
587
588 /*--------------------------------------------------------------------------
589 Procedure Process_PrePayment_Order
590 This is the main procedure for PrePayment. It is called from
591 OE_Verify_Payment_PUB.Verify_Payment for those prepaid orders.
592 ----------------------------------------------------------------------------*/
593 PROCEDURE Process_PrePayment_Order
594 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
595 , p_calling_action IN VARCHAR2
596 , p_delayed_request IN VARCHAR2
597 , x_msg_count OUT NOCOPY NUMBER
598 , x_msg_data OUT NOCOPY VARCHAR2
599 , x_return_status OUT NOCOPY VARCHAR2
600 )
601 IS
602
603 -- get all the lines for those haven't got INVOICE_INTERFACE_STATUS_CODE
604 -- populated.
605 CURSOR line_csr(p_header_id IN NUMBER) IS
606 SELECT line_id
607 FROM oe_order_lines
608 WHERE NVL(INVOICE_INTERFACE_STATUS_CODE, 'N') <> 'PREPAID'
609 AND header_id = p_header_id;
610
611
612 l_calling_action VARCHAR2(30) := p_calling_action;
613 l_header_rec OE_Order_PUB.Header_Rec_Type := p_header_rec;
614 l_pending_amount NUMBER := 0;
615 l_create_receipt VARCHAR2(1) := 'N';
616 l_request_refund VARCHAR2(1) := 'N';
617 l_apply_ppp_hold VARCHAR2(1) := 'N';
618 l_epayment_failure_hold VARCHAR2(1) := 'N';
619 l_ppp_hold_exists VARCHAR2(1) := 'N';
620 l_process_payment VARCHAR2(1) := 'N';
621 l_hold_exists VARCHAR2(1);
622 l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
623 l_threshold_amount NUMBER := 0;
624 l_bank_acct_id NUMBER;
625 l_bank_acct_uses_id NUMBER;
626 l_payment_response_error_code VARCHAR2(80);
627 l_approval_code VARCHAR2(80);
628 l_pay_method_id NUMBER;
629 l_exists_prepay VARCHAR2(1) := 'N';
630 l_line_id NUMBER;
631 l_payment_set_id NUMBER;
632 l_receipt_number VARCHAR2(30); -- bug 4724845
633 l_payment_types_rec OE_PAYMENTS_UTIL.Payment_Types_Rec_Type;
634 l_hold_result VARCHAR2(30);
635 l_msg_count NUMBER := 0 ;
636 l_msg_data VARCHAR2(2000):= NULL ;
637 l_result_out VARCHAR2(30) := NULL ;
638 l_return_status VARCHAR2(30) := NULL ;
639 l_fnd_profile_value VARCHAR2(1);
640 l_format_mask VARCHAR2(500);
641 l_trx_date DATE;
642 l_trxn_extension_id NUMBER; --R12 CC Encryption
643
644 --
645 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
646 --
647 BEGIN
648
649 IF l_debug_level > 0 THEN
650 oe_debug_pub.add( 'OEXVPPYB: ENTERING PROCESS PREPAYMENT ORDER' , 1 ) ;
651 END IF;
652 x_return_status := FND_API.G_RET_STS_SUCCESS;
653
654 -- calculate pending amount to be processed
655 l_pending_amount := OE_PrePayment_PVT.Calculate_Pending_Amount(l_header_rec);
656
657 IF l_debug_level > 0 THEN
658 oe_debug_pub.add( 'OEXVPPYB: PENDING AMOUNT IS: '||L_PENDING_AMOUNT , 3 ) ;
659 END IF;
660 IF l_pending_amount = 0 THEN
661 -- no payment processing needed
662 Release_Prepayment_Hold ( p_header_id => p_header_rec.header_id
663 , p_msg_count => l_msg_count
664 , p_msg_data => l_msg_data
665 , p_return_status => l_return_status
666 );
667
668 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
669 RAISE FND_API.G_EXC_ERROR;
670 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
671 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
672 END IF;
673 RETURN;
674 END IF;
675
676 /****************************************************************
677 if calling_action is NULL (this is coming from user action), then
678 if there is delta, then process payment.
679 if calling action is UPDATE (this is coming from delayed request),
680 or p_delayed_request is true, then
681 - if delta <> 0, apply PPP hold.(at this point order has been booked)
682 - else return.
683 if calling action is BOOKING, then
684 - if there is no ppp hold on the order(first time booking),
685 and profile option is set to be immediate at booking, then
686 process payment.
687 - else (there is ppp hold on the order, this comes from commit changes)
688 return.
689 else for other calling action, just return.
690
691 Error handler:
692 if AR API returns with error, apply credit card failure hold.
693 if AR API returns with success, release PPP hold and credit card failure hl.
694 *******************************************************************/
695
696 IF l_debug_level > 0 THEN
697 oe_debug_pub.add( 'OEXVPPYB: CALLING ACTION IS: '||L_CALLING_ACTION , 3 ) ;
698
699 END IF;
700
701 IF l_calling_action = 'UPDATE' THEN
702 IF l_debug_level > 0 THEN
703 oe_debug_pub.add( 'IN OEXVPPYB.PLS: THIS IS COMING FROM DELAYED REQUEST.' , 1 ) ;
704 END IF;
705 -- Need to apply PPP hold if there is change to order line after booking.
706
707 l_apply_ppp_hold := 'Y';
708
709 ELSIF l_calling_action = 'BOOKING' THEN
710 -- check if there is any ppp hold exists
711 OE_Verify_Payment_PUB.Hold_Exists
712 ( p_header_id => p_header_rec.header_id
713 , p_hold_id => 13 -- Seeded id for ppp hold
714 , p_hold_exists => l_ppp_hold_exists
715 );
716
717 IF l_debug_level > 0 THEN
718 oe_debug_pub.add( 'OEXVPPYB: VALUE FOR L_PPP_HOLD_EXISTS IS: '||L_PPP_HOLD_EXISTS , 1 ) ;
719 END IF;
720
721 l_fnd_profile_value := fnd_profile.value('ONT_PROCESS_PAYMENT_IMMEDIATELY');
722
723 IF l_debug_level > 0 THEN
724 oe_debug_pub.add( 'OEXVPPYB: VALUE FOR PREPAYMENT PROFILE OPTION IS : '||L_FND_PROFILE_VALUE , 1 ) ;
725 END IF;
726
727 IF l_ppp_hold_exists = 'N' THEN
728 IF l_fnd_profile_value= 'Y' THEN
729 l_process_payment := 'Y';
730 ELSE
731 l_apply_ppp_hold := 'Y';
732 END IF;
733 ELSE
734 IF l_debug_level > 0 THEN
735 oe_debug_pub.add( 'NO ACTION REQUIRED AS HOLD ALREADY EXISTS FOR HEADER: '||P_HEADER_REC.HEADER_ID , 3 ) ;
736 END IF;
737 RETURN;
738 END IF;
739
740 ELSIF l_calling_action is NULL THEN
741 -- this is coming from Action Process Payment or Concurrent Program.
742 l_process_payment := 'Y';
743
744 -- added for bug 4201622
745 IF p_delayed_request is NULL THEN
746 -- this is coming from concurrent manager, need to release all prepayment holds.
747 IF l_debug_level > 0 THEN
748 oe_debug_pub.add( 'OEXVPPYB: RELEASING ALL PREPAYMENT HOLDS AS THIS IS FROM CONCURRENT MANAGER.' , 1 ) ;
749 END IF;
750 Release_Prepayment_Hold ( p_header_id => l_header_rec.header_id
751 , p_msg_count => l_msg_count
752 , p_msg_data => l_msg_data
753 , p_return_status => l_return_status
754 );
755
756 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
757 RAISE FND_API.G_EXC_ERROR;
758 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
760 END IF;
761 END IF;
762
763 ELSE
764 -- no processing for other calling action
765 IF l_debug_level > 0 THEN
766 oe_debug_pub.add( 'NO ACTION REQUIRED AS CALLING_ACTION IS INVALID.' , 3 ) ;
767 END IF;
768 RETURN;
769 END IF;
770
771
772 IF l_process_payment = 'Y' THEN
773 IF l_pending_amount < 0 THEN
774 l_request_refund := 'Y';
775 ELSE
776 -- to get bank account id.
777 BEGIN
778
779 IF l_debug_level > 0 THEN
780 oe_debug_pub.add( 'OEXVPPYB: CURRENCY_CODE IS: '||P_HEADER_REC.TRANSACTIONAL_CURR_CODE , 3 ) ;
781 oe_debug_pub.add( 'OEXVPPYB: SOLD_TO_ORG_ID IS: '||P_HEADER_REC.SOLD_TO_ORG_ID , 3 ) ;
782 oe_debug_pub.add( 'OEXVPPYB: INVOICE_TO_ORG_ID IS: '||P_HEADER_REC.INVOICE_TO_ORG_ID , 3 ) ;
783 --oe_debug_pub.add( 'OEXVPPYB: CREDIT_CARD_NUMBER IS: '||P_HEADER_REC.CREDIT_CARD_NUMBER , 3 ) ;
784 --oe_debug_pub.add( 'OEXVPPYB: CREDIT_CARD_HOLDER IS: '||P_HEADER_REC.CREDIT_CARD_HOLDER_NAME , 3 ) ;
785 --oe_debug_pub.add( 'OEXVPPYB: CREDIT_CARD_EXP IS: '||P_HEADER_REC.CREDIT_CARD_EXPIRATION_DATE , 3 ) ;
786 END IF;
787
788 -- bug 3486808
789 --R12 CC Encryption
790 --Verify
791 /*l_trx_date := nvl(p_header_rec.ordered_date, sysdate)
792 - nvl( to_number(fnd_profile.value('ONT_DAYS_TO_BACKDATE_BANK_ACCT')), 0);
793
794 arp_bank_pkg.process_cust_bank_account
795 ( p_trx_date => l_trx_date
796 , p_currency_code => p_header_rec.transactional_curr_code
797 , p_cust_id => p_header_rec.sold_to_org_id
798 , p_site_use_id => p_header_rec.invoice_to_org_id
799 , p_credit_card_num => p_header_rec.credit_card_number
800 , p_acct_name => p_header_rec.credit_card_holder_name
801 , p_exp_date => p_header_rec.credit_card_expiration_date
802 , p_bank_account_id => l_bank_acct_id
803 , p_bank_account_uses_id => l_bank_acct_uses_id
804 ) ;*/
805
806 SELECT trxn_extension_id into l_trxn_extension_id
807 FROM OE_PAYMENTS where header_id = p_header_rec.header_id;
808 --R12 CC Encryption
809
810 EXCEPTION WHEN OTHERS THEN
811 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
812 OE_MSG_PUB.ADD;
813 IF l_debug_level > 0 THEN
814 oe_debug_pub.add( 'OEXVPPYB: ERROR IN ARP_BANK_PKG.PROCESS_CUST_BANK_ACCOUNT' , 3 ) ;
815 END IF;
816 -- apply epayment failure hold due to incorrect credit card information.
817 l_epayment_failure_hold := 'Y';
818 l_create_receipt := 'N';
819 END;
820
821 IF l_debug_level > 0 THEN
822 oe_debug_pub.add( 'OEXVPPYB: BANK ACCOUNT ID IS : '||L_BANK_ACCT_ID , 3 ) ;
823 END IF;
824
825 l_pay_method_id := OE_Verify_Payment_PUB.Get_Primary_Pay_Method
826 (p_header_rec => p_header_rec );
827
828 IF l_debug_level > 0 THEN
829 oe_debug_pub.add( 'OEXVPPYB: THE PRIMARY PAYMENT METHOD ID IS: '||L_PAY_METHOD_ID , 3 ) ;
830 END IF;
831
832 -- to validate the pay_method_id.
833 IF l_pay_method_id <= 0 THEN
834
835 IF l_debug_level > 0 THEN
836 oe_debug_pub.add( 'OEXVPPYB: THE PAYMENT METHOD ID IS INVALID: '||L_PAY_METHOD_ID , 3 ) ;
837 END IF;
838 FND_MESSAGE.SET_NAME('ONT','OE_VPM_NO_PAY_METHOD');
839 OE_MSG_PUB.ADD;
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
841 RETURN;
842 END IF;
843
844 IF l_debug_level > 0 THEN
845 oe_debug_pub.add( 'OEXVPPYB: L_RECEIPT_METHOD_ID IS: '||L_PAY_METHOD_ID , 1 ) ;
846 END IF;
847
848 l_create_receipt := 'Y';
849 END IF;
850 END IF; -- end of checking if l_process_payment is 'Y'.
851
852 IF l_debug_level > 0 THEN
853 oe_debug_pub.add( 'OEXVPPYB: L_PROCESS_PAYMENT IS: '||L_PROCESS_PAYMENT , 1 ) ;
854 oe_debug_pub.add( 'OEXVPPYB: L_CREATE_RECEIPT IS: '||L_CREATE_RECEIPT , 1 ) ;
855 oe_debug_pub.add( 'OEXVPPYB: L_APPLY_PPP_HOLD IS: '||L_APPLY_PPP_HOLD , 1 ) ;
856 END IF;
857
858 -- at this point, either apply hold or process payment.
859 IF l_apply_ppp_hold = 'Y' THEN
860
861
862 -- check for existing hold and apply hold if it doesn't exist already.
863 OE_GLOBALS.G_SYS_HOLD := TRUE; --8477694
864 Apply_Prepayment_Hold ( p_header_id => l_header_rec.header_id
865 , p_hold_id => 13 -- Seed Id for PPP Hold
866 , p_msg_count => l_msg_count
867 , p_msg_data => l_msg_data
868 , p_return_status => l_return_status
869 );
870 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
871
872 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
873
874 IF l_debug_level > 0 THEN
875 oe_debug_pub.add( 'OEXVPPYB: ORDER IS PLACED ON PPP HOLD.' , 3 ) ;
876 END IF;
877 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
878 RAISE FND_API.G_EXC_ERROR;
879 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
880 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
881 END IF;
882
883 ELSIF l_epayment_failure_hold = 'Y' THEN
884 OE_GLOBALS.G_SYS_HOLD := TRUE; --8477694
885 Apply_Prepayment_Hold ( p_header_id => l_header_rec.header_id
886 , p_hold_id => 14 -- Seed Id for PPP Hold
887 , p_msg_count => l_msg_count
888 , p_msg_data => l_msg_data
889 , p_return_status => l_return_status
890 );
891 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
892 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
893 RAISE FND_API.G_EXC_ERROR;
894 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
895 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
896 END IF;
897 ELSE
898 -- otherwise, call AR API to either create receipt or request refund.
899 -- check the installation status of IPayment
900 IF OE_GLOBALS.G_IPAYMENT_INSTALLED IS NULL THEN
901 OE_GLOBALS.G_IPAYMENT_INSTALLED := OE_GLOBALS.CHECK_PRODUCT_INSTALLED(673);
902 END IF;
903
904 IF OE_GLOBALS.G_IPAYMENT_INSTALLED <> 'Y' THEN
905 IF l_debug_level > 0 THEN
906 oe_debug_pub.add( 'OEXVPPYB: IPAYMENT IS NOT INSTALLED!' , 3 ) ;
907 END IF;
908 RETURN;
909 END IF;
910
911 IF l_create_receipt = 'Y' THEN
912
913 IF l_debug_level > 0 THEN
914 oe_debug_pub.add( 'OEXVPPYB: BEFORE CALLING AR API TO CREATE RECEIPT.' , 2 ) ;
915 oe_debug_pub.add( 'OEXVPPYB: BEFORE CHECKING THE TRANSACTION TYPE' , 3 ) ;
916 oe_debug_pub.add( 'OEXVPPYB: CALLING CREATE RECEIPT' , 3 ) ;
917 END IF;
918
919 -- call create receipt API
920 BEGIN
921 SELECT payment_set_id, check_number -- bug 4724845
922 INTO l_payment_set_id, l_receipt_number
923 FROM oe_payments
924 WHERE header_id = l_header_rec.header_id
925 AND payment_type_code = 'CREDIT_CARD';
926 EXCEPTION WHEN NO_DATA_FOUND THEN
927 l_payment_set_id := null;
928 END;
929
930 l_approval_code := p_header_rec.credit_card_approval_code;
931
932 OE_PrePayment_PVT.Create_Receipt
933 ( p_header_rec => p_header_rec
934 , p_amount => l_pending_amount
935 , p_receipt_method_id => l_pay_method_id
936 , p_bank_acct_id => l_bank_acct_id
937 , p_bank_acct_uses_id => l_bank_acct_uses_id
938 , p_trxn_extension_id => l_trxn_extension_id --R12 CC Encryption
939 , p_payment_set_id => l_payment_set_id
940 , p_receipt_number => l_receipt_number -- bug 4724845
941 , x_payment_response_error_code=> l_payment_response_error_code
942 , p_approval_code => l_approval_code
943 , x_msg_count => l_msg_count
944 , x_msg_data => l_msg_data
945 , x_result_out => l_result_out
946 , x_return_status => l_return_status
947 );
948
949 IF l_debug_level > 0 THEN
950 oe_debug_pub.add( 'OEXVPPYB: PAYMENT_SET_ID AFTER CREATE_RECEIPT: '||L_PAYMENT_SET_ID , 3 ) ;
951 oe_debug_pub.add( 'OEXVPPYB: RECEIPT NUMBER AFTER CREATE_RECEIPT: '||L_RECEIPT_NUMBER , 3 ) ; -- bug 4724845
952 oe_debug_pub.add( 'OEXVPPYB: RETURN_STATUS AFTER CALLING CREATE RECEIPT: '||L_RETURN_STATUS , 3 ) ;
953 END IF;
954
955 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
956 -- If no error occurred in processing the payment, release any existing
957 -- prepayment holds.
958 l_format_mask := get_format_mask(p_header_rec.transactional_curr_code);
959
960 FND_MESSAGE.SET_NAME('ONT','ONT_PAYMENT_PROCESS_SUCESS');
961 FND_MESSAGE.SET_TOKEN('AMOUNT' , TO_CHAR(l_pending_amount, l_format_mask));
962 OE_MSG_PUB.ADD;
963
964 IF l_debug_level > 0 THEN
965 oe_debug_pub.add( 'OEXVPPYB: RELEASING PREPAYMENT HOLD' , 3 ) ;
966 END IF;
967 Release_Prepayment_Hold ( p_header_id => l_header_rec.header_id
968 , p_msg_count => l_msg_count
969 , p_msg_data => l_msg_data
970 , p_return_status => l_return_status
971 );
972
973 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
974 RAISE FND_API.G_EXC_ERROR;
975 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
976 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
977 END IF;
978
979 -- to either insert or update the prepaid amount to oe_payments.
980 BEGIN
981 SELECT 'Y'
982 INTO l_exists_prepay
983 FROM oe_payments
984 WHERE header_id = l_header_rec.header_id
985 AND payment_type_code = 'CREDIT_CARD';
986 EXCEPTION WHEN NO_DATA_FOUND THEN
987 l_exists_prepay := 'N';
988 END;
989
990 IF l_exists_prepay = 'Y' THEN
991 -- update prepaid_amount on oe_order_headers
992 UPDATE oe_payments
993 SET prepaid_amount = nvl(prepaid_amount,0) + l_pending_amount
994 WHERE header_id = l_header_rec.header_id
995 AND payment_type_code = 'CREDIT_CARD';
996 ELSE
997 l_payment_types_rec.header_id := l_header_rec.header_id;
998 l_payment_types_rec.payment_set_id := l_payment_set_id;
999 l_payment_types_rec.payment_type_code := 'CREDIT_CARD';
1000 l_payment_types_rec.payment_trx_id := l_bank_acct_id;
1001 l_payment_types_rec.payment_level_code := 'ORDER';
1002 l_payment_types_rec.prepaid_amount := l_pending_amount;
1003 l_payment_types_rec.credit_card_number := l_header_rec.credit_card_number;
1004 l_payment_types_rec.credit_card_code := l_header_rec.credit_card_code;
1005 l_payment_types_rec.credit_card_holder_name
1006 := l_header_rec.credit_card_holder_name;
1007 l_payment_types_rec.credit_card_expiration_date
1008 := l_header_rec.credit_card_expiration_date;
1009 l_payment_types_rec.creation_date := SYSDATE;
1010 l_payment_types_rec.created_by := FND_GLOBAL.USER_ID;
1011 l_payment_types_rec.last_update_date := SYSDATE;
1012 l_payment_types_rec.last_updated_by := FND_GLOBAL.USER_ID;
1013
1014 oe_payments_util.insert_row(p_payment_types_rec => l_payment_types_rec);
1015
1016 END IF;
1017
1018 -- to update line information
1019 OPEN line_csr(l_header_rec.header_id);
1020 LOOP
1021
1022 FETCH line_csr INTO l_line_id;
1023 UPDATE oe_order_lines
1024 SET INVOICE_INTERFACE_STATUS_CODE = 'PREPAID'
1025 WHERE line_id = l_line_id;
1026
1027 EXIT WHEN line_csr%NOTFOUND;
1028 END LOOP;
1029 CLOSE line_csr;
1030
1031 ELSE
1032 -- if l_return_status is not FND_API.G_RET_STS_SUCCESS
1033 -- decode the error code return from AR, and apply the necessary holds.
1034 -- check for the existing holds, and apply the holds if non-existance.
1035 -- what is the seed id for ppp hold?
1036
1037 -- get the message count here, as we do not want to append
1038 -- the message ONT_PAYMENT_PROCESS_FAILED to the hold comments.
1039 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
1040
1041 l_format_mask := get_format_mask(p_header_rec.transactional_curr_code);
1042
1043 FND_MESSAGE.SET_NAME('ONT','ONT_PAYMENT_PROCESS_FAILED');
1044 FND_MESSAGE.SET_TOKEN('AMOUNT' , TO_CHAR(l_pending_amount, l_format_mask));
1045 OE_MSG_PUB.ADD;
1046
1047 -- fix for bug 4201632, get the messages and populate them
1048 -- as the hold comments.
1049 l_msg_data := null;
1050 FOR I IN 1..l_msg_count LOOP
1051 l_msg_data := l_msg_data||' '|| OE_MSG_PUB.Get(I,'F');
1052 IF l_debug_level > 0 THEN
1053 oe_debug_pub.add( L_MSG_DATA , 1 ) ;
1054 END IF;
1055 END LOOP;
1056
1057 -- check for existing hold and apply hold if it doesn't exist already.
1058 IF l_payment_response_error_code IN ('IBY_0001', 'IBY_0008') THEN
1059 -- need to apply epayment server failure hold (seeded id is 15).
1060 OE_GLOBALS.G_SYS_HOLD := TRUE; --8477694
1061 Apply_Prepayment_Hold ( p_header_id => l_header_rec.header_id
1062 , p_hold_id => 15
1063 , p_msg_count => l_msg_count
1064 , p_msg_data => l_msg_data
1065 , p_return_status => l_return_status
1066 );
1067 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
1068 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1069 RAISE FND_API.G_EXC_ERROR;
1070 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1072 END IF;
1073 ELSE
1074 -- for any other payment_response_error_code, need to apply epayment
1075 -- failure hold (seeded hold id is 14).
1076 OE_GLOBALS.G_SYS_HOLD := TRUE; --8477694
1077 Apply_Prepayment_Hold ( p_header_id => l_header_rec.header_id
1078 , p_hold_id => 14
1079 , p_msg_count => l_msg_count
1080 , p_msg_data => l_msg_data
1081 , p_return_status => l_return_status
1082 );
1083 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
1084
1085 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1086 RAISE FND_API.G_EXC_ERROR;
1087 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1089 END IF;
1090
1091
1092 END IF; -- end of checking l_payment_response_error_code.
1093
1094 END IF; -- end of checking AR API return status.
1095
1096 ELSIF l_request_refund = 'Y' THEN
1097
1098 BEGIN
1099 SELECT payment_set_id
1100 INTO l_payment_set_id
1101 FROM oe_payments
1102 WHERE header_id = l_header_rec.header_id
1103 AND payment_type_code = 'CREDIT_CARD';
1104 EXCEPTION WHEN NO_DATA_FOUND THEN
1105 l_payment_set_id := null;
1106 END;
1107
1108 IF l_debug_level > 0 THEN
1109 oe_debug_pub.add( 'OEXVPPYB: PAYMENT_SET_ID PASSED TO REFUND_REQUEST IS: '||L_PAYMENT_SET_ID , 1 ) ;
1110 oe_debug_pub.add( 'OEXVPPYB: CALLING REFUND REQUEST.' , 3 ) ;
1111 END IF;
1112 OE_PrePayment_PVT.Refund_Request( p_header_rec => p_header_rec
1113 , p_amount => l_pending_amount
1114 , p_payment_set_id=> l_payment_set_id
1115 , x_msg_count => l_msg_count
1116 , x_msg_data => l_msg_data
1117 , x_result_out => l_result_out
1118 , x_return_status => l_return_status
1119 );
1120
1121
1122 IF l_debug_level > 0 THEN
1123 oe_debug_pub.add( 'OEXVPPYB: RETURN STATUS AFTER CALLING REFUND IS: '||L_RETURN_STATUS , 1 ) ;
1124 END IF;
1125
1126 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1127 IF l_debug_level > 0 THEN
1128 oe_debug_pub.add( 'OEXVPPYB: RELEASING PREPAYMENT HOLD AFTER REFUND PROCESS.' , 3 ) ;
1129 END IF;
1130 Release_Prepayment_Hold ( p_header_id => l_header_rec.header_id
1131 , p_msg_count => l_msg_count
1132 , p_msg_data => l_msg_data
1133 , p_return_status => l_return_status
1134 );
1135
1136 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1137 RAISE FND_API.G_EXC_ERROR;
1138 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1140 END IF;
1141
1142 UPDATE oe_payments
1143 SET prepaid_amount = nvl(prepaid_amount,0) + l_pending_amount
1144 WHERE header_id = p_header_rec.header_id
1145 AND payment_type_code = 'CREDIT_CARD';
1146
1147 -- to update line information
1148 OPEN line_csr(l_header_rec.header_id);
1149 LOOP
1150
1151 FETCH line_csr INTO l_line_id;
1152 UPDATE oe_order_lines
1153 SET INVOICE_INTERFACE_STATUS_CODE = 'PREPAID'
1154 WHERE line_id = l_line_id;
1155
1156 EXIT WHEN line_csr%NOTFOUND;
1157 END LOOP;
1158 CLOSE line_csr;
1159
1160 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1161 RAISE FND_API.G_EXC_ERROR;
1162 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1164 END IF;
1165
1166 END IF;
1167 END IF;
1168
1169 x_return_status := l_return_status;
1170
1171 IF l_debug_level > 0 THEN
1172 oe_debug_pub.add( 'OEXVPPYB: EXITING PROCESS PREPAYMENT ORDER. '||x_return_status , 1 ) ;
1173 END IF;
1174
1175 EXCEPTION
1176
1177 WHEN FND_API.G_EXC_ERROR THEN
1178 x_return_status := FND_API.G_RET_STS_ERROR;
1179 OE_MSG_PUB.Count_And_Get
1180 ( p_count => l_msg_count,
1181 p_data => l_msg_data
1182 );
1183
1184 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186 OE_MSG_PUB.Count_And_Get
1187 ( p_count => l_msg_count,
1188 p_data => l_msg_data
1189 );
1190
1191 WHEN OTHERS THEN
1192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1193 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1194 THEN
1195 OE_MSG_PUB.Add_Exc_Msg
1196 ( G_PKG_NAME
1197 , 'Process_PrePayment_Order'
1198 );
1199 END IF;
1200
1201 OE_MSG_PUB.Count_And_Get
1202 ( p_count => l_msg_count,
1203 p_data => l_msg_data
1204 );
1205
1206 END Process_PrePayment_Order;
1207
1208 /*----------------------------------------------------------------------
1209 Returns 'Y' if any type of prepayment hold exists for the order.
1210 ----------------------------------------------------------------------*/
1211 PROCEDURE Any_Prepayment_Hold_Exists
1212 ( p_header_id IN NUMBER
1213 , p_hold_exists OUT NOCOPY VARCHAR2
1214 )
1215 IS
1216 l_hold_result VARCHAR2(30);
1217 l_return_status VARCHAR2(30);
1218 l_msg_count NUMBER;
1219 l_msg_data VARCHAR2(2000);
1220 --
1221 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1222 --
1223 BEGIN
1224
1225 IF l_debug_level > 0 THEN
1226 oe_debug_pub.add( 'OEXVPPYB: IN CHECK FOR PREPAYMENT HOLD' , 3 ) ;
1227 END IF;
1228
1229 -- Checking existense of unreleased holds on this order
1230 OE_HOLDS_PUB.Check_Holds
1231 ( p_api_version => 1.0
1232 , p_header_id => p_header_id
1233 , p_hold_id => 13
1234 , p_entity_code => 'O'
1235 , p_entity_id => p_header_id
1236 , x_result_out => l_hold_result
1237 , x_msg_count => l_msg_count
1238 , x_msg_data => l_msg_data
1239 , x_return_status => l_return_status
1240 );
1241
1242 -- Check the Result
1243 IF l_hold_result = FND_API.G_TRUE THEN
1244 p_hold_exists := 'Y';
1245 IF l_debug_level > 0 THEN
1246 oe_debug_pub.add( 'OEXVPPYB: PREPAYMENT HOLD 13 EXISTS ON ORDER' , 3 ) ;
1247 END IF;
1248 return;
1249 ELSE
1250 OE_HOLDS_PUB.Check_Holds
1251 ( p_api_version => 1.0
1252 , p_header_id => p_header_id
1253 , p_hold_id => 14
1254 , p_entity_code => 'O'
1255 , p_entity_id => p_header_id
1256 , x_result_out => l_hold_result
1257 , x_msg_count => l_msg_count
1258 , x_msg_data => l_msg_data
1259 , x_return_status => l_return_status
1260 );
1261 IF l_hold_result = FND_API.G_TRUE THEN
1262 p_hold_exists := 'Y';
1263 IF l_debug_level > 0 THEN
1264 oe_debug_pub.add( 'OEXVPPYB: PREPAYMENT HOLD 14 EXISTS ON ORDER' , 3 ) ;
1265 END IF;
1266 return;
1267 ELSE
1268 OE_HOLDS_PUB.Check_Holds
1269 ( p_api_version => 1.0
1270 , p_header_id => p_header_id
1271 , p_hold_id => 15
1272 , p_entity_code => 'O'
1273 , p_entity_id => p_header_id
1274 , x_result_out => l_hold_result
1275 , x_msg_count => l_msg_count
1276 , x_msg_data => l_msg_data
1277 , x_return_status => l_return_status
1278 );
1279 IF l_hold_result = FND_API.G_TRUE THEN
1280 p_hold_exists := 'Y';
1281 IF l_debug_level > 0 THEN
1282 oe_debug_pub.add( 'OEXVPPYB: PREPAYMENT HOLD 15 EXISTS ON ORDER' , 3 ) ;
1283 END IF;
1284 ELSE
1285 p_hold_exists := 'N';
1286 IF l_debug_level > 0 THEN
1287 oe_debug_pub.add( 'OEXVPPYB: NO PREPAYMENT HOLD ON ORDER' , 3 ) ;
1288 END IF;
1289 END IF;
1290 END IF;
1291 END IF;
1292
1293 EXCEPTION
1294
1295 WHEN OTHERS THEN
1296 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1297 THEN
1298 FND_MSG_PUB.Add_Exc_Msg
1299 ( G_PKG_NAME
1300 , 'Any_Prepayment_Hold_Exists'
1301 );
1302 END IF;
1303
1304 OE_MSG_PUB.Count_And_Get
1305 ( p_count => l_msg_count,
1306 p_data => l_msg_data
1307 );
1308
1309 END Any_Prepayment_Hold_Exists;
1310
1311 /*----------------------------------------------------------------------
1312 Applies a prepayment hold which based on the hold id passed in, uses
1313 standard Hold APIs.
1314 ----------------------------------------------------------------------*/
1315 PROCEDURE Apply_Prepayment_Hold
1316 ( p_header_id IN NUMBER
1317 , p_hold_id IN NUMBER
1318 , p_msg_count IN OUT NOCOPY NUMBER
1319 , p_msg_data IN OUT NOCOPY VARCHAR2
1320 , p_return_status OUT NOCOPY VARCHAR2
1321 )
1322 IS
1323
1324 l_hold_exists VARCHAR2(1) := NULL ;
1325 l_msg_count NUMBER := 0;
1326 l_msg_data VARCHAR2(2000);
1327 l_return_status VARCHAR2(30);
1328
1329 l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
1330
1331 --
1332 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1333 --
1334 BEGIN
1335 p_return_status := FND_API.G_RET_STS_SUCCESS;
1336
1337 IF l_debug_level > 0 THEN
1338 oe_debug_pub.add( 'OEXVPPYB: IN APPLY PREPAYMENT HOLDS' , 3 ) ;
1339 oe_debug_pub.add( 'OEXVPPYB: HEADER ID : '||P_HEADER_ID , 3 ) ;
1340 oe_debug_pub.add( 'OEXVPPYB: HOLD ID : '||P_HOLD_ID , 3 ) ;
1341 END IF;
1342
1343 -- Check if Hold already exists on this order
1344 IF l_debug_level > 0 THEN
1345 oe_debug_pub.add( 'OEXVPPYB: CHECKING IF REQUESTED PREPAYMENT HOLD ALREADY APPLIED' , 3 ) ;
1346 END IF;
1347 Any_Prepayment_Hold_Exists ( p_header_id => p_header_id
1348 , p_hold_exists => l_hold_exists
1349 );
1350
1351 -- Return with Success if this Hold Already exists on the order
1352 IF l_hold_exists = 'Y' THEN
1353 IF l_debug_level > 0 THEN
1354 oe_debug_pub.add( 'OEXVPPYB: HOLD ALREADY APPLIED ON HEADER ID : ' || P_HEADER_ID , 3 ) ;
1355 END IF;
1356 RETURN ;
1357 END IF ;
1358
1359 -- Apply Prepayment Hold on Header
1360 IF l_debug_level > 0 THEN
1361 oe_debug_pub.add( 'OEXVPPYB: APPLYING PREPAYMENT HOLD ON HEADER ID : ' || P_HEADER_ID , 3 ) ;
1362 END IF;
1363
1364 l_hold_source_rec.hold_id := p_hold_id ; -- Requested Hold
1365 l_hold_source_rec.hold_entity_code:= 'O'; -- Order Hold
1366 l_hold_source_rec.hold_entity_id := p_header_id; -- Order Header
1367
1368 -- to populate hold comments with the error messages.
1369 IF p_hold_id in (14, 15) THEN
1370 l_hold_source_rec.hold_comment := SUBSTR(p_msg_data,1,2000);
1371 END IF;
1372
1373 OE_Holds_PUB.Apply_Holds
1374 ( p_api_version => 1.0
1375 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1376 , p_hold_source_rec => l_hold_source_rec
1377 , x_msg_count => l_msg_count
1378 , x_msg_data => l_msg_data
1379 , x_return_status => l_return_status
1380 );
1381
1382 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1383 IF p_hold_id = 13 THEN
1384 FND_MESSAGE.SET_NAME('ONT','ONT_PPP_HOLD_APPLIED');
1385 OE_MSG_PUB.ADD;
1386 IF l_debug_level > 0 THEN
1387 oe_debug_pub.add( 'OEXVPPYB: PPP Hold has been applied on order.', 3 ) ;
1388 END IF;
1389 ELSIF p_hold_id = 14 THEN
1390 FND_MESSAGE.SET_NAME('ONT','ONT_PAYMENT_FAILURE_HOLD');
1391 OE_MSG_PUB.ADD;
1392 IF l_debug_level > 0 THEN
1393 oe_debug_pub.add( 'OEXVPPYB: payment failure hold has been applied on order.', 3 ) ;
1394 END IF;
1395 ELSIF p_hold_id = 15 THEN
1396 FND_MESSAGE.SET_NAME('ONT','ONT_PAYMENT_SERVER_FAIL_HOLD');
1397 OE_MSG_PUB.ADD;
1398 IF l_debug_level > 0 THEN
1399 oe_debug_pub.add( 'OEXVPPYB: payment server failure hold has been applied on order.', 3 ) ;
1400 END IF;
1401 END IF;
1402
1403 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1404 RAISE FND_API.G_EXC_ERROR;
1405 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1407 END IF;
1408
1409 IF l_debug_level > 0 THEN
1410 oe_debug_pub.add( 'OEXVPPYB: APPLIED PREPAYMENT HOLD ON HEADER ID:' || P_HEADER_ID , 3 ) ;
1411 END IF;
1412
1413 EXCEPTION
1414
1415 WHEN FND_API.G_EXC_ERROR THEN
1416 p_return_status := FND_API.G_RET_STS_ERROR;
1417 OE_MSG_PUB.Count_And_Get
1418 ( p_count => l_msg_count,
1419 p_data => l_msg_data
1420 );
1421
1422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1423 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1424 OE_MSG_PUB.Count_And_Get
1425 ( p_count => l_msg_count,
1426 p_data => l_msg_data
1427 );
1428
1429 WHEN OTHERS THEN
1430 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1431 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1432 THEN
1433 FND_MSG_PUB.Add_Exc_Msg
1434 ( G_PKG_NAME
1435 , 'Apply_Prepayment_Hold'
1436 );
1437 END IF;
1438
1439 OE_MSG_PUB.Count_And_Get
1440 ( p_count => l_msg_count,
1441 p_data => l_msg_data
1442 );
1443
1444 END Apply_Prepayment_Hold;
1445
1446 /*----------------------------------------------------------------------
1447 Releases all Prepayment Holds on the Order, uses standard Hold APIs.
1448 ----------------------------------------------------------------------*/
1449
1450 PROCEDURE Release_Prepayment_Hold
1451 ( p_header_id IN NUMBER
1452 , p_msg_count OUT NOCOPY NUMBER
1453 , p_msg_data OUT NOCOPY VARCHAR2
1454 , p_return_status OUT NOCOPY VARCHAR2
1455 )
1456 IS
1457
1458 --ER#7479609 l_hold_entity_id NUMBER := p_header_id;
1459 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE := p_header_id; --ER#7479609
1460 l_hold_id NUMBER;
1461 l_hold_exists VARCHAR2(1);
1462 l_msg_count NUMBER := 0;
1463 l_msg_data VARCHAR2(2000);
1464 l_return_status VARCHAR2(30);
1465 l_release_reason VARCHAR2(30);
1466 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
1467 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
1468
1469 --
1470 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1471 --
1472 BEGIN
1473 p_return_status := FND_API.G_RET_STS_SUCCESS;
1474 OE_GLOBALS.G_SYS_HOLD := TRUE; --8477694
1475
1476 IF l_debug_level > 0 THEN
1477 oe_debug_pub.add( 'OEXVPPYB: IN RELEASE PREPAYMENT HOLD' , 3 ) ;
1478 END IF;
1479
1480 -- Check What type of Holds to Release
1481 IF l_debug_level > 0 THEN
1482 oe_debug_pub.add( 'OEXVPPYB: RELEASE PREPAYMENT HOLDS FOR HEADER ID : ' || L_HOLD_ENTITY_ID , 3 ) ;
1483 END IF;
1484
1485 -- check for PPP hold.
1486 l_hold_id := 13 ;
1487
1488 IF l_debug_level > 0 THEN
1489 oe_debug_pub.add( 'OEXVPPYB: CHECKING EXISTENCE OF HOLD ID : '||L_HOLD_ID , 3 ) ;
1490 END IF;
1491 OE_Verify_Payment_PUB.Hold_Exists
1492 ( p_header_id => l_hold_entity_id
1493 , p_hold_id => l_hold_id
1494 , p_hold_exists => l_hold_exists
1495 ) ;
1496
1497 IF l_hold_exists = 'Y' THEN
1498
1499 IF l_debug_level > 0 THEN
1500 oe_debug_pub.add( 'OEXVPPYB: RELEASING CC RISK HOLD ON ORDER HEADER ID:' || L_HOLD_ENTITY_ID , 3 ) ;
1501 END IF;
1502 l_hold_source_rec.hold_id := l_hold_id;
1503 l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
1504 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
1505
1506 l_hold_release_rec.release_reason_code := 'PREPAYMENT';
1507 l_hold_release_rec.release_comment := 'Prepayment has been processed. Hold released automatically.';
1508
1509 OE_Holds_PUB.Release_Holds
1510 ( p_api_version => 1.0
1511 , p_hold_source_rec => l_hold_source_rec
1512 , p_hold_release_rec => l_hold_release_rec
1513 , x_msg_count => l_msg_count
1514 , x_msg_data => l_msg_data
1515 , x_return_status => l_return_status
1516 );
1517
1518 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1519 RAISE FND_API.G_EXC_ERROR;
1520 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1522 END IF;
1523 --bug3599715 start
1524 fnd_message.Set_Name('ONT', 'ONT_PPP_HOLD_RELEASED');
1525 oe_msg_pub.add;
1526 --bug3599715 end
1527 END IF;
1528
1529 -- check for epayment failure hold.
1530 l_hold_id := 14 ;
1531
1532 IF l_debug_level > 0 THEN
1533 oe_debug_pub.add( 'OEXVPPYB: CHECKING EXISTENCE OF HOLD ID : '||L_HOLD_ID , 3 ) ;
1534 END IF;
1535 OE_Verify_Payment_PUB.Hold_Exists
1536 ( p_header_id => l_hold_entity_id
1537 , p_hold_id => l_hold_id
1538 , p_hold_exists => l_hold_exists
1539 ) ;
1540
1541 IF l_hold_exists = 'Y' THEN
1542
1543 IF l_debug_level > 0 THEN
1544 oe_debug_pub.add( 'OEXVPPYB: RELEASING PAYMENT FAILURE HOLD ON ORDER HEADER ID:' || L_HOLD_ENTITY_ID , 3 ) ;
1545 END IF;
1546 l_hold_source_rec.hold_id := l_hold_id;
1547 l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
1548 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
1549
1550 l_hold_release_rec.release_reason_code := 'PREPAYMENT';
1551 l_hold_release_rec.release_comment := 'Prepayment has been processed. Hold released automatically.';
1552
1553 OE_Holds_PUB.Release_Holds
1554 ( p_api_version => 1.0
1555 , p_hold_source_rec => l_hold_source_rec
1556 , p_hold_release_rec => l_hold_release_rec
1557 , x_msg_count => l_msg_count
1558 , x_msg_data => l_msg_data
1559 , x_return_status => l_return_status
1560 );
1561
1562 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1563 RAISE FND_API.G_EXC_ERROR;
1564 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1566 END IF;
1567 fnd_message.Set_Name('ONT', 'ONT_PMNT_FAIL_HOLD_RELEASED');
1568 oe_msg_pub.add;
1569 END IF;
1570
1571 -- check for epayment server failure hold.
1572 l_hold_id := 15 ;
1573
1574 IF l_debug_level > 0 THEN
1575 oe_debug_pub.add( 'OEXVPPYB: CHECKING EXISTENCE OF HOLD ID : '||L_HOLD_ID , 3 ) ;
1576 END IF;
1577 OE_Verify_Payment_PUB.Hold_Exists
1578 ( p_header_id => l_hold_entity_id
1579 , p_hold_id => l_hold_id
1580 , p_hold_exists => l_hold_exists
1581 ) ;
1582
1583 IF l_hold_exists = 'Y' THEN
1584
1585 IF l_debug_level > 0 THEN
1586 oe_debug_pub.add( 'OEXVPPYB: RELEASING PAYMENT SERVER FAILURE HOLD ON ORDER HEADER ID:' || L_HOLD_ENTITY_ID , 3 ) ;
1587 END IF;
1588 l_hold_source_rec.hold_id := l_hold_id;
1589 l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
1590 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
1591
1592 l_hold_release_rec.release_reason_code := 'PREPAYMENT';
1593 l_hold_release_rec.release_comment := 'Prepayment has been processed. Hold released automatically.';
1594
1595 OE_Holds_PUB.Release_Holds
1596 ( p_api_version => 1.0
1597 , p_hold_source_rec => l_hold_source_rec
1598 , p_hold_release_rec => l_hold_release_rec
1599 , x_msg_count => l_msg_count
1600 , x_msg_data => l_msg_data
1601 , x_return_status => l_return_status
1602 );
1603
1604 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1605 RAISE FND_API.G_EXC_ERROR;
1606 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1607 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1608 END IF;
1609 fnd_message.Set_Name('ONT', 'ONT_PMNT_SERVER_HOLD_RELEASED');
1610 oe_msg_pub.add;
1611 END IF;
1612 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
1613 EXCEPTION
1614
1615 WHEN FND_API.G_EXC_ERROR THEN
1616 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
1617 p_return_status := FND_API.G_RET_STS_ERROR;
1618 OE_MSG_PUB.Count_And_Get
1619 ( p_count => l_msg_count,
1620 p_data => l_msg_data
1621 );
1622
1623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1624 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
1625 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1626 OE_MSG_PUB.Count_And_Get
1627 ( p_count => l_msg_count,
1628 p_data => l_msg_data
1629 );
1630
1631 WHEN OTHERS THEN
1632 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
1633 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1634 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1635 THEN
1636 FND_MSG_PUB.Add_Exc_Msg
1637 ( G_PKG_NAME
1638 , 'Release_Prepayment_Hold'
1639 );
1640 END IF;
1641
1642 OE_MSG_PUB.Count_And_Get
1643 ( p_count => l_msg_count,
1644 p_data => l_msg_data
1645 );
1646
1647 END Release_Prepayment_Hold;
1648
1649 /*----------------------------------------------------------------------
1650 Releases Payment Hold on the Order, uses standard Hold APIs.
1651 ----------------------------------------------------------------------*/
1652
1653 PROCEDURE Release_Payment_Hold
1654 ( p_header_id IN NUMBER
1655 , p_hold_id IN NUMBER
1656 , p_msg_count OUT NOCOPY NUMBER
1657 , p_msg_data OUT NOCOPY VARCHAR2
1658 , p_return_status OUT NOCOPY VARCHAR2
1659 )
1660 IS
1661
1662 --ER#7479609 l_hold_entity_id NUMBER := p_header_id;
1663 l_hold_entity_id oe_hold_sources_all.hold_entity_id%TYPE := p_header_id; --ER#7479609
1664 l_hold_id NUMBER;
1665 l_hold_exists VARCHAR2(1);
1666 l_msg_count NUMBER := 0;
1667 l_msg_data VARCHAR2(2000);
1668 l_return_status VARCHAR2(30);
1669 l_release_reason VARCHAR2(30);
1670 l_hold_source_rec OE_HOLDS_PVT.Hold_Source_Rec_Type;
1671 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
1672
1673 --
1674 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1675 --
1676 BEGIN
1677 p_return_status := FND_API.G_RET_STS_SUCCESS;
1678
1679 IF l_debug_level > 0 THEN
1680 oe_debug_pub.add( 'OEXVPPYB: IN RELEASE PAYMENT HOLD' , 3 ) ;
1681 END IF;
1682
1683 -- Check What type of Holds to Release
1684 IF l_debug_level > 0 THEN
1685 oe_debug_pub.add( 'OEXVPPYB: RELEASE PAYMENT HOLDS FOR HEADER ID : ' || L_HOLD_ENTITY_ID , 3 ) ;
1686 END IF;
1687
1688 IF l_debug_level > 0 THEN
1689 oe_debug_pub.add( 'OEXVPPYB: CHECKING EXISTENCE OF HOLD ID : '||P_HOLD_ID , 3 ) ;
1690 END IF;
1691 OE_Verify_Payment_PUB.Hold_Exists
1692 ( p_header_id => l_hold_entity_id
1693 , p_hold_id => p_hold_id
1694 , p_hold_exists => l_hold_exists
1695 ) ;
1696
1697 IF l_hold_exists = 'Y' THEN
1698
1699 IF l_debug_level > 0 THEN
1700 oe_debug_pub.add( 'OEXVPPYB: RELEASING PAYMENT HOLD ON ORDER HEADER ID:' || L_HOLD_ENTITY_ID , 3 ) ;
1701 END IF;
1702 l_hold_source_rec.hold_id := p_hold_id;
1703 l_hold_source_rec.HOLD_ENTITY_CODE := 'O';
1704 l_hold_source_rec.HOLD_ENTITY_ID := l_hold_entity_id;
1705
1706 IF p_hold_id in(13, 14, 15) THEN
1707 OE_GLOBALS.G_SYS_HOLD := TRUE; --8477694
1708 l_hold_release_rec.release_reason_code := 'PREPAYMENT';
1709 l_hold_release_rec.release_comment := 'Prepayment has been processed. Hold released automatically.';
1710 ELSIF p_hold_id = 16 THEN
1711 l_hold_release_rec.release_reason_code := 'AUTH_EPAYMENT';
1712 l_hold_release_rec.release_comment := 'Payment has been processed. Hold released automatically.';
1713 END IF;
1714
1715
1716 OE_Holds_PUB.Release_Holds
1717 ( p_api_version => 1.0
1718 , p_hold_source_rec => l_hold_source_rec
1719 , p_hold_release_rec => l_hold_release_rec
1720 , x_msg_count => l_msg_count
1721 , x_msg_data => l_msg_data
1722 , x_return_status => l_return_status
1723 );
1724
1725 --8477694
1726 IF p_hold_id in(13, 14, 15) THEN
1727 OE_GLOBALS.G_SYS_HOLD := FALSE;
1728 END IF;
1729 --8477694
1730
1731 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1732 RAISE FND_API.G_EXC_ERROR;
1733 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1735 END IF;
1736 END IF;
1737
1738
1739 EXCEPTION
1740
1741 WHEN FND_API.G_EXC_ERROR THEN
1742 p_return_status := FND_API.G_RET_STS_ERROR;
1743 OE_MSG_PUB.Count_And_Get
1744 ( p_count => l_msg_count,
1745 p_data => l_msg_data
1746 );
1747
1748 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1749 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1750 OE_MSG_PUB.Count_And_Get
1751 ( p_count => l_msg_count,
1752 p_data => l_msg_data
1753 );
1754
1755 WHEN OTHERS THEN
1756 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1757 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1758 THEN
1759 FND_MSG_PUB.Add_Exc_Msg
1760 ( G_PKG_NAME
1761 , 'Release_Payment_Hold'
1762 );
1763 END IF;
1764
1765 OE_MSG_PUB.Count_And_Get
1766 ( p_count => l_msg_count,
1767 p_data => l_msg_data
1768 );
1769
1770 END Release_Payment_Hold;
1771
1772 FUNCTION Get_Format_Mask(p_currency_code IN VARCHAR2)
1773 RETURN VARCHAR2
1774 IS
1775
1776 l_precision NUMBER;
1777 l_ext_precision NUMBER;
1778 l_min_acct_unit NUMBER;
1779 l_format_mask VARCHAR2(500);
1780
1781 --
1782 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1783 --
1784 BEGIN
1785 FND_CURRENCY.Get_Info(p_currency_code, -- IN variable
1786 l_precision,
1787 l_ext_precision,
1788 l_min_acct_unit);
1789
1790 FND_CURRENCY.Build_Format_Mask(l_format_mask, 20, l_precision,
1791 l_min_acct_unit, TRUE
1792 );
1793
1794 RETURN l_format_mask;
1795 END Get_Format_Mask;
1796
1797 -- New procedure for pack J multiple payments project.
1798 PROCEDURE Process_Payments
1799 ( p_header_id IN NUMBER
1800 , p_line_id IN NUMBER DEFAULT null --bug3524209
1801 , p_calling_action IN VARCHAR2
1802 , p_amount IN NUMBER
1803 , p_delayed_request IN VARCHAR2
1804 --R12 CVV2
1805 --comm rej, p_reject_on_auth_failure IN VARCHAR2 DEFAULT NULL
1806 --comm rej, p_reject_on_risk_failure IN VARCHAR2 DEFAULT NULL
1807 , p_risk_eval_flag IN VARCHAR2 DEFAULT NULL --bug 6805953 'Y'
1808 --R12 CVV2
1809 , p_process_prepayment IN VARCHAR2 DEFAULT 'Y'
1810 , p_process_authorization IN VARCHAR2 DEFAULT 'Y'
1811 , x_msg_count OUT NOCOPY NUMBER
1812 , x_msg_data OUT NOCOPY VARCHAR2
1813 , x_return_status OUT NOCOPY VARCHAR2
1814 ) IS
1815
1816 l_header_rec OE_ORDER_PUB.Header_Rec_Type;
1817 l_line_id NUMBER;
1818 l_payment_set_id NUMBER := NULL;
1819 l_bank_acct_id NUMBER := NULL;
1820 l_bank_acct_uses_id NUMBER := NULL;
1821 l_application_ref_id NUMBER := p_header_id;
1822 l_application_ref_num NUMBER;
1823 l_payment_response_error_code VARCHAR2(80);
1824 l_approval_code VARCHAR2(80);
1825 l_header_payment_rec OE_Order_PUB.Header_Payment_Rec_Type;
1826 l_insert VARCHAR2(1) := 'N';
1827 l_status NUMBER;
1828 l_msg_count NUMBER := 0;
1829 l_msg_data VARCHAR2(2000) := NULL;
1830 l_return_status VARCHAR2(30) := NULL;
1831 l_result_out VARCHAR2(30) := NULL;
1832 l_pending_amount NUMBER := 0;
1833 l_calling_action VARCHAR2(30) := p_calling_action;
1834 l_apply_ppp_hold VARCHAR2(1) := 'N';
1835 l_ppp_hold_exists VARCHAR2(1) := 'N';
1836 l_process_payment VARCHAR2(1) := 'N';
1837 l_receipt_method_id NUMBER := 0;
1838 prev_receipt_method_id NUMBER := 0;
1839 l_orig_cc_approval_code VARCHAR2(80) := NULL;
1840 l_prepaid_amount number := 0;
1841 l_exists_cc_payment VARCHAR2(1) := 'N';
1842 l_prepay_application_id NUMBER;
1843 l_number_of_refund_receipts NUMBER;
1844 l_receipt_number VARCHAR2(30);
1845 l_cash_receipt_id NUMBER;
1846 l_receivable_application_id NUMBER;
1847 l_receivables_trx_id NUMBER;
1848 l_do_cc_authorization VARCHAR2(1) := 'N';
1849 l_msg_text VARCHAR2(2000);
1850 l_exists_prepay VARCHAR2(1) := 'N';
1851 l_rule_defined VARCHAR2(1);
1852 l_exists_prepay_lines VARCHAR2(1) := 'N';
1853 l_operand number;
1854 l_amount number;
1855 l_code varchar2(80);
1856 l_payment_exists VARCHAR2(1) := 'N';
1857 l_cc_rev_reauth_code VARCHAR2(30) := NULL; --CC Reversal ER#16014135
1858 --R12 CC Encryption
1859 l_trxn_extension_id NUMBER;
1860 --
1861 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1862 --
1863
1864 --moac
1865 l_org_id NUMBER;
1866
1867 CURSOR hdr_payments_cur (p_header_id IN NUMBER) IS
1868 SELECT /*MOAC_SQL_CHANGE*/ Opt.credit_check_flag,
1869 Op.receipt_method_id,
1870 Op.payment_type_code,
1871 op.defer_payment_processing_flag,
1872 Op.payment_set_id,
1873 Op.payment_trx_id,
1874 Op.payment_collection_event,
1875 Op.prepaid_amount,
1876 Op.credit_card_code,
1877 Op.credit_card_approval_code,
1878 Op.check_number,
1879 Op.payment_number,
1880 Op.payment_amount,
1881 Op.trxn_extension_id --R12 CC Encryption
1882 FROM oe_payment_types_all opt,
1883 Oe_payments op
1884 WHERE opt.payment_type_code = op.payment_type_code
1885 AND op.payment_collection_event = 'PREPAY'
1886 AND op.payment_type_code <> 'COMMITMENT'
1887 AND op.line_id is null
1888 AND op.header_id =p_header_id
1889 And opt.org_id=l_org_id; --moac
1890
1891 cursor payment_count is
1892 select count(payment_type_code)
1893 from oe_payments
1894 where header_id = p_header_id
1895 and line_id is null;
1896
1897 l_order_total number; -- Added for bug 8478559
1898
1899 BEGIN
1900
1901 IF l_debug_level > 0 THEN
1902 oe_debug_pub.add( 'OEXVPPYB: Entering Process_Payments procedure.' , 1 ) ;
1903 oe_debug_pub.add( 'OEXVPPYB: l_calling_action is: '||l_calling_action , 1 ) ;
1904 oe_debug_pub.add( 'OEXVPPYB: p_delayed_request is: '||p_delayed_request , 1 ) ;
1905 END IF;
1906 x_return_status := FND_API.G_RET_STS_SUCCESS;
1907
1908
1909 oe_Header_util.query_row (p_header_id => p_Header_id
1910 ,x_header_rec=> l_header_rec );
1911
1912 --bug3524209
1913 IF p_line_id IS NULL THEN
1914 -- to create payment records when needed.
1915
1916 BEGIN
1917 SELECT 'Y'
1918 INTO l_payment_exists
1919 FROM oe_payments
1920 WHERE header_id = p_header_id
1921 AND rownum = 1;
1922 EXCEPTION
1923 WHEN NO_DATA_FOUND THEN
1924 l_payment_exists := 'N';
1925 END;
1926
1927 IF l_payment_exists = 'N' THEN
1928 -- call this procedure to create payment record only when there
1929 -- does not exist any payment.
1930 update_hdr_payment(p_header_id => p_header_id
1931 ,p_action => 'ACTIONS_PAYMENTS'
1932 ,x_return_status => l_return_status
1933 ,x_msg_data => l_msg_data
1934 ,x_msg_count => l_msg_count
1935 );
1936 END IF;
1937
1938 update_payment_numbers(p_header_id => p_header_id
1939 ,p_line_id => p_line_id
1940 ,x_return_status => l_return_status
1941 ,x_msg_data => l_msg_data
1942 ,x_msg_count => l_msg_count
1943 );
1944 ELSE
1945 update_payment_numbers(p_header_id => p_header_id
1946 ,p_line_id => p_line_id
1947 ,x_return_status => l_return_status
1948 ,x_msg_data => l_msg_data
1949 ,x_msg_count => l_msg_count
1950 );
1951 END IF;
1952
1953 --bug3524209
1954 IF p_line_id IS NULL THEN
1955 BEGIN
1956 SELECT 'Y'
1957 INTO l_exists_prepay
1958 FROM oe_payments
1959 WHERE payment_collection_event = 'PREPAY'
1960 AND header_id = p_header_id
1961 AND rownum=1;
1962 EXCEPTION WHEN NO_DATA_FOUND THEN
1963 l_exists_prepay := 'N';
1964 END;
1965
1966 IF l_debug_level > 0 THEN
1967 oe_debug_pub.add( 'OEXVPPYB: l_exists_prepay flag is: '||l_exists_prepay , 3 ) ;
1968 END IF;
1969 -- Prepayment Processing
1970 IF l_exists_prepay = 'Y'
1971 AND nvl(l_calling_action,'X') NOT IN ('SHIPPING','PACKING' ,'PICKING')
1972 AND p_process_prepayment = 'Y' THEN
1973
1974 IF l_debug_level > 0 THEN
1975 oe_debug_pub.add( 'OEXVPPYB: start prepayment processing.' , 3 ) ;
1976 END IF;
1977
1978 l_orig_cc_approval_code := l_header_rec.credit_card_approval_code;
1979
1980 -- get the payment_set_id if exists for the order.
1981 BEGIN
1982 SELECT payment_set_id
1983 INTO l_payment_set_id
1984 FROM oe_payments
1985 WHERE header_id = p_header_id
1986 AND payment_set_id IS NOT NULL
1987 AND rownum = 1;
1988 EXCEPTION WHEN NO_DATA_FOUND THEN
1989 null;
1990 END;
1991
1992 --moac
1993 l_org_id := mo_global.get_current_org_id;
1994
1995 /* Added for bug 8478559 */
1996 -- Derive the Payment Amounts based on Payment Percentage, only if Payment Amount is not specified by user.
1997 -- This should happen when Payment Processing is being done for Deferred Payments.
1998 -- Or when user explicitly triggers Payment Processing from UI.
1999 -- Or when Booking is processing the non-deferred Payments.
2000 -- This logic applies only for Prepayment.
2001 IF l_debug_level > 0 THEN
2002 oe_debug_pub.add('Updating pre-payment records, to convert percentage to amount, for records where user has not specified the payment_amount', 5);
2003 END IF;
2004
2005 l_order_total := OE_OE_TOTALS_SUMMARY.Outbound_Order_Total(p_header_id => p_header_id, p_all_lines => 'Y');
2006
2007 update oe_payments
2008 set payment_amount = ((payment_percentage * l_order_total) / 100)
2009 where header_id = p_header_id
2010 and payment_collection_event = 'PREPAY'
2011 and payment_type_code <> 'COMMITMENT'
2012 and line_id is null
2013 and payment_amount is null
2014 and ( ( nvl(defer_payment_processing_flag, 'N') = 'Y' and l_calling_action IS NULL)
2015 OR
2016 ( nvl(defer_payment_processing_flag, 'N') = 'N' )
2017 );
2018
2019 IF l_debug_level > 0 THEN
2020 oe_debug_pub.add('Update completed, records updated : ' || sql%rowcount, 5);
2021 END IF;
2022 /* End of bug 8478559 */
2023
2024
2025 -- call AR API to create receipt for each payment method.
2026 -- l_insert should be set to 'Y' if there are no payment records
2027 -- in oe_payments table
2028 -- and payment information is only present in headers.
2029
2030 For c_payment_rec IN hdr_payments_cur (p_header_id) LOOP
2031
2032 IF l_debug_level > 0 THEN
2033 oe_debug_pub.add( 'OEXVPPYB: Entering LOOP of the payment records. ', 3 ) ;
2034 oe_debug_pub.add( 'OEXVPPYB: defer payment processing flag is: '||c_payment_rec.defer_payment_processing_flag , 3 ) ;
2035 oe_debug_pub.add( 'OEXVPPYB: payment type is: '||c_payment_rec.payment_type_code , 1 ) ;
2036 oe_debug_pub.add( 'OEXVPPYB: trxn_extension_id is: '||c_payment_rec.trxn_extension_id , 1 ) ;
2037 oe_debug_pub.add( 'OEXVPPYB: check number is: '||c_payment_rec.check_number , 1 ) ; -- bug 4724845
2038 END IF;
2039
2040 --5932506 l_approval_code := null; -- initialize for each record.
2041 l_approval_code := nvl(c_payment_rec.credit_card_approval_code,l_header_rec.credit_card_approval_code); -- 5932506
2042 l_prepaid_amount := nvl(c_payment_rec.prepaid_amount,0);
2043 l_pending_amount := nvl(c_payment_rec.payment_amount,0) - l_prepaid_amount;
2044 l_trxn_extension_id := c_payment_rec.trxn_extension_id; --R12 CC Encryption Verify!
2045
2046 IF ((nvl(c_payment_rec.defer_payment_processing_flag, 'N') = 'Y'
2047 AND l_calling_action = 'BOOKING')
2048 --bug3507871
2049 OR (l_calling_action='UPDATE' AND p_delayed_request=FND_API.G_TRUE))
2050 AND ( l_pending_amount <> 0 or c_payment_rec.payment_amount IS NULL ) THEN -- Modified for bug 8478559
2051 -- if calling action is null, this is invoked from action, we
2052 -- need to process payments regardless of the defer flag.
2053 -- Apply PPP prepayment hold (hold id 13) on the order;
2054
2055 IF l_debug_level > 0 THEN
2056 oe_debug_pub.add( 'OEXVPPYB: l_calling_action is: '||l_calling_action , 1 ) ;
2057 oe_debug_pub.add( 'OEXVPPYB: place order on PPP hold.' , 1 ) ;
2058 END IF;
2059 OE_GLOBALS.G_SYS_HOLD := TRUE; --8477694
2060 Apply_Prepayment_Hold
2061 ( p_header_id => p_header_id
2062 ,p_hold_id => 13 -- Seed Id for PPP Hold
2063 ,p_msg_count => l_msg_count
2064 ,p_msg_data => l_msg_data
2065 ,p_return_status => l_return_status
2066 );
2067 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
2068
2069 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2070 IF l_debug_level > 0 THEN
2071 oe_debug_pub.add( 'OEXVPPYB: ORDER IS PLACED ON PPP HOLD.' , 3 ) ;
2072 END IF;
2073 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2074 RAISE FND_API.G_EXC_ERROR;
2075 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2077 END IF;
2078 ELSE
2079 -- process the payments.
2080
2081 IF l_debug_level > 0 THEN
2082 oe_debug_pub.add( 'OEXVPPYB: l_pending_amount is: '||l_pending_amount, 3 ) ;
2083 END IF;
2084
2085 l_bank_acct_id := c_payment_rec.payment_trx_id;
2086
2087 l_receipt_method_id := c_payment_rec.receipt_method_id;
2088
2089 l_receipt_number := c_payment_rec.check_number; -- bug 4724845
2090 IF l_receipt_method_id is null then
2091 Begin -- receipt_method_id selection
2092 select receipt_method_id into l_receipt_method_id
2093 from oe_payment_types_vl
2094 where payment_type_code = c_payment_rec.payment_type_code
2095 and rownum = 1;
2096 exception
2097 when others then
2098 l_receipt_method_id := null;
2099 end; -- receipt_method_id selection
2100
2101 IF l_receipt_method_id is null THEN
2102 IF c_payment_rec.payment_type_code = 'CREDIT_CARD'
2103 OR c_payment_rec.payment_type_code = 'ACH'
2104 OR c_payment_rec.payment_type_code = 'DIRECT_DEBIT' THEN -- bug 8771134
2105
2106 IF l_debug_level > 0 THEN
2107 oe_debug_pub.add('OEXVPPYB: Calling OE_Verify_Payment_PUB.Get_Primary_Pay_Method');
2108 oe_debug_pub.add('OEXVPPYB: Sold To Org ID :'||l_header_rec.sold_to_org_id);
2109 oe_debug_pub.add('OEXVPPYB: Invoice To Org ID :'||l_header_rec.invoice_to_org_id);
2110 END IF;
2111
2112 l_receipt_method_id := OE_Verify_Payment_PUB.Get_Primary_Pay_Method
2113 ( p_header_rec => l_header_rec ) ;
2114
2115 IF l_debug_level > 0 THEN
2116 oe_debug_pub.add( 'OEXVPPYB: After Getting primary payment method'||l_receipt_method_id , 5 ) ;
2117 END IF;
2118 END IF; -- if payment_type_code is CREDIT_CARD etc.
2119 END IF; -- if l_receipt_method_id is null for credit card etc.
2120 END IF; -- if l_receipt_method_id is null
2121
2122 IF l_debug_level > 0 THEN
2123 oe_debug_pub.add( 'OEXVPPYB: l_receipt_method_id is: '||l_receipt_method_id, 3 ) ;
2124 END IF;
2125 --bug 5204358
2126 IF l_receipt_method_id =0 THEN
2127 FND_MESSAGE.SET_NAME('ONT','OE_ATTRIBUTE_REQUIRED');
2128 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','Receipt Method');
2129 oe_debug_pub.add('OEXVPPYB: receipt method is null.',3);
2130 oe_msg_pub.Add;
2131 RAISE FND_API.G_EXC_ERROR;
2132
2133 ELSE
2134 -- receipt method is not null
2135
2136 IF l_pending_amount > 0 then
2137
2138 /* ideally create_receipt api signature should have payment_rec
2139 as input instead of l_header_rec - however since prepayments
2140 is only at the header level, we are okay in pack J.
2141 In future, this needs to be done */
2142
2143 l_header_rec.credit_card_approval_code := c_payment_rec.credit_card_approval_code;
2144
2145 IF l_debug_level > 0 THEN
2146 oe_debug_pub.add( 'OEXVPPYB: Before calling Create_Receipt API.', 3 ) ;
2147 oe_debug_pub.add( 'OEXVPPYB: amount is: '||l_pending_amount, 3 ) ;
2148 oe_debug_pub.add( 'OEXVPPYB: bank_acct_id is: '||l_bank_acct_id, 3 ) ;
2149 oe_debug_pub.add( 'OEXVPPYB: check number is: '||l_receipt_number, 3 ) ; -- bug 4724845
2150 END IF;
2151
2152 OE_PrePayment_PVT.Create_Receipt
2153 ( p_header_rec => l_header_rec
2154 , p_amount => l_pending_amount
2155 , p_receipt_method_id => l_receipt_method_id
2156 , p_bank_acct_id => l_bank_acct_id
2157 , p_bank_acct_uses_id => l_bank_acct_uses_id
2158 , p_trxn_extension_id => l_trxn_extension_id --R12 CC Encryption
2159 , p_payment_set_id => l_payment_set_id
2160 , p_receipt_number => l_receipt_number -- bug 4724845
2161 , p_payment_number => c_payment_rec.payment_number --7559372
2162 , x_payment_response_error_code=> l_payment_response_error_code
2163 , p_approval_code => l_approval_code
2164 , x_msg_count => l_msg_count
2165 , x_msg_data => l_msg_data
2166 , x_result_out => l_result_out
2167 , x_return_status => l_return_status
2168 );
2169
2170 l_header_rec.credit_card_approval_code := l_orig_cc_approval_code;
2171
2172 IF l_debug_level > 0 THEN
2173 oe_debug_pub.add( 'OEXVPPYB: After calling Create_Receipt API, return status is: '||l_return_status, 3) ;
2174 END IF;
2175
2176 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2177
2178 IF l_debug_level > 0 THEN
2179 oe_debug_pub.add( 'OEXVPPYB: update oe_payments for payment_set_id: '||l_payment_set_id, 3) ;
2180 END IF;
2181
2182 UPDATE oe_payments
2183 SET payment_set_id = l_payment_set_id,
2184 prepaid_amount = l_pending_amount + l_prepaid_amount,
2185 credit_card_approval_code = l_approval_code
2186 WHERE header_id = p_header_id
2187 and nvl(payment_number, -1) = nvl(c_payment_rec.payment_number, -1);
2188
2189 BEGIN
2190 SELECT 'Y'
2191 INTO l_exists_prepay_lines
2192 FROM oe_payments
2193 WHERE payment_collection_event = 'PREPAY'
2194 AND payment_set_id is NULL
2195 AND header_id = p_header_id
2196 AND payment_type_code <> 'COMMITMENT'
2197 AND line_id is null
2198 AND rownum=1;
2199 EXCEPTION WHEN NO_DATA_FOUND THEN
2200 l_exists_prepay_lines := 'N';
2201 END;
2202
2203 IF l_exists_prepay_lines = 'N' THEN
2204 -- Release all Prepayment holds;
2205 Release_Prepayment_Hold ( p_header_id => p_header_id
2206 , p_msg_count => l_msg_count
2207 , p_msg_data => l_msg_data
2208 , p_return_status => l_return_status
2209 );
2210
2211 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2212 RAISE FND_API.G_EXC_ERROR;
2213 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2215 END IF;
2216 END IF;
2217
2218 -- END IF; -- if l_insert = 'Y'
2219
2220
2221 ELSE
2222 IF l_payment_response_error_code IN ('IBY_0001', 'IBY_0008') THEN
2223 -- apply epayment server failure hold (seeded id is 15).;
2224 IF l_debug_level > 0 THEN
2225 oe_debug_pub.add( 'after create_receipt, error code is: '||l_payment_response_error_code, 3 ) ;
2226 END IF;
2227
2228 -- get the message count here, as we do not want to append
2229 -- the message ONT_PAYMENT_PROCESS_FAILED to the hold comments.
2230 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
2231
2232 -- fix for bug 4201632, get the messages and populate them
2233 -- as the hold comments.
2234 l_msg_data := null;
2235 FOR I IN 1..l_msg_count LOOP
2236 l_msg_data := l_msg_data||' '|| OE_MSG_PUB.Get(I,'F');
2237 IF l_debug_level > 0 THEN
2238 oe_debug_pub.add( L_MSG_DATA , 1 ) ;
2239 END IF;
2240 END LOOP;
2241 OE_GLOBALS.G_SYS_HOLD := TRUE; --8477694
2242 Apply_Prepayment_Hold
2243 ( p_header_id => p_header_id
2244 ,p_hold_id => 15
2245 ,p_msg_count => l_msg_count
2246 ,p_msg_data => l_msg_data
2247 ,p_return_status => l_return_status
2248 );
2249 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
2250 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2251 RAISE FND_API.G_EXC_ERROR;
2252 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2253 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2254 END IF;
2255
2256 ELSE
2257 -- Apply payment failure hold (seeded hold id is 14);
2258 IF l_debug_level > 0 THEN
2259 oe_debug_pub.add( 'after create_receipt, applying payment failure hold.', 3 ) ;
2260 END IF;
2261 OE_GLOBALS.G_SYS_HOLD := TRUE; --8477694
2262 Apply_Prepayment_Hold
2263 ( p_header_id => p_header_id
2264 ,p_hold_id => 14 -- payment failure Hold
2265 ,p_msg_count => l_msg_count
2266 ,p_msg_data => l_msg_data
2267 ,p_return_status => l_return_status
2268 );
2269 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
2270 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2271 RAISE FND_API.G_EXC_ERROR;
2272 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2273 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2274 END IF;
2275
2276 END IF; -- if l_payment_response_error_code...
2277 END IF; -- if l_return status...
2278
2279 ELSIF l_pending_amount < 0 THEN
2280 IF l_debug_level > 0 THEN
2281 oe_debug_pub.add( 'OEXVPPYB Process_Payments: Before calling create_refund.', 3 ) ;
2282 oe_debug_pub.add( 'OEXVPPYB: amount is: '||l_pending_amount, 3 ) ;
2283 oe_debug_pub.add( 'OEXVPPYB: receipt_method_id is: '||l_receipt_method_id, 3 ) ;
2284 oe_debug_pub.add( 'OEXVPPYB: payment_set_id is: '||l_payment_set_id, 3 ) ;
2285 oe_debug_pub.add( 'OEXVPPYB: bank_acct_id is: '||l_bank_acct_id, 3 ) ;
2286 END IF;
2287
2288 Create_Refund(
2289 p_header_rec => l_header_rec,
2290 p_refund_amount => l_pending_amount* (-1),
2291 p_payment_set_id => l_payment_set_id,
2292 p_bank_account_id => l_bank_acct_id,
2293 p_receipt_method_id => l_receipt_method_id,
2294 x_return_status => l_return_status
2295 );
2296
2297 IF l_debug_level > 0 THEN
2298 oe_debug_pub.add( 'OEXVPPYB Process_Payments: after calling create_refund, return status is: '||l_return_status, 3 ) ;
2299 END IF;
2300
2301 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2302 UPDATE oe_payments
2303 SET prepaid_amount = nvl(prepaid_amount,0) + l_pending_amount
2304 WHERE header_id = p_header_id
2305 AND nvl(payment_number, -1) = nvl(c_payment_rec.payment_number, -1);
2306
2307 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2308 -- Apply payment failure hold (seeded hold id is 14);
2309 IF l_debug_level > 0 THEN
2310 oe_debug_pub.add( 'after create_refund, applying payment failure hold.', 3 ) ;
2311 END IF;
2312
2313 l_msg_count:=OE_MSG_PUB.COUNT_MSG;
2314
2315 -- fix for bug 4201632, get the messages and populate them
2316 -- as the hold comments.
2317 l_msg_data := null;
2318 FOR I IN 1..l_msg_count LOOP
2319 l_msg_data := l_msg_data||' '|| OE_MSG_PUB.Get(I,'F');
2320 IF l_debug_level > 0 THEN
2321 oe_debug_pub.add( L_MSG_DATA , 1 ) ;
2322 END IF;
2323 END LOOP;
2324 OE_GLOBALS.G_SYS_HOLD := TRUE; --8477694
2325 Apply_Prepayment_Hold
2326 ( p_header_id => p_header_id
2327 ,p_hold_id => 14 -- payment failure Hold
2328 ,p_msg_count => l_msg_count
2329 ,p_msg_data => l_msg_data
2330 ,p_return_status => l_return_status
2331 );
2332 OE_GLOBALS.G_SYS_HOLD := FALSE; --8477694
2333 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2334 RAISE FND_API.G_EXC_ERROR;
2335 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2336 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2337 END IF;
2338
2339 END IF;
2340 ELSIF l_pending_amount = 0 THEN
2341 Release_Prepayment_Hold ( p_header_id => p_header_id
2342 , p_msg_count => l_msg_count
2343 , p_msg_data => l_msg_data
2344 , p_return_status => l_return_status
2345 );
2346
2347 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2348 RAISE FND_API.G_EXC_ERROR;
2349 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2351 END IF;
2352
2353 END IF; -- if l_pending_amount > 0 ...
2354
2355 END IF; -- if l_receipt_method_id is not null
2356 END IF; -- whether or not to apply prepayment hold
2357
2358 END LOOP;
2359
2360 -- Update INVOICE_INTERFACE_STATUS_CODE of the line to 'PREPAID';
2361 -- for now do not update invoice interface status code
2362 -- check if there are no payments at line level
2363 -- if there are no payments at line level then check if
2364 -- payment_terms at the header level has prepaid_flag ='Y'
2365 -- and if there are no invoice payments at the header level.
2366 -- things can change - so , I am not sure whether this is a good
2367 -- idea or not.
2368
2369
2370 END IF; -- end l_exists_prepay
2371 -- End of Prepayment Processing.
2372 END IF; --bug3524209
2373
2374 /* Processing CC Authorization */
2375 -- call verify_payment for credit card authorization
2376 -- if exists credit card payment on the order.
2377 BEGIN
2378 -- if there exists line level credit card payments.
2379 SELECT 'Y'
2380 INTO l_do_cc_authorization
2381 FROM oe_payments
2382 WHERE payment_type_code = 'CREDIT_CARD'
2383 AND payment_collection_event = 'INVOICE'
2384 AND header_id = p_header_id
2385 AND rownum = 1;
2386 EXCEPTION WHEN NO_DATA_FOUND THEN
2387 l_do_cc_authorization := 'N';
2388 END;
2389
2390 IF l_do_cc_authorization = 'Y' AND p_process_authorization = 'Y' THEN
2391
2392 -- no need to check for any rules if calling action is null, which means
2393 -- this is invoked from on-line.
2394 IF l_calling_action IS NOT NULL AND l_header_rec.booked_flag = 'Y' THEN
2395 l_rule_defined := OE_Verify_Payment_PUB.Check_Rule_Defined
2396 ( p_header_rec => l_header_rec
2397 , p_calling_action => l_calling_action
2398 ) ;
2399
2400 IF l_debug_level > 0 THEN
2401 oe_debug_pub.add( 'OEXVPPYB: RULE DEFINED FOR AUTHORIZATION: '|| L_RULE_DEFINED ) ;
2402 END IF;
2403
2404 IF l_rule_defined = 'N' THEN
2405 --CC Reversal ER#16014135 Start
2406 IF l_calling_action ='UPDATE' THEN
2407 IF l_debug_level > 0 THEN
2408 oe_debug_pub.add( 'OEXVPPYB: No rule defined for authorization. l_calling_action '||l_calling_action ) ;
2409 END IF;
2410 RETURN;
2411 END IF;
2412 l_cc_rev_reauth_code:= OE_VERIFY_PAYMENT_PUB.Get_CC_Rev_Reauth_Code(p_header_id);
2413 IF l_cc_rev_reauth_code = 'REV_REAUTH'
2414 OR (l_cc_rev_reauth_code = 'REV_REAUTH_AT_PICK' AND l_calling_action = 'PICKING')
2415 OR (l_cc_rev_reauth_code = 'CC_HOLD' AND OE_Verify_Payment_PUB.G_init_calling_action NOT IN ('SHIPPING','PICKING','PACKING'))
2416 OR l_calling_action ='REVERSAL_REAUTH'
2417 OR oe_verify_payment_pub.G_CC_Change_Flag = 'Y'
2418 THEN
2419 IF l_debug_level > 0 THEN
2420 oe_debug_pub.add( 'Reversal/Re-authorization is set, need to call Credit Card Authorization',5 ) ;
2421 END IF;
2422 ELSE
2423 --CC Reversal ER#16014135 End
2424 IF l_debug_level > 0 THEN
2425 oe_debug_pub.add( 'OEXVPPYB: No rule defined for authorization. ' ) ;
2426 END IF;
2427 --bug3511992
2428 x_msg_count := OE_MSG_PUB.Count_Msg;
2429
2430 oe_debug_pub.add('pviprana: x_msg_count in OEXVPPYB.pls = ' || x_msg_count);
2431 RETURN;
2432 END IF; --CC Reversal ER#16014135
2433 END IF;
2434 END IF;
2435
2436 IF l_debug_level > 0 THEN
2437 oe_debug_pub.add( 'OEXVPPYB: Calling Credit Card Authorization for Multiple Payments.' ) ;
2438 END IF;
2439
2440 OE_Verify_Payment_PUB.Authorize_MultiPayments
2441 ( p_header_rec => l_header_rec
2442 , p_line_id => p_line_id --bug3524209
2443 , p_calling_action => l_calling_action
2444 --R12 CVV2
2445 --comm rej , p_reject_on_auth_failure => p_reject_on_auth_failure
2446 --comm rej , p_reject_on_risk_failure => p_reject_on_risk_failure
2447 , p_risk_eval_flag => p_risk_eval_flag
2448 --R12 CVV2
2449 , p_msg_count => l_msg_count
2450 , p_msg_data => l_msg_data
2451 , p_result_out => l_result_out
2452 , p_return_status => l_return_status
2453 );
2454
2455 IF l_debug_level > 0 THEN
2456 oe_debug_pub.add( 'OEXVPPYB: After calling OE_Verify_Payment_PUB.Authorize_MultiPayments, return status is: '||l_return_status, 3) ;
2457 END IF;
2458
2459
2460 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2461 RAISE FND_API.G_EXC_ERROR;
2462 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2464 END IF;
2465
2466 END IF; -- end of CC authorization.
2467
2468 -- to set the calling action.
2469 IF l_calling_action IS NULL THEN
2470 l_calling_action := OE_Verify_Payment_PUB.Which_Rule
2471 (p_header_id => p_header_id);
2472 END IF;
2473 --CC Reversal ER#16014135 Start
2474 IF l_debug_level > 0 THEN
2475 oe_debug_pub.add( 'oe_verify_payment_pub.G_credit_check_flag '||oe_verify_payment_pub.G_credit_check_flag , 5 ) ;
2476 oe_debug_pub.add( 'g_process_pmt_req_logged '||g_process_pmt_req_logged , 5 );
2477
2478 END IF;
2479 IF (oe_verify_payment_pub.G_credit_check_flag = 'N'
2480 AND NOT OE_Delayed_Requests_Pvt.Check_For_Request
2481 (p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_PAYMENT
2482 ,p_entity_id => p_header_id
2483 ,p_request_type => OE_GLOBALS.G_PROCESS_PAYMENT
2484 )) THEN
2485 -- don't do credit checking , only authorization needs to be done.
2486 oe_verify_payment_pub.G_credit_check_flag := 'Y';
2487 RETURN;
2488 END IF;
2489 --CC Reversal ER#16014135 end
2490
2491 -- Check rule defined before going to credit checking engine.
2492 IF l_calling_action IS NOT NULL THEN
2493 l_rule_defined := OE_Verify_Payment_PUB.Check_Rule_Defined
2494 ( p_header_rec => l_header_rec
2495 , p_calling_action => l_calling_action
2496 ) ;
2497
2498 IF l_debug_level > 0 THEN
2499 oe_debug_pub.add( 'OEXVPPYB: RULE DEFINED : '|| L_RULE_DEFINED ) ;
2500 END IF;
2501
2502 IF l_rule_defined = 'N' THEN
2503 --bug3511992
2504 x_msg_count := OE_MSG_PUB.Count_Msg;
2505
2506 oe_debug_pub.add('pviprana: x_msg_count in OEXVPPYB.pls = ' || x_msg_count);
2507
2508 return;
2509 END IF;
2510 END IF;
2511
2512 -- Do not need to call Credit Checking redundantly when pressing
2513 -- Process Payment button right after Save button is pressed when
2514 -- changing payment type code for Invoice payment, as changing
2515 -- payment type code would result in g_process_payment getting
2516 -- logged and executed, which would already have called
2517 -- Credit Checking, thus no need to call again when Process Payment
2518 -- button is pressesd in this case.
2519
2520
2521
2522 IF NOT (g_process_pmt_req_logged = 'Y'
2523 AND NOT OE_Delayed_Requests_Pvt.Check_For_Request
2524 (p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_PAYMENT
2525 ,p_entity_id => p_header_id
2526 ,p_request_type => OE_GLOBALS.G_PROCESS_PAYMENT
2527 )
2528 AND p_delayed_request = 'N') or l_exists_prepay = 'Y' THEN -- 9132289
2529
2530 ------------- Begin Multi currency credit checking changes ----
2531 ----------------------------------------------------------------
2532 -- The credit checking code
2533 -- ( NON- Electronic, NON iPayment )
2534 -- code is now maintained, developed, enhanced
2535 -- and Bug fixed in the new MUlti currency API's.
2536 -- including customers prior to OM patch set G will
2537 -- get the new API's
2538
2539 -- For clarifications, please contact
2540 -- Global Manufacturing
2541 ----------------------------------------------------------------
2542
2543 BEGIN
2544 IF l_debug_level > 0 THEN
2545 oe_debug_pub.add( 'OEXVPPYB: BEGIN CHECK FOR MCC CODE. ' , 1 ) ;
2546 oe_debug_pub.add( 'P_HEADER_ID = '|| P_HEADER_ID , 1 ) ;
2547 END IF;
2548
2549 BEGIN
2550 IF l_debug_level > 0 THEN
2551 oe_debug_pub.add( 'OEXVPPYB:INTO MULTI CREDIT CHECKING FLOW ' , 1 ) ;
2552 oe_debug_pub.add( 'OEXVPPYB: CALL OE_CREDIT_ENGINE_GRP' , 1 ) ;
2553 END IF;
2554
2555 OE_Credit_Engine_GRP.Credit_check_with_payment_typ
2556 ( p_header_id => p_header_id
2557 , p_calling_action => l_calling_action
2558 , p_delayed_request => p_delayed_request
2559 , p_credit_check_rule_id => NULL
2560 , x_msg_count => l_msg_count
2561 , x_msg_data => l_msg_data
2562 , x_return_status => l_return_status
2563 );
2564
2565 IF l_debug_level > 0 THEN
2566 oe_debug_pub.add( 'OEXVPPYB: OUT OF OE_CREDIT_ENGINE_GRP' , 1 ) ;
2567 oe_debug_pub.add( 'X_RETURN_STATUS = ' || L_RETURN_STATUS , 1 ) ;
2568 END IF;
2569
2570 END ;
2571 END ; -- End MCC Block
2572 END IF;
2573
2574 -- set the value back to N.
2575 IF g_process_pmt_req_logged = 'Y'
2576 --14284192 start
2577 -- Added p_delayed_request='T' condition. Needs to reset the global variable as soon as process_payment is executed
2578 --AND p_delayed_request = 'N'
2579 AND (p_delayed_request = 'N' OR p_delayed_request = 'T')
2580 --14284192 End
2581 AND p_calling_action is NULL
2582 THEN
2583 g_process_pmt_req_logged := 'N';
2584 END IF;
2585
2586 --bug3511992
2587 x_msg_count := OE_MSG_PUB.Count_Msg;
2588
2589 oe_debug_pub.add('pviprana: x_msg_count in OEXVPPYB.pls = ' || x_msg_count);
2590
2591 x_return_status := l_return_status;
2592
2593 IF l_debug_level > 0 THEN
2594 oe_debug_pub.add( 'OEXVPPYB: Exiting Process_Payments procedure. '||x_return_status , 1 ) ;
2595 END IF;
2596
2597 EXCEPTION
2598
2599 WHEN FND_API.G_EXC_ERROR THEN
2600 x_return_status := FND_API.G_RET_STS_ERROR;
2601 OE_MSG_PUB.Count_And_Get
2602 ( p_count => l_msg_count,
2603 p_data => l_msg_data
2604 );
2605
2606 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2608 IF l_debug_level > 0 THEN
2609 oe_debug_pub.add( 'Unexpected error in Process_Payments: ' || SQLERRM , 3 ) ;
2610 END IF;
2611 OE_MSG_PUB.Count_And_Get
2612 ( p_count => l_msg_count,
2613 p_data => l_msg_data
2614 );
2615
2616 WHEN OTHERS THEN
2617 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2618 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2619 THEN
2620 FND_MSG_PUB.Add_Exc_Msg
2621 ( G_PKG_NAME
2622 , 'Process_Payments'
2623 );
2624 END IF;
2625
2626 IF l_debug_level > 0 THEN
2627 oe_debug_pub.add( 'Oracle error in others in process_payments: '||SQLERRM , 3 ) ;
2628 END IF;
2629
2630 OE_MSG_PUB.Count_And_Get
2631 ( p_count => l_msg_count,
2632 p_data => l_msg_data
2633 );
2634
2635 END Process_Payments;
2636 procedure Split_Payment
2637 (p_line_id IN NUMBER
2638 ,p_header_id IN NUMBER
2639 ,p_split_from_line_id IN NUMBER
2640 ,x_return_status OUT NOCOPY VARCHAR2
2641 ,x_msg_count OUT NOCOPY NUMBER
2642 ,x_msg_data OUT NOCOPY VARCHAR2
2643 ) IS
2644
2645 l_line_payment_tbl OE_ORDER_PUB.Line_Payment_Tbl_Type;
2646 l_x_old_Line_Payment_tbl OE_ORDER_PUB.Line_Payment_Tbl_Type;
2647 l_from_line_payment_tbl OE_ORDER_PUB.Line_Payment_Tbl_Type;
2648 l_control_rec OE_GLOBALS.Control_Rec_Type;
2649 l_return_status VARCHAR2(2000);
2650 i NUMBER;
2651 j NUMBER;
2652 l_org_id NUMBER;
2653 l_site_use_id NUMBER;
2654 l_trxn_extension_id NUMBER;
2655 l_is_credit_card VARCHAR2(1) := 'N';
2656 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2657
2658 BEGIN
2659
2660 IF l_debug_level > 0 THEN
2661 oe_debug_pub.add('Entering OE_Prepayment_PVT.split_payment ', 1);
2662 oe_debug_pub.add('p_split_from_line_id is: '||p_split_from_line_id, 1);
2663 oe_debug_pub.add('p_header_id is: '||p_header_id, 1);
2664 oe_debug_pub.add('p_line_id is: '||p_line_id, 1);
2665 END IF;
2666
2667 /* Line Level payments for FROM Line */
2668 OE_Line_Payment_Util.Query_Rows(p_line_id => p_split_from_line_id,
2669 p_header_id => p_header_id,
2670 x_line_payment_tbl => l_from_line_payment_tbl);
2671
2672
2673 i := l_from_line_payment_tbl.First;
2674 j := 1;
2675 While i is not null Loop
2676 IF l_debug_level > 0 THEN
2677 oe_debug_pub.add('Inside split payment loop '||l_from_line_payment_tbl(i).payment_type_code,3);
2678 oe_debug_pub.add('Inside split payment loop I is: '||i, 1);
2679 END IF;
2680
2681 IF l_from_line_payment_tbl(i).payment_type_code = 'COMMITMENT' THEN
2682 -- commitment is handled by delayed request for commitment.
2683 goto next_in_loop;
2684 END IF;
2685
2686
2687 l_line_payment_tbl(j).operation := OE_GLOBALS.G_OPR_CREATE;
2688 l_line_payment_tbl(j).header_id := p_header_id;
2689 l_line_payment_tbl(j).line_id := p_line_id;
2690 l_line_payment_tbl(j).payment_type_code := l_from_line_payment_tbl(i).payment_type_code;
2691 l_line_payment_tbl(j).payment_collection_event := l_from_line_payment_tbl(i).payment_collection_event;
2692 l_line_payment_tbl(j).payment_level_code := l_from_line_payment_tbl(i).payment_level_code;
2693 l_line_payment_tbl(j).payment_trx_id := l_from_line_payment_tbl(i).payment_trx_id;
2694
2695 -- comment out for R12 cc encryption
2696 /*
2697 l_line_payment_tbl(j).credit_card_code := l_from_line_payment_tbl(i).credit_card_code;
2698 l_line_payment_tbl(j).credit_card_number := l_from_line_payment_tbl(i).credit_card_number;
2699 l_line_payment_tbl(j).credit_card_holder_name := l_from_line_payment_tbl(i).credit_card_holder_name;
2700 l_line_payment_tbl(j).credit_card_expiration_date := l_from_line_payment_tbl(i).credit_card_expiration_date;
2701 l_line_payment_tbl(j).credit_card_approval_code := l_from_line_payment_tbl(i).credit_card_approval_code;
2702 */
2703
2704 l_line_payment_tbl(j).receipt_method_id := l_from_line_payment_tbl(i).receipt_method_id;
2705 l_line_payment_tbl(j).check_number := l_from_line_payment_tbl(i).check_number;
2706 -- l_line_payment_tbl(j).payment_number := l_from_line_payment_tbl(i).payment_number;
2707
2708 IF l_from_line_payment_tbl(i).payment_type_code = 'CREDIT_CARD' THEN
2709
2710 l_is_credit_card := 'Y';
2711
2712 -- create a new trxn_extension_id for the child line from the parent line
2713 BEGIN
2714 SELECT org_id, invoice_to_org_id
2715 INTO l_org_id, l_site_use_id
2716 FROM oe_order_lines_all
2717 WHERE line_id = p_line_id;
2718 EXCEPTION WHEN NO_DATA_FOUND THEN
2719 null;
2720 END;
2721
2722 IF l_debug_level > 0 THEN
2723 oe_debug_pub.add('source trxn_extenion_id is: '||l_from_line_payment_tbl(i).trxn_extension_id,3);
2724 END IF;
2725
2726 -- bug 5204275
2727 IF Oe_Payment_Trxn_Util.Get_CC_Security_Code_Use = 'REQUIRED' THEN
2728
2729 IF l_debug_level > 0 THEN
2730 oe_debug_pub.add(' security code is required.',3);
2731 END IF;
2732
2733 l_trxn_extension_id := null;
2734 l_line_payment_tbl(j).credit_card_approval_code := 'CVV2_REQUIRED';
2735 l_line_payment_tbl(j).payment_type_code := null;
2736
2737 ELSE
2738
2739 OE_Verify_Payment_PUB.Create_New_Payment_Trxn
2740 (p_trxn_extension_id => l_from_line_payment_tbl(i).trxn_extension_id,
2741 p_org_id => l_org_id,
2742 p_site_use_id => l_site_use_id,
2743 p_line_id => p_line_id,
2744 x_trxn_extension_id => l_trxn_extension_id,
2745 x_msg_count => x_msg_count,
2746 x_msg_data => x_msg_data,
2747 x_return_status => x_return_status);
2748 END IF;
2749
2750 IF l_debug_level > 0 THEN
2751 oe_debug_pub.add('new trxn_extenion_id is: '||l_trxn_extension_id,3);
2752 END IF;
2753
2754 l_line_payment_tbl(j).trxn_extension_id := l_trxn_extension_id;
2755
2756 END IF;
2757
2758 j := j + 1;
2759 <<next_in_loop>>
2760 i:= l_from_line_payment_tbl.Next(i);
2761 End Loop;
2762
2763 If l_line_payment_tbl.count > 0 Then
2764
2765 -- set control record
2766 l_control_rec.controlled_operation := TRUE;
2767 l_control_rec.write_to_DB := TRUE;
2768 l_control_rec.change_attributes := TRUE;
2769 l_control_rec.default_attributes := TRUE;
2770 l_control_rec.validate_entity := TRUE;
2771 l_control_rec.clear_dependents := TRUE;
2772
2773 l_control_rec.process := FALSE;
2774 l_control_rec.clear_api_cache := FALSE;
2775 l_control_rec.clear_api_requests := FALSE;
2776
2777 IF l_debug_level > 0 THEN
2778 oe_debug_pub.add('OEXVPPYB: before OE_Order_PVT.Line_Payments',3);
2779 END IF;
2780
2781 OE_Order_PVT.Line_Payments
2782 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
2783 , p_init_msg_list => FND_API.G_TRUE
2784 , p_control_rec => l_control_rec
2785 , p_x_Line_Payment_tbl => l_Line_Payment_tbl
2786 , p_x_old_Line_Payment_tbl => l_x_old_Line_Payment_tbl
2787 , x_return_Status => l_return_status
2788 );
2789
2790 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2791 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2792 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2793 RAISE FND_API.G_EXC_ERROR;
2794 END IF;
2795
2796 End If;
2797
2798 -- bug 5204275
2799 IF Oe_Payment_Trxn_Util.Get_CC_Security_Code_Use = 'REQUIRED'
2800 AND l_is_credit_card = 'Y' THEN
2801 FND_MESSAGE.SET_NAME('ONT','OE_CC_CVV2_REQD_FOR_SPLIT');
2802 OE_Msg_Pub.Add;
2803 END IF;
2804
2805 I := l_line_payment_tbl.FIRST;
2806 WHILE I IS NOT NULL LOOP
2807 IF Oe_Payment_Trxn_Util.Get_CC_Security_Code_Use = 'REQUIRED'
2808 AND l_line_payment_tbl(i).credit_card_approval_code = 'CVV2_REQUIRED' THEN
2809
2810 IF l_debug_level > 0 THEN
2811 oe_debug_pub.add('updating oe_payments. ',3);
2812 END IF;
2813
2814 UPDATE oe_payments
2815 SET credit_card_approval_code = 'CVV2_REQUIRED'
2816 WHERE line_id = l_line_payment_tbl(i).line_id
2817 AND header_id = l_line_payment_tbl(i).header_id;
2818 END IF;
2819
2820 I := l_line_payment_tbl.NEXT(I);
2821 END LOOP;
2822
2823
2824 IF l_debug_level > 0 THEN
2825 oe_debug_pub.add('Exiting OE_Prepayment_PVT.split_payment ', 1);
2826 END IF;
2827
2828 EXCEPTION
2829
2830 WHEN FND_API.G_EXC_ERROR THEN
2831 x_return_status := FND_API.G_RET_STS_ERROR;
2832
2833 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2835 oe_debug_pub.add(G_PKG_NAME||':split_payment :'||SQLERRM);
2836
2837 WHEN OTHERS THEN
2838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2839 If FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) Then
2840 OE_MSG_PUB.Add_Exc_Msg
2841 ( G_PKG_NAME
2842 , 'Split_Payment '
2843 );
2844 End If;
2845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2846
2847 END Split_Payment;
2848
2849 PROCEDURE Process_Payment_Assurance
2850 (p_api_version_number IN NUMBER
2851 ,p_line_id IN NUMBER
2852 ,p_activity_id IN NUMBER
2853 ,p_exists_prepay IN VARCHAR2 DEFAULT 'Y' --pnpl
2854 ,x_result_out OUT NOCOPY VARCHAR2
2855 ,x_return_status OUT NOCOPY VARCHAR2
2856 ,x_msg_count OUT NOCOPY VARCHAR2
2857 ,x_msg_data OUT NOCOPY VARCHAR2
2858 ) IS
2859
2860 Type ReceiptsCurType IS REF CURSOR;
2861 ar_receipts_cur ReceiptsCurType;
2862
2863 /**
2864 CURSOR AR_RECEIPTS_CUR(p_payment_set_id IN NUMBER) IS
2865 SELECT rc.creation_status creation_status,
2866 nvl(rm.payment_type_code, 'CHECK') payment_type_code
2867 FROM ar_cash_receipts cr,
2868 ar_receipt_classes rc,
2869 ar_receipt_methods rm,
2870 ar_receivable_applications ar
2871 WHERE rm.receipt_class_id = rc.receipt_class_id
2872 AND cr.receipt_method_id = rm.receipt_method_id
2873 AND cr.cash_receipt_id = ar.cash_receipt_id
2874 AND ar.display = 'Y'
2875 AND ar.applied_payment_schedule_id = -7
2876 AND ar.payment_set_id = p_payment_set_id;
2877 **/
2878
2879 l_line_payment_tbl OE_ORDER_PUB.Line_Payment_Tbl_Type;
2880 l_receipt_status VARCHAR2(30);
2881 l_payment_type_code VARCHAR2(30);
2882 l_payment_set_id NUMBER;
2883 l_payment_not_assured VARCHAR2(1) := 'N';
2884 l_return_status VARCHAR2(2000);
2885 l_sql_stmt VARCHAR2(2000);
2886
2887 --pnpl start
2888 l_prepaid_total NUMBER;
2889 l_pay_now_subtotal NUMBER;
2890 l_pay_now_tax NUMBER;
2891 l_pay_now_charges NUMBER;
2892 l_pay_now_total NUMBER;
2893 l_pay_now_commitment NUMBER;
2894 l_header_id NUMBER;
2895 l_exists_prepay BOOLEAN;
2896 l_trxn_extension_id NUMBER;
2897 l_exists_auth VARCHAR2(1);
2898 --pnpl end
2899
2900 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2901
2902 BEGIN
2903 IF l_debug_level > 0 THEN
2904 oe_debug_pub.add('Entering OE_Prepayment_PVT.Process_Payment_Assurance for line: '||p_line_id, 1);
2905 END IF;
2906
2907 x_return_status := FND_API.G_RET_STS_SUCCESS;
2908 x_result_out := 'COMPLETE:COMPLETE';
2909
2910 --pnpl start
2911 IF nvl(p_exists_prepay,'N') = 'Y' THEN
2912 l_exists_prepay := TRUE;
2913 IF l_debug_level > 0 THEN
2914 oe_debug_pub.add('There is prepayment for this order');
2915 END IF;
2916 ELSE
2917 l_exists_prepay := FALSE;
2918 END IF;
2919
2920
2921 IF OE_PREPAYMENT_UTIL.Get_Installment_Options = 'ENABLE_PAY_NOW' AND
2922 OE_PREPAYMENT_UTIL.Is_Pay_Now_Line(p_line_id) THEN
2923
2924 IF l_debug_level > 0 THEN
2925 oe_debug_pub.add('Installment Options is ENABLE_PAY_NOW');
2926 oe_debug_pub.add('This is a pay now line');
2927 END IF;
2928
2929 BEGIN
2930 SELECT header_id
2931 INTO l_header_id
2932 FROM oe_order_lines_all
2933 WHERE line_id = p_line_id;
2934 EXCEPTION
2935 WHEN OTHERS THEN
2936 IF l_debug_level > 0 THEN
2937 oe_debug_pub.add('unable to get header_id.. raising unexpected error');
2938 END IF;
2939 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2940 END;
2941
2942 IF l_exists_prepay THEN
2943 IF l_debug_level > 0 THEN
2944 oe_debug_pub.add('Checking if there is enough prepayment to cover the pay now total for the order');
2945 END IF;
2946
2947 BEGIN
2948 SELECT sum(nvl(payment_amount, 0))
2949 INTO l_prepaid_total
2950 FROM oe_payments op
2951 WHERE op.payment_collection_event = 'PREPAY'
2952 AND op.header_id = l_header_id;
2953 EXCEPTION
2954 WHEN NO_DATA_FOUND THEN
2955 l_prepaid_total := 0;
2956 END;
2957
2958 IF l_debug_level > 0 THEN
2959 oe_debug_pub.add('l_prepaid_total : ' || l_prepaid_total);
2960 END IF;
2961
2962 OE_Prepayment_PVT.Get_Pay_Now_Amounts
2963 (p_header_id => l_header_id
2964 ,p_line_id => null
2965 ,x_pay_now_subtotal => l_pay_now_subtotal
2966 ,x_pay_now_tax => l_pay_now_tax
2967 ,x_pay_now_charges => l_pay_now_charges
2968 ,x_pay_now_total => l_pay_now_total
2969 ,x_pay_now_commitment => l_pay_now_commitment
2970 ,x_msg_count => x_msg_count
2971 ,x_msg_data => x_msg_data
2972 ,x_return_status => l_return_status
2973 );
2974
2975 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2976 l_pay_now_total := 0;
2977 END IF;
2978
2979 IF l_debug_level > 0 THEN
2980 oe_debug_pub.add('l_pay_now_total : ' || l_pay_now_total);
2981 END IF;
2982
2983 IF l_prepaid_total >= l_pay_now_total OR
2984 l_pay_now_total = 0 THEN
2985 IF l_debug_level > 0 THEN
2986 oe_debug_pub.add('prepaid total covers the pay now total.. proceeding to Payment Assurance check');
2987 END IF;
2988 goto PAYMENT_ASSURANCE;
2989 END IF;
2990 END IF;
2991 --pnpl CC encryption changes (have modified the code to find out whether valid authorization exists for credit card invoice payments)
2992 --check if there is an invoice payment (credit card payments should have been authorized) at the line level or the order level.
2993 BEGIN
2994 --check if there exists a line level invoice payment
2995 SELECT payment_type_code, trxn_extension_id
2996 INTO l_payment_type_code, l_trxn_extension_id
2997 FROM oe_payments
2998 WHERE line_id = p_line_id
2999 AND header_id = l_header_id --To avoid FTS on oe_payments table (SQL ID 14882779)
3000 AND nvl(payment_type_code, 'COMMITMENT') <> 'COMMITMENT';
3001
3002 EXCEPTION
3003 WHEN NO_DATA_FOUND THEN
3004 --check if there is a header level invoice payment
3005 IF l_debug_level > 0 THEN
3006 oe_debug_pub.add('No line level invoice payment. Checking if there is a header level invoice payment');
3007 END IF;
3008
3009 BEGIN
3010 SELECT payment_type_code, trxn_extension_id
3011 INTO l_payment_type_code, l_trxn_extension_id
3012 FROM oe_payments
3013 WHERE header_id = l_header_id
3014 AND line_id IS NULL
3015 AND nvl(payment_collection_event, 'PREPAY') = 'INVOICE'
3016 AND nvl(payment_type_code, 'COMMITMENT') <> 'COMMITMENT';
3017
3018 EXCEPTION
3019 WHEN NO_DATA_FOUND THEN
3020 l_payment_type_code := null;
3021 l_trxn_extension_id := null;
3022 END;
3023 END;
3024
3025 l_exists_auth := 'N';
3026
3027 IF l_trxn_extension_id IS NOT NULL AND
3028 l_payment_type_code = 'CREDIT_CARD' THEN
3029 BEGIN
3030 /*
3031 SELECT 'Y'
3032 INTO l_exists_auth
3033 FROM IBY_TRXN_EXT_AUTHS_V
3034 WHERE trxn_extension_id = l_trxn_extension_id
3035 AND authorization_status=0;
3036 */
3037
3038 -- for performance reason, replace the above sql to join to base table
3039 -- instead of the view.
3040 SELECT 'Y'
3041 INTO l_exists_auth
3042 FROM IBY_FNDCPT_TX_OPERATIONS o,
3043 IBY_TRXN_SUMMARIES_ALL a
3044 WHERE o.trxn_extension_id = l_trxn_extension_id
3045 AND o.transactionid = a.transactionid
3046 AND a.status=0;
3047 EXCEPTION
3048 WHEN NO_DATA_FOUND THEN
3049 l_exists_auth := 'N';
3050 END;
3051 END IF;
3052
3053 -- set the status to COMPLETE if there exists payment record and
3054 -- payment should have been authorized if it is a credit card, otherwise set to incomplete.
3055 IF l_debug_level > 0 THEN
3056 oe_debug_pub.add('l_payment_type_code : ' || l_payment_type_code);
3057 oe_debug_pub.add('l_trxn_extension_id : ' || l_trxn_extension_id);
3058 oe_debug_pub.add('l_exists_auth : ' || l_exists_auth);
3059 END IF;
3060
3061
3062 IF nvl(l_payment_type_code, 'COMMITMENT') <> 'COMMITMENT' AND
3063 NOT (nvl(l_payment_type_code, 'COMMITMENT') = 'CREDIT_CARD' AND
3064 l_exists_auth = 'N') THEN
3065 oe_debug_pub.add('There exists an invoice payment');
3066 IF NOT l_exists_prepay THEN
3067 x_result_out := 'COMPLETE:COMPLETE';
3068
3069 OE_Order_WF_Util.Update_Flow_Status_Code
3070 (p_line_id => p_line_id
3071 ,p_flow_status_code => 'PAYMENT_ASSURANCE_COMPLETE'
3072 ,x_return_status => l_return_status);
3073
3074 IF l_debug_level > 0 THEN
3075 oe_debug_pub.add('Return status 1 from update_flow_status_code for pay now instrument check: '||l_return_status , 3 ) ;
3076 END IF;
3077 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3078 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3079 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3080 RAISE FND_API.G_EXC_ERROR;
3081 END IF;
3082
3083 IF l_debug_level > 0 THEN
3084 oe_debug_pub.add('Returning from the procedure as there is no prepayment record for the order');
3085 END IF;
3086 RETURN;
3087 ELSE
3088 IF l_debug_level > 0 THEN
3089 oe_debug_pub.add('Proceeding to the payment assurance check as there is prepayment for the order');
3090 END IF;
3091 goto PAYMENT_ASSURANCE;
3092 END IF;
3093
3094 ELSE
3095
3096 IF l_debug_level > 0 THEN
3097 oe_debug_pub.add('No Pay now payment instrument. setting x_result_out to INCOMPLETE and returning from the procedure');
3098 END IF;
3099 --bug4950878
3100 fnd_message.Set_Name('ONT', 'ONT_PAYNOW_PMT_NOT_ASSURED');
3101 oe_msg_pub.add;
3102
3103 x_result_out := 'COMPLETE:INCOMPLETE';
3104 OE_Order_WF_Util.Update_Flow_Status_Code
3105 (p_line_id => p_line_id
3106 ,p_flow_status_code => 'PAY_NOW_PAYMENT_NOT_ASSURED'
3107 ,x_return_status => l_return_status);
3108
3109 IF l_debug_level > 0 THEN
3110 oe_debug_pub.add('Return status 2 from update_flow_status_code for pay now instrument check: '||l_return_status , 3 ) ;
3111 END IF;
3112 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3114 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3115 RAISE FND_API.G_EXC_ERROR;
3116 END IF;
3117
3118 x_return_status := l_return_status;
3119 RETURN;
3120 END IF;
3121
3122 ELSE
3123 IF NOT l_exists_prepay THEN
3124 IF l_debug_level > 0 THEN
3125 oe_debug_pub.add('In procedure Process_Payment_Assurance: line not eligible for payment assurance');
3126 END IF;
3127 x_result_out := 'COMPLETE:NOT_ELIGIBLE';
3128 RETURN;
3129 END IF;
3130
3131 END IF; -- Installment Options and Pay Now Line
3132
3133 --pnpl end
3134
3135 <<PAYMENT_ASSURANCE>>
3136 -- Bug 7757937
3137 BEGIN
3138 SELECT payment_set_id
3139 INTO l_payment_set_id
3140 FROM oe_payments op
3141 ,oe_order_lines_all ool
3142 WHERE op.header_id = ool.header_id
3143 AND ool.line_id = p_line_id
3144 AND op.payment_collection_event = 'PREPAY'
3145 AND rownum = 1;
3146 EXCEPTION WHEN NO_DATA_FOUND THEN
3147 null;
3148 END;
3149 -- Bug 7757937
3150
3151 -- to check payment assurance for the payment_set_id.
3152 -- set the status to 'complete:incomplete' so that process control
3153 -- will go to Eligible block and retry later.
3154 IF l_payment_set_id IS NOT NULL THEN
3155
3156 IF l_debug_level > 0 THEN
3157 oe_debug_pub.add('Checking payment assurance for payment_set_id: '||l_payment_set_id,3);
3158 END IF;
3159
3160 -- if there exists one receipt that is neither REMITTED nor CLEARED.
3161 --bug5394265 Need to check that receipts other than those for Credit Cards need to be 'CLEARED' for the payment to be assured.
3162 l_sql_stmt := '
3163 SELECT ''Y''
3164 FROM dual
3165 WHERE
3166 (
3167 EXISTS
3168 (
3169 SELECT distinct ra.cash_receipt_id, crh.status
3170 FROM AR_CASH_RECEIPT_HISTORY crh , ar_cash_receipts cr, ar_receivable_applications ra
3171 WHERE crh.cash_receipt_id = ra.cash_receipt_id
3172 AND crh.cash_receipt_id = cr.cash_receipt_id
3173 AND ra.payment_set_id = :payment_set_id
3174 AND ((cr.payment_trxn_extension_id IS NOT NULL AND
3175 EXISTS (SELECT ''1''
3176 FROM IBY_PMT_INSTR_USES_ALL ipiu,
3177 IBY_FNDCPT_TX_EXTENSIONS ifte
3178 WHERE ipiu.instrument_payment_use_id = ifte.instr_assignment_id
3179 AND ifte.trxn_extension_id = cr.payment_trxn_extension_id AND
3180 ((ipiu.instrument_type = ''CREDITCARD'' AND
3181 crh.status NOT IN (''REMITTED'', ''CLEARED'')) OR
3182 (ipiu.instrument_type <> ''CREDITCARD'' AND
3183 crh.status NOT IN (''CLEARED''))))) OR
3184 (cr.payment_trxn_extension_id IS NULL AND
3185 crh.status <> ''CLEARED''))
3186 AND nvl(current_record_flag, ''N'') = ''Y''
3187 )
3188 )';
3189
3190 BEGIN
3191 EXECUTE IMMEDIATE l_sql_stmt
3192 INTO l_payment_not_assured
3193 USING l_payment_set_id ;
3194 EXCEPTION
3195 WHEN NO_DATA_FOUND THEN
3196 l_payment_not_assured := 'N';
3197 END;
3198
3199 IF l_debug_level > 0 THEN
3200 oe_debug_pub.add('payment_set_id is: '||l_payment_set_id,3);
3201 oe_debug_pub.add('l_payment_not_assured flag is: '||l_payment_not_assured,3);
3202 END IF;
3203
3204 IF l_payment_not_assured = 'Y' THEN
3205 x_result_out := 'COMPLETE:INCOMPLETE';
3206 ELSE
3207 x_result_out := 'COMPLETE:COMPLETE';
3208 END IF;
3209
3210 ELSE
3211 -- payment set id is null, fund has been collected yet.
3212 x_result_out := 'COMPLETE:INCOMPLETE';
3213 END IF;
3214
3215 IF x_result_out = 'COMPLETE:COMPLETE' THEN
3216 OE_Order_WF_Util.Update_Flow_Status_Code
3217 (p_line_id => p_line_id,
3218 p_flow_status_code => 'PAYMENT_ASSURANCE_COMPLETE',
3219 x_return_status => l_return_status);
3220
3221 IF l_debug_level > 0 THEN
3222 oe_debug_pub.add('Return status 1 from update_flow_status_code: '||l_return_status , 3 ) ;
3223 END IF;
3224 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3225 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3226 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3227 RAISE FND_API.G_EXC_ERROR;
3228 END IF;
3229 ELSIF x_result_out = 'COMPLETE:INCOMPLETE' THEN
3230 --bug4950878
3231 fnd_message.Set_Name('ONT', 'ONT_PAYMENT_NOT_ASSURED');
3232 oe_msg_pub.add;
3233
3234 OE_Order_WF_Util.Update_Flow_Status_Code
3235 (p_line_id => p_line_id,
3236 p_flow_status_code => 'PAYMENT_ASSURANCE_NOT_ASSURED',
3237 x_return_status => l_return_status);
3238
3239 IF l_debug_level > 0 THEN
3240 oe_debug_pub.add('Return status 2 from update_flow_status_code: '||l_return_status , 3 ) ;
3241 END IF;
3242 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3244 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3245 RAISE FND_API.G_EXC_ERROR;
3246 END IF;
3247 END IF;
3248
3249 x_return_status := l_return_status;
3250
3251 IF l_debug_level > 0 THEN
3252 oe_debug_pub.add( 'Process_Payment_Assurance , result_out is: '||x_result_out, 3);
3253 oe_debug_pub.add('Exiting OE_Prepayment_PVT.Process_Payment_Assurance for line: '||p_line_id, 1);
3254 END IF;
3255
3256 EXCEPTION
3257 WHEN FND_API.G_EXC_ERROR THEN
3258 x_return_status := FND_API.G_RET_STS_ERROR;
3259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3261 WHEN OTHERS THEN
3262 IF l_debug_level > 0 THEN
3263 oe_debug_pub.add( 'ORACLE ERROR: ' || SQLERRM , 1 ) ;
3264 END IF;
3265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3266 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3267 THEN
3268 OE_MSG_PUB.Add_Exc_Msg
3269 ( G_PKG_NAME
3270 , 'Payment Assurance'
3271 );
3272 END IF;
3273
3274 END Process_Payment_Assurance;
3275
3276 Procedure Print_Payment_Receipt(p_header_id in Number,
3277 x_result_out out NOCOPY /* file.sql.39 change */ varchar2,
3278 x_return_status out NOCOPY /* file.sql.39 change */ varchar2)
3279 is
3280
3281 l_organization_id Number;
3282 l_sob_id Number;
3283 l_new_request_id Number;
3284 errbuf Varchar2(200);
3285 l_request_id Number;
3286 l_order_source_id NUMBER;
3287 l_orig_sys_document_ref VARCHAR2(50);
3288 l_change_sequence VARCHAR2(50);
3289 l_source_document_type_id NUMBER;
3290 l_source_document_id NUMBER;
3291
3292 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3293
3294 begin
3295 If l_debug_level > 0 Then
3296 oe_debug_pub.add('entering OE_PrePayment_PVT.print_payment_receipt');
3297 End If;
3298 x_return_status := FND_API.G_RET_STS_SUCCESS;
3299 x_result_out := 'COMPLETE:COMPLETE';
3300 select order_source_id, orig_sys_document_ref, change_sequence
3301 ,source_document_type_id, source_document_id
3302 into l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
3303 l_source_document_type_id, l_source_document_id
3304 from oe_order_headers where header_id = p_header_id;
3305 -- Set message context
3306 OE_MSG_PUB.set_msg_context(
3307 p_entity_code => 'HEADER'
3308 ,p_entity_id => p_header_id
3309 ,p_header_id => p_header_id
3310 ,p_order_source_id => l_order_source_id
3311 ,p_orig_sys_document_ref => l_orig_sys_document_ref
3312 ,p_change_sequence => l_change_sequence
3313 ,p_source_document_type_id => l_source_document_type_id
3314 ,p_source_document_id => l_source_document_id);
3315
3316 select to_number(oe_sys_parameters.value ('SET_OF_BOOKS_ID')) into l_sob_id from dual;
3317
3318 -- MOAC changes
3319 -- select fnd_profile.value('ORG_ID') into l_organization_id from DUAL;
3320 SELECT org_id
3321 INTO l_organization_id
3322 FROM oe_order_headers_all
3323 WHERE header_id = p_header_id;
3324
3325 FND_REQUEST.set_org_id(l_organization_id);
3326
3327 l_new_request_id := FND_REQUEST.SUBMIT_REQUEST('ONT','OEXPMTRC',
3328 null,null,FALSE,l_sob_id,l_organization_id,NULL,NULL,p_header_id,
3329 chr(0));
3330 If l_debug_level > 0 Then
3331 oe_debug_pub.add('l_new_request_id = '||l_new_request_id);
3332 End If;
3333 FND_MESSAGE.SET_NAME('ONT','ONT_CONCURRENT_REQUEST_ID');
3334 fnd_message.set_token('REQUEST_ID',l_new_request_id);
3335 OE_MSG_PUB.Add;
3336
3337 IF (l_new_request_id = 0) THEN
3338 errbuf := FND_MESSAGE.GET;
3339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3340 END IF;
3341
3342 EXCEPTION
3343
3344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3345 If l_debug_level > 0 Then
3346 oe_debug_pub.add( 'UNEXPECTED ERROR. EXITING FROM PAYMENT RECEIPT: '||SQLERRM , 1 ) ;
3347 End If;
3348 oe_msg_pub.Add_Text(errbuf);
3349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3350
3351 WHEN OTHERS THEN
3352 If l_debug_level > 0 Then
3353 oe_debug_pub.add( 'EXCEPTION , OTHERS. EXITING FROM PAYMENT RECEIPT: '||SQLERRM , 1 ) ;
3354 End If;
3355 IF FND_MSG_PUB.Check_Msg_Level
3356 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3357 THEN
3358 OE_MSG_PUB.Add_Exc_Msg
3359 ( G_PKG_NAME
3360 , 'Print_Payment_Receipt'
3361 );
3362 END IF;
3363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3364
3365 END Print_Payment_Receipt;
3366
3367 --R12 CC Encryption
3368 Procedure Delete_Payments
3369 ( p_line_id IN NUMBER
3370 , p_header_id IN NUMBER
3371 , p_invoice_to_org_id IN NUMBER
3372 , x_return_status OUT NOCOPY VARCHAR2
3373 , x_msg_count OUT NOCOPY NUMBER
3374 , x_msg_data OUT NOCOPY VARCHAR2
3375 ) IS
3376 l_trxn_Extension_id NUMBER;
3377 l_payment_type_code VARCHAR2(80);
3378 l_return_status VARCHAR2(30);
3379 l_msg_count number := 0;
3380 l_msg_data VARCHAR2(2000) := NULL;
3381 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3382
3383 --Deleting the prepayment records of credit card payments
3384 --alone as the invoice payments of credit cards would be
3385 --deleted in the delete row procedure of Oe_header_payment_util
3386 --bug 4885313
3387 CURSOR header_payments IS
3388 SELECT payment_number, payment_type_code,
3389 trxn_extension_id --R12 CC Encryption
3390 FROM OE_PAYMENTS
3391 WHERE
3392 (
3393 HEADER_ID = p_header_id
3394 AND line_id is NULL
3395 AND payment_type_code IN ('ACH','DIRECT_DEBIT')
3396 ) OR
3397 (
3398 HEADER_ID = p_header_id AND line_id is null
3399 and payment_type_code = 'CREDIT_CARD'
3400 AND payment_collection_event = 'PREPAY'
3401 );
3402
3403 CURSOR line_payments IS
3404 SELECT payment_number,payment_type_code,
3405 trxn_extension_id
3406 FROM oe_payments
3407 where header_id = p_header_id
3408 and line_id = p_line_id
3409 AND payment_type_code IN ('CREDIT_CARD','ACH','DIRECT_DEBIT');
3410
3411 BEGIN
3412
3413 x_return_status := FND_API.G_RET_STS_SUCCESS;
3414
3415 IF l_debug_level > 0 THEN
3416 oe_debug_pub.add('Entering OEXVPPYB Delete_Payments');
3417 END IF;
3418
3419 IF p_line_id IS NOT NULL THEN
3420 FOR line_payments_rec IN line_payments
3421 LOOP
3422 IF line_payments_rec.trxn_extension_id is not null
3423 THEN
3424
3425 IF l_debug_level > 0 THEN
3426 oe_debug_pub.add('Header_id in delete payments'||p_header_id);
3427 oe_debug_pub.add('line id'||p_line_id);
3428 oe_debug_pub.add('payment number'||line_payments_rec.payment_number);
3429 oe_debug_pub.add('trxn extn id'||line_payments_rec.trxn_extension_id);
3430 END IF;
3431
3432 OE_PAYMENT_TRXN_UTIL.Delete_Payment_Trxn
3433 (p_header_id => p_header_id,
3434 p_line_id => p_line_id,
3435 p_payment_number => line_payments_rec.payment_number,
3436 P_site_use_id => p_invoice_to_org_id,
3437 p_trxn_extension_id => line_payments_rec.trxn_extension_id,
3438 x_return_status =>l_return_status,
3439 x_msg_count => x_msg_count,
3440 x_msg_data => x_msg_data);
3441
3442
3443 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3444 RAISE FND_API.G_EXC_ERROR;
3445 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3447 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3448 DELETE FROM OE_PAYMENTS
3449 WHERE HEADER_ID = p_header_id
3450 AND LINE_ID = p_line_id
3451 AND payment_number = line_payments_rec.payment_number;
3452 END IF;
3453 END IF;
3454 END LOOP;
3455
3456 ELSE
3457 FOR header_payments_rec IN header_payments
3458 LOOP
3459 IF header_payments_rec.trxn_extension_id is not null
3460 THEN
3461
3462 IF l_debug_level > 0 THEN
3463 oe_debug_pub.add('Header_id in delete payments inside header payments cursor'||p_header_id);
3464 oe_debug_pub.add('line id'||p_line_id);
3465 oe_debug_pub.add('payment number'||header_payments_rec.payment_number);
3466 oe_debug_pub.add('trxn extn id'||header_payments_rec.trxn_extension_id);
3467 END IF;
3468
3469 OE_PAYMENT_TRXN_UTIL.Delete_Payment_Trxn
3470 (p_header_id => p_header_id,
3471 p_line_id => null,
3472 p_payment_number => header_payments_rec.payment_number,
3473 P_site_use_id => p_invoice_to_org_id,
3474 p_trxn_extension_id => header_payments_rec.trxn_extension_id,
3475 x_return_status => l_return_status,
3476 x_msg_count => x_msg_count,
3477 x_msg_data => x_msg_data);
3478
3479 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3480 RAISE FND_API.G_EXC_ERROR;
3481 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3483 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3484 DELETE FROM OE_PAYMENTS
3485 WHERE HEADER_ID = p_header_id
3486 AND payment_number = header_payments_rec.payment_number
3487 AND line_id is null;
3488 --Need to update oe_order headers table with
3489 --null payment type code as it has been deleted from
3490 --oe_payments table.
3491 Update oe_order_headers_all set
3492 payment_type_code = null where
3493 header_id = p_header_id;
3494 IF l_debug_level > 0 THEN
3495 oe_debug_pub.add('Header id...after updating oe order headers all'||p_header_id);
3496 END IF;
3497 x_return_status := FND_API.G_RET_STS_SUCCESS;
3498 END IF;
3499 END IF;
3500 END LOOP;
3501 END IF;
3502
3503 EXCEPTION
3504
3505 WHEN FND_API.G_EXC_ERROR THEN
3506 x_return_status := FND_API.G_RET_STS_ERROR;
3507 OE_MSG_PUB.Count_And_Get
3508 ( p_count => x_msg_count,
3509 p_data => x_msg_data
3510 );
3511
3512 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3514 IF l_debug_level > 0 THEN
3515 oe_debug_pub.add( 'Unexpected error in Delete_Payments: ' || SQLERRM , 3 ) ;
3516 END IF;
3517 OE_MSG_PUB.Count_And_Get
3518 ( p_count => x_msg_count,
3519 p_data => x_msg_data
3520 );
3521
3522 WHEN OTHERS THEN
3523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3524 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3525 THEN
3526 FND_MSG_PUB.Add_Exc_Msg
3527 ( G_PKG_NAME
3528 , 'Delete_Payments'
3529 );
3530 END IF;
3531
3532 IF l_debug_level > 0 THEN
3533 oe_debug_pub.add( 'Oracle error in others in delete_payments: '||SQLERRM , 3 ) ;
3534 END IF;
3535
3536 OE_MSG_PUB.Count_And_Get
3537 ( p_count => x_msg_count,
3538 p_data => x_msg_data
3539 );
3540 END Delete_Payments;
3541 --R12 CC Encryption
3542
3543 Procedure Update_Hdr_Payment(p_header_id in number,
3544 p_action in varchar2,
3545 p_line_id in number,
3546 x_return_status out nocopy varchar2,
3547 x_msg_count out nocopy number,
3548 x_msg_data out nocopy varchar2) is
3549
3550 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
3551 l_msg_count number := 0;
3552 l_msg_data varchar2(2000) := NULL;
3553 l_del_payment number := 0; -- 0 means do not delete, 1 means delete.
3554
3555 cursor payment_count is
3556 select count(payment_type_code)
3557 from oe_payments
3558 where header_id = p_header_id
3559 and line_id is null;
3560
3561 cursor payment_cur is
3562 select payment_type_code,
3563 payment_amount,
3564 check_number
3565 /*credit_card_code,
3566 credit_card_holder_name,
3567 credit_card_number,
3568 credit_card_expiration_date,
3569 credit_card_approval_code*/ --R12 CC Encryption
3570 from oe_payments
3571 where header_id = p_header_id
3572 and payment_collection_event = 'INVOICE'
3573 and line_id is null;
3574
3575 cursor prepay_cur is
3576 select payment_type_code,
3577 payment_amount,
3578 check_number
3579 /*credit_card_code,
3580 credit_card_holder_name,
3581 credit_card_number,
3582 credit_card_expiration_date,
3583 credit_card_approval_code*/ --R12 CC Encryption
3584 from oe_payments
3585 where header_id = p_header_id
3586 and ( payment_collection_event = 'PREPAY'
3587 or prepaid_amount is not null )
3588 and line_id is null;
3589
3590 cursor prepay_count is
3591 select count(payment_type_code)
3592 from oe_payments
3593 where header_id = p_header_id
3594 and (payment_collection_event = 'PREPAY'
3595 or prepaid_amount is not null )
3596 and line_id is null;
3597
3598 /*
3599 and exists ( select null
3600 from ra_terms rat, oe_order_headers_all oeh
3601 where oeh.header_id = p_header_id
3602 and oeh.payment_term_id = rat.term_id
3603 and rat.prepayment_flag = 'Y' );
3604 */
3605
3606 cursor header_payment_cur is
3607 select payment_type_code,
3608 payment_amount,
3609 check_number,
3610 /*credit_card_code,
3611 credit_card_holder_name,
3612 credit_card_number,
3613 credit_card_expiration_date,
3614 credit_card_approval_code,
3615 credit_card_approval_date, --bug3906851 */ --R12 CC Encryption
3616 payment_term_id,
3617 transactional_curr_code
3618 from oe_order_headers_all
3619 where header_id = p_header_id;
3620
3621 l_payment_type_code varchar2(30) := NULL;
3622 l_payment_amount NUMBER := NULL;
3623 l_check_number varchar2(50) := NULL;
3624 l_credit_card_code varchar2(80) := NULL;
3625 l_credit_card_holder_name varchar2(80) := NULL;
3626 l_credit_card_number varchar2(80) := NULL;
3627 l_credit_card_approval_code varchar2(80) := NULL;
3628 l_credit_card_expiration_date DATE := NULL;
3629 p_payment_type_code varchar2(30) := NULL;
3630 p_payment_amount NUMBER := NULL;
3631 p_check_number varchar2(50) := NULL;
3632 p_credit_card_code varchar2(80) := NULL;
3633 p_credit_card_holder_name varchar2(80) := NULL;
3634 p_credit_card_number varchar2(80) := NULL;
3635 p_credit_card_approval_code varchar2(80) := NULL;
3636 p_credit_card_approval_date DATE := NULL; --bug3906851
3637 p_credit_card_expiration_date DATE := NULL;
3638 p_count number := 0;
3639 p_payment_event varchar2(30) := 'INVOICE';
3640 l_prepay_count number := 0;
3641 p_payment_term_id number;
3642 l_downpayment number;
3643 l_prepayment_flag varchar2(1) := NULL;
3644 p_currency_code varchar2(30) := NULL;
3645 l_order_total number;
3646 l_subtotal number;
3647 l_discount number;
3648 l_charges number;
3649 l_tax number;
3650 l_lock_line_id number;
3651 line_payment_type varchar2(30) := NULL;
3652
3653
3654 l_control_rec OE_GLOBALS.Control_Rec_Type;
3655 l_x_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
3656 l_x_Old_Header_Payment_tbl OE_Order_PUB.Header_Payment_Tbl_Type;
3657 l_commitment_id number;
3658 line_commitment_id number;
3659 l_pmt_changed varchar2(1) := 'N';
3660 --bug3733877 start
3661 l_invoice_payment_exists VARCHAR2(1) := 'N';
3662 l_max_payment_number NUMBER := 0;
3663 --bug3733877 end
3664 --bug3781675 start
3665 l_inv_payment_number NUMBER;
3666 l_old_header_rec OE_Order_Pub.Header_Rec_Type;
3667 l_header_rec OE_Order_Pub.Header_Rec_Type;
3668 l_index NUMBER;
3669 --bug3781675 end
3670
3671 --bug3906851
3672 l_trxn_id NUMBER;
3673
3674 cursor line_payments is
3675 select payment_type_code, payment_trx_id
3676 from oe_payments
3677 where line_id = l_lock_line_id
3678 and header_id = p_header_id;
3679
3680 --bug3733877
3681 --Cursor to find out if there is an invoice payment in oe_payments
3682 cursor invoice_payment_check is
3683 select 'Y',payment_number --bug3781675
3684 from oe_payments
3685 where header_id = p_header_id
3686 and line_id is null
3687 and nvl(payment_collection_event,'PREPAY') = 'INVOICE';
3688
3689 Begin
3690
3691 oe_debug_pub.add('entering update_hdr_payment ');
3692 x_return_status := l_return_status;
3693
3694 if p_action = 'UPDATE_LINE' then
3695
3696 if p_line_id is null then
3697
3698 x_return_status := FND_API.G_RET_STS_ERROR;
3699 oe_debug_pub.add('failed because line_id is null ');
3700 return;
3701
3702 else
3703
3704 Savepoint update_line;
3705
3706 select line_id, payment_type_code, commitment_id
3707 into l_lock_line_id, line_payment_type, line_commitment_id
3708 from oe_order_lines_all
3709 where line_id = p_line_id
3710 for update nowait;
3711
3712 if l_lock_line_id is null then
3713 x_return_status := FND_API.G_RET_STS_ERROR;
3714 oe_debug_pub.add('failed to lock the line with line_id : ' || l_lock_line_id);
3715 return;
3716 end if;
3717
3718 l_pmt_changed := 'N';
3719
3720 open line_payments;
3721 loop
3722 fetch line_payments
3723 into l_payment_type_code, l_commitment_id;
3724 exit when line_payments%notfound;
3725
3726 if l_payment_type_code = 'COMMITMENT'and
3727 l_commitment_id is not null and
3728 nvl(line_commitment_id, -1) <> l_commitment_id
3729 then
3730 line_commitment_id := l_commitment_id;
3731 l_pmt_changed := 'Y';
3732 elsif l_payment_type_code is not null and
3733 ( line_payment_type is null or
3734 line_payment_type <> l_payment_type_code)
3735 then
3736 line_payment_type := l_payment_type_code;
3737 l_pmt_changed := 'Y';
3738 end if;
3739
3740 end loop;
3741
3742 if l_pmt_changed = 'Y' then
3743 Begin
3744 update oe_order_lines_all
3745 set payment_type_code = line_payment_type,
3746 commitment_id = line_commitment_id
3747 where line_id = l_lock_line_id;
3748
3749 exception
3750 when others then
3751 x_return_status := FND_API.G_RET_STS_ERROR;
3752 oe_debug_pub.add('failed to update line payment type: ' || substr(sqlerrm,1,300));
3753 rollback to savepoint update_line;
3754 return;
3755 End;
3756 else
3757 rollback to savepoint update_line;
3758 end if; -- if l_pmt_changed
3759
3760 end if; -- if p_line_id is null
3761 return;
3762 end if; -- if p_action = 'UPDATE_LINE'
3763
3764
3765 /* get the count of records in oe_payments for this order header */
3766 p_count := 0;
3767
3768 open payment_count;
3769 fetch payment_count into p_count;
3770 close payment_count;
3771
3772 /* get the count of prepayment records in oe_payments */
3773 open prepay_count;
3774 fetch prepay_count into l_prepay_count;
3775 close prepay_count;
3776
3777 --bug3733877 start
3778 --Checking if there exists an invoice payment
3779 open invoice_payment_check;
3780 fetch invoice_payment_check into l_invoice_payment_exists, l_inv_payment_number; --bug3781675
3781 close invoice_payment_check;
3782 --bug3733877 end
3783
3784 /* get header payment info and payment_term_id */
3785 open header_payment_cur;
3786 fetch header_payment_cur into p_payment_type_code,
3787 p_payment_amount,
3788 p_check_number, --R12 CC Encryption
3789 /*p_credit_card_code,
3790 p_credit_card_holder_name,
3791 p_credit_card_number,
3792 p_credit_card_expiration_date,
3793 p_credit_card_approval_code,
3794 p_credit_card_approval_date, --bug3906851*/ --R12 CC Encryption
3795 p_payment_term_id,
3796 p_currency_code;
3797 close header_payment_cur;
3798
3799 --bug3733877 start
3800 BEGIN
3801 SELECT nvl(MAX(payment_number),0) INTO l_max_payment_number
3802 FROM oe_payments
3803 WHERE header_id = p_header_id
3804 AND line_id IS NULL;
3805 EXCEPTION
3806 WHEN OTHERS THEN
3807 l_max_payment_number := 0;
3808 END;
3809 --bug3733877 end
3810
3811 if p_payment_term_id is not null then
3812 oe_debug_pub.add('OEXVPPYB: term id : ' || p_payment_term_id);
3813
3814 l_prepayment_flag := AR_PUBLIC_UTILS.Check_Prepay_Payment_Term(p_payment_term_id);
3815
3816 oe_debug_pub.add('prepayment_flag is : ' || l_prepayment_flag );
3817 end if;
3818
3819
3820 IF p_action = 'UPDATE_HEADER' THEN
3821 --bug3733877 commenting the following condition and checking if invoice payment exists.
3822 --if (l_prepay_count = 1 and p_count = 1) then
3823 if nvl(l_invoice_payment_exists,'N') = 'N' then
3824 l_payment_type_code := null;
3825 l_payment_amount := null;
3826 l_check_number := null;
3827 l_credit_card_code := null;
3828 l_credit_card_holder_name := null;
3829 l_credit_card_number := null;
3830 l_credit_card_expiration_date := null;
3831 l_credit_card_approval_code := null;
3832 l_del_payment := 1; --delete payment info at the header
3833
3834 --bug3733877 commenting the following condition
3835 --elsif p_count > 0 then
3836 else
3837
3838 open payment_cur;
3839 loop
3840 oe_debug_pub.add('before payment_cur fetching ');
3841 fetch payment_cur into l_payment_type_code,
3842 l_payment_amount,
3843 l_check_number; --R12 CC Encryption
3844 /*l_credit_card_code,
3845 l_credit_card_holder_name,
3846 l_credit_card_number,
3847 l_credit_card_expiration_date,
3848 l_credit_card_approval_code ;*/ --R12 CC Encryption
3849 exit when payment_cur%NOTFOUND;
3850 oe_debug_pub.add('payment_type_code is : ' || l_payment_type_code );
3851 end loop; -- loop for payment_cur
3852 close payment_cur;
3853 --commenting the elsif part for bug3733877
3854 /* elsif p_count = 0 then
3855 return; */
3856
3857 end if; -- if nvl(l_invoice_payment_exists,'N') = 'N'
3858
3859 if l_payment_type_code is not null or l_del_payment = 1 then
3860 --bug3781675 start
3861
3862 -- Set up the Header record
3863 OE_Header_Util.Lock_Row
3864 (p_header_id => p_header_id
3865 ,p_x_header_rec => l_old_header_rec
3866 ,x_return_status => l_return_status
3867 );
3868 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3869 RAISE FND_API.G_EXC_ERROR;
3870 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3872 END IF;
3873
3874 l_header_rec := l_old_header_rec;
3875 l_header_rec.payment_type_code := l_payment_type_code;
3876 -- l_header_rec.payment_amount := l_payment_amount; --bug 5185139
3877 l_header_rec.check_number := l_check_number;
3878 l_header_rec.credit_card_code := l_credit_card_code;
3879 l_header_rec.credit_card_holder_name := l_credit_card_holder_name;
3880 l_header_rec.credit_card_number := l_credit_card_number;
3881 l_header_rec.credit_card_expiration_date := l_credit_card_expiration_date;
3882 l_header_rec.credit_card_approval_code := l_credit_card_approval_code;
3883 --bug3781675 end
3884
3885 oe_debug_pub.add('before updating oe_order_headers_all ');
3886 --oe_debug_pub.add('Credit card number'||l_credit_card_number);
3887 oe_debug_pub.add('Check number'||l_check_number);
3888 oe_debug_pub.add('PVIPRANA: l_del_payment is '|| l_del_payment);
3889
3890 update oe_order_headers_all
3891 set payment_type_code = l_payment_type_code,
3892 -- payment_amount = l_payment_amount, --bug 5185139
3893 check_number = l_check_number, --R12 CC Encryption
3894 /*credit_card_code = l_credit_card_code,
3895 credit_card_holder_name = l_credit_card_holder_name,
3896 credit_card_number = l_credit_card_number,
3897 credit_card_expiration_date = l_credit_card_expiration_date,
3898 credit_card_approval_code = l_credit_card_approval_code,*/ --R12 CC Encryption
3899 lock_control = lock_control + 1 --bug3781675
3900 where header_id = p_header_id;
3901
3902 oe_debug_pub.add('after updating oe_order_headers_all');
3903 --bug3781675 start
3904
3905 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110508' THEN
3906
3907 -- call notification framework to get header index position
3908 OE_ORDER_UTIL.Update_Global_Picture
3909 (p_Upd_New_Rec_If_Exists =>FALSE
3910 , p_header_rec => l_header_rec
3911 , p_old_header_rec => l_old_header_rec
3912 , p_header_id => l_header_rec.header_id
3913 , x_index => l_index
3914 , x_return_status => l_return_status);
3915
3916 oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FOR HDR IS: ' || L_RETURN_STATUS ) ;
3917 oe_debug_pub.add( 'HDR INDEX IS: ' || L_INDEX , 1 ) ;
3918
3919 IF l_index is not null then
3920 -- modify Global Picture
3921
3922 OE_ORDER_UTIL.g_header_rec.payment_type_code:=
3923 l_header_rec.payment_type_code;
3924 OE_ORDER_UTIL.g_header_rec.payment_amount:=
3925 l_header_rec.payment_amount;
3926 OE_ORDER_UTIL.g_header_rec.check_number:=
3927 l_header_rec.check_number;
3928 OE_ORDER_UTIL.g_header_rec.credit_card_code:=
3929 l_header_rec.credit_card_code;
3930 OE_ORDER_UTIL.g_header_rec.credit_card_holder_name:=
3931 l_header_rec.credit_card_holder_name;
3932 OE_ORDER_UTIL.g_header_rec.credit_card_number:=
3933 l_header_rec.credit_card_number;
3934 OE_ORDER_UTIL.g_header_rec.credit_card_expiration_date:=
3935 l_header_rec.credit_card_expiration_date;
3936 OE_ORDER_UTIL.g_header_rec.credit_card_approval_code:=
3937 l_header_rec.credit_card_approval_code;
3938 OE_ORDER_UTIL.g_header_rec.lock_control:=
3939 l_header_rec.lock_control;
3940
3941 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3942 RAISE FND_API.G_EXC_ERROR;
3943 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3944 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3945 END IF;
3946
3947 oe_debug_pub.add( 'OEXPVPMB: BEFORE CALLING PROCESS_REQUESTS_AND_NOTIFY' ) ;
3948
3949 OE_Order_PVT.Process_Requests_And_Notify
3950 ( p_process_requests => TRUE
3951 , p_notify => FALSE
3952 , p_header_rec => l_header_rec
3953 , p_old_header_rec => l_old_header_rec
3954 , x_return_status => l_return_status
3955 );
3956
3957 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3958 RAISE FND_API.G_EXC_ERROR;
3959 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3961 END IF;
3962
3963 END IF ; /* global entity index null check */
3964 END IF; --code_release_level check
3965 --bug3781675 end
3966
3967 end if; -- if l_payment_type_code is not null
3968
3969 elsif p_action in ('UPDATE_PAYMENT', 'ACTIONS_LINE_PAYMENTS', 'ACTIONS_PAYMENTS') then
3970
3971 if p_action = 'ACTIONS_LINE_PAYMENTS' then
3972
3973 if l_prepay_count > 0 then
3974
3975 oe_debug_pub.add('cannot do this as there exists prepayments ');
3976
3977 return;
3978 end if;
3979 end if; -- if p_action = 'ACTIONS_LINE_PAYMENTS'
3980
3981
3982 oe_debug_pub.add('p_count is : ' || p_count );
3983 oe_debug_pub.add('payment_type_code is : ' || nvl(p_payment_type_code, 'null' ));
3984
3985 if p_action = 'ACTIONS_PAYMENTS' then
3986
3987 if p_count > 0 then
3988
3989 oe_debug_pub.add('no need to do this as there exists payments ');
3990
3991 return;
3992 end if;
3993 end if; -- if p_action = 'ACTIONS_PAYMENTS'
3994
3995 --bug3781675 using the same l_x_header_payment_tbl for both create and update operations
3996
3997 if p_payment_type_code is not null
3998 and (p_credit_card_number is not null
3999 or p_check_number is not null
4000 or p_payment_type_code = 'CASH' )
4001 then -- insert payment record into oe_payments
4002
4003 if nvl(l_prepayment_flag, 'N') = 'Y' and
4004 --bug3733877 adding the following condition so that prepayment record gets insterted only when there are no payment record in oe_payments.
4005 p_count = 0 then
4006
4007 OE_OE_TOTALS_SUMMARY.Order_Totals
4008 (
4009 p_header_id=>p_header_id,
4010 p_subtotal =>l_subtotal,
4011 p_discount =>l_discount,
4012 p_charges =>l_charges,
4013 p_tax =>l_tax
4014 );
4015
4016 l_order_total := nvl(l_subtotal,0) + nvl(l_charges,0) + nvl(l_tax,0);
4017
4018 oe_debug_pub.add('order total is : ' || l_order_total);
4019
4020 l_downpayment := oe_prepayment_util.get_downpayment_amount(
4021 p_header_id => p_header_id,
4022 p_term_id => p_payment_term_id,
4023 p_curr_code => p_currency_code,
4024 p_order_total => l_order_total);
4025
4026 p_payment_event := 'PREPAY';
4027 p_payment_amount := l_downpayment;
4028
4029 end if; -- if nvl(l_prepayment_flag,'N') = 'Y'
4030
4031 oe_debug_pub.add('before calling BO ' );
4032 l_control_rec.controlled_operation := TRUE;
4033 l_control_rec.check_security := TRUE;
4034 l_control_rec.default_attributes := TRUE;
4035 l_control_rec.change_attributes := TRUE;
4036
4037 --bug3733877 setting the clear_dependents to FALSE as we pass all the values
4038 l_control_rec.clear_dependents := FALSE;
4039
4040 l_control_rec.validate_entity := TRUE;
4041 l_control_rec.write_to_DB := TRUE;
4042 l_control_rec.process := FALSE;
4043
4044 -- Instruct API to retain its caches
4045
4046 l_control_rec.clear_api_cache := FALSE;
4047 l_control_rec.clear_api_requests := FALSE;
4048
4049 -- Load IN parameters if any exist
4050 l_x_Header_Payment_tbl(1):=OE_ORDER_PUB.G_MISS_HEADER_PAYMENT_REC;
4051 l_x_old_Header_Payment_Tbl(1):=OE_ORDER_PUB.G_MISS_HEADER_PAYMENT_REC;
4052 l_x_Header_Payment_tbl(1).header_id := p_header_id;
4053
4054 --bug3781675 adding an IF-ELSE condition for create and update operations
4055 IF nvl(l_invoice_payment_exists,'N') = 'N' THEN
4056 l_x_Header_Payment_tbl(1).payment_number := l_max_payment_number+1;
4057 l_x_Header_Payment_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
4058
4059 -- Defaulting of flex values is currently done by the form.
4060 -- Set flex attributes to NULL in order to avoid defaulting them.
4061
4062 l_x_header_Payment_tbl(1).attribute1 := NULL;
4063 l_x_header_Payment_tbl(1).attribute2 := NULL;
4064 l_x_header_Payment_tbl(1).attribute3 := NULL;
4065 l_x_header_Payment_tbl(1).attribute4 := NULL;
4066 l_x_header_Payment_tbl(1).attribute5 := NULL;
4067 l_x_header_Payment_tbl(1).attribute6 := NULL;
4068 l_x_header_Payment_tbl(1).attribute7 := NULL;
4069 l_x_header_Payment_tbl(1).attribute8 := NULL;
4070 l_x_header_Payment_tbl(1).attribute9 := NULL;
4071 l_x_header_Payment_tbl(1).attribute10 := NULL;
4072 l_x_header_Payment_tbl(1).attribute11 := NULL;
4073 l_x_header_Payment_tbl(1).attribute12 := NULL;
4074 l_x_header_Payment_tbl(1).attribute13 := NULL;
4075 l_x_header_Payment_tbl(1).attribute14 := NULL;
4076 l_x_header_Payment_tbl(1).attribute15 := NULL;
4077 l_x_header_Payment_tbl(1).context := NULL;
4078 ELSE
4079 l_x_Header_Payment_tbl(1).payment_number := l_inv_payment_number;
4080 l_x_Header_Payment_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
4081 END IF;
4082
4083 oe_debug_pub.add('PVIPRANA: l_x_Header_Payment_tbl(1).operation is '||l_x_Header_Payment_tbl(1).operation);
4084 l_x_Header_Payment_tbl(1).payment_type_code := p_payment_type_code;
4085 l_x_Header_Payment_tbl(1).payment_collection_event := 'INVOICE';
4086 l_x_Header_Payment_tbl(1).payment_amount := NULL;
4087
4088 l_x_Header_Payment_tbl(1).check_number := p_check_number;
4089 l_x_Header_Payment_tbl(1).credit_card_code := p_credit_card_code;
4090 l_x_Header_Payment_tbl(1).credit_card_holder_name := p_credit_card_holder_name;
4091 l_x_Header_Payment_tbl(1).credit_card_number := p_credit_card_number;
4092 l_x_Header_Payment_tbl(1).credit_card_expiration_date := p_credit_card_expiration_date;
4093 l_x_Header_Payment_tbl(1).credit_card_approval_code := p_credit_card_approval_code;
4094 --bug3906851 start
4095 l_x_Header_Payment_tbl(1).credit_card_approval_date := p_credit_card_approval_date;
4096
4097 -- comment out for R12 CC encryption
4098 /*
4099 IF l_x_Header_Payment_tbl(1).credit_card_approval_code IS NOT NULL THEN
4100 oe_debug_pub.add('Credit Card Approval Code exists.... Fetching current auth code');
4101 oe_header_util.query_row
4102 ( p_header_id => p_header_id
4103 , x_header_rec => l_header_rec);
4104
4105 OE_Verify_Payment_PUB.Fetch_Current_Auth
4106 ( p_header_rec => l_header_rec
4107 , p_trxn_id => l_trxn_id
4108 , p_tangible_id => l_x_Header_Payment_tbl(1).tangible_id
4109 ) ;
4110
4111 oe_debug_pub.add( ' AFTER CALLING FETCH_CURRENT_AUTH' ) ;
4112
4113 oe_debug_pub.add( 'TANGIBLE ID IS : '||l_x_Header_Payment_tbl(1).tangible_id ) ;
4114 END IF;
4115 */
4116 --bug3906851 end
4117 -- Load IN parameters if any exist
4118
4119 if nvl(l_prepayment_flag, 'N') = 'Y' AND
4120 --bug3733877 Adding the following condition so that the prepayment record is inserted only when p_count = 0
4121 p_count = 0 THEN
4122
4123 l_x_Header_Payment_tbl(2):=OE_ORDER_PUB.G_MISS_HEADER_PAYMENT_REC;
4124 l_x_old_Header_Payment_Tbl(2):=OE_ORDER_PUB.G_MISS_HEADER_PAYMENT_REC;
4125 l_x_Header_Payment_tbl(2).header_id := p_header_id;
4126 l_x_Header_Payment_tbl(2).payment_number := l_max_payment_number+2; --bug3733877
4127
4128 -- Defaulting of flex values is currently done by the form.
4129 -- Set flex attributes to NULL in order to avoid defaulting them.
4130
4131 l_x_header_Payment_tbl(2).attribute1 := NULL;
4132 l_x_header_Payment_tbl(2).attribute2 := NULL;
4133 l_x_header_Payment_tbl(2).attribute3 := NULL;
4134 l_x_header_Payment_tbl(2).attribute4 := NULL;
4135 l_x_header_Payment_tbl(2).attribute5 := NULL;
4136 l_x_header_Payment_tbl(2).attribute6 := NULL;
4137 l_x_header_Payment_tbl(2).attribute7 := NULL;
4138 l_x_header_Payment_tbl(2).attribute8 := NULL;
4139 l_x_header_Payment_tbl(2).attribute9 := NULL;
4140 l_x_header_Payment_tbl(2).attribute10 := NULL;
4141 l_x_header_Payment_tbl(2).attribute11 := NULL;
4142 l_x_header_Payment_tbl(2).attribute12 := NULL;
4143 l_x_header_Payment_tbl(2).attribute13 := NULL;
4144 l_x_header_Payment_tbl(2).attribute14 := NULL;
4145 l_x_header_Payment_tbl(2).attribute15 := NULL;
4146 l_x_header_Payment_tbl(2).context := NULL;
4147
4148 -- Set Operation to Create
4149
4150 l_x_Header_Payment_tbl(2).operation := OE_GLOBALS.G_OPR_CREATE;
4151 l_x_Header_Payment_tbl(2).payment_type_code := p_payment_type_code;
4152 l_x_Header_Payment_tbl(2).payment_collection_event := p_payment_event;
4153 l_x_Header_Payment_tbl(2).payment_amount := p_payment_amount;
4154 l_x_Header_Payment_tbl(2).check_number := p_check_number;
4155 l_x_Header_Payment_tbl(2).credit_card_code := p_credit_card_code;
4156 l_x_Header_Payment_tbl(2).credit_card_holder_name := p_credit_card_holder_name;
4157 l_x_Header_Payment_tbl(2).credit_card_number := p_credit_card_number;
4158 l_x_Header_Payment_tbl(2).credit_card_expiration_date := p_credit_card_expiration_date;
4159 l_x_Header_Payment_tbl(2).credit_card_approval_code := p_credit_card_approval_code;
4160
4161
4162
4163 END IF; -- if prepayment_flag = 'Y'
4164
4165 oe_debug_pub.add('l_x_header_payment_tbl ' || l_x_header_payment_tbl.count);
4166 oe_debug_pub.add('l_x_old_header_payment_tbl ' || l_x_old_header_payment_tbl.count);
4167
4168 -- Call OE_Order_PVT.Header_Payments
4169 OE_Order_PVT.Header_Payments
4170 ( p_validation_level => FND_API.G_VALID_LEVEL_NONE
4171 , p_init_msg_list => FND_API.G_TRUE
4172 , p_control_rec => l_control_rec
4173 , p_x_Header_Payment_tbl => l_x_Header_Payment_tbl
4174 , p_x_old_Header_Payment_tbl => l_x_old_Header_Payment_tbl
4175 , x_return_Status => l_return_status
4176 );
4177
4178 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4180 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4181 RAISE FND_API.G_EXC_ERROR;
4182 END IF;
4183
4184
4185 else --if p_payment_type_code is null
4186 --bug3733877 start
4187 BEGIN
4188 DELETE FROM oe_payments
4189 WHERE header_id = p_header_id
4190 AND line_id IS NULL
4191 AND nvl(payment_collection_event,'PREPAY') = 'INVOICE';
4192 EXCEPTION
4193 WHEN OTHERS THEN
4194 null;
4195 END;
4196 --bug3733877 end
4197
4198 end if; -- if p_payment_type_code is not null
4199
4200
4201 END IF; -- if p_action in ('UPDATE_HEADER', 'DELETE_PAYMENT')
4202
4203 oe_debug_pub.add('before end ');
4204 x_return_status := l_return_status;
4205 oe_debug_pub.add('return status w no error is : ' || x_return_status);
4206
4207 EXCEPTION
4208
4209 WHEN others then
4210 x_return_status := FND_API.G_RET_STS_ERROR;
4211 oe_debug_pub.add('when others error ');
4212 RAISE;
4213
4214
4215 END Update_Hdr_Payment;
4216
4217 /*--------------------------------------------------------------------------
4218 Procedure Create_Refund
4219 This procedure calls AR Refund Wrapper API to create refund for prepayments.
4220 ----------------------------------------------------------------------------*/
4221 PROCEDURE Create_Refund
4222 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
4223 , p_refund_amount IN NUMBER
4224 , p_payment_set_id IN NUMBER
4225 , p_bank_account_id IN NUMBER
4226 , p_receipt_method_id IN NUMBER
4227 , x_return_status OUT NOCOPY VARCHAR2
4228 )
4229 IS
4230
4231 l_return_status VARCHAR2(30);
4232 l_prepay_application_id NUMBER;
4233 l_number_of_refund_receipts NUMBER;
4234 l_receipt_number VARCHAR2(30);
4235 l_cash_receipt_id NUMBER;
4236 l_receivable_application_id NUMBER;
4237 l_receivables_trx_id NUMBER;
4238 l_refund_amount NUMBER;
4239 l_format_mask VARCHAR2(500);
4240 l_msg_count NUMBER := 0 ;
4241 l_msg_data VARCHAR2(2000) := NULL ;
4242
4243 --
4244 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4245 --
4246 BEGIN
4247
4248 IF l_debug_level > 0 THEN
4249 oe_debug_pub.add( 'ENTERING OE_PREPAYMENT_PV.CREATE_REFUND.' , 1 ) ;
4250 oe_debug_pub.add( 'Before calling refund wrapper API refund amount IS: '||p_refund_amount , 1 ) ;
4251 oe_debug_pub.add( 'Before calling refund wrapper API payment_set_id IS: '||p_payment_set_id , 1 ) ;
4252 oe_debug_pub.add( 'OEXVPPYB: receipt_method_id is: '||p_receipt_method_id, 3 ) ;
4253 oe_debug_pub.add( 'OEXVPPYB: bank_account_id is: '||p_bank_account_id, 3 ) ;
4254 END IF;
4255
4256 AR_OM_PREPAY_REFUND_PVT.refund_prepayment_wrapper(
4257 p_api_version => 1.0,
4258 p_init_msg_list => FND_API.G_TRUE,
4259 p_commit => FND_API.G_FALSE,
4260 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4261 x_return_status => l_return_status,
4262 x_msg_count => l_msg_count,
4263 x_msg_data => l_msg_data,
4264 p_prepay_application_id => l_prepay_application_id,
4265 p_number_of_refund_receipts=> l_number_of_refund_receipts,
4266 p_bank_account_id => p_bank_account_id,
4267 p_receipt_method_id => p_receipt_method_id,
4268 p_receipt_number => l_receipt_number,
4269 p_cash_receipt_id => l_cash_receipt_id,
4270 p_receivable_application_id=> l_receivable_application_id, --OUT
4271 p_receivables_trx_id => l_receivables_trx_id, --OUT
4272 p_refund_amount => p_refund_amount,
4273 p_refund_date => sysdate,
4274 p_refund_gl_date => null,
4275 p_ussgl_transaction_code => null,
4276 p_attribute_rec => null,
4277 p_global_attribute_rec => null,
4278 p_comments => null,
4279 p_payment_set_id => p_payment_set_id
4280 );
4281
4282
4283 x_return_status := l_return_status;
4284 IF l_debug_level > 0 THEN
4285 oe_debug_pub.add( 'OEXVPPYB: after calling refund_prepayment_wrapper API, return status is: '||l_return_status, 3 ) ;
4286 oe_debug_pub.add( 'OEXVPPYB: RECEIPT_NUMBER AFTER CALLING AR REFUND_PREPAYMENTS IS: '||L_RECEIPT_NUMBER , 1 ) ;
4287 oe_debug_pub.add( 'OEXVPPYB: NUMBER_OF_REFUND AFTER CALLING AR REFUND_PREPAYMENT IS: '||L_NUMBER_OF_REFUND_RECEIPTS , 1 ) ;
4288 oe_debug_pub.add( 'OEXVPPYB: l_msg_count AFTER CALLING AR REFUND_PREPAYMENT IS: '||l_msg_count , 1 ) ;
4289 END IF;
4290
4291 l_format_mask := get_format_mask(p_header_rec.transactional_curr_code);
4292
4293 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4294 fnd_message.Set_Name('ONT', 'ONT_REFUND_PROCESS_SUCCESS');
4295 FND_MESSAGE.SET_TOKEN('AMOUNT' , TO_CHAR(p_refund_amount, l_format_mask));
4296 FND_MESSAGE.SET_TOKEN('NUMBER' , l_number_of_refund_receipts);
4297 oe_msg_pub.add;
4298 IF l_debug_level > 0 THEN
4299 oe_debug_pub.add( 'OEXVPPYB: REFUND REQUEST OF ' ||P_REFUND_AMOUNT||' HAS BEEN PROCESSED SUCCESSFULLY.' , 3 ) ;
4300 oe_debug_pub.add('pviprana: Releasing Prepayment Hold');
4301 END IF;
4302 --bug3507871
4303 Release_Prepayment_Hold ( p_header_id => p_header_rec.header_id
4304 , p_msg_count => l_msg_count
4305 , p_msg_data => l_msg_data
4306 , p_return_status => l_return_status
4307 );
4308
4309 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4310 RAISE FND_API.G_EXC_ERROR;
4311 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4312 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4313 END IF;
4314 ELSE
4315 fnd_message.Set_Name('ONT', 'ONT_REFUND_PROCESS_FAILED');
4316 FND_MESSAGE.SET_TOKEN('AMOUNT', TO_CHAR(p_refund_amount, l_format_mask));
4317 oe_msg_pub.add;
4318 IF l_debug_level > 0 THEN
4319 oe_debug_pub.add( 'OEXVPPYB: REFUND PROCESSING FOR ' ||P_REFUND_AMOUNT||' FAILED.' , 3 ) ;
4320 END IF;
4321
4322 IF l_msg_count = 1 THEN
4323 IF l_debug_level > 0 THEN
4324 oe_debug_pub.add('Error message after calling refund_prepayment_wrapper API: '||l_msg_data , 3 ) ;
4325 END IF;
4326 oe_msg_pub.add_text(p_message_text => l_msg_data);
4327 ELSIF ( FND_MSG_PUB.Count_Msg > 0 ) THEN
4328 arp_util.enable_debug;
4329 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
4330 -- l_msg_data := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
4331 l_msg_data := FND_MSG_PUB.Get(i,'F');
4332 IF l_debug_level > 0 THEN
4333 oe_debug_pub.Add( 'Error message from AR API: '|| L_MSG_DATA , 3 );
4334 END IF;
4335 oe_msg_pub.add_text(p_message_text => l_msg_data);
4336 END LOOP;
4337 END IF;
4338 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4339 RAISE FND_API.G_EXC_ERROR;
4340 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4341 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4342 END IF;
4343 END IF;
4344
4345 IF l_debug_level > 0 THEN
4346 oe_debug_pub.add( 'EXITING OE_PREPAYMENT_PV.Create_Refund.' , 1 ) ;
4347 END IF;
4348
4349 EXCEPTION
4350
4351 WHEN FND_API.G_EXC_ERROR THEN
4352 x_return_status := FND_API.G_RET_STS_ERROR;
4353 OE_MSG_PUB.Count_And_Get
4354 ( p_count => l_msg_count,
4355 p_data => l_msg_data
4356 );
4357
4358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4360 IF l_debug_level > 0 THEN
4361 oe_debug_pub.add( 'Unexpected error in Create_Refund: ' || SQLERRM , 3 ) ;
4362 END IF;
4363 OE_MSG_PUB.Count_And_Get
4364 ( p_count => l_msg_count,
4365 p_data => l_msg_data
4366 );
4367
4368 WHEN OTHERS THEN
4369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4370 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4371 THEN
4372 OE_MSG_PUB.Add_Exc_Msg
4373 ( G_PKG_NAME
4374 , 'Create_Refund'
4375 );
4376 END IF;
4377 IF l_debug_level > 0 THEN
4378 oe_debug_pub.add( 'Other oracle error in Create_Refund: ' || SQLERRM , 3 ) ;
4379 END IF;
4380
4381 OE_MSG_PUB.Count_And_Get
4382 ( p_count => l_msg_count,
4383 p_data => l_msg_data
4384 );
4385
4386 END Create_Refund;
4387
4388 PROCEDURE Process_Payment_Refund
4389 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
4390 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
4391 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4392 , x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
4393 ) IS
4394
4395 l_bank_account_id NUMBER;
4396 l_receipt_method_id NUMBER;
4397 l_refund_amount NUMBER;
4398 l_payment_set_id NUMBER;
4399 l_return_status VARCHAR2(30);
4400 l_msg_count NUMBER := 0 ;
4401 l_msg_data VARCHAR2(2000) := NULL ;
4402 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4403
4404 BEGIN
4405
4406 IF l_debug_level > 0 THEN
4407 oe_debug_pub.add( 'ENTERING OE_PREPAYMENT_PV.Process_Payment_Refund.' , 1 ) ;
4408 END IF;
4409
4410 OE_Prepayment_Util.Get_PrePayment_Info
4411 ( p_header_id => p_header_rec.header_id
4412 , x_payment_set_id => l_payment_set_id
4413 , x_prepaid_amount => l_refund_amount
4414 );
4415
4416 -- no need to process payment refund.
4417 IF nvl(l_refund_amount, 0) <= 0 THEN
4418 return;
4419 END IF;
4420
4421 IF l_debug_level > 0 THEN
4422 oe_debug_pub.add('In Process_Payment_Refund, refund amount is: '||l_refund_amount, 3);
4423 oe_debug_pub.add('In Process_Payment_Refund, payment_set_id is: '||l_payment_set_id,3) ;
4424 END IF;
4425
4426 Create_Refund
4427 (p_header_rec => p_header_rec,
4428 p_refund_amount => l_refund_amount,
4429 p_payment_set_id => l_payment_set_id,
4430 p_bank_account_id => l_bank_account_id,
4431 p_receipt_method_id => l_receipt_method_id,
4432 x_return_status => l_return_status
4433 );
4434
4435 IF l_debug_level > 0 THEN
4436 oe_debug_pub.add( 'In Process_Payment_Refund, after calling create_refund return status is: '||l_return_status, 3 ) ;
4437 END IF;
4438
4439
4440 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4441 UPDATE oe_payments
4442 SET payment_amount = 0, prepaid_amount = 0
4443 WHERE payment_collection_event = 'PREPAY'
4444 AND header_id = p_header_rec.header_id;
4445
4446 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4447 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4448 RAISE FND_API.G_EXC_ERROR;
4449 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4450 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4451 END IF;
4452 END IF;
4453
4454 x_return_status := l_return_status;
4455
4456 IF l_debug_level > 0 THEN
4457 oe_debug_pub.add( 'EXITING OE_PREPAYMENT_PV.Process_Payment_Refund.' , 1 ) ;
4458 END IF;
4459
4460 EXCEPTION
4461
4462 WHEN FND_API.G_EXC_ERROR THEN
4463 x_return_status := FND_API.G_RET_STS_ERROR;
4464 OE_MSG_PUB.Count_And_Get
4465 ( p_count => l_msg_count,
4466 p_data => l_msg_data
4467 );
4468
4469 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4470 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4471 OE_MSG_PUB.Count_And_Get
4472 ( p_count => l_msg_count,
4473 p_data => l_msg_data
4474 );
4475
4476 WHEN OTHERS THEN
4477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4478 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4479 THEN
4480 OE_MSG_PUB.Add_Exc_Msg
4481 ( G_PKG_NAME
4482 , 'Process_Payment_Refund'
4483 );
4484 END IF;
4485
4486 OE_MSG_PUB.Count_And_Get
4487 ( p_count => l_msg_count,
4488 p_data => l_msg_data
4489 );
4490
4491
4492 END Process_Payment_Refund;
4493
4494 /*----------------------------------------------------------------------
4495 Returns 'Y' if any type of payment hold exists for the order.
4496 This is introduced for multiple payments project, to check if hold id
4497 16 exists on order or line in addition to any prepayment holds
4498 (13, 14, 15).
4499 ----------------------------------------------------------------------*/
4500 PROCEDURE Any_Payment_Hold_Exists
4501 ( p_header_id IN NUMBER
4502 , p_line_id IN NUMBER DEFAULT NULL --pnpl
4503 , p_hold_exists OUT NOCOPY VARCHAR2
4504 )
4505 IS
4506 l_hold_result VARCHAR2(30);
4507 p_hold_rec OE_HOLDS_PUB.any_line_hold_rec;
4508 l_return_status VARCHAR2(30);
4509 l_msg_count NUMBER;
4510 l_msg_data VARCHAR2(2000);
4511 --
4512 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4513 --
4514 BEGIN
4515
4516 IF l_debug_level > 0 THEN
4517 oe_debug_pub.add( 'OEXVPPYB: Entering OE_PREPAYMENT_PVT.Any_Payment_Hold_Exists.' , 3 ) ;
4518 END IF;
4519
4520 --pnpl
4521 IF p_line_id IS NULL THEN
4522
4523 -- First check if there is any prepayment hold (hold id 13,14,15).
4524 Any_Prepayment_Hold_Exists ( p_header_id => p_header_id
4525 , p_hold_exists => p_hold_exists
4526 );
4527
4528 IF p_hold_exists = 'Y' THEN
4529 IF l_debug_level > 0 THEN
4530 oe_debug_pub.add( 'OEXVPPYB: PREPAYMENT HOLD EXISTS ON HEADER ID : ' ||
4531 P_HEADER_ID , 3 ) ;
4532 END IF;
4533 RETURN ;
4534 ELSE
4535 --pnpl adding checks for holds 11 and 12
4536 OE_HOLDS_PUB.Check_Holds
4537 ( p_api_version => 1.0
4538 , p_header_id => p_header_id
4539 , p_hold_id => 11 -- CC failure hold
4540 , p_entity_code => 'O'
4541 , p_entity_id => p_header_id
4542 , x_result_out => l_hold_result
4543 , x_msg_count => l_msg_count
4544 , x_msg_data => l_msg_data
4545 , x_return_status => l_return_status
4546 );
4547 -- Check the Result
4548 IF l_hold_result = FND_API.G_TRUE THEN
4549 p_hold_exists := 'Y';
4550 IF l_debug_level > 0 THEN
4551 oe_debug_pub.add( 'OEXVPPYB: Credit Card Authorization Failure hold EXISTS ON ORDER ' , 3 ) ;
4552 END IF;
4553 return;
4554
4555 ELSE
4556 OE_HOLDS_PUB.Check_Holds
4557 ( p_api_version => 1.0
4558 , p_header_id => p_header_id
4559 , p_hold_id => 12 -- CC risk hold
4560 , p_entity_code => 'O'
4561 , p_entity_id => p_header_id
4562 , x_result_out => l_hold_result
4563 , x_msg_count => l_msg_count
4564 , x_msg_data => l_msg_data
4565 , x_return_status => l_return_status
4566 );
4567 IF l_hold_result = FND_API.G_TRUE THEN
4568 p_hold_exists := 'Y';
4569 IF l_debug_level > 0 THEN
4570 oe_debug_pub.add( 'OEXVPPYB: Credit Card Risk hold EXISTS ON ORDER' , 3 ) ;
4571 END IF;
4572 return;
4573 ELSE
4574 -- check if there exists header level pending authorization hold.
4575 OE_HOLDS_PUB.Check_Holds
4576 ( p_api_version => 1.0
4577 , p_header_id => p_header_id
4578 , p_hold_id => 16
4579 , p_entity_code => 'O'
4580 , p_entity_id => p_header_id
4581 , x_result_out => l_hold_result
4582 , x_msg_count => l_msg_count
4583 , x_msg_data => l_msg_data
4584 , x_return_status => l_return_status
4585 );
4586
4587 IF l_hold_result = FND_API.G_TRUE THEN
4588 p_hold_exists := 'Y';
4589 IF l_debug_level > 0 THEN
4590 oe_debug_pub.add( 'OEXVPPYB: PAYMENT HOLD 16 EXISTS ON ORDER.' , 3 ) ;
4591 END IF;
4592 return;
4593 ELSE
4594 -- check if there exists line level pending authorization hold.
4595 p_hold_rec.header_id := p_header_id;
4596 p_hold_rec.hold_id := 16;
4597 OE_HOLDS_PUB.Check_Any_Line_Hold
4598 (x_hold_rec => p_hold_rec
4599 ,x_return_status => l_return_status
4600 ,x_msg_count => l_msg_count
4601 ,x_msg_data => l_msg_data
4602 );
4603
4604 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS AND
4605 p_hold_rec.x_result_out = FND_API.G_TRUE )
4606 THEN
4607 p_hold_exists := 'Y';
4608 IF l_debug_level > 0 THEN
4609 oe_debug_pub.add( 'OEXVPPYB: PAYMENT HOLD 16 EXISTS ON ORDER LINE.' , 3 ) ;
4610 END IF;
4611 ELSE
4612 p_hold_exists := 'N';
4613 IF l_debug_level > 0 THEN
4614 oe_debug_pub.add( 'OEXVPPYB: NO PAYMENT HOLD ON ORDER.' , 3 ) ;
4615 END IF;
4616 END IF;
4617 END IF;
4618 END IF;
4619 END IF;
4620 END IF;
4621
4622 --pnpl added check for line level holds
4623 ELSE --p_line_id IS NOT NULL
4624 -- Checking existense of unreleased holds on this order line
4625 OE_HOLDS_PUB.Check_Holds_Line
4626 ( p_hdr_id => p_header_id
4627 , p_line_id => p_line_id
4628 , p_hold_id => 11 -- CC failure hold
4629 , p_entity_code => 'O'
4630 , p_entity_id => p_header_id
4631 , x_result_out => l_hold_result
4632 , x_msg_count => l_msg_count
4633 , x_msg_data => l_msg_data
4634 , x_return_status => l_return_status
4635 );
4636 -- Check the Result
4637 IF l_hold_result = FND_API.G_TRUE THEN
4638 p_hold_exists := 'Y';
4639 IF l_debug_level > 0 THEN
4640 oe_debug_pub.add( 'OEXVPPYB: Credit Card Authorization Failure hold EXISTS ON ORDER LINE' , 3 ) ;
4641 END IF;
4642 return;
4643
4644 ELSE
4645 OE_HOLDS_PUB.Check_Holds_Line
4646 ( p_hdr_id => p_header_id
4647 , p_line_id => p_line_id
4648 , p_hold_id => 12 -- CC risk hold
4649 , p_entity_code => 'O'
4650 , p_entity_id => p_header_id
4651 , x_result_out => l_hold_result
4652 , x_msg_count => l_msg_count
4653 , x_msg_data => l_msg_data
4654 , x_return_status => l_return_status
4655 );
4656 IF l_hold_result = FND_API.G_TRUE THEN
4657 p_hold_exists := 'Y';
4658 IF l_debug_level > 0 THEN
4659 oe_debug_pub.add( 'OEXVPPYB: Credit Card Risk hold EXISTS ON ORDER LINE' , 3 ) ;
4660 END IF;
4661 return;
4662 ELSE
4663 OE_HOLDS_PUB.Check_Holds_Line
4664 ( p_hdr_id => p_header_id
4665 , p_line_id => p_line_id
4666 , p_hold_id => 16 -- pending authorization hold
4667 , p_entity_code => 'O'
4668 , p_entity_id => p_header_id
4669 , x_result_out => l_hold_result
4670 , x_msg_count => l_msg_count
4671 , x_msg_data => l_msg_data
4672 , x_return_status => l_return_status
4673 );
4674
4675 IF l_hold_result = FND_API.G_TRUE THEN
4676 p_hold_exists := 'Y';
4677 IF l_debug_level > 0 THEN
4678 oe_debug_pub.add( 'OEXVPPYB: Pending Authorization hold EXISTS ON ORDER LINE' , 3 ) ;
4679 END IF;
4680 ELSE
4681 p_hold_exists := 'N';
4682 IF l_debug_level > 0 THEN
4683 oe_debug_pub.add( 'OEXVPPYB: NO PAYMENT HOLD ON ORDER LINE' , 3 ) ;
4684 END IF;
4685 END IF;
4686 END IF;
4687 END IF;
4688 END IF; -- endif p_line_id IS NULL
4689
4690
4691 EXCEPTION
4692
4693 WHEN OTHERS THEN
4694 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4695 THEN
4696 FND_MSG_PUB.Add_Exc_Msg
4697 ( G_PKG_NAME
4698 , 'Any_Payment_Hold_Exists'
4699 );
4700 END IF;
4701
4702 OE_MSG_PUB.Count_And_Get
4703 ( p_count => l_msg_count,
4704 p_data => l_msg_data
4705 );
4706
4707 END Any_Payment_Hold_Exists;
4708
4709 PROCEDURE Update_Payment_Numbers(p_header_id in number,
4710 p_line_id in number := NULL,
4711 x_return_status out nocopy varchar2,
4712 x_msg_count out nocopy number,
4713 x_msg_data out nocopy varchar2) IS
4714
4715 CURSOR lock_lin_Payments(p_header_id in number,
4716 p_line_id in NUMBER) IS
4717 SELECT payment_type_code
4718 FROM oe_payments
4719 WHERE header_id = p_header_id
4720 AND line_id = p_line_id
4721 AND payment_number is null
4722 FOR UPDATE NOWAIT;
4723
4724 CURSOR lock_hdr_Payments(p_header_id NUMBER) IS
4725 SELECT payment_type_code
4726 FROM oe_payments
4727 WHERE header_id = p_header_id
4728 AND payment_number is null
4729 FOR UPDATE NOWAIT;
4730
4731 l_payment_type varchar2(30) := null;
4732 l_payment_trx_id number := -1;
4733 Begin
4734
4735 x_return_status := FND_API.G_RET_STS_SUCCESS;
4736 x_msg_count := 0;
4737 x_msg_data := null;
4738
4739 if p_line_id is not null then
4740
4741 Begin
4742 SAVEPOINT LOCK_LINE_PAYMENTS;
4743 OPEN lock_lin_Payments(p_header_id, p_line_id);
4744 FETCH lock_lin_Payments INTO l_payment_type;
4745 CLOSE lock_lin_Payments;
4746
4747 Exception
4748
4749 when no_data_found then
4750 IF lock_lin_Payments%ISOPEN Then
4751 close lock_lin_Payments;
4752 END IF;
4753 oe_debug_pub.add('no line payments exist');
4754 x_return_status := FND_API.G_RET_STS_SUCCESS;
4755 return;
4756 when others then
4757 ROLLBACK TO LINE_PAYMENTS;
4758 IF lock_lin_Payments%ISOPEN Then
4759 close lock_lin_Payments;
4760 END IF;
4761 oe_debug_pub.add('locking the row failed');
4762 -- issue an error message saying that lock row failed.
4763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4764 RAISE;
4765 End;
4766 -- was able to lock the row. so, go ahead and update oe_payments.
4767 Begin
4768
4769 if l_payment_type is not null then
4770
4771 update oe_payments
4772 set payment_number = 1
4773 where header_id = p_header_id
4774 and line_id = p_line_id
4775 and payment_number is null
4776 and payment_type_code = l_payment_type;
4777
4778 end if;
4779
4780 Exception
4781 when no_data_found then
4782 --oe_msg_pub.add( ); --
4783 x_return_status := FND_API.G_RET_STS_ERROR;
4784 RAISE;
4785
4786 when others then
4787 --oe_msg_pub.add(); --
4788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4790 End;
4791
4792 elsif p_header_id is not null then
4793
4794 Begin
4795 SAVEPOINT LOCK_HEADER_PAYMENTS;
4796 OPEN lock_hdr_Payments(p_header_id);
4797 LOOP
4798 FETCH lock_hdr_Payments INTO l_payment_type;
4799 EXIT WHEN lock_hdr_Payments%NOTFOUND;
4800 END LOOP;
4801 CLOSE lock_hdr_Payments;
4802
4803 Exception
4804
4805 when no_data_found then
4806 IF lock_hdr_Payments%ISOPEN Then
4807 close lock_hdr_Payments;
4808 END IF;
4809 oe_debug_pub.add('no header payments exist');
4810 x_return_status := FND_API.G_RET_STS_SUCCESS;
4811 return;
4812 when others then
4813 ROLLBACK TO LOCK_HEADER_PAYMENTS;
4814 IF lock_hdr_Payments%ISOPEN Then
4815 close lock_hdr_Payments;
4816 END IF;
4817 oe_debug_pub.add('locking the row failed');
4818 -- issue an error message saying that lock row failed.
4819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4820 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4821 End;
4822 -- was able to lock the row. so, go ahead and update oe_payments.
4823 Begin
4824
4825 update oe_payments
4826 set payment_number = 1
4827 where header_id = p_header_id
4828 and payment_number is null;
4829
4830 Exception
4831 when no_data_found then
4832 --oe_msg_pub.add( ); --
4833 x_return_status := FND_API.G_RET_STS_ERROR;
4834 RAISE;
4835 when others then
4836 --oe_msg_pub.add(); --
4837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4839 End;
4840
4841 end if;
4842
4843 END Update_Payment_Numbers;
4844
4845 PROCEDURE Delete_Payment_Hold
4846 (p_line_id IN NUMBER
4847 ,p_header_id IN NUMBER
4848 ,p_hold_type IN VARCHAR2
4849 ,x_return_status OUT NOCOPY VARCHAR2
4850 ,x_msg_count OUT NOCOPY NUMBER
4851 ,x_msg_data OUT NOCOPY VARCHAR2
4852 ) IS
4853
4854 l_prepay_exists VARCHAR2(1) := 'N';
4855 l_msg_count NUMBER := 0;
4856 l_msg_data VARCHAR2(2000);
4857 l_return_status VARCHAR2(30);
4858 --
4859 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4860 --
4861
4862 BEGIN
4863 IF l_debug_level > 0 THEN
4864 oe_debug_pub.add( 'OEXVPPYB: Entering OE_PREPAYMENT_PVT.Delete_Payment_Hold.' , 3 ) ;
4865 END IF;
4866 x_return_status := FND_API.G_RET_STS_SUCCESS;
4867
4868 IF p_line_id IS NOT NULL THEN
4869 OE_Verify_Payment_PUB.Release_Verify_Line_Hold
4870 ( p_header_id => p_header_id
4871 , p_line_id => p_line_id
4872 , p_epayment_hold => 'Y'
4873 , p_msg_count => l_msg_count
4874 , p_msg_data => l_msg_data
4875 , p_return_status => l_return_status
4876 );
4877
4878 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4879 RAISE FND_API.G_EXC_ERROR;
4880 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4882 END IF;
4883
4884 ELSE
4885 IF p_hold_type = 'PREPAYMENT' THEN
4886 OE_Prepayment_PVT.Release_Prepayment_Hold
4887 ( p_header_id => p_header_id
4888 , p_msg_count => l_msg_count
4889 , p_msg_data => l_msg_data
4890 , p_return_status => l_return_status
4891 );
4892
4893 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4894 RAISE FND_API.G_EXC_ERROR;
4895 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4896 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4897 END IF;
4898
4899 ELSE
4900 OE_Verify_Payment_PUB.Release_Verify_Hold
4901 ( p_header_id => p_header_id
4902 , p_epayment_hold => 'Y'
4903 , p_msg_count => l_msg_count
4904 , p_msg_data => l_msg_data
4905 , p_return_status => l_return_status
4906 );
4907 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4908 RAISE FND_API.G_EXC_ERROR;
4909 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
4910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4911 END IF;
4912 END IF;
4913 END IF;
4914
4915 EXCEPTION
4916 WHEN OTHERS THEN
4917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4918 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4919 THEN
4920 FND_MSG_PUB.Add_Exc_Msg
4921 ( G_PKG_NAME
4922 , 'Delete_Prepayment_Hold'
4923 );
4924 END IF;
4925
4926 OE_MSG_PUB.Count_And_Get
4927 ( p_count => l_msg_count,
4928 p_data => l_msg_data
4929 );
4930
4931 END Delete_Payment_Hold;
4932
4933 --pnpl start
4934 PROCEDURE Get_First_Installment
4935 (p_currency_code IN fnd_currencies.currency_code%TYPE
4936 ,p_x_due_now_total_detail_tbl IN OUT NOCOPY AR_VIEW_TERM_GRP.amounts_table
4937 ,x_due_now_total_summary_rec OUT NOCOPY AR_VIEW_TERM_GRP.summary_amounts_rec
4938 ,x_return_status OUT NOCOPY VARCHAR2
4939 ,x_msg_count OUT NOCOPY NUMBER
4940 ,x_msg_data OUT NOCOPY VARCHAR2
4941 ) IS
4942
4943 CURSOR due_now_cur(p_term_id IN NUMBER) IS
4944 SELECT first_installment_code , relative_amount/base_amount
4945 FROM ra_terms t,
4946 ra_terms_lines tl
4947 WHERE t.term_id = tl.term_id
4948 AND t.term_id = p_term_id
4949 AND sequence_num = 1;
4950
4951 i PLS_INTEGER;
4952 l_due_now_line_amount_sum NUMBER := 0;
4953 l_due_now_tax_amount_sum NUMBER := 0;
4954 l_due_now_freight_amount_sum NUMBER := 0;
4955 l_due_now_total_sum NUMBER := 0;
4956 l_due_now_subtotal NUMBER := 0;
4957 l_due_now_st_rnd NUMBER := 0;
4958 l_due_now_tax NUMBER := 0;
4959 l_due_now_tax_rnd NUMBER := 0;
4960 l_due_now_charges NUMBER := 0;
4961 l_due_now_chgs_rnd NUMBER := 0;
4962 l_due_now_total NUMBER := 0;
4963 l_installment_option VARCHAR2(12);
4964 l_percent NUMBER := 0;
4965 l_pr_return_value BOOLEAN;
4966
4967 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4968
4969 BEGIN
4970 IF l_debug_level > 0 THEN
4971 oe_debug_pub.add('ENTERING OE_PREPAYMENT_PVT.GET_FIRST_INSTALLMENT');
4972 END IF;
4973
4974 x_return_status := FND_API.G_RET_STS_SUCCESS;
4975
4976 l_pr_return_value := OE_ORDER_UTIL.Get_Precision(p_currency_code);
4977
4978
4979 FOR i IN p_x_due_now_total_detail_tbl.FIRST.. p_x_due_now_total_detail_tbl.LAST LOOP
4980
4981 OPEN due_now_cur(p_x_due_now_total_detail_tbl(i).term_id);
4982 FETCH due_now_cur INTO l_installment_option, l_percent;
4983 IF l_installment_option = 'ALLOCATE' THEN
4984 l_due_now_subtotal := p_x_due_now_total_detail_tbl(i).line_amount * l_percent;
4985 l_due_now_st_rnd := ROUND(nvl(l_due_now_subtotal,0), OE_ORDER_UTIL.G_Precision);
4986 l_due_now_tax := p_x_due_now_total_detail_tbl(i).tax_amount * l_percent;
4987 l_due_now_tax_rnd := ROUND(nvl(l_due_now_tax,0), OE_ORDER_UTIL.G_Precision);
4988 l_due_now_charges := p_x_due_now_total_detail_tbl(i).freight_amount * l_percent;
4989 l_due_now_chgs_rnd := ROUND(nvl(l_due_now_charges,0), OE_ORDER_UTIL.G_Precision);
4990 l_due_now_total := l_due_now_st_rnd + l_due_now_tax_rnd + l_due_now_chgs_rnd;
4991
4992 ELSIF l_installment_option = 'INCLUDE' THEN
4993 l_due_now_subtotal := p_x_due_now_total_detail_tbl(i).line_amount* l_percent;
4994 l_due_now_st_rnd := ROUND(nvl(l_due_now_subtotal,0), OE_ORDER_UTIL.G_Precision);
4995 l_due_now_tax := p_x_due_now_total_detail_tbl(i).tax_amount;
4996 l_due_now_charges := p_x_due_now_total_detail_tbl(i).freight_amount;
4997 l_due_now_total := l_due_now_st_rnd + l_due_now_tax + l_due_now_charges;
4998 END IF;
4999
5000 p_x_due_now_total_detail_tbl(i).total_amount := l_due_now_total;
5001 l_due_now_line_amount_sum := l_due_now_line_amount_sum + l_due_now_subtotal;
5002 l_due_now_tax_amount_sum := l_due_now_tax_amount_sum + l_due_now_tax;
5003 l_due_now_freight_amount_sum := l_due_now_freight_amount_sum + l_due_now_charges;
5004 l_due_now_total_sum := l_due_now_total_sum + l_due_now_total;
5005
5006 IF l_debug_level > 0 THEN
5007 oe_debug_pub.add('l_due_now_total_sum : ' || l_due_now_total_sum);
5008 END IF;
5009
5010 CLOSE due_now_cur;
5011 END LOOP;
5012
5013 x_due_now_total_summary_rec.line_amount := l_due_now_line_amount_sum;
5014 x_due_now_total_summary_rec.tax_amount := l_due_now_tax_amount_sum;
5015 x_due_now_total_summary_rec.freight_amount := l_due_now_freight_amount_sum;
5016 x_due_now_total_summary_rec.total_amount := l_due_now_total_sum;
5017
5018 IF l_debug_level > 0 THEN
5019 oe_debug_pub.add('EXITING OE_PREPAYMENT_PVT.GET_FIRST_INSTALLMENT');
5020 END IF;
5021
5022 EXCEPTION
5023 WHEN OTHERS THEN
5024 IF l_debug_level > 0 THEN
5025 oe_debug_pub.add('Error in OE_PREPAYMENT_PVT.Get_First_Installment');
5026 END IF;
5027
5028 x_return_status := FND_API.G_RET_STS_ERROR;
5029
5030 END Get_First_Installment;
5031
5032
5033 PROCEDURE Get_Pay_Now_Amounts
5034 (p_header_id IN NUMBER DEFAULT NULL
5035 ,p_line_id IN NUMBER DEFAULT NULL
5036 ,p_exc_inv_lines IN VARCHAR2 DEFAULT NULL
5037 ,x_pay_now_subtotal OUT NOCOPY NUMBER
5038 ,x_pay_now_tax OUT NOCOPY NUMBER
5039 ,x_pay_now_charges OUT NOCOPY NUMBER
5040 ,x_pay_now_total OUT NOCOPY NUMBER
5041 ,x_pay_now_commitment OUT NOCOPY NUMBER
5042 ,x_msg_count OUT NOCOPY NUMBER
5043 ,x_msg_data OUT NOCOPY VARCHAR2
5044 ,x_return_status OUT NOCOPY VARCHAR2
5045 ) IS
5046
5047 CURSOR lines_cur(p_header_id IN NUMBER) IS
5048 SELECT line_id
5049 ,payment_term_id
5050 FROM oe_order_lines_all
5051 WHERE header_id = p_header_id;
5052
5053 CURSOR exc_inv_lines_cur(p_header_id IN NUMBER) IS
5054 SELECT line_id
5055 ,payment_term_id
5056 FROM oe_order_lines_all
5057 WHERE header_id = p_header_id
5058 AND nvl(invoice_interface_status_code,'NO') <> 'YES';
5059
5060 l_pay_now_total_detail_tbl AR_VIEW_TERM_GRP.amounts_table;
5061 l_pay_now_total_summary_rec AR_VIEW_TERM_GRP.summary_amounts_rec;
5062 l_line_tbl oe_order_pub.line_tbl_type;
5063 i pls_integer;
5064 --bug5223078 start
5065 j pls_integer;
5066 l_hdr_term_id NUMBER;
5067 --bug5223078 end
5068 l_line_id NUMBER;
5069 l_header_id NUMBER;
5070 l_currency_code VARCHAR2(15);
5071 l_pay_now_commitment NUMBER;
5072 l_exc_inv_lines VARCHAR2(1);
5073
5074 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
5075
5076 BEGIN
5077 IF l_debug_level > 0 THEN
5078 oe_debug_pub.add('ENTERING OE_PREPAYMENT_PVT.GET_PAY_NOW_AMOUNTS');
5079 END IF;
5080 x_return_status := FND_API.G_RET_STS_SUCCESS;
5081
5082 IF p_exc_inv_lines IS NULL THEN
5083 l_exc_inv_lines := 'N';
5084 ELSE
5085 l_exc_inv_lines := 'Y';
5086 END IF;
5087
5088
5089 IF p_line_id IS NOT NULL THEN
5090 -- this is for line payment
5091 SELECT line_id
5092 ,header_id
5093 ,payment_term_id
5094 INTO l_line_tbl(1).line_id
5095 ,l_line_tbl(1).header_id
5096 ,l_line_tbl(1).payment_term_id
5097 FROM oe_order_lines_all
5098 WHERE line_id=p_line_id;
5099 ELSE
5100 -- this is for header payment
5101 i := 1;
5102 IF l_exc_inv_lines = 'N' THEN
5103 FOR c_line_rec in lines_cur(p_header_id) LOOP
5104 l_line_tbl(i).header_id := p_header_id;
5105 l_line_tbl(i).line_id := c_line_rec.line_id;
5106 l_line_tbl(i).payment_term_id := c_line_rec.payment_term_id;
5107 i := i + 1;
5108 END LOOP;
5109 ELSE
5110 FOR c_line_rec in exc_inv_lines_cur(p_header_id) LOOP
5111 l_line_tbl(i).header_id := p_header_id;
5112 l_line_tbl(i).line_id := c_line_rec.line_id;
5113 l_line_tbl(i).payment_term_id := c_line_rec.payment_term_id;
5114 i := i + 1;
5115 END LOOP;
5116 END IF;
5117
5118 END IF;
5119
5120 -- populate information to pl/sql table in order to call API to get Pay Now portion
5121
5122 i := l_line_tbl.First;
5123 j := 1;
5124 --bug4654227
5125 IF i IS NOT NULL THEN
5126 oe_order_cache.load_order_header(l_line_tbl(i).header_id);
5127 l_header_id := l_line_tbl(i).header_id;
5128 l_currency_code := OE_Order_Cache.g_header_rec.transactional_curr_code;
5129 END IF;
5130
5131 WHILE i IS NOT NULL LOOP
5132
5133 --bug5223078 start
5134 IF l_line_tbl(i).payment_term_id IS NULL THEN
5135 IF l_debug_level > 0 THEN
5136 oe_debug_pub.add('Not passing the line ' || l_line_tbl(i).line_id || ' to AR API since the payment term is null');
5137 END IF;
5138
5139 GOTO END_OF_LOOP;
5140 END IF;
5141
5142 --using the index j for l_pay_now_total_detail_tbl
5143 --bug5223078 end
5144
5145 l_pay_now_total_detail_tbl(j).line_id := l_line_tbl(i).line_id;
5146 l_pay_now_total_detail_tbl(j).term_id := l_line_tbl(i).payment_term_id;
5147 l_line_id := l_line_tbl(i).line_id;
5148
5149 l_pay_now_total_detail_tbl(j).line_amount :=
5150 OE_Verify_Payment_PUB.Get_Line_Total
5151 (p_line_id => l_line_id
5152 ,p_header_id => l_header_id
5153 ,p_currency_code => l_currency_code
5154 ,p_level => NULL
5155 ,p_amount_type => 'SUBTOTAL'
5156 );
5157 l_pay_now_total_detail_tbl(j).tax_amount :=
5158 OE_Verify_Payment_PUB.Get_Line_Total
5159 (p_line_id => l_line_id
5160 ,p_header_id => l_header_id
5161 ,p_currency_code => l_currency_code
5162 ,p_level => NULL
5163 ,p_amount_type => 'TAX'
5164 );
5165 l_pay_now_total_detail_tbl(j).freight_amount :=
5166 OE_Verify_Payment_PUB.Get_Line_Total
5167 (p_line_id => l_line_id
5168 ,p_header_id => l_header_id
5169 ,p_currency_code => l_currency_code
5170 ,p_level => NULL
5171 ,p_amount_type => 'CHARGES'
5172 );
5173
5174 --bug5223078 start
5175 j := j+1;
5176 <<END_OF_LOOP>>
5177 --bug5223078 end
5178 i := l_line_tbl.Next(i);
5179
5180 END LOOP;
5181
5182 IF p_line_id IS NULL THEN
5183 IF l_debug_level > 0 THEN
5184 oe_debug_pub.add('Before getting the header_charges');
5185 END IF;
5186
5187 i := l_pay_now_total_detail_tbl.count + 1;
5188 --bug5223078 start
5189 l_hdr_term_id := OE_Order_Cache.g_header_rec.payment_term_id;
5190 --appending the header level charges only if l_hdr_term_id IS NOT NULL
5191 IF l_hdr_term_id IS NULL THEN
5192 IF l_debug_level > 0 THEN
5193 oe_debug_pub.add('Not passing the header level charges to AR API since the header level payment term is null');
5194 END IF;
5195 END IF;
5196 --bug5223078 end
5197 END IF;
5198
5199 -- append header level charges to the detail line table
5200 IF p_line_id IS NULL AND
5201 l_hdr_term_id IS NOT NULL THEN
5202 --bug5009908
5203 oe_order_cache.load_order_header(p_header_id);
5204 l_pay_now_total_detail_tbl(i).line_id := null;
5205 l_pay_now_total_detail_tbl(i).line_amount :=0;
5206 l_pay_now_total_detail_tbl(i).tax_amount :=0;
5207 l_pay_now_total_detail_tbl(i).freight_amount :=
5208 OE_VERIFY_PAYMENT_PUB.Outbound_Order_Total
5209 (p_header_id => p_header_id
5210 ,p_total_type => 'HEADER_CHARGES'
5211 );
5212 l_pay_now_total_detail_tbl(i).Term_id := l_hdr_term_id;
5213 END IF;
5214
5215 --bug5223078
5216 IF l_pay_now_total_detail_tbl.count > 0 THEN
5217
5218 IF OE_Prepayment_Util.Get_Installment_Options = 'ENABLE_PAY_NOW' THEN
5219 -- calling AR API to get pay now total
5220 AR_VIEW_TERM_GRP.pay_now_amounts
5221 (p_api_version => 1.0
5222 ,p_init_msg_list => FND_API.G_TRUE
5223 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
5224 ,p_currency_code => OE_Order_Cache.g_header_rec.transactional_curr_code
5225 ,p_amounts_tbl => l_pay_now_total_detail_tbl
5226 ,x_pay_now_summary_rec => l_pay_now_total_summary_rec
5227 ,x_return_status => x_return_status
5228 ,x_msg_count => x_msg_count
5229 ,x_msg_data => x_msg_data
5230 );
5231 IF l_debug_level > 0 THEN
5232 oe_debug_pub.add('x_return_status after calling AR_VIEW_TERM_GRP.pay_now_amounts : '|| x_return_status);
5233 END IF;
5234
5235 ELSIF OE_Prepayment_Util.Get_Installment_Options ='AUTHORIZE_FIRST_INSTALLMENT' THEN
5236 -- the following API is used to get the values for tax, charges, subtotal and total
5237 -- for first installment.
5238 Get_First_Installment
5239 (p_currency_code => OE_Order_Cache.g_header_rec.transactional_curr_code
5240 ,p_x_due_now_total_detail_tbl => l_pay_now_total_detail_tbl
5241 ,x_due_now_total_summary_rec => l_pay_now_total_summary_rec
5242 ,x_return_status => x_return_status
5243 ,x_msg_count => x_msg_count
5244 ,x_msg_data => x_msg_data
5245 );
5246
5247
5248
5249 END IF;
5250
5251 END IF; --l_pay_now_total_detail_tbl.count > 0
5252
5253
5254 l_pay_now_commitment := 0;
5255
5256 --bug5223078
5257 IF l_pay_now_total_detail_tbl.count > 0 THEN
5258 FOR i IN l_pay_now_total_detail_tbl.FIRST..l_pay_now_total_detail_tbl.LAST LOOP
5259 IF l_pay_now_total_detail_tbl(i).line_id IS NOT NULL AND
5260 l_pay_now_total_detail_tbl(i).total_amount > 0 THEN
5261
5262 l_pay_now_commitment := l_pay_now_commitment +
5263 OE_Verify_Payment_PUB.Get_Line_Total
5264 (p_line_id => l_pay_now_total_detail_tbl(i).line_id
5265 ,p_header_id => l_header_id
5266 ,p_currency_code => l_currency_code
5267 ,p_level => NULL
5268 ,p_amount_type => 'COMMITMENT'
5269 );
5270 END IF;
5271
5272 END LOOP;
5273
5274
5275 -- pass the pay now amounts back to caller
5276 x_pay_now_subtotal := l_pay_now_total_summary_rec.line_amount;
5277 x_pay_now_tax := l_pay_now_total_summary_rec.tax_amount;
5278 x_pay_now_charges := l_pay_now_total_summary_rec.freight_amount;
5279 x_pay_now_total := l_pay_now_total_summary_rec.total_amount;
5280 x_pay_now_commitment := l_pay_now_commitment;
5281
5282 ELSE
5283
5284 x_pay_now_subtotal := 0;
5285 x_pay_now_tax := 0;
5286 x_pay_now_charges := 0;
5287 x_pay_now_total := 0;
5288 x_pay_now_commitment := 0;
5289
5290 END IF;
5291
5292
5293 IF l_debug_level > 0 THEN
5294 oe_debug_pub.add('x_pay_now_subtotal: ' || x_pay_now_subtotal);
5295 oe_debug_pub.add('x_pay_now_tax: ' || x_pay_now_tax);
5296 oe_debug_pub.add('x_pay_now_charges: ' || x_pay_now_charges);
5297 oe_debug_pub.add('x_pay_now_total: ' || x_pay_now_total);
5298 oe_debug_pub.add('EXITING OE_PREPAYMENT_PVT.GET_PAY_NOW_AMOUNTS');
5299 END IF;
5300
5301
5302 EXCEPTION
5303 WHEN OTHERS THEN
5304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5305 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
5306 FND_MSG_PUB.Add_Exc_Msg
5307 (G_PKG_NAME, 'Get_Pay_Now_Amounts'
5308 );
5309 END IF;
5310
5311 END Get_Pay_Now_Amounts;
5312
5313
5314 END OE_PrePayment_PVT ;