
-------------------------------Ali 2020-12-24--------------------------

  CREATE procedure [dbo].[Inv_StoreBalance]

(@storeCode int =null)
as
SELECT        Inv.StoreTransactionDetail.ItemCode, dbo.INV_ItemUnits.Name AS UnitName, dbo.INV_ItemUnits.Arabic_Name AS UnitAraName, Inv.Store.Name AS StoreName, Inv.Store.Arabic_Name AS StoreAraName, Inv.Store.StoreCode, 
                         Inv.ItemCard.ItemCardSerial, Inv.ItemCard.Name AS ItemName, Inv.ItemCard.Arabic_Name AS ItemAraName, SUM(Inv.StoreTransactionDetail.Quantity * Inv.StoreTransactionDetail.Indicator) AS Qty, 
                         Inv.StoreTransactionDetail.UnitPrice, Inv.StoreTransactionMaster.TransactionDate
FROM            Inv.StoreTransactionDetail INNER JOIN
                         Inv.StoreTransactionMaster ON Inv.StoreTransactionDetail.StoreTransactionMasterCode = Inv.StoreTransactionMaster.StoreTransactionMasterCode INNER JOIN
                         Inv.Store ON Inv.StoreTransactionDetail.StoreCode = Inv.Store.StoreCode INNER JOIN
                         dbo.INV_ItemUnits ON Inv.StoreTransactionDetail.UnitCode = dbo.INV_ItemUnits.UnitCode INNER JOIN
                         Inv.ItemCard ON Inv.StoreTransactionDetail.ItemCode = Inv.ItemCard.ItemCardCode
                       
					    where (@storeCode IS NULL OR Inv.Store.StoreCode = @storeCode)

GROUP BY Inv.StoreTransactionDetail.ItemCode, dbo.INV_ItemUnits.Name, dbo.INV_ItemUnits.Arabic_Name, Inv.Store.Name, Inv.Store.Arabic_Name, Inv.Store.StoreCode, Inv.ItemCard.ItemCardSerial, Inv.ItemCard.Name, 
                         Inv.ItemCard.Arabic_Name, Inv.StoreTransactionDetail.UnitPrice, Inv.StoreTransactionMaster.TransactionDate

  -----------------------------End 2020-12-24----------------------------
  ----------------------------Mahmoud Elsayed 2020-12-24------------------
  create view   WF_TypeResidencyFeesARHeaders
 as
SELECT '29' AS [RequestTypeCode],  [1], [2], [3], [4]  ,[5]  
FROM  (SELECT [TypeResidencyFeeCode], [Arabic_Name]   
    FROM [dbo].[WF_TypeResidencyFees]) AS SourceTable  
PIVOT  
(  
max ( [Arabic_Name])  
FOR [TypeResidencyFeeCode] IN ( [1], [2], [3], [4]  ,[5] )  
) AS PivotTable;
GO
Create view  WF_TypeResidencyFeesEnHeaders
 as
SELECT        '29' AS [RequestTypeCode], [1], [2], [3], [4], [5]
FROM            (SELECT        [TypeResidencyFeeCode], [Name]
                           FROM            [dbo].[WF_TypeResidencyFees]) AS SourceTable PIVOT (max([Name])
						   FOR [TypeResidencyFeeCode] IN ([1], [2], [3], [4], [5])) AS PivotTable;
---------------------------2020-12-24-----------------------------------------

