Finding lots on invoices

Prophet 21
, , , , , ,
1

I have a customer who would like to see what lots they have purchased from over the last 5 years, but I’m having trouble writing a query that can return lot numbers based on the customers invoices.

I’m using prophet 21 v12.1 and using Microsoft SQL Server Management Studio to find my data. I’m looking to return the following values for a certain customer in a certain time frame

  • Customer ID
  • Invoice Date
  • Invoice No
  • PO No
  • Qty Shipped
  • Item ID
  • Item Desc
  • Lot

I’m having trouble finding a link between an invoice line and it’s associated lots. I’ve got the following query going (with way to many inner joins) but this query returns every single lot starting with A or B for every invoice instead of the ones associated with the invoices.

SELECT DISTINCT
       ih.customer_id
      ,ih.invoice_date
      ,ih.invoice_no
      ,ih.po_no
      ,il.[qty_shipped]
      ,im.[item_id]
      ,im.[item_desc]
      ,lo.lot
     
  FROM [P21].[dbo].[inv_mast] as im
  INNER JOIN P21.dbo.lot as lo
  on lo.inv_mast_uid= im.inv_mast_uid

  INNER JOIN P21.dbo.invoice_line as il
  on il.inv_mast_uid = im.inv_mast_uid 

  INNER JOIN [P21].[dbo].[invoice_hdr] as ih
  on ih.invoice_no= il.invoice_no

  INNER join P21.dbo.invoice_line
  on lo.inv_mast_uid= il.inv_mast_uid

  INNER JOIN P21.dbo.inv_mast_lot as iml
  on iml.inv_mast_uid= lo.inv_mast_uid


  WHERE im.inv_mast_uid in 
	(SELECT inv_mast_uid
	 FROM P21.dbo.invoice_line
	 WHERE invoice_no in( 
		SELECT invoice_no
		FROM P21.dbo.invoice_hdr
		WHERE customer_id= 102337))
  AND ih.invoice_no between 5183101 AND 5221587
  AND (lot  like 'A%' 
	   OR lot like 'B%')
  AND il.item_id like 'ZZ-132%'
  AND YEAR(lo.date_last_modified) > 2019

I’m pretty new to both Prophet21 and SQL, so any help or feedback would be much appreciated.

2

Sorry, I don’t know the P21 Schema at all.

I’d suggest just breaking it down into little chunks one join at a time to work it out.

It seems that you have a lot of joins going on which should not be necessary. I think there is a duplicate as well.

2 Likes
3

Hi Simon.

I was able to solve my problem due to this advice. I didn’t realize how much data I was getting that I didn’t need. I removed the joins with aliases labeled “im” and “iml” which began to solve my problem.

When it was broken down a bit, I realized that the “link” between and invoice line and it’s associated lots was actually the date_last_modified column. Typically, this wouldn’t work, but the company I did this for used Prophet21’s lot tool to create hose assemblies, where it may have made more sense to use a serial number or bin.

Thanks so much for your help!

1 Like
4

Sometimes it’s easy to not see the forest from the trees… or is the the trees from the forest :thinking: