on 04-24-2011 11:56 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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 ..
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
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));
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
User | Count |
---|---|
89 | |
7 | |
6 | |
3 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.