BAQ Pulling Lots of Data

Epicor ERP 10
,
1

Hi All,

Can someone tell me where I’ve gone wrong with this?

TEAGLE-PartStoresAllocation.baq (120.8 KB)

Here is the SQL SELECT which works perfectly and returns 28 rows where the BAQ times out or returns over 20K

SELECT a.PartNum, a.PartDescription, a.TypeCode, COALESCE (b.WrksOHQty, 0.00) AS WrksOHQty, COALESCE (c.PartsOHQty, 0.00) AS PartsOHQty, COALESCE (d.minqty, 0.00) AS minqty, COALESCE (e.replenqty, 0.00) AS replenqty, f.jnum, 
                  g.ponu, h.jobqty, i.poqty, j.QBearing
FROM     Erp.Part AS a LEFT OUTER JOIN
                      (SELECT SparesMonitored_c AS SparesMonitored, ForeignSysRowID
                       FROM      Erp.Part_UD) AS k ON a.SysRowID = k.ForeignSysRowID LEFT OUTER JOIN
                      (SELECT PartNum, SUM(OnHandQty) AS WrksOHQty
                       FROM      Erp.PartWhse
                       WHERE   (WarehouseCode = 'WRKS')
                       GROUP BY PartNum) AS b ON a.PartNum = b.PartNum LEFT OUTER JOIN
                      (SELECT PartNum, SUM(OnHandQty) AS PartsOHQty
                       FROM      Erp.PartWhse
                       WHERE   (WarehouseCode = 'PARTS')
                       GROUP BY PartNum) AS c ON a.PartNum = c.PartNum LEFT OUTER JOIN
                      (SELECT PartNum, MinimumQty AS minqty
                       FROM      Erp.PartPlant
                       WHERE   (Plant = 'TGLPS')) AS d ON a.PartNum = d.PartNum AND c.PartsOHQty < d.minqty LEFT OUTER JOIN
                      (SELECT Erp.PartPlant.PartNum, SUM(CASE WHEN erp.part.Typecode = 'p' THEN erp.partplant.MinOrderQty WHEN erp.part.Typecode = 'm' THEN erp.partplant.MinMfgLotSize END) AS replenqty
                       FROM      Erp.PartPlant INNER JOIN
                                         Erp.Part ON Erp.PartPlant.PartNum = Erp.Part.PartNum
                       WHERE   (Erp.PartPlant.Plant = 'TGLPS')
                       GROUP BY Erp.PartPlant.PartNum) AS e ON a.PartNum = e.PartNum LEFT OUTER JOIN
                      (SELECT PartNum, JobNum AS jnum
                       FROM      Erp.JobHead
                       WHERE   (JobClosed = 0) AND (NOT (JobNum LIKE 'WAR%')) AND (NOT (JobNum LIKE 'PL%'))) AS f ON a.PartNum = f.PartNum LEFT OUTER JOIN
                      (SELECT PartNum, PONUM AS ponu
                       FROM      Erp.PODetail
                       WHERE   (OpenLine = 1)) AS g ON a.PartNum = g.PartNum LEFT OUTER JOIN
                      (SELECT PartNum, JobNum, SUM(ProdQty - ReceivedQty) AS jobqty
                       FROM      Erp.JobProd
                       GROUP BY JobNum, PartNum) AS h ON a.PartNum = h.PartNum AND f.jnum = h.JobNum LEFT OUTER JOIN
                      (SELECT PartNum, PONUM, SUM(OrderQty) AS poqty
                       FROM      Erp.PODetail
                       GROUP BY PONUM, PartNum) AS i ON a.PartNum = i.PartNum AND g.ponu = i.PONUM LEFT OUTER JOIN
                      (SELECT PartNum, QtyBearing AS QBearing
                       FROM      Erp.PartPlant
                       WHERE   (Plant = 'MFGSYS')) AS j ON a.PartNum = j.PartNum
WHERE  (d.minqty <> 0) AND (e.replenqty <> 0)

Everything is matching the SQL Select so I believe…

Ugh…

Cheers!

2

First thing (but won’t solve your problem) is that I don’t see any joining by Company. This will be an issue.

3

Hi Jason,

I resolved this issue. The joins in the BAQ where slightly different from the SQL I wrote.

Weird huh? Still didn’t use company to company in the BAQ and it’s working like a treat now to add open orders against the part num for ATP stock. :disappointed::grimacing:

4

Honestly, it will be at your peril if you don’t. This can cause locks that are hard to find and make your SQL server work much harder than it has to.

1 Like
5

Best put some company to company relationships :pleading_face:

6

Hi Jason,

You able to help me with this one? I keep getting this error on a calculated field.

image

With the following calculation, C.PartNum has Groupby, K.PartNum has Groupby, D.PartNum also has group b but i’m not sure where I’m going wrong?

SUM(C.Calculated_PARTSQOH - K.OrderDtl_OrderQty < D.PartPlant_MinimumQty)
7

< is not a valid mathematical operation. What are you trying to do?

8

it looks like you want something like:
sum( case when C.Calculated_PARTSQOH - K.OrderDtl_OrderQty < D.PartPlant_MinimumQty then FieldOrValueToSum else 0 end)