[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sim.verification_method,
are.import_reference,
itr.currency,
itr.account_number
from xtr_source_of_imports_v sim,
xtr_available_for_recon_v are,
xtr_import_trailer_details_v itr
where sim.source = itr.source
AND are.import_reference = itr.import_reference
AND are.import_reference >= NVL(G_import_reference_from, are.import_reference)
AND are.import_reference <= NVL(G_import_reference_to, are.import_reference);
select distinct sim.verification_method,
are.import_reference,
itr.currency,
itr.account_number
from xtr_source_of_imports_v sim,
xtr_available_for_recon_v are,
xtr_import_trailer_details_v itr,
XTR_PAY_REC_RECONCILIATION_V prr
where sim.source = itr.source
AND are.import_reference = itr.import_reference
AND prr.import_reference = are.import_reference
AND prr.import_reference = itr.import_reference
AND itr.account_number = NVL(G_acct_num, itr.account_number)
AND sim.source = NVL(G_source, sim.source)
--* bug#2464159, rravunny
--* changed the condition
AND prr.value_date between
least(nvl(date_from,prr.value_date),nvl(date_to,prr.value_date))
and
greatest(nvl(date_from,prr.value_date),nvl(date_to,prr.value_date));
xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>> call UPDATE_ROLL_TRANS -------->');
UPDATE_ROLL_TRANS( G_verification_method,
x_min_rec_nos,
x_max_rec_nos,
'AUTO');
xtr_debug_pkg.debug('AUTO_RECONCILIATION: ' || '>> call UPDATE_ROLL_TRANS -------->');
UPDATE_ROLL_TRANS( G_verification_method,
x_min_rec_nos,
x_max_rec_nos,
'AUTO');
select DEAL_NUMBER,TRANSACTION_NUMBER,AMOUNT,AMOUNT_DATE,
RECONCILED_REFERENCE,RECONCILED_PASS_CODE --reset -- AW 1/6/2000 Bug 1139396
from XTR_DEAL_DATE_AMOUNTS_V
where RECONCILED_REFERENCE between
to_number(nvl(p_min_rec_nos,9999999)) and
to_number(nvl(p_max_rec_nos,0))
--- and AMOUNT_TYPE = 'PRINFLW'
and DATE_TYPE = 'SETTLE'
and nvl(amount,0) <> 0
order by DEAL_NUMBER,TRANSACTION_NUMBER;
select START_DATE,CURRENCY,DEAL_SUBTYPE
from XTR_ROLLOVER_TRANSACTIONS_V
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = l_trans_no;
update XTR_DEAL_DATE_AMOUNTS_V
set AMOUNT = 0,
CASHFLOW_AMOUNT = 0,
DATE_TYPE = 'FORCAST' -- AW 1/6/2000 Bug 1139396
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = l_trans_no
and nvl(ACTION_CODE,'@#@') <>'INCRSE'
and DATE_TYPE = 'SETTLE'; -- AW 1/6/2000 Bug 1139396
update XTR_ROLLOVER_TRANSACTIONS_V
set PI_AMOUNT_RECEIVED = NULL,
SETTLE_DATE = NULL
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = l_trans_no;
PROCEDURE UPDATE_ROLL_TRANS_RTMM (p_verification_method VARCHAR2,
p_min_rec_nos NUMBER,
p_max_rec_nos NUMBER,
p_calling_method VARCHAR2,
p_val_date DATE) IS
--
l_min_rec_nos NUMBER;
select dda.DEAL_NUMBER, dda.TRANSACTION_NUMBER, dda.AMOUNT, dda.AMOUNT_DATE,
dda.RECONCILED_REFERENCE,substr(dda.RECONCILED_PASS_CODE,2) ---reset
from XTR_DEAL_DATE_AMOUNTS_V dda
where dda.RECONCILED_REFERENCE between
nvl(L_MIN_REC_NOS,9999999) and nvl(L_MAX_REC_NOS,0)
and dda.DEAL_TYPE = 'RTMM'
and nvl(dda.amount,0) <>0
and dda.DATE_TYPE <> 'COMENCE' -- bug 3045394
-- and substr( nvl(dda.RECONCILED_PASS_CODE, '@'), 1, 1) = '^'
and ( nvl(p_calling_method, 'AUTO') = 'MANUAL' OR
exists(select 'anyrow'
from XTR_RECONCILIATION_PROCESS rp
where rp.VERIFICATION_METHOD = nvl(p_verification_method,rp.VERIFICATION_METHOD)
and rp.RECONCILED_PASS_CODE = substr(dda.RECONCILED_PASS_CODE,2)
and nvl(rp.PROCESS_TYPE, 'M') = 'A'))
order by DEAL_NUMBER,TRANSACTION_NUMBER;
select START_DATE,CURRENCY,DEAL_SUBTYPE
from XTR_ROLLOVER_TRANSACTIONS_V
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = l_trans_no;
SELECT effective_date FROM ce_statement_lines WHERE statement_line_id IN (
SELECT statement_line_id FROM ce_statement_reconcils_all WHERE reference_id in
(SELECT settlement_summary_id FROM xtr_settlement_summary WHERE settlement_number
IN (SELECT settlement_number FROM xtr_deal_date_amounts WHERE reconciled_reference = p_rec_ref )));
/* select VALUE_DATE
from XTR_PAY_REC_RECONCILIATION_V
where RECONCILED_REFERENCE = p_rec_ref ; */
UPDATE XTR_ROLLOVER_TRANSACTIONS_V
SET PI_AMOUNT_RECEIVED = l_amount,
MATURITY_DATE = l_date,
SETTLE_DATE = l_settle_date
WHERE DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = l_trans_no;
xtr_debug_pkg.debug('UPDATE_ROLL_TRANS: ' || 'old_rec_ref = ' ||to_char(l_rec_ref));
xtr_debug_pkg.debug('EXCEPTION: XTR_AUTO_RECONCILIATION.update_roll_trans');
END UPDATE_ROLL_TRANS_RTMM;
| update_roll_trans |
| |
| DESCRIPTION |
| Updates Rollover Transaction Table |
| |
| REQUIRES |
| p_min_rec_nos |
| p_max_rec_nos |
| |
| RETURNS |
| |
| HISTORY |
--------------------------------------------------------------------- */
PROCEDURE UPDATE_ROLL_TRANS (p_verification_method VARCHAR2,
p_min_rec_nos NUMBER,
p_max_rec_nos NUMBER,
p_calling_method VARCHAR2) IS
--
l_min_rec_nos NUMBER;
select dda.DEAL_NUMBER, dda.TRANSACTION_NUMBER, dda.AMOUNT, dda.AMOUNT_DATE,
dda.RECONCILED_REFERENCE,substr(dda.RECONCILED_PASS_CODE,2) ---reset
from XTR_DEAL_DATE_AMOUNTS_V dda
where dda.RECONCILED_REFERENCE between
nvl(L_MIN_REC_NOS,9999999) and nvl(L_MAX_REC_NOS,0)
and dda.DEAL_TYPE = 'RTMM'
and nvl(dda.amount,0) <>0
and dda.DATE_TYPE <> 'COMENCE' -- bug 3045394
-- and substr( nvl(dda.RECONCILED_PASS_CODE, '@'), 1, 1) = '^'
and ( nvl(p_calling_method, 'AUTO') = 'MANUAL' OR
exists(select 'anyrow'
from XTR_RECONCILIATION_PROCESS rp
where rp.VERIFICATION_METHOD = nvl(p_verification_method,rp.VERIFICATION_METHOD)
and rp.RECONCILED_PASS_CODE = substr(dda.RECONCILED_PASS_CODE,2)
and nvl(rp.PROCESS_TYPE, 'M') = 'A'))
order by DEAL_NUMBER,TRANSACTION_NUMBER;
select START_DATE,CURRENCY,DEAL_SUBTYPE
from XTR_ROLLOVER_TRANSACTIONS_V
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = l_trans_no;
select VALUE_DATE
from XTR_PAY_REC_RECONCILIATION_V
where RECONCILED_REFERENCE = p_rec_ref ;
UPDATE XTR_ROLLOVER_TRANSACTIONS_V
SET PI_AMOUNT_RECEIVED = l_amount,
MATURITY_DATE = l_date,
SETTLE_DATE = l_settle_date
WHERE DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = l_trans_no;
xtr_debug_pkg.debug('UPDATE_ROLL_TRANS: ' || 'old_rec_ref = ' ||to_char(l_rec_ref));
xtr_debug_pkg.debug('EXCEPTION: XTR_AUTO_RECONCILIATION.update_roll_trans');
END UPDATE_ROLL_TRANS;
select MATURITY_DATE,CPARTY_CODE,CLIENT_CODE,PRODUCT_TYPE,
PORTFOLIO_CODE,SETTLE_ACCOUNT_NO,CPARTY_REF,
COMPANY_CODE,DEALER_CODE,DEAL_DATE,LIMIT_CODE
from XTR_DEALS_V
where DEAL_NO = l_deal_no
and deal_type = 'RTMM';
| p_least_inserted |
| p_ref_date |
| p_trans_no |
| p_rec_ref |
| p_rec_pass |
| p_limit_code |
| |
| RETURNS |
| |
| HISTORY |
--------------------------------------------------------------------- */
PROCEDURE RECALC_DT_DETAILS (p_deal_no NUMBER,
p_deal_date DATE,
p_company VARCHAR2,
p_subtype VARCHAR2,
p_product VARCHAR2,
p_portfolio VARCHAR2,
p_ccy VARCHAR2,
p_maturity DATE,
p_settle_acct VARCHAR2,
p_cparty VARCHAR2,
p_client VARCHAR2,
p_cparty_acct VARCHAR2,
p_dealer VARCHAR2,
p_least_inserted VARCHAR2,
p_ref_date DATE,
p_trans_no NUMBER,
p_rec_ref NUMBER,
p_rec_pass VARCHAR2,
p_limit_code VARCHAR2 ) IS
--
l_deal_no NUMBER;
l_least_inserted VARCHAR2(1);
select ROUNDING_FACTOR
from XTR_MASTER_CURRENCIES_V
where CURRENCY = l_ccy;
select YEAR_CALC_TYPE
from XTR_DEALS_V
where DEAL_NO = l_deal_no;
select nvl(a.HCE_RATE,1) HCE_RATE
from XTR_MASTER_CURRENCIES_V a
where a.CURRENCY = l_ccy;
select max(START_DATE)
from XTR_ROLLOVER_TRANSACTIONS_V
where DEAL_NUMBER = l_deal_no
and START_DATE <= l_ref_date
and STATUS_CODE = 'CURRENT';
select rowid
from XTR_ROLLOVER_TRANSACTIONS_V
where DEAL_NUMBER = l_deal_no
and START_DATE >= l_start_date
and STATUS_CODE = 'CURRENT'
order by START_DATE desc,nvl(SETTLE_DATE,MATURITY_DATE) desc,TRANSACTION_NUMBER desc;
select DEAL_TYPE,START_DATE,MATURITY_DATE,NO_OF_DAYS,BALANCE_OUT_BF,
BALANCE_OUT,PRINCIPAL_ADJUST,INTEREST_RATE,INTEREST,
INTEREST_SETTLED,PRINCIPAL_ACTION,TRANSACTION_NUMBER,
SETTLE_DATE,ACCUM_INTEREST_BF,PI_AMOUNT_DUE,PI_AMOUNT_RECEIVED,
ACCUM_INTEREST,ROWID,ADJUSTED_BALANCE,COMMENTS,
EXPECTED_BALANCE_BF,EXPECTED_BALANCE_OUT,PRINCIPAL_AMOUNT_TYPE,ENDORSER_CODE
from XTR_ROLLOVER_TRANSACTIONS_V
where DEAL_NUMBER = l_deal_no
and START_DATE >= l_start_date
and STATUS_CODE = 'CURRENT'
order by START_DATE asc,nvl(SETTLE_DATE,MATURITY_DATE) asc,TRANSACTION_NUMBER asc
for UPDATE OF START_DATE;
select b.INTEREST_ACTION
from XTR_DEALS_V a,
XTR_PAYMENT_SCHEDULE_V b
where a.DEAL_NO = l_deal_no
and b.PAYMENT_SCHEDULE_CODE = a.PAYMENT_SCHEDULE_CODE;
select day_count_type, rounding_type
from xtr_deals
where deal_no = l_deal_no;
select actual_settlement_date,settlement_number,deal_type,trans_mts
,settlement_authorised_by,audit_indicator
from xtr_deal_date_amounts_v
where deal_number = l_deal_no
and transaction_number = pmt.transaction_number
and amount_type = 'INTSET';
l_least_inserted :=p_least_inserted;
and nvl(l_least_inserted,'N') = 'Y' then
pmt.MATURITY_DATE := l_ref_date;
update XTR_ROLLOVER_TRANSACTIONS_V
set START_DATE = pmt.START_DATE,
BALANCE_OUT_BF = pmt.BALANCE_OUT_BF,
BALANCE_OUT_BF_HCE = hce_balbf,
ACCUM_INTEREST_BF = pmt.ACCUM_INTEREST_BF,
ACCUM_INTEREST_BF_HCE = hce_accum_int_bf,
PI_AMOUNT_DUE = pmt.PI_AMOUNT_DUE,
PI_AMOUNT_RECEIVED = pmt.PI_AMOUNT_RECEIVED,
ADJUSTED_BALANCE = pmt.ADJUSTED_BALANCE,
BALANCE_OUT = pmt.BALANCE_OUT,
BALANCE_OUT_HCE = hce_balos,
PRINCIPAL_ADJUST_HCE = hce_princ,
PRINCIPAL_ADJUST = pmt.PRINCIPAL_ADJUST,
INTEREST = pmt.INTEREST,
INTEREST_SETTLED = pmt.INTEREST_SETTLED,
INTEREST_HCE = hce_interest,
ACCUM_INTEREST = pmt.ACCUM_INTEREST,
ACCUM_INTEREST_HCE = hce_accum_int,
SETTLE_DATE = pmt.SETTLE_DATE,
NO_OF_DAYS = pmt.NO_OF_DAYS,
MATURITY_DATE = pmt.MATURITY_DATE,
EXPECTED_BALANCE_BF = nvl(pmt.EXPECTED_BALANCE_BF,0),
EXPECTED_BALANCE_OUT = pmt.EXPECTED_BALANCE_OUT
where ROWID = pmt.ROWID;
update XTR_DEAL_DATE_AMOUNTS_V
set AMOUNT = round(decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,nvl(pmt.PI_AMOUNT_DUE,0)
,nvl(pmt.INTEREST_SETTLED,0)),
rounding_fac),
HCE_AMOUNT = round(decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,hce_due
,nvl(hce_settled,
nvl(pmt.INTEREST_SETTLED,0))),
rounding_fac),
AMOUNT_DATE = nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),
DATE_TYPE = decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,'FORCAST','SETTLE'),
TRANSACTION_RATE = pmt.INTEREST_RATE,
CASHFLOW_AMOUNT = round(decode(l_subtype,
'FUND',(-1),1) *
decode(nvl(pmt.PI_AMOUNT_RECEIVED,0)
,0,nvl(pmt.PI_AMOUNT_DUE,0)
,nvl(pmt.INTEREST_SETTLED,0)),
rounding_fac),
RECONCILED_PASS_CODE = decode(substr(nvl(RECONCILED_PASS_CODE,'@'),1,1),'^',
substr(RECONCILED_PASS_CODE,2),RECONCILED_PASS_CODE)
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
and AMOUNT_TYPE = 'INTSET';
update XTR_DEAL_DATE_AMOUNTS_V
set AMOUNT = decode(nvl(AMOUNT,0),0,nvl(l_prin_decr,0),AMOUNT),
HCE_AMOUNT = decode(nvl(HCE_AMOUNT,0),0,hce_decr,HCE_AMOUNT),
AMOUNT_DATE = nvl(pmt.SETTLE_DATE,pmt.MATURITY_DATE),
DATE_TYPE = decode(nvl(pmt.PI_AMOUNT_RECEIVED,0),0,'FORCAST','SETTLE'),
TRANSACTION_RATE = pmt.INTEREST_RATE,
SETTLE = 'Y', -- bug 3045426
CASHFLOW_AMOUNT = decode(nvl(CASHFLOW_AMOUNT,0),0,decode(l_subtype
,'FUND',(-1),1) * l_prin_decr,CASHFLOW_AMOUNT),
RECONCILED_PASS_CODE = decode(substr(nvl(RECONCILED_PASS_CODE,'@'),1,1),'^',
substr(RECONCILED_PASS_CODE,2),'@',l_rec_pass,RECONCILED_PASS_CODE),
RECONCILED_REFERENCE = nvl(RECONCILED_REFERENCE,l_rec_ref)
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
and AMOUNT_TYPE = 'PRINFLW'
and ACTION_CODE = 'DECRSE';
update XTR_DEAL_DATE_AMOUNTS_V
set SETTLEMENT_NUMBER = l_settle_number,
ACTUAL_SETTLEMENT_DATE = l_settle_date,
TRANS_MTS = l_trans_mts,
SETTLEMENT_AUTHORISED_BY = l_settle_by,
AUDIT_INDICATOR = l_audit_indicator
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
and AMOUNT_TYPE = 'PRINFLW'
and ACTION_CODE = 'DECRSE';
delete from XTR_DEAL_DATE_AMOUNTS_V
where DEAL_NUMBER = l_deal_no
and TRANSACTION_NUMBER = pmt.TRANSACTION_NUMBER
and AMOUNT_TYPE in ('PRINFLW')
and ACTION_CODE = 'INCRSE';
delete from XTR_ROLLOVER_TRANSACTIONS_V
where DEAL_NUMBER = l_deal_no
and START_DATE >= l_nill_date;
UPDATE XTR_DEAL_DATE_AMOUNTS_V
set AMOUNT = nvl(pmt.BALANCE_OUT,0),
HCE_AMOUNT = hce_balos
where DEAL_NUMBER = l_deal_no
and DEAL_TYPE='RTMM'
and AMOUNT_TYPE = 'BALOUT';
insert into XTR_DEAL_DATE_AMOUNTS_V
(deal_type,amount_type,date_type,
deal_number,transaction_number,transaction_date,currency,
amount,hce_amount,amount_date,transaction_rate,
cashflow_amount,company_code,account_no,action_code,
cparty_account_no,deal_subtype,product_type,
portfolio_code,status_code,cparty_code,dealer_code,
settle,client_code,limit_code,limit_party)
values ('RTMM','BALOUT','COMENCE',
l_deal_no,pmt.TRANSACTION_NUMBER,
l_deal_date,l_ccy,nvl(pmt.BALANCE_OUT,0),
nvl(hce_balos,0),l_maturity,pmt.INTEREST_RATE,0,
l_company,l_settle_acct,NULL,
l_cparty_acct,l_subtype,l_product,
l_portfolio,'CURRENT',l_cparty,
l_dealer,'N',l_client,nvl(l_limit_code,'NILL'),l_cparty);
select RECONCILED_PASS_CODE,nvl(DAYS_ADJUSTMENT,0)
from XTR_RECONCILIATION_PROCESS
where VERIFICATION_METHOD = P_VERIFICATION_METHOD
-- and PROCESS_TYPE = 'A'
order by SEQUENCE_ORDER asc;
select RECONCILE_ON_COLUMN,RECONCILE_DETAIL
from XTR_RECONCILIATION_PASSES
where RECONCILED_PASS_CODE = P_PASS_CODE;
select *
from XTR_PAY_REC_RECONCILIATION
where IMPORT_REFERENCE = P_IMPORT_REFERENCE
and RECONCILED_PASS_CODE is NULL
and RECONCILED_REFERENCE is NULL
for UPDATE OF IMPORT_REFERENCE;
select XTR_DEAL_DATE_AMOUNTS_S.NEXTVAL
from DUAL;
select net_id from xtr_settlement_summary
where settlement_number =
(select settlement_number
from xtr_deal_date_amounts
where netoff_number = p_netoff_number
and rownum < 2);
select settlement_number
from xtr_deal_date_amounts
where reconciled_reference = p_reconciled_reference;
select sum(round(CASHFLOW_AMOUNT,2)),count(*),AMOUNT_DATE, NETOFF_NUMBER
from XTR_DEAL_DATE_AMOUNTS_V
where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and nvl(l_deal_no,'N') = 'Y')
or (l_deal_no is NULL )) --- modify
and ((AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
(rec_det.VALUE_DATE + l_days) and
nvl(l_date,'N') = 'N') or
(AMOUNT_DATE = rec_det.VALUE_DATE and nvl(l_date,'N')='Y'))
and CURRENCY =P_CURRENCY
and AMOUNT_DATE <= P_CGU$SYSDATE
and RECONCILED_REFERENCE is NULL
and RECONCILED_PASS_CODE is NULL
and NETOFF_NUMBER is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CASHFLOW_AMOUNT < 0 and rec_det.DEBIT_AMOUNT is NOT NULL)
and ((abs(CASHFLOW_AMOUNT) = rec_det.DEBIT_AMOUNT
and nvl(l_amount,'N') = 'Y') or (nvl(l_amount,'N') = 'N'))
or (nvl(rec_det.DEBIT_AMOUNT,0) = 0))
and ((CASHFLOW_AMOUNT > 0 and rec_det.CREDIT_AMOUNT is NOT NULL)
and ((abs(CASHFLOW_AMOUNT) = rec_det.CREDIT_AMOUNT
and nvl(l_amount,'N')='Y') or (nvl(l_amount,'N') = 'N'))
or (nvl(rec_det.CREDIT_AMOUNT,0) = 0))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and nvl(l_party,'N') = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N') ='Y')
group by AMOUNT_DATE, NETOFF_NUMBER
union all
select sum(round(CASHFLOW_AMOUNT,2)),count(distinct NETOFF_NUMBER),AMOUNT_DATE, NETOFF_NUMBER
from XTR_DEAL_DATE_AMOUNTS_V
where
/*
((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and nvl(l_deal_no,'N') = 'Y')
or (l_deal_no is NULL )) --- modify
and
*/
((AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
(rec_det.VALUE_DATE + l_days) and
nvl(l_date,'N') = 'N') or
(AMOUNT_DATE = rec_det.VALUE_DATE and nvl(l_date,'N')='Y'))
and CURRENCY =P_CURRENCY
and AMOUNT_DATE <= P_CGU$SYSDATE
and RECONCILED_REFERENCE is NULL
and RECONCILED_PASS_CODE is NULL
and NETOFF_NUMBER is NOT NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and nvl(l_party,'N') = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N') ='Y')
group by AMOUNT_DATE, NETOFF_NUMBER
having ((sum(round(CASHFLOW_AMOUNT,2)) < 0 and rec_det.DEBIT_AMOUNT is NOT NULL)
and ((abs(sum(round(CASHFLOW_AMOUNT,2))) = rec_det.DEBIT_AMOUNT
and nvl(l_amount,'N') = 'Y') or (nvl(l_amount,'N') = 'N'))
or (nvl(rec_det.DEBIT_AMOUNT,0) = 0))
and ((sum(round(CASHFLOW_AMOUNT,2)) > 0 and rec_det.CREDIT_AMOUNT is NOT NULL)
and ((abs(sum(round(CASHFLOW_AMOUNT,2))) = rec_det.CREDIT_AMOUNT
and nvl(l_amount,'N')='Y') or (nvl(l_amount,'N') = 'N'))
or (nvl(rec_det.CREDIT_AMOUNT,0) = 0))
;
select AMOUNT_DATE,sum(round(CASHFLOW_AMOUNT,2)),count(distinct nvl(NETOFF_NUMBER, -1)), NETOFF_NUMBER
from XTR_DEAL_DATE_AMOUNTS_V
where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and nvl(l_deal_no,'N') = 'Y')
or (l_deal_no is NULL and date_type <> 'FORCAST'))
and (((AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
(rec_det.VALUE_DATE + l_days) and
nvl(l_date,'N') = 'N') or
(AMOUNT_DATE = rec_det.VALUE_DATE and nvl(l_date,'N')='Y'))
and nvl(l_sum_date,'N') = 'Y')
and AMOUNT_DATE <= P_CGU$SYSDATE
and CURRENCY =P_CURRENCY
and RECONCILED_REFERENCE is NULL
and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and nvl(l_party,'N') = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y')
group by AMOUNT_DATE, NETOFF_NUMBER
;
select sum(round(CASHFLOW_AMOUNT,2)),count(distinct nvl(netoff_number, -1)), Netoff_number
from XTR_DEAL_DATE_AMOUNTS_V
where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and nvl(l_deal_no,'N') = 'Y')
or (l_deal_no is NULL and date_type<>'FORCAST'))
and (((AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
(rec_det.VALUE_DATE + l_days) and
nvl(l_date,'N') = 'N') or
(AMOUNT_DATE = rec_det.VALUE_DATE and nvl(l_date,'N')='Y')) and
nvl(l_sum_range,'N') = 'Y')
and AMOUNT_DATE <= P_CGU$SYSDATE
and CURRENCY =P_CURRENCY and RECONCILED_REFERENCE is NULL
and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and nvl(l_party,'N') = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y')
group by NETOFF_NUMBER
;
SELECT SUBSTR(value,1,DECODE(INSTR(value,','),0,LENGTH(value),INSTR(value,',')-1) )
FROM v$parameter
WHERE name = 'utl_file_dir';
update XTR_DEAL_DATE_AMOUNTS
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE =
decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
trunc(l_s_date)),
AMOUNT_DATE =
decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
HCE_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
(AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
nvl(l_reset_amt,0)),AMOUNT),
CASHFLOW_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and l_deal_no = 'Y') or (l_deal_no is NULL and DATE_TYPE <>'FORCAST'))
and AMOUNT_DATE = l_s_date
and NETOFF_NUMBER is NULL
and AMOUNT_TYPE <> 'FACEVAL'
and CURRENCY =P_CURRENCY
and RECONCILED_REFERENCE is NULL and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and l_party = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and l_serial_ref = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
Update Xtr_Settlement_Summary
Set status = 'R'
Where settlement_number = C2_Rec.settlement_number;
update XTR_DEAL_DATE_AMOUNTS
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE =
decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
trunc(l_s_date)),
AMOUNT_DATE =
decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
HCE_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
(AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
nvl(l_reset_amt,0)),AMOUNT),
CASHFLOW_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
where
/*
((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and l_deal_no = 'Y') or (l_deal_no is NULL and DATE_TYPE <>'FORCAST'))
and
*/
AMOUNT_DATE = l_s_date
and NETOFF_NUMBER = v_netoff_number
and AMOUNT_TYPE <> 'FACEVAL'
and CURRENCY =P_CURRENCY
and RECONCILED_REFERENCE is NULL and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and l_party = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and l_serial_ref = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
Update Xtr_Settlement_Summary
Set status = 'R'
Where settlement_summary_id = C1_Rec.net_ID;
update XTR_PAY_REC_RECONCILIATION
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE = P_PASS_CODE
where CURRENT OF REC;
update XTR_DEAL_DATE_AMOUNTS
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE =
decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
trunc(l_s_date)),
AMOUNT_DATE =
decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
HCE_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
(AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
nvl(l_reset_amt,0)),AMOUNT),
CASHFLOW_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and l_deal_no = 'Y') or (l_deal_no is NULL))
and AMOUNT_DATE = l_s_date
and NETOFF_NUMBER is NULL
and AMOUNT_TYPE <> 'FACEVAL'
and CURRENCY =P_CURRENCY
and RECONCILED_REFERENCE is NULL
and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and l_party = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and l_serial_ref = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM,'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
Update Xtr_Settlement_Summary
Set status = 'R'
Where settlement_number = C2_Rec.settlement_number;
update XTR_DEAL_DATE_AMOUNTS
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE =
decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
trunc(l_s_date)),
AMOUNT_DATE =
decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
HCE_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
(AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
nvl(l_reset_amt,0)),AMOUNT),
CASHFLOW_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
where
/*
((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and l_deal_no = 'Y') or (l_deal_no is NULL))
and
*/
AMOUNT_DATE = l_s_date
and NETOFF_NUMBER = v_netoff_number
and AMOUNT_TYPE <> 'FACEVAL'
and CURRENCY =P_CURRENCY
and RECONCILED_REFERENCE is NULL
and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and l_party = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and l_serial_ref = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM,'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
Update Xtr_Settlement_Summary
Set status = 'R'
Where settlement_summary_id = C1_Rec.net_ID;
update XTR_PAY_REC_RECONCILIATION
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE = P_PASS_CODE
where CURRENT OF REC;
update XTR_DEAL_DATE_AMOUNTS
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE =
decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
trunc(l_s_date)),
AMOUNT_DATE =
decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
HCE_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
(AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
nvl(l_reset_amt,0)),AMOUNT),
CASHFLOW_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and l_deal_no = 'Y') or (l_deal_no is NULL))
and AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
P_CGU$SYSDATE
and NETOFF_NUMBER is NULL
and AMOUNT_TYPE <> 'FACEVAL'
and CURRENCY =P_CURRENCY
and RECONCILED_REFERENCE is NULL -- add
and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and l_party = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and l_serial_ref = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
Update Xtr_Settlement_Summary
Set status = 'R'
Where settlement_number = C2_Rec.Settlement_Number;
update XTR_DEAL_DATE_AMOUNTS
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE =
decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
trunc(l_s_date)),
AMOUNT_DATE =
decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
HCE_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
(AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
nvl(l_reset_amt,0)),AMOUNT),
CASHFLOW_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
where
/*
((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and l_deal_no = 'Y') or (l_deal_no is NULL))
and
*/
AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
P_CGU$SYSDATE
and NETOFF_NUMBER = v_netoff_number
and AMOUNT_TYPE <> 'FACEVAL'
and CURRENCY =P_CURRENCY
and RECONCILED_REFERENCE is NULL -- add
and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and l_party = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and l_serial_ref = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
Update Xtr_Settlement_Summary
Set status = 'R'
Where settlement_summary_id = C1_Rec.net_ID;
update XTR_PAY_REC_RECONCILIATION
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE = P_PASS_CODE
where CURRENT OF REC;
update XTR_DEAL_DATE_AMOUNTS
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE =
decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
trunc(l_s_date)),
AMOUNT_DATE =
decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
HCE_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
(AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
nvl(l_reset_amt,0)),AMOUNT),
CASHFLOW_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
where AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
(rec_det.VALUE_DATE - l_days)
and ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and l_deal_no = 'Y') or (l_deal_no is NULL))
and AMOUNT_DATE <= P_CGU$SYSDATE
and NETOFF_NUMBER is NULL
and AMOUNT_TYPE <> 'FACEVAL'
and CURRENCY =P_CURRENCY
and RECONCILED_REFERENCE is NULL -- add
and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and l_party = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and l_serial_ref = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
Update Xtr_Settlement_Summary
Set status = 'R'
Where settlement_number = C2_Rec.Settlement_Number;
update XTR_DEAL_DATE_AMOUNTS
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE =
decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
trunc(l_s_date)),
AMOUNT_DATE =
decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
HCE_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
(AMOUNT/HCE_AMOUNT)*(nvl(HCE_AMOUNT,0) + nvl(l_reset_amt,0))),HCE_AMOUNT),
AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,nvl(AMOUNT,0) +
nvl(l_reset_amt,0)),AMOUNT),
CASHFLOW_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
CASHFLOW_AMOUNT,nvl(CASHFLOW_AMOUNT,0) + nvl(l_reset_amt,0)),CASHFLOW_AMOUNT)
where AMOUNT_DATE between (rec_det.VALUE_DATE - l_days) and
(rec_det.VALUE_DATE - l_days)
/*
and ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and l_deal_no = 'Y') or (l_deal_no is NULL))
*/
and AMOUNT_DATE <= P_CGU$SYSDATE
and NETOFF_NUMBER = v_netoff_number
and AMOUNT_TYPE <> 'FACEVAL'
and CURRENCY =P_CURRENCY
and RECONCILED_REFERENCE is NULL -- add
and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and l_bk_acct = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and l_party = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and l_serial_ref = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
Update Xtr_Settlement_Summary
Set status = 'R'
Where settlement_summary_id = C1_Rec.net_ID;
update XTR_PAY_REC_RECONCILIATION
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE = P_PASS_CODE
where CURRENT OF REC;
xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> UPDATE DDA >>>>>>>>>>>>>>');
update XTR_DEAL_DATE_AMOUNTS
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE =
decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
trunc(l_one_date)),
AMOUNT_DATE =
decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,abs(nvl(l_sum_amt,0))),AMOUNT),
HCE_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
(AMOUNT/HCE_AMOUNT)*abs(nvl(l_sum_amt,0))),HCE_AMOUNT),
CASHFLOW_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
CASHFLOW_AMOUNT,nvl(l_sum_amt,0)),CASHFLOW_AMOUNT)
where ((to_char(DEAL_NUMBER) = ltrim(rtrim(rec_det.PARTICULARS))
and nvl(l_deal_no,'N') = 'Y')
or (l_deal_no is NULL )) --- modify
and AMOUNT_DATE = l_one_date
and NETOFF_NUMBER is NULL
and AMOUNT_TYPE <> 'FACEVAL'
and CURRENCY =P_CURRENCY
and AMOUNT_DATE <= P_CGU$SYSDATE
and RECONCILED_REFERENCE is NULL
and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
and ((CASHFLOW_AMOUNT < 0 and rec_det.DEBIT_AMOUNT is NOT NULL)
and ((abs(CASHFLOW_AMOUNT) = rec_det.DEBIT_AMOUNT and nvl(l_amount,'N') =
'Y') or (nvl(l_amount,'N') = 'N' and date_type='FORCAST')) -----???
or (nvl(rec_det.DEBIT_AMOUNT,0) = 0))
and ((CASHFLOW_AMOUNT > 0 and rec_det.CREDIT_AMOUNT is NOT NULL)
and ((abs(CASHFLOW_AMOUNT) = rec_det.CREDIT_AMOUNT
and nvl(l_amount,'N')='Y') or (nvl(l_amount,'N') = 'N'))
or (nvl(rec_det.CREDIT_AMOUNT,0) = 0))
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and nvl(l_party,'N') = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y')
and rownum < 2; -- bug 5353780
Update Xtr_Settlement_Summary
Set status = 'R'
Where settlement_number = C2_Rec.Settlement_Number;
update XTR_DEAL_DATE_AMOUNTS
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE =
decode(DATE_TYPE,'FORCAST','^'||P_PASS_CODE,P_PASS_CODE),
RECONCILED_DAYS_ADJUST = (trunc(rec_det.VALUE_DATE) -
trunc(l_one_date)),
AMOUNT_DATE =
decode(DATE_TYPE,'FORCAST',rec_det.VALUE_DATE,AMOUNT_DATE),
DATE_TYPE = decode(DATE_TYPE,'FORCAST','SETTLE',DATE_TYPE),
AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(AMOUNT,0),0,AMOUNT,abs(nvl(l_sum_amt,0))),AMOUNT),
HCE_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(HCE_AMOUNT,0),0,HCE_AMOUNT,
(AMOUNT/HCE_AMOUNT)*abs(nvl(l_sum_amt,0))),HCE_AMOUNT),
CASHFLOW_AMOUNT =
decode(DATE_TYPE,'FORCAST',decode(nvl(CASHFLOW_AMOUNT,0),0,
CASHFLOW_AMOUNT,nvl(l_sum_amt,0)),CASHFLOW_AMOUNT)
where
/*
((to_char(DEAL_NUMBER) = ltrim((rtrim(rec_det.PARTICULARS))
and nvl(l_deal_no,'N') = 'Y')
or (l_deal_no is NULL )) --- modify
and
*/
AMOUNT_DATE = l_one_date
and NETOFF_NUMBER = v_netoff_number
and AMOUNT_TYPE <> 'FACEVAL'
and CURRENCY =P_CURRENCY
and AMOUNT_DATE <= P_CGU$SYSDATE
and RECONCILED_REFERENCE is NULL
and RECONCILED_PASS_CODE is NULL
and ((ACCOUNT_NO = P_ACCOUNT_NUMBER and nvl(l_bk_acct,'N') = 'Y') or
(nvl(l_bk_acct,'%') = '%'))
/*
and ((CASHFLOW_AMOUNT < 0 and rec_det.DEBIT_AMOUNT is NOT NULL)
and ((abs(CASHFLOW_AMOUNT) = rec_det.DEBIT_AMOUNT and nvl(l_amount,'N') =
'Y') or (nvl(l_amount,'N') = 'N' and date_type='FORCAST')) -----???
or (nvl(rec_det.DEBIT_AMOUNT,0) = 0))
and ((CASHFLOW_AMOUNT > 0 and rec_det.CREDIT_AMOUNT is NOT NULL)
and ((abs(CASHFLOW_AMOUNT) = rec_det.CREDIT_AMOUNT
and nvl(l_amount,'N')='Y') or (nvl(l_amount,'N') = 'N'))
or (nvl(rec_det.CREDIT_AMOUNT,0) = 0))
*/
and ((CPARTY_CODE = substr(rec_det.PARTY_NAME,1,7)
and nvl(l_party,'N') = 'Y') or (l_party is NULL))
and ((SERIAL_REFERENCE = rtrim(rec_det.SERIAL_REFERENCE)
and nvl(l_serial_ref,'N') = 'Y') or (l_serial_ref is NULL))
and DEAL_TYPE like nvl(l_deal_type ,'%')
and DEAL_SUBTYPE like nvl(l_subtype,'%')
and PRODUCT_TYPE like nvl(l_product,'%')
and PORTFOLIO_CODE like nvl(l_portfolio,'%')
and ((date_type <>'FORCAST' and NVL(P_INCL_RTM, 'N')='N') or NVL(P_INCL_RTM, 'N')='Y');
Update Xtr_Settlement_Summary
Set status = 'R'
Where settlement_summary_id = C1_Rec.net_ID;
xtr_debug_pkg.debug('P_RECONCILE: ' || '>>>>> UPDATE XTR_PAY_REC_REOCNCILIATION ');
update XTR_PAY_REC_RECONCILIATION
set RECONCILED_REFERENCE = l_rec_nos,
RECONCILED_PASS_CODE = P_PASS_CODE
where CURRENT OF REC;