conversion.unit.get - Item Conversion Unit

Basic Information

API Name

conversion.unit.get

Method Name

upConversionUnitCROSS

Target Table

ITEM_CONVERSION_UNIT

JSON Node

Conversionunit

Primary Key

ITEM_NO + CONVERSION_UNIT

Code Location

RegularESB.java (new)

Request Parameters

Field	Type	Required	Description
`data_time`	String	Required	Data modification start date (format: yyyy-MM-dd)

data_time Parameter Logic

• First execution: Uses current date
• Subsequent executions: Uses LAST_SUCCESS_TIME from EAI_PARAMETER table
• Immediate import: Uses frontend-specified date

Response Fields

ERP Field	MES Field	Type	Description
`item_no`	ITEM_NO	NVARCHAR(40)	Item number (composite key)
`conversion_unit`	CONVERSION_UNIT	NVARCHAR(10)	Conversion unit (composite key)
`molecular`	CONVERSION_RATE_MOLECULAR	DECIMAL(18,6)	Conversion rate numerator
`denominator`	CONVERSION_RATE_DENOMINATOR	DECIMAL(18,6)	Conversion rate denominator
`remark`	REMARK	NVARCHAR(255)	Remark
`tran_status`	TRAN_STATUS	NVARCHAR(10)	Transaction status

Response Example

{
  "Conversionunit": [
    {
      "item_no": "ITEM-001",
      "conversion_unit": "BOX",
      "molecular": "1",
      "denominator": "500",
      "remark": "500 pieces per box",
      "tran_status": "AU"
    }
  ]
}

Processing Logic

Composite Primary Key Design

Uses ITEM_NO (item number) + CONVERSION_UNIT (conversion unit) as composite primary key.

The same item can have multiple conversion units, for example:
• ITEM-001 + BOX (box)
• ITEM-001 + CTN (carton)
• ITEM-001 + PAL (pallet)

Denominator Protection

Conversion rate denominator (DENOMINATOR) cannot be 0, program will automatically set to 1:
IF denominator = 0 THEN denominator = 1

EAI_PARAMETER Record

After first successful synchronization, system will create a record in EAI_PARAMETER table,
subsequent executions will automatically use LAST_SUCCESS_TIME as data_time parameter.

Conversion Rate Calculation

Conversion Formula

Conversion Rate = Numerator (MOLECULAR) / Denominator (DENOMINATOR)

Example: 500 PCS = 1 BOX
Numerator = 1, Denominator = 500, Conversion Rate = 0.002

Database Update SQL

Check Insert or Update

SELECT COUNT(1) FROM ITEM_CONVERSION_UNIT
WHERE ITEM_NO = N'{item_no}' AND CONVERSION_UNIT = N'{conversion_unit}'
-- count > 0 → UPDATE, count = 0 → INSERT

INSERT (Add Conversion Unit)

INSERT INTO ITEM_CONVERSION_UNIT (
    ITEM_NO, CONVERSION_UNIT,
    CONVERSION_RATE_MOLECULAR, CONVERSION_RATE_DENOMINATOR,
    REMARK, TRAN_STATUS, SYNC_TIME, CREATE_TIME, UPDATE_TIME
) VALUES (
    :ITEM_NO,
    :CONVERSION_UNIT,
    :molecular,
    :denominator,
    :remark,
    :tran_status,
    N'{current_time}',
    N'{current_time}',
    N'{current_time}'
)

UPDATE (Modify Conversion Unit)

UPDATE ITEM_CONVERSION_UNIT SET
    CONVERSION_RATE_MOLECULAR = :molecular,
    CONVERSION_RATE_DENOMINATOR = :denominator,
    REMARK = :remark,
    TRAN_STATUS = :tran_status,
    UPDATE_TIME = N'{current_time}'
WHERE ITEM_NO = :ITEM_NO AND CONVERSION_UNIT = :CONVERSION_UNIT

Field Default Values

If ERP does not return the following fields, default values are applied:
• CONVERSION_RATE_MOLECULAR = 1
• CONVERSION_RATE_DENOMINATOR = 1 (cannot be 0)
• REMARK = '' (empty string)
• TRAN_STATUS = '' (empty string)