Devel /

Manual Import of Customers

Importing Customer Data

For the purposes of this example it is assumed you are working on the test company where the company table prefix is 0_, you can change it to apply to any other company by changeing the sql to refer to the correct company no.

This process has been split into 5 steps

  1. Data Security
  2. Data Preparation
  3. Complete and Cleanse the Data
  4. Customer Data Import
  5. Update 4 DB Tables 
 Any support regarding this method of importing customer data can be addressed to the author apmuthu? December 09, 2014, at 11:38 AM

First Step - Data Security

If the data loaded is not exactly as you want it you will need to restore the DB to the current position, change the data and reload.

There are 2 ways to resore the data to the current position, either take a back up or use sql to remove the new records.

Back Up your DB

Use standard FA funtionality to take a back up.

Use SQL TO Remove New Records

If you are using this method you need to find Out The Number of Records Already Stored in the DB by running this SQL before you do anything else 

SET @person_id:=(SELECT MAX(id) FROM `0_crm_persons`);
SET @branch_id:=(SELECT MAX(branch_code) FROM `0_cust_branch`);
SET @debtor_id:=(SELECT MAX(debtor_no) FROM `0_debtors_master`);
SET @contact_id:=(SELECT MAX(id) FROM `0_crm_contacts`);
SELECT @person_id, @branch_id, @debtor_id, @contact_id;

Note down the values of the above MySQL variables, NULL = 0, if you have an empty Db they will all be Null/zero but if you have already entered some customers they may be say:-

@person_id = 4 @branch_id = 7 @debtor_id = 12 @contact_id = 18

If you need to restore and re-import your customers the SQL to remove your imported customer data is at the end of this wiki entry, you will need the numbers noted above.

Second Step - Data Preparation

Prepare an Excel sheet containing your Customer Data with the following columns:

  1. CustomerID (Your Reference from the old accounting system, unique)
  2. CustName (<= 60 characters)
  3. BranchName (<= 60 characters)
  4. Contact
  5. ShippingAddress
  6. BillingAddress
  7. phone1 (<= 30 characters)
  8. phone2 (<= 30 characters)
  9. fax (<= 30 characters)
  10. email (<= 100 characters)
  11. sales_account (Leave blank if it can be taken from the Items)
  12. payment_terms
  13. credit_limit
  14. TaxNum (<= 55 characters GST / VAT / Tax Exemption Code / Number)
  15. TaxExempt (0 = Taxed, 1 = Tax Exempt)
  16. Pricing (1=Retail, 2=Wholesale)
  17. NeedPO (0 = Direct Invoicing, 1 = Need Purchase Order)
  18. IsMB (0 = Individual Customer, 1 = Multi-Branch Customer, all Multi-Branch Customers must have the same CustName)
  19. debtor_no (leave blank, will be completed as part of next step)
  20. debtor_ref (<= 30 characters, unique nickname for each company which is unique across all companies - no spaces, single and doublequotes, ampersand and other symbols, but hyphens and underscores to be used with discretion and only if very necessary.)
  21. branch_code (leave blank, to be completed as part of next step)
  22. branch_ref (<= 30 characters, unique nickname for each multi branch customer, ie., where IsMB=1, - no spaces etc, must be unique globally across all branches of all companies.)

Payment Terms

To set the correct payment term for each customer check the values of in the terms_indicator field field in the #_payment_terms table. Set the value of payment terms to the appropriate value.

You can see the text values at

FA => Setup => Miscellaneous => Payment Terms

but to get the terms_indicator field you have to check the values held in the db.

Pricing

To set the correct Pricing for each customer check the values of in the id field in the #_sales_types table. Set the value of payment terms to the appropriate value.

You can see the text values at

FA => Sales => Maintenance => Sales Types

but to get the id you have to check the values held in the db.

The default sales types available in FA are

1=Retail 2=Wholesale

Other Customer Data

When you try to import your data some fields in your current system may have equivalents in NotrinosERP, these can be imported into the notes field in FA.

The NeedPO is already configure to do this.

Say you also want to import UPS number, add 1 more columns, UPS#, to the excel spreadsheet and add 1 extra fields to the cust_list table below. 

   `UPSCollect` VARCHAR(30) NOT NULL DEFAULT '',

Then amend the sql where it says 

  , IF(NeedPO, 'Need PO', ' ') AS notes

 to 

  , TRIM(CONCAT(IF(LENGTH(UPSCollect)>0,CONCAT('UPS#', UPSCollect,' ', ''), IF(NeedPO, 'Need PO', ' ')))) AS notes

Third Step - Complete and Cleanse the Data

  • All CustName field must be the same for all branches of the same customer
  • Then sort the sheet by IsMB and then CustName
  • Ensure the values used in the debtor_ref and branch_ref columns do not already exist in the DB.
  • Fill in every cell in the debtor_no column starting from the top. The initial cell should be set to one more than the @person_id or one more than the @branch_id which ever is higher as noted in the First Step, this number should increment by 1 each time the CustName field changes.
  • Fill in the branch_code incrementing sequentially starting from one more than the last debtor_no generated in the last step. This number should increment by 1 each time the debtor_ref field changes if there is one.
    • Alternatively, if the debtor_ref field has been filled in correctly to be the same for each branch of the same customer, then the debtor_no can be generated after importing into mysql as stated later.
  • Clean up the data by
    • replacing all double spaces with single spaces in VARCHAR fields
    • stripping out single and double quotes, comma, apmersand, period, brackets and other unacceptable symbols from the debtor_ref, branch_ref, phone, phone2, fax, TaxNum fields
    • make sure that the email field has vaild email addresses
    • Ensure that numeric field are all positive
    • Ensure Pricing field restricted to either 1 or 2
    • Check TaxExempt, NeedPO and IsMB fields are boolean integer values of either 0 and 1

Fourth Step - Customer Data Import

Stage 1

  • Run this SQL to create an import table called cust_list. 
CREATE TABLE `cust_list` (
  `CustomerID` INT(11) NOT NULL AUTO_INCREMENT,
  `CustName` VARCHAR(60) NOT NULL DEFAULT '',
  `BranchName` VARCHAR(60) NOT NULL DEFAULT '',
  `Contact` VARCHAR(60) NOT NULL DEFAULT '',
  `ShippingAddress` TINYTEXT NOT NULL,
  `BillingAddress` TINYTEXT NOT NULL,
  `phone` VARCHAR(30) NOT NULL DEFAULT '',
  `phone2` VARCHAR(30) NOT NULL DEFAULT '',
  `fax` VARCHAR(30) NOT NULL DEFAULT '',
  `email` VARCHAR(100) NOT NULL DEFAULT '',
  `sales_account`  VARCHAR(15) NOT NULL DEFAULT '',
  `payment_terms` INT(11) NOT NULL DEFAULT '1',
  `credit_limit` FLOAT NOT NULL DEFAULT '0',
  `TaxNum` VARCHAR(55) NOT NULL DEFAULT '',
  `TaxExempt`  TINYINT(1) NOT NULL DEFAULT '0',
  `Pricing` INT(11) NOT NULL DEFAULT '1',
  `NeedPO` TINYINT(1) NOT NULL DEFAULT '0',
  `IsMB` TINYINT(1) NOT NULL DEFAULT '0',
  `debtor_no` INT(11) DEFAULT NULL,
  `debtor_ref` VARCHAR(30) DEFAULT NULL,
  `branch_code` INT(11) DEFAULT NULL,
  `branch_ref` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`CustomerID`)
) ENGINE=INNODB;

Stage 2

  • Now import the data in your csv file into the DB.
  • Customer and Branch Code autogeneration: If the debtor_no and / or branch_code have not been prepared, do so with the following (assuming that there were 3 customers and 6 branches already entered: 
SET @debtor_no:=3;
SET @branch_code:=6;
SET @debtor_ref:='';
UPDATE cust_list
    SET debtor_no  = IF(debtor_ref <> @debtor_ref, (@debtor_no:=@debtor_no+1), @debtor_no), 
        debtor_ref = IF(debtor_ref <> @debtor_ref, @debtor_ref:=debtor_ref, debtor_ref),
        branch_code=(@branch_code:=@branch_code+1)
    ORDER BY IsMB, debtor_ref, branch_ref;
SELECT * FROM cust_list ORDER BY IsMB, debtor_ref, branch_ref;

If you use PHPMyAdmin and the Import option then a couple of tips

  • Use CSV or CSV using Load Data, remember there is an option to skip rows thus enabling you to have a title row in the csv file.
  • Make sure the field separator is set to a comma.
  • The Lines terminated with: should be set to \n

Fifth Step - Update 4 DB Tables

The SQL in this section loads the customer using the data in the cust_list table, it populates 4 tables.

Table 1 - Importing Data to the #_crm_persons table

  • Each Individual Customer has one branch (itself)
  • Each Multi Branch Customer has one branch (iself as headquarters) and one branch for each of it's branch entities.

Table 2 - Importing Data to the #_debtors_master table

  • Individual Customers must have one debtor record each
  • Ihe Headquarters alone of Multi Branch Customers must have one debtor record each

Table 3 - Importing Data to the #_cust_branch table

  • Each Customer (Individual and Multi Branch Headquarters) will have one branch record with debtor_no / debtor_ref / CustName data
  • Each branch of the Multi Branch customers will have one branch record with branch_code / branch_ref / BranchName data

Table 4 - Importing Data to the #_contacts table

  • Each Customer (Individual and Multi Branch Headquarters) will have one customer and one cust_branch contact record for each imported into the debtors_master table
  • Each Multi Branch Customer's non HQ branch will have one cust_branch contact record
  • In the FA Web UI, the individual contacts can be edited for their Contact Names or alter the earlier (persons) sql to directly put in the Contact into the contact_name field in the #_crm_persons table

Run The Following SQL:

NOTE: If you get the error message

  1. 1062 - Duplicate entry '0' for key 'debtor_ref'

it means the field 'debtor_ref' is in a non-standard position in the table and the sql will need to be adjusted to reflect your field sequence. A sample has been added at the end of this wiki. 

# Importing Persons into the '''#_crm_persons''' table
INSERT INTO `0_crm_persons` 
SELECT 
    `debtor_no` AS `id`
  , `debtor_ref` AS `ref`
  , IF(LENGTH(`Contact`) > 0, `Contact`, `CustName`) AS `name`
  , '' AS `name2`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `phone`
  , `phone2`
  , `fax`
  , `email`
  , '' AS lang
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0  AS inactive
FROM `cust_list` WHERE NOT IsMB

UNION ALL

(SELECT 
    `debtor_no` AS `id`
  , `debtor_ref` AS `ref`
  , IF(LENGTH(`Contact`) > 0, `Contact`, `CustName`) AS `name`
  , '' AS `name2`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `phone`
  , `phone2`
  , `fax`
  , `email`
  , '' AS lang
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0  AS inactive
FROM `cust_list` WHERE IsMB GROUP BY debtor_no)

UNION ALL

SELECT 
    `branch_code` AS `id`
  , `branch_ref` AS `ref`
  , IF(LENGTH(`Contact`) > 0, `Contact`, `BranchName`) AS `name`
  , '' AS `name2`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `phone`
  , `phone2`
  , `fax`
  , `email`
  , '' AS lang
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0  AS inactive
FROM `cust_list` WHERE IsMB;

#Importing into '''#_debtors_master''' table

SET @currency=(SELECT MID(`value`,1,3) AS currency FROM `0_sys_prefs` WHERE category='setup.company' AND `name`='curr_default');
INSERT INTO `0_debtors_master`
(SELECT 
    `debtor_no`
  , `CustName` AS `name`
  , `debtor_ref`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `TaxNum` AS `tax_id`
  , @currency AS `curr_code`
  , `Pricing` AS `sales_type`
  , 0 AS `dimension_id`
  , 0 AS `dimension2_id`
  , 1 AS `credit_status`
  , `payment_terms`
  , 0 AS `discount`
  , 0 AS `pymt_discount`
  , `credit_limit`
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0 AS inactive
FROM cust_list WHERE NOT IsMB)

UNION ALL

(SELECT 
    `debtor_no`
  , `CustName` AS `name`
  , `debtor_ref`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `TaxNum` AS `tax_id`
  , @currency AS `curr_code`
  , `Pricing` AS `sales_type`
  , 0 AS `dimension_id`
  , 0 AS `dimension2_id`
  , 1 AS `credit_status`
  , `payment_terms`
  , 0 AS `discount`
  , 0 AS `pymt_discount`
  , `credit_limit`
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0 AS inactive
FROM cust_list WHERE IsMB GROUP BY debtor_no);

# Import the branches into '''#_cust_branch''' table

SET @salesdiscact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='default_sales_discount_act');
SET @debtorsact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='debtors_act');
SET @paymentdiscact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='default_prompt_payment_act');

INSERT INTO `0_cust_branch`
(SELECT 
    `debtor_no` AS `branch_code`
  , `debtor_no`
  , `CustName` AS `br_name`
  , `debtor_ref` AS `branch_ref`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `br_address`
  , 1 AS `area`
  , 1 AS `salesman`
  , `Contact` AS `contact_name`
  , 'DEF' AS `default_location`
  , `TaxExempt`+1 AS `tax_group_id`
  , `sales_account`
  , @salesdiscact AS `sales_discount_account`
  , @debtorsact AS receivables_account
  , @paymentdiscact AS `payment_discount_account`
  , 1 AS `default_ship_via`
  , 0 AS `disable_trans`
  , REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), "  ", " ") AS `br_post_address`
  , 0 AS `group_no`
  , '' AS `notes`
  , 0 AS `inactive`
FROM `cust_list` WHERE NOT `IsMB`)

UNION ALL

(SELECT 
    `debtor_no` AS `branch_code`
  , `debtor_no`
  , `CustName` AS `br_name`
  , `debtor_ref` AS `branch_ref`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `br_address`
  , 1 AS `area`
  , 1 AS `salesman`
  , `Contact` AS `contact_name`
  , 'DEF' AS `default_location`
  , `TaxExempt`+1 AS `tax_group_id`
  , `sales_account`
  , @salesdiscact AS `sales_discount_account`
  , @debtorsact AS receivables_account
  , @paymentdiscact AS `payment_discount_account`
  , 1 AS `default_ship_via`
  , 0 AS `disable_trans`
  , REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), "  ", " ") AS `br_post_address`
  , 0 AS `group_no`
  , '' AS `notes`
  , 0 AS `inactive`
FROM `cust_list` WHERE `IsMB` GROUP BY `debtor_no`)

UNION ALL

(SELECT 
    `branch_code`
  , `debtor_no`
  , `br_name`
  , `branch_ref`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `br_address`
  , 1 AS `area`
  , 1 AS `salesman`
  , `Contact` AS `contact_name`
  , 'DEF' AS `default_location`
  , `TaxExempt`+1 AS `tax_group_id`
  , `sales_account`
  , @salesdiscact AS `sales_discount_account`
  , @debtorsact AS receivables_account
  , @paymentdiscact AS `payment_discount_account`
  , 1 AS `default_ship_via`
  , 0 AS `disable_trans`
  , REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), "  ", " ") AS `br_post_address`
  , 0 AS `group_no`
  , '' AS `notes`
  , 0 AS `inactive`
FROM `cust_list` WHERE `IsMB`);

# Import the persons mappings into '''#_contacts''' table

INSERT INTO `0_crm_contacts`
SELECT * FROM (
SELECT 
    NULL AS id
  , `debtor_no` AS person_id
  , 'customer' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE `IsMB`

UNION ALL 

SELECT 
    NULL AS id
  , `debtor_no` AS person_id
  , 'cust_branch' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE `IsMB`

UNION ALL 

(SELECT 
    NULL AS id
  , `debtor_no` AS person_id
  , 'customer' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB` GROUP BY debtor_no)


UNION ALL

(SELECT 
    NULL AS id
  , `debtor_no` AS person_id
  , 'cust_branch' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB` GROUP BY debtor_no)


UNION ALL

SELECT 
    NULL AS id
  , `branch_code` AS person_id
  , 'cust_branch' AS `type`
  , 'general' AS `action`
  , `branch_code` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB`
) a ORDER BY entity_id, person_id, `type` DESC;

Conclusion

  • If all the above went well, the data has been imported successfully and matches the result you would have got by manually entering the customers one by one using the NotrinosERP GUI, but if nor see below how to remove and re-try.

How To Remove Your Load Data

This will enable you to try several imports.

In the first step you noted 4 values, substitute them in the first 4 lines of the sql below and run them: 

SET @person_id:=0;
SET @branch_id:=0;
SET @debtor_id:=0;
SET @contact_id:=0;

DELETE b.* FROM 0_crm_contacts a LEFT JOIN 0_crm_persons b ON (a.person_id = b.id) 
	WHERE a.`type` IN ('customer', 'cust_branch') AND a.id > @contact_id AND b.id > @person_id;
ALTER TABLE 0_crm_persons AUTO_INCREMENT=0;
DELETE FROM 0_cust_branch WHERE branch_code > @branch_id;
ALTER TABLE 0_cust_branch AUTO_INCREMENT=0;
DELETE FROM 0_debtors_master WHERE debtor_no > @debtor_id;
ALTER TABLE 0_debtors_master AUTO_INCREMENT=0;
DELETE FROM 0_crm_contacts WHERE id > @contact_id AND `type` IN ('customer', 'cust_branch');
ALTER TABLE 0_crm_contacts AUTO_INCREMENT=0;
  • Now you can correct the data in your csv file and reload it.
  • If the AUTO_INCREMENT=0 above does not work try AUTO_INCREMENT=1 or hardcode the actual value.

Sample SQL Where DB has Non-Standard Field Sequence

INSERT INTO `0_crm_persons`
SELECT
    `debtor_no` AS `id`
  , `debtor_ref` AS `ref`
  , IF(LENGTH(`Contact`) > 0, `Contact`, `CustName`) AS `name`
  , '' AS `name2`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `phone`
  , `phone2`
  , `fax`
  , `email`
  , '' AS lang
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0  AS inactive
FROM `cust_list` WHERE NOT IsMB

UNION ALL

(SELECT
    `debtor_no` AS `id`
  , `debtor_ref` AS `ref`
  , IF(LENGTH(`Contact`) > 0, `Contact`, `CustName`) AS `name`
  , '' AS `name2`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `phone`
  , `phone2`
  , `fax`
  , `email`
  , '' AS lang
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0  AS inactive
FROM `cust_list` WHERE IsMB GROUP BY debtor_no)

UNION ALL

SELECT
    `branch_code` AS `id`
  , `branch_ref` AS `ref`
  , IF(LENGTH(`Contact`) > 0, `Contact`, `BranchName`) AS `name`
  , '' AS `name2`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `phone`
  , `phone2`
  , `fax`
  , `email`
  , '' AS lang
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0  AS inactive
FROM `cust_list` WHERE IsMB;


SET @currency=(SELECT MID(`value`,1,3) AS currency FROM `0_sys_prefs` WHERE category='setup.company' AND `name`='curr_default');
INSERT INTO `0_debtors_master`
(SELECT
    `debtor_no`
  , `CustName` AS `name`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `TaxNum` AS `tax_id`
  , @currency AS `curr_code`
  , `Pricing` AS `sales_type`
  , 0 AS `dimension_id`
  , 0 AS `dimension2_id`
  , 1 AS `credit_status`
  , `payment_terms`
  , 0 AS `discount`
  , 0 AS `pymt_discount`
  , `credit_limit`
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0 AS inactive
  , `debtor_ref`
FROM cust_list WHERE NOT IsMB)

UNION ALL

(SELECT
    `debtor_no`
  , `CustName` AS `name`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `address`
  , `TaxNum` AS `tax_id`
  , @currency AS `curr_code`
  , `Pricing` AS `sales_type`
  , 0 AS `dimension_id`
  , 0 AS `dimension2_id`
  , 1 AS `credit_status`
  , `payment_terms`
  , 0 AS `discount`
  , 0 AS `pymt_discount`
  , `credit_limit`
  , IF(NeedPO, 'Need PO', ' ') AS notes
  , 0 AS inactive
  , `debtor_ref`
FROM cust_list WHERE IsMB GROUP BY debtor_no);

# Import the branches into #_cust_branch table

SET @salesdiscact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='default_sales_discount_act');
SET @debtorsact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='debtors_act');
SET @paymentdiscact=(SELECT (`value`+0) AS debtors_act FROM `0_sys_prefs` WHERE category='glsetup.sales' AND `name`='default_prompt_payment_act');

INSERT INTO `0_cust_branch`
(SELECT
    `debtor_no` AS `branch_code`
  , `debtor_no`
  , `CustName` AS `br_name`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `br_address`
  , 1 AS `area`
  , 1 AS `salesman`
  , `Contact` AS `contact_name`
  , 'DEF' AS `default_location`
  , `TaxExempt`+1 AS `tax_group_id`
  , `sales_account`
  , @salesdiscact AS `sales_discount_account`
  , @debtorsact AS receivables_account
  , @paymentdiscact AS `payment_discount_account`
  , 1 AS `default_ship_via`
  , 0 AS `disable_trans`
  , REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), "  ", " ") AS `br_post_address`
  , 0 AS `group_no`
  , '' AS `notes`
  , 0 AS `inactive`
  , `debtor_ref` AS `branch_ref`
FROM `cust_list` WHERE NOT `IsMB`)

UNION ALL

(SELECT
    `debtor_no` AS `branch_code`
  , `debtor_no`
  , `CustName` AS `br_name`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `br_address`
  , 1 AS `area`
  , 1 AS `salesman`
  , `Contact` AS `contact_name`
  , 'DEF' AS `default_location`
  , `TaxExempt`+1 AS `tax_group_id`
  , `sales_account`
  , @salesdiscact AS `sales_discount_account`
  , @debtorsact AS receivables_account
  , @paymentdiscact AS `payment_discount_account`
  , 1 AS `default_ship_via`
  , 0 AS `disable_trans`
  , REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), "  ", " ") AS `br_post_address`
  , 0 AS `group_no`
  , '' AS `notes`
  , 0 AS `inactive`
  , `debtor_ref` AS `branch_ref`
FROM `cust_list` WHERE `IsMB` GROUP BY `debtor_no`)

UNION ALL

(SELECT
    `branch_code`
  , `debtor_no`
  , `CustName` AS `br_name`
  , REPLACE(IF(LENGTH(BillingAddress) > 0, BillingAddress, ShippingAddress), "  ", " ") AS `br_address`
  , 1 AS `area`
  , 1 AS `salesman`
  , `Contact` AS `contact_name`
  , 'DEF' AS `default_location`
  , `TaxExempt`+1 AS `tax_group_id`
  , `sales_account`
  , @salesdiscact AS `sales_discount_account`
  , @debtorsact AS receivables_account
  , @paymentdiscact AS `payment_discount_account`
  , 1 AS `default_ship_via`
  , 0 AS `disable_trans`
  , REPLACE(IF(LENGTH(ShippingAddress) > 0, ShippingAddress, BillingAddress), "  ", " ") AS `br_post_address`
  , 0 AS `group_no`
  , '' AS `notes`
  , 0 AS `inactive`
  , `branch_ref`
FROM `cust_list` WHERE `IsMB`);


INSERT INTO `0_crm_contacts`
SELECT * FROM (
SELECT
    NULL AS id
  , `debtor_no` AS person_id
  , 'customer' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE `IsMB`

UNION ALL

SELECT
    NULL AS id
  , `debtor_no` AS person_id
  , 'cust_branch' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE `IsMB`

UNION ALL

(SELECT
    NULL AS id
  , `debtor_no` AS person_id
  , 'customer' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB` GROUP BY debtor_no)

UNION ALL

(SELECT
    NULL AS id
  , `debtor_no` AS person_id
  , 'cust_branch' AS `type`
  , 'general' AS `action`
  , `debtor_no` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB` GROUP BY debtor_no)

UNION ALL

SELECT
    NULL AS id
  , `branch_code` AS person_id
  , 'cust_branch' AS `type`
  , 'general' AS `action`
  , `branch_code` AS `entity_id`
FROM `cust_list` WHERE NOT `IsMB`
) a ORDER BY entity_id, person_id, `type` DESC;