51 lines
2.6 KiB
SQL
51 lines
2.6 KiB
SQL
-- 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"; |