[Home] [Help]
PACKAGE BODY: APPS.OE_PAYMENT_TRXN_UTIL
Source
1 PACKAGE BODY OE_PAYMENT_TRXN_UTIL AS
2 /* $Header: OEXUPTXB.pls 120.44.12020000.8 2013/04/10 15:05:47 suthumma ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_PAYMENT_TRXN_UTIL';
4
5
6 --9092936 start
7 PROCEDURE Process_Credit_Card
8 (p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type
9 , p_credit_card IN IBY_FNDCPT_SETUP_PUB.CreditCard_rec_Type
10 , p_assignment_attribs IN IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type
11 , p_assign_id OUT NOCOPY /* file.sql.39 change */ NUMBER
12 , p_response OUT NOCOPY /* file.sql.39 change */ IBY_FNDCPT_COMMON_PUB.Result_rec_type
13 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
14 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
15 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
16 )
17 IS
18
19 PRAGMA AUTONOMOUS_TRANSACTION;
20 --
21 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
22 --
23 BEGIN
24
25 p_return_status := FND_API.G_RET_STS_SUCCESS;
26
27 IBY_FNDCPT_SETUP_PUB.Process_Credit_Card
28 (
29 p_api_version => 1.0,
30 p_commit => FND_API.G_FALSE,
31 X_return_status => p_return_status,
32 X_msg_count => p_msg_count,
33 X_msg_data => p_msg_data,
34 P_payer => p_payer,
35 P_credit_card => p_credit_card,
36 P_assignment_attribs => p_assignment_attribs,
37 X_assign_id => p_assign_id,
38 X_response => p_response
39 );
40
41 IF p_return_status =FND_API.G_RET_STS_SUCCESS THEN
42 COMMIT;
43 END IF;
44
45 EXCEPTION
46 WHEN OTHERS THEN
47 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
48 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
49 THEN
50 FND_MSG_PUB.Add_Exc_Msg
51 ( G_PKG_NAME
52 , 'Process_Credit_Card'
53 );
54 END IF;
55
56 OE_MSG_PUB.Count_And_Get
57 ( p_count => p_msg_count,
58 p_data => p_msg_data
59 );
60 END Process_Credit_Card;
61 --9092936 end
62
63 Procedure Create_Payment_Trxn
64 (p_header_id IN NUMBER,
65 P_line_id IN NUMBER,
66 p_cust_id IN NUMBER,
67 P_site_use_id IN NUMBER,
68 P_payment_trx_id IN NUMBER,
69 P_payment_type_code IN VARCHAR2,
70 p_payment_number IN NUMBER, --Newly added
71 P_card_number IN VARCHAR2 DEFAULT NULL,
72 p_card_code IN VARCHAR2 DEFAULT NULL,
73 P_card_holder_name IN VARCHAR2 DEFAULT NULL,
74 P_exp_date IN VARCHAR2 DEFAULT NULL,
75 P_instrument_security_code IN VARCHAR2 DEFAULT NULL,
76 P_credit_card_approval_code IN VARCHAR2 DEFAULT NULL,
77 P_credit_card_approval_date IN DATE DEFAULT NULL,
78 p_instrument_id IN NUMBER DEFAULT NULL,
79 p_instrument_assignment_id IN NUMBER DEFAULT NULL,
80 p_receipt_method_id IN NUMBER,
81 p_update_card_flag IN VARCHAR2 DEFAULT 'N',
82 P_x_trxn_extension_id IN OUT NOCOPY NUMBER,
83 X_return_status OUT NOCOPY VARCHAR2,
84 X_msg_count OUT NOCOPY NUMBER,
85 X_msg_data OUT NOCOPY VARCHAR2)
86
87 IS
88 --R12 CC Encryption
89 L_credit_card_rec IBY_FNDCPT_SETUP_PUB.CreditCard_rec_Type;
90 L_card_exists VARCHAR2(1) := 'N';
91 L_return_status VARCHAR2(30);
92 L_msg_count NUMBER;
93 L_msg_data VARCHAR2(2000);
94 L_party_id NUMBER;
95 L_response_code IBY_FNDCPT_COMMON_PUB.Result_rec_type;
96 l_org_id NUMBER;
97 l_org_type VARCHAR2(80) := 'OPERATING_UNIT'; --Verify
98 L_card_id NUMBER;
99 L_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
100 L_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
101 L_instrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
102 L_assign_id NUMBER;
103 L_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
104 L_pmt_channel_code IBY_FNDCPT_PMT_CHNNLS_VL.payment_channel_code%TYPE;
105 l_instrument_type IBY_FNDCPT_PMT_CHNNLS_VL.instrument_type%TYPE;
106 --l_instrument_id IBY_FNDCPT_PAYER_ALL_INSTRS_V.instrument_id%TYPE;
107 l_trxn_extension_id NUMBER;
108 l_invoice_to_org_id OE_ORDER_LINES_ALL.invoice_to_org_id%TYPE;
109 l_err_message VARCHAR2(4000);
110 l_instrument_security_code VARCHAR2(30);
111 l_cust_account_id NUMBER;
112 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
113
114 l_party_site_id NUMBER;
115 l_instrument_id NUMBER; -- bug 5170754
116
117 BEGIN
118 IF l_debug_level > 0 THEN
119 oe_debug_pub.add('ENTERING OE_PAYMENT_TRXN_UTIL.Create_Payment_Trxn....');
120 oe_debug_pub.add('Instrument security code and payment number.....'||p_instrument_security_code||' and '||p_payment_number);
121 oe_debug_pub.add('Instrument id'||p_instrument_id);
122 oe_debug_pub.add('Instrument assgn id'||p_instrument_assignment_id);
123 oe_debug_pub.add('Trxn extension id'||P_x_trxn_extension_id);
124 END IF;
125 BEGIN
126
127 -- map payment type to the payment channel
128 IF p_payment_type_code IN( 'CREDIT_CARD') THEN
129 L_pmt_channel_code := p_payment_type_code;
130 ELSIF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
131 /*
132 select arm.payment_channel_code
133 into l_pmt_channel_code
134 from ar_receipt_methods arm
135 where arm.receipt_method_id = p_receipt_method_id;
136 */
137 l_pmt_channel_code := 'BANK_ACCT_XFER';
138 END IF;
139 IF l_debug_level > 0 THEN
140 oe_debug_pub.add('ksurendr payment channel code '||l_pmt_channel_code);
141 END IF;
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 IF l_debug_level > 0 THEN
146 oe_debug_pub.add('Error in getting payment channel code'||l_pmt_channel_code);
147 oe_debug_pub.add('Return status'||l_return_status||l_msg_data);
148 RAISE;
149 END IF;
150 END;
151
152 -- BEGIN
153 IF p_line_id is not null then
154 Select INVOICE_TO_ORG_ID,ORG_ID into l_invoice_to_org_id,l_org_id
155 from oe_order_lines_all where header_id = p_header_id
156 and line_id = p_line_id;
157 ELSE
158 /*
159 select invoice_to_org_id,org_id into l_invoice_to_org_id,l_org_id
160 from oe_order_headers_all where header_id = p_header_id;
161 */
162 -- get cached value.
163 oe_order_cache.load_order_header(p_header_id);
164 l_invoice_to_org_id := OE_Order_Cache.g_header_rec.invoice_to_org_id;
165 l_org_id := OE_Order_Cache.g_header_rec.org_id;
166 END IF;
167 IF l_debug_level > 0 THEN
168 oe_debug_pub.add('Invoice_to_org_id'||l_invoice_to_org_id);
169 oe_debug_pub.add('org id'||l_org_id);
170 END IF;
171 /*
172 EXCEPTION
173 WHEN OTHERS THEN
174 l_err_message := SQLERRM;
175 IF l_debug_level > 0 THEN
176 oe_debug_pub.add('Error in getting org id'||l_err_message);
177 oe_debug_pub.add('Invoice to org id'||l_invoice_to_org_id);
178 oe_debug_pub.add('Org id'||l_org_id);
179 RAISE;
180 END IF;
181 END;
182 */
183
184 BEGIN
185 Select hca.party_id, acct_site.cust_account_id,acct_site.party_site_id
186 Into l_party_id, l_cust_account_id,l_party_site_id
187 From HZ_CUST_SITE_USES_ALL SITE,
188 HZ_CUST_ACCT_SITES ACCT_SITE,
189 HZ_CUST_ACCOUNTS_ALL HCA
190 Where SITE.SITE_USE_ID = p_site_use_id
191 AND SITE.SITE_USE_CODE = 'BILL_TO'
192 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
193 AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
194 AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
195
196 IF l_debug_level > 0 THEN
197 oe_debug_pub.add('Party id in Create payment trxn'||l_party_id);
198 END IF;
199
200 l_payer.payment_function := 'CUSTOMER_PAYMENT';
201 l_payer.party_id := l_party_id;
202 l_payer.org_type := l_org_type;
203 l_payer.org_id := l_org_id;
204 l_payer.cust_account_id := l_cust_account_id;
205 l_payer.account_site_id := p_site_use_id;
206 IF l_debug_level > 0 THEN
207 oe_debug_pub.add('Cust id and acct site id'||p_cust_id||'and'||p_site_use_id);
208 oe_debug_pub.add('Payer context values');
209 oe_debug_pub.add('Payment function --- CUSTOMER_PAYMENT');
210 oe_debug_pub.add('PARTY ID'||l_party_id);
211 oe_debug_pub.add('org_id'||l_org_id);
212 oe_debug_pub.add('org type'||l_org_type);
213 oe_debug_pub.add('cust acct id'||l_cust_account_id);
214 oe_debug_pub.add('account site id'||p_site_use_id);
215 END IF;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 IF l_debug_level > 0 THEN
220 oe_debug_pub.add('Error in PARTY ID'||l_party_id);
221 oe_debug_pub.add('Invoice to org id'||l_invoice_to_org_id);
222 oe_debug_pub.add('Org id'||l_org_id);
223 oe_debug_pub.add('payment_number'||p_payment_number);
224 oe_debug_pub.add('Return status'||l_return_status||l_msg_data);
225 RAISE FND_API.G_EXC_ERROR;
226 END IF;
227 END;
228
229 --Getting the instrument type based on
230 --payment channel code
231 SELECT ifapc.instrument_type
232 INTO l_instrument_type
233 FROM iby_fndcpt_all_pmt_channels_v ifapc
234 WHERE ifapc.payment_channel_code = l_pmt_channel_code;
235
236 --if p_x_trxn_extension_id is null, then create a new id using the IN data.
237 --If it is not null, then we first get the instrument assignment id for
238 --the p_x_trxn_extension_id and use this assignment id to create a new
239 --trxn_extension_id, this is used for Copy Order
240 IF p_x_trxn_extension_id IS NULL THEN
241 -- get the l_trxn_attribs information
242 l_instrument.instrument_id := p_instrument_id;
243 l_instrument.instrument_type := l_instrument_type;
244 l_assignment_attribs.instrument := l_instrument;
245
246 IF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
247
248 --bug 5170754
249 /*IF p_payment_trx_id IS NOT NULL THEN
250 L_assign_id := p_payment_trx_id;
251 ELSE*/
252
253 --Since the ACH LOV shows the bank account numbers belonging
254 --to different assignments (bill to), always calling the
255 --set_payer_assignment API of payments to get the assignment
256 --id even though the assignment id is passed from the front end.
257
258 IF p_payment_trx_id IS NOT NULL THEN
259 IF l_debug_level > 0 THEN
260 oe_debug_pub.add('Assignment id for ach / direct debit --> '||p_payment_trx_id);
261 END IF;
262
263 SELECT INSTRUMENT_ID into
264 l_instrument_id from
265 IBY_FNDCPT_PAYER_ASSGN_INSTR_V
266 where INSTR_ASSIGNMENT_ID = p_payment_trx_id;
267
268 l_assignment_attribs.instrument.instrument_id := l_instrument_id;
269 l_assignment_attribs.Assignment_Id := p_payment_trx_id; -- bug 9857904
270 END IF;
271
272 IF l_debug_level > 0 THEN
273 oe_debug_pub.add('Before call to Set payer instr assignment API...');
274 oe_debug_pub.add('Assignment attributes passed ');
275 oe_debug_pub.add('l_instrument.instrument_id ---> '||p_instrument_id);
276 oe_debug_pub.add('l_instrument.instrument_type -> '||l_instrument_type);
277 END IF;
278
279 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
280 (p_api_version => 1.0,
281 p_commit => FND_API.G_FALSE,
282 X_return_status => l_return_status,
283 X_msg_count => l_msg_count,
284 X_msg_data => l_msg_data,
285 P_payer => l_payer,
286 P_assignment_attribs => l_assignment_attribs,
287 X_assign_id => l_assign_id,
288 X_response => l_response_code);
289
290 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
291 -- 16604394
292 -- Capturing the return status from IBY API and displaying the appropriate error message on the UI
293 IF l_response_code.result_code = 'INVALID_CARD_NUMBER' THEN
294 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
295 OE_MSG_PUB.ADD;
296 IF l_debug_level > 0 THEN
297 oe_debug_pub.add( 'OEXUPTXB: Invalid card number or expiration date in set payer instr assignment' || l_response_code.result_code ) ;
298 END IF;
299 ELSIF l_response_code.result_code = 'INVALID_ADDRESS' THEN
300 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_INVALID');
301 OE_MSG_PUB.ADD;
302 IF l_debug_level > 0 THEN
303 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address in set payer instr assignment' || l_response_code.result_code) ;
304 END IF;
305 ELSIF l_response_code.result_code = 'INVALID_CARD_ISSUER' THEN
306 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5012613
307 OE_MSG_PUB.ADD;
308 IF l_debug_level > 0 THEN
309 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address in set payer instr assignment' || l_response_code.result_code) ;
310 END IF;
311 ELSE --Setting a generic message bug 5244099
312 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
313 OE_MSG_PUB.ADD;
314 IF l_debug_level > 0 THEN
315 oe_debug_pub.add( 'OEXUPTXB: Setting the generic message in set payer instr assignment' || l_response_code.result_code) ;
316 END IF;
317 -- 16604394
318 END IF;
319 RAISE FND_API.G_EXC_ERROR;
320 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
321 -- 16604394
322 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5244099
323 OE_MSG_PUB.ADD;
324 -- 16604394
325 IF l_debug_level > 0 THEN
326 oe_debug_pub.add('Unexpected result error code in Set_Payer_Instr_Assignment-->'||l_response_code.result_code);
327 END IF;
328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
329 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
330 IF l_debug_level > 0 THEN
331 oe_debug_pub.add('Set Payer instr assignment Successful....');
332 oe_debug_pub.add('After calling Set_Payer_Instr_Assignment');
333 oe_debug_pub.add('Instr assignment id'||l_assign_id);
334 END IF;
335 END IF;
336 --END IF; bug 5170754
337 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
338
339 l_assignment_attribs.Assignment_Id := p_instrument_assignment_id; --BUG#10066595
340 IF p_update_card_flag = 'Y' AND p_instrument_id is not null THEN
341 L_credit_card_rec.expiration_date := p_exp_date;
342 l_credit_card_rec.Card_Holder_Name := p_card_holder_name;
343 l_credit_Card_rec.card_id := p_instrument_id;
344 IF l_debug_level > 0 THEN
345 oe_debug_pub.add('Before call to Update_Card API....');
346 --oe_debug_pub.add('Expiration date --> '||p_exp_date);
347 --oe_debug_pub.add('Holder Name ------> '||p_card_holder_name);
348 oe_debug_pub.add('Instrument id ----> '||p_instrument_id);
349 END IF;
350 IBY_FNDCPT_SETUP_PUB.Update_Card
351 (
352 p_api_version => 1.0,
353 p_init_msg_list => FND_API.G_TRUE,
354 p_commit => FND_API.G_FALSE,
355 x_return_status => l_return_status,
356 X_msg_count => l_msg_count,
357 X_msg_data => l_msg_data,
358 p_card_instrument => l_credit_card_rec,
359 x_response => l_response_code
360 );
361 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
362 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
363 IF l_debug_level > 0 THEN
364 oe_debug_pub.add('Result error code in Update_Card exp'||l_response_code.result_code);
365 END IF;
366 RAISE FND_API.G_EXC_ERROR;
367 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
368 IF l_debug_level > 0 THEN
369 oe_debug_pub.add('Result error code in Update_Card unxc'||l_response_code.result_code);
370 END IF;
371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
372 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
373 IF l_debug_level > 0 THEN
374 oe_debug_pub.add('Update_Card Successful....');
375 oe_debug_pub.add('Return status '||l_return_status);
376 END IF;
377 END IF;
378 END IF;
379
380 IF p_instrument_assignment_id IS NOT NULL THEN
381 IF l_debug_level > 0 THEN
382 oe_debug_pub.add('Before call to Set payer instr assignment API...');
383 oe_debug_pub.add('Assignment attributes passed ');
384 oe_debug_pub.add('l_instrument.instrument_id ---> '||p_instrument_id);
385 oe_debug_pub.add('l_instrument.instrument_type -> '||l_instrument_type);
386 END IF;
387
388 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
389 (p_api_version => 1.0,
390 p_commit => FND_API.G_FALSE,
391 X_return_status => l_return_status,
392 X_msg_count => l_msg_count,
393 X_msg_data => l_msg_data,
394 P_payer => l_payer,
395 P_assignment_attribs => l_assignment_attribs,
396 X_assign_id => l_assign_id,
397 X_response => l_response_code);
398
399 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
400 -- 16604394
401 -- Capturing the return status from IBY API and displaying the appropriate error message on the UI
402 IF l_response_code.result_code = 'INVALID_CARD_NUMBER' THEN
403 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
404 OE_MSG_PUB.ADD;
405 IF l_debug_level > 0 THEN
406 oe_debug_pub.add( 'OEXUPTXB: Invalid card number or expiration date in set payer instr assignment ' || l_response_code.result_code ) ;
407 END IF;
408 ELSIF l_response_code.result_code = 'INVALID_ADDRESS' THEN
409 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_INVALID');
410 OE_MSG_PUB.ADD;
411 IF l_debug_level > 0 THEN
412 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address in set payer instr assignment ' || l_response_code.result_code) ;
413 END IF;
414 ELSIF l_response_code.result_code = 'INVALID_CARD_ISSUER' THEN
415 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5012613
416 OE_MSG_PUB.ADD;
417 IF l_debug_level > 0 THEN
418 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address in set payer instr assignment ' || l_response_code.result_code) ;
419 END IF;
420 ELSE --Setting a generic message bug 5244099
421 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
422 OE_MSG_PUB.ADD;
423 IF l_debug_level > 0 THEN
424 oe_debug_pub.add( 'OEXUPTXB: Setting the generic message in set payer instr assignment ' || l_response_code.result_code) ;
425 END IF;
426 -- 16604394
427 END IF;
428 RAISE FND_API.G_EXC_ERROR;
429 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
430 -- 16604394
431 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5244099
432 OE_MSG_PUB.ADD;
433 -- 16604394
434 IF l_debug_level > 0 THEN
435 oe_debug_pub.add('Unexpected result error code in Set_Payer_Instr_Assignment-->'||l_response_code.result_code);
436 END IF;
437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
438 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
439 IF l_debug_level > 0 THEN
440 oe_debug_pub.add('Set Payer instr assignment Successful....');
441 oe_debug_pub.add('After calling Set_Payer_Instr_Assignment');
442 oe_debug_pub.add('Instr assignment id'||l_assign_id);
443 END IF;
444 END IF;
445 --l_assign_id := p_instrument_assignment_id;
446 ELSE
447 IF l_debug_level > 0 THEN
448 oe_debug_pub.add('Values of credit card passed to process_credit_card');
449 oe_debug_pub.add('Owner id'||l_party_id);
450 oe_debug_pub.add('site use id /invoice to org id'||p_site_use_id);
451 oe_debug_pub.add('l_party_site_id/stmt billing address id'||l_party_site_id);
452 --oe_debug_pub.add('card number'||p_card_number);
453 --oe_debug_pub.add('expiration date'||p_exp_date);
454 oe_debug_pub.add('instrument_type'||l_instrument_type);
455 --oe_debug_pub.add('Card issuer'||p_card_code);
456 oe_debug_pub.add('Instrument id'||l_assignment_attribs.instrument.instrument_id);
457 oe_debug_pub.add('call to process credit card');
458 END IF;
459
460 L_credit_card_rec.owner_id := l_party_id;
461 L_credit_card_rec.billing_address_id := l_party_site_id;
462 L_credit_card_rec.card_number := p_card_number;
463 L_credit_card_rec.expiration_date := p_exp_date;
464 L_credit_card_rec.instrument_type := l_instrument_type;
465 L_credit_card_rec.Card_Issuer := p_card_code;
466 l_credit_card_rec.Card_Holder_Name := p_card_holder_name;
467 --bug 5176015
468 IF L_credit_card_rec.card_number IS NOT NULL THEN
469 --13488830 /*9092936 start
470 IBY_FNDCPT_SETUP_PUB.Process_Credit_Card
471 (
472 p_api_version => 1.0,
473 p_commit => FND_API.G_FALSE,
474 X_return_status => l_return_status,
475 X_msg_count => l_msg_count,
476 X_msg_data => l_msg_data,
477 P_payer => l_payer,
478 P_credit_card => l_credit_card_rec,
479 P_assignment_attribs => l_assignment_attribs,
480 X_assign_id => l_assign_id,
481 X_response => l_response_code
482 );
483 --13488830 9092936 end*/
484 --9092936 start
485 /*13488830
486 Process_Credit_Card
487 (
488 P_payer => l_payer,
489 P_credit_card => l_credit_card_rec,
490 P_assignment_attribs => l_assignment_attribs,
491 p_assign_id => l_assign_id,
492 p_response => l_response_code,
493 p_msg_count => l_msg_count,
494 p_msg_data => l_msg_data,
495 p_return_status => l_return_status
496 );
497 13488830*/
498 --9092936 end
499
500 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
501
502 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
503 IF l_debug_level > 0 THEN
504 oe_debug_pub.add('Result error code in Process_Credit_Card -->'||l_response_code.result_code);
505 END IF;
506 IF l_response_code.result_code = 'INVALID_CARD_NUMBER' THEN
507 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
508 OE_MSG_PUB.ADD;
509 IF l_debug_level > 0 THEN
510 oe_debug_pub.add( 'OEXUPTXB: Invalid card number or expiration date' ) ;
511 END IF;
512 ELSIF l_response_code.result_code = 'INVALID_ADDRESS' THEN
513 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_INVALID');
514 OE_MSG_PUB.ADD;
515 IF l_debug_level > 0 THEN
516 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address' ) ;
517 END IF;
518 ELSIF l_response_code.result_code = 'INVALID_CARD_ISSUER' THEN
519 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5012613
520 OE_MSG_PUB.ADD;
521 IF l_debug_level > 0 THEN
522 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address' ) ;
523 END IF;
524 ELSE --Setting a generic message bug 5244099
525 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
526 OE_MSG_PUB.ADD;
527 IF l_debug_level > 0 THEN
528 oe_debug_pub.add( 'OEXUPTXB: Setting the generic message' ) ;
529 END IF;
530 END IF;
531 RAISE FND_API.G_EXC_ERROR;
532 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
533 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5244099
534 OE_MSG_PUB.ADD;
535 IF l_debug_level > 0 THEN
536 oe_debug_pub.add('Unexpected result error code in Process_Credit_Card-->'||l_response_code.result_code);
537 END IF;
538 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
539 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
540 IF l_debug_level > 0 THEN
541 oe_debug_pub.add('Process_Credit_Card assignment Successful....');
542 oe_debug_pub.add('After calling Process_Credit_Card');
543 oe_debug_pub.add('Instr assignment id'||l_assign_id);
544 END IF;
545 --If trxn extension id is null and approval code is not, then
546 --this approval code was obtained from outside payments system
547 --and we need to set up voice authorization in this case.
548 IF p_credit_card_approval_code IS NOT NULL THEN
549 L_trxn_attribs.VoiceAuth_flag := 'Y';
550 L_trxn_attribs.VoiceAuth_code := p_credit_card_approval_code;
551 L_trxn_attribs.VoiceAuth_date := p_credit_card_approval_date;
552 END IF;
553 END IF; -- return status
554 END IF;
555 --bug 5176015
556 END IF; -- assignment id not null
557 END IF;
558 --No need to create trxn extension id for check payments
559 L_trxn_attribs.order_id := p_header_id;
560 l_trxn_attribs.trxn_ref_number2 := p_payment_number;
561
562 -- store the line id in trx_ref_number1 if this is a line level payment
563 IF p_line_id IS NOT NULL THEN
564 l_trxn_attribs.trxn_ref_number1 := p_line_id;
565 END IF;
566
567 ELSE
568 -- p_x_trxn_extension_id is not null, then find the instrument assignment id first,
569 -- then create a new trxn transaction id using this assignment id. This is used in Copy Order.
570 IF l_debug_level > 0 THEN
571 oe_debug_pub.add('Before call to Get Transaction Extension API...');
572 oe_debug_pub.add('Trxn attributes passed ');
573 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 ---> '||p_line_id);
574 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 ---> '||p_payment_number);
575 oe_debug_pub.add('L_trxn_attribs.order_id -----------> '||p_header_id);
576 END IF;
577 IBY_FNDCPT_TRXN_PUB.Get_Transaction_Extension
578 (p_api_version => 1.0,
579 X_return_status => l_return_status,
580 X_msg_count => l_msg_count,
581 X_msg_data => l_msg_data,
582 P_entity_id => p_x_trxn_extension_id,
583 P_payer => l_payer,
584 X_trxn_attribs => l_trxn_attribs,
585 --x_authorized => l_authorized,
586 --x_settled => l_settled,
587 X_response => l_response_code);
588 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
589 IF l_debug_level > 0 THEN
590 oe_debug_pub.add('Result error code in Get_Transaction_Extension'||l_response_code.result_code);
591 END IF;
592 RAISE FND_API.G_EXC_ERROR;
593 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
594 IF l_debug_level > 0 THEN
595 oe_debug_pub.add('Result error code in Get_Transaction_Extension'||l_response_code.result_code);
596 END IF;
597 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
598 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
599 IF l_debug_level > 0 THEN
600 oe_debug_pub.add('Get_Transaction_Extension assignment Successful....');
601 oe_debug_pub.add('After call to Get Transaction Extension'||l_return_status);
602 oe_debug_pub.add('After call to get trxn...instr sec code'||l_trxn_attribs.instrument_security_code);
603 END IF;
604 END IF;
605
606 -- Based on the trxn extension id get the corresponding
607 -- assignment id from the payments table
608 -- bug 8586227
609 select instr_assignment_id into l_assign_id
610 from IBY_EXTN_INSTR_DETAILS_V where trxn_extension_id = p_x_trxn_extension_id;
611 IF l_debug_level > 0 THEN
612 oe_debug_pub.add('Instrument assignment id for existing instrument'||l_assign_id);
613 END IF;
614
615 END IF;
616
617 --<populate data to l_trxn_attribs from l_assignment_attribs, l_credit_card_rec if it is a credit card>
618
619 --Oracle payments requires that the combination of order_id, trxn_ref_number1 and trxn_ref_number2
620 --in the record type l_trxn_attribs must provide a unique transaction identifier for the application
621
622
623 IF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
624 l_trxn_attribs.Originating_Application_Id := 660;
625 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
626 l_trxn_attribs.Originating_Application_Id := 660;
627 --For copy order call, the instrument security code is obtained from
628 --the original order by the call to Get_Transaction_Extension
629 IF p_x_trxn_extension_id is null then
630 l_trxn_attribs.Instrument_Security_Code := p_instrument_security_code;
631 ELSE
632 l_trxn_attribs.Instrument_Security_Code := NULL; --bug 5190146
633 END IF;
634
635 END IF;
636
637 IF l_debug_level > 0 THEN
638 oe_debug_pub.add('Before calling create_transaction extension');
639 oe_debug_pub.add('payment channel -->'||l_pmt_channel_code);
640 oe_debug_pub.add('Assignment id ---->'|| l_assign_id);
641 oe_debug_pub.add('trxn attributes record type values');
642 oe_debug_pub.add('l_trxn_attribs.Instrument_Security_Code --->'||p_instrument_security_code);
643 oe_debug_pub.add('l_trxn_attribs.Originating application id ---> 660');
644 oe_debug_pub.add('l_trxn_attribs.order_id ----> '||p_header_id);
645 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 --->'||p_line_id);
646 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 --->'||p_payment_number);
647 --oe_debug_pub.add('l_trxn_attribs.VoiceAuth_date ---->'||p_credit_card_approval_date);
648 --oe_debug_pub.add('l_trxn_attribs.VoiceAuth_code ---->'||p_credit_card_approval_code);
649 END IF;
650 --bug 5176015
651 IF l_assign_id IS NOT NULL THEN
652
653 -- bug 5575513, regarding authorization for copied order.
654 l_trxn_attribs.order_id := p_header_id;
655 l_trxn_attribs.trxn_ref_number1 := p_line_id;
656
657 IF l_debug_level > 0 THEN
658 oe_debug_pub.add('new l_trxn_attribs.order_id -----> '||l_trxn_attribs.order_id);
659 END IF;
660
661 IBY_Fndcpt_Trxn_Pub.Create_Transaction_Extension
662 (p_api_version => 1.0,
663 p_init_msg_list => FND_API.G_TRUE,
664 p_commit => FND_API.G_FALSE,
665 X_return_status => l_return_status,
666 X_msg_count => l_msg_count,
667 X_msg_data => l_msg_data,
668 P_payer => l_payer,
669 P_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
670 P_pmt_channel => l_pmt_channel_code,
671 P_instr_assignment => l_assign_id,
672 P_trxn_attribs => l_trxn_attribs,
673 x_entity_id => l_trxn_extension_id,
674 X_response => l_response_code);
675
676 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
677
678 IF l_debug_level > 0 THEN
679 oe_debug_pub.add('After calling Create_Transaction_Extension'||l_trxn_extension_id);
680 oe_debug_pub.add('Result code'||l_Response_code.result_code);
681 oe_debug_pub.add('Return status'||l_Return_Status);
682 END IF;
683
684 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
685 IF l_debug_level > 0 THEN
686 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
687 END IF;
688 RAISE FND_API.G_EXC_ERROR;
689 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
690 IF l_debug_level > 0 THEN
691 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
692 END IF;
693 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
694 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
695 IF l_debug_level > 0 THEN
696 oe_debug_pub.add('Create_Transaction_Extension assignment Successful....');
697 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
698 oe_debug_pub.add('After call to create Transaction Extension');
699 oe_debug_pub.add('New trxn extension id'||l_trxn_extension_id);
700 oe_debug_pub.add('Return status'||l_return_status);
701 END IF;
702
703 -- bug 5204275
704 IF p_payment_type_code = 'CREDIT_CARD'
705 AND p_x_trxn_extension_id IS NULL
706 AND p_line_id IS NOT NULL
707 THEN
708 UPDATE oe_payments
709 SET credit_card_approval_code = NULL
710 WHERE header_id = p_header_id
711 AND line_id = p_line_id;
712 END IF;
713
714 END IF;
715 END IF;
716 --bug 5176015
717 P_x_trxn_extension_id := l_trxn_extension_id;
718 X_return_status := FND_API.G_RET_STS_SUCCESS;
719 IF l_debug_level > 0 THEN
720 oe_debug_pub.add('Exiting Create_Payment_Trxn.....');
721 END IF;
722 EXCEPTION
723
724 WHEN FND_API.G_EXC_ERROR THEN
725 l_err_message := SQLERRM;
726 IF l_debug_level > 0 THEN
727 oe_debug_pub.add('Create_Transaction_Extension assignment error....exc');
728 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
729 oe_debug_pub.add('Result code'||l_response_code.result_code);
730 oe_debug_pub.add('Error'||l_err_message);
731 END IF;
732
733 X_return_status := FND_API.G_RET_STS_ERROR;
734 OE_MSG_PUB.Count_And_Get
735 ( p_count => l_msg_count,
736 p_data => l_msg_data
737 );
738 RAISE FND_API.G_EXC_ERROR;
739
740 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
741 l_err_message := SQLERRM;
742 IF l_debug_level > 0 THEN
743 oe_debug_pub.add('Create_Transaction_Extension assignment error....unxc');
744 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
745 oe_debug_pub.add('Result code'||l_response_code.result_code);
746 oe_debug_pub.add('f Error'||l_err_message);
747 END IF;
748
749 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
750 OE_MSG_PUB.Count_And_Get
751 ( p_count => l_msg_count,
752 p_data => l_msg_data
753 );
754 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
755
756 WHEN OTHERS THEN
757 l_err_message := SQLERRM;
758 IF l_debug_level > 0 THEN
759 oe_debug_pub.add('Create_Transaction_Extension assignment error....others');
760 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
761 oe_debug_pub.add('Result code'||l_response_code.result_code);
762 oe_debug_pub.add('trx Error'||l_err_message);
763 END IF;
764
765 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
766 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
767 THEN
768 FND_MSG_PUB.Add_Exc_Msg
769 ( G_PKG_NAME
770 , 'OE_PAYMENT_TRXN_UTIL'
771 );
772 END IF;
773
774 OE_MSG_PUB.Count_And_Get
775 ( p_count => l_msg_count,
776 p_data => l_msg_data
777 );
778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
779 END Create_Payment_Trxn;
780
781 Procedure Update_Payment_Trxn
782 (p_header_id IN NUMBER,
783 P_line_id IN NUMBER,
784 p_cust_id IN NUMBER,
785 P_site_use_id IN NUMBER,
786 p_payment_trx_id IN NUMBER,
787 p_payment_type_code IN VARCHAR2,
788 p_payment_number IN NUMBER, --New
789 p_card_number IN VARCHAR2,
790 P_card_code IN VARCHAR2,
791 p_card_holder_name IN VARCHAR2,
792 p_exp_date IN DATE,
793 p_instrument_security_code IN VARCHAR2,
794 --Bug 7460481 starts
795 P_credit_card_approval_code IN VARCHAR2 DEFAULT NULL,
796 P_credit_card_approval_date IN DATE DEFAULT NULL,
797 --Bug 7460481 ends
798 p_instrument_id IN NUMBER DEFAULT NULL,
799 p_instrument_assignment_id IN NUMBER DEFAULT NULL,
800 p_receipt_method_id IN NUMBER,
801 p_update_card_flag IN VARCHAR2 DEFAULT 'N',
802 p_trxn_extension_id IN OUT NOCOPY NUMBER, --bug 4885313
803 X_return_status OUT NOCOPY VARCHAR2,
804 X_msg_count OUT NOCOPY NUMBER,
805 X_msg_data OUT NOCOPY VARCHAR2)
806
807 IS
808 --R12 CC Encryption
809 L_return_status VARCHAR2(30);
810 L_msg_count NUMBER;
811 L_msg_data VARCHAR2(2000);
812 L_party_id NUMBER;
813 p_card_type VARCHAR2(80);
814 L_response_code IBY_FNDCPT_COMMON_PUB.Result_rec_type;
815 L_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
816 L_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
817 l_org_id NUMBER;
818 l_org_type VARCHAR2(80) := 'OPERATING_UNIT';
819 l_instrument_type IBY_FNDCPT_PMT_CHNNLS_VL.instrument_type%TYPE;
820 l_instrument_id IBY_FNDCPT_PAYER_ALL_INSTRS_V.instrument_id%TYPE := p_instrument_id;
821 l_instrument_assignment_id IBY_FNDCPT_PAYER_ASSGN_INSTR_V.instr_assignment_id%TYPE := p_instrument_assignment_id;
822 L_credit_card_rec IBY_FNDCPT_SETUP_PUB.CreditCard_rec_Type;
823 L_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
824 L_instrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
825 L_assign_id NUMBER;
826 l_invoice_to_org_id OE_ORDER_LINES_ALL.invoice_to_org_id%TYPE;
827 L_pmt_channel_code IBY_FNDCPT_PMT_CHNNLS_VL.payment_channel_code%TYPE;
828 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
829 l_payment_number NUMBER;
830 l_cust_account_id NUMBER; --New
831 l_party_site_id NUMBER;
832 --Bug 4885313
833 l_trxn_extension_id NUMBER;
834
835 --bug 5028932
836 l_approval_code VARCHAR2(80);
837 l_settled_flag VARCHAR2(1);
838 l_effective_auth_amount NUMBER;
839 l_reauthorize_flag VARCHAR2(1);
840
841 --bug 5299050
842 l_old_instrument_id NUMBER;
843 l_old_card_number VARCHAR2(80);
844
845 l_pos NUMBER := 0;
846 l_retry_num NUMBER := 0;
847 --CC Reversal ER#16014135 Start
848 -- Base table it is varchar2 IBY_FNDCPT_TX_EXTENSIONS
849 --l_trxn_ref_number2 NUMBER;
850 l_trxn_ref_number2 VARCHAR2(50);
851 --CC Reversal ER#16014135 end
852
853 BEGIN
854
855
856 IF l_debug_level > 0 THEN
857 oe_debug_pub.add('Entering OE_PAYMENT_TRXN_UTIL.Update_Payment_Trxn...');
858 END IF;
859
860 IF p_payment_type_code IN( 'CREDIT_CARD') THEN
861 L_pmt_channel_code := p_payment_type_code;
862 ELSIF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
863 /*
864 select arm.payment_channel_code
865 into l_pmt_channel_code
866 from ar_receipt_methods arm
867 where arm.receipt_method_id = p_receipt_method_id;
868 */
869
870 l_pmt_channel_code := 'BANK_ACCT_XFER';
871 END IF;
872
873 IF l_debug_level > 0 THEN
874 oe_debug_pub.add('Payment channel code returned --->'||l_pmt_channel_code);
875 END IF;
876
877 --Getting the instrument type based on
878 --payment channel code
879 SELECT ifapc.instrument_type
880 INTO l_instrument_type
881 FROM iby_fndcpt_all_pmt_channels_v ifapc
882 WHERE ifapc.payment_channel_code = l_pmt_channel_code;
883
884 IF l_debug_level > 0 THEN
885 oe_debug_pub.add('Instrument type and instrument id'||l_instrument_type||' and '||l_instrument_id);
886 END IF;
887
888 --Get party id for the bill to site Verify
889
890 IF p_line_id is not null then
891 Select INVOICE_TO_ORG_ID,ORG_ID into l_invoice_to_org_id,l_org_id
892 from oe_order_lines_all where header_id = p_header_id and line_id = p_line_id;
893 else
894 /*
895 select invoice_to_org_id,org_id into l_invoice_to_org_id,l_org_id
896 from oe_order_headers_all where header_id = p_header_id;
897 */
898
899 oe_order_cache.load_order_header(p_header_id);
900 l_invoice_to_org_id := OE_Order_Cache.g_header_rec.invoice_to_org_id;
901 l_org_id := OE_Order_Cache.g_header_rec.org_id;
902 end if;
903
904 IF l_debug_level > 0 THEN
905 oe_debug_pub.add('INVOICE_TO_ORG_ID and ORG_ID'||l_invoice_to_org_id||' and '||l_org_id);
906 END IF;
907
908 Begin
909 Select hca.party_id, acct_site.cust_account_id,acct_site.party_site_id
910 Into l_party_id, l_cust_account_id,l_party_site_id
911 From HZ_CUST_SITE_USES_ALL SITE,
912 HZ_CUST_ACCT_SITES ACCT_SITE,
913 HZ_CUST_ACCOUNTS_ALL HCA
914 Where SITE.SITE_USE_ID = p_site_use_id
915 AND SITE.SITE_USE_CODE = 'BILL_TO'
916 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
917 AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
918 AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
919
920 Exception
921 When No_Data_Found THEN
922 Null;
923 End;
924
925 IF l_debug_level > 0 THEN
926 oe_debug_pub.add('Party id retrieved from hz tables-->'||l_party_id);
927 oe_debug_pub.add('Payment trxid..'||p_payment_trx_id);
928 END IF;
929
930
931 l_payer.payment_function := 'CUSTOMER_PAYMENT';
932 l_payer.party_id := l_party_id;
933 l_payer.org_type := l_org_type;
934 l_payer.org_id := l_org_id;
935 l_payer.cust_account_id := l_cust_account_id;
936 l_payer.account_site_id := p_site_use_id;
937
938 IF l_debug_level > 0 THEN
939 oe_debug_pub.add('Payer context values');
940 oe_debug_pub.add('Payment function --- CUSTOMER_PAYMENT');
941 oe_debug_pub.add('PARTY ID'||l_party_id);
942 oe_debug_pub.add('org_id'||l_org_id);
943 oe_debug_pub.add('org type'||l_org_type);
944 oe_debug_pub.add('cust acct id'||l_cust_account_id);
945 oe_debug_pub.add('account site id'||p_site_use_id);
946 END IF;
947
948 IF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
949 --bug 5170754
950 /*IF p_payment_trx_id IS NOT NULL THEN
951 L_assign_id := p_payment_trx_id;
952 ELSE*/
953
954 --Since the ACH LOV shows the bank account numbers belonging
955 --to different assignments (bill to), always calling the
956 --set_payer_assignment API of payments to get the assignment
957 --id even though the assignment id is passed from the front end.
958 IF p_payment_trx_id IS NOT NULL THEN
959 IF l_debug_level > 0 THEN
960 oe_debug_pub.add('Assignment id for ach / direct debit --> '||p_payment_trx_id);
961 END IF;
962
963 SELECT INSTRUMENT_ID into
964 l_instrument_id from
965 IBY_FNDCPT_PAYER_ASSGN_INSTR_V
966 where INSTR_ASSIGNMENT_ID = p_payment_trx_id;
967 END IF;
968
969
970 l_instrument.instrument_id := l_instrument_id;
971 l_instrument.instrument_type := l_instrument_type;
972 l_assignment_attribs.instrument := l_instrument;
973 l_assignment_attribs.Assignment_Id := p_payment_trx_id; -- bug 9857904
974
975 IF l_debug_level > 0 THEN
976 oe_debug_pub.add('Before call to Set payer instr assignment API...');
977 oe_debug_pub.add('Assignment attributes passed ');
978 oe_debug_pub.add('l_instrument.instrument_id ---> '||l_instrument_id);
979 oe_debug_pub.add('l_instrument.instrument_type -> '||l_instrument_type);
980 END IF;
981
982 --<p_payment_trx_id stores the instr_assignment_id, if not null, then no need to call this API>
983 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
984 (p_api_version => 1.0,
985 p_commit => FND_API.G_FALSE,
986 X_return_status => l_return_status,
987 X_msg_count => l_msg_count,
988 X_msg_data => l_msg_data,
989 P_payer => l_payer,
990 P_assignment_attribs => l_assignment_attribs,
991 X_assign_id => l_assign_id,
992 X_response => l_response_code);
993
994 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
995
996 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
997 IF l_debug_level > 0 THEN
998 oe_debug_pub.add('Result error code in Set_Payer_Instr_Assignment in set payer instr assignment '||l_response_code.result_code);
999 END IF;
1000 IF l_response_code.result_code = 'INVALID_CARD_NUMBER' THEN
1001 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
1002 OE_MSG_PUB.ADD;
1003 IF l_debug_level > 0 THEN
1004 oe_debug_pub.add( 'OEXUPTXB: Invalid card number or expiration date in set payer instr assignment ' ||l_response_code.result_code) ;
1005 END IF;
1006 ELSIF l_response_code.result_code = 'INVALID_ADDRESS' THEN
1007 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_INVALID');
1008 OE_MSG_PUB.ADD;
1009 IF l_debug_level > 0 THEN
1010 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address in set payer instr assignment '||l_response_code.result_code ) ;
1011 END IF;
1012 ELSIF l_response_code.result_code = 'INVALID_CARD_ISSUER' THEN
1013 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5012613
1014 OE_MSG_PUB.ADD;
1015 IF l_debug_level > 0 THEN
1016 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address in set payer instr assignment ' ||l_response_code.result_code) ;
1017 END IF;
1018 END IF;
1019 RAISE FND_API.G_EXC_ERROR;
1020 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1021 IF l_debug_level > 0 THEN
1022 oe_debug_pub.add('Result error code in Set_Payer_Instr_Assignment'||l_response_code.result_code);
1023 END IF;
1024 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1025 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1026 IF l_debug_level > 0 THEN
1027 oe_debug_pub.add('Set_Payer_Instr_Assignment assignment Successful....');
1028 oe_debug_pub.add('After calling Set_Payer_Instr_Assignment');
1029 oe_debug_pub.add('Instr assignment id'||l_assign_id);
1030 END IF;
1031 END IF;
1032
1033 --END IF; --bug 5170754
1034 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
1035
1036 L_assignment_attribs.Assignment_Id := p_instrument_assignment_id; --BUG#10066595
1037 IF l_debug_level > 0 THEN
1038 oe_debug_pub.add('l_inst id'||l_instrument_id);
1039 oe_debug_pub.add('Instr assignment id'||l_instrument_assignment_id);
1040 --oe_debug_pub.add('X value'||instr(p_card_number,'X'));
1041 END IF;
1042
1043 IF p_update_card_flag = 'Y' AND l_instrument_id is not null THEN
1044 IF l_debug_level > 0 THEN
1045 oe_debug_pub.add('Before calling update_card..');
1046 --oe_debug_pub.add('Expiration date passed...'||p_exp_date);
1047 --oe_debug_pub.add('Holder name'||p_card_holder_name);
1048 oe_debug_pub.add('Instrument id'||l_instrument_id);
1049 END IF;
1050 L_credit_card_rec.expiration_date := p_exp_date;
1051 l_credit_card_rec.Card_Holder_Name := p_card_holder_name;
1052 l_credit_Card_rec.card_id := l_instrument_id;
1053 IBY_FNDCPT_SETUP_PUB.Update_Card
1054 (
1055 p_api_version => 1.0,
1056 p_init_msg_list => FND_API.G_TRUE,
1057 p_commit => FND_API.G_FALSE,
1058 x_return_status => l_return_status,
1059 X_msg_count => l_msg_count,
1060 X_msg_data => l_msg_data,
1061 p_card_instrument => l_credit_card_rec,
1062 x_response => l_response_code
1063 );
1064 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
1065
1066 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1067 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --12691100
1068 OE_MSG_PUB.ADD; --12691100
1069 IF l_debug_level > 0 THEN
1070 oe_debug_pub.add('Result error code in Update_Card exp'||l_response_code.result_code);
1071 END IF;
1072 RAISE FND_API.G_EXC_ERROR;
1073 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1074 IF l_debug_level > 0 THEN
1075 oe_debug_pub.add('Result error code in Update_Card unxc'||l_response_code.result_code);
1076 END IF;
1077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1078 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1079 IF l_debug_level > 0 THEN
1080 oe_debug_pub.add('Update_Card Successful....');
1081 oe_debug_pub.add('Return status '||l_return_status);
1082 END IF;
1083 END IF; --return status
1084 END IF; --update card flag
1085
1086 IF l_instrument_assignment_id IS NOT NULL THEN
1087 IF l_debug_level > 0 THEN
1088 oe_debug_pub.add('Before call to Set payer instr assignment API...');
1089 oe_debug_pub.add('Assignment attributes passed ');
1090 oe_debug_pub.add('l_instrument.instrument_id ---> '||l_instrument_id);
1091 oe_debug_pub.add('l_instrument.instrument_type -> '||l_instrument_type);
1092 END IF;
1093 l_instrument.instrument_id := l_instrument_id;
1094 l_instrument.instrument_type := l_instrument_type;
1095 l_assignment_attribs.instrument := l_instrument;
1096
1097 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
1098 (p_api_version => 1.0,
1099 p_commit => FND_API.G_FALSE,
1100 X_return_status => l_return_status,
1101 X_msg_count => l_msg_count,
1102 X_msg_data => l_msg_data,
1103 P_payer => l_payer,
1104 P_assignment_attribs => l_assignment_attribs,
1105 X_assign_id => l_assign_id,
1106 X_response => l_response_code);
1107
1108 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1109 -- 16604394
1110 -- Capturing the return status from IBY API and displaying the appropriate error message on the UI
1111 IF l_response_code.result_code = 'INVALID_CARD_NUMBER' THEN
1112 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
1113 OE_MSG_PUB.ADD;
1114 IF l_debug_level > 0 THEN
1115 oe_debug_pub.add( 'OEXUPTXB: Invalid card number or expiration date in set payer instr assignment ' || l_response_code.result_code ) ;
1116 END IF;
1117 ELSIF l_response_code.result_code = 'INVALID_ADDRESS' THEN
1118 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_INVALID');
1119 OE_MSG_PUB.ADD;
1120 IF l_debug_level > 0 THEN
1121 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address in set payer instr assignment ' || l_response_code.result_code) ;
1122 END IF;
1123 ELSIF l_response_code.result_code = 'INVALID_CARD_ISSUER' THEN
1124 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5012613
1125 OE_MSG_PUB.ADD;
1126 IF l_debug_level > 0 THEN
1127 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address in set payer instr assignment ' || l_response_code.result_code) ;
1128 END IF;
1129 ELSE --Setting a generic message bug 5244099
1130 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
1131 OE_MSG_PUB.ADD;
1132 IF l_debug_level > 0 THEN
1133 oe_debug_pub.add( 'OEXUPTXB: Setting the generic message in set payer instr assignment ' || l_response_code.result_code) ;
1134 END IF;
1135 -- 16604394
1136 END IF;
1137 RAISE FND_API.G_EXC_ERROR;
1138 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1139 -- 16604394
1140 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5244099
1141 OE_MSG_PUB.ADD;
1142 IF l_debug_level > 0 THEN
1143 oe_debug_pub.add('Unexpected result error code in Set_Payer_Instr_Assignment-->'||l_response_code.result_code);
1144 END IF;
1145 -- 16604394
1146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1147 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1148 IF l_debug_level > 0 THEN
1149 oe_debug_pub.add('Set Payer instr assignment Successful....');
1150 oe_debug_pub.add('After calling Set_Payer_Instr_Assignment');
1151 oe_debug_pub.add('Instr assignment id'||l_assign_id);
1152 END IF;
1153 END IF;
1154 ELSE
1155 L_credit_card_rec.owner_id := l_party_id;
1156 L_credit_card_rec.billing_address_id := l_party_site_id;
1157 L_credit_card_rec.card_number := p_card_number;
1158 L_credit_card_rec.expiration_date := p_exp_date;
1159 l_credit_card_rec.Card_Holder_Name := p_card_holder_name;
1160 L_credit_card_rec.card_issuer := p_card_code;
1161 L_credit_card_rec.instrument_type := l_instrument_type;
1162
1163 IF l_debug_level > 0 THEN
1164 oe_debug_pub.add('Before calling process credit card....');
1165 oe_debug_pub.add('l_party_site_id/stmt billing add'||l_party_site_id);
1166 oe_debug_pub.add('site use id/invoice to org'||p_site_use_id);
1167 --oe_debug_pub.add('card number'||p_card_number);
1168 --oe_debug_pub.add('expiration date'||p_exp_date);
1169 --oe_debug_pub.add('instrument_type'||l_instrument_type);
1170 --oe_debug_pub.add('Card issuer'||p_card_code);
1171 oe_debug_pub.add('Instrument id'||l_instrument_id);
1172 --oe_debug_pub.add('Holder name'||p_card_holder_name);
1173 oe_debug_pub.add('call to process credit card');
1174 END IF;
1175
1176 --13488830 /*9092936 start
1177 IBY_FNDCPT_SETUP_PUB.Process_Credit_Card
1178 (p_api_version => 1.0,
1179 p_commit => FND_API.G_FALSE,
1180 X_return_status => l_return_status,
1181 X_msg_count => l_msg_count,
1182 X_msg_data => l_msg_data,
1183 P_payer => l_payer,
1184 P_credit_card => l_credit_card_rec,
1185 P_assignment_attribs => l_assignment_attribs,
1186 X_assign_id => l_assign_id,
1187 X_response => l_response_code);
1188 --13488830 9092936 end*/
1189 --9092936 start
1190 /*13488830
1191 Process_Credit_Card
1192 (
1193 P_payer => l_payer,
1194 P_credit_card => l_credit_card_rec,
1195 P_assignment_attribs => l_assignment_attribs,
1196 p_assign_id => l_assign_id,
1197 p_response => l_response_code,
1198 p_msg_count => l_msg_count,
1199 p_msg_data => l_msg_data,
1200 p_return_status => l_return_status
1201 );
1202 13488830*/
1203 --9092936 end
1204
1205 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
1206
1207 IF l_debug_level > 0 THEN
1208 oe_debug_pub.add('After call to process credit card....');
1209 END IF;
1210
1211
1212 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1213 IF l_debug_level > 0 THEN
1214 oe_debug_pub.add('Result error code in Process_Credit_Card -->'||l_response_code.result_code);
1215 END IF;
1216 IF l_response_code.result_code = 'INVALID_CARD_NUMBER' THEN
1217 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
1218 OE_MSG_PUB.ADD;
1219 IF l_debug_level > 0 THEN
1220 oe_debug_pub.add( 'OEXUPTXB: Invalid card number or expiration date' ) ;
1221 END IF;
1222 ELSIF l_response_code.result_code = 'INVALID_ADDRESS' THEN
1223 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_INVALID');
1224 OE_MSG_PUB.ADD;
1225 IF l_debug_level > 0 THEN
1226 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address' ) ;
1227 END IF;
1228 ELSIF l_response_code.result_code = 'INVALID_CARD_ISSUER' THEN
1229 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5012613
1230 OE_MSG_PUB.ADD;
1231 IF l_debug_level > 0 THEN
1232 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address' ) ;
1233 END IF;
1234 ELSE --Setting a generic message bug 5244099
1235 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
1236 OE_MSG_PUB.ADD;
1237 IF l_debug_level > 0 THEN
1238 oe_debug_pub.add( 'OEXUPTXB: Setting the generic message' ) ;
1239 END IF;
1240 END IF;
1241 RAISE FND_API.G_EXC_ERROR;
1242 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1243 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5244099
1244 OE_MSG_PUB.ADD;
1245 IF l_debug_level > 0 THEN
1246 oe_debug_pub.add('Unexpected result error code in Process_Credit_Card-->'||l_response_code.result_code);
1247 END IF;
1248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1249 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1250 IF l_debug_level > 0 THEN
1251 oe_debug_pub.add('Process_Credit_Card assignment Successful....');
1252 oe_debug_pub.add('After calling Process_Credit_Card');
1253 oe_debug_pub.add('Instr assignment id'||l_assign_id);
1254 END IF;
1255 END IF; --Return status
1256 END IF;--Instrument assignment id
1257 END IF;--payment type code
1258 --No need to create trxn extension ids for check payments
1259 --as it would be stored in OM tables itself
1260
1261 L_trxn_attribs.order_id := p_header_id;
1262 L_trxn_attribs.trxn_ref_number2 := p_payment_number;
1263 -- store the line id in trx_ref_number1 if this is a line level payment
1264 IF p_line_id IS NOT NULL THEN
1265 l_trxn_attribs.trxn_ref_number1 := p_line_id;
1266 END IF;
1267 --END IF;
1268 IF OE_Payment_Trxn_Util.g_old_bill_to_site IS NULL THEN
1269 --<populate data to l_trxn_attribs from l_assignment_attribs, l_credit_card_rec if it is a credit card>
1270 --<Update the transaction in the IBY payment trasaction extenstion table>
1271 IF l_debug_level > 0 THEN
1272 oe_debug_pub.add('Calling update transaction extension...');
1273 END IF;
1274
1275 IF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
1276 l_trxn_attribs.Originating_Application_Id := 660;
1277 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
1278 l_trxn_attribs.Originating_Application_Id := 660;
1279
1280 --Need to pass the instrument security code as G_MISS_CHAR
1281 --if the Security code is null to the payments API to update
1282 --the value appropriately.
1283 IF p_instrument_security_code is not null then
1284 l_trxn_attribs.Instrument_Security_Code := p_instrument_security_code;
1285 ELSE
1286 l_trxn_attribs.Instrument_Security_Code := FND_API.G_MISS_CHAR;
1287 END IF;
1288 END IF;
1289
1290 IF l_debug_level > 0 THEN
1291 oe_debug_pub.add('Values passed to Update_trxn_extn');
1292 oe_debug_pub.add('payment channel -->'||l_pmt_channel_code);
1293 oe_debug_pub.add('Assignment id ---->'|| l_assign_id);
1294 oe_debug_pub.add('trxn attributes record type values');
1295 oe_debug_pub.add('l_trxn_attribs.Instrument_Security_Code --->'||p_instrument_security_code);
1296 oe_debug_pub.add('l_trxn_attribs.Originating application id ---> 660');
1297 oe_debug_pub.add('l_trxn_attribs.order_id ----> '||p_header_id);
1298 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 --->'||p_line_id);
1299 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 --->'||p_payment_number);
1300 --oe_debug_pub.add('l_trxn_attribs.VoiceAuth_date ---->'||p_credit_card_approval_date);
1301 --oe_debug_pub.add('l_trxn_attribs.VoiceAuth_code ---->'||p_credit_card_approval_code);
1302 END IF;
1303
1304 --bug 5028932
1305 BEGIN
1306 SELECT AUTHORIZATION_CODE into
1307 l_approval_code FROM IBY_TRXN_EXT_AUTHS_V
1308 WHERE TRXN_EXTENSION_ID = p_trxn_extension_id
1309 --12694453 AND INITIATOR_EXTENSION_ID = p_trxn_extension_id; -- bug 9145261/9335940
1310 --ER#16014135 CC Reversal start
1311 AND nvl(authorization_amount,0) > 0
1312 AND authorization_status=0;
1313 --ER#16014135 CC Reversal end
1314 EXCEPTION
1315 WHEN OTHERS THEN
1316 l_approval_code := null;
1317 IF l_debug_level >0 THEN
1318 oe_debug_pub.add('Others part approval code value ---> '||l_approval_code);
1319 END IF;
1320 END;
1321
1322 -- to check if the authorization has been settled
1323 BEGIN
1324 -- bug 8586227
1325 /*SELECT nvl(settled_flag, 'N'),instrument_id,card_number
1326 INTO l_settled_flag,l_old_instrument_id,l_old_card_number
1327 FROM iby_trxn_extensions_v
1328 WHERE trxn_extension_id = p_trxn_extension_id;*/
1329 SELECT nvl(settled_flag, 'N'),instrument_id,card_number
1330 INTO l_settled_flag,l_old_instrument_id,l_old_card_number
1331 FROM IBY_EXTN_INSTR_DETAILS_V iextn,
1332 IBY_EXTN_SETTLEMENTS_V iset
1333 WHERE
1334 iextn.trxn_extension_id= iset.trxn_extension_id (+) AND
1335 iextn.trxn_extension_id = p_trxn_extension_id;
1336
1337 EXCEPTION WHEN NO_DATA_FOUND THEN
1338 l_settled_flag := 'N';
1339 l_old_instrument_id := NULL;
1340 l_old_card_number := NULL;
1341 END;
1342
1343 IF l_debug_level > 0 THEN
1344 oe_debug_pub.add('Settled Flag value....'||l_settled_flag);
1345 END IF;
1346
1347
1348 --If approval code is not null then the transaction
1349 --extension has already been authorized once. So cannot
1350 --call update_transaction API for this trxn_extension_id
1351 IF (l_approval_code IS NOT NULL AND
1352 NOT OE_GLOBALS.Equal(l_approval_code,FND_API.G_MISS_CHAR))
1353 OR l_settled_flag = 'Y' THEN
1354
1355 IF l_settled_flag = 'N' THEN
1356 -- need to re-authorize if the authorization has expired.
1357 -- effective_auth_amount of 0 indicates auth has expired.
1358 -- the auth would be valid if authorization_amount is equal to
1359 -- effective_auth_amount
1360 BEGIN
1361 SELECT effective_auth_amount
1362 INTO l_effective_auth_amount
1363 FROM iby_trxn_ext_auths_v
1364 WHERE trxn_extension_id = p_trxn_extension_id
1365 --12694453 AND INITIATOR_EXTENSION_ID = p_trxn_extension_id -- bug 9335940/9145261
1366 AND nvl(authorization_amount,0) > 0
1367 AND authorization_status=0;
1368 EXCEPTION WHEN NO_DATA_FOUND THEN
1369 --This case is not possible as the approval code
1370 --for the transaction extension id is not null which
1371 --means that the transaction has been authorized atleast once
1372 NULL;
1373 END;
1374
1375 IF nvl(l_effective_auth_amount,0) = 0 THEN
1376 l_reauthorize_flag := 'Y';
1377 IF l_debug_level > 0 THEN
1378 oe_debug_pub.add( 'OEXUPTXB: authorization has either expired or not exists.');
1379 END IF;
1380 END IF;
1381
1382 IF l_debug_level > 0 THEN
1383 oe_debug_pub.add('Reauthorize flag value ----> '||l_reauthorize_flag);
1384 END IF;
1385 END IF;
1386
1387 IF l_debug_level > 0 THEN
1388 oe_debug_pub.add('Old instrument_id ---> '||l_old_instrument_id);
1389 oe_debug_pub.add('New instrument id..... '||l_instrument_id);
1390 --oe_debug_pub.add('Old card number -----> '||l_old_card_number);
1391 --oe_debug_pub.add('New card number -----> '||p_card_number);
1392 END IF;
1393
1394 IF l_settled_flag = 'Y' OR l_reauthorize_flag = 'Y' THEN
1395
1396 IF l_debug_level > 0 THEN
1397 oe_debug_pub.add( 'OEXUPTXB.pls: authorization has been settled, need to re-authorize.');
1398 END IF;
1399
1400 IF Oe_Payment_Trxn_Util.Get_CC_Security_Code_Use = 'REQUIRED'
1401 AND (l_trxn_attribs.Instrument_Security_Code IS NULL OR
1402 OE_GLOBALS.Equal(l_trxn_attribs.Instrument_Security_Code,FND_API.G_MISS_CHAR))
1403 THEN
1404
1405 FND_MESSAGE.SET_NAME('ONT','OE_CC_SECURITY_CODE_REQD');
1406 OE_Msg_Pub.Add;
1407 RAISE FND_API.G_EXC_ERROR;
1408
1409 ELSIF Oe_Payment_Trxn_Util.Get_CC_Security_Code_Use = 'REQUIRED'
1410 AND l_trxn_attribs.Instrument_Security_Code IS NOT NULL
1411 AND NOT OE_GLOBALS.Equal(l_trxn_attribs.Instrument_Security_Code,FND_API.G_MISS_CHAR)
1412 THEN
1413
1414 -- need to create a new payment transaction extension as the old one has been settled.
1415 IF l_debug_level > 0 THEN
1416 oe_debug_pub.add( 'OEXUPTXB.pls: Before calling Create_New_Payment_Trxn');
1417 oe_debug_pub.add( 'p_trxn_extension --->'||p_trxn_extension_id);
1418 oe_debug_pub.add( 'p_org_id -----> '||l_org_id);
1419 oe_debug_pub.add( 'p_site_use_id ---> '||p_site_use_id);
1420 oe_debug_pub.add( 'l_trxn_extension_id --> '||l_trxn_extension_id);
1421 END IF;
1422
1423 OE_Verify_Payment_PUB.Create_New_Payment_Trxn (p_trxn_extension_id => p_trxn_extension_id,
1424 p_org_id => l_org_id,
1425 p_site_use_id => p_site_use_id,
1426 p_instrument_security_code => l_trxn_attribs.Instrument_Security_Code,
1427 x_trxn_extension_id => l_trxn_extension_id,
1428 x_msg_count => x_msg_count,
1429 x_msg_data => x_msg_data,
1430 x_return_status => x_return_status);
1431
1432 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1433 IF l_debug_level > 0 THEN
1434 oe_debug_pub.add( 'OEXUPTXB.pls: Exp. error in call to Create_New_Payment_Trxn');
1435 oe_debug_pub.add( 'SQL ERRM ----> '||sqlerrm);
1436 END IF;
1437 RAISE FND_API.G_EXC_ERROR;
1438 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1439 IF l_debug_level > 0 THEN
1440 oe_debug_pub.add( 'OEXUPTXB.pls: Unexp. error in call to Create_New_Payment_Trxn');
1441 oe_debug_pub.add( 'SQL ERRM ----> '||sqlerrm);
1442 END IF;
1443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1444 END IF;
1445
1446 IF l_debug_level > 0 THEN
1447 oe_debug_pub.add( 'OEXUPTXB.pls: After successful call to Create_New_Payment_Trxn');
1448 oe_debug_pub.add( 'New trxn extension --->'||l_trxn_extension_id);
1449 END IF;
1450 --CC Reversal ER#16014135 Start
1451 Begin
1452 oe_payment_trxn_util.Reverse_CreditCard_Auth
1453 (p_header_id => p_header_id,
1454 p_line_id => p_line_id,
1455 p_msg_count => l_msg_count,
1456 p_msg_data => l_msg_data,
1457 p_return_status => l_return_status,
1458 p_new_trxn_extension_id =>l_trxn_extension_id
1459 );
1460 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1461 IF l_debug_level > 0 THEN
1462 oe_debug_pub.add('Reverse_CreditCard_Auth error....exc',5);
1463 oe_debug_pub.add('After call to Reverse_CreditCard_Auth'||l_return_status,5);
1464 oe_debug_pub.add('Error'||sqlerrm,5);
1465 END IF;
1466 RAISE FND_API.G_EXC_ERROR;
1467
1468 ELSIF l_return_status= FND_API.G_RET_STS_UNEXP_ERROR THEN
1469 IF l_debug_level > 0 THEN
1470 oe_debug_pub.add('After call to Reverse_CreditCard_Auth --> Unexpected error',5);
1471 oe_debug_pub.add('Error message '||sqlerrm,5);
1472 END IF;
1473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1474 ELSIF l_return_status=FND_API.G_RET_STS_SUCCESS Then
1475 IF l_debug_level > 0 THEN
1476 oe_debug_pub.add('Call to Reverse_CreditCard_Auth --> Success',5);
1477 end if;
1478 END IF;
1479 END;
1480 --CC Reversal ER#16014135 End
1481 -- update oe_payments table
1482 p_trxn_extension_id := l_trxn_extension_id;
1483 END IF; --Security code use ='REQUIRED'
1484 --bug 5299050
1485 ELSIF NOT OE_GLOBALS.Is_Same_Credit_Card(l_old_card_number,
1486 p_card_number,l_old_instrument_id,l_instrument_id) THEN
1487
1488 l_trxn_attribs.Originating_Application_Id := 660;
1489 l_trxn_attribs.Instrument_Security_Code := p_instrument_security_code;
1490
1491 -- bug 5575513
1492 -- per IBY, The combination of order_id, trxn ref1 and
1493 -- trxn ref2 must be different for each trxn extension
1494 -- as they produce the order id used to distinguish
1495 -- payment operations. Since this is going to be a
1496 -- different trxn_extension_id for the same order, we will
1497 -- need to make sure the trxn ref2 is different, as the
1498 -- order id and trxn ref1 would be the same.
1499
1500 oe_debug_pub.add('Linda -- p_trxn_extension_id is: '||p_trxn_extension_id);
1501
1502 BEGIN
1503 -- bug 8586227
1504 select trxn_ref_number2
1505 into l_trxn_ref_number2
1506 from IBY_EXTN_INSTR_DETAILS_V
1507 where trxn_extension_id = p_trxn_extension_id;
1508
1509 EXCEPTION WHEN NO_DATA_FOUND THEN
1510 null;
1511 END;
1512
1513 l_pos := instr(l_trxn_ref_number2,'R');
1514
1515
1516 IF l_pos > 0 THEN
1517 l_retry_num := substr(l_trxn_ref_number2, l_pos+1, length(l_trxn_ref_number2)) + 1;
1518 l_trxn_attribs.trxn_ref_number2 := substr(l_trxn_ref_number2, 1, l_pos)||to_char(l_retry_num);
1519 ELSE
1520 l_retry_num := 1;
1521 l_trxn_attribs.trxn_ref_number2 := l_trxn_ref_number2||'R'||to_char(l_retry_num);
1522 END IF;
1523 -- end of bug 5575513
1524
1525 IF l_debug_level > 0 THEN
1526 oe_debug_pub.add('Before calling create_transaction extension');
1527 oe_debug_pub.add('payment channel -->'||l_pmt_channel_code);
1528 oe_debug_pub.add('Assignment id ---->'|| l_assign_id);
1529 oe_debug_pub.add('trxn attributes record type values');
1530 oe_debug_pub.add('l_trxn_attribs.Instrument_Security_Code --->'||p_instrument_security_code);
1531 oe_debug_pub.add('l_trxn_attribs.Originating application id ---> '||l_trxn_attribs.Originating_application_id);
1532 oe_debug_pub.add('l_trxn_attribs.order_id ----> '||l_trxn_attribs.order_id);
1533 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 --->'||l_trxn_attribs.trxn_ref_number1);
1534 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 --->'||l_trxn_attribs.trxn_ref_number2);
1535 END IF;
1536
1537 IBY_Fndcpt_Trxn_Pub.Create_Transaction_Extension
1538 (p_api_version => 1.0,
1539 p_init_msg_list => FND_API.G_TRUE,
1540 p_commit => FND_API.G_FALSE,
1541 X_return_status => l_return_status,
1542 X_msg_count => l_msg_count,
1543 X_msg_data => l_msg_data,
1544 P_payer => l_payer,
1545 P_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1546 P_pmt_channel => l_pmt_channel_code,
1547 P_instr_assignment => l_assign_id,
1548 P_trxn_attribs => l_trxn_attribs,
1549 x_entity_id => l_trxn_extension_id,
1550 X_response => l_response_code);
1551
1552 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
1553
1554 IF l_debug_level > 0 THEN
1555 oe_debug_pub.add('After calling Create_Transaction_Extension'||l_trxn_extension_id);
1556 oe_debug_pub.add('Result code'||l_Response_code.result_code);
1557 oe_debug_pub.add('Return status'||l_Return_Status);
1558 END IF;
1559
1560 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1561 IF l_debug_level > 0 THEN
1562 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
1563 END IF;
1564 RAISE FND_API.G_EXC_ERROR;
1565 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1566 IF l_debug_level > 0 THEN
1567 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
1568 END IF;
1569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1570 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1571 --CC Reversal ER#16014135 Start
1572 Begin
1573 oe_payment_trxn_util.Reverse_CreditCard_Auth
1574 (p_header_id => p_header_id,
1575 p_line_id => p_line_id,
1576 p_msg_count => l_msg_count,
1577 p_msg_data => l_msg_data,
1578 p_return_status => l_return_status,
1579 p_new_trxn_extension_id => l_trxn_extension_id
1580 );
1581 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1582 IF l_debug_level > 0 THEN
1583 oe_debug_pub.add('Reverse_CreditCard_Auth error....exc',5);
1584 oe_debug_pub.add('After call to Reverse_CreditCard_Auth'||l_return_status,5);
1585 oe_debug_pub.add('Error'||sqlerrm,5);
1586 END IF;
1587 RAISE FND_API.G_EXC_ERROR;
1588 ELSIF l_return_status= FND_API.G_RET_STS_UNEXP_ERROR Then
1589 IF l_debug_level > 0 THEN
1590 oe_debug_pub.add('After call to Reverse_CreditCard_Auth --> Unexpected error',5);
1591 oe_debug_pub.add('Error message '||sqlerrm,5);
1592 END IF;
1593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1594 ELSIF l_return_status=FND_API.G_RET_STS_SUCCESS Then
1595 IF l_debug_level > 0 THEN
1596 oe_debug_pub.add('Call to Reverse_CreditCard_Auth --> Success',5);
1597 end if;
1598 END IF;
1599 END;
1600 --CC Reversal ER#16014135 End
1601 --Setting the trxn extension id to the new value
1602 --as the old trxn extension id was deleted
1603 p_trxn_extension_id := l_trxn_extension_id ;
1604 IF l_debug_level > 0 THEN
1605 oe_debug_pub.add('Create_Transaction_Extension assignment Successful....');
1606 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
1607 oe_debug_pub.add('After call to create Transaction Extension');
1608 oe_debug_pub.add('New trxn extension id'||l_trxn_extension_id);
1609 oe_debug_pub.add('Return status'||l_return_status);
1610 END IF;
1611 END IF;
1612 --bug 5299050
1613 END IF; -- Settled or Expired
1614
1615 --If approval code is null, then this transaction has not yet been authorized
1616 --So can call update transaction API to update the required details for this
1617 --trxn extension id.
1618 ELSE
1619 --Bug 7460481 starts
1620 IF p_payment_type_code = 'CREDIT_CARD'
1621 THEN
1622 IF p_credit_card_approval_code IS NOT NULL THEN
1623 L_trxn_attribs.VoiceAuth_flag := 'Y';
1624 L_trxn_attribs.VoiceAuth_code := p_credit_card_approval_code;
1625 --Bug 8500353
1626 IF p_credit_card_approval_date is not null then
1627 L_trxn_attribs.VoiceAuth_date := p_credit_card_approval_date;
1628 ELSE
1629 L_trxn_attribs.VoiceAuth_date := sysdate;
1630 END IF;
1631 --Bug 8500353
1632 END IF;
1633 END IF;
1634
1635 IF l_debug_level > 0 THEN
1636 oe_debug_pub.add('l_trxn_attribs.VoiceAuth_date ---->'||l_trxn_attribs.VoiceAuth_date);
1637 oe_debug_pub.add('l_trxn_attribs.VoiceAuth_code ---->'||l_trxn_attribs.VoiceAuth_code);
1638 END IF;
1639 --Bug 7460481 ends
1640
1641 IBY_Fndcpt_Trxn_Pub.Update_Transaction_Extension
1642 (p_api_version => 1.0,
1643 p_init_msg_list => FND_API.G_TRUE,
1644 p_commit => FND_API.G_FALSE,
1645 X_return_status => l_return_status,
1646 X_msg_count => l_msg_count,
1647 X_msg_data => l_msg_data,
1648 P_payer => l_payer,
1649 p_entity_id => p_trxn_extension_id,
1650 P_trxn_attribs => l_trxn_attribs,
1651 x_response => l_response_code,
1652 p_pmt_channel => L_pmt_channel_code,
1653 p_instr_assignment => l_assign_id);
1654
1655 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
1656
1657 IF l_debug_level > 0 THEN
1658 oe_debug_pub.add('After calling update transaction extension...');
1659 END IF;
1660
1661 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1662 IF l_debug_level > 0 THEN
1663 oe_debug_pub.add('Result error code in Update_Transaction_Extension'||l_response_code.result_code);
1664 END IF;
1665 RAISE FND_API.G_EXC_ERROR;
1666 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1667 IF l_debug_level > 0 THEN
1668 oe_debug_pub.add('Result error code in Update_Transaction_Extension'||l_response_code.result_code);
1669 END IF;
1670 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1671 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1672 IF l_debug_level > 0 THEN
1673 oe_debug_pub.add('Update_Transaction_Extension assignment Successful....');
1674 oe_debug_pub.add('After calling Update_Transaction_Extension');
1675 END IF;
1676 END IF;
1677 END IF; -- Approval code not null
1678 --bug 5028932
1679 --bug 4885313
1680 ELSIF OE_Payment_Trxn_Util.g_old_bill_to_site IS NOT NULL THEN
1681 IF l_debug_level > 0 THEN
1682 oe_debug_pub.add('Bill to has changed....Need to delete this trxn id as context has changed!');
1683 oe_debug_pub.add('Before calling Delete Transaction Extension API...');
1684 oe_debug_pub.add('Trxn extension id --------> '||p_trxn_extension_id);
1685 oe_debug_pub.add('Payer equivalency --------> '||IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_IMMEDIATE);
1686 END IF;
1687 --Setting the context corresponding to the old bill to
1688 --site as the trxn extension id was created for that site.
1689 l_payer.account_site_id := OE_Payment_Trxn_Util.g_old_bill_to_site;
1690
1691 --Resetting the bill to change flag so as to
1692 --maintain the consistent behaviour in different sessions
1693
1694 OE_Payment_Trxn_Util.g_old_bill_to_site := null;
1695
1696 IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension
1697 (
1698 p_api_version => 1.0,
1699 X_return_status => l_return_status,
1700 X_msg_count => l_msg_count,
1701 X_msg_data => l_msg_data,
1702 p_commit => FND_API.G_FALSE,
1703 P_payer => l_payer,
1704 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_IMMEDIATE,
1705 X_response => l_response_code,
1706 p_entity_id => p_trxn_extension_id);
1707
1708 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1709 IF l_debug_level > 0 THEN
1710 oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
1711 oe_debug_pub.add('sql error'||sqlerrm);
1712 oe_debug_pub.add('msg data'||l_msg_data);
1713 END IF;
1714 RAISE FND_API.G_EXC_ERROR;
1715 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1716 IF l_debug_level > 0 THEN
1717 oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
1718 oe_debug_pub.add('sql error'||sqlerrm);
1719 oe_debug_pub.add('msg data'||l_msg_data);
1720 END IF;
1721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1722 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1723 IF l_debug_level > 0 THEN
1724 oe_debug_pub.add('Delete_Transaction_Extension Successful....');
1725 oe_debug_pub.add('After calling Delete_Transaction_Extension');
1726 oe_debug_pub.add('sql error'||sqlerrm);
1727 oe_debug_pub.add('msg data'||l_msg_data);
1728 END IF;
1729 END IF;
1730
1731 IF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
1732 l_trxn_attribs.Originating_Application_Id := 660;
1733 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
1734 l_trxn_attribs.Originating_Application_Id := 660;
1735 l_trxn_attribs.Instrument_Security_Code := p_instrument_security_code;
1736 /*IF p_instrument_security_code IS NOT NULL AND
1737 NOT OE_GLOBALS.Equal(p_instrument_security_code,FND_API.G_MISS_CHAR) THEN
1738 --The bill to site has changed and a new credit card
1739 --has been brought in. Since the CVV2 value used here
1740 --would be of the previous cards', displaying this message
1741 --to the user.
1742 FND_MESSAGE.SET_NAME('ONT','OE_CC_SECURITY_CODE_REQD');
1743 OE_Msg_Pub.Add;
1744 END IF;*/
1745 END IF;
1746 --Now setting the account site id as the new bill to site
1747 --for creating this trxn extension id
1748 l_payer.account_site_id := p_site_use_id;
1749 IF l_debug_level > 0 THEN
1750 oe_debug_pub.add('Before calling create_transaction extension');
1751 oe_debug_pub.add('payment channel -->'||l_pmt_channel_code);
1752 oe_debug_pub.add('Assignment id ---->'|| l_assign_id);
1753 oe_debug_pub.add('trxn attributes record type values');
1754 oe_debug_pub.add('l_trxn_attribs.Instrument_Security_Code --->'||p_instrument_security_code);
1755 oe_debug_pub.add('l_trxn_attribs.Originating application id ---> '||l_trxn_attribs.Originating_application_id);
1756 oe_debug_pub.add('l_trxn_attribs.order_id ----> '||l_trxn_attribs.order_id);
1757 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 --->'||l_trxn_attribs.trxn_ref_number1);
1758 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 --->'||l_trxn_attribs.trxn_ref_number2);
1759 END IF;
1760
1761 IBY_Fndcpt_Trxn_Pub.Create_Transaction_Extension
1762 (p_api_version => 1.0,
1763 p_init_msg_list => FND_API.G_TRUE,
1764 p_commit => FND_API.G_FALSE,
1765 X_return_status => l_return_status,
1766 X_msg_count => l_msg_count,
1767 X_msg_data => l_msg_data,
1768 P_payer => l_payer,
1769 P_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1770 P_pmt_channel => l_pmt_channel_code,
1771 P_instr_assignment => l_assign_id,
1772 P_trxn_attribs => l_trxn_attribs,
1773 x_entity_id => l_trxn_extension_id,
1774 X_response => l_response_code);
1775
1776 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
1777
1778 IF l_debug_level > 0 THEN
1779 oe_debug_pub.add('After calling Create_Transaction_Extension'||l_trxn_extension_id);
1780 oe_debug_pub.add('Result code'||l_Response_code.result_code);
1781 oe_debug_pub.add('Return status'||l_Return_Status);
1782 END IF;
1783
1784 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1785 IF l_debug_level > 0 THEN
1786 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
1787 END IF;
1788 RAISE FND_API.G_EXC_ERROR;
1789 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1790 IF l_debug_level > 0 THEN
1791 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
1792 END IF;
1793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1794 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1795 --CC Reversal ER#16014135 Start
1796 Begin
1797 oe_payment_trxn_util.Reverse_CreditCard_Auth
1798 (p_header_id => p_header_id,
1799 p_line_id => p_line_id,
1800 p_msg_count => l_msg_count,
1801 p_msg_data => l_msg_data,
1802 p_return_status => l_return_status,
1803 p_new_trxn_extension_id => l_trxn_extension_id
1804 );
1805 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1806 IF l_debug_level > 0 THEN
1807 oe_debug_pub.add('Reverse_CreditCard_Auth error....exc',5);
1808 oe_debug_pub.add('After call to Reverse_CreditCard_Auth'||l_return_status,5);
1809 oe_debug_pub.add('Error'||sqlerrm,5);
1810 END IF;
1811 RAISE FND_API.G_EXC_ERROR;
1812 ELSIF l_return_status= FND_API.G_RET_STS_UNEXP_ERROR Then
1813 IF l_debug_level > 0 THEN
1814 oe_debug_pub.add('After call to Reverse_CreditCard_Auth --> Unexpected error',5);
1815 oe_debug_pub.add('Error message '||sqlerrm,5);
1816 END IF;
1817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1818 ELSIF l_return_status=FND_API.G_RET_STS_SUCCESS Then
1819 IF l_debug_level > 0 THEN
1820 oe_debug_pub.add('Call to Reverse_CreditCard_Auth --> Success',5);
1821 end if;
1822 END IF;
1823 END;
1824 --CC Reversal ER#16014135 End
1825 --Setting the trxn extension id to the new value
1826 --as the old trxn extension id was deleted
1827 p_trxn_extension_id := l_trxn_extension_id ;
1828 IF l_debug_level > 0 THEN
1829 oe_debug_pub.add('Create_Transaction_Extension assignment Successful....');
1830 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
1831 oe_debug_pub.add('After call to create Transaction Extension');
1832 oe_debug_pub.add('New trxn extension id'||l_trxn_extension_id);
1833 oe_debug_pub.add('Return status'||l_return_status);
1834 END IF;
1835 END IF;
1836
1837 END IF; -- old bill to site
1838 --bug 4885313
1839 IF l_debug_level > 0 THEN
1840 oe_debug_pub.add('Exiting Update_Payment_Trxn.....');
1841 END IF;
1842
1843 X_return_status := FND_API.G_RET_STS_SUCCESS;
1844
1845 EXCEPTION
1846
1847 WHEN FND_API.G_EXC_ERROR THEN
1848 X_return_status := FND_API.G_RET_STS_ERROR;
1849 OE_MSG_PUB.Count_And_Get
1850 ( p_count => l_msg_count,
1851 p_data => l_msg_data
1852 );
1853 RAISE FND_API.G_EXC_ERROR;
1854
1855 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1856 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1857 OE_MSG_PUB.Count_And_Get
1858 ( p_count => l_msg_count,
1859 p_data => l_msg_data
1860 );
1861 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1862
1863 WHEN OTHERS THEN
1864 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1865 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1866 THEN
1867 FND_MSG_PUB.Add_Exc_Msg
1868 ( G_PKG_NAME
1869 , 'OE_PAYMENT_TRXN_UTIL'
1870 );
1871 END IF;
1872
1873 OE_MSG_PUB.Count_And_Get
1874 ( p_count => l_msg_count,
1875 p_data => l_msg_data
1876 );
1877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1878
1879 END Update_Payment_Trxn;
1880
1881 Procedure Copy_Payment_Trxn( p_header_id IN NUMBER,
1882 P_line_id IN NUMBER,
1883 p_cust_id IN NUMBER,
1884 P_site_use_id IN NUMBER,
1885 p_trxn_extension_id IN NUMBER,
1886 x_trxn_extension_id OUT NOCOPY NUMBER,
1887 X_return_status OUT NOCOPY VARCHAR2,
1888 X_msg_count OUT NOCOPY NUMBER,
1889 X_msg_data OUT NOCOPY VARCHAR2)
1890 IS
1891 --R12 CC Encryption
1892 L_return_status VARCHAR2(30);
1893 L_msg_count NUMBER;
1894 L_msg_data VARCHAR2(2000);
1895 L_party_id NUMBER;
1896 L_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
1897 L_response_code IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1898 L_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
1899 l_org_id NUMBER;
1900 l_org_type VARCHAR2(80) := 'OPERATING_UNIT';
1901 p_entities IBY_FNDCPT_COMMON_PUB.Id_tbl_type;
1902 l_invoice_to_org_id OE_ORDER_LINES_ALL.invoice_to_org_id%TYPE;
1903 l_cust_account_id NUMBER; --New
1904 l_err_message VARCHAR2(2000);
1905 l_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
1906 l_instr_assignment_id IBY_FNDCPT_PAYER_ASSGN_INSTR_V.instr_assignment_id%TYPE;
1907 l_instrument_id IBY_FNDCPT_PAYER_ALL_INSTRS_V.instrument_id%TYPE;
1908 l_instrument_type IBY_FNDCPT_PMT_CHNNLS_VL.instrument_type%TYPE;
1909 l_instrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
1910 l_exists_assignment VARCHAR2(1) := 'N';
1911 l_assign_id IBY_FNDCPT_PAYER_ASSGN_INSTR_V.instr_assignment_id%TYPE;
1912
1913 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1914
1915 BEGIN
1916 --Get party id for the bill to site;??
1917 IF p_line_id is not null then
1918 Select INVOICE_TO_ORG_ID,ORG_ID into l_invoice_to_org_id,l_org_id
1919 from oe_order_lines_all where header_id = p_header_id and line_id = p_line_id;
1920 else
1921 /*
1922 select invoice_to_org_id,ORG_ID into l_invoice_to_org_id,l_org_id
1923 from oe_order_headers_all where header_id = p_header_id;
1924 */
1925
1926 oe_order_cache.load_order_header(p_header_id);
1927 l_invoice_to_org_id := OE_Order_Cache.g_header_rec.invoice_to_org_id;
1928 l_org_id := OE_Order_Cache.g_header_rec.org_id;
1929 end if;
1930 IF l_debug_level > 0 THEN
1931 oe_debug_pub.add('INVOICE_TO_ORG_ID and ORG_ID'||l_invoice_to_org_id||' and '||l_org_id);
1932 END IF;
1933
1934 Begin
1935 Select hca.party_id, acct_site.cust_account_id
1936 Into l_party_id, l_cust_account_id
1937 From HZ_CUST_SITE_USES_ALL SITE,
1938 HZ_CUST_ACCT_SITES ACCT_SITE,
1939 HZ_CUST_ACCOUNTS_ALL HCA
1940 Where SITE.SITE_USE_ID = p_site_use_id
1941 AND SITE.SITE_USE_CODE = 'BILL_TO'
1942 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1943 AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
1944 AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
1945
1946 Exception
1947 When No_Data_Found THEN
1948 Null;
1949 End;
1950
1951 l_payer.payment_function := 'CUSTOMER_PAYMENT';
1952 l_payer.party_id := l_party_id;
1953 l_payer.org_type := l_org_type;
1954 l_payer.org_id := l_org_id;
1955 l_payer.cust_account_id := l_cust_account_id;
1956 l_payer.account_site_id := p_site_use_id;
1957
1958 Begin
1959 -- Bug 8586227
1960 select instrument_id, instrument_type, instr_assignment_id
1961 into l_instrument_id, l_instrument_type, l_instr_assignment_id
1962 from IBY_EXTN_INSTR_DETAILS_V
1963 where trxn_extension_id = p_trxn_extension_id;
1964 Exception When NO_DATA_FOUND THEN
1965 null;
1966 End;
1967
1968 IF l_debug_level > 0 THEN
1969 oe_debug_pub.add('instrument_id is: '||l_instrument_id,1);
1970 END IF;
1971
1972 -- Need to call the Set Payer Instr Assignment API always
1973 --to create a new assignment id at the account level as iStore
1974 --creates the assignment id at the Party or Site level.
1975
1976 --Commenting out this check for that.
1977 /*Begin
1978 Select 'Y'
1979 Into l_exists_assignment
1980 From IBY_FNDCPT_PAYER_ASSGN_INSTR_V
1981 Where party_id = l_party_id
1982 And instr_assignment_id = l_instr_assignment_id
1983 And rownum = 1;
1984 Exception When NO_DATA_FOUND THEN
1985 l_exists_assignment := 'N';
1986 End;
1987
1988 IF l_debug_level > 0 THEN
1989 oe_debug_pub.add('l_exists_assignment is: '||l_exists_assignment,1);
1990 END IF;
1991
1992 IF l_exists_assignment = 'N' THEN*/
1993
1994 -- create a new instrument assignment id for the payer
1995 -- and the instrument
1996
1997 l_instrument.instrument_type := l_instrument_type;
1998 l_instrument.instrument_id := l_instrument_id;
1999 l_assignment_attribs.instrument := l_instrument;
2000
2001 IF l_debug_level > 0 THEN
2002 oe_debug_pub.add('Calling Oracle Payments API to create new assignment.',1);
2003 oe_debug_pub.add('Before call to Set payer instr assignment API...');
2004 oe_debug_pub.add('Assignment attributes passed ');
2005 oe_debug_pub.add('l_instrument.instrument_id ---> '||l_instrument_id);
2006 oe_debug_pub.add('l_instrument.instrument_type -> '||l_instrument_type);
2007 END IF;
2008
2009 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
2010 (p_api_version => 1.0,
2011 p_init_msg_list => FND_API.G_TRUE,
2012 p_commit => FND_API.G_FALSE,
2013 X_return_status => l_return_status,
2014 X_msg_count => l_msg_count,
2015 X_msg_data => l_msg_data,
2016 P_payer => l_payer,
2017 P_assignment_attribs => l_assignment_attribs,
2018 X_assign_id => l_assign_id,
2019 X_response => l_response_code);
2020
2021 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2022 -- 16604394
2023 -- Capturing the return status from IBY API and displaying the appropriate error message on the UI
2024 IF l_response_code.result_code = 'INVALID_CARD_NUMBER' THEN
2025 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
2026 OE_MSG_PUB.ADD;
2027 IF l_debug_level > 0 THEN
2028 oe_debug_pub.add( 'OEXUPTXB: Invalid card number or expiration date in set payer instr assignment ' || l_response_code.result_code ) ;
2029 END IF;
2030 ELSIF l_response_code.result_code = 'INVALID_ADDRESS' THEN
2031 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_INVALID');
2032 OE_MSG_PUB.ADD;
2033 IF l_debug_level > 0 THEN
2034 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address in set payer instr assignment ' || l_response_code.result_code) ;
2035 END IF;
2036 ELSIF l_response_code.result_code = 'INVALID_CARD_ISSUER' THEN
2037 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5012613
2038 OE_MSG_PUB.ADD;
2039 IF l_debug_level > 0 THEN
2040 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address in set payer instr assignment ' || l_response_code.result_code) ;
2041 END IF;
2042 ELSE --Setting a generic message bug 5244099
2043 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
2044 OE_MSG_PUB.ADD;
2045 IF l_debug_level > 0 THEN
2046 oe_debug_pub.add( 'OEXUPTXB: Setting the generic message in set payer instr assignment ' || l_response_code.result_code) ;
2047 END IF;
2048 END IF;
2049 -- 16604394
2050 RAISE FND_API.G_EXC_ERROR;
2051 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2052 -- 16604394
2053 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5244099
2054 OE_MSG_PUB.ADD;
2055 -- 16604394
2056 IF l_debug_level > 0 THEN
2057 oe_debug_pub.add('Unexpected result error code in Set_Payer_Instr_Assignment-->'||l_response_code.result_code);
2058 END IF;
2059 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2060 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2061 IF l_debug_level > 0 THEN
2062 oe_debug_pub.add('Result code in Set_Payer_Instr_Assignment'||l_response_code.result_code);
2063 oe_debug_pub.add('new assignment id is: '||l_assign_id,1);
2064 END IF;
2065 END IF;
2066
2067 --Populating the new assignment id to the copy instr
2068 --assign id attribute and then calling copy transaction API.
2069 l_trxn_attribs.copy_instr_assign_id := l_assign_id;
2070 l_trxn_attribs.order_id := p_header_id;
2071
2072 p_entities(1) := p_trxn_extension_id;
2073
2074 IF l_debug_level > 0 THEN
2075 oe_debug_pub.add('Payer context in copy...'||l_party_id||' and '||l_org_type ||'and'||l_cust_account_id||'and'||'and'||p_site_use_id||'and'||p_trxn_extension_id||'and'||p_header_id);
2076 END IF;
2077
2078 --<store the line id in trx_ref_number1 if this is a line level payment>
2079 IF p_line_id IS NOT NULL THEN
2080 l_trxn_attribs.trxn_ref_number1 := p_line_id;
2081 END IF;
2082
2083 l_trxn_attribs.Originating_Application_Id := 660;
2084
2085 --<Copy the transaction in the IBY payment trasaction extenstion table>
2086 IF l_debug_level > 0 THEN
2087 oe_debug_pub.add('Values passed to Copy_transaction_extension');
2088 oe_debug_pub.add('original trxn extension id ---->'||p_entities(1));
2089 oe_debug_pub.add('trxn attributes record type values');
2090 --oe_debug_pub.add('l_trxn_attribs.Instrument_Security_Code --->'||p_instrument_security_code);
2091 oe_debug_pub.add('l_trxn_attribs.Originating application id ---> 660');
2092 oe_debug_pub.add('l_trxn_attribs.order_id ----> '||p_header_id);
2093 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 --->'||p_line_id);
2094 --oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 --->'||p_payment_number);
2095 oe_debug_pub.add('l_trxn_attribs.copy_instr_assign_id ---->'||l_assign_id);
2096 END IF;
2097 IBY_Fndcpt_Trxn_Pub.Copy_Transaction_Extension
2098 (p_api_version => 1.0,
2099 p_init_msg_list => FND_API.G_TRUE,
2100 p_commit => FND_API.G_FALSE,
2101 X_return_status => l_return_status,
2102 X_msg_count => l_msg_count,
2103 X_msg_data => l_msg_data,
2104 P_payer => l_payer,
2105 P_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
2106 p_entities => p_entities,
2107 p_trxn_attribs => l_trxn_attribs,
2108 X_entity_id => x_trxn_extension_id,
2109 x_response => l_response_code);
2110
2111 IF l_debug_level > 0 THEN
2112 oe_debug_pub.add('Return status from Copy_Transaction_Extension'||l_return_status);
2113 END IF;
2114
2115 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2116 IF l_debug_level > 0 THEN
2117 oe_debug_pub.add('Result error code in Copy_Transaction_Extension'||l_response_code.result_code);
2118 END IF;
2119 --IF l_response_code.result_code = '
2120 RAISE FND_API.G_EXC_ERROR;
2121 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2122 IF l_debug_level > 0 THEN
2123 oe_debug_pub.add('Result error code in Copy_Transaction_Extension'||l_response_code.result_code);
2124 END IF;
2125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2126 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2127 IF l_debug_level > 0 THEN
2128 oe_debug_pub.add('Result code in Copy_Transaction_Extension'||l_response_code.result_code);
2129 END IF;
2130 END IF;
2131
2132 IF l_debug_level > 0 THEN
2133 oe_debug_pub.add('Exiting OE_PAYMENT_TRXN_UTIL.Copy_Payment_Trxn.', 1);
2134 END IF;
2135
2136 X_return_status := FND_API.G_RET_STS_SUCCESS;
2137
2138 EXCEPTION
2139
2140 WHEN FND_API.G_EXC_ERROR THEN
2141 l_err_message := SQLERRM;
2142 IF l_debug_level > 0 THEN
2143 oe_debug_pub.add('Copy_Transaction_Extension error....exc');
2144 oe_debug_pub.add('After call to Copy_Transaction_Extension'||l_return_status);
2145 oe_debug_pub.add('Result code'||l_response_code.result_code);
2146 oe_debug_pub.add('Error'||l_err_message);
2147 END IF;
2148
2149 X_return_status := FND_API.G_RET_STS_ERROR;
2150 OE_MSG_PUB.Count_And_Get
2151 ( p_count => l_msg_count,
2152 p_data => l_msg_data
2153 );
2154 RAISE FND_API.G_EXC_ERROR;
2155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2156 l_err_message := SQLERRM;
2157 IF l_debug_level > 0 THEN
2158 oe_debug_pub.add('Copy_Transaction_Extension error....unxc');
2159 oe_debug_pub.add('After call to Copy_Transaction_Extension'||l_return_status);
2160 oe_debug_pub.add('Result code'||l_response_code.result_code);
2161 oe_debug_pub.add('f Error'||l_err_message);
2162 END IF;
2163
2164 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2165 OE_MSG_PUB.Count_And_Get
2166 ( p_count => l_msg_count,
2167 p_data => l_msg_data
2168 );
2169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2170
2171 WHEN OTHERS THEN
2172 l_err_message := SQLERRM;
2173 IF l_debug_level > 0 THEN
2174 oe_debug_pub.add('Create_Transaction_Extension assignment error....others');
2175 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
2176 oe_debug_pub.add('Result code'||l_response_code.result_code);
2177 oe_debug_pub.add('trx Error'||l_err_message);
2178 END IF;
2179
2180 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2181 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2182 THEN
2183 FND_MSG_PUB.Add_Exc_Msg
2184 ( G_PKG_NAME
2185 , 'OE_PAYMENT_TRXN_UTIL'
2186 );
2187 END IF;
2188
2189 OE_MSG_PUB.Count_And_Get
2190 ( p_count => l_msg_count,
2191 p_data => l_msg_data
2192 );
2193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2194 --R12 CC Encryption
2195 END Copy_Payment_Trxn;
2196
2197 Procedure Get_Payment_Trxn_Info(p_header_id IN NUMBER,
2198 P_trxn_extension_id IN NUMBER,
2199 P_payment_type_code IN VARCHAR2,
2200 X_credit_card_number OUT NOCOPY VARCHAR2,
2201 X_credit_card_holder_name OUT NOCOPY VARCHAR2,
2202 X_credit_card_expiration_date OUT NOCOPY VARCHAR2,
2203 X_credit_card_code OUT NOCOPY VARCHAR2,
2204 X_credit_card_approval_code OUT NOCOPY VARCHAR2,
2205 X_credit_card_approval_date OUT NOCOPY VARCHAR2,
2206 X_bank_account_number OUT NOCOPY VARCHAR2,
2207 --X_check_number OUT NOCOPY VARCHAR2,
2208 X_instrument_security_code OUT NOCOPY VARCHAR2,
2209 X_instrument_id OUT NOCOPY NUMBER,
2210 X_instrument_assignment_id OUT NOCOPY NUMBER,
2211 X_return_status OUT NOCOPY VARCHAR2,
2212 X_msg_count OUT NOCOPY NUMBER,
2213 X_msg_data OUT NOCOPY VARCHAR2) IS
2214
2215 --R12 CC Encryption
2216 L_trxn_extension_id NUMBER := P_trxn_extension_id;
2217 l_return_status VARCHAR2(30) := NULL ;
2218 l_msg_count NUMBER := 0 ;
2219 l_msg_data VARCHAR2(2000) := NULL ;
2220 l_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
2221 l_party_id NUMBER;
2222
2223 l_auth_result IBY_FNDCPT_TRXN_PUB.AuthResult_rec_type;
2224 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2225 l_cust_account_id NUMBER;
2226 l_acct_site_use_id NUMBER;
2227 l_org_type VARCHAR2(80);
2228 l_payment_function VARCHAR2(80);
2229 l_org_id NUMBER;
2230 l_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
2231 l_err_message VARCHAR2(2000);
2232 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2233 L_authorized VARCHAR2(1);
2234 l_encrypted VARCHAR2(30); --PADSS
2235 BEGIN
2236
2237 IF l_debug_level > 0 THEN
2238 oe_debug_pub.add('Entering Get_Payment_Trxn_Info...');
2239 x_return_status := FND_API.G_RET_STS_SUCCESS;
2240 END IF;
2241
2242 --< if l_trxn_extension_id is null, this might be called from sales order header>
2243 IF l_trxn_extension_id IS NULL THEN
2244 SELECT trxn_extension_id
2245 INTO l_trxn_extension_id
2246 FROM oe_payments
2247 WHERE header_id = p_header_id;
2248 IF l_debug_level > 0 THEN
2249 oe_debug_pub.add('Trxn extension id from oe_payments'||l_trxn_extension_id);
2250 END IF;
2251
2252 END IF;
2253
2254 IF l_trxn_extension_id is not null then
2255 IF p_payment_type_code in ('ACH', 'DIRECT_DEBIT') then
2256 -- bug 8586227
2257 Select account_number
2258 Into x_bank_account_number
2259 From IBY_EXTN_INSTR_DETAILS_V
2260 Where trxn_extension_id = l_trxn_extension_id;
2261 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
2262 -- bug 8586227
2263 /*Select itev.card_number,
2264 itev.card_holder_name,
2265 --itev.card_expirydate,
2266 itev.masked_card_expirydate, --PADSS
2267 itev.card_issuer_code,
2268 itev.authorized_flag,
2269 itev.instrument_security_code,
2270 itev.instrument_id,
2271 itev.instr_assignment_id
2272 into
2273 x_credit_card_number,
2274 x_credit_card_holder_name,
2275 x_credit_card_expiration_date,
2276 x_credit_card_code,
2277 l_authorized,
2278 x_instrument_security_code,
2279 x_instrument_id,
2280 x_instrument_assignment_id
2281 FROM
2282 IBY_TRXN_EXTENSIONS_V ITEV
2283 WHERE ITEV.TRXN_EXTENSION_ID = l_trxn_extension_id;*/
2284
2285 Select itev.card_number,
2286 itev.card_holder_name,
2287 --itev.card_expirydate,
2288 itev.masked_card_expirydate, --PADSS
2289 itev.card_issuer_code,
2290 iauth.authorized_flag,
2291 itev.instrument_security_code,
2292 itev.instrument_id,
2293 itev.instr_assignment_id
2294 into
2295 x_credit_card_number,
2296 x_credit_card_holder_name,
2297 x_credit_card_expiration_date,
2298 x_credit_card_code,
2299 l_authorized,
2300 x_instrument_security_code,
2301 x_instrument_id,
2302 x_instrument_assignment_id
2303 FROM
2304 IBY_EXTN_INSTR_DETAILS_V ITEV,
2305 IBY_EXTN_AUTHORIZATIONS_V IAUTH
2306 WHERE
2307 itev.TRXN_EXTENSION_ID= IAUTH.TRXN_EXTENSION_ID (+) AND
2308 ITEV.TRXN_EXTENSION_ID = l_trxn_extension_id;
2309
2310
2311 IF l_debug_level > 0 THEN
2312 oe_debug_pub.add('Values retrieved in Get_Payment_Trxn_Info...');
2313 --oe_debug_pub.add('x_credit_card_number ----> '||x_credit_card_number);
2314 --oe_debug_pub.add('x_credit_card_holder_name ----> '||x_credit_card_holder_name);
2315 --oe_debug_pub.add('x_credit_card_expiration_date ---> '||x_credit_card_expiration_date);
2316 --oe_debug_pub.add('x_credit_card_code ---> '||x_credit_card_code);
2317 oe_debug_pub.add('l_authorized ---> '||l_authorized);
2318 oe_debug_pub.add('x_instrument_security_code ----> '||x_instrument_security_code);
2319 oe_debug_pub.add('x_instrument_id ---> '||x_instrument_id);
2320 oe_debug_pub.add('x_instrument_assignment_id ----> '||x_instrument_assignment_id);
2321 END IF;
2322
2323 --PADSS start
2324 begin
2325 select encrypted
2326 into l_encrypted
2327 from iby_creditcard
2328 where instrid=x_instrument_id;
2329 exception
2330 when others then
2331 l_encrypted:=null;
2332 end;
2333 --IF iby_cc_security_pub.encryption_enabled() THEN
2334 IF nvl(l_encrypted,'N')= 'A' THEN
2335 x_credit_card_expiration_date := NULL;
2336 ELSE
2337 x_credit_card_expiration_date := to_date(x_credit_card_expiration_date,'mm/yy');
2338 END IF;
2339 --PADSS end
2340
2341 IF l_authorized = 'Y' THEN
2342 IF l_debug_level > 0 THEN
2343 oe_debug_pub.add('Before calling IBY Get_authorization API...');
2344 oe_debug_pub.add('trxn extn id passed to get auth'||l_trxn_extension_id);
2345 END IF;
2346 IBY_Fndcpt_Trxn_Pub.Get_Authorization
2347 (p_api_version => 1.0,
2348 x_return_status => l_return_status,
2349 x_msg_count => l_msg_count,
2350 x_msg_data => l_msg_data,
2351 p_payer => l_payer,
2352 p_trxn_entity_id => l_trxn_extension_id,
2353 x_auth_result => l_auth_result,
2354 x_response => l_response);
2355
2356 IF l_debug_level > 0 THEN
2357 oe_debug_pub.add('Return status from Get_Authorization'||l_return_status);
2358 END IF;
2359
2360 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2361 IF l_debug_level > 0 THEN
2362 oe_debug_pub.add('Result error code in Get_Authorization'||l_response.result_code);
2363 END IF;
2364 --IF l_response_code.result_code = '
2365 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2366 IF l_debug_level > 0 THEN
2367 oe_debug_pub.add('Result error code in Get_Authorization'||l_response.result_code);
2368 END IF;
2369 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2370 IF l_debug_level > 0 THEN
2371 oe_debug_pub.add('Success in Get_Authorization'||l_response.result_code);
2372 --oe_debug_pub.add('approval code ----> '||l_auth_result.auth_code);
2373 --oe_debug_pub.add('x_credit_card_approval_date ---> '||l_auth_result.auth_date);
2374 END IF;
2375 x_credit_card_approval_code := l_auth_result.auth_code;
2376 x_credit_card_approval_date := l_auth_result.auth_date;
2377 END IF;
2378 END IF; --Authorized flag = 'Y'
2379
2380 END IF; --Payment type code = 'Credit_card'
2381
2382 END IF; --trxn extension id not null
2383
2384 X_return_status := FND_API.G_RET_STS_SUCCESS;
2385
2386 IF l_debug_level > 0 THEN
2387 oe_debug_pub.add('Exiting Get_Payment_Trxn_Info....');
2388 END IF;
2389
2390 EXCEPTION
2391
2392 WHEN FND_API.G_EXC_ERROR THEN
2393 l_err_message := SQLERRM;
2394 IF l_debug_level > 0 THEN
2395 oe_debug_pub.add('Get_Payment_Trxn_Info error....exc');
2396 oe_debug_pub.add('After call to Get_Payment_Trxn_Info'||l_return_status);
2397 oe_debug_pub.add('Result code'||l_response.result_code);
2398 oe_debug_pub.add('Error'||l_err_message);
2399 END IF;
2400
2401
2402 OE_MSG_PUB.Count_And_Get
2403 ( p_count => l_msg_count,
2404 p_data => l_msg_data
2405 );
2406
2407 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2408 l_err_message := SQLERRM;
2409 IF l_debug_level > 0 THEN
2410 oe_debug_pub.add('Get_Payment_Trxn_Info error....unxc');
2411 oe_debug_pub.add('After call to Get_Payment_Trxn_Info'||l_return_status);
2412 oe_debug_pub.add('Result code'||l_response.result_code);
2413 oe_debug_pub.add('f Error'||l_err_message);
2414 END IF;
2415
2416
2417 OE_MSG_PUB.Count_And_Get
2418 ( p_count => l_msg_count,
2419 p_data => l_msg_data
2420 );
2421
2422 WHEN OTHERS THEN
2423 l_err_message := SQLERRM;
2424 IF l_debug_level > 0 THEN
2425 oe_debug_pub.add('Get_Payment_Trxn_Info error....others');
2426 oe_debug_pub.add('After call to Get_Payment_Trxn_Info'||l_return_status);
2427 oe_debug_pub.add('Result code'||l_response.result_code);
2428 oe_debug_pub.add('trx Error'||l_err_message);
2429 END IF;
2430
2431
2432 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2433 THEN
2434 FND_MSG_PUB.Add_Exc_Msg
2435 ( G_PKG_NAME
2436 , 'OE_PAYMENT_TRXN_UTIL'
2437 );
2438 END IF;
2439
2440 -- FND_MESSAGE.SET_NAME('ONT','Exception in Get_Payment_Trxn_Info');
2441 -- OE_MSG_PUB.Add;
2442
2443 OE_MSG_PUB.Count_And_Get
2444 ( p_count => l_msg_count,
2445 p_data => l_msg_data
2446 );
2447
2448 END Get_Payment_Trxn_Info;
2449
2450 PROCEDURE Delete_Payment_Trxn
2451 (p_header_id IN NUMBER,
2452 p_line_id IN NUMBER,
2453 p_payment_number IN NUMBER,
2454 x_return_status OUT NOCOPY VARCHAR2,
2455 x_msg_count OUT NOCOPY NUMBER,
2456 x_msg_data OUT NOCOPY VARCHAR2,
2457 p_trxn_extension_id IN NUMBER,
2458 P_site_use_id IN NUMBER
2459 )
2460 IS
2461 L_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
2462 l_org_id NUMBER;
2463 l_org_type VARCHAR2(80) := 'OPERATING_UNIT';
2464 L_return_status VARCHAR2(30);
2465 L_msg_count NUMBER;
2466 L_msg_data VARCHAR2(2000);
2467 L_party_id NUMBER;
2468 L_response_code IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2469 l_cust_account_id NUMBER;
2470 l_err_message VARCHAR2(2000);
2471 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2472 l_site_use_id NUMBER := p_site_use_id;
2473 l_payment_channel_code VARCHAR2(80);
2474 l_invoice_to_org_id NUMBER;
2475 -- bug 5194228
2476 l_settled_flag VARCHAR2(1);
2477 l_authorized_flag VARCHAR2(1);
2478 --CC Reversal ER#16014135 start
2479 l_cc_rev_reauth_code VARCHAR2(30);
2480 l_header_rec OE_Order_PUB.Header_Rec_Type;
2481 l_result_out VARCHAR2(30);
2482 l_header_payment_exists VARCHAR2(1);
2483 --CC Reversal ER#16014135 end
2484
2485 BEGIN
2486
2487 IF l_debug_level > 0 THEN
2488 oe_debug_pub.add('Entering Delete_Payment_Trxn...');
2489 END IF;
2490
2491 IF p_line_id is not null then
2492 Select ORG_ID into l_org_id
2493 from oe_order_lines_all where line_id = p_line_id and header_id = p_header_id;
2494 else
2495 oe_order_cache.load_order_header(p_header_id);
2496 l_org_id := OE_Order_Cache.g_header_rec.org_id;
2497 end if;
2498 --For ACH and direct debit payment types, the site use id that
2499 --needs to be queried from payment tables is different from the query
2500 --for Credit card payments. Hence to differentiate this, the payment
2501 --channel code is used.
2502 begin
2503 -- BUG 8586227
2504 /*select payment_channel_code,settled_flag,authorized_flag
2505 into l_payment_channel_code,l_settled_flag,l_authorized_flag -- bug 5194228
2506 from iby_trxn_extensions_v where trxn_Extension_id=p_trxn_extension_id;*/
2507 select payment_channel_code,nvl(settled_flag,'N'),nvl(authorized_flag,'N')
2508 into l_payment_channel_code,l_settled_flag,l_authorized_flag -- bug 5194228
2509 from IBY_EXTN_INSTR_DETAILS_V itev ,
2510 IBY_EXTN_AUTHORIZATIONS_V iauth,
2511 IBY_EXTN_SETTLEMENTS_V iset
2512 where itev.trxn_extension_id=iauth.trxn_extension_id (+)
2513 and itev.trxn_extension_id=iset.trxn_extension_id (+)
2514 and itev.trxn_extension_id=p_trxn_extension_id;
2515
2516 exception
2517 when others then
2518 oe_debug_pub.add('Trxn extn id not found....');
2519 end ;
2520
2521 --Incase of deleting payments due to bill to site change
2522 --the invoice to org id is passed as null. So querying the
2523 --old invoice to org id from the payments tables in this case.
2524 IF l_site_use_id is null then
2525 IF l_debug_level > 0 THEN
2526 oe_debug_pub.add('Inside l_site_use_id is null....'||l_payment_channel_code);
2527 oe_debug_pub.add('Trxn extension id used to query ----> '||p_trxn_extension_id);
2528 END IF;
2529
2530 BEGIN
2531 IF l_payment_channel_code = 'CREDIT_CARD' THEN
2532 IF l_debug_level > 0 THEN
2533 oe_debug_pub.add('Inside the credit card query for site use id...'||p_trxn_extension_id);
2534 END IF;
2535 -- bug 8586227
2536 select ifpai.acct_site_use_id into l_site_use_id
2537 from iby_fndcpt_payer_assgn_instr_v ifpai,
2538 IBY_EXTN_INSTR_DETAILS_V itev where
2539 ifpai.instr_assignment_id = itev.instr_assignment_id and
2540 itev.trxn_extension_id = p_trxn_extension_id;
2541 ELSE
2542 IF l_debug_level > 0 THEN
2543 oe_debug_pub.add('Inside the ACH and Direct debit query for site use id...'||p_trxn_extension_id);
2544 END IF;
2545 select iepa.acct_site_use_id into l_site_use_id from
2546 iby_external_payers_all iepa, iby_fndcpt_tx_extensions ifte
2547 where iepa.ext_payer_id = ifte.ext_payer_id and
2548 ifte.trxn_extension_id = p_trxn_extension_id;
2549 END IF;
2550 IF l_debug_level > 0 THEN
2551 oe_debug_pub.add('site use id queried ---> '||l_site_use_id);
2552 END IF;
2553 EXCEPTION
2554 WHEN NO_DATA_FOUND THEN
2555 IF l_debug_level > 0 THEN
2556 oe_debug_pub.add('site use id not found..!!'||sqlerrm);
2557 END IF;
2558 END;
2559 END IF;
2560
2561 Select hca.party_id, acct_site.cust_account_id
2562 Into l_party_id, l_cust_account_id
2563 From HZ_CUST_SITE_USES_ALL SITE,
2564 HZ_CUST_ACCT_SITES ACCT_SITE,
2565 HZ_CUST_ACCOUNTS_ALL HCA
2566 Where SITE.SITE_USE_ID = l_site_use_id
2567 AND SITE.SITE_USE_CODE = 'BILL_TO'
2568 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
2569 AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
2570 AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
2571
2572
2573 l_payer.payment_function := 'CUSTOMER_PAYMENT';
2574 l_payer.party_id := l_party_id;
2575 l_payer.org_type := l_org_type;
2576 l_payer.org_id := l_org_id;
2577 l_payer.cust_account_id := l_cust_account_id;
2578 --Setting the payer context appropriately
2579 l_payer.account_site_id := l_site_use_id;
2580
2581 IF l_debug_level > 0 THEN
2582 oe_debug_pub.add('Payer context values...');
2583 oe_debug_pub.add('party id'||l_party_id);
2584 oe_debug_pub.add('org id'||l_org_id);
2585 oe_debug_pub.add('cust acct id'||l_cust_account_id);
2586 oe_debug_pub.add('site use id'||l_site_use_id);
2587 END IF;
2588
2589 BEGIN
2590 IF l_debug_level > 0 THEN
2591 oe_debug_pub.add('Before calling Delete Transaction Extension API...');
2592 oe_debug_pub.add('Trxn extension id --------> '||p_trxn_extension_id);
2593 oe_debug_pub.add('Payer equivalency --------> '||IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_IMMEDIATE);
2594 END IF;
2595 -- bug 5194228
2596 IF nvl(l_authorized_flag,'N') = 'N' and nvl(l_Settled_flag,'N') = 'N' THEN
2597 IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension
2598 (
2599 p_api_version => 1.0,
2600 X_return_status => l_return_status,
2601 X_msg_count => l_msg_count,
2602 X_msg_data => l_msg_data,
2603 p_commit => FND_API.G_FALSE,
2604 P_payer => l_payer,
2605 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_IMMEDIATE,
2606 X_response => l_response_code,
2607 p_entity_id => p_trxn_extension_id);
2608
2609 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2610 IF l_debug_level > 0 THEN
2611 oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
2612 oe_debug_pub.add('sql error'||sqlerrm);
2613 oe_debug_pub.add('msg data'||l_msg_data);
2614 END IF;
2615 RAISE FND_API.G_EXC_ERROR;
2616 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2617 IF l_debug_level > 0 THEN
2618 oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
2619 oe_debug_pub.add('sql error'||sqlerrm);
2620 oe_debug_pub.add('msg data'||l_msg_data);
2621 END IF;
2622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2623 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
2624 IF l_debug_level > 0 THEN
2625 oe_debug_pub.add('Delete_Transaction_Extension Successful....');
2626 oe_debug_pub.add('After calling Delete_Transaction_Extension');
2627 oe_debug_pub.add('sql error'||sqlerrm);
2628 oe_debug_pub.add('msg data'||l_msg_data);
2629 END IF;
2630 x_return_status := FND_API.G_RET_STS_SUCCESS;
2631 END IF;
2632 END IF;
2633 -- bug 5194228
2634 --CC Reversal ER#16014135 Start
2635 IF nvl(l_authorized_flag,'N') = 'Y' and nvl(l_Settled_flag,'N') = 'N' THEN
2636 Begin
2637 oe_payment_trxn_util.Reverse_CreditCard_Auth
2638 (p_header_id => p_header_id,
2639 p_line_id => p_line_id,
2640 p_msg_count => l_msg_count,
2641 p_msg_data => l_msg_data,
2642 p_return_status => l_return_status
2643 );
2644 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2645 IF l_debug_level > 0 THEN
2646 oe_debug_pub.add('Reverse_CreditCard_Auth error....exc',5);
2647 oe_debug_pub.add('After call to Reverse_CreditCard_Auth'||l_return_status,5);
2648 oe_debug_pub.add('Error'||sqlerrm,5);
2649 END IF;
2650 RAISE FND_API.G_EXC_ERROR;
2651 ELSIF l_return_status= FND_API.G_RET_STS_UNEXP_ERROR Then
2652 IF l_debug_level > 0 THEN
2653 oe_debug_pub.add('After call to Reverse_CreditCard_Auth --> Unexpected error',5);
2654 oe_debug_pub.add('Error message '||sqlerrm,5);
2655 END IF;
2656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2657 ELSIF l_return_status=FND_API.G_RET_STS_SUCCESS Then
2658 IF l_debug_level > 0 THEN
2659 oe_debug_pub.add('Call to Reverse_CreditCard_Auth --> Success',5);
2660 end if;
2661 --need to evaluate again
2662 IF p_line_id IS NOT NULL THEN
2663 /* DELETE oe_payments
2664 WHERE trxn_extension_id = p_trxn_extension_id
2665 AND header_id = p_header_id
2666 AND nvl(line_id,-1) = nvl(p_line_id,-1)
2667 ;*/ --Commented for CC Reversal ER bug# 16595030
2668 UPDATE oe_order_lines_ALL
2669 SET payment_type_code=null
2670 WHERE 1=1
2671 AND header_id = p_header_id
2672 AND nvl(line_id,-1) = nvl(p_line_id,-1)
2673 ;
2674 -- Check if there is a header level invoice payment with cc
2675 BEGIN
2676 SELECT 'Y'
2677 INTO l_header_payment_exists
2678 FROM oe_payments
2679 WHERE header_id =p_header_id
2680 AND line_id is null --Added for CC Reversal ER bug# 16595030
2681 AND PAYMENT_COLLECTION_EVENT='INVOICE'
2682 AND PAYMENT_TYPE_CODE='CREDIT_CARD';
2683 EXCEPTION
2684 WHEN NO_DATA_FOUND THEN
2685 l_header_payment_exists:='N';
2686 WHEN OTHERS THEN
2687 l_header_payment_exists:='N';
2688 END;
2689 IF l_header_payment_exists ='Y' THEN
2690 -- Need to call authorization to refrsh header level payment
2691 OE_Header_UTIL.Query_Row
2692 (p_header_id => p_header_id
2693 ,x_header_rec => l_header_rec
2694 );
2695 OE_Verify_Payment_PUB.Authorize_MultiPayments
2696 ( p_header_rec => l_header_rec
2697 , p_calling_action => 'REVERSAL_REAUTH'
2698 , p_risk_eval_flag => null
2699 , p_msg_count => l_msg_count
2700 , p_msg_data => l_msg_data
2701 , p_result_out => l_result_out
2702 , p_return_status => l_return_status
2703 );
2704 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2705 RAISE FND_API.G_EXC_ERROR;
2706 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2708 END IF;
2709
2710 END IF;
2711
2712 END IF;
2713 END IF;
2714 END;
2715 end if;
2716 --CC Reversal ER#16014135 End
2717 END;
2718 x_return_status := nvl(l_return_status,FND_API.G_RET_STS_SUCCESS); --Added for CC Reversal ER bug# 16595030
2719 EXCEPTION
2720
2721 WHEN FND_API.G_EXC_ERROR THEN
2722 l_err_message := SQLERRM;
2723 IF l_debug_level > 0 THEN
2724 oe_debug_pub.add('Delete_Payment_Trxn error....exc');
2725 oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);
2726 oe_debug_pub.add('Result code'||l_response_code.result_code);
2727 oe_debug_pub.add('Error'||l_err_message);
2728 END IF;
2729
2730 X_return_status := FND_API.G_RET_STS_ERROR;
2731 OE_MSG_PUB.Count_And_Get
2732 ( p_count => l_msg_count,
2733 p_data => l_msg_data
2734 );
2735 RAISE FND_API.G_EXC_ERROR;
2736
2737 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2738 l_err_message := SQLERRM;
2739 IF l_debug_level > 0 THEN
2740 oe_debug_pub.add('Delete_Payment_Trxn error....unxc');
2741 oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);
2742 oe_debug_pub.add('Result code'||l_response_code.result_code);
2743 oe_debug_pub.add('f Error'||l_err_message);
2744 END IF;
2745
2746 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2747 OE_MSG_PUB.Count_And_Get
2748 ( p_count => l_msg_count,
2749 p_data => l_msg_data
2750 );
2751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2752
2753 WHEN OTHERS THEN
2754 l_err_message := SQLERRM;
2755 IF l_debug_level > 0 THEN
2756 oe_debug_pub.add('Delete_Payment_Trxn error....others');
2757 oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);
2758 oe_debug_pub.add('Result code'||l_response_code.result_code);
2759 oe_debug_pub.add('trx Error'||l_err_message);
2760 END IF;
2761
2762 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2763 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2764 THEN
2765 FND_MSG_PUB.Add_Exc_Msg
2766 ( G_PKG_NAME
2767 , 'OE_PAYMENT_TRXN_UTIL'
2768 );
2769 END IF;
2770
2771 -- FND_MESSAGE.SET_NAME('ONT','Exception in Delete_Payment_Trxn'||sqlerrm);
2772 -- OE_MSG_PUB.Add;
2773
2774 OE_MSG_PUB.Count_And_Get
2775 ( p_count => l_msg_count,
2776 p_data => l_msg_data
2777 );
2778 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2779 END Delete_Payment_Trxn;
2780
2781 FUNCTION Get_Settled_Flag(p_Trxn_Extension_Id Number)
2782 RETURN VARCHAR2
2783 IS
2784 l_settled_flag VARCHAR2(1) := 'N';
2785 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2786 BEGIN
2787 IF l_debug_level > 0 THEN
2788 oe_debug_pub.add('Entering Get_Settled_Flag Function....');
2789 oe_debug_pub.add('Trxn extension id ---> '||p_Trxn_Extension_id);
2790 END IF;
2791
2792 BEGIN
2793 IF p_trxn_extension_id IS NOT NULL AND
2794 NOT OE_GLOBALS.Equal(p_trxn_Extension_id, FND_API.G_MISS_NUM) THEN
2795 -- bug 8586227
2796 select settled_flag
2797 into l_Settled_flag
2798 from IBY_EXTN_SETTLEMENTS_V
2799 where trxn_Extension_id = p_trxn_extension_id;
2800 END IF;
2801 IF l_debug_level > 0 THEN
2802 oe_debug_pub.add('Settled flag retrieved successfully...');
2803 oe_debug_pub.add('Value of settled flag ----> '||l_settled_flag);
2804 END IF;
2805 EXCEPTION
2806 WHEN NO_DATA_FOUND THEN
2807 l_settled_flag := 'N';
2808 IF l_debug_level > 0 THEN
2809 oe_debug_pub.add('The transaction extension not found in IBY Table...');
2810 END IF;
2811 WHEN OTHERS THEN
2812 NULL;
2813 END;
2814
2815 RETURN l_settled_flag;
2816 EXCEPTION
2817 WHEN OTHERS THEN
2818 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2819 THEN
2820 OE_MSG_PUB.Add_Exc_Msg
2821 ( G_PKG_NAME ,
2822 'Get_CC_Security_Code_Use'
2823 );
2824 END IF;
2825
2826 END Get_Settled_Flag;
2827
2828 FUNCTION Get_CC_Security_Code_Use
2829 RETURN VARCHAR2
2830 IS
2831 l_return_status VARCHAR2(30) := NULL ;
2832 l_msg_count NUMBER := 0 ;
2833 l_msg_data VARCHAR2(2000) := NULL ;
2834 L_response_code IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2835 l_channel_attrib_uses IBY_FNDCPT_SETUP_PUB.PmtChannel_AttribUses_rec_type;
2836
2837 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2838
2839 BEGIN
2840 IF l_debug_level > 0 THEN
2841 oe_debug_pub.add('Before calling Get_Payment_Channel_Attribs API...');
2842 oe_debug_pub.add('Payment channel ---> CREDIT_CARD');
2843 END IF;
2844
2845 IBY_FNDCPT_SETUP_PUB.Get_Payment_Channel_Attribs
2846 (p_api_version => 1.0,
2847 X_return_status => l_return_status,
2848 X_msg_count => l_msg_count,
2849 X_msg_data => l_msg_data,
2850 P_channel_code => 'CREDIT_CARD',
2851 X_channel_attrib_uses => l_channel_attrib_uses,
2852 X_response => l_response_code);
2853
2854 G_CC_Security_Code_Use := l_channel_attrib_uses.Instr_SecCode_Use;
2855 --G_CC_Security_Code_Use := 'OPTIONAL';
2856
2857 IF l_debug_level > 0 then
2858 oe_debug_pub.add('Return Status --> '||l_return_status);
2859 oe_debug_pub.add('Response code --> '||l_response_code.result_code);
2860 oe_debug_pub.add('Security code use ---> '||G_CC_Security_Code_Use);
2861 oe_debug_pub.add('Statement Billing Address Use ----> '|| l_channel_attrib_uses.Instr_Billing_Address);
2862 oe_debug_pub.add('Exiting OE_PAYMENT_TRXN_UTIL.Get_CC_Security_Code_Use: '||l_return_status, 1);
2863 END IF;
2864
2865 RETURN G_CC_Security_Code_Use;
2866
2867 EXCEPTION
2868
2869 WHEN OTHERS THEN
2870
2871 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2872 THEN
2873 OE_MSG_PUB.Add_Exc_Msg
2874 ( G_PKG_NAME ,
2875 'Get_CC_Security_Code_Use'
2876 );
2877 END IF;
2878
2879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2880
2881 END Get_CC_Security_Code_Use;
2882
2883 --CC Reversal ER#16014135 start
2884 PROCEDURE Reverse_CreditCard_Auth
2885 ( p_header_id IN NUMBER
2886 , p_line_id IN NUMBER DEFAULT NULL
2887 , p_msg_count OUT NOCOPY NUMBER
2888 , p_msg_data OUT NOCOPY VARCHAR2
2889 , p_return_status OUT NOCOPY VARCHAR2
2890 , p_new_trxn_extension_id IN NUMBER DEFAULT NULL
2891 )
2892 IS
2893 l_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
2894 l_payment_function VARCHAR2(30) := 'CUSTOMER_PAYMENT';
2895 l_invoice_to_org_id NUMBER;
2896 l_party_id NUMBER;
2897 l_cust_account_id NUMBER;
2898 l_org_type VARCHAR2(30) := 'OPERATING_UNIT';
2899 l_org_id NUMBER;
2900 l_trxn_extension_id NUMBER;
2901 l_rev_attribs IBY_FNDCPT_TRXN_PUB.RevAttribs_rec_type;
2902 l_reauth_amount IBY_FNDCPT_TRXN_PUB.Amount_rec_type;
2903 l_return_status VARCHAR2(30) := NULL;
2904 --l_result_status VARCHAR2(30) := NULL;
2905 l_msg_count NUMBER := 0 ;
2906 l_msg_data VARCHAR2(2000) := NULL ;
2907 l_err_message VARCHAR2(2000);
2908 l_reversal_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2909 l_reauth_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2910 l_reauth_result IBY_FNDCPT_TRXN_PUB.AuthResult_rec_type;
2911 l_authorized_amt number:=0;
2912 l_cancelled_flag VARCHAR2(1) := 'N';
2913 l_reversal_allowed VARCHAR2(1) :='N';
2914 l_voice_auth VARCHAR2(1) := 'N';
2915 l_cc_rev_reauth_code VARCHAR2(30) := NULL;
2916 l_settled_flag VARCHAR2(1) := 'N';
2917 l_currency_code VARCHAR2(20);
2918 l_format_mask VARCHAR2(500);
2919
2920 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2921 l_old_invoice_to_org_id NUMBER :=NULL;
2922 l_header_rec OE_Order_PUB.Header_Rec_Type;
2923 l_result_out VARCHAR2(30);
2924
2925 BEGIN
2926 --p_result_out := 'PASS' ;
2927 p_return_status := FND_API.G_RET_STS_SUCCESS;
2928
2929 IF l_debug_level > 0 THEN
2930 oe_debug_pub.add( 'OEXPVPMB: Entering CREDIT CARD AUTHORIZATION REVERSAL',5 ) ;
2931 oe_debug_pub.add('p_header_id :'||p_header_id);
2932 oe_debug_pub.add('p_line_id : '||p_line_id);
2933 END IF;
2934
2935 l_cc_rev_reauth_code := OE_VERIFY_PAYMENT_PUB.Get_CC_Rev_Reauth_Code(p_header_id);
2936
2937 IF l_cc_rev_reauth_code is not null THEN
2938 IF l_debug_level > 0 THEN
2939 oe_debug_pub.add( 'OEXPVPMB: Reversal code '||l_cc_rev_reauth_code,5 ) ;
2940 END IF;
2941 IF p_line_id IS NOT NULL THEN
2942 IF l_debug_level > 0 THEN
2943 oe_debug_pub.add( 'processing for Line id: '||p_line_id,5 ) ;
2944 END IF;
2945 BEGIN
2946 SELECT invoice_to_org_id, org_id
2947 INTO l_invoice_to_org_id,l_org_id
2948 FROM oe_order_lines_all
2949 WHERE line_id = p_line_id;
2950 EXCEPTION WHEN NO_DATA_FOUND THEN
2951 IF l_debug_level > 0 THEN
2952 oe_debug_pub.add('Invoice to Org id is null.....',5);
2953 END IF;
2954 END;
2955 ELSE
2956 IF l_debug_level > 0 THEN
2957 oe_debug_pub.add( 'processing for header ',5 ) ;
2958 END IF;
2959 BEGIN
2960 SELECT invoice_to_org_id, org_id
2961 INTO l_invoice_to_org_id,l_org_id
2962 FROM oe_order_headers_all
2963 WHERE header_id = p_header_id;
2964 EXCEPTION WHEN NO_DATA_FOUND THEN
2965 IF l_debug_level > 0 THEN
2966 oe_debug_pub.add('Invoice to Org id is null.....',5);
2967 END IF;
2968 END;
2969 END IF;
2970 IF l_debug_level > 0 THEN
2971 oe_debug_pub.add('invoice_to_org_id.....'||l_invoice_to_org_id,5);
2972 END IF;
2973
2974 Begin
2975 SELECT hca.party_id,acctsite.cust_account_id
2976 INTO l_party_id, l_cust_account_id
2977 FROM hz_cust_acct_sites_all acctsite, hz_cust_site_uses_all site, hz_cust_accounts_all hca
2978 WHERE SITE.SITE_USE_CODE = 'BILL_TO'
2979 AND SITE.CUST_ACCT_SITE_ID = ACCTSITE.CUST_ACCT_SITE_ID
2980 AND ACCTSITE.cust_account_id = HCA.cust_account_id
2981 AND SITE.SITE_USE_ID = l_invoice_to_org_id;
2982 Exception When No_Data_Found THEN
2983 IF l_debug_level > 0 THEN
2984 oe_debug_pub.add('Party id and Cust_account_id is null.....',5);
2985 END IF;
2986 End;
2987 IF l_debug_level > 0 THEN
2988 oe_debug_pub.add('l_party_id '||l_party_id||'l_cust_account_id '||l_cust_account_id ,5);
2989 END IF;
2990
2991 BEGIN
2992 SELECT transactional_curr_code
2993 INTO l_currency_code
2994 FROM oe_order_headers_all
2995 WHERE header_id = p_header_id;
2996 EXCEPTION WHEN NO_DATA_FOUND THEN
2997 IF l_debug_level > 0 THEN
2998 oe_debug_pub.add('Currency code is null.....',5);
2999 END IF;
3000 END;
3001 IF l_debug_level > 0 THEN
3002 oe_debug_pub.add('l_currency_code '||l_currency_code ,5);
3003 END IF;
3004
3005 IF p_line_id IS NULL THEN
3006 IF l_debug_level > 0 THEN
3007 oe_debug_pub.add('Before getting trxn id for header: '||p_header_id,5);
3008 END IF;
3009 BEGIN
3010 SELECT trxn_extension_id
3011 INTO l_trxn_extension_id
3012 FROM OE_PAYMENTS
3013 WHERE HEADER_ID = p_header_id and line_id is null
3014 AND nvl(payment_collection_event,'PREPAY') = 'INVOICE';
3015 EXCEPTION WHEN NO_DATA_FOUND THEN
3016 IF l_debug_level > 0 THEN
3017 oe_debug_pub.add('Trxn extension id is null.....',5);
3018 END IF;
3019 END;
3020 ELSE
3021 BEGIN
3022 IF l_debug_level > 0 THEN
3023 oe_debug_pub.add('Before getting trxn id line_id '||p_line_id||' and header_id '||p_header_id,5);
3024 END IF;
3025 SELECT trxn_extension_id
3026 INTO l_trxn_extension_id
3027 FROM OE_PAYMENTS
3028 WHERE line_id = p_line_id
3029 AND header_id = p_header_id
3030 AND payment_type_code <> 'COMMITMENT';
3031 EXCEPTION WHEN NO_DATA_FOUND THEN
3032 IF l_debug_level > 0 THEN
3033 oe_debug_pub.add('Trxn extension id....'||nvl(l_trxn_extension_id,'0'),5);
3034 END IF;
3035 END;
3036 END IF;
3037 IF l_debug_level > 0 THEN
3038 oe_debug_pub.add('Trxn extension id....'||nvl(l_trxn_extension_id,'0'),5);
3039 END IF;
3040 Begin
3041 SELECT nvl(REVERSAL_ALLOWED,'N'), nvl(AUTHORIZATION_AMOUNT,0)
3042 INTO l_reversal_allowed, l_authorized_amt
3043 FROM IBY_TRXN_EXT_AUTHS_V
3044 WHERE TRXN_EXTENSION_ID = l_trxn_extension_id
3045 AND nvl(authorization_amount,0) > 0
3046 AND authorization_status=0;
3047 exception
3048 when others then
3049 IF l_debug_level > 0 then
3050 oe_debug_pub.add('Unable to query for authorization flag of Trxn extension id....'||nvl(l_trxn_extension_id,'0')|| SQLERRM,5);
3051 END IF;
3052 end;
3053 IF l_debug_level > 0 THEN
3054 oe_debug_pub.add('l_reversal_allowed '||l_reversal_allowed||' l_authorized_amt '||l_authorized_amt ,5);
3055 END IF;
3056
3057 BEGIN
3058 SELECT ACCT_SITE_USE_ID
3059 INTO
3060 l_old_invoice_to_org_id
3061 FROM iby_external_payers_all
3062 WHERE ext_payer_id in
3063 (SELECT ext_payer_id
3064 FROM iby_fndcpt_tx_extensions
3065 WHERE trxn_extension_id =l_trxn_extension_id)
3066 AND rownum<2;
3067 EXCEPTION
3068 WHEN NO_DATA_FOUND THEN
3069 l_old_invoice_to_org_id :=null;
3070 WHEN OTHERS THEN
3071 l_old_invoice_to_org_id :=null;
3072 END;
3073 IF l_debug_level > 0 THEN
3074 oe_debug_pub.add('l_old_invoice_to_org_id :'||l_old_invoice_to_org_id,5);
3075 END IF;
3076 BEGIN
3077 SELECT nvl(VOICE_AUTHORIZATION_FLAG,'N')
3078 INTO l_voice_auth
3079 FROM iby_fndcpt_tx_extensions
3080 WHERE TRXN_EXTENSION_ID = l_trxn_extension_id;
3081 EXCEPTION WHEN NO_DATA_FOUND THEN
3082 l_voice_auth :='N';
3083 END;
3084 IF l_debug_level > 0 THEN
3085 oe_debug_pub.add('Voice authorization: '||l_voice_auth,5);
3086 END IF;
3087 IF l_voice_auth = 'Y' THEN
3088 IF l_debug_level > 0 THEN
3089 oe_debug_pub.add('This is Voice authorization, No Reversal/Re-authorization is allowed',5);
3090 END IF;
3091 RETURN;
3092 END IF;
3093
3094 BEGIN
3095 SELECT nvl(settled_flag, 'N')
3096 INTO l_settled_flag
3097 FROM IBY_EXTN_SETTLEMENTS_V
3098 WHERE trxn_extension_id = l_trxn_extension_id;
3099 EXCEPTION WHEN NO_DATA_FOUND THEN
3100 l_settled_flag := 'N';
3101 WHEN OTHERS then
3102 IF l_debug_level > 0 then
3103 oe_debug_pub.add('Unable to query for settlement flag of Trxn extension id....'||nvl(l_trxn_extension_id,'0')|| SQLERRM,5);
3104 END IF;
3105 END;
3106
3107 IF l_debug_level > 0 THEN
3108 oe_debug_pub.add('settled_flag '||l_settled_flag,5);
3109 END IF;
3110 IF l_settled_flag='N' and l_reversal_allowed ='Y' and l_authorized_amt > 0 then
3111 l_payer.payment_function := l_payment_function ;
3112 l_payer.party_id := l_party_id;
3113 l_payer.org_type := l_org_type;
3114 l_payer.org_id := l_org_id;
3115 l_payer.cust_account_id := l_cust_account_id;
3116 l_payer.account_site_id := nvl(l_old_invoice_to_org_id,l_invoice_to_org_id);
3117 l_rev_attribs.Reauth_flag := 'N';
3118 l_reauth_amount.value := 0;
3119 l_reauth_amount.currency_code := l_currency_code;
3120 l_format_mask := OE_VERIFY_PAYMENT_PUB.get_format_mask(l_currency_code);
3121 l_rev_attribs.Revised_amount := l_reauth_amount;
3122 IF l_debug_level > 0 THEN
3123 oe_debug_pub.add('Calling IBY Create Reversal',5);
3124 END IF;
3125 IBY_Fndcpt_Trxn_Pub.Create_Reversal
3126 (p_api_version => 1.0,
3127 p_init_msg_list => FND_API.G_FALSE,
3128 p_payer => l_payer,
3129 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
3130 p_trxn_entity_id => l_trxn_extension_id,
3131 p_rev_attribs => l_rev_attribs,
3132 x_return_status => l_return_status,
3133 x_msg_count => l_msg_count,
3134 x_msg_data => l_msg_data,
3135 x_reversal_response => l_reversal_response,
3136 x_reauth_response => l_reauth_response,
3137 x_reauth_result => l_reauth_result
3138 );
3139 IF l_debug_level > 0 then
3140 oe_debug_pub.add('Return Status after call to Create_Reversal'||l_return_status,5);
3141 OE_DEBUG_PUB.ADD('l_reversal_response.Result_Code: '||l_reversal_response.result_code,5);
3142 OE_DEBUG_PUB.ADD('l_reauth_response.result_code: '||l_reauth_response.result_code,5 );
3143 END IF;
3144 IF (l_return_status = FND_API.G_RET_STS_SUCCESS AND
3145 l_reversal_response.result_code = IBY_FNDCPT_TRXN_PUB.G_RC_REVERSAL_SUCCESS ) THEN
3146 IF l_debug_level > 0 THEN
3147 oe_debug_pub.add('Reversal successful....',5);
3148 END IF; --16594955 setting message token outside of l_debug_level condition
3149 FND_MESSAGE.SET_NAME('ONT','ONT_PAYMENT_REV_SUCCESS');
3150 FND_MESSAGE.SET_TOKEN('AMOUNT',TO_CHAR(l_authorized_amt,l_format_mask));
3151 OE_MSG_PUB.ADD;
3152 --END IF; 16594955
3153 -- update the payment amount field on order header, this is for backward compatibility
3154 -- we used to show the authorized amount on order header. We have to update header
3155 -- table directly here as we did in 11i code, as the payment amount field is also
3156 -- used for Cash and Check payment type.
3157 -- if line_id is null, then this is for header invoice payment.
3158 IF p_line_id IS NULL THEN
3159 UPDATE oe_order_headers_all
3160 SET payment_amount = 0
3161 , last_updated_by = FND_GLOBAL.USER_ID
3162 , last_update_date = SYSDATE
3163 , last_update_login = FND_GLOBAL.LOGIN_ID
3164 , lock_control = lock_control + 1
3165 WHERE header_id = p_header_id
3166 AND payment_type_code = 'CREDIT_CARD';
3167 END IF;
3168
3169 IF p_new_trxn_extension_id is not null THEN
3170 IF l_debug_level > 0 THEN
3171 oe_debug_pub.ADD('kadiraju new trx '||oe_verify_payment_pub.G_CC_Change_Flag);
3172 END IF;
3173 oe_verify_payment_pub.G_CC_Change_Flag := 'Y';
3174 /* UPDATE oe_payments
3175 SET trxn_extension_id = p_new_trxn_extension_id
3176 WHERE header_id = p_header_id
3177 AND trxn_extension_id=l_trxn_extension_id;
3178
3179 OE_Header_UTIL.Query_Row
3180 (p_header_id => p_header_id
3181 ,x_header_rec => l_header_rec
3182 );
3183 OE_Verify_Payment_PUB.Authorize_MultiPayments
3184 ( p_header_rec => l_header_rec
3185 ,p_line_id =>p_line_id
3186 , p_calling_action => 'REVERSAL_REAUTH'
3187 , p_risk_eval_flag => null
3188 , p_msg_count => l_msg_count
3189 , p_msg_data => l_msg_data
3190 , p_result_out => l_result_out
3191 , p_return_status => l_return_status
3192 );
3193 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3194 RAISE FND_API.G_EXC_ERROR;
3195 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3197 END IF;
3198 */
3199
3200 END IF;
3201 ELSE
3202 IF l_debug_level >0 THEN
3203 oe_debug_pub.add('Reversal failure...!'||l_return_status,5);
3204 oe_debug_pub.add('l_reversal_response.result_code'||l_reversal_response.result_code,5);
3205 oe_debug_pub.add('Message....'||l_reversal_response.result_message,5);
3206 oe_debug_pub.add('SQLERRM '||sqlerrm,5);
3207 END IF;
3208 IF p_line_id is NULL THEN
3209 OE_Verify_Payment_PUB.Apply_Verify_Hold
3210 ( p_header_id => p_header_id
3211 , p_hold_id => 11 -- Seeded Id for CC Auth Failure Hold
3212 , p_msg_count => l_msg_count
3213 , p_msg_data => l_msg_data
3214 , p_return_status => l_return_status
3215 );
3216 ELSE
3217 OE_Verify_Payment_PUB.Apply_Verify_Line_Hold
3218 ( p_header_id => p_header_id
3219 , p_line_id => p_line_id
3220 , p_hold_id => 11 -- Seeded Id for CC Auth Failure Hold
3221 , p_msg_count => l_msg_count
3222 , p_msg_data => l_msg_data
3223 , p_return_status => l_return_status
3224 );
3225 END IF;
3226 FND_MESSAGE.SET_NAME('ONT','ONT_CC_AUTH_HOLD_APPLIED');
3227 FND_MESSAGE.SET_TOKEN('AMOUNT',TO_CHAR(l_authorized_amt,l_format_mask));
3228 OE_MSG_PUB.ADD;
3229 p_return_status := l_return_status;
3230 oe_msg_pub.add_text(p_message_text => l_reversal_response.result_message);
3231 END IF;--end if of l_return_status
3232 END IF;-- reversal, settlement flag if condition
3233 ELSE
3234 IF l_debug_level > 0 THEN
3235 oe_debug_pub.add( 'OEXPVPMB: Reversal code '||l_cc_rev_reauth_code,5 ) ;
3236 END IF;
3237 END IF;
3238 IF l_debug_level > 0 THEN
3239 oe_debug_pub.add( 'OEXPVPMB: Exiting CREDIT CARD AUTHORIZATION REVERSAL',1 ) ;
3240 END IF;
3241 EXCEPTION
3242 WHEN FND_API.G_EXC_ERROR THEN
3243 l_err_message := SQLERRM;
3244
3245 IF l_debug_level > 0 THEN
3246 oe_debug_pub.add('Create_Reversal error....exc',5);
3247 oe_debug_pub.add('After call to Create_Reversal error'||l_return_status,5);
3248 oe_debug_pub.add('Result code'||l_reversal_response.result_code,5);
3249 oe_debug_pub.add('Error'||l_err_message,5);
3250 END IF;
3251
3252 p_return_status := FND_API.G_RET_STS_ERROR;
3253 OE_MSG_PUB.Count_And_Get
3254 ( p_count => l_msg_count,
3255 p_data => l_msg_data
3256 );
3257
3258 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3259 l_err_message := SQLERRM;
3260
3261 IF l_debug_level > 0 THEN
3262 oe_debug_pub.add('Create_Reversal error....unxc',5);
3263 oe_debug_pub.add('After call to Create_Reversal error'||l_return_status,5);
3264 oe_debug_pub.add('Result code'||l_reversal_response.result_code,5);
3265 oe_debug_pub.add('f Error'||l_err_message,5);
3266 END IF;
3267
3268 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3269 OE_MSG_PUB.Count_And_Get
3270 ( p_count => l_msg_count,
3271 p_data => l_msg_data
3272 );
3273
3274 WHEN OTHERS THEN
3275 l_err_message := SQLERRM;
3276 IF l_debug_level > 0 THEN
3277 oe_debug_pub.add('Returned error others part in Reverse_CreditCard_Auth...'||l_err_message,5);
3278 END IF;
3279 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3280
3281 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3282 THEN
3283 FND_MSG_PUB.Add_Exc_Msg
3284 ( G_PKG_NAME
3285 , 'Reverse_CreditCard_Auth'
3286 );
3287 END IF;
3288
3289 OE_MSG_PUB.Count_And_Get
3290 ( p_count => l_msg_count,
3291 p_data => l_msg_data
3292 );
3293 END Reverse_CreditCard_Auth;
3294 --CC Reversal ER#16014135 End
3295
3296 --R12 CC Encryption
3297 END OE_PAYMENT_TRXN_UTIL;