使用者基本資料

基本資訊

回應欄位

ERP 欄位	MES 欄位	說明
employee_no	MV001	員工編號（主鍵）
employee_name	MV002	員工名稱
department_no	MV004	部門編號
email	MV020	電子郵件
resignation_date	MV022	離職日期
tran_status	TRAN_STATUS	交易狀態

回應範例

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

資料庫更新 SQL

判斷新增或修改

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

INSERT（新增員工）

INSERT INTO ERP_CMSMV (MV001, MV002, MV004, MV020, MV022)
VALUES (
    :userId,
    :userName,
    '',             -- 部門預設空
    '',             -- 郵件預設空
    ''              -- 離職日期預設空
)

UPDATE（修改員工）

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

SFT_PRINCIPAL（系統帳號連動）

-- 新增系統帳號
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
)

-- 修改系統帳號
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