SAP Business One Training Tips #1

Linking Customer/Vendor to a specific P&L account

 

A customer recently approached one of our Implementation Consultants and asked, “How can I track revenue/expenses to a specific P&L GL account by customer/vendor?” While the Accounting Tab of the Business Partner Master allows the user to define Payables and Receivables accounts for each customer/vendor accordingly, it is not possible to assign specific Expense and Revenue accounts at the BP level.

 

Business scenario: 

 

The customer needs to link a G/L account to his customers or vendors, so in the marketing documents, this account will be used automatically.

 

 

Solution: 

 

Using a User Defined Field, assign a G/L account to the BP master records and default the account to the Marketing Document via a formatted search. 

 

The solution varies for Items documents and Service documents.

 

  1. Create a user defined field in the BP master data as alpha numeric; for example UDF_GL and make sure that the UDF field # of characters correspond with the total characters of GL account in the COA.

clip_image001

 

  1. link the following query to the field as a formatted search:

 

SELECT T0.FormatCode, T0.AcctName FROM OACT T0 WHERE T0.ActType = ‘E’ Or T0.ActType = ‘I’ FOR BROWSE

 

The FormatCode field contains the complete account code including the different segments, without the separators.

 

The ActType identify the account type. In this example E=Expenses and I=Revenues (Income)

 

When creating a new BP, the user will chose from this list of GL account the relevant one to use or can be use update an existing blank BP record for this field.

 

 

clip_image002

 

 

For Items Documents:

 

 

  1. Create a user defined field as in Step 1 for account code field in Marketing Documents Title.

 

     2. In AR Invoice Window link the following query as a formatted search to this field:

 

 

SELECT T0.U_UDF_GL FROM OCRD T0 WHERE T0.CardCode = $[$-4.0.0] FOR BROWSE

 

This query will copy the GL account chosen in the BP to the Marketing document.

 

Note: Do not define Auto Refresh.

 

 

clip_image003

 

 

  1. Display the GL Account column in the Invoice rows  using the Form settings  and link the following query to the field for the formatted search:

 

 

SELECT $[OINV.U_UDF_GL] FOR BROWSE

 

Note this query is dedicated for AR  invoices; you may replace the table name according to your needs.

 

This query will copy the GL account from the header to the rows.

 

Define an Auto Refresh when the GL account header field is modified.

 

clip_image004

 

Process steps will be:

 

  • Choose customer (or vendor)
  • Choose items
  • Press Shift+F2 on the GL account Header fields
  • The GL Account in the rows will be refreshed automatically with the relevant account.

 

 

For Service Documents

 

 

  1. Link the following query to the GL account code in the rows

SELECT T0.U_UDF_GL FROM OCRD T0 WHERE T0.CardCode = $[$4.0.0] FOR BROWSE

 

Define and Auto Refresh when exiting the column Description.

 

clip_image005

 

 

Process steps will be:

 

  • Choose customer
  • Type the row description
  • The GL account will be populated automatically

 

 


Phone: 888.239.2818 | Fax: 312.621.9200

 Copyright © 2012 Clients First Business Solutions (North America) LLC. All rights reserved

CFBS Locations: New Jersey (Head Office)Alabama | California | FloridaIllinois | Minnesota | TennesseeTexas