View de Produtos

CREATE OR ALTER VIEW V_VT_PRODUTOS(
  "categs",
  "codigo",
  "descricao",
  "descricaoResumida",
  "erpId",
  "quantidade",
  "quantidadeMinima",
  "inativo",
  "nome",
  "peso",
  "dataInicio",
  "dataFim",
  "valorPrecoPromocional",
  "referencia",
  "valorPreco",
  "volume_length",
  "volume_height",
  "volume_width",
  "marca",
  "codigoBarras",
  "atributos",
  "dtAlteracaoSinc",
  "dtAlteracaoSincEstoque",
  "dtAlteracaoSincPreco",
  "estabItem",
  "ncm",
  "visibilidade",
  "unidade",
  "multiploVenda",
  "custoAquis"
) AS
SELECT
  ( SELECT LIST(CI.CATEGORIA) FROM CATEGORIAITEM CI
    WHERE CI.empresa = ITEEST.EMPRESA
      AND CI.ESTABitem = ITEEST.ESTABITEM
      AND CI.ITEM = ITEEST.ITEM
   ) "categs",
  ITEEST.ITEM "codigo",
  ITEECO.HTML "descricao",
  SUBSTRING(ITEECO.HTMLRESUMIDO FROM 1 FOR 32000) "descricaoResumida",
  CAST(ITEEST.ITEM AS INTEGER) "erpId",
  EST.SALDO "quantidade",
  COALESCE(ITEECO.QTDEESGOTADO,0) "quantidadeMinima",
  CASE
    WHEN COALESCE(ITEECO.VISIBILIDADE, 1) = 1 THEN 1
    ELSE 0
  END "inativo",
  COALESCE(ITEECO.DESCRICAO,ITE.DESCRICAO) "nome",
  COALESCE(ITE.PESOITEM,ITE.PESOLIQ,0) "peso",
  ITEECO.PROMOINI "dataInicio",
  ITEECO.PROMOFIM "dataFim",
  CASE
    WHEN ITEECO.PROMOINI IS NOT NULL AND ITEECO.PROMOFIM IS NOT NULL THEN ITEEST.PRPROMOCAO
    ELSE 0
  END "valorPrecoPromocional",
  CAST(ITEEST.ITEM AS INTEGER) "referencia",
  CASE
    WHEN COALESCE(ITEPRV.PRVDAATUAL,0) > 0 THEN ITEPRV.PRVDAATUAL
    ELSE 0
  END "valorPreco",
  CAST(ITEECO.comprimento AS VARCHAR(200)) "volume_length",
  CAST(ITEECO.ALTURA AS VARCHAR(200)) "volume_height",
  CAST(ITEECO.LARGURA AS VARCHAR(200)) "volume_width",
  MAR.DESCRICAO "marca",
  COALESCE(ITE.NROCODBARR,ITE.NROCODBARR2,ITE.NROCODBARR3) "codigoBarras",
  ( SELECT LIST(AI.VALOR,',') FROM ATRIBUTOITEM AI
    WHERE AI.ESTABITEM = ITEEST.EMPRESA
      AND AI.ESTABITEM = ITEEST.ESTABITEM
      AND AI.ITEM = ITEEST.ITEM
  ) AS "atributos",
  ITE.ULTALTVT "dtAlteracaoSinc",
  ITE.ULTALTVT "dtAlteracaoSincEstoque",
  ITE.ULTALTVT "dtAlteracaoSincPreco",
  ITEEST.ESTABITEM "estabItem", 
  ITE.NCM "ncm", 
  CASE 
    WHEN COALESCE(ITEEST.USAECOMMERCE, 'N') = 'N' THEN 1
    ELSE DECODE(COALESCE(ITEECO.VISIBILIDADE, 4), 0, 4, COALESCE(ITEECO.VISIBILIDADE, 4))
  END "visibilidade",
  ITE.UNIDADE "unidade",
  1 "multiploVenda",
  CAQ.PNCUSTOAQUIS "custoAquis"
FROM ITEMESTAB ITEEST
INNER JOIN ITEM ITE ON ITE.EMPRESA = ITEEST.ESTABITEM AND ITE.ITEM = ITEEST.ITEM
LEFT JOIN PMARCAS MAR ON MAR.MARCA = ITE.MARCA
LEFT JOIN ITEMECOMMERCE ITEECO ON ITEECO.EMPRESA = ITE.EMPRESA AND ITEECO.IDITEM = ITE.ITEM
LEFT JOIN PPRVDITE ITEPRV ON ITEPRV.ESTABITEM = ITEEST.ESTABITEM AND ITEPRV.EMPRESA = ITEEST.EMPRESA AND ITEPRV.ITEM = ITEEST.ITEM
INNER JOIN PRETORNASALDOITEM(ITEEST.ESTABITEM, ITEEST.ITEM, ITEEST.EMPRESA, CURRENT_DATE, NULL) EST ON (0 = 0)
INNER JOIN PRETORNACUSTOAQUIS(ITEEST.ESTABITEM, ITEEST.ITEM, ITEEST.EMPRESA, CURRENT_DATE) CAQ ON 0=0
WHERE ITEEST.USAECOMMERCE = 'S'
ORDER BY ITE.ITEM
;

CREATE OR ALTER VIEW V_VT_CATEGORIAS(
  "erpId",
  "nome",
  "parent",
  "dtAlteracaoSinc"
) AS
SELECT
  C.CODIGO "erpId",
  C.NOME "nome",
  COALESCE(C.PAI,0)"parent",
  C.ULTALT "dtAlteracaoSinc"
FROM CATEGORIA C 
ORDER BY
  C.CODIGO, 
  C.PAI
;

CREATE OR ALTER VIEW V_VT_MARCAS (
  "erpId",
  "nome"
) AS
SELECT
  M.MARCA "erpId",
  M.DESCRICAO "nome"
FROM PMARCAS M
ORDER BY
  M.MARCA
;

CREATE OR ALTER VIEW V_VT_IMAGEM (
  "estabItem",
  "erpId",
  "imageId",
  "fileName",
  "type",
  "dtAlteracaoSinc",
  "nome"
) AS
SELECT
  ITEEST.ESTABITEM "estabItem",
  ITEIMA.IDITEM "erpId",
  ITEIMA.IDIMAGEM "imageId",
  ITEIMA.WEIMAGEFILE "fileName",
  CASE
    WHEN ITEIMA.INATIVO = 'S' THEN '0,'
    ELSE ''
  END ||
  CASE
    WHEN ITEIMA.BASE = 'S' THEN '1,'
    ELSE ''
  END ||
  CASE
    WHEN ITEIMA.REDUZIDA = 'S' THEN '2,'
    ELSE ''
  END ||
  CASE
    WHEN ITEIMA.MINIATURA = 'S' THEN '3,'
    ELSE ''
  END ||
  CASE
    WHEN ITEIMA.MARCA = 'S' THEN '4,'
    ELSE ''
  END "type",
  ITEIMA.ULTALT "dtAlteracaoSinc",
  COALESCE(ITEECO.DESCRICAO, ITE.DESCRICAO) "nome"
FROM ITEMIMAGEM ITEIMA
LEFT JOIN ITEMESTAB ITEEST ON ITEEST.ESTABITEM = ITEIMA.ESTAB AND ITEEST.ITEM = ITEIMA.IDITEM
LEFT JOIN ITEMECOMMERCE ITEECO ON ITEECO.IDITEM = ITEIMA.IDITEM AND ITEECO.EMPRESA = ITEIMA.ESTAB
LEFT JOIN ITEM ITE ON ITE.ITEM = ITEIMA.IDITEM AND ITE.EMPRESA = ITEIMA.ESTAB
WHERE ITEEST.USAECOMMERCE = 'S'
;