NEED SQL FOR CHECK REGISTER REPORT

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.

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.

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