TULISAN

??USE [MinovaES_Tulisan_Dev]

GO

/****** Object:  StoredProcedure [dbo].[PRPTPUDOCRECAP]    Script Date: 20/10/2025 15.13.28 ******/

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) = '20240301'

--DECLARE @DocDateEnd VARCHAR(20) = '20240331'

--DECLARE @DocStatus VARCHAR(20) = ''

--DECLARE @DocType VARCHAR(20) = 'PO'

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 

	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