SQL View to get complex data but for which company

Question

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

Answer 1

On my end, with a view similar to yours GI results contain only records accessible from the current company. Below are code snippets and screenshots to prove this:

• SQL view:

  CREATE VIEW [dbo].[View1]
  AS
  SELECT  
      CompanyID,
      OrderNbr, 
      OrderType,
      SUM(LabourHours) AS LabourHours
  FROM
      (
          SELECT
              so.CompanyID,
              so.OrderNbr,
              so.OrderType,
              SUM(sol.OrderQty) 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
  GO
  

• DAC:

  using System;
  using PX.Data;
  
  namespace View
  {
    [Serializable]
    public class View1: IBqlTable
    {
      #region OrderNbr
      [PXDBString(15, IsKey = true, IsUnicode = true, InputMask = "")]
      [PXUIField(DisplayName = "Order Nbr")]
      public string OrderNbr { get; set; }
  
      public class orderNbr : IBqlField{}
      #endregion
  
      #region OrderType
      [PXDBString(2, IsKey = true, IsFixed = true, InputMask = "")]
      [PXUIField(DisplayName = "Order Type")]
      public string OrderType { get; set; }
  
      public class orderType : IBqlField{}
      #endregion
  
      #region LabourHours
  
      [PXDBDecimal()]
      [PXUIField(DisplayName = "Labour Hours")]
      public Decimal? LabourHours { get; set; }
  
      public class labourHours : IBqlField{}
      #endregion
    }
  }
  

• GI result for Company:

• GI result for Company2:

Per request below, I've added Sales Order of the SO Type and 001252 Number in both companies. Please see screenshots below with results (nothing unexpected or strange):

• GI result for Company:

• GI result for Company2: