Page Comparison

Change log:

Date:	Author:	Version:	Changes:	Completed	Ext.	Int.	Is in Core	Jira Ref.
06 January 2016	Jon Secher Kristensen	1.0	Doc. created	Yes	x		N/A

Municipality Data

The 118 Inquiry Service requires that all Telephone Subscriptions sent in the XML files to 118 contains contain Municipality Data. Municipality Numbers numbers and Street Numbers street numbers are retrieved from the CPR 's website and saved in the STREET_REGISTER table. When the 118 Export is processing, each address linked to a Telephone Subscription telephone subscription is looked up in the STREET_REGISTER table and if matched the Data there is a match, the data is saved in the XML file.

If Municipality Data and Street Number Data municipality data and street number data is missing from in the XML entities there is a high propability that the missing data is , this is most likely caused by a typotyping error.

To investigate find the reason for the missing data try to look for USERS entries with a missing match in STREET_REGISTER.

Code Block

language	sql

SELECT u.*  
 FROM SERVICE s, SUBSCRIPTION su, USERS u,ZIP_CODE z, SERVICE_OPTION so, PRODUCT_OPTION po, EXTRACT118_CONF ec
 WHERE s.SUBSCRIPTION_ID = su.ID
    AND su.OWNER_ID = u.ID
    AND su.ACCOUNT_ID = u.ACCOUNT_ID
    AND su.STATUS_ID = 2
    AND s.CODE = ec.CODE
    AND u.zip = z.zip
    AND s.id = so.service_id
    AND po.id = so.product_option_id
    AND po.option_key in (select option_key from EXTRACT118_CONF where is_option_key_field = 'Y')
    and not exists
    (select 1 from STREET_REGISTER sr where upper(sr.streetname) = upper(u.street) and sr.zipcode = u.zip);
    ;

The result should be the find those USERS which that would be grabbed for 118 Export where , but for whom the Street name and Zip code would not render a match in the Street Register table.

...

Below are examples of Street names and Zip code pairs that will cause missing Municipality Data in the XML files.

Example streetExample street	Example ZIPExample ZIP	Fix	Description
Kai LindbergsgadeKai Lindbergsgade	7730	Change street to 'Kai Lindbergs Gade'	Here the The STREET_REGISTER table will tell you that the street has been name was spelled wrongincorrectly, which will cause no match to be found in the STREET_REGISTER table.
Lautrupbjerg 12	2750	Change street to 'Lautrupbjerg' and put 45 in Street Number.	Here the The Street field in the USERS entry contains a Street street number which will cause no find match to be found in the STREET_REGISTER table.
Lautrupbjerg	2800	Change ZIP code to 2750	Here the The ZIP code field in the USERS entry contains the wrong an incorrect ZIP code for the street address, which will cause find no match to be found in the STREET_REGISTER table.

Versions Compared

Old Version 4

New Version 5

Key

Municipality Data