How to distinguish a BP Row from a non BP row in Journal Entry lines in a query?

[rajesh_khater]

Hi,

Suppose a customer has not implemented any specific coding sequence for Business Partners and for Chart of Accounts.

It may be possible that there is a GL and a BP with the same code.

If I have to write a query to return all Journal Entry lines for BP rows where the BP type is Customer, what is the full proof and most optimal way to do this?

Note that for non BP rows also, the JDT1.ShortName column contains value which is same as JDT1.Account. If JDT1.ShortName were null for non BP rows, it would have been pretty easy. But that is not the case.

In my query,

I could join the JDT1 table with OACT table and OCRD table like this:

SELECT T0.* FROM

JDT1 T0 INNER JOIN OACT T1

ON T1.AcctCode = T0.Account AND T1.LocManTran = 'Y'

INNER JOIN OCRD T2

ON T2.CardCode = T0.ShortName

AND T2.CardType = 'C'

But it requires JOINs to 2 tables. Is there a better, more optimal way?

Also note that if I were to write query with JOIN to only the OCRD table, it may not be fullproof.

Example:

SELECT T0.* FROM

JDT1 T0

INNER JOIN OCRD T2

ON T2.CardCode = T0.ShortName

AND T2.CardType = 'C'

The reason being: suppose the user has used GL Code '100001' in a JE line, so the columns Account and ShortName will both contain '100001'.

Suppose there happens to be a BP with the same code '100001'.

The query will think that the row is a BP row, which is NOT the case. So I need to check 2 conditions:

1. The Account Code in JE line is a Control Account

2. The ShortName has a corresponding CardCode in the OCRD table with CardType as 'C'

Is there an easier way?

Thanks.

[former_member212181 · Accepted solution]

Dear Rajesh,

SAP B1 won't allow to create a BP with same existing GL account code vice versa.

It will through below error message

An account with the same code already exists [Business Partner - BP Code] , '400010'  [Message 173-14].

So the right way to differentiate a BP row and non-BP row is  matching JDT1.Account with JDT1.ShortName

If this JDT1.Account = JDT1.ShortName then this row is not BP related row.

If this JDT1.Account <> JDT1.ShortName then this row is BP related row.

Hope its clear

Thanks

Unnikrishnan