SMS/SQL - Contents of a Package
select a.id as adjustment_id, p.id as package_id, pr.edi_number, pr.description, pl.lot_number from sms.adjustment a left join sms.package p on a.container_type = '3' and p.id = a.container_id left join sms.product pr on pr.id = a.product_id left join sms.product_lot pl on pl.id = a.lot_id and pl.product_id = pr.id where p.id = '20083765'
EDIT: As Pat mentioned, you may need to go to the Transfer table instead of the adjustment table. Something like the below will allow you to find items and lots off the transfer table, but will not be able to identify lots/items inside of kits.
select t.id as transfer_id, p.id as package_id, pr.edi_number, pr.description, pl.lot_number from sms.transfer t left join sms.package p on t.to_container_type = '3' and p.id = t.to_container_id left join sms.product pr on pr.id = t.product_id left join sms.product_lot pl on pl.id = t.lot_id and pl.product_id = pr.id where p.id = '19919870' and cancel_reason_code = '0'
If you need both adjustments and transfers you could use something like the below, but it still would not address kits, and I am not entirely sure what would happen if you had both adjustments and transfers against the same package.
select coalesce(a.id,t.id) as adjustment_transfer_id, p.id as package_id, coalesce(pr1.edi_number,pr2.edi_number) as edi_number, coalesce(pr1.description,pr2.description) as product_description, coalesce(pl1.lot_number,pl2.lot_number) as lot_number from sms.package p left join sms.adjustment a on a.container_type = '3' and p.id = a.container_id left join sms.transfer t on t.to_container_type = '3' and p.id = t.to_container_id and cancel_reason_code = '0' left join sms.product pr1 on pr1.id = t.product_id left join sms.product pr2 on pr2.id = a.product_id left join sms.product_lot pl1 on pl1.id = t.lot_id and pl1.product_id = pr1.id left join sms.product_lot pl2 on pl2.id = a.lot_id and pl2.product_id = pr2.id where p.id in('20083765','19919870')