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 G-BK Banking Module SQL Posted to Wiki Listing - G-BK Banking Module SQL Posted to Wiki    )

TYPE: Frequently Requested SQL.

AUTHOR NAME: Zal Parchem

PURPOSE AND BACKGROUND:  This SQL was created in 2007A SP01 PL05. The SQL lists out all incoming payments processed as "Bank Transfers" with a specific range selected by the end-user.  The SQL can be run for a single day or it can be run to find multiple payments over a month.  This SQL comes in handy for those individuals responsible for Cash Applications.  If you find this SQL helpful, then use “copy and paste” to place and save it using the Query Generator of your SAP B1 installation.

Here is a screen print of the SQL results:

 G-BK Payments by Bank Transfer within Date Range.png

Copy and Paste this SQL:

--G-BK Payments by Bank Transfer within Date Range Ver 1 ZP 2011 03 12

--DESCRIPTION:  SQL lists all Bank Transfer Payments made within a selected date range.

--AUTHOR(s):
--Version 1 Zal Parchem 2011 03 12

SELECT DISTINCT

T1.DocDate,
T0.CardCode,
T0.CardName,
T2.DocNum AS 'Inc Pay Nbr',
T1.DocTotal AS 'Appl Amt'

FROM OCRD T0 

LEFT OUTER JOIN ORCT T1
ON T0.CardCode = T1.CardCode

INNER JOIN ORCT T2
ON T1.DocNum = T2.DocNum
AND T2.TrsfrAcct IS NOT NULL

WHERE

T1.DocDate >= '[%0]'
AND T1.DocDate <= '[%1]'

ORDER BY

T1.DocDate DESC,
T0.CardCode,
T0.CardName,
T2.DocNum,
T1.DocTotal

FOR BROWSE

  • No labels