[Home] [Help]
PACKAGE BODY: APPS.IBY_FNDCPT_TRXN_PUB
Source
1 PACKAGE BODY IBY_FNDCPT_TRXN_PUB AS
2 /*$Header: ibyfctxb.pls 120.57.12010000.17 2009/01/21 08:40:23 sgogula ship $*/
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_FNDCPT_TRXN_PUB';
6
7 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_FNDCPT_TRXN_PUB';
8
9 --
10 -- Constants
11 --
12 CC_CAPTURE_TRXNTYPE CONSTANT NUMBER(15,0) := 8;
13 BA_CAPTURE_TRXNTYPE CONSTANT NUMBER(15,0) := 100;
14 CC_PMTCHANNEL_CODE CONSTANT VARCHAR2(30) := 'CREDIT_CARD';
15 DC_PMTCHANNEL_CODE CONSTANT VARCHAR2(30) := 'PINLESS_DEBIT_CARD';
16 BA_PMT_CHANNEL_CODE CONSTANT VARCHAR2(30) := 'BANK_ACCT_XFER';
17 CC_INSTR_TYPE CONSTANT VARCHAR2(30) :='CREDITCARD';
18 DC_INSTR_TYPE CONSTANT VARCHAR2(30) :='DEBITCARD';
19 BA_INSTR_TYPE CONSTANT VARCHAR2(30) :='BANKACCOUNT';
20 GATEWAY_SETTLEMENT_STATUS CONSTANT VARCHAR2(30) := '11';
21 PROCESSOR_SETTLEMENT_STATUS CONSTANT VARCHAR2(30) := '100';
22
23 -------------------------------------------------------------------------
24 -- II. Record Types
25 -------------------------------------------------------------------------
26
27
28 TYPE FuncCaptureOrder_rec_type IS RECORD (
29 TRXNMID IBY_TRXN_SUMMARIES_ALL.TRXNMID%TYPE,
30 -- From IBY_FNDCPT_TX_EXTENSIONS
31 TRXN_REF_NUMBER1 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER1%TYPE,
32 TRXN_REF_NUMBER2 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER2%TYPE,
33 PAYMENT_CHANNEL_CODE IBY_FNDCPT_TX_EXTENSIONS.PAYMENT_CHANNEL_CODE%TYPE,
34 PO_NUMBER IBY_FNDCPT_TX_EXTENSIONS.PO_NUMBER%TYPE,
35 VOICE_AUTHORIZATION_FLAG IBY_FNDCPT_TX_EXTENSIONS.VOICE_AUTHORIZATION_FLAG%TYPE,
36 PAYMENT_FUNCTION IBY_EXTERNAL_PAYERS_ALL.PAYMENT_FUNCTION%TYPE,
37 -- From the ORDERS View
38 CALL_APP_SERVICE_REQ_CODE IBY_TRXN_SUMMARIES_ALL.CALL_APP_SERVICE_REQ_CODE%TYPE,
39 --CALLING_APP_DOC_REF_NUMBER IBY_TRXN_SUMMARIES_ALL.CALLING_APP_DOC_REF_NUMBER%TYPE,
40 SETTLEMENT_DATE IBY_TRXN_SUMMARIES_ALL.SETTLEDATE%TYPE,
41 SETTLEMENT_DUE_DATE IBY_TRXN_SUMMARIES_ALL.SETTLEMENT_DUE_DATE%TYPE,
42 BR_MATURITY_DATE IBY_TRXN_SUMMARIES_ALL.BR_MATURITY_DATE%TYPE,
43 ORDER_DESCRIPTION VARCHAR2(255),
44 SETTLEMENT_CURRENCY_CODE IBY_TRXN_SUMMARIES_ALL.CURRENCYNAMECODE%TYPE,
45 SETTLEMENT_AMOUNT IBY_TRXN_SUMMARIES_ALL.AMOUNT%TYPE,
46 PAYMENT_TRXN_EXTENSION_ID IBY_FNDCPT_TX_EXTENSIONS.TRXN_EXTENSION_ID%TYPE,
47 INTERNAL_BANK_ACCOUNT_ID CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE,
48 PAYER_PARTY_ID IBY_EXTERNAL_PAYERS_ALL.PARTY_ID%TYPE,
49 CUST_ACCOUNT_ID IBY_TRXN_SUMMARIES_ALL.CUST_ACCOUNT_ID%TYPE,
50 ACCT_SITE_USE_ID IBY_TRXN_SUMMARIES_ALL.ACCT_SITE_USE_ID%TYPE,
51 ORG_ID IBY_TRXN_SUMMARIES_ALL.ORG_ID%TYPE,
52 ORG_TYPE IBY_TRXN_SUMMARIES_ALL.ORG_TYPE%TYPE,
53 LEGAL_ENTITY_ID IBY_TRXN_SUMMARIES_ALL.LEGAL_ENTITY_ID%TYPE,
54 BILL_TO_ADDRESS_ID IBY_TRXN_SUMMARIES_ALL.BILL_TO_ADDRESS_ID%TYPE,
55 SETTLEMENT_CUSTOMER_REFERENCE IBY_TRXN_SUMMARIES_ALL.SETTLEMENT_CUSTOMER_REFERENCE%TYPE,
56 AR_RECEIPT_METHOD_ID IBY_TRXN_SUMMARIES_ALL.AR_RECEIPT_METHOD_ID%TYPE,
57 BR_DRAWEE_ISSUED_FLAG IBY_TRXN_SUMMARIES_ALL.BR_DRAWEE_ISSUED_FLAG%TYPE,
58 BR_SIGNED_FLAG IBY_TRXN_SUMMARIES_ALL.BR_SIGNED_FLAG%TYPE,
59 BR_DISPUTED_FLAG VARCHAR2(1),
60 ATTRIBUTE_CATEGORY VARCHAR2(150),
61 ATTRIBUTE1 VARCHAR2(150),
62 ATTRIBUTE2 VARCHAR2(150),
63 ATTRIBUTE3 VARCHAR2(150),
64 ATTRIBUTE4 VARCHAR2(150),
65 ATTRIBUTE5 VARCHAR2(150),
66 ATTRIBUTE6 VARCHAR2(150),
67 ATTRIBUTE7 VARCHAR2(150),
68 ATTRIBUTE8 VARCHAR2(150),
69 ATTRIBUTE9 VARCHAR2(150),
70 ATTRIBUTE10 VARCHAR2(150),
71 ATTRIBUTE11 VARCHAR2(150),
72 ATTRIBUTE12 VARCHAR2(150),
73 ATTRIBUTE13 VARCHAR2(150),
74 ATTRIBUTE14 VARCHAR2(150),
75 ATTRIBUTE15 VARCHAR2(150),
76 CREATED_BY IBY_TRXN_SUMMARIES_ALL.CREATED_BY%TYPE,
77 CREATION_DATE IBY_TRXN_SUMMARIES_ALL.CREATION_DATE%TYPE,
78 LAST_UPDATED_BY IBY_TRXN_SUMMARIES_ALL.LAST_UPDATED_BY%TYPE,
79 LAST_UPDATE_DATE IBY_TRXN_SUMMARIES_ALL.LAST_UPDATE_DATE%TYPE,
80 LAST_UPDATE_LOGIN IBY_TRXN_SUMMARIES_ALL.LAST_UPDATE_LOGIN%TYPE,
81 OBJECT_VERSION_NUMBER IBY_TRXN_SUMMARIES_ALL.OBJECT_VERSION_NUMBER%TYPE,
82 -- From auth table
83 auth_transactionid IBY_TRXN_SUMMARIES_ALL.transactionid%TYPE,
84 auth_sales_rep_party_id IBY_TRXN_SUMMARIES_ALL.sales_rep_party_id%TYPE,
85 auth_tangibleid IBY_TRXN_SUMMARIES_ALL.tangibleid%TYPE,
86 auth_bepkey IBY_TRXN_SUMMARIES_ALL.bepkey%TYPE,
87 auth_payeeid IBY_TRXN_SUMMARIES_ALL.payeeid%TYPE,
88 auth_bepid IBY_TRXN_SUMMARIES_ALL.bepid%TYPE,
89 auth_mpayeeid IBY_TRXN_SUMMARIES_ALL.mpayeeid%TYPE,
90 auth_ecappid IBY_TRXN_SUMMARIES_ALL.ecappid%TYPE,
91 auth_paymentmethodname IBY_TRXN_SUMMARIES_ALL.paymentmethodname%TYPE,
92 auth_mtangibleid IBY_TRXN_SUMMARIES_ALL.mtangibleid%TYPE,
93 auth_payeeinstrid IBY_TRXN_SUMMARIES_ALL.payeeinstrid%TYPE,
94 auth_payerid IBY_TRXN_SUMMARIES_ALL.payerid%TYPE,
95 auth_payerinstrid IBY_TRXN_SUMMARIES_ALL.payerinstrid%TYPE,
96 auth_detaillookup IBY_TRXN_SUMMARIES_ALL.detaillookup%TYPE,
97 auth_instrnumber IBY_TRXN_SUMMARIES_ALL.instrnumber%TYPE,
98 auth_instrtype IBY_TRXN_SUMMARIES_ALL.instrtype%TYPE,
99 auth_instrsubtype IBY_TRXN_SUMMARIES_ALL.instrsubtype%TYPE,
100 auth_currencynamecode IBY_TRXN_SUMMARIES_ALL.currencynamecode%TYPE,
101 auth_desturl IBY_TRXN_SUMMARIES_ALL.desturl%TYPE,
102 auth_nlslang IBY_TRXN_SUMMARIES_ALL.nlslang%TYPE,
103 auth_amount IBY_TRXN_SUMMARIES_ALL.amount%TYPE,
104 auth_process_profile_code IBY_TRXN_SUMMARIES_ALL.process_profile_code%TYPE,
105 auth_payment_channel_code IBY_TRXN_SUMMARIES_ALL.payment_channel_code%TYPE,
106 auth_proc_reference_code IBY_TRXN_SUMMARIES_ALL.proc_reference_code%TYPE,
107 auth_proc_reference_amount IBY_TRXN_SUMMARIES_ALL.proc_reference_amount%TYPE,
108 auth_legal_entity_id IBY_TRXN_SUMMARIES_ALL.legal_entity_id%TYPE,
109 auth_settlement_due_date IBY_TRXN_SUMMARIES_ALL.settlement_due_date%TYPE,
110 auth_bill_to_address_id IBY_TRXN_SUMMARIES_ALL.bill_to_address_id%TYPE,
111 auth_br_maturity_date IBY_TRXN_SUMMARIES_ALL.br_maturity_date%TYPE,
112 auth_factored_flag IBY_TRXN_SUMMARIES_ALL.factored_flag%TYPE,
113 -- From Payers table
114 auth_payment_function IBY_EXTERNAL_PAYERS_ALL.PAYMENT_FUNCTION%TYPE,
115 auth_payer_party_id IBY_EXTERNAL_PAYERS_ALL.PARTY_ID%TYPE,
116 auth_cust_account_id IBY_EXTERNAL_PAYERS_ALL.CUST_ACCOUNT_ID%TYPE,
117 auth_acct_site_use_id IBY_EXTERNAL_PAYERS_ALL.ACCT_SITE_USE_ID%TYPE,
118 auth_org_id IBY_EXTERNAL_PAYERS_ALL.ORG_ID%TYPE,
119 auth_org_type IBY_EXTERNAL_PAYERS_ALL.ORG_TYPE%TYPE,
120 bep_type IBY_BEPINFO.bep_type%TYPE,
121 payer_instr_assignment_id IBY_TRXN_SUMMARIES_ALL.payer_instr_assignment_id%TYPE,
122 debit_auth_flag IBY_TRXN_SUMMARIES_ALL.debit_auth_flag%TYPE,
123 debit_auth_method IBY_TRXN_SUMMARIES_ALL.debit_auth_method%TYPE,
124 debit_auth_reference IBY_TRXN_SUMMARIES_ALL.debit_auth_reference%TYPE,
125 capture_transaction_id IBY_TRXN_SUMMARIES_ALL.transactionid%TYPE,
126 int_bank_country VARCHAR2(60),
127 settle_req_vrfy IBY_FNDCPT_SYS_EFT_PF_B.SETTLE_REQUIRE_VRFY_FLAG%TYPE,
128 auth_count PLS_INTEGER,
129 auth_dirdeb_instruction_code IBY_TRXN_SUMMARIES_ALL.dirdeb_instruction_code%TYPE
130 );
131
132 -- II.2 Table Types
133
134 TYPE caFundcaptureOrdersTabType IS TABLE OF FuncCaptureOrder_rec_type
135 INDEX BY BINARY_INTEGER;
136
137 --
138 -- Use: Determines cause of the instrument assignments view returning no
139 -- data
140 FUNCTION Get_Payer_Instr_Assgn_Fail
141 (p_instr_assign_id IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE,
142 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type
143 )
144 RETURN VARCHAR2
145 IS
146 l_msg VARCHAR2(100);
147 l_payer_id iby_pmt_instr_uses_all.ext_pmt_party_id%TYPE;
148 l_party_id iby_external_payers_all.party_id%TYPE;
149
150 l_count NUMBER;
151
152 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Get_Payer_Instr_Assgn_Fail';
153 BEGIN
154
155 iby_debug_pub.add('instr assignment id:=' || p_instr_assign_id,
156 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
157
158 l_msg := 'IBY_INVALID_INSTR_ASSIGN';
159
160 -- Bug: 7719030
161 -- Handling Exceptions in a different way
162 BEGIN
163
164 SELECT ext_pmt_party_id
165 INTO l_payer_id
166 FROM iby_pmt_instr_uses_all
167 WHERE (instrument_payment_use_id = p_instr_assign_id);
168
169 iby_debug_pub.add('payer id:=' || l_payer_id,
170 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
171
172 EXCEPTION
173 WHEN NO_DATA_FOUND THEN
174 iby_debug_pub.add('Exception: No Instrument found',
175 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
176 RETURN l_msg;
177 END;
178
179
180 l_msg := 'IBY_20491';
181 SELECT party_id
182 INTO l_party_id
183 FROM iby_external_payers_all
184 WHERE (ext_payer_id = l_payer_id);
185
186 iby_debug_pub.add('external payer count:=' || SQL%ROWCOUNT,
187 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
188
189 IF (SQL%ROWCOUNT < 1) THEN RETURN l_msg; END IF;
190
191 l_msg := 'IBY_INVALID_PARTY_CONTEXT';
192 iby_debug_pub.add('party id:=' || l_party_id,
193 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
194 IF (l_party_id <> p_payer.Party_Id) THEN RETURN l_msg; END IF;
195
196 RETURN null;
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 RETURN l_msg;
200 END Get_Payer_Instr_Assgn_Fail;
201
202 --
203 -- Use: Determine the reason extension lookup failed in the auth API
204 --
205 FUNCTION Get_Extension_Auth_Fail
206 (p_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
207 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type
208 )
209 RETURN VARCHAR2
210 IS
211 l_msg VARCHAR2(100);
212 l_instr_assign_id iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE;
213 l_ext_payer_id iby_fndcpt_tx_extensions.ext_payer_id%TYPE;
214
215 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Get_Extension_Auth_Fail';
216 BEGIN
217
218 l_msg := 'IBY_INVALID_TXN_EXTENSION';
219 -- Bug: 7719030.
220 --Changing exception handling.
221 BEGIN
222
223 SELECT instr_assignment_id, ext_payer_id
224 INTO l_instr_assign_id, l_ext_payer_id
225 FROM iby_fndcpt_tx_extensions
226 WHERE (p_trxn_extension_id = trxn_extension_id);
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 RETURN l_msg;
230 END;
231
232 iby_debug_pub.add('checking instrument assignment',
233 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
234 l_msg := Get_Payer_Instr_Assgn_Fail(l_instr_assign_id,p_payer);
235
236 IF (NOT l_msg IS NULL) THEN RETURN l_msg; END IF;
237
238 RETURN NULL;
239 EXCEPTION
240 WHEN NO_DATA_FOUND THEN
241 RETURN l_msg;
242 END Get_Extension_Auth_Fail;
243
244 --
245 -- Use: Gets the encryption mode for transaction extensions
246 --
247 FUNCTION Get_Extension_Encrypt_Mode
248 RETURN iby_sys_security_options.instr_sec_code_encryption_mode%TYPE
249 IS
250 l_mode iby_sys_security_options.instr_sec_code_encryption_mode%TYPE;
251 CURSOR c_encrypt_mode
252 IS
253 SELECT instr_sec_code_encryption_mode
254 FROM iby_sys_security_options;
255 BEGIN
256 IF (c_encrypt_mode%ISOPEN) THEN CLOSE c_encrypt_mode; END IF;
257
258 OPEN c_encrypt_mode;
259 FETCH c_encrypt_mode INTO l_mode;
260 CLOSE c_encrypt_mode;
261
262 RETURN l_mode;
263 END Get_Extension_Encrypt_Mode;
264
265 FUNCTION Get_Tangible_Id
266 (p_app_short_name IN fnd_application.application_short_name%TYPE,
267 p_order_id IN iby_fndcpt_tx_extensions.order_id%TYPE,
268 p_trxn_ref1 IN iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE,
269 p_trxn_ref2 IN iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE
270 )
271 RETURN iby_trxn_summaries_all.tangibleid%TYPE
272 IS
273 l_tangible_id iby_trxn_summaries_all.tangibleid%TYPE;
274 BEGIN
275 l_tangible_id := p_app_short_name || '_' || p_order_id;
276
277 IF (NOT p_trxn_ref1 IS NULL) THEN
278 l_tangible_id := l_tangible_id || '_' || p_trxn_ref1;
279 END IF;
280 IF (NOT p_trxn_ref2 IS NULL) THEN
281 l_tangible_id := l_tangible_id || '_' || p_trxn_ref2;
282 END IF;
283
284 RETURN l_tangible_id;
285 END Get_Tangible_Id;
286
287 --Overloading this function for bug : 7628586
288 FUNCTION Get_Tangible_Id
289 (p_app_short_name IN fnd_application.application_short_name%TYPE,
290 p_trxn_extn_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE
291 )
292 RETURN iby_trxn_summaries_all.tangibleid%TYPE
293 IS
294 l_tangible_id iby_trxn_summaries_all.tangibleid%TYPE;
295 BEGIN
296 l_tangible_id := p_app_short_name || '_' || p_trxn_extn_id;
297 RETURN l_tangible_id;
298 END Get_Tangible_Id;
299 --End of Overloaded Function
300
301 --
302 -- Use: Gets the internal payee id from a payee context
303 --
304 FUNCTION Get_Internal_Payee(p_payee IN PayeeContext_rec_type)
305 RETURN iby_payee.payeeid%TYPE
306 IS
307 l_payeeid iby_payee.payeeid%TYPE;
308
309 CURSOR c_payeeid
310 (ci_org_type IN iby_fndcpt_payee_appl.org_type%TYPE,
311 ci_org_id IN iby_fndcpt_payee_appl.org_id%TYPE
312 )
313 IS
314 SELECT p.payeeid
315 FROM iby_payee p, iby_fndcpt_payee_appl a
316 WHERE (p.mpayeeid = a.mpayeeid)
317 AND ((a.org_type = ci_org_type) AND (a.org_id = ci_org_id));
318 BEGIN
319 IF (c_payeeid%ISOPEN) THEN CLOSE c_payeeid; END IF;
320
321 OPEN c_payeeid(p_payee.Org_Type, p_payee.Org_Id);
322 FETCH c_payeeid INTO l_payeeid;
323 IF (c_payeeid%NOTFOUND) THEN
324 l_payeeid := NULL;
325 END IF;
326 CLOSE c_payeeid;
327
328 RETURN l_payeeid;
329 END Get_Internal_Payee;
330
331 FUNCTION Get_Tx_Extension_Copy_Count
332 (p_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
333 RETURN NUMBER
334 IS
335 l_copy_count NUMBER;
336
337 CURSOR c_xe_copies(ci_x_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
338 IS
339 SELECT COUNT(copy_trxn_extension_id)
340 FROM iby_fndcpt_tx_xe_copies
341 WHERE (source_trxn_extension_id = ci_x_id);
342 BEGIN
343
344 IF (c_xe_copies%ISOPEN) THEN CLOSE c_xe_copies; END IF;
345
346 OPEN c_xe_copies(p_trxn_extension_id);
347 FETCH c_xe_copies INTO l_copy_count;
348 CLOSE c_xe_copies;
349
350 RETURN NVL(l_copy_count,0);
351 END Get_Tx_Extension_Copy_Count;
352
353 --
354 -- Use: Validates the transaction extension attributes based upon their
355 -- use by the payment channel
356 --
357 FUNCTION Extension_Valid
358 (p_pmt_channel IN VARCHAR2,
359 p_trxn_attribs IN TrxnExtension_rec_type,
360 p_update IN BOOLEAN,
361 p_consumed_cvv2 IN BOOLEAN
362 )
363 RETURN BOOLEAN
364 IS
365 l_return_status VARCHAR2(1);
366 l_msg_count NUMBER;
367 l_msg_data VARCHAR2(100);
368 l_channel_attribs IBY_FNDCPT_SETUP_PUB.PmtChannel_AttribUses_rec_type;
369 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
370
371 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Extension_Valid';
372 BEGIN
373
374 iby_debug_pub.add('validating xtension',
375 iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
376
377 IBY_FNDCPT_SETUP_PUB.Get_Payment_Channel_Attribs
378 (1.0, FND_API.G_FALSE, l_return_status, l_msg_count, l_msg_data,
379 p_pmt_channel, l_channel_attribs, l_response);
380
381 IF (l_response.Result_Code <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS) THEN
382 RETURN FALSE;
383 END IF;
384
385 IF ( (l_channel_attribs.Instr_SecCode_Use =
386 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_REQUIRED)
387 AND
388 ( ((p_trxn_attribs.Instrument_Security_Code IS NULL)
389 AND (NOT p_update)
390 AND (NOT p_consumed_cvv2)
391 )
392 OR (p_trxn_attribs.Instrument_Security_Code = FND_API.G_MISS_CHAR)
393 )
394 )
395 THEN
396 RETURN FALSE;
397 END IF;
398 IF ( (l_channel_attribs.Instr_SecCode_Use =
399 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_DISABLED)
400 AND NOT ( (p_trxn_attribs.Instrument_Security_Code IS NULL) OR
401 (p_trxn_attribs.Instrument_security_Code = FND_API.G_MISS_CHAR)
402 )
403 )
404 THEN
405 RETURN FALSE;
406 END IF;
407
408 IF ( (l_channel_attribs.Instr_VoiceAuthFlag_Use =
409 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_REQUIRED)
410 AND ( (p_trxn_attribs.VoiceAuth_Flag IS NULL AND (NOT p_update))
411 OR (p_trxn_attribs.VoiceAuth_Flag = FND_API.G_MISS_CHAR) )
412 )
413 THEN
414 RETURN FALSE;
415 END IF;
416 --Fix for bug# 6404912: Voice Auth flag 'N' and NULL treated similarly
417 IF ( (l_channel_attribs.Instr_VoiceAuthFlag_Use =
418 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_DISABLED)
419 AND NOT ( ( nvl(p_trxn_attribs.VoiceAuth_Flag, 'N') = 'N') OR
420 (p_trxn_attribs.VoiceAuth_Flag = FND_API.G_MISS_CHAR)
421 )
422 )
423 THEN
424 RETURN FALSE;
425 END IF;
426
427 IF ( (l_channel_attribs.Instr_VoiceAuthCode_Use =
428 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_REQUIRED)
429 AND ( (p_trxn_attribs.VoiceAuth_Code IS NULL AND (NOT p_update))
430 OR (p_trxn_attribs.VoiceAuth_Code = FND_API.G_MISS_CHAR)
431 )
432 )
433 THEN
434 RETURN FALSE;
435 END IF;
436 IF ( (l_channel_attribs.Instr_VoiceAuthCode_Use =
437 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_DISABLED)
438 AND NOT ( (p_trxn_attribs.VoiceAuth_Code IS NULL) OR
439 (p_trxn_attribs.VoiceAuth_Code = FND_API.G_MISS_CHAR)
440 )
441 )
442 THEN
443 RETURN FALSE;
444 END IF;
445
446 IF ( (l_channel_attribs.Instr_VoiceAuthDate_Use =
447 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_REQUIRED)
448 AND ( (p_trxn_attribs.VoiceAuth_Date IS NULL AND (NOT p_update)) OR
449 (p_trxn_attribs.VoiceAuth_Date = FND_API.G_MISS_DATE) )
450 )
451 THEN
452 RETURN FALSE;
453 END IF;
454 IF ( (l_channel_attribs.Instr_VoiceAuthDate_Use =
455 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_DISABLED)
456 AND NOT ( (p_trxn_attribs.VoiceAuth_Date IS NULL) OR
457 (p_trxn_attribs.VoiceAuth_Date = FND_API.G_MISS_DATE)
458 )
459 )
460 THEN
461 RETURN FALSE;
462 END IF;
463
464 IF ( (l_channel_attribs.PO_Number_Use =
465 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_REQUIRED)
466 AND ( (p_trxn_attribs.PO_Number IS NULL AND (NOT p_update))
467 OR (p_trxn_attribs.PO_Number = FND_API.G_MISS_CHAR) )
468 )
469 THEN
470 RETURN FALSE;
471 END IF;
472 IF ( (l_channel_attribs.PO_Number_Use =
473 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_DISABLED)
474 AND NOT ( (p_trxn_attribs.PO_Number IS NULL) OR
475 (p_trxn_attribs.PO_Number = FND_API.G_MISS_CHAR)
476 )
477 )
478 THEN
479 RETURN FALSE;
480 END IF;
481
482 IF ( (l_channel_attribs.PO_Line_Number_Use =
483 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_REQUIRED)
484 AND ( (p_trxn_attribs.PO_Line_Number IS NULL OR (NOT p_update))
485 OR (p_trxn_attribs.PO_Line_Number = FND_API.G_MISS_CHAR) )
486 )
487 THEN
488 RETURN FALSE;
489 END IF;
490 IF ( (l_channel_attribs.PO_Line_Number_Use =
491 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_DISABLED)
492 AND NOT ( (p_trxn_attribs.PO_Line_Number IS NULL) OR
493 (p_trxn_attribs.PO_Line_Number = FND_API.G_MISS_CHAR)
494 )
495 )
496 THEN
497 RETURN FALSE;
498 END IF;
499
500 IF ( (l_channel_attribs.AddInfo_Use =
501 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_REQUIRED)
502 AND ( (p_trxn_attribs.Additional_Info IS NULL OR (NOT p_update))
503 OR (p_trxn_attribs.Additional_Info = FND_API.G_MISS_CHAR) )
504 )
505 THEN
506 RETURN FALSE;
507 END IF;
508 IF ( (l_channel_attribs.AddInfo_Use =
509 IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_DISABLED)
510 AND NOT ( (p_trxn_attribs.Additional_Info IS NULL) OR
511 (p_trxn_attribs.Additional_Info = FND_API.G_MISS_CHAR)
512 )
513 )
514 THEN
515 RETURN FALSE;
516 END IF;
517
518 IF ( (p_trxn_attribs.Order_Id IS NULL AND (NOT p_update)) OR
519 (p_trxn_attribs.Order_id = FND_API.G_MISS_CHAR)
520 )
521 THEN
522 RETURN FALSE;
523 END IF;
524
525 IF (NOT iby_utility_pvt.Validate_App_Id(p_trxn_attribs.Originating_Application_Id))
526 THEN
527 RETURN FALSE;
528 END IF;
529
530 RETURN TRUE;
531 END Extension_Valid;
532
533 --
534 -- USE: Validates the payment channel for the payer;
535 -- the given payer or an equivalent must be assigned this payment
536 -- channel
537 --
538 FUNCTION Payer_Channel_Valid
539 (p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
540 p_payer_level IN VARCHAR2,
541 p_payer_equiv IN VARCHAR2,
542 p_pmt_channel IN iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE
543 )
544 RETURN BOOLEAN
545 IS
546 l_channel_instr iby_fndcpt_pmt_chnnls_b.instrument_type%TYPE;
547
548 CURSOR c_trxn_channel
549 (ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
550 ci_payer_level IN VARCHAR2,
551 ci_payer_equiv IN VARCHAR2,
552 ci_channel_code IN iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE)
553 IS
554 SELECT c.instrument_type
555 FROM iby_ext_party_pmt_mthds pm, iby_fndcpt_pmt_chnnls_b c
556 WHERE (c.payment_channel_code = ci_channel_code)
557 AND (pm.payment_method_code = c.payment_channel_code)
558 AND (NVL(pm.inactive_date,SYSDATE-10)<SYSDATE)
559 AND (NVL(c.inactive_date,SYSDATE-10)<SYSDATE)
560 AND (pm.payment_flow = IBY_FNDCPT_SETUP_PUB.G_PMT_FLOW_FNDCPT)
561 AND pm.ext_pmt_party_id IN
562 (
563 SELECT ext_payer_id
564 FROM iby_external_payers_all
565 WHERE (payment_function = ci_payer.Payment_Function)
566 AND (party_id = ci_payer.Party_Id)
567 AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
568 (ci_payer.org_type, ci_payer.org_id,
569 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
570 ci_payer_level,ci_payer_equiv,org_type,org_id,
571 cust_account_id,acct_site_use_id) = 'T')
572 );
573 BEGIN
574
575 -- ENHANCEMENT:
576 -- Applicability rules for payer-assigned payment channels
577 -- not currently supported
578
579 RETURN TRUE;
580
581 IF (c_trxn_channel%ISOPEN) THEN CLOSE c_trxn_channel; END IF;
582
583 OPEN c_trxn_channel(p_payer,p_payer_level,p_payer_equiv,p_pmt_channel);
584 --
585 -- should only return the same payment channel and thus only
586 -- one instrument type value; storing this saves a later fetch
587 --
588 FETCH c_trxn_channel INTO l_channel_instr;
589 CLOSE c_trxn_channel;
590
591 IF (l_channel_instr<1) THEN
592 RETURN FALSE;
593 ELSE
594 RETURN TRUE;
595 END IF;
596 END Payer_Channel_Valid;
597
598 FUNCTION Channel_InstrAssignment_Appl
599 (p_channel IN iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE)
600 RETURN iby_pmt_mthd_attrib_appl.attribute_applicability%TYPE
601 IS
602 l_assign_appl iby_pmt_mthd_attrib_appl.attribute_applicability%TYPE;
603
604 CURSOR c_pmt_instr
605 (ci_pmt_channel iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE)
606 IS
607 SELECT attribute_applicability
608 FROM iby_pmt_mthd_attrib_appl
609 WHERE (payment_flow = 'FUNDS_CAPTURE')
610 AND (payment_method_code = ci_pmt_channel)
611 AND (attribute_code = 'PAYMENT_INSTRUMENT');
612 BEGIN
613 IF (c_pmt_instr%ISOPEN) THEN CLOSE c_pmt_instr; END IF;
614
615 OPEN c_pmt_instr(p_channel);
616 FETCH c_pmt_instr INTO l_assign_appl;
617 CLOSE c_pmt_instr;
618
619 RETURN l_assign_appl;
620 END Channel_InstrAssignment_Appl;
621
622 -- Verifies that the channel is correct for the given
623 -- instrument assignment
624 --
625 FUNCTION InstrAssignment_Channel_Valid
626 (p_channel IN iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE,
627 p_assign IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE,
628 x_channel_instr OUT NOCOPY iby_fndcpt_pmt_chnnls_b.instrument_type%TYPE
629 )
630 RETURN BOOLEAN
631 IS
632 l_assign_id iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE;
633 l_assign_appl iby_pmt_mthd_attrib_appl.attribute_applicability%TYPE;
634
635 -- cursor def changes for bug 5117954
636 CURSOR c_channel_assign
637 (ci_channel_code IN iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE,
638 ci_assign_id IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE
639 )
640 IS
641 SELECT c.instrument_type, u.instrument_payment_use_id
642 FROM iby_fndcpt_pmt_chnnls_b c, iby_pmt_instr_uses_all u
643 WHERE (payment_channel_code = ci_channel_code)
644 AND (u.instrument_payment_use_id (+) = ci_assign_id)
645 AND (c.instrument_type = u.instrument_type(+))
646 AND (NVL(payment_flow,IBY_FNDCPT_SETUP_PUB.G_PMT_FLOW_FNDCPT)
647 = IBY_FNDCPT_SETUP_PUB.G_PMT_FLOW_FNDCPT);
648
649 BEGIN
650 IF (c_channel_assign%ISOPEN) THEN CLOSE c_channel_assign; END IF;
651
652 OPEN c_channel_assign(p_channel,p_assign);
653 FETCH c_channel_assign INTO x_channel_instr, l_assign_id;
654 CLOSE c_channel_assign;
655
656 l_assign_appl := Channel_InstrAssignment_Appl(p_channel);
657
658 IF ((x_channel_instr = IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_MANUAL)
659 AND (l_assign_id IS NULL))
660 THEN
661 RETURN TRUE;
662 ELSIF (l_assign_appl = IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_OPTIONAL)
663 THEN
664 RETURN TRUE;
665 ELSIF (NOT l_assign_id IS NULL) THEN
666 RETURN TRUE;
667 ELSE
668 RETURN FALSE;
669 END IF;
670 END InstrAssignment_Channel_Valid;
671
672 --
673 -- USE: Validates the payment instrument assignment for the given payer
674 --
675 FUNCTION Payer_InstrAssignment_Valid
676 (p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
677 p_payer_level IN VARCHAR2,
678 p_payer_equiv IN VARCHAR2,
679 p_channel_code IN iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE,
680 p_instr_assign IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE,
681 p_new_extension IN BOOLEAN
682 )
683 RETURN BOOLEAN
684 IS
685 l_assign_id iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE;
686 l_instr_req iby_pmt_mthd_attrib_appl.attribute_applicability%TYPE;
687 l_instr_type iby_fndcpt_payer_assgn_instr_v.instrument_type%TYPE;
688 l_instr_id iby_fndcpt_payer_assgn_instr_v.instrument_id%TYPE;
689 l_info_only iby_fndcpt_payer_assgn_instr_v.card_info_only_flag%TYPE;
690 l_single_use iby_fndcpt_payer_assgn_instr_v.card_single_use_flag%TYPE;
691 l_auth_flag iby_trxn_extensions_v.authorized_flag%TYPE;
692
693 CURSOR c_instr_assigns
694 (ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
695 ci_payer_level IN VARCHAR2,
696 ci_payer_equiv IN VARCHAR2,
697 ci_instr_assign IN iby_fndcpt_payer_assgn_instr_v.instr_assignment_id%TYPE
698 )
699 IS
700 SELECT instr_assignment_id, instrument_type, instrument_id,
701 NVL(card_info_only_flag,'N'), NVL(card_single_use_flag,'N')
702 FROM iby_fndcpt_payer_assgn_instr_v
703 WHERE
704 (instr_assignment_id = ci_instr_assign)
705 AND ext_payer_id IN
706 (
707 SELECT ext_payer_id
708 FROM iby_external_payers_all
709 WHERE (payment_function = ci_payer.Payment_Function)
710 AND (party_id = ci_payer.Party_Id)
711 AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
712 (ci_payer.org_type, ci_payer.org_id,
713 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
714 ci_payer_level,ci_payer_equiv,org_type,org_id,
715 cust_account_id,acct_site_use_id) = 'T')
716 );
717
718 CURSOR c_instr_extensions
719 (ci_instr_type IN iby_trxn_extensions_v.instrument_type%TYPE,
720 ci_instr_id IN iby_trxn_extensions_v.instrument_id%TYPE
721 )
722 IS
723 SELECT NVL(authorized_flag,'N')
724 FROM iby_trxn_extensions_v
725 WHERE (instrument_id = ci_instr_id)
726 AND (instrument_type = ci_instr_type);
727
728 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Payer_InstrAssignment_Valid';
729 BEGIN
730 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
731
732 IF (c_instr_assigns%ISOPEN) THEN CLOSE c_instr_assigns; END IF;
733 IF (c_instr_extensions%ISOPEN) THEN CLOSE c_instr_extensions; END IF;
734
735 l_instr_req := Channel_InstrAssignment_Appl(p_channel_code);
736
737 iby_debug_pub.add('instrument applicability:=' || l_instr_req,
738 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
739
740 IF ((l_instr_req = IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_OPTIONAL)
741 AND (p_instr_assign IS NULL))
742 THEN
743 RETURN TRUE;
744 ELSIF (l_instr_req = IBY_FNDCPT_SETUP_PUB.G_CHNNL_ATTRIB_USE_DISABLED) THEN
745 RETURN (p_instr_assign IS NULL);
746 END IF;
747
748 OPEN c_instr_assigns(p_payer,p_payer_level,p_payer_equiv,p_instr_assign);
749 FETCH c_instr_assigns INTO l_assign_id, l_instr_type, l_instr_id,
750 l_info_only, l_single_use;
751 CLOSE c_instr_assigns;
752
753 iby_debug_pub.add('fetched assignment id:=' || l_assign_id,
754 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
755 iby_debug_pub.add('info only:=' || l_info_only,
756 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
757 iby_debug_pub.add('single use:=' || l_single_use,
758 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
759
760 IF ((l_assign_id IS NULL) OR (l_info_only = 'Y')) THEN
761 RETURN FALSE;
762 ELSE
763 RETURN TRUE;
764 END IF;
765
766 -- cannot use a single-use instrument for a new trxn extension which
767 -- has already been used
768 IF ((l_single_use = 'Y') AND p_new_extension) THEN
769 OPEN c_instr_extensions(l_instr_type,l_instr_id);
770 FETCH c_instr_extensions INTO l_auth_flag;
771 CLOSE c_instr_extensions;
772 RETURN (NVL(l_auth_flag,'N') = 'N');
773 END IF;
774 END Payer_InstrAssignment_Valid;
775
776
777 FUNCTION Extension_Operation_Exists
778 (p_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
779 RETURN BOOLEAN
780 IS
781 l_count NUMBER;
782
783 CURSOR c_operation
784 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
785 IS
786 SELECT count(1)
787 FROM iby_trxn_extensions_v
788 WHERE (trxn_extension_id = ci_extension_id)
789 AND (authorization_status IN ( 'AUTH_SUCCESS', 'AUTH_PENDING' ));
790 BEGIN
791 IF (c_operation%ISOPEN) THEN CLOSE c_operation; END IF;
792
793 OPEN c_operation(p_extension_id);
794 FETCH c_operation INTO l_count;
795 IF (c_operation%NOTFOUND) THEN
796 l_count := 0;
797 END IF;
798 CLOSE c_operation;
799
800 RETURN (l_count > 0);
801 END Extension_Operation_Exists;
802
803 PROCEDURE Create_Transaction_Extension
804 (
805 p_api_version IN NUMBER,
806 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
807 p_commit IN VARCHAR2 := FND_API.G_TRUE,
808 x_return_status OUT NOCOPY VARCHAR2,
809 x_msg_count OUT NOCOPY NUMBER,
810 x_msg_data OUT NOCOPY VARCHAR2,
811 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
812 p_payer_equivalency IN VARCHAR2 :=
813 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
814 p_pmt_channel IN VARCHAR2,
815 p_instr_assignment IN NUMBER,
816 p_trxn_attribs IN TrxnExtension_rec_type,
817 x_entity_id OUT NOCOPY NUMBER,
818 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
819 )
820 IS
821 l_api_version CONSTANT NUMBER := 1.0;
822 l_module CONSTANT VARCHAR2(30) := 'Create_Transaction_Extension';
823 l_prev_msg_count NUMBER;
824
825 l_payer_level VARCHAR2(30);
826 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
827 l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
828
829 l_msg_count NUMBER;
830 l_msg_data VARCHAR2(300);
831 l_return_status VARCHAR2(1);
832 l_resp_rec IBY_PAYMENT_ADAPTER_PUB.SecureCVVResp_rec_type;
833 l_sec_code VARCHAR2(10);
834 l_encrypted VARCHAR2(1) := 'N';
835 l_segment_id NUMBER;
836
837 lx_channel_instr iby_fndcpt_pmt_chnnls_b.instrument_type%TYPE;
838
839 lx_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
840
841 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
842 BEGIN
843 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
844 -- test_debug('Create_Transaction_Extension=> Enter..');
845
846 IF NOT FND_API.Compatible_API_Call (l_api_version,
847 p_api_version,
848 l_module,
849 G_PKG_NAME)
850 THEN
851 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
852 debug_level => FND_LOG.LEVEL_ERROR,
853 module => G_DEBUG_MODULE || l_module);
854 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
855 FND_MSG_PUB.Add;
856 RAISE FND_API.G_EXC_ERROR;
857 END IF;
858
859 IF FND_API.to_Boolean( p_init_msg_list ) THEN
860 FND_MSG_PUB.initialize;
861 END IF;
862 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
863
864 iby_debug_pub.add('checking payer context',
865 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
866 -- test_debug('Create_Transaction_Extension=> Checking Payer context..');
867
868 IBY_FNDCPT_SETUP_PUB.Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
869 l_payer_level,l_payer_id,l_payer_attribs);
870
871 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
872 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
873 ELSE
874 -- We do not need to check against the MAX_TANGIBLEID_LEN, because
875 -- we now generate the tangibleid using <apps short name>_<trxn_extension_id>
876 IF (p_trxn_attribs.Order_Id IS NULL)
877 -- OR (LENGTH(Get_Tangible_Id(p_trxn_attribs.Originating_Application_Id,
878 -- p_trxn_attribs.Order_Id,
879 -- p_trxn_attribs.Trxn_Ref_Number1,
880 -- p_trxn_attribs.Trxn_Ref_Number2))
881 -- > iby_bill_pkg.G_MAX_TANGIBLEID_LEN
882 -- )
883 THEN
884 -- test_debug('invalid order id');
885 iby_debug_pub.add('invalid order id',
886 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
887 x_response.Result_Code := G_RC_INVALID_EXTENSION_ATTRIB;
888 iby_fndcpt_common_pub.Prepare_Result
889 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
890 RETURN;
891 END IF;
892
893 -- channel must be assigned to an equivalent payer
894 --
895 IF (NOT Payer_Channel_Valid(p_payer,l_payer_level,
896 p_payer_equivalency,p_pmt_channel))
897 THEN
898 x_response.Result_Code := IBY_FNDCPT_SETUP_PUB.G_RC_INVALID_CHNNL;
899 iby_fndcpt_common_pub.Prepare_Result
900 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
901 RETURN;
902 END IF;
903
904 --
905 -- instrument assignment must be to an instrument of the same type
906 -- as the payment channel's
907 --
908 IF (NOT InstrAssignment_Channel_Valid(p_pmt_channel,p_instr_assignment,
909 lx_channel_instr))
910 THEN
911 -- test_debug('instrument asssignment not valid for channel');
912 iby_debug_pub.add('instrument asssignment not valid for channel',
913 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
914 x_response.Result_Code := IBY_FNDCPT_SETUP_PUB.G_RC_INVALID_INSTR_ASSIGN;
915 iby_fndcpt_common_pub.Prepare_Result
916 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
917 RETURN;
918 END IF;
919
920 --
921 -- instrument must be assigned to an equivalent payer
922 --
923 IF (NOT Payer_InstrAssignment_Valid(p_payer,l_payer_level,
924 p_payer_equivalency,
925 p_pmt_channel,
926 p_instr_assignment,TRUE))
927 THEN
928 --test_debug('instrument asssignment not valid payer');
929 iby_debug_pub.add('instrument asssignment not valid payer',
930 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
931 x_response.Result_Code := IBY_FNDCPT_SETUP_PUB.G_RC_INVALID_INSTR_ASSIGN;
932 iby_fndcpt_common_pub.Prepare_Result
933 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
934 RETURN;
935 END IF;
936
937 -- validate bank end-dates
938 IF (lx_channel_instr = IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_BANKACCT) THEN
939 null;
940 END IF;
941
942 IF (NOT Extension_Valid(p_pmt_channel,p_trxn_attribs,FALSE,FALSE)) THEN
943 -- test_debug('invalid trxn extension attributes');
944 iby_debug_pub.add('invalid trxn extension attributes',
945 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
946 x_response.Result_Code := G_RC_INVALID_EXTENSION_ATTRIB;
947 ELSE
948
949 -- create payer on the fly if necessary
950 IF (l_payer_id IS NULL) THEN
951 IBY_FNDCPT_SETUP_PUB.Set_Payer_Attributes
952 (1.0, FND_API.G_FALSE, FND_API.G_FALSE,
953 x_return_status, x_msg_count, x_msg_data,
954 p_payer, l_payer_attribs, l_payer_id, lx_result
955 );
956
957 IF (lx_result.Result_Code <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS) THEN
958 -- test_debug('could not set payer attributes');
959 iby_debug_pub.add('could not set payer attributes',
960 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
961 x_response := lx_result;
962 RETURN;
963 END IF;
964 END IF;
965
966 -- PABP: For IMMEDIATE mode of encryption, make an http callout --
967 -- to the ECApp and encrypt the CVV value --
968 IF (NOT ((p_trxn_attribs.Instrument_Security_Code IS NULL) OR
969 (p_trxn_attribs.Instrument_security_Code = FND_API.G_MISS_CHAR))
970 ) THEN
971 IF ('IMMEDIATE' = IBY_CREDITCARD_PKG.Get_CC_Encrypt_Mode()) THEN
972 -- Make an HTTP call out to the middle tier
973 -- test_debug('calling secure_extension..');
974 IBY_PAYMENT_ADAPTER_PUB.OraSecureExtension
975 (FND_API.G_FALSE,
976 p_trxn_attribs.Instrument_security_Code,
977 l_return_status,
978 l_msg_count,
979 l_msg_data,
980 l_resp_rec
981 );
982
983 --test_debug('after call to ora secure: respRec: '|| l_resp_rec.Segment_ID);
984
985 IF (NOT (l_resp_rec.Segment_ID IS NULL))
986 THEN
987
988 -- test_debug('cvv encryption successful..');
989
990 l_segment_id := l_resp_rec.Segment_ID;
991 l_encrypted := 'Y';
992 l_sec_code := LPAD('X',LENGTH(p_trxn_attribs.Instrument_Security_Code),'X');
993 ELSE
994 -- test_debug('Unknown error occurred while cvv encryption !!');
995 l_segment_id := null;
996 l_encrypted := 'N';
997 l_sec_code := null;
998 END IF;
999
1000 ELSE
1001 l_segment_id := null;
1002 l_encrypted := 'N';
1003 l_sec_code := p_trxn_attribs.Instrument_Security_Code;
1004 END IF;
1005 END IF;
1006 -- test_debug('l_segment_id: '|| l_segment_id);
1007 -- test_debug('l_encrypted: '|| l_encrypted);
1008 -- test_debug('l_sec_code: '|| l_sec_code);
1009
1010 SELECT iby_fndcpt_tx_extensions_s.NEXTVAL INTO x_entity_id FROM DUAL;
1011 INSERT INTO iby_fndcpt_tx_extensions
1012 (trxn_extension_id, payment_channel_code, ext_payer_id,
1013 instr_assignment_id, origin_application_id, instrument_security_code,
1014 voice_authorization_flag, voice_authorization_date,
1015 voice_authorization_code, order_id, po_number,
1016 po_line_number, trxn_ref_number1, trxn_ref_number2, additional_info,
1017 instr_sec_code_length, encrypted, instr_code_sec_segment_id,
1018 created_by, creation_date, last_updated_by, last_update_date,
1019 last_update_login, object_version_number
1020 )
1021 VALUES
1022 (
1023 x_entity_id, p_pmt_channel, l_payer_id, p_instr_assignment,
1024 p_trxn_attribs.Originating_Application_Id,
1025 l_sec_code,
1026 p_trxn_attribs.VoiceAuth_Flag,
1027 p_trxn_attribs.VoiceAuth_Date, p_trxn_attribs.VoiceAuth_Code,
1028 p_trxn_attribs.Order_Id, p_trxn_attribs.PO_Number,
1029 p_trxn_attribs.PO_Line_Number,
1030 p_trxn_attribs.Trxn_Ref_Number1, p_trxn_attribs.Trxn_Ref_Number2,
1031 p_trxn_attribs.Additional_Info,
1032 length(p_trxn_attribs.Instrument_Security_Code), l_encrypted,
1033 l_segment_id,
1034 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
1035 fnd_global.login_id, 1
1036 );
1037
1038 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1039 END IF;
1040 END IF;
1041
1042 iby_fndcpt_common_pub.Prepare_Result
1043 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1044
1045 IF FND_API.To_Boolean(p_commit) THEN
1046 COMMIT;
1047 END IF;
1048
1049 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1050
1051 EXCEPTION
1052
1053 WHEN FND_API.G_EXC_ERROR THEN
1054
1055 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1056 debug_level => FND_LOG.LEVEL_ERROR,
1057 module => G_DEBUG_MODULE || l_module);
1058 x_return_status := FND_API.G_RET_STS_ERROR;
1059 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1060 p_data => x_msg_data
1061 );
1062 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1063
1064 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1065 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1066 module => G_DEBUG_MODULE || l_module);
1067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1068 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1069 p_data => x_msg_data
1070 );
1071 WHEN OTHERS THEN
1072
1073 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1074 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1075 module => G_DEBUG_MODULE || l_module);
1076
1077 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1078
1079 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1080 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1081 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1082 END IF;
1083 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1084 p_data => x_msg_data
1085 );
1086 END Create_Transaction_Extension;
1087
1088
1089 PROCEDURE Update_Transaction_Extension
1090 (
1091 p_api_version IN NUMBER,
1092 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1093 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1094 x_return_status OUT NOCOPY VARCHAR2,
1095 x_msg_count OUT NOCOPY NUMBER,
1096 x_msg_data OUT NOCOPY VARCHAR2,
1097 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1098 p_payer_equivalency IN VARCHAR2 :=
1099 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1100 p_entity_id IN NUMBER,
1101 p_pmt_channel IN VARCHAR2,
1102 p_instr_assignment IN NUMBER,
1103 p_trxn_attribs IN TrxnExtension_rec_type,
1104 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1105 )
1106 IS
1107 l_api_version CONSTANT NUMBER := 1.0;
1108 l_module CONSTANT VARCHAR2(30) := 'Update_Transaction_Extension';
1109 l_prev_msg_count NUMBER;
1110
1111 l_payer_level VARCHAR2(30);
1112 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
1113 l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
1114
1115 l_xtension_id iby_fndcpt_tx_extensions.trxn_extension_id%TYPE;
1116 l_pmt_channel iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE;
1117 l_instr_assign iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE;
1118 l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
1119 l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
1120 l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
1121 l_app_id iby_fndcpt_tx_extensions.origin_application_id%TYPE;
1122 -- l_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
1123
1124 lx_channel_instr iby_fndcpt_pmt_chnnls_b.instrument_type%TYPE;
1125
1126 l_msg_count NUMBER;
1127 l_msg_data VARCHAR2(300);
1128 l_return_status VARCHAR2(1);
1129 l_resp_rec IBY_PAYMENT_ADAPTER_PUB.SecureCVVResp_rec_type;
1130 l_sec_code VARCHAR2(10);
1131 l_encrypted VARCHAR2(1) := 'N';
1132 l_segment_id NUMBER;
1133
1134 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || l_module;
1135
1136 CURSOR c_extension
1137 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
1138 ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1139 ci_payer_level IN VARCHAR2,
1140 ci_payer_equiv IN VARCHAR2
1141 )
1142 IS
1143 SELECT
1144 x.trxn_extension_id, x.payment_channel_code, x.instr_assignment_id,
1145 x.order_id, x.trxn_ref_number1, x.trxn_ref_number2,
1146 x.origin_application_id, x.instr_code_sec_segment_id
1147 FROM iby_fndcpt_tx_extensions x, iby_external_payers_all p
1148 WHERE (x.ext_payer_id = p.ext_payer_id)
1149 AND (x.trxn_extension_id = ci_extension_id)
1150 AND (p.party_id = ci_payer.Party_Id)
1151 AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
1152 (ci_payer.org_type, ci_payer.org_id,
1153 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
1154 ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
1155 p.cust_account_id,p.acct_site_use_id) = 'T');
1156
1157 BEGIN
1158 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1159
1160 -- test_debug('update_extension called..');
1161
1162 IF (c_extension%ISOPEN) THEN CLOSE c_extension; END IF;
1163
1164 IF NOT FND_API.Compatible_API_Call (l_api_version,
1165 p_api_version,
1166 l_module,
1167 G_PKG_NAME)
1168 THEN
1169 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1170 debug_level => FND_LOG.LEVEL_ERROR,
1171 module => G_DEBUG_MODULE || l_module);
1172 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1173 FND_MSG_PUB.Add;
1174 RAISE FND_API.G_EXC_ERROR;
1175 END IF;
1176
1177 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1178 FND_MSG_PUB.initialize;
1179 END IF;
1180 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1181
1182 iby_debug_pub.add('checking payer context',iby_debug_pub.G_LEVEL_INFO,
1183 l_dbg_mod);
1184 IBY_FNDCPT_SETUP_PUB.Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1185 l_payer_level,l_payer_id,l_payer_attribs);
1186
1187 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1188 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1189 ELSE
1190 -- verify the transaction id is for a payer equivalent to the
1191 -- given one
1192 OPEN c_extension(p_entity_id,p_payer,l_payer_level,p_payer_equivalency);
1193 FETCH c_extension INTO l_xtension_id, l_pmt_channel, l_instr_assign,
1194 l_order_id, l_trxn_ref1, l_trxn_ref2, l_app_id, l_segment_id;
1195 CLOSE c_extension;
1196
1197 l_order_id := NVL(p_trxn_attribs.Order_Id,l_order_id);
1198 l_trxn_ref1 := NVL(p_trxn_attribs.Trxn_Ref_Number1,l_trxn_ref1);
1199 l_trxn_ref2 := NVL(p_trxn_attribs.Trxn_Ref_Number2,l_trxn_ref2);
1200 l_app_id := NVL(p_trxn_attribs.Originating_Application_Id,l_app_id);
1201
1202 IF (LENGTH(Get_Tangible_Id(l_app_id,l_order_id,l_trxn_ref1,
1203 l_trxn_ref2))
1204 > iby_bill_pkg.G_MAX_TANGIBLEID_LEN
1205 )
1206 THEN
1207 iby_debug_pub.add('invalid order id',
1208 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1209 x_response.Result_Code := G_RC_INVALID_EXTENSION_ATTRIB;
1210 iby_fndcpt_common_pub.Prepare_Result
1211 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1212 RETURN;
1213 END IF;
1214
1215 l_pmt_channel := NVL(p_pmt_channel,l_pmt_channel);
1216 IF (p_instr_assignment = FND_API.G_MISS_NUM) THEN
1217 l_instr_assign := NULL;
1218 ELSIF (NOT p_instr_assignment IS NULL) THEN
1219 l_instr_ASSIGN := p_instr_assignment;
1220 END IF;
1221
1222 IF (NOT l_xtension_id IS NULL) THEN
1223 --
1224 -- validate the trxn attributes; note that mandatory ones
1225 -- may be NULL in which case the current values will be used
1226 --
1227 IF (NOT Extension_Valid(l_pmt_channel,p_trxn_attribs,TRUE,FALSE)) THEN
1228 iby_debug_pub.add('invalid extension attributes',
1229 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1230 x_response.Result_Code := G_RC_INVALID_EXTENSION_ATTRIB;
1231 --
1232 -- make sure no operations already performed on the extension;
1233 --
1234 ELSIF ( Extension_Operation_Exists(p_entity_id) ) THEN
1235 iby_debug_pub.add('operations exist for extension; cannot update',
1236 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1237 x_response.Result_Code := G_RC_EXTENSION_IMMUTABLE;
1238 ELSE
1239
1240 IF (NOT p_pmt_channel IS NULL) OR (NOT p_instr_assignment IS NULL)
1241 THEN
1242 IF (NOT Payer_Channel_Valid(p_payer,l_payer_level,
1243 p_payer_equivalency,l_pmt_channel))
1244 THEN
1245 iby_debug_pub.add('invalid payment channel for payer',
1246 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1247 x_response.Result_Code := IBY_FNDCPT_SETUP_PUB.G_RC_INVALID_CHNNL;
1248 iby_fndcpt_common_pub.Prepare_Result
1249 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,
1250 x_response);
1251 RETURN;
1252 END IF;
1253
1254 IF (NOT InstrAssignment_Channel_Valid(l_pmt_channel,l_instr_assign,
1255 lx_channel_instr))
1256 OR
1257 (NOT Payer_InstrAssignment_Valid(p_payer,l_payer_level,
1258 p_payer_equivalency,
1259 l_pmt_channel,
1260 l_instr_assign,FALSE))
1261 THEN
1262 iby_debug_pub.add('invalid payment channel for instrument or payer',
1263 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1264 x_response.Result_Code := IBY_FNDCPT_SETUP_PUB.G_RC_INVALID_INSTR_ASSIGN;
1265 iby_fndcpt_common_pub.Prepare_Result
1266 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,
1267 x_response);
1268 RETURN;
1269 END IF;
1270 END IF;
1271
1272
1273 -- PABP: For IMMEDIATE mode of encryption, make an http callout --
1274 -- to the ECApp and encrypt the CVV value --
1275 IF (NOT ((p_trxn_attribs.Instrument_Security_Code IS NULL) OR
1276 (p_trxn_attribs.Instrument_security_Code = FND_API.G_MISS_CHAR))
1277 ) THEN
1278 IF ('IMMEDIATE' = IBY_CREDITCARD_PKG.Get_CC_Encrypt_Mode()) THEN
1279 -- Make an HTTP call out to the middle tier
1280 -- test_debug('calling secure_extension..');
1281 IBY_PAYMENT_ADAPTER_PUB.OraSecureExtension
1282 (FND_API.G_FALSE,
1283 p_trxn_attribs.Instrument_security_Code,
1284 l_return_status,
1285 l_msg_count,
1286 l_msg_data,
1287 l_resp_rec
1288 );
1289 IF (NOT (l_resp_rec.Segment_ID IS NULL) )
1290 THEN
1291
1292 -- test_debug('cvv encryption successful..');
1293
1294 l_segment_id := l_resp_rec.Segment_ID;
1295 l_encrypted := 'Y';
1296 l_sec_code := LPAD('X',LENGTH(p_trxn_attribs.Instrument_Security_Code),'X');
1297 ELSE
1298 -- test_debug('Unknown error occurred while cvv encryption !!');
1299 l_segment_id := null;
1300 l_encrypted := 'N';
1301 l_sec_code := null;
1302 END IF;
1303
1304 ELSE
1305 l_segment_id := null;
1306 l_encrypted := 'N';
1307 l_sec_code := p_trxn_attribs.Instrument_Security_Code;
1308 END IF;
1309 END IF;
1310 -- test_debug('l_segment_id: '|| l_segment_id);
1311 -- test_debug('l_encrypted: '|| l_encrypted);
1312 -- test_debug('l_sec_code: '|| l_sec_code);
1313
1314
1315 --
1316 -- trxn attrib validation ensures that required columns will
1317 -- not be null'd out; note that GSCC standard File.Sql.48
1318 -- defines NULL values as missing and G_MISS_XXX values
1319 -- as an intention to NULL-out
1320 --
1321 -- test_debug('p_trxn_attribs.Instrument_Security_Code: '||p_trxn_attribs.Instrument_Security_Code);
1322 -- test_debug('encrypted: '||);
1323 UPDATE iby_fndcpt_tx_extensions
1324 SET
1325 payment_channel_code = NVL(p_pmt_channel,payment_channel_code),
1326 instr_assignment_id =
1327 DECODE(p_instr_assignment, FND_API.G_MISS_NUM,NULL,
1328 NULL,instr_assignment_id, p_instr_assignment),
1329 origin_application_id =
1330 NVL(p_trxn_attribs.Originating_Application_Id,
1331 origin_application_id),
1332 instrument_security_code =
1333 DECODE(p_trxn_attribs.Instrument_Security_Code,
1334 FND_API.G_MISS_CHAR,NULL, NULL,instrument_security_code,
1335 l_sec_code),
1336 instr_sec_code_length =
1337 length(DECODE(p_trxn_attribs.Instrument_Security_Code,
1338 FND_API.G_MISS_CHAR,NULL,
1339 NULL,instrument_security_code,
1340 l_sec_code)
1341 ),
1342 encrypted =
1343 DECODE(p_trxn_attribs.Instrument_Security_Code,
1344 FND_API.G_MISS_CHAR,'N', NULL,encrypted, l_encrypted),
1345 instr_code_sec_segment_id =
1346 NVL(l_segment_id, instr_code_sec_segment_id),
1347 voice_authorization_flag =
1348 DECODE(p_trxn_attribs.VoiceAuth_Flag, FND_API.G_MISS_CHAR,NULL,
1349 NULL,voice_authorization_flag, p_trxn_attribs.VoiceAuth_Flag),
1350 voice_authorization_date =
1351 DECODE(p_trxn_attribs.VoiceAuth_Date, FND_API.G_MISS_DATE,NULL,
1352 NULL,voice_authorization_date, p_trxn_attribs.VoiceAuth_Date),
1353 voice_authorization_code =
1354 DECODE(p_trxn_attribs.VoiceAuth_Code, FND_API.G_MISS_CHAR,NULL,
1355 NULL,voice_authorization_code, p_trxn_attribs.VoiceAuth_Code),
1356 order_id = NVL(p_trxn_attribs.Order_Id, order_id),
1357 po_number = DECODE(p_trxn_attribs.PO_Number, FND_API.G_MISS_CHAR,NULL,
1358 NULL,po_number, p_trxn_attribs.PO_Number),
1359 po_line_number = DECODE(p_trxn_attribs.PO_Line_Number,
1360 FND_API.G_MISS_CHAR,NULL, NULL,po_line_number,
1361 p_trxn_attribs.PO_Line_Number),
1362 trxn_ref_number1 = DECODE(p_trxn_attribs.Trxn_Ref_Number1,
1363 FND_API.G_MISS_CHAR,NULL, NULL,trxn_ref_number1,
1364 p_trxn_attribs.Trxn_Ref_Number1),
1365 trxn_ref_number2 = DECODE(p_trxn_attribs.Trxn_Ref_Number2,
1366 FND_API.G_MISS_CHAR,NULL, NULL,trxn_ref_number2,
1367 p_trxn_attribs.Trxn_Ref_Number2),
1368 additional_info = DECODE(p_trxn_attribs.Additional_Info,
1369 FND_API.G_MISS_CHAR,NULL, NULL,additional_info,
1370 p_trxn_attribs.Additional_Info),
1371 last_updated_by = fnd_global.user_id,
1372 last_update_date = SYSDATE,
1373 last_update_login = fnd_global.login_id,
1374 object_version_number = object_version_number + 1
1375 WHERE (trxn_extension_id = p_entity_id);
1376
1377 iby_debug_pub.add('updating extension',
1378 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1379 IF (NOT p_trxn_attribs.Instrument_Security_Code IS NULL) AND
1380 (p_trxn_attribs.Instrument_Security_Code <> FND_API.G_MISS_CHAR)
1381 THEN
1382 DELETE FROM iby_security_segments
1383 WHERE (sec_segment_id = l_segment_id);
1384 END IF;
1385
1386 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1387 END IF;
1388 ELSE
1389 x_response.Result_Code := G_RC_INVALID_EXTENSION_ID;
1390 END IF;
1391 END IF;
1392
1393 IF FND_API.To_Boolean(p_commit) THEN
1394 COMMIT;
1395 END IF;
1396
1397 iby_fndcpt_common_pub.Prepare_Result
1398 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1399
1400 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1401
1402 EXCEPTION
1403
1404 WHEN FND_API.G_EXC_ERROR THEN
1405
1406 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1407 debug_level => FND_LOG.LEVEL_ERROR,
1408 module => G_DEBUG_MODULE || l_module);
1409 x_return_status := FND_API.G_RET_STS_ERROR;
1410 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1411 p_data => x_msg_data
1412 );
1413 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1414
1415 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1416 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1417 module => G_DEBUG_MODULE || l_module);
1418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1419 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1420 p_data => x_msg_data
1421 );
1422 WHEN OTHERS THEN
1423
1424 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1425 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1426 module => G_DEBUG_MODULE || l_module);
1427
1428 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1429
1430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1431 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1432 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1433 END IF;
1434
1435 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1436 p_data => x_msg_data
1437 );
1438
1439 END Update_Transaction_Extension;
1440
1441
1442 PROCEDURE Get_Transaction_Extension
1443 (
1444 p_api_version IN NUMBER,
1445 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1446 x_return_status OUT NOCOPY VARCHAR2,
1447 x_msg_count OUT NOCOPY NUMBER,
1448 x_msg_data OUT NOCOPY VARCHAR2,
1449 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1450 p_entity_id IN NUMBER,
1451 x_trxn_attribs OUT NOCOPY TrxnExtension_rec_type,
1452 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1453 )
1454 IS
1455 l_api_version CONSTANT NUMBER := 1.0;
1456 l_module CONSTANT VARCHAR2(30) := 'Get_Transaction_Extension';
1457 l_prev_msg_count NUMBER;
1458
1459 l_payer_level VARCHAR2(30);
1460 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
1461 l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
1462
1463 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || l_module;
1464
1465 CURSOR c_extension
1466 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
1467 IS
1468 SELECT
1469 origin_application_id, RPAD('X',instr_sec_code_length,'X'),
1470 voice_authorization_flag, voice_authorization_date,
1471 voice_authorization_code, order_id, po_number,
1472 po_line_number, trxn_ref_number1, trxn_ref_number2, additional_info
1473 FROM iby_fndcpt_tx_extensions
1474 WHERE trxn_extension_id = ci_extension_id;
1475
1476 BEGIN
1477 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1478
1479 IF (c_extension%ISOPEN) THEN
1480 CLOSE c_extension;
1481 END IF;
1482
1483 IF NOT FND_API.Compatible_API_Call (l_api_version,
1484 p_api_version,
1485 l_module,
1486 G_PKG_NAME)
1487 THEN
1488 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1489 debug_level => FND_LOG.LEVEL_ERROR,
1490 module => G_DEBUG_MODULE || l_module);
1491 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1492 FND_MSG_PUB.Add;
1493 RAISE FND_API.G_EXC_ERROR;
1494 END IF;
1495
1496 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1497 FND_MSG_PUB.initialize;
1498 END IF;
1499 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1500
1501 iby_debug_pub.add('checking payer contxt',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1502 IBY_FNDCPT_SETUP_PUB.Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1503 l_payer_level,l_payer_id,l_payer_attribs);
1504
1505 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1506 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1507 ELSE
1508 OPEN c_extension(p_entity_id);
1509 FETCH c_extension INTO
1510 x_trxn_attribs.Originating_Application_Id,
1511 x_trxn_attribs.Instrument_Security_Code,
1512 x_trxn_attribs.VoiceAuth_Flag, x_trxn_attribs.VoiceAuth_Date,
1513 x_trxn_attribs.VoiceAuth_Code, x_trxn_attribs.Order_Id,
1514 x_trxn_attribs.PO_Number, x_trxn_attribs.PO_Line_Number,
1515 x_trxn_attribs.Trxn_Ref_Number1, x_trxn_attribs.Trxn_Ref_Number2,
1516 x_trxn_attribs.Additional_Info;
1517
1518 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1519 IF (c_extension%NOTFOUND) THEN
1520 iby_debug_pub.add('could not find extension',
1521 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1522 x_response.Result_Code := G_RC_INVALID_EXTENSION_ID;
1523 ELSE
1524 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1525 END IF;
1526 CLOSE c_extension;
1527 END IF;
1528
1529 iby_fndcpt_common_pub.Prepare_Result
1530 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1531
1532 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1533 EXCEPTION
1534
1535 WHEN FND_API.G_EXC_ERROR THEN
1536
1537 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1538 debug_level => FND_LOG.LEVEL_ERROR,
1539 module => G_DEBUG_MODULE || l_module);
1540 x_return_status := FND_API.G_RET_STS_ERROR;
1541 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1542 p_data => x_msg_data
1543 );
1544 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1545
1546 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1547 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1548 module => G_DEBUG_MODULE || l_module);
1549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1550 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1551 p_data => x_msg_data
1552 );
1553 WHEN OTHERS THEN
1554
1555 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1556 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1557 module => G_DEBUG_MODULE || l_module);
1558
1559 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1560
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) THEN
1563 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1564 END IF;
1565
1566 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1567 p_data => x_msg_data
1568 );
1569
1570 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1571 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1572 module => G_DEBUG_MODULE || l_module);
1573 iby_debug_pub.add(debug_msg => 'Exit Exception',
1574 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1575 module => G_DEBUG_MODULE || l_module);
1576
1577 END Get_Transaction_Extension;
1578
1579
1580 PROCEDURE Delete_Transaction_Extension
1581 (
1582 p_api_version IN NUMBER,
1583 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1584 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1585 x_return_status OUT NOCOPY VARCHAR2,
1586 x_msg_count OUT NOCOPY NUMBER,
1587 x_msg_data OUT NOCOPY VARCHAR2,
1588 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1589 p_payer_equivalency IN VARCHAR2 :=
1590 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1591 p_entity_id IN NUMBER,
1592 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1593 )
1594 IS
1595 l_api_version CONSTANT NUMBER := 1.0;
1596 l_module CONSTANT VARCHAR2(30) := 'Delete_Transaction_Extension';
1597 l_prev_msg_count NUMBER;
1598
1599 l_payer_level VARCHAR2(30);
1600 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
1601 l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
1602
1603 l_extension_id NUMBER;
1604 l_copy_count NUMBER;
1605
1606 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || l_module;
1607
1608 CURSOR c_extension
1609 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
1610 ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1611 ci_payer_level IN VARCHAR2,
1612 ci_payer_equiv IN VARCHAR2
1613 )
1614 IS
1615 SELECT x.trxn_extension_id
1616 FROM iby_fndcpt_tx_extensions x, iby_external_payers_all p,
1617 iby_pmt_instr_uses_all i
1618 WHERE (x.trxn_extension_id = ci_extension_id)
1619 AND (x.instr_assignment_id = i.instrument_payment_use_id(+))
1620 AND (NVL(x.ext_payer_id,i.ext_pmt_party_id) = p.ext_payer_id)
1621 AND (p.party_id = ci_payer.Party_Id)
1622 AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
1623 (ci_payer.org_type, ci_payer.org_id,
1624 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
1625 ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
1626 p.cust_account_id,p.acct_site_use_id) = 'T');
1627
1628 BEGIN
1629 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1630
1631 IF (c_extension%ISOPEN) THEN
1632 CLOSE c_extension;
1633 END IF;
1634
1635 IF NOT FND_API.Compatible_API_Call (l_api_version,
1636 p_api_version,
1637 l_module,
1638 G_PKG_NAME)
1639 THEN
1640 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1641 debug_level => FND_LOG.LEVEL_ERROR,
1642 module => G_DEBUG_MODULE || l_module);
1643 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1644 FND_MSG_PUB.Add;
1645 RAISE FND_API.G_EXC_ERROR;
1646 END IF;
1647
1648 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1649 FND_MSG_PUB.initialize;
1650 END IF;
1651 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1652
1653 iby_debug_pub.add('checking payer contxt',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1654 IBY_FNDCPT_SETUP_PUB.Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1655 l_payer_level,l_payer_id,l_payer_attribs);
1656
1657 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1658 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1659 ELSE
1660 -- verify the transaction id is for a payer equivalent to the
1661 -- given one
1662 OPEN c_extension(p_entity_id,p_payer,l_payer_level,p_payer_equivalency);
1663 FETCH c_extension INTO l_extension_id;
1664 IF (NOT c_extension%NOTFOUND) THEN
1665 -- if trxn extension copied or associated with a payment operation,
1666 -- cannot be deleted
1667 l_copy_count := Get_Tx_Extension_Copy_Count(p_entity_id);
1668 IF ( Extension_Operation_Exists(p_entity_id)
1669 OR (l_copy_count>0) )
1670 THEN
1671 iby_debug_pub.add('extension copied or operation done; cannot delete',
1672 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1673 x_response.Result_Code := G_RC_EXTENSION_IMMUTABLE;
1674 ELSE
1675 SAVEPOINT Delete_Transaction_Extension;
1676
1677 -- delete all links to its source extensions
1678 DELETE iby_fndcpt_tx_xe_copies
1679 WHERE (copy_trxn_extension_id = p_entity_id);
1680
1681 DELETE iby_fndcpt_tx_extensions
1682 WHERE (trxn_extension_id = p_entity_id);
1683 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1684 END IF;
1685 ELSE
1686 x_response.Result_Code := G_RC_INVALID_EXTENSION_ID;
1687 END IF;
1688 CLOSE c_extension;
1689 END IF;
1690
1691 IF FND_API.To_Boolean(p_commit) THEN
1692 COMMIT;
1693 END IF;
1694
1695 iby_fndcpt_common_pub.Prepare_Result
1696 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1697
1698 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1699 EXCEPTION
1700
1701 WHEN FND_API.G_EXC_ERROR THEN
1702 ROLLBACK TO Delete_Transaction_Extension;
1703 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1704 debug_level => FND_LOG.LEVEL_ERROR,
1705 module => G_DEBUG_MODULE || l_module);
1706 x_return_status := FND_API.G_RET_STS_ERROR;
1707 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1708 p_data => x_msg_data
1709 );
1710 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1711 ROLLBACK TO Delete_Transaction_Extension;
1712 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1713 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1714 module => G_DEBUG_MODULE || l_module);
1715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1716 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1717 p_data => x_msg_data
1718 );
1719
1720 WHEN OTHERS THEN
1721 ROLLBACK TO Delete_Transaction_Extension;
1722 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1723 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1724 module => G_DEBUG_MODULE || l_module);
1725
1726 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1727
1728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1729 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1730 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1731 END IF;
1732
1733 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1734 p_data => x_msg_data
1735 );
1736
1737 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1738 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1739 module => G_DEBUG_MODULE || l_module);
1740 iby_debug_pub.add(debug_msg => 'Exit Exception',
1741 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1742 module => G_DEBUG_MODULE || l_module);
1743
1744 END Delete_Transaction_Extension;
1745
1746 PROCEDURE Copy_Transaction_Extension
1747 (
1748 p_api_version IN NUMBER,
1749 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1750 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1751 x_return_status OUT NOCOPY VARCHAR2,
1752 x_msg_count OUT NOCOPY NUMBER,
1753 x_msg_data OUT NOCOPY VARCHAR2,
1754 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1755 p_payer_equivalency IN VARCHAR2 :=
1756 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1757 p_entities IN IBY_FNDCPT_COMMON_PUB.Id_tbl_type,
1758 p_trxn_attribs IN TrxnExtension_rec_type,
1759 x_entity_id OUT NOCOPY NUMBER,
1760 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1761 )
1762 IS
1763 l_api_version CONSTANT NUMBER := 1.0;
1764 l_module CONSTANT VARCHAR2(30) := 'Copy_Transaction_Extension';
1765 l_prev_msg_count NUMBER;
1766
1767 l_payer_level VARCHAR2(30);
1768 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
1769 l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
1770
1771 l_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
1772 l_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
1773 lc_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
1774 lc_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
1775 lc_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
1776 lc_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
1777 lc_va_flag iby_fndcpt_tx_extensions.voice_authorization_flag%TYPE;
1778 lc_va_code iby_fndcpt_tx_extensions.voice_authorization_code%TYPE;
1779 lc_va_date iby_fndcpt_tx_extensions.voice_authorization_date%TYPE;
1780 lc_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
1781 lc_instr_code_sec_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
1782
1783 l_extension TrxnExtension_rec_type;
1784 l_x_not_found BOOLEAN;
1785 -- whether the new instrument assignment (if passed) was validated
1786 l_val_instr BOOLEAN;
1787 l_consumed_cvv2 BOOLEAN;
1788 l_copy_count NUMBER;
1789 l_persist_auth VARCHAR2(1);
1790
1791 l_segment_id NUMBER;
1792
1793 lx_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1794
1795 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || l_module;
1796
1797 CURSOR c_extension
1798 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
1799 ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1800 ci_payer_level IN VARCHAR2,
1801 ci_payer_equiv IN VARCHAR2,
1802 ci_copy_instr_assign_id IN
1803 iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE
1804 )
1805 IS
1806 SELECT x.payment_channel_code, x.instr_assignment_id,
1807 x.instrument_security_code, x.instr_sec_code_length,
1808 x.voice_authorization_flag, x.voice_authorization_code,
1809 x.voice_authorization_date, x.encrypted, x.instr_code_sec_segment_id
1810 FROM iby_fndcpt_tx_extensions x, iby_pmt_instr_uses_all i,
1811 iby_pmt_instr_uses_all cpi, iby_external_payers_all p
1812 WHERE (x.instr_assignment_id = i.instrument_payment_use_id)
1813 -- can assume this assignment is for funds capture
1814 -- bug 7017004 - use the payer information on the trxn extension
1815 --AND (i.ext_pmt_party_id = p.ext_payer_id)
1816 AND (x.ext_payer_id = p.ext_payer_id)
1817 AND (x.trxn_extension_id = ci_extension_id)
1818 AND (NVL(ci_copy_instr_assign_id,x.instr_assignment_id) = cpi.instrument_payment_use_id)
1819 --
1820 -- payer may change during copy; thus allow the instrument assignment id
1821 -- to change so long as the underlying instrument is the same
1822 --
1823 AND (
1824 --
1825 -- EXCLUSIVE OR: NULL-out the payer party id if a new
1826 -- instrument assignment id is passed so that the clause does
1827 -- not succeed should the new assignment's instrument id not match
1828 -- put its payer does
1829 --
1830 ((p.party_id = ci_payer.Party_Id)
1831 AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
1832 (ci_payer.org_type, ci_payer.org_id,
1833 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
1834 ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
1835 p.cust_account_id,p.acct_site_use_id) = 'T'
1836 )
1837 AND (ci_copy_instr_assign_id IS NULL)
1838 )
1839 OR
1840 (
1841 (i.instrument_type = cpi.instrument_type)
1842 AND (i.instrument_id = cpi.instrument_id)
1843 AND (NOT ci_copy_instr_assign_id IS NULL)
1844 )
1845 )
1846 ORDER BY x.creation_date DESC;
1847
1848 CURSOR c_auths
1849 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
1850 IS
1851 SELECT NVL(sp.settle_require_vrfy_flag,'Y')
1852 FROM iby_trxn_ext_auths_v x, iby_fndcpt_sys_eft_pf_b sp,
1853 iby_fndcpt_user_eft_pf_b up
1854 WHERE (x.trxn_extension_id = ci_extension_id)
1855 AND (DECODE(x.instrument_type,
1856 IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_BANKACCT,x.process_profile_code,
1857 NULL) = up.user_eft_profile_code(+)
1858 )
1859 AND (up.sys_eft_profile_code = sp.sys_eft_profile_code(+));
1860
1861 BEGIN
1862 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1863
1864 IF (c_extension%ISOPEN) THEN CLOSE c_extension; END IF;
1865 IF (c_auths%ISOPEN) THEN CLOSE c_auths; END IF;
1866
1867 IF NOT FND_API.Compatible_API_Call (l_api_version,
1868 p_api_version,
1869 l_module,
1870 G_PKG_NAME)
1871 THEN
1872 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1873 debug_level => FND_LOG.LEVEL_ERROR,
1874 module => G_DEBUG_MODULE || l_module);
1875 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1876 FND_MSG_PUB.Add;
1877 RAISE FND_API.G_EXC_ERROR;
1878 END IF;
1879
1880 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1881 FND_MSG_PUB.initialize;
1882 END IF;
1883 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1884
1885 SAVEPOINT Copy_Txn_Extension_Merge;
1886
1887 l_extension := p_trxn_attribs;
1888
1889 iby_debug_pub.add('checking payer context',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1890
1891 iby_debug_pub.add('party id =' || p_payer.Party_Id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1892 iby_debug_pub.add('account id =' || p_payer.Cust_Account_Id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1893 iby_debug_pub.add('account site use id =' || p_payer.Account_Site_Id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1894 iby_debug_pub.add('org id =' || p_payer.Org_Id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1895 iby_debug_pub.add('org type =' || p_payer.Org_Type,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1896
1897 IBY_FNDCPT_SETUP_PUB.Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1898 l_payer_level,l_payer_id,l_payer_attribs);
1899
1900 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1901 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1902 ELSE
1903 IF (l_extension.Order_Id IS NULL)
1904 OR (LENGTH(Get_Tangible_Id(l_extension.Originating_Application_Id,
1905 l_extension.Order_Id,
1906 l_extension.Trxn_Ref_Number1,
1907 l_extension.Trxn_Ref_Number2))
1908 > iby_bill_pkg.G_MAX_TANGIBLEID_LEN
1909 )
1910 THEN
1911 iby_debug_pub.add('order id invalid',iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1912 x_response.Result_Code := G_RC_INVALID_EXTENSION_ATTRIB;
1913 iby_fndcpt_common_pub.Prepare_Result
1914 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1915 RETURN;
1916 END IF;
1917
1918 IBY_FNDCPT_SETUP_PUB.Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1919 l_payer_level,l_payer_id,l_payer_attribs);
1920
1921 -- create external payer if necessary
1922 IF (l_payer_id IS NULL) THEN
1923 iby_debug_pub.add('setting payer attribs',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1924 IBY_FNDCPT_SETUP_PUB.Set_Payer_Attributes
1925 (1.0, FND_API.G_FALSE, FND_API.G_FALSE,
1926 x_return_status, x_msg_count, x_msg_data,
1927 p_payer, l_payer_attribs, l_payer_id, lx_result
1928 );
1929
1930 IF (lx_result.Result_Code <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS) THEN
1931 x_response := lx_result;
1932 RETURN;
1933 END IF;
1934 END IF;
1935
1936 l_val_instr := (NOT p_trxn_attribs.Copy_Instr_Assign_Id IS NULL);
1937
1938 FOR i IN p_entities.FIRST..p_entities.LAST LOOP
1939 iby_debug_pub.add('trxn entity id:=' || p_entities(i),
1940 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1941 iby_debug_pub.add('copy instr assignment:=' || p_trxn_attribs.Copy_Instr_Assign_Id,
1942 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1943
1944 OPEN c_extension(p_entities(i), p_payer, l_payer_level,
1945 p_payer_equivalency,
1946 p_trxn_attribs.Copy_Instr_Assign_Id);
1947 FETCH c_extension
1948 INTO lc_pmt_channel, lc_instr_assign_id,
1949 lc_sec_code, lc_sec_code_len,
1950 lc_va_flag, lc_va_code, lc_va_date, lc_encrypted,
1951 lc_instr_code_sec_segment_id;
1952 l_x_not_found := c_extension%NOTFOUND;
1953 CLOSE c_extension;
1954
1955 l_copy_count := Get_Tx_Extension_Copy_Count(p_entities(i));
1956
1957 l_pmt_channel := NVL(l_pmt_channel,lc_pmt_channel);
1958 l_instr_assign_id := NVL(l_instr_assign_id,lc_instr_assign_id);
1959
1960 -- validate new instrument assignment here as payment channel
1961 -- is required
1962
1963 IF (l_val_instr) THEN
1964 IF (NOT Payer_InstrAssignment_Valid(p_payer,l_payer_level,
1965 p_payer_equivalency,
1966 l_pmt_channel,
1967 p_trxn_attribs.Copy_Instr_Assign_Id,
1968 TRUE
1969 )
1970 )
1971 THEN
1972 iby_debug_pub.add('payer instr assignment invalid ' ||
1973 p_trxn_attribs.Copy_Instr_Assign_Id ||
1974 ' for channel ' || l_pmt_channel,
1975 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1976 x_response.Result_Code :=
1977 IBY_FNDCPT_SETUP_PUB.G_RC_INVALID_INSTR_ASSIGN;
1978 iby_fndcpt_common_pub.Prepare_Result
1979 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1980 RETURN;
1981 END IF;
1982 l_instr_assign_id := p_trxn_attribs.Copy_Instr_Assign_Id;
1983 l_val_instr := FALSE;
1984 END IF;
1985 IF (l_x_not_found) THEN
1986 iby_debug_pub.add('extension invalid for payer, or non-existant',
1987 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1988 x_response.Result_Code := G_RC_INVALID_EXTENSION_ID;
1989 iby_fndcpt_common_pub.Prepare_Result
1990 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1991 RETURN;
1992 ELSIF ( (l_pmt_channel<>lc_pmt_channel)
1993 -- instr assignment may change;
1994 --OR (l_instr_assign_id<>lc_instr_assign_id)
1995 )
1996 THEN
1997 iby_debug_pub.add('incompatible pmt chanenel ' || l_pmt_channel,
1998 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
1999 x_response.Result_Code := G_RC_INCMP_EXTENSION_GROUP;
2000 iby_fndcpt_common_pub.Prepare_Result
2001 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2002 RETURN;
2003 /*
2004 ELSIF (l_copy_count>0) THEN
2005 x_response.Result_Code := G_RC_DUP_EXTENSION_COPY;
2006 iby_fndcpt_common_pub.Prepare_Result
2007 (x_return_status,x_msg_count,x_msg_data,x_response);
2008 RETURN;
2009 */
2010 ELSE
2011 -- inherit from the first extension with non-NULL values
2012 l_extension.Instrument_Security_Code :=
2013 NVL(l_extension.Instrument_Security_Code,lc_sec_code);
2014 -- We don't expect the upstream product to pass a segment id.
2015 -- So, we will inherit the segment id if the security code is passed
2016 -- as NULL or 'XXX' or 'XXXX'
2017 IF ((l_extension.Instrument_Security_Code IS NULL) OR
2018 (l_extension.Instrument_Security_Code = 'XXX') OR
2019 (l_extension.Instrument_Security_Code = 'XXXX'))
2020 THEN
2021 l_segment_id := lc_instr_code_sec_segment_id;
2022 END IF;
2023 l_extension.VoiceAuth_Flag :=
2024 NVL(l_extension.VoiceAuth_Flag,lc_va_flag);
2025 l_extension.VoiceAuth_Code :=
2026 NVL(l_extension.VoiceAuth_Code,lc_va_code);
2027 l_extension.VoiceAuth_Date :=
2028 NVL(l_extension.VoiceAuth_Date,lc_va_date);
2029
2030 -- new security codes always passed as clear-text; old
2031 -- ones will have the extensions existing encryptions state
2032 --
2033 IF (NOT p_trxn_attribs.Instrument_Security_Code IS NULL) THEN
2034 lc_encrypted := 'N';
2035 lc_sec_code_len := length(p_trxn_attribs.Instrument_Security_Code);
2036 END IF;
2037 END IF;
2038
2039 IF (p_entities.count > 1) THEN
2040 l_persist_auth := NULL;
2041
2042 OPEN c_auths(p_entities(i));
2043 FETCH c_auths INTO l_persist_auth;
2044 CLOSE c_auths;
2045
2046 IF (l_persist_auth = 'Y') THEN
2047 iby_debug_pub.add('persistent auth; cannot do 1-to-many copy',
2048 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
2049 x_response.Result_Code := G_RC_INCMP_EXTENSION_GROUP;
2050 iby_fndcpt_common_pub.Prepare_Result
2051 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2052 RETURN;
2053 END IF;
2054 END IF;
2055
2056 END LOOP;
2057
2058 IF (NVL(lc_sec_code_len,0)>0)
2059 AND (l_extension.Instrument_Security_Code IS NULL)
2060 THEN
2061 l_consumed_cvv2 := TRUE;
2062 ELSE
2063 l_consumed_cvv2 := FALSE;
2064 END IF;
2065
2066 IF (NOT Extension_Valid(l_pmt_channel,l_extension,FALSE,
2067 l_consumed_cvv2)) THEN
2068 x_response.Result_Code := G_RC_INVALID_EXTENSION_ATTRIB;
2069 ELSE
2070 SELECT iby_fndcpt_tx_extensions_s.NEXTVAL INTO x_entity_id FROM DUAL;
2071 INSERT INTO iby_fndcpt_tx_extensions
2072 (trxn_extension_id, payment_channel_code,
2073 ext_payer_id, instr_assignment_id,
2074 origin_application_id, instrument_security_code,
2075 instr_sec_code_length, encrypted, instr_code_sec_segment_id,
2076 voice_authorization_flag, voice_authorization_date,
2077 voice_authorization_code, order_id, po_number,
2078 po_line_number, trxn_ref_number1, trxn_ref_number2, additional_info,
2079 created_by, creation_date, last_updated_by, last_update_date,
2080 last_update_login, object_version_number
2081 )
2082 VALUES
2083 (
2084 x_entity_id, l_pmt_channel, l_payer_id, l_instr_assign_id,
2085 l_extension.Originating_Application_Id,
2086 l_extension.Instrument_Security_Code,
2087 lc_sec_code_len, lc_encrypted, l_segment_id,
2088 l_extension.VoiceAuth_Flag,
2089 l_extension.VoiceAuth_Date, l_extension.VoiceAuth_Code,
2090 l_extension.Order_Id, l_extension.PO_Number,
2091 l_extension.PO_Line_Number,
2092 l_extension.Trxn_Ref_Number1, l_extension.Trxn_Ref_Number2,
2093 l_extension.Additional_Info,
2094 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
2095 fnd_global.login_id, 1
2096 );
2097
2098 FOR i IN p_entities.FIRST..p_entities.LAST LOOP
2099
2100 -- associate the merged copy with all its sources
2101 INSERT INTO iby_fndcpt_tx_xe_copies
2102 (source_trxn_extension_id, copy_trxn_extension_id,
2103 created_by, creation_date, last_updated_by, last_update_date,
2104 last_update_login, object_version_number
2105 )
2106 VALUES
2107 (p_entities(i), x_entity_id,
2108 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
2109 fnd_global.login_id, 1
2110 );
2111
2112 -- only the first copy receives an operations for the extension
2113 IF (l_copy_count < 1) THEN
2114 -- note this assumes all operations have already been
2115 -- propogated to the immediate copy source from the extensions
2116 -- which THEY copied
2117 --
2118 INSERT INTO iby_fndcpt_tx_operations
2119 (trxn_extension_id, transactionid,
2120 created_by, creation_date, last_updated_by, last_update_date,
2121 last_update_login, object_version_number
2122 )
2123 -- instrument may change during copy
2124 SELECT
2125 x_entity_id, transactionid,
2126 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
2127 fnd_global.login_id, 1
2128 FROM iby_fndcpt_tx_operations
2129 WHERE trxn_extension_id = p_entities(i);
2130 END IF;
2131 END LOOP;
2132
2133 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
2134 END IF;
2135
2136 END IF;
2137
2138 iby_fndcpt_common_pub.Prepare_Result
2139 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2140
2141 IF FND_API.To_Boolean(p_commit) THEN
2142 COMMIT;
2143 END IF;
2144
2145 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
2146 EXCEPTION
2147
2148 WHEN FND_API.G_EXC_ERROR THEN
2149 ROLLBACK TO Copy_Txn_Extension_Merge;
2150 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2151 debug_level => FND_LOG.LEVEL_ERROR,
2152 module => G_DEBUG_MODULE || l_module);
2153 x_return_status := FND_API.G_RET_STS_ERROR;
2154 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2155 p_data => x_msg_data
2156 );
2157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2158 ROLLBACK TO Copy_Txn_Extension_Merge;
2159 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2160 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2161 module => G_DEBUG_MODULE || l_module);
2162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2163 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2164 p_data => x_msg_data
2165 );
2166
2167 WHEN OTHERS THEN
2168 ROLLBACK TO Copy_Txn_Extension_Merge;
2169
2170 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2171 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2172 module => G_DEBUG_MODULE || l_module);
2173
2174 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2175
2176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2177 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2178 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2179 END IF;
2180
2181 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2182 p_data => x_msg_data
2183 );
2184
2185 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
2186 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2187 module => G_DEBUG_MODULE || l_module);
2188 iby_debug_pub.add(debug_msg => 'Exit Exception',
2189 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2190 module => G_DEBUG_MODULE || l_module);
2191
2192 END Copy_Transaction_Extension;
2193
2194 --
2195 -- This is a Private utility procedure.
2196 -- Used to securely wipe out the CVV after
2197 -- the first authorization.
2198 -- As the PABP guidelines, a secure wipeout of data could be essentially achieved
2199 -- by updating the column with a randomly generated value, issuing a commit, and
2200 -- then updating the value with NULL (or deleting the row) and then issuing another
2201 -- commit.
2202 -- We achieve this through the following autonomous transaction block
2203 --
2204 PROCEDURE Consume_CVV
2205 (
2206 p_trxn_extn_id IN NUMBER,
2207 p_segment_id IN iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE
2208 )
2209 IS PRAGMA AUTONOMOUS_TRANSACTION;
2210 l_random_val NUMBER;
2211 BEGIN
2212
2213 -- If the segment Id is NULL, it implies that the CVV value wasn't encrypted
2214 -- Just securely wipe out the CVV column in this case.
2215
2216 IF (p_segment_id IS NULL) THEN
2217 SELECT trunc(DBMS_RANDOM.VALUE(1000,9999)) INTO l_random_val FROM dual;
2218 UPDATE iby_fndcpt_tx_extensions
2219 -- Update the sec_code with a randomly generated number
2220 SET instrument_security_code = l_random_val,
2221 --instr_sec_code_length = NULL,
2222 encrypted = 'N',
2223 last_updated_by = fnd_global.user_id,
2224 last_update_date = SYSDATE,
2225 last_update_login = fnd_global.login_id,
2226 object_version_number = object_version_number + 1
2227 WHERE trxn_extension_id = p_trxn_extn_id;
2228 COMMIT;
2229
2230 -- Again update the sec_code column with NULL value.
2231 UPDATE iby_fndcpt_tx_extensions
2232 SET instrument_security_code = NULL,
2233 --instr_sec_code_length = NULL,
2234 encrypted = 'N',
2235 last_updated_by = fnd_global.user_id,
2236 last_update_date = SYSDATE,
2237 last_update_login = fnd_global.login_id,
2238 object_version_number = object_version_number + 1
2239 WHERE trxn_extension_id = p_trxn_extn_id;
2240 COMMIT;
2241
2242 ELSE -- The CVV value is encrypted
2243
2244 -- Need not do any secure wipeout for the instrument_security_code
2245 -- column here, since this just contains the truncated value
2246 -- Just Nulling out the column is enough.
2247
2248 UPDATE iby_fndcpt_tx_extensions
2249 SET instrument_security_code = NULL,
2250 --instr_sec_code_length = NULL,
2251 encrypted = 'N',
2252 last_updated_by = fnd_global.user_id,
2253 last_update_date = SYSDATE,
2254 last_update_login = fnd_global.login_id,
2255 object_version_number = object_version_number + 1
2256 WHERE trxn_extension_id = p_trxn_extn_id;
2257
2258 -- Delete the Security Code reference from the
2259 -- IBY_SECURITY_SEGMENTS table.
2260
2261 UPDATE iby_security_segments
2262 SET segment_cipher_text = RAWTOHEX(fnd_crypto.randombytes(32))
2263 WHERE sec_segment_id = p_segment_id;
2264 COMMIT;
2265
2266 DELETE iby_security_segments
2267 WHERE sec_segment_id = p_segment_id;
2268 COMMIT;
2269
2270 END IF;
2271
2272 END Consume_CVV;
2273
2274 PROCEDURE Create_Authorization
2275 (
2276 p_api_version IN NUMBER,
2277 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2278 x_return_status OUT NOCOPY VARCHAR2,
2279 x_msg_count OUT NOCOPY NUMBER,
2280 x_msg_data OUT NOCOPY VARCHAR2,
2281 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
2282 p_payer_equivalency IN VARCHAR2 :=
2283 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
2284 p_payee IN PayeeContext_rec_type,
2285 p_trxn_entity_id IN NUMBER,
2286 p_auth_attribs IN AuthAttribs_rec_type,
2287 p_amount IN Amount_rec_type,
2288 x_auth_result OUT NOCOPY AuthResult_rec_type,
2289 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2290 )
2291 IS
2292 l_api_version CONSTANT NUMBER := 1.0;
2293 l_module CONSTANT VARCHAR2(30) := 'Create_Authorization';
2294 l_prev_msg_count NUMBER;
2295
2296 l_payer_level VARCHAR2(30);
2297 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
2298 l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
2299
2300 l_copy_count NUMBER;
2301 l_auth_flag iby_trxn_extensions_v.authorized_flag%TYPE;
2302 l_instr_auth_flag iby_trxn_extensions_v.authorized_flag%TYPE;
2303 l_single_use iby_fndcpt_payer_assgn_instr_v.card_single_use_flag%TYPE;
2304
2305 l_ecapp_id NUMBER;
2306 l_app_short_name fnd_application.application_short_name%TYPE;
2307 l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
2308 l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
2309 l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
2310 l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2311 l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2312 l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
2313 l_payer IBY_PAYMENT_ADAPTER_PUB.Payer_rec_type;
2314 l_tangible IBY_PAYMENT_ADAPTER_PUB.Tangible_rec_type;
2315 l_pmt_instr IBY_PAYMENT_ADAPTER_PUB.PmtInstr_rec_type;
2316 l_pmt_trxn IBY_PAYMENT_ADAPTER_PUB.PmtReqTrxn_rec_type;
2317 l_riskinfo IBY_PAYMENT_ADAPTER_PUB.RiskInfo_rec_type;
2318 l_reqresp IBY_PAYMENT_ADAPTER_PUB.ReqResp_rec_type;
2319 l_return_status VARCHAR2(1);
2320 l_msg_count NUMBER;
2321 l_msg_data VARCHAR2(300);
2322 l_fail_msg VARCHAR2(500);
2323 l_op_count NUMBER;
2324
2325 l_ext_not_found BOOLEAN;
2326
2327 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
2328
2329 CURSOR c_extension
2330 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
2331 ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
2332 ci_payer_level IN VARCHAR2,
2333 ci_payer_equiv IN VARCHAR2
2334 )
2335 IS
2336 SELECT NVL(i.instrument_type,pc.instrument_type),
2337 NVL(i.instrument_id,0),
2338 x.origin_application_id, a.application_short_name,
2339 x.order_id, x.trxn_ref_number1, x.trxn_ref_number2,
2340 x.instrument_security_code, x.instr_code_sec_segment_id, x.encrypted,
2341 x.po_number, x.voice_authorization_flag, x.voice_authorization_code,
2342 x.voice_authorization_date, i.card_single_use_flag,
2343 NVL(x.instr_assignment_id,0), x.payment_channel_code
2344 FROM iby_fndcpt_tx_extensions x, iby_fndcpt_payer_assgn_instr_v i,
2345 iby_external_payers_all p, fnd_application a,
2346 iby_fndcpt_pmt_chnnls_b pc
2347 WHERE (x.instr_assignment_id = i.instr_assignment_id(+))
2348 AND (x.payment_channel_code = pc.payment_channel_code)
2349 AND (x.origin_application_id = a.application_id)
2350 -- can assume this assignment is for funds capture
2351 AND (x.ext_payer_id = p.ext_payer_id)
2352 AND (x.trxn_extension_id = ci_extension_id)
2353 AND (p.party_id = ci_payer.Party_Id)
2354 AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
2355 (ci_payer.org_type, ci_payer.org_id,
2356 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
2357 ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
2358 p.cust_account_id,p.acct_site_use_id) = 'T');
2359
2360 CURSOR c_auth
2361 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
2362 IS
2363 SELECT authorized_flag
2364 FROM iby_trxn_extensions_v
2365 WHERE (trxn_extension_id = ci_extension_id);
2366
2367 CURSOR c_instr_extensions
2368 (ci_instr_type IN iby_trxn_extensions_v.instrument_type%TYPE,
2369 ci_instr_id IN iby_trxn_extensions_v.instrument_id%TYPE,
2370 ci_trxn_x_id IN iby_trxn_extensions_v.trxn_extension_id%TYPE
2371 )
2372 IS
2373 SELECT NVL(authorized_flag,'N')
2374 FROM iby_trxn_extensions_v
2375 WHERE (instrument_id = ci_instr_id)
2376 AND (instrument_type = ci_instr_type)
2377 AND (trxn_extension_id <> ci_trxn_x_id);
2378
2379 CURSOR c_operation_count
2380 (ci_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
2381 ci_trxn_id IN iby_trxn_summaries_all.transactionid%TYPE)
2382 IS
2383 SELECT count(1)
2384 FROM iby_fndcpt_tx_operations o
2385 WHERE o.transactionid = ci_trxn_id
2386 AND o.trxn_extension_id = ci_trxn_extension_id;
2387
2388 BEGIN
2389 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
2390
2391 IF (c_extension%ISOPEN) THEN CLOSE c_extension; END IF;
2392 IF (c_auth%ISOPEN) THEN CLOSE c_auth; END IF;
2393 IF (c_instr_extensions%ISOPEN) THEN CLOSE c_instr_extensions; END IF;
2394 IF (c_operation_count%ISOPEN) THEN CLOSE c_operation_count; END IF;
2395
2396 IF NOT FND_API.Compatible_API_Call (l_api_version,
2397 p_api_version,
2398 l_module,
2399 G_PKG_NAME)
2400 THEN
2401 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2402 debug_level => FND_LOG.LEVEL_ERROR,
2403 module => G_DEBUG_MODULE || l_module);
2404 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2405 FND_MSG_PUB.Add;
2406 RAISE FND_API.G_EXC_ERROR;
2407 END IF;
2408
2409 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2410 FND_MSG_PUB.initialize;
2411 END IF;
2412 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
2413
2414 iby_debug_pub.add('checking payer context',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2415 IBY_FNDCPT_SETUP_PUB.Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
2416 l_payer_level,l_payer_id,l_payer_attribs);
2417
2418 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
2419 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
2420 ELSE
2421 -- verify transaction entity is for a payer equivalent to the
2422 -- given one
2423 OPEN c_extension(p_trxn_entity_id,p_payer,l_payer_level,p_payer_equivalency);
2424 FETCH c_extension INTO l_pmt_instr.PmtInstr_Type,
2425 l_pmt_instr.PmtInstr_Id, l_ecapp_id, l_app_short_name,
2426 l_order_id, l_trxn_ref1, l_trxn_ref2,
2427 l_pmt_trxn.CVV2, l_code_segment_id, l_encrypted,
2428 l_pmt_trxn.PONum, l_pmt_trxn.VoiceAuthFlag,
2429 l_pmt_trxn.AuthCode, l_pmt_trxn.DateOfVoiceAuthorization,
2430 l_single_use,
2431 l_pmt_instr.Pmtinstr_assignment_id,
2432 l_pmt_trxn.payment_channel_code;
2433 l_ext_not_found := c_extension%NOTFOUND;
2434 CLOSE c_extension;
2435
2436 iby_debug_pub.add('channel code:=' || l_pmt_trxn.payment_channel_code,
2437 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2438 iby_debug_pub.add('instrument type:=' || l_pmt_instr.pmtinstr_type,
2439 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2440
2441 IF (NOT l_ext_not_found) THEN
2442
2443 -- map the records
2444 l_payee.Payee_Id := Get_Internal_Payee(p_payee);
2445 -- create on the fly??
2446 l_payer.Party_Id := p_payer.Party_Id;
2447
2448 l_copy_count := Get_Tx_Extension_Copy_Count(p_trxn_entity_id);
2449
2450 IF (l_payee.Payee_Id IS NULL) THEN
2451 x_response.Result_Code := G_RC_INVALID_PAYEE;
2452
2453 iby_fndcpt_common_pub.Prepare_Result
2454 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2455 RETURN;
2456 -- cannot do operations on a trxn entity already copied
2457 ELSIF (l_copy_count>0) THEN
2458 iby_debug_pub.add('extension has been copied ' || l_copy_count
2459 || ' times; cannot auth',iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
2460 x_response.Result_Code := G_RC_EXTENSION_IMMUTABLE;
2461 iby_fndcpt_common_pub.Prepare_Result
2462 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2463 RETURN;
2464 END IF;
2465
2466 --Changing the function here to generate new tangible_id
2467 l_tangible.Tangible_Id :=
2468 Get_Tangible_Id(l_app_short_name,p_trxn_entity_id);
2469 --l_tangible.Tangible_Id :=
2470 -- Get_Tangible_Id(l_app_short_name,l_order_id,l_trxn_ref1,l_trxn_ref2);
2471
2472 IF (l_pmt_instr.PmtInstr_Type =
2473 IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_BANKACCT)
2474 THEN
2475 l_pmt_trxn.Auth_Type := IBY_PAYMENT_ADAPTER_PUB.G_AUTHTYPE_VERIFY;
2476 ELSE
2477 l_pmt_trxn.Auth_Type := IBY_PAYMENT_ADAPTER_PUB.G_AUTHTYPE_AUTHONLY;
2478 END IF;
2479
2480 l_tangible.Tangible_Amount := p_amount.Value;
2481 l_tangible.Currency_Code := p_amount.Currency_Code;
2482 l_tangible.Memo := p_auth_attribs.Memo;
2483 l_tangible.OrderMedium := p_auth_attribs.Order_Medium;
2484
2485 l_pmt_trxn.Org_Id := p_payee.Org_Id;
2486 l_pmt_trxn.TaxAmount := p_auth_attribs.Tax_Amount.Value;
2487 l_pmt_trxn.ShipFromZip := p_auth_attribs.ShipFrom_PostalCode;
2488 l_pmt_trxn.ShipToZip := p_auth_attribs.ShipTo_PostalCode;
2489 l_pmt_trxn.Payment_Factor_Flag := p_auth_attribs.Payment_Factor_Flag;
2490
2491 -- ciphertext; get clear-text value in the engine
2492 IF (l_encrypted = 'Y') THEN
2493 l_pmt_trxn.CVV2 := NULL;
2494 l_pmt_trxn.Trxn_Extension_Id := p_trxn_entity_id;
2495 END IF;
2496
2497 -- cannot use a single use instrument which already has
2498 -- an authorization
2499 IF (l_single_use = 'Y') THEN
2500 OPEN c_instr_extensions(l_pmt_instr.PmtInstr_Type,
2501 l_pmt_instr.PmtInstr_Id, p_trxn_entity_id);
2502 FETCH c_instr_extensions INTO l_instr_auth_flag;
2503 CLOSE c_instr_extensions;
2504 IF (NVL(l_instr_auth_flag,'N') = 'Y') THEN
2505 iby_debug_pub.add('single use instrument cannot be reused',
2506 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
2507 x_response.Result_Code := IBY_FNDCPT_SETUP_PUB.G_RC_INVALID_INSTRUMENT;
2508 iby_fndcpt_common_pub.Prepare_Result
2509 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2510 RETURN;
2511 END IF;
2512 END IF;
2513
2514 OPEN c_auth(p_trxn_entity_id);
2515 FETCH c_auth INTO l_auth_flag;
2516 IF (l_auth_flag = 'Y') THEN
2517 iby_debug_pub.add('extension already authorized',
2518 iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
2519 x_response.Result_Code := G_RC_DUPLICATE_AUTHORIZATION;
2520 ELSE
2521 IF (p_auth_attribs.RiskEval_Enable_Flag = 'Y') THEN
2522 l_pmt_trxn.AnalyzeRisk := 'TRUE';
2523 END IF;
2524
2525 iby_debug_pub.add('send auth',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2526 IBY_PAYMENT_ADAPTER_PUB.OraPmtReq
2527 (1.0,
2528 p_init_msg_list,
2529 FND_API.G_FALSE,
2530 FND_API.G_VALID_LEVEL_FULL,
2531 l_ecapp_id,
2532 l_payee,
2533 l_payer,
2534 l_pmt_instr,
2535 l_tangible,
2536 l_pmt_trxn,
2537 l_return_status,
2538 l_msg_count,
2539 l_msg_data,
2540 l_reqresp
2541 );
2542
2543 iby_debug_pub.add('status :=' || l_return_status,
2544 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2545 iby_debug_pub.add('auth status :=' ||
2546 to_char(l_reqresp.Response.Status),
2547 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2548 iby_debug_pub.add('auth engine code :=' ||
2549 to_char(l_reqresp.Response.ErrCode),
2550 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2551 iby_debug_pub.add('auth engine msg :=' ||
2552 to_char(l_reqresp.Response.ErrMessage),
2553 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2554
2555 iby_debug_pub.add('payment system code :=' ||
2556 to_char(l_reqresp.BEPErrCode),
2557 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2558 iby_debug_pub.add('payment system msg :=' ||
2559 to_char(l_reqresp.BEPErrMessage),
2560 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2561
2562 iby_debug_pub.add('trxn id :=' ||
2563 to_char(l_reqresp.Trxn_ID),
2564 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2565
2566 IF (p_auth_attribs.RiskEval_Enable_Flag = 'Y') THEN
2567 x_auth_result.Risk_Result.Risk_Score :=
2568 l_reqresp.RiskResponse.Risk_Score;
2569 x_auth_result.Risk_Result.Risk_Threshold_Val :=
2570 l_reqresp.RiskResponse.Risk_Threshold_Val;
2571 IF (l_reqresp.RiskResponse.Risky_Flag = 'YES') THEN
2572 x_auth_result.Risk_Result.Risky_Flag := 'Y';
2573 ELSE
2574 x_auth_result.Risk_Result.Risky_Flag := 'N';
2575 END IF;
2576 END IF;
2577
2578 -- consume the security code
2579 -- UPDATE iby_fndcpt_tx_extensions
2580 -- SET instrument_security_code = NULL,
2581 --instr_sec_code_length = NULL,
2582 -- encrypted = 'N',
2583 -- last_updated_by = fnd_global.user_id,
2584 -- last_update_date = SYSDATE,
2585 -- last_update_login = fnd_global.login_id,
2586 -- object_version_number = object_version_number + 1
2587 -- WHERE trxn_extension_id = p_trxn_entity_id;
2588
2589 -- DELETE iby_security_segments
2590 -- WHERE sec_segment_id = l_code_segment_id;
2591
2592 -- As per PABP guidelines, the cvv value should be consumed
2593 -- securely. i.e, first update with a random value, do a commit
2594 -- then update with null and issue another commit.
2595 -- This is handled through the below procedure call.
2596 Consume_CVV(p_trxn_entity_id, l_code_segment_id);
2597
2598 IF (NOT l_reqresp.Trxn_Id IS NULL) THEN
2599
2600 -- populate the dirdeb_instruction_code column
2601 -- for settlement
2602 BEGIN
2603 IBY_FNDCPT_SETUP_PUB.Get_Trxn_Payer_Attributes(p_payer,p_payer_equivalency, l_payer_attribs);
2604
2605 UPDATE iby_trxn_summaries_all
2606 SET dirdeb_instruction_code = l_payer_attribs.DirectDebit_BankInstruction
2607 WHERE transactionid = l_reqresp.Trxn_Id;
2608
2609 iby_debug_pub.add('Set DirectDebit_BankInstruction for trxn',
2610 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2611
2612 EXCEPTION
2613 WHEN OTHERS THEN
2614 iby_debug_pub.add('Unable to retrieve/set payer attribs for trxn',
2615 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2616
2617 END;
2618
2619 -- Fix for bug# 7377455. Stamp the tangibleid on the PSON column of
2620 -- IBY_FNDCPT_TX_EXTENSIONS table
2621 iby_debug_pub.add( 'Stamping the PSON on the extension as '||
2622 l_tangible.Tangible_Id,
2623 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2624
2625 UPDATE iby_fndcpt_tx_extensions
2626 SET payment_system_order_number = l_tangible.Tangible_Id
2627 WHERE trxn_extension_id = p_trxn_entity_id;
2628
2629 -- Fix for bug# 7530578. Stamp the initiator transaction extension id
2630 -- on the corresponding record in iby_trxn_summaries_all
2631 iby_debug_pub.add( 'Stamping '||p_trxn_entity_id ||' as the initiator_extension_id'
2632 ||'on the auth record', iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2633
2634 UPDATE iby_trxn_summaries_all
2635 SET initiator_extension_id = p_trxn_entity_id
2636 WHERE transactionid = l_reqresp.Trxn_Id
2637 AND reqtype = 'ORAPMTREQ';
2638
2639 iby_debug_pub.add('creating extension operation record for=' ||
2640 p_trxn_entity_id,
2641 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2642
2643 -- check to see if the operation is already recorded
2644 OPEN c_operation_count(p_trxn_entity_id,l_reqresp.Trxn_Id);
2645 FETCH c_operation_count INTO l_op_count;
2646 CLOSE c_operation_count;
2647 l_op_count := NVL(l_op_count,0);
2648
2649 IF ( l_op_count > 0 ) THEN
2650 iby_debug_pub.add( 'operation already recorded',
2651 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2652 ELSE
2653 INSERT INTO iby_fndcpt_tx_operations
2654 (trxn_extension_id, transactionid,
2655 created_by, creation_date, last_updated_by, last_update_date,
2656 last_update_login, object_version_number
2657 )
2658 VALUES
2659 (p_trxn_entity_id, l_reqresp.Trxn_Id,
2660 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
2661 fnd_global.login_id, 1
2662 );
2663
2664 --
2665 -- back-propagate the authorization
2666 --
2667 INSERT INTO iby_fndcpt_tx_operations
2668 (trxn_extension_id, transactionid,
2669 created_by, creation_date, last_updated_by, last_update_date,
2670 last_update_login, object_version_number
2671 )
2672 SELECT source_trxn_extension_id, l_reqresp.Trxn_Id,
2673 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
2674 fnd_global.login_id, 1
2675 FROM iby_fndcpt_tx_xe_copies
2676 START WITH copy_trxn_extension_id = p_trxn_entity_id
2677 CONNECT BY PRIOR source_trxn_extension_id = copy_trxn_extension_id;
2678
2679 iby_debug_pub.add('back-propogated rows:='||SQL%ROWCOUNT,
2680 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2681
2682 --
2683 -- forward propogate the authorization
2684 --
2685 INSERT INTO iby_fndcpt_tx_operations
2686 (trxn_extension_id, transactionid,
2687 created_by, creation_date, last_updated_by, last_update_date,
2688 last_update_login, object_version_number
2689 )
2690 SELECT copy_trxn_extension_id, l_reqresp.Trxn_Id,
2691 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
2692 fnd_global.login_id, 1
2693 FROM iby_fndcpt_tx_xe_copies
2694 START WITH source_trxn_extension_id = p_trxn_entity_id
2695 CONNECT BY source_trxn_extension_id = PRIOR copy_trxn_extension_id;
2696
2697 iby_debug_pub.add('forward-propogated rows:='||SQL%ROWCOUNT,
2698 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2699
2700 END IF;
2701
2702 x_auth_result.Auth_Id := l_reqresp.Trxn_Id;
2703 x_auth_result.Auth_Date := l_reqresp.Trxn_Date;
2704 x_auth_result.Auth_Code := l_reqresp.Authcode;
2705 x_auth_result.AVS_Code := l_reqresp.AVSCode;
2706 x_auth_result.Instr_SecCode_Check := l_reqresp.CVV2Result;
2707 x_auth_result.PaymentSys_Code := l_reqresp.BEPErrCode;
2708 x_auth_result.PaymentSys_Msg := l_reqresp.BEPErrMessage;
2709 --x_auth_result.Risk_Result;
2710
2711 END IF;
2712
2713 --COMMIT;
2714
2715 IF (l_reqresp.Response.Status = 0) THEN
2716 x_response.Result_Code := G_RC_AUTH_SUCCESS;
2717 ELSE
2718 --x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR);
2719
2720 -- check if the result code is seeded in the result definitions
2721 -- table
2722 --
2723 IF (IBY_FNDCPT_COMMON_PUB.Get_Result_Category(x_response.Result_Code,iby_payment_adapter_pub.G_INTERFACE_CODE) IS NULL)
2724 THEN
2725 x_response.Result_Code := 'COMMUNICATION_ERROR';
2726 --IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR;
2727 END IF;
2728
2729 IF ( (NOT l_reqresp.Response.ErrMessage IS NULL)
2730 OR (NOT l_reqresp.Response.ErrCode IS NULL) )
2731 THEN
2732 l_reqresp.Response.ErrMessage :=
2733 l_reqresp.Response.ErrMessage || ' (' ||
2734 l_reqresp.Response.ErrCode || ')';
2735 END IF;
2736
2737 iby_fndcpt_common_pub.Prepare_Result(
2738 iby_payment_adapter_pub.G_INTERFACE_CODE,
2739 l_reqresp.Response.ErrMessage,
2740 l_prev_msg_count,
2741 x_return_status,
2742 x_msg_count,
2743 x_msg_data,
2744 x_response
2745 );
2746
2747 RETURN;
2748 END IF;
2749
2750 END IF;
2751 ELSE
2752 x_response.Result_Code := G_RC_INVALID_EXTENSION_ID;
2753
2754 l_fail_msg := Get_Extension_Auth_Fail(p_trxn_entity_id,p_payer);
2755 iby_debug_pub.add('fail msg code:=' || l_fail_msg,
2756 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2757 IF (NOT l_fail_msg IS NULL) THEN
2758 FND_MESSAGE.SET_NAME('IBY',l_fail_msg);
2759 l_fail_msg := FND_MESSAGE.GET();
2760
2761 iby_fndcpt_common_pub.Prepare_Result
2762 (iby_payment_adapter_pub.G_INTERFACE_CODE,
2763 l_fail_msg,l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,
2764 x_response);
2765
2766 RETURN;
2767 END IF;
2768 END IF;
2769 END IF;
2770
2771 iby_fndcpt_common_pub.Prepare_Result
2772 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2773
2774 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
2775 EXCEPTION
2776
2777 WHEN FND_API.G_EXC_ERROR THEN
2778
2779 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2780 debug_level => FND_LOG.LEVEL_ERROR,
2781 module => G_DEBUG_MODULE || l_module);
2782 x_return_status := FND_API.G_RET_STS_ERROR;
2783 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2784 p_data => x_msg_data
2785 );
2786 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2787
2788 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2789 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2790 module => G_DEBUG_MODULE || l_module);
2791 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2792 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2793 p_data => x_msg_data
2794 );
2795
2796 WHEN OTHERS THEN
2797
2798 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2799 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2800 module => G_DEBUG_MODULE || l_module);
2801
2802 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2803
2804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2805 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2806 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2807 END IF;
2808
2809 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2810 p_data => x_msg_data
2811 );
2812
2813 END Create_Authorization;
2814
2815
2816
2817
2818 PROCEDURE Get_Authorization
2819 (
2820 p_api_version IN NUMBER,
2821 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2822 x_return_status OUT NOCOPY VARCHAR2,
2823 x_msg_count OUT NOCOPY NUMBER,
2824 x_msg_data OUT NOCOPY VARCHAR2,
2825 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
2826 p_trxn_entity_id IN NUMBER,
2827 x_auth_result OUT NOCOPY AuthResult_rec_type,
2828 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2829 )
2830 IS
2831 l_api_version CONSTANT NUMBER := 1.0;
2832 l_module CONSTANT VARCHAR2(30) := 'Get_Authorization';
2833 l_prev_msg_count NUMBER;
2834
2835 CURSOR c_auth
2836 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
2837 IS
2838 SELECT authorization_id, authorization_date, authorization_code,
2839 avs_code, instr_sec_code_check, pmt_sys_err_code, pmt_sys_err_msg
2840 FROM iby_trxn_ext_auths_v
2841 WHERE (trxn_extension_id = ci_extension_id)
2842 ORDER BY
2843 DECODE(authorization_result_code, 'AUTH_SUCCESS',10,
2844 'AUTH_PENDING',5, 0) DESC;
2845 BEGIN
2846
2847 IF (c_auth%ISOPEN) THEN CLOSE c_auth; END IF;
2848
2849 IF NOT FND_API.Compatible_API_Call (l_api_version,
2850 p_api_version,
2851 l_module,
2852 G_PKG_NAME)
2853 THEN
2854 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2855 debug_level => FND_LOG.LEVEL_ERROR,
2856 module => G_DEBUG_MODULE || l_module);
2857 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2858 FND_MSG_PUB.Add;
2859 RAISE FND_API.G_EXC_ERROR;
2860 END IF;
2861
2862 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2863 FND_MSG_PUB.initialize;
2864 END IF;
2865 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
2866
2867 OPEN c_auth(p_trxn_entity_id);
2868 FETCH c_auth INTO
2869 x_auth_result.Auth_Id, x_auth_result.Auth_Date,
2870 x_auth_result.Auth_Code, x_auth_result.AVS_Code,
2871 x_auth_result.Instr_SecCode_Check, x_auth_result.PaymentSys_Code,
2872 x_auth_result.PaymentSys_Msg;
2873 IF (c_auth%NOTFOUND) THEN
2874 x_response.Result_Code := G_RC_INVALID_EXTENSION_ID;
2875 ELSE
2876 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
2877 END IF;
2878
2879 iby_fndcpt_common_pub.Prepare_Result
2880 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2881
2882 EXCEPTION
2883
2884 WHEN FND_API.G_EXC_ERROR THEN
2885
2886 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2887 debug_level => FND_LOG.LEVEL_ERROR,
2888 module => G_DEBUG_MODULE || l_module);
2889 x_return_status := FND_API.G_RET_STS_ERROR;
2890 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2891 p_data => x_msg_data
2892 );
2893 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2894
2895 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2896 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2897 module => G_DEBUG_MODULE || l_module);
2898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2899 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2900 p_data => x_msg_data
2901 );
2902
2903 WHEN OTHERS THEN
2904
2905 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2906 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2907 module => G_DEBUG_MODULE || l_module);
2908
2909 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2910
2911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2912 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2913 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2914 END IF;
2915
2916 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2917 p_data => x_msg_data
2918 );
2919
2920 END Get_Authorization;
2921
2922
2923 PROCEDURE Cancel_Authorization
2924 (
2925 p_api_version IN NUMBER,
2926 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2927 x_return_status OUT NOCOPY VARCHAR2,
2928 x_msg_count OUT NOCOPY NUMBER,
2929 x_msg_data OUT NOCOPY VARCHAR2,
2930 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
2931 p_auth_id IN NUMBER,
2932 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2933 )
2934 IS
2935 l_api_version CONSTANT NUMBER := 1.0;
2936 l_module CONSTANT VARCHAR2(30) := 'Cancel_Authorization';
2937 l_prev_msg_count NUMBER;
2938 BEGIN
2939
2940 IF NOT FND_API.Compatible_API_Call (l_api_version,
2941 p_api_version,
2942 l_module,
2943 G_PKG_NAME)
2944 THEN
2945 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2946 debug_level => FND_LOG.LEVEL_ERROR,
2947 module => G_DEBUG_MODULE || l_module);
2948 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2949 FND_MSG_PUB.Add;
2950 RAISE FND_API.G_EXC_ERROR;
2951 END IF;
2952
2953 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2954 FND_MSG_PUB.initialize;
2955 END IF;
2956 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
2957
2958 x_response.Result_Code := G_RC_AUTH_CANCEL_UNSUPPORTED;
2959
2960 iby_fndcpt_common_pub.Prepare_Result
2961 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2962
2963 EXCEPTION
2964
2965 WHEN FND_API.G_EXC_ERROR THEN
2966
2967 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2968 debug_level => FND_LOG.LEVEL_ERROR,
2969 module => G_DEBUG_MODULE || l_module);
2970 x_return_status := FND_API.G_RET_STS_ERROR;
2971 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2972 p_data => x_msg_data
2973 );
2974 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2975
2976 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2977 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2978 module => G_DEBUG_MODULE || l_module);
2979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2980 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2981 p_data => x_msg_data
2982 );
2983
2984 WHEN OTHERS THEN
2985
2986 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2987 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2988 module => G_DEBUG_MODULE || l_module);
2989
2990 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2991
2992 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2993 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2994 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2995 END IF;
2996
2997 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2998 p_data => x_msg_data
2999 );
3000
3001 END Cancel_Authorization;
3002
3003
3004
3005
3006 PROCEDURE Create_Settlement
3007 (
3008 p_api_version IN NUMBER,
3009 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3010 x_return_status OUT NOCOPY VARCHAR2,
3011 x_msg_count OUT NOCOPY NUMBER,
3012 x_msg_data OUT NOCOPY VARCHAR2,
3013 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
3014 p_payer_equivalency IN VARCHAR2 :=
3015 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
3016 p_trxn_entity_id IN NUMBER,
3017 p_amount IN Amount_rec_type,
3018 p_receipt_attribs IN ReceiptAttribs_rec_type,
3019 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
3020 )
3021 IS
3022 l_api_version CONSTANT NUMBER := 1.0;
3023 l_module CONSTANT VARCHAR2(30) := 'Create_Settlement';
3024 l_prev_msg_count NUMBER;
3025
3026 l_payer_level VARCHAR2(30);
3027 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
3028 l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
3029
3030 l_extension_count NUMBER;
3031 l_transactionid iby_fndcpt_tx_operations.transactionid%TYPE;
3032
3033 l_ecapp_id NUMBER;
3034 l_capture_trxn IBY_PAYMENT_ADAPTER_PUB.CaptureTrxn_rec_type;
3035 lx_capresp IBY_PAYMENT_ADAPTER_PUB.CaptureResp_rec_type;
3036 lx_return_status VARCHAR2(1);
3037 lx_msg_count NUMBER;
3038 lx_msg_data VARCHAR2(3000);
3039
3040
3041 l_app_short_name fnd_application.application_short_name%TYPE;
3042 l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
3043 l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
3044 l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
3045 l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
3046 l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
3047 l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
3048 l_payer IBY_PAYMENT_ADAPTER_PUB.Payer_rec_type;
3049 l_tangible IBY_PAYMENT_ADAPTER_PUB.Tangible_rec_type;
3050 l_pmt_instr IBY_PAYMENT_ADAPTER_PUB.PmtInstr_rec_type;
3051 l_pmt_trxn IBY_PAYMENT_ADAPTER_PUB.PmtReqTrxn_rec_type;
3052 l_reqresp IBY_PAYMENT_ADAPTER_PUB.ReqResp_rec_type;
3053 l_single_use iby_fndcpt_payer_assgn_instr_v.card_single_use_flag%TYPE;
3054
3055 l_op_count NUMBER;
3056
3057 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
3058
3059 CURSOR c_extension
3060 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
3061 ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
3062 ci_payer_level IN VARCHAR2,
3063 ci_payer_equiv IN VARCHAR2
3064 )
3065 IS
3066 SELECT NVL(i.instrument_type,pc.instrument_type), NVL(i.instrument_id,0),
3067 x.origin_application_id, a.application_short_name,
3068 x.order_id, x.trxn_ref_number1, x.trxn_ref_number2,
3069 x.instrument_security_code, x.instr_code_sec_segment_id, x.encrypted,
3070 x.po_number, x.voice_authorization_flag, x.voice_authorization_code,
3071 x.voice_authorization_date, NVL(x.instr_assignment_id,0),
3072 x.payment_channel_code
3073 FROM iby_fndcpt_tx_extensions x, iby_pmt_instr_uses_all i,
3074 iby_external_payers_all p, fnd_application a,
3075 iby_fndcpt_pmt_chnnls_b pc
3076 WHERE (x.instr_assignment_id = i.instrument_payment_use_id(+))
3077 AND (x.payment_channel_code = pc.payment_channel_code)
3078 -- can assume this assignment is for funds capture
3079 AND (x.ext_payer_id = p.ext_payer_id)
3080 AND (x.trxn_extension_id = ci_extension_id)
3081 AND (x.origin_application_id = a.application_id)
3082 AND (p.party_id = ci_payer.Party_Id)
3083 AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
3084 (ci_payer.org_type, ci_payer.org_id,
3085 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
3086 ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
3087 p.cust_account_id,p.acct_site_use_id) = 'T');
3088
3089 CURSOR c_auth
3090 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
3091 IS
3092 SELECT authorization_id
3093 FROM iby_trxn_ext_auths_v
3094 WHERE (trxn_extension_id = ci_extension_id)
3095 AND (authorization_status = 0);
3096
3097
3098 CURSOR C_PAYEE (ci_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE) IS
3099 select s.payeeid, s.org_id, s.org_type
3100 from iby_trxn_summaries_all s, iby_fndcpt_tx_operations o
3101 where s.trxntypeid = 20
3102 and s.transactionid = o.transactionid
3103 and o.trxn_extension_id = ci_trxn_extension_id;
3104
3105 CURSOR c_operation_count
3106 (ci_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
3107 ci_trxn_id IN iby_trxn_summaries_all.transactionid%TYPE)
3108 IS
3109 SELECT count(1)
3110 FROM iby_fndcpt_tx_operations o
3111 WHERE o.transactionid = ci_trxn_id
3112 AND o.trxn_extension_id = ci_trxn_extension_id;
3113
3114 BEGIN
3115
3116
3117 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
3118
3119
3120 IF (c_extension%ISOPEN) THEN CLOSE c_extension; END IF;
3121 IF (c_auth%ISOPEN) THEN CLOSE c_auth; END IF;
3122 IF (c_payee%ISOPEN) THEN CLOSE c_payee; END IF;
3123 IF (c_operation_count%ISOPEN) THEN CLOSE c_operation_count; END IF;
3124
3125 IF NOT FND_API.Compatible_API_Call (l_api_version,
3126 p_api_version,
3127 l_module,
3128 G_PKG_NAME)
3129 THEN
3130 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
3131 debug_level => FND_LOG.LEVEL_ERROR,
3132 module => G_DEBUG_MODULE || l_module);
3133 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
3134 FND_MSG_PUB.Add;
3135 RAISE FND_API.G_EXC_ERROR;
3136 END IF;
3137
3138 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3139 FND_MSG_PUB.initialize;
3140 END IF;
3141 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
3142
3143 IBY_FNDCPT_SETUP_PUB.Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
3144 l_payer_level,l_payer_id,l_payer_attribs);
3145
3146 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
3147 iby_debug_pub.add('invalid payer',iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
3148 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
3149 ELSE
3150 iby_debug_pub.add('Create Settlemnet- Valid Payer',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3151 OPEN c_extension(p_trxn_entity_id,p_payer,l_payer_level,p_payer_equivalency);
3152 FETCH c_extension INTO l_pmt_instr.PmtInstr_Type,
3153 l_pmt_instr.PmtInstr_Id, l_ecapp_id, l_app_short_name,
3154 l_order_id, l_trxn_ref1, l_trxn_ref2,
3155 l_pmt_trxn.CVV2, l_code_segment_id, l_encrypted,
3156 l_pmt_trxn.PONum, l_pmt_trxn.VoiceAuthFlag,
3157 l_pmt_trxn.AuthCode, l_pmt_trxn.DateOfVoiceAuthorization,
3158 l_pmt_instr.Pmtinstr_assignment_id,
3159 l_pmt_trxn.payment_channel_code;
3160 CLOSE c_extension;
3161
3162 iby_debug_pub.add('retrieveing extn attrs rows' || SQL%ROWCOUNT,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3163
3164
3165
3166 IF (l_ecapp_id IS NULL) THEN
3167 iby_debug_pub.add('Create Settlemnet. l_ecapp_id is null ',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3168 x_response.Result_Code := G_RC_INVALID_EXTENSION_ID;
3169 ELSE
3170
3171 iby_debug_pub.add('Create Settlemnet. l_ecapp_id is not null. l_pmt_instr.PmtInstr_Type ' || l_pmt_instr.PmtInstr_Type,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3172 IF (l_pmt_instr.PmtInstr_Type = IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_BANKACCT) THEN
3173
3174 iby_debug_pub.add('Create Settlemnet. Entered Bank Account flow. p_trxn_entity_id ' || p_trxn_entity_id,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3175
3176 OPEN C_PAYEE(p_trxn_entity_id);
3177 FETCH C_PAYEE INTO l_payee.Payee_Id, l_pmt_trxn.org_id, l_pmt_trxn.org_type;
3178 IF C_PAYEE%NOTFOUND THEN
3179 CLOSE C_PAYEE;
3180
3181 iby_debug_pub.add('Invalid Payee ',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3182
3183 x_response.Result_Code := G_RC_INVALID_PAYEE;
3184
3185 iby_fndcpt_common_pub.Prepare_Result
3186 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
3187 RETURN;
3188
3189 END IF;
3190 CLOSE C_PAYEE;
3191
3192 iby_debug_pub.add('Payee id ' || l_payee.Payee_Id || ' org id ' || l_pmt_trxn.org_id || ' org type ' || l_pmt_trxn.org_type ,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3193
3194
3195
3196 l_pmt_trxn.Auth_Type := IBY_PAYMENT_ADAPTER_PUB.G_AUTHTYPE_AUTHONLY;
3197 l_pmt_trxn.PmtMode := 'OFFLINE';
3198 l_pmt_trxn.Settlement_Date := sysdate;
3199
3200 l_payer.Party_Id := p_payer.Party_Id;
3201
3202 --Changing the way we generate tangible_id :Bug: 7628586
3203 l_tangible.Tangible_Id :=
3204 Get_Tangible_Id(l_app_short_name,p_trxn_entity_id);
3205 --l_tangible.Tangible_Id :=
3206 --Get_Tangible_Id(l_app_short_name,l_order_id,l_trxn_ref1,l_trxn_ref2);
3207
3208 l_tangible.Tangible_Amount := p_amount.Value;
3209 l_tangible.Currency_Code := p_amount.Currency_Code;
3210
3211
3212
3213 -- ciphertext; get clear-text value in the engine
3214 IF (l_encrypted = 'Y') THEN
3215 l_pmt_trxn.CVV2 := NULL;
3216 l_pmt_trxn.Trxn_Extension_Id := p_trxn_entity_id;
3217 END IF;
3218
3219
3220 iby_debug_pub.add('calling oraPmtReq ',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3221
3222 IBY_PAYMENT_ADAPTER_PUB.OraPmtReq
3223 (1.0, p_init_msg_list, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
3224 l_ecapp_id, l_payee, l_payer, l_pmt_instr,
3225 l_tangible,
3226 l_pmt_trxn,
3227 lx_return_status,
3228 lx_msg_count,
3229 lx_msg_data,
3230 l_reqresp
3231 );
3232
3233 iby_debug_pub.add('status :=' || lx_return_status,
3234 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3235 iby_debug_pub.add('auth status :=' ||
3236 to_char(l_reqresp.Response.Status),
3237 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3238 iby_debug_pub.add('auth engine code :=' ||
3239 to_char(l_reqresp.Response.ErrCode),
3240 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3241 iby_debug_pub.add('auth engine msg :=' ||
3242 to_char(l_reqresp.Response.ErrMessage),
3243 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3244
3245 iby_debug_pub.add('payment system code :=' ||
3246 to_char(l_reqresp.BEPErrCode),
3247 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3248 iby_debug_pub.add('payment system msg :=' ||
3249 to_char(l_reqresp.BEPErrMessage),
3250 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3251
3252 iby_debug_pub.add('trxn id :=' ||
3253 to_char(l_reqresp.Trxn_ID),
3254 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3255
3256 -- Fix for bug# 7377455. Stamp the tangibleid on the PSON column of
3257 -- IBY_FNDCPT_TX_EXTENSIONS table
3258 iby_debug_pub.add( 'Stamping the PSON on the extension as '||
3259 l_tangible.Tangible_Id,
3260 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3261
3262 UPDATE iby_fndcpt_tx_extensions
3263 SET payment_system_order_number = l_tangible.Tangible_Id
3264 WHERE trxn_extension_id = p_trxn_entity_id;
3265
3266 -- Fix for bug# 7530578. Stamp the initiator transaction extension id
3267 -- on the corresponding record in iby_trxn_summaries_all
3268 iby_debug_pub.add( 'Stamping '||p_trxn_entity_id ||' as the initiator_extension_id'
3269 ||'on the auth record', iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3270
3271 UPDATE iby_trxn_summaries_all
3272 SET initiator_extension_id = p_trxn_entity_id
3273 WHERE transactionid = l_reqresp.Trxn_Id
3274 AND reqtype = 'ORAPMTREQ';
3275
3276 IF (l_reqresp.Response.Status = 0) THEN
3277 --x_response.Result_Code := G_RC_AUTH_SUCCESS;
3278 x_response.Result_Code := 'SETTLEMENT_SUCCESS';
3279 ELSE
3280 --x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR);
3281
3282 -- check if the result code is seeded in the result definitions
3283 -- table
3284 --
3285 IF (IBY_FNDCPT_COMMON_PUB.Get_Result_Category(x_response.Result_Code,iby_payment_adapter_pub.G_INTERFACE_CODE) IS NULL)
3286 THEN
3287 x_response.Result_Code := 'COMMUNICATION_ERROR';
3288 --IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR;
3289 END IF;
3290
3291 IF ( (NOT l_reqresp.Response.ErrMessage IS NULL)
3292 OR (NOT l_reqresp.Response.ErrCode IS NULL) )
3293 THEN
3294 l_reqresp.Response.ErrMessage :=
3295 l_reqresp.Response.ErrMessage || ' (' ||
3296 l_reqresp.Response.ErrCode || ')';
3297 END IF;
3298
3299 iby_fndcpt_common_pub.Prepare_Result(
3300 iby_payment_adapter_pub.G_INTERFACE_CODE,
3301 l_reqresp.Response.ErrMessage,
3302 l_prev_msg_count,
3303 x_return_status,
3304 x_msg_count,
3305 x_msg_data,
3306 x_response
3307 );
3308
3309 RETURN;
3310
3311 END IF;
3312
3313 OPEN c_operation_count(p_trxn_entity_id,l_reqresp.Trxn_Id);
3314 FETCH c_operation_count INTO l_op_count;
3315 CLOSE c_operation_count;
3316
3317 IF (NVL(l_op_count,0) = 0) THEN
3318 iby_debug_pub.add('inserting into iby_fndcpt_tx_operations.',
3319 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3320
3321 INSERT INTO iby_fndcpt_tx_operations
3322 (trxn_extension_id, transactionid,
3323 created_by, creation_date, last_updated_by, last_update_date,
3324 last_update_login, object_version_number
3325 )
3326 VALUES
3327 (p_trxn_entity_id, l_reqresp.Trxn_Id,
3328 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
3329 fnd_global.login_id, 1
3330 );
3331
3332 --
3333 -- back-propagate the authorization
3334 --
3335 INSERT INTO iby_fndcpt_tx_operations
3336 (trxn_extension_id, transactionid,
3337 created_by, creation_date, last_updated_by, last_update_date,
3338 last_update_login, object_version_number
3339 )
3340 SELECT source_trxn_extension_id, l_reqresp.Trxn_Id,
3341 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
3342 fnd_global.login_id, 1
3343 FROM iby_fndcpt_tx_xe_copies
3344 START WITH copy_trxn_extension_id = p_trxn_entity_id
3345 CONNECT BY PRIOR source_trxn_extension_id = copy_trxn_extension_id;
3346
3347 iby_debug_pub.add('back-propogated rows:='||SQL%ROWCOUNT,
3348 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3349
3350 --
3351 -- forward propogate the authorization
3352 --
3353 INSERT INTO iby_fndcpt_tx_operations
3354 (trxn_extension_id, transactionid,
3355 created_by, creation_date, last_updated_by, last_update_date,
3356 last_update_login, object_version_number
3357 )
3358 SELECT copy_trxn_extension_id, l_reqresp.Trxn_Id,
3359 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
3360 fnd_global.login_id, 1
3361 FROM iby_fndcpt_tx_xe_copies
3362 START WITH source_trxn_extension_id = p_trxn_entity_id
3363 CONNECT BY source_trxn_extension_id = PRIOR copy_trxn_extension_id;
3364
3365 iby_debug_pub.add('forward-propogated rows:='||SQL%ROWCOUNT,
3366 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3367 END IF;
3368 ELSE
3369
3370 iby_debug_pub.add('not bank account settlement ',
3371 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3372
3373 OPEN c_auth(p_trxn_entity_id);
3374 FETCH c_auth INTO l_transactionid;
3375 CLOSE c_auth;
3376 IF (l_transactionid IS NULL) THEN
3377 x_response.Result_Code := G_RC_INVALID_AUTHORIZATION;
3378 ELSE
3379 iby_debug_pub.add('calling capture',
3380 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3381
3382 l_capture_trxn.PmtMode := 'ONLINE';
3383 l_capture_trxn.Trxn_Id := l_transactionid;
3384 l_capture_trxn.Price := p_amount.Value;
3385 l_capture_trxn.Currency := p_amount.Currency_Code;
3386
3387 iby_debug_pub.add('Settlement_Date passed as: '||p_receipt_attribs.Settlement_Date,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3388 iby_debug_pub.add('Settlement_Due_Date passed as: '||p_receipt_attribs.Settlement_Due_Date,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3389 l_capture_trxn.Settlement_Date := p_receipt_attribs.Settlement_Date;
3390
3391 iby_debug_pub.add('Create Settlement- calling OraPmtCapture ' ,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3392
3393 IBY_PAYMENT_ADAPTER_PUB.OraPmtCapture
3394 (1.0, p_init_msg_list, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
3395 l_ecapp_id, l_capture_trxn,
3396 lx_return_status, lx_msg_count, lx_msg_data, lx_capresp
3397 );
3398
3399 iby_debug_pub.add('status :=' || lx_return_status,
3400 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3401 iby_debug_pub.add('capture status :=' ||
3402 to_char(lx_capresp.Response.Status),
3403 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3404 iby_debug_pub.add('engine code :=' ||
3405 to_char(lx_capresp.Response.ErrCode),
3406 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3407 iby_debug_pub.add('engine msg :=' ||
3408 to_char(lx_capresp.Response.ErrMessage),
3409 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3410
3411 iby_debug_pub.add('payment system code :=' ||
3412 to_char(lx_capresp.BEPErrCode),
3413 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3414 iby_debug_pub.add('payment system msg :=' ||
3415 to_char(lx_capresp.BEPErrMessage),
3416 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3417
3418 -- Fix for bug# 7530578. Stamp the initiator transaction extension id
3419 -- on the corresponding record in iby_trxn_summaries_all
3420 iby_debug_pub.add( 'Stamping '||p_trxn_entity_id ||' as the initiator_extension_id'
3421 ||'on the capture record', iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
3422
3423 UPDATE iby_trxn_summaries_all
3424 SET initiator_extension_id = p_trxn_entity_id
3425 WHERE transactionid = l_reqresp.Trxn_Id
3426 AND reqtype = 'ORAPMTCAPTURE';
3427
3428 IF (lx_capresp.Response.Status = 0) THEN
3429 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
3430 ELSE
3431 x_response.Result_Code :=
3432 NVL(lx_capresp.Response.ErrCode,IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR);
3433 -- check if the result code is seeded in the result definitions
3434 -- table
3435 --
3436 IF (IBY_FNDCPT_COMMON_PUB.Get_Result_Category(x_response.Result_Code,iby_payment_adapter_pub.G_INTERFACE_CODE) IS NULL)
3437 THEN
3438 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR;
3439 END IF;
3440
3441 IF ( (NOT lx_capresp.Response.ErrMessage IS NULL)
3442 OR (NOT lx_capresp.Response.ErrCode IS NULL) )
3443 THEN
3444 lx_capresp.Response.ErrMessage :=
3445 lx_capresp.Response.ErrMessage || ' (' ||
3446 lx_capresp.Response.ErrCode || ')';
3447 END IF;
3448
3449 iby_fndcpt_common_pub.Prepare_Result(
3450 iby_payment_adapter_pub.G_INTERFACE_CODE,
3451 lx_capresp.Response.ErrMessage,
3452 l_prev_msg_count,
3453 x_return_status,
3454 x_msg_count,
3455 x_msg_data,
3456 x_response
3457 );
3458 RETURN;
3459 END IF;
3460 END IF;
3461 END IF;
3462
3463 END IF;
3464 END IF;
3465
3466
3467
3468 iby_fndcpt_common_pub.Prepare_Result
3469 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
3470
3471
3472
3473 EXCEPTION
3474
3475 WHEN FND_API.G_EXC_ERROR THEN
3476
3477 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
3478 debug_level => FND_LOG.LEVEL_ERROR,
3479 module => G_DEBUG_MODULE || l_module);
3480 x_return_status := FND_API.G_RET_STS_ERROR;
3481 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3482 p_data => x_msg_data
3483 );
3484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3485
3486 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
3487 debug_level => FND_LOG.LEVEL_UNEXPECTED,
3488 module => G_DEBUG_MODULE || l_module);
3489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3490 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
3491 p_data => x_msg_data
3492 );
3493
3494 WHEN OTHERS THEN
3495
3496 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
3497 debug_level => FND_LOG.LEVEL_UNEXPECTED,
3498 module => G_DEBUG_MODULE || l_module);
3499
3500 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
3501
3502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3503 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3504 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
3505 END IF;
3506
3507 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
3508 p_data => x_msg_data
3509 );
3510
3511 END Create_Settlement;
3512
3513
3514 --Overloaded API for backward compatibility
3515 PROCEDURE Create_Settlement
3516 (
3517 p_api_version IN NUMBER,
3518 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3519 x_return_status OUT NOCOPY VARCHAR2,
3520 x_msg_count OUT NOCOPY NUMBER,
3521 x_msg_data OUT NOCOPY VARCHAR2,
3522 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
3523 p_payer_equivalency IN VARCHAR2 :=
3524 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
3525 p_trxn_entity_id IN NUMBER,
3526 p_amount IN Amount_rec_type,
3527 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
3528 )
3529 IS
3530 p_receipt_attribs ReceiptAttribs_rec_type;
3531 l_module CONSTANT VARCHAR2(30) := 'Create_Settlement(2)';
3532
3533 BEGIN
3534 iby_debug_pub.add( 'Overloaded API invoked. ReceiptAttribs_rec_type will be defaulted to Null',
3535 iby_debug_pub.G_LEVEL_INFO,G_DEBUG_MODULE || l_module);
3536
3537 p_receipt_attribs.Settlement_Date := NULL;
3538 p_receipt_attribs.Settlement_Due_Date := NULL;
3539
3540 Create_Settlement
3541 (
3542 p_api_version,
3543 p_init_msg_list,
3544 x_return_status,
3545 x_msg_count,
3546 x_msg_data,
3547 p_payer,
3548 p_payer_equivalency,
3549 p_trxn_entity_id,
3550 p_amount,
3551 p_receipt_attribs,
3552 x_response
3553 );
3554 END Create_Settlement;
3555
3556 -- Create_Settlements
3557 --
3558 -- API name : create_settlements
3559 -- Type : Public
3560 -- Pre-reqs : None
3561 -- Function : Pick up bulk settlment data from the the application
3562 -- views to insert the settlement record in
3563 -- IBY_TRXN_SUMMARIES_ALL
3564 -- Current version : 1.0
3565 -- Previous version: 1.0
3566 -- Initial version : 1.0
3567 --
3568
3569 PROCEDURE Create_Settlements (
3570 p_api_version IN NUMBER,
3571 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3572 p_calling_app_id IN NUMBER,
3573 p_calling_app_request_code IN IBY_TRXN_SUMMARIES_ALL.CALL_APP_SERVICE_REQ_CODE%TYPE,
3574 p_order_view_name IN VARCHAR2,
3575 x_return_status OUT NOCOPY VARCHAR2,
3576 x_msg_count OUT NOCOPY NUMBER,
3577 x_msg_data OUT NOCOPY VARCHAR2,
3578 x_responses OUT NOCOPY SettlementResult_tbl_type
3579 ) IS
3580
3581 l_api_name CONSTANT VARCHAR2(30) := 'Create_Settlements';
3582 l_api_version CONSTANT NUMBER := 1.0;
3583 l_module_name CONSTANT VARCHAR2(200) := G_DEBUG_MODULE || '.' ||
3584 l_api_name;
3585 l_prev_msg_count NUMBER;
3586
3587 --
3588 -- This record are used to insert into the funds capture orders
3589 --
3590 TYPE TrxnTabType IS TABLE OF IBY_TRXN_SUMMARIES_ALL%ROWTYPE
3591 INDEX BY BINARY_INTEGER;
3592
3593 l_Trxn_Tab TrxnTabType;
3594 empty_Trxn_Tab TrxnTabType;
3595
3596 TYPE TrxnCoreTabType IS TABLE OF IBY_TRXN_CORE%ROWTYPE
3597 INDEX BY BINARY_INTEGER;
3598
3599 l_TrxnCore_Tab TrxnCoreTabType;
3600 empty_TrxnCore_Tab TrxnCoreTabType;
3601
3602 TYPE TangibleTabType IS TABLE OF IBY_TANGIBLE%ROWTYPE
3603 INDEX BY BINARY_INTEGER;
3604
3605 -- This record would be used in the create_settlements API to update the PSON
3606 -- attribute of an extension.
3607 -- This is the only attribute that we are populating in this table after a settlement
3608 -- so we will keep only this record in the record. We may add more in future
3609 TYPE Pson_rec_type IS RECORD (
3610 extension_id IBY_FNDCPT_TX_EXTENSIONS.trxn_extension_id%TYPE,
3611 tangibleid IBY_FNDCPT_TX_EXTENSIONS.payment_system_order_number%TYPE
3612 );
3613
3614 TYPE PsonTabType IS TABLE OF Pson_rec_type
3615 INDEX BY BINARY_INTEGER;
3616
3617 l_pson_Tab PsonTabType;
3618
3619 l_Tangible_Tab TangibleTabType;
3620 empty_Tangible_Tab TangibleTabType;
3621
3622 l_where_clause_index VARCHAR2(2000);
3623 l_view_name VARCHAR2(200);
3624 l_app_short_name VARCHAR2(200);
3625
3626 TYPE dyn_order_select IS REF CURSOR;
3627 l_orders_cursor dyn_order_select;
3628 l_orders_query VARCHAR2(32767);
3629
3630 l_settlement_trxn_rec FuncCaptureOrder_rec_type;
3631 l_caOrdersTab caFundcaptureOrdersTabType;
3632 emptyOrdersTab caFundcaptureOrdersTabType;
3633
3634 prev_trxn_ext_id NUMBER(15);
3635 current_trxn_ext_id NUMBER(15);
3636 l_mtangible NUMBER(15);
3637 l_tangibleid VARCHAR2(80);
3638
3639 indx PLS_INTEGER := 0;
3640 order_index PLS_INTEGER := 1;
3641 trxnCoreIndx PLS_INTEGER := 0;
3642 tangibleIndx PLS_INTEGER := 0;
3643
3644 -- Bug# 7658675.REMITTANCE BATCH (IBY_FNDCPT_TRXN_PUB.CREATE_SETTLEMENTS) LTD TO 1000 RECORDS
3645 -- new variables defined for inserting all the remitted records
3646 -- in the iby_trxn_summaries_all table.
3647 -- variable l is used to initialize the record# in the inner loop.
3648 -- rec_limit is the no of fecords processed in each fetch(iteration).It is
3649 -- initialized to 1000 as per design.
3650
3651 l PLS_INTEGER := 0;
3652 rec_limit NUMBER := 1000;
3653
3654 -- routing out parameters
3655
3656 lx_bep_id iby_bepinfo.bepid%TYPE;
3657 lx_process_profile iby_fndcpt_user_cc_pf_b.USER_CC_PROFILE_CODE %TYPE;
3658 lx_bep_key iby_bepkeys.key%TYPE;
3659 l_routing_failure BOOLEAN;
3660
3661 BEGIN
3662
3663 --iby_debug_pub.add('ENTER',iby_debug_pub.G_LEVEL_PROCEDURE,l_module_name);
3664 print_debuginfo(l_module_name, 'ENTER');
3665
3666 -- Close the cursors if open due to previous error
3667 IF (l_orders_cursor%ISOPEN) THEN CLOSE l_orders_cursor; END IF;
3668
3669
3670 SAVEPOINT create_settlements_pub;
3671
3672 -- Standard call to check for call compatibility.
3673 IF NOT FND_API.Compatible_API_Call(l_api_version,
3674 p_api_version,
3675 l_api_name,
3676 G_PKG_NAME) THEN
3677 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3678 END IF;
3679
3680 -- Initialize message list if p_init_msg_list is set to TRUE.
3681 IF FND_API.to_Boolean(p_init_msg_list) THEN
3682 FND_MSG_PUB.initialize;
3683 END IF;
3684 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
3685
3686 -- Initialize API return status to success
3687 x_return_status := FND_API.G_RET_STS_SUCCESS;
3688
3689 -- Start of API body
3690
3691 --
3692 -- Get the shortname of the calling app from the calling
3693 -- app id.
3694 --
3695 SELECT
3696 fnd.application_short_name
3697 INTO
3698 l_app_short_name
3699 FROM
3700 FND_APPLICATION fnd
3701 WHERE
3702 fnd.application_id = p_calling_app_id;
3703
3704 -- Read view name from parameter p_order_view_name
3705 -- if present
3706 IF (p_order_view_name is null) THEN
3707
3708 -- Remove 'SQL' FROM the Name of the view if the application short name
3709 -- begins with 'SQL' for e.g. SQLAP
3710 IF (INSTR(l_app_short_name, 'SQL') > 0) THEN
3711 l_app_short_name := SUBSTR(l_app_short_name, INSTR(l_app_short_name, 'SQL')+3,LENGTH(l_app_short_name));
3712 END IF;
3713
3714 print_debuginfo(l_module_name, 'Calling app short name: '
3715 || l_app_short_name);
3716
3717 --
3718 -- Dynamically form the view name.
3719 --
3720 -- The view name is dependent upon the calling
3721 -- app name and will be of the form
3722 -- <calling app name>_DOCUMENTS_PAYABLE.
3723 --
3724 l_view_name := l_app_short_name || '_FUNDS_CAPTURE_ORDERS_V';
3725 ELSE
3726 l_view_name := p_order_view_name;
3727 END IF;
3728
3729 IF l_view_name = 'AR_FUNDS_CAPTURE_ORDERS_V' THEN
3730 l_where_clause_index :=
3731 -- ' AND orders.selected_remittance_batch_id = decode(substr(:1,1,3),' ||
3732 -- '''AR_''' || ',to_number(substr(:2,4)),-99999)
3733 -- AND orders.worker_id = to_number(substr(:3,INSTR(:4,''_'',4) + 1)) ';
3734 ' AND orders.selected_remittance_batch_id = decode(substr(:1,1,3),' ||
3735 '''AR_''' || ',to_number(substr(substr(:2,1,INSTR(:3,''_'',4)-1),4)),-99999)
3736 AND orders.worker_id = to_number(substr(:4,INSTR(:5,''_'',4) + 1)) ';
3737 ELSE
3738 l_where_clause_index :=
3739 ' AND orders.CALL_APP_SERVICE_REQ_CODE = :1 ' ;
3740 END IF;
3741 --
3742 -- Read the documents for this payment service request
3743 -- from the calling app's view. The calling app's view
3744 -- will be prefixed with the application short name.
3745 --
3746 print_debuginfo(l_module_name, 'Fetching documents from '
3747 || l_view_name
3748 || ' view using CALL_APP_SERVICE_REQ_CODE '
3749 || p_calling_app_request_code
3750 || ' as key');
3751
3752 l_orders_query :=
3753 --OPEN l_orders_cursor FOR
3754 'SELECT
3755 IBY_TRXNSUMM_MID_S.NEXTVAL TRXNMID,
3756 TRXN_REF_NUMBER1,
3757 TRXN_REF_NUMBER2,
3758 PAYMENT_CHANNEL_CODE,
3759 PO_NUMBER,
3760 VOICE_AUTHORIZATION_FLAG,
3761 PAYMENT_FUNCTION,
3762 CALL_APP_SERVICE_REQ_CODE,
3763 SETTLEMENT_DATE,
3764 SETTLEMENT_DUE_DATE,
3765 BR_MATURITY_DATE,
3766 ORDER_DESCRIPTION,
3767 SETTLEMENT_CURRENCY_CODE,
3768 SETTLEMENT_AMOUNT,
3769 PAYMENT_TRXN_EXTENSION_ID,
3770 INTERNAL_BANK_ACCOUNT_ID,
3771 PAYER_PARTY_ID,
3772 CUST_ACCOUNT_ID,
3773 ACCT_SITE_USE_ID,
3774 ORG_ID,
3775 ORG_TYPE,
3776 LEGAL_ENTITY_ID,
3777 BILL_TO_ADDRESS_ID,
3778 SETTLEMENT_CUSTOMER_REFERENCE,
3779 AR_RECEIPT_METHOD_ID,
3780 BR_DRAWEE_ISSUED_FLAG,
3781 BR_SIGNED_FLAG,
3782 BR_DISPUTED_FLAG,
3783 ATTRIBUTE_CATEGORY,
3784 ATTRIBUTE1,
3785 ATTRIBUTE2,
3786 ATTRIBUTE3,
3787 ATTRIBUTE4,
3788 ATTRIBUTE5,
3789 ATTRIBUTE6,
3790 ATTRIBUTE7,
3791 ATTRIBUTE8,
3792 ATTRIBUTE9,
3793 ATTRIBUTE10,
3794 ATTRIBUTE11,
3795 ATTRIBUTE12,
3796 ATTRIBUTE13,
3797 ATTRIBUTE14,
3798 ATTRIBUTE15,
3799 CREATED_BY,
3800 CREATION_DATE,
3801 LAST_UPDATED_BY,
3802 LAST_UPDATE_DATE,
3803 LAST_UPDATE_LOGIN,
3804 OBJECT_VERSION_NUMBER,
3805 AUTH_TRANSACTION_ID,
3806 auth_sales_rep_party_id,
3807 auth_tangibleid,
3808 auth_bepkey,
3809 auth_payeeid,
3810 auth_bepid,
3811 auth_mpayeeid,
3812 auth_ecappid,
3813 auth_paymentmethodname,
3814 auth_mtangibleid,
3815 auth_payeeinstrid,
3816 auth_payerid,
3817 auth_payerinstrid,
3818 auth_detaillookup,
3819 auth_instrnumber,
3820 auth_instrtype,
3821 auth_instrsubtype,
3822 auth_currencynamecode,
3823 auth_desturl,
3824 auth_nlslang,
3825 auth_amount,
3826 auth_process_profile_code,
3827 auth_payment_channel_code,
3828 auth_proc_reference_code,
3829 auth_proc_reference_amount,
3830 auth_legal_entity_id,
3831 auth_settlement_due_date,
3832 auth_bill_to_address_id,
3833 auth_br_maturity_date,
3834 auth_factored_flag,
3835 auth_payment_function,
3836 auth_payer_party_id,
3837 auth_cust_account_id,
3838 auth_acct_site_use_id,
3839 auth_org_id,
3840 auth_org_type,
3841 bep_type,
3842 payer_instr_assignment_id,
3843 debit_auth_flag,
3844 debit_auth_method,
3845 debit_auth_reference,
3846 CAPTURE_TRANSACTION_ID,
3847 int_bank_country,
3848 settle_require_vrfy_flag,
3849 auth_count,
3850 auth_dirdeb_instruction_code
3851 FROM
3852 (SELECT /*+ leading(orders.intrm) cardinality(orders.intrm 10) */
3853 trxnext.TRXN_REF_NUMBER1,
3854 trxnext.TRXN_REF_NUMBER2,
3855 trxnext.PAYMENT_CHANNEL_CODE,
3856 trxnext.PO_NUMBER,
3857 trxnext.VOICE_AUTHORIZATION_FLAG,
3858 payer.PAYMENT_FUNCTION,
3859 SUBSTR(orders.CALL_APP_SERVICE_REQ_CODE, 1, 255) CALL_APP_SERVICE_REQ_CODE,
3860 orders.SETTLEMENT_DATE,
3861 orders.SETTLEMENT_DUE_DATE,
3862 orders.BR_MATURITY_DATE,
3863 SUBSTR(orders.ORDER_DESCRIPTION, 1, 255) ORDER_DESCRIPTION,
3864 SUBSTR(orders.SETTLEMENT_CURRENCY_CODE, 1, 10) SETTLEMENT_CURRENCY_CODE,
3865 orders.SETTLEMENT_AMOUNT,
3866 orders.PAYMENT_TRXN_EXTENSION_ID,
3867 orders.INTERNAL_BANK_ACCOUNT_ID,
3868 orders.PAYER_PARTY_ID,
3869 orders.CUST_ACCOUNT_ID,
3870 orders.ACCT_SITE_USE_ID,
3871 orders.ORG_ID,
3872 SUBSTR(orders.ORG_TYPE, 1, 30) ORG_TYPE,
3873 orders.LEGAL_ENTITY_ID,
3874 orders.BILL_TO_ADDRESS_ID,
3875 orders.SETTLEMENT_CUSTOMER_REFERENCE,
3876 orders.AR_RECEIPT_METHOD_ID,
3877 orders.BR_DRAWEE_ISSUED_FLAG,
3878 orders.BR_SIGNED_FLAG,
3879 orders.BR_DISPUTED_FLAG,
3880 SUBSTR(orders.ATTRIBUTE_CATEGORY, 1, 150) ATTRIBUTE_CATEGORY,
3881 SUBSTR(orders.ATTRIBUTE1, 1, 150) ATTRIBUTE1,
3882 SUBSTR(orders.ATTRIBUTE2, 1, 150) ATTRIBUTE2,
3883 SUBSTR(orders.ATTRIBUTE3, 1, 150) ATTRIBUTE3,
3884 SUBSTR(orders.ATTRIBUTE4, 1, 150) ATTRIBUTE4,
3885 SUBSTR(orders.ATTRIBUTE5, 1, 150) ATTRIBUTE5,
3886 SUBSTR(orders.ATTRIBUTE6, 1, 150) ATTRIBUTE6,
3887 SUBSTR(orders.ATTRIBUTE7, 1, 150) ATTRIBUTE7,
3888 SUBSTR(orders.ATTRIBUTE8, 1, 150) ATTRIBUTE8,
3889 SUBSTR(orders.ATTRIBUTE9, 1, 150) ATTRIBUTE9,
3890 SUBSTR(orders.ATTRIBUTE10, 1, 150) ATTRIBUTE10,
3891 SUBSTR(orders.ATTRIBUTE11, 1, 150) ATTRIBUTE11,
3892 SUBSTR(orders.ATTRIBUTE12, 1, 150) ATTRIBUTE12,
3893 SUBSTR(orders.ATTRIBUTE13, 1, 150) ATTRIBUTE13,
3894 SUBSTR(orders.ATTRIBUTE14, 1, 150) ATTRIBUTE14,
3895 SUBSTR(orders.ATTRIBUTE15, 1, 150) ATTRIBUTE15,
3896 orders.CREATED_BY,
3897 orders.CREATION_DATE,
3898 orders.LAST_UPDATED_BY,
3899 orders.LAST_UPDATE_DATE,
3900 orders.LAST_UPDATE_LOGIN,
3901 orders.OBJECT_VERSION_NUMBER,
3902 auth.transactionid AUTH_TRANSACTION_ID,
3903 auth.sales_rep_party_id auth_sales_rep_party_id,
3904 auth.tangibleid auth_tangibleid,
3905 auth.bepkey auth_bepkey,
3906 auth.payeeid auth_payeeid,
3907 auth.bepid auth_bepid,
3908 auth.mpayeeid auth_mpayeeid,
3909 auth.ecappid auth_ecappid,
3910 auth.paymentmethodname auth_paymentmethodname,
3911 auth.mtangibleid auth_mtangibleid,
3912 auth.payeeinstrid auth_payeeinstrid,
3913 auth.payerid auth_payerid,
3914 auth.payerinstrid auth_payerinstrid,
3915 auth.detaillookup auth_detaillookup,
3916 auth.instrnumber auth_instrnumber,
3917 auth.instrtype auth_instrtype,
3918 auth.instrsubtype auth_instrsubtype,
3919 auth.currencynamecode auth_currencynamecode,
3920 auth.desturl auth_desturl,
3921 auth.nlslang auth_nlslang,
3922 auth.amount auth_amount,
3923 auth.process_profile_code auth_process_profile_code,
3924 auth.payment_channel_code auth_payment_channel_code,
3925 auth.proc_reference_code auth_proc_reference_code,
3926 auth.proc_reference_amount auth_proc_reference_amount,
3927 auth.legal_entity_id auth_legal_entity_id,
3928 auth.settlement_due_date auth_settlement_due_date,
3929 auth.bill_to_address_id auth_bill_to_address_id,
3930 auth.br_maturity_date auth_br_maturity_date,
3931 auth.factored_flag auth_factored_flag,
3932 auth_payer.payment_function auth_payment_function,
3933 auth_payer.party_id auth_payer_party_id,
3934 auth_payer.cust_account_id auth_cust_account_id,
3935 auth_payer.acct_site_use_id auth_acct_site_use_id,
3936 auth_payer.org_id auth_org_id,
3937 auth_payer.org_type auth_org_type,
3938 bep.bep_type,
3939 auth.payer_instr_assignment_id,
3940 auth.debit_auth_flag,
3941 auth.debit_auth_method,
3942 auth.debit_auth_reference,
3943 captures.transactionid CAPTURE_TRANSACTION_ID,
3944 BranchParty.country int_bank_country,
3945 eft_sys.settle_require_vrfy_flag,
3946 COUNT(auth.transactionid) OVER (PARTITION BY
3947 orders.PAYMENT_TRXN_EXTENSION_ID) auth_count,
3948 auth.dirdeb_instruction_code auth_dirdeb_instruction_code
3949 FROM '||l_view_name||' orders,
3950 IBY_FNDCPT_TX_EXTENSIONS trxnext,
3951 IBY_EXTERNAL_PAYERS_ALL payer,
3952 IBY_TRXN_SUMMARIES_ALL auth,
3953 IBY_FNDCPT_TX_OPERATIONS trxnop,
3954 IBY_TRXN_SUMMARIES_ALL captures,
3955 IBY_EXTERNAL_PAYERS_ALL auth_payer,
3956 IBY_BEPINFO bep,
3957 IBY_FNDCPT_USER_EFT_PF_B eft_user,
3958 IBY_FNDCPT_SYS_EFT_PF_B eft_sys,
3959 CE_BANK_ACCOUNTS int_ba,
3960 --CE_BANK_BRANCHES_V branch
3961 HZ_PARTIES BranchParty,
3962 HZ_ORGANIZATION_PROFILES BranchOrgProfile
3963 WHERE orders.PAYMENT_TRXN_EXTENSION_ID = trxnext.TRXN_EXTENSION_ID(+)
3964 AND trxnop.transactionid = auth.transactionid(+)
3965 AND auth.payerid = auth_payer.ext_payer_id(+)
3966 AND auth.bepid = bep.bepid(+)
3967 AND auth.reqtype(+) = ''ORAPMTREQ''
3968 AND DECODE(auth.status(+),''0'',1,''100'',1,''31'',1,''32'',1,0) = 1
3969 AND orders.PAYMENT_TRXN_EXTENSION_ID = trxnop.TRXN_EXTENSION_ID(+)
3970 AND trxnop.transactionid = captures.transactionid(+)
3971 AND DECODE(captures.reqtype(+), ''ORAPMTCAPTURE'',1, ''ORAPMTBATCHREQ'',1, 0) = 1
3972 AND DECODE(captures.status(+), 0,1, 100,1, 11,1, 0) = 1
3973 AND trxnext.EXT_PAYER_ID = payer.EXT_PAYER_ID(+)
3974 AND DECODE(auth.instrtype, ''BANKACCOUNT'',auth.PROCESS_PROFILE_CODE,
3975 NULL) = eft_user.USER_EFT_PROFILE_CODE(+)
3976 AND eft_user.SYS_EFT_PROFILE_CODE = eft_sys.SYS_EFT_PROFILE_CODE(+)
3977 AND orders.INTERNAL_BANK_ACCOUNT_ID = int_ba.BANK_ACCOUNT_ID(+)
3978 --AND int_ba.BANK_BRANCH_ID = branch.BRANCH_PARTY_ID(+)
3979 AND int_ba.BANK_BRANCH_ID = BranchParty.party_id(+)
3980 AND BranchOrgProfile.party_id(+) = BranchParty.party_id
3981 AND BranchParty.status(+) = ''A''
3982 AND SYSDATE BETWEEN TRUNC(BranchOrgProfile.effective_start_date(+))
3983 AND NVL(TRUNC(BranchOrgProfile.effective_end_date(+)),SYSDATE + 1 )
3984 '||l_where_clause_index||'
3985 ORDER BY orders.PAYMENT_TRXN_EXTENSION_ID)';
3986 --USING p_calling_app_request_code,p_calling_app_request_code,
3987 -- p_calling_app_request_code,p_calling_app_request_code,
3988 -- p_calling_app_request_code;
3989 IF l_view_name = 'AR_FUNDS_CAPTURE_ORDERS_V' THEN
3990 OPEN l_orders_cursor FOR
3991 l_orders_query
3992 USING p_calling_app_request_code,p_calling_app_request_code,
3993 p_calling_app_request_code,p_calling_app_request_code,
3994 p_calling_app_request_code;
3995 ELSE
3996 OPEN l_orders_cursor FOR
3997 l_orders_query
3998 USING p_calling_app_request_code;
3999 END IF;
4000
4001 LOOP
4002 -- process only a limited number of records per iteration
4003 FETCH l_orders_cursor BULK COLLECT INTO l_caOrdersTab LIMIT rec_limit;
4004
4005 print_debuginfo(l_module_name, 'Fetch '
4006 || order_index||' : '||l_caOrdersTab.COUNT
4007 || ' order rows fetched');
4008
4009 order_index := order_index + 1;
4010
4011 EXIT WHEN l_caOrdersTab.COUNT = 0;
4012
4013 FOR j in l_caOrdersTab.FIRST..l_caOrdersTab.LAST LOOP
4014
4015 l_routing_failure := FALSE;
4016
4017 l_settlement_trxn_rec := l_caOrdersTab(j);
4018
4019 current_trxn_ext_id := l_settlement_trxn_rec.PAYMENT_TRXN_EXTENSION_ID;
4020 print_debuginfo(l_module_name, 'Transaction Extension Id : '||
4021 current_trxn_ext_id);
4022
4023 iby_debug_pub.add('processing extensionid: '|| current_trxn_ext_id,
4024 iby_debug_pub.G_LEVEL_PROCEDURE,l_module_name);
4025
4026 /*
4027 Bug7658675# REMITTANCE BATCH (IBY_FNDCPT_TRXN_PUB.CREATE_SETTLEMENTS) LTD TO 1000 RECORDS
4028 l is the new variable defined to initailize the x_responses recrod TYPE.
4029 All the occurrences of x_responses(j) are replaced by x_responses(l).
4030 This will allow the remittance batch to process the records more than the 1000(i,e Limit) records.
4031 */
4032
4033 l := (order_index - 2)*rec_limit + j;
4034 x_responses(l).Trxn_Extension_Id := current_trxn_ext_id;
4035
4036 print_debuginfo(l_module_name, 'settlement no-reroute:='
4037 || l_settlement_trxn_rec.settle_req_vrfy
4038 );
4039
4040 -- if the profile allows it, reroute based upon additional
4041 -- data at settlement time
4042 IF (l_settlement_trxn_rec.settle_req_vrfy = 'N') THEN
4043
4044 print_debuginfo(l_module_name, 'rerouting trxn:=' ||
4045 l_settlement_trxn_rec.TRXNMID);
4046
4047 DECLARE
4048 lx_payeeid iby_payee.payeeid%TYPE;
4049 lx_order_id iby_trxn_summaries_all.tangibleid%TYPE;
4050 lx_pmt_name VARCHAR2(100);
4051 lx_bep_lang iby_bepinfo.nls_language%TYPE;
4052 lx_payee_user_name iby_payee.username%TYPE;
4053 lx_payee_passwd iby_payee.password%TYPE;
4054 lx_payee_opt NUMBER;
4055 lx_bep_suffix iby_bepinfo.suffix%TYPE;
4056 lx_bep_url iby_bepinfo.baseurl%TYPE;
4057 lx_bep_pmtscheme iby_pmtschemes.pmtschemename%TYPE;
4058 lx_bep_username iby_bepinfo.bepusername%TYPE;
4059 lx_bep_passwd iby_bepinfo.beppassword%TYPE;
4060 lx_bep_security NUMBER;
4061 lx_bep_setinit NUMBER;
4062 lx_bep_leadtime iby_bepinfo.leadtime%TYPE;
4063 lx_bep_type iby_bepinfo.bep_type%TYPE;
4064 BEGIN
4065
4066 IBY_PAYMENTMANAGERDB_PKG.LISTBEP
4067 (l_settlement_trxn_rec.SETTLEMENT_AMOUNT,
4068 l_settlement_trxn_rec.PAYMENT_CHANNEL_CODE,
4069 l_settlement_trxn_rec.SETTLEMENT_CURRENCY_CODE,
4070 l_settlement_trxn_rec.AUTH_PAYEEID,
4071 l_settlement_trxn_rec.AUTH_INSTRSUBTYPE,
4072 l_settlement_trxn_rec.AUTH_INSTRNUMBER,
4073 NULL, -- ABA routing #
4074 l_settlement_trxn_rec.AUTH_ORG_ID,
4075 NULL, -- fin app type
4076 NULL, -- no transaction id
4077 'ORAPMTREQ',
4078 l_settlement_trxn_rec.AUTH_ECAPPID,
4079 l_settlement_trxn_rec.AUTH_INSTRSUBTYPE,
4080 l_settlement_trxn_rec.int_bank_country,
4081 l_settlement_trxn_rec.AUTH_FACTORED_FLAG,
4082 l_settlement_trxn_rec.INTERNAL_BANK_ACCOUNT_ID,
4083 l_settlement_trxn_rec.BR_SIGNED_FLAG,
4084 l_settlement_trxn_rec.BR_DRAWEE_ISSUED_FLAG,
4085 l_settlement_trxn_rec.AR_RECEIPT_METHOD_ID,
4086 lx_payeeid,
4087 lx_order_id,
4088 lx_pmt_name,
4089 lx_bep_lang,
4090 lx_payee_user_name,
4091 lx_payee_passwd,
4092 lx_payee_opt,
4093 lx_bep_id,
4094 lx_bep_suffix,
4095 lx_bep_url,
4096 lx_bep_key,
4097 lx_bep_pmtscheme,
4098 lx_bep_username,
4099 lx_bep_passwd,
4100 lx_bep_security,
4101 lx_bep_setinit,
4102 lx_bep_leadtime,
4103 lx_bep_type,
4104 lx_process_profile
4105 );
4106 EXCEPTION
4107 WHEN OTHERS THEN
4108 l_routing_failure := TRUE;
4109 print_debuginfo(l_module_name, 'Exception : '
4110 || SQLERRM);
4111 END;
4112
4113 print_debuginfo(l_module_name, 'new process profile:='
4114 || lx_process_profile);
4115 print_debuginfo(l_module_name, 'new bep id:=' || lx_bep_id);
4116 print_debuginfo(l_module_name, 'new bep key:=' || lx_bep_key);
4117
4118 END IF;
4119
4120 -- Perform the necessary validations on the data
4121 --
4122 -- 1. No duplicate settlement capture must exist.
4123 -- 2. The transaction/order id must belong to an existing, successful authorization
4124 -- 3. Transaction amount is a positive number no greater than the amount of the previous authorization
4125 -- 4. Currency of the amount is the same as for the preceding auth
4126 --
4127
4128 print_debuginfo(l_module_name,'related capture is:=' ||
4129 l_settlement_trxn_rec.CAPTURE_TRANSACTION_ID);
4130
4131 -- Transaction Extension Id Check
4132 IF(current_trxn_ext_id is NULL) THEN
4133 x_responses(l).Result.Result_Code := G_RC_INVALID_EXTENSION_ID;
4134 ELSIF (l_routing_failure) THEN
4135 x_responses(l).Result.Result_Code := 'ROUTING_FAILURE';
4136
4137 -- Bug# 7495745: Added this condition to fail those receipts which have multiple
4138 -- auths i.e., the extension is linked to multiple auth transactions, and the main
4139 -- cursor might return null column values in such a case. One of them being bep_type
4140 ELSIF (l_settlement_trxn_rec.bep_type IS NULL) THEN
4141 --flag it as duplicate auth
4142 x_responses(l).Result.Result_Code := G_RC_DUPLICATE_AUTHORIZATION;
4143 print_debuginfo(l_module_name,'Ignoring extension '||
4144 l_settlement_trxn_rec.PAYMENT_TRXN_EXTENSION_ID
4145 ||' (auth transactionid='||l_settlement_trxn_rec.auth_transactionid||
4146 ') as this has multiple Auths');
4147 -- Bug# 7495745: END
4148
4149 ELSIF(current_trxn_ext_id = prev_trxn_ext_id) THEN
4150 --ignore duplicate record due to multiple auths or multiple
4151 -- duplicate settlements
4152 NULL;
4153 ELSIF(NOT l_settlement_trxn_rec.CAPTURE_TRANSACTION_ID IS NULL) THEN
4154 --flag a duplicate settlement
4155 x_responses(l).Result.Result_Code := G_RC_DUPLICATE_SETTLEMENT;
4156 ELSIF(l_settlement_trxn_rec.auth_count = 0) THEN
4157 --flag no auth record present
4158 x_responses(l).Result.Result_Code := G_RC_INVALID_AUTHORIZATION;
4159 ELSIF(l_settlement_trxn_rec.auth_count > 1 AND
4160 l_settlement_trxn_rec.PAYMENT_CHANNEL_CODE in ('CC_PMTCHANNEL_CODE', 'DC_PMTCHANNEL_CODE')) THEN
4161 --only one auth allowed for CreditCards and DebitCards
4162 x_responses(l).Result.Result_Code := G_RC_AUTH_GROUPING_ERROR;
4163 ELSIF(l_settlement_trxn_rec.PAYMENT_CHANNEL_CODE in ('CC_PMTCHANNEL_CODE', 'DC_PMTCHANNEL_CODE')
4164 AND (l_settlement_trxn_rec.auth_amount < l_settlement_trxn_rec.SETTLEMENT_AMOUNT OR
4165 l_settlement_trxn_rec.auth_currencynamecode <> l_settlement_trxn_rec.SETTLEMENT_CURRENCY_CODE)) THEN
4166 -- Settlement amount check
4167 x_responses(l).Result.Result_Code := G_RC_INVALID_AMOUNT;
4168 ELSE
4169 -- Map to transaction table
4170 IF (x_responses(l).Result.Result_Code IS NULL) THEN
4171
4172 l_Trxn_Tab(indx).SALES_REP_PARTY_ID := l_settlement_trxn_rec.auth_SALES_REP_PARTY_ID;
4173 l_Trxn_Tab(indx).TRXNMID := l_settlement_trxn_rec.TRXNMID;
4174 l_Trxn_Tab(indx).TRANSACTIONID := l_settlement_trxn_rec.auth_transactionid;
4175 -- In the case of Bank Accounts and multiple authorizations,
4176 -- get tangible id from API and populate IBY_TANGIBLE table
4177 CASE (l_settlement_trxn_rec.auth_instrtype = BA_INSTR_TYPE
4178 AND l_settlement_trxn_rec.auth_count > 1)
4179 WHEN (TRUE) THEN
4180 print_debuginfo(l_module_name,'Calling IBY_FNDCPT_TRXN_PUB.Get_Tangible_Id...');
4181 print_debuginfo(l_module_name,'with data '||l_app_short_name
4182 ||', '|| l_settlement_trxn_rec.auth_tangibleid ||
4183 ', '|| l_settlement_trxn_rec.TRXN_REF_NUMBER1 ||
4184 ', '|| l_settlement_trxn_rec.TRXN_REF_NUMBER2);
4185
4186 --Bug: 7628586 :: New Functions for creating Tangible id
4187 l_tangibleid :=
4188 IBY_FNDCPT_TRXN_PUB.Get_Tangible_Id
4189 (p_app_short_name => l_app_short_name,
4190 p_trxn_extn_id => l_settlement_trxn_rec.PAYMENT_TRXN_EXTENSION_ID
4191 );
4192 -- l_tangibleid :=
4193 -- IBY_FNDCPT_TRXN_PUB.Get_Tangible_Id
4194 -- (p_app_short_name => l_app_short_name,
4195 -- p_order_id => l_settlement_trxn_rec.auth_tangibleid,
4196 -- p_trxn_ref1 => l_settlement_trxn_rec.TRXN_REF_NUMBER1,
4197 -- p_trxn_ref2 => l_settlement_trxn_rec.TRXN_REF_NUMBER2
4198 -- );
4199 l_Trxn_Tab(indx).TANGIBLEID := substr(l_tangibleid,0,80);
4200 print_debuginfo(l_module_name, 'Got Tangible Id '||
4201 l_Trxn_Tab(indx).TANGIBLEID);
4202
4203 -- insert row into IBY_TANGIBLE
4204 SELECT IBY_TANGIBLE_S.NEXTVAL
4205 INTO l_mtangible
4206 FROM DUAL;
4207
4208 print_debuginfo(l_module_name, 'Got MTtangible Id '
4209 ||l_mtangible);
4210
4211 l_Trxn_Tab(indx).MTANGIBLEID := l_mtangible;
4212 l_Tangible_Tab(tangibleIndx).MTANGIBLEID := l_mtangible;
4213 l_Tangible_Tab(tangibleIndx).TANGIBLEID := l_Trxn_Tab(indx).TANGIBLEID;
4214 l_Tangible_Tab(tangibleIndx).AMOUNT := l_settlement_trxn_rec.SETTLEMENT_AMOUNT;
4215 l_Tangible_Tab(tangibleIndx).CURRENCYNAMECODE := l_settlement_trxn_rec.SETTLEMENT_CURRENCY_CODE;
4216 l_Tangible_Tab(tangibleIndx).CREATED_BY := FND_GLOBAL.user_id;
4217 l_Tangible_Tab(tangibleIndx).CREATION_DATE := sysdate;
4218 l_Tangible_Tab(tangibleIndx).LAST_UPDATED_BY := FND_GLOBAL.user_id;
4219 l_Tangible_Tab(tangibleIndx).LAST_UPDATE_DATE := sysdate;
4220 l_Tangible_Tab(tangibleIndx).LAST_UPDATE_LOGIN := FND_GLOBAL.login_id;
4221 l_Tangible_Tab(tangibleIndx).OBJECT_VERSION_NUMBER := 1.0;
4222
4223
4224 --l_Tangible_Tab(tangibleIndx).SOURCE_OBJECT_TYPE_CODE :=
4225 --l_Tangible_Tab(tangibleIndx).SOURCE_OBJECT_ID :=
4226 --l_Tangible_Tab(tangibleIndx).ACCTNO :=
4227 --l_Tangible_Tab(tangibleIndx).MEMO :=
4228 --l_Tangible_Tab(tangibleIndx).ISSUEDATE :=
4229 --l_Tangible_Tab(tangibleIndx).ORDER_MEDIUM :=
4230 --l_Tangible_Tab(tangibleIndx).EFT_AUTH_METHOD :=
4231 --l_Tangible_Tab(tangibleIndx).SECURITY_GROUP_ID :=
4232
4233 tangibleIndx := tangibleIndx + 1;
4234
4235 print_debuginfo(l_module_name, 'Created Tangible record');
4236
4237 ELSE
4238 l_Trxn_Tab(indx).TANGIBLEID := l_settlement_trxn_rec.auth_tangibleid;
4239 l_Trxn_Tab(indx).MTANGIBLEID := l_settlement_trxn_rec.auth_mtangibleid;
4240
4241 END CASE;
4242 l_pson_Tab(indx).extension_id := l_settlement_trxn_rec.PAYMENT_TRXN_EXTENSION_ID;
4243 l_pson_Tab(indx).tangibleid := l_Trxn_Tab(indx).TANGIBLEID;
4244
4245 l_Trxn_Tab(indx).CALL_APP_SERVICE_REQ_CODE
4246 := l_settlement_trxn_rec.CALL_APP_SERVICE_REQ_CODE;
4247 l_Trxn_Tab(indx).PAYEEID := l_settlement_trxn_rec.auth_payeeid;
4248 l_Trxn_Tab(indx).BEPID :=
4249 NVL(lx_bep_id,l_settlement_trxn_rec.auth_bepid);
4250 l_Trxn_Tab(indx).BEPKEY :=
4251 NVL(lx_bep_key,l_settlement_trxn_rec.auth_bepkey);
4252 l_Trxn_Tab(indx).MPAYEEID := l_settlement_trxn_rec.auth_mpayeeid;
4253 l_Trxn_Tab(indx).ECAPPID := l_settlement_trxn_rec.auth_ecappid;
4254 l_Trxn_Tab(indx).PAYMENTMETHODNAME := l_settlement_trxn_rec.auth_paymentmethodname;
4255 l_Trxn_Tab(indx).PAYEEINSTRID := l_settlement_trxn_rec.auth_payeeinstrid;
4256 l_Trxn_Tab(indx).PAYERID := l_settlement_trxn_rec.auth_payerid;
4257 l_Trxn_Tab(indx).PAYERINSTRID := l_settlement_trxn_rec.auth_payerinstrid;
4258 l_Trxn_Tab(indx).DETAILLOOKUP := l_settlement_trxn_rec.auth_detaillookup;
4259 l_Trxn_Tab(indx).AMOUNT := l_settlement_trxn_rec.SETTLEMENT_AMOUNT;
4260 l_Trxn_Tab(indx).INSTRNUMBER := l_settlement_trxn_rec.auth_instrnumber;
4261 l_Trxn_Tab(indx).INSTRTYPE := l_settlement_trxn_rec.auth_instrtype;
4262 l_Trxn_Tab(indx).INSTRSUBTYPE := l_settlement_trxn_rec.auth_instrsubtype;
4263 l_Trxn_Tab(indx).CURRENCYNAMECODE := l_settlement_trxn_rec.auth_currencynamecode;
4264 -- Payer Context fields
4265 -- Note: The payer context should not be copied from the auth record but from
4266 -- orders view as Settlement can have a different payer context than the Auth.
4267 l_Trxn_Tab(indx).ORG_TYPE := l_settlement_trxn_rec.ORG_TYPE;
4268 l_Trxn_Tab(indx).ORG_ID := l_settlement_trxn_rec.org_id;
4269 l_Trxn_Tab(indx).CUST_ACCOUNT_ID := l_settlement_trxn_rec.cust_account_id;
4270 l_Trxn_Tab(indx).ACCT_SITE_USE_ID := l_settlement_trxn_rec.ACCT_SITE_USE_ID;
4271 l_Trxn_Tab(indx).BR_DRAWEE_ISSUED_FLAG := l_settlement_trxn_rec.BR_DRAWEE_ISSUED_FLAG;
4272 l_Trxn_Tab(indx).BR_SIGNED_FLAG := l_settlement_trxn_rec.BR_SIGNED_FLAG;
4273 l_Trxn_Tab(indx).AR_RECEIPT_METHOD_ID := l_settlement_trxn_rec.AR_RECEIPT_METHOD_ID;
4274 l_Trxn_Tab(indx).PAYEEINSTRID := l_settlement_trxn_rec.INTERNAL_BANK_ACCOUNT_ID;
4275 --Bug 7170445
4276 l_Trxn_Tab(indx).NEEDSUPDT := 'Y' ;
4277 CASE l_settlement_trxn_rec.bep_type
4278 WHEN 'GATEWAY' THEN
4279 l_Trxn_Tab(indx).STATUS := GATEWAY_SETTLEMENT_STATUS;
4280 WHEN 'PROCESSOR' THEN
4281 l_Trxn_Tab(indx).STATUS := PROCESSOR_SETTLEMENT_STATUS;
4282 END CASE;
4283 l_Trxn_Tab(indx).UPDATEDATE := sysdate;
4284 CASE l_settlement_trxn_rec.auth_instrtype
4285 WHEN BA_INSTR_TYPE THEN
4286 l_Trxn_Tab(indx).TRXNTYPEID := BA_CAPTURE_TRXNTYPE;
4287 ELSE
4288 l_Trxn_Tab(indx).TRXNTYPEID := CC_CAPTURE_TRXNTYPE;
4289 END CASE;
4290
4291 --
4292 -- No Sources for the following columns or the columns are not
4293 -- relevant at this stage.
4294 --
4295 --l_Trxn_Tab(indx).ERRORLOCATION := NULL;
4296 --l_Trxn_Tab(indx).BEPCODE := NULL;
4297 --l_Trxn_Tab(indx).BEPMESSAGE := NULL;
4298 --l_Trxn_Tab(indx).BATCHID := NULL;
4299 --l_Trxn_Tab(indx).MBATCHID := NULL;
4300 --l_Trxn_Tab(indx).ECBATCHID := NULL;
4301 --l_Trxn_Tab(indx).REQDATE := NULL;
4302 --l_Trxn_Tab(indx).REQSEQ := NULL;
4303 --l_Trxn_Tab(indx).NEEDSUPDT := NULL;
4304 --l_Trxn_Tab(indx).OVERALL_SCORE := NULL;
4305 --l_Trxn_Tab(indx).SUB_KEY_ID := NULL;
4306 --l_Trxn_Tab(indx).SECURITY_GROUP_ID := NULL;
4307
4308 l_Trxn_Tab(indx).SETTLEDATE := l_settlement_trxn_rec.settlement_date;
4309
4310 CASE l_settlement_trxn_rec.auth_instrtype
4311 WHEN BA_INSTR_TYPE THEN
4312 l_Trxn_Tab(indx).REQTYPE := 'ORAPMTBATCHREQ';
4313 ELSE
4314 l_Trxn_Tab(indx).REQTYPE := 'ORAPMTCAPTURE';
4315 END CASE;
4316 l_trxn_Tab(indx).REQDATE := sysdate;
4317 l_Trxn_Tab(indx).DESTURL := l_settlement_trxn_rec.auth_desturl;
4318 l_Trxn_Tab(indx).NLSLANG := l_settlement_trxn_rec.auth_nlslang;
4319
4320 l_Trxn_Tab(indx).OBJECT_VERSION_NUMBER := 1.0;
4321
4322 l_Trxn_Tab(indx).TRXNREF := l_settlement_trxn_rec.TRXN_REF_NUMBER1;
4323 l_Trxn_Tab(indx).LAST_UPDATE_DATE := sysdate;
4324 l_Trxn_Tab(indx).LAST_UPDATED_BY := FND_GLOBAL.user_id;
4325 l_Trxn_Tab(indx).CREATION_DATE := sysdate;
4326 l_Trxn_Tab(indx).CREATED_BY := FND_GLOBAL.user_id;
4327 l_Trxn_Tab(indx).LAST_UPDATE_LOGIN := FND_GLOBAL.login_id;
4328
4329
4330 -- New columns start here
4331 l_Trxn_Tab(indx).PAYER_PARTY_ID := l_settlement_trxn_rec.payer_party_id;
4332 l_Trxn_Tab(indx).PROCESS_PROFILE_CODE :=
4333 NVL(lx_process_profile,l_settlement_trxn_rec.auth_PROCESS_PROFILE_CODE);
4334 l_Trxn_Tab(indx).PAYMENT_CHANNEL_CODE := l_settlement_trxn_rec.PAYMENT_CHANNEL_CODE;
4335 l_Trxn_Tab(indx).PROC_REFERENCE_CODE := l_settlement_trxn_rec.auth_PROC_REFERENCE_CODE;
4336 l_Trxn_Tab(indx).PROC_REFERENCE_AMOUNT := l_settlement_trxn_rec.auth_PROC_REFERENCE_AMOUNT;
4337 -- l_Trxn_Tab(indx).SETTLEMENT_DUE_DATE := l_settlement_trxn_rec.auth_SETTLEMENT_DUE_DATE;
4338 l_Trxn_Tab(indx).SETTLEMENT_DUE_DATE := l_settlement_trxn_rec.SETTLEMENT_DUE_DATE;
4339 l_Trxn_Tab(indx).CALL_APP_SERVICE_REQ_CODE
4340 := l_settlement_trxn_rec.CALL_APP_SERVICE_REQ_CODE;
4341 l_Trxn_Tab(indx).SETTLEMENT_CUSTOMER_REFERENCE
4342 := l_settlement_trxn_rec.SETTLEMENT_CUSTOMER_REFERENCE;
4343 l_Trxn_Tab(indx).BILL_TO_ADDRESS_ID := l_settlement_trxn_rec.BILL_TO_ADDRESS_ID;
4344 l_Trxn_Tab(indx).BR_MATURITY_DATE := l_settlement_trxn_rec.BR_MATURITY_DATE;
4345 l_Trxn_Tab(indx).FACTORED_FLAG := l_settlement_trxn_rec.auth_FACTORED_FLAG;
4346 l_Trxn_Tab(indx).payer_instr_assignment_id := l_settlement_trxn_rec.payer_instr_assignment_id;
4347
4348 l_Trxn_Tab(indx).debit_auth_flag := l_settlement_trxn_rec.debit_auth_flag;
4349
4350 l_Trxn_Tab(indx).debit_auth_method := l_settlement_trxn_rec.debit_auth_method;
4351
4352 l_Trxn_Tab(indx).debit_auth_reference := l_settlement_trxn_rec.debit_auth_reference;
4353 l_Trxn_Tab(indx).dirdeb_instruction_code := l_settlement_trxn_rec.auth_dirdeb_instruction_code;
4354
4355 -- Fix for bug# 7530578. Stamp the initiator transaction extension id
4356 -- on the corresponding record in iby_trxn_summaries_all
4357 l_Trxn_Tab(indx).initiator_extension_id := l_settlement_trxn_rec.PAYMENT_TRXN_EXTENSION_ID;
4358
4359 /*
4360 * Fix for bug 5614670:
4361 *
4362 * Populate the LE id on the settlement
4363 * transaction based on the internal bank
4364 * account id.
4365 */
4366 l_Trxn_Tab(indx).legal_entity_id :=
4367 get_le_from_bankacct_id(l_Trxn_Tab(indx).payeeinstrid);
4368
4369 indx := indx + 1;
4370
4371 IF (l_settlement_trxn_rec.PAYMENT_CHANNEL_CODE = CC_PMTCHANNEL_CODE) THEN
4372 -- populate Trxn Core record
4373 l_TrxnCore_Tab(trxnCoreIndx).TrxnMID := l_settlement_trxn_rec.TRXNMID;
4374 l_TrxnCore_Tab(trxnCoreIndx).REFERENCECODE := l_settlement_trxn_rec.TRXN_REF_NUMBER1;
4375 l_TrxnCore_Tab(trxnCoreIndx).PONumber := l_settlement_trxn_rec.PO_NUMBER;
4376 l_TrxnCore_Tab(trxnCoreIndx).last_update_date := sysdate;
4377 l_TrxnCore_Tab(trxnCoreIndx).last_updated_by := FND_GLOBAL.user_id;
4378 l_TrxnCore_Tab(trxnCoreIndx).creation_date := sysdate;
4379 l_TrxnCore_Tab(trxnCoreIndx).created_by := FND_GLOBAL.user_id;
4380 l_TrxnCore_Tab(trxnCoreIndx).last_update_login := FND_GLOBAL.login_id;
4381 l_TrxnCore_Tab(trxnCoreIndx).object_version_number := 1.0;
4382
4383 trxnCoreIndx := trxnCoreIndx + 1;
4384 END IF;
4385 END IF;
4386
4387 /*
4388 * Fix for bug 4770275. Set the result code
4389 * and result message before exiting the loop.
4390 */
4391 x_responses(l).Result.Result_Code :=
4392 IBY_FNDCPT_COMMON_PUB.G_RC_SETTLE_PENDING;
4393 IBY_FNDCPT_COMMON_PUB.Prepare_Result
4394 (
4395 l_prev_msg_count,
4396 x_return_status,
4397 x_msg_count,
4398 x_msg_data,
4399 x_responses(l).Result
4400 );
4401
4402 END IF;
4403 -- save the current transaction id to compare
4404 -- in next iteration
4405 prev_trxn_ext_id := current_trxn_ext_id;
4406
4407 print_debuginfo(l_module_name, 'Transaction No: '||j||', '||'Result Code: '||
4408 x_responses(l).Result.Result_Code);
4409
4410 END LOOP; -- Settlement transaction loop
4411
4412 --
4413 -- Bulk Insert settlement records in IBY_TRXN_SUMMARIES_ALL
4414 --
4415 --
4416 print_debuginfo(l_module_name, 'Inserting ' || l_Trxn_Tab.count ||
4417 ' transactions');
4418
4419 IF (l_Trxn_Tab.COUNT > 0) THEN
4420 -- insert into IBY_TRXN_SUMMARIES_ALL
4421 FORALL j IN l_Trxn_Tab.first..l_Trxn_Tab.last
4422 INSERT INTO IBY_TRXN_SUMMARIES_ALL VALUES l_Trxn_Tab(j);
4423 END IF;
4424
4425 print_debuginfo(l_module_name, 'Inserted ' || SQL%ROWCOUNT
4426 || ' transactions');
4427
4428 IF (l_pson_Tab.COUNT > 0) THEN
4429
4430 -- Fix for bug# 7377455. Stamp the tangibleid on the PSON column of
4431 -- IBY_FNDCPT_TX_EXTENSIONS table
4432 -- FORALL j IN l_pson_Tab.first..l_pson_Tab.last
4433 -- UPDATE iby_fndcpt_tx_extensions
4434 -- SET payment_system_order_number = TREAT(l_pson_Tab(j) AS Pson_rec_type).tangibleid
4435 -- WHERE trxn_extension_id = TREAT(l_pson_Tab(j) AS Pson_rec_type).extension_id;
4436
4437 FOR i IN 0..(l_pson_Tab.COUNT-1) LOOP --index starts from 0 and ends at (COUNT-1)
4438 iby_debug_pub.add('updating extension '|| l_pson_Tab(i).extension_id
4439 ||' with PSON '||l_pson_Tab(i).tangibleid, iby_debug_pub.G_LEVEL_PROCEDURE,l_module_name);
4440 UPDATE iby_fndcpt_tx_extensions
4441 SET payment_system_order_number = l_pson_Tab(i).tangibleid,
4442 object_version_number = object_version_number + 1
4443 WHERE trxn_extension_id = l_pson_Tab(i).extension_id;
4444 END LOOP;
4445 iby_debug_pub.add('updating extension complete..'
4446 , iby_debug_pub.G_LEVEL_PROCEDURE,l_module_name);
4447
4448 END IF;
4449
4450 IF (l_TrxnCore_Tab.COUNT > 0) THEN
4451 -- insert into IBY_TRXN_CORE
4452 FORALL j IN l_Trxn_Tab.first..l_Trxn_Tab.last
4453 INSERT INTO IBY_TRXN_CORE VALUES l_TrxnCore_Tab(j);
4454 END IF;
4455
4456 IF (l_Tangible_Tab.COUNT > 0) THEN
4457 -- insert into IBY_TANGIBLE
4458 FORALL j IN l_Tangible_Tab.first..l_Tangible_Tab.last
4459 INSERT INTO IBY_TANGIBLE VALUES l_Tangible_Tab(j);
4460 END IF;
4461
4462 l_caOrdersTab := emptyOrdersTab;
4463 l_Trxn_Tab := empty_Trxn_Tab;
4464 l_Tangible_Tab := empty_Tangible_Tab;
4465
4466 END LOOP; -- Bulk Clause loop
4467
4468 CLOSE l_orders_cursor;
4469
4470 -- End of API body
4471
4472 -- get message count and if count is 1, get message info.
4473 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4474 p_count => x_msg_count,
4475 p_data => x_msg_data);
4476
4477 print_debuginfo(l_module_name, 'RETURN');
4478
4479 EXCEPTION
4480 WHEN fnd_api.g_exc_error THEN
4481 --ROLLBACK TO create_settlements_pub;
4482 x_return_status := fnd_api.g_ret_sts_error;
4483
4484 print_debuginfo(l_module_name, 'Exception : ' || SQLERRM);
4485
4486 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4487 p_count => x_msg_count,
4488 p_data => x_msg_data);
4489
4490
4491 WHEN fnd_api.g_exc_unexpected_error THEN
4492 --ROLLBACK TO create_settlements_pub;
4493 x_return_status := fnd_api.g_ret_sts_unexp_error;
4494
4495 print_debuginfo(l_module_name, 'Exception : ' || SQLERRM);
4496
4497 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4498 p_count => x_msg_count,
4499 p_data => x_msg_data);
4500
4501
4502 WHEN OTHERS THEN
4503 --ROLLBACK TO create_settlements_pub;
4504 x_return_status := fnd_api.g_ret_sts_unexp_error;
4505
4506 print_debuginfo(l_module_name, 'Exception : ' || SQLERRM);
4507
4508 fnd_message.set_name('IBY', 'IBY_API_OTHERS_EXCEP');
4509 fnd_message.set_token('ERROR',SQLERRM);
4510 fnd_msg_pub.add;
4511 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
4512 p_count => x_msg_count,
4513 p_data => x_msg_data);
4514
4515 END Create_Settlements;
4516
4517 PROCEDURE Create_Return
4518 (
4519 p_api_version IN NUMBER,
4520 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4521 x_return_status OUT NOCOPY VARCHAR2,
4522 x_msg_count OUT NOCOPY NUMBER,
4523 x_msg_data OUT NOCOPY VARCHAR2,
4524 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
4525 p_payer_equivalency IN VARCHAR2 :=
4526 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
4527 p_trxn_entity_id IN NUMBER,
4528 p_amount IN Amount_rec_type,
4529 p_receipt_attribs IN ReceiptAttribs_rec_type,
4530 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
4531 )
4532 IS
4533 l_api_version CONSTANT NUMBER := 1.0;
4534 l_module CONSTANT VARCHAR2(30) := 'Create_Return';
4535 l_prev_msg_count NUMBER;
4536
4537 l_payer_level VARCHAR2(30);
4538 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
4539 l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
4540 l_settled_flag VARCHAR2(1);
4541 l_returned_flag VARCHAR2(1);
4542 l_extension_found BOOLEAN;
4543
4544 l_ecapp_id NUMBER;
4545 l_app_short_name fnd_application.application_short_name%TYPE;
4546 l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
4547 l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
4548 l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
4549 l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
4550 l_tangible IBY_PAYMENT_ADAPTER_PUB.Tangible_rec_type;
4551 l_pmt_instr IBY_PAYMENT_ADAPTER_PUB.PmtInstr_rec_type;
4552 l_return_trxn IBY_PAYMENT_ADAPTER_PUB.ReturnTrxn_rec_type;
4553 l_credit_trxn IBY_PAYMENT_ADAPTER_PUB.CreditTrxn_rec_type;
4554
4555 lx_creditresp IBY_PAYMENT_ADAPTER_PUB.CreditResp_rec_type;
4556 lx_returnresp IBY_PAYMENT_ADAPTER_PUB.ReturnResp_rec_type;
4557 lx_return_status VARCHAR2(1);
4558 lx_msg_count NUMBER;
4559 lx_msg_data VARCHAR2(3000);
4560
4561 l_result_category iby_result_codes.result_category%TYPE;
4562 l_interface_code iby_result_codes.request_interface_code%TYPE;
4563
4564 l_return_pson VARCHAR2(100);
4565
4566 CURSOR c_extension
4567 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
4568 ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
4569 ci_payer_level IN VARCHAR2,
4570 ci_payer_equiv IN VARCHAR2
4571 )
4572 IS
4573 SELECT NVL(settled_flag,'N'), NVL(returned_flag,'N'),
4574 x.instrument_type, x.instrument_id,
4575 x.origin_application_id, a.application_short_name,
4576 x.order_id, x.trxn_ref_number1, x.trxn_ref_number2,
4577 x.payment_channel_code
4578 FROM iby_trxn_extensions_v x, iby_pmt_instr_uses_all i,
4579 iby_external_payers_all p, fnd_application a
4580 WHERE (x.instr_assignment_id = i.instrument_payment_use_id)
4581 -- can assume this assignment is for funds capture
4582 AND (x.origin_application_id = a.application_id)
4583 AND (i.ext_pmt_party_id = p.ext_payer_id)
4584 AND (x.trxn_extension_id = ci_extension_id)
4585 AND (p.party_id = ci_payer.Party_Id)
4586 AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
4587 (ci_payer.org_type, ci_payer.org_id,
4588 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
4589 ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
4590 p.cust_account_id,p.acct_site_use_id) = 'T');
4591
4592 CURSOR c_settlement
4593 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
4594 IS
4595 SELECT settlement_id, payee_id, org_id, payment_system_order_number
4596 FROM iby_trxn_ext_settlements_v
4597 WHERE (trxn_extension_id = ci_extension_id)
4598 ORDER BY settlement_id DESC;
4599 BEGIN
4600
4601 IF (c_extension%ISOPEN) THEN CLOSE c_extension; END IF;
4602 IF (c_settlement%ISOPEN) THEN CLOSE c_settlement; END IF;
4603
4604 IF NOT FND_API.Compatible_API_Call (l_api_version,
4605 p_api_version,
4606 l_module,
4607 G_PKG_NAME)
4608 THEN
4609 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
4610 debug_level => FND_LOG.LEVEL_ERROR,
4611 module => G_DEBUG_MODULE || l_module);
4612 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
4613 FND_MSG_PUB.Add;
4614 RAISE FND_API.G_EXC_ERROR;
4615 END IF;
4616
4617 IF FND_API.to_Boolean( p_init_msg_list ) THEN
4618 FND_MSG_PUB.initialize;
4619 END IF;
4620 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
4621
4622 IBY_FNDCPT_SETUP_PUB.Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
4623 l_payer_level,l_payer_id,l_payer_attribs);
4624
4625 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
4626 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
4627 ELSE
4628 OPEN c_extension(p_trxn_entity_id,p_payer,l_payer_level,
4629 p_payer_equivalency);
4630 FETCH c_extension INTO l_settled_flag, l_returned_flag,
4631 l_pmt_instr.PmtInstr_Type, l_pmt_instr.PmtInstr_Id, l_ecapp_id,
4632 l_app_short_name, l_order_id, l_trxn_ref1, l_trxn_ref2,
4633 l_credit_trxn.payment_channel_code;
4634 l_extension_found := NOT c_extension%NOTFOUND;
4635 CLOSE c_extension;
4636
4637 IF (l_settled_flag IS NULL) THEN
4638 x_response.Result_Code := G_RC_INVALID_EXTENSION_ID;
4639 ELSIF (l_settled_flag = 'N') THEN
4640 x_response.Result_Code := G_RC_INVALID_SETTLEMENT;
4641 ELSE
4642
4643 OPEN c_settlement(p_trxn_entity_id);
4644 FETCH c_settlement INTO l_return_trxn.Trxn_ID, l_payee.Payee_ID,
4645 l_credit_trxn.Org_ID, l_return_pson;
4646 CLOSE c_settlement;
4647
4648 IF (l_returned_flag = 'Y') THEN
4649 --Using overloaded trxn_extension_id :: 7628586
4650 l_tangible.Tangible_Id :=
4651 Get_Tangible_Id(l_app_short_name,p_trxn_entity_id);
4652 --l_tangible.Tangible_Id :=
4653 -- Get_Tangible_Id(l_app_short_name,l_order_id,l_trxn_ref1,l_trxn_ref2);
4654
4655 iby_debug_pub.add('Settlement_Date passed as: '||p_receipt_attribs.Settlement_Date,iby_debug_pub.G_LEVEL_INFO,G_DEBUG_MODULE || l_module);
4656 iby_debug_pub.add('Settlement_Due_Date passed as: '||p_receipt_attribs.Settlement_Due_Date,iby_debug_pub.G_LEVEL_INFO,G_DEBUG_MODULE || l_module);
4657
4658 l_tangible.Tangible_Amount := p_amount.Value;
4659 l_tangible.Currency_Code := p_amount.Currency_Code;
4660 l_credit_trxn.PmtMode := 'ONLINE';
4661 l_credit_trxn.Payer_Party_Id := p_payer.Party_Id;
4662 l_credit_trxn.Settlement_Date := p_receipt_attribs.Settlement_Date;
4663
4664 IBY_PAYMENT_ADAPTER_PUB.OraPmtCredit
4665 (1.0, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
4666 l_ecapp_id, l_payee, l_pmt_instr, l_tangible, l_credit_trxn,
4667 lx_return_status, lx_msg_count, lx_msg_data, lx_creditresp
4668 );
4669
4670 IF (NOT lx_creditresp.Trxn_Id IS NULL) THEN
4671 INSERT INTO iby_fndcpt_tx_operations
4672 (trxn_extension_id, transactionid,
4673 created_by, creation_date, last_updated_by, last_update_date,
4674 last_update_login, object_version_number
4675 )
4676 VALUES
4677 (p_trxn_entity_id, lx_creditresp.Trxn_Id,
4678 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
4679 fnd_global.login_id, 1
4680 );
4681 --
4682 -- back-propagate the credit
4683 --
4684 INSERT INTO iby_fndcpt_tx_operations
4685 (trxn_extension_id, transactionid,
4686 created_by, creation_date, last_updated_by, last_update_date,
4687 last_update_login, object_version_number
4688 )
4689 SELECT source_trxn_extension_id, lx_creditresp.Trxn_Id,
4690 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
4691 fnd_global.login_id, 1
4692 FROM iby_fndcpt_tx_xe_copies
4693 START WITH copy_trxn_extension_id = p_trxn_entity_id
4694 CONNECT BY source_trxn_extension_id = PRIOR copy_trxn_extension_id;
4695
4696 -- Fix for bug# 7377455. Stamp the tangibleid on the PSON column of
4697 -- IBY_FNDCPT_TX_EXTENSIONS table
4698 iby_debug_pub.add( 'Stamping the PSON on the extension as '||
4699 l_tangible.Tangible_Id,
4700 iby_debug_pub.G_LEVEL_INFO,G_DEBUG_MODULE || l_module);
4701
4702 UPDATE iby_fndcpt_tx_extensions
4703 SET payment_system_order_number = l_tangible.Tangible_Id
4704 WHERE trxn_extension_id = p_trxn_entity_id;
4705
4706 -- Fix for bug# 7530578. Stamp the initiator transaction extension id
4707 -- on the corresponding record in iby_trxn_summaries_all
4708 iby_debug_pub.add( 'Stamping '||p_trxn_entity_id ||' as the initiator_extension_id'
4709 ||'on the credit record', iby_debug_pub.G_LEVEL_INFO,G_DEBUG_MODULE || l_module);
4710
4711 UPDATE iby_trxn_summaries_all
4712 SET initiator_extension_id = p_trxn_entity_id
4713 WHERE transactionid = lx_creditresp.Trxn_Id
4714 AND reqtype = 'ORAPMTCREDIT';
4715
4716 COMMIT;
4717 END IF;
4718
4719 IF (lx_creditresp.Response.Status = 0) THEN
4720 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
4721 ELSE
4722 x_response.Result_Code :=
4723 NVL(lx_creditresp.Response.ErrCode,IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR);
4724 END IF;
4725 ELSE
4726
4727 iby_debug_pub.add('Settlement_Date passed as: '||p_receipt_attribs.Settlement_Date,iby_debug_pub.G_LEVEL_INFO,G_DEBUG_MODULE || l_module);
4728 iby_debug_pub.add('Settlement_Due_Date passed as: '||p_receipt_attribs.Settlement_Due_Date,iby_debug_pub.G_LEVEL_INFO,G_DEBUG_MODULE || l_module);
4729
4730 l_return_trxn.Currency := p_amount.Currency_Code;
4731 l_return_trxn.Price := p_amount.Value;
4732 l_return_trxn.PmtMode := 'ONLINE';
4733 l_return_trxn.Settlement_Date := p_receipt_attribs.Settlement_Date;
4734
4735 IBY_PAYMENT_ADAPTER_PUB.OraPmtReturn
4736 (1.0, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
4737 l_ecapp_id, l_return_trxn, lx_return_status, lx_msg_count,
4738 lx_msg_data, lx_returnresp
4739 );
4740
4741 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR;
4742 l_interface_code := IBY_FNDCPT_COMMON_PUB.G_INTERFACE_CODE;
4743
4744 -- Fix for bug# 7377455. Stamp the tangibleid on the PSON column of
4745 -- IBY_FNDCPT_TX_EXTENSIONS table
4746 iby_debug_pub.add( 'Stamping the PSON on the extension as '||
4747 l_return_pson,
4748 iby_debug_pub.G_LEVEL_INFO,G_DEBUG_MODULE || l_module);
4749
4750 UPDATE iby_fndcpt_tx_extensions
4751 SET payment_system_order_number = l_return_pson
4752 WHERE trxn_extension_id = p_trxn_entity_id;
4753
4754 -- Fix for bug# 7530578. Stamp the initiator transaction extension id
4755 -- on the corresponding record in iby_trxn_summaries_all
4756 iby_debug_pub.add( 'Stamping '||p_trxn_entity_id ||' as the initiator_extension_id'
4757 ||'on the return record', iby_debug_pub.G_LEVEL_INFO,G_DEBUG_MODULE || l_module);
4758
4759 UPDATE iby_trxn_summaries_all
4760 SET initiator_extension_id = p_trxn_entity_id
4761 WHERE transactionid = lx_returnresp.Trxn_Id
4762 AND reqtype = 'ORAPMTRETURN';
4763
4764 IF (lx_returnresp.Response.Status = 0) THEN
4765 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
4766 ELSIF (NOT lx_returnresp.Response.ErrCode IS NULL) THEN
4767 x_response.Result_Code := lx_returnresp.Response.ErrCode;
4768 l_interface_code := iby_payment_adapter_pub.G_INTERFACE_CODE;
4769 END IF;
4770 END IF;
4771
4772 IF (l_interface_code = iby_payment_adapter_pub.G_INTERFACE_CODE) THEN
4773 l_result_category :=
4774 IBY_FNDCPT_COMMON_PUB.Get_Result_Category
4775 (x_response.Result_Code,l_interface_code);
4776 -- check if the result code is seeded in the result definitions
4777 -- table
4778 --
4779 IF (l_result_category IS NULL) THEN
4780 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR;
4781 ELSIF (l_result_category = IBY_FNDCPT_COMMON_PUB.G_RCAT_DUP_REQ) THEN
4782 x_response.Result_Code := G_RC_DUPLICATE_RETURN;
4783 END IF;
4784 END IF;
4785 END IF;
4786 END IF;
4787
4788 iby_fndcpt_common_pub.Prepare_Result
4789 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
4790
4791 EXCEPTION
4792
4793 WHEN FND_API.G_EXC_ERROR THEN
4794
4795 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
4796 debug_level => FND_LOG.LEVEL_ERROR,
4797 module => G_DEBUG_MODULE || l_module);
4798 x_return_status := FND_API.G_RET_STS_ERROR;
4799 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4800 p_data => x_msg_data
4801 );
4802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4803
4804 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
4805 debug_level => FND_LOG.LEVEL_UNEXPECTED,
4806 module => G_DEBUG_MODULE || l_module);
4807 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4808 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
4809 p_data => x_msg_data
4810 );
4811
4812 WHEN OTHERS THEN
4813
4814 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
4815 debug_level => FND_LOG.LEVEL_UNEXPECTED,
4816 module => G_DEBUG_MODULE || l_module);
4817
4818 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
4819
4820 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4821 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4822 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
4823 END IF;
4824
4825 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
4826 p_data => x_msg_data
4827 );
4828
4829 END Create_Return;
4830
4831 PROCEDURE Create_Return
4832 (
4833 p_api_version IN NUMBER,
4834 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4835 x_return_status OUT NOCOPY VARCHAR2,
4836 x_msg_count OUT NOCOPY NUMBER,
4837 x_msg_data OUT NOCOPY VARCHAR2,
4838 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
4839 p_payer_equivalency IN VARCHAR2 :=
4840 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
4841 p_trxn_entity_id IN NUMBER,
4842 p_amount IN Amount_rec_type,
4843 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
4844 )
4845 IS
4846 p_receipt_attribs ReceiptAttribs_rec_type;
4847 l_module CONSTANT VARCHAR2(30) := 'Create_Return(2)';
4848 BEGIN
4849 iby_debug_pub.add( 'Overloaded API invoked. ReceiptAttribs_rec_type will be defaulted to Null',
4850 iby_debug_pub.G_LEVEL_INFO,G_DEBUG_MODULE || l_module);
4851
4852 p_receipt_attribs.Settlement_Date := NULL;
4853 p_receipt_attribs.Settlement_Due_Date := NULL;
4854
4855 --Call the overloaded API with ReceiptAttribs_rec_type IN parameter
4856 Create_Return
4857 (
4858 p_api_version,
4859 p_init_msg_list,
4860 x_return_status,
4861 x_msg_count,
4862 x_msg_data,
4863 p_payer,
4864 p_payer_equivalency,
4865 p_trxn_entity_id,
4866 p_amount,
4867 p_receipt_attribs,
4868 x_response
4869 );
4870 END Create_Return;
4871
4872 PROCEDURE Encrypt_Extensions
4873 (p_commit IN VARCHAR2,
4874 p_sys_security_key IN iby_security_pkg.DES3_KEY_TYPE,
4875 x_err_code OUT NOCOPY VARCHAR2
4876 )
4877 IS
4878 l_mode iby_sys_security_options.instr_sec_code_encryption_mode%TYPE;
4879 l_cvv2 VARCHAR2(100);
4880 lx_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
4881 l_subkey iby_security_pkg.DES3_KEY_TYPE;
4882
4883 CURSOR c_extensions
4884 IS
4885 SELECT x.trxn_extension_id, x.instrument_security_code
4886 FROM iby_fndcpt_tx_extensions x
4887 WHERE (NVL(x.encrypted,'N') = 'N')
4888 AND (NOT instrument_security_code IS NULL);
4889 BEGIN
4890
4891 l_mode := Get_Extension_Encrypt_Mode;
4892 IF (l_mode = iby_security_pkg.G_ENCRYPT_MODE_NONE) THEN
4893 RETURN;
4894 END IF;
4895
4896 iby_security_pkg.validate_sys_key(p_sys_security_key,x_err_code);
4897 IF (NOT x_err_code IS NULL) THEN
4898 RETURN;
4899 END IF;
4900
4901 FOR c_ext_rec IN c_extensions LOOP
4902 -- The CVV value should be padded upto 32 bytes of data
4903 -- as per the PABP norms. So, use the new Encode_CVV API
4904 -- instead of the previous Encode_Number API.
4905 l_cvv2 :=
4906 IBY_SECURITY_PKG.Encode_CVV(c_ext_rec.instrument_security_code,TRUE);
4907 IBY_SECURITY_PKG.Create_Segment
4908 (FND_API.G_FALSE,l_cvv2,iby_security_pkg.G_ENCODING_NUMERIC,
4909 p_sys_security_key,lx_segment_id);
4910
4911 UPDATE iby_fndcpt_tx_extensions
4912 SET
4913 instrument_security_code = LPAD('X',LENGTH(instrument_security_code),'X'),
4914 encrypted = 'Y',
4915 instr_code_sec_segment_id = lx_segment_id,
4916 last_updated_by = fnd_global.user_id,
4917 last_update_date = SYSDATE,
4918 last_update_login = fnd_global.login_id,
4919 object_version_number = object_version_number + 1
4920 WHERE trxn_extension_id = c_ext_rec.trxn_extension_id;
4921 END LOOP;
4922
4923 IF FND_API.To_Boolean(p_commit) THEN
4924 COMMIT;
4925 END IF;
4926 END Encrypt_Extensions;
4927
4928 PROCEDURE Encrypt_Security_Code
4929 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
4930 p_sys_security_key IN iby_security_pkg.DES3_KEY_TYPE,
4931 p_security_code IN iby_fndcpt_tx_extensions.instrument_security_code%TYPE,
4932 x_segment_id OUT NOCOPY NUMBER,
4933 x_err_code OUT NOCOPY VARCHAR2
4934 )
4935 IS
4936 l_mode iby_sys_security_options.instr_sec_code_encryption_mode%TYPE;
4937 l_cvv2 VARCHAR2(100);
4938 lx_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
4939 l_subkey iby_security_pkg.DES3_KEY_TYPE;
4940
4941 BEGIN
4942 -- test_debug('Inside Encrypt_Security_Code.. ');
4943 l_mode := Get_Extension_Encrypt_Mode;
4944 IF (l_mode = iby_security_pkg.G_ENCRYPT_MODE_NONE) THEN
4945 RETURN;
4946 END IF;
4947 iby_security_pkg.validate_sys_key(p_sys_security_key,x_err_code);
4948 -- test_debug('sysKey valid.. ');
4949 IF (NOT x_err_code IS NULL) THEN
4950 RETURN;
4951 END IF;
4952 l_cvv2 := IBY_SECURITY_PKG.Encode_CVV(p_security_code,TRUE);
4953 -- test_debug('encoded cvv: '|| l_cvv2);
4954 IBY_SECURITY_PKG.Create_Segment
4955 (FND_API.G_FALSE,l_cvv2,iby_security_pkg.G_ENCODING_NUMERIC,
4956 p_sys_security_key,lx_segment_id);
4957 -- test_debug('segmentId: '|| lx_segment_id);
4958
4959 x_segment_id := lx_segment_id;
4960
4961 IF FND_API.To_Boolean(p_commit) THEN
4962 COMMIT;
4963 END IF;
4964
4965 END Encrypt_Security_Code;
4966
4967 PROCEDURE Decrypt_Extensions
4968 (p_commit IN VARCHAR2 := FND_API.G_TRUE,
4969 p_sys_security_key IN iby_security_pkg.DES3_KEY_TYPE,
4970 x_err_code OUT NOCOPY VARCHAR2
4971 )
4972 IS
4973 l_mode iby_sys_security_options.instr_sec_code_encryption_mode%TYPE;
4974
4975 CURSOR c_extensions
4976 IS
4977 SELECT x.trxn_extension_id, x.instr_sec_code_length,
4978 NVL(x.encrypted,'N'), s.segment_cipher_text, k.subkey_cipher_text,
4979 s.sec_segment_id
4980 FROM iby_fndcpt_tx_extensions x, iby_security_segments s,
4981 iby_sys_security_subkeys k
4982 WHERE (NVL(x.encrypted,'N') = 'Y')
4983 AND (NOT instrument_security_code IS NULL)
4984 AND (x.instr_code_sec_segment_id = s.sec_segment_id(+))
4985 AND (s.sec_subkey_id = k.sec_subkey_id(+));
4986
4987 BEGIN
4988
4989 l_mode := Get_Extension_Encrypt_Mode;
4990 IF (NOT (l_mode = iby_security_pkg.G_ENCRYPT_MODE_NONE)) THEN
4991 RETURN;
4992 END IF;
4993
4994 iby_security_pkg.validate_sys_key(p_sys_security_key,x_err_code);
4995 IF (NOT x_err_code IS NULL) THEN
4996 RETURN;
4997 END IF;
4998
4999 FOR c_ext_rec IN c_extensions LOOP
5000 UPDATE iby_fndcpt_tx_extensions
5001 SET
5002 instrument_security_code =
5003 Get_Security_Code(p_sys_security_key,
5004 c_ext_rec.subkey_cipher_text,
5005 c_ext_rec.segment_cipher_text,
5006 c_ext_rec.instr_sec_code_length),
5007 encrypted = 'N',
5008 instr_code_sec_segment_id = NULL,
5009 last_updated_by = fnd_global.user_id,
5010 last_update_date = SYSDATE,
5011 last_update_login = fnd_global.login_id,
5012 object_version_number = object_version_number + 1
5013 WHERE trxn_extension_id = c_ext_rec.trxn_extension_id;
5014
5015 DELETE iby_security_segments
5016 WHERE sec_segment_id = c_ext_rec.sec_segment_id;
5017 END LOOP;
5018
5019 IF FND_API.To_Boolean(p_commit) THEN
5020 COMMIT;
5021 END IF;
5022
5023 END Decrypt_Extensions;
5024
5025 FUNCTION Get_Security_Code
5026 (p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
5027 p_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
5028 p_sec_code_cipher IN iby_security_segments.segment_cipher_text%TYPE,
5029 p_sec_code_len IN iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE
5030 )
5031 RETURN iby_fndcpt_tx_extensions.instrument_security_code%TYPE
5032 IS
5033 --Increased the varchar2 size from 30 to 100 since we now
5034 --pad the cvv to 32 bytes of data.
5035 l_sec_code VARCHAR2(100);
5036 l_subkey iby_security_pkg.DES3_KEY_TYPE;
5037 BEGIN
5038 l_subkey :=
5039 IBY_SECURITY_PKG.Get_Sys_Subkey(p_sys_sec_key,p_subkey_cipher);
5040
5041 l_sec_code :=
5042 dbms_obfuscation_toolkit.des3decrypt
5043 ( input => p_sec_code_cipher, key => l_subkey,
5044 which => dbms_obfuscation_toolkit.ThreeKeyMode
5045 );
5046
5047 l_sec_code := IBY_SECURITY_PKG.Decode_Number(l_sec_code,p_sec_code_len,TRUE);
5048
5049 RETURN l_sec_code;
5050 END Get_Security_Code;
5051
5052 FUNCTION Get_Security_Code
5053 (p_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
5054 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
5055 )
5056 RETURN iby_fndcpt_tx_extensions.instrument_security_code%TYPE
5057 IS
5058 l_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
5059 l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
5060 l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
5061 l_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
5062 l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
5063 lx_err_code VARCHAR2(100);
5064
5065 CURSOR c_extension
5066 (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
5067 IS
5068 SELECT x.instrument_security_code, x.instr_sec_code_length,
5069 NVL(x.encrypted,'N'), s.segment_cipher_text, k.subkey_cipher_text
5070 FROM iby_fndcpt_tx_extensions x, iby_security_segments s,
5071 iby_sys_security_subkeys k
5072 WHERE (trxn_extension_id = ci_extension_id)
5073 AND (x.instr_code_sec_segment_id = s.sec_segment_id(+))
5074 AND (s.sec_subkey_id = k.sec_subkey_id(+));
5075
5076 BEGIN
5077
5078 IF (c_extension%ISOPEN) THEN CLOSE c_extension; END IF;
5079
5080 OPEN c_extension(p_trxn_extension_id);
5081 FETCH c_extension
5082 INTO l_sec_code, l_sec_code_len, l_encrypted, l_segment_cipher,
5083 l_subkey_cipher;
5084 CLOSE c_extension;
5085
5086 IF (l_encrypted = 'N') THEN
5087 RETURN l_sec_code;
5088 ELSE
5089 iby_security_pkg.validate_sys_key(p_sys_sec_key,lx_err_code);
5090 IF (NOT lx_err_code IS NULL) THEN
5091 raise_application_error(-20000, lx_err_code, FALSE);
5092 RETURN NULL;
5093 END IF;
5094 RETURN Get_Security_Code(p_sys_sec_key,l_subkey_cipher,
5095 l_segment_cipher,l_sec_code_len);
5096 END IF;
5097 END Get_Security_Code;
5098
5099
5100 /*--------------------------------------------------------------------
5101 | NAME:
5102 | print_debuginfo
5103 |
5104 | PURPOSE:
5105 | This procedure prints the debug message to the concurrent manager
5106 | log file.
5107 |
5108 | PARAMETERS:
5109 | IN
5110 | p_debug_text - The debug message to be printed
5111 |
5112 | OUT
5113 |
5114 |
5115 | RETURNS:
5116 |
5117 | NOTES:
5118 |
5119 *---------------------------------------------------------------------*/
5120 PROCEDURE print_debuginfo(
5121 p_module IN VARCHAR2,
5122 p_debug_text IN VARCHAR2
5123 )
5124 IS
5125 BEGIN
5126
5127 IBY_TRANSACTIONCC_PKG.print_debuginfo(p_module, p_debug_text);
5128
5129 END print_debuginfo;
5130
5131
5132 /*--------------------------------------------------------------------
5133 | NAME:
5134 | get_le_from_bankacct_id
5135 |
5136 |
5137 | PURPOSE:
5138 |
5139 |
5140 | PARAMETERS:
5141 | IN
5142 |
5143 |
5144 | OUT
5145 |
5146 |
5147 | RETURNS:
5148 |
5149 | NOTES:
5150 |
5151 *---------------------------------------------------------------------*/
5152 FUNCTION get_le_from_bankacct_id(
5153 l_intbankacct_id IN IBY_TRXN_SUMMARIES_ALL.payeeinstrid%TYPE)
5154 RETURN NUMBER
5155 IS
5156
5157 l_le_id NUMBER;
5158 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
5159 '.get_le_from_bankacct_id';
5160
5161 BEGIN
5162
5163 IF (l_intbankacct_id IS NOT NULL) THEN
5164
5165 SELECT
5166 iba.account_owner_org_id -- legal_entity_id
5167 INTO
5168 l_le_id
5169 FROM
5170 CE_BANK_ACCOUNTS iba
5171 WHERE
5172 iba.bank_account_id = l_intbankacct_id
5173 ;
5174
5175 END IF;
5176
5177 RETURN l_le_id;
5178
5179 EXCEPTION
5180 WHEN OTHERS THEN
5181 print_debuginfo(l_module_name, 'Exception occured when '
5182 || 'retrieving LE id for '
5183 || 'internal bank account id: '
5184 || l_intbankacct_id
5185 );
5186
5187 print_debuginfo(l_module_name, 'SQLCODE: ' || SQLCODE);
5188 print_debuginfo(l_module_name, 'SQLERRM: ' || SQLERRM);
5189 print_debuginfo(l_module_name, 'Returning NULL for LE id');
5190 RETURN NULL;
5191
5192 END get_le_from_bankacct_id;
5193
5194
5195 END IBY_FNDCPT_TRXN_PUB;