• From: EU-UK Opera Proxy, Dubai, UAE
  • Registered: 02/23/2018
  • Posts: 5

Topic: Add sales area in Tax report

I would like to filter the tax report by Area wise. The Area field is already there in the customer /branch. May I know how to pull it into tax report.

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Add sales area in Tax report

The Tax Report is in reporting/rep709.php.
Current Report Layout that provides the Detailed and Summarised versions is attached.
The function getTaxTransactions in the said file is the one that provides the line items in the report.

The following is the $sql example that comes about for a company prefix of 0_:

SELECT 
  tt.name as taxname, 
  taxrec.*, 
  taxrec.amount*ex_rate AS amount, 
  taxrec.net_amount*ex_rate AS net_amount, 
  IF(taxrec.trans_type=1 OR taxrec.trans_type=2, 
    IF(gl.person_type_id<>0, gl.memo_, gl.person_id), 
    IF(ISNULL(supp.supp_name), debt.name, supp.supp_name)
  ) as name, 
  branch.br_name 
FROM 0_trans_tax_details taxrec 
  LEFT JOIN 0_tax_types tt ON taxrec.tax_type_id=tt.id 
  LEFT JOIN 0_gl_trans gl ON taxrec.trans_type=gl.type 
    AND taxrec.trans_no=gl.type_no 
    AND gl.amount<>0 
    AND (tt.purchasing_gl_code=gl.account OR tt.sales_gl_code=gl.account) 
  LEFT JOIN 0_supp_trans strans ON taxrec.trans_no=strans.trans_no 
    AND taxrec.trans_type=strans.type 
  LEFT JOIN 0_suppliers as supp ON strans.supplier_id=supp.supplier_id 
  LEFT JOIN 0_debtor_trans dtrans ON taxrec.trans_no=dtrans.trans_no 
    AND taxrec.trans_type=dtrans.type 
  LEFT JOIN 0_debtors_master as debt ON dtrans.debtor_no=debt.debtor_no 
  LEFT JOIN 0_cust_branch as branch ON dtrans.branch_code=branch.branch_code 
WHERE (taxrec.amount <> 0 OR taxrec.net_amount <> 0) 
  AND !ISNULL(taxrec.reg_type) 
  AND taxrec.tran_date >= '2015-03-01' 
  AND taxrec.tran_date <= '2018-03-31' 
ORDER BY 
  taxrec.trans_type, 
  taxrec.tran_date, 
  taxrec.trans_no,
  taxrec.ex_rate;

An example output is:

taxname    id    trans_type    trans_no    tran_date    tax_type_id    rate    ex_rate    included_in_price    net_amount    amount    memo    reg_type    amount    net_amount    name    br_name
Tax    5    10    2    2017-05-07    1    5    1    1    285.71    14.29    002/2017    0    14.29    285.71    Donald Easter LLC    Donald Easter LLC
\N    7    10    3    2017-05-07    0    0    1.123    1    267.14    0    003/2017    0    0    299.99822    MoneyMaker Ltd.    MoneyMaker Ltd.
Tax    2    10    1    2017-05-10    1    5    1    1    5942.86    297.14    001/2017    0    297.14    5942.86    Donald Easter LLC    Donald Easter LLC
Tax    10    10    5    2018-02-18    1    5    1    1    249.53    12.48    001/2018    0    12.48    249.53    Donald Easter LLC    Donald Easter LLC
Tax    3    20    1    2017-05-05    1    5    1    0    3000    150    rr4    1    150    3000    Dino Saurius Inc.    \N
Tax    8    20    2    2018-01-22    1    5    1.3895    0    2620    131    BE075    1    182.0245    3640.49    Beefeater Ltd.    \N

There is no Sales Area in the above output. If we add the branch.area to the select list by making line 41

                branch.br_name

to be:

                branch.br_name, branch.area

we can get that field. This will need to be added in to the header part when the new area argument is included.

@joe: nice suggestion - can we add it in?

Post's attachments

Rep709.pdf 3.5 kb, 10 downloads since 2018-04-07 

You don't have the permssions to download the attachments of this post.

apmuthu's Website

  • From: EU-UK Opera Proxy, Dubai, UAE
  • Registered: 02/23/2018
  • Posts: 5

Re: Add sales area in Tax report

Thanks for the prompt reply. I had some time yesterday to research on this subject and I used the below steps. The below would help for those who require region wise information in their tax report. It would be appreciated if this feature is added in the main FA program.

1. Add Line 41 branch.br_name, areas.description
2. Add Line 55 LEFT JOIN ".TB_PREF."areas areas ON areas.area_code = branch.area
3. Add Line 120 (inside the bracket) , _('Area') // this is the header
4. Add Line 121 (inside the bracket) ,'lift'
5. Add Line 158 $rep->TextCol(10,11, $trans['description']);

This report looks only good in landscape mode. Table does not fit in portrait mode.

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,085

Re: Add sales area in Tax report

Hellog Guys,

I guess this is a strong candidate for an Extension. Not many countries has this legal demand.

/Joe

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,089

Re: Add sales area in Tax report

@mmohamedyaser: post your changed files and let us make it an extension.

apmuthu's Website

  • From: EU-UK Opera Proxy, Dubai, UAE
  • Registered: 02/23/2018
  • Posts: 5

Re: Add sales area in Tax report

Thanks for your advice. Please find attached the changed file for the extension.

https://app.box.com/s/ffgu7oo2qptid3ztxu0yf4ri0uykimk7