close
--轉入海關文件unnumber, first tax , description 到 XX_UN_NUMBER_IMP 後,執行解析與檢查

   cursor cur is select * from XX_UN_NUMBER_IMP where 1=1  and NEED_LOAD='Y' for update ;
   rec_t XX_UN_NUMBER_IMP%rowtype;
   rec_empty XX_UN_NUMBER_IMP%rowtype;
   x_str varchar2(400):=null;
   str_pos number;
   end_pos number;
   now_str varchar2(400):=null;
   procedure set_hiward_id(i_HIZARD in varchar2 ,i_type in varchar2 ,xHIZARD_CLASS_ID1 in out number,xHIZARD_CLASS_ID2 in out number ) is
     tmp varchar2(1):='Y';
     v_HIZARD_CLASS_ID number;
   begin
     select HAZARD_CLASS_ID
       into v_HIZARD_CLASS_ID
       from PO.PO_HAZARD_CLASSES_TL
      where DESCRIPTION = i_HIZARD 
        and LANGUAGE='ZHT';
     if   i_type ='1' then
       xHIZARD_CLASS_ID1:=v_HIZARD_CLASS_ID;
     else
       xHIZARD_CLASS_ID2:=v_HIZARD_CLASS_ID;
     end if;       
   exception
     when no_data_found then
           null;
     when others then
          PUT_LINE_UNLIMIT(i_HIZARD || sqlerrm);           
   end;
  
   procedure set_un_id(i_UN_NUMBER in varchar2,i_type in varchar2 ,x_EXISTS_UNMBER in out varchar2 ,xUN_NUMBER_ID1 in out number  ,xUN_NUMBER_ID2 in out number ,i_end_str in varchar2) is
     tmp varchar2(1):='N';
     v_UN_NUMBER_ID number;
   begin
     x_EXISTS_UNMBER := tmp;
     select 'Y' ,UN_NUMBER_ID
       into x_EXISTS_UNMBER,v_UN_NUMBER_ID
       from PO.PO_UN_NUMBERS_TL
      where UN_NUMBER = i_UN_NUMBER
        and DESCRIPTION like   '%' || i_end_str
        and LANGUAGE='ZHT';
     if   i_type ='1' then
       xUN_NUMBER_ID1:=v_UN_NUMBER_ID;
     else
       xUN_NUMBER_ID2:=v_UN_NUMBER_ID;
     end if;
   exception
     when no_data_found then
           null;
     when others then
      x_EXISTS_UNMBER:='E';
       --null;--   PUT_LINE_UNLIMIT(i_UN_NUMBER || sqlerrm);  
   end;  
begin
    for rec in cur loop
      rec_t := rec_empty;
      rec_t.UN_NUMBER := rec.UN_NUMBER;
      rec_t.DESCRIPTION := rec.DESCRIPTION;
      rec_t.DESCRIPTION_US := rec.DESCRIPTION_US;
                
      if rec.FIRST_TAX like '%免稅%' then
        rec_t.PARSE_HIWZARD_TYPE := '1';
        rec_t.HIZARD_NAME1 :='0';
        rec_t.UN_NUMBER_NAME1 := rec.DESCRIPTION ;
        set_hiward_id(rec_t.HIZARD_NAME1,'1',rec_t.HIZARD_CLASS_ID1,rec_t.HIZARD_CLASS_ID2);  
        set_un_id(rec_t.UN_NUMBER ,'1',rec_t.EXISTS_UNMBER,rec_t.UN_NUMBER_ID1,rec_t.UN_NUMBER_ID2, null);       
       -- PUT_LINE_UNLIMIT(rec.PARSE_HIWZARD_TYPE  || ':' || rec.FIRST_TAX || '=>' ||  rec.HIZARD_NAME);
      elsif instrb(rec.FIRST_TAX,'%') > 0  then       
        end_pos :=  instr(rec.FIRST_TAX,'%');
        str_pos := instrb(rec.FIRST_TAX,'或');
        if  str_pos > 0 then
        
         now_str :=  trim(replace(substrb(rec.FIRST_TAX,str_pos+1),'從高徵稅',null));
         rec_t.HIZARD_NAME1 := now_str;
         set_hiward_id(rec_t.HIZARD_NAME1,'1',rec_t.HIZARD_CLASS_ID1,rec_t.HIZARD_CLASS_ID2);
         set_un_id(rec_t.UN_NUMBER ,'1',rec_t.EXISTS_UNMBER,rec_t.UN_NUMBER_ID1,rec_t.UN_NUMBER_ID2, '價');
         rec_t.UN_NUMBER_NAME1 := rec.DESCRIPTION || '-價';
        
         now_str :=  trim(replace(substrb(rec.FIRST_TAX,1,str_pos),'新臺幣',null));
         rec_t.HIZARD_NAME2 := now_str;
         set_hiward_id(rec_t.HIZARD_NAME2,'2',rec_t.HIZARD_CLASS_ID1,rec_t.HIZARD_CLASS_ID2);
         set_un_id(rec_t.UN_NUMBER ,'2',rec_t.EXISTS_UNMBER,rec_t.UN_NUMBER_ID1,rec_t.UN_NUMBER_ID2, '量');
         rec_t.UN_NUMBER_NAME2 := rec.DESCRIPTION || '-量';
         rec_t.PARSE_HIWZARD_TYPE := '3';
        else
         now_str := rec.FIRST_TAX;
         rec_t.HIZARD_NAME1 :=now_str;  
         rec_t.PARSE_HIWZARD_TYPE := '1';
         set_hiward_id(rec_t.HIZARD_NAME1,'1',rec_t.HIZARD_CLASS_ID1,rec_t.HIZARD_CLASS_ID2);  
         set_un_id(rec_t.UN_NUMBER ,'1',rec_t.EXISTS_UNMBER,rec_t.UN_NUMBER_ID1,rec_t.UN_NUMBER_ID2, null);
         rec_t.UN_NUMBER_NAME1 := rec.DESCRIPTION ;
        end if;
       
      --  PUT_LINE_UNLIMIT(rec.PARSE_HIWZARD_TYPE  || ':' || rec.FIRST_TAX || '=>' || now_str);
      
      else      
        now_str := trim(replace(rec.FIRST_TAX,'新臺幣',null));
        rec_t.PARSE_HIWZARD_TYPE := '2';    
        rec_t.HIZARD_NAME2 :=now_str; 
        set_hiward_id(rec_t.HIZARD_NAME2,rec_t.PARSE_HIWZARD_TYPE,rec_t.HIZARD_CLASS_ID1,rec_t.HIZARD_CLASS_ID2);
        set_un_id(rec_t.UN_NUMBER ,'2',rec_t.EXISTS_UNMBER,rec_t.UN_NUMBER_ID1,rec_t.UN_NUMBER_ID2, null);
        rec_t.UN_NUMBER_NAME2 := rec.DESCRIPTION ;
     --   PUT_LINE_UNLIMIT(rec.PARSE_HIWZARD_TYPE  || ':' || rec.FIRST_TAX || '=>' || now_str);
      end if;
  
  
      if rec_t.EXISTS_UNMBER ='E' then
       PUT_LINE_UNLIMIT(rec_t.UN_NUMBER  || ', ' || rec_t.DESCRIPTION || ', ' ||rec.FIRST_TAX);
      else
      update XX_UN_NUMBER_IMP
        set EXISTS_UNMBER=rec_t.EXISTS_UNMBER,
            HIZARD_CLASS_ID1=rec_t.HIZARD_CLASS_ID1,
            HIZARD_CLASS_ID2=rec_t.HIZARD_CLASS_ID2,
            HIZARD_NAME1=rec_t.HIZARD_NAME1,
            HIZARD_NAME2=rec_t.HIZARD_NAME2,
            UN_NUMBER_ID1=rec_t.UN_NUMBER_ID1,
            UN_NUMBER_ID2=rec_t.UN_NUMBER_ID2,
            UN_NUMBER_NAME1=rec_t.UN_NUMBER_NAME1,
            UN_NUMBER_NAME2=rec_t.UN_NUMBER_NAME2,
            PARSE_HIWZARD_TYPE=rec_t.PARSE_HIWZARD_TYPE
       where current of cur;
      end if;     
    end loop;
    commit;
end;
 
 
---新增及修改un number 程式碼
 
declare
  cursor cur is  select * from XX_UN_NUMBER_IMP where 1=1  and NEED_LOAD='Y' for update; 
  X_ROWID  VARCHAR2(30) ;
  X_UN_NUMBER_ID NUMBER;
  X_HAZARD_CLASS_ID NUMBER;
  X_INACTIVE_DATE DATE;
  X_ATTRIBUTE_CATEGORY VARCHAR2(30);
  X_ATTRIBUTE1 VARCHAR2(150);
  X_ATTRIBUTE2 VARCHAR2(150);
  X_ATTRIBUTE3 VARCHAR2(150);
  X_ATTRIBUTE4 VARCHAR2(150);
  X_ATTRIBUTE5 VARCHAR2(150);
  X_ATTRIBUTE6 VARCHAR2(150);
  X_ATTRIBUTE7 VARCHAR2(150);
  X_ATTRIBUTE8 VARCHAR2(150);
  X_ATTRIBUTE9 VARCHAR2(150);
  X_ATTRIBUTE10 VARCHAR2(150);
  X_ATTRIBUTE11 VARCHAR2(150);
  X_ATTRIBUTE12 VARCHAR2(150);
  X_ATTRIBUTE13 VARCHAR2(150);
  X_ATTRIBUTE14 VARCHAR2(150);
  X_ATTRIBUTE15 VARCHAR2(150);
  X_REQUEST_ID NUMBER;
  X_UN_NUMBER VARCHAR2(25);
  X_DESCRIPTION VARCHAR2(240);
  X_CREATION_DATE DATE;
  X_CREATED_BY NUMBER;
  X_LAST_UPDATE_DATE DATE;
  X_LAST_UPDATED_BY NUMBER;
  X_LAST_UPDATE_LOGIN NUMBER;
 
begin
 execute immediate 'ALTER SESSION SET NLS_LANGUAGE = ''TRADITIONAL CHINESE''';
   for rec in cur loop
  X_ROWID :=null;
  X_UN_NUMBER_ID :=null;
  X_HAZARD_CLASS_ID :=null;
  X_INACTIVE_DATE :=null;
  X_ATTRIBUTE_CATEGORY :=null;
  X_ATTRIBUTE1 :=null;
  X_ATTRIBUTE2 :=null;
  X_ATTRIBUTE3 :=null;
  X_ATTRIBUTE4 :=null;
  X_ATTRIBUTE5 :=null;
  X_ATTRIBUTE6 :=null;
  X_ATTRIBUTE7 :=null;
  X_ATTRIBUTE8 :=null;
  X_ATTRIBUTE9 :=null;
  X_ATTRIBUTE10 :=null;
  X_ATTRIBUTE11 :=null;
  X_ATTRIBUTE12 :=null;
  X_ATTRIBUTE13 :=null;
  X_ATTRIBUTE14 :=null;
  X_ATTRIBUTE15 :=null;
  X_REQUEST_ID :=null;
  X_UN_NUMBER :=rec.UN_NUMBER;
  X_DESCRIPTION :=null;
  X_CREATION_DATE :=sysdate;
  X_CREATED_BY :=3314;
  X_LAST_UPDATE_DATE :=sysdate;
  X_LAST_UPDATED_BY :=3314;
  X_LAST_UPDATE_LOGIN :=null;
     if rec.UN_NUMBER_ID1 is not null then
       X_UN_NUMBER_ID :=rec.UN_NUMBER_ID1;
       X_HAZARD_CLASS_ID :=rec.HIZARD_CLASS_ID1;
       X_DESCRIPTION:=rec.UN_NUMBER_NAME1;
         update PO_UN_NUMBERS_B set
                HAZARD_CLASS_ID = X_HAZARD_CLASS_ID,
                INACTIVE_DATE = X_INACTIVE_DATE,
                ATTRIBUTE1 = X_ATTRIBUTE1,
                LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
                LAST_UPDATED_BY = X_LAST_UPDATED_BY
          where UN_NUMBER_ID = X_UN_NUMBER_ID;
 
         if (sql%found) then
         -- PUT_LINE_UNLIMIT('upd ' || X_UN_NUMBER || ', id1='|| X_UN_NUMBER_ID );        
         update PO_UN_NUMBERS_TL set
                UN_NUMBER = X_UN_NUMBER,
                DESCRIPTION = X_DESCRIPTION,
                LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
                LAST_UPDATED_BY = X_LAST_UPDATED_BY
          where UN_NUMBER_ID = X_UN_NUMBER_ID;
          update XX_UN_NUMBER_IMP
            set import_status='UPD 1'
          where current of cur;           
         else
         -- PUT_LINE_UNLIMIT('upd not found' || X_UN_NUMBER || ', id1='|| X_UN_NUMBER_ID );        
          update XX_UN_NUMBER_IMP
            set import_status='UPD 1 NO'
          where current of cur;          
         end if;          
 
     end if; 
     if rec.UN_NUMBER_ID2 is not null then
       X_UN_NUMBER_ID :=rec.UN_NUMBER_ID2;
       X_HAZARD_CLASS_ID :=rec.HIZARD_CLASS_ID2;
       X_DESCRIPTION:=rec.UN_NUMBER_NAME2;
         update PO_UN_NUMBERS_B set
                HAZARD_CLASS_ID = X_HAZARD_CLASS_ID,
                INACTIVE_DATE = X_INACTIVE_DATE,
                ATTRIBUTE1 = X_ATTRIBUTE1,
                LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
                LAST_UPDATED_BY = X_LAST_UPDATED_BY
          where UN_NUMBER_ID = X_UN_NUMBER_ID;
         if (sql%found) then
       -- PUT_LINE_UNLIMIT('upd ' || X_UN_NUMBER || ', id2='|| X_UN_NUMBER_ID );
         update PO_UN_NUMBERS_TL set
                UN_NUMBER = X_UN_NUMBER,
                DESCRIPTION = X_DESCRIPTION,
                LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
                LAST_UPDATED_BY = X_LAST_UPDATED_BY
          where UN_NUMBER_ID = X_UN_NUMBER_ID;
          update XX_UN_NUMBER_IMP
            set import_status='UPD 2'
          where current of cur;              
         else
       --   PUT_LINE_UNLIMIT('upd not found ' || X_UN_NUMBER || ', id2='|| X_UN_NUMBER_ID );
          update XX_UN_NUMBER_IMP
            set import_status='UPD 2 NO'
          where current of cur;          
         end if;    
    
     end if;  
     if rec.UN_NUMBER_ID1 is null  and rec.UN_NUMBER_ID2 is null then
       if  rec.UN_NUMBER_NAME1 is not null then 
         X_ROWID :=null; 
         X_UN_NUMBER_ID :=null;
         X_HAZARD_CLASS_ID :=rec.HIZARD_CLASS_ID1;
         X_DESCRIPTION:=rec.UN_NUMBER_NAME1;
 
         PO_UN_NUMBERS_PKG.INSERT_ROW
          (X_ROWID,X_UN_NUMBER_ID,X_HAZARD_CLASS_ID,X_INACTIVE_DATE,
           X_ATTRIBUTE_CATEGORY,X_ATTRIBUTE1,X_ATTRIBUTE2,X_ATTRIBUTE3,
           X_ATTRIBUTE4,X_ATTRIBUTE5,X_ATTRIBUTE6,X_ATTRIBUTE7,
           X_ATTRIBUTE8,X_ATTRIBUTE9,X_ATTRIBUTE10,X_ATTRIBUTE11,
           X_ATTRIBUTE12,X_ATTRIBUTE13,X_ATTRIBUTE14,X_ATTRIBUTE15,
           X_REQUEST_ID,X_UN_NUMBER,X_DESCRIPTION,X_CREATION_DATE,
           X_CREATED_BY,X_LAST_UPDATE_DATE,X_LAST_UPDATED_BY,X_LAST_UPDATE_LOGIN);
       --  PUT_LINE_UNLIMIT('ins ' || X_UN_NUMBER || ', name1='|| X_DESCRIPTION || ',id1=' || X_UN_NUMBER_ID );          
         if  X_ROWID is not null then
         update XX_UN_NUMBER_IMP
            set UN_NUMBER_ID1=X_UN_NUMBER_ID,import_status='INS 1'
          where current of cur;     
         else
         update XX_UN_NUMBER_IMP
            set import_status='INS 2 NO'
          where current of cur;         
         end if;           
       end if; 
       if rec.UN_NUMBER_NAME2 is not null then   
         X_ROWID :=null;  
         X_UN_NUMBER_ID :=null;
         X_HAZARD_CLASS_ID :=rec.HIZARD_CLASS_ID2;
         X_DESCRIPTION:=rec.UN_NUMBER_NAME2; 
       
         PO_UN_NUMBERS_PKG.INSERT_ROW
          (X_ROWID,X_UN_NUMBER_ID,X_HAZARD_CLASS_ID,X_INACTIVE_DATE,
           X_ATTRIBUTE_CATEGORY,X_ATTRIBUTE1,X_ATTRIBUTE2,X_ATTRIBUTE3,
           X_ATTRIBUTE4,X_ATTRIBUTE5,X_ATTRIBUTE6,X_ATTRIBUTE7,
           X_ATTRIBUTE8,X_ATTRIBUTE9,X_ATTRIBUTE10,X_ATTRIBUTE11,
           X_ATTRIBUTE12,X_ATTRIBUTE13,X_ATTRIBUTE14,X_ATTRIBUTE15,
           X_REQUEST_ID,X_UN_NUMBER,X_DESCRIPTION,X_CREATION_DATE,
           X_CREATED_BY,X_LAST_UPDATE_DATE,X_LAST_UPDATED_BY,X_LAST_UPDATE_LOGIN);
         --   PUT_LINE_UNLIMIT('ins ' || X_UN_NUMBER || ', name2='|| X_DESCRIPTION || ',id2=' || X_UN_NUMBER_ID );
         if  X_ROWID is not null then
         update XX_UN_NUMBER_IMP
            set UN_NUMBER_ID2=X_UN_NUMBER_ID,import_status='INS 2'
          where current of cur;     
         else
         update XX_UN_NUMBER_IMP
            set import_status='INS 2 NO'
          where current of cur;         
         end if;               
       end if;               
     end if; 
   end loop;
end; 
 
 
arrow
arrow
    全站熱搜

    Nolem 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄