IF EXISTS
(
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'[dbo].[UFD_BUSCA_EST_PROD_CPL_PEND_SAIDA]')
          AND type IN(N'FN', N'IF', N'TF', N'FS', N'FT')
)
    BEGIN
        DROP FUNCTION [dbo].[UFD_BUSCA_EST_PROD_CPL_PEND_SAIDA]
    END;
GO

SET ANSI_NULLS ON;
GO

CREATE FUNCTION [dbo].[UFD_BUSCA_EST_PROD_CPL_PEND_SAIDA]
(@CD_EMP    INT,
 @CD_FILIAL VARCHAR(1000),
 @CD_PROD   INT
)
RETURNS @RS_PEND_SAIDA TABLE
(CD_EMP    INT,
 CD_FILIAL INT,
 CD_DEST   INT,
 CD_DOC    INT,
 DT_DOC    DATETIME,
 TP_DOC    VARCHAR(50),
 CD_PROD   INT,
 QT_SAI    MONEY
)
AS
    BEGIN
        DECLARE @TBL_QT_PEND_SAIDA TABLE
        (CD_EMP    INT,
         CD_FILIAL INT,
         CD_ORIG   INT,
         CD_DOC    INT,
         DT_DOC    DATETIME,
         TP_DOC    VARCHAR(50),
         CD_PROD   INT,
         QT_ENT    MONEY
        );

        --Ressuprimento
        INSERT INTO @TBL_QT_PEND_SAIDA
               SELECT EST_PROD_TRANSF.CD_EMP
                    , EST_PROD_TRANSF.CD_FILIAL
                    , EST_PROD_TRANSF.CD_FIL_DEST
                    , EST_PROD_TRANSF.CD_CTR
                    , EST_PROD_TRANSF.DT_TRANSF
                    , 'RESSUPRIMENTO'
                    , EST_PROD_TRANSF_IT.CD_PROD
                    , EST_PROD_TRANSF_IT.QT_SOL AS QT_ENT
               FROM
                    EST_PROD_TRANSF
                    INNER JOIN EST_PROD_TRANSF_IT
                    ON EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_IT.CD_EMP
                       AND EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_IT.CD_FILIAL
                       AND EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_IT.CD_CTR
                    INNER JOIN EST_PROD_TRANSF_PENDENCIA
                    ON EST_PROD_TRANSF.CD_EMP = EST_PROD_TRANSF_PENDENCIA.CD_EMP
                       AND EST_PROD_TRANSF.CD_FILIAL = EST_PROD_TRANSF_PENDENCIA.CD_FILIAL
                       AND EST_PROD_TRANSF.CD_CTR = EST_PROD_TRANSF_PENDENCIA.CD_CTR
               WHERE EST_PROD_TRANSF.CD_EMP = @CD_EMP
                     AND EST_PROD_TRANSF.CD_FILIAL IN
               (
                   SELECT ID
                   FROM UFD_PRM_IN_STRING(@CD_FILIAL)
               )
                    AND EST_PROD_TRANSF_IT.CD_PROD = @CD_PROD;

        --Pedidos de Vendas 
        INSERT INTO @TBL_QT_PEND_SAIDA
               SELECT EST_PED_VD.CD_EMP
                    , EST_PED_VD.CD_FILIAL AS CD_ORIG
                    , EST_PED_VD.CD_CLI AS CD_FILIAL
                    , EST_PED_VD.CD_PED
                    , EST_PED_VD.DT_EMI_PED
                    , 'PR-FATURAS / PEDIDO VENDAS'
                    , EST_PED_VD_IT.CD_PROD
                    , EST_PED_VD_IT.QT_IT
               FROM
                    EST_PED_VD_IT
                    INNER JOIN EST_PED_VD
                    ON EST_PED_VD_IT.CD_EMP = EST_PED_VD.CD_EMP
                       AND EST_PED_VD_IT.CD_FILIAL = EST_PED_VD.CD_FILIAL
                       AND EST_PED_VD_IT.CD_PED = EST_PED_VD.CD_PED
                    INNER JOIN EST_PED_VD_PENDENCIA
                    ON EST_PED_VD.CD_EMP = EST_PED_VD_PENDENCIA.CD_EMP
                       AND EST_PED_VD.CD_FILIAL = EST_PED_VD_PENDENCIA.CD_FILIAL
                       AND EST_PED_VD.CD_PED = EST_PED_VD_PENDENCIA.CD_PED
               WHERE EST_PED_VD_IT.CD_EMP = @CD_EMP
                     AND EST_PED_VD_IT.CD_FILIAL IN
               (
                   SELECT ID
                   FROM UFD_PRM_IN_STRING(@CD_FILIAL)
               )
                    AND EST_PED_VD_IT.CD_PROD = @CD_PROD;
        INSERT INTO @TBL_QT_PEND_SAIDA
               SELECT d.CD_EMP
                    , d.CD_FILIAL AS CD_ORIG
                    , CASE
                          WHEN O.DEST_ORIG = 2
                          THEN I.CD_FORN
                          ELSE 0
                      END CD_FILIAL_DEST
                    , d.CD_NF
                    , d.DT_DEVOLUCAO
                    , 'DEVOLUCAO' AS TP_DS
                    , d.CD_PROD
                    , QTDE_PROD - QTDE_PROD_DEVO_GERADA QT_IT
               FROM
                    EST_NF_ENT_IT_DEVOLUCAO D
                    INNER JOIN EST_NF_ENT I
                    ON D.CD_EMP = I.CD_EMP
                       AND D.CD_FILIAL = I.CD_FILIAL
                       AND D.CD_NF = I.CD_NF
                    INNER JOIN GLB_MOV_OP O
                    ON I.CD_NT_MOV = O.CD_MOV_OP
               WHERE D.QTDE_PROD - D.QTDE_PROD_DEVO_GERADA > 0
                     AND D.CD_EMP = 1
                     AND d.CD_PROD = @CD_PROD;

        --Pendencia de saida de bloqueio de produtos
        INSERT INTO @TBL_QT_PEND_SAIDA
               SELECT EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_EMP
                    , EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_FILIAL
                    , EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_FILIAL
                    , EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_CTR
                    , EST_PED_CP_PENDENCIA_IMPLANTACAO.DT_CAD
                    , 'BLOQUEIO DE ESTOQUE DE PRODUTOS - SAIDA' AS TP_DOC
                    , EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.CD_PROD
                    , EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.QT_IT
               FROM
                    EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL
                    INNER JOIN EST_PROD
                    ON EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.CD_EMP = EST_PROD.CD_EMP
                       AND EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.CD_PROD = EST_PROD.CD_PROD
                    INNER JOIN EST_PED_CP_PENDENCIA_IMPLANTACAO
                    ON EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.CD_CTR = EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_CTR
               WHERE EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_EMP = @CD_EMP
                     AND EST_PED_CP_PENDENCIA_IMPLANTACAO.CD_FILIAL IN
               (
                   SELECT ID
                   FROM UFD_PRM_IN_STRING(@CD_FILIAL)
               )
                    AND EST_PED_CP_PENDENCIA_IMPLANTACAO_CPL.CD_PROD = @CD_PROD
                    AND EST_PED_CP_PENDENCIA_IMPLANTACAO.BLQ_EST = 1;

--Pendencia de saida cross\Docking
INSERT INTO @TBL_QT_PEND_SAIDA
SELECT     
	DISTINCT
	V_EST_NF_ENT_CAB.CD_EMP, 
	V_EST_NF_ENT_CAB.CD_FILIAL, 
	V_EST_NF_ENT_CAB.CD_FILIAL, 
    V_EST_NF_ENT_CAB.NR_NF, 
    EST_NF_ENT_CROSS_DOCKING_PENDENTE.DT_CAD, 
    'CROSS-DOCKING/BLOQUEIO DE ESTOQUE' AS DS_TIPO_DOC, 
	EST_NF_ENT_IT.CD_PROD,
	EST_NF_ENT_IT.QT_IT
FROM         
	V_EST_NF_ENT_CAB INNER JOIN EST_NF_ENT_CROSS_DOCKING_PENDENTE ON 
	V_EST_NF_ENT_CAB.CD_EMP = EST_NF_ENT_CROSS_DOCKING_PENDENTE.CD_EMP AND 
	V_EST_NF_ENT_CAB.CD_FILIAL = EST_NF_ENT_CROSS_DOCKING_PENDENTE.CD_FILIAL AND 
	V_EST_NF_ENT_CAB.CD_NF = EST_NF_ENT_CROSS_DOCKING_PENDENTE.CD_NF   
	INNER JOIN EST_NF_ENT_IT ON
	V_EST_NF_ENT_CAB.CD_EMP = EST_NF_ENT_IT.CD_EMP AND
	V_EST_NF_ENT_CAB.CD_FILIAL = EST_NF_ENT_IT.CD_FILIAL AND
	V_EST_NF_ENT_CAB.CD_NF = EST_NF_ENT_IT.CD_NF  
	LEFT JOIN dbo.GLB_USU U ON 
	EST_NF_ENT_CROSS_DOCKING_PENDENTE.CD_USU = U.CD_USU
WHERE
	EST_NF_ENT_IT.CD_EMP = @CD_EMP
    AND EST_NF_ENT_IT.CD_FILIAL IN (
								SELECT ID
								FROM UFD_PRM_IN_STRING(@CD_FILIAL)
								)
	AND EST_NF_ENT_IT.CD_PROD=@CD_PROD

INSERT INTO @RS_PEND_SAIDA
        SELECT *
        FROM @TBL_QT_PEND_SAIDA;
RETURN;
END;
GO