cancel
Showing results for 
Search instead for 
Did you mean: 

credits applied

Former Member
0 Kudos

from which table i can pull all open credits that have applied to invoices and to which invoices it got applied to.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

no my question now is once the credit has been applied. We want to know to which invoices it got applied to. i couldnt figure out from OJDT table. Where orin it rin1 it doesnt show up to which it has got applied too.

Former Member
0 Kudos

Hi Try this one....

RIN1.BaseEntry <> -1....

This will give your the Invoice against Credit Note is raised......

Regards,

Rahul

Former Member
0 Kudos

and actually i was trying to get the invoice numbers also that has applied too.

Former Member
0 Kudos

Use RIN1.BaseRef for Invoice No..........

Regards,

Rahul

Former Member
0 Kudos

where can i get applied transactions. I mean from which table when i select credit memo on SAP B1 and right click and select applied transactions it gives me details where can i get those details from backend tables.

Former Member
0 Kudos

Hi......

Applide Transactions is itself a report which has no Specific Table but you can get the details from OJDT JDT1 and ORIN tables combiinely...........

Regards,

Rahul

Former Member
0 Kudos

OITR & ITR1 are the tables that store the reconciiliation details

Answers (1)

Answers (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

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