Keeping track of amounts due generated by the Payroll module

[MrMauro]

Hi everyone:

May I have your input on the way I have set up the Payroll and help me to check if it is the right way to do it? I’m not confident that I have grasped the concepts here.

This is what I have achieved so far:

With the standard functionality of the Payroll module I’m able to produce the Journal that contains entries for the salary due, and extra lines to record the money that we have to pay to other “suppliers” (health providers, contributions and other things like that).

Our customization is quite simple: We have created a server script that before submitting the Journal created by the Payroll, modifies every line to set the type and the employee/supplier according to the account type (The purpose is to keep track of what is due. That’s why there are accounts defined as type Payables besides their expenses counterparts).

In that way, we can nicely use ERPNext standard reports to check what is due. Please see here where the Accounts Payable show the amount due to every supplier and also for the extra concepts to pay to the employee that are not part of the standard salary:

However it has two problems:

1. To pay those amount dues I have used another Journal ( I was not able to use a standard Payment for that ). While it works because I’m mainly creating a record on the opposed debit/credit for the account, party, and amount due, it seems I have to later run the reconciliation tool, to match the payments, which is very time consuming. Is this the right way to do it, or a way to prevent that extra reconciliation (ie: doing it when entering the payment Journal)?

2. The payment of the salary seems to not be tracked by the standard reports. It seems we can’t use an account of type Payable for the reason explained here:

The payroll payable account should not have any Account Type set. It shouldn’t even be marked as “Payable” just blank. This is because HR module doesn’t support grouping by party.

While I don’t understand what is “Grouping by party” it seems that is a restriction for the ERPNext standard reports to show in one place all the amount due for the company. Is this the expected behaviour on the Payroll module? or am I missing something?

I appreciate your help.

Thank you!

[aliadoinf]

Hola Mauro, me gustaría que nos ayudaramos. Seria bueno poder ver como configuraste las formulas de cada componente salarial y como hiciste la estructura salarial. Podemos ponernos en contacto? soy de Medellin. Saludos.

[volkswagner]

I think it’s best if you can balance your salary structure to put the amounts into the correct accounts via the built in Payroll entry. To track items per employee ther is a setting for that “Process Payroll Accounting Entry based on Employee”.

There’s some good info in this thread.

[MrMauro]

Hey @volkswagner, that’s a great reference, thank you for that.

I think I have missed that very recent thread when I was searching in the web and in this forum. To me it is quite hard to know the relevant keywords used in other countries that may refer to the same concept in ours. In this case I noticed that Employer Contributions is the key term.

Back to the issue, I think I’m using the same approach described by @peterg here and that you tried there.

I have to implement an extra server script as here in Colombia, we need to track the specific organization the employee has chosen for every contributions. All it does it to set the Party in the payables (And in the expenses, which may not be something ERPNext expects, though) so when can use the standard reports of ERPNext to query the information.

My real issue here is that when paying those contributions, they are not reconciled. Then I must be missing something here.

Again, thank you @volkswagner for taking the time to respond and your references.

[volkswagner]

I’m not well-versed in compliance.

How many choices/suppliers are there per salary component?

Is it feasible to create a unique account for each vendor under a parent account for the deduction type?

One other thing that comes to mind is creating a custom doctype for employee details. If you have the suppliers with associated deduction type, you can create custom reports to summarize the details of the ledger accounts.

I can’t add anything about the payroll payable account type, but I can confirm mine is blank. I’m not sure if the setting below is supposed to help with the HR’s limitation on grouping.

What method are you using for payroll? Are you creating salary slips individually or are you creating them in bulk via the Payroll Entry doctype? Additionally, do you submit the Salary Slips via the Payroll Entry?

EDIT: I wish I read your github feature request before replying.

Modifying the accounting journal entries seems “scary” to me. This is the only reason for my suggestions to try other means.

I think your feature request makes perfect sense.

Can you post your script for modifying the Payrol Journal Entry?

[peterg]

It’s amazing to see all the different ways folks approach payroll structures. If this is working for you, that’s all that matters. I don’t think it would work where I am, though. My auditors would object to this way of structuring payroll.

Specifically, they would say that this journal entry represents a financial relationship with employees, not with suppliers. Paying employees does not itself create a debt to a third party. Instead, what’s actually happening here has to be one of two things: either (1) a withholding, or (2) a deduction for non-cash compensation (i.e., benefits).

In the case of a withholding (taxes and the like), this money would be deposited someplace in the employees name. The “party”, if any, should be the employee, not the place where the deposit is made.

In the case of a deduction for benefits, the purchase you’re making on behalf of your employees still requires an invoicing process. You can hold the deduction separately in a liability account in anticipation of the cost, but the debt to the supplier happens when the supplier bills you, not when pay your staff.

All that said, I don’t know specifically what’s happening here. I’m not an accountant, and I’m definitely not a Colombian accountant. The best advice we can give is to check with your regulators. If you can show us what the accounting entries are supposed to look like, we can help translate that into ERPNext/HRMS.

[volkswagner]

@peterg I don’t want to hijack this thread (hopefully still relevant).

I considered @MrMauro may want to use purchase invoice, but that seems to open another “can of worms”. Purchase Invoice will need to include Items from the Items table. Is it practical to add employee benefits to the Item table?

[peterg]

Yeah, definitely. I think that’s usually exactly what’s happening when employers purchase things on behalf of employees, I would think usually there’s an invoice generated from the supplier to the company. That’s when the payable debt accrues.

Withholding for taxes and the like would probably work differently, and I’m sure there’s a lot of locale-specific blurry middle ground as well.

As far as adding items to the invoice table goes, it can be as simple or as complicated as the implementation needs. I tend not to create Item types for non-stock purchases and just enter in whatever the invoice lists as free text.

[volkswagner]

@peterg how are you able to add items “via free text”? I’m not able to add sales invoice items that don’t exist in item list.

[peterg]

There are two fields in the Sales/Purchase Invoice Item child doctype, item (Link) and item_name (Data). So long as you type in something in item_name, you don’t need a linked item.

[volkswagner]

Thank you for pointing this out. It’s valuable information. I’ll be using it when necessary.

[MrMauro]

Hey guys, thank you so much for your responses. I’m sorry it took me a while to respond as I’m struggle a bit articulating what I’m trying to say.

After writing this I realized I went to long, but I hope it can provide some usefulness to other in similar situations.

Following the advise of @peterg I think it is better if I describe a context.

This is what we need as a result:

• To track the costs for every employee including salary, bonuses/related and legal contributions on behalf of them. Each employee chooses to which supplier we should pay those contributions that include among others retirements plans, health coverage, etc. Every year, we have to provide this information detailed to our fiscal authority.
• To track how much we owe the employees:
  • At the time the Payroll is processed (Normally it is the salary, but also we should include extras like bonuses, and we also have to deduct some of their share on the contributions)
  • Paid at another point of the year (We should provide and extra half salary paid every six months, money for vacations, etc)
• To track how much we owe in contributions to the suppliers.
  This is a key point: By-laws mandate us to pay to a single entity every month the contributions collected (For all the employees). Let’s call it “Collector”. That entity is responsible to transfer the resources to the suppliers.

Here is how we achieve the above:

• The standard Payroll module generates the journal for each employee. We can’t use a single journal for a group of employees as we won’t be able later to track the amounts of expenses and contributions for each one of them, as we need to match an electronic document we have to provide for our fiscal authority. This is not a big problem, as we have very few employees and the actual procedure is quite fast.
• We have a custom server script for the journals (I’ll post it briefly @volkswagner), whose sole purpose is to catch the Journals generated by the Payroll before submission to perform this:
  • Append an extra field to the journal that identifies the Employee.
  • For each one of the accounting entries, when a Payable account is identified by the script, it writes the Supplier type for it and the Expected supplier for this account. That info comes from extra fields added to the corresponding Account Charts. This is not ideal, though, as for a type of contributions multiple suppliers can exists, and employees can change it requiring to modify their account on the Salary Component assignment on the Payroll module. That’s is why I proposed this.
• Every month, when have to pay the contributions, we take advantage of the extra field that identifies the “collector” on a Journal. Each entry of the journal, records for the related Payment accounts the amounts due. The total is deducted from our company bank.

That’s it. As the Payable accounts have a correct Type (supplier) and identify the actual Supplier, the reports en ERPNext work great to keep track of everything. The same goes for the other values due for each employee (except the Salary that is tracked by the Account that can’t be se as Payable according to this).

Our nuances are the following:

• The issue of not being able to use a Payable account for the salaries due.
• It takes quite a time to generate the Journal with the payment of all the contributions, but the manual process helps to check the amounts being paid are correct.
• The reconciliation for the contributions has to be done manually. But I have learned the process can be done using the Payment Reconciliation Tool.

Reflecting on the valuable information provided by you guys:

But I’m aware of the points that @peterg have raised regarding the auditors objections. As I understand, the process on the accounting books should follow a causality with the “act” that originates those entries. In our case, we have to report to the our fiscal government agency one electronic document for each employee, if we want those associated costs to be available to be deducted from our company’s income tax. Happily with our current implementation the single Journal matches the electronic document we have to provide on every transaction per employee.

And our payment to the “Collector” of the contributions also matches the document they provide with the result of the payment. This is the actual dispendious part here, as it normally requires checking balances, performing value roundings, etc.

Also, on the point made by @peterg regarding that the debt to the suppliers is happening when we receive the invoice from them is the part that may not happen locally in our country. We never get any document from them. I guess that is why it makes sense to pay to a “Collector” or “middle supplier” with the amounts due, as nobody but us known the amount due for each contribution. See, the value may change if an employee is absent, or receives bonuses, among many other variable things that happen and are only know at the time the Payroll is processed.

I tried the idea of using an Purchase invoice to setup the payment to the “Collector”, but I abandoned it quickly, as the interface of the changing values was not as easy to navigate compared to a plain Journal.

Anyway, while this works for us, it surely can be improved. Hopefully I grow more confident on my programming skills to contribute with a viable solution. Without any doubt, the Payroll has been the more challenging part of implementing ERPNext here.

Thank you for your help!

[MrMauro]

Here I’, posting the current server script we use to perform the modifications on the Journal Entry produced by the Payroll Module before it is posted.

Please excuse the Spanglish and some of the redactions.
While it is far from perfect, it helps in our use case.

This is only meant as a reference, and should not be used with proper understanding of what is doing as it is heavily reliant in our specific implementation.

"""
GL Entry
Mauricio Vidal
Version 2025-01-23

--------------------------------------------------------------------------------
Propósito:
--------------------------------------------------------------------------------

Modificar el `Journal Entry` que genera el `Payroll Entry` antes de que sea
Submitted, para asegurar que los campos tengan el contenido esperado en la
implementación específica de ERPNext para *** que en general
consiste en:

1. Exclusivamente para los Journals generados por Payroll
   1.1. Cada uno de sus registros deben identificar al empleado/eps/caja/etc
        para que las cuentas por pagar puedan identificar al causante de ello.
   1.2. El Journal identifica al tercero que corresponde según la definición
        formal de la DIAN.

--------------------------------------------------------------------------------
Implementación:
--------------------------------------------------------------------------------

Este script debe estar guardado en la sección de ERPNext `Server Script` en un
registro con los siguientes datos:

`Name`: `Journal Entry digest` (O cualquier otro descriptivo)
`Script Type`: `Doctype Event`
`Reference Document Type`: `Journal Entry`
`DocType Event`: `Before Validate`

--------------------------------------------------------------------------------
Requerimientos:
--------------------------------------------------------------------------------

- La tabla `DIAN terceros` definida.
- Campos adicionales en `Account` para establecer al tipo y tipo de tercero
  (este último en caso que sea necesario) a los registros de tipo `Payable`.
- Campos adicionales en `Cost Center`.
- La existencia del tipo de documento `Business Unit` asociado a `Dimensions`.

--------------------------------------------------------------------------------
Limitaciones:
--------------------------------------------------------------------------------

- Solo funciona para un empleado por documento generado por el Payroll.
  Requerimos que sea así para asegurar que corresponda al documento electrónico
  transmitido a DIAN.

"""

VA_ENABLE_CONSOLE_DEBUG = False

VA_DOCTYPE_ACCOUNT_FIELD_REQUIRED_PARTY_TYPE = 'custom_required_party_type'
VA_DOCTYPE_ACCOUNT_FIELD_REQUIRED_PARTY = 'custom_required_party'
VA_DOCTYPE_COST_CENTER_FIELD_ASSOCIATED_BUSINESS_UNIT = 'custom_associated_business_unit'
VA_DOCTYPE_GL_ENTRY_FIELD_BUSINESS_UNIT = 'business_unit'

VA_DOCTYPE_DIAN_TERCEROS_NAME = 'DIAN terceros'
VA_DOCTYPE_EMPLOYEE_FIELD_DIAN_TERCERO = 'custom_dian_tercero'
VA_DOCTYPE_JOURNAL_FIELD_DIAN_TERCERO = 'custom_dian_tercero'

# Al parecer los objetos de los campos en ERPNext no son siempre None cuando no
# están asignados, por lo que acá hacemos explícito el valor None como cadena.
# Lo asignamos a `doc` porque por alguna razón, no estaría disponible a otros
# objetos dentro de este script.
doc.VA_DEFINE_NONE = "None"


def aux_decide_value(field, value_if_none, keep_original: bool = False):
    """
    This auxiliar function helps to deal with the ambiguous None, Empty strings.
    Returns:
    - The value of the `field` if it is not None or ''.
    - The string from `value_if_none`, otherwise.
    """
    if field is None or field == '':
        return value_if_none
    if keep_original:
        return field
    return str(field)

# Workaround para hacer disponible la función anterior a otros objetos
doc.va_aux_decide_value = aux_decide_value


def aux_set_required_value_to_field_on_register(
    doc,
    register,
    current_value,
    value_required_by_specification,
    field_name,
):
    """
    This auxiliar function helps to set a value for a field.

    doc: The current document.
    register: all the fields on the doc.
    current_value: The variable that is going to be updated if necessary.
    value_required_by_specification: The variable that holds the value that
        is the required according to the table of the specification.
    field_name: The string that identifies the field to be updated on the
        document.

    Returns:
    - The updated current value according to the preference on the
      specification.
    """

    # The value is only updated, if that is defined on the specification
    if value_required_by_specification != doc.VA_DEFINE_NONE:
        register.set(field_name, value_required_by_specification)
        # Vuelve a cargar la variable modificada
        return doc.va_aux_decide_value(register.get(field_name), doc.VA_DEFINE_NONE)

    else:
        return current_value

# Workaround para hacer disponible la función anterior a otros objetos
doc.va_aux_set_required_value_to_field_on_register = aux_set_required_value_to_field_on_register


"""
Here we check if this Journal Entry is generated by the Payroll module to
perform the desired changes on its records.
The mechanism implemented is based on the assumption that is any of the
Journal records has a reference to a `Payroll Entry` document, then it is
considered produced by the Payroll module.
"""
# Flag with the result
has_any_payroll_entry = False
more_than_one_employee_found = False

# We will also determine the employee and tercero IDs.
current_employee_id = None
current_dian_tercero = None

# Obtenemos todos los registros contables en el Journal
got_current_accounting_records = doc.va_aux_decide_value(doc.get('accounts'), doc.VA_DEFINE_NONE, keep_original=True)

for i in got_current_accounting_records:
    # Check if the record has been marked by the Payroll.
    if i.get('reference_type') == 'Payroll Entry':

        # It means this whole Journal is generated by the Payroll module
        has_any_payroll_entry = True

        # This record should also have the party defined as Employee
        current_employee_type = i.get('party_type')
        if current_employee_type == 'Employee':
            if current_employee_id is not None:
                more_than_one_employee_found = True
            # Get the Employee ID according to the Payroll module
            current_employee_id = i.get('party')
            # Now, determine the DIAN Tercer assigned to that Employee
            current_dian_tercero = frappe.db.get_value('Employee', current_employee_id, VA_DOCTYPE_EMPLOYEE_FIELD_DIAN_TERCERO)
        else:
            frappe.throw("Unexpected party type for the Journal register that contains the reference to the Payroll Entry: '" + current_employee_type + "'. Please contact our support TEAM")

if more_than_one_employee_found:
    frappe.throw("The Journal will contain data for more than one employee, which is not currently implemented. Please contact our support TEAM.")


"""
Update of the Journal.
Only if it is produced by the Payroll module.
"""
if has_any_payroll_entry is True:

    # Asignamos el tercero según el registro de la DIAN para el empleado.
    doc.set(VA_DOCTYPE_JOURNAL_FIELD_DIAN_TERCERO, current_dian_tercero)

    # Recorremos las cuentas contables para modificar lo pertinente
    for i in got_current_accounting_records:

        """
        Primero nos concentramos en establecer el valor de los campos del
        registro actual y los valores según las especificaciones asociadas.
        """

        # Por claridad, definimos el contenido de lo que tenemos actualmente
        got_current_voucher_type = doc.va_aux_decide_value(i.get('voucher_type'), doc.VA_DEFINE_NONE)
        got_current_account = doc.va_aux_decide_value(i.get('account'), doc.VA_DEFINE_NONE)
        got_current_party_type = doc.va_aux_decide_value(i.get('party_type'), doc.VA_DEFINE_NONE)
        got_current_party = doc.va_aux_decide_value(i.get('party'), doc.VA_DEFINE_NONE)
        got_current_cost_center = doc.va_aux_decide_value(i.get('cost_center'), doc.VA_DEFINE_NONE)
        got_current_business_unit = doc.va_aux_decide_value(i.get(VA_DOCTYPE_GL_ENTRY_FIELD_BUSINESS_UNIT), doc.VA_DEFINE_NONE)
        got_current_project = doc.va_aux_decide_value(i.get('project'), doc.VA_DEFINE_NONE)
        got_current_reference_type = doc.va_aux_decide_value(i.get('reference_type'), doc.VA_DEFINE_NONE)

        # Solo para debug
        if VA_ENABLE_CONSOLE_DEBUG:
            frappe.throw("En el documento, Type: '" + got_current_voucher_type + "' Account: '" + got_current_account + "' Party Type: '" + got_current_party_type + "' Party: '" + got_current_party + "' Cost Center: '" + got_current_cost_center + "' Business Unit: '" + got_current_business_unit + "'Project: " + got_current_project + "' Reference Type: '" + got_current_reference_type + "'")

        # Obtenemos las definiciones que nos interesan evaluar para la cuenta actual.
        got_from_account_definition = frappe.db.get_value('Account', got_current_account, [VA_DOCTYPE_ACCOUNT_FIELD_REQUIRED_PARTY_TYPE, VA_DOCTYPE_ACCOUNT_FIELD_REQUIRED_PARTY, 'root_type', 'account_type'] )
        got_party_type_from_account_definition = doc.va_aux_decide_value(got_from_account_definition[0], doc.VA_DEFINE_NONE)
        got_party_from_account_definition = doc.va_aux_decide_value(got_from_account_definition[1], doc.VA_DEFINE_NONE)
        got_account_root_type_from_account_definition = doc.va_aux_decide_value(got_from_account_definition[2], doc.VA_DEFINE_NONE)
        got_account_type_from_account_definition = doc.va_aux_decide_value(got_from_account_definition[3], doc.VA_DEFINE_NONE)

        # Obtenemos las definiciones que nos interesan evaluar para el  centro de costo
        # actual.
        got_from_cost_center_definition = frappe.db.get_value('Cost Center', got_current_cost_center, VA_DOCTYPE_COST_CENTER_FIELD_ASSOCIATED_BUSINESS_UNIT )
        got_business_unit_from_cost_center_definition = doc.va_aux_decide_value(got_from_cost_center_definition, doc.VA_DEFINE_NONE)

        # Obtenemos las definiciones que nos interesan evaluar para el proyecto actual.
        got_from_project_definition = frappe.db.get_value('Project', got_current_project, 'cost_center' )
        got_cost_center_from_project_definition = doc.va_aux_decide_value(got_from_project_definition, doc.VA_DEFINE_NONE)

        """
        Todas los registros contables deben tener una unidad de negocio
        asignada según la definición del centro de costo.
        """

        # La unidad de negocio, si no está registrada pero sí lo está el centro de costo, se toma de la definición para el centro de costo
        if got_current_business_unit == doc.VA_DEFINE_NONE and got_current_cost_center != doc.VA_DEFINE_NONE:
            got_current_business_unit = doc.va_aux_set_required_value_to_field_on_register(doc, i, got_current_business_unit, got_business_unit_from_cost_center_definition, VA_DOCTYPE_GL_ENTRY_FIELD_BUSINESS_UNIT)

        """
        Queremos evitar cambios que no sean los estrictamente esperados.
        Por ello filtramos para establecer los casos en que deben tratarse.
        """

        # Solo nos interesan las cuentas por pagar
        if got_account_root_type_from_account_definition == 'Liability':

            # Solo si el tipo de tercero ni el tercero están especificados actualmente
            if got_current_party_type == doc.VA_DEFINE_NONE and got_current_party == doc.VA_DEFINE_NONE:

                # Establece el tipo de tercero según la definición
                got_current_party_type = doc.va_aux_set_required_value_to_field_on_register(doc, i, got_current_party_type, got_party_type_from_account_definition, 'party_type')

                # Para el tipo empleado, establece el empleado
                if got_party_type_from_account_definition == 'Employee':
                    got_current_party = doc.va_aux_set_required_value_to_field_on_register(doc, i, got_current_party, current_employee_id, 'party')

                # O establecemos el proveedor si es lo solicitado por la definición según su tipo Supplier
                elif got_party_type_from_account_definition == 'Supplier':
                    got_current_party = doc.va_aux_set_required_value_to_field_on_register(doc, i, got_current_party, got_party_from_account_definition, 'party')

    # Solo para debug.
    # Sirve para que ERPNext reporte un error (en el recuadro de error cerca al botón del Payroll Entry que genera los Salary Slip) mostrando en detalle el estado de este punto.
    if VA_ENABLE_CONSOLE_DEBUG:
        assert False

[peterg]

Hi again, thanks for sharing more details.

At the end of the day, Frappe’s greatest strength is its flexibility. It is designed to manage exactly the kind of information structures you are describing, and there’s no single correct way to do it.

For most of us, though, that flexibility has some hard external constraints imposed by accounting standards. In the places I work, there is a definite correct answer to how this all should be booked.

What you’re describing sounds like a fairly standard withholding, and in my jurisdiction it would be incorrect to book that as an account payable to a supplier. It may not be incorrect where you are, of course. If you’re confident that the accounting is right, can you restate what you’re struggling with? Is it still the two problems you listed in your first post?

[volkswagner]

@MrMauro

Our nuances are the following:

• The issue of not being able to use a Payable account for the salaries due.
• It takes quite a time to generate the Journal with the payment of all the contributions, but the manual process helps to check the amounts being paid are correct.
• The reconciliation for the contributions has to be done manually. But I have learned the process can be done using the Payment Reconciliation Tool.

Doesn’t the payroll setting to account for each employee help with item 1?

As mentioned there’s often more than one way to reach a goal. I tend to avoid modifying the accounting entries in favor of using custom reports to present the data in a fashion that meets my needs.

I can imagine creating a custom doctype to handle the employee deductions/contributions to specific vendors. Others may choose to customize the employee doctype.

You could join a query on the employees choice of vendors for each deduction, then break out the amounts in a custom report. The main source would be the tabSalary Detail. I have a naming scheme for Salary Slip which includes the employee full name. I can extract this value and group results by component,employee. The following code would need to be modified to extract employee name and join with the custom table for vendors associated to the deduction.

I also consider creating unique accounts for the deductions as this will show in financial reports. If the vendor changes, yes you’d have to modify the salary structure and/or the salary structure assignment. This would also keep a historical record of the changes since salary structure assignments can be date dependant.

select  mid(parent,10,LENGTH(parent)-15) as employee,
salary_component as component,
sum(amount),
max(year_to_date) as ytd
from `tabSalary Detail`
where parenttype like "Salary Slip"
AND salary_component in ("Fed Income Tax","Medicare","NYS FL","NYS Income Tax","NYSI","SalaryBase","Social Security")
group by employee,component