FrontAccounting forum — Deleting a fiscal year problem

Re: Deleting a fiscal year problem

null@example.com (madosk) — Thu, 03 Sep 2020 15:43:16 +0000

Hi all again,
Unfortunately i couldn't solve this problem yet. I modified sql to find invoices, payments and credit notes which allocated in year to delete.

$sql = "SELECT trans_no, type 
            FROM ".TB_PREF."debtor_trans 
            WHERE tran_date <= '$to' 
            AND (round((ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount), 3) = round((SELECT SUM(amt) FROM ".TB_PREF."cust_allocations WHERE date_alloc <= '$to' AND trans_no = trans_no_to AND    type = trans_type_to), 3) 
            OR round((ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount), 3) = round((SELECT SUM(amt) FROM ".TB_PREF."cust_allocations WHERE date_alloc <= '$to' AND trans_no = trans_no_from AND    type = trans_type_from), 3))";

But it's not enough to find right transactions to delete. Still I have to separate some invoices and payment.
So I have an invoice (1800$) invoiced in 2015 and i have a payment (800$) paid in 2015. This payment is fully allocated to this invoice but remaining balance of this invoice (1000$) paid in 2016.
This sql find 800$ payment to delete because this payment is fully allocated in 2015 but invoice is still alive because it's not fully allocated in 2015. I have to keep this payment to find right balance at the end of 2015.
How can i do this? Hope i could explain the problem which i couldn't solve.

Thanks in advance.

Re: Deleting a fiscal year problem

null@example.com (madosk) — Wed, 20 May 2020 16:27:58 +0000

This fix only delete allocation. Payment must be deleted too.

$sql = "SELECT trans_no, type FROM ".TB_PREF."debtor_trans WHERE tran_date <= '$to' AND 
        (ov_amount + ov_gst + ov_freight + ov_freight_tax + ov_discount) = 
        (SELECT SUM(amt) FROM ".TB_PREF."cust_allocations WHERE date_alloc <= '$to' AND trans_no = trans_no_to AND
        type = trans_type_to)";

This sql can not find the payment to delete.

Re: Deleting a fiscal year problem

null@example.com (kvvaradha) — Wed, 20 May 2020 15:32:29 +0000

Looks like this is a strange issue. I feel the below code is missing the cust allocation.

  while ($row = db_fetch($result))
    {
        if ($row['type'] == ST_SALESINVOICE)
        {
            $deliveries = get_sales_parent_numbers($row['type'], $row['trans_no']);
            foreach ($deliveries as $delivery)
            {
                $sql = "DELETE FROM ".TB_PREF."debtor_trans_details WHERE debtor_trans_no = $delivery AND debtor_trans_type = ".ST_CUSTDELIVERY;
                db_query($sql, "Could not delete debtor trans details");
                $sql = "DELETE FROM ".TB_PREF."debtor_trans WHERE trans_no = $delivery AND type = ".ST_CUSTDELIVERY;
                db_query($sql, "Could not delete debtor trans");
                delete_attachments_and_comments(ST_CUSTDELIVERY, $delivery);
            }        
        }    
        $sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE trans_no_from = {$row['trans_no']} AND trans_type_from = {$row['type']}";
        db_query($sql, "Could not delete cust allocations");
.
.
.
}

And may be it should be like this

while ($row = db_fetch($result))
    {
        if ($row['type'] == ST_SALESINVOICE)
        {
            $deliveries = get_sales_parent_numbers($row['type'], $row['trans_no']);
            foreach ($deliveries as $delivery)
            {
                $sql = "DELETE FROM ".TB_PREF."debtor_trans_details WHERE debtor_trans_no = $delivery AND debtor_trans_type = ".ST_CUSTDELIVERY;
                db_query($sql, "Could not delete debtor trans details");
                $sql = "DELETE FROM ".TB_PREF."debtor_trans WHERE trans_no = $delivery AND type = ".ST_CUSTDELIVERY;
                db_query($sql, "Could not delete debtor trans");
                delete_attachments_and_comments(ST_CUSTDELIVERY, $delivery);
            }        
                      $sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE trans_no_to = {$row['trans_no']} AND trans_type_to = {$row['type']}";  // newly added 
        db_query($sql, "Could not delete cust allocations");  // newly added. 
        }    
        $sql = "DELETE FROM ".TB_PREF."cust_allocations WHERE trans_no_from = {$row['trans_no']} AND trans_type_from = {$row['type']}";
        db_query($sql, "Could not delete cust allocations");
}

I have added two more lines added newly to collect the respective invoice allocation entires delete. Like the same for supplier allocations.

If it helps feedback us to fix it in core.

Re: Deleting a fiscal year problem

null@example.com (madosk) — Wed, 20 May 2020 14:47:31 +0000

After deletion year 2014 customer has records in debtor_trans table. For example;

debtor_trans
trans_no: 15496
type: 12
tran_date: 2014-09-29
ov_amount: 785
alloc: 785

cust_allocations
amt: 335
date_alloc: 2014-09-29
trans_no_from: 15496
trans_type_from: 12
trans_no_to: 14931
trans_type_to: 10

amt: 450
date_alloc: 2014-09-29
trans_no_from: 15496
trans_type_from: 12
trans_no_to: 15472
trans_type_to: 10

But 15472, 14931 transactions do not exist in debtor_trans. Why this payment didn't deleted? whereas it payed in 2014 and allocated to invoices in 2014.

By the way invoices are still alive which are processed in 2014 and allocated in 2015. This part is working correct i think but payments part is not.

Re: Deleting a fiscal year problem

null@example.com (madosk) — Wed, 20 May 2020 13:56:04 +0000

I have compatibility problem. I 'm using 2.3.26 but commited file compatible with 2.4.x.
2.3.26 neither has add_journal method nor 0_journal table.
I removed this part of code and tried to delete year but unfortunately customer balance calculated wrong.
I'm investigating why balance is wrong. I'll inform you more if i find something.

Re: Deleting a fiscal year problem

null@example.com (apmuthu) — Sun, 17 May 2020 16:38:51 +0000

Updated Wiki.

Re: Deleting a fiscal year problem

null@example.com (kvvaradha) — Fri, 15 May 2020 11:12:28 +0000

@joe, your solution is good. Instead of rewriting it as journal entry. We keep the invoices alive and it's allocations alive. So it will be removed if they remove next fiscal year.

@madosk, This is definitely a time saving way for you to implement it. Hope your problem fixed with this. And post an reply after using this updated program. It would be a solved solution for future referring users.

Re: Deleting a fiscal year problem

null@example.com (madosk) — Fri, 15 May 2020 10:06:11 +0000

@joe: Thanks for solution. I have been working on it for a few days. Your solution is ver simple and i 'll try asap. Will it work for payed before and invoiced next year transactions?

Re: Deleting a fiscal year problem

null@example.com (joe) — Fri, 15 May 2020 08:44:52 +0000

The allocations has been revised and should now work. Committed to stable repo. The fixed file can be downloaded here.

Joe

Re: Deleting a fiscal year problem

null@example.com (joe) — Fri, 15 May 2020 07:36:22 +0000

Oh yes, I see. The alloc field got stamped in the next year and when the invoice was not allocated by end of last year.
I will have to look at the allocation routines instead. Thanks.

Joe

Re: Deleting a fiscal year problem

null@example.com (kvvaradha) — Fri, 15 May 2020 07:17:43 +0000

How ever there is a problem with payment of next year for previous year invoice.

Let's take his example. He issued several invoices at the end of last year and collected the payment in next year. Which means the system needs to make one journal entry for a customer at least to record the invoice balances as opening balance and that should be allocated in the next year.

Re: Deleting a fiscal year problem

null@example.com (joe) — Fri, 15 May 2020 06:40:49 +0000

Hello again.

I have now went through the deleting fiscal year procedure. I have found no problems here.
Here is a list over what happens when processing deleting of a fiscal year:

Delete Fiscalyear

1. Backup is done
2. All sales orders and details, up to last date, that are fully delivered are removed. Attachments are removed.
3. All purchase orders and details, up to last date, that are fully received are removed. Attachments are removed.
4. All GRN receivals, up to last date, are removed.
5. All debtor trans and details, up to last date, that are fully allocated are removed. Attachments are removed.
6. All cust allocations, up to last date, are removed.
7. All supp trans and details, up to last date, that are fully allocated are removed. Attachments are removed.
8. All supp allocations, up to last date, are removed.
9. All workorders, up to last date, and closed.
   a. All workorder issues are removed. Attachments are removed.
   b. All workorder manufacture are removed. 
   c. All workorder requirements are removed.
   d. Workorders removed. Attachments are removed.
10. All stock moves, up to last date, are removed and converted to a closing stock move.
11. All voided items, up to last date, are removed.
12. All trans tax details, up to last date, are removed.
13. All exchange rater, up to last date, are removed.
14. All cust and supp sub gl transactions are retrieved by account, removed and an end transaction are inserted. 
15. All bank trans, up to last date, are removed and converted to a closing balance trans.
16. All ausit trail, up to last date, are removed.
17. All comments, up to last date, are removed.
18. Finally the fiscalyear is removed.

Joe

Re: Deleting a fiscal year problem

null@example.com (joe) — Thu, 14 May 2020 10:11:43 +0000

This is a difficult task. I remember writing these routines years back and we really tried to get it all at that time.
I will try to look into it asap.

Joe

Re: Deleting a fiscal year problem

null@example.com (apmuthu) — Thu, 14 May 2020 09:28:05 +0000

@joe: Need to refurbish the end of year routines to make sure that the client balances are automatically made along with unpaid and partially paid invoice data for allocation of payments in the subsequent year. This needs to be done for suppliers as well. The appropriate stock moves too need to be taken care of.

Re: Deleting a fiscal year problem

null@example.com (madosk) — Wed, 13 May 2020 11:52:16 +0000

Yes you're right. Thanks for the idea
I'll inform you when finished.