Idempiere Use guide inventory out of storage research

Created by Blues Blues, Qq32876341,blog http://www.cnblogs.com/zzyan/
Idempiere Official Chinese Wiki homepage http://wiki.idempiere.org/zh
Idempiere Chinese Community www.idempiere.org.cn
Idempiere Chinese Community QQ Group 65713012

In this paper, we study the Idempiere inventory mechanism, which mainly involves 2 tables M_storageonhand, m_storagereservation and view M_storage.
You can refer to the peanut wiki to see how these 3 tables are used, and then come back to the actual test section of this article.
http://wiki.idempiere.org/zh/Inventory management mechanism

The organization used for this test is fertilizer,warehouse as fertilizer
Because of the later account setup problem, the last Test switches to the HQ organization, but the principle is the same.

Warehouse Settings Locator as follows

Use material receipt for a receive operation

The receipt form is as follows, received two locator, RAW1 and RAW2 respectively, the number is 100,200

Performs a complete operation on the receive order.

Due to account setup problem, complete error, New PO re-established receipt order operation

Updating the code seems to appear a counter document, resulting in the previous direct establishment of receipt order error

After receiving is complete, view the inventory of the item, and you can see that the inventory increase has been shown

As you can see, the located at feature of the system does not show the aggregated information of the hands, but instead shows the hand information of each locator directly.

Looking at the database, you can see that m_storage is a view, defined as follows
The m_storage consists of two tables, namely M_storageonhand and m_storagereservation.

CREATE OR REPLACE VIEWM_storage as  SELECTs.m_product_id, s.m_locator_id, s.ad_client_id, s.ad_org_id, S.isactive, s.created, S.createdby, s.updated, S.updatedby, S.qtyonhand,0  asqtyreserved,0  asqtyordered, S.datelastinventory, s.m_attributesetinstance_id, S.m_storageonhand_uu asM_storage_uu, S.datematerialpolicy fromM_storageonhand SUNION  All SELECTsr.m_product_id, w.m_reservelocator_id asm_locator_id, sr.ad_client_id, sr.ad_org_id, Sr.isactive, sr.created, Sr.createdby, sr.updated, Sr.updatedby,0  asQtyonhand, Sr.qty asqtyreserved,0  asqtyordered, Sr.datelastinventory, sr.m_attributesetinstance_id, Sr.m_storagereservation_uu asM_storage_uu,NULL::timestampWithout time zone asDatematerialpolicy fromm_storagereservation SRJOINM_warehouse W onsr.m_warehouse_id=w.m_warehouse_idWHERESr.issotrx= 'Y':: Bpchar andSr.qty<> 0:: NumericUNION  All SELECTso.m_product_id, w.m_reservelocator_id asm_locator_id, so.ad_client_id, so.ad_org_id, So.isactive, so.created, So.createdby, so.updated, So.updatedby,0  asQtyonhand,0  asqtyreserved, So.qty asqtyordered, So.datelastinventory, so.m_attributesetinstance_id, So.m_storagereservation_uu asM_storage_uu,NULL::timestampWithout time zone asDatematerialpolicy fromm_storagereservation SoJOINM_warehouse W onso.m_warehouse_id=w.m_warehouse_idWHERESo.issotrx= 'N':: Bpchar andSo.qty<> 0:: Numeric;

From the M_storagereservation table column information can be seen, in addition to the basic onhand information, there is no source document information, so there is no way to reverse query from reservation to order information

After performing the previous receive, M_storageonhand adds a row, while M_transaction also adds a row

Viewing M_transaciton, you can see one more row of records.
As can be seen from the M_transaction table, there are 3 kinds of movemnettype, v,m,c, after the test comparison, the 3 code should represent the following meaning.
V indicates vender
m should indicate move order or similar organization/warehouse internal movement, representing inventory internal transfer
C indicates customer, when the sales order is shipped, Movementtype is C-
The information behind the corresponding 3 main types includes the +/-number, which indicates that the locator value increases or decreases.

In the current inventory state, try to set up a standard so and complete operation to see if the system will automatically reservation operation

The number of so established is 20

After so, you can see the interface above reservation qty=20

Refresh the product interface, view the Onhand information, and find the row Onhand qty=0 in which the On Order qty becomes 38, just 20 more than just now, stating that the reservation record has been created.

Looking at the information in the M_reservation table in db, we found that the system updated the reservation line, Onhand displayed as 0,sotrx=y, indicating that this is a so transaction. Number changed to 38

Compare Onhand, found that the Onhand information is not changed, is still the original row record, the number is 100

The above interface also shows that our m_storage is the union all of the two tables of M_storageonhand and m_reservation. The located at information above the product info captures the M_storage view directly.

We create a new order, this time select the order type of POS, while the creation of a good order after the complete operation, because it is a POS order, the system will automatically perform the shipping operation. We set the order quantity to 17

Because the fertilizer inventory organization does not have account information set up, so there is no way to complete, directly switch to the HQ organization to do once. Re-purchase the HQ 100 quantity to HQ Warehouse. Build so, number 17, and try the complete operation.

After complete, you can see that the quantity information in the so line has changed, deliver qty is 17, indicating that the quantity has been shipped.

Looking at the DB, you can see one more line of m_transaction information, moveordertype=c-, indicating inventory reduction, type customer.

Viewing the Onhand information, you can see that the system does not deduct our latest line of records, but rather a reduction in the number of records in our old data, stating that by default, Idempiere's inventory deduction is deducted Onhand quantity by the advanced back-out (FIFO) way.

Idempiere Use guide inventory out of storage research