Global Sterling Data Exchange

 View Only

Sterling Database Query to track all steps for an order

  • 1.  Sterling Database Query to track all steps for an order

    Posted Wed March 20, 2024 03:41 PM

    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:

    1. My Customer submitted PO#1234 to an FTP and Sterling picked it up (this gets trackingID 66254618)
    2. Sterling translates the file to an 850 and sends it to the server for the Vendor to pick up (still under trackingID 66254618)
    3. Vendor sends back the 855; Sterling processes this and translates it to our .txt format and processed in the customer system (trackingID 17768118)
    4. 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
    ------------------------------