Query on Checks deposited for the day

[Former Member]

Hi Experts,

Can I have a query on checks which were deposited on-date. Columns would be: Dep No. Customer code, Customer Name, Check date, Check No., Bank code and Amount. Parameter would be the Deposit date.

Many thanks.

Don

[former_member325312 · Accepted solution]

Hi Don

SELECT T1.[DeposNum], T1.[DeposDate], T0.[CardCode], T0.[CardName], T2.[BankCode], T2.[CheckNum], T2.[CheckDate], T2.[CheckSum] FROM OCRD T0 , ODPS T1 INNER JOIN OCHH T2 ON T1.DeposId = T2.DpstAbs WHERE T0.[CardCode] = T2.[CardCode] and T1.[DeposDate] =[%0] GROUP BY T1.[DeposNum], T1.[DeposDate], T0.[CardCode], T0.[CardName], T2.[BankCode], T2.[CheckNum], T2.[CheckDate], T2.[CheckSum]

check this query i think this will solve ur problem if so pls do close the thread

Cheers

Jenny

[ashutosh_tomar]

You may try this:

SELECT T0.[DeposNum], T0.[DeposDate], T1.[CardCode], T1.[CheckDate], T1.[CheckNum], T1.[BankCode], T1.[CheckSum] FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs WHERE T0.[DeposDate] >=[%0] AND T0.[DeposDate] <=[%1]

Regards

Ashutosh

[zal_parchem2]

LOL Don - you have lots of choices now...but, if you want customer name, you will need to add the OCRD table to get the customer name in Jennifer's and Asutosh's SQL with

INNER JOIN OCRD T2

T1.CardCode = T2.CardCode

and place the CardName into the SELECT and GROUP...

Regards - Zal

[ashutosh_tomar]

yes....To get BP Name.

Cheers

[Former Member]

Hello All!

Thanks for the query! However, I have a little problem because I have Incoming payments created with 'Account' radio button, these deposited incoming payments doesn't seem to be included when I run all of your queries.

Thanks,

Don

[zal_parchem2]

Ohhhhhhh - now that is something different. With all of my customers, they do not allow the Cash Applications personnel to apply a dollar amount to Account. Everything must be applied to a document (that is why CM/Inv is in the SQL)...

You need a completely different SQL now...a little bit more involved - maybe someone can share the one they created

Regards - Zal

[ashutosh_tomar]

HI Don,

It is there in the query result the only thing is Account name is coming in place of Customer Code.

Regards

Ashutosh

[zal_parchem2]

Hi Don - Ashutosh is correct - this works with Place Payment on Account for both Full and Partial Account applications:

Regards - Zal

SELECT 

T0.DeposNum, 
T0.DeposDate, 
T1.CardCode, 
T2.CardName, 
T1.CheckDate, 
T1.CheckNum, 
T1.BankCode, 
T1.CheckSum 

FROM ODPS T0 

INNER JOIN OCHH T1 
ON T0.DeposId = T1.DpstAbs 

INNER JOIN OCRD T2
ON T1.CardCode = T2.CardCode

WHERE 

T0.DeposDate >=[%0] 
AND T0.DeposDate <=[%1]

[former_member325312]

hello Don

pls check this query

SELECT T0.[DeposNum],T1.[CardCode], T1.[CheckDate], T1.[CheckNum], T1.[BankCode], T1.[CheckSum] FROM ODPS T0 INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs WHERE T0.[DeposDate] = [%0] GROUP BY T0.[DeposNum],T1.[CardCode], T1.[CheckDate], T1.[CheckNum], T1.[BankCode], T1.[CheckSum]

i think this will solve ur problem

regards

Jenny

[Former Member]

Hi Jenny,

I used your query with the OCRD modification,

SELECT T0.DeposNum,T1.CardCode, T1.CheckDate, T1.CheckNum, T1.BankCode, T1.CheckSum FROM ODPS T0

INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs

INNER JOIN OCRD T2 T1.CardCode = T2.CardCode

WHERE T0.DeposDate = [%0]

GROUP BY T0.DeposNum,T1.CardCode, T1.CheckDate, T1.CheckNum, T1.BankCode, T1.CheckSum

However, I get an error message, 'Incorrect syntax near T1'. What could be the problem with the above query?

Hope to hear from you,

Thanks,

Don

[zal_parchem2]

Don - you forgot the ON between T2 and T1 in your JOIN statement when you did the modification...comparison...

INNER JOIN OCHH T1 ON T0.DeposId = T1.DpstAbs

INNER JOIN OCRD T2 T1.CardCode = T2.CardCode

Regards - Zal

Edited by: Zal Parchem on Jun 19, 2010 4:14 AM

[zal_parchem2]

Hello Don - maybe try this out to see if it works...Regards, Zal

SQL G BK Daily Check Deposit Report Detail by Date Range Ver 2 ZP 2009 01 20

--DESCRIPTION: SQL reports on deposit, incoming payment, check numbers, and customer invoice information. This SQL is the basis for the Daily Deposit Report Summary by Date Range.

--AUTHOR(s):

--Version 1 Zal Parchem 12 Jan 2009

--Version 2 Zal Parchem 20 Jan 2009 Added Bank Code

--PROBLEM(s):

--[P1] ZP 2008 11 18 - SAP does not have a direct link to provide all of the information. See how SQL places info together in the JOIN statements before making any changes.

SELECT

T0.[DeposDate] AS 'Deposit Date',

T3.CardName AS 'Vendor/Customer Name',

T3.CardCode AS 'Vendor/Customer Number',

T1.CheckNum As 'Check Number',

T3.DocNum AS 'Inv/Cred Memo',

CASE

WHEN (T2.InvType = 14)

THEN (T2.SumApplied * -1)

ELSE (T2.SumApplied *1)

END AS 'Sum Applied to',

T1.[DpstAbs] AS 'Deposit Number',

T1.BankCode AS u2018Bank Codeu2019

--T2.DocNum AS 'Inc Payment',

--T0.[DeposNum] AS 'Filter Number'

FROM ODPS T0

INNER JOIN OCHH T1

ON T0.DeposId = T1.DpstAbs

LEFT OUTER JOIN RCT2 T2

ON T1.RcptNum = T2.DocNum

LEFT OUTER JOIN OINV T3

ON T2.DocEntry = T3.DocEntry

WHERE T0.DeposDate >= '[%0]'

AND T0.DeposDate <= '[%1]'

ORDER BY T3.CardName

[Former Member]

Hi Zal,

Can I run this query in the Query Generator? If not, will there be revisions to the query?

Thanks,

Don

[zal_parchem2]

Don - sure, you can copy this into Query Generator adn run it from there.

The only change (and I am certain you know about it) is the parameter selections should be formatted differently than how this forum displays it with the "[" and the "]"...

Regards Zal

Edited by: Zal Parchem on Jun 18, 2010 6:03 AM