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.
This draft is based on general Oracle Fusion Cloud knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.