USE [MinovaES_Tulisan_Dev]
GO
/****** Object:  StoredProcedure [dbo].[PRPTPUDOCRECAP]    Script Date: 21/10/2025 09.23.31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[PRPTPUDOCRECAP]
(
	@CompanyID VARCHAR(20)
	,@BussinessUnit VARCHAR(20)
	,@DocDateStart VARCHAR(20)
	,@DocDateEnd VARCHAR(20)
	,@DocStatus VARCHAR(20)
	,@DocType VARCHAR (20)
)
AS

--DECLARE @CompanyID VARCHAR(20) = '1000'
--DECLARE @BussinessUnit VARCHAR(20) = ''
--DECLARE @DocDateStart VARCHAR(20) = ''
--DECLARE @DocDateEnd VARCHAR(20) = ''
--DECLARE @DocStatus VARCHAR(20) = ''
--DECLARE @DocType 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 @nowyear VARCHAR (4) = dbo.fn_formatdatetime(GETDATE(), 'yyyy')

SELECT distinct head.DocNo, SUM(ISNULL(item.NetAmount,0)) as NetAmount 
into #NetAmount
FROM PTRPURCHHEAD  head
left join PTRPURCHITEM item on head.DocNo=item.DocNo
where ( head.DocType= @DocType OR @DocType = '')
	and (head.DocStatus=@DocStatus OR @DocStatus ='')
	--and left(head.DocDate,4)=@nowyear 
	and head.DocDate BETWEEN @DocDateStart and @DocDateEnd
group by head.DocNo

SELECT DISTINCT comp.CompanyDescription
				,'PURCHASE RECAP' as Title
				,sh.* 
				,dt.DocTypeDesc
				,bus.Description AS budesc
				,wh.Description AS whdesc
				,dbo.fn_formatdatetime(sh.DocDate, 'dd/mm/yyyy') AS docdatedesc
				,ds.Description AS docstatdesc
				,ven.Description AS vendordesc
				,dbo.fn_formatdatetime(sh.InvoiceDate, 'dd/mm/yyyy') AS invoicedatedesc
				,COALESCE(NetAmount.NetAmount,0)  as NetAmount
FROM dbo.PTRPURCHHEAD AS sh
LEFT JOIN #NetAmount NetAmount on sh.DocNo=NetAmount.DocNo
LEFT JOIN dbo.PCMBSDOCTYPE AS dt
	ON sh.DocType = dt.DocType
LEFT JOIN dbo.PCMEPCOMPID AS comp
	ON comp.CompanyID = sh.CompanyID
LEFT JOIN dbo.PCMFILOBUSUNIT AS bus	
	ON bus.BuCode = sh.BusinessUnit
LEFT JOIN dbo.PCMFILOWAREHOUSE AS wh
	ON sh.Warehouse = wh.Warehouse
LEFT JOIN dbo.PCMBSPURCHASEDOCSTATUS AS ds
	ON sh.DocStatus = ds.DocStatus
LEFT JOIN dbo.PMDVEN0001 AS ven
	ON ven.VendorID = sh.VendorID
WHERE (sh.CompanyID = @CompanyID OR @CompanyID = '')
	AND (sh.BusinessUnit = @BussinessUnit OR @BussinessUnit = '')
	AND (sh.DocStatus = @DocStatus OR @DocStatus = '')
	AND (sh.DocDate BETWEEN @DocDateStart AND @DocDateEnd)
	AND (sh.DocType= @DocType or @DocType='')
	----select * from PCMBSPURCHASEDOCSTATUS

	drop table #NetAmount