Here’s a comprehensive solution addressing all three critical aspects of your property data migration:
1. Legacy Data Profiling & Analysis:
First, complete a thorough profiling of your 180 duplicate records. Create a classification framework:
-- Profiling query to categorize duplicates
SELECT
legacy_property_id,
COUNT(*) as occurrence_count,
COUNT(DISTINCT address) as unique_addresses,
COUNT(DISTINCT org_unit) as unique_orgs,
MIN(create_date) as first_occurrence,
MAX(create_date) as last_occurrence,
CASE
WHEN COUNT(DISTINCT address) = 1
THEN 'DATA_ENTRY_ERROR'
WHEN subdivision_flag = 'Y'
THEN 'SUBDIVISION'
ELSE 'REGIONAL_DUPLICATE'
END as duplicate_type
FROM legacy_properties
GROUP BY legacy_property_id
HAVING COUNT(*) > 1
Document each duplicate scenario with:
- Business reason for duplicate
- Which record should be the primary in Oracle
- Whether historical records need preservation
- Dependencies in other systems
2. Deduplication Strategy by Scenario:
Scenario A: Data Entry Errors (42 cases)
- Action: Consolidate into single Oracle property record
- ID Strategy: Keep most recent legacy ID or the one with most complete data
- Data Handling: Merge attributes, taking most recent or most complete values
ETL Logic:
SELECT
legacy_id,
COALESCE(r1.property_name, r2.property_name) as name,
COALESCE(r1.address, r2.address) as address,
GREATEST(r1.last_update, r2.last_update) as source_date
FROM
(SELECT * FROM legacy WHERE id = 'PROP-X'
ORDER BY last_update DESC LIMIT 1) r1
Scenario B: Subdivisions (65 cases)
- Action: Create unique Oracle property records for each subdivision
- ID Strategy: Generate new sequential IDs with parent reference
- Mapping: PROP-12345 becomes PROP-12345-A, PROP-12345-B, etc.
ID Generation:
Original: PROP-12345
Oracle IDs:
PROP-12345-01 (Unit A)
PROP-12345-02 (Unit B)
PROP-12345-03 (Unit C)
Store parent property ID in DFF for relationship tracking.
Scenario C: Regional Duplicates (35 cases)
- Action: Append region/org identifier to create unique IDs
- ID Strategy: {Legacy_ID}-{Region_Code}
- Example: PROP-12345 in East region becomes PROP-12345-EAST
Mapping Table Structure:
CREATE TABLE property_id_mapping (
legacy_property_id VARCHAR2(50),
oracle_property_id VARCHAR2(50),
region_code VARCHAR2(10),
duplicate_scenario VARCHAR2(30),
effective_start_date DATE,
effective_end_date DATE,
is_primary_record VARCHAR2(1),
migration_notes VARCHAR2(500)
);
Scenario D: Merge/Re-split (38 cases)
- Action: Create time-based property records with effective dating
- ID Strategy: Use current state IDs, document history in notes
- Relationship: Link related properties through custom attributes
3. ID Mapping Implementation:
Create Persistent Mapping Solution:
Store mapping table in Oracle as a custom table accessible to all integrations:
CREATE TABLE XXRE_PROPERTY_ID_XREF (
xref_id NUMBER PRIMARY KEY,
legacy_system_code VARCHAR2(10),
legacy_property_id VARCHAR2(50),
oracle_property_number VARCHAR2(50),
property_name VARCHAR2(240),
region_code VARCHAR2(10),
mapping_type VARCHAR2(30),
active_flag VARCHAR2(1),
created_by VARCHAR2(100),
creation_date DATE,
last_updated_by VARCHAR2(100),
last_update_date DATE
);
CREATE INDEX idx_legacy_prop
ON XXRE_PROPERTY_ID_XREF(legacy_property_id);
CREATE INDEX idx_oracle_prop
ON XXRE_PROPERTY_ID_XREF(oracle_property_number);
Build Integration Translation Layer:
For systems sending work orders or transactions with legacy IDs:
-- Translation function for incoming interfaces
CREATE OR REPLACE FUNCTION get_oracle_property_id(
p_legacy_id VARCHAR2,
p_region VARCHAR2 DEFAULT NULL
) RETURN VARCHAR2 IS
v_oracle_id VARCHAR2(50);
BEGIN
SELECT oracle_property_number
INTO v_oracle_id
FROM XXRE_PROPERTY_ID_XREF
WHERE legacy_property_id = p_legacy_id
AND active_flag = 'Y'
AND (region_code = p_region OR p_region IS NULL)
AND ROWNUM = 1;
RETURN v_oracle_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
Configure DFF for Legacy Reference:
In Oracle Real Estate Property setup:
- Navigate to: Setup and Maintenance > Manage Descriptive Flexfields
- Add custom segment: LEGACY_PROPERTY_ID
- Make it required and displayed on property forms
- Use this field in all reports requiring legacy reference
Migration Execution Plan:
-
Pre-Migration (Week 1-2):
- Complete duplicate analysis and classification
- Create and populate mapping table with proposed new IDs
- Review with business stakeholders for approval
- Document all ID mapping decisions
-
Data Transformation (Week 3):
- Apply deduplication rules in ETL
- Generate unique Oracle property IDs
- Populate mapping table with final assignments
- Transform FBDI files with new IDs
-
Migration (Week 4):
- Load property master data via FBDI
- Validate all properties imported successfully
- Verify no duplicate ID errors
- Run reconciliation reports
-
Post-Migration (Week 5):
- Deploy translation functions to integration middleware
- Update interface mappings to use crossref table
- Test work order submissions from facility system
- Monitor for any ID resolution issues
Validation Queries:
After migration, verify data quality:
-- Check for any remaining duplicates
SELECT property_number, COUNT(*)
FROM pjf_properties_vl
GROUP BY property_number
HAVING COUNT(*) > 1;
-- Verify mapping coverage
SELECT
COUNT(DISTINCT legacy_property_id) as legacy_count,
COUNT(DISTINCT oracle_property_number) as oracle_count
FROM XXRE_PROPERTY_ID_XREF;
This comprehensive approach ensures clean property data in Oracle while maintaining traceability to legacy systems and supporting ongoing integrations that reference old property identifiers.