I have created a SQL view to get complex data based on some calculations. Now I am using these view to create GI. However, while viewing GI result, it is showing me data for all companies irrespective of which company I am logged into.
Here is the SQL view I have created-
CREATE VIEW [dbo].[vw_View1]
AS
SELECT CompanyID,
OrderNbr,
OrderType,
SUM(BodyCost) AS BodyCost,
SUM(TruckCost) AS TruckCost,
SUM(CostOfParts) AS CostOfParts,
SUM(CostOfLabour) AS CostOfLabour,
SUM(CostOfSub) AS CostOfSub,
SUM(GrossSale) AS GrossSale,
SUM(FET) AS FET,
SUM(TireCredit) as TireCredit,
SUM(LabourHours) AS LabourHours,
InvoiceNbr
FROM
(
SELECT
/* 00 */ so.CompanyID,
/* 00 */ so.OrderNbr,
/* 00 */ so.OrderType,
/* 05 */ CASE WHEN (ic.ItemClassID = '030')
OR (ic.ItemClassID = '040')
OR (ic.ItemClassID = '050')
OR (ic.ItemClassID = '060')
OR (ic.ItemClassID = '061')
OR (ic.ItemClassID = '070')
OR (ic.ItemClassID = '080')
OR (ic.ItemClassID = '090')
OR (ic.ItemClassID = '100')
THEN SUM(sol.UsrUserDefinedCost * sol.OrderQty)
END AS BodyCost,
/* 35 */ CASE WHEN (ic.ItemClassID = '110')
OR (ic.ItemClassID = '111')
THEN SUM(sol.OrderQty)
END AS LabourHours
FROM SOOrder so
LEFT JOIN SOLine sol ON so.OrderType = sol.OrderType AND so.OrderNbr = sol.OrderNbr and so.CompanyID = sol.CompanyID
LEFT JOIN InventoryItem inv ON sol.InventoryID = inv.InventoryID and sol.CompanyID = inv.CompanyID
LEFT JOIN INItemClass ic ON inv.ItemClassID = ic.ItemClassID and inv.CompanyID = ic.CompanyID
WHERE so.CompanyID > 0
GROUP BY so.CompanyID,
so.OrderNbr,
so.OrderType,
ic.ItemClassID,
ic.Descr
) AS X
GROUP BY CompanyID, OrderNbr, OrderType, InvoiceNbr;
GO