Skip to Content
avatar image
Former Member

Query on Checks deposited for the day

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Jun 19, 2010 at 09:35 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 18, 2010 at 09:47 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Jun 18, 2010 at 10:07 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • avatar image
    Former Member
    Jun 18, 2010 at 10:29 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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]