Appearance
How does the import process for sales work, exactly?
This is a pretty big question for such a small number of words, and answer is going to be pretty long. So buckle in, and prepare to have your brain just a little bit fried.
Overview
There are two kinds of sales (or dealings … the words are used interchangeably below) that MVOW will import: unconfirmed (or OSR sales), and confirmed sales.
OSR sales originate from the Office of State Revenue, and are kinda scrappy. Many times they are missing a lot of data, such as PIDs, the prices and dates may be wrong, they use multiple dealings for what is actually a single sale, they can include vendors in the list of purchasers, and they don't come in order. “In order” is something that will be discussed a bit more below. OSR dealing numbers start with “OSR” and are followed by a string of numbers.
Confirmed sales come from VNSW, and are regarded by MVOW as the truth. The dealing numbers at present start with the letter A, followed by another letter (at present, R) and then a string of numbers.
Sales are supposed to come in a particular order. That is, the OSR sales come in first as preliminary information. You can analyse these sales, but you have to be careful, because the ownerships, contract and settlements and even the prices can vary from what the confirmed sale says. Sometimes, however, OSR sales are delivered after confirmed sales.
So down to what MVOW does with these incoming sales.
Importing OSR sales
As mentioned above, quite frequently OSR sales don't have a PID. If there is no PID and you have ticked the Contractor | Options | Sales | Reject incoming OSR sales without PIDs box, such sales won't be imported.
Assuming you have a PID (or that box isn't ticked) then work proceeds.
Again, as mentioned above, OSR splits individual sales into multiple incoming sales with different dealing numbers, one for each purchaser. The first thing the import process does is merge these into single dealings based on the PID, contract date, and the strata lot number (if it exists). It will transfer the purchasers from subsequent dealing records matching these criteria, and only keep the first such dealing found.
At present, OSR appears to be including vendors in with the list of purchasers, so you'll have to be careful with this if you choose to analyse these sales.
OSR sales are then checked against existing confirmed sales. If an OSR does match an existing confirmed dealing (PID, contract date, strata lot number), the OSR sale is marked as conflicted, and will show up in the sale import conflicts list. No further processing will be done with this sale under these conditions.
OSR sales are checked against existing OSR sales, and if there is an earlier version of this sale, it's updated with the incoming information.
Importing confirmed sales
Confirmed sale information contains a fair bit of property-specific information. If there is no property entry in the database because the sale has come through before the supp action has been completed, the import process will make a property entry “stub” out of the provided PID, district, unit number, street name, location, postcode, area, zone code and component code.
No value records are created. If a property entry exists but is missing any of these fields, the import process will fill in the blanks.
The import process then looks for any existing sales that have the same PID, contract date, price and strata lot number.
- If such a sale exists, it is updated with the incoming data. If the existing sale is analysed, a sale import conflict is created. If the incoming sale changes the sale code of the existing sale, this is added to the list of sale codes to export. The property entry's ownership is updated, and onsite, offsite and subdivider’s allowances are removed from the property values.
- If no such sale already exists, the import process looks for unconfirmed sales that match the PID and strata lot number, and, if there are any, records them as sale import conflicts.
If the sale is not involved in a conflict, it is simply imported, and if the PID is marked as for sale, then the sale has a note about the fact included.
Another more technical answer
Importing sales is not quite as straight-forward as you might like it to be. There are quite a few factors involved, and unfortunately, the software isn’t smart enough to be able to do the whole thing by itself. This section will explain the limitations, and where you’ll have to step in to resolve ambiguities.
The sale import process
The system will take the following steps when importing sales from an uploaded file:
- Open the file and start reading through it.
- The file has regular sale information, one or more records for the legal description (which are collapsed into a single field), and one or more owner records for each of the vendor and purchaser. The system will write this information to the “import” version of the Sales table.
- If the file is an “unconfirmed dealings” file, the system will process duplicate dealings. This will be explained below.
- If the file is a “confirmed dealings” file, the system will merge the information into the unconfirmed dealings. This will be explained below.
- Finally, the system will import non-conflicting information from the “import” tables into the actual Sales tables.
Processing duplicate dealings
This step only applies if the system is importing unconfirmed dealings. For some reason, the OSR files have one dealing per purchaser. The same dealing number is used, but the system has to merge the different dealings into a single a dealing with different purchasers.
Merging into unconfirmed dealings
This applies when importing confirmed dealings. For each dealing represented in the file, the system will:
- Find out if there are any onsite, offsite or subdividers allowances on the associated property. If so, the sale will be marked as “conflicted” and will require attention by a person. The system will then move on to the next dealing.
- Check to see if there is a corresponding property entry in the system. If there is, the property information will be used to populate any information missing from the dealing. If there is not yet a property, the system will create one based on the information in the dealing.
- Look to see if there is already a record for this dealing based on the PID, strata lot number (if available), contract date and price. If there are no exact matches, it will check to see if there are any dealings for this PID that are still unconfirmed. If there are, this dealing is marked as “conflicted”. Regardless, the process continues with the next dealing.
- As there is at least one duplicate, for each duplicate found:
- If there is an analysis associated with the duplicate, mark the incoming dealing as conflicting with the duplicate, and move on to the next duplicate.
- Copy incoming information into the existing dealing. The system won’t overwrite fields in the existing dealing with empty fields in the incoming dealing.
- If the existing dealing has a sale code, copy this into the new record.
- Move the new information from the import table to the real Sales table.
This process ensure that incoming information is added to existing sales, which means that sales will become confirmed in place. If there are analyses attached to the original sale, they will remain attached, but will not be updated in the same way the sales are. If you need to update the analyses, you’ll have to do this manually. Please let us know if this is going to be a problem.
Import non-conflicting information
Up to this point, we’ve merged duplicated OSR sales into single dealings, and merged confirmed dealings into provision sales. This has resulted in the already-processed dealings being deleted from the import table. So from this point, everything else will be copied directly into the Sales table, or flagged as needing help. For each non-conflicted sale, the system will:
- Retrieve the PID from the sale. If there is PID, the sale will be marked as conflicted and the system will move on to the next dealing.
- Retrieve the property entry and merge its information into the incoming dealing.
- Check to see if the sale has already been imported (we might be re-running a file). If so, update the existing file with incoming information.
- Replace all owner records with the incoming ones.
- Update the property entry due to the fact of the sale. This means replacing the owner names on the property with the sale’s purchasers, and, if it’s not a strata sale, removing the onsite, offsite and subdividers allowances from the property’s values.
How to deal with conflicts
Now that the import process is complete, how do you deal with the sale import conflicts? There will be an indication on the dashboard that there are conflicts, and you can click on that link to visit the page to deal with them. Alternatively, you can go to the Data transfer | Sale conflicts menu item.
Either way you get there, the system will show you a list of conflicts, and you can drill into them to deal with them as you see fit.