Custom ARForm - adding totals from previously billed invoices attached to milestones

Kinetic ERP
1

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.

2

This query did not account for the invoice I am printing, but I do have one that does remove it. Basically a non issue, because the main thing I need help with is getting the other milestone invoices amounts in with this one.

3

Seeing “one customer wants…” would make me consider writing report from scratch to supplement your regular ARForm. Mostly because the ARForm can be one of the hardest to customize.

2 Likes
4

While I can appreciate the suggestion, it does not help me resolve the issue and where I am currently. It seems that regardless of what the invoice num is, I should be able to see all of the milestones because of the Project ID on the Invoice Head. I can make that join with Project and ProjectMilestone tables. However when I try to bring that data into my report, I am not seeing all of the milestones, as I am in the BAQ i built to test this.

5

You might need to have the RDD fetch more data than you want, and then do some filtering/post-processing in the RDL.

One very important thing to remember (maybe you already know it - but always good to say, should someone else read this thread later) - is that the report (the RDL) references tables that have the same name as the actually DB tables, they’re not the same. The RDD creates temporary tables, and appends the GUID to the table name to prevent it from conflicting with other temporary tables, and so that the report knows which one to use.

Is the temp table PreviousMilestones_GUID created by the RDD populating as expected?

1 Like
6

I do not see the line in my sql query that says PreviousMilestones_GUID. I have a line that says PreviousMilestones PM. Is that what you are referring to?

If it is, then yes. It grabs the milestone associated to the current invoice. That is the issue I am running into, is that it wont give me the other milestone Billing Amounts.

But, yes the top part of my sql query is working.

7

I meant ProjectMilestone_GUID.

That is the one that is missing info from prior billings? To verify whats in there, either use SSMS (if you can), or create a sub-report (using just that table) and add it to your RDL.