小花爸技術網站

set serveroutput on 
declare   
   type azp_array is table of ges.azp_file%rowtype; 
   vArr azp_array; 

BEGIN

select * BULK COLLECT INTO vArr  
  from azp_file 
 where azp03 not like ('ds%') and azp03 not in ('tw999','soctest','soc')
 order by azp03;
  
for k in vArr.first..vArr.last 
loop
   --dbms_output.put_line(vArr(k).azp03);
   
   --update ?轉傳票系統參數
   --dbms_output.put_line('update '||vArr(k).azp03||'.ooy_file set ooydmy1=''Y'',ooyglcr=''Y'',ooygslp=''2GA1'' where ooyslip in (''2RB1'',''2RB2'',''2RD1'',''2RD2'');');     
   --dbms_output.put_line('update '||vArr(k).azp03||'.apy_file set apyglcr=''Y'',apygslp=''2GA1'' where apyslip in (''2AA1'',''2AA2'',''2AA3'',''2AA4'',''2AB1'',''2AB2'',''2AC1'',''2AC2'',''2AL2'');');
    
   --同步員工資料
   --dbms_output.put_line('insert into '||vArr(k).azp03||'.gen_file select * From ges.gen_file where genacti=''Y'' and gen01 not in (select gen01 From '||vArr(k).azp03||'.gen_file where genacti=''Y'') and exists (select 1 From zxy_file where zxy03='''||UPPER(vArr(k).azp03)||''' and zxy01=gen01);');

   --失效員工資料，以 ges 資料庫為主
   --dbms_output.put_line('select * From '||vArr(k).azp03||'.gen_file where gen01 not in (select gen01 From ges.gen_file where genacti=''Y'') and genacti =''Y'';');
   --dbms_output.put_line('update '||vArr(k).azp03||'.gen_file set genacti=''N'' where gen01 not in (select gen01 From ges.gen_file where genacti=''Y'') and genacti =''Y'';');
    
   --修改員工資料(部門、職稱、分機)，以 ges 資料庫為?
   --dbms_output.put_line('select * From '||vArr(k).azp03||'.gen_file a,ges.gen_file b where a.gen01=b.gen01 and b.genacti=''Y'' and a.gen03<>b.gen03;');
   --dbms_output.put_line('update '||vArr(k).azp03||'.gen_file b set (gen03,gen04,gen05)=(select gen03,gen04,gen05 from ges.gen_file a where a.gen01=b.gen01) where b.genacti=''Y'';');    
    
   --失效廠商資料，以 ges 資料庫為主
   --dbms_output.put_line('select * From '||vArr(k).azp03||'.pmc_file where pmc01 not in (select pmc01 From ges.pmc_file where pmcacti=''Y'') and pmcacti =''Y'';');
   --dbms_output.put_line('update '||vArr(k).azp03||'.pmc_file set pmcacti=''N'',pmc05=''3'' where pmc01 not in (select pmc01 From ges.pmc_file where pmcacti=''Y'') and pmcacti =''Y'';');
    
   --更新營運中心保管地點
   --dbms_output.put_line('insert into '||vArr(k).azp03||'.faf_file select faf01,faf02,'''||UPPER(vArr(k).azp03)||''',fafacti,fafuser,fafgrup,fafmodu,fafdate,faforig,faforiu From ges.faf_file where fafacti=''Y'' and faf01 not in (select faf01 From '||vArr(k).azp03||'.faf_file where fafacti=''Y'');');

   --同步部門資料
   --if vArr(k).azp03 <> 'ges' then 
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.gem_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.gem_file select * From ges.gem_file;');
   --end if; 
    
   --同步部門折舊科目資料
   --if vArr(k).azp03 <> 'ges' then 
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.fbi_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.fbi_file select * From ges.fbi_file;');
   --end if; 
    
   --同步拒絕部門資料
   --if vArr(k).azp03 <> 'ges' then 
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.aab_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.aab_file value(aab01,aab02,aab03,aab00) select aab01,aab02,aab03,(select aaa01 from '||vArr(k).azp03||'.aaa_file where aaa01 in (''00'',''01'')) From ges.aab_file;');
   --end if; 
    
   --同步自動編碼設定
   --if vArr(k).azp03 <> 'ges' then 
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.gff_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.gff_file select * From ges.gff_file;');
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.gef_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.gef_file select * From ges.gef_file;');
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.geg_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.geg_file select * From ges.geg_file;');
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.geh_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.geh_file select * From ges.geh_file;');
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.gei_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.gei_file select * From ges.gei_file;');
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.gej_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.gej_file select * From ges.gej_file;');
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.gek_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.gek_file select * From ges.gek_file;');
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.gel_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.gel_file select * From ges.gel_file;');
   --end if; 
    
   --同步報表的報表結構
   --if vArr(k).azp03 <> 'ges' then 
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.mai_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.mai_file select * From ges.mai_file;');
   --  dbms_output.put_line('delete from '||vArr(k).azp03||'.maj_file;');
   --  dbms_output.put_line('insert into '||vArr(k).azp03||'.maj_file select * From ges.maj_file;');
   --end if; 
end loop; 
END;