Order Payment and Shipping Methods code fields in Data Integration

[nunoaguiar]

Is your feature request related to a problem? Please describe. We integrate orders using Data Integration Jobs. It's normal in our world that an Order Payment method and Order Shipping method are updated in the ERP. Use cases:

• A customer chose to pay with Credit Card, but then needs to change to Invoice
• A customer chose to ship with "Next day", but because of an issue with warehouse needs to be changed to "Ground"

For the Data Integration job to work, we need to change some field on the order (i.e. OrderShippingMethod) and then run a SQL script to find a matching EcomShipId to update the Order accordingly (getting the proper Id, label, ...)

Describe the solution you'd like In the EcomOrders table, have columns to store the PaymentTermsCode, PaymentCode, ShippingCode and all other ERP fields to do proper updates of data (instead of using the OrderShippingMethod and PaymentShippingMethod (or some other custom field) for it.

In other words:

• We would update EcomShippingMethodCode (instead of EcomShippingMethod)
• We would then run the script to update the EcomShippingId and EcomShippingName accordingly

Describe alternatives you've considered We are already updating the EcomShippingMethod and EcomPaymentMethod, but that feels "dirty" as we replacing a friendly name (EcomShippingMethod - which should be matching EcomShippings.ShippingName), with a value from the ERP (which could be "FDXGRD") to then try to match it EcomShippings on a column called ShippingCode (not the ShippingName)

Right now our solution works, but seems "creative". We would like to have a way that is much more obvious to update a record (order, ledger...) with an ID coming from an external system, and then matching it on ours to find the relevant data (DW Id, which then could have web-friendly labels, icons/images, ...)

Best Regards, Nuno Aguiar

[DynamicRas]

Hi Nuno,

I don't like having BC specific fields on EcomOrder table. What about making an update job which has conditionals for each payment and shipping type from BC and then updating the EcomShippingMethod or EcomPaymentMethod accordingly?

BR Rasmus Sanggaard

[nunoaguiar]

Hi Rasmus,

We're open to any other solution. I'm also not a great fan of having BC (external id fields really) fields directly there, so allow me to rephrase.

• We need to update records using Data Integration jobs, with data that comes from a 3rd party system
• The 3rd party system does not know the ID from DW
• The table we need to update the records may not have the external id field How to solve for it?

Examples where we have this working in an acceptable way

• Updating Users
  • Because the AccessUser table has both AccessUserId and AccessUserExternalId (or AccessUserCustomerNumber) we can set the primary key accordingly
• Updating User Addresses
  • Because we use the User Provider, although there's no AccessUserAddressUserExternalId column, we can update records accordingly

Examples where we have this would need to be improved

• Updating Shipping method of an Order
  • We need to update the Shipment Method ID or Shipping method Name to the external system value
  • Then we need to run a second job that searches for "unexpected data" in the chosen column and find a matching record in the EcomShippings tables
  • Alternatively we can use custom order fields, instead of updating/invalidating the Shipping Method Id and/or Shipping Method name columns

Another possible solution is to have the logic be built in the Order Provider.

Summary Considering that we not always control what values we get from 3rd party source data, we need to be able to match some values (external Ids) to Dynamicweb Ids. We sometimes do this with table scripts. We talked about having the ability to do custom scripts for each column mapping as well. This "request" is about exposing more situations we're coming across that currently require "creative" solutions.

Does this help?

[DynamicRas]

Hi,

I think I need a bit more context. The Code, Terms Code and Term are BC specific fields used in the Live Integration to send field.

Are you using OData as source? Why would you need the methods updated in DW? Why do 3. party or BC or whatever not know the ID if the order was created in DW?

Could you describe the flow and what you are trying to achieve then we can try to think of a way to solve it.

BR Rasmus Sanggaard

[nunoaguiar]

Hi Rasmus,

| Are you using OData as source? XML, but the problem is not the source, but the Destination (DW)

| Why would you need the methods updated in DW? This is common for us (B2B scenarios):

• When order is placed, "FedEx Next Day" is chosen
• Then for some reason, in the ERP it needs to be updated to "FedEx Ground" (because of hazardous materials or something)
  • In the ERP, "FedEx Ground" is actually "FDXGRD" (or some other string)
  • We can update the Shipping string, but then we should also invalidate or update the Shipping method ID (otherwise any template updates will be off/wrong if they rely on shipping icons and description or anything)

Does that clarify the request?

BBR Nuno Aguiar

[DynamicRas]

Hi Nuno,

Ok, know I think I understand the problem. A solution could be that the Order Provider makes a lookup when updating shipping and payment ID and updates the corresponding fields accordingly, as you also suggested, would that work for you?

BR Rasmus Sanggaard

[nunoaguiar]

Hi Rasmus,

That works just fine.

BR Nuno Aguiar

[DynamicRas]

Hi,

Cool. We will take a look at it. DevOps #20551.

BR Rasmus Sanggaard

[DWDBE]

Hi Nuno

could you provide some sample source data you are using for the import? And it would be nice to see what columns are you updating, so maybe you can share the sql scripts or source code you use to solve that issue

BR, Dmitrij

[nunoaguiar]

Hi Dmitrij,

I don't have a source sample data with me, but I can rephrase it in a more technical term that may help you.

In the EcomOrders table (in 9.17.5) we have the following columns

• OrderShippingMethod
• OrderShippingMethodId
• OrderShippingMethodDescription
• OrderShippingMethodCode
• OrderShippingMethodAgentCode
• OrderShippingMethodAgentServiceCode

Comparing with the UI

• We don't see the ShippingMethodId, but that's OK
• Greens are being saved to the database
• Reds do not exist as columns in the database

It's our understanding that the Code and Agent * fields are basically values coming from the ERP. In a way we use them as "external id" values.

That said, here's the issue we're running into

• The customer placed an order with SHIP1
  • OrderShippingMethodId = "SHIP1"
• DW then populated all of the columns accordingly
  • OrderShippingMethod = "FedEx Next Day"
  • OrderShippingMethodCode = "FDXND"
  • ...
• But then something happens and in the ERP the order needs to be updated to a different shipping method (this is typical in a B2B scenario)
  • The ERP has no knowledge of DW's Ids
  • We would get something like OrderShippingMethodCode = "FDXGRD"

And this is where the complexity/problems begin

1. We need table scripts to look up in the EcomShipping table what is the Shipping Id, Name, Description and all other relevant fields WHERE OrderShippingMethodCode = "FDXGRD"
2. The Order Provider does not list all columns mentioned above

What we're discussing is a better way to handle problem 1. above. I guess 2 is easy to solve :)

The same thing would happen with Payments methods (i.e. started with Credit Card, but needs to be converted to Invoice)

Does this help?

Nuno Aguiar

[DWDBE]

Hi Nuno, yes, thx for the help. How about the field: [OrderShippingProviderData] - that contains the order.ShippingProviderValues Does it needs to be cleared once the shipping provider is changed? Or should it be cleared only if the updated shipping provider type is not the same as was before? Or it is ok to not change that field value? BR, Dmitrij

[nunoaguiar]

Hi Dmitrij,

Good question. I'd say we'd want to clear it, because:

• The Shipping provider might even change to a new one (i.e. they cancelled the contract with FedEx, so it's all going out via UPS or own company trucks or something)
• The Payment Provider would typically change from Credit Card to Invoice - so similar information such as OrderGatewayResult should probably also be updated (to empty/null). That would allow us to do proper audit without running into some issues

Thoughts? Nuno