ALTER TABLE TBPEDIDO_ITEM
    ADD CONTAS NUMERIC(18,8),
    ADD FRETE NUMERIC(18,8),
    ADD COMISSAO NUMERIC(18,8),
    ADD TRIBUTOS NUMERIC(18,8);

ALTER TABLE TBPEDIDO_ITEM
    ALTER AUMENTO_FEV2018 TYPE NUMERIC(18,4);

COMMENT ON COLUMN TBPEDIDO_ITEM.CONTAS IS
'CONTAS';

COMMENT ON COLUMN TBPEDIDO_ITEM.FRETE IS
'FRETE';

COMMENT ON COLUMN TBPEDIDO_ITEM.COMISSAO IS
'COMISSAO';

COMMENT ON COLUMN TBPEDIDO_ITEM.TRIBUTOS IS
'TRIBUTOS';

ALTER TABLE TBPEDIDO_ITEM
    ADD CUSTO COMPUTED BY ((
  C_MPR + C_STP + C_MOD + C_MOI_PR + C_MOI_AB
)),

ADD GASTO COMPUTED BY ((
  CUSTO + COMISSAO + FRETE + TRIBUTOS + CONTAS
));

ALTER TABLE TBPEDIDO_ITEM ALTER CUSTO COMPUTED BY (
(C_MPR + C_STP + C_MOD + C_MOI_PR + C_MOI_AB) * (PERCENTUAL_PERDA + 1)
);

ALTER TABLE TBPEDIDO_ITEM ALTER GASTO COMPUTED BY (CUSTO + COMISSAO + FRETE + TRIBUTOS + CONTAS);


ALTER TABLE TBPEDIDO_ITEM ALTER MARGEM_CONTRIBUICAO COMPUTED BY (
  (CUSTO + GASTO) / (VALOR + COALESCE(MARGEM,0))
);

ALTER TABLE TBPEDIDO_ITEM ALTER MARGEM_CONTRIBUICAO COMPUTED BY (
  IIF(((VALOR + COALESCE(MARGEM,0)) - GASTO) > 0,
      ((VALOR + COALESCE(MARGEM,0)) - GASTO) / (VALOR + COALESCE(MARGEM,0)),0)
);

ALTER TABLE TBPEDIDO_ITEM
    ADD PERCENTUAL_IR NUMERIC(18,8);

ALTER TABLE TBPEDIDO_ITEM ALTER MARGEM_CONTRIBUICAO COMPUTED BY (
  (IIF(((VALOR + COALESCE(MARGEM,0)) - GASTO) > 0,
      ((VALOR + COALESCE(MARGEM,0)) - GASTO) / (VALOR + COALESCE(MARGEM,0)),0))
   - 
  ((IIF(((VALOR + COALESCE(MARGEM,0)) - GASTO) > 0,
      ((VALOR + COALESCE(MARGEM,0)) - GASTO) / (VALOR + COALESCE(MARGEM,0)),0))
      
  * PERCENTUAL_IR)
);


ALTER TABLE TBPEDIDO_ITEM ALTER MARGEM_CONTRIBUICAO COMPUTED BY (
  cast(IIF(((VALOR + COALESCE(MARGEM,0)) - GASTO) > 0,
      ((VALOR + COALESCE(MARGEM,0)) - GASTO) / (VALOR + COALESCE(MARGEM,0)),0) as numeric(18,4))
   - 
  (cast(IIF(((VALOR + COALESCE(MARGEM,0)) - GASTO) > 0,
      ((VALOR + COALESCE(MARGEM,0)) - GASTO) / (VALOR + COALESCE(MARGEM,0)),0) as numeric(18,4))
      
  * cast(PERCENTUAL_IR / 100 as numeric(18,4)))
);


