 
GO

/****** Object:  StoredProcedure [dbo].[PendingPurchaseOrderOfProduct]    Script Date: 12/09/2020 19:40:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--PendingPurchaseOrderOfProduct 3474,1,1,0
ALTER PROCEDURE [dbo].[PendingPurchaseOrderOfProduct](
@ItemId numeric(18,0),
@DeptId numeric(18,0),
@UserId numeric(18,0),
@Type numeric(18,0)
)
AS BEGIN

--DROP TABLE  #POTBL;DROP TABLE  #PPTBL;DROP TABLE  #MRVTBL;DROP TABLE  #PITBL;DROP TABLE  #MRVQTY;DROP TABLE  #PIQTY;DROP TABLE  #PIFINAL;DROP TABLE #PPNO;
if(@ItemId<>0)
begin

DECLARE @RFLG NUMERIC(18,0)=0;
IF EXISTS(SELECT * FROM Mst_Users WHERE UserMenu LIKE '%'+'M204'+'%' AND UserId=@UserId)
SELECT @RFLG=1;

DECLARE @TBLDUMMYSUP AS TABLE(CustId NUMERIC(18,0),CustName varchar(300))
INSERT INTO @TBLDUMMYSUP 
SELECT CustId,CASE WHEN @RFLG=0 THEN CustContactName3 ELSE CustName END FROM Mst_CustomerMain WHERE DelFlag=1


select  '' as  PPSubID  , POs.SubId ,Pos.POMainSlno as  OrderNo, 
OrderDate   , 
(select top 1  CustName from @TBLDUMMYSUP where CustId = PPM.SupplierId) as  CustName ,
0 DeptId,
0 SuppId , 
0 PendingQty,
PPPm.ShipDate  
,(select top 1 CurrencyName from Mst_Currency where Id=PPM.CurrencyId) as  CurrencyName  ,
POS.FCRate , 
isnull(Pps.InvPPSlno,0) InvPPSlno ,POS.Quantity  ,isnull(Pps.Quantity,0)-isnull(pps.PPMrv_qty,0)  as  Performa_Qty  ,
isnull(PPMrv_qty,0) as  'MRV',convert(numeric(18,0),0) 'PUQty', 
convert(numeric(18,0),0) 'MRVSUBID',0 ReceivedQty,0 LocationId,
0 Rate,0 CurrencyRate,0	UserId,  pos.Performa_Qty 'PPQTY',
0	LocationName,0	DepartmentCode,0	Name 
into #dd1 from Inv_PurchaseOrderSub pos left outer  join Inv_PerformaSub pps on pos.SubId = pps.PO_SubId  and pps.DelFlag=1 
left outer join Inv_PurchaseOrderMain PPM on  PPM.OrderNo=POS.POMainSlno and PPM.DelFlag=1 
left outer join Inv_PerformaMain PPPM  on PPPM.PPNo=pps.InvPPSlno and PPPM.DelFlag=1 
where pos.ItemId= @ItemId and pos.DelFlag=1 --and ((pos.Quantity- pps.PPMrv_qty)<>0)
and CONVERT(datetime, OrderDate,103)> CONVERT(datetime,'10/02/2020',103)

update #dd1 set MRV=ps.Performa_Qty from Inv_PurchaseOrderSub ps where  ps.SubId=#dd1.SubId and mrv=0 --and ps.Performa_Qty=0

delete  from  #dd1 where Quantity<=PPQTY and Performa_Qty=0
update #dd1 set ReceivedQty=ReceivedQty+MRV where Performa_Qty=0

select * from #dd1 where (Quantity+Performa_Qty-MRV)>0
--union all
--select distinct * from #dd1 where Performa_Qty=0






--SELECT POS.SubId,POS.POMainSlno,POS.ItemId,POS.ItemCode,POS.Quantity,POS.Performa_Qty,POS.LocationId 
--,POM.SupplierId,POM.DepartmentId,POM.OrderDate,POM.ShippingDate,POM.CurrencyRate,POM.UserId,POS.Rate
--,POS.FCRate,POm.CurrencyId
--INTO #POTBL FROM Inv_PurchaseOrderSub POS
--LEFT OUTER JOIN Inv_PurchaseOrderMain POM ON POM.OrderNo=POS.POMainSlno AND POM.DepartmentId=POS.DeptId AND POM.DelFlag=1
--WHERE POS.ItemId=@ItemId AND POS.DelFlag=1 and POM.DelFlag=1

--SELECT PS.SubId,PS.InvPPSlno,PS.ItemId,PS.ItemCode,PS.Quantity,PS.PPMrv_qty,PS.LocationId,PS.PO_SubId ,PM.SupplierId,POS.POMainSlno AS OrderNo
--INTO #PPTBL FROM Inv_PerformaSub PS 
--LEFT OUTER JOIN Inv_PerformaMain PM ON PM.PPNo=PS.InvPPSlno AND PM.DepartmentId=PS.DeptId AND PM.DelFlag=1
--LEFT OUTER JOIN Inv_PurchaseOrderSub POS ON PS.PO_SubId=POS.SubId
--WHERE PS.ItemId=@ItemId AND PS.DelFlag=1 and pm.DelFlag=1

--SELECT OrderNo,SupplierId, InvPPSlno = 
--    STUFF((SELECT ', ' + CONVERT(VARCHAR(100),InvPPSlno)
--           FROM #PPTBL b 
--           WHERE b.OrderNo = a.OrderNo 
--          FOR XML PATH('')), 1, 2, '')
--INTO #PPNO FROM #PPTBL a
--GROUP BY OrderNo,SupplierId

--SELECT MS.SubId,MS.InvMRVSlno,MS.ItemId,MS.ItemCode,MS.Quantity,MS.MPurchase_Qty,LocationId,MS.Performa_No,MS.Performa_SubId ,MM.SupplierId
--INTO #MRVTBL FROM Inv_MRVSub MS 
--LEFT OUTER JOIN Inv_MRVMain MM ON MM.MRVNo=MS.InvMRVSlno AND MM.DepartmentId=MS.DeptId AND MM.DelFlag=1
--WHERE MS.ItemId=@ItemId AND MS.DelFlag=1


--SELECT PIS.SubId,PIS.InvMainSlno,PIS.ItemId,PIS.ItemCode,PIS.Quantity,PIS.LocationId,PIS.MRV_No,PIS.MRV_SubTbl_Id ,PIM.SupplierId
--INTO #PITBL FROM Inv_PurchaseSub PIS
--LEFT OUTER JOIN Inv_PurchaseMain PIM ON PIM.SlNo=PIS.InvMainSlno AND PIM.DepartmentId=PIS.DeptId AND PIM.DelFlag=1
--WHERE PIS.ItemId=@ItemId AND PIS.DelFlag=1 

--SELECT SUM(ISNULL(PPMrv_qty,0)) as PPMrv_qty,OrderNo,SupplierId INTO #MRVQTY FROM   #PPTBL group by OrderNo,SupplierId;
--SELECT SUM(ISNULL(MPurchase_Qty,0)) AS MPurchase_Qty,Performa_No,Performa_SubId,SupplierId,ItemId  INTO #PIQTY FROM   #MRVTBL GROUP  BY Performa_No,Performa_SubId,SupplierId,ItemId;--SELECT * FROM   #PITBL

--SELECT OrderNo,#PPTBL.SupplierId,ISNULL(MPurchase_Qty,0) AS MPurchase_Qty INTO #PIFINAL FROM #PPTBL
--LEFT OUTER JOIN #PIQTY ON Performa_SubId=SubId AND #PPTBL.SupplierId=#PIQTY.SupplierId
----GROUP BY  OrderNo,#PPTBL.SupplierId

--DECLARE @TBLFINAL AS TABLE (ROWID INT IDENTITY(1,1),SuppId NUMERIC(18,0),ODNo NUMERIC(18,0),DeptId INT,ItemId NUMERIC(18,0),
--PO_QTY NUMERIC(18,0),PPQTY NUMERIC(18,0),ODDate VARCHAR(30),ArrivalDate VARCHAR(30),FCCost NUMERIC(18,3),Currency VARCHAR(50),
--MRVQTY NUMERIC(18,0),PI_QTY NUMERIC(18,0),POBALANCE NUMERIC(18,0),INTRANSIT_QTY NUMERIC(18,0),LocationId NUMERIC(18,0),
--CurrencyRate NUMERIC(18,8),UserId NUMERIC(18,0),Rate NUMERIC(18,3))


--INSERT INTO @TBLFINAL
--SELECT SupplierId,POMainSlno,DepartmentId,ItemId,Quantity,Performa_Qty,OrderDate,ShippingDate,FCRate,CurrencyName,0,0,0,0,LocationId,#POTBL.CurrencyRate,UserId,Rate
--FROM #POTBL
--LEFT OUTER JOIN Mst_Currency ON #POTBL.CurrencyId=Mst_Currency.Id;


--UPDATE @TBLFINAL SET MRVQty=ISNULL(PPMrv_qty,0) FROM #MRVQTY WHERE SuppId=SupplierId AND ODNo=OrderNo
--UPDATE @TBLFINAL SET PI_QTY=ISNULL(MPurchase_Qty,0) FROM #PIFINAL WHERE SuppId=SupplierId AND ODNo=OrderNo
--UPDATE @TBLFINAL SET POBALANCE=ISNULL(PO_QTY,0)-ISNULL(PI_QTY,0)

--DELETE FROM @TBLFINAL WHERE POBALANCE<=0

--UPDATE @TBLFINAL SET INTRANSIT_QTY=ISNULL(PPQTY,0)-ISNULL(PI_QTY,0)


--SELECT ODNo AS OrderNo,ODDate AS OrderDate,CustName,FT.DeptId,SuppId,POBALANCE AS PendingQty,
--PO_QTY AS Quantity,INTRANSIT_QTY AS Performa_Qty,PI_QTY AS ReceivedQty,ArrivalDate AS ShipDate,FCCost AS FCRate,Currency AS CurrencyName,
--FT.LocationId,Rate,CurrencyRate,FT.UserId,LocationName,DepartmentCode,U.Name,ISNULL(#PPNO.InvPPSlno,'')AS InvPPSlno
--into #dd1 FROM @TBLFINAL AS FT
--LEFT OUTER JOIN Mst_CustomerMain ON FT.SuppId=CustId
--LEFT OUTER JOIN Mst_Location ON FT.LocationId=Mst_Location.LocationId
--LEFT OUTER JOIN Mst_Department D ON FT.DeptId=D.DepartmentId
--LEFT OUTER JOIN Mst_Users U ON FT.UserId=U.UserId
--LEFT OUTER JOIN #PPNO ON OrderNo=ODNo and OrderNo is not null
--where CONVERT(datetime, ODDate,103)> CONVERT(datetime,'10/02/2020',103)
--ORDER BY ODNo
----update #dd1 set ShipDate=''
--update #dd1 set ShipDate=Inv_PerformaMain.ShipDate from  Inv_PerformaMain where CONVERT(varchar(20), PPNo)=LEFT(CONVERT(varchar(20),InvPPSlno),3)

--select * from #dd1





--select * from Inv_PurchaseOrderSub where POMainSlno=168 and ItemId=1677


--select Inv_PerformaSub.SubId, LPO_No OrderNo, OrderDate,custname CustName,0 DeptId, 0 SuppId , 0 PendingQty,Inv_PerformaMain.ShipDate ShipDate,CurrencyName CurrencyName,Inv_PurchaseOrderSub.FCRate FCRate,invppslno InvPPSlno,Inv_PurchaseOrderSub.Quantity 'Quantity' ,Inv_PerformaSub.Quantity 'Performa_Qty', convert(numeric(18,0),0) 'MRV',convert(numeric(18,0),0) 'PUQty', convert(numeric(18,0),0) 'MRVSUBID',0 ReceivedQty,0 LocationId,0 Rate,0 CurrencyRate,0	UserId,0	LocationName,0	DepartmentCode,0	Name
--into #dd1 from Inv_PerformaSub inner join Inv_PerformaMain on InvPPSlno=PPNo 
--inner join Mst_CustomerMain on Mst_CustomerMain.CustId=Inv_PerformaMain.SupplierId
--inner join Mst_Currency on Mst_Currency.Id=Inv_PerformaMain.CurrencyId
--inner join Inv_PurchaseOrderSub on Inv_PurchaseOrderSub.ItemId=Inv_PerformaSub.ItemId and convert(varchar(20), LPO_No)=convert(varchar(20),POMainSlno)

--where Inv_PerformaSub.ItemId=@ItemId
--and Inv_PerformaMain.DelFlag=1 and  Inv_PerformaSub.DelFlag=1
--and Inv_PurchaseOrderSub.DelFlag=1
----and CONVERT(datetime, OrderDate,103)> CONVERT(datetime,'10/02/2020',103)



--select Inv_PerformaSub.SubId, LPO_No OrderNo,'' OrderDate,custname CustName,0 DeptId, 0 SuppId , 0 PendingQty,Inv_PerformaMain.ShipDate ShipDate,CurrencyName CurrencyName,Mst_Currency.CurrencyRate FCRate,invppslno InvPPSlno,Inv_PurchaseOrderSub.Quantity 'Quantity' ,Inv_PerformaSub.Quantity 'Performa_Qty', convert(numeric(18,0),0) 'MRV',convert(numeric(18,0),0) 'PUQty', convert(numeric(18,0),0) 'MRVSUBID',0 ReceivedQty,0 LocationId,0 Rate,0 CurrencyRate,0	UserId,0	LocationName,0	DepartmentCode,0	Name
-- into #dd1 from Inv_PerformaSub inner join Inv_PerformaMain on InvPPSlno=PPNo 
--inner join Mst_CustomerMain on Mst_CustomerMain.CustId=Inv_PerformaMain.SupplierId
--inner join Mst_Currency on Mst_Currency.Id=Inv_PerformaMain.CurrencyId
--inner join Inv_PurchaseOrderSub on Inv_PurchaseOrderSub.ItemId=Inv_PerformaSub.ItemId and LPO_No=POMainSlno

--where Inv_PerformaSub.ItemId=@ItemId
--and Inv_PerformaMain.DelFlag=1 and  Inv_PerformaSub.DelFlag=1 --and Inv_PurchaseOrderMain.DelFlag=1
--and CONVERT(datetime, OrderDate,103)> CONVERT(datetime,'10/02/2020',103)

--select * from Inv_PurchaseOrderMain


--update #dd1 set MRV=Inv_MRVSub.Quantity,MRVSUBID=Inv_MRVSub.SubId from Inv_MRVSub where #dd1.SubId=Performa_SubId





--select CONVERT(numeric(18,0),0)'PPSubID', Inv_PurchaseOrderSub.SubId, POMainSlno OrderNo,OrderDate,custname CustName,0 DeptId, 0 SuppId , 0 PendingQty,Inv_PerformaMain.ShipDate ShipDate,CurrencyName CurrencyName,Inv_PurchaseOrderSub.FCRate FCRate, Inv_PerformaMain.PPNo InvPPSlno,Inv_PurchaseOrderSub.Quantity 'Quantity' ,0 'Performa_Qty', convert(numeric(18,0),0) 'MRV',convert(numeric(18,0),0) 'PUQty', convert(numeric(18,0),0) 'MRVSUBID',0 ReceivedQty,0 LocationId,0 Rate,0 CurrencyRate,0	UserId,0	LocationName,0	DepartmentCode,0	Name
-- into #dd1 from Inv_PurchaseOrderSub 
--inner join Inv_PurchaseOrderMain on OrderNo=POMainSlno and Inv_PurchaseOrderMain.DelFlag=1
--left outer join Inv_PerformaMain on LEFT(convert(varchar(20), LPO_No),3)=convert(varchar(20),POMainSlno) and Inv_PerformaMain.DelFlag=1
----inner join Inv_PerformaSub on Inv_PerformaSub.InvPPSlno=Inv_PerformaMain.PPNo
--inner join Mst_CustomerMain on Mst_CustomerMain.CustId=Inv_PurchaseOrderMain.SupplierId
--inner join Mst_Currency on Mst_Currency.Id=Inv_PurchaseOrderMain.CurrencyId
--where Inv_PurchaseOrderSub.ItemId=@ItemId and Inv_PurchaseOrderSub.DelFlag=1

--update #dd1 set Name=1 from Inv_PerformaSub where ItemId=7545 and #dd1.InvPPSlno=Inv_PerformaSub.InvPPSlno
--delete #dd1 where name=0

--update #dd1 set Performa_Qty=Inv_PerformaSub.Quantity,PPSubID=Inv_PerformaSub.SubId
--from Inv_PerformaMain inner join Inv_PerformaSub on PPNo=InvPPSlno where #dd1.InvPPSlno=Inv_PerformaMain.PPNo and #dd1.SubId=PO_SubId and Inv_PerformaSub.DelFlag=1

--update #dd1 set MRV=Inv_MRVSub.Quantity,MRVSUBID=Inv_MRVSub.SubId from Inv_MRVSub where #dd1.PPSubID=Performa_SubId


--select * from #dd1 where Quantity>MRV 


--select * from #dd

--select * from Inv_PerformaMain where PPNo in (166, 167, 165)





end
else
begin

SELECT POS.SubId,POS.POMainSlno,POS.ItemId,POS.ItemCode,POS.Quantity,POS.Performa_Qty,POS.LocationId 
,POM.SupplierId,POM.DepartmentId,POM.OrderDate,POM.ShippingDate,POM.CurrencyRate,POM.UserId,POS.Rate
,POS.FCRate,POm.CurrencyId
INTO #POTBL1 FROM Inv_PurchaseOrderSub POS
LEFT OUTER JOIN Inv_PurchaseOrderMain POM ON POM.OrderNo=POS.POMainSlno AND POM.DepartmentId=POS.DeptId AND POM.DelFlag=1
WHERE   POS.DelFlag=1

SELECT PS.SubId,PS.InvPPSlno,PS.ItemId,PS.ItemCode,PS.Quantity,PS.PPMrv_qty,PS.LocationId,PS.PO_SubId ,PM.SupplierId,POS.POMainSlno AS OrderNo
INTO #PPTBL1 FROM Inv_PerformaSub PS 
LEFT OUTER JOIN Inv_PerformaMain PM ON PM.PPNo=PS.InvPPSlno AND PM.DepartmentId=PS.DeptId AND PM.DelFlag=1
LEFT OUTER JOIN Inv_PurchaseOrderSub POS ON PS.PO_SubId=POS.SubId
WHERE  PS.DelFlag=1

SELECT OrderNo,SupplierId, InvPPSlno = 
    STUFF((SELECT ', ' + CONVERT(VARCHAR(100),InvPPSlno)
           FROM #PPTBL1 b 
           WHERE b.OrderNo = a.OrderNo 
          FOR XML PATH('')), 1, 2, '')
INTO #PPNO1 FROM #PPTBL1 a
GROUP BY OrderNo,SupplierId

SELECT MS.SubId,MS.InvMRVSlno,MS.ItemId,MS.ItemCode,MS.Quantity,MS.MPurchase_Qty,LocationId,MS.Performa_No,MS.Performa_SubId ,MM.SupplierId
INTO #MRVTBL1 FROM Inv_MRVSub MS 
LEFT OUTER JOIN Inv_MRVMain MM ON MM.MRVNo=MS.InvMRVSlno AND MM.DepartmentId=MS.DeptId AND MM.DelFlag=1
WHERE   MS.DelFlag=1


SELECT PIS.SubId,PIS.InvMainSlno,PIS.ItemId,PIS.ItemCode,PIS.Quantity,PIS.LocationId,PIS.MRV_No,PIS.MRV_SubTbl_Id ,PIM.SupplierId
INTO #PITBL1 FROM Inv_PurchaseSub PIS
LEFT OUTER JOIN Inv_PurchaseMain PIM ON PIM.SlNo=PIS.InvMainSlno AND PIM.DepartmentId=PIS.DeptId AND PIM.DelFlag=1
WHERE  PIS.DelFlag=1 

SELECT SUM(ISNULL(PPMrv_qty,0)) as PPMrv_qty,OrderNo,SupplierId INTO #MRVQTY1 FROM   #PPTBL1 group by OrderNo,SupplierId;
SELECT SUM(ISNULL(MPurchase_Qty,0)) AS MPurchase_Qty,Performa_No,Performa_SubId,SupplierId,ItemId  INTO #PIQTY1 FROM   #MRVTBL1 GROUP  BY Performa_No,Performa_SubId,SupplierId,ItemId;--SELECT * FROM   #PITBL

SELECT OrderNo,#PPTBL1.SupplierId,SUM(ISNULL(MPurchase_Qty,0)) AS MPurchase_Qty INTO #PIFINAL1 FROM #PPTBL1
LEFT OUTER JOIN #PIQTY1 ON Performa_SubId=SubId AND #PPTBL1.SupplierId=#PIQTY1.SupplierId
GROUP BY  OrderNo,#PPTBL1.SupplierId

DECLARE @TBLFINAL1 AS TABLE (ROWID INT IDENTITY(1,1),SuppId NUMERIC(18,0),ODNo NUMERIC(18,0),DeptId INT,ItemId NUMERIC(18,0),
PO_QTY NUMERIC(18,0),PPQTY NUMERIC(18,0),ODDate VARCHAR(30),ArrivalDate VARCHAR(30),FCCost NUMERIC(18,3),Currency VARCHAR(50),
MRVQTY NUMERIC(18,0),PI_QTY NUMERIC(18,0),POBALANCE NUMERIC(18,0),INTRANSIT_QTY NUMERIC(18,0),LocationId NUMERIC(18,0),
CurrencyRate NUMERIC(18,8),UserId NUMERIC(18,0),Rate NUMERIC(18,3))


INSERT INTO @TBLFINAL1
SELECT SupplierId,POMainSlno,DepartmentId,ItemId,Quantity,Performa_Qty,OrderDate,ShippingDate,FCRate,CurrencyName,0,0,0,0,LocationId,#POTBL1.CurrencyRate,UserId,Rate
FROM #POTBL1
LEFT OUTER JOIN Mst_Currency ON #POTBL1.CurrencyId=Mst_Currency.Id;


UPDATE @TBLFINAL1 SET MRVQty=ISNULL(PPMrv_qty,0) FROM #MRVQTY1 WHERE SuppId=SupplierId AND ODNo=OrderNo
UPDATE @TBLFINAL1 SET PI_QTY=ISNULL(MPurchase_Qty,0) FROM #PIFINAL1 WHERE SuppId=SupplierId AND ODNo=OrderNo
UPDATE @TBLFINAL1 SET POBALANCE=ISNULL(PO_QTY,0)-ISNULL(PI_QTY,0)

DELETE FROM @TBLFINAL1 WHERE POBALANCE<=0

UPDATE @TBLFINAL1 SET INTRANSIT_QTY=ISNULL(PPQTY,0)-ISNULL(PI_QTY,0)


SELECT ODNo AS OrderNo,ODDate AS OrderDate,CustName,FT.DeptId,SuppId,POBALANCE AS PendingQty,
PO_QTY AS Quantity,INTRANSIT_QTY AS Performa_Qty,PI_QTY AS ReceivedQty,ArrivalDate AS ShipDate,FCCost AS FCRate,Currency AS CurrencyName,
FT.LocationId,Rate,CurrencyRate,FT.UserId,LocationName,DepartmentCode,U.Name,ISNULL(#PPNO1.InvPPSlno,'')AS InvPPSlno,ItemId
 into #dd FROM @TBLFINAL1 AS FT
LEFT OUTER JOIN @TBLDUMMYSUP ON FT.SuppId=CustId
LEFT OUTER JOIN Mst_Location ON FT.LocationId=Mst_Location.LocationId
LEFT OUTER JOIN Mst_Department D ON FT.DeptId=D.DepartmentId
LEFT OUTER JOIN Mst_Users U ON FT.UserId=U.UserId
LEFT OUTER JOIN #PPNO1 ON OrderNo=ODNo
where CONVERT(datetime, ODDate,103)> CONVERT(datetime,'10/02/2020',103)
ORDER BY ODNo

update #dd set LocationName=ItemCode,DepartmentCode=Description from Mst_Item where #dd.ItemId=Mst_Item.ItemId

update #dd set InvPPSlno=substring(InvPPSlno,0,CHARINDEX(',', InvPPSlno))
update #dd set Name= '' 

update #dd set Name= PPDate from Inv_PerformaMain where CONVERT(varchar(10), InvPPSlno)=convert(varchar(10),PPNo)



if(@Type=0)
begin
select distinct * from #dd 
end
else
begin
select distinct * from #dd where SuppId=@Type
end





end


 
--if(@Type=0)
--begin
--SELECT DISTINCT top 50  OrderNo,ItemId,ItemCode,convert(datetime,OrderDate,103)as ODate,OrderDate,c.CustName,
--CurrencyName,CUR.CurrencyRate,Rate,FCRate,LocationName,DepartmentCode,Name,
--0 Quantity,0 AS Performa_Qty ,0 AS MRV_Qty,0 AS Purchase_Qty,0 AS ReceivedQty,0 AS PendingQty,'01/01/2019' as ShipDate
--INTO #tbl FROM Inv_PurchaseOrderSub
--LEFT JOIN Inv_PurchaseOrderMain  ON POMainSlno=OrderNo AND DeptId=DepartmentId 
--left outer join Mst_Department D on Inv_PurchaseOrderMain.DepartmentId=D.DepartmentId
--left outer join Mst_Location L on Inv_PurchaseOrderSub.LocationId=L.LocationId
--left outer join Mst_Users on Inv_PurchaseOrderMain.UserId=Mst_Users.UserId
--left outer join Mst_CustomerMain C on Inv_PurchaseOrderMain.SupplierId=C.CustId
--left outer join Mst_Currency CUR on Inv_PurchaseOrderMain.CurrencyId=CUR.Id
--WHERE ItemId=@ItemId AND Inv_PurchaseOrderMain.DelFlag=1 AND Inv_PurchaseOrderSub.DelFlag=1  

--SELECT OrderNo,isnull(sum(Quantity),0) Quantity into #PItbl  FROM Inv_PurchaseOrderSub 
--LEFT JOIN Inv_PurchaseOrderMain  ON Inv_PurchaseOrderSub.POMainSlno=Inv_PurchaseOrderMain.OrderNo 
--AND Inv_PurchaseOrderSub.DeptId=Inv_PurchaseOrderMain.DepartmentId 
--WHERE ItemId=@ItemId and Inv_PurchaseOrderSub.DelFlag=1 and Inv_PurchaseOrderMain.DelFlag=1 and Inv_PurchaseOrderMain.DelFlag=1 group by OrderNo

--UPDATE #tbl SET Quantity =isnull((select Quantity from #PItbl where #tbl.OrderNo=#PItbl.OrderNo),0)


--SELECT LPO_No,isnull(SUM(Quantity),0) Quantity,ShipDate into #PPtbl FROM Inv_PerformaSub  
--LEFT JOIN Inv_PerformaMain  ON Inv_PerformaSub.InvPPSlno=Inv_PerformaMain.PPNo 
--LEFT JOIN Inv_PurchaseOrderMain  ON Inv_PerformaMain.LPO_No=Inv_PurchaseOrderMain.OrderNo   and Inv_PurchaseOrderMain.DelFlag=1
-- WHERE ItemId=@ItemId and Inv_PerformaSub.DelFlag=1 and Inv_PerformaMain.DelFlag=1  group by LPO_No,ShipDate

--UPDATE #tbl SET Performa_Qty=isnull((select Quantity from #PPtbl where #tbl.OrderNo=#PPtbl.LPO_No),0)-isnull((SELECT isnull(SUM(Quantity),0) FROM Inv_PurchaseSub 
--LEFT JOIN Inv_MRVMain  ON Inv_PurchaseSub.MRV_No=Inv_MRVMain.MRVNo 
--WHERE ItemId=@ItemId and Inv_PurchaseSub.DelFlag=1 and Inv_MRVMain.DelFlag=1),0)
--UPDATE #tbl SET ShipDate=''
--UPDATE #tbl SET ShipDate=isnull((select ShipDate from #PPtbl where #tbl.OrderNo=#PPtbl.LPO_No),'')


--UPDATE #tbl SET MRV_Qty=(SELECT isnull(SUM(Quantity),0) FROM Inv_MRVSub 
--LEFT JOIN Inv_MRVMain  ON Inv_MRVSub.InvMRVSlno=Inv_MRVMain.MRVNo 
--LEFT JOIN Inv_PerformaMain  ON Inv_MRVMain.Performa_No=Inv_PerformaMain.PPNo and Inv_PerformaMain.DelFlag=1
--WHERE ItemId=@ItemId and Inv_MRVSub.DelFlag=1 and Inv_MRVMain.DelFlag=1 )


--UPDATE #tbl SET Purchase_Qty=(SELECT isnull(SUM(Quantity),0) FROM Inv_PurchaseSub 
--LEFT JOIN Inv_MRVMain  ON Inv_PurchaseSub.MRV_No=Inv_MRVMain.MRVNo and Inv_MRVMain.DelFlag=1
--WHERE ItemId=@ItemId and Inv_PurchaseSub.DelFlag=1 )




--UPDATE #tbl SET ReceivedQty=(SELECT isnull(SUM(Quantity),0) FROM Inv_PurchaseSub 
--LEFT JOIN Inv_MRVMain  ON Inv_PurchaseSub.MRV_No=Inv_MRVMain.MRVNo and Inv_MRVMain.DelFlag=1
--WHERE ItemId=@ItemId and Inv_PurchaseSub.DelFlag=1 )

--UPDATE #tbl SET PendingQty=(select Quantity from #PItbl where #tbl.OrderNo=#PItbl.OrderNo)-(SELECT isnull(SUM(Quantity),0) FROM Inv_PurchaseSub 
--LEFT JOIN Inv_MRVMain  ON Inv_PurchaseSub.MRV_No=Inv_MRVMain.MRVNo  and Inv_MRVMain.DelFlag=1
--WHERE ItemId=@ItemId and Inv_PurchaseSub.DelFlag=1)


--select * from #tbl

--end

--else
--begin
--SELECT DISTINCT  OrderNo,ItemId,ItemCode,convert(datetime,OrderDate,103)as ODate,OrderDate,c.CustName,
--CurrencyName,CUR.CurrencyRate,Rate,FCRate,LocationName,DepartmentCode,Name,
--0 Quantity,0 AS Performa_Qty ,0 AS MRV_Qty,0 AS Purchase_Qty,0 AS ReceivedQty,0 AS PendingQty,'01/01/2019' as ShipDate
--INTO #tbl1 FROM Inv_PurchaseOrderSub
--LEFT JOIN Inv_PurchaseOrderMain  ON POMainSlno=OrderNo AND DeptId=DepartmentId 
--left outer join Mst_Department D on Inv_PurchaseOrderMain.DepartmentId=D.DepartmentId
--left outer join Mst_Location L on Inv_PurchaseOrderSub.LocationId=L.LocationId
--left outer join Mst_Users on Inv_PurchaseOrderMain.UserId=Mst_Users.UserId
--left outer join Mst_CustomerMain C on Inv_PurchaseOrderMain.SupplierId=C.CustId
--left outer join Mst_Currency CUR on Inv_PurchaseOrderMain.CurrencyId=CUR.Id
--WHERE ItemId=@ItemId AND Inv_PurchaseOrderMain.DelFlag=1 AND Inv_PurchaseOrderSub.DelFlag=1  

--SELECT OrderNo,isnull(sum(Quantity),0) Quantity into #PItbl1  FROM Inv_PurchaseOrderSub 
--LEFT JOIN Inv_PurchaseOrderMain  ON Inv_PurchaseOrderSub.POMainSlno=Inv_PurchaseOrderMain.OrderNo 
--AND Inv_PurchaseOrderSub.DeptId=Inv_PurchaseOrderMain.DepartmentId 
--WHERE ItemId=@ItemId and Inv_PurchaseOrderSub.DelFlag=1 and Inv_PurchaseOrderMain.DelFlag=1 group by OrderNo

--UPDATE #tbl1 SET Quantity =isnull((select Quantity from #PItbl1 where #tbl1.OrderNo=#PItbl1.OrderNo),0)


--SELECT LPO_No,isnull(SUM(Quantity),0) Quantity,ShipDate into #PPtbl1 FROM Inv_PerformaSub  
--LEFT JOIN Inv_PerformaMain  ON Inv_PerformaSub.InvPPSlno=Inv_PerformaMain.PPNo 
--LEFT JOIN Inv_PurchaseOrderMain  ON Inv_PerformaMain.LPO_No=Inv_PurchaseOrderMain.OrderNo   and Inv_PurchaseOrderMain.DelFlag=1
-- WHERE ItemId=@ItemId and Inv_PerformaSub.DelFlag=1 and Inv_PerformaMain.DelFlag=1 group by LPO_No,ShipDate

--UPDATE #tbl SET Performa_Qty=isnull((select Quantity from #PPtbl where #tbl.OrderNo=#PPtbl.LPO_No),0)-isnull((SELECT isnull(SUM(Quantity),0) FROM Inv_PurchaseSub 
--LEFT JOIN Inv_MRVMain  ON Inv_PurchaseSub.MRV_No=Inv_MRVMain.MRVNo 
--WHERE ItemId=@ItemId and Inv_PurchaseSub.DelFlag=1),0)
--UPDATE #tbl SET ShipDate=''
--UPDATE #tbl SET ShipDate=isnull((select ShipDate from #PPtbl where #tbl.OrderNo=#PPtbl.LPO_No),'')


--UPDATE #tbl1 SET MRV_Qty=(SELECT isnull(SUM(Quantity),0) FROM Inv_MRVSub 
--LEFT JOIN Inv_MRVMain  ON Inv_MRVSub.InvMRVSlno=Inv_MRVMain.MRVNo 
--LEFT JOIN Inv_PerformaMain  ON Inv_MRVMain.Performa_No=Inv_PerformaMain.PPNo and Inv_PerformaMain.DelFlag=1
--WHERE ItemId=@ItemId and Inv_MRVSub.DelFlag=1 and Inv_MRVMain.DelFlag=1 )


--UPDATE #tbl1 SET Purchase_Qty=(SELECT isnull(SUM(Quantity),0) FROM Inv_PurchaseSub 
--LEFT JOIN Inv_MRVMain  ON Inv_PurchaseSub.MRV_No=Inv_MRVMain.MRVNo  and Inv_MRVMain.DelFlag=1
--WHERE ItemId=@ItemId and Inv_PurchaseSub.DelFlag=1)



--UPDATE #tbl1 SET ReceivedQty=(SELECT isnull(SUM(Quantity),0) FROM Inv_PurchaseSub 
--LEFT JOIN Inv_MRVMain  ON Inv_PurchaseSub.MRV_No=Inv_MRVMain.MRVNo  and Inv_MRVMain.DelFlag=1
--WHERE ItemId=@ItemId and Inv_PurchaseSub.DelFlag=1)

--UPDATE #tbl1 SET PendingQty=(select Quantity from #PItbl1 where #tbl1.OrderNo=#PItbl1.OrderNo)-(SELECT isnull(SUM(Quantity),0) FROM Inv_PurchaseSub 
--LEFT JOIN Inv_MRVMain  ON Inv_PurchaseSub.MRV_No=Inv_MRVMain.MRVNo  and Inv_MRVMain.DelFlag=1
--WHERE ItemId=@ItemId and Inv_PurchaseSub.DelFlag=1)


--select * from #tbl

--end

END


GO


