Open Forum

Expand all | Collapse all

duplicate tracking numbers against multiple invoices (no visibility)

  • 1.  duplicate tracking numbers against multiple invoices (no visibility)

    Posted 6 days ago
    Hey all,

    We use StarShip here in our shipping department and it works quite well. However, as it applies tracking numbers to SOP10107 when the order is still an order prior to invoicing we end up having tracking numbers on both the order as well as the invoice. Now, normally this is not a problem if the order ships complete, however when we ship a partial against an order we lose all visibility on which tracking number is associated with which invoice as the original tracking number is transferred along with the new tracking number to the newest invoice from the original sales order.

    Consider this:

    SOPNUMBE SOPTYPE Tracking_Number DEX_ROW_ID
    TESTSO                2 trk11                                     26336
    INV-0007906           3 trk11                                     26337
    TESTSO                2 trk12                                     26338
    INV-0007907           3 trk11                                     26339
    INV-0007907           3 trk12                                     26340
    TESTSO                2 trk13                                     26341
    INV-0007908           3 trk11                                     26342
    INV-0007908           3 trk12                                     26343
    INV-0007908           3 trk13                                     26344


    So what we have here is one SO and three invoices against the SO. The first invoice (logically the lowest numbered invoice) would have only one tracking number (the first tracking number applied to the SO). However every subsequent tracking number applied to the SO would be applied to each new invoice so that we end up with a SOP10107 table resembling the example above.

    I am imagining a trigger such that when the so is transferred to an an invoice and after the tracking number is written to the invoice within SOP10107 that would then delete the original tracking number where the soptype = 2 and the sopnumbe is the orignumb of the new invoice. I'm just not fully wrapping my head around the steps to do this. Anyone have any suggestions?

    ------------------------------
    Danny Maxwell
    Business Analyst
    AFL
    Kent Washington
    ------------------------------


  • 2.  RE: duplicate tracking numbers against multiple invoices (no visibility)

    TOP CONTRIBUTOR
    Posted 6 days ago
    The SOP files for SOP Invoices note what the original SOP Order number was, so you should be able to link these with a SQL query to get the info you need.

    ------------------------------
    Bruce Strom
    Programmer Analyst
    Associated Grocers of Florida / Supervalu
    Sunrise FL
    ------------------------------



  • 3.  RE: duplicate tracking numbers against multiple invoices (no visibility)

    SILVER CONTRIBUTOR
    Posted 3 days ago
    Danny,

    Here's what I have experienced...

    Starship will only append the tracking numbers to the sales order because that's the only document number it gets from the user (manual entry or barcode scan).  Typically, you don't have an invoice document in GP at this point (shipping).  I doubt someone would write the additional logic in Starship (or a trigger executed during shipping) to update the invoice.

    If you are seeing tracking numbers on your invoices it's either (a) you are shipping from your invoices or (b) you have some code (possibly a trigger) that is copying the tracking numbers from the order to the invoice during the transfer (after shipping).

    I don't see a reason to do any copying of tracking numbers.   GP keeps the linkage of which invoices came from which orders.  The users can drill into the documents to see the linkage.  I would prefer to keep the tracking numbers with the document that actually shipped.

    If the users (A/R?) must see the tracking numbers from the invoice, I would build them a Smartlist or SSRS report to do the linkage and capture the tracking numbers.

    I hope this helps.

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------



  • 4.  RE: duplicate tracking numbers against multiple invoices (no visibility)

    Posted 3 days ago
    Thanks Chris,

    What I have found is that during the transfer of a sales order to an invoice, that all of our information under the "user defined" window including tracking numbers flows to the newly created invoice. If this is not native to vanilla GP then I will start looking for any code that references SOP10107 to see if I can find what mechanism is "transferring" the tracking numbers to the invoices.

    I do know that GP keeps the linkages of SOs to invoices by way of ORIGNUMB and other key fields however the way we do our reporting of shipments/tracking numbers is by concatenating all tracking numbers for an individual invoice to one row. Thus the above example can be problematic if older tracking numbers are transferred to the newest invoices.

    Danny

    ------------------------------
    Danny Maxwell
    Business Analyst
    AFL
    Kent Washington
    ------------------------------



  • 5.  RE: duplicate tracking numbers against multiple invoices (no visibility)

    SILVER CONTRIBUTOR
    Posted 3 days ago
    Danny,

    I stand corrected.  My apologies.  The user-defined fields do copy during the transfer.

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------



  • 6.  RE: duplicate tracking numbers against multiple invoices (no visibility)

    Posted 3 days ago
    No problem at all! I just want to make sure what I'm seeing is common to other GP instances. Hopefully Someone can provide an answer because I'm sure there have been other cases here where this has been an issue.

    ------------------------------
    Danny Maxwell
    Business Analyst
    AFL
    Kent Washington
    ------------------------------



  • 7.  RE: duplicate tracking numbers against multiple invoices (no visibility)

    SILVER CONTRIBUTOR
    Posted 3 days ago
    Yes.  Hopefully someone else has a good solution for this dilemma.  It would have been nice if that table had a date/time stamp.

    I would still try to write a Smartlist or a report for the users.  You'll need logic to interrogate all of the linked documents to determine which tracking number goes with which originating document.  Good times!

    Chris

    ------------------------------
    Chris Donnelly
    Sr Mgr of Info Systems and Financial Reporting
    Healthmark Industries
    ------------------------------



  • 8.  RE: duplicate tracking numbers against multiple invoices (no visibility)

    Posted 3 days ago
    Definitely, I've already written a query to pull the associated tracking numbers, but that's how I found out about this issue anyway.

    ------------------------------
    Danny Maxwell
    Business Analyst
    AFL
    Kent Washington
    ------------------------------



  • 9.  RE: duplicate tracking numbers against multiple invoices (no visibility)

    TOP CONTRIBUTOR
    Posted 3 days ago
    Danny-

    We use StarShip's younger sibling ShipGear and were running into the same issue with the tracking numbers.

    Our fix was letting the warehouse guys transfer the batch of orders ready to ship to invoices first. When keying in the document number to have the data pulled from GP to the carrier's systems, they are keying in the invoice number and it is pulling everything from the just created invoice -- since that is what is actually shipping that day.

    The tracking number getting written back is only for the contents of that particular invoice shipping to the customer.

    I hope this helps.

    Thanks.
    Joe

    ------------------------------
    Joseph C. Markovich
    IT Manager
    Okaya Electric America
    Valparaiso IN
    ------------------------------



  • 10.  RE: duplicate tracking numbers against multiple invoices (no visibility)

    Posted 3 days ago
    This makes sense! I figured this would be the "non-dirty" solution as I am fairly opposed to writing triggers on things because it can get messy. I was looking into other options (hence writing here) due to the procedures that are currently in place regarding fulfillment and order checking here. Ultimately, this is likely what I will end up suggesting to management.

    Thanks for your input! Also how do you like ShipGear? Does it offer delivery status reporting and things of that nature?

    ------------------------------
    Danny Maxwell
    Business Analyst
    AFL
    Kent Washington
    ------------------------------



  • 11.  RE: duplicate tracking numbers against multiple invoices (no visibility)

    TOP CONTRIBUTOR
    Posted 2 days ago
    Danny-

    I had a ton of concerns about having the warehouse guys do that kind of thing -- have access to sales batches to transfer orders/back orders to invoices and stuff. But then I thought -- it's the only way this process is going to work well. Plus they do all the receiving inside of GP and do a great job with that, so why am I freaking out over nothing?

    ShipGear is great and we've used it for about 11 or 12 years here. We are looking at going to StarShip -- because of the integration with the trucking companies our customers use and we are shipping more via DHL and USPS. ShipGear is limited to UPS and FedEx.

    It does all kinds of activity and status reporting (somewhat similar to StarShip), but everything is stored on the local computer in the warehouse and not in a SQL database.

    -Joe

    ------------------------------
    Joseph C. Markovich
    IT Manager
    Okaya Electric America
    Valparaiso IN
    ------------------------------



  • 12.  RE: duplicate tracking numbers against multiple invoices (no visibility)

    Posted 2 days ago
    Your first point is basically the stance I am taking as well.

    Thanks for the insight on shipgear, I am not familiar and was not familiar with starship prior to here as well.

    ------------------------------
    Danny Maxwell
    Business Analyst
    AFL Hyperscale
    Kent Washington
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role