How to add payment conditions to AR outstanding items in a report on Exact Online?

Question

I am using Invantive Control to create an Excel report with some outstanding invoices information from Exact Online.

I have created a model with the block designer and I have the outstanding invoices information I need. Now I also want to know the payment condition of the account which is in debt, but there is no information on the payment condition on the AROutstandingItems table.

This is the query I have so far:

select division_code
,      division_name
,      number_attr
,      outstandingitems_ar_account_code_attr
,      outstandingitems_ar_account_name
,      description
,      invoicedate
,      duedate
,      currency_code_attr
,      invoiceamtfc
,      outstandingamtfc
,      invoiceamtdc
,      outstandingamtdc 
from   aroutstandingitems 
order 
by     division_code
,      outstandingamtdc desc

How can I add the payment condition to my report?

You can use joins in invantive-sql. So join it with the table the has the payment condition information and display it accordingly. — Ravenix, Dec 13 '16 at 12:22
You can also use a join indeed on ExactOnlineREST..salesinvoices. However, depending on the volume of rows in salesinvoices and the presence of the REST API IN... optimization, it will be extremely slow or fast. The salesinvoices API does not excel in performance yet :-) — Guido Leenders, Dec 13 '16 at 13:02

score 3 · Answer 1 · edited Jan 11 '18 at 15:56

Payment conditions are referenced from the account on the outstanding items. In order to get the (sales) payment conditions on an account, there are several options.

Join the Accounts table and get the payment condition from there (from the field salespaymentcondition_code_attr and salespaymentcondition_description).

The SQL would look like this then:

select ...
,      act.salespaymentcondition_code_attr
from   aroutstandingitems aom
join   exactonlinexml..accounts act
on     aom.outstandingitems_ar_account_code_attr = act.code_attr

Use an Excel function to get the payment condition: I_EOL_ACT_SLS_PAY_CODE.

The formula has two parameters: division_code and account_code_attr. The first is optional.

A valid call to the formula would thus be: =I_EOL_ACT_SLS_PAY_CODE(,"22") for the payment condition code for account with code 22 in the current Exact Online company. You can incorporate that in your SQL like this:
```
select ...
,      '=I_EOL_ACT_SLS_PAY_CODE("' + division_code + '", "' + outstandingitems_ar_account_code_attr + '")'
       pcn_code
from   aroutstandingitems
```
That would result in your model on sync receive the formula for retrieving the payment condition code. Remember to check the check box 'Formula' to ensure that the SQL outcome is treated as an Excel formula.
The same as above, but then using column expressions:
```
select ...
,      '=I_EOL_ACT_SLS_PAY_CODE("$C{E,.,.,^,.}";"$C{E,.,.,^+3,.}")'
       pcn_code
from   aroutstandingitems
```
Remember to check the check boxes 'Formula' and 'Column expression' to ensure that the SQL outcome is treated as an Excel formula with $C column expressions.

The recommended option is to use column expressions, since these work across the widest range of deployment scenarios such as accountancy with hundreds of companies and the formulas are upgrade safe. SQL statements may need to be adapted with new releases of the Exact Online data model of Invantive.

How to add payment conditions to AR outstanding items in a report on Exact Online?

1 Answers1