USE [INVENTORY]
GO
/****** Object:  StoredProcedure [dbo].[sp_Inv_PriceListAll_PerLocation]    Script Date: 7/2/2021 4:43:53 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER Procedure [dbo].[sp_Inv_PriceListAll_PerLocation]--'21'
	@LocationID as varchar(10)
as


SELECT distinct A.ItemID,b.supplierid, isNull(B.ItemName,'') + ' ' + isNull(B.ItemDesc,'') AS ItemDesc, 
isNull(A.OnHand,0) AS OnHand,
--F.RoomClass,
OPD.sellingpricein AS OPD,
WARD.sellingpricein AS WARD,
SEMIPRIVATE.sellingpricein AS SEMIPRIVATE,
PRIVATE.sellingpricein AS PRIVATE,
SUITE.sellingpricein AS SUITE,
PRESIDENTIALSUITE.sellingpricein AS PRESIDENTIALSUITE,
semisuite.sellingpricein AS SEMISUITE,
er.sellingpricein AS ER,
icu.sellingpricein as ICU,	 
isnull(C.ItemClassificationName,''), 
B.Listcost AS CurrentCost, 
isnull(B.Packing,0) [Packing], 
A.LocationID, 
D.Location,
ISNULL(C.ItemClassificationName,'') as Itemclassificationname,
S.ListCost as OldCost
FROM Inventory..tbInvent A 
	Left Outer Join Inventory..tbinvMaster B  ON A.ItemId = B.ItemID  
	Left Outer Join Inventory..tbinvClassification C  ON B.ItemClassificationID = C.ItemClassificationID 
	Left Outer Join tbInvLocation D on A.LocationID = D.LocationID
	--Left Join tbInvSocialize E On A.ItemID = E.ItemID and E.LocationID = A.LocationID
	outer apply (select SellingPriceIn as sellingpricein from tbInvSocialize where A.ItemID = ItemID and LocationID = A.LocationID and RoomClassID = 'A') as OPD
	outer apply (select SellingPriceIn as sellingpricein from tbInvSocialize where A.ItemID = ItemID and LocationID = A.LocationID and RoomClassID = 'B') as WARD
	outer apply (select SellingPriceIn as sellingpricein from tbInvSocialize where A.ItemID = ItemID and LocationID = A.LocationID and RoomClassID = 'C') as SEMIPRIVATE
	outer apply (select SellingPriceIn as sellingpricein from tbInvSocialize where A.ItemID = ItemID and LocationID = A.LocationID and RoomClassID = 'D') as PRIVATE
	outer apply (select SellingPriceIn as sellingpricein from tbInvSocialize where A.ItemID = ItemID and LocationID = A.LocationID and RoomClassID = 'E') as SUITE
	outer apply (select SellingPriceIn as sellingpricein from tbInvSocialize where A.ItemID = ItemID and LocationID = A.LocationID and RoomClassID = 'F') as PRESIDENTIALSUITE
	outer apply (select SellingPriceIn as sellingpricein from tbInvSocialize where A.ItemID = ItemID and LocationID = A.LocationID and RoomClassID = 'G') as SEMISUITE
	outer apply (select SellingPriceIn as sellingpricein from tbInvSocialize where A.ItemID = ItemID and LocationID = A.LocationID and RoomClassID = 'I') as ER
	outer apply (select SellingPriceIn as sellingpricein from tbInvSocialize where A.ItemID = ItemID and LocationID = A.LocationID and RoomClassID = 'J') as ICU
	Left outer join tbinvsocialize S on a.itemid = s.itemid and a.locationid = s.locationid
	
	--Left Join BUILD_FILE..TbCoRoomClass F ON E.RoomClassID = F.RoomClassID
Where A.LocationID = @LocationID 
	  and (A.ItemStatus = 0 or  A.ItemStatus is null) and isnull(B.ItemStatus,0) <> 1
	  --and F.RoomClassID NOT IN ('H')
order by ItemDesc

 
 


--orig
--SELECT distinct A.ItemID,b.supplierid, isNull(B.ItemName,'') + ' ' + isNull(B.ItemDesc,'') AS ItemDesc, 
--isNull(A.OnHand,0) AS OnHand,
--F.RoomClass,
--E.RoomRate,
--E.SellingPriceIn,
--E.SellingPriceOut,
--isnull(C.ItemClassificationName,''), 
--B.Listcost, 
--isnull(B.Packing,0) [Packing], 
--A.LocationID, 
--D.Location,
--ISNULL(C.ItemClassificationName,'') as Itemclassificationname
--FROM Inventory..tbInvent A 
--	Left Outer Join Inventory..tbinvMaster B 
--		ON A.ItemId = B.ItemID  
--	Left Outer Join Inventory..tbinvClassification C 
--		ON B.ItemClassificationID = C.ItemClassificationID 
--	Left Outer Join tbInvLocation D
--		on A.LocationID = D.LocationID
--	Left Join tbInvSocialize E
--		On A.ItemID = E.ItemID and E.LocationID = A.LocationID
--	Left Join BUILD_FILE..TbCoRoomClass F
--		ON E.RoomClassID = F.RoomClassID
--Where A.LocationID = @LocationID 
--	  and (A.ItemStatus = 0 or  A.ItemStatus is null) and isnull(B.ItemStatus,0) <> 1
--	  and F.RoomClassID NOT IN ('G','H')
--order by ItemDesc