Convert Oracle Data Integrator mappings to modern pipelines

-- Mapping: LOAD_CUSTOMER_DIM
-- IKM: Oracle Incremental Update
-- LKM: SQL to Oracle
-- CKM: Oracle (flow control enabled)

Source datastores : CRM_CUSTOMER, CRM_ADDRESS
Join              : CUSTOMER.ADDR_ID = ADDRESS.ADDR_ID
Filter            : CUSTOMER.STATUS <> 'PURGED'
Target datastore  : CUSTOMER_DIM
Update Key        : CUST_NK
Mappings          : FULL_NAME = FIRST_NM || ' ' || LAST_NM
                    CITY      = ADDRESS.CITY

-- KM behaviour, not visible in the mapping:
--   1. load into I$_CUSTOMER_DIM staging
--   2. flow control writes violations to E$_CUSTOMER_DIM
--   3. update matched rows, insert unmatched by CUST_NK

-- IKM Incremental Update → MERGE
CREATE OR REPLACE TEMPORARY TABLE i_customer_dim AS
SELECT  c.cust_nk,
        c.first_nm || ' ' || c.last_nm  AS full_name,
        a.city                          AS city
FROM    crm_customer c
JOIN    crm_address  a ON c.addr_id = a.addr_id
WHERE   c.status <> 'PURGED';

-- CKM flow control → explicit reject capture
INSERT INTO e_customer_dim
SELECT * FROM i_customer_dim WHERE cust_nk IS NULL;

MERGE INTO customer_dim t
USING (SELECT * FROM i_customer_dim
       WHERE cust_nk IS NOT NULL) s
   ON t.cust_nk = s.cust_nk
WHEN MATCHED THEN UPDATE
   SET t.full_name = s.full_name, t.city = s.city
WHEN NOT MATCHED THEN INSERT
   (cust_nk, full_name, city)
   VALUES (s.cust_nk, s.full_name, s.city);