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 - E-PU Purchasing AP 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 08.SAP provided Purchase analysis report but some times user need to some additional field to show on report. This Sql can be used as basis  to include fields on Purchase Analysis Report.

Copy and Paste this SQL:

--SAP B1 SQL E-PU Purchase Analysis Report Ver1 ND  2011 11 11

--DESCRIPTION:  This Report will give you information on purchase value, invoice value during a selected period. This Query is linked to OCRD -BP master data and OITM i.e. Item Master data. Extra fields required from OCRD ,OITM and UDF's from OPCH and PCH1 can be added as per requirement.

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

SELECT DISTINCT
  T0.DocEntry,
  T0.DocNum AS [AP Invoice#],
  T0.DocDate,
  T0.CardCode as [Supplier Code],
  T0.CardName AS [Supplier Name],
  T1.ItemCode,
  T1.Dscription AS [Item Description],
  T1.Quantity,
  T1.Price ,
  T1.TaxCode,
  T1.LineTotal,
  T0.DocTotal
  -- Add fields From OCRD - Business Partner Master Data
  --Add Fields From OITM -- Item Master Data
  -- Add UDF's from Purchase Invoice header and lines table
FROM OPCH T0
    INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry
    INNER JOIN OCRD T2 on T0.CardCode= T2.CardCode
    INNER JOIN OITM T3 on T1.ItemCode =T3.ItemCode
WHERE T0.DocDate >= [%0] AND T0.DocDate <= [%1]
 

  • No labels