Via onderstaande query worden boekingen uit de verschillende (624) Exact Online Administraties geëxporteerd.
Echter, de query wordt succesvol uitgevoerd, ik zie alleen het resultaat nergens terug cq. per administratie geëxporteerd in Excel formaat.
Onderstaande query:
set requests-parallel-max@eol 16
use select code, 'eol' from systemdivisions@eol limit 2
set use-http-disk-cache@eol true
set http-disk-cache-max-age-sec@eol 86400
create or replace table tle@mss
( divisionownercompanyname varchar2 label '{res:itgen_eol_division_owner_company_name}'
, divisionownercompanynumber varchar2 label '{res:itgen_eol_division_owner_company_number}'
, divisionlabel varchar2 label '{res:itgen_eol_division_label}'
, financialyear int16 label '{res:itgen_eol_financialyear}'
, financialperiod int16 label '{res:itgen_eol_financialperiod}'
, date datetime label '{res:itgen_eol_date}'
, journalcode varchar2 label '{res:itgen_eol_journalcode}'
, journaldescription varchar2 label '{res:itgen_eol_journaldescription}'
, entrynumber int32 label '{res:itgen_eol_entrynumber}'
, linenumber int32 label '{res:itgen_eol_linenumber}'
, amountdc double label '{res:itgen_eol_amountdc}'
, currency varchar2 label '{res:itgen_eol_currency}'
, amountfc double label '{res:itgen_eol_amountfc}'
, amountvatbasefc double label '{res:itgen_eol_amountvatbasefc}'
, amountvatfc double label '{res:itgen_eol_amountvatfc}'
, CostCenter varchar2 label '{res:itgen_eol_costcenter}'
, CostCenterDescription varchar2 label '{res:itgen_eol_costcenter_description}'
, CostUnit varchar2 label '{res:itgen_eol_costunit}'
, CostUnitDescription varchar2 label '{res:itgen_eol_costunit_description}'
, description varchar2 label '{res:itgen_eol_description}'
, yourref varchar2 label '{res:itgen_eol_yourref}'
, glaccountcode varchar2 label '{res:itgen_eol_glaccountcode}'
, glaccountdescription varchar2 label '{res:itgen_eol_glaccountdescription}'
, linetype int16 label '{res:itgen_eol_linetype}'
, notes varchar2 label '{res:itgen_eol_notes}'
, paymentdiscountamount double label '{res:itgen_eol_paymentdiscountamount}'
, paymentreference varchar2 label '{res:itgen_eol_paymentreference}'
, quantity double label '{res:itgen_eol_quantity}'
, status int16 label '{res:itgen_eol_status}'
, type int32 label '{res:itgen_eol_type}'
, exchangerate double label '{res:itgen_eol_exchangerate}'
, accountcode varchar2 label '{res:itgen_eol_accountcode}'
, accountname varchar2 label '{res:itgen_eol_accountname}'
, itemcode varchar2 label '{res:itgen_eol_itemcode}'
, itemdescription varchar2 label '{res:itgen_eol_itemdescription}'
, projectcode varchar2 label '{res:itgen_eol_projectcode}'
, projectdescription varchar2 label '{res:itgen_eol_projectdescription}'
, ordernumber int32 label '{res:itgen_eol_ordernumber}'
, invoicenumber int32 label '{res:itgen_eol_invoicenumber}'
, duedate datetime label '{res:itgen_eol_duedate}'
, vatcode varchar2 label '{res:itgen_eol_vatcode}'
, vatcodedescription varchar2 label '{res:itgen_eol_vatcodedescription}'
, vatpercentage double label '{res:itgen_eol_vatpercentage}'
, vattype varchar2 label '{res:itgen_eol_vattype}'
, extradutyamountfc double label '{res:itgen_eol_extradutyamountfc}'
, extradutypercentage double label '{res:itgen_eol_extradutypercentage}'
, glaccounttype int32 label '{res:itgen_eol_gl_account_type}'
, glaccountbalanceside varchar2 label '{res:itgen_eol_gl_account_balance_side}'
, glaccountbalancetype varchar2 label '{res:itgen_eol_gl_account_balance_type}'
, glaccountsearchcode varchar2 label '{res:itgen_eol_gl_account_search_code}'
, glaccounttypedescription varchar2 label '{res:itgen_eol_gl_account_type_description}'
, SubscriptionNumber int32 label '{res:itgen_eol_subscription_number}'
, SubscriptionDescription varchar2 label '{res:itgen_eol_subscription_description}'
, created datetime label '{res:itgen_eol_created}'
, creatorfullname varchar2 label '{res:itgen_eol_creatorfullname}'
, modified datetime label '{res:itgen_eol_modified}'
, modifierfullname varchar2 label '{res:itgen_eol_modifierfullname}'
, divisionshortname int64 label '{res:itgen_eol_division_short_name}'
, divisioncompanycocnumber varchar2 label '{res:itgen_eol_division_coc_number}'
, divisioncompanyibannumber varchar2 label '{res:itgen_eol_division_iban_number}'
, divisioncompanyrsinnumber varchar2 label '{res:itgen_eol_division_rsin_number}'
, divisioncompanyvatnumber varchar2 label '{res:itgen_eol_division_vat_number}'
, divisioncompanywagetaxnumber varchar2 label '{res:itgen_eol_division_wage_tax_number}'
, divisionname varchar2 label '{res:itgen_eol_division_name}'
, divisioncompanyname varchar2 label '{res:itgen_eol_division_company_name}'
, division int32 label '{res:itgen_eol_division}'
)
bulk insert into tle@mss
( divisionownercompanyname
, divisionownercompanynumber
, divisionlabel
, financialyear
, financialperiod
, date
, journalcode
, journaldescription
, entrynumber
, linenumber
, amountdc
, currency
, amountfc
, amountvatbasefc
, amountvatfc
, CostCenter
, CostCenterDescription
, CostUnit
, CostUnitDescription
, description
, yourref
, glaccountcode
, glaccountdescription
, linetype
, notes
, paymentdiscountamount
, paymentreference
, quantity
, status
, type
, exchangerate
, accountcode
, accountname
, itemcode
, itemdescription
, projectcode
, projectdescription
, ordernumber
, invoicenumber
, duedate
, vatcode
, vatcodedescription
, vatpercentage
, vattype
, extradutyamountfc
, extradutypercentage
, glaccounttype
, glaccountbalanceside
, glaccountbalancetype
, glaccountsearchcode
, glaccounttypedescription
, SubscriptionNumber
, SubscriptionDescription
, created
, creatorfullname
, modified
, modifierfullname
, divisionshortname
, divisioncompanycocnumber
, divisioncompanyibannumber
, divisioncompanyrsinnumber
, divisioncompanyvatnumber
, divisioncompanywagetaxnumber
, divisionname
, divisioncompanyname
, division
)
select tle.DivisionOwnerCompanyName
, tle.DivisionOwnerCompanyNumber
, tle.DivisionLabel
, tle.FinancialYear
, tle.FinancialPeriod
, tle.Date
, tle.JournalCode
, tle.JournalDescription
, tle.EntryNumber
, tle.LineNumber
, tle.AmountDC
, tle.Currency
, tle.AmountFC
, tle.AmountVATBaseFC
, tle.AmountVATFC
, tle.CostCenter
, tle.CostCenterDescription
, tle.CostUnit
, tle.CostUnitDescription
, tle.Description
, tle.YourRef
, tle.GLAccountCode
, tle.GLAccountDescription
, tle.LineType
, tle.Notes
, tle.PaymentDiscountAmount
, tle.PaymentReference
, tle.Quantity
, tle.Status
, tle.Type
, tle.ExchangeRate
--
-- ERP module specific columns.
--
, tle.AccountCode
, tle.AccountName
, tle.ItemCode
, tle.ItemDescription
, tle.ProjectCode
, tle.ProjectDescription
, tle.OrderNumber
, tle.InvoiceNumber
, tle.DueDate
--
-- Generic fields.
--
, tle.VATCode
, tle.VATCodeDescription
, tle.VATPercentage
, tle.VATType
, tle.ExtraDutyAmountFC
, tle.ExtraDutyPercentage
, tle.GLAccountType
, tle.GLAccountBalanceSide
, tle.GLAccountBalanceType
, tle.GLAccountSearchCode
, tle.GLAccountTypeDescription
, ssn.Number
SubscriptionNumber
, ssn.Description
SubscriptionDescription
, tle.Created
, tle.CreatorFullName
, tle.Modified
, tle.ModifierFullName
, tle.DivisionShortName
, tle.DivisionCompanyCoCNumber
, tle.DivisionCompanyIBANNumber
, tle.DivisionCompanyRSINNumber
, tle.DivisionCompanyVATNumber
, tle.DivisionCompanyWageTaxNumber
, tle.DivisionName
, tle.DivisionCompanyName
, tle.division
from transactionlinesbulk@eol tle
left
outer
join ExactOnlineREST..Subscriptions@eol ssn
on ssn.Division = tle.Division
and ssn.EntryId = tle.Subscription
--select /*+ result_set_name("TransactionLines") */
-- *
--from tle@inmemorystorage tle
--order
--by tle.division
--, tle.journalcode
--, tle.entrynumber