艾碼系統產品模組中產品作業站修正或預設功能太陽春,當要個別批次作業,操作太繁瑣,若直接修改資料庫會比較方便, 提供範例程式碼如下:
------------重工流程手動修改企業邏輯----------------------------------
-- 此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 ;
