Challenge Overview
Project Overview
The purpose of this contest is to refactor an existing stored procedure.
This software manages product information sold in stores. The solution referred to in this document as Central is the master source and sends data to the stores (referred to as sites). Stores can update their own data so there is a chance to get out of synch. There is logic to pull data from stores, compare and report exceptions.
Competition Task Overview
The objective is to refactor the stored procedure spSetItemException. This procedure deletes and then writes item level exceptions to the table tblItemException. The procedure reports these discrepancies between the site(s) and central.
- Not at Site - a row is written out to table tblItemException for every Upc that is not in table tblItemPriceSiteList but exists in table tblItemMaster.
- Not at Central – a row is written out to table tblItemException for every Upc that is not in view View_RegularSite but exists in table tblItemPriceSiteList. The view displays a list of regular prices by site. The view may return more than 1 regular price per item/site combo due to some misconfiguration at Central. It is not advisable to do a JOIN in this case. An EXISTS statement works better to test the existence of an item’s regular price by site.
- Regular Price Exceptions – a row is written out to table tblItemException for every Upc’s regular price discrepancy between table tblItemPriceSiteList and view View_RegularSite.
- Current Price Exceptions - a row is written out to table tblItemException for every Upc’s current price discrepancy between table tblItemPriceSiteList and table tblBatchPrice. The current price of an item is stored in table tblBatchPrice. A function call to fnGetCurrentBatchEffectiveOn returns the required columns to get and item’s current price from table tblBatchPrice.
For more details please register to see the requriements document in contest forum. Make sure all requriements in the document are met.
Technology Overview
- SQL
- SQL Server 2008
- SQL Server 2005
Documentation Provided
Register to see documents provided in contest forum.
Final Submission Guidelines
Submission Deliverables
A complete list of deliverables can be viewed in the TopCoder Assembly competition Tutorial at: http://apps.topcoder.com/wiki/display/tc/Assembly+Competition+Tutorials
Below is an overview of the deliverables:
- Source code for refactored stored procedures.
- A complete and detailed deployment documented explaining how to deploy the application including configuration information.
- Test data (if more data is needed than the one provided by the client).
Final Submission
For each member, the final submission should be uploaded to the Online Review Tool.