| index

View V_MECO

Columns

Name | Type | Optional | Default | Comments
METR_CODE | VARCHAR2(80) | Y |  |
SELLING_PRICE | NUMBER(,2) |  |  |
COST_PRICE | NUMBER(,2) | Y |  |
DELIVERY_COST | NUMBER(,2) | Y |  |
REGI_CODE | VARCHAR2(80) | Y |  |
EFFECTIVE_FROM | DATE | Y |  |
EFFECTIVE_TO | DATE | Y |  |

SQL

    CREATE OR REPLACE VIEW V_MECO AS
    SELECT metr_code
          ,selling_price
          ,cost_price
          ,delivery_cost
          ,regi_code
          ,valid_from AS effective_from
          ,least(nvl(valid_to
                    ,to_date('2099'
                            ,'yyyy'))
                ,nvl(next_valid_from
                    ,to_date('2099'
                            ,'yyyy'))) AS effective_to
      FROM (SELECT metr_code
                  ,selling_price
                  ,cost_price
                  ,delivery_cost
                  ,regi_code
                  ,valid_from
                  ,valid_to
                  ,lead(valid_from) over(PARTITION BY cost.metr_code, regi_code ORDER BY valid_from) AS next_valid_from
              FROM costs cost
             WHERE cost_type = 'MECO');