Hi everyone,
I am working on a BAQ to compare open releases in Epicor to a customer data file in a UD table. There are two important elements to the query.
UD05 table containing a list of parts, dates, and quantities. These represent releases the customer wishes to have open. Key fields: Part number, PO number, Date, Qty.
A subquery consisting of OrderHed>OrderDtl>OrderRel. I placed criteria on each table to pull only open orders, lines, and releases. OrderHed also has criteria to filter only the orders for the specific customer number I need. Key fields: Part, PO, ReqDate, OurReqQty, OurRemainQty.
The goal of the BAQ is to show sales order releases in Epicor that do not match orders in the UD05 table.
The top level query has a subquery criteria: “UD05.Key1 Is Null”
All together this shows me open releases in our system that do not match releases the customer has requested. This works alright, but the more astute of you may notice there is a bit of a problem with this approach.
Problem
If our system has two (or more) releases for the same date and quantity, and the customer file only has one release for that data and quantity, this query does not show the ‘extra’ release in our system.
Approach
I need to find a way to allow the BAQ to match the first order in our system, and then any subsequent orders would not match because a match has already been found. Anything that doesn’t match should be listed in the final BAQ. Technically the dates and quantity will still match, but I need some other flag to say that the data/qty has already been accounted for.
I think the answer has something to do with aggregates and the min function, but I just can’t figure it out.
I am open to any ideas. Thank you for taking time to read this!
Nate
I have a question
Why your UD05 does not have a record for the ‘same release’ with same date and qty ? Key5 would have just a counter to make this row unique. And add fields to gather the right row to match (order number, line and release) ?
Thanks for the reply Pierre! If I understand your question correctly, why doesn’t UD05 perfectly match the releases in our Epicor tables?
UD05 is a data file exported from the customer’s website. The customer does not track which of their requests get linked to our internal order/line/release. The customer only cares that they have say, 4 open orders on 1/9/20 for 5 parts each. So The UD05 table does not contain any information about the related order, line, or release. I link the data based on the only things I can, PO, Part number, Date, and Qty.
I do have a key field for a unique ID number for every row in the UD table. I think that I need to use this somehow with an aggregate…
Could you not then edit UD05, when you create the order? with a BPM? and add the order/line/release missing info. If you accounter a duplicate (date/qty/PO ) you could create a new UD05 line which would represent your new Sales order release for that date/qty/PO duplicate.
OR
Use one of the UD tables which have child tables (UD100, UD100A) and use the child table to enter the orders created for your combination of (date/qty/PO ) from the parent table ?
I get a new data file to load into UD05 every Monday. So I start by deleting all the records in UD05, then uploading from a data file into UD05. So I can’t add the sales order info to the UD table. I might just be barking up the wrong tree.
Is this considered closed? I’m facing the exact same challenge right now and am curious if you were able to integrate with Epicor as you were intending.
I didn’t come up with a good solution. I created a dashboard that basically passes off the responsibility of identifying the duplicates to the user. So, if a release matches on quantity and date, then show all the matching UD rows, and let the user choose what to do. They get a flag to change the release date to match UD, or change the release quantity to match UD. If they don’t want to do either of these, they can close the unneeded release, and create a new release.
