How to fully submit a DocType from Python?

MartinHBramwell · October 29, 2020, 2:08pm

When I create and insert a Stock Entry programmatically, I don’t get the same result as when I do it from the user interface.

The problem is with the serial numbers.

Here is a query result for 4 serial numbers:

  SELECT SN.name, SN.serial_no, SN.item_code, SN.purchase_document_type, SN.purchase_document_no, SN.purchase_date
    FROM \`tabSerial No\` SN
   WHERE
         SN.name like 'T%'
ORDER BY SN.name
;
+---------+--------------------+------------------------+----------------------+---------------+
| name    | item_code          | purchase_document_type | purchase_document_no | purchase_date |
+---------+--------------------+------------------------+----------------------+---------------+
| TSTA001 | Radioactive Widget | Stock Entry            | MAT-STE-2020-00061   | 2020-10-29    |
| TSTA002 | Radioactive Widget | Stock Entry            | MAT-STE-2020-00061   | 2020-10-29    |
| TSTS002 | Radioactive Widget | NULL                   | NULL                 | NULL          |
| TSTS003 | Radioactive Widget | NULL                   | NULL                 | NULL          |
+---------+--------------------+------------------------+----------------------+---------------+

The first pair (TSTAxxx) were created by creating a Stock Entry via the user interface.
The second pair (TSTSxxx) were created by creating a Stock Entry by server side Python code.

Note that, the purchase document data only appeared after I manually submitted MAT-STE-2020-00061 !!

Here’s my Python code:

    stock_entry = frappe.new_doc("Stock Entry")
    stock_entry.docstatus = 0
    stock_entry.to_warehouse = "Lethal Stuff - LSSA"
    stock_entry.stock_entry_type = "Material Receipt"
    stock_entry.append("items", { "qty": cnt,
      "item_code": "Radioactive Widget",
      "serial_no": "{0}".format(snList)
    })
    stock_entry.insert()
    stock_entry.run_method('submit')
    frappe.db.commit()

Question:

How can I programmatically submit a Stock Entry so that it performs all it’s operations on all dependencies; ie, on batches, serial numbers, etc.?

rmeyer · October 29, 2020, 2:18pm

What’s the business process around this problem? Where does the data come from, where does it need to go?

In general you need to take care of more things with the programmatic way – in the backend there are no default values / auto completion like you get in the user interface.

MartinHBramwell · October 29, 2020, 3:15pm

Hi @rmeyer Thank you for looking at this.

The serial numbered items are imported from a legacy system.

The stock entries are “Material Receipt” so that they appear in initial stock.

I have a great many importations to do so I wish to avoid the manual operations of data import from CSV. However, using the API to create the Stock Entries proves to be very slow, (and also fails to correctly initialize the serial numbers).

I am all set up to do it with Python programming.

youssef · October 30, 2020, 10:22am

Check this may help you

github.com

aakvatech/CSF_TZ/blob/dc55cdb9f294df5cc4642c3c7f776b956c76174d/csf_tz/csf_tz/doctype/special_closing_balance/special_closing_balance.py#L42


      
          				item_row.item_balance = item_balance
          				item_row.db_update()
          				if item_row.quantity != item_balance:
          					item_dict = dict(
          						item_code=item_row.item,
          						qty=item_row.quantity - item_balance,
          						uom=item_row.uom,
          						s_warehouse=self.warehouse
          					)
          					items.append(item_dict)
          		stock_entry_doc = frappe.get_doc(dict(
          				doctype="Stock Entry",
          				posting_date=self.posting_date,
          				posting_time=self.posting_time,
          				set_posting_time = 1,
          				items=items,
          				stock_entry_type='Material Receipt',
          				purpose='Material Receipt',
          				to_warehouse=self.warehouse,
          				company=self.company,
          				remarks=user_remarks,

MartinHBramwell · October 30, 2020, 2:50pm

@youssef Thank you very much for looking that up.

That code shows submitting directly …

stock_entry_doc.submit()

… whereas I had done this:

stock_entry.insert()
stock_entry.run_method('submit')

I’m now testing to see if that was the critical difference.

MartinHBramwell · October 30, 2020, 4:38pm

@rmeyer @youssef

So, I switched from …

       stock_entry.run_method('submit')

… to …

       stock_entry.submit()

… as your source code reference suggests.

Then …

In order to be sure I know what’s going internally, I did a bunch of before and after diffs on the MariaDB database, like this:

Use the UI manually to complete a Material Transfer with serial numbered items.
Save a database dump as before.sql.
Call my whitelisted, user-defined API method to complete a Material Transfer with serial numbered items.
Save a database dump as after.sql.
Run diff to save a file of differences.

I found relevant insertions in 5 database tables:

GL Entry
Stock Ledger Entry
Stock Entry
Stock Entry Detail
Serial No

I then compared each column of each the altered tables.

The only differences I found were the ones I listed in the original post:

that the manual Submit writes purchase_document_type, purchase_document_no & purchase_date into “tabSerial No” while the automated Submit does not.

I suspect this is an undetected bug in the way that the source code, suggested by @youssef, submits a Stock Entry.

Meanwhile, my serial numbers still don’t refer back to their original “purchase” document.

Questions:

Can you suggest a different code sample?
Where would I find the code executed on the client and the server when a user hits Submit on a new Stock Entry?

A further issue:

A further annoyance is the following single error message (split into 4 parts for readability):

Traceback (most recent call last):
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/app.py", line 64, in application
    response = frappe.api.handle()
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/api.py", line 58, in handle
    return frappe.handler.handle()
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/handler.py", line 30, in handle
    data = execute_cmd(cmd)
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/handler.py", line 69, in execute_cmd
    return frappe.call(method, **frappe.form_dict)
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/__init__.py", line 1086, in call
    return fn(*args, **newargs)
  File "/home/erpdev/frappe-bench-DYPW/apps/returnable/returnable/returnable/doctype/returnable/returnable.py", line 534, in install_returnables
    frappe.enqueue('returnable.returnable.doctype.returnable.returnable.installReturnables', now = True, timeout=60000)
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/__init__.py", line 1537, in enqueue
    return frappe.utils.background_jobs.enqueue(*args, **kwargs)
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/utils/background_jobs.py", line 44, in enqueue
    return frappe.call(method, **kwargs)
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/__init__.py", line 1086, in call
    return fn(*args, **newargs)
  File "/home/erpdev/frappe-bench-DYPW/apps/returnable/returnable/returnable/doctype/returnable/returnable.py", line 523, in installReturnables
    createReturnablesInitialStockEntries()
  File "/home/erpdev/frappe-bench-DYPW/apps/returnable/returnable/returnable/doctype/returnable/returnable.py", line 514, in createReturnablesInitialStockEntries
    stock_entry.submit()

  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 897, in submit
    self._submit()
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 886, in _submit
    self.save()
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 281, in save
    return self._save(*args, **kwargs)
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 303, in _save
    self.insert()
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 264, in insert
    self.run_post_save_methods()
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 963, in run_post_save_methods
    self.run_method("on_submit")
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 827, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 1119, in composer
    return composed(self, method, *args, **kwargs)
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 1102, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 821, in <lambda>
    fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
  File "/home/erpdev/frappe-bench-DYPW/apps/erpnext/erpnext/stock/doctype/stock_entry/stock_entry.py", line 85, in on_submit
    self.update_stock_ledger()

  File "/home/erpdev/frappe-bench-DYPW/apps/erpnext/erpnext/stock/doctype/stock_entry/stock_entry.py", line 669, in update_stock_ledger
    self.make_sl_entries(sl_entries)
  File "/home/erpdev/frappe-bench-DYPW/apps/erpnext/erpnext/controllers/stock_controller.py", line 285, in make_sl_entries
    make_sl_entries(sl_entries, allow_negative_stock, via_landed_cost_voucher)
  File "/home/erpdev/frappe-bench-DYPW/apps/erpnext/erpnext/stock/stock_ledger.py", line 48, in make_sl_entries
    sle_id = make_entry(sle, allow_negative_stock, via_landed_cost_voucher)
  File "/home/erpdev/frappe-bench-DYPW/apps/erpnext/erpnext/stock/stock_ledger.py", line 70, in make_entry
    sle.submit()
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 897, in submit
    self._submit()
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 886, in _submit
    self.save()
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 281, in save
    return self._save(*args, **kwargs)
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 333, in _save
    self.run_post_save_methods()
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 963, in run_post_save_methods
    self.run_method("on_submit")
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 827, in run_method
    out = Document.hook(fn)(self, *args, **kwargs)
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 1119, in composer
    return composed(self, method, *args, **kwargs)

  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 1102, in runner
    add_to_return_value(self, fn(self, *args, **kwargs))
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/model/document.py", line 821, in <lambda>
    fn = lambda self, *args, **kwargs: getattr(self, method)(*args, **kwargs)
  File "/home/erpdev/frappe-bench-DYPW/apps/erpnext/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py", line 44, in on_submit
    process_serial_no(self)
  File "/home/erpdev/frappe-bench-DYPW/apps/erpnext/erpnext/stock/doctype/serial_no/serial_no.py", line 220, in process_serial_no
    validate_serial_no(sle, item_det)
  File "/home/erpdev/frappe-bench-DYPW/apps/erpnext/erpnext/stock/doctype/serial_no/serial_no.py", line 259, in validate_serial_no
    frappe.throw(_("Serial No {0} has already been received").format(serial_no),
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/__init__.py", line 396, in throw
    msgprint(msg, raise_exception=exc, title=title, indicator='red', is_minimizable=is_minimizable)
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/__init__.py", line 375, in msgprint
    _raise_exception()
  File "/home/erpdev/frappe-bench-DYPW/apps/frappe/frappe/__init__.py", line 326, in _raise_exception
    raise raise_exception(msg)
erpnext.stock.doctype.serial_no.serial_no.SerialNoDuplicateError: Serial No TSTC012 has already been received

I have made sure that the test serial numbers are entirely new.

As far as I can tell the code suggested by @youssef does not expect items to have serial numbers and possibly will not work if serial numbers are involved.

youssef · October 30, 2020, 6:40pm

This error coming from this line :

github.com

frappe/erpnext/blob/c451c0882458f1b7fcafbaf89899f16800609aab/erpnext/stock/doctype/serial_no/serial_no.py#L259


      
          
          					if sr and cint(sle.actual_qty) < 0 and sr.warehouse != sle.warehouse:
          						frappe.throw(_("Cannot cancel {0} {1} because Serial No {2} does not belong to the warehouse {3}")
          							.format(sle.voucher_type, sle.voucher_no, serial_no, sle.warehouse), SerialNoWarehouseError)
          
          					if sr.item_code!=sle.item_code:
          						if not allow_serial_nos_with_different_item(serial_no, sle):
          							frappe.throw(_("Serial No {0} does not belong to Item {1}").format(serial_no,
          								sle.item_code), SerialNoItemError)
          
          					if cint(sle.actual_qty) > 0 and has_duplicate_serial_no(sr, sle):
          						frappe.throw(_("Serial No {0} has already been received").format(serial_no),
          							SerialNoDuplicateError)
          
          					if (sr.delivery_document_no and sle.voucher_type not in ['Stock Entry', 'Stock Reconciliation']
          						and sle.voucher_type == sr.delivery_document_type):
          						return_against = frappe.db.get_value(sle.voucher_type, sle.voucher_no, 'return_against')
          						if return_against and return_against != sr.delivery_document_no:
          							frappe.throw(_("Serial no {0} has been already returned").format(sr.name))
          
          					if cint(sle.actual_qty) < 0:

And also you can check this function :

github.com

frappe/erpnext/blob/c451c0882458f1b7fcafbaf89899f16800609aab/erpnext/stock/doctype/serial_no/serial_no.py#L352


      
          			sle_doc.skip_serial_no_validaiton = True
          
          def validate_so_serial_no(sr, sales_order):
          	if not sr.sales_order or sr.sales_order!= sales_order:
          		msg = (_("Sales Order {0} has reservation for the item {1}, you can only deliver reserved {1} against {0}.")
          			.format(sales_order, sr.item_code))
          
          		frappe.throw(_("""{0} Serial No {1} cannot be delivered""")
          			.format(msg, sr.name))
          
          def has_duplicate_serial_no(sn, sle):
          	if (sn.warehouse and not sle.skip_serial_no_validaiton
          		and sle.voucher_type != 'Stock Reconciliation'):
          		return True
          
          	if sn.company != sle.company:
          		return False
          
          	status = False
          	if sn.purchase_document_no:
          		if sle.voucher_type in ['Purchase Receipt', 'Stock Entry', "Purchase Invoice"] and \

MartinHBramwell · October 30, 2020, 9:50pm

So it seems I have uncovered a bug:

The command to do what I need is as follows:

from erpnext.stock.doctype.stock_entry.stock_entry_utils import make_stock_entry

se = make_stock_entry(
    item_code = 'Radioactive Widget',
    qty = 1,
    company = 'Your Public Work.',
    to_warehouse = 'Lethal Stuff - LSSA',
    rate = 9.99,
    serial_no = 'TSTC020',
    do_not_save = False,
    do_not_submit = False,
)

With a single command, that does everything I was hoping to achieve.
It creates a single new data record in each of the tables …

Stock Ledger Entry
Stock Entry
Stock Entry Detail
Serial No

… and it creates two record rows in

GL Entry (debiting and crediting warehouse locations)

Also, in particular, it correctly fills out the Serial No table attributes I was expecting to see used: purchase_document_type , purchase_document_no & purchase_date

The Bug:

The last mentioned behaviour above (concerning reference to the original purchase) is only true if one single serial number is specified.

se = make_stock_entry(
    item_code = 'Radioactive Widget',
    qty = 5,
    company = 'Your Public Work.',
    to_warehouse = 'Lethal Stuff - LSSA',
    rate = 9.99,
    serial_no = 'TSTC020,TSTC021,TSTC022,TSTC023,TSTC024',
    do_not_save = False,
    do_not_submit = False,
)

This creates the same database records as above, with 5 rows appearing in “ tabSerial No ”, but with no values for purchase_document_type , purchase_document_no & purchase_date

If more than one serial number is specified then references to the original purchase doc are not recorded.

Question:

I would be grateful to know: is this a bug or is it intended behaviour?

MartinHBramwell · October 31, 2020, 1:37am

So here’s how I got everything to work:

import datetime
from erpnext.stock.doctype.stock_entry.stock_entry_utils import make_stock_entry

def getDmlFinalizeSerialNumbers(sns):
  return """
      UPDATE  `tabSerial No`
         SET
              purchase_document_type = 'Stock Entry'
            , purchase_document_no = '{1}'
            , purchase_date = '{2}'
            , purchase_time = '{3}'
            , purchase_rate = {4}
            , docstatus = 1
       WHERE serial_no IN ({0})
  """.format(
          sns.serial_nos
        , sns.purchase_document_no
        , sns.purchase_date
        , sns.purchase_time
        , sns.purchase_rate
      )

def createInitialStockEntries(ctx):

  ### 
  ### Submit new Stock Entry
  ### 
  se = make_stock_entry(
    item_code = 'Radioactive Widget',
    qty = ctx.cnt,
    company = 'Your Public Work',
    to_warehouse = 'Lethal Stuff - LSSA',
    rate = ctx.rate,
    serial_no = ctx.snos,
    do_not_save = False,
    do_not_submit = False,
  )

  ### 
  ### Recover creation date
  ### 
  creation = datetime.datetime.strptime(
    se.creation, "%Y-%m-%d %H:%M:%S.%f"
  )

  ### 
  ### Reformat created serial numbers
  ### 
  serial_nos = "'"
  sep = ""
  for sno in se.items[0].serial_no.split(','):
    serial_nos += sep + sno
    sep = "', '"
  serial_nos += "'"

  ### 
  ### Recollect Stock Entry details
  ### 
  snSpec = _dict({
    'serial_nos': serial_nos,
    'purchase_document_no': se.name,
    'purchase_date': creation.strftime("%Y-%m-%d"),
    'purchase_time': creation.strftime("%H:%M:%S.%f"),
    'purchase_rate': ctx.rate,
  })

  ### 
  ### Build update command for created serial numbers to
  ###      provide reference to Stock Entry
  ### 
  dml = getDmlFinalizeSerialNumbers(snSpec)

  ### 
  ### Update Serial Number records
  ### 
  frappe.db.sql(dml, as_dict=True)

def testCreateInitialStockEntries():
  LG("Test Create Initial Stock Entries")

  rate = 9.99

  count = 2

  start = 54
  secnd = start + 1
  serialNumbers= "TSTC0{0},TSTC0{1}".format(start,secnd)

  createInitialStockEntries (_dict({
    'rate': rate,
    'cnt': count,
    'snos': serialNumbers,
  }));