料件供應商資料

基本資訊

回應欄位

回應範例

{
  "item_supplier_data": [
    {
      "item_no": "MAT001",
      "modify_no": "USER001",
      "tran_status": "AU"
    }
  ]
}

處理邏輯

資料庫更新 SQL

前置查詢 ITEM 表

-- 先取得品號基本資訊
SELECT NAME, DESCRIPTION, UNIT FROM ITEM WHERE ID = :item_no

-- 若 ITEM 不存在則跳過（continue）

判斷新增或修改

SELECT COUNT(1) FROM material_catalog WHERE material_id = N'{item_no}'
-- count > 0 → UPDATE, count = 0 → INSERT

INSERT（新增料件供應商）

INSERT INTO material_catalog (
    material_id, material_name, norm, unit_no,
    operation_id, operation_name, oper_date, oper_id,
    mc01, mc02, mc03, mc04
) VALUES (
    :material_id,
    :material_name,        -- 從 ITEM.NAME 取得
    :norm,                 -- 從 ITEM.DESCRIPTION 取得
    :unit_no,              -- 從 ITEM.UNIT 取得
    :operation_id,
    :operation_name,
    N'{當前時間}',
    :oper_id,
    :mc01, :mc02, :mc03, :mc04
)

UPDATE（修改料件供應商）

UPDATE material_catalog SET
    material_name = :material_name,
    norm = :norm,
    unit_no = :unit_no,
    operation_id = :operation_id,
    operation_name = :operation_name,
    mc01 = :mc01,
    mc02 = :mc02,
    mc03 = :mc03,
    mc04 = :mc04
WHERE material_id = :material_id