AiDOPWarehouse/tools/sql/check_order_delivery_collations.sql

-- 诊断订单交付列表相关表的字符集校对问题

USE aidopdev;

-- 检查涉及的表和字段的字符集校对规则
SELECT 
    'crm_seorder' AS table_name,
    COLUMN_NAME,
    COLUMN_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'crm_seorder'
  AND COLUMN_NAME IN ('bill_no', 'Id', 'custom_no');

SELECT 
    'crm_seorderentry' AS table_name,
    COLUMN_NAME,
    COLUMN_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'crm_seorderentry'
  AND COLUMN_NAME IN ('id', 'seorder_id', 'item_number');

SELECT 
    'mes_moentry' AS table_name,
    COLUMN_NAME,
    COLUMN_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'mes_moentry'
  AND COLUMN_NAME IN ('moentry_mono', 'soentry_id');

SELECT 
    'mes_morder' AS table_name,
    COLUMN_NAME,
    COLUMN_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'mes_morder'
  AND COLUMN_NAME IN ('morder_no', 'product_code');

SELECT 
    'ASNBOLShipperDetail' AS table_name,
    COLUMN_NAME,
    COLUMN_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'ASNBOLShipperDetail'
  AND COLUMN_NAME IN ('OrdNbr', 'ContainerItem');

SELECT 
    'ShippingPlanDetail' AS table_name,
    COLUMN_NAME,
    COLUMN_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'ShippingPlanDetail'
  AND COLUMN_NAME IN ('sentry_id');

SELECT 
    'NbrMaster' AS table_name,
    COLUMN_NAME,
    COLUMN_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'NbrMaster'
  AND COLUMN_NAME IN ('WorkOrd');

SELECT 
    'PeriodSequenceDet' AS table_name,
    COLUMN_NAME,
    COLUMN_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'PeriodSequenceDet'
  AND COLUMN_NAME IN ('WorkOrds');

SELECT 
    'YearDemandManagement' AS table_name,
    COLUMN_NAME,
    COLUMN_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev' 
  AND TABLE_NAME = 'YearDemandManagement'
  AND COLUMN_NAME IN ('SAPItemNumber');