[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* inserts a new row into the transactions table after the vendor */
/* application has returned. It does not perform any error checking */
/* to make sure that the row already exists since the programmer */
/* should have called queryset.listvendor to check beforehand. */
PROCEDURE insert_init_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_trxn_summaries_all.TangibleID%TYPE,
merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
trxn_type_in IN OUT NOCOPY iby_trxn_summaries_all.TrxntypeID%TYPE,
payment_name_in IN iby_trxn_core.InstrName%TYPE,
price_in IN iby_trxn_summaries_all.Amount%TYPE,
currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
status_in IN iby_trxn_summaries_all.Status%TYPE,
transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
vendor_code_in IN iby_trxn_summaries_all.BEPCode%TYPE,
vendor_message_in IN iby_trxn_summaries_all.BEPMessage%TYPE,
error_location_in IN iby_trxn_summaries_all.ErrorLocation%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,
org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
instrnum_in IN iby_trxn_summaries_all.instrnumber%type)
IS
num_trxns NUMBER := 0;
SELECT count(*)
INTO num_trxns
FROM iby_trxn_summaries_all
WHERE TangibleID = order_id_in
AND UPPER(ReqType) = UPPER(req_type_in)
AND PayeeID = merchant_id_in;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO trxn_mid
FROM dual;
SELECT iby_trxnsumm_trxnid_s.NEXTVAL
INTO transaction_id
FROM dual;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID,TrxntypeID, ECAPPID, org_id,
ReqType, ReqDate,
Amount,CurrencyNameCode, UpdateDate,Status,
TangibleID,MPayeeID, PayeeID,BEPID,MtangibleId,
BEPCode,BEPMessage,Errorlocation,
payerinstrid, instrnumber,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES (trxn_mid, transaction_id, trxn_type_in, ecapp_id_in,
org_id_in,
req_type_in, time_in,
price_in, currency_in, time_in, status_in,
order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,l_tmid,
vendor_code_in, vendor_message_in, error_location_in,
payerinstrid_in, instrnum_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
INSERT INTO iby_trxn_extended
(TrxnMID, SplitID,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
VALUES (trxn_mid, '1',
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
SELECT TrxnMID, TransactionID,MtangibleId
INTO trxn_mid, transaction_id_in_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;
UPDATE iby_trxn_summaries_all
SET Amount = price_in,
CurrencyNameCode = currency_in,
--ReqDate = time_in,
updatedate = time_in,
Status = status_in,
ErrorLocation = error_location_in,
BEPCode = vendor_code_in,
BEPMessage = vendor_message_in,
payerinstrid = payerinstrid_in,
instrnumber = instrnum_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
UPDATE iby_trxn_extended
SET SplitID = '1',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
END insert_init_txn;
PROCEDURE insert_set_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_trxn_summaries_all.TangibleID%TYPE,
merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
split_id_in IN iby_trxn_extended.SplitID%TYPE,
payment_name_in IN iby_trxn_core.InstrName%TYPE,
price_in IN iby_trxn_summaries_all.Amount%TYPE,
currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
status_in IN iby_trxn_summaries_all.Status%TYPE,
authcode_in IN iby_trxn_core.AuthCode%TYPE,
capcode_in IN iby_trxn_core.OperationCode%TYPE,
completion_code_in IN iby_trxn_extended.CompletionCode%TYPE,
set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
batch_id_in IN iby_trxn_summaries_all.batchID%TYPE,
batch_seq_num_in IN iby_trxn_extended.BatchSeqNum%TYPE,
AVS_result_in IN iby_trxn_core.AVSCode%TYPE,
ret_ref_num_in IN iby_trxn_core.ReferenceCode%TYPE,
card_BIN_in IN iby_trxn_extended.Cardbin%TYPE,
terminal_id_in IN iby_trxn_extended.TerminalID%TYPE,
request_type_in IN iby_trxn_extended.SETReqType%TYPE,
subseq_auth_ind_in IN iby_trxn_extended.SubAuthInd%TYPE,
transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
payment_method_in IN iby_trxn_summaries_all.PAYMENTMETHODNAME%TYPE,
vendor_code_in IN iby_trxn_summaries_all.BEPCode%TYPE,
vendor_message_in IN iby_trxn_summaries_all.BEPMessage%TYPE,
error_location_in IN iby_trxn_summaries_all.ErrorLocation%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,
org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
instrnum_in IN iby_trxn_summaries_all.instrnumber%type)
IS
num_trxns NUMBER;
SELECT count(*)
INTO num_trxns
FROM iby_trxn_summaries_all
WHERE TangibleID = order_id_in
AND UPPER(ReqType) = UPPER(req_type_in)
AND PayeeID = merchant_id_in;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO trxn_mid
FROM dual;
SELECT count(*)
INTO counter
FROM iby_trxn_summaries_all
WHERE TangibleID = order_id_in
AND PayeeID = merchant_id_in;
SELECT iby_trxnsumm_trxnid_s.NEXTVAL
INTO transaction_id
FROM dual;
SELECT DISTINCT TransactionId
INTO transaction_id
FROM iby_trxn_summaries_all
WHERE TangibleID = order_id_in
AND PayeeID = merchant_id_in;
SELECT DISTINCT mtangibleid, tangibleid
INTO l_tmid, l_order_id
FROM iby_trxn_summaries_all
WHERE transactionid = transaction_id_in_out;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID,TrxntypeID,
ECAPPID, org_id, ReqType, ReqDate, MtangibleId,
Amount,CurrencyNameCode, TangibleID,MPayeeID, PayeeID,BEPID,
BEPCode, BEPMessage,Errorlocation,PAYMENTMETHODNAME,status,
payerinstrid, instrnumber,
last_update_date, updatedate, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES (trxn_mid, transaction_id, trxn_type_in,
ecapp_id_in, org_id_in, req_type_in, sysdate, l_tmid,
price_in, currency_in, l_order_id, l_mpayeeid,
merchant_id_in, vendor_id_in,
vendor_code_in, vendor_message_in, error_location_in,
payment_method_in,status_in,
payerinstrid_in, instrnum_in,
sysdate, sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
INSERT INTO iby_trxn_core
(TrxnMID, OperationCode, AVSCode, ReferenceCode,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
VALUES (trxn_mid, oper_code, avs_result_in, ret_ref_num_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
INSERT INTO iby_trxn_extended
(TrxnMID, SplitID, CompletionCode, SETTrxnID,
BatchSeqNum,
Cardbin, TerminalID, SETReqType, SubAuthInd,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
VALUES
(trxn_mid, '1', completion_code_in, set_trxn_id_in,
batch_seq_num_in,
card_bin_in, terminal_id_in, request_type_in, subseq_auth_ind_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
SELECT TrxnMID, TransactionID,MtangibleId
INTO trxn_mid, transaction_id_in_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;
-- Update iby_tangible table
iby_bill_pkg.modBill(l_tmid,order_id_in,price_in,currency_in,
billeracct_in,refinfo_in,memo_in,
order_medium_in, eft_auth_method_in);
UPDATE iby_trxn_summaries_all
SET Amount = price_in,
CurrencyNameCode = currency_in,
UpdateDate = time_in,
Status = status_in,
ErrorLocation = error_location_in,
BEPCode = vendor_code_in,
BEPMessage = vendor_message_in,
BatchID = batch_id_in,
MBatchID = l_mbatchid,
payerinstrid = payerinstrid_in,
instrnumber = instrnum_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
UPDATE iby_trxn_core
SET OperationCode = oper_code,
AvsCode = avs_result_in,
ReferenceCode = ret_ref_num_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
UPDATE iby_trxn_extended
SET SplitID = '1',
CompletionCode = completion_code_in,
SETTrxnID = set_trxn_id_in,
BatchSeqNum = batch_seq_num_in,
Cardbin = card_bin_in,
TerminalID = terminal_id_in,
SETReqType = request_type_in,
SubAuthInd = subseq_auth_ind_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
END insert_set_txn;
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_trxn_summaries_all.TangibleID%TYPE,
merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
split_id_in IN iby_trxn_extended.SplitID%TYPE,
payment_name_in IN iby_trxn_summaries_all.PaymentMethodName%TYPE,
price_in IN iby_trxn_summaries_all.Amount%TYPE,
currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
status_in IN iby_trxn_summaries_all.Status%TYPE,
authcode_in IN iby_trxn_core.AuthCode%TYPE,
capcode_in IN iby_trxn_core.OperationCode%TYPE,
completion_code_in IN iby_trxn_extended.CompletionCode%TYPE,
set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
batch_id_in IN OUT NOCOPY iby_trxn_summaries_all.batchID%TYPE,
batch_seq_num_in IN OUT NOCOPY iby_trxn_extended.BatchSeqNum%TYPE,
AVS_result_in IN OUT NOCOPY iby_trxn_core.AVSCode%TYPE,
ret_ref_num_in IN iby_trxn_core.ReferenceCode%TYPE,
card_BIN_in IN OUT NOCOPY iby_trxn_extended.Cardbin%TYPE,
terminal_id_in IN OUT NOCOPY iby_trxn_extended.TerminalID%TYPE,
subseq_auth_ind_in IN iby_trxn_extended.SubAuthInd%TYPE,
vendor_code_in IN OUT NOCOPY iby_trxn_summaries_all.BEPCode%TYPE,
vendor_message_in IN OUT NOCOPY iby_trxn_summaries_all.BEPMessage%TYPE,
error_location_in IN OUT NOCOPY iby_trxn_summaries_all.ErrorLocation%TYPE,
transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
payment_method_in IN
iby_trxn_summaries_all.PAYMENTMETHODNAME%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,
org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
instrnum_in IN iby_trxn_summaries_all.instrnumber%type)
IS
num_trxns NUMBER := 0;
SELECT count(*)
INTO num_trxns
FROM iby_trxn_summaries_all
WHERE TangibleID = order_id_in
AND UPPER(ReqType) = UPPER(req_type_in)
AND PayeeID = merchant_id_in;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO trxn_mid
FROM dual;
SELECT iby_trxnsumm_trxnid_s.NEXTVAL
INTO transaction_id
FROM dual;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TangibleID,MPayeeID, PayeeID,BEPID, PaymentMethodName,
TransactionID,TrxntypeID, ECAPPID, org_id,
ReqType, ReqDate, MtangibleId,
Amount,CurrencyNameCode,
UpdateDate,Status,MBatchID, BatchID,
BEPCode, BEPMessage,Errorlocation,
payerinstrid, instrnumber,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES (trxn_mid, order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
payment_method_in,
transaction_id, trxn_type_in, ecapp_id_in, org_id_in,
req_type_in, time_in, l_tmid,
price_in, currency_in, time_in, status_in, l_mbatchid,
batch_id_in,
vendor_code_in, vendor_message_in, error_location_in,
payerinstrid_in, instrnum_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
INSERT INTO iby_trxn_core
(TrxnMID, OperationCode, AVSCode, ReferenceCode,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
VALUES (trxn_mid, oper_code, avs_result_in, ret_ref_num_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
INSERT INTO iby_trxn_extended
(TrxnMID, SplitID, CompletionCode, SETTrxnID,
BatchSeqNum,
Cardbin, TerminalID, SubAuthInd,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
VALUES
(trxn_mid, split_id_in, completion_code_in, set_trxn_id_in,
batch_seq_num_in,
card_bin_in, terminal_id_in, subseq_auth_ind_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
SELECT TrxnMID, TransactionID,MtangibleId
INTO trxn_mid, transaction_id_in_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;
UPDATE iby_trxn_summaries_all
SET Amount = price_in,
CurrencyNameCode = currency_in,
UpdateDate = time_in,
Status = status_in,
ErrorLocation = error_location_in,
BEPCode = vendor_code_in,
BEPMessage = vendor_message_in,
BatchID = batch_id_in,
MBatchID = l_mbatchid,
payerinstrid = payerinstrid_in,
instrnumber = instrnum_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
UPDATE iby_trxn_core
SET OperationCode = oper_code,
AvsCode = avs_result_in,
ReferenceCode = ret_ref_num_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
UPDATE iby_trxn_extended
SET SplitID = split_id_in,
CompletionCode = completion_code_in,
SETTrxnID = set_trxn_id_in,
BatchSeqNum = batch_seq_num_in,
Cardbin = card_bin_in,
TerminalID = terminal_id_in,
SubAuthInd = subseq_auth_ind_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
END insert_auth_txn;
/* Inserts a new row into the PS_TRANSACTIONS table. This method */
/* would be called every time a SET CAPTURE, CAPTUREREV, CREDIT, or */
/* CREDITREV 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_trxn_summaries_all.TangibleID%TYPE,
merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
split_id_in IN iby_trxn_extended.SplitID%TYPE,
payment_name_in IN iby_trxn_summaries_all.PAYMENTMETHODNAME%TYPE,
price_in IN iby_trxn_summaries_all.Amount%TYPE,
currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
status_in IN iby_trxn_summaries_all.Status%TYPE,
operation_code_in IN iby_trxn_core.OperationCode%TYPE,
set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
batch_id_in IN OUT NOCOPY iby_trxn_summaries_all.batchID%TYPE,
batch_seq_num_in IN OUT NOCOPY iby_trxn_extended.BatchSeqNum%TYPE,
terminal_id_in IN OUT NOCOPY iby_trxn_extended.TerminalID%TYPE,
subseq_auth_ind_in IN iby_trxn_extended.SubAuthInd%TYPE,
vendor_code_in IN OUT NOCOPY iby_trxn_summaries_all.BEPCode%TYPE,
vendor_message_in IN OUT NOCOPY iby_trxn_summaries_all.BEPMessage%TYPE,
error_location_in IN OUT NOCOPY iby_trxn_summaries_all.ErrorLocation%TYPE,
transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%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,
org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
instrnum_in IN iby_trxn_summaries_all.instrnumber%type)
IS
num_trxns NUMBER := 0;
SELECT count(*)
INTO num_trxns
FROM iby_trxn_summaries_all summary, iby_trxn_extended extended
WHERE summary.TangibleID = order_id_in
AND summary.PayeeID = merchant_id_in
AND extended.SplitID = split_id_in
AND UPPER(summary.ReqType) = UPPER(req_type_in)
AND summary.TrxnMID = extended.TrxnMID;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO trxn_mid
FROM dual;
SELECT count(*)
INTO counter
FROM iby_trxn_summaries_all
WHERE TangibleID = order_id_in
AND PayeeID = merchant_id_in;
SELECT iby_trxnsumm_trxnid_s.NEXTVAL
INTO transaction_id
FROM dual;
SELECT DISTINCT TransactionId
INTO transaction_id
FROM iby_trxn_summaries_all
WHERE TangibleID = order_id_in
AND PayeeID = merchant_id_in;
SELECT DISTINCT mtangibleid, tangibleid
INTO l_tmid, l_order_id
FROM iby_trxn_summaries_all
WHERE transactionid = transaction_id_in_out;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TangibleID,MPayeeID, PayeeID,BEPID,
TransactionID,TrxntypeID, ECAPPID, org_id, ReqType, ReqDate,
MtangibleId,
Amount,CurrencyNameCode, UpdateDate,Status,MBatchID, BatchID,
BEPCode, BEPMessage,Errorlocation,PaymentMethodName,
payerinstrid, instrnumber,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES (trxn_mid, l_order_id, l_mpayeeid, merchant_id_in, vendor_id_in,
transaction_id, trxn_type_in, ecapp_id_in, org_id_in,
req_type_in, time_in, l_tmid,
price_in, currency_in, time_in, status_in,
l_mbatchid, batch_id_in,
vendor_code_in, vendor_message_in, error_location_in,
payment_name_in,
payerinstrid_in, instrnum_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
INSERT INTO iby_trxn_core
(TrxnMID, OperationCode,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
VALUES (trxn_mid, operation_code_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
INSERT INTO iby_trxn_extended
(TrxnMID, SplitID, SETTrxnID,
BatchSeqNum,
TerminalID, SubAuthInd,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
VALUES
(trxn_mid, split_id_in, set_trxn_id_in,
batch_seq_num_in,
terminal_id_in, subseq_auth_ind_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
SELECT summary.TrxnMID, summary.TransactionID,MtangibleId
INTO trxn_mid, transaction_id_in_out,l_tmid
FROM iby_trxn_summaries_all summary, iby_trxn_extended extended
WHERE summary.TangibleID = order_id_in
AND summary.PayeeID = merchant_id_in
AND extended.SplitID = split_id_in
AND UPPER(summary.ReqType) = UPPER(req_type_in)
AND summary.TrxnMID = extended.TrxnMID;
-- Update iby_tangible table
iby_bill_pkg.modBill(l_tmid,order_id_in,price_in,currency_in,
billeracct_in,refinfo_in,memo_in,
order_medium_in, eft_auth_method_in);
UPDATE iby_trxn_summaries_all
SET Amount = price_in,
CurrencyNameCode = currency_in,
UpdateDate = time_in,
Status = status_in,
ErrorLocation = error_location_in,
BEPCode = vendor_code_in,
BEPMessage = vendor_message_in,
BatchID = batch_id_in,
MBatchID = l_mbatchid,
payerinstrid = payerinstrid_in,
instrnumber = instrnum_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
UPDATE iby_trxn_core
SET OperationCode = operation_code_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
UPDATE iby_trxn_extended
SET SplitID = split_id_in,
SETTrxnID = set_trxn_id_in,
BatchSeqNum = batch_seq_num_in,
TerminalID = terminal_id_in,
SubAuthInd = subseq_auth_ind_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
END insert_other_txn;
/* Inserts or updates a row in the PS_TRXN_TABLE if the */
/* operation timed out from calling the vendor. */
PROCEDURE insert_timeout_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_trxn_summaries_all.TangibleID%TYPE,
merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
split_id_in IN iby_trxn_extended.SplitID%TYPE,
payment_name_in IN iby_trxn_core.InstrName%TYPE,
time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
status_in IN iby_trxn_summaries_all.Status%TYPE,
transaction_id_in_out IN OUT NOCOPY iby_trxn_summaries_all.TransactionID%TYPE,
currency_in IN iby_tangible.currencynamecode%type,
amount_in IN iby_transactions_v.amount%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,
org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
payerinstrid_in IN iby_trxn_summaries_all.payerinstrid%type,
instrnum_in IN iby_trxn_summaries_all.instrnumber%type)
IS
num_trxns NUMBER := 0;
SELECT count(*)
INTO num_trxns
FROM iby_trxn_summaries_all
WHERE TangibleID = order_id_in
AND UPPER(ReqType) = UPPER(req_type_in)
AND PayeeID = merchant_id_in;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO trxn_mid
FROM dual;
SELECT iby_trxnsumm_trxnid_s.NEXTVAL
INTO transaction_id
FROM dual;
SELECT DISTINCT mtangibleid, tangibleid
INTO l_tmid, l_order_id
FROM iby_trxn_summaries_all
WHERE transactionid = transaction_id_in_out;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID, ECAPPID, org_id,
TangibleID, MPayeeID, PayeeID, BEPID, PaymentMethodName,
TrxntypeID, ReqType, ReqDate, MtangibleId,
UpdateDate,Status,
payerinstrid, instrnumber,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES (trxn_mid, transaction_id, ecapp_id_in, org_id_in,
l_order_id, l_mpayeeid, merchant_id_in, vendor_id_in,
payment_name_in,
trxn_type_in, req_type_in, time_in, l_tmid,
time_in, status_in,
payerinstrid_in, instrnum_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
INSERT INTO iby_trxn_extended
(TrxnMID, SplitID,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
VALUES (trxn_mid, '1',
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
INSERT INTO iby_trxn_core
(TrxnMID, InstrName,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
VALUES (trxn_mid, payment_name_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
SELECT TrxnMID, MtangibleId
INTO trxn_mid, l_tmid
FROM iby_trxn_summaries_all
WHERE TangibleID = order_id_in
AND UPPER(ReqType) = UPPER(req_type_in)
AND PayeeID = merchant_id_in;
-- 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,
--ReqDate = time_in,
Status = status_in,
payerinstrid = payerinstrid_in,
instrnumber = instrnum_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
UPDATE iby_trxn_extended
SET SplitID = '1',
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
END insert_timeout_txn;
/* Inserts or updates a batch summary row into the PS_BATCH */
/* table. This should happen for open or close batch operations. */
PROCEDURE insert_batch_status
(batch_id_in IN iby_batches_all.BatchID%TYPE,
merchant_id_in IN iby_batches_all.PayeeID%TYPE,
bep_id_in IN iby_batches_all.BEPID%TYPE,
/* vendor_suffix_in IN iby_batches_all.vendor_suffix%TYPE, */
/* close_status_in IN iby_batches_all.BatchCloseStatus%TYPE, */
currency_in IN iby_batches_all.CurrencyNameCode%TYPE,
sale_price_in IN iby_batches_all.BatchSales%TYPE,
credit_price_in IN iby_batches_all.BatchCredit%TYPE,
trxn_count_in IN iby_batches_all.NumTrxns%TYPE,
sale_trxn_count_in IN iby_batches_all.NumTrxns%TYPE,
credit_trxn_count_in IN iby_batches_all.NumTrxns%TYPE,
open_date_in IN iby_batches_all.BatchOpenDate%TYPE,
close_date_in IN iby_batches_all.BatchCloseDate%TYPE,
status_in IN iby_batches_all.BatchStatus%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,
org_id_in IN iby_batches_all.org_id%TYPE,
req_type_in IN iby_batches_all.reqtype%type)
IS
num_trxns NUMBER;
SELECT count(*)
INTO num_trxns
FROM iby_batches_all
WHERE BatchId = batch_id_in
AND PayeeID = merchant_id_in;
SELECT iby_batches_s.NEXTVAL
INTO l_mbatchid
FROM dual;
INSERT INTO iby_batches_all
(MBatchID, BatchID, org_id, MPayeeID, PayeeID, /* BatchCloseStatus,*/
CURRENCYNameCode, BatchSales, BatchCredit, BatchTotal,
NumTrxns, BatchOpenDate, BatchCloseDate, BatchStatus,
BEPCode, BEPMessage, ErrorLocation, reqtype, reqdate,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
VALUES
(l_mbatchid, batch_id_in, org_id_in, l_mpayeeid,
merchant_id_in, /*close_status_in, */
currency_in, sale_price_in, credit_price_in, sale_price_in - credit_price_in,
sale_trxn_count_in + credit_trxn_count_in,
open_date_in, close_date_in, status_in,
vendor_code_in, vendor_message_in, error_location_in,
req_type_in, sysdate,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
UPDATE iby_batches_all
SET /* BatchCloseStatus = close_status_in, */
reqtype = req_type_in,
reqdate = sysdate,
CurrencyNameCode = currency_in,
BatchSales = sale_price_in,
BatchCredit = credit_price_in,
BatchTotal = sale_price_in - credit_price_in,
NumTrxns = sale_trxn_count_in + credit_trxn_count_in,
BatchOpenDate = open_date_in,
BatchCloseDate = close_date_in,
BatchStatus = status_in,
BEPCode = vendor_code_in,
BEPMessage = vendor_message_in,
ErrorLocation = error_location_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE BatchID = batch_id_in
AND PayeeID = merchant_id_in;
END insert_batch_status;
/* Inserts or updates the batch detail record upon the */
/* closebatch or querybatch operations. */
PROCEDURE insert_batch_txn
(ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
split_id_in IN iby_trxn_extended.SplitID%TYPE,
payment_name_in IN iby_trxn_core.InstrName%TYPE,
price_in IN iby_trxn_summaries_all.Amount%TYPE,
currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
status_in IN iby_trxn_summaries_all.Status%TYPE,
set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
prev_set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
batch_id_in IN iby_trxn_summaries_all.batchID%TYPE,
batch_seq_num_in IN iby_trxn_extended.BatchSeqNum%TYPE,
batch_trxn_status_in IN iby_trxn_extended.BatchTrxnStatus%TYPE,
card_BIN_in IN iby_trxn_extended.Cardbin%TYPE,
terminal_id_in IN iby_trxn_extended.TerminalID%TYPE,
vendor_code_in IN iby_trxn_summaries_all.BEPCode%TYPE,
vendor_message_in IN iby_trxn_summaries_all.BEPMessage%TYPE,
error_location_in IN iby_trxn_summaries_all.ErrorLocation%TYPE,
split_id_in_out IN OUT NOCOPY iby_trxn_extended.SplitID%TYPE,
org_id_in IN iby_trxn_summaries_all.org_id%TYPE)
IS
num_trxns NUMBER := 0;
SELECT count(*)
INTO num_trxns
FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
WHERE extended.SETTrxnID = set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND extended.TrxnMID = summary.TrxnMID;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO trxn_mid
FROM dual;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TangibleID,MPayeeID, PayeeID,BEPID,
TrxntypeID, ECAPPID, org_id,
Amount,CurrencyNameCode, UpdateDate,Status,MBatchID, BatchID,
BEPCode, BEPMessage,Errorlocation,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES (trxn_mid, order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
trxn_type_in, ecapp_id_in,org_id_in,
price_in, currency_in, time_in, status_in,
l_mbatchid, batch_id_in,
vendor_code_in, vendor_message_in, error_location_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
INSERT INTO iby_trxn_extended
(TrxnMID, SplitID, SETTrxnID,
BatchSeqNum, BatchTrxnStatus,
Cardbin, TerminalID,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login, object_version_number)
VALUES
(trxn_mid, '1', set_trxn_id_in,
batch_seq_num_in, batch_trxn_status_in,
card_bin_in, terminal_id_in,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
SELECT summary.TrxnMID
INTO trxn_mid
FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
WHERE extended.SETTrxnID = set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND extended.TrxnMID = summary.TrxnMID;
UPDATE iby_trxn_extended
SET BatchSeqNum = batch_seq_num_in,
BatchTrxnStatus = batch_trxn_status_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
SELECT extended.SplitID
INTO split_id_in_out
FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
WHERE extended.SETTrxnID = set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND extended.TrxnMID = summary.TrxnMID;
SELECT summary.TrxnMID
INTO trxn_mid
FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
WHERE extended.SETTrxnID = set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND extended.TrxnMID = summary.TrxnMID;
UPDATE iby_trxn_extended
SET Cardbin = card_BIN_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
SELECT summary.TrxnMID
INTO trxn_mid
FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
WHERE extended.SETTrxnID = set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND extended.TrxnMID = summary.TrxnMID;
UPDATE iby_trxn_extended
SET TerminalID = terminal_id_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
SELECT summary.TrxnMID
INTO trxn_mid
FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
WHERE extended.SETTrxnID = set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND extended.TrxnMID = summary.TrxnMID;
UPDATE iby_trxn_summaries_all
SET ErrorLocation = error_location_in,
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
SELECT summary.TrxnMID
INTO trxn_mid
FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
WHERE extended.SETTrxnID = set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND extended.TrxnMID = summary.TrxnMID;
UPDATE iby_trxn_summaries_all
SET BEPCode = vendor_code_in,
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
SELECT summary.TrxnMID
INTO trxn_mid
FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
WHERE extended.SETTrxnID = set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND extended.TrxnMID = summary.TrxnMID;
UPDATE iby_trxn_summaries_all
SET BEPMessage = vendor_message_in,
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
END insert_batch_txn;
/* Inserts transaction record for transaction query operation */
PROCEDURE insert_query_txn
(ecapp_id_in IN iby_trxn_summaries_all.ECAPPID%TYPE,
order_id_in IN iby_trxn_summaries_all.TangibleID%TYPE,
merchant_id_in IN iby_trxn_summaries_all.PayeeID%TYPE,
vendor_id_in IN iby_trxn_summaries_all.BEPID%TYPE,
trxn_type_in IN iby_trxn_summaries_all.TrxntypeID%TYPE,
price_in IN iby_trxn_summaries_all.Amount%TYPE,
currency_in IN iby_trxn_summaries_all.CurrencyNameCode%TYPE,
time_in IN iby_trxn_summaries_all.UpdateDate%TYPE,
status_in IN iby_trxn_summaries_all.Status%TYPE,
set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
prev_set_trxn_id_in IN iby_trxn_extended.SETTrxnID%TYPE,
ret_ref_num_in IN iby_trxn_core.ReferenceCode%TYPE,
card_BIN_in IN OUT NOCOPY iby_trxn_extended.Cardbin%TYPE,
terminal_id_in IN OUT NOCOPY iby_trxn_extended.TerminalID%TYPE,
vendor_code_in IN OUT NOCOPY iby_trxn_summaries_all.BEPCode%TYPE,
vendor_message_in IN OUT NOCOPY iby_trxn_summaries_all.BEPMessage%TYPE,
error_location_in IN OUT NOCOPY iby_trxn_summaries_all.ErrorLocation%TYPE,
split_id_in_out IN OUT NOCOPY iby_trxn_extended.SplitID%TYPE,
transaction_id_in IN iby_trxn_summaries_all.TransactionID%TYPE,
payment_method_in IN iby_trxn_summaries_all.PaymentMethodName%TYPE,
org_id_in IN iby_trxn_summaries_all.org_id%TYPE,
req_type_in IN iby_trxn_summaries_all.reqtype%type)
IS
num_trxns NUMBER := 0;
SELECT COUNT(*)
INTO num_trxns
FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
WHERE extended.SETTrxnID = set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND extended.TrxnMID = summary.TrxnMID;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO trxn_mid
FROM dual;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID, paymentMethodName,
TangibleID,MPayeeID, PayeeID,BEPID,
TrxntypeID, ECAPPID, org_id, ReqDate, ReqType,
Amount,CurrencyNameCode, UpdateDate,Status,
BEPCode, BEPMessage,Errorlocation,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES (trxn_mid, transaction_id_in, payment_method_in,
order_id_in, l_mpayeeid, merchant_id_in, vendor_id_in,
trxn_type_in, ecapp_id_in, org_id_in, time_in,req_type_in,
price_in, currency_in, time_in, status_in,
vendor_code_in, vendor_message_in, error_location_in,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
INSERT INTO iby_trxn_core
(TrxnMID, ReferenceCode,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, object_version_number)
VALUES (trxn_mid, ret_ref_num_in,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
INSERT INTO iby_trxn_extended
(TrxnMID, SplitID, SETTrxnID,
-- BatchSeqNum,
Cardbin, TerminalID,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, object_version_number)
VALUES
(trxn_mid,split_id_in_out, set_trxn_id_in,
-- batch_seq_num_in,
card_bin_in, terminal_id_in,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
SELECT count(*)
INTO num_trxns
FROM iby_trxn_summaries_all summary, iby_trxn_extended extended
WHERE extended.SETTrxnID = set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND summary.Status = 0
AND summary.TrxnMID = extended.TrxnMID;
SELECT summary.TrxnMID
INTO trxn_mid
FROM iby_trxn_summaries_all summary, iby_trxn_extended extended
WHERE extended.SETTrxnID = set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND summary.TrxnMID = extended.TrxnMID;
UPDATE iby_trxn_summaries_all
SET Amount = price_in,
CurrencyNameCode = currency_in,
UpdateDate = time_in,
Status = status_in,
ErrorLocation = error_location_in,
BEPCode = vendor_code_in,
BEPMessage = vendor_message_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
UPDATE iby_trxn_core
SET ReferenceCode = ret_ref_num_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
UPDATE iby_trxn_extended
SET Cardbin = card_bin_in,
TerminalID = terminal_id_in,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
object_version_number = 1
WHERE TrxnMID = trxn_mid;
END insert_query_txn;
SELECT count(distinct extended.SplitID)
INTO num_trxns
FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
WHERE extended.SETTrxnID = prev_set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND extended.TrxnMID = summary.TrxnMID;
SELECT distinct extended.SplitID
INTO mid
FROM iby_trxn_extended extended, iby_trxn_summaries_all summary
WHERE extended.SETTrxnID = prev_set_trxn_id_in
AND summary.BEPID = vendor_id_in
AND extended.TrxnMID = summary.TrxnMID;