1 (edited by JimmyC 03/20/2019 03:16:04 am)

Topic: Sales Direct Invoice has due_date and delivery_date bug

Currently I am using Version 2.4.4 Build 17.03.2018.  When I login and create my first Direct Invoice of this session, I first change the Invoice Date to the desired date, complete entering data and click "Place Invoice".

Upon examining the DB in the table debtor_trans, column due_date and table sales_order, column delivery_date, these two dates are the same but are always today's date and not the Invoice Date. 

If entering another invoice using the same invoice date without touching or changing the invoice date, then the above two dates are entered correctly to the same as the Invoice Date.

If I change to a new Invoice Date when entering the next invoice, the invoice dates (tran_date and ord_date) are the Invoice Date but the due_date and delivery_date are the previous invoice's Invoice Date.

In the listing below, I am trying to convey my explanations above using a beginning Invoice Date and Today's Date 2019-03-19:
 
   Invoice Date                  tran_date       &       due_date            ord_date       &       delivery_date
  2019-03-10                 2019-03-10             2019-03-19           2019-03-10            2019-03-19

  2019-03-10                 2019-03-10             2019-03-10           2019-03-10            2019-03-10
 
  2019-03-12                 2019-03-12             2019-03-10           2019-03-12            2019-03-10

  2019-03-15                 2019-03-15             2019-03-12           2019-03-15            2019-03-12

  2019-03-17                 2019-03-17             2019-03-15           2019-03-17            2019-03-15

  2019-03-17                 2019-03-17             2019-03-17           2019-03-17            2019-03-17

Log out and log back in:

  2019-03-17                 2019-03-17             2019-03-19           2019-03-17            2019-03-19

If hope I have portrayed this correctly so that you can replicate what I see happening in my DB.

If you need to know any Company, System or Preferences settings, please let me know and I'll try to reply quickly.


/JimmyC

[url=https://vgruk.com/]buy here[/url]

Re: Sales Direct Invoice has due_date and delivery_date bug

@joe: are we using a stale session here?

Re: Sales Direct Invoice has due_date and delivery_date bug

@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

4 (edited by JimmyC 03/23/2019 01:12:32 pm)

Re: Sales Direct Invoice has due_date and delivery_date bug

@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

[url=https://vgruk.com/]buy here[/url]

Re: Sales Direct Invoice has due_date and delivery_date bug

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

Hello again.

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

/Joe

7 (edited by JimmyC 03/24/2019 11:36:27 pm)

Re: Sales Direct Invoice has due_date and delivery_date bug

@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

[url=https://vgruk.com/]buy here[/url]

Re: Sales Direct Invoice has due_date and delivery_date bug

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

@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

[url=https://vgruk.com/]buy here[/url]

Re: Sales Direct Invoice has due_date and delivery_date bug

@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

@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

[url=https://vgruk.com/]buy here[/url]

Re: Sales Direct Invoice has due_date and delivery_date bug

@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

[url=https://vgruk.com/]buy here[/url]

Re: Sales Direct Invoice has due_date and delivery_date bug

@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

@Joe

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

/JimmyC

[url=https://vgruk.com/]buy here[/url]

Re: Sales Direct Invoice has due_date and delivery_date bug

@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

@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

[url=https://vgruk.com/]buy here[/url]