We need two procedures and one additional view created.
The first procedure selects the first record from the CanBeCalled view that can be called using the timezone rules, sets Customer_Preferences.Last_Date_Called = NOW() WHERE Customer_Preferences.Phone_Number = Phone_Number, and returns all the data.
The second procedure is for saving the results from the procedure above. If the call falls into the busy/no answer rules and we have re-called them less than three time, then Customer_Preferences.Last_Date_Called = NOW() WHERE Customer_Preferences.Phone_Number = Phone_Number otherwise Customer_Preferences.Last_Successful_Call = NOW() WHERE Customer_Preferences.Phone_Number = Phone_Number
We also need a view that returns the existing survey information for a given invoice number so that the user can edit it.
## Deliverables
See the attached sql dump for the database structure.