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 E-PU Purchasing AP Module SQL Posted to Wiki Listing - E-PU Purchasing AP 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 AP Billings (AP 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 Purchases 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:

--E-PU Purchases Register by Item for Indian Location Ver 1 JA 2011 02 24

--DESCRIPTION:  In a typical Indian scenario, we need 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 OPCH T0
WHERE T0.DocDate >= [%1]
AND T0.DocDate <= [%2]

SELECT

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

SELECT DISTINCT
T0.DocEntry,
T0.DocNum AS 'AP Inv Num',
CONVERT(VARCHAR(10), T0.DocDate, 3) AS 'Bill Date',
T0.CardName AS 'Vendor Name',
T1.Dscription AS 'Item Description', 
T1.Quantity,
T1.Price AS 'Price',
T1.TaxCode,
T1.LineTotal AS 'Basic Line Total',

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

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

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

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

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

T0.Comments

FROM OPCH T0

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

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

FOR BROWSE

  • No labels