AiDOPWarehouse/doc/migrations/2026-05-10_s3_mdp_first_sync_run.sql

-- S3 first MDP sync run for aidopdev.
-- Purpose:
--   - Execute the first controlled source -> mdp_stg_* sync for S3 entities registered in mdp_entity.
--   - Write one mdp_sync_log row per entity.
--
-- Execution note:
--   This file records the executable entity scope and verification SQL. The actual run was executed
--   entity-by-entity with short transactions to avoid long-running multi-statement client hangs.
--
-- Entity scope:
--   S3_SUPPLIER                  SuppMaster              -> mdp_stg_supplier
--   S3_CONSIGNEE_SUPPLIER        ConsigneeAddressMaster  -> mdp_stg_supplier
--   S3_ITEM_ERP                  ItemMaster              -> mdp_stg_item
--   S3_ITEM_NEW                  ic_item                 -> mdp_stg_item
--   S3_SOURCE_LIST               srm_purchase            -> mdp_stg_source_list
--   S3_SUPPLY_DEMAND             ic_demandschedule       -> mdp_stg_supply_demand
--   S3_PURCHASE_REQUEST          srm_pr_main             -> mdp_stg_supply_demand
--   S3_PURCHASE_ORDER_MASTER     PurOrdMaster            -> mdp_stg_purchase_order
--   S3_PURCHASE_ORDER_DETAIL     PurOrdDetail            -> mdp_stg_purchase_order
--   S3_DELIVERY_PLAN             srm_polist_ds           -> mdp_stg_delivery
--   S3_SHIPPER_MASTER            scm_shd                 -> mdp_stg_delivery
--   S3_SHIPPER_DETAIL            scm_shdzb               -> mdp_stg_delivery
--   S3_RECEIPT_MASTER            PurOrdRctMaster         -> mdp_stg_receipt
--   S3_RECEIPT_DETAIL            PurOrdRctDetail         -> mdp_stg_receipt
--   S3_WORK_ORDER_MASTER         WorkOrdMaster           -> mdp_stg_work_order_material
--   S3_WORK_ORDER_DETAIL         WorkOrdDetail           -> mdp_stg_work_order_material
--   S3_WORK_ORDER_ROUTING        WorkOrdRouting          -> mdp_stg_work_order_material
--   S3_ROUTING_OUTSOURCE         RoutingOpDetail         -> mdp_stg_work_order_material
--   S3_INVENTORY                 InvMaster               -> mdp_stg_work_order_material
--
-- Verification SQL:
--
-- SELECT target_table_name, COUNT(*) AS entity_count
-- FROM mdp_entity
-- WHERE entity_code LIKE 'S3_%'
-- GROUP BY target_table_name
-- ORDER BY target_table_name;
--
-- SELECT source_code, COUNT(*) AS log_count, SUM(rows_read) AS rows_read, SUM(rows_insert + rows_update) AS rows_written
-- FROM mdp_sync_log
-- WHERE sync_batch_id LIKE 'S3_MDP_SYNC_%'
-- GROUP BY source_code;
--
-- SELECT 'mdp_stg_supplier' AS table_name, COUNT(*) AS rows_count FROM mdp_stg_supplier
-- UNION ALL SELECT 'mdp_stg_item', COUNT(*) FROM mdp_stg_item
-- UNION ALL SELECT 'mdp_stg_source_list', COUNT(*) FROM mdp_stg_source_list
-- UNION ALL SELECT 'mdp_stg_supply_demand', COUNT(*) FROM mdp_stg_supply_demand
-- UNION ALL SELECT 'mdp_stg_purchase_order', COUNT(*) FROM mdp_stg_purchase_order
-- UNION ALL SELECT 'mdp_stg_delivery', COUNT(*) FROM mdp_stg_delivery
-- UNION ALL SELECT 'mdp_stg_receipt', COUNT(*) FROM mdp_stg_receipt
-- UNION ALL SELECT 'mdp_stg_work_order_material', COUNT(*) FROM mdp_stg_work_order_material;