Voorbeeld inlezen afgedrukte SEDIS facturen in Exact Online

[ekg90]

Wij gebruiken het volgende script om reeds afgedrukte facturen uit SEDIS in te lezen in Exact Online:

--
-- Importeer alle reeds verzonden facturen uit SEDIS
-- in Exact Online.
--
-- Het factuurnummer in Exact Online is gelijk aan dat uit SEDIS.
--
rollback transaction

set use-http-memory-cache@eol false

set use-http-disk-cache@eol false

local define DIVISION "123456"

local define INVOICENUMBER_FROM "25100001"

local define INVOICENUMBER_TO "25100250"

begin transaction

insert into exactonlinerest..SalesEntries@eol
( Division
, EntryNumber
, InvoiceNumber
, Customer
, Description
, Journal
, Status
, Type
, EntryDate
, DueDate
, Currency
) 
select ${DIVISION} Division
,      ftr.InvoiceNumber EntryNumber
,      ftr.InvoiceNumber
,      act.Id Customer
,      'Factuur ' || invoiceNumber description
,      '70' Journal
,      50
,      case
       when AmountDC < 0 then 21 else 20 end Type
,      to_date(ftr.InvoiceDate, 'DD-MM-YYYY') InvoiceDate
,      to_date(ftr.ExpiryDate, 'DD-MM-YYYY') DueDate
,      ftr.Currency
from   import20250115_ftr_sedis@sql ftr
left
outer
join   ExactOnlineREST..AccountsBulk@eol act
on     act.Division = ${DIVISION}
and    act.Code = to_char(ftr.AccountCode)
and    act.Status = 'C' /* Customer */
where  ftr.LineNumber = 1
and    to_char(ftr.InvoiceNumber) like '2510%'
and    ftr.invoicenumber >= ${INVOICENUMBER_FROM}
and    ftr.invoicenumber < ${INVOICENUMBER_TO}
identified by 'INVOICE' || ftr.InvoiceNumber

insert into ExactOnlineREST..SalesEntryLines@eol
( Division
, AmountFC
, Description
, GLAccount
, Quantity
, linenumber
, VATCode
, VATAmountFC
)
select ${DIVISION} Division
,      ftr.AmountDC + 1 * ftr.VATAmountDC
,      ftr.Description
,      gat.ID GLAccount
,      1 Quantity
,      ftr.linenumber
,      ftr.VATCode
,      ftr.VATAmountDC
from   import20250115_ftr_sedis@sql ftr
left
outer
join   GLAccountsBulk@eol gat
on     gat.Division = ${DIVISION}
and    gat.Code = 
       -- Vertaal oude grootboekrekeningen.
       case 
       when ftr.Grootboek = '8000' then '80000' 
       else ftr.Grootboek 
       end
where  1=1
and    ftr.invoicenumber >= ${INVOICENUMBER_FROM}
and    ftr.invoicenumber < ${INVOICENUMBER_TO}
attach to 'INVOICE' || ftr.InvoiceNumber

commit transaction

[ekg90]

En voor de verkoopfacturen die reeds verzonden zijn, maar nog niet betaald waren voor de overgang naar Exact Online gebruiken we deze query om de openstaande posten in te boeken (13000 is openstaande debiteuren, 2000 is kruisposten):

rollback transaction

set use-http-memory-cache@eol false

set use-http-disk-cache@eol false

local define DIVISION "123456"

local define INVOICENUMBER_FROM "23110200"

local define INVOICENUMBER_TO "25000000"

begin transaction

insert into exactonlinerest..GeneralJournalEntries@eol
( Division
, EntryNumber
, JournalCode
, Status
, Type
, FinancialPeriod
, FinancialYear
, Currency
) 
select ${DIVISION} Division
,      ftr.Factuurnummer EntryNumber
,      '90' Journal
,      20
,      90
,      12
,      2024
,      ftr.Currency
--, 'ENTRY' || ftr.Factuurnummer
from   import20250115_ftr0_sedis@sql ftr
where  ftr.Factuurnummer >= ${INVOICENUMBER_FROM}
and    ftr.Factuurnummer < ${INVOICENUMBER_TO}
group
by     ftr.Factuurnummer
,      ftr.Currency
identified by ftr.Factuurnummer

insert into ExactOnlineREST..GeneralJournalEntryLines@eol
( Division
, Account
, AmountFC
, Description
, GLAccount
, Quantity
, linenumber
, Date
)
select ${DIVISION} Division
--, ftr.KlantId
,      act.Id
,      case when gat.code = '13000' then 1 else -1 end * ftr.Bedrag
,      ftr.BR_DESCRIPTION
,      gat.ID GLAccount
,      1 Quantity
,      1 LineNumber
,      to_date('20241231', 'YYYYMMDD') EntryDate
--, ftr.Factuurnummer
--, 'ENTRY' || ftr.Factuurnummer
from   import20250115_ftr0_sedis@sql ftr
left
outer
join   ExactOnlineREST..AccountsBulk@eol act
on     act.Division = ${DIVISION}
and    act.Code = to_char(ftr.KlantId)
and    act.Status = 'C'
left
outer
join   GLAccountsBulk@eol gat
on     gat.Division = ${DIVISION}
and    gat.Code in ( '13000', '2000' )
where  1=1
and    ftr.Factuurnummer >= ${INVOICENUMBER_FROM}
and    ftr.Factuurnummer < ${INVOICENUMBER_TO}
attach to ftr.Factuurnummer

commit transaction