[Home] [Help]
39: -- Bug : 8763944
40: --Added last two columns in rec to get trxn_core info.
41: TYPE FuncCaptureOrder_rec_type IS RECORD (
42: TRXNMID IBY_TRXN_SUMMARIES_ALL.TRXNMID%TYPE,
43: -- From IBY_FNDCPT_TX_EXTENSIONS
44: TRXN_REF_NUMBER1 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER1%TYPE,
45: TRXN_REF_NUMBER2 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER2%TYPE,
46: PAYMENT_CHANNEL_CODE IBY_FNDCPT_TX_EXTENSIONS.PAYMENT_CHANNEL_CODE%TYPE,
47: PO_NUMBER IBY_FNDCPT_TX_EXTENSIONS.PO_NUMBER%TYPE,
40: --Added last two columns in rec to get trxn_core info.
41: TYPE FuncCaptureOrder_rec_type IS RECORD (
42: TRXNMID IBY_TRXN_SUMMARIES_ALL.TRXNMID%TYPE,
43: -- From IBY_FNDCPT_TX_EXTENSIONS
44: TRXN_REF_NUMBER1 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER1%TYPE,
45: TRXN_REF_NUMBER2 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER2%TYPE,
46: PAYMENT_CHANNEL_CODE IBY_FNDCPT_TX_EXTENSIONS.PAYMENT_CHANNEL_CODE%TYPE,
47: PO_NUMBER IBY_FNDCPT_TX_EXTENSIONS.PO_NUMBER%TYPE,
48: VOICE_AUTHORIZATION_FLAG IBY_FNDCPT_TX_EXTENSIONS.VOICE_AUTHORIZATION_FLAG%TYPE,
41: TYPE FuncCaptureOrder_rec_type IS RECORD (
42: TRXNMID IBY_TRXN_SUMMARIES_ALL.TRXNMID%TYPE,
43: -- From IBY_FNDCPT_TX_EXTENSIONS
44: TRXN_REF_NUMBER1 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER1%TYPE,
45: TRXN_REF_NUMBER2 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER2%TYPE,
46: PAYMENT_CHANNEL_CODE IBY_FNDCPT_TX_EXTENSIONS.PAYMENT_CHANNEL_CODE%TYPE,
47: PO_NUMBER IBY_FNDCPT_TX_EXTENSIONS.PO_NUMBER%TYPE,
48: VOICE_AUTHORIZATION_FLAG IBY_FNDCPT_TX_EXTENSIONS.VOICE_AUTHORIZATION_FLAG%TYPE,
49: PAYMENT_FUNCTION IBY_EXTERNAL_PAYERS_ALL.PAYMENT_FUNCTION%TYPE,
42: TRXNMID IBY_TRXN_SUMMARIES_ALL.TRXNMID%TYPE,
43: -- From IBY_FNDCPT_TX_EXTENSIONS
44: TRXN_REF_NUMBER1 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER1%TYPE,
45: TRXN_REF_NUMBER2 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER2%TYPE,
46: PAYMENT_CHANNEL_CODE IBY_FNDCPT_TX_EXTENSIONS.PAYMENT_CHANNEL_CODE%TYPE,
47: PO_NUMBER IBY_FNDCPT_TX_EXTENSIONS.PO_NUMBER%TYPE,
48: VOICE_AUTHORIZATION_FLAG IBY_FNDCPT_TX_EXTENSIONS.VOICE_AUTHORIZATION_FLAG%TYPE,
49: PAYMENT_FUNCTION IBY_EXTERNAL_PAYERS_ALL.PAYMENT_FUNCTION%TYPE,
50: -- From the ORDERS View
43: -- From IBY_FNDCPT_TX_EXTENSIONS
44: TRXN_REF_NUMBER1 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER1%TYPE,
45: TRXN_REF_NUMBER2 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER2%TYPE,
46: PAYMENT_CHANNEL_CODE IBY_FNDCPT_TX_EXTENSIONS.PAYMENT_CHANNEL_CODE%TYPE,
47: PO_NUMBER IBY_FNDCPT_TX_EXTENSIONS.PO_NUMBER%TYPE,
48: VOICE_AUTHORIZATION_FLAG IBY_FNDCPT_TX_EXTENSIONS.VOICE_AUTHORIZATION_FLAG%TYPE,
49: PAYMENT_FUNCTION IBY_EXTERNAL_PAYERS_ALL.PAYMENT_FUNCTION%TYPE,
50: -- From the ORDERS View
51: CALL_APP_SERVICE_REQ_CODE IBY_TRXN_SUMMARIES_ALL.CALL_APP_SERVICE_REQ_CODE%TYPE,
44: TRXN_REF_NUMBER1 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER1%TYPE,
45: TRXN_REF_NUMBER2 IBY_FNDCPT_TX_EXTENSIONS.TRXN_REF_NUMBER2%TYPE,
46: PAYMENT_CHANNEL_CODE IBY_FNDCPT_TX_EXTENSIONS.PAYMENT_CHANNEL_CODE%TYPE,
47: PO_NUMBER IBY_FNDCPT_TX_EXTENSIONS.PO_NUMBER%TYPE,
48: VOICE_AUTHORIZATION_FLAG IBY_FNDCPT_TX_EXTENSIONS.VOICE_AUTHORIZATION_FLAG%TYPE,
49: PAYMENT_FUNCTION IBY_EXTERNAL_PAYERS_ALL.PAYMENT_FUNCTION%TYPE,
50: -- From the ORDERS View
51: CALL_APP_SERVICE_REQ_CODE IBY_TRXN_SUMMARIES_ALL.CALL_APP_SERVICE_REQ_CODE%TYPE,
52: --CALLING_APP_DOC_REF_NUMBER IBY_TRXN_SUMMARIES_ALL.CALLING_APP_DOC_REF_NUMBER%TYPE,
55: BR_MATURITY_DATE IBY_TRXN_SUMMARIES_ALL.BR_MATURITY_DATE%TYPE,
56: ORDER_DESCRIPTION VARCHAR2(255),
57: SETTLEMENT_CURRENCY_CODE IBY_TRXN_SUMMARIES_ALL.CURRENCYNAMECODE%TYPE,
58: SETTLEMENT_AMOUNT IBY_TRXN_SUMMARIES_ALL.AMOUNT%TYPE,
59: PAYMENT_TRXN_EXTENSION_ID IBY_FNDCPT_TX_EXTENSIONS.TRXN_EXTENSION_ID%TYPE,
60: INTERNAL_BANK_ACCOUNT_ID CE_BANK_ACCOUNTS.BANK_ACCOUNT_ID%TYPE,
61: PAYER_PARTY_ID IBY_EXTERNAL_PAYERS_ALL.PARTY_ID%TYPE,
62: CUST_ACCOUNT_ID IBY_TRXN_SUMMARIES_ALL.CUST_ACCOUNT_ID%TYPE,
63: ACCT_SITE_USE_ID IBY_TRXN_SUMMARIES_ALL.ACCT_SITE_USE_ID%TYPE,
234: --
235: -- Use: Determine the reason extension lookup failed in the auth API
236: --
237: FUNCTION Get_Extension_Auth_Fail
238: (p_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
239: p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type
240: )
241: RETURN VARCHAR2
242: IS
241: RETURN VARCHAR2
242: IS
243: l_msg VARCHAR2(100);
244: l_instr_assign_id iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE;
245: l_ext_payer_id iby_fndcpt_tx_extensions.ext_payer_id%TYPE;
246:
247: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Get_Extension_Auth_Fail';
248: BEGIN
249:
257:
258:
259: SELECT instr_assignment_id, ext_payer_id
260: INTO l_instr_assign_id, l_ext_payer_id
261: FROM iby_fndcpt_tx_extensions
262: WHERE (p_trxn_extension_id = trxn_extension_id);
263: EXCEPTION
264: WHEN NO_DATA_FOUND THEN
265: RETURN l_msg;
313: END Get_Extension_Encrypt_Mode;
314:
315: FUNCTION Get_Tangible_Id
316: (p_app_short_name IN fnd_application.application_short_name%TYPE,
317: p_order_id IN iby_fndcpt_tx_extensions.order_id%TYPE,
318: p_trxn_ref1 IN iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE,
319: p_trxn_ref2 IN iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE
320: )
321: RETURN iby_trxn_summaries_all.tangibleid%TYPE
314:
315: FUNCTION Get_Tangible_Id
316: (p_app_short_name IN fnd_application.application_short_name%TYPE,
317: p_order_id IN iby_fndcpt_tx_extensions.order_id%TYPE,
318: p_trxn_ref1 IN iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE,
319: p_trxn_ref2 IN iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE
320: )
321: RETURN iby_trxn_summaries_all.tangibleid%TYPE
322: IS
315: FUNCTION Get_Tangible_Id
316: (p_app_short_name IN fnd_application.application_short_name%TYPE,
317: p_order_id IN iby_fndcpt_tx_extensions.order_id%TYPE,
318: p_trxn_ref1 IN iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE,
319: p_trxn_ref2 IN iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE
320: )
321: RETURN iby_trxn_summaries_all.tangibleid%TYPE
322: IS
323: l_tangible_id iby_trxn_summaries_all.tangibleid%TYPE;
345:
346: --Overloading this function for bug : 7628586
347: FUNCTION Get_Tangible_Id
348: (p_app_short_name IN fnd_application.application_short_name%TYPE,
349: p_trxn_extn_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE
350: )
351: RETURN iby_trxn_summaries_all.tangibleid%TYPE
352: IS
353: l_tangible_id iby_trxn_summaries_all.tangibleid%TYPE;
385: -- New Function to generate PSON for a re-authorization
386: FUNCTION Get_Reauth_Tangible_Id
387: (
388: p_app_short_name IN fnd_application.application_short_name%TYPE,
389: p_trxn_extn_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE
390: )
391: RETURN iby_trxn_summaries_all.tangibleid%TYPE
392: IS
393: l_tangible_id iby_trxn_summaries_all.tangibleid%TYPE;
408: -- l_tangible_id := p_app_short_name || '_' || p_trxn_extn_id;
409: l_tangible_id := Get_Tangible_Id(p_app_short_name, p_trxn_extn_id);
410:
411: SELECT payment_system_order_number INTO l_prev_pson
412: FROM iby_fndcpt_tx_extensions
413: WHERE trxn_extension_id = p_trxn_extn_id;
414:
415: IF(l_prev_pson IS NOT NULL) THEN
416: iby_debug_pub.add('PSON for earlier attempt: '||l_prev_pson,G_LEVEL_STATEMENT,l_dbg_mod);
469: RETURN l_payeeid;
470: END Get_Internal_Payee;
471:
472: FUNCTION Get_Tx_Extension_Copy_Count
473: (p_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
474: RETURN NUMBER
475: IS
476: l_copy_count NUMBER;
477:
474: RETURN NUMBER
475: IS
476: l_copy_count NUMBER;
477:
478: CURSOR c_xe_copies(ci_x_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
479: IS
480: SELECT COUNT(copy_trxn_extension_id)
481: FROM iby_fndcpt_tx_xe_copies
482: WHERE (source_trxn_extension_id = ci_x_id);
981: END Payer_InstrAssignment_Valid;
982:
983:
984: FUNCTION Extension_Operation_Exists
985: (p_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
986: RETURN BOOLEAN
987: IS
988: l_count NUMBER;
989: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Extension_Valid';
988: l_count NUMBER;
989: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.Extension_Valid';
990:
991: CURSOR c_operation
992: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
993: IS
994: SELECT count(1)
995: FROM iby_trxn_extensions_v
996: WHERE (trxn_extension_id = ci_extension_id)
1277: -- test_debug('l_segment_id: '|| l_segment_id);
1278: -- test_debug('l_encrypted: '|| l_encrypted);
1279: -- test_debug('l_sec_code: '|| l_sec_code);
1280:
1281: SELECT iby_fndcpt_tx_extensions_s.NEXTVAL INTO x_entity_id FROM DUAL;
1282:
1283: INSERT INTO iby_fndcpt_tx_extensions
1284: (trxn_extension_id, payment_channel_code, ext_payer_id,
1285: instr_assignment_id, origin_application_id, instrument_security_code,
1279: -- test_debug('l_sec_code: '|| l_sec_code);
1280:
1281: SELECT iby_fndcpt_tx_extensions_s.NEXTVAL INTO x_entity_id FROM DUAL;
1282:
1283: INSERT INTO iby_fndcpt_tx_extensions
1284: (trxn_extension_id, payment_channel_code, ext_payer_id,
1285: instr_assignment_id, origin_application_id, instrument_security_code,
1286: voice_authorization_flag, voice_authorization_date,
1287: voice_authorization_code, order_id, po_number,
1386: l_payer_level VARCHAR2(30);
1387: l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
1388: l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
1389:
1390: l_xtension_id iby_fndcpt_tx_extensions.trxn_extension_id%TYPE;
1391: l_pmt_channel iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE;
1392: l_instr_assign iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE;
1393: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
1394: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
1389:
1390: l_xtension_id iby_fndcpt_tx_extensions.trxn_extension_id%TYPE;
1391: l_pmt_channel iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE;
1392: l_instr_assign iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE;
1393: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
1394: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
1395: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
1396: l_app_id iby_fndcpt_tx_extensions.origin_application_id%TYPE;
1397: -- l_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
1390: l_xtension_id iby_fndcpt_tx_extensions.trxn_extension_id%TYPE;
1391: l_pmt_channel iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE;
1392: l_instr_assign iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE;
1393: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
1394: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
1395: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
1396: l_app_id iby_fndcpt_tx_extensions.origin_application_id%TYPE;
1397: -- l_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
1398:
1391: l_pmt_channel iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE;
1392: l_instr_assign iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE;
1393: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
1394: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
1395: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
1396: l_app_id iby_fndcpt_tx_extensions.origin_application_id%TYPE;
1397: -- l_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
1398:
1399: lx_channel_instr iby_fndcpt_pmt_chnnls_b.instrument_type%TYPE;
1392: l_instr_assign iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE;
1393: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
1394: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
1395: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
1396: l_app_id iby_fndcpt_tx_extensions.origin_application_id%TYPE;
1397: -- l_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
1398:
1399: lx_channel_instr iby_fndcpt_pmt_chnnls_b.instrument_type%TYPE;
1400:
1393: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
1394: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
1395: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
1396: l_app_id iby_fndcpt_tx_extensions.origin_application_id%TYPE;
1397: -- l_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
1398:
1399: lx_channel_instr iby_fndcpt_pmt_chnnls_b.instrument_type%TYPE;
1400:
1401: l_msg_count NUMBER;
1410:
1411: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || l_module;
1412:
1413: CURSOR c_extension
1414: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
1415: ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1416: ci_payer_level IN VARCHAR2,
1417: ci_payer_equiv IN VARCHAR2
1418: )
1420: SELECT
1421: x.trxn_extension_id, x.payment_channel_code, x.instr_assignment_id,
1422: x.order_id, x.trxn_ref_number1, x.trxn_ref_number2,
1423: x.origin_application_id, x.instr_code_sec_segment_id
1424: FROM iby_fndcpt_tx_extensions x, iby_external_payers_all p
1425: WHERE (x.ext_payer_id = p.ext_payer_id)
1426: AND (x.trxn_extension_id = ci_extension_id)
1427: AND (p.party_id = ci_payer.Party_Id)
1428: AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
1657: module => G_DEBUG_MODULE || l_module);
1658: END IF;
1659: l_segment_id := null;
1660: -- Bug# 13893195
1661: -- Since the 'ENCRYPTED' column in iby_fndcpt_tx_extensions is not nullable,
1662: -- Setting the value to 'N'
1663: l_encrypted := 'N';
1664: l_sec_code := p_trxn_attribs.Instrument_Security_Code;
1665: END IF;
1679: -- test_debug('encrypted: '||);
1680: --Bug# 14690786
1681: --When XXX or XXXX is passes as instrument security code, leave the original
1682: --as it is. Included these two values in the decode
1683: UPDATE iby_fndcpt_tx_extensions
1684: SET
1685: payment_channel_code = NVL(p_pmt_channel,payment_channel_code),
1686: instr_assignment_id =
1687: DECODE(p_instr_assignment, FND_API.G_MISS_NUM,NULL,
1830:
1831: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || l_module;
1832:
1833: CURSOR c_extension
1834: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
1835: IS
1836: SELECT
1837: origin_application_id, RPAD('X',instr_sec_code_length,'X'),
1838: voice_authorization_flag, voice_authorization_date,
1837: origin_application_id, RPAD('X',instr_sec_code_length,'X'),
1838: voice_authorization_flag, voice_authorization_date,
1839: voice_authorization_code, order_id, po_number,
1840: po_line_number, trxn_ref_number1, trxn_ref_number2, additional_info
1841: FROM iby_fndcpt_tx_extensions
1842: WHERE trxn_extension_id = ci_extension_id;
1843:
1844: BEGIN
1845: IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1990:
1991: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || l_module;
1992:
1993: CURSOR c_extension
1994: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
1995: ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1996: ci_payer_level IN VARCHAR2,
1997: ci_payer_equiv IN VARCHAR2
1998: )
1997: ci_payer_equiv IN VARCHAR2
1998: )
1999: IS
2000: SELECT x.trxn_extension_id
2001: FROM iby_fndcpt_tx_extensions x, iby_external_payers_all p,
2002: iby_pmt_instr_uses_all i
2003: WHERE (x.trxn_extension_id = ci_extension_id)
2004: AND (x.instr_assignment_id = i.instrument_payment_use_id(+))
2005: AND (NVL(x.ext_payer_id,i.ext_pmt_party_id) = p.ext_payer_id)
2070: -- delete all links to its source extensions
2071: DELETE iby_fndcpt_tx_xe_copies
2072: WHERE (copy_trxn_extension_id = p_entity_id);
2073:
2074: DELETE iby_fndcpt_tx_extensions
2075: WHERE (trxn_extension_id = p_entity_id);
2076: x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
2077: END IF;
2078: ELSE
2170: l_payer_level VARCHAR2(30);
2171: l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
2172: l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
2173:
2174: l_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2175: l_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2176: lc_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2177: lc_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2178: lc_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
2171: l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
2172: l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
2173:
2174: l_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2175: l_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2176: lc_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2177: lc_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2178: lc_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
2179: lc_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2172: l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
2173:
2174: l_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2175: l_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2176: lc_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2177: lc_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2178: lc_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
2179: lc_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2180: lc_va_flag iby_fndcpt_tx_extensions.voice_authorization_flag%TYPE;
2173:
2174: l_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2175: l_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2176: lc_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2177: lc_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2178: lc_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
2179: lc_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2180: lc_va_flag iby_fndcpt_tx_extensions.voice_authorization_flag%TYPE;
2181: lc_va_code iby_fndcpt_tx_extensions.voice_authorization_code%TYPE;
2174: l_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2175: l_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2176: lc_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2177: lc_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2178: lc_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
2179: lc_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2180: lc_va_flag iby_fndcpt_tx_extensions.voice_authorization_flag%TYPE;
2181: lc_va_code iby_fndcpt_tx_extensions.voice_authorization_code%TYPE;
2182: lc_va_date iby_fndcpt_tx_extensions.voice_authorization_date%TYPE;
2175: l_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2176: lc_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2177: lc_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2178: lc_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
2179: lc_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2180: lc_va_flag iby_fndcpt_tx_extensions.voice_authorization_flag%TYPE;
2181: lc_va_code iby_fndcpt_tx_extensions.voice_authorization_code%TYPE;
2182: lc_va_date iby_fndcpt_tx_extensions.voice_authorization_date%TYPE;
2183: lc_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2176: lc_pmt_channel iby_fndcpt_tx_extensions.payment_channel_code%TYPE;
2177: lc_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2178: lc_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
2179: lc_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2180: lc_va_flag iby_fndcpt_tx_extensions.voice_authorization_flag%TYPE;
2181: lc_va_code iby_fndcpt_tx_extensions.voice_authorization_code%TYPE;
2182: lc_va_date iby_fndcpt_tx_extensions.voice_authorization_date%TYPE;
2183: lc_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2184: lc_instr_code_sec_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2177: lc_instr_assign_id iby_fndcpt_tx_extensions.instr_assignment_id%TYPE;
2178: lc_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
2179: lc_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2180: lc_va_flag iby_fndcpt_tx_extensions.voice_authorization_flag%TYPE;
2181: lc_va_code iby_fndcpt_tx_extensions.voice_authorization_code%TYPE;
2182: lc_va_date iby_fndcpt_tx_extensions.voice_authorization_date%TYPE;
2183: lc_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2184: lc_instr_code_sec_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2185:
2178: lc_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
2179: lc_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2180: lc_va_flag iby_fndcpt_tx_extensions.voice_authorization_flag%TYPE;
2181: lc_va_code iby_fndcpt_tx_extensions.voice_authorization_code%TYPE;
2182: lc_va_date iby_fndcpt_tx_extensions.voice_authorization_date%TYPE;
2183: lc_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2184: lc_instr_code_sec_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2185:
2186: l_extension TrxnExtension_rec_type;
2179: lc_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2180: lc_va_flag iby_fndcpt_tx_extensions.voice_authorization_flag%TYPE;
2181: lc_va_code iby_fndcpt_tx_extensions.voice_authorization_code%TYPE;
2182: lc_va_date iby_fndcpt_tx_extensions.voice_authorization_date%TYPE;
2183: lc_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2184: lc_instr_code_sec_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2185:
2186: l_extension TrxnExtension_rec_type;
2187: l_x_not_found BOOLEAN;
2180: lc_va_flag iby_fndcpt_tx_extensions.voice_authorization_flag%TYPE;
2181: lc_va_code iby_fndcpt_tx_extensions.voice_authorization_code%TYPE;
2182: lc_va_date iby_fndcpt_tx_extensions.voice_authorization_date%TYPE;
2183: lc_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2184: lc_instr_code_sec_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2185:
2186: l_extension TrxnExtension_rec_type;
2187: l_x_not_found BOOLEAN;
2188: -- whether the new instrument assignment (if passed) was validated
2204: l_segment_id NUMBER;
2205:
2206:
2207: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || l_module;
2208: lc_PSON iby_fndcpt_tx_extensions.payment_system_order_number%TYPE;
2209: l_count NUMBER:=0;
2210:
2211: -- Bug : 8612180
2212: -- Changing cursor to get PSON Number with other information.
2210:
2211: -- Bug : 8612180
2212: -- Changing cursor to get PSON Number with other information.
2213: CURSOR c_extension
2214: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
2215: ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
2216: ci_payer_level IN VARCHAR2,
2217: ci_payer_equiv IN VARCHAR2,
2218: ci_copy_instr_assign_id IN
2223: x.instrument_security_code, x.instr_sec_code_length,
2224: x.voice_authorization_flag, x.voice_authorization_code,
2225: x.voice_authorization_date, x.encrypted, x.instr_code_sec_segment_id,
2226: x.payment_system_order_number
2227: FROM iby_fndcpt_tx_extensions x, iby_pmt_instr_uses_all i,
2228: iby_pmt_instr_uses_all cpi, iby_external_payers_all p
2229: WHERE (x.instr_assignment_id = i.instrument_payment_use_id(+))
2230: -- bug 11675835 - kept an outer join for instrument details
2231: -- can assume this assignment is for funds capture
2263: )
2264: ORDER BY x.creation_date DESC;
2265:
2266: CURSOR c_auths
2267: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
2268: IS
2269: SELECT NVL(sp.settle_require_vrfy_flag,'Y')
2270: FROM iby_trxn_ext_auths_v x, iby_fndcpt_sys_eft_pf_b sp,
2271: iby_fndcpt_user_eft_pf_b up
2584: IF (NOT Extension_Valid(l_pmt_channel,l_extension,FALSE,
2585: l_consumed_cvv2)) THEN
2586: x_response.Result_Code := G_RC_INVALID_EXTENSION_ATTRIB;
2587: ELSE
2588: SELECT iby_fndcpt_tx_extensions_s.NEXTVAL INTO x_entity_id FROM DUAL;
2589: -- Bug: 8612180
2590: -- Insert PSON Number from parent record when copying transaction extension.
2591: INSERT INTO iby_fndcpt_tx_extensions
2592: (trxn_extension_id, payment_channel_code,
2587: ELSE
2588: SELECT iby_fndcpt_tx_extensions_s.NEXTVAL INTO x_entity_id FROM DUAL;
2589: -- Bug: 8612180
2590: -- Insert PSON Number from parent record when copying transaction extension.
2591: INSERT INTO iby_fndcpt_tx_extensions
2592: (trxn_extension_id, payment_channel_code,
2593: ext_payer_id, instr_assignment_id,
2594: origin_application_id, instrument_security_code,
2595: instr_sec_code_length, encrypted, instr_code_sec_segment_id,
2735: -- We achieve this through the following autonomous transaction block
2736: --
2737: PROCEDURE Secure_Wipe_Segment
2738: (
2739: p_segment_id IN iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE
2740: )
2741: IS PRAGMA AUTONOMOUS_TRANSACTION;
2742: l_random_val NUMBER;
2743:
2810: l_single_use iby_fndcpt_payer_assgn_instr_v.card_single_use_flag%TYPE;
2811:
2812: l_ecapp_id NUMBER;
2813: l_app_short_name fnd_application.application_short_name%TYPE;
2814: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
2815: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
2816: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
2817: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2818: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2811:
2812: l_ecapp_id NUMBER;
2813: l_app_short_name fnd_application.application_short_name%TYPE;
2814: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
2815: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
2816: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
2817: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2818: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2819: l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2812: l_ecapp_id NUMBER;
2813: l_app_short_name fnd_application.application_short_name%TYPE;
2814: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
2815: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
2816: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
2817: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2818: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2819: l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2820: l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
2813: l_app_short_name fnd_application.application_short_name%TYPE;
2814: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
2815: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
2816: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
2817: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2818: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2819: l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2820: l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
2821: l_payer IBY_PAYMENT_ADAPTER_PUB.Payer_rec_type;
2814: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
2815: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
2816: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
2817: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2818: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2819: l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2820: l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
2821: l_payer IBY_PAYMENT_ADAPTER_PUB.Payer_rec_type;
2822: l_tangible IBY_PAYMENT_ADAPTER_PUB.Tangible_rec_type;
2815: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
2816: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
2817: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
2818: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2819: l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
2820: l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
2821: l_payer IBY_PAYMENT_ADAPTER_PUB.Payer_rec_type;
2822: l_tangible IBY_PAYMENT_ADAPTER_PUB.Tangible_rec_type;
2823: l_pmt_instr IBY_PAYMENT_ADAPTER_PUB.PmtInstr_rec_type;
2826: l_reqresp IBY_PAYMENT_ADAPTER_PUB.ReqResp_rec_type;
2827: l_status iby_trxn_summaries_all.status%TYPE;
2828: l_trxn_id IBY_TRXN_SUMMARIES_ALL.transactionid%TYPE;
2829: l_auth_result iby_trxn_ext_auths_v.authorization_result_code%TYPE;
2830: l_pson iby_fndcpt_tx_extensions.payment_system_order_number%TYPE;
2831: l_return_status VARCHAR2(1);
2832: l_msg_count NUMBER;
2833: l_msg_data VARCHAR2(300);
2834: l_fail_msg VARCHAR2(500);
2834: l_fail_msg VARCHAR2(500);
2835: l_op_count NUMBER;
2836: l_rec_mth_id NUMBER;
2837:
2838: l_tmp_segmdnt_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
2839: l_ext_not_found BOOLEAN;
2840:
2841: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
2842:
2840:
2841: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
2842:
2843: CURSOR c_extension
2844: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
2845: ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
2846: ci_payer_level IN VARCHAR2,
2847: ci_payer_equiv IN VARCHAR2
2848: )
2855: x.instr_sec_code_length, x.encrypted,
2856: x.po_number, x.voice_authorization_flag, x.voice_authorization_code,
2857: x.voice_authorization_date, i.card_single_use_flag,
2858: NVL(x.instr_assignment_id,0), x.payment_channel_code
2859: FROM iby_fndcpt_tx_extensions x, iby_fndcpt_payer_assgn_instr_v i,
2860: iby_external_payers_all p, fnd_application a,
2861: iby_fndcpt_pmt_chnnls_b pc
2862: WHERE (x.instr_assignment_id = i.instr_assignment_id(+))
2863: AND (x.payment_channel_code = pc.payment_channel_code)
2872: ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
2873: p.cust_account_id,p.acct_site_use_id) = 'T');
2874:
2875: /*CURSOR c_auth
2876: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
2877: IS
2878: SELECT authorized_flag
2879: FROM iby_trxn_extensions_v
2880: WHERE (trxn_extension_id = ci_extension_id); */
2881:
2882: --Bug# 16353469
2883: --Changed the cursor query to get the auth status from the base tables.
2884: CURSOR c_auth
2885: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
2886: IS
2887: SELECT
2888: a.transactionid,
2889: DECODE(a.status, 0, 'AUTH_SUCCESS', 1, 'COMMUNICATION_ERROR', 2, 'DUPLICATE_AUTH', 4,
2893: 'AUTH_PENDING', 101, 'COMMUNICATION_ERROR', 111, 'AUTH_PENDING', 22,
2894: 'FULLY_REVERSED', 23, 'PARTIALLY_REVERSED', 'GENERAL_SYS_ERROR') auth_result_code,
2895: x.payment_system_order_number
2896: FROM
2897: IBY_FNDCPT_TX_EXTENSIONS X,
2898: IBY_FNDCPT_TX_OPERATIONS O,
2899: IBY_TRXN_SUMMARIES_ALL A,
2900: IBY_TRXN_CORE C
2901: WHERE x.trxn_extension_id = ci_extension_id
2918: AND (instrument_type = ci_instr_type)
2919: AND (trxn_extension_id <> ci_trxn_x_id);
2920:
2921: CURSOR c_operation_count
2922: (ci_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
2923: ci_trxn_id IN iby_trxn_summaries_all.transactionid%TYPE)
2924: IS
2925: SELECT count(1)
2926: FROM iby_fndcpt_tx_operations o
2928: AND o.trxn_extension_id = ci_trxn_extension_id;
2929:
2930: CURSOR c_source_extns
2931: (
2932: ci_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE
2933: )
2934: IS
2935: SELECT cp.source_trxn_extension_id, ex.instr_code_sec_segment_id, ex.instrument_security_code
2936: FROM iby_fndcpt_tx_xe_copies cp, iby_fndcpt_tx_extensions ex
2932: ci_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE
2933: )
2934: IS
2935: SELECT cp.source_trxn_extension_id, ex.instr_code_sec_segment_id, ex.instrument_security_code
2936: FROM iby_fndcpt_tx_xe_copies cp, iby_fndcpt_tx_extensions ex
2937: WHERE cp.source_trxn_extension_id = ex.trxn_extension_id
2938: START WITH cp.copy_trxn_extension_id = ci_trxn_extension_id
2939: CONNECT BY PRIOR source_trxn_extension_id = copy_trxn_extension_id;
2940:
3202: END IF;
3203: END IF;
3204:
3205: -- consume the security code
3206: UPDATE iby_fndcpt_tx_extensions
3207: SET instrument_security_code = NULL,
3208: --instr_sec_code_length = NULL,
3209: encrypted = 'N',
3210: last_updated_by = fnd_global.user_id,
3212: last_update_login = fnd_global.login_id,
3213: object_version_number = object_version_number + 1
3214: WHERE trxn_extension_id = p_trxn_entity_id;
3215:
3216: UPDATE iby_fndcpt_tx_extensions
3217: SET instrument_security_code = NULL,
3218: --instr_sec_code_length = NULL,
3219: encrypted = 'N',
3220: last_updated_by = fnd_global.user_id,
3273: END IF;
3274: END;
3275:
3276: -- Fix for bug# 7377455. Stamp the tangibleid on the PSON column of
3277: -- IBY_FNDCPT_TX_EXTENSIONS table
3278: IF( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3279: iby_debug_pub.add( 'Stamping the PSON on the extension as '||
3280: l_tangible.Tangible_Id,
3281: G_LEVEL_STATEMENT,l_dbg_mod);
3280: l_tangible.Tangible_Id,
3281: G_LEVEL_STATEMENT,l_dbg_mod);
3282: END IF;
3283:
3284: UPDATE iby_fndcpt_tx_extensions
3285: SET payment_system_order_number = l_tangible.Tangible_Id
3286: WHERE trxn_extension_id = p_trxn_entity_id;
3287:
3288: -- Fix for bug# 7530578. Stamp the initiator transaction extension id
3554: l_module CONSTANT VARCHAR2(30) := 'Get_Authorization';
3555: l_prev_msg_count NUMBER;
3556:
3557: CURSOR c_auth
3558: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
3559: IS
3560: SELECT authorization_id, authorization_date, authorization_code,
3561: avs_code, instr_sec_code_check, pmt_sys_err_code, pmt_sys_err_msg
3562: FROM iby_trxn_ext_auths_v
3789: lx_msg_data VARCHAR2(3000);
3790:
3791:
3792: l_app_short_name fnd_application.application_short_name%TYPE;
3793: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
3794: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
3795: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
3796: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
3797: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
3790:
3791:
3792: l_app_short_name fnd_application.application_short_name%TYPE;
3793: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
3794: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
3795: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
3796: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
3797: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
3798: l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
3791:
3792: l_app_short_name fnd_application.application_short_name%TYPE;
3793: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
3794: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
3795: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
3796: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
3797: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
3798: l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
3799: l_payer IBY_PAYMENT_ADAPTER_PUB.Payer_rec_type;
3792: l_app_short_name fnd_application.application_short_name%TYPE;
3793: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
3794: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
3795: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
3796: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
3797: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
3798: l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
3799: l_payer IBY_PAYMENT_ADAPTER_PUB.Payer_rec_type;
3800: l_tangible IBY_PAYMENT_ADAPTER_PUB.Tangible_rec_type;
3793: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
3794: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
3795: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
3796: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
3797: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
3798: l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
3799: l_payer IBY_PAYMENT_ADAPTER_PUB.Payer_rec_type;
3800: l_tangible IBY_PAYMENT_ADAPTER_PUB.Tangible_rec_type;
3801: l_pmt_instr IBY_PAYMENT_ADAPTER_PUB.PmtInstr_rec_type;
3807:
3808: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
3809:
3810: CURSOR c_extension
3811: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
3812: ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
3813: ci_payer_level IN VARCHAR2,
3814: ci_payer_equiv IN VARCHAR2
3815: )
3820: x.instrument_security_code, x.instr_code_sec_segment_id, x.encrypted,
3821: x.po_number, x.voice_authorization_flag, x.voice_authorization_code,
3822: x.voice_authorization_date, NVL(x.instr_assignment_id,0),
3823: x.payment_channel_code
3824: FROM iby_fndcpt_tx_extensions x, iby_pmt_instr_uses_all i,
3825: iby_external_payers_all p, fnd_application a,
3826: iby_fndcpt_pmt_chnnls_b pc
3827: WHERE (x.instr_assignment_id = i.instrument_payment_use_id(+))
3828: AND (x.payment_channel_code = pc.payment_channel_code)
3837: ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
3838: p.cust_account_id,p.acct_site_use_id) = 'T');
3839:
3840: CURSOR c_auth
3841: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
3842: IS
3843: SELECT authorization_id
3844: FROM iby_trxn_ext_auths_v
3845: WHERE (trxn_extension_id = ci_extension_id)
3845: WHERE (trxn_extension_id = ci_extension_id)
3846: AND (authorization_status = 0);
3847:
3848:
3849: CURSOR C_PAYEE (ci_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE) IS
3850: select s.payeeid, s.org_id, s.org_type
3851: from iby_trxn_summaries_all s, iby_fndcpt_tx_operations o
3852: where s.trxntypeid = 20
3853: and s.transactionid = o.transactionid
3853: and s.transactionid = o.transactionid
3854: and o.trxn_extension_id = ci_trxn_extension_id;
3855:
3856: CURSOR c_operation_count
3857: (ci_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
3858: ci_trxn_id IN iby_trxn_summaries_all.transactionid%TYPE)
3859: IS
3860: SELECT count(1)
3861: FROM iby_fndcpt_tx_operations o
4016: to_char(l_reqresp.Trxn_ID),
4017: G_LEVEL_STATEMENT,l_dbg_mod);
4018:
4019: -- Fix for bug# 7377455. Stamp the tangibleid on the PSON column of
4020: -- IBY_FNDCPT_TX_EXTENSIONS table
4021: iby_debug_pub.add( 'Stamping the PSON on the extension as '||
4022: l_tangible.Tangible_Id,
4023: G_LEVEL_STATEMENT,l_dbg_mod);
4024: END IF;
4022: l_tangible.Tangible_Id,
4023: G_LEVEL_STATEMENT,l_dbg_mod);
4024: END IF;
4025:
4026: UPDATE iby_fndcpt_tx_extensions
4027: SET payment_system_order_number = l_tangible.Tangible_Id
4028: WHERE trxn_extension_id = p_trxn_entity_id;
4029:
4030: -- Fix for bug# 7530578. Stamp the initiator transaction extension id
4401: -- attribute of an extension.
4402: -- This is the only attribute that we are populating in this table after a settlement
4403: -- so we will keep only this record in the record. We may add more in future
4404: TYPE Pson_rec_type IS RECORD (
4405: extension_id IBY_FNDCPT_TX_EXTENSIONS.trxn_extension_id%TYPE,
4406: tangibleid IBY_FNDCPT_TX_EXTENSIONS.payment_system_order_number%TYPE
4407: );
4408:
4409: TYPE PsonTabType IS TABLE OF Pson_rec_type
4402: -- This is the only attribute that we are populating in this table after a settlement
4403: -- so we will keep only this record in the record. We may add more in future
4404: TYPE Pson_rec_type IS RECORD (
4405: extension_id IBY_FNDCPT_TX_EXTENSIONS.trxn_extension_id%TYPE,
4406: tangibleid IBY_FNDCPT_TX_EXTENSIONS.payment_system_order_number%TYPE
4407: );
4408:
4409: TYPE PsonTabType IS TABLE OF Pson_rec_type
4410: INDEX BY BINARY_INTEGER;
4783: auth.dirdeb_instruction_code auth_dirdeb_instruction_code,
4784: auth_trxn_core.card_subtype_code card_subtype_code,
4785: auth_trxn_core.card_data_level card_data_level
4786: FROM '||l_view_name||' orders,
4787: IBY_FNDCPT_TX_EXTENSIONS trxnext,
4788: IBY_EXTERNAL_PAYERS_ALL payer,
4789: IBY_TRXN_SUMMARIES_ALL auth,
4790: IBY_FNDCPT_TX_OPERATIONS trxnop,
4791: IBY_TRXN_SUMMARIES_ALL captures,
5337: ELSE
5338:
5339: select count(*) into l_mandate_trxn_count
5340: from iby_trxn_summaries_all summ
5341: ,iby_fndcpt_tx_extensions ext
5342: ,iby_fndcpt_tx_operations op
5343: where summ.transactionid = op.transactionid
5344: and op.trxn_extension_id = ext.trxn_extension_id
5345: and op.transactionid = l_settlement_trxn_rec.auth_transactionid
5453:
5454: IF (l_pson_Tab.COUNT > 0) THEN
5455:
5456: -- Fix for bug# 7377455. Stamp the tangibleid on the PSON column of
5457: -- IBY_FNDCPT_TX_EXTENSIONS table
5458: -- FORALL j IN l_pson_Tab.first..l_pson_Tab.last
5459: -- UPDATE iby_fndcpt_tx_extensions
5460: -- SET payment_system_order_number = TREAT(l_pson_Tab(j) AS Pson_rec_type).tangibleid
5461: -- WHERE trxn_extension_id = TREAT(l_pson_Tab(j) AS Pson_rec_type).extension_id;
5455:
5456: -- Fix for bug# 7377455. Stamp the tangibleid on the PSON column of
5457: -- IBY_FNDCPT_TX_EXTENSIONS table
5458: -- FORALL j IN l_pson_Tab.first..l_pson_Tab.last
5459: -- UPDATE iby_fndcpt_tx_extensions
5460: -- SET payment_system_order_number = TREAT(l_pson_Tab(j) AS Pson_rec_type).tangibleid
5461: -- WHERE trxn_extension_id = TREAT(l_pson_Tab(j) AS Pson_rec_type).extension_id;
5462:
5463: FOR i IN 0..(l_pson_Tab.COUNT-1) LOOP --index starts from 0 and ends at (COUNT-1)
5465: IF( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5466: iby_debug_pub.add('updating extension '|| l_pson_Tab(i).extension_id
5467: ||' with PSON '||l_pson_Tab(i).tangibleid, G_LEVEL_STATEMENT,l_module_name);
5468: END IF;
5469: UPDATE iby_fndcpt_tx_extensions
5470: SET payment_system_order_number = l_pson_Tab(i).tangibleid,
5471: object_version_number = object_version_number + 1
5472: WHERE trxn_extension_id = l_pson_Tab(i).extension_id;
5473: END LOOP;
5619: l_extension_found BOOLEAN;
5620:
5621: l_ecapp_id NUMBER;
5622: l_app_short_name fnd_application.application_short_name%TYPE;
5623: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
5624: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
5625: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
5626: l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
5627: l_tangible IBY_PAYMENT_ADAPTER_PUB.Tangible_rec_type;
5620:
5621: l_ecapp_id NUMBER;
5622: l_app_short_name fnd_application.application_short_name%TYPE;
5623: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
5624: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
5625: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
5626: l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
5627: l_tangible IBY_PAYMENT_ADAPTER_PUB.Tangible_rec_type;
5628: l_pmt_instr IBY_PAYMENT_ADAPTER_PUB.PmtInstr_rec_type;
5621: l_ecapp_id NUMBER;
5622: l_app_short_name fnd_application.application_short_name%TYPE;
5623: l_order_id iby_fndcpt_tx_extensions.order_id%TYPE;
5624: l_trxn_ref1 iby_fndcpt_tx_extensions.trxn_ref_number1%TYPE;
5625: l_trxn_ref2 iby_fndcpt_tx_extensions.trxn_ref_number2%TYPE;
5626: l_payee IBY_PAYMENT_ADAPTER_PUB.Payee_rec_type;
5627: l_tangible IBY_PAYMENT_ADAPTER_PUB.Tangible_rec_type;
5628: l_pmt_instr IBY_PAYMENT_ADAPTER_PUB.PmtInstr_rec_type;
5629: l_return_trxn IBY_PAYMENT_ADAPTER_PUB.ReturnTrxn_rec_type;
5642: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
5643:
5644:
5645: CURSOR c_extension
5646: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
5647: ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
5648: ci_payer_level IN VARCHAR2,
5649: ci_payer_equiv IN VARCHAR2
5650: )
5654: x.origin_application_id, a.application_short_name,
5655: x.order_id, x.trxn_ref_number1, x.trxn_ref_number2,
5656: x.payment_channel_code
5657: FROM iby_trxn_extensions_v x,
5658: iby_fndcpt_tx_extensions xt,
5659: -- iby_pmt_instr_uses_all i,
5660: iby_external_payers_all p,
5661: fnd_application a
5662: -- [lmallick] - Validate the payer context against the payer
5675: ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
5676: p.cust_account_id,p.acct_site_use_id) = 'T');
5677:
5678: CURSOR c_settlement
5679: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
5680: IS
5681: SELECT settlement_id, payee_id, org_id, payment_system_order_number
5682: FROM iby_trxn_ext_settlements_v
5683: WHERE (trxn_extension_id = ci_extension_id)
5815: START WITH copy_trxn_extension_id = p_trxn_entity_id
5816: CONNECT BY source_trxn_extension_id = PRIOR copy_trxn_extension_id;
5817:
5818: -- Fix for bug# 7377455. Stamp the tangibleid on the PSON column of
5819: -- IBY_FNDCPT_TX_EXTENSIONS table
5820: IF( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5821: iby_debug_pub.add( 'Stamping the PSON on the extension as '||
5822: l_tangible.Tangible_Id,
5823: G_LEVEL_STATEMENT,G_DEBUG_MODULE || l_module);
5821: iby_debug_pub.add( 'Stamping the PSON on the extension as '||
5822: l_tangible.Tangible_Id,
5823: G_LEVEL_STATEMENT,G_DEBUG_MODULE || l_module);
5824: END IF;
5825: UPDATE iby_fndcpt_tx_extensions
5826: SET payment_system_order_number = l_tangible.Tangible_Id
5827: WHERE trxn_extension_id = p_trxn_entity_id;
5828:
5829: -- Fix for bug# 7530578. Stamp the initiator transaction extension id
5865: x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR;
5866: l_interface_code := IBY_FNDCPT_COMMON_PUB.G_INTERFACE_CODE;
5867:
5868: -- Fix for bug# 7377455. Stamp the tangibleid on the PSON column of
5869: -- IBY_FNDCPT_TX_EXTENSIONS table
5870: IF( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5871: iby_debug_pub.add( 'Stamping the PSON on the extension as '||
5872: l_return_pson,
5873: G_LEVEL_STATEMENT,G_DEBUG_MODULE || l_module);
5871: iby_debug_pub.add( 'Stamping the PSON on the extension as '||
5872: l_return_pson,
5873: G_LEVEL_STATEMENT,G_DEBUG_MODULE || l_module);
5874: END IF;
5875: UPDATE iby_fndcpt_tx_extensions
5876: SET payment_system_order_number = l_return_pson
5877: WHERE trxn_extension_id = p_trxn_entity_id;
5878:
5879: -- Fix for bug# 7530578. Stamp the initiator transaction extension id
6060: l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
6061: l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
6062: l_instrtype IBY_FNDCPT_PMT_CHNNLS_B.instrument_type%TYPE;
6063: l_encrypted VARCHAR2(1);
6064: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
6065: l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
6066: l_app_short_name fnd_application.application_short_name%TYPE;
6067: l_pmt_chnl_code IBY_FNDCPT_PMT_CHNNLS_B.payment_channel_code%TYPE;
6068: l_trxn_id IBY_TRXN_SUMMARIES_ALL.transactionid%TYPE;
6061: l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
6062: l_instrtype IBY_FNDCPT_PMT_CHNNLS_B.instrument_type%TYPE;
6063: l_encrypted VARCHAR2(1);
6064: l_code_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
6065: l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
6066: l_app_short_name fnd_application.application_short_name%TYPE;
6067: l_pmt_chnl_code IBY_FNDCPT_PMT_CHNNLS_B.payment_channel_code%TYPE;
6068: l_trxn_id IBY_TRXN_SUMMARIES_ALL.transactionid%TYPE;
6069: l_auth_status IBY_TRXN_SUMMARIES_ALL.status%TYPE;
6106: l_module CONSTANT VARCHAR2(30) := 'Create_Reversal';
6107: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
6108:
6109: CURSOR c_extension
6110: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
6111: ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
6112: ci_payer_level IN VARCHAR2,
6113: ci_payer_equiv IN VARCHAR2
6114: )
6116: SELECT chnl.instrument_type, xt.origin_application_id,
6117: a.application_short_name, xt.payment_channel_code,
6118: xt.instrument_security_code, xt.encrypted,
6119: xt.instr_code_sec_segment_id, xt.instr_sec_code_length
6120: FROM iby_fndcpt_tx_extensions xt,
6121: iby_external_payers_all p,
6122: fnd_application a,
6123: iby_fndcpt_pmt_chnnls_b chnl
6124: WHERE (xt.origin_application_id = a.application_id)
6132: ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
6133: p.cust_account_id,p.acct_site_use_id) = 'T');
6134:
6135: CURSOR c_auth
6136: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
6137: IS
6138: SELECT auth.transactionid, auth.status, auth.amount, auth.currencynamecode,
6139: bep.auth_reversal_flag, brand.auth_reversal_flag,
6140: xt.payment_system_order_number, auth.payeeid,
6141: auth.payerinstrid, tg.memo,tg.order_medium,
6142: auth.org_id, core.authcode, core.tracenumber,
6143: core.taxamount, core.shipfromzip, core.shiptozip,
6144: auth.factored_flag, auth.ar_receipt_method_id
6145: FROM iby_fndcpt_tx_extensions xt,
6146: iby_fndcpt_tx_operations op,
6147: iby_trxn_summaries_all auth,
6148: iby_tangible tg,
6149: iby_trxn_core core,
6431: END IF;
6432: END IF;
6433: */
6434:
6435: UPDATE iby_fndcpt_tx_extensions
6436: SET payment_system_order_number = l_tangible.Tangible_Id
6437: WHERE trxn_extension_id = p_trxn_entity_id;
6438:
6439: UPDATE iby_trxn_summaries_all
6585: )
6586: IS
6587: l_mode iby_sys_security_options.instr_sec_code_encryption_mode%TYPE;
6588: l_cvv2 VARCHAR2(100);
6589: lx_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
6590: l_subkey iby_security_pkg.DES3_KEY_TYPE;
6591: l_module CONSTANT VARCHAR2(30) := 'Encrypt_Extensions';
6592: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
6593: CURSOR c_extensions
6592: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
6593: CURSOR c_extensions
6594: IS
6595: SELECT x.trxn_extension_id, x.instrument_security_code
6596: FROM iby_fndcpt_tx_extensions x
6597: WHERE (NVL(x.encrypted,'N') = 'N')
6598: AND (NOT instrument_security_code IS NULL);
6599:
6600: BEGIN
6624: IBY_SECURITY_PKG.Create_Segment
6625: (FND_API.G_FALSE,l_cvv2,iby_security_pkg.G_ENCODING_NUMERIC,
6626: p_sys_security_key,lx_segment_id);
6627:
6628: UPDATE iby_fndcpt_tx_extensions
6629: SET
6630: instrument_security_code = LPAD('X',LENGTH(instrument_security_code),'X'),
6631: encrypted = 'Y',
6632: instr_code_sec_segment_id = lx_segment_id,
6651:
6652: PROCEDURE Encrypt_Security_Code
6653: (p_commit IN VARCHAR2 := FND_API.G_TRUE,
6654: p_sys_security_key IN iby_security_pkg.DES3_KEY_TYPE,
6655: p_security_code IN iby_fndcpt_tx_extensions.instrument_security_code%TYPE,
6656: x_segment_id OUT NOCOPY NUMBER,
6657: x_err_code OUT NOCOPY VARCHAR2
6658: )
6659: IS
6658: )
6659: IS
6660: l_mode iby_sys_security_options.instr_sec_code_encryption_mode%TYPE;
6661: l_cvv2 VARCHAR2(100);
6662: lx_segment_id iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE;
6663: l_subkey iby_security_pkg.DES3_KEY_TYPE;
6664: l_module CONSTANT VARCHAR2(30) := 'Encrypt_Security_Code';
6665: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
6666:
6711: IS
6712: SELECT x.trxn_extension_id, x.instr_sec_code_length,
6713: NVL(x.encrypted,'N'), s.segment_cipher_text, k.subkey_cipher_text,
6714: s.sec_segment_id
6715: FROM iby_fndcpt_tx_extensions x, iby_security_segments s,
6716: iby_sys_security_subkeys k
6717: WHERE (NVL(x.encrypted,'N') = 'Y')
6718: AND (NOT instrument_security_code IS NULL)
6719: AND (x.instr_code_sec_segment_id = s.sec_segment_id(+))
6734: RETURN;
6735: END IF;
6736:
6737: FOR c_ext_rec IN c_extensions LOOP
6738: UPDATE iby_fndcpt_tx_extensions
6739: SET
6740: instrument_security_code =
6741: Get_Security_Code(p_sys_security_key,
6742: c_ext_rec.subkey_cipher_text,
6766: FUNCTION Get_Security_Code
6767: (p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
6768: p_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
6769: p_sec_code_cipher IN iby_security_segments.segment_cipher_text%TYPE,
6770: p_sec_code_len IN iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE
6771: )
6772: RETURN iby_fndcpt_tx_extensions.instrument_security_code%TYPE
6773: IS
6774: --Increased the varchar2 size from 30 to 100 since we now
6768: p_subkey_cipher IN iby_sys_security_subkeys.subkey_cipher_text%TYPE,
6769: p_sec_code_cipher IN iby_security_segments.segment_cipher_text%TYPE,
6770: p_sec_code_len IN iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE
6771: )
6772: RETURN iby_fndcpt_tx_extensions.instrument_security_code%TYPE
6773: IS
6774: --Increased the varchar2 size from 30 to 100 since we now
6775: --pad the cvv to 32 bytes of data.
6776: l_sec_code VARCHAR2(100);
6801: RETURN l_sec_code;
6802: END Get_Security_Code;
6803:
6804: FUNCTION Get_Security_Code
6805: (p_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
6806: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
6807: )
6808: RETURN iby_fndcpt_tx_extensions.instrument_security_code%TYPE
6809: IS
6804: FUNCTION Get_Security_Code
6805: (p_trxn_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE,
6806: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
6807: )
6808: RETURN iby_fndcpt_tx_extensions.instrument_security_code%TYPE
6809: IS
6810: l_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
6811: l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
6812: l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
6806: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
6807: )
6808: RETURN iby_fndcpt_tx_extensions.instrument_security_code%TYPE
6809: IS
6810: l_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
6811: l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
6812: l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
6813: l_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
6814: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
6807: )
6808: RETURN iby_fndcpt_tx_extensions.instrument_security_code%TYPE
6809: IS
6810: l_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
6811: l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
6812: l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
6813: l_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
6814: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
6815: lx_err_code VARCHAR2(100);
6810: l_sec_code iby_fndcpt_tx_extensions.instrument_security_code%TYPE;
6811: l_sec_code_len iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE;
6812: l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
6813: l_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
6814: l_encrypted iby_fndcpt_tx_extensions.encrypted%TYPE;
6815: lx_err_code VARCHAR2(100);
6816: l_module CONSTANT VARCHAR2(30) := 'Get_Security_Code(2)';
6817: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
6818:
6816: l_module CONSTANT VARCHAR2(30) := 'Get_Security_Code(2)';
6817: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
6818:
6819: CURSOR c_extension
6820: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
6821: IS
6822: SELECT x.instrument_security_code, x.instr_sec_code_length,
6823: NVL(x.encrypted,'N'), s.segment_cipher_text, k.subkey_cipher_text
6824: FROM iby_fndcpt_tx_extensions x, iby_security_segments s,
6820: (ci_extension_id IN iby_fndcpt_tx_extensions.trxn_extension_id%TYPE)
6821: IS
6822: SELECT x.instrument_security_code, x.instr_sec_code_length,
6823: NVL(x.encrypted,'N'), s.segment_cipher_text, k.subkey_cipher_text
6824: FROM iby_fndcpt_tx_extensions x, iby_security_segments s,
6825: iby_sys_security_subkeys k
6826: WHERE (trxn_extension_id = ci_extension_id)
6827: AND (x.instr_code_sec_segment_id = s.sec_segment_id(+))
6828: AND (s.sec_subkey_id = k.sec_subkey_id(+));
6858: END Get_Security_Code;
6859:
6860: FUNCTION Get_Security_Code
6861: (
6862: p_segment_id IN iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE,
6863: p_sec_code_len IN iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE,
6864: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
6865: )
6866: RETURN iby_fndcpt_tx_extensions.instrument_security_code%TYPE
6859:
6860: FUNCTION Get_Security_Code
6861: (
6862: p_segment_id IN iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE,
6863: p_sec_code_len IN iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE,
6864: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
6865: )
6866: RETURN iby_fndcpt_tx_extensions.instrument_security_code%TYPE
6867: IS
6862: p_segment_id IN iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE,
6863: p_sec_code_len IN iby_fndcpt_tx_extensions.instr_sec_code_length%TYPE,
6864: p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE
6865: )
6866: RETURN iby_fndcpt_tx_extensions.instrument_security_code%TYPE
6867: IS
6868: l_segment_cipher iby_security_segments.segment_cipher_text%TYPE;
6869: l_subkey_cipher iby_sys_security_subkeys.subkey_cipher_text%TYPE;
6870: lx_err_code VARCHAR2(100);
6871: l_module CONSTANT VARCHAR2(30) := 'Get_Security_Code(3)';
6872: l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
6873:
6874: CURSOR c_segment
6875: (ci_segment_id IN iby_fndcpt_tx_extensions.instr_code_sec_segment_id%TYPE)
6876: IS
6877: SELECT s.segment_cipher_text, k.subkey_cipher_text
6878: FROM iby_security_segments s, iby_sys_security_subkeys k
6879: WHERE (s.sec_segment_id = ci_segment_id)