-- SEVEN.JUV_ESTOQUE_CUSTOFIN_FILIAL fonte

CREATE OR REPLACE FORCE EDITIONABLE VIEW "SEVEN"."JUV_ESTOQUE_CUSTOFIN_FILIAL" ("FILIAL", "REGIAO", "CODPROD", "VL_ESTOQUE_VENDA", "VL_ESTOQUE_GERAL", "VL_CUSTOFIN_TOTAL", "VL_CUSTOFIN_DISP", "VL_CUSTOFIN_BLOQUEADO", "VL_CUSTOFIN_AVARIA", "VL_CUSTOFIN_EXPOSICAO", "QT_DISP", "QT_ESTGER", "QT_RESERV", "QT_INDENIZ") AS 
  SELECT
    EST.CODFILIAL AS FILIAL,
    CONF.NUMREGIAO_PADRAO AS REGIAO,
    EST.CODPROD,
    ROUND(SUM( (NVL(EST.QTESTGER,0) - NVL(EST.QTRESERV,0) - NVL(EST.QTINDENIZ,0)) * NVL(TAB.PVENDA,0) ),2) AS VL_ESTOQUE_VENDA,
    ROUND(SUM( NVL(EST.QTESTGER,0) * (NVL(TAB.PVENDA,0) )),2) AS VL_ESTOQUE_GERAL,
    ROUND(SUM( NVL(EST.QTESTGER,0) * NVL(EST.CUSTOFIN,0) ),2) AS VL_CUSTOFIN_TOTAL,
    ROUND(SUM( (NVL(EST.QTESTGER,0) - NVL(EST.QTRESERV,0) - NVL(EST.QTINDENIZ,0)) * NVL(EST.CUSTOFIN,0) ),2) AS VL_CUSTOFIN_DISP,
    ROUND(SUM( NVL(EST.QTBLOQUEADA,0) * NVL(EST.CUSTOFIN,0) ),2) AS VL_CUSTOFIN_BLOQUEADO,
    ROUND(SUM( NVL(EST.QTINDENIZ,0)  * NVL(EST.CUSTOFIN,0) ),2) AS VL_CUSTOFIN_AVARIA,
    ROUND(SUM( NVL(EST.QTEXPOSICAO,0) * NVL(EST.CUSTOFIN,0) ),2) AS VL_CUSTOFIN_EXPOSICAO,
    ROUND(SUM( NVL(EST.QTESTGER,0) - NVL(EST.QTRESERV,0) - NVL(EST.QTINDENIZ,0) ),2) AS QT_DISP,
    ROUND(SUM( NVL(EST.QTESTGER,0) ),2)  AS QT_ESTGER,
    ROUND(SUM( NVL(EST.QTRESERV,0) ),2)  AS QT_RESERV,
    ROUND(SUM( NVL(EST.QTINDENIZ,0) ),2) AS QT_INDENIZ
FROM PCEST EST
JOIN (
    SELECT CODFILIAL, TO_NUMBER(VALOR) AS NUMREGIAO_PADRAO 
    FROM PCPARAMFILIAL 
    WHERE NOME = 'FIL_NUMREGIAOPADRAO'
) CONF ON CONF.CODFILIAL = EST.CODFILIAL
JOIN PCPRODUT PROD
  ON PROD.CODPROD = EST.CODPROD
 AND PROD.DTEXCLUSAO IS NULL
LEFT JOIN PCTABPR TAB
  ON TAB.CODPROD = EST.CODPROD
 AND TAB.NUMREGIAO = CONF.NUMREGIAO_PADRAO 
WHERE EST.CODFILIAL IN (1,2,3,4,5,6,7,8,9,10,11,12,13,49,69)
  AND EXISTS (
      SELECT 1
      FROM PCTABPR T2
      JOIN PCREGIAO REG   ON REG.NUMREGIAO = T2.NUMREGIAO
      JOIN PCTABTRIB TRIB ON TRIB.CODPROD  = T2.CODPROD
      JOIN PCTRIBUT TR    ON TR.CODST     = TRIB.CODST
      WHERE T2.CODPROD = EST.CODPROD
        AND T2.NUMREGIAO = CONF.NUMREGIAO_PADRAO 
        AND (REG.STATUS IS NULL OR REG.STATUS <> 'I')
        AND (REG.CODFILIAL = EST.CODFILIAL OR NVL(REG.CODFILIAL,'99') = '99')
        AND TRIB.CODFILIALNF = DECODE(NVL(REG.CODFILIAL,'99'), '99', EST.CODFILIAL, REG.CODFILIAL)
        AND TRIB.UFDESTINO = REG.UF
  )
GROUP BY 
    EST.CODFILIAL, 
    CONF.NUMREGIAO_PADRAO, 
    EST.CODPROD;

GRANT SELECT ON "SEVEN"."JUV_ESTOQUE_CUSTOFIN_FILIAL" TO "BIJURU";
  GRANT SELECT ON "SEVEN"."JUV_ESTOQUE_CUSTOFIN_FILIAL" TO "CONSULTA";