ALTER procedure [dbo].[sp_SaveInvoice]
(
 @InvNo varchar(50), @BranchCode varchar(10)
 )
AS
begin
SELECT Invoice.DELDate,Invoice.Branch,Invoice.CreditPeriod,Invoice.Bank1,Invoice.Bank2,Invoice.Bank3,Invoice.Bank4,
Invoice.Del1,Invoice.Del2,Invoice.Del3,Invoice.Del4,Invoice.PODate,Invoice.Freight,Invoice.Retention, UPPER(LEFT(Invoice.BillType, 1)) + LOWER(SUBSTRING(Invoice.BillType, 2, LEN(Invoice.BillType))) AS BillType,
Invoice.InvNo,Invoice.EntryDate,Invoice.PONo,Invoice.DnNo,Invoice.RefNo,Invoice.CustomerCode,Invoice.CustomerName,
Invoice.Address,Invoice.Phone,Invoice.Fax,Invoice.Attended,Invoice.Narration,Invoice.GrossAmount,Invoice.NetAmount,
Invoice.LessAmount  AS less,InvoiceItem.SlNo,InvoiceItem.ItemCode,InvoiceItem.ItemName,JobCard.PlateNo,JobCard.Model as Model,JobCard.JobCardDate as JobCardDate,
InvoiceItem.PartNo,InvoiceItem.Rate,InvoiceItem.Unit,InvoiceItem.Qty,InvoiceItem.Amount,Item.CategoryName,InvoiceItem.ArabicName as ItemNameAr,
CONVERT(nchar, isnull(HijriDate,'01/01/1900'), 131) as HijriDate,isnull(invoice.QuotNo,'') as QuotNo
,ISNULL(INVOICEITEM.VATPER,0) AS VATPER,ISNULL(INVOICEITEM.VATAMT,0)AS VATAMT,ISNULL(INVOICE.VATTOTAL,0)AS VATTOTAL
,ISNULL(INVOICE.Vatno,'')AS Vatno,isnull(invoiceitem.rateafterdisc,0) as Rateafterdisc,ISNULL(INVOICE.REMARKS1,'') AS REMARKSS,INVOICE.MOBILE
,ISNULL(CONVERT(VARCHAR(25),D.ENTRYDATE,103),CONVERT(VARCHAR(25),INVOICE.ENTRYDATE,103)) AS DNDATE,ISNULL(C.namear,'') as Arabicname,isnull(C.address1ar,'') as ArabicAddress 
 ,CONVERT(VARCHAR(25),Invoice.EntryDate,103) 'DATE' ,
 dbo.currency_conversion(Invoice.NetAmount) as ARABICNETAMOUNT,isnull(pinvno,'')as pinvno,isnull(bankcode,'')as bankcode,isnull(bankname,'')as bankname,isnull(bankamount,'')as bankamount 
 ,isnull(vat,0)As vat,isnull(EINVOICE.qrcode,'')as qrcode

 ,isnull(C.ContactArabic,'')as ContactArabic,isnull(C.phoneArabic,'')as phoneArabic,isnull(C.FaxArabic,'')as FaxArabic,isnull(C.emailArabic,'')as emailArabic,isnull(C.mobArabic,'')as mobArabic,
 isnull(C.vatnoArabic,'')as vatnoArabic,isnull(C.BuildingNo,'')as BuildingNo,isnull(C.StreetName,'')as StreetName,isnull(C.District,'')as District,isnull(C.City,'')as City,isnull(C.Country,'')as Country,
 isnull(C.PostalCode,'')as PostalCode,isnull(C.AdditionalNo,'')as AdditionalNo,isnull(C.OtherSellerNo,'')as OtherSellerNo,isnull(C.BuildingNoArabic,'')as BuildingNoArabic,isnull(C.StreetNameArabic,'')as StreetNameArabic,
 isnull(C.DistrictArabic,'')as DistrictArabic,isnull(C.CityArabic,'')as CityArabic,isnull(C.CountryArabic,'')as CountryArabic,isnull(C.PostalCodeArabic,'')as PostalCodeArabic,isnull(C.AdditionalNoArabic,'')as AdditionalNoArabic 
 ,isnull(C.OtherSellerNoArabic,'')as OtherSellerNoArabic,isnull(cd.vatno,'')as cdvatno
  ,ISNULL(CD.Name,'')AS CDNAME,ISNULL(CD.ADDRESS1,'')AS CDADDRESS,ISNULL(CD.namear,'') as CDArabicname,isnull(CD.address1ar,'') as CDArabicAddress ,isnull(CD.ContactArabic,'')as CDContactArabic,isnull(CD.phoneArabic,'')as CDphoneArabic
 ,isnull(CD.FaxArabic,'')as CDFaxArabic,isnull(CD.emailArabic,'')as CDemailArabic,isnull(CD.mobArabic,'')as CDmobArabic,
 isnull(CD.vatnoArabic,'')as CDvatnoArabic,isnull(CD.Address1,'')as CDBuildingNo,isnull(CD.Address2,'')as CDStreetName,isnull(CD.District,'')as CDDistrict,isnull(CD.Address3,'')as CDCity,isnull(CD.Country,'')as CDCountry,
 isnull(CD.PostalCode,'')as CDPostalCode,isnull(CD.AdditionalNo,'')as CDAdditionalNo,isnull(CD.OtherSellerNo,'')as CDOtherSellerNo,isnull(CD.Address1AR,'')as CDBuildingNoArabic,isnull(CD.Address2AR,'')as CDStreetNameArabic,
 isnull(CD.DistrictArabic,'')as CDDistrictArabic,isnull(CD.Address3AR,'')as CDCityArabic,isnull(CD.CountryArabic,'')as CDCountryArabic,isnull(CD.PostalCodeArabic,'')as CDPostalCodeArabic,isnull(CD.AdditionalNoArabic,'')as CDAdditionalNoArabic 
 ,isnull(CD.OtherSellerNoArabic,'')as CDOtherSellerNoArabic,isnull(CD.VATNO,'')as CDVATNO
 ,isnull(logoimage,'')as logoimage,isnull(signatureimage,'')as signatureimage,isnull(sealimage,'')as sealimage,isnull(footerimage,'')as footerimage,isnull(headderimage,'')as headderimage
 FROM Invoice INNER JOIN InvoiceItem ON Invoice.InvNo = InvoiceItem.InvNo AND Invoice.SR = 'Sales'and  InvoiceItem.SR = 'Sales' 
LEFT JOIN DELIVERYNOTE D ON D.DNNO=INVOICEITEM.DNNO LEFT JOIN CUSTOMER C ON C.CODE=INVOICE.CUSTOMERCODE 
LEFT JOIN EINVOICE ON EINVOICE.EINVNO=INVOICE.INVNO
LEFT JOIN Item ON InvoiceItem.ItemCode = Item.ItemCode 
LEFT JOIN JobCard ON Invoice.JobCardNo=JobCard.JobCardNo

JOIN Companydetails CD ON ISNULL(CD.BranchCode,@BranchCode)=@BranchCode
where Invoice.InvNo=@InvNo



end


go