I’ll walk you through a complete solution covering all three critical areas:
1. Unique Identifier Mapping Strategy
Your current approach has no duplicate prevention. Here’s the correct implementation:
Create Staging Table with Constraints:
CREATE TABLE ecn_staging (
legacy_id VARCHAR2(50) PRIMARY KEY,
agile_number VARCHAR2(50) UNIQUE,
description VARCHAR2(500),
status VARCHAR2(50),
import_date DATE,
is_processed CHAR(1) DEFAULT 'N'
);
Use MERGE Instead of INSERT:
MERGE INTO agile.change_orders dest
USING (SELECT * FROM ecn_staging WHERE is_processed = 'N') src
ON (dest.legacy_id = src.legacy_id)
WHEN NOT MATCHED THEN
INSERT (legacy_id, number, description, status)
VALUES (src.legacy_id, ecn_seq.nextval, src.description, src.status);
Key Mapping Principles:
- Use legacy_id as natural key for duplicate detection
- Create composite unique index on (legacy_id, revision) if ECNs have revisions
- Map legacy status values to valid Agile workflow states
- Preserve original ECN number in a custom attribute for reference
- Use staging table to track what’s been processed
2. Pre-Import Deduplication Process
Before any import, clean your source data:
Step A: Identify Duplicates in Source
SELECT legacy_id, COUNT(*) as dup_count,
MIN(creation_date) as first_created,
MAX(creation_date) as last_created
FROM legacy_ecn_data
GROUP BY legacy_id
HAVING COUNT(*) > 1;
Step B: Deduplication Logic
For your 8% source duplicates, establish rules:
- If same legacy_id with different dates: Keep the latest (most recent revision)
- If same legacy_id with different descriptions: Manual review required
- If exact duplicates: Keep one, log the removal
Step C: Create Clean Source View
CREATE VIEW legacy_ecn_clean AS
SELECT legacy_id, description, status,
ROW_NUMBER() OVER (PARTITION BY legacy_id ORDER BY creation_date DESC) as rn
FROM legacy_ecn_data
WHERE rn = 1; -- Keep only latest version
Step D: Pre-Import Validation Script
# Validation before import
def validate_source_data():
duplicates = check_duplicates()
if duplicates > 0:
log_error(f"Found {duplicates} duplicate legacy IDs")
generate_duplicate_report()
return False
invalid_statuses = check_status_mapping()
if invalid_statuses > 0:
log_error(f"Found {invalid_statuses} unmapped statuses")
return False
return True
3. Post-Import Audit and Cleanup
Immediate Duplicate Detection:
-- Find duplicates created during import
SELECT legacy_id, COUNT(*) as duplicate_count,
LISTAGG(number, ', ') as agile_numbers
FROM agile.change_orders
WHERE import_date >= TRUNC(SYSDATE)
GROUP BY legacy_id
HAVING COUNT(*) > 1;
Systematic Cleanup Process:
- Identify Keeper vs. Duplicate:
WITH ranked_ecns AS (
SELECT change_id, legacy_id, number, creation_date,
ROW_NUMBER() OVER (PARTITION BY legacy_id ORDER BY creation_date ASC) as rank
FROM agile.change_orders
WHERE legacy_id IS NOT NULL
)
SELECT * FROM ranked_ecns WHERE rank > 1; -- These are duplicates to remove
- Handle Workflow Dependencies:
Before deleting duplicates, check for:
- Active workflow instances
- Affected items relationships
- Approval signatures
- Document attachments
- Safe Deletion Approach:
-- First, reassign any affected items from duplicate to keeper
UPDATE affected_items
SET change_id = (SELECT MIN(change_id)
FROM agile.change_orders
WHERE legacy_id = :legacy_id)
WHERE change_id IN (SELECT change_id
FROM ranked_ecns
WHERE rank > 1 AND legacy_id = :legacy_id);
-- Then delete the duplicate
DELETE FROM agile.change_orders
WHERE change_id IN (SELECT change_id FROM ranked_ecns WHERE rank > 1);
4. Comprehensive Post-Import Audit Report
Generate documentation showing:
- Total ECNs imported: 5000
- Source duplicates resolved: 400 (8%)
- Import duplicates prevented: 350 (7%)
- Final duplicate count: 0
- ECNs requiring manual review: 25
Prevention for Future Imports:
- Idempotent Import Script:
Make your script safe to run multiple times:
CREATE OR REPLACE PROCEDURE import_ecns_safe AS
BEGIN
-- Check if already imported
IF EXISTS (SELECT 1 FROM import_log WHERE import_type = 'ECN' AND status = 'COMPLETE') THEN
RAISE_APPLICATION_ERROR(-20001, 'ECN import already completed');
END IF;
-- Use MERGE for upsert behavior
MERGE INTO agile.change_orders...
-- Log completion
INSERT INTO import_log VALUES ('ECN', SYSDATE, 'COMPLETE');
COMMIT;
END;
- Add Unique Constraint:
ALTER TABLE agile.change_orders
ADD CONSTRAINT uk_legacy_id UNIQUE (legacy_id);
- Import Monitoring Dashboard:
Track in real-time:
- Records processed
- Duplicates detected and skipped
- Errors requiring attention
- Estimated completion time
Fixing Your Current 15% Duplicate Issue:
- Run the duplicate detection query above
- Export list of all duplicates with their Agile numbers
- For each legacy_id with duplicates:
- Keep the ECN with earliest creation_date (original import)
- Transfer any unique data from duplicates to keeper
- Update affected items to point to keeper
- Delete duplicate records
- Add unique constraint to prevent future duplicates
- Re-run import script (now safe with MERGE logic) to catch any missed records
This approach will clean up your existing duplicates and prevent new ones. The key is making your import process idempotent so running it multiple times doesn’t create problems.
This draft is based on general Oracle Agile PLM knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.