DAP Datamodel update: Tables and Columns renaming and additions

A heads up from the DAP team:
The following columns and tables will be renamed on Wednesday June 21st in order to improve consistency and to fix typos.
Later in the post you'll also find a summary of other recent (non-breaking) additions to the DAP datamodel.

Columns renamed:

f_planneddelivery.apartement_nr -> apartment_nr

f_deviation.appartment_number -> apartment_nr
f_deviation.is_delay -> ‘is_delayed’ to match f_routedeviation.is_delayed

shop.organization_number -> organisation_number

f_plannedbooking.innvoice_circulation -> invoiced_circulation

all instances of 'employement_start_date' -> 'employment_start_date'

all instances of 'employement_end_date' -> 'employment_end_date'

f_retailerinvoicinginformation.return_quantity -> returned_quantity

f_retailerinvoicinginformation.product_category -> product_category_id

f_retailerReturnCreditedQuantity.return and .credited -> .returned_quantity and .credited_quantity

Tables renamed:
invoice -> retailerinvoice

invoiceline -> retailerinvoiceline

New Table f_ProductionSystemandPackaging deployment in progress (ETA today or tomorrow):

• This table holds data about newspaper and magazine productions, including their packaging and transport systems, such as transport network/routes/drop-off and pick-up locations, including B2B parcels.

• It can be used to identify each packaging production and the parameters involved in the production.

• One notable feature of this table is that it contains information about the main vehicle and branch vehicle (hovedbil and grenbil) transportation systems.

Other recent additions:
Added back sorting columns:
Interested in seeing what your route sequence looks like? We are pleased to introduce an updated version of the sorting columns that will provide you with the ability to analyze your routes and delivery points. With these enhancements, you can gain valuable insights into the order and arrangement of your routes, enabling better understanding and optimization of your delivery operations. These columns are only correct / updated for historical dates.

• DAP.module.route_module_sorting represents the specific order sequence of modules within a route.

• DAP.distributionpoint.delivery_point_sorting represents the specific order sequence of delivery points within a module.

New columns introduced to SalaryDraft:
Several of you have reported some confusion around the SalaryDraft table. In order to address the issues arising from having both manual salary drafts (for status tracking) and salary reconciliation drafts (containing final results after processing) in one table, we have made updates to the data model. A new column called “line_type” has been introduced to differentiate between the different drafts.

• The “line_type” column determines whether the data/entry represents a final result or not. Specifically, a value of 1 indicates a final result entry after calculation (reskontro line), while a value of 0 signifies a manual entry that is not the final result (lonnkladd line).

• The “manual_posting” column (BaReskontro.Manuellpostering) indicates the source of the entry. A value of 1 indicates that the entry originates from a manual draft, while a value of 0 indicates it is not from a manual draft. If there is no value (NULL), the entry represents an unprocessed draft.

Added return_code in f_planneddelivery

• By including the return_code column in the f_planneddelivery table, we aim to make it independent of other tables when tracking return packages

• This column exists now, but backfilling is in progress, so expect historical data to arrive soon

Let us know how those additions generate business value for your organisation!

Eirik Lyngvi
Head of Analytics