Convert historical distribution data held in table COST_DIST_PROJECT and merge into existing and new distribution records in table COST_DIST_LOCATION.
DB Oracle 10g
Would prefer to have the solution as a stored procedure if possible.
## Deliverables
Convert historical distribution data held in table COST_DIST_PROJECT and merge into existing and new distribution records in table COST_DIST_LOCATION
1. Generate all historical cost location allocations up to 100%; select only records where the percentage allocated is < 100%. Then generate a new cost location record allocated to the home cost location for the remainder amount and percentage.
2. Select all project distributions and match against the percentage and expense report line item or expense report split line item and update the Project Id on the COST_DIST_LOCATION record.
3. Then, calculate the distribution amounts for all COST_DIST_LOCATION records. The amount allocated to each record should be calculated by summing the total PAYMENT_AMOUNT for each distinct expense_report_pk, expense_item_pk/expense_item_split_pk held in the ACCOUNTING_EXPORT_ARCHIVE table and for the specific cost_dist_cost_loc matching the allocated cost_loc in the COST_DIST_LOCATION table. Select on RPT record types from the ACCOUNTING_EXPORT_ARCHIVE table to perform this match. Update the accumulated amount into the cost_dist_location record.