Working with SAP data, we are willing to enrich sales data with the last customer. Depending on the sales type, there are different partner function codes that correspond to the last company to which the sale is performed (e.g.: we may have indirect or direct sales). For now, we have been considering tables VBAP/VBAK/VBPA. We extract data from each table to separate files using sap4j, and then join VBAP and VBPA on VBELN, and consider partner codes WE (goods recipient) or custom consignation codes indicating the last buyer for consignations.
Is there some accurate way to know who is the last buyer in the chain for a given sale?
It can be done in the following way:
def sales_tabkey(row):
return "001{}{}".format(row['VBELN'], row['POSNR'])
def expected_partner_function_for_sales_type(row):
consignation_codes = set(['ORK', 'XKB', 'ZSOK', 'ZLZK', 'ZTSK', 'KE', 'ZED', 'ZZN'])
if row['AUART'] in consignation_codes:
return 'ZK'
return 'WE'
def get_kunnrf_frame(vbap, vbak, vbpa, kna):
consignation_codes = set(['ORK', 'XKB', 'ZSOK', 'ZLZK', 'ZTSK', 'KE', 'ZED', 'ZZN'])
df = pd.merge(vbap, vbak, on=['VBELN'], how='left')
df = pd.merge(df, vbpa, on='VBELN', how='left')
df["EXPPARVW"]=df.apply(expected_partner_function_for_sales_type, axis=1)
# KUNNR in kna is considered end_client_id
df = pd.merge(df, kna, on='ADRNR', how='left')[['VBELN','POSNR', 'KUNNR','end_client_id', 'ADRNR', 'PARVW', 'EXPPARVW', 'AUART']].drop_duplicates()
df['TABKEY']=df.apply(sales_tabkey,axis=1)
endclient_tabkeys = set(df.TABKEY.unique())
dfa = df[df.PARVW==df['EXPPARVW']]
dfb = df[df.TABKEY.isin(endclient_tabkeys.difference(set(dfa.TABKEY.unique())))]
return pd.concat([dfa, dfb])