Customer Price Check

Epicor ERP 10
1

I am trying to create a BPM that will check the Customer Price Lists prior to posting an invoice. I have no problem with the placement of the BPM and I almost have the following code working, however the problem is we could have more than 1 active price list with the part number in, or the part number may not exist until the second price list.

I think I have the right order for linking \ checking the tables but the code fails if the part number isn’t in the first list. I think it works OK for finding the part if it is in the first list and not the second though I am still testing.

image
image.png1207×464 168 KB 

// Checks Price List Unit Price against Invoice Price if BASE currency

// Ice.Diagnostics.Log.WriteEntry("zBPM - ARInvoice.PrePrintInvoices");
// Ice.Diagnostics.Log.WriteEntry("zBPM - ARInvoice.PrePrintInvoices - ipGroupID: " + ipGroupID);

Erp.Tables.InvcHead InvcHead;
Erp.Tables.InvcDtl InvcDtl;
Erp.Tables.Customer Customer;
Erp.Tables.CustomerPriceLst CustomerPriceLst;
Erp.Tables.PriceLst PriceLst;
Erp.Tables.PriceLstParts PriceLstParts;

decimal unitPrice = decimal.Zero;
string infomsg = string.Empty;
string crlf = "\r\n";

foreach (var InvcHead_iterator in (from InvcHead_Row in Db.InvcHead
                                   where InvcHead_Row.Company == Session.CompanyID
                                   && InvcHead_Row.GroupID == ipGroupID
                                   select InvcHead_Row))
{
    InvcHead = InvcHead_iterator;
    if (InvcHead != null)
    {
        foreach (var InvcDtl_iterator in (from InvcDtl_Row in Db.InvcDtl
                                          where InvcDtl_Row.Company == InvcHead.Company
                                          && InvcDtl_Row.InvoiceNum == InvcHead.InvoiceNum
                                          select InvcDtl_Row))
        {
            InvcDtl = InvcDtl_iterator;
            Ice.Diagnostics.Log.WriteEntry("zBPM - ARInvoice.Update - InvcDtl.InvNum - InvLine: " + InvcDtl.InvoiceNum + " - " + InvcDtl.InvoiceLine);
            if (InvcDtl != null)
            {
                Customer = (from Customer_Row in Db.Customer
                            where Customer_Row.Company == InvcHead.Company
                            && Customer_Row.CustNum == InvcHead.CustNum
                            select Customer_Row).FirstOrDefault();
              Ice.Diagnostics.Log.WriteEntry("zBPM - ARInvoice.Update - Customer.CustNum: " + Customer.CustNum);
                if (Customer != null)
                {
                    CustomerPriceLst = (from CustomerPriceLst_Row in Db.CustomerPriceLst
                                        where CustomerPriceLst_Row.Company == Customer.Company
                                        && CustomerPriceLst_Row.CustNum == Customer.CustNum
                                        orderby CustomerPriceLst_Row.SeqNum ascending
                                        select CustomerPriceLst_Row).FirstOrDefault();
                  Ice.Diagnostics.Log.WriteEntry("zBPM - ARInvoice.Update - CustomerPriceLst.ListCode: " + CustomerPriceLst.ListCode);
                    if (CustomerPriceLst != null)
                    {
                        PriceLst = (from PriceLst_Row in Db.PriceLst
                                    where PriceLst_Row.Company == CustomerPriceLst.Company
                                    && PriceLst_Row.ListCode == CustomerPriceLst.ListCode
                                    && PriceLst_Row.EndDate == null
                                    orderby PriceLst_Row.StartDate descending
                                    select PriceLst_Row).FirstOrDefault();
                      Ice.Diagnostics.Log.WriteEntry("zBPM - ARInvoice.Update - PriceLst.ListCode: " + PriceLst.ListCode);
                        if (PriceLst != null)
                       {
                            PriceLstParts = (from PriceLstParts_Row in Db.PriceLstParts
                                             where PriceLstParts_Row.Company == PriceLst.Company
                                             && PriceLstParts_Row.ListCode == PriceLst.ListCode
                                             && PriceLstParts_Row.PartNum == InvcDtl.PartNum
                                             select PriceLstParts_Row).FirstOrDefault();
                          Ice.Diagnostics.Log.WriteEntry("zBPM - ARInvoice.Update - PriceLstParts.PartNum - PriceLstPrice - InvPrice: " + PriceLstParts.PartNum + " - " + PriceLstParts.BasePrice + " - " + InvcDtl.DocUnitPrice);
                            if (PriceLstParts != null)
                            // Ice.Diagnostics.Log.WriteEntry("zBPM - ARInvoice.Update - PriceLstPart Not Null");
                            {
                                {
                                    if (InvcDtl.DocUnitPrice != PriceLstParts.BasePrice)
                                    {
                                        infomsg = infomsg + "THE UNIT PRICE ON INVOICE NUMBER " + System.Convert.ToString(InvcHead.InvoiceNum) + " FOR PART NUMBER " + System.Convert.ToString(InvcDtl.PartNum) + " (INVOICE LINE " + InvcDtl.InvoiceLine + ") DOES NOT MATCH THE CURRENT PRICE LIST PRICE!" + crlf + crlf + "Please check before Posting.";
                                        Ice.Diagnostics.Log.WriteEntry("zBPM - ARInvoice.Update - infomsg: " + infomsg);
                                        Epicor.Customization.Bpm.InfoMessage.Publish(infomsg);
                                        infomsg = "";
                                }
                                    }

                            }
                        }
                    }
                }
            }
        }
    }
} ```
2

Seems like you need to make a loop around PriceLst

change the line

 if (PriceLst!= null){
    ...
    }

to

int i=0;  // will be used to keep track of which PriceList
while(PriceLst != null){
    ....  
// and then at the end of that block, 
//fetch the next `PriceLst` with something like
    i++;
    PriceLst = (from PriceLst_Row in Db.PriceLst
        where PriceLst_Row.Company == CustomerPriceLst.Company
          && PriceLst_Row.ListCode == CustomerPriceLst.ListCode
          && PriceLst_Row.EndDate == null
        orderby PriceLst_Row.StartDate descending
        select PriceLst_Row).Skip(i).FirstOrDefault();
    }

My thinking is that you need to cycle through all the pricelists, until no more exist for that customer

3

Checking price lists at Invoice Posting is rather late in the game. A better option will be check them at Order Entry, since all the functionality of Price Lists already exists in Order Entry. The order entry person just needs to be prompted for the correct price list.

That is my opinion. You use case may be different.

Vinay Kamboj

1 Like
4

I agree with @Vinaykamboj
However, there is a partially working business object that can do this. It’s the same one that the Price List Inquiry uses. This is the “simplest” way if you have a complex pricing structure.
The only accurate way is to use the price on the Order Entry at the time the order is placed though.

5

Thank you for your replies.

I agree @ckrusen that I need to cycle but I was thinking I want to stop when I hit the first result? I maybe overthinking it but I will definitely look at the code sample you have given.

@Vinaykamboj, @Jason_Woods - Unfortunately we mostly use Demand Entry to process our schedules as the PO numbers can exist for years and we can use Periodicity, alerts for dates, etc. We don’t often add new Sales Order lines which is where I believe the Price List functionality works?

I am currently in the process of moving all our customisations from V8 and we are getting pretty close to our implementation date now. I started with the invoices as if I don’t get chance to finish them all at least the error will get picked up (regardless of whether we used SO or DE) and I thought if I figured out how to do it with invoicing I could pretty much use the same code for Demand Entry. I will look into the business object mentioned.

Again, thank you all for your replies.

6

If you want it to stop searching after finding the first, change the while() expression to

while(PriceLst != null && i  != -1){

and set i to -1 when the entry is found. Like inside the block:

if (PriceLstParts != null){
    // your original code
    i = -1;
    }

While I’m not very familiar with LINQ, I’d bet a single query, using joins, could be made of :
Customer -> CustomerPriceList -> PriceList -> PriceListParts

7

That’s how we do it, both in BAQs and code.

A subquery returns MIN(SeqNum) for the combination of CustNum, ShipToNum and PartNum on a join of CustomerPriceLst, PriceLstParts and PriceLst with criteria on the StartDate and EndDate, and then you join that back to CustomerPriceLst and PriceLstParts on CustNum, ShipToNum, PartNum and MinSeqNum to SeqNum.

1 Like
8

Would the following be the syntax for a joined LINQ statement?

var Customer = (from Customer_Row in Db.Customer
	join CustomerPriceLst_Row in Db.CustomerPriceLst 
	  ON Customer_Row.company == CustomerPriceLst.company 
	    && CustomerPriceLst_Row.CustNum == Customer_Row.CustNum
	join PriceLst_Row in Db.PriceLst
	  ON PriceLst_Row.Company == CustomerPriceLst_Row.Company
		&& PriceLst_Row.ListCode == CustomerPriceLst_Row.ListCode
		&& PriceLst_Row.EndDate == null
	join PriceLstParts_Row in Db.PriceLstParts
	  ON PriceLstParts_Row.Company == PriceLst_Row.Company
	    && PriceLstParts_Row.ListCode == PriceLst_Row.ListCode
	    && PriceLstParts_Row.PartNum == InvcDtl_Row.PartNum
	where Customer_Row.Company == InvcHead_Row.Company
	    && Customer_Row.CustNum == InvcHead_Row.CustNum
	select Customer_Row).FirstOrDefault();

Such that only records would be found if they had the relationships:

Customer_Row.company == CustomerPriceLst.company  
&& CustomerPriceLst_Row.CustNum == Customer.CustNum
&& PriceLst_Row.Company == CustomerPriceLst.Company
&& PriceLst_Row.ListCode == CustomerPriceLst.ListCode
&& PriceLst_Row.EndDate == null
&& PriceLstParts_Row.Company == PriceLst.Company
&& PriceLstParts_Row.ListCode == PriceLst.ListCode
&& PriceLstParts_Row.PartNum == InvcDtl.PartNum
9

It looks like one approach at least, but I find it’s very difficult to be good at LINQ if you’ve already worked on understanding SQL, so I never trust a LINQ expression until I’ve put it into LINQPad! I’m not going to commit myself on a specific LINQ expression.

Our Price List use is quite complex, so we have to be more specific about StartDate and EndDate, that’s the main difference apart from how we group things, though. Once we found that the minimum SeqNum was what we needed, the rest was fairly straightforward.

1 Like
10

I notice no one is using Group pricing or Discounts.

11

We used to and stopped.

12

Thank you for all your comments. We don’t currently using group pricing or discounts as they are not relevant to our pricing system, although we may possibly be using them against certain customers in the future.

I was looking at the code again yesterday and I feel the logic is correct to count the PriceLst table but that the loop to the next PriceLst should be against a null record at PriceLstParts. I’m not sure my coding skills are up to this just yet! I have also been through a list of all customisations, etc with my Manager which are outstanding for our go-live in a few weeks and need to prioritise some others over this at the moment. I’ll let you know if \ when I get it sorted.

13

Any particular reason? Just curious.

14

I could write a novel on why … but it boils down to discounts being at the heart of a wild-west approach to pricing throughout the company, and they needed to be eliminated to have a hope of bringing control to it. Technically there’s no reason why discounts from a single price should be any different to multiple prices per product, but in our case, in human terms, it’s simplified things enormously going from the first to the second.

Our pricing system is still complex, but it’s now scaleable and hasn’t put limits on our growth. Other companies will have different needs and circumstances, naturally, so I’m not advocating everybody does the same.