Lex SNM1 Network Import

Overview

Lex will be sending their supplier files daily via SFTP to keep their network in sync with the network presented to them on the ViSN platform, these files act more like network updates. The 2 files are SNM1 and SNM2. The SNM2 file is a full file containing all the suppliers in the network, the SNM1 file is a delta file containing only the changes to the network. In this document, we will be discussing the SNM1 file.

download sample Lex SNM1-delta-ancillary-prices file.

Lex SNM1-delta-ancillary-prices file

Column Position	Lex Fields	ViSN Fields	Datatype	Validation	Max length	Mandatory	Default Value	Example
1	RecordType	IGNORE
2	RecordVersionNo	IGNORE
3	1LinkSiteReference	DI Group ID - DI Supplier ID	string	A-Z, 0-9 and all special characters (50 bc that’s 30 for Group ID + 20 for Supplier ID) ^[a-zA-Z0-9_-]+-[a-zA-Z0-9_-]+$	50	Y	Not Available
4	SchemeOperatorNetwork	IGNORE
5	ServiceCentreName	IGNORE
6	ExpectedAirConditioningCoolantPPL	Air Conditioning Coolant	string	0-9 and . (decimal number)	6	Y		4.5
7	ExpectedAntiFreezePPL	Anti-Freeze	string	0-9 and . (decimal number)	6	Y		4.5
8	ExpectedAutomaticTransmissionFluidPPL	Automatic Transmission Fluid	string	0-9 and . (decimal number)	6	Y		4.5
9	ExpectedAxleOilPPL	Axle Oil	string	0-9 and . (decimal number)	6	Y		4.5
10	ExpectedBrakeClutchFluidPPL	Brake Fluid	string	0-9 and . (decimal number)	6	Y		4.5
11	ExpectedGearboxOilMineralPPL	Gearbox Oil Mineral	string	0-9 and . (decimal number)	6	Y		4.5
12	ExpectedGearboxOilSyntheticPPL	Gearbox Oil Synthetic	string	0-9 and . (decimal number)	6	Y		4.5
13	ExpectedMineralOilPPL	Mineral Oil	string	0-9 and . (decimal number)	6	Y		4.5
14	ExpectedPowerSteeringFluidPPL	Power Steering Fluid	string	0-9 and . (decimal number)	6	Y		4.5
15	ExpectedSemiSyntheticOilPPL	Semi Synthetic1	string	0-9 and . (decimal number)	6	Y		4.5
16	ExpectedSuspensionFluidPPL	Suspension Fluid	string	0-9 and . (decimal number)	6	Y		4.5
17	ExpectedSyntheticOilPPL	Synthetic1	string	0-9 and . (decimal number)	6	Y		4.5
18	ServiceMaintenanceRepair	Service And Maintenance Repair	string	1 or 0		Y		1
19	MotTestStation	MOT	string	1 or 0		Y		1
20	Tyres	Tyres	string	1 or 0		Y		0
21	24HourCallOut	IGNORE
22	CourtesyCar	IGNORE
23	DeliveryAndCollection	IGNORE
24	BreakdownAssistance	Breakdown	string	1 or 0		Y		1
25	SupplierNotes	IGNORE
26	CollectionDeliveryPostCodeArea	IGNORE
27	WorkBookingIntroductionFee	IGNORE
28	InvoiceCommissionValue	Rebate Amount	string	Decimal Number (format should be 00.00)	6	N	0.00	10.25
29	InvoiceCommission%	Rebate Percentage	string	Decimal Number (format should be 00.00)	-	Y
30	1LinkSiteReference2	IGNORE
31	ExpectedLongLifeOilPPL	Long Life Oil	string	0-9 and . (decimal number)	6	Y		4.5
32	ExpectedHydraulicPPL	Hydraulic Fluid	string	0-9 and . (decimal number)	6	No		4.5
33	ExpectedRefrigeratorPPL	Refrigerator Coolant	string	0-9 and . (decimal number)	6	Y		4.5
34	ExpectedAdBluePPL	AdBlue	string	0-9 and . (decimal number)	6	No		4.5
35	ExpectedOtherFluidPPL	Other Fluid	string	0-9 and . (decimal number)	6	No		4.5
36	Inspections	IGNORE

Process of importing LEX SNM1 file

SNM1 import process is designed to ensure data consistency and integrity, especially in relation to the full network file, SNM2. Below is an overview of how the SNM1 import process works:

1. Scheduled Imports

Both the SNM2 and SNM1 files are processed through scheduled tasks. The SNM2 import runs first, as it contains the complete set of supplier data. After a predefined interval, the SNM1 import is executed. The file names are renamed to SNM1-delta-ancillary-prices.csv and SNM2-full-labour-rates.csv respectively.

2. Validation Against Existing Data

• Each row in the SNM1 file contains a key field, 1LinkSiteReference, which is a composite of the DI Group ID and DI Supplier ID in the format:
  DI Group ID - DI Supplier ID.
• During the SNM1 import, the system checks whether the supplier identified by this composite key exists in the database.

3. Dependency on SNM2 Updates

To ensure accuracy, SNM1 updates are only applied to suppliers that were successfully imported or updated as part of the SNM2 import on the same day. This safeguard ensures that delta updates do not inadvertently introduce discrepancies.

4. Field Updates

For each validated and eligible supplier, only the fields explicitly mentioned below from the SNM1 file are updated.

Column Position	Lex Fields name	Map to ViSN Fields (Description)	Data level
6	ExpectedAirConditioningCoolantPPL	Air Conditioning Coolant	Lease-Supplier
7	ExpectedAntiFreezePPL	Anti Freeze	Lease-Supplier
8	ExpectedAutomaticTransmissionFluidPPL	Automatic Transmission Fluid	Lease-Supplier
9	ExpectedAxleOilPPL	Axle Oil	Lease-Supplier
10	ExpectedBrakeClutchFluidPPL	Brake Fluid	Lease-Supplier
11	ExpectedGearboxOilMineralPPL	Gearbox Oil Mineral	Lease-Supplier
12	ExpectedGearboxOilSyntheticPPL	Gearbox Oil Synthetic	Lease-Supplier
13	ExpectedMineralOilPPL	Mineral Oil	Lease-Supplier
14	ExpectedPowerSteeringFluidPPL	Power Steering Fluid	Lease-Supplier
15	ExpectedSemiSyntheticOilPPL	Semi Synthetic1	Lease-Supplier
16	ExpectedSuspensionFluidPPL	Suspension Fluid	Lease-Supplier
17	ExpectedSyntheticOilPPL	Synthetic1	Lease-Supplier
18	ServiceMaintenanceRepair	Service And Maintenance Repair	Lease-Supplier
19	MotTestStation	MOT	Lease-Supplier
20	Tyres	Tyres	Lease-Supplier
24	BreakdownAssistance	Breakdown	Lease-Supplier
28	InvoiceCommissionValue	Rebate Amount	Lease-Supplier
29	InvoiceCommission%	Rebate Percentage	Lease-Supplier
31	ExpectedLongLifeOilPPL	Long Life Oil	Lease-Supplier
32	ExpectedHydraulicPPL	Hydraulic Fluid	Lease-Supplier
33	ExpectedRefrigeratorPPL	Refrigerator Coolant	Lease-Supplier
34	ExpectedAdBluePPL	AdBlue	Lease-Supplier
35	ExpectedOtherFluidPPL	Other Fluid	Lease-Supplier

This process ensures that delta updates from SNM1 files are applied in a controlled and reliable manner, aligning with the most recent supplier data provided in the SNM2 file.

Preparation

1. Create a new branch with Jira task number in the branch name

2. Delete all the files within the importFiles folder to prevent accidental import of incorrect files

3. Create a folder with the current sprint number

4. Copy the SNM1 file that needs to be imported along with SNM2 file into the folder

5. Go to package.json , find the line validate:lex-snm1-network-import

6. Modify --file with the relative path of the file and modify --secondaryFile with the relative path of the SNM2 file

7. Save the package.json

Validating locally

1. in a terminal run npm run build

2. in a terminal run npm run validate:lex-snm1-network-import

3. If there are no errors, push the code

Validating in workflow

1. Go to Action of Import-manger

2. Find <Environment> - on-demand-import

3. Select the branch that was created for this import

4. Enter script name as validate:lex-snm1-network-import

5. Leave Overrides empty

Importing the file

1. If the validation has passed

2. Go to Action of Import-manger

3. Find <Environment> - on-demand-import

4. Select the branch that was created for this import

5. Enter script name as validate:lex-snm1-network-import

6. Enter --persist in Overrides field

Importing and Validating the file via cloud run

Creating Schedulers for validating the Lex SNM1 delta file

1. Go to Cloud Scheduler and select the CREATE JOB

Setup Required

A Scheduler should be created cloud scheduler with the following parameters

• Name: lex-snm1-network-validation
• Region: europe-west2
• Frequency: 0 0 31 12 1
• TimeZone: GMT London
• Target type: HTTP
• URL: https://import-manager-36r2bu4izq-uc.a.run.app/lex-snm1-network-import
• HTTP method: POST
• Auth header: select OIDC token
• Service account: cloud-run-import-customer-contract
• body: a JSON object including the following parameters

JSON object for lex-snm1-network-validation scheduler

{
  "persist": "false",
  "bucket": "biddirect-2.appspot.com",
  "appDirectory": "/usr/src/app/",
  "importPath": "imports/fileDrop/",
  "importFileDropPath": "imports/fileDrop/",
  "importFileName": "SNM1-delta-ancillary-prices.csv",
  "secondaryImportFileName": "SNM2-full-labour-rates.csv",
  "email": "mailgun@digitalinnk.com",
  "fatalErrorEmailToAddress": "mailgun@digitalinnk.com",
  "leaseId": "QJKgdjS4I02sEia4yTne"
}

Create scheduler for validating Lex SNM1 delta file

Creating Schedulers for importing the Lex SNM1 delta file

1. Go to Cloud Scheduler and select the CREATE JOB

Setup Required

A Scheduler should be created cloud scheduler with the following parameters

• Name: lex-snm1-network-import
• Region: europe-west2
• Frequency: 0 0 31 12 1
• TimeZone: GMT London
• Target type: HTTP
• URL: https://import-manager-36r2bu4izq-uc.a.run.app/lex-snm1-network-import
• HTTP method: POST
• Auth header: select OIDC token
• Service account: cloud-run-import-customer-contract
• body: a JSON object including the following parameters

JSON object for lex-snm1-network-import scheduler

{
  "persist": "true",
  "bucket": "biddirect-2.appspot.com",
  "appDirectory": "/usr/src/app/",
  "importPath": "imports/fileDrop/",
  "importFileDropPath": "imports/fileDrop/",
  "importFileName": "SNM1-delta-ancillary-prices.csv",
  "secondaryImportFileName": "SNM2-full-labour-rates.csv",
  "email": "mailgun@digitalinnk.com",
  "fatalErrorEmailToAddress": "mailgun@digitalinnk.com",
  "leaseId": "QJKgdjS4I02sEia4yTne"
}

Create scheduler for validating Lex SNM1 delta file

Preparation:

1. Bucket name is biddirect-2.appspot.com and it will vary for higher envs and the file path should be imports/fileDrop, where you can drop the 2 CSV files named "SNM1-delta-ancillary-prices.csv" and "SNM2-full-labour-rates.csv". This action will place the file in the specified location within the designated bucket.

Validation:

1. Go to Cloud Scheduler
2. Run lex-snm1-network-validation job (job name could be different on other env)

logs in cloud

Importing:

1. Go to Cloud Scheduler
2. Run lex-snm1-network-import job (job name could be different on other env)

logs in cloud

Testing

1. For respective DI Group ID - DI Supplier ID service units, the fields mentioned in the table under "4. Field Updates" should be updated with the values from the SNM1 file.

Status: Approved
Category: Protected
Authored By: Sohan on Nov 21, 2024