1 Topic by oscarhc (edited by oscarhc 03/14/2010 07:09:11 am)

  • Registered: 02/05/2010
  • Posts: 8

Topic: DB Error while Placing a Direct Invoice with no customer selected.

Hello,

   Added to mantis 0000216: DB Error while Placing a Direct Invoice with no customer selected.
 
   FA 2.2.7

   If a Direct invoice is placed with no customer selected a DB error is shown. This can be avoided validating if a customer is selected before posting.

   Edit: Tried to place a Sales Quotation with no customer selected and is properly detected : "You must enter the person or company to whom delivery should be made to."

   Side efect of this error is that Sales Order is incremented.
   Suppose all are initial transactions. After placing a correct Direct Invoice, in customer transactions you can see that Invoice number is #1 Sales Order is #2 and Sales Order #1 is 'lost'.

   Regards, Oscar.

  • Registered: 02/05/2010
  • Posts: 8

Re: DB Error while Placing a Direct Invoice with no customer selected.

Similar issue while placing a purchase order, mantis 0000217.

3 Reply by surreyhills (edited by surreyhills 10/16/2012 12:46:46 pm)

  • Registered: 08/16/2012
  • Posts: 7

Re: DB Error while Placing a Direct Invoice with no customer selected.

I have the same error on supplier Direct Invoice.  Direct GRN works but Direct Invoice gives the following

At file G:\ServerFolders\Documents\htdocs\FATest\purchasing\includes\db\supp_trans_db.inc:45:
Cannot insert a supplier transaction record
error code : 1265
error message : Data truncated for column 'ov_discount' at row 1
sql that failed was : INSERT INTO 0_supp_trans (trans_no, type, supplier_id, tran_date, due_date, reference, supp_reference, ov_amount, ov_gst, rate, ov_discount, tax_included) VALUES ('3', '20', '2', '2008-12-31', '2009-01-01', '3', '123', '10', '0', '1', '', '0')

The bug is due to a fix made before in line 456 in po_entry_items.php which waas commented out and left ov_discount as null not 0.0. I have changed the code to:

//    MRB 16-10-12 - ov-discount set to zero to avoid DB error on insert
            $inv->ov_discount = 0.0;
// End of fix 16-10-12

which works.

4 Reply by apmuthu (edited by apmuthu 10/16/2012 02:40:31 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: DB Error while Placing a Direct Invoice with no customer selected.

Is this fix needed in Line 460 in the current version of the said file purchasing/po_entry_items.php ?

The field ov_discount in the table supp_trans is set to be NOT NULL for good reason - arithmetic cannot be done on NULL values. Hence setting it to 0.0 is correct. If it is not used as stated in the comments, then the field should be removed.

The same field name and NOT NULL state is observed in the debtor_trans table as well. Any similar inserts failing on it?

apmuthu's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: DB Error while Placing a Direct Invoice with no customer selected.

The supp_trans table is used by both documents and the payments. It is correct to set the value to 0 here.

Certain setup and versions of MySql accepts this though.

Will be fixed asap.

/Joe
BTW will check the ddebtor_trans also.

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: DB Error while Placing a Direct Invoice with no customer selected.

@surreyhills

Thanks for telling us. In certain releases of MySql or MySql setup won't allow the insert of empty value.

Fixed in HG repository. Your fix was used.

No problems in the Direct Sales Invoice.

/Joe

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: DB Error while Placing a Direct Invoice with no customer selected.

Thanks Joe. Maybe in v2.4, if the said field is not used, it can be removed.

Mercurial Changeset 3084.

apmuthu's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: DB Error while Placing a Direct Invoice with no customer selected.

Ah, the supp_trans table share info with payment records. But this could be hidden more effectively in purchase_order_entry.php. Will look at it in more detail.

/Joe