This question has been flagged

We wish to accrue a running "tab" of everything a customer purchases during the month and we will manually invoice at month end.  We follow the logic as such:  We create a quotation for at least one item.  We confirm it and it becomes a Sales Order.  Over the period of a couple of days the customer may add things to the purchase and so we edit that sales order by adding more line items.  But we don't invoice until end of month.  Later in the month the customer may make another order and we repeat the process described above.  So by the end of the month a customer can have multiple sales orders and each of them may have more than one order line item.  When we finally invoice the customer at month end we see invoice lines for each order line of each order.  Our problem is that the invoice lines don't contain the order dates for each for each line item.  We want each line item to contain the purchase date for that line item.

We used Odoo developer mode and added a new field "x_date_completed" to model sale.order.line and used "edit form view" for sale.order.form and added our "x_date_completed" field in tree string="Sales Order Lines" after "qty_delivered" field.  It works in that each order line gives us a field we can update when the line item is added or edited, but we would like those dates for each sales order line item to be carried over to the invoice line items.  We would like to be able to automate this for the sake of accuracy and efficiency.  Can anyone show us how to get the new field automatically carried over to the invoice line items?

 

 
Avatar
Discard
Author

Since no one answered, here is more info: I probably was misleading when I said "purchase order". I'm talking about invoices for sales. I know the same template is used for purchases from vendors though.

Best Answer

Hi Donald,

You would need to create a custom module for that, override the "_prepare_invoice_line" method and pass the value of the Purchase Date field of Sales Order Line.

Add following code in your custom module:

class SaleOrderLine(models.Model)
_inherit = 'sale.order.line'

@api.multi
def _prepare_invoice_line(self):
inv_line_vals = super(SaleOrderLine, self)._prepare_invoice_line()
inv_line_vals.update({'pur_date': self.pur_date})
return inv_line_vals

Let me know if you need any guidance in custom module.

Sudhir Arya
ERP Harbor Consulting Services
Skype:sudhir@erpharbor.com
Website: http://www.erpharbor.com


Avatar
Discard
Author

Thanks for the advice Sidhir. I will research how to do it and give it a try. But I was also thinking about adding the field to the corresponding models for invoices and purchases and using the PGADMIN app to add triggers to my new field in the sale.order.line table so that when it is updated the new field in the invoice and purchase line models would also be updated with the same value. I'm not thinking of defining any constraint though. What do you think? Could this work?

Author

Sorry, I meant put triggers in the corresponding new fields in purchase order line and invoice line.

Yes that would be fine if you can make it work like that. But if you create a custom module, you can use that feature in any database. You just need to install the module.

Author

Let me see if I got this right. You're saying I can modify an existing module by adding the code you specify? Which module (and where would I find it in my Ubuntu 16.04)? Then I create a new class in that module called SaleOrderLine and a new method called _prepare_invoice_line with the code you specified and the field you listed named 'pur_date' is actually the fields I created in sale.order.line and account.invoice.line? How do I "override" the "_prepare_invoice_line" method? Are you saying I simply rename the original module and give my edited module the original module name? If you could give me a step-by-step for what to do I'd really appreciate it. Thanks.

Actually you would need to create your own custom module and add the code I have posted in the answer. You should not change the code in the base modules.

You can find many blogs / documents on how to create a custom module.

Author

And have you any suggestions for how to code trigger elements?

Author Best Answer

Sudhir, I chose the trigger method.  Here's what I learned:  At first I put the trigger on the account_invoice_line table.  My initial logic was to use the sale_order_line_invoice_rel table to relate sale order line to invoice line.  I got the function and trigger coded successfully, but it did not copy the date.  I figured out why:  the row is not inserted into the sale_order_line_invoice_rel table until AFTER the account_invoice_line row is successfully inserted.  So I changed my function and trigger to operate on the sale_order_line_invoice_rel table.  Here's my code:

-- DROP FUNCTION public.set_invoice_line_date_of_service();

CREATE OR REPLACE FUNCTION public.set_invoice_line_date_of_service()
  RETURNS trigger AS
$BODY$
DECLARE
  xdateofservice date;
BEGIN
  xdateofservice := x_date_of_service from sale_order_line where id = NEW.order_line_id;
  update account_invoice_line
    SET x_date_of_service = xdateofservice WHERE id = NEW.invoice_line_id;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;


CREATE TRIGGER set_invoice_line_date_of_service
  AFTER INSERT
  ON public.sale_order_line_invoice_rel
  FOR EACH ROW
  EXECUTE PROCEDURE set_invoice_line_date_of_service();



I suppose I didn't need the "RETURN NEW" statement.  Am i right?  In any event, our company is a service company and we wanted a field in the table to keep track of when the service was provided and we could put that info on the invoice line.  As you see my trigger does indeed do that.  I added the x_date_of_service field to sale_order_line and account_invoice_line models, and I added the field to sale.order.form and account.invoice.form.  When we verify with the vendor that the service is complete we enter the date.  Then when we invoice the customer at month end, each invoice line has the date when service was complete.

Next we want to do this same logic with vendors, since each service is provided by a sub-contractor.  It is a one-for-one relation between sales and purchases.  So each time we send a sub-contractor to a job we generate a purchase order.  At week end we generate the vendor bills, and each bill has line items for each job that vendor worked, and so we want to put the date of service on each line.  Odoo business model doesn't exactly fit our business model, so we are making it fit.


Avatar
Discard
Author

PS: We've done it now for vendors, too. Now we need to figure out how to get it to show up on the PDFs that we either print/mail or email.