link between JDT1 and OCRD

[Former Member]

HI All,

Looking for SQL experts. How can i link between these two tables? Under JDT1, the field name for 'ContraAct' is actually the customer code. How can i link the OCRD? the business partner? so, i can have more details in the query.

Hopefully u guys can help.

Thanks.

Harith

MALAYSIA

[Former Member]

Hi All,

Thank you for the solutions!

Regards,

Harith

[Former Member]

Hi Harith

Each transaction type in SAP Business One has an "internal" object type that is numeric. For a list of these, open the REFDI.CHM help file that can be found under Program Files > SAP > SAP Business One SDK > Help. Then go to Search Tab and type in OJDT. In the list choose JournalEntries Object. In the right hand window click on the OJDT (Source table: OJDT) link. Under constraints you will find a list of all the object types and names.

Kind regards

Peter Juby

[Former Member]

Hi Harith

You can put the JDT1.TransType = 14 under the WHERE part and the other will be under the FROM part as follows:

SELECT T1.ShortName, T2.CardName, T1.RefDate, T1.ContraAct, T0.Segment_0, T0.Segment_2, T0.AcctName,T1.LineMemo, T1.Debit, T1.Credit FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account INNER JOIN OCRD T2 ON T1.ShortName = T2.CardCode INNER JOIN ORIN T3 ON T1.BaseRef = T3.DocNum WHERE T1.RefDate >%0 AND T1.RefDate <=%1 AND T0.Segment_0 = 2010080 AND T0.Segment_2 = 'AU.WAR' AND T1.TransType = '14'

Kind regards

Peter Juby

[Former Member]

Hi Peter,

I think i can manage from here now. BUt last question. Why does the transtype specifically at number 14? not 13 or other numbers? any reason?

Thanks.

Harith

[Former Member]

Transaction Type 14, refers to CreditNotes Screen

[Former Member]

Hi Harith

You will need to use 2 fields from JDT1, firstly TransType = 14 (Credit Note) and secondly BaseRef = ORIN.DocNum.

Kind regards

Peter Juby

[Former Member]

Hi Peter,

Thank you. But how would it be to use two fields in my query? I'm very sorry. Not really good in joining.

Thanks.

Appreciate your assistant.

Harith

[Former Member]

Hi Harith

If you look at a journal entry with 3 lines for example, the first line would be against the control account and the ShortName would contain the Customer code. The ContraAcc will have the control account code as well. For the second and third line the G/L account code will be in ShortName and the Customer Code in ContraAcct.

I will look at the query and respond to that in a moment.

Kind regards

Peter Juby

[Former Member]

Hi Harith

Try the following:

SELECT T1.ShortName, T2.CardName, T1.RefDate, T1.ContraAct, T0.Segment_0, T0.Segment_2, T0.AcctName,T1.LineMemo, T1.Debit, T1.Credit FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account INNER JOIN OCRD T2 ON T1.ShortName = T2.CardCode WHERE T1.RefDate >%0 AND T1.RefDate <=%1 AND T0.Segment_0 = 2010080 AND T0.Segment_2 = 'AU.WAR'

Just be aware that only 1 line of the entries from JDT1 will have the shortname (usually row 0) and that this will limit the results returned. Is the account code from Segment_0 the control account? If not, the above query will not work as the G/L account code you want is a line that does not have the shortname on it. In this case the ContraAcct should be used instead of ShortName as the Customer Account number for all other rows will be in the ContraAcc field.

Kind regards

Peter Juby

[Former Member]

Hi Peter,

Thank you for your comments. Anyhow, how can i link ORIN to this query :

SELECT T1.ShortName, T2.CardName, T1.RefDate, T1.ContraAct, T0.Segment_0, T0.Segment_2, T0.AcctName,T1.LineMemo, T1.Debit, T1.Credit FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account INNER JOIN OCRD T2 ON T1.ShortName = T2.CardCode WHERE T1.RefDate >%0 AND T1.RefDate <=%1 AND T0.Segment_0 = 2010080 AND T0.Segment_2 = 'AU.WAR'

And u did mentioned ,'Just be aware that only 1 line of the entries from JDT1 will have the shortname (usually row 0) and that this will limit the results returned'. What u mean by this?

Thanks.

Harith

[former_member187989]

Harith,

JDT1 T0 INNER JOIN OCRD T1 ON

T1.ShortName=T0.CardCode

Jeyakanthan

[Former Member]

Hi Jeyakanthan,

Thank you for your info. How can ur query to this :

SELECT T1.RefDate, T1.ContraAct, T0.Segment_0, T0.Segment_2, T0.AcctName,T1.LineMemo, T1.Debit, T1.Credit FROM OACT T0 INNER JOIN JDT1 T1 ON T0.AcctCode = T1.Account WHERE T1.RefDate >[%0] AND T1.RefDate <=[%1] AND T0.Segment_0 = 2010080 AND T0.Segment_2 = 'AU.WAR'

Appreciate your expert.

Thanks.

Harith

MALAYSIA