Hey big Epicor team, Nico here with some issues on getting information to show properly on the body while aggregating information from the lines.
To set the context up a little bit, I’m working on customizing most transactional documents - And all of them are having this issue.
I’ll use Quote report as the example of this:
I’ve set up my groups using Calc_LineDesc into QuoteLine fields to show each item’s information, such as Size, Quantity, Price, UOM, the usual.
My issue starts when I try to make a Sum(Quantity) or a Count(Quantity) in the main body, outside of the groups. When I do, these values get multiplied depending on a few conditions:
If the quote line has quantity breaks, it’ll multiply the amount of that line by the amount of quantity breaks the line has. If the line is domestic, it’ll get the amount duplicated before being multiplied by the amount of quantity breaks, which I smell is because of taxability of the invoice - I couldn’t thoroughly test this one.
Example on how it’s duplicating
First item has two quantity breaks in the line details, while the second one has none, thus duplicating the first line to 40 and leaving the second one in 10, resulting in 50 total.
I’ve tried isolating the QuoteDtl table so that it wouldn’t be affected by other tables but this didn’t produce any results. Dropping the query below for reference:
=" WITH QuoteDtlForSum AS(
SELECT
PartNum_LongDesc_c,
SellingExpectedUM_UOMSymbol,
SellingExpectedUM,
Company,
DocDiscount,
DisplaySeq,
DrawNum,
KitParentLine,
KitFlag,
KitPricing,
KitPrintCompsInv,
KitShipComplete,
LeadTime,
PartNum,
QuoteLine,
QuoteNum,
RevisionNum,
XPartNum,
XRevisionNum,
Calc_LineDesc,
Calc_HasMisc,
Calc_Duration,
Calc_Modifier,
Calc_Mate,
Calc_Labor,
Calc_Misc,
ContractNum,
RenewalNbr,
SellingExpectedQty,
DocExpUnitPrice,
DocExtPriceDtl,
ExpUnitPrice,
OrderQty,
PartNum_PartLength,
PartNum_Thickness,
PartNum_PartWidth,
PartNum_IUM
FROM QuoteDtl_" + Parameters!TableGuid.Value + ")
SELECT
T2.PartNum_LongDesc_c,
T1.UseOTS,
T1.OTSAddress1,
T1.OTSAddress2,
T1.OTSCity,
T1.OTSName,
T1.OTSCountryNum_Description,
T1.Calc_TaxMethod,
T1.DocQuoteAmt,
T1.DocTotalPotential,
T1.DocTotalGrossValue,
T1.Calc_LineMiscTotal,
T1.Calc_HeadMiscTotal,
T1.Calc_TotalTaxAmt,
T1.DateQuoted,
T1.ExpirationDate,
T1.QuoteComment,
T1.QuoteNum,
T1.Reference,
T1.Calc_CompanyAddr,
T1.Calc_CompFax,
T1.Calc_CompPhone,
T1.Calc_CurSymbol,
T1.Calc_CustContact,
T1.Calc_CustContactEMail,
T1.Calc_CustFax,
T1.Calc_CustPartOpts,
T1.Calc_CustPhone,
T1.Calc_EMail,
T1.Calc_Message1,
T1.Calc_Message2,
T1.Calc_QuoteAddr,
T1.Currency_CurrencyID,
T1.Customer_Name,
T1.Calc_HasHeadMisc,
T2.SellingExpectedUM_UOMSymbol,
T2.SellingExpectedUM,
T2.Company,
T1.DiscountPercent,
T2.DocDiscount,
T2.DisplaySeq,
T2.DrawNum,
T2.KitParentLine,
T2.KitFlag,
T2.KitPricing,
T2.KitPrintCompsInv,
T2.KitShipComplete,
T2.LeadTime,
T2.PartNum,
T2.QuoteLine,
T2.QuoteNum,
T2.RevisionNum,
T2.XPartNum,
T2.XRevisionNum,
T2.Calc_LineDesc,
T2.Calc_HasMisc,
T2.Calc_Duration,
T2.Calc_Modifier,
T2.Calc_Mate,
T2.Calc_Labor,
T2.Calc_Misc,
T2.ContractNum,
T2.RenewalNbr,
T2.SellingExpectedQty,
T2.DocExpUnitPrice,
T2.DocExtPriceDtl,
T3.DocUnitPrice,
T3.PricePerCode,
T3.QtyNum,
T3.SalesUM,
T3.SellingQuantity,
T3.UnitPrice,
T2.ExpUnitPrice,
T3.Calc_NetPrice,
T3.Calc_UMDescription,
T1.ProjectID_c,
T2.OrderQty,
T2.PartNum_PartLength,
T2.PartNum_Thickness,
T2.PartNum_PartWidth,
T1.Customer_Address1,
T1.Customer_Address2,
T1.Customer_City,
T1.Customer_Country,
T1.Terms_Description,
T1.PONum,
T1.Customer_PMUID,
T1.CustNum,
T4.CustNum,
T4.PMUID,
T4.PayMethod_Name,
T2.PartNum_IUM,
T5.Name AS SalesRepName,
T6.Calc_ChargeDesc,
T6.DocMiscAmt,
T6.SeqNum,
T1.Customer_LangNameID,
T3.SellingQuantity
FROM QuoteHed_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN QuoteDtlForSum T2
ON T1.Company = T2.Company AND T1.QuoteNum = T2.QuoteNum
LEFT OUTER JOIN QuoteQty_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.QuoteNum = T3.QuoteNum AND T2.QuoteLine = T3.QuoteLine
LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T4
ON T1.Company = T4.Company AND T1.CustNum = T4.CustNum
LEFT OUTER JOIN QSalesRP_" + Parameters!TableGuid.Value + " T5
ON T1.Company = T5.Company AND T1.QuoteNum = T5.QuoteNum
LEFT OUTER JOIN QuoteLnMsc_" + Parameters!TableGuid.Value + " T6
ON T1.Company = T6.Company AND T1.QuoteNum = T6.QuoteNum
"
It’s also pretty strange to me that individual values in the line are printing correctly (20, 10) but they get transformed when aggregating them in the main body.
Has anybody had this experience before? How were you able to aggregate line details into the main body?
Thanks, have an amazing rest of your day!