Skip to Main Content

Why is index not used on update of an updatable view?

zsysdbaOct 23 2013 — edited Oct 27 2013

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;

/

This post has been answered by Jonathan Lewis on Oct 27 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Nov 24 2013
Added on Oct 23 2013
1 comment
326 views