Data Migration Strategies

Overview

• What you’ll learn:
  • How to plan a data migration using a structured methodology — from assessment and mapping through transformation, loading, and validation
  • How to use iDempiere’s Import Loader framework, SQL migration scripts, and ETL tools to move master and transactional data from legacy systems
  • How to test migrations, plan cutovers, run parallel systems, and ensure data quality in the new iDempiere environment
• Prerequisites: Lessons 1-15 (understanding of iDempiere data model), SQL proficiency, familiarity with data analysis tools
• Estimated reading time: 25 minutes

Introduction

Data migration is simultaneously the most underestimated and most critical phase of any ERP implementation. Organizations frequently allocate months for requirements gathering and configuration, weeks for user acceptance testing, but then compress data migration into a frantic weekend before go-live. The result is predictable: missing records, incorrect balances, broken references, and users who lose trust in the new system from day one.

Successful data migration requires disciplined methodology, thorough planning, and — critically — multiple rehearsals before the actual cutover. This lesson provides a comprehensive framework for migrating data from legacy systems to iDempiere, covering every phase from initial assessment through post-migration validation.

Data Migration Methodology

A proven data migration follows a structured sequence of phases. Skipping or shortcutting any phase creates risk that compounds in later phases.

Phase 1: Assess

Before touching any data, understand what you are dealing with:

• Source system inventory: Document every data source — the legacy ERP database, spreadsheets, emails, paper records, and third-party systems. Many organizations are surprised by how much critical business data lives outside their primary ERP.
• Data volume analysis: Count records by entity type (customers, vendors, products, open orders, historical transactions). This drives technical decisions about migration approach and timing.
• Data quality assessment: Profile the source data for completeness (how many records have missing fields?), accuracy (do addresses validate? are tax IDs correctly formatted?), consistency (is the same customer entered multiple times with different names?), and currency (when was the data last updated?).
• Dependency mapping: Identify which data entities depend on others. For example, invoices reference business partners and products, so business partners and products must be migrated before invoices.

Phase 2: Plan

Based on the assessment, define the migration strategy:

• Scope decisions: Not all data needs to be migrated. Historical transactions older than a certain date may be archived rather than migrated. Inactive products, closed customers, and fully-paid invoices may be excluded. Define clear scope boundaries.
• Migration approach: Will you use iDempiere’s Import Loader (best for standard entities), direct SQL insertion (fastest for large volumes), ETL tools (best for complex transformations), or a combination?
• Sequence: Define the order of migration. A typical sequence is: (1) Chart of Accounts, (2) Tax configuration, (3) Business Partners, (4) Products and Price Lists, (5) Opening balances, (6) Open documents (pending orders, unpaid invoices).
• Timeline: Allocate sufficient time for at least three full migration rehearsals before the actual cutover.

Phase 3: Map

Create detailed data mapping documents that specify, for every target field in iDempiere, where the data comes from and how it is transformed:

Data mapping documents serve as the specification for your migration scripts and as the reference for validation testing. Keep them under version control and update them as mapping decisions change.

Phase 4: Cleanse

Clean the data before migration, not after. Common cleansing tasks include:

• Deduplication: Identify and merge duplicate records (e.g., the same customer entered as “ACME Corp”, “Acme Corporation”, and “ACME CORP.”). Use fuzzy matching algorithms and manual review.
• Standardization: Apply consistent formats to addresses (postal code validation, country code standardization), phone numbers (international format), and names (proper case, abbreviation expansion).
• Enrichment: Fill in missing data that iDempiere requires but the source system does not have (e.g., payment terms, price list assignments, accounting dimensions).
• Validation: Verify referential integrity in the source data — do all invoice references point to valid customers? Do all order lines reference valid products?

Phase 5: Transform and Load

Execute the actual data transformation and loading using one or more of the techniques described in the following sections.

Phase 6: Validate

After loading, systematically verify the migrated data against defined acceptance criteria (covered in detail in the post-migration validation section below).

iDempiere Import Loader Framework

iDempiere provides a built-in import mechanism based on staging tables (prefixed with I_). This is the recommended approach for standard entity migrations because it leverages iDempiere’s own validation logic and creates proper audit trails.

The I_ Tables Pattern

The import process follows a consistent pattern across all import types:

1. Populate the import table: Insert records into the staging table (e.g., I_BPartner, I_Product, I_Invoice, I_GLJournal) via CSV upload, SQL INSERT, or ETL tool. The import table contains both the raw data fields and resolution fields (e.g., both BPartner_Value and C_BPartner_ID).
2. Run the Import process: Execute the corresponding Import process from the iDempiere menu. The process resolves lookups (matching text values to iDempiere IDs), validates data (mandatory fields, valid references), and flags errors. Records with errors are marked in the I_ErrorMsg column and I_IsImported is set to ‘E’.
3. Review and fix errors: Query the import table for records with errors, fix the data, and re-run the import process. Iterate until all records are successfully imported.
4. Verify in iDempiere: Open the corresponding iDempiere windows to spot-check that imported records appear correctly.

Available Import Tables

• I_BPartner: Business partners (customers, vendors), including addresses and contacts.
• I_Product: Products and items.
• I_Invoice: Vendor and customer invoices with line items.
• I_Order: Sales and purchase orders with line items.
• I_GLJournal: General ledger journal entries for opening balances.
• I_Payment: Payments and receipts.
• I_BankStatement: Bank statement lines.
• I_Inventory: Physical inventory counts for initial stock loading.
• I_ElementValue: Chart of accounts (account elements).
• I_ReportLine: Financial report line definitions.

CSV Import Process

The most common method for loading import tables is via CSV files:

1. Prepare the CSV file with column headers matching the import table column names.
2. In iDempiere, navigate to the Import File Loader window.
3. Select the target import table, upload the CSV file, and map columns.
4. Run the loader to populate the import table.
5. Run the corresponding Import process to validate and create records.

-- Example: Directly loading I_BPartner via SQL (alternative to CSV upload)
INSERT INTO I_BPartner (
    AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy,
    Updated, UpdatedBy,
    Value, Name, Name2, TaxID,
    IsCustomer, IsVendor,
    GroupValue, -- Resolves to C_BP_Group_ID
    I_IsImported, I_ErrorMsg
)
SELECT
    1000000, 0, 'Y', now(), 100, now(), 100,
    TRIM(cust_code),
    TRIM(cust_name),
    TRIM(cust_name2),
    REGEXP_REPLACE(tax_num, '[^0-9A-Za-z]', '', 'g'),
    CASE WHEN cust_type IN ('C','B') THEN 'Y' ELSE 'N' END,
    CASE WHEN cust_type IN ('V','B') THEN 'Y' ELSE 'N' END,
    'Standard',
    'N', NULL
FROM legacy_staging.customers
WHERE cust_status = 'ACTIVE';

SQL-Based Migration Scripts

For large data volumes or complex transformations that exceed what the Import Loader can handle efficiently, direct SQL insertion into iDempiere’s core tables is an option. This approach is faster but riskier — it bypasses iDempiere’s application-level validation, so you must ensure data integrity yourself.

Key Principles for Direct SQL Migration

• Generate IDs correctly: Use iDempiere’s AD_Sequence table to obtain valid primary key values. Never hardcode IDs or use ad-hoc sequences that could conflict with iDempiere’s own sequence generation.

-- Function to get next ID from iDempiere sequence
CREATE OR REPLACE FUNCTION get_next_id(p_table_name VARCHAR)
RETURNS INTEGER AS $$
DECLARE
    v_id INTEGER;
BEGIN
    UPDATE AD_Sequence
    SET CurrentNext = CurrentNext + IncrementNo
    WHERE Name = p_table_name
    AND IsTableID = 'Y'
    RETURNING CurrentNext - IncrementNo INTO v_id;
    RETURN v_id;
END;
$$ LANGUAGE plpgsql;

• Populate all mandatory columns: Every iDempiere table requires AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, and the primary key column.
• Respect referential integrity: Verify that all foreign key references point to valid records. Run integrity checks after insertion.
• Disable triggers carefully: If you need to disable triggers for performance during bulk inserts, re-enable them immediately after and then run the validation logic that the triggers would have enforced.
• Wrap in transactions: Execute migration scripts within database transactions so you can roll back the entire batch if errors are detected.

Post-Insert Validation Queries

-- Check for orphaned foreign key references after migration
SELECT 'Missing BPartner' as issue, COUNT(*)
FROM C_Invoice i
WHERE i.C_BPartner_ID NOT IN (SELECT C_BPartner_ID FROM C_BPartner)
  AND i.AD_Client_ID = 1000000

UNION ALL

SELECT 'Missing Product' as issue, COUNT(*)
FROM C_InvoiceLine il
WHERE il.M_Product_ID IS NOT NULL
  AND il.M_Product_ID NOT IN (SELECT M_Product_ID FROM M_Product)
  AND il.AD_Client_ID = 1000000

UNION ALL

SELECT 'Missing Account' as issue, COUNT(*)
FROM Fact_Acct fa
WHERE fa.Account_ID NOT IN (SELECT C_ElementValue_ID FROM C_ElementValue)
  AND fa.AD_Client_ID = 1000000;

ETL Tools Overview

For complex migrations involving multiple source systems, heavy transformations, and ongoing data synchronization, dedicated ETL (Extract, Transform, Load) tools provide a more manageable approach than hand-written SQL scripts.

Pentaho Data Integration (Kettle)

Pentaho is particularly relevant for iDempiere because both projects share heritage in the Compiere/ADempiere ecosystem. Pentaho provides visual data pipeline design with drag-and-drop transformations. Key strengths include:

• Native JDBC connectivity to both source and target databases.
• Rich transformation steps (lookups, joins, aggregations, data cleansing, fuzzy matching).
• Reusable transformation templates — design once, parameterize for different entities.
• Built-in logging and error handling for each transformation step.

Talend Open Studio

Talend generates Java code from visual pipeline designs, providing both ease of design and high performance. It offers extensive connectors for databases, file formats, web services, and cloud platforms. The open-source Community Edition is sufficient for most migration projects.

Custom Python Scripts

For organizations without ETL tool expertise, well-structured Python scripts using libraries like pandas (for data transformation) and psycopg2 or SQLAlchemy (for database interaction) can be effective for smaller migrations:

# Example: Python-based data migration for Business Partners
import pandas as pd
import psycopg2
from psycopg2.extras import execute_batch

# Extract from legacy system
legacy_conn = psycopg2.connect(host="legacy-db", dbname="legacy_erp")
df = pd.read_sql("SELECT * FROM customers WHERE status = 'ACTIVE'", legacy_conn)

# Transform
df['Value'] = df['cust_code'].str.strip().str.upper()
df['Name'] = df['cust_name'].str.strip().str.title()
df['TaxID'] = df['tax_num'].str.replace(r'[^0-9A-Za-z]', '', regex=True)
df['IsCustomer'] = df['cust_type'].isin(['C', 'B']).map({True: 'Y', False: 'N'})

# Load into iDempiere import table
idempiere_conn = psycopg2.connect(host="idempiere-db", dbname="idempiere")
insert_sql = """
    INSERT INTO I_BPartner (AD_Client_ID, AD_Org_ID, IsActive,
        Created, CreatedBy, Updated, UpdatedBy,
        Value, Name, TaxID, IsCustomer, GroupValue,
        I_IsImported, I_ErrorMsg)
    VALUES (1000000, 0, 'Y', now(), 100, now(), 100,
        %(Value)s, %(Name)s, %(TaxID)s, %(IsCustomer)s, 'Standard',
        'N', NULL)
"""
execute_batch(idempiere_conn.cursor(), insert_sql, df.to_dict('records'))
idempiere_conn.commit()

Migrating Master Data

Master data migration is the foundation upon which all transactional migration depends. Errors in master data cascade into every transaction that references them.

Chart of Accounts

The Chart of Accounts (CoA) defines the accounting structure and must be migrated first:

1. Map the legacy account structure to iDempiere’s account element structure (C_ElementValue).
2. Determine how the legacy account segments map to iDempiere’s accounting dimensions (Account, Organization, Business Partner, Product, Activity, Campaign, Project).
3. Use the I_ElementValue import table or direct SQL to create the account elements.
4. Configure the default accounts in the Accounting Schema to reference the migrated chart of accounts.

Pay special attention to account types (Asset, Liability, Equity, Revenue, Expense) and summary vs. posting accounts. iDempiere requires correct account type assignments for financial statements to balance properly.

Business Partners

Business partners include customers, vendors, and employees. The migration must handle:

• The C_BPartner header record (name, search key, tax ID, group, payment terms).
• One or more C_BPartner_Location records (addresses) with proper C_Location records (country, region, city, postal code).
• One or more AD_User contact records linked to the business partner.
• Customer-specific data (C_BPartner.IsCustomer, credit limits, price lists).
• Vendor-specific data (C_BPartner.IsVendor, payment rules, purchase price lists).
• Bank account information (C_BP_BankAccount) for payment processing.

The I_BPartner import table handles most of this in a single import pass, creating the partner, location, and contact records together.

Products

Product migration includes:

• The M_Product record (search key, name, UOM, product category, product type).
• Price list entries (M_ProductPrice) for each applicable price list version.
• Bill of Materials (PP_Product_BOM and PP_Product_BOMLine) for manufactured items.
• Product attributes and attribute set instances for lot-tracked or serialized items.
• Replenishment rules and warehouse-specific configurations.

Migrating Transactional Data

Transactional migration is more complex than master data migration because it involves document state, accounting implications, and temporal consistency.

Opening Balances

The most common approach for financial cutover is to migrate opening balances as of a specific date rather than recreating every historical transaction:

1. General Ledger balances: Create a GL Journal entry that debits and credits all balance sheet accounts to match the legacy trial balance as of the cutover date. Use the I_GLJournal import table. The journal should use a specific document type (e.g., “Opening Balance”) for easy identification.
2. Accounts Receivable sub-ledger: Import individual open (unpaid) customer invoices so that the AR aging report in iDempiere matches the legacy system. Each open invoice is imported via I_Invoice and completed to create the accounting entries.
3. Accounts Payable sub-ledger: Similarly, import open vendor invoices to establish the AP sub-ledger.
4. Inventory: Use I_Inventory to load physical inventory counts, establishing opening stock quantities and values.
5. Bank balances: Create opening bank statement entries or GL journal entries to establish bank account balances.

Open Documents

Pending business documents that will continue to be active after cutover must be migrated:

• Open Sales Orders: Orders that have been placed but not yet fully shipped and invoiced. Import via I_Order or direct SQL, ensuring line quantities reflect only the outstanding (undelivered) amounts.
• Open Purchase Orders: Orders placed with vendors where goods have not yet been fully received.
• Pending Shipments: Partially shipped orders that require additional shipments.

For each open document, carefully determine the correct document status (Drafted, In Progress, Completed) and ensure that quantity fields reflect the current state, not the original order quantities.

Historical Data Considerations

Decide how to handle historical data that will not be actively used in iDempiere but may be needed for reference:

• Full migration: Import all historical transactions. This provides complete reporting history in iDempiere but significantly increases migration complexity and time.
• Summary migration: Import monthly or quarterly summary GL journal entries for historical periods, providing period-level reporting without individual transaction detail.
• Archive access: Keep the legacy system available in read-only mode for historical lookups. This is often the most pragmatic approach for detailed historical data.

Migration Testing Strategies

Migration testing is not a single event — it is a series of rehearsals, each more complete and realistic than the last.

Rehearsal Approach

1. Rehearsal 1 (Technical validation): Run the full migration against a test database. Verify that all scripts execute without errors, record counts match expectations, and foreign key relationships are valid. Focus on technical correctness, not business validation.
2. Rehearsal 2 (Business validation): Run the migration again and have business users validate the results. Compare key reports (trial balance, aging reports, inventory valuation) between the legacy system and iDempiere. Fix data quality issues and mapping errors.
3. Rehearsal 3 (Dress rehearsal): Simulate the actual cutover process end-to-end, including the data freeze in the legacy system, migration execution, validation, and go/no-go decision. Time everything. This rehearsal should be as close to the real cutover as possible.

Validation Checks

Build automated validation scripts that compare source and target data:

-- Record count comparison
SELECT 'Business Partners' as entity,
    (SELECT COUNT(*) FROM legacy_staging.customers
     WHERE cust_status = 'ACTIVE') as source_count,
    (SELECT COUNT(*) FROM C_BPartner
     WHERE AD_Client_ID = 1000000
     AND IsActive = 'Y') as target_count;

-- Financial balance comparison
SELECT 'AR Balance' as entity,
    (SELECT SUM(open_amount) FROM legacy_staging.open_invoices
     WHERE doc_type = 'AR') as source_balance,
    (SELECT SUM(OpenAmt) FROM RV_OpenItem
     WHERE AD_Client_ID = 1000000
     AND IsSOTrx = 'Y') as target_balance;

-- Inventory quantity comparison
SELECT 'Total Inventory Qty' as entity,
    (SELECT SUM(qty_on_hand) FROM legacy_staging.inventory) as source_qty,
    (SELECT SUM(QtyOnHand) FROM M_StorageOnHand
     WHERE AD_Client_ID = 1000000) as target_qty;

Cutover Planning

The cutover is the critical moment when the organization switches from the legacy system to iDempiere. Meticulous planning and rehearsal are essential.

Cutover Steps

1. Data freeze: Stop all data entry in the legacy system at a defined time (typically end of business on a Friday or end of a fiscal period).
2. Final data extraction: Extract the latest data from the legacy system, including any transactions entered since the last rehearsal extraction.
3. Migration execution: Run the migration scripts against the production iDempiere database. This should be highly automated based on the rehearsal experience.
4. Validation: Execute the automated validation scripts and have designated business users perform spot checks of critical data.
5. Go/No-Go decision: Based on validation results, decide whether to proceed with go-live or roll back.
6. Go-live: Enable user access to iDempiere. The legacy system remains available in read-only mode for reference.

Parallel Running

For high-risk migrations, a parallel running period (typically 1-3 months) where transactions are entered in both the legacy system and iDempiere provides a safety net. Results are compared at the end of each period (daily, weekly, or monthly) to identify discrepancies. Parallel running is expensive (double data entry) but provides maximum confidence.

Rollback Plan

Always have a rollback plan. Before cutover, take a full backup of the iDempiere database. If the migration fails validation or critical issues are discovered post-go-live, you can restore the backup and continue operating on the legacy system while issues are resolved. Define clear rollback criteria and a decision deadline (e.g., “If validation is not complete by Sunday 6 PM, we roll back”).

Post-Migration Data Quality Monitoring

Data quality does not end at cutover. The first weeks and months of operation in iDempiere will reveal data issues that were not caught during migration testing.

• Daily reconciliation: During the first month, reconcile key financial figures between iDempiere reports and independent sources (bank statements, tax returns, legacy system read-only access).
• Data quality dashboards: Build SQL-based checks that run daily and flag anomalies — orphaned records, unexpected NULL values in mandatory fields, duplicate business partner entries, accounting imbalances.
• User feedback channel: Establish a clear process for users to report data issues. Treat data quality issues with the same urgency as software bugs during the initial stabilization period.
• Legacy system retirement: Keep the legacy system available in read-only mode for at least 6-12 months post-migration to support lookups of historical data that was not migrated.

Summary

Data migration is a discipline that requires equal parts technical skill and project management rigor. The key success factors are:

• Start early — data assessment and cleansing can begin months before the planned go-live.
• Use iDempiere’s Import Loader for standard entities; supplement with SQL and ETL for complex scenarios.
• Rehearse the full migration at least three times before the real cutover.
• Build automated validation scripts and compare source-to-target data systematically.
• Plan for rollback — hope is not a strategy.
• Monitor data quality actively in the weeks following migration.

In the next lesson, we will zoom out to the broader picture — how to build complete business solutions on iDempiere, from requirements gathering through go-live and beyond.

-- 範例：透過 SQL 直接載入 I_BPartner（CSV 上傳的替代方案）
INSERT INTO I_BPartner (
    AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy,
    Updated, UpdatedBy,
    Value, Name, Name2, TaxID,
    IsCustomer, IsVendor,
    GroupValue, -- 解析為 C_BP_Group_ID
    I_IsImported, I_ErrorMsg
)
SELECT
    1000000, 0, 'Y', now(), 100, now(), 100,
    TRIM(cust_code),
    TRIM(cust_name),
    TRIM(cust_name2),
    REGEXP_REPLACE(tax_num, '[^0-9A-Za-z]', '', 'g'),
    CASE WHEN cust_type IN ('C','B') THEN 'Y' ELSE 'N' END,
    CASE WHEN cust_type IN ('V','B') THEN 'Y' ELSE 'N' END,
    'Standard',
    'N', NULL
FROM legacy_staging.customers
WHERE cust_status = 'ACTIVE';

-- 從 iDempiere 序列取得下一個 ID 的函式
CREATE OR REPLACE FUNCTION get_next_id(p_table_name VARCHAR)
RETURNS INTEGER AS $$
DECLARE
    v_id INTEGER;
BEGIN
    UPDATE AD_Sequence
    SET CurrentNext = CurrentNext + IncrementNo
    WHERE Name = p_table_name
    AND IsTableID = 'Y'
    RETURNING CurrentNext - IncrementNo INTO v_id;
    RETURN v_id;
END;
$$ LANGUAGE plpgsql;

-- 遷移後檢查孤立的外鍵參照
SELECT 'Missing BPartner' as issue, COUNT(*)
FROM C_Invoice i
WHERE i.C_BPartner_ID NOT IN (SELECT C_BPartner_ID FROM C_BPartner)
  AND i.AD_Client_ID = 1000000

UNION ALL

SELECT 'Missing Product' as issue, COUNT(*)
FROM C_InvoiceLine il
WHERE il.M_Product_ID IS NOT NULL
  AND il.M_Product_ID NOT IN (SELECT M_Product_ID FROM M_Product)
  AND il.AD_Client_ID = 1000000

UNION ALL

SELECT 'Missing Account' as issue, COUNT(*)
FROM Fact_Acct fa
WHERE fa.Account_ID NOT IN (SELECT C_ElementValue_ID FROM C_ElementValue)
  AND fa.AD_Client_ID = 1000000;

# 範例：基於 Python 的業務夥伴資料遷移
import pandas as pd
import psycopg2
from psycopg2.extras import execute_batch

# 從舊系統擷取
legacy_conn = psycopg2.connect(host="legacy-db", dbname="legacy_erp")
df = pd.read_sql("SELECT * FROM customers WHERE status = 'ACTIVE'", legacy_conn)

# 轉換
df['Value'] = df['cust_code'].str.strip().str.upper()
df['Name'] = df['cust_name'].str.strip().str.title()
df['TaxID'] = df['tax_num'].str.replace(r'[^0-9A-Za-z]', '', regex=True)
df['IsCustomer'] = df['cust_type'].isin(['C', 'B']).map({True: 'Y', False: 'N'})

# 載入到 iDempiere 匯入表
idempiere_conn = psycopg2.connect(host="idempiere-db", dbname="idempiere")
insert_sql = """
    INSERT INTO I_BPartner (AD_Client_ID, AD_Org_ID, IsActive,
        Created, CreatedBy, Updated, UpdatedBy,
        Value, Name, TaxID, IsCustomer, GroupValue,
        I_IsImported, I_ErrorMsg)
    VALUES (1000000, 0, 'Y', now(), 100, now(), 100,
        %(Value)s, %(Name)s, %(TaxID)s, %(IsCustomer)s, 'Standard',
        'N', NULL)
"""
execute_batch(idempiere_conn.cursor(), insert_sql, df.to_dict('records'))
idempiere_conn.commit()

-- 記錄數量比較
SELECT 'Business Partners' as entity,
    (SELECT COUNT(*) FROM legacy_staging.customers
     WHERE cust_status = 'ACTIVE') as source_count,
    (SELECT COUNT(*) FROM C_BPartner
     WHERE AD_Client_ID = 1000000
     AND IsActive = 'Y') as target_count;

-- 財務餘額比較
SELECT 'AR Balance' as entity,
    (SELECT SUM(open_amount) FROM legacy_staging.open_invoices
     WHERE doc_type = 'AR') as source_balance,
    (SELECT SUM(OpenAmt) FROM RV_OpenItem
     WHERE AD_Client_ID = 1000000
     AND IsSOTrx = 'Y') as target_balance;

-- 庫存數量比較
SELECT 'Total Inventory Qty' as entity,
    (SELECT SUM(qty_on_hand) FROM legacy_staging.inventory) as source_qty,
    (SELECT SUM(QtyOnHand) FROM M_StorageOnHand
     WHERE AD_Client_ID = 1000000) as target_qty;

-- 例：SQL による I_BPartner の直接ロード（CSV アップロードの代替）
INSERT INTO I_BPartner (
    AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy,
    Updated, UpdatedBy,
    Value, Name, Name2, TaxID,
    IsCustomer, IsVendor,
    GroupValue, -- C_BP_Group_ID に解決
    I_IsImported, I_ErrorMsg
)
SELECT
    1000000, 0, 'Y', now(), 100, now(), 100,
    TRIM(cust_code),
    TRIM(cust_name),
    TRIM(cust_name2),
    REGEXP_REPLACE(tax_num, '[^0-9A-Za-z]', '', 'g'),
    CASE WHEN cust_type IN ('C','B') THEN 'Y' ELSE 'N' END,
    CASE WHEN cust_type IN ('V','B') THEN 'Y' ELSE 'N' END,
    'Standard',
    'N', NULL
FROM legacy_staging.customers
WHERE cust_status = 'ACTIVE';

-- iDempiere シーケンスから次の ID を取得する関数
CREATE OR REPLACE FUNCTION get_next_id(p_table_name VARCHAR)
RETURNS INTEGER AS $$
DECLARE
    v_id INTEGER;
BEGIN
    UPDATE AD_Sequence
    SET CurrentNext = CurrentNext + IncrementNo
    WHERE Name = p_table_name
    AND IsTableID = 'Y'
    RETURNING CurrentNext - IncrementNo INTO v_id;
    RETURN v_id;
END;
$$ LANGUAGE plpgsql;

-- 移行後の孤立外部キー参照のチェック
SELECT 'Missing BPartner' as issue, COUNT(*)
FROM C_Invoice i
WHERE i.C_BPartner_ID NOT IN (SELECT C_BPartner_ID FROM C_BPartner)
  AND i.AD_Client_ID = 1000000

UNION ALL

SELECT 'Missing Product' as issue, COUNT(*)
FROM C_InvoiceLine il
WHERE il.M_Product_ID IS NOT NULL
  AND il.M_Product_ID NOT IN (SELECT M_Product_ID FROM M_Product)
  AND il.AD_Client_ID = 1000000

UNION ALL

SELECT 'Missing Account' as issue, COUNT(*)
FROM Fact_Acct fa
WHERE fa.Account_ID NOT IN (SELECT C_ElementValue_ID FROM C_ElementValue)
  AND fa.AD_Client_ID = 1000000;

# 例：Python ベースのビジネスパートナーデータ移行
import pandas as pd
import psycopg2
from psycopg2.extras import execute_batch

# レガシーシステムからの抽出
legacy_conn = psycopg2.connect(host="legacy-db", dbname="legacy_erp")
df = pd.read_sql("SELECT * FROM customers WHERE status = 'ACTIVE'", legacy_conn)

# 変換
df['Value'] = df['cust_code'].str.strip().str.upper()
df['Name'] = df['cust_name'].str.strip().str.title()
df['TaxID'] = df['tax_num'].str.replace(r'[^0-9A-Za-z]', '', regex=True)
df['IsCustomer'] = df['cust_type'].isin(['C', 'B']).map({True: 'Y', False: 'N'})

# iDempiere インポートテーブルへのロード
idempiere_conn = psycopg2.connect(host="idempiere-db", dbname="idempiere")
insert_sql = """
    INSERT INTO I_BPartner (AD_Client_ID, AD_Org_ID, IsActive,
        Created, CreatedBy, Updated, UpdatedBy,
        Value, Name, TaxID, IsCustomer, GroupValue,
        I_IsImported, I_ErrorMsg)
    VALUES (1000000, 0, 'Y', now(), 100, now(), 100,
        %(Value)s, %(Name)s, %(TaxID)s, %(IsCustomer)s, 'Standard',
        'N', NULL)
"""
execute_batch(idempiere_conn.cursor(), insert_sql, df.to_dict('records'))
idempiere_conn.commit()

-- レコード数の比較
SELECT 'Business Partners' as entity,
    (SELECT COUNT(*) FROM legacy_staging.customers
     WHERE cust_status = 'ACTIVE') as source_count,
    (SELECT COUNT(*) FROM C_BPartner
     WHERE AD_Client_ID = 1000000
     AND IsActive = 'Y') as target_count;

-- 財務残高の比較
SELECT 'AR Balance' as entity,
    (SELECT SUM(open_amount) FROM legacy_staging.open_invoices
     WHERE doc_type = 'AR') as source_balance,
    (SELECT SUM(OpenAmt) FROM RV_OpenItem
     WHERE AD_Client_ID = 1000000
     AND IsSOTrx = 'Y') as target_balance;

-- 在庫数量の比較
SELECT 'Total Inventory Qty' as entity,
    (SELECT SUM(qty_on_hand) FROM legacy_staging.inventory) as source_qty,
    (SELECT SUM(QtyOnHand) FROM M_StorageOnHand
     WHERE AD_Client_ID = 1000000) as target_qty;