SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO


--==================================
--Tom 02/01/2024 Desc:Hard Code Desinc to BusinessPartner
--==================================
ALTER PROC [dbo].[RPT_SP_ECOMSalesReport] (@begin DATETIME,@end DATETIME)
AS
DELETE RPT_ECOMSalesReport WHERE DateID>=@begin AND DateID<=@end

INSERT INTO RPT_ECOMSalesReport
SELECT DateID,ItemNo,f.CustomerCode,SUM(QtyPacked) SaleQty
,SUM(case when g.BusinessPartner=2
		then (QtyPacked*ItemUnitRetailPrice+ISNULL(RetailDiscountAmount,0))
		else QtyPacked*ItemUnitSellPrice+ISNULL(DiscountAmount,0) end )  SaleAmount
,f.BusinessUnit_ID,BusinessType_ID,SUM(ItemCustStandardPrice*QtyPacked)/CASE WHEN SUM(QtyPacked)>0 THEN SUM(QtyPacked) ELSE NULL END  ItemCustStandardPrice
FROM dbo.FCT_ECOMItemShipment f
LEFT JOIN EECSystem.dbo.tblCustomers g ON f.CustomerCode=g.CustomerCode AND f.BusinessUnit_ID=g.BusinessUnit_ID
WHERE DateID>=@begin AND DateID<=@end
GROUP BY DateID,ItemNo,f.CustomerCode,f.BusinessUnit_ID,BusinessType_ID
UNION ALL
SELECT DateID,ItemNo,CustomerGroup,SUM(ShippedQty) SaleQty,SUM(SalesAmount)  SaleAmount,BusinessUnit_ID,BusinessType_ID,sum(ItemCustStandardPrice*ShippedQty)/CASE WHEN SUM(ShippedQty)>0 THEN SUM(ShippedQty) ELSE NULL END  
--INTO RPT_ECOMSalesReport
FROM dbo.FCT_MacolaItemShipment
WHERE DateID>=@begin AND DateID<=@end
GROUP BY DateID,ItemNo,CustomerGroup,BusinessUnit_ID,BusinessType_ID

GO