USE [MinovaES_Internal_2021]
GO
/****** Object:  StoredProcedure [dbo].[PRPTINVDOCRECAP]    Script Date: 1/19/2022 10:33:02 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PRPTINVDOCRECAP]
(
	--//@CompanyID VARCHAR(20)
	--//,@BussinessUnit VARCHAR(20)
	@DocDateStart VARCHAR(20)
	,@DocDateEnd VARCHAR(20)
	,@DocStatus VARCHAR(20)
	,@Warehouse VARCHAR(20)
)
AS

----//DECLARE @CompanyID VARCHAR(20) = ''
----//DECLARE @BussinessUnit VARCHAR(20) = ''
--DECLARE @DocDateStart VARCHAR(20) = ''
--DECLARE @DocDateEnd VARCHAR(20) = ''
--DECLARE @DocStatus VARCHAR(20) = ''
--DECLARE @Warehouse VARCHAR(20) = ''

IF (@DocDateStart <> '') BEGIN SET @DocDateStart = @DocDateStart END ELSE BEGIN SET @DocDateStart = '19000101' END
IF (@DocDateEnd <> '') BEGIN SET @DocDateEnd = @DocDateEnd END ELSE BEGIN SET @DocDateEnd = '99991231' END

DECLARE @TRecap TABLE
(
		[DocNo] [VARCHAR](20) NOT NULL,
	[DocType] [VARCHAR](20) NULL,
	[DocDate] [VARCHAR](20) NULL,
	[DocStatus] [VARCHAR](20) NULL,
	[SendID] [VARCHAR](20) NULL,
	[SendType] [VARCHAR](20) NULL,
	[SendArea] [VARCHAR](50) NULL,
	[SendWarehouse] [VARCHAR](20) NULL,
	[ReceiverID] [VARCHAR](20) NULL,
	[ReceiverType] [VARCHAR](20) NULL,
	[ReceiverPlant] [VARCHAR](20) NULL,
	[CreateBy] [VARCHAR](18) NULL,
	[CreateDate] [VARCHAR](14) NULL,
	[ChangeBy] [VARCHAR](18) NULL,
	[ChangeDate] [VARCHAR](14) NULL,
	[ReceiverWarehouse] [VARCHAR](20) NULL,
	[StartDate] [VARCHAR](8) NULL,
	[EndDate] [VARCHAR](8) NULL,
	[TransTypeRef] [VARCHAR](20) NULL,
	[DocTypeRef] [VARCHAR](20) NULL,
	[DocIDRef] [VARCHAR](20) NULL,
	[DocItemRef] [VARCHAR](20) NULL,
	[ProcessID] [VARCHAR](8) NULL,
	[Description] [VARCHAR](500) NULL,
	[IntDocRef] [VARCHAR](50) NULL,
	[ExtDocRef] [VARCHAR](50) NULL,
	[RefType] [VARCHAR](2) NULL,
	[CompanyID] [VARCHAR](4) NULL,
	DocTypeDesc [VARCHAR](MAX), 
	docdatedesc [VARCHAR](20),
	docstatdesc [VARCHAR](20),
	sendwhdesc [VARCHAR](20),
	recievwhdesc [VARCHAR](20)
)

INSERT INTO @TRecap
SELECT DISTINCT ih.*
			,dt.DocTypeDesc
			,dbo.fn_formatdatetime(ih.DocDate, 'dd/mm/yyyy') AS docdatedesc
			,ds.Description AS docstatdesc
			,swh.Description AS sendwhdesc
			,rwh.Description AS recievwhdesc
FROM dbo.PTRMATERIALHEAD AS ih
LEFT JOIN dbo.PCMBSDOCTYPE AS dt
	ON ih.DocType = dt.DocType
LEFT JOIN dbo.PCMBSLOGDOCSTATUS AS ds
	ON ih.DocStatus = ds.DocStatus
LEFT JOIN dbo.PCMFILOWAREHOUSE AS swh
	ON ih.SendWarehouse = swh.Warehouse
LEFT JOIN dbo.PCMFILOWAREHOUSE AS rwh
	ON ih.ReceiverWarehouse = rwh.Warehouse
WHERE (ih.DocStatus = @DocStatus OR @DocStatus = '')
	--AND (ih.CompanyID = @CompanyID OR @CompanyID = '')
	--AND (ih.BusinessUnit = @BussinessUnit OR @BussinessUnit = '')	
	AND (ih.DocDate BETWEEN @DocDateStart AND @DocDateEnd)
	AND (ih.SendWarehouse = @Warehouse OR @Warehouse = '')

INSERT INTO @TRecap
SELECT DISTINCT ih.*
			,dt.DocTypeDesc
			,dbo.fn_formatdatetime(ih.DocDate, 'dd/mm/yyyy') AS docdatedesc
			,ds.Description AS docstatdesc
			,swh.Description AS sendwhdesc
			,rwh.Description AS recievwhdesc
FROM dbo.PTRMATERIALHEAD AS ih
LEFT JOIN dbo.PCMBSDOCTYPE AS dt
	ON ih.DocType = dt.DocType
LEFT JOIN dbo.PCMBSLOGDOCSTATUS AS ds
	ON ih.DocStatus = ds.DocStatus
LEFT JOIN dbo.PCMFILOWAREHOUSE AS swh
	ON ih.SendWarehouse = swh.Warehouse
LEFT JOIN dbo.PCMFILOWAREHOUSE AS rwh
	ON ih.ReceiverWarehouse = rwh.Warehouse
WHERE (ih.DocStatus = @DocStatus OR @DocStatus = '')
	--AND (ih.CompanyID = @CompanyID OR @CompanyID = '')
	--AND (ih.BusinessUnit = @BussinessUnit OR @BussinessUnit = '')	
	AND (ih.DocDate BETWEEN @DocDateStart AND @DocDateEnd)
	AND (ih.ReceiverWarehouse = @Warehouse OR @Warehouse = '' )

INSERT INTO @TRecap
SELECT DISTINCT ih.[DocNo]
			,ih.[DocType]
			,ih.[DocDate]
			,ih.[DocStatus]
			,ih.[SendID]
			,ih.[SendType]
			,ih.[SendArea]
			,ih.[SendWarehouse]
			,ih.[ReceiverID]
			,ih.[ReceiverType]
			,ih.[ReceiverPlant]
			,ih.[CreateBy]
			,ih.[CreateDate]
			,ih.[ChangeBy]
			,ih.[ChangeDate]
			,ih.[ReceiverWarehouse]
			,''
			,''
			,ih.[TransTypeRef]
			,ih.[DocTypeRef]
			,ih.[DocIDRef]
			,ih.[DocItemRef]
			,ih.[ProcessID]
			,ih.[Description]
			,ih.[IntDocRef]
			,ih.[ExtDocRef]
			,ih.[RefType]
			,ih.[CompanyID]
			,dt.DocTypeDesc
			,dbo.fn_formatdatetime(ih.DocDate, 'dd/mm/yyyy') AS docdatedesc
			,ds.Description AS docstatdesc
			,swh.Description AS sendwhdesc
			,rwh.Description AS recievwhdesc
FROM dbo.PTRMATERIALHEADINTERNAL AS ih
LEFT JOIN dbo.PCMBSDOCTYPE AS dt
	ON ih.DocType = dt.DocType
LEFT JOIN dbo.PCMBSLOGDOCSTATUS AS ds
	ON ih.DocStatus = ds.DocStatus
LEFT JOIN dbo.PCMFILOWAREHOUSE AS swh
	ON ih.SendWarehouse = swh.Warehouse
LEFT JOIN dbo.PCMFILOWAREHOUSE AS rwh
	ON ih.ReceiverWarehouse = rwh.Warehouse
WHERE (ih.DocStatus = @DocStatus OR @DocStatus = '')
	--AND (ih.CompanyID = @CompanyID OR @CompanyID = '')
	--AND (ih.BusinessUnit = @BussinessUnit OR @BussinessUnit = '')	
	AND (ih.DocDate BETWEEN @DocDateStart AND @DocDateEnd)

SELECT DISTINCT * FROM @TRecap