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');