picking adjust query

-- =============================================  

-- Author: Adeel Majeed  

-- Project : Based  

-- Create date: --  

-- Alter date : 14-09-18  

-- Description: Add the column and Multiply CurrencyRate Convert to ConversionRateofCurrency  

-- ===========================================================================================  

CREATE PROCEDURE PurchaseRequistionRemainingDtlGetByPurchaseRequistionId --82  

 @PurchaseRequistionId int  

AS  

BEGIN  

SELECT * FROM(  

 SELECT DISTINCT PRDTL.Id,  

 PRDTL.PurchaseRequistionId,  

 PRDTL.ProductId,  

 Product.ProductName,  

 ProGrpName,  

 (PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0)) AS Qty,  

 PRDTL.UnitPrice,  

 (PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice AS Price,  

 PRDTL.Discount,  

 --((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-ISNULL(PRDTL.DiscountAmount,0.00) AS Total,  

 CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-ISNULL(PRDTL.Discount,0)   

 ELSE ((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice) - ((((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * ISNULL(PRDTL.Discount,0))  

 END AS Total,  

 PRDTL.Remarks,  

 PRDTL.UnitName,  

 PurchaseRequistion.[Date],  

 PRDTL.[Status],  

 PRDTL.PoNo,  

 --DsicountQty,  

 (PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0)) AS DsicountQty,  

 --PRDTL.DiscountTotal,  

 --((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-ISNULL(PRDTL.DiscountAmount,0.00) AS DiscountTotal,  

 CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-ISNULL(PRDTL.DiscountAmount,0.00)  

 ELSE ((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice) - ((((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * ISNULL(PRDTL.Discount,0))  

 END AS DiscountTotal,  

 PRDTL.PartNo,  

 PRDTL.DiscountPercentage,  

 PRDTL.CurrencyId,  

 PRDTL.CurrencyRate,  

 PRDTL.DiscountFcAmount,  

 (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0)) * PRDTL.UnitPrice)*  

 (CASE WHEN cm.IsDivide=1 THEN 1/PRDTL.CurrencyRate ELSE PRDTL.CurrencyRate END))  

 -ISNULL(CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ISNULL( PRDTL.DiscountFcAmount,0)   

 ELSE (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * PRDTL.DiscountFc  

 END,0.00) AS AfterDiscountFcAmount,  

 PRDTL.DiscountFc,  

 PRDTL.FcUnitPrice,  

 (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0)) * PRDTL.UnitPrice)*  

 (CASE WHEN cm.IsDivide=1 THEN 1/PRDTL.CurrencyRate ELSE PRDTL.CurrencyRate END)) AS FcAmount,  

 Product.PageNumber,  

 Product.ItemRef,  

 Product.SerialNumber,  

 PRDTL.SaleOrderId,  

 PRDTL.PickDataID,  

 PRDTL.ConversionRateofCurrency,  

 PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0) AS ReturnQty,  

 (PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice AS ReturnTotal,  

 (CASE WHEN PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0) > 0 THEN 'True' ELSE 'False' END) AS ReturnStatus,  

 PRDTL.VATId,  

 PRDTL.VATPercentage,  

 --PRDTL.VATAmount,  

  

((((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ISNULL(PRDTL.Discount,0)   

 ELSE (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * PRDTL.Discount  

 END)/100)*PRDTL.VATPercentage  as VATAmount,  

  

((((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ISNULL(PRDTL.Discount,0)   

 ELSE (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * PRDTL.Discount   

 END)/100)*PRDTL.VATPercentage + (((PRDTL.Qty-isnull(sum(pqdtl.Qty),0))*PRDTL.UnitPrice)-CASE   

 WHEN PRDTL.DiscountPercentage = 0   

 THEN ISNULL(PRDTL.Discount,0)   

 ELSE (((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)/100) * PRDTL.Discount   

 END) AS AfterVATTotal,  

 cm.CurrencyName,  

 DiscountTaxMaster.Name AS VATName,  

 --PRDTL.AfterVATTotal,  

 --((PRDTL.Qty-ISNULL(SUM(pqdtl.Qty),0))*PRDTL.UnitPrice)-ISNULL(PRDTL.DiscountAmount,0.00)+ISNULL(PRDTL.VATAmount,0.00) AS AfterVATTotal,  

 PurchaseRequistion.VendorPriceListId,  

 PRDTL.unitID  

 ,PRDTL.NoOfStockInUnit  

 FROM dbo.PurchaseRequistionDtl PRDTL  

 LEFT JOIN PurchaseRequistion ON PurchaseRequistion.PurchaseRequistionId= PRDTL.id  

 LEFT JOIN ApprovalDocument ON ApprovalDocument.DocumentId=PurchaseRequistion.PurchaseRequistionId  

 LEFT JOIN PurchaseQuotationDtl pqdtl ON pqdtl.PickDataID = PRDTL.id and pqdtl.ProductId = PRDTL.ProductId  

 LEFT JOIN Product ON PRDTL.ProductId = Product.Id  

 LEFT JOIN ProductGroup ON Product.ProGrpId = ProductGroup.Id  

 LEFT JOIN CurrencyMaster cm ON cm.Id = PRDTL.CurrencyId  

 LEFT JOIN DiscountTaxMaster ON DiscountTaxMaster.Id = PRDTL.VATId  

WHERE PRDTL.PurchaseRequistionId = @PurchaseRequistionId  

-- and PRDTL.Status=1  

GROUP BY  

PRDTL.Id,PRDTL.PurchaseRequistionId,PRDTL.ProductId,Product.ProductName,ProGrpName,  

 PRDTL.Qty,PRDTL.UnitPrice,PRDTL.Discount,  

 PRDTL.Remarks,PRDTL.UnitName,PurchaseRequistion.[Date],PRDTL.[Status],  

 PRDTL.PoNo,DsicountQty,PRDTL.DiscountTotal,PRDTL.PartNo,PRDTL.DiscountPercentage,  

 PRDTL.CurrencyId,  

 PRDTL.CurrencyRate,  

 PRDTL.DiscountFcAmount,PRDTL.DiscountFc,  

 PRDTL.FcUnitPrice,  

 Product.PageNumber,  

 Product.ItemRef,  

 Product.SerialNumber ,  

 PRDTL.SaleOrderId,  

 PRDTL.PickDataID,PRDTL.Qty,  

 PRDTL.DiscountAmount,  

 PRDTL.ConversionRateofCurrency,  

 cm.IsDivide,  

 PRDTL.VATId,  

 PRDTL.VATPercentage,  

 PRDTL.VATAmount,  

 PRDTL.AfterVATTotal,  

 cm.CurrencyName,  

 DiscountTaxMaster.Name,  

 PurchaseRequistion.VendorPriceListId,  

 PRDTL.UNITID,  

 PRDTL.NoOfStockInUnit  

 ) AS Temptable  

 WHERE ReturnStatus='true'  

  

END