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 * not managed by batches*.

Copy and Paste this SQL:

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

--DESCRIPTION:  The following is a sample which can be used  as a basic outline to get Sale/Purchase FIFO price for items not managed by batchs 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,
  T5.CardName as [Supplier],
  T1.CardName,
  T1.ShipToCode,
  T4.OutQty,
  T4.CalcPrice as [Buy Price],
  (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0 then 1.0

else T0.Rate end)) as [Sell Price],
  T4.OutQty* (T4.CalcPrice) as [Total Buy Price],
  T4.OutQty * (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0

then 1.0 else T0.Rate end)) as [Total Sell Price],
  T4.OutQty * (T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0

then 1.0 else T0.Rate end)) - T4.OutQty* (T4.CalcPrice) as [Profit],
  CASE when T4.OutQty * (T0.Price / (CASE IsNULL(T0.Rate,

0) when 0.0 then 1.0 else T0.Rate end)) = 0 then 0.0 else (T4.OutQty *

(T0.Price / (CASE IsNULL(T0.Rate, 0) when 0.0 then 1.0 else T0.Rate end))

- T4.OutQty * (T4.CalcPrice))/ (T4.OutQty * (T0.Price / (CASE IsNULL

(T0.Rate, 0) when 0.0 then 1.0 else T0.Rate end))) end as [Profit Per],
  T3.ItmsGrpNam as [Category]
FROM dbo.INV1 T0
 INNER JOIN dbo.OINV T1 ON T0.DocEntry = T1.DocEntry
 INNER JOIN dbo.OITM T2 ON T0.ItemCode = T2.ItemCode
 INNER JOIN dbo.OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod
 INNER JOIN dbo.OINM T4 ON T1.DocNum=T4.BASE_REF and T1.ObjType =

T4.TransType and T0.LineNum = T4.DocLineNum
 INNER JOIN dbo.OINM T5 ON T5.TransNum = (select MIN(TransNum)

FROM OINM
          

 where ItemCode=T0.ItemCode
          

 and Warehouse=T0.WhsCode
          

 and TransNum<T4.TransNum
          

 having SUM(InQty-OutQty)>0
          

 )
 LEFT OUTER JOIN dbo.OPDN T6 ON T6.ObjType = T5.TransType and

T6.DocNum = T5.BASE_REF
 LEFT OUTER JOIN dbo.OPCH T7 ON T7.ObjType = T5.TransType and

T7.DocNum  = T5.BASE_REF
WHERE IsNULL(T2.ManBtchNum, 'N')='N' and IsNULL(T2.ManSerNum, 'N')='N'
  and T1.DocNum >='[%0]' and T1.DocNum <='[%1]'

  • No labels

1 Comment

  1. Guest

    Thanks...

    It helped me lot..