Flag for business partner to be deleted

[Former Member]

Hello friends,

My question is brief, I would like to know if there's a flag, database field or nested query to check if a business partner can be deleted from SAP. I know Balance and UpdateDate give not enough information.

Thank you in advance.

[kothandaraman_nagarajan · Accepted solution]

Hi,

BP can be deleted only when there is no transaction posted.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Thank you Nagarajan for your answer, anyway i'm still in the same place. I may be wrong but there are many database tables meaning a transaction, is there one in particular that i should look for records in?

[kothandaraman_nagarajan · Accepted solution]

Not getting your exact requirement. Please explain.

[Former Member · Accepted solution]

There are database tables which keep transactions for business partners, for example OINV, whose CardCode field references a business partner.

My question was, "Is there a way, using only SQL, to check if a business partner is able to be deleted?"

[Former Member · Accepted solution]

One quick way could be to query the BP mater data and see if they have any balances. Otherwise, as far as I'm aware, the only other way to achieve what you're trying to do is to query each document table and get a count of the BP code. If it's greater than 0 in any of the tables then the BP will not be able to be deleted from the system. Nagarajan or another member may be able to comment on a better method though.

[Former Member · Accepted solution]

Thank you Thomas, however balance is not enough because a customer with 0 balance doesn't mean it has no movements. The other way you propose seems more accurate but I don't know the names of all the tables i should scrap, do you?

Thank you in advance.

[Former Member · Accepted solution]

I don't know them all off the top of my head, but if you go into the Tools > Queries > Query Generator and press tab that will give you a list of the tables and a description. You could also enable System Information and navigate through the documents.

[kothandaraman_nagarajan · Accepted solution]

Hi,

1. Try this query to get list of BP with no transactions:

SELECT T0.[CardCode], T0.[CardName], T0.[Balance] FROM OCRD T0 WHERE T0.[CardType]  = 'c' and  T0.[Balance]  = '0' and  T0.[CardCode]  not in ( select t0.cardcode from ORDR t0) and T0.[CardCode]  not in ( select t0.cardcode from ODLN t0) and T0.[CardCode]  not in ( select t0.cardcode from OINV t0) and T0.[CardCode]  not in ( select t0.cardcode from ORCT t0) and T0.[CardCode]  not in ( select t0.cardcode from PWZ5 t0)

2. There is no one table to find BP to delete.

3. To know list of transaction for BP, in BP master data, right click and choose inventory posting list.

4. There was discussion in this forum, even with zero balance  and no transactions, still system not allowed to delete. SAP fix this issue by query. You may refer this thread

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Thank you Nagarajan, that was what I was looking for.