IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[V_EST_SNGPC_MOVIMENTACOES]'))
DROP VIEW [dbo].[V_EST_SNGPC_MOVIMENTACOES]
GO

CREATE VIEW dbo.V_EST_SNGPC_MOVIMENTACOES
as
SELECT
	ENT.CD_EMP
	, ENT.CD_FILIAL
	, ENT.DT_MOV
    ,COALESCE(  ES.CD_MOV,ENT.CD_ENT_SNGPC) AS CD_MOV
    , 'Entradas' AS MOV
	, (
		CASE
			WHEN ENT.CD_TP_ENT = 0 THEN
				'Compras'
			ELSE
				'Transferencias'
		END
	  ) AS TP_MOV
      , (
		CASE
			WHEN ENT.CD_TP_ENT = 0 THEN
				'ENTRADA COMPRAS REF.NOTA FISCAL NR.: '
                + CAST( NF.NR_NF AS VARCHAR(13))  +' CD. ORIGEM.: '
                + CAST( NF.CD_FORN AS VARCHAR(13))
			ELSE
			  'ENTRADA TRANFERENCIA REF.NOTA FISCAL NR.: '
                + CAST( NF.NR_NF AS VARCHAR(13))  +' CD. ORIGEM.: '
                + CAST( NF.CD_FORN AS VARCHAR(13))
		END
	  ) AS MOTIVO   
	, NF.NR_NF AS NOTA_CUPOM
	, CAST( IT.CD_PROD as VARCHAR(13)) +' - '+(EST_PROD.DS_PROD)  AS PRODUTO
    , LOT.CD_PROD
    , IT.DS_PROD
	, LOT.CD_LOTE 
    , LOT.NR_LOTE
    , COALESCE(CPL.QT_LOTE,0) AS SALDO_ATUAL
	, ENT.QT_LOTE
	, NF.CD_FORN AS CD_ORIG
	, 0 AS CD_CLI
	, 0 AS CD_CTR
	, 0 AS CD_DEST
    , 0 AS NR_RECEITA
FROM
	EST_SNGPC_ENTRADA  (NOLOCK)  AS ENT
	INNER JOIN EST_NF_ENT (NOLOCK) NF ON
	ENT.CD_EMP=NF.CD_EMP AND
	ENT.CD_FILIAL = NF.CD_FILIAL AND
	ENT.CD_NF = NF.CD_NF 
	INNER JOIN EST_NF_ENT_IT (NOLOCK) IT ON
	ENT.CD_EMP=IT.CD_EMP AND
	ENT.CD_FILIAL = IT.CD_FILIAL AND
	ENT.CD_NF = IT.CD_NF AND
	ENT.CD_PROD   = IT.CD_PROD
	INNER JOIN EST_PROD_EST_SNGPC_LOTE_CPL (NOLOCK) AS CPL ON 
	ENT.CD_EMP = CPL.CD_EMP AND
	ENT.CD_FILIAL = CPL.CD_FILIAL AND
	ENT.CD_LOTE = CPL.CD_lOTE 
	INNER JOIN EST_PROD_EST_SNGPC_LOTE (NOLOCK) AS LOT ON
	ENT.CD_LOTE = LOT.CD_LOTE
	INNER JOIN EST_SNGPC_ENT_SAI (NOLOCK) ES  ON
    ES.CD_ENT_SNGPC =ENT.CD_ENT_SNGPC   
	INNER JOIN EST_PROD (NOLOCK) ON
	ENT.CD_EMP = EST_PROD.CD_EMP AND
	ENT.CD_PROD = EST_PROD.CD_PROD 
UNION ALL 
  SELECT
    IV.CD_EMP
	, IV.CD_FILIAL
	, V.DT_REINICIO
    , 0
	, 'Saídas' AS MOV
	, 'Inventário.Sai'  AS TP_MOV
    ,'SAIDA INVENTARIO REF.INVENTARIO NR.: '
                + CAST( IV.CD_REINICIO AS VARCHAR(13))  +' DT.REINICIO: '
                + CAST( V.DT_REINICIO AS VARCHAR(13))AS  MOTIVO	
	,IV.CD_REINICIO   AS NOTA_CUPOM
 	, CAST( IV.CD_PROD as VARCHAR(13)) +' - '+(PROD.DS_PROD)  AS PRODUTO
    , LOT.CD_PROD
    , PROD.DS_PROD
	, LOT.CD_LOTE 
    , LOT.NR_LOTE
    , COALESCE(CPL.QT_LOTE,0) AS SALDO_ATUAL	
	, IV.QT_ATUAL
	, 0
	, 0 AS CD_CLI
	, 0 AS CD_CTR
	, 0 AS CD_DEST
    ,'' AS NR_RECEITA
FROM
    EST_SNGPC_REINICIO_INVENTARIO_IT (NOLOCK) IV 
    INNER JOIN  EST_SNGPC_REINICIO_INVENTARIO (NOLOCK) V ON  
    V.CD_EMP       = IV.CD_EMP AND 
    V.CD_FILIAL    = IV.CD_FILIAL AND 
    V.CD_REINICIO  = IV.CD_REINICIO
	INNER JOIN EST_PROD AS PROD ON
	IV.CD_EMP  = PROD.CD_EMP AND
	IV.CD_PROD = PROD.CD_PROD
	INNER JOIN EST_PROD_EST_SNGPC_LOTE_CPL (NOLOCK) AS CPL ON 
	IV.CD_EMP = CPL.CD_EMP AND
	IV.CD_FILIAL = CPL.CD_FILIAL AND 
	IV.CD_LOTE = CPL.CD_lOTE 
	INNER JOIN EST_PROD_EST_SNGPC_LOTE (NOLOCK) AS LOT ON
	IV.CD_LOTE = LOT.CD_LOTE
UNION ALL 
  SELECT  
      IV.CD_EMP
	, IV.CD_FILIAL
	, V.DT_REINICIO
    ,0
	, 'Entradas' AS MOV
	, 'Inventário.Ent' AS TP_MOV
    ,'ENTRADA INVENTARIO REF.FECHAMENTO INVENTARIO NR.: '
                + CAST( IV.CD_REINICIO AS VARCHAR(13))  +' DT.FINALIZACAO '
                + CAST( V.DT_FINALIZACAO  AS VARCHAR(13))AS MOTIVO	
	,IV.CD_REINICIO   AS NOTA_CUPOM
	, CAST( IV.CD_PROD as VARCHAR(13)) +' - '+(PROD.DS_PROD) AS PRODUTO
    , LOT.CD_PROD
    , PROD.DS_PROD
	, LOT.CD_LOTE 
    , LOT.NR_LOTE
    , COALESCE(CPL.QT_LOTE,0) AS SALDO_ATUAL
	,iv.QT_INVENTARIO
    , 0
	, 0 AS CD_CLI
	, 0 AS CD_CTR
	, 0 AS CD_DEST
    , 0 AS NR_RECEITA
FROM
	EST_SNGPC_REINICIO_INVENTARIO_IT (NOLOCK) IV
     INNER JOIN  EST_SNGPC_REINICIO_INVENTARIO (NOLOCK)  V ON  
	V.CD_EMP       = IV.CD_EMP AND
	V.CD_FILIAL    = IV.CD_FILIAL AND
	V.CD_REINICIO  = IV.CD_REINICIO  
	INNER JOIN EST_PROD (NOLOCK) AS PROD ON
	IV.CD_EMP  = PROD.CD_EMP AND
	IV.CD_PROD = PROD.CD_PROD
	INNER JOIN EST_PROD_EST_SNGPC_LOTE_CPL (NOLOCK) AS CPL ON 
	IV.CD_EMP = CPL.CD_EMP AND
	IV.CD_FILIAL = CPL.CD_FILIAL AND 
	IV.CD_LOTE = CPL.CD_lOTE 
	INNER JOIN EST_PROD_EST_SNGPC_LOTE (NOLOCK) AS LOT ON
	IV.CD_LOTE = LOT.CD_LOTE
UNION ALL
--Nota Fiscal
SELECT
	 SAI.CD_EMP
	, SAI.CD_FILIAL
	, SAI.DT_MOV
	 ,COALESCE( ES.CD_MOV,SAI.CD_SAI_SNGPC) AS CD_MOV
    , 'Saidas' AS MOV
	, (
		CASE
			WHEN TRA.CD_SAI_SNGPC IS NOT NULL THEN
				CASE WHEN NAT.TP_MOV IN (0, 8, 27) THEN
					'Vendas'
				ELSE
					'Transferencias'
				END
		END
	  ) AS TP_MOV
	, (
		CASE WHEN TRA.CD_SAI_SNGPC IS NOT NULL THEN
			CASE WHEN NAT.TP_MOV IN (0, 8, 27) THEN
					'SAIDA TRANFERENCIA REF.NOTA FISCAL NR.: '
                    + CAST( NF.NF_NF AS VARCHAR(13))  +' ORIGEM.: '
                    + NF.RZ_EMITENTE 
			ELSE
				    'SAIDA TRANFERENCIA REF.NOTA FISCAL NR.: '
                    + CAST( NF.NF_NF AS VARCHAR(13))  +' ORIGEM.: '
                    + NF.RZ_EMITENTE 
			END
		END
	  ) AS MOTIVO
	, NF.NF_NF  AS NOTA_CUPOM
 	, CAST( PROD.CD_PROD as VARCHAR(13)) +' - '+(PROD.DS_PROD) AS  PRODUTO
	 ,LOT.CD_PROD
     ,PROD.DS_PROD
	, LOT.CD_LOTE 
    , LOT.NR_LOTE
	, COALESCE(CPL.QT_LOTE,0) AS SALDO_ATUAL
    , SAI.QT_LOTE      
	, 0 CD_ORIG
	, 0 AS CD_CLI
	, 0 AS CD_CTR
	, 0 AS CD_FILIAL
    ,'' AS NR_RECEITA
FROM
	EST_SNGPC_SAIDA AS SAI (NOLOCK) 
    INNER JOIN EST_PROD_EST_SNGPC_LOTE_CPL (NOLOCK) AS CPL ON
	CPL.CD_EMP    = SAI.CD_EMP AND
	CPL.CD_FILIAL = SAI.CD_FILIAL AND
	CPL.CD_LOTE   = SAI .CD_LOTE
	INNER JOIN EST_PROD_EST_SNGPC_LOTE (NOLOCK) AS LOT ON
	SAI.CD_LOTE = LOT.CD_LOTE
	INNER JOIN EST_PROD (NOLOCK) AS PROD ON
	LOT.CD_EMP  = PROD.CD_EMP AND 
	LOT.CD_PROD = PROD.CD_PROD
	INNER JOIN EST_NF_SAI_IT_EST_SNGPC_SAIDA (NOLOCK) AS TRA ON
	SAI.CD_SAI_SNGPC = TRA.CD_SAI_SNGPC
	INNER JOIN  EST_NF_SAI (NOLOCK) AS NF ON
	TRA.CD_EMP    = NF.CD_EMP AND 
	TRA.CD_FILIAL = NF.CD_FILIAL AND 
	TRA.CD_NF     = NF.CD_NF
	INNER JOIN GLB_MOV_OP (NOLOCK) AS NAT ON
	NAT.CD_MOV_OP     = NF.CD_NT_MOV
	INNER JOIN EST_SNGPC_ENT_SAI (NOLOCK) ES  ON
    ES.CD_SAI_SNGPC =SAI.CD_SAI_SNGPC   
--PERDAS
UNION ALL
SELECT
	 SAI.CD_EMP
	, SAI.CD_FILIAL
	, SAI.DT_MOV
	 ,0 AS CD_MOV
    , 'Saidas' AS MOV
	, 'Perdas' AS TP_MOV
	, (
		CASE
			WHEN PER.CD_SAI_SNGPC IS NOT NULL THEN
				CASE
					WHEN PER.CD_TP_PERDA = 0 THEN
						'PERDA MOTIVO.: Furto/Roubo'
					WHEN PER.CD_TP_PERDA = 1 THEN
						'PERDA MOTIVO.: Avaria/Deteriorado'
					WHEN PER.CD_TP_PERDA = 2 THEN
						'PERDA MOTIVO.: Vencimento'
					WHEN PER.CD_TP_PERDA = 3 THEN
						'PPERDA MOTIVO.: Aprensão / Recolhimento pela Visa'
					WHEN PER.CD_TP_PERDA = 4 THEN
						'PERDA MOTIVO.: Perda no processo'
					WHEN PER.CD_TP_PERDA = 5 THEN
						'PERDA MOTIVO.: Coleta para qualidade de imagem'
					WHEN PER.CD_TP_PERDA = 6 THEN
                        'PERDA MOTIVO.: Perda de Exclusao da Portaria 344'
					WHEN PER.CD_TP_PERDA = 7 THEN
						'PERDA MOTIVO.: Por desvio de qualidade'
					WHEN PER.CD_TP_PERDA = 8 THEN
						'PERDA MOTIVO.: Recolhimento do fabricante'
				END
		END
	  ) AS MOTIVO
	, LOT.CD_LOTE AS NOTA_CUPOM
 	, CAST( PROD.CD_PROD as VARCHAR(13)) +' - '+(PROD.DS_PROD) AS  PRODUTO
	 ,LOT.CD_PROD
     ,PROD.DS_PROD
	, LOT.CD_LOTE 
    , LOT.NR_LOTE
	, COALESCE(CPL.QT_LOTE,0) AS SALDO_ATUAL
    , SAI.QT_LOTE      
	, 0 CD_ORIG
	, 0 AS CD_CLI
	, 0 AS CD_CTR
	, 0 AS CD_FILIAL
    , 0 AS NR_RECEITA
FROM
	EST_SNGPC_SAIDA (NOLOCK) AS SAI
     INNER JOIN EST_PROD_EST_SNGPC_LOTE_CPL (NOLOCK) AS CPL ON
	CPL.CD_EMP    = SAI.CD_EMP AND
	CPL.CD_FILIAL = SAI.CD_FILIAL AND
	CPL.CD_LOTE   = SAI .CD_LOTE
	INNER JOIN EST_PROD_EST_SNGPC_LOTE (NOLOCK) AS LOT ON
	SAI.CD_LOTE = LOT.CD_LOTE
	INNER JOIN EST_PROD AS PROD ON
	LOT.CD_EMP  = PROD.CD_EMP AND 
	LOT.CD_PROD = PROD.CD_PROD
	INNER JOIN EST_SNGPC_SAIDA_PERDA (NOLOCK) AS PER ON
	SAI.CD_SAI_SNGPC = PER.CD_SAI_SNGPC
	INNER JOIN EST_SNGPC_SAIDA_PERDA_NF (NOLOCK) NFPER ON                       
    NFPER.CD_EMP       = SAI.CD_EMP AND                                           
    NFPER.CD_FILIAL    = SAI.CD_FILIAL AND                               
    NFPER.CD_SAI_SNGPC = SAI.CD_SAI_SNGPC
UNION ALL
--Vendas cupoms fiscal
SELECT
	 SAI.CD_EMP
	, SAI.CD_FILIAL
	, SAI.DT_MOV
	 ,COALESCE( ES.CD_MOV,SAI.CD_SAI_SNGPC) AS CD_MOV
    , 'Saidas' AS MOV
	,'Vendas' AS TP_MOV
	, 'VENDA CUPON FISCAL NR.: '
                + CAST( PDV.NR_ECF AS VARCHAR(13))  +' PDV.: '
                + CAST( PDV.CD_CX AS VARCHAR(13)) AS MOTIVO
	, PDV.NR_ECF AS NOTA_CUPOM
 	, CAST( PROD.CD_PROD as VARCHAR(13)) +' - '+(PROD.DS_PROD) AS  PRODUTO
	 ,LOT.CD_PROD
     ,PROD.DS_PROD
	, LOT.CD_LOTE 
    , LOT.NR_LOTE
	, COALESCE(CPL.QT_LOTE,0) AS SALDO_ATUAL
    , SAI.QT_LOTE      
	, 0 CD_ORIG
	, COALESCE(PDV.CD_CLI, 0) AS CD_CLI
	, COALESCE(REC.CD_CTR, 0) AS CD_CTR
	, 0 AS CD_FILIAL
     ,REC.NR_RECEITA
FROM
	EST_SNGPC_SAIDA (NOLOCK) AS SAI 
    INNER JOIN EST_PROD_EST_SNGPC_LOTE_CPL (NOLOCK) AS CPL ON
	CPL.CD_EMP    = SAI.CD_EMP AND
	CPL.CD_FILIAL = SAI.CD_FILIAL AND
	CPL.CD_LOTE   = SAI .CD_LOTE
	INNER JOIN EST_PROD_EST_SNGPC_LOTE (NOLOCK) AS LOT ON
	SAI.CD_LOTE = LOT.CD_LOTE
	INNER JOIN EST_PROD (NOLOCK) AS PROD ON
	LOT.CD_EMP  = PROD.CD_EMP AND 
	LOT.CD_PROD = PROD.CD_PROD
	INNER JOIN PDV_VD_IT_EST_SNGPC_SAIDA (NOLOCK) AS VDA ON
	VDA.CD_SAI_SNGPC = SAI.CD_SAI_SNGPC AND 
	VDA.CD_EMP= SAI.CD_EMP AND 
	VDA.CD_FILIAL = SAI.CD_FILIAL
	INNER JOIN EST_SNGPC_SAIDA_RECEITA (NOLOCK) AS TMP2 ON
	VDA.CD_SAI_SNGPC = TMP2.CD_SAI_SNGPC
	INNER JOIN EST_SNGPC_RECEITA (NOLOCK) AS REC ON
	TMP2.CD_RECEITA = REC.CD_RECEITA
	INNER JOIN PDV_VD (NOLOCK) AS PDV ON
	VDA.CD_EMP    = PDV.CD_EMP AND 
	VDA.CD_FILIAL = PDV.CD_FILIAL AND
	VDA.CD_VD     = PDV.CD_VD 
	INNER JOIN EST_SNGPC_ENT_SAI (NOLOCK) ES  ON
    ES.CD_SAI_SNGPC =SAI.CD_SAI_SNGPC
GO