[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ts.currencynamecode
FROM iby_batches_all ba, iby_trxn_summaries_all ts
WHERE (ba.mbatchid = ci_mbatch_id)
AND (ba.payeeid = ts.payeeid)
AND (ba.batchid = ts.batchid)
GROUP BY ts.currencynamecode;
SELECT validation_code_package, validation_code_entry_point
FROM iby_validation_sets_b vs, iby_fndcpt_sys_cc_pf_b pf,
iby_val_assignments va
WHERE (vs.validation_code_language = 'PLSQL')
AND (vs.validation_level_code = 'INSTRUCTION' )
AND (pf.payment_system_id = ci_bep_id)
AND (pf.settlement_format_code = va.assignment_entity_id)
AND (va.val_assignment_entity_type = 'FORMAT')
AND (va.validation_set_code = vs.validation_set_code)
AND (NVL(va.inactive_date,SYSDATE-100) < SYSDATE);
SELECT COUNT(transactionid)
INTO l_sec_trxn_count
FROM iby_batches_all ba, iby_trxn_summaries_all ts
WHERE (ba.mbatchid = p_mbatch_id)
AND (ba.payeeid = ts.payeeid)
AND (ba.batchid = ts.batchid)
AND (NOT sub_key_id IS NULL);
SELECT COUNT(transactionid)
INTO x_trxn_count
FROM iby_batches_all ba, iby_trxn_summaries_all ts
WHERE (ba.mbatchid = p_mbatch_id)
AND (ba.payeeid = ts.payeeid)
AND (ba.batchid = ts.batchid);
PROCEDURE insert_extensibility
(
p_trxnmid IN iby_trxn_summaries_all.trxnmid%TYPE,
p_commit IN VARCHAR2,
p_extend_names IN JTF_VARCHAR2_TABLE_100,
p_extend_vals IN JTF_VARCHAR2_TABLE_200
)
IS
BEGIN
IF (p_extend_names IS NULL) THEN
RETURN;
INSERT INTO iby_trxn_extensibility
(trxn_extend_id,trxnmid,extend_name,extend_value,created_by,
creation_date,last_updated_by,last_update_date,last_update_login,
object_version_number)
VALUES
(iby_trxn_extensibility_s.NEXTVAL,
p_trxnmid,p_extend_names(i),p_extend_vals(i),
fnd_global.user_id,sysdate,fnd_global.user_id,sysdate,
fnd_global.login_id,1);
END insert_extensibility;
/* Inserts a new row into the IBY_TRXN_SUMMARIES_ALL table. This method */
/* would be called every time a MIPP authorize operation is performed. */
PROCEDURE insert_auth_txn
(
ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
order_id_in IN iby_transactions_v.order_id%TYPE,
merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
vendor_key_in IN iby_transactions_v.bepkey%TYPE,
amount_in IN iby_transactions_v.amount%TYPE,
currency_in IN iby_transactions_v.currency%TYPE,
status_in IN iby_transactions_v.status%TYPE,
time_in IN iby_transactions_v.time%TYPE,
payment_name_in IN iby_transactions_v.payment_name%TYPE,
payment_type_in IN iby_transactions_v.payment_type%TYPE,
trxn_type_in IN iby_transactions_v.trxn_type%TYPE,
authcode_in IN iby_transactions_v.authcode%TYPE,
referencecode_in IN iby_transactions_v.referencecode%TYPE,
AVScode_in IN iby_transactions_v.AVScode%TYPE,
acquirer_in IN iby_transactions_v.acquirer%TYPE,
Auxmsg_in IN iby_transactions_v.Auxmsg%TYPE,
vendor_code_in IN iby_transactions_v.vendor_code%TYPE,
vendor_message_in IN iby_transactions_v.vendor_message%TYPE,
error_location_in IN iby_transactions_v.error_location%TYPE,
trace_number_in IN iby_transactions_v.TraceNumber%TYPE,
org_id_in IN iby_trxn_summaries_all.org_id%type,
billeracct_in IN iby_tangible.acctno%type,
refinfo_in IN iby_tangible.refinfo%type,
memo_in IN iby_tangible.memo%type,
order_medium_in IN iby_tangible.order_medium%TYPE,
eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
payerid_in IN iby_trxn_summaries_all.payerid%type,
instrtype_in IN iby_trxn_summaries_all.instrType%type,
cvv2result_in IN iby_trxn_core.CVV2Result%type,
master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
subkey_seed_in IN RAW,
trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
dateofvoiceauth_in IN iby_trxn_core.date_of_voice_authorization%TYPE,
instr_expirydate_in IN iby_trxn_core.instr_expirydate%TYPE,
instr_sec_val_in IN VARCHAR2,
card_subtype_in IN iby_trxn_core.card_subtype_code%TYPE,
card_data_level_in IN iby_trxn_core.card_data_level%TYPE,
instr_owner_name_in IN iby_trxn_core.instr_owner_name%TYPE,
instr_address_line1_in IN iby_trxn_core.instr_owner_address_line1%TYPE,
instr_address_line2_in IN iby_trxn_core.instr_owner_address_line2%TYPE,
instr_address_line3_in IN iby_trxn_core.instr_owner_address_line3%TYPE,
instr_city_in IN iby_trxn_core.instr_owner_city%TYPE,
instr_state_in IN iby_trxn_core.instr_owner_state_province%TYPE,
instr_country_in IN iby_trxn_core.instr_owner_country%TYPE,
instr_postalcode_in IN iby_trxn_core.instr_owner_postalcode%TYPE,
instr_phonenumber_in IN iby_trxn_core.instr_owner_phone%TYPE,
instr_email_in IN iby_trxn_core.instr_owner_email%TYPE,
pos_reader_cap_in IN iby_trxn_core.pos_reader_capability_code%TYPE,
pos_entry_method_in IN iby_trxn_core.pos_entry_method_code%TYPE,
pos_card_id_method_in IN iby_trxn_core.pos_id_method_code%TYPE,
pos_auth_source_in IN iby_trxn_core.pos_auth_source_code%TYPE,
reader_data_in IN iby_trxn_core.reader_data%TYPE,
extend_names_in IN JTF_VARCHAR2_TABLE_100,
extend_vals_in IN JTF_VARCHAR2_TABLE_200,
debit_network_code_in IN iby_trxn_core.debit_network_code%TYPE,
surcharge_amount_in IN iby_trxn_core.surcharge_amount%TYPE,
proc_tracenumber_in IN iby_trxn_core.proc_tracenumber%TYPE,
transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
sub_key_id_in IN iby_trxn_summaries_all.sub_key_id%TYPE,
voiceAuthFlag_in IN iby_trxn_core.voiceauthflag%TYPE,
reauth_trxnid_in IN iby_trxn_summaries_all.TransactionID%TYPE
)
IS
l_num_trxns NUMBER := 0;
-- new auth request, insert into table
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO l_trxn_mid
FROM dual;
select card_owner_id
into l_payer_party_id
from iby_creditcard
where instrid=payerinstrid_in;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID,TrxntypeID, ReqType, ReqDate,
Amount,CurrencyNameCode, UpdateDate,Status, PaymentMethodName,
TangibleID,MPayeeID, PayeeID,BEPID,bepKey,mtangibleid,
BEPCode,BEPMessage,Errorlocation,ecappid,org_id,
payerinstrid, instrnumber, payerid, instrType,
last_update_date,last_updated_by,creation_date, created_by,
last_update_login,object_version_number,instrsubtype,trxnref,
org_type, payment_channel_code, factored_flag,
cc_issuer_range_id, instrnum_hash, instrnum_length,
instrnum_sec_segment_id, payer_party_id, process_profile_code,
salt_version,needsupdt,sub_key_id)
VALUES (l_trxn_mid, l_transaction_id, trxn_type_in, req_type_in,
sysdate,
amount_in, currency_in, time_in, status_in, payment_type_in,
order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
vendor_key_in, l_tmid, vendor_code_in, vendor_message_in,
error_location_in, ecapp_id_in, org_id_in,
payerinstrid_in, l_instrnum, payerid_in, instrType_in,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1, l_cc_type, trxnref_in,
org_type_in, payment_channel_code_in, factored_flag_in,
lx_range_id, lx_cc_hash, lx_instr_len, lx_segment_id,
l_payer_party_id, process_profile_code_in,
iby_security_pkg.get_salt_version,'Y',sub_key_id_in);
INSERT INTO iby_trxn_core (
TrxnMID, AuthCode, date_of_voice_authorization, voiceauthflag,
ReferenceCode, TraceNumber,AVSCode, CVV2Result, Acquirer,
Auxmsg, InstrName,
Instr_Expirydate, expiry_sec_segment_id,
Card_Subtype_Code, Card_Data_Level,
Instr_Owner_Name, chname_sec_segment_id, encrypted,
Instr_Owner_Address_Line1, Instr_Owner_Address_Line2,
Instr_Owner_Address_Line3, Instr_Owner_City, Instr_Owner_State_Province,
Instr_Owner_Country, Instr_Owner_PostalCode, Instr_Owner_Phone,
Instr_Owner_Email,
POS_Reader_Capability_Code, POS_Entry_Method_Code,
POS_Id_Method_Code, POS_Auth_Source_Code, Reader_Data, POS_Trxn_Flag,
debit_network_code, surcharge_amount, proc_tracenumber,
last_update_date, last_updated_by,
creation_date, created_by, last_update_login, object_version_number
) VALUES (
l_trxn_mid, authcode_in, dateofvoiceauth_in, l_voiceauth_flag,
referencecode_in, trace_number_in, AVScode_in, cvv2result_in,
acquirer_in, Auxmsg_in, payment_name_in,
l_expirydate, l_expdate_sec_segment_id,
card_subtype_in, card_data_level_in,
l_masked_chname, l_chname_sec_segment_id, l_encrypted,
instr_address_line1_in, instr_address_line2_in, instr_address_line3_in,
instr_city_in, instr_state_in, instr_country_in, instr_postalcode_in,
instr_phonenumber_in, instr_email_in,
pos_reader_cap_in, pos_entry_method_in, pos_card_id_method_in,
pos_auth_source_in, reader_data_in, l_pos_txn,debit_network_code_in, surcharge_amount_in, proc_tracenumber_in,
sysdate,fnd_global.user_id,
sysdate,fnd_global.user_id,fnd_global.login_id,1
);
insert_extensibility(l_trxn_mid,'N',extend_names_in,extend_vals_in);
--test_debug('insertion complete..');
SELECT TrxnMID, TransactionID, Mtangibleid, instrnum_sec_segment_id, sub_key_id
INTO l_trxn_mid, transaction_id_out, l_tmid, l_old_segment_id, l_sub_key_id
FROM iby_trxn_summaries_all
WHERE (TangibleID = order_id_in)
AND (UPPER(ReqType) = UPPER(req_type_in))
AND (PayeeID = merchant_id_in)
AND (status IN (11,9));
UPDATE iby_trxn_summaries_all
SET BEPID = vendor_id_in,
bepKey = vendor_key_in,
Amount = amount_in,
-- amount, bepid is updated as the request can come in
-- from another online
TrxntypeID = trxn_type_in,
CurrencyNameCode = currency_in,
UpdateDate = time_in,
Status = status_in,
ErrorLocation = error_location_in,
BEPCode = vendor_code_in,
BEPMessage = vendor_message_in,
instrType = instrType,
-- we don't update payerinstrid and org_id here
-- as it may overwrite previous payerinstrid, org_id
-- (from offline scheduling)
-- in case this request comes in from scheduler
-- could be a problem if this request comes in from
-- another online, w/ a different payment instrment
-- for a previous failed trxn, regardless, the
--'instrnumber' will always be correct
--org_id = org_id_in,
--payerinstrid = payerinstrid_in,
-- same for org_type
PaymentMethodName = NVL(payment_type_in,PaymentMethodName),
instrnumber = l_instrnum,
instrnum_hash = lx_cc_hash,
instrnum_length = lx_instr_len,
cc_issuer_range_id = lx_range_id,
instrnum_sec_segment_id = lx_segment_id,
trxnref = trxnref_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
creation_date = sysdate,
created_by = fnd_global.user_id,
object_version_number = object_version_number + 1,
payment_channel_code = payment_channel_code_in,
factored_flag = factored_flag_in
WHERE TrxnMID = l_trxn_mid;
DELETE iby_security_segments WHERE sec_segment_id = l_old_segment_id;
UPDATE iby_trxn_core
SET AuthCode = authcode_in,
date_of_voice_authorization = dateofvoiceauth_in,
--voiceauthflag = DECODE(dateofvoiceauth_in, NULL, 'N', 'Y'),
voiceauthflag = voiceAuthFlag_in,
AvsCode = AVScode_in,
CVV2Result = cvv2result_in,
ReferenceCode = referencecode_in,
Acquirer = acquirer_in,
Auxmsg = Auxmsg_in,
TraceNumber = trace_number_in,
InstrName = NVL(payment_name_in,InstrName),
encrypted = l_encrypted,
Instr_Expirydate = l_expirydate,
expiry_sec_segment_id = l_expdate_sec_segment_id,
Card_Subtype_Code = card_subtype_in,
Card_Data_Level = card_data_level_in,
Instr_Owner_Name = l_masked_chname,
chname_sec_segment_id = l_chname_sec_segment_id,
Instr_Owner_Address_Line1 = instr_address_line1_in,
Instr_Owner_Address_Line2 = instr_address_line2_in,
Instr_Owner_Address_Line3 = instr_address_line3_in,
Instr_Owner_City = instr_city_in,
Instr_Owner_State_Province = instr_state_in,
Instr_Owner_Country = instr_country_in,
Instr_Owner_PostalCode = instr_postalcode_in,
Instr_Owner_Phone = instr_phonenumber_in,
Instr_Owner_Email = instr_email_in,
POS_Reader_Capability_Code = pos_reader_cap_in,
POS_Entry_Method_Code = pos_entry_method_in,
POS_Id_Method_Code = pos_card_id_method_in,
POS_Auth_Source_Code = pos_auth_source_in,
Reader_Data = reader_data_in,
POS_Trxn_Flag = l_pos_txn,
debit_network_code = debit_network_code_in,
surcharge_amount = surcharge_amount_in,
proc_tracenumber = proc_tracenumber_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
creation_date = sysdate,
created_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE TrxnMID = l_trxn_mid;
insert_extensibility(l_trxn_mid,'N',extend_names_in,extend_vals_in);
END insert_auth_txn;
PROCEDURE insert_rev_result
( trxnmid_in IN iby_trxn_summaries_all.trxnmid%TYPE,
trxnid_in IN iby_trxn_summaries_all.transactionid%TYPE,
status_in IN iby_trxn_summaries_all.status%TYPE,
trxntype_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
revised_amt_in IN iby_trxn_summaries_all.amount%TYPE,
referencecode_in IN iby_transactions_v.referencecode%TYPE DEFAULT NULL,
AVScode_in IN iby_transactions_v.AVScode%TYPE DEFAULT NULL,
acquirer_in IN iby_transactions_v.acquirer%TYPE DEFAULT NULL,
Auxmsg_in IN iby_transactions_v.Auxmsg%TYPE DEFAULT NULL,
cvv2result_in IN iby_trxn_core.CVV2Result%type,
bepcode_in IN iby_trxn_summaries_all.bepcode%TYPE,
bepmessage_in IN iby_trxn_summaries_all.bepmessage%TYPE,
error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
tracenumber_in IN iby_trxn_core.tracenumber%TYPE,
authcode_in IN iby_trxn_core.authcode%TYPE,
extend_names_in IN JTF_VARCHAR2_TABLE_100,
extend_vals_in IN JTF_VARCHAR2_TABLE_200
)
IS
l_dbg_mod VARCHAR2(100) := 'IBY_TRANSACTIONCC_PKG' || '.insert_rev_result';
SELECT trxnmid FROM iby_trxn_summaries_all
WHERE transactionid = ci_transactionid
AND reqtype = 'ORAPMTREQ'
AND trxntypeid = 2
AND status = 0;
SELECT old_ext.extend_name extend_name,
nvl(new_ext.EXTEND_VALUE, old_ext.extend_value) extend_value
FROM IBY_TRXN_EXTENSIBILITY OLD_EXT,
IBY_TRXN_EXTENSIBILITY NEW_EXT
WHERE OLD_EXT.TRXNMID=ci_trxnmid_old
AND NEW_EXT.TRXNMID(+)=ci_trxnmid_new
and old_ext.extend_name=new_ext.extend_name(+);
UPDATE iby_trxn_summaries_all
SET status = status_in,
bepcode = bepcode_in,
bepmessage = bepmessage_in,
UpdateDate = SYSDATE,
errorlocation = error_loc_in,
Last_Update_Date = sysdate,
Last_Updated_by = fnd_global.user_id,
Object_Version_Number = object_version_number + 1
WHERE trxnmid = trxnmid_in
AND reqtype = 'ORAPMTREVERSE'
AND trxntypeid = trxntype_in;
UPDATE iby_trxn_summaries_all
SET status = trxntype_in,
Last_Update_Date = sysdate,
Last_Updated_by = fnd_global.user_id,
Object_Version_Number = object_version_number + 1
WHERE trxnmid = l_old_auth_trxnmid;
UPDATE iby_trxn_core
SET authcode = authcode_in,
ReferenceCode = referencecode_in,
AVSCode = AVScode_in,
CVV2Result = cvv2result_in,
Acquirer = acquirer_in,
Auxmsg = Auxmsg_in,
Last_Update_Date = sysdate,
Last_Updated_by = fnd_global.user_id,
Object_Version_Number = object_version_number + 1
WHERE trxnmid = trxnmid_in;
iby_debug_pub.add('Inserting extensibility data for reversal record.',IBY_DEBUG_PUB.G_LEVEL_INFO,l_dbg_mod);
insert_extensibility(trxnmid_in,'N',extend_names_in,extend_vals_in);
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO l_new_auth_trxnmid
FROM dual;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID,TrxntypeID, ReqType, ReqDate,
Amount,CurrencyNameCode, UpdateDate,Status, PaymentMethodName,
TangibleID,MPayeeID, PayeeID,BEPID,bepKey,mtangibleid,
BEPCode,BEPMessage,Errorlocation,ecappid,org_id,
payerinstrid, instrnumber, payerid, instrType,
last_update_date,last_updated_by,creation_date, created_by,
last_update_login,object_version_number,instrsubtype,trxnref,
org_type, payment_channel_code, factored_flag,
cc_issuer_range_id, instrnum_hash, instrnum_length,
instrnum_sec_segment_id, payer_party_id, process_profile_code,
salt_version,needsupdt,sub_key_id,initiator_extension_id)
(SELECT l_new_auth_trxnmid, TransactionID, TrxntypeID, ReqType,ReqDate,
revised_amt_in, CurrencyNameCode, UpdateDate, 0, PaymentMethodName,
TangibleID, MPayeeID, PayeeID, BEPID,bepKey, mtangibleid,
BEPCode, BEPMessage,Errorlocation,ecappid, org_id,
payerinstrid, instrnumber, payerid, instrType,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1, instrsubtype, trxnref,
org_type, payment_channel_code, factored_flag,
cc_issuer_range_id, instrnum_hash, instrnum_length,
instrnum_sec_segment_id, payer_party_id, process_profile_code,
salt_version,needsupdt,sub_key_id,initiator_extension_id
FROM iby_trxn_summaries_all
WHERE trxnmid = l_old_auth_trxnmid);
INSERT INTO iby_trxn_core (
TrxnMID, AuthCode, date_of_voice_authorization, voiceauthflag,
ReferenceCode, TraceNumber,AVSCode, CVV2Result, Acquirer,
Auxmsg, InstrName,
Instr_Expirydate, expiry_sec_segment_id,
Card_Subtype_Code, Card_Data_Level,
Instr_Owner_Name, chname_sec_segment_id, encrypted,
Instr_Owner_Address_Line1, Instr_Owner_Address_Line2,
Instr_Owner_Address_Line3, Instr_Owner_City, Instr_Owner_State_Province,
Instr_Owner_Country, Instr_Owner_PostalCode, Instr_Owner_Phone,
Instr_Owner_Email,
POS_Reader_Capability_Code, POS_Entry_Method_Code,
POS_Id_Method_Code, POS_Auth_Source_Code, Reader_Data, POS_Trxn_Flag,
debit_network_code, surcharge_amount, proc_tracenumber,
last_update_date, last_updated_by,
creation_date, created_by, last_update_login, object_version_number)
(SELECT
l_new_auth_trxnmid, authcode_in, date_of_voice_authorization, voiceauthflag,
NVL(referencecode_in, ReferenceCode), TraceNumber, NVL(AVScode_in, AVSCode),
NVL(cvv2result_in, CVV2Result), NVL(acquirer_in, Acquirer),
NVL(Auxmsg, Auxmsg_in), InstrName,
Instr_Expirydate, expiry_sec_segment_id,
Card_Subtype_Code, Card_Data_Level,
Instr_Owner_Name, chname_sec_segment_id, encrypted,
Instr_Owner_Address_Line1, Instr_Owner_Address_Line2,
Instr_Owner_Address_Line3, Instr_Owner_City, Instr_Owner_State_Province,
Instr_Owner_Country, Instr_Owner_PostalCode, Instr_Owner_Phone,
Instr_Owner_Email,
POS_Reader_Capability_Code, POS_Entry_Method_Code,
POS_Id_Method_Code, POS_Auth_Source_Code, Reader_Data, POS_Trxn_Flag,
debit_network_code, surcharge_amount, proc_tracenumber,
sysdate,fnd_global.user_id,
sysdate,fnd_global.user_id,fnd_global.login_id,1
FROM iby_trxn_core
WHERE trxnmid = l_old_auth_trxnmid);
--insert_extensibility(l_new_auth_trxnmid,'N',extend_names_in,extend_vals_in);
-- Copy the extensibility data from the old authorization. Any updated
-- attribute obtained during reversal overrides the old values.
--
FOR extend_rec IN c_extend(l_old_auth_trxnmid,trxnmid_in) LOOP
INSERT INTO iby_trxn_extensibility
(trxn_extend_id,trxnmid,extend_name,extend_value,created_by,
creation_date,last_updated_by,last_update_date,last_update_login,
object_version_number)
VALUES
(iby_trxn_extensibility_s.NEXTVAL,
l_new_auth_trxnmid,extend_rec.extend_name,extend_rec.extend_value,
fnd_global.user_id,sysdate,fnd_global.user_id,sysdate,
fnd_global.login_id,1);
END insert_rev_result;
/* Inserts a new row into the IBY_TRXN_SUMMARIES table. This method */
/* would be called every time a capture, credit, return, or void */
/* operation is performed. */
PROCEDURE insert_other_txn
( ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
order_id_in IN iby_transactions_v.order_id%TYPE,
merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
vendor_key_in IN iby_transactions_v.bepkey%TYPE,
status_in IN iby_transactions_v.status%TYPE,
time_in IN iby_transactions_v.time%TYPE,
payment_type_in IN iby_transactions_v.payment_type%TYPE,
payment_name_in IN iby_transactions_v.payment_name%TYPE,
trxn_type_in IN iby_transactions_v.trxn_type%TYPE,
amount_in IN iby_transactions_v.amount%TYPE,
currency_in IN iby_transactions_v.currency%TYPE,
referencecode_in IN iby_transactions_v.referencecode%TYPE,
vendor_code_in IN iby_transactions_v.vendor_code%TYPE,
vendor_message_in IN iby_transactions_v.vendor_message%TYPE,
error_location_in IN iby_transactions_v.error_location%TYPE,
trace_number_in IN iby_transactions_v.TraceNumber%TYPE,
org_id_in IN iby_trxn_summaries_all.org_id%type,
billeracct_in IN iby_tangible.acctno%type,
refinfo_in IN iby_tangible.refinfo%type,
memo_in IN iby_tangible.memo%type,
order_medium_in IN iby_tangible.order_medium%TYPE,
eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
payerid_in IN iby_trxn_summaries_all.payerid%type,
master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
subkey_seed_in IN RAW,
trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
instr_expirydate_in IN iby_trxn_core.instr_expirydate%TYPE,
card_subtype_in IN iby_trxn_core.card_subtype_code%TYPE,
instr_owner_name_in IN iby_trxn_core.instr_owner_name%TYPE,
instr_address_line1_in IN iby_trxn_core.instr_owner_address_line1%TYPE,
instr_address_line2_in IN iby_trxn_core.instr_owner_address_line2%TYPE,
instr_address_line3_in IN iby_trxn_core.instr_owner_address_line3%TYPE,
instr_city_in IN iby_trxn_core.instr_owner_city%TYPE,
instr_state_in IN iby_trxn_core.instr_owner_state_province%TYPE,
instr_country_in IN iby_trxn_core.instr_owner_country%TYPE,
instr_postalcode_in IN iby_trxn_core.instr_owner_postalcode%TYPE,
instr_phonenumber_in IN iby_trxn_core.instr_owner_phone%TYPE,
instr_email_in IN iby_trxn_core.instr_owner_email%TYPE,
extend_names_in IN JTF_VARCHAR2_TABLE_100,
extend_vals_in IN JTF_VARCHAR2_TABLE_200,
transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
settlement_date_in IN iby_trxn_summaries_all.settledate%TYPE,
settlement_due_date_in IN iby_trxn_summaries_all.settlement_due_date%TYPE,
process_profile_code_in IN iby_trxn_summaries_all.process_profile_code%TYPE,
instrtype_in IN iby_trxn_summaries_all.instrtype%TYPE
)
IS
l_num_trxns NUMBER := 0;
l_module_name CONSTANT VARCHAR2(200) := 'IBY_TRANSACTIONCC_PKG.insert_other_txn';
SELECT mtangibleid, tangibleid, instrType, instrsubtype,
process_profile_code, payer_party_id, payment_channel_code,
currencynamecode, payerid, payerinstrid
FROM iby_trxn_summaries_all
WHERE (transactionid = ci_trxnid)
-- ignore the reversal trxntypes
AND (trxntypeid NOT IN (22,23))
--
-- only consider succeeded ones here
-- b/c different mtangibleid may get created in case of failed
-- auth
--
-- status 100 is equivalent to 0
--
AND (status IN (0,100))
--
-- sort by trxnmid as lowest value indicates the
-- first trxn for this order, which is most likely
-- to have all information
--
ORDER BY trxnmid ASC;
print_debuginfo(l_module_name, 'INSERTING TRANSACTION ROW');
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO l_trxn_mid
FROM dual;
SELECT card_issuer_code INTO l_subtype FROM
iby_creditcard WHERE instrid = payerinstrid_in;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID,TrxntypeID, ReqType, ReqDate,
Amount,CurrencyNameCode, UpdateDate,Status, PaymentMethodName,
TangibleID,MPayeeID, PayeeID,BEPID,bepKey, MtangibleId,
BEPCode,BEPMessage,Errorlocation,ecappid,org_id,
payerinstrid, instrnumber, payerid,
last_update_date,last_updated_by,creation_date,created_by,
last_update_login,object_version_number,instrType,instrsubtype,trxnref, org_type, payment_channel_code, factored_flag,
instrnum_hash, instrnum_length, cc_issuer_range_id,
instrnum_sec_segment_id, payer_party_id, process_profile_code,
salt_version,needsupdt, settledate, settlement_due_date)
VALUES (l_trxn_mid, transaction_id_in_out, trxn_type_in,
req_type_in, sysdate,
amount_in, l_currency, time_in, status_in, payment_type_in,
l_order_id, l_mpayeeid, merchant_id_in, vendor_id_in,
vendor_key_in, l_tmid, vendor_code_in, vendor_message_in,
error_location_in, ecapp_id_in, l_org_id,
l_payerinstrid, l_instrnum, l_payerid,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1, l_instrtype, l_instrsubtype, trxnref_in,
org_type_in, nvl(payment_channel_code_in, l_pmt_chnl_code), factored_flag_in,
lx_cc_hash, lx_instr_len, lx_range_id, lx_segment_id,
l_payer_party_id, l_process_profile_code,
iby_security_pkg.get_salt_version,'Y', settlement_date_in, settlement_due_date_in
);
INSERT INTO iby_trxn_core
(TrxnMID, ReferenceCode, TraceNumber, InstrName,
Instr_Expirydate, Card_Subtype_Code,
Instr_Owner_Name, Instr_Owner_Address_Line1,
Instr_Owner_Address_Line2, Instr_Owner_Address_Line3,
Instr_Owner_City, Instr_Owner_State_Province, Instr_Owner_Country,
Instr_Owner_PostalCode, Instr_Owner_Phone, Instr_Owner_Email,
last_update_date,last_updated_by,creation_date,created_by,
last_update_login,object_version_number)
VALUES
(l_trxn_mid, referencecode_in, trace_number_in, payment_name_in,
l_expirydate, card_subtype_in,
instr_owner_name_in, instr_address_line1_in, instr_address_line2_in,
instr_address_line3_in, instr_city_in, instr_state_in,
instr_country_in, instr_postalcode_in, instr_phonenumber_in,
instr_email_in,
sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
fnd_global.login_id,1);
insert_extensibility(l_trxn_mid,'N',extend_names_in,extend_vals_in);
SELECT TrxnMID,Mtangibleid,transactionid, instrnum_sec_segment_id
INTO l_trxn_mid,l_tmid,transaction_id_in_out, l_old_segment_id
FROM iby_trxn_summaries_all
WHERE TangibleID = order_id_in
AND UPPER(ReqType) = UPPER(req_type_in)
AND PayeeID = merchant_id_in
AND Status IN (9,11);
--Update iby_tangible table
iby_bill_pkg.modBill(l_tmid,order_id_in,amount_in,currency_in,
billeracct_in,refinfo_in,memo_in,
order_medium_in, eft_auth_method_in);
-- do not update 'payerinstrid, org_id' here, same reason
-- as shown in 'auth'
l_expirydate := instr_expirydate_in;
UPDATE iby_trxn_summaries_all
SET BEPID = vendor_id_in,
bepKey = vendor_key_in,
Amount = amount_in,
TrxntypeID = trxn_type_in,
CurrencyNameCode = currency_in,
UpdateDate = time_in,
Status = status_in,
ErrorLocation = error_location_in,
BEPCode = vendor_code_in,
BEPMessage = vendor_message_in,
--payerinstrid = payerinstrid_in,
PaymentMethodName = NVL(payment_type_in,PaymentMethodName),
instrtype = NVL(l_instrtype,instrtype),
instrsubtype = NVL(l_instrsubtype,instrsubtype),
instrnumber = l_instrnum,
instrnum_hash = lx_cc_hash,
instrnum_length = lx_instr_len,
cc_issuer_range_id = lx_range_id,
instrnum_sec_segment_id = lx_segment_id,
trxnref = trxnref_in,
payment_channel_code = l_pmt_chnl_code,
Last_Update_Date = sysdate,
Last_Updated_by = fnd_global.user_id,
Creation_Date = sysdate,
Created_By = fnd_global.user_id,
Object_Version_Number = object_version_number + 1
WHERE TrxnMID = l_trxn_mid;
DELETE iby_security_segments WHERE sec_segment_id = l_old_segment_id;
UPDATE iby_trxn_core
SET ReferenceCode = referencecode_in,
TraceNumber = trace_number_in,
InstrName = NVL(payment_name_in,InstrName),
Instr_Expirydate = instr_expirydate_in,
Card_Subtype_Code = card_subtype_in,
Instr_Owner_Name = instr_owner_name_in,
Instr_Owner_Address_Line1 = instr_address_line1_in,
Instr_Owner_Address_Line2 = instr_address_line2_in,
Instr_Owner_Address_Line3 = instr_address_line3_in,
Instr_Owner_City = instr_city_in,
Instr_Owner_State_Province = instr_state_in,
Instr_Owner_Country = instr_country_in,
Instr_Owner_PostalCode = instr_postalcode_in,
Instr_Owner_Phone = instr_phonenumber_in,
Instr_Owner_Email = instr_email_in,
Last_Update_Date = sysdate,
Last_Updated_by = fnd_global.user_id,
Creation_Date = sysdate,
Created_By = fnd_global.user_id,
Object_Version_Number = object_version_number + 1
WHERE TrxnMID = l_trxn_mid;
insert_extensibility(l_trxn_mid,'N',extend_names_in,extend_vals_in);
UPDATE iby_trxn_summaries_all
SET
-- CHANGE: UPDATE STATUS FOR VOIDED GATEWAY TRXNS
--
-- currently only change the status for processor
-- batched trxns; in the future should probably
-- update for gateway trxns as well
--
status=decode(status,100,114,status),
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE (transactionid=transaction_id_in_out) AND (trxntypeid=l_target_trxn_type);
END insert_other_txn;
/* Inserts a row into the iby_transaction table if auth, capture, */
/* return, credit, and void timeout */
PROCEDURE insert_timeout_txn
(req_type_in IN iby_trxn_summaries_all.ReqType%TYPE,
order_id_in IN iby_transactions_v.order_id%TYPE,
merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
vendor_key_in IN iby_transactions_v.bepkey%TYPE,
ecapp_id_in IN iby_trxn_summaries_all.ecappid%TYPE,
time_in IN iby_transactions_v.time%TYPE,
status_in IN iby_transactions_v.status%TYPE,
org_id_in IN iby_trxn_summaries_all.org_id%type,
amount_in IN iby_tangible.amount%type,
currency_in IN iby_tangible.currencynamecode%type,
billeracct_in IN iby_tangible.acctno%type,
refinfo_in IN iby_tangible.refinfo%type,
memo_in IN iby_tangible.memo%type,
order_medium_in IN iby_tangible.order_medium%TYPE,
eft_auth_method_in IN iby_tangible.eft_auth_method%TYPE,
payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
instrnum_in IN iby_trxn_summaries_all.instrnumber%type,
payerid_in IN iby_trxn_summaries_all.payerid%type,
instrtype_in IN iby_trxn_summaries_all.instrType%type,
master_key_in IN iby_security_pkg.DES3_KEY_TYPE,
subkey_seed_in IN RAW,
trxnref_in IN iby_trxn_summaries_all.trxnref%TYPE,
transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
transaction_mid_out OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE,
trxntypeid_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
org_type_in IN iby_trxn_summaries_all.org_type%TYPE,
payment_channel_code_in IN iby_trxn_summaries_all.payment_channel_code%TYPE,
factored_flag_in IN iby_trxn_summaries_all.factored_flag%TYPE,
reauth_transaction_id IN iby_trxn_summaries_all.TransactionID%TYPE
)
IS
l_num_trxns NUMBER := 0;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO l_trxn_mid
FROM dual;
SELECT DISTINCT mtangibleid, tangibleid
INTO l_tmid, l_order_id
FROM iby_trxn_summaries_all
WHERE transactionid = transaction_id_out
--
-- 100 is equivalent to 0
--
AND (status IN (0,100))
AND TRXNTYPEID NOT IN(22,23);
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID, ReqType, ReqDate,
UpdateDate,Status, Amount, CurrencyNameCode,
TangibleID,MPayeeID, PayeeID,BEPID,bepKey, ECAppID,org_id,mtangibleid,
payerinstrid, instrnumber, sub_key_id, payerid, instrType,
last_update_date,last_updated_by,creation_date,created_by,
last_update_login,object_version_number,instrsubtype,TrxnTypeID,trxnref,
org_type, payment_channel_code, factored_flag,needsupdt)
VALUES (l_trxn_mid, transaction_id_out, req_type_in, time_in,
time_in, status_in, amount_in, currency_in,
l_order_id, l_mpayeeid, merchant_id_in, vendor_id_in,
vendor_key_in, ecapp_id_in, l_org_id,l_tmid,
payerinstrid_in, l_instrnum, l_subkey_id, payerid_in, instrType_in,
sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
fnd_global.login_id,1,l_cc_type,trxntypeid_in,trxnref_in,
org_type_in, payment_channel_code_in, factored_flag_in,'Y');
INSERT INTO iby_trxn_core
(TrxnMID,
last_update_date,last_updated_by,creation_date,created_by,
last_update_login,object_version_number)
VALUES (l_trxn_mid,
sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,1);
SELECT TrxnMID, TransactionID, MtangibleId
INTO l_trxn_mid, transaction_id_out, l_tmid
FROM iby_trxn_summaries_all
WHERE TangibleID = order_id_in
AND UPPER(ReqType) = UPPER(req_type_in)
AND PayeeID = merchant_id_in
AND Status IN (9,11);
-- Update iby_tangible table
iby_bill_pkg.modBill(l_tmid,order_id_in,amount_in,currency_in,
billeracct_in,refinfo_in,memo_in,
order_medium_in, eft_auth_method_in);
UPDATE iby_trxn_summaries_all
SET UpdateDate = time_in,
Status = status_in,
BEPID = vendor_id_in,
bepKey = vendor_key_in,
ECAppID = ecapp_id_in,
-- not updating payerinstrid, org_id, org_type for the same reason
--payerinstrid = payerinstrid_in,
instrnumber = l_instrnum,
sub_key_id = l_subkey_id,
instrType = instrType_in,
trxnref = trxnref_in,
Last_Update_Date = sysdate,
Last_Updated_by = fnd_global.user_id,
Creation_Date = sysdate,
Created_By = fnd_global.user_id,
Object_Version_Number = object_version_number + 1,
payment_channel_code = payment_channel_code_in,
factored_flag = factored_flag_in
WHERE TrxnMID = l_trxn_mid;
END insert_timeout_txn;
/* Inserts a row about batch status into iby_batches_all. This will */
/* be called for link error, timeout error or other batch status */
PROCEDURE insert_batch_status
(merch_batchid_in IN iby_batches_all.batchid%TYPE,
merchant_id_in IN iby_batches_all.payeeid%TYPE,
vendor_id_in IN iby_batches_all.bepid%TYPE,
vendor_key_in IN iby_batches_all.bepkey%TYPE,
pmt_type_in IN iby_batches_all.paymentmethodname%TYPE,
status_in IN iby_batches_all.batchstatus%TYPE,
time_in IN iby_batches_all.batchclosedate%TYPE,
viby_batchid_in IN iby_batches_all.vpsbatchid%TYPE ,
currency_in IN iby_batches_all.currencynamecode%TYPE,
numtrxns_in IN iby_batches_all.NumTrxns%TYPE,
batchstate_in IN iby_batches_all.BatchStateid%TYPE,
batchtotal_in IN iby_batches_all.BatchTotal%TYPE,
saleamount_in IN iby_batches_all.BatchSales%TYPE,
cramount_in IN iby_batches_all.BatchCredit%TYPE,
gwid_in IN iby_batches_all.GWBatchID%TYPE,
vendor_code_in IN iby_batches_all.BEPcode%TYPE,
vendor_message_in IN iby_batches_all.BEPmessage%TYPE,
error_location_in IN iby_batches_all.errorlocation%TYPE,
terminal_id_in IN iby_batches_all.TerminalId%TYPE,
acquirer_id_in IN iby_batches_all.Acquirer%TYPE,
org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
req_type_in IN iby_batches_all.reqtype%TYPE,
sec_key_present_in IN VARCHAR2,
mbatchid_out OUT NOCOPY iby_batches_all.mbatchid%type
)
IS
numrows NUMBER;
SELECT COUNT(*)
INTO numrows
FROM iby_batches_all
WHERE batchid = merch_batchid_in
AND payeeid = merchant_id_in;
SELECT iby_batches_s.NEXTVAL
INTO l_mbatchid
FROM dual;
SELECT NVL(bep_type,iby_bepinfo_pkg.C_BEPTYPE_GATEWAY)
INTO l_beptype
FROM iby_bepinfo
WHERE (bepid=vendor_id_in);
UPDATE iby_trxn_summaries_all
SET
status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING,
batchid = merch_batchid_in,
mbatchid = l_mbatchid,
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE (bepid = vendor_id_in)
AND (bepkey = vendor_key_in)
AND (payeeid = merchant_id_in)
AND (status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED)
AND ((instrtype IN (iby_creditcard_pkg.C_INSTRTYPE_CCARD,
iby_creditcard_pkg.C_INSTRTYPE_PCARD)
AND
(req_type_in = iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE))
OR
(instrtype IN (l_pinlessdebitcard)
AND
(req_type_in = iby_transactioncc_pkg.C_REQTYPE_PDC_BATCHCLOSE))
OR
instrtype IS NULL)
AND (batchid IS NULL);
INSERT INTO iby_batches_all
(MBATCHID, BATCHID, MPAYEEID, PAYEEID, BEPID, BEPKEY, PAYMENTMETHODNAME,
BATCHSTATUS, BATCHCLOSEDATE, VPSBATCHID, CURRENCYNAMECODE,
NUMTRXNS, BATCHSTATEID, BATCHTOTAL, BATCHSALES, BATCHCREDIT,
GWBATCHID, BEPCODE, BEPMESSAGE, ERRORLOCATION,
TerminalId, Acquirer,reqtype, reqdate,
last_update_date,last_updated_by,creation_date,created_by,
last_update_login, object_version_number,settledate)
VALUES
( l_mbatchid, merch_batchid_in, l_mpayeeid, merchant_id_in, vendor_id_in,
vendor_key_in, pmt_type_in, status_in, time_in, viby_batchid_in,
'', 0, batchstate_in, batchtotal_in, saleamount_in,
cramount_in, gwid_in, vendor_code_in, vendor_message_in,
error_location_in, terminal_id_in, Acquirer_id_in,req_type_in, sysdate,
sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,1,sysdate);
UPDATE iby_batches_all
SET CURRENCYNAMECODE = l_batchcurr,
NUMTRXNS = l_trxncount
WHERE mbatchid = l_mbatchid;
UPDATE iby_batches_all
SET PAYMENTMETHODNAME = pmt_type_in,
BATCHSTATUS = status_in,
BATCHCLOSEDATE = time_in,
CURRENCYNAMECODE = NVL(currency_in,CURRENCYNAMECODE),
NUMTRXNS = NVL(l_trxncount,NUMTRXNS),
BATCHSTATEID = batchstate_in,
BATCHTOTAL = batchtotal_in,
BATCHSALES = saleamount_in,
BATCHCREDIT = cramount_in,
GWBATCHID = gwid_in,
BEPCODE = vendor_code_in,
BEPMESSAGE = vendor_message_in,
ERRORLOCATION = error_location_in,
Last_Update_Date = sysdate,
Last_Updated_by = fnd_global.user_id,
-- Do not update creation timestamp
-- when updating records: Bug 3128675
--Creation_Date = sysdate,
--Created_By = fnd_global.user_id,
Object_Version_Number = Object_Version_Number + 1
WHERE batchid = merch_batchid_in
AND payeeid = merchant_id_in;
-- we don't update the following for querybatch
UPDATE iby_batches_all
SET VPSBATCHID = viby_batchid_in,
reqtype = req_type_in,
reqdate = sysdate
WHERE batchid = merch_batchid_in
AND payeeid = merchant_id_in;
SELECT mbatchid
INTO mbatchid_out
FROM iby_batches_all
WHERE batchid = merch_batchid_in
AND payeeid = merchant_id_in;
END insert_batch_status;
| insert_batch_status_new
|
| PURPOSE:
|
|
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insert_batch_status_new
(
merch_batchid_in IN iby_batches_all.batchid%TYPE,
profile_code_in IN iby_batches_all.process_profile_code%TYPE,
merchant_id_in IN iby_batches_all.payeeid%TYPE,
vendor_id_in IN iby_batches_all.bepid%TYPE,
vendor_key_in IN iby_batches_all.bepkey%TYPE,
pmt_type_in IN iby_batches_all.paymentmethodname%TYPE,
status_in IN iby_batches_all.batchstatus%TYPE,
time_in IN iby_batches_all.batchclosedate%TYPE,
viby_batchid_in IN iby_batches_all.vpsbatchid%TYPE ,
currency_in IN iby_batches_all.currencynamecode%TYPE,
numtrxns_in IN iby_batches_all.NumTrxns%TYPE,
batchstate_in IN iby_batches_all.BatchStateid%TYPE,
batchtotal_in IN iby_batches_all.BatchTotal%TYPE,
saleamount_in IN iby_batches_all.BatchSales%TYPE,
cramount_in IN iby_batches_all.BatchCredit%TYPE,
gwid_in IN iby_batches_all.GWBatchID%TYPE,
vendor_code_in IN iby_batches_all.BEPcode%TYPE,
vendor_message_in IN iby_batches_all.BEPmessage%TYPE,
error_location_in IN iby_batches_all.errorlocation%TYPE,
terminal_id_in IN iby_batches_all.TerminalId%TYPE,
acquirer_id_in IN iby_batches_all.Acquirer%TYPE,
org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
req_type_in IN iby_batches_all.reqtype%TYPE,
sec_key_present_in IN VARCHAR2,
acct_profile_in IN iby_batches_all.process_profile_code%TYPE,
instr_type_in IN iby_batches_all.instrument_type%TYPE,
br_disputed_flag_in IN iby_batches_all.br_disputed_flag%TYPE,
f_pmt_channel_in IN iby_trxn_summaries_all.
payment_channel_code%TYPE,
f_curr_in IN iby_trxn_summaries_all.
currencynamecode%TYPE,
f_settle_date IN iby_trxn_summaries_all.
settledate%TYPE,
f_due_date IN iby_trxn_summaries_all.
settlement_due_date%TYPE,
f_maturity_date IN iby_trxn_summaries_all.
br_maturity_date%TYPE,
f_instr_type IN iby_trxn_summaries_all.
instrtype%TYPE,
mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
)
IS
numrows NUMBER;
'.insert_batch_status_new';
SELECT
mbatchid
FROM
IBY_BATCHES_ALL
WHERE
batchid = batch_id AND
payeeid = payee_id AND
process_profile_code = profile_cd
;
SELECT
COUNT(*)
INTO
numrows
FROM
IBY_BATCHES_ALL
WHERE
batchid = merch_batchid_in AND
payeeid = merchant_id_in
;
* If row does not exist, then insert batch status into iby_batches_all
*/
IF numrows = 0 THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'num rows is zero');
SELECT
NVL(bep_type,iby_bepinfo_pkg.C_BEPTYPE_GATEWAY)
INTO
l_beptype
FROM
IBY_BEPINFO
WHERE
(bepid=vendor_id_in)
;
* the SQL update statement (below). It is kept
* here for documentation purposes.
*/
/*------------------------------------------
UPDATE
IBY_TRXN_SUMMARIES_ALL
SET
status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING,
batchid = merch_batchid_in
|| '_' || i,
mbatchid = l_trxns_in_batch_tab(i).mbatch_id,
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE
(bepid = vendor_id_in) AND
(bepkey = vendor_key_in) AND
(payeeid = merchant_id_in) AND
(status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED)i
AND
(
(instrtype IN
(iby_creditcard_pkg.C_INSTRTYPE_CCARD,
iby_creditcard_pkg.C_INSTRTYPE_PCARD) AND
(req_type_in =
iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE))
OR
(instrtype IN (l_pinlessdebitcard) AND
(req_type_in = iby_transactioncc_pkg.
C_REQTYPE_PDC_BATCHCLOSE)
)
OR
instrtype IS NULL
)
AND
(batchid IS NULL);
print_debuginfo(l_module_name, 'Going to update '
|| 'transaction ' || l_trxns_in_batch_tab(i).trxn_id);
UPDATE
IBY_TRXN_SUMMARIES_ALL
SET
status = iby_transactioncc_pkg.
C_STATUS_BATCH_PENDING,
batchid = l_trxns_in_batch_tab(i).
batch_id,
mbatchid = l_trxns_in_batch_tab(i).
mbatch_id,
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE
transactionid = l_trxns_in_batch_tab(i).trxn_id AND
status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED
;
print_debuginfo(l_module_name, 'Going to insert batch for mbatchid'
|| l_batches_tab(i).mbatch_id);
INSERT INTO
iby_batches_all
(
MBATCHID,
BATCHID,
MPAYEEID,
PAYEEID,
BEPID,
BEPKEY,
PAYMENTMETHODNAME,
BATCHSTATUS,
BATCHCLOSEDATE,
VPSBATCHID,
CURRENCYNAMECODE,
NUMTRXNS,
BATCHSTATEID,
BATCHTOTAL,
BATCHSALES,
BATCHCREDIT,
GWBATCHID,
BEPCODE,
BEPMESSAGE,
ERRORLOCATION,
TERMINALID,
ACQUIRER,
REQTYPE,
REQDATE,
PROCESS_PROFILE_CODE,
INSTRUMENT_TYPE,
BR_DISPUTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
PAYEEINSTRID,
LEGAL_ENTITY_ID,
ORG_ID,
ORG_TYPE,
SETTLEDATE
)
VALUES
(
l_batches_tab(i).mbatch_id,
merch_batchid_in || '_' || i,
l_mpayeeid,
merchant_id_in,
vendor_id_in,
l_batches_tab(i).bep_key,
pmt_type_in,
status_in,
time_in,
viby_batchid_in,
l_batches_tab(i).curr_code,
0,
batchstate_in,
batchtotal_in,
saleamount_in,
cramount_in,
gwid_in,
vendor_code_in,
vendor_message_in,
error_location_in,
terminal_id_in,
Acquirer_id_in,
req_type_in,
sysdate,
l_batches_tab(i).profile_code,
instr_type_in,
br_disputed_flag_in,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1,
l_batches_tab(i).int_bank_acct_id,
l_batches_tab(i).le_id,
l_batches_tab(i).org_id,
l_batches_tab(i).org_type,
l_batches_tab(i).settle_date
);
print_debuginfo(l_module_name, 'finished insert '
|| 'for batch id '
|| l_batches_tab(i).mbatch_id
);
UPDATE
IBY_BATCHES_ALL
SET
currencynamecode = l_batchcurr,
numtrxns = l_trxncount
WHERE
mbatchid = l_batches_tab(i).mbatch_id
;
/* One previous transaction, so update previous row */
UPDATE
IBY_BATCHES_ALL
SET
PAYMENTMETHODNAME = pmt_type_in,
BATCHSTATUS = status_in,
BATCHCLOSEDATE = time_in,
CURRENCYNAMECODE = NVL(currency_in,CURRENCYNAMECODE),
NUMTRXNS = NVL(l_trxncount,NUMTRXNS),
BATCHSTATEID = batchstate_in,
BATCHTOTAL = batchtotal_in,
BATCHSALES = saleamount_in,
BATCHCREDIT = cramount_in,
GWBATCHID = gwid_in,
BEPCODE = vendor_code_in,
BEPMESSAGE = vendor_message_in,
ERRORLOCATION = error_location_in,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
-- Do not update creation timestamp
-- when updating records: Bug 3128675
--CREATION_DATE = sysdate,
--CREATED_BY = fnd_global.user_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE
batchid = merch_batchid_in AND
payeeid = merchant_id_in
;
UPDATE
iby_batches_all
SET
VPSBATCHID = viby_batchid_in,
reqtype = req_type_in,
reqdate = sysdate
WHERE
batchid = merch_batchid_in AND
payeeid = merchant_id_in;
END insert_batch_status_new;
| insert_batch_status_new
|
| PURPOSE:
| This is an Overloaded API of the previous one. This one
| takes an Array of Account FC profiles instead of a single on.
| This virtually means that we are accepting multiple bep keys in the API.
| THis will turn on the feature where we will have multiple divisions per
| Settlement Batch file.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insert_batch_status_new
(
merch_batchid_in IN iby_batches_all.batchid%TYPE,
profile_code_array IN JTF_VARCHAR2_TABLE_100,
merchant_id_in IN iby_batches_all.payeeid%TYPE,
vendor_id_in IN iby_batches_all.bepid%TYPE,
vendor_key_in IN iby_batches_all.bepkey%TYPE,
pmt_type_in IN iby_batches_all.paymentmethodname%TYPE,
status_in IN iby_batches_all.batchstatus%TYPE,
time_in IN iby_batches_all.batchclosedate%TYPE,
viby_batchid_in IN iby_batches_all.vpsbatchid%TYPE ,
currency_in IN iby_batches_all.currencynamecode%TYPE,
numtrxns_in IN iby_batches_all.NumTrxns%TYPE,
batchstate_in IN iby_batches_all.BatchStateid%TYPE,
batchtotal_in IN iby_batches_all.BatchTotal%TYPE,
saleamount_in IN iby_batches_all.BatchSales%TYPE,
cramount_in IN iby_batches_all.BatchCredit%TYPE,
gwid_in IN iby_batches_all.GWBatchID%TYPE,
vendor_code_in IN iby_batches_all.BEPcode%TYPE,
vendor_message_in IN iby_batches_all.BEPmessage%TYPE,
error_location_in IN iby_batches_all.errorlocation%TYPE,
terminal_id_in IN iby_batches_all.TerminalId%TYPE,
acquirer_id_in IN iby_batches_all.Acquirer%TYPE,
org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
req_type_in IN iby_batches_all.reqtype%TYPE,
sec_key_present_in IN VARCHAR2,
acct_profile_in IN iby_batches_all.process_profile_code%TYPE,
instr_type_in IN iby_batches_all.instrument_type%TYPE,
br_disputed_flag_in IN iby_batches_all.br_disputed_flag%TYPE,
f_pmt_channel_in IN iby_trxn_summaries_all.
payment_channel_code%TYPE,
f_curr_in IN iby_trxn_summaries_all.
currencynamecode%TYPE,
f_settle_date IN iby_trxn_summaries_all.
settledate%TYPE,
f_due_date IN iby_trxn_summaries_all.
settlement_due_date%TYPE,
f_maturity_date IN iby_trxn_summaries_all.
br_maturity_date%TYPE,
f_instr_type IN iby_trxn_summaries_all.
instrtype%TYPE,
mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
)
IS
numrows NUMBER;
'.insert_batch_status_new';
l_cursor_stmt := ' SELECT mbatchid FROM '||
' IBY_BATCHES_ALL WHERE '||
' batchid = '''||merch_batchid_in||''' AND '||
' payeeid = '''||merchant_id_in||''' AND '||
' process_profile_code IN ('||strProfCodes||') '
;
SELECT
COUNT(*)
INTO
numrows
FROM
IBY_BATCHES_ALL
WHERE
batchid = merch_batchid_in AND
payeeid = merchant_id_in
;
* If row does not exist, then insert batch status into iby_batches_all
*/
IF numrows = 0 THEN
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'num rows is zero');
SELECT
NVL(bep_type,iby_bepinfo_pkg.C_BEPTYPE_GATEWAY)
INTO
l_beptype
FROM
IBY_BEPINFO
WHERE
(bepid=vendor_id_in)
;
* the SQL update statement (below). It is kept
* here for documentation purposes.
*/
/*------------------------------------------
UPDATE
IBY_TRXN_SUMMARIES_ALL
SET
status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING,
batchid = merch_batchid_in
|| '_' || i,
mbatchid = l_trxns_in_batch_tab(i).mbatch_id,
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE
(bepid = vendor_id_in) AND
(bepkey = vendor_key_in) AND
(payeeid = merchant_id_in) AND
(status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED)i
AND
(
(instrtype IN
(iby_creditcard_pkg.C_INSTRTYPE_CCARD,
iby_creditcard_pkg.C_INSTRTYPE_PCARD) AND
(req_type_in =
iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE))
OR
(instrtype IN (l_pinlessdebitcard) AND
(req_type_in = iby_transactioncc_pkg.
C_REQTYPE_PDC_BATCHCLOSE)
)
OR
instrtype IS NULL
)
AND
(batchid IS NULL);
--print_debuginfo(l_module_name, 'Going to update '
--|| 'transaction ' || l_trxns_in_batch_tab(i).trxn_id);
/*UPDATE
IBY_TRXN_SUMMARIES_ALL
SET
status = iby_transactioncc_pkg.
C_STATUS_BATCH_PENDING,
batchid = l_trxns_in_batch_tab(i).
batch_id,
mbatchid = l_trxns_in_batch_tab(i).
mbatch_id,
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE
transactionid = l_trxns_in_batch_tab(i).trxn_id AND
status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED
;*/
print_debuginfo(l_module_name, 'Going to insert batch for mbatchid'
|| l_batches_tab(i).mbatch_id);
print_debuginfo(l_module_name, 'Going to insert batch for batchid'
|| l_batches_tab(i).batch_id);
INSERT INTO
iby_batches_all
(
MBATCHID,
BATCHID,
MPAYEEID,
PAYEEID,
BEPID,
BEPKEY,
PAYMENTMETHODNAME,
BATCHSTATUS,
BATCHCLOSEDATE,
VPSBATCHID,
CURRENCYNAMECODE,
NUMTRXNS,
BATCHSTATEID,
BATCHTOTAL,
BATCHSALES,
BATCHCREDIT,
GWBATCHID,
BEPCODE,
BEPMESSAGE,
ERRORLOCATION,
TERMINALID,
ACQUIRER,
REQTYPE,
REQDATE,
PROCESS_PROFILE_CODE,
INSTRUMENT_TYPE,
BR_DISPUTED_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
PAYEEINSTRID,
LEGAL_ENTITY_ID,
ORG_ID,
ORG_TYPE,
SETTLEDATE
)
VALUES
(
l_batches_tab(i).mbatch_id,
merch_batchid_in || '_' || i,
l_mpayeeid,
merchant_id_in,
vendor_id_in,
l_batches_tab(i).bep_key,-- should be made NULL
pmt_type_in,
status_in,
time_in,
viby_batchid_in,
l_batches_tab(i).curr_code,-- should be made NULL
0,
batchstate_in,
batchtotal_in,
saleamount_in,
cramount_in,
gwid_in,
vendor_code_in,
vendor_message_in,
error_location_in,
terminal_id_in,
Acquirer_id_in,
req_type_in,
sysdate,
-- l_batches_tab(i).profile_code,-- should be made NULL
profile_code_array(1),
instr_type_in,
br_disputed_flag_in,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1,
l_batches_tab(i).int_bank_acct_id,
l_batches_tab(i).le_id,
l_batches_tab(i).org_id,
l_batches_tab(i).org_type,
l_batches_tab(i).settle_date
);
print_debuginfo(l_module_name, 'finished insert '
|| 'for mbatch id '
|| l_batches_tab(i).mbatch_id
);
UPDATE
IBY_BATCHES_ALL
SET
currencynamecode = l_batchcurr,-- should be made NULL
numtrxns = l_trxncount
WHERE
mbatchid = l_batches_tab(i).mbatch_id
;
/* One previous transaction, so update previous row */
UPDATE
IBY_BATCHES_ALL
SET
PAYMENTMETHODNAME = pmt_type_in,
BATCHSTATUS = status_in,
BATCHCLOSEDATE = time_in,
CURRENCYNAMECODE = NVL(currency_in,CURRENCYNAMECODE),-- should be made NULL
NUMTRXNS = NVL(l_trxncount,NUMTRXNS),
BATCHSTATEID = batchstate_in,
BATCHTOTAL = batchtotal_in,
BATCHSALES = saleamount_in,
BATCHCREDIT = cramount_in,
GWBATCHID = gwid_in,
BEPCODE = vendor_code_in,
BEPMESSAGE = vendor_message_in,
ERRORLOCATION = error_location_in,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
-- Do not update creation timestamp
-- when updating records: Bug 3128675
--CREATION_DATE = sysdate,
--CREATED_BY = fnd_global.user_id,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE
batchid = merch_batchid_in AND
payeeid = merchant_id_in
;
UPDATE
iby_batches_all
SET
VPSBATCHID = viby_batchid_in,
reqtype = req_type_in,
reqdate = sysdate
WHERE
batchid = merch_batchid_in AND
payeeid = merchant_id_in;
END insert_batch_status_new;
* PLSQL table is used in inserting a row into the IBY_BATCHES_ALL
* table.
*
* A separate data structure is needed to keep track of the transactions
* that are part of a batch. This information is tracked in the
* trxnsInBatchTabType table. The rows in trxnsInBatchTabType are
* used to update the rows in IBY_TRXN_SUMMARIES_ALL table with
* batch ids.
*
* l_batchTab l_trxnsInBatchTab
* (insert into IBY_BATCHES_ALL) (update IBY_TRXN_SUMMARIES_ALL)
* /-------------------------------------\ /------------\
* |MBatch |Profile|..|Curr |Org |..| |MBatch |Trx |
* |Id |Code |..|Code |Id |..| |Id |Id |
* | | |..| | |..| | | |
* |-------------------------------------| |------------|
* | 4000| 10| | USD| 204| | | 4000| 501|
* | | | | | | | | 4000| 504|
* | | | | | | | | 4000| 505|
* |-------|-------|--|-------|-------|--| |-------|----|
* | 4001| 11| | -- | 342| | | 4001| 502|
* | | | | | | | | 4001| 509|
* | | | | | | | | 4001| 511|
* | | | | | | | | 4001| 523|
* | | | | | | | | : | : |
* |-------|-------|--|-------|-------|--| |-------|----|
* | : | : | | : | : | | | : | : |
* \_______|_______|__|_______|_______|__/ \_______|____/
*
*/
l_batchRec IBY_TRANSACTIONCC_PKG.batchAttrRecType;
* the specified payment profile. The select statement will
* order the transactions based on grouping criteria.
*
* Important Note:
*
* Always ensure that there is a corresponding order by
* clause for each grouping criterion that you wish to use.
* This is required in order to create minimum possible
* batches from a given set of transactions.
*
* Note 2: The sample sql is not right as the base table for
* process profile is different
* the dynamic sql is changed according to that
*/
CURSOR c_transactions (
p_profile_code VARCHAR2,
p_instr_type VARCHAR2,
p_req_type VARCHAR2
)
IS
SELECT
txn.transactionid,
txn.process_profile_code,
txn.bepkey,
txn.org_id,
txn.org_type,
txn.currencynamecode,
txn.amount,
txn.legal_entity_id,
txn.payeeinstrid,
txn.settledate,
sys_prof.group_by_org,
sys_prof.group_by_legal_entity,
sys_prof.group_by_int_bank_account,
sys_prof.group_by_settlement_curr,
sys_prof.group_by_settlement_date,
sys_prof.limit_by_amt_curr,
sys_prof.limit_by_exch_rate_type,
sys_prof.limit_by_total_amt,
sys_prof.limit_by_settlement_num
FROM
IBY_TRXN_SUMMARIES_ALL txn,
IBY_FNDCPT_USER_CC_PF_B user_prof,
IBY_FNDCPT_SYS_CC_PF_B sys_prof
WHERE
user_prof.user_cc_profile_code = p_profile_code AND
txn.process_profile_code = user_prof.user_cc_profile_code AND
sys_prof.sys_cc_profile_code = user_prof.sys_cc_profile_code AND
txn.status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED AND
(
/*
* This clause will pick up credit card / purchase card
* transactions.
*/
(
p_instr_type IN
(
iby_creditcard_pkg.C_INSTRTYPE_CCARD,
iby_creditcard_pkg.C_INSTRTYPE_PCARD
)
AND
(
txn.reqtype IN
(
iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE,
iby_transactioncc_pkg.C_REQTYPE_CAPTURE,
iby_transactioncc_pkg.C_REQTYPE_CREDIT,
iby_transactioncc_pkg.C_REQTYPE_RETURN
)
)
AND
(
txn.instrtype IN
(
iby_creditcard_pkg.C_INSTRTYPE_CCARD,
iby_creditcard_pkg.C_INSTRTYPE_PCARD
)
)
)
/*
* This clause will pick up pinless debit card
* transactions.
*/
OR
(
p_instr_type IN
(
l_pinlessdebitcard
)
AND
(
txn.reqtype IN
(
iby_transactioncc_pkg.C_REQTYPE_PDC_BATCHCLOSE,
iby_transactioncc_pkg.C_REQTYPE_REQUEST
)
)
AND
(
txn.instrtype IN
(
l_pinlessdebitcard
)
)
)
/*
* This clause will pick up bank account transactions
* transactions.
*/
OR
(
p_instr_type IN
(
l_bankaccount
)
AND
(
txn.reqtype IN
(
iby_transactioncc_pkg.C_REQTYPE_EFT_BATCHCLOSE,
iby_transactioncc_pkg.C_REQTYPE_BATCHREQ
)
)
AND
(
txn.instrtype IN
(
l_bankaccount
)
)
/*
* Fix for bug 5442922:
*
* For bank account instruments, the auth / verify
* transaction will have trantypeid 20; The
(SELECT
ce.organization_id,
ce.organization_type
FROM
ce_security_profiles_v ce
)))
ORDER BY
txn.process_profile_code, --
txn.bepkey, -- Ensure that the
txn.org_id, -- grouping rules below
txn.org_type, -- follow this same
txn.legal_entity_id, -- order (necessary
txn.payeeinstrid, -- for creating minimum
txn.currencynamecode, -- number of batches)
txn.settledate --
;
SELECT
value
INTO
l_numeric_char_mask
FROM
V$NLS_PARAMETERS
WHERE
parameter='NLS_NUMERIC_CHARACTERS'
;
'SELECT '
|| 'txn.transactionid, '
|| 'txn.process_profile_code, '
|| 'txn.bepkey, '
|| 'txn.org_id, '
|| 'txn.org_type, '
|| 'txn.currencynamecode, '
|| 'txn.amount, '
|| 'txn.legal_entity_id, '
|| 'txn.payeeinstrid, '
|| 'txn.settledate, '
|| 'sys_prof.group_by_org, '
|| 'sys_prof.group_by_legal_entity, '
|| 'sys_prof.group_by_int_bank_account, '
|| 'sys_prof.group_by_settlement_curr, '
|| 'sys_prof.group_by_settlement_date, '
|| 'sys_prof.limit_by_amt_curr, '
|| 'sys_prof.limit_by_exch_rate_type, '
|| 'sys_prof.limit_by_total_amt, '
|| 'sys_prof.limit_by_settlement_num '
|| 'FROM '
|| 'IBY_TRXN_SUMMARIES_ALL txn, '
|| l_user_pf_table_name || ' user_prof, '
|| l_sys_pf_table_name || ' sys_prof '
|| 'WHERE '
|| 'user_prof.'||l_user_pf_column_name||' = :profile_code AND '
|| 'txn.process_profile_code = user_prof.'||l_user_pf_column_name||' AND '
|| 'sys_prof.' ||l_sys_pf_column_name||' = user_prof. '||l_sys_pf_column_name || ' AND '
|| 'txn.status = :open_batch AND '
|| '( '
/*
* This clause will pick up credit card / purchase card
* transactions.
*/
|| '( '
|| ':A IN (:C1, :C2) AND (txn.reqtype IN (:T1A, :T1B, :T1C, :T1D)) AND '
|| '(txn.instrtype IN (:C3, :C4)) '
|| ') '
/*
* This clause will pick up pinless debit card
* transactions.
*/
|| 'OR '
|| '( '
|| ':C IN (:P1) AND (txn.reqtype IN (:T2A, :T2B)) AND '
|| '(txn.instrtype IN (:P2)) '
|| ') '
/*
* This clause will pick up bank account transactions
* transactions.
*/
|| 'OR '
|| '( '
|| ':E IN (:B1) AND (txn.reqtype IN (:T3A, :T3B, :T3C)) AND '
|| '(txn.instrtype IN (:B2)) '
/*
* Fix for bug 5442922:
*
* For bank account instruments, the auth / verify
* transaction will have trantypeid 20; The
|| ' (SELECT '
|| ' ce.organization_id, '
|| ' ce.organization_type '
|| ' FROM '
|| ' ce_security_profiles_v ce '
|| ' ))) '
|| NVL (l_sql_str, 'AND 1=1 ')
|| 'ORDER BY '
|| 'txn.process_profile_code, ' --
|| 'txn.bepkey, ' -- Ensure that the
|| 'txn.org_id, ' -- grouping rules below
|| 'txn.org_type, ' -- follow this same
|| 'txn.legal_entity_id, ' -- order (necessary
|| 'txn.payeeinstrid, ' -- for creating minimum
|| 'txn.currencynamecode, ' -- number of batches)
|| 'txn.settledate ' --
;
* We will either insert this transaction into a new batch or
* we will be inserting this transaction into the currently running
* batch.
*
* In either case, we need to insert this trxn into a batch.
* So pre-populate the batch record with attributes of
* this document. This is because the batch takes on the
* attributes of its constituent transactions.
*
* Note: For user defined grouping rules, we will
* have to populate the batch attributes only if
* the user has turned on grouping by that attribute.
*/
/* Only pre-fill hardcoded grouping rule attributes */
l_batchRec.profile_code := curr_profile_cd;
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
false, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
| by the corresponding overloaded procedure insert_batch_ststus_new.
| This one also takes an array of User profile codes instead of one.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE performTransactionGrouping(
profile_code_array IN JTF_VARCHAR2_TABLE_100,
instr_type IN IBY_TRXN_SUMMARIES_ALL.
instrtype%TYPE,
req_type IN IBY_BATCHES_ALL.
reqtype%TYPE,
f_pmt_channel_in IN IBY_TRXN_SUMMARIES_ALL.
payment_channel_code%TYPE,
f_curr_in IN IBY_TRXN_SUMMARIES_ALL.
currencynamecode%TYPE,
f_settle_date IN IBY_TRXN_SUMMARIES_ALL.
settledate%TYPE,
f_due_date IN IBY_TRXN_SUMMARIES_ALL.
settlement_due_date%TYPE,
f_maturity_date IN IBY_TRXN_SUMMARIES_ALL.
br_maturity_date%TYPE,
f_instr_type IN IBY_TRXN_SUMMARIES_ALL.
instrtype%TYPE,
merch_batchid_in IN iby_batches_all.batchid%TYPE,
x_batchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
batchAttrTabType,
x_trxnsInBatchTab IN OUT NOCOPY IBY_TRANSACTIONCC_PKG.
trxnsInBatchTabType
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.performTransactionGrouping';
* PLSQL table is used in inserting a row into the IBY_BATCHES_ALL
* table.
*
* A separate data structure is needed to keep track of the transactions
* that are part of a batch. This information is tracked in the
* trxnsInBatchTabType table. The rows in trxnsInBatchTabType are
* used to update the rows in IBY_TRXN_SUMMARIES_ALL table with
* batch ids.
*
* l_batchTab l_trxnsInBatchTab
* (insert into IBY_BATCHES_ALL) (update IBY_TRXN_SUMMARIES_ALL)
* /-------------------------------------\ /------------\
* |MBatch |Profile|..|Curr |Org |..| |MBatch |Trx |
* |Id |Code |..|Code |Id |..| |Id |Id |
* | | |..| | |..| | | |
* |-------------------------------------| |------------|
* | 4000| 10| | USD| 204| | | 4000| 501|
* | | | | | | | | 4000| 504|
* | | | | | | | | 4000| 505|
* |-------|-------|--|-------|-------|--| |-------|----|
* | 4001| 11| | -- | 342| | | 4001| 502|
* | | | | | | | | 4001| 509|
* | | | | | | | | 4001| 511|
* | | | | | | | | 4001| 523|
* | | | | | | | | : | : |
* |-------|-------|--|-------|-------|--| |-------|----|
* | : | : | | : | : | | | : | : |
* \_______|_______|__|_______|_______|__/ \_______|____/
*
*/
l_batchRec IBY_TRANSACTIONCC_PKG.batchAttrRecType;
* the specified payment profile. The select statement will
* order the transactions based on grouping criteria.
*
* Important Note:
*
* Always ensure that there is a corresponding order by
* clause for each grouping criterion that you wish to use.
* This is required in order to create minimum possible
* batches from a given set of transactions.
*
* Note 2: The sample sql is not right as the base table for
* process profile is different
* the dynamic sql is changed according to that
*/
CURSOR c_transactions (
strProfiles VARCHAR2,
p_instr_type VARCHAR2,
p_req_type VARCHAR2
)
IS
SELECT
txn.transactionid,
txn.process_profile_code,
txn.bepkey,
txn.org_id,
txn.org_type,
txn.currencynamecode,
txn.amount,
txn.legal_entity_id,
txn.payeeinstrid,
--The grouping logic is modified, Now it will be grouped by settlement_due_date instead of settledate Bug# 13799238
txn.settlement_due_date,
sys_prof.group_by_org,
sys_prof.group_by_legal_entity,
sys_prof.group_by_int_bank_account,
sys_prof.group_by_settlement_curr,
sys_prof.group_by_settlement_date,
sys_prof.limit_by_amt_curr,
sys_prof.limit_by_exch_rate_type,
sys_prof.limit_by_total_amt,
sys_prof.limit_by_settlement_num
FROM
IBY_TRXN_SUMMARIES_ALL txn,
IBY_FNDCPT_USER_CC_PF_B user_prof,
IBY_FNDCPT_SYS_CC_PF_B sys_prof
WHERE
user_prof.user_cc_profile_code IN (strProfiles) AND
txn.process_profile_code = user_prof.user_cc_profile_code AND
sys_prof.sys_cc_profile_code = user_prof.sys_cc_profile_code AND
txn.status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED AND
(
/*
* This clause will pick up credit card / purchase card
* transactions.
*/
(
p_instr_type IN
(
iby_creditcard_pkg.C_INSTRTYPE_CCARD,
iby_creditcard_pkg.C_INSTRTYPE_PCARD
)
AND
(
txn.reqtype IN
(
iby_transactioncc_pkg.C_REQTYPE_BATCHCLOSE,
iby_transactioncc_pkg.C_REQTYPE_CAPTURE,
iby_transactioncc_pkg.C_REQTYPE_CREDIT,
iby_transactioncc_pkg.C_REQTYPE_RETURN
)
)
AND
(
txn.instrtype IN
(
iby_creditcard_pkg.C_INSTRTYPE_CCARD,
iby_creditcard_pkg.C_INSTRTYPE_PCARD
)
)
)
/*
* This clause will pick up pinless debit card
* transactions.
*/
OR
(
p_instr_type IN
(
l_pinlessdebitcard
)
AND
(
txn.reqtype IN
(
iby_transactioncc_pkg.C_REQTYPE_PDC_BATCHCLOSE,
iby_transactioncc_pkg.C_REQTYPE_REQUEST
)
)
AND
(
txn.instrtype IN
(
l_pinlessdebitcard
)
)
)
/*
* This clause will pick up bank account transactions
* transactions.
*/
OR
(
p_instr_type IN
(
l_bankaccount
)
AND
(
txn.reqtype IN
(
iby_transactioncc_pkg.C_REQTYPE_EFT_BATCHCLOSE,
iby_transactioncc_pkg.C_REQTYPE_BATCHREQ
)
)
AND
(
txn.instrtype IN
(
l_bankaccount
)
)
/*
* Fix for bug 5442922:
*
* For bank account instruments, the auth / verify
* transaction will have trantypeid 20; The
(SELECT
ce.organization_id,
ce.organization_type
FROM
ce_security_profiles_v ce
)))
ORDER BY
txn.process_profile_code, --
txn.bepkey, -- Ensure that the
txn.org_id, -- grouping rules below
txn.org_type, -- follow this same
txn.legal_entity_id, -- order (necessary
txn.payeeinstrid, -- for creating minimum
txn.currencynamecode, -- number of batches)
txn.settlement_due_date --
--The grouping logic is modified, Now it will be grouped by settlement_due_date instead of settledate Bug# 13799238
;
SELECT
value
INTO
l_numeric_char_mask
FROM
V$NLS_PARAMETERS
WHERE
parameter='NLS_NUMERIC_CHARACTERS'
;
'SELECT '
|| 'txn.transactionid, '
|| 'txn.process_profile_code, '
|| 'txn.bepkey, '
|| 'txn.org_id, '
|| 'txn.org_type, '
|| 'txn.currencynamecode, '
|| 'txn.amount, '
|| 'txn.legal_entity_id, '
|| 'txn.payeeinstrid, '
----The grouping logic is modified, Now it will be grouped by settlement_due_date instead of settledate Bug# 13799238
|| 'txn.settlement_due_date, '
|| 'sys_prof.group_by_org, '
|| 'sys_prof.group_by_legal_entity, '
|| 'sys_prof.group_by_int_bank_account, '
|| 'sys_prof.group_by_settlement_curr, '
|| 'sys_prof.group_by_settlement_date, '
|| 'sys_prof.limit_by_amt_curr, '
|| 'sys_prof.limit_by_exch_rate_type, '
|| 'sys_prof.limit_by_total_amt, '
|| 'sys_prof.limit_by_settlement_num '
|| 'FROM '
|| 'IBY_TRXN_SUMMARIES_ALL txn, '
|| l_user_pf_table_name || ' user_prof, '
|| l_sys_pf_table_name || ' sys_prof '
|| 'WHERE '
|| 'user_prof.'||l_user_pf_column_name||' IN ('||strProfCodes||') AND '
|| 'txn.process_profile_code = user_prof.'||l_user_pf_column_name||' AND '
|| 'sys_prof.' ||l_sys_pf_column_name||' = user_prof. '||l_sys_pf_column_name || ' AND '
|| 'txn.status = :open_batch AND '
|| '( '
/*
* This clause will pick up credit card / purchase card
* transactions.
*/
|| '( '
|| ':A IN (:C1, :C2) AND (txn.reqtype IN (:T1A, :T1B, :T1C, :T1D, :T1E)) AND '
|| '(txn.instrtype IN (:C3, :C4)) '
|| ') '
/*
* This clause will pick up pinless debit card
* transactions.
*/
|| 'OR '
|| '( '
|| ':C IN (:P1) AND (txn.reqtype IN (:T2A, :T2B)) AND '
|| '(txn.instrtype IN (:P2)) '
|| ') '
/*
* This clause will pick up bank account transactions
* transactions.
*/
|| 'OR '
|| '( '
|| ':E IN (:B1) AND (txn.reqtype IN (:T3A, :T3B, :T3C)) AND '
|| '(txn.instrtype IN (:B2)) '
/*
* Fix for bug 5442922:
*
* For bank account instruments, the auth / verify
* transaction will have trantypeid 20; The
|| ' (SELECT '
|| ' ce.organization_id, '
|| ' ce.organization_type '
|| ' FROM '
|| ' ce_security_profiles_v ce '
|| ' ))) '
|| NVL (l_sql_str, 'AND 1=1 ')
|| 'ORDER BY '
|| 'txn.process_profile_code, ' --
|| 'txn.bepkey, ' -- Ensure that the
|| 'txn.org_id, ' -- grouping rules below
|| 'txn.org_type, ' -- follow this same
|| 'txn.legal_entity_id, ' -- order (necessary
|| 'txn.payeeinstrid, ' -- for creating minimum
|| 'txn.currencynamecode, ' -- number of batches)
|| 'txn.settlement_due_date ' --
----The grouping logic is modified, Now it will be grouped by settlement_due_date instead of settledate Bug# 13799238
;
* We will either insert this transaction into a new batch or
* we will be inserting this transaction into the currently running
* batch.
*
* In either case, we need to insert this trxn into a batch.
* So pre-populate the batch record with attributes of
* this document. This is because the batch takes on the
* attributes of its constituent transactions.
*
* Note: For user defined grouping rules, we will
* have to populate the batch attributes only if
* the user has turned on grouping by that attribute.
*/
/* Only pre-fill hardcoded grouping rule attributes */
/* Commenting out this one as, we no more have profile code and currency
as hard coded grouping rules
*/
--l_batchRec.profile_code := curr_profile_cd;
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
true, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
insertTrxnIntoBatch(l_batchRec, l_batchTab,
false, l_mbatch_id, l_trxnsInBatchTab,
l_trxnsInBatchRec, l_trxns_in_batch_count);
-- Bulk Update
FORALL i in trxnTab.transactionid.FIRST .. trxnTab.transactionid.LAST
/* Bug 9958732 : Used INDEX hint to force the index */
UPDATE /*+ INDEX(IBY_TRXN_SUMMARIES_ALL IBY_TRXN_SUMM_TRANS_ID_N1) */
IBY_TRXN_SUMMARIES_ALL
SET
status = iby_transactioncc_pkg.
C_STATUS_BATCH_PENDING,
batchid = trxnTab.batchid(i),
mbatchid = trxnTab.mbatchid(i),
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE
transactionid = trxnTab.transactionid(i) AND
status = iby_transactioncc_pkg.C_STATUS_OPEN_BATCHED
;
delete_trxnTable;
| insertTrxnIntoBatch
|
| PURPOSE:
| Inserts a given transaction into a currently running batch
| or into a new batch as per given flag.
|
| This method is called by every grouping rule to add
| a given transaction into a current batch/new batch.
|
| PARAMETERS:
| IN
|
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE insertTrxnIntoBatch(
x_batchRec IN OUT NOCOPY batchAttrRecType,
x_batchTab IN OUT NOCOPY batchAttrTabType,
p_newBatchFlag IN BOOLEAN,
x_currentBatchId IN OUT NOCOPY IBY_BATCHES_ALL.batchid%TYPE,
x_trxnsInBatchTab IN OUT NOCOPY trxnsInBatchTabType,
x_trxnsInBatchRec IN OUT NOCOPY trxnsInBatchRecType,
x_trxnsInBatchCount IN OUT NOCOPY NUMBER
)
IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.insertTrxnIntoBatch';
* table is updated with the details of this transaction
* within this if-else block.
*
* We need to do this each time we enter this procedure
* because this might well be the last transaction in
* in the provided profile, and this procedure may
* not be called again for this profile. So
* the PLSQL batches table should always be up-to-date
* when it exits this procedure.
*/
IF (p_newBatchFlag = true) THEN
/*
* This is a new batch; Get an id for this batch
* transaction as a constituent, and insert this record
* into the PLSQL batches table.
*/
x_batchRec.mbatch_id := x_currentBatchId;
print_debuginfo(l_module_name,' Inserted transaction : '
|| x_trxnsInBatchRec.trxn_id || ' into new batch: '
|| x_currentBatchId);
* trxn, and insert the trxn into the trxns array.
*/
x_trxnsInBatchRec.mbatch_id := x_batchRec.mbatch_id;
* initialize it by inserting a dummy record. This dummy
* record will get overwritten below.
*/
IF (x_batchTab.COUNT = 0) THEN
getNextBatchId(x_currentBatchId);
* Insert the first record into the table. This
* is a dummy record.
*/
x_batchTab(x_batchTab.COUNT + 1) := x_batchRec;
* PLSQL batches table with the updated record.
*/
x_batchRec.trxn_count := x_trxnsInBatchCount;
print_debuginfo(l_module_name, 'Inserted transaction: '
|| x_trxnsInBatchRec.trxn_id || ' into existing batch: '
|| x_currentBatchId);
* transaction, and insert the trxn into the trxns array.
*/
x_trxnsInBatchRec.mbatch_id := x_batchRec.mbatch_id;
END insertTrxnIntoBatch;
SELECT IBY_BATCHID_S.NEXTVAL INTO x_batchID
FROM DUAL;
l_needupdate boolean;
SELECT count(*)
FROM iby_transactions_v
WHERE order_Id = order_id_in
AND merchant_id = merchant_id_in
AND vendor_id = vendor_id_in
AND status = status_in
AND trxn_type = tx1;
l_needupdate := true;
l_needupdate := true;
l_needupdate := true;
l_needupdate := true;
l_needupdate := false;
/* IF (l_numrows > 0 AND l_needupdate) THEN
UPDATE iby_trxn_summaries_all
SET trxntypeid = trxn_type_in
WHERE tangibleid = order_id_in
AND payeeid = merchant_id_in
AND bepid = vendor_id_in
AND status = status_in
AND trxntypeid IN (l_tx1, l_tx2);
/* Inserts the transaction record for the closebatch operation */
PROCEDURE insert_batch_txn
(ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
order_id_in IN iby_transactions_v.order_id%TYPE,
merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
merch_batchid_in IN iby_transactions_v.MerchBatchID%TYPE,
vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
vendor_key_in IN iby_transactions_v.bepkey%TYPE,
status_in IN iby_transactions_v.status%TYPE,
time_in IN iby_transactions_v.time%TYPE,
trxn_type_in IN iby_transactions_v.trxn_type%TYPE,
vendor_code_in IN iby_transactions_v.vendor_code%TYPE,
vendor_message_in IN iby_transactions_v.vendor_message%TYPE,
error_location_in IN iby_transactions_v.error_location%TYPE,
trace_number_in IN iby_transactions_v.TraceNumber%TYPE,
org_id_in IN iby_trxn_summaries_all.org_id%type,
transaction_id_out OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE)
IS
num_rows NUMBER;
UPDATE iby_trxn_summaries_all
SET BatchID = merch_batchid_in,
MBatchID = l_mbatchid,
last_update_date=sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
creation_date = sysdate,
created_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE TangibleID = order_id_in
AND PayeeID = merchant_id_in
AND TrxntypeID IN (2, 3, 8,9)
AND Status = 0;
UPDATE iby_trxn_summaries_all
SET BatchID = merch_batchid_in,
MBatchID = l_mbatchid,
last_update_date=sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
creation_date = sysdate,
created_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE TangibleID = order_id_in
AND PayeeID = merchant_id_in
AND TrxntypeID IN (5, 10)
AND Status = 0;
SELECT count(*)
INTO l_prev_trxn_count
FROM iby_trxn_summaries_all
WHERE tangibleid = order_id_in AND
payeeid = merchant_id_in;
SELECT DISTINCT transactionid, mtangibleid, instrtype, instrsubtype
INTO transaction_id_out, l_mtangibleid, l_instrtype, l_instrsubtype
FROM iby_trxn_summaries_all
WHERE tangibleid = order_id_in AND
payeeid = merchant_id_in
AND status = 0;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO l_trxn_mid
FROM dual;
-- this insertion from close/query batch is problematic
-- 1) there is no corresponding entry in ibY_trxn_core table
-- 2) mtangible id might not be correct, there might not be
-- an entry in iby_tangible table
-- 3) currency, amount is missing
-- regardless, this should be a rare case, it exists because
-- 1) Cybercash doesn't return 'trxntypeid' for capture/return
-- accurately
-- 2) during testing, there are trxns submitted directly to bep
-- (not through iPayment)
-- 3) during testing, requests submitted to Cybercash regular
-- and Cybercash SSL are considered different, but during close
-- batch, they are mixed.
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID,TangibleID,MPayeeID, PayeeID,BEPID, bepKey,
ECAppID,org_id, Status, UpdateDate,TrxnTypeID, MBatchID, BatchID,
BEPCode,BEPMessage,Errorlocation,
ReqType, ReqDate, mtangibleid,
last_update_date,last_updated_by,creation_date,created_by,
last_update_login,object_version_number,instrType,instrsubtype,needsupdt)
VALUES (l_trxn_mid, transaction_id_out,
order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
vendor_key_in, ecapp_id_in, org_id_in, status_in, time_in,
trxn_type_in, l_mbatchid, merch_batchid_in,
vendor_code_in, vendor_message_in, error_location_in,
l_reqtype, sysdate, l_mtangibleid,
sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
fnd_global.login_id,1,l_instrtype,l_instrsubtype,'Y');
SELECT distinct(transactionid)
INTO transaction_id_out
FROM iby_trxn_summaries_all
WHERE tangibleid = order_id_in
AND payeeid = merchant_id_in
AND bepid = vendor_id_in
AND trxntypeid = trxn_type_in
AND status = status_in;
END insert_batch_txn;
/* Inserts transaction record for transaction query operation */
PROCEDURE insert_query_txn
(transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE,
order_id_in IN iby_transactions_v.order_id%TYPE,
merchant_id_in IN iby_transactions_v.merchant_id%TYPE,
vendor_id_in IN iby_transactions_v.vendor_id%TYPE,
vendor_key_in IN iby_transactions_v.bepkey%TYPE,
status_in IN iby_transactions_v.status%TYPE,
time_in IN iby_transactions_v.time%TYPE DEFAULT sysdate,
trxn_type_in IN iby_transactions_v.trxn_type%TYPE,
amount_in IN iby_transactions_v.amount%TYPE DEFAULT NULL,
currency_in IN iby_transactions_v.currency%TYPE DEFAULT NULL,
payment_name_in IN iby_transactions_v.payment_name%TYPE DEFAULT NULL,
authcode_in IN iby_transactions_v.authcode%TYPE DEFAULT NULL,
referencecode_in IN iby_transactions_v.referencecode%TYPE DEFAULT NULL,
avscode_in IN iby_transactions_v.AVScode%TYPE DEFAULT NULL,
acquirer_in IN iby_transactions_v.acquirer%TYPE DEFAULT NULL,
auxmsg_in IN iby_transactions_v.Auxmsg%TYPE DEFAULT NULL,
vendor_code_in IN iby_transactions_v.vendor_code%TYPE DEFAULT NULL,
vendor_message_in IN iby_transactions_v.vendor_message%TYPE DEFAULT NULL,
error_location_in IN iby_transactions_v.error_location%TYPE DEFAULT NULL,
trace_number_in IN iby_transactions_v.TraceNumber%TYPE DEFAULT NULL,
org_id_in IN iby_trxn_summaries_all.org_id%type DEFAULT NULL,
ecappid_in IN iby_ecapp.ecappid%type,
req_type_in IN iby_trxn_summaries_all.reqtype%type)
IS
num_rows NUMBER;
SELECT trxnmid
FROM iby_trxn_summaries_all
WHERE (status = ci_status)
AND (payeeid = ci_merchid)
AND (transactionid = ci_trxnid)
AND (reqtype = ci_reqtype);
SELECT mtangibleid, instrtype, instrsubtype, trxnref
FROM iby_trxn_summaries_all
WHERE (tangibleid = ci_orderid)
AND (payeeid = ci_merchid)
AND (mtangibleid <> -1)
ORDER BY reqdate DESC;
UPDATE iby_trxn_summaries_all
SET ReqDate = NVL(time_in,reqdate),
--Amount = amount_in,
--CurrencyNameCode = currency_in,
UpdateDate = NVL(time_in,updatedate),
Status = status_in,
BEPCode = vendor_code_in,
BEPMessage = vendor_message_in,
Errorlocation = error_location_in,
instrType = NVL(l_instrtype,instrType),
instrsubtype = NVL(l_instrsubtype,instrsubtype),
--org_id = org_id_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = object_version_number+1
WHERE (trxnmid = l_trxn_mid);
UPDATE iby_trxn_core
SET AuthCode = authcode_in,
ReferenceCode = referencecode_in,
AVSCode = avscode_in,
Acquirer = acquirer_in,
Auxmsg = auxmsg_in,
TraceNumber = trace_number_in,
InstrName = NVL(payment_name_in,instrname),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = object_version_number+1
WHERE (trxnmid = l_trxn_mid);
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO l_trxn_mid
FROM dual;
select count(*) into l_cnt from iby_trxn_summaries_all
where tangibleid = order_id_in and
payeeid = merchant_id_in and
mtangibleid <> -1;
-- see insert_batch_txn, we want to ignore trxns done directly
-- on BEP, w/o going through iPayment
-- and other unknown cases
if (l_cnt > 0) then
OPEN c_order_info(order_id_in,merchant_id_in);
INSERT INTO iby_trxn_summaries_all
(ECAppID, TrxnMID, TransactionID,TrxntypeID, ReqDate, ReqType,
Amount,CurrencyNameCode, UpdateDate,Status,
TangibleID,MPayeeID, PayeeID,BEPID, bepKey, MTangibleID,
BEPCode,BEPMessage,Errorlocation, org_id,
last_update_date,last_updated_by,creation_date,created_by,
last_update_login,object_version_number,instrType,instrsubtype,trxnref,needsupdt)
VALUES (ecappid_in, l_trxn_mid, transaction_id_in, trxn_type_in,
time_in, req_type_in,
amount_in, currency_in, time_in, status_in,
order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
vendor_key_in, l_mtangibleid,
vendor_code_in, vendor_message_in, error_location_in, org_id_in,
sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,
fnd_global.login_id,1,l_instrtype,l_instrsubtype,l_trxnref,'Y');
INSERT INTO iby_trxn_core
(TrxnMID, AuthCode, ReferenceCode, AVSCode, Acquirer, Auxmsg,
TraceNumber, InstrName,
last_update_date,last_updated_by,creation_date,created_by,last_update_login,
object_version_number)
VALUES (l_trxn_mid, authcode_in,referencecode_in,avscode_in, acquirer_in,
auxmsg_in, trace_number_in, payment_name_in,
sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.login_id,1);
END insert_query_txn;
/* updates the statuses of trxns saved in a batch */
PROCEDURE updateBatchedTrxns
(
payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
bepid_in IN iby_trxn_summaries_all.bepid%TYPE,
bepkey_in IN iby_trxn_summaries_all.bepkey%TYPE,
oldstatus_in IN iby_trxn_summaries_all.status%TYPE,
newstatus_in IN iby_trxn_summaries_all.status%TYPE,
oldbatchid_in IN iby_trxn_summaries_all.batchid%TYPE,
newbatchid_in IN iby_trxn_summaries_all.batchid%TYPE
)
IS
l_mbatchid iby_trxn_summaries_all.mbatchid%TYPE;
UPDATE iby_trxn_summaries_all
SET
status = newstatus_in,
batchid = newbatchid_in,
mbatchid = l_mbatchid,
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE (bepid = bepid_in)
AND (bepkey = bepkey_in)
AND (payeeid = payeeid_in)
AND (status = oldstatus_in)
AND ((instrtype='CREDITCARD') OR (instrtype='PURCHASECARD') OR (instrtype IS NULL))
AND ((batchid IS NULL AND oldbatchid_IN IS NULL) OR (batchid = oldbatchid_in));
END updateBatchedTrxns;
PROCEDURE updateBatchQueryTrxn
(
payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
status_in IN iby_trxn_summaries_all.status%TYPE,
bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
)
IS
BEGIN
UPDATE
iby_trxn_summaries_all
SET
status=status_in,
--
-- only change these values if they have non-trivial values
--
bepcode=DECODE(NVL(bep_code_in,''), '',bepcode, bep_code_in),
bepmessage=DECODE(NVL(bep_msg_in,''), '',bepmessage, bep_msg_in),
errorlocation=DECODE(NVL(error_loc_in,''), '',errorlocation, error_loc_in),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE
--
-- Where clause modified to support
-- returns (transaction type 5):
-- Returns(5) and credits(11) are to be
-- treated as equivalents because in the
-- processor model we send return transactions
-- as credits. When we query for the status of
-- such a credit, we must remember to update the
-- status of the original return.
--
(payeeid = payeeid_in) AND
(tangibleid = orderid_in) AND
(trxntypeid IN (trxn_type_in, 5)) AND
(batchid = batchid_in);
END updateBatchQueryTrxn;
PROCEDURE updateBatchQueryTrxn
(
payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
orderid_in IN iby_trxn_summaries_all.batchid%TYPE,
trxn_type_in IN iby_trxn_summaries_all.trxntypeid%TYPE,
batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
status_in IN iby_trxn_summaries_all.status%TYPE,
bep_code_in IN iby_trxn_summaries_all.bepcode%TYPE,
bep_msg_in IN iby_trxn_summaries_all.bepmessage%TYPE,
error_loc_in IN iby_trxn_summaries_all.errorlocation%TYPE,
authcode_in IN iby_trxn_core.authcode%TYPE,
avscode_in IN iby_trxn_core.avscode%TYPE,
cvv2result_in IN iby_trxn_core.cvv2result%TYPE,
trxnid_out OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE
)
IS
l_trxnmid iby_trxn_summaries_all.trxnmid%TYPE;
updateBatchQueryTrxn(payeeid_in,orderid_in,trxn_type_in,batchid_in,status_in,bep_code_in,bep_msg_in,error_loc_in,trxnid_out);
SELECT
trxnmid
INTO
l_trxnmid
FROM
iby_trxn_summaries_all
WHERE
--
-- Where clause modified to support
-- returns (transaction type 5):
-- Returns(5) and credits(11) are to be
-- treated as equivalents because in the
-- processor model we send return transactions
-- as credits. When we query for the status of
-- such a credit, we must remember to update the
-- status of the original return.
--
(transactionid = trxnid_out) AND
(payeeid = payeeid_in) AND
(trxntypeid IN (trxn_type_in, 5));
UPDATE
iby_trxn_core
SET
authcode=authcode_in,
cvv2result=cvv2result_in,
avscode=avscode_in
WHERE
(trxnmid=l_trxnmid);
END updateBatchQueryTrxn;
PROCEDURE Update_Batch
(
ecapp_id_in IN iby_batches_all.ecappid%TYPE,
payeeid_in IN iby_trxn_summaries_all.payeeid%TYPE,
batchid_in IN iby_trxn_summaries_all.batchid%TYPE,
batch_status_in IN iby_batches_all.batchstatus%TYPE,
batch_total_in IN iby_batches_all.batchtotal%TYPE,
sale_amount_in IN iby_batches_all.batchsales%TYPE,
credit_amount_in IN iby_batches_all.batchcredit%TYPE,
bep_code_in IN iby_batches_all.bepcode%TYPE,
bep_message_in IN iby_batches_all.bepmessage%TYPE,
error_location_in IN iby_batches_all.errorlocation%TYPE,
ack_type_in IN VARCHAR2,
trxn_orderid_in IN JTF_VARCHAR2_TABLE_100,
trxn_reqtype_in IN JTF_VARCHAR2_TABLE_100,
trxn_status_in IN JTF_VARCHAR2_TABLE_100,
trxn_bep_code_in IN JTF_VARCHAR2_TABLE_100,
trxn_bep_msg_in IN JTF_VARCHAR2_TABLE_100,
trxn_error_loc_in IN JTF_VARCHAR2_TABLE_100,
trxn_authcode_in IN JTF_VARCHAR2_TABLE_100,
trxn_avscode_in IN JTF_VARCHAR2_TABLE_100,
trxn_cvv2result_in IN JTF_VARCHAR2_TABLE_100,
trxn_tracenumber IN JTF_VARCHAR2_TABLE_100
)
IS
l_tmid iby_trxn_summaries_all.trxnmid%TYPE;
l_module VARCHAR2(100) := G_DEBUG_MODULE || '.Update_Batch';
SELECT trxnmid, transactionid,needsupdt
FROM iby_trxn_summaries_all
WHERE (payeeid = ci_payeeid)
AND (tangibleid = ci_orderid)
-- ack cannot distinguish between credits and returns
AND (DECODE(reqtype, 'ORAPMTRETURN','ORAPMTCREDIT', reqtype) =
ci_reqtype)
AND (status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING)
AND (mbatchid = ci_mbatchid);
SELECT ibs.trxnmid, ibs.transactionid, ibs.needsupdt
FROM iby_trxn_summaries_all ibs,
iby_trxn_summaries_all orig_trxn,
iby_trxn_core ibc
WHERE ibs.transactionid = orig_trxn.transactionid
AND ibs.trxntypeid IN(8, 5, 11)
AND orig_trxn.trxnmid=ibc.trxnmid
AND ibc.tracenumber=ci_tracenumber
AND ibs.mbatchid=ci_mbatchid
AND ibs.status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING;
UPDATE iby_batches_all
SET batchstatus = batch_status_in,
batchtotal = batch_total_in,
batchsales = sale_amount_in,
batchcredit = credit_amount_in,
bepcode = bep_code_in,
bepmessage = bep_message_in,
errorlocation = error_location_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE (payeeid = payeeid_in)
AND (mbatchid = batchid_in);
UPDATE iby_trxn_summaries_all
SET status = TO_NUMBER(trxn_status_in(i)),
bepcode = trxn_bep_code_in(i),
bepmessage = trxn_bep_msg_in(i),
errorlocation = TO_NUMBER(trxn_error_loc_in(i)),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1,
needsupdt = l_needsupdt
WHERE (trxnmid = l_tmid);
UPDATE iby_trxn_core
SET authcode = trxn_authcode_in(i),
avscode = trxn_avscode_in(i),
cvv2result = trxn_cvv2result_in(i),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE (trxnmid = l_tmid);
UPDATE iby_trxn_summaries_all
SET
status = DECODE(ack_type_in,
'P',C_STATUS_BEP_FAIL,
'N',C_STATUS_SUCCESS,
C_STATUS_OPEN_BATCHED),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE (payeeid = payeeid_in)
AND (mbatchid = batchid_in)
AND (status = iby_transactioncc_pkg.C_STATUS_BATCH_PENDING);
END Update_Batch;
SELECT mBatchid from iby_Batches_All
WHERE Batchid = ci_Batchid
AND PayeeID = ci_PayeeID;
for QueryTrxn where there will be no orgid inserted
try to see if there is already some valid org id stored,
if so, use the non-null one first
*/
Function getOrgId(i_tid in iby_trxn_summaries_all.transactionid%type)
return number
IS
l_org_id NUMBER := NULL;
SELECT DISTINCT org_id
FROM iby_trxn_summaries_all
WHERE transactionid = i_tid
AND status <> -99 AND status <> 14 -- ignore cancelled trxns
AND org_id IS NOT NULL;
SELECT count(*)
INTO l_num_trxns
FROM iby_trxn_summaries_all
WHERE TangibleID = i_tangibleid
AND UPPER(ReqType) = UPPER(i_reqtype)
AND PayeeID = i_payeeid
AND (status IN (11,9));
SELECT distinct transactionid
FROM iby_trxn_summaries_all
WHERE tangibleid = ci_tangibleid
AND payeeid = ci_payeeid;
SELECT iby_trxnsumm_trxnid_s.NEXTVAL
INTO l_tid
FROM dual;
SELECT distinct transactionid
FROM iby_trxn_summaries_all
WHERE tangibleid = ci_tangibleid
AND payeeid = ci_payeeid;
SELECT iby_trxnsumm_trxnid_s.NEXTVAL
INTO l_tid
FROM dual;
SELECT /*+ rowid(tx) */
tx.trxnmid,
iby_utility_pvt.decode64(instrnumber) ccnum,
tx.instrtype
FROM iby_trxn_summaries_all tx
WHERE
(NOT instrnumber IS NULL)
AND (DECODE(instrtype, 'PINLESSDEBITCARD','CREDITCARD',
'PURCHASECARD','CREDITCARD',
-- instrument type will be NULL for credit card credit trxns
NULL,DECODE(reqtype,
'ORAPMTCREDIT','CREDITCARD',
NULL),
instrtype) = 'CREDITCARD')
AND (instrnum_sec_segment_id IS NULL);
SELECT
tx.trxnmid tmid,
core.instr_expirydate expdate,
core.instr_owner_name chname
FROM iby_trxn_summaries_all tx,
iby_trxn_core core
WHERE
(DECODE(tx.instrtype, 'PINLESSDEBITCARD','CREDITCARD',
'PURCHASECARD','CREDITCARD',
-- instrument type will be NULL for credit card credit trxns
NULL,DECODE(tx.reqtype,
'ORAPMTCREDIT','CREDITCARD',
NULL),
tx.instrtype) = 'CREDITCARD')
AND tx.trxnmid = core.trxnmid
AND NVL(core.encrypted, 'N') = 'N';
UPDATE iby_trxn_summaries_all
SET
instrnumber = l_maskedcc_tbl(i),
instrnum_hash = l_cchash_tbl(i),
cc_issuer_range_id = l_rangeid_tbl(i),
instrnum_length = l_instrlen_tbl(i),
instrnum_sec_segment_id = l_segmentid_tbl(i)
WHERE trxnmid=l_trxnmid_tbl(i);
l_maskedcc_tbl.delete;
l_issuer_tbl.delete;
l_cchash_tbl.delete;
l_rangeid_tbl.delete;
l_instrlen_tbl.delete;
l_segmentid_tbl.delete;
l_trxnmid_tbl.delete;
UPDATE iby_trxn_summaries_all
SET
instrnumber = l_maskedcc_tbl(i),
instrnum_hash = l_cchash_tbl(i),
cc_issuer_range_id = l_rangeid_tbl(i),
instrnum_length = l_instrlen_tbl(i),
instrnum_sec_segment_id = l_segmentid_tbl(i)
WHERE trxnmid=l_trxnmid_tbl(i);
UPDATE iby_trxn_core SET
encrypted = 'Y',
instr_owner_name = l_masked_chname,
chname_sec_segment_id = l_chname_sec_segment_id,
instr_expirydate = NULL,
expiry_sec_segment_id = l_expdate_sec_segment_id,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE (trxnmid = core_rec.tmid);
SELECT /*+ rowid(tx) */
tx.trxnmid,
iby_transactioncc_pkg.unencrypt_instr_num
(tx.instrnumber, NULL, NULL, ci_sys_key,
k.subkey_cipher_text, tx.instrnum_sec_segment_id,
seg.segment_cipher_text, r.card_number_prefix,
NVL(r.card_number_length,tx.instrnum_length),
i.digit_check_flag) ccnum,
instrnum_sec_segment_id
FROM iby_trxn_summaries_all tx,
iby_security_segments seg, iby_sys_security_subkeys k,
iby_cc_issuer_ranges r, iby_creditcard_issuers_b i
WHERE
/*
(NOT instrnumber IS NULL)
AND (DECODE(instrtype, 'PINLESSDEBITCARD','CREDITCARD',
'PURCHASECARD','CREDITCARD',
-- instrument type will be NULL for credit card credit trxns
NULL,DECODE(reqtype,
'ORAPMTCREDIT','CREDITCARD',
NULL),
instrtype) = 'CREDITCARD')
*/
(NOT instrnum_sec_segment_id IS NULL)
AND (tx.instrnum_sec_segment_id = seg.sec_segment_id(+))
AND (seg.sec_subkey_id = k.sec_subkey_id(+))
AND (tx.cc_issuer_range_id = r.cc_issuer_range_id(+))
AND (tx.instrsubtype = i.card_issuer_code(+));
SELECT
trxnmid,
instr_expirydate,
expiry_sec_segment_id,
instr_owner_name,
chname_sec_segment_id
FROM iby_trxn_core
WHERE NVL(encrypted, 'N') = 'Y';
UPDATE iby_trxn_summaries_all
SET
instrnumber = l_instrnum_tbl(i),
instrnum_sec_segment_id = NULL
WHERE trxnmid=l_trxnmid_tbl(i);
DELETE iby_security_segments
WHERE sec_segment_id = l_segmentid_tbl(i);
l_segmentid_tbl.delete;
l_instrnum_tbl.delete;
l_trxnmid_tbl.delete;
UPDATE iby_trxn_summaries_all
SET
instrnumber = l_instrnum_tbl(i),
instrnum_sec_segment_id = NULL
WHERE trxnmid=l_trxnmid_tbl(i);
DELETE iby_security_segments
WHERE sec_segment_id = l_segmentid_tbl(i);
UPDATE iby_trxn_core SET
encrypted = 'N',
instr_owner_name = l_chname,
chname_sec_segment_id = NULL,
instr_expirydate = l_exp_date,
expiry_sec_segment_id = NULL,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE (trxnmid = core_rec.trxnmid);
SELECT count(transactionid)
FROM iby_trxn_summaries_all
WHERE (ci_bepid=bepid)
AND (ci_payeeid=payeeid)
AND (ci_bepkey=bepkey)
--
-- can have multiple trxns from a
-- different ecapp's for a single payee account;
SELECT NVL(max_batch_size,-1)
FROM iby_bepinfo
WHERE (ci_bepid=bepid);
SELECT to_char(IBY_BATCHID_S.nextval)
INTO batchid_out
FROM dual;
iby_transactioncc_pkg.insert_batch_status
(
batchid_out,
payeeid_in,
bepid_in,
bepkey_in,
iby_creditcard_pkg.C_INSTRTYPE_CCARD,
iby_transactioncc_pkg.C_STATUS_COMMUNICATION_ERROR,
SYSDATE,
'',
'',
trxncount_out,
iby_transactioncc_pkg.C_STATUS_COMMUNICATION_ERROR,
0,
0,
0,
'',
'',
'',
'',
'',
'',
orgid_in,
'ORAPMTCLOSEBATCH',
seckey_present_in,
l_mbatch_id
);
| Update_Payer_Notif_Batch
|
| PURPOSE:
| This procedure updates the payer_notification_required flag for
| all the transactions in a batch.
|
| PARAMETERS:
| IN
| p_debug_text - The debug message to be printed
|
| OUT
|
|
| RETURNS:
|
| NOTES:
|
*---------------------------------------------------------------------*/
PROCEDURE Update_Payer_Notif_Batch(
mbatchid_in IN iby_batches_all.mbatchid%TYPE
) IS
l_process_profile iby_batches_all.process_profile_code%TYPE;
SELECT DECODE(payer_notification_format, null, 'N', 'Y')
FROM iby_fndcpt_user_cc_pf_b up, iby_fndcpt_sys_cc_pf_b sp
WHERE up.sys_cc_profile_code = sp.sys_cc_profile_code
AND up.user_cc_profile_code = c_user_profile;
SELECT DECODE(payer_notification_format, null, 'N', 'Y')
FROM iby_fndcpt_user_dc_pf_b up, iby_fndcpt_sys_dc_pf_b sp,
iby_batches_all b
WHERE up.sys_dc_profile_code = sp.sys_dc_profile_code
AND up.user_dc_profile_code =c_user_profile;
SELECT DECODE(payer_notification_format, null, 'N', 'Y')
FROM iby_fndcpt_user_eft_pf_b up, iby_fndcpt_sys_eft_pf_b sp,
iby_batches_all b
WHERE up.sys_eft_profile_code = sp.sys_eft_profile_code
AND up.user_eft_profile_code = c_user_profile;
SELECT instrument_type, process_profile_code
FROM iby_batches_all
WHERE mbatchid = i_mbatchid;
UPDATE iby_batches_all
SET
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1,
payer_notification_required = l_payer_notif_flag
WHERE (mbatchid = mbatchid_in);
UPDATE iby_trxn_summaries_all
SET payer_notification_required = l_payer_notif_flag,
last_update_date=sysdate,
last_updated_by = fnd_global.user_id,
object_version_number = object_version_number + 1
WHERE mbatchid = mbatchid_in
AND TrxntypeID IN (3,8,9,100);
END Update_Payer_Notif_Batch;
PROCEDURE delete_trxnTable IS
l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME ||
'.delete_trxnTable';
trxnTab.transactionid.delete;
trxnTab.mbatchid.delete;
trxnTab.batchid.delete;