SQL view help Shipped items vs not shipped items

Epicor ERP 9
1

I’m trying to come up with a way to show on one report the items we shipped on the current day and the items that had a request date of the current day that didn’t ship. Anyone have a good way to do this? I’m not having much luck.

2
  1. Use an OUTER LEFT JOIN between the OrderRel table and the ShipDtl.
  2. Table Criteria for OrderRel to include OrderRel.NeedByDate = GETDATE()
  3. Calcualted field to determine if shipment is today, in the past, in the future, or hasn’t shipped at all.

Note: this doesn’t take into account partial shipments. For that you’d need to compare the Qty shipped on the ShipDtl to the Qty on the OrderRel

Here’s the Query Phrase that E10’s BAQ designer created:

select
	[OrderRel].[OrderNum] as [OrderRel_OrderNum],
	[OrderRel].[OrderLine] as [OrderRel_OrderLine],
	[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
	[OrderRel].[PartNum] as [OrderRel_PartNum],
	[OrderRel].[NeedByDate] as [OrderRel_NeedByDate],
	[ShipHead].[ShipDate] as [ShipHead_ShipDate],
	(case  
     when (ShipHead.ShipDate) is null then 'OPEN'
     when (ShipHead.ShipDate < Constants.Today) then 'PRIOR'
     when (ShipHead.ShipDate = Constants.Today) then 'TODAY'
     else 'FUTURE'
 end) as [Calculated_ShipStat]
from Erp.OrderRel as OrderRel
left outer join Erp.ShipDtl as ShipDtl on  
    OrderRel.Company = ShipDtl.Company 
    And OrderRel.OrderNum = ShipDtl.OrderNum 
    And OrderRel.OrderLine = ShipDtl.OrderLine 
    And OrderRel.OrderRelNum = ShipDtl.OrderRelNum
left outer join Erp.ShipHead as ShipHead on 
	ShipDtl.Company = ShipHead.Company 
    And ShipDtl.PackNum = ShipHead.PackNum
where (OrderRel.NeedByDate = GETDATE())

Here’s a sample of the BAQ output
image

2 Likes
3

Thank you. I’ve been working on this for a couple days, but have struggled to find a way to do this without duplicating records.

I’ll give this a shot!!

4

As an update, I was able to resolve this issue. Here was my final query

SELECT DISTINCT
CUSTOMER.Name, Customer.State, ORDERREL.OrderNum, ORDERREL.ReqDate, ORDERREL.OurReqQty, oRDERHED.OrderDate AS OHORDATE,
ORDERREL.PartNum, ORDERHED.OrderDate, PartWhse.OnHandQty, Orderrel.OurStockShippedQty
FROM ORDERREL WITH (NOLOCK) INNER JOIN
CUSTOMER WITH (NOLOCK) ON CUSTOMER.CustNum = ORDERREL.ShipToCustNum INNER JOIN
PartWhse WITH (NOLOCK) ON PartWhse.PartNum = ORDERREL.PartNum INNER JOIN
OrderDtl WITH (NOLOCK) ON ORDERDTL.OrderNum = ORDERREL.OrderNum AND ORDERDTL.OrderLine = ORDERREL.OrderLine INNER JOIN
OrderHed WITH (NOLOCK) ON OrderHed.OrderNum = ORDERDTL.OrderNum
WHERE orderrel.WarehouseCode = ‘S’ AND partwhse.WarehouseCode = ‘s’ AND (ORDERREL.ReqDate = CAST(GETDATE() AS DATE)) AND OrderDtl.voidline = 0 AND
orderhed.HDCaseNum = 0 AND Orderdtl.OpenLine = 1
UNION
SELECT DISTINCT
customer.Name, customer.State, shipdtl.OrderNum, orderrel.ReqDate, shipdtl.SellingInventoryShipQty, orderhed.OrderDate, shipdtl.partnum, shiphead.ShipDate,
partwhse.OnHandQty, shipdtl.OurInventoryShipQty
FROM SHIPHEAD WITH (NOLOCK) INNER JOIN
SHIPDTL WITH (NOLOCK) ON SHIPDTL.PackNum = SHIPHEAD.PackNum INNER JOIN
PartWhse WITH (NOLOCK) ON PartWhse.PartNum = ShipDtl.PartNum INNER JOIN
Customer WITH (NOLOCK) ON CUSTOMER.CustNum = SHIPDTL.ShipToCustNum INNER JOIN
OrderHed WITH (NOLOCK) ON ORDERHED.OrderNum = SHIPDTL.OrderNum INNER JOIN
OrderRel WITH (NOLOCK) ON ORDERREL.OrderNum = SHIPDTL.OrderNum AND ORDERREL.OrderLine = SHIPDTL.OrderLine AND
ORDERREL.OrderRelNum = SHIPDTL.OrderRelNum
WHERE SHIPDTL.WarehouseCode = ‘S’ AND partwhse.WarehouseCode = ‘s’ AND Shiphead.ShipStatus = ‘Shipped’ AND (SHIPHEAD.ShipDate = CAST(GETDATE() AS DATE))