[Home] [Help]
TRIGGER: APPS.XTR_BU_ROLLOVER_TRANSACTIONS_T
Source
Description
XTR_BU_ROLLOVER_TRANSACTIONS_T
BEFORE UPDATE on XTR_ROLLOVER_TRANSACTIONS
FOR EACH ROW
Type
BEFORE EACH ROW
Event
UPDATE
Column
When
Referencing
REFERENCING NEW AS NEW OLD AS OLD
Body
declare
cursor CHK_AUDIT(p_event varchar2) is
select nvl(AUDIT_YN,'N')
from XTR_SETUP_AUDIT_REQMTS
where rtrim(EVENT) = p_event;
--
l_val VARCHAR2(1);
--
begin
--
if :NEW.STATUS_CODE = 'CANCELLED' and :OLD.STATUS_CODE <> 'CANCELLED' then
XTR_JOURNAL_PROCESS_P.UPDATE_JOURNALS(:NEW.DEAL_NUMBER,
:NEW.TRANSACTION_NUMBER,
:NEW.DEAL_TYPE);
elsif nvl(:OLD.DUAL_AUTHORISATION_BY,'@#@') <>
nvl(:NEW.DUAL_AUTHORISATION_BY,'@#@') then
update XTR_DEAL_DATE_AMOUNTS
set DUAL_AUTHORISATION_ON = :NEW.DUAL_AUTHORISATION_ON,
DUAL_AUTHORISATION_BY = :NEW.DUAL_AUTHORISATION_BY
where DEAL_NUMBER = :NEW.DEAL_NUMBER
and TRANSACTION_NUMBER = :NEW.TRANSACTION_NUMBER
and DEAL_TYPE = :NEW.DEAL_TYPE;
end if;
if :OLD.DEAL_TYPE <> 'TMM' then
-- Do not yet do for TMM due to recalc nature of all forward rows
-- we Audit TMM changes in the table TERM_ACTIONS
-- Check that Audit on this table has been specified
open CHK_AUDIT('TRANSACTIONS');
fetch CHK_AUDIT INTO l_val;
if CHK_AUDIT%NOTFOUND then
l_val := 'N';
end if;
close CHK_AUDIT;
-- Copy to Audit Table the Pre-Updated row
if nvl(upper(l_val),'N') = 'Y' then
INSERT INTO XTR_A_ROLLOVER_TRANSACTIONS(
DEAL_NUMBER, TRANSACTION_NUMBER, DEAL_TYPE,
START_DATE, MATURITY_DATE, NO_OF_DAYS,
BALANCE_OUT_BF, BALANCE_OUT_BF_HCE, BALANCE_OUT,
BALANCE_OUT_HCE, ACCUM_INTEREST_BF,
ACCUM_INTEREST_BF_HCE, ACCUM_INTEREST,
ACCUM_INTEREST_HCE, PRINCIPAL_ADJUST,
PRINCIPAL_ADJUST_HCE, INTEREST_RATE, INTEREST,
INTEREST_HCE, INTEREST_SETTLED, CREATED_BY,
CREATED_ON, UPDATED_BY, UPDATED_ON, COMPANY_CODE,
PRINCIPAL_AMOUNT_TYPE, PRINCIPAL_ACTION,
PRINCIPAL_ACCOUNT_NO, ACCUM_INT_AMOUNT_TYPE,
ACCUM_INT_ACTION, ACCUM_INT_ACCOUNT_NO,
BAL_OS_ACCOUNT_NO, SETTLE_DATE, DEAL_SUBTYPE,
CURRENCY, DEAL_DATE, PRODUCT_TYPE, SECURITY_TYPE,
STATUS_CODE, CPARTY_CODE, CLIENT_CODE,
DEALER_CODE, DUAL_AUTHORISATION_BY, BROKERAGE_CODE,
BROKERAGE_RATE, BROKERAGE_AMOUNT,
BROKERAGE_AMOUNT_HCE, TAX_CODE, TAX_RATE,
TAX_AMOUNT, TAX_AMOUNT_HCE, REPAY_AMOUNT,
REPAY_AMOUNT_HCE, SETTLEMENT_TYPE, INTEREST_FREQ,
INTEREST_SETTLEMENT_TYPE, COMMENTS,
CROSS_REF_TO_TRANS, PARENT_PARTY, PORTFOLIO_CODE,
PARCEL_SPLIT_NO, TRANS_CLOSEOUT_NO, NI_RENEG_DATE,
NI_PROFIT_LOSS, NI_PROFIT_LOSS_HCE, ACCEPTOR_CODE,
ACCEPTOR_NAME, ENDORSER_CODE, ENDORSER_NAME,
DRAWER_CODE, DRAWER_NAME, LIMIT_CODE, PRINTED_YN,
OLD_PRODUCT_TYPE, FID_TAX, FID_TAX_HCE,
TAX_SETTLED_REFERENCE, BKGE_SETTLED_REFERENCE,
ARCHIVE_DATE, ARCHIVE_BY, CLIENT_ADVICE,
ISSUER_ADVICE, DUAL_AUTHORISATION_ON,
DEAL_LINKING_CODE, RENEG_ONLY_LETTER, AUDIT_INDICATOR,
CPARTY_ADVICE, PI_AMOUNT_DUE, PI_AMOUNT_RECEIVED,
DATE_RECEIVED, ADJUSTED_BALANCE,
RATE_EFFECTIVE_CREATED, EXPECTED_BALANCE_BF,
EXPECTED_BALANCE_OUT, ACCRUAL_FROM,
ACCRUAL_TO, YEAR_BASIS, AUDIT_DATE_STORED)
VALUES (
:old.DEAL_NUMBER, :old.TRANSACTION_NUMBER, :old.DEAL_TYPE,
:old.START_DATE, :old.MATURITY_DATE, :old.NO_OF_DAYS,
:old.BALANCE_OUT_BF, :old.BALANCE_OUT_BF_HCE, :old.BALANCE_OUT,
:old.BALANCE_OUT_HCE, :old.ACCUM_INTEREST_BF,
:old.ACCUM_INTEREST_BF_HCE, :old.ACCUM_INTEREST,
:old.ACCUM_INTEREST_HCE, :old.PRINCIPAL_ADJUST,
:old.PRINCIPAL_ADJUST_HCE, :old.INTEREST_RATE, :old.INTEREST,
:old.INTEREST_HCE, :old.INTEREST_SETTLED, :old.CREATED_BY,
:old.CREATED_ON, :new.UPDATED_BY, sysdate, :old.COMPANY_CODE,
:old.PRINCIPAL_AMOUNT_TYPE, :old.PRINCIPAL_ACTION,
:old.PRINCIPAL_ACCOUNT_NO, :old.ACCUM_INT_AMOUNT_TYPE,
:old.ACCUM_INT_ACTION, :old.ACCUM_INT_ACCOUNT_NO,
:old.BAL_OS_ACCOUNT_NO, :old.SETTLE_DATE, :old.DEAL_SUBTYPE,
:old.CURRENCY, :old.DEAL_DATE, :old.PRODUCT_TYPE, :old.SECURITY_TYPE,
:old.STATUS_CODE, :old.CPARTY_CODE, :old.CLIENT_CODE,
:old.DEALER_CODE, :old.DUAL_AUTHORISATION_BY, :old.BROKERAGE_CODE,
:old.BROKERAGE_RATE, :old.BROKERAGE_AMOUNT,
:old.BROKERAGE_AMOUNT_HCE, :old.TAX_CODE, :old.TAX_RATE,
:old.TAX_AMOUNT, :old.TAX_AMOUNT_HCE, :old.REPAY_AMOUNT,
:old.REPAY_AMOUNT_HCE, :old.SETTLEMENT_TYPE, :old.INTEREST_FREQ,
:old.INTEREST_SETTLEMENT_TYPE, :old.COMMENTS,
:old.CROSS_REF_TO_TRANS, :old.PARENT_PARTY, :old.PORTFOLIO_CODE,
:old.PARCEL_SPLIT_NO, :old.TRANS_CLOSEOUT_NO, :old.NI_RENEG_DATE,
:old.NI_PROFIT_LOSS, :old.NI_PROFIT_LOSS_HCE, :old.ACCEPTOR_CODE,
:old.ACCEPTOR_NAME, :old.ENDORSER_CODE, :old.ENDORSER_NAME,
:old.DRAWER_CODE, :old.DRAWER_NAME, :old.LIMIT_CODE, :old.PRINTED_YN,
:old.OLD_PRODUCT_TYPE, :old.FID_TAX, :old.FID_TAX_HCE,
:old.TAX_SETTLED_REFERENCE, :old.BKGE_SETTLED_REFERENCE,
:old.ARCHIVE_DATE, :old.ARCHIVE_BY, :old.CLIENT_ADVICE,
:old.ISSUER_ADVICE, :old.DUAL_AUTHORISATION_ON,
:old.DEAL_LINKING_CODE, :old.RENEG_ONLY_LETTER, :old.AUDIT_INDICATOR,
:old.CPARTY_ADVICE, :old.PI_AMOUNT_DUE, :old.PI_AMOUNT_RECEIVED,
:old.DATE_RECEIVED, :old.ADJUSTED_BALANCE,
:old.RATE_EFFECTIVE_CREATED, :old.EXPECTED_BALANCE_BF,
:old.EXPECTED_BALANCE_OUT, :old.ACCRUAL_FROM,
:old.ACCRUAL_TO, :old.YEAR_BASIS,to_date(to_char(sysdate,'DD/MM/YYYY
HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS'));
end if;
end if;
end;