OFBiz
  1. OFBiz
  2. OFBIZ-4559

InventoryWorker.getOutstandingProductQuantities returns inaccurate results when there are multiple ItemIssuances for an order

    Details

    • Sprint:
      Bug Crush Event - 21/2/2015, Community Day 2 - 2015

      Description

      If you create a purchase order for say 50 of an item, and then receive part of that order, say 10, and then receive another 10. If you then call InventoryWorker.getOutstandingProductQuantities(), the OrderItemQuantityReportGroupByItem.quantityOpen will report 80. This is because there will be two ItemIssuance records, resulting in 2 * 50 OrderItem.quantity minus the 20 ItemIssuance.quantity in the result set of the query.

      I was able to verify this by creating a second view entity that can be viewed in WebTools

      Here is the added view entity (essentially removing the group-by="false" values:

      <view-entity entity-name="OrderItemQuantityAndIssuance"
      package-name="org.ofbiz.order.order"
      never-cache="true"
      title="Reports quantity ordered, issued and open by item for OrderItems.">
      <member-entity entity-alias="OH" entity-name="OrderHeader"/>
      <member-entity entity-alias="OI" entity-name="OrderItem"/>
      <member-entity entity-alias="II" entity-name="ItemIssuance"/>
      <alias entity-alias="OH" name="orderTypeId" group-by="true"/>
      <alias entity-alias="OI" name="productId" group-by="true"/>
      <alias entity-alias="OI" name="quantityOrdered" function="sum">
      <complex-alias operator="-">
      <complex-alias-field entity-alias="OI" field="quantity" default-value="0"/>
      <complex-alias-field entity-alias="OI" field="cancelQuantity" default-value="0"/>
      </complex-alias>
      </alias>
      <alias entity-alias="II" name="quantityIssued" field="quantity" function="sum"/>
      <alias entity-alias="OI" name="quantityOpen" function="sum">
      <complex-alias operator="-">
      <complex-alias-field entity-alias="OI" field="quantity" default-value="0"/>
      <complex-alias-field entity-alias="OI" field="cancelQuantity" default-value="0"/>
      <complex-alias-field entity-alias="II" field="quantity" default-value="0"/>
      </complex-alias>
      </alias>
      <view-link entity-alias="OI" rel-entity-alias="OH">
      <key-map field-name="orderId"/>
      </view-link>
      <view-link entity-alias="OI" rel-entity-alias="II" rel-optional="true">
      <key-map field-name="orderId"/>
      <key-map field-name="orderItemSeqId"/>
      </view-link>
      </view-entity>

      If you use this entity and find a purchase order with multiple item issuances, the problem will be apparent.

        Activity

        Hide
        Jacques Le Roux added a comment -

        Backported in R12.04 at r1693703
        Fixed "Fix Version/s" field

        Show
        Jacques Le Roux added a comment - Backported in R12.04 at r1693703 Fixed "Fix Version/s" field
        Hide
        Divesh Dutta added a comment -

        Thanks Rishi for your opinions on this ticket. I have committed the patch.

        Show
        Divesh Dutta added a comment - Thanks Rishi for your opinions on this ticket. I have committed the patch.
        Hide
        Divesh Dutta added a comment -

        Thanks Skip Dever for reporting the issue.

        Show
        Divesh Dutta added a comment - Thanks Skip Dever for reporting the issue.
        Hide
        Divesh Dutta added a comment -

        Fixed this issue in trunk revision number: 1686651

        Fixed in 14.12 in revision number : 1686652

        Fixed in 13.07 in revision number : 1686653

        Show
        Divesh Dutta added a comment - Fixed this issue in trunk revision number: 1686651 Fixed in 14.12 in revision number : 1686652 Fixed in 13.07 in revision number : 1686653
        Hide
        Divesh Dutta added a comment -

        Thanks Rishi Solanki for your views.

        Did any other community member get chance to review this code ?

        Show
        Divesh Dutta added a comment - Thanks Rishi Solanki for your views. Did any other community member get chance to review this code ?
        Hide
        Rishi Solanki added a comment - - edited

        Divesh Dutta: IMO, this may not the best way to go. But I like the idea of including another view which uses a single entity in it, which in turn replaces the same entity in the original view entity used for reporting. Also couldn't think of other way to solve this so I would say code looks fine and it won't give any major performance issue.
        From my side +1 for the fix provided, but we get the better solution then we should go for it. Lets get it reviewed by community if find everything fine then go for it.

        Show
        Rishi Solanki added a comment - - edited Divesh Dutta : IMO, this may not the best way to go. But I like the idea of including another view which uses a single entity in it, which in turn replaces the same entity in the original view entity used for reporting. Also couldn't think of other way to solve this so I would say code looks fine and it won't give any major performance issue. From my side +1 for the fix provided, but we get the better solution then we should go for it. Lets get it reviewed by community if find everything fine then go for it.
        Hide
        Divesh Dutta added a comment -

        I am attaching patch to solve this problem.

        Here I have created new view entity called "ItemIssuanceQuantitySum". This view entity will have sum of issued item grouped by orderItem of Order. Then this view entity is used in OrderItemQuantityReportGroupByProduct view entity instead of ItemIssuance entity. By doing this, we don't get inaccurate results.

        But I don't like this solution because, I think its not good practice to make view entity by using any other view-entity. As far as I know, this makes SQL more complex. But I could not think of any other better solution.

        Community Members,

        Please help to conclude if this is right way to solve this problem or it can be solved by better way.

        Show
        Divesh Dutta added a comment - I am attaching patch to solve this problem. Here I have created new view entity called "ItemIssuanceQuantitySum". This view entity will have sum of issued item grouped by orderItem of Order. Then this view entity is used in OrderItemQuantityReportGroupByProduct view entity instead of ItemIssuance entity. By doing this, we don't get inaccurate results. But I don't like this solution because, I think its not good practice to make view entity by using any other view-entity. As far as I know, this makes SQL more complex. But I could not think of any other better solution. Community Members, Please help to conclude if this is right way to solve this problem or it can be solved by better way.

          People

          • Assignee:
            Divesh Dutta
            Reporter:
            Skip Dever
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development

                Agile