SELECT ord.R1, ord.R2, ord.R3, ord.R4, ord.R5, ord.R6, ord.R7, ord.R8, ord.R9, ord.R10, ord.R11, ord.R12, ord.Ad1, ord.Ad2, ord.Ad3, ord.Ad4, ord.InqNo, ord.ArDt, ord.EmpNo, ord.EmpPrGr, ord.Rsp, ord.NoUn, ord.Area, ord.DtyFrAm, ord.DDtyFrAm, ord.Trunc, ord.DTrunc, ord.LstSetDt, ord.DifProd, ord.CfCnt, ord.CfDelDt, ord.PurcNo, ord.PurcCnt, ord.RmtSup, ord.GWgtCoSF, ord.GWgtTot, ord.ExPr, ord.EDISt, ord.ExSpc, ord.YrRef, ord.FactNo, ord.InvoSF, ord.InvoAm, ord.InvoIF, ord.DInvoIF, ord.InvoPl, ord.InvoNo, ord.LstInvDt, ord.InvoRef, ord.InvoCnt, ord.InvoCust, ord.OvCNOSm, ord.FrAm, ord.FrAm2, ord.FrAm3, ord.FrAm4, ord.FrCusPro, ord.ConNo, ord.ConCnt, ord.Free1, ord.Free2, ord.Free3, ord.Free4, ord.FinBy, ord.FinWgt, ord.FinDt, ord.SPrAdd, ord.PacNo, ord.PacCnt, ord.DueDt, ord.InqCnt, ord.ConsNo, ord.ERetDt, ord.Gr, ord.Gr2, ord.Gr3, ord.Gr4, ord.Gr5, ord.Gr6, ord.Gr7, ord.Gr8, ord.Gr9, ord.Gr10, ord.Gr11, ord.Gr12, ord.MainOrd, ord.IgnTest, ord.OrdBasNo, ord.PropPrc, ord.PropNo, ord.Package, ord.[LiaActNo], ord.[LiaAct2], ord.AcSet, ord.CtrAm, ord.DCtrAm, ord.CstExRt, ord.CstCur, ord.CustNo, ord.CustPrGr, ord.CustPrG2, ord.CustPrG3, ord.FrStc, ord.Ctry, ord.SupNo, ord.DelPri, ord.DelMt, ord.DelTrm, ord.DelAd1, ord.DelAd2, ord.DelAd3, ord.DelAd4, ord.DelActNo, ord.DelCtry, ord.DelNm, ord.DelPArea, ord.DelPNo, ord.DelLang, ord.Lgt, ord.WageRtNo, ord.NoteNm, ord.VatAm, ord.DVatAm, ord.ObVatNo, ord.Label, ord.Nm, ord.NWgt, ord.OfNo, ord.OfCnt, ord.ExVat, ord.CfNo, ord.OrdDt, ord.OrdNo, ord.CSOrdNo, ord.OrdPref, ord.OrdPrGr, ord.OrdBal, ord.DOrdBal, ord.OrdPrSt, ord.OrdPrSt2, ord.NOrdSum, ord.DNOrdSum, ord.OrdTp, ord.DepDt, ord.PicNo, ord.PicCnt, ord.PArea, ord.PNo, ord.PrSup, ord.SaleCstP, ord.ProdNo, ord.ProdCnt, ord.ReqNo, ord.SpcTxAm, ord.DSpcTxAm, ord.Lang, ord.OrdSumT, ord.DOrdSumT, ord.SelBuy, ord.DocSMt, ord.DocSMt2, ord.RlDelDt, ord.Tare, ord.TestRes, ord.TotDcP, ord.TotDcAm, ord.TotDcDAm, ord.TransGr, ord.TransGr2, ord.TransGr3, ord.TransGr4, ord.TrTp, ord.TspAgrNo, ord.PstAm, ord.CIncCst, ord.IncCst, ord.CIncSF, ord.IncSF, ord.CIncIF, ord.IncIF, ord.Inf, ord.Inf2, ord.Inf3, ord.Inf4, ord.Inf5, ord.Inf6, ord.CreUsr, ord.CreDt, ord.CreTm, ord.FCfDelDt, ord.ExRt, ord.Cur, ord.WebPg, ord.VoSr, ord.TonRt, ord.Vol, ord.OurRef, ord.DelDt, ord.ToStc, ord.ExOrdPrc, ord.NordSum+ord.Vatam [TotalGrossPrice], Cur.ISO as [CurISO], OrdActor.Phone CUSTPHONE, OrdActor.MailAd CUSTEMAILADDRESS, OrdInvoice.Nm INVNAME, OrdInvoice.Ad1 INVAD1, OrdInvoice.Ad2 INVAD2, OrdInvoice.Ad3 INVAD3, OrdInvoice.Ad4 INVAD4, OrdInvoice.Pno INVZIPCODE, OrdInvoice.PArea INVCITY, OrdInvoice.Ctry INVCOUNTRY, OrdInvoice.Phone INVPHONE, OrdInvoice.MailAd INVEMAILADDRESS, OrdInvoice.Fax INVFAX, OrdDelivery.Phone DELPHONE, OrdDelivery.MailAd DELEMAILADDRESS, OrdDelivery.Fax DELFAX, OrdDelTrmTxt.txt DELIVERYTERMTEXT, OrdDelMtTxt.txt DELIVERYMETHODTEXT, PmtMt.Descr PAYMENTMETHODTEXT, PmtTrm.Descr PAYMENTTERMTEXT, Stc.Nm WAREHOUSENAME, Emp.Nm EmpName, emp.Phone EmpPhone, emp.MailAd EmpMail, Invoices.Data Pdf, OrdConf.Data OrdConfPdf, Consign.Data ConsignPdf
FROM Ord LEFT OUTER JOIN Cur ON Cur.CurNo = Ord.Cur LEFT OUTER JOIN CustV OrdActor ON OrdActor.CustNo = Ord.CustNo LEFT OUTER JOIN CustV OrdInvoice ON OrdInvoice.CustNo = Ord.InvoCust LEFT OUTER JOIN CustV OrdDelivery ON OrdDelivery.ActNo = Ord.DelActNo LEFT OUTER JOIN Txt OrdDelTrmTxt ON OrdDelTrmTxt.TxtTp = 4 AND OrdDelTrmTxt.TxtNO = Ord.DelTrm AND OrdDelTrmTxt.Lang = 47 LEFT OUTER JOIN Txt OrdDelMtTxt ON OrdDelMtTxt.TxtTp = 5 AND OrdDelMtTxt.TxtNO = Ord.DelMt AND OrdDelMtTxt.Lang = 47 LEFT OUTER JOIN PmtMt ON PmtMt.PmtMtNo = Ord.PmtMt LEFT OUTER JOIN PmtTrm ON PmtTrm.PmtTrmNo = Ord.PmtTrm LEFT OUTER JOIN Stc ON Stc.StcNo = Ord.FrStc LEFT OUTER JOIN EmpV Emp on Ord.EmpNo > 0 and Ord.EmpNo = Emp.EmpNo OUTER APPLY (SELECT (SELECT Invoice.invono ‘@InvoNo’, FrmData.PDFPath + ” + DB_NAME() + ‘\FaxMail\Invoice’ + CONVERT(nvarchar(max), Invoice.invono) + ‘.pdf’ ‘@Pdf’, CASE WHEN Invoice.NOrdSum > 0 THEN ‘Faktura’ ELSE ‘Kreditnota’ END ‘@DocType’ FROM OrdDoc Invoice CROSS JOIN FrmData WHERE Invoice.OrdNo = Ord.OrdNo and DocTp = 1 FOR XML PATH(‘Invoice’), ROOT(‘Invoices’)) Data )Invoices OUTER APPLY (SELECT (SELECT OrdConf.cfcnt ‘@InvoNo’, FrmData.PDFPath + ” + DB_NAME() + ‘\FaxMail\OrderConf’ + CONVERT(nvarchar(max), OrdConf.CfNo) + ‘.pdf’ ‘@Pdf’, ‘Ordrebekreftelse’ as ‘@DocType’ FROM OrdDoc OrdConf CROSS JOIN FrmData WHERE OrdConf.OrdNo = Ord.OrdNo and DocTp = 5 FOR XML PATH(‘Invoice’), ROOT(‘Invoices’))Data)OrdConf OUTER APPLY (SELECT (SELECT OrdConf.pacno ‘@InvoNo’, FrmData.PDFPath + ” + DB_NAME() + ‘\FaxMail\Consign’ + CONVERT(nvarchar(max), OrdConf.pacno) + ‘.pdf’ ‘@Pdf’, ‘Pakkseddel’ as ‘@DocType’ FROM OrdDoc OrdConf CROSS JOIN FrmData WHERE OrdConf.OrdNo = Ord.OrdNo and DocTp = 3 FOR XML PATH(‘Invoice’), ROOT(‘Invoices’))Data)Consign WHERE Ord.InvoCust > 0