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

Problem with Purchase Order Accounting Entries

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: Release Branch 13.07, Trunk
    • Fix Version/s: 14.12.01, 13.07.02, 16.11.01
    • Component/s: accounting, order
    • Labels:
      None
    • Sprint:
      Sharan - Jira Test Sprint

      Description

      Hi

      I'm logging a Jira issue reported by Mohd Vigar on the mailing list. I have been able to replicate the problem and think that this is a bug. Details are below.

      I've also added some comments on another possible error that I've found around payments while I was doing these tests.

      Thanks
      Sharan

      --------------------
      I'm facing the below problem with accounting entries of purchase order, would appreciate any help with the same

      When I create a purchase order for lets say $15 and receive the inventory for that purchase order, I get the following accounting entry.

      Inventory Dr $15
      Uninvoiced Item Receipts Cr $15

      Then when I go to the purchase invoice that's automatically generated by receiving the product and change its status to "Ready', it creates a new accounting transaction entry as follows

      Inventory Dr $15
      Accounts Payable Cr $15

      thus the Trial balance show:

      Account Code Account Name Dr Cr Ending Balance
      140000 INVENTORY $30.00 $0.00 $30.00
      210000 ACCOUNTS PAYABLE $0.00 $15.00 $15.00
      214000 UNINVOICED ITEM RECEIPTS $0.00 $15.00 $15.00

      It should have ideally reversed the uninvoiced item receipts as follows

      Uninvoiced Item Receipts Dr $15
      Accounts Payable Cr $15

      Account Code Account Name Dr Cr Ending Balance
      140000 INVENTORY $15.00 $0.00 $15.00
      210000 ACCOUNTS PAYABLE $0.00 $15.00 $15.00
      214000 UNINVOICED ITEM RECEIPTS $15.00 $15.00 $0.00

      I've tested this on the trunk with the below mentioned data and it give me the same results..

      Product : GZ-1000-0 - Tiny Gizmo
      Quantity : 2
      Price : $7.5
      Total value: $15
      Supplier : Demo Supplier

      so i'm wondering if I'm doing something wrong or if this is a bug..
      -----------------

      During my tests I did find another potential issue and this is related to payments. I’ve come across a variation of it before replying to a query on the Drop Shipment process.

      • When you receive Purchase Order into inventory a “payment” is created under the “Payment Information” section of the Purchase Order.
      • Using the example above, the Payment is created for an amount of $15 with a status of “Not Paid”
      • When you receive the Purchase Order, the Pro Forma invoice is also created and is displayed below the payment on the view order screen.
      • If you click the link to the Pro Forma Invoice and look at the “Terms” section which shows the “Outstanding Amount” you will see that it is now showing zero. (I don’t think this is correct because you haven’t paid the supplier yet! I think the Outstanding Amount on the Pro Forma invoice should still be $15.)

      If you then go to Accounting Manager / Payments to locate Purchase Invoices by due date then this invoice isnt displayed even though the Due Date is the current date. I think this is because the Outstanding Amount is zero, OFBiz thinks that the invoice has already been paid (when it hasn’t).

        Activity

        Hide
        mhdwaqar Mohd Viqar added a comment -

        Sharon,
        For anybody using the purchase order process, this bug throws off accounting completely. So I think the priority should not be minor.

        Show
        mhdwaqar Mohd Viqar added a comment - Sharon, For anybody using the purchase order process, this bug throws off accounting completely. So I think the priority should not be minor.
        Hide
        sharan Sharan Foga added a comment -

        Hi Mohd - I will change the priority to Major but it will still need to be reviewed by a committer to confirm the priority.

        Show
        sharan Sharan Foga added a comment - Hi Mohd - I will change the priority to Major but it will still need to be reviewed by a committer to confirm the priority.
        Hide
        mz4wheeler mz4wheeler added a comment -

        Great work Sharan. I was wondering if you had a chance to test with 11.04 and 12.04, or even 9.04. That may narrow down the issue. I would expect 9.04 to work perfectly.

        Show
        mz4wheeler mz4wheeler added a comment - Great work Sharan. I was wondering if you had a chance to test with 11.04 and 12.04, or even 9.04. That may narrow down the issue. I would expect 9.04 to work perfectly.
        Hide
        sharan Sharan Foga added a comment -

        Hi All

        I've identified that the incorrect accounting entries are due to OFBiz configuration. The Inventory Account is defined as the default account to use in the GL Account Defaults for the Purchase Invoice. If you change the default account for the Purchase Invoice line item to Uninvoiced Item Receipts then OFBiz will generate the correct accounting transaction.

        I'm guessing that if it worked in previous versions, maybe the default accounts for these line items have been changed. I will downgrade the priority of this while I check on the payment issue I identified. If it is still happening it may be cleaner to close this issue and open a new one specifically for the payment issue. I will let everyone know.

        Show
        sharan Sharan Foga added a comment - Hi All I've identified that the incorrect accounting entries are due to OFBiz configuration. The Inventory Account is defined as the default account to use in the GL Account Defaults for the Purchase Invoice. If you change the default account for the Purchase Invoice line item to Uninvoiced Item Receipts then OFBiz will generate the correct accounting transaction. I'm guessing that if it worked in previous versions, maybe the default accounts for these line items have been changed. I will downgrade the priority of this while I check on the payment issue I identified. If it is still happening it may be cleaner to close this issue and open a new one specifically for the payment issue. I will let everyone know.
        Hide
        sharan Sharan Foga added a comment -

        Hi
        The payment issue is still happening. I will close this issue and create a new Jira for it as it may be confusing to follow it in this issue as the original issue is resolved by configuration.

        Show
        sharan Sharan Foga added a comment - Hi The payment issue is still happening. I will close this issue and create a new Jira for it as it may be confusing to follow it in this issue as the original issue is resolved by configuration.
        Hide
        sharan Sharan Foga added a comment -

        Accounting issues resolved through configuration of GL Account Defaults Purchase Invoice line types.

        Show
        sharan Sharan Foga added a comment - Accounting issues resolved through configuration of GL Account Defaults Purchase Invoice line types.
        Hide
        mhdwaqar Mohd Viqar added a comment -

        Purchase Order accounting issue is resolved and purchase order accounting entries are recorded as needed. But now I have one more question, When I return a purchase order everything is fine but i cant see any accounting entries for purchase returns.

        Thanks

        Show
        mhdwaqar Mohd Viqar added a comment - Purchase Order accounting issue is resolved and purchase order accounting entries are recorded as needed. But now I have one more question, When I return a purchase order everything is fine but i cant see any accounting entries for purchase returns. Thanks
        Hide
        pfm.smits Pierre Smits added a comment -

        Hi Sharan,

        Like for any other registration in components such as order and facility you need to have the mapping in place between the attributes of those objects and the accounting counterparts.

        Did you check whether these exist for a purchase return?

        Regards,

        Pierre

        Show
        pfm.smits Pierre Smits added a comment - Hi Sharan, Like for any other registration in components such as order and facility you need to have the mapping in place between the attributes of those objects and the accounting counterparts. Did you check whether these exist for a purchase return? Regards, Pierre
        Hide
        jacques.le.roux Jacques Le Roux added a comment -

        Pkease Mohd Viqar, if needed open a new Jira issiue with a complete description of your problem

        Why

        • What you were looking for
        • How did you try to achieve it
        • What did you expect as results
        • And what you really got

        How

        1. What you did (including detailed steps to reproduce)
        2. What you expected to happen
        3. What actually happened (including exact quotes of error messages, etc)
        4. If possible provide an URL
        Show
        jacques.le.roux Jacques Le Roux added a comment - Pkease Mohd Viqar, if needed open a new Jira issiue with a complete description of your problem Why What you were looking for How did you try to achieve it What did you expect as results And what you really got How What you did (including detailed steps to reproduce) What you expected to happen What actually happened (including exact quotes of error messages, etc) If possible provide an URL
        Hide
        vitech_it Storm Trinh added a comment -

        Dear Sharan Foga Sharan Foga

        I agree with you but if i create Purchase Invoice without Purchase Order, the incorrect accounting transaction happen.

        The Inventory Account in the GL Account Defaults also use for COGS in Sales, i think we could resolve as one specifically for the Purchase Invoice.

        What do you think about it ?

        Show
        vitech_it Storm Trinh added a comment - Dear Sharan Foga Sharan Foga I agree with you but if i create Purchase Invoice without Purchase Order, the incorrect accounting transaction happen. The Inventory Account in the GL Account Defaults also use for COGS in Sales, i think we could resolve as one specifically for the Purchase Invoice. What do you think about it ?
        Hide
        sharan sharan added a comment -

        Hi Storm

        This Jira issue has already been resolved and the issue was related to the mappings. If you are having a problem with the Purchase Invoice then please create a new Jira with the details of:

        The steps you used to generate the issue
        What results you expected and what results you actually got.

        Also the fact that you do not have Purchase Order makes this a different use case.

        Thanks
        Sharan

        Show
        sharan sharan added a comment - Hi Storm This Jira issue has already been resolved and the issue was related to the mappings. If you are having a problem with the Purchase Invoice then please create a new Jira with the details of: The steps you used to generate the issue What results you expected and what results you actually got. Also the fact that you do not have Purchase Order makes this a different use case. Thanks Sharan
        Hide
        vitech_it Storm Trinh added a comment -

        Thank Sharan,

        This is a different use case, so we need some new invoice item types (GL Account Defaults Purchase) for it.

        Show
        vitech_it Storm Trinh added a comment - Thank Sharan, This is a different use case, so we need some new invoice item types (GL Account Defaults Purchase) for it.
        Hide
        golja Anahita Goljahani added a comment -

        Hi, please find attached a patch that corrects the wrong default GL Account assignment in accounting demo data.

        Kind regards
        Anahita

        Show
        golja Anahita Goljahani added a comment - Hi, please find attached a patch that corrects the wrong default GL Account assignment in accounting demo data. Kind regards Anahita
        Hide
        mz4wheeler mz4wheeler added a comment -

        Thanks for the patch. I was wondering if it also solves the drop ship issue:

        > I’ve come across a variation of it before replying to a query on the Drop Shipment process.

        Show
        mz4wheeler mz4wheeler added a comment - Thanks for the patch. I was wondering if it also solves the drop ship issue: > I’ve come across a variation of it before replying to a query on the Drop Shipment process.
        Hide
        sharan Sharan Foga added a comment -

        Thanks for the patch Anahita. I will take a look at it and test it.

        Show
        sharan Sharan Foga added a comment - Thanks for the patch Anahita. I will take a look at it and test it.
        Hide
        sharan Sharan Foga added a comment -

        I will test the patch provided by Anahita to resolve the GL incorrect mapping

        Show
        sharan Sharan Foga added a comment - I will test the patch provided by Anahita to resolve the GL incorrect mapping
        Hide
        golja Anahita Goljahani added a comment -

        Hi,

        I'm testing the drop shipment scenario. I'll keep you posted.

        Kind regards

        Anahita

        Show
        golja Anahita Goljahani added a comment - Hi, I'm testing the drop shipment scenario. I'll keep you posted. Kind regards Anahita
        Hide
        golja Anahita Goljahani added a comment -

        Hi,

        while testing the drop shipment scenario, I made some changes to my patch in order to correctly map the purchase invoice item types 'PINV_SPROD_ITEM' (service) and PINV_DPROD_ITEM (digital good) to the default GL Account '500000' (Cost of Goods sold) (Sharan Foga, please discard the older one).

        As far as my tests of the drop shipment scenario are concerned, I found that the reason for the incorrect accounting posts is that the purchase invoice item type is set to 'PINV_FPROD_ITEM' (finished goods) for the product. Due to this setting, when the status of the purchase invoice is set to 'ready', the following accounting entries are posted (after application of the patch):

        Debit Uninvoiced Item Receipt
        Credit Accounts Payable

        which are correct in the case of purchases for the inventory, but not for drop shipments. In the latter case, the debit to 'Uninvoiced Item Receipt' account should be replaced by a debit to 'Cost of Goods Sold' account.

        One way to achieve this is to manually set the purchase invoice item type for the product to 'PINV_SPROD_ITEM' (service). It can be done by going to the 'ITEMS' tab from the purchase invoice screen (the screen from which the invoice is set to 'ready') and change 'Invoice Finished Good Item (Purchase)' to 'Invoice Service Product Item (Purchase)'. With the new item type (and the new mapping provided by the patch), when the purchase invoice status is set to 'ready', the following transaction entries are posted:

        Debit Cost of Goods Sold
        Credit Accounts Payable

        Let me know whether these entries are the ones you were looking for or you have a different way of posting accounting entries for drop shipment.

        Kind regards
        Anahita

        Show
        golja Anahita Goljahani added a comment - Hi, while testing the drop shipment scenario, I made some changes to my patch in order to correctly map the purchase invoice item types 'PINV_SPROD_ITEM' (service) and PINV_DPROD_ITEM (digital good) to the default GL Account '500000' (Cost of Goods sold) ( Sharan Foga , please discard the older one). As far as my tests of the drop shipment scenario are concerned, I found that the reason for the incorrect accounting posts is that the purchase invoice item type is set to 'PINV_FPROD_ITEM' (finished goods) for the product. Due to this setting, when the status of the purchase invoice is set to 'ready', the following accounting entries are posted (after application of the patch): Debit Uninvoiced Item Receipt Credit Accounts Payable which are correct in the case of purchases for the inventory, but not for drop shipments. In the latter case, the debit to 'Uninvoiced Item Receipt' account should be replaced by a debit to 'Cost of Goods Sold' account. One way to achieve this is to manually set the purchase invoice item type for the product to 'PINV_SPROD_ITEM' (service). It can be done by going to the 'ITEMS' tab from the purchase invoice screen (the screen from which the invoice is set to 'ready') and change 'Invoice Finished Good Item (Purchase)' to 'Invoice Service Product Item (Purchase)'. With the new item type (and the new mapping provided by the patch), when the purchase invoice status is set to 'ready', the following transaction entries are posted: Debit Cost of Goods Sold Credit Accounts Payable Let me know whether these entries are the ones you were looking for or you have a different way of posting accounting entries for drop shipment. Kind regards Anahita
        Hide
        sharan Sharan Foga added a comment -

        Hi Anahita

        Thanks very much for working on this. I've tested your patch and am happy that it works for finished goods products. It also resolves the original issue.

        I've also run through some tests using the drop shipment scenario and agree that the accounting entry should go against COGS instead of Uninvoiced Item Receipts. The workaround of fixing each of the of the drop ship purchase invoice items is doable in small quantities so will see what the feedback is. ([~mz4wheeler - Mike please let us know if this fixes your issue]

        Thanks
        Sharan

        Show
        sharan Sharan Foga added a comment - Hi Anahita Thanks very much for working on this. I've tested your patch and am happy that it works for finished goods products. It also resolves the original issue. I've also run through some tests using the drop shipment scenario and agree that the accounting entry should go against COGS instead of Uninvoiced Item Receipts. The workaround of fixing each of the of the drop ship purchase invoice items is doable in small quantities so will see what the feedback is. ( [~mz4wheeler - Mike please let us know if this fixes your issue] Thanks Sharan
        Hide
        sharan Sharan Foga added a comment -

        Applied patch from Anahita that fixes the original issue. Also backported fix to 14.12 and 13.07

        Show
        sharan Sharan Foga added a comment - Applied patch from Anahita that fixes the original issue. Also backported fix to 14.12 and 13.07

          People

          • Assignee:
            sharan Sharan Foga
            Reporter:
            sharan Sharan Foga
          • Votes:
            3 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development

                Agile