[Home] [Help]
TRIGGER: APPS.XTR_BU_DEALS_T
Source
Description
"APPS"."XTR_BU_DEALS_T"
BEFORE UPDATE
ON "XTR"."XTR_DEALS#" 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);
l_dummy NUMBER;
cursor CHK_LOCK_DEAL_DATE_AMOUNTS is
select 1 num
from XTR_DEAL_DATE_AMOUNTS
where DEAL_NUMBER = :NEW.DEAL_NO
and DEAL_TYPE = :NEW.DEAL_TYPE
and AMOUNT_TYPE <> 'SETTLE'
and DEAL_TYPE <> 'STOCK'
for update of DUAL_AUTHORISATION_BY NOWAIT;
--
cursor CHK_LOCK_DEAL_DATE_AMOUNTS_S is
select 1 num
from XTR_DEAL_DATE_AMOUNTS
where DEAL_NUMBER = :NEW.DEAL_NO
and DEAL_TYPE = :NEW.DEAL_TYPE
and AMOUNT_TYPE = 'SETTLE'
and DEAL_TYPE <> 'STOCK'
for update of SETTLEMENT_AUTHORISED_BY NOWAIT;
begin
--
if :NEW.STATUS_CODE = 'CANCELLED' and
:OLD.STATUS_CODE <> 'CANCELLED' then
XTR_JOURNAL_PROCESS_P.UPDATE_JOURNALS(:NEW.DEAL_NO,:NEW.DEAL_NO,
:NEW.DEAL_TYPE);
else
if nvl(:NEW.DUAL_AUTHORISATION_BY,'-1') <>
nvl(:OLD.DUAL_AUTHORISATION_BY,'-1') then
open CHK_LOCK_DEAL_DATE_AMOUNTS;
fetch CHK_LOCK_DEAL_DATE_AMOUNTS into l_dummy;
if CHK_LOCK_DEAL_DATE_AMOUNTS%FOUND then
close CHK_LOCK_DEAL_DATE_AMOUNTS;
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_NO
and DEAL_TYPE = :NEW.DEAL_TYPE
and AMOUNT_TYPE <> 'SETTLE';
else
close CHK_LOCK_DEAL_DATE_AMOUNTS;
end if;
end if;
--
if :NEW.DEAL_TYPE <> 'FXO' and
nvl(:NEW.SETTLE_DUAL_AUTHORISATION_BY,'-1') <>
nvl(:OLD.SETTLE_DUAL_AUTHORISATION_BY,'-1') then
open CHK_LOCK_DEAL_DATE_AMOUNTS_S;
fetch CHK_LOCK_DEAL_DATE_AMOUNTS_S into l_dummy;
if CHK_LOCK_DEAL_DATE_AMOUNTS_S%FOUND then
close CHK_LOCK_DEAL_DATE_AMOUNTS_S;
update XTR_DEAL_DATE_AMOUNTS
set DUAL_AUTHORISATION_ON = :NEW.SETTLE_DUAL_AUTHORISATION_ON,
DUAL_AUTHORISATION_BY = :NEW.SETTLE_DUAL_AUTHORISATION_BY
where DEAL_NUMBER = :NEW.DEAL_NO
and DEAL_TYPE = :NEW.DEAL_TYPE
and AMOUNT_TYPE = 'SETTLE';
else
close CHK_LOCK_DEAL_DATE_AMOUNTS_S;
end if;
end if;
end if;
-- 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_DEALS(
DEAL_NO, DEAL_TYPE, DEAL_SUBTYPE, STATUS_CODE,
COMPANY_CODE, CPARTY_CODE, DEAL_DATE, CREATED_BY,
CREATED_ON, UPDATED_BY, UPDATED_ON, CURRENCY,
CURRENCY_BUY, CURRENCY_SELL, DISCOUNT, EXERCISE,
CAP_OR_FLOOR, INTEREST_CUTOFF, FIXED_OR_FLOATING_RATE,
RISKPARTY_CODE, RISKPARTY_LIMIT_CODE, FXO_DEAL_NO,
FXD_DEAL_NO, LIMIT_CODE, FREQUENCY, BOND_ISSUE,
LIMIT_AMOUNT, PORTFOLIO_AMOUNT, CPARTY_REF,
COMMENTS, TRANSACTION_RATE, BASE_RATE, BASE_DATE,
VALUE_DATE, EXPIRY_DATE, RENEG_DATE, NO_OF_DAYS,
START_DATE, START_AMOUNT, START_HCE_AMOUNT,
START_ACCOUNT_NO, MATURITY_DATE, MATURITY_AMOUNT,
MATURITY_HCE_AMOUNT, MATURITY_ACCOUNT_NO,
INTEREST_AMOUNT, INTEREST_HCE_AMOUNT, INTEREST_RATE,
SETTLE_DATE, SETTLE_AMOUNT, SETTLE_HCE_AMOUNT,
SETTLE_RATE, SETTLE_NO_OF_DAYS, SETTLE_AMOUNT_TYPE,
SETTLE_ACTION, SETTLE_ACCOUNT_NO, PREMIUM_DATE,
PREMIUM_AMOUNT, PREMIUM_HCE_AMOUNT,
PREMIUM_NO_OF_DAYS, PREMIUM_AMOUNT_TYPE,
PREMIUM_ACTION, PREMIUM_ACCOUNT_NO, FACE_VALUE_AMOUNT,
FACE_VALUE_HCE_AMOUNT, OPTION_AMOUNT,
OPTION_HCE_AMOUNT, BUY_AMOUNT, BUY_HCE_AMOUNT,
BUY_ACCOUNT_NO, SELL_AMOUNT, SELL_HCE_AMOUNT,
SELL_ACCOUNT_NO, FORWARD_HCE_AMOUNT, YIELD_AMOUNT,
YIELD_HCE_AMOUNT, NI_DEAL_NO, FX_PD_DEAL_NO,
FX_RO_DEAL_NO, REF_CCY, CTY_CCY, SWAP_REF,
SECURITY_NAME, DEALER_CODE, DUAL_AUTHORISATION_BY,
EXCHANGE_CODE, COUPON_RATE, NEXT_COUPON_DATE,
COUPON_ACTION, ACCRUED_INTEREST_PRICE, CAPITAL_PRICE,
TOTAL_PRICE, TAX_CODE, TAX_RATE, TAX_AMOUNT,
TAX_AMOUNT_HCE, BROKERAGE_CODE, BROKERAGE_RATE,
BROKERAGE_AMOUNT, BROKERAGE_AMOUNT_HCE,
TRANSACTION_NO, INT_SWAP_REF, CLIENT_CODE,
ACCEPTOR_CODE, ACCEPTOR_NAME, ENDORSER_CODE,
ENDORSER_NAME, DRAWER_CODE, DRAWER_NAME,
PRODUCT_TYPE, PORTFOLIO_CODE, PRINCIPAL_SETTLED_BY,
MATURITY_BALANCE_AMOUNT, MATURITY_BALANCE_HCE_AMOUNT,
NI_PROFIT_LOSS, CLIENT_REF, FX_COVER_CROSS_REF,
PRINTED_YN, SYNTHETIC_PRINTED_YN,
DUAL_AUTHORISATION_ON, TAX_SETTLED_REFERENCE,
BKGE_SETTLED_REFERENCE, BROKERAGE_CURRENCY,
ARCHIVE_DATE, ARCHIVE_BY, OPTION_COMMENCEMENT,
OPTION_POINTS, ENTER_INTO_SWAP, CONTRACT_FEES,
CONTRACT_COMMISSION, FX_WHOLESALE_REF,
PREMIUM_CURRENCY, CONTRACT_RATE, CONTRACT_NOS,
TRANSACTION_CROSS_REF, CLIENT_ADVICE,
DEAL_LINKING_CODE, CALC_BASIS, AUDIT_INDICATOR,
CPARTY_ADVICE, PI_AMOUNT_DUE, PAYMENT_SCHEDULE_CODE,
PAYMENT_FREQ, TERM_MY, FIXED_TERM_MY,
OPTION_TYPE, BOND_RENEG_DATE,
BOND_SALE_DATE, ACCRUAL_FROM, ACCRUAL_TO,
BOND_PROFIT_LOSS, YEAR_BASIS,AUDIT_DATE_STORED,
PRICING_MODEL, MARKET_DATA_SET, INITIAL_FAIR_VALUE,
EXCHANGE_RATE_ONE, EXCHANGE_RATE_TWO, FIRST_REVAL_BATCH_ID,
LAST_REVAL_BATCH_ID, PREPAID_INTEREST,
DAY_COUNT_TYPE,ROUNDING_TYPE,SETTLE_DAY_COUNT_TYPE,
SETTLE_ROUNDING_TYPE, --bug 3430489
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15)
VALUES (:old.DEAL_NO, :old.DEAL_TYPE, :old.DEAL_SUBTYPE, :old.STATUS_CODE,
:old.COMPANY_CODE, :old.CPARTY_CODE, :old.DEAL_DATE, :old.CREATED_BY,
:old.CREATED_ON, :old.UPDATED_BY, sysdate, :old.CURRENCY,
:old.CURRENCY_BUY, :old.CURRENCY_SELL, :old.DISCOUNT, :old.EXERCISE,
:old.CAP_OR_FLOOR, :old.INTEREST_CUTOFF, :old.FIXED_OR_FLOATING_RATE,
:old.RISKPARTY_CODE, :old.RISKPARTY_LIMIT_CODE, :old.FXO_DEAL_NO,
:old.FXD_DEAL_NO, :old.LIMIT_CODE, :old.FREQUENCY, :old.BOND_ISSUE,
:old.LIMIT_AMOUNT, :old.PORTFOLIO_AMOUNT, :old.CPARTY_REF,
:old.COMMENTS, :old.TRANSACTION_RATE, :old.BASE_RATE, :old.BASE_DATE,
:old.VALUE_DATE, :old.EXPIRY_DATE, :old.RENEG_DATE, :old.NO_OF_DAYS,
:old.START_DATE, :old.START_AMOUNT, :old.START_HCE_AMOUNT,
:old.START_ACCOUNT_NO, :old.MATURITY_DATE, :old.MATURITY_AMOUNT,
:old.MATURITY_HCE_AMOUNT, :old.MATURITY_ACCOUNT_NO,
:old.INTEREST_AMOUNT, :old.INTEREST_HCE_AMOUNT, :old.INTEREST_RATE,
:old.SETTLE_DATE, :old.SETTLE_AMOUNT, :old.SETTLE_HCE_AMOUNT,
:old.SETTLE_RATE, :old.SETTLE_NO_OF_DAYS, :old.SETTLE_AMOUNT_TYPE,
:old.SETTLE_ACTION, :old.SETTLE_ACCOUNT_NO, :old.PREMIUM_DATE,
:old.PREMIUM_AMOUNT, :old.PREMIUM_HCE_AMOUNT,
:old.PREMIUM_NO_OF_DAYS, :old.PREMIUM_AMOUNT_TYPE,
:old.PREMIUM_ACTION, :old.PREMIUM_ACCOUNT_NO,
:old.FACE_VALUE_AMOUNT,
:old.FACE_VALUE_HCE_AMOUNT, :old.OPTION_AMOUNT,
:old.OPTION_HCE_AMOUNT, :old.BUY_AMOUNT, :old.BUY_HCE_AMOUNT,
:old.BUY_ACCOUNT_NO, :old.SELL_AMOUNT, :old.SELL_HCE_AMOUNT,
:old.SELL_ACCOUNT_NO, :old.FORWARD_HCE_AMOUNT, :old.YIELD_AMOUNT,
:old.YIELD_HCE_AMOUNT, :old.NI_DEAL_NO, :old.FX_PD_DEAL_NO,
:old.FX_RO_DEAL_NO, :old.REF_CCY, :old.CTY_CCY, :old.SWAP_REF,
:old.SECURITY_NAME, :old.DEALER_CODE, :old.DUAL_AUTHORISATION_BY,
:old.EXCHANGE_CODE, :old.COUPON_RATE, :old.NEXT_COUPON_DATE,
:old.COUPON_ACTION, :old.ACCRUED_INTEREST_PRICE, :old.CAPITAL_PRICE,
:old.TOTAL_PRICE, :old.TAX_CODE, :old.TAX_RATE, :old.TAX_AMOUNT,
:old.TAX_AMOUNT_HCE, :old.BROKERAGE_CODE, :old.BROKERAGE_RATE,
:old.BROKERAGE_AMOUNT, :old.BROKERAGE_AMOUNT_HCE,
:old.TRANSACTION_NO, :old.INT_SWAP_REF, :old.CLIENT_CODE,
:old.ACCEPTOR_CODE, :old.ACCEPTOR_NAME, :old.ENDORSER_CODE,
:old.ENDORSER_NAME, :old.DRAWER_CODE, :old.DRAWER_NAME,
:old.PRODUCT_TYPE, :old.PORTFOLIO_CODE, :old.PRINCIPAL_SETTLED_BY,
:old.MATURITY_BALANCE_AMOUNT, :old.MATURITY_BALANCE_HCE_AMOUNT,
:old.NI_PROFIT_LOSS, :old.CLIENT_REF, :old.FX_COVER_CROSS_REF,
:old.PRINTED_YN, :old.SYNTHETIC_PRINTED_YN,
:old.DUAL_AUTHORISATION_ON, :old.TAX_SETTLED_REFERENCE,
:old.BKGE_SETTLED_REFERENCE, :old.BROKERAGE_CURRENCY,
:old.ARCHIVE_DATE, :old.ARCHIVE_BY, :old.OPTION_COMMENCEMENT,
:old.OPTION_POINTS, :old.ENTER_INTO_SWAP, :old.CONTRACT_FEES,
:old.CONTRACT_COMMISSION, :old.FX_WHOLESALE_REF,
:old.PREMIUM_CURRENCY, :old.CONTRACT_RATE, :old.CONTRACT_NOS,
:old.TRANSACTION_CROSS_REF, :old.CLIENT_ADVICE,
:old.DEAL_LINKING_CODE, :old.CALC_BASIS, :old.AUDIT_INDICATOR,
:old.CPARTY_ADVICE, :old.PI_AMOUNT_DUE, :old.PAYMENT_SCHEDULE_CODE,
:old.PAYMENT_FREQ, :old.TERM_MY, :old.FIXED_TERM_MY,
:old.OPTION_TYPE, :old.BOND_RENEG_DATE,
:old.BOND_SALE_DATE, :old.ACCRUAL_FROM, :old.ACCRUAL_TO,
:old.BOND_PROFIT_LOSS, :old.YEAR_BASIS,
sysdate,
:old.PRICING_MODEL, :old.MARKET_DATA_SET, :old.INITIAL_FAIR_VALUE,
:old.EXCHANGE_RATE_ONE, :old.EXCHANGE_RATE_TWO, :old.FIRST_REVAL_BATCH_ID,
:old.LAST_REVAL_BATCH_ID, :old.PREPAID_INTEREST,
:old.DAY_COUNT_TYPE, :old.ROUNDING_TYPE, :old.SETTLE_DAY_COUNT_TYPE,
:old.SETTLE_ROUNDING_TYPE, --bug 3430489
:old.ATTRIBUTE_CATEGORY,
:old.ATTRIBUTE1, :old.ATTRIBUTE2, :old.ATTRIBUTE3, :old.ATTRIBUTE4,
:old.ATTRIBUTE5, :old.ATTRIBUTE6, :old.ATTRIBUTE7, :old.ATTRIBUTE8,
:old.ATTRIBUTE9, :old.ATTRIBUTE10, :old.ATTRIBUTE11, :old.ATTRIBUTE12,
:old.ATTRIBUTE13, :old.ATTRIBUTE14, :old.ATTRIBUTE15
);
end if;
exception
when app_exceptions.RECORD_LOCK_EXCEPTION then
if CHK_LOCK_DEAL_DATE_AMOUNTS%ISOPEN then
close CHK_LOCK_DEAL_DATE_AMOUNTS;
end if;
if CHK_LOCK_DEAL_DATE_AMOUNTS_S%ISOPEN then
close CHK_LOCK_DEAL_DATE_AMOUNTS_S;
end if;
end;