Invoice Total and Outstanding Amount rounding
Locked
Invoice Total and Outstanding Amount rounding
 
|
Hello, When trying to import some of my PAID invoices in OFBiz, i expect
to have Outstanding Amout "0" (paid), but i see that is "-0.04". It can be tested by import the following into OFBIZ: <entity-engine-xml> The problem here i think is this: - Invoice Total = quantity * amount (same as Unit Price from the InvoiceItem entity) = 74.45 * 121.8675 - the above operation, in the "Invoices List" page is calculated (not as expected): - 74.45 * 121.867 (only three decimals - can be seen
in the "Invoice Overview" page) = to 9,072.99815, wich is rounded
to 9,073.00 - the expected result is 74.45 * 121.8675 (with four decimals)=9,073.035375 wich rounded = 9,073.04 - Invoice Total - Payment Applied = 9,073.00 - 9,073.04 = -0.04 ( the issue that i got) How can i set OFBiz to use more that 3 decimals in calculations? Thanks. |
Re: Invoice Total and Outstanding Amount rounding
|
|
Hello Andrei,
There is a configuration file available (accounting/config/arithmetic.properties:29). Here you can configure invoice.decimals and other related configurations. HTH. -- Best Regards, Suraj Khurana Senior Technical Consultant On Thu, Mar 11, 2021 at 11:55 PM Andrei Stan <[hidden email]> wrote: > Hello, > > When trying to import some of my PAID invoices in OFBiz, i expect to have > Outstanding Amout "0" (paid), but i see that is "-0.04". > > It can be tested by import the following into OFBIZ: > > <entity-engine-xml> > <Invoice invoiceId="TestInvoice" invoiceTypeId="SALES_INVOICE" > partyIdFrom="Company" partyId="DemoCustCompany" statusId="INVOICE_PAID" > invoiceDate="2020-11-06 15:26:25.000" dueDate="2020-11-13" > currencyUomId="RON"/> > <InvoiceItem invoiceId="TestInvoice" invoiceItemSeqId="1" uomId="RON" > quantity="74.45" amount="121.8675" description="Consultancy services"/> > <Payment amount="9073.04" statusId="PMNT_RECEIVED" > partyIdFrom="DemoCustCompany" actualCurrencyAmount="44228.3489" > currencyUomId="RON" paymentId="TestInvoice" actualCurrencyUomId="RON" > partyIdTo="Company" paymentTypeId="CUSTOMER_PAYMENT"/> > <PaymentApplication paymentApplicationId="2021-Services" > paymentId="TestInvoice" invoiceId="TestInvoice" amountApplied="9073.04"/> > </entity-engine-xml> > > The problem here i think is this: > > - Invoice Total = quantity * amount (same as Unit Price from the > InvoiceItem entity) = 74.45 * 121.8675 > > - the above operation, in the "Invoices List" page is calculated (not as > expected): > > - 74.45 * 121.*867 *(only three decimals - can be seen in the > "Invoice Overview" page) = to 9,072.99815, wich is rounded to 9,073.00 > > - the expected result is 74.45 * 121.*8675* (with four > decimals)=9,073.035375 wich rounded = 9,073.04 > > - Invoice Total - Payment Applied = 9,073.00 - 9,073.04 = -0.04 ( the > issue that i got) > > How can i set OFBiz to use more that 3 decimals in calculations? > > Thanks. > |
Re: Invoice Total and Outstanding Amount rounding
|
|
Thank you Suraj, very helpful.
On 12.03.2021 06:51, Suraj Khurana wrote: > Hello Andrei, > > There is a configuration file available > (accounting/config/arithmetic.properties:29). Here you can configure > invoice.decimals and other related configurations. > HTH. > > -- > > Best Regards, > Suraj Khurana > Senior Technical Consultant > > > On Thu, Mar 11, 2021 at 11:55 PM Andrei Stan <[hidden email]> > wrote: > >> Hello, >> >> When trying to import some of my PAID invoices in OFBiz, i expect to have >> Outstanding Amout "0" (paid), but i see that is "-0.04". >> >> It can be tested by import the following into OFBIZ: >> >> <entity-engine-xml> >> <Invoice invoiceId="TestInvoice" invoiceTypeId="SALES_INVOICE" >> partyIdFrom="Company" partyId="DemoCustCompany" statusId="INVOICE_PAID" >> invoiceDate="2020-11-06 15:26:25.000" dueDate="2020-11-13" >> currencyUomId="RON"/> >> <InvoiceItem invoiceId="TestInvoice" invoiceItemSeqId="1" uomId="RON" >> quantity="74.45" amount="121.8675" description="Consultancy services"/> >> <Payment amount="9073.04" statusId="PMNT_RECEIVED" >> partyIdFrom="DemoCustCompany" actualCurrencyAmount="44228.3489" >> currencyUomId="RON" paymentId="TestInvoice" actualCurrencyUomId="RON" >> partyIdTo="Company" paymentTypeId="CUSTOMER_PAYMENT"/> >> <PaymentApplication paymentApplicationId="2021-Services" >> paymentId="TestInvoice" invoiceId="TestInvoice" amountApplied="9073.04"/> >> </entity-engine-xml> >> >> The problem here i think is this: >> >> - Invoice Total = quantity * amount (same as Unit Price from the >> InvoiceItem entity) = 74.45 * 121.8675 >> >> - the above operation, in the "Invoices List" page is calculated (not as >> expected): >> >> - 74.45 * 121.*867 *(only three decimals - can be seen in the >> "Invoice Overview" page) = to 9,072.99815, wich is rounded to 9,073.00 >> >> - the expected result is 74.45 * 121.*8675* (with four >> decimals)=9,073.035375 wich rounded = 9,073.04 >> >> - Invoice Total - Payment Applied = 9,073.00 - 9,073.04 = -0.04 ( the >> issue that i got) >> >> How can i set OFBiz to use more that 3 decimals in calculations? >> >> Thanks. >> |
Re: Invoice Total and Outstanding Amount rounding
|
|
How about changing the decimals number for the "Unit Price" of an Item
in the ../accounting/control/invoiceOverview -> Items panel. I want to change from 3 to 4 decimals: 121.867 -> 121.8675. Thank you. On 12.03.2021 14:18, Andrei Stan wrote: > Thank you Suraj, very helpful. > > On 12.03.2021 06:51, Suraj Khurana wrote: >> Hello Andrei, >> >> There is a configuration file available >> (accounting/config/arithmetic.properties:29). Here you can configure >> invoice.decimals and other related configurations. >> HTH. >> >> -- >> >> Best Regards, >> Suraj Khurana >> Senior Technical Consultant >> >> >> On Thu, Mar 11, 2021 at 11:55 PM Andrei Stan <[hidden email]> >> wrote: >> >>> Hello, >>> >>> When trying to import some of my PAID invoices in OFBiz, i expect to >>> have >>> Outstanding Amout "0" (paid), but i see that is "-0.04". >>> >>> It can be tested by import the following into OFBIZ: >>> >>> <entity-engine-xml> >>> <Invoice invoiceId="TestInvoice" invoiceTypeId="SALES_INVOICE" >>> partyIdFrom="Company" partyId="DemoCustCompany" statusId="INVOICE_PAID" >>> invoiceDate="2020-11-06 15:26:25.000" dueDate="2020-11-13" >>> currencyUomId="RON"/> >>> <InvoiceItem invoiceId="TestInvoice" invoiceItemSeqId="1" >>> uomId="RON" >>> quantity="74.45" amount="121.8675" description="Consultancy services"/> >>> <Payment amount="9073.04" statusId="PMNT_RECEIVED" >>> partyIdFrom="DemoCustCompany" actualCurrencyAmount="44228.3489" >>> currencyUomId="RON" paymentId="TestInvoice" actualCurrencyUomId="RON" >>> partyIdTo="Company" paymentTypeId="CUSTOMER_PAYMENT"/> >>> <PaymentApplication paymentApplicationId="2021-Services" >>> paymentId="TestInvoice" invoiceId="TestInvoice" >>> amountApplied="9073.04"/> >>> </entity-engine-xml> >>> >>> The problem here i think is this: >>> >>> - Invoice Total = quantity * amount (same as Unit Price from the >>> InvoiceItem entity) = 74.45 * 121.8675 >>> >>> - the above operation, in the "Invoices List" page is calculated >>> (not as >>> expected): >>> >>> - 74.45 * 121.*867 *(only three decimals - can be seen in the >>> "Invoice Overview" page) = to 9,072.99815, wich is rounded to 9,073.00 >>> >>> - the expected result is 74.45 * 121.*8675* (with four >>> decimals)=9,073.035375 wich rounded = 9,073.04 >>> >>> - Invoice Total - Payment Applied = 9,073.00 - 9,073.04 = -0.04 ( the >>> issue that i got) >>> >>> How can i set OFBiz to use more that 3 decimals in calculations? >>> >>> Thanks. >>> |
Re: Invoice Total and Outstanding Amount rounding
|
|
Hello,
After some investigations, i found out that when importing data into OFBiz, Invoice Item's "Unit Price" is imported only with 3 decimals in the database. So instead of 12.3456 in the database we find 12.345 and this is not what i need. This is because sql-type="NUMERIC(18,3)". To test this, i modified in file "../framework/entity/fieldtype/fieldtypepostgres.xml" sql-type to be "NUMERIC(18,4): <field-type-def type="currency-precise" sql-type="NUMERIC(18,4)" java-type="java.math.BigDecimal"/>. This solves my issue with the calculus described in the begining, now the outstanding amount for paid invoices are "0" as it should be. What do you guys think? can i change this without any impact on other entities? Do you have other suggestions ? Thank you, A. On 12.03.2021 15:00, Andrei Stan wrote: > How about changing the decimals number for the "Unit Price" of an Item > in the ../accounting/control/invoiceOverview -> Items panel. I want to > change from 3 to 4 decimals: 121.867 -> 121.8675. > > Thank you. > > On 12.03.2021 14:18, Andrei Stan wrote: >> Thank you Suraj, very helpful. >> >> On 12.03.2021 06:51, Suraj Khurana wrote: >>> Hello Andrei, >>> >>> There is a configuration file available >>> (accounting/config/arithmetic.properties:29). Here you can configure >>> invoice.decimals and other related configurations. >>> HTH. >>> >>> -- >>> >>> Best Regards, >>> Suraj Khurana >>> Senior Technical Consultant >>> >>> >>> On Thu, Mar 11, 2021 at 11:55 PM Andrei Stan <[hidden email]> >>> wrote: >>> >>>> Hello, >>>> >>>> When trying to import some of my PAID invoices in OFBiz, i expect >>>> to have >>>> Outstanding Amout "0" (paid), but i see that is "-0.04". >>>> >>>> It can be tested by import the following into OFBIZ: >>>> >>>> <entity-engine-xml> >>>> <Invoice invoiceId="TestInvoice" invoiceTypeId="SALES_INVOICE" >>>> partyIdFrom="Company" partyId="DemoCustCompany" >>>> statusId="INVOICE_PAID" >>>> invoiceDate="2020-11-06 15:26:25.000" dueDate="2020-11-13" >>>> currencyUomId="RON"/> >>>> <InvoiceItem invoiceId="TestInvoice" invoiceItemSeqId="1" >>>> uomId="RON" >>>> quantity="74.45" amount="121.8675" description="Consultancy >>>> services"/> >>>> <Payment amount="9073.04" statusId="PMNT_RECEIVED" >>>> partyIdFrom="DemoCustCompany" actualCurrencyAmount="44228.3489" >>>> currencyUomId="RON" paymentId="TestInvoice" actualCurrencyUomId="RON" >>>> partyIdTo="Company" paymentTypeId="CUSTOMER_PAYMENT"/> >>>> <PaymentApplication paymentApplicationId="2021-Services" >>>> paymentId="TestInvoice" invoiceId="TestInvoice" >>>> amountApplied="9073.04"/> >>>> </entity-engine-xml> >>>> >>>> The problem here i think is this: >>>> >>>> - Invoice Total = quantity * amount (same as Unit Price from the >>>> InvoiceItem entity) = 74.45 * 121.8675 >>>> >>>> - the above operation, in the "Invoices List" page is calculated >>>> (not as >>>> expected): >>>> >>>> - 74.45 * 121.*867 *(only three decimals - can be seen in the >>>> "Invoice Overview" page) = to 9,072.99815, wich is rounded to 9,073.00 >>>> >>>> - the expected result is 74.45 * 121.*8675* (with four >>>> decimals)=9,073.035375 wich rounded = 9,073.04 >>>> >>>> - Invoice Total - Payment Applied = 9,073.00 - 9,073.04 = -0.04 ( the >>>> issue that i got) >>>> >>>> How can i set OFBiz to use more that 3 decimals in calculations? >>>> >>>> Thanks. >>>> > |
Re: Invoice Total and Outstanding Amount rounding
Administrator
|
Hi Andrei,
I don't think you would cross any issues. IIRR, we decided for 3 decimals because this is what was used when the € was introduced to convert from European moneys. At least I proposed that. I also remember I had to use six (6!) decimals in a custom project in 2007 to minimize calculus issues. We used OFBiz 4.0 then, so maybe not relevant ;) HTH Jacques Le 19/03/2021 à 23:27, Andrei Stan a écrit : > Hello, > > After some investigations, i found out that when importing data into OFBiz, Invoice Item's "Unit Price" is imported only with 3 decimals in the > database. > > So instead of 12.3456 in the database we find 12.345 and this is not what i need. > > This is because sql-type="NUMERIC(18,3)". > > To test this, i modified in file "../framework/entity/fieldtype/fieldtypepostgres.xml" sql-type to be "NUMERIC(18,4): > > <field-type-def type="currency-precise" sql-type="NUMERIC(18,4)" java-type="java.math.BigDecimal"/>. > > This solves my issue with the calculus described in the begining, now the outstanding amount for paid invoices are "0" as it should be. > > What do you guys think? can i change this without any impact on other entities? > > Do you have other suggestions ? > > Thank you, > > A. > > On 12.03.2021 15:00, Andrei Stan wrote: >> How about changing the decimals number for the "Unit Price" of an Item in the ../accounting/control/invoiceOverview -> Items panel. I want to >> change from 3 to 4 decimals: 121.867 -> 121.8675. >> >> Thank you. >> >> On 12.03.2021 14:18, Andrei Stan wrote: >>> Thank you Suraj, very helpful. >>> >>> On 12.03.2021 06:51, Suraj Khurana wrote: >>>> Hello Andrei, >>>> >>>> There is a configuration file available >>>> (accounting/config/arithmetic.properties:29). Here you can configure >>>> invoice.decimals and other related configurations. >>>> HTH. >>>> >>>> -- >>>> >>>> Best Regards, >>>> Suraj Khurana >>>> Senior Technical Consultant >>>> >>>> >>>> On Thu, Mar 11, 2021 at 11:55 PM Andrei Stan <[hidden email]> >>>> wrote: >>>> >>>>> Hello, >>>>> >>>>> When trying to import some of my PAID invoices in OFBiz, i expect to have >>>>> Outstanding Amout "0" (paid), but i see that is "-0.04". >>>>> >>>>> It can be tested by import the following into OFBIZ: >>>>> >>>>> <entity-engine-xml> >>>>> <Invoice invoiceId="TestInvoice" invoiceTypeId="SALES_INVOICE" >>>>> partyIdFrom="Company" partyId="DemoCustCompany" statusId="INVOICE_PAID" >>>>> invoiceDate="2020-11-06 15:26:25.000" dueDate="2020-11-13" >>>>> currencyUomId="RON"/> >>>>> <InvoiceItem invoiceId="TestInvoice" invoiceItemSeqId="1" uomId="RON" >>>>> quantity="74.45" amount="121.8675" description="Consultancy services"/> >>>>> <Payment amount="9073.04" statusId="PMNT_RECEIVED" >>>>> partyIdFrom="DemoCustCompany" actualCurrencyAmount="44228.3489" >>>>> currencyUomId="RON" paymentId="TestInvoice" actualCurrencyUomId="RON" >>>>> partyIdTo="Company" paymentTypeId="CUSTOMER_PAYMENT"/> >>>>> <PaymentApplication paymentApplicationId="2021-Services" >>>>> paymentId="TestInvoice" invoiceId="TestInvoice" amountApplied="9073.04"/> >>>>> </entity-engine-xml> >>>>> >>>>> The problem here i think is this: >>>>> >>>>> - Invoice Total = quantity * amount (same as Unit Price from the >>>>> InvoiceItem entity) = 74.45 * 121.8675 >>>>> >>>>> - the above operation, in the "Invoices List" page is calculated (not as >>>>> expected): >>>>> >>>>> - 74.45 * 121.*867 *(only three decimals - can be seen in the >>>>> "Invoice Overview" page) = to 9,072.99815, wich is rounded to 9,073.00 >>>>> >>>>> - the expected result is 74.45 * 121.*8675* (with four >>>>> decimals)=9,073.035375 wich rounded = 9,073.04 >>>>> >>>>> - Invoice Total - Payment Applied = 9,073.00 - 9,073.04 = -0.04 ( the >>>>> issue that i got) >>>>> >>>>> How can i set OFBiz to use more that 3 decimals in calculations? >>>>> >>>>> Thanks. >>>>> >> > |
Re: Invoice Total and Outstanding Amount rounding
|
|
Hello Jacques,
I think this is the solution for me, at least the easy one, because i need to convert currency with 4 decimals, so differences occur. Thank you. On 20.03.2021 10:50, Jacques Le Roux wrote: > Hi Andrei, > > I don't think you would cross any issues. IIRR, we decided for 3 > decimals because this is what was used when the € was introduced to > convert from European moneys. At least I proposed that. > > I also remember I had to use six (6!) decimals in a custom project in > 2007 to minimize calculus issues. We used OFBiz 4.0 then, so maybe not > relevant ;) > > HTH > > Jacques > > Le 19/03/2021 à 23:27, Andrei Stan a écrit : >> Hello, >> >> After some investigations, i found out that when importing data into >> OFBiz, Invoice Item's "Unit Price" is imported only with 3 decimals >> in the database. >> >> So instead of 12.3456 in the database we find 12.345 and this is not >> what i need. >> >> This is because sql-type="NUMERIC(18,3)". >> >> To test this, i modified in file >> "../framework/entity/fieldtype/fieldtypepostgres.xml" sql-type to be >> "NUMERIC(18,4): >> >> <field-type-def type="currency-precise" sql-type="NUMERIC(18,4)" >> java-type="java.math.BigDecimal"/>. >> >> This solves my issue with the calculus described in the begining, now >> the outstanding amount for paid invoices are "0" as it should be. >> >> What do you guys think? can i change this without any impact on other >> entities? >> >> Do you have other suggestions ? >> >> Thank you, >> >> A. >> >> On 12.03.2021 15:00, Andrei Stan wrote: >>> How about changing the decimals number for the "Unit Price" of an >>> Item in the ../accounting/control/invoiceOverview -> Items panel. I >>> want to change from 3 to 4 decimals: 121.867 -> 121.8675. >>> >>> Thank you. >>> >>> On 12.03.2021 14:18, Andrei Stan wrote: >>>> Thank you Suraj, very helpful. >>>> >>>> On 12.03.2021 06:51, Suraj Khurana wrote: >>>>> Hello Andrei, >>>>> >>>>> There is a configuration file available >>>>> (accounting/config/arithmetic.properties:29). Here you can configure >>>>> invoice.decimals and other related configurations. >>>>> HTH. >>>>> >>>>> -- >>>>> >>>>> Best Regards, >>>>> Suraj Khurana >>>>> Senior Technical Consultant >>>>> >>>>> >>>>> On Thu, Mar 11, 2021 at 11:55 PM Andrei Stan >>>>> <[hidden email]> >>>>> wrote: >>>>> >>>>>> Hello, >>>>>> >>>>>> When trying to import some of my PAID invoices in OFBiz, i expect >>>>>> to have >>>>>> Outstanding Amout "0" (paid), but i see that is "-0.04". >>>>>> >>>>>> It can be tested by import the following into OFBIZ: >>>>>> >>>>>> <entity-engine-xml> >>>>>> <Invoice invoiceId="TestInvoice" invoiceTypeId="SALES_INVOICE" >>>>>> partyIdFrom="Company" partyId="DemoCustCompany" >>>>>> statusId="INVOICE_PAID" >>>>>> invoiceDate="2020-11-06 15:26:25.000" dueDate="2020-11-13" >>>>>> currencyUomId="RON"/> >>>>>> <InvoiceItem invoiceId="TestInvoice" invoiceItemSeqId="1" >>>>>> uomId="RON" >>>>>> quantity="74.45" amount="121.8675" description="Consultancy >>>>>> services"/> >>>>>> <Payment amount="9073.04" statusId="PMNT_RECEIVED" >>>>>> partyIdFrom="DemoCustCompany" actualCurrencyAmount="44228.3489" >>>>>> currencyUomId="RON" paymentId="TestInvoice" >>>>>> actualCurrencyUomId="RON" >>>>>> partyIdTo="Company" paymentTypeId="CUSTOMER_PAYMENT"/> >>>>>> <PaymentApplication paymentApplicationId="2021-Services" >>>>>> paymentId="TestInvoice" invoiceId="TestInvoice" >>>>>> amountApplied="9073.04"/> >>>>>> </entity-engine-xml> >>>>>> >>>>>> The problem here i think is this: >>>>>> >>>>>> - Invoice Total = quantity * amount (same as Unit Price from the >>>>>> InvoiceItem entity) = 74.45 * 121.8675 >>>>>> >>>>>> - the above operation, in the "Invoices List" page is calculated >>>>>> (not as >>>>>> expected): >>>>>> >>>>>> - 74.45 * 121.*867 *(only three decimals - can be seen in the >>>>>> "Invoice Overview" page) = to 9,072.99815, wich is rounded to >>>>>> 9,073.00 >>>>>> >>>>>> - the expected result is 74.45 * 121.*8675* (with four >>>>>> decimals)=9,073.035375 wich rounded = 9,073.04 >>>>>> >>>>>> - Invoice Total - Payment Applied = 9,073.00 - 9,073.04 = -0.04 ( >>>>>> the >>>>>> issue that i got) >>>>>> >>>>>> How can i set OFBiz to use more that 3 decimals in calculations? >>>>>> >>>>>> Thanks. >>>>>> >>> >> |
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |