Symptoms

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.

Resolution

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/custtax.sh
    #!/bin/bash
    cat /root/customersTax.sql | psql -Upba pba | mail -s CusotmerTaxZone admin@service.pro
    where admin@service.pro is an email where the notification will be sent to.

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

Internal content