Finding Inbound Shipment Items Ready to Convert to Vendor Bills

Introduction

We’ve been working on an interesting NetSuite project for the past little while with a company that processes a lot of invoices for combined POs. They produce highly customized items and purchase orders are usually for relatively small quantities. When their manufacturing partner ships an order and creates a bill, the bill could cover 1, 2, 5, 10, 20, or 50 different purchase orders.

They also have a requirement to take ownership of their inventory at the time that it ships from the manufacturing partner. We explored and looked at different options, but ultimately decided to use the inbound shipment record to account for these shipments from vendors.

Since we don’t want to touch paperwork more than once (if we can help it), we only want to record the information on the invoice one time and let the system do its magic to complete the process by generating the vendor bill after the inbound shipment is received.

This post does not cover all the technical ins and outs of converting to a vendor bill, but simply to show how we can determine which lines of an inbound shipment have been received and can be converted to a vendor bill.

Find receipt dates for PO Lines

The very generous Tim Dietrich outlined a technique for [finding transactions related to Purchase Orders in a 2020 blog post][dietrich-find-transactions] that you can use to find receipts and other transactions related to Purchase Order lines.

Our requirement was to search for PO lines that had been received and compare those lines with Inbound Shipment lines associated with the PO Lines to see which inbound shipment lines could be billed.

The following SQL (based on Tim’s article) allows us to find the PO Lines:

-- Find receipt dates for po lines. Stolen from the very generous Tim Dietrich: https://timdietrich.me/blog/netsuite-suiteql-purchase-order-item-receipts/
SELECT
    PO.id AS purchase_order_id,
    POLine.LineSequenceNumber AS po_line_sequence,
    POLine.id as po_line_id,
    Transaction.TranDate AS date_received
FROM
    Transaction
    INNER JOIN TransactionLine ON
        ( TransactionLine.Transaction = Transaction.ID )
        AND ( TransactionLine.IsInventoryAffecting = 'T' )
    INNER JOIN Transaction AS PO ON
        ( PO.ID = TransactionLine.CreatedFrom )
    INNER JOIN Item ON
        ( Item.ID = TransactionLine.Item )
    LEFT OUTER JOIN PreviousTransactionLineLink AS PTLL ON
        ( PTLL.NextType = 'ItemRcpt' )
        AND ( PTLL.NextDoc = TransactionLine.Transaction )
        AND ( PTLL.NextLine = TransactionLine.ID )
    LEFT OUTER JOIN TransactionLine AS POLine ON
        ( POLine.Transaction = PO.ID )
        AND ( POLine.id = PTLL.PreviousLine )
WHERE
    ( Transaction.Type = 'ItemRcpt' )
    AND ( Transaction.Voided = 'F' )
    AND ( TransactionLine.Quantity <> 0 )
ORDER BY
    date_received,
    purchase_order_id,
    po_line_sequence

Note: While I was experimenting with Tim’s solution, I found what I think may be a bug.

Tim’s original SQL had the final LEFT OUTER JOIN clause like this:

LEFT OUTER JOIN TransactionLine AS POLine
    ON ( POLine.Transaction = PO.ID )
    AND ( POLine.linesequencenumber = PTLL.PreviousLine )
              /* ^^^^^^^ I'm not sure this is right */

I think that instead of joining on the linesequencenumber, you should instead join on the id field, like this:

LEFT OUTER JOIN TransactionLine AS POLine
    ON ( POLine.Transaction = PO.ID )
    AND ( POLine.id = PTLL.PreviousLine )

The linesequencenumber field and the id field of the TransactionLine table are usually the same, which is why I think this worked for Tim. If, however, you ever insert new lines on a record after in between existing lines, the line sequence number will be different from the Line ID because NetSuite appends the new line in the database with an incrementally higher ID, and the line sequence numbers change to reflect the new order of the lines.

I did share my thoughts and comments with Tim. As you can imagine he is very busy doing work for his customers, but he did promise to look into this and update the original blog post when he had more time.

[dietrich-find-transactions]: https://timdietrich.me/blog/netsuite-suiteql-purchase-order-item-receipts/

Inbound Shipment Lines that have been received.

Now that we’ve gotten the PO lines and their related receipts, we can link those to the inbound shipment to show what remains to be billed for the shipment line.

To do that, I used the InboundShipment and InboundShipmentItem tables, and join them with the results of our PO Lines query like this:

SELECT
    IS.id is_id,
    IS.shipmentnumber is_shipment_number,
    IS.externalDocumentNumber is_vendor_bill_number,
    IS.expectedshippingdate is_vendor_bill_date,
    ISI.quantityReceived is_quantity_received,
    ISI.quantityBilled is_quantity_billed,
    T.id po_id,
    T.tranid po_document_number,
    T.entity po_vendor,
    TL.id po_line_id,
    TL.linesequencenumber-1 AS po_line_list_location, /* Used to give us the line location for scripting, if needed. */
    TL.quantitybilled po_quantity_billed,
    (ISI.quantityReceived - TL.quantityBilled) quantity_to_bill
FROM
    inboundshipment IS
    INNER JOIN inboundshipmentitem ISI ON ISI.inboundshipment = IS.id
    INNER JOIN transaction T ON T.id = ISI.purchaseordertransaction
    INNER JOIN transactionline TL ON TL.transaction = T.id AND TL.uniqueKey = ISI.shipmentitemtransaction

    /* This is the query discussed earlier */
    LEFT OUTER JOIN (
-- find receipt dates for po lines. Stolen from the very generous Tim Dietrich: https://timdietrich.me/blog/netsuite-suiteql-purchase-order-item-receipts/
SELECT
    PO.id AS purchase_order_id,
    POLine.LineSequenceNumber AS po_line_sequence,
    POLine.id as po_line_id,
    Transaction.TranDate AS date_received
FROM
    Transaction
    INNER JOIN TransactionLine ON
        ( TransactionLine.Transaction = Transaction.ID )
        AND ( TransactionLine.IsInventoryAffecting = 'T' )
    INNER JOIN Transaction AS PO ON
        ( PO.ID = TransactionLine.CreatedFrom )
    INNER JOIN Item ON
        ( Item.ID = TransactionLine.Item )
    LEFT OUTER JOIN PreviousTransactionLineLink AS PTLL ON
        ( PTLL.NextType = 'ItemRcpt' )
        AND ( PTLL.NextDoc = TransactionLine.Transaction )
        AND ( PTLL.NextLine = TransactionLine.ID )
    LEFT OUTER JOIN TransactionLine AS POLine ON
        ( POLine.Transaction = PO.ID )
        AND ( POLine.id = PTLL.PreviousLine )
WHERE
    ( Transaction.Type = 'ItemRcpt' )
    AND ( Transaction.TranDate <= SYSDATE - 10 ) /* In this case, we need to wait for 10 days from the receipt date before we bill */
    AND ( Transaction.Voided = 'F' )
    AND ( TransactionLine.Quantity <> 0 )
ORDER BY
    date_received,
    purchase_order_id,
    po_line_sequence
    ) AS PO_RECEIPTS
    ON ( PO_RECEIPTS.purchase_order_id = T.id )
    AND ( PO_RECEIPTS.po_line_id = TL.id )
WHERE
    ( ISI.quantityReceived - TL.quantityBilled ) > 0
    AND ( PO_RECEIPTS.date_received <= (SYSDATE - 10) ) /* Belt and Suspenders... Probably not necessary.*/

Using the information provided by this query, we now generate a vendor bill for all items that were on the inbound shipment. Two key fields are the original Purchase Order ID (internal ID) and Purchase Order Line ID. Those fields enable us to create a vendor bill linked to one or more purchase orders.

For those interested in learning about that, the Prolecto Team created a blog post about joining multiple POs to a single vendor bill back in 2021. I’ve used the technique there many times to be able to generate vendor bills from multiple POs.

Note on an Issue

Some readers may be wondering why we are not just pushing the “Bill” button on the inbound shipment to complete this proces. One of the other requirements on this project was to bill related, non-receivable/fulfillable items that were on the original PO but not on the inbound shipment. That requirement caused us to use a scripted technique to perform the billing.

What did you think?

Was this information helpful? Do you have a better way to find this? Think we write SQL badly? (If we’re being honest, you’re probably right.) Let us know what you think.