SMS/SQL - Contents of a Package


62
views
1
3 months ago by
With SQL, how does one find out what items and lots were in an SMS package when you have the package number?
Community: Zimmer Biomet

1 Answer


-2
3 months ago by
From package you need to go down to the adjustment table, from there you can pull the lot number and product number from their respective tables.
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')

  

This is not necessarily true.  There are not always adjustments associated to packages.   

You would need to look at transfers in and out of the packages as well.  Also, this gets really challenging when you start having kits because the stock table shows the kit in the package, while the components (lot level) are in the valid/invalid kit (stock record).  You would have to build an item history table down to the second.  It is theoretically possible, but would require major upgrades to Postgres environment.  Does anyone have a super computer because this is an interesting?!
written 3 months ago by Patrick Schenkel  
Please login to add an answer/comment or follow this question.

Similar posts:
Search »