同步数据package

create or replace package body PKG_Import_ITEM_INSTANCE is
  -- Created : 9/16/2011 2:09:59 PM
  -- Purpose :

  --DBLINK_NAME      
  V_DBLINK_NAME VARCHAR2(40) := '@JF_8004';
  V_SQL         VARCHAR2(5000) := '';
  V_ID          VARCHAR2(40);
  V_MSG         VARCHAR2(5000) := '';
 
  --记录 CUX_SCADA_JOBLOG 表
  procedure RecordJobResult(
                        p_JobID IN VARCHAR2,
                         p_StartDateTime IN DATE,
                           p_EndDateTime IN DATE,
                          p_ProcessCount IN NUMBER,
                                p_Result IN VARCHAR2,
                              p_ErrorMsg IN NVARCHAR2,
                              V_Username in varchar2) is
              v_UsedSeconds NUMBER;
       begin
           v_UsedSeconds := (p_EndDateTime - p_StartDateTime) * 24 * 60 * 60;

           INSERT INTO CUX_SCADA_JOBLOG(SERIAL,jobid,startdatetime,enddatetime,usedtime,processcount,result,errormsg,Username)
           VALUES(SYS_GUID(),p_JobID,p_StartDateTime,p_EndDateTime,v_UsedSeconds,p_ProcessCount,p_Result,p_ErrorMsg,V_Username);
       commit;
       return;
       exception
       when others then
       V_MSG  :=  substrb(sqlerrm,1,4000);
           return;

      end RecordJobResult;
 
  PROCEDURE ImportINSTANCE is
 
    V_INSTANCE_NUMBER            VARCHAR2(30); -- not null
    V_ITEM_NUMBER                VARCHAR2(40);
    V_ITEM_DESCRIPTION           VARCHAR2(240);
    V_ITEM_CATEGORY              VARCHAR2(240);
    V_QUANTITY                   NUMBER; --not null
    V_UNIT_OF_MEASURE            VARCHAR2(3); --not null
    V_SERIAL_NUMBER              VARCHAR2(30);
    V_LOT_NUMBER                 VARCHAR2(80);
    V_PROJECT_NUMBER             VARCHAR2(240);
    V_PROJECT_NAME               VARCHAR2(240);
    V_CUSTOMER_NAME              VARCHAR2(360); -- not null
    V_CUSTOMER_ADDRESS           VARCHAR2(240);
    V_BLADE_STALL                VARCHAR2(240);
    V_INSTALL_ANGLE_INIT_VALUE   VARCHAR2(240);
    V_INSTALL_ANGLE_ADJUST_VALUE VARCHAR2(240);
    V_SWIRL_GENERATOR            VARCHAR2(240);
    V_P_INSTANCE_NUMBER          VARCHAR2(30);
    V_P_ITEM_NUMBER              VARCHAR2(40);
    V_P_ITEM_DESCRIPTION         VARCHAR2(240);
    V_P_ITEM_CATEGORY            VARCHAR2(240);
    V_INSTALL_DATE               VARCHAR2(20);
    V_LAST_UPDATE_DATE           VARCHAR2(20); -- not null
    V_P_SERIAL_NUMBER            VARCHAR2(30);
    INSTANCE_Cursor              Sys_Refcursor;
    Update_Time                  VARCHAR2(30);
    B_INSTANCE_NUMBER            VARCHAR2(30);
   
     v_JobID    VARCHAR2(40)  :=  'JOB_ImportINSTANCE';
--     v_StartDateTime  DATE;
     v_StartDateTime  DATE :=sysdate;
     I_ProcessCount   NUMBER  :=  0;
     v_ErrorID  VARCHAR2(4000);
     V_Username varchar2(40)  := 'admin';
    
  begin

      select to_char(sysdate,'yyyy-MM-dd HH24:MI:SS')
      into Update_Time from dual;

    SELECT NVL(MAX(LAST_UPDATE_DATE), '1990-01-01 12:00:00')
      INTO V_LAST_UPDATE_DATE
      FROM cux_scada_item_instance_v;
     
      --测试之用
      --V_LAST_UPDATE_DATE :='2011-09-20 10:20:54';
       
    V_SQL := '';
    V_SQL := V_SQL || ' SELECT
a.INSTANCE_NUMBER,a.ITEM_NUMBER,a.ITEM_DESCRIPTION,a.ITEM_CATEGORY,a.QUANTITY,a.UNIT_OF_MEASURE,a.SERIAL_NUMBER,';
    V_SQL := V_SQL || '
a.LOT_NUMBER,a.PROJECT_NUMBER,a.PROJECT_NAME,a.CUSTOMER_NAME,a.CUSTOMER_ADDRESS,a.BLADE_STALL,a.INSTALL_ANGLE_INIT_VALUE,';
    V_SQL := V_SQL || '
a.INSTALL_ANGLE_ADJUST_VALUE,a.SWIRL_GENERATOR,a.P_INSTANCE_NUMBER,a.P_ITEM_NUMBER,a.P_ITEM_DESCRIPTION,';
    V_SQL := V_SQL || ' a.P_ITEM_CATEGORY,a.INSTALL_DATE,a.LAST_UPDATE_DATE,a.P_SERIAL_NUMBER,';
    V_SQL := V_SQL || ' NVL(b.INSTANCE_NUMBER,'' '') AS BINSTANCE_NUMBER';
    V_SQL := V_SQL || ' FROM (';
    V_SQL := V_SQL || ' SELECT *';
    V_SQL := V_SQL || ' from cux_scada_item_instance_v' || V_DBLINK_NAME;
    V_SQL := V_SQL || ' WHERE  to_char(LAST_UPDATE_DATE,''yyyy-MM-dd HH24:MI:SS'')>'''||V_LAST_UPDATE_DATE ||''') a';
    V_SQL := V_SQL || ' LEFT JOIN cux_scada_item_instance_v b';
    V_SQL := V_SQL || ' ON a.INSTANCE_NUMBER=b.INSTANCE_NUMBER';
 
    open INSTANCE_Cursor for V_SQL;
    loop
      fetch INSTANCE_Cursor
        into V_INSTANCE_NUMBER, V_ITEM_NUMBER, V_ITEM_DESCRIPTION, V_ITEM_CATEGORY, V_QUANTITY,
             V_UNIT_OF_MEASURE, V_SERIAL_NUMBER, V_LOT_NUMBER,V_PROJECT_NUMBER, V_PROJECT_NAME,
             V_CUSTOMER_NAME, V_CUSTOMER_ADDRESS, V_BLADE_STALL, V_INSTALL_ANGLE_INIT_VALUE,
             V_INSTALL_ANGLE_ADJUST_VALUE, V_SWIRL_GENERATOR, V_P_INSTANCE_NUMBER, V_P_ITEM_NUMBER,
             V_P_ITEM_DESCRIPTION, V_P_ITEM_CATEGORY, V_INSTALL_DATE, V_LAST_UPDATE_DATE, V_P_SERIAL_NUMBER,
             B_INSTANCE_NUMBER;            
      exit when INSTANCE_Cursor%notfound;     
    
      if(B_INSTANCE_NUMBER != ' ') then
            UPDATE cux_scada_item_instance_v
                    SET  ITEM_NUMBER = V_ITEM_NUMBER,
                         ITEM_DESCRIPTION = V_ITEM_DESCRIPTION,
                         ITEM_CATEGORY = V_ITEM_CATEGORY,
                         QUANTITY = V_QUANTITY,
                         UNIT_OF_MEASURE = V_UNIT_OF_MEASURE,
                         SERIAL_NUMBER = V_UNIT_OF_MEASURE,
                         LOT_NUMBER = V_UNIT_OF_MEASURE,
                         PROJECT_NUMBER = V_UNIT_OF_MEASURE,
                         PROJECT_NAME = V_PROJECT_NAME,
                         CUSTOMER_NAME = V_CUSTOMER_NAME,
                         CUSTOMER_ADDRESS = V_CUSTOMER_ADDRESS,
                         BLADE_STALL = V_BLADE_STALL,
                         INSTALL_ANGLE_INIT_VALUE = V_INSTALL_ANGLE_INIT_VALUE,
                         INSTALL_ANGLE_ADJUST_VALUE = V_INSTALL_ANGLE_ADJUST_VALUE,
                         SWIRL_GENERATOR = V_SWIRL_GENERATOR,
                         P_INSTANCE_NUMBER = V_P_INSTANCE_NUMBER,
                         P_ITEM_NUMBER = V_P_ITEM_NUMBER,
                         P_ITEM_DESCRIPTION = V_P_ITEM_DESCRIPTION,
                         P_ITEM_CATEGORY = V_P_ITEM_CATEGORY,
                         INSTALL_DATE = V_INSTALL_DATE,
                         LAST_UPDATE_DATE = Update_Time,
                         P_SERIAL_NUMBER = V_P_SERIAL_NUMBER
                 WHERE INSTANCE_NUMBER = B_INSTANCE_NUMBER;       else
            V_ID := SYS_GUID();   
            INSERT INTO cux_scada_item_instance_v
              (ID,
               INSTANCE_NUMBER,
               ITEM_NUMBER,
               ITEM_DESCRIPTION,
               ITEM_CATEGORY,
               QUANTITY,
               UNIT_OF_MEASURE,
               SERIAL_NUMBER,
               LOT_NUMBER,
               PROJECT_NUMBER,
               PROJECT_NAME,
               CUSTOMER_NAME,
               CUSTOMER_ADDRESS,
               BLADE_STALL,
               INSTALL_ANGLE_INIT_VALUE,
               INSTALL_ANGLE_ADJUST_VALUE,
               SWIRL_GENERATOR,
               P_INSTANCE_NUMBER,
               P_ITEM_NUMBER,
               P_ITEM_DESCRIPTION,
               P_ITEM_CATEGORY,
               INSTALL_DATE,
               LAST_UPDATE_DATE,
               P_SERIAL_NUMBER)
             VALUES
              (V_ID,
               V_INSTANCE_NUMBER,
               V_ITEM_NUMBER,
               V_ITEM_DESCRIPTION,
               V_ITEM_CATEGORY,
               V_QUANTITY,
               V_UNIT_OF_MEASURE,
               V_SERIAL_NUMBER,
               V_LOT_NUMBER,
               V_PROJECT_NUMBER,
               V_PROJECT_NAME,
               V_CUSTOMER_NAME,
               V_CUSTOMER_ADDRESS,
               V_BLADE_STALL,
               V_INSTALL_ANGLE_INIT_VALUE,
               V_INSTALL_ANGLE_ADJUST_VALUE,
               V_SWIRL_GENERATOR,
               V_P_INSTANCE_NUMBER,
               V_P_ITEM_NUMBER,
               V_P_ITEM_DESCRIPTION,
               V_P_ITEM_CATEGORY,
               V_INSTALL_DATE,
               Update_Time,
               V_P_SERIAL_NUMBER);         end if;         I_ProcessCount  := I_ProcessCount + 1;
    end loop;    close INSTANCE_Cursor;
    commit;
       
     RecordJobResult(v_JobID,v_StartDateTime,sysdate,I_ProcessCount,'OK','',V_Username);
  exception
    when others then
      v_ErrorID  :=  substrb(sqlerrm,1,4000);
      if INSTANCE_Cursor%isopen then
        close INSTANCE_Cursor;
      end if;      rollback;
      RecordJobResult(v_JobID,v_StartDateTime,sysdate,I_ProcessCount,'FAIL',v_ErrorID,V_Username);
      commit;                                 
   
  end ImportINSTANCE;
end PKG_Import_ITEM_INSTANCE;