PART2
-- ========================================================================
-- STEP 3: MODIFICATION
-- ========================================================================
-- Apply modifications only if verification passed (Diff After = 0)
-- If Diff After <> 0, data may have changed in EOL during API call,
-- skip modification to avoid inconsistencies (script will re-run at next scheduled time)
-- ========================================================================
if l_total_diff_after = 0 then
-- Verification passed: proceed with modifications
l_debug := 'now entering modification processing (all divisions at once) - ' || l_mode || ' mode';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
-- Initialize counters
l_countinserted := 0;
l_countdeleted := 0;
-- Execute MERGE and DELETE in a single transaction with BEGIN TRY
-- MERGE handles both INSERT and UPDATE operations
-- DELETE removes records marked for deletion
l_debug := 'executing MERGE and DELETE statements in transaction';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
if l_mode = 'PROD' then
-- PROD mode: Execute MERGE and DELETE, then COMMIT
for r_result in (
execute native 'BEGIN TRY
BEGIN TRANSACTION;
DECLARE @rows_affected_merge INT;
DECLARE @rows_deleted INT;
MERGE target_transaction_lines_table AS target
USING staging_insertupdate AS source
ON target.ID = source.ID
WHEN MATCHED THEN
UPDATE SET
Account = source.Account, AmountDC = source.AmountDC, AmountFC = source.AmountFC, AmountVATBaseFC = source.AmountVATBaseFC, AmountVATFC = source.AmountVATFC,
Asset = source.Asset, CostCenter = source.CostCenter, CostUnit = source.CostUnit, Created = source.Created, Creator = source.Creator,
Currency = source.Currency, Date = source.Date, Description = source.Description, Division = source.Division, Document = source.Document,
DueDate = source.DueDate, EntryID = source.EntryID, EntryNumber = source.EntryNumber, ExchangeRate = source.ExchangeRate,
ExtraDutyAmountFC = source.ExtraDutyAmountFC, ExtraDutyPercentage = source.ExtraDutyPercentage, FinancialPeriod = source.FinancialPeriod,
FinancialYear = source.FinancialYear, GLAccount = source.GLAccount, InvoiceNumber = source.InvoiceNumber, Item = source.Item,
JournalCode = source.JournalCode, LineNumber = source.LineNumber, LineType = source.LineType, Modified = source.Modified,
Modifier = source.Modifier, Notes = source.Notes, OffsetID = source.OffsetID, OrderNumber = source.OrderNumber,
PaymentDiscountAmount = source.PaymentDiscountAmount, PaymentReference = source.PaymentReference, Project = source.Project,
Quantity = source.Quantity, Status = source.Status, Subscription = source.Subscription, Timestamp = source.Timestamp,
TrackingNumber = source.TrackingNumber, Type = source.Type, VATCode = source.VATCode, VATPercentage = source.VATPercentage,
VATType = source.VATType, YourRef = source.YourRef
WHEN NOT MATCHED BY TARGET THEN
INSERT (Account, AmountDC, AmountFC, AmountVATBaseFC, AmountVATFC, Asset, CostCenter, CostUnit, Created, Creator, Currency, Date, Description, Division, Document, DueDate, EntryID, EntryNumber, ExchangeRate, ExtraDutyAmountFC, ExtraDutyPercentage, FinancialPeriod, FinancialYear, GLAccount, ID, InvoiceNumber, Item, JournalCode, LineNumber, LineType, Modified, Modifier, Notes, OffsetID, OrderNumber, PaymentDiscountAmount, PaymentReference, Project, Quantity, Status, Subscription, Timestamp, TrackingNumber, Type, VATCode, VATPercentage, VATType, YourRef)
VALUES (source.Account, source.AmountDC, source.AmountFC, source.AmountVATBaseFC, source.AmountVATFC, source.Asset, source.CostCenter, source.CostUnit, source.Created, source.Creator, source.Currency, source.Date, source.Description, source.Division, source.Document, source.DueDate, source.EntryID, source.EntryNumber, source.ExchangeRate, source.ExtraDutyAmountFC, source.ExtraDutyPercentage, source.FinancialPeriod, source.FinancialYear, source.GLAccount, source.ID, source.InvoiceNumber, source.Item, source.JournalCode, source.LineNumber, source.LineType, source.Modified, source.Modifier, source.Notes, source.OffsetID, source.OrderNumber, source.PaymentDiscountAmount, source.PaymentReference, source.Project, source.Quantity, source.Status, source.Subscription, source.Timestamp, source.TrackingNumber, source.Type, source.VATCode, source.VATPercentage, source.VATType, source.YourRef);
SET @rows_affected_merge = @@ROWCOUNT;
DELETE t FROM target_transaction_lines_table t
INNER JOIN staging_delete d ON t.ID = d.EntityKey;
SET @rows_deleted = @@ROWCOUNT;
COMMIT TRANSACTION;
SELECT @rows_affected_merge as rows_affected, @rows_deleted as rows_deleted;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW;
END CATCH' datacontainer 'your_sql_server_datacontainer'
)
loop
l_countinserted := r_result.rows_affected;
l_countdeleted := r_result.rows_deleted;
end loop;
else
-- TEST mode: Execute MERGE and DELETE, then ROLLBACK (no modifications persisted)
for r_result in (
execute native 'BEGIN TRY
BEGIN TRANSACTION;
DECLARE @rows_affected_merge INT;
DECLARE @rows_deleted INT;
MERGE target_transaction_lines_table AS target
USING staging_insertupdate AS source
ON target.ID = source.ID
WHEN MATCHED THEN
UPDATE SET
Account = source.Account, AmountDC = source.AmountDC, AmountFC = source.AmountFC, AmountVATBaseFC = source.AmountVATBaseFC, AmountVATFC = source.AmountVATFC,
Asset = source.Asset, CostCenter = source.CostCenter, CostUnit = source.CostUnit, Created = source.Created, Creator = source.Creator,
Currency = source.Currency, Date = source.Date, Description = source.Description, Division = source.Division, Document = source.Document,
DueDate = source.DueDate, EntryID = source.EntryID, EntryNumber = source.EntryNumber, ExchangeRate = source.ExchangeRate,
ExtraDutyAmountFC = source.ExtraDutyAmountFC, ExtraDutyPercentage = source.ExtraDutyPercentage, FinancialPeriod = source.FinancialPeriod,
FinancialYear = source.FinancialYear, GLAccount = source.GLAccount, InvoiceNumber = source.InvoiceNumber, Item = source.Item,
JournalCode = source.JournalCode, LineNumber = source.LineNumber, LineType = source.LineType, Modified = source.Modified,
Modifier = source.Modifier, Notes = source.Notes, OffsetID = source.OffsetID, OrderNumber = source.OrderNumber,
PaymentDiscountAmount = source.PaymentDiscountAmount, PaymentReference = source.PaymentReference, Project = source.Project,
Quantity = source.Quantity, Status = source.Status, Subscription = source.Subscription, Timestamp = source.Timestamp,
TrackingNumber = source.TrackingNumber, Type = source.Type, VATCode = source.VATCode, VATPercentage = source.VATPercentage,
VATType = source.VATType, YourRef = source.YourRef
WHEN NOT MATCHED BY TARGET THEN
INSERT (Account, AmountDC, AmountFC, AmountVATBaseFC, AmountVATFC, Asset, CostCenter, CostUnit, Created, Creator, Currency, Date, Description, Division, Document, DueDate, EntryID, EntryNumber, ExchangeRate, ExtraDutyAmountFC, ExtraDutyPercentage, FinancialPeriod, FinancialYear, GLAccount, ID, InvoiceNumber, Item, JournalCode, LineNumber, LineType, Modified, Modifier, Notes, OffsetID, OrderNumber, PaymentDiscountAmount, PaymentReference, Project, Quantity, Status, Subscription, Timestamp, TrackingNumber, Type, VATCode, VATPercentage, VATType, YourRef)
VALUES (source.Account, source.AmountDC, source.AmountFC, source.AmountVATBaseFC, source.AmountVATFC, source.Asset, source.CostCenter, source.CostUnit, source.Created, source.Creator, source.Currency, source.Date, source.Description, source.Division, source.Document, source.DueDate, source.EntryID, source.EntryNumber, source.ExchangeRate, source.ExtraDutyAmountFC, source.ExtraDutyPercentage, source.FinancialPeriod, source.FinancialYear, source.GLAccount, source.ID, source.InvoiceNumber, source.Item, source.JournalCode, source.LineNumber, source.LineType, source.Modified, source.Modifier, source.Notes, source.OffsetID, source.OrderNumber, source.PaymentDiscountAmount, source.PaymentReference, source.Project, source.Quantity, source.Status, source.Subscription, source.Timestamp, source.TrackingNumber, source.Type, source.VATCode, source.VATPercentage, source.VATType, source.YourRef);
SET @rows_affected_merge = @@ROWCOUNT;
DELETE t FROM target_transaction_lines_table t
INNER JOIN staging_delete d ON t.ID = d.EntityKey;
SET @rows_deleted = @@ROWCOUNT;
ROLLBACK TRANSACTION;
SELECT @rows_affected_merge as rows_affected, @rows_deleted as rows_deleted;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
THROW;
END CATCH' datacontainer 'your_sql_server_datacontainer'
)
loop
l_countinserted := r_result.rows_affected;
l_countdeleted := r_result.rows_deleted;
end loop;
end if;
-- Log modification results
l_debug := 'MERGE completed. Total rows affected: ' || l_countinserted || ' records.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
l_debug := 'DELETE completed. Total rows deleted: ' || l_countdeleted || ' records.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
if l_mode = 'PROD' then
l_debug := 'PROD mode: transaction committed';
else
l_debug := 'TEST mode: transaction rolled back (no modifications persisted)';
end if;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
-- Post-modification verification: get counts per division from staging tables
-- (for both TEST and PROD modes to verify operations)
-- Note: We calculate count_records_inserted from staging table (before truncate)
-- but for count_records_deleted, we use the actual DELETE result (l_countdeleted)
-- because after DELETE, records no longer exist in target table for INNER JOIN
l_debug := 'calculating counts per division from staging tables';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
-- Count inserts/updates per division (from staging table before truncate)
for r_counts in (
execute native 'SELECT
Division,
COUNT(*) as count_inserted
FROM staging_insertupdate
GROUP BY Division' datacontainer 'your_sql_server_datacontainer'
)
loop
update timestamp_table@inmemorystorage set count_records_inserted = r_counts.count_inserted where division = r_counts.Division;
end loop;
-- For deletes, we use the actual DELETE result (l_countdeleted) instead of recalculating
-- because after DELETE, records no longer exist in target table
-- Initialize count_records_deleted to 0 for all divisions, then set it based on l_countdeleted
-- Since we delete all divisions at once, we need to distribute l_countdeleted across divisions
-- For simplicity, we'll use to_delete as the expected count and compare with l_countdeleted globally
update timestamp_table@inmemorystorage set count_records_deleted = 0;
-- Cleanup staging tables (truncate instead of drop, tables must remain for parser recognition)
-- In TEST mode, don't truncate to allow inspection of data
if l_mode = 'PROD' then
execute native 'TRUNCATE TABLE dbo.staging_insertupdate;
TRUNCATE TABLE dbo.staging_delete;' datacontainer 'your_sql_server_datacontainer';
l_debug := 'staging tables truncated';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
else
l_debug := 'TEST mode: staging tables not truncated (data available for inspection)';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
end if;
l_debug := 'finished the modification processing.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
-- Final verifications before reporting
-- verify that all insert/update records were processed (count_records_inserted should equal insertupdate)
select sum(insertupdate - count_records_inserted) into l_checkinsertupdate from timestamp_table@inmemorystorage;
-- verify that MERGE result matches expected total (l_countinserted should equal sum of insertupdate)
select sum(insertupdate) into l_total_expected from timestamp_table@inmemorystorage;
if l_countinserted != l_total_expected then
l_checkinsertupdate := l_checkinsertupdate + (l_total_expected - l_countinserted);
l_debug := 'WARNING: MERGE result (' || l_countinserted || ') does not match expected total (' || l_total_expected || ')';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
end if;
-- verify that all delete records were processed
-- Use l_countdeleted (actual DELETE result) instead of count_records_deleted
-- because after DELETE, records no longer exist in target table for recalculation
select sum(to_delete) into l_total_expected from timestamp_table@inmemorystorage;
l_checkdeleted := l_total_expected - l_countdeleted;
if l_checkdeleted != 0 then
l_debug := 'WARNING: DELETE result (' || l_countdeleted || ') does not match expected total (' || l_total_expected || ')';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
end if;
-- in TEST mode, don't check diff with actual data (rollback means data didn't change)
-- in PROD mode, check that final count matches eol_count
if l_mode = 'PROD' then
select sum((sql_count + (insertupdate - to_update) - to_delete) - eol_count) into l_checkdiff from timestamp_table@inmemorystorage;
else
l_checkdiff := 0; -- skip diff check in TEST mode (data rolled back)
end if;
l_scriptsuccess := case when l_checkinsertupdate + l_checkdeleted + l_checkdiff = 0 then true else false end;
l_debug := 'global verification on insert/update/delete: '
|| case when l_scriptsuccess = true then 'ALL GOOD.' else 'ERROR: inconsistencies in verification step.' end;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
else
-- ========================================================================
-- Verification failed: Diff After <> 0
-- ========================================================================
-- Data may have changed in EOL during API call, skip modification
-- to avoid inconsistencies (script will re-run at next scheduled time)
-- ========================================================================
l_debug := 'WARNING: Verification failed - Diff After is not zero (' || l_total_diff_after || '). Data may have changed in EOL during API call. Skipping modification and post-verification. Script will re-run at next scheduled time.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
l_scriptsuccess := false;
l_countinserted := 0;
l_countdeleted := 0;
-- initialize count_records_inserted and count_records_deleted to 0 for email reporting
update timestamp_table@inmemorystorage set count_records_inserted = 0, count_records_deleted = 0;
-- cleanup staging tables in PROD mode (in TEST mode, keep them for inspection)
if l_mode = 'PROD' then
execute native 'TRUNCATE TABLE dbo.staging_insertupdate;
TRUNCATE TABLE dbo.staging_delete;' datacontainer 'your_sql_server_datacontainer';
l_debug := 'staging tables truncated';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
else
l_debug := 'TEST mode: staging tables not truncated (data available for inspection)';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
end if;
end if;
-- ========================================================================
-- STEP 4: EMAIL REPORTING
-- ========================================================================
-- Build and send email report with verification results:
-- - Script status (success/failed/skipped)
-- - HTML table with verification results per division
-- - Total operations summary
-- ========================================================================
l_scriptduration := round((sysdate-l_scriptstart)*60*60*24,3); -- in seconds
l_debug := 'script duration: ' || l_scriptduration || ' seconds.';
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
-- Build HTML table with division verification data
l_stmt := '<table border="1" cellpadding="5" cellspacing="0" style="border-collapse: collapse;"><tr><th>Division</th><th>Division Name</th><th>SQL Count</th><th>EOL Count</th><th>Diff Before</th><th>merge found from EOL</th><th>To Update</th><th>To Insert</th><th>delete found from EOL</th><th>To Delete</th><th>Total Operations</th><th>Diff After</th></tr>';
for r in (select * from timestamp_table@inmemorystorage order by division)
loop
l_stmt := l_stmt || '<tr><td>' || r.division || '</td><td>'
|| case
when r.division = 1000001 then 'Division 1'
when r.division = 1000002 then 'Division 2'
when r.division = 1000003 then 'Division 3'
when r.division = 1000004 then 'Division 4'
when r.division = 1000005 then 'Division 5'
when r.division = 1000006 then 'Division 6'
when r.division = 1000007 then 'Division 7'
when r.division = 1000008 then 'Division 8'
when r.division = 1000009 then 'Division 9'
when r.division = 1000010 then 'Division 10'
when r.division = 1000011 then 'Division 11'
when r.division = 1000012 then 'Division 12'
when r.division = 1000013 then 'Division 13'
when r.division = 1000014 then 'Division 14'
else 'Unknown'
end || '</td><td>' || nvl(r.sql_count, 0) || '</td><td>' || nvl(r.eol_count, 0) || '</td><td>'
|| (nvl(r.eol_count, 0) - nvl(r.sql_count, 0)) || '</td><td>' || nvl(r.insertupdate, 0) || '</td><td>'
|| nvl(r.to_update, 0) || '</td><td>' || nvl(r.to_insert, 0) || '</td><td>' || nvl(r.deleted, 0)
|| '</td><td>' || nvl(r.to_delete, 0) || '</td><td>' || (nvl(r.insertupdate, 0) + nvl(r.deleted, 0))
|| '</td><td>'
|| ((nvl(r.sql_count, 0) + nvl(r.to_insert, 0) - nvl(r.to_delete, 0)) - nvl(r.eol_count, 0)) || '</td></tr>';
end loop;
-- Add total row to HTML table
l_stmt := l_stmt || '<tr style="font-weight: bold; background-color: #f0f0f0;"><td colspan="2">TOTAL</td><td></td><td></td><td>'
|| (select sum(nvl(eol_count, 0) - nvl(sql_count, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(insertupdate, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(to_update, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(to_insert, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(deleted, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(to_delete, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(insertupdate, 0) + nvl(deleted, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum((nvl(sql_count, 0) + nvl(to_insert, 0) - nvl(to_delete, 0)) - nvl(eol_count, 0)) from timestamp_table@inmemorystorage) || '</td></tr>';
l_stmt := l_stmt || '</table>';
-- Send email report
sendmail(
-- email subject here
'[' || l_mode || '] synchronization script for eol transactionlines: ' || case when l_total_diff_after <> 0 then 'SKIPPED - verification failed (diff <> 0)' when l_scriptsuccess = true then 'full success!' else 'ERROR: inconsistencies in verification step.' end
-- email body here
,'script mode: ' || l_mode || '<br>'
|| 'script status: ' || case when l_total_diff_after <> 0 then '<b>SKIPPED - Verification failed: Diff After is not zero (' || l_total_diff_after || '). Data may have changed in EOL during API call. Modification and post-verification were skipped. Script will re-run at next scheduled time.</b>' when l_scriptsuccess = true then 'full success.' else 'ERROR: inconsistencies in verification step.' end || '<br>'
|| 'script start time: ' || to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') || '<br>'
|| 'script duration: ' || l_scriptduration || ' seconds.' || '<br>'
|| case when l_total_diff_after = 0 then 'records deleted (to delete and for update done by insert): ' || l_countdeleted || '<br>' || 'records inserted (insert and update): ' || (select sum(count_records_inserted) from timestamp_table@inmemorystorage) || '<br>' else '' end
|| case when l_mode = 'TEST' then '<b>NOTE: Running in TEST mode - no modifications were executed.</b><br>' else '' end
|| '<br>Verification results by division:<br>'
|| l_stmt
-- || 'full log below' || '<br>'
-- || replace(l_log, chr(13), '<br>')
-- recipients here
,l_emailrecipients
);
-- exception handling
exception
when others
then
l_debug := 'Error catched in exception: ' || chr(13) || sqlerrm;
dbms_output.put_line(l_debug); l_log := l_log || sysdate || ' ' || l_debug || chr(13);
l_scriptsuccess := false;
l_scriptduration := round((sysdate-l_scriptstart)*60*60*24,3); -- in seconds. computed again here because exception skipped it.
-- build HTML table with division verification data (if available)
begin
l_stmt := '<table border="1" cellpadding="5" cellspacing="0" style="border-collapse: collapse;"><tr><th>Division</th><th>Division Name</th><th>SQL Count</th><th>EOL Count</th><th>Diff Before</th><th>merge found from EOL</th><th>To Update</th><th>To Insert</th><th>delete found from EOL</th><th>To Delete</th><th>Total Operations</th><th>Diff After</th></tr>';
for r in (select * from timestamp_table@inmemorystorage order by division)
loop
l_stmt := l_stmt || '<tr><td>' || r.division || '</td><td>'
|| case
when r.division = 1000001 then 'Division 1'
when r.division = 1000002 then 'Division 2'
when r.division = 1000003 then 'Division 3'
when r.division = 1000004 then 'Division 4'
when r.division = 1000005 then 'Division 5'
when r.division = 1000006 then 'Division 6'
when r.division = 1000007 then 'Division 7'
when r.division = 1000008 then 'Division 8'
when r.division = 1000009 then 'Division 9'
when r.division = 1000010 then 'Division 10'
when r.division = 1000011 then 'Division 11'
when r.division = 1000012 then 'Division 12'
when r.division = 1000013 then 'Division 13'
when r.division = 1000014 then 'Division 14'
else 'Unknown'
end || '</td><td>' || nvl(r.sql_count, 0) || '</td><td>' || nvl(r.eol_count, 0) || '</td><td>'
|| (nvl(r.eol_count, 0) - nvl(r.sql_count, 0)) || '</td><td>' || nvl(r.insertupdate, 0) || '</td><td>'
|| nvl(r.to_update, 0) || '</td><td>' || nvl(r.to_insert, 0) || '</td><td>' || nvl(r.deleted, 0)
|| '</td><td>' || nvl(r.to_delete, 0) || '</td><td>' || (nvl(r.insertupdate, 0) + nvl(r.deleted, 0))
|| '</td><td>'
|| ((nvl(r.sql_count, 0) + nvl(r.to_insert, 0) - nvl(r.to_delete, 0)) - nvl(r.eol_count, 0)) || '</td></tr>';
end loop;
-- add total row
l_stmt := l_stmt || '<tr style="font-weight: bold; background-color: #f0f0f0;"><td colspan="2">TOTAL</td><td></td><td></td><td>'
|| (select sum(nvl(eol_count, 0) - nvl(sql_count, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(insertupdate, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(to_update, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(to_insert, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(deleted, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(to_delete, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum(nvl(insertupdate, 0) + nvl(deleted, 0)) from timestamp_table@inmemorystorage) || '</td><td>'
|| (select sum((nvl(sql_count, 0) + nvl(to_insert, 0) - nvl(to_delete, 0)) - nvl(eol_count, 0)) from timestamp_table@inmemorystorage) || '</td></tr>';
l_stmt := l_stmt || '</table>';
exception
when others then
l_stmt := '<p>Error building verification table: ' || sqlerrm || '</p>';
end;
sendmail(
-- email subject here
'[' || l_mode || '] synchronization script for eol transactionlines: ERROR. An sql exception occured.'
-- email body here
,'script mode: ' || l_mode || '<br>'
|| 'script status: ERROR. An sql exception occured.' || '<br>'
|| case when l_mode = 'PROD' then 'check the table consistency, it may be damaged as partial operation might have occured.' || '<br>' else '' end
|| 'script start time: ' || to_char(l_scriptstart, 'YYYY-MM-DD HH24:MI:SS') || '<br>'
|| 'script duration: ' || l_scriptduration || ' seconds.' || '<br>'
|| 'records deleted (to delete and for update done by insert): ' || l_countdeleted || '<br>'
|| 'records inserted (insert and update): ' || (select sum(count_records_inserted) from timestamp_table@inmemorystorage) || '<br>'
|| '<br>Verification results by division:<br>'
|| l_stmt
-- || 'full log below' || '<br>'
-- || replace(l_log, chr(13), '<br>')
-- recipients here
,l_emailrecipients
);
end;