Search Engine: Elastic

Article ID: 130280, created on Feb 13, 2017, last review on Feb 15, 2017


When customers change their country of residence in account settings, their tax zones will not be updated automatically. Whereas in some regions (e.g., Europe) service providers must issue next invoices with the taxes according to a new location. There’s a feature request PBA-62396 for an automatic Tax Zone update, not planned yet.


The solution below explains how to track such changes.

Generally, the following query can be used for getting a full list of customers:

select distinct "a"."AccountID", "a"."CountryID", "a"."TaxZoneID" from "Account" "a" CROSS JOIN "AccountArc" "b" where ("a"."AccountID" = "b"."AccountID" AND "a"."CountryID" <> "b"."CountryID" AND "a"."Type" = 3 AND "a"."AStatus" = 0) order by "AccountID";

But for more convenience, a Provider can build a script that returns the list of users that were changed within 7 days, and receive the updates to his/her email. This can be done as follows:

  1. Place the following file into /root folder on BA database server:

    # cat /root/customersTax.sql
    SELECT DISTINCT "a"."AccountID", "a"."CountryID", "a"."TaxZoneID", to_timestamp("a"."DateArc") AS "DateModified" from "Account" "a" CROSS JOIN "AccountArc" "b" WHERE ("a"."AccountID" = "b"."AccountID" AND "a"."CountryID" <> "b"."CountryID" AND "a"."Type" = 3 AND "a"."AStatus" = 0) AND "a"."DateArc">=(extract('epoch' from now()) - 60*60*24*7) ORDER BY "AccountID";

  2. Create file which will trigger sending the notification to your email address:

    # cat /etc/cron.daily/
    cat /root/customersTax.sql | psql -Upba pba | mail -s CusotmerTaxZone
    where is an email where the notification will be sent to.

  3. Make script executable:
    chmod +x /etc/cron.daily/

Email subscription for changes to this article
Save as PDF