on 05-18-2011 2:47 PM
from which table i can pull all open credits that have applied to invoices and to which invoices it got applied to.
Hi......
If I'm not wrong then you are asking the same question second time.
If you are not satisfied then do open your previous thread and request to contibue the same and let s know why you asking second time the same one?
Regards,
Rahul
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Try this to start:
SELECT Distinct T0.DocNum 'CR #', T3.DocNum 'Invoice #'
FROM ORIN T0
INNER JOIN RIN1 T1 ON T1.DocEntry=T0.DocEntry
INNER JOIN INV1 T2 ON T2.DocEntry=T1.BaseENtry AND T2.LineNum=T1.Baseline
INNER JOIN OINV T3 ON T3.DocEntry=T2.DocEntry
WHERE T0.docstatus = 'O'
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
thanks you guys but i found the exact tables. here is the query and the tables for your future reference.
'SELECT T1.[TransType], T1.[BaseRef], T1.[TransId], T1.[Line_ID], T2.[ReconType], T2.[ReconRule1], T2.[ReconRule2], T2.[ReconRule3], T1.[RefDate], T1.[DueDate], T2.[ReconDate], T1.[BatchNum], T1.[CreatedBy], T1.[Ref1], T1.[Ref2], T1.[Ref3Line], T1.[ContraAct], T1.[LineMemo], T0.[CashDisSum], T1.[Debit], T1.[Credit], T1.[BalDueDeb], T1.[BalDueCred], T0.[ReconSum], T1.[SYSCred], T1.[SYSDeb], T1.[BalScDeb], T1.[BalScCred], T0.[ReconSumSC], T1.[FCDebit], T1.[FCCredit], T1.[BalFcDeb], T1.[BalFcCred], T0.[ReconSumFC], T2.[ReconNum], T1.[ShortName], T1.[Account], T1.[FCCurrency], T2.[ReconCurr], T2.[ReconNum], T0.[IsCredit], T2.[OldMatNum] FROM [dbo].[ITR1] T0 INNER JOIN [dbo].[JDT1] T1 ON T0.[TransRowId] = T1.[Line_ID] AND T0.[TransId] = T1.[TransId] INNER JOIN [dbo].[OITR] T2 ON T0.[ReconNum] = T2.[ReconNum] WHERE EXISTS (SELECT U0.[ReconNum] FROM [dbo].[ITR1] U0 WHERE U0.[TransId] = (@P1) AND U0.[TransRowId] = (@P2) AND T2.[ReconNum] = U0.[ReconNum] ) AND (T1.[TransId] <> (@P3) OR T1.[Line_ID] <> (@P4) ) ORDER BY T2.[CreateDate]',N'@P1 int,@P2 int,@P3 int,@P4 int',52479,0,52479,0
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
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.