- Because working out the schedule is potentially the most error prone part of the process, you want to wrap the whole function in a try-catch block. In that try-catch block you'll want to begin a transaction. If any unforeseen error occurs, you want to fall into the catch block. We will test to see if we're in a transaction with the trans count, key word, and then rollback the transaction. When we use the cursor status function to determine if the cursor still exists and if so, deallocate it. Before we can deallocate it, if it does exist, we must close it. After we have rolled back the transaction and cleaned up the cursor, then we log the error to our error table. And then set the return result to zero to indicate the procedure has not been successful. This structure will ensure that the process succeeds in its entirety or fails completely. 

After we have updated the load schedules and the staging table, we need to validate it against the original data to make sure our schedules reflect reality. There are three validations needed to be performed. Does the ATM and the order file exist in the database? Was there a schedule record in the database to update? Does the total amount allocated to the ATM cassettes equal the ATM order file total? This check identifies canister configuration discrepancies. We will use our load schedule week function within our select statement to give us the correct week number. We will create a temporary schedule table, with the ATM identifier or week number for that load start period and the delivery day. And if we find a discrepancy in what we have loaded against the original data, we write that load error to the error table. Because our load amounts are not stored as integer values but varchars, we need to use the substring function to pull apart the varchar field to get the denomination and the amount. We need to do this for all our canister fields and then cast each substring as a integer and then add them together. We use the delivery day field from our temp schedule table to use the right day fields from load schedule. This may appear a little cumbersome but copy and paste is our friend. It would be possible to put this verbose case statement within a function, but I will leave that for you to do.