Join between ar invoice and dropshiphead/dtl- what is the proper join?

utaylor · February 5, 2020, 9:34pm

Does anyone know the proper way to join these two tables?

Aaron_Moreng · February 5, 2020, 9:39pm

Other than working back through via the sales order?

utaylor · February 5, 2020, 9:40pm

So the question is where did the invoiced line ship to? I figured the most accurate way to source this would be from the shipment record i.e. a drop shipment in this case. Additionally the invoiced value is required. So my thought process is to start at the invoice and join the shipment record to get the ship to location.

utaylor · February 5, 2020, 9:44pm

Without giving a business case to this though, is that join possible and is there a correct way to do it?

And if I asked the question, “What drop shipment was this on?” and all I had was the invoice, what could I do to arrive at that answer besides open up invoice tracker and use context menus.

Aaron_Moreng · February 5, 2020, 9:44pm

I can share my SQL code for how I report invoiced shipments (both customer and drop ships) via the sales order and invoice if that would help steer you right

utaylor · February 5, 2020, 9:45pm

That would be cool.

Aaron_Moreng · February 5, 2020, 9:54pm

Are you doing this in SQL or BAQ?

utaylor · February 5, 2020, 9:57pm

However you would like to share it is fine with me. I could use either of those.

Aaron_Moreng · February 5, 2020, 10:05pm

Ok this is obviously very specific to the report I generate, but it might give you an idea of what I’m doing.
My business requirements are likely different than yours, so please don’t cut/paste and expect perfection.
Additionally, our lot numbers are unique to both the part and to the shipment line and we do not ship on multiple releases (1 release per line).

/*For Testing Individual Dates, Comment when not in use*/
/*Start Testing Region*/
--DECLARE
--	@Company varchar(3)
--	,@CoolerPart varchar(8)
--	,@FemHeadPart varchar(10)
	
--	,@FromDatePS DATE
--	,@ToDatePS DATE
--SET @FromDatePS = '2019-04-01'
--SET @ToDatePS = '2019-04-30'	
--SET @Company = 'JRF'
--SET @CoolerPart = 'M3S0515L'
--SET @FemHeadPart = 'FMH-001'
/*End Testing Region*/
/*Top Level*/
SELECT 
	InvoiceDtl.InvoiceDate
	,InvoiceDtl.InvoiceSuffix	
	,InvoiceDtl.InvoiceNum
	,InvoiceDtl.PONum
	,InvoiceDtl.InvoiceLine
	,InvoiceDtl.PartNum
	,InvoiceDtl.LineDesc
	,P.ProdCode as PartType
	,P.IUM as UOM
	,CONVERT(DECIMAL(10,2), InvoiceDtl.[Total Line Amt]) as ExtPrice --added 10/12/18 AMM
	,CONVERT(DECIMAL(10,2),P.UnitPrice) as ListPrice
	,OrdersShipped.BTCustID
	,OrdersShipped.BTName
	,OrdersShipped.BTCustAddress1
	,OrdersShipped.BTCustAddress2
	,OrdersShipped.BTCustAddress3
	,OrdersShipped.BTCity
	,OrdersShipped.BTState
	,OrdersShipped.BTZip
	,OrdersShipped.ShipToNum
	,OrdersShipped.ShipToName
	,OrdersShipped.ShipToAddress1
	,OrdersShipped.ShipToAddress2
	,OrdersShipped.ShipToAddress3
	,OrdersShipped.ShipToCity
	,OrdersShipped.ShipToState
	,OrdersShipped.ShipToZIP
	,OrdersShipped.OrderDate
	,OrdersShipped.Instruments as OrderType
	,InvoiceDtl.OrderNum
	,InvoiceDtl.OrderLine
	,CASE 
		WHEN CustomerShip.ShipDtlLotNum IS null then DropShip.LotNum
		WHEN DropShip.LotNum is null then CustomerShip.ShipDtlLotNum
		WHEN CustomerShip.ShipDtlLotNum is null and DropShip.LotNum is null then 'N/A'
		END as LotNumber	
	,CASE
		WHEN InvoiceDtl.InvoiceSuffix = 'CM' then CONVERT(DECIMAL(10,0),OrdersShipped.SellingQuantity)*-1
		ELSE CONVERT(DECIMAL(10,0),OrdersShipped.SellingQuantity)
		END as Quantity
FROM
	(
		SELECT /*Invoice Info*/
			ID.InvoiceNum
			,ID.InvoiceLine
			,ID.ShipToNum
			,IH.OrderNum			
			,ID.OrderLine
			,IH.InvoiceSuffix
			,ID.PartNum
			,IH.PONum
			,ID.LineDesc			
			,CONVERT(DECIMAL(10,2),ID.OrdBasedPrice) as OrderBasedPrice
			,IH.InvoiceDate
			,CONVERT(DECIMAL(10,2),ID.DocExtPrice) as ExtPrice
			,(ID.ExtPrice - ID.Discount) as 'Total Line Amt' --Added 10/12/18 AMM
			
		FROM
			Epicor10Live.Erp.InvcHead IH
				inner join Epicor10Live.Erp.InvcDtl ID
					on IH.Company = ID.Company 
					and IH.InvoiceNum = ID.InvoiceNum
		WHERE IH.Company = @Company
			and ID.PartNum not in (@CoolerPart,@FemHeadPart)
			and IH.InvoiceDate >= @FromDatePS --DateParameters
			and IH.InvoiceDate <= @ToDatePS --Date Parameters
	)InvoiceDtl
INNER JOIN 
	(
		SELECT  /*Order Info*/
			OH.OrderNum as OrderNum
			,OD.OrderLine as OrderLine
			,OH.OrderDate as OrderDate
			,OH.RequestDate as RequestDate
			,C.CustID as BTCustID
			,C.Name as BTName
			,OH.Instruments_c as Instruments
			,OH.PONum as PONum
			,REL.PONum as DSPONum
			,REL.POLine as DSPOLine
			,ST.Name as ShipToName
			,ST.Address1 as ShipToAddress1
			,ST.Address2 as ShipToAddress2
			,ST.Address3 as ShipToAddress3
			,ST.City as ShipToCity
			,ST.ZIP as ShipToZIP
			,ST.State as ShipToState
			,C.Address1 as BTCustAddress1
			,C.Address2 as BTCustAddress2
			,C.Address3 as BTCustAddress3
			,C.City as BTCity
			,C.State as BTState
			,C.Zip as BTZip
			,OH.ShipToNum as ShipToNum
			,OD.PartNum as PartNum
			,OD.LineDesc as LineDesc		
			,(OD.ExtPriceDtl-OD.Discount) as 'TotalLineAmt' --added 10/12/18 AMM
			,OD.SellingQuantity
			,OH.SalesRepList
			,soldTo.CustID
		FROM
		Epicor10Live..OrderHed OH					--OrderHead
			inner join Epicor10Live..OrderDtl OD	--OrderDetail
				on OH.Company = OD.Company 
				and OH.OrderNum = OD.OrderNum
				
			inner join Epicor10Live..OrderRel REL	--OrderRelease
				on OH.Company = REL.Company
				and OH.OrderNum = REL.OrderNum
				and OD.OrderLine = REL.OrderLine
				
			inner join Epicor10Live.Erp.ShipTo ST	--ShipTo
				on OH.Company = ST.Company
				and OH.CustNum = ST.CustNum
				and OH.ShipToNum = ST.ShipToNum
				
			inner join Epicor10Live..Customer C		--Billing Customer
				on OH.Company = C.Company
				and OH.BTCustNum = C.CustNum
			
			inner join Epicor10LIVE.Erp.Customer soldto --sold to
				on OH.Company = soldto.Company
				and OH.CustNum = soldTo.CustNum
			where OH.Company = @Company
	) OrdersShipped
	ON InvoiceDtl.OrderNum = OrdersShipped.OrderNum
	AND InvoiceDtl.OrderLine = OrdersShipped.OrderLine
LEFT JOIN
	(
		SELECT /*Customer Shipment Info*/
			SD.OrderNum as ShipDtlOrderNum
			,SD.OrderLine as ShipDtlOrderLine
			,SD.LotNum as ShipDtlLotNum
		FROM Epicor10Live..ShipHead SH
			inner join Epicor10Live..ShipDtl SD
				on SH.Company = SD.Company
				and SH.PackNum = SD.PackNum
		WHERE SH.Company = @Company
			and SD.PartNum not in (@CoolerPart,@FemHeadPart)
	) CustomerShip
ON OrdersShipped.OrderNum = CustomerShip.ShipDtlOrderNum
	AND OrdersShipped.OrderLine = CustomerShip.ShipDtlOrderLine
LEFT JOIN
	(
		SELECT /*Drop Shipment Info*/
			DSD.PONum
			,DSD.POLine
			,DSD.LotNum
		FROM Epicor10Live.Erp.DropShipHead DSH
			inner join Epicor10Live.Erp.DropShipDtl DSD
				on DSH.Company = DSD.Company
				and DSH.VendorNum = DSD.VendorNum
				and DSH.PurPoint = DSD.PurPoint
				and DSH.PackSlip= DSD.PackSlip
		WHERE DSH.Company = @Company
			and DSD.PartNum not in (@CoolerPart,@FemHeadPart)
	) DropShip
ON OrdersShipped.DSPONum = DropShip.PONum
	AND OrdersShipped.DSPOLine = DropShip.POLine
	
/*Part Info*/
INNER JOIN Epicor10Live..Part P 
ON InvoiceDtl.PartNum = P.PartNum
WHERE P.Company = @Company

and InvoiceDtl.[Total Line Amt] != 0

ORDER BY 
	InvoiceDtl.InvoiceNum
	,InvoiceDtl.InvoiceLine

Topic		Replies	Views
Joining JobHead and OrderDTL to showing job costing and price history in excel Epicor ERP 10 sql	7	652	July 18, 2019
Ship To on Order Release Epicor ERP 10 e10 , epicor , baq	1	504	July 14, 2021
Join InvcHead with POHeader Epicor ERP 10 reports	8	621	February 6, 2024
AR INVOICE Combine Dropship and Pack Slips on 1 Invoice Epicor ERP 10 e10	2	1580	April 26, 2019
The difference and relationship between AR MIS and AR SHP bills Epicor ERP 10 sql , epicor , customization , dashboard	1	815	February 14, 2022

Join between ar invoice and dropshiphead/dtl- what is the proper join?

Related Topics