Skip to Content
author's profile photo Former Member
Former Member

NEED SQL FOR CHECK REGISTER REPORT

i need a report that shows the check that have been printed. i need to see the bank account used to pay the check.

check number, check date, check total, check payable to, what the check was paying

ap invoice number, ap invoice date, ap invoice total, ap invoice amount paid(this can be different that the ap invoice doc total.

ap down payment invoice, etc.

i basically need to see what is on the outgoing payment

thank you

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Aug 14, 2009 at 11:41 AM

    Hi Davincibz,

    If you need to print Cheque payments Details on Outgoing Payments Print.

    In SAP B1 behaviour, Cheque payments details defaultly have in System PLD.

    Check this,

    ->> Open the Outgoing Payments(System) PLD and Save as the NEW PLD.

    ->> Open the New PLD and you can see the Cheque Details in Repetetive Area Field.

    If it is not print the Cheque Details in Repetetive Area on Outgoing Payments,

    Try this,

    ->> Create Database Fields in Repetetive Area.

    Check Date:

    Table -> VPM1 - Outgoing Payments - Check Rows.

    Cloumn -> DueDate

    Check Number:

    Table -> VPM1 - Outgoing Payments - Check Rows.

    Cloumn -> CheckNum

    Bank Name:

    Table -> VPM1 - Outgoing Payments - Check Rows.

    Cloumn -> BankCode

    (or) Use System Variable - 126

    Bank Branch Name:

    Table -> VPM1 - Outgoing Payments - Check Rows.

    Cloumn -> Branch

    Account Code:

    Table -> VPM1 - Outgoing Payments - Check Rows.

    Cloumn -> AcctNum - Account Number.

    Check Amount:

    Table -> VPM1 - Outgoing Payments - Check Rows.

    Cloumn -> CheckSum - Check Amount.

    A/P Invoice Details:

    ->> A/P Invoice all Details Capture in System Variable Field. This field default in System PLD.

    If you need to create System Variable Field for A/P Invoice details.

    ->> Create 1 System Variable Field and put the Variable No. - 130.

    ->> Documents Paid Variable(130) will Capture the Customer/Vendor Ref. No. Field and A/P Invoice No., Date and Invoice Paid Amount.

    ->> System Variable No. - 130 will Display the Customer/Vendor Ref. No.(A/P Invoice No., Date)

    and invoice Paid Amount (or) Journal Enry No. and Total Paid Amount.

    Regards,

    Madhan.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 14, 2009 at 12:33 PM

    Hi DavinCibz,

    Try this, Query Report. it will get the Check Payments Detail.

    SELECT 
    T0.[DocNum], 
    T0.[DocDate],
    T5.[SeriesName],
    T0.[PIndicator], 
    T0.[CardName],
    T0.[Address],
    T1.[DueDate], 
    T1.[CheckNum], 
    T1.[BankCode] as 'Bank Name', 
    T1.[Branch], 
    T1.[AcctNum] as 'Check A/C No.', 
    T1.[CheckSum], 
    T0.[TrsfrDate],
    T0.[Comments] 
     FROM OVPM T0  full outer JOIN VPM1 T1 ON T0.DocEntry = T1.DocNum 
    INNER JOIN NNM1 T5 ON T0.Series = T5.Series
    INNER JOIN OPID T6 ON T0.PIndicator = T6.Indicator
    WHERE T0.[DocNum]>='[%0]' and T0.[DocNum]<='[%1]' and T6.[Indicator]='[%2]'
    ORDER BY T0.[DocNum]
    

    Regards,

    Madhan.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 14, 2009 at 05:52 PM

    Try this one:

    SELECT Distinct T1.[AcctNum] as 'Check A/C No.', T2.[CheckKey], T0.[CardName],T1.[DueDate], T2.[PmntNum], T2.[CheckSum], T2.[CheckNum], 
    T1.[BankCode] as 'Bank Name', T1.[Branch], T3.[DocEntry], T4.[DocDate], T4.[DocTotal], T4.[PaidToDate]
    FROM dbo.OVPM T0 INNER JOIN dbo.VPM1 T1 ON T0.DocEntry = T1.DocNum
    LEFT JOIN dbo.OCHO T2 ON T2.PmntNum = T0.DocNum
    LEFT JOIN dbo.VPM2 T3 ON T0.DocEntry = T3.DocNum AND T3.PaidDpm = 'N' 
    LEFT JOIN dbo.OPCH T4 ON T3.DocEntry = T4.DocNum AND T3.InvType = T4.ObjType 
    WHERE T1.[DueDate] BETWEEN '[%0]' and '[%1]' AND T2.Printed = 'Y' 
    UNION ALL
    SELECT Distinct T1.[AcctNum] as 'Check A/C No.', T2.[CheckKey], T0.[CardName],T1.[DueDate], T2.[PmntNum], T2.[CheckSum], T2.[CheckNum], 
    T1.[BankCode] as 'Bank Name', T1.[Branch], T3.[DocEntry], T4.[DocDate], T4.[DocTotal], T4.[PaidToDate]
    FROM dbo.OVPM T0 INNER JOIN dbo.VPM1 T1 ON T0.DocEntry = T1.DocNum
    LEFT JOIN dbo.OCHO T2 ON T2.PmntNum = T0.DocNum
    INNER JOIN dbo.VPM2 T3 ON T0.DocEntry = T3.DocNum AND T3.PaidDpm = 'Y' 
    INNER JOIN dbo.OPCH T4 ON T3.DocEntry = T4.DocNum AND T3.InvType = T4.ObjType AND T4.DpmAmnt > 0
    WHERE T1.[DueDate] BETWEEN '[%0]' and '[%1]' AND T2.Printed = 'Y' 
    ORDER BY T1.[AcctNum]
    

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.