[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct substr(script_name,1,4), package_name,eft_script_output_path,
transmission_code
from XTR_SETTLEMENT_SCRIPTS
where script_type = 'SCRIPT'
and script_name = l_script_name
and authorised = 'Y';
select outfile_name
from fnd_concurrent_requests
where request_id = v_request_id;
select 1
from DUAL;
/*select outfile_name from fnd_concurrent_requests
into l_file_name
where request_id = v_request_id;
update XTR_SETTLEMENT_SCRIPTS
set run_requested_on = trunc(sysdate),
last_run_on = trunc(sysdate),
last_run_by = fnd_global.user_id,
last_file_created = l_file_name
where script_name = l_script_name
and script_type = 'SCRIPT'
and company_code = l_company;
select distinct d.account_no
from XTR_DEAL_DATE_AMOUNTS_V d,
XTR_BANK_ACCOUNTS b,
XTR_SETTLEMENT_SCRIPTS s
-- RV 2305918 where d.actual_settlement_date = NVL(to_date(settlement_date,'DD-MON-RR'), d.actual_settlement_date)
where d.actual_settlement_date = NVL(trunc(settlement_date), d.actual_settlement_date)
and d.company_code = l_company
and NVL(d.beneficiary_party,d.cparty_code) like nvl(l_cparty,'%')
and d.account_no = NVL(l_account, d.account_no)
and d.currency = NVL(l_currency, d.currency)
and d.trans_mts = 'Y'
and ((upper(sett) = 'Y') or (upper(sett) = 'N' and d.settlement_actioned is NULL))
and b.account_number = d.account_no
and b.party_code = l_company
and SUBSTR(b.eft_script_name,1,4) = l_eft_script_name --RV
and s.script_name = b.eft_script_name
and nvl(s.currency_code,b.currency) = b.currency
and s.script_type = 'SCRIPT';
select abs(d.settle_amount * 100),
d.cparty_account_no,'2'||spce||substr(d.cparty_account_no,1,2)||
substr(d.cparty_account_no,4,4)||substr(d.cparty_account_no,9,7)||
substr(d.cparty_account_no,17)||spce||'50'||spce||to_char(abs(d.settle_amount * 100))||
spce||p.short_name||spce||spce||spce||spce||q.short_name||spce||
'Ref '||rtrim(l_file_name)||spce
from XTR_EFT_DEBITS_V d,
XTR_PARTIES_V p,
XTR_PARTIES_V q,
XTR_BANK_ACCOUNTS b
-- 2305918 where d.actual_settlement_date = NVL(to_date(settlement_date,'DD-MON-RR'), d.actual_settlement_date)
where d.actual_settlement_date = NVL(trunc(settlement_date), d.actual_settlement_date)
and d.account_no = comp_acct
and d.company_code = l_company
and NVL(d.settle_party,d.cparty_code) like nvl(l_cparty,'%')
and d.trans_mts = 'Y'
and ((upper(sett) = 'Y') or (upper(sett) = 'N' and d.settlement_actioned is NULL))
and q.party_code = d.company_code
and p.party_code = d.settle_party
and b.account_number = d.account_no
and b.party_code = d.company_code;
select abs(d.settle_amount * 100),
d.cparty_account_no,'2'||spce||substr(d.cparty_account_no,1,2)||
substr(d.cparty_account_no,4,4)||substr(d.cparty_account_no,9,7)||
substr(d.cparty_account_no,17)||spce||'50'||spce||to_char(abs(d.settle_amount *
100))||spce||p.short_name||spce||spce||spce||
spce||q.short_name||spce||'Ref '||rtrim(l_file_name)||spce
from XTR_EFT_CREDITS_V d,
XTR_PARTIES_V p,
XTR_PARTIES_V q,
XTR_BANK_ACCOUNTS b
--2305918 where d.actual_settlement_date = NVL(to_date(settlement_date,'DD-MON-RR'), d.actual_settlement_date)
where d.actual_settlement_date = NVL(trunc(settlement_date), d.actual_settlement_date)
and d.account_no = comp_acct
and d.company_code = l_company
and NVL(d.settle_party,d.cparty_code) like nvl(l_cparty,'%')
and ((upper(sett) = 'Y' ) or (upper(sett) = 'N' and d.settlement_actioned is NULL))
and q.party_code = d.company_code
and p.party_code = d.settle_party
and b.account_number = d.account_no
and b.party_code = d.company_code;
select transfer_details, file_name
from XTR_MTS_RECORDS
order by FILE_NAME,CREATED_ON_DATE asc;
XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>Insert into XTR_MTS_RECORDS');
insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
file_name,settlement_date)
values(l_eft_script_name,deb_header,l_date,l_file_name,
--2305918 to_date(settlement_date,'DD-MON-RR'));
XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> End of insertion');
XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>>Insert into XTR_MTS_RECORDS');
insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
file_name,settlement_date)
values(l_eft_script_name,deb_rec,l_date,l_file_name,
--RV 2305918 to_date(settlement_date,'DD-MON-RR'));
insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
file_name,settlement_date)
values(l_eft_script_name,deb_ctrl,l_date,l_file_name,
--RV 2305918 to_date(settlement_date,'DD-MON-RR'));
XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> Insert into XTR_MTS_RECORDS');
insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
file_name,settlement_date)
values(l_eft_script_name,cre_header,l_date,l_file_name,
-- RV 2305918 to_date(settlement_date,'DD-MON-RR'));
XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '> End of Insert');
insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
file_name,settlement_date)
values(l_eft_script_name,cre_rec,l_date,l_file_name,
-- RV 2305918 to_date(settlement_date,'DD-MON-RR'));
insert into XTR_MTS_RECORDS(script_name,transfer_details,created_on_date,
file_name,settlement_date)
values(l_eft_script_name,cre_ctrl,l_date,l_file_name,
--RV 2305198 to_date(settlement_date,'DD-MON-RR'));
XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>UPDATE xtr_settlement_scripts table');
update XTR_SETTLEMENT_SCRIPTS
set run_requested_on = null,
only_new_transactions = null,
--RV 2305918 last_run_on = to_date(settlement_date,'DD-MON-RR'),
last_run_on = trunc(settlement_date),
last_run_by = fnd_global.user_id,
last_file_created = l_file_name
where substr(script_name,1,4) = l_eft_script_name
and script_type = 'SCRIPT';
XTR_RISK_DEBUG_PKG.dlog('BNZ_EFT: ' || '>UPDATE dda table');
update XTR_DEAL_DATE_AMOUNTS d
set d.SETTLEMENT_ACTIONED = 'Y',
d.SETTLEMENT_ACTIONED_FILE = l_file_name
-- RV 2305918 where d.actual_settlement_date = NVL(to_date(settlement_date,'DD-MON-RR'), d.actual_settlement_date)
where d.actual_settlement_date = NVL(trunc(settlement_date), d.actual_settlement_date)
and d.cashflow_amount <> 0
and d.account_no = comp_acct
and d.company_code = l_company
and NVL(d.beneficiary_party,d.cparty_code) like nvl(l_cparty,'%')
and d.trans_mts = 'Y'
and d.settle = 'Y'
and ((upper(sett) = 'Y') or (upper(sett) = 'N' and d.settlement_actioned is NULL))
and d.account_no = (select distinct b.account_number
from XTR_BANK_ACCOUNTS b,
XTR_SETTLEMENT_SCRIPTS s
where b.account_number = d.account_no
and b.party_code = upper(l_company)
and SUBSTR(b.eft_script_name,1,4) = l_eft_script_name --RV
and s.script_name = b.eft_script_name
and nvl(s.currency_code,b.currency) = b.currency
and s.script_type = 'SCRIPT');
DELETE from XTR_MTS_RECORDS
WHERE file_name = mts_file_name
AND transfer_details = mts_details;
select distinct sw.company_acct_no
from XTR_SWIFT_EFT_V sw,
XTR_BANK_ACCOUNTS b,
XTR_SETTLEMENT_SCRIPTS s
where sw.settlement_date = NVL(trunc(settlement_date), sw.settlement_date)
and sw.company = l_company
and sw.cparty = nvl(l_cparty,sw.cparty)
and sw.company_acct_no = NVL(l_account, sw.company_acct_no)
and sw.currency = NVL(l_currency, sw.currency)
and b.account_number = sw.company_acct_no
and b.party_code = l_company
and SUBSTR(b.eft_script_name,1,4) = l_eft_script_name --RV
and s.script_name = b.eft_script_name
and nvl(s.currency_code,b.currency) = b.currency
and s.script_type = 'SCRIPT';
select s.settlement_number, s.settlement_date, s.currency,
abs(s.settlement_amount), comp_name, comp_address1,
comp_address2, comp_address3, company_acct_no, comp_swift_id,
comp_bank_name, comp_bank_street, comp_bank_location,
cparty_acct_no, cp_swift_id, cp_bank_name, cp_bank_street,
cp_bank_location, cp_name, cp_address1, cp_address2,
cp_address3, cp_corr_bank_name,cp_corr_bank_no,
s.settlement_summary_id, s.cparty
from XTR_SWIFT_EFT_V s
where trunc(s.settlement_date) = trunc(NVL(trunc(v_paydate), s.settlement_date))
and s.company_acct_no = v_account_no
and s.company = l_company
and s.cparty = nvl(l_cparty,s.cparty);
select count(*)
from xtr_settlement_summary
where net_id = v_settlement_summary_id;
select count(*)
from xtr_deal_date_amounts x
where x.settlement_number = v_settlement_number
and ((x.beneficiary_account_no is not null and v_cparty = x.beneficiary_party)
or
(x.beneficiary_account_no is null and v_cparty = x.cparty_code));
select count(*)
from xtr_deal_date_amounts x, xtr_settlement_summary s1,
xtr_settlement_summary s2
where s1.settlement_number = v_settlement_number
and s1.settlement_summary_id = s2.net_id
and s2.settlement_number = x.settlement_number
and ((x.beneficiary_account_no is not null and v_cparty = x.beneficiary_party)
or
(x.beneficiary_account_no is null and v_cparty = x.cparty_code));
select count(*)
from xtr_deal_date_amounts x
where x.settlement_number = v_settlement_number
and ((upper(sett) = 'Y') OR (x.settlement_actioned is null));
select count(*)
from xtr_deal_date_amounts x, xtr_settlement_summary s1,
xtr_settlement_summary s2
where s1.settlement_number = v_settlement_number
and s1.settlement_summary_id = s2.net_id
and s2.settlement_number = x.settlement_number
and ((upper(sett) = 'Y') OR (x.settlement_actioned is null));
SELECT netoff_number
FROM xtr_deal_date_amounts
where settlement_number = (select settlement_number
from xtr_settlement_summary
WHERE net_id = p_netid
and ROWNUM=1);
update XTR_DEAL_DATE_AMOUNTS d
set d.SETTLEMENT_ACTIONED = 'Y',
d.SETTLEMENT_ACTIONED_FILE = l_file_name
where SETTLEMENT_NUMBER in
(select settlement_number from xtr_settlement_summary x
where x.net_id = v_settlement_summary_id);
update XTR_DEAL_DATE_AMOUNTS d
set d.SETTLEMENT_ACTIONED = 'Y',
d.SETTLEMENT_ACTIONED_FILE = l_file_name
where SETTLEMENT_NUMBER = v_settlement_number;
select distinct sw.company_acct_no
from XTR_SWIFT_EFT_V sw,
XTR_BANK_ACCOUNTS b,
XTR_SETTLEMENT_SCRIPTS s
where sw.settlement_date = NVL(trunc(settlement_date), sw.settlement_date)
and sw.company = l_company
and sw.cparty = nvl(l_cparty,sw.cparty)
and sw.company_acct_no = NVL(l_account, sw.company_acct_no)
and sw.currency = NVL(l_currency, sw.currency)
and b.account_number = sw.company_acct_no
and b.party_code = l_company
and SUBSTR(b.eft_script_name,1,4) = l_eft_script_name --RV
and s.script_name = b.eft_script_name
and nvl(s.currency_code,b.currency) = b.currency
and s.script_type = 'SCRIPT';
select s.settlement_number, s.settlement_date, s.currency,
abs(s.settlement_amount), comp_name, comp_address1,
comp_address2, comp_address3, company_acct_no, comp_swift_id,
comp_bank_name, comp_bank_street, comp_bank_location,
cparty_acct_no, cp_swift_id, cp_bank_name, cp_bank_street,
cp_bank_location, cp_name, cp_address1, cp_address2,
cp_address3, s.company, s.cparty, s.settlement_summary_id
from XTR_SWIFT_EFT_V s
where trunc(s.settlement_date) = trunc(NVL(trunc(v_paydate), s.settlement_date))
and s.company_acct_no = v_account_no
and s.company = l_company
and s.cparty = nvl(l_cparty,s.cparty);
select count(*)
from xtr_settlement_summary x
where x.net_id = v_settlement_summary_id;
select count(*)
from xtr_deal_date_amounts x
where x.settlement_number = v_settlement_number
and ((upper(sett) = 'Y') OR (x.settlement_actioned is null));
select count(*)
from xtr_deal_date_amounts x, xtr_settlement_summary s1,
xtr_settlement_summary s2
where s1.settlement_number = v_settlement_number
and s1.settlement_summary_id = s2.net_id
and s2.settlement_number = x.settlement_number
and ((upper(sett) = 'Y') OR (x.settlement_actioned is null));
select count(*)
from xtr_deal_date_amounts x
where x.settlement_number = v_settlement_number
and ((x.beneficiary_account_no is not null and v_cparty = x.beneficiary_party)
or
(x.beneficiary_account_no is null and v_cparty = x.cparty_code));
select count(*)
from xtr_deal_date_amounts x, xtr_settlement_summary s1,
xtr_settlement_summary s2
where s1.settlement_number = v_settlement_number
and s1.settlement_summary_id = s2.net_id
and s2.settlement_number = x.settlement_number
and ((x.beneficiary_account_no is not null and v_cparty = x.beneficiary_party)
or
(x.beneficiary_account_no is null and v_cparty = x.cparty_code));
SELECT text
FROM xtr_sys_languages_tl
WHERE module_name='XTRSECOM'
AND item_name='PTY.ADDRESS_1'
AND LANGUAGE=USERENV('lang');
SELECT text
FROM xtr_sys_languages_tl
WHERE module_name='XTRSECPY'
AND item_name='PTY.P_ADDRESS_1'
AND LANGUAGE=USERENV('lang');
update XTR_DEAL_DATE_AMOUNTS d
set d.SETTLEMENT_ACTIONED = 'Y',
d.SETTLEMENT_ACTIONED_FILE = l_file_name
where SETTLEMENT_NUMBER in
(select settlement_number from xtr_settlement_summary x
where x.net_id = v_settlement_summary_id);
update XTR_DEAL_DATE_AMOUNTS d
set d.SETTLEMENT_ACTIONED = 'Y',
d.SETTLEMENT_ACTIONED_FILE = l_file_name
where SETTLEMENT_NUMBER = v_settlement_number;