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