How to close pull sheets?

Overview

Items in the open Pull Sheets are holding inventory but the orders were already shipped. You can't find these pull sheet numbers in open or closed tables.

Solution

Normally the pull sheet can be closed on the Pull Sheet Window, which can be accessed from the Main Menu > Inventory > Pull Sheets.

Screen_Shot_2021-01-29_at_12.14.50_PM.png

In the case of Pull Sheets that are holding inventory while the orders have already been shipped, they need to be closed using the method below:

Identify the pull sheets

SELECT PT.pull_sheet_no,
       PT.pt_doc_no,
       i.order_no,
       i.shipped,
       X.item_no,
       Sum(qty_to_be_picked - qty_picked) AS qty_held
FROM   pick_ticket PT WITH (nolock),
       invoices i WITH (nolock),
       x_pick_ticket X WITH (nolock)
WHERE  PT.invoices_id = i.invoices_id
       AND PT.pt_doc_no = X.pt_doc_no
       AND PT.ps_open = 'T'
       AND qty_to_be_picked - qty_picked > 0
       AND i.shipped = 'T'
GROUP  BY PT.pull_sheet_no,
          PT.pt_doc_no,
          i.order_no,
          i.shipped,
          X.item_no 

The query above will retrieve the list of Pull Sheets that are open and have shipped items.

Identify any pending line items 

SELECT *
FROM   invoices i
       INNER JOIN x_invoic x
               ON i.order_no = x.order_no
                  AND i.status = x.status
WHERE  i.status = 8
       AND i.order_no IN ( {list of order numbers} )
       AND x.item_qty > qty_ship

Replace the {list of order numbers} with the ones affected in your system. You can use a series of order numbers, such as: i.order_no IN ( '674619','674620','770287' ).

SELECT pt.ps_open,
       i.invoices_id,
       i.order_no,
       i.shipped,
       x.*
FROM   pick_ticket pt
       INNER JOIN invoices i
               ON pt.invoices_id = i.invoices_id
       LEFT JOIN x_invoic x
              ON i.order_no = x.order_no
                 AND i.status = x.status
WHERE  pull_sheet_no IN ( {list of pull sheets} )
       AND x.item_qty > qty_ship 

Replace the {list of pull sheets} with the ones affected in your system. You can use a series of pull sheet numbers, such as: pull_sheet_no IN ( 19983, 19074, 16416 ).

Resolve any line items that may result from the query above. Once no line items need to be resolved, proceed with the following query to close the pull sheets.

Close the pull sheets

SELECT *
INTO   temporary_pick_ticket
FROM   pick_ticket
WHERE  pull_sheet_no IN ( {list of pull sheets} )

-- Closing the tickets
UPDATE pick_ticket
SET    ps_open = 'F'
WHERE  pt_id IN (SELECT pt_id
                 FROM   temporary_pick_ticket) 

Replace the {list of pull sheets} with the pull sheet numbers of the ones you need to close. You can use a series of pull sheet numbers, such as: pull_sheet_no IN ( 19983, 19074, 16416 ).

Additional step

You can try to match the quantity to be picked to the ordered item qty and qty picked to the shipped quantity using the query below:

SELECT PT.pull_sheet_no,
       PT.pt_doc_no,
       i.order_no,
       i.order_date,
       i.shipped,
       x.x_pt_id,
       X.item_no,
       x.x_invoice_id,
       qty_to_be_picked,
       qty_picked,
       item_qty,
       qty_ship
INTO   temporary_x_pick_ticket
FROM   pick_ticket PT WITH (nolock),
       invoices i WITH (nolock),
       x_pick_ticket X WITH (nolock),
       x_invoic xi WITH(nolock)
WHERE  PT.invoices_id = i.invoices_id
       AND PT.pt_doc_no = X.pt_doc_no
       AND x.x_invoice_id = xi.x_invoice_id
       AND pt_id IN (SELECT pt_id
                     FROM   temporary_x_pick_ticket)
       AND qty_to_be_picked - qty_picked > 0
       AND i.shipped = 'T'

---Updating
UPDATE x_pick_ticket
SET    qty_to_be_picked = x.item_qty,
       qty_picked = x.qty_ship
FROM   temporary_x_pick_ticket x
WHERE  x_pick_ticket.x_pt_id = x.x_pt_id 

Testing

Please note that it may take some time for the solution to reflect in the Everest system. If you are still facing issues, please contact support.

Comments

0 comments

Please sign in to leave a comment.