ODBC > Get Parent Kit/Assembly Items for a Transaction Record

Given the Transaction ID of a specific transaction, we can get its items using the sample query below. The query gets the Internal ID of the transaction from the TRANSACTION table, and uses this to get the line items in the TRANSACTION_LINES table.

Sample SQL:
SELECT * FROM TRANSACTIONS, TRANSACTION_LINES
WHERE TRANSACTIONS.TRANSACTION_ID = TRANSACTION_LINES.TRANSACTION_ID AND
TRANSACTIONS.TRANID = 'SO123456' AND
(TRANSACTIONS.TRANSACTION_TYPE = 'Sales Order')

If the transaction contains an Assembly or Kit Item, all its member items are also returned. To only get the parent Assembly/Kit Item, use the filter KIT_PART_NUMBER and set this to NULL as shown below.

Sample SQL:
SELECT * FROM TRANSACTIONS, TRANSACTION_LINES
WHERE TRANSACTIONS.TRANSACTION_ID = TRANSACTION_LINES.TRANSACTION_ID AND
TRANSACTION_LINES.kit_part_number IS NULL AND
TRANSACTIONS.TRANID = 'SO123456' AND
(TRANSACTIONS.TRANSACTION_TYPE = 'Sales Order')