-->

SQL View to get complex data but for which company

2019-09-14 01:48发布

问题:

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

回答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:



标签: acumatica