Ok, so here’s the scoop. I have a customer that wants to have a field on their ARForm that shows the total from previously billed invoices that are attached to the milestones that are in a project. So if a project has 9 milestones and 4 of them have been invoiced, on any of those 4 given invoices ,if they are printed, they will show the total of what has been billed across those 4 invoices. i.e. Invoice 1: $13, Invoice 2: $5, Invoice 3: $17, Invoice 4: $20, etc. The field would show the Total billed: $58. There are two additional fields that I have no problem finding the values for. The firs is the Cost of the Total project, and the current invoice value. So then the total billed and the current invoice, are both subtracted from the total cost of the project and the remainder is shown. Which would obviously be the total of the remaining 5 invoices.
I was trying to do this through my sql query. The issue I am running into is that I can only ever pull in current invoices milestone value. I figured out how to exclude that one, but I still for some reason cannot pull in the other milestones.
Here is my SQL query:
WITH PreviousMilestones AS (
SELECT
Company,
OrderNum,
SUM(BillingAmount) AS TotalBilledAmount,
COUNT(DISTINCT InvoiceNum) AS CompletedInvoices
FROM
ProjectMilestone_" + Parameters!TableGuid.Value + "
WHERE
Complete = 1
GROUP BY
Company,
OrderNum
)
SELECT
T1.RptLanguageID,
T1.Company,
T1.CreditMemo,
T1.Calc_ExtPriceTotal,
T1.CustNum,
T1.DocDepositCredit,
T1.DocInvoiceAmt,
T1.Calc_TotalMiscChrg,
T1.Calc_TotalAdvBillCred,
T1.DocRounding,
T1.DocumentPrinted,
T1.Posted,
T1.InvoiceComment,
T1.InvoiceDate,
T1.InvoiceNum,
T1.InvoiceType,
T1.LegalNumber,
T1.PONum,
T1.SoldToInvoiceAddress,
T1.Calc_BillToAddressList,
T1.Calc_BottomAddress,
T1.Calc_CompanyAddressList,
T1.Calc_CurrDocDesc,
T1.Calc_CurSymbol,
T1.Calc_CustContactName,
T1.Calc_CustPartOpts,
T1.Calc_DteOrdrd,
T1.Calc_fFOB,
T1.Calc_MulPackNum,
T1.Calc_mulponum,
T1.Calc_MulShipDate,
T1.Calc_MulShipTo,
T1.Calc_MulShipVia,
T1.Calc_MultSoldTo,
T1.Calc_MultTaxID,
T1.Calc_NumRecordPerPage,
T1.Calc_NumTotalParts,
T1.Calc_PackNum,
T1.Calc_PrintBottomAddress,
T1.Calc_SalesPerson,
T1.Calc_SalesTerms,
T1.Calc_ShipDate,
T1.Calc_ShipToAddressList,
T1.Calc_ShipToContactName,
T1.Calc_ShipVia,
T1.[Calc_Voucher-String] as Calc_Voucher_String,
T1.CurrencyCode_CurrencyID,
T1.CurrencyCode_DecimalsGeneral,
T1.CurrencyCode_DecimalsPrice,
T1.Calc_SEBankRef,
T1.Calc_CustResaleID,
T1.CHISRCodeLine,
T1.Calc_CHBankAcctIBANCode,
T1.Calc_CHBankAcctISRPartyID,
T1.Calc_DocDspTaxAmt,
T1.Calc_IsDraftCopy,
T1.FirstPrintDate,
T1.Calc_ReverseChargeTax as Calc_ReverseChargeTaxTotal,
T1.DocCopyNum,
T1.PayDiscDays,
T1.OrderNum,
T1.DueDate,
T1.Description AS InvDescription,
T2.Calc_TrackByAttribute,
T2.AttributeSetShortDescription,
T2.Company as InvcDtl_Company,
T2.DocAdvanceBillCredit,
T2.DocDiscount,
T2.DocUnitPrice,
T2.InvoiceComment as InvcDtl_InvoiceComment,
T2.InvoiceLine,
T2.InvoiceNum as InvcDtl_InvoiceNum,
T2.PackLine,
T2.PackNum,
T2.DropShipPackSlip,
T2.PartNum,
T2.POLine,
T2.PricePerCode,
T2.RevisionNum,
T2.SalesUM,
T2.SellingOrderQty,
T2.SellingShipQty,
T2.ShipDate,
T2.XPartNum,
T2.XRevisionNum,
T2.Calc_ActDate,
T2.Calc_BackOrdQty,
T2.Calc_UnitPrice,
T2.Calc_ExtPrice,
T2.Calc_Duration,
T2.Calc_GetNextLegalNum,
T2.Calc_InvcComment,
T2.Calc_InvoiceDisplayLine,
T2.Calc_IsKitParent,
T2.Calc_JobNumber,
T2.Calc_LabDur,
T2.Calc_LabMod,
T2.Calc_Labor,
T2.Calc_LineDesc,
T2.Calc_LineSoldToAddressList,
T2.Calc_MatDur,
T2.Calc_Mate,
T2.Calc_MatMod,
T2.Calc_Misc,
T2.Calc_MiscDur,
T2.Calc_MiscMod,
T2.Calc_Modifier,
T2.Calc_NextLegalNumID,
T2.Calc_NumLineByInv,
T2.Calc_ponum,
T2.Calc_PSLegalNum,
T2.Calc_Reference,
T2.Calc_SerialNumber,
T2.Calc_SerialNumber2,
T2.Calc_SerialNumber3,
T2.Calc_SerialNumber4,
T2.Calc_SerialNumber5,
T2.Calc_SerialNumber6,
T2.Calc_SerialNumber7,
T2.Calc_ShipToAddressList as InvcDtl_Calc_ShipToAddressList,
T2.Calc_ShipToContactName as InvcDtl_Calc_ShipToContactName,
T2.Calc_ShipToShipVia,
T2.Calc_WarrDesc,
T2.Calc_WhseCode,
T2.OrderLine_KitFlag,
ISNULL(T2.OrderLine_KitPricing,'') AS OrderLine_KitPricing,
ISNULL(T2.OrderLine_KitPrintCompsInv,0) AS OrderLine_KitPrintCompsInv,
ISNULL(T2.OrderLine_KitShipComplete,0) AS OrderLine_KitShipComplete,
T2.OrderLine_KitsLoaded,
T2.PartNum_PartDescription,
T2.EpicorFSA,
T3.CallComment,
T3.CallQty,
T3.PartNum as FSCallDt_PartNum,
T3.RevisionNum as FSCallDt_RevisionNum,
T3.XPartNum as FSCallDt_XPartNum,
T3.XRevisionNum as FSCallDt_XRevisionNum,
T3.Calc_CProb,
T3.Calc_fCallLine,
T3.Calc_InvoiceLine,
T3.Calc_InvoiceNum,
T4.Calc_InvoiceNum as FSCallMt_Calc_InvoiceNum,
T4.Calc_MatNum,
T4.Calc_MtPaNum,
T4.Calc_MtQty,
T4.Calc_MtBillPrice,
T4.Calc_MtExtPrice,
T4.Calc_MtLinedesc,
T4.Calc_MtRevNum,
T4.ResReasonCode,
T4.Calc_MtCount,
T5.RptLanguageID as Label_RptLanguageID,
T5.Calc_ActDate as Label_Calc_ActDate,
T5.Calc_CreditMemo as Label_Calc_CreditMemo,
T5.Calc_Duration as Label_Calc_Duration,
T5.Calc_JobNumber as Label_Calc_JobNumber,
T5.Calc_LabDur as Label_Calc_LabDur,
T5.Calc_Labor as Label_Calc_Labor,
T5.Calc_MatDur as Label_Calc_MatDur,
T5.RptLiteralsLMaterial as Label_Calc_Mate,
T5.Calc_Misc as Label_Calc_Misc,
T5.Calc_MiscDur as Label_Calc_MiscDur,
T5.Calc_MtExtPrice as Label_Calc_MtExtPrice,
T5.Calc_SerialNumber as Label_Calc_SerialNumber,
T5.DocUnitPrice as Label_DocUnitPrice,
T5.InvoiceLine as Label_InvoiceLine,
T5.PONum as Label_PONum,
T5.ProbReasonCode as Label_ProbReasonCode,
T5.RptLiteralsLDescription as Label_Reference,
T5.RptLiteralsLBckOrd,
T5.RptLiteralsLBillTo,
T5.RptLiteralsLTWTotalTax,
T5.RptLiteralsLComeFrom,
T5.RptLiteralsLContinueInNext,
T5.RptLiteralsLCusPart,
T5.RptLiteralsLDate,
T5.RptLiteralsLEMaila,
T5.RptLiteralsLExtPrice,
T5.RptLiteralsLFax,
T5.RptLiteralsLFOB,
T5.RptLiteralsLHdng,
T5.RptLiteralsLInvoice,
T5.RptLiteralsLLegNum,
T5.RptLiteralsLLine,
T5.RptLiteralsLLineRef,
T5.RptLiteralsLof,
T5.RptLiteralsLOurPart,
T5.RptLiteralsLPackSlp,
T5.RptLiteralsLPage,
T5.RptLiteralsLPartDesc,
T5.RptLiteralsLPartRev,
T5.RptLiteralsLPhone,
T5.RptLiteralsLPONum,
T5.RptLiteralsLAULessDeposit,
T5.RptLiteralsLQty,
T5.RptLiteralsLQtyOrd,
T5.RptLiteralsLRev,
T5.RptLiteralsLLinesSubtotal,
T5.RptLiteralsLRounding,
T5.RptLiteralsLMiscChgs,
T5.RptLiteralsLSalesKit,
T5.RptLiteralsLSeeBelow,
T5.RptLiteralsLShipTo,
T5.RptLiteralsLShpVia,
T5.RptLiteralsLSlsTxID,
T5.RptLiteralsLSoldToL,
T5.RptLiteralsLTotal,
T5.RptLiteralsLWarrantyA,
T5.RptLiteralsLWHCode,
T5.ShipDate as Label_ShipDate,
T5.UnitPrice as Label_UnitPrice,
T5.RptLiteralsLSEOCR,
T5.RptLiteralsLNOKID,
T5.RptLiteralsLDiscountPercent,
T5.RptLiteralsLVatNr,
T5.RptLiteralsLlessAdvBill,
T5.RptLiteralsLAttributeSet,
T5.RptLiteralsLFSAEquipmentPartNum,
T5.RptLiteralsLFSAServiceOrderNum,
T1.Calc_CustFax,
T6.EMailAddress,
T6.FaxNum,
T6.ResaleID,
T5.RptLiteralsLOrdered as Label_Calc_DteOrdrd,
T5.RptLiteralsLSalsRep as Label_Calc_SalesPerson,
T5.RptLiteralsLTrms as Label_Calc_SalesTerms,
T1.Calc_CustEMailAddress,
T1.Calc_DropShipPackSlip,
T7.InvoiceComment as FSContDt_InvoiceComment,
T7.ContractQty,
T7.PricePerUnit,
T7.DocPricePerUnit,
T7.Calc_ContPrice,
T5.PricePerCode as Label_PricePerCode,
T5.PartNum as Label_PartNum,
T5.ExtPrice as Label_ExtPrice,
T1.Calc_PELegalText,
T5.RptLiteralsLAmt,
T5.Calc_SEBankRef as Label_Calc_SEBankRef,
T1.Calc_TaxMethod,
T1.SEBankRef,
T1.Calc_LessPrepaidDeposits,
T5.RptLiteralsLLessPrepDeposits,
T5.Calc_ReverseChargeTax as RptLiteralsLReverseChargeTax,
T7.ContractLine,
T7.PartNum as ContractPartNum,
T7.PartNum_PartDescription as ContractPartDescription,
T7.ContractNum as ContractNum,
(CASE WHEN EXISTS(SELECT 1 FROM InvcChrg_" + Parameters!TableGuid.Value + " Tmp WHERE Tmp.Company = T1.Company AND Tmp.InvoiceNum= T1.InvoiceNum AND Tmp.InvoiceLine = T2.InvoiceLine) THEN 1 ELSE 0 END) AS 'HasFinCharges',
(CASE WHEN T8.Description IS NOT NULL THEN 1 ELSE 0 END ) AS 'HasMiscCharges',
T8.[Description],
T8.DocMiscAmt,
T8.SeqNum,
T5.RptLiteralsLMiscChgs,
T5.[Description] AS Label_Description,
(CASE WHEN T8.SeqNum = T9.MinSeqNum THEN 1 ELSE 0 END ) AS DisplayLineInfo,
T5.RptLiteralsLCopy,
T5.RptLiteralsLPrintedAt,
T5.RptLiteralsLFirstPrinted,
T5.RptLiteralsLDraftCopy,
T5.RptLiteralsLInternalUse,
T10.FSAEquipmentPartNum,
T10.FSAServiceOrderNum,
T5.Calc_PLWasteRegisterNum as Label_Calc_PLWasteRegisterNum,
T8.MiscCode,
T8.Calc_ChargeDesc,
T5.Calc_CompanyEORINumber as Label_Calc_CompanyEORINumber,
T1.Calc_CompanyEORINumber,
T1.Calc_CustShipToEORINumber,
T2.ServiceSource,
T5.RptLiteralsLServiceSource,
T12.BillingAmount,
T12.BillingRequired,
T12.Complete,
T12.MilestoneID,
T13.DocTotalCharges,
T11.PayerRef as BankAcct_PayerRef,
ISNULL(PM.TotalBilledAmount, 0) AS TotalBilledMilestones,
ISNULL(PM.CompletedInvoices, 0) AS TotalCompletedInvoices,
T11.TypeCode as BankAcct_TypeCode
FROM InvcHead_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN PreviousMilestones PM
ON T1.Company = PM.Company AND T1.OrderNum = PM.OrderNum
LEFT OUTER JOIN InvcDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.InvoiceNum = T2.InvoiceNum
LEFT OUTER JOIN FSCallDt_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.InvoiceLine = T3.Calc_InvoiceLine AND T2.InvoiceNum = T3.Calc_InvoiceNum
LEFT OUTER JOIN FSCallMt_" + Parameters!TableGuid.Value + " T4
ON T3.Company = T4.Company AND T3.CallNum = T4.CallNum AND T3.Calc_fCallLine = T4.Calc_fCallLine AND T3.Calc_InvoiceLine = T4.Calc_InvoiceLine AND T3.Calc_InvoiceNum = T4.Calc_InvoiceNum
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T5
ON T1.RptLanguageID = T5.RptLanguageID LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T6
ON T1.Company = T6.Company AND T1.CustNum = T6.CustNum AND T1.InvoiceNum = T6.Calc_InvoiceNum
LEFT OUTER JOIN FSContDt_" + Parameters!TableGuid.Value + " T7
ON T2.Company = T7.Company AND T2.InvoiceLine = T7.Calc_InvoiceLine AND T2.InvoiceNum = T7.Calc_InvoiceNum
LEFT OUTER JOIN InvcMisc_" + Parameters!TableGuid.Value + " T8
ON T1.Company = T8.Company AND T1.InvoiceNum = T8.InvoiceNum AND T2.InvoiceLine = T8.InvoiceLine
LEFT OUTER JOIN FSAExtData_" + Parameters!TableGuid.Value + " T10
ON T2.Company = T10.Company AND T2.SysRowID = T10.ForeignSysRowID
LEFT OUTER JOIN ( SELECT temp.Company, temp.InvoiceNum, temp.InvoiceLine, MIN(temp.SeqNum) as MinSeqNum
FROM InvcMisc_" + Parameters!TableGuid.Value + " temp
GROUP BY temp.Company, temp.InvoiceNum, temp.InvoiceLine) T9
ON T1.Company = T9.Company
AND T1.InvoiceNum = T9.InvoiceNum
AND T2.InvoiceLine = T9.InvoiceLine
LEFT OUTER JOIN (SELECT DISTINCT Company, BankAcctID, PayerRef, TypeCode FROM BankAcct_" + Parameters!TableGuid.Value + ") T11
ON T1.Company = T11.Company AND T1.OurBank = T11.bankacctid
LEFT OUTER JOIN ProjectMilestone_" + Parameters!TableGuid.Value + " T12
ON T1.Company = T12.Company AND T1.InvoiceNum = T12.InvoiceNum
LEFT OUTER JOIN OrderHed_" + Parameters!TableGuid.Value + " T13
ON T12.Company = T13.Company AND T12.OrderNum = T13.OrderNum
ORDER BY T1.InvoiceNum, T2.Calc_NextLegalNumID, T2.InvoiceLine, T8.SeqNum, T3.Calc_fCallLine, T4.Calc_MtCount
Any help would be appreciated.