Other Integrations: 2 - 12 Tyre

2 - 12 Tyre

SELECT t1.*
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'T1' with appropriate VMRS Code for Single Tyre
AND t2.vmrscode = 'T1'-- Replace 'T' with appropriate postingclasscode for TyreAND t3.postingclasscode = 'T' AND producttype BETWEEN 2 AND 12
AND t5.product_code IS NULL;

13-26 = FastFit Products

These product codes need to be added to fmvmrscodes if not there already.

SELECT @nextid := uniqueid - 1 FROM gbuniqueids WHERE tablename = 'fmvmrscodes';INSERT INTO fmvmrscodes(vmrscodeid, vmrscode, description, invoicetype, quantity, unitcost, actioncode, externalcode)
VALUES
(@nextid := @nextid + 1, 'AIRVENT', 'Air and Ventilation', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'BATTRIES', 'Batteries', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'BRAKES', 'Brakes', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'COOLSYS', 'Cooling system', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'ENGINE', 'Engine', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'EXHAUSTS', 'Exhausts', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'FUELSYS', 'Fuel System', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'GLOBES', 'Globes', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'SUSPNSON', 'Suspension', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'TRNSMSN', 'Transmission', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'WINDSCRN', 'Windscreens', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'WIPERS', 'Wipers', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'TUBES', 'Tubes', 'maintenance', 0, 0.00, *, *),
(@nextid := @nextid + 1, 'SRVCHRG', 'Service Charges', 'maintenance', 0, 0.00, *, *);
REPLACE INTO gbuniqueids VALUES('fmvmrscodes', @next_id + 1);

Folloiwng query gives you a list of codes that need to be added into fminterfacevmrs_mappings. Repeat this with for Product Types 14-26.

SELECT t1.*
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'AIRVENT' with appropriate VMRS Code for Air and Ventilation if a VMRS code already exists with different code
AND t2.vmrscode = 'AIRVENT'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 13
AND t5.product_code IS NULL;

6. Run following query in a Script Tab in MySql Query Browser. This query assumes following exist in the DB.

SELECT @interfacevmrsmappingid := uniqueid - 1 FROM gbuniqueids WHERE tablename = 'fminterfacevmrsmappings';

1 = Tyre Service

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
AND t2.vmrscode = 'SUND006'-- Replace 'S' with appropriate postingclasscode for ServiceAND t3.postingclasscode = 'S' AND producttype = 1
AND t5.product_code IS NULL;

2 - 12 Tyre

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'T1' with appropriate VMRS Code for Single Tyre
AND t2.vmrscode = 'T1'-- Replace 'T' with appropriate postingclasscode for TyreAND t3.postingclasscode = 'T' AND producttype BETWEEN 2 AND 12
AND t5.product_code IS NULL;

13-26 = FastFit Products (Tyre and Services)

13-26 = FastFit Products (Misc Maintenance Items)

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'AIRVENT' with appropriate VMRS Code for Air and Ventilation if a VMRS code already exists with different code
AND t2.vmrscode = 'AIRVENT'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 13
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'BATTRIES' with appropriate VMRS Code for Batteries if a VMRS code already exists with different code
AND t2.vmrscode = 'BATTRIES'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 14
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'BRAKES' with appropriate VMRS Code for Brakes if a VMRS code already exists with different code
AND t2.vmrscode = 'BRAKES'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 15
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'COOLSYS' with appropriate VMRS Code for Cooling system if a VMRS code already exists with different code
AND t2.vmrscode = 'COOLSYS'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 16
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'ENGINE' with appropriate VMRS Code for Engine if a VMRS code already exists with different code
AND t2.vmrscode = 'ENGINE'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 17
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'EXHAUSTS' with appropriate VMRS Code for Exhausts if a VMRS code already exists with different code
AND t2.vmrscode = 'EXHAUSTS'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 18
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'FUELSYS' with appropriate VMRS Code for Fuel System if a VMRS code already exists with different code
AND t2.vmrscode = 'FUELSYS'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 19
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'GLOBES' with appropriate VMRS Code for Globes if a VMRS code already exists with different code
AND t2.vmrscode = 'GLOBES'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 20
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'SUSPNSON' with appropriate VMRS Code for Suspension if a VMRS code already exists with different code
AND t2.vmrscode = 'SUSPNSON'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 21
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'TRNSMSN' with appropriate VMRS Code for Transmission if a VMRS code already exists with different code
AND t2.vmrscode = 'TRNSMSN'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 22
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'WINDSCRN' with appropriate VMRS Code for Windscreens if a VMRS code already exists with different code
AND t2.vmrscode = 'WINDSCRN'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 23
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'WIPERS' with appropriate VMRS Code for Wipers if a VMRS code already exists with different code
AND t2.vmrscode = 'WIPERS'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 24
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'TUBES' with appropriate VMRS Code for Wipers if a VMRS code already exists with different code
AND t2.vmrscode = 'TUBES'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 25
AND t5.product_code IS NULL;

INSERT INTO fminterfacevmrsmappingsSELECT @interfacevmrsmappingid := @interfacevmrsmappingid + 1, t4.interfaceid, t1.1linkproductcode, t3.postingclassid, t2.vmrscodeid, t1.description,'no','active'
FROM 1linkproductcataloguemappingtmp t1, fmvmrscodes t2, glpostingclasses t3, fminterfaces t4LEFT JOIN fminterfacevmrsmappings AS t5 ON t5.productcode = t1.1linkproductcode AND t5.interfaceid = t4.interfaceidWHERE t4.interfacecode = '1link'
-- Replace 'SRVCHRG' with appropriate VMRS Code for Service Charges if a VMRS code already exists with different code
AND t2.vmrscode = 'SRVCHRG'-- Replace 'M' with appropriate postingclasscode for MaintenanceAND t3.postingclasscode = 'M' AND producttype = 26
AND t5.product_code IS NULL;

UPDATE gbuniqueids SET uniqueid = @interfacevmrsmappingid + 1 WHERE tablename = 'fminterfacevmrsmappings';

7. Drop temporary table created in #2.

DROP TABLE 1linkproductcataloguemappingtmp;
```