Author: diveshdutta
Date: Sat Nov 5 11:05:04 2016
New Revision: 1768186
URL: http://svn.apache.org/viewvc?rev=1768186&view=rev
Log:
Improvement: Vat tax should be recorded as separate line items in sales invoice
when products have vat tax included in its price. Added new Invoice Item type
for vat tax. This will enable posting taxes in liability account and actual
product's price in revenue account. So this will eventually fix accounting
transactions and reports.
(OFBIZ-7012)
Thanks: Thanks Ankush Upadhyay for your patch and Scott for design discussions
Modified:
ofbiz/trunk/applications/accounting/data/AccountingTypeData.xml
ofbiz/trunk/applications/accounting/src/main/java/org/apache/ofbiz/accounting/invoice/InvoiceServices.java
ofbiz/trunk/applications/accounting/src/main/java/org/apache/ofbiz/accounting/tax/TaxAuthorityServices.java
Modified: ofbiz/trunk/applications/accounting/data/AccountingTypeData.xml
URL:
http://svn.apache.org/viewvc/ofbiz/trunk/applications/accounting/data/AccountingTypeData.xml?rev=1768186&r1=1768185&r2=1768186&view=diff
==============================================================================
--- ofbiz/trunk/applications/accounting/data/AccountingTypeData.xml (original)
+++ ofbiz/trunk/applications/accounting/data/AccountingTypeData.xml Sat Nov 5
11:05:04 2016
@@ -425,6 +425,7 @@ under the License.
<InvoiceItemType description="Invoice Item Surcharge(Sales)" hasTable="N"
invoiceItemTypeId="ITM_SURCHARGE_ADJ" parentTypeId="SINVOICE_ITM_ADJ"/>
<InvoiceItemType description="Invoice Item Additional Feature(Sales)"
hasTable="N" invoiceItemTypeId="ITM_ADD_FEATURE"
parentTypeId="SINVOICE_ITM_ADJ"/>
<InvoiceItemType description="Invoice Item Warranty(Sales)" hasTable="N"
invoiceItemTypeId="ITM_WARRANTY_ADJ" parentTypeId="SINVOICE_ITM_ADJ"/>
+ <InvoiceItemType description="Invoice Item VAT Tax(Sales)" hasTable="N"
invoiceItemTypeId="ITM_VAT_TAX" parentTypeId="SINVOICE_ITM_ADJ"/>
<InvoiceItemType description="Invoice Product Item(Sales)" hasTable="N"
invoiceItemTypeId="INV_PROD_ITEM" parentTypeId=""/>
<InvoiceItemType description="Invoice Finished Good Item(Sales)"
hasTable="N" invoiceItemTypeId="INV_FPROD_ITEM" parentTypeId="INV_PROD_ITEM"/>
@@ -562,7 +563,7 @@ under the License.
<InvoiceItemTypeMap invoiceTypeId="SALES_INVOICE"
invoiceItemMapKey="SURCHARGE_ADJUSTMENT" invoiceItemTypeId="ITM_SURCHARGE_ADJ"/>
<InvoiceItemTypeMap invoiceTypeId="SALES_INVOICE"
invoiceItemMapKey="ADDITIONAL_FEATURE" invoiceItemTypeId="ITM_ADD_FEATURE"/>
<InvoiceItemTypeMap invoiceTypeId="SALES_INVOICE"
invoiceItemMapKey="WARRANTY_ADJUSTMENT" invoiceItemTypeId="ITM_WARRANTY_ADJ"/>
-
+ <InvoiceItemTypeMap invoiceTypeId="SALES_INVOICE"
invoiceItemMapKey="VAT_TAX" invoiceItemTypeId="ITM_VAT_TAX"/>
<!-- orderItemTypeId -->
<InvoiceItemTypeMap invoiceTypeId="PURCHASE_INVOICE"
invoiceItemMapKey="INVENTORY_ORDER_ITEM" invoiceItemTypeId="PINV_INVPRD_ITEM"/>
<InvoiceItemTypeMap invoiceTypeId="PURCHASE_INVOICE"
invoiceItemMapKey="SUPPLIES_ORDER_ITEM" invoiceItemTypeId="PINV_SUPLPRD_ITEM"/>
Modified:
ofbiz/trunk/applications/accounting/src/main/java/org/apache/ofbiz/accounting/invoice/InvoiceServices.java
URL:
http://svn.apache.org/viewvc/ofbiz/trunk/applications/accounting/src/main/java/org/apache/ofbiz/accounting/invoice/InvoiceServices.java?rev=1768186&r1=1768185&r2=1768186&view=diff
==============================================================================
---
ofbiz/trunk/applications/accounting/src/main/java/org/apache/ofbiz/accounting/invoice/InvoiceServices.java
(original)
+++
ofbiz/trunk/applications/accounting/src/main/java/org/apache/ofbiz/accounting/invoice/InvoiceServices.java
Sat Nov 5 11:05:04 2016
@@ -423,7 +423,18 @@ public class InvoiceServices {
shippingApplies = true;
}
- BigDecimal billingAmount =
orderItem.getBigDecimal("unitPrice").setScale(invoiceTypeDecimals, ROUNDING);
+ BigDecimal billingAmount = BigDecimal.ZERO;
+ GenericValue OrderAdjustment =
EntityUtil.getFirst(orderItem.getRelated("OrderAdjustment",
UtilMisc.toMap("orderAdjustmentTypeId", "VAT_TAX"), null, false));
+ /* Apply formula to get actual product price to set amount in
invoice item
+ Formula is: productPrice =
(productPriceWithTax.multiply(100)) / (orderAdj sourcePercentage + 100))
+ product price = (43*100) / (20+100) = 35.83 (Here product
price is 43 with VAT)
+ */
+ if (UtilValidate.isNotEmpty(OrderAdjustment) &&
(OrderAdjustment.getBigDecimal("amount").signum() == 0) &&
UtilValidate.isNotEmpty(OrderAdjustment.getBigDecimal("amountAlreadyIncluded"))
&& OrderAdjustment.getBigDecimal("amountAlreadyIncluded").signum() != 0) {
+ BigDecimal sourcePercentageTotal =
OrderAdjustment.getBigDecimal("sourcePercentage").add(new BigDecimal(100));
+ billingAmount =
orderItem.getBigDecimal("unitPrice").divide(sourcePercentageTotal, 100,
ROUNDING).multiply(new BigDecimal(100)).setScale(invoiceTypeDecimals, ROUNDING);
+ } else {
+ billingAmount =
orderItem.getBigDecimal("unitPrice").setScale(invoiceTypeDecimals, ROUNDING);
+ }
Map<String, Object> createInvoiceItemContext = new
HashMap<String, Object>();
createInvoiceItemContext.put("invoiceId", invoiceId);
@@ -532,6 +543,11 @@ public class InvoiceServices {
// if (adj.get("amount") == null) { TODO check usage with
webPos. Was: fix a bug coming from POS in case of use of a discount (on item(s)
or sale, item(s) here) and a cash amount higher than total (hence issuing
change)
// continue;
// }
+ // Set adjustment amount as amountAlreadyIncluded to
continue invoice item creation process
+ Boolean isTaxIncludedInPrice =
adj.getString("orderAdjustmentTypeId").equals("VAT_TAX") &&
UtilValidate.isNotEmpty(adj.getBigDecimal("amountAlreadyIncluded")) &&
adj.getBigDecimal("amountAlreadyIncluded").signum() != 0;
+ if ((adj.getBigDecimal("amount").signum() == 0) &&
isTaxIncludedInPrice) {
+ adj.set("amount",
adj.getBigDecimal("amountAlreadyIncluded"));
+ }
// If the absolute invoiced amount >= the abs of the
adjustment amount, the full amount has already been invoiced, so skip this
adjustment
if
(adjAlreadyInvoicedAmount.abs().compareTo(adj.getBigDecimal("amount").setScale(invoiceTypeDecimals,
ROUNDING).abs()) > 0) {
continue;
@@ -541,10 +557,52 @@ public class InvoiceServices {
BigDecimal amount = ZERO;
if (originalOrderItemQuantity.signum() != 0) {
if (adj.get("amount") != null) {
- // pro-rate the amount
- // set decimals = 100 means we don't round this
intermediate value, which is very important
- amount =
adj.getBigDecimal("amount").divide(originalOrderItemQuantity, 100, ROUNDING);
- amount = amount.multiply(billingQuantity);
+
if("PROMOTION_ADJUSTMENT".equals(adj.getString("orderAdjustmentTypeId")) &&
adj.get("productPromoId") != null) {
+ /* Find negative amountAlreadyIncluded in
OrderAdjustment to subtract it from discounted amount.
+ As
we stored negative sales tax amount in order adjustment for discounted item.
+ */
+ List<EntityExpr> exprs =
UtilMisc.toList(EntityCondition.makeCondition("orderId", EntityOperator.EQUALS,
orderItem.getString("orderId")),
+
EntityCondition.makeCondition("orderItemSeqId", EntityOperator.EQUALS,
orderItem.getString("orderItemSeqId")),
+
EntityCondition.makeCondition("orderAdjustmentTypeId", EntityOperator.EQUALS,
"VAT_TAX"),
+
EntityCondition.makeCondition("amountAlreadyIncluded",
EntityOperator.LESS_THAN, BigDecimal.ZERO));
+ EntityCondition andCondition =
EntityCondition.makeCondition(exprs, EntityOperator.AND);
+ GenericValue orderAdjustment =
EntityUtil.getFirst(delegator.findList("OrderAdjustment", andCondition, null,
null, null, false));
+ if
(UtilValidate.isNotEmpty(orderAdjustment)) {
+ amount =
adj.getBigDecimal("amount").subtract(orderAdjustment.getBigDecimal("amountAlreadyIncluded")).setScale(100,
ROUNDING);
+ } else {
+ amount = adj.getBigDecimal("amount");
+ }
+ } else {
+ // pro-rate the amount
+ // set decimals = 100 means we don't round
this intermediate value, which is very important
+ if (isTaxIncludedInPrice) {
+ BigDecimal priceWithTax =
originalOrderItem.getBigDecimal("unitPrice");
+ // Get tax included in item price
+ amount =
priceWithTax.subtract(billingAmount);
+ amount =
amount.multiply(billingQuantity);
+ // get adjustment amount
+ /* Get tax amount of other invoice and
calculate remaining amount need to store in invoice item(Handle case of of
partial shipment and promotional item)
+
to adjust tax amount in invoice item.
+ */
+ BigDecimal otherInvoiceTaxAmount =
BigDecimal.ZERO;
+ GenericValue orderAdjBilling =
EntityUtil.getFirst(delegator.findByAnd("OrderAdjustmentBilling",
UtilMisc.toMap("orderAdjustmentId", adj.getString("orderAdjustmentId")), null,
false));
+ if
(UtilValidate.isNotEmpty(orderAdjBilling)) {
+ List<GenericValue> invoiceItems =
delegator.findByAnd("InvoiceItem",
+
UtilMisc.toMap("invoiceId", orderAdjBilling.getString("invoiceId"),
"invoiceItemTypeId", "ITM_SALES_TAX", "productId",
originalOrderItem.getString("productId")), null, isTaxIncludedInPrice);
+ for (GenericValue invoiceItem :
invoiceItems) {
+ otherInvoiceTaxAmount =
otherInvoiceTaxAmount.add(invoiceItem.getBigDecimal("amount"));
+ }
+ if
(otherInvoiceTaxAmount.compareTo(BigDecimal.ZERO) > 0) {
+ BigDecimal remainingAmount =
adj.getBigDecimal("amountAlreadyIncluded").subtract(otherInvoiceTaxAmount);
+ amount =
amount.min(remainingAmount);
+ }
+ }
+ amount =
amount.min(adj.getBigDecimal("amountAlreadyIncluded")).setScale(100, ROUNDING);
+ } else {
+ amount =
adj.getBigDecimal("amount").divide(originalOrderItemQuantity, 100, ROUNDING);
+ amount =
amount.multiply(billingQuantity);
+ }
+ }
// Tax needs to be rounded differently from other
order adjustments
if
(adj.getString("orderAdjustmentTypeId").equals("SALES_TAX")) {
amount = amount.setScale(TAX_DECIMALS,
TAX_ROUNDING);
Modified:
ofbiz/trunk/applications/accounting/src/main/java/org/apache/ofbiz/accounting/tax/TaxAuthorityServices.java
URL:
http://svn.apache.org/viewvc/ofbiz/trunk/applications/accounting/src/main/java/org/apache/ofbiz/accounting/tax/TaxAuthorityServices.java?rev=1768186&r1=1768185&r2=1768186&view=diff
==============================================================================
---
ofbiz/trunk/applications/accounting/src/main/java/org/apache/ofbiz/accounting/tax/TaxAuthorityServices.java
(original)
+++
ofbiz/trunk/applications/accounting/src/main/java/org/apache/ofbiz/accounting/tax/TaxAuthorityServices.java
Sat Nov 5 11:05:04 2016
@@ -442,17 +442,29 @@ public class TaxAuthorityServices {
}
GenericValue taxAdjValue =
delegator.makeValue("OrderAdjustment");
- if (productPrice != null &&
"Y".equals(productPrice.getString("taxInPrice"))) {
- // tax is in the price already, so we want the adjustment
to be a VAT_TAX adjustment to be subtracted instead of a SALES_TAX adjustment
to be added
+ BigDecimal discountedSalesTax = BigDecimal.ZERO;
+ taxAdjValue.set("orderAdjustmentTypeId", "SALES_TAX");
+ if (productPrice != null &&
"Y".equals(productPrice.getString("taxInPrice")) && itemQuantity !=
BigDecimal.ZERO) {
+ // For example product price is 43 with 20% VAT(means
product actual price is 35.83).
+ // itemPrice = 43;
+ // itemQuantity = 3;
+ // taxAmountIncludedInFullPrice = (43-(43/(1+(20/100))))*3
= 21.51
taxAdjValue.set("orderAdjustmentTypeId", "VAT_TAX");
-
- // the amount will be different because we want to figure
out how much of the price was tax, and not how much tax needs to be added
- // the formula is: taxAmount = priceWithTax -
(priceWithTax/(1+taxPercentage/100))
- BigDecimal taxAmountIncluded =
itemAmount.subtract(itemAmount.divide(BigDecimal.ONE.add(taxRate.divide(PERCENT_SCALE,
4, BigDecimal.ROUND_HALF_UP)), 3, BigDecimal.ROUND_HALF_UP));
- taxAdjValue.set("amountAlreadyIncluded",
taxAmountIncluded);
+ BigDecimal taxAmountIncludedInFullPrice =
itemPrice.subtract(itemPrice.divide(BigDecimal.ONE.add(taxRate.divide(PERCENT_SCALE,
4, BigDecimal.ROUND_HALF_UP)), 2,
BigDecimal.ROUND_HALF_UP)).multiply(itemQuantity);
+ // If 1 quantity has 50% discount then itemAmount = 107.5
otherwise 129 (In case of no discount)
+ // Net price for each item
+ // netItemPrice = itemAmount / quantity = 107.5 / 3 =
35.833333333
+ BigDecimal netItemPrice = itemAmount.divide(itemQuantity,
BigDecimal.ROUND_HALF_UP);
+ // Calculate tax on the discounted price, be sure to round
to 2 decimal places before multiplying by quantity
+ // netTax = (netItemPrice - netItemPrice / (1 +
(taxRate/100))) * quantity
+ // netTax = (35.833333333-(35.833333333/(1+(20/100))))*3 =
17.92
+ BigDecimal netTax =
netItemPrice.subtract(netItemPrice.divide(BigDecimal.ONE.add(taxRate.divide(PERCENT_SCALE,
4, BigDecimal.ROUND_HALF_UP)), 2,
BigDecimal.ROUND_HALF_UP)).multiply(itemQuantity);
+ //Subtract net tax from base tax
(taxAmountIncludedFullPrice) to get the negative promotion tax adjustment amount
+ // discountedSalesTax = 17.92 - 21.51 = â3.59 (If no
discounted item quantity then discountedSalesTax will be ZERO)
+ discountedSalesTax =
netTax.subtract(taxAmountIncludedInFullPrice);
+ taxAdjValue.set("amountAlreadyIncluded",
taxAmountIncludedInFullPrice);
taxAdjValue.set("amount", BigDecimal.ZERO);
} else {
- taxAdjValue.set("orderAdjustmentTypeId", "SALES_TAX");
taxAdjValue.set("amount", taxAmount);
}
@@ -482,7 +494,12 @@ public class TaxAuthorityServices {
} else {
Debug.logInfo("NOTE: A tax calculation was done without a
billToPartyId or taxAuthGeoId, so no tax exemptions or tax IDs considered;
billToPartyId=[" + billToPartyId + "] taxAuthGeoId=[" + taxAuthGeoId + "]",
module);
}
-
+ if (discountedSalesTax.compareTo(BigDecimal.ZERO) < 0) {
+ GenericValue taxAdjValueNegative =
delegator.makeValue("OrderAdjustment");
+ taxAdjValueNegative.setFields(taxAdjValue);
+ taxAdjValueNegative.set("amountAlreadyIncluded",
discountedSalesTax);
+ adjustments.add(taxAdjValueNegative);
+ }
adjustments.add(taxAdjValue);
if (productPrice != null && itemQuantity != null &&
