Bij het uitvoeren van het volgende statement krijg ik een foutmelding onder 25.0.10:
itgensql712
The name ‘EOL_AMOUNT’ refers to multiple meanings.
Consider use of one of the following alternatives: eol_amount, EOL_AMOUNT_EXCL_VAT, eol_amount_vat, eol_account_id, eol_account_code, eol_account_name.
Onder 24.0.770 trad geen foutmelding op.
Ik zou verwachten dat:
- het regelnummer ook getoond wordt (de code is nogal lang).
- de foutmelding uberhaupt niet optreedt.
SQL-statement
Het volgende SQL-statement is gebruikt:
local define EOL_ROTATOR "{eol,eol2,eol3,eol4,eol5}"
local define EOL_MAIN_DIVISION "123456"
begin
create or replace table CustomerTransactionsBase@inmemorystorage
as
select cast(tle.Id as varchar2)
eol_id
, tle.Date
eol_transaction_date
, act.Code
bubs_lvr_id
, act.Id
eol_account_id
, act.Code
eol_account_code
, act.Name
eol_account_name
, case
when tle.JournalCode = '20'
then '{res:itgen_cloud_payment_thru_bank}'
when tle.JournalCode = '53'
then '{res:itgen_cloud_payment_thru_paypal}'
when tle.JournalCode = '70' and sie.type = 8020
then '{res:itgen_cloud_sales_invoice}'
when tle.JournalCode = '70' and sie.type = 8022
then '{res:itgen_cloud_sales_credit_note}'
when tle.JournalCode = '79'
then '{res:itgen_cloud_irrecoverable}'
when tle.JournalCode = '90'
then '{res:itgen_cloud_other}'
else tle.JournalCode
end
eol_transaction_category_description
, tle.JournalCode
eol_journal_code
, tle.EntryNumber
eol_transaction_number
, gat.Type
eol_gl_account_type_code
, '{res:' || gae.resource_code || '}'
eol_gl_account_type_description
, case
when tle.InvoiceNumber is not null
then tle.LineNumber
else null
end
eol_transaction_line_number
--
, tle.Currency
eol_currency_code
, --
-- Cash flow amount, including VATs.
--
case
when tle.AmountVATBaseFC is not null
then tle.AmountVATBaseFC + coalesce(tle.AmountVATFC, 0)
else tle.AmountFC
end
eol_transaction_amount_fc
, --
-- Cash flow amount, excluding VATs.
-- Can be null, for instance on bank transfers.
--
tle.AmountVATBaseFC
eol_transaction_amount_fc_excl_vat
, --
-- VAT amount Foreign Currency.
--
tle.AmountVATFC
eol_transaction_amount_vat_fc
, tle.Description
eol_transaction_description
, case
when tle.PaymentReference = 'NONREF'
then null
when tle.PaymentReference = 'EREF'
then null
else tle.PaymentReference
end
eol_payment_reference
, tle.Quantity
eol_quantity
, tle.Type
eol_transaction_type_code
, '{res:' || tte.resource_code || '}'
eol_transaction_type_description
, tle.YourRef
eol_customer_reference
, itm.Code
eol_item_code
, itm.Description
eol_item_description
, igp.Code
eol_item_group_code
, 'S'
product_group_code
, tle.Notes
eol_notes
, tle.DueDate
eol_due_date
from TransactionLinesIncremental@${EOL_ROTATOR} tle
join AccountsIncremental@${EOL_ROTATOR} act
on act.Id = tle.Account
and act.Status = 'C' /* Only customers. */
join GLAccountsIncremental@${EOL_ROTATOR} gat
on gat.Division = tle.Division
and gat.Id = tle.GLAccount
left
outer
join ItemsIncremental@${EOL_ROTATOR} itm
on itm.Division = tle.Division
and itm.Id = tle.Item
left
outer
join ExactOnlineREST..ItemGroups@${EOL_ROTATOR} igp
on igp.Division = itm.Division
and igp.Id = itm.ItemGroup
left
outer
join SalesInvoicesIncremental@${EOL_ROTATOR} sie
on sie.Division = tle.Division
and sie.InvoiceNumber = tle.InvoiceNumber
and tle.Type in (20, 21, 22)
left
outer
join TransactionTypes@${EOL_ROTATOR} tte
on tte.Code = tle.Type
left
outer
join InvoiceTypes@${EOL_ROTATOR} ite
on ite.Code = sie.Type
left
outer
join GLAccountTypes@${EOL_ROTATOR} gae
on gae.Code = gat.Type
where tle.Division = ${EOL_MAIN_DIVISION}
and tle.Account is not null
and tle.Type != 84 /* Geen boekingen voor uitgestelde omzet. */
and tle.AmountDC != 0
and tle.LineNumber != 9999 /* Geen BTW. */
and tle.JournalCode not in
( '10' /* Kas. */
, '21' /* Credit card. */
, '60' /* Purchase. */
)
;
--
create or replace table PartyTransactionsSoll@InMemoryStorage
as
select pty.id pty_id
, cte.eol_id
eol_id
, cte.eol_transaction_date
date
, cte.eol_journal_code
journal_code
, cte.eol_transaction_number
number
, cte.eol_transaction_line_number
line_number
, cte.eol_transaction_category_description
category_description
, cte.eol_gl_account_type_code
gl_account_type_code
, cte.eol_gl_account_type_description
gl_account_type_description
, cte.eol_currency_code
currency_code
, cte.eol_transaction_amount_fc
amount_fc
, cte.eol_transaction_amount_fc_excl_vat
amount_fc_excl_vat
, cte.eol_transaction_amount_vat_fc
amount_vat_fc
, cte.product_group_code
product_group_code
, cte.eol_quantity
quantity
, cte.eol_item_code
item_code
, cte.eol_item_description
item_description
, cte.eol_customer_reference
customer_reference
, cte.eol_transaction_description
description
, cte.eol_payment_reference
payment_reference
, cte.eol_transaction_type_code
type_code
, cte.eol_transaction_type_description
type_description
, substr(cte.eol_notes, 1, 4000)
notes
--
, newid() uid
, coalesce(user, '?')
created_by
, sys_context('USERENV', 'APPLICATION_FULL')
created_at
, sysdateutc
date_created
, sys_context('USERENV', 'SESSIONID')
session_created
, coalesce(user, '?')
modified_by
, sys_context('USERENV', 'APPLICATION_FULL')
modified_at
, sysdateutc
date_modified
, sys_context('USERENV', 'SESSIONID')
session_modified
from CustomerTransactionsBase@inmemorystorage cte
join cs_parties@cs pty
on pty.bubs_lvr_id = cte.bubs_lvr_id
;
create or replace table CustomerBalancesBase@InMemoryStorage
as
select bubs_lvr_id
, eol_account_id
, eol_account_code
, eol_account_name
, --
-- Total revenues including VAT.
--
eol_transaction_amount_fc
eol_amount
, --
-- Total revenues excluding VAT.
--
eol_transaction_amount_fc - eol_transaction_amount_vat_fc
eol_amount_excl_vat
, --
-- VATs.
--
eol_transaction_amount_vat_fc
eol_amount_vat
, eol_transaction_date
, product_group_code
, eol_transaction_number
from CustomerTransactionsBase@inmemorystorage
where eol_journal_code = '70'
and eol_transaction_line_number != 0
;
--
create or replace table CustomerBalancesBaseGroup@InMemoryStorage
as
select bubs_lvr_id
, eol_account_id
, eol_account_code
, eol_account_name
, --
-- Total revenues including VAT.
--
sum(-1 * eol_amount)
eol_amount
, --
-- Total revenues excluding VAT.
--
sum(-1 * eol_amount_excl_vat)
eol_amount_excl_vat
, --
-- VATs.
--
sum(-1 * eol_amount_vat)
eol_amount_vat
, --
-- Services amount (excluding VAT).
--
sum(case when product_group_code = 'S' then -1 * eol_amount_excl_vat else 0 end)
eol_services_amount_excl_vat
, --
-- Subscriptions amount (excluding VAT).
--
sum(case when product_group_code = 'L' then -1 * eol_amount_excl_vat else 0 end)
eol_subscriptions_amount_excl_vat
, min(eol_transaction_date)
eol_transaction_date_min
, max(eol_transaction_date)
eol_transaction_date_max
, count(distinct eol_transaction_number)
TransactionCount1D
from CustomerBalancesBase@inmemorystorage
group
by bubs_lvr_id
, eol_account_code
, eol_account_id
, eol_account_name
;
end;