Bruto marge in de verkoopdata is gewijzigd tussen release 25.0 en release 27.0 lijkt het.
Bijvoorbeeld order 20031765 heeft een margin in sorall@inmemorystorage van 50,15, maar dat was voorheen 56 plus centen.
Query om de margin op te halen:
select SOR_COSTPRICEQUANTITY
, POR_SUPPLIERCODE
, POR_SUPPLIERNAME
, POR_ORDERNUMBER
, margin
, margin_percentage
, sor_week
, sor_maand
, sor_Timestamp
, sor_AmountDC
, sor_AmountDiscount
, sor_AmountDiscountExclVat
, sor_AmountFC
, sor_AmountFCExclVat
, sor_ApprovalStatus
, sor_Approved
, sor_Approver
, null sor_CostCenter
, null sor_CostPriceFC
, null sor_CostUnit
, sor_Created
, sor_Creator
, sor_Currency
, null sor_CustomerItemCode
, sor_DeliverTo
, sor_DeliverToContactPerson
, sor_DeliveryAddress
, sor_DeliveryDate
, sor_DeliveryStatus
, sor_Description
, sor_Discount
, sor_Division
, sor_Document
, sor_ID
, sor_InvoiceStatus
, sor_InvoiceTo
, sor_InvoiceToContactPerson
, null sor_Item
, null sor_ItemVersion
, null sor_LineNumber
, sor_Modified
, sor_Modifier
, null sor_NetPrice
, sor_Notes
, sor_OrderDate
, sor_OrderedBy
, sor_OrderedByContactPerson
, sor_OrderID
, sor_OrderNumber
, sor_PaymentCondition
, sor_PaymentReference
, null sor_Pricelist
, sor_Project
, null sor_PurchaseOrder
, null sor_PurchaseOrderLine
, null sor_Quantity
, null sor_QuantityDelivered
, null sor_QuantityInvoiced
, sor_Remarks
, sor_Salesperson
, sor_SelectionCode
, sor_ShippingMethod
, null sor_ShopOrder
, sor_Status
, null sor_UnitCode
, null sor_UnitPrice
, null sor_UseDropShipment
, sor_VATAmount
, sor_VATCode
, sor_WarehouseID
, sor_YourRef
, sor_SalesChannel
, act_Timestamp
, act_Accountant
, act_AccountManager
, act_AccountManagerHID
, act_ActivitySector
, act_ActivitySubSector
, act_AddressLine1
, act_AddressLine2
, act_AddressLine3
, act_Blocked
, act_BRIN
, act_BSN
, act_BusinessType
, act_CanDropShip
, act_ChamberOfCommerce
, act_City
, act_Classification
, act_Classification1
, act_Classification2
, act_Classification3
, act_Classification4
, act_Classification5
, act_Classification6
, act_Classification7
, act_Classification8
, act_Code
, act_CodeAtSupplier
, act_CompanySize
, act_ConsolidationScenario
, act_ControlledDate
, act_Costcenter
, act_CostPaid
, act_Country
, act_Created
, act_Creator
, act_CreditLinePurchase
, act_CreditLineSales
, act_Currency
, act_CustomerSince
, act_DatevCreditorCode
, act_DatevDebtorCode
, act_DiscountPurchase
, act_DiscountSales
, act_Division
, act_Document
, act_DunsNumber
, act_Email
, act_EndDate
, act_EstablishedDate
, act_Fax
, act_GLAccountPurchase
, act_GLAccountSales
, act_GLAP
, act_GLAR
, act_GlnNumber
, act_HasWithholdingTaxSales
, act_ID
, act_IgnoreDatevWarningMessage
, act_IntraStatArea
, act_IntraStatDeliveryTerm
, act_IntraStatSystem
, act_IntraStatTransactionA
, act_IntraStatTransactionB
, act_IntraStatTransportMethod
, act_InvoiceAccount
, act_InvoiceAttachmentType
, act_InvoicingMethod
, act_IsAccountant
, act_IsAgency
, act_IsAnonymised
, act_IsBank
, act_IsCompetitor
, act_IsExtraDuty
, act_IsMailing
, act_IsMember
, act_IsPilot
, act_IsPurchase
, act_IsReseller
, act_IsSales
, act_IsSupplier
, act_Language
, act_Latitude
, act_LeadPurpose
, act_LeadSource
, act_Logo
, act_LogoFileName
, act_LogoThumbnailUrl
, act_LogoUrl
, act_Longitude
, act_MainContact
, act_Modified
, act_Modifier
, act_Name
, act_OINNumber
, act_Parent
, act_PayAsYouEarn
, act_PaymentConditionPurchase
, act_PaymentConditionSales
, act_Phone
, act_PhoneExtension
, act_Postcode
, act_PriceList
, act_PurchaseCurrency
, act_PurchaseLeadDays
, act_PurchaseVATCode
, act_RecepientOfCommissions
, act_Remarks
, act_Reseller
, act_RSIN
, act_SalesCurrency
, act_SalesTaxSchedule
, act_SalesVATCode
, act_SearchCode
, act_SecurityLevel
, act_SeparateInvPerProject
, act_SeparateInvPerSubscription
, act_ShippingLeadDays
, act_ShippingMethod
, act_StartDate
, act_State
, act_Status
, act_StatusSince
, act_TradeName
, act_Type
, act_UniqueTaxpayerReference
, act_VATLiability
, act_VATNumber
, act_Website
, act_DeliveryAdvice
, act_ShowRemarkForSales
, rowid$
from sorall@inmemorystorage
Voorbereidende trigger in Invantive Control for Excel:
declare
l_start_date date := to_date($X{startdate});
begin
create or replace table sim@inmemorystorage
as
select /*x http_disk_cache(true, true, interval '1 week') */
sim.division
, sim.item
, sim.itemcode
, sim.SupplierCode
, sim.SupplierDescription
from supplieritems sim
where sim.MainSupplier = true
;
create or replace table itm@inmemorystorage
as
select itm.*
, igp.code itemgroupcode
from itemsincremental itm
join itemgroups igp
on igp.id = itm.itemgroup
;
create or replace table gle8@InMemoryStorage
as
select id
from glaccountsincremental
where code like '8%'
;
create or replace table tle@inmemorystorage
as
select to_char(tle.date, 'YYYYIW')
tle_week
label 'Week'
, to_char(tle.date, 'YYYYMM')
tle_maand
label 'Maand'
, cast(-tle.amountdc as double)
amountdc
label 'Gefactureerde Omzet'
, itm.itemgroupcode
, tle.*
from gle8@InMemoryStorage gle
join transactionlinesincremental tle
on tle.date >= l_start_date
and tle.journalcode = '70'
and tle.glaccount = gle.id
and tle.linenumber not in (0, 9998, 9999)
join itm@inmemorystorage itm
on itm.id = tle.item
order
by tle.division
, tle.date
, tle.invoicenumber
, tle.linenumber
;
create or replace table sor@inmemorystorage
as
select to_char(sor.orderdate, 'YYYYIW')
sor_week
label 'Week'
, to_char(sor.orderdate, 'YYYYMM')
sor_maand
label 'Maand'
, sor.* prefix with 'sor_'
, act.* prefix with 'act_'
from SalesOrdersV2Incremental sor
join AccountsIncremental act
on act.id = sor.OrderedBy
where sor.orderdate >= l_start_date
;
--
-- Historische inkoopprijzen per artikel.
--
create or replace table purchaselines@inmemorystorage
as
select por.orderdate
, por.orderdate - to_number(to_char(por.orderdate, 'D'))
sun_in_week_date
, to_char(por.orderdate, 'YYYYIW')
por_week
label 'Week'
, to_char(por.orderdate, 'YYYYMM')
por_maand
label 'Maand'
, ple.itemcode
, ple.netprice
, ple.quantity
, por.ordernumber
from exactonlinerest..PurchaseOrders por
join exactonlinerest..PurchaseOrderLines ple
on ple.PurchaseOrderID = por.PurchaseOrderID
;
--
-- Maximum prijzen in een week waarin er inkopen geweest zijn
-- voor een artikel.
--
create or replace table pleweek@inmemorystorage
as
select itemcode
, por_week
, sun_in_week_date
, avg(quantity * netprice) netpricewavg
, avg(netprice) netpriceavg
, max(netprice) netpricemax
, min(netprice) netpricemin
, count(*) cnt
from purchaselines@inmemorystorage
group
by itemcode
, por_week
, sun_in_week_date
;
--
create or replace table purchaselinesmin@InMemoryStorage
as
select min(orderdate) orderdate
from purchaselines@inmemorystorage
;
--
-- Opzoektabel per week wanneer de laatste keer een artikel is ingekocht.
--
create or replace table plw@inmemorystorage
as
select clr.day_date
, plw.itemcode
, min(plw.por_week)
plw_por_week_min
, max(plw.sun_in_week_date)
plw_sun_in_week_date_min
from purchaselinesmin@InMemoryStorage pln
join CALENDAR@DataDictionary clr
--
-- Optimalisatie: kijk alleen vanaf de eerste besteldatum.
--
on clr.day_date >= pln.orderdate
and clr.day_in_week = 1
--
-- Optimalisatie: kijkt niet in de toekomst.
--
and clr.day_date <= add_months(trunc(sysdate), 1)
join pleweek@inmemorystorage plw
on plw.sun_in_week_date <= clr.day_date
group
by clr.day_date
, plw.itemcode
;
--
-- Bepaal per artikel wat de laatste bekende prijs is
-- per week. Als er meerdere prijzen zijn, dan wordt de hoogste
-- inkoopprijs genomen.
--
create or replace table approx_prices@inmemorystorage
as
select plw0.day_date
, plw0.itemcode
, plw0.plw_sun_in_week_date_min
, plw1.netpricemax
netpriceweek
from plw@inmemorystorage plw0
join pleweek@inmemorystorage plw1
on plw1.sun_in_week_date = plw0.plw_sun_in_week_date_min
and plw1.itemcode = plw0.itemcode
;
create or replace table sle@inmemorystorage
as
select sor.*
, sle.* prefix with 'sle_'
, coalesce(apeb.netpriceweek, apef.netpriceweek, itm.CostPriceStandard) * sle.quantity
sle_costpricequantity
label 'Kostprijs Artikelen'
, coalesce(por.suppliercode, sim.suppliercode)
por_suppliercode
, coalesce(por.suppliername, sim.supplierdescription)
por_suppliername
, por.ordernumber
por_ordernumber
, itm.code
sle_itemcode
label 'Artikelcode'
, itm.itemgroupcode
sle_itemgroupcode
, itm.costpricestandard
sle_costpricestandardcurrent
label 'Kostprijs Artikel Momenteel'
, apeb.netpriceweek
sle_costpriceapproximateb
label 'Kostprijs Artikel Historisch Terug'
, apef.netpriceweek
sle_costpriceapproximatef
label 'Kostprijs Artikel Historisch Vooruit'
, case
when apeb.netpriceweek is not null
then 'Vorige inkoop'
when apef.netpriceweek is not null
then 'Volgende inkoop'
else 'Standaard kostprijs'
end
herkomst
from sor@inmemorystorage sor
join SalesOrderLinesV2Incremental sle
on sle.orderid = sor.sor_orderid
join itm@inmemorystorage itm
on itm.id = sle.item
left
outer
join exactonlinerest..purchaseorders por
on por.purchaseorderid = sle.purchaseorder
-- Exclude: shipping
and itm.itemgroupcode != 'VK'
left
outer
join sim@inmemorystorage sim
on sim.item = sle.item
left
outer
join approx_prices@inmemorystorage apeb
on apeb.itemcode = itm.code
and apeb.day_date = sor.sor_orderdate - to_number(to_char(sor.sor_orderdate, 'D'))
-- Exclude: shipping
and itm.itemgroupcode != 'VK'
left
outer
join approx_prices@inmemorystorage apef
on apef.itemcode = itm.code
and apef.day_date = sor.sor_orderdate - to_number(to_char(sor.sor_orderdate, 'D')) + 7
-- Exclude: shipping
and itm.itemgroupcode != 'VK'
;
create or replace table sorcost@inmemorystorage
as
select sor_division
, sor_orderid
, sor_ordernumber
, sor_orderdate
, sum(sle_costpricequantity)
sor_costpricequantity
label 'Kostprijs Artikelen'
, min(por_suppliercode)
por_suppliercode
label 'Eerste Leverancier Code'
, min(por_suppliername)
por_suppliername
label 'Eerste Leverancier Naam'
, min(por_ordernumber)
por_ordernumber
label 'Eerste Leverancier Bestelnummer'
from sle@inmemorystorage
group
by sor_division
, sor_orderid
, sor_ordernumber
, sor_orderdate
;
create or replace table sorall@inmemorystorage
as
select cst.sor_costpricequantity
, cst.por_suppliercode
, cst.por_suppliername
, cst.por_ordernumber
, sor.sor_amountfcexclvat - cst.sor_costpricequantity
margin
label 'Bruto Marge'
, case
when sor.sor_amountfcexclvat != 0
then ( sor.sor_amountfcexclvat - cst.sor_costpricequantity )
/ sor.sor_amountfcexclvat
else 1
end
margin_percentage
label 'Bruto Marge %'
, sor.*
from sor@inmemorystorage sor
left
outer
join sorcost@inmemorystorage cst
on cst.sor_orderid = sor.sor_orderid
;
end;