[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ccnumber,
expirydate,
accttype
from iby_creditcard_v c,
iby_trxn_summaries_all d
where d.payerinstrid = cin_payerinstrid
and d.payerinstrid = c.instrid;
select b.bank_party_id,
b.branch_party_id,
b.ext_bank_account_id,
b.bank_account_type
from iby_ext_bank_accounts_v b,
iby_trxn_summaries_all d
where d.payeeid = cin_payeeid
and d.payeeinstrid = b.ext_bank_account_id;
select acctno,
refinfo,
memo,
currencynamecode
from iby_tangible t
where t.tangibleid = cin_tangibleid;
select b.bank_party_id,
b.branch_party_id,
b.ext_bank_account_id,
b.bank_account_type
from iby_ext_bank_accounts_v b,
iby_trxn_summaries_all d
where d.payerinstrid = cin_payerinstrid
and d.payerinstrid = b.ext_bank_account_id;
select b.bank_party_id,
b.branch_party_id,
b.ext_bank_account_id,
b.bank_account_type
from iby_ext_bank_accounts_v b,
iby_trxn_summaries_all d
where d.payeeid = cin_payeeid
and d.payeeinstrid = b.ext_bank_account_id;
select acctno,
refinfo,
memo,
currencynamecode
from iby_tangible t
where t.tangibleid = cin_tangibleid;
procedure update_ecapp is
TYPE txn_mid_TabTyp is TABLE OF iby_trans_core_v.trxnmid%TYPE
INDEX BY BINARY_INTEGER;
updatedt_Tab JTF_DATE_TABLE;
l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_SCHED.update_ecapp';
SELECT distinct(a.ECAPPID),
a.APPLICATION_SHORT_NAME FROM IBY_ECAPP_V a
WHERE EXISTS
(SELECT * FROM dba_objects b WHERE
b.object_name = a.application_short_name || '_ECAPP_PKG' and
owner=cin_owner);
SELECT /*+ NO_EXPAND use_nl(B C) INDEX (C IBY_TRXN_CORE_TRXNMID_U1)*/
b.transactionid,
b.status,
b.updatedate,
b.reqtype,
c.referencecode,
b.trxnmid
FROM iby_trxn_summaries_all b,
iby_trxn_core c
WHERE(b.trxnmid = c.trxnmid)
AND b.reqtype <> 'ORAPMTREQ'
AND b.status not in (1, 0, 11, 100, 111)
AND b.needsupdt IN ('Y','F')
AND b.ecappid = DECODE(cin_ecappid, '222', b.ecappid,cin_ecappid);
SELECT iby_trans_fi_v.TRANSACTIONID,
iby_trans_fi_v.STATUS,
iby_trans_fi_v.UPDATEDATE,
iby_trans_fi_v.REQTYPE,
iby_trans_fi_v.REFERENCECODE,
iby_trans_fi_v.TRXNMID
FROM iby_trans_fi_v
WHERE iby_trans_fi_v.needsupdt IN ('Y','F')
AND iby_trans_fi_v.reqtype <> 'ORAPMTREQ'
AND iby_trans_fi_v.status not in (1, 0, 11, 100, 111)
AND iby_trans_fi_v.ecappid = cin_ecappid;
SELECT iby_trans_bankacct_v.TRANSACTIONID,
iby_trans_bankacct_v.STATUS,
iby_trans_bankacct_v.UPDATEDATE,
iby_trans_bankacct_v.REQTYPE,
iby_trans_bankacct_v.REFERENCECODE,
iby_trans_bankacct_v.TRXNMID
FROM iby_trans_bankacct_v
WHERE iby_trans_bankacct_v.needsupdt IN ('Y','F')
AND iby_trans_bankacct_v.reqtype <> 'ORAPMTREQ'
AND iby_trans_bankacct_v.status <> 0
AND iby_trans_bankacct_v.ecappid = cin_ecappid;
SELECT trxn.TRANSACTIONID,
trxn.STATUS,
trxn.UPDATEDATE,
trxn.REQTYPE,
trxn.TRXNMID
FROM iby_trxn_summaries_all trxn,
iby_pmtschemes scheme,
iby_bepkeys bep
WHERE trxn.needsupdt IN ('Y','F')
AND trxn.bepid = scheme.bepid
AND trxn.bepid = bep.bepid
AND scheme.pmtschemename = 'BANKACCOUNT'
AND trxn.reqtype <> 'ORAPMTREQ'
AND trxn.status not in (1, 0, 11, 100, 111)
AND trxn.ecappid = cin_ecappid;
SELECT iby_trans_pcard_v.TRANSACTIONID,
iby_trans_pcard_v.STATUS,
iby_trans_pcard_v.UPDATEDATE,
iby_trans_pcard_v.REQTYPE,
iby_trans_pcard_v.REFERENCECODE,
iby_trans_pcard_v.TRXNMID
FROM iby_trans_pcard_v
WHERE iby_trans_pcard_v.needsupdt IN ('Y','F')
AND iby_trans_pcard_v.reqtype <> 'ORAPMTREQ'
AND iby_trans_pcard_v.status not in (1, 0, 11, 100, 111)
AND iby_trans_pcard_v.ecappid = cin_ecappid;
select oracle_username into l_object_owner
from fnd_oracle_userid
where read_only_flag = 'U';
updatedt_Tab := JTF_DATE_TABLE();
updatedt_Tab.extend(extendRows);
updatedt_Tab(l_recordCounter) := r_trans_core.UPDATEDATE;
updatedt_Tab.extend(extendRows);
updatedt_Tab(l_recordCounter) := r_trans_fi.UPDATEDATE;
updatedt_Tab.extend(extendRows);
updatedt_Tab(l_recordCounter) := r_trans_bankacct.UPDATEDATE;
updatedt_Tab.extend(extendRows);
updatedt_Tab(l_recordCounter) := r_trans_pcard.UPDATEDATE;
* ecapp_pkg.update_status() method does not exist.
*
* Fix for bug 3883880 - rameshsh
*/
IF(l_ecappTab.COUNT>0) THEN
FOR j in l_ecappTab.FIRST..l_ecappTab.LAST LOOP
l_ecapp_rec:=l_ecappTab(j);
v_procString := 'BEGIN '|| l_ecapp_rec.app_short_name || '_ecapp_pkg.update_status( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10); END; ';
iby_debug_pub.add('Invoking update_status Procedure for the application:' ||
l_ecapp_rec.app_short_name,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
IN updatedt_Tab,
IN refcode_Tab,
OUT o_status,
OUT o_errcode,
OUT o_errmsg,
IN OUT o_statusindiv_Tab;
* exist in iby_ecapp_v, or that the procedure ecapp_pkg.update_status
* does not exist. Both these are ok. Swallow the exception and
* all procedure to exit gracefully. Fix for bug 3883880.
*/
--iby_debug_pub.add('Exception Occurred: Either the ecapp name does not exist ||
--|| in iby_ecapp_v, or that the procedure ecapp_pkg.update_status does not exist'|| sqlerrm ,
--iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
iby_debug_pub.add('update_status has been executed for :'||
l_ecapp_rec.app_short_name ,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
UPDATE iby_trxn_summaries_all
SET NeedsUpdt = DECODE(upper(o_statusindiv_Tab(j)), 'TRUE', 'N', 'F')
WHERE trxnmid = txn_mid_Tab(j);
UPDATE iby_trxn_summaries_all
SET NeedsUpdt = 'F'
WHERE trxnmid = txn_mid_Tab(k);
end update_ecapp;
procedure update_ecapp (in_ecappid in iby_ecapp.ecappid%type)
is
TYPE txn_mid_TabTyp is TABLE OF iby_trans_core_v.trxnmid%TYPE
INDEX BY BINARY_INTEGER;
updatedt_Tab JTF_DATE_TABLE;
l_dbg_mod VARCHAR2(100) := 'iby.plsql.IBY_SCHED.update_ecapp';
SELECT iby_trans_core_v.TRANSACTIONID,
iby_trans_core_v.STATUS,
iby_trans_core_v.UPDATEDATE,
iby_trans_core_v.REQTYPE,
iby_trans_core_v.REFERENCECODE,
iby_trans_core_v.TRXNMID
FROM iby_trans_core_v
WHERE iby_trans_core_v.needsupdt IN ('Y','F')
--AND iby_trans_core_v.ecappid = DECODE(cin_ecappid, '222', cin_ecappid, iby_trans_core_v.ecappid);
SELECT iby_trans_fi_v.TRANSACTIONID,
iby_trans_fi_v.STATUS,
iby_trans_fi_v.UPDATEDATE,
iby_trans_fi_v.REQTYPE,
iby_trans_fi_v.REFERENCECODE,
iby_trans_fi_v.TRXNMID
FROM iby_trans_fi_v
WHERE iby_trans_fi_v.needsupdt IN ('Y','F')
AND iby_trans_fi_v.ecappid = cin_ecappid;
SELECT iby_trans_bankacct_v.TRANSACTIONID,
iby_trans_bankacct_v.STATUS,
iby_trans_bankacct_v.UPDATEDATE,
iby_trans_bankacct_v.REQTYPE,
iby_trans_bankacct_v.REFERENCECODE,
iby_trans_bankacct_v.TRXNMID
FROM iby_trans_bankacct_v
WHERE iby_trans_bankacct_v.needsupdt IN ('Y','F')
AND iby_trans_bankacct_v.ecappid = cin_ecappid;
SELECT iby_trans_pcard_v.TRANSACTIONID,
iby_trans_pcard_v.STATUS,
iby_trans_pcard_v.UPDATEDATE,
iby_trans_pcard_v.REQTYPE,
iby_trans_pcard_v.REFERENCECODE,
iby_trans_pcard_v.TRXNMID
FROM iby_trans_pcard_v
WHERE iby_trans_pcard_v.needsupdt IN ('Y','F')
AND iby_trans_pcard_v.ecappid = cin_ecappid;
updatedt_Tab := JTF_DATE_TABLE();
updatedt_Tab.extend(extendRows);
updatedt_Tab(i) := r_trans_core.UPDATEDATE;
updatedt_Tab.extend(extendRows);
updatedt_Tab(i) := r_trans_fi.UPDATEDATE;
updatedt_Tab.extend(extendRows);
updatedt_Tab(i) := r_trans_bankacct.UPDATEDATE;
updatedt_Tab.extend(extendRows);
updatedt_Tab(i) := r_trans_pcard.UPDATEDATE;
* ecapp_pkg.update_status() method does not exist.
*
* Fix for bug 3883880 - rameshsh
*/
BEGIN
-- Now getting the application short name
iby_debug_pub.add('Enter update_ecapp:',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
SELECT distinct(application_short_name)
INTO ecapp_name
FROM iby_ecapp_v
WHERE ecappid = in_ecappid;
v_procString := 'BEGIN '|| ecapp_name || '_ecapp_pkg.update_status( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10); END; ';
iby_debug_pub.add('Invoking update_status Procedure for the application:' ||
ecapp_name,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
IN updatedt_Tab,
IN refcode_Tab,
OUT o_status,
OUT o_errcode,
OUT o_errmsg,
IN OUT o_statusindiv_Tab;
iby_debug_pub.add('update_status has been executed for :'||
ecapp_name ,iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
UPDATE iby_trxn_summaries_all
SET NeedsUpdt = DECODE(upper(o_statusindiv_Tab(j)), 'TRUE', 'N', 'F')
WHERE trxnmid = txn_mid_Tab(j);
* exist in iby_ecapp_v, or that the procedure ecapp_pkg.update_status
* does not exist. Both these are ok. Swallow the exception and
* all procedure to exit gracefully. Fix for bug 3883880.
*/
--iby_debug_pub.add('Exception Occurred: Either the ecapp name does not exist ||
--|| in iby_ecapp_v, or that the procedure ecapp_pkg.update_status does not exist'|| sqlerrm ,
--iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
UPDATE iby_trxn_summaries_all
SET NeedsUpdt = 'F'
WHERE trxnmid = txn_mid_Tab(k);
end update_ecapp;
return number -- nonzero if rows were updated.
is
begin
update iby_trxn_summaries_all
set status = decode(in_pmtprcst, 'PAID', 0, 'UNPAID', 17, 'FAILED',
16, 'PAYFAILED', 16, 16),
updatedate = to_date(in_dtpmtprc,'YYYYMMDD'), needsupdt = 'Y'
where trxnmid in
(select trxnmid from iby_trxn_fi where psreqid = in_psreqid);
update iby_trxn_fi set srvid = in_srvrid, referencecode = in_refinfo
where psreqid = in_psreqid;
procedure update_trxn_status( i_unchanged_status IN NUMBER,
i_numTrxns IN NUMBER,
i_status_arr IN JTF_NUMBER_TABLE,
i_errLoc_arr IN JTF_NUMBER_TABLE,
i_errCode_arr IN JTF_VARCHAR2_TABLE_100,
i_errMsg_arr IN JTF_VARCHAR2_TABLE_300,
i_tangibleId_arr IN JTF_VARCHAR2_TABLE_100,
i_trxnMId_arr IN JTF_NUMBER_TABLE,
i_srvrId_arr IN JTF_VARCHAR2_TABLE_100,
i_refCode_arr IN JTF_VARCHAR2_TABLE_100,
i_auxMsg_arr IN JTF_VARCHAR2_TABLE_300,
i_fee_arr IN JTF_NUMBER_TABLE,
o_status_arr OUT NOCOPY JTF_NUMBER_TABLE,
o_error_code OUT NOCOPY NUMBER,
o_error_msg OUT NOCOPY VARCHAR2
)
IS
l_index INTEGER;
UPDATE iby_trxn_summaries_all
SET status = decode( l_status, i_unchanged_status, status, l_status),
errorlocation = i_errLoc_arr( l_index ),
BEPCode = i_errCode_arr( l_index ),
BEPMessage = i_errMsg_arr( l_index ),
needsupdt = 'Y'
WHERE TANGIBLEID = i_tangibleId_arr( l_index )
AND status <> -99;
UPDATE iby_trxn_fi
SET referencecode = i_refCode_arr( l_index ),
srvId = i_srvrId_arr( l_index ),
AUXMSG = i_auxMsg_arr( l_index ),
PROCESSFEE = i_fee_arr( l_index )
WHERE TRXNMID = i_trxnMId_arr( l_index );
END update_trxn_status;