[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select_clause VARCHAR(4000);
select_clause := 'SELECT TRXNMID
, org_id
, legal_entity_id
, payeeinstrid
, currencynamecode
, settledate
, category_purpose
, seq_type
, service_level
, localinstr
, bank_charge_bearer_code'
;
print_debuginfo(l_module_name, 'select_clause: '
|| select_clause);
EXECUTE IMMEDIATE select_clause
|| from_clause
|| where_clause
|| order_clause
BULK COLLECT INTO t_trxnmid
,t_org_id
,t_legal_entity_id
,t_payeeinstrid
,t_currencynamecode
,t_settledate
,t_category_purpose
,t_seq_type
,t_service_level
,t_localinstr
,t_bank_charge_bearer_code
;
UPDATE IBY_TRXN_SUMMARIES_ALL
SET logical_group_reference = t_logical_group_reference(j)
WHERE trxnmid = t_trxnmid(j);
* transaction which are due. If there is, it will insert a row into
* the iby_batches_all table to keep track of the batch status, and
* change the transactions status to other status. So, the open
* transactions will be sent as part of future batch close. Also, it
* will not allow any modification and cancellation to these transactions.
*/
PROCEDURE createBatchCloseTrxns(
merch_batchid_in IN VARCHAR2,
merchant_id_in IN VARCHAR2,
vendor_id_in IN NUMBER,
vendor_key_in IN VARCHAR2,
newstatus_in IN NUMBER,
oldstatus_in IN NUMBER,
batchstate_in IN NUMBER,
settlement_date_in IN DATE,
req_type_in IN VARCHAR2,
numtrxns_out OUT NOCOPY NUMBER
)
IS
numrows NUMBER;
SELECT
COUNT(*)
INTO
numtrxns_out
FROM
iby_trxn_summaries_all
WHERE
status = oldstatus_in AND
payeeid = merchant_id_in AND
bepid = vendor_id_in AND
bepkey = vendor_key_in AND
batchid IS NULL AND
trunc(settledate) <= trunc(settlement_date_in) AND
instrtype = 'BANKACCOUNT';
SELECT
iby_batches_s.NEXTVAL
INTO
l_mbatchid
FROM
DUAL;
INSERT INTO iby_batches_all
(MBATCHID,
BATCHID,
MPAYEEID,
PAYEEID,
BEPID,
BEPKEY,
BATCHSTATUS,
BATCHSTATEID,
BATCHCLOSEDATE,
REQTYPE,
REQDATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
SENTCOUNTER,
SENTCOUNTERDAILY
,settledate )
VALUES
(
l_mbatchid,
merch_batchid_in,
l_mpayeeid,
merchant_id_in,
vendor_id_in,
vendor_key_in,
batchstate_in,
batchstate_in,
settlement_date_in,
req_type_in,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
0,
0,
0
,sysdate);
UPDATE
IBY_TRXN_SUMMARIES_ALL
SET
status = newstatus_in,
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
status = oldstatus_in AND
payeeid = merchant_id_in AND
bepid = vendor_id_in AND
bepkey = vendor_key_in AND
batchid IS NULL AND
trunc(settledate) <= trunc(settlement_date_in) AND
instrtype = 'BANKACCOUNT'
;
* transaction which are due. If there is, it will insert a row into
* the iby_batches_all table to keep track of the batch status, and
* change the transactions status to other status. So, the open
* transactions will be sent as part of future batch close. Also, it
* will not allow any modification and cancellation to these transactions.
*/
PROCEDURE createBatchCloseTrxnsNew(
merch_batchid_in IN VARCHAR2,
profile_code_in IN iby_batches_all.
process_profile_code%TYPE,
merchant_id_in IN VARCHAR2,
vendor_id_in IN NUMBER,
vendor_key_in IN VARCHAR2,
newstatus_in IN NUMBER,
oldstatus_in IN NUMBER,
batchstate_in IN NUMBER,
settlement_date_in IN DATE,
req_type_in IN VARCHAR2,
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,
numtrxns_out OUT NOCOPY NUMBER,
mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
)
IS
numrows NUMBER;
SELECT
COUNT(*)
INTO
numtrxns_out
FROM
iby_trxn_summaries_all
WHERE
status = oldstatus_in AND
payeeid = merchant_id_in AND
bepid = vendor_id_in AND
bepkey = vendor_key_in AND
batchid IS NULL AND
trunc(nvl(settledate, sysdate)) <= trunc(nvl(settlement_date_in, sysdate-1)) AND
instrtype = 'BANKACCOUNT';
SELECT sp.funds_xfer_format_code
INTO l_system_prof_code
FROM iby_fndcpt_user_eft_pf_b up
,iby_fndcpt_sys_eft_pf_b sp
WHERE up.sys_eft_profile_code = sp.sys_eft_profile_code
AND up.user_eft_profile_code = profile_code_in;
SELECT
iby_batches_s.NEXTVAL
INTO
l_mbatchid
FROM
DUAL;
print_debuginfo(l_module_name, 'Going to insert batch '
|| l_batches_tab(i).mbatch_id);
INSERT INTO iby_batches_all
(MBATCHID,
BATCHID,
MPAYEEID,
PAYEEID,
BEPID,
BEPKEY,
BATCHSTATUS,
BATCHSTATEID,
BATCHCLOSEDATE,
REQTYPE,
REQDATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
SENTCOUNTER,
SENTCOUNTERDAILY,
PROCESS_PROFILE_CODE,
INSTRUMENT_TYPE,
BR_DISPUTED_FLAG,
CURRENCYNAMECODE,
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,
batchstate_in,
batchstate_in,
settlement_date_in,
req_type_in,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
0,
0,
0,
l_batches_tab(i).profile_code,
instr_type_in,
br_disputed_flag_in,
/*
* Fix for bug 5614670:
*
* Populate the batch related attributes
* created after grouping in this
* insert.
*/
l_batches_tab(i).curr_code,
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 batch '
|| l_batches_tab(i).mbatch_id);
print_debuginfo(l_module_name, 'Going to update transaction '
|| l_trxns_in_batch_tab(i).trxn_id);
UPDATE
IBY_TRXN_SUMMARIES_ALL
SET
status = newstatus_in,
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
;
* transaction which are due. If there is, it will insert a row into
* the iby_batches_all table to keep track of the batch status, and
* change the transactions status to other status. So, the open
* transactions will be sent as part of future batch close. Also, it
* will not allow any modification and cancellation to these transactions.
*/
PROCEDURE createBatchCloseTrxnsNew(
merch_batchid_in IN VARCHAR2,
profile_code_array IN JTF_VARCHAR2_TABLE_100,
merchant_id_in IN VARCHAR2,
vendor_id_in IN NUMBER,
vendor_key_in IN VARCHAR2,
newstatus_in IN NUMBER,
oldstatus_in IN NUMBER,
batchstate_in IN NUMBER,
settlement_date_in IN DATE,
req_type_in IN VARCHAR2,
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,
numtrxns_out OUT NOCOPY NUMBER,
mbatch_ids_out OUT NOCOPY JTF_NUMBER_TABLE,
batch_ids_out OUT NOCOPY JTF_VARCHAR2_TABLE_100
)
IS
numrows NUMBER;
/* SELECT
COUNT(*)
INTO
numtrxns_out
FROM
iby_trxn_summaries_all
WHERE
status = oldstatus_in AND
payeeid = merchant_id_in AND
bepid = vendor_id_in AND
bepkey = vendor_key_in AND
batchid IS NULL AND
trunc(nvl(settledate, sysdate)) <= trunc(nvl(settlement_date_in, sysdate-1)) AND
instrtype = 'BANKACCOUNT';
l_cursor_stmt := ' SELECT COUNT(*) FROM '||
' iby_trxn_summaries_all WHERE '||
' status = '||oldstatus_in||' AND '||
' payeeid = '''||merchant_id_in||''' AND '||
' bepid = '||vendor_id_in||' AND '||
' process_profile_code IN ('||strProfCodes||') AND '||
' batchid IS NULL AND '||
-- bug 8238335
-- ' trunc(nvl(settledate, sysdate)) <= trunc(nvl(to_date('''||settlement_date_in||'''), sysdate-1)) AND '||
' instrtype = ''BANKACCOUNT'' '
;
SELECT sp.funds_xfer_format_code
INTO l_system_prof_code
FROM iby_fndcpt_user_eft_pf_b up
,iby_fndcpt_sys_eft_pf_b sp
WHERE up.sys_eft_profile_code = sp.sys_eft_profile_code
AND up.user_eft_profile_code = profile_code_array(1);
SELECT
iby_batches_s.NEXTVAL
INTO
l_mbatchid
FROM
DUAL;
print_debuginfo(l_module_name, 'Going to insert batch '
|| l_batches_tab(i).mbatch_id);
* Modified to insert null values for bepkey, currency and
* profile code columns since these could have multiple values
* for a batch.
*/
IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
print_debuginfo(l_module_name, 'l_batches_tab(i).trxn_count '
|| l_batches_tab(i).trxn_count );
INSERT INTO iby_batches_all
(MBATCHID,
BATCHID,
MPAYEEID,
PAYEEID,
BEPID,
BEPKEY,
BATCHSTATUS,
BATCHSTATEID,
BATCHCLOSEDATE,
REQTYPE,
REQDATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
SENTCOUNTER,
SENTCOUNTERDAILY,
PROCESS_PROFILE_CODE,
INSTRUMENT_TYPE,
BR_DISPUTED_FLAG,
CURRENCYNAMECODE,
PAYEEINSTRID,
LEGAL_ENTITY_ID,
ORG_ID,
ORG_TYPE,
SETTLEDATE,
NUMTRXNS
)
VALUES
(
l_batches_tab(i).mbatch_id,
merch_batchid_in || '_' || i,
l_mpayeeid,
merchant_id_in,
vendor_id_in,
null, -- l_batches_tab(i).bep_key
batchstate_in,
batchstate_in,
settlement_date_in,
req_type_in,
sysdate,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
0,
0,
0,
--l_batches_tab(i).profile_code
profile_code_array(1),
instr_type_in,
br_disputed_flag_in,
/*
* Fix for bug 5614670:
*
* Populate the batch related attributes
* created after grouping in this
* insert.
*/
l_batches_tab(i).curr_code,
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,
l_batches_tab(i).trxn_count
);
print_debuginfo(l_module_name, 'Finished insert batch '
|| l_batches_tab(i).mbatch_id);
print_debuginfo(l_module_name, 'Going to update transaction '
|| l_trxns_in_batch_tab(i).trxn_id);
UPDATE
IBY_TRXN_SUMMARIES_ALL
SET
status = newstatus_in,
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
;
/*Update the batch and transactions status and other infomations based on the
payeeid and batchid */
PROCEDURE updateBatchCloseTrxns(
merch_batchid_in IN VARCHAR2,
merchant_id_in IN VARCHAR2,
newstatus_in IN NUMBER,
batchstate_in IN NUMBER,
numtrxns_in IN NUMBER,
batchtotal_in IN NUMBER DEFAULT null,
salestotal_in IN NUMBER DEFAULT null,
credittotal_in IN NUMBER DEFAULT null,
time_in IN DATE,
vendor_code_in IN VARCHAR2,
vendor_message_in IN VARCHAR2
)
IS
BEGIN
-- reset the OBJECT VERSION NUMBER, since we not using it
-- the purpose of this, it is to keep track the EFT batchseq number
UPDATE iby_batches_all SET
SENTCOUNTERDAILY = 0
WHERE batchid = merch_batchid_in
AND payeeid = merchant_id_in
AND trunc(LAST_UPDATE_DATE) < trunc(sysdate);
UPDATE iby_batches_all SET
BATCHSTATUS = batchstate_in,
BATCHSTATEID = batchstate_in,
NUMTRXNS = numtrxns_in,
--
-- only change these values if the incoming values are
-- non-trivial
--
BATCHTOTAL = DECODE(NVL(batchtotal_in,''),'',batchtotal,batchtotal_in),
BATCHSALES = DECODE(NVL(salestotal_in,''),'',batchsales,salestotal_in),
BATCHCREDIT = DECODE(NVL(credittotal_in,''),'',batchcredit,credittotal_in),
BATCHCLOSEDATE = time_in,
BEPCODE = vendor_code_in,
BEPMESSAGE = vendor_message_in,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
OBJECT_VERSION_NUMBER = Object_Version_Number + 1,
SENTCOUNTER = SENTCOUNTER + 1,
SENTCOUNTERDAILY = SENTCOUNTERDAILY + 1
WHERE batchid = merch_batchid_in
AND payeeid = merchant_id_in;
UPDATE iby_trxn_summaries_all
SET
STATUS = newstatus_in,
BEPCODE = vendor_code_in,
BEPMESSAGE = vendor_message_in,
LAST_UPDATE_DATE = sysdate,
UPDATEDATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id,
OBJECT_VERSION_NUMBER = object_version_number + 1
WHERE
-- 109 means STATUS_BATCH_TRANSITIONAL, 101 means STATUS_BATCH_COMM_ERROR, 120 means STATUS_BATCH_MAX_EXCEEDED
status in (109, 101, 120)
AND
batchid = merch_batchid_in
AND
payeeid = merchant_id_in
AND
instrtype = 'BANKACCOUNT';
END updateBatchCloseTrxns;
/*Update the transactions status and other informations by passed the data in as array.*/
procedure updateTrxnResultStatus(i_merch_batchid IN VARCHAR2,
i_merchant_id IN VARCHAR2,
i_status_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,
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 = i_status_arr( l_index ),
BEPCODE = i_errCode_arr( l_index ),
BEPMESSAGE = i_errMsg_arr( l_index ),
LAST_UPDATE_DATE = sysdate,
UPDATEDATE = sysdate,
LAST_UPDATED_BY = fnd_global.user_id
WHERE TANGIBLEID = i_tangibleId_arr( l_index )
AND batchid = i_merch_batchid
AND payeeid = i_merchant_id;
END updateTrxnResultStatus;
PROCEDURE insertEFTBatchTrxns(
i_ecappid IN iby_trxn_summaries_all.ecappid%TYPE,
i_payeeid IN iby_trxn_summaries_all.payeeid%TYPE,
i_ecbatchid IN iby_trxn_summaries_all.ecbatchid%TYPE,
i_bepid IN iby_trxn_summaries_all.bepid%TYPE,
i_bepkey IN iby_trxn_summaries_all.bepkey%TYPE,
i_pmtmethod IN iby_trxn_summaries_all.paymentmethodname%TYPE,
i_reqtype IN iby_trxn_summaries_all.reqtype%TYPE,
i_reqdate IN iby_trxn_summaries_all.reqdate%TYPE,
i_payeeinstrid IN iby_trxn_summaries_all.payeeinstrid%TYPE,
i_orgid IN iby_trxn_summaries_all.org_id%TYPE,
i_payerinstrid IN JTF_NUMBER_TABLE,
i_amount IN JTF_NUMBER_TABLE,
i_payerid IN JTF_VARCHAR2_TABLE_100,
i_tangibleid IN JTF_VARCHAR2_TABLE_100,
i_currency IN JTF_VARCHAR2_TABLE_100,
i_refinfo IN JTF_VARCHAR2_TABLE_100,
i_memo IN JTF_VARCHAR2_TABLE_100,
i_ordermedium IN JTF_VARCHAR2_TABLE_100,
i_eftauthmethod IN JTF_VARCHAR2_TABLE_100,
i_instrsubtype IN JTF_VARCHAR2_TABLE_100,
i_settledate IN JTF_DATE_TABLE,
i_issuedate IN JTF_DATE_TABLE,
i_customerref IN JTF_VARCHAR2_TABLE_100,
o_trxnId OUT NOCOPY JTF_NUMBER_TABLE
)
IS
l_mtangibleid JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
SELECT iby_trxnsumm_mid_s.nextval
FROM DUAL;
SELECT iby_tangible_s.nextval
FROM DUAL;
SELECT count(*) INTO l_count
FROM iby_trxn_summaries_all
WHERE ecbatchid = i_ecbatchid
AND ecappid = i_ecappid
AND payeeid = i_payeeid;
select count(*) into l_count
from iby_trxn_summaries_all s
where payeeId = i_payeeId
and tangibleid = i_tangibleId(j)
and UPPER(reqType) = UPPER(i_reqType);
INSERT INTO iby_tangible
(
mtangibleId, tangibleid, amount,
currencyNameCode, refinfo, memo, issuedate,
order_medium, eft_auth_method,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number
)
VALUES
(
l_mtangibleid(j), i_tangibleid(j), i_amount(j),
i_currency(j), i_refinfo(j), i_memo(j), i_issuedate(j),
i_ordermedium(j), i_eftauthmethod(j),
sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1
);
INSERT INTO iby_trxn_summaries_all
(
org_id, ecappid, mpayeeid, payeeid,
bepid, bepkey, paymentMethodname,
ecbatchid, trxnmid, transactionid, mtangibleId,
tangibleid, payeeinstrid, payerid, payerinstrid,
amount, currencyNameCode, reqdate,
reqtype, status, settledate, instrtype, instrsubtype,
settlement_customer_reference,
last_update_date, updatedate, last_updated_by,
creation_date, created_by,
last_update_login, object_version_number,needsupdt
)
VALUES
(
i_orgid, i_ecappid, l_mpayeeid, i_payeeid,
i_bepid, i_bepkey, i_pmtmethod, i_ecbatchid,
l_trxnmid(j), o_trxnid(j), l_mtangibleid(j),
i_tangibleid(j), i_payeeinstrid, i_payerid(j),
i_payerinstrid(j), i_amount(j), i_currency(j),
i_reqdate, i_reqtype, 100, i_settledate(j),
'BANKACCOUNT', i_instrsubtype(j),
i_customerref(j),
sysdate, sysdate, fnd_global.user_id,
sysdate, fnd_global.user_id,
fnd_global.login_id, 1,'Y'
);
UPDATE iby_tangible
set mtangibleId = l_mtangibleid(j),
amount = i_amount(j),
currencyNameCode = i_currency(j),
refinfo = i_refinfo(j),
memo = i_memo(j),
order_medium = i_ordermedium(j),
eft_auth_method = i_eftauthmethod(j),
issuedate = i_issuedate(j),
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
creation_date = sysdate,
created_by = fnd_global.user_id,
last_update_login= fnd_global.login_id,
object_version_number = 1
where tangibleid = i_tangibleid(j);
UPDATE iby_trxn_summaries_all
set org_id = i_orgid,
ecappid = i_ecappid,
mpayeeid = l_mpayeeid,
payeeid = i_payeeid,
bepid = i_bepid,
bepkey = i_bepkey,
paymentMethodname = i_pmtmethod,
ecbatchid = i_ecbatchid,
trxnmid = l_trxnmid(j),
transactionid = o_trxnid(j),
mtangibleId = l_mtangibleid(j),
payeeinstrid = i_payeeinstrid,
payerid = i_payerid(j),
payerinstrid = i_payerinstrid(j),
amount = i_amount(j),
currencyNameCode = i_currency(j),
reqdate = i_reqdate,
reqtype = i_reqtype,
status = 100,
settledate = i_settledate(j),
instrtype = 'BANKACCOUNT',
instrsubtype = i_instrsubtype(j),
settlement_customer_reference
= i_customerref(j),
bepcode = null,
bepmessage = null,
batchid = null,
mbatchid = null,
errorlocation = null,
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 = 1
where tangibleid = i_tangibleid(j);
END insertEFTBatchTrxns;
i_trxndate IN iby_trxn_summaries_all.updatedate%TYPE,
i_trxntypeid IN iby_trxn_summaries_all.TrxntypeID%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,
o_trxnid OUT NOCOPY iby_trxn_summaries_all.transactionid%TYPE,
i_orgtype IN iby_trxn_summaries_all.org_type%TYPE,
i_pmtchannelcode IN iby_trxn_summaries_all.payment_channel_code%TYPE,
i_factoredflag IN iby_trxn_summaries_all.factored_flag%TYPE,
i_pmtinstrassignmentId IN iby_trxn_summaries_all.payer_instr_assignment_id%TYPE,
i_process_profile_code IN iby_trxn_summaries_all.process_profile_code%TYPE,
o_trxnmid OUT NOCOPY iby_trxn_summaries_all.trxnmid%TYPE
) IS
l_mtangibleid iby_trxn_summaries_all.mtangibleid%TYPE;
SELECT 'Y', trxnmid, mtangibleid
FROM iby_trxn_summaries_all s
WHERE payeeId = i_payeeId
AND tangibleid = i_tangibleid
AND UPPER(reqType) = UPPER(i_reqType)
AND status <> '0'
ORDER BY trxnmid desc;
SELECT DECODE(payer_notification_format, null, 'N', 'Y')
FROM iby_fndcpt_user_eft_pf_b up, iby_fndcpt_sys_eft_pf_b sp
WHERE up.sys_eft_profile_code = sp.sys_eft_profile_code
AND up.user_eft_profile_code = i_user_fcpp_code;
select debit_auth_flag,
debit_auth_method,
debit_auth_reference
into l_debit_auth_flag,
l_debit_auth_method,
l_debit_auth_reference
from iby_pmt_instr_uses_all
where instrument_payment_use_id=i_pmtinstrassignmentId;
SELECT bep_type
INTO l_bep_type
FROM iby_bepinfo
WHERE bepid = i_bepid;
INSERT INTO iby_trxn_summaries_all(
trxnmid,
org_id,
ecappid,
mpayeeid,
payeeid,
bepid,
bepkey,
paymentMethodname,
transactionid,
mtangibleId,
tangibleid,
payeeinstrid,
payerid,
payerinstrid,
amount,
currencyNameCode,
reqtype,
status,
settledate,
instrtype,
instrsubtype,
last_update_date,
reqdate,
updatedate,
last_updated_by,
creation_date,
created_by,
last_update_login,
object_version_number,
bepcode,
bepmessage,
errorlocation,
trxntypeid,
proc_reference_code,
org_type,
payment_channel_code,
factored_flag,
payer_instr_assignment_id,
process_profile_code,
payer_party_id,
debit_auth_flag,
debit_auth_method,
debit_auth_reference,
payer_notification_required,
needsupdt
) VALUES (
iby_trxnsumm_mid_s.NEXTVAL, -- trxnmid
i_orgid, -- org_id
i_ecappid, -- ecappid
l_mpayeeid, -- mpayeeid
i_payeeid, -- payeeid
i_bepid, -- bepid
i_bepkey, -- bepkey
i_pmtmethod, -- paymentMethodname
o_trxnid, -- transactionid
l_mtangibleid, -- mtangibleId
i_tangibleid, -- tangibleid
i_payeeinstrid, -- payeeinstrid
i_payerid, -- payerid
i_payerinstrid, -- payerinstrid
i_amount, -- amount
i_currency, -- currencyNameCode
i_reqtype, -- reqtype
i_status, -- status
null, -- settledate
i_instrtype, -- instrtype
i_instrsubtype, -- instrsubtype
sysdate, -- last_update_date
i_trxndate, -- reqdate
i_trxndate, -- updatedate
fnd_global.user_id, -- last_updated_by
sysdate, -- creation_date
fnd_global.user_id, -- created_by
fnd_global.login_id, -- last_update_login
1, -- object_version_number
i_bepcode, -- bepcode
i_bepmessage, -- bepmessage
i_errorlocation, -- errorlocation
i_trxntypeid, -- trxntypeid
i_referencecode, -- reference code
i_orgtype, -- org_type
i_pmtchannelcode, -- payment_channel_code
i_factoredflag, -- factored_flag
i_pmtinstrassignmentId,
i_process_profile_code,
l_payer_party_id,
l_debit_auth_flag,
l_debit_auth_method,
l_debit_auth_reference,
DECODE(i_status, 0, l_payer_notif_flag, 'N'),
'Y'
) RETURNING trxnmid INTO l_trxnmid;
UPDATE iby_trxn_summaries_all
SET tangibleid = i_tangibleid,
org_id = i_orgid,
ecappid = i_ecappid,
mpayeeid = l_mpayeeid,
payeeid = i_payeeid,
bepid = i_bepid,
bepkey = i_bepkey,
paymentMethodname = i_pmtmethod,
transactionid = o_trxnid,
mtangibleId = l_mtangibleid,
payeeinstrid = i_payeeinstrid,
payerid = i_payerid,
payerinstrid = i_payerinstrid,
amount = i_amount,
currencyNameCode = i_currency,
reqtype = i_reqtype,
status = i_status,
instrtype = i_instrtype,
instrsubtype = i_instrsubtype,
bepcode = i_bepcode,
bepmessage = i_bepmessage,
errorlocation = i_errorlocation,
last_update_date = sysdate,
reqdate = i_trxndate,
updatedate = i_trxndate,
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 = 1,
-- trxntypeid = i_trxntypeid,
proc_reference_code = i_referencecode,
org_type = i_orgtype,
payment_channel_code = i_pmtchannelcode,
factored_flag = i_factoredflag,
debit_auth_flag = l_debit_auth_flag,
debit_auth_method = l_debit_auth_method,
debit_auth_reference = l_debit_auth_reference,
payer_instr_assignment_id= i_pmtinstrassignmentId,
process_profile_code = i_process_profile_code,
payer_party_id = l_payer_party_id,
payer_notification_required = DECODE(i_status, 0, l_payer_notif_flag, 'N')
WHERE trxnmid = l_trxnmid;
SELECT distinct validation_code_package, validation_code_entry_point
FROM iby_validation_sets_b vs, iby_fndcpt_sys_eft_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.funds_xfer_format_code = va.assignment_entity_id)
AND (va.val_assignment_entity_type = 'FORMAT')
AND (va.validation_set_code = vs.validation_set_code)
AND (va.inactive_date IS NULL)
AND (pf.inactive_date IS NULL)
and (pf.funds_xfer_format_code = p_funds_xfer_format_code);
SELECT COUNT(transactionid)
INTO l_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);