iMES: 批次修改產品設定作業站企業邏輯

艾碼系統產品模組中產品作業站修正或預設功能太陽春,當要個別批次作業,操作太繁瑣,若直接修改資料庫會比較方便, 提供範例程式碼如下:

------------重工流程手動修改企業邏輯---------------------------------- -- 此SQL程序為手動調整產品作業站企業邏輯 --<< 鎖定參考產品料號設定同步複製需求產品的作業站流程企業邏輯>> --RHP作業站原流程: [START]-->[L_CI_GEN]-->[L_CO_SC]-->[END] -- ^^^^^^^^^^ --RHP作業站原流程: [START]-->[RWDATAINHERIT]-->[L_CI_GEN]-->[L_CO_SC]-->[END] -- 修改部分 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ -- 參考產品料號: 2231002312-0000, opno='RHP', ProductVersion=1 -- 宣告區 declare -- 宣告Cursor, 要loop檢查的資料來源 --TBLPRDOPBUSINESSRULERELATION尋找有RHP作業站產品料號 cursor c1 is select distinct productno,productversion from TBLPRDOPBUSINESSRULERELATION where opno='RHP' and productno like '2231008318%'; v_serialno varchar2(20); --儲存SERIALNO -- 程式開始 begin for r1 in c1 loop -- Loop開始, 以r1為變數,來源為宣告區的cursor c1 -- 修改TBLPRDOPBUSINESSRULERELATION delete from TBLPRDOPBUSINESSRULERELATION where opno='RHP' and productno=r1.productno and productversion=r1.productversion; Insert into TBLPRDOPBUSINESSRULERELATION (PRODUCTNO,PRODUCTVERSION,OPNO,FROMNODE,TONODE,LINKNAME,PHASENO) select r1.productno,r1.productversion,OPNO,FROMNODE,TONODE,LINKNAME,PHASENO from TBLPRDOPBUSINESSRULERELATION where productno='2231002312-0000' and opno='RHP'; -- 修改TBLPRDOPRULEXML_CLOB delete from TBLPRDOPRULEXML_CLOB where opno='RHP' and productno=r1.productno and productversion=r1.productversion; Insert into TBLPRDOPRULEXML_CLOB (PRODUCTNO,PRODUCTVERSION,OPNO,RULEXMLSTRING) select r1.productno,r1.productversion,opno,RULEXMLSTRING from TBLPRDOPRULEXML_CLOB where productno='2231002312-0000' and opno='RHP'; -- 修改參數 delete from TBLPRDOPATTRIB where serialno = (select serialno from TBLPRDOP where opno='RHP' and productno=r1.productno and productversion=r1.productversion); select serialno into v_serialno from TBLPRDOP where productno=r1.productno and productversion=r1.productversion and opno='RHP'; Insert into TBLPRDOPATTRIB (SERIALNO,ATTRIBTYPE,ATTRIBNO,ATTRIBSOURCE,ATTRIBPHASE,ATTRIBSEQUENCE,ATTRIBVALUE,ATTRIBSCRIPT,SAVETOLOTPROPERTYNO) select v_serialno,B.ATTRIBTYPE,B.ATTRIBNO,B.ATTRIBSOURCE,B.ATTRIBPHASE,B.ATTRIBSEQUENCE,B.ATTRIBVALUE,B.ATTRIBSCRIPT,B.SAVETOLOTPROPERTYNO FROM TBLPRDOP A,TBLPRDOPATTRIB B WHERE A.SERIALNO=B.SERIALNO AND A.PRODUCTNO='2231002312-0000' AND A.PRODUCTVERSION=1 AND OPNO='RHP' ; end loop; -- Loop結束 -- commit; -- 確認 end ;