To all Invantive’s coders:
we started to use Cursor AI to create our Invantive scripts.
It works, it is not 100% fluent in PL/SQL-Invantive grammar but somehow does the job pretty close.
Out of curiousity: is it possible to give a sample of resulting PSQL?
Below an example:
It requires always a bit of interaction, because Invantive’s syntax is not in the LLM.
--
-- Script to fetch Fortnox voucher lines using temporary tables.
-- This approach avoids using the view due to performance issues.
--
-- Following Invantive Fortnox Data Model: https://documentation.invantive.com/2017R2/fortnox-data-model/webhelp/index.html
-- Following Invantive Data Dictionary: https://documentation.invantive.com/2017R2/data-dictionary-data-model/webhelp/index.html
-- =================================================================
--
-- Get all fiscal years.
--
create or replace table fiscal_years@inmemorystorage
as select *
from fortnox.financialyears.financialyears@fortnox
;
--
-- Step 1: Get all vouchers from the last year.
--
create or replace table vouchers@inmemorystorage
as
select *
from fortnox.vouchers.vouchers@fortnox
;
--
-- Step 2: Create empty voucher lines table with same structure.
--
create or replace table voucher_lines@inmemorystorage
as
select * from fortnox.vouchers.VoucherLinesByVoucherSeriesAndVoucherNumber@fortnox('A', '1', 2024, null, null, null, null) limit 0
;
--
-- Step 3: Fill voucher lines using a loop with dynamic SQL.
--
declare
v_sql varchar2;
begin
for v in (select VoucherNumber, VoucherSeries, Year from vouchers@inmemorystorage)
loop
v_sql := 'insert into voucher_lines@inmemorystorage (
rowid$, Account, ApprovalState, c_url, Comments, CostCenter_2, CostCenter,
Credit, Debit, Description_2, Description, Project_2, Project,
Quantity, ReferenceNumber, ReferenceType, Removed, TransactionDate,
TransactionInformation, VoucherNumber, VoucherSeries, Year
)
select
null as rowid$,
vl.Account, vl.ApprovalState, vl.c_url, vl.Comments, vl.CostCenter_2, vl.CostCenter,
vl.Credit, vl.Debit, vl.Description_2, vl.Description, vl.Project_2, vl.Project,
vl.Quantity, vl.ReferenceNumber, vl.ReferenceType, vl.Removed, vl.TransactionDate,
vl.TransactionInformation, vl.VoucherNumber, vl.VoucherSeries, vl.Year
from fortnox.vouchers.VoucherLinesByVoucherSeriesAndVoucherNumber@fortnox(''' ||
v.VoucherSeries || ''', ''' ||
v.VoucherNumber || ''', ' ||
v.Year || ', null, null, null, null) vl';
execute immediate v_sql;
end loop;
end;
select * from voucher_lines@inmemorystorage
--
-- Step 4: Join vouchers and voucher lines with all fields.
--
select
-- Voucher fields
v.* except rowid$,
-- Voucher line fields
vl.* except rowid$,
-- Fiscal year fields
fy.* except rowid$
from vouchers@inmemorystorage v
join voucher_lines@inmemorystorage vl
on v.VoucherNumber = vl.VoucherNumber
and v.VoucherSeries = vl.VoucherSeries
and v.Year = vl.Year
join fiscal_years@inmemorystorage fy
on fy.Id = v.Year
order by
v.TransactionDate,
v.VoucherNumber,
vl.Account;
1 like
Great illustration! A hunch of the original database platform follows from the naming convention, but this is indeed great solution!
As a side-note, since not related to Cursor / AI: Invantive is trying to interprete the Swedish SIE-format available as an API call, which is an awful lot faster than using the APIs itself to extract the transactions.
Another example. generating this reporting by email took 6 prompts and 2 minutes in total
now write a report by email using @https://forums.invantive.com/t/how-to-send-html-table-by-email-from-a-select/1300 for the grammar
(…)
Adjusting a few things including error correction by copy-paste errors shown by Invantive Query Tool
at the top part of the email, add a synthesys reporting grouping by site, counting the number of devices offline / online, oldest offline and soonest offline
(…)
Here it is:
-- Send email report of outdated devices
declare
l_email_table varchar2;
l_outdated_count int;
begin
-- Count outdated devices
select count(*) into l_outdated_count
from results_last@inmemorystorage
where last_update_utc is not null
and last_update_utc < (sysdate - 2);
-- Only send email if there are outdated devices
if l_outdated_count > 0 then
-- Configure SMTP settings
set smtp-host-address@mail "******";
set smtp-minimum-deliver-duration-ms@mail 5000;
set smtp-user-name@mail "*****";
set smtp-password@mail "*****";
set [mail-from-name]@mail "*****";
set [mail-from-email]@mail "*****";
set [mail-body-html]@mail false;
set smtp-enable-ssl@mail true;
set mail-priority@mail -1;
set smtp-minimum-deliver-duration-ms@mail 1000;
set mail-body-html@mail true;
set mail-priority@mail 0;
-- Prepare email content (HTML table with outdated devices)
l_email_table := '<table border="1" style="border-collapse: collapse; width: 100%;"><tr style="background-color: #f2f2f2;"><th style="padding: 8px; border: 1px solid #ddd;">Sensor ID</th><th style="padding: 8px; border: 1px solid #ddd;">Channel</th><th style="padding: 8px; border: 1px solid #ddd;">Site</th><th style="padding: 8px; border: 1px solid #ddd;">Location</th><th style="padding: 8px; border: 1px solid #ddd;">Last Update</th><th style="padding: 8px; border: 1px solid #ddd;">Days Old</th><th style="padding: 8px; border: 1px solid #ddd;">Status</th></tr>';
for r in (select
sensor_id,
channel,
site,
location1 || case when location2 is not null then ' - ' || location2 else '' end ||
case when location3 is not null then ' - ' || location3 else '' end as location,
last_update_utc,
round((sysdate - last_update_utc), 2) as days_old,
result
from results_last@inmemorystorage
where last_update_utc is not null
and last_update_utc < (sysdate - 2)
order by last_update_utc desc)
loop
l_email_table := l_email_table
|| '<tr><td style="padding: 8px; border: 1px solid #ddd;">' || r.sensor_id
|| '</td><td style="padding: 8px; border: 1px solid #ddd;">' || r.channel
|| '</td><td style="padding: 8px; border: 1px solid #ddd;">' || r.site
|| '</td><td style="padding: 8px; border: 1px solid #ddd;">' || r.location
|| '</td><td style="padding: 8px; border: 1px solid #ddd;">' || r.last_update_utc
|| '</td><td style="padding: 8px; border: 1px solid #ddd; text-align: right;">' || r.days_old
|| '</td><td style="padding: 8px; border: 1px solid #ddd;">' || r.result
|| '</td></tr>';
end loop;
l_email_table := l_email_table || '</table>';
-- Send email
insert into smtp@mail
( toEmail
, ccEmail
, bccEmail
, subject
, body
, attachment1Contents
, attachment1Name
, attachment1MimeType
)
select 'my_email@my_domain'
toEmail
, cast(null as varchar2)
ccEmail
, cast(null as varchar2)
bccEmail
, 'Meter Alert: ' || l_outdated_count || ' devices with outdated data'
subject
, '<h2>Meter Monitoring Report</h2>'
|| '<p><strong>Alert:</strong> ' || l_outdated_count || ' devices have data older than 48 hours.</p>'
|| '<p><strong>Report generated:</strong> ' || sysdate || '</p>'
|| '<br>'
|| '<h3>Site Summary:</h3>'
|| '<table border="1" style="border-collapse: collapse; width: 100%; margin-bottom: 20px;"><tr style="background-color: #f2f2f2;"><th style="padding: 8px; border: 1px solid #ddd;">Site</th><th style="padding: 8px; border: 1px solid #ddd;">Total Devices</th><th style="padding: 8px; border: 1px solid #ddd;">Online</th><th style="padding: 8px; border: 1px solid #ddd;">Offline</th><th style="padding: 8px; border: 1px solid #ddd;">Oldest Offline (days)</th><th style="padding: 8px; border: 1px solid #ddd;">Newest Offline (days)</th></tr>'
|| (select listagg('<tr><td style="padding: 8px; border: 1px solid #ddd;">' || site || '</td><td style="padding: 8px; border: 1px solid #ddd; text-align: center;">' || total_devices || '</td><td style="padding: 8px; border: 1px solid #ddd; text-align: center;">' || online_count || '</td><td style="padding: 8px; border: 1px solid #ddd; text-align: center;">' || offline_count || '</td><td style="padding: 8px; border: 1px solid #ddd; text-align: center;">' || oldest_offline || '</td><td style="padding: 8px; border: 1px solid #ddd; text-align: center;">' || newest_offline || '</td></tr>', '') within group (order by site)
from (select
site,
count(*) as total_devices,
sum(case when last_update_utc >= (sysdate - 2) then 1 else 0 end) as online_count,
sum(case when last_update_utc < (sysdate - 2) then 1 else 0 end) as offline_count,
max(case when last_update_utc < (sysdate - 2) then round((sysdate - last_update_utc), 2) else 0 end) as oldest_offline,
min(case when last_update_utc < (sysdate - 2) then round((sysdate - last_update_utc), 2) else 999 end) as newest_offline
from results_last@inmemorystorage
where last_update_utc is not null
group by site))
|| '</table>'
|| '<h3>Outdated Devices:</h3>'
|| l_email_table
|| '<br>'
|| '<p><em>This is an automated report from the meter monitoring system.</em></p>'
body
, null
attachment1Contents
, null
attachment1Name
, null
attachment1MimeType;
dbms_output.put_line('Email report sent for ' || l_outdated_count || ' outdated devices.');
else
dbms_output.put_line('No outdated devices found. No email report sent.');
end if;
end;
1 like
It is really amazing (and quite creepy) what AI can do and how it has grown in capabilities. At Invantive we don’t use it for coding itself, but it completes Google and creating raw drafts of texts and analyses. For instance, it is quite good at analyzing large volumes of texts and seems capable to extract the voice of tone as well as people that were involved during the whole process, but solely from the documents.