Raw SQL from the "Customer Account Balance.jrxml" report.

Trying to extract the raw SQL from the above report so I can make my own report, but when I open this "Customer Account Balance.jrxml" file, with either iReport or just Notepad++, I don't see any SQL statements, is this report baked into the application somehow? It has it's own section(OpenVPMS > Reporting > Debtors), instead of just being in the list under: OpenVPMS > Reporting > Reports.

We are on v1.9.1

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Raw SQL from the "Customer Account Balance.jrxml" report.

The Customer Account Balance report uses data supplied by the application rather than SQL.

The SQL would look something like:

select a.act_id,
       a.activity_start_time as start_time,
       a.status,
       f.total as amount,
       f.allocated_amount as allocated_amount,
       f.total - f.allocated_amount as unallocated_amount,
       a.arch_short_name as archetype,
       a.name as transaction_name,
       f.credit as credit,
       customer.name as customer,
       customer.entity_id as customer_id,
       account_type.code as account_type_code,
       account_type.name as account_type_name,
       payment_terms.value as payment_terms,
       payment_terms_units.value as payment_terms_units,
       case
            when payment_terms_units.value = "MONTHS" then subdate(now(), interval payment_terms.value MONTH) # replace now() with appropriate date
            when payment_terms_units.value = "WEEKS" then subdate(now(), interval payment_terms.value WEEK) # replace now() with appropriate date
            when payment_terms_units.value = "DAYS" then subdate(now(), interval payment_terms.value DAY) # replace now() with appropriate date
       end as overdue_date
from acts a
join financial_acts f
    on a.act_id = f.financial_act_id
join participations pbalance
    on pbalance.act_id = a.act_id
        and pbalance.arch_short_name = "participation.customerAccountBalance"
join entities customer
    on pbalance.entity_id = customer.entity_id        
left join entity_classifications ec
    on ec.entity_id = customer.entity_id
left join lookups account_type
    on ec.lookup_id = account_type.lookup_id
        and account_type.arch_short_name = "lookup.customerAccountType"
left join lookup_details payment_terms
    on account_type.lookup_id = payment_terms.lookup_id
    and payment_terms.name = 'paymentTerms'
left join lookup_details payment_terms_units
    on account_type.lookup_id = payment_terms_units.lookup_id
    and payment_terms_units.name = 'paymentUom'
where a.arch_short_name in (
        "act.customerAccountChargesCounter",
        "act.customerAccountChargesCredit",
        "act.customerAccountChargesInvoice",
        "act.customerAccountBadDebt",
        "act.customerAccountCreditAdjust",
        "act.customerAccountDebitAdjust",
        "act.customerAccountInitialBalance",
        "act.customerAccountPayment",
        "act.customerAccountRefund")
    and a.status in ("COMPLETED", "POSTED")        
    and a.activity_start_time <= "2018-08-29" # replace with appropriate date
order by
     a.activity_start_time asc

Calculation is done internally to determine the overdue and credit and unbilled balances. For reference, this is done in the org.openvpms.archetype.rules.finance.account.CustomerBalanceSummaryQuery class. It could be replicated in SQL.

 

 

Re: Raw SQL from the "Customer Account Balance.jrxml" report.

Exactly the answer I'm after - really appreciate it.

 

Syndicate content