BOM 物料清单同步：主表与子件双表同步

一、BOM 业务背景

物料清单（Bill of Materials，BOM）  是生产制造企业的核心数据，描述了产品与原材料之间的组成关系。一条 BOM 记录包含：

• 主表（父件） ：产品编码、名称、版本、产出数量、工艺路线
• 子件表（明细） ：子件编码、用量、损耗率、是否虚拟件、发料仓库

畅捷通 T+ 的 BOM API 一次返回包含父子信息的嵌套结构，同步时需要拆分为两张表存储。

二、数据模型设计

三、API 接口详情

POST https://openapi.chanjet.com/tplus/api/v2/bom/QueryPage
Headers:
  appKey: {appKey}
  appSecret: {appSecret}
  openToken: {accessToken}
Content-Type: application/json

{
    "param": {
        "PageSize": 100,
        "PageIndex": 1,
        "Selectfields": "ID,Code,Name,Specification,
                         Unit.Code,Unit.Name,
                         Version,ProduceQuantity,
                         IsDefaultBom,IsPhantom,Disabled,
                         Routing.Code,Routing.Name,
                         Manufactureplant.Code,Manufactureplant.Name,
                         WarehouseIn.Code,WarehouseIn.Name,
                         Maker,Auditor,
                         CreateDate,UpdateDate,AuditedDate,
                         BOMChilds"
    }
}
​

四、嵌套数据解析

4.1 API 响应结构

{
    "Data": [
        {
            "ID": 1001,
            "Code": "FG-001",
            "Name": "成品A",
            "Specification": "100mm×200mm",
            "Unit": {
                "Code": "1",
                "Name": "个"
            },
            "Version": "V1.0",
            "ProduceQuantity": 1.0,
            "IsDefaultBom": true,
            "Manufactureplant": {
                "Code": "CJ01",
                "Name": "生产车间"
            },
            "BOMChilds": [
                {
                    "LineNo": 1,
                    "ChildCode": "RM-001",
                    "ChildName": "原料X",
                    "ChildSpec": "",
                    "Unit": {
                        "Code": "4",
                        "Name": "公斤"
                    },
                    "Quantity": 2.5,
                    "WasteRate": 0.03,
                    "IsPhantom": false,
                    "BackflushMaterial": true,
                    "WarehouseOut": {
                        "Code": "CK01",
                        "Name": "原料仓"
                    },
                    "Process": {
                        "Code": "GX01",
                        "Name": "投料工序"
                    }
                },
                {
                    "LineNo": 2,
                    "ChildCode": "RM-002",
                    "ChildName": "辅料Y",
                    "Quantity": 0.5
                }
            ]
        }
    ]
}
​

4.2 解析流程

4.3 核心代码

foreach ($rows as $bom) {
    // 1. 写入主表
    $tplusId = intval($bom['ID'] ?? 0);
    $bomStmt->execute([
        ':tplus_id'               => $tplusId,
        ':parent_code'            => $bom['Code'] ?? '',
        ':parent_name'            => $bom['Name'] ?? '',
        ':parent_spec'            => $bom['Specification'] ?? '',
        ':unit_code'              => $bom['Unit']['Code'] ?? '',
        ':unit_name'              => $bom['Unit']['Name'] ?? '',
        ':version'                => $bom['Version'] ?? '',
        ':produce_quantity'       => floatval($bom['ProduceQuantity'] ?? 0),
        ':is_default'             => toBool($bom['IsDefaultBom'] ?? false),
        ':is_phantom'             => toBool($bom['IsPhantom'] ?? false),
        ':disabled'               => intval($bom['Disabled'] ?? 0),
        ':routing_code'           => $bom['Routing']['Code'] ?? '',
        ':routing_name'           => $bom['Routing']['Name'] ?? '',
        ':manufacturer_plant_code'=> $bom['Manufactureplant']['Code'] ?? '',
        ':manufacturer_plant_name'=> $bom['Manufactureplant']['Name'] ?? '',
        ':warehouse_in_code'      => $bom['WarehouseIn']['Code'] ?? '',
        ':warehouse_in_name'      => $bom['WarehouseIn']['Name'] ?? '',
        ':maker'                  => $bom['Maker'] ?? '',
        ':auditor'                => $bom['Auditor'] ?? '',
        ':create_date'            => $bom['CreateDate'] ?? '',
        ':update_date'            => $bom['UpdateDate'] ?? '',
        ':audited_date'           => $bom['AuditedDate'] ?? '',
    ]);

    // 2. 写入子件表
    $children = $bom['BOMChilds'] ?? [];
    foreach ($children as $child) {
        $detailStmt->execute([
            ':bom_id'              => $tplusId,
            ':line_no'             => intval($child['LineNo'] ?? 0),
            ':child_code'          => $child['ChildCode'] ?? '',
            ':child_name'          => $child['ChildName'] ?? '',
            ':child_spec'          => $child['ChildSpec'] ?? '',
            ':unit_code'           => $child['Unit']['Code'] ?? '',
            ':unit_name'           => $child['Unit']['Name'] ?? '',
            ':quantity'            => floatval($child['Quantity'] ?? 0),
            ':waste_rate'          => floatval($child['WasteRate'] ?? 0),
            ':is_phantom'          => toBool($child['IsPhantom'] ?? false),
            ':backflush_material'  => toBool($child['BackflushMaterial'] ?? false),
            ':warehouse_out_code'  => $child['WarehouseOut']['Code'] ?? '',
            ':warehouse_out_name'  => $child['WarehouseOut']['Name'] ?? '',
            ':process_code'        => $child['Process']['Code'] ?? '',
            ':process_name'        => $child['Process']['Name'] ?? '',
        ]);
    }
}
​

五、关联查询应用

同步后，可以通过 SQL 轻松查询 BOM 结构：

5.1 查询某产品的完整 BOM

SELECT
    b.parent_code,
    b.parent_name,
    d.line_no,
    d.child_code,
    d.child_name,
    d.quantity,
    d.waste_rate,
    d.unit_name
FROM tplus_bom b
JOIN tplus_bom_detail d ON d.bom_id = b.tplus_id
WHERE b.parent_code = 'FG-001'
ORDER BY d.line_no;
​

5.2 查询某原料被哪些产品使用（反查）

SELECT
    b.parent_code,
    b.parent_name,
    d.quantity,
    d.waste_rate
FROM tplus_bom b
JOIN tplus_bom_detail d ON d.bom_id = b.tplus_id
WHERE d.child_code = 'RM-001';
​

5.3 统计 BOM 用量汇总

SELECT
    child_code,
    child_name,
    COUNT(DISTINCT bom_id) AS usage_count,
    SUM(quantity * (1 + waste_rate)) AS total_qty
FROM tplus_bom_detail
GROUP BY child_code, child_name
ORDER BY usage_count DESC;
​

六、同步结果

表	记录数	说明
tplus_bom	95 条	物料清单主表
tplus_bom_detail	526 条	子件明细，平均每条 BOM 含 5.5 个子件