SET TERM ^ ;

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,
    NF_ITENS integer,
    BLOCO_C100_SAIDA integer = 1,
    BLOCO_C100_ENTRADA integer = 1)
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
declare variable CNPJ_ESTAB varchar(20);
declare variable ESTABS varchar(100);
BEGIN

    :CNPJ_ESTAB = COALESCE((SELECT FIRST 1 V.CNPJ FROM TBESTABELECIMENTO V WHERE V.CODIGO = :ESTABELECIMENTO), '');
    :ESTABS     = COALESCE((SELECT LIST(V.CODIGO) FROM TBESTABELECIMENTO V WHERE V.CNPJ = :CNPJ_ESTAB), '');

    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,
            IIF(COALESCE(FF.NG_SITUACAO, '') = '', '99', FF.NG_SITUACAO) AS SITUACAO,
            COALESCE(FF.NG_ALIQICM, 0) AS ALIQUOTA_ICMS,
            IIF(COALESCE(FF.NG_TIPOPRDSRV, '') = '', '99', 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.SITUACAO = 2) OR (B.NATUREZA = 2 AND B.SITUACAO = 1))
                AND     IIF(:NF_ITENS = 1, TRUE, B.ESPECIE <> :ESPECIE)
                AND     B.SITUACAO_NF = 2
                AND     B.ESTABELECIMENTO_CODIGO IN (SELECT OSPLIT FROM SPLIT(:ESTABS,','))
                AND     B.DATA_EMISSAO BETWEEN :DATA_INICIO AND :DATA_FIM
                AND     :BLOCO_C100_SAIDA = 1
        
            UNION ALL

            SELECT DISTINCT
                    A.PRODUTO_ID,
                    A.NCM_CODIGO AS NCM_NF,
                    0 AS ESTOQUE
                FROM TBPDV_CFE_ITEM A, TBPDV_CFE B
                WHERE   A.PDV_CFE_ID = B.ID
                AND     B.SPED_FISCAL = 1
                AND     B.TIPO = '1'
                AND     IIF(:NF_ITENS = 1, TRUE, FALSE)
                AND     B.SITUACAO = 2
                AND     B.ESTABELECIMENTO_ID IN (SELECT OSPLIT FROM SPLIT(:ESTABS,','))
                AND     B.DATA_EMISSAO BETWEEN :DATA_INICIO AND :DATA_FIM
                AND     :BLOCO_C100_SAIDA = 1
        
            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 IIF(:NF_ITENS = 1, TRUE, B.ESPECIE <> :ESPECIE)) OR
                        (B.NATUREZA = 2 AND B.SITUACAO = 1 AND B.EMPRESA_UF <> 'EX'))
                AND     B.SITUACAO_NF = 2
                AND     B.ESPECIE IN ('01','1B','04','55')
                AND     B.ESTABELECIMENTO_CODIGO IN (SELECT OSPLIT FROM SPLIT(:ESTABS,','))
                AND     B.DATA_FISCAL BETWEEN :DATA_INICIO AND :DATA_FIM
                AND     :BLOCO_C100_ENTRADA = 1
        
            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 IN (SELECT OSPLIT FROM SPLIT(:ESTABS,','))
                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   :INVENTARIO = 1
                    AND     DATA <= :DATA_INVENTARIO
                    AND     ESTABELECIMENTO_CODIGO IN (SELECT OSPLIT FROM SPLIT(:ESTABS,','))
                    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   :INVENTARIO = 1
                    AND     DATA <= :DATA_INVENTARIO
                    AND     ESTABELECIMENTO_CODIGO IN (SELECT OSPLIT FROM SPLIT(:ESTABS,','))
                    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   :INVENTARIO = 1
                    AND     DATA <= :DATA_INVENTARIO
                    AND     ESTABELECIMENTO_CODIGO IN (SELECT OSPLIT FROM SPLIT(:ESTABS,','))
                    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   :PRODUCAO_ESTOQUE = 1
                    AND     DATA <= :DATA_FIM
                    AND     ESTABELECIMENTO_CODIGO IN (SELECT OSPLIT FROM SPLIT(:ESTABS,','))
                    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
                        A.ESTABELECIMENTO_CODIGO,
                        A.EMPRESA_CODIGO,
                        A.PRODUTO_CODIGO,
                        (MAX(CAST((SELECT FIRST 1 G.CUSTO_MEDIO FROM TBESTOQUE_SALDO_DIARIO G
                                        WHERE G.ESTABELECIMENTO_CODIGO = A.ESTABELECIMENTO_CODIGO
                                            AND G.PRODUTO_CODIGO = A.PRODUTO_CODIGO
                                            AND G.SALDO_EMTERCEIRO > 0
                                            AND G.DATA <= A.DATA ORDER BY G.DATA DESC) AS NUMERIC(18,4)))) CUSTO_MEDIO,
                        MAX(A.DATA) DATA
                    FROM TBESTOQUE_SALDO_EM_TERCEIRO A
                    WHERE   :PRODUCAO_ESTOQUE = 1
                    AND     A.DATA <= :DATA_FIM
                    AND     A.ESTABELECIMENTO_CODIGO IN (SELECT OSPLIT FROM SPLIT(:ESTABS,','))
                    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     A.CUSTO_MEDIO > 0
                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   :PRODUCAO_ESTOQUE = 1
                    AND     DATA <= :DATA_FIM
                    AND     ESTABELECIMENTO_CODIGO IN (SELECT OSPLIT FROM SPLIT(:ESTABS,','))
                    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_INVENTARIO
        LEFT JOIN TBUNIDADEMEDIDA U ON U.SIGLA = P.UNIDADEMEDIDA_SIGLA
        LEFT JOIN TBFAMILIA_FICHA FF ON FF.ESTABELECIMENTO_CODIGO IN (SELECT OSPLIT FROM SPLIT(:ESTABS,',')) AND FF.FAMILIA_CODIGO = F.CODIGO

        WHERE IIF(X.ESTOQUE = 2, F.INVENTARIO = '1', TRUE)
        AND IIF(X.ESTOQUE = 2 OR X.ESTOQUE = 3 , 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
^

SET TERM ; ^

