[Home] [Help]
PACKAGE BODY: APPS.OE_PAYMENT_TRXN_UTIL
Source
1 PACKAGE BODY OE_PAYMENT_TRXN_UTIL AS
2 /* $Header: OEXUPTXB.pls 120.28.12010000.2 2008/11/25 14:21:32 sgoli ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_PAYMENT_TRXN_UTIL';
4
5 Procedure Create_Payment_Trxn
6 (p_header_id IN NUMBER,
7 P_line_id IN NUMBER,
8 p_cust_id IN NUMBER,
9 P_site_use_id IN NUMBER,
10 P_payment_trx_id IN NUMBER,
11 P_payment_type_code IN VARCHAR2,
12 p_payment_number IN NUMBER, --Newly added
13 P_card_number IN VARCHAR2 DEFAULT NULL,
14 p_card_code IN VARCHAR2 DEFAULT NULL,
15 P_card_holder_name IN VARCHAR2 DEFAULT NULL,
16 P_exp_date IN VARCHAR2 DEFAULT NULL,
17 P_instrument_security_code IN VARCHAR2 DEFAULT NULL,
18 P_credit_card_approval_code IN VARCHAR2 DEFAULT NULL,
19 P_credit_card_approval_date IN DATE DEFAULT NULL,
20 p_instrument_id IN NUMBER DEFAULT NULL,
21 p_instrument_assignment_id IN NUMBER DEFAULT NULL,
22 p_receipt_method_id IN NUMBER,
23 p_update_card_flag IN VARCHAR2 DEFAULT 'N',
24 P_x_trxn_extension_id IN OUT NOCOPY NUMBER,
25 X_return_status OUT NOCOPY VARCHAR2,
26 X_msg_count OUT NOCOPY NUMBER,
27 X_msg_data OUT NOCOPY VARCHAR2)
28
29 IS
30 --R12 CC Encryption
31 L_credit_card_rec IBY_FNDCPT_SETUP_PUB.CreditCard_rec_Type;
32 L_card_exists VARCHAR2(1) := 'N';
33 L_return_status VARCHAR2(30);
34 L_msg_count NUMBER;
35 L_msg_data VARCHAR2(2000);
36 L_party_id NUMBER;
37 L_response_code IBY_FNDCPT_COMMON_PUB.Result_rec_type;
38 l_org_id NUMBER;
39 l_org_type VARCHAR2(80) := 'OPERATING_UNIT'; --Verify
40 L_card_id NUMBER;
41 L_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
42 L_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
43 L_instrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
44 L_assign_id NUMBER;
45 L_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
46 L_pmt_channel_code IBY_FNDCPT_PMT_CHNNLS_VL.payment_channel_code%TYPE;
47 l_instrument_type IBY_FNDCPT_PMT_CHNNLS_VL.instrument_type%TYPE;
48 --l_instrument_id IBY_FNDCPT_PAYER_ALL_INSTRS_V.instrument_id%TYPE;
49 l_trxn_extension_id NUMBER;
50 l_invoice_to_org_id OE_ORDER_LINES_ALL.invoice_to_org_id%TYPE;
51 l_err_message VARCHAR2(4000);
52 l_instrument_security_code VARCHAR2(30);
53 l_cust_account_id NUMBER;
54 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
55
56 l_party_site_id NUMBER;
57 l_instrument_id NUMBER; -- bug 5170754
58
59 BEGIN
60 IF l_debug_level > 0 THEN
61 oe_debug_pub.add('ENTERING OE_PAYMENT_TRXN_UTIL.Create_Payment_Trxn....');
62 oe_debug_pub.add('Instrument security code and payment number.....'||p_instrument_security_code||' and '||p_payment_number);
63 oe_debug_pub.add('Instrument id'||p_instrument_id);
64 oe_debug_pub.add('Instrument assgn id'||p_instrument_assignment_id);
65 oe_debug_pub.add('Trxn extension id'||P_x_trxn_extension_id);
66 END IF;
67 BEGIN
68
69 -- map payment type to the payment channel
70 IF p_payment_type_code IN( 'CREDIT_CARD') THEN
71 L_pmt_channel_code := p_payment_type_code;
72 ELSIF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
73 /*
74 select arm.payment_channel_code
75 into l_pmt_channel_code
76 from ar_receipt_methods arm
77 where arm.receipt_method_id = p_receipt_method_id;
78 */
79 l_pmt_channel_code := 'BANK_ACCT_XFER';
80 END IF;
81 IF l_debug_level > 0 THEN
82 oe_debug_pub.add('ksurendr payment channel code '||l_pmt_channel_code);
83 END IF;
84
85 EXCEPTION
86 WHEN OTHERS THEN
87 IF l_debug_level > 0 THEN
88 oe_debug_pub.add('Error in getting payment channel code'||l_pmt_channel_code);
89 oe_debug_pub.add('Return status'||l_return_status||l_msg_data);
90 RAISE;
91 END IF;
92 END;
93
94 -- BEGIN
95 IF p_line_id is not null then
96 Select INVOICE_TO_ORG_ID,ORG_ID into l_invoice_to_org_id,l_org_id
97 from oe_order_lines_all where header_id = p_header_id
98 and line_id = p_line_id;
99 ELSE
100 /*
101 select invoice_to_org_id,org_id into l_invoice_to_org_id,l_org_id
102 from oe_order_headers_all where header_id = p_header_id;
103 */
104 -- get cached value.
105 oe_order_cache.load_order_header(p_header_id);
106 l_invoice_to_org_id := OE_Order_Cache.g_header_rec.invoice_to_org_id;
107 l_org_id := OE_Order_Cache.g_header_rec.org_id;
108 END IF;
109 IF l_debug_level > 0 THEN
110 oe_debug_pub.add('Invoice_to_org_id'||l_invoice_to_org_id);
111 oe_debug_pub.add('org id'||l_org_id);
112 END IF;
113 /*
114 EXCEPTION
115 WHEN OTHERS THEN
116 l_err_message := SQLERRM;
117 IF l_debug_level > 0 THEN
118 oe_debug_pub.add('Error in getting org id'||l_err_message);
119 oe_debug_pub.add('Invoice to org id'||l_invoice_to_org_id);
120 oe_debug_pub.add('Org id'||l_org_id);
121 RAISE;
122 END IF;
123 END;
124 */
125
126 BEGIN
127 Select hca.party_id, acct_site.cust_account_id,acct_site.party_site_id
128 Into l_party_id, l_cust_account_id,l_party_site_id
129 From HZ_CUST_SITE_USES_ALL SITE,
130 HZ_CUST_ACCT_SITES ACCT_SITE,
131 HZ_CUST_ACCOUNTS_ALL HCA
132 Where SITE.SITE_USE_ID = p_site_use_id
133 AND SITE.SITE_USE_CODE = 'BILL_TO'
134 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
135 AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
136 AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
137
138 IF l_debug_level > 0 THEN
139 oe_debug_pub.add('Party id in Create payment trxn'||l_party_id);
140 END IF;
141
142 l_payer.payment_function := 'CUSTOMER_PAYMENT';
143 l_payer.party_id := l_party_id;
144 l_payer.org_type := l_org_type;
145 l_payer.org_id := l_org_id;
146 l_payer.cust_account_id := l_cust_account_id;
147 l_payer.account_site_id := p_site_use_id;
148 IF l_debug_level > 0 THEN
149 oe_debug_pub.add('Cust id and acct site id'||p_cust_id||'and'||p_site_use_id);
150 oe_debug_pub.add('Payer context values');
151 oe_debug_pub.add('Payment function --- CUSTOMER_PAYMENT');
152 oe_debug_pub.add('PARTY ID'||l_party_id);
153 oe_debug_pub.add('org_id'||l_org_id);
154 oe_debug_pub.add('org type'||l_org_type);
155 oe_debug_pub.add('cust acct id'||l_cust_account_id);
156 oe_debug_pub.add('account site id'||p_site_use_id);
157 END IF;
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 IF l_debug_level > 0 THEN
162 oe_debug_pub.add('Error in PARTY ID'||l_party_id);
163 oe_debug_pub.add('Invoice to org id'||l_invoice_to_org_id);
164 oe_debug_pub.add('Org id'||l_org_id);
165 oe_debug_pub.add('payment_number'||p_payment_number);
166 oe_debug_pub.add('Return status'||l_return_status||l_msg_data);
167 RAISE FND_API.G_EXC_ERROR;
168 END IF;
169 END;
170
171 --Getting the instrument type based on
172 --payment channel code
173 SELECT ifapc.instrument_type
174 INTO l_instrument_type
175 FROM iby_fndcpt_all_pmt_channels_v ifapc
176 WHERE ifapc.payment_channel_code = l_pmt_channel_code;
177
178 --if p_x_trxn_extension_id is null, then create a new id using the IN data.
179 --If it is not null, then we first get the instrument assignment id for
180 --the p_x_trxn_extension_id and use this assignment id to create a new
181 --trxn_extension_id, this is used for Copy Order
182 IF p_x_trxn_extension_id IS NULL THEN
183 -- get the l_trxn_attribs information
184 l_instrument.instrument_id := p_instrument_id;
185 l_instrument.instrument_type := l_instrument_type;
186 l_assignment_attribs.instrument := l_instrument;
187
188 IF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
189
190 --bug 5170754
191 /*IF p_payment_trx_id IS NOT NULL THEN
192 L_assign_id := p_payment_trx_id;
193 ELSE*/
194
195 --Since the ACH LOV shows the bank account numbers belonging
196 --to different assignments (bill to), always calling the
197 --set_payer_assignment API of payments to get the assignment
198 --id even though the assignment id is passed from the front end.
199
200 IF p_payment_trx_id IS NOT NULL THEN
201 IF l_debug_level > 0 THEN
202 oe_debug_pub.add('Assignment id for ach / direct debit --> '||p_payment_trx_id);
203 END IF;
204
205 SELECT INSTRUMENT_ID into
206 l_instrument_id from
207 IBY_FNDCPT_PAYER_ASSGN_INSTR_V
208 where INSTR_ASSIGNMENT_ID = p_payment_trx_id;
209
210 l_assignment_attribs.instrument.instrument_id := l_instrument_id;
211 END IF;
212
213 IF l_debug_level > 0 THEN
214 oe_debug_pub.add('Before call to Set payer instr assignment API...');
215 oe_debug_pub.add('Assignment attributes passed ');
216 oe_debug_pub.add('l_instrument.instrument_id ---> '||p_instrument_id);
217 oe_debug_pub.add('l_instrument.instrument_type -> '||l_instrument_type);
218 END IF;
219
220 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
221 (p_api_version => 1.0,
222 p_commit => FND_API.G_FALSE,
223 X_return_status => l_return_status,
224 X_msg_count => l_msg_count,
225 X_msg_data => l_msg_data,
226 P_payer => l_payer,
227 P_assignment_attribs => l_assignment_attribs,
228 X_assign_id => l_assign_id,
229 X_response => l_response_code);
230
231 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
232 IF l_debug_level > 0 THEN
233 oe_debug_pub.add('Result error code in set payer instr assignment'||l_response_code.result_code);
234 END IF;
235 RAISE FND_API.G_EXC_ERROR;
236 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
237 IF l_debug_level > 0 THEN
238 oe_debug_pub.add('Result error code in set payer instr assignment'||l_response_code.result_code);
239 END IF;
240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
241 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
242 IF l_debug_level > 0 THEN
243 oe_debug_pub.add('Set Payer instr assignment Successful....');
244 oe_debug_pub.add('After calling Set_Payer_Instr_Assignment');
245 oe_debug_pub.add('Instr assignment id'||l_assign_id);
246 END IF;
247 END IF;
248 --END IF; bug 5170754
249 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
250
251 IF p_update_card_flag = 'Y' AND p_instrument_id is not null THEN
252 L_credit_card_rec.expiration_date := p_exp_date;
253 l_credit_card_rec.Card_Holder_Name := p_card_holder_name;
254 l_credit_Card_rec.card_id := p_instrument_id;
255 IF l_debug_level > 0 THEN
256 oe_debug_pub.add('Before call to Update_Card API....');
257 oe_debug_pub.add('Expiration date --> '||p_exp_date);
258 oe_debug_pub.add('Holder Name ------> '||p_card_holder_name);
259 oe_debug_pub.add('Instrument id ----> '||p_instrument_id);
260 END IF;
261 IBY_FNDCPT_SETUP_PUB.Update_Card
262 (
263 p_api_version => 1.0,
264 p_init_msg_list => FND_API.G_TRUE,
265 p_commit => FND_API.G_FALSE,
266 x_return_status => l_return_status,
267 X_msg_count => l_msg_count,
268 X_msg_data => l_msg_data,
269 p_card_instrument => l_credit_card_rec,
270 x_response => l_response_code
271 );
272 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
273 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
274 IF l_debug_level > 0 THEN
275 oe_debug_pub.add('Result error code in Update_Card exp'||l_response_code.result_code);
276 END IF;
277 RAISE FND_API.G_EXC_ERROR;
278 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
279 IF l_debug_level > 0 THEN
280 oe_debug_pub.add('Result error code in Update_Card unxc'||l_response_code.result_code);
281 END IF;
282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
283 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
284 IF l_debug_level > 0 THEN
285 oe_debug_pub.add('Update_Card Successful....');
286 oe_debug_pub.add('Return status '||l_return_status);
287 END IF;
288 END IF;
289 END IF;
290
291 IF p_instrument_assignment_id IS NOT NULL THEN
292 IF l_debug_level > 0 THEN
293 oe_debug_pub.add('Before call to Set payer instr assignment API...');
294 oe_debug_pub.add('Assignment attributes passed ');
295 oe_debug_pub.add('l_instrument.instrument_id ---> '||p_instrument_id);
296 oe_debug_pub.add('l_instrument.instrument_type -> '||l_instrument_type);
297 END IF;
298
299 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
300 (p_api_version => 1.0,
301 p_commit => FND_API.G_FALSE,
302 X_return_status => l_return_status,
303 X_msg_count => l_msg_count,
304 X_msg_data => l_msg_data,
305 P_payer => l_payer,
306 P_assignment_attribs => l_assignment_attribs,
307 X_assign_id => l_assign_id,
308 X_response => l_response_code);
309
310 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
311 IF l_debug_level > 0 THEN
312 oe_debug_pub.add('Result error code in set payer instr assignment'||l_response_code.result_code);
313 END IF;
314 RAISE FND_API.G_EXC_ERROR;
315 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
316 IF l_debug_level > 0 THEN
317 oe_debug_pub.add('Result error code in set payer instr assignment'||l_response_code.result_code);
318 END IF;
319 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
320 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
321 IF l_debug_level > 0 THEN
322 oe_debug_pub.add('Set Payer instr assignment Successful....');
323 oe_debug_pub.add('After calling Set_Payer_Instr_Assignment');
324 oe_debug_pub.add('Instr assignment id'||l_assign_id);
325 END IF;
326 END IF;
327 --l_assign_id := p_instrument_assignment_id;
328 ELSE
329 IF l_debug_level > 0 THEN
330 oe_debug_pub.add('Values of credit card passed to process_credit_card');
331 oe_debug_pub.add('Owner id'||l_party_id);
332 oe_debug_pub.add('site use id /invoice to org id'||p_site_use_id);
333 oe_debug_pub.add('l_party_site_id/stmt billing address id'||l_party_site_id);
334 oe_debug_pub.add('card number'||p_card_number);
335 oe_debug_pub.add('expiration date'||p_exp_date);
336 oe_debug_pub.add('instrument_type'||l_instrument_type);
337 oe_debug_pub.add('Card issuer'||p_card_code);
338 oe_debug_pub.add('Instrument id'||l_assignment_attribs.instrument.instrument_id);
339 oe_debug_pub.add('call to process credit card');
340 END IF;
341
342 L_credit_card_rec.owner_id := l_party_id;
343 L_credit_card_rec.billing_address_id := l_party_site_id;
344 L_credit_card_rec.card_number := p_card_number;
345 L_credit_card_rec.expiration_date := p_exp_date;
346 L_credit_card_rec.instrument_type := l_instrument_type;
347 L_credit_card_rec.Card_Issuer := p_card_code;
348 l_credit_card_rec.Card_Holder_Name := p_card_holder_name;
349 --bug 5176015
350 IF L_credit_card_rec.card_number IS NOT NULL THEN
351 IBY_FNDCPT_SETUP_PUB.Process_Credit_Card
352 (
353 p_api_version => 1.0,
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_payer => l_payer,
359 P_credit_card => l_credit_card_rec,
360 P_assignment_attribs => l_assignment_attribs,
361 X_assign_id => l_assign_id,
362 X_response => l_response_code
363 );
364 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
365
366 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
367 IF l_debug_level > 0 THEN
368 oe_debug_pub.add('Result error code in Process_Credit_Card -->'||l_response_code.result_code);
369 END IF;
370 IF l_response_code.result_code = 'INVALID_CARD_NUMBER' THEN
371 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
372 OE_MSG_PUB.ADD;
373 IF l_debug_level > 0 THEN
374 oe_debug_pub.add( 'OEXUPTXB: Invalid card number or expiration date' ) ;
375 END IF;
376 ELSIF l_response_code.result_code = 'INVALID_ADDRESS' THEN
377 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_INVALID');
378 OE_MSG_PUB.ADD;
379 IF l_debug_level > 0 THEN
380 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address' ) ;
381 END IF;
382 ELSIF l_response_code.result_code = 'INVALID_CARD_ISSUER' THEN
383 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5012613
384 OE_MSG_PUB.ADD;
385 IF l_debug_level > 0 THEN
386 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address' ) ;
387 END IF;
388 ELSE --Setting a generic message bug 5244099
389 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
390 OE_MSG_PUB.ADD;
391 IF l_debug_level > 0 THEN
392 oe_debug_pub.add( 'OEXUPTXB: Setting the generic message' ) ;
393 END IF;
394 END IF;
395 RAISE FND_API.G_EXC_ERROR;
396 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
397 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5244099
398 OE_MSG_PUB.ADD;
399 IF l_debug_level > 0 THEN
400 oe_debug_pub.add('Unexpected result error code in Process_Credit_Card-->'||l_response_code.result_code);
401 END IF;
402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
403 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
404 IF l_debug_level > 0 THEN
405 oe_debug_pub.add('Process_Credit_Card assignment Successful....');
406 oe_debug_pub.add('After calling Process_Credit_Card');
407 oe_debug_pub.add('Instr assignment id'||l_assign_id);
408 END IF;
409 --If trxn extension id is null and approval code is not, then
410 --this approval code was obtained from outside payments system
411 --and we need to set up voice authorization in this case.
412 IF p_credit_card_approval_code IS NOT NULL THEN
413 L_trxn_attribs.VoiceAuth_flag := 'Y';
414 L_trxn_attribs.VoiceAuth_code := p_credit_card_approval_code;
415 L_trxn_attribs.VoiceAuth_date := p_credit_card_approval_date;
416 END IF;
417 END IF; -- return status
418 END IF;
419 --bug 5176015
420 END IF; -- assignment id not null
421 END IF;
422 --No need to create trxn extension id for check payments
423 L_trxn_attribs.order_id := p_header_id;
424 l_trxn_attribs.trxn_ref_number2 := p_payment_number;
425
426 -- store the line id in trx_ref_number1 if this is a line level payment
427 IF p_line_id IS NOT NULL THEN
428 l_trxn_attribs.trxn_ref_number1 := p_line_id;
429 END IF;
430
431 ELSE
432 -- p_x_trxn_extension_id is not null, then find the instrument assignment id first,
433 -- then create a new trxn transaction id using this assignment id. This is used in Copy Order.
434 IF l_debug_level > 0 THEN
435 oe_debug_pub.add('Before call to Get Transaction Extension API...');
436 oe_debug_pub.add('Trxn attributes passed ');
437 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 ---> '||p_line_id);
438 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 ---> '||p_payment_number);
439 oe_debug_pub.add('L_trxn_attribs.order_id -----------> '||p_header_id);
440 END IF;
441 IBY_FNDCPT_TRXN_PUB.Get_Transaction_Extension
442 (p_api_version => 1.0,
443 X_return_status => l_return_status,
444 X_msg_count => l_msg_count,
445 X_msg_data => l_msg_data,
446 P_entity_id => p_x_trxn_extension_id,
447 P_payer => l_payer,
448 X_trxn_attribs => l_trxn_attribs,
449 --x_authorized => l_authorized,
450 --x_settled => l_settled,
451 X_response => l_response_code);
452 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
453 IF l_debug_level > 0 THEN
454 oe_debug_pub.add('Result error code in Get_Transaction_Extension'||l_response_code.result_code);
455 END IF;
456 RAISE FND_API.G_EXC_ERROR;
457 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
458 IF l_debug_level > 0 THEN
459 oe_debug_pub.add('Result error code in Get_Transaction_Extension'||l_response_code.result_code);
460 END IF;
461 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
462 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
463 IF l_debug_level > 0 THEN
464 oe_debug_pub.add('Get_Transaction_Extension assignment Successful....');
465 oe_debug_pub.add('After call to Get Transaction Extension'||l_return_status);
466 oe_debug_pub.add('After call to get trxn...instr sec code'||l_trxn_attribs.instrument_security_code);
467 END IF;
468 END IF;
469
470 -- Based on the trxn extension id get the corresponding
471 -- assignment id from the payments table
472 select instr_assignment_id into l_assign_id
473 from IBY_TRXN_EXTENSIONS_V where trxn_extension_id = p_x_trxn_extension_id;
474 IF l_debug_level > 0 THEN
475 oe_debug_pub.add('Instrument assignment id for existing instrument'||l_assign_id);
476 END IF;
477
478 END IF;
479
480 --<populate data to l_trxn_attribs from l_assignment_attribs, l_credit_card_rec if it is a credit card>
481
482 --Oracle payments requires that the combination of order_id, trxn_ref_number1 and trxn_ref_number2
483 --in the record type l_trxn_attribs must provide a unique transaction identifier for the application
484
485
486 IF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
487 l_trxn_attribs.Originating_Application_Id := 660;
488 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
489 l_trxn_attribs.Originating_Application_Id := 660;
490 --For copy order call, the instrument security code is obtained from
491 --the original order by the call to Get_Transaction_Extension
492 IF p_x_trxn_extension_id is null then
493 l_trxn_attribs.Instrument_Security_Code := p_instrument_security_code;
494 ELSE
495 l_trxn_attribs.Instrument_Security_Code := NULL; --bug 5190146
496 END IF;
497
498 END IF;
499
500 IF l_debug_level > 0 THEN
501 oe_debug_pub.add('Before calling create_transaction extension');
502 oe_debug_pub.add('payment channel -->'||l_pmt_channel_code);
503 oe_debug_pub.add('Assignment id ---->'|| l_assign_id);
504 oe_debug_pub.add('trxn attributes record type values');
505 oe_debug_pub.add('l_trxn_attribs.Instrument_Security_Code --->'||p_instrument_security_code);
506 oe_debug_pub.add('l_trxn_attribs.Originating application id ---> 660');
507 oe_debug_pub.add('l_trxn_attribs.order_id ----> '||p_header_id);
508 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 --->'||p_line_id);
509 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 --->'||p_payment_number);
510 oe_debug_pub.add('l_trxn_attribs.VoiceAuth_date ---->'||p_credit_card_approval_date);
511 oe_debug_pub.add('l_trxn_attribs.VoiceAuth_code ---->'||p_credit_card_approval_code);
512 END IF;
513 --bug 5176015
514 IF l_assign_id IS NOT NULL THEN
515
516 -- bug 5575513, regarding authorization for copied order.
517 l_trxn_attribs.order_id := p_header_id;
518 l_trxn_attribs.trxn_ref_number1 := p_line_id;
519
520 IF l_debug_level > 0 THEN
521 oe_debug_pub.add('new l_trxn_attribs.order_id -----> '||l_trxn_attribs.order_id);
522 END IF;
523
524 IBY_Fndcpt_Trxn_Pub.Create_Transaction_Extension
525 (p_api_version => 1.0,
526 p_init_msg_list => FND_API.G_TRUE,
527 p_commit => FND_API.G_FALSE,
528 X_return_status => l_return_status,
529 X_msg_count => l_msg_count,
530 X_msg_data => l_msg_data,
531 P_payer => l_payer,
532 P_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
533 P_pmt_channel => l_pmt_channel_code,
534 P_instr_assignment => l_assign_id,
535 P_trxn_attribs => l_trxn_attribs,
536 x_entity_id => l_trxn_extension_id,
537 X_response => l_response_code);
538
539 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
540
541 IF l_debug_level > 0 THEN
542 oe_debug_pub.add('After calling Create_Transaction_Extension'||l_trxn_extension_id);
543 oe_debug_pub.add('Result code'||l_Response_code.result_code);
544 oe_debug_pub.add('Return status'||l_Return_Status);
545 END IF;
546
547 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
548 IF l_debug_level > 0 THEN
549 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
550 END IF;
551 RAISE FND_API.G_EXC_ERROR;
552 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
553 IF l_debug_level > 0 THEN
554 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
555 END IF;
556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
557 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
558 IF l_debug_level > 0 THEN
559 oe_debug_pub.add('Create_Transaction_Extension assignment Successful....');
560 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
561 oe_debug_pub.add('After call to create Transaction Extension');
562 oe_debug_pub.add('New trxn extension id'||l_trxn_extension_id);
563 oe_debug_pub.add('Return status'||l_return_status);
564 END IF;
565
566 -- bug 5204275
567 IF p_payment_type_code = 'CREDIT_CARD'
568 AND p_x_trxn_extension_id IS NULL
569 AND p_line_id IS NOT NULL
570 THEN
571 UPDATE oe_payments
572 SET credit_card_approval_code = NULL
573 WHERE header_id = p_header_id
574 AND line_id = p_line_id;
575 END IF;
576
577 END IF;
578 END IF;
579 --bug 5176015
580 P_x_trxn_extension_id := l_trxn_extension_id;
581 X_return_status := FND_API.G_RET_STS_SUCCESS;
582 IF l_debug_level > 0 THEN
583 oe_debug_pub.add('Exiting Create_Payment_Trxn.....');
584 END IF;
585 EXCEPTION
586
587 WHEN FND_API.G_EXC_ERROR THEN
588 l_err_message := SQLERRM;
589 IF l_debug_level > 0 THEN
590 oe_debug_pub.add('Create_Transaction_Extension assignment error....exc');
591 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
592 oe_debug_pub.add('Result code'||l_response_code.result_code);
593 oe_debug_pub.add('Error'||l_err_message);
594 END IF;
595
596 X_return_status := FND_API.G_RET_STS_ERROR;
597 OE_MSG_PUB.Count_And_Get
598 ( p_count => l_msg_count,
599 p_data => l_msg_data
600 );
601 RAISE FND_API.G_EXC_ERROR;
602
603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604 l_err_message := SQLERRM;
605 IF l_debug_level > 0 THEN
606 oe_debug_pub.add('Create_Transaction_Extension assignment error....unxc');
607 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
608 oe_debug_pub.add('Result code'||l_response_code.result_code);
609 oe_debug_pub.add('f Error'||l_err_message);
610 END IF;
611
612 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
613 OE_MSG_PUB.Count_And_Get
614 ( p_count => l_msg_count,
615 p_data => l_msg_data
616 );
617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
618
619 WHEN OTHERS THEN
620 l_err_message := SQLERRM;
621 IF l_debug_level > 0 THEN
622 oe_debug_pub.add('Create_Transaction_Extension assignment error....others');
623 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
624 oe_debug_pub.add('Result code'||l_response_code.result_code);
625 oe_debug_pub.add('trx Error'||l_err_message);
626 END IF;
627
628 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
629 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
630 THEN
631 FND_MSG_PUB.Add_Exc_Msg
632 ( G_PKG_NAME
633 , 'OE_PAYMENT_TRXN_UTIL'
634 );
635 END IF;
636
637 OE_MSG_PUB.Count_And_Get
638 ( p_count => l_msg_count,
639 p_data => l_msg_data
640 );
641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
642 END Create_Payment_Trxn;
643
644 Procedure Update_Payment_Trxn
645 (p_header_id IN NUMBER,
646 P_line_id IN NUMBER,
647 p_cust_id IN NUMBER,
648 P_site_use_id IN NUMBER,
649 p_payment_trx_id IN NUMBER,
650 p_payment_type_code IN VARCHAR2,
651 p_payment_number IN NUMBER, --New
652 p_card_number IN VARCHAR2,
653 P_card_code IN VARCHAR2,
654 p_card_holder_name IN VARCHAR2,
655 p_exp_date IN DATE,
656 p_instrument_security_code IN VARCHAR2,
657 --Bug 7460481 starts
658 P_credit_card_approval_code IN VARCHAR2 DEFAULT NULL,
659 P_credit_card_approval_date IN DATE DEFAULT NULL,
660 --Bug 7460481 ends
661 p_instrument_id IN NUMBER DEFAULT NULL,
662 p_instrument_assignment_id IN NUMBER DEFAULT NULL,
663 p_receipt_method_id IN NUMBER,
664 p_update_card_flag IN VARCHAR2 DEFAULT 'N',
665 p_trxn_extension_id IN OUT NOCOPY NUMBER, --bug 4885313
666 X_return_status OUT NOCOPY VARCHAR2,
667 X_msg_count OUT NOCOPY NUMBER,
668 X_msg_data OUT NOCOPY VARCHAR2)
669
670 IS
671 --R12 CC Encryption
672 L_return_status VARCHAR2(30);
673 L_msg_count NUMBER;
674 L_msg_data VARCHAR2(2000);
675 L_party_id NUMBER;
676 p_card_type VARCHAR2(80);
677 L_response_code IBY_FNDCPT_COMMON_PUB.Result_rec_type;
678 L_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
679 L_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
680 l_org_id NUMBER;
681 l_org_type VARCHAR2(80) := 'OPERATING_UNIT';
682 l_instrument_type IBY_FNDCPT_PMT_CHNNLS_VL.instrument_type%TYPE;
683 l_instrument_id IBY_FNDCPT_PAYER_ALL_INSTRS_V.instrument_id%TYPE := p_instrument_id;
684 l_instrument_assignment_id IBY_FNDCPT_PAYER_ASSGN_INSTR_V.instr_assignment_id%TYPE := p_instrument_assignment_id;
685 L_credit_card_rec IBY_FNDCPT_SETUP_PUB.CreditCard_rec_Type;
686 L_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
687 L_instrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
688 L_assign_id NUMBER;
689 l_invoice_to_org_id OE_ORDER_LINES_ALL.invoice_to_org_id%TYPE;
690 L_pmt_channel_code IBY_FNDCPT_PMT_CHNNLS_VL.payment_channel_code%TYPE;
691 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
692 l_payment_number NUMBER;
693 l_cust_account_id NUMBER; --New
694 l_party_site_id NUMBER;
695 --Bug 4885313
696 l_trxn_extension_id NUMBER;
697
698 --bug 5028932
699 l_approval_code VARCHAR2(80);
700 l_settled_flag VARCHAR2(1);
701 l_effective_auth_amount NUMBER;
702 l_reauthorize_flag VARCHAR2(1);
703
704 --bug 5299050
705 l_old_instrument_id NUMBER;
706 l_old_card_number VARCHAR2(80);
707
708 l_pos NUMBER := 0;
709 l_retry_num NUMBER := 0;
710 l_trxn_ref_number2 NUMBER;
711
712 BEGIN
713
714
715 IF l_debug_level > 0 THEN
716 oe_debug_pub.add('Entering OE_PAYMENT_TRXN_UTIL.Update_Payment_Trxn...');
717 END IF;
718
719 IF p_payment_type_code IN( 'CREDIT_CARD') THEN
720 L_pmt_channel_code := p_payment_type_code;
721 ELSIF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
722 /*
723 select arm.payment_channel_code
724 into l_pmt_channel_code
725 from ar_receipt_methods arm
726 where arm.receipt_method_id = p_receipt_method_id;
727 */
728
729 l_pmt_channel_code := 'BANK_ACCT_XFER';
730 END IF;
731
732 IF l_debug_level > 0 THEN
733 oe_debug_pub.add('Payment channel code returned --->'||l_pmt_channel_code);
734 END IF;
735
736 --Getting the instrument type based on
737 --payment channel code
738 SELECT ifapc.instrument_type
739 INTO l_instrument_type
740 FROM iby_fndcpt_all_pmt_channels_v ifapc
741 WHERE ifapc.payment_channel_code = l_pmt_channel_code;
742
743 IF l_debug_level > 0 THEN
744 oe_debug_pub.add('Instrument type and instrument id'||l_instrument_type||' and '||l_instrument_id);
745 END IF;
746
747 --Get party id for the bill to site Verify
748
749 IF p_line_id is not null then
750 Select INVOICE_TO_ORG_ID,ORG_ID into l_invoice_to_org_id,l_org_id
751 from oe_order_lines_all where header_id = p_header_id and line_id = p_line_id;
752 else
753 /*
754 select invoice_to_org_id,org_id into l_invoice_to_org_id,l_org_id
755 from oe_order_headers_all where header_id = p_header_id;
756 */
757
758 oe_order_cache.load_order_header(p_header_id);
759 l_invoice_to_org_id := OE_Order_Cache.g_header_rec.invoice_to_org_id;
760 l_org_id := OE_Order_Cache.g_header_rec.org_id;
761 end if;
762
763 IF l_debug_level > 0 THEN
764 oe_debug_pub.add('INVOICE_TO_ORG_ID and ORG_ID'||l_invoice_to_org_id||' and '||l_org_id);
765 END IF;
766
767 Begin
768 Select hca.party_id, acct_site.cust_account_id,acct_site.party_site_id
769 Into l_party_id, l_cust_account_id,l_party_site_id
770 From HZ_CUST_SITE_USES_ALL SITE,
771 HZ_CUST_ACCT_SITES ACCT_SITE,
772 HZ_CUST_ACCOUNTS_ALL HCA
773 Where SITE.SITE_USE_ID = p_site_use_id
774 AND SITE.SITE_USE_CODE = 'BILL_TO'
775 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
776 AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
777 AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
778
779 Exception
780 When No_Data_Found THEN
781 Null;
782 End;
783
784 IF l_debug_level > 0 THEN
785 oe_debug_pub.add('Party id retrieved from hz tables-->'||l_party_id);
786 oe_debug_pub.add('Payment trxid..'||p_payment_trx_id);
787 END IF;
788
789
790 l_payer.payment_function := 'CUSTOMER_PAYMENT';
791 l_payer.party_id := l_party_id;
792 l_payer.org_type := l_org_type;
793 l_payer.org_id := l_org_id;
794 l_payer.cust_account_id := l_cust_account_id;
795 l_payer.account_site_id := p_site_use_id;
796
797 IF l_debug_level > 0 THEN
798 oe_debug_pub.add('Payer context values');
799 oe_debug_pub.add('Payment function --- CUSTOMER_PAYMENT');
800 oe_debug_pub.add('PARTY ID'||l_party_id);
801 oe_debug_pub.add('org_id'||l_org_id);
802 oe_debug_pub.add('org type'||l_org_type);
803 oe_debug_pub.add('cust acct id'||l_cust_account_id);
804 oe_debug_pub.add('account site id'||p_site_use_id);
805 END IF;
806
807 IF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
808 --bug 5170754
809 /*IF p_payment_trx_id IS NOT NULL THEN
810 L_assign_id := p_payment_trx_id;
811 ELSE*/
812
813 --Since the ACH LOV shows the bank account numbers belonging
814 --to different assignments (bill to), always calling the
815 --set_payer_assignment API of payments to get the assignment
816 --id even though the assignment id is passed from the front end.
817 IF p_payment_trx_id IS NOT NULL THEN
818 IF l_debug_level > 0 THEN
819 oe_debug_pub.add('Assignment id for ach / direct debit --> '||p_payment_trx_id);
820 END IF;
821
822 SELECT INSTRUMENT_ID into
823 l_instrument_id from
824 IBY_FNDCPT_PAYER_ASSGN_INSTR_V
825 where INSTR_ASSIGNMENT_ID = p_payment_trx_id;
826 END IF;
827
828
829 l_instrument.instrument_id := l_instrument_id;
830 l_instrument.instrument_type := l_instrument_type;
831 l_assignment_attribs.instrument := l_instrument;
832
833 IF l_debug_level > 0 THEN
834 oe_debug_pub.add('Before call to Set payer instr assignment API...');
835 oe_debug_pub.add('Assignment attributes passed ');
836 oe_debug_pub.add('l_instrument.instrument_id ---> '||l_instrument_id);
837 oe_debug_pub.add('l_instrument.instrument_type -> '||l_instrument_type);
838 END IF;
839
840 --<p_payment_trx_id stores the instr_assignment_id, if not null, then no need to call this API>
841 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
842 (p_api_version => 1.0,
843 p_commit => FND_API.G_FALSE,
844 X_return_status => l_return_status,
845 X_msg_count => l_msg_count,
846 X_msg_data => l_msg_data,
847 P_payer => l_payer,
848 P_assignment_attribs => l_assignment_attribs,
849 X_assign_id => l_assign_id,
850 X_response => l_response_code);
851
852 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
853
854 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
855 IF l_debug_level > 0 THEN
856 oe_debug_pub.add('Result error code in Set_Payer_Instr_Assignment'||l_response_code.result_code);
857 END IF;
858 IF l_response_code.result_code = 'INVALID_CARD_NUMBER' THEN
859 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
860 OE_MSG_PUB.ADD;
861 IF l_debug_level > 0 THEN
862 oe_debug_pub.add( 'OEXUPTXB: Invalid card number or expiration date' ) ;
863 END IF;
864 ELSIF l_response_code.result_code = 'INVALID_ADDRESS' THEN
865 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_INVALID');
866 OE_MSG_PUB.ADD;
867 IF l_debug_level > 0 THEN
868 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address' ) ;
869 END IF;
870 ELSIF l_response_code.result_code = 'INVALID_CARD_ISSUER' THEN
871 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5012613
872 OE_MSG_PUB.ADD;
873 IF l_debug_level > 0 THEN
874 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address' ) ;
875 END IF;
876 END IF;
877 RAISE FND_API.G_EXC_ERROR;
878 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
879 IF l_debug_level > 0 THEN
880 oe_debug_pub.add('Result error code in Set_Payer_Instr_Assignment'||l_response_code.result_code);
881 END IF;
882 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
883 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
884 IF l_debug_level > 0 THEN
885 oe_debug_pub.add('Set_Payer_Instr_Assignment assignment Successful....');
886 oe_debug_pub.add('After calling Set_Payer_Instr_Assignment');
887 oe_debug_pub.add('Instr assignment id'||l_assign_id);
888 END IF;
889 END IF;
890
891 --END IF; --bug 5170754
892 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
893
894 IF l_debug_level > 0 THEN
895 oe_debug_pub.add('l_inst id'||l_instrument_id);
896 oe_debug_pub.add('Instr assignment id'||l_instrument_assignment_id);
897 oe_debug_pub.add('X value'||instr(p_card_number,'X'));
898 END IF;
899
900 IF p_update_card_flag = 'Y' AND l_instrument_id is not null THEN
901 IF l_debug_level > 0 THEN
902 oe_debug_pub.add('Before calling update_card..');
903 oe_debug_pub.add('Expiration date passed...'||p_exp_date);
904 oe_debug_pub.add('Holder name'||p_card_holder_name);
905 oe_debug_pub.add('Instrument id'||l_instrument_id);
906 END IF;
907 L_credit_card_rec.expiration_date := p_exp_date;
908 l_credit_card_rec.Card_Holder_Name := p_card_holder_name;
909 l_credit_Card_rec.card_id := l_instrument_id;
910 IBY_FNDCPT_SETUP_PUB.Update_Card
911 (
912 p_api_version => 1.0,
913 p_init_msg_list => FND_API.G_TRUE,
914 p_commit => FND_API.G_FALSE,
915 x_return_status => l_return_status,
916 X_msg_count => l_msg_count,
917 X_msg_data => l_msg_data,
918 p_card_instrument => l_credit_card_rec,
919 x_response => l_response_code
920 );
921 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
922
923 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
924 IF l_debug_level > 0 THEN
925 oe_debug_pub.add('Result error code in Update_Card exp'||l_response_code.result_code);
926 END IF;
927 RAISE FND_API.G_EXC_ERROR;
928 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
929 IF l_debug_level > 0 THEN
930 oe_debug_pub.add('Result error code in Update_Card unxc'||l_response_code.result_code);
931 END IF;
932 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
933 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
934 IF l_debug_level > 0 THEN
935 oe_debug_pub.add('Update_Card Successful....');
936 oe_debug_pub.add('Return status '||l_return_status);
937 END IF;
938 END IF; --return status
939 END IF; --update card flag
940
941 IF l_instrument_assignment_id IS NOT NULL THEN
942 IF l_debug_level > 0 THEN
943 oe_debug_pub.add('Before call to Set payer instr assignment API...');
944 oe_debug_pub.add('Assignment attributes passed ');
945 oe_debug_pub.add('l_instrument.instrument_id ---> '||l_instrument_id);
946 oe_debug_pub.add('l_instrument.instrument_type -> '||l_instrument_type);
947 END IF;
948 l_instrument.instrument_id := l_instrument_id;
949 l_instrument.instrument_type := l_instrument_type;
950 l_assignment_attribs.instrument := l_instrument;
951
952 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
953 (p_api_version => 1.0,
954 p_commit => FND_API.G_FALSE,
955 X_return_status => l_return_status,
956 X_msg_count => l_msg_count,
957 X_msg_data => l_msg_data,
958 P_payer => l_payer,
959 P_assignment_attribs => l_assignment_attribs,
960 X_assign_id => l_assign_id,
961 X_response => l_response_code);
962
963 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
964 IF l_debug_level > 0 THEN
965 oe_debug_pub.add('Result error code in set payer instr assignment'||l_response_code.result_code);
966 END IF;
967 RAISE FND_API.G_EXC_ERROR;
968 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
969 IF l_debug_level > 0 THEN
970 oe_debug_pub.add('Result error code in set payer instr assignment'||l_response_code.result_code);
971 END IF;
972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
973 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
974 IF l_debug_level > 0 THEN
975 oe_debug_pub.add('Set Payer instr assignment Successful....');
976 oe_debug_pub.add('After calling Set_Payer_Instr_Assignment');
977 oe_debug_pub.add('Instr assignment id'||l_assign_id);
978 END IF;
979 END IF;
980 ELSE
981 L_credit_card_rec.owner_id := l_party_id;
982 L_credit_card_rec.billing_address_id := l_party_site_id;
983 L_credit_card_rec.card_number := p_card_number;
984 L_credit_card_rec.expiration_date := p_exp_date;
985 l_credit_card_rec.Card_Holder_Name := p_card_holder_name;
986 L_credit_card_rec.card_issuer := p_card_code;
987 L_credit_card_rec.instrument_type := l_instrument_type;
988
989 IF l_debug_level > 0 THEN
990 oe_debug_pub.add('Before calling process credit card....');
991 oe_debug_pub.add('l_party_site_id/stmt billing add'||l_party_site_id);
992 oe_debug_pub.add('site use id/invoice to org'||p_site_use_id);
993 oe_debug_pub.add('card number'||p_card_number);
994 oe_debug_pub.add('expiration date'||p_exp_date);
995 oe_debug_pub.add('instrument_type'||l_instrument_type);
996 oe_debug_pub.add('Card issuer'||p_card_code);
997 oe_debug_pub.add('Instrument id'||l_instrument_id);
998 oe_debug_pub.add('Holder name'||p_card_holder_name);
999 oe_debug_pub.add('call to process credit card');
1000 END IF;
1001
1002 IBY_FNDCPT_SETUP_PUB.Process_Credit_Card
1003 (p_api_version => 1.0,
1004 p_commit => FND_API.G_FALSE,
1005 X_return_status => l_return_status,
1006 X_msg_count => l_msg_count,
1007 X_msg_data => l_msg_data,
1008 P_payer => l_payer,
1009 P_credit_card => l_credit_card_rec,
1010 P_assignment_attribs => l_assignment_attribs,
1011 X_assign_id => l_assign_id,
1012 X_response => l_response_code);
1013
1014 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
1015
1016 IF l_debug_level > 0 THEN
1017 oe_debug_pub.add('After call to process credit card....');
1018 END IF;
1019
1020
1021 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1022 IF l_debug_level > 0 THEN
1023 oe_debug_pub.add('Result error code in Process_Credit_Card -->'||l_response_code.result_code);
1024 END IF;
1025 IF l_response_code.result_code = 'INVALID_CARD_NUMBER' THEN
1026 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
1027 OE_MSG_PUB.ADD;
1028 IF l_debug_level > 0 THEN
1029 oe_debug_pub.add( 'OEXUPTXB: Invalid card number or expiration date' ) ;
1030 END IF;
1031 ELSIF l_response_code.result_code = 'INVALID_ADDRESS' THEN
1032 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_INVALID');
1033 OE_MSG_PUB.ADD;
1034 IF l_debug_level > 0 THEN
1035 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address' ) ;
1036 END IF;
1037 ELSIF l_response_code.result_code = 'INVALID_CARD_ISSUER' THEN
1038 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5012613
1039 OE_MSG_PUB.ADD;
1040 IF l_debug_level > 0 THEN
1041 oe_debug_pub.add( 'OEXUPTXB: Invalid billing address' ) ;
1042 END IF;
1043 ELSE --Setting a generic message bug 5244099
1044 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET');
1045 OE_MSG_PUB.ADD;
1046 IF l_debug_level > 0 THEN
1047 oe_debug_pub.add( 'OEXUPTXB: Setting the generic message' ) ;
1048 END IF;
1049 END IF;
1050 RAISE FND_API.G_EXC_ERROR;
1051 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1052 FND_MESSAGE.SET_NAME('ONT','OE_VPM_CC_ACCT_NOT_SET'); --bug 5244099
1053 OE_MSG_PUB.ADD;
1054 IF l_debug_level > 0 THEN
1055 oe_debug_pub.add('Unexpected result error code in Process_Credit_Card-->'||l_response_code.result_code);
1056 END IF;
1057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1058 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1059 IF l_debug_level > 0 THEN
1060 oe_debug_pub.add('Process_Credit_Card assignment Successful....');
1061 oe_debug_pub.add('After calling Process_Credit_Card');
1062 oe_debug_pub.add('Instr assignment id'||l_assign_id);
1063 END IF;
1064 END IF; --Return status
1065 END IF;--Instrument assignment id
1066 END IF;--payment type code
1067 --No need to create trxn extension ids for check payments
1068 --as it would be stored in OM tables itself
1069
1070 L_trxn_attribs.order_id := p_header_id;
1071 L_trxn_attribs.trxn_ref_number2 := p_payment_number;
1072 -- store the line id in trx_ref_number1 if this is a line level payment
1073 IF p_line_id IS NOT NULL THEN
1074 l_trxn_attribs.trxn_ref_number1 := p_line_id;
1075 END IF;
1076 --END IF;
1077 IF OE_Payment_Trxn_Util.g_old_bill_to_site IS NULL THEN
1078 --<populate data to l_trxn_attribs from l_assignment_attribs, l_credit_card_rec if it is a credit card>
1079 --<Update the transaction in the IBY payment trasaction extenstion table>
1080 IF l_debug_level > 0 THEN
1081 oe_debug_pub.add('Calling update transaction extension...');
1082 END IF;
1083
1084 IF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
1085 l_trxn_attribs.Originating_Application_Id := 660;
1086 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
1087 l_trxn_attribs.Originating_Application_Id := 660;
1088
1089 --Need to pass the instrument security code as G_MISS_CHAR
1090 --if the Security code is null to the payments API to update
1091 --the value appropriately.
1092 IF p_instrument_security_code is not null then
1093 l_trxn_attribs.Instrument_Security_Code := p_instrument_security_code;
1094 ELSE
1095 l_trxn_attribs.Instrument_Security_Code := FND_API.G_MISS_CHAR;
1096 END IF;
1097 END IF;
1098
1099 IF l_debug_level > 0 THEN
1100 oe_debug_pub.add('Values passed to Update_trxn_extn');
1101 oe_debug_pub.add('payment channel -->'||l_pmt_channel_code);
1102 oe_debug_pub.add('Assignment id ---->'|| l_assign_id);
1103 oe_debug_pub.add('trxn attributes record type values');
1104 oe_debug_pub.add('l_trxn_attribs.Instrument_Security_Code --->'||p_instrument_security_code);
1105 oe_debug_pub.add('l_trxn_attribs.Originating application id ---> 660');
1106 oe_debug_pub.add('l_trxn_attribs.order_id ----> '||p_header_id);
1107 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 --->'||p_line_id);
1108 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 --->'||p_payment_number);
1109 --oe_debug_pub.add('l_trxn_attribs.VoiceAuth_date ---->'||p_credit_card_approval_date);
1110 --oe_debug_pub.add('l_trxn_attribs.VoiceAuth_code ---->'||p_credit_card_approval_code);
1111 END IF;
1112
1113 --bug 5028932
1114 BEGIN
1115 SELECT AUTHORIZATION_CODE into
1116 l_approval_code FROM IBY_TRXN_EXT_AUTHS_V
1117 WHERE TRXN_EXTENSION_ID = p_trxn_extension_id;
1118 EXCEPTION
1119 WHEN OTHERS THEN
1120 l_approval_code := null;
1121 IF l_debug_level >0 THEN
1122 oe_debug_pub.add('Others part approval code value ---> '||l_approval_code);
1123 END IF;
1124 END;
1125
1126 -- to check if the authorization has been settled
1127 BEGIN
1128 SELECT nvl(settled_flag, 'N'),instrument_id,card_number
1129 INTO l_settled_flag,l_old_instrument_id,l_old_card_number
1130 FROM iby_trxn_extensions_v
1131 WHERE trxn_extension_id = p_trxn_extension_id;
1132 EXCEPTION WHEN NO_DATA_FOUND THEN
1133 l_settled_flag := 'N';
1134 l_old_instrument_id := NULL;
1135 l_old_card_number := NULL;
1136 END;
1137
1138 IF l_debug_level > 0 THEN
1139 oe_debug_pub.add('Settled Flag value....'||l_settled_flag);
1140 END IF;
1141
1142
1143 --If approval code is not null then the transaction
1144 --extension has already been authorized once. So cannot
1145 --call update_transaction API for this trxn_extension_id
1146 IF (l_approval_code IS NOT NULL AND
1147 NOT OE_GLOBALS.Equal(l_approval_code,FND_API.G_MISS_CHAR))
1148 OR l_settled_flag = 'Y' THEN
1149
1150 IF l_settled_flag = 'N' THEN
1151 -- need to re-authorize if the authorization has expired.
1152 -- effective_auth_amount of 0 indicates auth has expired.
1153 -- the auth would be valid if authorization_amount is equal to
1154 -- effective_auth_amount
1155 BEGIN
1156 SELECT effective_auth_amount
1157 INTO l_effective_auth_amount
1158 FROM iby_trxn_ext_auths_v
1159 WHERE trxn_extension_id = p_trxn_extension_id
1160 AND nvl(authorization_amount,0) > 0
1161 AND authorization_status=0;
1162 EXCEPTION WHEN NO_DATA_FOUND THEN
1163 --This case is not possible as the approval code
1164 --for the transaction extension id is not null which
1165 --means that the transaction has been authorized atleast once
1166 NULL;
1167 END;
1168
1169 IF nvl(l_effective_auth_amount,0) = 0 THEN
1170 l_reauthorize_flag := 'Y';
1171 IF l_debug_level > 0 THEN
1172 oe_debug_pub.add( 'OEXUPTXB: authorization has either expired or not exists.');
1173 END IF;
1174 END IF;
1175
1176 IF l_debug_level > 0 THEN
1177 oe_debug_pub.add('Reauthorize flag value ----> '||l_reauthorize_flag);
1178 END IF;
1179 END IF;
1180
1181 IF l_debug_level > 0 THEN
1182 oe_debug_pub.add('Old instrument_id ---> '||l_old_instrument_id);
1183 oe_debug_pub.add('New instrument id..... '||l_instrument_id);
1184 oe_debug_pub.add('Old card number -----> '||l_old_card_number);
1185 oe_debug_pub.add('New card number -----> '||p_card_number);
1186 END IF;
1187
1188 IF l_settled_flag = 'Y' OR l_reauthorize_flag = 'Y' THEN
1189
1190 IF l_debug_level > 0 THEN
1191 oe_debug_pub.add( 'OEXUPTXB.pls: authorization has been settled, need to re-authorize.');
1192 END IF;
1193
1194 IF Oe_Payment_Trxn_Util.Get_CC_Security_Code_Use = 'REQUIRED'
1195 AND (l_trxn_attribs.Instrument_Security_Code IS NULL OR
1196 OE_GLOBALS.Equal(l_trxn_attribs.Instrument_Security_Code,FND_API.G_MISS_CHAR))
1197 THEN
1198
1199 FND_MESSAGE.SET_NAME('ONT','OE_CC_SECURITY_CODE_REQD');
1200 OE_Msg_Pub.Add;
1201 RAISE FND_API.G_EXC_ERROR;
1202
1203 ELSIF Oe_Payment_Trxn_Util.Get_CC_Security_Code_Use = 'REQUIRED'
1204 AND l_trxn_attribs.Instrument_Security_Code IS NOT NULL
1205 AND NOT OE_GLOBALS.Equal(l_trxn_attribs.Instrument_Security_Code,FND_API.G_MISS_CHAR)
1206 THEN
1207
1208 -- need to create a new payment transaction extension as the old one has been settled.
1209 IF l_debug_level > 0 THEN
1210 oe_debug_pub.add( 'OEXUPTXB.pls: Before calling Create_New_Payment_Trxn');
1211 oe_debug_pub.add( 'p_trxn_extension --->'||p_trxn_extension_id);
1212 oe_debug_pub.add( 'p_org_id -----> '||l_org_id);
1213 oe_debug_pub.add( 'p_site_use_id ---> '||p_site_use_id);
1214 oe_debug_pub.add( 'l_trxn_extension_id --> '||l_trxn_extension_id);
1215 END IF;
1216
1217 OE_Verify_Payment_PUB.Create_New_Payment_Trxn (p_trxn_extension_id => p_trxn_extension_id,
1218 p_org_id => l_org_id,
1219 p_site_use_id => p_site_use_id,
1220 p_instrument_security_code => l_trxn_attribs.Instrument_Security_Code,
1221 x_trxn_extension_id => l_trxn_extension_id,
1222 x_msg_count => x_msg_count,
1223 x_msg_data => x_msg_data,
1224 x_return_status => x_return_status);
1225
1226 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1227 IF l_debug_level > 0 THEN
1228 oe_debug_pub.add( 'OEXUPTXB.pls: Exp. error in call to Create_New_Payment_Trxn');
1229 oe_debug_pub.add( 'SQL ERRM ----> '||sqlerrm);
1230 END IF;
1231 RAISE FND_API.G_EXC_ERROR;
1232 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1233 IF l_debug_level > 0 THEN
1234 oe_debug_pub.add( 'OEXUPTXB.pls: Unexp. error in call to Create_New_Payment_Trxn');
1235 oe_debug_pub.add( 'SQL ERRM ----> '||sqlerrm);
1236 END IF;
1237 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1238 END IF;
1239
1240 IF l_debug_level > 0 THEN
1241 oe_debug_pub.add( 'OEXUPTXB.pls: After successful call to Create_New_Payment_Trxn');
1242 oe_debug_pub.add( 'New trxn extension --->'||l_trxn_extension_id);
1243 END IF;
1244
1245 -- update oe_payments table
1246 p_trxn_extension_id := l_trxn_extension_id;
1247 END IF; --Security code use ='REQUIRED'
1248 --bug 5299050
1249 ELSIF NOT OE_GLOBALS.Is_Same_Credit_Card(l_old_card_number,
1250 p_card_number,l_old_instrument_id,l_instrument_id) THEN
1251
1252 l_trxn_attribs.Originating_Application_Id := 660;
1253 l_trxn_attribs.Instrument_Security_Code := p_instrument_security_code;
1254
1255 -- bug 5575513
1256 -- per IBY, The combination of order_id, trxn ref1 and
1257 -- trxn ref2 must be different for each trxn extension
1258 -- as they produce the order id used to distinguish
1259 -- payment operations. Since this is going to be a
1260 -- different trxn_extension_id for the same order, we will
1261 -- need to make sure the trxn ref2 is different, as the
1262 -- order id and trxn ref1 would be the same.
1263
1264 oe_debug_pub.add('Linda -- p_trxn_extension_id is: '||p_trxn_extension_id);
1265
1266 BEGIN
1267 select trxn_ref_number2
1268 into l_trxn_ref_number2
1269 from iby_trxn_extensions_v
1270 where trxn_extension_id = p_trxn_extension_id;
1271
1272 EXCEPTION WHEN NO_DATA_FOUND THEN
1273 null;
1274 END;
1275
1276 l_pos := instr(l_trxn_ref_number2,'R');
1277
1278
1279 IF l_pos > 0 THEN
1280 l_retry_num := substr(l_trxn_ref_number2, l_pos+1, length(l_trxn_ref_number2)) + 1;
1281 l_trxn_attribs.trxn_ref_number2 := substr(l_trxn_ref_number2, 1, l_pos)||to_char(l_retry_num);
1282 ELSE
1283 l_retry_num := 1;
1284 l_trxn_attribs.trxn_ref_number2 := l_trxn_ref_number2||'R'||to_char(l_retry_num);
1285 END IF;
1286 -- end of bug 5575513
1287
1288 IF l_debug_level > 0 THEN
1289 oe_debug_pub.add('Before calling create_transaction extension');
1290 oe_debug_pub.add('payment channel -->'||l_pmt_channel_code);
1291 oe_debug_pub.add('Assignment id ---->'|| l_assign_id);
1292 oe_debug_pub.add('trxn attributes record type values');
1293 oe_debug_pub.add('l_trxn_attribs.Instrument_Security_Code --->'||p_instrument_security_code);
1294 oe_debug_pub.add('l_trxn_attribs.Originating application id ---> '||l_trxn_attribs.Originating_application_id);
1295 oe_debug_pub.add('l_trxn_attribs.order_id ----> '||l_trxn_attribs.order_id);
1296 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 --->'||l_trxn_attribs.trxn_ref_number1);
1297 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 --->'||l_trxn_attribs.trxn_ref_number2);
1298 END IF;
1299
1300 IBY_Fndcpt_Trxn_Pub.Create_Transaction_Extension
1301 (p_api_version => 1.0,
1302 p_init_msg_list => FND_API.G_TRUE,
1303 p_commit => FND_API.G_FALSE,
1304 X_return_status => l_return_status,
1305 X_msg_count => l_msg_count,
1306 X_msg_data => l_msg_data,
1307 P_payer => l_payer,
1308 P_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1309 P_pmt_channel => l_pmt_channel_code,
1310 P_instr_assignment => l_assign_id,
1311 P_trxn_attribs => l_trxn_attribs,
1312 x_entity_id => l_trxn_extension_id,
1313 X_response => l_response_code);
1314
1315 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
1316
1317 IF l_debug_level > 0 THEN
1318 oe_debug_pub.add('After calling Create_Transaction_Extension'||l_trxn_extension_id);
1319 oe_debug_pub.add('Result code'||l_Response_code.result_code);
1320 oe_debug_pub.add('Return status'||l_Return_Status);
1321 END IF;
1322
1323 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1324 IF l_debug_level > 0 THEN
1325 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
1326 END IF;
1327 RAISE FND_API.G_EXC_ERROR;
1328 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1329 IF l_debug_level > 0 THEN
1330 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
1331 END IF;
1332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1333 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1334 --Setting the trxn extension id to the new value
1335 --as the old trxn extension id was deleted
1336 p_trxn_extension_id := l_trxn_extension_id ;
1337 IF l_debug_level > 0 THEN
1338 oe_debug_pub.add('Create_Transaction_Extension assignment Successful....');
1339 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
1340 oe_debug_pub.add('After call to create Transaction Extension');
1341 oe_debug_pub.add('New trxn extension id'||l_trxn_extension_id);
1342 oe_debug_pub.add('Return status'||l_return_status);
1343 END IF;
1344 END IF;
1345 --bug 5299050
1346 END IF; -- Settled or Expired
1347
1348 --If approval code is null, then this transaction has not yet been authorized
1349 --So can call update transaction API to update the required details for this
1350 --trxn extension id.
1351 ELSE
1352 --Bug 7460481 starts
1353 IF p_payment_type_code = 'CREDIT_CARD'
1354 THEN
1355 IF p_credit_card_approval_code IS NOT NULL THEN
1356 L_trxn_attribs.VoiceAuth_flag := 'Y';
1357 L_trxn_attribs.VoiceAuth_code := p_credit_card_approval_code;
1358 L_trxn_attribs.VoiceAuth_date := p_credit_card_approval_date;
1359 END IF;
1360 END IF;
1361
1362 IF l_debug_level > 0 THEN
1363 oe_debug_pub.add('l_trxn_attribs.VoiceAuth_date ---->'||l_trxn_attribs.VoiceAuth_date);
1364 oe_debug_pub.add('l_trxn_attribs.VoiceAuth_code ---->'||l_trxn_attribs.VoiceAuth_code);
1365 END IF;
1366 --Bug 7460481 ends
1367
1368 IBY_Fndcpt_Trxn_Pub.Update_Transaction_Extension
1369 (p_api_version => 1.0,
1370 p_init_msg_list => FND_API.G_TRUE,
1371 p_commit => FND_API.G_FALSE,
1372 X_return_status => l_return_status,
1373 X_msg_count => l_msg_count,
1374 X_msg_data => l_msg_data,
1375 P_payer => l_payer,
1376 p_entity_id => p_trxn_extension_id,
1377 P_trxn_attribs => l_trxn_attribs,
1378 x_response => l_response_code,
1379 p_pmt_channel => L_pmt_channel_code,
1380 p_instr_assignment => l_assign_id);
1381
1382 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
1383
1384 IF l_debug_level > 0 THEN
1385 oe_debug_pub.add('After calling update transaction extension...');
1386 END IF;
1387
1388 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1389 IF l_debug_level > 0 THEN
1390 oe_debug_pub.add('Result error code in Update_Transaction_Extension'||l_response_code.result_code);
1391 END IF;
1392 RAISE FND_API.G_EXC_ERROR;
1393 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1394 IF l_debug_level > 0 THEN
1395 oe_debug_pub.add('Result error code in Update_Transaction_Extension'||l_response_code.result_code);
1396 END IF;
1397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1398 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1399 IF l_debug_level > 0 THEN
1400 oe_debug_pub.add('Update_Transaction_Extension assignment Successful....');
1401 oe_debug_pub.add('After calling Update_Transaction_Extension');
1402 END IF;
1403 END IF;
1404 END IF; -- Approval code not null
1405 --bug 5028932
1406 --bug 4885313
1407 ELSIF OE_Payment_Trxn_Util.g_old_bill_to_site IS NOT NULL THEN
1408 IF l_debug_level > 0 THEN
1409 oe_debug_pub.add('Bill to has changed....Need to delete this trxn id as context has changed!');
1410 oe_debug_pub.add('Before calling Delete Transaction Extension API...');
1411 oe_debug_pub.add('Trxn extension id --------> '||p_trxn_extension_id);
1412 oe_debug_pub.add('Payer equivalency --------> '||IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_IMMEDIATE);
1413 END IF;
1414 --Setting the context corresponding to the old bill to
1415 --site as the trxn extension id was created for that site.
1416 l_payer.account_site_id := OE_Payment_Trxn_Util.g_old_bill_to_site;
1417
1418 --Resetting the bill to change flag so as to
1419 --maintain the consistent behaviour in different sessions
1420
1421 OE_Payment_Trxn_Util.g_old_bill_to_site := null;
1422
1423 IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension
1424 (
1425 p_api_version => 1.0,
1426 X_return_status => l_return_status,
1427 X_msg_count => l_msg_count,
1428 X_msg_data => l_msg_data,
1429 p_commit => FND_API.G_FALSE,
1430 P_payer => l_payer,
1431 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_IMMEDIATE,
1432 X_response => l_response_code,
1433 p_entity_id => p_trxn_extension_id);
1434
1435 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1436 IF l_debug_level > 0 THEN
1437 oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
1438 oe_debug_pub.add('sql error'||sqlerrm);
1439 oe_debug_pub.add('msg data'||l_msg_data);
1440 END IF;
1441 RAISE FND_API.G_EXC_ERROR;
1442 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1443 IF l_debug_level > 0 THEN
1444 oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
1445 oe_debug_pub.add('sql error'||sqlerrm);
1446 oe_debug_pub.add('msg data'||l_msg_data);
1447 END IF;
1448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1449 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1450 IF l_debug_level > 0 THEN
1451 oe_debug_pub.add('Delete_Transaction_Extension Successful....');
1452 oe_debug_pub.add('After calling Delete_Transaction_Extension');
1453 oe_debug_pub.add('sql error'||sqlerrm);
1454 oe_debug_pub.add('msg data'||l_msg_data);
1455 END IF;
1456 END IF;
1457
1458 IF p_payment_type_code IN ('ACH', 'DIRECT_DEBIT') THEN
1459 l_trxn_attribs.Originating_Application_Id := 660;
1460 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
1461 l_trxn_attribs.Originating_Application_Id := 660;
1462 l_trxn_attribs.Instrument_Security_Code := p_instrument_security_code;
1463 /*IF p_instrument_security_code IS NOT NULL AND
1464 NOT OE_GLOBALS.Equal(p_instrument_security_code,FND_API.G_MISS_CHAR) THEN
1465 --The bill to site has changed and a new credit card
1466 --has been brought in. Since the CVV2 value used here
1467 --would be of the previous cards', displaying this message
1468 --to the user.
1469 FND_MESSAGE.SET_NAME('ONT','OE_CC_SECURITY_CODE_REQD');
1470 OE_Msg_Pub.Add;
1471 END IF;*/
1472 END IF;
1473 --Now setting the account site id as the new bill to site
1474 --for creating this trxn extension id
1475 l_payer.account_site_id := p_site_use_id;
1476 IF l_debug_level > 0 THEN
1477 oe_debug_pub.add('Before calling create_transaction extension');
1478 oe_debug_pub.add('payment channel -->'||l_pmt_channel_code);
1479 oe_debug_pub.add('Assignment id ---->'|| l_assign_id);
1480 oe_debug_pub.add('trxn attributes record type values');
1481 oe_debug_pub.add('l_trxn_attribs.Instrument_Security_Code --->'||p_instrument_security_code);
1482 oe_debug_pub.add('l_trxn_attribs.Originating application id ---> '||l_trxn_attribs.Originating_application_id);
1483 oe_debug_pub.add('l_trxn_attribs.order_id ----> '||l_trxn_attribs.order_id);
1484 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 --->'||l_trxn_attribs.trxn_ref_number1);
1485 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 --->'||l_trxn_attribs.trxn_ref_number2);
1486 END IF;
1487
1488 IBY_Fndcpt_Trxn_Pub.Create_Transaction_Extension
1489 (p_api_version => 1.0,
1490 p_init_msg_list => FND_API.G_TRUE,
1491 p_commit => FND_API.G_FALSE,
1492 X_return_status => l_return_status,
1493 X_msg_count => l_msg_count,
1494 X_msg_data => l_msg_data,
1495 P_payer => l_payer,
1496 P_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1497 P_pmt_channel => l_pmt_channel_code,
1498 P_instr_assignment => l_assign_id,
1499 P_trxn_attribs => l_trxn_attribs,
1500 x_entity_id => l_trxn_extension_id,
1501 X_response => l_response_code);
1502
1503 --oe_msg_pub.add_text(p_message_text => l_response_code.result_message);
1504
1505 IF l_debug_level > 0 THEN
1506 oe_debug_pub.add('After calling Create_Transaction_Extension'||l_trxn_extension_id);
1507 oe_debug_pub.add('Result code'||l_Response_code.result_code);
1508 oe_debug_pub.add('Return status'||l_Return_Status);
1509 END IF;
1510
1511 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1512 IF l_debug_level > 0 THEN
1513 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
1514 END IF;
1515 RAISE FND_API.G_EXC_ERROR;
1516 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1517 IF l_debug_level > 0 THEN
1518 oe_debug_pub.add('Result error code in Create_Transaction_Extension'||l_response_code.result_code);
1519 END IF;
1520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1521 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1522 --Setting the trxn extension id to the new value
1523 --as the old trxn extension id was deleted
1524 p_trxn_extension_id := l_trxn_extension_id ;
1525 IF l_debug_level > 0 THEN
1526 oe_debug_pub.add('Create_Transaction_Extension assignment Successful....');
1527 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
1528 oe_debug_pub.add('After call to create Transaction Extension');
1529 oe_debug_pub.add('New trxn extension id'||l_trxn_extension_id);
1530 oe_debug_pub.add('Return status'||l_return_status);
1531 END IF;
1532 END IF;
1533
1534 END IF; -- old bill to site
1535 --bug 4885313
1536 IF l_debug_level > 0 THEN
1537 oe_debug_pub.add('Exiting Update_Payment_Trxn.....');
1538 END IF;
1539
1540 X_return_status := FND_API.G_RET_STS_SUCCESS;
1541
1542 EXCEPTION
1543
1544 WHEN FND_API.G_EXC_ERROR THEN
1545 X_return_status := FND_API.G_RET_STS_ERROR;
1546 OE_MSG_PUB.Count_And_Get
1547 ( p_count => l_msg_count,
1548 p_data => l_msg_data
1549 );
1550 RAISE FND_API.G_EXC_ERROR;
1551
1552 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1553 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1554 OE_MSG_PUB.Count_And_Get
1555 ( p_count => l_msg_count,
1556 p_data => l_msg_data
1557 );
1558 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1559
1560 WHEN OTHERS THEN
1561 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1562 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1563 THEN
1564 FND_MSG_PUB.Add_Exc_Msg
1565 ( G_PKG_NAME
1566 , 'OE_PAYMENT_TRXN_UTIL'
1567 );
1568 END IF;
1569
1570 OE_MSG_PUB.Count_And_Get
1571 ( p_count => l_msg_count,
1572 p_data => l_msg_data
1573 );
1574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1575
1576 END Update_Payment_Trxn;
1577
1578 Procedure Copy_Payment_Trxn( p_header_id IN NUMBER,
1579 P_line_id IN NUMBER,
1580 p_cust_id IN NUMBER,
1581 P_site_use_id IN NUMBER,
1582 p_trxn_extension_id IN NUMBER,
1583 x_trxn_extension_id OUT NOCOPY NUMBER,
1584 X_return_status OUT NOCOPY VARCHAR2,
1585 X_msg_count OUT NOCOPY NUMBER,
1586 X_msg_data OUT NOCOPY VARCHAR2)
1587 IS
1588 --R12 CC Encryption
1589 L_return_status VARCHAR2(30);
1590 L_msg_count NUMBER;
1591 L_msg_data VARCHAR2(2000);
1592 L_party_id NUMBER;
1593 L_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
1594 L_response_code IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1595 L_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
1596 l_org_id NUMBER;
1597 l_org_type VARCHAR2(80) := 'OPERATING_UNIT';
1598 p_entities IBY_FNDCPT_COMMON_PUB.Id_tbl_type;
1599 l_invoice_to_org_id OE_ORDER_LINES_ALL.invoice_to_org_id%TYPE;
1600 l_cust_account_id NUMBER; --New
1601 l_err_message VARCHAR2(2000);
1602 l_assignment_attribs IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
1603 l_instr_assignment_id IBY_FNDCPT_PAYER_ASSGN_INSTR_V.instr_assignment_id%TYPE;
1604 l_instrument_id IBY_FNDCPT_PAYER_ALL_INSTRS_V.instrument_id%TYPE;
1605 l_instrument_type IBY_FNDCPT_PMT_CHNNLS_VL.instrument_type%TYPE;
1606 l_instrument IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type;
1607 l_exists_assignment VARCHAR2(1) := 'N';
1608 l_assign_id IBY_FNDCPT_PAYER_ASSGN_INSTR_V.instr_assignment_id%TYPE;
1609
1610 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1611
1612 BEGIN
1613 --Get party id for the bill to site;??
1614 IF p_line_id is not null then
1615 Select INVOICE_TO_ORG_ID,ORG_ID into l_invoice_to_org_id,l_org_id
1616 from oe_order_lines_all where header_id = p_header_id and line_id = p_line_id;
1617 else
1618 /*
1619 select invoice_to_org_id,ORG_ID into l_invoice_to_org_id,l_org_id
1620 from oe_order_headers_all where header_id = p_header_id;
1621 */
1622
1623 oe_order_cache.load_order_header(p_header_id);
1624 l_invoice_to_org_id := OE_Order_Cache.g_header_rec.invoice_to_org_id;
1625 l_org_id := OE_Order_Cache.g_header_rec.org_id;
1626 end if;
1627 IF l_debug_level > 0 THEN
1628 oe_debug_pub.add('INVOICE_TO_ORG_ID and ORG_ID'||l_invoice_to_org_id||' and '||l_org_id);
1629 END IF;
1630
1631 Begin
1632 Select hca.party_id, acct_site.cust_account_id
1633 Into l_party_id, l_cust_account_id
1634 From HZ_CUST_SITE_USES_ALL SITE,
1635 HZ_CUST_ACCT_SITES ACCT_SITE,
1636 HZ_CUST_ACCOUNTS_ALL HCA
1637 Where SITE.SITE_USE_ID = p_site_use_id
1638 AND SITE.SITE_USE_CODE = 'BILL_TO'
1639 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
1640 AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
1641 AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
1642
1643 Exception
1644 When No_Data_Found THEN
1645 Null;
1646 End;
1647
1648 l_payer.payment_function := 'CUSTOMER_PAYMENT';
1649 l_payer.party_id := l_party_id;
1650 l_payer.org_type := l_org_type;
1651 l_payer.org_id := l_org_id;
1652 l_payer.cust_account_id := l_cust_account_id;
1653 l_payer.account_site_id := p_site_use_id;
1654
1655 Begin
1656 select instrument_id, instrument_type, instr_assignment_id
1657 into l_instrument_id, l_instrument_type, l_instr_assignment_id
1658 from iby_trxn_extensions_v
1659 where trxn_extension_id = p_trxn_extension_id;
1660 Exception When NO_DATA_FOUND THEN
1661 null;
1662 End;
1663
1664 IF l_debug_level > 0 THEN
1665 oe_debug_pub.add('instrument_id is: '||l_instrument_id,1);
1666 END IF;
1667
1668 -- Need to call the Set Payer Instr Assignment API always
1669 --to create a new assignment id at the account level as iStore
1670 --creates the assignment id at the Party or Site level.
1671
1672 --Commenting out this check for that.
1673 /*Begin
1674 Select 'Y'
1675 Into l_exists_assignment
1676 From IBY_FNDCPT_PAYER_ASSGN_INSTR_V
1677 Where party_id = l_party_id
1678 And instr_assignment_id = l_instr_assignment_id
1679 And rownum = 1;
1680 Exception When NO_DATA_FOUND THEN
1681 l_exists_assignment := 'N';
1682 End;
1683
1684 IF l_debug_level > 0 THEN
1685 oe_debug_pub.add('l_exists_assignment is: '||l_exists_assignment,1);
1686 END IF;
1687
1688 IF l_exists_assignment = 'N' THEN*/
1689
1690 -- create a new instrument assignment id for the payer
1691 -- and the instrument
1692
1693 l_instrument.instrument_type := l_instrument_type;
1694 l_instrument.instrument_id := l_instrument_id;
1695 l_assignment_attribs.instrument := l_instrument;
1696
1697 IF l_debug_level > 0 THEN
1698 oe_debug_pub.add('Calling Oracle Payments API to create new assignment.',1);
1699 oe_debug_pub.add('Before call to Set payer instr assignment API...');
1700 oe_debug_pub.add('Assignment attributes passed ');
1701 oe_debug_pub.add('l_instrument.instrument_id ---> '||l_instrument_id);
1702 oe_debug_pub.add('l_instrument.instrument_type -> '||l_instrument_type);
1703 END IF;
1704
1705 IBY_FNDCPT_SETUP_PUB.Set_Payer_Instr_Assignment
1706 (p_api_version => 1.0,
1707 p_init_msg_list => FND_API.G_TRUE,
1708 p_commit => FND_API.G_FALSE,
1709 X_return_status => l_return_status,
1710 X_msg_count => l_msg_count,
1711 X_msg_data => l_msg_data,
1712 P_payer => l_payer,
1713 P_assignment_attribs => l_assignment_attribs,
1714 X_assign_id => l_assign_id,
1715 X_response => l_response_code);
1716
1717 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1718 IF l_debug_level > 0 THEN
1719 oe_debug_pub.add('Result error code in Set_Payer_Instr_Assignment'||l_response_code.result_code);
1720 END IF;
1721 --IF l_response_code.result_code = '
1722 RAISE FND_API.G_EXC_ERROR;
1723 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1724 IF l_debug_level > 0 THEN
1725 oe_debug_pub.add('Result error code in Set_Payer_Instr_Assignment'||l_response_code.result_code);
1726 END IF;
1727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1728 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1729 IF l_debug_level > 0 THEN
1730 oe_debug_pub.add('Result code in Set_Payer_Instr_Assignment'||l_response_code.result_code);
1731 oe_debug_pub.add('new assignment id is: '||l_assign_id,1);
1732 END IF;
1733 END IF;
1734
1735 --Populating the new assignment id to the copy instr
1736 --assign id attribute and then calling copy transaction API.
1737 l_trxn_attribs.copy_instr_assign_id := l_assign_id;
1738 l_trxn_attribs.order_id := p_header_id;
1739
1740 p_entities(1) := p_trxn_extension_id;
1741
1742 IF l_debug_level > 0 THEN
1743 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);
1744 END IF;
1745
1746 --<store the line id in trx_ref_number1 if this is a line level payment>
1747 IF p_line_id IS NOT NULL THEN
1748 l_trxn_attribs.trxn_ref_number1 := p_line_id;
1749 END IF;
1750
1751 l_trxn_attribs.Originating_Application_Id := 660;
1752
1753 --<Copy the transaction in the IBY payment trasaction extenstion table>
1754 IF l_debug_level > 0 THEN
1755 oe_debug_pub.add('Values passed to Copy_transaction_extension');
1756 oe_debug_pub.add('original trxn extension id ---->'||p_entities(1));
1757 oe_debug_pub.add('trxn attributes record type values');
1758 --oe_debug_pub.add('l_trxn_attribs.Instrument_Security_Code --->'||p_instrument_security_code);
1759 oe_debug_pub.add('l_trxn_attribs.Originating application id ---> 660');
1760 oe_debug_pub.add('l_trxn_attribs.order_id ----> '||p_header_id);
1761 oe_debug_pub.add('l_trxn_attribs.trxn_ref_number1 --->'||p_line_id);
1762 --oe_debug_pub.add('l_trxn_attribs.trxn_ref_number2 --->'||p_payment_number);
1763 oe_debug_pub.add('l_trxn_attribs.copy_instr_assign_id ---->'||l_assign_id);
1764 END IF;
1765 IBY_Fndcpt_Trxn_Pub.Copy_Transaction_Extension
1766 (p_api_version => 1.0,
1767 p_init_msg_list => FND_API.G_TRUE,
1768 p_commit => FND_API.G_FALSE,
1769 X_return_status => l_return_status,
1770 X_msg_count => l_msg_count,
1771 X_msg_data => l_msg_data,
1772 P_payer => l_payer,
1773 P_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1774 p_entities => p_entities,
1775 p_trxn_attribs => l_trxn_attribs,
1776 X_entity_id => x_trxn_extension_id,
1777 x_response => l_response_code);
1778
1779 IF l_debug_level > 0 THEN
1780 oe_debug_pub.add('Return status from Copy_Transaction_Extension'||l_return_status);
1781 END IF;
1782
1783 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1784 IF l_debug_level > 0 THEN
1785 oe_debug_pub.add('Result error code in Copy_Transaction_Extension'||l_response_code.result_code);
1786 END IF;
1787 --IF l_response_code.result_code = '
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 Copy_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 IF l_debug_level > 0 THEN
1796 oe_debug_pub.add('Result code in Copy_Transaction_Extension'||l_response_code.result_code);
1797 END IF;
1798 END IF;
1799
1800 IF l_debug_level > 0 THEN
1801 oe_debug_pub.add('Exiting OE_PAYMENT_TRXN_UTIL.Copy_Payment_Trxn.', 1);
1802 END IF;
1803
1804 X_return_status := FND_API.G_RET_STS_SUCCESS;
1805
1806 EXCEPTION
1807
1808 WHEN FND_API.G_EXC_ERROR THEN
1809 l_err_message := SQLERRM;
1810 IF l_debug_level > 0 THEN
1811 oe_debug_pub.add('Copy_Transaction_Extension error....exc');
1812 oe_debug_pub.add('After call to Copy_Transaction_Extension'||l_return_status);
1813 oe_debug_pub.add('Result code'||l_response_code.result_code);
1814 oe_debug_pub.add('Error'||l_err_message);
1815 END IF;
1816
1817 X_return_status := FND_API.G_RET_STS_ERROR;
1818 OE_MSG_PUB.Count_And_Get
1819 ( p_count => l_msg_count,
1820 p_data => l_msg_data
1821 );
1822 RAISE FND_API.G_EXC_ERROR;
1823 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1824 l_err_message := SQLERRM;
1825 IF l_debug_level > 0 THEN
1826 oe_debug_pub.add('Copy_Transaction_Extension error....unxc');
1827 oe_debug_pub.add('After call to Copy_Transaction_Extension'||l_return_status);
1828 oe_debug_pub.add('Result code'||l_response_code.result_code);
1829 oe_debug_pub.add('f Error'||l_err_message);
1830 END IF;
1831
1832 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1833 OE_MSG_PUB.Count_And_Get
1834 ( p_count => l_msg_count,
1835 p_data => l_msg_data
1836 );
1837 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1838
1839 WHEN OTHERS THEN
1840 l_err_message := SQLERRM;
1841 IF l_debug_level > 0 THEN
1842 oe_debug_pub.add('Create_Transaction_Extension assignment error....others');
1843 oe_debug_pub.add('After call to Create_Transaction_Extension'||l_return_status);
1844 oe_debug_pub.add('Result code'||l_response_code.result_code);
1845 oe_debug_pub.add('trx Error'||l_err_message);
1846 END IF;
1847
1848 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1849 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1850 THEN
1851 FND_MSG_PUB.Add_Exc_Msg
1852 ( G_PKG_NAME
1853 , 'OE_PAYMENT_TRXN_UTIL'
1854 );
1855 END IF;
1856
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 --R12 CC Encryption
1863 END Copy_Payment_Trxn;
1864
1865 Procedure Get_Payment_Trxn_Info(p_header_id IN NUMBER,
1866 P_trxn_extension_id IN NUMBER,
1867 P_payment_type_code IN VARCHAR2,
1868 X_credit_card_number OUT NOCOPY VARCHAR2,
1869 X_credit_card_holder_name OUT NOCOPY VARCHAR2,
1870 X_credit_card_expiration_date OUT NOCOPY VARCHAR2,
1871 X_credit_card_code OUT NOCOPY VARCHAR2,
1872 X_credit_card_approval_code OUT NOCOPY VARCHAR2,
1873 X_credit_card_approval_date OUT NOCOPY VARCHAR2,
1874 X_bank_account_number OUT NOCOPY VARCHAR2,
1875 --X_check_number OUT NOCOPY VARCHAR2,
1876 X_instrument_security_code OUT NOCOPY VARCHAR2,
1877 X_instrument_id OUT NOCOPY NUMBER,
1878 X_instrument_assignment_id OUT NOCOPY NUMBER,
1879 X_return_status OUT NOCOPY VARCHAR2,
1880 X_msg_count OUT NOCOPY NUMBER,
1881 X_msg_data OUT NOCOPY VARCHAR2) IS
1882
1883 --R12 CC Encryption
1884 L_trxn_extension_id NUMBER := P_trxn_extension_id;
1885 l_return_status VARCHAR2(30) := NULL ;
1886 l_msg_count NUMBER := 0 ;
1887 l_msg_data VARCHAR2(2000) := NULL ;
1888 l_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
1889 l_party_id NUMBER;
1890
1891 l_auth_result IBY_FNDCPT_TRXN_PUB.AuthResult_rec_type;
1892 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1893 l_cust_account_id NUMBER;
1894 l_acct_site_use_id NUMBER;
1895 l_org_type VARCHAR2(80);
1896 l_payment_function VARCHAR2(80);
1897 l_org_id NUMBER;
1898 l_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
1899 l_err_message VARCHAR2(2000);
1900 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1901 L_authorized VARCHAR2(1);
1902 BEGIN
1903
1904 IF l_debug_level > 0 THEN
1905 oe_debug_pub.add('Entering Get_Payment_Trxn_Info...');
1906 x_return_status := FND_API.G_RET_STS_SUCCESS;
1907 END IF;
1908
1909 --< if l_trxn_extension_id is null, this might be called from sales order header>
1910 IF l_trxn_extension_id IS NULL THEN
1911 SELECT trxn_extension_id
1912 INTO l_trxn_extension_id
1913 FROM oe_payments
1914 WHERE header_id = p_header_id;
1915 IF l_debug_level > 0 THEN
1916 oe_debug_pub.add('Trxn extension id from oe_payments'||l_trxn_extension_id);
1917 END IF;
1918
1919 END IF;
1920
1921 IF l_trxn_extension_id is not null then
1922 IF p_payment_type_code in ('ACH', 'DIRECT_DEBIT') then
1923 Select account_number
1924 Into x_bank_account_number
1925 From iby_trxn_extensions_v
1926 Where trxn_extension_id = l_trxn_extension_id;
1927 ELSIF p_payment_type_code = 'CREDIT_CARD' THEN
1928
1929 Select itev.card_number,
1930 itev.card_holder_name,
1931 itev.card_expirydate,
1932 itev.card_issuer_code,
1933 itev.authorized_flag,
1934 itev.instrument_security_code,
1935 itev.instrument_id,
1936 itev.instr_assignment_id
1937 into
1938 x_credit_card_number,
1939 x_credit_card_holder_name,
1940 x_credit_card_expiration_date,
1941 x_credit_card_code,
1942 l_authorized,
1943 x_instrument_security_code,
1944 x_instrument_id,
1945 x_instrument_assignment_id
1946 FROM
1947 IBY_TRXN_EXTENSIONS_V ITEV
1948 WHERE ITEV.TRXN_EXTENSION_ID = l_trxn_extension_id;
1949
1950 IF l_debug_level > 0 THEN
1951 oe_debug_pub.add('Values retrieved in Get_Payment_Trxn_Info...');
1952 oe_debug_pub.add('x_credit_card_number ----> '||x_credit_card_number);
1953 oe_debug_pub.add('x_credit_card_holder_name ----> '||x_credit_card_holder_name);
1954 oe_debug_pub.add('x_credit_card_expiration_date ---> '||x_credit_card_expiration_date);
1955 oe_debug_pub.add('x_credit_card_code ---> '||x_credit_card_code);
1956 oe_debug_pub.add('l_authorized ---> '||l_authorized);
1957 oe_debug_pub.add('x_instrument_security_code ----> '||x_instrument_security_code);
1958 oe_debug_pub.add('x_instrument_id ---> '||x_instrument_id);
1959 oe_debug_pub.add('x_instrument_assignment_id ----> '||x_instrument_assignment_id);
1960 END IF;
1961
1962 IF l_authorized = 'Y' THEN
1963 IF l_debug_level > 0 THEN
1964 oe_debug_pub.add('Before calling IBY Get_authorization API...');
1965 oe_debug_pub.add('trxn extn id passed to get auth'||l_trxn_extension_id);
1966 END IF;
1967 IBY_Fndcpt_Trxn_Pub.Get_Authorization
1968 (p_api_version => 1.0,
1969 x_return_status => l_return_status,
1970 x_msg_count => l_msg_count,
1971 x_msg_data => l_msg_data,
1972 p_payer => l_payer,
1973 p_trxn_entity_id => l_trxn_extension_id,
1974 x_auth_result => l_auth_result,
1975 x_response => l_response);
1976
1977 IF l_debug_level > 0 THEN
1978 oe_debug_pub.add('Return status from Get_Authorization'||l_return_status);
1979 END IF;
1980
1981 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1982 IF l_debug_level > 0 THEN
1983 oe_debug_pub.add('Result error code in Get_Authorization'||l_response.result_code);
1984 END IF;
1985 --IF l_response_code.result_code = '
1986 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1987 IF l_debug_level > 0 THEN
1988 oe_debug_pub.add('Result error code in Get_Authorization'||l_response.result_code);
1989 END IF;
1990 ELSIF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1991 IF l_debug_level > 0 THEN
1992 oe_debug_pub.add('Success in Get_Authorization'||l_response.result_code);
1993 oe_debug_pub.add('approval code ----> '||l_auth_result.auth_code);
1994 oe_debug_pub.add('x_credit_card_approval_date ---> '||l_auth_result.auth_date);
1995 END IF;
1996 x_credit_card_approval_code := l_auth_result.auth_code;
1997 x_credit_card_approval_date := l_auth_result.auth_date;
1998 END IF;
1999 END IF; --Authorized flag = 'Y'
2000
2001 END IF; --Payment type code = 'Credit_card'
2002
2003 END IF; --trxn extension id not null
2004
2005 X_return_status := FND_API.G_RET_STS_SUCCESS;
2006
2007 IF l_debug_level > 0 THEN
2008 oe_debug_pub.add('Exiting Get_Payment_Trxn_Info....');
2009 END IF;
2010
2011 EXCEPTION
2012
2013 WHEN FND_API.G_EXC_ERROR THEN
2014 l_err_message := SQLERRM;
2015 IF l_debug_level > 0 THEN
2016 oe_debug_pub.add('Get_Payment_Trxn_Info error....exc');
2017 oe_debug_pub.add('After call to Get_Payment_Trxn_Info'||l_return_status);
2018 oe_debug_pub.add('Result code'||l_response.result_code);
2019 oe_debug_pub.add('Error'||l_err_message);
2020 END IF;
2021
2022
2023 OE_MSG_PUB.Count_And_Get
2024 ( p_count => l_msg_count,
2025 p_data => l_msg_data
2026 );
2027
2028 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2029 l_err_message := SQLERRM;
2030 IF l_debug_level > 0 THEN
2031 oe_debug_pub.add('Get_Payment_Trxn_Info error....unxc');
2032 oe_debug_pub.add('After call to Get_Payment_Trxn_Info'||l_return_status);
2033 oe_debug_pub.add('Result code'||l_response.result_code);
2034 oe_debug_pub.add('f Error'||l_err_message);
2035 END IF;
2036
2037
2038 OE_MSG_PUB.Count_And_Get
2039 ( p_count => l_msg_count,
2040 p_data => l_msg_data
2041 );
2042
2043 WHEN OTHERS THEN
2044 l_err_message := SQLERRM;
2045 IF l_debug_level > 0 THEN
2046 oe_debug_pub.add('Get_Payment_Trxn_Info error....others');
2047 oe_debug_pub.add('After call to Get_Payment_Trxn_Info'||l_return_status);
2048 oe_debug_pub.add('Result code'||l_response.result_code);
2049 oe_debug_pub.add('trx Error'||l_err_message);
2050 END IF;
2051
2052
2053 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2054 THEN
2055 FND_MSG_PUB.Add_Exc_Msg
2056 ( G_PKG_NAME
2057 , 'OE_PAYMENT_TRXN_UTIL'
2058 );
2059 END IF;
2060
2061 -- FND_MESSAGE.SET_NAME('ONT','Exception in Get_Payment_Trxn_Info');
2062 -- OE_MSG_PUB.Add;
2063
2064 OE_MSG_PUB.Count_And_Get
2065 ( p_count => l_msg_count,
2066 p_data => l_msg_data
2067 );
2068
2069 END Get_Payment_Trxn_Info;
2070
2071 PROCEDURE Delete_Payment_Trxn
2072 (p_header_id IN NUMBER,
2073 p_line_id IN NUMBER,
2074 p_payment_number IN NUMBER,
2075 x_return_status OUT NOCOPY VARCHAR2,
2076 x_msg_count OUT NOCOPY NUMBER,
2077 x_msg_data OUT NOCOPY VARCHAR2,
2078 p_trxn_extension_id IN NUMBER,
2079 P_site_use_id IN NUMBER
2080 )
2081 IS
2082 L_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
2083 l_org_id NUMBER;
2084 l_org_type VARCHAR2(80) := 'OPERATING_UNIT';
2085 L_return_status VARCHAR2(30);
2086 L_msg_count NUMBER;
2087 L_msg_data VARCHAR2(2000);
2088 L_party_id NUMBER;
2089 L_response_code IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2090 l_cust_account_id NUMBER;
2091 l_err_message VARCHAR2(2000);
2092 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2093 l_site_use_id NUMBER := p_site_use_id;
2094 l_payment_channel_code VARCHAR2(80);
2095 l_invoice_to_org_id NUMBER;
2096 -- bug 5194228
2097 l_settled_flag VARCHAR2(1);
2098 l_authorized_flag VARCHAR2(1);
2099
2100 BEGIN
2101
2102 IF l_debug_level > 0 THEN
2103 oe_debug_pub.add('Entering Delete_Payment_Trxn...');
2104 END IF;
2105
2106 IF p_line_id is not null then
2107 Select ORG_ID into l_org_id
2108 from oe_order_lines_all where line_id = p_line_id and header_id = p_header_id;
2109 else
2110 oe_order_cache.load_order_header(p_header_id);
2111 l_org_id := OE_Order_Cache.g_header_rec.org_id;
2112 end if;
2113 --For ACH and direct debit payment types, the site use id that
2114 --needs to be queried from payment tables is different from the query
2115 --for Credit card payments. Hence to differentiate this, the payment
2116 --channel code is used.
2117 begin
2118 select payment_channel_code,settled_flag,authorized_flag
2119 into l_payment_channel_code,l_settled_flag,l_authorized_flag -- bug 5194228
2120 from iby_trxn_extensions_v where trxn_Extension_id=p_trxn_extension_id;
2121 exception
2122 when others then
2123 oe_debug_pub.add('Trxn extn id not found....');
2124 end ;
2125
2126 --Incase of deleting payments due to bill to site change
2127 --the invoice to org id is passed as null. So querying the
2128 --old invoice to org id from the payments tables in this case.
2129 IF l_site_use_id is null then
2130 IF l_debug_level > 0 THEN
2131 oe_debug_pub.add('Inside l_site_use_id is null....'||l_payment_channel_code);
2132 oe_debug_pub.add('Trxn extension id used to query ----> '||p_trxn_extension_id);
2133 END IF;
2134
2135 BEGIN
2136 IF l_payment_channel_code = 'CREDIT_CARD' THEN
2137 IF l_debug_level > 0 THEN
2138 oe_debug_pub.add('Inside the credit card query for site use id...'||p_trxn_extension_id);
2139 END IF;
2140 select ifpai.acct_site_use_id into l_site_use_id
2141 from iby_fndcpt_payer_assgn_instr_v ifpai,
2142 iby_trxn_extensions_v itev where
2143 ifpai.instr_assignment_id = itev.instr_assignment_id and
2144 itev.trxn_extension_id = p_trxn_extension_id;
2145 ELSE
2146 IF l_debug_level > 0 THEN
2147 oe_debug_pub.add('Inside the ACH and Direct debit query for site use id...'||p_trxn_extension_id);
2148 END IF;
2149 select iepa.acct_site_use_id into l_site_use_id from
2150 iby_external_payers_all iepa, iby_fndcpt_tx_extensions ifte
2151 where iepa.ext_payer_id = ifte.ext_payer_id and
2152 ifte.trxn_extension_id = p_trxn_extension_id;
2153 END IF;
2154 IF l_debug_level > 0 THEN
2155 oe_debug_pub.add('site use id queried ---> '||l_site_use_id);
2156 END IF;
2157 EXCEPTION
2158 WHEN NO_DATA_FOUND THEN
2159 IF l_debug_level > 0 THEN
2160 oe_debug_pub.add('site use id not found..!!'||sqlerrm);
2161 END IF;
2162 END;
2163 END IF;
2164
2165 Select hca.party_id, acct_site.cust_account_id
2166 Into l_party_id, l_cust_account_id
2167 From HZ_CUST_SITE_USES_ALL SITE,
2168 HZ_CUST_ACCT_SITES ACCT_SITE,
2169 HZ_CUST_ACCOUNTS_ALL HCA
2170 Where SITE.SITE_USE_ID = l_site_use_id
2171 AND SITE.SITE_USE_CODE = 'BILL_TO'
2172 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
2173 AND ACCT_SITE.CUST_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
2174 AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
2175
2176
2177 l_payer.payment_function := 'CUSTOMER_PAYMENT';
2178 l_payer.party_id := l_party_id;
2179 l_payer.org_type := l_org_type;
2180 l_payer.org_id := l_org_id;
2181 l_payer.cust_account_id := l_cust_account_id;
2182 --Setting the payer context appropriately
2183 l_payer.account_site_id := l_site_use_id;
2184
2185 IF l_debug_level > 0 THEN
2186 oe_debug_pub.add('Payer context values...');
2187 oe_debug_pub.add('party id'||l_party_id);
2188 oe_debug_pub.add('org id'||l_org_id);
2189 oe_debug_pub.add('cust acct id'||l_cust_account_id);
2190 oe_debug_pub.add('site use id'||l_site_use_id);
2191 END IF;
2192
2193 BEGIN
2194 IF l_debug_level > 0 THEN
2195 oe_debug_pub.add('Before calling Delete Transaction Extension API...');
2196 oe_debug_pub.add('Trxn extension id --------> '||p_trxn_extension_id);
2197 oe_debug_pub.add('Payer equivalency --------> '||IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_IMMEDIATE);
2198 END IF;
2199 -- bug 5194228
2200 IF nvl(l_authorized_flag,'N') = 'N' and nvl(l_Settled_flag,'N') = 'N' THEN
2201 IBY_FNDCPT_TRXN_PUB.Delete_Transaction_Extension
2202 (
2203 p_api_version => 1.0,
2204 X_return_status => l_return_status,
2205 X_msg_count => l_msg_count,
2206 X_msg_data => l_msg_data,
2207 p_commit => FND_API.G_FALSE,
2208 P_payer => l_payer,
2209 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_IMMEDIATE,
2210 X_response => l_response_code,
2211 p_entity_id => p_trxn_extension_id);
2212
2213 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2214 IF l_debug_level > 0 THEN
2215 oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
2216 oe_debug_pub.add('sql error'||sqlerrm);
2217 oe_debug_pub.add('msg data'||l_msg_data);
2218 END IF;
2219 RAISE FND_API.G_EXC_ERROR;
2220 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2221 IF l_debug_level > 0 THEN
2222 oe_debug_pub.add('Result error code in Delete_Transaction_Extension'||l_response_code.result_code);
2223 oe_debug_pub.add('sql error'||sqlerrm);
2224 oe_debug_pub.add('msg data'||l_msg_data);
2225 END IF;
2226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2227 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
2228 IF l_debug_level > 0 THEN
2229 oe_debug_pub.add('Delete_Transaction_Extension Successful....');
2230 oe_debug_pub.add('After calling Delete_Transaction_Extension');
2231 oe_debug_pub.add('sql error'||sqlerrm);
2232 oe_debug_pub.add('msg data'||l_msg_data);
2233 END IF;
2234 x_return_status := FND_API.G_RET_STS_SUCCESS;
2235 END IF;
2236 END IF;
2237 -- bug 5194228
2238 END;
2239 EXCEPTION
2240
2241 WHEN FND_API.G_EXC_ERROR THEN
2242 l_err_message := SQLERRM;
2243 IF l_debug_level > 0 THEN
2244 oe_debug_pub.add('Delete_Payment_Trxn error....exc');
2245 oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);
2246 oe_debug_pub.add('Result code'||l_response_code.result_code);
2247 oe_debug_pub.add('Error'||l_err_message);
2248 END IF;
2249
2250 X_return_status := FND_API.G_RET_STS_ERROR;
2251 OE_MSG_PUB.Count_And_Get
2252 ( p_count => l_msg_count,
2253 p_data => l_msg_data
2254 );
2255 RAISE FND_API.G_EXC_ERROR;
2256
2257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2258 l_err_message := SQLERRM;
2259 IF l_debug_level > 0 THEN
2260 oe_debug_pub.add('Delete_Payment_Trxn error....unxc');
2261 oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);
2262 oe_debug_pub.add('Result code'||l_response_code.result_code);
2263 oe_debug_pub.add('f Error'||l_err_message);
2264 END IF;
2265
2266 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2267 OE_MSG_PUB.Count_And_Get
2268 ( p_count => l_msg_count,
2269 p_data => l_msg_data
2270 );
2271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2272
2273 WHEN OTHERS THEN
2274 l_err_message := SQLERRM;
2275 IF l_debug_level > 0 THEN
2276 oe_debug_pub.add('Delete_Payment_Trxn error....others');
2277 oe_debug_pub.add('After call to Delete_Payment_Trxn'||l_return_status);
2278 oe_debug_pub.add('Result code'||l_response_code.result_code);
2279 oe_debug_pub.add('trx Error'||l_err_message);
2280 END IF;
2281
2282 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2283 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2284 THEN
2285 FND_MSG_PUB.Add_Exc_Msg
2286 ( G_PKG_NAME
2287 , 'OE_PAYMENT_TRXN_UTIL'
2288 );
2289 END IF;
2290
2291 -- FND_MESSAGE.SET_NAME('ONT','Exception in Delete_Payment_Trxn'||sqlerrm);
2292 -- OE_MSG_PUB.Add;
2293
2294 OE_MSG_PUB.Count_And_Get
2295 ( p_count => l_msg_count,
2296 p_data => l_msg_data
2297 );
2298 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2299 END Delete_Payment_Trxn;
2300
2301 FUNCTION Get_Settled_Flag(p_Trxn_Extension_Id Number)
2302 RETURN VARCHAR2
2303 IS
2304 l_settled_flag VARCHAR2(1) := 'N';
2305 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2306 BEGIN
2307 IF l_debug_level > 0 THEN
2308 oe_debug_pub.add('Entering Get_Settled_Flag Function....');
2309 oe_debug_pub.add('Trxn extension id ---> '||p_Trxn_Extension_id);
2310 END IF;
2311
2312 BEGIN
2313 IF p_trxn_extension_id IS NOT NULL AND
2314 NOT OE_GLOBALS.Equal(p_trxn_Extension_id, FND_API.G_MISS_NUM) THEN
2315 select settled_flag
2316 into l_Settled_flag
2317 from iby_trxn_extensions_v
2318 where trxn_Extension_id = p_trxn_extension_id;
2319 END IF;
2320 IF l_debug_level > 0 THEN
2321 oe_debug_pub.add('Settled flag retrieved successfully...');
2322 oe_debug_pub.add('Value of settled flag ----> '||l_settled_flag);
2323 END IF;
2324 EXCEPTION
2325 WHEN NO_DATA_FOUND THEN
2326 l_settled_flag := 'N';
2327 IF l_debug_level > 0 THEN
2328 oe_debug_pub.add('The transaction extension not found in IBY Table...');
2329 END IF;
2330 WHEN OTHERS THEN
2331 NULL;
2332 END;
2333
2334 RETURN l_settled_flag;
2335 EXCEPTION
2336 WHEN OTHERS THEN
2337 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2338 THEN
2339 OE_MSG_PUB.Add_Exc_Msg
2340 ( G_PKG_NAME ,
2341 'Get_CC_Security_Code_Use'
2342 );
2343 END IF;
2344
2345 END Get_Settled_Flag;
2346
2347 FUNCTION Get_CC_Security_Code_Use
2348 RETURN VARCHAR2
2349 IS
2350 l_return_status VARCHAR2(30) := NULL ;
2351 l_msg_count NUMBER := 0 ;
2352 l_msg_data VARCHAR2(2000) := NULL ;
2353 L_response_code IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2354 l_channel_attrib_uses IBY_FNDCPT_SETUP_PUB.PmtChannel_AttribUses_rec_type;
2355
2356 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2357
2358 BEGIN
2359 IF l_debug_level > 0 THEN
2360 oe_debug_pub.add('Before calling Get_Payment_Channel_Attribs API...');
2361 oe_debug_pub.add('Payment channel ---> CREDIT_CARD');
2362 END IF;
2363
2364 IBY_FNDCPT_SETUP_PUB.Get_Payment_Channel_Attribs
2365 (p_api_version => 1.0,
2366 X_return_status => l_return_status,
2367 X_msg_count => l_msg_count,
2368 X_msg_data => l_msg_data,
2369 P_channel_code => 'CREDIT_CARD',
2370 X_channel_attrib_uses => l_channel_attrib_uses,
2371 X_response => l_response_code);
2372
2373 G_CC_Security_Code_Use := l_channel_attrib_uses.Instr_SecCode_Use;
2374 --G_CC_Security_Code_Use := 'OPTIONAL';
2375
2376 IF l_debug_level > 0 then
2377 oe_debug_pub.add('Return Status --> '||l_return_status);
2378 oe_debug_pub.add('Response code --> '||l_response_code.result_code);
2379 oe_debug_pub.add('Security code use ---> '||G_CC_Security_Code_Use);
2380 oe_debug_pub.add('Statement Billing Address Use ----> '|| l_channel_attrib_uses.Instr_Billing_Address);
2381 oe_debug_pub.add('Exiting OE_PAYMENT_TRXN_UTIL.Get_CC_Security_Code_Use: '||l_return_status, 1);
2382 END IF;
2383
2384 RETURN G_CC_Security_Code_Use;
2385
2386 EXCEPTION
2387
2388 WHEN OTHERS THEN
2389
2390 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2391 THEN
2392 OE_MSG_PUB.Add_Exc_Msg
2393 ( G_PKG_NAME ,
2394 'Get_CC_Security_Code_Use'
2395 );
2396 END IF;
2397
2398 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2399
2400 END Get_CC_Security_Code_Use;
2401
2402
2403 --R12 CC Encryption
2404 END OE_PAYMENT_TRXN_UTIL;