CREATE OR ALTER procedure SPC_SPED_PRODUTOS (
    ESTABELECIMENTO integer,
    DATA_INICIO date,
    DATA_FIM date,
    ESPECIE varchar(5),
    DATA_INVENTARIO date,
    INVENTARIO integer,
    PRODUCAO_ESTOQUE INTEGER)
returns (
    PRODUTO_ID integer,
    DESCRICAO varchar(100),
    UNIDADEMEDIDA_SIGLA varchar(10),
    NCM_CODIGO varchar(10),
    SITUACAO char(10),
    ALIQUOTA_ICMS numeric(15,2),
    TIPO_PRODUTO char(10),
    UNID_DIEF char(10),
    CEST char(10),
    UM_DESCRICAO varchar(50))
as
BEGIN

    FOR SELECT DISTINCT
        K.PRODUTO_ID,
        K.DESCRICAO,
        K.UNIDADEMEDIDA_SIGLA,
        MAX(K.NCM_CODIGO),
        K.SITUACAO,
        K.ALIQUOTA_ICMS,
        K.TIPO_PRODUTO,
        K.UNID_DIEF,
        K.CEST,
        K.UM_DESCRICAO
    FROM (
        SELECT DISTINCT
            X.PRODUTO_ID,
            P.DESCRICAO,
            P.UNIDADEMEDIDA_SIGLA,
            IIF(CHAR_LENGTH(X.NCM_NF) >= 8, X.NCM_NF, IIF(CHAR_LENGTH(P.NCM_CODIGO) >= 8, P.NCM_CODIGO, IIF(CHAR_LENGTH(F.NCM_CODIGO) >= 8, F.NCM_CODIGO, ''))) AS NCM_CODIGO,
            COALESCE(FF.NG_SITUACAO, '') AS SITUACAO,
            COALESCE(FF.NG_ALIQICM, 0) AS ALIQUOTA_ICMS,
            COALESCE(FF.NG_TIPOPRDSRV, '') AS TIPO_PRODUTO,
            COALESCE(FF.UNID_DIEF, '') AS UNID_DIEF,
            COALESCE(F.CEST, '') AS CEST,
            U.DESCRICAO AS UM_DESCRICAO
        
        FROM (
        
                SELECT DISTINCT
                    A.PRODUTO_CODIGO AS PRODUTO_ID,
                    A.NCM_CODIGO AS NCM_NF,
                    0 AS ESTOQUE
                FROM TBNFS_ITEM A, TBNFS B
                WHERE   A.NFS_CONTROLE = B.CONTROLE
                AND     B.SPED = '1'
                AND     B.NATUREZA = 1
                AND     B.ESPECIE <> :ESPECIE
                AND     B.SITUACAO = 2
                AND     B.ESTABELECIMENTO_CODIGO = :ESTABELECIMENTO
                AND     B.DATA_EMISSAO BETWEEN :DATA_INICIO AND :DATA_FIM
        
            UNION ALL
        
                SELECT DISTINCT
                    A.PRODUTO_CODIGO AS PRODUTO_ID,
                    A.NCM_CODIGO NCM_NF,
                    0 AS ESTOQUE
                FROM TBNFE_ITEM A, TBNFE B
                WHERE   A.NFE_CONTROLE = B.CONTROLE
                AND     B.SPED = '1'
                AND     ((B.NATUREZA = 1 AND B.SITUACAO = 2  AND B.ESPECIE <> :ESPECIE) OR
                        (B.NATUREZA = 2 AND B.SITUACAO = 1 AND B.EMPRESA_UF <> 'EX'))
                AND     B.ESPECIE IN ('01','1B','04','55')
                AND     B.ESTABELECIMENTO_CODIGO = :ESTABELECIMENTO
                AND     B.DATA_FISCAL BETWEEN :DATA_INICIO AND :DATA_FIM
        
            UNION ALL
        
                SELECT DISTINCT
                    D.PRODUTO_CODIGO AS PRODUTO_ID,
                    '' AS NCM_NF,
                    0 AS ESTOQUE
                FROM TBPATRIMONIO_ENTRADA B, TBCIAP C, TBNFE_ITEM D
                WHERE   B.PATRIMONIO_ID = C.PATRIMONIO_ID
                AND     B.COMPONENTE_ID = C.COMPONENTE_ID
                AND     B.NFE_ITEM_ID = D.CONTROLE
                AND     D.ESTABELECIMENTO_CODIGO = :ESTABELECIMENTO
                AND     C.DATA BETWEEN :DATA_INICIO AND :DATA_FIM
            
            UNION ALL

                SELECT
                    A.PRODUTO_CODIGO AS PRODUTO_ID,
                    '' AS NCM_NF,
                    1 AS ESTOQUE
                FROM (
                    SELECT
                        ESTABELECIMENTO_CODIGO,
                        LOCALIZACAO_CODIGO,
                        PRODUTO_CODIGO,
                        MAX(DATA) DATA
                    FROM TBESTOQUE_SALDO_DIARIO
                    WHERE   DATA <= :DATA_INVENTARIO
                    AND     ESTABELECIMENTO_CODIGO = :ESTABELECIMENTO
                    GROUP BY 1,2,3
                ) A
                INNER JOIN TBESTOQUE_SALDO_DIARIO E ON  A.ESTABELECIMENTO_CODIGO = E.ESTABELECIMENTO_CODIGO
                AND     A.LOCALIZACAO_CODIGO = E.LOCALIZACAO_CODIGO AND A.PRODUTO_CODIGO = E.PRODUTO_CODIGO
                AND     A.DATA = E.DATA

                WHERE   E.SALDO > 0
                AND     E.INVENTARIO = '1'
                AND     E.CUSTO_MEDIO > 0
                AND     :INVENTARIO = 1

            UNION ALL

               SELECT
                    A.PRODUTO_CODIGO AS PRODUTO_ID,
                    '' AS NCM_NF,
                    2 AS ESTOQUE
                FROM (
                    SELECT
                        ESTABELECIMENTO_CODIGO,
                        EMPRESA_CODIGO,
                        PRODUTO_CODIGO,
                        MAX(DATA) DATA
                    FROM TBESTOQUE_SALDO_EM_TERCEIRO
                    WHERE   DATA <= :DATA_INVENTARIO
                    AND     ESTABELECIMENTO_CODIGO = :ESTABELECIMENTO
                    GROUP BY 1,2,3
                ) A
                INNER JOIN TBESTOQUE_SALDO_EM_TERCEIRO E ON  A.ESTABELECIMENTO_CODIGO = E.ESTABELECIMENTO_CODIGO
                AND A.EMPRESA_CODIGO = E.EMPRESA_CODIGO AND A.PRODUTO_CODIGO = E.PRODUTO_CODIGO
                AND A.DATA = E.DATA

                WHERE   E.SALDO_EMTERCEIRO > 0
                AND     :INVENTARIO = 1

            UNION ALL

                SELECT
                    A.PRODUTO_CODIGO AS PRODUTO_ID,
                    '' AS NCM_NF,
                    2 AS ESTOQUE
                FROM (
                    SELECT
                        ESTABELECIMENTO_CODIGO,
                        EMPRESA_CODIGO,
                        PRODUTO_CODIGO,
                        MAX(DATA) DATA
                    FROM TBESTOQUE_SALDO_DE_TERCEIRO
                    WHERE   DATA <= :DATA_INVENTARIO
                    AND     ESTABELECIMENTO_CODIGO = :ESTABELECIMENTO
                    GROUP BY 1,2,3
                ) A
                INNER JOIN TBESTOQUE_SALDO_DE_TERCEIRO E ON  A.ESTABELECIMENTO_CODIGO = E.ESTABELECIMENTO_CODIGO
                AND A.EMPRESA_CODIGO = E.EMPRESA_CODIGO AND A.PRODUTO_CODIGO = E.PRODUTO_CODIGO
                AND A.DATA = E.DATA

                WHERE   E.SALDO_DETERCEIRO > 0
                AND     E.CUSTO_MEDIO > 0
                AND     :INVENTARIO = 1


            UNION ALL

                SELECT
                    A.PRODUTO_CODIGO AS PRODUTO_ID,
                    '' AS NCM_NF,
                    3 AS ESTOQUE
                FROM (
                    SELECT
                        ESTABELECIMENTO_CODIGO,
                        LOCALIZACAO_CODIGO,
                        PRODUTO_CODIGO,
                        MAX(DATA) DATA
                    FROM TBESTOQUE_SALDO_DIARIO
                    WHERE   DATA <= :DATA_FIM
                    AND     ESTABELECIMENTO_CODIGO = :ESTABELECIMENTO
                    GROUP BY 1,2,3
                ) A
                INNER JOIN TBESTOQUE_SALDO_DIARIO E ON  A.ESTABELECIMENTO_CODIGO = E.ESTABELECIMENTO_CODIGO
                AND A.LOCALIZACAO_CODIGO = E.LOCALIZACAO_CODIGO AND A.PRODUTO_CODIGO = E.PRODUTO_CODIGO
                AND A.DATA = E.DATA

                WHERE   E.SALDO > 0
                AND     E.INVENTARIO = '1'
                AND     :PRODUCAO_ESTOQUE = 1

             UNION ALL

                SELECT
                    A.PRODUTO_CODIGO AS PRODUTO_ID,
                    '' AS NCM_NF,
                    3 AS ESTOQUE
                FROM (
                    SELECT
                        ESTABELECIMENTO_CODIGO,
                        EMPRESA_CODIGO,
                        PRODUTO_CODIGO,
                        MAX(DATA) DATA
                    FROM TBESTOQUE_SALDO_DE_TERCEIRO
                    WHERE   DATA <= :DATA_FIM
                    AND     ESTABELECIMENTO_CODIGO = :ESTABELECIMENTO
                    GROUP BY 1,2,3
                ) A
                INNER JOIN TBESTOQUE_SALDO_DE_TERCEIRO E ON  A.ESTABELECIMENTO_CODIGO = E.ESTABELECIMENTO_CODIGO
                AND A.EMPRESA_CODIGO = E.EMPRESA_CODIGO AND A.PRODUTO_CODIGO = E.PRODUTO_CODIGO
                AND A.DATA = E.DATA

                WHERE   E.SALDO_DETERCEIRO > 0
                AND     :PRODUCAO_ESTOQUE = 1

        ) X
        INNER JOIN TBPRODUTO P ON P.CODIGO = X.PRODUTO_ID
        INNER JOIN TBFAMILIA F ON F.CODIGO = P.FAMILIA_CODIGO
        INNER JOIN TBUNIDADEMEDIDA U ON U.SIGLA = P.UNIDADEMEDIDA_SIGLA
        LEFT JOIN TBFAMILIA_FICHA FF ON FF.ESTABELECIMENTO_CODIGO = :ESTABELECIMENTO AND FF.FAMILIA_CODIGO = F.CODIGO

        WHERE IIF(X.ESTOQUE = 2, F.INVENTARIO = '1', TRUE)
        AND IIF(X.ESTOQUE = 2, P.INVENTARIO = '1', TRUE)
        AND IIF(X.ESTOQUE = 3, FF.NG_SITUACAO IN ('00','01','02','03','04','05','06','10'), TRUE)
        ORDER BY X.PRODUTO_ID) K
    GROUP BY PRODUTO_ID, DESCRICAO, UNIDADEMEDIDA_SIGLA,
        SITUACAO, ALIQUOTA_ICMS, TIPO_PRODUTO, UNID_DIEF,
        CEST, UM_DESCRICAO
    INTO
        :PRODUTO_ID, :DESCRICAO, :UNIDADEMEDIDA_SIGLA,
        :NCM_CODIGO, :SITUACAO, :ALIQUOTA_ICMS, :TIPO_PRODUTO,
        :UNID_DIEF, :CEST, :UM_DESCRICAO
    DO BEGIN
        SUSPEND;
    END

END