[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ext_bank_account_id
FROM iby_ext_bank_accounts
WHERE ext_bank_account_id = ci_instrid;
select iby_trxnsumm_mid_s.nextval
from dual;
select proc_reference_code, amount
from iby_trxn_summaries_all
where tangibleid= ci_tangible_id
--and trxntypeid=20
--and status=0
order by trxnmid desc;
select trxnmid, mtangibleid
from iby_trxn_summaries_all
where tangibleid=i_tangibleid
and payeeid=i_payeeid
and bepid=i_bepid
and bepkey=i_bepkey
and trxntypeid is null
order by trxnmid desc;
* insert for transaction status.
*/
SELECT NVL(bep_type,iby_bepinfo_pkg.C_BEPTYPE_GATEWAY)
INTO l_beptype
FROM IBY_BEPINFO
WHERE (bepid=i_bepid);
SELECT
hz.account_name
INTO
l_settle_cust_ref
FROM
HZ_CUST_ACCOUNTS hz
WHERE
hz.cust_account_id = i_custacctid
;
SELECT
COUNT(*)
INTO
l_cnt
FROM
IBY_TRXN_SUMMARIES_ALL trxn
WHERE
trxn.mpayeeid = l_mpayeeid AND
trxn.instrtype = 'BANKACCOUNT' AND
trxn.instrsubtype = 'ACH' AND
trxn.payerid = i_payerid
;
insert into iby_trxn_summaries_all
( trxnMId, transactionId, tangibleid,
mpayeeid, payeeid, payeeinstrid,
bepid, bepkey, ecappid, org_id, org_type,
paymentMethodname, payerid, payerinstrid,
amount, currencyNameCode, reqdate,
reqtype, status, settledate,
mtangibleId, nlslang, instrtype, instrsubtype,
last_update_date, updatedate, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,
proc_reference_code, proc_reference_amount,
cust_account_id, acct_site_id, acct_site_use_id,
settlement_customer_reference, first_trxn_flag,
process_profile_code, trxntypeid,needsupdt
)
values ( l_tmid, l_tid, i_tangibleid, l_mpayeeid,
i_payeeid, i_bnfcryinstrid, i_bepid,
i_bepkey, i_ecappid, i_org_id, i_org_type, i_pmtMethod,
i_payerid, i_instrid, i_billamount,
i_billcurDef, i_reqdate, l_reqtype,
l_status, i_scheddate, l_mtangibleid, i_nlslang,
'BANKACCOUNT', 'ACH',
sysdate, sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1, l_reference_code, l_reference_amount,
i_custacctid, i_acctsiteid, i_acctsiteuseid,
l_settle_cust_ref, l_first_trx_flag, i_profilecode,
l_trxntypeid,'Y'
);
insert into iby_trxn_fi
(trxnMid, psreqid, splitId,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
values ( l_tmid, i_psreqid, i_splitId,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
UPDATE iby_trxn_summaries_all
SET
org_id = i_org_id,
ecappid = i_ecappid,
payeeid = i_payeeid,
bepid = i_bepid,
bepkey = i_bepkey,
paymentMethodname = i_pmtmethod,
payerid = i_payerid,
payerinstrid = i_instrid,
amount = i_billamount,
currencyNameCode = i_billcurDef,
status = l_status,
cust_account_id = i_custacctid,
acct_site_id = i_acctsiteid,
acct_site_use_id = i_acctsiteuseid,
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
creation_date = sysdate,
created_by = fnd_global.user_id,
last_update_login = fnd_global.user_id,
object_version_number = object_version_number+1,
settlement_customer_reference = l_settle_cust_ref,
first_trxn_flag = l_first_trx_flag
WHERE trxnmid = l_old_tmid;
select count(*) into l_cnt
from iby_trxn_summaries_all s
where transactionId = i_tid
and reqType = i_reqType;
select count(*) into l_cnt
from iby_trxn_summaries_all s
where payeeId = i_payeeId
and tangibleid = i_tangibleId
and UPPER(reqType) = UPPER(i_reqType)
and trxntypeid not in (20);
select count(*) into l_cnt
from iby_trxn_summaries_all s
where transactionId = i_tid
and (( (reqtype = 'ORAPMTREQ' or reqtype = 'ORAPMTCREDIT') and (status <> -99 and status <> 100)) or
(reqtype = 'ORAPMTCANC'));
select iby_trxnsumm_mid_s.nextval
from dual;
select mtangibleid , tangibleid
from iby_trxn_summaries_all
where transactionId = ci_tid
group by mtangibleid , tangibleid;
select reqtype into l_reqtype from iby_trxn_summaries_all
WHERE transactionId = i_tid and status = 100
and (reqtype = 'ORAPMTREQ' or reqtype = 'ORAPMTCREDIT');
** update the transactions table with the new data.
*/
update iby_trxn_summaries_all
set status = -99,
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 transactionId = i_tid
and (reqtype = 'ORAPMTREQ' or reqtype = 'ORAPMTCREDIT')
and status = 100;
select splitId into l_splitId
from iby_trxn_fi
where trxnmid in ( select trxnmid
from iby_trxn_summaries_all
where transactionId = i_tid)
and rownum < 2;
insert into iby_trxn_summaries_all
( trxnMId, transactionId, tangibleid,
mpayeeid, payeeid, bepid, bepkey, ecappid,
org_id, paymentMethodname,
payerid, payerinstrid, amount,
currencyNameCode,
reqdate, reqtype, status, settledate,
mtangibleId, nlslang, instrtype,
last_update_date, updatedate, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,needsupdt)
values ( l_tmid, i_tid, i_tangibleid, l_mpayeeid, i_payeeid,
i_bepid, i_bepkey, i_ecappid, i_org_id, i_pmtMethod,
i_payerid, i_instrid,
i_billamount, i_billcurDef, i_reqdate,
l_reqtype, 100, i_scheddate, l_mtangibleid, i_nlslang,
'BANKACCOUNT',
sysdate, sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y');
** After inserting an entry in transactions_summaries_all table,
** make an entry in the iby_trxn_fi table, corresponding to
** the transaction master id.
*/
insert into iby_trxn_fi
(trxnMid, psreqid, splitId,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
values ( l_tmid, i_psreqid, l_splitId,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1);
** Procedure: deletePayment.
** Purpose: Marks the payment whose id matches the tid passed as
** 'CANCELLED'.
*/
-- procedure deletePayment(i_ecappid in iby_ecapp.ecappid%type,
procedure deletePayment( i_tid in iby_trxn_summaries_all.transactionId%type )
is
l_old_tmid iby_trxn_summaries_all.trxnmid%type;
select iby_trxnsumm_mid_s.nextval
from dual;
select trxnmid , status
from iby_trxn_summaries_all s
where transactionid=ci_tid
and status = 100
-- // status for pending.
and (reqtype = 'ORAPMTREQ' or reqtype = 'ORAPMTCREDIT');
** update the transactions table with the new data.
*/
update iby_trxn_summaries_all
set status = -99,
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 transactionId = i_tid
and (reqtype = 'ORAPMTREQ' or reqtype = 'ORAPMTCREDIT');
** Insert record, with values that of latest record and increment the
** tid value by 1.
*/
insert into iby_trxn_summaries_all
( trxnMId, transactionId, tangibleid,
mpayeeid, payeeid, bepid, ecappid,
org_id, paymentMethodname,
payerid, payerinstrid,
amount, currencyNameCode,
reqdate, reqtype, status,
mtangibleId, nlslang, instrtype,
last_update_date, updatedate, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,needsupdt)
select l_new_tmid, transactionId, tangibleid,
mpayeeid, payeeid, bepid, ecappid,
org_id, paymentMethodname,
payerid, payerinstrid,
amount, currencyNameCode,
sysdate, 'ORAPMTCANC', 14,
mtangibleId, nlslang, 'BANKACCOUNT',
sysdate, sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y'
from iby_trxn_summaries_all
where trxnmid = l_old_tmid;
insert into iby_trxn_fi
(trxnMid, psreqid, splitId,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
select l_new_tmid, psreqid, splitId,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1
from iby_trxn_fi
where trxnmid = l_old_tmid;
end deletePayment;
update iby_trxn_summaries_all
set status=i_status
where trxnmid=i_tmid;
select iby_trxnsumm_mid_s.nextval
from dual;
SELECT 'Y', trxnmid
FROM iby_trxn_summaries_all s
WHERE transactionid=i_tid
AND trxntypeid=5
AND status <> '0'
ORDER BY reqdate desc;
select trxnmid , status, proc_reference_code
from iby_trxn_summaries_all s
where transactionid=ci_tid
and status in ( 0, 100)
-- // status for pending or success.
and (reqtype = 'ORAPMTREQ')
and trxntypeid is null;
update iby_trxn_summaries_all
set currencynamecode=i_currencycode,
amount=i_amount,
proc_reference_code=l_reference_code,
last_update_date = sysdate,
updatedate = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.user_id,
object_version_number = 1
where trxnmid=l_new_tmid;
** Insert record, with values that of latest record and increment the
** tid value by 1.
*/
insert into iby_trxn_summaries_all
( trxnMId, transactionId, tangibleid,
mpayeeid, payeeid, bepid, ecappid,
org_id, paymentMethodname,
payerid, payerinstrid,
amount, currencyNameCode,
reqdate, reqtype, status,
mtangibleId, nlslang, instrtype,
last_update_date, updatedate, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,
proc_reference_code, proc_reference_amount, trxntypeid, bepkey,needsupdt
,payment_channel_code,settledate,settlement_due_date)
select l_new_tmid, transactionId, tangibleid,
mpayeeid, payeeid, bepid, ecappid,
org_id, paymentMethodname,
payerid, payerinstrid,
i_amount, i_currencycode,
sysdate, 'ORAPMTRETURN', 9,
mtangibleId, nlslang, 'BANKACCOUNT',
sysdate, sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1,
proc_reference_code, amount, 5, bepkey,'Y'
, payment_channel_code,settledate,settlement_due_date
from iby_trxn_summaries_all
where trxnmid = l_old_tmid;
insert into iby_trxn_fi
(trxnMid, psreqid, splitId,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number)
select l_new_tmid, psreqid, splitId,
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1
from iby_trxn_fi
where trxnmid = l_old_tmid;
* Procedure: updateTrxn
* Purpose: Update the transaction status with the return results
*
*/
procedure updateTrxn(
i_trxnmid in iby_trxn_summaries_all.trxnmid%type,
i_status in iby_trxn_summaries_all.status%type,
i_bepcode IN iby_trxn_summaries_all.BEPCode%TYPE,
i_bepmessage IN iby_trxn_summaries_all.BEPMessage%TYPE,
i_errorlocation IN iby_trxn_summaries_all.errorlocation%TYPE,
i_referenceCode IN iby_trxn_summaries_all.proc_reference_code%TYPE)
is
begin
UPDATE iby_trxn_summaries_all
SET
status = i_status,
bepcode = i_bepcode,
bepmessage = i_bepmessage,
errorlocation = i_errorlocation,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.user_id,
object_version_number = 1,
proc_reference_code = i_referencecode
WHERE trxnmid = i_trxnmid;
end updateTrxn;