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: Monthly Special

AUTHOR NAME: Neetu Dhami

PURPOSE AND BACKGROUND:  This SQL was created in 2007A SP 01 PL 08.Finance People require report for Sales profitability based on back to back orders. Below Query shows linking in sales purchase transactions.The following is a sample which can be used  as a basic outline and modified per client requirements to display Sales Profitability .

Copy and Paste this SQL:

--D-SL  Calculate Sales profitability based on B2B Orders Ver1 ND  2011 10 11

--DESCRIPTION:  Finance People require report for Sales profitability based on back to back orders. The following is a sample which can be used  as a basic outline and modified per your requirements.

--AUTHOR(s):
--Version 1 Neetu Dhami  11 Oct. 2011

Select distinct  T0.DocDate as [Sales Inv Posting Date] ,T0.DocNum as [Sales Inv. No] ,T4.CardCode as [Sales Order Customer No.] ,T4.CardName as [Sales Order Customer Name]

                        ,T0.FatherCard as [Sales Invoice Billing Code],T1.ItemCode as [Sales Invoice Item No.] ,T1.Dscription as [Sales Invoice Item Description]
                        ,T1.AcctCode as [Sales Invoice Acc Code],T1.LineTotal as [Sales Inv Item Value],T4.DocNum as [Sales Order No] ,T2.DocNum  as [Deliv Note No]

,T7.DocNum as [Purchase Order No]
                        ,T10.DocNum as [Purchase Inv No] ,T9.LineTotal as [Purchase Inv Item Value] ,(isnull(T1.LineTotal,0) -isnull(T9.LineTotal,0) ) as [Gross Margin] 
FROM OINV T0 
            Inner Join INV1 T1 on T0.DocEntry =T1.DocEntry and T1.BaseType =15
            INNER JOIN ODLN T2 on T2.DocEntry =T1.BaseEntry 
            INNER JOIN DLN1 T3 on T3.DocEntry =T2.DocEntry and T3.BaseType =17 and T3.LineNum =T1.BaseLine 
            INNER JOIN ORDR T4 on T4.DocEntry =T3.BaseEntry 
            Inner join RDR1 T5 on T5.DocEntry =T4.DocEntry  and T5.LineNum =T3.BaseLine 
            LEFT Outer Join POR1 T6 on T6.DocEntry  =T5.PoTrgEntry and T6.BaseType =17 
            LEFT OUTER JOIN OPOR T7 on T7.DocEntry =T6.DocEntry    
            LEFT OUTER JOIN PDN1 T8 on T8.BaseEntry=T7.DocEntry and T8.BaseType =22 and T8.BaseLine =T6.LineNum  
            --LEFT OUTER JOIN OPDN T11 on T11.DocEntry =T8.DocEntry 
            LEFT OUTER JOIN PCH1 T9 on (T9.BaseEntry =T8.DocEntry or T9.BaseEntry =T6.DocEntry)and (T9.BaseType =20 or T9.BaseType =22) and T9.ItemCode = T1.ItemCode 
            LEFT OUTER JOIN OPCH T10 on T9.DocEntry =T10.DocEntry
WHERE T0.DocDate >=[%0] and T0.DocDate <= [%1]
Order by T4.CardCode  

  • No labels