- [Instructor] Now, Creating the Schedule Records. This is the meat of the job where we use our imported data to update the schedule records. A number of processes have to happen here. We need to identify the record by an ATM ID, the period stamp date and the week number. And then update the appropriate day amount fields based on the delivery date. Then we need to allocate the denomination amounts to the correct canisters for the ATM. For example, an ATM could be set up as having one 50 dollar canister and three 20 dollar canisters. Obviously, each canister has a capacity, and in the case of 20 dollar canister, it's $40,000. Another requirement is that denominations should be spread across available canisters. While $80,000 of 20s could go into two canisters, if the ATM has three, we need to spread it across all three just to add one more level of complication. Notes come in bundles call bricks, that's 1,000 notes in flats, which has 100 notes. Where possible, we don't want to split bricks. So in the case of 80,000 in 20s, split across three canisters, we wanted to be split is 40,000, 20,000 and 20,000. So this is an involved calculation that is going to be repeated many times for different denominations, so we would want to put it in a function. We are going to update a copy of the schedule table first, then update the production table. I'm updating a copy, as it is the most complex and time consuming part of the process, and I want to wrap it in a transaction. It is not advisable to lock production tables and transactions in a multi-user environment, but working on a staging version we can make sure that this part of the process validates and completes before doing a relatively quick update into production. I'm going to call the procedure that does the main work, usp_ATM_Schedule-Load. This will take a file name as it's one argument. I will need to clear a bunch of variables that'll be used during operation. This operation needs to convene successfully in its entirety so I will wrap it and I begin, try, and I begin transaction block. I will stop by getting the data set of older information from schedule, and report on joining it with the ATM reference table to get the ATM configurations.

 

- See here, I'm just joining with ATMs, where they're stages Then I will create a cursor to loop through this data, data set of denomination amounts and configurations. Firstly, we will need to find the load start period and the week number. To do this, I've created a function called udf_Load_Schedule_Week. Working out the week number is a tedious repetitive task. So let's put that in a function that we can use it over and over again. A scalar function is similar to functions you have in other programming languages. It returns a single value. Return type is to clear it and it returns segment of the function declaration. You want to return an integer value. The function will take the next minimum load start period, lists then the delivery date, and the delivery date is parameters and returns the correct week number. We used the date and function of this QL server to add weeks to the load/start period date. Each clause of the case statement corresponds to one week of the load/start period. Remember, we reached a clause with a number of weeks and into the load/start period date that exceeds the delivery date. Then we have found the week that our delivery date occurs, and this is a good example of how the order of your case statement does make a difference. To find the schedule record we will use the ATM identified delivery date and week number. Couldn't see the other Load Week Schedule function, then search the Load Schedule Table. Once we have our Load Schedule Record we can go through and zero out the cassette on the outfields with Dynamic HQL. We do this as a first precaution just in case this process has run already and the data has been incorrect. We always want to begin the process with a clean slate. We construct the appropriate field names using the DATENAME function, along with substring to get the first three characters of the DATENAME. Then it's the field names, this updates, then it will change depending on the delivery date, you need to use Dynamic HQL to construct the update statement. We do not want to overwrite days that we are not interested in.