| index

View V_LACO

Columns

Name | Type | Optional | Default | Comments
ENTY_CODE | VARCHAR2(80) | Y |  |
METY_CODE | VARCHAR2(80) | Y |  |
SVCPT_CODE | VARCHAR2(80) | Y |  |
MESC_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_LACO AS
    SELECT enty_code
          ,mety_code
          ,svcpt_code
          ,mesc_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 enty_code
                  ,mety_code
                  ,svcpt_code
                  ,mesc_code
                  ,selling_price
                  ,cost_price
                  ,delivery_cost
                  ,regi_code
                  ,valid_from
                  ,valid_to
                  ,lead(valid_from) over(PARTITION BY enty_code, mety_code, svcpt_code, mesc_code, regi_code ORDER BY valid_from) AS next_valid_from
              FROM costs cost
             WHERE cost_type = 'LACO');