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.
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.