(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:
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.
--Version 1 Zal Parchem 2011 Feb 08
T0.DeposDate AS 'Deposit Date',
T0.DeposNum AS 'Depoit Numb',
T2.DocNum AS 'Inc Pay Numb',
T3.InvoiceId + 1 AS 'Line Numb',
WHEN T2.PayNoDoc = 'Y' THEN 'Yes'
WHEN T2.PayNoDoc = 'N' THEN ' '
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',
WHEN T3.SumApplied = T6.CheckSum THEN T3.SumApplied
WHEN T3.SumApplied < T6.CheckSum THEN T3.SumApplied
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]'