| index

View V_AICO

Columns

Name | Type | Optional | Default | Comments
ID | NUMBER | Y |  |
ADIT_CODE | VARCHAR2(240) | Y |  |
SELLING_PRICE | NUMBER(,2) | Y |  |
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_AICO AS
    SELECT id
          ,adit_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 id
                  ,adit_code
                  ,selling_price
                  ,cost_price
                  ,delivery_cost
                  ,regi_code
                  ,valid_from
                  ,valid_to
                  ,lead(valid_from) over(PARTITION BY cost.adit_code, regi_code ORDER BY valid_from) AS next_valid_from
              FROM costs cost
             WHERE cost_type = 'AICO');