Import Final Mile Facilities

WG-145 - Getting issue details... STATUS

Final mile facilities are provided as a Excel file and should be imported to a WhiteGlove OrganisationRegistry database. The below is a spet-by-step instruction how to do this:

Create an intermediate SQL table (ensure the order of columns in Excel and the table is the same):

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FinalMileFacilityImport]') AND type in (N'U'))
DROP TABLE [dbo].[FinalMileFacilityImport]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[FinalMileFacilityImport](
	[CompanyCode] [nvarchar](100) NULL,
	[CompanyDescription] [nvarchar](100) NULL,
	[FinalMileFacilityCode] [nvarchar](50) NULL,
	[FinalMileFacilityDescription] [nvarchar](50) NULL,
	[Title] [nvarchar](100) NULL,
	[FirstName] [nvarchar](100) NULL,
	[LastName] [nvarchar](100) NULL,
	[Line1] [nvarchar](100) NULL,
	[Line2] [nvarchar](100) NULL,
	[Line3] [nvarchar](100) NULL,
	[City] [nvarchar](100) NULL,
	[Country] [nvarchar](100) NULL,
	[PostalZip] [nvarchar](100) NULL,
	[CountryCode] [nvarchar](100) NULL,
	[PhoneNumber] [nvarchar](100) NULL,
	[EmailAddress] [nvarchar](100) NULL,
) ON [PRIMARY]
GO

Go to Edit FinalMileFacilityImport table data mode in SSMS.
Copy data from the excel file an past it to the table.
1. Verify everything is copied well as multiline values may be pasted as a separate rows.

Run the following script:

BEGIN TRAN LOAD_LOCATIONS;

WITH organisations AS (
    SELECT TRIM(fm.CompanyCode) as CompanyCode,
           TRIM(fm.CompanyDescription) as CompanyDescription,
           ROW_NUMBER() OVER(PARTITION BY fm.CompanyCode order by CompanyCode) AS rank
      FROM FinalMileFacilityImport fm)

MERGE OrganisationRegistry.Organisations target
USING (SELECT * FROM organisations WHERE rank = 1) AS source
ON source.CompanyCode = target.ExternalCode and target.Name <> source.CompanyDescription
WHEN NOT MATCHED THEN
    INSERT (ExternalCode, Name, Role, City, Country, CreatedDate, LastModifiedDate, Code)  
    VALUES (source.CompanyCode, source.CompanyDescription, 'WHITE_GLOVE_PARTNER', 'N/A', 'N/A', GETUTCDATE(), GETUTCDATE(), source.CompanyCode)		
WHEN MATCHED THEN
	UPDATE SET
		Name = source.CompanyDescription,
		LastModifiedDate = GETUTCDATE();

WITH locations AS (
    SELECT
		TRIM(fm.FinalMileFacilityCode) as Code,  
        TRIM(fm.FinalMileFacilityDescription) as Name,
		TRIM(fm.Line1) as AddressLine1,
		TRIM(fm.Line2) as AddressLine2,
		TRIM(fm.Line3) as AddressLine3,
		TRIM(fm.City) as City,
		TRIM(PostalZip) as Postcode,
		c.CountryId as CountryId,
		o.OrganisationId as OrganisationId
      FROM FinalMileFacilityImport fm
	  LEFT JOIN OrganisationRegistry.Countries c ON TRIM(c.Code) = fm.CountryCode
	  LEFT JOIN OrganisationRegistry.Organisations o ON TRIM(o.ExternalCode) = fm.CompanyCode)

MERGE OrganisationRegistry.Locations target
USING (SELECT * FROM locations) as source
ON source.Code = target.ExternalCode and source.OrganisationId = target.OrganisationId
WHEN NOT MATCHED THEN
    INSERT (
		Code,
		ExternalCode,
		Name,
		Type,
		AddressLine1,
		AddressLine2,
		AddressLine3,
		City,
		Postcode,
		OrganisationId,
		CreatedDate,
		LastModifiedDate,
		CountryId)  
    VALUES (
		source.Code,
		source.Code,
		source.Name,
		'FINAL_MILE_FACILITY',
		source.AddressLine1,
		source.AddressLine2,
		source.AddressLine3,
		source.City,
		source.Postcode,
		source.OrganisationId,
		GETUTCDATE(),
		GETUTCDATE(),
		source.CountryId)		
WHEN MATCHED THEN
	UPDATE SET
		Name = source.Name,
		AddressLine1 = source.AddressLine1,
		AddressLine2 = source.AddressLine2,
		AddressLine3 = source.AddressLine3,
		City = source.City,
		Postcode = source.Postcode,
		LastModifiedDate = GETUTCDATE(),
		CountryId = source.CountryId;

COMMIT TRAN LOAD_LOCATIONS;

5. Run the script to export existing locations:

SELECT        l.LocationId, l.Code, l.ExternalCode, l.Name, l.Type, l.AddressLine1, l.AddressLine2, l.AddressLine3, l.City, l.Region, l.Postcode, o.Code AS Expr1, l.CreatedDate, l.LastModifiedDate, l.DisableDate, 
                         c.Name AS Expr2
FROM            OrganisationRegistry.Locations AS l LEFT OUTER JOIN
                         OrganisationRegistry.Organisations AS o ON o.OrganisationId = l.OrganisationId LEFT OUTER JOIN
                         OrganisationRegistry.Countries AS c ON c.CountryId = l.CountryId