I've been trying to find a way to write a query that can track the business processes/correlations to the same PO #. Here's an example:
- My Customer submitted PO#1234 to an FTP and Sterling picked it up (this gets trackingID 66254618)
- Sterling translates the file to an 850 and sends it to the server for the Vendor to pick up (still under trackingID 66254618)
- Vendor sends back the 855; Sterling processes this and translates it to our .txt format and processed in the customer system (trackingID 17768118)
- Vendor sends back the 810; Sterling processes this and translates it to our .txt format and processed in the customer system (trackingID 20906118)
I'm looking for details from correlation_set such as: Mailbox_MessageName, Mailbox_Path, PO#, Invoice# (all fields listed under the NAME field; I want the associated VALUE).
My initial thought was too complex to understand how to write a query for (for each "PONumber" under NAME, get the TRACKINGID under NAME and the associated OBJECT_ID for the TRACKINGID. Using the OBJECT_ID's, pull the NAME and VALUE's I want and group by PO# and sort by REC_TIME.
Anybody have a query they use like this? Just trying to build a report that makes it easier to search for the files processed by vendor/customer
------------------------------
Melissa Hunchuck
------------------------------