IF  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].UFD_RETORNAR_ESTOQUE_COM_RESERVA') AND TYPE IN (N'FN', N'IF', N'TF', N'FS', N'FT'))
    DROP FUNCTION [DBO].[UFD_RETORNAR_ESTOQUE_COM_RESERVA]
GO 


CREATE FUNCTION dbo.UFD_RETORNAR_ESTOQUE_COM_RESERVA
(
    @CD_EMP     INT,
    @CD_FILIAL  INT,
    @CD_PROD    INT
)
RETURNS TABLE
AS
RETURN
(
    WITH BASE AS
    (
        SELECT
            QT_ESTOQUE_COMERCIALIZACAO =
                ISNULL(
                    (
                        SELECT TOP 1 QT_EST
                        FROM EST_PROD_CPL
                        WHERE CD_EMP = @CD_EMP
                          AND CD_FILIAL = @CD_FILIAL
                          AND CD_PROD = @CD_PROD
                    ),0
                ),

            QT_RESERVADA_PENDENCIAS =
                ISNULL(
                    (
                        SELECT TOP 1 QT_EST_PEND_SAI
                        FROM EST_PROD_PENDENCIA_ENTRADA_FILIAL
                        WHERE CD_EMP = @CD_EMP
                          AND CD_FILIAL = @CD_FILIAL
                          AND CD_PROD = @CD_PROD
                    ),0
                ),

            QT_ESTOQUE_PRE_VENDAS_EST_ORC =
                ISNULL(
                    (
                        SELECT SUM(CPL.QT)
                        FROM EST_ORC O
                        INNER JOIN EST_ORC_CPL CPL
                            ON O.CD_EMP = CPL.CD_EMP
                           AND O.CD_FILIAL = CPL.CD_FILIAL
                           AND O.CD_ORC = CPL.CD_ORC
                        WHERE O.CD_EMP = @CD_EMP
                          AND O.CD_FILIAL = @CD_FILIAL
                          AND CPL.CD_PROD = @CD_PROD
                          AND O.STS_ORC = 0
                    ),0
                ),

            QT_ESTOQUE_PRE_VENDAS_TELE_ORC =
                ISNULL(
                    (
                        SELECT SUM(I.QT_ITEM)
                        FROM TELE_ORC T
                        INNER JOIN TELE_ORC_ITEM I
                            ON T.CD_ORC = I.CD_ORC
                        INNER JOIN TELE_ORC_ETAPA E
                            ON E.CD_ORC_ETAPA = T.CD_ORC_ETAPA
                           AND E.CD_ORC = T.CD_ORC
                        WHERE T.CD_EMP = @CD_EMP
                          AND T.CD_FILIAL = @CD_FILIAL
                          AND I.CD_PROD = @CD_PROD
                          AND E.CD_ETAPAS = 2
                    ),0
                )
    )
    SELECT
        QT_ESTOQUE_COMERCIALIZACAO,
        QT_RESERVADA_PENDENCIAS,
        QT_RESERVADA_PREVENDA =
            QT_ESTOQUE_PRE_VENDAS_EST_ORC +
            QT_ESTOQUE_PRE_VENDAS_TELE_ORC,

        QT_ESTOQUE_DISPONIVEL =
            QT_ESTOQUE_COMERCIALIZACAO
            - QT_RESERVADA_PENDENCIAS
            - (
                QT_ESTOQUE_PRE_VENDAS_EST_ORC +
                QT_ESTOQUE_PRE_VENDAS_TELE_ORC
              )
    FROM BASE
);
GO


