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

GO

create view DBA.SalesDetailsAudit 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, --обща сума – в лв.
    'Затворена' 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.SaleOrders as SO on SL.DocID = SO.ID union all
  select SO.UNP as UNP, --уникален номер на продажба – съгласно т. 9;
    null as DocNomer, --системен номер на продажбата, присвоен от софтуера;
    SO.StoreName as StoreName,
    convert(date,SO.MarkDateTime) as StartDocDate,
    SO.WorkPlaceID as PosId,
    SO.UserId as UserKod,
    DBA.CalcNotPayedSaleTotalNoVat(SO.ID) as TotalSumNoVat,
    DBA.CalcNotPayedSaleGrandTotal(SO.ID) 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.RegQty-SL.PayedQty) 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, --обща сума – в лв.
    (if SL.Status = 1 then 'Отворена' else if SL.Status = 2 then 'Анулирана' else if SL.Status = 3 then 'Анулираща' endif endif endif) as Status
    from DBA.SaleOrderLines as SL join DBA.SaleOrders as SO on SL.DocID = SO.ID
    where SL.RegQty <> SL.PayedQty
//[v 18.12.2018]