TDC 118 Export - Missing Municipality Data

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 contain Municipality Data. Municipality numbers and street numbers are retrieved from the CPR website and saved in the STREET_REGISTER table. When the 118 Export is processing, each address linked to a telephone subscription is looked up in the STREET_REGISTER table and if there is a match, the data is saved in the XML file.

If municipality data and street number data is missing in the XML entities, this is most likely caused by a typing error.

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

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 find those USERS that would be grabbed for 118 Export, but for whom the Street name and Zip code would not render a match in the Street Register table.

Examples

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

Example street	Example ZIP	Fix	Description
Kai Lindbergsgade	7730	Change street to 'Kai Lindbergs Gade'	The STREET_REGISTER table will tell you that the street name was spelled incorrectly which will find no match in the STREET_REGISTER table.
Lautrupbjerg 12	2750	Separate street name and number and put '12' into the Street Number column.	The Street field in the USERS entry contains a street number which will find no match in the STREET_REGISTER table.
Lautrupbjerg	2800	Change ZIP code to 2750	The ZIP code field in the USERS entry contains an incorrect ZIP code for the street address which will find no match in the STREET_REGISTER table.