Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

(Back to D-SL Sales AR Module SQL Posted to Wiki Listing - D-SL Sales AR Module SQL Posted to Wiki  )

TYPE: Frequently Requested SQL

AUTHOR NAME: Neetu Dhami

PURPOSE AND BACKGROUND:  This SQL was created in 2007A SP 01 PL 07. Many of the Clients those are using FIFO valuation method require the information that which FIFO layer is used at particular invoice.The following is a sample which can be used  as a basic outline and modified per your requirements to display the Open FIFO layers of items managed by batches ,

Copy and Paste this SQL:

--D-SL Sales and Buy FIFO Price for Batch managed Items Ver 1 ND 2011 05 20

--DESCRIPTION:  Clients those are using FIFO valuation method require the information that which FIFO layer is used at particular invoice. The following is a sample which can be used  as a basic outline and modified per your requirements.

--AUTHOR(s):
--Version 1 Neetu Dhami  20 May 2011

SELECT  T0.LineNum,T0.DocEntry,
   T0.VisOrder,
   T1.DocDate,
   T0.Dscription,
   T1.DocNum,
   T6.CardName as [Supplier],
   T1.CardName,
   T1.ShipToCode,
   T2.Quantity,
   T9.Price as [Buy Price], 

   (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0 then 1.0 else T0.Rate end)) as [Sell Price],
   T2.Quantity * T9.Price as [Total Buy Price],
   T2.Quantity * (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0 then 1.0 else T0.Rate end)) as [Total Sell Price], 
   T2.Quantity * (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0 then 1.0 else T0.Rate end)) - T2.Quantity * T9.Price as [Profit],
  CASE when T2.Quantity * (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0 then 1.0 else T0.Rate end)) = 0 then 0.0 else (T2.Quantity *

 (T0.Price / (CASE   IsNULL(T0.Rate, 0) when 0.0 then 1.0 else T0.Rate end)) - T2.Quantity * T9.Price)/
 (T2.Quantity * (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0 then 1.0  else T0.Rate end))) end as [Profit Per],
  T4.ItmsGrpNam as [Category]

FROM dbo.INV1 T0
          INNER JOIN dbo.OINV T1 ON T0.DocEntry = T1.DocEntry
          INNER JOIN dbo.IBT1 T2 ON T2.BaseType = T1.ObjType and
         T2.BaseEntry = T0.DocEntry and T2.BaseLinNum=T0.LineNum
         INNER JOIN dbo.OITM T3 ON T0.ItemCode = T3.ItemCode
          INNER JOIN dbo.OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod
           INNER JOIN dbo.OIBT T5 ON T5.ItemCode = T0.ItemCode and
          T2.BatchNum = T5.BatchNum and T5.WhsCode = T0.WhsCode

          INNER JOIN (select MIN(TransNum) as [TransNum], TX.ItemCode, TX.BatchNum
                                from OIBT TX
                                   INNER JOIN OINM TX1 ON TX.BaseType = TX1.TransType and TX.BaseNum = TX1.BASE_REF
                                   and TX.BaseLinNum = TX1.DocLineNum and TX.ItemCode=TX1.ItemCode and TX.WhsCode = TX1.Warehouse
                               group by TX.ItemCode, TX.BatchNum) TX6 ON TX6.ItemCode = T0.ItemCode and TX6.BatchNum = T2.BatchNum

           INNER JOIN dbo.OINM T6 ON TX6.TransNum=T6.TransNum  and T6.InQty<>0
          LEFT OUTER JOIN dbo.OPDN T7 ON T7.ObjType = T5.BaseType and T7.DocEntry = T5.BaseEntry
          LEFT OUTER JOIN dbo.OPCH T8 ON T8.ObjType = T5.BaseType and T8.DocEntry = T5.BaseEntry 
          INNER JOIN (select SUM(TX.CalcPrice*TX.OutQty)/SUM(TX.OutQty) as [Price], TX.BASE_REF, TX.TransType, TX.DocLineNum
                                from OINM TX where TX.TransType=13            
                             group by TX.BASE_REF, TX.TransType, TX.DocLineNum) T9 ON T9.BASE_REF=T1.DocNum and T9.TransType=T1.ObjType and T9.DocLineNum=T0.LineNum

WHERE T1.DocNum >='[%0]' and T1.DocNum <='[%1]'

  • No labels