Case 1：Shop Order, SAPME

MES @ HTKS, China

Description

SAPME 是 MES 解決方案之一，其資料庫系統建構在 Oracle、MS SQL Server 或 SAP HANA 上。資料的核心價值是工單 (SHOP_ORDER) 與 SFC，SAPME 透過工單與 SFC 管理工廠的製造流程，並對生產過程進行追蹤，達到貨暢其流的目標。

不論是進行中或已結案的工單，從基本資料如 BOM 到生產交易訊息如 SFC_STEP 皆儲存在資料庫中，隨著營運時間日益增長，累積的大量資料漸漸成為系統的負擔並造成效能的降低，為了解決此問題，並配合分析報表的需求，將已結案超過有效保留時間的工單轉移至備份區，一來能達到資料減量、降低系統營運減壓，同時分析報表也不再需要 100% 依賴線上系統進行製作。

等待轉移的工單，其相關訊息大致分成兩大類：基本資料 與 交易資料。

• 基本資料：與工單的關係為 間接參考，如 ITEM、BOM、BOM_COMPONENT。此類資料與工單的交易行為無直接關聯，但在系統營運過程中會因為產線上的需求不停被調整。因此 基本資料 在同步策略上會採取 "複製"，持續更新至備份區，並保證原始數據不會被刪除。

• 交易資料 ：與工單的關係為 直接關聯，如 ACTIVITY_LOG、SFC、SFC_STEP 或是客製化表格，此類資料在生產操作過程如過站、上下料、退回、入庫 ... 時被動態新增或更新，亦會隨著工單結案後一起被凍結。因此 交易資料 在同步策略上會採取 "轉移"，在備份區保留最後的結果，轉移成功後把原始數據刪除。

Design - Shop Order

完成一張工單的 "轉移" 作業，會依據下面步驟進行分析並制定計畫：

1. 建立交易的關聯 (Build Relation of Transactions)

2. 確認表格是否合規 (Confirm Tables)

3. 配置資料庫與執行工作 (Plan Databases and Jobs)

Build Relation of Transactions

關聯的分析，可根據需求與系統實際設計進行推測，例如：

1. SHOP_ORDER 從 ERP 開立時，會同時建立 LOT 與 WAFER 的關聯。

2. SHOP_ORDER 下線會產生 SFC，每個 SFC 會各自要生產的 WAFER 清單。

3. SFC 下線後會產生 SFC_STEP、DISPATCH_SFC 等過站基本資料。

4. SFC 在各站點會有過站紀錄，包括 Move In/Out、Track In/Out 、Material & Tooling 的上下等等。

5. SFC 內的 WAFER 會有統計品管數據，並在包裝站時被拆批分散到多個包裝箱內。

6. ...

以下為 HTKS MES 建立的的關聯模型：

<taskSpace>
  <task name="HOLD_DETAIL">
    <sourceSelect table="HOLD_DETAIL" />
    <targetUpdate />
    <next>
      <plan taskName="ZR_HOLD_SFC" where="HOLD_DETAIL_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
    </next>
  </task>
  <task name="SHOP_ORDER">
    <sourceSelect table="SHOP_ORDER" />
    <targetUpdate />
    <next>
      <plan taskName="ACTIVITY_LOG" where="SHOP_ORDER_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="SHOP_ORDER_SFC" where="SHOP_ORDER_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="SFC" where="SHOP_ORDER_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZD_CUSTOMER_ITEM" where="SHOP_ORDER_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_SHOP_ORDER_ITEMSET_LOG" where="SHOP_ORDER_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
    </next>
  </task>
  <task name="SHOP_ORDER_SFC">
    <sourceSelect table="SHOP_ORDER_SFC" />
    <targetUpdate />
  </task>
  <task name="ACTIVITY_LOG">
    <sourceSelect table="ACTIVITY_LOG" />
    <targetUpdate />
  </task>
  <task name="SFC">
    <sourceSelect table="SFC" />
    <targetUpdate />
    <next>
      <!-- WIP -->
      <plan taskName="SFC_BOM" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="SFC_ROUTING" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="HOLD_DETAIL" where="HOLD_CONTEXT_G_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <!-- HTKS -->
      <plan taskName="ZD_SFC" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_BINMAP_COMBINE" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_DC_RESULT" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_DISPATCH_SFC" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_RUN_DEFECT" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_RUN_JUMP" where="RUN_SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_RUN_MT_SFC" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_RUN_SFC" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_RUN_SPLIT_MERGE" where="BEF_SOURCE_SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_SAMPLING_RESULT" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_BINMAP_COMBINE" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_SFC_LOG" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_SPC_ALARM" where="SFC_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="ZR_SPC_ALARM_MAIL" where="SFC=?">
        <param sourceColumn="SFC" />
      </plan>
    </next>
  </task>
  <task name="SFC_BOM">
    <sourceSelect table="SFC_BOM" />
    <targetUpdate />
  </task>
  <task name="SFC_ROUTING">
    <sourceSelect table="SFC_ROUTING" />
    <targetUpdate />
    <next>
      <plan taskName="SFC_ROUTER" where="SFC_ROUTING_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
    </next>
  </task>
  <task name="SFC_ROUTER">
    <sourceSelect table="SFC_ROUTER" />
    <targetUpdate />
    <next>
      <plan taskName="SFC_STEP" where="SFC_ROUTER_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
    </next>
  </task>
  <task name="SFC_STEP">
    <sourceSelect table="SFC_STEP" />
    <targetUpdate />
  </task>

  <task name="ZD_CUSTOMER_ITEM">
    <sourceSelect table="ZD_CUSTOMER_ITEM" />
    <targetUpdate />
    <next>
      <plan taskName="ZD_SFC_ITEM" where="CUSTOMER_ITEM_BO=?">
        <param sourceColumn="ID" />
      </plan>
      <plan taskName="ZD_SFC_ITEM_GRADE" where="CUSTOMER_ITEM_BO=?">
        <param sourceColumn="ID" />
      </plan>
      <plan taskName="ZR_CUSTOMER_ITEM_DEFECT" where="CUSTOMER_ITEM_BO=?">
        <param sourceColumn="ID" />
      </plan>
      <plan taskName="ZR_CUSTOMER_ITEM_GRADE" where="CUSTOMER_ITEM_BO=?">
        <param sourceColumn="ID" />
      </plan>
      <plan taskName="ZR_SAMPLING_RESULT_DETAIL" where="CUSTOMER_ITEM_BO=?">
        <param sourceColumn="ID" />
      </plan>
      <plan taskName="ZR_RUN_SFC_ITEM" where="CUSTOMER_ITEM_BO=?">
        <param sourceColumn="ID" />
      </plan>
      <plan taskName="ZR_RUN_SPLIT_MERGE_ITEM" where="CUSTOMER_ITEM_BO=?">
        <param sourceColumn="ID" />
      </plan>
    </next>
  </task>
  <task name="ZD_CUSTOMER_ITEMSET">
    <sourceSelect table="ZD_CUSTOMER_ITEMSET" />
    <targetUpdate />
  </task>
  <task name="ZD_SFC">
    <sourceSelect table="ZD_SFC" />
    <targetUpdate />
  </task>
  <task name="ZD_SFC_ITEM">
    <sourceSelect table="ZD_SFC_ITEM" />
    <targetUpdate />
  </task>
  <task name="ZD_SFC_ITEM_GRADE">
    <sourceSelect table="ZD_SFC_ITEM_GRADE" />
    <targetUpdate />
  </task>
  <task name="ZD_SHOP_ORDER_ITEMSET">
    <sourceSelect table="ZD_SHOP_ORDER_ITEMSET" />
    <targetUpdate />
  </task>
  <task name="ZR_BINMAP_COMBINE">
    <sourceSelect table="ZR_BINMAP_COMBINE" />
    <targetUpdate />
  </task>
  <task name="ZR_BINMAP_COMBINE_OPERATION">
    <sourceSelect table="ZR_BINMAP_COMBINE_OPERATION" />
    <targetUpdate />
  </task>
  <task name="ZR_CUSTOMER_ITEM_DEFECT">
    <sourceSelect table="ZR_CUSTOMER_ITEM_DEFECT" />
    <targetUpdate />
  </task>
  <task name="ZR_CUSTOMER_ITEM_GRADE">
    <sourceSelect table="ZR_CUSTOMER_ITEM_GRADE" />
    <targetUpdate />
  </task>
  <task name="ZR_DC_RESULT">
    <sourceSelect table="ZR_DC_RESULT" />
    <targetUpdate />
    <next>
      <plan taskName="ZR_DC_RESULT_DETAIL" where="DC_RESULT_BO=?">
        <param sourceColumn="ID" />
      </plan>
      <plan taskName="ZR_DC_RESULT_RAW" where="DC_RESULT_BO=?">
        <param sourceColumn="ID" />
      </plan>
      <plan taskName="ZR_DC_RESULT_STAT" where="DC_RESULT_BO=?">
        <param sourceColumn="ID" />
      </plan>
    </next>
  </task>
  <task name="ZR_DC_RESULT_DETAIL">
    <sourceSelect table="ZR_DC_RESULT_DETAIL" />
    <targetUpdate />
  </task>
  <task name="ZR_DC_RESULT_RAW">
    <sourceSelect table="ZR_DC_RESULT_RAW" />
    <targetUpdate />
  </task>
  <task name="ZR_DC_RESULT_STAT">
    <sourceSelect table="ZR_DC_RESULT_STAT" />
    <targetUpdate />
  </task>
  <task name="ZR_DISPATCH_SFC">
    <sourceSelect table="ZR_DISPATCH_SFC" />
    <targetUpdate />
  </task>
  <task name="ZR_HOLD_RELEASE">
    <sourceSelect table="ZR_HOLD_RELEASE" />
    <targetUpdate />
    <next>
      <plan taskName="ZR_HOLD_RELEASE_SFC" where="HOLD_RELEASE_BO=?">
        <param sourceColumn="ID" />
      </plan>
      <plan taskName="ZR_HOLD_RELEASE_SFC_ITEM" where="HOLD_RELEASE_BO=?">
        <param sourceColumn="ID" />
      </plan>
    </next>
  </task>
  <task name="ZR_HOLD_RELEASE_SFC">
    <sourceSelect table="ZR_HOLD_RELEASE_SFC" />
    <targetUpdate />
  </task>
  <task name="ZR_HOLD_RELEASE_SFC_ITEM">
    <sourceSelect table="ZR_HOLD_RELEASE_SFC_ITEM" />
    <targetUpdate />
  </task>
  <task name="ZR_HOLD_RELEASE_SFC_ITEM_STATE">
    <sourceSelect table="ZR_HOLD_RELEASE_SFC_ITEM_STATE" />
    <targetUpdate />
  </task>
  <task name="ZR_HOLD_SFC">
    <sourceSelect table="ZR_HOLD_SFC" />
    <targetUpdate />
  </task>
  <task name="ZR_PKG_CONTAINER">
    <sourceSelect table="ZR_PKG_CONTAINER" />
    <targetUpdate />
    <next>
      <plan taskName="ZR_PKG_CONTAINER_LOG" where="ID=?">
        <param sourceColumn="ID" />
      </plan>
      <plan taskName="ZR_PKG_CONTAINER_LABEL" where="CONTAINER_BO=?">
        <param sourceColumn="ID" />
      </plan>
      <plan taskName="ZR_PKG_CONTAINER_SFC" where="CONTAINER_BO=?">
        <param sourceColumn="ID" />
      </plan>
    </next>
  </task>
  <task name="ZR_PKG_CONTAINER_LABEL">
    <sourceSelect table="ZR_PKG_CONTAINER_LABEL" />
    <targetUpdate />
  </task>
  <task name="ZR_PKG_CONTAINER_LOG">
    <sourceSelect table="ZR_PKG_CONTAINER_LOG" />
    <targetUpdate />
  </task>
  <task name="ZR_PKG_CONTAINER_SFC">
    <sourceSelect table="ZR_PKG_CONTAINER_SFC" />
    <targetUpdate />
  </task>
  <task name="ZR_PKG_CONTAINER_SFC_LOG">
    <sourceSelect table="ZR_PKG_CONTAINER_SFC_LOG" />
    <targetUpdate />
  </task>
  <task name="ZR_RUN_DEFECT">
    <sourceSelect table="ZR_RUN_DEFECT" />
    <targetUpdate />
  </task>
  <task name="ZR_RUN_JUMP">
    <sourceSelect table="ZR_RUN_JUMP" />
    <targetUpdate />
  </task>
  <task name="ZR_RUN_MT_SFC">
    <sourceSelect table="ZR_RUN_MT_SFC" />
    <targetUpdate />
  </task>
  <task name="ZR_RUN_SFC">
    <sourceSelect table="ZR_RUN_SFC" />
    <targetUpdate />
  </task>
  <task name="ZR_RUN_SFC_ITEM">
    <sourceSelect table="ZR_RUN_SFC_ITEM" />
    <targetUpdate />
  </task>
  <task name="ZR_RUN_SPLIT_MERGE">
    <sourceSelect table="ZR_RUN_SPLIT_MERGE" />
    <targetUpdate />
  </task>
  <task name="ZR_RUN_SPLIT_MERGE_ITEM">
    <sourceSelect table="ZR_RUN_SPLIT_MERGE_ITEM" />
    <targetUpdate />
  </task>
  <task name="ZR_SAMPLING_RESULT">
    <sourceSelect table="ZR_SAMPLING_RESULT" />
    <targetUpdate />
  </task>
  <task name="ZR_SAMPLING_RESULT_DETAIL">
    <sourceSelect table="ZR_SAMPLING_RESULT_DETAIL" />
    <targetUpdate />
  </task>
  <task name="ZR_SFC_LOG">
    <sourceSelect table="ZR_SFC_LOG" />
    <targetUpdate />
  </task>
  <task name="ZR_SHOP_ORDER_ITEMSET_LOG">
    <sourceSelect table="ZR_SHOP_ORDER_ITEMSET_LOG" />
    <targetUpdate />
  </task>
  <task name="ZR_SPC_ALARM">
    <sourceSelect table="ZR_SPC_ALARM" />
    <targetUpdate />
  </task>
  <task name="ZR_SPC_ALARM_MAIL">
    <sourceSelect table="ZR_SPC_ALARM_MAIL" />
    <targetUpdate />
  </task>

  <!-- PMS -->
  <task name="Z_PMS_CHECKLIST">
    <sourceSelect table="Z_PMS_CHECKLIST" />
    <targetUpdate truncate="true" />
  </task>
  <task name="Z_PMS_CHECKLIST_PARAMETER">
    <sourceSelect table="Z_PMS_CHECKLIST_PARAMETER" />
    <targetUpdate truncate="true" />
  </task>
  <task name="Z_PMS_FILE">
    <sourceSelect table="Z_PMS_FILE" />
    <targetUpdate truncate="true" />
  </task>
  <task name="Z_PMS_JOB">
    <sourceSelect table="Z_PMS_JOB" />
    <targetUpdate />
    <next>
      <plan taskName="Z_PMS_JOB_CHECKLIST_PARAMETER" where="JOB_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
      <plan taskName="Z_PMS_JOB_RESOURCE_CHECKLIST" where="JOB_BO=?">
        <param sourceColumn="HANDLE" />
      </plan>
    </next>
  </task>
  <task name="Z_PMS_JOB_CHECKLIST_PARAMETER">
    <sourceSelect table="Z_PMS_JOB_CHECKLIST_PARAMETER" />
    <targetUpdate />
  </task>
  <task name="Z_PMS_JOB_OPERATION_LOG">
    <sourceSelect table="Z_PMS_JOB_OPERATION_LOG" />
    <targetUpdate />
  </task>
  <task name="Z_PMS_JOB_RESOURCE_CHECKLIST">
    <sourceSelect table="Z_PMS_JOB_RESOURCE_CHECKLIST" />
    <targetUpdate />
  </task>
  <task name="Z_PMS_PLAN">
    <sourceSelect table="Z_PMS_PLAN" />
    <targetUpdate truncate="true" />
  </task>
  <task name="Z_PMS_PLAN_CHANGE">
    <sourceSelect table="Z_PMS_PLAN_CHANGE" />
    <targetUpdate />
  </task>
  <task name="Z_PMS_RESOURCE_MAINTENANCE">
    <sourceSelect table="Z_PMS_RESOURCE_MAINTENANCE" />
    <targetUpdate truncate="true" />
  </task>
  <task name="Z_PMS_RESOURCE_MOVE_LOG">
    <sourceSelect table="Z_PMS_RESOURCE_MOVE_LOG" />
    <targetUpdate />
  </task>
  <task name="Z_PMS_RESOURCE_SPAREPARTS_MAINTENANCE">
    <sourceSelect table="Z_PMS_RESOURCE_SPAREPARTS_MAINTENANCE" />
    <targetUpdate truncate="true" />
  </task>
  <task name="Z_PMS_RESOURCE_TYPE_DATA">
    <sourceSelect table="Z_PMS_RESOURCE_TYPE_DATA" />
    <targetUpdate truncate="true" />
  </task>
  <task name="Z_PMS_SPAREPARTS_PARAMETER">
    <sourceSelect table="Z_PMS_SPAREPARTS_PARAMETER" />
    <targetUpdate truncate="true" />
  </task>
  <task name="Z_PMS_STATUS_CHANGE">
    <sourceSelect table="Z_PMS_STATUS_CHANGE" />
    <targetUpdate truncate="true" />
  </task>
  <task name="Z_PMS_STATUS_CHANGE_LOG">
    <sourceSelect table="Z_PMS_STATUS_CHANGE_LOG" />
    <targetUpdate />
  </task>
</taskSpace>

Confirm Tables

資料能順利轉移的前提是來源與目標資料表必須具備 主鍵，很不幸的是 SAPME WIP 在設計上雖然對資料有唯一鍵值的規範與約束，但資料表本身並沒有主鍵的設定，需要額外補充資料表的主鍵訊息。

以下是對資料表進行主鍵的設定：

<tableSpace>
  <table name="ACTIVITY_LOG">
    <pk>
      <column>SHOP_ORDER_BO</column>
      <column>PARTITION_DATE</column>
    </pk>
  </table>
  <table name="HOLD_DETAIL">
    <pk>
      <column>HANDLE</column>
    </pk>
  </table>
  <table name="SFC">
    <pk>
      <column>HANDLE</column>
    </pk>
  </table>
  <table name="SFC_BOM">
    <pk>
      <column>HANDLE</column>
    </pk>
  </table>
  <table name="SFC_ROUTER">
    <pk>
      <column>HANDLE</column>
    </pk>
  </table>
  <table name="SFC_ROUTING">
    <pk>
      <column>HANDLE</column>
    </pk>
  </table>
  <table name="SFC_STEP">
    <pk>
      <column>HANDLE</column>
    </pk>
  </table>
  <table name="SHOP_ORDER">
    <pk>
      <column>HANDLE</column>
    </pk>
  </table>
  <table name="SHOP_ORDER_SFC">
    <pk>
      <column>HANDLE</column>
    </pk>
  </table>
</tableSpace>

Plan Databases and Jobs

Databases

根據需求設定資料來源與轉移目的的資料庫訊息：

<databaseSpace>
  <database>
    <id>IDLE</id>
    <host>0.0.0.0</host>
    <port>0</port>
    <dbName></dbName>
    <user></user>
    <password></password>
    <driverClass>uia.tmd.IdleAccess</driverClass>
  </database>
  <database>
    <id>HANA_PROD</id>
    <host>10.160.2.20</host>
    <port>30015</port>
    <dbName>WIP</dbName>
    <user>WIP</user>
    <password>Sap12345</password>
    <driverClass>uia.tmd.access.HanaAccess</driverClass>
  </database>
  <database>
    <id>HANA_ARCHIVE_TEST</id>
    <host>10.160.2.23</host>
    <port>31015</port>
    <dbName>WIP_ARCHIVE</dbName>
    <user>WIP_ARCHIVE</user>
    <password>Sap54321</password>
    <driverClass>uia.tmd.access.HanaAccess</driverClass>
  </database>
  <database>
    <id>HANA_ARCHIVE_PROD</id>
    <host>10.160.2.23</host>
    <port>32015</port>
    <dbName>WIP_ARCHIVE</dbName>
    <user>WIP_ARCHIVE</user>
    <password>Sap54321</password>
    <driverClass>uia.tmd.access.HanaAccess</driverClass>
  </database>
</databaseSpace>

Jobs

根據需求設定工作的執行週期、工作項、資料來源與轉移目的，如：

1. SHOP_ODER：同步工單，條件：結案超過 140 天。

2. SHOP_ORDER_DELETE：刪除工單（特殊措施）。

3. PKG_CONTAINER：同步包裝，條件：結案超過 100 天。

<jobSpace>
  <job name="SHOP_ORDER" 
       source="HANA_PROD" 
       target="HANA_ARCHIVE_TEST" 
       itemRunner="ui.tmd.zztop.TxKeyItemRunner">
    <item taskName="SHOP_ORDER" 
          where="SITE='1020' and ACTUAL_COMP_DATE is not null and now()>add_days(ACTUAL_COMP_DATE, 140)" />
  </job>
  <job name="SHOP_ORDER_DELETE" 
       source="HANA_ARCHIVE_TEST" 
       sourceDelete="true" 
       target="IDLE" 
       itemRunner="ui.tmd.zztop.TxKeyItemRunner">
    <item taskName="SHOP_ORDER" 
          where="SITE='1020' and ACTUAL_COMP_DATE is not null and now()>add_days(ACTUAL_COMP_DATE, 140)" />
  </job>
  <job name="PKG_CONTAINER" 
       source="HANA_PROD" 
       target="HANA_ARCHIVE_TEST" 
       itemRunner="ui.tmd.zztop.TxKeyItemRunner">
    <item taskName="ZR_PKG_CONTAINER" 
          where="STATE_NAME in ('IN_STOCK') and now()>add_days(PACKING_TIME, 100)" />
  </job>
</jobSpace>

Execute

執行定義在 wip.xml 內的 SHOP_ORDER 工作：

// Load plans from XML file.
TaskFactory factory = new TaskFactory(new File("wip.xml"));

// Create a runner of SHOP_ORDER of job.
JobRunner runner = factory.createRunner("SHOP_ORDER");

// RUN without where statement.
runner.run((String) null);