TRIGGER: APPS.XTR_BU_ROLLOVER_TRANSACTIONS_T

Source

Description

"APPS"."XTR_BU_ROLLOVER_TRANSACTIONS_T" 
 BEFORE UPDATE
 ON  "XTR"."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;