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

GO

create view DBA.SalesReversedAudit
  as select INV.UNP as UNP, --уникален номер на продажба – съгласно т. 9;
    convert(varchar(10),INV.InvoiceNo) as DocNomer, --системен номер на продажбата, присвоен от софтуера;
    SO.StoreName as StoreName,
    convert(date,SO.MarkDateTime) as StartDocDate,
    SO.WorkPlaceID as PosId,
    INV.KodAdmin as UserKod,
    DBA.CalcSaleInvoiceTotalNoVat(INV.InvoiceNo) as TotalSumNoVat,
    DBA.CalcSaleInvoiceGrandTotal(INV.InvoiceNo) as TotalSumWithVat,
    TotalSumWithVAT-TotalSumNoVAT as SumVAT,
    DBA.CalcSaleInvoiceSumDiscount(INV.InvoiceNo) as SumDiscounts,
    INV.InvoiceDateDT as EndDocDate,
    SO.HotelID as StoreN,
    SO.FiscalDeviceN as FiscalDeviceN,
    INV.KodAdmin as UserN,
    SL.ItemID as ItemCode, --код на стоката/услугата;
    SL.ItemName as ItemName, --наименование на стоката/услугата;
    A.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, --обща сума – в лв.
    convert(date,I1.TrueInvoiceHour) as EndDocTime,
    (select first SaleOrders.MarkDateTime from DBA.SaleOrders where SaleOrders.DocType = 0 and SaleOrders.UNP = SO.UNP and SaleOrders.GuestNum = SO.GuestNum) as StartDocTime,
    convert(date,SO.MarkDateTime) as SaleReversedDate,
    convert(time,SO.MarkDateTime) as SaleReversedTime,
    'Затворена' as Status
    from DBA.Invoices as INV join DBA.Articles as A on INV.InvoiceNo = A.InvoiceNo
      join DBA.SaleOrderLines_Articles as SA on SA.InvoiceNo = INV.InvoiceNo and SA.ArticleNo = A.ArticleNo
      join DBA.SaleOrderLines as SL on SA.SaleLineID = SL.ID
      join DBA.SaleOrderLines as SL1 on SL1.SaleLineGuid = SL.SaleLineGuid and SL1.ID <> SL.ID
      join DBA.SaleOrderLines_Articles as SA1 on SA1.SaleLineID = SL1.ID
      join DBA.Invoices as I1 on SA1.InvoiceNo = I1.InvoiceNo
      join DBA.SaleOrders as SO on SL.DocID = SO.ID
    where SO.DocType = 5