Within Opportunity Quote Entry, we have BOMs built using a configurator and then we use the “Get Details” feature to get prices for the materials within the BOM in Quote Entry. Get Details grabs the price from the supplier price list with the current prices of the time period at the time of quoting. Is there a way for us to pull future prices instead of current prices of materials from Supplier Price List into a BOM? This will allow us to quote BOMs with prices that’ll be active a few months down the road which will align with the production schedule which is also a few months after the product is quoted.
Can this be done out of the box? Or will I need to create a Post-Processing BPM that will manually add prices?
I would try playing around with the effective dates of the revision. If you are on revision A, copy it and create B but put the effective date in the future. Then get details in the quote with B.
Here is the code without price break leveraging. This is single price UD Method I planned on moving into a function later to provide ‘DATED’ part pricing lookup via a server function as the Epicor Price Lookup BO does not… Updated code also allows Price List override if needed.
Maybe this will give you some ideas (sorry for the sloppy mixed linq queries, I prefer query style, but had not updated all to method type yet for consistency as some linq method queries were needed)
// Enter valid C# code and do not forget this method returns a decimal value.
string serverName = "server";
string fileName = "PriceLookup";
string dbName = "TEST";
decimal returnPrice = 0;
string msg = String.Empty;
string ShipToID = String.Empty;
string ListCode = String.Empty;
int partPriceRecordsCount =0;
string LookupPartNum=String.Empty;
// NEW : Use add override price list if present!
string sOverrideListCode = "";
bool bOverridePL = false;
string sStartWithChar = (Context.CompanyID == "CO" ? "C" : "K");
string sUOM = (PartNum.StartsWith(sStartWithChar, true, null) ? "EAC" : "EA");
try
{
switch(Context.Entity)
{
case "OrderDtl":
ShipToID=(string)(Db.OrderHed
.Where(oh => oh.Company == Context.CompanyID && oh.OrderNum == Context.OrderNumber)
.Select(oh => oh.ShipToNum).FirstOrDefault() ?? "");
bOverridePL = (bool)(Db.OrderDtl
.Where(r => r.Company == Context.CompanyID && r.OrderNum == Context.OrderNumber && r.OrderLine == Context.OrderLineNumber)
.Select(r => r.OverridePriceList).FirstOrDefault());
sOverrideListCode = (string)(Db.OrderDtl
.Where(r => r.Company == Context.CompanyID && r.OrderNum == Context.OrderNumber && r.OrderLine == Context.OrderLineNumber)
.Select(r => r.BreakListCode).FirstOrDefault());
break;
case "QuoteDtl":
ShipToID=(string)(Db.QuoteHed
.Where(qh => qh.Company == Context.CompanyID && qh.QuoteNum == Context.QuoteNumber)
.Select(qh => qh.ShipToNum).FirstOrDefault() ?? "");
bOverridePL = (bool)(Db.QuoteDtl
.Where(r => r.Company == Context.CompanyID && r.QuoteNum == Context.QuoteNumber && r.QuoteLine == Context.QuoteLineNumber)
.Select(r => r.OverridePriceList).FirstOrDefault());
sOverrideListCode = (string)(Db.QuoteDtl
.Where(r => r.Company == Context.CompanyID && r.QuoteNum == Context.QuoteNumber && r.QuoteLine == Context.QuoteLineNumber)
.Select(r => r.BreakListCode).FirstOrDefault());
break;
};
// NEW : Find appropriate ListCodes for pricing based on date!
List<string> ListCodes =
(((from cust in Db.Customer
where cust.CustID == Context.CustomerID && cust.Company == Context.CompanyID
join cpl in Db.CustomerPriceLst on new {cust.CustNum, STID=ShipToID} equals new {cpl.CustNum, STID=cpl.ShipToNum} into cpls
from cpl in cpls
where cpl.Company == Context.CompanyID
join pls in Db.PriceLst on cpl.ListCode equals pls.ListCode
where (pls.StartDate <= PricingDate && pls.EndDate >= PricingDate) || (pls.StartDate <= PricingDate && pls.EndDate == null)
select new
{
ListCode =(string)cpl.ListCode,Seq=(int)(cpl.ListCode==sOverrideListCode&&bOverridePL?0:cpl.SeqNum)
}))
.Concat(
(from cust in Db.Customer
where cust.CustID == Context.CustomerID && cust.Company == Context.CompanyID
join cpg in Db.CustGrupPriceLst on cust.GroupCode equals cpg.GroupCode into cpgs
from cpl in cpgs
where cpl.Company == Context.CompanyID
join pls in Db.PriceLst on cpl.ListCode equals pls.ListCode
where (pls.StartDate <= PricingDate && pls.EndDate >= PricingDate) || (pls.StartDate <= PricingDate && pls.EndDate == null)
select new
{
ListCode =(string)cpl.ListCode,Seq=(int)(cpl.ListCode==sOverrideListCode&&bOverridePL?0:100+cpl.SeqNum)
}))).GroupBy(r => r.ListCode).Select(r => r.FirstOrDefault()).OrderBy(r => r.Seq).Select(r=>r.ListCode).ToList();
var partPriceRecords = ListCodes
.Join(
Db.PriceLstParts
.Where(pl=>pl.PartNum == PartNum && ListCodes.Contains(pl.ListCode))
.Select(pl => new { Price = pl.BasePrice, ListCode = pl.ListCode, PartNum = pl.PartNum })
, lc => lc.ToUpper()
, pl => pl.ListCode.ToUpper()
, (lc, pl) => new { Price = pl.Price, PartNum = pl.PartNum, ListCode = pl.ListCode }
).ToList();
//.OrderBy(r => r.Seq);
decimal dPrice = 0m;
partPriceRecordsCount=partPriceRecords.Count();
if ( partPriceRecordsCount > 0)
{
dPrice = partPriceRecords.Select(r => r.Price).ToList().DefaultIfEmpty(0m).FirstOrDefault();
}
else
{
// This section allows lookups against several base records either a 'O' Option or a 'T' Trailer and table to find a fallback (original) default price value
switch (LookupType)
{
case "O":
dPrice =
(from line in Db.UD40
where
line.Company == Context.CompanyID &&
line.Key1 == TrailerNum &&
line.Key2 == ModelYear &&
line.Key4 == PartNum
select line.Number01).ToList().DefaultIfEmpty(0m).FirstOrDefault();
break;
case "T":
decimal? tmpPrice = (
(from line in Db.PcConData
where
line.Company == Context.CompanyID &&
line.Key5 == ("20"+ModelYear) &&
line.Drawing_c == TrailerNum
select line.Price_c).ToList().DefaultIfEmpty(0m).FirstOrDefault());
dPrice = tmpPrice??0m;
break;
}
}
ListCode = partPriceRecords.Select(r => r.ListCode).ToList().DefaultIfEmpty("Not found").FirstOrDefault();
msg = "\nPrice List: " + ListCode + " used for customer: " + Context.CustomerID + ((ShipToID!="")?"; ShipTo: " + ShipToID:"") + " for PartNum: " + PartNum + " giving price of: $" + dPrice.ToString() +" "+sUOM+ " for date: " + PricingDate +"\nsLC:"+ListCode+" | fOP:"+bOverridePL.ToString()+" | pPR_C:"+partPriceRecordsCount.ToString();
if(DebugLogToDisk) DiskLogMessage(msg, fileName, serverName, dbName); // Trigger off debug flag.
returnPrice = dPrice;
}
catch (Exception ex)
{
DiskLogMessage("Error in PriceLookup; Check if part: '" + PartNum + "' UOM: '" + sUOM + "' is valid.\n"+ex.Message, fileName, serverName, dbName);
throw new Ice.BLException("Error in PriceLookup\n\n Check if part: '" + PartNum + "' UOM: '" + sUOM + "' is valid.\n\n"+ex.Message
+"\n\nsLC:"+ListCode+" | fOP:"+bOverridePL.ToString()+" | pPR_C:"+partPriceRecordsCount.ToString()
);
}
return returnPrice;
Mind you this is a configurator @ Server UD Method based on our company setup & tables.