USE [prince]
GO

/****** Object:  View [Common].[View_CMD_Info]    Script Date: 11/17/2025 14:28:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW [Common].[View_CMD_Info]
AS
	    WITH CTE_Main
        AS (
          SELECT *      
            FROM [Common].[Commodity] 
            JOIN [Common].CountingUnitMultiple ON (CUM_CMD_ID = CMD_ID) AND (CUM_IsDefault = 1) AND (CUM_IsDelete = 0)
            JOIN [Common].CountingUnit ON (CNU_CMD_ID = CMD_ID) AND (CNU_CUM_ID = CUM_ID) AND (CNU_Coefficient IS NULL)
           )
        ,
       CTE_Price
        AS
        (
        
        SELECT P.PRL_CMD_ID,ISNULL([1],0) AS [CMD_Cost1] ,ISNULL([2],0) AS [CMD_Cost2],ISNULL([3],0) AS [CMD_Cost3],ISNULL([4],0) AS [CMD_Cost4],ISNULL([5],0) AS [CMD_Cost5],R.PRL_BuyPrice AS [CMD_InCost] 
        FROM (
            SELECT PRL_CMD_ID,PRL_SalePrice,RN
            from (
                   SELECT R.*,
                          ROW_NUMBER() OVER (PARTITION BY PRL_CMD_ID ORDER BY PRL_IsDefault DESC,PRL_ID) RN 
                     FROM CTE_Main
                     JOIN [Common].PriceLevel R ON (PRL_CMD_ID = CMD_ID) AND (PRL_CUM_ID = CUM_ID) AND (PRL_CNU_ID = CNU_ID)
                    WHERE ((SELECT SST_Value FROM Common.SystemSetting WHERE SST_Title = 'SoftwareType' AND SST_UserID = 0) = 9)
                 ) as b
                 ) as a
              PIVOT
	             (
	              MIN(PRL_SalePrice)
	              FOR RN IN ([1],[2],[3],[4],[5])
	              )
	          AS p   
              JOIN [Common].PriceLevel R ON (R.PRL_CMD_ID = P.PRL_CMD_ID) AND (PRL_IsDefault = 1) 
         union all
         
         select PRL_CMD_ID,PRL_SalePrice AS [CMD_Cost1] ,0 AS [CMD_Cost2],0 AS [CMD_Cost3],0 AS [CMD_Cost4],0 AS [CMD_Cost5],PRL_BuyPrice AS [CMD_InCost] 
           FROM CTE_Main
           JOIN [Common].PriceLevel R ON (PRL_CMD_ID = CMD_ID) AND (PRL_CUM_ID = CUM_ID) AND (PRL_CNU_ID = CNU_ID)
                and PRL_IsDefault = 1
           WHERE ((SELECT SST_Value FROM Common.SystemSetting WHERE SST_Title = 'SoftwareType' AND SST_UserID = 0) <> 9)
           
        )
         
        SELECT
               [CMD_ID]
			  ,[CMD_Name]
			  ,[CMD_AbbName]
			  ,[CMD_Code]
			  ,[CMD_Description]
			  ,[CMD_LKP_Type]
			  ,[CMD_PLC_Store]
			  ,[CMD_PLC_Department]
			  ,[CMD_CMC_ID]
			  ,[CMD_MinimumAvailable]
			  ,[CMD_MaximumAvailable]
			  ,[CMD_PrintBarcode]
			  ,[CMD_HasSerialNumber]
			  ,[CMD_HasTax]
			  ,[CMD_IsDisabled]
			  ,[CMD_Image]
			  ,[CMD_CurrentAmount]
			  ,[CMD_CNU_ID_OfPrice]
			  ,[CMD_HotKeyCode]
			  ,[CMD_Market_Percent]
			  ,[CMD_HasPercent]
			  ,[CMD_Tax]
			  ,[CMD_Formula1]
			  ,[CMD_Formula2]
			  ,[CMD_CMA_Count]
			  ,[CMD_FDATE]
			  ,[CMD_TDATE]
			  ,[CMD_Discount_kind]
			  ,[CMD_MONEY]
			  ,[CMD_FDATE_Int]
			  ,[CMD_TDATE_Int]
			  ,[CMD_Weighted]
			  ,[CMD_Display_BTN]
			  ,[CMD_ORDER]
			  ,[CMD_CoverPrice]
			  ,[CMD_IncDec]
			  ,[CMD_CoverPercent]
			  ,[CMD_IsEssential]
			  ,[CMD_AvailableToCustomer]
			  ,[CMD_IsIngredient]
			  ,[CMD_UserProductionForStore]
			  ,[CMD_CountForProduct]
			  ,[CMD_HasToll]
			  ,[CMD_Toll]
			  ,[CMD_KitchenUse]
			  ,[CMD_IsCalcGuest]
			  ,[CMD_LastEditedTime]
			  ,[CMD_FirstCategory_ID]
			  ,[CMD_SecondCategory_ID]
			  ,[CMD_ThirdCategory_ID]
			  ,[CMD_CategoryCaption]
			  ,[CMD_Barcode_list]
			  ,[CMD_PTL_ID]
			  ,(SELECT [PTL_Name] FROM [Common].[PrintLine] WHERE ([PTL_ID] = CMD_PTL_ID))AS [CMD_PTL]
			  ,[CMD_IMG_ID]
			  ,[CMD_IsFood]
			  ,[CMD_InitialCount]
			  ,[CMD_LKP_RecGrpID]
			  ,(SELECT [LKP_Caption] FROM [Common].[Lookup] WHERE ([LKP_ID] = [CMD_LKP_RecGrpID])) AS [CMD_RecGrp]
			  ,[CMD_LKP_Section]
			  ,(SELECT [LKP_Caption] FROM [Common].[Lookup] WHERE ([LKP_ID] = [CMD_LKP_Section])) AS [CMD_Section]
			  ,[CMD_PrintOrder]
			  ,[CMD_Saloon_PTL_ID1]
			  ,[CMD_Saloon_PTL_ID2]
			  ,[CMD_Out_PTL_ID1]
			  ,[CMD_Out_PTL_ID2]
			  ,[CMD_SalesCount]
			  ,[CMD_InputCount]
			  ,[CMD_Saloon_PTL_ID3]
			  ,[CMD_Out_PTL_ID3]
			  ,[CMD_LKP_Group]
			  ,(SELECT [LKP_Caption] FROM [Common].[Lookup] WHERE ([LKP_ID] = [CMD_LKP_Group])) AS [CMD_Group]  
			  ,[CMD_UseProductionForProfit]
			  ,[CMD_Saloon_PTL_ID4]
			  ,[CMD_Out_PTL_ID4]
			  ,[CMD_Saloon_PTL_ID5]
			  ,[CMD_Out_PTL_ID5]
			  
         ------------------------------------------
              ,[CUM_ID]
              ,[CUM_NAME] 
         ------------------------------------------
              ,[CNU_ID]
              ,[CNU_LKP_UnitID]
              ,(SELECT [LKP_Caption] FROM [Common].[Lookup] WHERE ([LKP_ID] = [CNU_LKP_UnitID])) AS [CMD_Unit] 
         ------------------------------------------                          
			  ,[CMD_InCost] 
              ,[CMD_Cost1] 
              ,[CMD_Cost2]
              ,[CMD_Cost3] 
              ,[CMD_Cost4]
              ,[CMD_Cost5] 
		 ------------------------------------------
              ,[LKP_CityCode] AS [GroupColor]
              ,LKP_Order
              ,CMD_IsRent
              ,CMD_Exempt_TaxTol_SaffronSale
              ,[CMD_IsBrand]
              ,[CMD_MaxSaleInOneFactor]
              ,[CMD_SourceBranchCode]
              ,[CMD_ShareInApi]
              ,[CMD_FoodContents]
              ,[CMD_IsHide_Kiosk]
              ,[CMD_IsHide_VirtualMenuApp]  
			  ,[CMD_LKP_KioskAppGroup]  
			  ,(SELECT [LKP_Caption] FROM [Common].[Lookup] WHERE ([LKP_ID] = [CMD_LKP_KioskAppGroup])) AS [CMD_KioskAppGroup]      
			  ,[CMD_LKP_VirtualMenuAppGroup]
			  ,(SELECT [LKP_Caption] FROM [Common].[Lookup] WHERE ([LKP_ID] = [CMD_LKP_VirtualMenuAppGroup])) AS [CMD_VirtualMenuAppGroup]  
        FROM CTE_Main
        JOIN CTE_Price ON (PRL_CMD_ID = CMD_ID)
        LEFT JOIN [Common].[Lookup] ON [CMD_LKP_Group] = [LKP_ID] 







GO


