IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vwRpAcCSRpRBDetail]') AND OBJECTPROPERTY(id, N'IsView') = 1)
DROP VIEW [dbo].[vwRpAcCSRpRBDetail]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
---2006/09/12--- AcStkuom.UomDescription
---2006/11/27--- Add ArticleNo
---2006/12/05--- Add Package Feature
---2007/04/04--- Group by Brand,Group and Category 
---2007/06/21--- Keric,6-51025, solve DiscountPercent2 zero problem if discount1=0 and discount2<>0
---2007/10/03--- Add ItemTotalWithouGST 6.51030
---15/10/2010,6.60008     Keric:remove ItemTotalWithouGST
-- 18/02/2014,2014.1GST	  Keric:added ItemTotalCalc,GSTTaxCode for Msia GST


CREATE     VIEW [dbo].[vwRpAcCSRpRBDetail]
AS(
SELECT d.*, AcStk.Description, AcStk.Description1,
AcStk.Price1,AcStk.Price2,AcStk.Price3,AcStk.Price4,AcStk.Price5,
AcStk.Brand As StockBrand,AcStk.Category, AcStk.Grp as 'StockGroup',
AcBrand.Description as BrandDescription, AcGroup.Description as GroupDescription,
AcCat.Description as CategoryDescription,
AcStk.Colour,AcStk.Sizes, AcStk.ArticleNo, AcColour.description as ColourDescription,
Acsize.description as sizeDescription,AcStkuom.Barcode,AcStkuom.UomDescription,

case 
  when d.IsGSTTaxExc='Y' then d.ItemTotal
  when d.IsGSTTaxExc<>'Y' then d.ItemTotalWGST
  else d.ItemTotal
end as ItemTotalCalc,

case 
  when d.IsGSTTaxExc='Y' then d.AcGstTaxCode
  when d.IsGSTTaxExc<>'Y' and d.GSTRateD<>0 then d.AcGstTaxCode+'*'
  else d.AcGstTaxCode
end as GSTTaxCode,

ISNULL((uomqty*price),0) as GrossAmount,
(case when (uomqty<>0 and price<>0 and discount1<>0) then Discount1/(cast(uomqty as money)*price)*100 else 0 end )as DiscountPercent1,
(case when (uomqty<>0 and price<>0 and discount2<>0) then Discount2/(cast(uomqty as money)*price-Discount1)*100 else 0 end )as DiscountPercent2,
(case when (uomqty<>0 and price<>0 and d.discount<>0) then d.Discount/(cast(uomqty as money)*price)*100 else 0 end )as TotalPercent,
dbo.pfGetSupplierSerial (d.DocumentNo,'%',d.StockID,'CSH','AR',d.ItemNo) as SerialNo,
ISNULL((case when acstk.stocktype<>'NSTKS' then (uomqty*price) end),0) as TotalParts,
ISNULL((case when acstk.stocktype='NSTKS' then (uomqty*price) end),0) as TotalServices,

p.Description as PackageDescription,
p.UnitOfMeasure as PackageUOM,

case when d.Package is Null then
d.Price
else
(Select Sum(f.Price) from AcCSD f where f.DocumentNo=d.DocumentNo
and f.Package=d.Package and f.PackageItemNo=d.PackageItemNo) end as PackagePrice,

case when d.Package is Null then
d.Discount
else
(Select Sum(f.Discount) from AcCSD f where f.DocumentNo=d.DocumentNo
and f.Package=d.Package and f.PackageItemNo=d.PackageItemNo) end as PackageDiscount,

case when d.Package is Null then
Round((cast(d.UomQty as money) * d.Price-d.Discount),2)
else
(Select Round(Sum(f.UomQty*f.Price-f.Discount),2) from AcCSD f where f.DocumentNo=d.DocumentNo
and f.Package=d.Package and f.PackageItemNo=d.PackageItemNo) end as PackageItemTotal,

case when d.StockID = d.Package then
''
else d.StockID end as PackageItems,

case when d.StockID = d.Package then
''
else d.Notes end as PackageItemsNotes,

case when d.StockID = d.Package then
Null
else d.UomQty end as PackageItemsQty,

case when d.StockID = d.Package then
''
else d.UOM end as PackageItemsUOM

from AcCSD d
LEFT OUTER JOIN AcSTK ON d.StockID = AcSTK.Code
LEFT OUTER JOIN AcBrand ON AcBrand.code = AcSTK.Brand
LEFT OUTER JOIN AcGroup ON AcGroup.code = AcSTK.Grp
LEFT OUTER JOIN AcCat ON AcCat.code = AcSTK.Category
LEFT OUTER JOIN Accolour ON Accolour.code = AcSTK.Colour
LEFT OUTER JOIN Acsize ON Acsize.code = AcSTK.sizes
LEFT OUTER JOIN Acstkuom ON (Acstkuom.stockid = d.stockid and Acstkuom.uom = d.uom) 
LEFT OUTER JOIN AcSTK p ON d.Package = p.Code
)
GO
