Looking to Update from 10.1.400 to 10.2.300. So I ran the ERP Analyzer on our Production DB, and it lists two issues under the Health Checks section.
Issue #1 ID = Update PartCOPart where ium <> part.ium or partdescription = part.partdescription set ium to part.ium and partdescription to part.partdescriptiona. Table: PartCOPart Description: Search PartCOPart based on Part using primary key where company is Part.company and partnum is Part.partnum. Filter by ium <> part.ium or partdescription <> part.partdescription. Update PartCOPart and set ium to part.ium and partdescription
Issue #2 is: ID: Delete partdtl if not found on tforddtl where tfordline <> 0 Table: PartDtl Description: Search partdtl based on Company using primary key where company is Company.company. Filter by tfordline <> 0. Search tforddtl for partdtl.company, partdtl.tfordnum, partdtl.tfordline and tforddtl.openline = true. If not found then partdtl.
I’ve created BAQ’s for these: and found about 700 records need fixing on Issue #1, and about 20 for Issue #2
Issue #1 can be fixed with a uBAQ and updatable Dashboard
But #2 requires deleting records. How would I go about doing that from within the E10 App (i.e., not via SQL)?
PartDtl isn’t directly updatable, but you can get a Data Fix from support to delete orphaned records. We had an issue where a job was updated to have 0 production qty and then Closed, and this left orphaned PartDtl records in the database. I got a data fix that allowed me to search for and delete these records. I imagine it should be very similar for records orphaned from Transfer Orders.
PartDtl is built from all supply/demand records. I wonder if Refresh Part Quantities and Allocations would clean up PartDtl even though it doesn’t list that as one of the tables affected.
I re-ran a fix that I was previously sent by support (FX_DELClosedTFOrdDemand), and it reported 78 records - that were not the 20-some that my BAQ found. I went ahead and ran the DataFix. Then ran it again (in report mode) and it came up clean.
Then I ran the ERP analyzer gain it it is still balking at PartDtl table (same issue as the previous run).
I think there is a mistake in the recommended solution:
Search partdtl based on Company using primary key where company is Company.company. Filter by tfordline <> 0. Search tforddtl for partdtl.company, partdtl.tfordnum, partdtl.tfordline and tforddtl.openline = true. If not found then partdtl.
I highlighted what I think is incorrect. PartDtl.tfordline is not an integer, because TFO Order Nums have a string prefix (at least ours do).
To compensate for this, my BAQ has a the table criteria:
PartDtl.TFOrdNum <> ""
This finds the PartDtl records that specify any non-blank TFOrdNum, which I then do an outer join to TFOrdDtl. And I look for PartDtl Records with no matching TFOrdDtl
TFOrdLine is actually an integer. It’s the Line Number of the Transfer Detail. TFOrdNum and TFLineNum are both character strings since those contain prefixes (TFLineNum is unique across all transfer orders).
TFOrdLine = 0 means that the PartDtl record is not from a Transfer Order, so TFOrdLine <> 0 means the PartDtl is linked to a transfer order.
EDIT: I misread your changed criteria. Checking for TFOrdNum or TFLineNum <> ‘’ should also work just the same way as their suggested criteria.
Thanks for pointing that out. I see I had an error in my BAQ.
Maybe my BAQ is still not right.
Here’s what ERP Analyzer says needs to be done:
Search partdtl based on Company using primary key where company is Company.company. Filter by tfordline <> 0 . Search tforddtl for partdtl.company, partdtl.tfordnum, partdtl.tfordline and tforddtl.openline = true. If not found then partdtl.
And Here’s how I’m trying to find those records
SubQuery 1 - an Inner Sub Query used by the Top Level Query
That all looks good to me. The only thing I noticed when i recreated that query in my system was that in-transit transfer orders came up as well unless you added a filter to the PartDtl table where PartDtl.RequirementFlag = TRUE. The Transfer Order Line is marked closed when the Shipment ships out, but the PartDtl record showing that the receiving plant is expecting parts to come in still exists until the TFO Shipment is received.
Does your query give you any results after you ran the data fix you had?
AFter running the datafix, it seems to have dropped from 20–some results to 15.
And I too see in-transit TO’s in my query. I added the filed PartDtl.DueDate, and see about 5 of the 15 records have very old dates (10/29/2018 and earlier), while the other 10 are 6/2/2019 and later. I assume these 10 are in-transit.
The drop from 20-some to 15 may have been due to TO Receipts, and not the data fix.
And FTR - the DataFix’s info is:
DataFix: FX_DELClosedTFOrdDemand Title: e10.Delete partdtl where related TFOrdDtl record is closed Descript: Delete partdtl where related TFOrdDtl record is closed
So it might only address closed “closed” TO’s, which I assume requires receiving them to close them.
