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. Customers generally like to see what invoices were paid not only by SAP B1 Incoming Payment, but also what invoices are connected to a deposit.  The SQL shows the information usually requested for Cash Applications done through the "lockbox" (checks received).  It includes the Deposit, Incoming Payment, Customer, AR Invoice Numbers, and Checks Numbers for all applications done during a specific date.  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 Daily Deposit with AR Invoices from Lockbox.png

Copy and Paste this SQL:

--G-BK Daily Deposit with AR Invoices Lockbox Ver 1 ZP 2011 02 08

--DESCRIPTION:  SQL shows information on what Customer Checks and AR Invoice Numbers are associated with the daily deposit.

--AUTHOR(s):
--Version 1 Zal Parchem 2011 Feb 08

SELECT

T0.DeposDate AS 'Deposit Date',
T0.DeposNum AS 'Depoit Numb',
T2.DocNum AS 'Inc Pay Numb',
T3.InvoiceId + 1 AS 'Line Numb',

CASE
WHEN T2.PayNoDoc = 'Y' THEN 'Yes'
WHEN T2.PayNoDoc = 'N' THEN ' '
ELSE 'Error'
END AS 'Unique Pay',

T5.CardCode AS 'Cust  Numb',
T5.CardName AS 'Customer Name',
T1.CheckNum AS 'Cust Ck Numb',
T4.NumAt Card AS 'Cust Ref',
T4.DocNum AS 'Inv Numb',

CASE
WHEN T3.SumApplied = T6.CheckSum THEN T3.SumApplied
WHEN T3.SumApplied < T6.CheckSum THEN T3.SumApplied
ELSE T6.CheckSum
END AS 'Paid on Inv'

FROM ODPS T0 

LEFT OUTER JOIN OCHH T1
ON T0.DeposId = T1.DpstAbs

LEFT OUTER JOIN ORCT T2
ON T1.RcptNum = T2.DocEntry

LEFT OUTER JOIN RCT2 T3
ON T2.DocEntry = T3.DocNum

LEFT OUTER JOIN OINV T4
ON T3.DocEntry = T4.DocEntry

LEFT OUTER JOIN OCRD T5
ON T2.CardCode = T5.CardCode

LEFT OUTER JOIN RCT1 T6
ON T2.DocEntry = T6.DocNum

WHERE T0.DeposDate = '[%0]'

FOR BROWSE

  • No labels