Creating database procedure to synchronize contact field with associated company field

From LeonWiki!

Contents 1 Description 2 Current Processed Field Values 3 Logic 3.1 Logic for batch process client 3.2 Logic for stored procedure

Description

We want to run a batch process that will synchronize any field in a contact record to its value on the company record. To solve this we create a stored procedure that accepts as parameters a company client_id, and a field type_id

Current Processed Field Values

• Lead Status
  1. Type_id = 57998
     • Code_id = {'Hot':57998,'Warm':57997,'Cold':57996,'Suspended':57995,'Abandoned':57994,'Unknown':57999,'N/A':3,'Transfered to Symmetry01':1}
• Category
  1. Type_id = 60030
     • Category values range from 1-27

Logic

Logic for batch process client

1. Get all unique client_ids
   • For each client_id:
     • Call sp_synchronize_contact_fields passing in client_id, type_id

Logic for stored procedure

1. Get all company records from user_fields_tbl
2. Get the contacts for the company from the client_tbl
3. Delete the company contacts from the user_fields_tbl
4. For each contact
   • insert a record in the user_fields_tbl with client_id,contact_number,type_id,code_id (as these fields make up the defined index on the user_fields_tbl)