Uploaded image for project: 'OFBiz'
  1. OFBiz
  2. OFBIZ-6763

Wrong purchase invoice item type for product type IDs RAW_MATERIAL, GOOD

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: Trunk
    • Fix Version/s: Trunk, 14.12.01, 13.07.03
    • Component/s: accounting
    • Labels:
      None

      Description

      Hi there,
      system incorrectly derives invoice item type INV_FPROD_ITEM (instead of PINV_FPROD_ITEM, for example) in purchase invoices for purchase orders with products of type RAW_MATERIAL and GOOD. As a result, the GL account derivation is incorrect as well where a revenue GL account gets debited instead of GL "Uninvoiced Shipment Receipts".
      The incorrect accounting entry posted is:
      DR Revenue (as a result of invoice item type INV_FPROD_ITEM)
      CR A/P
      The correct entry should be:
      DR Uninvoiced Shipment Receipts (derived from PINV_FPROD_ITEM)
      CR A/P

      It seems the above is caused by missing entries in table invoice_item_type_map where
      INVOICE_ITEM_MAP_KEY should contain RAW_MATERIAL and GOOD and/or PRODUCT_ORDER_ITEM.

      The above symptom does not affect the Goods Receipt Transactions, which gets correctly posted as:

      DR Inventory
      CR Uninvoiced Shipment Receipts

      Any help is appreciated.

      1. OFBIZ-6763-AccountingData.patch
        3 kB
        Pierre Smits
      2. screenshot-1.png
        63 kB
        Michael Powacht

        Activity

        Hide
        rlhowell Rupert Howell added a comment -

        Hi Michael

        It appears that there is some seed data missing.
        The below data will add a new invoice item type and map raw materials to the correct account.

        <InvoiceItemType description="Invoice Raw Material(Purchase)" hasTable="N" invoiceItemTypeId="PINV_RPROD_ITEM" parentTypeId="PINV_PROD_ITEM"/>
        <InvoiceItemTypeMap invoiceTypeId="PURCHASE_INVOICE" invoiceItemMapKey="RAW_MATERIAL" invoiceItemTypeId="PINV_RPROD_ITEM"/>
        <InvoiceItemType invoiceItemTypeId="PINV_RPROD_ITEM" defaultGlAccountId="214000"/>

        GOOD is already mapped however so that should be picking up PINV_FPROD_ITEM

        Show
        rlhowell Rupert Howell added a comment - Hi Michael It appears that there is some seed data missing. The below data will add a new invoice item type and map raw materials to the correct account. <InvoiceItemType description="Invoice Raw Material(Purchase)" hasTable="N" invoiceItemTypeId="PINV_RPROD_ITEM" parentTypeId="PINV_PROD_ITEM"/> <InvoiceItemTypeMap invoiceTypeId="PURCHASE_INVOICE" invoiceItemMapKey="RAW_MATERIAL" invoiceItemTypeId="PINV_RPROD_ITEM"/> <InvoiceItemType invoiceItemTypeId="PINV_RPROD_ITEM" defaultGlAccountId="214000"/> GOOD is already mapped however so that should be picking up PINV_FPROD_ITEM
        Hide
        pfm.smits Pierre Smits added a comment -

        This patch addresses the issue.

        Show
        pfm.smits Pierre Smits added a comment - This patch addresses the issue.
        Hide
        mpowacht Michael Powacht added a comment -

        Thanks for your prompt action Rupert.
        Will check it out tomorrow.

        Sent from my Samsung Galaxy smartphone.

        -------- Original message --------
        From: "Rupert Howell (JIRA)" <jira@apache.org>
        Date: 12/10/2015 19:30 (GMT+07:00)
        To: Michael Powacht <michael.powacht@zi-th.com>
        Subject: [jira] [Commented] (OFBIZ-6763) Wrong purchase invoice item type for product type IDs RAW_MATERIAL, GOOD

        [ https://issues.apache.org/jira/browse/OFBIZ-6763?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15050910#comment-15050910 ]

        Rupert Howell commented on OFBIZ-6763:
        --------------------------------------

        Hi Michael

        It appears that there is some seed data missing.
        The below data will add a new invoice item type and map raw materials to the correct account.

        <InvoiceItemType description="Invoice Raw Material(Purchase)" hasTable="N" invoiceItemTypeId="PINV_RPROD_ITEM" parentTypeId="PINV_PROD_ITEM"/>
        <InvoiceItemTypeMap invoiceTypeId="PURCHASE_INVOICE" invoiceItemMapKey="RAW_MATERIAL" invoiceItemTypeId="PINV_RPROD_ITEM"/>
        <InvoiceItemType invoiceItemTypeId="PINV_RPROD_ITEM" defaultGlAccountId="214000"/>

        GOOD is already mapped however so that should be picking up PINV_FPROD_ITEM


        This message was sent by Atlassian JIRA
        (v6.3.4#6332)

        Show
        mpowacht Michael Powacht added a comment - Thanks for your prompt action Rupert. Will check it out tomorrow. Sent from my Samsung Galaxy smartphone. -------- Original message -------- From: "Rupert Howell (JIRA)" <jira@apache.org> Date: 12/10/2015 19:30 (GMT+07:00) To: Michael Powacht <michael.powacht@zi-th.com> Subject: [jira] [Commented] ( OFBIZ-6763 ) Wrong purchase invoice item type for product type IDs RAW_MATERIAL, GOOD [ https://issues.apache.org/jira/browse/OFBIZ-6763?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15050910#comment-15050910 ] Rupert Howell commented on OFBIZ-6763 : -------------------------------------- Hi Michael It appears that there is some seed data missing. The below data will add a new invoice item type and map raw materials to the correct account. <InvoiceItemType description="Invoice Raw Material(Purchase)" hasTable="N" invoiceItemTypeId="PINV_RPROD_ITEM" parentTypeId="PINV_PROD_ITEM"/> <InvoiceItemTypeMap invoiceTypeId="PURCHASE_INVOICE" invoiceItemMapKey="RAW_MATERIAL" invoiceItemTypeId="PINV_RPROD_ITEM"/> <InvoiceItemType invoiceItemTypeId="PINV_RPROD_ITEM" defaultGlAccountId="214000"/> GOOD is already mapped however so that should be picking up PINV_FPROD_ITEM – This message was sent by Atlassian JIRA (v6.3.4#6332)
        Hide
        jacques.le.roux Jacques Le Roux added a comment -

        Thansk Michael, Rupert and Pierre,

        Pierre your patch is in
        trunk r1719094
        R14.12 r1719095
        R13.07 r1719095

        R12.04 was confusing, I did not backport

        Show
        jacques.le.roux Jacques Le Roux added a comment - Thansk Michael, Rupert and Pierre, Pierre your patch is in trunk r1719094 R14.12 r1719095 R13.07 r1719095 R12.04 was confusing, I did not backport
        Hide
        mpowacht Michael Powacht added a comment -

        Thanks to all for your prompt actions, which is truly impressive! Products with product type ID "RAW_MATERIAL" work as expected now but I still face issues with products assigned to product_type_id "GOOD" where the purchase invoice still erroneously derives item type INV_FPROD_ITEM and thereby a revenue GL. I am planning to assign product_type_id GOOD to physical trading products (i.e they are not of type FINISHED_GOOD, DIGITAL_GOOD or FINDIG_GOOD)

        Did I miss something?

        Thanks again,
        Michael

        Show
        mpowacht Michael Powacht added a comment - Thanks to all for your prompt actions, which is truly impressive! Products with product type ID "RAW_MATERIAL" work as expected now but I still face issues with products assigned to product_type_id "GOOD" where the purchase invoice still erroneously derives item type INV_FPROD_ITEM and thereby a revenue GL. I am planning to assign product_type_id GOOD to physical trading products (i.e they are not of type FINISHED_GOOD, DIGITAL_GOOD or FINDIG_GOOD) Did I miss something? Thanks again, Michael
        Hide
        jacopoc Jacopo Cappellato added a comment -

        Thanks for your work Michael Powacht. As regards the type named "GOOD", it is a special type that is not supposed to be used in orders; in fact it is an "abstract" type that is parent type for several other types including physical and non physical items like "raw materials", "subassembly", "digital goods" etc..
        In my opinion a better fix would be that of preventing to assign this type to a product directly: the check could be done when the product data is created/updated and when the product is added to an order (to catch wrong product data imported into the system with external tools).

        Show
        jacopoc Jacopo Cappellato added a comment - Thanks for your work Michael Powacht . As regards the type named "GOOD", it is a special type that is not supposed to be used in orders; in fact it is an "abstract" type that is parent type for several other types including physical and non physical items like "raw materials", "subassembly", "digital goods" etc.. In my opinion a better fix would be that of preventing to assign this type to a product directly: the check could be done when the product data is created/updated and when the product is added to an order (to catch wrong product data imported into the system with external tools).
        Hide
        mpowacht Michael Powacht added a comment -

        Many thanks for your clarification Jacopo, I understand.

        Which product type would you suggest to be assigned to regular physical products for resale?
        The following product types don't classify in my opinion:
        Raw Material: Used as input materials for Mfg.
        Finished Good: The finished output from Mfg.
        Subassembly: Semifinished ouptput from Mfg.

        Assigning resale products to product type Finished Goods could lead to wrong requirements generated by MRP, i.e. internal requirements (and production runs) instead of product requirements in the form of proposed purchase orders or will that only happen if a BOM
        is defined for a Finished Good Product?

        Thanks,
        Michael

        Show
        mpowacht Michael Powacht added a comment - Many thanks for your clarification Jacopo, I understand. Which product type would you suggest to be assigned to regular physical products for resale? The following product types don't classify in my opinion: Raw Material: Used as input materials for Mfg. Finished Good: The finished output from Mfg. Subassembly: Semifinished ouptput from Mfg. Assigning resale products to product type Finished Goods could lead to wrong requirements generated by MRP, i.e. internal requirements (and production runs) instead of product requirements in the form of proposed purchase orders or will that only happen if a BOM is defined for a Finished Good Product? Thanks, Michael
        Hide
        pfm.smits Pierre Smits added a comment -

        Michael Powacht] You could create a new good type, say TGOOD (Trade Goods) with parentId=GOOD, and use that as the basis for all dependent entities related to inventory, orders, and accounting.

        Show
        pfm.smits Pierre Smits added a comment - Michael Powacht ] You could create a new good type, say TGOOD (Trade Goods) with parentId=GOOD, and use that as the basis for all dependent entities related to inventory, orders, and accounting.
        Hide
        mpowacht Michael Powacht added a comment -

        Sounds good Pierra, thanks!
        Sorry by the way for the triple post. I was facing some internet issues earlier when replying.

        Show
        mpowacht Michael Powacht added a comment - Sounds good Pierra, thanks! Sorry by the way for the triple post. I was facing some internet issues earlier when replying.
        Hide
        pfm.smits Pierre Smits added a comment -

        No worries. It happens to all of us at times.

        Show
        pfm.smits Pierre Smits added a comment - No worries. It happens to all of us at times.
        Hide
        jacopoc Jacopo Cappellato added a comment -

        The (naive) mechanism currently implemented is the following: if the product has a BOM and doesn't have SupplierProduct associated then it is treated as a finished output from Mfg.

        Show
        jacopoc Jacopo Cappellato added a comment - The (naive) mechanism currently implemented is the following: if the product has a BOM and doesn't have SupplierProduct associated then it is treated as a finished output from Mfg.
        Hide
        mpowacht Michael Powacht added a comment -

        Thanks Jacopo! That's very useful information.

        Show
        mpowacht Michael Powacht added a comment - Thanks Jacopo! That's very useful information.

          People

          • Assignee:
            jacques.le.roux Jacques Le Roux
            Reporter:
            mpowacht Michael Powacht
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development