Calculate Sales Price - Part.UnitPrice

kylepbsps · July 7, 2020, 3:52pm

I’m experimenting and changing my plans as I go. So far my goal is to try and have Sales Pricing automatically updated with our Supplier Price List. (condition: default supplier, latest effective date, etc.)

I have create a UD field in Part table to store a percentage field which will assist in multiplying the VendPart.BaseUnitPrice * Part.SalePricePercentage_c

I’m having a problem using Method Directive to update this value. When i build the method directive on VendPart.Update it will map reverse of what I want it to. Basically would like to copy the data since i cannot retreive it from Part Form.

I probably did not explain this well but here are some screenshots.

josecgomez · July 7, 2020, 3:55pm

ok so (if I understand correctly)
On Vendor List Update (post processing)
Lookup the related Part Record then Update the Part.UnitPrice field accordingly.

kylepbsps · July 7, 2020, 3:56pm

Yes.

I’ve never done any lookups so I’m guessing thats where i struggle.

There is probably some C# where you can just lookup VendPart.BaseUnitPrice and multiply it but i’m not really sure since I need to have some criteria in there, due to multiple part records.

josecgomez · July 7, 2020, 4:00pm

Can you write code? are you familiar with the Epicor architecture? or the Epicor DB? what’s your skill-set I don’t want to throw a bunch of code at you if you’ll have no idea what to do with it I also don’t want to put you in a situation where you can’t maintain it.

You might be able to do this without writing code maybe with the BPM Widgets

kylepbsps · July 7, 2020, 4:06pm

I have some C# experience but not within Epicor. I’ll figure it out if you can give me a starting point.

Here is the scope of what was requested.
1 Populate Part.InternalUnitPrice (Internal Price) with correct value from Supplier Price List
2 Calculate percentage (from new UD field) markup onto Part.UnitPrice (Sales Unit Price)
3 Use override Checkbox (UD Field) to manually enter Sales Unit Price and ignore BPM.

#1 is the difficult part for me
#2 can be done with BPM from two fields in part table with Data Directive
#3 Data Directive also

EDIT: Fixed field labels to match epicor =)

josecgomez · July 7, 2020, 4:24pm

You can do a lookup in a BPM using LINQ for example

foreach(var ttVP in ttVendorPart)
{
//Gets the Part (table) Record
var partData = ( from p in Db.Part where p.Company ==ttVP.Company && p.PartNum == ttVP.PartNum select p).FirstOrDefault();
}

You should use the Business Object (BO) to make the updates though, and so for that you’ll need to use and instantiate the business object

Something like this

using(var partBO = Ice.Assemblies.ServiceRenderer.GetService<PartSvcContract>(this.Db))
{
      var pds = partBO.GetByID("YourPartNum");
      pds.Part[0].BaseUnitPrice = 12;
      .
      .
      .
      partBO.Update(ref pds);
}

Follow a trace to see what specific calls need to be made to update the Price and Such but that should get you close.

kylepbsps · July 7, 2020, 4:57pm

Thanks!

I’ll post my code once I get it sorted.

kylepbsps · July 7, 2020, 8:47pm

Got very close. I think something is wrong with the BO i picked.

foreach(var ttVP in ttVendPart)
{
//Gets the Part (table) Record
var partData = ( from p in Db.Part where p.Company == ttVP.Company && p.PartNum == ttVP.PartNum select p).FirstOrDefault();

using(var partBO = Ice.Assemblies.ServiceRenderer.GetService<PartSvcContract>(this.Db))

  {
      var pds = partBO.GetByID("12875");
      pds.Part[0].InternalUnitPrice = 12;
      
      partBO.Update(ref pds);
  } 

}

My trace log references these when changing Part.InternalUnitPrice

Erp.Proxy.BO.PartImpl
ChangePartInternalUnitPrice

Erp.Proxy.BO.PartImpl
CheckPartChanges

Erp.Proxy.BO.PartImpl
Update

I tried replacing the BO with Erp.Proxy.BO.PartImpl but i got an error about “non-generic type”

Also threw in random part number 12875 and random price of 12

josecgomez · July 7, 2020, 8:53pm

The BO I gave you is the correct one.

kylepbsps · July 7, 2020, 8:57pm

Since the part i’m testing with has multiple price records, how does it know which to pick from? Or am i missing something in the code?

I’m also guessing i can use the vendpart.partnum and vendpart.baseunitprice fields instead of the static entries of 12875 and 12.

josecgomez · July 7, 2020, 9:02pm

What do you mean multiple records?
There is only one Part record in Epicor per part number.

kylepbsps · July 7, 2020, 9:06pm

Supplier price list which is from VendPart table has multiple records. Not sure if that has anything to do with why its not updating on Part table. We’re copying field from VendPart to Part I believe.

josecgomez · July 7, 2020, 9:08pm

The BPM works on your “current” Record so that should be fine.

kylepbsps · July 7, 2020, 9:26pm

No errors by setting ttVP variables. But still no worky =( I have a popup message that confirms it executed.

foreach(var ttVP in ttVendPart)
{
//Gets the Part (table) Record
var partData = ( from p in Db.Part where p.Company == ttVP.Company && p.PartNum == ttVP.PartNum select p).FirstOrDefault();

using(var partBO = Ice.Assemblies.ServiceRenderer.GetService<PartSvcContract>(this.Db))

  {
      var pds = partBO.GetByID(ttVP.PartNum);
      pds.Part[0].InternalUnitPrice = ttVP.BaseUnitPrice;
      
      partBO.Update(ref pds);
  } 

}

kylepbsps · July 8, 2020, 4:56pm

I need another push. I cannot get this to write the value to part.InternalUnitPrice

Is this the correct method directive?

josecgomez · July 8, 2020, 4:57pm

Is your BPM Firing? (How do you know)

kylepbsps · July 8, 2020, 5:05pm

Yeah. BPM name is “Cheese” =)

I can see the value change in the trace log for VendPart.BaseUnitPrice. I don’t see anything about Part.InternalUnitCost

  <changedValue tableName="VendPart" rowState="Modified" rowNum="0" colName="BaseUnitPrice"><![CDATA[69.00000]]></changedValue>
  <changedValue tableName="VendPart" rowState="Modified" rowNum="0" colName="UD_SysRevID"><![CDATA[System.Byte[]]]></changedValue>

    <BpmDirective Type="3" ID="ef87c9ce-607e-467b-b8fd-df0bd3e24602" Name="cheese" VisibilityScope="0" PreparationStepDuration="0" Duration="69" />[full.txt|attachment](upload://c6r2rPzYlminzPzIQhYuz86hQID.txt) (3.1 KB)

kylepbsps · July 8, 2020, 5:32pm

It’s not running a BO for updating Part table is my guess.

kylepbsps · July 8, 2020, 6:10pm

Found this knowledge base article and got it working but not quite the way I want to. It will update when the part form is saved (output area) but not when the supplier price list is changed (input area). I’ll try to work with that for a while.

Blockquote

When creating a new record, how can I create a BPM to copy information from one source to another?

Error

N/A

Resolution

This is easiest shown by example using the Update Table by Query widget from the BPM Designer.

For example purposes, we will assume that the Order Comments on a Sales Order should default to the Comments against the Customer record. We will also assume that you can change the default value and your changes should not be overridden.

Steps to Create BPM:

Open the menu item Method Directives Maintenance
Search for the SalesOrder business object, and the MasterUpdate method
Select New then New Pre-Processing
Give it an appropriate Name, e.g., DefaultCustomerComment
Click the Design button to open the designer
Add an Update Table by Query widget and join it to the Start widget
Select the Update Table by Query widget and click on the Specified query link
Give the Query an appropriate Name, e.g., CustomerComment
Add the ttOrderHed table then select it. Add a Table Criteria of RowMod = “A”
This will filter for only added (new) Orders.
Add the Customer table and join it to the ttOrderHed table. Select the Join and add the following Table Relations: Company to Company, CustNum to CustNum
Go to the Display Fields tab and add the following fields: ttOrderHed.Company, ttOrderHed.OrderNum, ttOrderHed.RowMod, Customer.Comment
Click OK to save the BPM Query
Change the focus from all rows to added rows
Click the Specified table link. Choose the ttOrderHed table
Click the configured mapping link.
In the relations section, add the following links: Company to ttOrderHed_Company, OrderNum to ttOrderHed_OrderNum, RowMod to ttOrderHed_RowMod
In the Columns section, search for OrderComment and bind this to field: Customer_Comment. Repeat for the other Comment types if you want.
Click OK to close the table mapping screen.
Click Validate then Save then close the Designer
Tick Enabled and save your BPM.

Test your BPM:

Open Customer Maintenance and search for any Customer
Go to the Customer tab, then select the Comments tab. Put a sample comment in here and press save.
Open Sales Order Entry and press New.
Search for the Customer adjusted above and then save the order.
Go to the Header tab, then select the Comments tab. The Sales Order comment should have defaulted to the one against the Customer.
Change the comment to something else and press save. You will note your changes are saved.

Blockquote

https://epicorcs.service-now.com/epiccare/?id=kb_article&sys_id=4f8356fbdb209b84a5db76731f961999

kylepbsps · July 9, 2020, 2:37pm

This is functional.