DueDate field in the Incoming payments

[Former Member]

I need know the Incoming payments paid with the respective duedates..

for this, i use this SQL statement:

SELECT

T1.DocNum ,

T2.Serial ,

T1.CardCode ,

T1.Cardname ,

T6.GroupName,

T4.SlpName ,

T3.CityS ,

T0.SumApplied,

T1.DocDate ,

FROM [dbo].[RCT2] T0

LEFT OUTER JOIN [dbo].[ORCT] T1 ON T1.DocNum = T0.DocNum

LEFT OUTER JOIN [dbo].[OINV] T2 ON T2.DocEntry = T0.DocEntry

LEFT OUTER JOIN INV12 T3 ON T3.DocEntry=T2.DocEntry

LEFT OUTER JOIN OSLP T4 ON T4.SlpCode=T2.SlpCode

LEFT OUTER JOIN OCRD T5 ON T5.CardCode=T2.CardCode

LEFT OUTER JOIN OCRG T6 ON T6.GroupCode=T5.GroupCode

WHERE T1.CardCode='[%0]'

How can i add the duedate field from INV6 table, without duplicate records in this select?There is any other duedate field

in another table that corresponds the INV6 table(duedate)?

Thanks

Edited by: Philip Eller on May 20, 2008 9:25 AM

Edited by: Philip Eller on Jun 3, 2008 8:37 AM*

Edited by: Philip Eller on Jun 17, 2008 9:30 AM**

[former_member583013 · Accepted solution]

What are you exactly referring by "respective duedates.."

Is it the Invoice's DUE DATE. (DocDueDate) ?

Suda

[Former Member · Accepted solution]

The value date in the INV6.. (AR Invoice>Accounting tab>Instalments)...

The date of the expiration in the Invoice's DUE DATE. (DocDueDate) can be different of the date of Instalments.

I can divide the total of the Invoice with different dates(DueDate).

Each one generates a incomming payments

I need a query that shows this field...

basically...

SELECT

T1.DocNum ,

T2.Serial ,

T1.CardCod,

T1.Cardname ,

T0.SumApplied ,

FROM [dbo].[RCT2] T0

LEFT OUTER JOIN [dbo].[ORCT] T1 ON T1.DocNum = T0.DocNum

LEFT OUTER JOIN [dbo].[OINV] T2 ON T2.DocEntry = T0.DocEntry

WHERE T1.CardCode='[%0]'

and add the the DueDate (Instalments)

[Former Member · Accepted solution]

It can be the same date that is shown in <sales analysis report by customer>...in the column Value Date...

[Former Member · Accepted solution]

try the query as

SELECT

T1.DocNum ,

T2.Serial ,

T1.CardCod,

T1.Cardname ,

T0.SumApplied ,

(select top 1 duedate from inv6 where docentry = T2.docentry) as duedate

FROM dbo.RCT2 T0

LEFT OUTER JOIN dbo.ORCT T1 ON T1.DocNum = T0.DocNum

LEFT OUTER JOIN dbo.OINV T2 ON T2.DocEntry = T0.DocEntry

WHERE T1.CardCode='%0'

[Former Member · Accepted solution]

Thanks for reply...

In this case, i showing the date of the first record and does not the date relative to the payment...

if i add the field <InstlmntID> in the sql statement

(select top 1 InstlmntID from inv6 where docentry = T2.docentry) as id)

it shows wrong value...

[Former Member]

add subselect into your query as

SELECT

(select top 1 docduedate from inv6 where xxxx) as duedate

T1.DocNum ,

T2.Serial ,

T1.CardCode ,

T1.Cardname ,

T6.GroupName,

T4.SlpName ,

T3.CityS ,

T0.SumApplied,

T1.DocDate ,

i

FROM dbo.RCT2 T0

LEFT OUTER JOIN dbo.ORCT T1 ON T1.DocNum = T0.DocNum

LEFT OUTER JOIN dbo.OINV T2 ON T2.DocEntry = T0.DocEntry

LEFT OUTER JOIN INV12 T3 ON T3.DocEntry=T2.DocEntry

LEFT OUTER JOIN OSLP T4 ON T4.SlpCode=T2.SlpCode

LEFT OUTER JOIN OCRD T5 ON T5.CardCode=T2.CardCode

LEFT OUTER JOIN OCRG T6 ON T6.GroupCode=T5.GroupCode

WHERE T1.CardCode='%0'