Automatic generation of distribution keys for accounting operations linked to the Payroll modules

[lomamech]

Automatic generation of distribution keys for accounting operations linked to the Payroll modules

When implementing the Payroll module, we had to determine for each heading the accounts of third parties and the accounts of third parties or charges, which we can represent in the following table. For the generation of distribution keys, we will focus more on the items that are a responsibility of the company. Here is the list of items that are supported by the company in the configuration of the IMCK Tshikaji database

It should be noted that the CNSS QPO withholding (quotte part ouvrière) and the IPR (Professional Income Tax) is not a charge of the company but that of the employee. With the modules linked to Payroll in BHIMA, there is the Account configuration option which allows you to configure the [66110011] Remuneration Personnel account to be used during the automatic drafting of accounting entries.

A. ACCOUNTING DOCUMENTS FOR THE PAYROLL 1. Commitment For salary commitments, the employee salary account is used, this account will be credited and the following expense accounts will be debited

For cost accounting, during the cost center analysis, it will be necessary to know how to classify each employee in the cost center corresponding to his department and to break down the costs.

2. Employee deductions

Once the commitments are made, it is appropriate to retain all the items that are chargeable to the employees, for this the employee accounts will have to be debited.

For employee deduction entries generally these are not a business expense.

3. Social security charges on remuneration

In the same way, for the case of commitments, an analysis will have to be made for the distribution of the expense accounts to the cost centers.

B. USE OF DATA FROM THE PAYMENT TABLE TO GENERATE AUTOMATIC DISTRIBUTION KEYS

If we were to rely on the data in the entry journal, we would have difficulty making the allocation for commitment postings and totally impossible for payroll tax postings on remuneration. But based on the payment table, we have the possibility of grouping together and having precise information for each employee, by department, by cost center. We had run the SQL query against the IMCK Tshikaji database, after assigning each employee to a department and completing the correspondence between cost centers and departments.

SELECT acc.costCenterLabel AS cost_cent_aux, cc.label AS cost_aux_principal, SUM(rbp.value) AS valeur
FROM rubric_paiement AS rbp
JOIN paiement AS p ON p.uuid = rbp.paiement_uuid
JOIN payroll_configuration AS pll ON pll.id = p.payroll_configuration_id
JOIN employee AS emp ON emp.uuid = p.employee_uuid
JOIN service AS ser ON ser.uuid = emp.service_uuid
JOIN patient AS pat ON pat.uuid = emp.patient_uuid
JOIN rubric_payroll AS rub ON rub.id = rbp.rubric_payroll_id
JOIN account AS a ON a.id = rub.expense_account_id
JOIN service_cost_center AS scs ON scs.service_uuid = ser.uuid
JOIN cost_center AS cc ON cc.id = scs.cost_center_id
LEFT JOIN reference_cost_center AS rcs ON rcs.cost_center_id = cc.id
JOIN (
    SELECT aa.account_id, cc.id AS cost_center_id, cc.label AS costCenterLabel, IF(cc.is_principal, cost_center_id, NULL) AS principal_center_id
    FROM cost_center AS cc
    JOIN reference_cost_center AS rfc ON rfc.cost_center_id = cc.id
    JOIN account_reference AS ar ON ar.id = rfc.account_reference_id
    JOIN account_reference_item AS ritem ON ritem.account_reference_id = ar.id
    JOIN account AS a ON a.id = ritem.account_id
    JOIN (
    SELECT a.id AS account_id, a.label, a.number
    FROM account AS a
    WHERE a.type_id <> 6
    ) AS aa ON aa.number LIKE CONCAT(a.number ,'%')
    WHERE ritem.is_exception = 0
) AS acc ON acc.account_id = a.id

WHERE DATE(pll.dateFrom) >= DATE('2018-01-01') AND DATE(pll.dateTo) <= DATE('2018-12-31') AND (cc.is_principal = 1 OR rcs.is_cost = 1 OR rcs.is_cost IS NULL)
GROUP BY acc.cost_center_id, cc.id
ORDER BY acc.costCenterLabel, cc.label ASC;

Based on the following information, it is desirable that the distribution keys can be generated automatically and by fiscal year.

[lomamech]

@jniles @mbayopanda @jmcameron

[jniles]

My personal feeling is that the payroll for an employee that works in a service should be a direct cost to the service's cost center, since without the service, we wouldn't have hired the employee. I understand that the way BHIMA stores the payroll transaction makes this information impossible to recover right now, but creating a transaction per employee should fix that (https://github.com/IMA-WorldHealth/bhima/issues/5965) and give us accurate cost information per service without having to do the gymnastics of computing allocation keys.