[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
LAST_UPDATED_BY IBY_TRXN_SUMMARIES_ALL.LAST_UPDATED_BY%TYPE,
LAST_UPDATE_DATE IBY_TRXN_SUMMARIES_ALL.LAST_UPDATE_DATE%TYPE,
LAST_UPDATE_LOGIN IBY_TRXN_SUMMARIES_ALL.LAST_UPDATE_LOGIN%TYPE,
OBJECT_VERSION_NUMBER IBY_TRXN_SUMMARIES_ALL.OBJECT_VERSION_NUMBER%TYPE,
-- From auth table
auth_transactionid IBY_TRXN_SUMMARIES_ALL.transactionid%TYPE,
auth_sales_rep_party_id IBY_TRXN_SUMMARIES_ALL.sales_rep_party_id%TYPE,
auth_tangibleid IBY_TRXN_SUMMARIES_ALL.tangibleid%TYPE,
auth_bepkey IBY_TRXN_SUMMARIES_ALL.bepkey%TYPE,
auth_payeeid IBY_TRXN_SUMMARIES_ALL.payeeid%TYPE,
auth_bepid IBY_TRXN_SUMMARIES_ALL.bepid%TYPE,
auth_mpayeeid IBY_TRXN_SUMMARIES_ALL.mpayeeid%TYPE,
auth_ecappid IBY_TRXN_SUMMARIES_ALL.ecappid%TYPE,
auth_paymentmethodname IBY_TRXN_SUMMARIES_ALL.paymentmethodname%TYPE,
auth_mtangibleid IBY_TRXN_SUMMARIES_ALL.mtangibleid%TYPE,
auth_payeeinstrid IBY_TRXN_SUMMARIES_ALL.payeeinstrid%TYPE,
auth_payerid IBY_TRXN_SUMMARIES_ALL.payerid%TYPE,
auth_payerinstrid IBY_TRXN_SUMMARIES_ALL.payerinstrid%TYPE,
auth_detaillookup IBY_TRXN_SUMMARIES_ALL.detaillookup%TYPE,
auth_instrnumber IBY_TRXN_SUMMARIES_ALL.instrnumber%TYPE,
auth_instrtype IBY_TRXN_SUMMARIES_ALL.instrtype%TYPE,
auth_instrsubtype IBY_TRXN_SUMMARIES_ALL.instrsubtype%TYPE,
auth_currencynamecode IBY_TRXN_SUMMARIES_ALL.currencynamecode%TYPE,
auth_desturl IBY_TRXN_SUMMARIES_ALL.desturl%TYPE,
auth_nlslang IBY_TRXN_SUMMARIES_ALL.nlslang%TYPE,
auth_amount IBY_TRXN_SUMMARIES_ALL.amount%TYPE,
auth_process_profile_code IBY_TRXN_SUMMARIES_ALL.process_profile_code%TYPE,
auth_payment_channel_code IBY_TRXN_SUMMARIES_ALL.payment_channel_code%TYPE,
auth_proc_reference_code IBY_TRXN_SUMMARIES_ALL.proc_reference_code%TYPE,
auth_proc_reference_amount IBY_TRXN_SUMMARIES_ALL.proc_reference_amount%TYPE,
auth_legal_entity_id IBY_TRXN_SUMMARIES_ALL.legal_entity_id%TYPE,
auth_settlement_due_date IBY_TRXN_SUMMARIES_ALL.settlement_due_date%TYPE,
auth_bill_to_address_id IBY_TRXN_SUMMARIES_ALL.bill_to_address_id%TYPE,
auth_br_maturity_date IBY_TRXN_SUMMARIES_ALL.br_maturity_date%TYPE,
auth_factored_flag IBY_TRXN_SUMMARIES_ALL.factored_flag%TYPE,
-- From Payers table
auth_payment_function IBY_EXTERNAL_PAYERS_ALL.PAYMENT_FUNCTION%TYPE,
auth_payer_party_id IBY_EXTERNAL_PAYERS_ALL.PARTY_ID%TYPE,
auth_cust_account_id IBY_EXTERNAL_PAYERS_ALL.CUST_ACCOUNT_ID%TYPE,
auth_acct_site_use_id IBY_EXTERNAL_PAYERS_ALL.ACCT_SITE_USE_ID%TYPE,
auth_org_id IBY_EXTERNAL_PAYERS_ALL.ORG_ID%TYPE,
auth_org_type IBY_EXTERNAL_PAYERS_ALL.ORG_TYPE%TYPE,
bep_type IBY_BEPINFO.bep_type%TYPE,
payer_instr_assignment_id IBY_TRXN_SUMMARIES_ALL.payer_instr_assignment_id%TYPE,
debit_auth_flag IBY_TRXN_SUMMARIES_ALL.debit_auth_flag%TYPE,
debit_auth_method IBY_TRXN_SUMMARIES_ALL.debit_auth_method%TYPE,
debit_auth_reference IBY_TRXN_SUMMARIES_ALL.debit_auth_reference%TYPE,
capture_transaction_id IBY_TRXN_SUMMARIES_ALL.transactionid%TYPE,
int_bank_country VARCHAR2(60),
settle_req_vrfy IBY_FNDCPT_SYS_EFT_PF_B.SETTLE_REQUIRE_VRFY_FLAG%TYPE,
auth_count PLS_INTEGER,
auth_dirdeb_instruction_code IBY_TRXN_SUMMARIES_ALL.dirdeb_instruction_code%TYPE,
card_subtype_code IBY_TRXN_CORE.card_subtype_code%TYPE,
card_data_level IBY_TRXN_CORE.card_data_level%TYPE
);
SELECT ext_pmt_party_id
INTO l_payer_id
FROM iby_pmt_instr_uses_all
WHERE (instrument_payment_use_id = p_instr_assign_id);
SELECT party_id
INTO l_party_id
FROM iby_external_payers_all
WHERE (ext_payer_id = l_payer_id);
SELECT instr_assignment_id, ext_payer_id
INTO l_instr_assign_id, l_ext_payer_id
FROM iby_fndcpt_tx_extensions
WHERE (p_trxn_extension_id = trxn_extension_id);
SELECT instr_sec_code_encryption_mode
FROM iby_sys_security_options;
SELECT payment_system_order_number INTO l_prev_pson
FROM iby_fndcpt_tx_extensions
WHERE trxn_extension_id = p_trxn_extn_id;
SELECT p.payeeid
FROM iby_payee p, iby_fndcpt_payee_appl a
WHERE (p.mpayeeid = a.mpayeeid)
AND ((a.org_type = ci_org_type) AND (a.org_id = ci_org_id));
SELECT COUNT(copy_trxn_extension_id)
FROM iby_fndcpt_tx_xe_copies
WHERE (source_trxn_extension_id = ci_x_id);
p_update IN BOOLEAN,
p_consumed_cvv2 IN BOOLEAN
)
RETURN BOOLEAN
IS
l_return_status VARCHAR2(1);
AND (NOT p_update)
AND (NOT p_consumed_cvv2)
)
OR (p_trxn_attribs.Instrument_Security_Code = FND_API.G_MISS_CHAR)
)
)
THEN
RETURN FALSE;
AND ( (p_trxn_attribs.VoiceAuth_Flag IS NULL AND (NOT p_update))
OR (p_trxn_attribs.VoiceAuth_Flag = FND_API.G_MISS_CHAR) )
)
THEN
RETURN FALSE;
AND ( (p_trxn_attribs.VoiceAuth_Code IS NULL AND (NOT p_update))
OR (p_trxn_attribs.VoiceAuth_Code = FND_API.G_MISS_CHAR)
)
)
THEN
RETURN FALSE;
AND ( (p_trxn_attribs.VoiceAuth_Date IS NULL AND (NOT p_update)) OR
(p_trxn_attribs.VoiceAuth_Date = FND_API.G_MISS_DATE) )
)
THEN
RETURN FALSE;
AND ( (p_trxn_attribs.PO_Number IS NULL AND (NOT p_update))
OR (p_trxn_attribs.PO_Number = FND_API.G_MISS_CHAR) )
)
THEN
RETURN FALSE;
AND ( (p_trxn_attribs.PO_Line_Number IS NULL OR (NOT p_update))
OR (p_trxn_attribs.PO_Line_Number = FND_API.G_MISS_CHAR) )
)
THEN
RETURN FALSE;
AND ( (p_trxn_attribs.Additional_Info IS NULL OR (NOT p_update))
OR (p_trxn_attribs.Additional_Info = FND_API.G_MISS_CHAR) )
)
THEN
RETURN FALSE;
IF ( (p_trxn_attribs.Order_Id IS NULL AND (NOT p_update)) OR
(p_trxn_attribs.Order_id = FND_API.G_MISS_CHAR)
)
THEN
RETURN FALSE;
SELECT c.instrument_type
FROM iby_ext_party_pmt_mthds pm, iby_fndcpt_pmt_chnnls_b c
WHERE (c.payment_channel_code = ci_channel_code)
AND (pm.payment_method_code = c.payment_channel_code)
AND (NVL(pm.inactive_date,SYSDATE-10)
SELECT attribute_applicability
FROM iby_pmt_mthd_attrib_appl
WHERE (payment_flow = 'FUNDS_CAPTURE')
AND (payment_method_code = ci_pmt_channel)
AND (attribute_code = 'PAYMENT_INSTRUMENT');
SELECT c.instrument_type, u.instrument_payment_use_id
FROM iby_fndcpt_pmt_chnnls_b c, iby_pmt_instr_uses_all u
WHERE (payment_channel_code = ci_channel_code)
AND (u.instrument_payment_use_id (+) = ci_assign_id)
AND (c.instrument_type = u.instrument_type(+))
AND (NVL(payment_flow,IBY_FNDCPT_SETUP_PUB.G_PMT_FLOW_FNDCPT)
= IBY_FNDCPT_SETUP_PUB.G_PMT_FLOW_FNDCPT);
SELECT instr_assignment_id, instrument_type, instrument_id,
NVL(card_info_only_flag,'N'), NVL(card_single_use_flag,'N')
FROM iby_fndcpt_payer_assgn_instr_v
WHERE
(instr_assignment_id = ci_instr_assign)
AND ext_payer_id IN
(
SELECT ext_payer_id
FROM iby_external_payers_all
WHERE (payment_function = ci_payer.Payment_Function)
AND (party_id = ci_payer.Party_Id)
AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
(ci_payer.org_type, ci_payer.org_id,
ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
ci_payer_level,ci_payer_equiv,org_type,org_id,
cust_account_id,acct_site_use_id) = 'T')
);
SELECT NVL(authorized_flag,'N')
FROM iby_trxn_extensions_v
WHERE (instrument_id = ci_instr_id)
AND (instrument_type = ci_instr_type);
SELECT count(1)
FROM iby_trxn_extensions_v
WHERE (trxn_extension_id = ci_extension_id)
AND (authorization_status IN ( 'AUTH_SUCCESS', 'AUTH_PENDING' ));
SELECT iby_fndcpt_tx_extensions_s.NEXTVAL INTO x_entity_id FROM DUAL;
INSERT INTO iby_fndcpt_tx_extensions
(trxn_extension_id, payment_channel_code, ext_payer_id,
instr_assignment_id, origin_application_id, instrument_security_code,
voice_authorization_flag, voice_authorization_date,
voice_authorization_code, order_id, po_number,
po_line_number, trxn_ref_number1, trxn_ref_number2, additional_info,
instr_sec_code_length, encrypted, instr_code_sec_segment_id,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number,seq_type_last
)
VALUES
(
x_entity_id, p_pmt_channel, l_payer_id, p_instr_assignment,
p_trxn_attribs.Originating_Application_Id,
l_sec_code,
p_trxn_attribs.VoiceAuth_Flag,
p_trxn_attribs.VoiceAuth_Date, p_trxn_attribs.VoiceAuth_Code,
p_trxn_attribs.Order_Id, p_trxn_attribs.PO_Number,
p_trxn_attribs.PO_Line_Number,
p_trxn_attribs.Trxn_Ref_Number1, p_trxn_attribs.Trxn_Ref_Number2,
p_trxn_attribs.Additional_Info,
length(p_trxn_attribs.Instrument_Security_Code), l_encrypted,
l_segment_id,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1, p_trxn_attribs.seq_type_last
);
PROCEDURE Update_Transaction_Extension
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_TRUE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
p_payer_equivalency IN VARCHAR2 :=
IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
p_entity_id IN NUMBER,
p_pmt_channel IN VARCHAR2,
p_instr_assignment IN NUMBER,
p_trxn_attribs IN TrxnExtension_rec_type,
x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_module CONSTANT VARCHAR2(30) := 'Update_Transaction_Extension';
SELECT
x.trxn_extension_id, x.payment_channel_code, x.instr_assignment_id,
x.order_id, x.trxn_ref_number1, x.trxn_ref_number2,
x.origin_application_id, x.instr_code_sec_segment_id
FROM iby_fndcpt_tx_extensions x, iby_external_payers_all p
WHERE (x.ext_payer_id = p.ext_payer_id)
AND (x.trxn_extension_id = ci_extension_id)
AND (p.party_id = ci_payer.Party_Id)
AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
(ci_payer.org_type, ci_payer.org_id,
ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
p.cust_account_id,p.acct_site_use_id) = 'T');
SELECT instrument_type into l_instrumenttype from iby_fndcpt_pmt_chnnls_b where
payment_channel_code = l_pmt_channel;
iby_debug_pub.add('operations exist for extension; cannot update',
UPDATE iby_fndcpt_tx_extensions
SET
payment_channel_code = NVL(p_pmt_channel,payment_channel_code),
instr_assignment_id =
DECODE(p_instr_assignment, FND_API.G_MISS_NUM,NULL,
NULL,instr_assignment_id, p_instr_assignment),
origin_application_id =
NVL(p_trxn_attribs.Originating_Application_Id,
origin_application_id),
instrument_security_code =
DECODE(p_trxn_attribs.Instrument_Security_Code,
FND_API.G_MISS_CHAR,NULL, NULL,instrument_security_code,
'XXX', instrument_security_code,
'XXXX', instrument_security_code,
l_sec_code),
instr_sec_code_length =
length(DECODE(p_trxn_attribs.Instrument_Security_Code,
FND_API.G_MISS_CHAR,NULL,
NULL,instrument_security_code,
'XXX', instrument_security_code,
'XXXX', instrument_security_code,
l_sec_code)
),
encrypted =
DECODE(p_trxn_attribs.Instrument_Security_Code,
FND_API.G_MISS_CHAR,'N', NULL,encrypted, l_encrypted),
instr_code_sec_segment_id =
NVL(l_segment_id, instr_code_sec_segment_id),
voice_authorization_flag =
DECODE(p_trxn_attribs.VoiceAuth_Flag, FND_API.G_MISS_CHAR,NULL,
NULL,voice_authorization_flag, p_trxn_attribs.VoiceAuth_Flag),
voice_authorization_date =
DECODE(p_trxn_attribs.VoiceAuth_Date, FND_API.G_MISS_DATE,NULL,
NULL,voice_authorization_date, p_trxn_attribs.VoiceAuth_Date),
voice_authorization_code =
DECODE(p_trxn_attribs.VoiceAuth_Code, FND_API.G_MISS_CHAR,NULL,
NULL,voice_authorization_code, p_trxn_attribs.VoiceAuth_Code),
order_id = NVL(p_trxn_attribs.Order_Id, order_id),
po_number = DECODE(p_trxn_attribs.PO_Number, FND_API.G_MISS_CHAR,NULL,
NULL,po_number, p_trxn_attribs.PO_Number),
po_line_number = DECODE(p_trxn_attribs.PO_Line_Number,
FND_API.G_MISS_CHAR,NULL, NULL,po_line_number,
p_trxn_attribs.PO_Line_Number),
trxn_ref_number1 = DECODE(p_trxn_attribs.Trxn_Ref_Number1,
FND_API.G_MISS_CHAR,NULL, NULL,trxn_ref_number1,
p_trxn_attribs.Trxn_Ref_Number1),
trxn_ref_number2 = DECODE(p_trxn_attribs.Trxn_Ref_Number2,
FND_API.G_MISS_CHAR,NULL, NULL,trxn_ref_number2,
p_trxn_attribs.Trxn_Ref_Number2),
additional_info = DECODE(p_trxn_attribs.Additional_Info,
FND_API.G_MISS_CHAR,NULL, NULL,additional_info,
p_trxn_attribs.Additional_Info),
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
object_version_number = object_version_number + 1
WHERE (trxn_extension_id = p_entity_id);
DELETE FROM iby_security_segments
WHERE (sec_segment_id = l_seg_id_old);
END Update_Transaction_Extension;
SELECT
origin_application_id, RPAD('X',instr_sec_code_length,'X'),
voice_authorization_flag, voice_authorization_date,
voice_authorization_code, order_id, po_number,
po_line_number, trxn_ref_number1, trxn_ref_number2, additional_info
FROM iby_fndcpt_tx_extensions
WHERE trxn_extension_id = ci_extension_id;
PROCEDURE Delete_Transaction_Extension
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_TRUE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
p_payer_equivalency IN VARCHAR2 :=
IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
p_entity_id IN NUMBER,
x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_module CONSTANT VARCHAR2(30) := 'Delete_Transaction_Extension';
SELECT x.trxn_extension_id
FROM iby_fndcpt_tx_extensions x, iby_external_payers_all p,
iby_pmt_instr_uses_all i
WHERE (x.trxn_extension_id = ci_extension_id)
AND (x.instr_assignment_id = i.instrument_payment_use_id(+))
AND (NVL(x.ext_payer_id,i.ext_pmt_party_id) = p.ext_payer_id)
AND (p.party_id = ci_payer.Party_Id)
AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
(ci_payer.org_type, ci_payer.org_id,
ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
p.cust_account_id,p.acct_site_use_id) = 'T');
iby_debug_pub.add('extension copied or operation done; cannot delete',
SAVEPOINT Delete_Transaction_Extension;
DELETE iby_fndcpt_tx_xe_copies
WHERE (copy_trxn_extension_id = p_entity_id);
DELETE iby_fndcpt_tx_extensions
WHERE (trxn_extension_id = p_entity_id);
ROLLBACK TO Delete_Transaction_Extension;
ROLLBACK TO Delete_Transaction_Extension;
ROLLBACK TO Delete_Transaction_Extension;
END Delete_Transaction_Extension;
SELECT x.payment_channel_code, x.instr_assignment_id,
x.instrument_security_code, x.instr_sec_code_length,
x.voice_authorization_flag, x.voice_authorization_code,
x.voice_authorization_date, x.encrypted, x.instr_code_sec_segment_id,
x.payment_system_order_number
FROM iby_fndcpt_tx_extensions x, iby_pmt_instr_uses_all i,
iby_pmt_instr_uses_all cpi, iby_external_payers_all p
WHERE (x.instr_assignment_id = i.instrument_payment_use_id(+))
-- bug 11675835 - kept an outer join for instrument details
-- can assume this assignment is for funds capture
-- bug 7017004 - use the payer information on the trxn extension
--AND (i.ext_pmt_party_id = p.ext_payer_id)
AND (x.ext_payer_id = p.ext_payer_id)
AND (x.trxn_extension_id = ci_extension_id)
AND (NVL(ci_copy_instr_assign_id,x.instr_assignment_id) = cpi.instrument_payment_use_id(+))
-- bug 11675835 - kept an outer join for instrument details
-- payer may change during copy; thus allow the instrument assignment id
SELECT NVL(sp.settle_require_vrfy_flag,'Y')
FROM iby_trxn_ext_auths_v x, iby_fndcpt_sys_eft_pf_b sp,
iby_fndcpt_user_eft_pf_b up
WHERE (x.trxn_extension_id = ci_extension_id)
AND (DECODE(x.instrument_type,
IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_BANKACCT,x.process_profile_code,
NULL) = up.user_eft_profile_code(+)
)
AND (up.sys_eft_profile_code = sp.sys_eft_profile_code(+));
SELECT COUNT(*) INTO l_count FROM iby_pmt_instr_uses_all
WHERE instrument_payment_use_id = l_instr_assign_id AND
NVL(end_date, sysdate + 10) > sysdate;
SELECT iby_fndcpt_tx_extensions_s.NEXTVAL INTO x_entity_id FROM DUAL;
-- Insert PSON Number from parent record when copying transaction extension.
INSERT INTO iby_fndcpt_tx_extensions
(trxn_extension_id, payment_channel_code,
ext_payer_id, instr_assignment_id,
origin_application_id, instrument_security_code,
instr_sec_code_length, encrypted, instr_code_sec_segment_id,
voice_authorization_flag, voice_authorization_date,
voice_authorization_code, order_id, po_number,
po_line_number, trxn_ref_number1, trxn_ref_number2, additional_info,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number,payment_system_order_number,
seq_type_last
)
VALUES
(
x_entity_id, l_pmt_channel, l_payer_id, l_instr_assign_id,
l_extension.Originating_Application_Id,
l_extension.Instrument_Security_Code,
lc_sec_code_len, lc_encrypted, l_segment_id,
l_extension.VoiceAuth_Flag,
l_extension.VoiceAuth_Date, l_extension.VoiceAuth_Code,
l_extension.Order_Id, l_extension.PO_Number,
l_extension.PO_Line_Number,
l_extension.Trxn_Ref_Number1, l_extension.Trxn_Ref_Number2,
l_extension.Additional_Info,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1, lc_PSON,
p_trxn_attribs.seq_type_last
);
INSERT INTO iby_fndcpt_tx_xe_copies
(source_trxn_extension_id, copy_trxn_extension_id,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number
)
VALUES
(p_entities(i), x_entity_id,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1
);
INSERT INTO iby_fndcpt_tx_operations
(trxn_extension_id, transactionid,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number
)
-- instrument may change during copy
SELECT
x_entity_id, transactionid,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1
FROM iby_fndcpt_tx_operations
WHERE trxn_extension_id = p_entities(i);
SELECT trunc(DBMS_RANDOM.VALUE(1000,9999)) INTO l_random_val FROM dual;
UPDATE iby_security_segments
SET segment_cipher_text = RAWTOHEX(fnd_crypto.randombytes(32))
WHERE sec_segment_id = p_segment_id;
UPDATE iby_security_segments
SET segment_cipher_text = null
WHERE sec_segment_id = p_segment_id;
SELECT NVL(i.instrument_type,pc.instrument_type),
NVL(i.instrument_id,0),
x.origin_application_id, a.application_short_name,
x.order_id, x.trxn_ref_number1, x.trxn_ref_number2,
x.instrument_security_code, x.instr_code_sec_segment_id,
x.instr_sec_code_length, x.encrypted,
x.po_number, x.voice_authorization_flag, x.voice_authorization_code,
x.voice_authorization_date, i.card_single_use_flag,
NVL(x.instr_assignment_id,0), x.payment_channel_code
FROM iby_fndcpt_tx_extensions x, iby_fndcpt_payer_assgn_instr_v i,
iby_external_payers_all p, fnd_application a,
iby_fndcpt_pmt_chnnls_b pc
WHERE (x.instr_assignment_id = i.instr_assignment_id(+))
AND (x.payment_channel_code = pc.payment_channel_code)
AND (x.origin_application_id = a.application_id)
-- can assume this assignment is for funds capture
AND (x.ext_payer_id = p.ext_payer_id)
AND (x.trxn_extension_id = ci_extension_id)
AND (p.party_id = ci_payer.Party_Id)
AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
(ci_payer.org_type, ci_payer.org_id,
ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
p.cust_account_id,p.acct_site_use_id) = 'T');
SELECT authorized_flag
FROM iby_trxn_extensions_v
WHERE (trxn_extension_id = ci_extension_id); */
SELECT
a.transactionid,
DECODE(a.status, 0, 'AUTH_SUCCESS', 1, 'COMMUNICATION_ERROR', 2, 'DUPLICATE_AUTH', 4,
'GENERAL_INVALID_PARAM', 5, 'PAYMENT_SYS_REJECT', 11, 'AUTH_PENDING', 12, 'AUTH_PENDING',
13, 'AUTH_PENDING', 16, 'PAYMENT_SYS_REJECT', 17, 'PAYMENT_SYS_REJECT', 18,
'AUTH_PENDING', 19, 'GENERAL_INVALID_PARAM', 20, 'PAYMENT_SYS_REJECT', 100,
'AUTH_PENDING', 101, 'COMMUNICATION_ERROR', 111, 'AUTH_PENDING', 22,
'FULLY_REVERSED', 23, 'PARTIALLY_REVERSED', 'GENERAL_SYS_ERROR') auth_result_code,
x.payment_system_order_number
FROM
IBY_FNDCPT_TX_EXTENSIONS X,
IBY_FNDCPT_TX_OPERATIONS O,
IBY_TRXN_SUMMARIES_ALL A,
IBY_TRXN_CORE C
WHERE x.trxn_extension_id = ci_extension_id
AND (x.trxn_extension_id = o.trxn_extension_id)
AND (o.transactionid = a.transactionid)
AND (a.trxnmid = c.trxnmid(+))
AND (a.reqtype = 'ORAPMTREQ')
AND (a.trxntypeid IN (2,3,20))
ORDER BY a.trxnmid desc;
SELECT NVL(authorized_flag,'N')
FROM iby_trxn_extensions_v
WHERE (instrument_id = ci_instr_id)
AND (instrument_type = ci_instr_type)
AND (trxn_extension_id <> ci_trxn_x_id);
SELECT count(1)
FROM iby_fndcpt_tx_operations o
WHERE o.transactionid = ci_trxn_id
AND o.trxn_extension_id = ci_trxn_extension_id;
SELECT cp.source_trxn_extension_id, ex.instr_code_sec_segment_id, ex.instrument_security_code
FROM iby_fndcpt_tx_xe_copies cp, iby_fndcpt_tx_extensions ex
WHERE cp.source_trxn_extension_id = ex.trxn_extension_id
START WITH cp.copy_trxn_extension_id = ci_trxn_extension_id
CONNECT BY PRIOR source_trxn_extension_id = copy_trxn_extension_id;
select RECEIPT_METHOD_ID
into l_rec_mth_id
from ar_cash_receipts_all
where payment_trxn_extension_id = p_trxn_entity_id;
UPDATE iby_fndcpt_tx_extensions
SET instrument_security_code = NULL,
--instr_sec_code_length = NULL,
encrypted = 'N',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
object_version_number = object_version_number + 1
WHERE trxn_extension_id = p_trxn_entity_id;
UPDATE iby_fndcpt_tx_extensions
SET instrument_security_code = NULL,
--instr_sec_code_length = NULL,
encrypted = 'N',
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
object_version_number = object_version_number + 1
WHERE trxn_extension_id IN
(SELECT source_trxn_extension_id
FROM iby_fndcpt_tx_xe_copies
START WITH copy_trxn_extension_id = p_trxn_entity_id
CONNECT BY PRIOR source_trxn_extension_id = copy_trxn_extension_id
);
iby_debug_pub.add('No. of source extensions updated= :'||SQL%ROWCOUNT,
G_LEVEL_STATEMENT,l_dbg_mod);
-- securely. i.e, first update with a random value, do a commit
-- then update with null and issue another commit.
-- This is handled through the below procedure call.
IF (l_code_segment_id IS NOT NULL) THEN
IF( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
iby_debug_pub.add('Call to Securely_Wipe_Segment.',
G_LEVEL_STATEMENT,l_dbg_mod);
UPDATE iby_trxn_summaries_all
SET dirdeb_instruction_code = l_payer_attribs.DirectDebit_BankInstruction
WHERE transactionid = l_reqresp.Trxn_Id;
UPDATE iby_fndcpt_tx_extensions
SET payment_system_order_number = l_tangible.Tangible_Id
WHERE trxn_extension_id = p_trxn_entity_id;
UPDATE iby_trxn_summaries_all
SET initiator_extension_id = p_trxn_entity_id
WHERE transactionid = l_reqresp.Trxn_Id
AND reqtype = 'ORAPMTREQ';
INSERT INTO iby_fndcpt_tx_operations
(trxn_extension_id, transactionid,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number
)
VALUES
(p_trxn_entity_id, l_reqresp.Trxn_Id,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1
);
INSERT INTO iby_fndcpt_tx_operations
(trxn_extension_id, transactionid,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number
)
SELECT source_trxn_extension_id, l_reqresp.Trxn_Id,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1
FROM iby_fndcpt_tx_xe_copies
START WITH copy_trxn_extension_id = p_trxn_entity_id
CONNECT BY PRIOR source_trxn_extension_id = copy_trxn_extension_id;
INSERT INTO iby_fndcpt_tx_operations
(trxn_extension_id, transactionid,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number
)
SELECT copy_trxn_extension_id, l_reqresp.Trxn_Id,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1
FROM iby_fndcpt_tx_xe_copies
START WITH source_trxn_extension_id = p_trxn_entity_id
CONNECT BY source_trxn_extension_id = PRIOR copy_trxn_extension_id;
UPDATE iby_creditcard SET active_flag = 'N' where instrid = l_pmt_instr.PmtInstr_Id;
SELECT authorization_id, authorization_date, authorization_code,
avs_code, instr_sec_code_check, pmt_sys_err_code, pmt_sys_err_msg
FROM iby_trxn_ext_auths_v
WHERE (trxn_extension_id = ci_extension_id)
ORDER BY
DECODE(authorization_result_code, 'AUTH_SUCCESS',10,
'AUTH_PENDING',5, 0) DESC;
SELECT NVL(i.instrument_type,pc.instrument_type), NVL(i.instrument_id,0),
x.origin_application_id, a.application_short_name,
x.order_id, x.trxn_ref_number1, x.trxn_ref_number2,
x.instrument_security_code, x.instr_code_sec_segment_id, x.encrypted,
x.po_number, x.voice_authorization_flag, x.voice_authorization_code,
x.voice_authorization_date, NVL(x.instr_assignment_id,0),
x.payment_channel_code
FROM iby_fndcpt_tx_extensions x, iby_pmt_instr_uses_all i,
iby_external_payers_all p, fnd_application a,
iby_fndcpt_pmt_chnnls_b pc
WHERE (x.instr_assignment_id = i.instrument_payment_use_id(+))
AND (x.payment_channel_code = pc.payment_channel_code)
-- can assume this assignment is for funds capture
AND (x.ext_payer_id = p.ext_payer_id)
AND (x.trxn_extension_id = ci_extension_id)
AND (x.origin_application_id = a.application_id)
AND (p.party_id = ci_payer.Party_Id)
AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
(ci_payer.org_type, ci_payer.org_id,
ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
p.cust_account_id,p.acct_site_use_id) = 'T');
SELECT authorization_id
FROM iby_trxn_ext_auths_v
WHERE (trxn_extension_id = ci_extension_id)
AND (authorization_status = 0);
select s.payeeid, s.org_id, s.org_type
from iby_trxn_summaries_all s, iby_fndcpt_tx_operations o
where s.trxntypeid = 20
and s.transactionid = o.transactionid
and o.trxn_extension_id = ci_trxn_extension_id;
SELECT count(1)
FROM iby_fndcpt_tx_operations o
WHERE o.transactionid = ci_trxn_id
AND o.trxn_extension_id = ci_trxn_extension_id;
UPDATE iby_fndcpt_tx_extensions
SET payment_system_order_number = l_tangible.Tangible_Id
WHERE trxn_extension_id = p_trxn_entity_id;
UPDATE iby_trxn_summaries_all
SET initiator_extension_id = p_trxn_entity_id
WHERE transactionid = l_reqresp.Trxn_Id
AND reqtype = 'ORAPMTREQ';
iby_debug_pub.add('inserting into iby_fndcpt_tx_operations.',
G_LEVEL_STATEMENT,l_dbg_mod);
INSERT INTO iby_fndcpt_tx_operations
(trxn_extension_id, transactionid,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number
)
VALUES
(p_trxn_entity_id, l_reqresp.Trxn_Id,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1
);
INSERT INTO iby_fndcpt_tx_operations
(trxn_extension_id, transactionid,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number
)
SELECT source_trxn_extension_id, l_reqresp.Trxn_Id,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1
FROM iby_fndcpt_tx_xe_copies
START WITH copy_trxn_extension_id = p_trxn_entity_id
CONNECT BY PRIOR source_trxn_extension_id = copy_trxn_extension_id;
INSERT INTO iby_fndcpt_tx_operations
(trxn_extension_id, transactionid,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number
)
SELECT copy_trxn_extension_id, l_reqresp.Trxn_Id,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1
FROM iby_fndcpt_tx_xe_copies
START WITH source_trxn_extension_id = p_trxn_entity_id
CONNECT BY source_trxn_extension_id = PRIOR copy_trxn_extension_id;
UPDATE iby_trxn_summaries_all
SET initiator_extension_id = p_trxn_entity_id
WHERE transactionid = l_reqresp.Trxn_Id
AND reqtype = 'ORAPMTCAPTURE';
TYPE dyn_order_select IS REF CURSOR;
l_orders_cursor dyn_order_select;
SELECT CORE.authcode
FROM IBY_TRXN_SUMMARIES_ALL TX,
IBY_TRXN_CORE CORE
WHERE TX.TRANSACTIONID = c_tid
AND TX.TRXNMID=CORE.TRXNMID
AND TX.TRXNTYPEID=2
AND TX.STATUS=0;
SELECT NVL(B.ALLOW_CAPTURE_EX_AUTH_FLAG, 'Y') BEP_FLAG,
NVL(i.ALLOW_CAPTURE_EX_AUTH_FLAG, 'Y') card_issuer_flag
FROM IBY_BEPINFO B, IBY_CREDITCARD_ISSUERS_B I
WHERE B.BEPID=ci_bepid
AND I.CARD_ISSUER_CODE=ci_issuer;
SELECT
fnd.application_short_name
INTO
l_app_short_name
FROM
FND_APPLICATION fnd
WHERE
fnd.application_id = p_calling_app_id;
' AND orders.selected_remittance_batch_id = decode(substr(:1,1,3),' ||
'''AR_''' || ',to_number(substr(substr(:2,1,INSTR(:3,''_'',4)-1),4)),-99999)
AND orders.worker_id = to_number(substr(:4,INSTR(:5,''_'',4) + 1)) ';
'SELECT
IBY_TRXNSUMM_MID_S.NEXTVAL TRXNMID,
TRXN_REF_NUMBER1,
TRXN_REF_NUMBER2,
PAYMENT_CHANNEL_CODE,
PO_NUMBER,
VOICE_AUTHORIZATION_FLAG,
PAYMENT_FUNCTION,
CALL_APP_SERVICE_REQ_CODE,
SETTLEMENT_DATE,
SETTLEMENT_DUE_DATE,
BR_MATURITY_DATE,
ORDER_DESCRIPTION,
SETTLEMENT_CURRENCY_CODE,
SETTLEMENT_AMOUNT,
PAYMENT_TRXN_EXTENSION_ID,
INTERNAL_BANK_ACCOUNT_ID,
PAYER_PARTY_ID,
CUST_ACCOUNT_ID,
ACCT_SITE_USE_ID,
ORG_ID,
ORG_TYPE,
LEGAL_ENTITY_ID,
BILL_TO_ADDRESS_ID,
SETTLEMENT_CUSTOMER_REFERENCE,
AR_RECEIPT_METHOD_ID,
BR_DRAWEE_ISSUED_FLAG,
BR_SIGNED_FLAG,
BR_DISPUTED_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
AUTH_TRANSACTION_ID,
auth_sales_rep_party_id,
auth_tangibleid,
auth_bepkey,
auth_payeeid,
auth_bepid,
auth_mpayeeid,
auth_ecappid,
auth_paymentmethodname,
auth_mtangibleid,
auth_payeeinstrid,
auth_payerid,
auth_payerinstrid,
auth_detaillookup,
auth_instrnumber,
auth_instrtype,
auth_instrsubtype,
auth_currencynamecode,
auth_desturl,
auth_nlslang,
auth_amount,
auth_process_profile_code,
auth_payment_channel_code,
auth_proc_reference_code,
auth_proc_reference_amount,
auth_legal_entity_id,
auth_settlement_due_date,
auth_bill_to_address_id,
auth_br_maturity_date,
auth_factored_flag,
auth_payment_function,
auth_payer_party_id,
auth_cust_account_id,
auth_acct_site_use_id,
auth_org_id,
auth_org_type,
bep_type,
payer_instr_assignment_id,
debit_auth_flag,
debit_auth_method,
debit_auth_reference,
CAPTURE_TRANSACTION_ID,
int_bank_country,
settle_require_vrfy_flag,
auth_count,
auth_dirdeb_instruction_code,
card_subtype_code,
card_data_level
FROM
(SELECT /*+ leading(orders.intrm) cardinality(orders.intrm 10) */
trxnext.TRXN_REF_NUMBER1,
trxnext.TRXN_REF_NUMBER2,
trxnext.PAYMENT_CHANNEL_CODE,
trxnext.PO_NUMBER,
trxnext.VOICE_AUTHORIZATION_FLAG,
payer.PAYMENT_FUNCTION,
SUBSTR(orders.CALL_APP_SERVICE_REQ_CODE, 1, 255) CALL_APP_SERVICE_REQ_CODE,
orders.SETTLEMENT_DATE,
orders.SETTLEMENT_DUE_DATE,
orders.BR_MATURITY_DATE,
SUBSTR(orders.ORDER_DESCRIPTION, 1, 255) ORDER_DESCRIPTION,
SUBSTR(orders.SETTLEMENT_CURRENCY_CODE, 1, 10) SETTLEMENT_CURRENCY_CODE,
orders.SETTLEMENT_AMOUNT,
orders.PAYMENT_TRXN_EXTENSION_ID,
orders.INTERNAL_BANK_ACCOUNT_ID,
orders.PAYER_PARTY_ID,
orders.CUST_ACCOUNT_ID,
orders.ACCT_SITE_USE_ID,
orders.ORG_ID,
SUBSTR(orders.ORG_TYPE, 1, 30) ORG_TYPE,
orders.LEGAL_ENTITY_ID,
orders.BILL_TO_ADDRESS_ID,
orders.SETTLEMENT_CUSTOMER_REFERENCE,
orders.AR_RECEIPT_METHOD_ID,
orders.BR_DRAWEE_ISSUED_FLAG,
orders.BR_SIGNED_FLAG,
orders.BR_DISPUTED_FLAG,
SUBSTR(orders.ATTRIBUTE_CATEGORY, 1, 150) ATTRIBUTE_CATEGORY,
SUBSTR(orders.ATTRIBUTE1, 1, 150) ATTRIBUTE1,
SUBSTR(orders.ATTRIBUTE2, 1, 150) ATTRIBUTE2,
SUBSTR(orders.ATTRIBUTE3, 1, 150) ATTRIBUTE3,
SUBSTR(orders.ATTRIBUTE4, 1, 150) ATTRIBUTE4,
SUBSTR(orders.ATTRIBUTE5, 1, 150) ATTRIBUTE5,
SUBSTR(orders.ATTRIBUTE6, 1, 150) ATTRIBUTE6,
SUBSTR(orders.ATTRIBUTE7, 1, 150) ATTRIBUTE7,
SUBSTR(orders.ATTRIBUTE8, 1, 150) ATTRIBUTE8,
SUBSTR(orders.ATTRIBUTE9, 1, 150) ATTRIBUTE9,
SUBSTR(orders.ATTRIBUTE10, 1, 150) ATTRIBUTE10,
SUBSTR(orders.ATTRIBUTE11, 1, 150) ATTRIBUTE11,
SUBSTR(orders.ATTRIBUTE12, 1, 150) ATTRIBUTE12,
SUBSTR(orders.ATTRIBUTE13, 1, 150) ATTRIBUTE13,
SUBSTR(orders.ATTRIBUTE14, 1, 150) ATTRIBUTE14,
SUBSTR(orders.ATTRIBUTE15, 1, 150) ATTRIBUTE15,
orders.CREATED_BY,
orders.CREATION_DATE,
orders.LAST_UPDATED_BY,
orders.LAST_UPDATE_DATE,
orders.LAST_UPDATE_LOGIN,
orders.OBJECT_VERSION_NUMBER,
auth.transactionid AUTH_TRANSACTION_ID,
auth.sales_rep_party_id auth_sales_rep_party_id,
auth.tangibleid auth_tangibleid,
auth.bepkey auth_bepkey,
auth.payeeid auth_payeeid,
auth.bepid auth_bepid,
auth.mpayeeid auth_mpayeeid,
auth.ecappid auth_ecappid,
auth.paymentmethodname auth_paymentmethodname,
auth.mtangibleid auth_mtangibleid,
auth.payeeinstrid auth_payeeinstrid,
auth.payerid auth_payerid,
auth.payerinstrid auth_payerinstrid,
auth.detaillookup auth_detaillookup,
auth.instrnumber auth_instrnumber,
auth.instrtype auth_instrtype,
auth.instrsubtype auth_instrsubtype,
auth.currencynamecode auth_currencynamecode,
auth.desturl auth_desturl,
auth.nlslang auth_nlslang,
auth.amount auth_amount,
auth.process_profile_code auth_process_profile_code,
auth.payment_channel_code auth_payment_channel_code,
auth.proc_reference_code auth_proc_reference_code,
auth.proc_reference_amount auth_proc_reference_amount,
auth.legal_entity_id auth_legal_entity_id,
auth.settlement_due_date auth_settlement_due_date,
auth.bill_to_address_id auth_bill_to_address_id,
auth.br_maturity_date auth_br_maturity_date,
decode(orders.remit_method_code, ''STANDARD'', ''N'', ''FACTORING'', ''Y'', ''STANDARD_AND_FACTORING'', ''Y'', ''N'') auth_factored_flag,
auth_payer.payment_function auth_payment_function,
auth_payer.party_id auth_payer_party_id,
auth_payer.cust_account_id auth_cust_account_id,
auth_payer.acct_site_use_id auth_acct_site_use_id,
auth_payer.org_id auth_org_id,
auth_payer.org_type auth_org_type,
bep.bep_type,
auth.payer_instr_assignment_id,
auth.debit_auth_flag,
auth.debit_auth_method,
auth.debit_auth_reference,
captures.transactionid CAPTURE_TRANSACTION_ID,
BranchParty.country int_bank_country,
eft_sys.settle_require_vrfy_flag,
COUNT(auth.transactionid) OVER (PARTITION BY
orders.PAYMENT_TRXN_EXTENSION_ID) auth_count,
auth.dirdeb_instruction_code auth_dirdeb_instruction_code,
auth_trxn_core.card_subtype_code card_subtype_code,
auth_trxn_core.card_data_level card_data_level
FROM '||l_view_name||' orders,
IBY_FNDCPT_TX_EXTENSIONS trxnext,
IBY_EXTERNAL_PAYERS_ALL payer,
IBY_TRXN_SUMMARIES_ALL auth,
IBY_FNDCPT_TX_OPERATIONS trxnop,
IBY_TRXN_SUMMARIES_ALL captures,
IBY_EXTERNAL_PAYERS_ALL auth_payer,
IBY_BEPINFO bep,
IBY_FNDCPT_USER_EFT_PF_B eft_user,
IBY_FNDCPT_SYS_EFT_PF_B eft_sys,
CE_BANK_ACCOUNTS int_ba,
--CE_BANK_BRANCHES_V branch
HZ_PARTIES BranchParty,
HZ_ORGANIZATION_PROFILES BranchOrgProfile,
IBY_TRXN_CORE auth_trxn_core
WHERE orders.PAYMENT_TRXN_EXTENSION_ID = trxnext.TRXN_EXTENSION_ID(+)
AND trxnop.transactionid = auth.transactionid(+)
AND auth.payerid = auth_payer.ext_payer_id(+)
AND auth.bepid = bep.bepid
AND auth.reqtype(+) = ''ORAPMTREQ''
AND DECODE(auth.status(+),''0'',1,''100'',1,''31'',1,''32'',1,0) = 1
AND orders.PAYMENT_TRXN_EXTENSION_ID = trxnop.TRXN_EXTENSION_ID(+)
AND trxnop.transactionid = captures.transactionid(+)
AND DECODE(captures.reqtype(+), ''ORAPMTCAPTURE'',1, ''ORAPMTBATCHREQ'',1, 0) = 1
AND DECODE(captures.status(+), 0,1, 100,1, 11,1, 0) = 1
AND trxnext.EXT_PAYER_ID = payer.EXT_PAYER_ID(+)
AND DECODE(auth.instrtype, ''BANKACCOUNT'',auth.PROCESS_PROFILE_CODE,
NULL) = eft_user.USER_EFT_PROFILE_CODE(+)
AND eft_user.SYS_EFT_PROFILE_CODE = eft_sys.SYS_EFT_PROFILE_CODE(+)
AND orders.INTERNAL_BANK_ACCOUNT_ID = int_ba.BANK_ACCOUNT_ID(+)
--AND int_ba.BANK_BRANCH_ID = branch.BRANCH_PARTY_ID(+)
AND int_ba.BANK_BRANCH_ID = BranchParty.party_id(+)
AND BranchOrgProfile.party_id(+) = BranchParty.party_id
AND BranchParty.status(+) = ''A''
AND SYSDATE BETWEEN TRUNC(BranchOrgProfile.effective_start_date(+))
AND NVL(TRUNC(BranchOrgProfile.effective_end_date(+)),SYSDATE + 1 )
AND auth_trxn_core.trxnmid(+) = auth.trxnmid
'||l_where_clause_index||'
ORDER BY orders.PAYMENT_TRXN_EXTENSION_ID)';
select country_code
into lx_countrycode
from iby_ext_bank_accounts
where ext_bank_account_id =l_settlement_trxn_rec.auth_payerinstrid;
SELECT IBY_TANGIBLE_S.NEXTVAL
INTO l_mtangible
FROM DUAL;
l_Tangible_Tab(tangibleIndx).LAST_UPDATED_BY := FND_GLOBAL.user_id;
l_Tangible_Tab(tangibleIndx).LAST_UPDATE_DATE := sysdate;
l_Tangible_Tab(tangibleIndx).LAST_UPDATE_LOGIN := FND_GLOBAL.login_id;
SELECT DECODE(b.auth_reversal_flag, 'M', DECODE(i.auth_reversal_flag,'M','Y','N'), 'N')
INTO l_rev_allowed
FROM iby_bepinfo b, iby_creditcard_issuers_b i
WHERE b.bepid = l_settlement_trxn_rec.auth_bepid
AND i.card_issuer_code = l_settlement_trxn_rec.auth_instrsubtype;
l_Trxn_Tab(indx).UPDATEDATE := sysdate;
l_Trxn_Tab(indx).LAST_UPDATE_DATE := sysdate;
l_Trxn_Tab(indx).LAST_UPDATED_BY := FND_GLOBAL.user_id;
l_Trxn_Tab(indx).LAST_UPDATE_LOGIN := FND_GLOBAL.login_id;
SELECT MAX(debit_authorization_id) into l_debit_auth_id
FROM iby_debit_authorizations
WHERE external_bank_account_use_id = l_settlement_trxn_rec.payer_instr_assignment_id
AND nvl(auth_cancel_date,sysdate+10) >= sysdate
-- AND auth_cancel_date is null
AND debit_auth_flag = 'Y'
-- AND nvl(debit_auth_end,sysdate) <= sysdate
AND nvl(debit_auth_end,sysdate+10) >= sysdate;
SELECT payment_type_code into l_payment_type_code
FROM iby_debit_authorizations
WHERE debit_authorization_id = l_debit_auth_id;
select count(*) into l_mandate_trxn_count
from iby_trxn_summaries_all summ
,iby_fndcpt_tx_extensions ext
,iby_fndcpt_tx_operations op
where summ.transactionid = op.transactionid
and op.trxn_extension_id = ext.trxn_extension_id
and op.transactionid = l_settlement_trxn_rec.auth_transactionid
and ext.seq_type_last = 'Y';
select count(*) into l_mandate_trxn_count
from iby_trxn_summaries_all
where trxntypeid = 100
and instrtype = 'BANKACCOUNT'
and debit_authorization_id = l_debit_auth_id
and status in (100,0,111);
l_TrxnCore_Tab(trxnCoreIndx).last_update_date := sysdate;
l_TrxnCore_Tab(trxnCoreIndx).last_updated_by := FND_GLOBAL.user_id;
l_TrxnCore_Tab(trxnCoreIndx).last_update_login := FND_GLOBAL.login_id;
print_debuginfo(l_module_name, 'Inserting ' || l_Trxn_Tab.count ||
' transactions');
INSERT INTO IBY_TRXN_SUMMARIES_ALL VALUES l_Trxn_Tab(j);
print_debuginfo(l_module_name, 'Inserted ' || SQL%ROWCOUNT
|| ' transactions');
-- UPDATE iby_fndcpt_tx_extensions
-- SET payment_system_order_number = TREAT(l_pson_Tab(j) AS Pson_rec_type).tangibleid
-- WHERE trxn_extension_id = TREAT(l_pson_Tab(j) AS Pson_rec_type).extension_id;
UPDATE iby_fndcpt_tx_extensions
SET payment_system_order_number = l_pson_Tab(i).tangibleid,
object_version_number = object_version_number + 1
WHERE trxn_extension_id = l_pson_Tab(i).extension_id;
INSERT INTO IBY_TRXN_CORE VALUES l_TrxnCore_Tab(j);
INSERT INTO IBY_TANGIBLE VALUES l_Tangible_Tab(j);
SELECT NVL(settled_flag,'N'), NVL(returned_flag,'N'),
x.instrument_type, x.instrument_id,
x.origin_application_id, a.application_short_name,
x.order_id, x.trxn_ref_number1, x.trxn_ref_number2,
x.payment_channel_code
FROM iby_trxn_extensions_v x,
iby_fndcpt_tx_extensions xt,
-- iby_pmt_instr_uses_all i,
iby_external_payers_all p,
fnd_application a
-- [lmallick] - Validate the payer context against the payer
-- stamped on the extension. Not the assignment.
WHERE --(x.instr_assignment_id = i.instrument_payment_use_id)
-- can assume this assignment is for funds capture
-- AND
(x.origin_application_id = a.application_id)
-- AND (i.ext_pmt_party_id = p.ext_payer_id)
AND (xt.EXT_PAYER_ID = p.ext_payer_id)
AND (x.trxn_extension_id = ci_extension_id)
AND (p.party_id = ci_payer.Party_Id)
AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
(ci_payer.org_type, ci_payer.org_id,
ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
p.cust_account_id,p.acct_site_use_id) = 'T');
SELECT settlement_id, payee_id, org_id, payment_system_order_number
FROM iby_trxn_ext_settlements_v
WHERE (trxn_extension_id = ci_extension_id)
ORDER BY settlement_id DESC;
SELECT RECEIPT_METHOD_ID
INTO l_rec_mth_id
FROM ar_cash_receipts_all
WHERE payment_trxn_extension_id = p_trxn_entity_id;
INSERT INTO iby_fndcpt_tx_operations
(trxn_extension_id, transactionid,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number
)
VALUES
(p_trxn_entity_id, lx_creditresp.Trxn_Id,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1
);
INSERT INTO iby_fndcpt_tx_operations
(trxn_extension_id, transactionid,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login, object_version_number
)
SELECT source_trxn_extension_id, lx_creditresp.Trxn_Id,
fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
fnd_global.login_id, 1
FROM iby_fndcpt_tx_xe_copies
START WITH copy_trxn_extension_id = p_trxn_entity_id
CONNECT BY source_trxn_extension_id = PRIOR copy_trxn_extension_id;
UPDATE iby_fndcpt_tx_extensions
SET payment_system_order_number = l_tangible.Tangible_Id
WHERE trxn_extension_id = p_trxn_entity_id;
UPDATE iby_trxn_summaries_all
SET initiator_extension_id = p_trxn_entity_id
WHERE transactionid = lx_creditresp.Trxn_Id
AND reqtype = 'ORAPMTCREDIT';
UPDATE iby_fndcpt_tx_extensions
SET payment_system_order_number = l_return_pson
WHERE trxn_extension_id = p_trxn_entity_id;
UPDATE iby_trxn_summaries_all
SET initiator_extension_id = p_trxn_entity_id
WHERE transactionid = lx_returnresp.Trxn_Id
AND reqtype = 'ORAPMTRETURN';
SELECT chnl.instrument_type, xt.origin_application_id,
a.application_short_name, xt.payment_channel_code,
xt.instrument_security_code, xt.encrypted,
xt.instr_code_sec_segment_id, xt.instr_sec_code_length
FROM iby_fndcpt_tx_extensions xt,
iby_external_payers_all p,
fnd_application a,
iby_fndcpt_pmt_chnnls_b chnl
WHERE (xt.origin_application_id = a.application_id)
AND (xt.EXT_PAYER_ID = p.ext_payer_id)
AND (xt.trxn_extension_id = ci_extension_id)
AND (xt.payment_channel_code = chnl.payment_channel_code)
AND (p.party_id = ci_payer.Party_Id)
AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
(ci_payer.org_type, ci_payer.org_id,
ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
p.cust_account_id,p.acct_site_use_id) = 'T');
SELECT auth.transactionid, auth.status, auth.amount, auth.currencynamecode,
bep.auth_reversal_flag, brand.auth_reversal_flag,
xt.payment_system_order_number, auth.payeeid,
auth.payerinstrid, tg.memo,tg.order_medium,
auth.org_id, core.authcode, core.tracenumber,
core.taxamount, core.shipfromzip, core.shiptozip,
auth.factored_flag, auth.ar_receipt_method_id
FROM iby_fndcpt_tx_extensions xt,
iby_fndcpt_tx_operations op,
iby_trxn_summaries_all auth,
iby_tangible tg,
iby_trxn_core core,
iby_bepinfo bep,
iby_creditcard_issuers_b brand
WHERE (xt.trxn_extension_id = ci_extension_id)
AND (xt.trxn_extension_id = op.trxn_extension_id)
AND (op.transactionid = auth.transactionid)
AND (auth.reqtype = 'ORAPMTREQ')
AND (auth.status = 0)
AND (auth.tangibleid = tg.tangibleid)
AND (auth.trxnmid = core.trxnmid)
AND (auth.bepid = bep.bepid)
AND (auth.instrsubtype = brand.card_issuer_code);
SELECT 1 FROM iby_trxn_summaries_all
WHERE transactionid = ci_transactionid
AND reqtype = 'ORAPMTCAPTURE'
AND status IN (0,100,111,11);
UPDATE iby_fndcpt_tx_extensions
SET payment_system_order_number = l_tangible.Tangible_Id
WHERE trxn_extension_id = p_trxn_entity_id;
UPDATE iby_trxn_summaries_all
SET initiator_extension_id = p_trxn_entity_id
WHERE transactionid = l_reqresp.Trxn_Id
AND reqtype = 'ORAPMTREQ'
AND initiator_extension_id IS NULL;
SELECT x.trxn_extension_id, x.instrument_security_code
FROM iby_fndcpt_tx_extensions x
WHERE (NVL(x.encrypted,'N') = 'N')
AND (NOT instrument_security_code IS NULL);
UPDATE iby_fndcpt_tx_extensions
SET
instrument_security_code = LPAD('X',LENGTH(instrument_security_code),'X'),
encrypted = 'Y',
instr_code_sec_segment_id = lx_segment_id,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
object_version_number = object_version_number + 1
WHERE trxn_extension_id = c_ext_rec.trxn_extension_id;
SELECT x.trxn_extension_id, x.instr_sec_code_length,
NVL(x.encrypted,'N'), s.segment_cipher_text, k.subkey_cipher_text,
s.sec_segment_id
FROM iby_fndcpt_tx_extensions x, iby_security_segments s,
iby_sys_security_subkeys k
WHERE (NVL(x.encrypted,'N') = 'Y')
AND (NOT instrument_security_code IS NULL)
AND (x.instr_code_sec_segment_id = s.sec_segment_id(+))
AND (s.sec_subkey_id = k.sec_subkey_id(+));
UPDATE iby_fndcpt_tx_extensions
SET
instrument_security_code =
Get_Security_Code(p_sys_security_key,
c_ext_rec.subkey_cipher_text,
c_ext_rec.segment_cipher_text,
c_ext_rec.instr_sec_code_length),
encrypted = 'N',
instr_code_sec_segment_id = NULL,
last_updated_by = fnd_global.user_id,
last_update_date = SYSDATE,
last_update_login = fnd_global.login_id,
object_version_number = object_version_number + 1
WHERE trxn_extension_id = c_ext_rec.trxn_extension_id;
DELETE iby_security_segments
WHERE sec_segment_id = c_ext_rec.sec_segment_id;
SELECT x.instrument_security_code, x.instr_sec_code_length,
NVL(x.encrypted,'N'), s.segment_cipher_text, k.subkey_cipher_text
FROM iby_fndcpt_tx_extensions x, iby_security_segments s,
iby_sys_security_subkeys k
WHERE (trxn_extension_id = ci_extension_id)
AND (x.instr_code_sec_segment_id = s.sec_segment_id(+))
AND (s.sec_subkey_id = k.sec_subkey_id(+));
SELECT s.segment_cipher_text, k.subkey_cipher_text
FROM iby_security_segments s, iby_sys_security_subkeys k
WHERE (s.sec_segment_id = ci_segment_id)
AND (s.sec_subkey_id = k.sec_subkey_id);
SELECT
iba.account_owner_org_id -- legal_entity_id
INTO
l_le_id
FROM
CE_BANK_ACCOUNTS iba
WHERE
iba.bank_account_id = l_intbankacct_id
;