SQL Query Help

Epicor ERP 9
,
1

Hello, could any body help me find out how the NewReco field is working it seems to be add up the values in SQL - dbo.GetShipmentValue(dbo.ShipTo.Country, dbo.ShipHead.ShipViaCode, dbo.ShipHead.Weight, dbo.ShipTo.CustNum)

Full query below.

SELECT     TOP (100) PERCENT dbo.ShipHead.ShipDate, dbo.ShipHead.PackNum, dbo.Customer.CustID, dbo.Customer.Name AS Custname, dbo.ShipHead.Weight, 
                      dbo.ShipTo.City AS ShipCity, dbo.ShipTo.Country AS ShipCountry, CASE WHEN COALESCE (dbo.ShipHead.Character01, '') 
                      = '' THEN '0' ELSE rtrim(dbo.ShipHead.Character01) END AS ShippingCost, dbo.ShipHead.Character02 AS RecoveryCost, dbo.Customer.Number01, 
                      dbo.GetUnitPrice(dbo.ShipHead.PackNum) AS RecCost, dbo.GetShipmentValue(dbo.ShipTo.Country, dbo.ShipHead.ShipViaCode, dbo.ShipHead.Weight, 
                      dbo.ShipTo.CustNum) AS NewReco, dbo.ShipHead.ShipViaCode
FROM         dbo.ShipTo INNER JOIN
                      dbo.ShipHead ON dbo.ShipTo.ShipToNum = dbo.ShipHead.ShipToNum AND dbo.ShipTo.CustNum = dbo.ShipHead.CustNum INNER JOIN
                      dbo.Customer ON dbo.ShipTo.CustNum = dbo.Customer.CustNum AND dbo.ShipTo.Company = dbo.Customer.Company
ORDER BY dbo.ShipHead.PackNum DESC

thanks

2

It looks like a custom Scalar Function that is being passed three parameters. In SSMS you can open the DB - Programmability - Scalar-valued Functions and look for dbo.GetShipmentValue. You can then right-click - Modify to view the SQL for that function and edit it if needed.

It also looks like you have one called dbo.GetUnitPrice that is being passed the PackNum.