BO to get pricing based on future date within configurator?

Epicor ERP 10
, ,
1

Price Inquiry BO does not work for this as it has no date parameter to be passed. Is there another BO I can use to get a price passing in Date, Qty, UOM, PartNum, CustID and/or a ShipToID? Our Customers have multiple pricelist groups attached based on effective dates. Any direction on the correct BO to use would be greatly appreciated.

2

Can you just use a LINQ expression to query the DB directly?

1 Like
3

Sounds like a good a plan as I’ve had. Working on the Query needed now. Thank you for the confirmation :slight_smile:

4

I took a peek at the tables you’ll need, and be forewarned that it looks a bit tricky. At least 3 tables are probably needed. And choosing which QtyBreak price (as they are in columns not rows) will take some thought.

I’d tackle it by trying to make a BAQ that returns the one record you want, and then implementing that as LINQ code.

5

Ya working it in SQL first I was thinking something like this as we don’t use price breaks for configured product and the discounts can be factored in afterwards:

SELECT TOP (1) *  FROM (
SELECT C2.Company AS Company
	, C2.CustNum as CustNum
	, C2.CustID as CustID
	, CPL.ListCode as [ListCode]
	, PL2.[StartDate] AS [StartDate]
	, PL2.[EndDate] AS [EndDate] 
FROM [Erp].[CustomerPriceLst] CPL
	LEFT JOIN [Erp].[Customer] C2 ON  CPL.Company=C2.Company AND CPL.CustNum=C2.CustNum
	LEFT JOIN [Erp].[PriceLst] PL2 ON PL2.Company=CPL.Company AND PL2.ListCode=CPL.ListCode
UNION ALL
SELECT C1.Company AS Company
	, C1.CustNum as CustNum
	, C1.CustID as CustID
	, CPG.[ListCode] as [ListCode]
	, PL1.[StartDate] AS [StartDate]
	, PL1.[EndDate] AS [EndDate]
	FROM [Erp].[CustGrupPriceLst] CPG
	LEFT JOIN [Erp].[Customer] C1 ON CPG.Company=C1.Company AND CPG.GroupCode=C1.GroupCode
	LEFT JOIN [Erp].[PriceLst] PL1 ON PL1.Company=CPG.Company AND PL1.ListCode=CPG.ListCode
	) AS UA	
WHERE UA.Company='XYZ' AND UA.CustID='MyCustID' AND ((UA.StartDate<=@PricingDate) AND (UA.[EndDate]>=@PricingDate))
6

@ckrusen Also, not using ShipTo ID’s at this time so this simpler version gets me the list at a given date. :smiley:

Do you see any room for improvements as I am still learning the LINQ queries?
Here is my simplified version of Priice List Lookup in LINQ (Developed in LINQpad5):

List<string> ListCodes = (((from cust in Customers
							where cust.CustID == CustID && cust.Company == Company
							join cpl in CustomerPriceLsts on cust.CustNum equals cpl.CustNum into cpls
							from cpl in cpls
							join pls in PriceLsts on cpl.ListCode equals pls.ListCode
							where pls.StartDate <= PricingDate && pls.EndDate >= PricingDate
							select new
							{
								rListCode = cpl.ListCode,
							}).ToList()).Concat(
			(from cust in Customers
			 where cust.CustID == CustID && cust.Company == Company
			 join cpg in CustGrupPriceLsts on cust.GroupCode equals cpg.GroupCode into cpgs
			 from cpl in cpgs
			 join pls in PriceLsts on cpl.ListCode equals pls.ListCode
			 where pls.StartDate <= PricingDate && pls.EndDate >= PricingDate
			 select new
			 {
				 rListCode = cpl.ListCode,
			 }).ToList()).Select(r => r.rListCode)).ToList();

var partRecords = (from pl in PriceLstParts
				  where pl.PartNum==PartNum && ListCodes.Contains(pl.ListCode)
				  select new { Price = pl.BasePrice, ListCode=pl.ListCode, PartNum = pl.PartNum }).ToList().OrderBy(pr=>ListCodes.IndexOf(pr.ListCode)).ToList();

decimal dPrice = partRecords.Select(r => r.Price).ToList().DefaultIfEmpty(0m).FirstOrDefault();
string sListCode = partRecords.Select(r => r.ListCode).ToList().DefaultIfEmpty("").FirstOrDefault();

ListCodes.Dump();
partRecords.Dump();
Console.WriteLine("Date: " + PricingDate.ToString() + "\nCustomer ID:" + CustID + "\nPartNum: " + PartNum + "\nListCode: " + sListCode + "\nPrice: " + dPrice.ToString());```
1 Like