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: Joseph Anthony

PURPOSE AND BACKGROUND:  This SQL was created in 200X SP XX PL XX. In a typical Indian scenario, we need sale and purchase registers to check the excise payable and receivable. The following is a sample which can be used  as a basic outline and modified per your requirements for Items. For instance, the "Statype" can be changed to the desired tax type you are looking for coming from the Billings (Invoices) and Items.  To find the Statype, you can use the simple SQL of " SELECT * FROM OSTT".   This would show you the Statype which can be used in the query.  The report below gives a Sales Register by Item Detail.  This is a fairly straight and simple SQL which Financial Personnel appreciate.  You might be able to use it "as is" or add more data fields to this SQL, depending upon your own requirements.

Here is a screen print of the SQL results:

Copy and Paste this SQL:

--D-SL Sales Tax Register by Item for Indian Localization Ver 1 JA 2011 02 24

--DESCRIPTION:  In a typical Indian scenario, we need sale and purchase registers to check the excise payable and receivable. The following is a sample which can be used  as a basic outline and modified per your requirements.

--AUTHOR(s):
--Version 1 Joseph Anthony 24 Feb 2011

DECLARE

@StartDate DATETIME,
@EndDate DATETIME,
@Dummy INTEGER

SELECT TOP 1 @Dummy = DocNum
FROM  OINV T0
WHERE T0.DocDate >= [%1]
AND T0.DocDate <= [%2]

SELECT

@StartDate= '[%1]',
@EndDate = '[%2]'

SELECT DISTINCT

T0.Docentry,
T0.DocNum as 'Bill No.',
CONVERT(VARCHAR(10), T0.DocDate, 3) AS 'Bill date',
T0.CardName AS 'Customer Name' , 
T1.Dscription AS 'Item Description', 
T1.Quantity,
T1.Price AS 'Price',
T1.TaxCode,
T1.LineTotal AS 'Basic Line Total' ,

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4
WHERE INV4.StaType = -90
AND INV4.DocEntry = T0.DocEntry
AND INV4.LineNum = T1.LineNum) AS 'BED',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4
WHERE INV4.StaType = -60
AND INV4.DocEntry = T0.DocEntry
AND INV4.LineNum = T1.LineNum) AS 'Cess',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4
WHERE INV4.StaType = 9
AND INV4.DocEntry = T0.DocEntry
AND INV4.LineNum = T1.LineNum) AS 'HeCess',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4
WHERE INV4.StaType = 1
AND INV4.DocEntry = T0.DocEntry
AND INV4.LineNum = T1.LineNum) AS 'VAT',

(SELECT DISTINCT ISNULL (SUM(INV4.TaxSum),0)
FROM INV4
WHERE INV4.StaType = 8
AND INV4.DocEntry = T0.DocEntry
AND INV4.LineNum = T1.LineNum) AS 'CST',

T0.Comments

FROM OINV T0

INNER JOIN INV1 T1
ON T0.DocEntry = T1.DocEntry 

WHERE T0.DocDate >= @StartDate
AND T0.DocDate <= @EndDate
AND T1.TargetType < > 14

FOR BROWSE

  • No labels