USE [MinovaES_DEMO_GolfCourse]
GO
/****** Object:  StoredProcedure [dbo].[SPERPRPTSALESRECAP]    Script Date: 20/10/2025 14.19.49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER procedure [dbo].[SPERPRPTSALESRECAP] 

@DocNoFrom varchar (8),
@DocNoTo varchar (8),
@DocDateFrom varchar (10),
@DocDateTo varchar (10),
@DocType varchar (10) ,
@DocStatus varchar (10)

as 


--declare @DocNoFrom varchar (8) =''
--declare @DocNoTo varchar (8) =''
--declare @DocDateFrom varchar (10) =''
--declare @DocDateTo varchar (10) =''
--declare @DocType varchar (10) =''
--declare @DocStatus varchar (10) =''

select distinct
item.DocNo
,header.CustomerID
,'' AS CustomerIDDesc --customer01.[Description] as CustomerIDDesc
,'' AS CustomerIDDescFix
--,case 
--when customer01.[Description] is null then header.CustomerID else customer01.[Description] end as
-- CustomerIDDescFix
,header.CompanyID
,header.CustPONumber
,header.DocType
,doctype.DocTypeDesc as DocTypeDesc
,header.DocStatus
,docstatus.[Description] as DocStatusDesc
,header.[Description] AS headerDesc
,header.ExtDocRef
,header.BusinessUnit
,header.DocDate
,case when header.DocDate =null then '' when header.DocDate ='' then '' 
else dbo.fn_formatdatetime_indonesia(header.DocDate, 'DD MMMM YYYY') end as DocDateDesc
,item.ItemType
,itemtype.[Description] as ServiceType
,item.Material
,case
	when item.ItemType =0 then mat01.ExternalID
	when item.ItemType =1 then v01.ExternalID
	when item.ItemType =2 then ast01.AssetType end as ServiceID
,v01.ExternalID as ServiceIDtest
,case 
	when item.ItemType =0 then mat01.[Description]
	--when item.ItemType =1 then v01.[Description]
	when item.ItemType =2 then ast01.AssetName
	end as ServiceDesc
,item.Currency
,item.PriceAmount
,item.QuantityMaterial
,item.QuantityTransaction
,item.DiscountPercentage
,item.Discount
--,((item.PriceAmount*item.QuantityTransaction)-item.Discount) as NetAmount
,item.NetAmount
--,((item.PriceAmount*item.QuantityTransaction)-item.Discount)*0.11 as TaxAmount
,item.NetAmount*0.11 as TaxAmount
--,((item.PriceAmount*item.QuantityTransaction)-item.Discount) + ((item.PriceAmount*item.QuantityTransaction)-item.Discount)*0.11 as TotalAmount
,item.TotAmount
,term.PaymentTermID
,'SALES RECAP' as Title
,(select CompanyDescription from PCMEPCOMPID where CompanyID='1000' and EndDate='99991231') as CompanyDescription
 from PTRSALESHEADER header 
Inner join PTRSALESITEMS item ON header.DocNo = item.DocNo
Left join PTRSALESPAYTERM term ON item.DocNo = term.DocNo
AND item.DocItemID = term.DocItemID
left join PMDCUS0001 customer01 on header.CustomerID = customer01.CustomerID
left join PCMBSDOCTYPE doctype on header.DocType = doctype.DocType
left join PCMBSSALESDOCSTATUS docstatus on header.DocStatus = docstatus.DocStatus
left join PCMLOGITEMTYPE itemtype on item.ItemType = itemtype.ItemType
left join PMDSRV0001 v01 on item.Material = v01.ServiceID
left join PMDMAT0001 mat01 
	on item.Material = mat01.MaterialID
left join PMDSRV0001 srv01 
	on item.Material = srv01.ServiceID
left join PMDAST0001 ast01 
	on item.Material = ast01.AssetCode
	
where  
	(item.DocNo >= @DocNoFrom or @DocNoFrom='')
	and 
	(item.DocNo <= @DocNoTo or @DocNoTo='')
	and
	 (header.DocDate >= @DocDateFrom or @DocDateFrom='')
	and
	 (header.DocDate <= @DocDateTo or @DocDateTo='')
	 and
	 (header.DocType = @DocType or @DocType ='')
	 and
	 (header.DocStatus = @DocStatus or @DocStatus='')


