cancel
Showing results for 
Search instead for 
Did you mean: 

Query help

former_member224264
Participant
0 Kudos
49

Greetings of the day.

Can anyone help us with a query for list of invoices raised against and payment received?

Regards.

Accepted Solutions (1)

Accepted Solutions (1)

former_member224264
Participant

Greetings of the day Nagarajan,

Have tried with ur second query and getting 'No data found'

Attached the screen shot.

Greetings Mustafa,

SBO is 8.82 PL08.

none of the document number is showing in the query provided for the pay date.

Regards.

Rafaee_Mustafa
Active Contributor
0 Kudos

Check This,

SELECT T0.[DocNum] PayNum , T0.[DocDate] PayDate , T0.[CardCode], T0.[CardName], T0.[CashSum], T0.[CheckSum], T0.[TrsfrSum], T2.[DocNum] InvNum , T2.[DocDate] InvDate FROM [dbo].[ORCT]  T0 INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum, OINV T2 WHERE T0.[Canceled] ='N' and  T2.[DocEntry] =  T1.[DocEntry] and  T2.[DocDate]  between [%0] and [%1]

former_member224264
Participant
0 Kudos

Greetings Mustafa,

Wow. thanks a lot for the query and changes.

Your support and help has indeed provided a good solution.

Regards,

Answers (6)

Answers (6)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Have you checked my second reply?

former_member224264
Participant
0 Kudos

Greetings of the day.

No internal reconciliations are done for payment received.

They are done thru the Banking module for payment received by using incoming payment of ORCT table.

Regards.

Rafaee_Mustafa
Active Contributor
0 Kudos

Which SBO version are you using ?

can you verify any of the document in which payment made  but query not showing

former_member224264
Participant
0 Kudos

Greetings of the day.

Thanks Manish K and Mustafa Rafaee for the help on the query.  they are working satisfactorily.

Need an addition, and hope we are not asking more.

Can we have the date of payment received.  Tried by inserting the table ocrt for the date but of no avail.

Regards.

Rafaee_Mustafa
Active Contributor
0 Kudos

Sure Mekhala,

Here you go.

SELECT        T0.DocEntry, T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T0.VatSum, T0.DocTotal, T0.PaidToDate, (T0.DocTotal - T0.PaidToDate) AS Balance,

                         T0.ReceiptNum AS PayNum, ORCT.DocDate PayDate

FROM  OINV AS T0 LEFT OUTER JOIN ORCT ON T0.ReceiptNum = ORCT.DocNum

where T0.[DocDate] between  [%1] and  [%2]

former_member224264
Participant
0 Kudos

Greetings Mustafa,

Thanks for the query.

tried and got the following error.

""1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Must specify table to select from. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement 'Blanket Agreement' (OOAT) (s) could not be prepared.""

Regards.

Rafaee_Mustafa
Active Contributor
0 Kudos

Sorry that will work on SQL but not in SBO

try this for SBO

SELECT        T0.DocEntry, T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T0.VatSum, T0.DocTotal, T0.PaidToDate, (T0.DocTotal - T0.PaidToDate) Balance,

                         T0.ReceiptNum PayNum, ORCT.DocDate PayDate

FROM  OINV T0 LEFT OUTER JOIN ORCT ON T0.ReceiptNum = ORCT.DocNum

where T0.[DocDate] between  [%1] and  [%2]

former_member224264
Participant
0 Kudos

Greetings of the day.

The query is working good, but unable to get the date of payment done.

Is is possible.

Regards.

Rafaee_Mustafa
Active Contributor
0 Kudos

Hi,

You not getting PayDate column ? or data in it ?

former_member224264
Participant
0 Kudos

Greetings of the day Mustafa,

The query displays the results.

The Paydate column is being displayed and there is no data in it and is blank.  Have checked the query for the period from 01.04.2014 till today.

Regards.

Rafaee_Mustafa
Active Contributor
0 Kudos

Hi,

If you have closed your INV by using internal reconciliation it wont show through RCT tables.

Its showing at my side on all DBs. Can you please verify any one document at your end ?

Regards

Rafaee M.

former_member184146
Active Contributor
0 Kudos

Hi,

     try this

SELECT T0.[DocEntry], T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[VatSum], T0.[DocTotal],

T0.[PaidToDate],(T0.[DocTotal]- T0.[PaidToDate])Balance, T0.[ReceiptNum]PayNum

FROM OINV T0

group by T0.[DocEntry], T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[VatSum], T0.[DocTotal],

T0.[PaidToDate],T0.[DocTotal], T0.[PaidToDate], T0.[ReceiptNum]

having sum(T0.[DocTotal]- T0.PaidToDate)=0

Regards,

Manish

Rafaee_Mustafa
Active Contributor
0 Kudos

Hi Mekhala,

check this,

SELECT T0.[DocEntry], T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[VatSum], T0.[DocTotal], T0.[PaidToDate],(T0.[DocTotal]- T0.[PaidToDate])Balance, T0.[ReceiptNum]PayNum FROM OINV T0

where T0.[DocDate] between  [%1] and  [%2]

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try:

SELECT T0.[DocNum], T0.[DocDate], T0.[CardName],T0.[CashSum], T0.[CreditSum], T0.[TrsfrSum],T0.[DocTotal], T2.[DocNum], T2.[DocDate], T2.[CardName], T3.[ItemCode], T3.[Quantity], T3.[LineStatus] FROM ORCT T0 INNER JOIN RCT2 T1 ON T0.DocNum = T1.DocNum left join OINV T2 on t1.docentry = t2.docentry INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry WHERE T0.[DocDate] between [%0] and [%1] GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardName],T0.[CashSum], T0.[CreditSum], T0.[TrsfrSum],T0.[DocTotal], T2.[DocNum], T2.[DocDate], T2.[CardName], T3.[ItemCode], T3.[Quantity], T3.[LineStatus]

Thanks & Regards,

Nagarajan

former_member224264
Participant
0 Kudos

Greetings Nagarajan Sir,

Thanks for the query.

have tried using and results are being shown.  some observations.

1     Though from and to dates are selected but all invoices are being shown.

2.     Document total of AR invoice not tabulated.

3.     Data error of T0. CardName and T0.DocDate, year is 2013 but shown as 2014,

4.     Card Code of customer now shown.

Regards.

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try:

SELECT T0.[DocNum], T0.[DocDate], T0.[CardName],T0.[CashSum], T0.[CreditSum], T0.[TrsfrSum],T0.[DocTotal], T2.[DocNum], T2.[DocDate], T2.[CardName],T2.[DocTotal]

FROM ORCT T0 INNER JOIN RCT2 T1 ON T0.DocNum = T1.DocNum left join OINV T2 on t1.docentry = t2.docentry INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry WHERE T2.[DocDate] between [%0] and [%1] GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardName],T0.[CashSum], T0.[CreditSum], T0.[TrsfrSum],T0.[DocTotal], T2.[DocNum], T2.[DocDate], T2.[CardName],T2.[DocTotal]