cancel
Showing results for 
Search instead for 
Did you mean: 

Incoming payment Query Help

Former Member
0 Kudos

Due Date

Invoice amount

Receipt Number

Payment Date

Paid amount.

For all Receipt I tried to link Invoice. RCT2 ORCT and OITR and ITR1 table to get all invoices and receipt including reconciled one. but my query is not linking reconciled invoices

Please help me to fix this issue.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

hi,

can u post ur query in forum... so that we can know ur exact requirement... anyways jus try this..

SELECT T2.[DocDate] AS 'Posting Date',T2.[DocNum] AS 'Document Number',T2.[CardCode] AS 'Customer/Vendor Code', T2.[CardName] AS 'Customer/Vendor Name',

T1.[SumApplied] AS 'Paid to Invoice'

FROM [dbo].[OINV] T0 INNER JOIN [dbo].[RCT2] T1 ON T1.[DocEntry] = T0.[DocEntry]

INNER JOIN [dbo].[ORCT] T2 ON T2.[DocNum] = T1.[DocNum]

WHERE T2.[DocDate] >= [%0] AND T2.[DocDate] <= [%1]

regards,

Vignesh

Former Member
0 Kudos

hi vignesh

thank you for your reply here is my query

SELECT OINV.CardCode,

OINV.CardName,

OINV.DocNum Invno,

OINV.DocDate,

T0.DocNum,

OITR.ReconDate

T1.DocTotal,

FROM OINV INNER JOIN ((RCT2 AS T0 INNER JOIN ORCT AS T1 ON T0.DocNum = T1.DocNum)

INNER JOIN (OITR INNER JOIN ITR1 ON OITR.ReconNum = ITR1.ReconNum)

ON T1.TransId = ITR1.TransId) ON OINV.DocEntry = T0.DocEntry

kvbalakumar
Active Contributor
0 Kudos

Hi,

Try this

SELECT T2.[CardCode], T2.[CardName], 
T2.[DocDate], T2.[DocDueDate], 
T2.[DocNum], T2.[DocTotal], 
T0.[DocNum] [Payment#], T0.[DocDate] [Payment Date], T1.[SumApplied] [Paid Amount] 
FROM ORCT T0  INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum
 inner join OINV T2 on T1.[DocEntry] = T2.DocEntry

Regards,

Bala

Edited by: Balakumar Viswanathan on Apr 26, 2011 1:25 PM

Former Member
0 Kudos

hi bala

i have tried this one already but it is not filtering reconciled Invoice

Please advise me how can i include those records in this report?

kvbalakumar
Active Contributor
0 Kudos

Hi,

Then try this

Select * from (SELECT distinct T2.[CardCode], 
T2.[CardName], T2.[DocNum] [Invoice#], T2.[DocDate], 
T2.[DocDueDate], T2.[DocTotal], T0.[ReconDate] [Paid Date], 
T0.[Total] [Paid Amount], 
(Select distinct T5.DocNum from 
ORCT T5 inner join ITR1 T6 on T5.DocEntry = T6.SrcObjAbs and 
T6.SrcObjTyp = '24' where T6.ReconNum = T0.ReconNum) [Payment Num]
FROM [dbo].[OITR]  T0 INNER JOIN ITR1 T1 ON T0.ReconNum = T1.ReconNum 
inner join OINV T2 on T1.[SrcObjAbs] = T2.DocEntry 
and T1.SrcObjTyp = 13) A
Where A.[Payment Num] is not null

Regards,

Bala

Former Member
0 Kudos

dear bala

We are almost there but still we need to modify this query as i am not able to execute sub query is returing more than one value could you please check i did try as well but not luck

thank you

poonam

kvbalakumar
Active Contributor
0 Kudos

Hi Poonam,

Try this

SELECT OINV.CardCode, 
OINV.CardName, OINV.DocNum [Invno],
OINV.DocDate,T0.DocNum,OITR.ReconDate,
T1.DocTotal
FROM OINV 
INNER JOIN RCT2 T0 ON OINV.DocEntry = T0.DocEntry
INNER JOIN ORCT AS T1 ON T0.DocNum = T1.DocNum
INNER JOIN OITR 
INNER JOIN ITR1 ON OITR.ReconNum = ITR1.ReconNum ON T1.TransId = ITR1.TransId

Regards,

Bala

Former Member
0 Kudos

Dear Bala,

I have noticed this linking of invoice and Receipt is not working both the way

1. we are not getting right receipt no against invoice and

2. it is not filtering internal reconsiled invoice still.

I am really stuck with this Please please find me right way to move on..

Any help will be appreciated ..

kvbalakumar
Active Contributor
0 Kudos

Dear Poonam,

Have you check this query or not?

Give a try to this query....

Select * from (SELECT distinct T2.[CardCode], 
T2.[CardName], T2.[DocNum] [Invoice#], T2.[DocDate], 
T2.[DocDueDate], T2.[DocTotal], T0.[ReconDate] [Paid Date], 
T0.[Total] [Paid Amount], 
(Select distinct T5.DocNum from 
ORCT T5 inner join ITR1 T6 on T5.DocEntry = T6.SrcObjAbs and 
T6.SrcObjTyp = '24' where T6.ReconNum = T0.ReconNum) [Payment Num]
FROM [dbo].[OITR]  T0 INNER JOIN ITR1 T1 ON T0.ReconNum = T1.ReconNum 
inner join OINV T2 on T1.[SrcObjAbs] = T2.DocEntry 
and T1.SrcObjTyp = 13) A
Where A.[Payment Num] is not null

Let me know the feedback....

Regards,

Bala

Former Member
0 Kudos

Dear Bala

i did mentioned that this query is not working as sub query is not formed the way it has to i did try but no luck so far,,

i m working on same concept... here is my query

SELECT dbo_OITR.InitObjAbs, dbo_OITR.ReconDate, dbo_ITR1.ReconSum, dbo_OINV.DocNum, dbo_OINV.DocDueDate, dbo_OINV.DocTotal, dbo_OINV.CardCode, dbo_OINV.CardName, dbo_OITR.InitObjTyp

FROM (dbo_OITR INNER JOIN dbo_ITR1 ON dbo_OITR.ReconNum = dbo_ITR1.ReconNum) INNER JOIN dbo_OINV ON dbo_ITR1.TransId = dbo_OINV.TransId

WHERE (((dbo_OITR.InitObjAbs) Is Not Null));

Former Member
0 Kudos

I tested and sound like your query is running. I have simplify it as follows:

SELECT T0.InitObjAbs, T0.ReconDate, T1.ReconSum,

T2.DocNum, T2.DocDueDate, T2.DocTotal, T2.CardCode, T2.CardName, T0.InitObjTyp

FROM dbo.OITR T0

INNER JOIN dbo.ITR1 T1 ON T0.ReconNum = T1.ReconNum

INNER JOIN dbo.OINV T2 ON T1.TransId = T2.TransId

WHERE T0.InitObjAbs Is Not Null AND T1.ReconSum > 0

ORDER By T2.DocNum

What issue is still outstanding?

Thanks,

Gordon

Answers (0)