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.
- 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.
- 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.
For Items Documents:
- 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.
- 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.
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
- 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.
Process steps will be:
- Choose customer
- Type the row description
- The GL account will be populated automatically
