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

-- S3 first batch sample backfill from current aidopdev business tables.
-- This script is idempotent for the first validation batch and can be rerun after API generation tests.

SET @batch_id := CONCAT('S3_FIRST_BATCH_', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'));
SET @sync_time := NOW();
SET @stat_date := CURRENT_DATE();

INSERT INTO mdp_stg_purchase_order
(tenant_id, source_system, source_table, source_row_id, source_biz_key, sync_batch_id, sync_time, process_status, raw_data)
SELECT
    COALESCE(m.tenant_id, 0),
    'AIDOP',
    'PurOrdMaster/PurOrdDetail',
    CAST(d.RecID AS CHAR),
    CONCAT(IFNULL(m.PurOrd,''), '|', IFNULL(d.Line, 0)),
    @batch_id,
    @sync_time,
    'DONE',
    JSON_OBJECT('purOrd', m.PurOrd, 'line', d.Line, 'itemNum', d.ItemNum, 'supp', m.Supp, 'qtyOrded', d.QtyOrded, 'dueDate', d.DueDate)
FROM PurOrdMaster m
JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
WHERE IFNULL(m.PurOrd, '') <> ''
ON DUPLICATE KEY UPDATE
    sync_batch_id = VALUES(sync_batch_id),
    sync_time = VALUES(sync_time),
    process_status = VALUES(process_status),
    raw_data = VALUES(raw_data),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO mdp_std_purchase_order
(tenant_id, source_system, po_no, po_line, po_type, supplier_code, item_code, item_name, order_qty, received_qty, returned_qty, due_date, need_date, order_date, status, buyer, work_order, source_biz_key, sync_batch_id, sync_time)
SELECT
    COALESCE(m.tenant_id, 0),
    'AIDOP',
    IFNULL(m.PurOrd, ''),
    CAST(IFNULL(d.Line, 0) AS CHAR),
    IFNULL(m.Potype, ''),
    IFNULL(m.Supp, ''),
    IFNULL(d.ItemNum, ''),
    IFNULL(i.Descr, ''),
    IFNULL(d.QtyOrded, 0),
    IFNULL(d.QtyReceived, IFNULL(d.RctQty, 0)),
    IFNULL(d.QtyReturned, 0),
    d.DueDate,
    d.NeedDate,
    m.OrdDate,
    CASE WHEN IFNULL(LENGTH(m.Status), 0) = 0 THEN 'R' ELSE m.Status END,
    m.Buyer,
    m.WorkOrd,
    CONCAT(IFNULL(m.PurOrd,''), '|', IFNULL(d.Line, 0)),
    @batch_id,
    @sync_time
FROM PurOrdMaster m
JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
LEFT JOIN ItemMaster i ON d.ItemNum = i.ItemNum
WHERE IFNULL(m.PurOrd, '') <> ''
  AND IFNULL(d.ItemNum, '') <> ''
ON DUPLICATE KEY UPDATE
    po_type = VALUES(po_type),
    supplier_code = VALUES(supplier_code),
    item_code = VALUES(item_code),
    item_name = VALUES(item_name),
    order_qty = VALUES(order_qty),
    received_qty = VALUES(received_qty),
    returned_qty = VALUES(returned_qty),
    due_date = VALUES(due_date),
    need_date = VALUES(need_date),
    order_date = VALUES(order_date),
    status = VALUES(status),
    buyer = VALUES(buyer),
    work_order = VALUES(work_order),
    sync_batch_id = VALUES(sync_batch_id),
    sync_time = VALUES(sync_time),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO mdp_stg_delivery
(tenant_id, source_system, source_table, source_row_id, source_biz_key, sync_batch_id, sync_time, process_status, raw_data)
SELECT
    COALESCE(tenant_id, 0),
    'AIDOP',
    'srm_polist_ds',
    CAST(Id AS CHAR),
    IFNULL(dsnum, ''),
    @batch_id,
    @sync_time,
    'DONE',
    JSON_OBJECT('dsnum', dsnum, 'ponumber', ponumber, 'poline', poline, 'itemnum', itemnum, 'schedqty', schedqty, 'restqty', restqty)
FROM srm_polist_ds
WHERE IFNULL(dsnum, '') <> ''
ON DUPLICATE KEY UPDATE
    sync_batch_id = VALUES(sync_batch_id),
    sync_time = VALUES(sync_time),
    process_status = VALUES(process_status),
    raw_data = VALUES(raw_data),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO mdp_std_delivery_schedule
(tenant_id, source_system, delivery_plan_no, po_no, po_line, item_code, supplier_code, supplier_name, schedule_qty, sent_qty, rest_qty, return_qty, request_date, need_date, submit_date, last_sent_date, status, source_biz_key, sync_batch_id, sync_time)
SELECT
    COALESCE(ds.tenant_id, 0),
    'AIDOP',
    IFNULL(ds.dsnum, ''),
    IFNULL(ds.ponumber, ''),
    CAST(IFNULL(ds.poline, 0) AS CHAR),
    IFNULL(ds.itemnum, ''),
    IFNULL(ds.suppliercode, ''),
    IFNULL(ds.supplier, ''),
    IFNULL(ds.schedqty, 0),
    IFNULL(ds.sentqty, 0),
    IFNULL(ds.restqty, 0),
    IFNULL(ds.returnqty, 0),
    ds.requestdate,
    ds.needdate,
    ds.submitdate,
    ds.lastsentdate,
    ds.status,
    IFNULL(ds.dsnum, ''),
    @batch_id,
    @sync_time
FROM srm_polist_ds ds
WHERE IFNULL(ds.dsnum, '') <> ''
ON DUPLICATE KEY UPDATE
    po_no = VALUES(po_no),
    po_line = VALUES(po_line),
    item_code = VALUES(item_code),
    supplier_code = VALUES(supplier_code),
    supplier_name = VALUES(supplier_name),
    schedule_qty = VALUES(schedule_qty),
    sent_qty = VALUES(sent_qty),
    rest_qty = VALUES(rest_qty),
    return_qty = VALUES(return_qty),
    request_date = VALUES(request_date),
    need_date = VALUES(need_date),
    submit_date = VALUES(submit_date),
    last_sent_date = VALUES(last_sent_date),
    status = VALUES(status),
    sync_batch_id = VALUES(sync_batch_id),
    sync_time = VALUES(sync_time),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO dwd_supplier_delivery
(tenant_id, stat_date, po_no, po_line, po_type, supplier_code, supplier_name, item_code, item_name, order_qty, schedule_qty, delivery_qty, receipt_qty, return_qty, remaining_qty, due_date, need_date, last_delivery_date, delivery_status, source_system, sync_batch_id, calc_time)
SELECT
    COALESCE(m.tenant_id, ds.tenant_id, 0),
    @stat_date,
    IFNULL(m.PurOrd, IFNULL(ds.ponumber, '')),
    CAST(IFNULL(d.Line, IFNULL(ds.poline, 0)) AS CHAR),
    IFNULL(m.Potype, ''),
    IFNULL(m.Supp, IFNULL(ds.suppliercode, '')),
    IFNULL(ds.supplier, ''),
    IFNULL(d.ItemNum, IFNULL(ds.itemnum, '')),
    IFNULL(i.Descr, ''),
    IFNULL(d.QtyOrded, 0),
    IFNULL(ds.schedqty, 0),
    IFNULL(ds.sentqty, 0),
    IFNULL(d.QtyReceived, IFNULL(d.RctQty, 0)),
    IFNULL(ds.returnqty, IFNULL(d.QtyReturned, 0)),
    GREATEST(IFNULL(d.QtyOrded, 0) - IFNULL(d.QtyReceived, IFNULL(d.RctQty, 0)) - IFNULL(d.QtyReturned, 0), 0),
    d.DueDate,
    COALESCE(ds.needdate, d.NeedDate),
    ds.lastsentdate,
    CASE
        WHEN IFNULL(ds.status, '') = 'C' THEN 'CANCELLED'
        WHEN IFNULL(ds.restqty, 0) <= 0 THEN 'CLOSED'
        WHEN IFNULL(ds.sentqty, 0) > 0 THEN 'PARTIAL'
        ELSE 'OPEN'
    END,
    'AIDOP',
    @batch_id,
    @sync_time
FROM PurOrdMaster m
JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
LEFT JOIN srm_polist_ds ds ON d.PurOrd = ds.ponumber AND d.Line = ds.poline AND ds.isactive = 1
LEFT JOIN ItemMaster i ON d.ItemNum = i.ItemNum
WHERE IFNULL(m.PurOrd, '') <> ''
  AND IFNULL(d.ItemNum, '') <> ''
ON DUPLICATE KEY UPDATE
    po_type = VALUES(po_type),
    supplier_code = VALUES(supplier_code),
    supplier_name = VALUES(supplier_name),
    item_code = VALUES(item_code),
    item_name = VALUES(item_name),
    order_qty = VALUES(order_qty),
    schedule_qty = VALUES(schedule_qty),
    delivery_qty = VALUES(delivery_qty),
    receipt_qty = VALUES(receipt_qty),
    return_qty = VALUES(return_qty),
    remaining_qty = VALUES(remaining_qty),
    due_date = VALUES(due_date),
    need_date = VALUES(need_date),
    last_delivery_date = VALUES(last_delivery_date),
    delivery_status = VALUES(delivery_status),
    sync_batch_id = VALUES(sync_batch_id),
    calc_time = VALUES(calc_time),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO mdp_std_process_outsource_order
(tenant_id, source_system, work_order, op_code, routing_code, supplier_code, po_no, po_line, order_qty, completed_qty, due_date, status, source_biz_key, sync_batch_id, sync_time)
SELECT
    COALESCE(m.tenant_id, 0),
    'AIDOP',
    IFNULL(m.WorkOrd, ''),
    CAST(IFNULL(d.Op, 0) AS CHAR),
    IFNULL(d.ItemNum, ''),
    IFNULL(m.Supp, ''),
    IFNULL(m.PurOrd, ''),
    CAST(IFNULL(d.Line, 0) AS CHAR),
    IFNULL(d.QtyOrded, 0),
    IFNULL(d.QtyReceived, IFNULL(d.RctQty, 0)),
    d.DueDate,
    CASE WHEN IFNULL(LENGTH(m.Status), 0) = 0 THEN 'R' ELSE m.Status END,
    CONCAT(IFNULL(m.PurOrd,''), '|', IFNULL(d.Line, 0)),
    @batch_id,
    @sync_time
FROM PurOrdMaster m
JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
WHERE m.Potype = 'PW'
  AND IFNULL(m.WorkOrd, '') <> ''
  AND IFNULL(d.ItemNum, '') <> ''
ON DUPLICATE KEY UPDATE
    supplier_code = VALUES(supplier_code),
    po_no = VALUES(po_no),
    po_line = VALUES(po_line),
    order_qty = VALUES(order_qty),
    completed_qty = VALUES(completed_qty),
    due_date = VALUES(due_date),
    status = VALUES(status),
    sync_batch_id = VALUES(sync_batch_id),
    sync_time = VALUES(sync_time),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO mdp_stg_work_order_material
(tenant_id, source_system, source_table, source_row_id, source_biz_key, sync_batch_id, sync_time, process_status, raw_data)
SELECT
    0,
    'AIDOP',
    'WorkOrdTemp',
    CAST(RowNum AS CHAR),
    CONCAT(IFNULL(workord,''), '|', IFNULL(Op, 0), '|', IFNULL(ItemNum,''), '|', IFNULL(RowNum, 0)),
    @batch_id,
    @sync_time,
    'DONE',
    JSON_OBJECT('workord', workord, 'op', Op, 'itemNum', ItemNum, 'qtyRequired', QtyRequired, 'shortage', shortage, 'supplier', supplier_name)
FROM WorkOrdTemp
WHERE IFNULL(workord, '') <> ''
ON DUPLICATE KEY UPDATE
    sync_batch_id = VALUES(sync_batch_id),
    sync_time = VALUES(sync_time),
    process_status = VALUES(process_status),
    raw_data = VALUES(raw_data),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO mdp_std_material_readiness
(tenant_id, source_system, work_order, op_code, item_code, component_item_code, required_qty, issued_qty, received_qty, available_qty, in_transit_qty, incoming_qty, shortage_qty, ready_status, need_date, supplier_code, source_biz_key, sync_batch_id, sync_time)
SELECT
    0,
    'AIDOP',
    IFNULL(workord, ''),
    CAST(IFNULL(Op, 0) AS CHAR),
    IFNULL(bom_number, ''),
    COALESCE(NULLIF(ItemNum, ''), NULLIF(PMBOM, ''), CONCAT('WORKORDTEMP-', IFNULL(RowNum, 0))),
    IFNULL(QtyRequired, 0),
    IFNULL(QtyFrom, 0),
    IFNULL(QtyRec, 0),
    IFNULL(available, 0),
    IFNULL(ztsl, 0),
    IFNULL(PurOrdQty, 0),
    IFNULL(shortage, 0),
    CASE WHEN IFNULL(shortage, 0) > 0 THEN 'SHORTAGE' ELSE 'READY' END,
    COALESCE(jhshrq, hfjq, RecDate),
    supplier_number,
    CONCAT(IFNULL(workord,''), '|', IFNULL(Op, 0), '|', IFNULL(ItemNum,''), '|', IFNULL(RowNum, 0)),
    @batch_id,
    @sync_time
FROM WorkOrdTemp
WHERE IFNULL(workord, '') <> ''
ON DUPLICATE KEY UPDATE
    required_qty = VALUES(required_qty),
    issued_qty = VALUES(issued_qty),
    received_qty = VALUES(received_qty),
    available_qty = VALUES(available_qty),
    in_transit_qty = VALUES(in_transit_qty),
    incoming_qty = VALUES(incoming_qty),
    shortage_qty = VALUES(shortage_qty),
    ready_status = VALUES(ready_status),
    need_date = VALUES(need_date),
    supplier_code = VALUES(supplier_code),
    sync_batch_id = VALUES(sync_batch_id),
    sync_time = VALUES(sync_time),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO dwd_material_readiness
(tenant_id, stat_date, work_order, op_code, parent_item_code, component_item_code, component_item_name, required_qty, cumulative_required_qty, stock_available_qty, qc_pending_qty, in_transit_qty, delivery_reply_qty, available_qty, shortage_qty, ready_status, supplier_code, supplier_name, need_date, calc_batch_id, calc_time)
SELECT
    0,
    @stat_date,
    IFNULL(workord, ''),
    CAST(IFNULL(Op, 0) AS CHAR),
    IFNULL(bom_number, ''),
    COALESCE(NULLIF(ItemNum, ''), NULLIF(PMBOM, ''), CONCAT('WORKORDTEMP-', IFNULL(RowNum, 0))),
    IFNULL(ComponentItemDescr, ''),
    IFNULL(QtyRequired, 0),
    IFNULL(ljsl, 0),
    IFNULL(locStock, 0),
    IFNULL(jqsl, 0),
    IFNULL(ztsl, 0),
    IFNULL(PurOrdQty, 0),
    IFNULL(available, 0),
    IFNULL(shortage, 0),
    CASE WHEN IFNULL(shortage, 0) > 0 THEN 'SHORTAGE' ELSE 'READY' END,
    supplier_number,
    supplier_name,
    COALESCE(jhshrq, hfjq, RecDate),
    @batch_id,
    @sync_time
FROM WorkOrdTemp
WHERE IFNULL(workord, '') <> ''
ON DUPLICATE KEY UPDATE
    component_item_name = VALUES(component_item_name),
    required_qty = VALUES(required_qty),
    cumulative_required_qty = VALUES(cumulative_required_qty),
    stock_available_qty = VALUES(stock_available_qty),
    qc_pending_qty = VALUES(qc_pending_qty),
    in_transit_qty = VALUES(in_transit_qty),
    delivery_reply_qty = VALUES(delivery_reply_qty),
    available_qty = VALUES(available_qty),
    shortage_qty = VALUES(shortage_qty),
    ready_status = VALUES(ready_status),
    supplier_code = VALUES(supplier_code),
    supplier_name = VALUES(supplier_name),
    need_date = VALUES(need_date),
    calc_time = VALUES(calc_time),
    update_time = CURRENT_TIMESTAMP;

INSERT INTO dwd_material_shortage
(tenant_id, stat_date, work_order, op_code, component_item_code, shortage_qty, shortage_reason, expected_supply_date, supplier_code, related_po_no, risk_level, calc_batch_id, calc_time)
SELECT
    0,
    @stat_date,
    IFNULL(workord, ''),
    CAST(IFNULL(Op, 0) AS CHAR),
    COALESCE(NULLIF(ItemNum, ''), NULLIF(PMBOM, ''), CONCAT('WORKORDTEMP-', IFNULL(RowNum, 0))),
    IFNULL(shortage, 0),
    '当前可用量不足',
    COALESCE(jhshrq, hfjq, RecDate),
    supplier_number,
    PurOrds,
    CASE
        WHEN IFNULL(shortage, 0) > IFNULL(QtyRequired, 0) THEN 'HIGH'
        WHEN IFNULL(shortage, 0) > 0 THEN 'MEDIUM'
        ELSE 'LOW'
    END,
    @batch_id,
    @sync_time
FROM WorkOrdTemp
WHERE IFNULL(workord, '') <> ''
  AND IFNULL(shortage, 0) > 0;