-- AUDITORIA DE NOTAS DE ENTRADA DE USO E CONSUMO SEM CONTAS A PAGAR (ORACLE)
-- Regra de negócio: segregação canônica por Ramos do Viman (Ramo 4 - Material Uso/Consumo e Ramo 8 - Serviços Uso/Consumo)
WITH NOTAS_ENTRADA_FISICAS AS (
    SELECT '01 - VITORIA HOSPITALAR MATRIZ - ES (001)' AS FILIAL, '1' AS COD_EMPRESA, INNRDC, INFORN, INRAMO, INDTEM, INDTMV, INCFOP, INVRTO, INQTPC FROM VIMAN.VETEIN01
    UNION ALL SELECT '02 - VITORIA HOSPITALAR FILIAL SP (002)', '2', INNRDC, INFORN, INRAMO, INDTEM, INDTMV, INCFOP, INVRTO, INQTPC FROM VIMAN.VETEIN02
    UNION ALL SELECT '03 - VITORIA HOSPITALAR FILIAL MG (003)', '3', INNRDC, INFORN, INRAMO, INDTEM, INDTMV, INCFOP, INVRTO, INQTPC FROM VIMAN.VETEIN03
    UNION ALL SELECT '04 - VITORIA HOSPITALAR FILIAL RJ (004)', '4', INNRDC, INFORN, INRAMO, INDTEM, INDTMV, INCFOP, INVRTO, INQTPC FROM VIMAN.VETEIN04
    UNION ALL SELECT '05 - VITORIA HOSPITALAR FILIAL R.P.-SP (005)', '5', INNRDC, INFORN, INRAMO, INDTEM, INDTMV, INCFOP, INVRTO, INQTPC FROM VIMAN.VETEIN05
    UNION ALL SELECT '10 - VH CONECTA MATRIZ - RJ (10)', '10', INNRDC, INFORN, INRAMO, INDTEM, INDTMV, INCFOP, INVRTO, INQTPC FROM VIMAN.VETEIN10
    UNION ALL SELECT '11 - VH CONECTA FILIAL - SAO PAULO/SP (11)', '11', INNRDC, INFORN, INRAMO, INDTEM, INDTMV, INCFOP, INVRTO, INQTPC FROM VIMAN.VETEIN11
    UNION ALL SELECT '12 - VH CONECTA - FILIAL RIB.PRETO/SP (12)', '12', INNRDC, INFORN, INRAMO, INDTEM, INDTMV, INCFOP, INVRTO, INQTPC FROM VIMAN.VETEIN12
    UNION ALL SELECT '13 - VH RIO VIX (13)', '13', INNRDC, INFORN, INRAMO, INDTEM, INDTMV, INCFOP, INVRTO, INQTPC FROM VIMAN.VETEIN13
),
TODOS_TITULOS_CONTAS_A_PAGAR AS (
    SELECT DPCODC, DPNRDP FROM VIMAN.VETEDP01 WHERE DPCPCR = 'P' GROUP BY DPCODC, DPNRDP
    UNION ALL SELECT DPCODC, DPNRDP FROM VIMAN.VETEDP02 WHERE DPCPCR = 'P' GROUP BY DPCODC, DPNRDP
    UNION ALL SELECT DPCODC, DPNRDP FROM VIMAN.VETEDP03 WHERE DPCPCR = 'P' GROUP BY DPCODC, DPNRDP
    UNION ALL SELECT DPCODC, DPNRDP FROM VIMAN.VETEDP04 WHERE DPCPCR = 'P' GROUP BY DPCODC, DPNRDP
    UNION ALL SELECT DPCODC, DPNRDP FROM VIMAN.VETEDP05 WHERE DPCPCR = 'P' GROUP BY DPCODC, DPNRDP
    UNION ALL SELECT DPCODC, DPNRDP FROM VIMAN.VETEDP10 WHERE DPCPCR = 'P' GROUP BY DPCODC, DPNRDP
    UNION ALL SELECT DPCODC, DPNRDP FROM VIMAN.VETEDP11 WHERE DPCPCR = 'P' GROUP BY DPCODC, DPNRDP
    UNION ALL SELECT DPCODC, DPNRDP FROM VIMAN.VETEDP12 WHERE DPCPCR = 'P' GROUP BY DPCODC, DPNRDP
    UNION ALL SELECT DPCODC, DPNRDP FROM VIMAN.VETEDP13 WHERE DPCPCR = 'P' GROUP BY DPCODC, DPNRDP
)
SELECT NF.FILIAL, NF.INNRDC AS NUM_NOTA, TO_CHAR(NF.INDTMV, 'DD/MM/YYYY') AS DT_ENTRADA,
       CASE NF.INRAMO WHEN '4' THEN 'MATERIAL USO/CONSUMO' WHEN '8' THEN 'SERVIÇO USO/CONSUMO' ELSE 'USO/CONSUMO' END AS RAMO,
       FORN.CLNONF AS FORNECEDOR, NF.INCFOP AS CFOP, NF.INVRTO AS VALOR_TOTAL
FROM NOTAS_ENTRADA_FISICAS NF
LEFT JOIN TODOS_TITULOS_CONTAS_A_PAGAR TIT 
    ON TIT.DPCODC = NF.INFORN 
   AND TIT.DPNRDP = NF.INNRDC
LEFT JOIN VIMAN.VETECL FORN 
    ON FORN.CLCODI = NF.INFORN 
   AND FORN.CLRAMO = NF.INRAMO
WHERE TIT.DPNRDP IS NULL
  AND NF.INRAMO IN ('4', '8')
ORDER BY NF.INDTMV DESC, NF.COD_EMPRESA;