User Master Data

Basic Information

Response Fields

ERP Field	MES Field	Description
employee_no	MV001	Employee number (Primary Key)
employee_name	MV002	Employee name
department_no	MV004	Department number
email	MV020	Email address
resignation_date	MV022	Resignation date
tran_status	TRAN_STATUS	Transaction status

Response Example

{
  "user_data": [
    {
      "employee_no": "EMP001",
      "employee_name": "John Smith",
      "department_no": "DEPT01",
      "email": "emp001@company.com",
      "resignation_date": "",
      "tran_status": "AU"
    }
  ]
}

Database Update SQL

Check Insert or Update

SELECT COUNT(1) FROM ERP_CMSMV WHERE MV001 = N'{employee_no}'
-- count > 0 → UPDATE, count = 0 → INSERT

INSERT (Add Employee)

INSERT INTO ERP_CMSMV (MV001, MV002, MV004, MV020, MV022)
VALUES (
    :userId,
    :userName,
    '',             -- Department default empty
    '',             -- Email default empty
    ''              -- Resignation date default empty
)

UPDATE (Modify Employee)

UPDATE ERP_CMSMV SET
    MV002 = :userName
WHERE MV001 = :userId

SFT_PRINCIPAL (System Account Linked Update)

-- Add system account
INSERT INTO {EMESSYS}..SFT_PRINCIPAL (
    AUTH_ID, AUTH_NAME, AUTH_PASSWORD, USEDSTATE,
    AUTH_DESCRIPTION, Creator, CreateDate, PHONE, MAIL, WECHAT
) VALUES (
    :userId, :userName, :encryptPassWord, :status,
    :description, 'ESB', GETDATE(), :phone, :mail, :weChat
)

-- Modify system account
UPDATE {EMESSYS}..SFT_PRINCIPAL SET
    AUTH_NAME = :userName,
    AUTH_PASSWORD = :encryptPassWord,
    USEDSTATE = :status,
    AUTH_DESCRIPTION = :description,
    PHONE = :phone,
    MAIL = :mail,
    WECHAT = :weChat
WHERE AUTH_ID = :userId