Tax calculation on line item - Quote Sugar 7.9

I needed line item tax as well, I called the field line_tax_amount_c. In my case this is not a percentage but an amount, I have an API which retrieves the correct amount based on tax tables on our ERP system and returns it to the Quote. 

I add up the line item tax at ProductBundle level:

custom/Extension/modules/ProductBundles/Ext/Vardefs/vardef.ext.php

<?php
$dictionary['ProductBundle']['fields']['tax_amount_c']['labelValue']='Tax Amount';
$dictionary['ProductBundle']['fields']['tax_amount_c']['calculated']='true';
$dictionary['ProductBundle']['fields']['tax_amount_c']['formula']='rollupCurrencySum($products, "line_tax_amount_c")';
$dictionary['ProductBundle']['fields']['tax_amount_c']['enforced']='true';
$dictionary['ProductBundle']['fields']['tax_amount_c']['dependency']='';
$dictionary['ProductBundle']['fields']['tax_amount_c']['related_fields']=array (
  0 => 'currency_id',
  1 => 'base_rate',
);

Then I add the Tax to the Bundle Total:

custom/Extension/modules/ProductBundles/Ext/Vardefs/sugarfield_total.php

<?php
$dictionary['ProductBundle']['fields']['total']['formula']='currencyAdd($new_sub, $tax_amount_c)';
$dictionary['ProductBundle']['fields']['total']['related_fields']=array (
  0 => 'currency_id',
  1 => 'base_rate',
  2 => 'new_sub',
  3 => 'tax_amount_c',
);
$dictionary['ProductBundle']['fields']['total']['calculated']='true';
$dictionary['ProductBundle']['fields']['total']['enforced']='true';

I then add the bundle tax for the total Tax at Quote level:

custom/Extension/modules/Quotes/Ext/Vardefs/sugarfield_tax_amount_c.php

<?php
// created: 2017-06-13 11:14:46
$dictionary['Quote']['fields']['tax_amount_c']['duplicate_merge_dom_value']=0;
$dictionary['Quote']['fields']['tax_amount_c']['labelValue']='Tax';
$dictionary['Quote']['fields']['tax_amount_c']['calculated']='true';
$dictionary['Quote']['fields']['tax_amount_c']['formula']='rollupCurrencySum($product_bundles,"tax_amount_c")';
$dictionary['Quote']['fields']['tax_amount_c']['enforced']='true';
$dictionary['Quote']['fields']['tax_amount_c']['dependency']='';
$dictionary['Quote']['fields']['tax_amount_c']['related_fields']=array (
  0 => 'currency_id',
  1 => 'base_rate',
);

The Quote total includes all the tax as it is because it's just a rollup total of the individual bundle totals, which include the line item tax per the customization above.

HTH

FrancescaS

HiFrancesca Shiekh,

I will try it.

Thanks

Asif

Offshore Evolution

Offshore Evolution, could you please share the code you used for creating the dropdown on the line?

Is it an enum? Or a relate field to the TaxRates class? I've been trying relate fields, enums, as well as attempting to mimic the product template lookup, but I'm struggling to get this to work as you've done, and I have the exact same requirement. If you had the code to hand, I'd really appreciate a point in the right direction.

He Richard Coleman,

I have not checked and I have not get resolved my issue. If you will find, then you will inform me.

Thanks

Asif

Hi Offshore Evolution,

Its not that difficult as you think. All we need to do is to place it in the right file.Francesca Shiekh comment above is exactly how u need to do it. But one additional config.

CUSTOM FIELD

custom/Extension/modules/Products/Ext/Vardefs/sugarfield_line_tax_amount_c.php

$dictionary['Product']['fields']['line_tax_amount_c']['duplicate_merge_dom_value']=0;
$dictionary['Product']['fields']['line_tax_amount_c']['labelValue']='Tax Amount';
$dictionary['Product']['fields']['line_tax_amount_c']['calculated']='true';
$dictionary['Product']['fields']['line_tax_amount_c']['formula']='multiply($total_amount,divide($line_tax_percent_c,100))';
$dictionary['Product']['fields']['line_tax_amount_c']['enforced']='true';
$dictionary['Product']['fields']['line_tax_amount_c']['dependency']='';
$dictionary['Product']['fields']['line_tax_amount_c']['related_fields']=array (
  0 => 'currency_id',
  1 => 'base_rate',
);

and in quote-data-group-list.php

               array(
                  'name'=>'line_tax_percent_c',
                  'widthClass' => 'cell-small',
                  'label'=>'LBL_LINE_TAX_PERCENT_C',

                ),
                array(
                  'name'=>'line_tax_amount_c',
                  'label'=>'LBL_LINE_TAX_AMOUNT_C',
                  'type' => 'currency',
                  'widthClass' => 'cell-small',
                  'showTransactionalAmount' => true,
                  'related_fields' => array(
                    'total_amount',
                    'currency_id',
                    'base_rate',
                  ),
                ),

The additional part the Key is to add the custom field in Quotes record view 

product_bundle_items

/custom/modules/Quotes/clients/base/views/record/record.php

array(
         'name' => 'product_bundle_items',
         'fields' => array(
                           'name',
                          'quote_id',
                          'description',
                          'quantity',
                          'product_template_name',
                          'product_template_id',
                          'deal_calc',
                          'mft_part_num',
                          'discount_price',
                          'discount_amount',
                          'tax',
                          'tax_class',
                          'subtotal',
                          'position',
                          'currency_id',
                          'base_rate',
                          'discount_select',
                          'line_tax_amount_c',
                          'custom_product_field_c',
                 ),
         'max_num' => -1,
),

then Repair and rebuild 

You are right! Thank you Aravind Kumar

FrancescaS

Thank you Francesca Shiekh, Hope Offshore Evolution confirm its working.

Hello Francesca Shiekh,

Thanks for Francesca Shiekh and Aravind Kumar.

Line item level tax is working for me.

How can I calculate Group level total tax add in Group total ?

How can I calculate total tax add in Quote Grand total ?

I added a calculated field for the bundle tax total:

custom/Extension/modules/ProductBundles/fields/tax_amount_c.php

<?php
$dictionary['ProductBundle']['fields']['tax_amount_c']['labelValue']='Tax Amount';
$dictionary['ProductBundle']['fields']['tax_amount_c']['calculated']='true';
$dictionary['ProductBundle']['fields']['tax_amount_c']['formula']='rollupCurrencySum($products, "line_tax_amount_c")';
$dictionary['ProductBundle']['fields']['tax_amount_c']['enforced']='true';
$dictionary['ProductBundle']['fields']['tax_amount_c']['dependency']='';
$dictionary['ProductBundle']['fields']['tax_amount_c']['related_fields']=array (
  0 => 'currency_id',
  1 => 'base_rate',
);

and one for the Quote tax total

<?php
// created: 2017-06-13 11:14:46
$dictionary['Quote']['fields']['tax_amount_c']['duplicate_merge_dom_value']=0;
$dictionary['Quote']['fields']['tax_amount_c']['labelValue']='Tax';
$dictionary['Quote']['fields']['tax_amount_c']['calculated']='true';
$dictionary['Quote']['fields']['tax_amount_c']['formula']='rollupCurrencySum($product_bundles,"tax_amount_c")';
$dictionary['Quote']['fields']['tax_amount_c']['enforced']='true';
$dictionary['Quote']['fields']['tax_amount_c']['dependency']='';
$dictionary['Quote']['fields']['tax_amount_c']['related_fields']=array (
  0 => 'currency_id',
  1 => 'base_rate',
);

?>

Note that they happen to be named the same but are separate fields, one in ProductBundles, the other in Quotes.

And the ProductBundle total:

custom/Extension/modules/ProductBundles/Ext/Vardefs/sugarfield_total.php

$dictionary['ProductBundle']['fields']['total']['formula']='currencyAdd($new_sub, $tax_amount_c)';
$dictionary['ProductBundle']['fields']['total']['related_fields']=array (
  0 => 'currency_id',
  1 => 'base_rate',
  2 => 'new_sub',
  3 => 'tax_amount_c',
);
$dictionary['ProductBundle']['fields']['total']['calculated']='true';
$dictionary['ProductBundle']['fields']['total']['enforced']='true';

The Quote total is a rollup of ProductBundle total so it does not need changing.

This is not the only way, you could redefine the calculations for the existing tax fields, but I found it easier to create my own tax fields.

You will need to edit your views to include your new calculated fields:

custom/modules/ProductBundles/clients/base/views/quote-data-group-footer/quote-data-group-footer.php

and 

custom/modules/Quotes/clients/base/views/quote-data-grand-totals-footer/quote-data-grand-totals-footer.php

and

custom/modules/Quotes/clients/base/views/quote-data-grand-totals-header/quote-data-grand-totals-header.php

I hope I didn't miss anything.

FrancescaS

Hi Francesca Shiekh,

It is working fine. Thank you very much for help.

Thanks

Asif