Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We鈥檒l occasionally send you account related emails.

Already on GitHub? Sign in to your account

Short term availability check for sales orders #5108

Closed
metas-ts opened this issue Apr 3, 2019 · 2 comments

Comments

Projects
None yet
2 participants
@metas-ts
Copy link
Member

commented Apr 3, 2019

Is this a bug or feature request?

FR

Raw concept with 馃 comments and 馃敤 results

When user tries to complete an Sales Order, we need to make sure that the qty from order lines while be available on DatePromised.

We need a quick but efficient way to do that.

  • 馃 comment-ts:
    • totally true; quick but efficient. So don't consider it done when you only know that it runs on a DB with 5 products and 10 order lines; note: we same sufficently large DBs to the this with, so no need to try and generate some artificial data yourself
    • that probably also means that we need to the following stuff for n order lines with just one query (i.e. not 1 query for each order line) in order to avoid the dreaded n+1 problem
  • 馃敤 result-ts: done via function de_metas_material.retrieve_available_for_sales which uses the view de_metas_material.MD_ShipmentQty_V and MD_Stock.

Basically, for each sales order line for current sales order, we shall calculate:

  • STOCK: current stock level (i.e. MD_Stock table)

    • 馃敤 result-ts done
  • SCHEDULED_RECEIPTS: sum of all M_ReceiptSchedule until order's DatePromised (date+time!)

    • 馃敤 result-ts: decided with mark to not include M_ReceiptSchedule because these items might or might not be delivered on time
  • SCHEDULED_SHIPMENTS (firm): sum of all M_ShipmentSchedule(s) open quantities which are in progress (NO MATTER which is their date)

    • 馃 comment-ts:
      • here i suggest to introduce an optional parameter (SysConfig): "number of days"
      • only consider scheduled shipments with a planned date before now() + number-of-days
      • the value of number of day can be set to reflect the time it usually takes to replentish missing products
      • why: otherwise there might be planned shipments (e.g. for subscriptions) that are not relevant to the user;
        • background: she only needs to be notified if a current sales order could cause a "direct" problem; planned shipments that are sufficiently far in the future will be a addressed using material-dispo, purchase-order-disposition, production etc and shall not bother her
    • 馃敤 result-ts: intrduced lookahead (hrs) in the dedicated config window for this feature
  • PLANNED_SHIPMENTS: sum of all draft sales order lines created before our sales order line (it's enough to compare by C_OrderLine_ID to get the ordering); make sure this figure is excluding our current order line!

    • 馃 comment-ts:
      • likewise, i suggest to have a (SysConfig) to make sure the don't look into the past too far. because some users have a number of stale sales orders..ofc if would be better if they cleaned those op
      • so instead of comparing by C_OrderLine_ID, we could add an index on Updated (if it's not yet there) and then compare by that
    • 馃敤 result-ts: intrduced lookbehind (hrs) in the dedicated config window for this feature

Now, having those we can calculate if we can fulfill the current order line:
Available to Promise (ATP) = STOCK + SCHEDULED_RECEIPTS - SCHEDULED_SHIPMENTS - PLANNED_SHIPMENTS

  • 馃 comment-ts:
    • i think we need to use another term because we already have ATP and afaiu (looked on wikipedia and talked to a supply-chain expert I know) we even use it fairly correctly.
    • we might call it CTP but i'm not sure...actually i think the term "CTP" would promise to much
    • => wdyt about calling it "Available to sale"
  • 馃敤 result-ts: added two read-only field to the sales order line window
    • "kurzfr. Verf眉gbar" / "Available at short notice" ( = STOCK + SCHEDULED_SHIPMENTS - PLANNED_SHIPMENTS )
    • there is also color field "Nicht kurzfr. Verf眉gbar" / "Not avail. on short term" that is set to a predefined color if there is a problem

Comparate the ATP with order line's QtyOrdered.
If the ATP >= QtyOrdered => OK
else => throw exception because we cannot promise that Qty.

  • 馃 comment-ts:
    • we need to notify the user, not prevent him for closing the order. to that end, i think we need to
      • display the "Available to sale" quantity in the product lookup, like we currently display the ATP. Ofc, it needs to be configurable if and which of the two one we display.
      • when user tries to complete the sale order *do not prevent that", but show a notification. Show it in a way that the user can't oversee (requires API + frontend improvements)
        • the use shall be able to complete the sale order, but need to know that she has to take immediate action.
    • 馃敤 result-ts: user is notified via the color field in newly crated or changed sales order lines

Hints and other stuffs you have to take care of

ASI storage key

Make sure all quantities above are computed by matching the ASI storage key.
The ASI storage key contains only the attributes which are relevant from storage point of view.

To create an ASI storage key, see org.adempiere.mm.attributes.api.AttributesKeys.createAttributesKeyFromASIStorageAttributes(int)

  • 馃 comment-ts:
    • "matching" means not only "equal". I.e. if you have a storage key in an MD_Stock record and you have a storage key from your order line, then the order-line-key can also be a substring of the MD_Stock's key
    • for the case that it somehow turn out we need to do this directly on the DB: afair there is also a DB function which does this
  • 馃敤 result-ts: done

UOMs

Make sure you compare the Qtys using stocking UOM.

  • 馃敤 result-ts:
    • we ignore QtyOrdered, but convert the available qty into the sales order line's UOM and then compare with Qtyentered.
    • why: the check is done on qtyEntered-change (amonth other columns) and the user can't even see QtyOrdered. I wanted to avaoid making assumptions about the value of QtyOrdered

@metas-ts metas-ts changed the title Short-Term availability check for sales orders Short term availability check for sales orders Apr 3, 2019

metas-ts added a commit that referenced this issue Apr 3, 2019

Short term availability check for sales orders
* introduce a DB function to retrieve the short-term availability for a product and attributesKey, at a certain date
  * played with the function on a really big DB to make sure it performs well
* introduce a dedicated config window for an admin to specify the parameters for this feature on a per-org basis
* use that DB function to check and update sales order lines and sales orders
* also
  * introduce ColorId
  * organize imports in different de.metas.material packages
#5108

metas-ts added a commit that referenced this issue Apr 3, 2019

Short term availability check for sales orders
* adding a model interceptor for order records (change of preparationdate)
* extract common code into AvailableForSalesUtil.java
#5108

metas-ts added a commit to metasfresh/metasfresh-webui-api that referenced this issue Apr 3, 2019

Short term availability check for sales orders
* follow refactoring in metasfresh
* minor change in Jenkinsfile
metasfresh/metasfresh#5108
@metas-ts

This comment has been minimized.

Copy link
Member Author

commented Apr 4, 2019

Dev-Test

Normal usage

Deployed to https://dev404.metasfresh.com

image


Create sales order with two lines;
Note

  • this is a vanilla DB, i.e. nothing on stock
  • existing lines are not updated if another line is added. I think that's OK because we ususally don't add multiple lines with the same product
    image

Create a purchase order and receipt

  • note that compared to the two sales order lines, the receipt has additional attributes
    image

Trigger an update to the existing sales order lines by changing the order`s PreparationDate:

Create a purchase order and receipt
image


Create another receipt *but without any attribute. the received goods shall therefore not match the "Bio" sales order line.

Again, trigger an update to the existing sales order lines by changing the order`s PreparationDate:

note that the qty of the sencond recipt matched only the 2nd line
image

Error handling

Goal: IF there is any error in the asynchronous operation, the user is notified and can forward the respective issue's URL to an admin, but the user can still go on creating sales orders

Set the config's ascync timeout to 1ms. Note that this is not enough time for the async update to finish

Trigger an update to the existing sales order lines by changing the order`s PreparationDate:
image

open the lotifications:
image

click on it:
image

metas-ts added a commit that referenced this issue Apr 4, 2019

Short term availability check for sales orders
* change config window layout to our best practice
* fix bug when computing the available quantity; shall be directly comparable to the resp. ordered quantity and that didn't work properly
#5108

metas-ts added a commit that referenced this issue Apr 4, 2019

Short term availability check for sales orders
* fix plus/minus resp add/subtract bug
#5108

metas-ts added a commit that referenced this issue Apr 4, 2019

Short term availability check for sales orders
* fix possible bug by not depending on QtyOrdered;
* also add more tests;
* I_C_OrderLine fix method-name-typo in
#5108

metas-ts added a commit that referenced this issue Apr 4, 2019

Short term availability check for sales orders
* mention in the config fields' descriptions that it's about hours
#5108

metas-ts added a commit that referenced this issue Apr 4, 2019

Short term availability check for sales orders
* fix the data retrieval DB function; when deciting which old order lines to still include, now() has to be the point of reference, and not the PreparationDate
#5108

metas-ts added a commit that referenced this issue Apr 5, 2019

Short term availability check for sales orders
* add async-option to the config if set, then in the webui the request will be done in a dedicated thread.
  * also add timeout-option so the webui aborts and not ends up with 20 never-endeing async-background threads if there are unexpected perf-problems
  * also add an exception handler for timeout- and other exceptions happening in the async thread. it creates an AD_Isue and a user notification (with a link to said AD_Issue)
* fix a but in TypedSqlQuery's SQL creation for union queries
#5108

metas-ts added a commit that referenced this issue Apr 5, 2019

Short term availability check for sales orders
* remove failing SQL that should not have been there
#5108

metas-ts added a commit that referenced this issue Apr 5, 2019

Short term availability check for sales orders
* TypedSqlQuery - fix for SQL creation;
  * different unions need their respective own from clause;
  * however (at least for now) they shall all the first query's select clause
  * why? because otherwise there are problems with an explode-or-to-union query that just wants to list Ids
#5108

metas-ts added a commit that referenced this issue Apr 6, 2019

Short term availability check for sales orders
* rename local variables sqlFrom to fromClause to make sure they don't shadow the sqlFrom member variable (better sayfe than sorry with this legacy code)
* add a space in front to FROMs to lower the possibility of syntax errors
#5108

metas-ts added a commit that referenced this issue Apr 6, 2019

Short term availability check for sales orders
* make two method parameters final
#5108

metas-ts added a commit that referenced this issue Apr 6, 2019

Short term availability check for sales orders
* cleanup duplicated DDL
* include PreparationDate_Override in the DB function, hopefully preserving perf
* minor code cleanups
#5108

metas-ts added a commit that referenced this issue Apr 7, 2019

Short term availability check for sales orders
* remove date result columns; they prevent matching oder lines from being aggregated and therefore cause the onStock-qty to be multiplied in the end result.
#5108

metas-ts added a commit that referenced this issue Apr 8, 2019

Short term availability check for sales orders
* add a previously missing DB function to create a storage attributes key from an M_AttributerSetInstance_ID
#5108

metas-ts added a commit that referenced this issue Apr 8, 2019

Short term availability check for sales orders
* change the was we invoke Retrieve_available_for_Sales so that order lines without ASI match *all* the stock etc
#5108

metas-ts added a commit that referenced this issue Apr 9, 2019

Short term availability check for sales orders
add standard config that should work on almost every system; background: we now have this color indicator field at a prominent place in the sales order window, so let's use it
#5108

metas-ts added a commit that referenced this issue Apr 9, 2019

Short term availability check for sales orders
fix bug in case no order line is eligible for availability checking
#5108

metas-ts added a commit that referenced this issue Apr 10, 2019

Short term availability check for sales orders
fix - order line eligibility needs to be based on QtyEntered; but should not matter, still having qtyOrdered here creates FUD
#5108

metas-ts added a commit that referenced this issue Apr 11, 2019

Short term availability check for sales orders
fixes -
* storage attributes key should match exactly (not substring), unless it's -1000 (a.k.a. ALL)
* M_ShiptmentSchedule.QtyTodeliver was no good; changed to QtyReserved
#5108

metas-ts added a commit that referenced this issue Apr 11, 2019

Short term availability check for sales orders
fix - storage attributes key should match exactly (not substring), unless it's -1000 (a.k.a. ALL)
#5108

@metas-ts metas-ts closed this in 7344358 Apr 15, 2019

teosarca added a commit that referenced this issue Apr 16, 2019

teosarca added a commit that referenced this issue Apr 16, 2019

@metas-ts metas-ts reopened this Apr 16, 2019

metas-ts added a commit that referenced this issue Apr 22, 2019

Update ReleaseNotes.md
  * [#5108](#5108) Short term availability check for sales orders
  * [#5143](#5143) Product Specification process only for one selected product
  * [#36](metasfresh/metasfresh-e2e#36) Use fixtures for sales order
  * [#3](metasfresh/metasfresh-edi#3) Process new data formats
  * [#2217](metasfresh/metasfresh-webui-frontend#2217) Console flooded with error "Cannot read property 'getInstance' of null"
@metas-dh

This comment has been minimized.

Copy link
Member

commented May 9, 2019

Results of IT1
tested in medios.metasfresh

(Tested with 2 sales orders, each 1 orderline, for the same product)

  1. 2 new fields in orderline:
  • Nicht kurzfr. Verf眉gbar: red dot when no short term availability for a product: OK
  • kurzfr. Verf眉gbar: shows qty available at short term for this sales orderline: OK
  1. red dot is displayed when qty for short term availability is not enough for the resp. orderline: OK

  2. qty in kurzfr. Verf眉gbar is updated correctly, with other sales order(lines) considered:

  • attributes: are correctly considered, only available qty with matching attributes (storage relevant!) are considered: OK
  • material receipt: after qty in stock is increased bc of receipt, the qty in kurzfr. Verf眉gbar is increased correctly: OK
  • Reverse: after reverse of material receipt, the qty in kurzfr. Verf眉gbar is increased correctly: OK
  • production: after qty is received and PP_Order is processed, the qty in kurzfr. Verf眉gbar is increased correctly: OK
  • did not test with qty decreased when product issued & detroyed in PP_Order, bc we know the qty in stock is not updated correctly after that, see #1032
  • shipment: after qty in stock is decreased bc of receipt, the qty in kurzfr. Verf眉gbar is decreased correctly: OK
  1. DatePromised of other sales order(lines) correctly considered:
  • orderlines are not considered if DatePromised is +72 days in the future: OK
  • orderlines are not considered if they were not touched for +8hrs: OK
  1. Error notifiaction:
  • notification with link to the System Error Report pops up when the asynchronous operation runs into timeout (see dev test): OK
  • checked with metas-ts: the "normal" will not have access to that report window, but the URL will be provided and can be sent to an Admin or metasfresh: OK

problems:

  • UOM considered correctly: tested with different UOMs (Stk as product UOM, and set Kg as UOM in orderline per db): the qty in kurzfr. Verf眉gbar was updated correctly, but only after the qty in the orderline was changed; just updating the order (by changing DatePromised) the qty in kurzfr. Verf眉gbar was updated, but not correctly; discussed with metas-ts: this case is very unrealistic, different UOMs will be set from the beginning before the orderline is created, changing it afterwards is not possible since it
    s read-only.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can鈥檛 perform that action at this time.