I need help understanding why Oracle 11.2.0.3 ignores an index for an updatable view. When I reference the base table, offer$, I get index access. When I reference the view, offer, I get a full scan of offer$. There is an instead of trigger on the view. Does that factor into it somehow?
> EXPLAIN PLAN FOR
UPDATE OFFER$ SET PRESENTABLE_FLAG = 'F', ACCEPTED_OFFER_FLAG ='T' WHERE ASSIGNED_OFFER_ID = (SELECT SOLICITATION_REF_ID FROM SOLICITATION WHERE SOLICITATION_ID = :B1 )
plan FOR succeeded.
> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null,null,'ALL'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3047622554
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | UPDATE | OFFER$ | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | OFFER$ | 1 | 25 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | AK_OFFER_ASSIGNED_OFFER_ID | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| SOLICITATION$ | 1 | 20 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_SOLICITATION$ | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - UPD$1 / OFFER$@UPD$1
3 - UPD$1 / OFFER$@UPD$1
4 - SEL$F5BB74E1 / SOLICITATION$@SEL$2
5 - SEL$F5BB74E1 / SOLICITATION$@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ASSIGNED_OFFER_ID"= (SELECT "SOLICITATION_REF_ID" FROM KOHP."SOLICITATION$"
"SOLICITATION$" WHERE "SOLICITATION_ID"=TO_NUMBER(:B1)))
5 - access("SOLICITATION_ID"=TO_NUMBER(:B1))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (upd=4,6,7; cmp=3,7; cpy=2,4,5) "OFFER$".ROWID[ROWID,10], "OFFER$"."DECISION_ID"[NUMBER,22],
"ASSIGNED_OFFER_ID"[VARCHAR2,255], "ACCEPTED_OFFER_FLAG"[CHARACTER,1],
"OFFER$"."CURRENT_OFFER_FLAG"[CHARACTER,1], "PRESENTABLE_FLAG"[CHARACTER,1],
"OFFER$"."CHNG_DTTM"[TIMESTAMP,11]
3 - "OFFER$".ROWID[ROWID,10], "ASSIGNED_OFFER_ID"[VARCHAR2,255]
4 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22],
"SOLICITATION_REF_ID"[VARCHAR2,50]
5 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22]
40 rows selected
> EXPLAIN PLAN FOR
UPDATE OFFER SET PRESENTABLE_FLAG = 'F', ACCEPTED_OFFER_FLAG ='T' WHERE ASSIGNED_OFFER_ID = (SELECT SOLICITATION_REF_ID FROM SOLICITATION WHERE SOLICITATION_ID = :B1 )
plan FOR succeeded.
> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null,null,'ALL'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1083159671
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 958K| 987M| 9182 (1)| 00:01:51 |
| 1 | UPDATE | OFFER | | | | |
|* 2 | VIEW | OFFER | 958K| 987M| 9181 (1)| 00:01:51 |
| 3 | TABLE ACCESS FULL | OFFER$ | 958K| 127M| 9181 (1)| 00:01:51 |
| 4 | TABLE ACCESS BY INDEX ROWID| SOLICITATION$ | 1 | 20 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_SOLICITATION$ | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - UPD$1
2 - SEL$1 / OFFER@UPD$1
3 - SEL$1 / OFFER$@SEL$1
4 - SEL$335DD26A / SOLICITATION$@SEL$3
5 - SEL$335DD26A / SOLICITATION$@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ASSIGNED_OFFER_ID"= (SELECT "SOLICITATION_REF_ID" FROM
KOHP."SOLICITATION$" "SOLICITATION$" WHERE "SOLICITATION_ID"=TO_NUMBER(:B1)))
5 - access("SOLICITATION_ID"=TO_NUMBER(:B1))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (upd=13,45; cmp=1,2,3,4,5,6,7,8,9,10,11,12,14,15,16,17,18,19,20,21,22,23,24,25,26,2
7,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,46,47,48,49,50)
"OFFER"."OFFER_ID"[NUMBER,22], "OFFER"."DECISION_ID"[NUMBER,22],
"OFFER"."OFFER_TYPE_CD"[NUMBER,22], "OFFER"."PROMO_ID"[NUMBER,22],
"OFFER"."PYMT_METHOD_TYPE_CD"[NUMBER,22], "OFFER"."CS_RESULT_ID"[NUMBER,22],
"OFFER"."CS_RESULT_USAGE_TYPE_CD"[NUMBER,22], "OFFER"."RATE_INDEX_TYPE_CD"[NUMBER,22],
"OFFER"."SUB_PRODUCT_ID"[NUMBER,22], "OFFER"."CAMPAIGN_ID"[NUMBER,22],
"OFFER"."MARKET_CELL_ID"[NUMBER,22], "ASSIGNED_OFFER_ID"[VARCHAR2,255],
"ACCEPTED_OFFER_FLAG"[CHARACTER,1], "OFFER"."CURRENT_OFFER_FLAG"[CHARACTER,1],
"OFFER"."OFFER_GOOD_UNTIL_DATE"[DATE,7], "OFFER"."RESCINDABLE_DAYS"[NUMBER,22],
"OFFER"."RESCINDED_DATE"[DATE,7], "OFFER"."AMOUNT"[NUMBER,22],
"OFFER"."MAX_AMOUNT"[NUMBER,22], "OFFER"."AMOUNT_FINANCED"[NUMBER,22],
"OFFER"."DOWN_PYMT"[NUMBER,22], "OFFER"."RATE"[NUMBER,22], "OFFER"."TERM_MM"[NUMBER,22],
"OFFER"."ORIGINATION_FEE_AMOUNT"[NUMBER,22], "OFFER"."ORIGINATION_FEE_RATE"[NUMBER,22],
"OFFER"."FINANCE_CHARGE"[NUMBER,22], "OFFER"."NBR_OF_PYMTS"[NUMBER,22],
"OFFER"."PYMT"[NUMBER,22], "OFFER"."TOTAL_PYMTS"[NUMBER,22],
"OFFER"."FIRST_PYMT_DATE"[DATE,7], "OFFER"."CONTRACT_DATE"[DATE,7],
"OFFER"."ACCT_NBR"[VARCHAR2,20], "OFFER"."ACCT_NBR_ASSIGNED_DTTM"[TIMESTAMP,11],
"OFFER"."ACCT_EXPIRATION_DTTM"[DATE,8], "OFFER"."OFFER_DESC"[VARCHAR2,255],
"OFFER"."MIN_RATE"[NUMBER,22], "OFFER"."MAX_RATE"[NUMBER,22],
"OFFER"."MIN_AMOUNT"[NUMBER,22], "OFFER"."ANNUAL_FEE_AMOUNT"[NUMBER,22],
"OFFER"."ANNUAL_FEE_WAIVED_MM"[NUMBER,22], "OFFER"."LATE_FEE_PERCENT"[NUMBER,22],
"OFFER"."LATE_FEE_MIN_AMOUNT"[NUMBER,22], "OFFER"."OFFER_SALES_SCRIPT"[VARCHAR2,500],
"OFFER"."OFFER_ORDER"[NUMBER,22], "PRESENTABLE_FLAG"[CHARACTER,1],
"OFFER"."INDEX_RATE"[NUMBER,22], "OFFER"."ACTV_FLAG"[VARCHAR2,1],
"OFFER"."CORRELATION_ID"[VARCHAR2,64], "OFFER"."OFFER_STATUS_TYPE_CD"[NUMBER,22],
"OFFER"."PRESENTATION_INSTRUMENT_NBR"[VARCHAR2,20]
3 - "OFFER_ID"[NUMBER,22], "DECISION_ID"[NUMBER,22], "OFFER_TYPE_CD"[NUMBER,22],
"PROMO_ID"[NUMBER,22], "PYMT_METHOD_TYPE_CD"[NUMBER,22], "CS_RESULT_ID"[NUMBER,22],
"CS_RESULT_USAGE_TYPE_CD"[NUMBER,22], "RATE_INDEX_TYPE_CD"[NUMBER,22],
"SUB_PRODUCT_ID"[NUMBER,22], "CAMPAIGN_ID"[NUMBER,22], "MARKET_CELL_ID"[NUMBER,22],
"ASSIGNED_OFFER_ID"[VARCHAR2,255], "ACCEPTED_OFFER_FLAG"[CHARACTER,1],
"CURRENT_OFFER_FLAG"[CHARACTER,1], "OFFER_GOOD_UNTIL_DATE"[DATE,7],
"RESCINDABLE_DAYS"[NUMBER,22], "RESCINDED_DATE"[DATE,7], "AMOUNT"[NUMBER,22],
"MAX_AMOUNT"[NUMBER,22], "AMOUNT_FINANCED"[NUMBER,22], "DOWN_PYMT"[NUMBER,22],
"RATE"[NUMBER,22], "TERM_MM"[NUMBER,22], "ORIGINATION_FEE_AMOUNT"[NUMBER,22],
"ORIGINATION_FEE_RATE"[NUMBER,22], "FINANCE_CHARGE"[NUMBER,22],
"NBR_OF_PYMTS"[NUMBER,22], "PYMT"[NUMBER,22], "TOTAL_PYMTS"[NUMBER,22],
"FIRST_PYMT_DATE"[DATE,7], "CONTRACT_DATE"[DATE,7], "ACCT_NBR$"[RAW,40],
"ACCT_NBR_ASSIGNED_DTTM"[TIMESTAMP,11], "ACCT_EXPIRATION_DTTM$"[RAW,40],
"OFFER_DESC"[VARCHAR2,255], "MIN_RATE"[NUMBER,22], "MAX_RATE"[NUMBER,22],
"MIN_AMOUNT"[NUMBER,22], "ANNUAL_FEE_AMOUNT"[NUMBER,22],
"ANNUAL_FEE_WAIVED_MM"[NUMBER,22], "LATE_FEE_PERCENT"[NUMBER,22],
"LATE_FEE_MIN_AMOUNT"[NUMBER,22], "OFFER_SALES_SCRIPT"[VARCHAR2,500],
"OFFER_ORDER"[NUMBER,22], "PRESENTABLE_FLAG"[CHARACTER,1], "INDEX_RATE"[NUMBER,22],
"ACTV_FLAG"[VARCHAR2,1], "CORRELATION_ID"[VARCHAR2,64],
"OFFER_STATUS_TYPE_CD"[NUMBER,22], "PRESENTATION_INSTRUMENT_NBR$"[RAW,40]
4 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22],
"SOLICITATION_REF_ID"[VARCHAR2,50]
5 - "SOLICITATION$".ROWID[ROWID,10], "SOLICITATION_ID"[NUMBER,22]
82 rows selected
CREATE OR REPLACE FORCE VIEW "OFFER" ("OFFER_ID","DECISION_ID","OFFER_TYPE_CD","PROMO_ID","PYMT_METHOD_TYPE_CD","CS_RESULT_ID","CS_RESULT_USAGE_TYPE_CD","RATE_INDEX_TYPE_CD","SUB_PRODUCT_ID","CAMPAIGN_ID","MARKET_CELL_ID","ASSIGNED_OFFER_ID","ACCEPTED_OFFER_FLAG","CURRENT_OFFER_FLAG","OFFER_GOOD_UNTIL_DATE","RESCINDABLE_DAYS","RESCINDED_DATE","AMOUNT","MAX_AMOUNT","AMOUNT_FINANCED","DOWN_PYMT","RATE","TERM_MM","ORIGINATION_FEE_AMOUNT","ORIGINATION_FEE_RATE","FINANCE_CHARGE","NBR_OF_PYMTS","PYMT","TOTAL_PYMTS","FIRST_PYMT_DATE","CONTRACT_DATE","ACCT_NBR","ACCT_NBR_ASSIGNED_DTTM","ACCT_EXPIRATION_DTTM","OFFER_DESC","MIN_RATE","MAX_RATE","MIN_AMOUNT","ANNUAL_FEE_AMOUNT","ANNUAL_FEE_WAIVED_MM","LATE_FEE_PERCENT","LATE_FEE_MIN_AMOUNT","OFFER_SALES_SCRIPT","OFFER_ORDER","PRESENTABLE_FLAG","INDEX_RATE","INSRT_DTTM","INSRT_USR_ID","CHNG_DTTM","CHNG_USR_ID","ACTV_FLAG","CORRELATION_ID","OFFER_STATUS_TYPE_CD","PRESENTATION_INSTRUMENT_NBR")
AS
SELECT
OFFER_ID
,DECISION_ID
,OFFER_TYPE_CD
,PROMO_ID
,PYMT_METHOD_TYPE_CD
,CS_RESULT_ID
,CS_RESULT_USAGE_TYPE_CD
,RATE_INDEX_TYPE_CD
,SUB_PRODUCT_ID
,CAMPAIGN_ID
,MARKET_CELL_ID
,ASSIGNED_OFFER_ID
,ACCEPTED_OFFER_FLAG
,CURRENT_OFFER_FLAG
,OFFER_GOOD_UNTIL_DATE
,RESCINDABLE_DAYS
,RESCINDED_DATE
,AMOUNT
,MAX_AMOUNT
,AMOUNT_FINANCED
,DOWN_PYMT
,RATE
,TERM_MM
,ORIGINATION_FEE_AMOUNT
,ORIGINATION_FEE_RATE
,FINANCE_CHARGE
,NBR_OF_PYMTS
,PYMT
,TOTAL_PYMTS
,FIRST_PYMT_DATE
,CONTRACT_DATE
,CAST ( zcrypto.decrypt ( ACCT_NBR$ ) AS VARCHAR2 ( 20 ) ) ACCT_NBR
,ACCT_NBR_ASSIGNED_DTTM
,TO_DATE ( zcrypto.decrypt ( ACCT_EXPIRATION_DTTM$ ) ) ACCT_EXPIRATION_DTTM
,OFFER_DESC
,MIN_RATE
,MAX_RATE
,MIN_AMOUNT
,ANNUAL_FEE_AMOUNT
,ANNUAL_FEE_WAIVED_MM
,LATE_FEE_PERCENT
,LATE_FEE_MIN_AMOUNT
,OFFER_SALES_SCRIPT
,OFFER_ORDER
,PRESENTABLE_FLAG
,INDEX_RATE
,INSRT_DTTM
,INSRT_USR_ID
,CHNG_DTTM
,CHNG_USR_ID
,ACTV_FLAG
,CORRELATION_ID
,OFFER_STATUS_TYPE_CD
,CAST ( zcrypto.decrypt ( PRESENTATION_INSTRUMENT_NBR$ ) AS VARCHAR2 ( 20 ) ) PRESENTATION_INSTRUMENT_NBR
FROM
OFFER$;
CREATE OR REPLACE TRIGGER "OFFER_IO" INSTEAD OF
INSERT OR
UPDATE
ON OFFER REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN IF ( INSERTING ) THEN
INSERT
INTO
OFFER$
(
OFFER_ID
,DECISION_ID
,OFFER_TYPE_CD
,PROMO_ID
,PYMT_METHOD_TYPE_CD
,CS_RESULT_ID
,CS_RESULT_USAGE_TYPE_CD
,RATE_INDEX_TYPE_CD
,SUB_PRODUCT_ID
,CAMPAIGN_ID
,MARKET_CELL_ID
,ASSIGNED_OFFER_ID
,ACCEPTED_OFFER_FLAG
,CURRENT_OFFER_FLAG
,OFFER_GOOD_UNTIL_DATE
,RESCINDABLE_DAYS
,RESCINDED_DATE
,AMOUNT
,MAX_AMOUNT
,AMOUNT_FINANCED
,DOWN_PYMT
,RATE
,TERM_MM
,ORIGINATION_FEE_AMOUNT
,ORIGINATION_FEE_RATE
,FINANCE_CHARGE
,NBR_OF_PYMTS
,PYMT
,TOTAL_PYMTS
,FIRST_PYMT_DATE
,CONTRACT_DATE
,ACCT_NBR$
,ACCT_NBR_ASSIGNED_DTTM
,ACCT_EXPIRATION_DTTM$
,OFFER_DESC
,MIN_RATE
,MAX_RATE
,MIN_AMOUNT
,ANNUAL_FEE_AMOUNT
,ANNUAL_FEE_WAIVED_MM
,LATE_FEE_PERCENT
,LATE_FEE_MIN_AMOUNT
,OFFER_SALES_SCRIPT
,OFFER_ORDER
,PRESENTABLE_FLAG
,INDEX_RATE
,ACTV_FLAG
,CORRELATION_ID
,OFFER_STATUS_TYPE_CD
,PRESENTATION_INSTRUMENT_NBR$
)
VALUES
(
:NEW.OFFER_ID
,:NEW.DECISION_ID
,:NEW.OFFER_TYPE_CD
,:NEW.PROMO_ID
,:NEW.PYMT_METHOD_TYPE_CD
,:NEW.CS_RESULT_ID
,:NEW.CS_RESULT_USAGE_TYPE_CD
,:NEW.RATE_INDEX_TYPE_CD
,:NEW.SUB_PRODUCT_ID
,:NEW.CAMPAIGN_ID
,:NEW.MARKET_CELL_ID
,:NEW.ASSIGNED_OFFER_ID
,:NEW.ACCEPTED_OFFER_FLAG
,:NEW.CURRENT_OFFER_FLAG
,:NEW.OFFER_GOOD_UNTIL_DATE
,:NEW.RESCINDABLE_DAYS
,:NEW.RESCINDED_DATE
,:NEW.AMOUNT
,:NEW.MAX_AMOUNT
,:NEW.AMOUNT_FINANCED
,:NEW.DOWN_PYMT
,:NEW.RATE
,:NEW.TERM_MM
,:NEW.ORIGINATION_FEE_AMOUNT
,:NEW.ORIGINATION_FEE_RATE
,:NEW.FINANCE_CHARGE
,:NEW.NBR_OF_PYMTS
,:NEW.PYMT
,:NEW.TOTAL_PYMTS
,:NEW.FIRST_PYMT_DATE
,:NEW.CONTRACT_DATE
,zcrypto.encrypt ( :NEW.ACCT_NBR )
,:NEW.ACCT_NBR_ASSIGNED_DTTM
,zcrypto.encrypt ( :NEW.ACCT_EXPIRATION_DTTM )
,:NEW.OFFER_DESC
,:NEW.MIN_RATE
,:NEW.MAX_RATE
,:NEW.MIN_AMOUNT
,:NEW.ANNUAL_FEE_AMOUNT
,:NEW.ANNUAL_FEE_WAIVED_MM
,:NEW.LATE_FEE_PERCENT
,:NEW.LATE_FEE_MIN_AMOUNT
,:NEW.OFFER_SALES_SCRIPT
,:NEW.OFFER_ORDER
,:NEW.PRESENTABLE_FLAG
,:NEW.INDEX_RATE
,:NEW.ACTV_FLAG
,:NEW.CORRELATION_ID
,:NEW.OFFER_STATUS_TYPE_CD
,zcrypto.encrypt ( :NEW.PRESENTATION_INSTRUMENT_NBR )
);
ELSE
UPDATE
OFFER$
SET
DECISION_ID = :NEW.DECISION_ID
,OFFER_TYPE_CD = :NEW.OFFER_TYPE_CD
,PROMO_ID = :NEW.PROMO_ID
,PYMT_METHOD_TYPE_CD = :NEW.PYMT_METHOD_TYPE_CD
,CS_RESULT_ID = :NEW.CS_RESULT_ID
,CS_RESULT_USAGE_TYPE_CD = :NEW.CS_RESULT_USAGE_TYPE_CD
,RATE_INDEX_TYPE_CD = :NEW.RATE_INDEX_TYPE_CD
,SUB_PRODUCT_ID = :NEW.SUB_PRODUCT_ID
,CAMPAIGN_ID = :NEW.CAMPAIGN_ID
,MARKET_CELL_ID = :NEW.MARKET_CELL_ID
,ASSIGNED_OFFER_ID = :NEW.ASSIGNED_OFFER_ID
,ACCEPTED_OFFER_FLAG = :NEW.ACCEPTED_OFFER_FLAG
,CURRENT_OFFER_FLAG = :NEW.CURRENT_OFFER_FLAG
,OFFER_GOOD_UNTIL_DATE = :NEW.OFFER_GOOD_UNTIL_DATE
,RESCINDABLE_DAYS = :NEW.RESCINDABLE_DAYS
,RESCINDED_DATE = :NEW.RESCINDED_DATE
,AMOUNT = :NEW.AMOUNT
,MAX_AMOUNT = :NEW.MAX_AMOUNT
,AMOUNT_FINANCED = :NEW.AMOUNT_FINANCED
,DOWN_PYMT = :NEW.DOWN_PYMT
,RATE = :NEW.RATE
,TERM_MM = :NEW.TERM_MM
,ORIGINATION_FEE_AMOUNT = :NEW.ORIGINATION_FEE_AMOUNT
,ORIGINATION_FEE_RATE = :NEW.ORIGINATION_FEE_RATE
,FINANCE_CHARGE = :NEW.FINANCE_CHARGE
,NBR_OF_PYMTS = :NEW.NBR_OF_PYMTS
,PYMT = :NEW.PYMT
,TOTAL_PYMTS = :NEW.TOTAL_PYMTS
,FIRST_PYMT_DATE = :NEW.FIRST_PYMT_DATE
,CONTRACT_DATE = :NEW.CONTRACT_DATE
,ACCT_NBR$ = zcrypto.encrypt ( :NEW.ACCT_NBR )
,ACCT_NBR_ASSIGNED_DTTM = :NEW.ACCT_NBR_ASSIGNED_DTTM
,ACCT_EXPIRATION_DTTM$ = zcrypto.encrypt ( :NEW.ACCT_EXPIRATION_DTTM )
,OFFER_DESC = :NEW.OFFER_DESC
,MIN_RATE = :NEW.MIN_RATE
,MAX_RATE = :NEW.MAX_RATE
,MIN_AMOUNT = :NEW.MIN_AMOUNT
,ANNUAL_FEE_AMOUNT = :NEW.ANNUAL_FEE_AMOUNT
,ANNUAL_FEE_WAIVED_MM = :NEW.ANNUAL_FEE_WAIVED_MM
,LATE_FEE_PERCENT = :NEW.LATE_FEE_PERCENT
,LATE_FEE_MIN_AMOUNT = :NEW.LATE_FEE_MIN_AMOUNT
,OFFER_SALES_SCRIPT = :NEW.OFFER_SALES_SCRIPT
,OFFER_ORDER = :NEW.OFFER_ORDER
,PRESENTABLE_FLAG = :NEW.PRESENTABLE_FLAG
,INDEX_RATE = :NEW.INDEX_RATE
,ACTV_FLAG = :NEW.ACTV_FLAG
,CORRELATION_ID = :NEW.CORRELATION_ID
,OFFER_STATUS_TYPE_CD = :NEW.OFFER_STATUS_TYPE_CD
,PRESENTATION_INSTRUMENT_NBR$ = zcrypto.encrypt ( :NEW.PRESENTATION_INSTRUMENT_NBR )
WHERE
OFFER_ID = :NEW.OFFER_ID;
END IF;
END;
/