Useful query for checking sourcing status table

[avillanueva]

I had an issue I opened with tech support. The overall sourcing status was showing No AML but I did in fact have AML/AVL data for a particular part. It turns out the OEMPARTAXLCONTEXTLINK table had no entry. Still investigating as to how this occurred but this query showed the extent of the issue. I will close this thread with a summary of how we think it was caused.

SELECT m.wtpartnumber, A.IDA2A2, a.amlpreferencedata, a.avlpreferencedata, o.oempreferencedata
FROM WTPART P, WTPARTMASTER M, oempartaxlcontextlink O, AXLEntry A
WHERE
p.ida3masterreference = M.IDA2A2 AND
o.ida3a5 (+) = p.ida2a2 AND
a.IDA3C4 = p.ida2a2 AND
p.latestiterationinfo = 1

Where you have null entries for the OEMPREFERENCEDATA is where you have problems.

[Marco_Tosin]

Hi Antonio,

have you already tried this report?

[QML] Report listing all Supplier Parts, OEM Parts and their sourcing status

[avillanueva]

No but I loaded it in. Nice but I would change a few things to make it more useful. The one thing I needed was that outer join for null conditions to check for issues. This has some odd constraints but I am sure I can turn my query into a qml easy enough.

[Marco_Tosin]

I don't know if you've ever used this technique, but even from the report query builder criteria you can set outer joins (see image).

It's an option I haven't used often, but a couple of times it has helped me.

[avillanueva]

Yep. old school guy here. Some times I like to roll my own query first.

[Marco_Tosin]

Sorry, I misinterpreted your question.
The link to the support article does not solve your problem

[avillanueva · Accepted solution]

Closing out this thread with resolution from TS "working as expected" (Grrr). To fix this, I created a DELETE import sheet to clear the AML/AVL. Once the was cleared, I was able to flip DELETE to ADD and reimport. That seemed to resolve the issue.