When replicating Visma.net JournalTransactions to Azure SQL Server, I am getting an error when the transaction lines are being fetched:
itgenoda061
One or more errors occurred.
Either ‘FinPeriodId’ or ‘LastModifiedDateTime’ must be defined as a filter (-)
Replication of Customers and Suppliers across 7 entities works fine.
Can the information need be covered through the view JournalTransactionLines?
The JournalTransactions and JournalTransactionsByPeriodOrDateV2 require a company code plus period or date (exactly one of these).
The next release of Invantive Cloud includes a new view JournalTransactions, similar to JournalTransactionLines which covers all transaction headers. Once released, this topic will be updated.
For performance analysis, please grant delegation as described in Access via delegation on Invantive Cloud for consultants.
The new Visma.net views which pre-join the data have been tested using the following module to copy essential Visma.net tables from Visma.net to SQL Server:
begin
cloud_http.set_response_content_type('text/html');
--
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Started.</p>');
--
create or replace table JournalTransactions@mss
as
select *
from VismaNet.Views.JournalTransactions@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed JournalTransactions with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table JournalTransactionLines@mss
as
select *
from VismaNet.Views.JournalTransactionLines@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed JournalTransactionLines with ' || to_char(sqlrowcount) || ' rows.</p>');
--
/* CUSTOMERS */
create or replace table Customers@mss
as
select *
from Customers@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed Customers with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table CustomerCreditNotes@mss
as
select *
from CustomerCreditNotes@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed CustomerCreditNotes with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table CustomerCreditNoteLines@mss
as
select *
from CustomerCreditNoteLines@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed CustomerCreditNoteLines with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table CustomerDebitNotes@mss
as
select *
from CustomerDebitNotes@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed CustomerDebitNotes with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table CustomerDebitNoteLines@mss
as
select *
from CustomerDebitNoteLines@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed CustomerDebitNoteLines with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table CustomerInvoices@mss
as
select *
from CustomerInvoices@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed CustomerInvoices with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table CustomerInvoiceLines@mss
as
select *
from CustomerInvoiceLines@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed CustomerInvoiceLines with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table CustomerPayments@mss
as
select *
from CustomerPayments@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed CustomerPayments with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table CustomerPaymentLines@mss
as
select *
from CustomerPaymentLines@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed CustomerPaymentLines with ' || to_char(sqlrowcount) || ' rows.</p>');
--
/* SUPPLIERS */
create or replace table Suppliers@mss
as
select *
from Suppliers@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed Suppliers with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table SupplierInvoices@mss
as
select *
from SupplierInvoices@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed SupplierInvoices with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table SupplierInvoiceLines@mss
as
select *
from SupplierInvoiceLines@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed SupplierInvoiceLines with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table SupplierPayments@mss
as
select *
from SupplierPayments@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed SupplierPayments with ' || to_char(sqlrowcount) || ' rows.</p>');
--
create or replace table SupplierPaymentLines@mss
as
select *
from SupplierPaymentLines@vnt
;
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Completed SupplierPaymentLines with ' || to_char(sqlrowcount) || ' rows.</p>');
--
cloud_http.append_to_response_body_text('<p>' || to_char(sysdateutc) || ' Finished.</p>');
end;
The Visma.net replication outputs a HTML-page such as:
27-05-2022 15:55 Started.
27-05-2022 16:04 Completed JournalTransactions with **** rows.
27-05-2022 17:46 Completed JournalTransactionLines with ****** rows.
27-05-2022 17:49 Completed Customers with ***** rows.
27-05-2022 17:50 Completed CustomerCreditNotes with **** rows.
27-05-2022 17:51 Completed CustomerCreditNoteLines with **** rows.
27-05-2022 17:51 Completed CustomerDebitNotes with *** rows.
27-05-2022 17:51 Completed CustomerDebitNoteLines with *** rows.
27-05-2022 18:00 Completed CustomerInvoices with ***** rows.
27-05-2022 18:11 Completed CustomerInvoiceLines with ****** rows.
27-05-2022 18:21 Completed CustomerPayments with ***** rows.
27-05-2022 18:30 Completed CustomerPaymentLines with ****** rows.
27-05-2022 18:30 Completed Suppliers with **** rows.
27-05-2022 18:32 Completed SupplierInvoices with ***** rows.
27-05-2022 18:34 Completed SupplierInvoiceLines with ***** rows.
27-05-2022 18:39 Completed SupplierPayments with ***** rows.
27-05-2022 18:44 Completed SupplierPaymentLines with ***** rows.
27-05-2022 18:44 Finished.
This question was automatically closed after at least 2 weeks of inactivity after a possible solution was provided. The last answer given has been marked as a solution.
Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.