The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*) into l_cnt
from iby_bepkeys
where bepid = i_bepid
and ownerid = i_payeeid
and ownertype = 'PAYEE'
and key = i_bepkey;
SELECT DISTINCT mtangibleid, payerinstrid into l_tmid, l_instrid
from iby_trxn_summaries_all
where transactionid = io_transactionid
--and status <> -99 and status <> 14;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO l_mid
FROM dual;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID, TangibleID,
MPayeeID, PayeeID,BEPID, bepkey, ECAppID, PaymentMethodName,
PayerID, PayerInstrID, Amount,CurrencyNameCode,
Status, TrxntypeID, SettleDate, ReqDate, ReqType, DestUrl, Nlslang,
mtangibleid, org_id, instrtype,
last_update_date, updatedate, last_updated_by, creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES
(l_mid, io_transactionid, i_tangibleid,
l_mpayeeid, i_payeeid, i_bepid, i_bepkey, i_ecappid, i_pmtmethod,
i_payerid, l_instrid, i_amount, i_currency,
l_status, i_trxntypeid, i_settledate, sysdate, i_reqtype, i_desturl,
i_nlslang, l_tmid, l_org_id, i_instrtype,
sysdate, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id, fnd_global.login_id, 1,'Y');
INSERT INTO iby_trxn_core
(TrxnMID, Authtype, PONumber, TaxAmount, ShipFromZip, ShipToZip,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, object_version_number, AuthCode, VoiceAuthFlag)
VALUES
(l_mid, i_authtype, i_ponum, i_taxamt, i_shipfromzip, i_shiptozip,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1, i_authcode, i_voiceauthflag);
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO l_mid
FROM dual;
select mtangibleid, payerinstrid into l_tmid, l_instrid
from iby_trxn_summaries_all
where transactionid = i_transactionid
and reqtype = i_reqtype_tocancel
and rownum < 2;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID, TangibleID,
MPayeeID, PayeeID,BEPID, bepkey, ECAppID, PaymentMethodName,
status, mtangibleid, trxntypeid,
reqtype, reqdate, payerinstrid,
last_update_date, updatedate, last_updated_by, creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES
(l_mid, i_transactionid, i_tangibleid,
l_mpayeeid, i_payeeid, i_bepid, i_bepkey, i_ecappid, i_pmtmethod,
l_status, l_tmid, i_trxntypeid_tocancel,
i_reqtype, sysdate, l_instrid,
sysdate, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
SELECT iby_batches_s.NEXTVAL
INTO l_mbatchid
FROM dual;
INSERT INTO iby_batches_all
(MBatchID, BatchID, MPayeeID, PayeeID, BEPID, bepkey, ECAppID,
PaymentMethodName, BatchStatus,
ReqType, ReqDate, DestUrl,
Nlslang, TerminalID, BatchOpenDate,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, object_version_number)
VALUES
(l_mbatchid, i_batchid, l_mpayeeid, i_payeeid, i_bepid, i_bepkey, i_ecappid,
i_pmtmethod, l_status,
i_reqtype, sysdate, i_desturl,
i_nlslang, i_terminalid, i_schedDate,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
--SELECT iby_trxnsumm_trxnid_s.NEXTVAL
--INTO l_tid
--FROM dual;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO l_mid
FROM dual;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID, TangibleID,
MPayeeID, PayeeID,BEPID, ECAppID, PaymentMethodName,
PayerID, PayerInstrID, Amount,CurrencyNameCode,
Status, TrxntypeID, SettleDate, ReqDate, ReqType, ReqSeq, DestUrl,
Nlslang, MBatchID, BatchId,
last_update_date, updatedate, last_updated_by, creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES
(l_mid, io_transactionid, i_tangibleid,
l_mpayeeid, i_payeeid, i_bepid, i_ecappid, i_pmtmethod,
i_payerid, i_instrid, i_amount, i_currency,
l_status, i_trxntypeid, i_settledate, sysdate, i_reqtype,
i_reqseq,i_desturl,
i_nlslang, l_mbatchid, batchid,
sysdate, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
INSERT INTO iby_trxn_extended
(TRXNMID, RecurringFreq, RecurringExpDate, DestPostalCode,
CustRefNum, LocalTaxPrice, LocalTaxCurrency,
AuthPrice, AuthCurrency, InstallTotalTrxns, SplitShipment, SplitId,
BatchSeqNum, TerminalId,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, object_version_number)
VALUES
(l_mid, recurringfreq,recurringexpdate,destpostalcode,
custrefnum, localtaxprice, localtaxcurrency,
authprice, authcurrency, installtotaltrxns, splitshipment, splitid,
batchseqnum, terminalid,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
INSERT INTO iby_trxn_core
(TrxnMID, Authtype,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, object_version_number)
VALUES
(l_mid, i_authtype,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO l_mid
FROM dual;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID, TangibleID,
MPayeeID, PayeeID,BEPID, ECAppID, PaymentMethodName, status,
last_update_date, updatedate, last_updated_by, creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES
(l_mid, i_transactionid, i_tangibleid,
l_mpayeeid, i_payeeid, i_bepid, i_ecappid, i_pmtmethod, l_status,
sysdate, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
--raise_application_error(-20300,'Cannot insert cancel trxn into iby_trxn_summaries_all');
--SELECT iby_trxnsumm_trxnid_s.NEXTVAL
--INTO l_tid
--FROM dual;
SELECT iby_trxnsumm_mid_s.NEXTVAL
INTO l_mid
FROM dual;
INSERT INTO iby_trxn_summaries_all
(TrxnMID, TransactionID, TangibleID,
MPayeeID, PayeeID,BEPID, ECAppID, PaymentMethodName,
PayerID, PayerInstrID, Amount,CurrencyNameCode,
Status, TrxntypeID, SettleDate, ReqDate, ReqType, ReqSeq, DestUrl,
Nlslang, MBatchId, BatchId,
last_update_date, updatedate, last_updated_by, creation_date, created_by,
last_update_login, object_version_number,needsupdt)
VALUES
(l_mid, io_transactionid, i_tangibleid,
l_mpayeeid, i_payeeid, i_bepid, i_ecappid, i_pmtmethod,
i_payerid, i_instrid, i_amount, i_currency,
l_status, i_trxntypeid, i_settledate, sysdate, i_reqtype,
i_reqseq,i_desturl,
i_nlslang, l_mbatchid, batchid,
sysdate, sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
INSERT INTO iby_trxn_extended
(TRXNMID, SplitId, PrevSplitId, SubAuthInd,
BatchSeqNum, TerminalId,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, object_version_number)
VALUES
(l_mid, splitid, prevsplitid, subauthind,
batchseqnum, terminalid,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
INSERT INTO iby_trxn_core
(TrxnMID, Authtype,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, object_version_number)
VALUES
(l_mid, i_authtype,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
SELECT iby_batches_s.NEXTVAL
INTO l_mbatchid
FROM dual;
INSERT INTO iby_batches_all
(MBatchID, BatchID, MPayeeID, PayeeID, BEPID, ECAppID,
ReqType, ReqDate, DestUrl, PaymentMethodName,
Nlslang, TerminalID,
last_update_date, last_updated_by, creation_date, created_by,
last_update_login, object_version_number)
VALUES
(l_mbatchid, i_batchid, l_mpayeeid, i_payeeid, i_bepid, i_ecappid,
i_reqtype, sysdate, i_desturl, i_pmtmethod,
i_nlslang, i_terminalid,
sysdate, fnd_global.user_id, sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
SELECT count(*)
INTO l_cnt
FROM iby_trxn_summaries_all
WHERE payeeId = i_payeeid
AND tangibleid = i_tangibleid
AND UPPER(reqType) = UPPER(i_reqtype)
AND bepid = i_bepid
--AND ( status = 11 or status = 0 );
SELECT count(*)
INTO l_cnt
FROM iby_trxn_summaries_all a, iby_trxn_core b
WHERE payeeId = i_payeeid
AND tangibleid = i_tangibleid
AND UPPER(reqType) = 'ORAPMTREQ'
AND bepid = i_bepid
AND a.trxnmid = b.trxnmid
AND b.authtype = 'AUTHANDCAPTURE'
AND ( status = 11); -- didn't need status 0
SELECT count(*)
INTO l_cnt
FROM iby_batches_all
WHERE payeeId = i_payeeId
AND batchId = i_batchid
AND UPPER(reqType) = UPPER(i_reqType)
AND (batchstatus = 11 or batchstatus = 0);
SELECT instrid
FROM iby_creditcard_v
WHERE instrid = ci_instrid;
SELECT instrid
FROM iby_purchasecard_v
WHERE instrid = ci_instrid;