item.supplier.get - Item Supplier Data

Basic Information

API Name

item.supplier.get

Method Name

upItemSupplierCROSS

Target Table

material_catalog

JSON Node

item_supplier_data

Primary Key

material_id (item_no)

Code Location

RegularESB.java:4225

Response Fields

ERP Field	MES Field	Description
`item_no`	material_id	Item number (Primary Key)
`modify_no`	-	Modified by
`tran_status`	TRAN_STATUS	Transaction status

Response Example

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

Processing Logic

Depends on ITEM Table

This API first queries the ITEM table to retrieve item name (NAME), specification (DESCRIPTION), unit (UNIT), etc. If the item number does not exist in the ITEM table, that record is skipped (continue).

Database Update SQL

Pre-query ITEM Table

-- First retrieve item basic information
SELECT NAME, DESCRIPTION, UNIT FROM ITEM WHERE ID = :item_no

-- If ITEM does not exist, skip (continue)

Check Insert or Update

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

INSERT (Add Item Supplier)

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,        -- From ITEM.NAME
    :norm,                 -- From ITEM.DESCRIPTION
    :unit_no,              -- From ITEM.UNIT
    :operation_id,
    :operation_name,
    N'{current_time}',
    :oper_id,
    :mc01, :mc02, :mc03, :mc04
)

UPDATE (Modify Item Supplier)

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