IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('SalesRevokedAudit')))
THEN Drop view SalesRevokedAudit
END IF

GO

create view DBA.SalesRevokedAudit as
  select SO.UNP as UNP, --уникален номер на продажба – съгласно т. 9;
    convert(varchar(10),SO.DocNumbers) as DocNomer, --системен номер на продажбата, присвоен от софтуера;
    SO.StoreName as StoreName,
    convert(date,SO.MarkDateTime) as StartDocDate,
    SO.WorkPlaceID as PosId,
    SO.UserID as UserKod,
    round(SUM(SL.Qty*(SL.SellPrice/(1+SL.VatRate)*(1+Sl.Discount/100))),2) as TotalSumNoVat,
    round(SUM(SL.Qty*(SL.SellPrice*(1+Sl.Discount/100))),2) as TotalSumWithVat,
    TotalSumWithVAT-TotalSumNoVAT as SumVAT,
    round(SUM(SL.Qty*(SL.SellPrice*(-Sl.Discount/100))),2) as SumDiscounts,
    convert(date,SO.MarkDateTime) as EndDocDate,
    SO.HotelID as StoreN,
    SO.FiscalDeviceN as FiscalDeviceN,
    SO.UserID as UserN,
    SL.ItemID as ItemCode, --код на стоката/услугата;
    SL.ItemName as ItemName, --наименование на стоката/услугата;
    SL.Qty as SoldItems, --количество;
    round(SL.SellPrice/(1+SL.VatRate),2) as UnitPrice, --единична цена (без отстъпка) – без ДДС, в лв.;
    round(SL.SellPrice*(-SL.Discount/100),2) as Discount, --отстъпка (сума) – в лв.;
    abs(SL.VatRate*100) as VAT, --ДДС ставка;
    round(SL.SellPrice-UnitPrice,2) as VATSum, --ДДС – сума, в лв.;
    round(SoldItems*SL.SellPrice*(1+SL.Discount/100),2) as CostSum, --обща сума – в лв.
    null as EndDocTime,
    convert(date,SO.MarkDateTime) as StartDocTime,
    convert(date,SL.InsDateTime) as SaleRevokedDate,
    convert(time,SL.InsDateTime) as SaleRevokedTime,
    'Анулирана' as Status
    from DBA.SaleOrderLines as SL
      join DBA.SaleOrders as SO on SL.DocID = SO.ID
    where SL.Status = 2
    group by SO.UNP,SO.DocNumbers,SO.StoreName,SO.MarkDateTime,SO.WorkPlaceID,SO.UserID,SO.HotelID,So.fiscalDeviceN,Sl.ItemId,Sl.ItemName,SL.Qty,SL.ID,SL.SellPrice,SL.VatRate,SL.Discount,SL.InsDateTime union all
  select SO.UNP as UNP, --уникален номер на продажба – съгласно т. 9;
    convert(varchar(10),SO.DocNumbers) as DocNomer, --системен номер на продажбата, присвоен от софтуера;
    SO.StoreName as StoreName,
    convert(date,SO.MarkDateTime) as StartDocDate,
    SO.WorkPlaceID as PosId,
    SO.UserID as UserKod,
    round(SUM(SL.Qty*(SL.SellPrice/(1+SL.VatRate)*(1+Sl.Discount/100))),2) as TotalSumNoVat,
    round(SUM(SL.Qty*(SL.SellPrice*(1+Sl.Discount/100))),2) as TotalSumWithVat,
    TotalSumWithVAT-TotalSumNoVAT as SumVAT,
    DBA.CalcNotpayedSaleSumDiscount(SO.ID) as SumDiscounts,
    convert(date,SO.MarkDateTime) as EndDocDate,
    SO.HotelID as StoreN,
    SO.FiscalDeviceN as FiscalDeviceN,
    SO.UserID as UserN,
    SL.ItemID as ItemCode, --код на стоката/услугата;
    SL.ItemName as ItemName, --наименование на стоката/услугата;
    SL.Qty as SoldItems, --количество;
    round(SL.SellPrice/(1+SL.VatRate),2) as UnitPrice, --единична цена (без отстъпка) – без ДДС, в лв.;
    round(SL.SellPrice*(-SL.Discount/100),2) as Discount, --отстъпка (сума) – в лв.;
    abs(SL.VatRate*100) as VAT, --ДДС ставка;
    round(SL.SellPrice-UnitPrice,2) as VATSum, --ДДС – сума, в лв.;
    round(SoldItems*SL.SellPrice*(1+SL.Discount/100),2) as CostSum, --обща сума – в лв.
    null as EndDocTime,
    convert(date,SO.MarkDateTime) as StartDocTime,
    convert(date,SL.InsDateTime) as SaleRevokedDate,
    convert(time,SL.InsDateTime) as SaleRevokedTime,
    'Анулирана' as Status
    from DBA.SaleOrders as SO left outer join DBA.SaleOrderLines as SL on SO.ID = SL.DocID
    where SO.DocType = 6
    group by So.ID,SO.UNP,SO.DocNumbers,SO.StoreName,SO.MarkDateTime,SO.WorkPlaceID,SO.UserID,SO.HotelID,So.fiscalDeviceN,Sl.ItemId,Sl.ItemName,SL.Qty,SL.ID,SL.SellPrice,SL.VatRate,SL.Discount,SL.InsDateTime
//[v 20.12.2018]