FrontAccounting forum — Sales Direct Invoice has due_date and delivery_date bug

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-26T03:30:48Z

@joe

Yes, you are correct. Everything looks correct and the DB is not goofing up the due_date or delivery_date no matter how hard I try.
Thank you for dropping everything to work on this problem. You have done an awesomely quick job and it is greatly appreciated by me.

Have a great day!

/JimmyC

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-26T00:48:01Z

@JimmyC

The file /sales/includes/db/sales_invoice_db.inc has now been fixed and committed to stable.

A Fixed file can be downloaded here

Joe

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-26T00:30:58Z

@Joe

Thank you! When you post it, I'll try it as quick as I can and send you feedback.

/JimmyC

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-26T00:01:39Z

@JimmyC

Thanks JimmyC. I have found the bug now. There is a zero amount tax record with an amount of 50 that are inserted in the trans_tax-details table.
The tax amount of this is 0 and is inserted as a 0 amount of tax with an unknown account. This record should never create a gl_trans.

I will fix that in a while.

/Joe

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-25T22:49:00Z

@Joe

Don't know if it would help but I tried to capture the sql_trail for just one entry. I exported it and pasted it in the code box below.

(599, 'SET sql_mode = 'STRICT_ALL_TABLES'', 1, ''),
(600, 'UPDATE 0_users SET last_visit_date='2019-03-25 18:28:28'\n        WHERE user_id='Admin'', 1, 'could not update last visit date for user Admin'),
(601, 'SET sql_mode = 'STRICT_ALL_TABLES'', 1, ''),
(602, 'SET sql_mode = 'STRICT_ALL_TABLES'', 1, ''),
(603, 'SET sql_mode = 'STRICT_ALL_TABLES'', 1, ''),
(604, 'SET sql_mode = 'STRICT_ALL_TABLES'', 1, ''),
(605, 'SET sql_mode = 'STRICT_ALL_TABLES'', 1, ''),
(606, 'SET sql_mode = 'STRICT_ALL_TABLES'', 1, ''),
(607, 'SET sql_mode = 'STRICT_ALL_TABLES'', 1, ''),
(608, 'SET sql_mode = 'STRICT_ALL_TABLES'', 1, ''),
(609, 'BEGIN', 1, 'could not start a transaction'),
(610, 'INSERT INTO 0_sales_orders (order_no, type, debtor_no, trans_type, branch_code, customer_ref, reference, comments, ord_date,\n        order_type, ship_via, deliver_to, delivery_address, contact_phone,\n        freight_cost, from_stk_loc, delivery_date, payment_terms, total, prep_amount)\n        VALUES ('930','0','27', '30','27', '','auto','','2019-03-24', '3', '1','Janet Gould','1445 Iva Street\\nBurton, MI 48509', '', '0', 'DEF', '2019-03-24','4','50','0')', 1, 'order Cannot be Added'),
(611, 'INSERT INTO 0_sales_order_details (order_no, trans_type, stk_code, description, unit_price, quantity, discount_percent) VALUES (930,30,'101', 'Donation -Tithe', 50,\n                1,\n                0)', 1, 'order Details Cannot be Added'),
(612, 'INSERT INTO 0_audit_trail (type, trans_no, user, gl_date, description, stamp)\n            VALUES('30', '930',1,'2019-03-24','', CURRENT_TIMESTAMP)', 1, 'Cannot add audit info'),
(613, 'UPDATE 0_audit_trail audit LEFT JOIN 0_fiscal_year year ON year.begin<='2019-03-24' AND year.end>='2019-03-24'\n        SET audit.gl_seq = IF(audit.id=4689, 0, NULL),audit.fiscal_year=year.id, stamp=CURRENT_TIMESTAMP WHERE type='30' AND trans_no='930'', 1, 'Cannot update audit gl_seq'),
(614, 'UPDATE 0_sales_orders SET version=version+1 WHERE order_no='930' AND version=0 AND trans_type=30', 1, 'Concurrent editing conflict while sales order update'),
(615, 'INSERT INTO 0_debtor_trans (\n        trans_no, type,\n        debtor_no, branch_code,\n        tran_date, due_date,\n        reference, tpe,\n        order_, ov_amount, ov_discount,\n        ov_gst, ov_freight, ov_freight_tax,\n        rate, ship_via, alloc,\n        dimension_id, dimension2_id, payment_terms, tax_included, prep_amount\n        ) VALUES ('930', '13',\n        '27', '27',\n        '2019-03-24', '2019-03-24', 'auto',\n        '3', '930', 50, '0', 0,\n        '0',\n        0, 1, '1', 0,\n        '6', '0', '4', '0', '0')', 1, 'The debtor transaction record could not be inserted'),
(616, 'INSERT INTO 0_audit_trail (type, trans_no, user, gl_date, description, stamp)\n            VALUES('13', '930',1,'2019-03-24','', CURRENT_TIMESTAMP)', 1, 'Cannot add audit info'),
(617, 'UPDATE 0_audit_trail audit LEFT JOIN 0_fiscal_year year ON year.begin<='2019-03-24' AND year.end>='2019-03-24'\n        SET audit.gl_seq = IF(audit.id=4690, 0, NULL),audit.fiscal_year=year.id, stamp=CURRENT_TIMESTAMP WHERE type='13' AND trans_no='930'', 1, 'Cannot update audit gl_seq'),
(618, 'INSERT INTO 0_debtor_trans_details (debtor_trans_no,\n                debtor_trans_type, stock_id, description, quantity, unit_price,\n                unit_tax, discount_percent, standard_cost, src_id)\n            VALUES ('930', '13', '101', 'Donation -Tithe',\n                1, 50, 0, \n                0, 0,'1013')', 1, 'The debtor transaction detail could not be written'),
(619, 'UPDATE 0_sales_order_details\n                SET qty_sent = qty_sent + 1 WHERE id='1013'', 1, 'The parent document detail record could not be updated'),
(620, 'INSERT INTO 0_stock_moves (stock_id, trans_no, type, loc_code,\n        tran_date, reference, qty, standard_cost, price) VALUES ('101', '930', '13', 'DEF', '2019-03-24', 'auto', '-1', '0','50')', 1, 'The stock movement record cannot be inserted'),
(621, 'INSERT INTO 0_trans_tax_details \n        (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate,\n            included_in_price, net_amount, amount, memo, reg_type)\n        VALUES ('13','930','2019-03-24',0,0,1,0,'50','0','auto',NULL)', 1, 'Cannot save trans tax details'),
(622, 'UPDATE 0_debtor_trans SET version=version+1\n            WHERE type='13' AND ((trans_no='930' AND version=0))', 1, 'Concurrent editing conflict'),
(623, 'INSERT INTO 0_debtor_trans (\n        trans_no, type,\n        debtor_no, branch_code,\n        tran_date, due_date,\n        reference, tpe,\n        order_, ov_amount, ov_discount,\n        ov_gst, ov_freight, ov_freight_tax,\n        rate, ship_via, alloc,\n        dimension_id, dimension2_id, payment_terms, tax_included, prep_amount\n        ) VALUES ('930', '10',\n        '27', '27',\n        '2019-03-24', '2019-03-24', 'Dn190148',\n        '3', '930', 50, '0', 0,\n        '0',\n        0, 1, '1', 0,\n        '6', '0', '4', '0', '0')', 1, 'The debtor transaction record could not be inserted'),
(624, 'INSERT INTO 0_audit_trail (type, trans_no, user, gl_date, description, stamp)\n            VALUES('10', '930',1,'2019-03-24','', CURRENT_TIMESTAMP)', 1, 'Cannot add audit info'),
(625, 'UPDATE 0_audit_trail audit LEFT JOIN 0_fiscal_year year ON year.begin<='2019-03-24' AND year.end>='2019-03-24'\n        SET audit.gl_seq = IF(audit.id=4691, 0, NULL),audit.fiscal_year=year.id, stamp=CURRENT_TIMESTAMP WHERE type='10' AND trans_no='930'', 1, 'Cannot update audit gl_seq'),
(626, 'INSERT INTO 0_debtor_trans_details (debtor_trans_no,\n                debtor_trans_type, stock_id, description, quantity, unit_price,\n                unit_tax, discount_percent, standard_cost, src_id)\n            VALUES ('930', '10', '101', 'Donation -Tithe',\n                1, 50, 0, \n                0, 0,'2025')', 1, 'The debtor transaction detail could not be written'),
(627, 'UPDATE 0_debtor_trans_details\n                SET qty_done = qty_done + 1\n                WHERE id='2025'', 1, 'The parent document detail record could not be updated'),
(628, 'INSERT INTO 0_gl_trans ( type, type_no, tran_date,\n        account, dimension_id, dimension2_id, memo_, amount) VALUES ('10', '930', '2019-03-24',\n        '4011', '6', '1', '', '-50') ', 1, 'The sales price GL posting could not be inserted'),
(629, 'INSERT INTO 0_gl_trans ( type, type_no, tran_date,\n        account, dimension_id, dimension2_id, memo_, amount, person_type_id, person_id) VALUES ('10', '930', '2019-03-24',\n        '1200', '0', '0', '', '50', '2', '27') ', 1, 'The total debtor GL posting could not be inserted'),
(630, 'INSERT INTO 0_trans_tax_details \n        (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate,\n            included_in_price, net_amount, amount, memo, reg_type)\n        VALUES ('10','930','2019-03-24',0,0,1,0,'50','0','Dn190148','0')', 1, 'Cannot save trans tax details'),
(631, 'INSERT INTO 0_gl_trans ( type, type_no, tran_date,\n        account, dimension_id, dimension2_id, memo_, amount) VALUES ('10', '930', '2019-03-24',\n        '', '0', '0', '', '0') ', 1, 'A tax GL posting could not be inserted'),
(632, 'REPLACE 0_refs SET reference='Dn190148', type='10', id='930'', 1, 'could not update reference entry'),
(633, 'INSERT INTO 0_debtor_trans (\n        trans_no, type,\n        debtor_no, branch_code,\n        tran_date, due_date,\n        reference, tpe,\n        order_, ov_amount, ov_discount,\n        ov_gst, ov_freight, ov_freight_tax,\n        rate, ship_via, alloc,\n        dimension_id, dimension2_id, payment_terms, tax_included, prep_amount\n        ) VALUES ('930', '12',\n        '27', '27',\n        '2019-03-24', '0000-00-00', 'CP19138',\n        '0', '0', 50, '0', 0,\n        '0',\n        0, 1, '0', 0,\n        '0', '0', NULL, '0', '0')', 1, 'The debtor transaction record could not be inserted'),
(634, 'INSERT INTO 0_audit_trail (type, trans_no, user, gl_date, description, stamp)\n            VALUES('12', '930',1,'2019-03-24','', CURRENT_TIMESTAMP)', 1, 'Cannot add audit info'),
(635, 'UPDATE 0_audit_trail audit LEFT JOIN 0_fiscal_year year ON year.begin<='2019-03-24' AND year.end>='2019-03-24'\n        SET audit.gl_seq = IF(audit.id=4692, 0, NULL),audit.fiscal_year=year.id, stamp=CURRENT_TIMESTAMP WHERE type='12' AND trans_no='930'', 1, 'Cannot update audit gl_seq'),
(636, 'INSERT INTO 0_gl_trans ( type, type_no, tran_date,\n        account, dimension_id, dimension2_id, memo_, amount) VALUES ('12', '930', '2019-03-24',\n        '1045', '0', '0', '', '50') ', 1, 'The GL transaction could not be inserted'),
(637, 'INSERT INTO 0_gl_trans ( type, type_no, tran_date,\n        account, dimension_id, dimension2_id, memo_, amount, person_type_id, person_id) VALUES ('12', '930', '2019-03-24',\n        '1200', '0', '0', '', '-50', '2', '27') ', 1, 'Cannot insert a GL transaction for the debtors account credit'),
(638, 'INSERT INTO 0_bank_trans (type, trans_no, bank_act, ref,\n        trans_date, amount, person_type_id, person_id) VALUES (12, 930, '4', 'CP19138', '2019-03-24',\n        '50', '2', '27')', 1, 'The bank transaction could not be inserted'),
(639, 'INSERT INTO 0_comments (type, id, date_, memo_)\n            VALUES ('12', '930', '2019-03-24', 'POS_RRBC #930')', 1, 'could not add comments transaction entry'),
(640, 'REPLACE 0_refs SET reference='CP19138', type='12', id='930'', 1, 'could not update reference entry'),
(641, 'INSERT INTO 0_cust_allocations (\n        amt, date_alloc,\n        trans_type_from, trans_no_from, trans_no_to, trans_type_to, person_id)\n        VALUES (50, '2019-03-24', '12', '930', '930', '10', '27')', 1, 'A customer allocation could not be added to the database'),
(642, 'COMMIT', 1, 'could not commit a transaction'),
(643, 'SET sql_mode = 'STRICT_ALL_TABLES'', 1, ''),
(644, 'SET sql_mode = 'STRICT_ALL_TABLES'', 1, '');

Good luck!

/JimmyC

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-25T16:22:46Z

@Joe

To answer your questions and for you to make sense of them, I need to tell you that before I downloaded your fix, I upgraded to Version 2.4.6 Build 24.03.2019 to make sure I didn't have any old legacy modules. I updated to the very latest updates that morning about a couple of hours before your message.

Because of my update being a major update to me, I had cleared my error.log file. It is still empty with no errors at all. Also, below in the code box is the DB line showing that exchange_diff_act in glsetup.general is set to "Exchange Variances Account: 8500 Other expenses" because I am using the "Not for Profit" Chart of Accounts for the USA.

('exchange_diff_act', 'glsetup.general', 'varchar', '15', '8500'),

I have today input another Direct Invoice in the same manner as the previous day and the output for both days is shown below:

('5716', '10', '927', '2019-03-24', '4011', '', '-150', '6', '1', NULL, NULL),
('5717', '10', '927', '2019-03-24', '1200', '', '150', '0', '0', '2', '12'),
('5718', '10', '927', '2019-03-24', '', '', '0', '0', '0', NULL, NULL),
('5719', '12', '927', '2019-03-24', '1045', '', '150', '0', '0', NULL, NULL),
('5720', '12', '927', '2019-03-24', '1200', '', '-150', '0', '0', '2', '12'),
('5721', '10', '928', '2019-03-24', '4011', '', '-100', '6', '1', NULL, NULL),
('5722', '10', '928', '2019-03-24', '1200', '', '100', '0', '0', '2', '41'),
('5723', '10', '928', '2019-03-24', '', '', '0', '0', '0', NULL, NULL),
('5724', '12', '928', '2019-03-24', '1045', '', '100', '0', '0', NULL, NULL),
('5725', '12', '928', '2019-03-24', '1200', '', '-100', '0', '0', '2', '41');

As you can see, the output has the same NULL row and my error.log file is still empty.

In the code box below is the trans_tax_details for both entries:

('1853', '13', '927', '2019-03-24', '0', '0', '1', '0', '150', '0', 'auto', NULL),
('1854', '10', '927', '2019-03-24', '0', '0', '1', '0', '150', '0', 'Dn190145', '0'),
('1855', '13', '928', '2019-03-24', '0', '0', '1', '0', '100', '0', 'auto', NULL),
('1856', '10', '928', '2019-03-24', '0', '0', '1', '0', '100', '0', 'Dn190146', '0');

If you can offer any other suggestions, I will try them out. This problem is far above my pay grade level.

/JimmyC

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-25T08:41:49Z

@jimmyC

I have been doing some investigation about this empty account line.

This line is created if floatcmp reports a balance. Look in gl_db_trans.inc, line 95.
There should also be an error line in your tmp/errors.log file for the date. Please have a look and report.

But the reason that the account code is empty is that there is no System and GL account for 'exchange_diff_act'.
If you go into Setup/System and GL setup and look up this account, there might be a value here, but if it doesn't show the right account change the account to show the right act, then save the setup.

Then please try another Direct Invoice. Now it should work.

/Joe

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-25T01:48:44Z

@Joe

Thank you again for your very quick response. I am unable to do your request until tomorrow morning. I will update you with my findings as soon as I can do what you ask.

/JimmyC

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-25T00:35:03Z

the later GL transactions seems to include an empty account line (are there any missing accounts in your chart of accounts.
FA should never post anything to an empty account.
The line could be some rounded values, but the value is 0 so this looks strange.
I have no idea. In my testing everything went ok in the GL lines and it should do so.

Please check that all your default accounts in the System and GL setup are ok. Do this check in the phpMyAdmin.

/Joe

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-24T23:35:52Z

@Joe Thank you! I downloaded it (sales_order_entry.php) and I now get the correct dates for due_date and delivery _date.

I just have one question .... With the previous code, gl_trans created four lines of data:

('5525', '10', '909', '2019-03-10', '4011', '', '-150', '6', '1', NULL, NULL),
('5526', '10', '909', '2019-03-10', '1200', '', '150', '0', '0', '2', '12'),
('5527', '12', '909', '2019-03-10', '1045', '', '150', '0', '0', NULL, NULL),
('5528', '12', '909', '2019-03-10', '1200', '', '-150', '0', '0', '2', '12'),

and with the new code, five lines of data:

('5716', '10', '927', '2019-03-24', '4011', '', '-150', '6', '1', NULL, NULL),
('5717', '10', '927', '2019-03-24', '1200', '', '150', '0', '0', '2', '12'),
('5718', '10', '927', '2019-03-24', '', '', '0', '0', '0', NULL, NULL),
('5719', '12', '927', '2019-03-24', '1045', '', '150', '0', '0', NULL, NULL),
('5720', '12', '927', '2019-03-24', '1200', '', '-150', '0', '0', '2', '12');

Is this the expected or required output?

/JimmyC

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-24T17:06:34Z

Hello again.

I found the place to fix the bug and this has been committed to stable repo.

/Joe

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-23T22:26:47Z

Hello again @JimmyC.

I see the problem now. When the Document date is earlier than todays date, then the duedate becomes todays date. This is only for Cash Payments.

I have problem finding where to fix this error. Janusz wrote these routines. I will ask him to fix this. Thanks for reporting this.

/Joe

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-23T13:02:17Z

@Joe

Thank you for such a quick response! Yes,this did fix while viewing within FA when you are using the program.

But I must not have been clear on my terminology of the bug I was trying to explain. The bug you fixed is what led me to open the MySQL Database and look at the values being stored and in which column. That is when I saw the anomaly that on my very first entry of this FA session inputs a wrong due_date. By "session" I mean that I have just opened a browser tab for FA, logged in and selected Sales -> Direct Invoice.

Line number 926 was the last data entered of a "previous session" and the tran_date and due_date are correct. I then logged out or FA, logged back into FA, selected Sales -> Direct Invoice and entered the data in the same manner with the due_date being the default auto generated value (trans_no 927). Note that the due_date "defaulted" to today's date of 3/23/2019 instead of 3/17/2019.

I then created a second sale for the same customer and the due_date was stored as 3/17/2019 which is a correct default entry.

Table: 0_debtor_trans
trans_no type version debtor_no branch_code tran_date    due_date    reference tpe order_ ov_amount
926    13    1    40    40    3/17/2019    3/17/2019 auto    3    926    100
927    13    1    12    12    3/17/2019    3/23/2019 auto    3    927    150
928    13    1    12    12    3/17/2019    3/17/2019 auto    3    928    20

Table: 0_sales_orders
order_no trans_type    version type debtor_no branch_code reference ... ord_date ... delivery_date
926    30    1    0    40    40    auto ... 3/17/2019 ... 3/17/2019
927    30    1    0    12    12    auto ... 3/17/2019 ... 3/23/2019
928    30    1    0    12    12    auto ... 3/17/2019 ... 3/17/2019

Please let me know if I am not explaining correctly and I will try again.

/JimmyC

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-23T09:49:04Z

@JimmyC.

I cannot reproduce your examples in version 2.4.6. However there was a presentation bug in Sales Order View.

In the Direct Invoice, the Sales Order is automatically created with the reference 'auto'. The value in the due_date field was the later invoice due_date. So in the dispatch view, you will see the label 'Due Date' instead of 'Requested Delivery'.

Therefore I have changed the 'Sales Order View' to present the 'Due Date' value if the reference is 'auto'.

This should eliminate the misunderstanding.

I am open for other users testing this to see if I have missed another bug.

The view_sales_order.php has been committed to stable repo.

/Joe

Re: Sales Direct Invoice has due_date and delivery_date bug

2019-03-22T15:39:10Z

@joe: are we using a stale session here?